[Home] [Help]
PACKAGE BODY: APPS.JL_JLARPPFF_XMLP_PKG
Source
1 PACKAGE BODY JL_JLARPPFF_XMLP_PKG AS
2 /* $Header: JLARPPFFB.pls 120.2 2008/01/11 07:52:01 abraghun noship $ */
3 PROCEDURE GET_BASE_CURR_DATA IS
4 BASE_CURR AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5 PREC FND_CURRENCIES.PRECISION%TYPE;
6 MIN_AU FND_CURRENCIES.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
7 DESCR FND_CURRENCIES.DESCRIPTION%TYPE;
8 ORG_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
9 BEGIN
10 BASE_CURR := '';
11 PREC := 0;
12 MIN_AU := 0;
13 DESCR := '';
14 ORG_NAME := '';
15 SELECT
16 FCURR.CURRENCY_CODE,
17 FCURR.PRECISION,
18 FCURR.MINIMUM_ACCOUNTABLE_UNIT,
19 FCURR.DESCRIPTION,
20 GSBKS.NAME
21 INTO BASE_CURR,PREC,MIN_AU,DESCR,ORG_NAME
22 FROM
23 AR_SYSTEM_PARAMETERS ASP,
24 FND_CURRENCIES_VL FCURR,
25 GL_SETS_OF_BOOKS GSBKS
26 WHERE ASP.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
27 AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 NULL;
31 WHEN OTHERS THEN
32 C_BASE_CURRENCY_CODE := BASE_CURR;
33 C_BASE_PRECISION := PREC;
34 C_BASE_MIN_ACCT_UNIT := MIN_AU;
35 C_BASE_DESCRIPTION := DESCR;
36 END GET_BASE_CURR_DATA;
37
38 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
39 BEGIN
40 RETURN (TRUE);
41 RETURN NULL;
42 EXCEPTION
43 WHEN OTHERS THEN
44 RETURN (FALSE);
45 END CUSTOM_INIT;
46
47 FUNCTION AFTERREPORT RETURN BOOLEAN IS
48 BEGIN
49 BEGIN
50 ZX_EXTRACT_PKG.PURGE(P_CONC_REQUEST_ID);
51 EXCEPTION
52 WHEN OTHERS THEN
53 NULL;
54 END;
55 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
56 RETURN (TRUE);
57 EXCEPTION
58 WHEN OTHERS THEN
59 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
60 END AFTERREPORT;
61
62 FUNCTION FORMAT_CURRENCY(P_AMOUNT IN NUMBER) RETURN VARCHAR2 IS
63 FORMAT_STRING VARCHAR2(16);
64 FORMATED_AMOUNT VARCHAR2(15);
65 BEGIN
69 FORMAT_STRING := LPAD(FORMAT_STRING
66 FORMAT_STRING := RPAD('0.'
67 ,4
68 ,'0');
70 ,16
71 ,'0');
72 FORMATED_AMOUNT := LPAD(REPLACE(LTRIM(RTRIM(TO_CHAR(ABS(P_AMOUNT)
73 ,FORMAT_STRING)))
74 ,'.'
75 ,'')
76 ,15
77 ,'0');
78 RETURN (FORMATED_AMOUNT);
79 EXCEPTION
80 WHEN OTHERS THEN
81 FORMATED_AMOUNT := LPAD(REPLACE(TO_CHAR(ABS(ROUND(P_AMOUNT
82 ,2)))
83 ,'.'
84 ,'')
85 ,15
86 ,'0');
87 RETURN FORMATED_AMOUNT;
88 END FORMAT_CURRENCY;
89
90 FUNCTION CF_TOT_DOC_FFORMULA(CS_TOT_DOC_AMT IN NUMBER) RETURN VARCHAR2 IS
91 L_CHAR_OPERATION_AMT VARCHAR2(15);
92 BEGIN
93 L_CHAR_OPERATION_AMT := FORMAT_CURRENCY(CS_TOT_DOC_AMT);
94 RETURN (L_CHAR_OPERATION_AMT);
95 EXCEPTION
96 WHEN OTHERS THEN
97 RETURN ('000000000000000');
98 END CF_TOT_DOC_FFORMULA;
99
100 FUNCTION CF_TOT_NON_TAXABLE_AMTFORMULA(CS_NON_TAXABLE_AMT IN NUMBER) RETURN VARCHAR2 IS
101 L_CHAR_NON_TAXABLE_AMT VARCHAR2(15);
102 BEGIN
103 L_CHAR_NON_TAXABLE_AMT := FORMAT_CURRENCY(CS_NON_TAXABLE_AMT);
104 RETURN (L_CHAR_NON_TAXABLE_AMT);
105 EXCEPTION
106 WHEN OTHERS THEN
107 RETURN ('000000000000000');
108 END CF_TOT_NON_TAXABLE_AMTFORMULA;
109
110 FUNCTION CF_TOT_TAXABLE_AMTFORMULA(CS_TAXABLE_AMT IN NUMBER) RETURN CHAR IS
111 L_CHAR_TAXABLE_AMT VARCHAR2(15);
112 BEGIN
113 L_CHAR_TAXABLE_AMT := FORMAT_CURRENCY(CS_TAXABLE_AMT);
114 RETURN (L_CHAR_TAXABLE_AMT);
115 EXCEPTION
116 WHEN OTHERS THEN
117 RETURN ('000000000000000');
118 END CF_TOT_TAXABLE_AMTFORMULA;
119
120 FUNCTION CF_TOT_VAT_TAX_AMTFORMULA(CS_VAT_TAX_AMT IN NUMBER) RETURN VARCHAR2 IS
121 L_VAT_TAX_AMT VARCHAR2(15);
122 BEGIN
123 L_VAT_TAX_AMT := FORMAT_CURRENCY(CS_VAT_TAX_AMT);
124 RETURN (L_VAT_TAX_AMT);
125 EXCEPTION
126 WHEN OTHERS THEN
127 RETURN ('000000000000000');
128 END CF_TOT_VAT_TAX_AMTFORMULA;
129
130 FUNCTION CF_TOT_VAT_PER_AMTFORMULA(CS_VAT_PER_AMT IN NUMBER) RETURN VARCHAR2 IS
131 L_CHAR_VAT_PER_AMT VARCHAR2(15);
132 BEGIN
133 L_CHAR_VAT_PER_AMT := FORMAT_CURRENCY(CS_VAT_PER_AMT);
134 RETURN (L_CHAR_VAT_PER_AMT);
135 EXCEPTION
136 WHEN OTHERS THEN
137 RETURN ('000000000000000');
138 END CF_TOT_VAT_PER_AMTFORMULA;
139
140 FUNCTION CF_TOT_PRO_PER_AMTFORMULA(CS_PRO_PER_AMT IN NUMBER) RETURN CHAR IS
141 L_CHAR_PRO_PER_AMT VARCHAR2(15);
142 BEGIN
143 L_CHAR_PRO_PER_AMT := FORMAT_CURRENCY(CS_PRO_PER_AMT);
144 RETURN (L_CHAR_PRO_PER_AMT);
145 EXCEPTION
146 WHEN OTHERS THEN
147 RETURN ('000000000000000');
148 END CF_TOT_PRO_PER_AMTFORMULA;
149
150 FUNCTION CF_TOT_EXMPT_AMTFORMULA(CS_EXMPT_AMT IN NUMBER) RETURN VARCHAR2 IS
151 L_CHAR_EXMPT_AMT VARCHAR2(15);
152 BEGIN
153 L_CHAR_EXMPT_AMT := FORMAT_CURRENCY(CS_EXMPT_AMT);
154 RETURN (L_CHAR_EXMPT_AMT);
155 EXCEPTION
156 WHEN OTHERS THEN
157 RETURN ('000000000000000');
158 END CF_TOT_EXMPT_AMTFORMULA;
159
160 FUNCTION CF_TOT_MUN_PER_AMTFORMULA(CS_MUN_PER_AMT IN NUMBER) RETURN VARCHAR2 IS
161 L_CHAR_MUN_PER_AMT VARCHAR2(15);
162 BEGIN
163 L_CHAR_MUN_PER_AMT := FORMAT_CURRENCY(CS_MUN_PER_AMT);
164 RETURN (L_CHAR_MUN_PER_AMT);
165 EXCEPTION
166 WHEN OTHERS THEN
167 RETURN ('000000000000000');
168 END CF_TOT_MUN_PER_AMTFORMULA;
169
170 FUNCTION CF_TOT_EXC_AMTFORMULA(CS_EXC_AMT IN NUMBER) RETURN VARCHAR2 IS
171 L_CHAR_EXC_AMT VARCHAR2(15);
172 BEGIN
173 L_CHAR_EXC_AMT := FORMAT_CURRENCY(CS_EXC_AMT);
174 RETURN (L_CHAR_EXC_AMT);
175 EXCEPTION
176 WHEN OTHERS THEN
177 RETURN ('000000000000000');
178 END CF_TOT_EXC_AMTFORMULA;
179
180 FUNCTION CF_REC_TYPE_2FORMULA(CF_TOT_REC IN NUMBER
181 ,CF_TOT_DOC_F IN VARCHAR2
182 ,CF_TOT_NON_TAXABLE_AMT IN VARCHAR2
183 ,CF_TOT_TAXABLE_AMT IN VARCHAR2
184 ,CF_TOT_VAT_TAX_AMT IN VARCHAR2
185 ,CF_TOT_EXMPT_AMT IN VARCHAR2
186 ,CF_TOT_VAT_PER_AMT IN VARCHAR2
187 ,CF_TOT_FED_PER_AMT IN VARCHAR2
188 ,CF_TOT_PRO_PER_AMT IN VARCHAR2
189 ,CF_TOT_MUN_PER_AMT IN VARCHAR2
190 ,CF_TOT_EXC_AMT IN VARCHAR2) RETURN VARCHAR2 IS
191 L_REC_TYPE_2 VARCHAR2(369);
192 BEGIN
193 RETURN (RPAD('2' || TO_CHAR(P_GL_DATE_FROM
194 ,'YYYYMM') || RPAD(' '
195 ,10
196 ,' ') || LPAD(TO_CHAR(CF_TOT_REC)
197 ,12
198 ,'0') || RPAD(' '
199 ,31
200 ,' ') || CP_COMP_TAX_ID || RPAD(' '
201 ,30
202 ,' ') || CF_TOT_DOC_F || CF_TOT_NON_TAXABLE_AMT || CF_TOT_TAXABLE_AMT || RPAD(' '
203 ,4
204 ,' ') || CF_TOT_VAT_TAX_AMT || CF_TOT_EXMPT_AMT || CF_TOT_VAT_PER_AMT || CF_TOT_FED_PER_AMT || CF_TOT_PRO_PER_AMT ||
208 END CF_REC_TYPE_2FORMULA;
205 CF_TOT_MUN_PER_AMT || CF_TOT_EXC_AMT
206 ,369
207 ,' '));
209
210 FUNCTION CF_BLANK_SUMFORMULA RETURN CHAR IS
211 BEGIN
212 RETURN (' ');
213 END CF_BLANK_SUMFORMULA;
214
215 FUNCTION CF_TOT_DOC_AMT_NUMFORMULA(STATUS IN VARCHAR2
216 ,DOC_AMT_NON1 IN NUMBER) RETURN NUMBER IS
217 L_TOT_DOC_AMT NUMBER;
218 BEGIN
219 L_TOT_DOC_AMT := 0;
220 BEGIN
221 IF STATUS <> 'CANCELLED' THEN
222 L_TOT_DOC_AMT := DOC_AMT_NON1;
223 END IF;
224 END;
225 RETURN (L_TOT_DOC_AMT);
226 END CF_TOT_DOC_AMT_NUMFORMULA;
227
228 FUNCTION CF_EXMPT_AMT_NUMFORMULA(STATUS IN VARCHAR2
229 ,EXMPT_AMT IN NUMBER) RETURN NUMBER IS
230 L_EXMPT_AMOUNT NUMBER;
231 BEGIN
232 L_EXMPT_AMOUNT := 0;
233 IF STATUS <> 'CANCELLED' THEN
234 L_EXMPT_AMOUNT := EXMPT_AMT;
235 END IF;
236 BEGIN
237 /*SRW.MESSAGE('01'
238 ,'Exempt Amt :' || TO_CHAR(L_EXMPT_AMOUNT))*/NULL;
239 EXCEPTION
240 WHEN OTHERS THEN
241 L_EXMPT_AMOUNT := 0;
242 END;
243 RETURN (L_EXMPT_AMOUNT);
244 END CF_EXMPT_AMT_NUMFORMULA;
245
246 FUNCTION CF_TAXABLE_AMT_NUMFORMULA(STATUS IN VARCHAR2
247 ,TAXABLE_AMT IN NUMBER) RETURN NUMBER IS
248 L_TAXABLE_AMT NUMBER;
249 BEGIN
250 L_TAXABLE_AMT := 0;
251 BEGIN
252 IF STATUS <> 'CANCELLED' THEN
253 L_TAXABLE_AMT := TAXABLE_AMT;
254 /*SRW.MESSAGE('01'
255 ,'Taxable Amount :' || TO_CHAR(L_TAXABLE_AMT))*/NULL;
256 END IF;
257 EXCEPTION
258 WHEN OTHERS THEN
259 L_TAXABLE_AMT := 0;
260 END;
261 RETURN (L_TAXABLE_AMT);
262 END CF_TAXABLE_AMT_NUMFORMULA;
263
264 FUNCTION CF_VAT_PER_AMT_NUMFORMULA(STATUS IN VARCHAR2
265 ,VAT_PER_AMT IN NUMBER) RETURN NUMBER IS
266 L_VAT_PER_AMOUNT NUMBER;
267 BEGIN
268 L_VAT_PER_AMOUNT := 0;
269 BEGIN
270 IF STATUS <> 'CANCELLED' THEN
271 L_VAT_PER_AMOUNT := VAT_PER_AMT;
272 END IF;
273 EXCEPTION
274 WHEN OTHERS THEN
275 L_VAT_PER_AMOUNT := 0;
276 END;
277 RETURN (L_VAT_PER_AMOUNT);
278 END CF_VAT_PER_AMT_NUMFORMULA;
279
280 FUNCTION CF_PRO_PER_AMT_NUMFORMULA(STATUS IN VARCHAR2
281 ,PRO_AMT IN NUMBER) RETURN NUMBER IS
282 L_PRO_PER_AMT NUMBER;
283 BEGIN
284 L_PRO_PER_AMT := 0;
285 BEGIN
286 IF STATUS <> 'CANCELLED' THEN
287 L_PRO_PER_AMT := PRO_AMT;
288 END IF;
289 /*SRW.MESSAGE('01'
290 ,'Pro Perception Amt :' || TO_CHAR(L_PRO_PER_AMT))*/NULL;
291 EXCEPTION
292 WHEN OTHERS THEN
293 L_PRO_PER_AMT := 0;
294 END;
295 RETURN (L_PRO_PER_AMT);
296 END CF_PRO_PER_AMT_NUMFORMULA;
297
298 FUNCTION CF_MUN_PER_AMT_NUMFORMULA(STATUS IN VARCHAR2
299 ,MUN_AMT1 IN NUMBER) RETURN NUMBER IS
300 L_MUN_PER_AMT NUMBER;
301 BEGIN
302 L_MUN_PER_AMT := 0;
303 BEGIN
304 IF STATUS <> 'CANCELLED' THEN
305 L_MUN_PER_AMT := MUN_AMT1;
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 L_MUN_PER_AMT := 0;
310 END;
311 RETURN (L_MUN_PER_AMT);
312 END CF_MUN_PER_AMT_NUMFORMULA;
313
314 FUNCTION CF_EXC_AMT_NUMFORMULA(STATUS IN VARCHAR2
315 ,EXCS_AMT IN NUMBER) RETURN NUMBER IS
316 L_EXC_AMT NUMBER;
317 BEGIN
318 L_EXC_AMT := 0;
319 BEGIN
320 IF STATUS <> 'CANCELLED' THEN
321 L_EXC_AMT := EXCS_AMT;
322 END IF;
323 EXCEPTION
324 WHEN OTHERS THEN
325 L_EXC_AMT := 0;
326 END;
327 RETURN (L_EXC_AMT);
328 END CF_EXC_AMT_NUMFORMULA;
329
330 FUNCTION CF_TOT_DOC_AMTFORMULA(CF_TOT_DOC_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
331 L_CHAR_OPERATION_AMT VARCHAR2(15);
332 BEGIN
333 L_CHAR_OPERATION_AMT := FORMAT_CURRENCY(CF_TOT_DOC_AMT_NUM);
334 RETURN (L_CHAR_OPERATION_AMT);
335 END CF_TOT_DOC_AMTFORMULA;
336
337 FUNCTION CF_TAXABLE_AMTFORMULA(CF_TAXABLE_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
338 L_CHAR_TAXABLE_AMT VARCHAR2(15);
339 BEGIN
340 L_CHAR_TAXABLE_AMT := FORMAT_CURRENCY(CF_TAXABLE_AMT_NUM);
341 RETURN (L_CHAR_TAXABLE_AMT);
342 END CF_TAXABLE_AMTFORMULA;
343
344 FUNCTION CF_VAT_TAX_AMT_NUMFORMULA(STATUS IN VARCHAR2
345 ,VAT_TAX_AMT1 IN NUMBER) RETURN NUMBER IS
346 BEGIN
347 IF STATUS <> 'CANCELLED' THEN
348 RETURN (VAT_TAX_AMT1);
349 ELSE
350 RETURN (0);
351 END IF;
352 END CF_VAT_TAX_AMT_NUMFORMULA;
353
354 FUNCTION CF_VAT_TAX_AMTFORMULA(CF_VAT_TAX_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
355 L_VAT_TAX_AMT VARCHAR2(15);
356 BEGIN
357 L_VAT_TAX_AMT := FORMAT_CURRENCY(CF_VAT_TAX_AMT_NUM);
358 RETURN (L_VAT_TAX_AMT);
359 END CF_VAT_TAX_AMTFORMULA;
360
361 FUNCTION CF_EXMPT_AMTFORMULA(CF_EXMPT_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
362 L_CHAR_EXMPT_AMT VARCHAR2(15);
363 BEGIN
364 L_CHAR_EXMPT_AMT := FORMAT_CURRENCY(CF_EXMPT_AMT_NUM);
365 RETURN (L_CHAR_EXMPT_AMT);
366 END CF_EXMPT_AMTFORMULA;
367
371 L_CHAR_VAT_PER_AMT := FORMAT_CURRENCY(CF_VAT_PER_AMT_NUM);
368 FUNCTION CF_FED_PER_AMTFORMULA(CF_VAT_PER_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
369 L_CHAR_VAT_PER_AMT VARCHAR2(15);
370 BEGIN
372 RETURN (L_CHAR_VAT_PER_AMT);
373 END CF_FED_PER_AMTFORMULA;
374
375 FUNCTION CF_PRO_PER_AMTFORMULA(CF_PRO_PER_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
376 L_CHAR_PRO_PER_AMT VARCHAR2(15);
377 BEGIN
378 L_CHAR_PRO_PER_AMT := FORMAT_CURRENCY(CF_PRO_PER_AMT_NUM);
379 RETURN (L_CHAR_PRO_PER_AMT);
380 END CF_PRO_PER_AMTFORMULA;
381
382 FUNCTION CF_MUN_PER_AMTFORMULA(CF_MUN_PER_AMT_NUM IN NUMBER) RETURN VARCHAR2 IS
383 L_CHAR_MUN_PER_AMT VARCHAR2(15);
384 BEGIN
385 L_CHAR_MUN_PER_AMT := FORMAT_CURRENCY(CF_MUN_PER_AMT_NUM);
386 RETURN (L_CHAR_MUN_PER_AMT);
387 END CF_MUN_PER_AMTFORMULA;
388
389 FUNCTION CF_VAT_RATEFORMULA(TAX_RATE IN NUMBER) RETURN VARCHAR2 IS
390 L_RATE_CHAR VARCHAR2(4);
391 FORMAT_STRING VARCHAR2(5);
392 FORMATED_RATE VARCHAR2(4);
393 BEGIN
394 FORMAT_STRING := RPAD('0.'
395 ,4
396 ,'0');
397 FORMAT_STRING := LPAD(FORMAT_STRING
398 ,5
399 ,'0');
400 FORMATED_RATE := LPAD(REPLACE(LTRIM(RTRIM(TO_CHAR(ABS(TAX_RATE)
401 ,FORMAT_STRING)))
402 ,'.'
403 ,'')
404 ,4
405 ,'0');
406 RETURN (FORMATED_RATE);
407 EXCEPTION
408 WHEN OTHERS THEN
409 FORMATED_RATE := LPAD(REPLACE(TO_CHAR(ABS(ROUND(TAX_RATE
410 ,2)))
411 ,'.'
412 ,'')
413 ,4
414 ,'0');
415 RETURN (FORMATED_RATE);
416 END CF_VAT_RATEFORMULA;
417
418 FUNCTION CF_VAT_RATE_QTYFORMULA(TRX_ID IN NUMBER) RETURN VARCHAR2 IS
419 L_VAT_RATE_QTY NUMBER;
420 BEGIN
421 L_VAT_RATE_QTY := 1;
422 BEGIN
423 SELECT
424 COUNT(DET.TAX_RATE_ID)
425 INTO L_VAT_RATE_QTY
426 FROM
427 ZX_REP_TRX_DETAIL_T DET
428 WHERE DET.TRX_ID = CF_VAT_RATE_QTYFORMULA.TRX_ID
429 AND DET.TAX_TYPE_CODE in ( P_VAT_TAX_TYPE , P_NON_TAXABLE_TAX_TYPE );
430 EXCEPTION
431 WHEN OTHERS THEN
432 L_VAT_RATE_QTY := 1;
433 END;
434 RETURN (TO_CHAR(L_VAT_RATE_QTY));
435 END CF_VAT_RATE_QTYFORMULA;
436
437 FUNCTION CF_BLANK_CHRFORMULA RETURN VARCHAR2 IS
438 BEGIN
439 RETURN (' ');
440 END CF_BLANK_CHRFORMULA;
441
442 FUNCTION CF_REC_TYPE1FORMULA(NON_TAXABLE_AMOUNT1 IN NUMBER
443 ,CF_REC_COUNT IN NUMBER
444 ,STATUS IN VARCHAR2
445 ,DOC_DATE1 IN VARCHAR2
446 ,DGI_DOC_TYPE_CODE1 IN VARCHAR2
447 ,FISC_PRN1 IN VARCHAR2
448 ,CF_POINT_OF_SALE IN VARCHAR2
449 ,CF_DOC_NUM IN VARCHAR2
450 ,CUST_ISSUE_DTL1 IN VARCHAR2
451 ,CF_CUST_NUM_VALDIGIT IN VARCHAR2
452 ,SUPPLIER_DTL1 IN VARCHAR2
453 ,CAI_INFO1 IN VARCHAR2
454 ,TAX_RATE IN NUMBER
455 ,CF_TOT_DOC_AMT IN VARCHAR2
456 ,CF_EXMPT_AMT IN VARCHAR2
457 ,CF_VAT_PER_AMT IN VARCHAR2
458 ,CF_FED_PER_AMT IN VARCHAR2
459 ,CF_PRO_PER_AMT IN VARCHAR2
460 ,CF_MUN_PER_AMT IN VARCHAR2
461 ,CF_EXC_AMT IN VARCHAR2
462 ,SUP_VAT_REGN_STATUS_CODE1 IN VARCHAR2
463 ,CURRENCY_CODE1 IN VARCHAR2
464 ,CF_EXCHANGE_RATE IN VARCHAR2
465 ,CF_VAT_RATE_QTY IN VARCHAR2
466 ,DGI_TRANSACTION_CODE IN VARCHAR2
467 ,CF_TAXABLE_AMT IN VARCHAR2
468 ,CF_VAT_RATE IN VARCHAR2
469 ,CF_VAT_TAX_AMT IN VARCHAR2
470 ,CF_VAT_COUNT IN NUMBER
471 ,CF_DOC_AMT_CHR IN VARCHAR2
472 ,CF_NON_AMT_CHR IN VARCHAR2
473 ,CF_RATE_QTY IN VARCHAR2
474 ,CF_DGI_TRAN_CODE IN VARCHAR2) RETURN VARCHAR2 IS
475 L_REC_TYPE_1 VARCHAR2(369);
476 BEGIN
477 IF NVL(NON_TAXABLE_AMOUNT1
478 ,0) = 0 THEN
479 IF CF_REC_COUNT = CP_REC_COUNT THEN
480 BEGIN
481 IF STATUS = 'Y' THEN
482 L_REC_TYPE_1 := RPAD('1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 ||
483 CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || '000000000000000' || '000000000000000' || '000000000000000' || '0000' || '000000000000000' ||
484 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '00' || ' ' || '0000000000' || '0' || ' ' || CAI_INFO1
485 ,369
486 ,' ');
487 ELSE
488 BEGIN
489 IF TAX_RATE = 0 THEN
490 L_REC_TYPE_1 := RPAD('1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 || CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || CF_TOT_DOC_AMT || '000000000000000' ||
494 ,' ');
491 '000000000000000' || '0000' || '000000000000000' || CF_EXMPT_AMT || CF_VAT_PER_AMT || CF_FED_PER_AMT || CF_PRO_PER_AMT || CF_MUN_PER_AMT || CF_EXC_AMT || SUP_VAT_REGN_STATUS_CODE1 || CURRENCY_CODE1
492 || CF_EXCHANGE_RATE || CF_VAT_RATE_QTY || DGI_TRANSACTION_CODE || CAI_INFO1
493 ,369
495 ELSE
496 L_REC_TYPE_1 := RPAD('1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 || CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || CF_TOT_DOC_AMT || '000000000000000' ||
497 CF_TAXABLE_AMT || CF_VAT_RATE || CF_VAT_TAX_AMT || '000000000000000' || CF_VAT_PER_AMT || CF_FED_PER_AMT || CF_PRO_PER_AMT || CF_MUN_PER_AMT || CF_EXC_AMT || SUP_VAT_REGN_STATUS_CODE1 || CURRENCY_CODE1
498 || CF_EXCHANGE_RATE || CF_VAT_RATE_QTY || DGI_TRANSACTION_CODE || CAI_INFO1
499 ,369
500 ,' ');
501 END IF;
502 END;
503 END IF;
504 CP_TOT_REC := NVL(CP_TOT_REC
505 ,0) + 1;
506 CP_REC_COUNT := 1;
507 END;
508 ELSE
509 BEGIN
510 IF STATUS = 'Y' THEN
511 L_REC_TYPE_1 := RPAD('1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 || CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || '000000000000000' || '000000000000000' ||
512 '000000000000000' || '0000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '00' || ' ' || '0000000000' ||
513 '0' || ' ' || CAI_INFO1
514 ,369
515 ,' ');
516 ELSE
517 BEGIN
518 IF TAX_RATE = 0 THEN
519 L_REC_TYPE_1 := RPAD('1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 ||
520 CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || '000000000000000' || '000000000000000' || '000000000000000' || '0000' || '000000000000000'
521 || CF_EXMPT_AMT || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' ||
522 SUP_VAT_REGN_STATUS_CODE1 || CURRENCY_CODE1 || CF_EXCHANGE_RATE || CF_VAT_RATE_QTY || DGI_TRANSACTION_CODE || CAI_INFO1
523 ,369
524 ,' ');
525 ELSE
526 L_REC_TYPE_1 := RPAD('1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 ||
527 CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || '000000000000000' || '000000000000000' || CF_TAXABLE_AMT || CF_VAT_RATE || CF_VAT_TAX_AMT ||
528 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' ||
529 SUP_VAT_REGN_STATUS_CODE1 || CURRENCY_CODE1 || CF_EXCHANGE_RATE || CF_VAT_RATE_QTY || DGI_TRANSACTION_CODE || CAI_INFO1
530 ,369
531 ,' ');
532 END IF;
533 END;
534 END IF;
535 CP_REC_COUNT := NVL(CP_REC_COUNT
536 ,1) + 1;
537 CP_TOT_REC := NVL(CP_TOT_REC
538 ,0) + 1;
539 END;
540 END IF;
541 ELSE
542 IF STATUS = 'Y' THEN
543 L_REC_TYPE_1 := '1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 ||
544 CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || '000000000000000' || '000000000000000' || '000000000000000' || '0000' || '000000000000000' ||
545 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '00' ||
546 ' ' || '0000000000' || '0' || ' ' || CAI_INFO1 || RPAD(' '
547 ,75);
548 ELSE
549 BEGIN
550 IF CF_VAT_COUNT = 0 THEN
551 L_REC_TYPE_1 := '1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 ||
552 CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || CF_DOC_AMT_CHR || CF_NON_AMT_CHR || '000000000000000' || '0000' || '000000000000000' ||
553 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' ||
554 SUP_VAT_REGN_STATUS_CODE1 || CURRENCY_CODE1 || CF_EXCHANGE_RATE || CF_RATE_QTY || CF_DGI_TRAN_CODE || CAI_INFO1 || RPAD(' '
555 ,75);
556 ELSE
557 L_REC_TYPE_1 := '1' || DOC_DATE1 || DGI_DOC_TYPE_CODE1 || FISC_PRN1 || CF_POINT_OF_SALE || CF_DOC_NUM || CUST_ISSUE_DTL1 ||
558 CF_CUST_NUM_VALDIGIT || SUPPLIER_DTL1 || '000000000000000' || CF_NON_AMT_CHR || '000000000000000' || '0000' || '000000000000000' ||
559 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' ||
560 SUP_VAT_REGN_STATUS_CODE1 || CURRENCY_CODE1 || CF_EXCHANGE_RATE || CF_RATE_QTY || CF_DGI_TRAN_CODE || CAI_INFO1 || RPAD(' '
561 ,75);
562 END IF;
563 END;
564 END IF;
565 CP_NON_COUNT := NVL(CP_NON_COUNT
566 ,0) + 1;
567 END IF;
568 RETURN (L_REC_TYPE_1);
569 END CF_REC_TYPE1FORMULA;
570
571 FUNCTION CF_EXC_AMTFORMULA(CF_EXC_AMT_NUM IN NUMBER) RETURN CHAR IS
572 L_CHAR_EXC_AMT VARCHAR2(15);
573 BEGIN
574 L_CHAR_EXC_AMT := FORMAT_CURRENCY(CF_EXC_AMT_NUM);
575 RETURN (L_CHAR_EXC_AMT);
576 END CF_EXC_AMTFORMULA;
577
578 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
579 L_TAX_ID VARCHAR2(11);
580 BEGIN
581 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
582 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
583 BEGIN
584 P_LOCATION_ID := JG_ZZ_COMPANY_INFO.GET_LOCATION_ID;
585 EXCEPTION
586 WHEN OTHERS THEN
587 /*SRW.MESSAGE(02
588 ,'ERROR unknown Location ID')*/NULL;
592 SELECT
589 RAISE;
590 END;
591 BEGIN
593 RPAD(DECODE(HR.GLOBAL_ATTRIBUTE11
594 ,NULL
595 ,' '
596 ,SUBSTR(HR.GLOBAL_ATTRIBUTE11
597 ,1
598 ,10) || SUBSTR(HR.GLOBAL_ATTRIBUTE12
599 ,1
600 ,1))
601 ,11
602 ,' ')
603 INTO L_TAX_ID
604 FROM
605 HR_LOCATIONS_ALL HR
606 WHERE HR.LOCATION_ID = P_LOCATION_ID;
607 EXCEPTION
608 WHEN OTHERS THEN
609 L_TAX_ID := ' ';
610 END;
611 CP_COMP_TAX_ID := L_TAX_ID;
612 IF POPULATE_TRL <> TRUE THEN
613 NULL;
614 END IF;
615 RETURN (TRUE);
616 END BEFOREREPORT;
617
618 FUNCTION CF_REC_COUNTFORMULA RETURN NUMBER IS
619 L_COUNT NUMBER;
620 BEGIN
621 L_COUNT := 1;
622 RETURN (L_COUNT);
623 END CF_REC_COUNTFORMULA;
624
625 FUNCTION CF_TOT_RECFORMULA RETURN NUMBER IS
626 L_TOT NUMBER;
627 BEGIN
628 L_TOT := NVL(CP_TOT_REC
629 ,0) + NVL(CP_NON_COUNT
630 ,0);
631 RETURN (L_TOT);
632 END CF_TOT_RECFORMULA;
633
634 FUNCTION CF_VAT_AMTFORMULA(STATUS IN VARCHAR2
635 ,VAT_TAX_AMT1 IN NUMBER) RETURN NUMBER IS
636 L_VAT_AMT NUMBER;
637 BEGIN
638 L_VAT_AMT := 0;
639 IF STATUS <> 'CANCELLED' THEN
640 L_VAT_AMT := VAT_TAX_AMT1;
641 END IF;
642 RETURN (L_VAT_AMT);
643 END CF_VAT_AMTFORMULA;
644
645 FUNCTION CF_FED_PER_AMT_NUMFORMULA0004(STATUS IN VARCHAR2
646 ,FED_PER_AMT IN NUMBER) RETURN NUMBER IS
647 L_FED_PER_AMOUNT NUMBER;
648 BEGIN
649 L_FED_PER_AMOUNT := 0;
650 BEGIN
651 IF STATUS <> 'CANCELLED' THEN
652 L_FED_PER_AMOUNT := FED_PER_AMT;
653 ELSE
654 L_FED_PER_AMOUNT := 0;
655 END IF;
656 EXCEPTION
657 WHEN OTHERS THEN
658 L_FED_PER_AMOUNT := 0;
659 END;
660 RETURN (L_FED_PER_AMOUNT);
661 END CF_FED_PER_AMT_NUMFORMULA0004;
662
663 FUNCTION CF_FED_PER_AMTFORMULA0006(CF_FED_PER_AMT_NUM IN NUMBER) RETURN CHAR IS
664 L_CHAR_FED_PER_AMT VARCHAR2(15);
665 BEGIN
666 L_CHAR_FED_PER_AMT := FORMAT_CURRENCY(CF_FED_PER_AMT_NUM);
667 RETURN (L_CHAR_FED_PER_AMT);
668 END CF_FED_PER_AMTFORMULA0006;
669
670 FUNCTION CF_TOT_FED_PER_AMTFORMULA(CS_FED_PER_AMOUNT IN NUMBER) RETURN CHAR IS
671 L_CHAR_FED_PER_AMT VARCHAR2(15);
672 BEGIN
673 L_CHAR_FED_PER_AMT := FORMAT_CURRENCY(CS_FED_PER_AMOUNT);
674 RETURN (L_CHAR_FED_PER_AMT);
675 EXCEPTION
676 WHEN OTHERS THEN
677 RETURN ('000000000000000');
678 END CF_TOT_FED_PER_AMTFORMULA;
679
680 FUNCTION POPULATE_TRL RETURN BOOLEAN IS
681 BEGIN
682 /*SRW.MESSAGE('01'
683 ,'Call to TRL API : zx_extract_pkg.populate_tax_data')*/NULL;
684 ZX_EXTRACT_PKG.POPULATE_TAX_DATA(P_REPORTING_LEVEL => P_REPORTING_LEVEL
685 ,P_REPORTING_CONTEXT => P_REPORTING_ENTITY_ID
686 ,P_LEGAL_ENTITY_ID => P_LEGAL_ENTITY_ID
687 ,P_SUMMARY_LEVEL => 'TRANSACTION_LINE'
688 ,P_LEDGER_ID => P_SET_OF_BOOKS_ID
689 ,P_REGISTER_TYPE => 'TAX'
690 ,P_PRODUCT => 'AP'
691 ,P_MATRIX_REPORT => 'N'
692 ,P_CURRENCY_CODE_LOW => NULL
693 ,P_CURRENCY_CODE_HIGH => NULL
694 ,P_INCLUDE_AP_STD_TRX_CLASS => 'Y'
695 ,P_INCLUDE_AP_DM_TRX_CLASS => 'Y'
696 ,P_INCLUDE_AP_CM_TRX_CLASS => 'Y'
697 ,P_INCLUDE_AP_PREP_TRX_CLASS => 'Y'
698 ,P_INCLUDE_AP_MIX_TRX_CLASS => 'N'
699 ,P_INCLUDE_AP_EXP_TRX_CLASS => 'N'
700 ,P_INCLUDE_AP_INT_TRX_CLASS => 'N'
701 ,P_INCLUDE_AR_INV_TRX_CLASS => 'N'
702 ,P_INCLUDE_AR_APPL_TRX_CLASS => 'N'
703 ,P_INCLUDE_AR_ADJ_TRX_CLASS => 'N'
704 ,P_INCLUDE_AR_MISC_TRX_CLASS => 'N'
705 ,P_INCLUDE_AR_BR_TRX_CLASS => 'N'
706 ,P_INCLUDE_GL_MANUAL_LINES => 'N'
707 ,P_TRX_NUMBER_LOW => NULL
708 ,P_TRX_NUMBER_HIGH => NULL
709 ,P_EXTRACT_REPORT_LINE_NUMBER => NULL
710 ,P_AR_TRX_PRINTING_STATUS => NULL
711 ,P_AR_EXEMPTION_STATUS => NULL
712 ,P_GL_DATE_LOW => P_GL_DATE_FROM
713 ,P_GL_DATE_HIGH => P_GL_DATE_TO
714 ,P_TRX_DATE_LOW => NULL
715 ,P_TRX_DATE_HIGH => NULL
716 ,P_ACCOUNTING_STATUS => 'ACCOUNTED'
717 ,P_GL_PERIOD_NAME_LOW => NULL
718 ,P_GL_PERIOD_NAME_HIGH => NULL
719 ,P_TRX_DATE_PERIOD_NAME_LOW => NULL
720 ,P_TRX_DATE_PERIOD_NAME_HIGH => NULL
721 ,P_TAX_REGIME_CODE => NULL
722 ,P_TAX => NULL
726 ,P_TAX_TYPE_CODE_LOW => NULL
723 ,P_TAX_STATUS_CODE => NULL
724 ,P_TAX_RATE_CODE_LOW => NULL
725 ,P_TAX_RATE_CODE_HIGH => NULL
727 ,P_TAX_TYPE_CODE_HIGH => NULL
728 ,P_DOCUMENT_SUB_TYPE => NULL
729 ,P_TRX_BUSINESS_CATEGORY => NULL
730 ,P_TAX_INVOICE_DATE_LOW => NULL
731 ,P_TAX_INVOICE_DATE_HIGH => NULL
732 ,P_POSTING_STATUS => NULL
733 ,P_EXTRACT_ACCTED_TAX_LINES => NULL
734 ,P_INCLUDE_ACCOUNTING_SEGMENTS => NULL
735 ,P_BALANCING_SEGMENT_LOW => NULL
736 ,P_BALANCING_SEGMENT_HIGH => NULL
737 ,P_INCLUDE_DISCOUNTS => NULL
738 ,P_EXTRACT_STARTING_LINE_NUM => NULL
739 ,P_REQUEST_ID => P_CONC_REQUEST_ID
740 ,P_REPORT_NAME => 'JLARPPFF'
741 ,P_VAT_TRANSACTION_TYPE_CODE => NULL
742 ,P_INCLUDE_FULLY_NR_TAX_FLAG => 'Y'
743 ,P_MUNICIPAL_TAX_TYPE_CODE_LOW => P_MUN_TAX_TYPE_FROM
744 ,P_MUNICIPAL_TAX_TYPE_CODE_HIGH => P_MUN_TAX_TYPE_TO
745 ,P_PROV_TAX_TYPE_CODE_LOW => P_PRO_TAX_TYPE_FROM
746 ,P_PROV_TAX_TYPE_CODE_HIGH => P_PRO_TAX_TYPE_TO
747 ,P_EXCISE_TAX_TYPE_CODE_LOW => P_EXC_TAX_TYPE_FROM
748 ,P_EXCISE_TAX_TYPE_CODE_HIGH => P_EXC_TAX_TYPE_TO
749 ,P_NON_TAXABLE_TAX_TYPE_CODE => P_NON_TAXABLE_TAX_TYPE
750 ,P_PER_TAX_TYPE_CODE_LOW => P_PER_TAX_TYPE_FROM
751 ,P_PER_TAX_TYPE_CODE_HIGH => P_PER_TAX_TYPE_TO
752 ,P_VAT_TAX_TYPE_CODE => P_VAT_TAX_TYPE
753 ,P_EXCISE_TAX => NULL
754 ,P_VAT_ADDITIONAL_TAX => NULL
755 ,P_VAT_NON_TAXABLE_TAX => NULL
756 ,P_VAT_NOT_TAX => NULL
757 ,P_VAT_PERCEPTION_TAX => NULL
758 ,P_VAT_TAX => NULL
759 ,P_INC_SELF_WD_TAX => NULL
760 ,P_EXCLUDING_TRX_LETTER => P_EXCLUDED_TRX_LETTER
761 ,P_TRX_LETTER_LOW => NULL
762 ,P_TRX_LETTER_HIGH => NULL
763 ,P_INCLUDE_REFERENCED_SOURCE => NULL
764 ,P_PARTY_NAME => NULL
765 ,P_BATCH_NAME => NULL
766 ,P_BATCH_DATE_LOW => NULL
767 ,P_BATCH_DATE_HIGH => NULL
768 ,P_BATCH_SOURCE_ID => NULL
769 ,P_ADJUSTED_DOC_FROM => NULL
770 ,P_ADJUSTED_DOC_TO => NULL
771 ,P_STANDARD_VAT_TAX_RATE => NULL
772 ,P_MUNICIPAL_TAX => NULL
773 ,P_PROVINCIAL_TAX => NULL
774 ,P_TAX_ACCOUNT_LOW => NULL
775 ,P_TAX_ACCOUNT_HIGH => NULL
776 ,P_EXP_CERT_DATE_FROM => NULL
777 ,P_EXP_CERT_DATE_TO => NULL
778 ,P_EXP_METHOD => NULL
779 ,P_PRINT_COMPANY_INFO => 'Y'
780 ,P_REPRINT => 'N'
781 ,P_ERRBUF => P_ERRBUF
782 ,P_RETCODE => P_RETCODE);
783 IF P_RETCODE <> 0 THEN
784 /*SRW.MESSAGE('100'
785 ,'TRL: Return Code : ' || P_RETCODE)*/NULL;
786 /*SRW.MESSAGE('101'
787 ,'TRL: Error Buffer : ' || P_ERRBUF)*/NULL;
788 RETURN (FALSE);
789 ELSE
790 RETURN (TRUE);
791 END IF;
792 EXCEPTION
793 WHEN OTHERS THEN
794 /*SRW.MESSAGE('102'
795 ,SQLERRM)*/NULL;
796 RETURN (FALSE);
797 END POPULATE_TRL;
798
799 FUNCTION CF_DOC_AMT_NONFORMULA(STATUS IN VARCHAR2
800 ,DOC_AMT_NON1 IN NUMBER) RETURN NUMBER IS
801 L_TOT_DOC_AMT NUMBER;
802 BEGIN
803 L_TOT_DOC_AMT := 0;
804 BEGIN
805 IF STATUS <> 'CANCELLED' THEN
806 L_TOT_DOC_AMT := DOC_AMT_NON1;
807 END IF;
808 EXCEPTION
809 WHEN OTHERS THEN
810 L_TOT_DOC_AMT := 0;
811 END;
812 RETURN (L_TOT_DOC_AMT);
813 END CF_DOC_AMT_NONFORMULA;
814
815 FUNCTION CF_DOC_AMT_CHRFORMULA(CF_DOC_AMT_NON IN NUMBER) RETURN VARCHAR2 IS
816 L_NON_TAXABLE VARCHAR2(15);
817 BEGIN
818 L_NON_TAXABLE := FORMAT_CURRENCY(CF_DOC_AMT_NON);
819 RETURN (L_NON_TAXABLE);
820 END CF_DOC_AMT_CHRFORMULA;
821
822 FUNCTION CF_NON_BLANKFORMULA RETURN CHAR IS
823 BEGIN
824 RETURN (' ');
825 END CF_NON_BLANKFORMULA;
826
827 FUNCTION CF_NON_AMT_CHRFORMULA(NON_TAXABLE_AMOUNT1 IN NUMBER) RETURN CHAR IS
828 L_CHAR_NON_AMT VARCHAR2(15);
829 BEGIN
830 L_CHAR_NON_AMT := FORMAT_CURRENCY(NON_TAXABLE_AMOUNT1);
831 RETURN (L_CHAR_NON_AMT);
832 EXCEPTION
833 WHEN OTHERS THEN
834 RETURN ('000000000000000');
838 L_DGI_CODE VARCHAR2(1);
835 END CF_NON_AMT_CHRFORMULA;
836
837 FUNCTION CF_DGI_TRAN_CODEFORMULA(INVOICE_ID IN VARCHAR2) RETURN VARCHAR2 IS
839 BEGIN
840 L_DGI_CODE := ' ';
841 BEGIN
842 SELECT
843 NVL(SUBSTR(ATC.GLOBAL_ATTRIBUTE4
844 ,1
845 ,1)
846 ,' ')
847 INTO L_DGI_CODE
848 FROM
849 AP_TAX_CODES ATC,
850 AP_INVOICE_DISTRIBUTIONS AID
851 WHERE AID.INVOICE_ID = CF_DGI_TRAN_CODEFORMULA.INVOICE_ID
852 AND AID.LINE_TYPE_LOOKUP_CODE = 'TAX'
853 AND AID.TAX_CODE_ID = ATC.TAX_ID
854 AND ATC.TAX_TYPE = P_NON_TAXABLE_TAX_TYPE
855 AND ROWNUM = 1;
856 EXCEPTION
857 WHEN OTHERS THEN
858 L_DGI_CODE := ' ';
859 END;
860 RETURN (L_DGI_CODE);
861 END CF_DGI_TRAN_CODEFORMULA;
862
863 FUNCTION CF_VAT_COUNTFORMULA(INVOICE_ID IN VARCHAR2) RETURN NUMBER IS
864 L_COUNT NUMBER;
865 BEGIN
866 L_COUNT := 0;
867 BEGIN
868 SELECT
869 count(ATC.TAX_RATE)
870 INTO L_COUNT
871 FROM
872 AP_INVOICE_DISTRIBUTIONS LINES,
873 AP_TAX_CODES ATC
874 WHERE LINES.TAX_CODE_ID = ATC.TAX_ID
875 AND LINES.INVOICE_ID = CF_VAT_COUNTFORMULA.INVOICE_ID
876 AND ATC.TAX_TYPE = P_VAT_TAX_TYPE
877 AND LINES.LINE_TYPE_LOOKUP_CODE = 'TAX';
878 EXCEPTION
879 WHEN OTHERS THEN
880 L_COUNT := 0;
881 END;
882 RETURN (L_COUNT);
883 END CF_VAT_COUNTFORMULA;
884
885 FUNCTION CF_RATE_QTYFORMULA(INVOICE_ID IN VARCHAR2) RETURN VARCHAR2 IS
886 L_VAT_RATE_QTY NUMBER;
887 BEGIN
888 L_VAT_RATE_QTY := 1;
889 BEGIN
890 SELECT
891 COUNT(ATC.TAX_ID)
892 INTO L_VAT_RATE_QTY
893 FROM
894 AP_TAX_CODES ATC,
895 AP_INVOICE_DISTRIBUTIONS AID
896 WHERE AID.INVOICE_ID = CF_RATE_QTYFORMULA.INVOICE_ID
897 AND ATC.TAX_ID = AID.TAX_CODE_ID
898 AND AID.LINE_TYPE_LOOKUP_CODE = 'TAX'
899 AND ATC.TAX_TYPE in ( P_VAT_TAX_TYPE , P_NON_TAXABLE_TAX_TYPE );
900 EXCEPTION
901 WHEN OTHERS THEN
902 L_VAT_RATE_QTY := 1;
903 END;
904 RETURN (TO_CHAR(L_VAT_RATE_QTY));
905 END CF_RATE_QTYFORMULA;
906
907 FUNCTION CF_EXEMPT_AMOUNTFORMULA(EXMPT_AMT IN NUMBER) RETURN NUMBER IS
908 L_EXMPT_AMOUNT NUMBER;
909 L_COUNT NUMBER;
910 BEGIN
911 L_EXMPT_AMOUNT := 0;
912 L_COUNT := 0;
913 BEGIN
914 L_EXMPT_AMOUNT := EXMPT_AMT;
915 EXCEPTION
916 WHEN OTHERS THEN
917 L_EXMPT_AMOUNT := 0;
918 END;
919 RETURN (L_EXMPT_AMOUNT);
920 END CF_EXEMPT_AMOUNTFORMULA;
921
922 FUNCTION CF_VAT_PER_AMOUNTFORMULA(STATUS IN VARCHAR2
923 ,VAT_PER_AMT IN NUMBER) RETURN NUMBER IS
924 L_VAT_PER_AMOUNT NUMBER;
925 BEGIN
926 L_VAT_PER_AMOUNT := 0;
927 BEGIN
928 IF STATUS <> 'CANCELLED' THEN
929 L_VAT_PER_AMOUNT := VAT_PER_AMT;
930 END IF;
931 EXCEPTION
932 WHEN OTHERS THEN
933 L_VAT_PER_AMOUNT := 0;
934 END;
935 RETURN (L_VAT_PER_AMOUNT);
936 END CF_VAT_PER_AMOUNTFORMULA;
937
938 FUNCTION CF_FED_PER_AMOUNTFORMULA(STATUS IN VARCHAR2
939 ,FED_PER_AMT IN NUMBER) RETURN NUMBER IS
940 L_FED_PER_AMOUNT NUMBER;
941 BEGIN
942 L_FED_PER_AMOUNT := 0;
943 BEGIN
944 IF STATUS <> 'CANCELLED' THEN
945 L_FED_PER_AMOUNT := FED_PER_AMT;
946 ELSE
947 L_FED_PER_AMOUNT := 0;
948 END IF;
949 EXCEPTION
950 WHEN OTHERS THEN
951 L_FED_PER_AMOUNT := 0;
952 END;
953 RETURN (L_FED_PER_AMOUNT);
954 END CF_FED_PER_AMOUNTFORMULA;
955
956 FUNCTION CF_MUN_AMOUNTFORMULA(STATUS IN VARCHAR2
957 ,MUN_AMT1 IN NUMBER) RETURN NUMBER IS
958 L_MUN_AMT NUMBER;
959 BEGIN
960 L_MUN_AMT := 0;
961 BEGIN
962 IF STATUS <> 'CANCELLED' THEN
963 L_MUN_AMT := MUN_AMT1;
964 ELSE
965 L_MUN_AMT := 0;
966 END IF;
967 EXCEPTION
968 WHEN OTHERS THEN
969 L_MUN_AMT := 0;
970 END;
971 RETURN (L_MUN_AMT);
972 END CF_MUN_AMOUNTFORMULA;
973
974 FUNCTION CF_PRO_AMOUNTFORMULA(STATUS IN VARCHAR2
975 ,PRO_AMT IN NUMBER) RETURN NUMBER IS
976 L_PRO_PER_AMT NUMBER;
977 BEGIN
978 L_PRO_PER_AMT := 0;
979 BEGIN
980 IF STATUS <> 'CANCELLED' THEN
981 L_PRO_PER_AMT := PRO_AMT;
982 END IF;
983 EXCEPTION
984 WHEN OTHERS THEN
985 L_PRO_PER_AMT := 0;
986 END;
987 RETURN (L_PRO_PER_AMT);
988 END CF_PRO_AMOUNTFORMULA;
989
990 FUNCTION CF_EXCISE_AMOUNTFORMULA(STATUS IN VARCHAR2
991 ,EXCS_AMT IN NUMBER) RETURN NUMBER IS
992 L_EXC_AMT NUMBER;
993 BEGIN
994 L_EXC_AMT := 0;
995 BEGIN
996 IF STATUS <> 'CANCELLED' THEN
997 L_EXC_AMT := EXCS_AMT;
998 END IF;
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 L_EXC_AMT := 0;
1002 END;
1003 RETURN (L_EXC_AMT);
1004 END CF_EXCISE_AMOUNTFORMULA;
1005
1006 FUNCTION CF_NON_AMOUNTFORMULA(STATUS IN VARCHAR2
1007 ,NON_TAXABLE_AMOUNT1 IN NUMBER) RETURN NUMBER IS
1011 BEGIN
1008 L_NON_TAXABLE_AMT NUMBER;
1009 BEGIN
1010 L_NON_TAXABLE_AMT := 0;
1012 IF STATUS <> 'CANCELLED' THEN
1013 L_NON_TAXABLE_AMT := NON_TAXABLE_AMOUNT1;
1014 ELSE
1015 L_NON_TAXABLE_AMT := 0;
1016 END IF;
1017 EXCEPTION
1018 WHEN NO_DATA_FOUND THEN
1019 L_NON_TAXABLE_AMT := 0;
1020 END;
1021 RETURN (L_NON_TAXABLE_AMT);
1022 END CF_NON_AMOUNTFORMULA;
1023
1024 FUNCTION CF_TOT_VAT_AMOUNTFORMULA(STATUS IN VARCHAR2
1025 ,TOT_VAT1 IN NUMBER) RETURN NUMBER IS
1026 L_TOT_VAT NUMBER;
1027 BEGIN
1028 IF STATUS <> 'CANCELLED' THEN
1029 L_TOT_VAT := TOT_VAT1;
1030 END IF;
1031 RETURN (L_TOT_VAT);
1032 END CF_TOT_VAT_AMOUNTFORMULA;
1033
1034 FUNCTION CF_CUST_NUM_VALDIGITFORMULA(CUSTOM_ISSUE_NUM1 IN VARCHAR2) RETURN VARCHAR2 IS
1035 L_VALIDATION_DIGIT VARCHAR2(1);
1036 L_CUST_NUM VARCHAR2(6);
1037 BEGIN
1038 IF CUSTOM_ISSUE_NUM1 IS NULL THEN
1039 L_CUST_NUM := '000000';
1040 ELSE
1041 L_CUST_NUM := LPAD(SUBSTR(CUSTOM_ISSUE_NUM1
1042 ,1
1043 ,6)
1044 ,6
1045 ,'0');
1046 END IF;
1047 L_VALIDATION_DIGIT := NVL(SUBSTR(CUSTOM_ISSUE_NUM1
1048 ,7
1049 ,1)
1050 ,' ');
1051 RETURN (L_CUST_NUM || L_VALIDATION_DIGIT);
1052 END CF_CUST_NUM_VALDIGITFORMULA;
1053
1054 FUNCTION CF_FISCAL_PRINTERFORMULA RETURN CHAR IS
1055 L_PRINTER VARCHAR2(1);
1056 BEGIN
1057 RETURN (' ');
1058 END CF_FISCAL_PRINTERFORMULA;
1059
1060 FUNCTION CF_DOC_NUMFORMULA(INVOICE_ID IN VARCHAR2) RETURN CHAR IS
1061 L_DOC_NUM VARCHAR2(20);
1062 L_DOC_NUM1 VARCHAR2(20);
1063 L_SEPARATOR NUMBER;
1064 BEGIN
1065 L_SEPARATOR := INSTR(INVOICE_ID
1066 ,'-'
1067 ,1
1068 ,1);
1069 IF L_SEPARATOR = 0 THEN
1070 L_DOC_NUM := SUBSTR(RTRIM(LTRIM(INVOICE_ID))
1071 ,1
1072 ,20);
1073 ELSE
1074 L_DOC_NUM := SUBSTR(LTRIM(RTRIM(INVOICE_ID))
1075 ,L_SEPARATOR + 1
1076 ,20);
1077 END IF;
1078 L_DOC_NUM1 := TRANSLATE(UPPER(L_DOC_NUM)
1079 ,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-'
1080 ,'0123456789');
1081 RETURN (LPAD(L_DOC_NUM1
1082 ,20
1083 ,'0'));
1084 END CF_DOC_NUMFORMULA;
1085
1086 FUNCTION CF_EXCHANGE_RATEFORMULA(EXCHANGE_RATE1 IN NUMBER) RETURN CHAR IS
1087 L_FORMATED_EXCHG_RATE VARCHAR2(10);
1088 FORMAT_STRING VARCHAR2(11);
1089 BEGIN
1090 FORMAT_STRING := RPAD('0.'
1091 ,8
1092 ,'0');
1093 FORMAT_STRING := LPAD(FORMAT_STRING
1094 ,11
1095 ,'9');
1096 L_FORMATED_EXCHG_RATE := LPAD(REPLACE(LTRIM(RTRIM(TO_CHAR(ABS(EXCHANGE_RATE1)
1097 ,FORMAT_STRING)))
1098 ,'.'
1099 ,'')
1100 ,10
1101 ,'0');
1102 RETURN (L_FORMATED_EXCHG_RATE);
1103 END CF_EXCHANGE_RATEFORMULA;
1104
1105 FUNCTION CF_POINT_OF_SALEFORMULA(INVOICE_ID IN VARCHAR2) RETURN CHAR IS
1106 L_POS VARCHAR2(4);
1107 L_SEPARATOR NUMBER;
1108 L_DOC_NUM VARCHAR2(50);
1109 BEGIN
1110 L_DOC_NUM := TRANSLATE(UPPER(INVOICE_ID)
1111 ,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
1112 ,'0123456789');
1113 L_SEPARATOR := INSTR(INVOICE_ID
1114 ,'-'
1115 ,1
1116 ,1);
1117 IF L_SEPARATOR = 0 THEN
1118 L_POS := '0000';
1119 ELSE
1120 L_POS := LPAD(NVL(REPLACE(SUBSTR(L_DOC_NUM
1121 ,1
1122 ,L_SEPARATOR - 1)
1123 ,'-'
1124 ,'')
1125 ,'0')
1126 ,4
1127 ,'0');
1128 END IF;
1129 RETURN (L_POS);
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 RETURN ('0000');
1133 END CF_POINT_OF_SALEFORMULA;
1134
1135 FUNCTION CF_TOT_DOC_AMOUNTFORMULA RETURN NUMBER IS
1136 L_TOT_DOC_AMOUNT NUMBER;
1137 BEGIN
1138 L_TOT_DOC_AMOUNT := 0;
1139 RETURN L_TOT_DOC_AMOUNT;
1140 END CF_TOT_DOC_AMOUNTFORMULA;
1141
1142 FUNCTION CP_TOT_RECFORMULA RETURN NUMBER IS
1143 BEGIN
1144 NULL;
1145 END CP_TOT_RECFORMULA;
1146
1147 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
1148 BEGIN
1149 RETURN C_COMPANY_NAME_HEADER;
1150 END C_COMPANY_NAME_HEADER_P;
1151
1152 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1153 BEGIN
1154 RETURN C_BASE_CURRENCY_CODE;
1155 END C_BASE_CURRENCY_CODE_P;
1156
1157 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1158 BEGIN
1159 RETURN C_BASE_PRECISION;
1160 END C_BASE_PRECISION_P;
1161
1162 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1163 BEGIN
1164 RETURN C_BASE_MIN_ACCT_UNIT;
1165 END C_BASE_MIN_ACCT_UNIT_P;
1166
1167 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1168 BEGIN
1169 RETURN C_BASE_DESCRIPTION;
1170 END C_BASE_DESCRIPTION_P;
1171
1172 FUNCTION CP_COMP_TAX_ID_P RETURN VARCHAR2 IS
1173 BEGIN
1174 RETURN CP_COMP_TAX_ID;
1175 END CP_COMP_TAX_ID_P;
1176
1177 FUNCTION CP_REC_COUNT_P RETURN NUMBER IS
1178 BEGIN
1179 RETURN CP_REC_COUNT;
1180 END CP_REC_COUNT_P;
1181
1182 FUNCTION P_LOCATION_ID_P RETURN NUMBER IS
1183 BEGIN
1184 RETURN P_LOCATION_ID;
1185 END P_LOCATION_ID_P;
1186
1187 FUNCTION CP_TOT_REC_P RETURN NUMBER IS
1188 BEGIN
1189 RETURN CP_TOT_REC;
1190 END CP_TOT_REC_P;
1191
1192 FUNCTION CP_NON_COUNT_P RETURN NUMBER IS
1193 BEGIN
1194 RETURN CP_NON_COUNT;
1195 END CP_NON_COUNT_P;
1196
1197 END JL_JLARPPFF_XMLP_PKG;
1198
1199
1200