[Home] [Help]
PACKAGE BODY: APPS.AP_APXT7UTT_XMLP_PKG
Source
1 PACKAGE BODY AP_APXT7UTT_XMLP_PKG AS
2 /* $Header: APXT7UTTB.pls 120.0 2007/12/27 08:37:51 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 RETURN (TRUE);
45
46 RETURN NULL; EXCEPTION
47
48 WHEN OTHERS THEN
49 RETURN (FALSE);
50
51 END;
52
53 FUNCTION get_cover_page_values RETURN BOOLEAN IS
54
55 BEGIN
56
57 RETURN(TRUE);
58
59 RETURN NULL; EXCEPTION
60 WHEN OTHERS THEN
61 RETURN(FALSE);
62
63 END;
64
65 FUNCTION get_nls_strings RETURN BOOLEAN IS
66 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;
67 BEGIN
68
69 SELECT ly.meaning,
70 ln.meaning,
71 l1.displayed_field,
72 l2.displayed_field,
73 l3.displayed_field
74 INTO nls_yes,
75 nls_no,
76 nls_all,
77 nls_void,
78 nls_na
79 FROM fnd_lookups ly,
80 fnd_lookups ln,
81 ap_lookup_codes l1,
82 ap_lookup_codes l2,
83 ap_lookup_codes l3
84 WHERE ly.lookup_type = 'YES_NO'
85 AND ly.lookup_code = 'Y'
86 AND ln.lookup_type = 'YES_NO'
87 AND ln.lookup_code = 'N'
88 AND l1.lookup_type = 'NLS REPORT PARAMETER'
89 AND l1.lookup_code = 'ALL'
90 AND l2.lookup_type = 'NLS TRANSLATION'
91 AND l2.lookup_code = 'VOID'
92 AND l3.lookup_type = 'NLS REPORT PARAMETER'
93 AND l3.lookup_code = 'NA';
94
95 C_NLS_YES := nls_yes;
96 C_NLS_NO := nls_no;
97 C_NLS_ALL := nls_all;
98 C_NLS_VOID := nls_void;
99 C_NLS_NA := nls_na;
100
101
102
103 /*srw.user_exit('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
104
105 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_NLS_NO_DATA_EXISTS"');*/null;
106
107 /*srw.user_exit('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
108
109 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_NLS_END_OF_REPORT"');*/null;
110
111
112 C_NLS_NO_DATA_EXISTS := 'No Data Found';
113 C_NLS_END_OF_REPORT := 'End of Report';
114
115 RETURN (TRUE);
116
117 RETURN NULL; EXCEPTION
118 WHEN OTHERS THEN
119 RETURN (FALSE);
120 END;
121
122 function BeforeReport return boolean is
123 begin
124
125 LP_start_date := to_char(P_start_date, 'DD-MON-YY');
126 LP_end_date := to_char(P_end_date, 'DD-MON-YY');
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 IF (get_company_name() <> TRUE) THEN RAISE init_failure;
148 END IF;
149 IF (p_debug_switch in ('y','Y')) THEN
150 /*SRW.MESSAGE('2','After Get_Company_Name');*/null;
151
152 END IF;
153
154
155 IF (get_nls_strings() <> TRUE) THEN RAISE init_failure;
156 END IF;
157 IF (p_debug_switch in ('y','Y')) THEN
158 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
159
160 END IF;
161
162
163 IF (get_base_curr_data() <> TRUE) THEN RAISE init_failure;
164 END IF;
165 IF (p_debug_switch in ('y','Y')) THEN
166 /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
167
168 END IF;
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192 IF(custom_init() <> TRUE) THEN
193 RAISE init_failure;
194 END IF;
195 IF (p_debug_switch in ('y','Y')) THEN
196 /*SRW.MESSAGE('13','After Custom_Init');*/null;
197
198 END IF;
199
200
201
202
203 IF (p_debug_switch in ('y','Y')) THEN
204 /*SRW.BREAK;*/null;
205
206 END IF;
207
208
209
210 RETURN (TRUE);
211
212
213
214 EXCEPTION
215
216 WHEN OTHERS THEN
217
218 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
219
220
221 END;
222 return (TRUE);
223 end;
224
225 function afterreport(C_tot_vendor in number, C_tot_vendor_R in number) return boolean is
226 begin
227
228 DECLARE
229 closing_failure EXCEPTION;
230 BEGIN
231 IF C_tot_vendor = 0 AND C_tot_vendor_R = 0 THEN
232 /*SRW.MESSAGE('16','No information found - check parameters.');*/null;
233
234 ELSIF C_tot_vendor = 0 THEN
235 /*SRW.MESSAGE('17','No mis-matched distributions found');*/null;
236
237 ELSIF C_tot_vendor_R = 0 THEN
238 /*SRW.MESSAGE('18','No distributions found for 1099 Vendors');*/null;
239
240 ELSE
241 /*SRW.MESSAGE('19','*********** Report completed ***********');*/null;
242
243 END IF;
244 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
245
246 IF (P_DEBUG_SWITCH = 'Y') THEN
247 /*SRW.MESSAGE('20','After SRWEXIT');*/null;
248
249 END IF;
250 EXCEPTION
251 WHEN OTHERS THEN
252 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
253
254 END; return (TRUE);
255 end;
256
257 FUNCTION get_company_name RETURN BOOLEAN IS
258 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
259 l_name gl_sets_of_books.name%TYPE;
260 l_sob_id number;
261 l_curdate varchar2(15);
262 BEGIN
263
264 if P_SET_OF_BOOKS_ID is not null then
265 l_sob_id := p_set_of_books_id;
266 SELECT name, to_char(sysdate,'DD-MON-RR HH24:MI'),
267 chart_of_accounts_id
268 INTO l_name, l_curdate,
269 l_chart_of_accounts_id
270 FROM gl_sets_of_books
271 WHERE set_of_books_id = l_sob_id;
272
273 c_company_name := l_name;
274 c_chart_of_accounts_id := l_chart_of_accounts_id;
275 c_curdate := l_curdate;
276
277 end if;
278
279 RETURN (TRUE);
280
281 RETURN NULL; EXCEPTION
282
283 WHEN OTHERS THEN
284 RETURN (FALSE);
285
286 END;
287
288 FUNCTION get_flexdata RETURN BOOLEAN IS
289
290 BEGIN
291
292 if C_CHART_OF_ACCOUNTS_ID is not null then
293
294 null;
295 return (TRUE);
296 else
297 /*SRW.MESSAGE('999','Cannot use flex API without a chart of accounts ID.');*/null;
298
299 return(FALSE);
300 end if;
301
302 RETURN NULL; EXCEPTION
303 WHEN OTHERS THEN
304 RETURN(FALSE);
305 END;
306
307 function c_update_miscsformula(type_1099 in varchar2, vendor_id in number) return varchar2 is
308 begin
309
310 BEGIN
311 IF P_update_misc = 'UPDATE' THEN
312 UPDATE ap_invoice_distributions id
313 SET type_1099 = type_1099
314 WHERE nvl(type_1099,1) <> nvl(type_1099,1)
315 AND invoice_id in (SELECT i.invoice_id
316 FROM AP_Invoices i, AP_Invoice_Payments ip
317 WHERE i.vendor_id = vendor_id
318 AND i.invoice_id = ip.invoice_id
319 AND nvl(ip.accounting_date,sysdate) between
320 P_start_date and
321 P_end_date )
322 AND id.line_type_lookup_code <> 'AWT'; return ('Y');
323 ELSE
324 return ('N');
325 END IF;
326 EXCEPTION
327 WHEN OTHERS THEN
328 return ('N');
329 END;
330 RETURN NULL; end;
331
332 function c_update_regionsformula(region_R in varchar2, vendor_id_R in number, site_id_R in number) return varchar2 is
333 begin
334
335 DECLARE
336 l_region varchar2(25);
337 BEGIN
338 IF P_update_region = 'UPDATE' THEN
339 IF P_region_code='VENDOR SITE' THEN
340 l_region := region_R;
341 ELSE
342 l_region := P_region_code;
343 END IF;
344
345 IF P_region_code <> 'INCOME TAX REPORTING SITE' then
346 UPDATE ap_invoice_distributions id
347 SET income_tax_region = l_region
348 WHERE ( (P_region_code <>'VENDOR SITE'
349 AND NVL(id.income_tax_region, 'DuMmY') <> P_region_code)
350 OR (P_region_code = 'VENDOR SITE'
351 AND NVL(id.income_tax_region, 'DuMmY') <> l_region))
352 AND id.invoice_id in (SELECT i.invoice_id
353 FROM AP_Invoices i, AP_Invoice_Payments ip
354 WHERE i.vendor_id = vendor_id_R
355 AND i.vendor_site_id = site_id_R
356 AND i.invoice_id = ip.invoice_id
357 AND nvl(ip.accounting_date,sysdate) between
358 P_start_date and
359 P_end_date );
360 ELSE
361 UPDATE ap_invoice_distributions id
362 SET income_tax_region = (select pvs.state
363 from po_vendor_sites pvs
364 where pvs.tax_reporting_site_flag= 'Y'
365 and pvs.vendor_id=vendor_id_R)
366 WHERE id.invoice_id in (SELECT i.invoice_id
367 FROM AP_Invoices i, AP_Invoice_Payments ip
368 WHERE i.vendor_id = vendor_id_R
369 AND i.vendor_site_id = site_id_R
370 AND i.invoice_id = ip.invoice_id
371 AND nvl(ip.accounting_date,sysdate) between
372 P_start_date and P_end_date )
373 AND nvl(id.income_tax_region,'DuMmY') <>
374 (select pvs2.state from po_vendor_sites pvs2
375 where pvs2.tax_reporting_site_flag= 'Y'
376 and pvs2.vendor_id=vendor_id_R);
377
378
379
380
381 END IF;
382
383
384
385 END IF;
386 EXCEPTION
387 WHEN OTHERS THEN
388 return ('N');
389 END;
390 RETURN NULL; end;
391
392 --Functions to refer Oracle report placeholders--
393
394 Function C_NLS_YES_p return varchar2 is
395 Begin
396 return C_NLS_YES;
397 END;
398 Function C_NLS_NO_p return varchar2 is
399 Begin
400 return C_NLS_NO;
401 END;
402 Function C_NLS_ALL_p return varchar2 is
403 Begin
404 return C_NLS_ALL;
405 END;
406 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
407 Begin
408 return C_NLS_NO_DATA_EXISTS;
409 END;
410 Function C_NLS_VOID_p return varchar2 is
411 Begin
412 return C_NLS_VOID;
413 END;
414 Function C_NLS_NA_p return varchar2 is
415 Begin
416 return C_NLS_NA;
417 END;
418 Function C_NLS_END_OF_REPORT_p return varchar2 is
419 Begin
420 return C_NLS_END_OF_REPORT;
421 END;
422 Function C_REPORT_START_DATE_p return date is
423 Begin
424 return C_REPORT_START_DATE;
425 END;
426 Function C_BASE_CURRENCY_CODE_p return varchar2 is
427 Begin
428 return C_BASE_CURRENCY_CODE;
429 END;
430 Function C_BASE_PRECISION_p return number is
431 Begin
432 return C_BASE_PRECISION;
433 END;
434 Function C_BASE_MIN_ACCT_UNIT_p return number is
435 Begin
436 return C_BASE_MIN_ACCT_UNIT;
437 END;
438 Function C_BASE_DESCRIPTION_p return varchar2 is
439 Begin
440 return C_BASE_DESCRIPTION;
441 END;
442 Function C_CHART_OF_ACCOUNTS_ID_p return number is
443 Begin
444 return C_CHART_OF_ACCOUNTS_ID;
445 END;
446 --Commented By Raj
447 /*Function Applications Template Report_p return varchar2 is
448 Begin
449 return Applications Template Report;
450 END;*/
451 Function Applications_Template_Report_p return varchar2 is
452 Begin
453 return Applications_Template_Report;
454 END;
455 Function C_COMPANY_NAME_p return varchar2 is
456 Begin
457 return C_COMPANY_NAME;
458 END;
459 Function C_curdate_p return varchar2 is
460 Begin
461 return C_curdate;
462 END;
463 END AP_APXT7UTT_XMLP_PKG ;
464