The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE PreInsert_Rollup_errors
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'PreInsert_Rollup_errors';
UPDATE OKS_INT_HEADER_STG_TEMP hst
SET hst.INTERFACE_STATUS = (CASE WHEN EXISTS (SELECT 'X' FROM OKS_INT_ERROR_STG_TEMP
WHERE hst.HEADER_INTERFACE_ROWID = HEADER_INTERFACE_ROWID)
THEN 'E'
ELSE 'S'
END);
END PreInsert_Rollup_errors;
INSERT INTO OKS_IMP_ERRORS
(REQUEST_ID,
PARENT_REQUEST_ID,
INTERFACE_TABLE,
HEADER_INTERFACE_ID,
INTERFACE_ID,
ERROR_MESSAGE)
SELECT OIES.CONCURRENT_REQUEST_ID,
P_parent_request_id,
OIES.INTERFACE_SOURCE_TABLE,
OHI.HEADER_INTERFACE_ID,
OIES.INTERFACE_ID,
OIES.ERROR_MSG
FROM OKS_INT_ERROR_STG_TEMP OIES, OKS_HEADERS_INTERFACE OHI
WHERE OIES.HEADER_INTERFACE_ROWID = OHI.ROWID;
UPDATE OKS_HEADERS_INTERFACE ohi
SET ohi.INTERFACE_STATUS = (CASE WHEN EXISTS (SELECT 'X' FROM OKS_INT_ERROR_STG_TEMP WHERE ohi.ROWID = HEADER_INTERFACE_ROWID)
THEN 'E'
WHEN P_mode = 'I' THEN 'S'
ELSE NULL
END),
ohi.PARENT_REQUEST_ID = P_parent_request_id
WHERE ohi.rowid between P_start_rowid and P_end_rowid
AND ohi.batch_id = P_batch_id
AND (ohi.interface_status IS NULL OR ohi.interface_status = 'R');
INSERT ALL
WHEN (STAT_TYPE = 1) THEN
INTO OKS_IMPORT_STATISTICS
(BATCH_ID,
PARENT_REQUEST_ID,
REQUEST_ID,
STATISTIC_TYPE_ID,
HEADERS_STAT,
LINES_STAT,
COVERED_LEVELS_STAT,
USAGE_COUNTERS_STAT,
SALES_CREDITS_STAT,
NOTES_STAT)
VALUES(
P_batch_id,
P_parent_request_id,
G_WORKER_REQ_ID,
1,
HEADERS_SELECTED,
LINES_SELECTED,
COVERED_LEVELS_SELECTED,
USAGE_COUNTERS_SELECTED,
SALES_CREDITS_SELECTED,
NOTES_SELECTED)
WHEN (STAT_TYPE = 2) THEN
INTO OKS_IMPORT_STATISTICS
(BATCH_ID,
PARENT_REQUEST_ID,
REQUEST_ID,
STATISTIC_TYPE_ID,
HEADERS_STAT,
LINES_STAT,
COVERED_LEVELS_STAT,
USAGE_COUNTERS_STAT,
SALES_CREDITS_STAT,
NOTES_STAT)
VALUES(
P_batch_id,
P_parent_request_id,
G_WORKER_REQ_ID,
decode(P_mode, 'I', 3, 4),
HEADERS_IMPORTED,
LINES_IMPORTED,
COVERED_LEVELS_IMPORTED,
USAGE_COUNTERS_IMPORTED,
SALES_CREDITS_IMPORTED,
NOTES_IMPORTED)
SELECT SUM(COUNT_Q.HEADERS_COUNT) HEADERS_SELECTED,
SUM(COUNT_Q.LINES_COUNT) LINES_SELECTED,
SUM(COUNT_Q.COVERED_LEVELS_COUNT) COVERED_LEVELS_SELECTED,
SUM(COUNT_Q.USAGE_COUNTERS_COUNT) USAGE_COUNTERS_SELECTED,
SUM(COUNT_Q.SALES_CREDITS_COUNT) SALES_CREDITS_SELECTED,
SUM(COUNT_Q.NOTES1_COUNT) + SUM(COUNT_Q.NOTES2_COUNT) NOTES_SELECTED,
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.HEADERS_COUNT,0)) HEADERS_IMPORTED,
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.LINES_COUNT,0)) LINES_IMPORTED,
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.COVERED_LEVELS_COUNT,0)) COVERED_LEVELS_IMPORTED,
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.USAGE_COUNTERS_COUNT,0)) USAGE_COUNTERS_IMPORTED,
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.SALES_CREDITS_COUNT,0)) SALES_CREDITS_IMPORTED,
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.NOTES1_COUNT,0)) +
SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.NOTES2_COUNT,0)) NOTES_IMPORTED,
FL.STAT_TYPE STAT_TYPE
FROM (SELECT rownum STAT_TYPE from dual connect by level <= 2) FL,
(SELECT distinct OHST.INTERFACE_STATUS,
count(distinct OHST.rowid) over (partition by OHST.INTERFACE_STATUS) HEADERS_COUNT,
count(distinct OLST.rowid) over (partition by OHST.INTERFACE_STATUS) LINES_COUNT,
count(distinct OCLST.rowid) over (partition by OHST.INTERFACE_STATUS) COVERED_LEVELS_COUNT,
count(distinct OUCST.rowid) over (partition by OHST.INTERFACE_STATUS) USAGE_COUNTERS_COUNT,
count(distinct OSCST.rowid) over (partition by OHST.INTERFACE_STATUS) SALES_CREDITS_COUNT,
count(distinct ONI1.rowid) over (partition by OHST.INTERFACE_STATUS) NOTES1_COUNT,
count(distinct ONI2.rowid) over (partition by OHST.INTERFACE_STATUS) NOTES2_COUNT
FROM OKS_INT_HEADER_STG_TEMP OHST,
OKS_INT_LINE_STG_TEMP OLST,
OKS_INT_SALES_CREDIT_STG_TEMP OSCST,
OKS_INT_COVERED_LEVEL_STG_TEMP OCLST,
OKS_INT_USAGE_COUNTER_STG_TEMP OUCST,
OKS_NOTES_INTERFACE ONI1,
OKS_NOTES_INTERFACE ONI2
WHERE OHST.HEADER_INTERFACE_ID = OLST.HEADER_INTERFACE_ID (+)
AND OHST.HEADER_INTERFACE_ID = OSCST.HEADER_INTERFACE_ID (+)
AND OLST.LINE_INTERFACE_ID = OCLST.LINE_INTERFACE_ID (+)
AND OLST.LINE_INTERFACE_ID = OUCST.LINE_INTERFACE_ID (+)
AND OHST.HEADER_INTERFACE_ID = ONI1.HEADER_INTERFACE_ID (+)
AND ONI1.LINE_INTERFACE_ID (+) IS NULL
AND OLST.LINE_INTERFACE_ID = ONI2.LINE_INTERFACE_ID (+)) COUNT_Q
GROUP BY FL.STAT_TYPE;
INSERT INTO OKS_IMPORT_STATISTICS
(BATCH_ID,
PARENT_REQUEST_ID,
REQUEST_ID,
STATISTIC_TYPE_ID,
HEADERS_STAT,
LINES_STAT,
COVERED_LEVELS_STAT,
USAGE_COUNTERS_STAT,
SALES_CREDITS_STAT,
NOTES_STAT)
SELECT P_batch_id BATCH_ID,
P_parent_request_id PARENT_REQUEST_ID,
G_WORKER_REQ_ID REQUEST_ID,
2 STATISTIC_TYPE_ID,
nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_HEADERS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) HEADERS_INVALID,
nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_LINES_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) LINES_INVALID,
nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_COVERED_LEVELS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) COVERED_LEVELS_INVALID,
nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_USAGE_COUNTERS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) USAGE_COUNTERS_INVALID,
nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_SALES_CREDITS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) SALES_CREDITS_INVALID,
nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_NOTES_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) NOTES_INVALID
FROM (SELECT distinct INTERFACE_SOURCE_TABLE,
count(distinct INTERFACE_ID) over (partition by INTERFACE_SOURCE_TABLE) INVALID_COUNT
FROM OKS_INT_ERROR_STG_TEMP) INVALID_Q;
DELETE FROM OKS_INT_HEADER_STG_TEMP;
DELETE FROM OKS_INT_LINE_STG_TEMP;
DELETE FROM OKS_COVERED_INSTANCE_STG_TEMP;
DELETE FROM OKS_COVERED_ITEM_STG_TEMP;
DELETE FROM OKS_COVERED_PARTY_STG_TEMP;
DELETE FROM OKS_COVERED_ACCOUNT_STG_TEMP;
DELETE FROM OKS_COVERED_SITE_STG_TEMP;
DELETE FROM OKS_COVERED_SYSTEM_STG_TEMP;
DELETE FROM OKS_INT_COVERED_LEVEL_STG_TEMP;
DELETE FROM OKS_INT_USAGE_COUNTER_STG_TEMP;
DELETE FROM OKS_INT_ERROR_STG_TEMP;
DELETE FROM OKS_INT_SALES_CREDIT_STG_TEMP;
l_update_name varchar2(30);
SELECT OHI.header_interface_id ,OHI.category,OLI.line_interface_id,
NVL(osci.header_interface_id,-1) Sales_Credits,
DECODE(UPPER(ohi.category),'WARRANTY',NVL(OCLI.line_interface_id,NVL2(OLI.line_interface_id,-2,-1))
,'SERVICE',DECODE(OLI.line_interface_id, NULL,-1
,DECODE (UPPER(OLI.LINE_TYPE),'USAGE',NVL(OUCI.LINE_INTERFACE_ID,-3)
,NVL(OCLI.LINE_INTERFACE_ID,-2)))
,'SUBSCRIPTION',NVL(OLI.line_interface_id,-1) ) line_flow
FROM OKS_HEADERS_INTERFACE OHI
,OKS_LINES_INTERFACE OLI
,OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_USAGE_COUNTERS_INTERFACE OUCI
,OKS_SALES_CREDITS_INTERFACE OSCI
WHERE OHI.batch_id = q_batch_id
AND OLI.HEADER_INTERFACE_ID(+) = OHI.HEADER_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID(+) = OLI.line_interface_id
AND OUCI.LINE_INTERFACE_ID(+) = OLI.line_interface_id
AND OHI.HEADER_INTERFACE_ID = OSCI.HEADER_INTERFACE_ID(+);
l_update_name := 'Import'||P_batch_id;
ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
P_worker_id,
P_num_workers,
P_commit_size,
0);
ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_commit_size,
TRUE);
SELECT count(1) INTO l_row_count FROM OKS_HEADERS_INTERFACE
WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
AND BATCH_ID = P_batch_id
AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R')
AND rownum = 1;
UPDATE oks_headers_interface
SET interface_status ='E'
WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
INSERT INTO OKS_IMP_ERRORS
(REQUEST_ID,
PARENT_REQUEST_ID,
INTERFACE_TABLE,
HEADER_INTERFACE_ID,
INTERFACE_ID,
ERROR_MESSAGE)
VALUES
(G_WORKER_REQ_ID,
P_parent_request_id,
'OKS_HEADERS_INTERFACE',
process_rec.HEADER_INTERFACE_ID,
process_rec.HEADER_INTERFACE_ID,
'OKS_IMP_HDR_SAL_CREDIT' );
UPDATE oks_headers_interface
SET interface_status ='E'
WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
INSERT INTO OKS_IMP_ERRORS
(REQUEST_ID,
PARENT_REQUEST_ID,
INTERFACE_TABLE,
HEADER_INTERFACE_ID,
INTERFACE_ID,
ERROR_MESSAGE)
VALUES
(G_WORKER_REQ_ID,
P_parent_request_id,
'OKS_HEADERS_INTERFACE',
process_rec.HEADER_INTERFACE_ID,
process_rec.HEADER_INTERFACE_ID,
'OKS_IMP_HDR_INVALID_LINE' );
UPDATE oks_headers_interface
SET interface_status ='E'
WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
INSERT INTO OKS_IMP_ERRORS
(REQUEST_ID,
PARENT_REQUEST_ID,
INTERFACE_TABLE,
HEADER_INTERFACE_ID,
INTERFACE_ID,
ERROR_MESSAGE)
VALUES
(G_WORKER_REQ_ID,
P_parent_request_id,
'OKS_LINES_INTERFACE',
process_rec.HEADER_INTERFACE_ID,
process_rec.LINE_INTERFACE_ID,
'OKS_IMP_LINE_INVALID_COVL' );
UPDATE oks_headers_interface
SET interface_status ='E'
WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
INSERT INTO OKS_IMP_ERRORS
(REQUEST_ID,
PARENT_REQUEST_ID,
INTERFACE_TABLE,
HEADER_INTERFACE_ID,
INTERFACE_ID,
ERROR_MESSAGE)
VALUES
(G_WORKER_REQ_ID,
P_parent_request_id,
'OKS_LINES_INTERFACE',
process_rec.HEADER_INTERFACE_ID,
process_rec.LINE_INTERFACE_ID,
'OKS_IMP_LINE_INVALID_USAGE' );
PreInsert_Rollup_errors;
OKS_IMPORT_INSERT.Insert_Contracts;
-- Invoking Post Insert Routines
l_stmt_num := 85;
OKS_IMPORT_POST_INSERT.Import_Post_Insert;
ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_commit_size,
FALSE);