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.OE_ORDER_HEADERS_HIST_V

Source

View Text - Preformatted


SELECT h.hist_created_by, h.responsibility_id, h.HIST_CREATION_DATE, h.HIST_COMMENTS, h.HIST_TYPE_CODE, h.reason_code, h.PRICE_REQUEST_CODE, h.header_id, h.org_id, h.order_type_id, h.order_number, h.version_number, h.order_source_id, h.source_document_type_id, h.orig_sys_document_ref, h.source_document_id, h.ordered_date, h.request_date, h.pricing_date, h.shipment_priority_code, h.demand_class_code, h.price_list_id, h.tax_exempt_flag, h.tax_exempt_number, h.tax_exempt_reason_code, h.conversion_rate, h.conversion_rate_date, h.conversion_type_code, h.partial_shipments_allowed, h.ship_tolerance_above, h.ship_tolerance_below, h.transactional_curr_code, h.agreement_id, h.tax_point_code, h.cust_po_number, h.invoicing_rule_id, h.accounting_rule_id, h.accounting_rule_duration, h.payment_term_id, h.shipping_method_code, h.fob_point_code, h.freight_terms_code, h.sold_to_org_id, h.sold_to_phone_id, h.ship_from_org_id, h.ship_to_org_id, h.invoice_to_org_id, h.deliver_to_org_id, h.sold_to_contact_id, h.ship_to_contact_id, h.invoice_to_contact_id, h.deliver_to_contact_id, h.creation_date, h.created_by, h.last_update_date, h.last_updated_by, h.last_update_login, h.expiration_date, h.request_id, h.program_application_id, h.program_id, h.program_update_date, h.context, h.attribute1, h.attribute2, h.attribute3, h.attribute4, h.attribute5, h.attribute6, h.attribute7, h.attribute8, h.attribute9, h.attribute10, h.attribute11, h.attribute12, h.attribute13, h.attribute14, h.attribute15, h.attribute16, h.attribute17, h.attribute18, h.attribute19, h.attribute20, h.global_attribute_category, h.global_attribute1, h.global_attribute2, h.global_attribute3, h.global_attribute4, h.global_attribute5, h.global_attribute6, h.global_attribute7, h.global_attribute8, h.global_attribute9, h.global_attribute10, h.global_attribute11, h.global_attribute12, h.global_attribute13, h.global_attribute14, h.global_attribute15, h.global_attribute16, h.global_attribute17, h.global_attribute18, h.global_attribute19, h.global_attribute20, h.TP_CONTEXT, h.TP_ATTRIBUTE1, h.TP_ATTRIBUTE2, h.TP_ATTRIBUTE3, h.TP_ATTRIBUTE4, h.TP_ATTRIBUTE5, h.TP_ATTRIBUTE6, h.TP_ATTRIBUTE7, h.TP_ATTRIBUTE8, h.TP_ATTRIBUTE9, h.TP_ATTRIBUTE10, h.TP_ATTRIBUTE11, h.TP_ATTRIBUTE12, h.TP_ATTRIBUTE13, h.TP_ATTRIBUTE14, h.TP_ATTRIBUTE15, h.freight_carrier_code, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, NULL ORDER_SOURCE, ot.name ORDER_TYPE, NULL SOURCE_DOCUMENT_TYPE, NULL AGREEMENT, pl.name PRICE_LIST, NULL CONVERSION_TYPE, accrule.name ACCOUNTING_RULE, invrule.name INVOICING_RULE, term.name TERMS, PARTY.PARTY_NAME SOLD_TO, ship_from_org.organization_code SHIP_FROM, NULL SHIP_FROM_LOCATION, NULL SHIP_FROM_ADDRESS1, NULL SHIP_FROM_ADDRESS2, NULL SHIP_FROM_ADDRESS3, NULL SHIP_FROM_ADDRESS4, ship_su.location SHIP_TO, ship_su.location SHIP_TO_LOCATION, ship_loc.address1 SHIP_TO_ADDRESS1, ship_loc.address2 SHIP_TO_ADDRESS2, ship_loc.address3 SHIP_TO_ADDRESS3, ship_loc.address4 SHIP_TO_ADDRESS4, DECODE(ship_loc.city, NULL, NULL, ship_loc.city || ', ') || DECODE(ship_loc.state, NULL, NULL, ship_loc.state || ', ') || DECODE(ship_loc.postal_code, NULL, NULL, ship_loc.postal_code || ', ') || DECODE(ship_loc.country, NULL, NULL, ship_loc.country) SHIP_TO_ADDRESS5, NULL DELIVER_TO, NULL DELIVER_TO_LOCATION, NULL DELIVER_TO_ADDRESS1, NULL DELIVER_TO_ADDRESS2, NULL DELIVER_TO_ADDRESS3, NULL DELIVER_TO_ADDRESS4, bill_su.location INVOICE_TO, bill_su.location INVOICE_TO_LOCATION, bill_loc.address1 INVOICE_TO_ADDRESS1, bill_loc.address2 INVOICE_TO_ADDRESS2, bill_loc.address3 INVOICE_TO_ADDRESS3, bill_loc.address4 INVOICE_TO_ADDRESS4, DECODE(bill_loc.city, NULL, NULL, bill_loc.city || ', ') || DECODE(bill_loc.state, NULL, NULL, bill_loc.state || ', ') || DECODE(bill_loc.postal_code, NULL, NULL, bill_loc.postal_code || ', ') || DECODE(bill_loc.country, NULL, NULL, bill_loc.country) INVOICE_TO_ADDRESS5, sold_party.PERSON_LAST_NAME || DECODE(sold_party.PERSON_FIRST_NAME, NULL, NULL, ', ' || sold_party.PERSON_FIRST_NAME) || DECODE(sold_arl.meaning,NULL, NULL, ' '||sold_arl.meaning) SOLD_TO_CONTACT, ship_party.PERSON_LAST_NAME || DECODE(ship_party.PERSON_FIRST_NAME, NULL, NULL, ', ' || ship_party.PERSON_FIRST_NAME) || DECODE(ship_arl.meaning,NULL, NULL, ' '||ship_arl.meaning) SHIP_TO_CONTACT, invoice_party.PERSON_LAST_NAME || DECODE(invoice_party.PERSON_FIRST_NAME, NULL, NULL, ', ' || invoice_party.PERSON_FIRST_NAME) || DECODE(invoice_arl.meaning,NULL, NULL, ' '||invoice_arl.meaning) INVOICE_TO_CONTACT, NULL DELIVER_TO_CONTACT, h.salesrep_id, h.return_reason_code, NULL return_reason, h.order_date_type_code, h.earliest_schedule_limit, h.latest_schedule_limit, h.PAYMENT_TYPE_CODE, h.PAYMENT_AMOUNT, h.CHECK_NUMBER, h.CREDIT_CARD_CODE, h.CREDIT_CARD_HOLDER_NAME, h.CREDIT_CARD_NUMBER, h.CREDIT_CARD_EXPIRATION_DATE, h.CREDIT_CARD_APPROVAL_CODE, h.CREDIT_CARD_APPROVAL_DATE, h.FIRST_ACK_CODE, h.FIRST_ACK_DATE, h.LAST_ACK_CODE, h.LAST_ACK_DATE, h.booked_flag, h.booked_date, h.cancelled_flag, h.open_flag, h.sold_from_org_id, h.shipping_instructions, h.packing_instructions, h.order_category_code, h.flow_status_code, NULL FREIGHT_CARRIER, decode(h.CUSTOMER_PREFERENCE_SET_CODE,'ARRIVAL','Arrival','SHIP','Ship'), h.sales_channel_code, h.upgraded_flag, h.lock_control, party.email_address, nvl(party.gsa_indicator_flag,'N') gsa_indicator, h.blanket_number, h.QUOTE_DATE, h.QUOTE_NUMBER, h.SALES_DOCUMENT_NAME, h.TRANSACTION_PHASE_CODE, h.USER_STATUS_CODE, h.DRAFT_SUBMITTED_FLAG, h.SOURCE_DOCUMENT_VERSION_NUMBER, h.SOLD_TO_SITE_USE_ID, h.SUPPLIER_SIGNATURE, h.SUPPLIER_SIGNATURE_DATE, h.CUSTOMER_SIGNATURE, h.CUSTOMER_SIGNATURE_DATE, h.reason_id, h.audit_flag, h.version_flag, h.phase_change_flag, h.minisite_id, h.end_customer_id, h.end_customer_contact_id, h.end_customer_site_use_id, null end_customer_name, null end_customer_number, null end_customer_contact, null end_customer_location, null end_customer_address1, null end_customer_address2, null end_customer_address3, null end_customer_address4, null end_customer_address5, h.ib_owner, h.ib_current_location, h.ib_installed_at_location, lkup.meaning transaction_phase, h.ORDER_FIRMED_DATE, h.INSTRUMENT_ID FROM mtl_parameters ship_from_org, hz_cust_site_uses_all ship_su, hz_party_sites ship_ps, hz_locations ship_loc, hz_cust_acct_sites_all ship_cas, hz_cust_site_uses_all bill_su, hz_party_sites bill_ps, hz_locations bill_loc, hz_cust_acct_sites_all bill_cas, hz_parties party, hz_cust_accounts cust_acct, ra_terms_tl term, oe_order_header_history h, hz_cust_account_roles sold_roles, hz_parties sold_party, hz_relationships sold_rel, hz_cust_accounts sold_acct, ar_lookups sold_arl, hz_cust_account_roles ship_roles, hz_parties ship_party, hz_relationships ship_rel, hz_cust_accounts ship_acct, ar_lookups ship_arl, hz_cust_account_roles invoice_roles, hz_parties invoice_party, hz_relationships invoice_rel, hz_cust_accounts invoice_acct, ar_lookups invoice_arl, oe_transaction_types_tl ot, qp_list_headers_tl pl, ra_rules invrule, ra_rules accrule, OE_LOOKUPS lkup WHERE h.order_type_id = ot.transaction_type_id AND ot.language = userenv('LANG') AND h.price_list_id = pl.list_header_id(+) AND pl.language(+) = userenv('LANG') AND h.invoicing_rule_id = invrule.rule_id(+) AND h.accounting_rule_id = accrule.rule_id(+) AND h.payment_term_id = term.term_id(+) AND TERM.Language(+) =userenv('LANG') AND h.sold_to_org_id = cust_acct.cust_account_id(+) AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+) AND h.ship_from_org_id = ship_from_org.organization_id (+) AND h.ship_to_org_id = ship_su.site_use_id(+) AND ship_su.cust_acct_site_id= ship_cas.cust_acct_site_id(+) AND ship_cas.party_site_id = ship_ps.party_site_id(+) AND ship_loc.location_id(+) = ship_ps.location_id AND h.invoice_to_org_id = bill_su.site_use_id(+) AND bill_su.cust_acct_site_id= bill_cas.cust_acct_site_id(+) AND bill_cas.party_site_id = bill_ps.party_site_id(+) AND bill_loc.location_id(+) = bill_ps.location_id AND h.sold_to_contact_id = sold_roles.cust_account_role_id(+) AND sold_roles.party_id = sold_rel.party_id(+) AND sold_roles.role_type(+) = 'CONTACT' AND sold_rel.subject_id = sold_party.party_id(+) AND sold_arl.lookup_type(+) = 'CONTACT_TITLE' AND sold_arl.lookup_code(+) = sold_party.person_pre_name_adjunct AND h.ship_to_contact_id = ship_roles.cust_account_role_id(+) AND ship_roles.party_id = ship_rel.party_id(+) AND ship_roles.role_type(+) = 'CONTACT' AND ship_rel.subject_id = ship_party.party_id(+) AND ship_arl.lookup_type(+) = 'CONTACT_TITLE' AND ship_arl.lookup_code(+) = ship_party.person_pre_name_adjunct AND h.invoice_to_contact_id = invoice_roles.cust_account_role_id(+) AND invoice_roles.party_id = invoice_rel.party_id(+) AND invoice_roles.role_type(+) = 'CONTACT' AND invoice_rel.subject_id = invoice_party.party_id(+) AND invoice_arl.lookup_type(+) = 'CONTACT_TITLE' AND invoice_arl.lookup_code(+) = invoice_party.person_pre_name_adjunct AND h.transaction_phase_code = lkup.LOOKUP_CODE(+) AND lkup.LOOKUP_TYPE(+) = 'TRANSACTION_PHASE' and ship_rel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND invoice_rel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' and invoice_rel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND ship_rel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND sold_rel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' and sold_rel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND sold_roles.cust_account_id = sold_acct.cust_account_id(+) AND nvl(sold_rel.object_id,-1) = nvl(sold_acct.party_id,-1) AND ship_roles.cust_account_id = ship_acct.cust_account_id(+) AND nvl(ship_rel.object_id,-1) = nvl(ship_acct.party_id,-1) AND invoice_roles.cust_account_id = invoice_acct.cust_account_id(+) AND nvl(invoice_rel.object_id,-1) = nvl(invoice_acct.party_id,-1)

