[Home] [Help]
PACKAGE BODY: APPS.JG_JGZZRCAK_XMLP_PKG
Source
1 PACKAGE BODY JG_JGZZRCAK_XMLP_PKG AS
2 /* $Header: JGZZRCAKB.pls 120.2 2007/12/25 16:05:50 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
7 IF (P_DEBUG_SWITCH = 'Y') THEN
8 /*SRW.MESSAGE(1
9 ,'After SRWINIT.')*/NULL;
10 END IF;
11 DECLARE
12 COAID NUMBER;
13 LEDNAME VARCHAR2(30);
14 FUNCURR VARCHAR2(15);
15 ERRBUF VARCHAR2(132);
16 P_PRECISION NUMBER;
17 P_EXT_PRECISION NUMBER;
18 P_MIN_ACCT_UNIT NUMBER;
19 ORG_ID NUMBER;
20 BEGIN
21 GL_INFO.GL_GET_LEDGER_INFO(P_SOB_ID
22 ,COAID
23 ,LEDNAME
24 ,FUNCURR
25 ,ERRBUF);
26 IF (ERRBUF IS NOT NULL) THEN
27 /*SRW.MESSAGE(4
28 ,ERRBUF)*/NULL;
29 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
30 END IF;
31 CP_FUNC_CURRENCY := FUNCURR;
32 CP_STRUCT_NUM := COAID;
33 CP_SOB_NAME := LEDNAME;
34 GET_INFO(CP_FUNC_CURRENCY
35 ,P_PRECISION
36 ,P_EXT_PRECISION
37 ,P_MIN_ACCT_UNIT);
38 CP_PRECISION := P_PRECISION;
39 SELECT
40 TO_CHAR(SYSDATE
41 ,'DD-MON-YYYY HH24:MI:SS')
42 INTO CP_SYSDATE
43 FROM
44 DUAL;
45 CP_LETTER_DATE := SUBSTR(CP_SYSDATE
46 ,1
47 ,11);
48 ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
49 CP_COUNTRY_CODE := JG_ZZ_SHARED_PKG.GET_COUNTRY(ORG_ID);
50 P_ORG_ID := ORG_ID;
51 IF CP_COUNTRY_CODE = NULL THEN
52 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
53 END IF;
54 IF (P_DEBUG_SWITCH = 'Y') THEN
55 /*SRW.BREAK*/NULL;
56 END IF;
57 DECLARE
58 CURSOR GET_DOC_SEQUENCE_INFO IS
59 SELECT
60 DSC.NAME,
61 DS.DOC_SEQUENCE_ID,
62 DSU.NEXTVAL
63 FROM
64 FND_DOC_SEQUENCE_CATEGORIES DSC,
65 FND_DOCUMENT_SEQUENCES DS,
66 FND_DOC_SEQUENCE_ASSIGNMENTS DSA,
67 FND_DOC_SEQUENCE_USERS DSU
68 WHERE DSA.CATEGORY_CODE = DSC.CODE
69 AND DSA.APPLICATION_ID = DSC.APPLICATION_ID
70 AND DS.DOC_SEQUENCE_ID = DSA.DOC_SEQUENCE_ID
71 AND DSU.DOC_SEQUENCE_ID = DSA.DOC_SEQUENCE_ID
72 AND DSA.DOC_SEQUENCE_ASSIGNMENT_ID = P_DOC_SEQ_ASSIGN_ID
73 AND DSA.SET_OF_BOOKS_ID = P_SOB_ID
74 AND DSA.END_DATE is NULL;
75 CURSOR GET_CUST_NAME IS
76 SELECT
77 DISTINCT
78 MIN(HP.PARTY_NAME),
79 MAX(HP.PARTY_NAME)
80 FROM
81 AR_CASH_RECEIPTS ACR,
82 HZ_PARTIES HP,
83 HZ_CUST_ACCOUNTS HCA
84 WHERE ( ACR.GLOBAL_ATTRIBUTE1 is NULL
85 AND ACR.GLOBAL_ATTRIBUTE2 is NULL )
86 AND HP.PARTY_ID = HCA.PARTY_ID
87 AND HCA.CUST_ACCOUNT_ID = ACR.PAY_FROM_CUSTOMER;
88 CURSOR GET_RECEIPT_DATES IS
89 SELECT
90 DISTINCT
91 MIN(RECEIPT_DATE),
92 MAX(RECEIPT_DATE)
93 FROM
94 AR_CASH_RECEIPTS CR
95 WHERE ( CR.GLOBAL_ATTRIBUTE1 is NULL
96 AND CR.GLOBAL_ATTRIBUTE2 is NULL );
97 CURSOR GET_LOCATION_INFO IS
98 SELECT
99 SUBSTR(HR.LOCATION_CODE
100 ,1
101 ,20),
102 SUBSTR(HR.ADDRESS_LINE_1
103 ,1
104 ,60),
105 SUBSTR(HR.ADDRESS_LINE_2
106 ,1
107 ,60),
108 SUBSTR(HR.ADDRESS_LINE_3
109 ,1
110 ,60),
111 HR.POSTAL_CODE || ' ' || HR.TOWN_OR_CITY,
112 FT2.TERRITORY_SHORT_NAME,
113 HR.TELEPHONE_NUMBER_1,
114 HR.TELEPHONE_NUMBER_2,
115 HR.TELEPHONE_NUMBER_3,
116 XFI.REGISTRATION_NUMBER
117 FROM
118 HR_LOCATIONS HR,
119 XLE_FIRSTPARTY_INFORMATION_V XFI,
120 FND_TERRITORIES_VL FT2
121 WHERE XFI.LEGAL_ENTITY_ID = P_ORG_ID
122 AND XFI.LOCATION_ID = HR.LOCATION_ID
123 AND FT2.TERRITORY_CODE = HR.COUNTRY
124 AND HR.OFFICE_SITE_FLAG = 'Y';
125 BEGIN
126 OPEN GET_LOCATION_INFO;
127 FETCH GET_LOCATION_INFO
128 INTO CP_HR_LOCATION,CP_HR_ADDRESS1,CP_HR_ADDRESS2,CP_HR_ADDRESS3,CP_HR_CITY,CP_HR_TERRITORY,CP_HR_TELEPHONE,CP_HR_FAX,CP_HR_TELEPHONE3,CP_HR_TAX_ID;
129 CLOSE GET_LOCATION_INFO;
130 IF (P_REPRINT = 'N') THEN
131 IF ((P_RA_FROM IS NOT NULL) OR (P_RA_TO IS NOT NULL)) THEN
132 SET_NAME('JG'
133 ,'JG_ZZ_ACK_CREATE');
134 /*SRW.MESSAGE(12
135 ,GET)*/NULL;
136 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
137 END IF;
138 OPEN GET_DOC_SEQUENCE_INFO;
139 FETCH GET_DOC_SEQUENCE_INFO
140 INTO CP_CATEGORY,CP_DOC_SEQ_ID,P_RA_FROM;
141 CLOSE GET_DOC_SEQUENCE_INFO;
142 IF ((P_CUSTOMER_FROM IS NULL) AND (P_CUSTOMER_TO IS NULL)) THEN
143 OPEN GET_CUST_NAME;
144 FETCH GET_CUST_NAME
145 INTO P_CUSTOMER_FROM,P_CUSTOMER_TO;
146 CLOSE GET_CUST_NAME;
147 ELSIF (P_CUSTOMER_FROM IS NULL) THEN
148 P_CUSTOMER_FROM := P_CUSTOMER_TO;
149 ELSIF (P_CUSTOMER_TO IS NULL) THEN
150 P_CUSTOMER_TO := P_CUSTOMER_FROM;
151 ELSIF (P_CUSTOMER_FROM > P_CUSTOMER_TO) THEN
152 /*SRW.MESSAGE(18
153 ,'P_CUSTOMER_FROM is larger than P_CUSTOMER_TO.')*/NULL;
154 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
155 END IF;
156 IF ((P_RECEIPT_FROM IS NULL) AND (P_RECEIPT_TO IS NULL)) THEN
157 OPEN GET_RECEIPT_DATES;
158 FETCH GET_RECEIPT_DATES
159 INTO P_RECEIPT_FROM,P_RECEIPT_TO;
160 CLOSE GET_RECEIPT_DATES;
161 ELSIF (P_RECEIPT_FROM IS NULL) THEN
162 P_RECEIPT_FROM := P_RECEIPT_TO;
163 ELSIF (P_RECEIPT_TO IS NULL) THEN
164 P_RECEIPT_TO := P_RECEIPT_FROM;
165 ELSIF (P_RECEIPT_FROM > P_RECEIPT_TO) THEN
166 /*SRW.MESSAGE(20
167 ,'P_RECEIPT_FROM is larger than P_RECEIPT_TO.')*/NULL;
168 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
169 END IF;
170 CP_WHERE_RECEIPT_DATES := 'cr.receipt_date between to_date(' || '''' || TO_CHAR(P_RECEIPT_FROM) || '''' || ',''DD-MON-RR'') AND
171 to_date(' || '''' || TO_CHAR(P_RECEIPT_TO) || '''' || ',''DD-MON-RR'')';
172 CUSTOMER_RECEIPTS(P_CUSTOMER_FROM
173 ,P_CUSTOMER_TO);
174 ELSE
175 IF ((P_RA_FROM IS NULL) AND (P_RA_TO IS NULL)) THEN
176 SET_NAME('JG'
177 ,'JG_ZZ_ACK_REPRINT');
178 /*SRW.MESSAGE(24
179 ,GET)*/NULL;
180 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
181 ELSIF (P_RA_TO IS NULL) THEN
182 P_RA_TO := P_RA_FROM;
183 ELSIF (P_RA_FROM IS NULL) THEN
184 P_RA_FROM := P_RA_TO;
185 ELSIF (P_RA_FROM > P_RA_TO) THEN
186 /*SRW.MESSAGE(28
187 ,'P_RA_FROM is larger than P_RA_TO.')*/NULL;
188 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
189 END IF;
190 SELECT
191 DISTINCT
192 MIN(RECEIPT_DATE),
193 MAX(RECEIPT_DATE)
194 INTO P_RECEIPT_FROM,P_RECEIPT_TO
195 FROM
196 AR_CASH_RECEIPTS CR
197 WHERE TO_NUMBER(CR.GLOBAL_ATTRIBUTE1) >= P_RA_FROM
198 AND TO_NUMBER(CR.GLOBAL_ATTRIBUTE1) <= P_RA_TO;
199 IF (P_RECEIPT_FROM IS NULL) AND (P_RECEIPT_TO IS NULL) THEN
200 /*SRW.MESSAGE(32
201 ,'The receipt sequence number has been modified manually!')*/NULL;
202 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
203 END IF;
204 END IF;
205 CP_DATE_FROM := TO_CHAR(P_RECEIPT_FROM
206 ,'DD-MON-YYYY');
207 CP_DATE_TO := TO_CHAR(P_RECEIPT_TO
208 ,'DD-MON-YYYY');
209 END;
210 IF (P_DEBUG_SWITCH = 'Y') THEN
211 /*SRW.BREAK*/NULL;
212 END IF;
213 EXCEPTION
214 WHEN OTHERS THEN
215 /*SRW.MESSAGE(888
216 ,'Error in BeforeReport Trigger.')*/NULL;
217 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
218 END;
219 RETURN (TRUE);
220 END BEFOREREPORT;
221
222 FUNCTION AFTERREPORT RETURN BOOLEAN IS
223 BEGIN
224 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
225 RETURN (TRUE);
226 END AFTERREPORT;
227
228 FUNCTION CF_WORD_AMOUNTFORMULA(C_CURR_TOTAL_CURRENCY IN VARCHAR2
229 ,CS_CURR_TOTAL IN NUMBER) RETURN VARCHAR2 IS
230 BEGIN
231 DECLARE
232 L_WORD_TEXT VARCHAR2(240);
233 L_WIDTH NUMBER := 58;
234 L_PRECISION NUMBER;
235 L_CURRENCY_NAME VARCHAR2(80);
236 BEGIN
237 IF (C_CURR_TOTAL_CURRENCY <> 'PTE') THEN
238 SELECT
239 NAME,
240 PRECISION
241 INTO L_CURRENCY_NAME,L_PRECISION
242 FROM
243 FND_CURRENCIES_VL
244 WHERE CURRENCY_CODE = C_CURR_TOTAL_CURRENCY;
245 CP_UNIT_SINGULAR := L_CURRENCY_NAME;
246 CP_UNIT_PLURAL := L_CURRENCY_NAME;
247 CP_SUB_UNIT_SINGULAR := '';
248 CP_SUB_UNIT_PLURAL := '';
249 CP_UNIT_RATIO := POWER(10
250 ,L_PRECISION);
251 ELSE
252 CP_UNIT_SINGULAR := 'Escudo';
253 CP_UNIT_PLURAL := 'Escudos';
254 CP_SUB_UNIT_SINGULAR := 'Centavo';
255 CP_SUB_UNIT_PLURAL := 'Centavos';
256 CP_UNIT_RATIO := 100;
257 END IF;
258 L_WORD_TEXT := GET_WORD_VALUE(CS_CURR_TOTAL
259 ,CP_UNIT_SINGULAR
260 ,CP_UNIT_PLURAL
261 ,CP_SUB_UNIT_SINGULAR
262 ,CP_SUB_UNIT_PLURAL
263 ,CP_UNIT_RATIO);
264 L_WORD_TEXT := L_WORD_TEXT || ' ';
265 IF NVL(LENGTH(L_WORD_TEXT)
266 ,0) <= L_WIDTH THEN
267 L_WORD_TEXT := RPAD(L_WORD_TEXT
268 ,L_WIDTH
269 ,'*');
270 ELSIF NVL(LENGTH(L_WORD_TEXT)
271 ,0) <= L_WIDTH * 2 THEN
272 L_WORD_TEXT := RPAD(L_WORD_TEXT
273 ,L_WIDTH * 2 - (L_WIDTH - INSTR(SUBSTR(L_WORD_TEXT
274 ,1
275 ,L_WIDTH + 1)
276 ,' '
277 ,-1))
278 ,'*');
279 ELSIF NVL(LENGTH(L_WORD_TEXT)
280 ,0) <= L_WIDTH * 3 THEN
281 L_WORD_TEXT := RPAD(L_WORD_TEXT
282 ,L_WIDTH * 3
283 ,'*');
284 END IF;
285 RETURN (L_WORD_TEXT);
286 EXCEPTION
287 WHEN OTHERS THEN
288 NULL;
289 END;
290 RETURN NULL;
291 END CF_WORD_AMOUNTFORMULA;
292
293 PROCEDURE CUSTOMER_RECEIPTS(FROM_CUST IN VARCHAR2
294 ,TO_CUST IN VARCHAR2) IS
295 CURSOR CUR_CUST(C_FROM IN VARCHAR2,C_TO IN VARCHAR2) IS
296 SELECT
297 DISTINCT
298 HCA.CUST_ACCOUNT_ID,
299 CR.CURRENCY_CODE,
300 HCSU.SITE_USE_ID
301 FROM
302 HZ_PARTIES HP,
303 HZ_CUST_ACCOUNTS HCA,
304 HZ_CUST_SITE_USES HCSU,
305 HZ_LOCATIONS HL,
306 HZ_CUST_ACCT_SITES HCAS,
307 AR_CASH_RECEIPTS CR,
308 AR_CASH_RECEIPT_HISTORY CRH,
309 HZ_PARTY_SITES HPS
310 WHERE HP.PARTY_ID = HCA.PARTY_ID
311 AND ( HP.PARTY_NAME >= C_FROM
312 AND HP.PARTY_NAME <= C_TO )
313 AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
314 AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
315 AND HP.PARTY_ID = HPS.PARTY_ID
316 AND HL.LOCATION_ID = HPS.LOCATION_ID
317 AND UPPER(HCSU.SITE_USE_CODE) = 'BILL_TO'
318 AND HCSU.GLOBAL_ATTRIBUTE1 = 'Y'
319 AND ( CR.GLOBAL_ATTRIBUTE1 is NULL
320 AND CR.GLOBAL_ATTRIBUTE2 is NULL )
321 AND CR.RECEIPT_DATE >= P_RECEIPT_FROM
322 AND CR.RECEIPT_DATE <= P_RECEIPT_TO
323 AND CRH.STATUS = 'CLEARED'
324 AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
325 AND CR.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID;
326 CURSOR UPDATE_AR_CASH_RECEIPTS(C_CUST IN NUMBER,C_CURRENCY IN VARCHAR2,C_SITE_USE_ID IN NUMBER) IS
327 SELECT
328 ROWID
329 FROM
330 AR_CASH_RECEIPTS CR
331 WHERE CR.PAY_FROM_CUSTOMER = C_CUST
332 AND CR.CURRENCY_CODE = C_CURRENCY
333 AND CR.CUSTOMER_SITE_USE_ID = C_SITE_USE_ID
334 AND CR.GLOBAL_ATTRIBUTE1 is NULL
335 AND CR.GLOBAL_ATTRIBUTE2 is NULL
336 AND CR.RECEIPT_DATE >= P_RECEIPT_FROM
337 AND CR.RECEIPT_DATE <= P_RECEIPT_TO
338 AND exists (
339 SELECT
340 CRH.CASH_RECEIPT_ID
341 FROM
342 AR_CASH_RECEIPT_HISTORY CRH
343 WHERE CRH.STATUS = 'CLEARED'
344 AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID );
345 C_CUST NUMBER;
346 C_CURRENCY VARCHAR2(3);
347 C_RECEIPT NUMBER;
348 C_SITE_USE_ID NUMBER;
349 L_BATCH NUMBER;
350 L_COUNT NUMBER;
351 BEGIN
352 L_BATCH := 1000;
353 L_COUNT := 0;
354 FOR customer_row IN CUR_CUST(FROM_CUST, TO_CUST) LOOP
355 C_CUST := CUSTOMER_ROW.CUST_ACCOUNT_ID;
356 C_CURRENCY := CUSTOMER_ROW.CURRENCY_CODE;
357 C_SITE_USE_ID := CUSTOMER_ROW.SITE_USE_ID;
358 C_RECEIPT := GET_NEXT_USER_SEQUENCE('0'
359 ,P_DOC_SEQ_ASSIGN_ID
360 ,CP_DOC_SEQ_ID);
361 CP_DOC_VALUE := C_RECEIPT;
362 P_RA_TO := C_RECEIPT;
363 AR_DOC_SEQUENCE_AUDIT_P;
364 FOR receipt_row IN UPDATE_AR_CASH_RECEIPTS(C_CUST,C_CURRENCY,C_SITE_USE_ID) LOOP
365 UPDATE
366 AR_CASH_RECEIPTS cr
367 SET
368 CR.GLOBAL_ATTRIBUTE1 = C_RECEIPT
369 ,CR.GLOBAL_ATTRIBUTE2 = TO_CHAR(SYSDATE
370 ,'YYYY/MM/DD HH24:MI:SS')
371 ,CR.GLOBAL_ATTRIBUTE3 = CP_CATEGORY
372 WHERE CR.ROWID = RECEIPT_ROW.ROWID;
373 L_COUNT := L_COUNT + 1;
374 IF (L_COUNT = L_BATCH) THEN
375 COMMIT;
376 L_COUNT := 0;
377 END IF;
378 END LOOP;
379 END LOOP;
380 IF (L_COUNT < L_BATCH) THEN
381 COMMIT;
382 END IF;
383 EXCEPTION
384 WHEN OTHERS THEN
385 /*SRW.MESSAGE(999
386 ,'Error in procedure - Customer_Receipts' || TO_CHAR(SQLCODE))*/NULL;
387 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
388 END CUSTOMER_RECEIPTS;
389
390 PROCEDURE AR_DOC_SEQUENCE_AUDIT_P IS
391 BEGIN
392 INSERT INTO AR_DOC_SEQUENCE_AUDIT
393 (DOC_SEQUENCE_ID
394 ,DOC_SEQUENCE_ASSIGNMENT_ID
395 ,DOC_SEQUENCE_VALUE
396 ,CREATION_DATE
397 ,CREATED_BY)
398 VALUES (CP_DOC_SEQ_ID
399 ,P_DOC_SEQ_ASSIGN_ID
400 ,CP_DOC_VALUE
401 ,SYSDATE
402 ,0);
403 END AR_DOC_SEQUENCE_AUDIT_P;
404
405 FUNCTION CF_COUNTFORMULA(CS_INV_APPLIED_AMT IN NUMBER) RETURN NUMBER IS
406 BEGIN
407 IF (CS_INV_APPLIED_AMT = 0) THEN
408 RETURN (0);
409 ELSE
410 RETURN (1);
411 END IF;
412 END CF_COUNTFORMULA;
413
414 FUNCTION CONVERT_NUMBER2(SEGMENT IN NUMBER) RETURN VARCHAR2 IS
415 VALUE_TEXT VARCHAR2(80) := '';
416 V_CENTENAS VARCHAR2(12);
417 CENTENAS NUMBER := POWER(10
418 ,2);
419 V_DEZENAS VARCHAR2(10);
420 DEZENAS NUMBER := 10;
421 V_UNIDADES VARCHAR2(10);
422 CURRENT_SEG VARCHAR2(80);
423 RESTO VARCHAR2(3);
424 NUM INTEGER := SEGMENT;
425 BEGIN
426 CURRENT_SEG := TRUNC(NUM / CENTENAS);
427 RESTO := MOD(NUM
428 ,CENTENAS);
429 NUM := NUM - (CURRENT_SEG * CENTENAS);
430 IF CURRENT_SEG <> 0 THEN
431 IF CURRENT_SEG = 1 THEN
432 IF RESTO = 0 THEN
433 VALUE_TEXT := VALUE_TEXT || 'cem ';
434 ELSE
435 VALUE_TEXT := VALUE_TEXT || 'cento ';
436 END IF;
437 ELSIF CURRENT_SEG = 2 THEN
438 VALUE_TEXT := VALUE_TEXT || 'duzentos ';
439 ELSIF CURRENT_SEG = 3 THEN
440 VALUE_TEXT := VALUE_TEXT || 'trezentos ';
441 ELSIF CURRENT_SEG = 4 THEN
442 VALUE_TEXT := VALUE_TEXT || 'quatrocentos ';
443 ELSIF CURRENT_SEG = 5 THEN
444 VALUE_TEXT := VALUE_TEXT || 'quinhentos ';
445 ELSIF CURRENT_SEG = 6 THEN
446 VALUE_TEXT := VALUE_TEXT || 'seiscentos ';
447 ELSIF CURRENT_SEG = 7 THEN
448 VALUE_TEXT := VALUE_TEXT || 'setecentos ';
449 ELSIF CURRENT_SEG = 8 THEN
450 VALUE_TEXT := VALUE_TEXT || 'oitocentos ';
451 ELSIF CURRENT_SEG = 9 THEN
452 VALUE_TEXT := VALUE_TEXT || 'novecentos ';
453 END IF;
454 END IF;
455 CURRENT_SEG := TRUNC(NUM / DEZENAS);
456 RESTO := MOD(NUM
457 ,DEZENAS);
458 NUM := NUM - (CURRENT_SEG * DEZENAS);
459 IF CURRENT_SEG <> 0 THEN
460 IF CURRENT_SEG = 1 THEN
461 IF RESTO = 0 THEN
462 VALUE_TEXT := VALUE_TEXT || 'dez ';
463 ELSIF RESTO = 1 THEN
464 VALUE_TEXT := VALUE_TEXT || 'onze ';
465 ELSIF RESTO = 2 THEN
466 VALUE_TEXT := VALUE_TEXT || 'doze ';
467 ELSIF RESTO = 3 THEN
468 VALUE_TEXT := VALUE_TEXT || 'treze ';
469 ELSIF RESTO = 4 THEN
470 VALUE_TEXT := VALUE_TEXT || 'catorze ';
471 ELSIF RESTO = 5 THEN
472 VALUE_TEXT := VALUE_TEXT || 'quinze ';
473 ELSIF RESTO = 6 THEN
474 VALUE_TEXT := VALUE_TEXT || 'dezasseis ';
475 ELSIF RESTO = 7 THEN
476 VALUE_TEXT := VALUE_TEXT || 'dezassete ';
477 ELSIF RESTO = 8 THEN
478 VALUE_TEXT := VALUE_TEXT || 'dezoito ';
479 ELSIF RESTO = 9 THEN
480 VALUE_TEXT := VALUE_TEXT || 'dezanove ';
481 END IF;
482 ELSIF CURRENT_SEG = 2 THEN
483 VALUE_TEXT := VALUE_TEXT || 'vinte ';
484 ELSIF CURRENT_SEG = 3 THEN
485 VALUE_TEXT := VALUE_TEXT || 'trinta ';
486 ELSIF CURRENT_SEG = 4 THEN
487 VALUE_TEXT := VALUE_TEXT || 'quarenta ';
488 ELSIF CURRENT_SEG = 5 THEN
489 VALUE_TEXT := VALUE_TEXT || 'cinquenta ';
490 ELSIF CURRENT_SEG = 6 THEN
491 VALUE_TEXT := VALUE_TEXT || 'sessenta ';
492 ELSIF CURRENT_SEG = 7 THEN
493 VALUE_TEXT := VALUE_TEXT || 'setenta ';
494 ELSIF CURRENT_SEG = 8 THEN
495 VALUE_TEXT := VALUE_TEXT || 'oitenta ';
496 ELSIF CURRENT_SEG = 9 THEN
497 VALUE_TEXT := VALUE_TEXT || 'noventa ';
498 END IF;
499 END IF;
500 IF NUM <> 0 AND CURRENT_SEG <> 1 THEN
501 IF NUM = 1 THEN
502 VALUE_TEXT := VALUE_TEXT || 'um ';
503 ELSIF NUM = 2 THEN
504 VALUE_TEXT := VALUE_TEXT || 'dois ';
505 ELSIF NUM = 3 THEN
506 VALUE_TEXT := VALUE_TEXT || 'tres ';
507 ELSIF NUM = 4 THEN
508 VALUE_TEXT := VALUE_TEXT || 'quatro ';
509 ELSIF NUM = 5 THEN
510 VALUE_TEXT := VALUE_TEXT || 'cinco ';
511 ELSIF NUM = 6 THEN
512 VALUE_TEXT := VALUE_TEXT || 'seis ';
513 ELSIF NUM = 7 THEN
514 VALUE_TEXT := VALUE_TEXT || 'sete ';
515 ELSIF NUM = 8 THEN
516 VALUE_TEXT := VALUE_TEXT || 'oito ';
517 ELSIF NUM = 9 THEN
518 VALUE_TEXT := VALUE_TEXT || 'nove ';
519 END IF;
520 END IF;
521 RETURN (VALUE_TEXT);
522 END CONVERT_NUMBER2;
523
524 FUNCTION CONVERT_NUMBER(IN_NUMERAL IN INTEGER := 0) RETURN VARCHAR2 IS
525 NUMBER_TOO_LARGE EXCEPTION;
526 NUMERAL INTEGER := ABS(IN_NUMERAL);
527 MAX_DIGIT INTEGER := 12;
528 NUMBER_TEXT VARCHAR2(240) := '';
529 CURRENT_SEGMENT VARCHAR2(80);
530 B_ZERO VARCHAR2(25) := 'Zero';
531 B_THOUSAND VARCHAR2(25) := 'Mil ';
532 THOUSAND NUMBER := POWER(10
533 ,3);
534 B_MILLION VARCHAR2(25) := 'Milh?o ';
535 B_MILLIONS VARCHAR2(25) := 'Milh?es ';
536 MILLION NUMBER := POWER(10
537 ,6);
538 B_BILLION VARCHAR2(25) := 'Bili? ';
539 B_BILLIONS VARCHAR2(25) := 'Bili?es ';
540 BILLION NUMBER := POWER(10
541 ,9);
542 BEGIN
543 IF NUMERAL >= POWER(10
544 ,MAX_DIGIT) THEN
545 RAISE NUMBER_TOO_LARGE;
546 END IF;
547 IF NUMERAL = 0 THEN
548 RETURN (B_ZERO);
549 END IF;
550 CURRENT_SEGMENT := TRUNC(NUMERAL / BILLION);
551 NUMERAL := NUMERAL - (CURRENT_SEGMENT * BILLION);
552 IF CURRENT_SEGMENT <> 0 THEN
553 IF CURRENT_SEGMENT = 1 THEN
554 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER2(CURRENT_SEGMENT) || B_BILLION;
555 ELSE
556 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER2(CURRENT_SEGMENT) || B_BILLIONS;
557 END IF;
558 END IF;
559 CURRENT_SEGMENT := TRUNC(NUMERAL / MILLION);
560 NUMERAL := NUMERAL - (CURRENT_SEGMENT * MILLION);
561 IF CURRENT_SEGMENT <> 0 THEN
562 IF CURRENT_SEGMENT = 1 THEN
563 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER2(CURRENT_SEGMENT) || B_MILLION;
564 ELSE
565 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER2(CURRENT_SEGMENT) || B_MILLIONS;
566 END IF;
567 END IF;
568 CURRENT_SEGMENT := TRUNC(NUMERAL / THOUSAND);
569 NUMERAL := NUMERAL - (CURRENT_SEGMENT * THOUSAND);
570 IF CURRENT_SEGMENT <> 0 THEN
571 IF CURRENT_SEGMENT = 1 THEN
572 NUMBER_TEXT := NUMBER_TEXT || B_THOUSAND;
573 ELSE
574 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER2(CURRENT_SEGMENT) || B_THOUSAND;
575 END IF;
576 END IF;
577 IF NUMERAL <> 0 THEN
578 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER2(NUMERAL);
579 END IF;
580 NUMBER_TEXT := SUBSTR(NUMBER_TEXT
581 ,1
582 ,1) || RTRIM(LOWER(SUBSTR(NUMBER_TEXT
583 ,2
584 ,NVL(LENGTH(NUMBER_TEXT)
585 ,0))));
586 RETURN (NUMBER_TEXT);
587 RETURN NULL;
588 EXCEPTION
589 WHEN NUMBER_TOO_LARGE THEN
590 RETURN (NULL);
591 WHEN OTHERS THEN
592 RETURN (NULL);
593 END CONVERT_NUMBER;
594
595 FUNCTION GET_WORD_VALUE(P_AMOUNT IN NUMBER
596 ,P_UNIT_SINGULAR IN VARCHAR2
597 ,P_UNIT_PLURAL IN VARCHAR2
598 ,P_SUB_UNIT_SINGULAR IN VARCHAR2
599 ,P_SUB_UNIT_PLURAL IN VARCHAR2
600 ,P_UNIT_RATIO IN NUMBER) RETURN VARCHAR2 IS
601 L_WORD_AMOUNT VARCHAR2(240) := CONVERT_NUMBER(TRUNC(P_AMOUNT));
602 L_DECIMAL_PART NUMBER := TRUNC((P_AMOUNT - TRUNC(P_AMOUNT)) * P_UNIT_RATIO);
603 L_WORD_AMOUNT2 VARCHAR2(240);
604 L_CURRENCY_WORD VARCHAR2(240);
605 L_LOG INTEGER;
606 FUNCTION MY_LOG(A IN INTEGER
607 ,B IN INTEGER) RETURN NUMBER IS
608 BEGIN
609 IF A <> 10 THEN
610 RETURN (NULL);
611 ELSIF B > 0 AND B <= 10 THEN
612 RETURN (1);
613 ELSIF B > 10 AND B <= 100 THEN
614 RETURN (2);
615 ELSIF B > 100 AND B <= 1000 THEN
616 RETURN (3);
617 ELSE
618 RETURN (NULL);
619 END IF;
620 RETURN NULL;
621 END MY_LOG;
622 BEGIN
623 L_LOG := MY_LOG(10
624 ,P_UNIT_RATIO);
625 IF (L_DECIMAL_PART > 0) THEN
626 L_WORD_AMOUNT2 := CONVERT_NUMBER(L_DECIMAL_PART);
627 SELECT
628 INITCAP(LOWER('e' || ' ' || L_WORD_AMOUNT2 || ' ' || DECODE(L_DECIMAL_PART
629 ,1
630 ,P_SUB_UNIT_SINGULAR
631 ,P_SUB_UNIT_PLURAL)))
632 INTO L_WORD_AMOUNT2
633 FROM
634 DUAL;
635 END IF;
636 IF P_UNIT_RATIO in (0,1) OR P_UNIT_RATIO IS NULL THEN
637 SELECT
638 INITCAP(LOWER(L_WORD_AMOUNT || ' ' || DECODE(TRUNC(P_AMOUNT)
639 ,1
640 ,P_UNIT_SINGULAR
641 ,P_UNIT_PLURAL)))
642 INTO L_CURRENCY_WORD
643 FROM
644 DUAL;
645 ELSE
646 SELECT
647 INITCAP(LOWER(L_WORD_AMOUNT || ' ' || DECODE(TRUNC(P_AMOUNT)
648 ,1
649 ,P_UNIT_SINGULAR
650 ,P_UNIT_PLURAL) || ' ' || L_WORD_AMOUNT2))
651 INTO L_CURRENCY_WORD
652 FROM
653 DUAL;
654 END IF;
655 RETURN (L_CURRENCY_WORD);
656 END GET_WORD_VALUE;
657
658 FUNCTION PRECISION(CUR_CODE IN VARCHAR2) RETURN NUMBER IS
659 L_PRECISION NUMBER;
660 BEGIN
661 SELECT
662 PRECISION
663 INTO L_PRECISION
664 FROM
665 FND_CURRENCIES_VL
666 WHERE CURRENCY_CODE = CUR_CODE;
667 RETURN (L_PRECISION);
668 EXCEPTION
669 WHEN OTHERS THEN
670 /*SRW.MESSAGE(999
671 ,'Error in procedure - precision')*/NULL;
672 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
673 END PRECISION;
674
675 FUNCTION CP_FUNC_CURRENCY_P RETURN VARCHAR2 IS
676 BEGIN
677 RETURN CP_FUNC_CURRENCY;
678 END CP_FUNC_CURRENCY_P;
679
680 FUNCTION CP_PRECISION_P RETURN NUMBER IS
681 BEGIN
682 RETURN CP_PRECISION;
683 END CP_PRECISION_P;
684
685 FUNCTION CP_STRUCT_NUM_P RETURN NUMBER IS
686 BEGIN
687 RETURN CP_STRUCT_NUM;
688 END CP_STRUCT_NUM_P;
689
690 FUNCTION CP_SOB_NAME_P RETURN VARCHAR2 IS
691 BEGIN
692 RETURN CP_SOB_NAME;
693 END CP_SOB_NAME_P;
694
695 FUNCTION CP_WHERE_RECEIPT_DATES_P RETURN VARCHAR2 IS
696 BEGIN
697 RETURN CP_WHERE_RECEIPT_DATES;
698 END CP_WHERE_RECEIPT_DATES_P;
699
700 FUNCTION CP_SUB_UNIT_PLURAL_P RETURN VARCHAR2 IS
701 BEGIN
702 RETURN CP_SUB_UNIT_PLURAL;
703 END CP_SUB_UNIT_PLURAL_P;
704
705 FUNCTION CP_SUB_UNIT_SINGULAR_P RETURN VARCHAR2 IS
706 BEGIN
707 RETURN CP_SUB_UNIT_SINGULAR;
708 END CP_SUB_UNIT_SINGULAR_P;
709
710 FUNCTION CP_UNIT_PLURAL_P RETURN VARCHAR2 IS
711 BEGIN
712 RETURN CP_UNIT_PLURAL;
713 END CP_UNIT_PLURAL_P;
714
715 FUNCTION CP_UNIT_SINGULAR_P RETURN VARCHAR2 IS
716 BEGIN
717 RETURN CP_UNIT_SINGULAR;
718 END CP_UNIT_SINGULAR_P;
719
720 FUNCTION CP_UNIT_RATIO_P RETURN NUMBER IS
721 BEGIN
722 RETURN CP_UNIT_RATIO;
723 END CP_UNIT_RATIO_P;
724
725 FUNCTION CP_SYSDATE_P RETURN VARCHAR2 IS
726 BEGIN
727 RETURN CP_SYSDATE;
728 END CP_SYSDATE_P;
729
730 FUNCTION CP_COUNTRY_CODE_P RETURN VARCHAR2 IS
731 BEGIN
732 RETURN CP_COUNTRY_CODE;
733 END CP_COUNTRY_CODE_P;
734
735 FUNCTION CP_DATE_FROM_P RETURN VARCHAR2 IS
736 BEGIN
737 RETURN CP_DATE_FROM;
738 END CP_DATE_FROM_P;
739
740 FUNCTION CP_DATE_TO_P RETURN VARCHAR2 IS
741 BEGIN
742 RETURN CP_DATE_TO;
743 END CP_DATE_TO_P;
744
745 FUNCTION CP_CATEGORY_P RETURN VARCHAR2 IS
746 BEGIN
747 RETURN CP_CATEGORY;
748 END CP_CATEGORY_P;
749
750 FUNCTION CP_DOC_SEQ_ID_P RETURN NUMBER IS
751 BEGIN
752 RETURN CP_DOC_SEQ_ID;
753 END CP_DOC_SEQ_ID_P;
754
755 FUNCTION CP_DOC_VALUE_P RETURN NUMBER IS
756 BEGIN
757 RETURN CP_DOC_VALUE;
758 END CP_DOC_VALUE_P;
759
760 FUNCTION CP_LETTER_DATE_P RETURN DATE IS
761 BEGIN
762 RETURN CP_LETTER_DATE;
763 END CP_LETTER_DATE_P;
764
765 FUNCTION CP_HR_ADDRESS1_P RETURN VARCHAR2 IS
766 BEGIN
767 RETURN CP_HR_ADDRESS1;
768 END CP_HR_ADDRESS1_P;
769
770 FUNCTION CP_HR_ADDRESS2_P RETURN VARCHAR2 IS
771 BEGIN
772 RETURN CP_HR_ADDRESS2;
773 END CP_HR_ADDRESS2_P;
774
775 FUNCTION CP_HR_ADDRESS3_P RETURN VARCHAR2 IS
776 BEGIN
777 RETURN CP_HR_ADDRESS3;
778 END CP_HR_ADDRESS3_P;
779
780 FUNCTION CP_HR_CITY_P RETURN VARCHAR2 IS
781 BEGIN
782 RETURN CP_HR_CITY;
783 END CP_HR_CITY_P;
784
785 FUNCTION CP_HR_FAX_P RETURN VARCHAR2 IS
786 BEGIN
787 RETURN CP_HR_FAX;
788 END CP_HR_FAX_P;
789
790 FUNCTION CP_HR_LOCATION_P RETURN VARCHAR2 IS
791 BEGIN
792 RETURN CP_HR_LOCATION;
793 END CP_HR_LOCATION_P;
794
795 FUNCTION CP_HR_TAX_ID_P RETURN VARCHAR2 IS
796 BEGIN
797 RETURN CP_HR_TAX_ID;
798 END CP_HR_TAX_ID_P;
799
800 FUNCTION CP_HR_TELEPHONE_P RETURN VARCHAR2 IS
801 BEGIN
802 RETURN CP_HR_TELEPHONE;
803 END CP_HR_TELEPHONE_P;
804
805 FUNCTION CP_HR_TELEPHONE3_P RETURN VARCHAR2 IS
806 BEGIN
807 RETURN CP_HR_TELEPHONE3;
808 END CP_HR_TELEPHONE3_P;
809
810 FUNCTION CP_HR_TERRITORY_P RETURN VARCHAR2 IS
811 BEGIN
812 RETURN CP_HR_TERRITORY;
813 END CP_HR_TERRITORY_P;
814
815 PROCEDURE JG_GET_SET_OF_BOOKS_INFO(SOBID IN NUMBER
816 ,COAID OUT NOCOPY NUMBER
817 ,SOBNAME OUT NOCOPY VARCHAR2
818 ,FUNC_CURR OUT NOCOPY VARCHAR2
819 ,ERRBUF OUT NOCOPY VARCHAR2) IS
820 BEGIN
821 JG_INFO.JG_GET_SET_OF_BOOKS_INFO(SOBID, COAID, SOBNAME, FUNC_CURR, ERRBUF);
822 /*STPROC.INIT('begin JG_INFO.JG_GET_SET_OF_BOOKS_INFO(:SOBID, :COAID, :SOBNAME, :FUNC_CURR, :ERRBUF); end;');
823 STPROC.BIND_I(SOBID);
824 STPROC.BIND_O(COAID);
825 STPROC.BIND_O(SOBNAME);
826 STPROC.BIND_O(FUNC_CURR);
827 STPROC.BIND_O(ERRBUF);
828 STPROC.EXECUTE;
829 STPROC.RETRIEVE(2
830 ,COAID);
831 STPROC.RETRIEVE(3
832 ,SOBNAME);
833 STPROC.RETRIEVE(4
834 ,FUNC_CURR);
835 STPROC.RETRIEVE(5
836 ,ERRBUF);*/
837 END JG_GET_SET_OF_BOOKS_INFO;
838
839 PROCEDURE JG_GET_BUD_OR_ENC_NAME(ACTUAL_TYPE IN VARCHAR2
840 ,TYPE_ID IN NUMBER
841 ,NAME OUT NOCOPY VARCHAR2
842 ,ERRBUF OUT NOCOPY VARCHAR2) IS
843 BEGIN
844 JG_INFO.JG_GET_BUD_OR_ENC_NAME(ACTUAL_TYPE, TYPE_ID, NAME, ERRBUF);
845 /*STPROC.INIT('begin JG_INFO.JG_GET_BUD_OR_ENC_NAME(:ACTUAL_TYPE, :TYPE_ID, :NAME, :ERRBUF); end;');
846 STPROC.BIND_I(ACTUAL_TYPE);
847 STPROC.BIND_I(TYPE_ID);
848 STPROC.BIND_O(NAME);
849 STPROC.BIND_O(ERRBUF);
850 STPROC.EXECUTE;
851 STPROC.RETRIEVE(3
852 ,NAME);
853 STPROC.RETRIEVE(4
854 ,ERRBUF);*/
855 END JG_GET_BUD_OR_ENC_NAME;
856
857 PROCEDURE JG_GET_LOOKUP_VALUE(LMODE IN VARCHAR2
858 ,CODE IN VARCHAR2
859 ,TYPE IN VARCHAR2
860 ,VALUE OUT NOCOPY VARCHAR2
861 ,ERRBUF OUT NOCOPY VARCHAR2) IS
862 BEGIN
863 JG_INFO.JG_GET_LOOKUP_VALUE(LMODE, CODE, TYPE, VALUE, ERRBUF);
864 /* STPROC.INIT('begin JG_INFO.JG_GET_LOOKUP_VALUE(:LMODE, :CODE, :TYPE, :VALUE, :ERRBUF); end;');
865 STPROC.BIND_I(LMODE);
866 STPROC.BIND_I(CODE);
867 STPROC.BIND_I(TYPE);
868 STPROC.BIND_O(VALUE);
869 STPROC.BIND_O(ERRBUF);
870 STPROC.EXECUTE;
871 STPROC.RETRIEVE(4
872 ,VALUE);
873 STPROC.RETRIEVE(5
874 ,ERRBUF);*/
875 END JG_GET_LOOKUP_VALUE;
876
877 PROCEDURE JG_GET_FIRST_PERIOD(APP_ID IN NUMBER
878 ,TSET_OF_BOOKS_ID IN NUMBER
879 ,TPERIOD_NAME IN VARCHAR2
880 ,TFIRST_PERIOD OUT NOCOPY VARCHAR2
881 ,ERRBUF OUT NOCOPY VARCHAR2) IS
882 BEGIN
883 JG_INFO.JG_GET_FIRST_PERIOD(APP_ID, TSET_OF_BOOKS_ID, TPERIOD_NAME, TFIRST_PERIOD, ERRBUF);
884 /*STPROC.INIT('begin JG_INFO.JG_GET_FIRST_PERIOD(:APP_ID, :TSET_OF_BOOKS_ID, :TPERIOD_NAME, :TFIRST_PERIOD, :ERRBUF); end;');
885 STPROC.BIND_I(APP_ID);
886 STPROC.BIND_I(TSET_OF_BOOKS_ID);
887 STPROC.BIND_I(TPERIOD_NAME);
888 STPROC.BIND_O(TFIRST_PERIOD);
889 STPROC.BIND_O(ERRBUF);
890 STPROC.EXECUTE;
891 STPROC.RETRIEVE(4
892 ,TFIRST_PERIOD);
893 STPROC.RETRIEVE(5
894 ,ERRBUF);*/
895 END JG_GET_FIRST_PERIOD;
896
897 PROCEDURE JG_GET_FIRST_PERIOD_OF_QUARTER(APP_ID IN NUMBER
898 ,TSET_OF_BOOKS_ID IN NUMBER
899 ,TPERIOD_NAME IN VARCHAR2
900 ,TFIRST_PERIOD OUT NOCOPY VARCHAR2
901 ,ERRBUF OUT NOCOPY VARCHAR2) IS
902 BEGIN
903 JG_INFO.JG_GET_FIRST_PERIOD_OF_QUARTER(APP_ID, TSET_OF_BOOKS_ID, TPERIOD_NAME, TFIRST_PERIOD, ERRBUF);
904 /*STPROC.INIT('begin JG_INFO.JG_GET_FIRST_PERIOD_OF_QUARTER(:APP_ID, :TSET_OF_BOOKS_ID, :TPERIOD_NAME, :TFIRST_PERIOD, :ERRBUF); end;');
905 STPROC.BIND_I(APP_ID);
906 STPROC.BIND_I(TSET_OF_BOOKS_ID);
907 STPROC.BIND_I(TPERIOD_NAME);
908 STPROC.BIND_O(TFIRST_PERIOD);
909 STPROC.BIND_O(ERRBUF);
910 STPROC.EXECUTE;
911 STPROC.RETRIEVE(4
912 ,TFIRST_PERIOD);
913 STPROC.RETRIEVE(5
914 ,ERRBUF);*/
915 END JG_GET_FIRST_PERIOD_OF_QUARTER;
916
917 FUNCTION JG_FORMAT_CURR_AMT(IN_PRECISION IN NUMBER
918 ,IN_AMOUNT_DISP IN VARCHAR2) RETURN VARCHAR2 IS
919 X0 VARCHAR2(2000);
920 BEGIN
921 X0 := JG_INFO.JG_FORMAT_CURR_AMT(IN_PRECISION, IN_AMOUNT_DISP);
922 /*STPROC.INIT('begin :X0 := JG_INFO.JG_FORMAT_CURR_AMT(:IN_PRECISION, :IN_AMOUNT_DISP); end;');
923 STPROC.BIND_O(X0);
924 STPROC.BIND_I(IN_PRECISION);
925 STPROC.BIND_I(IN_AMOUNT_DISP);
926 STPROC.EXECUTE;
927 STPROC.RETRIEVE(1
928 ,X0);*/
929 RETURN X0;
930 END JG_FORMAT_CURR_AMT;
931
932 FUNCTION GET_FORMAT_MASK(CURRENCY_CODE IN VARCHAR2
933 ,FIELD_LENGTH IN NUMBER) RETURN VARCHAR2 IS
934 X0 VARCHAR2(2000);
935 BEGIN
936 X0 := FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE, FIELD_LENGTH);
937 /*STPROC.INIT('begin :X0 := FND_CURRENCY.GET_FORMAT_MASK(:CURRENCY_CODE, :FIELD_LENGTH); end;');
938 STPROC.BIND_O(X0);
939 STPROC.BIND_I(CURRENCY_CODE);
940 STPROC.BIND_I(FIELD_LENGTH);
941 STPROC.EXECUTE;
942 STPROC.RETRIEVE(1
943 ,X0);*/
944 RETURN X0;
945 END GET_FORMAT_MASK;
946
947 FUNCTION SAFE_GET_FORMAT_MASK(CURRENCY_CODE IN VARCHAR2
948 ,FIELD_LENGTH IN NUMBER) RETURN VARCHAR2 IS
949 X0 VARCHAR2(2000);
950 BEGIN
951 X0 := FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURRENCY_CODE, FIELD_LENGTH);
952 /*STPROC.INIT('begin :X0 := FND_CURRENCY.SAFE_GET_FORMAT_MASK(:CURRENCY_CODE, :FIELD_LENGTH); end;');
953 STPROC.BIND_O(X0);
954 STPROC.BIND_I(CURRENCY_CODE);
955 STPROC.BIND_I(FIELD_LENGTH);
956 STPROC.EXECUTE;
957 STPROC.RETRIEVE(1
958 ,X0);*/
959 RETURN X0;
960 END SAFE_GET_FORMAT_MASK;
961
962 PROCEDURE GET_INFO(CURRENCY_CODE IN VARCHAR2
963 ,PRECISION OUT NOCOPY NUMBER
964 ,EXT_PRECISION OUT NOCOPY NUMBER
965 ,MIN_ACCT_UNIT OUT NOCOPY NUMBER) IS
966 BEGIN
967 FND_CURRENCY.GET_INFO(CURRENCY_CODE, PRECISION, EXT_PRECISION, MIN_ACCT_UNIT);
968 /*STPROC.INIT('begin FND_CURRENCY.GET_INFO(:CURRENCY_CODE, :PRECISION, :EXT_PRECISION, :MIN_ACCT_UNIT); end;');
969 STPROC.BIND_I(CURRENCY_CODE);
970 STPROC.BIND_O(PRECISION);
971 STPROC.BIND_O(EXT_PRECISION);
972 STPROC.BIND_O(MIN_ACCT_UNIT);
973 STPROC.EXECUTE;
974 STPROC.RETRIEVE(2
975 ,PRECISION);
976 STPROC.RETRIEVE(3
977 ,EXT_PRECISION);
978 STPROC.RETRIEVE(4
979 ,MIN_ACCT_UNIT);*/
980 END GET_INFO;
981
982 PROCEDURE BUILD_FORMAT_MASK(FORMAT_MASK OUT NOCOPY VARCHAR2
983 ,FIELD_LENGTH IN NUMBER
984 ,PRECISION IN NUMBER
985 ,MIN_ACCT_UNIT IN NUMBER
986 ,DISP_GRP_SEP IN BOOLEAN
987 ,NEG_FORMAT IN VARCHAR2
988 ,POS_FORMAT IN VARCHAR2) IS
989 --DISP_GRP_SEP BOOLEAN;
990 begin
991 --DISP_GRP_SEP := sys.diutil.int_to_bool(:DISP_GRP_SEP);
992 FND_CURRENCY.BUILD_FORMAT_MASK(FORMAT_MASK, FIELD_LENGTH, PRECISION, MIN_ACCT_UNIT, DISP_GRP_SEP, NEG_FORMAT, POS_FORMAT);
993 /*STPROC.INIT('declare DISP_GRP_SEP BOOLEAN; begin DISP_GRP_SEP := sys.diutil.int_to_bool(:DISP_GRP_SEP); FND_CURRENCY.BUILD_FORMAT_MASK(:FORMAT_MASK, :FIELD_LENGTH, :PRECISION, :MIN_ACCT_UNIT, DISP_GRP_SEP, :NEG_FORMAT, :POS_FORMAT); end;');
994 STPROC.BIND_I(DISP_GRP_SEP);
995 STPROC.BIND_O(FORMAT_MASK);
996 STPROC.BIND_I(FIELD_LENGTH);
997 STPROC.BIND_I(PRECISION);
998 STPROC.BIND_I(MIN_ACCT_UNIT);
999 STPROC.BIND_I(NEG_FORMAT);
1000 STPROC.BIND_I(POS_FORMAT);
1001 STPROC.EXECUTE;
1002 STPROC.RETRIEVE(2
1003 ,FORMAT_MASK);*/
1004 END BUILD_FORMAT_MASK;
1005
1006 PROCEDURE SAFE_BUILD_FORMAT_MASK(FORMAT_MASK OUT NOCOPY VARCHAR2
1007 ,FIELD_LENGTH IN NUMBER
1008 ,PRECISION IN NUMBER
1009 ,MIN_ACCT_UNIT IN NUMBER
1010 ,DISP_GRP_SEP IN BOOLEAN
1011 ,NEG_FORMAT IN VARCHAR2
1012 ,POS_FORMAT IN VARCHAR2) IS
1013 --DISP_GRP_SEP BOOLEAN;
1014 begin
1015 --DISP_GRP_SEP := sys.diutil.int_to_bool(:DISP_GRP_SEP);
1016 FND_CURRENCY.SAFE_BUILD_FORMAT_MASK(FORMAT_MASK, FIELD_LENGTH, PRECISION, MIN_ACCT_UNIT, DISP_GRP_SEP, NEG_FORMAT, POS_FORMAT);
1017 /*STPROC.INIT('declare DISP_GRP_SEP BOOLEAN; begin DISP_GRP_SEP := sys.diutil.int_to_bool(:DISP_GRP_SEP); FND_CURRENCY.SAFE_BUILD_FORMAT_MASK(:FORMAT_MASK, :FIELD_LENGTH, :PRECISION, :MIN_ACCT_UNIT, DISP_GRP_SEP, :NEG_FORMAT, :POS_FORMAT); end;');
1018 STPROC.BIND_I(DISP_GRP_SEP);
1019 STPROC.BIND_O(FORMAT_MASK);
1020 STPROC.BIND_I(FIELD_LENGTH);
1021 STPROC.BIND_I(PRECISION);
1022 STPROC.BIND_I(MIN_ACCT_UNIT);
1023 STPROC.BIND_I(NEG_FORMAT);
1024 STPROC.BIND_I(POS_FORMAT);
1025 STPROC.EXECUTE;
1026 STPROC.RETRIEVE(2
1027 ,FORMAT_MASK);*/
1028 END SAFE_BUILD_FORMAT_MASK;
1029
1030 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
1031 ,NAME IN VARCHAR2) IS
1032 BEGIN
1033 FND_MESSAGE.SET_NAME(APPLICATION, NAME);
1034 /*STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
1035 STPROC.BIND_I(APPLICATION);
1036 STPROC.BIND_I(NAME);
1037 STPROC.EXECUTE;*/
1038 END SET_NAME;
1039
1040 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
1041 ,VALUE IN VARCHAR2
1042 ,TRANSLATE IN BOOLEAN) IS
1043 --TRANSLATE BOOLEAN;
1044 begin
1045 --TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE);
1046 FND_MESSAGE.SET_TOKEN(TOKEN, VALUE, TRANSLATE);
1047 /* STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
1048 STPROC.BIND_I(TRANSLATE);
1049 STPROC.BIND_I(TOKEN);
1050 STPROC.BIND_I(VALUE);
1051 STPROC.EXECUTE;*/
1052 END SET_TOKEN;
1053
1054 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
1055 BEGIN
1056 FND_MESSAGE.RETRIEVE(MSGOUT);
1057 /*STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
1058 STPROC.BIND_O(MSGOUT);
1059 STPROC.EXECUTE;
1060 STPROC.RETRIEVE(1
1061 ,MSGOUT);*/
1062 END RETRIEVE;
1063
1064 PROCEDURE CLEAR IS
1065 BEGIN
1066 FND_MESSAGE.CLEAR;
1067 /*STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
1068 STPROC.EXECUTE;*/
1069 END CLEAR;
1070
1071 FUNCTION GET_STRING(APPIN IN VARCHAR2
1072 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
1073 X0 VARCHAR2(2000);
1074 BEGIN
1075 X0 := FND_MESSAGE.GET_STRING(APPIN, NAMEIN);
1076 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
1077 STPROC.BIND_O(X0);
1078 STPROC.BIND_I(APPIN);
1079 STPROC.BIND_I(NAMEIN);
1080 STPROC.EXECUTE;
1081 STPROC.RETRIEVE(1
1082 ,X0);*/
1083 RETURN X0;
1084 END GET_STRING;
1085
1086 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
1087 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
1088 X0 NUMBER;
1089 BEGIN
1090 X0 := FND_MESSAGE.GET_NUMBER(APPIN, NAMEIN);
1091 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
1092 STPROC.BIND_O(X0);
1093 STPROC.BIND_I(APPIN);
1094 STPROC.BIND_I(NAMEIN);
1095 STPROC.EXECUTE;
1096 STPROC.RETRIEVE(1
1097 ,X0);*/
1098 RETURN X0;
1099 END GET_NUMBER;
1100
1101 FUNCTION GET RETURN VARCHAR2 IS
1102 X0 VARCHAR2(2000);
1103 BEGIN
1104 X0 := FND_MESSAGE.GET;
1105 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
1106 STPROC.BIND_O(X0);
1107 STPROC.EXECUTE;
1108 STPROC.RETRIEVE(1
1109 ,X0);*/
1110 RETURN X0;
1111 END GET;
1112
1113 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
1114 X0 VARCHAR2(2000);
1115 BEGIN
1116 X0 := FND_MESSAGE.GET_ENCODED;
1117 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
1118 STPROC.BIND_O(X0);
1119 STPROC.EXECUTE;
1120 STPROC.RETRIEVE(1
1121 ,X0);*/
1122 RETURN X0;
1123 END GET_ENCODED;
1124
1125 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
1126 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
1127 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
1128 BEGIN
1129 FND_MESSAGE.PARSE_ENCODED(ENCODED_MESSAGE, APP_SHORT_NAME, MESSAGE_NAME);
1130 /*STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
1131 STPROC.BIND_I(ENCODED_MESSAGE);
1132 STPROC.BIND_O(APP_SHORT_NAME);
1133 STPROC.BIND_O(MESSAGE_NAME);
1134 STPROC.EXECUTE;
1135 STPROC.RETRIEVE(2
1136 ,APP_SHORT_NAME);
1137 STPROC.RETRIEVE(3
1138 ,MESSAGE_NAME);*/
1139 END PARSE_ENCODED;
1140
1141 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
1142 BEGIN
1143 FND_MESSAGE.SET_ENCODED(ENCODED_MESSAGE);
1144 /*STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
1145 STPROC.BIND_I(ENCODED_MESSAGE);
1146 STPROC.EXECUTE;*/
1147 END SET_ENCODED;
1148
1149 PROCEDURE RAISE_ERROR IS
1150 BEGIN
1151 FND_MESSAGE.RAISE_ERROR;
1152 /* STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
1153 STPROC.EXECUTE;*/
1154 END RAISE_ERROR;
1155
1156 FUNCTION GET_NEXT_SEQUENCE(APPID IN NUMBER
1157 ,CAT_CODE IN VARCHAR2
1158 ,SOBID IN NUMBER
1159 ,MET_CODE IN VARCHAR2
1160 ,TRX_DATE IN DATE
1161 ,DBSEQNM IN OUT NOCOPY VARCHAR2
1162 ,DBSEQID IN OUT NOCOPY INTEGER) RETURN NUMBER IS
1163 X0 NUMBER;
1164 BEGIN
1165 X0 := FND_SEQNUM.GET_NEXT_SEQUENCE(APPID, CAT_CODE, SOBID, MET_CODE, TRX_DATE, DBSEQNM, DBSEQID);
1166 /*STPROC.INIT('begin :X0 := FND_SEQNUM.GET_NEXT_SEQUENCE(:APPID, :CAT_CODE, :SOBID, :MET_CODE, :TRX_DATE, :DBSEQNM, :DBSEQID); end;');
1167 STPROC.BIND_O(X0);
1168 STPROC.BIND_I(APPID);
1169 STPROC.BIND_I(CAT_CODE);
1170 STPROC.BIND_I(SOBID);
1171 STPROC.BIND_I(MET_CODE);
1172 STPROC.BIND_I(TRX_DATE);
1173 STPROC.BIND_IO(DBSEQNM);
1174 STPROC.BIND_IO(DBSEQID);
1175 STPROC.EXECUTE;
1176 STPROC.RETRIEVE(1
1177 ,X0);
1178 STPROC.RETRIEVE(7
1179 ,DBSEQNM);
1180 STPROC.RETRIEVE(8
1181 ,DBSEQID);*/
1182 RETURN X0;
1183 END GET_NEXT_SEQUENCE;
1184
1185 PROCEDURE GET_SEQ_NAME(APPID IN NUMBER
1186 ,CAT_CODE IN VARCHAR2
1187 ,SOBID IN NUMBER
1188 ,MET_CODE IN VARCHAR2
1189 ,TRX_DATE IN DATE
1190 ,DBSEQNM OUT NOCOPY VARCHAR2
1191 ,DBSEQID OUT NOCOPY INTEGER
1192 ,SEQASSID OUT NOCOPY INTEGER) IS
1193 BEGIN
1194 FND_SEQNUM.GET_SEQ_NAME(APPID, CAT_CODE, SOBID, MET_CODE, TRX_DATE, DBSEQNM, DBSEQID, SEQASSID);
1195 /*STPROC.INIT('begin FND_SEQNUM.GET_SEQ_NAME(:APPID, :CAT_CODE, :SOBID, :MET_CODE, :TRX_DATE, :DBSEQNM, :DBSEQID, :SEQASSID); end;');
1196 STPROC.BIND_I(APPID);
1197 STPROC.BIND_I(CAT_CODE);
1198 STPROC.BIND_I(SOBID);
1199 STPROC.BIND_I(MET_CODE);
1200 STPROC.BIND_I(TRX_DATE);
1201 STPROC.BIND_O(DBSEQNM);
1202 STPROC.BIND_O(DBSEQID);
1203 STPROC.BIND_O(SEQASSID);
1204 STPROC.EXECUTE;
1205 STPROC.RETRIEVE(6
1206 ,DBSEQNM);
1207 STPROC.RETRIEVE(7
1208 ,DBSEQID);
1209 STPROC.RETRIEVE(8
1210 ,SEQASSID);*/
1211 END GET_SEQ_NAME;
1212
1213 FUNCTION GET_NEXT_AUTO_SEQ(DBSEQNM IN VARCHAR2) RETURN NUMBER IS
1214 X0 NUMBER;
1215 BEGIN
1216 X0 := FND_SEQNUM.GET_NEXT_AUTO_SEQ(DBSEQNM);
1217 /*STPROC.INIT('begin :X0 := FND_SEQNUM.GET_NEXT_AUTO_SEQ(:DBSEQNM); end;');
1218 STPROC.BIND_O(X0);
1219 STPROC.BIND_I(DBSEQNM);
1220 STPROC.EXECUTE;
1221 STPROC.RETRIEVE(1
1222 ,X0);*/
1223 RETURN X0;
1224 END GET_NEXT_AUTO_SEQ;
1225
1226 FUNCTION GET_NEXT_AUTO_SEQUENCE(APPID IN NUMBER
1227 ,CAT_CODE IN VARCHAR2
1228 ,SOBID IN NUMBER
1229 ,MET_CODE IN VARCHAR2
1230 ,TRX_DATE IN VARCHAR2) RETURN NUMBER IS
1231 X0 NUMBER;
1232 BEGIN
1233 X0 := FND_SEQNUM.GET_NEXT_AUTO_SEQUENCE(APPID, CAT_CODE, SOBID, MET_CODE, TRX_DATE);
1234 /*STPROC.INIT('begin :X0 := FND_SEQNUM.GET_NEXT_AUTO_SEQUENCE(:APPID, :CAT_CODE, :SOBID, :MET_CODE, :TRX_DATE); end;');
1235 STPROC.BIND_O(X0);
1236 STPROC.BIND_I(APPID);
1237 STPROC.BIND_I(CAT_CODE);
1238 STPROC.BIND_I(SOBID);
1239 STPROC.BIND_I(MET_CODE);
1240 STPROC.BIND_I(TRX_DATE);
1241 STPROC.EXECUTE;
1242 STPROC.RETRIEVE(1
1243 ,X0);*/
1244 RETURN X0;
1245 END GET_NEXT_AUTO_SEQUENCE;
1246
1247 FUNCTION GET_NEXT_AUTO_SEQUENCE(APPID IN NUMBER
1248 ,CAT_CODE IN VARCHAR2
1249 ,SOBID IN NUMBER
1250 ,MET_CODE IN VARCHAR2
1251 ,TRX_DATE IN DATE) RETURN NUMBER IS
1252 X0 NUMBER;
1253 BEGIN
1254 X0 := FND_SEQNUM.GET_NEXT_AUTO_SEQUENCE(APPID, CAT_CODE, SOBID, MET_CODE, TRX_DATE);
1255 /* STPROC.INIT('begin :X0 := FND_SEQNUM.GET_NEXT_AUTO_SEQUENCE(:APPID, :CAT_CODE, :SOBID, :MET_CODE, :TRX_DATE); end;');
1256 STPROC.BIND_O(X0);
1257 STPROC.BIND_I(APPID);
1258 STPROC.BIND_I(CAT_CODE);
1259 STPROC.BIND_I(SOBID);
1260 STPROC.BIND_I(MET_CODE);
1261 STPROC.BIND_I(TRX_DATE);
1262 STPROC.EXECUTE;
1263 STPROC.RETRIEVE(1
1264 ,X0);*/
1265 RETURN X0;
1266 END GET_NEXT_AUTO_SEQUENCE;
1267
1268 PROCEDURE CREATE_GAPLESS_SEQUENCES IS
1269 BEGIN
1270 FND_SEQNUM.CREATE_GAPLESS_SEQUENCES;
1271 /*STPROC.INIT('begin FND_SEQNUM.CREATE_GAPLESS_SEQUENCES; end;');
1272 STPROC.EXECUTE;*/
1273 END CREATE_GAPLESS_SEQUENCES;
1274
1275 /*FUNCTION CREATE_GAPLESS_SEQUENCE(SEQID IN NUMBER
1276 ,SEQASSID IN NUMBER) RETURN NUMBER IS
1277 X0 NUMBER;
1278 BEGIN
1279 X0 := FND_SEQNUM.CREATE_GAPLESS_SEQUENCE(SEQID,SEQASSID);
1280 STPROC.INIT('begin :X0 := FND_SEQNUM.CREATE_GAPLESS_SEQUENCE(:SEQID, :SEQASSID); end;');
1281 STPROC.BIND_O(X0);
1282 STPROC.BIND_I(SEQID);
1283 STPROC.BIND_I(SEQASSID);
1284 STPROC.EXECUTE;
1285 STPROC.RETRIEVE(1
1286 ,X0);
1287 RETURN X0;
1288 END CREATE_GAPLESS_SEQUENCE;*/
1289
1290 FUNCTION GET_NEXT_USER_SEQUENCE(FDS_USER_ID IN NUMBER
1291 ,SEQASSID IN NUMBER
1292 ,SEQID IN NUMBER) RETURN NUMBER IS
1293 X0 NUMBER;
1294 BEGIN
1295 X0 := FND_SEQNUM.GET_NEXT_USER_SEQUENCE(FDS_USER_ID, SEQASSID, SEQID);
1296 /*STPROC.INIT('begin :X0 := FND_SEQNUM.GET_NEXT_USER_SEQUENCE(:FDS_USER_ID, :SEQASSID, :SEQID); end;');
1297 STPROC.BIND_O(X0);
1298 STPROC.BIND_I(FDS_USER_ID);
1299 STPROC.BIND_I(SEQASSID);
1300 STPROC.BIND_I(SEQID);
1301 STPROC.EXECUTE;
1302 STPROC.RETRIEVE(1
1303 ,X0);*/
1304 RETURN X0;
1305 END GET_NEXT_USER_SEQUENCE;
1306
1307 END JG_JGZZRCAK_XMLP_PKG;
1308
1309