VIEW: APPS.IBE_ORDER_LINE_DTL_V

Source

View Text - Preformatted

SELECT oh.header_id, ol.line_id, oh.order_number, ol.line_number, ol.top_model_line_id, ol.link_to_line_id, msi.concatenated_segments item_number, msi.description item_description, msi.attribute7 product_description, msi.segment6 licensing_unit, ol.pricing_attribute10, ol.item_type_code, ol.ordered_quantity, ol.shipped_quantity, ol.cancelled_quantity, ol.fulfilled_quantity, ol.order_quantity_uom, msit.unit_of_measure_tl UOM_desc, ol.unit_list_price, ol.unit_selling_price, ol.request_date, ol.promise_date, ol.schedule_ship_date, ol.flow_status_code, oel.meaning line_status, ol.SHIPMENT_NUMBER, ol.OPTION_NUMBER, ol.COMPONENT_NUMBER, ol.SERVICE_NUMBER, ol.SHIPPING_METHOD_CODE req_ship_method_code, olkp2.MEANING req_ship_method_desc, ol.FREIGHT_TERMS_CODE freight_terms_code, olkp1.meaning freight_terms_desc, ol.AGREEMENT_ID, oav.NAME agreement_name, oav.name||', '||oav.agreement_num||decode(oav.agreement_num,NULL,'', ' , ')||oav.revision full_Agreement_name, ol.PAYMENT_TERM_ID, term.NAME payment_terms, oh.sold_to_org_id , sold_to_account.party_id , sold_to_party.party_name , oh.sold_to_contact_id , sold_to_contact.party_name , IBE_ORDER_UTIL_PVT.get_phone_email(decode(sold_to_party.party_type,'PERSON', sold_to_account.party_id, sold_to_contact.rel_party_id)), ol.invoice_to_org_id, inv_cust_party.party_name, inv_acct_site.cust_account_id, ol.invoice_to_contact_id, inv_contact.party_name, invaddr.city || ' ' || invaddr.state bill_to_location , invaddr.address1 , invaddr.address2 , invaddr.address3 , invaddr.address4 , invaddr.address_lInes_phonetic , invaddr.city , invaddr.state , invaddr.postal_code , invaddr.county , invaddr.province , invaddr.country , null , ol.ship_to_org_id, shp_cust_party.party_name, shp_acct_site.cust_account_id, ol.ship_to_contact_id, shp_contact.party_name, shipaddr.city || ' ' || shipaddr.state ship_to_location , shipaddr.address1 , shipaddr.address2 , shipaddr.address3 , shipaddr.address4 , shipaddr.address_lines_phonetic , shipaddr.city , shipaddr.state , shipaddr.postal_code , shipaddr.county , shipaddr.province , shipaddr.country , null, ol.end_customer_id, end_cust_party.party_name , ol.end_customer_contact_id, end_contact.party_name , endaddr.city || ' ' || endaddr.state end_location , endaddr.address1 , endaddr.address2 , endaddr.address3 , endaddr.address4 , endaddr.address_lines_phonetic , endaddr.city , endaddr.state , endaddr.postal_code , endaddr.county , endaddr.province , endaddr.country , ol.shipping_instructions, ol.packing_instructions, ol.commitment_id, rct.trx_number commitment_name, ol.cust_po_number , ol.shipment_priority_code , sp_lookup.meaning shipment_priority_code_mng , ol.context , ol.attribute1 , ol.attribute2 , ol.attribute3 , ol.attribute4 , ol.attribute5 , ol.attribute6 , ol.attribute7 , ol.attribute8 , ol.attribute9 , ol.attribute10 , ol.attribute11 , ol.attribute12 , ol.attribute13 , ol.attribute14 , ol.attribute15, oh.org_id org_id FROM oe_order_lines_all ol, oe_order_headers_all oh, mtl_system_items_vl msi, mtl_units_of_measure_tl msit, oe_lookups oel, oe_lookups olkp1, OE_SHIP_METHODS_V olkp2, ra_terms_tl term, OE_AGREEMENTS_VL oav, RA_CUSTOMER_TRX_ALL rct, hz_cust_accounts sold_to_account, hz_parties sold_to_party, ibe_oe_contacts_v sold_to_contact, hz_cust_site_uses_all shp_site_use, hz_cust_acct_sites_all shp_acct_site, hz_party_sites shp_party_site, hz_locations shipaddr, hz_cust_accounts shp_cust, hz_parties shp_cust_party, ibe_oe_contacts_v shp_contact, hz_cust_site_uses_all inv_site_use, hz_cust_acct_sites_all inv_acct_site, hz_party_sites inv_party_site, hz_locations invaddr, hz_cust_accounts inv_cust, hz_parties inv_cust_party, ibe_oe_contacts_v inv_contact, hz_parties end_cust_party, hz_cust_accounts end_cust_accounts, ibe_oe_contacts_v end_contact , hz_locations endaddr, hz_party_sites end_party_site, hz_cust_acct_sites_all end_acct_site, hz_cust_site_uses_all end_site_use, oe_lookups sp_lookup WHERE ol.inventory_item_id = msi.inventory_item_Id AND msi.organization_id = oe_profile.value('OE_ORGANIZATION_ID',ol.org_id) AND ol.header_id = oh.header_id AND ol.order_quantity_uom = msit.uom_code AND msit.language = userenv('LANG') AND oel.lookup_code(+) = ol.flow_status_code AND oel.lookup_type = 'LINE_FLOW_STATUS' AND ol.freight_terms_code = olkp1.lookup_code(+) AND olkp1.lookup_type (+) = 'FREIGHT_TERMS' AND ol.SHIPPING_METHOD_CODE = olkp2.lookup_code(+) AND olkp2.lookup_type (+) = 'SHIP_METHOD' AND ol.payment_term_id = term.term_id(+) AND term.language (+) = userenv('LANG') AND ol.AGREEMENT_ID = oav.AGREEMENT_ID(+) AND ol.COMMITMENT_ID = rct.CUSTOMER_TRX_ID(+) AND ol.sold_TO_ORG_ID = sold_to_account.cust_account_id AND sold_to_account.party_id = sold_to_party.party_id AND oh.sold_to_contact_id = sold_to_contact.cust_account_role_id(+) AND ol.ship_to_org_id = shp_site_use.site_use_id(+) AND shp_site_use.cust_acct_site_id = shp_acct_site.cust_acct_site_id(+) AND shp_acct_site.party_site_id = shp_party_site.party_site_id(+) AND shp_party_site.location_id = shipaddr.location_id(+) AND shp_acct_site.cust_account_id = shp_cust.cust_account_id(+) AND shp_cust.party_id = shp_cust_party.party_id(+) AND ol.ship_to_contact_id = shp_contact.cust_account_role_id(+) AND ol.INVOICE_TO_ORG_ID = inv_site_use.site_use_id(+) AND inv_site_use.cust_acct_site_id = inv_acct_site.cust_acct_site_id(+) AND inv_acct_site.party_site_id = inv_party_site.party_site_id(+) AND inv_party_site.location_id = invaddr.location_id(+) AND inv_acct_site.cust_account_id = inv_cust.cust_account_id(+) AND inv_cust.party_id = inv_cust_party.party_id(+) AND ol.invoice_to_contact_id = inv_contact.cust_account_role_id(+) AND ol.end_customer_id = end_cust_accounts.cust_account_id (+) AND end_cust_accounts.party_id = end_cust_party.party_id(+) AND ol.end_customer_site_use_id = end_site_use.site_use_id(+) AND end_site_use.cust_acct_site_id = end_acct_site.cust_acct_site_id(+) AND end_acct_site.party_site_id = end_party_site.party_site_id(+) AND end_party_site.location_id = endaddr.location_id(+) AND ol.end_customer_contact_id = end_contact.cust_account_role_id(+) AND ol.shipment_priority_code = sp_lookup.lookup_code (+) AND sp_lookup.lookup_type (+) = 'SHIPMENT_PRIORITY'

