OPM WIP Value
Description
SELECT (CASE WHEN wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE AND TRUNC (wdj.DATE_CLOSED) < OAP.SCHEDULE_CLOSE_DATE + 1 THEN 'Variance' WHEN wdj.date_closed IS NULL -- the job is OPEN AND TRUNC (wdj.CREATION_DATE) <= OAP.SCHEDULE_CLOSE_DATE THEN 'Valuation' WHEN wdj.DATE_CLOSED IS NOT NULL -- the job is closed and ...the job was closed after the accounting period AND TRUNC (wdj.DATE_CLOSED) > OAP.SCHEDULE_CLOSE_DATE THEN 'Valuation' END) "Report Type", gl.name "Ledger", haou2.name "Operating Unit", mp.organization_code "Org Code", oap.period_name "Period Name", gcc2.segment1 "Co", gcc1.segment2 "Cost Ctr", gcc1.segment3 "Acct", gcc1.segment4 "Sub-Acct", gcc2.segment5 "Prod Grp", gcc1.segment6 "Loc", gcc1.segment7 "InterCo", gcc1.segment8 "Future1", gcc1.segment9 "Future2", wac.class_code "WIP Class", ml2.meaning "Class Type", we.wip_entity_name "Batch or WIP Job", ml.meaning "Batch or Job Status", TO_CHAR (wdj.date_completed, 'DD-MON-YYYY') "Date Completed", TO_CHAR (wdj.date_closed, 'DD-MON-YYYY') "Date Closed", TO_CHAR (wdj.last_update_date, 'DD-MON-YYYY') "Last Updated", NVL (wdj.START_QUANTITY, 0) "Start Quantity", NVL (wdj.quantity_completed, 0) "FG Quantity Completed", NVL (wdj.quantity_scrapped, 0) "FG Quantity Scrapped", NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0) "Total FG Quantity", msi.segment1 "FG Item Number", msi.description "FG Description", msi2.segment1 "Component or Ingredient", msi2.description "Component or Ingredient Desc", gl.currency_code "Curr Code", SUM (cic.item_cost) "Gross Item Cost", NVL ( (SELECT SUM (NVL (cicd.ITEM_COST, 0)) FROM apps.cst_item_cost_details cicd, apps.cst_cost_types cct, apps.bom_resources br WHERE 2=2 and cicd.inventory_item_id = msi2.inventory_item_id AND cicd.organization_id = msi2.organization_id AND br.resource_id = cicd.resource_id AND br.resource_code = 'ICP' AND CCT.COST_TYPE_ID = CICD.COST_TYPE_ID), -- AND cct.cost_type = :P_ICP_COST_TYPE), 0) "ICP Item Cost", -- P_COST_TYPE msi2.primary_uom_code "UOM Code", ROUND ( SUM ( (DECODE ( wro.basis_type, NULL, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1), 1, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1), 2, NVL (wro.REQUIRED_QUANTITY, 1), NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1)))), 5) "Quantity Per FG", -- A basis of 2 indicates the component is issued per lot not per assembly -- For a basis of 2 the component yield factor is ignored ROUND ( SUM ( (DECODE ( wro.basis_type, NULL, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 1, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 2, NVL (wro.REQUIRED_QUANTITY, 1), NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0))))), 5) "Total Req Quantity", SUM (NVL (wro.quantity_issued, 0)) "Quantity Issued", -- Need to include component yield in the ICP calculation ROUND ( SUM ( ( NVL (wro.quantity_issued, 0) - -- A basis of 2 indicates the component is issued per lot not per assembly -- For a basis of 2 the component yield factor is ignored DECODE ( wro.basis_type, NULL, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 1, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 2, NVL (wro.REQUIRED_QUANTITY, 1), NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0))))), 5) "Est. Quantity Left in WIP", ROUND ( SUM ( ( NVL (wro.quantity_issued, 0) - -- A basis of 2 indicates the component is issued per lot not per assembly -- For a basis of 2 the component yield factor is ignored DECODE ( wro.basis_type, NULL, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 1, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 2, NVL (wro.REQUIRED_QUANTITY, 1), NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0))))) * SUM (NVL (cic.ITEM_COST, 0)), 2) "Gross Matl Usage Var.", ROUND ( SUM ( ( NVL (wro.quantity_issued, 0) - -- A basis of 2 indicates the component is issued per lot not per assembly -- For a basis of 2 the component yield factor is ignored DECODE ( wro.basis_type, NULL, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 1, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 2, NVL (wro.REQUIRED_QUANTITY, 1), NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0))))) * NVL ( (SELECT SUM (NVL (cicd.ITEM_COST, 0)) FROM apps.cst_item_cost_details cicd, apps.cst_cost_types cct, apps.bom_resources br WHERE 2=2 and cicd.inventory_item_id = msi2.inventory_item_id AND cicd.organization_id = msi2.organization_id AND br.resource_id = cicd.resource_id AND br.resource_code = 'ICP' AND CCT.COST_TYPE_ID = CICD.COST_TYPE_ID), -- AND cct.cost_type = :P_ICP_COST_TYPE), 0), 2) "ICP in WIP Variances", -- P_COST_TYPE ROUND ( SUM ( ( NVL (wro.quantity_issued, 0) - -- A basis of 2 indicates the component is issued per lot not per assembly -- For a basis of 2 the component yield factor is ignored DECODE ( wro.basis_type, NULL, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 1, NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)), 2, NVL (wro.REQUIRED_QUANTITY, 1), NVL (wro.quantity_per_assembly, 0) * 1 / NVL (wro.component_yield_factor, 1) * ( NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0))))) * ( SUM (NVL (cic.ITEM_COST, 0)) + NVL ( (SELECT SUM (NVL (cicd.ITEM_COST, 0)) FROM apps.cst_item_cost_details cicd, apps.cst_cost_types cct, apps.bom_resources br WHERE 2=2 and cicd.inventory_item_id = msi2.inventory_item_id AND cicd.organization_id = msi2.organization_id AND br.resource_id = cicd.resource_id AND br.resource_code = 'ICP' AND cct.cost_type_id = cicd.cost_type_id), -- AND cct.cost_type = :P_ICP_COST_TYPE), 0)), 2) "Net Matl Usage Var." -- P_COST_TYPE FROM apps.wip_accounting_classes wac, apps.wip_entities we, -- Revision for version 1.7 -- Get the corrected WIP Qty Completed and Qty Scrapped ( SELECT wdjsum.wip_entity_id, wdjsum.organization_id, wdjsum.class_code, wdjsum.date_closed, wdjsum.date_completed, wdjsum.last_update_date, wdjsum.creation_date, wdjsum.primary_item_id, wdjsum.status_type, wdjsum.start_quantity, SUM (wdjsum.quantity_completed) quantity_completed, SUM (wdjsum.quantity_scrapped) quantity_scrapped FROM (SELECT wdj.wip_entity_id, wdj.organization_id, wdj.class_code, wdj.date_closed, wdj.date_completed, wdj.last_update_date, wdj.creation_date, wdj.primary_item_id, wdj.status_type, wdj.start_quantity, wdj.quantity_completed, wdj.quantity_scrapped FROM apps.wip_discrete_jobs wdj, apps.org_acct_periods oap WHERE 1=1 and oap.organization_id = wdj.organization_id --AND oap.period_name = -- NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME -- ================================================= -- Omit jobs created after the period close date. -- ================================================= -- Find jobs that were open during the report period AND ( ( ( wdj.date_closed IS NULL -- the job is OPEN AND TRUNC (wdj.creation_date) <= oap.schedule_close_date -- the job is OPENED before the period close date ... or ... |