[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_delete_flag varchar2(1); -- added by kalyan for delete flag
g_last_updated_by number ;
g_last_update_login number ;
g_insert_scheme varchar2(30) := null;
procedure insert_order_measures;
procedure insert_order_delivery_measures;
delete from bic_periods;
insert into bic_periods
( ACT_PERIOD_NAME ,
LAST_UPDATE_DATE ,
CREATION_DATE ,
LAST_UPDATED_BY ,
CREATED_BY ,
START_DATE ,
ACT_PERIOD_START_DATE ,
ACT_PERIOD_END_DATE ,
ACT_YEAR ,
ACT_PERIOD_NUM ,
ACT_QUARTER ,
ACT_YEAR_START_DATE ,
ACT_QUARTER_START_DATE ,
ACT_HALF_YEAR )
SELECT period_name act_period_name
, sysdate
, sysdate
, 0
, 0
, start_date
, start_date act_period_start_date
, end_date act_period_end_date
, period_year act_year
, period_num act_period_num
, quarter_num act_quarter
, year_start_date act_year_start_date
, quarter_start_date act_quarter_start_date
, decode(quarter_num,1,1,2,1,3,2,4,2,null) act_half_year
from gl_periods gprd
WHERE
PERIOD_SET_NAME=FND_PROFILE.VALUE('CRMBIS:PERIOD_SET_NAME')
AND ADJUSTMENT_PERIOD_FLAG <>'Y'
AND PERIOD_TYPE=FND_PROFILE.VALUE('CRMBIS:PERIOD_TYPE')
;
select count(*) into cnt
from bic_dimv_time
where --act_period_start_date = trunc(p_date,'MONTH');
select fnd_profile.value('BIC_SMRY_EXTRACTION_DATE') into x_date
from dual;
select act_period_end_date +1 into g_period_start_date
from bic_dimv_time
where trunc(act_period_start_date) = trunc(x_date);
select max(act_period_start_date) into g_period_end_date
from bic_dimv_time
where trunc(act_period_end_date) < trunc(sysdate);
p_delete_flag varchar2,
p_org_id number ) IS
TYPE curTyp IS REF CURSOR;
org_str := ' select distinct bdt.* , hou.organization_id
from hr_operating_units hou,
fnd_product_groups ,
bic_party_summ db ,
bic_dimv_time bdt
where product_group_id = 1
and multi_org_flag = ''Y''
and ACT_PERIOD_START_DATE between :p_start_date
and :p_end_date
and hou.organization_id = db.org_id (+)
and hou.organization_id = nvl ( :p_org_id , hou.organization_id)
and not exists (
select 1
from bic_party_summ bps
where bdt.ACT_PERIOD_START_DATE = bps.PERIOD_START_DATE
and bps.org_id = hou.organization_id
and '||rtrim(ltrim(p_measure_code))||' is not null ) ' ;
n_org_str:= ' select *
from bic_dimv_time bdt
where ACT_PERIOD_START_DATE between :p_start_date
and :p_end_date
and not exists (
select 1
from bic_party_status_summ bps
where bdt.ACT_PERIOD_START_DATE = bps.PERIOD_START_DATE
and '||rtrim(ltrim(p_measure_code))||' is not null ) ' ;
if (p_delete_flag = 'Y' ) then
write_log('Exiting from fill_dates as delete flag is Y');
delete from bic_temp_periods;
insert into bic_temp_periods
( ACT_PERIOD_NAME, START_DATE, ACT_PERIOD_START_DATE, ACT_PERIOD_END_DATE, ACT_YEAR,
ACT_PERIOD_NUM, ACT_QUARTER, ACT_YEAR_START_DATE, ACT_QUARTER_START_DATE, ACT_HALF_YEAR , ORG_ID ) values
( rec_temp.ACT_PERIOD_NAME, rec_temp.START_DATE, rec_temp.ACT_PERIOD_START_DATE, rec_temp.ACT_PERIOD_END_DATE, rec_temp.ACT_YEAR,
rec_temp.ACT_PERIOD_NUM, rec_temp.ACT_QUARTER, rec_temp.ACT_YEAR_START_DATE, rec_temp.ACT_QUARTER_START_DATE, rec_temp.ACT_HALF_YEAR, rec_temp.ORG_ID ) ;
insert into bic_temp_periods
( ACT_PERIOD_NAME, START_DATE, ACT_PERIOD_START_DATE, ACT_PERIOD_END_DATE, ACT_YEAR,
ACT_PERIOD_NUM, ACT_QUARTER, ACT_YEAR_START_DATE, ACT_QUARTER_START_DATE, ACT_HALF_YEAR ) values
( rec.ACT_PERIOD_NAME, rec.START_DATE, rec.ACT_PERIOD_START_DATE, rec.ACT_PERIOD_END_DATE, rec.ACT_YEAR,
rec.ACT_PERIOD_NUM, rec.ACT_QUARTER, rec.ACT_YEAR_START_DATE, rec.ACT_QUARTER_START_DATE, rec.ACT_HALF_YEAR ) ;
delete from bic_temp_periods;
insert into bic_temp_periods (
SELECT bdt.act_period_name , bdt.start_date , bdt.act_period_start_date ,
bdt.act_period_end_date , bdt.act_year , bdt.act_period_num ,
bdt.act_quarter , bdt.act_year_start_date , bdt.act_quarter_start_date ,
bdt.act_half_year , null
FROM bic_dimv_time bdt
where ACT_PERIOD_START_DATE between p_start_date
and p_end_date );
select measure_id
from bic_measures_all
where org_id is null
and measure_code = p_measure_code;
insert into bic_debug ( report_id,message,creation_date)
values ('BICCSUMM' || to_char(g_srl_no,'999'),
to_char(g_srl_no,'99') || '-'|| p_msg || ': ' ||
g_proc_name || ': ' ||
to_char(sysdate,'HH24:mi:ss'),
sysdate
);
select disable_flag into x_disable_flag
from bic_measure_attribs
where measure_code = p_measure_code;
select nvl(activation_period,90)
from bic_profile_values_all;
select nvl(ATTRITION_PERIOD,2)
from bic_profile_values_all;
select value into p_value
from bic_customer_summary_all
where period_start_date = p_period_start_date
and customer_id = p_customer_id
and measure_id = p_measure_id;
select measure_id into p_measure_id
from bic_measures_all
where measure_code = p_measure_code
and nvl(org_id,-1) = nvl(p_org_id,-1);
select nvl(weight,0) , measure_code
into p_weight, p_measure_code
from bic_measures_all
where measure_id = p_measure_id;
select bucket_id, bucket_points --into p_bucket_id, p_bucket_points
from bic_measure_buckets
where nvl(p_value,0) >= nvl(low_value,0)
and nvl(p_value,0) < nvl(high_value,p_value+2)
and measure_id = p_measure_id
order by low_value;
procedure update_score is
cursor cust_summary_recs is
select measure_id, value, org_id
from bic_customer_summary_all
where bucket_id = -1
and trunc(period_start_date)
between trunc(g_period_start_date) and trunc(g_period_end_date)
and (g_org_id is null or g_org_id = org_id)
for update of score, bucket_id;
g_proc_name := 'Update_score';
update bic_customer_summary_all
set bucket_id = x_bucket_id,
measure_code = x_measure_code,
score = nvl(x_weight * x_bucket_points,0)
where current of cust_summary_recs;
end update_score;
procedure insert_record(p_measure_code varchar2,
p_period_start_date date,
p_customer_id number,
p_value number,
p_org_id number,
p_index varchar2 default null) as
x_measure_id bic_measures_all.measure_id % type;
g_proc_name := 'insert_record';
-- have no impact on parent measures' value. So do not insert
-- such records.
if x_weight = 0 then return; end if;
insert into bic_customer_summary_all (
MEASURE_ID ,
PERIOD_START_DATE ,
CUSTOMER_ID ,
BUCKET_ID ,
VALUE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
ORG_ID ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
SCORE ,
MEASURE_CODE )
values ( x_measure_id , --MEASURE_ID
p_period_start_date , --PERIOD_START_DATE
p_customer_id , --CUSTOMER_ID
x_bucket_id , --BUCKET_ID
p_value , --VALUE
sysdate ,
g_last_updated_by ,
sysdate ,
g_created_by ,
p_org_id , -- ORG_ID
g_last_update_login ,
g_request_id ,
g_program_application_id ,
g_program_id ,
sysdate ,
x_score , -- SCORE
p_measure_code
);
end insert_record;
x_insert_str varchar2(2000);
-- such as login_id, update_date etc can be inserted into SQL statement
x_from_pos := instr(upper(p_sttmnt),'FROM' );
-- condition can be inserted into SQL statement. The extra condition may
-- be needed if org_id is not null.
x_where_end := instr(upper(p_sttmnt),'WHERE') + 6;
x_extra_cond := 'exists (select 1 from bic_temp_periods btp where btp.start_date = bdt.start_date) and';
if g_delete_flag = 'N' then
x_extra_cond := 'exists (select 1 from bic_temp_periods btp where btp.start_date = bdt.start_date and btp.org_id = bma.org_id) and ';
x_insert_str := substr(p_sttmnt,1,x_where_end -1) ||
x_extra_cond ||
substr(p_sttmnt,x_where_end)
;
debug('sql query : '|| x_insert_str);
open x_cur for x_insert_str using g_period_start_date,
g_period_end_date;
open x_cur for x_insert_str using g_org_id,
g_period_start_date,
g_period_end_date;
write_log('g_last_updated_by :'||g_last_updated_by);
insert into bic_customer_summary_all (
MEASURE_ID
,CUSTOMER_ID
,PERIOD_START_DATE
,ORG_ID
,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
,MEASURE_CODE)
values (
x_measure_id
,x_customer_id
,x_period_start_date
,x_org_id
,x_value
,x_bucket_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
,x_bucket_points*x_weight
,x_measure_code)
;
select distinct period_start_date, customer_id, bma.org_id
from bic_customer_summary_all bcs,
bic_measure_hierarchy bmh,
bic_measures_all bma
where bcs.measure_id = bma.measure_id
and bmh.measure_code = bma.measure_code
and bmh.parent_measure_code = p_measure_code
and trunc(bcs.period_start_date)
between trunc(g_period_start_date) and trunc(g_period_end_date)
and (g_org_id is null or g_org_id = bma.org_id);
select measure_code, operation_code
from bic_measure_hierarchy
where parent_measure_code = cp_parent_measure_code
order by sequence_number ;
insert_record(p_measure_code ,
x_period_start_date ,
x_customer_id ,
x_value ,
x_org_id ,
null );
bic_summary_extract_pkg.write_log('No of Recrods Inserted='||to_char(cust_and_dates%rowcount));
select period_start_date, customer_id, bma.org_id,
bcs.measure_id, bcs.value, bmh.operation_code
from bic_customer_summary_all bcs,
bic_measure_hierarchy bmh,
bic_measures_all bma
where bcs.measure_id = bma.measure_id
and trunc(bcs.period_start_date)
between trunc(g_period_start_date) and trunc(g_period_end_date)
and bmh.measure_code = bma.measure_code
and bmh.parent_measure_code = p_measure_code
and (g_org_id is null or g_org_id = bma.org_id);
select act_period_end_date - act_period_start_date +1 into x_days
from bic_temp_periods
where trunc(start_date) = trunc(x_period_start_date)
and nvl(org_id,x_org_id) = x_org_id;
insert_record(p_measure_code ,
x_period_start_date ,
x_customer_id ,
x_value ,
x_org_id ) ;
bic_summary_extract_pkg.write_log('No of Recrods Inserted='||to_char(cust_and_dates%rowcount));
select bcs.customer_id, bcs.org_id, bcs.period_start_date,
sum(nvl(bcs.score,0)), sum(nvl(bma.weight,0)),count(1)
from bic_measure_hierarchy bmh,
bic_measures_all bma,
bic_customer_summary_all bcs
where bmh.parent_measure_code = p_measure_code
and bmh.measure_code = bma.measure_code
and (bma.org_id = g_org_id or g_org_id is null)
and bma.measure_id = bcs.measure_id
and trunc(bcs.period_start_date)
between trunc(g_period_start_date) and trunc(g_period_end_date)
group by bcs.customer_id, bcs.org_id,bcs.period_start_date;
--There is no need to insert record with value=0 as it will
-- not contribute to main index. main index is sub index value times
-- weight and main index with 0 value is of no use.
insert_record(p_measure_code,
x_period_start_date,
x_customer_id,
x_value,
x_org_id,
p_index);
bic_summary_extract_pkg.write_log('No of Recrods Inserted='||to_char(index_recs%rowcount));
select sql_statement, operation_type, nvl(mult_factor,1)
into p_sttmnt, p_operation_type, p_mult_factor
from bic_measure_attribs
where measure_code = p_measure_code
and nvl(disable_flag,'N') <> 'Y';
select measure_code, max(level)
from bic_measure_hierarchy
where measure_code not in ('REFERALS','INTERAC_CUML')
start with parent_measure_code = p_measure_code
connect by prior measure_code = parent_measure_code
group by measure_code
order by 2 desc, 1;
g_delete_flag,
g_org_id);
select count(*) into rec_count
from bic_temp_periods;
g_delete_flag,
g_org_id);
select count(*) into rec_count
from bic_temp_periods;
select party_id, min(nvl(account_established_date,creation_date))
from hz_cust_accounts
group by party_id;
insert into bic_party_summary (
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
,measure_code)
select distinct
g_measure_id_for_retn
,party_id --hca.cust_account_id
,add_months(period_start_date,g_attrition_period*1)
,4
,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
,'RETENTION'
from bic_party_summary psum
where psum.period_start_date
between add_months(g_period_start_date,g_attrition_period*-1)
and add_months(g_period_end_date,g_attrition_period*-1)
and measure_id = g_measure_id_for_retn
and not exists ( select 1 from bic_party_summary psum_in
where psum_in.measure_id = g_measure_id_for_retn
and psum_in.party_id = psum.party_id
and psum_in.period_start_date =
add_months(psum.period_start_date,g_attrition_period)
);
select party_id, min(nvl(account_established_date,creation_date))
from hz_cust_accounts
group by party_id;
insert into bic_party_summary (
MEASURE_ID
,PARTY_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
,measure_code)
select g_measure_id_for_retn
,x_party_id --hca.cust_account_id
,bdt.act_period_start_date
,3
,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
,'RETENTION'
from bic_temp_periods bdt
where trunc(bdt.start_date)
--Ex: if the start date is 1-mar-2003 and the attrition period is 2 months then calculate retention retained from
--1-jan-2003 inorder to calculate retention_churned
between trunc(add_months(g_period_start_date,g_attrition_period*-1)) and trunc(g_period_end_date)
and x_account_established_date
<= add_months(bdt.act_period_end_date, g_attrition_period*-1)
and exists (select 'x' from oe_order_headers_all oeh,
hz_cust_accounts hca
where oeh.sold_to_org_id = hca.cust_account_id
and hca.party_id = x_party_id
and ordered_date between add_months(bdt.act_period_end_date,
g_attrition_period*-1)+1
and bdt.act_period_end_date
)
and (exists
(select 'x' from oe_order_headers_all oeh,
hz_cust_accounts hca
where oeh.sold_to_org_id = hca.cust_account_id
and hca.party_id = x_party_id
and ordered_date between add_months(bdt.act_period_end_date,
g_attrition_period*-2)+1
and add_months(bdt.act_period_end_date,
g_attrition_period*-1)
)
or x_account_established_date
between add_months(bdt.act_period_end_date,
g_attrition_period * -2) +1
and add_months(bdt.act_period_end_date,
g_attrition_period * -1)
)
;
select party_id, min(nvl(account_established_date,creation_date))
from hz_cust_accounts
group by party_id;
insert into bic_party_summary (
MEASURE_ID
,PARTY_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
,measure_code)
select g_measure_id_for_retn
,x_party_id
,bdt.act_period_start_date
,2
,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
,'RETENTION'
from bic_temp_periods bdt
where trunc(bdt.start_date)
--Ex: if the start date is 1-mar-2003 and the attrition period is 2 months then
--calculate retention reactivated from
--1-jan-2003 inorder to calculate retention_churned
between trunc(add_months(g_period_start_date,g_attrition_period*-1))and trunc(g_period_end_date)
and x_account_established_date
<=add_months(bdt.act_period_end_date, g_attrition_period*-2)
-- above line means acquired before previous attrition period
-- <= sign is used because you want to account_established date between
-- 1-apr-99 and 30-jun-99 and not between 31-mar-99 and 30-jun-99.
-- 30-jun-99 minus 3 months will return 31-mar-99
--
-- for same reasons 1 is added while comparing ordered_date
and exists (select 'x' from oe_order_headers_all oeh,
hz_cust_accounts hca
where oeh.sold_to_org_id = hca.cust_account_id
and hca.party_id = x_party_id
and ordered_date between add_months(bdt.act_period_end_date,
g_attrition_period*-1)+1
and bdt.act_period_end_date
)
and not exists
(select 'x' from oe_order_headers_all oeh,
hz_cust_accounts hca
where oeh.sold_to_org_id = hca.cust_account_id
and hca.party_id = x_party_id
and ordered_date between add_months(bdt.act_period_end_date,
g_attrition_period*-2)+1
and add_months(bdt.act_period_end_date,
g_attrition_period*-1)
)
;
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_period_start_date,g_attrition_period*-1+1)
-- 1 month is added so that you can compare with
-- first period end date. Ex: g_period_start_date=1-aug-98, attrition
-- period = 3 month and above expression will return you 1-jun-98.
-- You want to know who was NEW on 31-aug-98
;
insert into bic_party_summary (
MEASURE_ID
,PARTY_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
,measure_code)
select
g_measure_id_for_retn
,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
,'RETENTION'
from bic_temp_periods bdt
where trunc(bdt.start_date)
--Ex: if the start date is 1-mar-2003 and the attrition period is 2 months
--then calculate retention new from
--1-jan-2003 inorder to calculate retention_churned
between trunc(add_months(g_period_start_date,g_attrition_period*-1))and trunc(g_period_end_date)
and x_account_established_date
between add_months(bdt.act_period_end_date,g_attrition_period*-1)+1
and bdt.act_period_end_date
;
if g_delete_flag = 'N' then
extract_periods( add_months(g_period_start_date,g_attrition_period*-1),
g_period_end_date ,
'RETENTION' ,
'N',
g_delete_flag,
null);
select count(*) into rec_count
from bic_temp_periods;
write_log('g_delete_flag : ' || g_delete_flag);
g_delete_flag,
g_org_id);
select count(*) into rec_count
from bic_temp_periods;
insert into bic_customer_summary_all (
measure_id,
customer_id,
period_start_date,
org_id,
value,
last_update_date,
creation_date,
program_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
last_update_login,
measure_code)
select bma.measure_id,
hca.party_id,
bdt.act_period_start_date,
bma.org_id,
sum( bic_summary_extract_pkg.convert_amt( gsb.currency_code, gl.gl_date, gl.acctd_amount) ) ,
sysdate,
sysdate,
sysdate,
g_last_updated_by,
g_created_by,
g_request_id,
g_program_application_id,
g_program_id,
g_last_update_login,
'SALES'
from hz_cust_accounts hca,
bic_temp_periods bdt,
bic_measures_all bma,
ra_customer_trx_all trx,
ra_customer_trx_lines_all lines,
ra_cust_trx_line_gl_dist_all gl,
gl_sets_of_books gsb
where nvl(bma.org_id,-99) = nvl(trx.org_id,-99)
and trx.bill_to_customer_id = hca.cust_account_id
and bma.measure_code = 'SALES'
and gl.gl_date between bdt.act_period_start_date and bdt.act_period_end_date
and trunc(bdt.start_date)
between trunc(p_period_start_date) and trunc(p_period_end_date)
and bma.org_id = decode(g_delete_flag,'N',bdt.org_id,nvl(p_org_id,bma.org_id))
and trx.customer_trx_id = lines.customer_trx_id
and lines.customer_trx_line_id = gl.customer_trx_line_id
and account_Set_flag = 'N'
and complete_flag = 'Y'
and account_class = 'REV'
and lines.line_type = 'LINE'
and trx.previous_customer_trx_id IS NULL -- modified for 2992478
and trx.set_of_books_id = gsb.set_of_books_id (+)
group by bma.measure_id,
hca.party_id,
bdt.act_period_start_date,
bma.org_id;
g_delete_flag,
g_org_id);
select count(*) into rec_count
from bic_temp_periods;
insert into bic_customer_summary_all (
measure_id,
customer_id,
period_start_date,
org_id,
value,
last_update_date,
creation_date,
program_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
last_update_login,
measure_code)
select bma.measure_id,
hca.party_id,
bdt.act_period_start_date,
bma.org_id,
sum(bic_summary_extract_pkg.convert_amt(gsb.currency_code,
cmt.gl_date,
cogs_amount)
),
sysdate,
sysdate,
sysdate,
g_last_updated_by,
g_created_by,
g_request_id,
g_program_application_id,
g_program_id,
g_last_update_login,
'COGS'
from hz_cust_accounts hca,
bic_temp_periods bdt,
bic_measures_all bma,
cst_bis_margin_summary cmt,
hr_organization_information hoi,
gl_sets_of_books gsb
where nvl(bma.org_id,-99) = nvl(cmt.org_id,-99)
and bma.measure_code = 'COGS'
and cmt.gl_date between bdt.act_period_start_date and
bdt.act_period_end_date
and trunc(bdt.start_date)
between trunc(g_period_start_date)and trunc(g_period_end_date)
and bma.org_id = decode(g_delete_flag,'N',bdt.org_id,nvl(g_org_id,bma.org_id))
and cmt.legal_entity_id = hoi.organization_id (+)
and hoi.org_information_context (+)= 'Legal Entity Accounting'
and hoi.org_information1 = gsb.set_of_books_id (+)
and cmt.customer_id = hca.cust_account_id
and cmt.source = 'COGS' -- added by vsegu
group by bma.measure_id,
hca.party_id,
bdt.act_period_start_date,
bma.org_id;
select party_id, min(nvl(account_established_date,creation_date))
from hz_cust_accounts
group by party_id;
g_delete_flag,
g_org_id);
select count(*) into rec_count
from bic_temp_periods;
insert into bic_party_summary ( --bic_customer_summary_all (
measure_id,
party_id, --customer_id,
period_start_date,
value,
last_update_date,
creation_date,
program_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
last_update_login,
measure_code)
select distinct
g_measure_id_for_acqu, --bma.measure_id,
x_party_id,
bdt.start_date,
/*to_number(to_char(nvl(hca.account_established_date,hca.creation_date)
,'J')
),*/ to_number(to_char(x_account_established_date,'J')),
sysdate,
sysdate,
sysdate,
g_last_updated_by,
g_created_by,
g_request_id,
g_program_application_id,
g_program_id,
g_last_update_login,
'ACQUISITION'
from bic_temp_periods bdt
where x_account_established_date
between bdt.act_period_start_date and
bdt.act_period_end_date ;
insert into bic_party_summary (measure_id ,
party_id ,
period_start_date ,
value ,
last_update_date ,
creation_date ,
program_update_date ,
last_updated_by ,
created_by ,
request_id ,
program_application_id,
program_id ,
last_update_login ,
measure_code)
select
g_measure_id_for_acti,
bcs.party_id,
trunc(min(aoh.ordered_date), 'MONTH'),
1,
sysdate,
sysdate,
sysdate,
g_last_updated_by,
g_created_by,
g_request_id,
g_program_application_id,
g_program_id,
g_last_update_login,
'ACTIVATION'
from oe_order_headers_all aoh, --4434468 replaced aso_i_oe_order_headers_v with oe_order_headers_all
bic_party_summary bcs,
hz_cust_accounts acct
where bcs.measure_id = g_measure_id_for_acqu
--and to_date(bcs.value + g_activation_period ,'J') >= g_period_start_date
--and to_date(bcs.value ,'J') <= g_period_end_date
and bcs.party_id = acct.party_id
and acct.cust_account_id = aoh.sold_to_org_id
and aoh.ordered_date between to_date(bcs.value,'J') and
to_date(bcs.value+g_activation_period,'J')
group by
g_measure_id_for_acti,
bcs.party_id,
sysdate,
sysdate,
sysdate,
g_last_updated_by,
g_created_by,
g_request_id,
g_program_application_id,
g_program_id,
g_last_update_login
having min(aoh.ordered_date) between g_period_start_date
and g_period_end_date;
p_delete_flag varchar2 default null,
p_measure_code varchar2 default null,
p_org_id number default null) as
x_start_date date;
if p_delete_flag is null then
g_delete_flag := 'N';
g_delete_flag := p_delete_flag;
write_log(' Delete Flag:'||p_delete_flag);
g_last_updated_by := fnd_global.user_id ;
g_last_update_login := fnd_global.login_id ;
if nvl(g_delete_flag,'N') = 'Y' then
extract_all_periods(g_period_start_date , g_period_end_date );
g_delete_flag,
g_org_id
);
bic_consolidate_cust_data_pkg.update_market_segment;
procedure insert_order_measures is
cursor c_orders is
SELECT hca.party_id customer_id,
bdt.start_date period_start_date,
ooh.org_id,
count(distinct decode(ool.line_category_code,'ORDER',ooh.header_id,
null
)) orders,
count(distinct decode(ool.line_category_code,'RETURN',ooh.header_id,
null
)) returns,
sum((decode(ool.line_category_code,'ORDER',
ool.ordered_quantity - nvl(ool.cancelled_quantity,0)) *
bic_summary_extract_pkg.convert_amt(
ooh.transactional_curr_code,
ooh.ordered_date,
ool.unit_selling_price))
) order_amt,
sum((decode(ool.line_category_code,'RETURN',
ool.ordered_quantity - nvl(ool.cancelled_quantity,0)) *
bic_summary_extract_pkg.convert_amt(
ooh.transactional_curr_code,
ooh.ordered_date,
ool.unit_selling_price))
) return_amt,
sum(decode(ool.line_category_code,'ORDER',
ool.ordered_quantity - nvl(ool.cancelled_quantity,0),
null)) order_qty,
sum(decode(ool.line_category_code,'RETURN',
ool.ordered_quantity - nvl(ool.cancelled_quantity,0),
null)) return_qty
FROM aso_i_oe_order_lines_v ool,
aso_i_oe_order_headers_v ooh,
bic_dimv_time bdt,
hz_cust_accounts hca
WHERE ooh.header_id = ool.header_id
AND ooh.sold_to_org_id is not null
AND ooh.ordered_date between bdt.start_date and act_period_end_date
AND trunc(bdt.start_date)
BETWEEN trunc(g_period_start_date)AND trunc(g_period_end_date)
and hca.cust_account_id = ooh.sold_to_org_id
group by hca.party_id , bdt.start_date, ooh.org_id
;
insert_record('ORDER_NUM', x_start_date, x_party_id,
x_orders, x_org_id);
insert_record('ORDER_QTY', x_start_date, x_party_id,
x_order_qty, x_org_id);
insert_record('ORDER_AMT', x_start_date, x_party_id,
x_order_amt, x_org_id);
insert_record('RETURNS', x_start_date, x_party_id,
x_returns, x_org_id);
insert_record('RETURN_QTY', x_start_date, x_party_id,
x_return_qty, x_org_id);
insert_record('RETURN_BY_VALUE', x_start_date, x_party_id,
x_return_amt, x_org_id);
end insert_order_measures;
procedure insert_order_delivery_measures is
cursor c_orders is
select
hca.party_id customer_id,
bdt.start_date period_start_date,
ooh.org_id,
count(decode(ool.line_category_code,'ORDER', ool.line_id,null)) line_dl,
count(decode(sign(ool.request_date-ool.actual_shipment_date),
1,null, 1)) line_ot,
sum(ool.shipped_quantity *
bic_summary_extract_pkg.convert_amt(ooh.transactional_curr_code,
ooh.ordered_date,
ool.unit_selling_price)
) del_val,
sum(decode(sign(ool.request_date-ool.actual_shipment_date),
1,0,
ool.shipped_quantity *
bic_summary_extract_pkg.convert_amt(ooh.transactional_curr_code,
ooh.ordered_date,
ool.unit_selling_price)
)) ontime_val
from
aso_i_oe_order_headers_v ooh,
aso_i_oe_order_lines_v ool,
hz_cust_accounts hca,
bic_dimv_time bdt
where
trunc(bdt.start_date)
BETWEEN trunc(g_period_start_date)AND trunc(g_period_end_date)
and ooh.sold_to_org_id is not null
and ool.header_id = ooh.header_id
and ool.actual_shipment_date between bdt.start_date
and bdt.act_period_end_date
and hca.cust_account_id = ooh.sold_to_org_id
group by
hca.party_id,
bdt.start_date,
ooh.org_id;
insert_record('ORDER_LINES_DELIVERED', x_start_date, x_party_id,
x_line_del, x_org_id);
insert_record('ORDER_LINES_ONTIME', x_start_date, x_party_id,
x_line_ontime, x_org_id);
insert_record('OL_DEL_VALUE', x_start_date, x_party_id,
x_del_val, x_org_id);
insert_record('OL_ONTIME_VALUE', x_start_date, x_party_id,
x_ontime_val, x_org_id);
end insert_order_delivery_measures;
procedure bulk_insert_sql_measures (p_stmnt varchar2) is
x_err varchar2(250);
insert into bic_debug(report_id,message) values ('BICSUMMB',x_err);
select sql_statement, measure_code
from bic_measure_attribs
where sql_statement is not null
and nvl(disable_flag,'N') = 'N'
;
x_str :=' insert into bic_customer_summary_all (
measure_id
,customer_id
,period_start_date
,org_id
,value
,bucket_id
,score
,measure_code
,last_update_date
,creation_date
,last_updated_by
,created_by)
select a.measure_id
,a.customer_id
,a.period_start_date
,a.org_id
,a.value
,b.bucket_id
,nvl(a.weight * b.bucket_points,0) score
,' || '''' || x_msr_code || '''' ||
',sysdate
,sysdate
,' || to_char(g_created_by) || ',' ||
to_char(g_last_updated_by) || '
from bic_measure_buckets b, (' || x_stmnt2 || ') a
where a.measure_id = b.measure_id(+)
and nvl(a.value,0) >= nvl(b.low_value (+),0)
and nvl(a.value,0) < nvl(b.high_value (+),
nvl(a.value,0)+2) ';
insert into bic_debug(report_id,message) values ('BICSUMMB',x_str);
bulk_insert_sql_measures(x_str);