[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
--g_for_insert Varchar2(1) := 'Y';
-- Utility to delete all records in pl/sql tables
--
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(100);
--v_select_clause VARCHAR2(100);
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,NULL, '; -- Added 'NULL' for party_site_id by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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',
'SITE' -- Added by spamujul for ER#8473903
);
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'
,'SITE' -- Added by spamujul for ER#8473903
);
select_clause;
--insert_stmnt := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T ' || batch_sql_stmnt; -- Commented the following code by spamujul for ER#8473903
insert_stmnt := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,PARTY_SITE_ID,VALUE) ' || batch_sql_stmnt;
EXECUTE IMMEDIATE (insert_stmnt);
v_select_pos := NULL;
v_select_length := NULL;
v_select_sum := 'SELECT hz.party_id, null,NULL, '; -- Added 'NULL' for party_site_id by spamujul for ER#8473903
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 ; -- Commented the following code by spamujul for ER#8473903
insert_stmnt_sum := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,PARTY_SITE_ID,VALUE) ' || v_select_sum ;
EXECUTE IMMEDIATE (insert_stmnt_sum);
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID,-- Added by spamujul for ER#8473903
VALUE)
SELECT party_id,
NULL,
NULL, -- Added by spamujul for ER#8473903
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 C.PARTY_SITE_ID IS NULL -- Added by spamujul for ER#8473903
)
AND hz.status = 'A'
AND hz.party_type IN ('PERSON', 'ORGANIZATION')
;
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID,-- Added by spamujul for ER#8473903
VALUE)
SELECT party_id,
NULL,
NULL, -- Added 'NULL' for party_site_id by spamujul for ER#8473903
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 C.PARTY_SITE_ID IS NULL -- Added by spamujul for ER#8473903
)
AND hz.status = 'A'
AND hz.party_type = 'PARTY_RELATIONSHIP' ;
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID,-- Added by spamujul for ER#8473903
VALUE
)
SELECT party_id,
cust_account_id,
NULL, -- Added 'NULL' for party_site_id by spamujul for ER#8473903
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 C.PARTY_SITE_ID IS NULL -- Added by spamujul for ER#8473903
)
AND hz.status = 'A'
;
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID,-- Added by spamujul for ER#8473903
VALUE)
SELECT person_id,
NULL,
NULL , -- Added 'NULL' for party_site_id by spamujul for ER#8473903
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
AND C.PARTY_SITE_ID IS NULL -- Added by spamujul for ER#8473903
) ;
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID,
VALUE)
SELECT hz.party_id,
NULL,
hz.party_site_id ,
NULL
FROM hz_party_sites hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c
WHERE c.party_id = hz.party_id
and c.party_site_id = hz.party_site_id)
AND hz.status = 'A'
AND nvl(hz.created_by_module,'XXX') <> 'SR_ONETIME';
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, party_site_id,value, currency_code, grade, '|| -- Inlcuded 'party_site_id' for ER#8473903
' 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,party_site_id, ' -- Inlcuded 'party_site_id' by spamujul for ER#8473903
|| 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 and PARTY_SITE_ID IS NULL'; -- Inlcuded 'party_site_id' IS NULL FOR ER#8473903
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, '||
-- added party_site_id in the below line for ER#8473903
'(check_results_id, check_id, party_id, cust_account_id,party_site_id, value, currency_code, grade, '|| -- Inlcuded 'party_site_id' for ER#8473903
' 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, '
-- added party_site_id in the below line for ER#8473903
|| chk_id || ', party_id, account_id,party_site_id, ' -- Inlcuded 'party_site_id' by spamujul for ER#8473903
|| 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(party_site_id, -999999) <> -999999 and account_id IS NULL';
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, party_site_id,value, currency_code, grade, '|| -- Inlcuded 'party_site_id' for ER#8473903
' 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,party_site_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 and PARTY_SITE_ID IS NULL'; -- Inlcuded 'party_site_id' IS NULL FOR ER#8473903';
EXECUTE IMMEDIATE (insert_stmnt_final);
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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_ID =42
AND check_level IN ('PARTY',
'ACCOUNT',
'SITE' --Added 'SITE' by spamujul for ER#8473903
)
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 party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
-- check if there are still records to be inserted
IF l_ip_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_up_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results(l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be inserted
IF l_ia_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_ua_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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'
,'SITE' -- added by spamujul for ER#8473903
)
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 party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
-- check if there are still records to be inserted
IF l_ip_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_up_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_ua_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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' ;
SELECT party_id,
party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = v_party_id
AND status ='A'
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
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_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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',
'SITE' --Added 'SITE' by spamujul for ER#8473903
)
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',
'SITE' -- Added 'SITE' by spamujul for ER#8473903
)
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 IN ( 'CONTACT',
'SITE' --Added 'SITE' by spamujul for ER#8473903
)
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 ( 'CONTACT',
'SITE' --Added 'SITE' by spamujul for ER#8473903
)
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', -- Added 'SITE' by spamujul for ER#8473903
'SITE'
)
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 party_id,party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = p_party_id
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
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 ( 'CONTACT',
'SITE' -- Added'SITE' by spamujul for ER#8473903
)
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_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- Loop through check_id and update block results for a party or account
OPEN checks_relparty_csr;
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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(100);
--v_select_clause VARCHAR2(100);
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,null, '; -- Included 'NULL' value for party_site_id by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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','SITE') -- Included 'SITE' by spamujul for ER#8473903
;
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','SITE') -- Included 'SITE' by spamujul for ER#8473903
;
select_clause;
insert_stmnt := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,PARTY_SITE_ID,VALUE) ' || batch_sql_stmnt; -- Added by spamujul for ER#8473903
EXECUTE IMMEDIATE (insert_stmnt);
v_select_pos := NULL;
v_select_length := NULL;
v_select_sum := 'SELECT hz.party_id, null, NULL, '; -- Added Last 'Null' for Site operation
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 ; -- Commented the following code by spamujul for ER#8473903
insert_stmnt_sum := 'INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,ACCOUNT_ID,PARTY_SITE_ID,VALUE)' || v_select_sum ;
EXECUTE IMMEDIATE (insert_stmnt_sum);
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID, --Included party_site_id for ER#8473903
VALUE)
SELECT party_id, NULL, NULL,NULL -- Included NULL for party_site_id for ER#8473903
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,
PARTY_SITE_ID, --Included party_site_id for ER#8473903
VALUE)
SELECT party_id, NULL, NULL ,NULL -- Included NULL for party_site_id for ER#8473903
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,
PARTY_SITE_ID, --Included party_site_id for ER#8473903
VALUE)
SELECT party_id, cust_account_id, NULL ,NULL -- Included NULL for party_site_id for ER#8473903
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,PARTY_SITE_ID,VALUE) --Included party_site_id for ER#8473903
SELECT person_id, NULL, NULL,NULL -- Included NULL for party_site_id for ER#8473903
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) ;
INSERT INTO CSC_PROF_BATCH_RESULTS1_T(PARTY_ID,
ACCOUNT_ID,
PARTY_SITE_ID, --Included party_site_id for ER#8473903
VALUE)
SELECT hz.party_id, NULL, hz.party_site_id ,NULL -- Included NULL for party_site_id for ER#8473903
FROM hz_party_sites hz
WHERE NOT EXISTS (SELECT 1 FROM CSC_PROF_BATCH_RESULTS1_T c WHERE c.party_id = hz.party_id and c.party_site_id = hz.party_site_id)
AND hz.status = 'A'
AND nvl(hz.created_by_module,'XXX') <> 'SR_ONETIME';
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, '||
-- added party_site_id in the below line for ER#8473903
'(check_results_id, check_id, party_id, cust_account_id,party_site_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, '
-- added party_site_id in the below line for ER#8473903
|| chk_id || ', party_id, account_id,party_site_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 and party_site_id IS NULL'; -- Added 'AND PARTY_SITE_ID IS NULL' for ER#8473903
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, '||
-- added party_site_id in the below line for ER#8473903
'(check_results_id, check_id, party_id, cust_account_id,party_site_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, '
-- added party_site_id in the below line for ER#8473903
|| chk_id || ', party_id, account_id,party_site_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(party_site_id, -999999) <> -999999 and account_id IS NULL';
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, '||
-- added party_site_id in the below line for ER#8473903
'(check_results_id, check_id, party_id, cust_account_id,party_site_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, '
-- added party_site_id in the below line for ER#8473903
|| chk_id || ', party_id, account_id,party_site_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 and party_site_id IS NULL'; -- Added 'AND PARTY_SITE_ID IS NULL' for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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,
party_site_id, -- Added by spamujul for ER#8473903
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','SITE') -- Included the 'SITE' by spamujul for ER#8473903
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 party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
-- check if there are still records to be inserted
IF l_ip_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_up_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be inserted
IF l_ia_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_ua_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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','SITE') -- Included by spamujul for ER#8473903
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 party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
SELECT block_id, sql_stmnt, currency_code
FROM csc_prof_blocks_b a
WHERE a.block_id = sel_blk_id;
-- check if there are still records to be inserted
IF l_ip_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_up_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be inserted
IF l_ia_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
-- check if there are still records to be updated
IF l_ua_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ia_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_ua_total,
l_for_insert,
l_for_party
,l_for_psite
);
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
and party_site_id is null -- Added by spamujul for ER#8473903
;
-- 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
, l_for_psite -- Added by spamujul for ER#8473903
);
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
select 1 into v_account_count from csc_prof_check_results
where check_id = p_chk_id
and party_id = p_party_id
and party_site_id is null -- Added by spamujul for ER#8473903
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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
select 1 into v_psite_count
from csc_prof_check_results
where check_id = p_chk_id
and party_id = p_party_id
and cust_account_id is null
and party_site_id =p_psite_id;
l_for_insert := 'Y';
Insert_Update_Check_Results
(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Check_Results(p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
--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'
,'SITE' -- Added by spamujul for ER#8473903
);
-- cursor to update all results of a party
CURSOR bparty_csr IS
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE IN ('PERSON','ORGANIZATION');
-- cursor to update all results of an account
/* added this condition party_id=v_party_id for Bug 1937730*/
CURSOR baccount_csr IS
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
SELECT party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
l_for_insert := 'Y';
Insert_Update_Block_Results(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Block_Results (p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
table_delete;
l_for_insert := 'Y';
Insert_Update_Block_Results(p_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
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' ;
SELECT party_id,
party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = v_party_id
AND status ='A'
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
table_delete;
-- check if there are still records to be inserted
IF p_ia_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Block_Results (p_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Block_Results(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Block_Results(p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
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));
-- cursor to update all results of an account
/* added this condition party_id=v_party_id for Bug 1937730*/
CURSOR baccount_csr IS
SELECT party_id, cust_account_id
FROM hz_cust_accounts
WHERE party_id=v_party_id
AND status = 'A' ;
SELECT party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
-- 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
,l_for_psite -- added by spamujul for ER#8473903
);
-- 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_psite -- added by spamujul for ER#8473903
);
-- 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
,l_for_psite -- Added by spamujul for ER# 8473903
);
-- 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_psite -- Added by spamujul for ER# 8473903
);
l_for_insert := 'Y';
Insert_Update_Block_Results(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Block_Results (p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
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','SITE') -- Included by spamujul for ER#8473903
;
-- cursor to update all results of a party
CURSOR bparty_csr IS
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 party_id,
party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = v_party_id
AND status ='A'
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
-- check if there are still records to be inserted
IF p_ia_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Block_Results(p_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Block_Results(p_ia_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Block_Results(p_ua_total,
l_for_insert,
l_for_party
,l_for_psite
);
table_delete;
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;
-- cursor to update all results of a party
CURSOR bparty_csr IS
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 party_id,
party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = v_party_id
AND status ='A'
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
l_for_insert := 'Y';
Insert_Update_Block_Results (p_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Block_Results(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
-- check if there are still records to be updated
IF p_us_total <> 0 THEN
l_for_insert := 'N';
Insert_Update_Block_Results(p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
table_delete;
-- check if there are still records to be inserted
IF p_ia_total <> 0 THEN
l_for_insert := 'Y';
Insert_Update_Block_Results(p_ia_total,
l_for_insert,
l_for_party
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
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','SITE') -- 'Included 'SITE' by spamujul for ER#8473903
AND (Sysdate BETWEEN Nvl(start_date_active, Sysdate)
AND Nvl(end_date_active, Sysdate));
SELECT party_id, party_site_id
FROM hz_party_sites
WHERE party_id=v_party_id
AND status = 'A'
AND NVL(created_by_module,'XXX') <> 'SR_ONETIME';
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Block_Results(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Block_Results (p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert varchar2(1) := 'Y';
SELECT party_id
FROM hz_parties
WHERE status='A'
AND PARTY_TYPE = 'PARTY_RELATIONSHIP';
SELECT party_id,
party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = v_party_id
AND status ='A'
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
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 IN ( 'CONTACT','SITE'); -- Included 'SITE' by spamujul for ER#8473903
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 IN ('CONTACT','SITE'); -- Included 'SITE' by spamujul for ER#8473903
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 ('CONTACT','SITE'); -- Included 'SITE' by spamujul for ER#8473903
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 IN ( 'CONTACT','SITE'); -- Included 'SITE' by spamujul for ER#8473903
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 IN ('CONTACT','SITE'); -- Included 'SITE' by spamujul for ER#8473903
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 ('CONTACT','SITE'); -- Included 'SITE' by spamujul for ER#8473903
l_for_insert := 'Y';
Insert_Update_Block_Results(p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
l_for_insert := 'N';
Insert_Update_Block_Results (p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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_psite -- Added by spamujul for ER#8473903
);
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 party_id,
party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = v_party_id
AND status ='A'
AND NVL(created_by_module,'XXX') <>'SR_ONETIME';
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 ( 'CONTACT','SITE') -- Included by spamujul for ER#8473903
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 IN ( 'CONTACT','SITE') -- Included by spamujul for ER#8473903
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 IN ( 'CONTACT','SITE') -- Included by spamujul for ER#8473903
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 IN ( 'CONTACT','SITE') -- Included by spamujul for ER#8473903'
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 IN ( 'CONTACT','SITE') -- Included by spamujul for ER#8473903'
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 IN ( 'CONTACT','SITE') -- Included by spamujul for ER#8473903'
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_is_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results(l_us_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'Y';
Insert_Update_Check_Results(l_ip_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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_psite -- Added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Block_Results(p_up_total,
l_for_insert,
l_for_party
,l_for_psite -- Added by spamujul for ER#8473903
);
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_psite -- added by spamujul for ER#8473903
);
l_for_insert := 'N';
Insert_Update_Check_Results (l_up_total,
l_for_insert,
l_for_party
,l_for_psite -- added by spamujul for ER#8473903
);
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
and party_site_id is NULL -- Added by spamujul for ER#8473903
;
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
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
and party_site_id is null -- Added by spamujul for ER#8473903
;
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
-- 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
,l_for_psite -- Added by spamujul for ER#8473903
);
select 1 into v_psite_count from csc_prof_block_results
where block_id = p_blk_id
and party_id = p_party_id
and cust_account_id IS NULL
and party_site_id =p_psite_id ;
l_for_insert := 'Y';
Insert_Update_Block_Results (p_is_total,
l_for_insert,
l_for_party
,l_for_psite
);
-- assign values to update party tables
us_block_id(p_us_total) := p_blk_id;
l_for_insert := 'N';
Insert_Update_Block_Results(p_us_total,
l_for_insert,
l_for_party
,l_for_psite
);
PROCEDURE Insert_Update_Block_Results
( p_count IN Number,
p_for_insert IN Varchar2,
p_for_party IN Varchar2
,p_for_psite IN Varchar2 -- Added by spamujul for ER#8473903
) 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
,party_site_id -- Added by spamujul for ER#8473903
, 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_psite_id(i) -- Added by spamujul for ER#8473903
, 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_psite_id.DELETE; -- Added by spamujul for ER#8473903
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)
AND cust_account_id IS NULL AND party_site_id IS NULL -- Added by spamujul for ER#8473903
;
up_block_id.DELETE;
up_party_id.DELETE;
up_account_id.DELETE;
up_psite_id.DELETE; -- Added by spamujul for ER#8473903
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
, party_site_id -- Added by spamujul for ER#8473903
, 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_psite_id(i) -- Added by spamujul for ER#8473903
, 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_psite_id.DELETE; -- Added by spamujul for ER#8473903
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)
And party_site_id IS NULL -- Added by spamujul for ER#8473903
;
ua_block_id.DELETE;
ua_party_id.DELETE;
ua_account_id.DELETE;
ua_psite_id.DELETE; -- Added by spamujul for ER#8473903
ua_value.DELETE;
ua_currency.DELETE;
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
, party_site_id
)
VALUES
(csc_prof_block_results_s.nextval
, is_block_id(i)
, is_party_id(i)
, is_account_id(i)
, is_value(i)
, is_currency(i)
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.USER_ID
, Sysdate
, FND_GLOBAL.CONC_LOGIN_ID
, is_psite_id(i)
);
is_block_id.DELETE;
is_party_id.DELETE;
is_account_id.DELETE;
is_psite_id.DELETE;
is_value.DELETE;
is_currency.DELETE;
UPDATE Csc_Prof_Block_Results
Set value = us_value(i)
, currency_code = us_currency(i)
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = sysdate
, last_update_login = FND_GLOBAL.CONC_LOGIN_ID
Where block_id = us_block_id(i)
And party_id = us_party_id(i)
And cust_account_id IS NULL
And party_site_id = us_psite_id(i)
;
us_block_id.DELETE;
us_party_id.DELETE;
us_account_id.DELETE;
us_psite_id.DELETE;
us_value.DELETE;
us_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 ,
p_for_psite IN VARCHAR2 -- Added by spamujul for ER#8473903
)
IS
TABLESEGMENT_FULL EXCEPTION;
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,
party_site_id, -- Added by spamujul for ER#8473903
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_psite_id(i), -- Added by spamujul for ER#8473903
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_psite_id.DELETE; -- Added by spamujul for ER#8473903
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
AND party_site_id IS NULL -- Added by spamujul for ER#8473903
;
up_check_id.DELETE;
up_party_id.DELETE;
up_account_id.DELETE;
up_psite_id.DELETE; -- Added by spamujul for ER#8473903
up_value.DELETE;
up_currency.DELETE;
up_grade.DELETE;
up_rating_code.DELETE;
up_color_code.DELETE;
up_results.DELETE;
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 ,
party_site_id , -- Added by spamujul for ER#8473903
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_psite_id(i) , -- Added by spamujul for ER#8473903
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_psite_id.DELETE; -- Added by spamujul for ER#8473903
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)
AND party_site_id IS NULL -- Added by spamujul for ER#8473903
;
ua_check_id.DELETE;
ua_party_id.DELETE;
ua_account_id.DELETE;
ua_psite_id.DELETE; -- Added by spamujul for ER#8473903
ua_value.DELETE;
ua_currency.DELETE;
ua_grade.DELETE;
ua_rating_code.DELETE;
ua_color_code.DELETE;
ua_results.DELETE;
IF p_for_insert = 'Y' THEN
FORALL i IN 1..p_count
INSERT
INTO Csc_Prof_Check_Results
(
check_results_id ,
check_id ,
party_id ,
cust_account_id ,
party_site_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 ,
is_check_id(i) ,
is_party_id(i) ,
is_account_id(i) ,
is_psite_id(i) ,
is_value(i) ,
is_results(i) ,
is_currency(i) ,
is_grade(i) ,
is_rating_code(i) ,
is_color_code(i) ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.CONC_LOGIN_ID
);
is_check_id.DELETE;
is_party_id.DELETE;
is_account_id.DELETE;
is_psite_id.DELETE;
is_value.DELETE;
is_currency.DELETE;
is_grade.DELETE;
is_rating_code.DELETE;
is_color_code.DELETE;
is_results.DELETE;
UPDATE Csc_Prof_Check_Results
SET value = us_value(i) ,
results_threshold_flag = us_results(i) ,
currency_code = us_currency(i) ,
grade = us_grade(i) ,
rating_code = us_rating_code(i) ,
color_code = us_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 = us_check_id(i)
AND party_id = us_party_id(i)
AND cust_account_id IS NULL
AND party_site_id = us_psite_id(i);
us_check_id.DELETE;
us_party_id.DELETE;
us_account_id.DELETE;
us_psite_id.DELETE;
us_value.DELETE;
us_currency.DELETE;
us_grade.DELETE;
us_rating_code.DELETE;
us_color_code.DELETE;
us_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 party_site_id IS NULL AND value ' || rules_rec.comparison_operator; -- Included "PARTY_SITE_ID IS NULL" by spamujul for ER#8473903
rule := rule || '(SELECT value from csc_prof_block_results WHERE block_id = '||
rules_rec.expr_to_block_id || ' AND cust_account_id IS NULL AND party_site_id IS NULL )'; -- Included "PARTY_SITE_ID IS NULL" by spamujul for ER#8473903
'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 party_site_id IS NULL' || -- Included "PARTY_SITE_ID IS NULL" by spamujul for ER#8473903
' AND value ' || rules_rec.comparison_operator;
rule := rule || '(SELECT value from 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 AND party_site_id IS NULL )' ; -- Included "PARTY_SITE_ID IS NULL" by spamujul for ER#8473903
'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 PARTY_SITE_ID IS NULL AND value ' || rules_rec.comparison_operator;
rule := rule || '(SELECT value from csc_prof_block_results WHERE block_id = '||
rules_rec.expr_to_block_id || ' AND cust_account_id IS NULL AND PARTY_SITE_ID IS NULL)';
'EXISTS (SELECT 1 FROM csc_prof_block_results WHERE block_id = ' ||
rules_rec.block_id || ' AND party_id = :party_id' ||
' AND party_site_id = :party_site_id and cust_account_id is null' ||
' AND value ' || rules_rec.comparison_operator;
rule := rule || '(SELECT value from csc_prof_block_results WHERE block_id = '
||rules_rec.expr_to_block_id || ' AND party_id = :party_id' ||
' AND party_site_id = :party_site_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 IS NULL AND party_site_id IS NULL AND value ' || rules_rec.comparison_operator; -- Included "PARTY_SITE_ID IS NULL" by spamujul for ER#8473903
rule := rule || '(SELECT value from csc_prof_block_results WHERE block_id = '||
rules_rec.expr_to_block_id || ' AND cust_account_id IS NULL AND party_site_id IS NULL)'; -- Included "PARTY_SITE_ID IS NULL" by spamujul for ER#8473903
PROCEDURE Table_Delete IS
Begin
IP_Block_Id.delete;
IP_Check_Id.delete;
IP_Party_Id.delete;
IP_Account_Id.delete;
IP_Psite_id.delete; -- added by spamujul for ER#8473903
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_Psite_id.delete; -- added by spamujul for ER#8473903
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_Psite_id.delete; -- added by spamujul for ER#8473903
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_Psite_id.delete; -- added by spamujul for ER#8473903
UA_Value.delete;
UA_Currency.delete;
UA_Grade.delete;
US_Block_Id.delete;
US_Check_Id.delete;
US_Party_Id.delete;
US_Account_Id.delete;
US_Psite_Id.delete;
US_Value.delete;
US_Currency.delete;
US_Grade.delete;
US_Rating_code.delete;
US_Color_code.delete;
US_Results.delete;
IS_Block_Id.delete;
IS_Check_Id.delete;
IS_Party_Id.delete;
IS_Account_Id.delete;
IS_Psite_Id.delete;
IS_Value.delete;
IS_Currency.delete;
IS_Grade.delete;
IS_Rating_Code.delete;
IS_Color_Code.delete;
IS_Results.delete;
plan_id_plan_table.delete;
check_id_plan_table.delete;
party_id_plan_table.delete;
account_id_plan_table.delete;
END Table_Delete;