[Home] [Help]
PACKAGE BODY: APPS.OE_CUSTACCEPTREP_PVT
Source
1 PACKAGE BODY OE_CustAcceptRep_PVT AS
2 /* $Header: OEXVCARB.pls 120.12 2006/09/19 11:08:01 myerrams noship $ */
3 --Package Name
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'OE_CustAcceptRep_PVT';
5 --Global Variables that will contain the parameters values
6 g_sorted_by VARCHAR2(30);
7 g_customer_name_low VARCHAR2(100); --myerrams, CustomerName can be more than 30 chars long. Making it to 100
8 g_customer_name_high VARCHAR2(100); --myerrams, CustomerName can be more than 30 chars long. Making it to 100
9 g_customer_no_low VARCHAR2(30);
10 g_customer_no_high VARCHAR2(30);
11 g_order_type_low VARCHAR2(30);
12 g_order_type_high VARCHAR2(30);
13 g_order_no_low NUMBER;
14 g_order_no_high NUMBER;
15 g_order_date_low DATE;
16 g_order_date_high DATE;
17 g_fulfill_date_low DATE;
18 g_fulfill_date_high DATE;
19 g_accepted_date_low DATE;
20 g_accepted_date_high DATE;
21 g_acceptance_status VARCHAR2(30);
22 g_item_display VARCHAR2(30);
23 g_currency VARCHAR2(30);
24
25 --myerrams, currency related global variables
26 g_func_currency VARCHAR2(30);
27 g_func_currency_desc VARCHAR2(30);
28 g_ord_currency VARCHAR2(30);
29 --myerrams, end
30
31 g_org_id NUMBER;
32 g_operating_unit VARCHAR2(240);
33 --Get the value of debug level set by the user.
34 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
35 g_counter NUMBER := 0; --myerrams, Introduced for no_date_found logic.
36
37 --=============================================================================
38 -- PROCEDURES AND FUNCTIONS
39 --=============================================================================
40 --=============================================================================
41 -- Start of comments
42 -- PROCEDURE : gl_conversion_rate_func
43 -- PRE-CONDITIONS : None.
44 -- COMMENTS : Puts the parameter values in the XML output.
45 --=============================================================================
46 function gl_conversion_rate_func (
47 P_from_currency VARCHAR2,
48 P_to_currency VARCHAR2,
49 P_ordered_date DATE,
50 P_conversion_type_code VARCHAR2
51 )
52 return Number is
53 begin
54
55 Declare
56 gl_rate number;
57 BEGIN
58 IF p_conversion_type_code is null then
59 gl_rate := gl_currency_api.get_rate (
60 P_from_currency,
61 P_to_currency,
62 P_ordered_date);
63 RETURN(gl_rate);
64 ELSE
65 gl_rate := gl_currency_api.get_rate (
66 P_from_currency,
67 P_to_currency,
68 P_ordered_date,
69 P_conversion_type_code );
70 RETURN(gl_rate);
71 END IF;
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 RETURN(-1);
75 WHEN OTHERS THEN
76 RETURN(-1);
77 end;
78
79 RETURN NULL; end;
80
81 --=============================================================================
82 -- PROCEDURE NAME: To_Xsd_Date_String
83 -- TYPE : PUBLIC
84 -- PARAMETERS :
85 -- p_date Oracle Date to be converted to XSD Date Format
86 -- RETURN : A String representing the passed in Date in XSD Date Format
87 -- DESCRIPTION : Convert an Oracle DB Date Object to a date string represented
88 -- in the XSD Date Format. This is mainly for use by the
89 -- XML Publisher Reports.
90 -- EXCEPTIONS :
91 --
92 -- CHANGE HISTORY: 012-SEP-06 MYERRAMS Created.
93 --=============================================================================
94
95 FUNCTION To_Xsd_Date_String
96 ( p_date IN DATE
97 )
98 RETURN VARCHAR2
99 IS
100
101 l_api_name CONSTANT VARCHAR2(30) := 'To_Xsd_Date_String';
102 l_xsd_date_string VARCHAR2(40);
103
104 BEGIN
105
106 IF l_debug_level > 0
107 THEN
108 oe_debug_pub.add( 'OEXCARR:' || l_api_name || '.begin') ;
109 END IF;
110
111 SELECT TO_CHAR(p_date, 'YYYY-MM-DD')
112 INTO l_xsd_date_string
113 FROM DUAL;
114
115 IF l_debug_level > 0
116 THEN
117 oe_debug_pub.add( 'OEXCARR:' || l_api_name || '.end: Returning XSD Date = ' || l_xsd_date_string ) ;
118 END IF;
119
120 l_xsd_date_string := TRIM(l_xsd_date_string);
121
122 RETURN l_xsd_date_string;
123
124 EXCEPTION
125
126 WHEN OTHERS THEN
127 IF l_debug_level > 0
128 THEN
129 oe_debug_pub.add( 'OEXCARR:' || l_api_name || ': ' || sqlerrm ) ;
130 END IF;
131
132 RETURN NULL;
133
134 END To_Xsd_Date_String;
135
136 --=============================================================================
137
138 --=============================================================================
139 -- Start of comments
140 -- PROCEDURE : put_parameter_tags
141 -- PRE-CONDITIONS : None.
142 -- COMMENTS : Puts the parameter values in the XML output.
143 --=============================================================================
144 PROCEDURE put_parameter_tags IS
145 l_acceptance_status VARCHAR2(100);
146 l_item_display VARCHAR2(50);
147 l_sorted_by VARCHAR2(30);
148 l_order_type_low VARCHAR2(30);
149 l_order_type_high VARCHAR2(30);
150
151 l_order_date_low VARCHAR2(50);
152 l_order_date_high VARCHAR2(50);
153 l_fulfill_date_low VARCHAR2(50);
154 l_fulfill_date_high VARCHAR2(50);
155 l_accepted_date_low VARCHAR2(50);
156 l_accepted_date_high VARCHAR2(50);
157
158 BEGIN
159 IF l_debug_level > 0
160 THEN
161 oe_debug_pub.add( 'OEXCARR: Inside put_parameter_tags') ;
162 END IF;
163
164 --myerrams, converting the sort by option to an appropriate display name.
165 --myerrams, Bug: 5480694
166 IF g_sorted_by IS NOT NULL
167 THEN
168 select meaning into l_sorted_by
169 from oe_lookups
170 where lookup_type='OECARR_ORDER_BY'
171 and lookup_code=g_sorted_by;
172 END IF;
173 --myerrams, end
174
175 --myerrams, Bug: 5480694
176 IF g_acceptance_status IS NOT NULL
177 THEN
178 select meaning into l_acceptance_status
179 from oe_lookups
180 where lookup_type='OECARR_ACC_STATUS'
181 and lookup_code = g_acceptance_status;
182 END IF;
183
184 --myerrams, Selecting the appropriate Item Display Name from OE Lookups
185 IF g_item_display IS NOT NULL
186 THEN
187 select meaning into l_item_display
188 from OE_LOOKUPS
189 where lookup_type = 'ITEM_DISPLAY_CODE'
190 and LOOKUP_CODE=g_item_display;
191 END IF;
192 --myerrams, end
193
194 --myerrams, Selecting the appropriate Order Type Name. Bug: 5230819
195 IF g_order_type_low IS NOT NULL
196 THEN
197 select name into l_order_type_low
198 from OE_ORDER_TYPES_V
199 where order_type_id = g_order_type_low ;
200 END IF;
201 IF g_order_type_high IS NOT NULL
202 THEN
203 select name into l_order_type_high
204 from OE_ORDER_TYPES_V
205 where order_type_id = g_order_type_high ;
206 END IF;
207 --myerrams, end;
208
209 /* myerrams, Convert an Oracle DB Date Object to a date string represented in the XSD Date Format */
210
211 IF g_order_date_low IS NOT NULL
212 THEN
213 l_order_date_low := To_Xsd_Date_String(g_order_date_low);
214 END IF;
215
216 IF g_order_date_high IS NOT NULL
217 THEN
218 l_order_date_high := To_Xsd_Date_String(g_order_date_high);
219 END IF;
220
221 IF g_fulfill_date_low IS NOT NULL
222 THEN
223 l_fulfill_date_low := To_Xsd_Date_String(g_fulfill_date_low);
224 END IF;
225
226 IF g_fulfill_date_high IS NOT NULL
227 THEN
228 l_fulfill_date_high := To_Xsd_Date_String(g_fulfill_date_high);
229 END IF;
230
231 IF g_accepted_date_low IS NOT NULL
232 THEN
233 l_accepted_date_low := To_Xsd_Date_String(g_accepted_date_low);
234 END IF;
235
236 IF g_accepted_date_high IS NOT NULL
237 THEN
238 l_accepted_date_high := To_Xsd_Date_String(g_accepted_date_high);
239 END IF;
240
241 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?xml version="1.0" ?>');
242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<OEXCARR>');
243 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<OPERATING_UNIT>' ||g_operating_unit|| '</OPERATING_UNIT>');
244 --myerrams FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CURRENCY>' ||g_currency|| '</CURRENCY>');
245 --myerrams FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_BY>' || g_sorted_by || '</ORDER_BY>');
246 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_BY_PARAM>' || l_sorted_by || '</ORDER_BY_PARAM>');
247 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NAME_LOW>' || '<![CDATA['||g_customer_name_low||']]>' || '</CUSTOMER_NAME_LOW>');
248 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NAME_HIGH>' || '<![CDATA['||g_customer_name_high||']]>' || '</CUSTOMER_NAME_HIGH>');
249 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NO_LOW>' || g_customer_no_low || '</CUSTOMER_NO_LOW>');
250 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NO_HIGH>' || g_customer_no_high || '</CUSTOMER_NO_HIGH>');
251 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_TYPE_LOW>' || l_order_type_low || '</ORDER_TYPE_LOW>');
252 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_TYPE_HIGH>' || l_order_type_high || '</ORDER_TYPE_HIGH>');
253 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NO_LOW>' || g_order_no_low || '</ORDER_NO_LOW>');
254 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NO_HIGH>' || g_order_no_high || '</ORDER_NO_HIGH>');
255 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDERED_DATE_LOW>' || l_order_date_low || '</ORDERED_DATE_LOW>');
256 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDERED_DATE_HIGH>' || l_order_date_high || '</ORDERED_DATE_HIGH>');
257 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<FULFILL_DATE_LOW>' || l_fulfill_date_low || '</FULFILL_DATE_LOW>');
258 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<FULFILL_DATE_HIGH>' || l_fulfill_date_high || '</FULFILL_DATE_HIGH>');
259 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTED_DATE_LOW>' || l_accepted_date_low || '</ACCEPTED_DATE_LOW>');
260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTED_DATE_HIGH>' || l_accepted_date_high || '</ACCEPTED_DATE_HIGH>');
261 --myerrams FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTANCE_STATUS>' || g_acceptance_status || '</ACCEPTANCE_STATUS>');
262 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTANCE_STATUS>' || l_acceptance_status || '</ACCEPTANCE_STATUS>');
263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<FUNC_CURRENCY>' || g_func_currency_desc || '</FUNC_CURRENCY>');
264 --myerrams
265 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ITEM_DISPLAY>' || l_item_display || '</ITEM_DISPLAY>');
266 --myerrams, end
267 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<USERID>' || FND_GLOBAL.USER_NAME || '</USERID>');
268 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<REQUESTID>' || FND_GLOBAL.CONC_REQUEST_ID || '</REQUESTID>');
269 END;
270 --=============================================================================
271 -- Start of comments
272 -- FUNCTION : create_where_clause
273 -- PRE-CONDITIONS : None.
274 -- COMMENTS : Creates where clause for the queries based on the
275 -- input parameters passed by the user.
276 -- PARAMETERS : The query String
277 -- RETURN : Query string with where clause generated based on the
278 -- user parameters.
279 --=============================================================================
280
281 FUNCTION create_where_clause
282 (p_where_clause VARCHAR2)
283 RETURN VARCHAR2
284 IS
285 l_query VARCHAR2(15000);
286 BEGIN
287
288 IF l_debug_level > 0
289 THEN
290 oe_debug_pub.add( 'OEXCARR: Inside create_where_clause') ;
291 END IF;
292 l_query := p_where_clause;
293
294 IF g_customer_no_low IS NOT NULL
295 THEN
296 /*myerrams, customer number is varchar, not a number
297 l_query := l_query || ' AND hca.account_number BETWEEN '|| g_customer_no_low||
298 ' AND ' || nvl(g_customer_no_high,g_customer_no_low);
299 myerrams, end*/
300 l_query := l_query || ' AND hca.account_number BETWEEN '||''''|| g_customer_no_low||''''||
301 ' AND ' ||''''|| nvl(g_customer_no_high,g_customer_no_low)||'''';
302 END IF;
303
304 IF g_customer_name_low IS NOT NULL
305 THEN
306 l_query := l_query || ' AND hp.party_name BETWEEN '||''''|| g_customer_name_low ||''''||
307 ' AND ' ||''''|| nvl(g_customer_name_high,g_customer_name_low)||'''';
308 END IF;
309
310 IF g_order_date_low IS NOT NULL
311 THEN
312 l_query := l_query || ' AND oha.ordered_date BETWEEN '||
313 ''''|| g_order_date_low ||''''||' AND ' ||''''|| nvl(g_order_date_high,g_order_date_low)||'''';
314 END IF;
315
316 IF g_order_type_low IS NOT NULL
317 THEN
318 l_query := l_query || ' AND oha.order_type_id BETWEEN '||
319 g_order_type_low ||' AND ' || nvl(g_order_type_high,g_order_type_low);
320 END IF;
321
322 IF g_order_no_low IS NOT NULL
323 THEN
324 l_query := l_query || ' AND oha.order_number BETWEEN '||
325 g_order_no_low ||' AND ' || nvl(g_order_no_high,g_order_no_low);
326 END IF;
327
328 IF g_fulfill_date_low IS NOT NULL
329 THEN
330 l_query := l_query || ' AND oln.fulfillment_date BETWEEN '||
331 ''''|| g_fulfill_date_low ||''''||' AND ' ||
332 ''''|| nvl(g_fulfill_date_high,g_fulfill_date_low)||'''';
333 END IF;
334 IF g_accepted_date_low IS NOT NULL
335 THEN
336 l_query := l_query || ' AND oln.revrec_signature_date BETWEEN '||
337 ''''|| g_accepted_date_low ||''''||' AND ' ||
338 ''''|| nvl(g_accepted_date_high,g_accepted_date_low)||'''';
339 END IF;
340
341 IF g_acceptance_status = 'PRE-BILLING_ACCEPTANCE'
342 THEN
343 l_query := l_query || ' AND oln.flow_status_code = ''PRE-BILLING_ACCEPTANCE''' ;
344 ELSIF g_acceptance_status = 'POST-BILLING_ACCEPTANCE'
345 THEN
346 l_query := l_query || ' AND oln.flow_status_code = ''POST-BILLING_ACCEPTANCE''' ;
347 ELSIF g_acceptance_status = 'REJECTED'
348 THEN
349 l_query := l_query || ' AND oln.accepted_quantity = 0' ;
350 ELSIF g_acceptance_status = 'ACCEPTED'
351 THEN
352 l_query := l_query || ' AND oln.accepted_quantity > 0' ;
353 ELSE
354 l_query := l_query || ' AND (oln.flow_status_code = ''PRE-BILLING_ACCEPTANCE''
355 OR oln.flow_status_code = ''POST-BILLING_ACCEPTANCE''
356 OR oln.accepted_quantity >= 0) ';
357 END IF;
358
359 IF l_debug_level > 0 THEN
360 oe_debug_pub.add( 'OEXCARR: create_where_clause returns : '||l_query);
361 END IF;
362 return l_query;
363
364 END;
365
366 --=============================================================================
367 -- Start of comments
368 -- FUNCTION : create_ord_where_clause
369 -- PRE-CONDITIONS : None.
370 -- COMMENTS : Creates where clause for the queries based on the
371 -- input parameters passed by the user.
372 -- PARAMETERS : The query String
373 -- RETURN : Query string with where clause generated based on the
374 -- user parameters.
375 --=============================================================================
376
377 FUNCTION create_ord_where_clause
378 ( p_where_clause VARCHAR2
379 ,p_cust_id NUMBER)
380 RETURN VARCHAR2
381 IS
382 l_query VARCHAR2(15000);
383 BEGIN
384
385 IF l_debug_level > 0
386 THEN
387 oe_debug_pub.add( 'OEXCARR: Inside create_ord_where_clause') ;
388 END IF;
389 l_query := p_where_clause;
390 IF p_cust_id IS NOT NULL
391 THEN
392 l_query := l_query || ' AND oha.sold_to_org_id = '|| p_cust_id;
393 END IF;
394
395 IF g_order_date_low IS NOT NULL
396 THEN
397 l_query := l_query || ' AND oha.ordered_date BETWEEN '||
398 ''''|| g_order_date_low ||''''||' AND ' ||''''|| nvl(g_order_date_high,g_order_date_low)||'''';
399 END IF;
400
401 IF g_order_type_low IS NOT NULL
402 THEN
403 l_query := l_query || ' AND oha.order_type_id BETWEEN '||
404 g_order_type_low ||' AND ' || nvl(g_order_type_high,g_order_type_low);
405 END IF;
406
407 IF g_order_no_low IS NOT NULL
408 THEN
409 l_query := l_query || ' AND oha.order_number BETWEEN '||
410 g_order_no_low ||' AND ' || nvl(g_order_no_high,g_order_no_low);
411 END IF;
412
413 IF g_acceptance_status = 'PRE-BILLING_ACCEPTANCE'
414 THEN
415 l_query := l_query || ' AND oln.flow_status_code = ''PRE-BILLING_ACCEPTANCE''' ;
416 ELSIF g_acceptance_status = 'POST-BILLING_ACCEPTANCE'
417 THEN
418 l_query := l_query || ' AND oln.flow_status_code = ''POST-BILLING_ACCEPTANCE''' ;
419 ELSIF g_acceptance_status = 'REJECTED'
420 THEN
421 l_query := l_query || ' AND oln.accepted_quantity = 0' ;
422 ELSIF g_acceptance_status = 'ACCEPTED'
423 THEN
424 l_query := l_query || ' AND oln.accepted_quantity > 0' ;
425 ELSE
426 l_query := l_query || ' AND (oln.flow_status_code = ''PRE-BILLING_ACCEPTANCE''
427 OR oln.flow_status_code = ''POST-BILLING_ACCEPTANCE''
428 OR oln.accepted_quantity >= 0) ';
429 END IF;
430
431 IF l_debug_level > 0 THEN
432 oe_debug_pub.add( 'OEXCARR: create_where_clause returns : '||l_query);
433 END IF;
434 return l_query;
435
436 END;
437
438 --myerrams, start
439 --=============================================================================
440 -- Start of comments
441 -- FUNCTION : create_curr_where_clause
442 -- PRE-CONDITIONS : None.
443 -- COMMENTS : Creates where clause for the queries based on the
444 -- input parameters passed by the user.
445 -- PARAMETERS : The query String
446 -- RETURN : Query string with where clause generated based on the
447 -- user parameters.
448 --=============================================================================
449
450 FUNCTION create_curr_where_clause
451 ( p_where_clause VARCHAR2
452 ,p_curr_code VARCHAR2)
453 RETURN VARCHAR2
454 IS
455 l_query VARCHAR2(15000);
456 BEGIN
457
458 IF l_debug_level > 0
459 THEN
460 oe_debug_pub.add( 'OEXCARR: Inside create_curr_where_clause') ;
461 END IF;
462 l_query := p_where_clause;
463 IF p_curr_code IS NOT NULL
464 THEN
465 l_query := l_query || ' AND oha.TRANSACTIONAL_CURR_CODE = '|| ''''|| p_curr_code||'''';
466 END IF;
467 return l_query;
468 END;
469 --myerrams, end
470
471 --=============================================================================
472 -- Start of comments
473 -- FUNCTION : create_lines_where_clause
474 -- PRE-CONDITIONS : None.
475 -- COMMENTS : Creates where clause for the queries based on the
476 -- input parameters passed by the user.
477 -- PARAMETERS : The query String
478 -- RETURN : Query string with where clause generated based on the
479 -- user parameters.
480 --=============================================================================
481
482 FUNCTION create_lines_where_clause
483 (p_where_clause VARCHAR2
484 ,p_header_id NUMBER
485 )
486 RETURN VARCHAR2
487 IS
488 l_query VARCHAR2(15000);
489 BEGIN
490
491 IF l_debug_level > 0
492 THEN
493 oe_debug_pub.add( 'OEXCARR: Inside create_lines_where_clause') ;
494 END IF;
495 l_query := p_where_clause;
496 IF p_header_id IS NOT NULL
497 THEN
498 l_query := l_query || ' AND oln.header_id = '|| p_header_id;
499 END IF;
500
501 IF g_fulfill_date_low IS NOT NULL
502 THEN
503 l_query := l_query || ' AND oln.fulfillment_date BETWEEN '||
504 ''''|| g_fulfill_date_low ||''''||' AND ' ||
505 ''''|| nvl(g_fulfill_date_high,g_fulfill_date_low)||'''';
506 END IF;
507 IF g_accepted_date_low IS NOT NULL
508 THEN
509 l_query := l_query || ' AND oln.revrec_signature_date BETWEEN '||
510 ''''|| g_accepted_date_low ||''''||' AND ' ||
511 ''''|| nvl(g_accepted_date_high,g_accepted_date_low)||'''';
512 END IF;
513
514 IF g_acceptance_status = 'PRE-BILLING_ACCEPTANCE'
515 THEN
516 l_query := l_query || ' AND oln.flow_status_code = ''PRE-BILLING_ACCEPTANCE''' ;
517 ELSIF g_acceptance_status = 'POST-BILLING_ACCEPTANCE'
518 THEN
519 l_query := l_query || ' AND oln.flow_status_code = ''POST-BILLING_ACCEPTANCE''' ;
520 ELSIF g_acceptance_status = 'REJECTED'
521 THEN
522 l_query := l_query || ' AND oln.accepted_quantity = 0' ;
523 ELSIF g_acceptance_status = 'ACCEPTED'
524 THEN
525 l_query := l_query || ' AND oln.accepted_quantity > 0' ;
526 ELSE
527 l_query := l_query || ' AND (oln.flow_status_code = ''PRE-BILLING_ACCEPTANCE''
528 OR oln.flow_status_code = ''POST-BILLING_ACCEPTANCE''
529 OR oln.accepted_quantity >= 0) ';
530 END IF;
531
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add( 'OEXCARR: create_where_clause returns : '||l_query);
534 END IF;
535 return l_query;
536
537 END;
538
539 --=============================================================================
540 -- Start of comments
541 -- PROCEDURE : put_ghead_tags
542 -- PRE-CONDITIONS : None.
543 -- COMMENTS : Puts the header tags in the generated XML output.
544 --=============================================================================
545 PROCEDURE put_ghead_tags
546 ( p_party_name VARCHAR2
547 ,p_account_number VARCHAR2
548 )
549 IS
550 BEGIN
551 IF l_debug_level > 0 THEN
552 oe_debug_pub.add( 'OEXCARR: Inside put_ghead_tags') ;
553 END IF;
554
555 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<LIST_G_CUST>');
556 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NAME>' || '<![CDATA['||p_party_name||']]>' || '</CUSTOMER_NAME>');
557 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NUMBER>'|| p_account_number || '</CUSTOMER_NUMBER>');
558 END;
559
560 --myerrams, start
561 --=============================================================================
562 -- Start of comments
563 -- PROCEDURE : put_gcurr_tags
564 -- PRE-CONDITIONS : None.
565 -- COMMENTS : Puts the currency header tags in the generated XML output.
566 --=============================================================================
567 PROCEDURE put_gcurr_tags
568 ( p_currency VARCHAR2
569 ,p_order_by VARCHAR2
570 )
571 IS
572
573 BEGIN
574 IF l_debug_level > 0 THEN
575 oe_debug_pub.add( 'OEXCARR: Inside put_gcurr_tags') ;
576 END IF;
577 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<LIST_G_CURR>');
578 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CURRENCY>' || p_currency || '</CURRENCY>');
579 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_BY>'|| p_order_by || '</ORDER_BY>');
580 END;
581 --myerrams, end
582
583 --=============================================================================
584 -- Start of comments
585 -- PROCEDURE : put_ord_tags
586 -- PRE-CONDITIONS : None.
587 -- COMMENTS : Puts the header tags in the generated XML output.
588 --=============================================================================
589 PROCEDURE put_ord_tags
590 ( p_order_number VARCHAR2
591 ,p_order_date DATE
592 ,p_customer_po VARCHAR2
593 ,p_order_type VARCHAR2
594 ,p_sales_rep VARCHAR2
595 ,p_party_name VARCHAR2
596 ,p_account_number VARCHAR2
597 ,p_currency VARCHAR2 --myerrams, added the currency to comply with XML Publisher Currency Formatting Standards
598 )
599 IS
600 BEGIN
601 IF l_debug_level > 0 THEN
602 oe_debug_pub.add( 'OEXCARR: Inside put_ord_tags') ;
603 END IF;
604
605 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<LIST_G_ORD>');
606 IF p_party_name IS NOT NULL
607 THEN
608 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NAME>' || '<![CDATA['||p_party_name||']]>' || '</CUSTOMER_NAME>');
609 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_NUMBER>'|| p_account_number || '</CUSTOMER_NUMBER>');
610 END IF;
611 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NUMBER>' || p_order_number || '</ORDER_NUMBER>');
612 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_DATE>'|| p_order_date || '</ORDER_DATE>');
613 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CUSTOMER_PO>'|| p_customer_po || '</CUSTOMER_PO>');
614 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_TYPE>'|| p_order_type || '</ORDER_TYPE>');
615 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SALES_PERSON>' || p_sales_rep || '</SALES_PERSON>');
616 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CURRENCY_LINE>' || p_currency || '</CURRENCY_LINE>'); --myerrams, Added order level Currency Tag. Bug: 5480694
617 END;
618
619 --=============================================================================
620 -- Start of comments
621 -- PROCEDURE : put_line_tags
622 -- PRE-CONDITIONS : None.
623 -- COMMENTS : Puts the line tags in the generated XML output.
624 --=============================================================================
625 PROCEDURE put_line_tags
626 ( p_line_number NUMBER
627 ,p_ordered_item VARCHAR2
628 ,p_fulfilled_quantity NUMBER
629 ,p_shipping_quantity_uom VARCHAR2
630 ,p_accepted_quantity NUMBER
631 ,p_rejected_quantity NUMBER
632 ,p_pending_quantity NUMBER
633 ,p_accepted_quantity_value NUMBER
634 ,p_rejected_quantity_value NUMBER
635 ,p_pending_quantity_value NUMBER
636 ,p_currency VARCHAR2 --myerrams, added the currency to comply with XML Publisher Number Formatting Standards
637 ) IS
638 BEGIN
639 IF l_debug_level > 0 THEN
640 oe_debug_pub.add( 'OEXCARR: Inside put_line_tags') ;
641 END IF;
642 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<LIST_G_LINES>');
643 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CURRENCY_LINE>' || p_currency || '</CURRENCY_LINE>'); --myerrams, Added order level Currency Tag for formatting the number columns in the RTF template. Bug: 5460837
644 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_LINE_NUMBER>' || p_line_number || '</SO_LINE_NUMBER>');
645 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_ITEM>' || p_ordered_item || '</SO_ITEM>');
646 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_FULFILLED_QTY>' || nvl(p_fulfilled_quantity,0) || '</SO_FULFILLED_QTY>');
647 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_UOM>' || p_shipping_quantity_uom || '</SO_UOM>');
648 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_ACCEPTED_QTY>'|| nvl(p_accepted_quantity,0) || '</SO_ACCEPTED_QTY>');
649 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_ACCEPTED_VALUE>'|| nvl(p_accepted_quantity_value,0) || '</SO_ACCEPTED_VALUE>');
650 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_REJECTED_QTY>'||nvl(p_rejected_quantity,0) || '</SO_REJECTED_QTY>');
651 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_REJECTED_VALUE>'|| nvl(p_rejected_quantity_value,0) || '</SO_REJECTED_VALUE>');
652 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_PENDING_ACC_QTY>'|| nvl(p_pending_quantity,0)|| '</SO_PENDING_ACC_QTY>');
653 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SO_PENDING_ACC_VALUE>'|| nvl(p_pending_quantity_value,0)|| '</SO_PENDING_ACC_VALUE>');
654 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</LIST_G_LINES>');
655 END;
656 --=============================================================================
657 -- Start of comments
658 -- PROCEDURE : put_order_total_tags
659 -- PRE-CONDITIONS : None.
660 -- COMMENTS : Puts the order level totals in the generated XML output.
661 --=============================================================================
662 PROCEDURE put_order_total_tags
663 ( p_accepted_value NUMBER
664 ,p_rejected_value NUMBER
665 ,p_pending_value NUMBER
666 )
667 IS
668 BEGIN
669 IF l_debug_level > 0 THEN
670 oe_debug_pub.add( 'OEXCARR: Inside put_order_total_tags') ;
671 END IF;
672 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTED_ORD_TOTAL>'|| p_accepted_value || '</ACCEPTED_ORD_TOTAL>');
673 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<REJECTED_ORD_TOTAL>'|| p_rejected_value || '</REJECTED_ORD_TOTAL>');
674 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PENDING_ORD_TOTAL>'|| p_pending_value || '</PENDING_ORD_TOTAL>');
675 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</LIST_G_ORD>');
676 END;
677 --=============================================================================
678 -- Start of comments
679 -- PROCEDURE : put_cust_total_tags
680 -- PRE-CONDITIONS : None.
681 -- COMMENTS : Puts the customer level totals in the generated XML output.
682 --=============================================================================
683 PROCEDURE put_cust_total_tags
684 ( p_accepted_value NUMBER
685 ,p_rejected_value NUMBER
686 ,p_pending_value NUMBER
687 )
688 IS
689 BEGIN
690 IF l_debug_level > 0 THEN
691 oe_debug_pub.add( 'OEXCARR: Inside put_cust_total_tags') ;
692 END IF;
693 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTED_CUST_TOTAL>'|| p_accepted_value || '</ACCEPTED_CUST_TOTAL>');
694 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<REJECTED_CUST_TOTAL>'|| p_rejected_value || '</REJECTED_CUST_TOTAL>');
695 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PENDING_CUST_TOTAL>'|| p_pending_value || '</PENDING_CUST_TOTAL>');
696 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</LIST_G_CUST>');
697 END;
698 --=============================================================================
699 -- Start of comments
700 -- PROCEDURE : put_rep_total_tags
701 -- PRE-CONDITIONS : None.
702 -- COMMENTS : Puts the report level totals in the generated XML output.
703 --=============================================================================
704 PROCEDURE put_rep_total_tags
705 ( p_accepted_value NUMBER
706 ,p_rejected_value NUMBER
707 ,p_pending_value NUMBER
708 )
709 IS
710 BEGIN
711 IF l_debug_level > 0 THEN
712 oe_debug_pub.add( 'OEXCARR: Inside put_rep_total_tags') ;
713 END IF;
714 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ACCEPTED_REP_TOTAL>'|| p_accepted_value || '</ACCEPTED_REP_TOTAL>');
715 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<REJECTED_REP_TOTAL>'|| p_rejected_value || '</REJECTED_REP_TOTAL>');
716 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PENDING_REP_TOTAL>'|| p_pending_value || '</PENDING_REP_TOTAL>');
717 --myerrams, start
718 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</OEXCARR>');
719 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</LIST_G_CURR>');
720 --myerrams, end
721
722 END;
723
724 --=============================================================================
725 -- Start of comments
726 -- PROCEDURE : put_remaining_tags
727 -- PRE-CONDITIONS : None.
728 -- COMMENTS : Puts the customer, order and line values in the XML output.
729 --=============================================================================
730 PROCEDURE put_remaining_tags
731 (
732 p_sorted_by VARCHAR2
733 , p_func_currency VARCHAR2
734 , p_errbuf OUT NOCOPY VARCHAR2
735 , p_retcode OUT NOCOPY VARCHAR2
736 )
737 IS
738 l_cust_cursor_query VARCHAR2(15000) := 'SELECT DISTINCT hp.party_name, '||
739 ' hca.account_number, '||
740 ' hca.cust_account_id '||
741 'FROM hz_parties hp, '||
742 ' hz_cust_accounts_all hca, '||
743 ' oe_order_headers_all oha, '||
744 ' oe_order_lines_all oln, '||
745 ' oe_transaction_types_all ota '||
746 'WHERE hp.party_id = hca.party_id '||
747 'AND hca.cust_account_id = oha.sold_to_org_id '||
748 'AND oha.header_id = oln.header_id '||
749 'AND oha.order_type_id = ota.transaction_type_id ';
750
751 --myerrams, Modified the following query to add oe_order_lines_all
752 --myerrams, Bug: 5259257. Added Distinct keyword to remove the duplicates.
753 l_ord_cursor_query VARCHAR2(15000) := 'SELECT distinct oha.order_number,'||
754 ' oha.header_id,'||
755 ' oha.ordered_date,'||
756 ' oha.cust_po_number,'||
757 ' ota.order_category_code,'||
758 ' rsa.name, '||
759 ' oha.TRANSACTIONAL_CURR_CODE, '||
760 ' oha.conversion_type_code, '||
761 ' oha.conversion_rate '||
762 'FROM oe_order_headers_all oha, '||
763 ' oe_order_lines_all oln, '||
764 ' oe_transaction_types_all ota, '||
765 ' ra_salesreps_all rsa '||
766 'WHERE oha.order_type_id = ota.transaction_type_id '||
767 'AND oha.header_id = oln.header_id ' ||
768 'AND oha.salesrep_id = rsa.salesrep_id ';
769
770 l_line_cursor_query VARCHAR2(15000) := 'SELECT oln.line_number,'||
771 ' oln.ordered_item,'||
772 ' oln.user_item_description,'||
773 ' msi.concatenated_segments,'||
774 ' msi.description,'||
775 ' oln.ordered_quantity, '||
776 ' oln.ordered_quantity * oln.unit_selling_price ordered_value, '||
777 ' oln.fulfilled_quantity,'||
778 ' oln.fulfilled_quantity * oln.unit_selling_price fulfilled_value,'||
779 ' oln.shipped_quantity,'||
780 ' oln.shipped_quantity * oln.unit_selling_price shipped_value, '||
781 ' oln.order_quantity_uom,'||
782 ' oln.accepted_quantity,'||
783 ' oln.accepted_quantity * oln.unit_selling_price acceptance_value '||
784 'FROM oe_order_lines_all oln, '||
785 ' mtl_system_items_b_kfv msi '||
786 'WHERE msi.inventory_item_id = oln.inventory_item_id '||
787 'AND msi.organization_id = oln.ship_from_org_id ';
788
789 TYPE c_cust_from_param_type IS REF CURSOR;
790 c_cust_from_param c_cust_from_param_type;
791
792 TYPE c_ord_from_param_type IS REF CURSOR;
793 c_ord_from_param c_ord_from_param_type;
794
795 TYPE c_line_from_param_type IS REF CURSOR;
796 c_line_from_param c_line_from_param_type;
797
798
799 l_cust_query VARCHAR2(15000);
800 l_ord_query VARCHAR2(15000);
801 l_lines_query VARCHAR2(15000);
802
803 l_party_name VARCHAR2(360);
804 l_account_number VARCHAR2(30);
805 l_order_number NUMBER;
806 l_ordered_date DATE;
807 --myerrams, added the following Conversion related variables
808 l_conversion_type_code VARCHAR2(30);
809 l_ord_conversion_rate NUMBER;
810 l_conversion_rate NUMBER;
811 --myerrams, end
812 l_order_type VARCHAR2(30);
813 l_header_id NUMBER;
814 l_cust_acc_id NUMBER;
815 l_line_number NUMBER;
816 l_ordered_item VARCHAR2(2000);
817 l_ordered_item_desc VARCHAR2(1000);
818 l_internal_item VARCHAR2(2000);
819 l_internal_item_desc VARCHAR2(1000);
820 l_fulfilled_quantity NUMBER;
821 l_shipping_quantity_uom VARCHAR2(3);
822 l_shipped_quantity NUMBER := 0;
823 l_shipped_quantity_value NUMBER := 0;
824
825 l_ordered_quantity NUMBER := 0; --myerrams, Added for finding the pending quantity
826 l_ordered_quantity_value NUMBER := 0; --myerrams, Added for finding the pending quantity value
827
828 l_accepted_quantity NUMBER := 0;
829 l_accepted_quantity_value NUMBER := 0;
830 l_acceptance_value NUMBER := 0;
831 l_pending_quantity NUMBER := 0;
832 l_pending_quantity_value NUMBER := 0;
833 l_rejected_quantity NUMBER := 0;
834 l_rejected_quantity_value NUMBER := 0;
835 l_fulfilled_value NUMBER := 0;
836 l_flow_status_code VARCHAR2(30);
837 l_total_shipped_value NUMBER := 0;
838 l_total_line_value NUMBER := 0;
839 l_total_line_qty NUMBER := 0;
840 l_item_value VARCHAR2(5000);
841 l_cust_po_number VARCHAR2(50);
842 l_sales_rep_name VARCHAR2(360);
843
844 l_line_accepted_total NUMBER := 0;
845 l_line_rejected_total NUMBER := 0;
846 l_line_pending_total NUMBER := 0;
847 l_cust_accepted_total NUMBER := 0;
848 l_cust_rejected_total NUMBER := 0;
849 l_cust_pending_total NUMBER := 0;
850 l_rep_accepted_total NUMBER := 0;
851 l_rep_rejected_total NUMBER := 0;
852 l_rep_pending_total NUMBER := 0;
853 l_first_time_flag BOOLEAN; --myerrams, Bug: 5231338
854 l_orders_flag BOOLEAN; --myerrams, Bug: 5231338
855 l_line_counter NUMBER; --myerrams, line counter;
856 l_currency VARCHAR2(30); --myerrams, added for populating the line currency
857
858 BEGIN
859 IF l_debug_level > 0
860 THEN
861 oe_debug_pub.add( 'OEXCARR: Inside put_remaining_tags') ;
862 END IF;
863
864 -- Get the query string by calling function create_where_clause, This
865 -- function creates the where clause for the query based on the user input parameters.
866 l_cust_query := l_cust_cursor_query;
867 l_cust_query := create_where_clause (l_cust_query);
868 IF l_debug_level > 0 THEN
869 oe_debug_pub.add( 'OEXCARR: Query to be executed: '||l_cust_query ) ;
870 END IF;
871 OPEN c_cust_from_param FOR l_cust_query;
872 LOOP
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add( 'OEXCARR: Inside the LOOP for Customer');
875 END IF;
876
877 FETCH c_cust_from_param
878 INTO l_party_name,
879 l_account_number,
880 l_cust_acc_id;
881 EXIT WHEN c_cust_from_param%NOTFOUND;
882 g_counter := g_counter + 1; --myerrams, increase the counter by 1
883 -- Call the procedure put_ghead_tags to populate the output XML with the header tags
884 --myerrams, Bug: 5231338.
885 l_first_time_flag := TRUE;
886 l_orders_flag := FALSE;
887
888 l_ord_query := l_ord_cursor_query;
889 --myerrams, constraining to the passed Operating unit
890 IF g_org_id IS NOT NULL
891 THEN
892 l_ord_query := l_ord_query || ' AND oha.org_id =' || g_org_id;
893 END IF;
894 --myerrams, end
895
896 IF l_debug_level > 0 THEN
897 oe_debug_pub.add( 'OEXCARR: Value of l_cust_acc_id : '||l_cust_acc_id) ;
898 END IF;
899 l_ord_query := create_ord_where_clause(l_ord_query,l_cust_acc_id);
900
901 --myerrams, start
902 IF p_func_currency <> 'Y' THEN
903 l_ord_query := create_curr_where_clause(l_ord_query,g_ord_currency);
904 END IF;
905 --myerrams, end
906
907 --myerrams, to Order the records by Sales Order Number
908 l_ord_query := l_ord_query || ' ORDER BY oha.order_number ';
909 --myerrams, end
910
911
912 IF l_debug_level > 0 THEN
913 oe_debug_pub.add( 'OEXCARR: Query to be executed for Order: '||l_ord_query ) ;
914 END IF;
915
916 OPEN c_ord_from_param FOR l_ord_query;
917 LOOP
918 FETCH c_ord_from_param
919 INTO l_order_number,
920 l_header_id,
921 l_ordered_date,
922 l_cust_po_number,
923 l_order_type,
924 l_sales_rep_name,
925 g_currency,
926 l_conversion_type_code,
927 l_ord_conversion_rate;
928 EXIT WHEN c_ord_from_param%NOTFOUND;
929 --myerrams, Bug: 5231338. This flag is used to decide whether to place customer total tags or not.
930 l_orders_flag := TRUE;
931
932 --myerrams, added for populating the order and line currency
933 IF(p_func_currency = 'Y' and g_currency <> g_func_currency) THEN
934 l_currency := g_func_currency;
935 ELSE
936 l_currency := g_currency;
937 END IF;
938
939 IF (p_sorted_by = 'CUSTOMER')
940 THEN
941 /* myerrams, Bug: 5231338. Place the header tags if there are any orders for this customer.
942 l_first_time_flag is used to place header tags only once.*/
943 IF l_first_time_flag THEN
944 put_ghead_tags(l_party_name
945 ,l_account_number
946 );
947 l_first_time_flag := FALSE;
948 END IF;
949 /* myerrams,Bug: 5231338; end */
950 put_ord_tags(l_order_number
951 ,l_ordered_date
952 ,l_cust_po_number
953 ,l_order_type
954 ,l_sales_rep_name
955 ,NULL
956 ,NULL
957 ,l_currency
958 );
959 ELSE
960 put_ord_tags(l_order_number
961 ,l_ordered_date
962 ,l_cust_po_number
963 ,l_order_type
964 ,l_sales_rep_name
965 ,l_party_name
966 ,l_account_number
967 ,l_currency
968 );
969 END IF;
970
971 --myerrams, currency conversion
972 IF l_debug_level > 0 THEN
973 oe_debug_pub.add( 'OEXCARR: Value of l_ord_conversion_rate:'||l_ord_conversion_rate);
974 END IF;
975 IF(p_func_currency = 'Y' and g_currency <> g_func_currency) THEN
976 IF l_debug_level > 0 THEN
977 oe_debug_pub.add( 'OEXCARR: before calculating the conversion_rate');
978 oe_debug_pub.add( 'OEXCARR: Value of g_currency:'||g_currency);
979 oe_debug_pub.add( 'OEXCARR: Value of g_func_currency:'||g_func_currency);
980 oe_debug_pub.add( 'OEXCARR: Value of l_ordered_date:'||l_ordered_date);
981 oe_debug_pub.add( 'OEXCARR: Value of l_conversion_type_code:'||l_conversion_type_code);
982 END IF;
983
984 if(l_ord_conversion_rate is null) then
985 l_conversion_rate := gl_conversion_rate_func
986 (g_currency,
987 g_func_currency,
988 l_ordered_date,
989 l_conversion_type_code
990 );
991 else
992 l_conversion_rate := l_ord_conversion_rate;
993 end if;
994
995 IF l_debug_level > 0 THEN
996 oe_debug_pub.add( 'OEXCARR: Value of l_conversion_rate:'||l_conversion_rate);
997 END IF;
998 END IF;
999 --myerrams, currency conversion, end
1000 l_lines_query := l_line_cursor_query;
1001
1002 l_lines_query := create_lines_where_clause(l_lines_query,l_header_id);
1003 --myerrams, added the order by clause
1004 l_lines_query := l_lines_query || ' Order by oln.line_number, oln.line_id ';
1005 --myerrams, end
1006
1007 IF l_debug_level > 0 THEN
1008 oe_debug_pub.add( 'OEXCARR: Query to be executed for Lines: '||l_lines_query ) ;
1009 END IF;
1010
1011 l_line_counter := 0; --myerrams, Initialize the Line counter.
1012
1013 OPEN c_line_from_param FOR l_lines_query;
1014 LOOP
1015 FETCH c_line_from_param
1016 INTO l_line_number,
1017 l_ordered_item,
1018 l_ordered_item_desc,
1019 l_internal_item,
1020 l_internal_item_desc,
1021 l_ordered_quantity,
1022 l_ordered_quantity_value,
1023 l_fulfilled_quantity,
1024 l_fulfilled_value,
1025 l_shipped_quantity,
1026 l_shipped_quantity_value,
1027 l_shipping_quantity_uom,
1028 l_accepted_quantity,
1029 l_acceptance_value;
1030 EXIT WHEN c_line_from_param%NOTFOUND;
1031 l_line_counter := l_line_counter + 1; --myerrams, Increase the counter by 1.
1032
1033 IF g_item_display = 'D'
1034 THEN
1035 l_item_value := l_internal_item_desc;
1036 ELSIF g_item_display = 'F'
1037 THEN
1038 l_item_value := l_internal_item;
1039 ELSIF g_item_display = 'I'
1040 THEN
1041 l_item_value := l_internal_item ||' - '||l_internal_item_desc;
1042 ELSIF g_item_display = 'P'
1043 THEN
1044 l_item_value := l_ordered_item_desc;
1045 ELSIF g_item_display = 'O'
1046 THEN
1047 l_item_value := l_ordered_item;
1048 ELSIF g_item_display = 'C'
1049 THEN
1050 l_item_value := l_ordered_item ||' - '||l_ordered_item_desc;
1051 END IF;
1052 IF l_accepted_quantity IS NOT NULL
1053 THEN
1054 l_pending_quantity := 0;
1055 l_pending_quantity_value := 0;
1056 IF l_accepted_quantity = 0
1057 THEN
1058 l_rejected_quantity := l_fulfilled_quantity;
1059 l_accepted_quantity := 0;
1060 l_accepted_quantity_value := 0;
1061 l_rejected_quantity_value := l_fulfilled_value;
1062 ELSE
1063 l_rejected_quantity := 0;
1064 l_accepted_quantity := l_accepted_quantity;
1065 l_accepted_quantity_value := l_acceptance_value;
1066 l_rejected_quantity_value := 0;
1067 END IF;
1068 ELSE
1069 l_accepted_quantity := 0;
1070 l_accepted_quantity_value := 0;
1071 l_rejected_quantity := 0;
1072 l_rejected_quantity_value := 0;
1073 --myerrams, Pending quantity has to be taken from either Fulfilled Quantity or Shipped Quantity or Ordered Quantity in the same order
1074 l_pending_quantity := NVL(l_fulfilled_quantity,NVL(l_shipped_quantity, NVL (l_ordered_quantity, 0)));
1075 l_pending_quantity_value := NVL(l_fulfilled_value,NVL(l_shipped_quantity_value, NVL (l_ordered_quantity_value, 0)));
1076
1077 END IF;
1078 l_line_accepted_total := l_line_accepted_total + nvl(l_accepted_quantity_value,0);
1079 l_line_rejected_total := l_line_rejected_total + nvl(l_rejected_quantity_value,0);
1080 l_line_pending_total := l_line_pending_total + nvl(l_pending_quantity_value,0);
1081 --myerrams, currency conversion
1082 IF(p_func_currency = 'Y' and g_currency <> g_func_currency) THEN
1083 --do a null and zero check
1084 l_accepted_quantity_value := l_accepted_quantity_value * l_conversion_rate;
1085 l_rejected_quantity_value := l_rejected_quantity_value * l_conversion_rate;
1086 l_pending_quantity_value := l_pending_quantity_value * l_conversion_rate;
1087 END IF;
1088 --myerrams, currency conversion, end
1089 put_line_tags(l_line_number
1090 ,l_item_value
1091 ,l_fulfilled_quantity
1092 ,l_shipping_quantity_uom
1093 ,l_accepted_quantity
1094 ,l_rejected_quantity
1095 ,l_pending_quantity
1096 ,l_accepted_quantity_value
1097 ,l_rejected_quantity_value
1098 ,l_pending_quantity_value
1099 ,l_currency
1100 );
1101 END LOOP; -- LOOP END for c_line_from_param
1102 close c_line_from_param;
1103 l_lines_query := NULL;
1104 IF l_debug_level > 0 THEN
1105 oe_debug_pub.add( 'OEXCARR: Value of l_line_accepted_total 2: '||l_line_accepted_total ) ;
1106 oe_debug_pub.add( 'OEXCARR: Value of l_line_rejected_total 2: '||l_line_rejected_total ) ;
1107 oe_debug_pub.add( 'OEXCARR: Value of l_line_pending_total 2: '||l_line_pending_total ) ;
1108 END IF;
1109 IF l_line_counter > 0 THEN --myerrams, put the order total tags only when there are lines present.
1110 --myerrams, currency conversion
1111 IF(p_func_currency = 'Y' and g_currency <> g_func_currency) THEN
1112 l_line_accepted_total := l_line_accepted_total * l_conversion_rate;
1113 l_line_rejected_total := l_line_rejected_total * l_conversion_rate;
1114 l_line_pending_total := l_line_pending_total * l_conversion_rate;
1115 END IF;
1116 --myerrams, currency conversion, end
1117 put_order_total_tags (l_line_accepted_total
1118 ,l_line_rejected_total
1119 ,l_line_pending_total);
1120 l_line_counter := 0;
1121 END IF;
1122 l_cust_accepted_total := l_cust_accepted_total + nvl(l_line_accepted_total,0);
1123 l_cust_rejected_total := l_cust_rejected_total + nvl(l_line_rejected_total,0);
1124 l_cust_pending_total := l_cust_pending_total + nvl(l_line_pending_total,0);
1125
1126 l_line_accepted_total := 0;
1127 l_line_rejected_total := 0;
1128 l_line_pending_total := 0;
1129 END LOOP;-- LOOP END for c_ord_from_param
1130 close c_ord_from_param;
1131 l_ord_query := NULL;
1132
1133 IF (p_sorted_by = 'CUSTOMER')
1134 THEN
1135 --myerrams, currency conversion
1136 IF(p_func_currency = 'Y' and g_currency <> g_func_currency) THEN
1137 l_cust_accepted_total := l_cust_accepted_total * l_conversion_rate;
1138 l_cust_rejected_total := l_cust_rejected_total * l_conversion_rate;
1139 l_cust_pending_total := l_cust_pending_total * l_conversion_rate;
1140 END IF;
1141 --myerrams, currency conversion, end
1142 /*myerrams, Bug: 5231338. Place the customer total tags if there are any orders for this customer.
1143 l_orders_flag is used to decide if there are any orders for this customer */
1144 IF l_orders_flag THEN
1145 put_cust_total_tags(l_cust_accepted_total
1146 ,l_cust_rejected_total
1147 ,l_cust_pending_total);
1148 END IF;
1149 /*myerrams, Bug: 5231338*/
1150 END IF;
1151 l_rep_accepted_total := l_rep_accepted_total + nvl(l_cust_accepted_total,0);
1152 l_rep_rejected_total := l_rep_rejected_total + nvl(l_cust_rejected_total,0);
1153 l_rep_pending_total := l_rep_pending_total + nvl(l_cust_pending_total,0);
1154 l_cust_accepted_total := 0;
1155 l_cust_rejected_total := 0;
1156 l_cust_pending_total := 0;
1157 END LOOP;-- LOOP END for c_cust_from_param
1158 close c_cust_from_param;
1159 l_cust_query := NULL;
1160 --myerrams, currency conversion
1161 IF(p_func_currency = 'Y' and g_currency <> g_func_currency) THEN
1162 l_rep_accepted_total := l_rep_accepted_total * l_conversion_rate;
1163 l_rep_rejected_total := l_rep_rejected_total * l_conversion_rate;
1164 l_rep_pending_total := l_rep_pending_total * l_conversion_rate;
1165 END IF;
1166 --myerrams, currency conversion, end
1167 put_rep_total_tags(l_rep_accepted_total
1168 ,l_rep_rejected_total
1169 ,l_rep_pending_total);
1170
1171 EXCEPTION
1172 WHEN NO_DATA_FOUND THEN
1173 fnd_file.put_line(FND_FILE.LOG, 'NO_DATA_FOUND exception in the program '||sqlerrm);
1174 p_ERRBUF := 'NO_DATA_FOUND exception in the program '||sqlerrm;
1175 p_RETCODE := 2;
1176 WHEN OTHERS THEN
1177 fnd_file.put_line(FND_FILE.LOG, 'OTHERS exception in the program '||sqlerrm);
1178 p_ERRBUF := 'OTHERS exception in the program '||sqlerrm;
1179 p_RETCODE := 2;
1180 END;
1181 --=============================================================================
1182
1183 --=============================================================================
1184 -- Start of comments
1185 -- PROCEDURE : Generate_ReportData
1186 -- PRE-CONDITIONS : None.
1187 -- COMMENTS : Generates XML data for Customer Acceptance Report.
1188 -- PARAMETERS :
1189 -- IN :
1190 -- p_sorted_by IN VARCHAR2 Required
1191 -- p_from_customer_name IN VARCHAR2 Optional
1192 -- Default = NULL
1193 -- p_to_customer_name IN VARCHAR2 Optional
1194 -- Default = NULL
1195 -- p_from_customer_no IN VARCHAR2 Optional
1196 -- Default = NULL
1197 -- p_to_customer_no IN VARCHAR2 Optional
1198 -- Default = NULL
1199 -- p_from_order_date IN DATE Optional
1200 -- Default = NULL
1201 -- p_to_order_date IN DATE Optional
1202 -- Default = NULL
1203 -- p_from_fulfill_date IN DATE Optional
1204 -- Default = NULL
1205 -- p_to_fulfill_date IN DATE Optional
1206 -- Default = NULL
1207 -- p_from_accepted_date IN DATE Optional
1208 -- Default = NULL
1209 -- p_to_accepted_date IN DATE Optional
1210 -- Default = NULL
1211 -- p_acceptance_status IN VARCHAR2 Optional
1212 -- Default = NULL
1213 -- p_item_display IN VARCHAR2 Required
1214 -- p_func_currency IN VARCHAR2 Required
1215 -- OUT :
1216 -- errbuf OUT NOCOPY VARCHAR2
1217 -- retcode OUT NOCOPY VARCHAR2
1218 --
1219 -- EXCEPTIONS : None.
1220 -- NOTES : This Procedure is called from the concurrent program
1221 -- which generates the XML data for the customer acceptance
1222 -- rejection report.
1223 -- End of comments
1224 --=============================================================================
1225
1226 PROCEDURE Generate_ReportData
1227 ( ERRBUF OUT NOCOPY VARCHAR2,
1228 RETCODE OUT NOCOPY VARCHAR2,
1229 p_sorted_by IN VARCHAR2,
1230 p_customer_name_low IN VARCHAR2,
1231 p_customer_name_high IN VARCHAR2,
1232 p_customer_no_low IN VARCHAR2,
1233 p_customer_no_high IN VARCHAR2,
1234 p_order_type_low IN VARCHAR2,
1235 p_order_type_high IN VARCHAR2,
1236 p_order_number_low IN NUMBER,
1237 p_order_number_high IN NUMBER,
1238 --myerrams, Bug: 5214119. Modified the types of all date vars to VARCHAR2 as conc prog passes VARCHAR2.
1239 p_order_date_low IN VARCHAR2,
1240 p_order_date_high IN VARCHAR2,
1241 p_fulfill_date_low IN VARCHAR2,
1242 p_fulfill_date_high IN VARCHAR2,
1243 p_accepted_date_low IN VARCHAR2,
1244 p_accepted_date_high IN VARCHAR2,
1245 --myerrams, Bug: 5214119. end.
1246 p_acceptance_status IN VARCHAR2,
1247 p_item_display IN VARCHAR2,
1248 p_func_currency IN VARCHAR2
1249 )
1250 IS
1251 --myerrams, new variables
1252 l_cust_query_to_append VARCHAR2(15000);
1253 l_curr_query VARCHAR2(15000);
1254
1255 --myerrams, Modified the following query to add oe_order_lines_all
1256 l_curr_cursor_query VARCHAR2(15000) := 'SELECT distinct oha.TRANSACTIONAL_CURR_CODE '||
1257 'FROM oe_order_headers_all oha, '||
1258 ' oe_order_lines_all oln, '||
1259 ' oe_transaction_types_all ota, '||
1260 ' ra_salesreps_all rsa '||
1261 'WHERE oha.order_type_id = ota.transaction_type_id '||
1262 'AND oha.header_id = oln.header_id '||
1263 'AND oha.salesrep_id = rsa.salesrep_id ';
1264
1265 l_cust_soldto_cursor_query VARCHAR2(15000) := 'SELECT DISTINCT hca.cust_account_id '||
1266 'FROM hz_parties hp, '||
1267 ' hz_cust_accounts_all hca, '||
1268 ' oe_order_headers_all oha, '||
1269 ' oe_order_lines_all oln, '||
1270 ' oe_transaction_types_all ota '||
1271 'WHERE hp.party_id = hca.party_id '||
1272 'AND hca.cust_account_id = oha.sold_to_org_id '||
1273 'AND oha.header_id = oln.header_id '||
1274 'AND oha.order_type_id = ota.transaction_type_id ';
1275 TYPE c_curr_from_param_type IS REF CURSOR;
1276 c_curr_from_param c_curr_from_param_type;
1277 --myerrams, new variables, end
1278
1279 BEGIN
1280
1281 IF l_debug_level > 0
1282 THEN
1283 oe_debug_pub.add( 'OEXCARR: Starting Generate_ReportData procedure') ;
1284 END IF;
1285
1286 --myerrams, Bug: 5214119. Converting the VARCHAR2 variables to DATE variables.
1287 g_order_date_low := fnd_date.canonical_to_date(p_order_date_low);
1288 g_order_date_high := fnd_date.canonical_to_date(p_order_date_high);
1289 g_fulfill_date_low := fnd_date.canonical_to_date(p_fulfill_date_low);
1290 g_fulfill_date_high := fnd_date.canonical_to_date(p_fulfill_date_high);
1291 g_accepted_date_low := fnd_date.canonical_to_date(p_accepted_date_low);
1292 g_accepted_date_high := fnd_date.canonical_to_date(p_accepted_date_high);
1293 --myerrams, Bug: 5214119. end.
1294 g_org_id := MO_GLOBAL.get_current_org_id;
1295 BEGIN
1296 SELECT name
1297 INTO g_operating_unit
1298 FROM hr_organization_units
1299 WHERE organization_id = g_org_id;
1300 EXCEPTION
1301 WHEN OTHERS
1302 THEN
1303 IF l_debug_level > 0
1304 THEN
1305 oe_debug_pub.add( 'OEXCARR: Error in MO Query, Value of g_org_id '||g_org_id) ;
1306 END IF;
1307 END;
1308
1309 g_sorted_by := p_sorted_by;
1310 g_customer_name_low := p_customer_name_low;
1311 g_customer_name_high := p_customer_name_high;
1312 g_customer_no_low := p_customer_no_low;
1313 g_customer_no_high := p_customer_no_high;
1314 g_order_type_low := p_order_type_low;
1315 g_order_type_high := p_order_type_high;
1316 g_order_no_low := p_order_number_low;
1317 g_order_no_high := p_order_number_high;
1318 g_acceptance_status := p_acceptance_status; --myerrams, Bug:4749985
1319 g_item_display := p_item_display;
1320 IF l_debug_level > 0
1321 THEN
1322 oe_debug_pub.add('OEXCARR: Before call to put_parameter_tags procedure, p_func_currency:'||p_func_currency);
1323 END IF;
1324 --myerrams, Bug: 5484771
1325 IF p_func_currency IS NOT NULL
1326 THEN
1327 SELECT meaning into g_func_currency_desc
1328 FROM FND_LOOKUPS
1329 WHERE lookup_type = 'YES_NO'
1330 AND lookup_code = p_func_currency;
1331 END IF;
1332
1333 --myerrams
1334 --Call the procedure to place the parameter values in the XML output.
1335 put_parameter_tags;
1336 IF l_debug_level > 0
1337 THEN
1338 oe_debug_pub.add( 'OEXCARR: After call to put_parameter_tags procedure') ;
1339 END IF;
1340
1341 IF p_func_currency = 'Y'
1342 THEN
1343 SELECT curr.currency_code
1344 INTO g_func_currency
1345 FROM fnd_currencies curr,
1346 gl_sets_of_books sob
1347 WHERE curr.enabled_flag ='Y'
1348 AND sob.set_of_books_id = (select FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
1349 from dual)
1350 AND curr.currency_code = sob.currency_code;
1351
1352 --myerrams, start
1353 put_gcurr_tags(g_func_currency,g_sorted_by);
1354 put_remaining_tags(p_sorted_by,P_FUNC_CURRENCY,ERRBUF,RETCODE);
1355 --myerrams, end
1356
1357 --myerrams, start
1358 ELSE
1359 --get the cust query to append to curr query
1360 l_cust_query_to_append := l_cust_soldto_cursor_query;
1361 l_cust_query_to_append := create_where_clause (l_cust_query_to_append);
1362
1363 --get the curr query
1364 l_curr_query := l_curr_cursor_query;
1365 --myerrams, constraining to the passed Operating unit
1366 IF g_org_id IS NOT NULL
1367 THEN
1368 l_curr_query := l_curr_query || ' AND oha.org_id =' || g_org_id;
1369 END IF;
1370 --myerrams, end
1371 --myerrams, Bug: 5218214. There is a white space char missing before AND. Added that white space char.
1372 l_curr_query := l_curr_query || ' AND oha.sold_to_org_id in (' || l_cust_query_to_append || ') ';
1373 l_curr_query := create_ord_where_clause(l_curr_query,NULL);
1374
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add( 'OEXCARR: Query to be executed: '||l_curr_query ) ;
1377 END IF;
1378 OPEN c_curr_from_param FOR l_curr_query;
1379 LOOP
1380 IF l_debug_level > 0 THEN
1381 oe_debug_pub.add( 'OEXCARR: Inside the LOOP for Currency');
1382 END IF;
1383
1384 FETCH c_curr_from_param
1385 INTO
1386 g_ord_currency;
1387 EXIT WHEN c_curr_from_param%NOTFOUND;
1388 IF l_debug_level > 0 THEN
1389 oe_debug_pub.add( 'OEXCARR: Value of g_ord_currency:'||g_ord_currency);
1390 END IF;
1391 put_gcurr_tags(g_ord_currency,g_sorted_by);
1392 put_remaining_tags(p_sorted_by,P_FUNC_CURRENCY,ERRBUF,RETCODE);
1393 END LOOP; --c_curr_from_param loop
1394 --myerrams, end
1395 END IF;
1396
1397 --myerrams, put the value for no_data_found tag.
1398 IF g_counter = 0 THEN
1399 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_DATA_FOUND>'|| 'Y' || '</NO_DATA_FOUND>');
1400 ELSE
1401 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_DATA_FOUND>'|| 'N' || '</NO_DATA_FOUND>');
1402 END IF;
1403
1404 --myerrams, start
1405 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</OEXCARR>');
1406 --myerrams, end
1407
1408 EXCEPTION
1409 WHEN NO_DATA_FOUND THEN
1410 fnd_file.put_line(FND_FILE.LOG, 'NO_DATA_FOUND exception in the program '||sqlerrm);
1411 ERRBUF := 'NO_DATA_FOUND exception in the program '||sqlerrm;
1412 RETCODE := 2;
1413 WHEN OTHERS THEN
1414 fnd_file.put_line(FND_FILE.LOG, 'OTHERS exception in the program '||sqlerrm);
1415 ERRBUF := 'OTHERS exception in the program '||sqlerrm;
1416 RETCODE := 2;
1417
1418 END Generate_ReportData;
1419
1420 END OE_CustAcceptRep_PVT;