Total Pageviews

Monday, 26 April 2021

OPM : Query to get Item wise cost in OPM

 OPM : Query to get Item wise cost in OPM


SELECT   ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group,

         SUM (NVL (cmpnt_cost, 0)) costs

    --ccm.cost_cmpntcls_code,

FROM     cm_cmpt_dtl_vw ccdv,

         cm_cldr_mst_v ccmv,

         cm_cmpt_mst ccm,

         mtl_parameters mp,

         org_organization_definitions ood,

         gl_code_combinations gcc,

         gmf_fiscal_policies gfp

   WHERE 1 = 1

     AND ccdv.period_id = ccmv.period_id

     AND TRUNC (ccmv.start_date) >= :p_start_date  

     AND TRUNC (ccmv.end_date) <= :p_end_date   

     AND ccmv.calendar_code = :p_calendar_code

     AND ccdv.cost_cmpntcls_id = ccm.cost_cmpntcls_id

     AND ccmv.cost_type_id = gfp.cost_type_id

     AND ccdv.organization_id = mp.organization_id

     AND mp.organization_id = ood.organization_id

     AND mp.material_account = gcc.code_combination_id

     AND gcc.segment1 BETWEEN :p_child_low AND :p_child_high

     AND (:p_business_unit IS NULL OR gcc.segment2 = :p_business_unit)

     AND ood.legal_entity = gfp.legal_entity_id

GROUP BY ccdv.inventory_item_id, ccdv.organization_id, ccm.cmpnt_group

No comments:

Post a Comment

FUSION: Purchasing Query

  1) ---------CPA to PO Amount---------- select poh.segment1, (select sum(nvl(cpol.quantity,1)* cpol.unit_price) from po_headers_all cpoh,po...