CREATE OR REPLACE PACKAGE BODY APPS.XXDC_P2_CONV_UTIL
IS
PROCEDURE gen_pre_validation (p_conv_id IN NUMBER)
IS
TYPE cur_type IS REF CURSOR;
exp_records cur_type;
x_rowid UROWID;
x_key VARCHAR2 (255);
x_src_file_name VARCHAR2 (255);
sql_stmt VARCHAR2 (4000);
--p_conv_id NUMBER (15) := 10;
upd_sql_stmt VARCHAR2 (4000);
err_sql_stmt VARCHAR2 (4000);
x_err_msg VARCHAR2 (4000);
x_process_status VARCHAR2 (2) := '-1';
l_upd_sql VARCHAR (4000);
CURSOR get_process_status (l_conv_id IN NUMBER)
IS
SELECT DISTINCT process_status
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id
AND process_status NOT IN ('E', 'UT')
AND rule_id <> -100;
--ORDER BY 1 DESC;
CURSOR get_data_validation_rules (
l_conv_id IN NUMBER,
l_process_status IN VARCHAR
)
IS
SELECT rule_id, table_name, rule_name, sql_code, process_status
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id
AND process_status = l_process_status
AND rule_id <> -100;
CURSOR get_update_sql (l_conv_id IN NUMBER)
IS
SELECT rule_id, table_name, rule_name, sql_code
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id AND process_status = 'UT';
BEGIN
FOR get_update_sql_rec IN get_update_sql (p_conv_id)
LOOP
DBMS_OUTPUT.put_line ('rule_name - ' || get_update_sql_rec.rule_name);
l_upd_sql := get_update_sql_rec.sql_code;
DBMS_OUTPUT.put_line ('Update sql_stmt l_upd_sql - ' || l_upd_sql);
END LOOP;
FOR get_process_status_rec IN get_process_status (p_conv_id)
LOOP
FOR get_data_validation_rules_rec IN
get_data_validation_rules (p_conv_id,
get_process_status_rec.process_status
)
LOOP
DBMS_OUTPUT.put_line ( 'rule_name - '
|| get_data_validation_rules_rec.rule_name
);
DBMS_OUTPUT.put_line
( 'get_data_validation_rules_rec.sql_code - '
|| get_data_validation_rules_rec.sql_code
);
DBMS_OUTPUT.put_line
( 'get_data_validation_rules_rec.table_name - '
|| get_data_validation_rules_rec.table_name
);
sql_stmt := get_data_validation_rules_rec.sql_code;
DBMS_OUTPUT.put_line ('<><><><> sql_stmt <><><> ');
DBMS_OUTPUT.put_line (sql_stmt);
DBMS_OUTPUT.put_line ('<><><><><><><><><><><><> ');
DBMS_OUTPUT.put_line ('Use of cursor in selecting exception rows');
OPEN exp_records FOR sql_stmt;
LOOP
FETCH exp_records
INTO x_rowid, x_key, x_err_msg;
EXIT WHEN exp_records%NOTFOUND;
DBMS_OUTPUT.put_line ( 'Conv Key - '
|| x_key
|| ' x_rowid - '
|| x_rowid
|| ' x_err_msg - '
|| x_err_msg
);
INSERT INTO xxdc.xxdc_p2_conv_errors
(conv_id, rule_id,
request_id, conv_key, error_msg,
created_by, creation_date, last_updated_by,
last_update_date
)
VALUES (p_conv_id, get_data_validation_rules_rec.rule_id,
fnd_global.conc_request_id, x_key, x_err_msg,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
SYSDATE
);
DBMS_OUTPUT.put_line ('After Insert');
--'Total Collected and Orginal Amounts are null');
upd_sql_stmt :=
'UPDATE '
|| get_data_validation_rules_rec.table_name
|| ' SET process_status = '
|| 'decode (process_status , ''RS'', ''RS'', ''RD'', ''RD'','''
|| get_data_validation_rules_rec.process_status
|| ''') '
|| ' where rowid = :1';
DBMS_OUTPUT.put_line (' upd_sql_stmt - ' || upd_sql_stmt);
EXECUTE IMMEDIATE upd_sql_stmt
USING x_rowid;
DBMS_OUTPUT.put_line (' After first update sql ');
DBMS_OUTPUT.put_line (' l_upd_sql - ' || l_upd_sql);
DBMS_OUTPUT.put_line
( ' get_data_validation_rules_rec.process_status - '
|| get_data_validation_rules_rec.process_status
);
DBMS_OUTPUT.put_line ('x_key - ' || x_key);
IF (l_upd_sql IS NOT NULL)
THEN
EXECUTE IMMEDIATE l_upd_sql
USING
get_data_validation_rules_rec.process_status,
x_key;
DBMS_OUTPUT.put_line ('after execute update ');
END IF;
END LOOP;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
END gen_pre_validation;
PROCEDURE gen_datatype_validation (p_conv_id IN NUMBER)
IS
v_data_type VARCHAR2 (50);
v_data_length NUMBER;
v_rowid ROWID;
v_row_data VARCHAR2 (255);
v_date DATE;
v_number NUMBER;
TYPE cur_type IS REF CURSOR;
src_records cur_type;
v_sql_stmt VARCHAR2 (4000);
v_upd_sql_stmt VARCHAR2 (4000);
v_insrt_sql_stmt VARCHAR2 (4000);
CURSOR get_map_data (v_conv_id NUMBER)
IS
SELECT *
FROM xxdc_p2_conv_table_column_map
WHERE conv_id = v_conv_id AND validate_flag = 'Y';
CURSOR get_sql -- (v_conv_id NUMBER, v_table_name VARCHAR2)
IS
SELECT *
FROM xxdc_p2_conv_rules
WHERE rule_id = 0;
BEGIN
FOR get_map_data_rec IN get_map_data (p_conv_id)
LOOP
DBMS_OUTPUT.put_line ('******************************************* ');
DBMS_OUTPUT.put_line ( 'get_map_data_rec.TARGET_TABLE_NAME - '
|| get_map_data_rec.target_table_name
);
DBMS_OUTPUT.put_line ( 'get_map_data_rec.TARGET_COLUMN_NAME - '
|| get_map_data_rec.target_column_name
);
SELECT data_type, data_length
INTO v_data_type, v_data_length
FROM all_tab_columns
WHERE table_name = UPPER (get_map_data_rec.target_table_name)
AND column_name = UPPER (get_map_data_rec.target_column_name);
DBMS_OUTPUT.put_line (' v_data_type - ' || v_data_type);
DBMS_OUTPUT.put_line (' v_data_length -' || v_data_length);
IF (v_data_type = 'VARCHAR2')
THEN
v_sql_stmt :=
'SELECT ROWID FROM '
|| get_map_data_rec.source_table_name
|| ' WHERE length( '
|| get_map_data_rec.source_column_name
|| ' ) > '
|| v_data_length;
DBMS_OUTPUT.put_line ('v_sql_stmt - ' || v_sql_stmt);
OPEN src_records FOR v_sql_stmt;
LOOP
FETCH src_records
INTO v_rowid;
EXIT WHEN src_records%NOTFOUND;
FOR get_sql_rec IN get_sql
LOOP
DBMS_OUTPUT.put_line (' In varchar2 exception ');
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
v_upd_sql_stmt :=
'UPDATE '
|| get_map_data_rec.source_table_name
|| ' SET process_status = '''
|| get_sql_rec.process_status
|| ''' where rowid = :1';
DBMS_OUTPUT.put_line (' v_upd_sql_stmt - ' || v_upd_sql_stmt);
EXECUTE IMMEDIATE v_upd_sql_stmt
USING v_rowid;
v_insrt_sql_stmt := get_sql_rec.sql_code;
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
v_insrt_sql_stmt :=
REPLACE (v_insrt_sql_stmt,
'tab_name',
get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
EXECUTE IMMEDIATE v_insrt_sql_stmt
USING p_conv_id,
get_sql_rec.rule_id,
get_map_data_rec.pk_column_name,
get_map_data_rec.source_column_name,
get_map_data_rec.source_column_name,
v_data_type,
get_map_data_rec.source_table_name,
v_rowid;
DBMS_OUTPUT.put_line ('after execute update ');
END LOOP;
END LOOP;
ELSIF (v_data_type = 'DATE')
THEN
v_sql_stmt :=
'SELECT ROWID, '
|| get_map_data_rec.source_column_name
|| ' FROM '
|| get_map_data_rec.source_table_name;
DBMS_OUTPUT.put_line ('v_sql_stmt - ' || v_sql_stmt);
OPEN src_records FOR v_sql_stmt;
LOOP
FETCH src_records
INTO v_rowid, v_row_data;
EXIT WHEN src_records%NOTFOUND;
BEGIN
v_date := TO_DATE (v_row_data, 'MM/DD/YYYY HH24:MI:SS');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (' In to_date exception ');
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);
FOR get_sql_rec IN get_sql
LOOP
v_upd_sql_stmt :=
'UPDATE '
|| get_map_data_rec.source_table_name
|| ' SET process_status = '''
|| get_sql_rec.process_status
|| ''' where rowid = :1';
DBMS_OUTPUT.put_line ( ' v_upd_sql_stmt - '
|| v_upd_sql_stmt
);
EXECUTE IMMEDIATE v_upd_sql_stmt
USING v_rowid;
v_insrt_sql_stmt := get_sql_rec.sql_code;
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
DBMS_OUTPUT.put_line ( 'get_sql_rec.rule_id - '
|| get_sql_rec.rule_id
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.pk_column_name - '
|| get_map_data_rec.pk_column_name
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_column_name - '
|| get_map_data_rec.source_column_name
);
DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_table_name - '
|| get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
v_insrt_sql_stmt :=
REPLACE (v_insrt_sql_stmt,
'tab_name',
get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
EXECUTE IMMEDIATE v_insrt_sql_stmt
USING
p_conv_id,
get_sql_rec.rule_id,
get_map_data_rec.pk_column_name,
get_map_data_rec.source_column_name,
get_map_data_rec.source_column_name,
v_data_type,
--get_map_data_rec.source_table_name,
v_rowid;
DBMS_OUTPUT.put_line ('after execute update ');
END LOOP;
END;
END LOOP;
ELSIF (v_data_type = 'NUMBER')
THEN
v_sql_stmt :=
'SELECT ROWID, '
|| get_map_data_rec.source_column_name
|| ' FROM '
|| get_map_data_rec.source_table_name;
DBMS_OUTPUT.put_line ('v_sql_stmt - ' || v_sql_stmt);
OPEN src_records FOR v_sql_stmt;
LOOP
FETCH src_records
INTO v_rowid, v_row_data;
EXIT WHEN src_records%NOTFOUND;
BEGIN
v_number := TO_NUMBER (v_row_data);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (' In to_number exception ');
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);
FOR get_sql_rec IN get_sql
LOOP
v_upd_sql_stmt :=
'UPDATE '
|| get_map_data_rec.source_table_name
|| ' SET process_status = '''
|| get_sql_rec.process_status
|| ''' where rowid = :1';
DBMS_OUTPUT.put_line ( ' v_upd_sql_stmt - '
|| v_upd_sql_stmt
);
EXECUTE IMMEDIATE v_upd_sql_stmt
USING v_rowid;
DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
DBMS_OUTPUT.put_line ( 'get_sql_rec.rule_id - '
|| get_sql_rec.rule_id
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.pk_column_name - '
|| get_map_data_rec.pk_column_name
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_column_name - '
|| get_map_data_rec.source_column_name
);
DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_table_name - '
|| get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
v_insrt_sql_stmt := get_sql_rec.sql_code;
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
v_insrt_sql_stmt :=
REPLACE (v_insrt_sql_stmt,
'tab_name',
get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
EXECUTE IMMEDIATE v_insrt_sql_stmt
USING
p_conv_id,
get_sql_rec.rule_id,
get_map_data_rec.pk_column_name,
get_map_data_rec.source_column_name,
get_map_data_rec.source_column_name,
v_data_type,
--get_map_data_rec.source_table_name,
v_rowid;
DBMS_OUTPUT.put_line ('after execute update ');
END LOOP;
END;
END LOOP;
END IF;
DBMS_OUTPUT.put_line ('******************************************* ');
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('In exception ');
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
END gen_datatype_validation;
PROCEDURE gen_ins_upd_error (
p_conv_id IN NUMBER,
p_rule_id IN NUMBER,
p_sqlldr_req_id IN NUMBER,
p_key IN VARCHAR2,
p_err_msg IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_err_msg VARCHAR2 (4000);
v_sql_code VARCHAR2 (4000);
CURSOR get_upd_sql (v_conv_id IN NUMBER, v_rule_id IN NUMBER)
IS
SELECT sql_code, process_status, rule_id
FROM xxdc_p2_conv_rules
WHERE rule_id = v_rule_id AND conv_id = v_conv_id;
BEGIN
IF (p_rule_id IS NOT NULL) AND (p_key IS NOT NULL)
THEN
/*
* executing insert command to insert error records in XXDC_P2_CONV_ERRORS
*/
v_err_msg := SUBSTR (p_err_msg, 1, 4000);
fnd_file.put_line (fnd_file.LOG,
'XXDC_P2_CONV_ERRORS - v_err_msg - ' || v_err_msg
);
EXECUTE IMMEDIATE 'INSERT INTO xxdc.xxdc_p2_conv_errors VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)'
USING p_conv_id,
p_rule_id,
fnd_global.conc_request_id,
p_key,
v_err_msg,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
p_sqlldr_req_id;
fnd_file.put_line (fnd_file.LOG,
'XXDC_P2_CONV_ERRORS - After insert');
/*
* executing update command to update error status in source tables..
*/
FOR get_upd_sql_rec IN get_upd_sql (p_conv_id, p_rule_id)
LOOP
v_sql_code := get_upd_sql_rec.sql_code;
IF (v_sql_code IS NOT NULL)
THEN
fnd_file.put_line (fnd_file.LOG,
'XXDC_P2_CONV_ERRORS - v_sql_code - '
|| v_sql_code
);
EXECUTE IMMEDIATE v_sql_code
USING get_upd_sql_rec.process_status,
p_key,
p_sqlldr_req_id;
END IF;
END LOOP;
END IF;
COMMIT;
END;
------
------ controller below
------
PROCEDURE controller (
p_mode IN VARCHAR2,
p_info IN VARCHAR2,
p_conv_id IN NUMBER,
p_file_cdate IN VARCHAR2,
p_user_id IN NUMBER,
p_user IN VARCHAR2,
p_run_req_id IN NUMBER,
p_sqlload_req_id IN NUMBER,
p_launched_req_id IN NUMBER,
p_launched_prog IN VARCHAR2
)
IS
CURSOR src_file_cur
IS
SELECT source_file_date, source_file_name, load_file_name
FROM xxdc_p2_source_files_v
WHERE conv_id = p_conv_id AND source_file_cdate = p_file_cdate;
CURSOR load_file_cur
IS
SELECT load_file_name
FROM xxdc_p2_source_files_v
WHERE conv_id = p_conv_id AND source_file_cdate IS NULL;
CURSOR stg_tbl_cur
IS
SELECT 'XXDC.' || table_name table_name
FROM all_tables, xxdc.xxdc_p2_convs xpc
WHERE owner = 'XXDC'
AND 'XXDC_' || xpc.conv_code =
SUBSTR (table_name, 1, LENGTH (xpc.conv_code) + 5)
AND conv_id = p_conv_id;
v_sql VARCHAR2 (32767);
v_archive_file_name VARCHAR2 (255);
v_prog_unit VARCHAR (100) := gc_pkg || '.CONTROLLER';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
IF p_mode = 'LP' AND p_info IN ('SQLLOAD FAILED', 'SQLLOAD SUCCESS')
THEN
-- run post load stuff
-- clean up loaded file
FOR load_file_rec IN load_file_cur
LOOP
UTL_FILE.fremove (gc_p2_data_dir, load_file_rec.load_file_name);
xxd_utils.LOG
(p_log_text => gv_debug_log_text,
p_new_text => 'Conversion Source file removed: '
|| gc_p2_data_dir
|| '/'
|| load_file_rec.load_file_name,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
END LOOP;
-- Update launched request status
UPDATE xxdc.xxdc_p2_conv_runs
SET launched_request_status = p_info
WHERE run_request_id = p_run_req_id
AND sqlload_request_id = p_sqlload_req_id;
IF p_info = 'SQLLOAD SUCCESS'
THEN
-- update staging table with request id
FOR stg_tbl_rec IN stg_tbl_cur
LOOP
v_sql :=
'UPDATE '
|| stg_tbl_rec.table_name
|| ' SET request_id = '
|| p_sqlload_req_id
|| ', created_by = '
|| p_user_id
|| ', last_updated_by = '
|| p_user_id
|| ' where nvl(request_id,-1) = -1';
EXECUTE IMMEDIATE v_sql;
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => 'Set request ID '
|| p_sqlload_req_id
|| ' for '
|| stg_tbl_rec.table_name
|| ' with '
|| v_sql,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
END LOOP;
IF p_conv_id = 18
THEN
xxdc_load_insp_case.split_data (p_run_req_id);
END IF;
END IF;
ELSE
-- log run
INSERT INTO xxdc.xxdc_p2_conv_runs
(run_mode, run_request_id, run_date, run_by_user_id,
run_by_user, conv_id,
source_file_date,
source_file_cdate, sqlload_request_id,
launched_request_id, launched_program,
launched_request_status
)
VALUES (p_mode, p_run_req_id, SYSDATE, p_user_id,
p_user, p_conv_id,
TO_DATE (p_file_cdate, 'YYYY-MM-DD_HH24-MI-SS'),
p_file_cdate, p_sqlload_req_id,
p_launched_req_id, p_launched_prog,
p_info
);
END IF;
-- archive/move file for SQLLOAD
IF p_mode = 'LP' AND p_info = 'SQLLOAD'
THEN
FOR src_file_rec IN src_file_cur
LOOP
xxd_utils.LOG
(p_log_text => gv_debug_log_text,
p_new_text => 'Process conversion source file: '
|| xxd_utils.get_path
(gc_p2_data_dir)
|| '/'
|| src_file_rec.source_file_name,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
v_archive_file_name :=
'LOADED_'
|| TO_CHAR (SYSDATE, 'YYYY-MM-DD_HH24:MI:SS')
|| '_'
|| src_file_rec.source_file_name;
-- copy to archive
UTL_FILE.fcopy (gc_p2_data_dir,
src_file_rec.source_file_name,
gc_p2_archive_dir,
v_archive_file_name
);
xxd_utils.LOG
(p_log_text => gv_debug_log_text,
p_new_text => 'Conversion Source file archived to: '
|| xxd_utils.get_path
(gc_p2_archive_dir)
|| '/'
|| v_archive_file_name,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
-- rename for load
UTL_FILE.frename (gc_p2_data_dir,
src_file_rec.source_file_name,
gc_p2_data_dir,
src_file_rec.load_file_name,
gc_true
);
xxd_utils.LOG
(p_log_text => gv_debug_log_text,
p_new_text => 'Conversion Source file renamed to: '
|| src_file_rec.load_file_name,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
-- log file
INSERT INTO xxdc.xxdc_p2_conv_run_files
(run_request_id, conv_id,
source_file_date,
source_file_name, archive_file_name
)
VALUES (p_run_req_id, p_conv_id,
src_file_rec.source_file_date,
src_file_rec.source_file_name, v_archive_file_name
);
END LOOP;
END IF;
-- end p_mode
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => 'james.jen@calrecycle.ca.gov',
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
DBMS_OUTPUT.put_line (gv_debug_log_text);
gv_debug_log_text := NULL;
END controller;
FUNCTION get_attr_group_id (
p_attr_grp_disp_name IN ego_attr_groups_v.attr_group_disp_name%TYPE
)
RETURN NUMBER
IS
v_attr_group_id NUMBER;
BEGIN
SELECT attr_group_id
INTO v_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_disp_name = p_attr_grp_disp_name;
RETURN v_attr_group_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_attr_group_id;
PROCEDURE gen_pre_validation (p_conv_id IN NUMBER, p_request_id IN NUMBER)
IS
TYPE cur_type IS REF CURSOR;
exp_records cur_type;
x_rowid UROWID;
x_key VARCHAR2 (255);
x_src_file_name VARCHAR2 (255);
sql_stmt VARCHAR2 (4000);
--p_conv_id NUMBER (15) := 10;
upd_sql_stmt VARCHAR2 (4000);
err_sql_stmt VARCHAR2 (4000);
x_err_msg VARCHAR2 (4000);
x_process_status VARCHAR2 (2) := '-1';
l_upd_sql VARCHAR (4000);
v_prog_unit VARCHAR (100) := gc_pkg || '.GEN_PRE_VALIDATION';
CURSOR get_process_status (l_conv_id IN NUMBER)
IS
SELECT DISTINCT process_status
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id
AND process_status NOT IN ('E', 'UT')
AND rule_id <> -100;
--ORDER BY 1 DESC;
CURSOR get_data_validation_rules (
l_conv_id IN NUMBER,
l_process_status IN VARCHAR
)
IS
SELECT rule_id, table_name, rule_name, sql_code, process_status
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id
AND process_status = l_process_status
AND rule_id <> -100;
CURSOR get_update_sql (l_conv_id IN NUMBER)
IS
SELECT rule_id, table_name, rule_name, sql_code
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id AND process_status = 'UT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
FOR get_process_status_rec IN get_process_status (p_conv_id)
LOOP
FOR get_data_validation_rules_rec IN
get_data_validation_rules (p_conv_id,
get_process_status_rec.process_status
)
LOOP
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'Rule Name - '
|| get_data_validation_rules_rec.rule_name
|| 'Table Name - '
|| get_data_validation_rules_rec.table_name
|| 'Process Status -'
|| get_data_validation_rules_rec.process_status,
p_prog_unit => v_prog_unit
);
/*dbms_output.put_line('Rule Name - '
|| get_data_validation_rules_rec.rule_name
|| 'Table Name - '
|| get_data_validation_rules_rec.table_name
|| 'Process Status -'
|| get_data_validation_rules_rec.process_status);*/
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'SQL Code - '
|| TO_CHAR
(get_data_validation_rules_rec.sql_code
),
p_prog_unit => v_prog_unit
);
sql_stmt := get_data_validation_rules_rec.sql_code;
DBMS_OUTPUT.put_line (' sql_stmt - ' || sql_stmt);
OPEN exp_records FOR sql_stmt USING p_request_id;
LOOP
FETCH exp_records
INTO x_rowid, x_key, x_err_msg;
EXIT WHEN exp_records%NOTFOUND;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'Conv Key - '
|| x_key
|| ' x_rowid - '
|| x_rowid
|| ' x_err_msg - '
|| x_err_msg,
p_prog_unit => v_prog_unit
);
/*dbms_output.put_line('Conv Key - '
|| x_key
|| ' x_rowid - '
|| x_rowid
|| ' x_err_msg - '
|| x_err_msg);
*/
INSERT INTO xxdc.xxdc_p2_conv_errors
(conv_id, rule_id,
request_id, conv_key, error_msg,
created_by, creation_date, last_updated_by,
last_update_date
)
VALUES (p_conv_id, get_data_validation_rules_rec.rule_id,
fnd_global.conc_request_id, x_key, x_err_msg,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
SYSDATE
);
upd_sql_stmt :=
'UPDATE '
|| get_data_validation_rules_rec.table_name
|| ' SET process_status = '
|| 'decode (process_status , ''RS'', ''RS'', ''RD'', ''RD'','''
|| get_data_validation_rules_rec.process_status
|| ''') '
|| ' where rowid = :1';
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => ' upd_sql_stmt - '
|| upd_sql_stmt,
p_prog_unit => v_prog_unit
);
--dbms_output.put_line('upd_sql_stmt - '||upd_sql_stmt);
--dbms_output.put_line('x_rowid - '||x_rowid);
EXECUTE IMMEDIATE upd_sql_stmt
USING x_rowid;
FOR get_update_sql_rec IN get_update_sql (p_conv_id)
LOOP
l_upd_sql := get_update_sql_rec.sql_code;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => ' l_upd_sql - '
|| l_upd_sql
|| 'rule_name - '
|| get_update_sql_rec.rule_name,
p_prog_unit => v_prog_unit
);
END LOOP;
--dbms_output.put_line('l_upd_sql - '||l_upd_sql);
IF (l_upd_sql IS NOT NULL)
THEN
EXECUTE IMMEDIATE l_upd_sql
USING
get_data_validation_rules_rec.process_status,
x_key,
p_request_id;
END IF;
END LOOP;
END LOOP;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => '0',
p_debug => 'Y',
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END gen_pre_validation;
PROCEDURE gen_datatype_validation (
p_conv_id IN NUMBER,
p_request_id IN NUMBER
)
IS
v_data_type VARCHAR2 (50);
v_data_length NUMBER;
v_rowid ROWID;
v_row_data VARCHAR2 (255);
v_date DATE;
v_number NUMBER;
TYPE cur_type IS REF CURSOR;
src_records cur_type;
v_sql_stmt VARCHAR2 (4000);
v_upd_sql_stmt VARCHAR2 (4000);
v_insrt_sql_stmt VARCHAR2 (4000);
CURSOR get_map_data (v_conv_id NUMBER)
IS
SELECT *
FROM xxdc_p2_conv_table_column_map
WHERE conv_id = v_conv_id AND validate_flag = 'Y';
CURSOR get_sql -- (v_conv_id NUMBER, v_table_name VARCHAR2)
IS
SELECT *
FROM xxdc_p2_conv_rules
WHERE rule_id = 0;
BEGIN
FOR get_map_data_rec IN get_map_data (p_conv_id)
LOOP
DBMS_OUTPUT.put_line ('******************************************* ');
DBMS_OUTPUT.put_line ( 'get_map_data_rec.TARGET_TABLE_NAME - '
|| get_map_data_rec.target_table_name
);
DBMS_OUTPUT.put_line ( 'get_map_data_rec.TARGET_COLUMN_NAME - '
|| get_map_data_rec.target_column_name
);
SELECT data_type, data_length
INTO v_data_type, v_data_length
FROM all_tab_columns
WHERE table_name = UPPER (get_map_data_rec.target_table_name)
AND column_name = UPPER (get_map_data_rec.target_column_name);
DBMS_OUTPUT.put_line (' v_data_type - ' || v_data_type);
DBMS_OUTPUT.put_line (' v_data_length -' || v_data_length);
IF (v_data_type = 'VARCHAR2')
THEN
v_sql_stmt :=
'SELECT ROWID FROM '
|| get_map_data_rec.source_table_name
|| ' WHERE length( '
|| get_map_data_rec.source_column_name
|| ' ) > '
|| v_data_length
|| ' AND NVL(REQUEST_ID, -1) = nvl('
|| p_request_id
|| ', -1)';
DBMS_OUTPUT.put_line ('v_sql_stmt - ' || v_sql_stmt);
OPEN src_records FOR v_sql_stmt;
LOOP
FETCH src_records
INTO v_rowid;
EXIT WHEN src_records%NOTFOUND;
FOR get_sql_rec IN get_sql
LOOP
DBMS_OUTPUT.put_line (' In varchar2 exception ');
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
v_upd_sql_stmt :=
'UPDATE '
|| get_map_data_rec.source_table_name
|| ' SET process_status = '''
|| get_sql_rec.process_status
|| ''' where rowid = :1';
DBMS_OUTPUT.put_line (' v_upd_sql_stmt - ' || v_upd_sql_stmt);
EXECUTE IMMEDIATE v_upd_sql_stmt
USING v_rowid;
v_insrt_sql_stmt := get_sql_rec.sql_code;
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
v_insrt_sql_stmt :=
REPLACE (v_insrt_sql_stmt,
'tab_name',
get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
EXECUTE IMMEDIATE v_insrt_sql_stmt
USING p_conv_id,
get_sql_rec.rule_id,
get_map_data_rec.pk_column_name,
get_map_data_rec.source_column_name,
get_map_data_rec.source_column_name,
v_data_type,
get_map_data_rec.source_table_name,
v_rowid;
DBMS_OUTPUT.put_line ('after execute update ');
END LOOP;
END LOOP;
ELSIF (v_data_type = 'DATE')
THEN
v_sql_stmt :=
'SELECT ROWID, '
|| get_map_data_rec.source_column_name
|| ' FROM '
|| get_map_data_rec.source_table_name
|| ' WHERE NVL(REQUEST_ID, -1) = nvl('
|| p_request_id
|| ', -1)';
DBMS_OUTPUT.put_line ('v_sql_stmt - ' || v_sql_stmt);
OPEN src_records FOR v_sql_stmt;
LOOP
FETCH src_records
INTO v_rowid, v_row_data;
EXIT WHEN src_records%NOTFOUND;
BEGIN
v_date := TO_DATE (v_row_data, 'MM/DD/YYYY HH24:MI:SS');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (' In to_date exception ');
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);
FOR get_sql_rec IN get_sql
LOOP
v_upd_sql_stmt :=
'UPDATE '
|| get_map_data_rec.source_table_name
|| ' SET process_status = '''
|| get_sql_rec.process_status
|| ''' where rowid = :1';
DBMS_OUTPUT.put_line ( ' v_upd_sql_stmt - '
|| v_upd_sql_stmt
);
EXECUTE IMMEDIATE v_upd_sql_stmt
USING v_rowid;
v_insrt_sql_stmt := get_sql_rec.sql_code;
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
DBMS_OUTPUT.put_line ( 'get_sql_rec.rule_id - '
|| get_sql_rec.rule_id
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.pk_column_name - '
|| get_map_data_rec.pk_column_name
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_column_name - '
|| get_map_data_rec.source_column_name
);
DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_table_name - '
|| get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
v_insrt_sql_stmt :=
REPLACE (v_insrt_sql_stmt,
'tab_name',
get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
EXECUTE IMMEDIATE v_insrt_sql_stmt
USING
p_conv_id,
get_sql_rec.rule_id,
get_map_data_rec.pk_column_name,
get_map_data_rec.source_column_name,
get_map_data_rec.source_column_name,
v_data_type,
--get_map_data_rec.source_table_name,
v_rowid;
DBMS_OUTPUT.put_line ('after execute update ');
END LOOP;
END;
END LOOP;
ELSIF (v_data_type = 'NUMBER')
THEN
v_sql_stmt :=
'SELECT ROWID, '
|| get_map_data_rec.source_column_name
|| ' FROM '
|| get_map_data_rec.source_table_name
|| ' WHERE NVL(REQUEST_ID, -1) = nvl('
|| p_request_id
|| ', -1)';
DBMS_OUTPUT.put_line ('v_sql_stmt - ' || v_sql_stmt);
OPEN src_records FOR v_sql_stmt;
LOOP
FETCH src_records
INTO v_rowid, v_row_data;
EXIT WHEN src_records%NOTFOUND;
BEGIN
v_number := TO_NUMBER (v_row_data);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (' In to_number exception ');
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);
FOR get_sql_rec IN get_sql
LOOP
v_upd_sql_stmt :=
'UPDATE '
|| get_map_data_rec.source_table_name
|| ' SET process_status = '''
|| get_sql_rec.process_status
|| ''' where rowid = :1';
DBMS_OUTPUT.put_line ( ' v_upd_sql_stmt - '
|| v_upd_sql_stmt
);
EXECUTE IMMEDIATE v_upd_sql_stmt
USING v_rowid;
DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
DBMS_OUTPUT.put_line ( 'get_sql_rec.rule_id - '
|| get_sql_rec.rule_id
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.pk_column_name - '
|| get_map_data_rec.pk_column_name
);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_column_name - '
|| get_map_data_rec.source_column_name
);
DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
DBMS_OUTPUT.put_line
( 'get_map_data_rec.source_table_name - '
|| get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
v_insrt_sql_stmt := get_sql_rec.sql_code;
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
v_insrt_sql_stmt :=
REPLACE (v_insrt_sql_stmt,
'tab_name',
get_map_data_rec.source_table_name
);
DBMS_OUTPUT.put_line ( 'v_insrt_sql_stmt - '
|| v_insrt_sql_stmt
);
EXECUTE IMMEDIATE v_insrt_sql_stmt
USING
p_conv_id,
get_sql_rec.rule_id,
get_map_data_rec.pk_column_name,
get_map_data_rec.source_column_name,
get_map_data_rec.source_column_name,
v_data_type,
--get_map_data_rec.source_table_name,
v_rowid;
DBMS_OUTPUT.put_line ('after execute update ');
END LOOP;
END;
END LOOP;
END IF;
DBMS_OUTPUT.put_line ('******************************************* ');
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('In exception ');
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
END gen_datatype_validation;
PROCEDURE get_case_party_info (
p_legacy_acct_num IN VARCHAR2,
p_party_id OUT NUMBER,
p_party_type OUT VARCHAR2,
p_account_id OUT NUMBER,
p_party_site_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_LOAD_INSP_PARTY_INFO';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
SELECT hp.party_id, hp.party_type, hps.party_site_id,
hca.cust_account_id
INTO p_party_id, p_party_type, p_party_site_id,
p_account_id
FROM hz_parties hp, hz_cust_accounts hca, hz_party_sites hps
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hps.identifying_address_flag = 'Y'
AND hca.account_number = p_legacy_acct_num;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_party_id := -1;
p_party_type := NULL;
p_account_id := -1;
p_party_site_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_party_id := -2;
p_party_type := NULL;
p_account_id := -2;
p_party_site_id := -2;
END get_case_party_info;
PROCEDURE prevalidation (
p_conv_id IN NUMBER,
p_request_id IN NUMBER,
p_log OUT CLOB,
p_result OUT VARCHAR2
)
IS
TYPE cur_type IS REF CURSOR;
exp_records cur_type;
x_rowid UROWID;
x_key VARCHAR2 (255);
x_src_file_name VARCHAR2 (255);
sql_stmt VARCHAR2 (4000);
--p_conv_id NUMBER (15) := 10;
upd_sql_stmt VARCHAR2 (4000);
err_sql_stmt VARCHAR2 (4000);
x_err_msg VARCHAR2 (4000);
x_process_status VARCHAR2 (2) := '-1';
l_upd_sql VARCHAR (4000);
v_insert_cnt INTEGER := 0;
v_update_cnt INTEGER := 0;
v_prog_unit VARCHAR (100) := gc_pkg || '.PREVALIDATION';
CURSOR get_process_status (l_conv_id IN NUMBER)
IS
SELECT DISTINCT process_status
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id
AND process_status NOT IN ('E', 'UT')
AND rule_id <> -100;
--ORDER BY 1 DESC;
CURSOR get_data_validation_rules (
l_conv_id IN NUMBER,
l_process_status IN VARCHAR
)
IS
SELECT rule_id, rule_number, table_name, rule_name, sql_code,
process_status
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id
AND process_status = l_process_status
AND rule_id <> -100;
CURSOR get_update_sql (l_conv_id IN NUMBER)
IS
SELECT rule_id, table_name, rule_name, sql_code
FROM xxdc_p2_conv_rules
WHERE conv_id = l_conv_id AND process_status = 'UT';
BEGIN
p_result := 0;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'Conv ID: ' || p_conv_id,
p_dt_stamp_yn => 'N',
p_prog_unit => v_prog_unit
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'SQLLoad Request ID: '
|| p_request_id,
p_dt_stamp_yn => 'N',
p_prog_unit => v_prog_unit
);
FOR get_process_status_rec IN get_process_status (p_conv_id)
LOOP
FOR get_data_validation_rules_rec IN
get_data_validation_rules (p_conv_id,
get_process_status_rec.process_status
)
LOOP
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'Rule ID/#/Name: '
|| get_data_validation_rules_rec.rule_id
|| '/'
|| get_data_validation_rules_rec.rule_number
|| '/'
|| get_data_validation_rules_rec.rule_name,
p_prog_unit => v_prog_unit
);
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'Table: '
|| get_data_validation_rules_rec.table_name
|| ' Process Status: '
|| get_data_validation_rules_rec.process_status,
p_prog_unit => v_prog_unit
);
/**
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => 'SQL: '
|| TO_CHAR
(get_data_validation_rules_rec.sql_code)
,p_prog_unit => v_prog_unit);
**/
sql_stmt := get_data_validation_rules_rec.sql_code;
v_insert_cnt := 0;
v_update_cnt := 0;
OPEN exp_records FOR sql_stmt USING p_request_id;
LOOP
FETCH exp_records
INTO x_rowid, x_key, x_err_msg;
EXIT WHEN exp_records%NOTFOUND;
INSERT INTO xxdc.xxdc_p2_conv_errors
(conv_id,
rule_id,
request_id, conv_key, error_msg,
created_by, creation_date, last_updated_by,
last_update_date, sqlload_request_id
)
VALUES (p_conv_id,
get_data_validation_rules_rec.rule_id,
fnd_global.conc_request_id, x_key, x_err_msg,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
SYSDATE, p_request_id
);
v_insert_cnt := v_insert_cnt + SQL%ROWCOUNT;
upd_sql_stmt :=
'UPDATE xxdc.'
|| get_data_validation_rules_rec.table_name
|| ' SET process_status = '
|| 'decode (process_status , ''RS'', ''RS'', ''RD'', ''RD'','''
|| get_data_validation_rules_rec.process_status
|| ''') '
|| ' where rowid = :1';
/**
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => ' upd_sql_stmt - ' || upd_sql_stmt
,p_prog_unit => v_prog_unit);
**/
EXECUTE IMMEDIATE upd_sql_stmt
USING x_rowid;
v_update_cnt := v_update_cnt + SQL%ROWCOUNT;
FOR get_update_sql_rec IN get_update_sql (p_conv_id)
LOOP
l_upd_sql := get_update_sql_rec.sql_code;
/**
xxd_utils.LOG (p_log_text => gv_debug_log_clob
,p_new_text => ' l_upd_sql - '
|| l_upd_sql
|| 'rule_name - '
|| get_update_sql_rec.rule_name
,p_prog_unit => v_prog_unit);
**/
END LOOP;
--dbms_output.put_line('l_upd_sql - '||l_upd_sql);
IF (l_upd_sql IS NOT NULL)
THEN
EXECUTE IMMEDIATE l_upd_sql
USING
get_data_validation_rules_rec.process_status,
x_key,
p_request_id;
END IF;
END LOOP;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => v_insert_cnt
|| ' record(s) inserted into XXDC.XXDC_P2_CONV_ERRORS for rule.',
p_prog_unit => v_prog_unit
);
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => v_update_cnt
|| ' record(s) updated in '
|| get_data_validation_rules_rec.table_name
|| ' for rule.',
p_prog_unit => v_prog_unit
);
IF (v_insert_cnt > 0) OR (v_update_cnt > 0)
THEN
p_result := 1;
END IF;
END LOOP;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
p_log := gv_debug_log_clob;
EXCEPTION
WHEN OTHERS
THEN
p_result := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
p_log := gv_debug_log_clob;
END prevalidation;
----------------------------------------------------
---- shared lookup procedures/functions below ------
----------------------------------------------------
--
-- to be clean/revised
--
PROCEDURE get_bill_to_site_use_id (
p_legacy_acct_num IN VARCHAR2,
p_bill_to_party_id OUT NUMBER,
p_cust_acct_id OUT NUMBER,
p_bill_to_site_id OUT NUMBER,
p_bill_to_site_use_id OUT NUMBER
)
IS
v_party_id hz_parties.party_id%TYPE := NULL;
v_cust_acct_id hz_cust_accounts.cust_account_id%TYPE := NULL;
v_bill_to_site_id hz_party_sites.party_site_id%TYPE := NULL;
v_bill_to_site_use_id hz_party_site_uses.party_site_use_id%TYPE
:= NULL;
BEGIN
SELECT aa.cust_account_id, ua.site_use_id, sa.party_site_id,
hp.party_id
INTO v_cust_acct_id, v_bill_to_site_id, v_bill_to_site_use_id,
v_party_id
FROM hz_cust_accounts_all aa,
hz_parties hp,
hz_cust_acct_sites_all sa,
hz_cust_site_uses_all ua
WHERE aa.account_number = p_legacy_acct_num
AND hp.party_id = aa.party_id
AND sa.cust_account_id = aa.cust_account_id
AND sa.cust_acct_site_id = ua.cust_acct_site_id
AND ua.site_use_code = 'BILL_TO'
AND ua.status = 'A'
AND ua.primary_flag = 'Y';
p_bill_to_party_id := v_party_id;
p_cust_acct_id := v_cust_acct_id;
p_bill_to_site_id := v_bill_to_site_id;
p_bill_to_site_use_id := v_bill_to_site_use_id;
EXCEPTION
WHEN OTHERS
THEN
p_bill_to_party_id := NULL;
p_bill_to_site_id := NULL;
p_bill_to_site_use_id := NULL;
p_cust_acct_id := NULL;
END get_bill_to_site_use_id;
--
-- generic utility
--
FUNCTION get_inventory_item_id (p_caller IN VARCHAR2, p_segment1 IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_INVENTORY_ITEM_ID';
v_inventory_item_id mtl_system_items_vl.inventory_item_id%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT inventory_item_id
INTO v_inventory_item_id
FROM mtl_system_items_vl iv
WHERE iv.segment1 = p_segment1
AND iv.inventory_item_status_code = 'Active';
RETURN v_inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_segment1 - '
|| p_segment1
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inventory_item_id;
FUNCTION get_person_id (p_caller IN VARCHAR2, p_emp_num IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_PERSON_ID';
v_person_id per_all_people_f.person_id%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT person_id
INTO v_person_id
FROM per_all_people_f papf
WHERE papf.employee_number = p_emp_num
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
RETURN v_person_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_emp_num - '
|| p_emp_num
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_person_id;
FUNCTION get_attr_group_id (
p_caller IN VARCHAR2,
p_attr_grp_disp_name IN ego_attr_groups_v.attr_group_disp_name%TYPE
)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_ATTR_GROUP_ID';
v_attr_group_id NUMBER;
BEGIN
gv_debug_log_text := NULL;
SELECT attr_group_id
INTO v_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_disp_name = p_attr_grp_disp_name;
RETURN v_attr_group_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_attr_grp_disp_name - '
|| p_attr_grp_disp_name
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_attr_group_id;
--
-- Case stuff
--
PROCEDURE get_party_info (
p_caller IN VARCHAR2,
p_legacy_acct_num IN VARCHAR2,
p_party_id OUT NUMBER,
p_party_type OUT VARCHAR2,
p_account_id OUT NUMBER,
p_party_site_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_PARTY_INFO';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
SELECT hp.party_id, hp.party_type, hps.party_site_id,
hca.cust_account_id
INTO p_party_id, p_party_type, p_party_site_id,
p_account_id
FROM hz_parties hp, hz_cust_accounts hca, hz_party_sites hps
WHERE hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hps.identifying_address_flag = 'Y'
AND hca.account_number = p_legacy_acct_num;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_party_id := -1;
p_party_type := NULL;
p_account_id := -1;
p_party_site_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_party_id := -2;
p_party_type := NULL;
p_account_id := -2;
p_party_site_id := -2;
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| 'Party_info '
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
END get_party_info;
PROCEDURE get_bill_to_pt_site_use_id (
p_caller IN VARCHAR2,
p_legacy_acct_num IN VARCHAR2,
p_bill_to_party_id OUT NUMBER,
p_cust_acct_id OUT NUMBER,
p_bill_to_site_id OUT NUMBER,
p_bill_to_site_use_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_BILL_TO_PT_SITE_USE_ID';
BEGIN
SELECT ps.party_site_id, pu.party_site_use_id, ca.cust_account_id,
hp.party_id
INTO p_bill_to_site_id, p_bill_to_site_use_id, p_cust_acct_id,
p_bill_to_party_id
FROM hz_parties hp,
hz_cust_accounts_all ca,
hz_party_sites ps,
hz_party_site_uses pu
WHERE ca.account_number = p_legacy_acct_num
AND ca.party_id = hp.party_id
AND ps.party_id = ca.party_id
AND pu.party_site_id = ps.party_site_id
AND ps.identifying_address_flag = 'Y'
AND pu.site_use_type = 'BILL_TO'
AND pu.primary_per_type = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_bill_to_party_id := -1;
p_bill_to_site_id := -1;
p_bill_to_site_use_id := -1;
p_cust_acct_id := -1;
WHEN TOO_MANY_ROWS
THEN
p_bill_to_party_id := -2;
p_bill_to_site_id := -2;
p_bill_to_site_use_id := -2;
p_cust_acct_id := -2;
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| 'p_caller - '
|| p_caller
|| v_prog_unit
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
END get_bill_to_pt_site_use_id;
PROCEDURE get_bill_to_acct_site_use_id (
p_caller IN VARCHAR2,
p_legacy_acct_num IN VARCHAR2,
p_bill_to_party_id OUT NUMBER,
p_cust_acct_id OUT NUMBER,
p_bill_to_site_id OUT NUMBER,
p_bill_to_site_use_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_BILL_TO_ACCT_SITE_USE_ID';
v_party_id hz_parties.party_id%TYPE := NULL;
v_cust_acct_id hz_cust_accounts.cust_account_id%TYPE := NULL;
v_bill_to_site_id hz_party_sites.party_site_id%TYPE := NULL;
v_bill_to_site_use_id hz_party_site_uses.party_site_use_id%TYPE
:= NULL;
BEGIN
gv_debug_log_text := NULL;
SELECT aa.cust_account_id, ua.site_use_id, sa.party_site_id,
hp.party_id
INTO v_cust_acct_id, v_bill_to_site_id, v_bill_to_site_use_id,
v_party_id
FROM hz_cust_accounts_all aa,
hz_parties hp,
hz_cust_acct_sites_all sa,
hz_cust_site_uses_all ua
WHERE aa.account_number = p_legacy_acct_num
AND hp.party_id = aa.party_id
AND sa.cust_account_id = aa.cust_account_id
AND sa.cust_acct_site_id = ua.cust_acct_site_id
AND ua.site_use_code = 'BILL_TO'
AND ua.status = 'A'
AND ua.primary_flag = 'Y';
p_bill_to_party_id := v_party_id;
p_cust_acct_id := v_cust_acct_id;
p_bill_to_site_id := v_bill_to_site_id;
p_bill_to_site_use_id := v_bill_to_site_use_id;
EXCEPTION
WHEN OTHERS
THEN
p_bill_to_party_id := NULL;
p_bill_to_site_id := NULL;
p_bill_to_site_use_id := NULL;
p_cust_acct_id := NULL;
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_legacy_acct_num - '
|| p_legacy_acct_num
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
END get_bill_to_acct_site_use_id;
FUNCTION get_inc_problem_code (
p_caller IN VARCHAR2,
p_problem_meaning IN VARCHAR2
)
RETURN VARCHAR2
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_INC_PROBLEM_CODE';
v_problem_code cs_lookups.lookup_code%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT lookup_code
INTO v_problem_code
FROM cs_lookups
WHERE lookup_type = 'REQUEST_PROBLEM_CODE'
AND UPPER (meaning) = UPPER (p_problem_meaning)
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
SYSDATE
);
RETURN v_problem_code;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_problem_meaning - '
|| p_problem_meaning
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_problem_code;
FUNCTION get_inc_type_id (p_caller IN VARCHAR2, p_incident_type IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_INC_TYPE_ID';
v_incident_type_id cs_incidents_all_b.incident_type_id%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT incident_type_id
INTO v_incident_type_id
FROM cs_incident_types_vl
WHERE UPPER (NAME) = UPPER (p_incident_type)
AND incident_subtype = gc_inc_subtype
AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
AND NVL (end_date_active, SYSDATE);
RETURN v_incident_type_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_incident_type - '
|| p_incident_type
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_type_id;
FUNCTION get_inc_resolution_code (
p_caller IN VARCHAR2,
p_resolution_meaning IN VARCHAR2
)
RETURN VARCHAR2
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_INC_RESOLUTION_CODE';
v_resolution_code cs_lookups.lookup_code%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT lookup_code
INTO v_resolution_code
FROM cs_lookups
WHERE lookup_type = 'REQUEST_RESOLUTION_CODE'
AND UPPER (meaning) = UPPER (p_resolution_meaning)
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
SYSDATE
);
RETURN v_resolution_code;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_resolution_meaning - '
|| p_resolution_meaning
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_resolution_code;
FUNCTION get_inc_urgency_id (p_caller IN VARCHAR2, p_urgency IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_INC_URGENCY_ID';
v_incident_urgency_id cs_incident_urgencies_vl.incident_urgency_id%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT incident_urgency_id
INTO v_incident_urgency_id
FROM cs_incident_urgencies_vl
WHERE UPPER (NAME) = UPPER (p_urgency)
AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
SYSDATE
);
RETURN v_incident_urgency_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_urgency - '
|| p_urgency
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_urgency_id;
FUNCTION get_inc_severity_id (p_caller IN VARCHAR2, p_severity IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100)
:= gc_pkg || '.GET_INC_SEVERITY_ID';
v_incident_severity_id cs_incident_severities_vl.incident_severity_id%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT incident_severity_id
INTO v_incident_severity_id
FROM cs_incident_severities_vl
WHERE UPPER (NAME) = UPPER (p_severity)
AND incident_subtype = gc_inc_subtype;
RETURN v_incident_severity_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_severity - '
|| p_severity
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_severity_id;
FUNCTION get_inc_group_id (p_caller IN VARCHAR2, p_group_name IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_INC_GROUP_ID';
v_inc_group_id jtf_rs_groups_vl.GROUP_ID%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT GROUP_ID
INTO v_inc_group_id
FROM jtf_rs_groups_vl
WHERE UPPER (group_name) = UPPER (p_group_name)
AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
SYSDATE
);
RETURN v_inc_group_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_group_name - '
|| p_group_name
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_group_id;
FUNCTION get_inc_status_id (p_caller IN VARCHAR2, p_inc_status IN VARCHAR2)
RETURN NUMBER
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_INC_STATUS_ID';
v_inc_status_id jtf_rs_groups_vl.GROUP_ID%TYPE;
BEGIN
gv_debug_log_text := NULL;
SELECT incident_status_id
INTO v_inc_status_id
FROM cs_incident_statuses_vl
WHERE UPPER (NAME) = UPPER (p_inc_status)
AND incident_subtype = gc_inc_subtype;
RETURN v_inc_status_id;
EXCEPTION
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| ' p_inc_status - '
|| p_inc_status
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
RETURN NULL;
END get_inc_status_id;
PROCEDURE get_inc_location_info (
p_caller IN VARCHAR2,
p_legacy_acct_num IN VARCHAR2,
p_party_id OUT NUMBER,
p_party_name OUT VARCHAR2,
p_party_type OUT VARCHAR2,
p_account_id OUT NUMBER,
p_party_site_id OUT NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.GET_INC_LOCATION_INFO';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
SELECT hp.party_id, hp.party_name, ca.cust_account_id,
ps.party_site_id, hp.party_type
INTO p_party_id, p_party_name, p_account_id,
p_party_site_id, p_party_type
FROM hz_parties hp,
hz_cust_accounts_all ca,
hz_party_sites ps,
hz_party_site_uses pu
WHERE ca.account_number = p_legacy_acct_num
AND ca.party_id = hp.party_id
AND ps.party_id = ca.party_id
AND ps.identifying_address_flag = 'Y'
AND pu.party_site_id = ps.party_site_id
AND pu.site_use_type = 'BUSINESS';
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_party_id := NULL;
p_party_name := NULL;
p_party_type := NULL;
p_account_id := NULL;
p_party_site_id := NULL;
WHEN OTHERS
THEN
xxd_utils.LOG (p_log_text => gv_debug_log_text,
p_new_text => xxd_utils.gc_exception
|| ' p_caller - '
|| p_caller
|| 'Party_info '
|| ', error: '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.alert (p_to => xxd_email_addr.dl_doriis_tech,
p_prog_unit => v_prog_unit,
p_mesg => gv_debug_log_text
);
END get_inc_location_info;
END xxdc_p2_conv_util;
/
No comments:
Post a Comment