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_INO_HEADER_V

Source

View Text - Preformatted


SELECT /*+ ordered */ /* index(rct1,RA_CUSTOMER_TRX_N11) */ /* leading(etd) */ 'EDI' COMMUNICATION_METHOD, ETD.TEST_FLAG Test_Flag, ETD.DOCUMENT_ID Tp_Document_ID, RCTT.TYPE Document_Type, RCTT.NAME IInvoice_Name, TO_CHAR(NULL) Tp_Document_Purpose_Code, RCT1.TRX_NUMBER TP_DOCUMENT_CODE, ETD.TRANSLATOR_CODE Tp_Translator_Code, RA1.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, ETD.DOCUMENT_STANDARD Document_Standard, RA1.ORIG_SYSTEM_REFERENCE Bill_To_Customer_Code_Int, substrb(HP1.PARTY_NAME,1,50) 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, DECODE(HP1.PARTY_TYPE, 'ORGANIZATION', HP1.SIC_CODE, NULL) Bill_To_Customer_SIC_Code, RC1.SALES_CHANNEL_CODE Bill_To_Customer_Sales_Channel, NULL Bill_To_Contact_Last_Name, NULL Bill_To_Contact_First_Name, NULL Bill_To_Contact_Job_Title, RC1.ATTRIBUTE_CATEGORY Bill_To_Customer_Att_Category, RC1.ATTRIBUTE1 Bill_To_Customer_Attribute1, RC1.ATTRIBUTE2 Bill_To_Customer_Attribute2, RC1.ATTRIBUTE3 Bill_To_Customer_Attribute3, RC1.ATTRIBUTE4 Bill_To_Customer_Attribute4, RC1.ATTRIBUTE5 Bill_To_Customer_Attribute5, RC1.ATTRIBUTE6 Bill_To_Customer_Attribute6, RC1.ATTRIBUTE7 Bill_To_Customer_Attribute7, RC1.ATTRIBUTE8 Bill_To_Customer_Attribute8, RC1.ATTRIBUTE9 Bill_To_Customer_Attribute9, RC1.ATTRIBUTE10 Bill_To_Customer_Attribute10, RC1.ATTRIBUTE11 Bill_To_Customer_Attribute11, RC1.ATTRIBUTE12 Bill_To_Customer_Attribute12, RC1.ATTRIBUTE13 Bill_To_Customer_Attribute13, RC1.ATTRIBUTE14 Bill_To_Customer_Attribute14, RC1.ATTRIBUTE15 Bill_To_Customer_Attribute15, RSU1.SITE_USE_CODE Site_Use_Code, RSU1.ATTRIBUTE_CATEGORY Bill_To_Site_Att_Category, RSU1.ATTRIBUTE1 Bill_To_Site_Attribute1, RSU1.ATTRIBUTE2 Bill_To_Site_Attribute2, RSU1.ATTRIBUTE3 Bill_To_Site_Attribute3, RSU1.ATTRIBUTE4 Bill_To_Site_Attribute4, RSU1.ATTRIBUTE5 Bill_To_Site_Attribute5, RSU1.ATTRIBUTE6 Bill_To_Site_Attribute6, RSU1.ATTRIBUTE7 Bill_To_Site_Attribute7, RSU1.ATTRIBUTE8 Bill_To_Site_Attribute8, RSU1.ATTRIBUTE9 Bill_To_Site_Attribute9, RSU1.ATTRIBUTE10 Bill_To_Site_Attribute10, RSU1.ATTRIBUTE11 Bill_To_Site_Attribute11, RSU1.ATTRIBUTE12 Bill_To_Site_Attribute12, RSU1.ATTRIBUTE13 Bill_To_Site_Attribute13, RSU1.ATTRIBUTE14 Bill_To_Site_Attribute14, RSU1.ATTRIBUTE15 Bill_To_Site_Attribute15, RA2.ORIG_SYSTEM_REFERENCE Ship_To_Customer_Code_Int, substrb(HP2.PARTY_NAME,1,50) 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, DECODE(HP2.PARTY_TYPE, 'ORGANIZATION', HP2.SIC_CODE, NULL) Ship_To_Customer_SIC_Code, RC2.SALES_CHANNEL_CODE Ship_To_Customer_Sales_Channel, NULL Ship_To_Contact_Last_Name, NULL Ship_To_Contact_First_Name, NULL Ship_To_Contact_Job_Title, RA3.ORIG_SYSTEM_REFERENCE Sold_To_Customer_Code_Int, substrb(HP3.PARTY_NAME,1,50) 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, DECODE(HP3.PARTY_TYPE, 'ORGANIZATION', HP3.SIC_CODE, NULL) Sold_To_Customer_SIC_Code, RC3.SALES_CHANNEL_CODE Sold_To_Customer_Sales_Channel, NULL Sold_To_Contact_Last_Name, NULL Sold_To_Contact_First_Name, NULL 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(RB.DUE_CUTOFF_DAY,32)), RB.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, RB.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, ece_ar_transaction.get_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, RA1.CUST_ACCT_SITE_ID Bill_To_Address_Id, RSU1.LOCATION Bill_To_CUSTOMER_Location, RC1.ACCOUNT_NUMBER Bill_To_Customer_Number, RA2.CUST_ACCT_SITE_ID Ship_To_Address_Id, RSU2.LOCATION Ship_To_CUSTOMER_Location, RC2.ACCOUNT_NUMBER Ship_To_Customer_Number, RA3.CUST_ACCT_SITE_ID Sold_To_Address_Id, RSU3.LOCATION Sold_To_CUSTOMER_Location, RC3.ACCOUNT_NUMBER Sold_To_Customer_Number, RCT1.CUSTOMER_TRX_ID Transaction_ID, RB.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, RB.DUE_CUTOFF_DAY Term_Due_Cutoff_Day, NVL( RTL.DUE_DATE, APS.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(RB.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, APS.AMOUNT_DUE_ORIGINAL TOTAL_AMOUNT_DUE, 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 RECEIVEABLES_CHARGES_REMAINING, NVL(RCT1.INTERFACE_HEADER_ATTRIBUTE8,NULL) BILL_OF_LADING_NUMBER, NVL(RCT1.INTERFACE_HEADER_ATTRIBUTE12,0) SHIPMENT_NUMBER, ETD.MAP_ID MAP_ID, RA2.ECE_TP_LOCATION_CODE SHIP_TO_EDI_LOCATION_CODE, RA3.ECE_TP_LOCATION_CODE SOLD_TO_EDI_LOCATION_CODE FROM ECE_TP_DETAILS ETD, RA_CUST_TRX_TYPES RCTT, HZ_CUST_ACCT_SITES_ALL RA1, ECE_TP_HEADERS ETH, HZ_CUST_ACCOUNTS RC1, RA_CUSTOMER_TRX RCT1 , RA_BATCH_SOURCES ABS, RA_SALESREPS_ALL RS, AR_PAYMENT_SCHEDULES APS, HZ_CUST_SITE_USES_ALL RSU1, HZ_CUST_SITE_USES_ALL RSU2, HZ_CUST_SITE_USES_ALL RSU3, RA_CUSTOMER_TRX RCT4, RA_CUSTOMER_TRX RCT3, RA_CUSTOMER_TRX RCT2, RA_TERMS_B RB, RA_TERMS_TL RT, RA_TERMS_LINES RTL, HZ_PARTIES HP1, HZ_PARTIES HP2, HZ_CUST_ACCT_SITES_ALL RA2, HZ_CUST_ACCOUNTS RC2, HZ_CUST_ACCT_SITES_ALL RA3, HZ_PARTIES HP3, HZ_CUST_ACCOUNTS RC3, HZ_PARTY_SITES HPS1, HZ_LOCATIONS LOC1, HZ_PARTY_SITES HPS2, HZ_LOCATIONS LOC2, HZ_PARTY_SITES HPS3, HZ_LOCATIONS LOC3 WHERE ETH.TP_HEADER_ID = ETD.TP_HEADER_ID AND ETD.DOCUMENT_ID = 'INO' AND ETD.EDI_FLAG = 'Y' AND RA1.TP_HEADER_ID = ETD.TP_HEADER_ID AND RA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID AND HPS1.LOCATION_ID = LOC1.LOCATION_ID AND RC1.CUST_ACCOUNT_ID = RA1.CUST_ACCOUNT_ID AND HP1.PARTY_ID = RC1.PARTY_ID AND RSU1.CUST_ACCT_SITE_ID = RA1.CUST_ACCT_SITE_ID AND RCT1.BILL_TO_CUSTOMER_ID = RC1.CUST_ACCOUNT_ID AND RCT1.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID AND RCT1.COMPLETE_FLAG = 'Y' AND RCT1.PRINTING_PENDING = 'Y' AND RCT1.PRINTING_OPTION = 'PRI' AND NVL(RCT1.EDI_PROCESSED_STATUS, 'ED') IN ('ED', 'PR', 'EP') AND NVL(RCT1.EDI_PROCESSED_FLAG, 'N') = 'N' AND RCTT.TYPE = ETD.DOCUMENT_TYPE AND RCT1.BILL_TO_SITE_USE_ID = RSU1.SITE_USE_ID AND RCT1.SHIP_TO_CUSTOMER_ID = RC2.CUST_ACCOUNT_ID (+) AND RC2.PARTY_ID = HP2.PARTY_ID (+) AND RCT1.SHIP_TO_SITE_USE_ID = RSU2.SITE_USE_ID (+) AND RSU2.CUST_ACCT_SITE_ID = RA2.CUST_ACCT_SITE_ID (+) AND RA2.PARTY_SITE_ID = HPS2.PARTY_SITE_ID(+) AND HPS2.LOCATION_ID = LOC2.LOCATION_ID (+) AND RCT1.SOLD_TO_CUSTOMER_ID = RC3.CUST_ACCOUNT_ID (+) AND RC3.PARTY_ID = HP3.PARTY_ID (+) AND RCT1.SOLD_TO_SITE_USE_ID = RSU3.SITE_USE_ID (+) AND RSU3.CUST_ACCT_SITE_ID = RA3.CUST_ACCT_SITE_ID (+) AND RA3.PARTY_SITE_ID = HPS3.PARTY_SITE_ID(+) AND HPS3.LOCATION_ID = LOC3.LOCATION_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 = RB.TERM_ID (+) AND RT.TERM_ID(+) = RB.TERM_ID AND RT.LANGUAGE(+) = userenv('LANG') AND RB.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 NVL(APS.TERMS_SEQUENCE_NUMBER, NVL(RTL.SEQUENCE_NUM,0))= NVL(RTL.SEQUENCE_NUM, NVL(APS.TERMS_SEQUENCE_NUMBER,0)) AND NVL(RS.ORG_ID(+), NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99)) = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99) 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(RB.DUE_CUTOFF_DAY,32)), RB.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(RB.PRINTING_LEAD_DAYS,99999) <= SYSDATE AND nvl(rtl.sequence_num,0) = (select nvl(min(sequence_num),0) from ra_terms_lines rtl1 where rtl1.term_id = rtl.term_id)

