[Home] [Help]
PACKAGE BODY: APPS.BIL_TX_OPTY_LIST_RPT_PKG
Source
1 PACKAGE BODY BIL_TX_OPTY_LIST_RPT_PKG AS
2 /* $Header: biltxolb.pls 120.25 2006/08/23 04:55:24 vselvapr ship $ */
3
4 PROCEDURE OPTY_LIST_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(180);
13 l_viewby VARCHAR2(180) ;
14 l_proc VARCHAR2(500);
15 l_region_id VARCHAR2(150);
16 g_pkg VARCHAR2(100);
17 l_parameter_valid BOOLEAN;
18 l_sql_error_desc VARCHAR2(4000);
19 -- sql statement related
20 l_custom_sql VARCHAR2(32000);
21
22 l_select1 VARCHAR2(32000);
23 l_select2 VARCHAR2(32000);
24 l_from VARCHAR2(5000);
25 l_where_clause VARCHAR2(20000);
26 l_dummy_where_clause VARCHAR2(5000);
27 l_group_by VARCHAR2(5000);
28 l_order_by VARCHAR2(5000);
29 -- parameters
30 l_period_type VARCHAR2(5000);
31 l_from_date DATE;
32 l_to_date DATE;
33 l_forecast_owner VARCHAR2(5000); -- sales credit id needs to be passed I think.
34 l_sales_group VARCHAR2(5000);
35 l_sales_person VARCHAR2(5000);
36 l_opty_name VARCHAR2(1000);
37 l_customer VARCHAR2(5000);
38 l_source VARCHAR2(500);
39 l_opp_status VARCHAR2(5000); -- multi select
40 l_win_probability VARCHAR2(100);
41 l_win_probability_opr VARCHAR2(100);
42 l_sales_channel VARCHAR2(5000);
43 l_sales_stage VARCHAR2(5000);
44 l_sls_methodology VARCHAR2(5000);
45 l_product_category VARCHAR2(5000); -- if only prod cat is passed
46 l_item_id VARCHAR2(5000); -- may pass both item and prod.
47 l_partner_id VARCHAR2(5000);
48 l_partner_name VARCHAR2(5000);
49 l_to_currency VARCHAR2(5000);
50 l_c_to_currency VARCHAR2(5000);
51 l_report_by VARCHAR2(5000);
52 l_close_reason VARCHAR2(5000);
53 l_competitor VARCHAR2(5000);
54 l_opty_number VARCHAR2(1000);
55 l_total_opp_amount VARCHAR2(1000);
56 l_total_opp_amt_opr VARCHAR2(1000);
57 l_oppty_url1 VARCHAR2(1000);
58 l_customer_url2 VARCHAR2(1000);
59 l_url VARCHAR2(1000);
60 l_sales_team_access VARCHAR2(1000);
61 l_credit_type_id NUMBER;
62 l_to_period_name VARCHAR2(500);
63 l_order VARCHAR2(5000); -- Kiran
64 l_period_set_name VARCHAR2(500);
65 l_conversion_type VARCHAR2(500);
66 l_cur_conv_missing VARCHAR2(2000);
67
68
69 BEGIN
70
71 -- IF WE want we can get region and function from get page params
72 -- Initializing variables as per new standard
73
74 l_region_id := 'BIL_TX_OPTY_LIST_RPT';
75 l_parameter_valid := FALSE;
76 l_rpt_str := 'BIL_TX_OPTY_LIST_RPT_R';
77 l_proc := 'OPTY_LIST_RPT.';
78 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_LIST_RPT_PKG.';
79 --Fix for bug 5469370,added replace function
80 l_cur_conv_missing := replace(FND_MESSAGE.GET_STRING('BIL','BIL_TX_CUR_CONV_MIS'),'''','''''');
81
82
83 l_period_set_name := NVL(FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR'), 'Accounting');
84 l_conversion_type := nvl(FND_PROFILE.VALUE('CRMBIS:GL_CONVERSION_TYPE'), 'Corporate');
85
86
87 -- FND logging standard
88 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
89
90 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
91 p_module => g_pkg || l_proc || 'begin',
92 p_msg => 'Start of Procedure '|| l_proc );
93 END IF;
94
95
96
97 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
98 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
99
100
101 l_oppty_url1 :='''pFunctionName=ASN_OPPTYDETPG&addBreadCrumb=Y&ASNReqFrmOpptyId=''||ascd.LEAD_NUMBER '; -- ASN Opportunity page link
102
103 l_customer_url2 := '''pFunctionName=ASN_CUSTDETGWAYPG&ASNReqAcsErrInDlg=Y&addBreadCrumb=Y&ASNReqFrmCustId=''||ascd.CUSTOMER_ID ';
104
105
106 BIL_TX_UTIL_RPT_PKG.GET_PAGE_PARAMS
107 (p_page_parameter_tbl => p_page_parameter_tbl
108 ,p_region_id => l_region_id
109 ,x_period_type => l_period_type
110 ,x_to_currency => l_to_currency
111 ,x_to_period_name => l_to_period_name
112 ,x_sg_id => l_sales_group
113 ,x_resource_id => l_sales_person
114 ,x_frcst_owner => l_sales_team_access
115 ,x_prodcat_id => l_product_category
116 ,x_item_id => l_item_id
117 ,x_parameter_valid => l_parameter_valid
118 ,x_viewby => l_viewby
119 ,x_order => l_order
120 ,x_rptby => l_report_by
121 ,x_sls_chnl => l_sales_channel
122 ,x_sls_stge => l_sales_stage
123 ,x_opp_status => l_opp_status
124 ,x_source => l_source
125 ,x_sls_methodology => l_sls_methodology
126 ,x_win_probability => l_win_probability
127 ,x_win_probability_opr => l_win_probability_opr
128 ,x_close_reason => l_close_reason
129 ,x_competitor => l_competitor
130 ,x_opty_number => l_opty_number
131 ,x_total_opp_amount => l_total_opp_amount
132 ,x_total_opp_amt_opr => l_total_opp_amt_opr
133 ,x_opty_name => l_opty_name
134 ,x_customer => l_customer
135 ,x_partner => l_partner_id
136 ,x_from_date => l_from_date
137 ,x_to_date => l_to_date);
138
139 l_sales_team_access := REPLACE(l_sales_team_access , '''');
140
141 IF l_sales_team_access = 'ST' THEN
142 l_credit_type_id := FND_PROFILE.VALUE('ASN_FRCST_CREDIT_TYPE_ID');
143 ELSE
144 -- For now if it is not a sales team access only credit type id is passed.
145 l_credit_type_id := TO_NUMBER(l_sales_team_access) ;
146 END IF;
147
148 IF l_credit_type_id IS NULL THEN
149 l_credit_type_id := FND_PROFILE.VALUE('ASN_FRCST_CREDIT_TYPE_ID');
150 END IF;
151
152
153
154
155 -- Once PMV date fix is given change this call and defaults
156
157
158 l_sql_error_desc :=
159 'l_viewby => '||l_viewby||', '||
160 'l_period_type => '|| l_period_type ||', ' ||
161 'l_sales_group => '|| l_sales_group ||', ' ||
162 'l_sales_perso => '|| l_sales_person ||', ' ||
163 'l_product_category => '|| l_product_category ||', ' ||
164 'l_from_date => '|| l_from_date ||','||
165 'l_to_date => '|| l_to_date ||','||
166 'l_to_currency => '||l_to_currency ||','||
167 'l_close_reason => '||l_close_reason ||','||
168 'l_opty_number => '||l_opty_number ||','||
169 'l_win_probability => '||l_win_probability||','||
170 'l_win_probability_opr => '||l_win_probability_opr ||','||
171 'l_total_opp_amount => '||l_total_opp_amount ||','||
172 'l_total_opp_amt_opr => '||l_total_opp_amt_opr ||','||
173 'l_competitor => '||l_competitor ||','||
174 'l_sales_team_access => '||l_sales_team_access ||','||
175 'l_credit_type_id => '||l_credit_type_id||','||
176 'l_period_set_name => '||l_period_set_name||', '||
177 'l_conversion_type => '||l_conversion_type ||', '||
178 'l_report_by => '||l_report_by;
179
180
181
182 -- insert into zz values(l_sql_error_desc);
183 -- commit;
184
185 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
186 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
187 p_module => g_pkg || l_proc ,
188 p_msg => ' Params are =>'||l_sql_error_desc);
189
190 END IF;
191
192 /*** Query column mapping ******************************************************
193
194 * BIL_TX_MEASURE1 = Lead Id
195 * BIL_TX_MEASURE2 = Opportunity Name
196 * BIL_TX_MEASURE3 = Customer_id
197 * BIL_TX_MEASURE4 = Customer Name
198 * BIL_TX_MEASURE5 = Sales Id
199 * BIL_TX_MEASURE6 = Sales Group
200 * BIL_TX_MEASURE7 = Sales Person
201 * BIL_TX_MEASURE8 = Days to close
202 * BIL_TX_MEASURE9 = Win Probability
203 * BIL_TX_MEASURE10 = Total Opportunity Amount
204 * BIL_TX_MEASURE11 = Status
205 * BIL_TX_MEASURE12 = close date -- decision_date
206 -- Product information
207 * BIL_TX_MEASURE13 = Product_category_id
208 * BIL_TX_MEASURE14 = Item_id
209 * BIL_TX_MEASURE15 = Item description
210 * BIL_TX_MEASURE16 = Amount
211 * BIL_TX_MEASURE17 = Forecast Date
212 * BIL_TX_MEASURE18 = Best Amount
213 * BIL_TX_MEASURE19 = Forecast Amount
214 * BIL_TX_MEASURE20 = Worst Amount
215 -- Competitor Information
216 * BIL_TX_MEASURE21 = Competitor_id -- can be taken out
217 * BIL_TX_MEASURE22 = Competitor_name
218 * BIL_TX_MEASURE23 = Competitor Product_name
219 * BIL_TX_MEASURE24 = Win Loss Status
220 * BIL_TX_MEASURE25 = Partner Name Removed in 120.16
221 * BIL_TX_MEASURE26 = Close reason
222 * BIL_TX_MEASURE27 = Created Date
223 * BIL_TX_MEASURE28 = Created By
224 * BIL_TX_MEASURE29 = customer classification code
225 * BIL_TX_MEASURE30 = updated date
226 * BIL_TX_MEASURE31 = updated by
227 * BIL_TX_MEASURE32 = Opportunity Number
228 * BIL_TX_MEASURE33 = Weighted Amount
229 * BIL_TX_MEASURE34 = Sales Channel
230
231 * BIL_TX_URL1 = Link to Opportunity Name
232 * BIL_TX_URL2 = Link to Customer
233
234 *******************************************************************************/
235
236 -- SELECT SECTION
237
238
239
240
241
242 l_select1 := 'SELECT ascd.lead_id BIL_TX_MEASURE1 '||
243 ' ,ascd.OPP_DESCRIPTION BIL_TX_MEASURE2 '||
244 ' ,ascd.CUSTOMER_ID BIL_TX_MEASURE3 '||
245 ' ,hzpt1.party_name BIL_TX_MEASURE4 ';
246
247 IF l_sales_team_access = 'ST' THEN
248 l_select1 := l_select1 ||
249 ' ,aca.sales_group_id BIL_TX_MEASURE5 ';
250 ELSE
251 l_select1 := l_select1 ||
252 ' ,ascd.sales_group_id BIL_TX_MEASURE5 ';
253 END IF;
254
255 l_select1 := l_select1 ||
256 ' ,jrgt.GROUP_NAME BIL_TX_MEASURE6 '||
257 ' ,jrre.SOURCE_NAME BIL_TX_MEASURE7 '||
258 ' ,decode( greatest(SYSDATE, ascd.DECISION_DATE), ascd.DECISION_DATE, to_char(trunc(ascd.DECISION_DATE)-trunc(SYSDATE)),NULL) BIL_TX_MEASURE8 '||
259 ' ,ascd.WIN_PROBABILITY BIL_TX_MEASURE9 '||
260 ' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,ascd.TOTAL_AMOUNT , ascd.TOTAL_AMOUNT)) '||
261 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.TOTAL_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE10 '||
262 ' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL '||
263 ' ,ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100) , ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100))) '||
264 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100)*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE33 '||
265 ' ,asst.meaning BIL_TX_MEASURE11 '||
266 ' ,ascd.DECISION_DATE BIL_TX_MEASURE12 '||
267 ' ,ascd.PRODUCT_CATEGORY_ID BIL_TX_MEASURE13 '||
268 ' ,ascd.ITEM_ID BIL_TX_MEASURE14 '||
269 ' ,NVL(msit.description,mct.description) BIL_TX_MEASURE15 '||
270 ' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL, ascd.SALES_CREDIT_AMOUNT, ascd.SALES_CREDIT_AMOUNT)) '||
271 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.SALES_CREDIT_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE16 '||
272 ' ,ascd.FORECAST_DATE BIL_TX_MEASURE17 '||
273 ' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,NVL(ascd.OPP_BEST_FORECAST_AMOUNT,0) , ascd.OPP_BEST_FORECAST_AMOUNT)) '||
274 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.OPP_BEST_FORECAST_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE18 '||
275 ' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,NVL(ascd.OPP_FORECAST_AMOUNT,0) , ascd.OPP_FORECAST_AMOUNT)) '||
276 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.OPP_FORECAST_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE19 '||
277 ' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,NVL(ascd.OPP_WORST_FORECAST_AMOUNT,0) , ascd.OPP_WORST_FORECAST_AMOUNT)) '||
278 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.OPP_WORST_FORECAST_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE20 '||
279 ' ,ascd.CLOSE_COMPETITOR_ID BIL_TX_MEASURE21 '||
280 ' ,hzpt.party_name BIL_TX_MEASURE22 '||
281 ' ,acpt.COMPETITOR_PRODUCT_NAME BIL_TX_MEASURE23 '||
282 ' ,INITCAP(alcp.WIN_LOSS_STATUS) BIL_TX_MEASURE24 '||
283 ' ,flvl2.meaning BIL_TX_MEASURE26 '||
284 ' ,ascd.OPPORTUNITY_CREATION_DATE BIL_TX_MEASURE27 '||
285 ' ,JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_CREATED_BY) BIL_TX_MEASURE28 '||
286 ' ,flvl3.meaning BIL_TX_MEASURE29 '||
287 ' ,ascd.LAST_UPDATE_DATE BIL_TX_MEASURE30 '||
288 ' ,JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_LAST_UPDATED_BY) BIL_TX_MEASURE31 '||
289 ' ,ascd.LEAD_NUMBER BIL_TX_MEASURE32 '||
290 ' ,flvl1.meaning BIL_TX_MEASURE34 '|| --ascd.CHANNEL_CODE
291 ' ,ascd.credit_type_id BIL_TX_MEASURE44 ';
292
293
294 l_select2 := ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE46 ' ||
295 ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE47 ' ||
296 ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE48 ' ||
297 ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE49 ' ||
298 ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE50 ' ||
299 ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE45 ' ||
300 ', '||l_oppty_url1||' BIL_TX_URL1'||
301 ', '||l_customer_url2||' BIL_TX_URL2 ';
302
303
304 l_from := ' FROM as_sales_credits_denorm ascd '||
305 ' ,jtf_rs_groups_denorm denorm '||
306 ' ,jtf_rs_group_usages usages '||
307 ' ,GL_DAILY_RATES m '||
308 ' ,as_lead_comp_products alcp '||
309 ' ,ams_competitor_products_tl acpt '||
310 ' ,ams_competitor_products_b acpb '||
311 ' ,mtl_categories_tl mct ' ||
312 ' ,mtl_system_items_tl msit '|| -- Prod cat
313 ' ,fnd_lookup_values flvl1 '|| -- for chaneel code
314 ' ,fnd_lookup_values flvl2 '|| -- FOR close reason
315 ' ,fnd_lookup_values flvl3 '||-- for customer category
316 ' ,hz_parties hzpt '||
317 ' ,hz_parties hzpt1 '||
318 ' ,as_statuses_tl asst '||
319 ' ,jtf_rs_groups_tl jrgt '||
320 ' ,jtf_rs_resource_extns jrre ';
321
322
323
324 IF l_sales_team_access = 'ST' THEN
325 l_from := l_from || ' ,as_accesses_all aca ';
326 END IF;
327
328 -- Add only when Partner Id is passed
329 IF l_partner_id IS NOT NULL THEN
330 l_from := l_from ||
331 ' ,( SELECT acc.lead_id , '||
332 ' partner.party_id party_id '||
333 ' FROM HZ_PARTIES PARTNER, '||
334 ' AS_ACCESSES_ALL ACC, '||
338 ' WHERE hzr.PARTY_ID = ACC.PARTNER_CUSTOMER_ID '||
335 ' JTF_RS_RESOURCE_EXTNS EXT, '||
336 ' hz_organization_profiles HZOP, '||
337 ' hz_relationships HZR '||
339 ' AND EXT.RESOURCE_ID = ACC.SALESFORCE_ID '||
340 ' AND PARTNER.status IN (''A'' , ''I'') '||
341 ' AND HZR.subject_table_name = ''HZ_PARTIES'' '||
342 ' AND HZR.object_table_name = ''HZ_PARTIES'' '||
343 ' AND HZR.object_id = HZOP.party_id '||
344 ' AND HZOP.internal_flag = ''Y'' '||
345 ' AND NVL(HZOP.status, ''A'') = ''A'' '||
346 ' AND NVL(HZOP.effective_end_date, SYSDATE) >= SYSDATE '||
347 ' AND HZR.party_id = EXT.source_id '||
348 ' AND EXT.category = ''PARTNER'' '||
349 ' AND HZR.subject_id = PARTNER.party_id ) partner ';
350 END IF;
351
352 -- Dummy Where Clause
353 l_dummy_where_clause := ' WHERE 1 = 2 ';
354
355
356 -- WHERE section
357 IF l_report_by = 2 THEN
358 l_where_clause := ' WHERE ascd.FORECAST_DATE BETWEEN :l_from_date AND :l_to_date '||
359 ' AND m.conversion_date(+) = ascd.forecast_date ' ;
360 ELSE
361 l_where_clause := ' WHERE ascd.DECISION_DATE BETWEEN :l_from_date AND :l_to_date '||
362 ' AND m.conversion_date(+) = ascd.decision_date ' ;
363 END IF;
364
365 l_where_clause := l_where_clause ||
366 ' AND denorm.parent_group_id IN ( &ORGANIZATION+JTF_ORG_SALES_GROUP ) '||
367 ' AND denorm.latest_relationship_flag = ''Y'' '||
368 ' AND usages.usage = ''SALES'' '||
369 ' AND usages.group_id = denorm.group_id '||
370 ' AND ascd.credit_type_id = :l_credit_type_id '||
371 ' AND m.FROM_CURRENCY(+) = ascd.currency_code '||
372 ' AND m.TO_CURRENCY(+) = &CURRENCY+CURR '||
373 ' AND m.CONVERSION_TYPE(+) = :l_conversion_type '||
374 ' AND ascd.lead_id = alcp.LEad_id(+) '||
375 ' AND ascd.lead_line_id = alcp.lead_line_id(+) '||
376 ' AND alcp.competitor_product_id = acpt.competitor_product_id(+) '||
377 ' AND acpt.competitor_product_id = acpb.competitor_product_id(+) '||
378 ' AND acpt.language(+) = USERENV(''LANG'') '||
379 ' AND TRUNC(NVL(acpb.start_date, SYSDATE)) <= TRUNC(SYSDATE) '||
380 ' AND TRUNC(NVL(acpb.end_date, SYSDATE)) >= TRUNC(SYSDATE) '||
381 ' AND acpb.competitor_party_id = hzpt.party_id (+) '||
382 ' AND ascd.CUSTOMER_ID = hzpt1.party_id '||
383 ' AND ascd.item_id = msit.inventory_item_id(+) '||
384 ' AND ascd.organization_id = msit.organization_id (+) '||
385 ' AND msit.language (+) = USERENV(''LANG'') '||
386 ' AND ascd.PRODUCT_CATEGORY_ID = mct.CATEGORY_ID '|| -- Product desc
387 ' AND mct.language = USERENV(''LANG'') '|| -- Product desc
388 ' AND jrgt.GROUP_ID = denorm.group_id '|| -- For GRP
389 ' AND jrgt.LANGUAGE = USERENV( ''LANG'' ) '|| -- For GRP
390 ' AND ascd.channel_code = flvl1.lookup_code '|| -- channel code
391 ' AND trunc(nvl(flvl1.start_date_active, SYSDATE)) <= trunc(SYSDATE)'|| -- channel code
392 ' AND trunc(nvl(flvl1.end_date_active, SYSDATE)) >= trunc(SYSDATE) '|| -- channel code
393 ' AND flvl1.enabled_flag = ''Y'' '|| -- channel code
394 ' AND flvl1.language = USERENV(''LANG'') '|| -- channel code
395 ' AND flvl1.lookup_type = ''SALES_CHANNEL'' '|| -- channel code
396 ' AND flvl1.view_application_id = 660 '|| -- channel code
397 ' AND ascd.STATUS_CODE = asst.status_code '|| -- status code
398 ' AND asst.language= userenv(''LANG'') '|| -- status code
399 ' AND ascd.CLOSE_REASON = flvl2.lookup_code(+) '|| -- close reason
400 ' AND trunc(nvl(flvl2.start_date_active(+), SYSDATE)) <= trunc(SYSDATE ) '|| -- close reason
401 ' AND trunc(nvl(flvl2.end_date_active(+), SYSDATE)) >= trunc(SYSDATE) '|| -- close reason
402 ' AND flvl2.enabled_flag(+) = ''Y'' '|| -- close reason
403 ' AND flvl2.language(+) = USERENV(''LANG'') '|| -- close reason
404 ' AND flvl2.lookup_type(+) = ''ASN_OPPTY_CLOSE_REASON'' '|| -- close reason
405 ' AND flvl2.view_application_id(+) = 0 '|| -- close reason
406 ' AND ascd.CUSTOMER_CATEGORY_CODE = flvl3.lookup_code(+) '||
407 ' AND trunc(nvl(flvl3.start_date_active(+), SYSDATE)) <= trunc(SYSDATE) '|| -- Customer category
408 ' AND trunc(nvl(flvl3.end_date_active(+), SYSDATE)) >= trunc(SYSDATE) '|| -- Customer category
409 ' AND flvl3.enabled_flag(+) = ''Y'' '|| -- Customer category
410 ' AND flvl3.language(+) = USERENV(''LANG'') '|| --
411 ' AND flvl3.lookup_type(+) = ''CUSTOMER_CATEGORY'' '|| -- Customer category
412 ' AND flvl3.view_application_id(+) = 222 '; -- Customer category
413
414
415
416 IF l_sales_team_access = 'ST' THEN
417
418 l_where_clause := l_where_clause ||
419 ' AND aca.sales_group_id = denorm.group_id '||
420 ' AND usages.group_id = aca.sales_group_id '||
421 ' AND aca.lead_id = ascd.lead_id '||
422 ' AND NVL(aca.OPEN_FLAG ,''N'') = ''Y'' '||
423 ' AND aca.LEAD_ID IS NOT NULL ' ||
424 ' AND jrre.resource_id = ACA.SALESFORCE_ID ' ;
425
426 IF l_partner_id IS NOT NULL THEN
427 l_where_clause := l_where_clause ||
428 ' AND aca.lead_id = PARTNER.lead_id (+) ';
429 END IF;
430 ELSE
431 -- For revenue and Non revenue
432 l_where_clause := l_where_clause ||
433 ' AND ascd.sales_group_id = denorm.group_id '||
434 ' AND usages.group_id = ascd.sales_group_id '||
435 ' AND jrre.resource_id = ascd.SALESFORCE_ID ' ;
436
437 IF l_partner_id IS NOT NULL THEN
441 END IF;
438 l_where_clause := l_where_clause ||
439 ' AND ascd.lead_id = PARTNER.lead_id (+) ';
440 END IF;
442
443
444 -- FROM section
445
446
447 IF l_product_category IS NOT NULL THEN
448
449 l_from := l_from ||
450 ',ENI_DENORM_HIERARCHIES edeh '||
451 ',MTL_DEFAULT_CATEGORY_SETS mdcs ';
452 END IF;
453
454
455 IF l_product_category IS NOT NULL THEN
456
457 l_where_clause := l_where_clause ||
458 ' AND mdcs.FUNCTIONAL_AREA_ID = 11 '||
459 ' AND mdcs.CATEGORY_SET_ID = edeh.OBJECT_ID '||
460 ' AND edeh.OBJECT_TYPE = ''CATEGORY_SET'' '||
461 ' AND edeh.PARENT_ID = :l_product_category '|| -- pass product cat id.
462 ' AND edeh.OLTP_FLAG = ''Y'' '||
463 ' AND mdcs.CATEGORY_SET_ID = ascd.PRODUCT_CAT_SET_ID '||
464 ' AND edeh.CHILD_ID = ascd.PRODUCT_CATEGORY_ID ';
465 END IF;
466 IF l_item_id IS NOT NULL THEN
467 l_where_clause := l_where_clause ||
468 ' AND ascd.ITEM_ID = :l_item_id ';
469 END IF;
470
471
472 -- Sales Person (MULTI SELECT)
473 IF l_sales_person IS NOT NULL THEN
474 l_where_clause := l_where_clause ||
475 ' AND ascd.SALESFORCE_ID IN ( &SLS_PRSON+PERSON ) ';
476 END IF;
477
478
479
480 -- Opportunity Name
481 IF l_opty_name IS NOT NULL THEN
482 l_where_clause := l_where_clause ||
483 ' AND ascd.OPP_DESCRIPTION LIKE &BIL_TX_OPTY_NAME '; --:l_opty_name ';
484 END IF;
485
486 -- Opportunity/Lead Source
487 IF l_source IS NOT NULL THEN
488 l_where_clause := l_where_clause ||
489 ' AND ascd.SOURCE_PROMOTION_ID = &SOURCE+SOUR ';
490 END IF;
491
492 -- Opportunity Status (MS)
493 IF l_opp_status IS NOT NULL THEN
494 l_where_clause := l_where_clause ||
495 ' AND ascd.STATUS_CODE IN ( &OPP_STATUS+STAT ) ';
496 END IF;
497
498 -- Win Probability
499 IF l_win_probability IS NOT NULL THEN
500 l_win_probability := TO_NUMBER(REPLACE(l_win_probability,',',NULL)); -- strip off commas
501
502 l_where_clause := l_where_clause ||
503 ' AND ascd.WIN_PROBABILITY '|| l_win_probability_opr ||' :l_win_probability ';
504 END IF;
505
506 -- Sales channel (MS)
507 IF l_sales_channel IS NOT NULL THEN
508 l_where_clause := l_where_clause ||
509 ' AND ascd.CHANNEL_CODE IN ( &SLS_CHNL+CHNL )';
510 END IF;
511
512 -- Sales Stage
513 IF l_sales_stage IS NOT NULL THEN
514 l_where_clause := l_where_clause ||
515 ' AND ascd.SALES_STAGE_ID = &SLS_STAGE+STAGE ';
516 END IF;
517
518
519 -- Sales Methodology
520 IF l_sls_methodology IS NOT NULL THEN
521 l_where_clause := l_where_clause ||
522 ' AND ascd.SALES_METHODOLOGY_ID = &METHODOLOGY+METH ';
523 END IF;
524
525
526 -- Close Reason
527 IF l_close_reason IS NOT NULL THEN
528 l_where_clause := l_where_clause ||
529 ' AND ascd.CLOSE_REASON = &CLOSE+REASON ';
530 END IF;
531
532 -- Competitor
533 IF l_competitor IS NOT NULL THEN
534 l_where_clause := l_where_clause ||
535 ' AND acpb.competitor_party_id = &COMPTETOR+COMP ';
536 END IF;
537
538 -- customer (MULTI SELECT)
539 IF l_customer IS NOT NULL THEN
540 l_where_clause := l_where_clause ||
541 ' AND ascd.CUSTOMER_ID IN ( &CUSTOMER+CUST )';
542 END IF;
543
544
545 -- Opportunity Number PARTIAL WILD CARD ALSO SHOULD WORK
546 IF l_opty_number IS NOT NULL THEN
547 l_where_clause := l_where_clause ||
548 ' AND ascd.LEAD_NUMBER LIKE &BIL_TX_OPP_NUMBER '; -- :l_opty_number ';
549 END IF;
550
551
552 IF l_total_opp_amount IS NOT NULL THEN
553
554 l_total_opp_amount := TO_NUMBER(REPLACE(l_total_opp_amount,',',NULL)); -- strip off commas
555
556 l_where_clause := l_where_clause ||
557 ' AND ascd.TOTAL_AMOUNT '|| l_total_opp_amt_opr ||' :l_total_opp_amount ';
558 END IF;
559
560 -- Partner
561 IF l_partner_id IS NOT NULL THEN
562 l_where_clause := l_where_clause ||
563 ' AND partner.party_id IN ( &PARTNER+NAME ) ';
564 END IF;
565
566
567 l_order_by := ' ORDER BY BIL_TX_MEASURE1, BIL_TX_MEASURE3, BIL_TX_MEASURE5, BIL_TX_MEASURE7, BIL_TX_MEASURE13,BIL_TX_MEASURE21 ';
568
569
570
571 IF l_parameter_valid THEN
572 x_custom_sql := l_custom_sql||l_select1||l_select2||l_from||l_where_clause||l_order_by ;
573 ELSE
574 x_custom_sql := l_custom_sql||l_select1||l_select2||l_from||l_dummy_where_clause ;
575 END IF;
576
577
578 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
579 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
580 p_proc => l_proc,
581 p_query => x_custom_sql);
582 END IF;
583
584
585
586 l_bind_ctr := 1;
587
588 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
589 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
590 l_custom_rec.attribute_value := l_viewby;
591 x_custom_attr.Extend();
592 x_custom_attr(l_bind_ctr):=l_custom_rec;
593 l_bind_ctr:=l_bind_ctr+1;
594
595
596
597 l_custom_rec.attribute_name :=':l_from_date';
598 l_custom_rec.attribute_value := TO_CHAR(l_from_date,'dd/MM/yyyy') ;
599 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
600 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
601 x_custom_attr.Extend();
602 x_custom_attr(l_bind_ctr):=l_custom_rec;
603 l_bind_ctr:=l_bind_ctr+1;
604
605 l_custom_rec.attribute_name :=':l_to_date';
606 l_custom_rec.attribute_value := TO_CHAR(l_to_date,'dd/MM/yyyy') ;
607 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
608 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
609 x_custom_attr.Extend();
610 x_custom_attr(l_bind_ctr):=l_custom_rec;
611 l_bind_ctr:=l_bind_ctr+1;
612
613
614 l_custom_rec.attribute_name :=':l_product_category';
615 l_custom_rec.attribute_value :=l_product_category;
616 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
617 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
618 x_custom_attr.Extend();
619 x_custom_attr(l_bind_ctr):=l_custom_rec;
620 l_bind_ctr:=l_bind_ctr+1;
621
622 l_custom_rec.attribute_name :=':l_item_id';
623 l_custom_rec.attribute_value := l_item_id;
624 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
625 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
626 x_custom_attr.Extend();
627 x_custom_attr(l_bind_ctr):=l_custom_rec;
628 l_bind_ctr:=l_bind_ctr+1;
629
630
631 l_custom_rec.attribute_name :=':l_credit_type_id';
632 l_custom_rec.attribute_value := l_credit_type_id;
633 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_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;
637 l_bind_ctr:=l_bind_ctr+1;
638
639
640
641 l_custom_rec.attribute_name :=':l_period_set_name';
642 l_custom_rec.attribute_value :=l_period_set_name;
643 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
644 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
645 x_custom_attr.Extend();
646 x_custom_attr(l_bind_ctr):=l_custom_rec;
647 l_bind_ctr:=l_bind_ctr+1;
648
649
650 l_custom_rec.attribute_name :=':l_conversion_type';
651 l_custom_rec.attribute_value :=l_conversion_type;
652 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
653 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
654 x_custom_attr.Extend();
655 x_custom_attr(l_bind_ctr):=l_custom_rec;
656 l_bind_ctr:=l_bind_ctr+1;
657
658 l_custom_rec.attribute_name :=':l_win_probability_opr';
659 l_custom_rec.attribute_value :=l_win_probability_opr;
660 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
661 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
662 x_custom_attr.Extend();
663 x_custom_attr(l_bind_ctr):=l_custom_rec;
664 l_bind_ctr:=l_bind_ctr+1;
665
666 l_custom_rec.attribute_name :=':l_win_probability';
667 l_custom_rec.attribute_value := l_win_probability;
668 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
669 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
670 x_custom_attr.Extend();
671 x_custom_attr(l_bind_ctr):=l_custom_rec;
672 l_bind_ctr:=l_bind_ctr+1;
673
674 l_custom_rec.attribute_name :=':l_total_opp_amt_opr';
675 l_custom_rec.attribute_value :=l_total_opp_amt_opr;
676 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
677 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
678 x_custom_attr.Extend();
679 x_custom_attr(l_bind_ctr):=l_custom_rec;
680 l_bind_ctr:=l_bind_ctr+1;
681
682 l_custom_rec.attribute_name :=':l_total_opp_amount';
683 l_custom_rec.attribute_value := l_total_opp_amount;
684 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
685 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
686 x_custom_attr.Extend();
687 x_custom_attr(l_bind_ctr):=l_custom_rec;
688 l_bind_ctr:=l_bind_ctr+1;
689
690 l_custom_rec.attribute_name :=':l_to_period_name';
691 l_custom_rec.attribute_value :=l_to_period_name;
692 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
693 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
694 x_custom_attr.Extend();
695 x_custom_attr(l_bind_ctr):=l_custom_rec;
696 l_bind_ctr:=l_bind_ctr+1;
697
698 l_custom_rec.attribute_name :=':l_rpt_str';
699 l_custom_rec.attribute_value :=l_rpt_str;
700 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
701 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
702 x_custom_attr.Extend();
703 x_custom_attr(l_bind_ctr):=l_custom_rec;
704 l_bind_ctr:=l_bind_ctr+1;
705
706
707 l_custom_rec.attribute_name :=':l_viewby';
708 l_custom_rec.attribute_value := l_viewby;
709 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
710 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
711 x_custom_attr.Extend();
712 x_custom_attr(l_bind_ctr):=l_custom_rec;
713 l_bind_ctr:=l_bind_ctr+1;
714
715 END OPTY_LIST_RPT;
716
717 END BIL_TX_OPTY_LIST_RPT_PKG;
718