[Home] [Help]
PACKAGE BODY: APPS.BIL_TX_OPTY_AGE_RPT_PKG
Source
1 PACKAGE BODY BIL_TX_OPTY_AGE_RPT_PKG AS
2 /* $Header: biltxoab.pls 120.31 2006/08/23 04:54:50 vselvapr noship $ */
3
4 PROCEDURE OPTY_AGE_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
8
9
10 l_custom_rec BIS_QUERY_ATTRIBUTES;
11 l_bind_ctr NUMBER;
12 l_rpt_str VARCHAR2(80);
13 l_viewby VARCHAR2(80) ;
14 l_proc VARCHAR2(100);
15 l_region_id VARCHAR2(50);
16 g_pkg VARCHAR2(100);
17 l_parameter_valid BOOLEAN;
18 -- sql statement related
19 l_custom_sql VARCHAR2(32000);
20 l_outer_select VARCHAR2(4000);
21 l_outer_select0 VARCHAR2(4000);
22 l_select_1 VARCHAR2(15000);
23 l_from VARCHAR2(15000);
24 l_insert_stmnt VARCHAR2(4000);
25 l_where_clause_1 VARCHAR2(4000);
26 l_where_clause_2 VARCHAR2(4000);
27 l_group_by VARCHAR2(4000);
28 l_order_by VARCHAR2(1000);
29 -- parameters
30 l_period_type VARCHAR2(100);
31 l_from_time_id VARCHAR2(100);
32 l_to_time_id VARCHAR2(100);
33 l_sales_group VARCHAR2(4000);
34 l_sales_group_flag VARCHAR2(1);
35 l_sales_person VARCHAR2(4000);
36 l_sales_person_flag VARCHAR2(1);
37 l_opty_name VARCHAR2(500);
38
39 l_customer VARCHAR2(400);
40 l_customer_flag VARCHAR2(1);
41 l_source VARCHAR2(4000);
42 l_source_flag VARCHAR2(4000);
43 l_opp_status VARCHAR2(4000);
44 l_opp_status_flag VARCHAR2(1);
45 l_sales_channel VARCHAR2(4000);
46 l_sales_channel_flag VARCHAR2(1);
47 l_sales_stage VARCHAR2(4000);
48 l_sls_methodology VARCHAR2(100);
49 l_product_category VARCHAR2(4000); -- if only prod cat is passed
50 l_product_category_flag VARCHAR2(1); -- if only prod cat is passed
51 l_item_id VARCHAR2(4000); -- may pass both item and prod.
52 l_item_id_flag VARCHAR2(1); -- may pass both item and prod.
53 l_partner VARCHAR2(4000);
54 l_partner_id VARCHAR2(500);
55 l_to_currency VARCHAR2(500);
56 l_report_by VARCHAR2(500);
57 l_close_reason VARCHAR2(500);
58 l_competitor VARCHAR2(100);
59 l_opty_number VARCHAR2(100);
60 l_convsersion_type VARCHAR2(100);
61 l_period_set_name VARCHAR2(100);
62 l_from_date DATE;
63 l_to_date DATE;
64 l_total_opp_amount VARCHAR2(100);
65 l_total_opp_amt_opr VARCHAR2(100);
66 l_oppty_url1 VARCHAR2(1000);
67 l_customer_url2 VARCHAR2(1000);
68 l_url VARCHAR2(1000);
69 l_credit_type_id NUMBER;
70 seq NUMBER;
71 CNT NUMBER;
72 des VARCHAR2(100);
73 l_status_days VARCHAR2(4000);
74 l_total_days VARCHAR2(4000);
75 l_measure_outer VARCHAR2(4000);
76 type t_stats_c is ref cursor;
77 l_status_c t_stats_c;
78 l_sales_team_access VARCHAR2(100);
79 l_to_period_name VARCHAR2(100);
80 l_ok VARCHAR2(1);
81 l_status VARCHAR2(1000);
82 l_asn_Table BIS_MAP_TBL;
83 l_order VARCHAR2(100);
84 l_win_probability VARCHAR2(100);
85 l_win_probability_opr VARCHAR2(100);
86 l_cur_conv_missing VARCHAR2(1000);
87 l_win_prob NUMBER(3);
88 rc number;
89 l_log_param VARCHAR2(4000);
90
91
92
93
94 BEGIN
95
96 -- IF WE want we can get region and function from get page params
97 -- Initializing variables as per new standard
98
99 l_region_id := 'BIL_TX_OPTY_AGE_RPT';
100 l_parameter_valid := FALSE;
101 l_rpt_str := 'BIL_TX_OPTY_AGE_RPT_R';
102 l_proc := 'OPTY_AGE_RPT.';
103 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_AGE_RPT_PKG.';
104 l_convsersion_type := FND_PROFILE.VALUE('CRMBIS:GL_CONVERSION_TYPE');
105 l_period_set_name := FND_PROFILE.VALUE('CRMBIS:PERIOD_SET_NAME');
106 l_credit_type_id := FND_PROFILE.VALUE('ASN_FRCST_CREDIT_TYPE_ID');
107 --Fix for bug 5469370,added replace function
108 l_cur_conv_missing := replace(FND_MESSAGE.GET_STRING('BIL','BIL_TX_CUR_CONV_MIS'),'''','''''');
109 l_select_1 := NULL;
110 l_from := NULL;
111 l_where_clause_1 := NULL;
112 l_where_clause_2 := NULL;
113
114
115
116
117 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
118 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
119
120
121
122 l_oppty_url1 := '''pFunctionName=ASN_OPPTYDETPG&addBreadCrumb=Y&ASNReqFrmOpptyId=''||ascd.lead_number';
123
124 l_customer_url2 := '''pFunctionName=ASN_CUSTDETGWAYPG&ASNReqAcsErrInDlg=Y&addBreadCrumb=Y&ASNReqFrmCustId=''||ascd.CUSTOMER_ID ';
125
126 BIL_TX_UTIL_RPT_PKG.GET_PAGE_PARAMS
127 (p_page_parameter_tbl => p_page_parameter_tbl
128 ,p_region_id => l_region_id
129 ,x_period_type => l_period_type
130 ,x_to_currency => l_to_currency
131 ,x_to_period_name => l_to_period_name
132 ,x_sg_id => l_sales_group
133 ,x_resource_id => l_sales_person
134 ,x_frcst_owner => l_sales_team_access
135 ,x_prodcat_id => l_product_category
136 ,x_item_id => l_item_id
137 ,x_parameter_valid => l_parameter_valid
138 ,x_viewby => l_viewby
139 ,x_order => l_order
140 ,x_rptby => l_report_by
141 ,x_sls_chnl => l_sales_channel
142 ,x_sls_stge => l_sales_stage
143 ,x_opp_status => l_opp_status
144 ,x_source => l_source
145 ,x_sls_methodology => l_sls_methodology
146 ,x_win_probability => l_win_probability
147 ,x_win_probability_opr => l_win_probability_opr
148 ,x_close_reason => l_close_reason
149 ,x_competitor => l_competitor
150 ,x_opty_number => l_opty_number
151 ,x_total_opp_amount => l_total_opp_amount
152 ,x_total_opp_amt_opr => l_total_opp_amt_opr
153 ,x_opty_name => l_opty_name
154 ,x_customer => l_customer
155 ,x_partner => l_partner_id
156 ,x_from_date => l_from_date
157 ,x_to_date => l_to_date);
158
159 /*
160 l_log_param := 'l_period_type =>' ||l_period_type|| ' , '||
161 'l_to_period_name =>' ||l_to_period_name || ' , '||
162 'l_to_currency=>' || l_to_currency|| ' , '||
163 'l_sales_group =>'|| l_sales_group|| ' , '||
164 'l_sales_person =>'|| l_sales_person|| ' , '||
165 'l_product_category =>'|| l_product_category|| ' , '||
166 'l_item_id =>'|| l_item_id|| ' , '||
167 'l_viewby =>'|| l_viewby|| ' , '||
168 'l_report_by =>'|| l_report_by|| ' , '||
169 'l_sales_channel =>'|| l_sales_channel|| ' , '||
170 'l_sales_stage =>'|| l_sales_stage|| ' , '||
171 'l_sls_methodology =>'|| l_sls_methodology|| ' , '||
172 'l_win_probability =>'|| l_win_probability|| ' , '||
173 'l_win_probability_opr =>'|| l_win_probability_opr || ' , '||
174 'l_from_date =>'|| l_from_date|| ' , '||
175 'l_to_date =>'|| l_to_date;
176 */
177
178
179
180
181
182 /*** Query column mapping ******************************************************
183 -- OUTER MOST SELECT IS NEEDED ONLY FOR THIS REPORT
184
185 Lead_id BIL_TX_MEASURE1
186 Opportunity Name BIL_TX_MEASURE2
187 Customer ID BIL_TX_MEASURE3
188 Customer Name BIL_TX_MEASURE4
189 Sales Group ID BIL_TX_MEASURE5
190 Sales Group Name BIL_TX_MEASURE6
191 Sales Person BIL_TX_MEASURE7
192 Win Probability % BIL_TX_MEASURE8
193 Total Opportunity Amount BIL_TX_MEASURE9
194 Status BIL_TX_MEASURE10
195 Close Date BIL_TX_MEASURE11
196 Days BIL_TX_MEASURE12
197 To Close BIL_TX_MEASURE13
198 Since Creation BIL_TX_MEASURE14
199 Past Close BIL_TX_MEASURE15
200 Days in Status BIL_TX_MEASURE16
201 Total Days BIL_TX_MEASURE21
202 LEAD_NUMBER BIL_TX_MEASURE24
203 CHANNEL_CODE BIL_TX_MEASURE25
204 OPPORTUNITY_LAST_UPDATE_DATE BIL_TX_MEASURE26
205 OPPORTUNITY_LAST_UPDATED_NAME BIL_TX_MEASURE27
206 OPPORTUNITY_CREATION_DATE BIL_TX_MEASURE28
207 OPPORTUNITY_CREATED_NAME BIL_TX_MEASURE29
208 CUSTOMER_CATEGORY BIL_TX_MEASURE30
209 WEIGHTED_AMOUNT BIL_TX_MEASURE31
210 CLOSE_REASON_MEANING BIL_TX_MEASURE32
211 Missing Currency Bubble Text BIL_TX_MEASURE33
212 Missing Currency Bubble Text BIL_TX_MEASURE34
213 */
214
215
216 -- This method was suggested by Seema. Getting the same results as the oroginal method.
217
218 l_asn_Table := BIS_MAP_TBL();
219 BIL_TX_UTIL_RPT_PKG.days_in_status (p_page_parameter_tbl,l_asn_Table);
220
221 IF l_asn_table IS NOT NULL AND l_asn_table.COUNT > 0 THEN
222 FOR i IN l_asn_table.first..l_asn_table.last LOOP
223 --Fix for bug 5469370,added replace function
224 l_status := replace(l_asn_table(i).value,'''','''''');
225 l_status_days := l_status_days||', AVG(DECODE (UPPER(ss.status_code),UPPER( '''|| l_status ||'''),ss.status_days)) BIL_TX_MEASURE16_B'||i ;
226
227 l_total_days := l_total_days || 'NVL(BIL_TX_MEASURE16_B'||i||',0)+';
228 l_measure_outer := l_measure_outer ||'BIL_TX_MEASURE16_B'||i||',';
229 END LOOP;
230 l_total_days := ' '||substr(l_total_days,1,INSTR(l_total_days,'+',-1)-1);
231 -- l_total_days := 'DECODE('||l_total_days||',0,NULL,'||l_total_days||')';
232 l_measure_outer := ', '||substr(l_measure_outer,1,INSTR(l_measure_outer,',',-1)-1);
233
234 END IF;
235
236
237 l_outer_select := 'SELECT BIL_TX_MEASURE1, BIL_TX_MEASURE2, BIL_TX_MEASURE3, '||
238 ' BIL_TX_MEASURE4, BIL_TX_MEASURE5, BIL_TX_MEASURE6, '||
239 ' BIL_TX_MEASURE7, BIL_TX_MEASURE8, BIL_TX_MEASURE9, BIL_TX_MEASURE10, ' ||
240 ' BIL_TX_MEASURE11, BIL_TX_MEASURE13 , '||
241 ' BIL_TX_MEASURE14, BIL_TX_MEASURE15 ' ||l_measure_outer||
242 ','||l_total_days||' BIL_TX_MEASURE21,'||
243 ' BIL_TX_MEASURE24, BIL_TX_MEASURE25, BIL_TX_MEASURE26, BIL_TX_MEASURE27, '||
244 ' BIL_TX_MEASURE28, BIL_TX_MEASURE29,'||
245 ' BIL_TX_MEASURE30, BIL_TX_MEASURE31, BIL_TX_MEASURE32, BIL_TX_MEASURE33,BIL_TX_MEASURE34, BIL_TX_URL1, BIL_TX_URL2 FROM';
246
247
248 l_select_1 := ' ( SELECT ascd.lead_id BIL_TX_MEASURE1 '||
249 ' ,ascd.OPP_DESCRIPTION BIL_TX_MEASURE2 '||
250 ' ,ascd.customer_id BIL_TX_MEASURE3 ,party.party_name BIL_TX_MEASURE4 ,'||
251 ' ascd.sales_group_id BIL_TX_MEASURE5 ,jrgst.GROUP_NAME BIL_TX_MEASURE6 ,'||
252 ' jrret.RESOURCE_NAME BIL_TX_MEASURE7 ,ascd.win_probability BIL_TX_MEASURE8 ,'||
253 ' (CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,ascd.TOTAL_AMOUNT , ascd.TOTAL_AMOUNT)) '||
254 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.TOTAL_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE9, '||
255 ' status.meaning BIL_TX_MEASURE10 ,ascd.decision_date BIL_TX_MEASURE11 ,'||
256 ' decode( greatest(SYSDATE, ascd.DECISION_DATE), ascd.DECISION_DATE, to_char(trunc(ascd.DECISION_DATE)-trunc(SYSDATE)),0) BIL_TX_MEASURE13 ,'||
257 ' (TRUNC(SYSDATE)-trunc(ascd.OPPORTUNITY_CREATION_DATE)) BIL_TX_MEASURE14 ,'||
258 ' decode( greatest(SYSDATE, ascd.DECISION_DATE), SYSDATE, to_char(trunc(SYSDATE)-trunc(ascd.DECISION_DATE)),0) BIL_TX_MEASURE15 ' ||
259 l_status_days ||
260 ' ,0 BIL_TX_MEASURE21 '||
261 ' , ascd.LEAD_NUMBER BIL_TX_MEASURE24 ,flvl2.meaning BIL_TX_MEASURE25 ,'||
262 ' ascd.OPPORTUNITY_LAST_UPDATE_DATE BIL_TX_MEASURE26, '||
263 ' JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_LAST_UPDATED_BY) BIL_TX_MEASURE27, '||
264 ' ascd.OPPORTUNITY_CREATION_DATE BIL_TX_MEASURE28, '||
265 ' JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_CREATED_BY) BIL_TX_MEASURE29, '||
266 ' flvl1.meaning BIL_TX_MEASURE30, '||
267 ' ( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN '||
268 ' (DECODE(m.CONVERSION_RATE, NULL ,ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100),ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100))) '||
269 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100) *m.CONVERSION_RATE)) END ) BIL_TX_MEASURE31, '||
270 ' flvl3.meaning BIL_TX_MEASURE32, '||
271 ' ''' || l_cur_conv_missing || ''' BIL_TX_MEASURE33, ' ||
275
272 ' ''' || l_cur_conv_missing || ''' BIL_TX_MEASURE34, ' ||
273 l_oppty_url1||' BIL_TX_URL1 ,'||
274 l_customer_url2||' BIL_TX_URL2 ';
276
277 -- FROM section
278 l_from := ' FROM as_sales_credits_denorm ascd '||
279 ' ,jtf_rs_groups_denorm denorm '||
280 ' ,jtf_rs_group_usages usages '||
281 ' ,JTF_RS_RESOURCE_EXTNS_TL jrret'||
282 ' ,jtf_rs_groups_tl jrgst'||
283 ' ,gl_daily_rates m '||
284 ' ,as_statuses_tl status '||
285 ' ,fnd_lookup_values flvl1'||
286 ' ,fnd_lookup_values flvl2'||
287 ' ,fnd_lookup_values flvl3'||
288 ' ,hz_parties party'||
289 ' ,AS_LLOG_STATUS_SUMMARY ss ';
290
291 l_where_clause_1 := ' WHERE ascd.decision_date BETWEEN :l_from_date AND :l_to_date '||
292 ' AND denorm.parent_group_id IN (&ORGANIZATION+JTF_ORG_SALES_GROUP) '||
293 ' AND denorm.LATEST_RELATIONSHIP_FLAG = ''Y'' '||
294 ' AND usages.usage = ''SALES'''||
295 ' AND usages.group_id = denorm.group_id'||
296 ' AND ascd.sales_group_id = denorm.group_id'||
297 ' AND usages.group_id = ascd.sales_group_id'||
298 ' AND jrret.RESOURCE_ID = ascd.SALESFORCE_ID '||
299 ' AND party.party_id = ascd.customer_id '||
300 ' AND jrgst.GROUP_ID = ascd.sales_group_id '||
301 ' AND jrgst.LANGUAGE(+) = USERENV('||'''LANG'''||') ' ||
302 ' AND jrret.LANGUAGE(+) = USERENV('||'''LANG'''||') ' ||
303 ' AND m.CONVERSION_DATE(+) = ascd.decision_date'||
304 ' AND m.FROM_CURRENCY(+) = ascd.currency_code'||
305 ' AND m.TO_CURRENCY(+) = &CURRENCY+CURR'||
306 ' AND m.CONVERSION_TYPE(+) = :l_convsersion_type'||
307 ' AND ascd.credit_type_id = :l_credit_type_id'||
308 ' AND ascd.status_code = status.status_code'||
309 ' AND ascd.lead_id = ss.lead_id(+)'||
310 ' AND status.LANGUAGE(+) = USERENV('||'''LANG'''||') ' ||
311 ' AND ascd.channel_code = flvl1.lookup_code ' || -- channel code
312 ' AND trunc(nvl(flvl1.start_date_active, SYSDATE)) <= trunc(SYSDATE)' || -- channel code
313 ' AND trunc(nvl(flvl1.end_date_active, SYSDATE)) >= trunc(SYSDATE) ' || -- channel code
314 ' AND flvl1.enabled_flag = ''Y'' ' ||-- channel code
315 ' AND flvl1.language = USERENV(''LANG'')' || -- channel code
316 ' AND flvl1.lookup_type = ''SALES_CHANNEL'' ' ||-- channel code
317 ' AND flvl1.view_application_id = 660 ' ||-- channel code
318 ' AND ascd.CLOSE_REASON = flvl2.lookup_code(+) '||-- close reason
319 ' AND trunc(nvl(flvl2.start_date_active(+), SYSDATE)) <= trunc(SYSDATE ) '||-- close reason
320 ' AND trunc(nvl(flvl2.end_date_active(+), SYSDATE)) >= trunc(SYSDATE) '||-- close reason
321 ' AND flvl2.enabled_flag(+) = ''Y'' '||-- close reason
322 ' AND flvl2.language(+) = USERENV(''LANG'') '||-- close reaso
323 ' AND flvl2.lookup_type(+) = ''ASN_OPPTY_CLOSE_REASON'' '||-- close reason
324 ' AND flvl2.view_application_id(+) = 0 '||-- close reason
325 ' AND ascd.CUSTOMER_CATEGORY_CODE = flvl3.lookup_code(+) '||
326 ' AND trunc(nvl(flvl3.start_date_active(+), SYSDATE)) <= trunc(SYSDATE) '||-- Customer category
327 ' AND trunc(nvl(flvl3.end_date_active(+), SYSDATE)) >= trunc(SYSDATE) '||-- Customer category
328 ' AND flvl3.enabled_flag(+) = ''Y'' '||-- Customer category
329 ' AND flvl3.language(+) = USERENV(''LANG'') '|| --
330 ' AND flvl3.lookup_type(+) = ''CUSTOMER_CATEGORY'' '||-- Customer category
331 ' AND flvl3.view_application_id(+) = 222 ' ;
332
333
334
335
336 IF l_product_category IS NOT NULL THEN
337 l_from := l_from ||
338 ',ENI_DENORM_HIERARCHIES edeh '||
339 ',MTL_DEFAULT_CATEGORY_SETS mdcs ';
340
341 l_where_clause_2 := ' AND mdcs.CATEGORY_SET_ID = edeh.OBJECT_ID
342 AND mdcs.FUNCTIONAL_AREA_ID = 11
343 AND edeh.OBJECT_TYPE = '|| '''CATEGORY_SET''
344 AND edeh.PARENT_ID = :l_product_category
345 AND edeh.OLTP_FLAG = ''Y''
346 AND mdcs.CATEGORY_SET_ID = ascd.PRODUCT_CAT_SET_ID
347 AND edeh.CHILD_ID = ascd.PRODUCT_CATEGORY_ID ';
348 END IF;
349 -- WHERE section
350
351
352 IF l_customer IS NOT NULL THEN
353 l_where_clause_2 := l_where_clause_2 ||
354 ' AND ascd.CUSTOMER_ID IN (&CUSTOMER+CUST) ';
355
356 END IF;
357
358 IF l_item_id IS NOT NULL THEN
359 l_where_clause_2 := l_where_clause_2 ||
360 ' AND ascd.ITEM_ID = :l_item_id ';
361
362 END IF;
363
364 -- Opportunity Status
365 IF l_opp_status IS NOT NULL THEN
366 l_where_clause_2 := l_where_clause_2 ||
367 ' AND ascd.STATUS_CODE IN (&OPP_STATUS+STAT) ';
368 END IF;
369
370
371
372 -- Sales Stage
373
374 IF l_sales_stage IS NOT NULL THEN
375 l_where_clause_2 := l_where_clause_2 ||
376 ' AND ascd.SALES_STAGE_ID IN (&SLS_STAGE+STAGE) ';
377 END IF;
378
379 -- Win Probability
380
381 IF l_win_probability IS NOT NULL THEN
382 l_where_clause_2 := l_where_clause_2 ||
383 ' AND ascd.WIN_PROBABILITY'|| l_win_probability_opr ||' to_number(:l_win_probability )';
384 ELSE
388 -- Opportunity/Lead Source
385 l_where_clause_2 := l_where_clause_2 || ' AND 1 = 0 ';
386 END IF;
387
389 IF l_source IS NOT NULL THEN
390 l_where_clause_2 := l_where_clause_2 ||
391 ' AND ascd.SOURCE_PROMOTION_ID IN (&SOURCE+SOUR)';
392 END IF;
393
394 -- Sales channel
395 IF l_sales_channel IS NOT NULL THEN
396 l_where_clause_2 := l_where_clause_2 ||
397 ' AND ascd.CHANNEL_CODE IN(&SLS_CHNL+CHNL) ';
398 END IF;
399
400 -- Opportunity Name
401 IF REPLACE (l_opty_name, '%',NULL) IS NOT NULL THEN
402 l_where_clause_2 := l_where_clause_2 ||
403 ' AND ascd.OPP_DESCRIPTION LIKE :l_opty_name ';
404 END IF;
405
406
407 -- Opportunity number
408 IF l_opty_number IS NOT NULL THEN
409 l_where_clause_2 := l_where_clause_2 ||
410 ' AND ascd.LEAD_NUMBER LIKE &BIL_TX_OPP_NUMBER ';
411 END IF;
412
413 -- Sales Person
414 IF l_sales_person IS NOT NULL THEN
415 l_where_clause_2 := l_where_clause_2 ||
416 ' AND ascd.SALESFORCE_ID IN (&SLS_PRSON+PERSON) ';
417 END IF;
418
419 -- Sales Methodology
420 IF l_sls_methodology IS NOT NULL THEN
421 l_where_clause_2 := l_where_clause_2 ||
422 ' AND ascd.SALES_METHODOLOGY_ID IN (&METHODOLOGY+METH) ';
423 END IF;
424
425
426 -- Partner Name
427 IF l_partner_id IS NOT NULL THEN
428 l_from := l_from ||
429 ' ,( SELECT acc.lead_id , '||
430 ' partner.party_id party_id '||
431 ' FROM HZ_PARTIES PARTNER, '||
432 ' AS_ACCESSES_ALL ACC, '||
433 ' JTF_RS_RESOURCE_EXTNS EXT, '||
434 ' hz_organization_profiles HZOP, '||
435 ' hz_relationships HZR '||
436 ' WHERE hzr.PARTY_ID = ACC.PARTNER_CUSTOMER_ID '||
437 ' AND EXT.RESOURCE_ID = ACC.SALESFORCE_ID '||
438 ' AND PARTNER.status IN (''A'' , ''I'') '||
439 ' AND HZR.subject_table_name = ''HZ_PARTIES'' '||
440 ' AND HZR.object_table_name = ''HZ_PARTIES'' '||
441 ' AND HZR.object_id = HZOP.party_id '||
442 ' AND HZOP.internal_flag = ''Y'' '||
443 ' AND NVL(HZOP.status, ''A'') = ''A'' '||
444 ' AND NVL(HZOP.effective_end_date, SYSDATE) >= SYSDATE '||
445 ' AND HZR.party_id = EXT.source_id '||
446 ' AND EXT.category = ''PARTNER'' '||
447 ' AND HZR.subject_id = PARTNER.party_id ) partner ';
448
449 l_where_clause_2 := l_where_clause_2 || ' AND partner.party_id IN (&PARTNER+NAME) AND PARTNER.lead_id = ascd.lead_id';
450 END IF;
451
452 -- GROUP BY SECTION
453 l_group_by := ' GROUP BY
454 ascd.lead_id
455 ,ascd.OPP_DESCRIPTION
456 ,ascd.customer_id , party.party_name
457 , ascd.sales_group_id, jrgst.GROUP_NAME
458 ,jrret.RESOURCE_NAME ,ascd.win_probability
459 ,(CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,ascd.TOTAL_AMOUNT , ascd.TOTAL_AMOUNT))
460 ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.TOTAL_AMOUNT*m.CONVERSION_RATE)) END )
461 ,status.meaning ,ascd.decision_date
462 ,decode( greatest(SYSDATE, ascd.DECISION_DATE), ascd.DECISION_DATE, to_char(trunc(ascd.DECISION_DATE)-trunc(SYSDATE)),0)
463 ,(TRUNC(SYSDATE)-trunc(ascd.OPPORTUNITY_CREATION_DATE))
464 ,decode( greatest(SYSDATE, ascd.DECISION_DATE), SYSDATE, to_char(trunc(SYSDATE)-trunc(ascd.DECISION_DATE)),0)
465 ,0
466 , ascd.LEAD_NUMBER
467 , flvl2.meaning
468 ,ascd.OPPORTUNITY_LAST_UPDATE_DATE
469 ,JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_LAST_UPDATED_BY)
470 ,ascd.OPPORTUNITY_CREATION_DATE
471 , JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_CREATED_BY)
472 ,flvl1.meaning
473 , ( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN
474 (DECODE(m.CONVERSION_RATE, NULL ,ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100),ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100)))
475 ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100) *m.CONVERSION_RATE)) END )
476 , flvl3.meaning
477 ,''' || l_cur_conv_missing || '''
478 ,''' || l_cur_conv_missing || ''' ' ;
479
480 l_group_by := l_group_by || ', ' ||l_oppty_url1 || ',' ||l_customer_url2;
481
482
483
484 -- ORDER BY section
485 -- sarma
486 IF l_order IS NULL THEN
487 l_order := ' NLSSORT(BIL_TX_MEASURE2, ''NLS_SORT=BINARY'') ';
488 END IF;
489
490 l_order_by := ' ORDER BY '||l_order ; -- Opportunity Name
491
492 x_custom_sql := l_outer_select ||l_select_1|| l_from|| l_where_clause_1||
493 l_where_clause_2||l_group_by;
494 x_custom_sql := x_custom_sql ||')' || l_order_by;
495
496 -- x_custom_sql := l_select_1|| l_from|| l_where_clause_1||
497 -- l_where_clause_2||l_group_by || l_order_by;
498
499
500
501
502 -- insert into x1 values ('l_outer_select = ' || l_outer_select,sysdate); commit;
503 -- insert into x1 values ('l_select_1 = ' || l_select_1,sysdate); commit;
504 -- insert into x1 values ('l_from = ' || l_from,sysdate); commit;
505 -- insert into x1 values ('l_where_clause_1 = ' || l_where_clause_1,sysdate); commit;
506 -- insert into x1 values ('l_where_clause_2 = ' || l_where_clause_2,sysdate); commit;
507
508
509 l_bind_ctr := 1;
510
511 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
512 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
513 l_custom_rec.attribute_value := l_viewby;
514 x_custom_attr.Extend();
515 x_custom_attr(l_bind_ctr):=l_custom_rec;
516 l_bind_ctr:=l_bind_ctr+1;
517
518
519 IF REPLACE (l_opty_name, '%',NULL) IS NOT NULL THEN
520 l_custom_rec.attribute_name :=':l_opty_name';
524 x_custom_attr.Extend();
521 l_custom_rec.attribute_value := l_opty_name;
522 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
523 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
525 x_custom_attr(l_bind_ctr):=l_custom_rec;
526 l_bind_ctr:=l_bind_ctr+1;
527 END IF;
528 l_custom_rec.attribute_name :=':l_period_set_name';
529 l_custom_rec.attribute_value :=l_period_set_name;
530 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
531 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
532 x_custom_attr.Extend();
533 x_custom_attr(l_bind_ctr):=l_custom_rec;
534 l_bind_ctr:=l_bind_ctr+1;
535
536
537
538 IF l_convsersion_type IS NOT NULL THEN
539 l_custom_rec.attribute_name :=':l_convsersion_type';
540 l_custom_rec.attribute_value :=l_convsersion_type;
541 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
542 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
543 x_custom_attr.Extend();
544 x_custom_attr(l_bind_ctr):=l_custom_rec;
545 l_bind_ctr:=l_bind_ctr+1;
546 END IF;
547
548 IF l_opty_number IS NOT NULL THEN
549 l_custom_rec.attribute_name :=':l_opty_number';
550 l_custom_rec.attribute_value := l_opty_number;
551 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
552 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
553 x_custom_attr.Extend();
554 x_custom_attr(l_bind_ctr):=l_custom_rec;
555 l_bind_ctr:=l_bind_ctr+1;
556 END IF;
557
558
559 l_custom_rec.attribute_name :=':l_to_period_name';
560 l_custom_rec.attribute_value := l_to_period_name;
561 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
562 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
563 x_custom_attr.Extend();
564 x_custom_attr(l_bind_ctr):=l_custom_rec;
565 l_bind_ctr:=l_bind_ctr+1;
566
567
568 IF l_item_id IS NOT NULL THEN
569 l_custom_rec.attribute_name :=':l_item_id';
570 l_custom_rec.attribute_value := l_item_id;
571 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
572 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
573 x_custom_attr.Extend();
574 x_custom_attr(l_bind_ctr):=l_custom_rec;
575 l_bind_ctr:=l_bind_ctr+1;
576 END IF;
577
578
579 IF l_product_category IS NOT NULL THEN
580 l_custom_rec.attribute_name :=':l_product_category';
581 l_custom_rec.attribute_value := l_product_category;
582 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
583 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
584 x_custom_attr.Extend();
585 x_custom_attr(l_bind_ctr):=l_custom_rec;
586 l_bind_ctr:=l_bind_ctr+1;
587 END IF;
588
589 l_custom_rec.attribute_name :=':l_period_type';
590 l_custom_rec.attribute_value := l_period_type;
591 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
592 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
593 x_custom_attr.Extend();
594 x_custom_attr(l_bind_ctr):=l_custom_rec;
595 l_bind_ctr:=l_bind_ctr+1;
596
597
598 l_custom_rec.attribute_name :=':l_win_probability_opr';
599 l_custom_rec.attribute_value :=l_win_probability_opr;
600 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
601 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
602 x_custom_attr.Extend();
603 x_custom_attr(l_bind_ctr):=l_custom_rec;
604 l_bind_ctr:=l_bind_ctr+1;
605
606 l_custom_rec.attribute_name :=':l_win_probability';
607 l_custom_rec.attribute_value := l_win_probability;
608 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
609 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
610 x_custom_attr.Extend();
611 x_custom_attr(l_bind_ctr):=l_custom_rec;
612 l_bind_ctr:=l_bind_ctr+1;
613
614
615 l_custom_rec.attribute_name :=':l_credit_type_id';
616 l_custom_rec.attribute_value := l_credit_type_id;
617 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
618 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
619 x_custom_attr.Extend();
620 x_custom_attr(l_bind_ctr):=l_custom_rec;
621 l_bind_ctr:=l_bind_ctr+1;
622
623 l_custom_rec.attribute_name :=':l_from_date';
624 l_custom_rec.attribute_value := TO_CHAR(l_from_date,'dd/MM/yyyy') ;
625 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
626 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
627 x_custom_attr.Extend();
628 x_custom_attr(l_bind_ctr):=l_custom_rec;
629 l_bind_ctr:=l_bind_ctr+1;
630
631 l_custom_rec.attribute_name :=':l_to_date';
632 l_custom_rec.attribute_value := TO_CHAR(l_to_date,'dd/MM/yyyy') ;
633 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
634 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
635 x_custom_attr.Extend();
636 x_custom_attr(l_bind_ctr):=l_custom_rec;
640 l_custom_rec.attribute_name :=':l_rpt_str';
637 l_bind_ctr:=l_bind_ctr+1;
638
639
641 l_custom_rec.attribute_value :=l_rpt_str;
642 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
643 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
644 x_custom_attr.Extend();
645 x_custom_attr(l_bind_ctr):=l_custom_rec;
646 l_bind_ctr:=l_bind_ctr+1;
647
648
649
650 END OPTY_AGE_RPT;
651 END BIL_TX_OPTY_AGE_RPT_PKG;