View Text - HTML Formatted

SELECT H.HIST_CREATED_BY
, H.RESPONSIBILITY_ID
, H.HIST_CREATION_DATE
, H.HIST_COMMENTS
, H.HIST_TYPE_CODE
, H.REASON_CODE
, H.PRICE_REQUEST_CODE
, H.HEADER_ID
, H.ORG_ID
, H.ORDER_TYPE_ID
, H.ORDER_NUMBER
, H.VERSION_NUMBER
, H.ORDER_SOURCE_ID
, H.SOURCE_DOCUMENT_TYPE_ID
, H.ORIG_SYS_DOCUMENT_REF
, H.SOURCE_DOCUMENT_ID
, H.ORDERED_DATE
, H.REQUEST_DATE
, H.PRICING_DATE
, H.SHIPMENT_PRIORITY_CODE
, H.DEMAND_CLASS_CODE
, H.PRICE_LIST_ID
, H.TAX_EXEMPT_FLAG
, H.TAX_EXEMPT_NUMBER
, H.TAX_EXEMPT_REASON_CODE
, H.CONVERSION_RATE
, H.CONVERSION_RATE_DATE
, H.CONVERSION_TYPE_CODE
, H.PARTIAL_SHIPMENTS_ALLOWED
, H.SHIP_TOLERANCE_ABOVE
, H.SHIP_TOLERANCE_BELOW
, H.TRANSACTIONAL_CURR_CODE
, H.AGREEMENT_ID
, H.TAX_POINT_CODE
, H.CUST_PO_NUMBER
, H.INVOICING_RULE_ID
, H.ACCOUNTING_RULE_ID
, H.ACCOUNTING_RULE_DURATION
, H.PAYMENT_TERM_ID
, H.SHIPPING_METHOD_CODE
, H.FOB_POINT_CODE
, H.FREIGHT_TERMS_CODE
, H.SOLD_TO_ORG_ID
, H.SOLD_TO_PHONE_ID
, H.SHIP_FROM_ORG_ID
, H.SHIP_TO_ORG_ID
, H.INVOICE_TO_ORG_ID
, H.DELIVER_TO_ORG_ID
, H.SOLD_TO_CONTACT_ID
, H.SHIP_TO_CONTACT_ID
, H.INVOICE_TO_CONTACT_ID
, H.DELIVER_TO_CONTACT_ID
, H.CREATION_DATE
, H.CREATED_BY
, H.LAST_UPDATE_DATE
, H.LAST_UPDATED_BY
, H.LAST_UPDATE_LOGIN
, H.EXPIRATION_DATE
, H.REQUEST_ID
, H.PROGRAM_APPLICATION_ID
, H.PROGRAM_ID
, H.PROGRAM_UPDATE_DATE
, H.CONTEXT
, H.ATTRIBUTE1
, H.ATTRIBUTE2
, H.ATTRIBUTE3
, H.ATTRIBUTE4
, H.ATTRIBUTE5
, H.ATTRIBUTE6
, H.ATTRIBUTE7
, H.ATTRIBUTE8
, H.ATTRIBUTE9
, H.ATTRIBUTE10
, H.ATTRIBUTE11
, H.ATTRIBUTE12
, H.ATTRIBUTE13
, H.ATTRIBUTE14
, H.ATTRIBUTE15
, H.ATTRIBUTE16
, H.ATTRIBUTE17
, H.ATTRIBUTE18
, H.ATTRIBUTE19
, H.ATTRIBUTE20
, H.GLOBAL_ATTRIBUTE_CATEGORY
, H.GLOBAL_ATTRIBUTE1
, H.GLOBAL_ATTRIBUTE2
, H.GLOBAL_ATTRIBUTE3
, H.GLOBAL_ATTRIBUTE4
, H.GLOBAL_ATTRIBUTE5
, H.GLOBAL_ATTRIBUTE6
, H.GLOBAL_ATTRIBUTE7
, H.GLOBAL_ATTRIBUTE8
, H.GLOBAL_ATTRIBUTE9
, H.GLOBAL_ATTRIBUTE10
, H.GLOBAL_ATTRIBUTE11
, H.GLOBAL_ATTRIBUTE12
, H.GLOBAL_ATTRIBUTE13
, H.GLOBAL_ATTRIBUTE14
, H.GLOBAL_ATTRIBUTE15
, H.GLOBAL_ATTRIBUTE16
, H.GLOBAL_ATTRIBUTE17
, H.GLOBAL_ATTRIBUTE18
, H.GLOBAL_ATTRIBUTE19
, H.GLOBAL_ATTRIBUTE20
, H.TP_CONTEXT
, H.TP_ATTRIBUTE1
, H.TP_ATTRIBUTE2
, H.TP_ATTRIBUTE3
, H.TP_ATTRIBUTE4
, H.TP_ATTRIBUTE5
, H.TP_ATTRIBUTE6
, H.TP_ATTRIBUTE7
, H.TP_ATTRIBUTE8
, H.TP_ATTRIBUTE9
, H.TP_ATTRIBUTE10
, H.TP_ATTRIBUTE11
, H.TP_ATTRIBUTE12
, H.TP_ATTRIBUTE13
, H.TP_ATTRIBUTE14
, H.TP_ATTRIBUTE15
, H.FREIGHT_CARRIER_CODE
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, NULL ORDER_SOURCE
, OT.NAME ORDER_TYPE
, NULL SOURCE_DOCUMENT_TYPE
, NULL AGREEMENT
, PL.NAME PRICE_LIST
, NULL CONVERSION_TYPE
, ACCRULE.NAME ACCOUNTING_RULE
, INVRULE.NAME INVOICING_RULE
, TERM.NAME TERMS
, PARTY.PARTY_NAME SOLD_TO
, SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM
, NULL SHIP_FROM_LOCATION
, NULL SHIP_FROM_ADDRESS1
, NULL SHIP_FROM_ADDRESS2
, NULL SHIP_FROM_ADDRESS3
, NULL SHIP_FROM_ADDRESS4
, SHIP_SU.LOCATION SHIP_TO
, SHIP_SU.LOCATION SHIP_TO_LOCATION
, SHIP_LOC.ADDRESS1 SHIP_TO_ADDRESS1
, SHIP_LOC.ADDRESS2 SHIP_TO_ADDRESS2
, SHIP_LOC.ADDRESS3 SHIP_TO_ADDRESS3
, SHIP_LOC.ADDRESS4 SHIP_TO_ADDRESS4
, DECODE(SHIP_LOC.CITY
, NULL
, NULL
, SHIP_LOC.CITY || '
, ') || DECODE(SHIP_LOC.STATE
, NULL
, NULL
, SHIP_LOC.STATE || '
, ') || DECODE(SHIP_LOC.POSTAL_CODE
, NULL
, NULL
, SHIP_LOC.POSTAL_CODE || '
, ') || DECODE(SHIP_LOC.COUNTRY
, NULL
, NULL
, SHIP_LOC.COUNTRY) SHIP_TO_ADDRESS5
, NULL DELIVER_TO
, NULL DELIVER_TO_LOCATION
, NULL DELIVER_TO_ADDRESS1
, NULL DELIVER_TO_ADDRESS2
, NULL DELIVER_TO_ADDRESS3
, NULL DELIVER_TO_ADDRESS4
, BILL_SU.LOCATION INVOICE_TO
, BILL_SU.LOCATION INVOICE_TO_LOCATION
, BILL_LOC.ADDRESS1 INVOICE_TO_ADDRESS1
, BILL_LOC.ADDRESS2 INVOICE_TO_ADDRESS2
, BILL_LOC.ADDRESS3 INVOICE_TO_ADDRESS3
, BILL_LOC.ADDRESS4 INVOICE_TO_ADDRESS4
, DECODE(BILL_LOC.CITY
, NULL
, NULL
, BILL_LOC.CITY || '
, ') || DECODE(BILL_LOC.STATE
, NULL
, NULL
, BILL_LOC.STATE || '
, ') || DECODE(BILL_LOC.POSTAL_CODE
, NULL
, NULL
, BILL_LOC.POSTAL_CODE || '
, ') || DECODE(BILL_LOC.COUNTRY
, NULL
, NULL
, BILL_LOC.COUNTRY) INVOICE_TO_ADDRESS5
, SOLD_PARTY.PERSON_LAST_NAME || DECODE(SOLD_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || SOLD_PARTY.PERSON_FIRST_NAME) || DECODE(SOLD_ARL.MEANING
, NULL
, NULL
, ' '||SOLD_ARL.MEANING) SOLD_TO_CONTACT
, SHIP_PARTY.PERSON_LAST_NAME || DECODE(SHIP_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || SHIP_PARTY.PERSON_FIRST_NAME) || DECODE(SHIP_ARL.MEANING
, NULL
, NULL
, ' '||SHIP_ARL.MEANING) SHIP_TO_CONTACT
, INVOICE_PARTY.PERSON_LAST_NAME || DECODE(INVOICE_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || INVOICE_PARTY.PERSON_FIRST_NAME) || DECODE(INVOICE_ARL.MEANING
, NULL
, NULL
, ' '||INVOICE_ARL.MEANING) INVOICE_TO_CONTACT
, NULL DELIVER_TO_CONTACT
, H.SALESREP_ID
, H.RETURN_REASON_CODE
, NULL RETURN_REASON
, H.ORDER_DATE_TYPE_CODE
, H.EARLIEST_SCHEDULE_LIMIT
, H.LATEST_SCHEDULE_LIMIT
, H.PAYMENT_TYPE_CODE
, H.PAYMENT_AMOUNT
, H.CHECK_NUMBER
, H.CREDIT_CARD_CODE
, H.CREDIT_CARD_HOLDER_NAME
, H.CREDIT_CARD_NUMBER
, H.CREDIT_CARD_EXPIRATION_DATE
, H.CREDIT_CARD_APPROVAL_CODE
, H.CREDIT_CARD_APPROVAL_DATE
, H.FIRST_ACK_CODE
, H.FIRST_ACK_DATE
, H.LAST_ACK_CODE
, H.LAST_ACK_DATE
, H.BOOKED_FLAG
, H.BOOKED_DATE
, H.CANCELLED_FLAG
, H.OPEN_FLAG
, H.SOLD_FROM_ORG_ID
, H.SHIPPING_INSTRUCTIONS
, H.PACKING_INSTRUCTIONS
, H.ORDER_CATEGORY_CODE
, H.FLOW_STATUS_CODE
, NULL FREIGHT_CARRIER
, DECODE(H.CUSTOMER_PREFERENCE_SET_CODE
, 'ARRIVAL'
, 'ARRIVAL'
, 'SHIP'
, 'SHIP')
, H.SALES_CHANNEL_CODE
, H.UPGRADED_FLAG
, H.LOCK_CONTROL
, PARTY.EMAIL_ADDRESS
, NVL(PARTY.GSA_INDICATOR_FLAG
, 'N') GSA_INDICATOR
, H.BLANKET_NUMBER
, H.QUOTE_DATE
, H.QUOTE_NUMBER
, H.SALES_DOCUMENT_NAME
, H.TRANSACTION_PHASE_CODE
, H.USER_STATUS_CODE
, H.DRAFT_SUBMITTED_FLAG
, H.SOURCE_DOCUMENT_VERSION_NUMBER
, H.SOLD_TO_SITE_USE_ID
, H.SUPPLIER_SIGNATURE
, H.SUPPLIER_SIGNATURE_DATE
, H.CUSTOMER_SIGNATURE
, H.CUSTOMER_SIGNATURE_DATE
, H.REASON_ID
, H.AUDIT_FLAG
, H.VERSION_FLAG
, H.PHASE_CHANGE_FLAG
, H.MINISITE_ID
, H.END_CUSTOMER_ID
, H.END_CUSTOMER_CONTACT_ID
, H.END_CUSTOMER_SITE_USE_ID
, NULL END_CUSTOMER_NAME
, NULL END_CUSTOMER_NUMBER
, NULL END_CUSTOMER_CONTACT
, NULL END_CUSTOMER_LOCATION
, NULL END_CUSTOMER_ADDRESS1
, NULL END_CUSTOMER_ADDRESS2
, NULL END_CUSTOMER_ADDRESS3
, NULL END_CUSTOMER_ADDRESS4
, NULL END_CUSTOMER_ADDRESS5
, H.IB_OWNER
, H.IB_CURRENT_LOCATION
, H.IB_INSTALLED_AT_LOCATION
, LKUP.MEANING TRANSACTION_PHASE
, H.ORDER_FIRMED_DATE
, H.INSTRUMENT_ID
FROM MTL_PARAMETERS SHIP_FROM_ORG
, HZ_CUST_SITE_USES_ALL SHIP_SU
, HZ_PARTY_SITES SHIP_PS
, HZ_LOCATIONS SHIP_LOC
, HZ_CUST_ACCT_SITES_ALL SHIP_CAS
, HZ_CUST_SITE_USES_ALL BILL_SU
, HZ_PARTY_SITES BILL_PS
, HZ_LOCATIONS BILL_LOC
, HZ_CUST_ACCT_SITES_ALL BILL_CAS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, RA_TERMS_TL TERM
, OE_ORDER_HEADER_HISTORY H
, HZ_CUST_ACCOUNT_ROLES SOLD_ROLES
, HZ_PARTIES SOLD_PARTY
, HZ_RELATIONSHIPS SOLD_REL
, HZ_CUST_ACCOUNTS SOLD_ACCT
, AR_LOOKUPS SOLD_ARL
, HZ_CUST_ACCOUNT_ROLES SHIP_ROLES
, HZ_PARTIES SHIP_PARTY
, HZ_RELATIONSHIPS SHIP_REL
, HZ_CUST_ACCOUNTS SHIP_ACCT
, AR_LOOKUPS SHIP_ARL
, HZ_CUST_ACCOUNT_ROLES INVOICE_ROLES
, HZ_PARTIES INVOICE_PARTY
, HZ_RELATIONSHIPS INVOICE_REL
, HZ_CUST_ACCOUNTS INVOICE_ACCT
, AR_LOOKUPS INVOICE_ARL
, OE_TRANSACTION_TYPES_TL OT
, QP_LIST_HEADERS_TL PL
, RA_RULES INVRULE
, RA_RULES ACCRULE
, OE_LOOKUPS LKUP
WHERE H.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.LANGUAGE = USERENV('LANG')
AND H.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
AND PL.LANGUAGE(+) = USERENV('LANG')
AND H.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND H.ACCOUNTING_RULE_ID = ACCRULE.RULE_ID(+)
AND H.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE(+) =USERENV('LANG')
AND H.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND H.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID (+)
AND H.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID(+)
AND SHIP_SU.CUST_ACCT_SITE_ID= SHIP_CAS.CUST_ACCT_SITE_ID(+)
AND SHIP_CAS.PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID(+)
AND SHIP_LOC.LOCATION_ID(+) = SHIP_PS.LOCATION_ID
AND H.INVOICE_TO_ORG_ID = BILL_SU.SITE_USE_ID(+)
AND BILL_SU.CUST_ACCT_SITE_ID= BILL_CAS.CUST_ACCT_SITE_ID(+)
AND BILL_CAS.PARTY_SITE_ID = BILL_PS.PARTY_SITE_ID(+)
AND BILL_LOC.LOCATION_ID(+) = BILL_PS.LOCATION_ID
AND H.SOLD_TO_CONTACT_ID = SOLD_ROLES.CUST_ACCOUNT_ROLE_ID(+)
AND SOLD_ROLES.PARTY_ID = SOLD_REL.PARTY_ID(+)
AND SOLD_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND SOLD_REL.SUBJECT_ID = SOLD_PARTY.PARTY_ID(+)
AND SOLD_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SOLD_ARL.LOOKUP_CODE(+) = SOLD_PARTY.PERSON_PRE_NAME_ADJUNCT
AND H.SHIP_TO_CONTACT_ID = SHIP_ROLES.CUST_ACCOUNT_ROLE_ID(+)
AND SHIP_ROLES.PARTY_ID = SHIP_REL.PARTY_ID(+)
AND SHIP_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND SHIP_REL.SUBJECT_ID = SHIP_PARTY.PARTY_ID(+)
AND SHIP_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SHIP_ARL.LOOKUP_CODE(+) = SHIP_PARTY.PERSON_PRE_NAME_ADJUNCT
AND H.INVOICE_TO_CONTACT_ID = INVOICE_ROLES.CUST_ACCOUNT_ROLE_ID(+)
AND INVOICE_ROLES.PARTY_ID = INVOICE_REL.PARTY_ID(+)
AND INVOICE_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND INVOICE_REL.SUBJECT_ID = INVOICE_PARTY.PARTY_ID(+)
AND INVOICE_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND INVOICE_ARL.LOOKUP_CODE(+) = INVOICE_PARTY.PERSON_PRE_NAME_ADJUNCT
AND H.TRANSACTION_PHASE_CODE = LKUP.LOOKUP_CODE(+)
AND LKUP.LOOKUP_TYPE(+) = 'TRANSACTION_PHASE'
AND SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND INVOICE_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND INVOICE_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_ROLES.CUST_ACCOUNT_ID = SOLD_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(SOLD_REL.OBJECT_ID
, -1) = NVL(SOLD_ACCT.PARTY_ID
, -1)
AND SHIP_ROLES.CUST_ACCOUNT_ID = SHIP_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(SHIP_REL.OBJECT_ID
, -1) = NVL(SHIP_ACCT.PARTY_ID
, -1)
AND INVOICE_ROLES.CUST_ACCOUNT_ID = INVOICE_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(INVOICE_REL.OBJECT_ID
, -1) = NVL(INVOICE_ACCT.PARTY_ID
, -1)