[Home] [Help]
PACKAGE BODY: APPS.JL_JLZZPSSR_XMLP_PKG
Source
1 PACKAGE BODY JL_JLZZPSSR_XMLP_PKG AS
2 /* $Header: JLZZPSSRB.pls 120.1 2007/12/25 16:54:32 dwkrishn noship $ */
3 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
4
5 INIT EXCEPTION;
6
7 BEGIN
8
9 IF (ACCEPT_PARAMETERS=FALSE) THEN
10 RAISE INIT;
11 END IF;
12
13 GET_AWT_TIME;
14
15 -- GET THE FORMAT MASK FOR DATES
16 -- SRW.USER_EXIT('FND DATE4FORMAT
17 -- RESULT=":CP_DATE4_FORMAT"');
18
19 RETURN (TRUE);
20
21 EXCEPTION WHEN OTHERS THEN
22 RETURN (FALSE);
23
24 END;
25
26 FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
27 BASE_CURR AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
28 PREC FND_CURRENCIES_VL.PRECISION%TYPE;
29 MIN_AU FND_CURRENCIES_VL.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
30 DESCR FND_CURRENCIES_VL.DESCRIPTION%TYPE;
31 BEGIN
32 BASE_CURR := '';
33 PREC := 0;
34 MIN_AU := 0;
35 DESCR := '';
36 SELECT
37 P.BASE_CURRENCY_CODE,
38 C.PRECISION,
39 C.MINIMUM_ACCOUNTABLE_UNIT,
40 C.DESCRIPTION
41 INTO BASE_CURR,PREC,MIN_AU,DESCR
42 FROM
43 AP_SYSTEM_PARAMETERS P,
44 FND_CURRENCIES_VL C
45 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
46 C_BASE_CURRENCY_CODE := BASE_CURR;
47 C_BASE_PRECISION := PREC;
48 C_BASE_MIN_ACCT_UNIT := MIN_AU;
49 C_BASE_DESCRIPTION := DESCR;
50 RETURN (TRUE);
51 RETURN NULL;
52 EXCEPTION
53 WHEN OTHERS THEN
54 RETURN (FALSE);
55 END GET_BASE_CURR_DATA;
56
57 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
58 BEGIN
59 RETURN (TRUE);
60 RETURN NULL;
61 EXCEPTION
62 WHEN OTHERS THEN
63 RETURN (FALSE);
64 END GET_COVER_PAGE_VALUES;
65
66 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
67 NLS_VOID AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
68 NLS_NA AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
69 NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
70 NLS_YES FND_LOOKUPS.MEANING%TYPE;
71 NLS_NO FND_LOOKUPS.MEANING%TYPE;
72 BEGIN
73 SELECT
74 LY.MEANING,
75 LN.MEANING,
76 L1.DISPLAYED_FIELD,
77 L2.DISPLAYED_FIELD,
78 L3.DISPLAYED_FIELD
79 INTO NLS_YES,NLS_NO,NLS_ALL,NLS_VOID,NLS_NA
80 FROM
81 FND_LOOKUPS LY,
82 FND_LOOKUPS LN,
83 AP_LOOKUP_CODES L1,
84 AP_LOOKUP_CODES L2,
85 AP_LOOKUP_CODES L3
86 WHERE LY.LOOKUP_TYPE = 'YES_NO'
87 AND LY.LOOKUP_CODE = 'Y'
88 AND LN.LOOKUP_TYPE = 'YES_NO'
89 AND LN.LOOKUP_CODE = 'N'
90 AND L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
91 AND L1.LOOKUP_CODE = 'ALL'
92 AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
93 AND L2.LOOKUP_CODE = 'VOID'
94 AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
95 AND L3.LOOKUP_CODE = 'NA';
96 C_NLS_YES := NLS_YES;
97 C_NLS_NO := NLS_NO;
98 C_NLS_ALL := NLS_ALL;
99 C_NLS_VOID := NLS_VOID;
100 C_NLS_NA := NLS_NA;
101 FND_MESSAGE.SET_NAME('SQLAP'
102 ,'AP_APPRVL_NO_DATA');
103 C_NLS_NO_DATA_EXISTS := FND_MESSAGE.GET;
104 FND_MESSAGE.SET_NAME('SQLAP'
105 ,'AP_ALL_END_OF_REPORT');
106 C_NLS_END_OF_REPORT := FND_MESSAGE.GET;
107 --C_NLS_NO_DATA_EXISTS := '*** ' || C_NLS_NO_DATA_EXISTS || ' ***';
108 --C_NLS_END_OF_REPORT := '*** ' || C_NLS_END_OF_REPORT || ' ***';
109 RETURN (TRUE);
110 RETURN NULL;
111 EXCEPTION
112 WHEN OTHERS THEN
113 RETURN (FALSE);
114 END GET_NLS_STRINGS;
115
116 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
117 BEGIN
118 DECLARE
119 INIT_FAILURE EXCEPTION;
120 l_concurrent_program_name varchar2(1000);
121 BEGIN
122 C_REPORT_START_DATE := SYSDATE;
123 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
124 CP_START_DATE := TO_CHAR(P_START_DATE,'DD-MON-YYYY');
125 CP_END_DATE := TO_CHAR(P_END_DATE,'DD-MON-YYYY');
126
127 SELECT CONCURRENT_PROGRAM_NAME
128 INTO L_CONCURRENT_PROGRAM_NAME
129 FROM FND_CONCURRENT_PROGRAMS_VL
130 WHERE CONCURRENT_PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID;
131
132 IF (L_CONCURRENT_PROGRAM_NAME = 'JLARPSSR_XML') THEN
133 P_COUNTRY_CODE := 'AR';
134 END IF;
135 IF (L_CONCURRENT_PROGRAM_NAME = 'JLCLPSSR_XML') THEN
136 P_COUNTRY_CODE := 'CL';
137 END IF;
138
139
140 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
141 /*SRW.MESSAGE('1'
142 ,'After SRWINIT')*/NULL;
143 END IF;
144 IF (GET_COMPANY_NAME <> TRUE) THEN
145 RAISE INIT_FAILURE;
146 END IF;
147 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
148 /*SRW.MESSAGE('2'
149 ,'After Get_Company_Name')*/NULL;
150 END IF;
151 IF (GET_NLS_STRINGS <> TRUE) THEN
152 RAISE INIT_FAILURE;
153 END IF;
154 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
155 /*SRW.MESSAGE('3'
156 ,'After Get_NLS_Strings')*/NULL;
157 END IF;
158 IF (GET_BASE_CURR_DATA <> TRUE) THEN
159 RAISE INIT_FAILURE;
160 END IF;
161 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
162 /*SRW.MESSAGE('4'
163 ,'After Get_Base_Curr_Data')*/NULL;
164 END IF;
165 IF (CUSTOM_INIT <> TRUE) THEN
166 RAISE INIT_FAILURE;
167 END IF;
168 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
169 /*SRW.MESSAGE('7'
170 ,'After Custom_Init')*/NULL;
171 END IF;
172 IF (P_DEBUG_SWITCH in ('y','Y')) THEN
173 /*SRW.BREAK*/NULL;
174 END IF;
175 RETURN (TRUE);
176 EXCEPTION
177 WHEN OTHERS THEN
178 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
179 END;
180 RETURN (TRUE);
181
182 END BEFOREREPORT;
183
184 FUNCTION AFTERREPORT RETURN BOOLEAN IS
185 BEGIN
186 BEGIN
187 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
188 IF (P_DEBUG_SWITCH = 'Y') THEN
189 /*SRW.MESSAGE('20'
190 ,'After SRWEXIT')*/NULL;
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
195 END;
196 RETURN (TRUE);
197 END AFTERREPORT;
198
199 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
200 L_LEGAL_ENTITY_ID NUMBER;
201 BEGIN
202 IF P_REPORTING_LEVEL = '1000' THEN
203 L_LEGAL_ENTITY_ID := P_LEGAL_ENTITY_ID;
204 ELSIF P_REPORTING_LEVEL = '2000' THEN
205 L_LEGAL_ENTITY_ID := P_REPORTING_CONTEXT;
206 ELSE
207 L_LEGAL_ENTITY_ID := XLE_UTILITIES_GRP.GET_DEFAULTLEGALCONTEXT_OU(P_REPORTING_CONTEXT);
208 END IF;
209 SELECT
210 XLE.NAME
211 INTO C_COMPANY_NAME_HEADER
212 FROM
213 XLE_FIRSTPARTY_INFORMATION_V XLE
214 WHERE XLE.LEGAL_ENTITY_ID = L_LEGAL_ENTITY_ID;
215 RETURN (TRUE);
216 RETURN NULL;
217 EXCEPTION
218 WHEN OTHERS THEN
219 RETURN (FALSE);
220 END GET_COMPANY_NAME;
221
222 FUNCTION CF_REF_TRANS_DATEFORMULA(TRANS_TYPE IN VARCHAR2
223 ,TRANS_ID IN NUMBER) RETURN DATE IS
224 V_INVOICE_ID AP_INVOICES.INVOICE_ID%TYPE;
225 V_INVOICE_DATE AP_INVOICES.INVOICE_NUM%TYPE;
226 V_INVOICE_NUM AP_INVOICES.INVOICE_NUM%TYPE;
227 V_CHECK_ID AP_CHECKS.CHECK_ID%TYPE;
228 V_CHECK_DATE AP_CHECKS.CHECK_DATE%TYPE;
229 V_CHECK_NUMBER AP_CHECKS.CHECK_NUMBER%TYPE;
230 V_TRANS_TYPE VARCHAR2(25);
231 CURSOR C_INV_REL_AWT IS
232 SELECT
233 INVOICE_DATE,
234 INVOICE_NUM
235 FROM
236 AP_INVOICES_ALL
237 WHERE INVOICE_ID = V_INVOICE_ID;
238 CURSOR C_PMT_REL_AWT IS
239 SELECT
240 CHECK_DATE,
241 CHECK_NUMBER
242 FROM
243 AP_CHECKS_ALL
244 WHERE CHECK_ID = V_CHECK_ID;
245 BEGIN
246 IF TRANS_TYPE = 'AWT' THEN
247 IF CP_CREATE_AWT_DISTS_TYPE = 'PAYMENT' THEN
248 V_CHECK_ID := TRANS_ID;
249 OPEN C_PMT_REL_AWT;
250 FETCH C_PMT_REL_AWT
251 INTO V_CHECK_DATE,V_CHECK_NUMBER;
252 CLOSE C_PMT_REL_AWT;
253 CP_REF_TRANS_NUM := V_CHECK_NUMBER;
254 BEGIN
255 SELECT
256 L.MEANING
257 INTO V_TRANS_TYPE
258 FROM
259 FND_LOOKUPS L
260 WHERE L.LOOKUP_CODE = 'PMT'
261 AND L.LOOKUP_TYPE = 'JLZZ_SUPPLIER_TRX_TYPE'
262 AND NVL(L.START_DATE_ACTIVE
263 ,SYSDATE) <= sysdate
264 AND NVL(L.END_DATE_ACTIVE
265 ,SYSDATE) >= sysdate
266 AND L.ENABLED_FLAG = 'Y';
267 EXCEPTION
268 WHEN OTHERS THEN
269 CP_REF_TRANS_TYPE := NULL;
270 END;
271 CP_REF_TRANS_TYPE := V_TRANS_TYPE;
272 RETURN (V_CHECK_DATE);
273 ELSIF CP_CREATE_AWT_DISTS_TYPE = 'APPROVAL' THEN
274 V_INVOICE_ID := TRANS_ID;
275 OPEN C_INV_REL_AWT;
276 FETCH C_INV_REL_AWT
277 INTO V_INVOICE_DATE,V_INVOICE_NUM;
278 CLOSE C_INV_REL_AWT;
279 CP_REF_TRANS_NUM := V_INVOICE_NUM;
280 BEGIN
281 SELECT
282 L.MEANING
283 INTO V_TRANS_TYPE
284 FROM
285 FND_LOOKUPS L
286 WHERE L.LOOKUP_CODE = 'INV'
287 AND L.LOOKUP_TYPE = 'JLZZ_SUPPLIER_TRX_TYPE'
288 AND NVL(L.START_DATE_ACTIVE
289 ,SYSDATE) <= sysdate
290 AND NVL(L.END_DATE_ACTIVE
291 ,SYSDATE) >= sysdate
292 AND L.ENABLED_FLAG = 'Y';
293 EXCEPTION
294 WHEN OTHERS THEN
295 CP_REF_TRANS_TYPE := NULL;
296 END;
297 CP_REF_TRANS_TYPE := V_TRANS_TYPE;
298 RETURN (V_INVOICE_DATE);
299 ELSE
300 CP_REF_TRANS_NUM := NULL;
301 CP_REF_TRANS_TYPE := NULL;
302 RETURN (NULL);
303 END IF;
304 ELSE
305 CP_REF_TRANS_NUM := NULL;
306 CP_REF_TRANS_TYPE := NULL;
307 RETURN (NULL);
308 END IF;
309 RETURN NULL;
310 END CF_REF_TRANS_DATEFORMULA;
311
312 FUNCTION CF_AMOUNT_CURRENCYFORMULA(TRANS_AMT IN NUMBER
313 ,TRANS_BASE_AMT IN NUMBER) RETURN NUMBER IS
314 BEGIN
315 IF P_REPORT_CURRENCY = 'ORIGINAL' THEN
316 RETURN (TRANS_AMT);
317 ELSE
318 RETURN (ROUND(TRANS_BASE_AMT
319 ,C_BASE_PRECISION));
320 END IF;
321 RETURN NULL;
322 END CF_AMOUNT_CURRENCYFORMULA;
323
324 FUNCTION CF_AMOUNT_DUE_CURRENCYFORMULA(TRANS_TYPE IN VARCHAR2
325 ,TRANS_ID IN NUMBER
326 ,TRANS_AMT IN NUMBER
327 ,TRANS_BASE_AMT IN NUMBER
328 ,SECTION IN VARCHAR2
329 ,CF_TRX_PRECISION IN NUMBER) RETURN NUMBER IS
330 V_REMAINING_AMOUNT NUMBER;
331 V_AMOUNT_PAID NUMBER;
332 V_BASE_AMOUNT_PAID NUMBER;
333 V_PMT_AMOUNT NUMBER;
334 V_PMT_BASE_AMOUNT NUMBER;
335 V_INVOICE_ID AP_INVOICES.INVOICE_ID%TYPE;
336 V_RATE NUMBER;
337 V_AMOUNT NUMBER;
338 V_PAYMENT_STATUS_FLAG VARCHAR2(1);
339 V_DISC_AMT NUMBER;
340 V_EXCH_RATE NUMBER;
341 CURSOR C_PRPMT IS
342 SELECT
343 SUM(NVL(AID.PREPAY_AMOUNT_REMAINING
344 ,AID.AMOUNT)) * - 1
345 FROM
346 AP_INVOICE_DISTRIBUTIONS_ALL AID
347 WHERE AID.INVOICE_ID = V_INVOICE_ID
348 AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM'
349 AND AID.POSTED_FLAG = 'Y';
350 CURSOR C_INVOICE IS
351 SELECT
352 NVL(SUM(AID.AMOUNT)
353 ,0) * - 1,
354 NVL(SUM(NVL(AID.BASE_AMOUNT
355 ,AID.AMOUNT))
356 ,0) * - 1
357 FROM
358 AP_INVOICE_DISTRIBUTIONS_ALL AID
359 WHERE AID.INVOICE_ID = V_INVOICE_ID
360 AND ( AID.LINE_TYPE_LOOKUP_CODE = 'AWT'
361 OR AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' )
362 AND AID.POSTED_FLAG = 'Y';
363 CURSOR C_PMT IS
364 SELECT
365 NVL(SUM(IP.AMOUNT)
366 ,0),
367 NVL(SUM(NVL(IP.INVOICE_BASE_AMOUNT
368 ,IP.AMOUNT))
369 ,0)
370 FROM
371 AP_INVOICE_PAYMENTS_ALL IP
372 WHERE IP.INVOICE_ID = V_INVOICE_ID
373 AND IP.POSTED_FLAG = 'Y';
374 CURSOR C_DISC_AMT IS
375 SELECT
376 NVL(DISCOUNT_AMOUNT_TAKEN
377 ,0),
378 NVL(EXCHANGE_RATE
379 ,1)
380 FROM
381 AP_INVOICES_ALL AI
382 WHERE AI.INVOICE_ID = V_INVOICE_ID;
383 BEGIN
384 IF TRANS_TYPE = 'INV' THEN
385 V_INVOICE_ID := TRANS_ID;
386 OPEN C_INVOICE;
387 FETCH C_INVOICE
388 INTO V_AMOUNT_PAID,V_BASE_AMOUNT_PAID;
389 IF C_INVOICE%NOTFOUND THEN
390 V_AMOUNT_PAID := 0;
391 V_BASE_AMOUNT_PAID := 0;
392 END IF;
393 CLOSE C_INVOICE;
394 OPEN C_PMT;
395 FETCH C_PMT
396 INTO V_PMT_AMOUNT,V_PMT_BASE_AMOUNT;
397 IF C_PMT%NOTFOUND THEN
398 V_PMT_AMOUNT := 0;
399 V_PMT_BASE_AMOUNT := 0;
400 END IF;
401 CLOSE C_PMT;
402 OPEN C_DISC_AMT;
403 FETCH C_DISC_AMT
404 INTO V_DISC_AMT,V_EXCH_RATE;
405 CLOSE C_DISC_AMT;
406 IF P_REPORT_CURRENCY = 'ORIGINAL' THEN
407 RETURN (TRANS_AMT - (V_AMOUNT_PAID + V_PMT_AMOUNT) - V_DISC_AMT);
408 ELSE
409 V_AMOUNT := TRANS_BASE_AMT - (V_BASE_AMOUNT_PAID + V_PMT_BASE_AMOUNT) - (V_DISC_AMT * V_EXCH_RATE);
410 RETURN (V_AMOUNT);
411 END IF;
412 ELSIF TRANS_TYPE = 'PRPMT' THEN
413 IF SECTION = 'APPLIED' THEN
414 RETURN (NULL);
415 ELSE
416 V_INVOICE_ID := TRANS_ID;
417 OPEN C_PRPMT;
418 FETCH C_PRPMT
419 INTO V_REMAINING_AMOUNT;
420 IF C_PRPMT%NOTFOUND THEN
421 V_REMAINING_AMOUNT := 0;
422 END IF;
423 CLOSE C_PRPMT;
424 IF P_REPORT_CURRENCY = 'ORIGINAL' THEN
425 RETURN (V_REMAINING_AMOUNT);
426 ELSE
427 V_RATE := NVL(TRANS_BASE_AMT
428 ,0) / NVL(TRANS_AMT
429 ,999999);
430 V_AMOUNT := ROUND(V_REMAINING_AMOUNT * V_RATE
431 ,CF_TRX_PRECISION);
432 RETURN (V_AMOUNT);
433 END IF;
434 END IF;
435 ELSE
436 RETURN (NULL);
437 END IF;
438 RETURN NULL;
439 END CF_AMOUNT_DUE_CURRENCYFORMULA;
440
441 PROCEDURE GET_AWT_TIME IS
442 BEGIN
443 IF P_COUNTRY_CODE = 'AR' THEN
444 CP_CREATE_AWT_DISTS_TYPE := 'PAYMENT';
445 ELSIF P_COUNTRY_CODE = 'CL' THEN
446 CP_CREATE_AWT_DISTS_TYPE := 'APPROVAL';
447 ELSE
448 CP_CREATE_AWT_DISTS_TYPE := NULL;
449 END IF;
450 END GET_AWT_TIME;
451
452 FUNCTION CF_SUPP_BBFORMULA(VENDOR_ID IN NUMBER
453 ,GROUP_CURRENCY IN VARCHAR2
454 ,CF_GROUP_PRECISION IN NUMBER) RETURN NUMBER IS
455 L_BALANCE NUMBER;
456 L_SOB NUMBER;
457 L_PARTY_ID AP_SUPPLIERS.PARTY_ID%TYPE;
458 BEGIN
459 BEGIN
460 SELECT
461 PARTY_ID
462 INTO L_PARTY_ID
463 FROM
464 AP_SUPPLIERS
465 WHERE VENDOR_ID = CF_SUPP_BBFORMULA.VENDOR_ID;
466 EXCEPTION
467 WHEN OTHERS THEN
468 L_PARTY_ID := NULL;
469 END;
470 IF P_REPORT_CURRENCY = 'ORIGINAL' THEN
471 SELECT
472 NVL(SUM(AEL.ENTERED_CR)
473 ,0) - NVL(SUM(AEL.ENTERED_DR)
474 ,0)
475 INTO L_BALANCE
476 FROM
477 XLA_AE_LINES AEL,
478 XLA_AE_HEADERS AEH,
479 XLA_ACCT_CLASS_ASSGNS ACS,
480 XLA_ASSIGNMENT_DEFNS_B ASD
481 WHERE AEH.LEDGER_ID = L_SOB
482 AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
483 AND AEH.ACCOUNTING_DATE < P_START_DATE
484 AND AEL.PARTY_ID = L_PARTY_ID
485 AND AEL.CURRENCY_CODE = GROUP_CURRENCY
486 AND AEL.ACCOUNTING_CLASS_CODE = ACS.ACCOUNTING_CLASS_CODE
487 AND ACS.PROGRAM_CODE = 'JL_POST_LIAB_REGISTER'
488 AND ACS.PROGRAM_CODE = ASD.PROGRAM_CODE
489 AND ASD.ASSIGNMENT_CODE = ACS.ASSIGNMENT_CODE
490 AND ASD.ENABLED_FLAG = 'Y';
491 ELSE
492 SELECT
493 NVL(SUM(AEL.ACCOUNTED_CR)
494 ,0) - NVL(SUM(AEL.ACCOUNTED_DR)
495 ,0)
496 INTO L_BALANCE
497 FROM
498 XLA_AE_LINES AEL,
499 XLA_AE_HEADERS AEH,
500 XLA_ACCT_CLASS_ASSGNS ACS,
501 XLA_ASSIGNMENT_DEFNS_B ASD
502 WHERE AEH.LEDGER_ID = L_SOB
503 AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
504 --AND AEL.LEDGER_ID = P_SOB_ID
505 AND AEL.LEDGER_ID = CP_SOB_ID
506 AND AEH.ACCOUNTING_DATE < P_START_DATE
507 AND AEL.PARTY_ID = L_PARTY_ID
508 AND AEL.CURRENCY_CODE = GROUP_CURRENCY
509 AND AEL.ACCOUNTING_CLASS_CODE = ACS.ACCOUNTING_CLASS_CODE
510 AND ACS.PROGRAM_CODE = 'JL_POST_LIAB_REGISTER'
511 AND ACS.PROGRAM_CODE = ASD.PROGRAM_CODE
512 AND ASD.ASSIGNMENT_CODE = ACS.ASSIGNMENT_CODE
513 AND ASD.ENABLED_FLAG = 'Y';
514 END IF;
515 RETURN (ROUND(L_BALANCE
516 ,CF_GROUP_PRECISION));
517 END CF_SUPP_BBFORMULA;
518
519 FUNCTION CF_GROUP_PRECISIONFORMULA(GROUP_CURRENCY IN VARCHAR2) RETURN NUMBER IS
520 PREC FND_CURRENCIES.PRECISION%TYPE;
521 BEGIN
522 IF GROUP_CURRENCY = C_BASE_CURRENCY_CODE THEN
523 RETURN (C_BASE_PRECISION);
524 ELSE
525 PREC := 0;
526 SELECT
527 C.PRECISION
528 INTO PREC
529 FROM
530 FND_CURRENCIES C
531 WHERE C.CURRENCY_CODE = GROUP_CURRENCY;
532 RETURN (PREC);
533 END IF;
534 RETURN NULL;
535 EXCEPTION
536 WHEN OTHERS THEN
537 /*SRW.MESSAGE(999
538 ,'Report did not find a definition for the currency ' || GROUP_CURRENCY)*/NULL;
539 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
540 END CF_GROUP_PRECISIONFORMULA;
541
542 FUNCTION CF_REMAIN_BALANCEFORMULA(CF_SUPP_BB IN NUMBER
543 ,CS_AMOUNT_APPLIED IN NUMBER) RETURN NUMBER IS
544 BEGIN
545 RETURN (CF_SUPP_BB + CS_AMOUNT_APPLIED);
546 END CF_REMAIN_BALANCEFORMULA;
547
548 FUNCTION CF_TRX_PRECISIONFORMULA(TRANS_CURRENCY IN VARCHAR2) RETURN NUMBER IS
549 PREC FND_CURRENCIES.PRECISION%TYPE;
550 BEGIN
551 IF TRANS_CURRENCY = C_BASE_CURRENCY_CODE THEN
552 RETURN (C_BASE_PRECISION);
553 ELSE
554 PREC := 0;
555 SELECT
556 C.PRECISION
557 INTO PREC
558 FROM
559 FND_CURRENCIES C
560 WHERE C.CURRENCY_CODE = TRANS_CURRENCY;
561 RETURN (PREC);
562 END IF;
563 RETURN NULL;
564 EXCEPTION
565 WHEN OTHERS THEN
566 /*SRW.MESSAGE(999
567 ,'Report did not find a definition for the currency ' || TRANS_CURRENCY)*/NULL;
568 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
569 END CF_TRX_PRECISIONFORMULA;
570
571 FUNCTION CF_ENDING_BALANCEFORMULA(CF_SUPP_BB IN NUMBER
572 ,CS_ACCUM_BALANCE IN NUMBER) RETURN NUMBER IS
573 BEGIN
574 RETURN (CF_SUPP_BB + CS_ACCUM_BALANCE);
575 END CF_ENDING_BALANCEFORMULA;
576
577 FUNCTION ACCEPT_PARAMETERS RETURN BOOLEAN IS
578 BEGIN
579 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
580 FND_MESSAGE.SET_NAME('JL'
581 ,'JL_ZZ_AP_SRS_SUPPL_NAME_RANGE');
582 CP_MESSAGE := FND_MESSAGE.GET;
583 /*SRW.MESSAGE('999'
584 ,CP_MESSAGE)*/NULL;
585 RETURN (FALSE);
586 END IF;
587 IF P_SUPPLIER_NAME_FROM IS NOT NULL THEN
588 IF P_SUPPLIER_TAX_ID IS NOT NULL THEN
589 FND_MESSAGE.SET_NAME('JL'
590 ,'JL_ZZ_AP_SRS_INV_SUPPL_PARAM');
591 CP_MESSAGE := FND_MESSAGE.GET;
592 /*SRW.MESSAGE('999'
593 ,CP_MESSAGE)*/NULL;
594 RETURN (FALSE);
595 END IF;
596 END IF;
597 IF P_SUPPLIER_NAME_FROM IS NOT NULL THEN
598 LP_SUPPLIER := ' WHERE pov.vendor_name BETWEEN ''' || P_SUPPLIER_NAME_FROM || ''' AND ''' || P_SUPPLIER_NAME_TO || '''';
599 LP_SUPPLIER := LP_SUPPLIER || ' AND pov.vendor_id = pvs.vendor_id ';
600 ELSIF P_SUPPLIER_TAX_ID IS NOT NULL THEN
601 LP_SUPPLIER := ' WHERE pov.num_1099 = ''' || P_SUPPLIER_TAX_ID || '''';
602 LP_SUPPLIER := LP_SUPPLIER || ' AND pov.vendor_id = pvs.vendor_id ';
603 ELSE
604 LP_SUPPLIER := ' WHERE pov.vendor_id = pvs.vendor_id';
605 END IF;
606 RETURN (TRUE);
607 END ACCEPT_PARAMETERS;
608
609 FUNCTION CF_AMOUNT_TO_APPLYFORMULA(SECTION IN VARCHAR2
610 ,CF_AMOUNT_CURRENCY IN NUMBER
611 ,CF_AMOUNT_DUE_CURRENCY IN NUMBER) RETURN NUMBER IS
612 BEGIN
613 IF SECTION = 'APPLIED' THEN
614 RETURN (CF_AMOUNT_CURRENCY);
615 ELSE
616 RETURN (CF_AMOUNT_DUE_CURRENCY);
617 END IF;
618 END CF_AMOUNT_TO_APPLYFORMULA;
619
620 FUNCTION AFTERPFORM RETURN BOOLEAN IS
621 BEGIN
622 CP_SOB_ID:=P_SOB_ID;
623 IF P_COUNTRY_CODE = 'CL' AND P_CA_SET_OF_BOOKS_ID IS NOT NULL THEN
624 CP_SOB_ID := P_CA_SET_OF_BOOKS_ID;
625 END IF;
626 IF P_REPORTING_LEVEL = '2000' THEN
627 LP_AP_CHECKS_WH := 'AND ac.legal_entity_id = ' || P_REPORTING_CONTEXT;
628 LP_AP_INVOICES_WH := 'AND ai.legal_entity_id = ' || P_REPORTING_CONTEXT;
629 ELSE
630 /*SRW.MESSAGE('124'
631 ,' Not 2000')*/NULL;
632 FND_MO_REPORTING_API.INITIALIZE(P_REPORTING_LEVEL
633 ,P_REPORTING_CONTEXT
634 ,'AUTO');
635 LP_AP_INVOICE_DISTRIBUTIONS_WH := FND_MO_REPORTING_API.GET_PREDICATE('DIS'
636 ,NULL
637 ,P_REPORTING_CONTEXT);
638 LP_AP_CHECKS_WH := FND_MO_REPORTING_API.GET_PREDICATE('AC'
639 ,NULL
640 ,P_REPORTING_CONTEXT);
641 LP_AP_INVOICES_WH := FND_MO_REPORTING_API.GET_PREDICATE('AI'
642 ,NULL
643 ,P_REPORTING_CONTEXT);
644 LP_AP_INVOICE_PAYMENTS_WH := FND_MO_REPORTING_API.GET_PREDICATE('AIP'
645 ,NULL
646 ,P_REPORTING_CONTEXT);
647 END IF;
648 /*SRW.MESSAGE('125'
649 ,'pass param')*/NULL;
650 LP_AP_INVOICES := 'AP_INVOICES_ALL';
651 LP_AP_INVOICE_DISTRIBUTIONS := 'AP_INVOICE_DISTRIBUTIONS_ALL';
652 LP_AP_INVOICE_PAYMENTS := 'AP_INVOICE_PAYMENTS_ALL';
653 /*SRW.MESSAGE('126'
654 ,'pass param 2')*/NULL;
655 RETURN (TRUE);
656 END AFTERPFORM;
657
658 FUNCTION CP_REF_TRANS_NUM_P RETURN VARCHAR2 IS
659 BEGIN
660 RETURN CP_REF_TRANS_NUM;
661 END CP_REF_TRANS_NUM_P;
662
663 FUNCTION CP_REF_TRANS_TYPE_P RETURN VARCHAR2 IS
664 BEGIN
665 RETURN CP_REF_TRANS_TYPE;
666 END CP_REF_TRANS_TYPE_P;
667
668 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
669 BEGIN
670 RETURN C_NLS_YES;
671 END C_NLS_YES_P;
672
673 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
674 BEGIN
675 RETURN C_NLS_NO;
676 END C_NLS_NO_P;
677
678 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
679 BEGIN
680 RETURN C_NLS_ALL;
681 END C_NLS_ALL_P;
682
683 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
684 BEGIN
685 RETURN C_NLS_NO_DATA_EXISTS;
686 END C_NLS_NO_DATA_EXISTS_P;
687
688 FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
689 BEGIN
690 RETURN C_NLS_VOID;
691 END C_NLS_VOID_P;
692
693 FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
694 BEGIN
695 RETURN C_NLS_NA;
696 END C_NLS_NA_P;
697
698 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
699 BEGIN
700 RETURN C_NLS_END_OF_REPORT;
701 END C_NLS_END_OF_REPORT_P;
702
703 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
704 BEGIN
705 RETURN C_REPORT_START_DATE;
706 END C_REPORT_START_DATE_P;
707
708 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
709 BEGIN
710 RETURN C_COMPANY_NAME_HEADER;
711 END C_COMPANY_NAME_HEADER_P;
712
713 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
714 BEGIN
715 RETURN C_BASE_CURRENCY_CODE;
716 END C_BASE_CURRENCY_CODE_P;
717
718 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
719 BEGIN
720 RETURN C_BASE_PRECISION;
721 END C_BASE_PRECISION_P;
722
723 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
724 BEGIN
725 RETURN C_BASE_MIN_ACCT_UNIT;
726 END C_BASE_MIN_ACCT_UNIT_P;
727
728 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
729 BEGIN
730 RETURN C_BASE_DESCRIPTION;
731 END C_BASE_DESCRIPTION_P;
732
733 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
734 BEGIN
735 RETURN C_CHART_OF_ACCOUNTS_ID;
736 END C_CHART_OF_ACCOUNTS_ID_P;
737
738 FUNCTION APPLICATIONS_TEMPLATE_REPORT_P RETURN VARCHAR2 IS
739 BEGIN
740 RETURN APPLICATIONS_TEMPLATE_REPORT;
741 END APPLICATIONS_TEMPLATE_REPORT_P;
742
743 FUNCTION CP_CREATE_AWT_DISTS_TYPE_P RETURN VARCHAR2 IS
744 BEGIN
745 RETURN CP_CREATE_AWT_DISTS_TYPE;
746 END CP_CREATE_AWT_DISTS_TYPE_P;
747
748 FUNCTION LP_SUPPLIER_P RETURN VARCHAR2 IS
749 BEGIN
750 RETURN LP_SUPPLIER;
751 END LP_SUPPLIER_P;
752
753 FUNCTION CP_DATE4_FORMAT_P RETURN VARCHAR2 IS
754 BEGIN
755 RETURN CP_DATE4_FORMAT;
756 END CP_DATE4_FORMAT_P;
757
758 FUNCTION CP_MESSAGE_P RETURN VARCHAR2 IS
759 BEGIN
760 RETURN CP_MESSAGE;
761 END CP_MESSAGE_P;
762
763 END JL_JLZZPSSR_XMLP_PKG;
764
765
766