eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKS_IMPORT_CONTRACTS_WORKER SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 47


PROCEDURE PreInsert_Rollup_errors
IS
  l_stmt_num  NUMBER := 0;

Line: 50


  l_routine   CONSTANT VARCHAR2(30) := 'PreInsert_Rollup_errors';

Line: 61


 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);

Line: 82


 END PreInsert_Rollup_errors;

Line: 118


 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;

Line: 136


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');

Line: 194


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;

Line: 282


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;

Line: 344


DELETE FROM OKS_INT_HEADER_STG_TEMP;

Line: 345


DELETE FROM OKS_INT_LINE_STG_TEMP;

Line: 346


DELETE FROM OKS_COVERED_INSTANCE_STG_TEMP;

Line: 347


DELETE FROM OKS_COVERED_ITEM_STG_TEMP;

Line: 348


DELETE FROM OKS_COVERED_PARTY_STG_TEMP;

Line: 349


DELETE FROM OKS_COVERED_ACCOUNT_STG_TEMP;

Line: 350


DELETE FROM OKS_COVERED_SITE_STG_TEMP;

Line: 351


DELETE FROM OKS_COVERED_SYSTEM_STG_TEMP;

Line: 352


DELETE FROM OKS_INT_COVERED_LEVEL_STG_TEMP;

Line: 353


DELETE FROM OKS_INT_USAGE_COUNTER_STG_TEMP;

Line: 354


DELETE FROM OKS_INT_ERROR_STG_TEMP;

Line: 355


DELETE FROM OKS_INT_SALES_CREDIT_STG_TEMP;

Line: 399


  l_update_name           varchar2(30);

Line: 427


 l_update_name := 'Import'||P_batch_id;

Line: 456


    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);

Line: 465


    ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
                                             l_end_rowid,
                                             l_any_rows_to_process,
                                             P_commit_size,
                                             TRUE);

Line: 474


	      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;

Line: 487


	           PreInsert_Rollup_errors;

Line: 495


		      OKS_IMPORT_INSERT.Insert_Contracts;

Line: 497


				-- Invoking Post Insert Routines
				  l_stmt_num := 85;

Line: 499


			OKS_IMPORT_POST_INSERT.Import_Post_Insert;

Line: 519


	      ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
                                                            l_end_rowid);

Line: 524


	      ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
                                                      l_end_rowid,
                                                      l_any_rows_to_process,
                                                      P_commit_size,
                                                      FALSE);