Oracle Sales Order Query(OM)

SELECT

  MP.ORGANIZATION_CODE ORGANIZATION_CODE,

  OOHA.ORDER_NUMBER ORDER_NUMBER,

  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,

  HP.PARTY_NAME ORDER_SOLD_TO,

  OOHA.CUST_PO_NUMBER CUST_PO_NUMBER,

  OOHA.ORDERED_DATE ORDERED_DATE,

  OTTT.NAME ORDER_TYPE,

  QLHT.NAME PRICE_LIST_NAME,

  OOHA.TRANSACTIONAL_CURR_CODE,

  RES.RESOURCE_NAME SALES_PERSON,

  RTT.NAME PAYMENT_TERMS,

  OOLA.LINE_NUMBER,

  OOLA.ORDERED_ITEM ORDERED_ITEM_DSP,

  OOLA.ORDERED_QUANTITY ORDERED_QUANTITY,

  OOLA.ORDER_QUANTITY_UOM ORDER_QUANTITY_UOM,

  OOLA.UNIT_SELLING_PRICE UNIT_SELLING_PRICE,

  OOLA. UNIT_LIST_PRICE UNIT_LIST_PRICE,

  NULL SUBINVENTORY,

  HCSUA.LOCATION SHIP_TO,

  BILL_TO.LOCATION BILL_TO,

  OOLA.REQUEST_DATE REQUEST_DATE,

  OOLA.SCHEDULE_SHIP_DATE SCHEDULE_SHIP_DATE,

  OOHA.SHIPPING_INSTRUCTIONS SHIPPING_INSTRUCTIONS

FROM

  OE_ORDER_HEADERS_ALL OOHA,

  OE_ORDER_LINES_ALL OOLA,

  HZ_CUST_ACCOUNTS HCA,

  OE_TRANSACTION_TYPES_TL OTTT,

  QP_LIST_HEADERS_TL QLHT,

  JTF_RS_RESOURCE_EXTNS_VL RES,

  JTF_RS_SALESREPS jR,

  RA_TERMS_TL RTT,

  MTL_PARAMETERS MP,

  HZ_PARTIES HP,

  HZ_CUST_SITE_USES_ALL HCSUA,

  hz_cust_site_uses_all bill_to

 

WHERE 1=1

AND ooha.order_number=<ORDER_NUMBER>

AND OOLA.HEADER_ID         = OOHA.HEADER_ID

AND OOhA.SOLD_TO_ORG_ID    = HCA.CUST_ACCOUNT_ID(+)

AND OOhA.ORDER_TYPE_ID     = OTTT.TRANSACTION_TYPE_ID(+)

AND OTTT.LANGUAGE          = USERENV('LANG')

AND OOhA.PRICE_LIST_ID     = QLHT.LIST_HEADER_ID(+)

AND QLHT.LANGUAGE(+)       = USERENV('LANG')

AND OOHA.SALESREP_ID       = JR.SALESREP_ID (+)

AND JR.RESOURCE_ID         = RES.RESOURCE_ID (+)

AND OOhA.PAYMENT_TERM_ID   = RTT.TERM_ID(+)

AND RTT.LANGUAGE(+)        =USERENV('LANG')

AND OOhA.SHIP_FROM_ORG_ID  = MP.ORGANIZATION_ID (+)

AND HCA.PARTY_ID           = HP.PARTY_ID(+)

AND OOhA.SHIP_TO_ORG_ID    = HCSUA.SITE_USE_ID(+)

AND OOhA.INVOICE_TO_ORG_ID = BILL_TO.SITE_USE_ID(+)

AND JR.org_id(+)           = OOHA.org_id;