eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AP_APXVCHCR_XMLP_PKG

Source


1 PACKAGE BODY AP_APXVCHCR_XMLP_PKG AS
2 /* $Header: APXVCHCRB.pls 120.1 2008/06/18 11:01:44 npannamp 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_all       ap_lookup_codes.displayed_field%TYPE;    nls_yes       fnd_lookups.meaning%TYPE;     nls_no        fnd_lookups.meaning%TYPE;     l_nls_display_supp_address   fnd_lookups.meaning%TYPE;
68    l_nls_zero_amt_option    fnd_lookups.meaning%TYPE;
69    nls_none_ep    ap_lookup_codes.displayed_field%TYPE;
70 
71 BEGIN
72 
73    nls_all     := '';
74    nls_yes     := '';
75    nls_no      := '';
76    nls_none_ep := '';
77 
78    SELECT  ly.meaning,
79            ln.meaning,
80            la.displayed_field,
81            lanep.displayed_field
82    INTO    nls_yes,  nls_no,  nls_all, nls_none_ep
83    FROM    fnd_lookups ly,  fnd_lookups ln,  ap_lookup_codes la,
84            ap_lookup_codes lanep
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   la.lookup_type = 'NLS REPORT PARAMETER'
90      AND   la.lookup_code = 'ALL'
91      AND   lanep.lookup_type = 'NLS TRANSLATION'
92      AND   lanep.lookup_code = 'NONE ELECTRONIC PAYMENT';
93 
94 
95    c_nls_yes := nls_yes;
96    c_nls_no  := nls_no;
97    c_nls_all := nls_all;
98    c_nls_none_ep := nls_none_ep;
99 
100 
101 
102    SELECT meaning
103    INTO   l_nls_display_supp_address
104    FROM   fnd_lookups
105    WHERE  lookup_type = 'YES_NO'
106    AND    lookup_code = P_ADDRS_OPTION;
107 
108    C_NLS_ADDR_OPTION := l_nls_display_supp_address;
109 
110 
111    SELECT meaning
112    INTO   l_nls_zero_amt_option
113    FROM   fnd_lookups
114    WHERE  lookup_type = 'YES_NO'
115    AND    lookup_code = P_INCLUDE_ZEROS;
116 
117    C_NLS_ZERO_AMT_OPTION := l_nls_zero_amt_option;
118 
119 
120    /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
121 
122    /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_no_data_exists"');*/null;
123 
124    /*c_nls_no_data_exists := '*** '||c_nls_no_data_exists||' ***';*/
125    c_nls_no_data_exists := 'No Data Found';
126 
127    /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
128 
129    /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_end_of_report"');*/null;
130 
131    /*c_nls_end_of_report := '*** '||c_nls_end_of_report||' ***';*/
132    c_nls_end_of_report := 'End of Report';
133 
134 RETURN (TRUE);
135 
136 RETURN NULL; EXCEPTION
137    WHEN OTHERS THEN
138       RETURN (FALSE);
139 END;
140 
141 function BeforeReport return boolean is
142 begin
143 
144 LP_START_DATE := to_char(P_START_DATE, 'DD-MON-YY');
145 LP_END_DATE := to_char(P_END_DATE, 'DD-MON-YY');
146 
147 DECLARE
148 
149   init_failure    EXCEPTION;
150 
151 BEGIN
152 
153 
154 
155 
156   C_FIRST_REC := 'Y';
157 
158   /*SRW.USER_EXIT('FND SRWINIT');*/null;
159 
160   IF (p_debug_switch = 'Y') THEN
161      /*SRW.MESSAGE('1','After SRWINIT');*/null;
162 
163   END IF;
164 
165 
166 
167 
168   IF (get_company_name() <> TRUE) THEN       RAISE init_failure;
169   END IF;
170   IF (p_debug_switch = 'Y') THEN
171      /*SRW.MESSAGE('2','After Get_Company_Name');*/null;
172 
173   END IF;
174 
175 
176   IF (get_nls_strings() <> TRUE) THEN      RAISE init_failure;
177   END IF;
178   IF (p_debug_switch = 'Y') THEN
179      /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
180 
181   END IF;
182 
183 
184   IF (get_base_curr_data() <> TRUE) THEN        RAISE init_failure;
185   END IF;
186   IF (p_debug_switch = 'Y') THEN
187      /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
188 
189   END IF;
190 
191 
192 
193 
194 
195 
196 
197 
198 
199 
200 
201 
202 
203 
204 
205 
206 
207 
208 
209 
210 
211 
212 
213 
214 
215 
216 
217 
218 
219 
220   IF (get_date_option() <> TRUE) THEN        RAISE init_failure;
221   END IF;
222 
223   if P_DATE_OPTION = 'Payment Date' then
224      P_WHERE1 := ('where cpd.payment_document_id(+) = chk.payment_document_id
225                    AND   chk.ce_bank_acct_use_id = cbau.bank_acct_use_id
226                    AND   cbau.bank_account_id  =  ba.bank_account_id
227                    AND   ba.bank_branch_id = bb.branch_party_id
228                    AND   chk.void_date is not null
229                    AND   decode('''||P_INCLUDE_ZEROS||''', ''N'', chk.amount,  1) <> 0
230                    AND   chk.check_date BETWEEN ''' ||TO_CHAR(P_START_DATE,'DD-MON-YYYY')||
231                                         ''' AND '''|| TO_CHAR(P_END_DATE,'DD-MON-YYYY')||'''
232                    AND   ft.territory_code(+) = chk.country
233                  ');
234   else
235     P_WHERE1 := ('where  cpd.payment_document_id(+) = chk.payment_document_id
236                   AND    chk.ce_bank_acct_use_id = cbau.bank_acct_use_id
237                   AND    cbau.bank_account_id = ba.bank_account_id
238                   AND    ba.bank_branch_id = bb.branch_party_id
239                   AND    chk.void_date is not null
240                   AND    decode('''||P_INCLUDE_ZEROS||''', ''N'', chk.amount,  1) <> 0
241                   AND    chk.void_date BETWEEN ''' ||TO_CHAR(P_START_DATE,'DD-MON-YYYY')||
242                                         ''' AND '''|| TO_CHAR(P_END_DATE,'DD-MON-YYYY')||'''
243                   AND    ft.territory_code(+) = chk.country
244                 ');
245   end if;
246 
247 
248 
249 
250   IF (p_debug_switch = 'Y') THEN
251      /*SRW.BREAK;*/null;
252 
253   END IF;
254 
255 
256 
257   RETURN (TRUE);
258 
259 
260 
261 EXCEPTION
262 
263   WHEN   OTHERS  THEN
264 
265     RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
266 
267 
268 END;  return (TRUE);
269 end;
270 
271 function AfterReport return boolean is
272 begin
273 
274 BEGIN
275    /*SRW.USER_EXIT('FND SRWEXIT');*/null;
276 
277    IF (P_DEBUG_SWITCH = 'Y') THEN
278       /*SRW.MESSAGE('20','After SRWEXIT');*/null;
279 
280    END IF;
281 EXCEPTION
282 WHEN OTHERS THEN
283    RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
284 
285 END;  return (TRUE);
286 end;
287 
288 FUNCTION  get_company_name    RETURN BOOLEAN IS
289   l_chart_of_accounts_id  gl_sets_of_books.chart_of_accounts_id%TYPE;
290   l_name                  gl_sets_of_books.name%TYPE;
291   l_sob_id                NUMBER;
292   l_report_start_date     DATE;
293 BEGIN
294   l_report_start_date := sysdate;   l_sob_id := p_set_of_books_id;
295   SELECT  name,
296           chart_of_accounts_id
297   INTO    l_name,
298           l_chart_of_accounts_id
299   FROM    gl_sets_of_books
300   WHERE   set_of_books_id = l_sob_id;
301 
302   c_company_name_header     := l_name;
303   c_chart_of_accounts_id    := l_chart_of_accounts_id;
304   c_report_start_date       := l_report_start_date;
305 
306   RETURN (TRUE);
307 
308 RETURN NULL; EXCEPTION
309 
310   WHEN   OTHERS  THEN
311     RETURN (FALSE);
312 
313 END;
314 
315 FUNCTION get_flexdata RETURN BOOLEAN IS
316 
317 BEGIN
318 
319 
320  null;
321    RETURN (TRUE);
322 
323 RETURN NULL; EXCEPTION
324    WHEN OTHERS THEN
325         RETURN(FALSE);
326 END;
327 
328 FUNCTION calculate_run_time RETURN BOOLEAN IS
329 end_date   DATE;
330 start_date DATE;
331 BEGIN
332 end_date   := sysdate;
333 start_date := C_REPORT_START_DATE;
334 C_REPORT_RUN_TIME := to_char(to_date('01/01/0001','DD/MM/YYYY') + ((end_date - start_date)),'HH24:MI:SS');
335 RETURN(TRUE);
336 RETURN NULL; EXCEPTION
337 WHEN OTHERS THEN
338 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
339 
340 END;
341 
342 function c_pay_curr_nameformula(C_PAY_CURR_CODE in varchar2) return varchar2 is
343 begin
344 
345 declare
346 l_pay_curr_name  FND_CURRENCIES_VL.name%TYPE;
347 begin
348 select c2.name
349 into   l_pay_curr_name
350 from   FND_CURRENCIES_VL c2
351 where  c2.currency_code = C_PAY_CURR_CODE;
352 
353 return(l_pay_curr_name);
354 end;
355 RETURN NULL; end;
356 
357 function c_bank_curr_nameformula(C_BANK_CURR_CODE in varchar2) return varchar2 is
358 begin
359 
360 declare
361   l_bank_curr_name  FND_CURRENCIES_VL.name%TYPE;
362 begin
363   select c1.name
364   into   l_bank_curr_name
365   from   FND_CURRENCIES_VL c1
366   where  c1.currency_code = C_BANK_CURR_CODE;
367 
368   if C_FIRST_REC = 'Y' then
369      C_OLD_BANK_CURR_CODE := C_BANK_CURR_CODE;
370      C_CURR_CODE_CHANGE_FLAG := 'N';
371      C_FIRST_REC := 'N';
372   else
373      if C_OLD_BANK_CURR_CODE <> C_BANK_CURR_CODE then
374         C_OLD_BANK_CURR_CODE := C_BANK_CURR_CODE;
375         C_CURR_CODE_CHANGE_FLAG := 'Y';
376      else
377         C_CURR_CODE_CHANGE_FLAG := 'N';
378      end if;
379   end if;
380 
381   return(l_bank_curr_name);
382 end;
383 
384 RETURN NULL; end;
385 
386 FUNCTION GET_DATE_OPTION RETURN BOOLEAN IS
387 l_displayed_field  ap_lookup_codes.displayed_field%TYPE;
388 
389 BEGIN
390 
391   SELECT  displayed_field
392   INTO    l_displayed_field
393   FROM    ap_lookup_codes
394   WHERE   lookup_type  = 'CHECK OR VOID'
395   AND     lookup_code = P_DATE_OPTION;
396 
397   c_date_option  := l_displayed_field;
398 
399  RETURN (TRUE);
400  EXCEPTION
401  WHEN   OTHERS  THEN
402     /*srw.message('100','An Error Occured in Get_Date_Option');*/null;
403 
404     /*srw.message('101',SQLCODE||SQLERRM);*/null;
405 
406     RETURN (FALSE);
407 
408 END;
409 
410 --Functions to refer Oracle report placeholders--
411 
412  Function C_BASE_CURRENCY_CODE_p return varchar2 is
413 	Begin
414 	 return C_BASE_CURRENCY_CODE;
415 	 END;
416  Function C_BASE_PRECISION_p return number is
417 	Begin
418 	 return C_BASE_PRECISION;
419 	 END;
420  Function C_BASE_MIN_ACCT_UNIT_p return number is
421 	Begin
422 	 return C_BASE_MIN_ACCT_UNIT;
423 	 END;
424  Function C_BASE_DESCRIPTION_p return varchar2 is
425 	Begin
426 	 return C_BASE_DESCRIPTION;
427 	 END;
428  Function C_COMPANY_NAME_HEADER_p return varchar2 is
429 	Begin
430 	 return C_COMPANY_NAME_HEADER;
431 	 END;
432  Function C_REPORT_START_DATE_p return date is
433 	Begin
434 	 return C_REPORT_START_DATE;
435 	 END;
436  Function C_NLS_YES_p return varchar2 is
437 	Begin
438 	 return C_NLS_YES;
439 	 END;
440  Function C_NLS_NO_p return varchar2 is
441 	Begin
442 	 return C_NLS_NO;
443 	 END;
444  Function C_NLS_ALL_p return varchar2 is
445 	Begin
446 	 return C_NLS_ALL;
447 	 END;
448  Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
449 	Begin
450 	 return C_NLS_NO_DATA_EXISTS;
451 	 END;
452  Function C_REPORT_RUN_TIME_p return varchar2 is
453 	Begin
454 	 return C_REPORT_RUN_TIME;
455 	 END;
456  Function C_CHART_OF_ACCOUNTS_ID_p return number is
457 	Begin
458 	 return C_CHART_OF_ACCOUNTS_ID;
459 	 END;
460  Function C_FIRST_REC_p return varchar2 is
461 	Begin
462 	 return C_FIRST_REC;
463 	 END;
464  Function C_OLD_BANK_CURR_CODE_p return varchar2 is
465 	Begin
466 	 return C_OLD_BANK_CURR_CODE;
467 	 END;
468  Function C_CURR_CODE_CHANGE_FLAG_p return varchar2 is
469 	Begin
470 	 return C_CURR_CODE_CHANGE_FLAG;
471 	 END;
472  Function C_NLS_END_OF_REPORT_p return varchar2 is
473 	Begin
474 	 return C_NLS_END_OF_REPORT;
475 	 END;
476  Function C_NLS_ADDR_OPTION_p return varchar2 is
477 	Begin
478 	 return C_NLS_ADDR_OPTION;
479 	 END;
480  Function C_NLS_ZERO_AMT_OPTION_p return varchar2 is
481 	Begin
482 	 return C_NLS_ZERO_AMT_OPTION;
483 	 END;
484  Function C_DATE_OPTION_p return varchar2 is
485 	Begin
486 	 return C_DATE_OPTION;
487 	 END;
488  Function C_nls_none_ep_p return varchar2 is
489 	Begin
490 	 return C_nls_none_ep;
491 	 END;
492 END AP_APXVCHCR_XMLP_PKG ;
493