[Home] [Help]
PACKAGE BODY: APPS.JA_JAINSTAC_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINSTAC_XMLP_PKG AS
2 /* $Header: JAINSTACB.pls 120.1 2007/12/25 16:29:38 dwkrishn noship $ */
3 FUNCTION BALANCE(DT1 IN DATE
4 ,DT2 IN DATE
5 ,VEND_ID IN NUMBER) RETURN NUMBER IS
6 V_AMOUNT NUMBER;
7 LV_PREPAYMENT AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
8 BEGIN
9 LV_PREPAYMENT := 'PREPAYMENT';
10 SELECT
11 SUM((API.INVOICE_AMOUNT - NVL(AMOUNT_PAID
12 ,0)) * NVL(EXCHANGE_RATE
13 ,1)) AMOUNT
14 INTO V_AMOUNT
15 FROM
16 AP_INVOICES_ALL API
17 WHERE API.VENDOR_ID = VEND_ID
18 AND TRUNC(API.INVOICE_DATE) BETWEEN TRUNC(DT1)
19 AND TRUNC(DT2)
20 AND API.PAYMENT_STATUS_FLAG IN ( 'N' , 'P' )
21 AND API.INVOICE_TYPE_LOOKUP_CODE <> LV_PREPAYMENT
22 AND ( API.ORG_ID = P_ORG_ID
23 OR API.ORG_ID IS NULL );
24 RETURN (NVL(V_AMOUNT
25 ,0));
26 END BALANCE;
27
28 FUNCTION FINAL_BALANCE(DT IN DATE
29 ,VEND_ID IN NUMBER) RETURN NUMBER IS
30 V_AMOUNT NUMBER;
31 BEGIN
32 SELECT
33 SUM((API.INVOICE_AMOUNT - NVL(AMOUNT_PAID
34 ,0)) * NVL(EXCHANGE_RATE
35 ,1)) AMOUNT
36 INTO V_AMOUNT
37 FROM
38 AP_INVOICES_ALL API
39 WHERE API.VENDOR_ID = VEND_ID
40 AND TRUNC(API.INVOICE_DATE) < TRUNC(DT)
41 AND API.PAYMENT_STATUS_FLAG IN ( 'N' , 'P' )
42 AND API.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
43 AND ( API.ORG_ID = P_ORG_ID
44 OR API.ORG_ID IS NULL );
45 RETURN (NVL(V_AMOUNT
46 ,0));
47 END FINAL_BALANCE;
48
49 FUNCTION CF_AGE2FORMULA(VENDOR_ID IN NUMBER) RETURN NUMBER IS
50 BEGIN
51 P_AGE1 := SYSDATE - P_AGING_INTERVAL_DAYS;
52 P_AGE1_AMOUNT := BALANCE(P_AGE1
53 ,SYSDATE
54 ,VENDOR_ID);
55 IF P_NO_OF_INTERVALS = 2 THEN
56 P_AGE13 := SYSDATE - P_AGING_INTERVAL_DAYS;
57 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
58 ,VENDOR_ID);
59 ELSIF P_NO_OF_INTERVALS = 3 THEN
60 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
61 P_AGE2_AMOUNT := BALANCE(P_AGE2
62 ,P_AGE1 - 1
63 ,VENDOR_ID);
64 P_AGE13 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
65 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
66 ,VENDOR_ID);
67 ELSIF P_NO_OF_INTERVALS = 4 THEN
68 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
69 P_AGE2_AMOUNT := BALANCE(P_AGE2
70 ,P_AGE1 - 1
71 ,VENDOR_ID);
72 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
73 P_AGE3_AMOUNT := BALANCE(P_AGE3
74 ,P_AGE2 - 1
75 ,VENDOR_ID);
76 P_AGE13 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
77 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
78 ,VENDOR_ID);
79 ELSIF P_NO_OF_INTERVALS = 5 THEN
80 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
81 P_AGE2_AMOUNT := BALANCE(P_AGE2
82 ,P_AGE1 - 1
83 ,VENDOR_ID);
84 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
85 P_AGE3_AMOUNT := BALANCE(P_AGE3
86 ,P_AGE2 - 1
87 ,VENDOR_ID);
88 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
89 P_AGE4_AMOUNT := BALANCE(P_AGE4
90 ,P_AGE3 - 1
91 ,VENDOR_ID);
92 P_AGE13 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
93 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13 - 1
94 ,VENDOR_ID);
95 ELSIF P_NO_OF_INTERVALS = 6 THEN
96 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
97 P_AGE2_AMOUNT := BALANCE(P_AGE2
98 ,P_AGE1 - 1
99 ,VENDOR_ID);
100 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
101 P_AGE3_AMOUNT := BALANCE(P_AGE3
102 ,P_AGE2 - 1
103 ,VENDOR_ID);
104 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
105 P_AGE4_AMOUNT := BALANCE(P_AGE4
106 ,P_AGE3 - 1
107 ,VENDOR_ID);
108 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
109 P_AGE5_AMOUNT := BALANCE(P_AGE5
110 ,P_AGE4 - 1
111 ,VENDOR_ID);
112 P_AGE13 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
113 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
114 ,VENDOR_ID);
115 ELSIF P_NO_OF_INTERVALS = 7 THEN
116 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
117 P_AGE2_AMOUNT := BALANCE(P_AGE2
118 ,P_AGE1 - 1
119 ,VENDOR_ID);
120 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
121 P_AGE3_AMOUNT := BALANCE(P_AGE3
122 ,P_AGE2 - 1
123 ,VENDOR_ID);
124 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
125 P_AGE4_AMOUNT := BALANCE(P_AGE4
126 ,P_AGE3 - 1
127 ,VENDOR_ID);
128 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
129 P_AGE5_AMOUNT := BALANCE(P_AGE5
130 ,P_AGE4 - 1
131 ,VENDOR_ID);
132 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
133 P_AGE6_AMOUNT := BALANCE(P_AGE6
134 ,P_AGE5 - 1
135 ,VENDOR_ID);
136 P_AGE13 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
137 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
138 ,VENDOR_ID);
139 ELSIF P_NO_OF_INTERVALS = 8 THEN
140 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
141 P_AGE2_AMOUNT := BALANCE(P_AGE2
142 ,P_AGE1 - 1
143 ,VENDOR_ID);
144 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
145 P_AGE3_AMOUNT := BALANCE(P_AGE3
146 ,P_AGE2 - 1
147 ,VENDOR_ID);
148 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
149 P_AGE4_AMOUNT := BALANCE(P_AGE4
150 ,P_AGE3 - 1
151 ,VENDOR_ID);
152 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
153 P_AGE5_AMOUNT := BALANCE(P_AGE5
154 ,P_AGE4 - 1
155 ,VENDOR_ID);
156 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
157 P_AGE6_AMOUNT := BALANCE(P_AGE6
158 ,P_AGE5 - 1
159 ,VENDOR_ID);
160 P_AGE7 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
161 P_AGE7_AMOUNT := BALANCE(P_AGE7
162 ,P_AGE6 - 1
163 ,VENDOR_ID);
164 P_AGE13 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
165 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
166 ,VENDOR_ID);
167 ELSIF P_NO_OF_INTERVALS = 9 THEN
168 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
169 P_AGE2_AMOUNT := BALANCE(P_AGE2
170 ,P_AGE1 - 1
171 ,VENDOR_ID);
172 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
173 P_AGE3_AMOUNT := BALANCE(P_AGE3
174 ,P_AGE2 - 1
175 ,VENDOR_ID);
176 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
177 P_AGE4_AMOUNT := BALANCE(P_AGE4
178 ,P_AGE3 - 1
179 ,VENDOR_ID);
180 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
181 P_AGE5_AMOUNT := BALANCE(P_AGE5
182 ,P_AGE4 - 1
183 ,VENDOR_ID);
184 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
185 P_AGE6_AMOUNT := BALANCE(P_AGE6
186 ,P_AGE5 - 1
187 ,VENDOR_ID);
188 P_AGE7 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
189 P_AGE7_AMOUNT := BALANCE(P_AGE7
190 ,P_AGE6 - 1
191 ,VENDOR_ID);
192 P_AGE8 := SYSDATE - 8 * P_AGING_INTERVAL_DAYS;
193 P_AGE8_AMOUNT := BALANCE(P_AGE8
194 ,P_AGE7 - 1
195 ,VENDOR_ID);
196 P_AGE13 := SYSDATE - 8 * P_AGING_INTERVAL_DAYS;
197 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
198 ,VENDOR_ID);
199 ELSIF P_NO_OF_INTERVALS = 10 THEN
200 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
201 P_AGE2_AMOUNT := BALANCE(P_AGE2
202 ,P_AGE1 - 1
203 ,VENDOR_ID);
204 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
205 P_AGE3_AMOUNT := BALANCE(P_AGE3
206 ,P_AGE2 - 1
207 ,VENDOR_ID);
208 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
209 P_AGE4_AMOUNT := BALANCE(P_AGE4
210 ,P_AGE3 - 1
211 ,VENDOR_ID);
212 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
213 P_AGE5_AMOUNT := BALANCE(P_AGE5
214 ,P_AGE4 - 1
215 ,VENDOR_ID);
216 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
217 P_AGE6_AMOUNT := BALANCE(P_AGE6
218 ,P_AGE5 - 1
219 ,VENDOR_ID);
220 P_AGE7 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
221 P_AGE7_AMOUNT := BALANCE(P_AGE7
222 ,P_AGE6 - 1
223 ,VENDOR_ID);
224 P_AGE8 := SYSDATE - 8 * P_AGING_INTERVAL_DAYS;
225 P_AGE8_AMOUNT := BALANCE(P_AGE8
226 ,P_AGE7 - 1
227 ,VENDOR_ID);
228 P_AGE9 := SYSDATE - 9 * P_AGING_INTERVAL_DAYS;
229 P_AGE9_AMOUNT := BALANCE(P_AGE9
230 ,P_AGE8 - 1
231 ,VENDOR_ID);
232 P_AGE13 := SYSDATE - 9 * P_AGING_INTERVAL_DAYS;
233 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13 - 1
234 ,VENDOR_ID);
235 ELSIF P_NO_OF_INTERVALS = 11 THEN
236 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
237 P_AGE2_AMOUNT := BALANCE(P_AGE2
238 ,P_AGE1 - 1
239 ,VENDOR_ID);
240 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
241 P_AGE3_AMOUNT := BALANCE(P_AGE3
242 ,P_AGE2 - 1
243 ,VENDOR_ID);
244 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
245 P_AGE4_AMOUNT := BALANCE(P_AGE4
246 ,P_AGE3 - 1
247 ,VENDOR_ID);
248 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
249 P_AGE5_AMOUNT := BALANCE(P_AGE5
250 ,P_AGE4 - 1
251 ,VENDOR_ID);
252 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
253 P_AGE6_AMOUNT := BALANCE(P_AGE6
254 ,P_AGE5 - 1
255 ,VENDOR_ID);
256 P_AGE7 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
257 P_AGE7_AMOUNT := BALANCE(P_AGE7
258 ,P_AGE6 - 1
259 ,VENDOR_ID);
260 P_AGE8 := SYSDATE - 8 * P_AGING_INTERVAL_DAYS;
261 P_AGE8_AMOUNT := BALANCE(P_AGE8
262 ,P_AGE7 - 1
263 ,VENDOR_ID);
264 P_AGE9 := SYSDATE - 9 * P_AGING_INTERVAL_DAYS;
265 P_AGE9_AMOUNT := BALANCE(P_AGE9
266 ,P_AGE8 - 1
267 ,VENDOR_ID);
268 P_AGE10 := SYSDATE - 10 * P_AGING_INTERVAL_DAYS;
269 P_AGE10_AMOUNT := BALANCE(P_AGE10
270 ,P_AGE9 - 1
271 ,VENDOR_ID);
272 P_AGE13 := SYSDATE - 10 * P_AGING_INTERVAL_DAYS;
273 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
274 ,VENDOR_ID);
275 ELSIF P_NO_OF_INTERVALS = 12 THEN
276 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
277 P_AGE2_AMOUNT := BALANCE(P_AGE2
278 ,P_AGE1 - 1
279 ,VENDOR_ID);
280 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
281 P_AGE3_AMOUNT := BALANCE(P_AGE3
282 ,P_AGE2 - 1
283 ,VENDOR_ID);
284 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
285 P_AGE4_AMOUNT := BALANCE(P_AGE4
286 ,P_AGE3 - 1
287 ,VENDOR_ID);
288 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
289 P_AGE5_AMOUNT := BALANCE(P_AGE5
290 ,P_AGE4 - 1
291 ,VENDOR_ID);
292 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
293 P_AGE6_AMOUNT := BALANCE(P_AGE6
294 ,P_AGE5 - 1
295 ,VENDOR_ID);
296 P_AGE7 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
297 P_AGE7_AMOUNT := BALANCE(P_AGE7
298 ,P_AGE6 - 1
299 ,VENDOR_ID);
300 P_AGE8 := SYSDATE - 8 * P_AGING_INTERVAL_DAYS;
301 P_AGE8_AMOUNT := BALANCE(P_AGE8
302 ,P_AGE7 - 1
303 ,VENDOR_ID);
304 P_AGE9 := SYSDATE - 9 * P_AGING_INTERVAL_DAYS;
305 P_AGE9_AMOUNT := BALANCE(P_AGE9
306 ,P_AGE8 - 1
307 ,VENDOR_ID);
308 P_AGE10 := SYSDATE - 10 * P_AGING_INTERVAL_DAYS;
309 P_AGE10_AMOUNT := BALANCE(P_AGE10
310 ,P_AGE9 - 1
311 ,VENDOR_ID);
312 P_AGE11 := SYSDATE - 11 * P_AGING_INTERVAL_DAYS;
313 P_AGE11_AMOUNT := BALANCE(P_AGE11
314 ,P_AGE10 - 1
315 ,VENDOR_ID);
316 P_AGE13 := SYSDATE - 11 * P_AGING_INTERVAL_DAYS;
317 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
318 ,VENDOR_ID);
319 ELSIF P_NO_OF_INTERVALS = 13 THEN
320 P_AGE2 := SYSDATE - 2 * P_AGING_INTERVAL_DAYS;
321 P_AGE2_AMOUNT := BALANCE(P_AGE2
322 ,P_AGE1 - 1
323 ,VENDOR_ID);
324 P_AGE3 := SYSDATE - 3 * P_AGING_INTERVAL_DAYS;
325 P_AGE3_AMOUNT := BALANCE(P_AGE3
326 ,P_AGE2 - 1
327 ,VENDOR_ID);
328 P_AGE4 := SYSDATE - 4 * P_AGING_INTERVAL_DAYS;
329 P_AGE4_AMOUNT := BALANCE(P_AGE4
330 ,P_AGE3 - 1
331 ,VENDOR_ID);
332 P_AGE5 := SYSDATE - 5 * P_AGING_INTERVAL_DAYS;
333 P_AGE5_AMOUNT := BALANCE(P_AGE5
334 ,P_AGE4 - 1
335 ,VENDOR_ID);
336 P_AGE6 := SYSDATE - 6 * P_AGING_INTERVAL_DAYS;
337 P_AGE6_AMOUNT := BALANCE(P_AGE6
338 ,P_AGE5 - 1
339 ,VENDOR_ID);
340 P_AGE7 := SYSDATE - 7 * P_AGING_INTERVAL_DAYS;
341 P_AGE7_AMOUNT := BALANCE(P_AGE7
342 ,P_AGE6 - 1
343 ,VENDOR_ID);
344 P_AGE8 := SYSDATE - 8 * P_AGING_INTERVAL_DAYS;
345 P_AGE8_AMOUNT := BALANCE(P_AGE8
346 ,P_AGE7 - 1
347 ,VENDOR_ID);
348 P_AGE9 := SYSDATE - 9 * P_AGING_INTERVAL_DAYS;
349 P_AGE9_AMOUNT := BALANCE(P_AGE9
350 ,P_AGE8 - 1
351 ,VENDOR_ID);
352 P_AGE10 := SYSDATE - 10 * P_AGING_INTERVAL_DAYS;
353 P_AGE10_AMOUNT := BALANCE(P_AGE10
354 ,P_AGE9 - 1
355 ,VENDOR_ID);
356 P_AGE11 := SYSDATE - 11 * P_AGING_INTERVAL_DAYS;
357 P_AGE11_AMOUNT := BALANCE(P_AGE11
358 ,P_AGE10 - 1
359 ,VENDOR_ID);
360 P_AGE12 := SYSDATE - 12 * P_AGING_INTERVAL_DAYS;
361 P_AGE12_AMOUNT := BALANCE(P_AGE12
362 ,P_AGE11 - 1
363 ,VENDOR_ID);
364 P_AGE13 := SYSDATE - 12 * P_AGING_INTERVAL_DAYS;
365 P_AGE13_AMOUNT := FINAL_BALANCE(P_AGE13
366 ,VENDOR_ID);
367 END IF;
368 RETURN (1);
369 END CF_AGE2FORMULA;
370
371 FUNCTION CF_COMP_NAMEFORMULA(ORG_ID IN NUMBER) RETURN VARCHAR2 IS
372 V_LEGAL_ENTITY NUMBER;
373 V_NAME VARCHAR2(60);
374 BEGIN
375 SELECT
376 NAME
377 INTO V_NAME
378 FROM
379 HR_ORGANIZATION_UNITS
380 WHERE NVL(ORGANIZATION_ID
381 ,0) = NVL(ORG_ID
382 ,0);
383 RETURN (V_NAME);
384 RETURN NULL;
385 EXCEPTION
386 WHEN OTHERS THEN
387 RETURN (NULL);
388 END CF_COMP_NAMEFORMULA;
389
390 FUNCTION CF_COMP_ADDRFORMULA(ORG_ID IN NUMBER) RETURN VARCHAR2 IS
391 V_LOCATION_ID NUMBER;
392 V_ADDRESS VARCHAR2(1000);
393 CURSOR FOR_LOCATION IS
394 SELECT
395 LOCATION_ID
396 FROM
397 HR_ORGANIZATION_UNITS
398 WHERE ORGANIZATION_ID = ORG_ID;
399 CURSOR FOR_ADDRESS IS
400 SELECT
401 ADDRESS_LINE_1 || ',' || ADDRESS_LINE_2 || ',' || ADDRESS_LINE_3 || ',' || TOWN_OR_CITY || ',' || COUNTRY || ',' || POSTAL_CODE || ',' || TELEPHONE_NUMBER_1 || ',' || TELEPHONE_NUMBER_2 || ',' || TELEPHONE_NUMBER_3
402 FROM
403 HR_LOCATIONS
404 WHERE LOCATION_ID = V_LOCATION_ID;
405 BEGIN
406 OPEN FOR_LOCATION;
407 FETCH FOR_LOCATION
408 INTO V_LOCATION_ID;
409 CLOSE FOR_LOCATION;
410 OPEN FOR_ADDRESS;
411 FETCH FOR_ADDRESS
412 INTO V_ADDRESS;
413 CLOSE FOR_ADDRESS;
414 RETURN (V_ADDRESS);
415 END CF_COMP_ADDRFORMULA;
416
417 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
418 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
419 SELECT
420 CONCURRENT_PROGRAM_ID,
421 NVL(ENABLE_TRACE
422 ,'N')
423 FROM
424 FND_CONCURRENT_REQUESTS
425 WHERE REQUEST_ID = P_REQUEST_ID;
426 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
427 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
428 BEGIN
429 /*SRW.MESSAGE(1275
430 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
431 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
432 CP_AS_ON_DATE := TO_CHAR(P_AS_ON_DATE,'DD-MON-YY');
433 CP_AS_ON_DATE1:= TO_CHAR(P_AS_ON_DATE,'DD-MON-YYYY');
434 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
435 BEGIN
436 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
437 FETCH C_PROGRAM_ID
438 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
439 CLOSE C_PROGRAM_ID;
440 /*SRW.MESSAGE(1275
441 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
442 IF V_ENABLE_TRACE = 'Y' THEN
443 EXECUTE IMMEDIATE
444 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
445 END IF;
446 EXCEPTION
447 WHEN OTHERS THEN
448 /*SRW.MESSAGE(1275
449 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
450 END;
451 IF P_AGING_INTERVAL_DAYS < 1 THEN
452 CP_AGING_INTERVAL_DAYS := 1;
453 END IF;
454 IF P_NO_OF_INTERVALS < 2 THEN
455 CP_NO_OF_INTERVALS := 2;
456 END IF;
457 IF P_NO_OF_INTERVALS > 13 THEN
458 CP_NO_OF_INTERVALS := 13;
459 END IF;
460 RETURN (TRUE);
461 END BEFOREREPORT;
462
463 FUNCTION CF_SOB_NAMEFORMULA RETURN VARCHAR2 IS
464 CURSOR FOR_SOB_ID(V_ORG_ID IN NUMBER) IS
465 SELECT
466 SET_OF_BOOKS_ID
467 FROM
468 ORG_ORGANIZATION_DEFINITIONS
469 WHERE NVL(OPERATING_UNIT
470 ,0) = NVL(V_ORG_ID
471 ,0);
472 CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
473 SELECT
474 NAME
475 FROM
476 GL_SETS_OF_BOOKS
477 WHERE SET_OF_BOOKS_ID = SOB_ID;
478 CURSOR FOR_INSTALL_INFO IS
479 SELECT
480 MULTI_ORG_FLAG
481 FROM
482 FND_PRODUCT_GROUPS;
483 V_SET_OF_BOOKS_ID NUMBER;
484 V_SOB_NAME VARCHAR2(100);
485 V_MULTI_ORG VARCHAR2(1);
486 V_ORG_ID NUMBER;
487 BEGIN
488 OPEN FOR_INSTALL_INFO;
489 FETCH FOR_INSTALL_INFO
490 INTO V_MULTI_ORG;
491 CLOSE FOR_INSTALL_INFO;
492 IF NVL(V_MULTI_ORG
493 ,'N') = 'N' THEN
494 V_ORG_ID := P_ORG_ID;
495 ELSE
496 V_ORG_ID := 0;
497 END IF;
498 OPEN FOR_SOB_ID(V_ORG_ID);
499 FETCH FOR_SOB_ID
500 INTO V_SET_OF_BOOKS_ID;
501 CLOSE FOR_SOB_ID;
502 OPEN FOR_SOB_NAME(V_SET_OF_BOOKS_ID);
503 FETCH FOR_SOB_NAME
504 INTO V_SOB_NAME;
505 CLOSE FOR_SOB_NAME;
506 RETURN (V_SOB_NAME);
507 END CF_SOB_NAMEFORMULA;
508
509 FUNCTION CF_P_VENDORFORMULA RETURN VARCHAR2 IS
510 CURSOR FOR_VENDOR_NAME(V_ID IN NUMBER) IS
511 SELECT
512 VENDOR_NAME
513 FROM
514 PO_VENDORS
515 WHERE VENDOR_ID = V_ID;
516 V_VENDOR_NAME VARCHAR(100);
517 BEGIN
518 OPEN FOR_VENDOR_NAME(P_VENDOR_ID);
519 FETCH FOR_VENDOR_NAME
520 INTO V_VENDOR_NAME;
521 CLOSE FOR_VENDOR_NAME;
522 RETURN (V_VENDOR_NAME);
523 END CF_P_VENDORFORMULA;
524
525 FUNCTION CF_INR_AMOUNT1FORMULA(INVOICE_TYPE_LOOKUP_CODE1 IN VARCHAR2
526 ,EXCHANGE_RATE1 IN NUMBER
527 ,INVOICE_ID1 IN NUMBER
528 ,INVOICE_AMOUNT1 IN NUMBER
529 ,AMOUNT_PAID1 IN NUMBER) RETURN NUMBER IS
530 AMOUNT NUMBER;
531 PREPAY_APPLIED_AMT NUMBER;
532 BEGIN
533 IF INVOICE_TYPE_LOOKUP_CODE1 = 'PREPAYMENT' THEN
534 SELECT
535 SUM(PREPAY_AMOUNT_REMAINING) * NVL(EXCHANGE_RATE1
536 ,1)
537 INTO AMOUNT
538 FROM
539 AP_INVOICE_DISTRIBUTIONS_ALL APD
540 GROUP BY
541 APD.INVOICE_ID
542 HAVING APD.INVOICE_ID = INVOICE_ID1;
543 ELSE
544 AMOUNT := (INVOICE_AMOUNT1 - NVL(AMOUNT_PAID1
545 ,0)) * NVL(EXCHANGE_RATE1
546 ,1);
547 END IF;
548 IF AMOUNT IS NULL THEN
549 AMOUNT := INVOICE_AMOUNT1 * NVL(EXCHANGE_RATE1
550 ,1);
551 END IF;
552 RETURN AMOUNT;
553 END CF_INR_AMOUNT1FORMULA;
554
555 FUNCTION CF_AMOUNT1FORMULA(INVOICE_TYPE_LOOKUP_CODE1 IN VARCHAR2
556 ,INVOICE_ID1 IN NUMBER
557 ,INVOICE_AMOUNT1 IN NUMBER
558 ,AMOUNT_PAID1 IN NUMBER) RETURN NUMBER IS
559 AMOUNT NUMBER;
560 PREPAY_APPLIED_AMT NUMBER;
561 BEGIN
562 IF INVOICE_TYPE_LOOKUP_CODE1 = 'PREPAYMENT' THEN
563 SELECT
564 SUM(PREPAY_AMOUNT_REMAINING)
565 INTO AMOUNT
566 FROM
567 AP_INVOICE_DISTRIBUTIONS_ALL APD
568 GROUP BY
569 APD.INVOICE_ID
570 HAVING APD.INVOICE_ID = INVOICE_ID1;
571 ELSE
572 AMOUNT := (INVOICE_AMOUNT1 - NVL(AMOUNT_PAID1
573 ,0));
574 END IF;
575 IF AMOUNT IS NULL THEN
576 AMOUNT := INVOICE_AMOUNT1;
577 END IF;
578 RETURN AMOUNT;
579 END CF_AMOUNT1FORMULA;
580
581 FUNCTION CF_REMARKSFORMULA(PAYMENT_STATUS_FLAG1 IN VARCHAR2) RETURN CHAR IS
582 REMARK VARCHAR2(20);
583 BEGIN
584 IF PAYMENT_STATUS_FLAG1 = 'N' THEN
585 REMARK := 'NOT PAID';
586 ELSIF PAYMENT_STATUS_FLAG1 = 'P' THEN
587 REMARK := 'PARTIALLY PAID';
588 ELSIF PAYMENT_STATUS_FLAG1 = 'Y' THEN
589 REMARK := 'FULLY PAID';
590 END IF;
591 RETURN REMARK;
592 END CF_REMARKSFORMULA;
593
594 FUNCTION AFTERREPORT RETURN BOOLEAN IS
595 BEGIN
596 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
597 RETURN (TRUE);
598 END AFTERREPORT;
599
600 END JA_JAINSTAC_XMLP_PKG;
601
602