[Home] [Help]
SELECT hp.party_name party_name ,cm.interface_header_context cm_interface_header_context ,cm.interface_header_attribute1 cm_interface_header_attribute1 ,cm.interface_header_attribute2 cm_interface_header_attribute2 ,cm.bill_to_customer_id ar_bill_to_party_id ,cm.bill_to_site_use_id ar_bill_to_site_use_id ,cm.interface_header_attribute3 cm_interface_header_attribute3 ,cm.interface_header_attribute4 cm_interface_header_attribute4 ,cm.customer_trx_id cm_customer_trx_id ,cm.trx_date cm_trx_date ,cm.trx_number cm_trx_number ,cmps.status cm_trx_status ,cmps.class cm_trx_class_code ,l_cm_class.meaning cm_trx_class_name ,cmapp.apply_date cm_apply_date ,cmapp.amount_applied cm_amount_applied ,cmps.amount_due_original cm_amount_original ,cmps.amount_due_remaining cm_amount_remaining ,inv.customer_trx_id inv_customer_trx_id ,inv.trx_date inv_trx_date ,inv.trx_number inv_trx_number ,cmps.org_id org_id ,cmps.LAST_UPDATE_DATE LAST_UPDATE_DATE ,cmps.LAST_UPDATED_BY LAST_UPDATED_BY ,cmps.CREATION_DATE CREATION_DATE ,cmps.CREATED_BY CREATED_BY ,cmps.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN ,cmps.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID ,cmps.PROGRAM_ID PROGRAM_ID ,cmps.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE FROM ra_customer_trx_all cm, ar_payment_schedules_all cmps, ar_lookups l_cm_class, ar_receivable_applications_all cmapp, ra_customer_trx_all inv, hz_parties hp, hz_cust_accounts_all hca WHERE cmps.class = 'CM' and cmps.class = l_cm_class.lookup_code and l_cm_class.lookup_type = 'INV/CM' and cmps.customer_trx_id = cm.customer_trx_id and cmps.payment_schedule_id = cmapp.payment_schedule_id(+) and cmapp.applied_customer_trx_id = inv.customer_trx_id(+) and cmps.org_id = cm.org_id and cmps.org_id = cmapp.org_id(+) and cmapp.org_id = inv.org_id(+) and hp.party_id = hca.party_id and hca.orig_system_reference = 'EXCHANGE_CUST'||cm.interface_header_attribute1
SELECT HP.PARTY_NAME PARTY_NAME
, CM.INTERFACE_HEADER_CONTEXT CM_INTERFACE_HEADER_CONTEXT
, CM.INTERFACE_HEADER_ATTRIBUTE1 CM_INTERFACE_HEADER_ATTRIBUTE1
, CM.INTERFACE_HEADER_ATTRIBUTE2 CM_INTERFACE_HEADER_ATTRIBUTE2
, CM.BILL_TO_CUSTOMER_ID AR_BILL_TO_PARTY_ID
, CM.BILL_TO_SITE_USE_ID AR_BILL_TO_SITE_USE_ID
, CM.INTERFACE_HEADER_ATTRIBUTE3 CM_INTERFACE_HEADER_ATTRIBUTE3
, CM.INTERFACE_HEADER_ATTRIBUTE4 CM_INTERFACE_HEADER_ATTRIBUTE4
, CM.CUSTOMER_TRX_ID CM_CUSTOMER_TRX_ID
, CM.TRX_DATE CM_TRX_DATE
, CM.TRX_NUMBER CM_TRX_NUMBER
, CMPS.STATUS CM_TRX_STATUS
, CMPS.CLASS CM_TRX_CLASS_CODE
, L_CM_CLASS.MEANING CM_TRX_CLASS_NAME
, CMAPP.APPLY_DATE CM_APPLY_DATE
, CMAPP.AMOUNT_APPLIED CM_AMOUNT_APPLIED
, CMPS.AMOUNT_DUE_ORIGINAL CM_AMOUNT_ORIGINAL
, CMPS.AMOUNT_DUE_REMAINING CM_AMOUNT_REMAINING
, INV.CUSTOMER_TRX_ID INV_CUSTOMER_TRX_ID
, INV.TRX_DATE INV_TRX_DATE
, INV.TRX_NUMBER INV_TRX_NUMBER
, CMPS.ORG_ID ORG_ID
, CMPS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CMPS.LAST_UPDATED_BY LAST_UPDATED_BY
, CMPS.CREATION_DATE CREATION_DATE
, CMPS.CREATED_BY CREATED_BY
, CMPS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CMPS.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CMPS.PROGRAM_ID PROGRAM_ID
, CMPS.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
FROM RA_CUSTOMER_TRX_ALL CM
, AR_PAYMENT_SCHEDULES_ALL CMPS
, AR_LOOKUPS L_CM_CLASS
, AR_RECEIVABLE_APPLICATIONS_ALL CMAPP
, RA_CUSTOMER_TRX_ALL INV
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS_ALL HCA
WHERE CMPS.CLASS = 'CM'
AND CMPS.CLASS = L_CM_CLASS.LOOKUP_CODE
AND L_CM_CLASS.LOOKUP_TYPE = 'INV/CM'
AND CMPS.CUSTOMER_TRX_ID = CM.CUSTOMER_TRX_ID
AND CMPS.PAYMENT_SCHEDULE_ID = CMAPP.PAYMENT_SCHEDULE_ID(+)
AND CMAPP.APPLIED_CUSTOMER_TRX_ID = INV.CUSTOMER_TRX_ID(+)
AND CMPS.ORG_ID = CM.ORG_ID
AND CMPS.ORG_ID = CMAPP.ORG_ID(+)
AND CMAPP.ORG_ID = INV.ORG_ID(+)
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCA.ORIG_SYSTEM_REFERENCE = 'EXCHANGE_CUST'||CM.INTERFACE_HEADER_ATTRIBUTE1
|
|
|
|