[Home] [Help]
| Product: | AR - Receivables |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PS.ROWID
, PS.CUSTOMER_TRX_ID
, PS.CUST_TRX_TYPE_ID
, PS.CUSTOMER_ID
, DED.ASSIGNED_TO_SALESREP_ID
, DED.ASSIGNED_TO_COLLECTOR_ID
, DED.DEDUCTN_ASSGNMT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PS.TRX_NUMBER
, CTT.NAME
, CTT.GL_ID_REC
, PS.AMOUNT_DUE_REMAINING
, DECODE(ASSIGNED_TO_SALESREP_ID
, NULL
, DECODE(ASSIGNED_TO_COLLECTOR_ID
, NULL
, NULL
, COL2.NAME)
, SREP2.NAME)
, DECODE(TRANSFER_TO_SALESREP_ID
, NULL
, DECODE(TRANSFER_TO_COLLECTOR_ID
, NULL
, NULL
, COL.NAME)
, SREP.NAME)
, CTX.TRX_DATE
, DECODE(PS.STATUS
, 'OP'
, 'OPEN'
, 'CL'
, 'CLOSED'
, 'PEN'
, 'PENDING'
, 'VD'
, 'VOID'
, NULL)
, DED.DEDUCTN_TEXT
, DED.DATE_ASSIGNED
, DED.TRANSFER_TO_COLLECTOR_ID
, DED.TRANSFER_TO_SALESREP_ID
, PS.CUSTOMER_SITE_USE_ID
, PS.GL_DATE
, PS.ORG_ID
, CTX.COMMENTS
, CTX.RELATED_CUSTOMER_TRX_ID
, CTL.CUSTOMER_TRX_LINE_ID
, PS.PAYMENT_SCHEDULE_ID
, CTX.INVOICE_CURRENCY_CODE
, CTX.EXCHANGE_RATE_TYPE
, CTX.EXCHANGE_RATE
, CTX.EXCHANGE_DATE
, CTX.BATCH_SOURCE_ID
, CTX.CREATION_DATE
, CTX.CREATED_BY
, CTX.LAST_UPDATE_DATE
, CTX.LAST_UPDATED_BY
, SITE.LOCATION
, CTX.ATTRIBUTE_CATEGORY
, CTX.ATTRIBUTE1
, CTX.ATTRIBUTE2
, CTX.ATTRIBUTE3
, CTX.ATTRIBUTE4
, CTX.ATTRIBUTE5
, CTX.ATTRIBUTE6
, CTX.ATTRIBUTE7
, CTX.ATTRIBUTE8
, CTX.ATTRIBUTE9
, CTX.ATTRIBUTE10
, CTX.ATTRIBUTE11
, CTX.ATTRIBUTE12
, CTX.ATTRIBUTE13
, CTX.ATTRIBUTE14
, CTX.ATTRIBUTE15
FROM AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_COLLECTORS COL
, AR_COLLECTORS COL2
, RA_CUST_TRX_TYPES CTT
, RA_SALESREPS SREP2
, RA_SALESREPS SREP
, HZ_CUST_SITE_USES SITE
, AR_TA_DEDN_ASSGNS DED
, RA_CUSTOMER_TRX CTX
, RA_CUSTOMER_TRX_LINES CTL
WHERE CTT.TYPE IN ('DM'
, 'CM')
AND CTT.ATTRIBUTE10 IN ('DED'
, 'UCD')
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND PS.CUSTOMER_TRX_ID = CTX.CUSTOMER_TRX_ID (+)AND PS.CUSTOMER_TRX_ID = DED.CUSTOMER_TRX_ID
AND DED.TRANSFER_TO_SALESREP_ID IS NULL
AND DED.TRANSFER_TO_COLLECTOR_ID IS NULL
AND PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PS.CUSTOMER_SITE_USE_ID = SITE.SITE_USE_ID
AND CTX.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
AND DED.ASSIGNED_TO_SALESREP_ID = SREP2.SALESREP_ID(+)
AND DED.ASSIGNED_TO_COLLECTOR_ID = COL2.COLLECTOR_ID(+)
AND DED.TRANSFER_TO_SALESREP_ID = SREP.SALESREP_ID(+)
AND DED.TRANSFER_TO_COLLECTOR_ID = COL.COLLECTOR_ID(+)