[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Table_Delete;
table_delete;
CSC_PROF_PARTY_SQL_CUHK will be invoked to get the select statement for fetching party
records by the profile engine.
*************************************************************************************/
l_custom_hook_enabled := 'N';
table_delete;
insert_stmnt VARCHAR2(4000);
insert_stmnt_sum VARCHAR2(4000);
insert_stmnt_final VARCHAR2(4000);
insert_stmnt_party VARCHAR2(4000);
insert_stmnt_acct VARCHAR2(4000);
select_clause VARCHAR2(2000);
v_select_clause VARCHAR2(2000);
/* variables for columns of insert statement */
c_fmt_mask VARCHAR2(1000);
v_select_pos number;
v_select_length number;
v_select_sum varchar2(2000) := 'SELECT hz.party_id, null, ';
SELECT block_id, sql_stmnt, batch_sql_stmnt, currency_code, select_clause
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
Select Range_Low_Value, Range_High_Value
From csc_prof_check_ratings
Where check_id = chk_id
and check_rating_grade = thd_grade;
SELECT rating_code, check_rating_grade, color_code, range_low_value, range_high_value
FROM csc_prof_check_ratings
WHERE check_id = chk_id;
SELECT count(*) INTO v_chk_count FROM csc_prof_check_results;
SELECT count(*) INTO v_batch_count FROM CSC_PROF_BATCH_RESULTS2_T;
INSERT /*+ PARALLEL (csc_prof_check_results, 12) */
INTO csc_prof_check_results
(check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
)
SELECT
check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
FROM CSC_PROF_BATCH_RESULTS2_T;
SELECT check_id, select_block_id, check_level,
data_type, format_mask, check_upper_lower_flag, threshold_grade
FROM csc_prof_checks_b
WHERE SYSDATE BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'B'
AND check_level IN ('EMPLOYEE');
SELECT check_id, select_block_id,check_level,
data_type, format_mask, check_upper_lower_flag, threshold_grade
FROM csc_prof_checks_b
WHERE SYSDATE BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'B'
AND check_level IN ('PARTY', 'ACCOUNT', 'CONTACT');
SELECT check_id, select_block_id,check_level,
data_type, format_mask, check_upper_lower_flag, threshold_grade
FROM csc_prof_checks_b
WHERE SYSDATE BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'B'
AND check_level IN ('PARTY', 'ACCOUNT');
Fetch block_csr INTO blk_id, sql_stmt, batch_sql_stmnt, curr_code, select_clause;
insert_stmnt := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T ' || batch_sql_stmnt;
EXECUTE IMMEDIATE (insert_stmnt);
v_select_pos := NULL;
v_select_length := NULL;
v_select_sum := 'SELECT hz.party_id, null, ';
v_select_clause := NULL;
v_select_clause := rtrim(ltrim(UPPER(select_clause)));
v_select_sum := v_select_sum || v_select_clause;
v_select_pos := instr(upper(batch_sql_stmnt), v_select_clause);
v_select_length := length(v_select_clause);
v_from_pos := v_select_pos + v_select_length;
v_select_sum := v_select_sum || ' ' || v_from_sum;
csc_core_utils_pvt.validate_sql_stmnt( p_sql_stmnt => v_select_sum,
x_return_status => return_status);
insert_stmnt_sum := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T ' || v_select_sum ;
EXECUTE IMMEDIATE (insert_stmnt_sum);
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT party_id, NULL, NULL
FROM hz_parties hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.party_id)
AND hz.status = 'A'
AND hz.party_type IN ('PERSON', 'ORGANIZATION');
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT party_id, NULL, NULL
FROM hz_parties hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.party_id)
AND hz.status = 'A'
AND hz.party_type = 'PARTY_RELATIONSHIP';
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT party_id, cust_account_id, NULL
FROM hz_cust_accounts hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.account_id = hz.cust_account_id)
AND hz.status = 'A';
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT person_id, NULL, NULL
FROM per_workforce_current_x hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.person_id) ;
SELECT COUNT(*) INTO v_count FROM csc_prof_check_ratings
WHERE check_id = chk_id;
rating_tbl.delete;
insert_stmnt_final := 'INSERT /*+ PARALLEL(CSC_PROF_BATCH_RESULTS2_T, 12) */ INTO CSC_PROF_BATCH_RESULTS2_T ' ||
'(check_results_id, check_id, party_id, cust_account_id, value, currency_code, grade, '||
' created_by, creation_date, last_updated_by, last_update_date, last_update_login, '||
' results_threshold_flag, rating_code, color_code)' ||
' SELECT csc_prof_check_results_s.nextval, '
|| chk_id || ', party_id, account_id, '
|| c_fmt_mask ||', '
|| c_curr_code || ', '
|| c_grade
|| ', FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.CONC_LOGIN_ID, '
|| c_threshold ||', '
|| c_rating || ', '
|| c_color
||' FROM CSC_PROF_BATCH_RESULTS1_T where nvl(account_id, -999999) <> -999999';
insert_stmnt_final := 'INSERT /*+ PARALLEL(CSC_PROF_BATCH_RESULTS2_T, 12) */ INTO CSC_PROF_BATCH_RESULTS2_T ' ||
'(check_results_id, check_id, party_id, cust_account_id, value, currency_code, grade, '||
' created_by, creation_date, last_updated_by, last_update_date, last_update_login, '||
' results_threshold_flag, rating_code, color_code)' ||
' SELECT csc_prof_check_results_s.nextval, '
|| chk_id || ', party_id, account_id, '
|| c_fmt_mask ||', '
|| c_curr_code || ', '
|| c_grade
|| ', FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.CONC_LOGIN_ID, '
|| c_threshold ||', '
|| c_rating || ', '
|| c_color
||' FROM CSC_PROF_BATCH_RESULTS1_T where account_id IS NULL';
EXECUTE IMMEDIATE (insert_stmnt_final);
INSERT /*+ PARALLEL (CSC_PROF_BATCH_RESULTS2_T, 12) */
INTO CSC_PROF_BATCH_RESULTS2_T
(check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
)
SELECT
check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
FROM csc_prof_check_results a
WHERE NOT EXISTS (SELECT null FROM csc_prof_checks_b b
WHERE a.check_id = b.check_id
AND b.select_type = 'B');
INSERT /*+ PARALLEL (csc_prof_check_results, 12) */
INTO csc_prof_check_results
(check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
)
SELECT
check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
FROM CSC_PROF_BATCH_RESULTS2_T;
l_for_insert varchar2(1) := 'Y';
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,
threshold_grade, check_level
FROM csc_prof_checks_b a
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks)
AND select_type = 'B'
AND check_level IN ('PARTY', 'ACCOUNT')
AND EXISTS (SELECT null
FROM csc_prof_blocks_b b
WHERE a.select_block_id = b.block_id
AND b.batch_sql_stmnt IS NULL)
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE);
SELECT party_id
FROM hz_parties
WHERE status = 'A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT check_id, select_type, select_block_id,check_level,
data_type, format_mask,check_upper_lower_flag,threshold_grade
FROM csc_prof_checks_b
WHERE Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND check_level IN ('PARTY', 'ACCOUNT')
AND select_type = 'T'
ORDER BY check_id;
SELECT COUNT(*)
FROM csc_prof_checks_b
WHERE Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'T';
SELECT party_id
FROM hz_parties
WHERE status = 'A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_ua_total, l_for_insert, l_for_party);
p_select_type IN VARCHAR2,
p_select_block_id IN NUMBER,
p_data_type IN VARCHAR2,
p_format_mask IN VARCHAR2,
p_chk_upp_low_flag IN VARCHAR2,
p_threshold_grade IN VARCHAR2,
p_check_level IN VARCHAR2,
p_group_id IN NUMBER
)
IS
chk_id Number;
l_for_insert varchar2(1) := 'Y';
SELECT distinct block_id
FROM csc_prof_check_rules_b
WHERE check_id = chk_id
UNION
(SELECT distinct expr_to_block_id
FROM csc_prof_check_rules_b
WHERE check_id = chk_id);
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = p_select_block_id;
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
sel_type := p_select_type;
sel_blk_id := p_select_block_id;
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total, l_for_insert, l_for_party);
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag, threshold_grade, check_level
FROM csc_prof_checks_b chk
WHERE check_level IN ('PARTY','ACCOUNT')
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND EXISTS ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE a.group_id = p_group_id
AND chk.check_id = a.check_id);
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag, threshold_grade, check_level
FROM csc_prof_checks_b chk
WHERE check_level IN ('PARTY','ACCOUNT')
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND EXISTS ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE a.group_id = p_group_id
AND chk.check_id = a.check_id
AND a.critical_flag = 'Y');
SELECT 1
FROM hz_parties
WHERE party_id = c_party_id
AND party_type = 'PARTY_RELATIONSHIP'
AND status = 'A';
SELECT 1
FROM per_workforce_current_x
WHERE person_id = c_party_id;
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag, threshold_grade, check_level
FROM csc_prof_checks_b chk
WHERE check_level = 'CONTACT'
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND EXISTS ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE a.group_id = p_group_id
AND chk.check_id = a.check_id);
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag, threshold_grade, check_level
FROM csc_prof_checks_b chk
WHERE check_level = 'CONTACT'
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND EXISTS ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE a.group_id = p_group_id
AND chk.check_id = a.check_id
AND a.critical_flag = 'Y');
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag, threshold_grade, check_level
FROM csc_prof_checks_b chk
WHERE check_level = 'EMPLOYEE'
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND EXISTS ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE a.group_id = p_group_id
AND chk.check_id = a.check_id);
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag, threshold_grade, check_level
FROM csc_prof_checks_b chk
WHERE check_level = 'EMPLOYEE'
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND EXISTS ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE a.group_id = p_group_id
AND chk.check_id = a.check_id
AND a.critical_flag = 'Y');
p_select_type => i.select_type,
p_select_block_id => i.select_block_id,
p_data_type => i.data_type,
p_format_mask => i.format_mask,
p_chk_upp_low_flag => i.check_upper_lower_flag,
p_threshold_grade => i.threshold_grade,
p_check_level => i.check_level,
p_group_id => p_group_id
);
p_select_type => i.select_type,
p_select_block_id => i.select_block_id,
p_data_type => i.data_type,
p_format_mask => i.format_mask,
p_chk_upp_low_flag => i.check_upper_lower_flag,
p_threshold_grade => i.threshold_grade,
p_check_level => i.check_level,
p_group_id => p_group_id
);
p_select_type => i.select_type,
p_select_block_id => i.select_block_id,
p_data_type => i.data_type,
p_format_mask => i.format_mask,
p_chk_upp_low_flag => i.check_upper_lower_flag,
p_threshold_grade => i.threshold_grade,
p_check_level => i.check_level,
p_group_id => p_group_id
);
p_select_type => i.select_type,
p_select_block_id => i.select_block_id,
p_data_type => i.data_type,
p_format_mask => i.format_mask,
p_chk_upp_low_flag => i.check_upper_lower_flag,
p_threshold_grade => i.threshold_grade,
p_check_level => i.check_level,
p_group_id => p_group_id
);
p_select_type => i.select_type,
p_select_block_id => i.select_block_id,
p_data_type => i.data_type,
p_format_mask => i.format_mask,
p_chk_upp_low_flag => i.check_upper_lower_flag,
p_threshold_grade => i.threshold_grade,
p_check_level => i.check_level,
p_group_id => p_group_id
);
p_select_type => i.select_type,
p_select_block_id => i.select_block_id,
p_data_type => i.data_type,
p_format_mask => i.format_mask,
p_chk_upp_low_flag => i.check_upper_lower_flag,
p_threshold_grade => i.threshold_grade,
p_check_level => i.check_level,
p_group_id => p_group_id
);
l_for_insert varchar2(1) := 'Y';
SELECT check_id, select_type, select_block_id, data_type,
format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b chk
WHERE check_level IN ('PARTY','ACCOUNT')
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND Exists ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE chk.check_id = a.check_id
AND sysdate between
nvl(a.start_date_active,sysdate)
AND nvl(a.end_date_active,sysdate));
SELECT check_id, select_type, select_block_id, data_type,
format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b chk
WHERE check_level IN ('EMPLOYEE')
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND Exists ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE chk.check_id = a.check_id
AND sysdate between
nvl(a.start_date_active,sysdate)
AND nvl(a.end_date_active,sysdate));
Select distinct block_id
From csc_prof_check_rules_b
Where check_id = chk_id
UNION
(Select distinct expr_to_block_id
From csc_prof_check_rules_b
Where check_id = chk_id);
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
SELECT check_id, select_type, select_block_id, data_type, format_mask,
check_upper_lower_flag,threshold_grade,check_level
FROM csc_prof_checks_b chk
WHERE check_level = 'CONTACT'
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND nvl(end_date_active, Sysdate))
AND Exists ( SELECT a.check_id
FROM csc_prof_group_checks a
WHERE chk.check_id = a.check_id
AND sysdate between nvl(a.start_date_active,sysdate)
AND nvl(a.end_date_active,sysdate));
SELECT party_id FROM hz_parties
WHERE party_id = c_party_id
AND party_type = 'PARTY_RELATIONSHIP'
AND status = 'A';
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_ua_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
insert_stmnt VARCHAR2(4000);
insert_stmnt_sum VARCHAR2(4000);
insert_stmnt_final VARCHAR2(4000);
insert_stmnt_party VARCHAR2(4000);
insert_stmnt_acct VARCHAR2(4000);
select_clause VARCHAR2(2000);
v_select_clause VARCHAR2(2000);
/* variables for columns of insert statement */
c_fmt_mask VARCHAR2(1000);
v_select_pos number;
v_select_length number;
v_select_sum varchar2(2000) := 'SELECT hz.party_id, null, ';
SELECT block_id, sql_stmnt, UPPER(batch_sql_stmnt), currency_code, select_clause
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
Select Range_Low_Value, Range_High_Value
From csc_prof_check_ratings
Where check_id = chk_id
and check_rating_grade = thd_grade;
SELECT rating_code, check_rating_grade, color_code, range_low_value, range_high_value
FROM csc_prof_check_ratings
WHERE check_id = chk_id;
SELECT count(*) INTO v_chk_count FROM csc_prof_check_results;
SELECT count(*) INTO v_batch_count FROM CSC_PROF_BATCH_RESULTS2_T;
INSERT /*+ PARALLEL (csc_prof_check_results, 12) */
INTO csc_prof_check_results
(check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
)
SELECT
check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
FROM CSC_PROF_BATCH_RESULTS2_T;
SELECT check_id, select_block_id, check_level,
data_type, format_mask, check_upper_lower_flag, threshold_grade
FROM csc_prof_checks_b
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id)
AND SYSDATE BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'B'
AND check_level IN ('EMPLOYEE');
SELECT check_id, select_block_id,check_level,
data_type, format_mask, check_upper_lower_flag, threshold_grade
FROM csc_prof_checks_b
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id)
AND SYSDATE BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'B'
AND check_level IN ('PARTY', 'ACCOUNT', 'CONTACT');
SELECT check_id, select_block_id,check_level,
data_type, format_mask, check_upper_lower_flag, threshold_grade
FROM csc_prof_checks_b
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id)
AND SYSDATE BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'B'
AND check_level IN ('PARTY', 'ACCOUNT');
FETCH block_csr INTO blk_id, sql_stmt, batch_sql_stmnt, curr_code, select_clause;
insert_stmnt := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T ' || batch_sql_stmnt;
EXECUTE IMMEDIATE (insert_stmnt);
v_select_pos := NULL;
v_select_length := NULL;
v_select_sum := 'SELECT hz.party_id, null, ';
v_select_clause := NULL;
v_select_clause := rtrim(ltrim(UPPER(select_clause)));
v_select_sum := v_select_sum || v_select_clause;
v_select_pos := instr(upper(batch_sql_stmnt), v_select_clause);
v_select_length := length(v_select_clause);
v_from_pos := v_select_pos + v_select_length;
v_select_sum := v_select_sum || ' ' || v_from_sum;
csc_core_utils_pvt.validate_sql_stmnt( p_sql_stmnt => v_select_sum,
x_return_status => return_status);
insert_stmnt_sum := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T ' || v_select_sum ;
EXECUTE IMMEDIATE (insert_stmnt_sum);
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT party_id, NULL, NULL
FROM hz_parties hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.party_id)
AND hz.status = 'A'
AND hz.party_type IN ('PERSON', 'ORGANIZATION');
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT party_id, NULL, NULL
FROM hz_parties hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.party_id)
AND hz.status = 'A'
AND hz.party_type = 'PARTY_RELATIONSHIP';
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT party_id, cust_account_id, NULL
FROM hz_cust_accounts hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.account_id = hz.cust_account_id)
AND hz.status = 'A';
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,VALUE)
SELECT person_id, NULL, NULL
FROM per_workforce_current_x hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.person_id) ;
SELECT COUNT(*) INTO v_count FROM csc_prof_check_ratings
WHERE check_id = chk_id;
rating_tbl.delete;
insert_stmnt_final := 'INSERT /*+ PARALLEL(CSC_PROF_BATCH_RESULTS2_T, 12) */ INTO CSC_PROF_BATCH_RESULTS2_T ' ||
'(check_results_id, check_id, party_id, cust_account_id, value, currency_code, grade, '||
' created_by, creation_date, last_updated_by, last_update_date, last_update_login, '||
' results_threshold_flag, rating_code, color_code)' ||
' SELECT csc_prof_check_results_s.nextval, '
|| chk_id || ', party_id, account_id, '
|| c_fmt_mask ||', '
|| c_curr_code || ', '
|| c_grade
|| ', FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.CONC_LOGIN_ID, '
|| c_threshold ||', '
|| c_rating || ', '
|| c_color
||' FROM CSC_PROF_BATCH_RESULTS1_T where nvl(account_id, -999999) <> -999999';
insert_stmnt_final := 'INSERT /*+ PARALLEL(CSC_PROF_BATCH_RESULTS2_T, 12) */ INTO CSC_PROF_BATCH_RESULTS2_T ' ||
'(check_results_id, check_id, party_id, cust_account_id, value, currency_code, grade, '||
' created_by, creation_date, last_updated_by, last_update_date, last_update_login, '||
' results_threshold_flag, rating_code, color_code)' ||
' SELECT csc_prof_check_results_s.nextval, '
|| chk_id || ', party_id, account_id, '
|| c_fmt_mask ||', '
|| c_curr_code || ', '
|| c_grade
|| ', FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.USER_ID, Sysdate, FND_GLOBAL.CONC_LOGIN_ID, '
|| c_threshold ||', '
|| c_rating || ', '
|| c_color
||' FROM CSC_PROF_BATCH_RESULTS1_T where account_id IS NULL';
EXECUTE IMMEDIATE (insert_stmnt_final);
INSERT /*+ PARALLEL (CSC_PROF_BATCH_RESULTS2_T, 12) */
INTO CSC_PROF_BATCH_RESULTS2_T
(check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
)
SELECT
check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
FROM csc_prof_check_results
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
MINUS
SELECT check_id FROM csc_prof_group_checks a
WHERE group_id = p_group_id
AND EXISTS (SELECT 1 FROM csc_prof_checks_b b
WHERE a.check_id = b.check_id AND b.select_type = 'B'));
INSERT /*+ PARALLEL (csc_prof_check_results, 12) */
INTO csc_prof_check_results
(check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
)
SELECT
check_results_id, check_id, party_id, cust_account_id,
value, currency_code, grade, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
results_threshold_flag, rating_code, color_code
FROM CSC_PROF_BATCH_RESULTS2_T;
l_for_insert varchar2(1) := 'Y';
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,
threshold_grade, check_level
FROM csc_prof_checks_b a
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id)
AND select_type = 'B'
AND check_level IN ('PARTY','ACCOUNT')
AND EXISTS (SELECT null
FROM csc_prof_blocks_b b
WHERE a.select_block_id = b.block_id
AND b.batch_sql_stmnt IS NULL)
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE);
SELECT party_id
FROM hz_parties
WHERE status = 'A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_id in (select check_id from csc_prof_group_checks
where group_id = p_group_id)
AND check_level IN ('PARTY','ACCOUNT')
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'T';
SELECT COUNT(*)
FROM csc_prof_checks_b
WHERE check_id in (select check_id from csc_prof_group_checks
where group_id = p_group_id)
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND select_type = 'T';
SELECT party_id
FROM hz_parties
WHERE status = 'A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
Select check_rating_grade,rating_code,color_code
From csc_prof_check_ratings
Where check_id = p_chk_id
And ( nvl(Range_Low_Value,val) <= v_val AND
nvl(Range_High_Value,val) >= v_val );
Select check_rating_grade,rating_code,color_code
From csc_prof_check_ratings
Where check_id = p_chk_id
And ( nvl(Range_Low_Value,val) <= val AND
nvl(Range_High_Value,val) >= val );
Select Range_Low_Value,Range_High_Value
From csc_prof_check_ratings
Where check_id = p_chk_id
and check_rating_grade=p_thd_grade;
Select count(*)
From csc_prof_check_results
Where check_id = p_chk_id
And party_id = p_party_id
And cust_account_id IS NULL;
Select count(*)
From csc_prof_check_results
Where check_id = p_chk_id
And party_id = p_party_id
And cust_account_id = p_acct_id;
select 1 into v_party_count
from csc_prof_check_results
where check_id = p_chk_id
and party_id = p_party_id
and cust_account_id is null;
-- assign values to insert party check results
ip_check_id(p_ip_total) := p_chk_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(p_ip_total, l_for_insert, l_for_party);
-- assign values to update party check results
up_check_id(p_up_total) := p_chk_id;
l_for_insert := 'N';
Insert_Update_Check_Results
(p_up_total, l_for_insert, l_for_party);
select 1 into v_account_count from csc_prof_check_results
where check_id = p_chk_id
and party_id = p_party_id
and CUST_ACCOUNT_ID =p_acct_id; -- Bug 5255227 Fix
-- assign values to insert
ia_check_id(p_ia_total) := p_chk_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(p_ia_total, l_for_insert, l_for_party);
-- assign values to update party tables
ua_check_id(p_ua_total) := p_chk_id;
l_for_insert := 'N';
Insert_Update_Check_Results
(p_ua_total, l_for_insert, l_for_party);
--table_delete;
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE exists ( Select b.block_id
From csc_prof_check_rules_b b
where b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level IN ('PARTY','ACCOUNT');
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
table_delete;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT null
FROM csc_prof_checks_b b
WHERE b.select_block_id = a.block_id
AND b.select_type = 'B'
AND check_id IN (SELECT check_id FROM csc_prof_group_checks))
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE)
AND batch_sql_stmnt IS NULL;
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
table_delete;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b
WHERE block_id = p_block_id
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate));
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
-- check if there are still records to be inserted
IF p_ip_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
-- check if there are still records to be updated
IF p_up_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
-- check if there are still records to be inserted
Exception
When others then
IF (dbms_sql.is_open(cid)) THEN
dbms_sql.close_cursor(cid);
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ia_total, l_for_insert, l_for_party);
-- check if there are still records to be updated
IF p_ua_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Block_Results
(p_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE exists ( Select b.block_id
From csc_prof_check_rules_b b
where (b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
And check_id in (Select check_id from csc_prof_group_checks
where group_id = p_group_id))
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level IN ('PARTY','ACCOUNT');
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
table_delete;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT null
FROM csc_prof_checks_b b
WHERE b.select_block_id = a.block_id
AND b.select_type = 'B'
AND check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id))
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE)
AND batch_sql_stmnt IS NULL;
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
table_delete;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ia_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_ua_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b
WHERE block_id = c_block_id
AND block_level in( 'CONTACT','EMPLOYEE')
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate));
-- check if there are still records to be inserted
IF p_ip_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
-- check if there are still records to be updated
IF p_up_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE = 'PARTY_RELATIONSHIP';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT null
FROM csc_prof_checks_b b
WHERE b.select_block_id = a.block_id
AND b.select_type = 'B'
AND check_id IN (SELECT check_id FROM csc_prof_group_checks))
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE)
AND batch_sql_stmnt IS NULL
AND block_level = 'CONTACT';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE exists ( SELECT b.block_id
FROM csc_prof_check_rules_b b, csc_prof_checks_b c
WHERE (b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND c.select_type = 'T'
AND b.check_id = c.check_id)
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level ='CONTACT';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE exists ( Select b.block_id
From csc_prof_check_rules_b b
where b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level ='CONTACT';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT null
FROM csc_prof_checks_b b
WHERE b.select_block_id = a.block_id
AND b.select_type = 'B'
AND check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id))
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE)
AND batch_sql_stmnt IS NULL
AND block_level = 'CONTACT';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT b.block_id
FROM csc_prof_check_rules_b b, csc_prof_checks_b c
WHERE (b.block_id = a.block_id
OR b.expr_to_block_id = a.block_id)
AND c.select_type = 'T'
AND b.check_id = c.check_id
AND b.check_id IN (SELECT check_id from csc_prof_group_checks
WHERE group_id = p_group_id))
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level = 'CONTACT';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT b.block_id
FROM csc_prof_check_rules_b b
WHERE (b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND check_id IN (Select check_id from csc_prof_group_checks
where group_id = p_group_id))
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level ='CONTACT';
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
table_delete;
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
SELECT party_id
FROM hz_parties
WHERE status='A'
AND party_type = 'PARTY_RELATIONSHIP';
SELECT check_id, select_type, select_block_id,
data_type, format_mask, check_upper_lower_flag,
threshold_grade, check_level
FROM csc_prof_checks_b a
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks)
AND select_type = 'B'
AND check_level = 'CONTACT'
AND EXISTS (SELECT null
FROM csc_prof_blocks_b b
WHERE a.select_block_id = b.block_id
AND b.batch_sql_stmnt IS NULL)
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level ='CONTACT'
AND select_type = 'T'
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level ='CONTACT'
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,
threshold_grade, check_level
FROM csc_prof_checks_b a
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id)
AND select_type = 'B'
AND check_level = 'CONTACT'
AND EXISTS (SELECT null
FROM csc_prof_blocks_b b
WHERE a.select_block_id = b.block_id
AND b.batch_sql_stmnt IS NULL)
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level = 'CONTACT'
AND select_type = 'T'
AND check_id in (select check_id from csc_prof_group_checks
where group_id = p_group_id)
And Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level = 'CONTACT'
AND check_id in (select check_id from csc_prof_group_checks
where group_id = p_group_id)
And Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
sel_type := v_rec_var.select_type;
sel_blk_id := v_rec_var.select_block_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
SELECT person_id
FROM per_workforce_current_x;
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT null
FROM csc_prof_checks_b b
WHERE b.select_block_id = a.block_id
AND b.select_type = 'B'
AND check_id IN (SELECT check_id FROM csc_prof_group_checks))
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE)
AND batch_sql_stmnt IS NULL
AND block_level = 'EMPLOYEE';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE exists ( SELECT b.block_id
FROM csc_prof_check_rules_b b, csc_prof_checks_b c
WHERE (b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND c.select_type = 'T'
AND b.check_id = c.check_id)
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level ='EMPLOYEE';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE exists ( Select b.block_id
From csc_prof_check_rules_b b
where b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level ='EMPLOYEE';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT null
FROM csc_prof_checks_b b
WHERE b.select_block_id = a.block_id
AND b.select_type = 'B'
AND check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id))
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE)
AND batch_sql_stmnt IS NULL
AND block_level = 'EMPLOYEE';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT b.block_id
FROM csc_prof_check_rules_b b, csc_prof_checks_b c
WHERE (b.block_id = a.block_id
OR b.expr_to_block_id = a.block_id)
AND c.select_type = 'T'
AND b.check_id = c.check_id
AND b.check_id IN (SELECT check_id from csc_prof_group_checks
WHERE group_id = p_group_id))
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level = 'EMPLOYEE';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE EXISTS ( SELECT b.block_id
FROM csc_prof_check_rules_b b
WHERE (b.block_id = a.block_id
or b.expr_to_block_id = a.block_id)
AND check_id IN (Select check_id from csc_prof_group_checks
where group_id = p_group_id))
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate)
AND block_level ='EMPLOYEE';
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
table_delete;
l_for_insert varchar2(1) := 'Y';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
SELECT person_id
FROM per_workforce_current_x;
SELECT check_id, select_type, select_block_id,
data_type, format_mask, check_upper_lower_flag,
threshold_grade, check_level
FROM csc_prof_checks_b a
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks)
AND select_type = 'B'
AND check_level = 'EMPLOYEE'
AND EXISTS (SELECT null
FROM csc_prof_blocks_b b
WHERE a.select_block_id = b.block_id
AND b.batch_sql_stmnt IS NULL)
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level ='EMPLOYEE'
AND select_type = 'T'
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level ='EMPLOYEE'
AND Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,
threshold_grade, check_level
FROM csc_prof_checks_b a
WHERE check_id IN (SELECT check_id FROM csc_prof_group_checks
WHERE group_id = p_group_id)
AND select_type = 'B'
AND check_level = 'EMPLOYEE'
AND EXISTS (SELECT null
FROM csc_prof_blocks_b b
WHERE a.select_block_id = b.block_id
AND b.batch_sql_stmnt IS NULL)
AND SYSDATE BETWEEN Nvl(start_date_active, SYSDATE)
AND Nvl(end_date_active, SYSDATE);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level = 'EMPLOYEE'
AND select_type = 'T'
AND check_id in (select check_id from csc_prof_group_checks
where group_id = p_group_id)
And Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
SELECT check_id, select_type, select_block_id,
data_type, format_mask,check_upper_lower_flag,threshold_grade,
check_level
FROM csc_prof_checks_b
WHERE check_level = 'EMPLOYEE'
AND check_id in (select check_id from csc_prof_group_checks
where group_id = p_group_id)
And Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate);
sel_type := v_rec_var.select_type;
sel_blk_id := v_rec_var.select_block_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(l_ip_total, l_for_insert, l_for_party);
l_for_insert := 'N';
Insert_Update_Check_Results
(l_up_total, l_for_insert, l_for_party);
l_for_insert varchar2(1) := 'Y';
Select count(*)
From csc_prof_block_results
Where block_id = p_blk_id
And party_id = p_party_id
And cust_account_id IS NULL;
Select count(*)
From csc_prof_block_results
Where block_id = p_blk_id
And party_id = p_party_id
And cust_account_id = p_acct_id;
select 1 into v_party_count from csc_prof_block_results
where block_id = p_blk_id
and party_id = p_party_id
and cust_account_id is null;
-- assign values to insert party
ip_block_id(p_ip_total) := p_blk_id;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ip_total, l_for_insert, l_for_party);
-- assign values to update party tables
up_block_id(p_up_total) := p_blk_id;
l_for_insert := 'N';
Insert_Update_Block_Results
(p_up_total, l_for_insert, l_for_party);
select 1 into v_account_count from csc_prof_block_results
where block_id = p_blk_id
and party_id = p_party_id
and cust_account_id = p_acct_id;
-- assign values to insert
ia_block_id(p_ia_total) := p_blk_id;
l_for_insert := 'Y';
Insert_Update_Block_Results
(p_ia_total, l_for_insert, l_for_party);
-- assign values to update party tables
ua_block_id(p_ua_total) := p_blk_id;
l_for_insert := 'N';
Insert_Update_Block_Results
(p_ua_total, l_for_insert, l_for_party);
PROCEDURE Insert_Update_Block_Results
( p_count IN Number,
p_for_insert IN Varchar2,
p_for_party IN Varchar2) IS
TABLESEGMENT_FULL EXCEPTION;
-- PARTY level insert or update of records
IF p_for_insert = 'Y' THEN
-- Insert Records into Csc_Prof_Block_Results Table
FORALL i IN 1..p_count
INSERT INTO Csc_Prof_Block_Results
( block_results_id
, block_id
, party_id
, cust_account_id
, value
, currency_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login)
VALUES
(csc_prof_block_results_s.nextval
, ip_block_id(i)
, ip_party_id(i)
, ip_account_id(i)
, ip_value(i)
, ip_currency(i)
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.CONC_LOGIN_ID );
ip_block_id.DELETE;
ip_party_id.DELETE;
ip_account_id.DELETE;
ip_value.DELETE;
ip_currency.DELETE;
UPDATE Csc_Prof_Block_Results
Set value = up_value(i)
, currency_code = up_currency(i)
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = sysdate
, last_update_login = FND_GLOBAL.CONC_LOGIN_ID
Where block_id = up_block_id(i)
And party_id = up_party_id(i);
up_block_id.DELETE;
up_party_id.DELETE;
up_account_id.DELETE;
up_value.DELETE;
up_currency.DELETE;
-- ACCOUNT level insert or update of records
IF p_for_insert = 'Y' THEN
-- Insert Records into Csc_Prof_Block_Results Table
FORALL i IN 1..p_count
INSERT INTO Csc_Prof_Block_Results
( block_results_id
, block_id
, party_id
, cust_account_id
, value
, currency_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login)
VALUES
(csc_prof_block_results_s.nextval
, ia_block_id(i)
, ia_party_id(i)
, ia_account_id(i)
, ia_value(i)
, ia_currency(i)
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.CONC_LOGIN_ID );
ia_block_id.DELETE;
ia_party_id.DELETE;
ia_account_id.DELETE;
ia_value.DELETE;
ia_currency.DELETE;
UPDATE Csc_Prof_Block_Results
Set value = ua_value(i)
, currency_code = ua_currency(i)
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = sysdate
, last_update_login = FND_GLOBAL.CONC_LOGIN_ID
Where block_id = ua_block_id(i)
And party_id = ua_party_id(i)
And cust_account_id = ua_account_id(i);
ua_block_id.DELETE;
ua_party_id.DELETE;
ua_account_id.DELETE;
ua_value.DELETE;
ua_currency.DELETE;
table_delete;
END Insert_Update_Block_Results;
PROCEDURE Insert_Update_Check_Results
( p_count IN Number,
p_for_insert IN Varchar2,
p_for_party IN Varchar2) IS
TABLESEGMENT_FULL EXCEPTION;
-- PARTY level insert or update of records
IF p_for_insert = 'Y' THEN
-- Insert Records into Csc_Prof_Check_Results Table
FORALL i IN 1..p_count
INSERT INTO Csc_Prof_Check_Results
( check_results_id
, check_id
, party_id
, cust_account_id
, value
, results_threshold_flag
, currency_code
, grade
, rating_code
, color_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login)
VALUES
(csc_prof_check_results_s.nextval
, ip_check_id(i)
, ip_party_id(i)
, ip_account_id(i)
, ip_value(i)
, ip_results(i)
, ip_currency(i)
, ip_grade(i)
, ip_rating_code(i)
, ip_color_code(i)
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.CONC_LOGIN_ID );
ip_check_id.DELETE;
ip_party_id.DELETE;
ip_account_id.DELETE;
ip_value.DELETE;
ip_currency.DELETE;
ip_grade.DELETE;
ip_rating_code.DELETE;
ip_color_code.DELETE;
ip_results.DELETE;
UPDATE Csc_Prof_Check_Results
Set value = up_value(i)
, results_threshold_flag = up_results(i)
, currency_code = up_currency(i)
, grade = up_grade(i)
, rating_code = up_rating_code(i)
, color_code = up_color_code(i)
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = sysdate
, last_update_login = FND_GLOBAL.CONC_LOGIN_ID
Where check_id = up_check_id(i)
And party_id = up_party_id(i)
And cust_account_id IS NULL;
up_check_id.DELETE;
up_party_id.DELETE;
up_account_id.DELETE;
up_value.DELETE;
up_currency.DELETE;
up_grade.DELETE;
up_rating_code.DELETE;
up_color_code.DELETE;
up_results.DELETE;
-- ACCOUNT level insert or update of records
IF p_for_insert = 'Y' THEN
-- Insert Records into Csc_Prof_Check_Results Table
FORALL i IN 1..p_count
INSERT INTO Csc_Prof_Check_Results
( check_results_id
, check_id
, party_id
, cust_account_id
, value
, results_threshold_flag
, currency_code
, grade
, rating_code
, color_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login)
VALUES
(csc_prof_check_results_s.nextval
, ia_check_id(i)
, ia_party_id(i)
, ia_account_id(i)
, ia_value(i)
, ia_results(i)
, ia_currency(i)
, ia_grade(i)
, ia_rating_code(i)
, ia_color_code(i)
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.CONC_LOGIN_ID );
ia_check_id.DELETE;
ia_party_id.DELETE;
ia_account_id.DELETE;
ia_value.DELETE;
ia_currency.DELETE;
ia_grade.DELETE;
ia_rating_code.DELETE;
ia_color_code.DELETE;
ia_results.DELETE;
UPDATE Csc_Prof_Check_Results
Set value = ua_value(i)
, results_threshold_flag = ua_results(i)
, currency_code = ua_currency(i)
, grade = ua_grade(i)
, rating_code = ua_rating_code(i)
, color_code = ua_color_code(i)
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = sysdate
, last_update_login = FND_GLOBAL.CONC_LOGIN_ID
Where check_id = ua_check_id(i)
And party_id = ua_party_id(i)
And cust_account_id = ua_account_id(i);
ua_check_id.DELETE;
ua_party_id.DELETE;
ua_account_id.DELETE;
ua_value.DELETE;
ua_currency.DELETE;
ua_grade.DELETE;
ua_rating_code.DELETE;
ua_color_code.DELETE;
ua_results.DELETE;
table_delete;
END Insert_Update_Check_Results;
UPDATE_JIT_STATUS
Added this procedure for JIT enhancement -- Bug 4535407
Updates the columns jit_status, jit_err_code in CCT_MEDIA_ITEMS.
Called from CSC_PROF_JIT
********************************************/
PROCEDURE update_jit_status
( p_status VARCHAR2 DEFAULT NULL,
p_err_code NUMBER DEFAULT NULL,
p_media_item_id NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE CCT_MEDIA_ITEMS
SET jit_status = p_status,
jit_err_code = p_err_code
WHERE media_item_id = p_media_item_id;
END update_jit_status;
SELECT jit_status
FROM CCT_MEDIA_ITEMS
WHERE media_item_id = l_media_item_id;
SELECT a.dashboard_group_id
FROM csc_prof_module_groups a, csc_prof_groups_b b
WHERE a.dashboard_group_id = b.group_id
AND a.party_type IN (l_party_type, 'ALL')
AND a.form_function_name = l_form_name
AND (((a.responsibility_id = l_resp_id AND a.resp_appl_id = l_resp_appl_id)
AND a.responsibility_id IS NOT NULL)
OR a.responsibility_id IS NULL)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(b.start_date_active, SYSDATE))
AND TRUNC(NVL(b.end_date_active, SYSDATE))
ORDER BY a.party_type desc, a.responsibility_id;
SELECT party_type
FROM hz_parties
WHERE party_id = l_party_id;
SELECT cust_account_id
FROM hz_cust_accounts
WHERE party_id = l_party_id
AND ((status = 'A' AND l_show_active_acct = 'Y')
OR (l_show_active_acct = 'N'))
AND status NOT IN ('M', 'D')
ORDER BY creation_date DESC;
SELECT cust_account_id
FROM hz_cust_accounts
WHERE party_id = l_party_id
AND ((status = 'A' AND l_show_active_acct = 'Y')
OR (l_show_active_acct = 'N'))
AND status NOT IN ('M', 'D')
ORDER BY creation_date;
SELECT obj_party_id, obj_party_type
FROM csc_hz_parties_rel_v
WHERE rel_party_id = l_party_id;
/* OTM updates jit_status to null before calling this procedure.
IF screenpop happens when the call to this procedure is in queue, then
forms would have populated jit_status column. In such cases return back
*/
IF l_status IS NOT NULL OR chk_status%NOTFOUND THEN
RETURN;
update_jit_status
( p_status => l_jit_prg_status,
p_err_code => NULL,
p_media_item_id => l_media_item_id );
SELECT cust_account_id
INTO l_acct_id
FROM hz_cust_accounts
WHERE party_id = l_party_id
AND ((status = 'A' AND l_show_active_acct = 'Y') OR (l_show_active_acct = 'N'))
AND status NOT IN ('M', 'D');
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
SELECT cust_account_id
INTO l_acct_id
FROM hz_cust_accounts
WHERE party_id = l_party_id
AND ((status = 'A' AND l_show_active_acct = 'Y') OR (l_show_active_acct = 'N'))
AND status NOT IN ('M', 'D');
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
SELECT cust_account_id
INTO l_acct_id
FROM hz_cust_accounts
WHERE party_id = l_party_id
AND ((status = 'A' AND l_show_active_acct = 'Y') OR (l_show_active_acct = 'N'))
AND status NOT IN ('M', 'D');
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
update_jit_status
( p_status => l_jit_err_status,
p_err_code => -20001,
p_media_item_id => l_media_item_id );
update_jit_status
( p_status => l_jit_complete_status,
p_err_code => NULL,
p_media_item_id => l_media_item_id );
update_jit_status
( p_status => l_jit_err_status,
p_err_code => l_sql_code,
p_media_item_id => l_media_item_id );
SELECT logical_operator, left_paren, block_id, comparison_operator,
expression, expr_to_block_id, right_paren
FROM csc_prof_check_rules_vl
WHERE check_id = chk_id;
select block_level from
csc_prof_blocks_b
where block_id=v_blk_id;
rule := 'SELECT 1 FROM dual WHERE';
'EXISTS (SELECT 1 FROM csc_prof_block_results WHERE block_id = ' ||
rules_rec.block_id || ' AND party_id = :party_id' ||
' AND cust_account_id IS NULL AND value ' || rules_rec.comparison_operator;
rule := rule || '(SELECT value FORM csc_prof_block_results WHERE block_id = '||
rules_rec.expr_to_block_id || ' AND cust_account_id IS NULL )';
'EXISTS (SELECT 1 FROM csc_prof_block_results WHERE block_id = ' ||
rules_rec.block_id || ' AND party_id = :party_id' ||
' AND cust_account_id = :cust_account_id' ||
' AND value ' || rules_rec.comparison_operator;
rule := rule || '(SELECT value FORM csc_prof_block_results WHERE block_id = '
||
rules_rec.expr_to_block_id || ' AND party_id = :party_id' ||
' AND cust_account_id = :cust_account_id)' ;
'EXISTS (SELECT 1 FROM csc_prof_block_results WHERE block_id = ' ||
rules_rec.block_id || ' AND party_id = :party_id' ||
' AND cust_account_id IS NULL AND value ' || rules_rec.comparison_operator;
rule := rule || '(SELECT value FORM csc_prof_block_results WHERE block_id = '||
rules_rec.expr_to_block_id || ' AND cust_account_id IS NULL )';
PROCEDURE Table_Delete IS
Begin
IP_Block_Id.delete;
IP_Check_Id.delete;
IP_Party_Id.delete;
IP_Account_Id.delete;
IP_Value.delete;
IP_Currency.delete;
UP_Grade.delete;
UP_Block_Id.delete;
UP_Check_Id.delete;
UP_Party_Id.delete;
UP_Account_Id.delete;
UP_Value.delete;
UP_Currency.delete;
UP_Grade.delete;
IA_Block_Id.delete;
IA_Check_Id.delete;
IA_Party_Id.delete;
IA_Account_Id.delete;
IA_Value.delete;
IA_Currency.delete;
UA_Grade.delete;
UA_Block_Id.delete;
UA_Check_Id.delete;
UA_Party_Id.delete;
UA_Account_Id.delete;
UA_Value.delete;
UA_Currency.delete;
UA_Grade.delete;
plan_id_plan_table.delete;
check_id_plan_table.delete;
party_id_plan_table.delete;
account_id_plan_table.delete;
END Table_Delete;