[Home] [Help]
PACKAGE BODY: APPS.JL_JLARTSFF_XMLP_PKG
Source
1 PACKAGE BODY JL_JLARTSFF_XMLP_PKG AS
2 /* $Header: JLARTSFFB.pls 120.2 2008/01/11 07:52:36 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 BEFOREREPORT RETURN BOOLEAN IS
48 L_NAME VARCHAR2(30);
49 L_TAX_ID VARCHAR2(30);
50 L_ORG_ID NUMBER(15);
51 L_VAT_ID NUMBER(15);
52 L_VATPERC_ID NUMBER(15);
53 L_VATADDL_ID NUMBER(15);
54 L_EXC_ID NUMBER(15);
55 L_VATNOT_ID NUMBER(15);
56 L_NON_TAX_ID NUMBER(15);
57 err number(10):=0;
58 BEGIN
59 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
60 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
61 L_VAT_ID := NULL;
62 BEGIN
63 SELECT
64 TAX_CATEGORY_ID
65 INTO L_VAT_ID
66 FROM
67 JL_ZZ_AR_TX_CATEGRY
68 WHERE TAX_CATEGORY = P_VAT
69 AND ROWNUM = 1;
70 EXCEPTION
71 WHEN OTHERS THEN
72 L_VAT_ID := NULL;
73 END;
74 err:=1;
75 L_VATPERC_ID := NULL;
76 BEGIN
77 SELECT
78 TAX_CATEGORY_ID
79 INTO L_VATPERC_ID
80 FROM
81 JL_ZZ_AR_TX_CATEGRY
82 WHERE TAX_CATEGORY = P_VATPERC
83 AND ROWNUM = 1;
84 EXCEPTION
85 WHEN OTHERS THEN
86 L_VATPERC_ID := NULL;
87 END;
88 err:=2;
89 L_VATADDL_ID := NULL;
90 BEGIN
91 SELECT
92 TAX_CATEGORY_ID
93 INTO L_VATADDL_ID
94 FROM
95 JL_ZZ_AR_TX_CATEGRY
96 WHERE TAX_CATEGORY = P_VATADDL
97 AND ROWNUM = 1;
98 EXCEPTION
99 WHEN OTHERS THEN
100 L_VATADDL_ID := NULL;
101 END;
102 err:=3;
103 L_EXC_ID := NULL;
104 BEGIN
105 SELECT
106 TAX_CATEGORY_ID
107 INTO L_EXC_ID
108 FROM
109 JL_ZZ_AR_TX_CATEGRY
110 WHERE TAX_CATEGORY = P_EXCISE
111 AND ROWNUM = 1;
112 EXCEPTION
113 WHEN OTHERS THEN
114 L_EXC_ID := NULL;
115 END;
116 err:=4;
117 L_VATNOT_ID := NULL;
118 BEGIN
119 SELECT
120 TAX_CATEGORY_ID
121 INTO L_VATNOT_ID
122 FROM
123 JL_ZZ_AR_TX_CATEGRY
124 WHERE TAX_CATEGORY = P_VATNOT
125 AND ROWNUM = 1;
126 EXCEPTION
127 WHEN OTHERS THEN
128 L_VATNOT_ID := NULL;
129 END;
130 err:=5;
131 L_NON_TAX_ID := NULL;
132 BEGIN
133 SELECT
134 TAX_CATEGORY_ID
135 INTO L_NON_TAX_ID
136 FROM
137 JL_ZZ_AR_TX_CATEGRY
138 WHERE TAX_CATEGORY = P_VAT_NON_TAXABLE
139 AND ROWNUM = 1;
140 EXCEPTION
141 WHEN OTHERS THEN
142 L_NON_TAX_ID := NULL;
143 END;
144 err:=6;
145 L_ORG_ID := OE_PROFILE.VALUE_WNPS('SO_ORGANIZATION_ID');
146 BEGIN
147 SELECT
148 RPAD(DECODE(HR.GLOBAL_ATTRIBUTE11
149 ,NULL
150 ,' '
151 ,SUBSTR(HR.GLOBAL_ATTRIBUTE11
152 ,1
153 ,10) || SUBSTR(HR.GLOBAL_ATTRIBUTE12
154 ,1
155 ,1))
156 ,11
157 ,' ')
158 INTO L_TAX_ID
159 FROM
160 HR_ORGANIZATION_UNITS HRU,
161 HR_LOCATIONS_ALL HR
162 WHERE HRU.ORGANIZATION_ID = L_ORG_ID
163 AND HRU.LOCATION_ID = HR.LOCATION_ID;
164 EXCEPTION
165 WHEN OTHERS THEN
166 L_TAX_ID := ' Test1 ';
167 END;
168 err:=7;
169 SELECT
170 TAXPAYER_ID
171 INTO L_TAX_ID
172 FROM
173 ZX_REP_CONTEXT_T
174 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
175 err:=8;
176 EXCEPTION
177 WHEN OTHERS THEN
178 -- RAISE_APPLICATION_ERROR(-20101, err || SQLERRM || ' ' ||P_CONC_REQUEST_ID);
179 L_TAX_ID := ' Test 2 ';
180 CP_COMP_TAX_ID := L_TAX_ID;
181 P_VAT_ID := L_VAT_ID;
182 P_VATADDL_ID := L_VATADDL_ID;
183 P_VATPERC_ID := L_VATPERC_ID;
184 P_EXC_ID := L_EXC_ID;
185 P_VATNOT_ID := L_VATNOT_ID;
186 P_NON_TAX_ID := L_NON_TAX_ID;
187 IF POPULATE_TRL <> TRUE THEN
188 NULL;
189 END IF;
190 BEGIN
191 SELECT
192 TAXPAYER_ID
193 INTO L_TAX_ID
194 FROM
195 ZX_REP_CONTEXT_T
196 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
197 EXCEPTION
198 WHEN OTHERS THEN
199 L_TAX_ID := ' ';
200 END;
201 CP_COMP_TAX_ID := L_TAX_ID;
202 RETURN (TRUE);
203 END BEFOREREPORT;
204
205 FUNCTION AFTERREPORT RETURN BOOLEAN IS
206 BEGIN
207 BEGIN
208 ZX_EXTRACT_PKG.PURGE(P_CONC_REQUEST_ID);
209 EXCEPTION
210 WHEN OTHERS THEN
211 NULL;
212 END;
213 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
214 RETURN (TRUE);
215 EXCEPTION
216 WHEN OTHERS THEN
217 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
218 END AFTERREPORT;
219
220 FUNCTION FORMAT_CURRENCY(P_AMOUNT IN NUMBER) RETURN VARCHAR2 IS
221 FORMAT_STRING VARCHAR2(16);
222 FORMATED_AMOUNT VARCHAR2(15);
223 BEGIN
224 FORMAT_STRING := RPAD('0.'
225 ,4
226 ,'0');
227 FORMAT_STRING := LPAD(FORMAT_STRING
228 ,16
229 ,'0');
230 FORMATED_AMOUNT := LPAD(REPLACE(LTRIM(RTRIM(TO_CHAR(ABS(P_AMOUNT)
231 ,FORMAT_STRING)))
232 ,'.'
233 ,'')
234 ,15
235 ,'0');
236 RETURN (FORMATED_AMOUNT);
237 EXCEPTION
238 WHEN OTHERS THEN
239 FORMATED_AMOUNT := LPAD(REPLACE(TO_CHAR(ABS(ROUND(P_AMOUNT
240 ,2)))
241 ,'.'
242 ,'')
243 ,15
244 ,'0');
245 RETURN FORMATED_AMOUNT;
246 END FORMAT_CURRENCY;
247
248 FUNCTION CF_TOT_DOC_FFORMULA(CS_TOT_DOC_AMT IN NUMBER) RETURN VARCHAR2 IS
249 L_CHAR_OPERATION_AMT VARCHAR2(15);
250 BEGIN
251 L_CHAR_OPERATION_AMT := FORMAT_CURRENCY(CS_TOT_DOC_AMT);
252 RETURN (L_CHAR_OPERATION_AMT);
253 EXCEPTION
254 WHEN OTHERS THEN
255 RETURN ('000000000000000');
256 END CF_TOT_DOC_FFORMULA;
257
258 FUNCTION CF_TOT_NON_TAXABLE_AMTFORMULA(CS_NON_TAXABLE_AMT IN NUMBER) RETURN VARCHAR2 IS
259 L_CHAR_NON_TAXABLE_AMT VARCHAR2(15);
260 BEGIN
261 L_CHAR_NON_TAXABLE_AMT := FORMAT_CURRENCY(CS_NON_TAXABLE_AMT);
262 RETURN (L_CHAR_NON_TAXABLE_AMT);
263 EXCEPTION
264 WHEN OTHERS THEN
265 RETURN ('000000000000000');
266 END CF_TOT_NON_TAXABLE_AMTFORMULA;
267
268 FUNCTION CF_TOT_TAXABLE_AMTFORMULA(CS_TAXABLE_AMT IN NUMBER) RETURN CHAR IS
269 L_CHAR_TAXABLE_AMT VARCHAR2(15);
270 BEGIN
271 L_CHAR_TAXABLE_AMT := FORMAT_CURRENCY(CS_TAXABLE_AMT);
272 RETURN (L_CHAR_TAXABLE_AMT);
273 EXCEPTION
274 WHEN OTHERS THEN
275 RETURN ('000000000000000');
276 END CF_TOT_TAXABLE_AMTFORMULA;
277
278 FUNCTION CF_TOT_VAT_TAX_AMTFORMULA(CS_VAT_TAX_AMT IN NUMBER) RETURN VARCHAR2 IS
279 L_VAT_TAX_AMT VARCHAR2(15);
280 BEGIN
281 L_VAT_TAX_AMT := FORMAT_CURRENCY(CS_VAT_TAX_AMT);
282 RETURN (L_VAT_TAX_AMT);
283 EXCEPTION
284 WHEN OTHERS THEN
285 RETURN ('000000000000000');
286 END CF_TOT_VAT_TAX_AMTFORMULA;
287
288 FUNCTION CF_TOT_NOT_REG_TAX_AMTFORMULA(CS_NOT_REG_TAX_AMT IN NUMBER) RETURN CHAR IS
289 L_CHAR_NOT_REG_TAX_AMT VARCHAR2(15);
290 BEGIN
291 L_CHAR_NOT_REG_TAX_AMT := FORMAT_CURRENCY(CS_NOT_REG_TAX_AMT);
292 RETURN (L_CHAR_NOT_REG_TAX_AMT);
293 EXCEPTION
294 WHEN OTHERS THEN
295 RETURN ('000000000000000');
296 END CF_TOT_NOT_REG_TAX_AMTFORMULA;
297
298 FUNCTION CF_TOT_FED_PER_AMTFORMULA(CS_FED_PER_AMT IN NUMBER) RETURN VARCHAR2 IS
299 L_CHAR_FED_PER_AMT VARCHAR2(15);
300 BEGIN
301 L_CHAR_FED_PER_AMT := FORMAT_CURRENCY(CS_FED_PER_AMT);
302 RETURN (L_CHAR_FED_PER_AMT);
303 EXCEPTION
304 WHEN OTHERS THEN
305 RETURN ('000000000000000');
306 END CF_TOT_FED_PER_AMTFORMULA;
307
308 FUNCTION CF_TOT_PRO_PER_AMTFORMULA(CS_PRO_PER_AMT IN NUMBER) RETURN CHAR IS
309 L_CHAR_PRO_PER_AMT VARCHAR2(15);
310 BEGIN
311 L_CHAR_PRO_PER_AMT := FORMAT_CURRENCY(CS_PRO_PER_AMT);
312 RETURN (L_CHAR_PRO_PER_AMT);
313 EXCEPTION
314 WHEN OTHERS THEN
315 RETURN ('000000000000000');
316 END CF_TOT_PRO_PER_AMTFORMULA;
317
318 FUNCTION CF_TOT_EXMPT_AMTFORMULA(CS_EXMPT_AMT IN NUMBER) RETURN VARCHAR2 IS
319 L_CHAR_EXMPT_AMT VARCHAR2(15);
320 BEGIN
321 L_CHAR_EXMPT_AMT := FORMAT_CURRENCY(CS_EXMPT_AMT);
322 RETURN (L_CHAR_EXMPT_AMT);
323 EXCEPTION
324 WHEN OTHERS THEN
325 RETURN ('000000000000000');
326 END CF_TOT_EXMPT_AMTFORMULA;
327
328 FUNCTION CF_TOT_MUN_PER_AMTFORMULA(CS_MUN_PER_AMT IN NUMBER) RETURN VARCHAR2 IS
329 L_CHAR_MUN_PER_AMT VARCHAR2(15);
330 BEGIN
331 L_CHAR_MUN_PER_AMT := FORMAT_CURRENCY(CS_MUN_PER_AMT);
332 RETURN (L_CHAR_MUN_PER_AMT);
333 EXCEPTION
334 WHEN OTHERS THEN
335 RETURN ('000000000000000');
336 END CF_TOT_MUN_PER_AMTFORMULA;
337
338 FUNCTION CF_TOT_EXC_AMTFORMULA(CS_EXC_AMT IN NUMBER) RETURN VARCHAR2 IS
339 L_CHAR_EXC_AMT VARCHAR2(15);
340 BEGIN
341 L_CHAR_EXC_AMT := FORMAT_CURRENCY(CS_EXC_AMT);
342 RETURN (L_CHAR_EXC_AMT);
343 EXCEPTION
344 WHEN OTHERS THEN
345 RETURN ('000000000000000');
346 END CF_TOT_EXC_AMTFORMULA;
347
348 FUNCTION CF_REC_TYPE_2FORMULA(CF_TOT_CNT IN NUMBER
349 ,CF_TOT_DOC_F IN VARCHAR2
350 ,CF_TOT_NON_TAXABLE_AMT IN VARCHAR2
351 ,CF_TOT_TAXABLE_AMT IN VARCHAR2
352 ,CF_TOT_VAT_TAX_AMT IN VARCHAR2
353 ,CF_TOT_NOT_REG_TAX_AMT IN VARCHAR2
354 ,CF_TOT_EXMPT_AMT IN VARCHAR2
355 ,CF_TOT_FED_PER_AMT IN VARCHAR2
356 ,CF_TOT_PRO_PER_AMT IN VARCHAR2
357 ,CF_TOT_MUN_PER_AMT IN VARCHAR2
358 ,CF_TOT_EXC_AMT IN VARCHAR2) RETURN VARCHAR2 IS
359 L_REC_TYPE_2 VARCHAR2(375);
360 L_TOT_DOC_F VARCHAR2(15);
361 L_TOT_NON_TAXABLE_AMT VARCHAR2(15);
362 L_TOT_VAT_TAX_AMT VARCHAR2(15);
363 BEGIN
364 L_REC_TYPE_2 := '2' || TO_CHAR(P_FROM_DATE
365 ,'YYYYMM') || RPAD(' '
366 ,29
367 ,' ') || LPAD(CF_TOT_CNT
368 ,12
369 ,'0') || RPAD(' '
370 ,10
371 ,' ') || CP_COMP_TAX_ID || RPAD(' '
372 ,30
373 ,' ') || CF_TOT_DOC_F || CF_TOT_NON_TAXABLE_AMT || CF_TOT_TAXABLE_AMT || RPAD(' '
374 ,4
375 ,' ') || CF_TOT_VAT_TAX_AMT || CF_TOT_NOT_REG_TAX_AMT || CF_TOT_EXMPT_AMT || CF_TOT_FED_PER_AMT || CF_TOT_PRO_PER_AMT || CF_TOT_MUN_PER_AMT || CF_TOT_EXC_AMT || RPAD(' '
376 ,75
377 ,' ');
378 RETURN (L_REC_TYPE_2);
379 END CF_REC_TYPE_2FORMULA;
380
381 FUNCTION CF_BLANK_SUMFORMULA RETURN CHAR IS
382 BEGIN
383 RETURN (' ');
384 END CF_BLANK_SUMFORMULA;
385
386 FUNCTION CF_TOT_CNTFORMULA RETURN NUMBER IS
387 L_TOT NUMBER;
388 BEGIN
389 L_TOT := CP_TOT + CP_NON_TOT;
390 RETURN (L_TOT);
391 END CF_TOT_CNTFORMULA;
392
393 FUNCTION POPULATE_TRL RETURN BOOLEAN IS
394 BEGIN
395 /*SRW.MESSAGE('01'
396 ,'TRL API Call : zx_extract_pkg.populate_tax_data ')*/NULL;
397 ZX_EXTRACT_PKG.POPULATE_TAX_DATA(P_REPORTING_LEVEL => P_REPORTING_LEVEL
398 ,P_REPORTING_CONTEXT => P_REPORTING_ENTITY_ID
399 ,P_LEGAL_ENTITY_ID => P_LEGAL_ENTITY_ID
400 ,P_SUMMARY_LEVEL => 'TRANSACTION_LINE'
401 ,P_LEDGER_ID => P_SET_OF_BOOKS_ID
402 ,P_REGISTER_TYPE => 'ALL'
403 ,P_PRODUCT => 'AR'
404 ,P_MATRIX_REPORT => 'N'
405 ,P_CURRENCY_CODE_LOW => NULL
406 ,P_CURRENCY_CODE_HIGH => NULL
407 ,P_INCLUDE_AP_STD_TRX_CLASS => 'N'
408 ,P_INCLUDE_AP_DM_TRX_CLASS => 'N'
409 ,P_INCLUDE_AP_CM_TRX_CLASS => 'N'
410 ,P_INCLUDE_AP_PREP_TRX_CLASS => 'N'
411 ,P_INCLUDE_AP_MIX_TRX_CLASS => 'N'
412 ,P_INCLUDE_AP_EXP_TRX_CLASS => 'N'
413 ,P_INCLUDE_AP_INT_TRX_CLASS => 'N'
414 ,P_INCLUDE_AR_INV_TRX_CLASS => 'Y'
415 ,P_INCLUDE_AR_APPL_TRX_CLASS => 'N'
416 ,P_INCLUDE_AR_ADJ_TRX_CLASS => 'N'
417 ,P_INCLUDE_AR_MISC_TRX_CLASS => 'N'
418 ,P_INCLUDE_AR_BR_TRX_CLASS => 'N'
419 ,P_INCLUDE_GL_MANUAL_LINES => 'N'
420 ,P_TRX_NUMBER_LOW => NULL
421 ,P_TRX_NUMBER_HIGH => NULL
422 ,P_EXTRACT_REPORT_LINE_NUMBER => NULL
423 ,P_AR_TRX_PRINTING_STATUS => NULL
424 ,P_AR_EXEMPTION_STATUS => NULL
425 ,P_GL_DATE_LOW => NULL
426 ,P_GL_DATE_HIGH => NULL
427 ,P_TRX_DATE_LOW => P_FROM_DATE
428 ,P_TRX_DATE_HIGH => P_TO_DATE
429 ,P_GL_PERIOD_NAME_LOW => NULL
430 ,P_GL_PERIOD_NAME_HIGH => NULL
431 ,P_TRX_DATE_PERIOD_NAME_LOW => NULL
432 ,P_TRX_DATE_PERIOD_NAME_HIGH => NULL
433 ,P_TAX_REGIME_CODE => P_TAX_REGIME
434 ,P_TAX => NULL
435 ,P_TAX_STATUS_CODE => NULL
436 ,P_TAX_RATE_CODE_LOW => NULL
437 ,P_TAX_RATE_CODE_HIGH => NULL
438 ,P_TAX_TYPE_CODE_LOW => NULL
439 ,P_TAX_TYPE_CODE_HIGH => NULL
440 ,P_DOCUMENT_SUB_TYPE => NULL
441 ,P_TRX_BUSINESS_CATEGORY => NULL
442 ,P_TAX_INVOICE_DATE_LOW => NULL
443 ,P_TAX_INVOICE_DATE_HIGH => NULL
444 ,P_POSTING_STATUS => NULL
445 ,P_EXTRACT_ACCTED_TAX_LINES => NULL
446 ,P_INCLUDE_ACCOUNTING_SEGMENTS => NULL
447 ,P_BALANCING_SEGMENT_LOW => NULL
448 ,P_BALANCING_SEGMENT_HIGH => NULL
449 ,P_INCLUDE_DISCOUNTS => NULL
450 ,P_EXTRACT_STARTING_LINE_NUM => NULL
451 ,P_REQUEST_ID => P_CONC_REQUEST_ID
452 ,P_REPORT_NAME => 'JLARTSFF'
453 ,P_VAT_TRANSACTION_TYPE_CODE => NULL
454 ,P_INCLUDE_FULLY_NR_TAX_FLAG => NULL
455 ,P_MUNICIPAL_TAX_TYPE_CODE_LOW => NULL
456 ,P_MUNICIPAL_TAX_TYPE_CODE_HIGH => NULL
457 ,P_PROV_TAX_TYPE_CODE_LOW => NULL
458 ,P_PROV_TAX_TYPE_CODE_HIGH => NULL
459 ,P_EXCISE_TAX_TYPE_CODE_LOW => NULL
460 ,P_EXCISE_TAX_TYPE_CODE_HIGH => NULL
461 ,P_NON_TAXABLE_TAX_TYPE_CODE => NULL
462 ,P_PER_TAX_TYPE_CODE_LOW => NULL
463 ,P_PER_TAX_TYPE_CODE_HIGH => NULL
464 ,P_VAT_TAX_TYPE_CODE => NULL
465 ,P_EXCISE_TAX => P_EXCISE
466 ,P_VAT_ADDITIONAL_TAX => P_VATADDL
467 ,P_VAT_NON_TAXABLE_TAX => P_VAT_NON_TAXABLE
468 ,P_VAT_NOT_TAX => P_VATNOT
469 ,P_VAT_PERCEPTION_TAX => P_VATPERC
470 ,P_VAT_TAX => P_VAT
471 ,P_INC_SELF_WD_TAX => NULL
472 ,P_EXCLUDING_TRX_LETTER => NULL
473 ,P_TRX_LETTER_LOW => NULL
474 ,P_TRX_LETTER_HIGH => NULL
475 ,P_INCLUDE_REFERENCED_SOURCE => NULL
476 ,P_PARTY_NAME => NULL
477 ,P_BATCH_NAME => NULL
478 ,P_BATCH_DATE_LOW => NULL
479 ,P_BATCH_DATE_HIGH => NULL
480 ,P_BATCH_SOURCE_ID => NULL
481 ,P_ADJUSTED_DOC_FROM => NULL
482 ,P_ADJUSTED_DOC_TO => NULL
483 ,P_STANDARD_VAT_TAX_RATE => NULL
484 ,P_MUNICIPAL_TAX => P_MUN_TAX_REGIME
485 ,P_PROVINCIAL_TAX => P_PRO_TAX_REGIME
486 ,P_TAX_ACCOUNT_LOW => NULL
487 ,P_TAX_ACCOUNT_HIGH => NULL
488 ,P_EXP_CERT_DATE_FROM => NULL
489 ,P_EXP_CERT_DATE_TO => NULL
490 ,P_EXP_METHOD => NULL
491 ,P_PRINT_COMPANY_INFO => 'Y'
492 ,P_REPRINT => 'N'
493 ,P_ERRBUF => P_ERRBUF
494 ,P_RETCODE => P_RETCODE);
495 IF P_RETCODE <> 0 THEN
496 /*SRW.MESSAGE('100'
497 ,'TRL: Return Code : ' || P_RETCODE)*/NULL;
498 /*SRW.MESSAGE('101'
499 ,'TRL: Error Buffer : ' || P_ERRBUF)*/NULL;
500 RETURN (FALSE);
501 ELSE
502 RETURN (TRUE);
503 END IF;
504 EXCEPTION
505 WHEN OTHERS THEN
506 /*SRW.MESSAGE('102'
507 ,SQLERRM)*/NULL;
508 RETURN (FALSE);
509 END POPULATE_TRL;
510
511 FUNCTION CF_DOC_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
512 ,DOC_AMT IN NUMBER) RETURN NUMBER IS
513 BEGIN
514 IF VOID_TRX IS NULL THEN
515 RETURN (DOC_AMT);
516 ELSE
517 RETURN (0);
518 END IF;
519 END CF_DOC_AMT_NUMFORMULA;
520
521 FUNCTION CF_NON_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
522 ,NON_TXBL_AMT IN NUMBER) RETURN NUMBER IS
523 BEGIN
524 IF VOID_TRX IS NULL THEN
525 RETURN (NON_TXBL_AMT);
526 ELSE
527 RETURN (0);
528 END IF;
529 END CF_NON_AMT_NUMFORMULA;
530
531 FUNCTION CF_TAXABLE_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
532 ,TXBL_AMT IN NUMBER) RETURN NUMBER IS
533 BEGIN
534 IF VOID_TRX IS NULL THEN
535 RETURN (TXBL_AMT);
536 ELSE
537 RETURN (0);
538 END IF;
539 END CF_TAXABLE_AMT_NUMFORMULA;
540
541 FUNCTION CF_VAT_TAX_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
542 ,VAT_AMOUNT IN NUMBER) RETURN NUMBER IS
543 BEGIN
544 IF VOID_TRX IS NULL THEN
545 RETURN (VAT_AMOUNT);
546 ELSE
547 RETURN (0);
548 END IF;
549 END CF_VAT_TAX_AMT_NUMFORMULA;
550
551 FUNCTION CF_NON_REG_TAX_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
552 ,NON_REG_AMT IN NUMBER) RETURN NUMBER IS
553 BEGIN
554 IF VOID_TRX IS NULL THEN
555 RETURN (NON_REG_AMT);
556 ELSE
557 RETURN (0);
558 END IF;
559 END CF_NON_REG_TAX_AMT_NUMFORMULA;
560
561 FUNCTION CF_EXMPT_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
562 ,EXMPT_AMT IN NUMBER) RETURN NUMBER IS
563 BEGIN
564 IF VOID_TRX IS NULL THEN
565 RETURN (EXMPT_AMT);
566 ELSE
567 RETURN (0);
568 END IF;
569 END CF_EXMPT_AMT_NUMFORMULA;
570
571 FUNCTION CF_FED_PER_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
572 ,FED_PERCEP_AMT IN NUMBER) RETURN NUMBER IS
573 BEGIN
574 IF VOID_TRX IS NULL THEN
575 RETURN (FED_PERCEP_AMT);
576 ELSE
577 RETURN (0);
578 END IF;
579 END CF_FED_PER_AMT_NUMFORMULA;
580
581 FUNCTION CF_PRO_PER_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
582 ,PROV_PRECEP_AMT IN NUMBER) RETURN NUMBER IS
583 BEGIN
584 IF VOID_TRX IS NULL THEN
585 RETURN (PROV_PRECEP_AMT);
586 ELSE
587 RETURN (0);
588 END IF;
589 END CF_PRO_PER_AMT_NUMFORMULA;
590
591 FUNCTION CF_MUN_PER_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
592 ,MUNIC_PERCEP_AMT IN NUMBER) RETURN NUMBER IS
593 BEGIN
594 IF VOID_TRX IS NULL THEN
595 RETURN (MUNIC_PERCEP_AMT);
596 ELSE
597 RETURN (0);
598 END IF;
599 END CF_MUN_PER_AMT_NUMFORMULA;
600
601 FUNCTION CF_EXC_AMT_NUMFORMULA(VOID_TRX IN VARCHAR2
602 ,EXCISE_AMT IN NUMBER) RETURN NUMBER IS
603 BEGIN
604 IF VOID_TRX IS NULL THEN
605 RETURN (EXCISE_AMT);
606 ELSE
607 RETURN (0);
608 END IF;
609 END CF_EXC_AMT_NUMFORMULA;
610
611 FUNCTION CF_DOC_AMT_CHRFORMULA(DOC_AMT IN NUMBER) RETURN CHAR IS
612 BEGIN
613 RETURN (FORMAT_CURRENCY(DOC_AMT));
614 EXCEPTION
615 WHEN OTHERS THEN
616 RETURN ('000000000000000');
617 END CF_DOC_AMT_CHRFORMULA;
618
619 FUNCTION CF_NON_AMT_CHRFORMULA(NON_TXBL_AMT IN NUMBER) RETURN CHAR IS
620 BEGIN
621 RETURN (FORMAT_CURRENCY(NON_TXBL_AMT));
622 EXCEPTION
623 WHEN OTHERS THEN
624 RETURN ('000000000000000');
625 END CF_NON_AMT_CHRFORMULA;
626
627 FUNCTION CF_TAXABLE_AMT_CHRFORMULA(TXBL_AMT IN NUMBER) RETURN CHAR IS
628 BEGIN
629 RETURN (FORMAT_CURRENCY(TXBL_AMT));
630 EXCEPTION
631 WHEN OTHERS THEN
632 RETURN ('000000000000000');
633 END CF_TAXABLE_AMT_CHRFORMULA;
634
635 FUNCTION CF_VAT_TAX_AMT_CHRFORMULA(VAT_AMOUNT IN NUMBER) RETURN CHAR IS
636 BEGIN
637 RETURN (FORMAT_CURRENCY(VAT_AMOUNT));
638 EXCEPTION
639 WHEN OTHERS THEN
640 RETURN ('000000000000000');
641 END CF_VAT_TAX_AMT_CHRFORMULA;
642
643 FUNCTION CF_NON_REG_TAX_AMT_CHRFORMULA(NON_REG_AMT IN NUMBER) RETURN CHAR IS
644 BEGIN
645 RETURN (FORMAT_CURRENCY(NON_REG_AMT));
646 EXCEPTION
647 WHEN OTHERS THEN
648 RETURN ('000000000000000');
649 END CF_NON_REG_TAX_AMT_CHRFORMULA;
650
651 FUNCTION CF_EXMPT_AMT_CHRFORMULA(EXMPT_AMT IN NUMBER) RETURN CHAR IS
652 BEGIN
653 RETURN (FORMAT_CURRENCY(EXMPT_AMT));
654 EXCEPTION
655 WHEN OTHERS THEN
656 RETURN ('000000000000000');
657 END CF_EXMPT_AMT_CHRFORMULA;
658
659 FUNCTION CF_FED_PER_AMT_CHRFORMULA(FED_PERCEP_AMT IN NUMBER) RETURN CHAR IS
660 BEGIN
661 RETURN (FORMAT_CURRENCY(FED_PERCEP_AMT));
662 EXCEPTION
663 WHEN OTHERS THEN
664 RETURN ('000000000000000');
665 END CF_FED_PER_AMT_CHRFORMULA;
666
667 FUNCTION CF_PRO_PER_AMT_CHRFORMULA(PROV_PRECEP_AMT IN NUMBER) RETURN CHAR IS
668 BEGIN
669 RETURN (FORMAT_CURRENCY(PROV_PRECEP_AMT));
670 EXCEPTION
671 WHEN OTHERS THEN
672 RETURN ('000000000000000');
673 END CF_PRO_PER_AMT_CHRFORMULA;
674
675 FUNCTION CF_MUN_PER_AMT_CHRFORMULA(MUNIC_PERCEP_AMT IN NUMBER) RETURN CHAR IS
676 BEGIN
677 RETURN (FORMAT_CURRENCY(MUNIC_PERCEP_AMT));
678 EXCEPTION
679 WHEN OTHERS THEN
680 RETURN ('000000000000000');
681 END CF_MUN_PER_AMT_CHRFORMULA;
682
683 FUNCTION CF_EXC_AMT_CHRFORMULA(EXCISE_AMT IN NUMBER) RETURN CHAR IS
684 BEGIN
685 RETURN (FORMAT_CURRENCY(EXCISE_AMT));
686 EXCEPTION
687 WHEN OTHERS THEN
688 RETURN ('000000000000000');
689 END CF_EXC_AMT_CHRFORMULA;
690
691 FUNCTION CF_EXCHANGE_RATEFORMULA(EXCHANGE_RATE IN NUMBER) RETURN CHAR IS
692 L_FORMATED_EXCHG_RATE VARCHAR2(10);
693 FORMAT_STRING VARCHAR2(11);
694 BEGIN
695 FORMAT_STRING := RPAD('0.'
696 ,8
697 ,'0');
698 FORMAT_STRING := LPAD(FORMAT_STRING
699 ,11
700 ,'9');
701 L_FORMATED_EXCHG_RATE := LPAD(REPLACE(LTRIM(RTRIM(TO_CHAR(ABS(EXCHANGE_RATE)
702 ,FORMAT_STRING)))
703 ,'.'
704 ,'')
705 ,10
706 ,'0');
707 RETURN L_FORMATED_EXCHG_RATE;
708 END CF_EXCHANGE_RATEFORMULA;
709
710 FUNCTION CF_REC_COUNTFORMULA(CUSTOMER_TRX_ID IN NUMBER) RETURN NUMBER IS
711 L_VAT_COUNT NUMBER;
712 BEGIN
713 SELECT
714 count(TAX_RATE)
715 INTO L_VAT_COUNT
716 FROM
717 ZX_REP_TRX_DETAIL_T
718 WHERE REQUEST_ID = P_CONC_REQUEST_ID
719 AND TRX_ID = CUSTOMER_TRX_ID
720 AND TAX = P_VAT;
721 RETURN (L_VAT_COUNT);
722 END CF_REC_COUNTFORMULA;
723
724 FUNCTION CF_VAT_NON_RECFORMULA(NON_TXBL_AMT IN NUMBER
725 ,VOID_TRX IN VARCHAR2
726 ,DOC_DATE IN VARCHAR2
727 ,DGI_CODES IN VARCHAR2
728 ,FISCAL_PRINTER IN VARCHAR2
729 ,STRING1 IN VARCHAR2
730 ,CAI_INFO IN VARCHAR2
731 ,DOC_VOID_DATE IN VARCHAR2
732 ,CF_REC_COUNT IN NUMBER
733 ,CF_DOC_AMT_CHR IN VARCHAR2
734 ,CF_NON_AMT_CHR IN VARCHAR2
735 ,CUST_VAT_REG_CODE IN VARCHAR2
736 ,CUR_CODE IN VARCHAR2
737 ,CF_EXCHANGE_RATE IN VARCHAR2
738 ,VAT_RATE_QTY IN NUMBER
739 ,DGI_TRX_CODE IN VARCHAR2
740 ,VAT_RATE IN NUMBER
741 ,CF_EXMPT_AMT_CHR IN VARCHAR2
742 ,CF_TAXABLE_AMT_CHR IN VARCHAR2
743 ,CF_VAT_RATE IN VARCHAR2
744 ,CF_VAT_TAX_AMT_CHR IN VARCHAR2
745 ,CF_NON_REG_TAX_AMT_CHR IN VARCHAR2
746 ,CF_FED_PER_AMT_CHR IN VARCHAR2
747 ,CF_PRO_PER_AMT_CHR IN VARCHAR2
748 ,CF_MUN_PER_AMT_CHR IN VARCHAR2
749 ,CF_EXC_AMT_CHR IN VARCHAR2) RETURN CHAR IS
750 L_REC_TYPE_1 VARCHAR2(375);
751 BEGIN
752 IF NON_TXBL_AMT <> 0 THEN
753 BEGIN
754 IF VOID_TRX IS NOT NULL THEN
755 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || '000000000000000' || '000000000000000' || '000000000000000' || '0000' || '000000000000000' || '000000000000000' || '000000000000000' ||
756 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '00' || ' ' || '0000000000' || '0' || ' ' || CAI_INFO || DOC_VOID_DATE
757 ,375
758 ,' ');
759 ELSIF CF_REC_COUNT = 0 THEN
760 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || CF_DOC_AMT_CHR || CF_NON_AMT_CHR || '000000000000000' || '0000' || '000000000000000' || '000000000000000' || '000000000000000' ||
761 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || CUST_VAT_REG_CODE || CUR_CODE || CF_EXCHANGE_RATE || VAT_RATE_QTY || DGI_TRX_CODE || CAI_INFO || DOC_VOID_DATE
762 ,375
763 ,' ');
764 ELSE
765 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || '000000000000000' || CF_NON_AMT_CHR || '000000000000000' || '0000' || '000000000000000' || '000000000000000' || '000000000000000' ||
766 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || CUST_VAT_REG_CODE || CUR_CODE || CF_EXCHANGE_RATE || VAT_RATE_QTY || DGI_TRX_CODE || CAI_INFO || DOC_VOID_DATE
767 ,375
768 ,' ');
769 END IF;
770 CP_NON_TOT := NVL(CP_NON_TOT
771 ,0) + 1;
772 RETURN (L_REC_TYPE_1);
773 END;
774 ELSE
775 IF CF_REC_COUNT = CP_REC_COUNT THEN
776 IF VOID_TRX IS NOT NULL THEN
777 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || '000000000000000' || '000000000000000' ||
778 '000000000000000' || '0000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' ||
779 '000000000000000' || '00' || ' ' || '0000000000' || '0' || ' ' || CAI_INFO || DOC_VOID_DATE
780 ,375
781 ,' ');
782 ELSIF VAT_RATE = 0 THEN
783 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || CF_DOC_AMT_CHR || '000000000000000' || '000000000000000' ||
784 VAT_RATE || '000000000000000' || '000000000000000' || CF_EXMPT_AMT_CHR || '000000000000000' || '000000000000000' || '000000000000000' ||
785 '000000000000000' || CUST_VAT_REG_CODE || CUR_CODE || CF_EXCHANGE_RATE || VAT_RATE_QTY || DGI_TRX_CODE || CAI_INFO || DOC_VOID_DATE
786 ,375
787 ,' ');
788 ELSE
789 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || CF_DOC_AMT_CHR || '000000000000000' || CF_TAXABLE_AMT_CHR || CF_VAT_RATE || CF_VAT_TAX_AMT_CHR || CF_NON_REG_TAX_AMT_CHR ||
790 '000000000000000' || CF_FED_PER_AMT_CHR || CF_PRO_PER_AMT_CHR || CF_MUN_PER_AMT_CHR || CF_EXC_AMT_CHR || CUST_VAT_REG_CODE || CUR_CODE || CF_EXCHANGE_RATE || VAT_RATE_QTY || DGI_TRX_CODE || CAI_INFO || DOC_VOID_DATE
791 ,375
792 ,' ');
793 END IF;
794 CP_TOT := NVL(CP_TOT
795 ,0) + 1;
796 CP_REC_COUNT := 1;
797 ELSE
798 IF VOID_TRX IS NOT NULL THEN
799 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || '000000000000000' || '000000000000000' || '000000000000000' || '0000' || '000000000000000' || '000000000000000' || '000000000000000' ||
800 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '00' || ' ' || '0000000000' || '0' || ' ' || CAI_INFO || DOC_VOID_DATE
801 ,375
802 ,' ');
803 ELSIF VAT_RATE = 0 THEN
804 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || '000000000000000' || '000000000000000' || '000000000000000' || VAT_RATE || '000000000000000' || '000000000000000'
805 || CF_EXMPT_AMT_CHR || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || CUST_VAT_REG_CODE || CUR_CODE || CF_EXCHANGE_RATE || VAT_RATE_QTY || DGI_TRX_CODE || CAI_INFO || DOC_VOID_DATE
806 ,375
807 ,' ');
808 ELSE
809 L_REC_TYPE_1 := RPAD('1' || DOC_DATE || DGI_CODES || FISCAL_PRINTER || STRING1 || '000000000000000' || '000000000000000' || CF_TAXABLE_AMT_CHR || CF_VAT_RATE || CF_VAT_TAX_AMT_CHR || '000000000000000' ||
810 '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || '000000000000000' || CUST_VAT_REG_CODE || CUR_CODE || CF_EXCHANGE_RATE || VAT_RATE_QTY || DGI_TRX_CODE || CAI_INFO || DOC_VOID_DATE
811 ,375
812 ,' ');
813 END IF;
814 CP_REC_COUNT := NVL(CP_REC_COUNT
815 ,0) + 1;
816 CP_TOT := NVL(CP_TOT
817 ,0) + 1;
818 END IF;
819 RETURN (L_REC_TYPE_1);
820 END IF;
821 END CF_VAT_NON_RECFORMULA;
822
823 FUNCTION CF_BLANK_CHRFORMULA RETURN CHAR IS
824 BEGIN
825 RETURN (' ');
826 END CF_BLANK_CHRFORMULA;
827
828 FUNCTION CP_REC_COUNTFORMULA RETURN NUMBER IS
829 BEGIN
830 NULL;
831 END CP_REC_COUNTFORMULA;
832
833 FUNCTION CF_VAT_RATEFORMULA(VAT_RATE IN NUMBER) RETURN CHAR IS
834 L_RATE_CHAR VARCHAR2(4);
835 FORMAT_STRING VARCHAR2(5);
836 FORMATED_RATE VARCHAR2(4);
837 BEGIN
838 FORMAT_STRING := RPAD('0.'
839 ,4
840 ,'0');
841 FORMAT_STRING := LPAD(FORMAT_STRING
842 ,5
843 ,'0');
844 FORMATED_RATE := LPAD(REPLACE(LTRIM(RTRIM(TO_CHAR(ABS(VAT_RATE)
845 ,FORMAT_STRING)))
846 ,'.'
847 ,'')
848 ,4
849 ,'0');
850 RETURN (FORMATED_RATE);
851 EXCEPTION
852 WHEN OTHERS THEN
853 FORMATED_RATE := LPAD(REPLACE(TO_CHAR(ABS(ROUND(VAT_RATE
854 ,2)))
855 ,'.'
856 ,'')
857 ,4
858 ,'0');
859 RETURN (FORMATED_RATE);
860 END CF_VAT_RATEFORMULA;
861
862 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
863 BEGIN
864 RETURN C_COMPANY_NAME_HEADER;
865 END C_COMPANY_NAME_HEADER_P;
866
867 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
868 BEGIN
869 RETURN C_BASE_CURRENCY_CODE;
870 END C_BASE_CURRENCY_CODE_P;
871
872 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
873 BEGIN
874 RETURN C_BASE_PRECISION;
875 END C_BASE_PRECISION_P;
876
877 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
878 BEGIN
879 RETURN C_BASE_MIN_ACCT_UNIT;
880 END C_BASE_MIN_ACCT_UNIT_P;
881
882 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
883 BEGIN
884 RETURN C_BASE_DESCRIPTION;
885 END C_BASE_DESCRIPTION_P;
886
887 FUNCTION CP_COMP_TAX_ID_P RETURN VARCHAR2 IS
888 BEGIN
889 RETURN CP_COMP_TAX_ID;
890 END CP_COMP_TAX_ID_P;
891
892 FUNCTION CP_REC_COUNT_P RETURN NUMBER IS
893 BEGIN
894 RETURN CP_REC_COUNT;
895 END CP_REC_COUNT_P;
896
897 FUNCTION P_VAT_ID_P RETURN NUMBER IS
898 BEGIN
899 RETURN P_VAT_ID;
900 END P_VAT_ID_P;
901
902 FUNCTION P_VATADDL_ID_P RETURN NUMBER IS
903 BEGIN
904 RETURN P_VATADDL_ID;
905 END P_VATADDL_ID_P;
906
907 FUNCTION P_VATPERC_ID_P RETURN NUMBER IS
908 BEGIN
909 RETURN P_VATPERC_ID;
910 END P_VATPERC_ID_P;
911
912 FUNCTION P_EXC_ID_P RETURN NUMBER IS
913 BEGIN
914 RETURN P_EXC_ID;
915 END P_EXC_ID_P;
916
917 FUNCTION P_VATNOT_ID_P RETURN NUMBER IS
918 BEGIN
919 RETURN P_VATNOT_ID;
920 END P_VATNOT_ID_P;
921
922 FUNCTION P_NON_TAX_ID_P RETURN NUMBER IS
923 BEGIN
924 RETURN P_NON_TAX_ID;
925 END P_NON_TAX_ID_P;
926
927 FUNCTION CP_TOT_P RETURN NUMBER IS
928 BEGIN
929 RETURN CP_TOT;
930 END CP_TOT_P;
931
932 FUNCTION CP_NON_TOT_P RETURN NUMBER IS
933 BEGIN
934 RETURN CP_NON_TOT;
935 END CP_NON_TOT_P;
936
937 END JL_JLARTSFF_XMLP_PKG;
938
939
940