Product: | AR - Receivables |
---|---|
Description: | (Release 11.5 Only) |
Implementation/DBA Data: | APPS.AR_CORRESPONDENCES_V |
SELECT CPS.ROWID
, C.CORRESPONDENCE_DATE
, DLS.NAME
, DL.LETTER_NAME
, SUM(CPS.AMOUNT_DUE_REMAINING)
, SUM(CPS.AMOUNT_ACCRUE)
, SUB.LOCATION
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.CUST_ACCOUNT_ID
, SUB.LOCATION
, C.CORRESPONDENCE_ID
, PS.INVOICE_CURRENCY_CODE
, C.PURGED_CHILDREN_FLAG
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
, C.CREATED_BY
, C.CREATION_DATE
FROM AR_CORRESPONDENCES C
, AR_DUNNING_LETTER_SETS DLS
, AR_DUNNING_LETTERS DL
, AR_CORRESPONDENCE_PAY_SCHED CPS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SUB
, AR_PAYMENT_SCHEDULES PS
, AR_LOOKUPS L
WHERE DLS.DUNNING_LETTER_SET_ID = C.REFERENCE1
AND DL.DUNNING_LETTER_ID = C.REFERENCE2
AND CPS.CORRESPONDENCE_ID(+) = C.CORRESPONDENCE_ID
AND PS.PAYMENT_SCHEDULE_ID = CPS.PAYMENT_SCHEDULE_ID
AND PS.CLASS = L.LOOKUP_CODE
AND L.LOOKUP_TYPE = 'INV/CM'
AND CUST_ACCT.CUST_ACCOUNT_ID = C.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SUB.SITE_USE_ID = C.SITE_USE_ID
AND SUB.SITE_USE_CODE IN ('BILL_TO'
, 'DUN'
, 'DRAWEE') /* 18-MAY-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND C.PRELIMINARY_FLAG = 'N' GROUP BY CPS.ROWID
, C.CORRESPONDENCE_DATE
, DLS.NAME
, DL.LETTER_NAME
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.CUST_ACCOUNT_ID
, SUB.LOCATION
, C.CORRESPONDENCE_ID
, PS.INVOICE_CURRENCY_CODE
, C.PURGED_CHILDREN_FLAG
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
, C.CREATED_BY
, C.CREATION_DATE