[Home] [Help]
PACKAGE BODY: APPS.AP_APXINMHD_XMLP_PKG
Source
1 PACKAGE BODY AP_APXINMHD_XMLP_PKG AS
2 /* $Header: APXINMHDB.pls 120.0 2007/12/27 07:57:29 vjaganat noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 DECLARE
8
9 init_failure EXCEPTION;
10
11 BEGIN
12
13
14
15 /*SRW.USER_EXIT('FND SRWINIT');*/null;
16
17 IF (p_debug_switch = 'Y') THEN
18 /*SRW.MESSAGE('1','After SRWINIT');*/null;
19
20 END IF;
21
22
23 IF (get_company_name() <> TRUE) THEN RAISE init_failure;
24 END IF;
25 IF (p_debug_switch = 'Y') THEN
26 /*SRW.MESSAGE('2','After Get_Company_Name');*/null;
27
28 END IF;
29
30
31 IF (get_nls_strings() <> TRUE) THEN RAISE init_failure;
32 END IF;
33 IF (p_debug_switch = 'Y') THEN
34 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
35
36 END IF;
37
38
39 IF (get_base_curr_data() <> TRUE) THEN RAISE init_failure;
40 END IF;
41 IF (p_debug_switch = 'Y') THEN
42 /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
43
44 END IF;
45
46
47 IF (get_nls_released_held() <> TRUE) THEN RAISE init_failure;
48 END IF;
49 IF (p_debug_switch = 'Y') THEN
50 /*SRW.MESSAGE('5','After Get_nls_released_held');*/null;
51
52 END IF;
53
54
55 IF (get_flexdata() <> TRUE) THEN RAISE init_failure;
56 END IF;
57 IF (p_debug_switch = 'Y') THEN
58 /*SRW.MESSAGE('6','After get_flexdata() ');*/null;
59
60 END IF;
61
62
63 IF (custom_init() <> TRUE) THEN RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
64
65 END IF;
66 IF (p_debug_switch = 'Y') THEN
67 /*SRW.MESSAGE('7','After Custom_Init');*/null;
68
69 END IF;
70
71 IF P_MATCH_TYPE = 'HOLD' THEN
72 P_WHERE := (' WHERE V.vendor_id = decode('''||P_TEST_VENDOR_ID||''', NULL, V.vendor_id,
73 ''All'',V.vendor_id, '''||P_TEST_VENDOR_ID||''')
74 AND V.vendor_id = I.vendor_id
75 AND I.invoice_id = M.invoice_id
76 AND I.cancelled_date IS NULL
77 AND I.batch_id = B.batch_id (+)
78 AND EXISTS
79 (SELECT null FROM ap_holds M2
80 WHERE M2.invoice_id = I.invoice_id
81 AND M2.last_update_date between
82 NVL('''||to_char(P_START_ACTIVITY_DATE)||''',
83 M2.last_update_date) and
84 NVL('''||to_char(P_END_ACTIVITY_DATE)||''',
85 M2.last_update_date)
86 AND M2.line_location_id IS NOT NULL)
87 AND EXISTS
88 (SELECT null FROM ap_holds M2
89 WHERE M2.invoice_id = I.invoice_id
90 AND M2.release_lookup_code is null)
91 AND ((UPPER('''||P_HOLD_DETAIL_TYPE||''')=''ALL APPROVALS'') OR
92 ((UPPER('''||P_HOLD_DETAIL_TYPE||''')=''AUDIT REPORT'')
93 AND (M.STATUS_FLAG=''R'' OR M.STATUS_FLAG=''S'')))
94 ');
95
96 ELSE IF P_MATCH_TYPE = 'RELEASE' THEN
97 P_WHERE := (' WHERE V.vendor_id = decode('''||P_TEST_VENDOR_ID||''', null,
98 V.vendor_id,''All'',V.vendor_id,'''||P_TEST_VENDOR_ID||''')
99 AND V.vendor_id = I.vendor_id
100 AND I.invoice_id = M.invoice_id
101 AND I.batch_id = B.batch_id (+)
102 AND EXISTS
103 (SELECT null FROM ap_holds M2
104 WHERE M2.invoice_id = I.invoice_id
105 AND M2.last_update_date between
106 NVL('''||to_char(P_START_ACTIVITY_DATE)||''',
107 M2.last_update_date) and
108 NVL('''||to_char(P_END_ACTIVITY_DATE)||''',
109 M2.last_update_date)
110 AND M2.line_location_id IS NOT NULL)
111 AND NOT EXISTS
112 (SELECT null FROM ap_holds M2
113 WHERE M2.invoice_id = I.invoice_id
114 AND M2.release_lookup_code is null)
115 AND ((UPPER('''||P_HOLD_DETAIL_TYPE||''')=''ALL APPROVALS'') OR
116 ((UPPER('''||P_HOLD_DETAIL_TYPE||''')=''AUDIT REPORT'')
117 AND (M.STATUS_FLAG=''R'' OR M.STATUS_FLAG=''S'')))
118 ');
119
120 ELSE IF P_MATCH_TYPE = 'All' OR P_MATCH_TYPE = 'ALL' OR P_MATCH_TYPE IS NULL THEN
121 P_WHERE := (' WHERE V.vendor_id = decode('''||P_TEST_VENDOR_ID||''',null, V.vendor_id,
122 ''All'',V.vendor_id,'''||P_TEST_VENDOR_ID||''')
123 AND V.vendor_id = I.vendor_id
124 AND I.invoice_id = M.invoice_id
125 AND I.batch_id = B.batch_id (+)
126 AND EXISTS
127 (SELECT null FROM ap_holds M2
128 WHERE M2.invoice_id = I.invoice_id
129 AND M2.last_update_date between
130 NVL('''||to_char(P_START_ACTIVITY_DATE)||''',
131 M2.last_update_date) and
132 NVL('''||to_char(P_END_ACTIVITY_DATE)||''',
133 M2.last_update_date)
134 AND M2.line_location_id IS NOT NULL)
135 AND ((UPPER('''||P_HOLD_DETAIL_TYPE||''')=''ALL APPROVALS'') OR
136 ((UPPER('''||P_HOLD_DETAIL_TYPE||''')=''AUDIT REPORT'')) AND
137 (M.STATUS_FLAG=''R'' OR M.STATUS_FLAG=''S''))
138 ');
139 ELSE
140 P_WHERE := ' ';
141 END IF;
142 END IF;
143 END IF;
144
145 RETURN (TRUE);
146
147 EXCEPTION
148
149 WHEN OTHERS THEN
150
151 /*SRW.USER_EXIT('FND SRWEXIT');*/
152 null;
153
154 --RETURN (FALSE);
155
156 END; return (TRUE);
157 end;
158
159 function AfterReport return boolean is
160 begin
161
162 BEGIN
163 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
164
165 END; return (TRUE);
166 end;
167
168 FUNCTION custom_init RETURN BOOLEAN IS
169
170 BEGIN
171
172
173 RETURN (TRUE);
174
175 RETURN NULL; EXCEPTION
176
177 WHEN OTHERS THEN
178 RETURN (FALSE);
179
180 END;
181
182 FUNCTION get_base_curr_data RETURN BOOLEAN IS
183
184 l_base_curr VARCHAR2(15); l_prec NUMBER; l_min_au NUMBER; l_sob_id NUMBER;
185 BEGIN
186
187 SELECT p.base_currency_code,
188 c.precision,
189 c.minimum_accountable_unit
190 INTO l_base_curr,
191 l_prec,
192 l_min_au
193 FROM ap_system_parameters p,
194 fnd_currencies_vl c
195 WHERE p.base_currency_code = c.currency_code;
196
197 c_base_currency_code := l_base_curr;
198 c_base_precision := l_prec;
199 c_base_min_acct_unit := l_min_au;
200
201 RETURN (TRUE);
202
203 RETURN NULL; EXCEPTION
204
205 WHEN OTHERS THEN
206 RETURN (FALSE);
207
208 END;
209
210 FUNCTION get_company_name RETURN BOOLEAN IS
211
212 l_chart_of_accounts_id NUMBER;
213 l_name VARCHAR2(30); l_sob_id NUMBER;
214 l_report_start_date date;
215
216 BEGIN
217
218 l_sob_id := p_sob_id; l_report_start_date := sysdate;
219
220 SELECT substr(name,1,30),
221 chart_of_accounts_id
222 INTO l_name,
223 l_chart_of_accounts_id
224 FROM gl_sets_of_books
225 WHERE set_of_books_id = l_sob_id;
226
227 c_company_name_header := l_name;
228 c_chart_of_accounts_id := l_chart_of_accounts_id;
229 c_report_start_date := l_report_start_date;
230
231 RETURN (TRUE);
232
233 RETURN NULL; EXCEPTION
234
235 WHEN OTHERS THEN
236 RETURN (FALSE);
237
238 END;
239
240 FUNCTION get_nls_strings RETURN BOOLEAN IS
241 l_nls_yes fnd_lookups.meaning%TYPE;
242 l_nls_no fnd_lookups.meaning%TYPE;
243 l_nls_all ap_lookup_codes.displayed_field%TYPE;
244 l_nls_na ap_lookup_codes.displayed_field%TYPE;
245
246 BEGIN
247 SELECT ly.meaning,
248 ln.meaning,
249 la.displayed_field,
250 la1.displayed_field
251 INTO l_nls_yes, l_nls_no, l_nls_all ,l_nls_na
252 FROM fnd_lookups ly, fnd_lookups ln, ap_lookup_codes la,
253 ap_lookup_codes la1
254 WHERE ly.lookup_type = 'YES_NO'
255 AND ly.lookup_code = 'Y'
256 AND ln.lookup_type = 'YES_NO'
257 AND ln.lookup_code = 'N'
258 AND la.lookup_type = 'NLS REPORT PARAMETER'
259 AND la.lookup_code = 'ALL'
260
261 AND la1.lookup_type = 'NLS REPORT PARAMETER'
262 AND la1.lookup_code = 'NA';
263
264
265 c_nls_yes := l_nls_yes;
266 c_nls_no := l_nls_no;
267 c_nls_all := l_nls_all;
268 c_nls_na := l_nls_na;
269
270 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
271
272 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_no_data_exists"');*/null;
273
274 c_nls_no_data_exists := '*** '||c_nls_no_data_exists||' ***';
275
276 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
277
278 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_end_of_report"');*/null;
279
280 c_nls_end_of_report := '*** '||c_nls_end_of_report||' ***';
281
282
283 RETURN (TRUE);
284
285 RETURN NULL; EXCEPTION
286 WHEN OTHERS THEN
287 RETURN (FALSE);
288 END;
289
290 FUNCTION get_nls_released_held RETURN BOOLEAN IS
291 l_nls_released ap_lookup_codes.displayed_field%TYPE;
292 l_nls_held ap_lookup_codes.displayed_field%TYPE;
293
294 BEGIN
295
296 SELECT a1c1.displayed_field, a1c2.displayed_field
297 INTO l_nls_released, l_nls_held
298 FROM ap_lookup_codes a1c1, ap_lookup_codes a1c2
299 WHERE a1c1.lookup_type = 'INVOICE HOLD STATUS'
300 AND a1c1.lookup_code = 'RELEASED'
301 AND a1c2.lookup_type = 'INVOICE HOLD STATUS'
302 AND a1c2.lookup_code = 'HELD';
303
304
305
306 c_nls_released := l_nls_released;
307 c_nls_held := l_nls_held;
308
309 RETURN (TRUE);
310
311 RETURN NULL; EXCEPTION
312
313 WHEN OTHERS THEN
314 RETURN (FALSE);
315
316 END;
317
318 FUNCTION get_flexdata RETURN BOOLEAN IS
319
320 BEGIN
321
322
323 null;
324 RETURN (TRUE);
325
326 RETURN NULL; EXCEPTION
327 WHEN OTHERS THEN
328 RETURN(FALSE);
329 END;
330
331 --function c_accepted_fmtformula(C_MATCHING_BASIS in varchar2, C_ACCEPTED in varchar2) return varchar2 is
332 function c_accepted_fmtformula(C_MATCHING_BASIS in varchar2, C_ACCEPTED in varchar2) return varchar2 is
333 begin
334 /*SRW.REFERENCE(C_ACCEPTED);*/null;
335
336 /*SRW.REFERENCE(C_NLS_NA);*/null;
337
338
339 IF(C_MATCHING_BASIS ='QUANTITY') THEN
340
341 RETURN(C_ACCEPTED);
342
343 ELSIF (C_MATCHING_BASIS ='AMOUNT') THEN
344
345 RETURN(C_NLS_NA);
346
347 END IF;
348 end;
349
350 --Functions to refer Oracle report placeholders--
351
352 Function C_BASE_CURRENCY_CODE_p return varchar2 is
353 Begin
354 return C_BASE_CURRENCY_CODE;
355 END;
356 Function C_BASE_PRECISION_p return number is
357 Begin
358 return C_BASE_PRECISION;
359 END;
360 Function C_BASE_MIN_ACCT_UNIT_p return number is
361 Begin
362 return C_BASE_MIN_ACCT_UNIT;
363 END;
364 Function C_NLS_YES_p return varchar2 is
365 Begin
366 return C_NLS_YES;
367 END;
368 Function C_NLS_NO_p return varchar2 is
369 Begin
370 return C_NLS_NO;
371 END;
372 Function C_NLS_ALL_p return varchar2 is
373 Begin
374 return C_NLS_ALL;
375 END;
376 Function C_COMPANY_NAME_HEADER_p return varchar2 is
377 Begin
378 return C_COMPANY_NAME_HEADER;
379 END;
380 Function C_CHART_OF_ACCOUNTS_ID_p return number is
381 Begin
382 return C_CHART_OF_ACCOUNTS_ID;
383 END;
384 Function C_NLS_RELEASED_p return varchar2 is
385 Begin
386 return C_NLS_RELEASED;
387 END;
388 Function C_NLS_HELD_p return varchar2 is
389 Begin
390 return C_NLS_HELD;
391 END;
392 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
393 Begin
394 return C_NLS_NO_DATA_EXISTS;
395 END;
396 Function C_REPORT_START_DATE_p return date is
397 Begin
398 return C_REPORT_START_DATE;
399 END;
400 Function C_NLS_END_OF_REPORT_p return varchar2 is
401 Begin
402 return C_NLS_END_OF_REPORT;
403 END;
404 Function C_NLS_NA_p return varchar2 is
405 Begin
406 return C_NLS_NA;
407 END;
408 END AP_APXINMHD_XMLP_PKG ;
409