[Home] [Help]
Skip to content
PACKAGE BODY: APPS.BIL_TX_OPTY_SMRY_RPT_PKG
Source
1 PACKAGE BODY BIL_TX_OPTY_SMRY_RPT_PKG AS
2 /*$Header: biltxosb.pls 120.17 2006/08/23 04:54:05 vselvapr noship $*/
3
4 PROCEDURE OPTY_SMRY_RPT (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
5 ,x_custom_sql OUT NOCOPY VARCHAR2
6 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7 l_custom_rec BIS_QUERY_ATTRIBUTES;
8 l_bind_ctr NUMBER;
9 l_rpt_str VARCHAR2(80);
10 l_viewby VARCHAR2(80) ;
11 l_proc VARCHAR2(100);
12 l_region_id VARCHAR2(50);
13 g_pkg VARCHAR2(100);
14 l_parameter_valid BOOLEAN;
15
16
17 -- sql statement related
18 l_with_cls VARCHAR2(32000);
19 l_outer_cls VARCHAR2(32000);
20 l_outer_cls1 VARCHAR2(32000);
21 l_custom_sql VARCHAR2(32000);
22 l_custom_sql1 VARCHAR2(32000);
23 l_sgrp_frm_cls VARCHAR2(4000);
24 l_srep_frm_cls VARCHAR2(4000);
25 l_where_com_cls VARCHAR2(4000);
26 l_where_period_cls VARCHAR2(4000);
27 l_where_prod_sgrp_cls VARCHAR2(4000);
28 l_where_prod_srep_cls VARCHAR2(4000);
29 l_comm_sel VARCHAR2(4000);
30 l_sgrp_sel VARCHAR2(4000);
31 l_srep_sel VARCHAR2(4000);
32 l_where_dt_cls VARCHAR2(4000);
33 l_grp_by VARCHAR2(4000);
34 l_order_by VARCHAR2(4000);
35 -- parameters
36 l_period_type VARCHAR2(4000);
37 l_from_date DATE;
38 l_to_date DATE;
39 l_forecast_owner VARCHAR2(4000); -- sales credit id needs to be passed I think.
40 l_sales_group VARCHAR2(4000);
41 l_sales_person VARCHAR2(4000);
42 l_source VARCHAR2(4000);
43 l_competitor VARCHAR2(4000);
44 l_win_probability VARCHAR2(4000);
45 l_sales_channel VARCHAR2(4000);
46 l_sales_stage VARCHAR2(4000);
47 l_opty_number VARCHAR2(4000);
48 l_total_opp_amount NUMBER;
49 l_opty_name VARCHAR2(4000);
50 l_customer VARCHAR2(4000);
51 l_opp_status VARCHAR2(4000);
52 l_sls_methodology VARCHAR2(4000);
53 l_product_category VARCHAR2(4000); -- if only prod cat is passed
54 l_item_id VARCHAR2(4000); -- may pass both item and prod.
55 l_to_currency VARCHAR2(4000);
56 l_report_by VARCHAR2(4000);
57 l_close_reason VARCHAR2(4000);
58 l_oppty_url1 VARCHAR2(4000);
59 l_oppty_url2 VARCHAR2(4000);
60 l_oppty_url3 VARCHAR2(4000);
61 l_oppty_url4 VARCHAR2(4000);
62 l_oppty_url5 VARCHAR2(4000);
63 l_oppty_url6 VARCHAR2(4000);
64 l_oppty_url7 VARCHAR2(4000);
65 l_oppty_url8 VARCHAR2(4000);
66 l_oppty_url9 VARCHAR2(4000);
67 l_log_param VARCHAR2(4000);
68 l_from_dt date;
69 l_to_dt date;
70 l_len number;
71 l_to_period_name VARCHAR2(4000);
72 l_frcst_owner VARCHAR2(4000);
73 l_partner VARCHAR2(4000);
74 l_total_opp_amt_opr VARCHAR2(4000);
75 l_win_probability_opr VARCHAR2(4000);
76 l_from_prd_id VARCHAR2(4000);
77 l_schema VARCHAR2(4000);
78 l_period_set_name VARCHAR2(500);
79 l_org_id NUMBER;
80
81
82 ---Product category parsing ---
83
84 l_product_category1 VARCHAR2(4000);
85 l_prodcat_id VARCHAR2(4000);
86
87 -- sorting parameters ---
88 l_order VARCHAR2(4000);
89 l_orderBy VARCHAR2(4000);
90 l_sortBy VARCHAR2(4000);
91
92 l_win_probability_val VARCHAR2(4000);
93
94
95 --- Status code Parameters ---
96
97 l_pip_st VARCHAR2(4000);
98 l_wt_pip_st VARCHAR2(4000);
99 l_open_st VARCHAR2(4000);
100 l_Won_st VARCHAR2(4000);
101 l_lost_st VARCHAR2(4000);
102 l_No_Opp_st VARCHAR2(4000);
103
104 -- Error Message Parameter --
105 l_err_msg VARCHAR2(4000);
106
107
108
109 BEGIN
110
111 -- IF WE want we can get region and function from get page params
112 -- Initializing variables as per new standard
113
114 l_region_id := 'BIL_TX_OPTY_SMRY_RPT';
115 l_parameter_valid := FALSE;
116 l_rpt_str := 'BIL_TX_OPTY_SMRY_RPT';
117 l_proc := 'OPTY_SMRY_RPT.';
118 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_SMRY_RPT_PKG.';
119 l_schema := 'BIL';
120 --Fix for bug 5469370,added replace function
121 l_err_msg := replace(FND_MESSAGE.GET_STRING('BIL','BIL_TX_CUR_CONV_MIS'),'''','''''');
122 l_period_set_name := NVL(FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR'), 'Accounting');
123 l_org_id := fnd_profile.value('ORG_ID');
124
125 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
126 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
127
128 -- FND logging --
129 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE)
130 THEN
131 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
132 p_module => g_pkg || l_proc || 'begin',
133 p_msg => 'Start of Procedure '|| l_proc );
134 END IF;
135
136
137
138 -- Getting the Status Codes based on the Status Flags --
139
140 l_pip_st := BIL_TX_UTIL_RPT_PKG.GET_STATS_CODS_OPTY_FLGS('__Y');
141 l_wt_pip_st := BIL_TX_UTIL_RPT_PKG.GET_STATS_CODS_OPTY_FLGS('__Y');
142 l_open_st := BIL_TX_UTIL_RPT_PKG.GET_STATS_CODS_OPTY_FLGS('NYY');
143 l_Won_st := BIL_TX_UTIL_RPT_PKG.GET_STATS_CODS_OPTY_FLGS('WNY' );
144 l_lost_st := BIL_TX_UTIL_RPT_PKG.GET_STATS_CODS_OPTY_FLGS('LNN');
145 l_No_Opp_st := BIL_TX_UTIL_RPT_PKG.GET_STATS_CODS_OPTY_FLGS('NNN');
146
147
148 -- Page Parameters --
149
150 BIL_TX_UTIL_RPT_PKG.GET_PAGE_PARAMS
151 (p_page_parameter_tbl => p_page_parameter_tbl
152 ,p_region_id => l_region_id
153 ,x_period_type => l_period_type
154 ,x_to_currency => l_to_currency
155 ,x_to_period_name => l_to_period_name
156 ,x_sg_id => l_sales_group
157 ,x_resource_id => l_sales_person
158 ,x_frcst_owner => l_frcst_owner
159 ,x_prodcat_id => l_product_category
160 ,x_item_id => l_item_id
161 ,x_parameter_valid => l_parameter_valid
162 ,x_viewby => l_viewby
163 ,x_order => l_order
164 ,x_rptby => l_report_by
165 ,x_sls_chnl => l_sales_channel
166 ,x_sls_stge => l_sales_stage
167 ,x_opp_status => l_opp_status
168 ,x_source => l_source
169 ,x_sls_methodology => l_sls_methodology
170 ,x_win_probability => l_win_probability
171 ,x_win_probability_opr => l_win_probability_opr
172 ,x_close_reason => l_close_reason
173 ,x_competitor => l_competitor
174 ,x_opty_number => l_opty_number
175 ,x_total_opp_amount => l_total_opp_amount
176 ,x_total_opp_amt_opr => l_total_opp_amt_opr
177 ,x_opty_name => l_opty_name
178 ,x_customer => l_customer
179 ,x_partner => l_partner
180 ,x_from_date => l_from_date
181 ,x_to_date => l_to_date);
182
183
184
185 l_from_date := BIL_TX_UTIL_RPT_PKG.GET_FROM_DATE(l_to_period_name);
186 l_to_date := BIL_TX_UTIL_RPT_PKG.GET_TO_DATE(l_to_period_name);
187
188
189
190 -- Getting the Product category parameter --
191 -- This loop to be removed once Util Pkg is modified
192 -- to pass PC as multiple values
193
194 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last
195 LOOP
196
197 IF p_page_parameter_tbl(i).parameter_name = 'PROD_CAT+CAT'
198 THEN
199 l_product_category := p_page_parameter_tbl(i).parameter_id;
200
201 END IF;
202
203 IF p_page_parameter_tbl(i).parameter_name = 'TO_PRD+TO'
204 THEN
205 l_from_prd_id := REPLACE(p_page_parameter_tbl(i).parameter_id,'''');
206
207 END IF;
208
209 END LOOP;
210
211
212
213
214
215 --- Drill across Links --
216
217 l_oppty_url1 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y'||
218 '&BIL_TX_STATUS='||l_pip_st||'&BIL_TX_FROM_PRD='|| l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
219 l_oppty_url2 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y'||
220 '&BIL_TX_STATUS='||l_wt_pip_st||'&BIL_TX_FROM_PRD='|| l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
221 l_oppty_url3 :='''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y'||
222 '&BIL_TX_STATUS='||l_open_st||'&BIL_TX_FROM_PRD='|| l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
223 l_oppty_url4 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y'||
224 '&BIL_TX_STATUS='||l_Won_st||'&BIL_TX_FROM_PRD='|| l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
225 l_oppty_url5 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y'||
226 '&BIL_TX_STATUS='||l_lost_st||'&BIL_TX_FROM_PRD='|| l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
227 l_oppty_url6 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y'||
228 '&BIL_TX_STATUS='||l_No_Opp_st||'&BIL_TX_FROM_PRD='|| l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
229 l_oppty_url7 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y&BIL_TX_FROM_PRD=' || l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
230 l_oppty_url8 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y&BIL_TX_FROM_PRD=' || l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
231 l_oppty_url9 := '''pFunctionName=BIL_TX_OPTY_LIST_RPT_R&pForceRun=Y&pParameters=pParamIds&BIL_TX_PROD_CAT=''||prod_id||''&pParamIds=Y&BIL_TX_FROM_PRD=' || l_from_prd_id||'&BIL_TX_TO_PRD='|| l_from_prd_id||'''';
232
233
234
235
236 --- Logging of Parameters --
237
238 l_log_param := 'l_period_type =>' ||l_period_type|| ' , '||
239 'l_to_period_name =>' ||l_to_period_name || ' , '||
240 'l_to_currency=>' || l_to_currency|| ' , '||
241 'l_sales_group =>'|| l_sales_group|| ' , '||
242 'l_sales_person =>'|| l_sales_person|| ' , '||
243 'l_product_category =>'|| l_product_category|| ' , '||
244 'l_item_id =>'|| l_item_id|| ' , '||
245 'l_viewby =>'|| l_viewby|| ' , '||
246 'l_report_by =>'|| l_report_by|| ' , '||
247 'l_sales_channel =>'|| l_sales_channel|| ' , '||
248 'l_sales_stage =>'|| l_sales_stage|| ' , '||
249 'l_sls_methodology =>'|| l_sls_methodology|| ' , '||
250 'l_win_probability =>'|| l_win_probability|| ' , '||
251 'l_win_probability_opr =>'|| l_win_probability_opr || ' , '||
252 'l_from_date =>'|| l_from_date|| ' , '||
253 'l_to_date =>'|| l_to_date|| ' , '||
254 'l_order =>'||l_order|| ' , '||
255 ' l_orderBy =>'||l_orderBy|| ' , '||
256 ' l_sortBy =>'||l_sortBy|| ' , '||
257 'l_frcst_owner =>' ||l_frcst_owner;
258
259 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE)
260 THEN
261 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
262 p_module => g_pkg || l_proc,
263 p_msg => ' Params are =>'||l_log_param );
264 END IF;
265
266
267
268 IF l_order IS NULL THEN
269
270 l_order := ' NLSSORT(BIL_TX_MEASURE9, ''NLS_SORT=BINARY'') ';
271
272 END IF;
273
274
275 --deletion of records from the Global Temporary Table --
276
277 EXECUTE IMMEDIATE 'TRUNCATE TABLE'||' '||l_schema||'.'||'BIL_TX_PROD_TMP ';
278
279
280 --- product category parsing -----
281
282 IF l_product_category IS NOT NULL
283 THEN
284 BEGIN
285 LOOP
286 l_len := instr(l_product_category,',');
287
288 IF l_len <> 0 THEN
289 l_product_category1:= substr(l_product_category,1, l_len-1) ;
290 ELSE
291 l_product_category1 := l_product_category;
292 END IF;
293
294 l_prodcat_id := l_product_category1;
295
296 BIL_TX_UTIL_RPT_PKG.PARSE_PRODCAT_ITEM_ID(l_prodcat_id,l_item_id) ;
297
298 IF l_item_id is null THEN
299 INSERT INTO BIL_TX_PROD_TMP(attr1,attr2,attr3) VALUES(l_prodcat_id,l_item_id,'C');
300 ELSE
301 INSERT INTO BIL_TX_PROD_TMP(attr1,attr2,attr3) VALUES(l_prodcat_id,l_item_id,'P');
302 END IF;
303
304 l_product_category := substr(l_product_category, l_len+1);
305
306 EXIT WHEN l_len = 0;
307
308 END LOOP;
309
310 END;
311 END IF;
312
313
314 -- With clause for fetching the Product Categories --
315
316 l_with_cls := 'WITH
317 child_pc AS
318 (SELECT a.object_id category_set_id
319 , a.parent_id
320 , a.child_id
321 ,tl.description prodcat_name
322 FROM eni_denorm_hierarchies a
323 , mtl_default_category_sets b
324 , BIL_TX_PROD_TMP cat
325 , mtl_categories_tl tl
326 WHERE b.functional_area_id = 11
327 AND b.category_set_id = a.object_id
328 AND a.object_type = ''CATEGORY_SET''
329 AND a.parent_id = cat.ATTR1
330 AND a.oltp_flag = ''Y''
331 AND cat.ATTR3 = ''C''
332 AND tl.category_id = cat.ATTR1
333 AND tl.language = userenv(''LANG'') )' ;
334
335
336
337 /*** Query column mapping ******************************************************
338
339 * BIL_TX_MEASURE1 - Pipeline
340 * BIL_TX_MEASURE2 - Open
341 * BIL_TX_MEASURE3 - Won
342 * BIL_TX_MEASURE4 - Lost
346 * BIL_TX_MEASURE8 - Forecast Worst
343 * BIL_TX_MEASURE5 - No Opportunity
344 * BIL_TX_MEASURE6 - Forecast Best
345 * BIL_TX_MEASURE7 - Forecast Forecast
347 * BIL_TX_MEASURE9 - Product Cat Desc
348 * BIL_TX_MEASURE11 - Weighted Pipeline
349 * BIL_TX_URL1 - Pipeline Url
350 * BIL_TX_URL2 - Weighted Pipeline Url
351 * BIL_TX_URL3 - Open Url
352 * BIL_TX_URL4 - Won Url
353 * BIL_TX_URL5 - Lost Url
354 * BIL_TX_URL6 - No Opportunity Url
355 * BIL_TX_URL7 - Forecast - Best Url
356 * BIL_TX_URL8 - Forecast Url
357 * BIL_TX_URL9 - Forecast - Worst Url
358 * BIL_TX_MEASURE12 - Grand Total(Pipeline)
359 * BIL_TX_MEASURE13 - Grand Total(Weighted Pipeline)
360 * BIL_TX_MEASURE14 - Grand Total(Open)
361 * BIL_TX_MEASURE15 - Grand Total(Won)
362 * BIL_TX_MEASURE16 - Grand Total(Lost)
363 * BIL_TX_MEASURE17 - Grand Total(No Opportunity)
364 * BIL_TX_MEASURE18 - Grand Total(Forecast Best)
365 * BIL_TX_MEASURE19 - Grand Total(Forecast)
366 * BIL_TX_MEASURE20 - Grand Total(Forecast Worst)
367 * BIL_TX_MEASURE21 - Error Message
368
369 *******************************************************************************/
370
371
372 -- Outer Select Statement --
373
374 l_outer_cls := 'SELECT prod_dec BIL_TX_MEASURE9
375 ,pipe_opty_amt BIL_TX_MEASURE1
376 ,wat_amnt BIL_TX_MEASURE11
377 ,open_opty_amt BIL_TX_MEASURE2
378 ,won_opty_amt BIL_TX_MEASURE3
379 ,lost_opty_amt BIL_TX_MEASURE4
380 ,no_opp_opty_amt BIL_TX_MEASURE5
381 ,opp_forcast_best_amount BIL_TX_MEASURE6
382 ,opp_forcast_amount BIL_TX_MEASURE7
383 ,opp_forecast_worst_amnt BIL_TX_MEASURE8
384 ,'||l_oppty_url1||' BIL_TX_URL1
385 ,'||l_oppty_url2||' BIL_TX_URL2
386 ,'||l_oppty_url3||' BIL_TX_URL3
387 ,'||l_oppty_url4||' BIL_TX_URL4
388 ,'||l_oppty_url5||' BIL_TX_URL5
389 ,'||l_oppty_url6||' BIL_TX_URL6
390 ,'||l_oppty_url7||' BIL_TX_URL7
391 ,'||l_oppty_url8||' BIL_TX_URL8
392 ,'||l_oppty_url9||' BIL_TX_URL9
393 ,SUM(pipe_opty_amt)OVER() BIL_TX_MEASURE12
394 ,SUM(wat_amnt)OVER() BIL_TX_MEASURE13
395 ,SUM(open_opty_amt)OVER() BIL_TX_MEASURE14
396 ,SUM(won_opty_amt)OVER() BIL_TX_MEASURE15
397 ,SUM(lost_opty_amt)OVER() BIL_TX_MEASURE16
398 ,SUM(no_opp_opty_amt)OVER() BIL_TX_MEASURE17
399 ,SUM(opp_forcast_best_amount)OVER() BIL_TX_MEASURE18
400 ,SUM(opp_forcast_amount)OVER() BIL_TX_MEASURE19
401 ,SUM(opp_forecast_worst_amnt)OVER() BIL_TX_MEASURE20
402 ,DECODE(conversion_status_flag, 0,NULL ,''' || l_err_msg ||''' ) BIL_TX_MEASURE21
403
404 FROM ( ' ;
405
406
407
408 -- Sales Group Query From clause --
409
410 l_sgrp_frm_cls := ' FROM as_sales_credits_denorm ascd
411 ,child_pc h
412 ,jtf_rs_group_usages usg1
413 ,as_period_days p ';
414
415
416 -- Sales Rep Query From clause --
417
418
419 l_srep_frm_cls := ' FROM as_sales_credits_denorm ascd
420 ,bil_tx_prod_tmp prod
421 ,mtl_system_items_tl tl
422 ,jtf_rs_group_usages usg1
423 ,as_period_days p ';
424 -- Report By --
425
426 IF instr(l_report_by ,'1') > 0
427 THEN
428 l_where_dt_cls := ' ascd.decision_date ' ;
429 ELSIF instr(l_report_by,'2') > 0
430 THEN
431 l_where_dt_cls := ' NVL(ascd.forecast_date , ascd.decision_date) ' ;
432 END IF;
433
434 -- Common where clause for Sales Group and Sales Rep --
435
436 l_where_com_cls := ' WHERE '||l_where_dt_cls ||' between :l_from_date and :l_to_date '||
437 ' AND ascd.SALES_GROUP_ID IN ( &ORGANIZATION+JTF_ORG_SALES_GROUP )
438 AND ascd.salesforce_id = &SLS_PRSON+PERSON
439 AND ascd.SALES_GROUP_ID = usg1.group_id
440 AND usg1.usage = ''SALES''
441 AND P.period_day = ascd.decision_date
442 AND p.period_type = &PRD_TYPE+TYPE
443 AND p.period_set_name = :l_period_set_name ';
444
445
446 -- Sales Methodolgy --
447 IF l_sls_methodology IS NOT NULL THEN
448 l_where_com_cls := l_where_com_cls || ' AND SALES_METHODOLOGY_ID = &METHODOLOGY+METH ' ;
449 END IF ;
450
451 -- Sales Stage --
452 IF l_sales_stage IS NOT NULL THEN
453 l_where_com_cls := l_where_com_cls || ' AND SALES_STAGE_ID = &SLS_STAGE+STAGE ' ;
454 END IF ;
455
456 -- Sales Channel --
457 IF l_sales_channel IS NOT NULL THEN
458 l_where_com_cls := l_where_com_cls || ' AND CHANNEL_CODE IN (&SLS_CHNL+CHNL) ' ;
459 END IF ;
460
461 IF l_sales_channel IS NULL THEN
462 l_where_com_cls := l_where_com_cls ||
463 ' AND channel_code = flvl.lookup_code '|| -- channel code
464 ' AND trunc(nvl(flvl.start_date_active, SYSDATE)) <= trunc(SYSDATE)'|| -- channel code
468 ' AND flvl.lookup_type = ''SALES_CHANNEL'' '|| -- channel code
465 ' AND trunc(nvl(flvl.end_date_active, SYSDATE)) >= trunc(SYSDATE) '|| -- channel code
466 ' AND flvl.enabled_flag = ''Y'' '|| -- channel code
467 ' AND flvl.language = USERENV(''LANG'') '|| -- channel code
469 ' AND flvl.view_application_id = 660 '; -- channel code
470
471 l_sgrp_frm_cls := l_sgrp_frm_cls || ' ,fnd_lookup_values flvl ' ; -- for channel code
472 l_srep_frm_cls := l_srep_frm_cls || ' ,fnd_lookup_values flvl ' ; -- for channel code
473
474 END IF ;
475
476 -- Win Probability --
477 IF l_win_probability IS NOT NULL THEN
478 l_where_com_cls := l_where_com_cls || ' AND ascd.WIN_PROBABILITY '|| l_win_probability_opr ||' :l_win_probability ';
479 END IF;
480
481
482 -- ForeCast Type
483 IF l_frcst_owner IS NOT NULL THEN
484 l_where_com_cls := l_where_com_cls || ' AND ascd.CREDIT_TYPE_ID = &FRCST_ONER+ONER ';
485 END IF;
486
487 -- Period Where clause --
488
489 l_where_period_cls := ' AND p.period_name = m.period_name
490 AND p.period_set_name = m.period_set_name
491 AND p.period_type = m.period_type
492 AND m.to_currency = &CURRENCY+CURR
493 AND p.period_type = &PRD_TYPE+TYPE
494 AND p.period_set_name = :l_period_set_name
495 AND m.from_currency = qry.currency_code
496 AND p.period_day = qry.decision_date';
497
498 -- Producat category criteria for Sales Group --
499
500
501 l_where_prod_sgrp_cls := ' AND h.child_id = ascd.product_category_id
502 AND h.category_set_id = ascd.product_cat_set_id ';
503
504 -- Producat category criteria for Sales Rep --
505
506
507 l_where_prod_srep_cls := ' AND prod.ATTR1 = ascd.product_category_id
508 AND prod.ATTR2 = ascd.item_id
509 AND tl.INVENTORY_ITEM_ID = prod.ATTR2
510 AND tl.ORGANIZATION_ID = :l_org_id
511 AND tl.language = userenv(''LANG'')
512 AND prod.ATTR3 = ''P''';
513
514
515
516 -- Common Select statment for fetching the Measures based on the flags --
517
518 l_comm_sel := ' SELECT
519 SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''NYY''
520 THEN ( (sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
521 ELSE NULL
522 END) open_opty_amt
523 ,SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''WNY''
524 THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
525 ELSE NULL
526 END) won_opty_amt
527 ,SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''LNN''
528 THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
529 ELSE NULL
530 END) lost_opty_amt
531 ,SUM(CASE WHEN win_loss_indicator||opp_open_status_flag||forecast_rollup_flag =''NNN''
532 THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
533 ELSE NULL
534 END) no_opp_opty_amt
535 ,SUM(CASE WHEN win_loss_indicator||forecast_rollup_flag IN (''NY'',''WY'')
536 THEN ((sales_credit_amount/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
537 ELSE NULL
538 END) pipe_opty_amt
539 ,SUM( nvl( (CASE WHEN win_loss_indicator||forecast_rollup_flag =''WY''
540 THEN (( (sales_credit_amount * win_probability/100) /NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
541 ELSE NULL
542 END),
543 0) +
544 nvl( (CASE WHEN win_loss_indicator||forecast_rollup_flag =''NY''
545 THEN (((sales_credit_amount* win_probability/100)/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE )
546 ELSE NULL
547 END),
548 0)
549 ) wat_amnt
550 ,SUM(CASE WHEN forecast_rollup_flag =''Y''
551 THEN ((OPP_BEST_FORECAST_AMOUNT/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
552 ELSE NULL
553 END) opp_forcast_best_amount
554 ,SUM(CASE WHEN forecast_rollup_flag =''Y''
555 THEN ((OPP_FORECAST_AMOUNT/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
556 ELSE NULL
557 END) opp_forcast_amount
558 ,SUM(CASE WHEN forecast_rollup_flag =''Y''
559 THEN ((OPP_WORST_FORECAST_AMOUNT/NVL(m.DENOMINATOR_RATE,1))*m.NUMERATOR_RATE)
560 ELSE NULL
561 END) opp_forecast_worst_amnt
562 , prod_id
563 , prod_dec
564 ,SUM(nvl(conversion_status_flag,1)) conversion_status_flag FROM ( ';
565
566
567 -- Selected columns for the Sales Group --
568
569 l_sgrp_sel := 'SELECT sales_credit_amount,
570 opp_best_forecast_amount,
571 opp_forecast_amount,
572 opp_worst_forecast_amount,
573 H.parent_id||''.001'' prod_id,
574 H.prodcat_name prod_dec ,
575 win_probability,
576 win_loss_indicator,
577 forecast_rollup_flag,
578 opp_open_status_flag,
579 ascd.decision_date,
580 ascd.currency_code,
581 period_set_name,
582 period_type,
583 period_name ' ;
584
585 -- Selected columns for the Sales Rep --
586
587 l_srep_sel := 'SELECT sales_credit_amount,
588 opp_best_forecast_amount,
589 opp_forecast_amount,
590 opp_worst_forecast_amount,
591 PROD.ATTR1 ||''.''||PROD.ATTR2 prod_id,
592 tl.DESCRIPTION prod_dec,
593 win_probability,
594 win_loss_indicator,
595 forecast_rollup_flag,
596 opp_open_status_flag,
597 ascd.decision_date,
598 ascd.currency_code,
599 period_set_name,
600 period_type,
601 period_name ' ;
602
603 -- Group by --
604
605 l_grp_by := ' group by prod_id , prod_dec ' ;
606
607
608
609 l_outer_cls1 := ' )q, as_period_rates m
610 where
611 m.period_name(+) = q.period_name
612 AND m.period_set_name(+) = q.period_set_name
613 AND m.period_type(+) = q.period_type
614 AND m.to_currency(+) = &CURRENCY+CURR
615 AND m.from_currency(+) = q.currency_code
616 group by prod_id , prod_dec
617 )' ;
618
619
620
621
622 -- Sales Group level final Query --
623
624 l_custom_sql := l_sgrp_sel || ' '|| l_sgrp_frm_cls ||' '|| l_where_com_cls||l_where_prod_sgrp_cls ;
625
626 -- Sales Rep level final Query --
627
628 l_custom_sql1 := l_srep_sel || ' '|| l_srep_frm_cls ||' '|| l_where_com_cls||l_where_prod_srep_cls;
629
630
631 x_custom_sql := l_with_cls|| ' '||l_outer_cls || ' '|| l_comm_sel || ' ' ||l_custom_sql || 'union all ' || l_custom_sql1 ||' ' || l_outer_cls1|| ' ' || ' order by ' || l_order ;
632
633 -- Parameters binding --
634
635 l_bind_ctr := 1;
636 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
637 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
638 l_custom_rec.attribute_value := l_viewby;
639 x_custom_attr.Extend();
640 x_custom_attr(l_bind_ctr):=l_custom_rec;
641 l_bind_ctr:=l_bind_ctr+1;
642
643 l_custom_rec.attribute_name :=':l_from_date';
644 l_custom_rec.attribute_value :=TO_CHAR(l_from_date,'DD/MM/YYYY') ;
645 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
646 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
647 x_custom_attr.Extend();
648 x_custom_attr(l_bind_ctr):=l_custom_rec;
649 l_bind_ctr:=l_bind_ctr+1;
650
651 l_custom_rec.attribute_name :=':l_to_date';
652 l_custom_rec.attribute_value :=TO_CHAR(l_to_date,'dd/MM/yyyy') ;
653 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
654 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
655 x_custom_attr.Extend();
656 x_custom_attr(l_bind_ctr):=l_custom_rec;
657 l_bind_ctr:=l_bind_ctr+1;
658
659 l_custom_rec.attribute_name :=':l_win_probability';
660 l_custom_rec.attribute_value :=l_win_probability;
661 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
662 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
663 x_custom_attr.Extend();
664 x_custom_attr(l_bind_ctr):=l_custom_rec;
665 l_bind_ctr:=l_bind_ctr+1;
666
667 l_custom_rec.attribute_name :=':l_win_probability_opr';
668 l_custom_rec.attribute_value :=l_win_probability_opr;
669 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
670 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
671 x_custom_attr.Extend();
672 x_custom_attr(l_bind_ctr):=l_custom_rec;
673 l_bind_ctr:=l_bind_ctr+1;
674
675 l_custom_rec.attribute_name :=':l_period_set_name';
676 l_custom_rec.attribute_value :=l_period_set_name;
677 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
678 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
679 x_custom_attr.Extend();
680 x_custom_attr(l_bind_ctr):=l_custom_rec;
681 l_bind_ctr:=l_bind_ctr+1;
682
683
684 l_custom_rec.attribute_name :=':l_org_id';
685 l_custom_rec.attribute_value :=l_org_id;
686 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
687 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
688 x_custom_attr.Extend();
689 x_custom_attr(l_bind_ctr):=l_custom_rec;
690 l_bind_ctr:=l_bind_ctr+1;
691
692
693 l_custom_rec.attribute_name :=':l_viewby';
694 l_custom_rec.attribute_value := l_viewby;
695 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
696 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
697 x_custom_attr.Extend();
698 x_custom_attr(l_bind_ctr):=l_custom_rec;
699 l_bind_ctr:=l_bind_ctr+1;
700
701 END OPTY_SMRY_RPT;
702
703 END BIL_TX_OPTY_SMRY_RPT_PKG;
704