1 PACKAGE BODY JE_JEPLPSSR_XMLP_PKG AS
2 /* $Header: JEPLPSSRB.pls 120.2 2008/01/11 08:02:07 abraghun noship $ */
3 FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
4 BASE_CURR AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5 PREC FND_CURRENCIES_VL.PRECISION%TYPE;
6 MIN_AU FND_CURRENCIES_VL.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
7 DESCR FND_CURRENCIES_VL.DESCRIPTION%TYPE;
8 BEGIN
9 BASE_CURR := '';
10 PREC := 0;
11 MIN_AU := 0;
12 DESCR := '';
13 SELECT
14 P.BASE_CURRENCY_CODE,
15 C.PRECISION,
16 C.MINIMUM_ACCOUNTABLE_UNIT,
17 C.DESCRIPTION
18 INTO BASE_CURR,PREC,MIN_AU,DESCR
19 FROM
20 AP_SYSTEM_PARAMETERS P,
21 FND_CURRENCIES_VL C
22 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
23 C_BASE_CURRENCY_CODE := BASE_CURR;
24 C_BASE_PRECISION := PREC;
25 C_BASE_MIN_ACCT_UNIT := MIN_AU;
26 C_BASE_DESCRIPTION := DESCR;
27 RETURN (TRUE);
28 RETURN NULL;
29 EXCEPTION
30 WHEN OTHERS THEN
31 RETURN (FALSE);
32 END GET_BASE_CURR_DATA;
33
34 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
35 BEGIN
36 RETURN (TRUE);
37 RETURN NULL;
38 EXCEPTION
39 WHEN OTHERS THEN
40 RETURN (FALSE);
41 END GET_COVER_PAGE_VALUES;
42
43 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
44 NLS_VOID AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
45 NLS_NA AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
46 NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
47 NLS_YES FND_LOOKUPS.MEANING%TYPE;
48 NLS_NO FND_LOOKUPS.MEANING%TYPE;
49 BEGIN
50 SELECT
51 LY.MEANING,
52 LN.MEANING,
53 L1.DISPLAYED_FIELD,
54 L2.DISPLAYED_FIELD,
55 L3.DISPLAYED_FIELD
56 INTO NLS_YES,NLS_NO,NLS_ALL,NLS_VOID,NLS_NA
57 FROM
58 FND_LOOKUPS LY,
59 FND_LOOKUPS LN,
60 AP_LOOKUP_CODES L1,
61 AP_LOOKUP_CODES L2,
62 AP_LOOKUP_CODES L3
63 WHERE LY.LOOKUP_TYPE = 'YES_NO'
64 AND LY.LOOKUP_CODE = 'Y'
65 AND LN.LOOKUP_TYPE = 'YES_NO'
66 AND LN.LOOKUP_CODE = 'N'
67 AND L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
68 AND L1.LOOKUP_CODE = 'ALL'
69 AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
70 AND L2.LOOKUP_CODE = 'VOID'
71 AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
72 AND L3.LOOKUP_CODE = 'NA';
73 C_NLS_YES := NLS_YES;
74 C_NLS_NO := NLS_NO;
75 C_NLS_ALL := NLS_ALL;
76 C_NLS_VOID := NLS_VOID;
77 C_NLS_NA := NLS_NA;
78 FND_MESSAGE.SET_NAME('JE'
79 ,'JE_ALL_DATA_NOT_FOUND');
80 C_NLS_NO_DATA_EXISTS := SUBSTR(FND_MESSAGE.GET
81 ,1
82 ,40);
83 C_NLS_NO_DATA_EXISTS := C_NLS_NO_DATA_EXISTS;
84 FND_MESSAGE.SET_NAME('JE'
85 ,'JE_ALL_END_OF_REPORT');
86 C_NLS_END_OF_REPORT := SUBSTR(FND_MESSAGE.GET
87 ,1
88 ,40);
89 C_NLS_END_OF_REPORT := C_NLS_END_OF_REPORT;
90 RETURN (TRUE);
91 RETURN NULL;
92 EXCEPTION
93 WHEN OTHERS THEN
94 RETURN (FALSE);
95 END GET_NLS_STRINGS;
96
97 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
98 BEGIN
99 DECLARE
100 INIT_FAILURE EXCEPTION;
101 BEGIN
102 C_REPORT_START_DATE := SYSDATE;
103 CP_REPORT_CURRENCY := P_REPORT_CURRENCY;
104 CP_SUPPLIER_NAME_FROM := P_SUPPLIER_NAME_FROM;
105 CP_SUPPLIER_NAME_TO := P_SUPPLIER_NAME_TO;
106 CP_POSTED_FLAG := P_POSTED;
107 CP_APPROVED_FLAG := P_APPROVED;
108 CP_PARTIAL_LEDGER_MSG := P_PARTIAL_LEDGER_MSG;
109 BEGIN
110 SELECT
111 USER_CONCURRENT_PROGRAM_NAME
112 INTO CP_TITLE
113 FROM
114 FND_CONCURRENT_REQUESTS R,
115 FND_CONCURRENT_PROGRAMS_VL P
116 WHERE R.REQUEST_ID = P_CONC_REQUEST_ID
117 AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
118 AND R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 CP_TITLE := 'Polish Supplier Statement';
122 END;
123 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
124 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
125 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
126 /*SRW.MESSAGE('1'
127 ,'After SRWINIT')*/NULL;
128 END IF;
129 IF (P_TRACE_SWITCH in ('y','Y')) THEN
130 EXECUTE IMMEDIATE
131 'alter session set sql_trace TRUE';
132 END IF;
133 SELECT
134 MEANING
135 INTO P_REPORT_CURRENCY_DISP
136 FROM
137 FND_LOOKUPS
138 WHERE LOOKUP_TYPE = 'JGZZ_CURRENCY_TYPE'
139 AND LOOKUP_CODE = P_REPORT_CURRENCY;
140 CP_REPORT_CURR_DISP := P_REPORT_CURRENCY_DISP;
141 IF (GET_NLS_STRINGS <> TRUE) THEN
142 RAISE INIT_FAILURE;
143 END IF;
144 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
145 /*SRW.MESSAGE('3'
146 ,'After Get_NLS_Strings')*/NULL;
147 END IF;
148 IF (GET_BASE_CURR_DATA <> TRUE) THEN
149 RAISE INIT_FAILURE;
150 END IF;
151 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
152 /*SRW.MESSAGE('4'
153 ,'After Get_Base_Curr_Data')*/NULL;
154 END IF;
155 IF (CUSTOM_INIT() <> TRUE) THEN
156 RAISE INIT_FAILURE;
157 END IF;
158 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
159 /*SRW.MESSAGE('7'
160 ,'After Custom_Init')*/NULL;
161 END IF;
162 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
163 /*SRW.BREAK*/NULL;
164 END IF;
165 IF P_DEBUG_SWITCH = 'Y' THEN
166 /*SRW.MESSAGE(105
167 ,'PWHERE: ' || P_WHERE)*/NULL;
168 END IF;
169 P_WHERE := ' ';
170 IF P_APPROVED = 'Y' THEN
171 P_WHERE := ' AND aid.match_status_flag IN (' || '''' || 'A' || '''' || ',' || '''' || 'T' || '''' || ')';
172 END IF;
173 IF P_DEBUG_SWITCH = 'Y' THEN
174 /*SRW.MESSAGE(105
175 ,'PWHERE: ' || P_WHERE)*/NULL;
176 END IF;
177 P_WHERE_CAT := ' ';
178 IF P_DOCUMENT_CATEGORY IS NOT NULL THEN
179 P_WHERE_CAT := ' AND ltrim(rtrim(ai.doc_category_code)) = ' || '''' || P_DOCUMENT_CATEGORY || '''';
180 CP_DOCUMENT_CATEGORY := P_DOCUMENT_CATEGORY;
181 END IF;
182 BEGIN
183 SELECT
184 GLP.NAME
185 INTO P_LEDGER_NAME
186 FROM
187 GL_LEDGERS_PUBLIC_V GLP
188 WHERE GLP.LEDGER_ID = P_LEDGER_ID;
189 CP_LEDGER_NAME := P_LEDGER_NAME;
190 EXCEPTION
191 WHEN OTHERS THEN
192 /*SRW.MESSAGE(002
193 ,'Get Ledger Name has failed')*/NULL;
194 /*SRW.MESSAGE(003
195 ,SQLERRM)*/NULL;
196 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
197 END;
198 LP_START_DATE:=P_START_DATE;
199 LP_END_DATE:=P_END_DATE;
200 RETURN (TRUE);
201 EXCEPTION
202 WHEN OTHERS THEN
203 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
204 END;
205
206 RETURN (TRUE);
207 END BEFOREREPORT;
208
209 FUNCTION AFTERREPORT RETURN BOOLEAN IS
210 BEGIN
211 BEGIN
212 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
213 IF (P_DEBUG_SWITCH = 'Y') THEN
214 /*SRW.MESSAGE('20'
215 ,'After SRWEXIT')*/NULL;
216 END IF;
217 EXCEPTION
218 WHEN OTHERS THEN
219 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
220 END;
221 RETURN (TRUE);
222 END AFTERREPORT;
223
224 FUNCTION CF_REF_TRANS_DATEFORMULA(TRANS_TYPE IN VARCHAR2
225 ,TRANS_ID IN NUMBER) RETURN DATE IS
226 V_INVOICE_ID AP_INVOICES.INVOICE_ID%TYPE;
227 V_INVOICE_DATE AP_INVOICES.INVOICE_NUM%TYPE;
228 V_INVOICE_NUM AP_INVOICES.INVOICE_NUM%TYPE;
229 V_CHECK_ID AP_CHECKS.CHECK_ID%TYPE;
230 V_CHECK_DATE AP_CHECKS.CHECK_DATE%TYPE;
231 V_CHECK_NUMBER AP_CHECKS.CHECK_NUMBER%TYPE;
232 V_TRANS_TYPE VARCHAR2(25);
233 CURSOR C_INV_REL_AWT IS
234 SELECT
235 INVOICE_DATE,
236 INVOICE_NUM
237 FROM
238 AP_INVOICES
239 WHERE INVOICE_ID = V_INVOICE_ID;
240 CURSOR C_PMT_REL_AWT IS
241 SELECT
242 CHECK_DATE,
243 CHECK_NUMBER
244 FROM
245 AP_CHECKS
246 WHERE CHECK_ID = V_CHECK_ID;
247 BEGIN
248 IF TRANS_TYPE = 'AWT' THEN
249 IF CP_CREATE_AWT_DISTS_TYPE = 'PAYMENT' THEN
250 V_CHECK_ID := TRANS_ID;
251 OPEN C_PMT_REL_AWT;
252 FETCH C_PMT_REL_AWT
253 INTO V_CHECK_DATE,V_CHECK_NUMBER;
254 CLOSE C_PMT_REL_AWT;
255 CP_REF_TRANS_NUM := V_CHECK_NUMBER;
256 BEGIN
257 SELECT
258 L.MEANING
259 INTO V_TRANS_TYPE
260 FROM
261 FND_LOOKUPS L
262 WHERE L.LOOKUP_CODE = 'PMT'
263 AND L.LOOKUP_TYPE = 'JLZZ_SUPPLIER_TRX_TYPE'
264 AND NVL(L.START_DATE_ACTIVE
265 ,SYSDATE) <= sysdate
266 AND NVL(L.END_DATE_ACTIVE
267 ,SYSDATE) >= sysdate
268 AND L.ENABLED_FLAG = 'Y';
269 EXCEPTION
270 WHEN OTHERS THEN
271 CP_REF_TRANS_TYPE := NULL;
272 END;
273 CP_REF_TRANS_TYPE := V_TRANS_TYPE;
274 RETURN (V_CHECK_DATE);
275 ELSIF CP_CREATE_AWT_DISTS_TYPE = 'APPROVAL' THEN
276 V_INVOICE_ID := TRANS_ID;
277 OPEN C_INV_REL_AWT;
278 FETCH C_INV_REL_AWT
279 INTO V_INVOICE_DATE,V_INVOICE_NUM;
280 CLOSE C_INV_REL_AWT;
281 CP_REF_TRANS_NUM := V_INVOICE_NUM;
282 BEGIN
283 SELECT
284 L.MEANING
285 INTO V_TRANS_TYPE
286 FROM
287 FND_LOOKUPS L
288 WHERE L.LOOKUP_CODE = 'INV'
289 AND L.LOOKUP_TYPE = 'JLZZ_SUPPLIER_TRX_TYPE'
290 AND NVL(L.START_DATE_ACTIVE
291 ,SYSDATE) <= sysdate
292 AND NVL(L.END_DATE_ACTIVE
293 ,SYSDATE) >= sysdate
294 AND L.ENABLED_FLAG = 'Y';
295 EXCEPTION
296 WHEN OTHERS THEN
297 CP_REF_TRANS_TYPE := NULL;
298 END;
299 CP_REF_TRANS_TYPE := V_TRANS_TYPE;
300 RETURN (V_INVOICE_DATE);
301 ELSE
302 CP_REF_TRANS_NUM := NULL;
303 CP_REF_TRANS_TYPE := NULL;
304 RETURN (NULL);
305 END IF;
306 ELSE
307 CP_REF_TRANS_NUM := NULL;
308 CP_REF_TRANS_TYPE := NULL;
309 RETURN (NULL);
310 END IF;
311 RETURN NULL;
312 END CF_REF_TRANS_DATEFORMULA;
313
314 FUNCTION CF_AMOUNT_CURRENCYFORMULA(TRANS_AMT IN NUMBER
315 ,TRANS_BASE_AMT IN NUMBER) RETURN NUMBER IS
316 BEGIN
317 IF P_REPORT_CURRENCY = 'T' THEN
318 RETURN (TRANS_AMT);
319 ELSE
320 RETURN (ROUND(TRANS_BASE_AMT
321 ,C_BASE_PRECISION));
322 END IF;
323 RETURN NULL;
324 END CF_AMOUNT_CURRENCYFORMULA;
325
326 FUNCTION CF_AMOUNT_DUE_CURRENCYFORMULA(TRANS_TYPE IN VARCHAR2
327 ,TRANS_ID IN NUMBER
328 ,TRANS_AMT IN NUMBER
329 ,TRANS_BASE_AMT IN NUMBER
330 ,SECTION IN VARCHAR2
331 ,CF_TRX_PRECISION IN NUMBER) RETURN NUMBER IS
332 V_REMAINING_AMOUNT NUMBER;
333 V_AMOUNT_PAID NUMBER;
334 V_BASE_AMOUNT_PAID NUMBER;
335 V_PMT_AMOUNT NUMBER;
336 V_PMT_BASE_AMOUNT NUMBER;
337 V_INVOICE_ID AP_INVOICES.INVOICE_ID%TYPE;
338 V_RATE NUMBER;
339 V_AMOUNT NUMBER;
340 V_PAYMENT_STATUS_FLAG VARCHAR2(1);
341 V_DISC_AMT NUMBER;
342 V_EXCH_RATE NUMBER;
343 CURSOR C_PRPMT IS
344 SELECT
345 SUM(NVL(AID.PREPAY_AMOUNT_REMAINING
346 ,AID.AMOUNT)) * - 1
347 FROM
348 AP_INVOICE_DISTRIBUTIONS AID
349 WHERE AID.INVOICE_ID = V_INVOICE_ID
350 AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM'
351 AND AID.POSTED_FLAG = 'Y';
352 CURSOR C_INVOICE IS
353 SELECT
354 NVL(SUM(AID.AMOUNT)
355 ,0) * - 1,
356 NVL(SUM(NVL(AID.BASE_AMOUNT
357 ,AID.AMOUNT))
358 ,0) * - 1
359 FROM
360 AP_INVOICE_DISTRIBUTIONS AID
361 WHERE AID.INVOICE_ID = V_INVOICE_ID
362 AND ( AID.LINE_TYPE_LOOKUP_CODE = 'AWT'
363 OR AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' )
364 AND AID.POSTED_FLAG = 'Y';
365 CURSOR C_PMT IS
366 SELECT
367 NVL(SUM(IP.AMOUNT)
368 ,0),
369 NVL(SUM(NVL(IP.INVOICE_BASE_AMOUNT
370 ,IP.AMOUNT))
371 ,0)
372 FROM
373 AP_INVOICE_PAYMENTS IP
374 WHERE IP.INVOICE_ID = V_INVOICE_ID
375 AND IP.POSTED_FLAG = 'Y';
376 CURSOR C_DISC_AMT IS
377 SELECT
378 NVL(DISCOUNT_AMOUNT_TAKEN
379 ,0),
380 NVL(EXCHANGE_RATE
381 ,1)
382 FROM
383 AP_INVOICES AI
384 WHERE AI.INVOICE_ID = V_INVOICE_ID;
385 BEGIN
386 IF TRANS_TYPE = 'INV' THEN
387 V_INVOICE_ID := TRANS_ID;
388 OPEN C_INVOICE;
389 FETCH C_INVOICE
390 INTO V_AMOUNT_PAID,V_BASE_AMOUNT_PAID;
391 IF C_INVOICE%NOTFOUND THEN
392 V_AMOUNT_PAID := 0;
393 V_BASE_AMOUNT_PAID := 0;
394 END IF;
395 CLOSE C_INVOICE;
396 OPEN C_PMT;
397 FETCH C_PMT
398 INTO V_PMT_AMOUNT,V_PMT_BASE_AMOUNT;
399 IF C_PMT%NOTFOUND THEN
400 V_PMT_AMOUNT := 0;
401 V_PMT_BASE_AMOUNT := 0;
402 END IF;
403 CLOSE C_PMT;
404 OPEN C_DISC_AMT;
405 FETCH C_DISC_AMT
406 INTO V_DISC_AMT,V_EXCH_RATE;
407 CLOSE C_DISC_AMT;
408 IF P_REPORT_CURRENCY = 'T' THEN
409 RETURN (TRANS_AMT - (V_AMOUNT_PAID + V_PMT_AMOUNT) - V_DISC_AMT);
410 ELSE
411 V_AMOUNT := TRANS_BASE_AMT - (V_BASE_AMOUNT_PAID + V_PMT_BASE_AMOUNT) - (V_DISC_AMT * V_EXCH_RATE);
412 RETURN (V_AMOUNT);
413 END IF;
414 ELSIF TRANS_TYPE = 'PRPMT' THEN
415 IF SECTION = 'APPLIED' THEN
416 RETURN (NULL);
417 ELSE
418 V_INVOICE_ID := TRANS_ID;
419 OPEN C_PRPMT;
420 FETCH C_PRPMT
421 INTO V_REMAINING_AMOUNT;
422 IF C_PRPMT%NOTFOUND THEN
423 V_REMAINING_AMOUNT := 0;
424 END IF;
425 CLOSE C_PRPMT;
426 IF P_REPORT_CURRENCY = 'T' THEN
427 RETURN (V_REMAINING_AMOUNT);
428 ELSE
429 V_RATE := NVL(TRANS_BASE_AMT
430 ,0) / NVL(TRANS_AMT
431 ,999999);
432 V_AMOUNT := ROUND(V_REMAINING_AMOUNT * V_RATE
433 ,CF_TRX_PRECISION);
434 RETURN (V_AMOUNT);
435 END IF;
436 END IF;
437 ELSE
438 RETURN (NULL);
439 END IF;
440 RETURN NULL;
441 END CF_AMOUNT_DUE_CURRENCYFORMULA;
442
443 FUNCTION CF_SUPP_BBFORMULA(VENDOR_ID IN NUMBER
444 ,GROUP_CURRENCY IN VARCHAR2
445 ,CF_GROUP_PRECISION IN NUMBER) RETURN NUMBER IS
446 L_BALANCE NUMBER;
447 L_SOB NUMBER;
448 BEGIN
449 SELECT
450 SET_OF_BOOKS_ID
451 INTO L_SOB
452 FROM
453 AP_SYSTEM_PARAMETERS;
454 IF P_REPORT_CURRENCY = 'T' THEN
455 SELECT
456 NVL(SUM(AEL.ENTERED_CR)
457 ,0) - NVL(SUM(AEL.ENTERED_DR)
458 ,0)
459 INTO L_BALANCE
460 FROM
461 AP_AE_LINES_ALL AEL,
462 AP_AE_HEADERS_ALL AEH
463 WHERE AEL.AE_LINE_TYPE_CODE = 'LIABILITY'
464 AND AEH.SET_OF_BOOKS_ID = L_SOB
465 AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
466 AND AEH.ACCOUNTING_DATE < P_START_DATE
467 AND AEL.THIRD_PARTY_ID = VENDOR_ID
468 AND AEL.CURRENCY_CODE = GROUP_CURRENCY;
469 ELSE
470 SELECT
471 NVL(SUM(AEL.ACCOUNTED_CR)
472 ,0) - NVL(SUM(AEL.ACCOUNTED_DR)
473 ,0)
474 INTO L_BALANCE
475 FROM
476 AP_AE_LINES_ALL AEL,
477 AP_AE_HEADERS_ALL AEH
478 WHERE AEL.AE_LINE_TYPE_CODE = 'LIABILITY'
479 AND AEH.SET_OF_BOOKS_ID = L_SOB
480 AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
481 AND AEH.ACCOUNTING_DATE < P_START_DATE
482 AND AEL.THIRD_PARTY_ID = VENDOR_ID;
483 END IF;
484 RETURN (ROUND(L_BALANCE
485 ,CF_GROUP_PRECISION));
486 END CF_SUPP_BBFORMULA;
487
488 FUNCTION CF_GROUP_PRECISIONFORMULA(GROUP_CURRENCY IN VARCHAR2) RETURN NUMBER IS
489 PREC FND_CURRENCIES.PRECISION%TYPE;
490 BEGIN
491 IF GROUP_CURRENCY = C_BASE_CURRENCY_CODE THEN
492 RETURN (C_BASE_PRECISION);
493 ELSE
494 PREC := 0;
495 SELECT
496 C.PRECISION
497 INTO PREC
498 FROM
499 FND_CURRENCIES C
500 WHERE C.CURRENCY_CODE = GROUP_CURRENCY;
501 RETURN (PREC);
502 END IF;
503 RETURN NULL;
504 EXCEPTION
505 WHEN OTHERS THEN
506 /*SRW.MESSAGE(999
507 ,'Report did not find a definition for the currency ' || GROUP_CURRENCY)*/NULL;
508 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
509 END CF_GROUP_PRECISIONFORMULA;
510
511 FUNCTION CF_REMAIN_BALANCEFORMULA(CF_SUPP_BB IN NUMBER
512 ,CS_AMOUNT_APPLIED IN NUMBER) RETURN NUMBER IS
513 BEGIN
514 RETURN (CF_SUPP_BB + CS_AMOUNT_APPLIED);
515 END CF_REMAIN_BALANCEFORMULA;
516
517 FUNCTION CF_TRX_PRECISIONFORMULA(TRANS_CURRENCY IN VARCHAR2) RETURN NUMBER IS
518 PREC FND_CURRENCIES.PRECISION%TYPE;
519 BEGIN
520 IF TRANS_CURRENCY = C_BASE_CURRENCY_CODE THEN
521 RETURN (C_BASE_PRECISION);
522 ELSE
523 PREC := 0;
524 SELECT
525 C.PRECISION
526 INTO PREC
527 FROM
528 FND_CURRENCIES C
529 WHERE C.CURRENCY_CODE = TRANS_CURRENCY;
530 RETURN (PREC);
531 END IF;
532 RETURN NULL;
533 EXCEPTION
534 WHEN OTHERS THEN
535 /*SRW.MESSAGE(999
536 ,'Report did not find a definition for the currency ' || TRANS_CURRENCY)*/NULL;
537 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
538 END CF_TRX_PRECISIONFORMULA;
539
540 FUNCTION CF_ENDING_BALANCEFORMULA(CF_SUPP_BB IN NUMBER
541 ,CS_ACCUM_BALANCE IN NUMBER) RETURN NUMBER IS
542 BEGIN
543 RETURN (NVL(CF_SUPP_BB
544 ,0) + NVL(CS_ACCUM_BALANCE
545 ,0));
546 END CF_ENDING_BALANCEFORMULA;
547
548 FUNCTION ACCEPT_PARAMETERS RETURN BOOLEAN IS
549 BEGIN
550 IF (P_SUPPLIER_NAME_FROM IS NOT NULL AND P_SUPPLIER_NAME_TO IS NULL) OR (P_SUPPLIER_NAME_FROM IS NULL AND P_SUPPLIER_NAME_TO IS NOT NULL) THEN
551 FND_MESSAGE.SET_NAME('JL'
552 ,'JL_ZZ_AP_SRS_SUPPL_NAME_RANGE');
553 CP_MESSAGE := FND_MESSAGE.GET;
554 /*SRW.MESSAGE('999'
555 ,CP_MESSAGE)*/NULL;
556 RETURN (FALSE);
557 END IF;
558 IF P_SUPPLIER_NAME_FROM IS NOT NULL THEN
559 IF P_SUPPLIER_TAX_ID IS NOT NULL THEN
560 FND_MESSAGE.SET_NAME('JL'
561 ,'JL_ZZ_AP_SRS_INV_SUPPL_PARAM');
562 CP_MESSAGE := FND_MESSAGE.GET;
563 /*SRW.MESSAGE('999'
564 ,CP_MESSAGE)*/NULL;
565 RETURN (FALSE);
566 END IF;
567 END IF;
568 IF P_SUPPLIER_NAME_FROM IS NOT NULL THEN
569 LP_SUPPLIER := ' AND po1.vendor_name BETWEEN ''' || P_SUPPLIER_NAME_FROM || ''' AND ''' || P_SUPPLIER_NAME_TO || '''';
570 ELSIF P_SUPPLIER_TAX_ID IS NOT NULL THEN
571 LP_SUPPLIER := ' AND nvl(papf.national_identifier,nvl(po1.individual_1099,po1.num_1099)) = ''' || P_SUPPLIER_TAX_ID || '''';
572 ELSE
573 LP_SUPPLIER := ' ';
574 END IF;
575 CP_SUPPLIER_TAX_ID := P_SUPPLIER_TAX_ID;
576 RETURN (TRUE);
577 END ACCEPT_PARAMETERS;
578
579 FUNCTION CF_AMOUNT_TO_APPLYFORMULA(SECTION IN VARCHAR2
580 ,RELATE_TRANS_TYPE IN VARCHAR2
581 ,CF_AMOUNT_CURRENCY IN NUMBER
582 ,CF_AMOUNT_DUE_CURRENCY IN NUMBER) RETURN NUMBER IS
583 BEGIN
584 IF SECTION = 'APPLIED' AND RELATE_TRANS_TYPE <> 'PRPMT' THEN
585 RETURN (CF_AMOUNT_CURRENCY);
586 ELSE
587 RETURN (CF_AMOUNT_DUE_CURRENCY);
588 END IF;
589 END CF_AMOUNT_TO_APPLYFORMULA;
590
591 FUNCTION CF_1FORMULA(TRANS_ID IN NUMBER) RETURN CHAR IS
592 L_NAME VARCHAR2(50);
593 BEGIN
594 SELECT
595 NAME
596 INTO L_NAME
597 FROM
598 FND_DOC_SEQUENCE_CATEGORIES FDOC,
599 AP_INVOICES_ALL AI
600 WHERE AI.INVOICE_ID = TRANS_ID
601 AND FDOC.CODE = AI.DOC_CATEGORY_CODE
602 AND FDOC.APPLICATION_ID = 200;
603 RETURN (L_NAME);
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 RETURN (' ');
607 END CF_1FORMULA;
608
609 FUNCTION CF_TRANS_DATEFORMULA(TRANS_DATE IN DATE) RETURN CHAR IS
610 BEGIN
611 RETURN FND_DATE.DATE_TO_CHARDATE(TRANS_DATE);
612 END CF_TRANS_DATEFORMULA;
613
614 FUNCTION CF_REF_TRANS_DATE1FORMULA(REF_TRANS_DATE IN DATE) RETURN CHAR IS
615 BEGIN
616 RETURN FND_DATE.DATE_TO_CHARDATE(REF_TRANS_DATE);
617 END CF_REF_TRANS_DATE1FORMULA;
618
619 FUNCTION CF_START_DATEFORMULA RETURN CHAR IS
620 BEGIN
621 RETURN FND_DATE.DATE_TO_CHARDATE(P_START_DATE);
622 END CF_START_DATEFORMULA;
623
624 FUNCTION CF_END_DATEFORMULA RETURN CHAR IS
625 BEGIN
626 RETURN FND_DATE.DATE_TO_CHARDATE(P_END_DATE);
627 END CF_END_DATEFORMULA;
628
629 FUNCTION CF_COVER_START_DATEFORMULA RETURN CHAR IS
630 BEGIN
631 RETURN FND_DATE.DATE_TO_CHARDATE(P_START_DATE);
632 END CF_COVER_START_DATEFORMULA;
633
634 FUNCTION CF_COVER_END_DATEFORMULA RETURN CHAR IS
635 BEGIN
636 RETURN FND_DATE.DATE_TO_CHARDATE(P_END_DATE);
637 END CF_COVER_END_DATEFORMULA;
638
639 FUNCTION CF_TEXTFORMULA(DESCRIPTION1 IN VARCHAR2
640 ,GROUP_CURRENCY IN VARCHAR2
641 ,CF_AMT_DUE_DSP IN VARCHAR2) RETURN CHAR IS
642 L_BUFFER VARCHAR2(2000);
643 BEGIN
644 L_BUFFER := DESCRIPTION1;
645 L_BUFFER := REPLACE(L_BUFFER ,'&'
646 ,' ');
647 L_BUFFER := REPLACE(L_BUFFER
648 ,' F_TOTAL_BALANCE'
649 ,'(' || GROUP_CURRENCY || ') ' || LTRIM(RTRIM(CF_AMT_DUE_DSP)));
650 L_BUFFER := REPLACE(L_BUFFER
651 ,' F_AS_OF_DATE'
652 ,TO_CHAR(SYSDATE
653 ,'DD-MON-YYYY'));
654 RETURN (L_BUFFER);
655 END CF_TEXTFORMULA;
656
657 FUNCTION CF_AMOUNT_DUEFORMULA(VENDOR_ID IN NUMBER
658 ,VENDOR_SITE_ID IN NUMBER
659 ,CF_ENDING_BALANCE IN NUMBER) RETURN NUMBER IS
660 L_PREPAY_AMOUNT NUMBER;
661 L_AMOUNT_DUE NUMBER;
662
663 BEGIN
664 SELECT
665 SUM(NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT)) * - 1
666 INTO L_PREPAY_AMOUNT
667 FROM
668 AP_INVOICES_ALL AI,
669 AP_INVOICE_DISTRIBUTIONS_ALL AID,
670 AP_LOOKUP_CODES ALC
671 WHERE AID.ACCOUNTING_DATE <= P_END_DATE
672 AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
673 AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM'
674 AND AI.INVOICE_ID = AID.INVOICE_ID
675 AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
676 AND AI.INVOICE_TYPE_LOOKUP_CODE = ALC.LOOKUP_CODE
677 AND AID.POSTED_FLAG LIKE DECODE(P_POSTED
678 ,'N'
679 ,'%'
680 ,'Y')
681 AND AID.MATCH_STATUS_FLAG LIKE DECODE(P_APPROVED
682 ,'Y'
683 ,'A'
684 ,'%')
685 AND NVL(LTRIM(RTRIM(AI.DOC_CATEGORY_CODE))
686 ,'X') LIKE DECODE(P_DOCUMENT_CATEGORY
687 ,NULL
688 ,'%'
689 ,P_DOCUMENT_CATEGORY)
690 AND AI.VENDOR_ID = CF_AMOUNT_DUEFORMULA.VENDOR_ID
691 AND AI.VENDOR_SITE_ID = CF_AMOUNT_DUEFORMULA.VENDOR_SITE_ID;
692 L_AMOUNT_DUE := NVL(CF_ENDING_BALANCE
693 ,0) + NVL(L_PREPAY_AMOUNT
694 ,0);
695
696 RETURN (L_AMOUNT_DUE);
697 END CF_AMOUNT_DUEFORMULA;
698
699 FUNCTION CF_VENDOR_CHECKFORMULA(VENDOR_ID IN NUMBER) RETURN NUMBER IS
700 BEGIN
701 IF NVL(P_VENDOR_ID
702 ,0) <> VENDOR_ID THEN
703 P_VENDOR_ID := VENDOR_ID;
704 RETURN (1);
705 ELSE
706 RETURN (0);
707 END IF;
708 END CF_VENDOR_CHECKFORMULA;
709
710 FUNCTION CF_POSTED_ONLYFORMULA RETURN CHAR IS
711 A_FLAG FND_LOOKUPS.MEANING%TYPE;
712 BEGIN
713 SELECT
714 DECODE(P_POSTED
715 ,'Y'
716 ,C_NLS_YES
717 ,'N'
718 ,C_NLS_NO)
719 INTO A_FLAG
720 FROM
721 DUAL;
722 RETURN (A_FLAG);
723 END CF_POSTED_ONLYFORMULA;
724
725 FUNCTION CF_APPROVED_ONLYFORMULA RETURN CHAR IS
726 A_FLAG FND_LOOKUPS.MEANING%TYPE;
727 BEGIN
728 SELECT
729 DECODE(P_APPROVED
730 ,'Y'
731 ,C_NLS_YES
732 ,'N'
733 ,C_NLS_NO)
734 INTO A_FLAG
735 FROM
736 DUAL;
737 RETURN (A_FLAG);
738 END CF_APPROVED_ONLYFORMULA;
739
740 FUNCTION CF_GROUP_CURRFORMULA(GROUP_CURRENCY IN VARCHAR2) RETURN CHAR IS
741 BEGIN
742 RETURN (GROUP_CURRENCY);
743 END CF_GROUP_CURRFORMULA;
744
745 FUNCTION CP_TEXTFORMULA(CF_TEXT IN VARCHAR2) RETURN CHAR IS
746 BEGIN
747 RETURN (CF_TEXT);
748 END CP_TEXTFORMULA;
749
750 FUNCTION AFTERPFORM RETURN BOOLEAN IS
751 BEGIN
752 FND_MO_REPORTING_API.INITIALIZE(P_REPORTING_LEVEL
753 ,P_REPORTING_ENTITY_ID
754 ,'AUTO');
755 P_ORG_WHERE_POSIT := FND_MO_REPORTING_API.GET_PREDICATE('posit'
756 ,NULL);
757 P_ORG_WHERE_AI := FND_MO_REPORTING_API.GET_PREDICATE('ai'
758 ,NULL);
759 P_ORG_WHERE_AI2 := FND_MO_REPORTING_API.GET_PREDICATE('ai2'
760 ,NULL);
761 P_ORG_WHERE_AID := FND_MO_REPORTING_API.GET_PREDICATE('aid'
762 ,NULL);
763 P_ORG_WHERE_AID2 := FND_MO_REPORTING_API.GET_PREDICATE('aid2'
764 ,NULL);
765 P_ORG_WHERE_ALC := FND_MO_REPORTING_API.GET_PREDICATE('alc'
766 ,NULL);
767 P_ORG_WHERE_AC := FND_MO_REPORTING_API.GET_PREDICATE('ac'
768 ,NULL);
769 P_ORG_WHERE_AIP := FND_MO_REPORTING_API.GET_PREDICATE('aip'
770 ,NULL);
771 P_REPORTING_ENTITY_NAME := SUBSTRB(FND_MO_REPORTING_API.GET_REPORTING_ENTITY_NAME
772 ,1
773 ,50);
774 P_REPORTING_LEVEL_NAME := SUBSTRB(FND_MO_REPORTING_API.GET_REPORTING_LEVEL_NAME
775 ,1
776 ,50);
777 IF P_REPORTING_LEVEL = '1000' THEN
778 P_CHECK_LEDGER_IN_SP := MO_UTILS.CHECK_LEDGER_IN_SP(P_REPORTING_ENTITY_ID);
779 IF P_CHECK_LEDGER_IN_SP = 'N' THEN
780 FND_MESSAGE.SET_NAME('FND'
781 ,'FND_MO_RPT_PARTIAL_LEDGER');
782 P_PARTIAL_LEDGER_MSG := FND_MESSAGE.GET;
783 END IF;
784 END IF;
785 RETURN (TRUE);
786 END AFTERPFORM;
787
788 FUNCTION CF_ORG_NAMEFORMULA(ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
789 L_ORG_NAME VARCHAR2(100);
790 BEGIN
791 SELECT
792 ORG.NAME
793 INTO L_ORG_NAME
794 FROM
795 HR_LOCATIONS LOC,
796 HR_ALL_ORGANIZATION_UNITS ORG
797 WHERE ORG.ORGANIZATION_ID = CF_ORG_NAMEFORMULA.ORGANIZATION_ID
798 AND LOC.LOCATION_ID = ORG.LOCATION_ID;
799 RETURN (L_ORG_NAME);
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 RETURN (' ');
803 END CF_ORG_NAMEFORMULA;
804
805 FUNCTION P_CHECK_LEDGER_IN_SPVALIDTRIGG RETURN BOOLEAN IS
806 BEGIN
807 RETURN (TRUE);
808 END P_CHECK_LEDGER_IN_SPVALIDTRIGG;
809
810 FUNCTION P_PARTIAL_LEDGER_MSGVALIDTRIGG RETURN BOOLEAN IS
811 BEGIN
812 RETURN (TRUE);
813 END P_PARTIAL_LEDGER_MSGVALIDTRIGG;
814
815 FUNCTION CP_TEXT_P RETURN VARCHAR2 IS
816 BEGIN
817 RETURN CP_TEXT;
818 END CP_TEXT_P;
819
820 FUNCTION CP_REF_TRANS_NUM_P RETURN VARCHAR2 IS
821 BEGIN
822 RETURN CP_REF_TRANS_NUM;
823 END CP_REF_TRANS_NUM_P;
824
825 FUNCTION CP_REF_TRANS_TYPE_P RETURN VARCHAR2 IS
826 BEGIN
827 RETURN CP_REF_TRANS_TYPE;
828 END CP_REF_TRANS_TYPE_P;
829
830 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
831 BEGIN
832 RETURN C_NLS_YES;
833 END C_NLS_YES_P;
834
835 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
836 BEGIN
837 RETURN C_NLS_NO;
838 END C_NLS_NO_P;
839
840 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
841 BEGIN
842 RETURN C_NLS_ALL;
843 END C_NLS_ALL_P;
844
845 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
846 BEGIN
847 RETURN C_NLS_NO_DATA_EXISTS;
848 END C_NLS_NO_DATA_EXISTS_P;
849
850 FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
851 BEGIN
852 RETURN C_NLS_VOID;
853 END C_NLS_VOID_P;
854
855 FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
856 BEGIN
857 RETURN C_NLS_NA;
858 END C_NLS_NA_P;
859
860 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
861 BEGIN
862 RETURN C_NLS_END_OF_REPORT;
863 END C_NLS_END_OF_REPORT_P;
864
865 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
866 BEGIN
867 RETURN C_REPORT_START_DATE;
868 END C_REPORT_START_DATE_P;
869
870 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
871 BEGIN
872 RETURN C_COMPANY_NAME_HEADER;
873 END C_COMPANY_NAME_HEADER_P;
874
875 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
876 BEGIN
877 RETURN C_BASE_CURRENCY_CODE;
878 END C_BASE_CURRENCY_CODE_P;
879
880 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
881 BEGIN
882 RETURN C_BASE_PRECISION;
883 END C_BASE_PRECISION_P;
884
885 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
886 BEGIN
887 RETURN C_BASE_MIN_ACCT_UNIT;
888 END C_BASE_MIN_ACCT_UNIT_P;
889
890 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
891 BEGIN
892 RETURN C_BASE_DESCRIPTION;
893 END C_BASE_DESCRIPTION_P;
894
895 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
896 BEGIN
897 RETURN C_CHART_OF_ACCOUNTS_ID;
898 END C_CHART_OF_ACCOUNTS_ID_P;
899
900 FUNCTION APPLICATIONS_TEMPLATE_REPORT_P RETURN VARCHAR2 IS
901 BEGIN
902 RETURN APPLICATIONS_TEMPLATE_REPORT;
903 END APPLICATIONS_TEMPLATE_REPORT_P;
904
905 FUNCTION CP_CREATE_AWT_DISTS_TYPE_P RETURN VARCHAR2 IS
906 BEGIN
907 RETURN CP_CREATE_AWT_DISTS_TYPE;
908 END CP_CREATE_AWT_DISTS_TYPE_P;
909
910 FUNCTION LP_SUPPLIER_P RETURN VARCHAR2 IS
911 BEGIN
912 RETURN LP_SUPPLIER;
913 END LP_SUPPLIER_P;
914
915 FUNCTION CP_DATE4_FORMAT_P RETURN VARCHAR2 IS
916 BEGIN
917 RETURN CP_DATE4_FORMAT;
918 END CP_DATE4_FORMAT_P;
919
920 FUNCTION CP_MESSAGE_P RETURN VARCHAR2 IS
921 BEGIN
922 RETURN CP_MESSAGE;
923 END CP_MESSAGE_P;
924
925 FUNCTION CP_REPORT_CURRENCY_P RETURN VARCHAR2 IS
926 BEGIN
927 RETURN CP_REPORT_CURRENCY;
928 END CP_REPORT_CURRENCY_P;
929
930 FUNCTION CP_REPORT_CURR_DISP_P RETURN VARCHAR2 IS
931 BEGIN
932 RETURN CP_REPORT_CURR_DISP;
933 END CP_REPORT_CURR_DISP_P;
934
935 FUNCTION CP_SUPPLIER_NAME_FROM_P RETURN VARCHAR2 IS
936 BEGIN
937 RETURN CP_SUPPLIER_NAME_FROM;
938 END CP_SUPPLIER_NAME_FROM_P;
939
940 FUNCTION CP_SUPPLIER_NAME_TO_P RETURN VARCHAR2 IS
941 BEGIN
942 RETURN CP_SUPPLIER_NAME_TO;
943 END CP_SUPPLIER_NAME_TO_P;
944
945 FUNCTION CP_POSTED_FLAG_P RETURN VARCHAR2 IS
946 BEGIN
947 RETURN CP_POSTED_FLAG;
948 END CP_POSTED_FLAG_P;
949
950 FUNCTION CP_APPROVED_FLAG_P RETURN VARCHAR2 IS
951 BEGIN
952 RETURN CP_APPROVED_FLAG;
953 END CP_APPROVED_FLAG_P;
954
955 FUNCTION CP_LEDGER_NAME_P RETURN VARCHAR2 IS
956 BEGIN
957 RETURN CP_LEDGER_NAME;
958 END CP_LEDGER_NAME_P;
959
960 FUNCTION CP_SUPPLIER_TAX_ID_P RETURN VARCHAR2 IS
961 BEGIN
962 RETURN CP_SUPPLIER_TAX_ID;
963 END CP_SUPPLIER_TAX_ID_P;
964
965 FUNCTION CP_DOCUMENT_CATEGORY_P RETURN VARCHAR2 IS
966 BEGIN
967 RETURN CP_DOCUMENT_CATEGORY;
968 END CP_DOCUMENT_CATEGORY_P;
969
970 FUNCTION CP_TITLE_P RETURN VARCHAR2 IS
971 BEGIN
972 RETURN CP_TITLE;
973 END CP_TITLE_P;
974
975 FUNCTION CP_PARTIAL_LEDGER_MSG_P RETURN VARCHAR2 IS
976 BEGIN
977 RETURN CP_PARTIAL_LEDGER_MSG;
978 END CP_PARTIAL_LEDGER_MSG_P;
979
980 FUNCTION CONVERT(P_YES_NO IN VARCHAR2) RETURN VARCHAR2 IS
981 BEGIN
982 IF P_YES_NO IS NOT NULL THEN
983 SELECT
984 MEANING
985 INTO P_PARAMETERS_DESC
986 FROM
987 FND_LOOKUPS
988 WHERE LOOKUP_CODE = P_YES_NO
989 AND LOOKUP_TYPE = 'YES_NO';
990 CP_PARAMETERS_DESC:=P_PARAMETERS_DESC;
991 RETURN CP_PARAMETERS_DESC;
992 END IF;
993 EXCEPTION
994 WHEN OTHERS THEN
995 /*SRW.MESSAGE(022
996 ,'Convert function has failed with paramater value:' || P_PARAMETERS_DESC)*/NULL;
997 /*SRW.MESSAGE(023
998 ,SQLERRM)*/NULL;
999 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
1000 END CONVERT;
1001
1002 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
1003
1004 init exception;
1005
1006 BEGIN
1007
1008 IF (ACCEPT_PARAMETERS=FALSE) THEN
1009 raise init;
1010 END IF;
1011
1012 --GET_AWT_TIME;
1013
1014 -- Get the format mask for dates
1015 /*SRW.USER_EXIT('FND DATE4FORMAT
1016 RESULT=":cp_date4_format"');*/null;
1017
1018 RETURN (TRUE);
1019
1020 RETURN NULL; EXCEPTION
1021
1022 WHEN OTHERS THEN
1023 RETURN (FALSE);
1024
1025 END;
1026
1027 END JE_JEPLPSSR_XMLP_PKG;
1028
1029
1030
[Home] [Help]