Cost Comp for OPM Organization

===== Cost Comp for OPM Organization ==========

Select OU_NAME,OU_UNIT,ORG_CODE,ORG_NAME,Item_COED,description,UOM,ITEM_TYPE,PERIOD_CODE, pvs_mth2,pvs_mth1,cur_mth,
(cur_mth - pvs_mth1) Diff,
TO_CHAR(ROUND ((  (  (cur_mth - pvs_mth1)
                    / (CASE
                          WHEN (cur_mth) = 0
                             THEN 1
                          ELSE (cur_mth)
                       END
                      )
                   )
                 * 100
                ),
                6
               )) PP_VR
from
(SELECT ood.OPERATING_UNIT OU_UNIT, hr.NAME OU_NAME,msib.segment1 Item_COED, msib.description,msib.primary_uom_code UOM,
msib.ITEM_TYPE, mp.organization_code ORG_CODE,
       ood.organization_name ORG_NAME,ps.PERIOD_ID,ps.PERIOD_CODE,
     
       trunc(xxprg_item_cost1 (msib.organization_id,
                  msib.inventory_item_id,
                    (SELECT ps.PERIOD_ID FROM GMF_PERIOD_STATUSES PS WHERE TRUNC(END_DATE) = (
                    LAST_DAY(ADD_MONTHS((SELECT TRUNC(START_DATE) FROM GMF_PERIOD_STATUSES PS WHERE PERIOD_ID=:P_PERIOD_ID),-1))))
                 ),4) pvs_mth1,
        trunc(xxprg_item_cost1 (msib.organization_id,
                  msib.inventory_item_id,
                  ps.PERIOD_ID
                 ),4)cur_mth
  FROM mtl_system_items_b msib,
       mtl_parameters mp,
       org_organization_definitions ood,
      HR_OPERATING_UNITS hr,
       GMF_PERIOD_STATUSES PS
 WHERE ood.organization_id = mp.organization_id
   AND msib.organization_id = mp.organization_id
   --AND ood.BUSINESS_GROUP_ID = BU.BUSINESS_GROUP_ID
   AND ood.operating_unit = hr.ORGANIZATION_ID
   AND ood.operating_unit = :P_OU_ID
   -----AND ps.PERIOD_ID = :P_PERIOD_ID-----
   ---and ps.PERIOD_CODE =:P_PERIOD_NAME
  ---- and msib.ITEM_TYPE in----
   ---AND mp.process_enabled_flag -------
   --AND mp.organization_id <> 102
   AND EXISTS (
          SELECT 1
            FROM mtl_material_transactions
           WHERE organization_id = mp.organization_id
             AND inventory_item_id = msib.inventory_item_id
          AND TRUNC(TRANSACTION_DATE) BETWEEN TO_DATE(TO_CHAR(PS.START_DATE,'dd-mon-yyyy HH24:MI:SS' ), 'dd-mon-yyyy HH24:MI:SS') AND 
                TO_DATE(TO_CHAR(PS.END_DATE,'dd-mon-yyyy HH24:MI:SS' ), 'dd-mon-yyyy HH24:MI:SS'))
  order by  ood.organization_name)
  group by OU_UNIT,OU_NAME,Item_COED,description,UOM,ITEM_TYPE,ORG_CODE,ORG_NAME,PERIOD_CODE,pvs_mth2,pvs_mth1,cur_mth
  order by ORG_NAME ASC