OPM WIP Value - V2
Description
SELECT (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 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 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 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 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 NVL (BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14 AND LINE_TYPE = 2 GROUP BY BATCH_ID) c WHERE 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 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' AND HAOU2.ORGANIZATION_ID = TO_NUMBER (HOI.ORG_INFORMATION3) AND HOI.ORGANIZATION_ID = MP.ORGANIZATION_ID AND MP.PROCESS_ENABLED_FLAG = 'Y' AND SUBSTR (MP.ORGANIZATION_CODE, 1, 1) <> '9' 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 = 'JUN-08' AND MSUB.SECONDARY_INVENTORY_NAME = GMD.SUBINVENTORY AND MSUB.ORGANIZATION_ID = GMD.ORGANIZATION_ID 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 |