[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT OPI_EDW_JOB_DETAIL_INC_S.NEXTVAL INTO l_seq_id FROM dual;
INSERT
INTO OPI_EDW_OPI_JOB_DETAIL_INC(primary_key1, primary_key2, primary_key3, seq_id)
SELECT
primary_key1,
primary_key2,
primary_key3,
l_seq_id
FROM
(
SELECT
JOBS.WIP_ENTITY_ID primary_key1,
JOBS.REPETITIVE_SCHEDULE_ID primary_key2,
JOBS.JOB_ID primary_key3
FROM
(
SELECT
EN.WIP_ENTITY_ID WIP_ENTITY_ID,
TO_NUMBER(NULL) REPETITIVE_SCHEDULE_ID,
EN.WIP_ENTITY_ID || '-' JOB_ID,
MAX(GREATEST(EN.LAST_UPDATE_DATE, DI.LAST_UPDATE_DATE, WPB.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
FROM WIP_ENTITIES EN , WIP_DISCRETE_JOBS DI, WIP_PERIOD_BALANCES WPB
WHERE
DI.STATUS_TYPE IN (4,5,7,12) AND
DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
DI.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
GROUP BY
EN.WIP_ENTITY_ID
UNION
SELECT
EN.WIP_ENTITY_ID WIP_ENTITY_ID,
RE.REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID,
EN.WIP_ENTITY_ID || '-' || RE.REPETITIVE_SCHEDULE_ID JOB_ID,
MAX(GREATEST(EN.LAST_UPDATE_DATE, RE.LAST_UPDATE_DATE, WPB.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
FROM
WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE, WIP_PERIOD_BALANCES WPB
WHERE
RE.STATUS_TYPE IN (4,5,7,12) AND
RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
RE.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID AND RE.REPETITIVE_SCHEDULE_ID = WPB.REPETITIVE_SCHEDULE_ID
GROUP BY
EN.WIP_ENTITY_ID, RE.REPETITIVE_SCHEDULE_ID
UNION
SELECT
EN.WIP_ENTITY_ID WIP_ENTITY_ID,
TO_NUMBER(NULL) REPETITIVE_SCHEDULE_ID,
EN.WIP_ENTITY_ID || '-' JOB_ID,
MAX(GREATEST(EN.LAST_UPDATE_DATE, FL.LAST_UPDATE_DATE, WPB.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
FROM
WIP_ENTITIES EN , WIP_FLOW_SCHEDULES FL, WIP_PERIOD_BALANCES WPB
WHERE
FL.STATUS = 2 AND
FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID AND
FL.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
GROUP BY
EN.WIP_ENTITY_ID
) JOBS,
(
SELECT /*+ parallel(mmt) */
MMTMMTA.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,
MMTMMTA.REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID,
MMTMMTA.JOB_ID,
MAX(GREATEST(MMTMMTA.LAST_UPDATE_DATE, WRO.LAST_UPDATE_DATE)) LAST_UPDATE_DATE
FROM
(select MMT.TRANSACTION_SOURCE_ID,
MMTA.REPETITIVE_SCHEDULE_ID ,
MMT.TRANSACTION_SOURCE_ID ||'-'|| NVL(MMTA.REPETITIVE_SCHEDULE_ID,'') JOB_ID,
GREATEST(NVL(MMT.LAST_UPDATE_DATE, TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
NVL(MMTA.LAST_UPDATE_DATE, TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
NVL(WSV.LAST_UPDATE_DATE,TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss'))) LAST_UPDATE_DATE
from MTL_MATERIAL_TRANSACTIONS MMT, MTL_MATERIAL_TXN_ALLOCATIONS MMTA,
WIP_SCRAP_VALUES WSV,
MTL_PARAMETERS MP
where
(MMT.TRANSACTION_ACTION_ID IN (1, 27, 33, 34, 31, 32, 30)) AND
MMT.TRANSACTION_SOURCE_TYPE_ID = 5 AND
MMT.TRANSACTION_ID = MMTA.TRANSACTION_ID (+) AND
MMT.TRANSACTION_ID = WSV.TRANSACTION_ID (+) AND
MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID AND
MP.PROCESS_ENABLED_FLAG > 'Y'
) MMTMMTA,
(select WRO.WIP_ENTITY_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.LAST_UPDATE_DATE,
WRO.WIP_ENTITY_ID ||'-'|| NVL(WRO.REPETITIVE_SCHEDULE_ID,'') JOB_ID
from WIP_REQUIREMENT_OPERATIONS WRO) WRO
WHERE
MMTMMTA.JOB_ID = WRO.JOB_ID (+)
GROUP BY
MMTMMTA.TRANSACTION_SOURCE_ID, MMTMMTA.REPETITIVE_SCHEDULE_ID, MMTMMTA.JOB_ID
) JOBITEMTOTAL_MAT_BPR_SCRAP
WHERE
JOBS.JOB_ID = JOBITEMTOTAL_MAT_BPR_SCRAP.JOB_ID (+) AND
GREATEST(NVL(JOBITEMTOTAL_MAT_BPR_SCRAP.LAST_UPDATE_DATE,TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
NVL(JOBS.LAST_UPDATE_DATE,TO_DATE('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN g_push_from_date and g_push_to_date
UNION
SELECT
primary_key1,
primary_key2,
primary_key3
FROM
OPI_EDW_OPI_JOB_DETAIL_INC
);
DELETE FROM OPI_EDW_OPI_JOB_DETAIL_INC WHERE seq_id IS NULL;
/* in the Select statement */
INSERT INTO OPI_EDW_JOB_DETAIL_FSTG
(
JOB_DETAIL_PK,
LOCATOR_FK,
ITEM_FK,
PRD_LINE_FK,
TRX_DATE_FK,
SOB_CURRENCY_FK,
BASE_UOM_FK,
INSTANCE_FK,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
ACT_BPR_VAL_B,
ACT_BPR_VAL_G,
ACT_CMPL_DATE,
ACT_CNCL_DATE,
ACT_INP_VAL_B,
ACT_INP_VAL_G,
ACT_JOB_TIME,
ACT_MTL_INP_VAL_B,
ACT_MTL_INP_VAL_G,
ACT_OUT_QTY,
ACT_OUT_VAL_B,
ACT_OUT_VAL_G,
ACT_SCR_VAL_B,
ACT_SCR_VAL_G,
ACT_STRT_DATE,
CREATION_DATE,
FST_PASS_YLD,
JOB_NO,
JOB_STATUS,
LAST_UPDATE_DATE,
MFG_MODE,
MOVE_TIME,
NO_ADJ,
NO_TIME_RESH,
PLN_BPR_VAL_B,
PLN_BPR_VAL_G,
PLN_CMPL_DATE,
PLN_INP_VAL_B,
PLN_INP_VAL_G,
PLN_JOB_TIME,
PLN_MTL_INP_VAL_B,
PLN_MTL_INP_VAL_G,
PLN_OUT_QTY,
PLN_OUT_VAL_B,
PLN_OUT_VAL_G,
PLN_SCR_VAL_B,
PLN_SCR_VAL_G,
PLN_STRT_DATE,
QC_FAIL_QTY,
QC_TEST,
QUEUE_TIME,
RESH_REASON_CODE,
RES_LOOKUP_FK,
REWORK_QTY,
RUN_TIME,
SETUP_TIME,
SMPL_CNT,
STD_QTY,
STD_TIME,
STD_VAL_B,
STD_VAL_G,
STS_LOOKUP_FK,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT /*+ ALL_ROWS */
JOB_DETAIL_PK,
NVL(LOCATOR_FK,'NA_EDW'),
NVL(ITEM_FK,'NA_EDW'),
NVL(PRD_LINE_FK,'NA_EDW'),
NVL(TRX_DATE_FK,'NA_EDW'),
NVL(SOB_CURRENCY_FK,'NA_EDW'),
NVL(BASE_UOM_FK,'NA_EDW'),
NVL(INSTANCE_FK,'NA_EDW'),
NVL(USER_FK1,'NA_EDW'),
NVL(USER_FK2,'NA_EDW'),
NVL(USER_FK3,'NA_EDW'),
NVL(USER_FK4,'NA_EDW'),
NVL(USER_FK5,'NA_EDW'),
ACT_BPR_VAL_B,
ACT_BPR_VAL_B * CONVERSION_RATE,
ACT_CMPL_DATE,
ACT_CNCL_DATE,
ACT_INP_VAL_B,
ACT_INP_VAL_B * CONVERSION_RATE,
ACT_JOB_TIME,
ACT_MTL_INP_VAL_B,
ACT_MTL_INP_VAL_B * CONVERSION_RATE,
ACT_OUT_QTY,
ACT_OUT_VAL_B,
ACT_OUT_VAL_B * CONVERSION_RATE,
ACT_SCR_VAL_B,
ACT_SCR_VAL_B * CONVERSION_RATE,
ACT_STRT_DATE,
CREATION_DATE,
TO_NUMBER(NULL),
JOB_NO,
JOB_STATUS,
LAST_UPDATE_DATE,
MFG_MODE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
PLN_BPR_VAL_B,
PLN_BPR_VAL_B * CONVERSION_RATE,
PLN_CMPL_DATE,
PLN_INP_VAL_B,
PLN_INP_VAL_B * CONVERSION_RATE,
PLN_JOB_TIME,
PLN_MTL_INP_VAL_B,
PLN_MTL_INP_VAL_B * CONVERSION_RATE,
PLN_OUT_QTY,
PLN_OUT_VAL_B,
PLN_OUT_VAL_B * CONVERSION_RATE,
PLN_SCR_VAL_B,
PLN_SCR_VAL_B * CONVERSION_RATE,
PLN_STRT_DATE,
TO_NUMBER(NULL),
NULL,
TO_NUMBER(NULL),
NULL,
'NA_EDW',
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
STANDARD_QTY,
STD_TIME,
STD_VAL_B,
STD_VAL_B * CONVERSION_RATE,
'NA_EDW',
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
DECODE( CONVERSION_RATE, -1, 'RATE NOT AVAILABLE', DECODE( CONVERSION_RATE, -2, 'INVALID CURRENCY', 'LOCAL READY') )
FROM OPI_EDW_OPI_JOB_DETAIL_FCV
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
l_rows_inserted Number:=0;
SELECT DISTINCT
SOB_CURRENCY_FK FROM_CURRENCY,
NVL(SUBSTR(ACT_CMPL_DATE,1,10),CREATION_DATE) C_DATE,
COLLECTION_STATUS
FROM
OPI_EDW_JOB_DETAIL_FSTG
WHERE
SUBSTRB(JOB_DETAIL_PK,LENGTH(JOB_DETAIL_PK)-2,3) = 'OPI'
AND COLLECTION_STATUS IN ('RATE NOT AVAILABLE', 'INVALID CURRENCY')
ORDER BY FROM_CURRENCY, C_DATE;
l_rows_deleted Number:=0;
edw_log.put_line('Inserting into local staging table for view type 1');
edw_log.put_line('Inserted ' || Nvl(l_row_count_view1,0) ||
' rows into local staging table for view type 1');
edw_log.put_line('For all view types, inserted ' || Nvl(g_row_count,0)
|| ' rows into local staging table.');
INSERT
INTO OPI_EDW_OPI_JOB_DETAIL_INC(primary_key1, primary_key2, primary_key3)
SELECT distinct
SUBSTRB(JOB_DETAIL_PK,INSTR(JOB_DETAIL_PK,'-',1,1)+1,INSTR(JOB_DETAIL_PK,'-',1,2)-INSTR(JOB_DETAIL_PK,'-',1,1)-1) primary_key1,
SUBSTRB(JOB_DETAIL_PK,INSTR(JOB_DETAIL_PK,'-',1,2)+1,INSTR(JOB_DETAIL_PK,'-',1,3)-INSTR(JOB_DETAIL_PK,'-',1,2)-1) primary_key2,
SUBSTRB(JOB_DETAIL_PK,INSTR(JOB_DETAIL_PK,'-',1,1)+1,INSTR(JOB_DETAIL_PK,'-',1,3)-INSTR(JOB_DETAIL_PK,'-',1,1)-1) primary_key3
FROM
OPI_EDW_JOB_DETAIL_FSTG
WHERE
SUBSTRB(JOB_DETAIL_PK,LENGTH(JOB_DETAIL_PK)-2,3) = 'OPI'
AND COLLECTION_STATUS IN ('RATE NOT AVAILABLE', 'INVALID CURRENCY');
DELETE FROM OPI_EDW_JOB_DETAIL_FSTG
WHERE SUBSTRB(JOB_DETAIL_PK,LENGTH(JOB_DETAIL_PK)-2,3) = 'OPI'
AND COLLECTION_STATUS IN ('RATE NOT AVAILABLE', 'INVALID CURRENCY');
l_rows_deleted:= sql%rowcount;
edw_log.put_line('Deleted '||nvl(l_rows_deleted,0)||
' missing rate/invalid currency rows from local staging table');
edw_log.put_line('There are ' || to_char(Nvl(g_row_count,0) - nvl(l_rows_deleted,0))
|| ' remaining rows in local staging table.');
edw_log.put_line('Inserted '|| to_char(nvl(g_row_count,0) - nvl(l_rows_deleted,0))||
' rows into the staging table');
rollback; -- Rollback insert into local staging
edw_log.put_line('Inserting into local staging have failed');