[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct customer_id, org_id, period_start_date
from bic_customer_summary_all
where period_start_date between p_start_date and p_end_date;
select measure_code, value
from bic_customer_summary_all
where period_start_date = g_date
and customer_id = g_party_id
and nvl(org_id,-99) = nvl(g_org_id,-99);
select ' elsif l_measure_code = ''' || measure_code || ''' then
l_' || measure_code || ' = l_measure_value'
from bic_measure_attribs
*****************************/
end loop;
update bic_party_summ set
ASSOCIATION_YRS = nvl(l_ASSOCIATION_YRS , ASSOCIATION_YRS ),
AVG_CLOSED_SRS = nvl(l_AVG_CLOSED_SRS , AVG_CLOSED_SRS ),
AVG_COMPLAINTS = nvl(l_AVG_COMPLAINTS , AVG_COMPLAINTS ),
AVG_HOLD_TIME = nvl(l_AVG_HOLD_TIME , AVG_HOLD_TIME ),
AVG_INTERACTIONS = nvl(l_AVG_INTERACTIONS , AVG_INTERACTIONS ),
AVG_INTERACTIONS_PER_SR = nvl(l_AVG_INTERACTIONS_PER_SR , AVG_INTERACTIONS_PER_SR ),
AVG_LEN_OF_EMP = nvl(l_AVG_LEN_OF_EMP , AVG_LEN_OF_EMP ),
AVG_OUTSTANDING_SRS = nvl(l_AVG_OUTSTANDING_SRS , AVG_OUTSTANDING_SRS ),
AVG_PERIOD_FOR_ACTIVE_CONT = nvl(l_AVG_PERIOD_FOR_ACTIVE_CONT, AVG_PERIOD_FOR_ACTIVE_CONT ),
AVG_SRS_LOGGED = nvl(l_AVG_SRS_LOGGED , AVG_SRS_LOGGED ),
AVG_SR_RESL_TIME = nvl(l_AVG_SR_RESL_TIME , AVG_SR_RESL_TIME ),
AVG_SR_RESPONSE_TIME = nvl(l_AVG_SR_RESPONSE_TIME , AVG_SR_RESPONSE_TIME ),
AVG_TALK_TIME = nvl(l_AVG_TALK_TIME , AVG_TALK_TIME ),
AVG_TRANSFERS_BEF_RESL = nvl(l_AVG_TRANSFERS_BEF_RESL , AVG_TRANSFERS_BEF_RESL ),
AVG_TRANSFERS_PER_SR = nvl(l_AVG_TRANSFERS_PER_SR , AVG_TRANSFERS_PER_SR ),
AVG_WORKLOAD = nvl(l_AVG_WORKLOAD , AVG_WORKLOAD ),
CALLS = nvl(l_CALLS , CALLS ),
CALL_LENGTH = nvl(l_CALL_LENGTH , CALL_LENGTH ),
CALL_TYPE_INTERACTIONS = nvl(l_CALL_TYPE_INTERACTIONS , CALL_TYPE_INTERACTIONS ),
CLOSED_SRS = nvl(l_CLOSED_SRS , CLOSED_SRS ),
COGS = nvl(l_COGS , COGS ),
CONTRACTS_CUML = nvl(l_CONTRACTS_CUML , CONTRACTS_CUML ),
CONTRACT_AMT = nvl(l_CONTRACT_AMT , CONTRACT_AMT ),
CONTRACT_DURATION = nvl(l_CONTRACT_DURATION , CONTRACT_DURATION ),
ESC_SRS = nvl(l_ESC_SRS , ESC_SRS ),
FIRST_CALL_CL_RATE = nvl(l_FIRST_CALL_CL_RATE , FIRST_CALL_CL_RATE ),
INACTIVE_CONTRACTS = nvl(l_INACTIVE_CONTRACTS , INACTIVE_CONTRACTS ),
INTERACTIONS = nvl(l_INTERACTIONS , INTERACTIONS ),
INTERAC_CUML = nvl(l_INTERAC_CUML , INTERAC_CUML ),
LOYALTY = nvl(l_LOYALTY , LOYALTY ),
NEW_CONTRACTS = nvl(l_NEW_CONTRACTS , NEW_CONTRACTS ),
NO_OF_COMPLAINTS = nvl(l_NO_OF_COMPLAINTS , NO_OF_COMPLAINTS ),
NO_OF_INTERACTIONS = nvl(l_NO_OF_INTERACTIONS , NO_OF_INTERACTIONS ),
NO_OF_TRANSFERS = nvl(l_NO_OF_TRANSFERS , NO_OF_TRANSFERS ),
OL_DEL_VALUE = nvl(l_OL_DEL_VALUE , OL_DEL_VALUE ),
OL_ONTIME_VALUE = nvl(l_OL_ONTIME_VALUE , OL_ONTIME_VALUE ),
ONTIME_PAYMENTS = nvl(l_ONTIME_PAYMENTS , ONTIME_PAYMENTS ),
ONTIME_SHIP_PCT = nvl(l_ONTIME_SHIP_PCT , ONTIME_SHIP_PCT ),
ONTIME_VALUE_PCT = nvl(l_ONTIME_VALUE_PCT , ONTIME_VALUE_PCT ),
ON_TIME_PAYMENT_RATE = nvl(l_ON_TIME_PAYMENT_RATE , ON_TIME_PAYMENT_RATE ),
OPEN_CONTRACTS = nvl(l_OPEN_CONTRACTS , OPEN_CONTRACTS ),
OPEN_SRS = nvl(l_OPEN_SRS , OPEN_SRS ),
ORDER_AMT = nvl(l_ORDER_AMT , ORDER_AMT ),
ORDER_LINES_DELIVERED = nvl(l_ORDER_LINES_DELIVERED , ORDER_LINES_DELIVERED ),
ORDER_LINES_ONTIME = nvl(l_ORDER_LINES_ONTIME , ORDER_LINES_ONTIME ),
ORDER_NUM = nvl(l_ORDER_NUM , ORDER_NUM ),
ORDER_QTY = nvl(l_ORDER_QTY , ORDER_QTY ),
ORDER_QTY_CUML = nvl(l_ORDER_QTY_CUML , ORDER_QTY_CUML ),
ORDER_RECENCY = nvl(l_ORDER_RECENCY , ORDER_RECENCY ),
PAYMENTS = nvl(l_PAYMENTS , PAYMENTS ),
PCT_ESC_SRS = nvl(l_PCT_ESC_SRS , PCT_ESC_SRS ),
PCT_REOPENED_SRS = nvl(l_PCT_REOPENED_SRS , PCT_REOPENED_SRS ),
PCT_RETURN_QTY = nvl(l_PCT_RETURN_QTY , PCT_RETURN_QTY ),
PERCT_CALL_REWORK = nvl(l_PERCT_CALL_REWORK , PERCT_CALL_REWORK ),
PRODUCTS = nvl(l_PRODUCTS , PRODUCTS ),
PROFITABILITY = nvl(l_PROFITABILITY , PROFITABILITY ),
REFERALS = nvl(l_REFERALS , REFERALS ),
RENEWED_CONTRACTS = nvl(l_RENEWED_CONTRACTS , RENEWED_CONTRACTS ),
REOPENED_SRS = nvl(l_REOPENED_SRS , REOPENED_SRS ),
RETURNS = nvl(l_RETURNS , RETURNS ),
RETURN_BY_VALUE = nvl(l_RETURN_BY_VALUE , RETURN_BY_VALUE ),
RETURN_BY_VALUE_PCT = nvl(l_RETURN_BY_VALUE_PCT , RETURN_BY_VALUE_PCT ),
RETURN_QTY = nvl(l_RETURN_QTY , RETURN_QTY ),
SALES = nvl(l_SALES , SALES ),
SATISFACTION = nvl(l_SATISFACTION , SATISFACTION ),
SF_BILLING = nvl(l_SF_BILLING , SF_BILLING ),
SF_CONTRACT = nvl(l_SF_CONTRACT , SF_CONTRACT ),
SF_QUALITY = nvl(l_SF_QUALITY , SF_QUALITY ),
SF_SERVICE = nvl(l_SF_SERVICE , SF_SERVICE ),
SF_SHIPMENT = nvl(l_SF_SHIPMENT , SF_SHIPMENT ),
SRS_LOGGED = nvl(l_SRS_LOGGED , SRS_LOGGED ),
SR_CLOSED_INT = nvl(l_SR_CLOSED_INT , SR_CLOSED_INT ),
TOTAL_HOLD_TIME = nvl(l_TOTAL_HOLD_TIME , TOTAL_HOLD_TIME ),
TOTAL_LEN_OF_EMP = nvl(l_TOTAL_LEN_OF_EMP , TOTAL_LEN_OF_EMP ),
TOTAL_SR_RESL_TIME = nvl(l_TOTAL_SR_RESL_TIME , TOTAL_SR_RESL_TIME ),
TOTAL_SR_RESPONSE_TIME = nvl(l_TOTAL_SR_RESPONSE_TIME , TOTAL_SR_RESPONSE_TIME )
where period_start_date = g_date
and party_id = g_party_id
and nvl(org_id,-99) = nvl(g_org_id,-99);
insert into bic_party_summ (
party_id ,
org_id,
period_start_date,
ASSOCIATION_YRS ,
AVG_CLOSED_SRS ,
AVG_COMPLAINTS ,
AVG_HOLD_TIME ,
AVG_INTERACTIONS ,
AVG_INTERACTIONS_PER_SR ,
AVG_LEN_OF_EMP ,
AVG_OUTSTANDING_SRS ,
AVG_PERIOD_FOR_ACTIVE_CONT,
AVG_SRS_LOGGED ,
AVG_SR_RESL_TIME ,
AVG_SR_RESPONSE_TIME ,
AVG_TALK_TIME ,
AVG_TRANSFERS_BEF_RESL ,
AVG_TRANSFERS_PER_SR ,
AVG_WORKLOAD ,
CALLS ,
CALL_LENGTH ,
CALL_TYPE_INTERACTIONS ,
CLOSED_SRS ,
COGS ,
CONTRACTS_CUML ,
CONTRACT_AMT ,
CONTRACT_DURATION ,
ESC_SRS ,
FIRST_CALL_CL_RATE ,
INACTIVE_CONTRACTS ,
INTERACTIONS ,
INTERAC_CUML ,
LOYALTY ,
NEW_CONTRACTS ,
NO_OF_COMPLAINTS ,
NO_OF_INTERACTIONS ,
NO_OF_TRANSFERS ,
OL_DEL_VALUE ,
OL_ONTIME_VALUE ,
ONTIME_PAYMENTS ,
ONTIME_SHIP_PCT ,
ONTIME_VALUE_PCT ,
ON_TIME_PAYMENT_RATE ,
OPEN_CONTRACTS ,
OPEN_SRS ,
ORDER_AMT ,
ORDER_LINES_DELIVERED ,
ORDER_LINES_ONTIME ,
ORDER_NUM ,
ORDER_QTY ,
ORDER_QTY_CUML ,
ORDER_RECENCY ,
PAYMENTS ,
PCT_ESC_SRS ,
PCT_REOPENED_SRS ,
PCT_RETURN_QTY ,
PERCT_CALL_REWORK ,
PRODUCTS ,
PROFITABILITY ,
REFERALS ,
RENEWED_CONTRACTS ,
REOPENED_SRS ,
RETURNS ,
RETURN_BY_VALUE ,
RETURN_BY_VALUE_PCT ,
RETURN_QTY ,
SALES ,
SATISFACTION ,
SF_BILLING ,
SF_CONTRACT ,
SF_QUALITY ,
SF_SERVICE ,
SF_SHIPMENT ,
SRS_LOGGED ,
SR_CLOSED_INT ,
TOTAL_HOLD_TIME ,
TOTAL_LEN_OF_EMP ,
TOTAL_SR_RESL_TIME ,
TOTAL_SR_RESPONSE_TIME ,
last_updated_by ,
created_by ,
last_update_date ,
creation_date )
values ( g_party_id,
g_org_id,
g_date,
--l_ACQUISITION ,
--l_ACTIVATION ,
l_ASSOCIATION_YRS ,
l_AVG_CLOSED_SRS ,
l_AVG_COMPLAINTS ,
l_AVG_HOLD_TIME ,
l_AVG_INTERACTIONS ,
l_AVG_INTERACTIONS_PER_SR ,
l_AVG_LEN_OF_EMP ,
l_AVG_OUTSTANDING_SRS ,
l_AVG_PERIOD_FOR_ACTIVE_CONT,
l_AVG_SRS_LOGGED ,
l_AVG_SR_RESL_TIME ,
l_AVG_SR_RESPONSE_TIME ,
l_AVG_TALK_TIME ,
l_AVG_TRANSFERS_BEF_RESL ,
l_AVG_TRANSFERS_PER_SR ,
l_AVG_WORKLOAD ,
l_CALLS ,
l_CALL_LENGTH ,
l_CALL_TYPE_INTERACTIONS ,
l_CLOSED_SRS ,
l_COGS ,
l_CONTRACTS_CUML ,
l_CONTRACT_AMT ,
l_CONTRACT_DURATION ,
l_ESC_SRS ,
l_FIRST_CALL_CL_RATE ,
l_INACTIVE_CONTRACTS ,
l_INTERACTIONS ,
l_INTERAC_CUML ,
--l_LIFE_CYCLE ,
l_LOYALTY ,
l_NEW_CONTRACTS ,
l_NO_OF_COMPLAINTS ,
l_NO_OF_INTERACTIONS ,
l_NO_OF_TRANSFERS ,
l_OL_DEL_VALUE ,
l_OL_ONTIME_VALUE ,
l_ONTIME_PAYMENTS ,
l_ONTIME_SHIP_PCT ,
l_ONTIME_VALUE_PCT ,
l_ON_TIME_PAYMENT_RATE ,
l_OPEN_CONTRACTS ,
l_OPEN_SRS ,
l_ORDER_AMT ,
l_ORDER_LINES_DELIVERED ,
l_ORDER_LINES_ONTIME ,
l_ORDER_NUM ,
l_ORDER_QTY ,
l_ORDER_QTY_CUML ,
l_ORDER_RECENCY ,
l_PAYMENTS ,
l_PCT_ESC_SRS ,
l_PCT_REOPENED_SRS ,
l_PCT_RETURN_QTY ,
l_PERCT_CALL_REWORK ,
l_PRODUCTS ,
l_PROFITABILITY ,
l_REFERALS ,
l_RENEWED_CONTRACTS ,
l_REOPENED_SRS ,
--l_RETENTION ,
l_RETURNS ,
l_RETURN_BY_VALUE ,
l_RETURN_BY_VALUE_PCT ,
l_RETURN_QTY ,
l_SALES ,
l_SATISFACTION ,
l_SF_BILLING ,
l_SF_CONTRACT ,
l_SF_QUALITY ,
l_SF_SERVICE ,
l_SF_SHIPMENT ,
l_SRS_LOGGED ,
l_SR_CLOSED_INT ,
l_TOTAL_HOLD_TIME ,
l_TOTAL_LEN_OF_EMP ,
l_TOTAL_SR_RESL_TIME ,
l_TOTAL_SR_RESPONSE_TIME ,
0,0,sysdate,sysdate);
insert into bic_debug (report_id, message)
values ( 'SKM','Party_id:' || to_char(g_party_id) ||
x_err
);
end; -- of block for inserting record into bic_party_summ table.
select distinct party_id, period_start_date
from bic_party_summary
where period_start_date between p_start_date and p_end_date;
select measure_code, value
from bic_party_summary
where period_start_date = g_date
and party_id = g_party_id;
update bic_party_status_summ
set acquisition = nvl(l_acquisition, acquisition),
activation = nvl(l_activation , activation ),
retention = nvl(l_retention , retention ),
life_cycle = nvl(l_life_cycle , life_cycle )
where period_start_date = g_date
and party_id = g_party_id;
insert into bic_party_status_summ (
party_id ,
period_start_date,
ACQUISITION ,
ACTIVATION ,
RETENTION ,
LIFE_CYCLE ,
last_updated_by,
created_by,
last_update_date,
creation_date)
values ( g_party_id ,
g_date ,
l_ACQUISITION ,
l_ACTIVATION ,
l_RETENTION ,
l_LIFE_CYCLE ,
0,0,sysdate,sysdate );
insert into bic_debug (report_id, message)
values ( 'SKM','Party_id:' || to_char(g_party_id) ||
x_err
);
end; -- of block for inserting record into bic_party_summ table.
procedure update_market_segment is
begin
update bic_party_summ summ
set market_segment_id = (select market_segment_id
from ams_party_market_segments mseg
where market_segment_flag = 'Y'
and mseg.party_id = summ.party_id
and rownum = 1);
update bic_party_status_summ summ
set market_segment_id = (select market_segment_id
from ams_party_market_segments mseg
where market_segment_flag = 'Y'
and mseg.party_id = summ.party_id
and rownum = 1);
end update_market_segment;
update_market_segment;
delete from bic_customer_summary_all
where period_start_date between p_start_date and p_end_date;
delete from bic_party_summary
where period_start_date between p_start_date and p_end_date;
delete
from bic_party_summary;
delete
from bic_customer_summary_all;