SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS') report_run_date,
onaccount_amount,
unapplied_amount,
unidentified_amount,
manually_applied_amount,
auto_applied_amount,
receipt_amount,
receipt_number,
to_char(receipt_date,'DD-MON-YYYY') receipt_date,
to_char(apply_date,'DD-MON-YYYY') apply_date,
receipt_method,
decode(status,'APP','Applied','UNAPP','Unapplied','UNID','Unidentified','Reversed') status,
pobox,
station,
to_char(gl_date,'DD-MON-YYYY') gl_date,
customer_name,
customer_number,
masked_account_num,
decode(receipt_type,'CASH','Standard','MISC','Miscellaneous',receipt_type) receipt_type,
market
FROM
(
SELECT sum(onaccount_amount)onaccount_amount,
sum(unapplied_amount)unapplied_amount,
sum(manually_applied_amount) manually_applied_amount,
sum(auto_applied_amount) auto_applied_amount,
sum(unidentified_amount) unidentified_amount,
receipt_number,
receipt_date,
receipt_amount,
max(apply_date) apply_date ,
status ,
pobox,
station,
receipt_method,
gl_date,
customer_name,
customer_number,
masked_account_num,
receipt_type,
market
FROM
(
--UNAPPLIED RECEIPT AMOUNTS
select onaccount_amount, sum(unapplied_amount)unapplied_amount, unidentified_amount, manually_applied_amount, auto_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
from
(
--UNAPPLIED AMOUNTS
select 0 onaccount_amount,
sum(araa.amount_applied) unapplied_amount,
0 unidentified_amount,
0 manually_applied_amount,
0 Auto_applied_amount,
acra.amount receipt_amount,
acra.receipt_number,
acra.receipt_date,
max(apply_date) apply_date,
acra.status,
acra.attribute8 pobox,
acra.attribute3 station,
arm.name receipt_method,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type receipt_type,
(select ffvt.description
from fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where flex_value = acra.attribute8
and ffv.flex_value_id = ffvt.flex_value_id
and flex_value_set_id = '1010389'
and ffv.enabled_flag ='Y'
and ffvt.language= 'US') market
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where araa.status = 'UNAPP'
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
group by acra.receipt_number,acra.receipt_date,acra.status,
acra.attribute8,acra.attribute3,arm.name, acra.amount,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,CBA.MASKED_ACCOUNT_NUM,acra.type )
group by onaccount_amount, auto_applied_amount, unidentified_amount, manually_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
UNION
--UNIDENTIFIED RECEIPT AMOUNTS
select onaccount_amount, unapplied_amount,sum(unidentified_amount) unidentified_amount, manually_applied_amount, auto_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
from
(
select 0 onaccount_amount,
0 unapplied_amount,
sum(araa.amount_applied) unidentified_amount,
0 manually_applied_amount,
0 Auto_applied_amount,
acra.amount receipt_amount,
acra.receipt_number,
acra.receipt_date,
apply_date apply_date,
acra.status,
acra.attribute8 pobox,
acra.attribute3 station,
arm.name receipt_method,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type receipt_type,
(select ffvt.description
from fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where flex_value = acra.attribute8
and ffv.flex_value_id = ffvt.flex_value_id
and flex_value_set_id = '1010389'
and ffv.enabled_flag ='Y'
and ffvt.language= 'US') market
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where araa.status = 'UNID'
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
group by acra.amount ,
acra.receipt_number,
acra.receipt_date,
apply_date ,
acra.status,
acra.attribute8 ,
acra.attribute3 ,
arm.name ,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type
having sum(araa.amount_applied) > 0
)
group by auto_applied_amount, unapplied_amount, onaccount_amount, manually_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
UNION
--ONACCOUNT RECEIPT AMOUNTS
select sum(onaccount_amount) onaccount_amount, unapplied_amount, unidentified_amount, manually_applied_amount, auto_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
from
(
select sum(araa.amount_applied) onaccount_amount,
0 unapplied_amount,
0 unidentified_amount,
0 manually_applied_amount,
0 Auto_applied_amount,
acra.amount receipt_amount,
acra.receipt_number,
acra.receipt_date,
max(apply_date) apply_date,
acra.status,
acra.attribute8 pobox,
acra.attribute3 station,
arm.name receipt_method,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type receipt_type,
(select ffvt.description
from fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where flex_value = acra.attribute8
and ffv.flex_value_id = ffvt.flex_value_id
and flex_value_set_id = '1010389'
and ffv.enabled_flag ='Y'
and ffvt.language= 'US') market
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where araa.status = 'ACC'
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
and araa.display = 'Y'
group by acra.receipt_number,acra.receipt_date,acra.status,
acra.attribute8,acra.attribute3,arm.name, acra.amount,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,CBA.MASKED_ACCOUNT_NUM,acra.type )
group by auto_applied_amount, unapplied_amount, unidentified_amount, manually_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
UNION
--Receipt Write Off amount as Manual Applied Amount
select onaccount_amount, unapplied_amount,unidentified_amount, sum(manually_applied_amount) manually_applied_amount, auto_applied_amount, receipt_number, receipt_date,
receipt_amount,apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market
from (
select 0 onaccount_amount,
0 unapplied_amount,
0 unidentified_amount,
araa.amount_applied manually_applied_amount,
0 Auto_applied_amount,
acra.amount receipt_amount,
acra.receipt_number,
acra.receipt_date,
apply_date ,
acra.status,
acra.attribute8 pobox,
acra.attribute3 station,
arm.name receipt_method,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type receipt_type,
(select ffvt.description
from fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where flex_value = acra.attribute8
and ffv.flex_value_id = ffvt.flex_value_id
and flex_value_set_id = '1010389'
and ffv.enabled_flag ='Y'
and ffvt.language= 'US') market
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where 1=1 --and receipt_number = '275944750084'
and araa.status in ('ACTIVITY')
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
and display ='Y'
and araa.receivable_application_id not in (
select araa.receivable_application_id
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where araa.status in ('APP')
and araa.display = 'Y'
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
and araa.applied_customer_trx_id in (
select ract.customer_trx_id
from ar_cash_remit_refs_all acrra,
ra_customer_trx_all ract
where ract.trx_number = acrra.invoice_reference
and acrra.cash_receipt_id = araa.cash_receipt_id
and acrra.invoice_reference is not null
and acrra.resolved_matching_number is not null
and acrra.resolved_matching_number <> 'NULL'
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date)))
)
) group by
onaccount_amount, unapplied_amount,unidentified_amount, auto_applied_amount, receipt_number, receipt_date,receipt_amount,
apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market
UNION
-- MANUALLY_APPLIED_AMOUNT
select onaccount_amount, unapplied_amount,unidentified_amount, sum(manually_applied_amount) manually_applied_amount, auto_applied_amount, receipt_number, receipt_date,
receipt_amount,apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market
from (
select 0 onaccount_amount,
0 unapplied_amount,
0 unidentified_amount,
araa.amount_applied manually_applied_amount,
0 Auto_applied_amount,
acra.amount receipt_amount,
acra.receipt_number,
acra.receipt_date,
apply_date ,
acra.status,
acra.attribute8 pobox,
acra.attribute3 station,
arm.name receipt_method,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type receipt_type,
(select ffvt.description
from fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where flex_value = acra.attribute8
and ffv.flex_value_id = ffvt.flex_value_id
and flex_value_set_id = '1010389'
and ffv.enabled_flag ='Y'
and ffvt.language= 'US') market
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where 1=1 --and receipt_number = '275944750084'
and araa.status in ('APP')
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
and display ='Y'
and araa.receivable_application_id not in (
select araa.receivable_application_id
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where araa.status in ('APP')
and araa.display = 'Y'
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
and araa.applied_customer_trx_id in (
select ract.customer_trx_id
from ar_cash_remit_refs_all acrra,
ra_customer_trx_all ract
where ract.trx_number = acrra.invoice_reference
and acrra.cash_receipt_id = araa.cash_receipt_id
and acrra.invoice_reference is not null
and acrra.resolved_matching_number is not null
and acrra.resolved_matching_number <> 'NULL'
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date)))
)
) group by
onaccount_amount, unapplied_amount,unidentified_amount, auto_applied_amount, receipt_number, receipt_date,receipt_amount,
apply_date, status, pobox,station,receipt_method,gl_date, customer_name, customer_number, masked_account_num,receipt_type,market
UNION
-- AUTO APPLIED RECEIPT AMOUNTS WITH AUTO_APPLIED Y
select onaccount_amount, unapplied_amount, unidentified_amount, manually_applied_amount, sum(auto_applied_amount) auto_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
from
(
select 0 onaccount_amount,
0 unapplied_amount,
0 unidentified_amount,
0 manually_applied_amount,
araa.amount_applied Auto_applied_amount,
acra.amount receipt_amount,
acra.receipt_number,
acra.receipt_date,
apply_date apply_date,
acra.status,
acra.attribute8 pobox,
acra.attribute3 station,
arm.name receipt_method,
arpsa.gl_date,
ac.customer_name,
ac.customer_number,
CBA.MASKED_ACCOUNT_NUM,
acra.type receipt_type,
(select ffvt.description
from fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where flex_value = acra.attribute8
and ffv.flex_value_id = ffvt.flex_value_id
and flex_value_set_id = '1010389'
and ffv.enabled_flag ='Y'
and ffvt.language= 'US') market
from ar_receivable_applications_all araa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
ar_payment_schedules_all arpsa,
ar_customers ac,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL ABA,
CE_BANK_BRANCHES_V ABB
where araa.status in ('APP')
and araa.display = 'Y'
and acra.cash_receipt_id = araa.cash_receipt_id
and arm.receipt_method_id = acra.receipt_method_id
and arpsa.payment_schedule_id = araa.payment_schedule_id
and ac.customer_id(+) = arpsa.customer_id
and acra.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
and CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
and ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date))
and acra.set_of_books_id = '1001'
and araa.applied_customer_trx_id in (
select ract.customer_trx_id
from ar_cash_remit_refs_all acrra,
ra_customer_trx_all ract
where ract.trx_number = acrra.invoice_reference
and acrra.invoice_reference is not null
and acrra.cash_receipt_id = araa.cash_receipt_id
and acrra.resolved_matching_number is not null
and acrra.resolved_matching_number <> 'NULL'
and trunc(acra.receipt_date) between nvl(:P_RECEIPT_FROM_DATE,trunc(acra.receipt_date)) and nvl(:P_RECEIPT_TO_DATE,trunc(acra.receipt_date))
and trunc(arpsa.gl_date) between nvl(:P_GL_FROM_DATE,trunc(arpsa.gl_date)) and nvl(:P_GL_TO_DATE,trunc(arpsa.gl_date)))
)
group by onaccount_amount, unapplied_amount, unidentified_amount, manually_applied_amount,
receipt_number, receipt_date, receipt_amount, apply_date , status, pobox,station, receipt_method, gl_date, customer_name,
customer_number, masked_account_num, receipt_type, market
)group by receipt_number, receipt_date, receipt_amount, status , pobox, station,
receipt_method, gl_date, customer_name, customer_number, masked_account_num, receipt_type, market
)
order by receipt_date
No comments:
Post a Comment