VIEW: APPS.IEX_OPEN_LINES_V

Source

View Text - Preformatted

SELECT 'TRANSACTION' REC_TYPE, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, substrb(PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME, PARTY.PARTY_NUMBER CUSTOMER_NUMBER, CONS.CONS_BILLING_NUMBER BILLING_NUMBER, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, to_number(null) CASH_RECEIPT_ID, to_number(null) CLAIM_ID, to_number(null) ROOT_CLAIM_ID, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, CT.PURCHASE_ORDER PURCHASE_ORDER, CT.WAYBILL_NUMBER WAYBILL_NUMBER, decode(CT.INTERFACE_HEADER_CONTEXT, 'CLAIM', CT.INTERFACE_HEADER_ATTRIBUTE1, null) CLAIM_NUMBER, decode(CT.INTERFACE_HEADER_CONTEXT, 'CLAIM', CT.INTERFACE_HEADER_ATTRIBUTE6, ARPT_SQL_FUNC_UTIL.get_lookup_meaning(decode(PS.CLASS,'CM','CREDIT_MEMO_REASON','INVOICING_REASON'),reason_code)) CUSTOMER_REASON, nvl(decode(CT.INTERFACE_HEADER_CONTEXT, 'CLAIM', CT.INTERFACE_HEADER_ATTRIBUTE5, null), CT.CT_REFERENCE) CUSTOMER_REFERENCE, decode(CT.INTERFACE_HEADER_CONTEXT, 'CLAIM', CT.INTERFACE_HEADER_ATTRIBUTE7 , ARPT_SQL_FUNC_UTIL.get_lookup_meaning(decode(PS.CLASS,'CM','CREDIT_MEMO_REASON','INVOICING_REASON'),reason_code) ) CLAIM_REASON, CT.CT_REFERENCE REFERENCE, PS.CLASS DOC_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CCM', PS.CLASS) DOC_DESC, PS.TRX_NUMBER DOC_NUMBER, PS.DUE_DATE DUE_DATE, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER, TS.NAME TERMS_NAME, TS.DESCRIPTION TERMS_DESCRIPTION, PS.AMOUNT_DUE_ORIGINAL AMOUNT, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE, PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE, to_number(null) CLAIM_AMOUNT, (PS.DISCOUNT_TAKEN_EARNED+PS.DISCOUNT_TAKEN_UNEARNED) DISCOUNT_AMOUNT, DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE, PS.TRX_DATE DOC_DATE, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE, GLSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE, PS.EXCHANGE_RATE EXCHANGE_RATE, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, CT.ATTRIBUTE1 ATTRIBUTE1, CT.ATTRIBUTE2 ATTRIBUTE2, CT.ATTRIBUTE3 ATTRIBUTE3, CT.ATTRIBUTE4 ATTRIBUTE4, CT.ATTRIBUTE5 ATTRIBUTE5, CT.ATTRIBUTE6 ATTRIBUTE6, CT.ATTRIBUTE7 ATTRIBUTE7, CT.ATTRIBUTE8 ATTRIBUTE8, CT.ATTRIBUTE9 ATTRIBUTE9, CT.ATTRIBUTE10 ATTRIBUTE10, CT.ATTRIBUTE11 ATTRIBUTE11, CT.ATTRIBUTE12 ATTRIBUTE12, CT.ATTRIBUTE13 ATTRIBUTE13, CT.ATTRIBUTE14 ATTRIBUTE14, CT.ATTRIBUTE15 ATTRIBUTE15, ps.org_id ORG_ID, hu.name OPERATING_UNIT FROM ra_customer_trx CT , ar_payment_schedules PS , hz_cust_accounts HCA , hz_parties PARTY , ar_cons_inv CONS , ra_terms TS , ar_system_parameters ARSYS , gl_sets_of_books GLSOB, hr_operating_units hu WHERE HCA.CUST_ACCOUNT_ID = PS.CUSTOMER_ID AND HCA.PARTY_ID = PARTY.PARTY_ID AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID AND PS.CLASS IN ('INV','CB','DM','CM','GUAR','DEP','BR') AND PS.STATUS = 'OP' AND nvl(PS.ACTIVE_CLAIM_FLAG, 'N') <> 'Y' AND PS.CONS_INV_ID = CONS.CONS_INV_ID(+) AND PS.TERM_ID = TS.TERM_ID(+) AND ARSYS.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID AND ARSYS.ORG_ID=PS.ORG_ID AND HU.ORGANIZATION_ID = PS.ORG_ID UNION ALL SELECT 'RECEIPT' REC_TYPE, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, substrb(PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME, PARTY.PARTY_NUMBER CUSTOMER_NUMBER, null BILLING_NUMBER, to_number(null) CUSTOMER_TRX_ID, to_number(null) PAYMENT_SCHEDULE_ID, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID, to_number(null) CLAIM_ID, to_number(null) ROOT_CLAIM_ID, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, null PURCHASE_ORDER, null WAYBILL_NUMBER, null CLAIM_NUMBER, NVL(DECODE(RA.STATUS,'UNAPP',NULL,RA.CUSTOMER_REASON),NULL) CUSTOMER_REASON, NVL(DECODE(RA.STATUS,'UNAPP',NULL,RA.CUSTOMER_REFERENCE),NULL) CUSTOMER_REFERENCE, null CLAIM_REASON, null REFERENCE, RA.STATUS DOC_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('PAYMENT_TYPE', RA.STATUS) DOC_DESC, CR.RECEIPT_NUMBER DOC_NUMBER, PS.DUE_DATE DUE_DATE, to_number(null) TERMS_SEQUENCE_NUMBER, null TERMS_NAME, null TERMS_DESCRIPTION, to_number(null) AMOUNT, -sum(RA.AMOUNT_APPLIED) AMOUNT_DUE, -sum(RA.ACCTD_AMOUNT_APPLIED_FROM) ACCTD_ACOUNT_DUE, to_number(null) CLAIM_AMOUNT, to_number(null) DISCOUNT_AMOUNT, to_number(null) DAYS_PAST_DUE, CR.RECEIPT_DATE DOC_DATE, to_number(null) AMOUNT_IN_DISPUTE, CR.CURRENCY_CODE CURRENCY_CODE, GLSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE, CR.EXCHANGE_RATE EXCHANGE_RATE, CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, CR.ATTRIBUTE1 ATTRIBUTE1, CR.ATTRIBUTE2 ATTRIBUTE2, CR.ATTRIBUTE3 ATTRIBUTE3, CR.ATTRIBUTE4 ATTRIBUTE4, CR.ATTRIBUTE5 ATTRIBUTE5, CR.ATTRIBUTE6 ATTRIBUTE6, CR.ATTRIBUTE7 ATTRIBUTE7, CR.ATTRIBUTE8 ATTRIBUTE8, CR.ATTRIBUTE9 ATTRIBUTE9, CR.ATTRIBUTE10 ATTRIBUTE10, CR.ATTRIBUTE11 ATTRIBUTE11, CR.ATTRIBUTE12 ATTRIBUTE12, CR.ATTRIBUTE13 ATTRIBUTE13, CR.ATTRIBUTE14 ATTRIBUTE14, CR.ATTRIBUTE15 ATTRIBUTE15, ps.org_id ORG_ID, hu.name OPERATING_UNIT FROM ar_cash_receipts CR , ar_payment_schedules PS , ar_receivable_applications RA , hz_cust_accounts HCA , hz_parties PARTY , ar_system_parameters ARSYS , gl_sets_of_books GLSOB, hr_operating_units hu WHERE HCA.CUST_ACCOUNT_ID = CR.PAY_FROM_CUSTOMER AND HCA.PARTY_ID = PARTY.PARTY_ID AND CR.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID AND ( (RA.STATUS = 'UNAPP') OR (RA.STATUS = 'ACC' AND RA.DISPLAY = 'Y') ) AND CR.CASH_RECEIPT_ID = PS.CASH_RECEIPT_ID AND PS.CLASS = 'PMT' AND ARSYS.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID AND ARSYS.ORG_ID=PS.ORG_ID AND HU.ORGANIZATION_ID = PS.ORG_ID GROUP BY HCA.ACCOUNT_NUMBER ,HCA.CUST_ACCOUNT_ID ,substrb(PARTY.PARTY_NAME,1,50) ,PARTY.PARTY_NUMBER ,CR.CASH_RECEIPT_ID ,CR.DOC_SEQUENCE_VALUE ,CR.RECEIPT_NUMBER ,CR.RECEIPT_DATE ,CR.CURRENCY_CODE ,CR.EXCHANGE_RATE ,CR.ATTRIBUTE_CATEGORY ,CR.ATTRIBUTE1 ,CR.ATTRIBUTE2 ,CR.ATTRIBUTE3 ,CR.ATTRIBUTE4 ,CR.ATTRIBUTE5 ,CR.ATTRIBUTE6 ,CR.ATTRIBUTE7 ,CR.ATTRIBUTE8 ,CR.ATTRIBUTE9 ,CR.ATTRIBUTE10 ,CR.ATTRIBUTE11 ,CR.ATTRIBUTE12 ,CR.ATTRIBUTE13 ,CR.ATTRIBUTE14 ,CR.ATTRIBUTE15 ,PS.DUE_DATE ,RA.STATUS ,decode(RA.STATUS,'UNAPP',RA.STATUS,'ACC',RA.RECEIVABLE_APPLICATION_ID) , NVL(DECODE(RA.STATUS,'UNAPP',NULL,RA.CUSTOMER_REASON),NULL) , NVL(DECODE(RA.STATUS,'UNAPP',NULL,RA.CUSTOMER_REFERENCE),NULL), GLSOB.CURRENCY_CODE,PS.ORG_ID,HU.NAME HAVING sum(RA.AMOUNT_APPLIED) <> 0 UNION ALL SELECT decode(CT.CUSTOMER_TRX_ID, null, 'CLAIM', 'TRANSACTION') REC_TYPE, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, substrb(PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME, PARTY.PARTY_NUMBER CUSTOMER_NUMBER, CONS.CONS_BILLING_NUMBER BILLING_NUMBER, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, to_number(null) CASH_RECEIPT_ID, OCA.CLAIM_ID CLAIM_ID, OCA.ROOT_CLAIM_ID ROOT_CLAIM_ID, decode(CT.CUSTOMER_TRX_ID, null, OCA.DOC_SEQUENCE_VALUE, CT.DOC_SEQUENCE_VALUE) DOC_SEQUENCE_VALUE, CT.PURCHASE_ORDER PURCHASE_ORDER, CT.WAYBILL_NUMBER WAYBILL_NUMBER, OCA.CLAIM_NUMBER CLAIM_NUMBER, TO_CHAR(OCA.CUSTOMER_REASON) CUSTOMER_REASON , nvl(OCA.CUSTOMER_REF_NUMBER, nvl(decode(CT.INTERFACE_HEADER_CONTEXT, 'CLAIM', CT.INTERFACE_HEADER_ATTRIBUTE5, null), CT.CT_REFERENCE)) CUSTOMER_REFERENCE, ORC.NAME CLAIM_REASON, CT.CT_REFERENCE REFERENCE, 'CLAIM' doc_type, 'Claim Investigation' doc_desc, decode(CT.CUSTOMER_TRX_ID, null, OCA.RECEIPT_NUMBER, PS.TRX_NUMBER) DOC_NUMBER, decode(CT.CUSTOMER_TRX_ID, null, OCA.DUE_DATE, PS.DUE_DATE) DUE_DATE, decode(CT.CUSTOMER_TRX_ID, null, to_number(null), PS.TERMS_SEQUENCE_NUMBER) TERMS_SEQUENCE_NUMBER, TS.NAME TERMS_NAME, TS.DESCRIPTION TERMS_DESCRIPTION, PS.AMOUNT_DUE_ORIGINAL AMOUNT, decode(CT.CUSTOMER_TRX_ID,null, (OCA.AMOUNT_REMAINING + OCA.AMOUNT_SETTLED), PS.AMOUNT_DUE_REMAINING) AMOUNT_DUE, decode(CT.CUSTOMER_TRX_ID, null, (OCA.ACCTD_AMOUNT_REMAINING + OCA.ACCTD_AMOUNT_SETTLED), PS.ACCTD_AMOUNT_DUE_REMAINING) ACCTD_AMOUNT_DUE, (OCA.AMOUNT_REMAINING + OCA.AMOUNT_SETTLED) CLAIM_AMOUNT, (PS.DISCOUNT_TAKEN_EARNED+PS.DISCOUNT_TAKEN_UNEARNED) DISCOUNT_AMOUNT, DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE, decode(CT.CUSTOMER_TRX_ID,null, OCA.CLAIM_DATE, PS.TRX_DATE) DOC_DATE, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE, decode(CT.CUSTOMER_TRX_ID, null, OCA.CURRENCY_CODE, PS.INVOICE_CURRENCY_CODE) CURRENCY_CODE, GLSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE, decode(CT.CUSTOMER_TRX_ID, null, OCA.EXCHANGE_RATE, PS.EXCHANGE_RATE) EXCHANGE_RATE, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE_CATEGORY, CT.ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE1, CT.ATTRIBUTE1) ATTRIBUTE1, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE2, CT.ATTRIBUTE2) ATTRIBUTE2, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE3, CT.ATTRIBUTE3) ATTRIBUTE3, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE4, CT.ATTRIBUTE4) ATTRIBUTE4, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE5, CT.ATTRIBUTE5) ATTRIBUTE5, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE6, CT.ATTRIBUTE6) ATTRIBUTE6, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE7, CT.ATTRIBUTE7) ATTRIBUTE7, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE8, CT.ATTRIBUTE8) ATTRIBUTE8, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE9, CT.ATTRIBUTE9) ATTRIBUTE9, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE10, CT.ATTRIBUTE10) ATTRIBUTE10, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE11, CT.ATTRIBUTE11) ATTRIBUTE11, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE12, CT.ATTRIBUTE12) ATTRIBUTE12, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE13, CT.ATTRIBUTE13) ATTRIBUTE13, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE14, CT.ATTRIBUTE14) ATTRIBUTE14, decode(CT.CUSTOMER_TRX_ID, null, OCA.DEDUCTION_ATTRIBUTE15, CT.ATTRIBUTE15) ATTRIBUTE15, ps.org_id ORG_ID, hu.name OPERATING_UNIT FROM ozf_claims OCA , ozf_reason_codes_vl ORC , ra_customer_trx CT , ar_payment_schedules PS , hz_cust_accounts HCA , hz_parties PARTY , ar_cons_inv CONS , ra_terms TS , ar_system_parameters ARSYS , gl_sets_of_books GLSOB, hr_operating_units hu WHERE HCA.CUST_ACCOUNT_ID = OCA.CUST_ACCOUNT_ID AND HCA.PARTY_ID = PARTY.PARTY_ID AND OCA.STATUS_CODE NOT IN ('CLOSED','CANCELLED') AND OCA.RECEIPT_ID IS NOT NULL AND OCA.REASON_CODE_ID = ORC.REASON_CODE_ID AND OCA.SOURCE_OBJECT_ID = CT.CUSTOMER_TRX_ID(+) AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID(+) AND PS.CONS_INV_ID = CONS.CONS_INV_ID(+) AND PS.TERM_ID = TS.TERM_ID(+) AND ARSYS.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID AND ARSYS.ORG_ID = OCA.LEGAL_ENTITY_ID AND HU.ORGANIZATION_ID = OCA.LEGAL_ENTITY_ID

View Text - HTML Formatted

SELECT 'TRANSACTION' REC_TYPE
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, CONS.CONS_BILLING_NUMBER BILLING_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, TO_NUMBER(NULL) CASH_RECEIPT_ID
, TO_NUMBER(NULL) CLAIM_ID
, TO_NUMBER(NULL) ROOT_CLAIM_ID
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, CT.PURCHASE_ORDER PURCHASE_ORDER
, CT.WAYBILL_NUMBER WAYBILL_NUMBER
, DECODE(CT.INTERFACE_HEADER_CONTEXT
, 'CLAIM'
, CT.INTERFACE_HEADER_ATTRIBUTE1
, NULL) CLAIM_NUMBER
, DECODE(CT.INTERFACE_HEADER_CONTEXT
, 'CLAIM'
, CT.INTERFACE_HEADER_ATTRIBUTE6
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING(DECODE(PS.CLASS
, 'CM'
, 'CREDIT_MEMO_REASON'
, 'INVOICING_REASON')
, REASON_CODE)) CUSTOMER_REASON
, NVL(DECODE(CT.INTERFACE_HEADER_CONTEXT
, 'CLAIM'
, CT.INTERFACE_HEADER_ATTRIBUTE5
, NULL)
, CT.CT_REFERENCE) CUSTOMER_REFERENCE
, DECODE(CT.INTERFACE_HEADER_CONTEXT
, 'CLAIM'
, CT.INTERFACE_HEADER_ATTRIBUTE7
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING(DECODE(PS.CLASS
, 'CM'
, 'CREDIT_MEMO_REASON'
, 'INVOICING_REASON')
, REASON_CODE) ) CLAIM_REASON
, CT.CT_REFERENCE REFERENCE
, PS.CLASS DOC_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CCM'
, PS.CLASS) DOC_DESC
, PS.TRX_NUMBER DOC_NUMBER
, PS.DUE_DATE DUE_DATE
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, TS.NAME TERMS_NAME
, TS.DESCRIPTION TERMS_DESCRIPTION
, PS.AMOUNT_DUE_ORIGINAL AMOUNT
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE
, PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE
, TO_NUMBER(NULL) CLAIM_AMOUNT
, (PS.DISCOUNT_TAKEN_EARNED+PS.DISCOUNT_TAKEN_UNEARNED) DISCOUNT_AMOUNT
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE
, PS.TRX_DATE DOC_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, GLSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE
, PS.EXCHANGE_RATE EXCHANGE_RATE
, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1 ATTRIBUTE1
, CT.ATTRIBUTE2 ATTRIBUTE2
, CT.ATTRIBUTE3 ATTRIBUTE3
, CT.ATTRIBUTE4 ATTRIBUTE4
, CT.ATTRIBUTE5 ATTRIBUTE5
, CT.ATTRIBUTE6 ATTRIBUTE6
, CT.ATTRIBUTE7 ATTRIBUTE7
, CT.ATTRIBUTE8 ATTRIBUTE8
, CT.ATTRIBUTE9 ATTRIBUTE9
, CT.ATTRIBUTE10 ATTRIBUTE10
, CT.ATTRIBUTE11 ATTRIBUTE11
, CT.ATTRIBUTE12 ATTRIBUTE12
, CT.ATTRIBUTE13 ATTRIBUTE13
, CT.ATTRIBUTE14 ATTRIBUTE14
, CT.ATTRIBUTE15 ATTRIBUTE15
, PS.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT
FROM RA_CUSTOMER_TRX CT
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES PARTY
, AR_CONS_INV CONS
, RA_TERMS TS
, AR_SYSTEM_PARAMETERS ARSYS
, GL_SETS_OF_BOOKS GLSOB
, HR_OPERATING_UNITS HU
WHERE HCA.CUST_ACCOUNT_ID = PS.CUSTOMER_ID
AND HCA.PARTY_ID = PARTY.PARTY_ID
AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CLASS IN ('INV'
, 'CB'
, 'DM'
, 'CM'
, 'GUAR'
, 'DEP'
, 'BR')
AND PS.STATUS = 'OP'
AND NVL(PS.ACTIVE_CLAIM_FLAG
, 'N') <> 'Y'
AND PS.CONS_INV_ID = CONS.CONS_INV_ID(+)
AND PS.TERM_ID = TS.TERM_ID(+)
AND ARSYS.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID
AND ARSYS.ORG_ID=PS.ORG_ID
AND HU.ORGANIZATION_ID = PS.ORG_ID UNION ALL SELECT 'RECEIPT' REC_TYPE
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, NULL BILLING_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, TO_NUMBER(NULL) PAYMENT_SCHEDULE_ID
, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, TO_NUMBER(NULL) CLAIM_ID
, TO_NUMBER(NULL) ROOT_CLAIM_ID
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, NULL PURCHASE_ORDER
, NULL WAYBILL_NUMBER
, NULL CLAIM_NUMBER
, NVL(DECODE(RA.STATUS
, 'UNAPP'
, NULL
, RA.CUSTOMER_REASON)
, NULL) CUSTOMER_REASON
, NVL(DECODE(RA.STATUS
, 'UNAPP'
, NULL
, RA.CUSTOMER_REFERENCE)
, NULL) CUSTOMER_REFERENCE
, NULL CLAIM_REASON
, NULL REFERENCE
, RA.STATUS DOC_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, RA.STATUS) DOC_DESC
, CR.RECEIPT_NUMBER DOC_NUMBER
, PS.DUE_DATE DUE_DATE
, TO_NUMBER(NULL) TERMS_SEQUENCE_NUMBER
, NULL TERMS_NAME
, NULL TERMS_DESCRIPTION
, TO_NUMBER(NULL) AMOUNT
, -SUM(RA.AMOUNT_APPLIED) AMOUNT_DUE
, -SUM(RA.ACCTD_AMOUNT_APPLIED_FROM) ACCTD_ACOUNT_DUE
, TO_NUMBER(NULL) CLAIM_AMOUNT
, TO_NUMBER(NULL) DISCOUNT_AMOUNT
, TO_NUMBER(NULL) DAYS_PAST_DUE
, CR.RECEIPT_DATE DOC_DATE
, TO_NUMBER(NULL) AMOUNT_IN_DISPUTE
, CR.CURRENCY_CODE CURRENCY_CODE
, GLSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE
, CR.EXCHANGE_RATE EXCHANGE_RATE
, CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CR.ATTRIBUTE1 ATTRIBUTE1
, CR.ATTRIBUTE2 ATTRIBUTE2
, CR.ATTRIBUTE3 ATTRIBUTE3
, CR.ATTRIBUTE4 ATTRIBUTE4
, CR.ATTRIBUTE5 ATTRIBUTE5
, CR.ATTRIBUTE6 ATTRIBUTE6
, CR.ATTRIBUTE7 ATTRIBUTE7
, CR.ATTRIBUTE8 ATTRIBUTE8
, CR.ATTRIBUTE9 ATTRIBUTE9
, CR.ATTRIBUTE10 ATTRIBUTE10
, CR.ATTRIBUTE11 ATTRIBUTE11
, CR.ATTRIBUTE12 ATTRIBUTE12
, CR.ATTRIBUTE13 ATTRIBUTE13
, CR.ATTRIBUTE14 ATTRIBUTE14
, CR.ATTRIBUTE15 ATTRIBUTE15
, PS.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT
FROM AR_CASH_RECEIPTS CR
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS RA
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES PARTY
, AR_SYSTEM_PARAMETERS ARSYS
, GL_SETS_OF_BOOKS GLSOB
, HR_OPERATING_UNITS HU
WHERE HCA.CUST_ACCOUNT_ID = CR.PAY_FROM_CUSTOMER
AND HCA.PARTY_ID = PARTY.PARTY_ID
AND CR.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID
AND ( (RA.STATUS = 'UNAPP') OR (RA.STATUS = 'ACC'
AND RA.DISPLAY = 'Y') )
AND CR.CASH_RECEIPT_ID = PS.CASH_RECEIPT_ID
AND PS.CLASS = 'PMT'
AND ARSYS.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID
AND ARSYS.ORG_ID=PS.ORG_ID
AND HU.ORGANIZATION_ID = PS.ORG_ID GROUP BY HCA.ACCOUNT_NUMBER
, HCA.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, PARTY.PARTY_NUMBER
, CR.CASH_RECEIPT_ID
, CR.DOC_SEQUENCE_VALUE
, CR.RECEIPT_NUMBER
, CR.RECEIPT_DATE
, CR.CURRENCY_CODE
, CR.EXCHANGE_RATE
, CR.ATTRIBUTE_CATEGORY
, CR.ATTRIBUTE1
, CR.ATTRIBUTE2
, CR.ATTRIBUTE3
, CR.ATTRIBUTE4
, CR.ATTRIBUTE5
, CR.ATTRIBUTE6
, CR.ATTRIBUTE7
, CR.ATTRIBUTE8
, CR.ATTRIBUTE9
, CR.ATTRIBUTE10
, CR.ATTRIBUTE11
, CR.ATTRIBUTE12
, CR.ATTRIBUTE13
, CR.ATTRIBUTE14
, CR.ATTRIBUTE15
, PS.DUE_DATE
, RA.STATUS
, DECODE(RA.STATUS
, 'UNAPP'
, RA.STATUS
, 'ACC'
, RA.RECEIVABLE_APPLICATION_ID)
, NVL(DECODE(RA.STATUS
, 'UNAPP'
, NULL
, RA.CUSTOMER_REASON)
, NULL)
, NVL(DECODE(RA.STATUS
, 'UNAPP'
, NULL
, RA.CUSTOMER_REFERENCE)
, NULL)
, GLSOB.CURRENCY_CODE
, PS.ORG_ID
, HU.NAME HAVING SUM(RA.AMOUNT_APPLIED) <> 0 UNION ALL SELECT DECODE(CT.CUSTOMER_TRX_ID
, NULL
, 'CLAIM'
, 'TRANSACTION') REC_TYPE
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, CONS.CONS_BILLING_NUMBER BILLING_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, TO_NUMBER(NULL) CASH_RECEIPT_ID
, OCA.CLAIM_ID CLAIM_ID
, OCA.ROOT_CLAIM_ID ROOT_CLAIM_ID
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DOC_SEQUENCE_VALUE
, CT.DOC_SEQUENCE_VALUE) DOC_SEQUENCE_VALUE
, CT.PURCHASE_ORDER PURCHASE_ORDER
, CT.WAYBILL_NUMBER WAYBILL_NUMBER
, OCA.CLAIM_NUMBER CLAIM_NUMBER
, TO_CHAR(OCA.CUSTOMER_REASON) CUSTOMER_REASON
, NVL(OCA.CUSTOMER_REF_NUMBER
, NVL(DECODE(CT.INTERFACE_HEADER_CONTEXT
, 'CLAIM'
, CT.INTERFACE_HEADER_ATTRIBUTE5
, NULL)
, CT.CT_REFERENCE)) CUSTOMER_REFERENCE
, ORC.NAME CLAIM_REASON
, CT.CT_REFERENCE REFERENCE
, 'CLAIM' DOC_TYPE
, 'CLAIM INVESTIGATION' DOC_DESC
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.RECEIPT_NUMBER
, PS.TRX_NUMBER) DOC_NUMBER
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DUE_DATE
, PS.DUE_DATE) DUE_DATE
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, TO_NUMBER(NULL)
, PS.TERMS_SEQUENCE_NUMBER) TERMS_SEQUENCE_NUMBER
, TS.NAME TERMS_NAME
, TS.DESCRIPTION TERMS_DESCRIPTION
, PS.AMOUNT_DUE_ORIGINAL AMOUNT
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, (OCA.AMOUNT_REMAINING + OCA.AMOUNT_SETTLED)
, PS.AMOUNT_DUE_REMAINING) AMOUNT_DUE
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, (OCA.ACCTD_AMOUNT_REMAINING + OCA.ACCTD_AMOUNT_SETTLED)
, PS.ACCTD_AMOUNT_DUE_REMAINING) ACCTD_AMOUNT_DUE
, (OCA.AMOUNT_REMAINING + OCA.AMOUNT_SETTLED) CLAIM_AMOUNT
, (PS.DISCOUNT_TAKEN_EARNED+PS.DISCOUNT_TAKEN_UNEARNED) DISCOUNT_AMOUNT
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.CLAIM_DATE
, PS.TRX_DATE) DOC_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.CURRENCY_CODE
, PS.INVOICE_CURRENCY_CODE) CURRENCY_CODE
, GLSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.EXCHANGE_RATE
, PS.EXCHANGE_RATE) EXCHANGE_RATE
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE1
, CT.ATTRIBUTE1) ATTRIBUTE1
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE2
, CT.ATTRIBUTE2) ATTRIBUTE2
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE3
, CT.ATTRIBUTE3) ATTRIBUTE3
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE4
, CT.ATTRIBUTE4) ATTRIBUTE4
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE5
, CT.ATTRIBUTE5) ATTRIBUTE5
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE6
, CT.ATTRIBUTE6) ATTRIBUTE6
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE7
, CT.ATTRIBUTE7) ATTRIBUTE7
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE8
, CT.ATTRIBUTE8) ATTRIBUTE8
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE9
, CT.ATTRIBUTE9) ATTRIBUTE9
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE10
, CT.ATTRIBUTE10) ATTRIBUTE10
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE11
, CT.ATTRIBUTE11) ATTRIBUTE11
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE12
, CT.ATTRIBUTE12) ATTRIBUTE12
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE13
, CT.ATTRIBUTE13) ATTRIBUTE13
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE14
, CT.ATTRIBUTE14) ATTRIBUTE14
, DECODE(CT.CUSTOMER_TRX_ID
, NULL
, OCA.DEDUCTION_ATTRIBUTE15
, CT.ATTRIBUTE15) ATTRIBUTE15
, PS.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT
FROM OZF_CLAIMS OCA
, OZF_REASON_CODES_VL ORC
, RA_CUSTOMER_TRX CT
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES PARTY
, AR_CONS_INV CONS
, RA_TERMS TS
, AR_SYSTEM_PARAMETERS ARSYS
, GL_SETS_OF_BOOKS GLSOB
, HR_OPERATING_UNITS HU
WHERE HCA.CUST_ACCOUNT_ID = OCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = PARTY.PARTY_ID
AND OCA.STATUS_CODE NOT IN ('CLOSED'
, 'CANCELLED')
AND OCA.RECEIPT_ID IS NOT NULL
AND OCA.REASON_CODE_ID = ORC.REASON_CODE_ID
AND OCA.SOURCE_OBJECT_ID = CT.CUSTOMER_TRX_ID(+)
AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID(+)
AND PS.CONS_INV_ID = CONS.CONS_INV_ID(+)
AND PS.TERM_ID = TS.TERM_ID(+)
AND ARSYS.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID
AND ARSYS.ORG_ID = OCA.LEGAL_ENTITY_ID
AND HU.ORGANIZATION_ID = OCA.LEGAL_ENTITY_ID