OPM2
Description
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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) |
