View: ECE_CDMO_HEADER_V

Product:	EC - e-Commerce Gateway
Description:	This view extracts header informationfor the outbound Credit/Debit Memo (812/CREADV/DEBADV) transaction.@rep:scope private@rep:product AR@rep:lifecycle active@rep:displayname Credit/Debit Memo Header View@rep:category BUSINESS_ENTITY AR_CRE
Implementation/DBA Data:	APPS.ECE_CDMO_HEADER_V

View Text

SELECT 'EDI' COMMUNICATION_METHOD
, ETD.TEST_FLAG TEST_FLAG
, ETD.DOCUMENT_ID TP_DOCUMENT_ID
, DECODE(RCTT.TYPE
, 'CM'
, DECODE(RCT1.PREVIOUS_CUSTOMER_TRX_ID
, NULL
, 'OACM'
, 'CM')
, RCTT.TYPE) DOCUMENT_TYPE
, /* RCTT.TYPE DOCUMENT_TYPE
, */ RCTT.NAME INVOICE_NAME
, TO_CHAR(NULL) TP_DOCUMENT_PURPOSE_CODE
, RCT1.TRX_NUMBER TP_DOCUMENT_CODE
, ETD.TRANSLATOR_CODE TP_TRANSLATOR_CODE
, CAS1.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, ETH.TP_DESCRIPTION TP_DESCRIPTION
, ETH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1
, ETH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2
, SYSDATE TRANSACTION_DATE
, ETD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY
, ETD.ATTRIBUTE1 TPD_ATTRIBUTE1
, ETD.ATTRIBUTE2 TPD_ATTRIBUTE2
, ETD.ATTRIBUTE3 TPD_ATTRIBUTE3
, ETD.ATTRIBUTE4 TPD_ATTRIBUTE4
, ETD.ATTRIBUTE5 TPD_ATTRIBUTE5
, CAS1.ORIG_SYSTEM_REFERENCE BILL_TO_CUSTOMER_CODE_INT
, PT1.PARTY_NAME BILL_TO_CUSTOMER_NAME
, LOC1.ADDRESS1 BILL_TO_ADDRESS1
, LOC1.ADDRESS2 BILL_TO_ADDRESS2
, LOC1.ADDRESS3 BILL_TO_ADDRESS3
, LOC1.ADDRESS4 BILL_TO_ADDRESS4
, LOC1.CITY BILL_TO_CITY
, LOC1.POSTAL_CODE BILL_TO_POSTAL_CODE
, LOC1.COUNTRY BILL_TO_COUNTRY
, LOC1.STATE BILL_TO_STATE
, LOC1.PROVINCE BILL_TO_PROVINCE
, LOC1.COUNTY BILL_TO_COUNTY
, PT1.SIC_CODE BILL_TO_CUSTOMER_SIC_CODE
, CA1.SALES_CHANNEL_CODE BILL_TO_CUSTOMER_SALES_CHANNEL
, PTS1.PERSON_LAST_NAME BILL_TO_CONTACT_LAST_NAME
, PTS1.PERSON_FIRST_NAME BILL_TO_CONTACT_FIRST_NAME
, OC1.JOB_TITLE BILL_TO_CONTACT_JOB_TITLE
, CA1.ATTRIBUTE_CATEGORY BILL_TO_CUSTOMER_ATT_CATEGORY
, CA1.ATTRIBUTE1 BILL_TO_CUSTOMER_ATTRIBUTE1
, CA1.ATTRIBUTE2 BILL_TO_CUSTOMER_ATTRIBUTE2
, CA1.ATTRIBUTE3 BILL_TO_CUSTOMER_ATTRIBUTE3
, CA1.ATTRIBUTE4 BILL_TO_CUSTOMER_ATTRIBUTE4
, CA1.ATTRIBUTE5 BILL_TO_CUSTOMER_ATTRIBUTE5
, CA1.ATTRIBUTE6 BILL_TO_CUSTOMER_ATTRIBUTE6
, CA1.ATTRIBUTE7 BILL_TO_CUSTOMER_ATTRIBUTE7
, CA1.ATTRIBUTE8 BILL_TO_CUSTOMER_ATTRIBUTE8
, CA1.ATTRIBUTE9 BILL_TO_CUSTOMER_ATTRIBUTE9
, CA1.ATTRIBUTE10 BILL_TO_CUSTOMER_ATTRIBUTE10
, CA1.ATTRIBUTE11 BILL_TO_CUSTOMER_ATTRIBUTE11
, CA1.ATTRIBUTE12 BILL_TO_CUSTOMER_ATTRIBUTE12
, CA1.ATTRIBUTE13 BILL_TO_CUSTOMER_ATTRIBUTE13
, CA1.ATTRIBUTE14 BILL_TO_CUSTOMER_ATTRIBUTE14
, CA1.ATTRIBUTE15 BILL_TO_CUSTOMER_ATTRIBUTE15
, CSU1.SITE_USE_CODE SITE_USE_CODE
, CSU1.ATTRIBUTE_CATEGORY BILL_TO_SITE_ATT_CATEGORY
, CSU1.ATTRIBUTE1 BILL_TO_SITE_ATTRIBUTE1
, CSU1.ATTRIBUTE2 BILL_TO_SITE_ATTRIBUTE2
, CSU1.ATTRIBUTE3 BILL_TO_SITE_ATTRIBUTE3
, CSU1.ATTRIBUTE4 BILL_TO_SITE_ATTRIBUTE4
, CSU1.ATTRIBUTE5 BILL_TO_SITE_ATTRIBUTE5
, CSU1.ATTRIBUTE6 BILL_TO_SITE_ATTRIBUTE6
, CSU1.ATTRIBUTE7 BILL_TO_SITE_ATTRIBUTE7
, CSU1.ATTRIBUTE8 BILL_TO_SITE_ATTRIBUTE8
, CSU1.ATTRIBUTE9 BILL_TO_SITE_ATTRIBUTE9
, CSU1.ATTRIBUTE10 BILL_TO_SITE_ATTRIBUTE10
, CSU1.ATTRIBUTE11 BILL_TO_SITE_ATTRIBUTE11
, CSU1.ATTRIBUTE12 BILL_TO_SITE_ATTRIBUTE12
, CSU1.ATTRIBUTE13 BILL_TO_SITE_ATTRIBUTE13
, CSU1.ATTRIBUTE14 BILL_TO_SITE_ATTRIBUTE14
, CSU1.ATTRIBUTE15 BILL_TO_SITE_ATTRIBUTE15
, CAS2.ORIG_SYSTEM_REFERENCE SHIP_TO_CUSTOMER_CODE_INT
, PT2.PARTY_NAME SHIP_TO_CUSTOMER_NAME
, LOC2.ADDRESS1 SHIP_TO_ADDRESS1
, LOC2.ADDRESS2 SHIP_TO_ADDRESS2
, LOC2.ADDRESS3 SHIP_TO_ADDRESS3
, LOC2.ADDRESS4 SHIP_TO_ADDRESS4
, LOC2.CITY SHIP_TO_CITY
, LOC2.POSTAL_CODE SHIP_TO_POSTAL_CODE
, LOC2.COUNTRY SHIP_TO_COUNTRY
, LOC2.STATE SHIP_TO_STATE
, LOC2.PROVINCE SHIP_TO_PROVINCE
, LOC2.COUNTY SHIP_TO_COUNTY
, PT2.SIC_CODE SHIP_TO_CUSTOMER_SIC_CODE
, CA2.SALES_CHANNEL_CODE SHIP_TO_CUSTOMER_SALES_CHANNEL
, PTS2.PERSON_LAST_NAME SHIP_TO_CONTACT_LAST_NAME
, PTS2.PERSON_FIRST_NAME SHIP_TO_CONTACT_FIRST_NAME
, OC2.JOB_TITLE SHIP_TO_CONTACT_JOB_TITLE
, CAS3.ORIG_SYSTEM_REFERENCE SOLD_TO_CUSTOMER_CODE_INT
, PT3.PARTY_NAME SOLD_TO_CUSTOMER_NAME
, LOC3.ADDRESS1 SOLD_TO_ADDRESS1
, LOC3.ADDRESS2 SOLD_TO_ADDRESS2
, LOC3.ADDRESS3 SOLD_TO_ADDRESS3
, LOC3.ADDRESS4 SOLD_TO_ADDRESS4
, LOC3.CITY SOLD_TO_CITY
, LOC3.POSTAL_CODE SOLD_TO_POSTAL_CODE
, LOC3.COUNTRY SOLD_TO_COUNTRY
, LOC3.STATE SOLD_TO_STATE
, LOC3.PROVINCE SOLD_TO_PROVINCE
, LOC3.COUNTY SOLD_TO_COUNTY
, PT3.SIC_CODE SOLD_TO_CUSTOMER_SIC_CODE
, CA3.SALES_CHANNEL_CODE SOLD_TO_CUSTOMER_SALES_CHANNEL
, PTS3.PERSON_LAST_NAME SOLD_TO_CONTACT_LAST_NAME
, PTS3.PERSON_FIRST_NAME SOLD_TO_CONTACT_FIRST_NAME
, OC3.JOB_TITLE SOLD_TO_CONTACT_JOB_TITLE
, RCT1.TRX_NUMBER TRANSACTION_NUMBER
, RCT2.TRX_NUMBER CREDITED_INVOICE_NUMBER
, RCT4.TRX_NUMBER REFERENCE_INVOICE_NUMBER
, RCT3.TRX_NUMBER PARENT_INVOICE_NUMBER
, RCT1.SHIP_DATE_ACTUAL SHIPMENT_DATE
, RCT1.PURCHASE_ORDER PURCHASE_ORDER_NUMBER
, RCT1.CREATION_DATE CREATION_DATE
, RCT1.PURCHASE_ORDER_REVISION PURCHASE_ORDER_REVISION_NUMBER
, RCT1.START_DATE_COMMITMENT COMMITMENT_START_DATE
, RCT1.PURCHASE_ORDER_DATE PURCHASE_ORDER_DATE
, RCT1.END_DATE_COMMITMENT COMMITMENT_END_DATE
, RCT1.TRX_DATE INV_TRANSACTION_DATE
, RCT1.LAST_UPDATE_DATE LAST_UPDATE_DATE
, DECODE(RCTT.ACCOUNTING_AFFECT_FLAG
, 'Y'
, APS.DUE_DATE
, DECODE (RTL.DUE_DAYS
, NULL
, NVL(RTL.DUE_DATE
, DECODE(LEAST(TO_NUMBER(TO_CHAR(RCT1.TRX_DATE
, 'DD'))
, NVL(RT.DUE_CUTOFF_DAY
, 32))
, RT.DUE_CUTOFF_DAY
, LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, RTL.DUE_MONTHS_FORWARD)) + LEAST(RTL.DUE_DAY_OF_MONTH
, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, RTL.DUE_MONTHS_FORWARD + 1))
, 'DD')))
, LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, (RTL.DUE_MONTHS_FORWARD - 1))) + LEAST(RTL.DUE_DAY_OF_MONTH
, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, RTL.DUE_MONTHS_FORWARD))
, 'DD')))))
, RCT1.TRX_DATE + RTL.DUE_DAYS)) DUE_DATE
, RCT1.REASON_CODE CREDIT_MEMO_REASON
, RT.PRINTING_LEAD_DAYS TRANSMISSION_LEAD_DAYS
, ABS.NAME TRANSACTION_SOURCE
, NVL(RTL.SEQUENCE_NUM
, 1) INSTALLMENT_NUMBER
, RCT1.WAYBILL_NUMBER SHIPMENT_WAYBILL_NUMBER
, RCT1.SHIP_VIA SHIP_VIA
, RCT1.FOB_POINT SHIPMENT_FOB_POINT
, RCT1.INVOICE_CURRENCY_CODE CURRENCY_CODE
, NVL(RCT1.EXCHANGE_RATE
, 1) CURRENCY_EXCHANGE_RATE
, GLB.CURRENCY_CODE BASE_CURRENCY_CODE
, RT.NAME PAYMENT_TERM_NAME
, DECODE(RS.SALESREP_ID
, -3
, NULL
, -2
, NULL
, -1
, NULL
, RS.NAME) PRIMARY_SALESREP_NAME
, RCT1.INTERNAL_NOTES COMMENTS
, ETH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY
, ETH.ATTRIBUTE1 TPH_ATTRIBUTE1
, ETH.ATTRIBUTE2 TPH_ATTRIBUTE2
, ETH.ATTRIBUTE3 TPH_ATTRIBUTE3
, ETH.ATTRIBUTE4 TPH_ATTRIBUTE4
, ETH.ATTRIBUTE5 TPH_ATTRIBUTE5
, ETH.ATTRIBUTE6 TPH_ATTRIBUTE6
, ETH.ATTRIBUTE7 TPH_ATTRIBUTE7
, ETH.ATTRIBUTE8 TPH_ATTRIBUTE8
, ETH.ATTRIBUTE9 TPH_ATTRIBUTE9
, ETH.ATTRIBUTE10 TPH_ATTRIBUTE10
, ETH.ATTRIBUTE11 TPH_ATTRIBUTE11
, ETH.ATTRIBUTE12 TPH_ATTRIBUTE12
, ETH.ATTRIBUTE13 TPH_ATTRIBUTE13
, ETH.ATTRIBUTE14 TPH_ATTRIBUTE14
, ETH.ATTRIBUTE15 TPH_ATTRIBUTE15
, CAS1.PARTY_SITE_ID BILL_TO_ADDRESS_ID
, CSU1.LOCATION BILL_TO_CUSTOMER_LOCATION
, PT1.PARTY_NUMBER BILL_TO_CUSTOMER_NUMBER
, CAS2.PARTY_SITE_ID SHIP_TO_ADDRESS_ID
, CSU2.LOCATION SHIP_TO_CUSTOMER_LOCATION
, PT2.PARTY_NUMBER SHIP_TO_CUSTOMER_NUMBER
, CAS3.PARTY_SITE_ID SOLD_TO_ADDRESS_ID
, CSU3.LOCATION SOLD_TO_CUSTOMER_LOCATION
, PT3.PARTY_NUMBER SOLD_TO_CUSTOMER_NUMBER
, RCT1.CUSTOMER_TRX_ID TRANSACTION_ID
, RT.TERM_ID PAYMENT_TERM_ID
, RCT1.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, RCT1.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, RCT1.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, RCT1.SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID
, RCT1.SOLD_TO_CUSTOMER_ID SOLD_TO_CUSTOMER_ID
, RCT1.SOLD_TO_SITE_USE_ID SOLD_TO_SITE_USE_ID
, RT.DUE_CUTOFF_DAY TERM_DUE_CUTOFF_DAY
, RTL.DUE_DATE TERM_DUE_DATE
, RTL.DUE_DAYS TERM_DUE_DAYS
, RTL.DUE_DAY_OF_MONTH TERM_DUE_DAY_OF_MONTH
, RTL.DUE_MONTHS_FORWARD TERM_DUE_MONTHS_FORWARD
, (NVL(RTL.RELATIVE_AMOUNT
, 0)/NVL(RT.BASE_AMOUNT
, 1)) * 100 TERM_DUE_PERCENT
, TO_CHAR(NULL) REMIT_TO_ADDRESS1
, TO_CHAR(NULL) REMIT_TO_ADDRESS2
, TO_CHAR(NULL) REMIT_TO_ADDRESS3
, TO_CHAR(NULL) REMIT_TO_ADDRESS4
, TO_CHAR(NULL) REMIT_TO_CITY
, TO_CHAR(NULL) REMIT_TO_COUNTY
, TO_CHAR(NULL) REMIT_TO_STATE
, TO_CHAR(NULL) REMIT_TO_PROVINCE
, TO_CHAR(NULL) REMIT_TO_COUNTRY
, TO_CHAR(NULL) REMIT_TO_POSTAL_CODE
, TO_CHAR(NULL) SHIP_FROM_CODE_INT
, TO_CHAR(NULL) SHIP_FROM_CODE_EXT
, TO_CHAR(NULL) BILL_TO_TP_REFERENCE_EXT1
, TO_CHAR(NULL) BILL_TO_TP_REFERENCE_EXT2
, TO_CHAR(NULL) SHIP_TO_TP_REFERENCE_EXT1
, TO_CHAR(NULL) SHIP_TO_TP_REFERENCE_EX2
, TO_CHAR(NULL) SOLD_TO_TP_REFERENCE_EXT1
, TO_CHAR(NULL) SOLD_TO_TP_REFERENCE_EXT2
, TO_CHAR(NULL) REMIT_TO_TP_REFERENCE_EXT1
, TO_CHAR(NULL) REMIT_TO_TP_REFERENCE_EXT2
, ETD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, APS.AMOUNT_DUE_ORIGINAL TOTAL_AMOUNT_DUE
, APS.AMOUNT_DUE_REMAINING TOTAL_AMOUNT_REMAINING
, APS.TAX_ORIGINAL AMOUNT_TAX_DUE
, APS.FREIGHT_ORIGINAL AMOUNT_FREIGHT_DUE
, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_DUE
, APS.RECEIVABLES_CHARGES_REMAINING RECEIVABLES_CHARGES_REMAINING
FROM HZ_PARTIES PT1
, HZ_CUST_ACCOUNTS CA1
, HZ_PARTIES PT2
, HZ_CUST_ACCOUNTS CA2
, HZ_PARTIES PT3
, HZ_CUST_ACCOUNTS CA3
, HZ_ORG_CONTACTS OC1
, HZ_RELATIONSHIPS PR1
, HZ_PARTIES PTS1
, HZ_ORG_CONTACTS OC2
, HZ_RELATIONSHIPS PR2
, HZ_PARTIES PTS2
, HZ_ORG_CONTACTS OC3
, HZ_RELATIONSHIPS PR3
, HZ_PARTIES PTS3
, HZ_CUST_ACCT_SITES_ALL CAS1
, HZ_PARTY_SITES PS1
, HZ_LOCATIONS LOC1
, HZ_CUST_ACCT_SITES_ALL CAS2
, HZ_PARTY_SITES PS2
, HZ_LOCATIONS LOC2
, HZ_CUST_ACCT_SITES_ALL CAS3
, HZ_PARTY_SITES PS3
, HZ_LOCATIONS LOC3
, HZ_CUST_SITE_USES_ALL CSU1
, HZ_CUST_SITE_USES_ALL CSU2
, HZ_CUST_SITE_USES_ALL CSU3
, AR_SYSTEM_PARAMETERS ASP
, GL_SETS_OF_BOOKS GLB
, RA_CUST_TRX_TYPES RCTT
, RA_SALESREPS RS
, RA_TERMS RT
, RA_TERMS_LINES RTL
, AR_PAYMENT_SCHEDULES APS
, AR_BATCH_SOURCES ABS
, ECE_TP_HEADERS ETH
, ECE_TP_DETAILS ETD
, RA_CUSTOMER_TRX RCT4
, RA_CUSTOMER_TRX RCT3
, RA_CUSTOMER_TRX RCT2
, RA_CUSTOMER_TRX RCT1
WHERE RCT1.COMPLETE_FLAG = 'Y'
AND RCT1.PRINTING_PENDING = 'Y'
AND RCT1.PRINTING_OPTION = 'PRI'
AND ((ETD.DOCUMENT_ID = 'CDMO')
AND RCTT.TYPE IN ('DM'
, 'CM'))
AND RCT1.BILL_TO_CUSTOMER_ID = CA1.CUST_ACCOUNT_ID
AND CA1.PARTY_ID = PT1.PARTY_ID
AND RCT1.BILL_TO_CONTACT_ID = OC1.ORG_CONTACT_ID (+)
AND OC1.PARTY_RELATIONSHIP_ID = PR1.RELATIONSHIP_ID (+)
AND NVL(PR1.SUBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(PR1.OBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(PR1.DIRECTIONAL_FLAG
, 'F') = 'F'
AND PR1.PARTY_ID = PTS1.PARTY_ID (+)
AND RCT1.BILL_TO_SITE_USE_ID = CSU1.SITE_USE_ID
AND CSU1.CUST_ACCT_SITE_ID = CAS1.CUST_ACCT_SITE_ID
AND CAS1.PARTY_SITE_ID = PS1.PARTY_SITE_ID
AND PS1.LOCATION_ID = LOC1.LOCATION_ID
AND RCT1.SHIP_TO_CUSTOMER_ID = CA2.CUST_ACCOUNT_ID (+)
AND CA2.PARTY_ID = PT2.PARTY_ID (+)
AND RCT1.SHIP_TO_CONTACT_ID = OC2.ORG_CONTACT_ID (+)
AND OC2.PARTY_RELATIONSHIP_ID = PR2.RELATIONSHIP_ID (+)
AND NVL(PR2.SUBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(PR2.OBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(PR2.DIRECTIONAL_FLAG
, 'F') = 'F'
AND PR2.PARTY_ID = PTS2.PARTY_ID (+)
AND RCT1.SHIP_TO_SITE_USE_ID = CSU2.SITE_USE_ID (+)
AND CSU2.CUST_ACCT_SITE_ID = CAS2.CUST_ACCT_SITE_ID (+)
AND CAS2.PARTY_SITE_ID = PS2.PARTY_SITE_ID (+)
AND PS2.LOCATION_ID = LOC2.LOCATION_ID (+)
AND RCT1.SOLD_TO_CUSTOMER_ID = CA3.CUST_ACCOUNT_ID (+)
AND CA3.PARTY_ID = PT3.PARTY_ID (+)
AND RCT1.SOLD_TO_CONTACT_ID = OC3.ORG_CONTACT_ID (+)
AND OC3.PARTY_RELATIONSHIP_ID = PR3.RELATIONSHIP_ID (+)
AND NVL(PR3.SUBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(PR3.OBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(PR3.DIRECTIONAL_FLAG
, 'F') = 'F'
AND PR3.PARTY_ID = PTS3.PARTY_ID (+)
AND RCT1.SOLD_TO_SITE_USE_ID = CSU3.SITE_USE_ID (+)
AND CSU3.CUST_ACCT_SITE_ID = CAS3.CUST_ACCT_SITE_ID (+)
AND CAS3.PARTY_SITE_ID = PS3.PARTY_SITE_ID (+)
AND PS3.LOCATION_ID = LOC3.LOCATION_ID (+)
AND ASP.SET_OF_BOOKS_ID = GLB.SET_OF_BOOKS_ID
AND RCT1.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCT1.PRIMARY_SALESREP_ID = RS.SALESREP_ID (+)
AND RCT1.PREVIOUS_CUSTOMER_TRX_ID = RCT2.CUSTOMER_TRX_ID (+)
AND RCT1.INITIAL_CUSTOMER_TRX_ID = RCT3.CUSTOMER_TRX_ID (+)
AND RCT1.RELATED_CUSTOMER_TRX_ID = RCT4.CUSTOMER_TRX_ID (+)
AND RCT1.TERM_ID = RT.TERM_ID (+)
AND RT.TERM_ID = RTL.TERM_ID (+)
AND RCT1.BATCH_SOURCE_ID = ABS.BATCH_SOURCE_ID (+)
AND NVL(RCT1.LAST_PRINTED_SEQUENCE_NUM
, 0) < NVL(RTL.SEQUENCE_NUM
, 1)
AND RCT1.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID (+)
AND CAS1.TP_HEADER_ID = ETH.TP_HEADER_ID
AND ETH.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ETD.DOCUMENT_ID = 'CDMO'
AND ETD.DOCUMENT_TYPE = RCTT.TYPE
AND ETD.EDI_FLAG = 'Y'
AND NVL(RCT1.EDI_PROCESSED_STATUS
, 'ED') IN ('ED'
, 'PR'
, 'EP')
AND NVL(RCT1.EDI_PROCESSED_FLAG
, 'N') = 'N'
AND ETD.DOCUMENT_TYPE = RCTT.TYPE
AND NVL(RTL.SEQUENCE_NUM
, 0) = (SELECT NVL(MIN( SEQUENCE_NUM)
, 0)
FROM RA_TERMS_LINES RTL1
WHERE RTL1.TERM_ID = RTL.TERM_ID )
AND NVL(APS.TERMS_SEQUENCE_NUMBER
, NVL(RTL.SEQUENCE_NUM
, 0)) = NVL(RTL.SEQUENCE_NUM
, NVL(APS.TERMS_SEQUENCE_NUMBER
, 0))
AND NVL(DECODE(RCTT.ACCOUNTING_AFFECT_FLAG
, 'Y'
, APS.DUE_DATE
, DECODE(RTL.DUE_DAYS
, NULL
, NVL(RTL.DUE_DATE
, DECODE(LEAST(TO_NUMBER(TO_CHAR(RCT1.TRX_DATE
, 'DD '))
, NVL(RT.DUE_CUTOFF_DAY
, 32))
, RT.DUE_CUTOFF_DAY
, LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, RTL.DUE_MONTHS_FORWARD)) + LEAST(RTL.DUE_DAY_OF_MONTH
, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, RTL.DUE_MONTHS_FORWARD + 1))
, 'DD')))
, LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, (RTL.DUE_MONTHS_FORWARD- 1))) + LEAST(RTL.DUE_DAY_OF_MONTH
, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(RCT1.TRX_DATE
, RTL.DUE_MONTHS_FORWARD))
, 'DD')))))
, RCT1.TRX_DATE + RTL.DUE_DAYS))
, SYSDATE) - NVL(RT.PRINTING_LEAD_DAYS
, 356) <= SYSDATE

Columns