*PURPOSE: SQL Query to get Customer Statement Report *
*AUTHOR: Samba Siva Chowdary *
*******************************************************************/
SELECT ou_name,
ou_addr,
trx_number,
trx_date,
currency_code,
exchange_rate,
customer_number,
customer_name,
inv_amt,
receipt_amt,
short_excess,
receipt_date,
TYPE,
inv_type,
due_date,
extra_day
FROM (SELECT (SELECT hou.name
FROM hr_operating_units hou
WHERE organization_id = RCTA.org_id) OU_NAME,
(SELECT ( hla.address_line_1
|| ' '
|| hla.address_line_2
|| ' '
|| hla.address_line_3
|| ' '
|| hla.address_line_4
|| ' '
|| hla.town_or_city
|| ' '
|| hla.region_1
|| ' '
|| hla.region_2 ) addd
FROM hr_locations_all hla,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrou.organization_id = RCTA.org_id
AND hla.location_id = hrou.location_id)
|| ' '
||(SELECT ft.territory_short_name
FROM fnd_territories_vl ft,
hr_locations_all hrl,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrl.country = ft.territory_code
AND hrou.organization_id = RCTA.org_id
AND hrl.location_id = hrou.location_id)
|| ' '
||(SELECT postal_code
FROM hr_locations_all hrl,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrou.organization_id = RCTA.org_id
AND hrl.location_id = hrou.location_id) OU_ADDR,
rcta.trx_number,
rcta.trx_date trx_date,
rcta.invoice_currency_code
CURRENCY_CODE,
Nvl(rcta.exchange_rate, 1)
EXCHANGE_RATE,
hca.account_number
customer_number,
hp.party_name
customer_name,
trx_amount.extended_amount INV_AMT,
araa.amount_applied RECEIPT_AMT,
( trx_amount.extended_amount - araa.amount_applied ) Short_Excess
,
araa.receipt_date,
rctt.TYPE,
To_char(apsa.due_date, 'DD/MM/YYYY') due_date,
( Trunc(araa.apply_date) - Trunc(apsa.due_date) ) extra_day
--multiple recepts dates ?
,
1 Flag,
rctt.name Inv_Type
FROM hz_parties hp,
hz_cust_accounts hca,
ra_customer_trx_all rcta,
ra_cust_trx_types_all rctt,
ar_payment_schedules_all apsa,
(SELECT araa.applied_customer_trx_id,
SUM(araa.amount_applied)
amount_applied,
Listagg(DISTINCT To_char(araa.apply_date, 'DD/MM/YYYY'),
' / ')
within GROUP (ORDER BY
To_char(araa.apply_date, 'DD/MM/YYYY'))
receipt_date,
Max(araa.apply_date)
APPLY_DATE
FROM ar_cash_receipts_all acra,
ar_receivable_applications_all araa
WHERE 1 = 1
--and araa.applied_customer_trx_id = rcta.customer_trx_id
AND acra.cash_receipt_id = araa.cash_receipt_id
--and araa.applied_customer_trx_id=81066
GROUP BY araa.applied_customer_trx_id) araa,
(SELECT rctla.customer_trx_id,
SUM (rctla.extended_amount) extended_amount
FROM ra_customer_trx_lines_all rctla
GROUP BY rctla.customer_trx_id) trx_amount
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND rcta.customer_trx_id = apsa.customer_trx_id(+)
AND araa.applied_customer_trx_id(+) = rcta.customer_trx_id
--AND rcta.trx_number = '2122705200000279'
AND rctt.cust_trx_type_seq_id = RCTa.cust_trx_type_seq_id
AND ( rcta.org_id IN ( :p_org_name )
OR 'All' IN ( :p_org_name
|| 'All' ) )
AND apsa.gl_date <= :p_end_date
AND ( hp.party_id IN ( :P_PARTY_NAME )
OR 'All' IN ( :P_PARTY_NAME
|| 'All' ) )
AND ( RCTa.invoice_currency_code IN ( :P_CURR )
OR 'All' IN ( :P_CURR
|| 'All' ) )
AND trx_amount.customer_trx_id(+) = rcta.customer_trx_id
AND ( ( rctt.name ) IN ( :P_INVOICE_TYPE )
OR 'All' IN ( :P_INVOICE_TYPE
|| 'All' ) )
UNION
SELECT (SELECT hou.name
FROM hr_operating_units hou
WHERE organization_id = acra.org_id) OU_NAME,
(SELECT ( hla.address_line_1
|| ' '
|| hla.address_line_2
|| ' '
|| hla.address_line_3
|| ' '
|| hla.address_line_4
|| ' '
||hla.town_or_city
|| ' '
|| hla.region_1
|| ' '
|| hla.region_2 ) addd
FROM hr_locations_all hla,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrou.organization_id = acra.org_id
AND hla.location_id = hrou.location_id)
|| ' '
||(SELECT ft.territory_short_name
FROM fnd_territories_vl ft,
hr_locations_all hrl,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrl.country = ft.territory_code
AND hrou.organization_id = acra.org_id
AND hrl.location_id = hrou.location_id)
|| ' '
||(SELECT postal_code
FROM hr_locations_all hrl,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrou.organization_id = acra.org_id
AND hrl.location_id = hrou.location_id) OU_ADDR,
NULL trx_number,
--acra.RECEIPT_NUMBER trx_number,
NULL trx_date,
--to_char(acra.receipt_date,'DD/MM/YYYY') trx_date,
acra.currency_code,
Nvl(acra.exchange_rate, 1) EXCHANGE_RATE,
HCA.account_number customer_number
,
hp.party_name
customer_name,
0 INVOICE_AMOUNT,
--acra.amount RECEIPT_AMT,
Abs(apsa.amount_due_remaining) RECEIPT_AMT,
( 0 - Abs(apsa.amount_due_remaining) ) Short_Excess,
To_char(acra.receipt_date, 'DD/MM/YYYY') receipt_date,
acra.TYPE,
To_char(apsa.due_date, 'DD/MM/YYYY') due_date,
NULL extra_day,
2 Flag,
'Receipt' Inv_Type
FROM ar_cash_receipts_all acra,
ar_receivable_applications_all arra,
ar_payment_schedules_all apsa,
hz_cust_accounts HCA,
hz_parties HP
WHERE acra.status = 'UNAPP' --in ('APP', 'UNAPP')
AND acra.cash_receipt_id = arra.cash_receipt_id
AND acra.cash_receipt_id = apsa.cash_receipt_id
AND ( acra.org_id IN ( :p_org_name )
OR 'All' IN ( :p_org_name
|| 'All' ) )
AND apsa.gl_date <= :p_end_date
AND ( acra.currency_code IN ( :P_CURR )
OR 'All' IN ( :P_CURR
|| 'All' ) )
--AND (hp.party_id in (:P_PARTY_NAME) or 'All' in (:P_PARTY_NAME || 'All'))
AND ACRA.pay_from_customer = HCA.cust_account_id
AND HCA.party_id = HP.party_id
AND ( hp.party_id IN ( :P_PARTY_NAME )
OR 'All' IN ( :P_PARTY_NAME
|| 'All' ) )
--AND ((rctt.Name) IN (:P_INVOICE_TYPE) OR 'All' IN (:P_INVOICE_TYPE || 'All'))
) DT
ORDER BY customer_name,
flag,
trx_date,
trx_number
No comments:
Post a Comment