OPM2
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 3=3 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 = 'Frozen'), 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 4=4 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 = 'Frozen'), 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 5=5 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 = 'Frozen'), 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 9=9 and oap.organization_id = wdj.organization_id AND oap.period_name = 'AUG-08' -- 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 ... ) OR ( 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)) OR -- Find jobs that were closed during the report period ( wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE AND TRUNC (wdj.date_closed) < oap.schedule_close_date + 1)) UNION ALL SELECT mmt.transaction_source_id, -- wip_entity_id mmt.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, DECODE (mmt.transaction_type_id, 90, 0, -- Scrap assemblies from WIP 91, 0, -- Return assemblies scrapped from WIP 44, -1 * mmt.primary_quantity, -- WIP Completion 17, mmt.primary_quantity -- WIP Completion Return ) quantity_completed, DECODE (mmt.transaction_type_id, 90, mmt.primary_quantity, -- Scrap assemblies from WIP 91, -1 * mmt.primary_quantity, -- Return assemblies scrapped from WIP 44, 0, -- WIP Completion 17, 0 -- WIP Completion Return ) quantity_scrapped FROM apps.mtl_material_transactions mmt, apps.wip_discrete_jobs wdj, apps.org_acct_periods oap WHERE 5=5 and mmt.transaction_source_type_id = 5 AND mmt.transaction_source_id = wdj.wip_entity_id AND mmt.transaction_date >= oap.schedule_close_date + 1 AND oap.period_name = 'AUG-08' -- NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME AND oap.organization_id = mmt.organization_id -- Disable an index to increase performance AND oap.acct_period_id + 0 = mmt.acct_period_id) wdjsum GROUP BY 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) wdj, -- Revision for version 1.7 -- Get the corrected WIP component issue quantities ( SELECT wrosum.wip_entity_id, wrosum.organization_id, wrosum.inventory_item_id, wrosum.quantity_per_assembly, SUM (wrosum.REQUIRED_QUANTITY) REQUIRED_QUANTITY, -- Revision 1.8 Added SUM wrosum.component_yield_factor, SUM (wrosum.quantity_issued) quantity_issued, wrosum.basis_type basis_type FROM (SELECT wro.wip_entity_id, wro.organization_id, wro.inventory_item_id, wro.quantity_per_assembly, wro.REQUIRED_QUANTITY, wro.component_yield_factor, wro.quantity_issued, wro.basis_type FROM apps.wip_requirement_operations wro, apps.wip_discrete_jobs wdj, apps.org_acct_periods oap WHERE 7=7 and wdj.wip_entity_id = wro.wip_entity_id AND wdj.organization_id = wro.organization_id AND wdj.organization_id = oap.organization_id AND oap.period_name = 'AUG-08' -- 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 ... ) OR ( 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)) OR -- Find jobs that were closed during the report period ( wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE AND TRUNC (wdj.date_closed) < oap.schedule_close_date + 1)) UNION ALL SELECT mmt.transaction_source_id, wro.organization_id, mmt.inventory_item_id, wro.quantity_per_assembly, wro.REQUIRED_QUANTITY, wro.component_yield_factor, DECODE (mmt.transaction_type_id, 35, mmt.primary_quantity, -- WIP Component Issue 43, -1 * mmt.primary_quantity -- WIP Component Return ) quantity_issued, wro.basis_type FROM apps.mtl_material_transactions mmt, apps.wip_discrete_jobs wdj, apps.wip_requirement_operations wro, |