[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER;
g_last_update_login NUMBER;
FUNCTION Common_Select RETURN VARCHAR2 ;
PROCEDURE Insert_New_Cust ;
PROCEDURE Insert_Insig_Cust(p_select VARCHAR2) ;
PROCEDURE Insert_Record (p_org_id NUMBER,
p_measure_id NUMBER,
p_start_date DATE,
p_cust_id NUMBER,
p_value NUMBER) ;
PROCEDURE Insert_Others(p_select VARCHAR2) ;
p_delete_flag varchar2,
p_org_id NUMBER
) IS
x_c_select VARCHAR2(2000) ;
IF p_delete_flag = 'N' THEN
bic_summary_extract_pkg.extract_periods (
add_months(p_start_date ,g_lc_new_cust_period*- 1),
p_end_date,
'LIFE_CYCLE',
'N',
p_delete_flag,
p_org_id);
SELECT count(*) INTO rec_count
FROM bic_temp_periods;
write_log('Before Inserting New Customers for Life Cycle ....',
'bic_lifecycle_extract_pkg.extract_lifecycle_data');
IF p_delete_flag = 'N' THEN
bic_summary_extract_pkg.extract_periods (
add_months(p_start_date ,g_lc_new_cust_period*- 1),
p_end_date,
'LIFE_CYCLE',
'N',
p_delete_flag,
p_org_id);
x_c_select := Common_Select ;
Insert_New_Cust ;
write_log('Before Inserting Insignificant Customers for Life Cycle ....',
'bic_lifecycle_extract_pkg.extract_lifecycle_data');
insert_Insig_Cust(x_c_select) ;
write_log('Before Inserting Stable, Defected, Growing and Declining
Customers for Life Cycle ....',
'bic_lifecycle_extract_pkg.extract_lifecycle_data');
Insert_Others(x_c_select) ;
PROCEDURE Insert_New_Cust IS
CURSOR party_cur IS
SELECT party_id, MIN(NVL(account_established_date,creation_date))
FROM hz_cust_accounts
GROUP BY party_id
HAVING MIN(NVL(account_established_date,creation_date)) >=
ADD_MONTHS(g_lc_st_month,g_lc_new_cust_period*-1 +1 );
bic_summary_extract_pkg.debug(' entered Insert_New_Cust + : ');
INSERT INTO bic_party_summary (
measure_code
,measure_id
,party_id --,customer_id
,period_start_date
,VALUE
,bucket_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,score)
SELECT
'LIFE_CYCLE'
,g_lc_measure_id
,x_party_id
,bdt.act_period_start_date
,1
,NULL
,SYSDATE
,g_last_updated_by
,SYSDATE
,g_created_by
,g_last_update_login
,g_request_id
,g_program_application_id
,g_program_id
,SYSDATE
,NULL
FROM
bic_temp_periods bdt
WHERE
bdt.start_date BETWEEN g_lc_st_month AND g_lc_end_month
AND x_account_established_date BETWEEN
ADD_MONTHS(bdt.act_period_end_date,g_lc_new_cust_period *-1)+1
AND bdt.act_period_end_date ;
bic_summary_extract_pkg.debug(' exited Insert_New_Cust + : ');
write_log('Error:' || sqlerrm, 'bic_lifecycle_extract_pkg.Insert_New_Cust') ; */
END Insert_New_Cust ;
FUNCTION Common_Select RETURN VARCHAR2 IS
x_n1 NUMBER ;
x_s1 := 'SELECT start_date, customer_id , sum(p1_value) value_p1, sum(p2_value) value_p2
FROM (' ;
x_sc1 := 'SELECT start_date, customer_id , ' ;
END Common_Select ;
PROCEDURE Insert_Insig_Cust(p_select VARCHAR2) IS
x_s VARCHAR2(40) ;
bic_summary_extract_pkg.debug(' entered Insert_Insig_Cust + : ');
x_s1 := 'SELECT start_date, customer_id FROM (' ;
OPEN x_cust_cur FOR x_s1 || p_select || x_s ;
Insert_Record (NULL, g_lc_measure_id, x_start_date(i), x_cust_id(i), 6) ;
bic_summary_extract_pkg.debug(' exited Insert_Insig_Cust + : ');
write_log('Error:' || sqlerrm || 'for party ' || x_cust_id(i), 'bic_lifecycle_extract_pkg.Insert_Insig_Cust');
END Insert_Insig_Cust ;
PROCEDURE Insert_Others(p_select VARCHAR2) IS
x_s VARCHAR2(40) ;
bic_summary_extract_pkg.debug(' entered Insert_Others + : ');
x_s1 := 'SELECT start_date, customer_id, value_p1, value_p2 FROM (' ;
OPEN x_cur FOR x_s1 || p_select || x_s ;
Insert_Record (NULL, g_lc_measure_id, x_start_date(i), x_cust_id(i), x_value) ;
bic_summary_extract_pkg.debug(' exited Insert_Others + : ');
write_log('Error:' || sqlerrm || ' for party ' || x_cust_id(i), 'bic_lifecycle_extract_pkg.Insert_Others'); */
END Insert_Others ;
PROCEDURE Insert_Record (p_org_id NUMBER,
p_measure_id NUMBER,
p_start_date DATE,
p_cust_id NUMBER,
p_value NUMBER) IS
BEGIN
INSERT INTO bic_party_summary
(measure_code, measure_id, period_start_date, VALUE,
party_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
request_id, program_application_id, program_id,
program_update_date)
VALUES ('LIFE_CYCLE', p_measure_id, p_start_date, p_value,
p_cust_id, SYSDATE, g_last_updated_by,
SYSDATE, g_created_by, g_last_update_login,
g_request_id, g_program_application_id, g_program_id,
SYSDATE) ;
write_log('LifeCycle Step-up resulted: Error inside insert_record :' || SQLERRM || ' for customer ' || p_cust_id ||
' period ' || p_start_date, 'bic_lifecycle_extract_pkg.Insert_Record');
-- write_log('Error:' || sqlerrm, 'bic_lifecycle_extract_pkg.Insert_Record');
END Insert_Record ;
g_last_updated_by := fnd_global.user_id ;
g_last_update_login := fnd_global.login_id ;
SELECT measure_value1, measure_value2, measure_value1_op, measure_value2_op
INTO g_growing_vlu1, g_growing_vlu2, g_op_growing1, g_op_growing2
FROM bic_lc_setup_all
WHERE stage_code = 'GROWING';
SELECT measure_value1, measure_value2, measure_value1_op, measure_value2_op
INTO g_defected_vlu1, g_defected_vlu2, g_op_defected1, g_op_defected2
FROM bic_lc_setup_all
WHERE stage_code = 'DEFECTED';
SELECT measure_value1, measure_value2, measure_value1_op, measure_value2_op
INTO g_declining_vlu1, g_declining_vlu2, g_op_declining1, g_op_declining2
FROM bic_lc_setup_all
WHERE stage_code = 'DECLINING';
SELECT lc_comparison_type, lc_measure_code,
lc_granularity_level, lc_starting_period,
lc_new_cust_period, lc_insig_level
INTO g_lc_comparison_type, g_measure_for_lc_stage,
g_lc_granularity_level,g_lc_starting_period,
g_lc_new_cust_period, g_lc_insig_level
FROM bic_profile_values_all
WHERE org_id IS NULL ;
SELECT measure_id INTO g_lc_measure_id
FROM bic_measures_all
WHERE measure_code = 'LIFE_CYCLE' AND org_id IS NULL ;