VIEW: APPS.IBE_PAYMENT_HEADER_V

Source

View Text - Preformatted

SELECT cr.cash_receipt_id , cr.receipt_number , acct.cust_account_id , cust.party_name , cr.amount , al_pay_type.meaning type , ABS(ps.amount_applied) , cr.receipt_date , ps.due_date , cr.currency_code currency_code , sum(decode(ra.status, 'UNAPP', nvl(ra.amount_applied, 0), 0)) unapplied_amount , cust.party_id party_id , cr.created_by , ps.org_id org_id FROM ar_lookups al_pay_type, hz_cust_accounts acct, hz_parties cust, ar_payment_schedules_all ps, ar_receivable_applications_all ra, ar_cash_receipts_all cr WHERE cr.pay_from_customer = acct.cust_account_id (+) AND acct.party_id = cust.party_id AND ps.cash_receipt_id (+) = cr.cash_receipt_id AND cr.cash_receipt_id = ra.cash_receipt_id(+) AND al_pay_type.lookup_type = 'PAYMENT_CATEGORY_TYPE' AND cr.type = al_pay_type.lookup_code GROUP BY cr.cash_receipt_id, cr.receipt_number, acct.cust_account_id, cust.party_name, cr.amount, al_pay_type.meaning, ps.amount_applied, cr.currency_code, cr.receipt_date, ps.due_date, cust.party_id, cr.created_by, ps.org_id

View Text - HTML Formatted

SELECT CR.CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER
, ACCT.CUST_ACCOUNT_ID
, CUST.PARTY_NAME
, CR.AMOUNT
, AL_PAY_TYPE.MEANING TYPE
, ABS(PS.AMOUNT_APPLIED)
, CR.RECEIPT_DATE
, PS.DUE_DATE
, CR.CURRENCY_CODE CURRENCY_CODE
, SUM(DECODE(RA.STATUS
, 'UNAPP'
, NVL(RA.AMOUNT_APPLIED
, 0)
, 0)) UNAPPLIED_AMOUNT
, CUST.PARTY_ID PARTY_ID
, CR.CREATED_BY
, PS.ORG_ID ORG_ID
FROM AR_LOOKUPS AL_PAY_TYPE
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, AR_PAYMENT_SCHEDULES_ALL PS
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
WHERE CR.PAY_FROM_CUSTOMER = ACCT.CUST_ACCOUNT_ID (+)
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID(+)
AND AL_PAY_TYPE.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND CR.TYPE = AL_PAY_TYPE.LOOKUP_CODE GROUP BY CR.CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER
, ACCT.CUST_ACCOUNT_ID
, CUST.PARTY_NAME
, CR.AMOUNT
, AL_PAY_TYPE.MEANING
, PS.AMOUNT_APPLIED
, CR.CURRENCY_CODE
, CR.RECEIPT_DATE
, PS.DUE_DATE
, CUST.PARTY_ID
, CR.CREATED_BY
, PS.ORG_ID