CREATE OR REPLACE PROCEDURE SRPSTL.Z_MIE_TEST_3 as
/*
To populate data to Z_MIE_B_USR112_BC_ACCT from USR_112.BC_ACCT@UATCBS
exec SRPSTL.SP_GRANT_TAB('Z_MIE_B_USR112_BC_ACCT');
Notes:
1st creation - Friday, 18 December 2020 2:35:00 PM
*/
SRC BINARY_INTEGER;
RET BINARY_INTEGER;
ACCT_ID NUMBER (20);
CUST_ID NUMBER (20);
U_CUST_ID NUMBER (20);
ACCT_CODE VARCHAR2 (288);
ACCT_NAME VARCHAR2 (512);
ACCT_TYPE VARCHAR2 (9);
STATUS_TIME DATE;
CREATE_OPER_ID NUMBER (20);
CREATE_TIME DATE;
row_read number(10):=0;
row_update number(10):=0;
row_insert number(10):=0;
row_delete number(10):=0;
row_reject number(10):=0;
i number:=0;
j number:=0;
v_ErrorCode number;
v_Errortext varchar2(200);
start_time date := sysdate;
script VARCHAR2(123) := '/EDWH-DMT04/SRPETL/script/UAT/CBS/Daily/Truncate/load_B_USR112.sh';
proc_name VARCHAR2(36) := 'Z_MIE_TEST_3';
tbl_name VARCHAR2(36) := 'Z_MIE_B_USR112_BC_ACCT';
src_name VARCHAR2(36) := 'USR_112.BC_ACCT';
subject_area VARCHAR2(45) := 'UAT CBS';
frequency VARCHAR2(50) := 'DAILY';
method VARCHAR2(50) := 'TRUNCATE';
custom_text VARCHAR2(45) := null;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE Z_MIE_B_USR112_BC_ACCT';
SRC:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@uatCBS;
DBMS_HS_PASSTHROUGH.PARSE@uatCBS(SRC,
'select
ACCT_ID, CUST_ID, ACCT_CODE, ACCT_NAME, ACCT_TYPE,
sTATUS_TIME, CREATE_OPER_ID, CREATE_TIME,
(select count(*) from USR_112.BC_ACCT) as row_count
from
USR_112.BC_ACCT'
);
BEGIN
-- DBMS_OUTPUT.ENABLE(1000000);
RET:=0;
WHILE(TRUE)
LOOP
RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@uatCBS(SRC,FALSE);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,1,ACCT_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,2,CUST_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,3,ACCT_CODE);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,4,ACCT_NAME);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,5,ACCT_TYPE);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,6,STATUS_TIME);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,7,CREATE_OPER_ID);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,8,CREATE_TIME);
DBMS_HS_PASSTHROUGH.GET_VALUE@UATCBS(SRC,9,row_read);
INSERT INTO Z_MIE_B_USR112_BC_ACCT
(ACCT_ID,
CUST_ID,
ACCT_CODE,
ACCT_NAME,
ACCT_TYPE,
STATUS_TIME,
CREATE_OPER_ID,
CREATE_TIME)
VALUES
(ACCT_ID,
CUST_ID,
ACCT_CODE,
ACCT_NAME,
ACCT_TYPE,
STATUS_TIME,
CREATE_OPER_ID,
CREATE_TIME)
;
row_insert := row_insert + 1;
END LOOP;
-- commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('END OF FETCH');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@uatCBS(SRC);
END;
END;
insert into z_TRACKING_LOG
(SCRIPT_NAME, FREQUENCY, PROCEDURE_NAME, SOURCE_TABLE, TABLE_NAME, METHOD, ROWS_READ, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED, ROWS_INSERTED_BDEL,
ROWS_INSERTED_ADEL, ROWS_REJECTED, START_TIME, END_TIME, ELAPSE_TIME, REMARKS, STATUS, SUBJECT_AREA)
values
(script, frequency, proc_name||replace(decode(custom_text,null,null,' ('||custom_text||')'),' ()'), src_name,
tbl_name, method, row_read, row_insert, row_update, row_delete, NULL, NULL, row_reject, start_time, sysdate, (sysdate-start_time)* 86400,
v_ErrorText, 'SUCCESS', subject_area);
COMMIT;
END;
/