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 |
