ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E11C2759D578351AE0530100007FFBF1Y select z.ledger, z.operating_unit, z.org_code, z.organization_name, z.class_code wip_class, xxen_util.meaning(z.class_type,'WIP_CLASS_TYPE',700) class_type, z.wip_entity_name wip_job, xxen_util.meaning(z.status_type,'WIP_JOB_STATUS',700) job_status, z.date_released, z.date_completed, z.last_update_date, z.item_number, z.item_description, &category_columns xxen_util.meaning(z.item_type,'ITEM_TYPE',3) item_type, z.item_status, xxen_util.meaning(z.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, xxen_util.meaning(z.wip_supply_type,'WIP_SUPPLY',700) supply_type, z.transaction_type, z.resource_code, z.overhead_code, z.op_seq_num operation_seq_number, z.res_seq_num resource_seq_number, xxen_util.meaning(z.basis_type,'CST_BASIS',700) basis_type, xxen_util.meaning(z.ovhd_basis_type,'CST_BASIS',700) overhead_basis_type, z.currency_code, z.uom_code, round(z.material1,5) new_material_cost, round(z.material2,5) old_material_cost, round(z.moh1,5) new_material_overhead_cost, round(z.moh2,5) old_material_overhead_cost, round(z.resource1,5) new_resource_cost, round(z.resource2,5) old_resource_cost, round(z.osp1,5) new_outside_processing_cost, round(z.osp2,5) old_outside_processing_cost, round(z.overhead1,5) new_overhead_cost, round(z.overhead2,5) old_overhead_cost, round(z.item_cost1,5) new_gross_item_cost, round(z.item_cost2,5) old_gross_item_cost, round(z.pii1,5) new_pii_cost, round(z.pii2,5) old_pii_cost, round(z.item_cost1,5)-z.pii_sign*round(z.pii1,5) new_net_item_cost, round(z.item_cost2,5)-z.pii_sign*round(z.pii2,5) old_net_item_cost, round(z.item_cost1,5)-round(z.item_cost2,5) gross_item_cost_difference, case when round(z.item_cost1,5)=round(z.item_cost2,5) then 0 when round(z.item_cost2,5)=0 then 100 else round((z.item_cost1-z.item_cost2)/z.item_cost2*100,2) end gross_percent_difference, round(z.pii1,5)-round(z.pii2,5) pii_item_cost_difference, case when round(z.pii1-z.pii2,5)=0 then 0 when round(z.pii1-z.pii2,5)=round(z.pii1,5) then 100 when round(z.pii2-z.pii1,5)=round(z.pii2,5) then -100 else round((z.pii1-z.pii2)/z.pii2*100,1) end pii_percent_difference, round(z.item_cost1,5)-z.pii_sign*round(z.pii1,5)-(round(z.item_cost2,5)-z.pii_sign*round(z.pii2,5)) net_item_cost_difference, z.wip_quantity, z.value_sign*round(z.item_cost1*z.quantity1,2) new_gross_wip_value, z.value_sign*round(z.pii_sign*z.pii1*z.quantity1,2) new_pii_value, z.value_sign*round((z.item_cost1-z.pii_sign*z.pii1)*z.quantity1,2) new_net_wip_value, z.value_sign*round(z.item_cost2*z.quantity2,2) old_gross_wip_value, z.value_sign*round(z.pii_sign*z.pii2*z.quantity2,2) old_pii_value, z.value_sign*round((z.item_cost2-z.pii_sign*z.pii2)*z.quantity2,2) old_net_wip_value, z.value_sign*(round(z.item_cost1*z.quantity1,2)-round(z.item_cost2*z.quantity2,2)) gross_wip_value_difference, abs(round(z.item_cost1*z.quantity1,2)-round(z.item_cost2*z.quantity2,2)) abs_wip_value_difference, z.value_sign*round(z.pii_sign*(z.pii1-z.pii2)*z.quantity1,2) pii_value_difference, z.value_sign*round(z.item_cost1*z.quantity1-z.item_cost2*z.quantity2-z.pii_sign*(z.pii1*z.quantity1-z.pii2*z.quantity2),2) net_wip_value_difference, z.rate1 new_fx_rate, z.rate2 old_fx_rate, z.rate1-z.rate2 exchange_rate_difference, z.value_sign*round(z.material1*z.rate1*z.quantity1,2) "&p_to_currency_code New Material Value", z.value_sign*round(z.material2*z.rate2*z.quantity2,2) "&p_to_currency_code Old Material Value", z.value_sign*round(z.moh1*z.rate1*z.quantity1,2) "&p_to_currency_code New Material Ovhd Value", z.value_sign*round(z.moh2*z.rate2*z.quantity2,2) "&p_to_currency_code Old Material Ovhd Value", z.value_sign*round(z.resource1*z.rate1*z.quantity1,2) "&p_to_currency_code New Resource Value", z.value_sign*round(z.resource2*z.rate2*z.quantity2,2) "&p_to_currency_code Old Resource Value", z.value_sign*round(z.osp1*z.rate1*z.quantity1,2) "&p_to_currency_code New OSP Value", z.value_sign*round(z.osp2*z.rate2*z.quantity2,2) "&p_to_currency_code Old OSP Value", z.value_sign*round(z.overhead1*z.rate1*z.quantity1,2) "&p_to_currency_code New Overhead Value", z.value_sign*round(z.overhead2*z.rate2*z.quantity2,2) "&p_to_currency_code Old Overhead Value", z.value_sign*round(z.item_cost1*z.quantity1*z.rate1,2) "&p_to_currency_code New Gross WIP Value", z.value_sign*round(z.item_cost2*z.quantity2*z.rate2,2) "&p_to_currency_code Old Gross WIP Value", z.value_sign*(round(z.item_cost1*z.quantity1*z.rate1,2)-round(z.item_cost2*z.quantity2*z.rate2,2)) "&p_to_currency_code Gross WIP Value Diff", abs(round(z.item_cost1*z.quantity1*z.rate1,2)-round(z.item_cost2*z.quantity2*z.rate2,2)) "&p_to_currency_code Abs WIP Value Diff", z.value_sign*round(z.pii_sign*z.pii1*z.rate1*z.quantity1,2) "&p_to_currency_code New PII Value", z.value_sign*round(z.pii_sign*z.pii2*z.rate2*z.quantity2,2) "&p_to_currency_code Old PII Value", z.value_sign*round(z.pii_sign*(z.pii1*z.rate1-z.pii2*z.rate2)*z.quantity1,2) "&p_to_currency_code PII Value Difference", z.value_sign*round((z.item_cost1-z.pii_sign*z.pii1)*z.rate1*z.quantity1,2) "&p_to_currency_code New Net Value", z.value_sign*round((z.item_cost2-z.pii_sign*z.pii2)*z.rate2*z.quantity2,2) "&p_to_currency_code Old Net Value", z.value_sign*round(((z.item_cost1-z.pii_sign*z.pii1)*z.rate1*z.quantity1-(z.item_cost2-z.pii_sign*z.pii2)*z.rate2*z.quantity2),2) "&p_to_currency_code Net Value Difference", z.value_sign*round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate1,2) "&p_to_currency_code Gross Value Diff-New Rate", z.value_sign*round(z.pii_sign*(z.pii1-z.pii2)*z.rate1*z.quantity1,2) "&p_to_currency_code PII Value Diff-New Rate", z.value_sign*(round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate1,2)-round(z.pii_sign*(z.pii1-z.pii2)*z.rate1*z.quantity1,2)) "&p_to_currency_code Net Value Diff-New Rate", z.value_sign*round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate2,2) "&p_to_currency_code Gross Value Diff-Old Rate", z.value_sign*round(z.pii_sign*(z.pii1-z.pii2)*z.rate2*z.quantity1,2) "&p_to_currency_code PII Value Diff-Old Rate", z.value_sign*(round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate2,2)-round(z.pii_sign*(z.pii1-z.pii2)*z.rate2*z.quantity1,2)) "&p_to_currency_code Net Value Diff-Old Rate", z.value_sign*(round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate1,2)-round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate2,2)) "&p_to_currency_code Gross Value FX Diff", z.value_sign*(round(z.pii_sign*(z.pii1-z.pii2)*z.rate1*z.quantity1,2)-round(z.pii_sign*(z.pii1-z.pii2)*z.rate2*z.quantity1,2)) "&p_to_currency_code PII Value FX Diff", z.value_sign*(round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate1,2)-round(z.pii_sign*(z.pii1-z.pii2)*z.rate1*z.quantity1,2)-(round((z.item_cost1*z.quantity1-z.item_cost2*z.quantity2)*z.rate2,2)-round(z.pii_sign*(z.pii1-z.pii2)*z.rate2*z.quantity1,2))) "&p_to_currency_code Net Value FX Diff" from ( select y.ledger, y.operating_unit, y.org_code, y.organization_name, y.currency_code, y.organization_id, y.inventory_item_id, y.wip_entity_name, y.class_code, y.class_type, y.status_type, y.date_released, y.date_completed, y.last_update_date, y.transaction_type, y.resource_code, y.overhead_code, y.op_seq_num, y.res_seq_num, y.basis_type, y.ovhd_basis_type, y.wip_supply_type, y.value_sign, msiv.concatenated_segments item_number, msiv.description item_description, msiv.item_type, misv.inventory_item_status_code_tl item_status, msiv.planning_make_buy_code, msiv.primary_uom_code uom_code, decode(y.row_type,'O',round(decode(y.overhead_rate1,0,y.quantity2,y.quantity1),3),y.quantity) wip_quantity, decode(y.row_type,'O',y.quantity1,y.quantity) quantity1, decode(y.row_type,'O',y.quantity2,y.quantity) quantity2, decode(y.cost_source,'M',nvl(cic1.material_cost,0),0) material1, decode(y.cost_source,'M',nvl(cic2.material_cost,0),0) material2, decode(y.cost_source,'M',round(nvl(cic1.material_overhead_cost,0)-decode(y.this_level,'Y',nvl(cic1.tl_material_overhead,0),0),5),0) moh1, decode(y.cost_source,'M',round(nvl(cic2.material_overhead_cost,0)-decode(y.this_level,'Y',nvl(cic2.tl_material_overhead,0),0),5),0) moh2, decode(y.cost_source,'M',nvl(cic1.resource_cost,0),'R',decode(y.cost_element_id,3,nvl(crc1.resource_rate,0),0),0) resource1, decode(y.cost_source,'M',nvl(cic2.resource_cost,0),'R',decode(y.cost_element_id,3,nvl(crc2.resource_rate,0),0),0) resource2, decode(y.cost_source,'M',nvl(cic1.outside_processing_cost,0),'R',decode(y.cost_element_id,4,nvl(crc1.resource_rate,0),0),0) osp1, decode(y.cost_source,'M',nvl(cic2.outside_processing_cost,0),'R',decode(y.cost_element_id,4,nvl(crc2.resource_rate,0),0),0) osp2, decode(y.cost_source,'M',nvl(cic1.overhead_cost,0),'O',y.overhead_rate1,0) overhead1, decode(y.cost_source,'M',nvl(cic2.overhead_cost,0),'O',y.overhead_rate2,0) overhead2, decode(y.cost_source,'M',round(nvl(cic1.item_cost,0)-decode(y.this_level,'Y',nvl(cic1.tl_material_overhead,0),0),5),'R',nvl(crc1.resource_rate,0),y.overhead_rate1) item_cost1, decode(y.cost_source,'M',round(nvl(cic2.item_cost,0)-decode(y.this_level,'Y',nvl(cic2.tl_material_overhead,0),0),5),'R',nvl(crc2.resource_rate,0),y.overhead_rate2) item_cost2, decode(y.cost_source,'M',nvl(cic1.item_cost,0),'R',nvl(crc1.resource_rate,0),y.overhead_rate1*y.quantity1) filter1, decode(y.cost_source,'M',nvl(cic2.item_cost,0),'R',nvl(crc2.resource_rate,0),y.overhead_rate2*y.quantity2) filter2, decode(y.currency_code,:p_to_currency_code,1,(select gdr1.conversion_rate from gl_daily_conversion_types gdct1, gl_daily_rates gdr1 where 4=4 and 5=5 and 11=11 and gdct1.conversion_type=gdr1.conversion_type and y.currency_code=gdr1.from_currency)) rate1, decode(y.currency_code,:p_to_currency_code,1,(select gdr2.conversion_rate from gl_daily_conversion_types gdct2, gl_daily_rates gdr2 where 6=6 and 7=7 and gdct2.conversion_type=gdr2.conversion_type and y.currency_code=gdr2.from_currency and gdr2.to_currency=:p_to_currency_code)) rate2, decode(y.row_type,'R',0,'O',0,decode(msiv.inventory_asset_flag,'Y',nvl((select sum(cicd.item_cost) from cst_cost_types cct, cst_item_cost_details cicd, bom_resources br where 12=12 and 14=14 and cct.cost_type_id=cicd.cost_type_id and y.organization_id=cicd.organization_id and y.inventory_item_id=cicd.inventory_item_id and cicd.resource_id=br.resource_id),0),0)) pii1, decode(y.row_type,'R',0,'O',0,decode(msiv.inventory_asset_flag,'Y',nvl((select sum(cicd.item_cost) from cst_cost_types cct, cst_item_cost_details cicd, bom_resources br where 13=13 and 14=14 and cct.cost_type_id=cicd.cost_type_id and y.organization_id=cicd.organization_id and y.inventory_item_id=cicd.inventory_item_id and cicd.resource_id=br.resource_id),0),0)) pii2, decode(sign(:p_sign_pii),-1,-1,1) pii_sign from ( select j.ledger, j.operating_unit, j.org_code, j.organization_name, j.currency_code, j.organization_id, j.wip_entity_name, j.class_code, j.class_type, j.status_type, j.date_released, j.date_completed, j.last_update_date, nvl(d.inventory_item_id,j.primary_item_id) inventory_item_id, d.row_type, decode(d.row_type,'C','M',d.row_type) cost_source, d.transaction_type, d.resource_code, d.overhead_code, d.op_seq_num, d.res_seq_num, d.basis_type, d.ovhd_basis_type, d.wip_supply_type, decode(d.row_type,'C',-1,1) value_sign, case when d.row_type='C' and j.class_type in (1,5) then 'Y' end this_level, d.quantity, d.resource_id, d.cost_element_id, d.overhead_rate1, d.overhead_rate2, d.quantity1, d.quantity2, case when d.row_type in ('C','M') then nvl((select cic.cost_type_id from cst_cost_types cct, cst_item_costs cic where cct.cost_type=:p_cost_type1 and cct.cost_type_id=cic.cost_type_id and j.organization_id=cic.organization_id and nvl(d.inventory_item_id,j.primary_item_id)=cic.inventory_item_id),1) when d.row_type='R' then nvl((select crc.cost_type_id from cst_cost_types cct, cst_resource_costs crc where cct.cost_type=:p_cost_type1 and cct.cost_type_id=crc.cost_type_id and j.organization_id=crc.organization_id and d.resource_id=crc.resource_id),1) end cost_type_id1, case when d.row_type in ('C','M') then nvl((select cic.cost_type_id from cst_cost_types cct, cst_item_costs cic where cct.cost_type=:p_cost_type2 and cct.cost_type_id=cic.cost_type_id and j.organization_id=cic.organization_id and nvl(d.inventory_item_id,j.primary_item_id)=cic.inventory_item_id),1) when d.row_type='R' then nvl((select crc.cost_type_id from cst_cost_types cct, cst_resource_costs crc where cct.cost_type=:p_cost_type2 and cct.cost_type_id=crc.cost_type_id and j.organization_id=crc.organization_id and d.resource_id=crc.resource_id),1) end cost_type_id2 from ( select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, haou.name organization_name, gl.currency_code, wdj.organization_id, wdj.wip_entity_id, we.wip_entity_name, wdj.primary_item_id, wdj.class_code, wac.class_type, wdj.status_type, wdj.date_released, wdj.date_completed, wdj.last_update_date from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, wip_discrete_jobs wdj, wip_entities we, wip_accounting_classes wac where 1=1 and 9=9 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and sysdate<nvl(haou.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=wdj.organization_id and wdj.date_closed is null and nvl(wdj.issue_zero_cost_flag,'N')='N' and wdj.wip_entity_id=we.wip_entity_id and wdj.class_code=wac.class_code and wdj.organization_id=wac.organization_id and wac.class_type not in (4,6,7) ) j, ( select 'C' row_type, wdj.wip_entity_id, to_number(null) inventory_item_id, (select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_type_id=44) transaction_type, null resource_code, null overhead_code, to_number(null) op_seq_num, to_number(null) res_seq_num, 1 basis_type, to_number(null) ovhd_basis_type, to_number(null) wip_supply_type, wdj.quantity_completed quantity, to_number(null) resource_id, to_number(null) cost_element_id, to_number(null) overhead_rate1, to_number(null) overhead_rate2, to_number(null) quantity1, to_number(null) quantity2 from wip_discrete_jobs wdj where wdj.quantity_completed+wdj.quantity_scrapped<>0 union all select 'M' row_type, wro.wip_entity_id, wro.inventory_item_id, (select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_type_id=decode(sign(wro.quantity_issued),1,35,43)) transaction_type, null resource_code, null overhead_code, wro.operation_seq_num op_seq_num, null res_seq_num, nvl(wro.basis_type,1) basis_type, null ovhd_basis_type, wro.wip_supply_type, wro.quantity_issued quantity, null resource_id, null cost_element_id, null overhead_rate1, null overhead_rate2, null quantity1, null quantity2 from wip_requirement_operations wro where wro.quantity_issued<>0 union all select 'R' row_type, wor.wip_entity_id, null inventory_item_id, xxen_util.meaning(decode(br.cost_element_id,3,1,4,3),'WIP_TRANSACTION_TYPE',700) transaction_type, br.resource_code, null overhead_code, wor.operation_seq_num op_seq_num, wor.resource_seq_num res_seq_num, wor.basis_type, null ovhd_basis_type, (select max(wro.wip_supply_type) from wip_requirement_operations wro where wor.wip_entity_id=wro.wip_entity_id and wor.operation_seq_num=wro.operation_seq_num) wip_supply_type, round(wor.applied_resource_units,3) quantity, wor.resource_id, br.cost_element_id, null overhead_rate1, null overhead_rate2, null quantity1, null quantity2 from wip_operation_resources wor, bom_resources br where wor.applied_resource_units<>0 and wor.resource_id=br.resource_id and br.cost_element_id in (3,4) union all select 'O' row_type, o.wip_entity_id, null inventory_item_id, xxen_util.meaning(2,'WIP_TRANSACTION_TYPE',700) transaction_type, o.resource_code, o.overhead_code, o.op_seq_num, o.res_seq_num, o.res_basis_type basis_type, o.ovhd_basis_type, (select max(wro.wip_supply_type) from wip_requirement_operations wro where o.wip_entity_id=wro.wip_entity_id and o.op_seq_num=wro.operation_seq_num) wip_supply_type, null quantity, null resource_id, null cost_element_id, nvl(sum(decode(o.side,1,o.overhead_rate)),0) overhead_rate1, nvl(sum(decode(o.side,2,o.overhead_rate)),0) overhead_rate2, nvl(sum(decode(o.side,1,o.quantity)),0) quantity1, nvl(sum(decode(o.side,2,o.quantity)),0) quantity2 from ( select wo.wip_entity_id, null resource_code, br_ovhd.resource_code overhead_code, wo.operation_seq_num op_seq_num, to_number(null) res_seq_num, to_number(null) res_basis_type, cdo.basis_type ovhd_basis_type, cdo.side, cdo.overhead_rate, decode(cdo.basis_type,1,wo.quantity_completed,2,1) quantity from wip_operations wo, ( select rowgen.column_value side, cdo.organization_id, cdo.department_id, cdo.overhead_id, cdo.cost_type_id, cdo.basis_type, nvl(cdo.rate_or_amount,0) overhead_rate from table(xxen_util.rowgen(2)) rowgen, cst_cost_types cct, cst_department_overheads cdo where cct.cost_type=decode(rowgen.column_value,1,:p_cost_type1,:p_cost_type2) and (cdo.cost_type_id=cct.cost_type_id or cdo.cost_type_id=1 and cct.cost_type_id<>1 and (cdo.organization_id,cdo.department_id,cdo.overhead_id) not in (select cdo2.organization_id, cdo2.department_id, cdo2.overhead_id from cst_department_overheads cdo2 where cct.cost_type_id=cdo2.cost_type_id)) ) cdo, bom_resources br_ovhd where wo.quantity_completed<>0 and wo.organization_id=cdo.organization_id and wo.department_id=cdo.department_id and cdo.basis_type in (1,2) and cdo.overhead_id=br_ovhd.resource_id union all select wor.wip_entity_id, br.resource_code, br_ovhd.resource_code overhead_code, wor.operation_seq_num op_seq_num, wor.resource_seq_num res_seq_num, wor.basis_type res_basis_type, cdo.basis_type ovhd_basis_type, cdo.side, cdo.overhead_rate, decode(cdo.basis_type,3,wor.applied_resource_units,4,wor.applied_resource_units*nvl((select max(crc.resource_rate) keep (dense_rank first order by decode(crc.cost_type_id,1,2,1)) from cst_resource_costs crc where wor.organization_id=crc.organization_id and wor.resource_id=crc.resource_id and crc.cost_type_id in (cdo.requested_cost_type_id,1)),0)) quantity from wip_operations wo, wip_operation_resources wor, bom_resources br, cst_resource_overheads cro, ( select rowgen.column_value side, cct.cost_type_id requested_cost_type_id, cdo.organization_id, cdo.department_id, cdo.overhead_id, cdo.cost_type_id, cdo.basis_type, nvl(cdo.rate_or_amount,0) overhead_rate from table(xxen_util.rowgen(2)) rowgen, cst_cost_types cct, cst_department_overheads cdo where cct.cost_type=decode(rowgen.column_value,1,:p_cost_type1,:p_cost_type2) and (cdo.cost_type_id=cct.cost_type_id or cdo.cost_type_id=1 and cct.cost_type_id<>1 and (cdo.organization_id,cdo.department_id,cdo.overhead_id) not in (select cdo2.organization_id, cdo2.department_id, cdo2.overhead_id from cst_department_overheads cdo2 where cct.cost_type_id=cdo2.cost_type_id)) ) cdo, bom_resources br_ovhd where wo.wip_entity_id=wor.wip_entity_id and wo.operation_seq_num=wor.operation_seq_num and wo.organization_id=wor.organization_id and wor.applied_resource_units<>0 and wor.resource_id=br.resource_id and wo.organization_id=cdo.organization_id and wo.department_id=cdo.department_id and cdo.basis_type in (3,4) and cdo.cost_type_id=cro.cost_type_id and cdo.organization_id=cro.organization_id and cdo.overhead_id=cro.overhead_id and wor.resource_id=cro.resource_id and cdo.overhead_id=br_ovhd.resource_id ) o group by o.wip_entity_id, o.resource_code, o.overhead_code, o.op_seq_num, o.res_seq_num, o.res_basis_type, o.ovhd_basis_type ) d where j.wip_entity_id=d.wip_entity_id and (d.row_type<>'O' or j.date_released is not null) ) y, mtl_system_items_vl msiv, mtl_item_status_vl misv, cst_item_costs cic1, cst_item_costs cic2, cst_resource_costs crc1, cst_resource_costs crc2 where 15=15 and y.organization_id=msiv.organization_id and y.inventory_item_id=msiv.inventory_item_id and msiv.inventory_item_status_code=misv.inventory_item_status_code and decode(y.row_type,'C',y.inventory_item_id,'M',y.inventory_item_id)=cic1.inventory_item_id(+) and y.organization_id=cic1.organization_id(+) and y.cost_type_id1=cic1.cost_type_id(+) and decode(y.row_type,'C',y.inventory_item_id,'M',y.inventory_item_id)=cic2.inventory_item_id(+) and y.organization_id=cic2.organization_id(+) and y.cost_type_id2=cic2.cost_type_id(+) and y.resource_id=crc1.resource_id(+) and y.organization_id=crc1.organization_id(+) and y.cost_type_id1=crc1.cost_type_id(+) and y.resource_id=crc2.resource_id(+) and y.organization_id=crc2.organization_id(+) and y.cost_type_id2=crc2.cost_type_id(+) and (y.row_type not in ('C','M') or cic1.cost_type_id=1 or cic1.based_on_rollup_flag=1 and cic1.item_cost is not null or cic1.based_on_rollup_flag=2) and (y.row_type<>'R' or crc1.resource_id is not null) ) z where (:p_all_wip_jobs='Y' or nvl(abs(round(z.filter1*z.rate1,5)-round(z.filter2*z.rate2,5))+abs(round(z.pii1*z.rate1,5)-round(z.pii2*z.rate2,5)),abs(round(z.filter1,5)-round(z.filter2,5))+abs(round(z.pii1,5)-round(z.pii2,5)))<>0) order by z.ledger, z.operating_unit, z.org_code, z.organization_name, z.class_code, z.wip_entity_name, z.transaction_type, z.item_number, z.op_seq_num, z.res_seq_num Rebuilt to the Blitz Report SQL standards: one pass over completions, component issues, resources and overheads, overheads of jobs that were never released are excluded, rows without a make or buy code are no longer missing, the duplicate UOM Code column is removed. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. #,##0.00000;[Red](#,##0.00000) US CAC ICP PII WIP Pending Cost Adjustment Report showing the potential standard cost changes for WIP discrete jobs, for the WIP completions, WIP component issues and WIP resource (labor) transactions, including gross costs, profit in inventory (commonly abbreviated as PII or ICP - InterCompany Profit). Production overheads (resource overheads) are reported per WIP operation and overhead, from the department overhead rates of the two cost types. Move-based overheads earn on the operation quantity completed (Item basis) or once per operation (Lot basis); resource-based overheads earn on the applied resource units (Resource Units basis) or on the applied resource units at the resource rate of each cost type (Resource Value basis), for the overheads assigned to the resource. For these rows the WIP Quantity is this overhead basis quantity. The Cost Type (Old) defaults to your Costing Method Cost Type (Average, Standard, etc.); the Currency Conversion Dates default to the latest open or closed accounting period; and the To Currency Code and the Organization Code default from the organization code set for this session. And if you choose Yes for "Include All WIP Jobs" all WIP jobs will be reported even if there are no valuation changes. The To Currency columns are blank when no conversion rate exists for the date and type. -- ================================================================= Copyright 2022 Douglas Volz Consulting, Inc. All rights reserved. Permission to use this code is granted provided the original author is acknowledged Original Author: Douglas Volz (doug@volzconsulting.com) -- ================================================================= Hidden Parameters: p_sign_pii: Hidden parameter to set the sign of the profit in inventory amounts. This parameter determines if PII is normally entered as a positive or negative amount. Displayed Parameters: Cost Type (New): the new cost type to be reported, mandatory Cost Type (Old): the old cost type to be reported, mandatory PII Cost Type (New): the new PII_Cost_Type you wish to report PII Cost Type(Old): the prior or old PII_Cost_Type you wish to report such as PII or ICP (mandatory) PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP (mandatory) Currency Conversion Date (New): the new currency conversion date, mandatory Currency Conversion Date (Old): the old currency conversion date, mandatory Currency Conversion Type (New): the desired currency conversion type to use for cost type 1, mandatory Currency Conversion Type (Old ): the desired currency conversion type to use for cost type 2, mandatory To Currency Code: the currency you are converting into Category Set 1: the first item category set to report, typically the Cost or Product Line Category_Set Category Set 2: the second item category set to report, typically the Inventory Category_Set All WIP Jobs: enter No to only report WIP jobs with valuation changes, enter Yes to report all WIP jobs. Org Code: specific inventory organization you wish to report (optional) Operating Unit: operating unit you wish to report, leave blank for all operating units (optional) Ledger: general ledger you wish to report, leave blank for all ledgers (optional) -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.6 08 Jun 2022 Douglas Volz Create PII version based on WIP Pending Cost Adjust Rept. -- | 1.7 19 Sep 2026 Enginatics Add production (resource) overhead cost adjustments, -- | correct the net value, percentage and FX columns. -- | 1.8 28 Sep 2026 Enginatics Report rows without a currency conversion rate. -- | 1.10 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. Enginatics &category_columns &p_to_currency_code 11=11 12=12 13=13 14=14 15=15 1=1 4=4 5=5 6=6 7=7 9=9 :p_all_wip_jobs :p_cost_type1 :p_cost_type2 :p_sign_pii :p_to_currency_code 1 -20 :p_sign_pii Number 1 US Numeric Sign for PII 2 10 :p_cost_type1 LOV CST Cost Type (No AvgRates No PII) A486BD05719A2A82E053BB6B6358FC8E select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type Y US Cost Type (New) 3 20 :p_cost_type2 LOV CST Cost Type (No AvgRates No PII) A486BD05719A2A82E053BB6B6358FC8E select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type select cct.cost_type from cst_cost_types cct, mtl_parameters mp where cct.cost_type_id = mp.primary_cost_method and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Cost Type (Old) 4 30 12=12 cct.cost_type = :p_pii_cost_type_new LOV CST Cost Type (No AvgRates) 91D3167A335B1204E053BB6B63585EE3 select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type desc PII Y US PII Cost Type (New) 5 40 13=13 cct.cost_type = :p_pii_cost_type_old LOV CST Cost Type (No AvgRates) 91D3167A335B1204E053BB6B63585EE3 select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type desc PII Y US PII Cost Type (Old) 6 50 14=14 br.resource_code = :p_pii_sub_element LOV CST PII Sub-Elements E234C6573B214FDAE05369FB090533D7 select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code PII Y US PII Sub-Element 7 60 4=4 gdr1.conversion_date = :p_curr_conv_date1 Date select oap.schedule_close_date value, oap.period_name description from org_acct_periods oap where oap.acct_period_id = (select max(oap2.acct_period_id) from org_acct_periods oap2 where oap2.schedule_close_date = (select max(oap3.schedule_close_date) from org_acct_periods oap3, org_organization_definitions ood where ood.organization_id = oap3.organization_id and nvl(ood.disable_date,sysdate + 1) > sysdate ) ) Y US Currency Conversion Date (New) 8 70 5=5 gdct1.user_conversion_type = :p_curr_conv_type1 LOV GL Currency Conversion Type 91D3167A335F1204E053BB6B63585EE3 select gdct.conversion_type id, gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by gdct.user_conversion_type value Corporate Y US Currency Conversion Type (New) 9 80 6=6 gdr2.conversion_date = :p_curr_conv_date2 Date select oap.schedule_close_date value, oap.period_name description from org_acct_periods oap where oap.acct_period_id = (select max(oap2.acct_period_id) from org_acct_periods oap2 where oap2.schedule_close_date = (select max(oap3.schedule_close_date) from org_acct_periods oap3, org_organization_definitions ood where ood.organization_id = oap3.organization_id and nvl(ood.disable_date,sysdate + 1) > sysdate ) ) Y US Currency Conversion Date (Old) 10 90 7=7 gdct2.user_conversion_type = :p_curr_conv_type2 LOV GL Currency Conversion Type 91D3167A335F1204E053BB6B63585EE3 select gdct.conversion_type id, gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by gdct.user_conversion_type value Corporate Y US Currency Conversion Type (Old) 11 100 11=11 gdr1.to_currency = :p_to_currency_code LOV FND Currency 8E2FF36EDE9079D2E0530100007F1FF2 select fcv.currency_code id, fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code select gl.currency_code from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and mp.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') Y US To Currency Code 12 &p_to_currency_code <parameter_value> US To Currency Code 13 110 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'z') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 14 120 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'z') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 15 130 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'z') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 16 140 :p_all_wip_jobs LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y US Include All WIP Jobs 17 150 9=9 mp.organization_code=:p_org_code LOV custom select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 18 160 1=1 haouv_ou.name=:p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and (:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 19 170 1=1 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger