[Home] [Help]
PACKAGE BODY: APPS.BIL_TX_OPTY_DETL_RPT_PKG
Source
1 PACKAGE BODY BIL_TX_OPTY_DETL_RPT_PKG AS
2 /* $Header: biltxodb.pls 120.15 2006/01/03 15:32 syeddana ship $ */
3
4 PROCEDURE OPP_DETL_TAB (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_region_id VARCHAR2(50);
12 l_parameter_valid BOOLEAN;
13 l_rpt_str VARCHAR2(80);
14 l_viewby VARCHAR2(80) ;
15 l_bind_ctr NUMBER;
16 l_proc VARCHAR2(100);
17 l_custom_sql VARCHAR2(32000);
18 l_outer_select VARCHAR2(4000);
19 g_pkg VARCHAR2(100);
20 l_where_clause VARCHAR2(10000);
21 l_lead_id VARCHAR2(100) ;
22 l_cust_id VARCHAR2(100);
23 l_credit_type_id VARCHAR2(100);
24 l_period_set_name VARCHAR2(500);
25 l_conversion_type VARCHAR2(500);
26 l_cur_conv_missing VARCHAR2(2000);
27
28
29
30 BEGIN
31
32 -- Initializing variables as per new standard
33
34 l_region_id := 'BIL_TX_OPTY_DETL_RPT';
35 l_parameter_valid := FALSE;
36 l_rpt_str := 'BIL_TX_OPTY_DETL_RPT_R';
37 l_proc := 'OPP_DETL_TAB.';
38 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
39
40
41 -- FND logging standard
42 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
43
44 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
45 p_module => g_pkg || l_proc || 'begin',
46 p_msg => 'Start of Procedure '|| l_proc );
47 END IF;
48
49
50 l_period_set_name := NVL(FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR'), 'Accounting');
51 l_conversion_type := nvl(FND_PROFILE.VALUE('CRMBIS:GL_CONVERSION_TYPE'), 'Corporate');
52
53 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
54 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
55
56
57 -- code for a procedure to get parameter values.
58 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
59 (p_page_parameter_tbl => p_page_parameter_tbl,
60 p_region_id => l_region_id,
61 x_parameter_valid => l_parameter_valid,
62 x_viewby => l_viewby,
63 x_lead_id => l_lead_id,
64 x_cust_id => l_cust_id,
65 x_credit_type_id => l_credit_type_id
66 ) ;
67
68
69
70
71 /*** Query column mapping ******************************************************
72
73 * BIL_TX_MEASURE1 = Opportunity Name
74 * BIL_TX_MEASURE2 = Sales Channel
75 * BIL_TX_MEASURE3 = Close reason
76 * BIL_TX_MEASURE4 = Opportunity Number
77 * BIL_TX_MEASURE5 = Total Opportunity Amount
78 * BIL_TX_MEASURE6 = Close date
79 * BIL_TX_MEASURE7 = Customer Name
80 * BIL_TX_MEASURE8 = Total Forecast Amount
81 * BIL_TX_MEASURE9 = Creation Date
82 * BIL_TX_MEASURE10 = Customer Address
83 * BIL_TX_MEASURE11 = Currency
84 * BIL_TX_MEASURE12 = Created By
85 * BIL_TX_MEASURE13 = Status
86 * BIL_TX_MEASURE14 = Methodology
87 * BIL_TX_MEASURE15 = Updated date
88 * BIL_TX_MEASURE16 = Win Probability
89 * BIL_TX_MEASURE17 = Sales Stage
90 * BIL_TX_MEASURE18 = Updated By
91
92 *******************************************************************************/
93
94
95 l_custom_sql := ' SELECT OpportunityEO.description BIL_TX_MEASURE1, '||
96 ' flv.meaning BIL_TX_MEASURE2, '||
97 ' FLV2.MEANING BIL_TX_MEASURE3, '||
98 ' OpportunityEO.lead_id BIL_TX_MEASURE4, '||
99 ' OpportunityEO.total_amount BIL_TX_MEASURE5, '||
100 ' OpportunityEO.decision_date BIL_TX_MEASURE6, '||
101 ' hp.party_name BIL_TX_MEASURE7, '||
102 ' (SELECT SUM(opp_forecast_amount) FROM as_sales_credits ascs '||
103 ' WHERE ascs.lead_id = OpportunityEO.lead_id AND ascs.credit_type_id = :l_credit_type_id ) BIL_TX_MEASURE8, '||
104 ' OpportunityEO.creation_date BIL_TX_MEASURE9, '||
105 ' hz_format_pub.format_address(hl.location_id, null, null, '', '', null, null,null, null) || decode(ftt.territory_short_name, null, null, '', ''||ftt.territory_short_name) BIL_TX_MEASURE10, '||
106 ' fc.name BIL_TX_MEASURE11, '||
107 ' JTF_COMMON_PVT.GetUserInfo(OpportunityEO.CREATED_BY ) BIL_TX_MEASURE12, '||
108 ' astl.meaning BIL_TX_MEASURE13, '||
109 ' asmt.sales_methodology_name BIL_TX_MEASURE14, '||
110 ' OpportunityEO.LAST_UPDATE_DATE BIL_TX_MEASURE15, '||
111 ' OpportunityEO.win_probability BIL_TX_MEASURE16, '||
112 ' asst.name BIL_TX_MEASURE17, '||
113 ' JTF_COMMON_PVT.GetUserInfo(OpportunityEO.LAST_UPDATE_LOGIN) BIL_TX_MEASURE18 ';
114
115
116 l_where_clause := ' FROM as_leads_all OpportunityEO, '||
117 ' hz_parties hp, '||
118 ' hz_party_sites hps, '||
119 ' hz_locations hl, '||
120 ' fnd_territories_tl ftt, '||
121 ' fnd_currencies_tl fc, '||
122 ' fnd_lookup_values flv, '||
123 ' fnd_lookup_values flv2, '||
124 ' as_statuses_tl astl, '||
125 ' as_sales_methodology_tl asmt, '||
126 ' as_sales_stages_all_tl asst '||
127 ' WHERE OpportunityEO.customer_id = hp.party_id '||
128 ' AND OpportunityEO.address_id = hps.party_site_id(+) '||
129 ' AND OpportunityEO.customer_id = hps.party_id (+) '||
130 ' AND hps.location_id = hl.location_id (+) '||
131 ' AND hl.country = ftt.territory_code (+) '||
132 ' AND ftt.language (+) = USERENV(''LANG'') '||
133 ' AND OpportunityEO.currency_code = fc.CURRENCY_CODE(+) '||
134 ' AND fc.LANGUAGE = USERENV(''LANG'') '||
135 ' and OpportunityEO.channel_code = flv.lookup_code '||
136 ' AND flv.enabled_flag = ''Y'' '||
137 ' AND FLV.language = USERENV(''LANG'') '||
138 ' AND flv.lookup_type = ''SALES_CHANNEL'' '||
139 ' AND flv.view_application_id = 660 '||
140 ' AND trunc(nvl(flv.start_date_active, SYSDATE)) <= trunc(SYSDATE) '||
141 ' AND trunc(nvl(flv.end_date_active, SYSDATE)) >= trunc(SYSDATE) '||
142 ' AND OpportunityEO.close_reason = flv2.lookup_code(+) '||
143 ' AND flv2.lookup_type(+) = ''ASN_OPPTY_CLOSE_REASON'' '||
144 ' AND flv2.view_application_id(+) = 0 '||
145 ' AND flv2.enabled_flag(+) = ''Y'' '||
146 ' AND flv2.language(+) = USERENV(''LANG'') '||
147 ' AND trunc(nvl(flv2.start_date_active, SYSDATE)) <= trunc(SYSDATE) '||
148 ' AND trunc(nvl(flv2.end_date_active, SYSDATE)) >= trunc(SYSDATE) '||
149 ' AND opportunityEO.status = astl.status_code '||
150 ' AND astl.language = userenv(''LANG'') '||
151 ' AND OpportunityEO.sales_methodology_id = asmt.sales_methodology_id (+) '||
152 ' AND asmt.language(+) = USERENV (''LANG'') '||
153 ' AND OpportunityEO.sales_stage_id = asst.sales_stage_id(+) '||
154 ' AND asst.language(+) = USERENV(''LANG'') '||
155 ' AND OpportunityEO.lead_id = :l_lead_id ';
156
157
158
159 x_custom_sql := l_custom_sql ||l_where_clause ;
160
161 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
162 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
163 p_proc => l_proc,
164 p_query => x_custom_sql);
165 END IF;
166
167 l_bind_ctr := 1;
168
169 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
170 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
171 l_custom_rec.attribute_value := l_viewby;
172 x_custom_attr.Extend();
173 x_custom_attr(l_bind_ctr):=l_custom_rec;
174 l_bind_ctr:=l_bind_ctr+1;
175
176
177 l_custom_rec.attribute_name :=':l_rpt_str';
178 l_custom_rec.attribute_value :=l_rpt_str;
179 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
180 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
181 x_custom_attr.Extend();
182 x_custom_attr(l_bind_ctr):=l_custom_rec;
183 l_bind_ctr:=l_bind_ctr+1;
184
185 l_custom_rec.attribute_name :=':l_lead_id';
186 l_custom_rec.attribute_value := l_lead_id;
187 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
188 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
189 x_custom_attr.Extend();
190 x_custom_attr(l_bind_ctr):=l_custom_rec;
191 l_bind_ctr:=l_bind_ctr+1;
192
193
194 l_custom_rec.attribute_name :=':l_credit_type_id';
195 l_custom_rec.attribute_value := l_credit_type_id;
196 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
197 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
198 x_custom_attr.Extend();
199 x_custom_attr(l_bind_ctr):=l_custom_rec;
200 l_bind_ctr:=l_bind_ctr+1;
201
202
203 l_custom_rec.attribute_name :=':l_viewby';
204 l_custom_rec.attribute_value := l_viewby;
205 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
206 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
207 x_custom_attr.Extend();
208 x_custom_attr(l_bind_ctr):=l_custom_rec;
209 l_bind_ctr:=l_bind_ctr+1;
210
211 END OPP_DETL_TAB;
212
213 PROCEDURE OPP_FLEX_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
214 ,x_custom_sql OUT NOCOPY VARCHAR2
215 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
216
217
218
219 l_custom_rec BIS_QUERY_ATTRIBUTES;
220 l_region_id VARCHAR2(50);
221 l_parameter_valid BOOLEAN;
222 l_rpt_str VARCHAR2(80);
223 l_viewby VARCHAR2(80) ;
224 l_bind_ctr NUMBER;
225 l_proc VARCHAR2(100);
226 l_custom_sql VARCHAR2(32000);
227 l_outer_select VARCHAR2(4000);
228 g_pkg VARCHAR2(100);
229 l_where_clause VARCHAR2(1000);
230 l_lead_id VARCHAR2(100);
231 l_cust_id VARCHAR2(100) ;
232 l_credit_type_id VARCHAR2(100);
233
234
235 BEGIN
236
237
238 -- Initializing variables as per new standard
239
240 l_region_id := 'BIL_TX_OPTY_FLEX_RPT';
241 l_parameter_valid := FALSE;
242 l_rpt_str := 'BIL_TX_OPTY_FLEX_RPT_R';
243 l_proc := 'OPP_FLEX_TAB.';
244 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
245
246
247 -- FND logging standard
248 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
249
250 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
251 p_module => g_pkg || l_proc || 'begin',
252 p_msg => 'Start of Procedure '|| l_proc );
253 END IF;
254
255
256 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
257 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
258
259
260 -- code for a procedure to get parameter values.
261 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
265 x_viewby => l_viewby,
262 (p_page_parameter_tbl => p_page_parameter_tbl,
263 p_region_id => l_region_id,
264 x_parameter_valid => l_parameter_valid,
266 x_lead_id => l_lead_id,
267 x_cust_id => l_cust_id,
268 x_credit_type_id => l_credit_type_id
269 ) ;
270
271
272 /*** Query column mapping ******************************************************
273
274 * BIL_TX_MEASURE1 = attribute1
275 * BIL_TX_MEASURE2 = attribute2
276 * BIL_TX_MEASURE3 = attribute3
277 * BIL_TX_MEASURE4 = attribute4
278 * BIL_TX_MEASURE5 = attribute5
279 * BIL_TX_MEASURE6 = attribute6
280 * BIL_TX_MEASURE7 = attribute7
281 * BIL_TX_MEASURE8 = attribute8
282 * BIL_TX_MEASURE9 = attribute9
283 * BIL_TX_MEASURE10 = attribute10
284 * BIL_TX_MEASURE11 = attribute11
285 * BIL_TX_MEASURE12 = attribute12
286 * BIL_TX_MEASURE13 = attribute13
287 * BIL_TX_MEASURE14 = attribute14
288 * BIL_TX_MEASURE15 = attribute15
289 * BIL_TX_MEASURE16 = Attribute Category
290
291
292 *******************************************************************************/
293
294
295
296
297 l_custom_sql := ' SELECT OpportunityEO.attribute1 BIL_TX_MEASURE1, '||
298 ' OpportunityEO.attribute2 BIL_TX_MEASURE2, '||
299 ' OpportunityEO.attribute3 BIL_TX_MEASURE3, '||
300 ' OpportunityEO.attribute4 BIL_TX_MEASURE4, '||
301 ' OpportunityEO.attribute5 BIL_TX_MEASURE5, '||
302 ' OpportunityEO.attribute6 BIL_TX_MEASURE6, '||
303 ' OpportunityEO.attribute7 BIL_TX_MEASURE7, '||
304 ' OpportunityEO.attribute8 BIL_TX_MEASURE8, '||
305 ' OpportunityEO.attribute9 BIL_TX_MEASURE9, '||
306 ' OpportunityEO.attribute10 BIL_TX_MEASURE10, '||
307 ' OpportunityEO.attribute11 BIL_TX_MEASURE11, '||
308 ' OpportunityEO.attribute12 BIL_TX_MEASURE12, '||
309 ' OpportunityEO.attribute13 BIL_TX_MEASURE13, '||
310 ' OpportunityEO.attribute14 BIL_TX_MEASURE14, '||
311 ' OpportunityEO.attribute15 BIL_TX_MEASURE15, '||
312 ' OpportunityEO.attribute_category BIL_TX_MEASURE16 '||
313 ' FROM as_leads_all OpportunityEO ' ||
314 ' WHERE OpportunityEO.lead_id = :l_lead_id ';
315 /*
316 IF l_lead_id IS NULL THEN
317 l_where_clause := ' WHERE 1 = 2 ';
318 ELSE
319 l_where_clause := ' WHERE OpportunityEO.lead_id = :l_lead_id ';
320 END IF;
321 */
322
323 -- x_custom_sql := l_custom_sql||l_where_clause ;
324
325 x_custom_sql := l_custom_sql;
326
327 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
328 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
329 p_proc => l_proc,
330 p_query => x_custom_sql);
331 END IF;
332
333 l_bind_ctr := 1;
334
335 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
336 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
337 l_custom_rec.attribute_value := l_viewby;
338 x_custom_attr.Extend();
339 x_custom_attr(l_bind_ctr):=l_custom_rec;
340 l_bind_ctr:=l_bind_ctr+1;
341
342
343 l_custom_rec.attribute_name :=':l_rpt_str';
344 l_custom_rec.attribute_value :=l_rpt_str;
345 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
346 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
347 x_custom_attr.Extend();
348 x_custom_attr(l_bind_ctr):=l_custom_rec;
349 l_bind_ctr:=l_bind_ctr+1;
350
351 l_custom_rec.attribute_name :=':l_lead_id';
352 l_custom_rec.attribute_value := l_lead_id;
353 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
354 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
355 x_custom_attr.Extend();
356 x_custom_attr(l_bind_ctr):=l_custom_rec;
357 l_bind_ctr:=l_bind_ctr+1;
358
359 l_custom_rec.attribute_name :=':l_viewby';
360 l_custom_rec.attribute_value := l_viewby;
361 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
362 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
363 x_custom_attr.Extend();
364 x_custom_attr(l_bind_ctr):=l_custom_rec;
365 l_bind_ctr:=l_bind_ctr+1;
366
367 END OPP_FLEX_TAB;
368
369 PROCEDURE PRODUCTS_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
370 ,x_custom_sql OUT NOCOPY VARCHAR2
371 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
372
373
374 l_custom_rec BIS_QUERY_ATTRIBUTES;
375 l_region_id VARCHAR2(100);
376 l_parameter_valid BOOLEAN;
377 l_rpt_str VARCHAR2(180);
378 l_viewby VARCHAR2(180) ;
379 l_bind_ctr NUMBER;
380 l_proc VARCHAR2(100);
381 l_custom_sql VARCHAR2(32000);
382 l_outer_select VARCHAR2(4000);
383 g_pkg VARCHAR2(500);
384 l_view_param VARCHAR2(500);
385 l_page_period_type VARCHAR2(100);
386 l_lead_id VARCHAR2(100) ;
387 l_cust_id VARCHAR2(100);
388 l_to_currency VARCHAR2(100);
389 l_period_type VARCHAR2(100);
390 l_period_name VARCHAR2(100);
391 l_credit_type_id VARCHAR2(100);
395
392 l_period_set_name VARCHAR2(500);
393 l_conversion_type VARCHAR2(500);
394 l_sg_id VARCHAR2(200);
396 BEGIN
397 -- Initializing variables as per new standard
398
399 l_region_id := 'BIL_TX_OPTY_DETL_PROD_RPT';
400 l_parameter_valid := FALSE;
401 l_rpt_str := 'BIL_TX_OPTY_DETL_PROD_RPT_R';
402 l_proc := 'PRODUCTS_TAB.';
403 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
404
405 l_period_set_name := NVL(FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR'), 'Accounting');
406 l_conversion_type := nvl(FND_PROFILE.VALUE('CRMBIS:GL_CONVERSION_TYPE'), 'Corporate');
407
408
409 -- FND logging standard
410 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
411
412 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
413 p_module => g_pkg || l_proc || 'begin',
414 p_msg => 'Start of Procedure '|| l_proc );
415 END IF;
416
417 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
418 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
419
420 -- code for a procedure to get parameter values.
421 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
422 (p_page_parameter_tbl => p_page_parameter_tbl,
423 p_region_id => l_region_id,
424 x_parameter_valid => l_parameter_valid,
425 x_viewby => l_viewby,
426 x_lead_id => l_lead_id,
427 x_cust_id => l_cust_id,
428 x_credit_type_id => l_credit_type_id
429 ) ;
430
431
432 /*** Query column mapping ******************************************************
433
434 * BIL_TX_MEASURE1 = lead Id
435 * BIL_TX_MEASURE2 = Product category id
436 * BIL_TX_MEASURE3 = Item id
437 * BIL_TX_MEASURE4 = Product
438 * BIL_TX_MEASURE5 = UOM
439 * BIL_TX_MEASURE6 = Quantity
440 * BIL_TX_MEASURE7 = Amount
441 * BIL_TX_MEASURE8 = sales_group_id
442 * BIL_TX_MEASURE9 = sales Group
443 * BIL_TX_MEASURE10 = Forecast Owner
444 * BIL_TX_MEASURE11 = Forecast_Type
445 * BIL_TX_MEASURE12 = Best
446 * BIL_TX_MEASURE13 = Forecast
447 * BIL_TX_MEASURE14 = Worst
448 * BIL_TX_MEASURE15 = CLOSE COMPETITOR ID
449 * BIL_TX_MEASURE16 = COMPETITOR NAME
450 * BIL_TX_MEASURE17 = Comp Product
451 * BIL_TX_MEASURE18 = Win/Loss Status
452 *******************************************************************************/
453
454 l_custom_sql := ' SELECT ascd.lead_id BIL_TX_MEASURE1 '||
455 ' ,ascd.PRODUCT_CATEGORY_ID BIL_TX_MEASURE2 '||
456 ' ,ascd.ITEM_ID BIL_TX_MEASURE3 '||
457 ' ,NVL(msit.description, mct.description) BIL_TX_MEASURE4 '||
458 ' ,mumt.DESCRIPTION BIL_TX_MEASURE5 '||
459 ' ,ascd.QUANTITY BIL_TX_MEASURE6 '||
460 ' ,ascd.SALES_CREDIT_AMOUNT BIL_TX_MEASURE7 '||
461 ' ,ascd.sales_group_id BIL_TX_MEASURE8 '||
462 ' ,jrgt.GROUP_NAME BIL_TX_MEASURE9 '||
463 ' ,jrre.SOURCE_NAME BIL_TX_MEASURE10 '||
464 ' ,osct.name BIL_TX_MEASURE11 '||
465 ' ,ascd.OPP_BEST_FORECAST_AMOUNT BIL_TX_MEASURE12 '||
466 ' ,ascd.OPP_FORECAST_AMOUNT BIL_TX_MEASURE13 '||
467 ' ,ascd.OPP_WORST_FORECAST_AMOUNT BIL_TX_MEASURE14 '||
468 ' ,ascd.CLOSE_COMPETITOR_ID BIL_TX_MEASURE15 '||
469 ' ,hzpt.party_name BIL_TX_MEASURE16 '||
470 ' ,acpt.COMPETITOR_PRODUCT_NAME BIL_TX_MEASURE17 '||
471 ' ,INITCAP(alcp.WIN_LOSS_STATUS) BIL_TX_MEASURE18 '||
472 ' FROM as_sales_credits_denorm ascd '||
473 ' ,as_lead_comp_products alcp '||
474 ' ,ams_competitor_products_tl acpt '||
475 ' ,oe_sales_credit_types osct '||
476 ' ,mtl_system_items_tl msit '||
477 ' ,mtl_categories_tl mct '||
478 ' ,mtl_units_of_measure_tl mumt '||
479 ' ,ams_competitor_products_b acpb '||
480 ' ,hz_parties hzpt '||
481 ' ,jtf_rs_groups_tl jrgt '||
482 ' ,jtf_rs_resource_extns jrre '||
483 ' WHERE ascd.LEAD_ID = :l_lead_id '||
484 ' AND ascd.lead_id = alcp.LEad_id(+) '||
485 ' AND ascd.lead_line_id = alcp.lead_line_id(+) '||
486 ' AND alcp.competitor_product_id = acpt.competitor_product_id(+) '||
487 ' AND acpt.language(+) = USERENV( ''LANG'' ) '||
488 ' AND acpt.competitor_product_id = acpb.competitor_product_id(+) '||
489 ' AND TRUNC(NVL(acpb.start_date, SYSDATE)) <= TRUNC(SYSDATE) '||
490 ' AND TRUNC(NVL(acpb.end_date, SYSDATE)) >= TRUNC(SYSDATE) '||
491 ' AND osct.SALES_CREDIT_TYPE_ID = ascd.CREDIT_TYPE_ID '||
492 ' AND osct.ENABLED_FLAG = ''Y'' '||
493 ' AND ascd.product_category_id = mct.category_id '||
494 ' AND ascd.item_id = msit.inventory_item_id(+) '||
495 ' AND ascd.organization_id = msit.organization_id(+) '||
496 ' AND msit.language(+) = USERENV( ''LANG'' ) '||
497 ' AND mct.language = USERENV( ''LANG'' ) '||
498 ' AND acpb.competitor_party_ID = hzpt.party_id(+) '||
499 ' AND jrgt.GROUP_ID = ascd.sales_group_id '||
500 ' AND jrgt.LANGUAGE = USERENV( ''LANG'' ) '||
504 ' ORDER BY ascd.PRODUCT_CATEGORY_ID ' ;
501 ' AND jrre.resource_id = ascd.SALESFORCE_ID '||
502 ' AND ascd.UOM_CODE = mumt.UOM_CODE(+) '||
503 ' AND mumt.LANGUAGE(+) = USERENV( ''LANG'' ) '||
505
506
507
508
509 x_custom_sql := l_custom_sql ;
510
511 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
512 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
513 p_proc => l_proc,
514 p_query => x_custom_sql);
515 END IF;
516
517 l_bind_ctr := 1;
518
519 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
520 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
521 l_custom_rec.attribute_value := l_viewby;
522 x_custom_attr.Extend();
523 x_custom_attr(l_bind_ctr):=l_custom_rec;
524 l_bind_ctr:=l_bind_ctr+1;
525
526 l_custom_rec.attribute_name :=':l_lead_id';
527 l_custom_rec.attribute_value :=l_lead_id;
528 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
529 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
530 x_custom_attr.Extend();
531 x_custom_attr(l_bind_ctr):=l_custom_rec;
532 l_bind_ctr:=l_bind_ctr+1;
533
534
535
536 l_custom_rec.attribute_name :=':l_credit_type_id';
537 l_custom_rec.attribute_value := l_credit_type_id;
538 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
539 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
540 x_custom_attr.Extend();
541 x_custom_attr(l_bind_ctr):=l_custom_rec;
542 l_bind_ctr:=l_bind_ctr+1;
543
544
545
546 l_custom_rec.attribute_name :=':l_rpt_str';
547 l_custom_rec.attribute_value :=l_rpt_str;
548 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
549 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
550 x_custom_attr.Extend();
551 x_custom_attr(l_bind_ctr):=l_custom_rec;
552 l_bind_ctr:=l_bind_ctr+1;
553
554
555 l_custom_rec.attribute_name :=':l_viewby';
556 l_custom_rec.attribute_value := l_viewby;
557 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
558 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
559 x_custom_attr.Extend();
560 x_custom_attr(l_bind_ctr):=l_custom_rec;
561 l_bind_ctr:=l_bind_ctr+1;
562
563 END PRODUCTS_TAB;
564
565
566 PROCEDURE SALES_TEAM_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
567 ,x_custom_sql OUT NOCOPY VARCHAR2
568 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
569
570
571 l_custom_rec BIS_QUERY_ATTRIBUTES;
572 l_region_id VARCHAR2(100);
573 l_parameter_valid BOOLEAN;
574 l_rpt_str VARCHAR2(180);
575 l_viewby VARCHAR2(180) ;
576 l_bind_ctr NUMBER;
577 l_proc VARCHAR2(100);
578 l_custom_sql VARCHAR2(32000);
579 l_outer_select VARCHAR2(4000);
580 g_pkg VARCHAR2(500);
581 l_view_param VARCHAR2(500);
582 l_page_period_type VARCHAR2(100);
583 l_lead_id VARCHAR2(100) ;
584 l_cust_id VARCHAR2(100);
585 l_credit_type_id VARCHAR2(100);
586
587 BEGIN
588 -- Initializing variables as per new standard
589
590 l_region_id := 'BIL_TX_OPTY_DETL_STEAM_RPT';
591 l_parameter_valid := FALSE;
592 l_rpt_str := 'BIL_TX_OPTY_DETL_STEAM_RPT_R';
593 l_proc := 'SALES_TEAM_TAB.';
594 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
595
596
597 -- FND logging standard
598 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
599
600 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
601 p_module => g_pkg || l_proc || 'begin',
602 p_msg => 'Start of Procedure '|| l_proc );
603 END IF;
604
605 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
606 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
607
608 -- code for a procedure to get parameter values.
609 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
610 (p_page_parameter_tbl => p_page_parameter_tbl,
611 p_region_id => l_region_id,
612 x_parameter_valid => l_parameter_valid,
613 x_viewby => l_viewby,
614 x_lead_id => l_lead_id,
615 x_cust_id => l_cust_id,
616 x_credit_type_id => l_credit_type_id
617 ) ;
618
619
620
621 /*** Query column mapping ******************************************************
622
623 * BIL_TX_MEASURE1 = Sales Person
624 * BIL_TX_MEASURE2 = Job Title
625 * BIL_TX_MEASURE3 = Sales Group
626 * BIL_TX_MEASURE4 = Phone
627 * BIL_TX_MEASURE5 = Email
628 * BIL_TX_MEASURE6 = Owner
629 * BIL_TX_MEASURE7 = Contributor
630
631 *******************************************************************************/
632
633
634
635 l_custom_sql := ' SELECT jrt.resource_name BIL_TX_MEASURE1, '||
636 ' jrb.source_job_title BIL_TX_MEASURE2, '||
640 ' DECODE(OpportunityAccessEO.owner_flag, ''Y'', ''bischeck.gif'',NULL) BIL_TX_MEASURE6, '||
637 ' jrgt.group_name BIL_TX_MEASURE3 ,'||
638 ' jrb.source_phone BIL_TX_MEASURE4, '||
639 ' jrb.source_email BIL_TX_MEASURE5, '||
641 ' DECODE(OpportunityAccessEO.contributor_flag, ''Y'', ''bischeck.gif'',NULL) BIL_TX_MEASURE7 '||
642 ' FROM jtf_rs_resource_extns jrb, '||
643 ' jtf_rs_resource_extns_tl jrt, '||
644 ' jtf_rs_groups_tl jrgt, '||
645 ' as_accesses_all OpportunityAccessEO '||
646 ' WHERE OpportunityAccessEO.salesforce_id = jrb.resource_id '||
647 ' AND jrb.resource_id = jrt.resource_id '||
648 ' AND jrb.category = jrt.category '||
649 ' AND jrt.language = USERENV(''LANG'') '||
650 ' AND OpportunityAccessEO.sales_lead_id IS NULL '||
651 ' AND OpportunityAccessEO.lead_id IS NOT NULL '||
652 ' AND jrb.category = ''EMPLOYEE'' '||
653 ' AND OpportunityAccessEO.sales_group_id = jrgt.GROUP_ID '||
654 ' AND jrgt.language = USERENV(''LANG'') '||
655 ' AND OpportunityAccessEO.lead_id = :l_lead_id ' ||
656 ' ORDER BY BIL_TX_MEASURE1 ' ;
657
658
659
660
661 x_custom_sql := l_custom_sql ;
662
663
664 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
665 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
666 p_proc => l_proc,
667 p_query => x_custom_sql);
668 END IF;
669
670 l_bind_ctr := 1;
671
672 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
673 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
674 l_custom_rec.attribute_value := l_viewby;
675 x_custom_attr.Extend();
676 x_custom_attr(l_bind_ctr):=l_custom_rec;
677 l_bind_ctr:=l_bind_ctr+1;
678
679 l_custom_rec.attribute_name :=':l_lead_id';
680 l_custom_rec.attribute_value :=l_lead_id;
681 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
682 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
683 x_custom_attr.Extend();
684 x_custom_attr(l_bind_ctr):=l_custom_rec;
685 l_bind_ctr:=l_bind_ctr+1;
686
687 l_custom_rec.attribute_name :=':l_rpt_str';
688 l_custom_rec.attribute_value :=l_rpt_str;
689 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
690 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
691 x_custom_attr.Extend();
692 x_custom_attr(l_bind_ctr):=l_custom_rec;
693 l_bind_ctr:=l_bind_ctr+1;
694
695
696 l_custom_rec.attribute_name :=':l_viewby';
697 l_custom_rec.attribute_value := l_viewby;
698 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
699 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
700 x_custom_attr.Extend();
701 x_custom_attr(l_bind_ctr):=l_custom_rec;
702 l_bind_ctr:=l_bind_ctr+1;
703
704 END SALES_TEAM_TAB;
705
706 PROCEDURE PARTNER_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
707 ,x_custom_sql OUT NOCOPY VARCHAR2
708 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
709
710
711 l_custom_rec BIS_QUERY_ATTRIBUTES;
712 l_region_id VARCHAR2(100);
713 l_parameter_valid BOOLEAN;
714 l_rpt_str VARCHAR2(180);
715 l_viewby VARCHAR2(180) ;
716 l_bind_ctr NUMBER;
717 l_proc VARCHAR2(100);
718 l_custom_sql VARCHAR2(32000);
719 l_outer_select VARCHAR2(4000);
720 g_pkg VARCHAR2(500);
721 l_view_param VARCHAR2(500);
722 l_page_period_type VARCHAR2(100);
723 l_lead_id VARCHAR2(100) ;
724 l_cust_id VARCHAR2(100);
725 l_credit_type_id VARCHAR2(100);
726
727
728 BEGIN
729
730 -- Initializing variables as per new standard
731
732 l_region_id := 'BIL_TX_OPTY_DETL_PTNR_RPT';
733 l_parameter_valid := FALSE;
734 l_rpt_str := 'BIL_TX_OPTY_DETL_PTNR_RPT_R';
735 l_proc := 'PARTNER_TAB.';
736 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
737
738
739
740 -- FND logging standard
741 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
742
743 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
744 p_module => g_pkg || l_proc || 'begin',
745 p_msg => 'Start of Procedure '|| l_proc );
746 END IF;
747
748 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
749 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
750
751
752 -- code for a procedure to get parameter values.
753 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
754 (p_page_parameter_tbl => p_page_parameter_tbl,
755 p_region_id => l_region_id,
756 x_parameter_valid => l_parameter_valid,
757 x_viewby => l_viewby,
758 x_lead_id => l_lead_id,
759 x_cust_id => l_cust_id,
760 x_credit_type_id => l_credit_type_id
761 ) ;
762
766 * BIL_TX_MEASURE2 = Partner Customer Id
763 /*** Query column mapping ******************************************************
764
765 * BIL_TX_MEASURE1 = Lead Id
767 * BIL_TX_MEASURE3 = Partner Name
768 * BIL_TX_MEASURE4 = Address
769 * BIL_TX_MEASURE5 = Level
770 * BIL_TX_MEASURE6 = Type
771 * BIL_TX_MEASURE7 = Last Offered Date
772 * BIL_TX_MEASURE8 = Prefereed
773 * BIL_TX_MEASURE9 = Contact
774 * BIL_TX_MEASURE10 = Assignment Status
775
776 *******************************************************************************/
777
778
779 l_custom_sql := ' SELECT PvExternalSalesteamEO.lead_id BIL_TX_MEASURE1, '||
780 ' PvExternalSalesteamEO.PARTNER_CUSTOMER_ID BIL_TX_MEASURE2, '||
781 ' PARTNER.PARTY_NAME BIL_TX_MEASURE3, '||
782 ' ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(Null, HZL.ADDRESS1, HZL.ADDRESS2, HZL.ADDRESS3, HZL.ADDRESS4, HZL.CITY, HZL.COUNTY, '||
783 ' HZL.STATE, HZL.PROVINCE, HZL.POSTAL_CODE, HZL.COUNTRY, HZL.COUNTRY, Null, Null, Null, Null, Null, Null, NULL, NULL, 2000, '||
784 ' 1, 1) BIL_TX_MEASURE4, '||
785 ' T.DESCRIPTION BIL_TX_MEASURE5, '||
786 ' PV_MATCH_V3_PUB.get_partner_types(PvExternalSalesteamEO.PARTNER_CUSTOMER_ID) BIL_TX_MEASURE6, '||
787 ' to_char(PVPP.oppty_last_offered_date, FND_PROFILE.VALUE(''ICX_DATE_FORMAT_MASK'')) BIL_TX_MEASURE7, '||
788 ' DECODE( PV_MATCH_V3_PUB.pref_partner_flag(PvExternalSalesteamEO.LEAD_ID, PvExternalSalesteamEO.PARTNER_CUSTOMER_ID), ''Y'', ''bischeck.gif'',NULL) BIL_TX_MEASURE8, '||
789 ' Decode(PARTNER.primary_phone_country_code,NULL, '''',PARTNER.primary_phone_country_code||''-'')|| Decode(PARTNER.primary_phone_area_code, '||
790 ' NULL, '''',PARTNER.primary_phone_area_code||''-'')|| DECODE(PARTNER.primary_phone_number, NULL, '''', '||
791 ' PARTNER.primary_phone_number||''-'')|| DECODE(PARTNER.primary_phone_extension, NULL, '''',PARTNER.primary_phone_extension) BIL_TX_MEASURE9, '||
792 ' PV_MATCH_V3_PUB.get_assign_status_meaning( PvExternalSalesteamEO.lead_id, PvExternalSalesteamEO.PARTNER_CUSTOMER_ID) BIL_TX_MEASURE10 '||
793 ' FROM AS_ACCESSES_ALL PvExternalSalesteamEO, '||
794 ' HZ_PARTIES PARTNER, '||
795 ' PV_PARTNER_PROFILES PVPP, '||
796 ' HZ_PARTY_SITES HZPS, '||
797 ' HZ_LOCATIONS HZL, '||
798 ' FND_LOOKUP_VALUES fndlv , '||
799 ' PV_ATTRIBUTE_CODES_TL T , '||
800 ' PV_ATTRIBUTE_CODES_B B '||
801 ' WHERE PVPP.PARTNER_ID = PvExternalSalesteamEO.PARTNER_CUSTOMER_ID '||
802 ' AND PVPP.PARTNER_RESOURCE_ID = PvExternalSalesteamEO.SALESFORCE_ID '||
803 ' AND PVPP.PARTNER_PARTY_ID = PARTNER.party_id '||
804 ' AND HZPS.party_site_id(+) = PvExternalSalesteamEO.PARTNER_ADDRESS_ID '||
805 ' AND HZPS.location_id = HZL.location_id (+) '||
806 ' AND fndlv.lookup_code(+) = PARTNER.certification_level '||
807 ' AND fndlv.lookup_type(+) = ''HZ_PARTY_CERT_LEVEL'' '||
808 ' AND fndlv.LANGUAGE(+) = USERENV(''LANG'') '||
809 ' AND PvExternalSalesteamEO.PERSON_ID IS NULL '||
810 ' AND PvExternalSalesteamEO.PARTNER_CONT_PARTY_ID IS NULL '||
811 ' AND PvExternalSalesteamEO.LEAD_ID = :l_lead_id '||
812 ' AND PVPP.partner_level = T.ATTR_CODE_ID(+) '|| -- changed code
813 ' AND B.ATTR_CODE_ID(+) = T.ATTR_CODE_ID '||
814 ' AND T.LANGUAGE(+) = userenv(''LANG'') '||
815 ' AND B.attribute_id(+) = 19 '||
816 ' AND B.ENABLED_FLAG(+) = ''Y'' '||
817 ' ORDER BY BIL_TX_MEASURE3 ' ;
818
819
820
821 x_custom_sql := l_custom_sql ;
822
823 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
824 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
825 p_proc => l_proc,
826 p_query => x_custom_sql);
827 END IF;
828
829
830 l_bind_ctr := 1;
831
832 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
833 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
834 l_custom_rec.attribute_value := l_viewby;
835 x_custom_attr.Extend();
836 x_custom_attr(l_bind_ctr):=l_custom_rec;
837 l_bind_ctr:=l_bind_ctr+1;
838
839 l_custom_rec.attribute_name :=':l_lead_id';
840 l_custom_rec.attribute_value :=l_lead_id;
841 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
842 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
843 x_custom_attr.Extend();
844 x_custom_attr(l_bind_ctr):=l_custom_rec;
845 l_bind_ctr:=l_bind_ctr+1;
846
847 l_custom_rec.attribute_name :=':l_rpt_str';
848 l_custom_rec.attribute_value :=l_rpt_str;
849 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
850 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
851 x_custom_attr.Extend();
852 x_custom_attr(l_bind_ctr):=l_custom_rec;
853 l_bind_ctr:=l_bind_ctr+1;
854
855
856 l_custom_rec.attribute_name :=':l_viewby';
857 l_custom_rec.attribute_value := l_viewby;
858 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
859 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
860 x_custom_attr.Extend();
861 x_custom_attr(l_bind_ctr):=l_custom_rec;
862 l_bind_ctr:=l_bind_ctr+1;
863
864 END PARTNER_TAB;
865
866
867 PROCEDURE CONTACTS_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
868 ,x_custom_sql OUT NOCOPY VARCHAR2
869 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
870
871
872 l_custom_rec BIS_QUERY_ATTRIBUTES;
873 l_region_id VARCHAR2(100);
874 l_parameter_valid BOOLEAN;
875 l_rpt_str VARCHAR2(180);
876 l_viewby VARCHAR2(180) ;
880 l_outer_select VARCHAR2(4000);
877 l_bind_ctr NUMBER;
878 l_proc VARCHAR2(100);
879 l_custom_sql VARCHAR2(32000);
881 g_pkg VARCHAR2(500);
882 l_view_param VARCHAR2(500);
883 l_page_period_type VARCHAR2(100);
884 l_lead_id VARCHAR2(100) ;
885 l_customer_id VARCHAR2(100);
886 l_credit_type_id VARCHAR2(100);
887
888
889 BEGIN
890
891 -- Initializing variables as per new standard
892
893 l_region_id := 'BIL_TX_OPTY_DETL_CONT_RPT';
894 l_parameter_valid := FALSE;
895 l_rpt_str := 'BIL_TX_OPTY_DETL_CONT_RPT_R';
896 l_proc := 'CONTACTS_TAB.';
897 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
898
899
900 -- FND logging standard
901 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
902
903 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
904 p_module => g_pkg || l_proc || 'begin',
905 p_msg => 'Start of Procedure '|| l_proc );
906 END IF;
907
908 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
909 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
910
911
912 -- code for a procedure to get parameter values.
913 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
914 (p_page_parameter_tbl => p_page_parameter_tbl,
915 p_region_id => l_region_id,
916 x_parameter_valid => l_parameter_valid,
917 x_viewby => l_viewby,
918 x_lead_id => l_lead_id,
919 x_cust_id => l_customer_id,
920 x_credit_type_id => l_credit_type_id
921 ) ;
922
923 /*** Query column mapping ******************************************************
924
925 * BIL_TX_MEASURE1 = Party Id
926 * BIL_TX_MEASURE2 = Name
927 * BIL_TX_MEASURE3 = Job Title
928 * BIL_TX_MEASURE4 = Phone
929 * BIL_TX_MEASURE5 = Email
930 * BIL_TX_MEASURE6 = Role (still to fix this)
931 * BIL_TX_MEASURE7 = SUBJECT ID
932 * BIL_TX_MEASURE8 = Object ID
933 * BIL_TX_MEASURE9 = Party ID
934 * BIL_TX_MEASURE10 = Relationship ID
935
936 *******************************************************************************/
937
938
939 l_custom_sql := ' SELECT hzpt.party_id BIL_TX_MEASURE1, '||
940 ' hzpt.party_name BIL_TX_MEASURE2, '||
941 ' hoc1.job_title BIL_TX_MEASURE3, '||
942 ' DECODE(hoc.phone_country_code,NULL,'''', hoc.phone_country_code || ''-'') '||
943 ' || DECODE(hoc.phone_area_code,NULL,'''',hoc.phone_area_code|| ''-'') '||
944 ' || DECODE(hoc.phone_number,NULL,'''',hoc.phone_number) '||
945 ' || DECODE(hoc.phone_extension,NULL,'''',''x'' ||hoc.phone_extension) BIL_TX_MEASURE4, '||
946 ' hzpt.EMAIL_ADDRESS BIL_TX_MEASURE5, '||
947 ' fndl.meaning BIL_TX_MEASURE6, '||
948 ' hr.subject_id BIL_TX_MEASURE7, '||
949 ' hr.object_id BIL_TX_MEASURE8, '||
950 ' hr.party_id BIL_TX_MEASURE9, '||
951 ' hr.relationship_id BIL_TX_MEASURE10 '||
952 ' FROM as_lead_contacts_all alca, '||
953 ' hz_contact_points hoc, '||
954 ' hz_relationships hr, '||
955 ' hz_parties hzpt, '||
956 ' hz_org_contacts hoc1, '||
957 ' fnd_lookups fndl '||
958 ' WHERE alca.contact_party_id = hoc.owner_table_id(+) '||
959 ' AND hoc.owner_table_name(+) = ''HZ_PARTIES'' '||
960 ' AND hoc.primary_flag(+) = ''Y'' '||
961 ' AND hoc.contact_point_type(+) = ''PHONE'' '||
962 ' AND alca.contact_party_id = hr.party_id '||
963 ' AND alca.customer_id = hr.object_id '||
964 ' AND hr.object_table_name = ''HZ_PARTIES'' '||
965 ' AND hzpt.party_id = alca.contact_party_id '||
966 ' AND hr.relationship_id = hoc1.party_relationship_id (+) '||
967 ' AND alca.rank = fndl.lookup_code (+) '||
968 ' AND fndl.lookup_type(+) = ''ASN_CONTACT_ROLE'' '||
969 ' AND alca.lead_id = :l_lead_id '||
970 ' ORDER BY BIL_TX_MEASURE2 ';
971
972
973 x_custom_sql := l_custom_sql ;
974
975 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
976 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
977 p_proc => l_proc,
978 p_query => x_custom_sql);
979 END IF;
980
981
982 l_bind_ctr := 1;
983
984 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
985 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
986 l_custom_rec.attribute_value := l_viewby;
987 x_custom_attr.Extend();
988 x_custom_attr(l_bind_ctr):=l_custom_rec;
989 l_bind_ctr:=l_bind_ctr+1;
990
991 l_custom_rec.attribute_name :=':l_lead_id';
992 l_custom_rec.attribute_value :=l_lead_id;
993 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
994 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
995 x_custom_attr.Extend();
996 x_custom_attr(l_bind_ctr):=l_custom_rec;
997 l_bind_ctr:=l_bind_ctr+1;
998
999 l_custom_rec.attribute_name :=':l_customer_id';
1000 l_custom_rec.attribute_value :=l_customer_id;
1001 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1002 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1003 x_custom_attr.Extend();
1004 x_custom_attr(l_bind_ctr):=l_custom_rec;
1005 l_bind_ctr:=l_bind_ctr+1;
1006
1010 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1007 l_custom_rec.attribute_name :=':l_rpt_str';
1008 l_custom_rec.attribute_value :=l_rpt_str;
1009 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1011 x_custom_attr.Extend();
1012 x_custom_attr(l_bind_ctr):=l_custom_rec;
1013 l_bind_ctr:=l_bind_ctr+1;
1014
1015
1016 l_custom_rec.attribute_name :=':l_viewby';
1017 l_custom_rec.attribute_value := l_viewby;
1018 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1019 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1020 x_custom_attr.Extend();
1021 x_custom_attr(l_bind_ctr):=l_custom_rec;
1022 l_bind_ctr:=l_bind_ctr+1;
1023
1024 END CONTACTS_TAB;
1025
1026
1027 PROCEDURE PROPOSAL_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1028 ,x_custom_sql OUT NOCOPY VARCHAR2
1029 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1030
1031
1032 l_custom_rec BIS_QUERY_ATTRIBUTES;
1033 l_region_id VARCHAR2(100);
1034 l_parameter_valid BOOLEAN;
1035 l_rpt_str VARCHAR2(180);
1036 l_viewby VARCHAR2(180) ;
1037 l_bind_ctr NUMBER;
1038 l_proc VARCHAR2(100);
1039 l_custom_sql VARCHAR2(32000);
1040 l_outer_select VARCHAR2(4000);
1041 g_pkg VARCHAR2(500);
1042 l_view_param VARCHAR2(500);
1043 l_page_period_type VARCHAR2(100);
1044 l_lead_id VARCHAR2(100) ;
1045 l_cust_id VARCHAR2(100);
1046 l_credit_type_id VARCHAR2(100);
1047
1048
1049 BEGIN
1050
1051 -- Initializing variables as per new standard
1052
1053 l_region_id := 'BIL_TX_OPTY_DETL_PROP_RPT';
1054 l_parameter_valid := FALSE;
1055 l_rpt_str := 'BIL_TX_OPTY_DETL_PROP_RPT_R';
1056 l_proc := 'PROPOSAL_TAB.';
1057 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
1058
1059
1060
1061 -- FND logging standard
1062 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1063
1064 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
1065 p_module => g_pkg || l_proc || 'begin',
1066 p_msg => 'Start of Procedure '|| l_proc );
1067 END IF;
1068
1069 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1070 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1071
1072
1073 -- code for a procedure to get parameter values.
1074 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
1075 (p_page_parameter_tbl => p_page_parameter_tbl,
1076 p_region_id => l_region_id,
1077 x_parameter_valid => l_parameter_valid,
1078 x_viewby => l_viewby,
1079 x_lead_id => l_lead_id,
1080 x_cust_id => l_cust_id,
1081 x_credit_type_id => l_credit_type_id
1082 ) ;
1083
1084 /*** Query column mapping ******************************************************
1085
1086 * BIL_TX_MEASURE1 = Proposal Id
1087 * BIL_TX_MEASURE2 = Proposal Name
1088 * BIL_TX_MEASURE3 = Description
1089 * BIL_TX_MEASURE4 = Owner
1090 * BIL_TX_MEASURE5 = Due Date
1091 * BIL_TX_MEASURE6 = Status
1092
1093 *******************************************************************************/
1094
1095
1096
1097 l_custom_sql := ' SELECT pp.proposal_id BIL_TX_MEASURE1, '||
1098 ' pp.proposal_name BIL_TX_MEASURE2, '||
1099 ' pp.proposal_desc BIL_TX_MEASURE3, '||
1100 ' jrt.resource_name BIL_TX_MEASURE4, '||
1101 ' pp.due_date BIL_TX_MEASURE5, '||
1102 ' fl.meaning BIL_TX_MEASURE6 '||
1103 ' FROM prp_proposals pp, '||
1104 ' prp_proposal_objects ppo, '||
1105 ' fnd_lookups fl, '||
1106 ' jtf_rs_resource_extns jrb, '||
1107 ' jtf_rs_resource_extns_tl jrt '||
1108 ' WHERE pp.proposal_status = fl.lookup_code '||
1109 ' AND fl.lookup_type = ''PRP_PROPOSAL_STATUS'' '||
1110 ' AND ppo.object_type = ''OPPORTUNITY'' '||
1111 ' AND ppo.proposal_id = pp.proposal_id '||
1112 ' AND pp.user_id = jrb.user_id(+) '||
1113 ' AND jrb.category = jrt.category(+) '||
1114 ' AND jrb.resource_id = jrt.resource_id (+) '||
1115 ' AND jrt.language(+) = USERENV(''LANG'') '||
1116 ' AND ppo.object_id = :l_lead_id ' ||
1117 ' ORDER BY BIL_TX_MEASURE2 ';
1118
1119
1120 x_custom_sql := l_custom_sql ;
1121
1122 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1123 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
1124 p_proc => l_proc,
1125 p_query => x_custom_sql);
1126 END IF;
1127
1128
1129 l_bind_ctr := 1;
1130
1131 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1132 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1133 l_custom_rec.attribute_value := l_viewby;
1134 x_custom_attr.Extend();
1135 x_custom_attr(l_bind_ctr):=l_custom_rec;
1136 l_bind_ctr:=l_bind_ctr+1;
1137
1141 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1138 l_custom_rec.attribute_name :=':l_lead_id';
1139 l_custom_rec.attribute_value :=l_lead_id;
1140 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1142 x_custom_attr.Extend();
1143 x_custom_attr(l_bind_ctr):=l_custom_rec;
1144 l_bind_ctr:=l_bind_ctr+1;
1145
1146 l_custom_rec.attribute_name :=':l_rpt_str';
1147 l_custom_rec.attribute_value :=l_rpt_str;
1148 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1149 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1150 x_custom_attr.Extend();
1151 x_custom_attr(l_bind_ctr):=l_custom_rec;
1152 l_bind_ctr:=l_bind_ctr+1;
1153
1154
1155 l_custom_rec.attribute_name :=':l_viewby';
1156 l_custom_rec.attribute_value := l_viewby;
1157 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1158 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1159 x_custom_attr.Extend();
1160 x_custom_attr(l_bind_ctr):=l_custom_rec;
1161 l_bind_ctr:=l_bind_ctr+1;
1162
1163 END PROPOSAL_TAB;
1164
1165
1166
1167 PROCEDURE QUOTE_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1168 ,x_custom_sql OUT NOCOPY VARCHAR2
1169 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1170
1171
1172
1173 l_custom_rec BIS_QUERY_ATTRIBUTES;
1174 l_region_id VARCHAR2(50);
1175 l_parameter_valid BOOLEAN;
1176 l_rpt_str VARCHAR2(80);
1177 l_viewby VARCHAR2(80) ;
1178 l_bind_ctr NUMBER;
1179 l_proc VARCHAR2(100);
1180 l_custom_sql VARCHAR2(32000);
1181 l_outer_select VARCHAR2(4000);
1182 g_pkg VARCHAR2(100);
1183 l_where_clause VARCHAR2(1000);
1184 l_lead_id VARCHAR2(100) ;
1185 l_cust_id VARCHAR2(100);
1186 l_credit_type_id VARCHAR2(100);
1187
1188
1189 BEGIN
1190
1191
1192
1193 -- Initializing variables as per new standard
1194
1195 l_region_id := 'BIL_TX_OPTY_DETL_QUOTE_RPT';
1196 l_parameter_valid := FALSE;
1197 l_rpt_str := 'BIL_TX_OPTY_DETL_QUOTE_RPT_R';
1198 l_proc := 'QUOTE_TAB.';
1199 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
1200
1201
1202 -- FND logging standard
1203 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1204
1205 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
1206 p_module => g_pkg || l_proc || 'begin',
1207 p_msg => 'Start of Procedure '|| l_proc );
1208 END IF;
1209
1210 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1211 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1212
1213 -- code for a procedure to get parameter values.
1214 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
1215 (p_page_parameter_tbl => p_page_parameter_tbl,
1216 p_region_id => l_region_id,
1217 x_parameter_valid => l_parameter_valid,
1218 x_viewby => l_viewby,
1219 x_lead_id => l_lead_id,
1220 x_cust_id => l_cust_id,
1221 x_credit_type_id => l_credit_type_id
1222 ) ;
1223
1224
1225
1226 /*** Query column mapping ******************************************************
1227
1228 * BIL_TX_MEASURE1 = Quote name
1229 * BIL_TX_MEASURE2 = Quote header Id
1230 * BIL_TX_MEASURE3 = Quote Number
1231 * BIL_TX_MEASURE4 = Owner
1232 * BIL_TX_MEASURE5 = Quote status
1233 * BIL_TX_MEASURE6 = Creation Date
1234 * BIL_TX_MEASURE7 = Expiration
1235 * BIL_TX_MEASURE8 = Amount
1236 * BIL_TX_MEASURE9 = currency code
1237 * BIL_TX_MEASURE10 = currency name
1238 * BIL_TX_MEASURE11 = order number
1239
1240 *******************************************************************************/
1241
1242
1243
1244 -- query complete pass lead _id
1245 l_custom_sql := ' SELECT aqha.quote_name BIL_TX_MEASURE1, '||
1246 ' aqha.quote_header_id BIL_TX_MEASURE2, '||
1247 ' aqha.quote_number BIL_TX_MEASURE3, '||
1248 ' jrst.resource_name BIL_TX_MEASURE4, '||
1249 ' aqst.meaning BIL_TX_MEASURE5 , '||
1250 ' aqha.creation_date BIL_TX_MEASURE6, '||
1251 ' aqha.quote_expiration_date BIL_TX_MEASURE7, '||
1252 ' aqha.total_quote_price BIL_TX_MEASURE8, '||
1253 ' aqha.currency_code BIL_TX_MEASURE9, '||
1254 ' fct.name BIL_TX_MEASURE10, '||
1255 ' aqha.order_id BIL_TX_MEASURE11 '||
1256 ' FROM aso_quote_related_objects aqro, '||
1257 ' aso_quote_headers_all aqha, '||
1258 ' aso_quote_statuses_tl aqst, '||
1259 ' jtf_rs_resource_extns_tl jrst, '||
1260 ' fnd_currencies_tl fct '||
1261 'WHERE aqro.quote_object_type_code = ''HEADER'' '||
1262 ' AND aqro.relationship_type_code = ''OPP_QUOTE'' '||
1263 ' AND aqro.quote_object_id = aqha.quote_header_id '||
1264 ' AND aqha.quote_status_id = aqst.quote_status_id '||
1265 ' AND aqst.language = USERENV(''LANG'') '||
1266 ' AND aqha.resource_id = jrst.resource_id '||
1267 ' AND jrst.language = aqst.language '||
1268 ' AND aqha.resource_id IS NOT NULL '||
1269 ' AND NVL(aqha.quote_type, ''Q'') = ''Q'' '||
1273 -- ' AND NVL(aqha.org_id, NVL(TO_NUMBER(DECODE(SUBSTRB '||
1270 ' AND NVL(aqha.max_version_flag,''Y'') = ''Y'' '||
1271 ' AND aqha.currency_code = fct.currency_code '||
1272 ' AND fct.language = aqst.language '||
1274 -- ' (USERENV(''CLIENT_INFO''), 1 , 1), '''', NULL, '||
1275 -- ' SUBSTRB(USERENV(''CLIENT_INFO''), 1, 10))), -99)) = '||
1276 -- ' NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV(''CLIENT_INFO''), 1, 1), '''', NULL, '||
1277 -- ' SUBSTRB(USERENV(''CLIENT_INFO''), 1, 10))), -99) '||
1278 ' AND aqro.object_id = :l_lead_id ' ||
1279 ' ORDER BY BIL_TX_MEASURE1 ';
1280
1281
1282
1283 x_custom_sql := l_custom_sql ;
1284
1285 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1286 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
1287 p_proc => l_proc,
1288 p_query => x_custom_sql);
1289 END IF;
1290
1291
1292 l_bind_ctr := 1;
1293
1294 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1295 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1296 l_custom_rec.attribute_value := l_viewby;
1297 x_custom_attr.Extend();
1298 x_custom_attr(l_bind_ctr):=l_custom_rec;
1299 l_bind_ctr:=l_bind_ctr+1;
1300
1301
1302 l_custom_rec.attribute_name :=':l_rpt_str';
1303 l_custom_rec.attribute_value :=l_rpt_str;
1304 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1305 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1306 x_custom_attr.Extend();
1307 x_custom_attr(l_bind_ctr):=l_custom_rec;
1308 l_bind_ctr:=l_bind_ctr+1;
1309
1310 l_custom_rec.attribute_name :=':l_lead_id';
1311 l_custom_rec.attribute_value :=l_lead_id;
1312 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1313 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1314 x_custom_attr.Extend();
1315 x_custom_attr(l_bind_ctr):=l_custom_rec;
1316 l_bind_ctr:=l_bind_ctr+1;
1317
1318 l_custom_rec.attribute_name :=':l_viewby';
1319 l_custom_rec.attribute_value := l_viewby;
1320 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1321 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1322 x_custom_attr.Extend();
1323 x_custom_attr(l_bind_ctr):=l_custom_rec;
1324 l_bind_ctr:=l_bind_ctr+1;
1325
1326 END QUOTE_TAB;
1327
1328 -- Except for passing param it is complete.
1329
1330 PROCEDURE PROJECTS_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1331 ,x_custom_sql OUT NOCOPY VARCHAR2
1332 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1333
1334
1335
1336 l_custom_rec BIS_QUERY_ATTRIBUTES;
1337 l_region_id VARCHAR2(50);
1338 l_parameter_valid BOOLEAN;
1339 l_rpt_str VARCHAR2(80);
1340 l_viewby VARCHAR2(80) ;
1341 l_bind_ctr NUMBER;
1342 l_proc VARCHAR2(100);
1343 l_custom_sql VARCHAR2(32000);
1344 l_outer_select VARCHAR2(4000);
1345 g_pkg VARCHAR2(100);
1346 l_where_clause VARCHAR2(1000);
1347 l_lead_id VARCHAR2(100) ;
1348 l_cust_id VARCHAR2(100);
1349 l_credit_type_id VARCHAR2(100);
1350
1351
1352 BEGIN
1353
1354
1355
1356 -- Initializing variables as per new standard
1357
1358 l_region_id := 'BIL_TX_OPTY_DETL_PROJ_RPT';
1359 l_parameter_valid := FALSE;
1360 l_rpt_str := 'BIL_TX_OPTY_DETL_PROJ_RPT_R';
1361 l_proc := 'PROJECTS_TAB.';
1362 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
1363
1364
1365 -- FND logging standard
1366 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1367
1368 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
1369 p_module => g_pkg || l_proc || 'begin',
1370 p_msg => 'Start of Procedure '|| l_proc );
1371 END IF;
1372
1373 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1374 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1375
1376 -- code for a procedure to get parameter values.
1377 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
1378 (p_page_parameter_tbl => p_page_parameter_tbl,
1379 p_region_id => l_region_id,
1380 x_parameter_valid => l_parameter_valid,
1381 x_viewby => l_viewby,
1382 x_lead_id => l_lead_id,
1383 x_cust_id => l_cust_id,
1384 x_credit_type_id => l_credit_type_id
1385 ) ;
1386
1387
1388
1389 /*** Query column mapping ******************************************************
1390
1391 * BIL_TX_MEASURE1 = Segment Id
1392 * BIL_TX_MEASURE2 = Project name
1393 * BIL_TX_MEASURE3 = Project Number
1394 * BIL_TX_MEASURE4 = Organization Id
1395 * BIL_TX_MEASURE5 = Organization Name
1396 * BIL_TX_MEASURE6 = Manager ID
1397 * BIL_TX_MEASURE7 = Manager
1398 * BIL_TX_MEASURE8 = Start Date
1399 * BIL_TX_MEASURE9 = Completion Date
1400 * BIL_TX_MEASURE10 = Project Type
1401 * BIL_TX_MEASURE11 = status code
1402 * BIL_TX_MEASURE12 = Status Name
1403
1404
1405 *******************************************************************************/
1406
1410 ' ppa.carrying_out_organization_id BIL_TX_MEASURE4, '||
1407 l_custom_sql := ' SELECT ppa.segment1 BIL_TX_MEASURE1, '||
1408 ' ppa.name BIL_TX_MEASURE2, '||
1409 ' ppa.project_id BIL_TX_MEASURE3, '||
1411 ' hou.name BIL_TX_MEASURE5, '||
1412 ' ppp.person_id BIL_TX_MEASURE6, '||
1413 ' ppf.full_name BIL_TX_MEASURE7, '||
1414 ' ppa.start_date BIL_TX_MEASURE8, '||
1415 ' ppa.completion_date BIL_TX_MEASURE9, '||
1416 ' ppa.project_type BIL_TX_MEASURE10, '||
1417 ' ppa.project_status_code BIL_TX_MEASURE11, '||
1418 ' pps.project_status_name BIL_TX_MEASURE12 '||
1419 ' FROM pa_projects_all ppa , '||
1420 ' hr_all_organization_units_tl hou , '||
1421 ' pa_project_statuses pps, '||
1422 ' pa_project_players ppp , '||
1423 ' per_people_f ppf '||
1424 ' WHERE ppa.carrying_out_organization_id = hou.organization_id '||
1425 ' AND hou.language = userenv(''lang'') '||
1426 ' AND ppa.project_status_code = pps.project_status_code '||
1427 ' AND ppa.project_id = ppp.project_id '||
1428 ' AND ppp.project_role_type = ''PROJECT MANAGER'' '||
1429 ' AND ppp.person_id = ppf.person_id '||
1430 ' AND (trunc(sysdate) >= ppf.effective_start_date '||
1431 ' AND trunc(sysdate) <= ppf.effective_end_date) '||
1432 ' AND ppa.project_id in ( SELECT object_id_to1 '||
1433 ' FROM ( SELECT object_type_to, '||
1434 ' object_id_to1 '||
1435 ' FROM pa_object_relationships '||
1436 ' WHERE relationship_type = ''A'' '||
1437 ' AND relationship_subtype = ''PROJECT_REQUEST'' '||
1438 ' START WITH (object_type_from = ''AS_LEADS'' '||
1439 ' AND object_id_from1 = :l_lead_id) '||
1440 ' CONNECT BY (prior object_id_to1 = object_id_from1 '||
1441 ' AND prior object_type_to = object_type_from '||
1442 ' AND prior object_id_from1 <> object_id_to1)) a '||
1443 ' WHERE a.object_type_to = ''PA_PROJECTS'') ' ||
1444 ' ORDER BY BIL_TX_MEASURE2 ';
1445
1446
1447 x_custom_sql := l_custom_sql ;
1448
1449 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1450 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
1451 p_proc => l_proc,
1452 p_query => x_custom_sql);
1453 END IF;
1454
1455
1456 l_bind_ctr := 1;
1457
1458 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1459 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1460 l_custom_rec.attribute_value := l_viewby;
1461 x_custom_attr.Extend();
1462 x_custom_attr(l_bind_ctr):=l_custom_rec;
1463 l_bind_ctr:=l_bind_ctr+1;
1464
1465
1466 l_custom_rec.attribute_name :=':l_rpt_str';
1467 l_custom_rec.attribute_value :=l_rpt_str;
1468 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1469 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1470 x_custom_attr.Extend();
1471 x_custom_attr(l_bind_ctr):=l_custom_rec;
1472 l_bind_ctr:=l_bind_ctr+1;
1473
1474
1475 l_custom_rec.attribute_name :=':l_viewby';
1476 l_custom_rec.attribute_value := l_viewby;
1477 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1478 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1479 x_custom_attr.Extend();
1480 x_custom_attr(l_bind_ctr):=l_custom_rec;
1481 l_bind_ctr:=l_bind_ctr+1;
1482
1483 l_custom_rec.attribute_name :=':l_lead_id';
1484 l_custom_rec.attribute_value := l_lead_id;
1485 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1486 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1487 x_custom_attr.Extend();
1488 x_custom_attr(l_bind_ctr):=l_custom_rec;
1489 l_bind_ctr:=l_bind_ctr+1;
1490
1491 END PROJECTS_TAB;
1492
1493 PROCEDURE TASKS_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1494 ,x_custom_sql OUT NOCOPY VARCHAR2
1495 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1496
1497
1498
1499 l_custom_rec BIS_QUERY_ATTRIBUTES;
1500 l_region_id VARCHAR2(50);
1501 l_parameter_valid BOOLEAN;
1502 l_rpt_str VARCHAR2(80);
1503 l_viewby VARCHAR2(80) ;
1504 l_bind_ctr NUMBER;
1505 l_proc VARCHAR2(100);
1506 l_custom_sql VARCHAR2(32000);
1507 l_outer_select VARCHAR2(4000);
1508 g_pkg VARCHAR2(100);
1509 l_where_clause VARCHAR2(1000);
1510 l_lead_id VARCHAR2(100) ;
1511 l_cust_id VARCHAR2(100);
1512 l_credit_type_id VARCHAR2(100);
1513
1514 BEGIN
1515
1516
1517
1518 -- Initializing variables as per new standard
1519
1520 l_region_id := 'BIL_TX_OPTY_DETL_TASK_RPT';
1521 l_parameter_valid := FALSE;
1522 l_rpt_str := 'BIL_TX_OPTY_DETL_TASK_RPT_R';
1523 l_proc := 'TASKS_TAB.';
1524 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
1525
1526
1527
1528 -- FND logging standard
1529 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1530
1531 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
1532 p_module => g_pkg || l_proc || 'begin',
1533 p_msg => 'Start of Procedure '|| l_proc );
1534 END IF;
1535
1536 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1540 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
1537 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1538
1539 -- code for a procedure to get parameter values.
1541 (p_page_parameter_tbl => p_page_parameter_tbl,
1542 p_region_id => l_region_id,
1543 x_parameter_valid => l_parameter_valid,
1544 x_viewby => l_viewby,
1545 x_lead_id => l_lead_id,
1546 x_cust_id => l_cust_id,
1547 x_credit_type_id => l_credit_type_id
1548 ) ;
1549
1550
1551
1552
1553 /*** Query column mapping ******************************************************
1554
1555 * BIL_TX_MEASURE1 = Task ID --
1556 * BIL_TX_MEASURE2 = Source Object ID --
1557 * BIL_TX_MEASURE3 = Subject -- Task Name
1558 * BIL_TX_MEASURE4 = Due Date
1559 * BIL_TX_MEASURE5 = Type
1560 * BIL_TX_MEASURE6 = Status
1561 * BIL_TX_MEASURE7 = Created By
1562 * BIL_TX_MEASURE8 = Owner
1563 * BIL_TX_MEASURE9 = Description --
1564 * BIL_TX_MEASURE10 = Assignee
1565
1566 *******************************************************************************/
1567 -- work on query
1568
1569
1570 l_custom_sql := ' SELECT TaskEO.TASK_ID BIL_TX_MEASURE1, '||
1571 ' TaskEO.SOURCE_OBJECT_ID BIL_TX_MEASURE2, '||
1572 ' jtl.TASK_NAME BIL_TX_MEASURE3, '||
1573 ' TaskEO.PLANNED_END_DATE BIL_TX_MEASURE4, '||
1574 ' tt.NAME BIL_TX_MEASURE5, '||
1575 ' ts.NAME BIL_TX_MEASURE6, '||
1576 ' Rscreator.SOURCE_NAME BIL_TX_MEASURE7, '||
1577 ' JTF_TASK_UTL.get_owner(TaskEO.OWNER_TYPE_CODE, TaskEO.OWNER_ID) BIL_TX_MEASURE8, '||
1578 ' jtl.DESCRIPTION BIL_TX_MEASURE9, '||
1579 ' JTF_TASK_UTL.get_owner(Assign.RESOURCE_TYPE_CODE, Assign.RESOURCE_ID) BIL_TX_MEASURE10 '||
1580 ' FROM jtf_tasks_b TaskEO, '||
1581 ' jtf_task_statuses_tl ts, '||
1582 ' jtf_task_types_tl tt, '||
1583 ' jtf_tasks_tl jtl, '||
1584 ' JTF_RS_RESOURCE_EXTNS Rscreator, '||
1585 ' JTF_TASK_ALL_ASSIGNMENTS Assign '||
1586 ' WHERE Assign.task_id (+) = TaskEO.task_id '||
1587 ' AND TaskEO.created_by = Rscreator.user_id(+) '||
1588 ' AND TaskEO.entity = ''TASK'' '||
1589 ' AND TaskEO.source_object_type_code in (select object_code from jtf_objects_b where enter_from_task = ''Y'') '||
1590 ' AND NVL(TaskEO.deleted_flag,''N'') = ''N'' '||
1591 ' AND TaskEO.task_id = jtl.task_id '||
1592 ' AND jtl.language = USERENV(''LANG'') '||
1593 ' AND TaskEO.task_status_id = ts.task_status_id '||
1594 ' AND ts.language = userenv(''LANG'') '||
1595 ' AND TaskEO.task_type_id = tt.task_type_id '||
1596 ' AND tt.language = userenv(''LANG'') '||
1597 ' AND TaskEO.SOURCE_OBJECT_ID = :l_lead_id ' ||
1598 ' ORDER BY BIL_TX_MEASURE3 ';
1599
1600
1601
1602
1603
1604
1605 x_custom_sql := l_custom_sql ;
1606
1607 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1608 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
1609 p_proc => l_proc,
1610 p_query => x_custom_sql);
1611 END IF;
1612
1613
1614 l_bind_ctr := 1;
1615
1616 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1617 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1618 l_custom_rec.attribute_value := l_viewby;
1619 x_custom_attr.Extend();
1620 x_custom_attr(l_bind_ctr):=l_custom_rec;
1621 l_bind_ctr:=l_bind_ctr+1;
1622
1623
1624 l_custom_rec.attribute_name :=':l_rpt_str';
1625 l_custom_rec.attribute_value :=l_rpt_str;
1626 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1627 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1628 x_custom_attr.Extend();
1629 x_custom_attr(l_bind_ctr):=l_custom_rec;
1630 l_bind_ctr:=l_bind_ctr+1;
1631
1632 l_custom_rec.attribute_name :=':l_lead_id';
1633 l_custom_rec.attribute_value := l_lead_id;
1634 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1635 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1636 x_custom_attr.Extend();
1637 x_custom_attr(l_bind_ctr):=l_custom_rec;
1638 l_bind_ctr:=l_bind_ctr+1;
1639
1640
1641 l_custom_rec.attribute_name :=':l_viewby';
1642 l_custom_rec.attribute_value := l_viewby;
1643 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1644 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1645 x_custom_attr.Extend();
1646 x_custom_attr(l_bind_ctr):=l_custom_rec;
1647 l_bind_ctr:=l_bind_ctr+1;
1648
1649 END TASKS_TAB;
1650
1651 PROCEDURE ATTACHMENTS_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1652 ,x_custom_sql OUT NOCOPY VARCHAR2
1653 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1654
1655
1656
1657 l_custom_rec BIS_QUERY_ATTRIBUTES;
1658 l_region_id VARCHAR2(50);
1659 l_parameter_valid BOOLEAN;
1660 l_rpt_str VARCHAR2(80);
1661 l_viewby VARCHAR2(80) ;
1662 l_bind_ctr NUMBER;
1663 l_proc VARCHAR2(100);
1664 l_custom_sql VARCHAR2(32000);
1668 l_lead_id VARCHAR2(100) ;
1665 l_outer_select VARCHAR2(4000);
1666 g_pkg VARCHAR2(100);
1667 l_where_clause VARCHAR2(1000);
1669 l_cust_id VARCHAR2(100);
1670 l_to_currency VARCHAR2(100);
1671 l_period_type VARCHAR2(100);
1672 l_period_name VARCHAR2(100);
1673 l_credit_type_id VARCHAR2(100);
1674 l_sg_id VARCHAR2(200);
1675
1676
1677 BEGIN
1678
1679
1680
1681 -- Initializing variables as per new standard
1682
1683 l_region_id := 'BIL_TX_OPTY_DETL_ATCH_RPT';
1684 l_parameter_valid := FALSE;
1685 l_rpt_str := 'BIL_TX_OPTY_DETL_ATCH_RPT_R';
1686 l_proc := 'ATTACHMENTS_TAB.';
1687 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
1688
1689
1690 -- FND logging standard
1691 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1692
1693 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
1694 p_module => g_pkg || l_proc || 'begin',
1695 p_msg => 'Start of Procedure '|| l_proc );
1696 END IF;
1697
1698 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1699 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1700
1701 -- code for a procedure to get parameter values.
1702 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
1703 (p_page_parameter_tbl => p_page_parameter_tbl,
1704 p_region_id => l_region_id,
1705 x_parameter_valid => l_parameter_valid,
1706 x_viewby => l_viewby,
1707 x_lead_id => l_lead_id,
1708 x_cust_id => l_cust_id,
1709 x_credit_type_id => l_credit_type_id
1710 ) ;
1711
1712
1713
1714 /*** Query column mapping ******************************************************
1715
1716 * BIL_TX_MEASURE1 = Attached Document ID
1717 * BIL_TX_MEASURE2 = Document Id
1718 * BIL_TX_MEASURE3 = Name
1719 * BIL_TX_MEASURE4 = Description
1720 * BIL_TX_MEASURE5 = Attachment Type
1721 * BIL_TX_MEASURE6 = Category
1722 * BIL_TX_MEASURE7 = Updated By
1723 * BIL_TX_MEASURE8 = Updated Date
1724 * BIL_TX_MEASURE9 = Usage
1725
1726 *******************************************************************************/
1727
1728
1729 l_custom_sql := ' SELECT ad.ATTACHED_DOCUMENT_ID BIL_TX_MEASURE1, '||
1730 ' ad.DOCUMENT_ID BIL_TX_MEASURE2, '||
1731 ' decode(d.FILE_NAME, null, (select message_text from fnd_new_messages '||
1732 ' where message_name = ''FND_UNDEFINED'' '||
1733 ' and application_id = 0 and language_code = userenv(''LANG'')), D.FILE_NAME) BIL_TX_MEASURE3, '||
1734 ' dL.DESCRIPTION BIL_TX_MEASURE4, '||
1735 ' DD.USER_NAME BIL_TX_MEASURE5, '||
1736 ' cl.user_name BIL_TX_MEASURE6, '||
1737 ' u.USER_NAME BIL_TX_MEASURE7, '||
1738 ' ad.LAST_UPDATE_DATE BIL_TX_MEASURE8 , '||
1739 ' L.MEANING BIL_TX_MEASURE9 '||
1740 ' FROM FND_DOCUMENTS D , '||
1741 ' FND_DOCUMENTS_TL DL, '||
1742 ' FND_DOCUMENT_DATATYPES DD, '||
1743 ' FND_LOOKUP_VALUES L, '||
1744 ' FND_ATTACHED_DOCUMENTS ad, '||
1745 ' FND_USER u, '||
1746 ' FND_DOCUMENT_CATEGORIES_TL cl '||
1747 ' WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID '||
1748 ' and ad.LAST_UPDATED_BY = u.USER_ID(+) '||
1749 ' and cl.language = userenv(''LANG'') '||
1750 ' and cl.category_id = decode(ad.category_id, null, d.category_id, ad.category_id) '||
1751 ' and ad.ENTITY_NAME = ''AS_OPPORTUNITY_ATTCH'' '||
1752 ' and D.DOCUMENT_ID = DL.DOCUMENT_ID '||
1753 ' AND DL.LANGUAGE= USERENV(''LANG'') '||
1754 ' AND D.DATATYPE_ID = DD.DATATYPE_ID '||
1755 ' AND DD.LANGUAGE = USERENV(''LANG'') '||
1756 ' AND D.USAGE_TYPE = L.LOOKUP_CODE '||
1757 ' AND L.LANGUAGE = USERENV(''LANG'') '||
1758 ' AND L.LOOKUP_TYPE = ''ATCHMT_DOCUMENT_TYPE'' '||
1759 ' and ad.PK1_VALUE = :l_lead_id '||
1760 ' ORDER BY BIL_TX_MEASURE3 ';
1761
1762
1763
1764
1765 x_custom_sql := l_custom_sql ;
1766
1767 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1768 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
1769 p_proc => l_proc,
1770 p_query => x_custom_sql);
1771 END IF;
1772
1773
1774 l_bind_ctr := 1;
1775
1776 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1777 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1778 l_custom_rec.attribute_value := l_viewby;
1779 x_custom_attr.Extend();
1780 x_custom_attr(l_bind_ctr):=l_custom_rec;
1781 l_bind_ctr:=l_bind_ctr+1;
1782
1783
1784 l_custom_rec.attribute_name :=':l_rpt_str';
1785 l_custom_rec.attribute_value :=l_rpt_str;
1786 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1787 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1788 x_custom_attr.Extend();
1789 x_custom_attr(l_bind_ctr):=l_custom_rec;
1790 l_bind_ctr:=l_bind_ctr+1;
1791
1792 l_custom_rec.attribute_name :=':l_viewby';
1796 x_custom_attr.Extend();
1793 l_custom_rec.attribute_value := l_viewby;
1794 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1795 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1797 x_custom_attr(l_bind_ctr):=l_custom_rec;
1798 l_bind_ctr:=l_bind_ctr+1;
1799
1800 l_custom_rec.attribute_name :=':l_lead_id';
1801 l_custom_rec.attribute_value := l_lead_id;
1802 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1803 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1804 x_custom_attr.Extend();
1805 x_custom_attr(l_bind_ctr):=l_custom_rec;
1806 l_bind_ctr:=l_bind_ctr+1;
1807
1808 END ATTACHMENTS_TAB;
1809
1810 PROCEDURE NOTES_TAB (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1811 ,x_custom_sql OUT NOCOPY VARCHAR2
1812 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1813
1814
1815
1816 l_custom_rec BIS_QUERY_ATTRIBUTES;
1817 l_region_id VARCHAR2(50);
1818 l_parameter_valid BOOLEAN;
1819 l_rpt_str VARCHAR2(80);
1820 l_viewby VARCHAR2(80) ;
1821 l_bind_ctr NUMBER;
1822 l_proc VARCHAR2(100);
1823 l_custom_sql VARCHAR2(32000);
1824 l_outer_select VARCHAR2(4000);
1825 g_pkg VARCHAR2(100);
1826 l_where_clause VARCHAR2(1000);
1827 l_source_object_code VARCHAR2(100);
1828 l_lead_id VARCHAR2(100) ;
1829 l_cust_id VARCHAR2(100);
1830 l_to_currency VARCHAR2(100);
1831 l_period_type VARCHAR2(100);
1832 l_period_name VARCHAR2(100);
1833 l_credit_type_id VARCHAR2(100);
1834 l_sg_id VARCHAR2(200);
1835
1836 BEGIN
1837
1838
1839
1840 -- Initializing variables as per new standard
1841
1842 l_region_id := 'BIL_TX_OPTY_DETL_NOTES_RPT';
1843 l_parameter_valid := FALSE;
1844 l_rpt_str := 'BIL_TX_OPTY_DETL_NOTES_RPT_R';
1845 l_proc := 'NOTES_TAB.';
1846 g_pkg := 'asn.patch.115.sql.BIL_TX_OPTY_DETL_RPT_PKG.';
1847
1848
1849
1850 -- FND logging standard
1851 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1852
1853 BIL_TX_UTIL_RPT_PKG.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
1854 p_module => g_pkg || l_proc || 'begin',
1855 p_msg => 'Start of Procedure '|| l_proc );
1856 END IF;
1857
1858 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1859 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1860
1861 -- code for a procedure to get parameter values.
1862 BIL_TX_UTIL_RPT_PKG.GET_DETAIL_PAGE_PARAMS
1863 (p_page_parameter_tbl => p_page_parameter_tbl,
1864 p_region_id => l_region_id,
1865 x_parameter_valid => l_parameter_valid,
1866 x_viewby => l_viewby,
1867 x_lead_id => l_lead_id,
1868 x_cust_id => l_cust_id,
1869 x_credit_type_id => l_credit_type_id
1870 ) ;
1871
1872
1873
1874 /*** Query column mapping ******************************************************
1875
1876 * BIL_TX_MEASURE1 = Entered Date
1877 * BIL_TX_MEASURE2 = Created By
1878 * BIL_TX_MEASURE3 = Type
1879 * BIL_TX_MEASURE4 = Status
1880 * BIL_TX_MEASURE5 = Notes
1881
1882
1883 *******************************************************************************/
1884
1885
1886 l_custom_sql := ' SELECT JNB.ENTERED_DATE BIL_TX_MEASURE1, '||
1887 ' JTF_COMMON_PVT.GetUserInfo(JNB.ENTERED_BY) BIL_TX_MEASURE2, '||
1888 ' FLS.MEANING BIL_TX_MEASURE3, '||
1889 ' FLP.MEANING BIL_TX_MEASURE4, '||
1890 ' JNT.NOTES BIL_TX_MEASURE5 '||
1891 ' FROM JTF_NOTES_B JNB , '||
1892 ' JTF_NOTES_TL JNT , '||
1893 ' FND_LOOKUPS FLS , '||
1894 ' FND_LOOKUPS FLP '||
1895 ' WHERE JNB.JTF_NOTE_ID = JNT.JTF_NOTE_ID '||
1896 ' AND JNT.LANGUAGE = USERENV(''LANG'') '||
1897 ' AND FLS.LOOKUP_TYPE(+) = ''JTF_NOTE_TYPE'' '||
1898 ' AND FLS.LOOKUP_CODE(+) = JNB.NOTE_TYPE '||
1899 ' AND FLP.lookup_type = ''JTF_NOTE_STATUS'' '||
1900 ' AND FLP.lookup_code = JNB.note_status '||
1901 ' AND JNB.SOURCE_OBJECT_CODE = ''OPPORTUNITY'' '||
1902 ' AND JNB.SOURCE_OBJECT_ID = :l_lead_id '||
1903 ' ORDER BY BIL_TX_MEASURE1 ';
1904
1905
1906
1907 x_custom_sql := l_custom_sql ;
1908
1909 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1910 BIL_TX_UTIL_RPT_PKG.writeQuery(p_pkg => g_pkg,
1911 p_proc => l_proc,
1912 p_query => x_custom_sql);
1913 END IF;
1914
1915
1916 l_bind_ctr := 1;
1917
1918 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1919 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1920 l_custom_rec.attribute_value := l_viewby;
1921 x_custom_attr.Extend();
1922 x_custom_attr(l_bind_ctr):=l_custom_rec;
1923 l_bind_ctr:=l_bind_ctr+1;
1924
1928 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1925
1926 l_custom_rec.attribute_name :=':l_rpt_str';
1927 l_custom_rec.attribute_value :=l_rpt_str;
1929 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1930 x_custom_attr.Extend();
1931 x_custom_attr(l_bind_ctr):=l_custom_rec;
1932 l_bind_ctr:=l_bind_ctr+1;
1933
1934 l_custom_rec.attribute_name :=':l_lead_id';
1935 l_custom_rec.attribute_value := l_lead_id;
1936 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1937 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1938 x_custom_attr.Extend();
1939 x_custom_attr(l_bind_ctr):=l_custom_rec;
1940 l_bind_ctr:=l_bind_ctr+1;
1941
1942
1943 l_custom_rec.attribute_name :=':l_viewby';
1944 l_custom_rec.attribute_value := l_viewby;
1945 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1946 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1947 x_custom_attr.Extend();
1948 x_custom_attr(l_bind_ctr):=l_custom_rec;
1949 l_bind_ctr:=l_bind_ctr+1;
1950
1951 END NOTES_TAB;
1952
1953 END BIL_TX_OPTY_DETL_RPT_PKG;
1954