Thursday, February 20, 2014

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect