Friday 24 January 2014

Billing and receipts history report formated

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)

No comments:

Post a Comment