Query to find onhand quantity Oracle Apps

-

  SELECT   ON_HAND.SUBINVENTORY_CODE,ITEM.SEGMENT1 ITEM_CODE, ITEM.DESCRIPTION ITEM_DESCRIPTION,

         SUM (ON_HAND.ON_HAND) ON_HAND

    FROM MTL_SYSTEM_ITEMS_B ITEM, MTL_ONHAND_TOTAL_MWB_V ON_HAND

   WHERE ITEM.ORGANIZATION_ID = 3762  --ORG

  AND ITEM.INVENTORY_ITEM_ID = ON_HAND.INVENTORY_ITEM_ID

     AND ON_HAND.SUBINVENTORY_CODE IN ('PI6.FG','PI7.FG')  --SUB INVENTORY

   --  AND ITEM.SEGMENT1 = '886631' ---ITEM

GROUP BY ON_HAND.SUBINVENTORY_CODE,ITEM.SEGMENT1, ITEM.DESCRIPTION ORDER BY ON_HAND.SUBINVENTORY_CODE; 

          

------------second


   SELECT   ON_HAND.ORGANIZATION_CODE,ON_HAND.SUBINVENTORY_CODE, ITEM.DESCRIPTION ITEM_DESCRIPTION,ITEM.SEGMENT1 ITEM_CODE,

         SUM (ON_HAND.ON_HAND) ON_HAND

    FROM MTL_SYSTEM_ITEMS_B ITEM, MTL_ONHAND_TOTAL_MWB_V ON_HAND

   WHERE ITEM.ORGANIZATION_ID = 3759  --ORG ID

  AND ITEM.INVENTORY_ITEM_ID = ON_HAND.INVENTORY_ITEM_ID

     --AND ON_HAND.SUBINVENTORY_CODE IN ('PA0.FG')  --SUB INVENTORY

   --  AND ITEM.SEGMENT1 = '886631' ---ITEM NAME

GROUP BY ON_HAND.ORGANIZATION_CODE,ON_HAND.SUBINVENTORY_CODE,ITEM.SEGMENT1, ITEM.DESCRIPTION ORDER BY ON_HAND.ORGANIZATION_CODE; 

          

            select * from xx_org_info --where ORG_CODE='PH0'

Comments

Post a Comment

Popular posts from this blog

Formula list

-
 SELECT ffm.formula_no, ffm.formula_desc1 formula_name, DECODE (fmd.line_type, 1, 'Product', 'Ingredient') TYPE, msi.segment1 icode, ood.organization_code org, fmd.qty, fmd.detail_uom, DECODE (ffm.formula_status, 700, 'Active', 'New') status FROM apps.fm_matl_dtl fmd, apps.fm_form_mst ffm, apps.mtl_system_items_b msi, apps.org_organization_definitions ood WHERE fmd.formula_id = ffm.formula_id AND (fmd.inventory_item_id = msi.inventory_item_id(+) AND fmd.organization_id = msi.organization_id(+)) AND fmd.organization_id = ood.organization_id AND fmd.line_type=1 ORDER BY formula_no, DECODE (fmd.line_type, 1, 'Product', 'Ingredient') DESC; SELECT ffm.formula_no, ffm.formula_desc1 formula_name, DECODE (fmd.line_type, 1, 'Product', 'Ingredient') TYPE, msi.segment1 icode, ood.organization_code org, fmd.qty, fmd.detail_uom, DECODE (ffm.formula_status, 700, 'Active', 'New') status FROM apps.fm_matl_dtl fmd, apps.fm_
Read more

Formula Line change query

-
  SELECT * FROM FM_MATL_DTL WHERE FORMULA_ID=(select FORMULA_ID FROM FM_FORM_MST WHERE FORMULA_NO='941306') AND RELEASE_TYPE='1' update FM_MATL_DTL set LINE_NO=3  WHERE FORMULALINE_ID='799366' and FORMULA_ID='66980' AND RELEASE_TYPE='1'; update FM_MATL_DTL set LINE_NO=1  WHERE FORMULALINE_ID='799367' and FORMULA_ID='66980' AND RELEASE_TYPE='1'; update FM_MATL_DTL set LINE_NO=2  WHERE FORMULALINE_ID='799366' and FORMULA_ID='66980' AND RELEASE_TYPE='1'; delete from FM_MATL_DTL where FORMULALINE_ID='785344' and FORMULA_ID='65865' AND RELEASE_TYPE='1'; update FM_MATL_DTL set LINE_NO=3  WHERE FORMULALINE_ID=(SELECT FORMULALINE_ID FROM FM_MATL_DTL WHERE FORMULA_ID=(select FORMULA_ID FROM FM_FORM_MST WHERE FORMULA_NO='866642') AND RELEASE_TYPE='1' AND LINE_NO='1') AND RELEASE_TYPE='1' AND LINE_NO='1'; update FM_MATL_DTL set LINE_NO=1  WHERE FO
Read more