[Home] [Help]
PACKAGE BODY: APPS.AP_APXINROH_XMLP_PKG
Source
1 PACKAGE BODY AP_APXINROH_XMLP_PKG AS
2 /* $Header: APXINROHB.pls 120.1 2008/01/06 11:52:23 vjaganat noship $ */
3
4 FUNCTION get_base_curr_data RETURN BOOLEAN IS
5
6 base_curr ap_system_parameters.base_currency_code%TYPE; prec fnd_currencies_vl.precision%TYPE; min_au fnd_currencies_vl.minimum_accountable_unit%TYPE; descr fnd_currencies_vl.description%TYPE;
7 BEGIN
8
9 base_curr := '';
10 prec := 0;
11 min_au := 0;
12 descr := '';
13
14 SELECT p.base_currency_code,
15 c.precision,
16 c.minimum_accountable_unit,
17 c.description
18 INTO base_curr,
19 prec,
20 min_au,
21 descr
22 FROM ap_system_parameters p,
23 fnd_currencies_vl c
24 WHERE p.base_currency_code = c.currency_code;
25
26 c_base_currency_code := base_curr;
27 c_base_precision := prec;
28 c_base_min_acct_unit := min_au;
29 c_base_description := descr;
30
31 RETURN (TRUE);
32
33 RETURN NULL; EXCEPTION
34
35 WHEN OTHERS THEN
36 RETURN (FALSE);
37
38 END;
39
40 FUNCTION custom_init RETURN BOOLEAN IS
41
42 BEGIN
43
44 if P_DATE_PAR = 'Discount Date' then
45 P_START_DISCOUNT_DATE := P_START_DATE;
46 P_END_DISCOUNT_DATE := P_END_DATE;
47 else
48 P_START_DUE_DATE := P_START_DATE;
49 P_END_DUE_DATE := P_END_DATE;
50 end if;
51
52 return(TRUE);
53
54 END;
55
56 FUNCTION get_cover_page_values RETURN BOOLEAN IS
57
58 BEGIN
59
60 RETURN(TRUE);
61
62 RETURN NULL; EXCEPTION
63 WHEN OTHERS THEN
64 RETURN(FALSE);
65
66 END;
67
68 FUNCTION get_nls_strings RETURN BOOLEAN IS
69 nls_all ap_lookup_codes.displayed_field%TYPE; nls_na ap_lookup_codes.displayed_field%TYPE; nls_no_desc ap_lookup_codes.displayed_field%TYPE; nls_yes fnd_lookups.meaning%TYPE; nls_no fnd_lookups.meaning%TYPE;
70 BEGIN
71
72 nls_all := '';
73 nls_yes := '';
74 nls_no := '';
75
76 SELECT ly.meaning,
77 ln.meaning,
78 l1.displayed_field,
79 l2.displayed_field,
80 l3.displayed_field
81 INTO nls_yes, nls_no, nls_all, nls_na, nls_no_desc
82 FROM fnd_lookups ly, fnd_lookups ln,
83 ap_lookup_codes l1, ap_lookup_codes l2,
84 ap_lookup_codes l3
85 WHERE ly.lookup_type = 'YES_NO'
86 AND ly.lookup_code = 'Y'
87 AND ln.lookup_type = 'YES_NO'
88 AND ln.lookup_code = 'N'
89 AND l1.lookup_type = 'NLS REPORT PARAMETER'
90 AND l1.lookup_code = 'ALL'
91 AND l2.lookup_type = 'NLS REPORT PARAMETER'
92 AND l2.lookup_code = 'NA'
93 AND l3.lookup_type = 'NLS TRANSLATION'
94 AND l3.lookup_code = 'NO DESCRIPTION';
95
96 c_nls_yes := nls_yes;
97 c_nls_no := nls_no;
98 c_nls_all := nls_all;
99 c_nls_na := nls_na;
100 c_nls_no_description := nls_no_desc;
101
102 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
103
104 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_no_data_exists"');*/null;
105
106 /*c_nls_no_data_exists := '*** '||c_nls_no_data_exists||' ***';*/
107 c_nls_no_data_exists := 'No Data Found';
108
109 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
110
111 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_end_of_report"');*/null;
112
113 /*c_nls_end_of_report := '*** '||c_nls_end_of_report||' ***';*/
114 c_nls_end_of_report := 'End of Report';
115
116 RETURN (TRUE);
117
118 RETURN NULL; EXCEPTION
119 WHEN OTHERS THEN
120 RETURN (FALSE);
121 END;
122
123 function BeforeReport return boolean is
124 begin
125 LP_START_CREATION_DATE := to_char(P_START_CREATION_DATE, 'DD-MON-YY');
126 LP_END_CREATION_DATE := to_char(P_END_CREATION_DATE, 'DD-MON-YY');
127 LP_START_DATE := to_char(P_START_DATE, 'DD-MON-YY');
128 LP_END_DATE := to_char(P_END_DATE, 'DD-MON-YY');
129
130
131 DECLARE
132
133 init_failure EXCEPTION;
134
135 BEGIN
136 /*srw.message(0, 'And so it begins ...');*/null;
137
138
139 if P_ORDER_BY is null or P_ORDER_BY <> 'Vendor Name' then
140 P_ORDER_BY := 'Hold Name';
141 end if;
142
143
144
145 /*SRW.USER_EXIT('FND SRWINIT');*/null;
146
147 IF (p_debug_switch = 'Y') THEN
148 /*SRW.MESSAGE('1','After SRWINIT');*/null;
149
150 END IF;
151
152
153 IF (get_company_name() <> TRUE) THEN RAISE init_failure;
154 END IF;
155 IF (p_debug_switch = 'Y') THEN
156 /*SRW.MESSAGE('2','After Get_Company_Name');*/null;
157
158 END IF;
159
160
161 IF (get_nls_strings() <> TRUE) THEN RAISE init_failure;
162 END IF;
163 IF (p_debug_switch = 'Y') THEN
164 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
165
166 END IF;
167
168
169 IF (get_base_curr_data() <> TRUE) THEN RAISE init_failure;
170 END IF;
171 IF (p_debug_switch = 'Y') THEN
172 /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
173
174 END IF;
175
176
177 IF (get_parameter_disp_value() = TRUE)then
178 IF (p_debug_switch = 'Y') THEN
179 /*SRW.MESSAGE('8', 'After Get_Parameter_Disp_Value');*/null;
180
181 END IF;
182 END IF;
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207 IF(custom_init() <> TRUE) THEN
208 RAISE init_failure;
209 END IF;
210 IF (p_debug_switch = 'Y') THEN
211 /*SRW.MESSAGE('7','After Custom_Init');*/null;
212
213 END IF;
214
215
216
217
218 IF (p_debug_switch = 'Y') THEN
219 /*SRW.BREAK;*/null;
220
221 END IF;
222
223
224 BEGIN
225
226 SELECT sort_by_alternate_field
227 INTO SORT_BY_ALTERNATE
228 FROM AP_SYSTEM_PARAMETERS;
229
230
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 SORT_BY_ALTERNATE := 'N';
235 END;
236
237
238
239 RETURN (TRUE);
240
241
242
243 EXCEPTION
244
245 WHEN OTHERS THEN
246
247 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
248
249
250 END; return (TRUE);
251 end;
252
253 function AfterReport return boolean is
254 begin
255
256 BEGIN
257 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
258
259 IF (P_DEBUG_SWITCH = 'Y') THEN
260 /*SRW.MESSAGE('20','After SRWEXIT');*/null;
261
262 END IF;
263 EXCEPTION
264 WHEN OTHERS THEN
265 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
266
267 END; return (TRUE);
268 end;
269
270 FUNCTION get_company_name RETURN BOOLEAN IS
271 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
272 l_name gl_sets_of_books.name%TYPE;
273 l_sob_id NUMBER;
274 l_report_start_date DATE;
275 BEGIN
276 l_report_start_date := sysdate; l_sob_id := p_set_of_books_id;
277 SELECT name,
278 chart_of_accounts_id
279 INTO l_name,
280 l_chart_of_accounts_id
281 FROM gl_sets_of_books
282 WHERE set_of_books_id = l_sob_id;
283
284 c_company_name_header := l_name;
285 c_chart_of_accounts_id := l_chart_of_accounts_id;
286 c_report_start_date := l_report_start_date;
287
288 RETURN (TRUE);
289
290 RETURN NULL; EXCEPTION
291
292 WHEN OTHERS THEN
293 RETURN (FALSE);
294
295 END;
296
297 FUNCTION get_flexdata RETURN BOOLEAN IS
298
299 BEGIN
300
301
302 null;
303 RETURN (TRUE);
304
305 RETURN NULL; EXCEPTION
306 WHEN OTHERS THEN
307 RETURN(FALSE);
308 END;
309
310 function C_ORDER_BYFormula return VARCHAR2 is
311 begin
312
313 if P_ORDER_BY = 'Hold Name' then
314 return(
315 'order by decode(:P_ORDER_BY,''Vendor Name'',''Do not sort by Hold Name'',upper(decode(h.hold_lookup_code,null,:C_NLS_NA,alc.displayed_field))),
316 hp.party_name,
317 inv1.invoice_date,
318 inv1.invoice_id,
319 B.batch_name,
320 inv1.invoice_num,
321 decode(h.hold_lookup_code,null,:C_NLS_NA,h.hold_lookup_code),
322 decode(h.hold_lookup_code,null,:C_NLS_NA,alc.displayed_field),
323 alc.displayed_field,
324 decode(:SORT_BY_ALTERNATE, ''Y'', upper(hp.organization_name_phonetic), upper(hp.party_name)),
325 inv1.invoice_date asc,
326 upper(B.batch_name),
327 inv1.vendor_id,
328 inv1.invoice_num,
329 DECODE(inv1.invoice_currency_code, :C_BASE_CURRENCY_CODE,inv1.invoice_amount,inv1.base_amount) desc'
330 );
331
332 else
333 return(
334 'order by decode(:P_ORDER_BY,''Vendor Name'',''Do not sort by Hold Name'',upper(decode(h.hold_lookup_code,null,:C_NLS_NA,alc.displayed_field))),
335 hp.party_name,
336 inv1.invoice_date,
337 inv1.invoice_id,
338 B.batch_name,
339 inv1.invoice_num,
340 decode(h.hold_lookup_code,null,:C_NLS_NA,h.hold_lookup_code),
341 decode(h.hold_lookup_code,null,:C_NLS_NA,alc.displayed_field),
342 decode(:SORT_BY_ALTERNATE, ''Y'', upper(hp.organization_name_phonetic), upper(hp.party_name)),
343 inv1.invoice_date asc,
344 upper(B.batch_name),
345 inv1.vendor_id,
346 inv1.invoice_num,
347 alc.displayed_field,
348 DECODE(inv1.invoice_currency_code, :C_BASE_CURRENCY_CODE,inv1.invoice_amount,inv1.base_amount) desc'
349 );
350 end if;
351
352 RETURN ' '; end;
353
354 function C_ORDER_BY1Formula return VARCHAR2 is
355 begin
356
357 if P_ORDER_BY = 'Hold Name' then
358 return(
359 'order by decode(:SORT_BY_ALTERNATE, ''Y'', upper(hp.organization_name_phonetic), upper(hp.party_name)),
360 hp.party_name,
361 inv1.vendor_id,
362 to_char(inv1.invoice_date,''YYYYMM''),
363 to_char(inv1.invoice_date,''fmMonth YYYY''),
364 inv1.invoice_date,
365 B.batch_name,
366 inv1.invoice_num,
367 inv1.invoice_id,
368 decode(h.hold_lookup_code,null,:C_NLS_NA,alc.displayed_field),
369 decode(h.hold_lookup_code,null,:C_NLS_NA,h.hold_lookup_code),
370 alc.displayed_field,
371 decode(:SORT_BY_ALTERNATE, ''Y'', upper(hp.organization_name_phonetic), upper(hp.party_name)),
372 inv1.invoice_date asc,
373 upper(B.batch_name),
374 inv1.vendor_id,
375 inv1.invoice_num,
376 DECODE(inv1.invoice_currency_code, :C_BASE_CURRENCY_CODE,inv1.invoice_amount,inv1.base_amount) desc'
377 );
378
379 else
380 return(
381 ' order by decode(:SORT_BY_ALTERNATE, ''Y'', upper(hp.organization_name_phonetic), upper(hp.party_name)),
382 hp.party_name,
383 inv1.vendor_id,
384 to_char(inv1.invoice_date,''YYYYMM''),
385 to_char(inv1.invoice_date,''fmMonth YYYY''),
386 inv1.invoice_date,
387 B.batch_name,
388 inv1.invoice_num,
389 inv1.invoice_id,
390 decode(h.hold_lookup_code,null,:C_NLS_NA,alc.displayed_field),
391 decode(h.hold_lookup_code,null,:C_NLS_NA,h.hold_lookup_code),
392 decode(:SORT_BY_ALTERNATE, ''Y'', upper(hp.organization_name_phonetic), upper(hp.party_name)),
393 inv1.invoice_date asc,
394 upper(B.batch_name),
395 inv1.vendor_id,
396 inv1.invoice_num,
397 alc.displayed_field,
398 DECODE(inv1.invoice_currency_code, :C_BASE_CURRENCY_CODE,inv1.invoice_amount,inv1.base_amount) desc'
399 );
400 end if;
401
402 RETURN ' '; end;
403
404 FUNCTION GET_PARAMETER_DISP_VALUE RETURN BOOLEAN IS
405
406 l_party_name hz_parties.party_name%TYPE;
407 l_order_by VARCHAR2(80);
408 l_hold_period_option VARCHAR2(80);
409 l_due_or_discount VARCHAR2(80);
410 l_include_hold_desc VARCHAR2(80);
411
412 BEGIN
413
414 if p_party_id is not null then
415 SELECT party_name
416 INTO l_party_name
417 FROM hz_parties
418 WHERE party_id = p_party_id;
419
420 cp_party_name := l_party_name;
421 end if;
422
423 if p_order_by is not null then
424 SELECT displayed_field
425 INTO l_order_by
426 FROM ap_lookup_codes
427 WHERE lookup_type = 'RPT ORDER BY'
428 AND lookup_code = p_order_by;
429 end if;
430
431 if p_subtotal_flag is not null then
432 SELECT meaning
433 INTO l_hold_period_option
434 FROM fnd_lookups
435 WHERE lookup_type = 'YES_NO'
436 AND lookup_code = p_subtotal_flag;
437 end if;
438
439 if p_date_par is not null then
440 SELECT displayed_field
441 INTO l_due_or_discount
442 FROM ap_lookup_codes
443 WHERE lookup_type = 'DATE RANGE'
444 AND lookup_code = p_date_par;
445 end if;
446
447 if p_hold_desc_flag is not null then
448 SELECT meaning
449 INTO l_include_hold_desc
450 FROM fnd_lookups
451 WHERE lookup_type = 'YES_NO'
452 AND lookup_code = p_hold_desc_flag;
453 end if;
454
455 cp_order_by := l_order_by;
456 cp_hold_period_option := l_hold_period_option;
457 cp_due_or_discount := l_due_or_discount;
458 cp_include_hold_desc := l_include_hold_desc;
459
460
461 RETURN(TRUE);
462 RETURN NULL; EXCEPTION
463
464 WHEN OTHERS THEN
465 RETURN (FALSE);
466
467
468 END;
469
470 function c_rep_total_countformula(c_total_count in number, c_pay_on_hold_count in number, c_sites_on_hold_count in number, c_total_count1 in number) return number is
471 begin
472
473 if p_hold_code is null then
474 if P_ORDER_BY = 'Hold Name' then
475 return (nvl(c_total_count,0) + nvl(c_pay_on_hold_count,0)
476 + nvl(c_sites_on_hold_count,0));
477 else
478 return (nvl(c_total_count1,0) + nvl(c_pay_on_hold_count,0)
479 + nvl(c_sites_on_hold_count,0));
480 end if;
481 else
482 if P_ORDER_BY = 'Hold Name' then
483 return (nvl(c_total_count,0));
484 else
485 return (nvl(c_total_count1,0));
486 end if;
487 end if;
488 end;
489
490 function c_rep_total_remainingformula(c_total_remaining in number, c_ph_total_remaining in number, c_sh_total_remaining in number, c_total_remaining1 in number) return number is
491 begin
492
493 if p_hold_code is null then
494 if P_ORDER_BY = 'Hold Name' then
495 return (nvl(c_total_remaining,0) + nvl(c_ph_total_remaining,0)
496 + nvl(c_sh_total_remaining,0));
497 else
498 return (nvl(c_total_remaining1,0) + nvl(c_ph_total_remaining,0)
499 + nvl(c_sh_total_remaining,0));
500 end if;
501 else
502 if P_ORDER_BY = 'Hold Name' then
503 return (nvl(c_total_remaining,0));
504 else
505 return (nvl(c_total_remaining1,0));
506 end if;
507 end if;
508 end;
509
510 function c_rep_total_originalformula(c_total_original in number, c_ph_total_original in number, c_sh_total_original in number, c_total_original1 in number) return number is
511 begin
512
513 if p_hold_code is null then
514 if P_ORDER_BY = 'Hold Name' then
515 return (nvl(c_total_original,0) + nvl(c_ph_total_original,0)
516 + nvl(c_sh_total_original,0));
517 else
518 return (nvl(c_total_original1,0) + nvl(c_ph_total_original,0)
519 + nvl(c_sh_total_original,0));
520 end if;
521 else
522 if P_ORDER_BY = 'Hold Name' then
523 return (nvl(c_total_original,0));
524 else
525 return (nvl(c_total_original1,0));
526 end if;
527 end if;
528 end;
529
530 function C_vendor_clauseFormula return Char is
531 begin
532 if P_PARTY_ID is not null then
533 return('AND hp.party_id = '||to_char(P_PARTY_ID)||' ' );
534 else
535 return ' ';
536 end if;
537 end;
538
539 --Functions to refer Oracle report placeholders--
540
541 Function C_BASE_CURRENCY_CODE_p return varchar2 is
542 Begin
543 return C_BASE_CURRENCY_CODE;
544 END;
545 Function C_BASE_PRECISION_p return number is
546 Begin
547 return C_BASE_PRECISION;
548 END;
549 Function C_BASE_MIN_ACCT_UNIT_p return number is
550 Begin
551 return C_BASE_MIN_ACCT_UNIT;
552 END;
553 Function C_BASE_DESCRIPTION_p return varchar2 is
554 Begin
555 return C_BASE_DESCRIPTION;
556 END;
557 Function C_COMPANY_NAME_HEADER_p return varchar2 is
558 Begin
559 return C_COMPANY_NAME_HEADER;
560 END;
561 Function C_REPORT_START_DATE_p return date is
562 Begin
563 return C_REPORT_START_DATE;
564 END;
565 Function C_NLS_YES_p return varchar2 is
566 Begin
567 return C_NLS_YES;
568 END;
569 Function C_NLS_NO_p return varchar2 is
570 Begin
571 return C_NLS_NO;
572 END;
573 Function C_NLS_ALL_p return varchar2 is
574 Begin
575 return C_NLS_ALL;
576 END;
577 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
578 Begin
579 return C_NLS_NO_DATA_EXISTS;
580 END;
581 Function C_CHART_OF_ACCOUNTS_ID_p return number is
582 Begin
583 return C_CHART_OF_ACCOUNTS_ID;
584 END;
585 Function C_NLS_NA_p return varchar2 is
586 Begin
587 return C_NLS_NA;
588 END;
589 Function C_NLS_NO_DESCRIPTION_p return varchar2 is
590 Begin
591 return C_NLS_NO_DESCRIPTION;
592 END;
593 Function C_NLS_END_OF_REPORT_p return varchar2 is
594 Begin
595 return C_NLS_END_OF_REPORT;
596 END;
597 Function CP_PARTY_NAME_p return varchar2 is
598 Begin
599 return CP_PARTY_NAME;
600 END;
601 Function CP_ORDER_BY_p return varchar2 is
602 Begin
603 return CP_ORDER_BY;
604 END;
605 Function CP_HOLD_PERIOD_OPTION_p return varchar2 is
606 Begin
607 return CP_HOLD_PERIOD_OPTION;
608 END;
609 Function CP_INCLUDE_HOLD_DESC_p return varchar2 is
610 Begin
611 return CP_INCLUDE_HOLD_DESC;
612 END;
613 Function CP_DUE_OR_DISCOUNT_p return varchar2 is
614 Begin
615 return CP_DUE_OR_DISCOUNT;
616 END;
617 END AP_APXINROH_XMLP_PKG ;
618
619