Tuesday, June 3, 2014

Sales Order Hold Information Queries

SELECT * FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID='45185' --INVOICE_TO_ORG_ID ,HEADER_ID

SELECT *FROM OE_ORDER_LINES_ALL  --HEADER_ID(INVENTORY_ITEM_ID)

SELECT *FROM MTL_SYSTEM_ITEMS_B  --INVENTORY_ITEM_ID

SELECT * FROM HZ_CUST_SITE_USES_ALL  --SITE_USE_ID,CUST_ACCT_SITE_ID 

SELECT *FROM HZ_CUST_ACCT_SITES_ALL --CUST_ACCT_SITE_ID ,PARTY_SITE_ID,
             
SELECT *FROM HZ_PARTY_SITES --PATRY_SITE_ID , LOCATION_ID

SELECT * FROM HZ_LOCATIONS --LOCATION_ID

SELECT *FROM OE_ORDER_HOLDS_ALL   --HOLD_SOURCE_ID

SELECT *FROM OE_HOLD_SOURCES_ALL  --HOLD_SOURCE_ID,HOLD_ID,  (HOLD_UNTIL_DATE,HOLD_COMMENT)

SELECT *FROM OE_HOLD_DEFINITIONS  --HOLD_ID    (HOLD NAME)

SELECT HZP.PARTY_NUMBER CUST_NUM,
       HZP.PARTY_NAME CUST_NAME,
       OOH.ORDER_NUMBER ORD_NUM,
       OOH.ORDERED_DATE ORD_DATE,
       HZS.LOCATION SHIP_TO,
       HZS.LOCATION BILL_TO,
       OHS.HOLD_UNTIL_DATE HOLD_DATE,
       OHS.HOLD_COMMENT H_COMMENT,
       OHD.NAME HOLD_NAME,
       OOL.ORDERED_ITEM
  FROM HZ_PARTIES HZP,
       OE_ORDER_HEADERS_ALL OOH,
       HZ_CUST_ACCOUNTS HZC,
       HZ_CUST_SITE_USES_ALL HZS,
       OE_ORDER_HOLDS_ALL OEH,
       OE_HOLD_SOURCES_ALL OHS,
       OE_HOLD_DEFINITIONS  OHD,
       OE_ORDER_LINES_ALL  OOL
WHERE  HZP.PARTY_ID=HZC.PARTY_ID
AND    OOH.SOLD_TO_ORG_ID=HZC.CUST_ACCOUNT_ID
AND    OEH.HOLD_SOURCE_ID=OHS.HOLD_SOURCE_ID
AND    OOH.INVOICE_TO_ORG_ID=HZS.SITE_USE_ID
AND    OHS.HOLD_ID=OHD.HOLD_ID
AND    OOH.HEADER_ID=OOL.HEADER_ID
AND    OOH.HEADER_ID=OEH.HEADER_ID


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect