SELECT hp.party_name customer_name,
hcaa.account_number,
rcta.trx_number,
rctta.name type,
rcta.trx_date,
apsa.due_date,
apsa.amount_due_original invoice_amount,
apsa.amount_due_remaining balance_due,
acra.receipt_date,
acra.receipt_number,
acra.receipt_date - apsa.due_date days_paid,
acra.amount applied_amount,
hou.name operating_unit
FROM RA_CUSTOMER_TRX_ALL RCTA,
HZ_CUST_ACCOUNTS_ALL HCAA,
HZ_PARTIES HP,
RA_CUST_TRX_TYPES_ALL RCTTA,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPTS_ALL ACRA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
HR_OPERATING_UNITS HOU
WHERE rcta.trx_number ='12115'
AND rcta.complete_flag = 'Y'
AND rcta.bill_to_customer_id = hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND rcta.org_id = rctta.org_id
AND rcta.customer_trx_id = apsa.customer_trx_id
AND rcta.customer_trx_id = araa.applied_customer_trx_id(+)
AND araa.cash_receipt_id = acra.cash_receipt_id(+)
AND hou.organization_id = rcta.org_id
AND rcta.trx_date BETWEEN :P_FROM_TRX_DATE AND :P_TO_TRX_DATE
AND rcta.org_id = DECODE(:P_CROSS_OPERATING_UNIT,
'N',FND_PROFILE.VALUE('ORG_ID'),
'Y',rcta.org_id)
hcaa.account_number,
rcta.trx_number,
rctta.name type,
rcta.trx_date,
apsa.due_date,
apsa.amount_due_original invoice_amount,
apsa.amount_due_remaining balance_due,
acra.receipt_date,
acra.receipt_number,
acra.receipt_date - apsa.due_date days_paid,
acra.amount applied_amount,
hou.name operating_unit
FROM RA_CUSTOMER_TRX_ALL RCTA,
HZ_CUST_ACCOUNTS_ALL HCAA,
HZ_PARTIES HP,
RA_CUST_TRX_TYPES_ALL RCTTA,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPTS_ALL ACRA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
HR_OPERATING_UNITS HOU
WHERE rcta.trx_number ='12115'
AND rcta.complete_flag = 'Y'
AND rcta.bill_to_customer_id = hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND rcta.org_id = rctta.org_id
AND rcta.customer_trx_id = apsa.customer_trx_id
AND rcta.customer_trx_id = araa.applied_customer_trx_id(+)
AND araa.cash_receipt_id = acra.cash_receipt_id(+)
AND hou.organization_id = rcta.org_id
AND rcta.trx_date BETWEEN :P_FROM_TRX_DATE AND :P_TO_TRX_DATE
AND rcta.org_id = DECODE(:P_CROSS_OPERATING_UNIT,
'N',FND_PROFILE.VALUE('ORG_ID'),
'Y',rcta.org_id)
No comments:
Post a Comment