[Home] [Help]
PACKAGE BODY: APPS.JL_JLBRRINV_XMLP_PKG
Source
1 PACKAGE BODY JL_JLBRRINV_XMLP_PKG AS
2 /* $Header: JLBRRINVB.pls 120.1 2007/12/25 16:41:59 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN OTHERS THEN
9 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
10 END;
11 RETURN (TRUE);
12 END AFTERREPORT;
13 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
14 BEGIN
15 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
16 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
17 BEGIN
18 /*SRW.USER_EXIT('FND GETPROFILE
19 NAME="ORG_ID"
20 FIELD="H_OPERATION_UNIT"')*/NULL;
21 FND_PROFILE.GET('ORG_ID',H_OPERATION_UNIT);
22 EXCEPTION
23 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
24 /*SRW.MESSAGE(20
25 ,'Failed in Profile Options Init.')*/NULL;
26 RAISE;
27 END;
28 BEGIN
29 H_SO_ORGANIZATION := FND_PROFILE.VALUE('SO_ORGANIZATION_ID');
30 EXCEPTION
31 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
32 /*SRW.MESSAGE(20
33 ,'Failed in Profile Options Init.')*/NULL;
34 RAISE;
35 END;
36 IF H_SO_ORGANIZATION IS NULL THEN
37 H_SO_ORGANIZATION := H_OPERATION_UNIT;
38 END IF;
39 GET_BASE_CURR_DATA;
40 IF (SET_P_WHERE <> TRUE) THEN
41 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
42 END IF;
43 RETURN (TRUE);
44 END BEFOREREPORT;
45 PROCEDURE GET_BASE_CURR_DATA IS
46 BASE_CURR FND_CURRENCIES_VL.CURRENCY_CODE%TYPE;
47 PREC FND_CURRENCIES_VL.PRECISION%TYPE;
48 BEGIN
49 BASE_CURR := '';
50 PREC := 0;
51 BEGIN
52 SELECT
53 FCURR.CURRENCY_CODE,
54 FCURR.PRECISION
55 INTO BASE_CURR,PREC
56 FROM
57 AR_SYSTEM_PARAMETERS ASP,
58 FND_CURRENCIES_VL FCURR,
59 GL_SETS_OF_BOOKS GSBKS
60 WHERE ASP.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
61 AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 RAISE_ERR('200XX'
65 ,'JL_AR_FA_CURR_DET_NOT_DEFINED');
66 WHEN OTHERS THEN
67 RAISE_ORA_ERR('200XX');
68 END;
69 CP_BASE_CURRENCY_CODE := BASE_CURR;
70 CP_BASE_PRECISION := PREC;
71 END GET_BASE_CURR_DATA;
72 PROCEDURE RAISE_ERR(ERRNO IN VARCHAR2
73 ,MSGNAME IN VARCHAR2) IS
74 ERRMSG VARCHAR2(1000);
75 BEGIN
76 SET_NAME('JL'
77 ,MSGNAME);
78 ERRMSG := GET;
79 /*SRW.MESSAGE(ERRNO
80 ,ERRMSG)*/NULL;
81 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
82 END RAISE_ERR;
83 PROCEDURE RAISE_ORA_ERR(ERRNO IN VARCHAR2) IS
84 ERRMSG VARCHAR2(1000);
85 BEGIN
86 ERRMSG := SQLERRM;
87 /*SRW.MESSAGE(ERRNO
88 ,ERRMSG)*/NULL;
89 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
90 END RAISE_ORA_ERR;
91 FUNCTION CF_ICMS_TAXFORMULA(C_DTL_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
92 X_ICMS_TAX_RATE NUMBER;
93 X_ICMS_TAX_AMOUNT NUMBER;
94 X_ICMS_TAX_BASE_AMOUNT NUMBER;
95 BEGIN
96 BEGIN
97 SELECT
98 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
99 ,'Y'
100 ,RACTL.EXTENDED_AMOUNT
101 ,0)),
102 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
103 ,'Y'
104 ,NVL(RACTL.TAX_RATE
105 ,0)
106 ,0)),
107 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
108 ,'Y'
109 ,FND_NUMBER.CANONICAL_TO_NUMBER(RACTL.GLOBAL_ATTRIBUTE11)
110 ,0))
111 INTO X_ICMS_TAX_AMOUNT,X_ICMS_TAX_RATE,X_ICMS_TAX_BASE_AMOUNT
112 FROM
113 RA_CUSTOMER_TRX_LINES RACTL,
114 AR_VAT_TAX ARVT
115 WHERE RACTL.LINE_TYPE = 'TAX'
116 AND RACTL.LINK_TO_CUST_TRX_LINE_ID = C_DTL_TRX_LINE_ID
117 AND ARVT.VAT_TAX_ID = RACTL.VAT_TAX_ID
118 AND TO_NUMBER(ARVT.GLOBAL_ATTRIBUTE1) = 2;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 NULL;
122 WHEN OTHERS THEN
123 NULL;
124 END;
125 CP_ICMS_TAX_BASE_AMOUNT := X_ICMS_TAX_BASE_AMOUNT;
126 CP_ICMS_TAX_AMOUNT := X_ICMS_TAX_AMOUNT;
127 RETURN (X_ICMS_TAX_RATE);
128 END CF_ICMS_TAXFORMULA;
129 FUNCTION CF_ISS_TAX_AMOUNTFORMULA(C_DTL_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
130 X_ISS_TAX_AMOUNT NUMBER;
131 BEGIN
132 BEGIN
133 SELECT
134 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
135 ,'Y'
136 ,RACTL.EXTENDED_AMOUNT
137 ,0))
138 INTO X_ISS_TAX_AMOUNT
139 FROM
140 RA_CUSTOMER_TRX_LINES RACTL,
141 AR_VAT_TAX ARVT
142 WHERE RACTL.LINE_TYPE = 'TAX'
143 AND RACTL.LINK_TO_CUST_TRX_LINE_ID = C_DTL_TRX_LINE_ID
144 AND ARVT.VAT_TAX_ID = RACTL.VAT_TAX_ID
145 AND TO_NUMBER(ARVT.GLOBAL_ATTRIBUTE1) = 5;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 NULL;
149 WHEN OTHERS THEN
150 NULL;
151 END;
152 RETURN (NVL(X_ISS_TAX_AMOUNT,0));
153 END CF_ISS_TAX_AMOUNTFORMULA;
154 FUNCTION CF_IRJ_TAX_AMOUNTFORMULA(C_DTL_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
155 X_IRJ_TAX_AMOUNT NUMBER;
156 BEGIN
157 BEGIN
158 SELECT
159 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
160 ,'Y'
161 ,RACTL.EXTENDED_AMOUNT
162 ,0))
163 INTO X_IRJ_TAX_AMOUNT
164 FROM
165 RA_CUSTOMER_TRX_LINES RACTL,
166 AR_VAT_TAX ARVT
167 WHERE RACTL.LINE_TYPE = 'TAX'
168 AND RACTL.LINK_TO_CUST_TRX_LINE_ID = C_DTL_TRX_LINE_ID
169 AND ARVT.VAT_TAX_ID = RACTL.VAT_TAX_ID
170 AND TO_NUMBER(ARVT.GLOBAL_ATTRIBUTE1) = 4;
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 NULL;
174 WHEN OTHERS THEN
175 NULL;
176 END;
177 RETURN (X_IRJ_TAX_AMOUNT);
178 END CF_IRJ_TAX_AMOUNTFORMULA;
179 FUNCTION CF_ICMSST_TAX_AMOUNTFORMULA(C_DTL_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
180 X_ICMSST_TAX_AMOUNT NUMBER;
181 X_ICMSST_TAX_BASE_AMOUNT NUMBER;
182 BEGIN
183 BEGIN
184 SELECT
185 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
186 ,'Y'
187 ,RACTL.EXTENDED_AMOUNT
188 ,0)),
189 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
190 ,'Y'
191 ,FND_NUMBER.CANONICAL_TO_NUMBER(RACTL.GLOBAL_ATTRIBUTE11)
192 ,0))
193 INTO X_ICMSST_TAX_AMOUNT,X_ICMSST_TAX_BASE_AMOUNT
194 FROM
195 RA_CUSTOMER_TRX_LINES RACTL,
196 AR_VAT_TAX ARVT
197 WHERE RACTL.LINE_TYPE = 'TAX'
198 AND RACTL.LINK_TO_CUST_TRX_LINE_ID = C_DTL_TRX_LINE_ID
199 AND ARVT.VAT_TAX_ID = RACTL.VAT_TAX_ID
200 AND TO_NUMBER(ARVT.GLOBAL_ATTRIBUTE1) = 3;
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 NULL;
204 WHEN OTHERS THEN
205 NULL;
206 END;
207 CP_ICMSST_TAX_BASE_AMOUNT := X_ICMSST_TAX_BASE_AMOUNT;
208 RETURN (X_ICMSST_TAX_AMOUNT);
209 END CF_ICMSST_TAX_AMOUNTFORMULA;
210 FUNCTION CF_ITEM_CODEFORMULA(C_INVENTORY_ID IN NUMBER) RETURN VARCHAR2 IS
211 X_ITEM_CODE VARCHAR2(150);
212 BEGIN
213 X_ITEM_CODE := NULL;
214 BEGIN
215 IF C_INVENTORY_ID IS NOT NULL THEN
216 SELECT
217 MTL_SYSTEM_ITEMS.SEGMENT1
218 INTO X_ITEM_CODE
219 FROM
220 MTL_SYSTEM_ITEMS
221 WHERE MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID = C_INVENTORY_ID
222 AND MTL_SYSTEM_ITEMS.ORGANIZATION_ID = H_SO_ORGANIZATION;
223 END IF;
224 EXCEPTION
225 WHEN OTHERS THEN
226 NULL;
227 END;
228 RETURN (X_ITEM_CODE);
229 END CF_ITEM_CODEFORMULA;
230 FUNCTION CF_TOTAL_PRICEFORMULA(CS_TOTAL_PRICE IN NUMBER
231 ,CS_TOTAL_BASE_ICMS IN NUMBER) RETURN NUMBER IS
232 BEGIN
233 IF NVL(CS_TOTAL_PRICE
234 ,0) = 0 THEN
235 RETURN CS_TOTAL_BASE_ICMS;
236 ELSE
237 RETURN CS_TOTAL_PRICE;
238 END IF;
239 RETURN NULL;
240 END CF_TOTAL_PRICEFORMULA;
241 PROCEDURE PRINTING_COUNT(X_TRX_NUMBER IN VARCHAR2
242 ,CUST_TRX_ID IN NUMBER) IS
243 FLAG_PRINT VARCHAR2(1);
244 P_COUNT NUMBER;
245 L_TRX_REC RA_CUSTOMER_TRX%ROWTYPE;
246 BEGIN
247 BEGIN
248 SELECT
249 PRINTING_PENDING,
250 NVL(PRINTING_COUNT
251 ,0)
252 INTO FLAG_PRINT,P_COUNT
253 FROM
254 RA_CUSTOMER_TRX
255 WHERE TRX_NUMBER = X_TRX_NUMBER
256 AND CUSTOMER_TRX_ID = CUST_TRX_ID;
257 END;
258 IF NVL(FLAG_PRINT
259 ,'Y') = 'Y' THEN
260 ARP_CT_PKG.LOCK_FETCH_P(L_TRX_REC
261 ,CUST_TRX_ID);
262 L_TRX_REC.PRINTING_ORIGINAL_DATE := SYSDATE;
263 L_TRX_REC.PRINTING_LAST_PRINTED := SYSDATE;
264 L_TRX_REC.PRINTING_COUNT := 1;
265 L_TRX_REC.PRINTING_PENDING := 'N';
266 ARP_CT_PKG.UPDATE_P_PRINT(L_TRX_REC
267 ,CUST_TRX_ID);
268 ELSE
269 ARP_CT_PKG.LOCK_FETCH_P(L_TRX_REC
270 ,CUST_TRX_ID);
271 L_TRX_REC.PRINTING_LAST_PRINTED := SYSDATE;
272 L_TRX_REC.PRINTING_COUNT := P_COUNT + 1;
273 ARP_CT_PKG.UPDATE_P_PRINT(L_TRX_REC
274 ,CUST_TRX_ID);
275 END IF;
276 EXCEPTION
277 WHEN OTHERS THEN
278 NULL;
279 END PRINTING_COUNT;
280 FUNCTION CCF_STATE_INSC_SUBSTITUTIONFOR(C_SHIP_TO_SITE_USE_ID IN NUMBER
281 ,C_TRX_DATE IN DATE) RETURN VARCHAR2 IS
282 X_TRIB_SUBST_INSCRIPTION VARCHAR2(30);
283 BEGIN
284 BEGIN
285 SELECT
286 LOC.TRIB_SUBST_INSCRIPTION
287 INTO X_TRIB_SUBST_INSCRIPTION
288 FROM
289 JL_ZZ_AR_TX_LOCN LOC,
290 AR_SYSTEM_PARAMETERS ARSP,
291 HZ_CUST_ACCT_SITES AD,
292 HZ_LOCATIONS LOCN,
293 HZ_PARTY_SITES PST,
294 HZ_CUST_SITE_USES SU,
295 HZ_GEOGRAPHIES GEO,
296 HR_LOCATIONS HRL,
297 HR_ORGANIZATION_UNITS ORG
298 WHERE ORG.ORGANIZATION_ID = H_OPERATION_UNIT
299 AND HRL.LOCATION_ID = ORG.LOCATION_ID
300 AND LOC.SHIP_FROM_CODE = HRL.REGION_2
301 AND SU.SITE_USE_ID = C_SHIP_TO_SITE_USE_ID
302 AND SU.CUST_ACCT_SITE_ID = AD.CUST_ACCT_SITE_ID
303 AND AD.PARTY_SITE_ID = PST.PARTY_SITE_ID
304 AND PST.LOCATION_ID = LOCN.LOCATION_ID
305 AND LOC.SHIP_TO_SEGMENT_ID = GEO.GEOGRAPHY_ID
306 AND UPPER(GEO.GEOGRAPHY_NAME) = UPPER(DECODE(ARSP.GLOBAL_ATTRIBUTE9
307 ,'STATE'
308 ,LOCN.STATE
309 ,LOCN.PROVINCE))
310 AND GEO.GEOGRAPHY_TYPE = ARSP.GLOBAL_ATTRIBUTE9
311 AND LOC.TAX_CATEGORY_ID = 1
312 AND LOC.END_DATE_ACTIVE >= C_TRX_DATE
313 AND NVL(LOC.START_DATE_ACTIVE
314 ,C_TRX_DATE) <= C_TRX_DATE;
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 NULL;
318 WHEN OTHERS THEN
319 NULL;
320 END;
321 RETURN (X_TRIB_SUBST_INSCRIPTION);
322 END CCF_STATE_INSC_SUBSTITUTIONFOR;
323 FUNCTION CF_BRANDFORMULA RETURN VARCHAR2 IS
324 X_BRAND VARCHAR2(150);
325 BEGIN
326 BEGIN
327 SELECT
328 HRL.GLOBAL_ATTRIBUTE5
329 INTO X_BRAND
330 FROM
331 HR_LOCATIONS HRL,
332 HR_ORGANIZATION_UNITS ORG
333 WHERE ORG.ORGANIZATION_ID = H_OPERATION_UNIT
334 AND HRL.LOCATION_ID = ORG.LOCATION_ID;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 NULL;
338 WHEN OTHERS THEN
339 NULL;
340 END;
341 RETURN (X_BRAND);
342 END CF_BRANDFORMULA;
343 FUNCTION CF_AMOUNT_WORDSFORMULA(CS_AMOUNT_DUE IN NUMBER) RETURN VARCHAR2 IS
344 L_AMOUNT_N_WORDS VARCHAR2(2000);
345 BEGIN
346 L_AMOUNT_N_WORDS := BR_CONVERT_AMOUNT(CS_AMOUNT_DUE
347 ,'REAL');
348 RETURN (L_AMOUNT_N_WORDS);
349 END CF_AMOUNT_WORDSFORMULA;
350 FUNCTION SET_P_WHERE RETURN BOOLEAN IS
351 X_PRINT_FLAG VARCHAR2(1);
352 BEGIN
353 X_PRINT_FLAG := 'N';
354 BEGIN
355 SELECT
356 SUBSTR(NVL(GLOBAL_ATTRIBUTE12
357 ,'N')
358 ,1
359 ,1)
360 INTO X_PRINT_FLAG
361 FROM
362 AR_SYSTEM_PARAMETERS;
363 EXCEPTION
364 WHEN OTHERS THEN
365 NULL;
366 END;
367 C_INV_FORM_PREDICATE := NULL;
368 IF (P_INVOICE_PRINT_SUBMIT IS NOT NULL) THEN
369 IF (P_INVOICE_PRINT_SUBMIT = 'SPECIFIC_INVOICE') THEN
370 C_INV_FORM_PREDICATE := 'and ract.customer_trx_id =' || TO_CHAR(P_CUSTOMER_TRX_ID);
371 ELSIF (P_INVOICE_PRINT_SUBMIT = 'BATCH_INVOICE') THEN
372 IF X_PRINT_FLAG = 'N' THEN
373 IF ((P_ISSUE_DATE_HIGH IS NOT NULL) AND (P_TRX_NUMBER_HIGH IS NOT NULL)) THEN
374 C_INV_FORM_PREDICATE := 'and to_date(substr(ract.global_attribute8,1,10),''YYYY/MM/DD'') <=' || '''' || TO_CHAR(P_ISSUE_DATE_HIGH
375 ,'DD-MON-YYYY') || '''';
376 C_INV_FORM_PREDICATE := C_INV_FORM_PREDICATE || ' and lpad(ract.trx_number,20,' || '''' || TO_CHAR(0) || '''' || ') <=' || '''' || LPAD(P_TRX_NUMBER_HIGH
377 ,20
378 ,'0') || '''';
379 ELSIF (P_ISSUE_DATE_HIGH IS NOT NULL) THEN
380 C_INV_FORM_PREDICATE := 'and to_date(substr(ract.global_attribute8,1,10),''YYYY/MM/DD'') <=' || '''' || TO_CHAR(P_ISSUE_DATE_HIGH
381 ,'DD-MON-YYYY') || '''';
382 ELSIF (P_TRX_NUMBER_HIGH IS NOT NULL) THEN
383 C_INV_FORM_PREDICATE := C_INV_FORM_PREDICATE || ' and lpad(ract.trx_number,20,' || '''' || TO_CHAR(0) || '''' || ') <=' || '''' || LPAD(P_TRX_NUMBER_HIGH
384 ,20
385 ,'0') || '''';
386 END IF;
387 ELSE
388 C_INV_FORM_PREDICATE := 'and ract.customer_trx_id IS NULL';
389 END IF;
390 ELSIF (P_INVOICE_PRINT_SUBMIT = 'AUTOINVOICE') THEN
391 IF X_PRINT_FLAG = 'Y' THEN
392 C_INV_FORM_PREDICATE := 'and ract.created_from = ''RAXTRX''';
393 ELSE
394 C_INV_FORM_PREDICATE := 'and ract.customer_trx_id IS NULL';
395 END IF;
396 END IF;
397 IF (P_INVOICE_PRINT_SUBMIT = 'RE_PRINT_INVOICE') THEN
398 C_INV_FORM_PREDICATE := 'and nvl(ract.printing_count,0) > 0';
399 C_INV_FORM_PREDICATE := C_INV_FORM_PREDICATE || ' and ract.trx_number =' || '''' || P_TRX_NUMBER || '''';
400 ELSE
401 C_INV_FORM_PREDICATE := C_INV_FORM_PREDICATE || ' and nvl(ract.printing_count,0) = 0';
402 END IF;
403 ELSE
404 C_INV_FORM_PREDICATE := 'and nvl(ract.printing_count,0) = 0';
405 END IF;
406 IF P_SUB_SERIES IS NOT NULL THEN
407 C_INV_FORM_PREDICATE := C_INV_FORM_PREDICATE || ' and rabs2.batch_source_id = ' || P_SUB_SERIES;
408 END IF;
409 /*SRW.MESSAGE('99'
410 ,C_INV_FORM_PREDICATE)*/NULL;
411 RETURN (TRUE);
412 END SET_P_WHERE;
413 FUNCTION CF_ADDITIONAL_INFOFORMULA(C_CUSTOMER_TRX_ID IN NUMBER
414 ,C_STATUS_TRX IN VARCHAR2) RETURN VARCHAR2 IS
415 X_MEANING VARCHAR2(80);
416 X_FISCAL_CODE VARCHAR2(1500);
417 CURSOR C1 IS
418 SELECT
419 ROWNUM CURRENT_ROW,
420 DECODE(PRODUCT_CATEGORY
421 ,NULL
422 ,PRODUCT_FISC_CLASSIFICATION
423 ,PRODUCT_CATEGORY) FISCAL_CLASS_CODE
424 FROM
425 RA_CUSTOMER_TRX_LINES RACTL,
426 ZX_LINES_DET_FACTORS ZLD
427 WHERE RACTL.CUSTOMER_TRX_ID = C_CUSTOMER_TRX_ID
428 AND ZLD.APPLICATION_ID = 222
429 AND ZLD.ENTITY_CODE = 'TRANSACTIONS'
430 AND ZLD.EVENT_CLASS_CODE in ( 'INVOICE' , 'CREDIT_MEMO' , 'DEBIT_MEMO' )
431 AND ZLD.TRX_ID = C_CUSTOMER_TRX_ID
432 AND ZLD.TRX_LINE_ID = RACTL.CUSTOMER_TRX_LINE_ID
433 AND RACTL.LINE_TYPE = 'LINE'
434 ORDER BY
435 RACTL.CUSTOMER_TRX_ID ASC;
436 BEGIN
437 X_MEANING := NULL;
438 BEGIN
439 SELECT
440 ARL.MEANING
441 INTO X_MEANING
442 FROM
443 AR_LOOKUPS ARL,
444 RA_CUSTOMER_TRX RACT
445 WHERE RACT.STATUS_TRX = 'VD'
446 AND ARL.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
447 AND ARL.LOOKUP_CODE = C_STATUS_TRX;
448 EXCEPTION
449 WHEN OTHERS THEN
450 NULL;
451 END;
452 X_FISCAL_CODE := NULL;
453 FOR c1_rec IN C1 LOOP
454 IF X_FISCAL_CODE IS NULL THEN
455 X_FISCAL_CODE := TO_CHAR(C1_REC.CURRENT_ROW) || '-' || C1_REC.FISCAL_CLASS_CODE;
456 ELSE
457 X_FISCAL_CODE := X_FISCAL_CODE || ', ' || TO_CHAR(C1_REC.CURRENT_ROW) || '-' || C1_REC.FISCAL_CLASS_CODE;
458 END IF;
459 END LOOP;
460 RETURN (X_FISCAL_CODE || ' ' || X_MEANING);
461 END CF_ADDITIONAL_INFOFORMULA;
462 FUNCTION CF_FREIGHT_STATE_INS_NUMBERFOR(SHIP_VIA IN VARCHAR2) RETURN VARCHAR2 IS
463 L_F_NAME VARCHAR2(80) := NULL;
464 L_JOIN_1 VARCHAR2(150) := NULL;
465 L_F_ADDRESS VARCHAR2(150) := NULL;
466 L_F_CITY VARCHAR2(12) := NULL;
467 L_F_STATE VARCHAR2(150) := NULL;
468 L_F_STATE_INS_NUMBER VARCHAR2(150) := NULL;
469 BEGIN
470 IF SHIP_VIA IS NOT NULL THEN
471 BEGIN
472 SELECT
473 DESCRIPTION,
474 GLOBAL_ATTRIBUTE5 || DECODE(GLOBAL_ATTRIBUTE4
475 ,2
476 ,'/' || GLOBAL_ATTRIBUTE6
477 ,3
478 ,'/' || GLOBAL_ATTRIBUTE6) || '-' || GLOBAL_ATTRIBUTE7,
479 GLOBAL_ATTRIBUTE1,
480 SUBSTR(GLOBAL_ATTRIBUTE2
481 ,1
482 ,12),
483 GLOBAL_ATTRIBUTE3,
484 GLOBAL_ATTRIBUTE8
485 INTO L_F_NAME,L_JOIN_1,L_F_ADDRESS,L_F_CITY,L_F_STATE,L_F_STATE_INS_NUMBER
486 FROM
487 ORG_FREIGHT
488 WHERE FREIGHT_CODE = SHIP_VIA
489 AND ORGANIZATION_ID = H_SO_ORGANIZATION;
490 EXCEPTION
491 WHEN OTHERS THEN
492 NULL;
493 END;
494 END IF;
495 CP_FREIGHT_NAME := L_F_NAME;
496 CP_FREIGHT_INSCRIPTION_NUMBER := L_JOIN_1;
497 CP_FREIGHT_ADDRESS := L_F_ADDRESS;
498 CP_FREIGHT_CITY := L_F_CITY;
499 CP_FREIGHT_STATE := L_F_STATE;
500 RETURN (L_F_STATE_INS_NUMBER);
501 END CF_FREIGHT_STATE_INS_NUMBERFOR;
502 FUNCTION CF_IPI_TAX_AMOUNTFORMULA(C_DTL_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
503 X_IPI_TAX_RATE NUMBER;
504 X_IPI_TAX_AMOUNT NUMBER;
505 BEGIN
506 BEGIN
507 SELECT
508 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
509 ,'Y'
510 ,RACTL.EXTENDED_AMOUNT
511 ,0)),
512 SUM(DECODE(ARVT.GLOBAL_ATTRIBUTE2
513 ,'Y'
514 ,NVL(RACTL.TAX_RATE
515 ,0)
516 ,0))
517 INTO X_IPI_TAX_AMOUNT,X_IPI_TAX_RATE
518 FROM
519 RA_CUSTOMER_TRX_LINES RACTL,
520 AR_VAT_TAX ARVT
521 WHERE RACTL.LINE_TYPE = 'TAX'
522 AND RACTL.LINK_TO_CUST_TRX_LINE_ID = C_DTL_TRX_LINE_ID
523 AND ARVT.VAT_TAX_ID = RACTL.VAT_TAX_ID
524 AND TO_NUMBER(ARVT.GLOBAL_ATTRIBUTE1) = 6;
525 EXCEPTION
526 WHEN NO_DATA_FOUND THEN
527 NULL;
528 WHEN OTHERS THEN
529 NULL;
530 END;
531 CP_IPI_TAX_RATE := X_IPI_TAX_RATE;
532 RETURN (X_IPI_TAX_AMOUNT);
533 END CF_IPI_TAX_AMOUNTFORMULA;
534 FUNCTION CF_TOTAL_EXTENDED_AMOUNTFORMUL(CS_TOTAL_EXTENDED_AMOUNT IN NUMBER) RETURN NUMBER IS
535 L_TOTAL NUMBER;
536 BEGIN
537 L_TOTAL := NVL(CS_TOTAL_EXTENDED_AMOUNT
538 ,0);
539 RETURN (L_TOTAL);
540 END CF_TOTAL_EXTENDED_AMOUNTFORMUL;
541 FUNCTION CF_OPR_CODEFORMULA(C_OPERATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
542 BEGIN
543 RETURN (SUBSTR(C_OPERATION_CODE
544 ,1
545 ,4));
546 END CF_OPR_CODEFORMULA;
547 FUNCTION CF_PRINTING_COUNT(TRX_NUM IN VARCHAR2
548 ,C_CUSTOMER_TRX_ID IN NUMBER) RETURN NUMBER IS
549 BEGIN
550 PRINTING_COUNT(TRX_NUM
551 ,C_CUSTOMER_TRX_ID);
552 RETURN (0);
553 END CF_PRINTING_COUNT;
554 FUNCTION CP_OPERATION_NATURE_P RETURN VARCHAR2 IS
555 BEGIN
556 RETURN CP_OPERATION_NATURE;
557 END CP_OPERATION_NATURE_P;
558 FUNCTION CP_OPERATION_CODE_P RETURN VARCHAR2 IS
559 BEGIN
560 RETURN CP_OPERATION_CODE;
561 END CP_OPERATION_CODE_P;
562 FUNCTION CP_FREIGHT_CITY_P RETURN VARCHAR2 IS
563 BEGIN
564 RETURN CP_FREIGHT_CITY;
565 END CP_FREIGHT_CITY_P;
566 FUNCTION CP_FREIGHT_STATE_P RETURN VARCHAR2 IS
567 BEGIN
568 RETURN CP_FREIGHT_STATE;
569 END CP_FREIGHT_STATE_P;
570 FUNCTION CP_FREIGHT_ADDRESS_P RETURN VARCHAR2 IS
571 BEGIN
572 RETURN CP_FREIGHT_ADDRESS;
573 END CP_FREIGHT_ADDRESS_P;
574 FUNCTION CF_FREIGHT_INSCRIPTION_NUMBER RETURN VARCHAR2 IS
575 BEGIN
576 RETURN CP_FREIGHT_INSCRIPTION_NUMBER;
577 END CF_FREIGHT_INSCRIPTION_NUMBER;
578 FUNCTION CP_FREIGHT_NAME_P RETURN VARCHAR2 IS
579 BEGIN
580 RETURN CP_FREIGHT_NAME;
581 END CP_FREIGHT_NAME_P;
582 FUNCTION CP_ICMS_TAX_AMOUNT_P RETURN NUMBER IS
583 BEGIN
584 RETURN CP_ICMS_TAX_AMOUNT;
585 END CP_ICMS_TAX_AMOUNT_P;
586 FUNCTION CP_ICMS_TAX_BASE_AMOUNT_P RETURN NUMBER IS
587 BEGIN
588 RETURN CP_ICMS_TAX_BASE_AMOUNT;
589 END CP_ICMS_TAX_BASE_AMOUNT_P;
590 FUNCTION CP_IPI_TAX_RATE_P RETURN NUMBER IS
591 BEGIN
592 RETURN CP_IPI_TAX_RATE;
593 END CP_IPI_TAX_RATE_P;
594 FUNCTION CP_ICMSST_TAX_BASE_AMOUNT_P RETURN NUMBER IS
595 BEGIN
596 RETURN CP_ICMSST_TAX_BASE_AMOUNT;
597 END CP_ICMSST_TAX_BASE_AMOUNT_P;
598 FUNCTION CP_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
599 BEGIN
600 RETURN CP_BASE_CURRENCY_CODE;
601 END CP_BASE_CURRENCY_CODE_P;
602 FUNCTION CP_BASE_PRECISION_P RETURN NUMBER IS
603 BEGIN
604 RETURN CP_BASE_PRECISION;
605 END CP_BASE_PRECISION_P;
606 FUNCTION H_OPERATION_UNIT_P RETURN NUMBER IS
607 BEGIN
608 RETURN H_OPERATION_UNIT;
609 END H_OPERATION_UNIT_P;
610 FUNCTION C_INV_FORM_PREDICATE_P RETURN VARCHAR2 IS
611 BEGIN
612 RETURN C_INV_FORM_PREDICATE;
613 END C_INV_FORM_PREDICATE_P;
614 FUNCTION H_SO_ORGANIZATION_P RETURN NUMBER IS
615 BEGIN
616 RETURN H_SO_ORGANIZATION;
617 END H_SO_ORGANIZATION_P;
618 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
619 ,NAME IN VARCHAR2) IS
620 BEGIN
621 /*STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
622 STPROC.BIND_I(APPLICATION);
623 STPROC.BIND_I(NAME);
624 STPROC.EXECUTE;*/
625 FND_MESSAGE.SET_NAME(APPLICATION, NAME);
626 END SET_NAME;
627 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
628 ,VALUE IN VARCHAR2
629 ,TRANSLATE IN BOOLEAN) IS
630 BEGIN
631 /*STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
632 STPROC.BIND_I(TRANSLATE);
633 STPROC.BIND_I(TOKEN);
634 STPROC.BIND_I(VALUE);
635 STPROC.EXECUTE;*/null;
636 END SET_TOKEN;
637 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
638 BEGIN
639 /*STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
640 STPROC.BIND_O(MSGOUT);
641 STPROC.EXECUTE;
642 STPROC.RETRIEVE(1
643 ,MSGOUT);*/null;
644 END RETRIEVE;
645 PROCEDURE CLEAR IS
646 BEGIN
647 /*STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
648 STPROC.EXECUTE;*/null;
649 END CLEAR;
650 FUNCTION GET_STRING(APPIN IN VARCHAR2
651 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
652 X0 VARCHAR2(2000);
653 BEGIN
654 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
655 STPROC.BIND_O(X0);
656 STPROC.BIND_I(APPIN);
657 STPROC.BIND_I(NAMEIN);
658 STPROC.EXECUTE;
659 STPROC.RETRIEVE(1
660 ,X0);*/null;
661 RETURN X0;
662 END GET_STRING;
663 FUNCTION GET RETURN VARCHAR2 IS
664 X0 VARCHAR2(2000);
665 BEGIN
666 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
667 STPROC.BIND_O(X0);
668 STPROC.EXECUTE;
669 STPROC.RETRIEVE(1
670 ,X0);*/
671 X0 := FND_MESSAGE.GET;
672 RETURN X0;
673 END GET;
674 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
675 X0 VARCHAR2(2000);
676 BEGIN
677 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
678 STPROC.BIND_O(X0);
679 STPROC.EXECUTE;
680 STPROC.RETRIEVE(1
681 ,X0);*/null;
682 RETURN X0;
683 END GET_ENCODED;
684 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
685 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
686 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
687 BEGIN
688 /*STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
689 STPROC.BIND_I(ENCODED_MESSAGE);
690 STPROC.BIND_O(APP_SHORT_NAME);
691 STPROC.BIND_O(MESSAGE_NAME);
692 STPROC.EXECUTE;
693 STPROC.RETRIEVE(2
694 ,APP_SHORT_NAME);
695 STPROC.RETRIEVE(3
696 ,MESSAGE_NAME);*/null;
697 END PARSE_ENCODED;
698 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
699 BEGIN
700 /*STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
701 STPROC.BIND_I(ENCODED_MESSAGE);
702 STPROC.EXECUTE;*/null;
703 END SET_ENCODED;
704 PROCEDURE RAISE_ERROR IS
705 BEGIN
706 /*STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
707 STPROC.EXECUTE;*/null;
708 END RAISE_ERROR;
709 FUNCTION BR_CONVERT_AMOUNT(X_INVOICE_AMOUNT IN NUMBER
710 ,X_CURRENCY_NAME IN VARCHAR2) RETURN VARCHAR2 IS
711 X0 VARCHAR2(2000);
712 BEGIN
713 /*STPROC.INIT('begin :X0 := JL_BR_AR_PRINT_AMNT.BR_CONVERT_AMOUNT(:X_INVOICE_AMOUNT, :X_CURRENCY_NAME); end;');
714 STPROC.BIND_O(X0);
715 STPROC.BIND_I(X_INVOICE_AMOUNT);
716 STPROC.BIND_I(X_CURRENCY_NAME);
717 STPROC.EXECUTE;
718 STPROC.RETRIEVE(1
719 ,X0);*/
720 X0 := JL_BR_AR_PRINT_AMNT.BR_CONVERT_AMOUNT(X_INVOICE_AMOUNT, X_CURRENCY_NAME);
721 RETURN X0;
722 END BR_CONVERT_AMOUNT;
723 END JL_JLBRRINV_XMLP_PKG;
724
725