[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO BIL_TX_PROD_TMP(attr1,attr2,attr3) VALUES(l_prodcat_id,l_item_id,'C');
INSERT INTO BIL_TX_PROD_TMP(attr1,attr2,attr3) VALUES(l_prodcat_id,l_item_id,'P');
(SELECT a.object_id category_set_id
, a.parent_id
, a.child_id
,tl.description prodcat_name
FROM eni_denorm_hierarchies a
, mtl_default_category_sets b
, BIL_TX_PROD_TMP cat
, mtl_categories_tl tl
WHERE b.functional_area_id = 11
AND b.category_set_id = a.object_id
AND a.object_type = ''CATEGORY_SET''
AND a.parent_id = cat.ATTR1
AND a.oltp_flag = ''Y''
AND cat.ATTR3 = ''C''
AND tl.category_id = cat.ATTR1
AND tl.language = userenv(''LANG'') )' ;
l_outer_cls := 'SELECT prod_dec BIL_TX_MEASURE9
,pipe_opty_amt BIL_TX_MEASURE1
,wat_amnt BIL_TX_MEASURE11
,open_opty_amt BIL_TX_MEASURE2
,won_opty_amt BIL_TX_MEASURE3
,lost_opty_amt BIL_TX_MEASURE4
,no_opp_opty_amt BIL_TX_MEASURE5
,opp_forcast_best_amount BIL_TX_MEASURE6
,opp_forcast_amount BIL_TX_MEASURE7
,opp_forecast_worst_amnt BIL_TX_MEASURE8
,'||l_oppty_url1||' BIL_TX_URL1
,'||l_oppty_url2||' BIL_TX_URL2
,'||l_oppty_url3||' BIL_TX_URL3
,'||l_oppty_url4||' BIL_TX_URL4
,'||l_oppty_url5||' BIL_TX_URL5
,'||l_oppty_url6||' BIL_TX_URL6
,'||l_oppty_url7||' BIL_TX_URL7
,'||l_oppty_url8||' BIL_TX_URL8
,'||l_oppty_url9||' BIL_TX_URL9
,SUM(pipe_opty_amt)OVER() BIL_TX_MEASURE12
,SUM(wat_amnt)OVER() BIL_TX_MEASURE13
,SUM(open_opty_amt)OVER() BIL_TX_MEASURE14
,SUM(won_opty_amt)OVER() BIL_TX_MEASURE15
,SUM(lost_opty_amt)OVER() BIL_TX_MEASURE16
,SUM(no_opp_opty_amt)OVER() BIL_TX_MEASURE17
,SUM(opp_forcast_best_amount)OVER() BIL_TX_MEASURE18
,SUM(opp_forcast_amount)OVER() BIL_TX_MEASURE19
,SUM(opp_forecast_worst_amnt)OVER() BIL_TX_MEASURE20
,DECODE(conversion_status_flag, 0,NULL ,''' || l_err_msg ||''' ) BIL_TX_MEASURE21
FROM ( ' ;
l_comm_sel := ' SELECT
SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''NYY''
THEN ( (sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) open_opty_amt
,SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''WNY''
THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) won_opty_amt
,SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''LNN''
THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) lost_opty_amt
,SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''NNN''
THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) no_opp_opty_amt
,SUM(CASE WHEN win_loss_indicator||forecast_rollup_flag IN (''NY'',''WY'')
THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) pipe_opty_amt
,SUM( nvl( (CASE WHEN win_loss_indicator||forecast_rollup_flag =''WY''
THEN (( (sales_credit_amount * win_probability/100) /NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END),
0) +
nvl( (CASE WHEN win_loss_indicator||forecast_rollup_flag =''NY''
THEN (((sales_credit_amount* win_probability/100)/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE )
ELSE NULL
END),
0)
) wat_amnt
,SUM(CASE WHEN forecast_rollup_flag =''Y''
THEN ((OPP_BEST_FORECAST_AMOUNT/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) opp_forcast_best_amount
,SUM(CASE WHEN forecast_rollup_flag =''Y''
THEN ((OPP_FORECAST_AMOUNT/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) opp_forcast_amount
,SUM(CASE WHEN forecast_rollup_flag =''Y''
THEN ((OPP_WORST_FORECAST_AMOUNT/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
ELSE NULL
END) opp_forecast_worst_amnt
, prod_id
, prod_dec
,SUM(nvl(conversion_status_flag,1)) conversion_status_flag FROM ( ';
l_sgrp_sel := 'SELECT sales_credit_amount,
opp_best_forecast_amount,
opp_forecast_amount,
opp_worst_forecast_amount,
H.parent_id||''.001'' prod_id,
H.prodcat_name prod_dec ,
win_probability,
win_loss_indicator,
forecast_rollup_flag,
opp_open_status_flag,
ascd.decision_date,
ascd.currency_code,
period_set_name,
period_type,
period_name ' ;
l_srep_sel := 'SELECT sales_credit_amount,
opp_best_forecast_amount,
opp_forecast_amount,
opp_worst_forecast_amount,
PROD.ATTR1 ||''.''||PROD.ATTR2 prod_id,
tl.DESCRIPTION prod_dec,
win_probability,
win_loss_indicator,
forecast_rollup_flag,
opp_open_status_flag,
ascd.decision_date,
ascd.currency_code,
period_set_name,
period_type,
period_name ' ;