[Home] [Help]
PACKAGE BODY: APPS.JA_JAINCVAT_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINCVAT_XMLP_PKG AS
2 /* $Header: JAINCVATB.pls 120.1 2007/12/25 16:16:36 dwkrishn noship $ */
3 FUNCTION CF_PERCENTFORMULA(CENVAT_CREDIT_TAKEN IN NUMBER
4 ,DUTY_PAYABLE IN NUMBER) RETURN NUMBER IS
5 BEGIN
6 RETURN (ROUND(NVL(CENVAT_CREDIT_TAKEN
7 ,0) / NVL(DUTY_PAYABLE
8 ,1) * 100
9 ,2));
10 RETURN NULL;
11 EXCEPTION
12 WHEN OTHERS THEN
13 RETURN (0);
14 END CF_PERCENTFORMULA;
15 FUNCTION CF_ORGANIZATION_NAMEFORMULA RETURN CHAR IS
16 V_ORGANIZATION_NAME ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
17 BEGIN
18 SELECT
19 ORGANIZATION_NAME
20 INTO V_ORGANIZATION_NAME
21 FROM
22 ORG_ORGANIZATION_DEFINITIONS
23 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
24 RETURN (V_ORGANIZATION_NAME);
25 EXCEPTION
26 WHEN OTHERS THEN
27 RETURN NULL;
28 END CF_ORGANIZATION_NAMEFORMULA;
29 FUNCTION CF_LOCATION_NAMEFORMULA RETURN CHAR IS
30 V_LOCATION_NAME HR_LOCATIONS.LOCATION_CODE%TYPE;
31 BEGIN
32 SELECT
33 LOCATION_CODE
34 INTO V_LOCATION_NAME
35 FROM
36 HR_LOCATIONS
37 WHERE LOCATION_ID = P_LOCATION_ID;
38 RETURN (V_LOCATION_NAME);
39 EXCEPTION
40 WHEN OTHERS THEN
41 RETURN NULL;
42 END CF_LOCATION_NAMEFORMULA;
43 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
44 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
45 SELECT
46 CONCURRENT_PROGRAM_ID,
47 NVL(ENABLE_TRACE
48 ,'N')
49 FROM
50 FND_CONCURRENT_REQUESTS
51 WHERE REQUEST_ID = P_REQUEST_ID;
52 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
53 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
54 BEGIN
55 /*SRW.MESSAGE(1275
56 ,'Report Version is 120.3 Last modified date is 23/11/2006')*/NULL;
57 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
58 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
59 BEGIN
60 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
61 FETCH C_PROGRAM_ID
62 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
63 CLOSE C_PROGRAM_ID;
64 /*SRW.MESSAGE(1275
65 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
66 IF V_ENABLE_TRACE = 'Y' THEN
67 EXECUTE IMMEDIATE
68 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
69 END IF;
70 EXCEPTION
71 WHEN OTHERS THEN
72 /*SRW.MESSAGE(1275
73 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
74 END;
75 RETURN (TRUE);
76 END BEFOREREPORT;
77 FUNCTION CF_CESSFORMULA(REGISTER_ID IN NUMBER
78 ,EXCISE_INVOICE_NO IN VARCHAR2
79 ,BALANCE_CESS IN NUMBER) RETURN NUMBER IS
80 LN_ROUNDING_ID NUMBER;
81 LN_EXCISE NUMBER;
82 LN_EXCISE_ROUNDED_BAL NUMBER;
86 SELECT
83 LV_TAX_TYPE_EXC_EDU_CESS CONSTANT VARCHAR2(30) DEFAULT 'EXCISE_EDUCATION_CESS';
84 LV_TAX_TYPE_CVD_EDU_CESS CONSTANT VARCHAR2(30) DEFAULT 'CVD_EDUCATION_CESS';
85 CURSOR CUR_ROUNDING_ID IS
87 ROUNDING_ID
88 FROM
89 JAI_CMN_RG_23AC_II_TRXS
90 WHERE REGISTER_ID = REGISTER_ID;
91 CURSOR CUR_EXCISE IS
92 SELECT
93 ROUNDED_EXCISE_EDU_CESS + ROUNDED_CVD_EDU_CESS,
94 ( EXCISE_EDU_CESS + CVD_EDU_CESS - ROUNDED_EXCISE_EDU_CESS - ROUNDED_CVD_EDU_CESS ) BAL
95 FROM
96 JAI_CMN_RG_ROUND_HDRS
97 WHERE EXCISE_INVOICE_NO = EXCISE_INVOICE_NO
98 AND ROUNDING_ID = LN_ROUNDING_ID;
99 CURSOR CUR_RG_EXCISE IS
100 SELECT
101 NVL(SUM(CREDIT)
102 ,0)
103 FROM
104 JAI_CMN_RG_OTHERS
105 WHERE SOURCE_REGISTER_ID = REGISTER_ID
106 AND SOURCE_TYPE = 1
107 AND TAX_TYPE in ( LV_TAX_TYPE_EXC_EDU_CESS , LV_TAX_TYPE_CVD_EDU_CESS );
108 BEGIN
109 OPEN CUR_ROUNDING_ID;
110 FETCH CUR_ROUNDING_ID
111 INTO LN_ROUNDING_ID;
112 CLOSE CUR_ROUNDING_ID;
113 LN_EXCISE_ROUNDED_BAL := 0;
114 IF LN_ROUNDING_ID IS NOT NULL AND LN_ROUNDING_ID <> -99999 THEN
115 OPEN CUR_EXCISE;
116 FETCH CUR_EXCISE
117 INTO LN_EXCISE,LN_EXCISE_ROUNDED_BAL;
118 CLOSE CUR_EXCISE;
119 ELSE
120 OPEN CUR_RG_EXCISE;
121 FETCH CUR_RG_EXCISE
122 INTO LN_EXCISE;
123 CLOSE CUR_RG_EXCISE;
124 END IF;
125 CP_BALANCE_CESS := BALANCE_CESS + LN_EXCISE_ROUNDED_BAL;
126 RETURN LN_EXCISE;
127 EXCEPTION
128 WHEN OTHERS THEN
129 RETURN NULL;
130 END CF_CESSFORMULA;
131 FUNCTION CF_CENVAT_CREDIT_TAKENFORMULA(REGISTER_ID IN NUMBER
132 ,EXCISE_INVOICE_NO IN VARCHAR2
133 ,CENVAT_CREDIT_TAKEN IN NUMBER
134 ,ADDITIONAL_CVD IN NUMBER
135 ,BALANCE_CREDIT IN NUMBER) RETURN NUMBER IS
136 LN_ROUNDING_ID NUMBER;
137 LN_CENVAT NUMBER;
138 LN_CENVAT_ROUNDED_BAL NUMBER;
139 LN_ROUNDED_ADDCVD NUMBER;
140 CURSOR CUR_ROUNDING_ID IS
141 SELECT
142 ROUNDING_ID
143 FROM
144 JAI_CMN_RG_23AC_II_TRXS
145 WHERE REGISTER_ID = REGISTER_ID;
146 CURSOR CUR_CENVAT IS
147 SELECT
148 ROUNDED_BASIC_ED + ROUNDED_ADDITIONAL_ED + ROUNDED_OTHER_ED,
149 NVL(ROUNDED_ADDITIONAL_CVD
150 ,0),
151 ( BASIC_ED + ADDITIONAL_ED + OTHER_ED ) - ( ROUNDED_BASIC_ED + ROUNDED_ADDITIONAL_ED + ROUNDED_OTHER_ED )
152 FROM
153 JAI_CMN_RG_ROUND_HDRS
154 WHERE EXCISE_INVOICE_NO = EXCISE_INVOICE_NO
155 AND ROUNDING_ID = LN_ROUNDING_ID;
156 BEGIN
157 OPEN CUR_ROUNDING_ID;
158 FETCH CUR_ROUNDING_ID
159 INTO LN_ROUNDING_ID;
160 CLOSE CUR_ROUNDING_ID;
161 LN_CENVAT_ROUNDED_BAL := 0;
162 IF LN_ROUNDING_ID IS NOT NULL AND LN_ROUNDING_ID <> -99999 THEN
163 OPEN CUR_CENVAT;
164 FETCH CUR_CENVAT
165 INTO LN_CENVAT,LN_ROUNDED_ADDCVD,LN_CENVAT_ROUNDED_BAL;
166 CLOSE CUR_CENVAT;
167 ELSE
168 LN_CENVAT := CENVAT_CREDIT_TAKEN;
169 LN_ROUNDED_ADDCVD := ADDITIONAL_CVD;
170 END IF;
171 CP_BALANCE_CREDIT := BALANCE_CREDIT + LN_CENVAT_ROUNDED_BAL;
172 CP_ADDITIONAL_CVD := NVL(LN_ROUNDED_ADDCVD
173 ,0);
174 RETURN LN_CENVAT;
175 EXCEPTION
176 WHEN OTHERS THEN
177 RETURN NULL;
178 END CF_CENVAT_CREDIT_TAKENFORMULA;
179 FUNCTION AFTERREPORT RETURN BOOLEAN IS
180 BEGIN
181 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
182 RETURN (TRUE);
183 END AFTERREPORT;
184 FUNCTION CF_SH_CESS_CREDIT_TAKENFORMULA(REGISTER_ID IN NUMBER
185 ,EXCISE_INVOICE_NO IN VARCHAR2) RETURN NUMBER IS
186 LN_ROUNDING_ID NUMBER;
187 LN_SH_EXCISE NUMBER;
188 LN_SH_EXCISE_ROUNDED_BAL NUMBER;
189 CURSOR CUR_ROUNDING_ID IS
190 SELECT
191 ROUNDING_ID
192 FROM
193 JAI_CMN_RG_23AC_II_TRXS
194 WHERE REGISTER_ID = REGISTER_ID;
195 CURSOR CUR_EXCISE IS
196 SELECT
197 ROUNDED_SH_EXCISE_EDU_CESS + ROUNDED_SH_CVD_EDU_CESS,
198 ( SH_EXCISE_EDU_CESS + SH_CVD_EDU_CESS - ROUNDED_SH_EXCISE_EDU_CESS - ROUNDED_SH_CVD_EDU_CESS ) BAL
199 FROM
200 JAI_CMN_RG_ROUND_HDRS
201 WHERE EXCISE_INVOICE_NO = EXCISE_INVOICE_NO
202 AND ROUNDING_ID = LN_ROUNDING_ID;
203 CURSOR CUR_RG_EXCISE IS
204 SELECT
205 NVL(SUM(CREDIT)
206 ,0)
207 FROM
208 JAI_CMN_RG_OTHERS
209 WHERE SOURCE_REGISTER_ID = REGISTER_ID
210 AND SOURCE_TYPE = 1
211 AND TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
212 BEGIN
213 OPEN CUR_ROUNDING_ID;
214 FETCH CUR_ROUNDING_ID
215 INTO LN_ROUNDING_ID;
216 CLOSE CUR_ROUNDING_ID;
217 LN_SH_EXCISE_ROUNDED_BAL := 0;
218 IF NVL(LN_ROUNDING_ID
219 ,0) <> 0 AND LN_ROUNDING_ID <> -99999 THEN
220 OPEN CUR_EXCISE;
221 FETCH CUR_EXCISE
222 INTO LN_SH_EXCISE,LN_SH_EXCISE_ROUNDED_BAL;
223 CLOSE CUR_EXCISE;
224 ELSE
225 OPEN CUR_RG_EXCISE;
226 FETCH CUR_RG_EXCISE
227 INTO LN_SH_EXCISE;
228 CLOSE CUR_RG_EXCISE;
229 END IF;
230 RETURN LN_SH_EXCISE;
231 EXCEPTION
232 WHEN OTHERS THEN
233 RETURN NULL;
234 END CF_SH_CESS_CREDIT_TAKENFORMULA;
235 FUNCTION CF_QUANTITYFORMULA(TRANSACTION_ID IN NUMBER
236 ,QUANTITY IN NUMBER
237 ,REFERENCE_NUM IN VARCHAR2
238 ,DUTY_PAYABLE IN NUMBER
239 ,CF_CENVAT_CREDIT_TAKEN IN NUMBER
240 ,CF_CESS_CREDIT_TAKEN IN NUMBER
241 ,CF_SH_CESS_CREDIT_TAKEN IN NUMBER) RETURN NUMBER IS
242 CURSOR C_CESS_AMOUNT IS
243 SELECT
244 SUM(NVL(CREDIT
245 ,0))
246 FROM
247 JAI_CMN_RG_OTHERS
248 WHERE SOURCE_TYPE = 4
249 AND SOURCE_REGISTER_ID = TRANSACTION_ID
250 AND TAX_TYPE IN ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' );
251 CURSOR C_SH_CESS_AMOUNT IS
252 SELECT
253 SUM(NVL(CREDIT
254 ,0))
255 FROM
256 JAI_CMN_RG_OTHERS
257 WHERE SOURCE_TYPE = 4
258 AND SOURCE_REGISTER_ID = TRANSACTION_ID
259 AND TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
260 LN_CORRECTED_QTY NUMBER;
261 LV_EXC_FLAG VARCHAR2(1);
262 LN_REFERENCE_NUM NUMBER;
263 LN_CESS_AMT NUMBER;
264 LN_SH_CESS_AMT NUMBER;
265 BEGIN
266 LN_CORRECTED_QTY := NVL((QUANTITY + NULL)
267 ,0);
268 BEGIN
269 LN_REFERENCE_NUM := TO_NUMBER(REFERENCE_NUM);
270 LV_EXC_FLAG := 'A';
271 /*SRW.MESSAGE(1275
272 ,' reference_num:' || REFERENCE_NUM)*/NULL;
273 EXCEPTION
274 WHEN OTHERS THEN
275 LV_EXC_FLAG := 'C';
276 /*SRW.MESSAGE(1275
277 ,'Exception ' || SQLERRM || ' occurred and is handled')*/NULL;
278 /*SRW.MESSAGE(1275
279 ,' reference_num:' || REFERENCE_NUM)*/NULL;
280 END;
281 OPEN C_CESS_AMOUNT;
282 FETCH C_CESS_AMOUNT
283 INTO LN_CESS_AMT;
284 CLOSE C_CESS_AMOUNT;
285 OPEN C_SH_CESS_AMOUNT;
286 FETCH C_SH_CESS_AMOUNT
287 INTO LN_SH_CESS_AMT;
288 CLOSE C_SH_CESS_AMOUNT;
289 IF LV_EXC_FLAG = 'A' THEN
290 LN_CORRECTED_QTY := QUANTITY - LN_CORRECTED_QTY;
291 CP_DUTY_PAYABLE := ROUND(DUTY_PAYABLE * (LN_CORRECTED_QTY / QUANTITY));
292 CP_CESS_PAYABLE := ROUND(LN_CESS_AMT * (LN_CORRECTED_QTY / QUANTITY));
293 CP_SH_CESS_PAYABLE := ROUND(LN_SH_CESS_AMT * (LN_CORRECTED_QTY / QUANTITY));
294 CP_CENVAT_CREDIT_TAKEN := CP_DUTY_PAYABLE;
295 CP_CESS_CREDIT_TAKEN := CP_CESS_PAYABLE;
296 CP_SH_CESS_CREDIT_TAKEN := CP_SH_CESS_PAYABLE;
297 ELSIF LV_EXC_FLAG = 'C' THEN
298 CP_DUTY_PAYABLE := ROUND(DUTY_PAYABLE * (LN_CORRECTED_QTY / QUANTITY));
299 CP_CESS_PAYABLE := ROUND(LN_CESS_AMT * (LN_CORRECTED_QTY / QUANTITY));
300 CP_SH_CESS_PAYABLE := ROUND(LN_SH_CESS_AMT * (LN_CORRECTED_QTY / QUANTITY));
301 CP_CENVAT_CREDIT_TAKEN := ROUND(CF_CENVAT_CREDIT_TAKEN * (LN_CORRECTED_QTY / QUANTITY));
302 CP_ADDITIONAL_CVD := ROUND(CP_ADDITIONAL_CVD * (LN_CORRECTED_QTY / QUANTITY));
303 CP_CESS_CREDIT_TAKEN := ROUND(CF_CESS_CREDIT_TAKEN * (LN_CORRECTED_QTY / QUANTITY));
304 CP_SH_CESS_CREDIT_TAKEN := ROUND(CF_SH_CESS_CREDIT_TAKEN * (LN_CORRECTED_QTY / QUANTITY));
305 END IF;
306 CP_BALANCE_CREDIT := CP_DUTY_PAYABLE - CP_CENVAT_CREDIT_TAKEN - CP_ADDITIONAL_CVD;
307 CP_BALANCE_CESS := CP_CESS_PAYABLE - CP_CESS_CREDIT_TAKEN;
308 CP_BALANCE_SH_CESS := CP_SH_CESS_PAYABLE - CP_SH_CESS_CREDIT_TAKEN;
309 RETURN LN_CORRECTED_QTY;
310 END CF_QUANTITYFORMULA;
311 FUNCTION CP_CENVAT_CREDIT_TAKEN_P RETURN NUMBER IS
312 BEGIN
313 RETURN CP_CENVAT_CREDIT_TAKEN;
314 END CP_CENVAT_CREDIT_TAKEN_P;
315 FUNCTION CP_DUTY_PAYABLE_P RETURN NUMBER IS
316 BEGIN
317 RETURN CP_DUTY_PAYABLE;
318 END CP_DUTY_PAYABLE_P;
319 FUNCTION CP_BALANCE_CESS_P RETURN NUMBER IS
320 BEGIN
321 RETURN CP_BALANCE_CESS;
322 END CP_BALANCE_CESS_P;
323 FUNCTION CP_BALANCE_CREDIT_P RETURN NUMBER IS
324 BEGIN
325 RETURN CP_BALANCE_CREDIT;
326 END CP_BALANCE_CREDIT_P;
327 FUNCTION CP_BALANCE_SH_CESS_P RETURN NUMBER IS
328 BEGIN
329 RETURN CP_BALANCE_SH_CESS;
330 END CP_BALANCE_SH_CESS_P;
331 FUNCTION CP_ADDITIONAL_CVD_P RETURN NUMBER IS
332 BEGIN
333 RETURN CP_ADDITIONAL_CVD;
334 END CP_ADDITIONAL_CVD_P;
335 FUNCTION CP_CESS_PAYABLE_P RETURN NUMBER IS
336 BEGIN
337 RETURN CP_CESS_PAYABLE;
338 END CP_CESS_PAYABLE_P;
339 FUNCTION CP_SH_CESS_PAYABLE_P RETURN NUMBER IS
340 BEGIN
341 RETURN CP_SH_CESS_PAYABLE;
342 END CP_SH_CESS_PAYABLE_P;
343 FUNCTION CP_CESS_CREDIT_TAKEN_P RETURN NUMBER IS
344 BEGIN
345 RETURN CP_CESS_CREDIT_TAKEN;
346 END CP_CESS_CREDIT_TAKEN_P;
347 FUNCTION CP_SH_CESS_CREDIT_TAKEN_P RETURN NUMBER IS
348 BEGIN
349 RETURN CP_SH_CESS_CREDIT_TAKEN;
350 END CP_SH_CESS_CREDIT_TAKEN_P;
351 END JA_JAINCVAT_XMLP_PKG;
352
353