[Home] [Help]
PACKAGE BODY: APPS.JA_JAINCINI_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINCINI_XMLP_PKG AS
2 /* $Header: JAINCINIB.pls 120.1 2007/12/25 16:14:44 dwkrishn noship $ */
3 FUNCTION CF_2FORMULA(SUM_DUTY IN NUMBER) RETURN VARCHAR2 IS
4 TEMP VARCHAR2(2000);
5 TMP NUMBER := SUM_DUTY;
6 BEGIN
7 TEMP := 'sum of Rs. (in words) ' || RTRIM(CONVERT_NUMBER(TMP)) || ' ';
8 RETURN (TEMP);
9 END CF_2FORMULA;
10
11 FUNCTION CONVERT_NUMBER(IN_NUMERAL IN INTEGER := 0) RETURN VARCHAR2 IS
12 NUMBER_TOO_LARGE EXCEPTION;
13 NUMERAL INTEGER := ABS(IN_NUMERAL);
14 MAX_DIGIT INTEGER := 12;
15 NUMBER_TEXT VARCHAR2(240) := '';
16 CURRENT_SEGMENT VARCHAR2(80);
17 B_ZERO VARCHAR2(25);
18 B_THOUSAND VARCHAR2(25);
19 THOUSAND NUMBER := POWER(10
20 ,3);
21 B_LAKH VARCHAR2(25);
22 LAKH NUMBER := POWER(10
23 ,5);
24 B_CRORE VARCHAR2(25);
25 CRORE NUMBER := POWER(10
26 ,7);
27 LV_CRORE CONSTANT VARCHAR2(10) DEFAULT 'CRORE';
28 LV_LAKH CONSTANT VARCHAR2(10) DEFAULT 'LAKH';
29 LV_THOUSAND CONSTANT VARCHAR2(10) DEFAULT 'THOUSAND';
30 LV_ZERO CONSTANT VARCHAR2(10) DEFAULT 'ZERO';
31 LV_NLS_TRANS CONSTANT VARCHAR2(30) DEFAULT 'JAI_NLS_TRANSLATION';
32 FUNCTION CONVERT_NUMBER(SEGMENT IN NUMBER) RETURN VARCHAR2 IS
33 VALUE_TEXT VARCHAR2(80);
34 BEGIN
35 VALUE_TEXT := TO_CHAR(TO_DATE(SEGMENT
36 ,'YYYY')
37 ,'Yyyysp');
38 RETURN (VALUE_TEXT);
39 END CONVERT_NUMBER;
40 BEGIN
41 IF NUMERAL >= POWER(10
42 ,MAX_DIGIT) THEN
43 RAISE NUMBER_TOO_LARGE;
44 END IF;
45 SELECT
46 ' ' || LC1.MEANING || ' ',
47 ' ' || LC2.MEANING || ' ',
48 ' ' || LC3.MEANING || ' ',
49 ' ' || LC4.MEANING
50 INTO B_CRORE,B_LAKH,B_THOUSAND,B_ZERO
51 FROM
52 JA_LOOKUPS LC1,
53 JA_LOOKUPS LC2,
54 JA_LOOKUPS LC3,
55 JA_LOOKUPS LC4
56 WHERE LC1.LOOKUP_CODE = LV_CRORE
57 AND LC1.LOOKUP_TYPE = LV_NLS_TRANS
58 AND LC2.LOOKUP_CODE = LV_LAKH
59 AND LC2.LOOKUP_TYPE = LV_NLS_TRANS
60 AND LC3.LOOKUP_CODE = LV_THOUSAND
61 AND LC3.LOOKUP_TYPE = LV_NLS_TRANS
62 AND LC4.LOOKUP_CODE = LV_ZERO
63 AND LC4.LOOKUP_TYPE = LV_NLS_TRANS;
64 IF NUMERAL = 0 THEN
65 RETURN (B_ZERO);
66 END IF;
67 CURRENT_SEGMENT := TRUNC(NUMERAL / CRORE);
68 NUMERAL := NUMERAL - (CURRENT_SEGMENT * CRORE);
69 IF CURRENT_SEGMENT <> 0 THEN
70 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER(CURRENT_SEGMENT) || B_CRORE;
71 END IF;
72 CURRENT_SEGMENT := TRUNC(NUMERAL / LAKH);
73 NUMERAL := NUMERAL - (CURRENT_SEGMENT * LAKH);
74 IF CURRENT_SEGMENT <> 0 THEN
75 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER(CURRENT_SEGMENT) || B_LAKH;
76 END IF;
77 CURRENT_SEGMENT := TRUNC(NUMERAL / THOUSAND);
78 NUMERAL := NUMERAL - (CURRENT_SEGMENT * THOUSAND);
79 IF CURRENT_SEGMENT <> 0 THEN
80 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER(CURRENT_SEGMENT) || B_THOUSAND;
81 END IF;
82 IF NUMERAL <> 0 THEN
83 NUMBER_TEXT := NUMBER_TEXT || CONVERT_NUMBER(NUMERAL);
84 END IF;
85 NUMBER_TEXT := SUBSTR(NUMBER_TEXT
86 ,1
87 ,1) || RTRIM(LOWER(SUBSTR(NUMBER_TEXT
88 ,2
89 ,NVL(LENGTH(NUMBER_TEXT)
90 ,0))));
91 RETURN (NUMBER_TEXT);
92 RETURN NULL;
93 EXCEPTION
94 WHEN NUMBER_TOO_LARGE THEN
95 RETURN (NULL);
96 WHEN OTHERS THEN
97 RETURN (NULL);
98 END CONVERT_NUMBER;
99
100 FUNCTION CF_3FORMULA RETURN NUMBER IS
101 V_EXT_AMT NUMBER;
102 CURSOR EXTENDED_AMOUNT_CUR IS
103 SELECT
104 SUM(EXTENDED_AMOUNT)
105 FROM
106 RA_CUSTOMER_TRX_LINES_ALL
107 WHERE CUSTOMER_TRX_ID = NVL(P_INVOICE_NO
108 ,CUSTOMER_TRX_ID);
109 BEGIN
110 OPEN EXTENDED_AMOUNT_CUR;
111 FETCH EXTENDED_AMOUNT_CUR
112 INTO V_EXT_AMT;
113 CLOSE EXTENDED_AMOUNT_CUR;
114 RETURN (V_EXT_AMT);
115 END CF_3FORMULA;
116
117 FUNCTION CF_4FORMULA(CF_3 IN NUMBER) RETURN VARCHAR2 IS
118 TEMP VARCHAR2(2000);
119 TMP NUMBER := CF_3;
120 V_CHR VARCHAR2(2000);
121 BEGIN
122 V_CHR := CONVERT_NUMBER(TMP);
123 TEMP := 'Rs.(in words)' || RTRIM(CONVERT_NUMBER(TMP)) || ' ';
124 RETURN (TEMP);
125 END CF_4FORMULA;
126
127 FUNCTION CF_5FORMULA RETURN VARCHAR2 IS
128 TEMP VARCHAR2(2000);
129 BEGIN
130 TEMP := 'Name and Address of Supplier, if he is not a Manufacturer/ Importer';
131 RETURN (TEMP);
132 END CF_5FORMULA;
133
134 FUNCTION CF_6FORMULA RETURN VARCHAR2 IS
135 TEMP VARCHAR2(30);
136 CURSOR VEHICLE_NUM IS
137 SELECT
138 CARRIER_ID
139 FROM
140 WSH_NEW_DELIVERIES A,
141 RA_CUSTOMER_TRX_ALL B
142 WHERE B.CUSTOMER_TRX_ID = P_INVOICE_NO
143 AND DELIVERY_ID = TO_NUMBER(B.INTERFACE_HEADER_ATTRIBUTE3);
144 BEGIN
145 OPEN VEHICLE_NUM;
146 FETCH VEHICLE_NUM
147 INTO TEMP;
148 CLOSE VEHICLE_NUM;
149 RETURN (TEMP);
150 END CF_6FORMULA;
151
152 FUNCTION CF_7FORMULA RETURN DATE IS
153 TEMP DATE;
154 CURSOR VEHICLE_NUM IS
155 SELECT
156 CONFIRM_DATE
157 FROM
158 WSH_NEW_DELIVERIES A,
159 RA_CUSTOMER_TRX_ALL B
160 WHERE B.CUSTOMER_TRX_ID = P_INVOICE_NO
161 AND DELIVERY_ID = TO_NUMBER(B.INTERFACE_HEADER_ATTRIBUTE3);
162 BEGIN
163 OPEN VEHICLE_NUM;
164 FETCH VEHICLE_NUM
165 INTO TEMP;
166 CLOSE VEHICLE_NUM;
167 RETURN (TEMP);
168 END CF_7FORMULA;
169
170 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
171 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
172 SELECT
173 CONCURRENT_PROGRAM_ID,
174 NVL(ENABLE_TRACE
175 ,'N')
176 FROM
177 FND_CONCURRENT_REQUESTS
178 WHERE REQUEST_ID = P_REQUEST_ID;
179 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
180 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
181 BEGIN
182 /*SRW.MESSAGE(1275
183 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
184 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
185 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
186 BEGIN
187 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
188 FETCH C_PROGRAM_ID
189 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
190 CLOSE C_PROGRAM_ID;
191 /*SRW.MESSAGE(1275
192 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
193 IF V_ENABLE_TRACE = 'Y' THEN
194 EXECUTE IMMEDIATE
195 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
196 END IF;
197 RETURN (TRUE);
198 EXCEPTION
199 WHEN OTHERS THEN
200 /*SRW.MESSAGE(1275
201 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
202 END;
203 END BEFOREREPORT;
204
205 FUNCTION AFTERREPORT RETURN BOOLEAN IS
206 BEGIN
207 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
208 RETURN (TRUE);
209 END AFTERREPORT;
210
211 FUNCTION M_1FORMATTRIGGER RETURN NUMBER IS
212 CURSOR EC_CODE_CUR IS
213 SELECT INSTR(TR_EC_CODE,'D')
214 FROM JAI_CMN_INVENTORY_ORGS
215 WHERE ORGANIZATION_ID = P_ORG_ID AND LOCATION_ID = P_LOC_ID;
216 V_TR_EC_CODE NUMBER;
217 BEGIN
218 OPEN EC_CODE_CUR;
219 FETCH EC_CODE_CUR INTO V_TR_EC_CODE;
220 CLOSE EC_CODE_CUR;
221 RETURN (V_TR_EC_CODE);
222 /*IF V_TR_EC_CODE <> 0 THEN
223 RETURN (TRUE);
224 ELSE
225 RETURN (FALSE);
226 END IF; */
227 RETURN NULL; END;
228
229 FUNCTION M_3_GRPFR1FORMATTRIGGER RETURN NUMBER IS
230 CURSOR EC_CODE_CUR IS
231 SELECT INSTR(TR_EC_CODE,'D')
232 FROM JAI_CMN_INVENTORY_ORGS
233 WHERE ORGANIZATION_ID =P_ORG_ID AND LOCATION_ID =P_LOC_ID;
234 V_TR_EC_CODE NUMBER;
235
236 BEGIN
237 OPEN EC_CODE_CUR;
238 FETCH EC_CODE_CUR INTO V_TR_EC_CODE;
239 CLOSE EC_CODE_CUR;
240 RETURN (V_TR_EC_CODE);
241 /*IF V_TR_EC_CODE = 0 THEN
242 RETURN (TRUE);
243 ELSE
244 RETURN (FALSE);
245 END IF; */
246 RETURN NULL; END;
247
248 FUNCTION M_2FORMATTRIGGER RETURN NUMBER IS
249 CURSOR EC_CODE_CUR IS
250 SELECT INSTR(TR_EC_CODE,'D')
251 FROM JAI_CMN_INVENTORY_ORGS
252 WHERE ORGANIZATION_ID =P_ORG_ID AND LOCATION_ID = P_LOC_ID;
253 V_TR_EC_CODE NUMBER;
254
255 BEGIN
256 OPEN EC_CODE_CUR;
257 FETCH EC_CODE_CUR INTO V_TR_EC_CODE;
258 CLOSE EC_CODE_CUR;
259 RETURN (V_TR_EC_CODE);
260 /*IF V_TR_EC_CODE = 0 THEN
261 RETURN (TRUE);
262 ELSE
263 RETURN (FALSE);
264 END IF; */
265
266 RETURN NULL; END;
267
268 END JA_JAINCINI_XMLP_PKG;
269
270