select Item_code
,Item_description
,round(Cost_in_Curr_Period,2)Cost_in_Curr_Period
,round(Cost_in_Comparison_Period,2)Cost_in_Comparison_Period
,round((Cost_in_Comparison_Period-Cost_in_Curr_Period),2)Cost_Varaice
,decode(Cost_in_Comparison_Period,0,0,(round(((Cost_in_Comparison_Period-Cost_in_Curr_Period)/Cost_in_Comparison_Period),2)*100)) Cost_Varaice_prec
,ORGANIZATION_CODE
,Current_period
,Comp_period
from(select msi.SEGMENT1 Item_code
,msi.DESCRIPTION Item_description
,nvl(cst.ACCTG_COST,0) Cost_in_Curr_Period
,nvl((select cst1.ACCTG_COST
from gl_item_cst cst1
,gmf_period_statuses gps1
where 1=1
and cst1.INVENTORY_ITEM_ID=cst.INVENTORY_ITEM_ID
and cst1.ORGANIZATION_ID=cst.ORGANIZATION_ID
and cst1.PERIOD_ID=gps1.PERIOD_ID
and cst1.COST_TYPE_ID=mthd.COST_TYPE_ID
and gps1.PERIOD_CODE=:P_COMP_PERIOD),0) Cost_in_Comparison_Period,god.ORGANIZATION_CODE
,:P_COST_VARAICE Cost_Varaice
,:P_CURR_PERIOD Current_period
,:P_COMP_PERIOD Comp_period
from gl_item_cst cst
,mtl_system_items_b msi
,org_organization_definitions god–gmf_organization_definitions god
,gmf_period_statuses gps
,cm_mthd_mst mthd
,gmf_legal_entities_vw2 gle
where 1=1
and cst.ORGANIZATION_ID=msi.ORGANIZATION_ID
and cst.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
and cst.ORGANIZATION_ID=god.ORGANIZATION_ID
and cst.PERIOD_ID=gps.PERIOD_ID
and cst.COST_TYPE_ID=mthd.COST_TYPE_ID
AND gle.legal_entity_id = god.legal_entity
AND gle.legal_entity_name =NVL (:p_legal_entity, gle.legal_entity_name)
— and god.ORGANIZATION_CODE in (:P_ORG_1,:P_ORG_2)
and (god.ORGANIZATION_CODE =nvl(:P_ORG_1,god.ORGANIZATION_CODE)
OR god.ORGANIZATION_CODE =nvl(:P_ORG_2,god.ORGANIZATION_CODE))
and mthd.COST_MTHD_CODE=:P_COST_TYPE
and gps.PERIOD_CODE=:P_CURR_PERIOD)
where 1=1
and abs(round((Cost_in_Comparison_Period-Cost_in_Curr_Period),2))>=:P_COST_VARAICE
ORDER BY ORGANIZATION_CODE,Item_code
Recent Posts