[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Into_Sumry_Incr;
PROCEDURE Insert_Into_Sumry_Init;
SELECT MAX(period_to) INTO l_date
FROM bis_refresh_log
WHERE object_name = p_object_name AND
status='FAILURE' AND
last_update_date =(SELECT MAX(last_update_date)
FROM bis_refresh_log
WHERE object_name= p_object_name AND
status='FAILURE' ) ;
SELECT MIN(period_from) INTO l_date
FROM bis_refresh_log
WHERE object_name = p_object_name AND
status='SUCCESS' AND
last_update_date =(SELECT MIN(last_update_date)
FROM bis_refresh_log
WHERE object_name= p_object_name AND
status='SUCCESS' ) ;
SELECT count(1)
INTO l_count
FROM BIL_BI_OPDTL_STG
WHERE rownum < 2;
/*Update the staging with new conversion rates.*/
Summary_Err_Check
(
x_valid_curr => l_valid_curr,
x_valid_date => l_valid_date,
x_valid_prod => l_valid_prod,
x_return_warn => l_return_warn_resume
);
SELECT count(1) INTO l_count FROM BIL_BI_OPDTL_F WHERE rownum < 2;
Insert_Into_Sumry_Incr;
Insert_Into_Sumry_Init;
BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_OPDTL_F');
SELECT COUNT(1) into l_fact_count FROM BIL_BI_OPDTL_F where rownum < 2;
Insert_Into_Sumry_Incr;
Insert_Into_Sumry_Init;
SELECT DISTINCT
stg.txn_currency txn_currency,
trunc( decode(stg.prim_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'),least(sysdate, stg.EFFECTIVE_DATE))) TXN_DATE,
decode(sign(nvl(stg.prim_conversion_rate,-1)),-1,'P') prim_curr_type,
decode(sign(nvl(stg.CONVERSION_RATE_S,-1)),-1,'S') sec_curr_type
FROM BIL_BI_OPDTL_STG stg
WHERE ((stg.PRIM_CONVERSION_RATE < 0 OR stg.PRIM_CONVERSION_RATE is null )
OR (g_sec_currency IS NOT NULL AND (stg.CONVERSION_RATE_S < 0 OR stg.CONVERSION_RATE_S is null )))
AND effective_date<= add_months(trunc(g_program_start),24);
SELECT lead_number, effective_date FROM BIL_BI_OPDTL_STG
WHERE effective_date > p_date;
SELECT lead_number FROM BIL_BI_OPDTL_STG
WHERE effective_date <= p_date
and nvl(product_category_id,-999)=-999;
g_phase := 'update rates';
UPDATE /*+ PARALLEL(stg)*/ BIL_BI_OPDTL_STG stg
SET (stg.prim_conversion_rate,stg.CONVERSION_RATE_S)
= (select exchange_rate, exchange_rate_s
from BIL_BI_CURRENCY_RATE
where currency_code = stg.txn_currency
and exchange_date = stg.effective_date)
WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL)
OR ((CONVERSION_RATE_S < 0) OR CONVERSION_RATE_S IS NULL);
UPDATE BIL_BI_OPDTL_STG stg
SET (stg.prim_conversion_rate,stg.CONVERSION_RATE_S)
= (select exchange_rate, exchange_rate_s
from BIL_BI_CURRENCY_RATE
where currency_code = stg.txn_currency
and exchange_date = stg.effective_date)
WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL )
OR ((CONVERSION_RATE_S < 0) OR CONVERSION_RATE_S IS NULL);
UPDATE /*+ PARALLEL(stg)*/ BIL_BI_OPDTL_STG stg
SET stg.prim_conversion_rate =(select exchange_rate from BIL_BI_CURRENCY_RATE
where currency_code = stg.txn_currency
and exchange_date = stg.effective_date)
WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL) ;
UPDATE BIL_BI_OPDTL_STG stg
SET stg.prim_conversion_rate =
(select exchange_rate from BIL_BI_CURRENCY_RATE
where currency_code = stg.txn_currency
and exchange_date = stg.effective_date)
WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL);
p_msg => 'Updated rates for '|| sql%rowcount || ' rows');
SELECT /*+ PARALLEL(stg)*/ count(1)
INTO l_conv_rate_cnt
FROM BIL_BI_OPDTL_STG stg
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL AND (CONVERSION_RATE_S < 0 OR CONVERSION_RATE_S IS NULL)))
AND stg.effective_date <= l_limit_date;
SELECT count(1)
INTO l_conv_rate_cnt
FROM BIL_BI_OPDTL_STG stg
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL AND (CONVERSION_RATE_S < 0 OR CONVERSION_RATE_S IS NULL)))
AND stg.effective_date <= l_limit_date;
SELECT /*+ parallel(stg) */
NVL(MIN(stg.TXN_DATE), G_Start_Date),
NVL(Max(stg.TXN_DATE), G_End_Date),
LEAST(NVL(MIN(stg.Effective_DATE), G_Start_Date), NVL(MIN(stg.close_date), G_Start_Date)),
LEAST(add_months(sysdate,24), GREATEST(nvl(Max(stg.Effective_DATE),G_End_Date), nvl(Max(stg.close_DATE),G_End_Date)))
INTO l_stg_min_txn_dt,
l_stg_max_txn_dt,
l_stg_min_eff_dt,
l_stg_max_eff_dt
FROM BIL_BI_OPDTL_STG stg;
SELECT NVL(MIN(stg.TXN_DATE), G_Start_Date),
NVL(Max(stg.TXN_DATE), G_End_Date),
LEAST(NVL(MIN(stg.Effective_DATE), G_Start_Date), NVL(MIN(stg.close_date), G_Start_Date)),
LEAST(add_months(sysdate,24), GREATEST(nvl(Max(stg.Effective_DATE),G_End_Date), nvl(Max(stg.close_DATE),G_End_Date)))
INTO l_stg_min_txn_dt,
l_stg_max_txn_dt,
l_stg_min_eff_dt,
l_stg_max_eff_dt
FROM BIL_BI_OPDTL_STG stg;
p_msg => 'Deleted'|| sql%rowcount || ' rows due to bad item/prod or close date');
INSERT INTO BIL_BI_OPDTL_STG stg (
txn_date
,effective_date
,lead_id
,lead_line_id
,sales_credit_id
,sales_group_id
,salesrep_id
,txn_currency
,sales_credit_amount
--,primary_conversion_rate
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
--,valid_flag
,opty_creation_date
,opty_ld_conversion_date
,product_category_id
,item_id
,item_organization_id
,competitor_id
,lead_number
,hdr_source_promotion_id
--,line_source_promotion_id, not required?
,customer_id
,opty_global_amt
,owner_sales_group_id
,owner_salesrep_id
,sales_stage_id
,status
,close_date
,opty_rank
) SELECT /*+ full(denorm) */
denorm.opportunity_last_update_date
,CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
ELSE
TRUNC(denorm.decision_date)
END
,denorm.lead_id
,denorm.lead_line_id
,denorm.sales_credit_id
,denorm.sales_group_id
,denorm.salesforce_id
,denorm.currency_code
,denorm.sales_credit_amount
-- currency table
,denorm.win_probability
,denorm.opp_open_status_flag
,denorm.win_loss_indicator
,denorm.forecast_rollup_flag
--,valid_flag
,LEAST(TRUNC(denorm.opportunity_creation_date), TRUNC(denorm.decision_date))
,CASE WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
AND TRUNC(denorm.opportunity_creation_date) <= TRUNC(denorm.decision_date) )
THEN TRUNC(denorm.opportunity_creation_date)
WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
AND TRUNC(denorm.opportunity_creation_date) > TRUNC(denorm.decision_date) )
THEN TRUNC(denorm.decision_date)
ELSE TRUNC(MIN(aslo1.creation_date)) END
,denorm.product_category_id
,nvl(denorm.item_id,-1)
,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
,nvl(denorm.close_competitor_id,-1)
,denorm.lead_number
--, line not required?
,nvl(denorm.source_promotion_id,-1)
,nvl(denorm.customer_id,-1)
,denorm.total_amount
,denorm.owner_sales_group_id
,denorm.owner_salesforce_id
,denorm.sales_stage_id
,denorm.status_code
,TRUNC(denorm.decision_date)
,RANK() OVER(PARTITION BY lead_id ORDER BY lead_id, TRUNC(denorm.decision_date),
CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
ELSE
TRUNC(denorm.decision_date)
END) opty_rank
FROM as_sales_credits_denorm denorm,
as_sales_lead_opportunity aslo1
WHERE denorm.lead_id = aslo1.opportunity_id (+)
AND denorm.sales_group_id IS NOT NULL
AND denorm.sales_credit_amount IS NOT NULL
AND denorm.credit_type_id = g_credit_type_id
--both decision_date and forecast_date (if present) need to be between
--g_start_date and l_limit_date
AND (denorm.decision_date >= l_start_date
OR denorm.forecast_date >= l_start_date
OR denorm.opportunity_last_update_date >= G_Start_DATE)
AND denorm.decision_date >= g_start_date
AND denorm.decision_date <= l_limit_date
AND (denorm.forecast_date is null
OR (denorm.forecast_date >= g_start_date
AND denorm.forecast_date <= l_limit_date))
AND
(-- sc level change
exists ( select 1 from as_sales_credits credit
where credit.last_update_date>= G_Start_DATE
and denorm.lead_id = credit.lead_id)
OR denorm.opportunity_last_update_date >= G_Start_DATE
OR -- link to lead works => lead to opportunity
exists (SELECT 1 from as_sales_lead_opportunity aslo2
where denorm.lead_id = aslo2.opportunity_id
and aslo2.creation_date >= G_START_DATE)
)
GROUP BY
denorm.opportunity_last_update_date
,TRUNC(denorm.decision_date)
,denorm.lead_id
,denorm.lead_line_id
,denorm.sales_credit_id
,denorm.sales_group_id
,denorm.salesforce_id
,denorm.currency_code
,denorm.sales_credit_amount
--, currrency table
,denorm.win_probability
,denorm.opp_open_status_flag
,denorm.win_loss_indicator
,denorm.forecast_rollup_flag
--,valid_flag
,TRUNC(denorm.opportunity_creation_date)
,denorm.product_category_id
,nvl(denorm.item_id,-1)
,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
,nvl(denorm.close_competitor_id,-1)
,denorm.lead_number
--, line not required?
,denorm.source_promotion_id
,nvl(denorm.customer_id,-1)
,denorm.total_amount
,denorm.owner_sales_group_id
,denorm.owner_salesforce_id
,denorm.sales_stage_id
,denorm.status_code
,TRUNC(denorm.forecast_date);
p_msg => 'Rows Inserted into staging table are: '||l_cnt);
/*delete from bil.bil_bi_opdtl_f a where exists
(select lead_id from as_leads_all b where
a.opty_id=b.lead_id and b.last_update_date >=G_Start_Date
and b.lead_id not in (select distinct lead_id from as_sales_credits));
dbms_output.put_line('deleted these many rows in ins_new_chgd_opty_incr procedure from fact :'||l_cnt);*/
INSERT /*+ APPEND PARALLEL(stg)*/ INTO BIL_BI_OPDTL_STG stg (
txn_date
,effective_date
,lead_id
,lead_line_id
,sales_credit_id
,sales_group_id
,salesrep_id
,txn_currency
,sales_credit_amount
--,primary_conversion_rate
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
--,valid_flag
,opty_creation_date
,opty_ld_conversion_date
,product_category_id
,item_id
,item_organization_id
,competitor_id
,lead_number
,hdr_source_promotion_id
--,line_source_promotion_id, not required?
,customer_id
,opty_global_amt
,owner_sales_group_id
,owner_salesrep_id
,sales_stage_id
,status
,close_date
,opty_rank
) SELECT /*+ PARALLEL(denorm) PARALLEL(aslo1) PARALLEL(codes) */
denorm.opportunity_last_update_date
,CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
ELSE
TRUNC(denorm.decision_date)
END
,denorm.lead_id
,denorm.lead_line_id
,denorm.sales_credit_id
,denorm.sales_group_id
,denorm.salesforce_id
,denorm.currency_code
,denorm.sales_credit_amount
-- currency table
,denorm.win_probability
,denorm.opp_open_status_flag
,denorm.win_loss_indicator
,denorm.forecast_rollup_flag
--,valid_flag
,LEAST(TRUNC(denorm.opportunity_creation_date), TRUNC(denorm.decision_date))
,CASE WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
AND TRUNC(denorm.opportunity_creation_date) <= TRUNC(denorm.decision_date) )
THEN TRUNC(denorm.opportunity_creation_date)
WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
AND TRUNC(denorm.opportunity_creation_date) > TRUNC(denorm.decision_date) )
THEN TRUNC(denorm.decision_date)
ELSE TRUNC(MIN(aslo1.creation_date)) END
,denorm.product_category_id
,nvl(denorm.item_id,-1)
,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
,nvl(denorm.close_competitor_id,-1)
,denorm.lead_number
--, line not required?
,nvl(denorm.source_promotion_id,-1)
,nvl(denorm.customer_id,-1)
,denorm.total_amount
,denorm.owner_sales_group_id
,denorm.owner_salesforce_id
,denorm.sales_stage_id
,denorm.status_code
,TRUNC(denorm.decision_date)
,RANK() OVER(PARTITION BY lead_id ORDER BY lead_id, TRUNC(denorm.decision_date),
CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
ELSE
TRUNC(denorm.decision_date)
END) opty_rank
FROM as_sales_credits_denorm denorm,
as_sales_lead_opportunity aslo1
WHERE
denorm.lead_id = aslo1.opportunity_id (+)
--AND denorm.source_promotion_id = codes.source_code_id (+)
--check that both decision_date and forecast_date (if present)
--are after g_start_date and before l_limit_date
AND denorm.decision_date >= g_start_date
AND denorm.decision_date <= l_limit_date
AND (denorm.forecast_date is null OR (denorm.forecast_date >= g_start_date AND denorm.forecast_date <= l_limit_date))
AND denorm.sales_group_id IS NOT NULL
AND denorm.sales_credit_amount IS NOT NULL
AND denorm.credit_type_id = g_credit_type_id
GROUP BY
denorm.opportunity_last_update_date
,TRUNC(denorm.decision_date)
,denorm.lead_id
,denorm.lead_line_id
,denorm.sales_credit_id
,denorm.sales_group_id
,denorm.salesforce_id
,denorm.currency_code
,denorm.sales_credit_amount
--, currrency table
,denorm.win_probability
,denorm.opp_open_status_flag
,denorm.win_loss_indicator
,denorm.forecast_rollup_flag
--,valid_flag
,TRUNC(denorm.opportunity_creation_date)
,denorm.product_category_id
,nvl(denorm.item_id,-1)
,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
,nvl(denorm.close_competitor_id,-1)
,denorm.lead_number
--, line not required?
,nvl(denorm.source_promotion_id,-1)
,nvl(denorm.customer_id,-1)
,denorm.total_amount
,denorm.owner_sales_group_id
,denorm.owner_salesforce_id
,denorm.sales_stage_id
,denorm.status_code
,TRUNC(denorm.forecast_date);
p_msg => 'Rows Inserted into staging table are: '||sql%rowcount);
Procedure Insert_Into_Sumry_Incr
IS
l_sysdate DATE ;
l_proc := 'Insert_Into_Sumry_Incr';
DELETE FROM BIL_BI_OPDTL_F f
WHERE NOT EXISTS
( SELECT 1 FROM AS_SALES_CREDITS sc WHERE f.sales_credit_id = sc.sales_credit_id );
p_msg => 'Deleted '|| l_count ||' from BIL_BI_OPDTL_F');
(SELECT
lead_id
,to_number(to_char(txn_date, 'J')) txn_time_id
,to_number(to_char(effective_date, 'J')) opty_close_time_id
,to_number(to_char(opty_ld_conversion_date, 'J')) opty_ld_conversion_time_id
,to_number(to_char(opty_creation_date, 'J')) opty_creation_time_id
,SUM(decode(sales_credit_amount*prim_conversion_rate, 0, null, sales_credit_amount*prim_conversion_rate)) sales_credit_amt
,SUM(decode(sales_credit_amount*CONVERSION_RATE_S, 0, null, sales_credit_amount*CONVERSION_RATE_S)) sales_credit_amt_s
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_global_amt * prim_conversion_rate opty_amt
,opty_global_amt * CONVERSION_RATE_S opty_amt_s
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,g_user_id created_by
,sysdate creation_date
,g_user_id last_updated_by
,sysdate last_update_date
,G_Login_Id last_update_login
,G_request_id request_id
,G_appl_id program_application_id
,G_program_id program_id
,sysdate program_update_date
,lead_line_id
,sales_credit_id
,to_number(to_char(close_date, 'J')) opty_effective_time_id
,opty_rank
FROM BIL_BI_OPDTL_STG stg
WHERE (nvl(product_category_id,-999)<> -999)
GROUP BY
lead_id
,lead_line_id
,sales_credit_id
,to_number(to_char(txn_date, 'J'))
,to_number(to_char(effective_date, 'J'))
,to_number(to_char(opty_ld_conversion_date, 'J'))
,to_number(to_char(opty_creation_date, 'J'))
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_global_amt * prim_conversion_rate
,opty_global_amt * CONVERSION_RATE_S
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,to_number(to_char(close_date, 'J'))
,opty_rank) stage
ON (stage.sales_credit_id = fact.sales_credit_id)
WHEN MATCHED THEN
UPDATE SET
fact.txn_time_id = stage.txn_time_id
,fact.opty_close_time_id = stage.opty_close_time_id
,fact.opty_ld_conversion_time_id = stage.opty_ld_conversion_time_id
,fact.opty_creation_time_id = stage.opty_creation_time_id
,fact.sales_credit_amt = stage.sales_credit_amt
,fact.sales_credit_amt_s = stage.sales_credit_amt_s
,fact.product_category_id = stage.product_category_id
,fact.item_id = stage.item_id
,fact.item_organization_id = stage.item_organization_id
,fact.competitor_id = stage.competitor_id
,fact.hdr_source_promotion_id = stage.hdr_source_promotion_id
,fact.customer_id = stage.customer_id
,fact.opty_amt = stage.opty_amt
,fact.opty_amt_s = stage.opty_amt_s
,fact.owner_sales_group_id = stage.owner_sales_group_id
,fact.owner_salesrep_id = stage.owner_salesrep_id
,fact.lead_number = stage.lead_number
,fact.sales_stage_id = stage.sales_stage_id
,fact.status = stage.status
,fact.sales_group_id = stage.sales_group_id
,fact.salesrep_id = stage.salesrep_id
,fact.win_probability = stage.win_probability
,fact.open_status_flag = stage.opp_open_status_flag
,fact.win_loss_indicator = stage.win_loss_indicator
,fact.forecast_rollup_flag = stage.forecast_rollup_flag
,fact.last_updated_by = stage.last_updated_by
,fact.last_update_date = stage.last_update_date
,fact.last_update_login = stage.last_update_login
,fact.request_id = stage.request_id
,fact.program_application_id = stage.program_application_id
,fact.program_id = stage.program_id
,fact.program_update_date = stage.program_update_date
,fact.lead_line_id = stage.lead_line_id
,fact.opty_effective_time_id = stage.opty_effective_time_id
,fact.opty_rank = stage.opty_rank
WHERE
(fact.opty_close_time_id <> stage.opty_close_time_id) OR
(fact.opty_ld_conversion_time_id <> stage.opty_ld_conversion_time_id) OR
(fact.opty_creation_time_id <> stage.opty_creation_time_id) OR
(fact.sales_credit_amt <> stage.sales_credit_amt) OR
(fact.sales_credit_amt_s <> stage.sales_credit_amt_s) OR
(fact.product_category_id <> stage.product_category_id) OR
(fact.item_id <> stage.item_id) OR
(fact.item_organization_id <> stage.item_organization_id) OR
(fact.competitor_id <> stage.competitor_id) OR
(fact.hdr_source_promotion_id <> stage.hdr_source_promotion_id) OR
(fact.customer_id <> stage.customer_id) OR
(fact.opty_amt <> stage.opty_amt) OR
(fact.opty_amt_s <> stage.opty_amt_s) OR
(fact.owner_sales_group_id <> stage.owner_sales_group_id) OR
(fact.owner_salesrep_id <> stage.owner_salesrep_id) OR
(fact.lead_number <> stage.lead_number) OR
(fact.sales_stage_id <> stage.sales_stage_id) OR
(fact.status <> stage.status) OR
(fact.sales_group_id <> stage.sales_group_id) OR
(fact.salesrep_id <> stage.salesrep_id) OR
(fact.win_probability <> stage.win_probability) OR
(fact.open_status_flag <> stage.opp_open_status_flag) OR
(fact.win_loss_indicator <> stage.win_loss_indicator) OR
(fact.forecast_rollup_flag <> stage.forecast_rollup_flag) OR
(fact.lead_line_id <> stage.lead_line_id) OR
(fact.opty_effective_time_id <> stage.opty_effective_time_id) OR
(fact.opty_rank <> stage.opty_rank)
DELETE WHERE (to_date(stage.opty_close_time_id,'J')
INSERT INTO BIL_BI_OPDTL_F sumry (
opty_id
,txn_time_id
,opty_close_time_id
,opty_ld_conversion_time_id
,opty_creation_time_id
,sales_credit_amt
,sales_credit_amt_s
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_amt
,opty_amt_s
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_line_id
,sales_credit_id
) SELECT
lead_id
,to_number(to_char(txn_date, 'J'))
,to_number(to_char(effective_date, 'J'))
,to_number(to_char(opty_ld_conversion_date, 'J'))
,to_number(to_char(opty_creation_date, 'J'))
,SUM(decode(sales_credit_amount*prim_conversion_rate, 0, null, sales_credit_amount*prim_conversion_rate))
,SUM(decode(sales_credit_amount*CONVERSION_RATE_S, 0, null, sales_credit_amount*CONVERSION_RATE_S))
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_global_amt * prim_conversion_rate
,opty_global_amt * CONVERSION_RATE_S
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,g_user_id
,sysdate
,g_user_id
,sysdate
,G_Login_Id
,G_request_id
,G_appl_id
,G_program_id
,sysdate
,lead_line_id
,sales_credit_id
FROM BIL_BI_OPDTL_STG stg
WHERE (nvl(product_category_id,-999)<> -999)
AND effective_date <= l_limit_date
GROUP BY
lead_id
,lead_line_id
,sales_credit_id
,to_number(to_char(txn_date, 'J'))
,to_number(to_char(effective_date, 'J'))
,to_number(to_char(opty_ld_conversion_date, 'J'))
,to_number(to_char(opty_creation_date, 'J'))
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_global_amt * prim_conversion_rate
,opty_global_amt * CONVERSION_RATE_S
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
;
p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');
END Insert_Into_Sumry_Incr;
Procedure Insert_Into_Sumry_Init
IS
l_sysdate DATE ;
l_proc := 'Insert_Into_Sumry_Init';
INSERT /*+ APPEND PARALLEL(sumry)*/ INTO BIL_BI_OPDTL_F sumry
(
opty_id
,txn_time_id
,opty_close_time_id
,opty_ld_conversion_time_id
,opty_creation_time_id
,sales_credit_amt
,sales_credit_amt_s
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_amt
,opty_amt_s
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_line_id
,sales_credit_id
,opty_effective_time_id
,opty_rank
)
SELECT /*+ PARALLEL(stg)*/
lead_id
,to_number(to_char(txn_date, 'J'))
,to_number(to_char(effective_date, 'J'))
,to_number(to_char(opty_ld_conversion_date, 'J'))
,to_number(to_char(opty_creation_date, 'J'))
,SUM(decode(sales_credit_amount* prim_conversion_rate, 0, null, sales_credit_amount* prim_conversion_rate))
,SUM(decode(sales_credit_amount* CONVERSION_RATE_S, 0, null, sales_credit_amount* CONVERSION_RATE_S))
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_global_amt * prim_conversion_rate
,opty_global_amt * CONVERSION_RATE_S
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,g_user_id
,sysdate
,g_user_id
,sysdate
,G_Login_Id
,G_request_id
,G_appl_id
,G_program_id
,sysdate
,lead_line_id
,sales_credit_id
,to_number(to_char(close_date, 'J'))
,opty_rank
FROM BIL_BI_OPDTL_STG stg
WHERE ( nvl(product_category_id,-999)<> -999)
GROUP BY
lead_id
,lead_line_id
,sales_credit_id
,to_number(to_char(txn_date, 'J'))
,to_number(to_char(effective_date, 'J'))
,to_number(to_char(opty_ld_conversion_date, 'J'))
,to_number(to_char(opty_creation_date, 'J'))
,product_category_id
,item_id
,item_organization_id
,competitor_id
,hdr_source_promotion_id
,customer_id
,opty_global_amt * prim_conversion_rate
,opty_global_amt * CONVERSION_RATE_S
,owner_sales_group_id
,owner_salesrep_id
,lead_number
,sales_stage_id
,status
,sales_group_id
,salesrep_id
,win_probability
,opp_open_status_flag
,win_loss_indicator
,forecast_rollup_flag
,to_number(to_char(close_date, 'J'))
,opty_rank;
p_msg => 'Inserted '||g_row_num||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');
END Insert_Into_Sumry_Init;
USING ( SELECT
txn_currency,
effective_date,
decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) rate,
decode(txn_currency,g_sec_currency,1,fii_currency.get_global_rate_secondary(txn_currency,
trunc(least(sysdate,effective_date)))) rate_s
FROM (SELECT /*+ parallel(stg) */ DISTINCT
txn_currency ,
effective_date
FROM BIL_BI_OPDTL_STG stg
)
) rates
ON
( rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.rate,
sumry.exchange_rate_s = rates.rate_s
WHEN NOT MATCHED THEN
INSERT (sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate,
sumry.exchange_rate_s)
VALUES (
rates.txn_currency,
rates.effective_date,
rates.rate,
rates.rate_s);
USING ( SELECT
txn_currency,
effective_date,
decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) rate,
decode(txn_currency,g_sec_currency,1,fii_currency.get_global_rate_secondary(txn_currency,
trunc(least(sysdate,effective_date)))) rate_s
FROM (SELECT DISTINCT txn_currency,
effective_date
FROM BIL_BI_OPDTL_STG stg
)
) rates
ON
( rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.rate,
sumry.exchange_rate_s = rates.rate_s
WHEN NOT MATCHED THEN
INSERT (sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate,
sumry.exchange_rate_s)
VALUES (
rates.txn_currency,
rates.effective_date,
rates.rate,
rates.rate_s
);
USING ( SELECT
txn_currency,
effective_date,
decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) rate
FROM (SELECT /*+ parallel(stg) */ DISTINCT
txn_currency ,
effective_date
FROM BIL_BI_OPDTL_STG stg
)
) rates
ON
( rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.rate
WHEN NOT MATCHED THEN
INSERT (sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate)
VALUES (
rates.txn_currency,
rates.effective_date,
rates.rate);
USING ( SELECT
txn_currency,
effective_date,
decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) rate
FROM (SELECT DISTINCT txn_currency,
effective_date
FROM BIL_BI_OPDTL_STG stg
)
) rates
ON
( rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.rate
WHEN NOT MATCHED THEN
INSERT (sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate)
VALUES (
rates.txn_currency,
rates.effective_date,
rates.rate);
p_msg => 'Inserted '||sql%rowcount||' into BIL_BI_CURRENCY_RATE table');