VIEW: APPS.OEFV_ORDER_HEADERS

Source

View Text - Preformatted

SELECT HEADER.ORDER_NUMBER, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, PARTY.PARTY_NAME CUSTOMER, SUBSTRB(SOLDCONT2.PERSON_LAST_NAME,1,50) || DECODE(SOLDCONT2.PERSON_FIRST_NAME, NULL, NULL, ', '|| SUBSTRB(SOLDCONT2.PERSON_FIRST_NAME,1,50)) || DECODE(sold_arl.meaning,NULL, NULL, ' ' || sold_arl.meaning) CUSTOMER_CONTACT , soldcont6.email_address, DECODE(SOLDCONT5.phone_country_code, NULL, NULL, SOLDCONT5.phone_country_code || ' ') || DECODE(SOLDCONT5.phone_area_code, NULL, NULL, SOLDCONT5.phone_area_code || '-') || DECODE(SOLDCONT5.phone_number, NULL, NULL, SOLDCONT5.phone_number) CUSTOMER_CONTACT_PHONE_NUMBER, soldcont5.phone_extension, AGREE.NAME AGREEMENT, PL.NAME, SHIP_TO_ORG.LOCATION SHIP_TO_LOCATION, SHIP_TO_ADDR2.ADDRESS1 SHIP_TO_ADDRESS1, SHIP_TO_ADDR2.ADDRESS2 SHIP_TO_ADDRESS2, SHIP_TO_ADDR2.ADDRESS3 SHIP_TO_ADDRESS3, SHIP_TO_ADDR2.ADDRESS4 SHIP_TO_ADDRESS4, DECODE(SHIP_TO_ADDR2.CITY, NULL, NULL, SHIP_TO_ADDR2.CITY || ', ') || DECODE(SHIP_TO_ADDR2.STATE, NULL, NULL, SHIP_TO_ADDR2.STATE || ', ') || DECODE(SHIP_TO_ADDR2.POSTAL_CODE, NULL, NULL, SHIP_TO_ADDR2.POSTAL_CODE || ', ') || DECODE(SHIP_TO_ADDR2.COUNTRY, NULL, NULL, SHIP_TO_ADDR2.COUNTRY) SHIP_TO_CTY_ST_ZIP_CTRY, SUBSTRB(SHIPCONT2.PERSON_LAST_NAME,1,50) || DECODE(SHIPCONT2.PERSON_FIRST_NAME, NULL, NULL, ', '|| SUBSTRB(SHIPCONT2.PERSON_FIRST_NAME,1,50)) || DECODE(ship_arl.meaning,NULL, NULL, ' '|| ship_arl.meaning) SHIP_TO_CONTACT, SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM_ORG, OS.NAME ORDER_SOURCE, OT.NAME ORDER_TYPE, OE_BIS_SALESPERSON.GET_SALESPERSON_NAME(HEADER.SALESREP_ID), BILLORG.LOCATION BILL_TO, BILL_TO_ADDR2.ADDRESS1 BILL_TO_ADDRESS1, BILL_TO_ADDR2.ADDRESS2 BILL_TO_ADDRESS2, BILL_TO_ADDR2.ADDRESS3 BILL_TO_ADDRESS3, BILL_TO_ADDR2.ADDRESS4 BILL_TO_ADDRESS4, DECODE(BILL_TO_ADDR2.CITY, NULL, NULL, BILL_TO_ADDR2.CITY || ', ') || DECODE(BILL_TO_ADDR2.STATE, NULL, NULL, BILL_TO_ADDR2.STATE || ', ') || DECODE(BILL_TO_ADDR2.POSTAL_CODE, NULL, NULL, BILL_TO_ADDR2.POSTAL_CODE || ', ') || DECODE(BILL_TO_ADDR2.COUNTRY, NULL, NULL, BILL_TO_ADDR2.COUNTRY) BILL_TO_CTY_ST_ZIP_CTRY, SUBSTRB(BILLCONT2.PERSON_LAST_NAME,1,50) || DECODE(BILLCONT2.PERSON_FIRST_NAME, NULL, NULL, ', '|| SUBSTRB(BILLCONT2.PERSON_FIRST_NAME,1,50)) || DECODE(bill_arl.meaning,NULL, NULL, ' '||bill_arl.meaning) BILL_TO_CONTACT, TERM.NAME, INVRULE.NAME, HEADER.ORDERED_DATE, HEADER.CUST_PO_NUMBER, '_LA:HEADER.SALES_CHANNEL_CODE:OE_LOOKUPS:SALES_CHANNEL:MEANING', '_LA:HEADER.FLOW_STATUS_CODE:OE_LOOKUPS:FLOW_STATUS:MEANING', '_LA:HEADER.TAX_EXEMPT_FLAG:OE_LOOKUPS:YES_NO:MEANING' , '_LA:HEADER.TAX_EXEMPT_REASON_CODE:AR_LOOKUPS:TAX_REASON:MEANING' , HEADER.TAX_EXEMPT_NUMBER, HEADER.TRANSACTIONAL_CURR_CODE, HEADER.PAYMENT_TYPE_CODE, '_LA:HEADER.SHIPMENT_PRIORITY_CODE:OE_LOOKUPS:SHIPMENT_PRIORITY:MEANING' , '_LA:HEADER.FREIGHT_TERMS_CODE:OE_LOOKUPS:FREIGHT_TERMS:MEANING' , '_LA:HEADER.BOOKED_FLAG:OE_LOOKUPS:YES_NO:MEANING' , '_LA:HEADER.CANCELLED_FLAG:OE_LOOKUPS:YES_NO:MEANING' , HEADER.ORG_ID, HEADER.SOLD_FROM_ORG_ID, HEADER.HEADER_ID, HEADER.ORDER_TYPE_ID, HEADER.SOLD_TO_CONTACT_ID, HEADER.AGREEMENT_ID, HEADER.PRICE_LIST_ID, HEADER.SHIP_TO_ORG_ID, HEADER.SHIP_TO_CONTACT_ID, SUBSTRB(SHIP_PARTY.PARTY_NAME,1,50), SHIP_CUST_ACCT.CUST_ACCOUNT_ID, HEADER.SHIP_FROM_ORG_ID, HEADER.INVOICING_RULE_ID, HEADER.INVOICE_TO_ORG_ID, HEADER.INVOICE_TO_CONTACT_ID, SUBSTRB(BILL_PARTY.PARTY_NAME,1,50), BILL_CUST_ACCT.CUST_ACCOUNT_ID, HEADER.PAYMENT_TERM_ID, HEADER.ORDER_SOURCE_ID, HEADER.ORIG_SYS_DOCUMENT_REF, HEADER.SALESREP_ID, '_DF:ONT:OE_HEADER_ATTRIBUTES:HEADER', '_DF:ONT:OE_HEADER_GLOBAL_ATTRIBUTE:HEADER', '_DF:ONT:OE_HEADER_TP_ATTRIBUTES:HEADER', HEADER.BOOKED_DATE, HEADER.LAST_UPDATE_DATE, HEADER.LAST_UPDATED_BY, USER1.USER_NAME, HEADER.CREATION_DATE, HEADER.CREATED_BY, USER2.USER_NAME, HEADER.VERSION_NUMBER, HEADER.EXPIRATION_DATE, HEADER.SOURCE_DOCUMENT_TYPE_ID, HEADER.CHANGE_SEQUENCE, HEADER.SOURCE_DOCUMENT_ID, HEADER.REQUEST_DATE, HEADER.PRICING_DATE, HEADER.DEMAND_CLASS_CODE, HEADER.PRICE_REQUEST_CODE, HEADER.CONVERSION_RATE, HEADER.CONVERSION_TYPE_CODE, HEADER.CONVERSION_RATE_DATE, '_LA:HEADER.PARTIAL_SHIPMENTS_ALLOWED:OE_LOOKUPS:YES_NO:MEANING' , HEADER.SHIP_TOLERANCE_ABOVE, HEADER.SHIP_TOLERANCE_BELOW, '_LA:HEADER.TAX_POINT_CODE:OE_LOOKUPS:TAX_POINT:MEANING', '_LA:HEADER.OPEN_FLAG:OE_LOOKUPS:YES_NO:MEANING' , HEADER.ACCOUNTING_RULE_ID, HEADER.ACCOUNTING_RULE_DURATION, '_LA:HEADER.SHIPPING_METHOD_CODE:OE_SHIP_METHODS_V:SHIP_METHOD:MEANING' , HEADER.FREIGHT_CARRIER_CODE, HEADER.FOB_POINT_CODE, HEADER.SOLD_TO_ORG_ID, HEADER.DELIVER_TO_ORG_ID, HEADER.DELIVER_TO_CONTACT_ID, HEADER.RETURN_REASON_CODE, HEADER.ORDER_DATE_TYPE_CODE, HEADER.EARLIEST_SCHEDULE_LIMIT, HEADER.LATEST_SCHEDULE_LIMIT, HEADER.PAYMENT_AMOUNT, HEADER.CHECK_NUMBER, HEADER.CREDIT_CARD_CODE, HEADER.CREDIT_CARD_HOLDER_NAME, HEADER.CREDIT_CARD_NUMBER, HEADER.CREDIT_CARD_EXPIRATION_DATE, HEADER.CREDIT_CARD_APPROVAL_CODE, HEADER.FIRST_ACK_CODE, HEADER.FIRST_ACK_DATE, HEADER.LAST_ACK_CODE, HEADER.LAST_ACK_DATE, '_LA:HEADER.ORDER_CATEGORY_CODE:OE_LOOKUPS:ORDER_CATEGORY:MEANING' , HEADER.SHIPPING_INSTRUCTIONS, HEADER.PACKING_INSTRUCTIONS, HEADER.CREDIT_CARD_APPROVAL_DATE, HEADER.CUSTOMER_PREFERENCE_SET_CODE, HEADER.MARKETING_SOURCE_CODE_ID, HEADER.UPGRADED_FLAG, HEADER.TRANSACTION_PHASE_CODE, HEADER.ORDER_FIRMED_DATE FROM MTL_PARAMETERS SHIP_FROM_ORG, HZ_CUST_SITE_USES_ALL SHIP_TO_ORG, HZ_PARTY_SITES SHIP_TO_ADDR1, HZ_LOCATIONS SHIP_TO_ADDR2, HZ_CUST_ACCT_SITES_ALL SHIP_TO_ADDR3, HZ_PARTY_SITES BILL_TO_ADDR1, HZ_LOCATIONS BILL_TO_ADDR2, HZ_CUST_ACCT_SITES_ALL BILL_TO_ADDR3, HZ_CUST_SITE_USES_ALL BILLORG, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, RA_TERMS TERM, OE_ORDER_HEADERS_ALL HEADER, HZ_CUST_ACCOUNT_ROLES SOLDCONT1, HZ_PARTIES SOLDCONT2, ar_lookups sold_arl, HZ_RELATIONSHIPS SOLDCONT4, HZ_CONTACT_POINTS soldcont5, HZ_PARTIES soldcont6, HZ_CUST_ACCOUNT_ROLES SHIPCONT1, HZ_PARTIES SHIPCONT2, ar_lookups ship_arl, HZ_RELATIONSHIPS SHIPCONT4, HZ_CUST_ACCOUNT_ROLES BILLCONT1, HZ_PARTIES BILLCONT2, ar_lookups bill_arl, HZ_RELATIONSHIPS BILLCONT4, FND_CURRENCIES FNDCUR, OE_ORDER_SOURCES OS, OE_TRANSACTION_TYPES_TL OT, OE_AGREEMENTS_TL AGREE, QP_LIST_HEADERS_TL PL, RA_RULES INVRULE, HZ_PARTIES SHIP_PARTY, HZ_CUST_ACCOUNTS SHIP_CUST_ACCT, HZ_PARTIES BILL_PARTY, HZ_CUST_ACCOUNTS BILL_CUST_ACCT, FND_USER USER1, FND_USER USER2, HZ_CUST_ACCOUNTS SOLDACCT, HZ_CUST_ACCOUNTS SHIPACCT, HZ_CUST_ACCOUNTS BILLACCT WHERE nvl(HEADER.order_source_id, HEADER.source_document_type_id ) = os.order_source_id(+) AND HEADER.order_type_id = ot.transaction_type_id AND ot.language = userenv('LANG') AND HEADER.agreement_id = agree.agreement_id(+) AND agree.language(+) = userenv('LANG') AND HEADER.price_list_id = pl.list_header_id(+) AND pl.language(+) = userenv('LANG') AND HEADER.invoicing_rule_id = invrule.rule_id(+) AND HEADER.payment_term_id = term.term_id(+) AND HEADER.transactional_curr_code = fndcur.currency_code AND HEADER.sold_to_org_id = cust_acct.cust_account_id(+) AND PARTY.party_id(+) = cust_acct.party_id AND HEADER.ship_from_org_id = ship_from_org.organization_id(+) AND HEADER.SHIP_TO_ORG_ID = SHIP_TO_ORG.SITE_USE_ID(+) AND SHIP_TO_ORG.CUST_ACCT_SITE_ID = SHIP_TO_ADDR3.CUST_ACCT_SITE_ID(+) AND SHIP_TO_ADDR3.PARTY_SITE_ID = SHIP_TO_ADDR1.PARTY_SITE_ID(+) AND SHIP_TO_ADDR2.LOCATION_ID(+) = SHIP_TO_ADDR1.LOCATION_ID AND HEADER.INVOICE_TO_ORG_ID = BILLORG.SITE_USE_ID(+) AND BILLORG.CUST_ACCT_SITE_ID = BILL_TO_ADDR3.CUST_ACCT_SITE_ID(+) AND BILL_TO_ADDR3.PARTY_SITE_ID = BILL_TO_ADDR1.PARTY_SITE_ID(+) AND BILL_TO_ADDR2.LOCATION_ID(+) = BILL_TO_ADDR1.LOCATION_ID AND HEADER.SOLD_TO_CONTACT_ID = SOLDCONT1.CUST_ACCOUNT_ROLE_ID(+) AND SOLDCONT1.PARTY_ID = SOLDCONT4.PARTY_ID(+) AND SOLDCONT1.ROLE_TYPE(+) = 'CONTACT' and sold_arl.lookup_type(+) = 'CONTACT_TITLE' and sold_arl.lookup_code(+) = soldcont2.person_pre_name_adjunct AND SOLDCONT1.CUST_ACCOUNT_ID = SOLDACCT.CUST_ACCOUNT_ID(+) AND nvl(SOLDCONT4.OBJECT_ID,-1) = nvl(SOLDACCT.PARTY_ID,-1) AND SOLDCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND SOLDCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND SOLDCONT4.SUBJECT_ID = SOLDCONT2.PARTY_ID(+) AND SOLDCONT4.party_ID = soldcont6.PARTY_ID(+) AND SOLDCONT5.owner_table_id(+) = soldcont6.PARTY_ID AND SOLDCONT5.owner_table_name(+) = 'HZ_PARTIES' AND SOLDCONT5.contact_point_type(+) = 'PHONE' AND SOLDCONT5.status (+) = 'A' AND SOLDCONT5.primary_flag(+) = 'Y' AND HEADER.SHIP_TO_CONTACT_ID = SHIPCONT1.CUST_ACCOUNT_ROLE_ID(+) AND SHIPCONT1.PARTY_ID = SHIPCONT4.PARTY_ID(+) AND SHIPCONT1.ROLE_TYPE(+) = 'CONTACT' and ship_arl.lookup_type(+) = 'CONTACT_TITLE' and ship_arl.lookup_code(+) = shipcont2.person_pre_name_adjunct AND SHIPCONT1.CUST_ACCOUNT_ID = SHIPACCT.CUST_ACCOUNT_ID(+) AND nvl(SHIPCONT4.OBJECT_ID,-1) = nvl(SHIPACCT.PARTY_ID,-1) AND SHIPCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND SHIPCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND SHIPCONT4.SUBJECT_ID = SHIPCONT2.PARTY_ID(+) AND HEADER.INVOICE_TO_CONTACT_ID = BILLCONT1.CUST_ACCOUNT_ROLE_ID(+) AND BILLCONT1.PARTY_ID = BILLCONT4.PARTY_ID(+) AND BILLCONT1.ROLE_TYPE(+) = 'CONTACT' and bill_arl.lookup_type(+) = 'CONTACT_TITLE' and bill_arl.lookup_code(+) = billcont2.person_pre_name_adjunct AND BILLCONT1.CUST_ACCOUNT_ID = BILLACCT.CUST_ACCOUNT_ID(+) AND nvl(BILLCONT4.OBJECT_ID,-1) = nvl(BILLACCT.PARTY_ID,-1) AND BILLCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND BILLCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND BILLCONT4.SUBJECT_ID = BILLCONT2.PARTY_ID(+) AND SHIP_TO_ADDR3.CUST_ACCOUNT_ID = SHIP_CUST_ACCT.cust_account_id(+) AND SHIP_PARTY.PARTY_ID(+)= SHIP_CUST_ACCT.PARTY_ID AND BILL_TO_ADDR3.CUST_ACCOUNT_ID = BILL_CUST_ACCT.cust_account_id(+) AND BILL_PARTY.PARTY_ID(+)= BILL_CUST_ACCT.PARTY_ID AND HEADER.LAST_UPDATED_BY = USER1.USER_ID(+) AND HEADER.CREATED_BY = USER2.USER_ID (+) AND '_SEC:HEADER.ORG_ID' IS NOT NULL WITH READ ONLY