View Text - HTML Formatted

SELECT OH.HEADER_ID
, OL.LINE_ID
, OH.ORDER_NUMBER
, OL.LINE_NUMBER
, OL.TOP_MODEL_LINE_ID
, OL.LINK_TO_LINE_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.ATTRIBUTE7 PRODUCT_DESCRIPTION
, MSI.SEGMENT6 LICENSING_UNIT
, OL.PRICING_ATTRIBUTE10
, OL.ITEM_TYPE_CODE
, OL.ORDERED_QUANTITY
, OL.SHIPPED_QUANTITY
, OL.CANCELLED_QUANTITY
, OL.FULFILLED_QUANTITY
, OL.ORDER_QUANTITY_UOM
, MSIT.UNIT_OF_MEASURE_TL UOM_DESC
, OL.UNIT_LIST_PRICE
, OL.UNIT_SELLING_PRICE
, OL.REQUEST_DATE
, OL.PROMISE_DATE
, OL.SCHEDULE_SHIP_DATE
, OL.FLOW_STATUS_CODE
, OEL.MEANING LINE_STATUS
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, OL.SHIPPING_METHOD_CODE REQ_SHIP_METHOD_CODE
, OLKP2.MEANING REQ_SHIP_METHOD_DESC
, OL.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE
, OLKP1.MEANING FREIGHT_TERMS_DESC
, OL.AGREEMENT_ID
, OAV.NAME AGREEMENT_NAME
, OAV.NAME||'
, '||OAV.AGREEMENT_NUM||DECODE(OAV.AGREEMENT_NUM
, NULL
, ''
, '
, ')||OAV.REVISION FULL_AGREEMENT_NAME
, OL.PAYMENT_TERM_ID
, TERM.NAME PAYMENT_TERMS
, OH.SOLD_TO_ORG_ID
, SOLD_TO_ACCOUNT.PARTY_ID
, SOLD_TO_PARTY.PARTY_NAME
, OH.SOLD_TO_CONTACT_ID
, SOLD_TO_CONTACT.PARTY_NAME
, IBE_ORDER_UTIL_PVT.GET_PHONE_EMAIL(DECODE(SOLD_TO_PARTY.PARTY_TYPE
, 'PERSON'
, SOLD_TO_ACCOUNT.PARTY_ID
, SOLD_TO_CONTACT.REL_PARTY_ID))
, OL.INVOICE_TO_ORG_ID
, INV_CUST_PARTY.PARTY_NAME
, INV_ACCT_SITE.CUST_ACCOUNT_ID
, OL.INVOICE_TO_CONTACT_ID
, INV_CONTACT.PARTY_NAME
, INVADDR.CITY || ' ' || INVADDR.STATE BILL_TO_LOCATION
, INVADDR.ADDRESS1
, INVADDR.ADDRESS2
, INVADDR.ADDRESS3
, INVADDR.ADDRESS4
, INVADDR.ADDRESS_LINES_PHONETIC
, INVADDR.CITY
, INVADDR.STATE
, INVADDR.POSTAL_CODE
, INVADDR.COUNTY
, INVADDR.PROVINCE
, INVADDR.COUNTRY
, NULL
, OL.SHIP_TO_ORG_ID
, SHP_CUST_PARTY.PARTY_NAME
, SHP_ACCT_SITE.CUST_ACCOUNT_ID
, OL.SHIP_TO_CONTACT_ID
, SHP_CONTACT.PARTY_NAME
, SHIPADDR.CITY || ' ' || SHIPADDR.STATE SHIP_TO_LOCATION
, SHIPADDR.ADDRESS1
, SHIPADDR.ADDRESS2
, SHIPADDR.ADDRESS3
, SHIPADDR.ADDRESS4
, SHIPADDR.ADDRESS_LINES_PHONETIC
, SHIPADDR.CITY
, SHIPADDR.STATE
, SHIPADDR.POSTAL_CODE
, SHIPADDR.COUNTY
, SHIPADDR.PROVINCE
, SHIPADDR.COUNTRY
, NULL
, OL.END_CUSTOMER_ID
, END_CUST_PARTY.PARTY_NAME
, OL.END_CUSTOMER_CONTACT_ID
, END_CONTACT.PARTY_NAME
, ENDADDR.CITY || ' ' || ENDADDR.STATE END_LOCATION
, ENDADDR.ADDRESS1
, ENDADDR.ADDRESS2
, ENDADDR.ADDRESS3
, ENDADDR.ADDRESS4
, ENDADDR.ADDRESS_LINES_PHONETIC
, ENDADDR.CITY
, ENDADDR.STATE
, ENDADDR.POSTAL_CODE
, ENDADDR.COUNTY
, ENDADDR.PROVINCE
, ENDADDR.COUNTRY
, OL.SHIPPING_INSTRUCTIONS
, OL.PACKING_INSTRUCTIONS
, OL.COMMITMENT_ID
, RCT.TRX_NUMBER COMMITMENT_NAME
, OL.CUST_PO_NUMBER
, OL.SHIPMENT_PRIORITY_CODE
, SP_LOOKUP.MEANING SHIPMENT_PRIORITY_CODE_MNG
, OL.CONTEXT
, OL.ATTRIBUTE1
, OL.ATTRIBUTE2
, OL.ATTRIBUTE3
, OL.ATTRIBUTE4
, OL.ATTRIBUTE5
, OL.ATTRIBUTE6
, OL.ATTRIBUTE7
, OL.ATTRIBUTE8
, OL.ATTRIBUTE9
, OL.ATTRIBUTE10
, OL.ATTRIBUTE11
, OL.ATTRIBUTE12
, OL.ATTRIBUTE13
, OL.ATTRIBUTE14
, OL.ATTRIBUTE15
, OH.ORG_ID ORG_ID
FROM OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_TL MSIT
, OE_LOOKUPS OEL
, OE_LOOKUPS OLKP1
, OE_SHIP_METHODS_V OLKP2
, RA_TERMS_TL TERM
, OE_AGREEMENTS_VL OAV
, RA_CUSTOMER_TRX_ALL RCT
, HZ_CUST_ACCOUNTS SOLD_TO_ACCOUNT
, HZ_PARTIES SOLD_TO_PARTY
, IBE_OE_CONTACTS_V SOLD_TO_CONTACT
, HZ_CUST_SITE_USES_ALL SHP_SITE_USE
, HZ_CUST_ACCT_SITES_ALL SHP_ACCT_SITE
, HZ_PARTY_SITES SHP_PARTY_SITE
, HZ_LOCATIONS SHIPADDR
, HZ_CUST_ACCOUNTS SHP_CUST
, HZ_PARTIES SHP_CUST_PARTY
, IBE_OE_CONTACTS_V SHP_CONTACT
, HZ_CUST_SITE_USES_ALL INV_SITE_USE
, HZ_CUST_ACCT_SITES_ALL INV_ACCT_SITE
, HZ_PARTY_SITES INV_PARTY_SITE
, HZ_LOCATIONS INVADDR
, HZ_CUST_ACCOUNTS INV_CUST
, HZ_PARTIES INV_CUST_PARTY
, IBE_OE_CONTACTS_V INV_CONTACT
, HZ_PARTIES END_CUST_PARTY
, HZ_CUST_ACCOUNTS END_CUST_ACCOUNTS
, IBE_OE_CONTACTS_V END_CONTACT
, HZ_LOCATIONS ENDADDR
, HZ_PARTY_SITES END_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL END_ACCT_SITE
, HZ_CUST_SITE_USES_ALL END_SITE_USE
, OE_LOOKUPS SP_LOOKUP
WHERE OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID'
, OL.ORG_ID)
AND OL.HEADER_ID = OH.HEADER_ID
AND OL.ORDER_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')
AND OEL.LOOKUP_CODE(+) = OL.FLOW_STATUS_CODE
AND OEL.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
AND OL.FREIGHT_TERMS_CODE = OLKP1.LOOKUP_CODE(+)
AND OLKP1.LOOKUP_TYPE (+) = 'FREIGHT_TERMS'
AND OL.SHIPPING_METHOD_CODE = OLKP2.LOOKUP_CODE(+)
AND OLKP2.LOOKUP_TYPE (+) = 'SHIP_METHOD'
AND OL.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE (+) = USERENV('LANG')
AND OL.AGREEMENT_ID = OAV.AGREEMENT_ID(+)
AND OL.COMMITMENT_ID = RCT.CUSTOMER_TRX_ID(+)
AND OL.SOLD_TO_ORG_ID = SOLD_TO_ACCOUNT.CUST_ACCOUNT_ID
AND SOLD_TO_ACCOUNT.PARTY_ID = SOLD_TO_PARTY.PARTY_ID
AND OH.SOLD_TO_CONTACT_ID = SOLD_TO_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.SHIP_TO_ORG_ID = SHP_SITE_USE.SITE_USE_ID(+)
AND SHP_SITE_USE.CUST_ACCT_SITE_ID = SHP_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND SHP_ACCT_SITE.PARTY_SITE_ID = SHP_PARTY_SITE.PARTY_SITE_ID(+)
AND SHP_PARTY_SITE.LOCATION_ID = SHIPADDR.LOCATION_ID(+)
AND SHP_ACCT_SITE.CUST_ACCOUNT_ID = SHP_CUST.CUST_ACCOUNT_ID(+)
AND SHP_CUST.PARTY_ID = SHP_CUST_PARTY.PARTY_ID(+)
AND OL.SHIP_TO_CONTACT_ID = SHP_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.INVOICE_TO_ORG_ID = INV_SITE_USE.SITE_USE_ID(+)
AND INV_SITE_USE.CUST_ACCT_SITE_ID = INV_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND INV_ACCT_SITE.PARTY_SITE_ID = INV_PARTY_SITE.PARTY_SITE_ID(+)
AND INV_PARTY_SITE.LOCATION_ID = INVADDR.LOCATION_ID(+)
AND INV_ACCT_SITE.CUST_ACCOUNT_ID = INV_CUST.CUST_ACCOUNT_ID(+)
AND INV_CUST.PARTY_ID = INV_CUST_PARTY.PARTY_ID(+)
AND OL.INVOICE_TO_CONTACT_ID = INV_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.END_CUSTOMER_ID = END_CUST_ACCOUNTS.CUST_ACCOUNT_ID (+)
AND END_CUST_ACCOUNTS.PARTY_ID = END_CUST_PARTY.PARTY_ID(+)
AND OL.END_CUSTOMER_SITE_USE_ID = END_SITE_USE.SITE_USE_ID(+)
AND END_SITE_USE.CUST_ACCT_SITE_ID = END_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND END_ACCT_SITE.PARTY_SITE_ID = END_PARTY_SITE.PARTY_SITE_ID(+)
AND END_PARTY_SITE.LOCATION_ID = ENDADDR.LOCATION_ID(+)
AND OL.END_CUSTOMER_CONTACT_ID = END_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.SHIPMENT_PRIORITY_CODE = SP_LOOKUP.LOOKUP_CODE (+)
AND SP_LOOKUP.LOOKUP_TYPE (+) = 'SHIPMENT_PRIORITY'