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, apps.org_acct_periods oap WHERE 11=11 and mmt.transaction_source_type_id = 5 AND mmt.transaction_source_id = wdj.wip_entity_id AND wro.wip_entity_id = mmt.transaction_source_id AND wro.organization_id = mmt.organization_id AND mmt.transaction_date >= oap.schedule_close_date + 1 AND oap.organization_id = mmt.organization_id AND oap.period_name = 'AUG-08' -- NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME -- Disable an index to increase performance AND oap.acct_period_id + 0 = mmt.acct_period_id) wrosum GROUP BY wrosum.wip_entity_id, wrosum.organization_id, wrosum.inventory_item_id, wrosum.quantity_per_assembly, wrosum.component_yield_factor, wrosum.basis_type) wro, -- End revision for version 1.7 apps.mtl_parameters mp, apps.mtl_system_items_b msi, apps.mtl_system_items_b msi2, -- Fix for version 1.9 apps.cst_item_costs cic, apps.org_acct_periods oap, apps.mfg_lookups ml, apps.mfg_lookups ml2, apps.gl_code_combinations gcc1, -- wip class accounts apps.gl_code_combinations gcc2, -- product group info from COGS account apps.hr_organization_information hoi, apps.hr_all_organization_units haou, apps.hr_all_organization_units haou2, apps.gl_ledgers gl -- =========================================== -- WIP Job Entity and Class joins -- =========================================== WHERE 1=1 and we.wip_entity_id = wdj.wip_entity_id AND wro.wip_entity_id = wdj.wip_entity_id AND wac.class_code = wdj.class_code AND wac.organization_id = we.organization_id AND oap.organization_id = mp.organization_id AND oap.period_name = 'AUG-08' --NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME -- ======================================================================== -- Mtl parameter, item master and WIP assembly and WIP component joins -- ======================================================================== AND mp.organization_id = msi.organization_id AND msi.organization_id = we.organization_id AND msi.inventory_item_id = we.primary_item_id -- FG assembly item AND msi.organization_id = msi2.organization_id AND msi2.inventory_item_id = wro.inventory_item_id -- Component item -- ======================================================================== -- Item Cost joins for the components -- ======================================================================== AND msi2.organization_id = cic.organization_id AND msi2.inventory_item_id = cic.inventory_item_id -- Component item AND cic.cost_type_id = mp.primary_cost_method -- gets the Frozen or Average cost type based on your costing method -- =========================================== -- Lookup Code joins -- =========================================== AND ml.lookup_type = 'WIP_JOB_STATUS' AND ml.lookup_code = wdj.status_type AND ml2.lookup_type = 'WIP_CLASS_TYPE' AND ml2.lookup_code = wac.class_type -- =========================================== -- Accounting code combination joins -- =========================================== AND wac.material_account = gcc1.code_combination_id AND msi.cost_of_sales_account = gcc2.code_combination_id -- =========================================== -- Organization joins to the HR org model -- =========================================== AND hoi.org_information_context = 'Accounting Information' AND hoi.organization_id = mp.organization_id AND hoi.organization_id = haou.organization_id -- this gets the organization name AND haou2.organization_id = TO_NUMBER (hoi.org_information3) -- this gets the operating unit id AND gl.ledger_id = TO_NUMBER (hoi.org_information1) -- get the ledger_id AND gl.name = 'OPM US' -- DECODE (:P_LEDGER, -- 'None', GL.NAME, -- NULL, GL.NAME, -- :P_LEDGER) GROUP BY gl.name, haou2.name, mp.organization_code, oap.period_name, gcc2.segment1, gcc1.segment2, gcc1.segment3, gcc1.segment4, gcc2.segment5, gcc1.segment6, gcc1.segment7, gcc1.segment8, gcc1.segment9, wac.class_code, ml2.meaning, we.wip_entity_name, ml.meaning, TO_CHAR (wdj.date_completed, 'DD-MON-YYYY'), TO_CHAR (wdj.date_closed, 'DD-MON-YYYY'), TO_CHAR (wdj.last_update_date, 'DD-MON-YYYY'), wdj.START_QUANTITY, wdj.quantity_completed, wdj.quantity_scrapped, msi.segment1, msi.description, msi2.segment1, msi2.description, gl.currency_code, msi2.primary_uom_code, MSI2.INVENTORY_ITEM_ID, MSI2.ORGANIZATION_ID, (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) UNION ALL --==================================== --OPM WIP Ingredient Query --==================================== SELECT -- Revision 1.5 from Doug Volz 13-Jan-2015 -- Replace logic, used wrong column to indicate close date -- (case when GBH.ACTUAL_CMPLT_DATE>= OAP.PERIOD_START_DATE -- and TRUNC (GBH.ACTUAL_CMPLT_DATE)< oap.schedule_close_date +1 -- then 'Variance' -- when gbh.ACTUAL_CMPLT_DATE is null -- the job is OPEN -- and TRUNC (gbh.creation_DATE) <= oap.schedule_close_date -- then 'Valuation' -- when GBH.ACTUAL_CMPLT_DATE is not null -- the job is closed and ...the job was closed after the accounting period -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) > OAP.SCHEDULE_CLOSE_DATE (CASE WHEN GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE AND TRUNC (GBH.BATCH_CLOSE_DATE) < oap.schedule_close_date + 1 THEN 'Variance' WHEN gbh.BATCH_CLOSE_DATE IS NULL -- the job is OPEN AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date THEN 'Valuation' WHEN GBH.BATCH_CLOSE_DATE IS NOT NULL -- the job is closed and ...the job was closed after the accounting period AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE THEN 'Valuation' END) "Report Type", -- End of revision 1.5 from Doug Volz 13-Jan-2015 GL.NAME "Ledger", HAOU2.NAME "Operating Unit", MP.ORGANIZATION_CODE "Org Code", OAP.PERIOD_NAME "Period Name", GCC1.SEGMENT1 "Co", GCC2.SEGMENT2 "Cost Ctr", (SELECT MEANING FROM apps.MFG_LOOKUPS WHERE LOOKUP_TYPE = 'CELG_CST_GLINV_RECON_ACCOUNTS' AND description = 'WIP') "Acct", GCC2.SEGMENT4 "Sub-Acct", GCC1.SEGMENT5 "Prod Grp", GCC2.SEGMENT6 "Loc", GCC2.SEGMENT7 "InterCo", GCC2.SEGMENT8 "Future1", GCC2.SEGMENT9 "Future2", NULL "WIP Class", NULL "Class Type", GBH.BATCH_NO "Batch or WIP Job", FLV1.MEANING "Batch or Job Status", TO_CHAR (GBH.ACTUAL_CMPLT_DATE, 'DD-MON-YYYY') "Date Completed", TO_CHAR (GBH.BATCH_CLOSE_DATE, 'DD-MON-YYYY') "Date Closed", TO_CHAR (GBH.LAST_UPDATE_DATE, 'DD-MON-YYYY') "Last Updated", NVL (ITEM.STQTY, 0) "Start Quantity", NVL (ITEM.CQTY, 0) "FG Quantity Completed", NVL (ITEM.SCRAPPED, 0) "FG Quantity Scrapped", NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0) "Total FG Quantity", ITEM.FG_ITEM "FG Item Number", ITEM.FG_DESC "FG Description", ITEM.COMP_ITEM "Component or Ingredient", ITEM.COMP_DESC "Component or Ingredient Desc", GL.CURRENCY_CODE "Curr Code", -- Revision for version 1.2, get OPM costs from transactions /*round((SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE 1=1 AND MSI.ORGANIZATION_ID = CCD.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID),5) "Gross Item Cost",*/ --Commented by Apps on 09Dec14 (CASE WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0 THEN (ROUND ( (SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE 1=1 AND ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID), 5)) ELSE ROUND (GXEH_SUB.ITEM_COST, 5) END) "Gross Item Cost", --Added by Apps on 09Dec14 0 "ICP Item Cost", item.uom "UOM Code", -- Revision for version 1.3 NVL (ITEM.REQTY / ITEM.STQTY, 0) "Quantity Per FG", --Modified by Apps on 22Dec14 ITEM.REQTY "Total Req Quantity", ITEM.QTYISS "Quantity Issued", -- Revision for version 1.4 --NVL (ITEM.REQTY, 0) - NVL (ITEM.QTYISS, 0) "Est. Quantity Left in WIP", --Commented by Apps on 1/7/15 ( NVL (ITEM.QTYISS, 0) - ( (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0)) * NVL (ITEM.REQTY / ITEM.STQTY, 0))) "Est. Quantity Left in WIP", ROUND ( ( NVL (ITEM.QTYISS, 0) - ( (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0)) * NVL (ITEM.REQTY / ITEM.STQTY, 0))) * (CASE WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0 THEN (ROUND ( (SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE 1=1 AND ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID), 5)) ELSE ROUND (GXEH_SUB.ITEM_COST, 5) END), 2) "Gross Matl Usage Var.", --Modified by Apps 1/7/14 0 "ICP in WIP Variances", ROUND ( ( NVL (ITEM.QTYISS, 0) - ( (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0)) * NVL (ITEM.REQTY / ITEM.STQTY, 0))) * (CASE WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0 THEN (ROUND ( (SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE 1=1 AND ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID), 5)) ELSE ROUND (GXEH_SUB.ITEM_COST, 5) END), 2) "Net Matl Usage Var." --Modified by Apps on 1/7/14 FROM APPS.GL_LEDGERS GL, APPS.HR_ORGANIZATION_INFORMATION HOI, APPS.HR_ALL_ORGANIZATION_UNITS HAOU2, APPS.MTL_PARAMETERS MP, APPS.HR_ALL_ORGANIZATION_UNITS HAOU, APPS.MTL_SYSTEM_ITEMS_B MSI, APPS.GL_CODE_COMBINATIONS GCC1, APPS.MTL_SECONDARY_INVENTORIES MSUB, APPS.GL_CODE_COMBINATIONS GCC2, APPS.ORG_ACCT_PERIODS OAP, APPS.GMF_PERIOD_STATUSES GPS, APPS.GMF_FISCAL_POLICIES GFP, APPS.GMF_CALENDAR_ASSIGNMENTS GCA, APPS.GME_BATCH_HEADER GBH, APPS.GME_MATERIAL_DETAILS GMD, APPS.FND_LOOKUP_VALUES FLV1 --, APPS.CM_CMPT_DTL CCD , (SELECT A.FG_ITEM, A.FG_DESC, B.COMP_ITEM, B.COMP_DESC, A.BATCH_ID, A.ORGANIZATION_ID, A.INVENTORY_ITEM_ID, A.PLAN_QTY STQTY, A.ACTUAL_QTY cqty, C.SCRAPPED, b.reqty, B.QTYISS, b.uom, b.inventory_item_id comp_inventory_item_id, --Added by Apps on 09Dec14 b.organization_id comp_organization_id --Added by Apps on 09Dec14 FROM (SELECT msi.segment1 FG_Item, MSI.DESCRIPTION FG_DESC, GMD.BATCH_ID, GMD.ORGANIZATION_ID, GMD.INVENTORY_ITEM_ID, GMD.ACTUAL_QTY, gmd.PLAN_QTY FROM APPS.GME_MATERIAL_DETAILS GMD, apps.MTL_SYSTEM_ITEMS_B MSI WHERE GMD.LINE_TYPE = 1 --product AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND msi.inventory_item_id = gmd.inventory_item_id) A, (SELECT msi.segment1 comp_item, MSI.DESCRIPTION COMP_DESC, GMD.BATCH_ID, GMD.ORGANIZATION_ID, GMD.INVENTORY_ITEM_ID, GMD.PLAN_QTY REQTY, GMD.ACTUAL_QTY QTYISS, msi.primary_uom_code uom FROM APPS.GME_MATERIAL_DETAILS GMD, apps.MTL_SYSTEM_ITEMS_B MSI WHERE gmd.LINE_TYPE = -1 --ingredient AND NVL (GMD.BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14 AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID) B, ( SELECT SUM (ACTUAL_QTY) SCRAPPED, BATCH_ID FROM APPS.GME_MATERIAL_DETAILS WHERE 1=1 AND NVL (BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14 AND LINE_TYPE = 2 GROUP BY BATCH_ID) c WHERE 1=1 AND A.BATCH_ID = B.BATCH_ID AND A.BATCH_ID = C.BATCH_ID(+)) ITEM, -- Revision 1.8: Added below sub query to improve performance ( SELECT GXEH.SOURCE_DOCUMENT_ID, GXEH.INVENTORY_ITEM_ID, GXEH.ORGANIZATION_ID, NVL (SUM (GXEH.TRANSACTION_VALUE / TRANSACTION_QUANTITY), 0) ITEM_COST FROM APPS.GMF_XLA_EXTRACT_HEADERS GXEH WHERE 1=1 AND GXEH.event_class_code = 'BATCH_MATERIAL' GROUP BY GXEH.SOURCE_DOCUMENT_ID, GXEH.INVENTORY_ITEM_ID, GXEH.ORGANIZATION_ID) GXEH_SUB WHERE 1=1 and GL.LEDGER_ID = TO_NUMBER (HOI.ORG_INFORMATION1) -- =========================================== -- Organization joins to the HR org model -- =========================================== AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information' AND GL.NAME = 'OPM US' -- DECODE (:P_LEDGER, 'None', GL.NAME, NULL, GL.NAME, :P_LEDGER) AND HAOU2.ORGANIZATION_ID = TO_NUMBER (HOI.ORG_INFORMATION3) AND HOI.ORGANIZATION_ID = MP.ORGANIZATION_ID AND MP.PROCESS_ENABLED_FLAG = 'Y' AND MP.ORGANIZATION_CODE ='PR1' AND HOI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID -- Comment this out, can be inconsistent. D.Volz 08-Jan-2015 -- AND MSI.PROCESS_COSTING_ENABLED_FLAG = 'Y' -- =========================================== -- Accounting code combination joins -- =========================================== AND MSI.COST_OF_SALES_ACCOUNT = GCC1.CODE_COMBINATION_ID AND MSUB.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSUB.MATERIAL_ACCOUNT = GCC2.CODE_COMBINATION_ID -- =========================================== -- Period name joins -- =========================================== AND OAP.ORGANIZATION_ID = MP.ORGANIZATION_ID AND OAP.PERIOD_NAME = GPS.PERIOD_CODE AND OAP.SCHEDULE_CLOSE_DATE = TRUNC (GPS.END_DATE) AND OAP.PERIOD_START_DATE = GPS.START_DATE AND GPS.LEGAL_ENTITY_ID = TO_NUMBER (HOI.ORG_INFORMATION2) AND GPS.LEGAL_ENTITY_ID = GFP.LEGAL_ENTITY_ID AND GPS.COST_TYPE_ID = GFP.COST_TYPE_ID AND GPS.LEGAL_ENTITY_ID = GCA.LEGAL_ENTITY_ID AND GPS.CALENDAR_CODE = GCA.CALENDAR_CODE AND GPS.COST_TYPE_ID = GCA.COST_TYPE_ID -- =========================================== -- Batch joins -- =========================================== AND GBH.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND GBH.BATCH_ID = GMD.BATCH_ID AND GMD.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND GMD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID -- =========================================== -- lookup type joins -- =========================================== AND FLV1.LOOKUP_TYPE = 'GME_BATCH_STATUS' AND FLV1.LOOKUP_CODE = GBH.BATCH_STATUS AND FLV1.SOURCE_LANG = USERENV ('LANG') AND FLV1.LANGUAGE = USERENV ('LANG') -- =========================================== -- Batch date validation -- =========================================== -- Revision version 1.5 from Doug Volz 13-Jan-2015 -- Incorrect column used -- AND ( ( ( gbh.ACTUAL_CMPLT_DATE IS NULL -- AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date) -- OR ( GBH.ACTUAL_CMPLT_DATE IS NOT NULL -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) > OAP.SCHEDULE_CLOSE_DATE)) -- or (GBH.ACTUAL_CMPLT_DATE >= OAP.PERIOD_START_DATE -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) < OAP.SCHEDULE_CLOSE_DATE + 1)) AND ( ( ( gbh.BATCH_CLOSE_DATE IS NULL AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date) OR ( GBH.BATCH_CLOSE_DATE IS NOT NULL AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE)) OR ( GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE AND TRUNC (GBH.BATCH_CLOSE_DATE) < OAP.SCHEDULE_CLOSE_DATE + 1)) -- End of revision 1.5 from Doug Volz -- =========================================== -- Ingredients joins -- =========================================== AND GMD.BATCH_ID = ITEM.BATCH_ID AND GMD.ORGANIZATION_ID = ITEM.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID AND OAP.PERIOD_NAME = 'AUG-08' -- NVL (:P_PERIOD_NAME, OAP.PERIOD_NAME) AND MSUB.SECONDARY_INVENTORY_NAME = GMD.SUBINVENTORY AND MSUB.ORGANIZATION_ID = GMD.ORGANIZATION_ID -- Revision for version 1.5 by Doug Volz 13-Jan-2015 -- Wrong column used -- GROUP BY (case when GBH.ACTUAL_CMPLT_DATE>= OAP.PERIOD_START_DATE -- and TRUNC (GBH.ACTUAL_CMPLT_DATE)< oap.schedule_close_date +1 -- then 'Variance' -- when gbh.ACTUAL_CMPLT_DATE is null -- the job is OPEN -- and TRUNC (gbh.creation_DATE) <= oap.schedule_close_date -- then 'Valuation' -- when GBH.ACTUAL_CMPLT_DATE is not null -- the job is closed and ...the job was closed after the accounting period -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) > OAP.SCHEDULE_CLOSE_DATE -- then 'Valuation' -- end), -- Revision 1.8: Added below conditions to improve performance AND ITEM.BATCH_ID = GXEH_SUB.SOURCE_DOCUMENT_ID(+) AND ITEM.COMP_INVENTORY_ITEM_ID = GXEH_SUB.INVENTORY_ITEM_ID(+) AND ITEM.COMP_ORGANIZATION_ID = GXEH_SUB.ORGANIZATION_ID(+) GROUP BY (CASE WHEN GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE AND TRUNC (GBH.BATCH_CLOSE_DATE) < oap.schedule_close_date + 1 THEN 'Variance' WHEN gbh.BATCH_CLOSE_DATE IS NULL -- the job is OPEN AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date THEN 'Valuation' WHEN GBH.BATCH_CLOSE_DATE IS NOT NULL -- the job is closed and ...the job was closed after the accounting period AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE THEN 'Valuation' END), -- End revision 1.5 from Doug Volz GL.NAME, haou2.name, mp.organization_code, oap.period_name, GCC1.SEGMENT1, GCC2.SEGMENT2, gcc2.segment4, gcc1.segment5, gcc2.segment6, gcc2.segment7, gcc2.segment8, GCC2.SEGMENT9, GBH.BATCH_NO, FLV1.MEANING, TO_CHAR (GBH.ACTUAL_CMPLT_DATE, 'DD-MON-YYYY'), TO_CHAR (GBH.BATCH_CLOSE_DATE, 'DD-MON-YYYY'), TO_CHAR (GBH.LAST_UPDATE_DATE, 'DD-MON-YYYY'), ITEM.STQTY, ITEM.CQTY, ITEM.SCRAPPED, ITEM.FG_ITEM, ITEM.FG_DESC, ITEM.COMP_ITEM, ITEM.COMP_DESC, GL.CURRENCY_CODE, item.uom, DECODE (ITEM.QTYISS, NVL (ITEM.QTYISS, 0), NVL (ITEM.QTYISS, 0), NVL (ITEM.QTYISS, 0) / NVL (item.STQTY, 0)), ITEM.REQTY, ITEM.QTYISS, NVL (ITEM.REQTY, 0) - NVL (ITEM.QTYISS, 0), MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, GPS.PERIOD_ID, GPS.COST_TYPE_ID, -- Revision for version 1.9, needed for column select statements ITEM.COMP_INVENTORY_ITEM_ID, --Added by Apps on 09Dec14 ITEM.COMP_ORGANIZATION_ID, --Added by Apps on 09Dec14, GBH.BATCH_ID, --Added by Apps on 09Dec14 GXEH_SUB.ITEM_COST ORDER BY 2, 3, 4, 5, 6, 7, 8, 15, 17, 28 |