[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_From_Stg( ERRBUF IN OUT NOCOPY VARCHAR2
,RETCODE IN OUT NOCOPY VARCHAR2
);
PROCEDURE Insert_Into_Stg
(
p_mode IN VARCHAR2
);
BIS_COLLECTION_UTILITIES.deleteLogForObject (g_obj_name);
SELECT
COUNT(*)
INTO
l_stg_cnt
FROM BIL_BI_NEW_FST_ID
WHERE ROWNUM < 2;
set g_* with resume type and proceed to insert into the stg and pfi tables
-----------------------------------------------------------------------------
*/
IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
bil_bi_util_collection_pkg.writeLog
(
p_log_level => fnd_log.LEVEL_STATEMENT,
p_module => g_pkg || l_proc ,
p_msg =>
'g_* variables before exchange = g_end_date,g_start_date,g_mode,'||
' => '||g_end_date||','||g_start_date||','||','||g_mode
);
SELECT
MIN(submission_date) start_date,
MAX(submission_date) end_date,
MAX(collect_mode) collect_mode
INTO
g_start_date,
g_end_date,
l_temp_collect_mode
FROM
BIL_BI_NEW_FST_ID;
g_phase := 'Resume: Insert into staging';
Insert_into_Stg
(
g_mode
);
Insert_From_Stg
(
ERRBUF => ERRBUF
,RETCODE => RETCODE
);
g_phase := 'RESUME: Completed insert into fact';
Insert_into_Stg
(
g_mode
);
Insert_From_Stg
(
ERRBUF => ERRBUF
,RETCODE => RETCODE
);
p_msg => 'Inserting in INITIAL mode'
);
INSERT /*+ PARALLEL(nfst) */ INTO BIL_BI_NEW_FST_ID nfst
(
record_id,
forecast_id,
currency_code,
submission_date,
collect_mode,
period_name
)
SELECT /*+ USE_HASH(aif) PARALLEL(aif) PARALLEL(glp) */
rownum,
aif.forecast_id,
aif.currency_code,
aif.submission_date,
l_collect_mode,
aif.period_name
FROM
as_internal_forecasts aif,
gl_periods glp
WHERE
aif.submission_date BETWEEN p_start_date AND p_end_date
AND aif.status_code = 'SUBMITTED'
AND glp.period_set_name = g_cal
AND glp.period_name = aif.period_name
AND glp.period_type = g_fsct_per_type;
p_msg => 'Inserting in Incremental mode'
);
INSERT INTO BIL_BI_NEW_FST_ID
(
record_id,
forecast_id,
currency_code,
submission_date,
collect_mode,
period_name
)
SELECT
ROWNUM,
aif.forecast_id,
aif.currency_code,
aif.submission_date,
l_collect_mode,
aif.period_name
FROM
as_internal_forecasts aif,
gl_periods glp
WHERE
NOT EXISTS (SELECT forecast_id FROM bil_bi_processed_fst_id bpfi
WHERE aif.forecast_id = bpfi.forecast_id)
AND aif.submission_date BETWEEN P_Start_Date AND P_End_Date
AND aif.status_code = 'SUBMITTED'
AND glp.period_set_name = g_cal
AND glp.period_name = aif.period_name
AND glp.period_type = g_fsct_per_type;
p_msg => 'Inserted '||l_number_of_rows||' forecast IDs into BIL_BI_NEW_FST_ID');
SELECT
currency_code,
submission_date,
DECODE(currency_code,g_prim_currency,1,
fii_currency.get_global_rate_primary(currency_code,trunc(least(sysdate, submission_date)))) prate,
DECODE(g_sec_currency,NULL,NULL,DECODE(currency_code,g_sec_currency,1,
fii_currency.get_global_rate_secondary(currency_code,trunc(least(sysdate, submission_date))))) srate
FROM
(
SELECT /*+ PARALLEL(nfi) */
DISTINCT currency_code currency_code,
TRUNC(submission_date) submission_date
FROM
bil_bi_new_fst_id nfi
)
) rates
ON
(
rates.currency_code = sumry.currency_code
AND rates.submission_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET
sumry.exchange_rate = rates.prate,
sumry.EXCHANGE_RATE_S = rates.srate
WHEN NOT MATCHED THEN
INSERT
(
sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate,
sumry.exchange_rate_s
)
VALUES
(
rates.currency_code,
rates.submission_date,
rates.prate,
rates.srate
);
* PROCEDURE Insert_Into_Stg
* This procedure is used to insert a range of forecasts in to the staging table.
**************************************************************************************/
PROCEDURE Insert_Into_Stg
(
p_mode IN VARCHAR2
) IS
l_number_of_rows NUMBER;
l_proc := 'Insert_Into_Stg.';
p_msg => 'insert into staging - initial'
);
INSERT ALL
/*+ PARALLEL(bil_bi_fst_dtl_stg) */ INTO BIL_BI_FST_DTL_STG
(
Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
,forecast_period_day,forecast_period_week,forecast_period_period,forecast_period_quarter,forecast_period_year
,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,valid_flag,functional_currency
,Primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt,opp_forecast_amt_s
)
VALUES
(
Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
,forecast_period_day,forecast_period_week,forecast_period_Period,forecast_period_quarter,forecast_period_year
,sales_group_id,salesrep_id,adjusted_amt_p,adjusted_amt_s,valid_flag,functional_currency
,primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
adjusted_opp_forecast_amt_p,adjusted_opp_forecast_amt_s
)
/*+ PARALLEL(bil_bi_processed_fst_id) */ INTO bil_bi_processed_fst_id
(
creation_date,created_by,last_update_date,last_updated_by,LAST_UPDATE_LOGIN,Txn_Day
,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,functional_currency
,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt,opp_forecast_amt_s
)
VALUES
(
SYSDATE,G_user_id,SYSDATE,G_user_id,g_login_id,Txn_Day
,sales_group_id,salesrep_id,forecast_amt_p,forecast_amt_s,functional_currency
,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt_p,opp_forecast_amt_s
)
(
SELECT
/*+ PARALLEL(aif) PARALLEL(bnfi) PARALLEL(asfc)
USE_HASH(aif) USE_HASH(bnfi) USE_HASH(asfc) */
to_char(submission_date, 'J') txn_DAY,
to_number(NULL, 999) txn_WEEK,
to_number(NULL, 999) txn_PERIOD,
to_number(NULL, 999) txn_QUARTER,
to_number(NULL, 999) txn_YEAR,
to_number(NULL,999) forecast_period_day,
to_number(NULL,999)forecast_period_week,
to_number(NULL,999)forecast_period_period,
to_number(NULL,999)forecast_period_quarter,
to_number(NULL,999)forecast_period_year,
sales_group_id,
salesforce_id salesrep_id,
forecast_amt_p,
forecast_amt_s,
adjusted_amt_p,
adjusted_amt_s,
'T' valid_flag,
currency_code functional_currency,
NULL primary_conversion_rate,
product_category_id,
credit_type_id,
period_name,
submission_date,
forecast_id,
opp_forecast_amt_p,
opp_forecast_amt_s,
adjusted_opp_forecast_amt_p,
adjusted_opp_forecast_amt_s
FROM
(
SELECT
forecast_id,
submission_date,
sales_group_id,
salesforce_id,
product_category_id,
period_name,
currency_code,
credit_type_id,
forecast_amount,
forecast_amt_p,
forecast_amt_s,
forecast_amt_p-NVL(lag_forecast_amt_p,0) adjusted_amt_p,
forecast_amt_s-NVL(lag_forecast_amt_s,0) adjusted_amt_s,
opp_forecast_amt_p,
opp_forecast_amt_s,
opp_forecast_amt_p-NVL(lag_opp_forecast_amt_p,0) adjusted_opp_forecast_amt_p,
opp_forecast_amt_s-NVL(lag_opp_forecast_amt_s,0) adjusted_opp_forecast_amt_s
FROM
(
SELECT
forecast_id,
submission_date,
sales_group_id,
salesforce_id,
product_category_id,
period_name,
currency_code,
credit_type_id,
forecast_amount,
forecast_amt_p,
forecast_amt_s,
opp_forecast_amt_p,
opp_forecast_amt_s,
LAG((forecast_amt_p)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
,period_name,credit_type_id ORDER BY submission_date ASC) lag_forecast_amt_p,
LAG((forecast_amt_s)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
,period_name,credit_type_id ORDER BY submission_date ASC) lag_forecast_amt_s,
LAG((opp_forecast_amt_p)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
,period_name,credit_type_id ORDER BY submission_date ASC) lag_opp_forecast_amt_p,
LAG((opp_forecast_amt_s)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
,period_name,credit_type_id ORDER BY submission_date ASC) lag_opp_forecast_amt_s
FROM
(
SELECT
/*+
PARALLEL(aif) PARALLEL(bnfi) PARALLEL(asfc) PARALLEL(rates)
USE_HASH(aif) USE_HASH(bnfi) USE_HASH(asfc) USE_HASH(rates)
*/
aif.forecast_id,
aif.submission_date,
aif.sales_group_id,
aif.salesforce_id,
asfc.product_category_id,
aif.period_name,
aif.currency_code,
aif.credit_type_id,
aif.forecast_amount,
aif.forecast_amount*rates.exchange_rate forecast_amt_p,
aif.forecast_amount*rates.exchange_rate_s forecast_amt_s,
NULL opp_forecast_amt_p,
NULL opp_forecast_amt_s
FROM
as_internal_forecasts aif,
bil_bi_new_fst_id bnfi,
as_fst_sales_categories asfc,
bil_bi_currency_rate rates
WHERE
aif.forecast_id = bnfi.forecast_id
AND aif.status_code = 'SUBMITTED'
AND aif.submission_date >= g_start_date
AND aif.submission_date <= LEAST(g_end_date,(g_asn_date-(1/(24*60*60))))
AND NVL(aif.FORECAST_AMOUNT_FLAG,'Y') = 'Y'
AND aif.forecast_category_id = asfc.forecast_category_id
AND NVL(asfc.end_date_active,SYSDATE) >= SYSDATE
AND asfc.start_date_active <= SYSDATE
AND TRUNC(aif.submission_date) = rates.exchange_date
AND aif.currency_code = rates.currency_code
AND product_category_id IS NOT NULL
AND aif.forecast_category_id IN
(
SELECT
afsc1.forecast_category_id
FROM
as_fst_sales_categories afsc1
WHERE
NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
AND afsc1.start_date_active <= SYSDATE
AND NOT(NVL(interest_type_id,-1)<0 AND product_category_id IS NULL)
GROUP BY
afsc1.forecast_category_id
HAVING COUNT(1) = 1
)
UNION ALL
SELECT
/*+
PARALLEL(aif) PARALLEL(bnfi) PARALLEL(asfc) PARALLEL(apwsl)
USE_HASH(aif) USE_HASH(bnfi) USE_HASH(asfc) USE_HASH(apwsl)
*/
aif.forecast_id,
aif.submission_date,
aif.sales_group_id,
aif.salesforce_id,
apwsl.product_category_id,
aif.period_name,
aif.currency_code,
aif.credit_type_id,
apwsl.forecast_amount,
apwsl.forecast_amount*rates.exchange_rate forecast_amt_p,
apwsl.forecast_amount*rates.exchange_rate_s forecast_amt_s,
apwsl.opp_forecast_amount*rates.exchange_rate opp_forecast_amt_p,
apwsl.opp_forecast_amount*rates.exchange_rate_s opp_forecast_amt_s
FROM
as_internal_forecasts aif,
as_prod_worksheet_lines apwsl,
bil_bi_new_fst_id bnfi,
bil_bi_currency_rate rates
WHERE
aif.forecast_id = bnfi.forecast_id
AND aif.status_code = 'SUBMITTED'
AND apwsl.status_code = 'SUBMITTED'
AND aif.submission_date >= GREATEST(g_start_date,g_asn_date)
AND aif.submission_date <= g_end_date
AND nvl(aif.FORECAST_AMOUNT_FLAG,'Y') = 'Y'
AND aif.forecast_id = apwsl.forecast_id
AND TRUNC(aif.submission_date) = rates.exchange_date
AND aif.currency_code = rates.currency_code
)
)
)
);
p_msg => 'insert into staging - incremental'
);
INSERT ALL
INTO BIL_BI_FST_DTL_STG
(
Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
,forecast_period_day,forecast_period_week,forecast_period_period,forecast_period_quarter,forecast_period_year
,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,valid_flag,functional_currency
,Primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt,opp_forecast_amt_s
)
VALUES
(
Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
,forecast_period_day,forecast_period_week,forecast_period_Period,forecast_period_quarter,forecast_period_year
,sales_group_id,salesrep_id,forecast_amt_p,forecast_amt_s,valid_flag,functional_currency
,primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt_p,opp_forecast_amt_s
)
INTO bil_bi_processed_fst_id
(
creation_date,created_by,last_update_date,last_updated_by,LAST_UPDATE_LOGIN,Txn_Day
,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,functional_currency
,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt,opp_forecast_amt_s
)
VALUES
(
SYSDATE,G_user_id,SYSDATE,G_user_id,g_login_id,Txn_Day
,sales_group_id,salesrep_id,forecast_amt_p,forecast_amt_s,functional_currency
,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
opp_forecast_amt_p,opp_forecast_amt_s
)
(
SELECT
to_char(submission_date, 'J') txn_DAY,
to_number(NULL, 999) txn_WEEK,
to_number(NULL, 999) txn_PERIOD,
to_number(NULL, 999) txn_QUARTER,
to_number(NULL, 999) txn_YEAR,
to_number(NULL,999) forecast_period_day,
to_number(NULL,999)forecast_period_week,
to_number(NULL,999)forecast_period_period,
to_number(NULL,999)forecast_period_quarter,
to_number(NULL,999)forecast_period_year,
sales_group_id,
salesforce_id salesrep_id,
forecast_amt_p,
forecast_amt_s,
'T' valid_flag,
currency_code functional_currency,
NULL primary_conversion_rate,
product_category_id,
credit_type_id,
period_name,
submission_date,
forecast_id,
opp_forecast_amt_p,
opp_forecast_amt_s
FROM
(
SELECT
forecast_id,
submission_date,
sales_group_id,
salesforce_id,
product_category_id,
period_name,
currency_code,
credit_type_id,
forecast_amount,
forecast_amt_p,
forecast_amt_s,
opp_forecast_amt_p,
opp_forecast_amt_s
FROM
(
SELECT
aif.forecast_id,
aif.submission_date,
aif.sales_group_id,
aif.salesforce_id,
apwsl.product_category_id,
aif.period_name,
aif.currency_code,
aif.credit_type_id,
apwsl.forecast_amount,
apwsl.forecast_amount*rates.exchange_rate forecast_amt_p,
apwsl.forecast_amount*rates.exchange_rate_s forecast_amt_s,
NVL(apwsl.opp_forecast_amount,0)*rates.exchange_rate opp_forecast_amt_p,
NVL(apwsl.opp_forecast_amount,0)*rates.exchange_rate_s opp_forecast_amt_s
FROM
as_internal_forecasts aif,
as_prod_worksheet_lines apwsl,
bil_bi_new_fst_id bnfi,
bil_bi_currency_rate rates
WHERE
aif.forecast_id = bnfi.forecast_id
AND aif.status_code = 'SUBMITTED'
AND apwsl.status_code = 'SUBMITTED'
AND aif.submission_date >= g_start_date
AND aif.submission_date <= g_end_date
AND nvl(aif.FORECAST_AMOUNT_FLAG,'Y') = 'Y'
AND aif.forecast_id = apwsl.forecast_id
AND TRUNC(aif.submission_date) = rates.exchange_date
AND aif.currency_code = rates.currency_code
AND NOT EXISTS
(
SELECT 1
FROM bil_bi_processed_fst_id bpfi
WHERE bpfi.forecast_id = aif.forecast_id
)
)
)
);
p_msg => ' Rows Inserted into Staging + Processed fst id Table: '||l_number_of_rows
);
END Insert_Into_Stg;
l_select VARCHAR2(3000);
* Here Using period start and end dates of all the start and end periods, the forecasts are updated accordingly.
* Need to determine logic here.
*/
-- use dynamic sql since this query is run once per collection, so
-- should not cause too manu soft parse.
CASE
WHEN g_map_ent_per_type = 'FII_TIME_WEEK' THEN
BEGIN
NULL;
l_select := '
Txn_Day
,Txn_Week
,Txn_Period
,Txn_Quarter
,Txn_Year ,
to_number(NULL,999) DAY ,
to_number(NULL,999) week ,
to_number(NULL,999) Period ,
fep.ENT_QTR_ID,
fep.ENT_YEAR_ID,
sales_group_id ,
salesrep_id ,
sum(forecast_amt) forecast_amt,
sum(forecast_amt_s) forecast_amt_s,
sum(opp_forecast_amt) opp_forecast_amt,
sum(opp_forecast_amt_s) opp_forecast_amt_s,
''T'' ,
''NA'' ,
1,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID';
l_select := '
Txn_Day
,Txn_Week
,Txn_Period
,Txn_Quarter
,Txn_Year ,
to_number(NULL,999) DAY ,
to_number(NULL,999) week ,
to_number(NULL,999) Period ,
to_number(NULL,999) Quarter ,
fep.ENT_YEAR_ID,
sales_group_id ,
salesrep_id ,
sum(forecast_amt) forecast_amt,
sum(forecast_amt_s) forecast_amt_s,
sum(opp_forecast_amt) opp_forecast_amt,
sum(opp_forecast_amt_s) opp_forecast_amt_s,
''T'' ,
''NA'' ,
1,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID';
l_stmt := ' INSERT /*+ PARALLEL(stg1) */ into BIL_BI_FST_DTL_STG stg1 (';
l_stmt := ' INSERT into BIL_BI_FST_DTL_STG (';
l_stmt := l_stmt || ' (SELECT /*+ PARALLEL(stg) PARALLEL(fep) */' ;
l_stmt := l_stmt || ' (SELECT ';
l_select ||
l_from ||
l_where || ' GROUP BY
stg.Txn_Day,
stg.Txn_Week,
stg.Txn_Period,
stg.Txn_Quarter,
stg.Txn_Year,
stg.sales_group_id,
stg.SALESREP_ID,
stg.PRODUCT_CATEGORY_ID,
stg.CREDIT_TYPE_ID,'
|| l_rollup || ')';
p_msg => 'Inserted ' || l_cnt || ' rows of aggregated (based on fst period) data into summary table'
);
INSERT /*+ PARALLEL(stg1) */ INTO BIL_BI_FST_DTL_STG stg1(
Txn_DAY
,Txn_Week
,Txn_Period
,Txn_Quarter
,Txn_Year
,FORECAST_PERIOD_DAY
,FORECAST_PERIOD_WEEK
,FORECAST_PERIOD_Period
,FORECAST_PERIOD_Quarter
,FORECAST_PERIOD_Year
, SALES_GROUP_ID
, SALESREP_ID
,FORECAST_AMT
,forecast_amt_s
,OPP_FORECAST_AMT
,opp_forecast_amt_s
,VALID_FLAG
,functional_currency
,Primary_Conversion_Rate
,PRODUCT_CATEGORY_ID
,CREDIT_TYPE_ID )
SELECT /*+ PARALLEL(stg) PARALLEL(fday) */
to_number(NULL,999),
fday.week_id,
fday.ent_period_id,
fday.ENT_QTR_ID,
fday.ENT_YEAR_ID,
FORECAST_PERIOD_DAY
,FORECAST_PERIOD_WEEK
,FORECAST_PERIOD_Period
,FORECAST_PERIOD_Quarter
,FORECAST_PERIOD_Year ,
sales_group_id ,
salesrep_id ,
sum(forecast_amt),
sum(forecast_amt_s),
sum(opp_forecast_amt),
sum(opp_forecast_amt_s),
'T' ,
'NA' ,
1,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID
FROM BIL_BI_FST_DTL_STG stg,
FII_TIME_Day fday
WHERE stg.txn_day = fday.report_date_julian
GROUP BY
stg.FORECAST_PERIOD_DAY
,stg.FORECAST_PERIOD_WEEK
,stg.FORECAST_PERIOD_Period
,stg.FORECAST_PERIOD_Quarter
,stg.FORECAST_PERIOD_Year,
stg.sales_group_id,
stg.SALESREP_ID,
stg.PRODUCT_CATEGORY_ID,
stg.CREDIT_TYPE_ID,
grouping sets((fday.ENT_Year_ID,
fday.ENT_Qtr_ID,fday.ent_period_id,fday.week_id),(fday.ENT_Year_ID,
fday.ENT_Qtr_ID,fday.ent_period_id), (fday.ENT_Year_ID,
fday.ENT_Qtr_ID), fday.ENT_Year_ID);
INSERT into BIL_BI_FST_DTL_STG (
Txn_DAY
,Txn_Week
,Txn_Period
,Txn_Quarter
,Txn_Year
,FORECAST_PERIOD_DAY
,FORECAST_PERIOD_WEEK
,FORECAST_PERIOD_Period
,FORECAST_PERIOD_Quarter
,FORECAST_PERIOD_Year
, SALES_GROUP_ID
, SALESREP_ID
,FORECAST_AMT
,forecast_amt_s
,OPP_FORECAST_AMT
,opp_forecast_amt_s
,VALID_FLAG
,functional_currency
,Primary_Conversion_Rate
,PRODUCT_CATEGORY_ID
,CREDIT_TYPE_ID )
SELECT
to_number(NULL,999),
fday.week_id,
fday.ent_period_id,
fday.ENT_QTR_ID,
fday.ENT_YEAR_ID,
FORECAST_PERIOD_DAY
,FORECAST_PERIOD_WEEK
,FORECAST_PERIOD_Period
,FORECAST_PERIOD_Quarter
,FORECAST_PERIOD_Year ,
sales_group_id ,
salesrep_id ,
sum(forecast_amt),
sum(forecast_amt_s),
sum(opp_forecast_amt),
sum(opp_forecast_amt_s),
'T' ,
'NA' ,
1,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID
FROM BIL_BI_FST_DTL_STG stg,
FII_TIME_Day fday
WHERE stg.txn_day = fday.report_date_julian
GROUP BY
stg.FORECAST_PERIOD_DAY
,stg.FORECAST_PERIOD_WEEK
,stg.FORECAST_PERIOD_Period
,stg.FORECAST_PERIOD_Quarter
,stg.FORECAST_PERIOD_Year,
stg.sales_group_id,
stg.SALESREP_ID,
stg.PRODUCT_CATEGORY_ID,
stg.CREDIT_TYPE_ID,
grouping sets((fday.ENT_Year_ID,
fday.ENT_Qtr_ID,fday.ent_period_id,fday.week_id),(fday.ENT_Year_ID,
fday.ENT_Qtr_ID,fday.ent_period_id), (fday.ENT_Year_ID,
fday.ENT_Qtr_ID), fday.ENT_Year_ID);
p_msg => 'Inserted ' || l_cnt || ' rows of aggregated (based on time) data into summary table');
PROCEDURE Insert_From_Stg( ERRBUF IN OUT NOCOPY VARCHAR2
,RETCODE IN OUT NOCOPY VARCHAR2
)
IS
l_number_of_rows number;
l_proc := 'Insert_From_Stg.';
INSERT /*+ PARALLEL(fact) */ into BIL_BI_FST_DTL_F fact
(TXN_TIME_ID,
TXN_PERIOD_TYPE_ID,
FORECAST_TIME_ID,
FORECAST_PERIOD_TYPE_ID,
SALES_GROUP_ID,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SALESREP_ID,
FORECAST_AMT,
forecast_amt_s,
opp_forecast_amt,
opp_forecast_amt_s,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SECURITY_GROUP_ID)
(select
STAGE.TXN_TIME_ID ,
STAGE.TXN_PERIOD_TYPE_ID ,
STAGE.FORECAST_TIME_ID ,
STAGE.FORECAST_PERIOD_TYPE_ID,
STAGE.SALES_GROUP_ID,
STAGE.PRODUCT_CATEGORY_ID,
STAGE.CREDIT_TYPE_ID,
sysdate,
g_user_id ,
sysdate,
g_user_id ,
g_login_id ,
STAGE.SALESREP_ID,
STAGE.AMOUNT,
STAGE.SEC_AMOUNT,
stage.opp_amount,
stage.sec_opp_amount,
G_request_id,
G_appl_id,
G_program_id,
sysdate,
NULL FROM (select /*+ PARALLEL (stg) */
SUM(forecast_amt) AMOUNT,
SUM(forecast_amt_s) SEC_AMOUNT,
SUM(opp_forecast_amt) opp_amount,
SUM(opp_forecast_amt_s) sec_opp_amount,
(CASE WHEN txn_day IS NOT NULL THEN txn_day
WHEN Txn_Week IS NOT NULL THEN Txn_Week
WHEN Txn_Period IS NOT NULL THEN Txn_Period
WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
WHEN Txn_Year IS NOT NULL THEN Txn_Year END) TXN_TIME_ID,
(CASE WHEN txn_day IS NOT NULL THEN 1
WHEN Txn_Week IS NOT NULL THEN 16
WHEN Txn_Period IS NOT NULL THEN 32
WHEN Txn_Quarter IS NOT NULL THEN 64
WHEN Txn_Year IS NOT NULL THEN 128 END) TXN_PERIOD_TYPE_ID,
(CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END) FORECAST_TIME_ID,
(CASE WHEN forecast_period_week IS NOT NULL THEN 16
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END) FORECAST_PERIOD_TYPE_ID,
SALES_GROUP_ID,
SALESREP_ID,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID
FROM BIL_BI_FST_DTL_STG stg
GROUP BY
(CASE WHEN txn_day IS NOT NULL THEN txn_day
WHEN Txn_Week IS NOT NULL THEN Txn_Week
WHEN Txn_Period IS NOT NULL THEN Txn_Period
WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
WHEN Txn_Year IS NOT NULL THEN Txn_Year END),
(CASE WHEN txn_day IS NOT NULL THEN 1
WHEN Txn_Week IS NOT NULL THEN 16
WHEN Txn_Period IS NOT NULL THEN 32
WHEN Txn_Quarter IS NOT NULL THEN 64
WHEN Txn_Year IS NOT NULL THEN 128 END),
(CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END),
(CASE WHEN forecast_period_week IS NOT NULL THEN 16
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END),
SALES_GROUP_ID,
SALESREP_ID,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID) STAGE);
USING (select
SUM(forecast_amt) AMOUNT,
SUM(forecast_amt_s) SEC_AMOUNT,
SUM(opp_forecast_amt) opp_amount,
SUM(opp_forecast_amt_s) sec_opp_amount,
(CASE WHEN txn_day IS NOT NULL THEN txn_day
WHEN Txn_Week IS NOT NULL THEN Txn_Week
WHEN Txn_Period IS NOT NULL THEN Txn_Period
WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
WHEN Txn_Year IS NOT NULL THEN Txn_Year END) TXN_TIME_ID,
(CASE WHEN txn_day IS NOT NULL THEN 1
WHEN Txn_Week IS NOT NULL THEN 16
WHEN Txn_Period IS NOT NULL THEN 32
WHEN Txn_Quarter IS NOT NULL THEN 64
WHEN Txn_Year IS NOT NULL THEN 128 END) TXN_PERIOD_TYPE_ID,
(CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END) FORECAST_TIME_ID,
(CASE WHEN forecast_period_week IS NOT NULL THEN 16
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END) FORECAST_PERIOD_TYPE_ID,
SALES_GROUP_ID,
SALESREP_ID,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID
FROM BIL_BI_FST_DTL_STG stg
GROUP BY
(CASE WHEN txn_day IS NOT NULL THEN txn_day
WHEN Txn_Week IS NOT NULL THEN Txn_Week
WHEN Txn_Period IS NOT NULL THEN Txn_Period
WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
WHEN Txn_Year IS NOT NULL THEN Txn_Year END),
(CASE WHEN txn_day IS NOT NULL THEN 1
WHEN Txn_Week IS NOT NULL THEN 16
WHEN Txn_Period IS NOT NULL THEN 32
WHEN Txn_Quarter IS NOT NULL THEN 64
WHEN Txn_Year IS NOT NULL THEN 128 END),
(CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END),
(CASE WHEN forecast_period_week IS NOT NULL THEN 16
WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END),
SALES_GROUP_ID,
SALESREP_ID,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID) STAGE
ON (bsum.txn_time_id = stage.txn_time_id AND
bsum.txn_period_type_id = stage.txn_period_type_id AND
bsum.forecast_time_id = stage.forecast_time_id AND
bsum.forecast_period_type_id = stage.forecast_period_type_id AND
bsum.SALES_GROUP_ID = stage.SALES_GROUP_ID AND
nvl(bsum.SALESREP_ID, -999) = nvl(stage.SALESREP_ID, -999) AND
bsum.PRODUCT_CATEGORY_ID = stage.PRODUCT_CATEGORY_ID AND
bsum.CREDIT_TYPE_ID = stage.CREDIT_TYPE_ID
)
WHEN MATCHED THEN UPDATE SET bsum.forecast_amt = (bsum.forecast_amt+ stage.amount)
,bsum.forecast_amt_s = (bsum.forecast_amt_s+ stage.sec_amount)
,bsum.opp_forecast_amt = (bsum.opp_forecast_amt+ stage.opp_amount)
,bsum.opp_forecast_amt_s = (bsum.opp_forecast_amt_s+ stage.sec_opp_amount)
,bsum.LAST_UPDATED_BY = g_user_id
,bsum.LAST_UPDATE_DATE = l_sysdate
,bsum.LAST_UPDATE_LOGIN= G_Login_Id
WHEN NOT MATCHED THEN INSERT
(TXN_TIME_ID ,
TXN_PERIOD_TYPE_ID ,
FORECAST_TIME_ID ,
FORECAST_PERIOD_TYPE_ID ,
SALES_GROUP_ID ,
SALESREP_ID ,
FORECAST_AMT ,
forecast_amt_s ,
OPP_FORECAST_AMT ,
opp_forecast_amt_s ,
PRODUCT_CATEGORY_ID,
CREDIT_TYPE_ID,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
values(
STAGE.TXN_TIME_ID ,
STAGE.TXN_PERIOD_TYPE_ID ,
STAGE.FORECAST_TIME_ID ,
STAGE.FORECAST_PERIOD_TYPE_ID,
STAGE.SALES_GROUP_ID,
STAGE.SALESREP_ID,
stage.amount,
stage.sec_amount,
stage.opp_amount,
stage.sec_opp_amount,
STAGE.PRODUCT_CATEGORY_ID,
STAGE.CREDIT_TYPE_ID,
sysdate,
g_user_id,
sysdate,
g_user_id ,
g_login_id ,
G_request_id,
G_appl_id,
G_program_id,
sysdate);
END Insert_From_Stg;
cursor c5 is select distinct stg.period_name
FROM
BIL_BI_FST_DTL_STG stg
WHERE valid_flag = 'F';
update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
not exists
(select '1' FROM
gl_periods glp,
FII_TIME_WEEK fep
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type );
update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
not exists
(select '1' FROM
gl_periods glp,
FII_TIME_ENT_PERIOD fep
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type );
update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
not exists
(select '1' FROM
gl_periods glp,
FII_TIME_ENT_QTR fep
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type );
update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
not exists
(select '1' FROM
gl_periods glp,
FII_TIME_ENT_YEAR fep
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type );
p_msg => 'updated status bits of ' || sql%rowcount ||' rows,
they will be deleted due to invalid mapping forecast period');
DELETE FROM bil_bi_processed_fst_id WHERE forecast_id IN
(SELECT forecast_id FROM BIL_BI_FST_DTL_STG
WHERE valid_flag = 'F');
p_msg =>'deleted '||SQL%ROWCOUNT||' rows FROM bil_bi_processed_fst_id table due to valid_flag');
DELETE FROM BIL_BI_FST_DTL_STG stg WHERE valid_flag = 'F';
p_msg =>'deleted '||SQL%ROWCOUNT||' rows from BIL_BI_FST_DTL_STG table due to valid_flag');
UPDATE /*+ parallel(stg) */ BIL_BI_FST_DTL_STG stg
SET stg.FORECAST_PERIOD_Week
= (select fep.week_id FROM FII_TIME_WEEK fep , gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
);
UPDATE BIL_BI_FST_DTL_STG stg
SET stg.FORECAST_PERIOD_Week
= (select fep.week_id FROM FII_TIME_WEEK fep , gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
),
(stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
(
SELECT
(stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
(stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
FROM
bil_bi_processed_fst_id pfi
WHERE
pfi.product_category_id=stg.product_category_id
AND pfi.sales_group_id=stg.sales_group_id
AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi.period_name=stg.period_name
AND pfi.credit_type_id=stg.credit_type_id
AND rownum < 2
AND pfi.submission_date=
(
SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
WHERE
pfi1.product_category_id=stg.product_category_id
AND pfi1.sales_group_id=stg.sales_group_id
AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi1.period_name=stg.period_name
AND pfi1.credit_type_id=stg.credit_type_id
AND pfi1.submission_date < stg.submission_date
)
);
UPDATE /*+ parallel(stg) */ BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_PERIOD
= (SELECT fep.ent_period_id FROM FII_TIME_ENT_PERIOD fep,gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
);
UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_PERIOD
= (select fep.ent_period_id FROM FII_TIME_ENT_PERIOD fep , gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
),
(stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
(
SELECT
(stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
(stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
FROM
bil_bi_processed_fst_id pfi
WHERE
pfi.product_category_id=stg.product_category_id
AND pfi.sales_group_id=stg.sales_group_id
AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi.period_name=stg.period_name
AND pfi.credit_type_id=stg.credit_type_id
AND rownum < 2
AND pfi.submission_date=
(
SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
WHERE
pfi1.product_category_id=stg.product_category_id
AND pfi1.sales_group_id=stg.sales_group_id
AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi1.period_name=stg.period_name
AND pfi1.credit_type_id=stg.credit_type_id
AND pfi1.submission_date < stg.submission_date
)
);
UPDATE /*+ parallel(stg) */ BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_QUARTER
= (SELECT fep.ent_qtr_id FROM FII_TIME_ENT_QTR fep,gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
);
UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_QUARTER
= (select fep.ent_qtr_id FROM FII_TIME_ENT_QTR fep , gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
),
(stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
(
SELECT
(stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
(stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
FROM
bil_bi_processed_fst_id pfi
WHERE
pfi.product_category_id=stg.product_category_id
AND pfi.sales_group_id=stg.sales_group_id
AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi.period_name=stg.period_name
AND pfi.credit_type_id=stg.credit_type_id
AND rownum < 2
AND pfi.submission_date=
(
SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
WHERE
pfi1.product_category_id=stg.product_category_id
AND pfi1.sales_group_id=stg.sales_group_id
AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi1.period_name=stg.period_name
AND pfi1.credit_type_id=stg.credit_type_id
AND pfi1.submission_date < stg.submission_date
)
);
UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_YEAR
= (SELECT fep.ent_year_id FROM FII_TIME_ENT_YEAR fep,gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
);
UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_YEAR
= (select fep.ent_year_id FROM FII_TIME_ENT_YEAR fep , gl_periods glp
WHERE
fep.start_date = glp.start_date and
fep.end_date = glp.end_date and
glp.period_set_name = g_cal and
glp.period_name = stg.period_name and
glp.period_type = g_fsct_per_type
),
(stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
(
SELECT
(stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
(stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
FROM
bil_bi_processed_fst_id pfi
WHERE
pfi.product_category_id=stg.product_category_id
AND pfi.sales_group_id=stg.sales_group_id
AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi.period_name=stg.period_name
AND pfi.credit_type_id=stg.credit_type_id
AND rownum < 2
AND pfi.submission_date=
(
SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
WHERE
pfi1.product_category_id=stg.product_category_id
AND pfi1.sales_group_id=stg.sales_group_id
AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
AND pfi1.period_name=stg.period_name
AND pfi1.credit_type_id=stg.credit_type_id
AND pfi1.submission_date < stg.submission_date
)
);
UPDATE
bil_bi_fst_dtl_stg
SET
forecast_amt = forecast_amt_1,forecast_amt_s=forecast_amt_s_1,
opp_forecast_amt=opp_forecast_amt_1,opp_forecast_amt_s=opp_forecast_amt_s_1
WHERE forecast_amt_1 IS NOT NULL;
p_msg => 'secondary currency update done for' || sql%rowcount ||' rows');
SELECT /*+ PARALLEL(rates) */ COUNT(1)
INTO g_conv_rate_cnt
FROM bil_bi_currency_rate rates
WHERE ((exchange_rate < 0 OR exchange_rate is NULL)
OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s is NULL)))
AND exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
AND rownum < 2;
SELECT COUNT(1)
INTO g_conv_rate_cnt
FROM bil_bi_currency_rate
WHERE ((exchange_rate < 0 OR exchange_rate is NULL)
OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s is NULL)))
AND exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
AND rownum < 2;
SELECT MIN(nfi.submission_date),
MAX(nfi.submission_date)
INTO l_stg_min,
l_stg_max
FROM bil_bi_new_fst_id nfi;
SELECT
MIN(glp.start_date),
MAX(glp.end_date)
INTO
l_day_min,
l_day_max
FROM
bil_bi_new_fst_id nfi,
gl_periods glp
WHERE
glp.period_set_name = g_cal
AND glp.period_name = nfi.period_name
AND glp.period_type = g_fsct_per_type;
SELECT
rate.currency_code,
TRUNC(DECODE(rate.exchange_rate,-3,
TO_DATE('01/01/1999','MM/DD/RRRR'), LEAST(SYSDATE,report_date))) report_date,
decode(sign(nvl(rate.exchange_rate,-1)),-1,'P') prim_curr_type,
decode(sign(nvl(rate.exchange_rate_s,-1)),-1,'S') sec_curr_type
FROM
bil_bi_currency_rate rate,
fii_time_day fday
WHERE
rate.exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
AND rate.exchange_date = fday.report_date
AND ((exchange_rate < 0 OR exchange_rate IS NULL)
OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s IS NULL)));
SELECT
afsc1.forecast_category_id,
afsc_tl.forecast_category_name
FROM
as_fst_sales_categories afsc1,
as_forecast_categories_tl afsc_tl
WHERE
afsc1.forecast_category_id = afsc_tl.forecast_category_id
AND afsc_tl.LANGUAGE = userenv('LANG')
AND NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
AND afsc1.start_date_active <= SYSDATE
AND NOT(NVL(interest_type_id,-1)<0 AND product_category_id IS NULL)
GROUP BY afsc1.forecast_category_id,
afsc_tl.forecast_category_name
HAVING COUNT(1) > 1;
SELECT
DISTINCT glp2.period_name
FROM
gl_periods glp1,
gl_periods glp2
WHERE glp1.period_set_name = g_cal
AND glp2.period_set_name = g_asf_calendar
AND glp2.period_name = glp1.period_name
AND (glp1.start_date <> glp2.start_date OR glp1.end_date <> glp2.end_date);
SELECT
COUNT(1)
INTO l_cnt
FROM
as_fst_sales_categories afsc1
WHERE
NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
AND afsc1.start_date_active <= SYSDATE
AND afsc1.product_category_id IS NULL
AND NVL(afsc1.interest_type_id,-1) > 0;
SELECT
afsc1.forecast_category_id,
afsc_tl.forecast_category_name
FROM
as_fst_sales_categories afsc1,
as_forecast_categories_tl afsc_tl
WHERE
afsc1.forecast_category_id = afsc_tl.forecast_category_id
AND afsc_tl.LANGUAGE = userenv('LANG')
and NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
and afsc1.start_date_active <= SYSDATE
and afsc1.product_category_id IS NULL
AND NVL(afsc1.interest_type_id,-1) > 0
GROUP BY
afsc1.forecast_category_id,
afsc_tl.forecast_category_name
)
LOOP
fnd_message.set_name('BIL','BIL_BI_FST_CAT_MAP_ERR_DTL');