eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.ECE_CDMO_HEADER_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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