Query to get item on hand by org wise

 Hello Below is the query to get the on hand by org wise.

Sample Output: 

-----------------------------------------------------------------------------------------------------------------------------

SELECT iop.organization_code,
esi.item_number,
sum(ioqd.TRANSACTION_QUANTITY) on_hand,
esi.description,
ESI.PRIMARY_UOM_CODE UOM
FROM egp_system_items_vl esi,
inv_org_parameters IOP,
inv_onhand_quantities_detail ioqd
WHERE 1 = 1
AND ESI.organization_id = IOP.organization_id
AND ioqd.inventory_item_id = esi.INVENTORY_ITEM_ID
AND ioqd.ORGANIZATION_ID = esi.ORGANIZATION_ID
AND esi.item_number = nvl(:p_item, esi.item_number)
AND iop.organization_code = nvl(:p_org, iop.organization_code)
GROUP BY iop.organization_code,
esi.item_number,
esi.description,
ESI.PRIMARY_UOM_CODE  
ORDER BY iop.organization_code,
esi.item_number

-----------------------------------------------------------------------------------------------------------------------------

                                                                    ** Thank You **

Query to get Item on hand & Item Cost (Cost Accounting)

 Hello Below is the query to get the item on hand quantity & item cost from the Cost Accounting.

Sample Output:

-----------------------------------------------------------------------------------------------------------------------------

WITH costing_details
     AS (SELECT *
         FROM   (SELECT Trunc(cpc.unit_cost_average, 4) unit_cost_average,
                        cpc.uom_code                    AS uom,
                        cpc.currency_code               AS curr,
                        vub.val_unit_id,
                        vub.val_unit_code,
                        cicp.inventory_item_id          item_id,
                        ccio.inv_org_id,
                        Rank()
                          OVER (
                            partition BY cicp.cost_org_id, cicp.cost_book_id,
                          cpc.inventory_item_id,
                          cpc.val_unit_id
                            ORDER BY cpc.cost_date DESC, cpc.eff_date DESC,
                          cpc.transaction_id
                          DESC )
                                                        row_num,
                        iop.organization_code           org_name,
                        cco.cost_org_name
                 FROM   cst_cost_inv_orgs ccio,
                        cst_item_cost_profiles cicp,
                        cst_cost_profiles_b ccp,
                        cst_val_units_b vub,
                        cst_val_structures_b vsb,
                        cst_val_unit_details vud,
                        cst_val_unit_combinations vuc,
                        cst_perpavg_cost cpc,
                        cst_cost_orgs_v cco,
                        inv_org_parameters iop,
                        cst_transactions ct
                 WHERE  1 = 1
                        AND ct.cost_org_id = cpc.cost_org_id
                        AND ct.cost_book_id = cpc.cost_book_id
                        AND ct.val_unit_id = cpc.val_unit_id
                        AND ct.transaction_id = cpc.transaction_id
                        AND ccio.cost_org_id = cicp.cost_org_id
                        AND ccp.cost_profile_id = cicp.asset_cost_profile_id
                        AND vub.cost_org_id = cicp.cost_org_id
                        AND vub.cost_book_id = cicp.cost_book_id
                        AND vub.val_structure_id = ccp.val_structure_id
                        AND vub.val_unit_id = vud.val_unit_id
                        AND vud.val_unit_combination_id =
                            vuc.val_unit_combination_id
                        AND ccp.val_structure_id = vsb.val_structure_id
                        AND vsb.structure_instance_number =
                            vuc.structure_instance_number
                        AND vub.cost_org_id = cco.cost_org_id
                        AND iop.organization_id = ccio.inv_org_id
                        AND vuc.cost_org_code = cco.cost_org_code
                        AND vub.val_structure_id = ccp.val_structure_id
                        AND vub.val_unit_id = vud.val_unit_id
                        AND vub.val_structure_id = vud.val_structure_id
                        AND vub.cost_book_id = vud.cost_book_id
                        AND vud.val_unit_combination_id =
                            vuc.val_unit_combination_id
                        AND Nvl(vuc.inv_org_code, iop.organization_code) =
                            iop.organization_code
                        AND cpc.cost_org_id = cicp.cost_org_id
                        AND cpc.cost_book_id = cicp.cost_book_id
                        AND cpc.inventory_item_id = cicp.inventory_item_id
                        AND cpc.val_unit_id = vub.val_unit_id
                        AND Trunc(cpc.cost_date) <= Trunc(ct.cost_date)
                        AND cpc.cost_book_id = (SELECT ccb.cost_book_id
                                                FROM   cst_cost_org_books ccb
                                                WHERE
                            cicp.cost_org_id = ccb.cost_org_id
                            AND ccb.primary_book_flag = 'Y')
                        AND vsb.val_structure_type_code = 'ASSET'
                --AND CCP.COST_PROFILE_CODE = 'FS Asset Cost Profile'
                )
         WHERE  row_num = 1)
SELECT costing_details.*,
       esi.item_number,
       Nvl((SELECT Sum(ioqd.transaction_quantity)
            FROM   inv_onhand_quantities_detail ioqd
            WHERE  ioqd.inventory_item_id = esi.inventory_item_id
                   AND ioqd.organization_id = esi.organization_id), 0) on_hand,
       IOP.organization_code,
       esi.description
FROM   costing_details,
       egp_system_items_vl esi,
       inv_org_parameters IOP
WHERE  esi.inventory_item_id = costing_details.item_id
       AND esi.organization_id = costing_details.inv_org_id
       AND ESI.organization_id = IOP.organization_id
       -- AND esi.inventory_item_id = 100000004434739
       AND esi.item_number = Nvl(:p_item, esi.item_number)
       AND iop.organization_code = Nvl(:p_org, iop.organization_code)
       AND esi.item_number = Nvl(:p_item, esi.item_number)
ORDER  BY esi.item_number,
          org_name 

-----------------------------------------------------------------------------------------------------------------------------

                                                    ** Thank You **

Query to ger Rrequisition Approval History

Below is the query used to get the latest Requisition Approved details

 --------------------------------------------------------------------------------------------

SELECT POA.ACTION_CODE,
PANF.FULL_NAME,
PRH.Requisition_Number
FROM POR_REQUISITION_HEADERS_ALL PRH,
PO_ACTION_HISTORY POA,
PER_PERSON_NAMES_F panf
WHERE 1 = 1
AND POA.OBJECT_ID = PRH.REQUISITION_HEADER_ID
AND POA.ACTION_CODE = 'APPROVE'
AND POA.PERFORMER_ID <> - 1
AND POA.SEQUENCE_NUM = (
SELECT MAX(SEQUENCE_NUM)
FROM PO_ACTION_HISTORY PAH
WHERE 1 = 1
AND PAH.ACTION_CODE = 'APPROVE'
AND PAH.PERFORMER_ID <> - 1
AND PAH.OBJECT_ID = POA.OBJECT_ID
)
AND POA.PERFORMER_ID = PANF.PERSON_ID
AND SYSDATE BETWEEN panf.effective_start_date
AND panf.effective_end_date
AND panf.NAME_TYPE = 'GLOBAL'
AND PRH.Requisition_Number IN ('REQ1105738','REQ1308571')

 --------------------------------------------------------------------------------------------

                                                           **Thank You**