View Text - HTML Formatted

SELECT /*+ ORDERED */ /* INDEX(RCT1
, RA_CUSTOMER_TRX_N11) */ /* LEADING(ETD) */ 'EDI' COMMUNICATION_METHOD
, ETD.TEST_FLAG TEST_FLAG
, ETD.DOCUMENT_ID TP_DOCUMENT_ID
, RCTT.TYPE DOCUMENT_TYPE
, RCTT.NAME IINVOICE_NAME
, TO_CHAR(NULL) TP_DOCUMENT_PURPOSE_CODE
, RCT1.TRX_NUMBER TP_DOCUMENT_CODE
, ETD.TRANSLATOR_CODE TP_TRANSLATOR_CODE
, RA1.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
, ETD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, RA1.ORIG_SYSTEM_REFERENCE BILL_TO_CUSTOMER_CODE_INT
, SUBSTRB(HP1.PARTY_NAME
, 1
, 50) 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
, DECODE(HP1.PARTY_TYPE
, 'ORGANIZATION'
, HP1.SIC_CODE
, NULL) BILL_TO_CUSTOMER_SIC_CODE
, RC1.SALES_CHANNEL_CODE BILL_TO_CUSTOMER_SALES_CHANNEL
, NULL BILL_TO_CONTACT_LAST_NAME
, NULL BILL_TO_CONTACT_FIRST_NAME
, NULL BILL_TO_CONTACT_JOB_TITLE
, RC1.ATTRIBUTE_CATEGORY BILL_TO_CUSTOMER_ATT_CATEGORY
, RC1.ATTRIBUTE1 BILL_TO_CUSTOMER_ATTRIBUTE1
, RC1.ATTRIBUTE2 BILL_TO_CUSTOMER_ATTRIBUTE2
, RC1.ATTRIBUTE3 BILL_TO_CUSTOMER_ATTRIBUTE3
, RC1.ATTRIBUTE4 BILL_TO_CUSTOMER_ATTRIBUTE4
, RC1.ATTRIBUTE5 BILL_TO_CUSTOMER_ATTRIBUTE5
, RC1.ATTRIBUTE6 BILL_TO_CUSTOMER_ATTRIBUTE6
, RC1.ATTRIBUTE7 BILL_TO_CUSTOMER_ATTRIBUTE7
, RC1.ATTRIBUTE8 BILL_TO_CUSTOMER_ATTRIBUTE8
, RC1.ATTRIBUTE9 BILL_TO_CUSTOMER_ATTRIBUTE9
, RC1.ATTRIBUTE10 BILL_TO_CUSTOMER_ATTRIBUTE10
, RC1.ATTRIBUTE11 BILL_TO_CUSTOMER_ATTRIBUTE11
, RC1.ATTRIBUTE12 BILL_TO_CUSTOMER_ATTRIBUTE12
, RC1.ATTRIBUTE13 BILL_TO_CUSTOMER_ATTRIBUTE13
, RC1.ATTRIBUTE14 BILL_TO_CUSTOMER_ATTRIBUTE14
, RC1.ATTRIBUTE15 BILL_TO_CUSTOMER_ATTRIBUTE15
, RSU1.SITE_USE_CODE SITE_USE_CODE
, RSU1.ATTRIBUTE_CATEGORY BILL_TO_SITE_ATT_CATEGORY
, RSU1.ATTRIBUTE1 BILL_TO_SITE_ATTRIBUTE1
, RSU1.ATTRIBUTE2 BILL_TO_SITE_ATTRIBUTE2
, RSU1.ATTRIBUTE3 BILL_TO_SITE_ATTRIBUTE3
, RSU1.ATTRIBUTE4 BILL_TO_SITE_ATTRIBUTE4
, RSU1.ATTRIBUTE5 BILL_TO_SITE_ATTRIBUTE5
, RSU1.ATTRIBUTE6 BILL_TO_SITE_ATTRIBUTE6
, RSU1.ATTRIBUTE7 BILL_TO_SITE_ATTRIBUTE7
, RSU1.ATTRIBUTE8 BILL_TO_SITE_ATTRIBUTE8
, RSU1.ATTRIBUTE9 BILL_TO_SITE_ATTRIBUTE9
, RSU1.ATTRIBUTE10 BILL_TO_SITE_ATTRIBUTE10
, RSU1.ATTRIBUTE11 BILL_TO_SITE_ATTRIBUTE11
, RSU1.ATTRIBUTE12 BILL_TO_SITE_ATTRIBUTE12
, RSU1.ATTRIBUTE13 BILL_TO_SITE_ATTRIBUTE13
, RSU1.ATTRIBUTE14 BILL_TO_SITE_ATTRIBUTE14
, RSU1.ATTRIBUTE15 BILL_TO_SITE_ATTRIBUTE15
, RA2.ORIG_SYSTEM_REFERENCE SHIP_TO_CUSTOMER_CODE_INT
, SUBSTRB(HP2.PARTY_NAME
, 1
, 50) 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
, DECODE(HP2.PARTY_TYPE
, 'ORGANIZATION'
, HP2.SIC_CODE
, NULL) SHIP_TO_CUSTOMER_SIC_CODE
, RC2.SALES_CHANNEL_CODE SHIP_TO_CUSTOMER_SALES_CHANNEL
, NULL SHIP_TO_CONTACT_LAST_NAME
, NULL SHIP_TO_CONTACT_FIRST_NAME
, NULL SHIP_TO_CONTACT_JOB_TITLE
, RA3.ORIG_SYSTEM_REFERENCE SOLD_TO_CUSTOMER_CODE_INT
, SUBSTRB(HP3.PARTY_NAME
, 1
, 50) 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
, DECODE(HP3.PARTY_TYPE
, 'ORGANIZATION'
, HP3.SIC_CODE
, NULL) SOLD_TO_CUSTOMER_SIC_CODE
, RC3.SALES_CHANNEL_CODE SOLD_TO_CUSTOMER_SALES_CHANNEL
, NULL SOLD_TO_CONTACT_LAST_NAME
, NULL SOLD_TO_CONTACT_FIRST_NAME
, NULL 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(RB.DUE_CUTOFF_DAY
, 32))
, RB.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
, RB.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
, ECE_AR_TRANSACTION.GET_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
, RA1.CUST_ACCT_SITE_ID BILL_TO_ADDRESS_ID
, RSU1.LOCATION BILL_TO_CUSTOMER_LOCATION
, RC1.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, RA2.CUST_ACCT_SITE_ID SHIP_TO_ADDRESS_ID
, RSU2.LOCATION SHIP_TO_CUSTOMER_LOCATION
, RC2.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUMBER
, RA3.CUST_ACCT_SITE_ID SOLD_TO_ADDRESS_ID
, RSU3.LOCATION SOLD_TO_CUSTOMER_LOCATION
, RC3.ACCOUNT_NUMBER SOLD_TO_CUSTOMER_NUMBER
, RCT1.CUSTOMER_TRX_ID TRANSACTION_ID
, RB.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
, RB.DUE_CUTOFF_DAY TERM_DUE_CUTOFF_DAY
, NVL( RTL.DUE_DATE
, APS.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(RB.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
, APS.AMOUNT_DUE_ORIGINAL TOTAL_AMOUNT_DUE
, 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 RECEIVEABLES_CHARGES_REMAINING
, NVL(RCT1.INTERFACE_HEADER_ATTRIBUTE8
, NULL) BILL_OF_LADING_NUMBER
, NVL(RCT1.INTERFACE_HEADER_ATTRIBUTE12
, 0) SHIPMENT_NUMBER
, ETD.MAP_ID MAP_ID
, RA2.ECE_TP_LOCATION_CODE SHIP_TO_EDI_LOCATION_CODE
, RA3.ECE_TP_LOCATION_CODE SOLD_TO_EDI_LOCATION_CODE
FROM ECE_TP_DETAILS ETD
, RA_CUST_TRX_TYPES RCTT
, HZ_CUST_ACCT_SITES_ALL RA1
, ECE_TP_HEADERS ETH
, HZ_CUST_ACCOUNTS RC1
, RA_CUSTOMER_TRX RCT1
, RA_BATCH_SOURCES ABS
, RA_SALESREPS_ALL RS
, AR_PAYMENT_SCHEDULES APS
, HZ_CUST_SITE_USES_ALL RSU1
, HZ_CUST_SITE_USES_ALL RSU2
, HZ_CUST_SITE_USES_ALL RSU3
, RA_CUSTOMER_TRX RCT4
, RA_CUSTOMER_TRX RCT3
, RA_CUSTOMER_TRX RCT2
, RA_TERMS_B RB
, RA_TERMS_TL RT
, RA_TERMS_LINES RTL
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_CUST_ACCT_SITES_ALL RA2
, HZ_CUST_ACCOUNTS RC2
, HZ_CUST_ACCT_SITES_ALL RA3
, HZ_PARTIES HP3
, HZ_CUST_ACCOUNTS RC3
, HZ_PARTY_SITES HPS1
, HZ_LOCATIONS LOC1
, HZ_PARTY_SITES HPS2
, HZ_LOCATIONS LOC2
, HZ_PARTY_SITES HPS3
, HZ_LOCATIONS LOC3
WHERE ETH.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ETD.DOCUMENT_ID = 'INO'
AND ETD.EDI_FLAG = 'Y'
AND RA1.TP_HEADER_ID = ETD.TP_HEADER_ID
AND RA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID
AND HPS1.LOCATION_ID = LOC1.LOCATION_ID
AND RC1.CUST_ACCOUNT_ID = RA1.CUST_ACCOUNT_ID
AND HP1.PARTY_ID = RC1.PARTY_ID
AND RSU1.CUST_ACCT_SITE_ID = RA1.CUST_ACCT_SITE_ID
AND RCT1.BILL_TO_CUSTOMER_ID = RC1.CUST_ACCOUNT_ID
AND RCT1.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCT1.COMPLETE_FLAG = 'Y'
AND RCT1.PRINTING_PENDING = 'Y'
AND RCT1.PRINTING_OPTION = 'PRI'
AND NVL(RCT1.EDI_PROCESSED_STATUS
, 'ED') IN ('ED'
, 'PR'
, 'EP')
AND NVL(RCT1.EDI_PROCESSED_FLAG
, 'N') = 'N'
AND RCTT.TYPE = ETD.DOCUMENT_TYPE
AND RCT1.BILL_TO_SITE_USE_ID = RSU1.SITE_USE_ID
AND RCT1.SHIP_TO_CUSTOMER_ID = RC2.CUST_ACCOUNT_ID (+)
AND RC2.PARTY_ID = HP2.PARTY_ID (+)
AND RCT1.SHIP_TO_SITE_USE_ID = RSU2.SITE_USE_ID (+)
AND RSU2.CUST_ACCT_SITE_ID = RA2.CUST_ACCT_SITE_ID (+)
AND RA2.PARTY_SITE_ID = HPS2.PARTY_SITE_ID(+)
AND HPS2.LOCATION_ID = LOC2.LOCATION_ID (+)
AND RCT1.SOLD_TO_CUSTOMER_ID = RC3.CUST_ACCOUNT_ID (+)
AND RC3.PARTY_ID = HP3.PARTY_ID (+)
AND RCT1.SOLD_TO_SITE_USE_ID = RSU3.SITE_USE_ID (+)
AND RSU3.CUST_ACCT_SITE_ID = RA3.CUST_ACCT_SITE_ID (+)
AND RA3.PARTY_SITE_ID = HPS3.PARTY_SITE_ID(+)
AND HPS3.LOCATION_ID = LOC3.LOCATION_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 = RB.TERM_ID (+)
AND RT.TERM_ID(+) = RB.TERM_ID
AND RT.LANGUAGE(+) = USERENV('LANG')
AND RB.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 NVL(APS.TERMS_SEQUENCE_NUMBER
, NVL(RTL.SEQUENCE_NUM
, 0))= NVL(RTL.SEQUENCE_NUM
, NVL(APS.TERMS_SEQUENCE_NUMBER
, 0))
AND NVL(RS.ORG_ID(+)
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
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(RB.DUE_CUTOFF_DAY
, 32))
, RB.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(RB.PRINTING_LEAD_DAYS
, 99999) <= SYSDATE
AND NVL(RTL.SEQUENCE_NUM
, 0) = (SELECT NVL(MIN(SEQUENCE_NUM)
, 0)
FROM RA_TERMS_LINES RTL1
WHERE RTL1.TERM_ID = RTL.TERM_ID)