1 PACKAGE BODY JA_JAINRECI_XMLP_PKG AS
2 /* $Header: JAINRECIB.pls 120.1 2007/12/25 16:27:07 dwkrishn noship $ */
3 FUNCTION CF_TRANSACTIONAL_CURRFORMULA(PARENT_TRANSACTION_ID IN NUMBER) RETURN VARCHAR2 IS
4 CURSOR PARENT_CUR IS
5 SELECT
6 CURRENCY_CODE,
7 CURRENCY_CONVERSION_RATE,
8 TRANSACTION_TYPE,
9 PO_HEADER_ID
10 FROM
11 RCV_TRANSACTIONS
12 WHERE TRANSACTION_ID = PARENT_TRANSACTION_ID;
13 V_PARENT_REC PARENT_CUR%ROWTYPE;
14 BEGIN
15 OPEN PARENT_CUR;
16 FETCH PARENT_CUR
17 INTO V_PARENT_REC;
18 CLOSE PARENT_CUR;
19 IF V_PARENT_REC.TRANSACTION_TYPE <> 'MATCH' THEN
20 RETURN V_PARENT_REC.CURRENCY_CODE;
21 ELSE
22 FOR po_rec IN (SELECT
23 CURRENCY_CODE,
24 RATE
25 FROM
26 PO_HEADERS_ALL
27 WHERE PO_HEADER_ID = V_PARENT_REC.PO_HEADER_ID) LOOP
28 RETURN PO_REC.CURRENCY_CODE;
29 END LOOP;
30 END IF;
31 RETURN NULL;
32 END CF_TRANSACTIONAL_CURRFORMULA;
33
34 FUNCTION CF_2FORMULA(PARENT_TRANSACTION_ID IN NUMBER
35 ,SHIPMENT_LINE_ID IN NUMBER
36 ,CF_CURRENCY_CODE IN VARCHAR2
37 ,PRIMARY_QUANTITY IN NUMBER) RETURN NUMBER IS
38 V_CONV_FACTOR RCV_TRANSACTIONS.CURRENCY_CONVERSION_RATE%TYPE;
39 V_CVD_AMT NUMBER;
40 V_CURRENCY_CONVERSION_RATE RCV_TRANSACTIONS.CURRENCY_CONVERSION_RATE%TYPE;
41 LN_VAT_TAX NUMBER;
42 CURSOR PARENT_CUR IS
43 SELECT
44 CURRENCY_CODE,
45 CURRENCY_CONVERSION_RATE,
46 TRANSACTION_TYPE,
47 PRIMARY_QUANTITY,
48 PO_HEADER_ID
49 FROM
50 RCV_TRANSACTIONS
51 WHERE TRANSACTION_ID = PARENT_TRANSACTION_ID;
52 V_PARENT_REC PARENT_CUR%ROWTYPE;
53 BEGIN
54 OPEN PARENT_CUR;
55 FETCH PARENT_CUR
56 INTO V_PARENT_REC;
57 CLOSE PARENT_CUR;
58 IF V_PARENT_REC.TRANSACTION_TYPE <> 'MATCH' THEN
59 V_CURRENCY_CONVERSION_RATE := V_PARENT_REC.CURRENCY_CONVERSION_RATE;
60 ELSE
61 FOR po_rec IN (SELECT
62 CURRENCY_CODE,
63 RATE
64 FROM
65 PO_HEADERS_ALL
66 WHERE PO_HEADER_ID = V_PARENT_REC.PO_HEADER_ID) LOOP
67 V_CURRENCY_CONVERSION_RATE := PO_REC.RATE;
68 END LOOP;
69 END IF;
70 FOR cr_rec IN (SELECT
71 JRTL.TAX_AMOUNT,
72 JRTL.TAX_TYPE,
73 JRTL.CURRENCY,
74 NVL(MOD_CR_PERCENTAGE
75 ,0) MOD_CR_PERCENTAGE
76 FROM
77 JAI_RCV_LINE_TAXES JRTL,
78 JAI_CMN_TAXES_ALL JTC
79 WHERE SHIPMENT_LINE_ID = CF_2FORMULA.SHIPMENT_LINE_ID
80 AND NVL(MODVAT_FLAG
81 ,'N') = 'Y'
82 AND JRTL.TAX_ID = JTC.TAX_ID) LOOP
83 IF CR_REC.CURRENCY <> CF_CURRENCY_CODE THEN
84 V_CONV_FACTOR := NVL(V_CURRENCY_CONVERSION_RATE
85 ,1);
86 ELSE
87 V_CONV_FACTOR := 1;
88 END IF;
89 IF (V_PARENT_REC.PRIMARY_QUANTITY <> 0) THEN
90 V_CONV_FACTOR := V_CONV_FACTOR * (PRIMARY_QUANTITY / V_PARENT_REC.PRIMARY_QUANTITY);
91 END IF;
92 IF CR_REC.TAX_TYPE = 'CVD' THEN
93 V_CVD_AMT := NVL(V_CVD_AMT
94 ,0) + (NVL(CR_REC.TAX_AMOUNT
95 ,0) * CR_REC.MOD_CR_PERCENTAGE / 100) * V_CONV_FACTOR;
96 ELSIF CR_REC.TAX_TYPE in ('VALUE ADDED TAX','PURCHASE TAX','TURNOVER TAX','ENTRY TAX') THEN
97 LN_VAT_TAX := NVL(LN_VAT_TAX
98 ,0) + (NVL(CR_REC.TAX_AMOUNT
99 ,0) * CR_REC.MOD_CR_PERCENTAGE / 100) * V_CONV_FACTOR;
100 END IF;
101 END LOOP;
102 CP_VAT_TAX := NVL(LN_VAT_TAX
103 ,0);
104 RETURN NVL(V_CVD_AMT
105 ,0);
106 END CF_2FORMULA;
107
108 FUNCTION CF_3FORMULA(LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
109 BEGIN
110 FOR loc_rec IN (SELECT
111 DESCRIPTION
112 FROM
113 HR_LOCATIONS
114 WHERE LOCATION_ID = cf_3formula.LOCATION_ID) LOOP
115 RETURN LOC_REC.DESCRIPTION;
116 END LOOP;
117 RETURN NULL;
118 END CF_3FORMULA;
119
120 FUNCTION CF_4FORMULA(LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
121 BEGIN
122 FOR loc_rec IN (SELECT
123 COUNTRY
124 FROM
125 HR_LOCATIONS
126 WHERE LOCATION_ID = cf_4formula.LOCATION_ID) LOOP
127 RETURN LOC_REC.COUNTRY;
128 END LOOP;
129 RETURN NULL;
130 END CF_4FORMULA;
131
132 FUNCTION CF_5FORMULA(LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
133 BEGIN
134 FOR loc_rec IN (SELECT
135 ADDRESS_LINE_1
136 FROM
137 HR_LOCATIONS
138 WHERE LOCATION_ID = CF_5FORMULA.LOCATION_ID) LOOP
139 RETURN LOC_REC.ADDRESS_LINE_1;
140 END LOOP;
141 RETURN NULL;
142 END CF_5FORMULA;
143
144 FUNCTION CF_6FORMULA(LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
145 BEGIN
146 FOR loc_rec IN (SELECT
147 ADDRESS_LINE_2
148 FROM
149 HR_LOCATIONS
150 WHERE LOCATION_ID = CF_6FORMULA.LOCATION_ID) LOOP
151 RETURN LOC_REC.ADDRESS_LINE_2;
152 END LOOP;
153 RETURN NULL;
154 END CF_6FORMULA;
155
156 FUNCTION CF_7FORMULA(LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
157 BEGIN
158 FOR loc_rec IN (SELECT
159 ADDRESS_LINE_3
160 FROM
161 HR_LOCATIONS
162 WHERE LOCATION_ID = CF_7FORMULA.LOCATION_ID) LOOP
163 RETURN LOC_REC.ADDRESS_LINE_3;
164 END LOOP;
165 RETURN NULL;
166 END CF_7FORMULA;
167
168 FUNCTION CF_8FORMULA(ORGANIZATION_ID1 IN NUMBER
169 ,INVENTORY_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
170 BEGIN
171 FOR item_rec IN (SELECT
172 ITEM_FOLIO
173 FROM
174 JAI_INV_ITM_SETUPS
175 WHERE ORGANIZATION_ID = ORGANIZATION_ID1
176 AND INVENTORY_ITEM_ID = CF_8FORMULA.INVENTORY_ITEM_ID) LOOP
177 RETURN ITEM_REC.ITEM_FOLIO;
178 END LOOP;
179 RETURN NULL;
180 END CF_8FORMULA;
181
182 FUNCTION CF_CURRENCY_CODEFORMULA(ORGANIZATION_ID1 IN NUMBER) RETURN CHAR IS
183 V_CURRENCY_CODE GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE;
184 BEGIN
185 FOR fetch_curr_code IN (SELECT
186 CURRENCY_CODE
187 FROM
188 GL_SETS_OF_BOOKS
189 WHERE SET_OF_BOOKS_ID in (
190 SELECT
191 SET_OF_BOOKS_ID
192 FROM
193 ORG_ORGANIZATION_DEFINITIONS
194 WHERE ORGANIZATION_ID = ORGANIZATION_ID1 )) LOOP
195 V_CURRENCY_CODE := FETCH_CURR_CODE.CURRENCY_CODE;
196 END LOOP;
197 RETURN (V_CURRENCY_CODE);
198 EXCEPTION
199 WHEN OTHERS THEN
200 RETURN (NULL);
201 END CF_CURRENCY_CODEFORMULA;
202
203 FUNCTION CF_ORGANIZATION_NAMEFORMULA(ORGANIZATION_ID1 IN NUMBER) RETURN CHAR IS
204 V_ORGANIZATION_NAME ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
205 BEGIN
206 FOR fetch_org_name IN (SELECT
207 ORGANIZATION_NAME
208 FROM
209 ORG_ORGANIZATION_DEFINITIONS
210 WHERE ORGANIZATION_ID = ORGANIZATION_ID1) LOOP
211 V_ORGANIZATION_NAME := FETCH_ORG_NAME.ORGANIZATION_NAME;
212 END LOOP;
213 RETURN (V_ORGANIZATION_NAME);
214 EXCEPTION
215 WHEN OTHERS THEN
216 RETURN (NULL);
217 END CF_ORGANIZATION_NAMEFORMULA;
218
219 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
220 BEGIN
221 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
222 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
223 /*SRW.MESSAGE(1275
224 ,'Report Version is 120.6 Last modified date is 24-Nov-2006')*/NULL;
225 RETURN (TRUE);
226 END BEFOREREPORT;
227
228 FUNCTION CF_CESS_AMOUNTFORMULA(TRANSACTION_ID IN NUMBER
229 ,ORGANIZATION_ID1 IN NUMBER
230 ,LOCATION_ID IN NUMBER
231 ,REGISTER_TYPE IN VARCHAR2
232 ,DR_BASIC_ED IN NUMBER
233 ,DR_ADDITIONAL_ED IN NUMBER
234 ,DR_OTHER_ED IN NUMBER) RETURN NUMBER IS
235 LN_CESS_AMOUNT NUMBER;
236 LV_EXC_EDU_CESS JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE;
237 LV_CVD_EDU_CESS JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE;
238 CURSOR LCU_GET_PLA_CESS_AMOUNT IS
239 SELECT
240 NVL(SUM(JRO.CREDIT)
241 ,SUM(JRO.DEBIT))
242 FROM
243 JAI_CMN_RG_OTHERS JRO
244 WHERE JRO.SOURCE_REGISTER_ID IN (
245 SELECT
246 RG.REGISTER_ID
247 FROM
248 JAI_CMN_RG_PLA_TRXS RG
249 WHERE RG.REF_DOCUMENT_ID = TRANSACTION_ID
250 AND RG.ORGANIZATION_ID = ORGANIZATION_ID1
251 AND RG.TRANSACTION_SOURCE_NUM = 19
252 AND RG.LOCATION_ID = cf_cess_amountformula.LOCATION_ID )
253 AND JRO.SOURCE_TYPE = 2
254 AND JRO.TAX_TYPE IN ( LV_EXC_EDU_CESS , LV_CVD_EDU_CESS );
255 CURSOR LCU_GET_CESS_AMOUNT IS
256 SELECT
257 NVL(SUM(JRO.CREDIT)
258 ,SUM(JRO.DEBIT))
259 FROM
260 JAI_CMN_RG_OTHERS JRO
261 WHERE JRO.SOURCE_REGISTER_ID IN (
262 SELECT
263 RG.REGISTER_ID
264 FROM
265 JAI_CMN_RG_23AC_II_TRXS RG
266 WHERE RG.RECEIPT_REF = TRANSACTION_ID
267 AND RG.ORGANIZATION_ID = ORGANIZATION_ID1
268 AND RG.TRANSACTION_SOURCE_NUM = 18
269 AND RG.LOCATION_ID = cf_cess_amountformula.LOCATION_ID )
270 AND JRO.SOURCE_TYPE = 1
271 AND JRO.TAX_TYPE IN ( LV_EXC_EDU_CESS , LV_CVD_EDU_CESS );
272 BEGIN
273 LV_EXC_EDU_CESS := 'EXCISE_EDUCATION_CESS';
274 LV_CVD_EDU_CESS := 'CVD_EDUCATION_CESS';
275 IF (REGISTER_TYPE = 'PLA') THEN
276 OPEN LCU_GET_PLA_CESS_AMOUNT;
277 FETCH LCU_GET_PLA_CESS_AMOUNT
278 INTO LN_CESS_AMOUNT;
279 CLOSE LCU_GET_PLA_CESS_AMOUNT;
280 ELSE
281 OPEN LCU_GET_CESS_AMOUNT;
282 FETCH LCU_GET_CESS_AMOUNT
283 INTO LN_CESS_AMOUNT;
284 CLOSE LCU_GET_CESS_AMOUNT;
285 END IF;
286 /*SRW.MESSAGE(1000
287 ,'CessFor basic:' || DR_BASIC_ED || ', rndBasic:' || CP_ROUND_BASIC_ED || ', addl:' || DR_ADDITIONAL_ED || ', rndAddl:' || CP_ROUND_ADDITIONAL_ED || ', oth:' || DR_OTHER_ED || ', rndOth:'
288 || CP_ROUND_OTHER_ED || ', cess:' || LN_CESS_AMOUNT || ', rndAddl:' || CP_ROUND_CESS)*/NULL;
289 RETURN (NVL(LN_CESS_AMOUNT
290 ,0) + NVL(CP_ROUND_CESS
291 ,0));
292 EXCEPTION
293 WHEN OTHERS THEN
294 IF (LCU_GET_PLA_CESS_AMOUNT%ISOPEN) THEN
295 CLOSE LCU_GET_PLA_CESS_AMOUNT;
296 END IF;
297 IF (LCU_GET_CESS_AMOUNT%ISOPEN) THEN
298 CLOSE LCU_GET_CESS_AMOUNT;
299 END IF;
300 RETURN (0);
301 END CF_CESS_AMOUNTFORMULA;
302
303 FUNCTION CF_ROUNDING_AMTSFORMULA(ROUNDING_ID IN NUMBER
304 ,REGISTER_TYPE IN VARCHAR2
305 ,DR_BASIC_ED IN NUMBER
306 ,DR_ADDITIONAL_ED IN NUMBER
307 ,DR_OTHER_ED IN NUMBER) RETURN NUMBER IS
308 CURSOR C_ROUNDING_DTL(CP_ROUNDING_ID IN NUMBER) IS
309 SELECT
310 REGISTER_ID
311 FROM
312 JAI_CMN_RG_ROUND_HDRS
313 WHERE ROUNDING_ID = CP_ROUNDING_ID;
314 CURSOR C_23P2_RND_AMTS(CP_REGISTER_ID IN NUMBER) IS
315 SELECT
316 - NVL(CR_BASIC_ED
317 ,0) + NVL(DR_BASIC_ED
318 ,0) BASIC_ED,
319 - NVL(CR_ADDITIONAL_ED
320 ,0) + NVL(DR_ADDITIONAL_ED
321 ,0) ADDITIONAL_ED,
322 - NVL(CR_OTHER_ED
323 ,0) + NVL(DR_OTHER_ED
324 ,0) OTHER_ED,
325 - NVL(CR_ADDITIONAL_CVD
326 ,0) + NVL(DR_ADDITIONAL_CVD
327 ,0) OTHER_ED
328 FROM
329 JAI_CMN_RG_23AC_II_TRXS
330 WHERE REGISTER_ID = CP_REGISTER_ID;
331 CURSOR C_PLA_RND_AMTS(CP_REGISTER_ID IN NUMBER) IS
332 SELECT
333 - NVL(CR_BASIC_ED
334 ,0) + NVL(DR_BASIC_ED
335 ,0) BASIC_ED,
336 - NVL(CR_ADDITIONAL_ED
337 ,0) + NVL(DR_ADDITIONAL_ED
338 ,0) ADDITIONAL_ED,
339 - NVL(CR_OTHER_ED
340 ,0) + NVL(DR_OTHER_ED
341 ,0) OTHER_ED
342 FROM
343 JAI_CMN_RG_PLA_TRXS
344 WHERE REGISTER_ID = CP_REGISTER_ID;
345 LV_EXC_EDU_CESS JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE;
346 LV_CVD_EDU_CESS JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE;
347 CURSOR C_RND_CESS_AMT(CP_REGISTER_ID IN NUMBER,CP_SOURCE_TYPE IN NUMBER) IS
348 SELECT
349 SUM(NVL(DEBIT
350 ,0) - NVL(CREDIT
351 ,0))
352 FROM
353 JAI_CMN_RG_OTHERS
354 WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
355 AND SOURCE_TYPE = CP_SOURCE_TYPE
356 AND TAX_TYPE in ( LV_EXC_EDU_CESS , LV_CVD_EDU_CESS );
357 V_RND_BASIC_ED NUMBER;
358 V_RND_ADDITIONAL_ED NUMBER;
359 V_RND_ADDITIONAL_CVD NUMBER;
360 V_RND_OTHER_ED NUMBER;
361 V_RND_CESS NUMBER;
362 V_REGISTER_ID NUMBER;
363 BEGIN
364 IF ROUNDING_ID IS NOT NULL THEN
365 OPEN C_ROUNDING_DTL(ROUNDING_ID);
366 FETCH C_ROUNDING_DTL
367 INTO V_REGISTER_ID;
368 CLOSE C_ROUNDING_DTL;
369 IF REGISTER_TYPE in ('A','C') THEN
370 OPEN C_23P2_RND_AMTS(V_REGISTER_ID);
371 FETCH C_23P2_RND_AMTS
372 INTO V_RND_BASIC_ED,V_RND_ADDITIONAL_ED,V_RND_OTHER_ED,V_RND_ADDITIONAL_CVD;
373 CLOSE C_23P2_RND_AMTS;
374 LV_EXC_EDU_CESS := 'EXCISE_EDUCATION_CESS';
375 LV_CVD_EDU_CESS := 'CVD_EDUCATION_CESS';
376 OPEN C_RND_CESS_AMT(V_REGISTER_ID,1);
377 FETCH C_RND_CESS_AMT
378 INTO V_RND_CESS;
379 CLOSE C_RND_CESS_AMT;
380 ELSIF REGISTER_TYPE = 'PLA' THEN
381 OPEN C_PLA_RND_AMTS(V_REGISTER_ID);
385 LV_EXC_EDU_CESS := 'EXCISE_EDUCATION_CESS';
382 FETCH C_PLA_RND_AMTS
383 INTO V_RND_BASIC_ED,V_RND_ADDITIONAL_ED,V_RND_OTHER_ED;
384 CLOSE C_PLA_RND_AMTS;
386 LV_CVD_EDU_CESS := 'CVD_EDUCATION_CESS';
387 OPEN C_RND_CESS_AMT(V_REGISTER_ID,2);
388 FETCH C_RND_CESS_AMT
389 INTO V_RND_CESS;
390 CLOSE C_RND_CESS_AMT;
391 END IF;
392 CP_ROUND_BASIC_ED := V_RND_BASIC_ED;
393 CP_ROUND_OTHER_ED := V_RND_OTHER_ED;
394 CP_ROUND_ADDITIONAL_ED := V_RND_ADDITIONAL_ED;
395 CP_ROUND_ADDITIONAL_CVD := V_RND_ADDITIONAL_CVD;
396 CP_ROUND_CESS := V_RND_CESS;
397 ELSE
398 CP_ROUND_BASIC_ED := 0;
399 CP_ROUND_OTHER_ED := 0;
400 CP_ROUND_ADDITIONAL_ED := 0;
401 CP_ROUND_CESS := 0;
402 CP_ROUND_ADDITIONAL_CVD := 0;
403 END IF;
404 /*SRW.MESSAGE(1000
405 ,'RndAmts basic:' || DR_BASIC_ED || ', rndBasic:' || CP_ROUND_BASIC_ED || ', addl:' || DR_ADDITIONAL_ED || ', rndAddl:' || CP_ROUND_ADDITIONAL_ED || ', oth:' || DR_OTHER_ED
406 || ', rndOth:' || CP_ROUND_OTHER_ED || ', rndAddl:' || CP_ROUND_CESS)*/NULL;
407 RETURN (V_RND_BASIC_ED + V_RND_OTHER_ED + V_RND_ADDITIONAL_ED + V_RND_CESS);
408 END CF_ROUNDING_AMTSFORMULA;
409
410 FUNCTION CF_BASIC_EDFORMULA(DR_BASIC_ED IN NUMBER) RETURN NUMBER IS
411 BEGIN
412 RETURN (NVL(DR_BASIC_ED
413 ,0) + NVL(CP_ROUND_BASIC_ED
414 ,0));
415 END CF_BASIC_EDFORMULA;
416
417 FUNCTION CF_ADDITIONAL_EDFORMULA(DR_ADDITIONAL_ED IN NUMBER) RETURN NUMBER IS
418 BEGIN
419 RETURN (NVL(DR_ADDITIONAL_ED
420 ,0) + NVL(CP_ROUND_ADDITIONAL_ED
421 ,0));
422 END CF_ADDITIONAL_EDFORMULA;
423
424 FUNCTION CF_OTHER_EDFORMULA(DR_OTHER_ED IN NUMBER) RETURN NUMBER IS
425 BEGIN
426 RETURN (NVL(DR_OTHER_ED
427 ,0) + NVL(CP_ROUND_OTHER_ED
428 ,0));
429 END CF_OTHER_EDFORMULA;
430
431 FUNCTION AFTERREPORT RETURN BOOLEAN IS
432 BEGIN
433 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
434 RETURN (TRUE);
435 END AFTERREPORT;
436
437 FUNCTION CF_ADDITIONAL_CVDFORMULA(DR_ADDITIONAL_CVD IN NUMBER) RETURN NUMBER IS
438 BEGIN
439 RETURN (NVL(DR_ADDITIONAL_CVD
440 ,0) + NVL(CP_ROUND_ADDITIONAL_CVD
441 ,0));
442 END CF_ADDITIONAL_CVDFORMULA;
443
444 FUNCTION CF_CALC_VAT_INVNUM_DATEFORMULA(TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
445 LC_VAT_INV_NO VARCHAR2(50);
446 LD_VAT_INV_DATE DATE;
447 CURSOR LCU_GET_VAT_DETAILS(P_TRANSACTION_ID IN NUMBER) IS
448 SELECT
449 JIRT.VAT_INVOICE_NO,
450 JIRT.VAT_INVOICE_DATE
451 FROM
452 JAI_RCV_TRANSACTIONS JIRT
453 WHERE JIRT.TRANSACTION_ID = P_TRANSACTION_ID;
454 BEGIN
455 OPEN LCU_GET_VAT_DETAILS(P_TRANSACTION_ID => TRANSACTION_ID);
456 FETCH LCU_GET_VAT_DETAILS
457 INTO LC_VAT_INV_NO,LD_VAT_INV_DATE;
458 CLOSE LCU_GET_VAT_DETAILS;
459 CP_VAT_INVOICE_NUMBER := LC_VAT_INV_NO;
460 CP_VAT_INVOICE_DATE := LD_VAT_INV_DATE;
461 RETURN (NULL);
462 EXCEPTION
463 WHEN OTHERS THEN
464 IF (LCU_GET_VAT_DETAILS%ISOPEN) THEN
465 CLOSE LCU_GET_VAT_DETAILS;
466 END IF;
467 RETURN (NULL);
468 END CF_CALC_VAT_INVNUM_DATEFORMULA;
469
470 FUNCTION CF_SH_CESS_AMOUNTFORMULA(TRANSACTION_ID IN NUMBER
471 ,ORGANIZATION_ID1 IN NUMBER
472 ,LOCATION_ID IN NUMBER
473 ,REGISTER_TYPE IN VARCHAR2
474 ,DR_BASIC_ED IN NUMBER
475 ,DR_ADDITIONAL_ED IN NUMBER
476 ,DR_OTHER_ED IN NUMBER) RETURN NUMBER IS
477 CURSOR LCU_GET_PLA_CESS_AMOUNT IS
478 SELECT
479 NVL(SUM(JRO.CREDIT)
480 ,SUM(JRO.DEBIT))
481 FROM
482 JAI_CMN_RG_OTHERS JRO
483 WHERE JRO.SOURCE_REGISTER_ID in (
484 SELECT
485 RG.REGISTER_ID
486 FROM
487 JAI_CMN_RG_PLA_TRXS RG
488 WHERE RG.REF_DOCUMENT_ID = TRANSACTION_ID
489 AND RG.ORGANIZATION_ID = ORGANIZATION_ID1
490 AND RG.TRANSACTION_SOURCE_NUM = 19
491 AND RG.LOCATION_ID = cf_sh_cess_amountformula.LOCATION_ID )
492 AND JRO.SOURCE_TYPE = 2
493 AND JRO.TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
494 CURSOR LCU_GET_CESS_AMOUNT IS
495 SELECT
496 NVL(SUM(JRO.CREDIT)
497 ,SUM(JRO.DEBIT))
498 FROM
499 JAI_CMN_RG_OTHERS JRO
500 WHERE JRO.SOURCE_REGISTER_ID in (
501 SELECT
502 RG.REGISTER_ID
503 FROM
504 JAI_CMN_RG_23AC_II_TRXS RG
505 WHERE RG.RECEIPT_REF = TRANSACTION_ID
506 AND RG.ORGANIZATION_ID = ORGANIZATION_ID1
507 AND RG.TRANSACTION_SOURCE_NUM = 18
508 AND RG.LOCATION_ID = cf_sh_cess_amountformula.LOCATION_ID )
509 AND JRO.SOURCE_TYPE = 1
510 AND JRO.TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
511 LN_SH_CESS_AMOUNT NUMBER;
512 BEGIN
513 IF (REGISTER_TYPE = 'PLA') THEN
514 OPEN LCU_GET_PLA_CESS_AMOUNT;
515 FETCH LCU_GET_PLA_CESS_AMOUNT
516 INTO LN_SH_CESS_AMOUNT;
517 CLOSE LCU_GET_PLA_CESS_AMOUNT;
518 ELSE
519 OPEN LCU_GET_CESS_AMOUNT;
520 FETCH LCU_GET_CESS_AMOUNT
521 INTO LN_SH_CESS_AMOUNT;
522 CLOSE LCU_GET_CESS_AMOUNT;
523 END IF;
524 /*SRW.MESSAGE(1000
525 ,'CessFor basic:' || DR_BASIC_ED || ', rndBasic:' || CP_ROUND_BASIC_ED || ', addl:' || DR_ADDITIONAL_ED || ', rndAddl:' || CP_ROUND_ADDITIONAL_ED || ', oth:' || DR_OTHER_ED || ', rndOth:' ||
526 CP_ROUND_OTHER_ED || ', cess:' || LN_SH_CESS_AMOUNT || ', SH Rndcess:' || CP_SH_ROUND_CESS)*/NULL;
527 RETURN (NVL(LN_SH_CESS_AMOUNT
528 ,0) + NVL(CP_SH_ROUND_CESS
529 ,0));
530 EXCEPTION
531 WHEN OTHERS THEN
532 IF (LCU_GET_PLA_CESS_AMOUNT%ISOPEN) THEN
533 CLOSE LCU_GET_PLA_CESS_AMOUNT;
534 END IF;
535 IF (LCU_GET_CESS_AMOUNT%ISOPEN) THEN
536 CLOSE LCU_GET_CESS_AMOUNT;
537 END IF;
538 RETURN (0);
539 END CF_SH_CESS_AMOUNTFORMULA;
540
541 FUNCTION CP_ROUND_BASIC_ED_P RETURN NUMBER IS
542 BEGIN
543 RETURN CP_ROUND_BASIC_ED;
544 END CP_ROUND_BASIC_ED_P;
545
546 FUNCTION CP_ROUND_ADDITIONAL_ED_P RETURN NUMBER IS
547 BEGIN
548 RETURN CP_ROUND_ADDITIONAL_ED;
549 END CP_ROUND_ADDITIONAL_ED_P;
550
551 FUNCTION CP_ROUND_ADDITIONAL_CVD_P RETURN NUMBER IS
552 BEGIN
553 RETURN CP_ROUND_ADDITIONAL_CVD;
554 END CP_ROUND_ADDITIONAL_CVD_P;
555
556 FUNCTION CP_ROUND_OTHER_ED_P RETURN NUMBER IS
557 BEGIN
558 RETURN CP_ROUND_OTHER_ED;
559 END CP_ROUND_OTHER_ED_P;
560
561 FUNCTION CP_ROUND_CESS_P RETURN NUMBER IS
562 BEGIN
563 RETURN CP_ROUND_CESS;
564 END CP_ROUND_CESS_P;
565
566 FUNCTION CP_VAT_TAX_P RETURN NUMBER IS
567 BEGIN
568 RETURN CP_VAT_TAX;
569 END CP_VAT_TAX_P;
570
571 FUNCTION CP_SH_ROUND_CESS_P RETURN NUMBER IS
572 BEGIN
573 RETURN CP_SH_ROUND_CESS;
574 END CP_SH_ROUND_CESS_P;
575
576 FUNCTION CP_VAT_INVOICE_NUMBER_P RETURN NUMBER IS
577 BEGIN
578 RETURN CP_VAT_INVOICE_NUMBER;
579 END CP_VAT_INVOICE_NUMBER_P;
580
581 FUNCTION CP_VAT_INVOICE_DATE_P RETURN DATE IS
582 BEGIN
583 RETURN CP_VAT_INVOICE_DATE;
584 END CP_VAT_INVOICE_DATE_P;
585
586 END JA_JAINRECI_XMLP_PKG;
587
588
589
[Home] [Help]