View Text - HTML Formatted

SELECT HEADER.ORDER_NUMBER
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, PARTY.PARTY_NAME CUSTOMER
, SUBSTRB(SOLDCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(SOLDCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(SOLDCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(SOLD_ARL.MEANING
, NULL
, NULL
, ' ' || SOLD_ARL.MEANING) CUSTOMER_CONTACT
, SOLDCONT6.EMAIL_ADDRESS
, DECODE(SOLDCONT5.PHONE_COUNTRY_CODE
, NULL
, NULL
, SOLDCONT5.PHONE_COUNTRY_CODE || ' ') || DECODE(SOLDCONT5.PHONE_AREA_CODE
, NULL
, NULL
, SOLDCONT5.PHONE_AREA_CODE || '-') || DECODE(SOLDCONT5.PHONE_NUMBER
, NULL
, NULL
, SOLDCONT5.PHONE_NUMBER) CUSTOMER_CONTACT_PHONE_NUMBER
, SOLDCONT5.PHONE_EXTENSION
, AGREE.NAME AGREEMENT
, PL.NAME
, SHIP_TO_ORG.LOCATION SHIP_TO_LOCATION
, SHIP_TO_ADDR2.ADDRESS1 SHIP_TO_ADDRESS1
, SHIP_TO_ADDR2.ADDRESS2 SHIP_TO_ADDRESS2
, SHIP_TO_ADDR2.ADDRESS3 SHIP_TO_ADDRESS3
, SHIP_TO_ADDR2.ADDRESS4 SHIP_TO_ADDRESS4
, DECODE(SHIP_TO_ADDR2.CITY
, NULL
, NULL
, SHIP_TO_ADDR2.CITY || '
, ') || DECODE(SHIP_TO_ADDR2.STATE
, NULL
, NULL
, SHIP_TO_ADDR2.STATE || '
, ') || DECODE(SHIP_TO_ADDR2.POSTAL_CODE
, NULL
, NULL
, SHIP_TO_ADDR2.POSTAL_CODE || '
, ') || DECODE(SHIP_TO_ADDR2.COUNTRY
, NULL
, NULL
, SHIP_TO_ADDR2.COUNTRY) SHIP_TO_CTY_ST_ZIP_CTRY
, SUBSTRB(SHIPCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(SHIPCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(SHIPCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(SHIP_ARL.MEANING
, NULL
, NULL
, ' '|| SHIP_ARL.MEANING) SHIP_TO_CONTACT
, SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM_ORG
, OS.NAME ORDER_SOURCE
, OT.NAME ORDER_TYPE
, OE_BIS_SALESPERSON.GET_SALESPERSON_NAME(HEADER.SALESREP_ID)
, BILLORG.LOCATION BILL_TO
, BILL_TO_ADDR2.ADDRESS1 BILL_TO_ADDRESS1
, BILL_TO_ADDR2.ADDRESS2 BILL_TO_ADDRESS2
, BILL_TO_ADDR2.ADDRESS3 BILL_TO_ADDRESS3
, BILL_TO_ADDR2.ADDRESS4 BILL_TO_ADDRESS4
, DECODE(BILL_TO_ADDR2.CITY
, NULL
, NULL
, BILL_TO_ADDR2.CITY || '
, ') || DECODE(BILL_TO_ADDR2.STATE
, NULL
, NULL
, BILL_TO_ADDR2.STATE || '
, ') || DECODE(BILL_TO_ADDR2.POSTAL_CODE
, NULL
, NULL
, BILL_TO_ADDR2.POSTAL_CODE || '
, ') || DECODE(BILL_TO_ADDR2.COUNTRY
, NULL
, NULL
, BILL_TO_ADDR2.COUNTRY) BILL_TO_CTY_ST_ZIP_CTRY
, SUBSTRB(BILLCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(BILLCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(BILLCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(BILL_ARL.MEANING
, NULL
, NULL
, ' '||BILL_ARL.MEANING) BILL_TO_CONTACT
, TERM.NAME
, INVRULE.NAME
, HEADER.ORDERED_DATE
, HEADER.CUST_PO_NUMBER
, '_LA:HEADER.SALES_CHANNEL_CODE:OE_LOOKUPS:SALES_CHANNEL:MEANING'
, '_LA:HEADER.FLOW_STATUS_CODE:OE_LOOKUPS:FLOW_STATUS:MEANING'
, '_LA:HEADER.TAX_EXEMPT_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, '_LA:HEADER.TAX_EXEMPT_REASON_CODE:AR_LOOKUPS:TAX_REASON:MEANING'
, HEADER.TAX_EXEMPT_NUMBER
, HEADER.TRANSACTIONAL_CURR_CODE
, HEADER.PAYMENT_TYPE_CODE
, '_LA:HEADER.SHIPMENT_PRIORITY_CODE:OE_LOOKUPS:SHIPMENT_PRIORITY:MEANING'
, '_LA:HEADER.FREIGHT_TERMS_CODE:OE_LOOKUPS:FREIGHT_TERMS:MEANING'
, '_LA:HEADER.BOOKED_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, '_LA:HEADER.CANCELLED_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, HEADER.ORG_ID
, HEADER.SOLD_FROM_ORG_ID
, HEADER.HEADER_ID
, HEADER.ORDER_TYPE_ID
, HEADER.SOLD_TO_CONTACT_ID
, HEADER.AGREEMENT_ID
, HEADER.PRICE_LIST_ID
, HEADER.SHIP_TO_ORG_ID
, HEADER.SHIP_TO_CONTACT_ID
, SUBSTRB(SHIP_PARTY.PARTY_NAME
, 1
, 50)
, SHIP_CUST_ACCT.CUST_ACCOUNT_ID
, HEADER.SHIP_FROM_ORG_ID
, HEADER.INVOICING_RULE_ID
, HEADER.INVOICE_TO_ORG_ID
, HEADER.INVOICE_TO_CONTACT_ID
, SUBSTRB(BILL_PARTY.PARTY_NAME
, 1
, 50)
, BILL_CUST_ACCT.CUST_ACCOUNT_ID
, HEADER.PAYMENT_TERM_ID
, HEADER.ORDER_SOURCE_ID
, HEADER.ORIG_SYS_DOCUMENT_REF
, HEADER.SALESREP_ID
, '_DF:ONT:OE_HEADER_ATTRIBUTES:HEADER'
, '_DF:ONT:OE_HEADER_GLOBAL_ATTRIBUTE:HEADER'
, '_DF:ONT:OE_HEADER_TP_ATTRIBUTES:HEADER'
, HEADER.BOOKED_DATE
, HEADER.LAST_UPDATE_DATE
, HEADER.LAST_UPDATED_BY
, USER1.USER_NAME
, HEADER.CREATION_DATE
, HEADER.CREATED_BY
, USER2.USER_NAME
, HEADER.VERSION_NUMBER
, HEADER.EXPIRATION_DATE
, HEADER.SOURCE_DOCUMENT_TYPE_ID
, HEADER.CHANGE_SEQUENCE
, HEADER.SOURCE_DOCUMENT_ID
, HEADER.REQUEST_DATE
, HEADER.PRICING_DATE
, HEADER.DEMAND_CLASS_CODE
, HEADER.PRICE_REQUEST_CODE
, HEADER.CONVERSION_RATE
, HEADER.CONVERSION_TYPE_CODE
, HEADER.CONVERSION_RATE_DATE
, '_LA:HEADER.PARTIAL_SHIPMENTS_ALLOWED:OE_LOOKUPS:YES_NO:MEANING'
, HEADER.SHIP_TOLERANCE_ABOVE
, HEADER.SHIP_TOLERANCE_BELOW
, '_LA:HEADER.TAX_POINT_CODE:OE_LOOKUPS:TAX_POINT:MEANING'
, '_LA:HEADER.OPEN_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, HEADER.ACCOUNTING_RULE_ID
, HEADER.ACCOUNTING_RULE_DURATION
, '_LA:HEADER.SHIPPING_METHOD_CODE:OE_SHIP_METHODS_V:SHIP_METHOD:MEANING'
, HEADER.FREIGHT_CARRIER_CODE
, HEADER.FOB_POINT_CODE
, HEADER.SOLD_TO_ORG_ID
, HEADER.DELIVER_TO_ORG_ID
, HEADER.DELIVER_TO_CONTACT_ID
, HEADER.RETURN_REASON_CODE
, HEADER.ORDER_DATE_TYPE_CODE
, HEADER.EARLIEST_SCHEDULE_LIMIT
, HEADER.LATEST_SCHEDULE_LIMIT
, HEADER.PAYMENT_AMOUNT
, HEADER.CHECK_NUMBER
, HEADER.CREDIT_CARD_CODE
, HEADER.CREDIT_CARD_HOLDER_NAME
, HEADER.CREDIT_CARD_NUMBER
, HEADER.CREDIT_CARD_EXPIRATION_DATE
, HEADER.CREDIT_CARD_APPROVAL_CODE
, HEADER.FIRST_ACK_CODE
, HEADER.FIRST_ACK_DATE
, HEADER.LAST_ACK_CODE
, HEADER.LAST_ACK_DATE
, '_LA:HEADER.ORDER_CATEGORY_CODE:OE_LOOKUPS:ORDER_CATEGORY:MEANING'
, HEADER.SHIPPING_INSTRUCTIONS
, HEADER.PACKING_INSTRUCTIONS
, HEADER.CREDIT_CARD_APPROVAL_DATE
, HEADER.CUSTOMER_PREFERENCE_SET_CODE
, HEADER.MARKETING_SOURCE_CODE_ID
, HEADER.UPGRADED_FLAG
, HEADER.TRANSACTION_PHASE_CODE
, HEADER.ORDER_FIRMED_DATE
FROM MTL_PARAMETERS SHIP_FROM_ORG
, HZ_CUST_SITE_USES_ALL SHIP_TO_ORG
, HZ_PARTY_SITES SHIP_TO_ADDR1
, HZ_LOCATIONS SHIP_TO_ADDR2
, HZ_CUST_ACCT_SITES_ALL SHIP_TO_ADDR3
, HZ_PARTY_SITES BILL_TO_ADDR1
, HZ_LOCATIONS BILL_TO_ADDR2
, HZ_CUST_ACCT_SITES_ALL BILL_TO_ADDR3
, HZ_CUST_SITE_USES_ALL BILLORG
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, RA_TERMS TERM
, OE_ORDER_HEADERS_ALL HEADER
, HZ_CUST_ACCOUNT_ROLES SOLDCONT1
, HZ_PARTIES SOLDCONT2
, AR_LOOKUPS SOLD_ARL
, HZ_RELATIONSHIPS SOLDCONT4
, HZ_CONTACT_POINTS SOLDCONT5
, HZ_PARTIES SOLDCONT6
, HZ_CUST_ACCOUNT_ROLES SHIPCONT1
, HZ_PARTIES SHIPCONT2
, AR_LOOKUPS SHIP_ARL
, HZ_RELATIONSHIPS SHIPCONT4
, HZ_CUST_ACCOUNT_ROLES BILLCONT1
, HZ_PARTIES BILLCONT2
, AR_LOOKUPS BILL_ARL
, HZ_RELATIONSHIPS BILLCONT4
, FND_CURRENCIES FNDCUR
, OE_ORDER_SOURCES OS
, OE_TRANSACTION_TYPES_TL OT
, OE_AGREEMENTS_TL AGREE
, QP_LIST_HEADERS_TL PL
, RA_RULES INVRULE
, HZ_PARTIES SHIP_PARTY
, HZ_CUST_ACCOUNTS SHIP_CUST_ACCT
, HZ_PARTIES BILL_PARTY
, HZ_CUST_ACCOUNTS BILL_CUST_ACCT
, FND_USER USER1
, FND_USER USER2
, HZ_CUST_ACCOUNTS SOLDACCT
, HZ_CUST_ACCOUNTS SHIPACCT
, HZ_CUST_ACCOUNTS BILLACCT
WHERE NVL(HEADER.ORDER_SOURCE_ID
, HEADER.SOURCE_DOCUMENT_TYPE_ID ) = OS.ORDER_SOURCE_ID(+)
AND HEADER.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.LANGUAGE = USERENV('LANG')
AND HEADER.AGREEMENT_ID = AGREE.AGREEMENT_ID(+)
AND AGREE.LANGUAGE(+) = USERENV('LANG')
AND HEADER.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
AND PL.LANGUAGE(+) = USERENV('LANG')
AND HEADER.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND HEADER.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND HEADER.TRANSACTIONAL_CURR_CODE = FNDCUR.CURRENCY_CODE
AND HEADER.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND HEADER.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID(+)
AND HEADER.SHIP_TO_ORG_ID = SHIP_TO_ORG.SITE_USE_ID(+)
AND SHIP_TO_ORG.CUST_ACCT_SITE_ID = SHIP_TO_ADDR3.CUST_ACCT_SITE_ID(+)
AND SHIP_TO_ADDR3.PARTY_SITE_ID = SHIP_TO_ADDR1.PARTY_SITE_ID(+)
AND SHIP_TO_ADDR2.LOCATION_ID(+) = SHIP_TO_ADDR1.LOCATION_ID
AND HEADER.INVOICE_TO_ORG_ID = BILLORG.SITE_USE_ID(+)
AND BILLORG.CUST_ACCT_SITE_ID = BILL_TO_ADDR3.CUST_ACCT_SITE_ID(+)
AND BILL_TO_ADDR3.PARTY_SITE_ID = BILL_TO_ADDR1.PARTY_SITE_ID(+)
AND BILL_TO_ADDR2.LOCATION_ID(+) = BILL_TO_ADDR1.LOCATION_ID
AND HEADER.SOLD_TO_CONTACT_ID = SOLDCONT1.CUST_ACCOUNT_ROLE_ID(+)
AND SOLDCONT1.PARTY_ID = SOLDCONT4.PARTY_ID(+)
AND SOLDCONT1.ROLE_TYPE(+) = 'CONTACT'
AND SOLD_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SOLD_ARL.LOOKUP_CODE(+) = SOLDCONT2.PERSON_PRE_NAME_ADJUNCT
AND SOLDCONT1.CUST_ACCOUNT_ID = SOLDACCT.CUST_ACCOUNT_ID(+)
AND NVL(SOLDCONT4.OBJECT_ID
, -1) = NVL(SOLDACCT.PARTY_ID
, -1)
AND SOLDCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLDCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLDCONT4.SUBJECT_ID = SOLDCONT2.PARTY_ID(+)
AND SOLDCONT4.PARTY_ID = SOLDCONT6.PARTY_ID(+)
AND SOLDCONT5.OWNER_TABLE_ID(+) = SOLDCONT6.PARTY_ID
AND SOLDCONT5.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLDCONT5.CONTACT_POINT_TYPE(+) = 'PHONE'
AND SOLDCONT5.STATUS (+) = 'A'
AND SOLDCONT5.PRIMARY_FLAG(+) = 'Y'
AND HEADER.SHIP_TO_CONTACT_ID = SHIPCONT1.CUST_ACCOUNT_ROLE_ID(+)
AND SHIPCONT1.PARTY_ID = SHIPCONT4.PARTY_ID(+)
AND SHIPCONT1.ROLE_TYPE(+) = 'CONTACT'
AND SHIP_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SHIP_ARL.LOOKUP_CODE(+) = SHIPCONT2.PERSON_PRE_NAME_ADJUNCT
AND SHIPCONT1.CUST_ACCOUNT_ID = SHIPACCT.CUST_ACCOUNT_ID(+)
AND NVL(SHIPCONT4.OBJECT_ID
, -1) = NVL(SHIPACCT.PARTY_ID
, -1)
AND SHIPCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIPCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIPCONT4.SUBJECT_ID = SHIPCONT2.PARTY_ID(+)
AND HEADER.INVOICE_TO_CONTACT_ID = BILLCONT1.CUST_ACCOUNT_ROLE_ID(+)
AND BILLCONT1.PARTY_ID = BILLCONT4.PARTY_ID(+)
AND BILLCONT1.ROLE_TYPE(+) = 'CONTACT'
AND BILL_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND BILL_ARL.LOOKUP_CODE(+) = BILLCONT2.PERSON_PRE_NAME_ADJUNCT
AND BILLCONT1.CUST_ACCOUNT_ID = BILLACCT.CUST_ACCOUNT_ID(+)
AND NVL(BILLCONT4.OBJECT_ID
, -1) = NVL(BILLACCT.PARTY_ID
, -1)
AND BILLCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND BILLCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND BILLCONT4.SUBJECT_ID = BILLCONT2.PARTY_ID(+)
AND SHIP_TO_ADDR3.CUST_ACCOUNT_ID = SHIP_CUST_ACCT.CUST_ACCOUNT_ID(+)
AND SHIP_PARTY.PARTY_ID(+)= SHIP_CUST_ACCT.PARTY_ID
AND BILL_TO_ADDR3.CUST_ACCOUNT_ID = BILL_CUST_ACCT.CUST_ACCOUNT_ID(+)
AND BILL_PARTY.PARTY_ID(+)= BILL_CUST_ACCT.PARTY_ID
AND HEADER.LAST_UPDATED_BY = USER1.USER_ID(+)
AND HEADER.CREATED_BY = USER2.USER_ID (+)
AND '_SEC:HEADER.ORG_ID' IS NOT NULL WITH READ ONLY