VIEW: APPS.OPI_EDW_JOB_DETAIL_V

Source

View Text - Preformatted

SELECT inv.ALL_PK_KEY ALL_INV_ID, inv.PCMP_OPM_COMPANY_PK_KEY PCMP_ID, inv.PORG_OPM_ORGANIZATION_PK_KEY PORG_ID, inv.OU_OPERATING_UNIT_PK_KEY OPERATING_UNIT_ID, inv.PLNT_PLANT_PK_KEY PLANT_ID, time.ALL_ALL_PK_KEY ALL_TIME_ID, time.cyr_cal_year_pk_key CAL_YR_ID, time.cqtr_cal_qtr_pk_key CAL_QTR_ID, time.cper_cal_period_pk_key CAL_PERIOD_ID, pl.ALL_ALL_PK_KEY ALL_PRDL_ID, pl.PRDL_PRDL_PK_KEY PRDL_ID, it.ALL_ALL_PK_KEY ALL_ITEMS_ID, it.CO16_CATEGORY_PK_KEY ITEM_CAT_ID, it.IREV_ITEM_REVISION_PK_KEY ITEM_ID, it.ITEM_ITEM_NUMBER_PK_KEY ITEM_NUMBER_ID, it.IORG_ITEM_ORG_PK_KEY ITEM_ORG_ID, uom.UOM_EDW_UOM_PK_KEY UOM_ID, jd.ACT_MTL_INP_VAL_G ACT_MTL_INP_VAL_G, jd.PLN_MTL_INP_VAL_G PLN_MTL_INP_VAL_G, jd.ACT_OUT_VAL_G ACT_OUT_VAL_G, jd.PLN_OUT_VAL_G PLN_OUT_VAL_G, jd.ACT_SCR_VAL_G ACT_SCR_VAL_G, jd.PLN_SCR_VAL_G PLN_SCR_VAL_G, jd.ACT_OUT_QTY ACT_OUT_QTY, jd.PLN_OUT_QTY PLN_OUT_QTY, jd.ACT_JOB_TIME ACT_JOB_TIME, jd.STD_QTY STD_QTY, jd.STND_HRS_EARNED STND_HRS_EARNED, jd.STD_TIME STD_TIME, case when ACT_CMPL_DATE > PLN_CMPL_DATE THEN 1 ELSE 0 END LATE_JOB, 1 JOB from OPI_EDW_JOB_DETAIL_F jd, EDW_MTL_INVENTORY_LOC_M inv, EDW_TIME_M time, EDW_OPI_PRDL_M pl, edw_items_m it, EDW_MTL_UOM_M uom WHERE jd.LOCATOR_FK_KEY = inv.INVL_LOCATOR_PK_KEY AND jd.TRX_DATE_FK_KEY = time.CDAY_CAL_DAY_PK_KEY AND jd.PRD_LINE_FK_KEY = pl.PRDL_PRDL_PK_KEY AND jd.ITEM_FK_KEY = it.IREV_ITEM_REVISION_PK_KEY and jd.BASE_UOM_FK_KEY = uom.UOM_EDW_UOM_PK_KEY and jd.JOB_STATUS IN ('Closed Batch','Certified','Complete','Closed')

View Text - HTML Formatted

SELECT INV.ALL_PK_KEY ALL_INV_ID
, INV.PCMP_OPM_COMPANY_PK_KEY PCMP_ID
, INV.PORG_OPM_ORGANIZATION_PK_KEY PORG_ID
, INV.OU_OPERATING_UNIT_PK_KEY OPERATING_UNIT_ID
, INV.PLNT_PLANT_PK_KEY PLANT_ID
, TIME.ALL_ALL_PK_KEY ALL_TIME_ID
, TIME.CYR_CAL_YEAR_PK_KEY CAL_YR_ID
, TIME.CQTR_CAL_QTR_PK_KEY CAL_QTR_ID
, TIME.CPER_CAL_PERIOD_PK_KEY CAL_PERIOD_ID
, PL.ALL_ALL_PK_KEY ALL_PRDL_ID
, PL.PRDL_PRDL_PK_KEY PRDL_ID
, IT.ALL_ALL_PK_KEY ALL_ITEMS_ID
, IT.CO16_CATEGORY_PK_KEY ITEM_CAT_ID
, IT.IREV_ITEM_REVISION_PK_KEY ITEM_ID
, IT.ITEM_ITEM_NUMBER_PK_KEY ITEM_NUMBER_ID
, IT.IORG_ITEM_ORG_PK_KEY ITEM_ORG_ID
, UOM.UOM_EDW_UOM_PK_KEY UOM_ID
, JD.ACT_MTL_INP_VAL_G ACT_MTL_INP_VAL_G
, JD.PLN_MTL_INP_VAL_G PLN_MTL_INP_VAL_G
, JD.ACT_OUT_VAL_G ACT_OUT_VAL_G
, JD.PLN_OUT_VAL_G PLN_OUT_VAL_G
, JD.ACT_SCR_VAL_G ACT_SCR_VAL_G
, JD.PLN_SCR_VAL_G PLN_SCR_VAL_G
, JD.ACT_OUT_QTY ACT_OUT_QTY
, JD.PLN_OUT_QTY PLN_OUT_QTY
, JD.ACT_JOB_TIME ACT_JOB_TIME
, JD.STD_QTY STD_QTY
, JD.STND_HRS_EARNED STND_HRS_EARNED
, JD.STD_TIME STD_TIME
, CASE WHEN ACT_CMPL_DATE > PLN_CMPL_DATE THEN 1 ELSE 0 END LATE_JOB
, 1 JOB
FROM OPI_EDW_JOB_DETAIL_F JD
, EDW_MTL_INVENTORY_LOC_M INV
, EDW_TIME_M TIME
, EDW_OPI_PRDL_M PL
, EDW_ITEMS_M IT
, EDW_MTL_UOM_M UOM
WHERE JD.LOCATOR_FK_KEY = INV.INVL_LOCATOR_PK_KEY
AND JD.TRX_DATE_FK_KEY = TIME.CDAY_CAL_DAY_PK_KEY
AND JD.PRD_LINE_FK_KEY = PL.PRDL_PRDL_PK_KEY
AND JD.ITEM_FK_KEY = IT.IREV_ITEM_REVISION_PK_KEY
AND JD.BASE_UOM_FK_KEY = UOM.UOM_EDW_UOM_PK_KEY
AND JD.JOB_STATUS IN ('CLOSED BATCH'
, 'CERTIFIED'
, 'COMPLETE'
, 'CLOSED')