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