[Home] [Help]
PACKAGE BODY: APPS.AP_APXT7VEE_XMLP_PKG
Source
1 PACKAGE BODY AP_APXT7VEE_XMLP_PKG AS
2 /* $Header: APXT7VEEB.pls 120.0 2007/12/27 08:38:49 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
45 RETURN (TRUE);
46
47 RETURN NULL; EXCEPTION
48
49 WHEN OTHERS THEN
50 RETURN (FALSE);
51
52 END;
53
54 FUNCTION get_cover_page_values RETURN BOOLEAN IS
55
56 BEGIN
57
58 RETURN(TRUE);
59
60 RETURN NULL; EXCEPTION
61 WHEN OTHERS THEN
62 RETURN(FALSE);
63
64 END;
65
66 FUNCTION get_nls_strings RETURN BOOLEAN IS
67 nls_void ap_lookup_codes.displayed_field%TYPE; nls_na ap_lookup_codes.displayed_field%TYPE; nls_all ap_lookup_codes.displayed_field%TYPE; nls_yes fnd_lookups.meaning%TYPE; nls_no fnd_lookups.meaning%TYPE;
68 BEGIN
69
70 SELECT ly.meaning,
71 ln.meaning,
72 l1.displayed_field,
73 l2.displayed_field,
74 l3.displayed_field
75 INTO nls_yes,
76 nls_no,
77 nls_all,
78 nls_void,
79 nls_na
80 FROM fnd_lookups ly,
81 fnd_lookups ln,
82 ap_lookup_codes l1,
83 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 TRANSLATION'
92 AND l2.lookup_code = 'VOID'
93 AND l3.lookup_type = 'NLS REPORT PARAMETER'
94 AND l3.lookup_code = 'NA';
95
96 C_NLS_YES := nls_yes;
97 C_NLS_NO := nls_no;
98 C_NLS_ALL := nls_all;
99 C_NLS_VOID := nls_void;
100 C_NLS_NA := nls_na;
101
102
103
104 /*srw.user_exit('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
105
106 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_NLS_NO_DATA_EXISTS"');*/null;
107
108 /*srw.user_exit('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
109
110 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_NLS_END_OF_REPORT"');*/null;
111
112
113 C_NLS_NO_DATA_EXISTS := '*** '||C_NLS_NO_DATA_EXISTS||' ***';
114 C_NLS_END_OF_REPORT := '*** '||C_NLS_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
126
127
128 DECLARE
129
130 init_failure EXCEPTION;
131
132 BEGIN
133
134
135 C_REPORT_START_DATE := sysdate;
136
137
138
139
140 /*SRW.USER_EXIT('FND SRWINIT');*/null;
141
142 IF (p_debug_switch in ('y','Y')) THEN
143 /*SRW.MESSAGE('1','After SRWINIT');*/null;
144
145 END IF;
146
147
148
149
150
151
152
153 IF (get_company_name() <> TRUE) THEN RAISE init_failure;
154 END IF;
155 IF (p_debug_switch in ('y','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 in ('y','Y')) THEN
164 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
165
166 END IF;
167
168
169
170 IF (get_base_curr_data() <> TRUE) THEN RAISE init_failure;
171 END IF;
172 IF (p_debug_switch in ('y','Y')) THEN
173 /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
174
175 END IF;
176
177
178
179
180
181
182
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
208
209
210
211 IF (get_entity_name() <> TRUE) then
212 raise init_failure;
213 END IF;
214 IF (p_debug_switch in ('y','Y')) THEN
215 /*SRW.MESSAGE('9','After Get_Entity_Name');*/null;
216
217 END IF;
218
219 IF (get_payment_exists() <> TRUE) then
220 RAISE init_failure;
221 END IF;
222 IF (p_debug_switch in ('y','Y')) THEN
223 /*SRW.MESSAGE('10', 'After get_payment_exists');*/null;
224
225 END IF;
226
227 IF (get_balancing_segment() <> TRUE) then
228 RAISE init_failure;
229 END IF;
230 IF (p_debug_switch in ('y','Y')) THEN
231 /*SRW.MESSAGE('11', 'After get_balancing_segment');*/null;
232
233 END IF;
234
235
236
237 IF (p_debug_switch in ('y','Y')) THEN
238 /*SRW.BREAK;*/null;
239
240 END IF;
241
242
243
244 RETURN (TRUE);
245
246
247
248 EXCEPTION
249
250 WHEN OTHERS THEN
251
252 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
253
254
255 END;
256 return (TRUE);
257 end;
258
259 function AfterReport return boolean is
260 begin
261
262 BEGIN
263 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
264
265 IF (P_DEBUG_SWITCH = 'Y') THEN
266 /*SRW.MESSAGE('20','After SRWEXIT');*/null;
267
268 END IF;
269 EXCEPTION
270 WHEN OTHERS THEN
271 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
272
273 END; return (TRUE);
274 end;
275
276 FUNCTION get_company_name RETURN BOOLEAN IS
277 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
278 l_name gl_sets_of_books.name%TYPE;
279 l_sob_id number;
280 BEGIN
281
282 if P_SET_OF_BOOKS_ID is not null then
283 l_sob_id := p_set_of_books_id;
284 SELECT name,
285 chart_of_accounts_id
286 INTO l_name,
287 l_chart_of_accounts_id
288 FROM gl_sets_of_books
289 WHERE set_of_books_id = l_sob_id;
290
291 c_company_name_header := l_name;
292 c_chart_of_accounts_id := l_chart_of_accounts_id;
293
294 end if;
295
296 RETURN (TRUE);
297
298 RETURN NULL; EXCEPTION
299
300 WHEN OTHERS THEN
301 RETURN (FALSE);
302
303 END;
304
305 FUNCTION get_flexdata RETURN BOOLEAN IS
306
307 BEGIN
308
309 if C_CHART_OF_ACCOUNTS_ID is not null then
310
311 null;
312 return (TRUE);
313 else
314 /*SRW.MESSAGE('999','Cannot use flex API without a chart of accounts ID.');*/null;
315
316 return(FALSE);
317 end if;
318
319 RETURN NULL; EXCEPTION
320 WHEN OTHERS THEN
321 RETURN(FALSE);
322 END;
323
324 FUNCTION Get_Balancing_Segment RETURN BOOLEAN IS
325
326 l_dynamic_sql varchar2(200);
327
328
329 BEGIN
330
331 SELECT fnd.application_column_name
332 INTO c_balancing_segment
333 FROM fnd_segment_attribute_values fnd,
334 gl_sets_of_books gl
335 WHERE fnd.attribute_value = 'Y'
336 AND fnd.segment_attribute_type = 'GL_BALANCING'
337 AND fnd.id_flex_num = gl.chart_of_accounts_id
338 AND fnd.id_flex_code = 'GL#'
339 AND gl.set_of_books_id = p_set_of_books_id;
340
341 IF (p_debug_switch in ('y','Y')) THEN
342 /*SRW.MESSAGE('10.1','C_BALANCING_SEGMENT: '||c_balancing_segment);*/null;
343
344 END IF;
345
346
347
348
349 IF (c_balancing_segment LIKE 'SEGMENT1') THEN
350 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment1 = rel.balancing_segment_value';
351 ELSIF (c_balancing_segment LIKE 'SEGMENT2') THEN
352 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment2 = rel.balancing_segment_value';
353 ELSIF (c_balancing_segment LIKE 'SEGMENT3') THEN
354 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment3 = rel.balancing_segment_value';
355 ELSIF (c_balancing_segment LIKE 'SEGMENT4') THEN
356 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment4 = rel.balancing_segment_value';
357 ELSIF (c_balancing_segment LIKE 'SEGMENT5') THEN
358 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment5 = rel.balancing_segment_value';
359 ELSIF (c_balancing_segment LIKE 'SEGMENT6') THEN
360 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment6 = rel.balancing_segment_value';
361 ELSIF (c_balancing_segment LIKE 'SEGMENT7') THEN
362 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment7 = rel.balancing_segment_value';
363 ELSIF (c_balancing_segment LIKE 'SEGMENT8') THEN
364 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment8 = rel.balancing_segment_value';
365 ELSIF (c_balancing_segment LIKE 'SEGMENT9') THEN
366 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment9 = rel.balancing_segment_value';
367 ELSIF (c_balancing_segment LIKE 'SEGMENT10') THEN
368 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment10 = rel.balancing_segment_value';
369 ELSIF (c_balancing_segment LIKE 'SEGMENT11') THEN
370 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment11 = rel.balancing_segment_value';
371 ELSIF (c_balancing_segment LIKE 'SEGMENT12') THEN
372 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment12 = rel.balancing_segment_value';
373 ELSIF (c_balancing_segment LIKE 'SEGMENT13') THEN
374 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment13 = rel.balancing_segment_value';
375 ELSIF (c_balancing_segment LIKE 'SEGMENT14') THEN
376 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment14 = rel.balancing_segment_value';
377 ELSIF (c_balancing_segment LIKE 'SEGMENT15') THEN
378 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment15 = rel.balancing_segment_value';
379 ELSIF (c_balancing_segment LIKE 'SEGMENT16') THEN
380 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment16 = rel.balancing_segment_value';
381 ELSIF (c_balancing_segment LIKE 'SEGMENT17') THEN
382 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment17 = rel.balancing_segment_value';
383 ELSIF (c_balancing_segment LIKE 'SEGMENT18') THEN
384 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment18 = rel.balancing_segment_value';
385 ELSIF (c_balancing_segment LIKE 'SEGMENT19') THEN
386 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment19 = rel.balancing_segment_value';
387 ELSIF (c_balancing_segment LIKE 'SEGMENT20') THEN
388 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment20 = rel.balancing_segment_value';
389 ELSIF (c_balancing_segment LIKE 'SEGMENT21') THEN
390 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment21 = rel.balancing_segment_value';
391 ELSIF (c_balancing_segment LIKE 'SEGMENT22') THEN
392 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment22 = rel.balancing_segment_value';
393 ELSIF (c_balancing_segment LIKE 'SEGMENT23') THEN
394 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment23 = rel.balancing_segment_value';
395 ELSIF (c_balancing_segment LIKE 'SEGMENT24') THEN
396 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment24 = rel.balancing_segment_value';
397 ELSIF (c_balancing_segment LIKE 'SEGMENT25') THEN
398 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment25 = rel.balancing_segment_value';
399 ELSIF (c_balancing_segment LIKE 'SEGMENT26') THEN
400 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment26 = rel.balancing_segment_value';
401 ELSIF (c_balancing_segment LIKE 'SEGMENT27') THEN
402 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment27 = rel.balancing_segment_value';
403 ELSIF (c_balancing_segment LIKE 'SEGMENT28') THEN
404 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment28 = rel.balancing_segment_value';
405 ELSIF (c_balancing_segment LIKE 'SEGMENT29') THEN
406 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment29 = rel.balancing_segment_value';
407 ELSIF (c_balancing_segment LIKE 'SEGMENT30') THEN
408 l_dynamic_sql := l_dynamic_sql || ' AND gcc.segment30 = rel.balancing_segment_value';
409 END IF;
410
411
412
413
414
415
416
417 c_dynamic_sql := l_dynamic_sql;
418
419
420
421 IF (p_debug_switch in ('y','Y')) THEN
422 /*SRW.MESSAGE('10.3','C_DYNAMIC_SQL: '||c_dynamic_sql);*/null;
423
424 END IF;
425
426
427 RETURN (TRUE);
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 RETURN (FALSE);
432
433 --END get_balancing_segment() ;
434 END get_balancing_segment ;
435
436 FUNCTION Get_Entity_Name RETURN BOOLEAN IS
437
438 BEGIN
439
440 SELECT entity_name
441 INTO c_rep_entity_name
442 FROM ap_reporting_entities
443 WHERE tax_entity_id = p_rep_entity_id;
444
445 RETURN (TRUE);
446
447 RETURN NULL; EXCEPTION
448 WHEN OTHERS THEN
449 RETURN (FALSE);
450
451 --END get_entity_name() ;
452 END get_entity_name ;
453
454 FUNCTION GET_MISC_TOTAL RETURN BOOLEAN IS
455 BEGIN
456
457 IF (P_END_DATE IS NOT NULL) OR (P_START_DATE IS NOT NULL)
458 OR (P_END_DATE IS NOT NULL AND P_START_DATE IS NOT NULL) THEN
459
460 C_MISC_TOTAL := ' GROUP BY 1 HAVING '||
461 ' (sum(nvl(decode(type_1099,'||'''MISC1'''||',decode(I.invoice_amount,0
462 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
463 *IP.amount),0),0)) + '||
464 ' sum(nvl(decode(type_1099,'||'''MISC3'''||',decode(I.invoice_amount,0
465 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
466 *IP.amount),0),0)) + '||
467 ' sum(nvl(decode(type_1099,'||'''MISC6'''||',decode(I.invoice_amount,0
468 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
469 *IP.amount),0),0)) + '||
470 ' sum(nvl(decode(type_1099,'||'''MISC7'''||',decode(I.invoice_amount,0
471 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
472 *IP.amount),0),0)) + '||
473 ' sum(nvl(decode(type_1099,'||'''MISC9'''||',decode(I.invoice_amount,0
474 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
475 *IP.amount),0),0)) + '||
476 ' sum(nvl(decode(type_1099,'||'''MISC10'''||',decode(I.invoice_amount,0
477 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
478 *IP.amount),0),0)) >= 600 '||
479 ' OR sum(nvl(decode(type_1099,'||'''MISC2'''||',decode(I.invoice_amount,0
480 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
481 *IP.amount),0),0)) + '||
482 ' sum(nvl(decode(type_1099,'||'''MISC8'''||',decode(I.invoice_amount,0
483 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
484 *IP.amount),0),0)) >= 10 '||
485 ' OR sum(nvl(decode(type_1099,'||'''MISC5'''||',decode(I.invoice_amount,0
486 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
487 *IP.amount),0),0)) + '||
488 ' sum(nvl(decode(type_1099,'||'''MISC13 A'''||',decode(I.invoice_amount,0
489 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
490 *IP.amount),0),0)) + '||
491 ' sum(nvl(decode(type_1099,'||'''MISC13 EPP'''||',decode(I.invoice_amount,0
492 ,0,(ID.amount/decode(I.invoice_amount,0,1,I.invoice_amount))
493 *IP.amount),0),0)) > 0))';
494
495
496 END IF;
497
498
499 IF (p_debug_switch in ('y','Y')) THEN
500 /*SRW.MESSAGE('11.1','C_MISC_TOTAL: '||c_misc_total);*/null;
501
502 END IF;
503
504 RETURN (TRUE);
505
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 RETURN (FALSE);
510
511 --END get_misc_total() ;
512 END get_misc_total ;
513
514 FUNCTION GET_PAYMENT_EXISTS RETURN BOOLEAN IS
515 BEGIN
516
517
518
519
520
521
522 IF (P_END_DATE IS NOT NULL AND P_START_DATE IS NOT NULL) THEN
523
524 cp_payment_date := 'AND ip.accounting_date BETWEEN '''||to_char(p_start_date)
525 ||''''||' AND '''||to_char(p_end_date)||'''';
526
527
528 ELSIF (P_END_DATE IS NOT NULL AND P_START_DATE IS NULL) THEN
529
530 cp_payment_date := 'AND ip.accounting_date <= '''||to_char(p_end_date)||'''';
531
532
533 ELSIF (P_START_DATE IS NOT NULL AND P_END_DATE IS NULL) THEN
534
535 cp_payment_date := 'AND ip.accounting_date >= '''||to_char(p_start_date)||'''';
536
537
538 END IF;
539
540
541
542
543 RETURN (TRUE);
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 RETURN (FALSE);
548
549 --END get_payment_exists() ;
550 END get_payment_exists;
551
552
553 --Functions to refer Oracle report placeholders--
554
555 Function C_NLS_YES_p return varchar2 is
556 Begin
557 return C_NLS_YES;
558 END;
559 Function C_NLS_NO_p return varchar2 is
560 Begin
561 return C_NLS_NO;
562 END;
563 Function C_NLS_ALL_p return varchar2 is
564 Begin
565 return C_NLS_ALL;
566 END;
567 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
568 Begin
569 return C_NLS_NO_DATA_EXISTS;
570 END;
571 Function C_NLS_VOID_p return varchar2 is
572 Begin
573 return C_NLS_VOID;
574 END;
575 Function C_NLS_NA_p return varchar2 is
576 Begin
577 return C_NLS_NA;
578 END;
579 Function C_NLS_END_OF_REPORT_p return varchar2 is
580 Begin
581 return C_NLS_END_OF_REPORT;
582 END;
583 Function C_REPORT_START_DATE_p return date is
584 Begin
585 return C_REPORT_START_DATE;
586 END;
587 Function C_COMPANY_NAME_HEADER_p return varchar2 is
588 Begin
589 return C_COMPANY_NAME_HEADER;
590 END;
591 Function C_BASE_CURRENCY_CODE_p return varchar2 is
592 Begin
593 return C_BASE_CURRENCY_CODE;
594 END;
595 Function C_BASE_PRECISION_p return number is
596 Begin
597 return C_BASE_PRECISION;
598 END;
599 Function C_BASE_MIN_ACCT_UNIT_p return number is
600 Begin
601 return C_BASE_MIN_ACCT_UNIT;
602 END;
603 Function C_BASE_DESCRIPTION_p return varchar2 is
604 Begin
605 return C_BASE_DESCRIPTION;
606 END;
607 Function C_CHART_OF_ACCOUNTS_ID_p return number is
608 Begin
609 return C_CHART_OF_ACCOUNTS_ID;
610 END;
611 --Function Applications Template Report_p return varchar2 is
612 Function Applications_Template_Report_p return varchar2 is
613 Begin
614 --return Applications Template Report;
615 return Applications_Template_Report;
616 END;
617 Function C_BALANCING_SEGMENT_p return varchar2 is
618 Begin
619 return C_BALANCING_SEGMENT;
620 END;
621 Function C_REP_ENTITY_NAME_p return varchar2 is
622 Begin
623 return C_REP_ENTITY_NAME;
624 END;
625 Function C_REPORTABLE_ONLY_p return varchar2 is
626 Begin
627 return C_REPORTABLE_ONLY;
628 END;
629 Function C_DYNAMIC_SQL_p return varchar2 is
630 Begin
631 return C_DYNAMIC_SQL;
632 END;
633 Function C_MISC_TOTAL_p return varchar2 is
634 Begin
635 return C_MISC_TOTAL;
636 END;
637 Function CP_Payment_Date_p return varchar2 is
638 Begin
639 return CP_Payment_Date;
640 END;
641 END AP_APXT7VEE_XMLP_PKG ;
642