1 PACKAGE BODY JA_JAINPLA_XMLP_PKG AS
2 /* $Header: JAINPLAB.pls 120.1 2007/12/25 16:24:10 dwkrishn noship $ */
3 FUNCTION CF_BED_CLOSE_BALFORMULA(CR_BASIC_ED IN NUMBER
4 ,DR_BASIC_ED IN NUMBER) RETURN NUMBER IS
5 BEGIN
6 IF (P_FIRST_REC = 'T') THEN
7 CP_BED_OPEN_BAL := NVL(P_BED_OPEN_BAL
8 ,0) + NVL(CR_BASIC_ED
9 ,0) - NVL(DR_BASIC_ED
10 ,0);
11 P_FIRST_REC := 'F';
12 ELSE
13 CP_BED_OPEN_BAL := NVL(CP_BED_OPEN_BAL
14 ,0) + NVL(CR_BASIC_ED
15 ,0) - NVL(DR_BASIC_ED
16 ,0);
17 END IF;
18 CP_BED_OPEN_BAL := CP_BED_OPEN_BAL - CP_RND_BASIC_ED;
19 RETURN (NVL(CP_BED_OPEN_BAL
20 ,0));
21 RETURN NULL;
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN
24 RETURN (0);
25 END CF_BED_CLOSE_BALFORMULA;
26
27 FUNCTION CF_AED_CLOSE_BALFORMULA(CR_ADDITIONAL_ED IN NUMBER
28 ,DR_ADDITIONAL_ED IN NUMBER) RETURN NUMBER IS
29 BEGIN
30 IF (P_FIRST_REC = 'T') THEN
31 CP_AED_OPEN_BAL := NVL(P_AED_OPEN_BAL
32 ,0) + NVL(CR_ADDITIONAL_ED
33 ,0) - NVL(DR_ADDITIONAL_ED
34 ,0);
35 ELSE
36 CP_AED_OPEN_BAL := NVL(CP_AED_OPEN_BAL
37 ,0) + NVL(CR_ADDITIONAL_ED
38 ,0) - NVL(DR_ADDITIONAL_ED
39 ,0);
40 END IF;
41 CP_AED_OPEN_BAL := CP_AED_OPEN_BAL - CP_RND_ADDL_ED;
42 RETURN (NVL(CP_AED_OPEN_BAL
43 ,0));
44 RETURN NULL;
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 RETURN (0);
48 END CF_AED_CLOSE_BALFORMULA;
49
50 FUNCTION CF_SED_CLOSE_BALFORMULA(CR_OTHER_ED IN NUMBER
51 ,DR_OTHER_ED IN NUMBER) RETURN NUMBER IS
52 BEGIN
53 IF (P_FIRST_REC = 'T') THEN
54 CP_SED_OPEN_BAL := NVL(P_SED_OPEN_BAL
55 ,0) + NVL(CR_OTHER_ED
56 ,0) - NVL(DR_OTHER_ED
57 ,0);
58 ELSE
59 CP_SED_OPEN_BAL := NVL(CP_SED_OPEN_BAL
60 ,0) + NVL(CR_OTHER_ED
61 ,0) - NVL(DR_OTHER_ED
62 ,0);
63 END IF;
64 CP_SED_OPEN_BAL := CP_SED_OPEN_BAL - CP_RND_OTHER_ED;
65 RETURN (NVL(CP_SED_OPEN_BAL
66 ,0));
67 RETURN NULL;
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 RETURN (0);
71 END CF_SED_CLOSE_BALFORMULA;
72
73 FUNCTION AFTERPFORM RETURN BOOLEAN IS
74 FOLIOMONTH DATE;
75 MAXENDDATE DATE;
76 LV_TAX_TYPE JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE;
77 CURSOR CUR_OPEN_CESS_BAL(P_ORG_ID IN JAI_CMN_RG_PLA_TRXS.ORGANIZATION_ID%TYPE,P_LOC_ID IN JAI_CMN_RG_PLA_TRXS.LOCATION_ID%TYPE,P_TRN_FROM_DATE IN DATE,
78 P_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE,CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE) IS
79 SELECT
80 NVL(CLOSING_BALANCE
81 ,0)
82 FROM
83 JAI_CMN_RG_OTHERS
84 WHERE SOURCE_REGISTER = CP_SOURCE_REGISTER
85 AND TAX_TYPE = P_TAX_TYPE
86 AND SOURCE_REGISTER_ID = (
87 SELECT
88 MAX(A.REGISTER_ID)
89 FROM
90 JAI_CMN_RG_PLA_TRXS A,
91 JAI_CMN_RG_OTHERS B
92 WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID
93 AND B.SOURCE_REGISTER = CP_SOURCE_REGISTER
94 AND B.TAX_TYPE = P_TAX_TYPE
95 AND A.TRANSACTION_DATE = (
96 SELECT
97 MAX(C.TRANSACTION_DATE)
98 FROM
99 JAI_CMN_RG_PLA_TRXS C,
100 JAI_CMN_RG_OTHERS D
101 WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID
102 AND D.SOURCE_REGISTER = CP_SOURCE_REGISTER
103 AND D.TAX_TYPE = P_TAX_TYPE
104 AND TRUNC(C.TRANSACTION_DATE) < TRUNC(P_TRN_FROM_DATE)
105 AND C.ORGANIZATION_ID = P_ORG_ID
106 AND C.LOCATION_ID = P_LOC_ID ) );
107 BEGIN
108 IF (P_TRN_FROM_DATE IS NULL) THEN
109 P_BED_OPEN_BAL := 0;
110 P_AED_OPEN_BAL := 0;
111 P_SED_OPEN_BAL := 0;
112 P_EXCISE_CESS_OPEN_BAL := 0;
113 P_CVD_CESS_OPEN_BAL := 0;
114 ELSE
115 BEGIN
116 SELECT
117 SUM(NVL(CR_BASIC_ED
118 ,0)) - SUM(NVL(DR_BASIC_ED
119 ,0)),
120 SUM(NVL(CR_ADDITIONAL_ED
121 ,0)) - SUM(NVL(DR_ADDITIONAL_ED
122 ,0)),
123 SUM(NVL(CR_OTHER_ED
124 ,0)) - SUM(NVL(DR_OTHER_ED
125 ,0))
126 INTO P_BED_OPEN_BAL,P_AED_OPEN_BAL,P_SED_OPEN_BAL
127 FROM
128 JAI_CMN_RG_PLA_TRXS
129 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
130 AND LOCATION_ID = P_LOCATION_ID
131 AND TRUNC(CREATION_DATE) <= TRUNC(P_TRN_FROM_DATE) - 1;
132 LV_TAX_TYPE := 'EXCISE_EDUCATION_CESS';
133 OPEN CUR_OPEN_CESS_BAL(P_ORGANIZATION_ID,P_LOCATION_ID,P_TRN_FROM_DATE,LV_TAX_TYPE,'PLA');
134 FETCH CUR_OPEN_CESS_BAL
135 INTO P_EXCISE_CESS_OPEN_BAL;
136 CLOSE CUR_OPEN_CESS_BAL;
137 LV_TAX_TYPE := 'CVD_EDUCATION_CESS';
138 OPEN CUR_OPEN_CESS_BAL(P_ORGANIZATION_ID,P_LOCATION_ID,P_TRN_FROM_DATE,LV_TAX_TYPE,'PLA');
139 FETCH CUR_OPEN_CESS_BAL
140 INTO P_CVD_CESS_OPEN_BAL;
141 CLOSE CUR_OPEN_CESS_BAL;
142 EXCEPTION
143 WHEN OTHERS THEN
144 /*SRW.MESSAGE(150
145 ,SQLERRM)*/NULL;
146 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
147 END;
148 END IF;
149 IF P_FISCAL_YEAR IS NOT NULL AND P_MONTH IS NOT NULL THEN
150 FOLIOMONTH := TO_DATE('01-' || UPPER(P_MONTH) || TO_CHAR(P_FISCAL_YEAR
151 ,'-YYYY')
152 ,'DD-MON-YYYY');
153 IF TRUNC(FOLIOMONTH) < TRUNC(P_FISCAL_YEAR) THEN
154 FOLIOMONTH := ADD_MONTHS(FOLIOMONTH
155 ,12);
156 END IF;
157 IF TRUNC(LAST_DAY(FOLIOMONTH)) < TRUNC(SYSDATE) THEN
158 P_TRN_FROM_DATE := TO_DATE('01-' || TO_CHAR(FOLIOMONTH
159 ,'MON-YYYY')
160 ,'DD-MON-YYYY');
161 P_TRN_TO_DATE := LAST_DAY(P_TRN_FROM_DATE);
162 VALIDATION_FLAG := 'Y';
163 IF PREV_PAGE = -1 THEN
164 VALIDATION_FLAG := 'N';
165 END IF;
166 END IF;
167 ELSE
168 VALIDATION_FLAG := 'N';
169 END IF;
170 RETURN (TRUE);
171 END AFTERPFORM;
172
173 FUNCTION CF_2FORMULA(ROUNDING_ID IN NUMBER
174 ,INVENTORY_ITEM_ID IN NUMBER
175 ,DR_INVOICE_NO IN VARCHAR2
176 ,CR_BASIC_ED IN NUMBER
177 ,CR_ADDITIONAL_ED IN NUMBER
178 ,CR_OTHER_ED IN NUMBER
179 ,TR6_CHALLAN_NO IN VARCHAR2
180 ,DR_BASIC_ED IN NUMBER
181 ,DR_ADDITIONAL_ED IN NUMBER
182 ,DR_OTHER_ED IN NUMBER) RETURN VARCHAR2 IS
183 BEGIN
184 IF ROUNDING_ID = -1 AND INVENTORY_ITEM_ID = 0 THEN
185 P_DOC_NO := DR_INVOICE_NO;
186 ELSIF NVL(CR_BASIC_ED
187 ,0) > 0 OR NVL(CR_ADDITIONAL_ED
188 ,0) > 0 OR NVL(CR_OTHER_ED
189 ,0) > 0 THEN
190 P_DOC_NO := TR6_CHALLAN_NO;
191 ELSIF NVL(DR_BASIC_ED
192 ,0) > 0 OR NVL(DR_ADDITIONAL_ED
193 ,0) > 0 OR NVL(DR_OTHER_ED
194 ,0) > 0 THEN
195 P_DOC_NO := DR_INVOICE_NO;
196 ELSE
197 P_DOC_NO := NULL;
198 END IF;
199 RETURN (P_DOC_NO);
200 EXCEPTION
201 WHEN OTHERS THEN
202 RETURN (NULL);
203 END CF_2FORMULA;
204
205 FUNCTION CF_1FORMULA(ROUNDING_ID IN NUMBER
206 ,INVENTORY_ITEM_ID IN NUMBER
207 ,DR_INVOICE_DATE IN VARCHAR2
208 ,CR_BASIC_ED IN NUMBER
209 ,CR_ADDITIONAL_ED IN NUMBER
210 ,CR_OTHER_ED IN NUMBER
211 ,TR6_CHALLAN_DATE IN VARCHAR2
212 ,DR_BASIC_ED IN NUMBER
213 ,DR_ADDITIONAL_ED IN NUMBER
214 ,DR_OTHER_ED IN NUMBER) RETURN VARCHAR2 IS
215 BEGIN
216 IF ROUNDING_ID = -1 AND INVENTORY_ITEM_ID = 0 THEN
217 P_DOC_DATE := DR_INVOICE_DATE;
218 ELSIF NVL(CR_BASIC_ED
219 ,0) > 0 OR NVL(CR_ADDITIONAL_ED
220 ,0) > 0 OR NVL(CR_OTHER_ED
221 ,0) > 0 THEN
222 P_DOC_DATE := TR6_CHALLAN_DATE;
223 ELSIF NVL(DR_BASIC_ED
224 ,0) > 0 OR NVL(DR_ADDITIONAL_ED
225 ,0) > 0 OR NVL(DR_OTHER_ED
226 ,0) > 0 THEN
227 P_DOC_DATE := DR_INVOICE_DATE;
228 ELSE
229 P_DOC_DATE := NULL;
230 END IF;
231 RETURN (P_DOC_DATE);
232 EXCEPTION
233 WHEN OTHERS THEN
234 RETURN (NULL);
235 END CF_1FORMULA;
236
237 FUNCTION CF_1FORMULA0031(TRANSACTION_ID IN NUMBER
238 ,ROUNDING_ID IN NUMBER
239 ,INVENTORY_ITEM_ID IN NUMBER
240 ,TR6_CHALLAN_DATE IN VARCHAR2
241 ,REMARKS IN VARCHAR2
242 ,DOC_DATE IN DATE
243 ,CR_BASIC_ED IN NUMBER
244 ,CR_ADDITIONAL_ED IN NUMBER
245 ,CR_OTHER_ED IN NUMBER
246 ,DR_BASIC_ED IN NUMBER
247 ,DR_ADDITIONAL_ED IN NUMBER
248 ,DR_OTHER_ED IN NUMBER) RETURN VARCHAR2 IS
249 BEGIN
250 IF TRANSACTION_ID = 19 AND ROUNDING_ID = -1 AND INVENTORY_ITEM_ID = 0 THEN
251 P_TR6NO := 'ROUNDING';
252 ELSIF TR6_CHALLAN_DATE IS NULL AND SUBSTR(REMARKS
253 ,1
254 ,23) = 'Duty Payment Adjustment' THEN
255 /*SRW.MESSAGE(101
256 ,'Remarks ' || TO_CHAR(DOC_DATE))*/NULL;
257 P_TR6NO := 'CONSOLIDATION';
258 ELSIF CR_BASIC_ED > 0 OR CR_ADDITIONAL_ED > 0 AND CR_OTHER_ED > 0 THEN
259 P_TR6NO := 'TR6';
260 ELSIF DR_BASIC_ED > 0 OR DR_ADDITIONAL_ED > 0 AND DR_OTHER_ED > 0 THEN
261 P_TR6NO := 'INVOICE';
262 ELSE
263 P_TR6NO := NULL;
264 END IF;
265 RETURN (P_TR6NO);
266 RETURN NULL;
267 EXCEPTION
268 WHEN OTHERS THEN
269 RETURN (0);
270 END CF_1FORMULA0031;
271
272 FUNCTION CF_CETFORMULA(INVENTORY_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
273 CURSOR C_ITEM_TARIFF IS
274 SELECT
275 B.ITEM_TARIFF
276 FROM
277 JAI_INV_ITM_SETUPS B
278 WHERE B.INVENTORY_ITEM_ID = CF_CETFORMULA.INVENTORY_ITEM_ID
279 AND B.ORGANIZATION_ID = P_ORGANIZATION_ID;
280 LV_ITEM_TARIFF JAI_INV_ITM_SETUPS.ITEM_TARIFF%TYPE;
281 BEGIN
282 OPEN C_ITEM_TARIFF;
283 FETCH C_ITEM_TARIFF
284 INTO LV_ITEM_TARIFF;
285 CLOSE C_ITEM_TARIFF;
286 IF LV_ITEM_TARIFF IS NOT NULL THEN
287 RETURN (LV_ITEM_TARIFF);
288 END IF;
289 RETURN NULL;
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 RETURN (0);
293 RETURN NULL;
294 END CF_CETFORMULA;
295
296 FUNCTION CF_1FORMULA0032(VENDOR_ID IN NUMBER
297 ,VENDOR_SITE_ID IN NUMBER
298 ,VFLAG IN VARCHAR2) RETURN VARCHAR2 IS
299 CURSOR C_EC_CODE_CUSTOMER IS
300 SELECT
301 A.EC_CODE
302 FROM
303 JAI_CMN_CUS_ADDRESSES A,
304 HZ_CUST_SITE_USES_ALL C
305 WHERE ( A.ADDRESS_ID = C.CUST_ACCT_SITE_ID
306 OR ( A.ADDRESS_ID is NULL
307 AND C.CUST_ACCT_SITE_ID is NULL ) )
308 AND A.CUSTOMER_ID = VENDOR_ID
309 AND C.SITE_USE_ID = VENDOR_SITE_ID;
310 CURSOR C_EC_CODE_VENDOR IS
311 SELECT
312 EC_CODE
313 FROM
314 JAI_CMN_VENDOR_SITES
315 WHERE VENDOR_ID = CF_1FORMULA0032.VENDOR_ID
316 AND ROWNUM = 1
317 AND VENDOR_SITE_ID = CF_1FORMULA0032.VENDOR_SITE_ID;
318 CURSOR C_EC_CODE_HR IS
319 SELECT
320 A.EC_CODE
321 FROM
322 JAI_CMN_INVENTORY_ORGS A
323 WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
324 AND ROWNUM = 1
325 AND A.LOCATION_ID = P_LOCATION_ID;
326 X_EC_CODE JAI_CMN_INVENTORY_ORGS.EC_CODE%TYPE;
327 BEGIN
328 IF VFLAG = 'C' THEN
329 OPEN C_EC_CODE_CUSTOMER;
330 FETCH C_EC_CODE_CUSTOMER
331 INTO X_EC_CODE;
332 CLOSE C_EC_CODE_CUSTOMER;
333 RETURN (X_EC_CODE);
334 ELSIF VFLAG = 'V' THEN
335 OPEN C_EC_CODE_VENDOR;
336 FETCH C_EC_CODE_VENDOR
337 INTO X_EC_CODE;
338 CLOSE C_EC_CODE_VENDOR;
339 RETURN (X_EC_CODE);
340 ELSIF VFLAG = 'O' THEN
341 OPEN C_EC_CODE_HR;
342 FETCH C_EC_CODE_HR
343 INTO X_EC_CODE;
344 CLOSE C_EC_CODE_HR;
345 RETURN (X_EC_CODE);
346 ELSE
347 RETURN (0);
348 END IF;
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 RETURN (0);
352 END CF_1FORMULA0032;
353
354 FUNCTION AFTERREPORT RETURN BOOLEAN IS
355 V_MONTH VARCHAR2(3);
356 V_FINAL_FOLIO NUMBER;
357 BEGIN
358 V_MONTH := TO_CHAR(P_TRN_FROM_DATE
359 ,'MON');
360 V_FINAL_FOLIO := PREV_PAGE + LAST_PAGE;
361 IF VALIDATION_FLAG = 'Y' THEN
362 NULL;
363 END IF;
364 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
365 RETURN (TRUE);
366 END AFTERREPORT;
367
368 FUNCTION CF_FOLIOFORMULA(INVENTORY_ITEM_ID IN NUMBER
369 ,SLNO IN NUMBER) RETURN VARCHAR2 IS
370 BEGIN
371 RETURN (TO_CHAR(NVL(INVENTORY_ITEM_ID
372 ,0)) || '/' || TO_CHAR(SLNO));
373 END CF_FOLIOFORMULA;
374
375 FUNCTION CF_OPENING_BALFORMULA RETURN NUMBER IS
376 CURSOR OPEN_BAL1(FRM_DT IN DATE) IS
377 SELECT
378 OPENING_BALANCE
379 FROM
380 JAI_CMN_RG_PLA_TRXS
381 WHERE TRUNC(CREATION_DATE) >= TRUNC(FRM_DT)
382 AND REGISTER_ID = (
383 SELECT
384 MIN(REGISTER_ID)
385 FROM
386 JAI_CMN_RG_PLA_TRXS
387 WHERE TRUNC(CREATION_DATE) >= TRUNC(FRM_DT) );
388 CURSOR OPEN_BAL2 IS
389 SELECT
390 OPENING_BALANCE
391 FROM
392 JAI_CMN_RG_PLA_TRXS
393 WHERE REGISTER_ID = (
394 SELECT
395 MIN(REGISTER_ID)
396 FROM
397 JAI_CMN_RG_PLA_TRXS );
398 OPEN_BAL NUMBER;
399 BEGIN
400 IF P_TRN_FROM_DATE IS NOT NULL THEN
401 OPEN OPEN_BAL1(P_TRN_FROM_DATE);
402 FETCH OPEN_BAL1
403 INTO OPEN_BAL;
404 CLOSE OPEN_BAL1;
405 ELSE
406 OPEN OPEN_BAL2;
407 FETCH OPEN_BAL2
408 INTO OPEN_BAL;
409 CLOSE OPEN_BAL2;
410 END IF;
411 RETURN (OPEN_BAL);
412 END CF_OPENING_BALFORMULA;
413
414 FUNCTION CF_CR_OPEN_BALFORMULA RETURN NUMBER IS
415 BASIC_BAL NUMBER;
416 BEGIN
417 IF P_TRN_FROM_DATE IS NOT NULL THEN
418 BEGIN
419 SELECT
420 NVL(SUM(CR_BASIC_ED)
421 ,0) - NVL(SUM(DR_BASIC_ED)
422 ,0)
423 INTO BASIC_BAL
424 FROM
425 JAI_CMN_RG_PLA_TRXS
426 WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
427 AND ORGANIZATION_ID = P_ORGANIZATION_ID
428 AND LOCATION_ID = P_LOCATION_ID;
429 EXCEPTION
430 WHEN OTHERS THEN
431 BASIC_BAL := 0;
432 END;
433 ELSE
434 BASIC_BAL := 0;
435 END IF;
436 RETURN (ROUND(BASIC_BAL
437 ,2));
438 END CF_CR_OPEN_BALFORMULA;
439
440 FUNCTION CF_ADDITIONAL_OPEN_BALFORMULA RETURN NUMBER IS
441 ADDITIONAL_BAL NUMBER;
442 BEGIN
443 IF P_TRN_FROM_DATE IS NOT NULL THEN
444 BEGIN
445 SELECT
446 NVL(SUM(CR_ADDITIONAL_ED)
447 ,0) - NVL(SUM(DR_ADDITIONAL_ED)
448 ,0)
449 INTO ADDITIONAL_BAL
450 FROM
451 JAI_CMN_RG_PLA_TRXS
452 WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
453 AND ORGANIZATION_ID = P_ORGANIZATION_ID
454 AND LOCATION_ID = P_LOCATION_ID;
455 EXCEPTION
456 WHEN OTHERS THEN
457 ADDITIONAL_BAL := 0;
458 RETURN (0);
459 END;
460 ELSE
461 ADDITIONAL_BAL := 0;
462 END IF;
463 RETURN (ROUND(ADDITIONAL_BAL
464 ,2));
465 END CF_ADDITIONAL_OPEN_BALFORMULA;
466
467 FUNCTION CF_OTHER_OPEN_BALFORMULA RETURN NUMBER IS
468 OTHER_BAL NUMBER;
469 BEGIN
470 IF P_TRN_FROM_DATE IS NOT NULL THEN
471 BEGIN
472 SELECT
473 NVL(SUM(CR_OTHER_ED)
474 ,0) - NVL(SUM(DR_OTHER_ED)
475 ,0)
476 INTO OTHER_BAL
477 FROM
478 JAI_CMN_RG_PLA_TRXS
479 WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
480 AND ORGANIZATION_ID = P_ORGANIZATION_ID
481 AND LOCATION_ID = P_LOCATION_ID;
482 EXCEPTION
483 WHEN OTHERS THEN
484 OTHER_BAL := 0;
485 END;
486 ELSE
487 OTHER_BAL := 0;
488 END IF;
489 RETURN (ROUND(OTHER_BAL
490 ,2));
491 END CF_OTHER_OPEN_BALFORMULA;
492
493 FUNCTION CF_ACK_RECD_DATEFORMULA(TRANSACTION_ID IN NUMBER
494 ,REGISTER_ID IN NUMBER
495 ,SLNO IN NUMBER
496 ,CREATION_DATE IN DATE) RETURN DATE IS
497 V_ACK_DATE DATE;
498 BEGIN
499 IF TRANSACTION_ID = 91 THEN
500 FOR C_FETCH_ACK_DATE IN (SELECT
501 ACK_RECVD_DATE
502 FROM
503 JAI_CMN_RG_PLA_HDRS A,
504 JAI_CMN_RG_PLA_TRXS B
505 WHERE A.PLA_RG_SR_NO = B.SLNO
506 AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
507 AND A.LOCATION_ID = B.LOCATION_ID
508 AND B.REGISTER_ID = CF_ACK_RECD_DATEFORMULA.REGISTER_ID
509 AND B.SLNO = CF_ACK_RECD_DATEFORMULA.SLNO) LOOP
510 V_ACK_DATE := C_FETCH_ACK_DATE.ACK_RECVD_DATE;
511 END LOOP;
512 ELSE
513 V_ACK_DATE := CREATION_DATE;
514 END IF;
515 IF V_ACK_DATE IS NOT NULL THEN
516 RETURN (V_ACK_DATE);
517 ELSE
518 RETURN (CREATION_DATE);
519 END IF;
520 END CF_ACK_RECD_DATEFORMULA;
521
522 FUNCTION CF_HDR_EC_CODEFORMULA RETURN CHAR IS
523 V_ECC_CODE JAI_CMN_INVENTORY_ORGS.EC_CODE%TYPE;
524 CURSOR C_EC_CODE IS
525 SELECT
526 EC_CODE
527 FROM
528 JAI_CMN_INVENTORY_ORGS
529 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
530 AND LOCATION_ID = P_LOCATION_ID;
531 CURSOR C_EC_CODE_WO_LOCATION IS
532 SELECT
533 EC_CODE
534 FROM
535 JAI_CMN_INVENTORY_ORGS
536 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
537 AND LOCATION_ID = 0;
538 BEGIN
539 OPEN C_EC_CODE;
540 FETCH C_EC_CODE
541 INTO V_ECC_CODE;
542 CLOSE C_EC_CODE;
543 IF V_ECC_CODE IS NULL THEN
544 OPEN C_EC_CODE_WO_LOCATION;
545 FETCH C_EC_CODE_WO_LOCATION
546 INTO V_ECC_CODE;
547 CLOSE C_EC_CODE_WO_LOCATION;
548 END IF;
549 RETURN (V_ECC_CODE);
550 EXCEPTION
551 WHEN OTHERS THEN
552 RETURN (NULL);
553 END CF_HDR_EC_CODEFORMULA;
554
555 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
556 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
557 SELECT
558 CONCURRENT_PROGRAM_ID,
559 NVL(ENABLE_TRACE
560 ,'N')
561 FROM
562 FND_CONCURRENT_REQUESTS
563 WHERE REQUEST_ID = P_REQUEST_ID;
564 CURSOR GET_AUDSID IS
565 SELECT
566 A.SID,
567 A.SERIAL#,
568 B.SPID
569 FROM
570 V$SESSION A,
571 V$PROCESS B
572 WHERE AUDSID = USERENV('SESSIONID')
573 AND A.PADDR = B.ADDR;
574 CURSOR GET_DBNAME IS
575 SELECT
576 NAME
577 FROM
578 V$DATABASE;
579 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
580 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
581 V_SID V$SESSION.SID%TYPE;
582 V_SERIAL V$SESSION.SERIAL#%TYPE;
583 V_SPID V$PROCESS.SPID%TYPE;
584 V_NAME V$DATABASE.NAME%TYPE;
585
586 BEGIN
587 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
588 LP_TRN_FROM_DATE := to_char(P_TRN_FROM_DATE,'DD-MON-YYYY');
589 LP_TRN_TO_DATE := to_char(P_TRN_TO_DATE,'DD-MON-YYYY') ;
590 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
591 /*SRW.MESSAGE(1275
592 ,'Report Version is 120.4 Last modified date is 15/02/2007')*/NULL;
593 BEGIN
594 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
595 FETCH C_PROGRAM_ID
596 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
597 CLOSE C_PROGRAM_ID;
598 /*SRW.MESSAGE(1275
599 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
600 IF V_ENABLE_TRACE = 'Y' THEN
601 OPEN GET_AUDSID;
602 FETCH GET_AUDSID
603 INTO V_SID,V_SERIAL,V_SPID;
604 CLOSE GET_AUDSID;
605 OPEN GET_DBNAME;
606 FETCH GET_DBNAME
607 INTO V_NAME;
608 CLOSE GET_DBNAME;
609 /*SRW.MESSAGE(1275
610 ,'TraceFile Name = ' || LOWER(V_NAME) || '_ora_' || V_SPID || '.trc')*/NULL;
611 EXECUTE IMMEDIATE
612 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
613 END IF;
614 EXCEPTION
615 WHEN OTHERS THEN
616 /*SRW.MESSAGE(1275
617 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
618 END;
619 RETURN (TRUE);
620 OPENING_BALANCE;
621 END BEFOREREPORT;
622
623 FUNCTION CF_CESS_OPEN_BALFORMULA RETURN NUMBER IS
624 CURSOR CUR_CESS_OPEN_BAL(P_ORG_ID IN JAI_CMN_RG_PLA_TRXS.ORGANIZATION_ID%TYPE,P_LOC_ID IN JAI_CMN_RG_PLA_TRXS.LOCATION_ID%TYPE,
625 P_TRN_FRM_DATE IN DATE,CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
626 SELECT
627 CLOSING_BALANCE
628 FROM
629 JAI_CMN_RG_OTHERS
630 WHERE SOURCE_REGISTER = CP_SOURCE_REGISTER
631 AND TAX_TYPE = CP_TAX_TYPE
632 AND SOURCE_REGISTER_ID = (
633 SELECT
634 MAX(A.REGISTER_ID)
635 FROM
636 JAI_CMN_RG_PLA_TRXS A,
637 JAI_CMN_RG_OTHERS B
638 WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID
639 AND B.SOURCE_REGISTER = CP_SOURCE_REGISTER
640 AND B.TAX_TYPE = CP_TAX_TYPE
641 AND A.TRANSACTION_DATE = (
642 SELECT
643 MAX(C.TRANSACTION_DATE)
644 FROM
645 JAI_CMN_RG_PLA_TRXS C,
646 JAI_CMN_RG_OTHERS D
647 WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID
648 AND D.SOURCE_REGISTER = CP_SOURCE_REGISTER
649 AND D.TAX_TYPE = CP_TAX_TYPE
650 AND TRUNC(C.TRANSACTION_DATE) < TRUNC(P_TRN_FRM_DATE)
651 AND C.ORGANIZATION_ID = P_ORG_ID
652 AND C.LOCATION_ID = P_LOC_ID ) );
653 LN_CESS_OPEN_BAL NUMBER := 0;
654 BEGIN
655 IF P_TRN_FROM_DATE IS NOT NULL THEN
656 OPEN CUR_CESS_OPEN_BAL(P_ORG_ID => P_ORGANIZATION_ID,P_LOC_ID => P_LOCATION_ID,P_TRN_FRM_DATE => P_TRN_FROM_DATE,
657 CP_SOURCE_REGISTER => 'PLA',CP_TAX_TYPE => 'EXCISE_EDUCATION_CESS');
658 FETCH CUR_CESS_OPEN_BAL
659 INTO LN_CESS_OPEN_BAL;
660 CLOSE CUR_CESS_OPEN_BAL;
661 END IF;
662 RETURN (ROUND(LN_CESS_OPEN_BAL
663 ,2));
664 END CF_CESS_OPEN_BALFORMULA;
665
666 FUNCTION CF_CESS_CLOSE_BALFORMULA1(REGISTER_ID IN NUMBER) RETURN NUMBER IS
667 CURSOR CUR_CESS_BAL(CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
668 SELECT
669 NVL(SUM(CREDIT)
670 ,0) - NVL(SUM(DEBIT)
671 ,0)
672 FROM
673 JAI_CMN_RG_OTHERS RG_OTH
674 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
675 AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
676 AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;
677
678 LN_SHE_CESS_BAL NUMBER := 0;
679 LN_CESS_BAL NUMBER := 0;
680 BEGIN
681 OPEN CUR_CESS_BAL('PLA','EXCISE_EDUCATION_CESS');
682 FETCH CUR_CESS_BAL
683 INTO LN_CESS_BAL;
684 CLOSE CUR_CESS_BAL;
685
686 /*IF (P_FIRST_REC1 = 'T') THEN
687 CP_EXCISE_CESS_OPEN_BAL := NVL(P_EXCISE_CESS_OPEN_BAL
688 ,0) + NVL(LN_CESS_BAL
689 ,0) + NVL(LN_SHE_CESS_BAL
690 ,0);
691 P_FIRST_REC1 := 'F';
692
693
694 ELSE
695 CP_EXCISE_CESS_OPEN_BAL := NVL(CP_EXCISE_CESS_OPEN_BAL
696 ,0) + NVL(LN_CESS_BAL
697 ,0) + NVL(LN_SHE_CESS_BAL
698 ,0);
699
700 END IF;
701 CP_EXCISE_CESS_OPEN_BAL := CP_EXCISE_CESS_OPEN_BAL - CP_RND_EDU_CESS;*/
702 RETURN (NVL(LN_CESS_BAL
703 ,0));
704 END CF_CESS_CLOSE_BALFORMULA1;
705
706 FUNCTION CF_CESS_CLOSE_BALFORMULA2(REGISTER_ID IN NUMBER) RETURN NUMBER IS
707 CURSOR CUR_SHE_CESS_BAL IS
708 SELECT
709 NVL(SUM(CREDIT)
710 ,0) - NVL(SUM(DEBIT)
711 ,0)
712 FROM
713 JAI_CMN_RG_OTHERS RG_OTH
714 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
715 AND RG_OTH.SOURCE_REGISTER = 'PLA'
716 AND SOURCE_TYPE = 2
717 AND RG_OTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' );
718 LN_SHE_CESS_BAL NUMBER := 0;
719 LN_CESS_BAL NUMBER := 0;
720 BEGIN
721
722 OPEN CUR_SHE_CESS_BAL;
723 FETCH CUR_SHE_CESS_BAL
724 INTO LN_SHE_CESS_BAL;
725 CLOSE CUR_SHE_CESS_BAL;
726
727 RETURN (NVL(LN_SHE_CESS_BAL
728 ,0));
729 END CF_CESS_CLOSE_BALFORMULA2;
730
731 FUNCTION CF_CR_CESSFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
732 CURSOR CUR_CR_CESS(CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
733 SELECT
734 NVL(SUM(CREDIT)
735 ,0)
736 FROM
737 JAI_CMN_RG_OTHERS RG_OTH
738 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
739 AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
740 AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;
741 CURSOR CUR_SHE_CR_CESS IS
742 SELECT
743 NVL(SUM(CREDIT)
744 ,0)
745 FROM
746 JAI_CMN_RG_OTHERS RG_OTH
747 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
748 AND RG_OTH.SOURCE_REGISTER = 'PLA'
749 AND RG_OTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' );
750 LN_SHE_CR_CESS_AMT NUMBER := 0;
751 LV_CR_CESS_AMT NUMBER := 0;
752 BEGIN
753 OPEN CUR_CR_CESS('PLA','EXCISE_EDUCATION_CESS');
754 FETCH CUR_CR_CESS
755 INTO LV_CR_CESS_AMT;
756 CLOSE CUR_CR_CESS;
757 OPEN CUR_SHE_CR_CESS;
758 FETCH CUR_SHE_CR_CESS
759 INTO LN_SHE_CR_CESS_AMT;
760 CLOSE CUR_SHE_CR_CESS;
761 RETURN (ROUND(NVL(LV_CR_CESS_AMT
762 ,0) + NVL(LN_SHE_CR_CESS_AMT
763 ,0)
764 ,2));
765 END CF_CR_CESSFORMULA;
766
767 FUNCTION CF_DB_CESSFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
768 CURSOR CUR_DB_CESS(CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
769 SELECT
770 NVL(SUM(DEBIT)
771 ,0)
772 FROM
773 JAI_CMN_RG_OTHERS RG_OTH
774 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
775 AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
776 AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;
777 CURSOR CUR_SHE_DB_CESS IS
778 SELECT
779 NVL(SUM(DEBIT)
780 ,0)
781 FROM
782 JAI_CMN_RG_OTHERS RG_OTH
783 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
784 AND RG_OTH.SOURCE_REGISTER = 'PLA'
785 AND RG_OTH.TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' );
786 LN_SHE_DB_CESS_AMT NUMBER := 0;
787 LV_DB_CESS_AMT NUMBER := 0;
788 BEGIN
789 OPEN CUR_DB_CESS('PLA','EXCISE_EDUCATION_CESS');
790 FETCH CUR_DB_CESS
791 INTO LV_DB_CESS_AMT;
792 CLOSE CUR_DB_CESS;
793 OPEN CUR_SHE_DB_CESS;
794 FETCH CUR_SHE_DB_CESS
795 INTO LN_SHE_DB_CESS_AMT;
796 CLOSE CUR_SHE_DB_CESS;
797 RETURN (ROUND(NVL(LV_DB_CESS_AMT
798 ,0) + NVL(CP_RND_EDU_CESS
799 ,0) + NVL(LN_SHE_DB_CESS_AMT
800 ,0)
801 ,2));
802 END CF_DB_CESSFORMULA;
803
804 FUNCTION CF_CR_CVD_CESSFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
805 CURSOR CUR_CR_CVD_CESS(CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
806 SELECT
807 NVL(SUM(CREDIT)
808 ,0)
809 FROM
810 JAI_CMN_RG_OTHERS RG_OTH
811 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
812 AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
813 AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;
814 CURSOR CUR_SHE_CR_CVD_CESS IS
815 SELECT
816 NVL(SUM(CREDIT)
817 ,0)
818 FROM
819 JAI_CMN_RG_OTHERS RG_OTH
820 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
821 AND RG_OTH.SOURCE_REGISTER = 'PLA'
822 AND RG_OTH.TAX_TYPE IN ( 'CVD_SH_EDU_CESS' );
823 LN_SHE_CR_CESS_CVD_AMT NUMBER := 0;
824 LV_CR_CESS_CVD_AMT NUMBER := 0;
825 BEGIN
826 OPEN CUR_CR_CVD_CESS('PLA','CVD_EDUCATION_CESS');
827 FETCH CUR_CR_CVD_CESS
828 INTO LV_CR_CESS_CVD_AMT;
829 CLOSE CUR_CR_CVD_CESS;
830 OPEN CUR_SHE_CR_CVD_CESS;
831 FETCH CUR_SHE_CR_CVD_CESS
832 INTO LN_SHE_CR_CESS_CVD_AMT;
833 CLOSE CUR_SHE_CR_CVD_CESS;
834 RETURN (ROUND(NVL(LV_CR_CESS_CVD_AMT
835 ,0) + NVL(LN_SHE_CR_CESS_CVD_AMT
836 ,0)
837 ,2));
838 END CF_CR_CVD_CESSFORMULA;
839
840 FUNCTION CF_DB_CVD_CESSFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
841 CURSOR CUR_DB_CVD_CESS(CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
842 SELECT
843 NVL(SUM(DEBIT)
844 ,0)
845 FROM
846 JAI_CMN_RG_OTHERS RG_OTH
847 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
848 AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
849 AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;
850 CURSOR CUR_SHE_DB_CVD_CESS IS
851 SELECT
852 NVL(SUM(DEBIT)
853 ,0)
854 FROM
855 JAI_CMN_RG_OTHERS RG_OTH
856 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
857 AND RG_OTH.SOURCE_REGISTER = 'PLA'
858 AND RG_OTH.TAX_TYPE IN ( 'CVD_SH_EDU_CESS' );
859 LN_SHE_DB_CVD_CESS_AMT NUMBER := 0;
860 LV_DB_CVD_CESS_AMT NUMBER := 0;
861 BEGIN
862 OPEN CUR_DB_CVD_CESS('PLA','CVD_EDUCATION_CESS');
863 FETCH CUR_DB_CVD_CESS
864 INTO LV_DB_CVD_CESS_AMT;
865 CLOSE CUR_DB_CVD_CESS;
866 OPEN CUR_SHE_DB_CVD_CESS;
867 FETCH CUR_SHE_DB_CVD_CESS
868 INTO LN_SHE_DB_CVD_CESS_AMT;
869 CLOSE CUR_SHE_DB_CVD_CESS;
870 RETURN (ROUND(NVL(LV_DB_CVD_CESS_AMT
871 ,0) + NVL(CP_RND_CVD_CESS
872 ,0) + NVL(LN_SHE_DB_CVD_CESS_AMT
873 ,0)
874 ,2));
875 END CF_DB_CVD_CESSFORMULA;
876
877 FUNCTION CF_CVD_CESS_CLOS_BALFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
878 CURSOR CUR_CESS_CVD_BAL(CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
879 SELECT
880 NVL(SUM(CREDIT)
881 ,0) - NVL(SUM(DEBIT)
882 ,0)
883 FROM
884 JAI_CMN_RG_OTHERS RG_OTH
885 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
886 AND RG_OTH.SOURCE_REGISTER = CP_SOURCE_REGISTER
887 AND RG_OTH.TAX_TYPE = CP_TAX_TYPE;
888 CURSOR CUR_SHE_CESS_CVD_BAL IS
889 SELECT
890 NVL(SUM(CREDIT)
891 ,0) - NVL(SUM(DEBIT)
892 ,0)
893 FROM
894 JAI_CMN_RG_OTHERS RG_OTH
895 WHERE RG_OTH.SOURCE_REGISTER_ID = REGISTER_ID
896 AND RG_OTH.SOURCE_REGISTER = 'PLA'
897 AND RG_OTH.TAX_TYPE IN ( 'CVD_SH_EDU_CESS' );
898 LN_SHE_CESS_CVD_BAL NUMBER := 0;
899 LN_CESS_CVD_BAL NUMBER := 0;
900 BEGIN
901 OPEN CUR_CESS_CVD_BAL('PLA','CVD_EDUCATION_CESS');
902 FETCH CUR_CESS_CVD_BAL
903 INTO LN_CESS_CVD_BAL;
904 CLOSE CUR_CESS_CVD_BAL;
905 OPEN CUR_SHE_CESS_CVD_BAL;
906 FETCH CUR_SHE_CESS_CVD_BAL
907 INTO LN_SHE_CESS_CVD_BAL;
908 CLOSE CUR_SHE_CESS_CVD_BAL;
909 IF (P_FIRST_REC2 = 'T') THEN
910 CP_CVD_CESS_OPEN_BAL := NVL(P_CVD_CESS_OPEN_BAL
911 ,0) + NVL(LN_CESS_CVD_BAL
912 ,0) + NVL(LN_SHE_CESS_CVD_BAL
913 ,0);
914 P_FIRST_REC2 := 'F';
915 ELSE
916 CP_CVD_CESS_OPEN_BAL := NVL(CP_CVD_CESS_OPEN_BAL
917 ,0) + NVL(LN_CESS_CVD_BAL
918 ,0) + NVL(LN_SHE_CESS_CVD_BAL
919 ,0);
920 END IF;
921 CP_CVD_CESS_OPEN_BAL := CP_CVD_CESS_OPEN_BAL - CP_RND_CVD_CESS;
922 RETURN (NVL(CP_CVD_CESS_OPEN_BAL
923 ,0));
924 END CF_CVD_CESS_CLOS_BALFORMULA;
925
926 FUNCTION CF_CVD_CESS_OPEN_BALFORMULA RETURN NUMBER IS
927 CURSOR CUR_CVD_CESS_OPEN_BAL(P_ORG_ID IN JAI_CMN_RG_PLA_TRXS.ORGANIZATION_ID%TYPE,P_LOC_ID IN JAI_CMN_RG_PLA_TRXS.LOCATION_ID%TYPE,
928 P_TRN_FRM_DATE IN DATE,CP_SOURCE_REGISTER IN JAI_CMN_RG_OTHERS.SOURCE_REGISTER%TYPE,CP_TAX_TYPE IN JAI_CMN_RG_OTHERS.TAX_TYPE%TYPE) IS
929 SELECT
930 CLOSING_BALANCE
931 FROM
932 JAI_CMN_RG_OTHERS
933 WHERE SOURCE_REGISTER = CP_SOURCE_REGISTER
934 AND TAX_TYPE = CP_TAX_TYPE
935 AND SOURCE_REGISTER_ID = (
936 SELECT
937 MAX(A.REGISTER_ID)
938 FROM
939 JAI_CMN_RG_PLA_TRXS A,
940 JAI_CMN_RG_OTHERS B
941 WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID
942 AND B.SOURCE_REGISTER = CP_SOURCE_REGISTER
943 AND B.TAX_TYPE = CP_TAX_TYPE
944 AND A.TRANSACTION_DATE = (
945 SELECT
946 MAX(C.TRANSACTION_DATE)
947 FROM
948 JAI_CMN_RG_PLA_TRXS C,
949 JAI_CMN_RG_OTHERS D
950 WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID
951 AND D.SOURCE_REGISTER = CP_SOURCE_REGISTER
952 AND D.TAX_TYPE = CP_TAX_TYPE
953 AND TRUNC(C.TRANSACTION_DATE) < TRUNC(P_TRN_FRM_DATE)
954 AND C.ORGANIZATION_ID = P_ORG_ID
955 AND C.LOCATION_ID = P_LOC_ID ) );
956 LN_CVD_CESS_OPEN_BAL NUMBER := 0;
957 BEGIN
958 IF P_TRN_FROM_DATE IS NOT NULL THEN
959 OPEN CUR_CVD_CESS_OPEN_BAL(P_ORG_ID => P_ORGANIZATION_ID,P_LOC_ID => P_LOCATION_ID,P_TRN_FRM_DATE => P_TRN_FROM_DATE,
960 CP_SOURCE_REGISTER => 'PLA',CP_TAX_TYPE => 'CVD_EDUCATION_CESS');
961 FETCH CUR_CVD_CESS_OPEN_BAL
962 INTO LN_CVD_CESS_OPEN_BAL;
963 CLOSE CUR_CVD_CESS_OPEN_BAL;
964 END IF;
965 RETURN (ROUND(LN_CVD_CESS_OPEN_BAL
966 ,2));
967 END CF_CVD_CESS_OPEN_BALFORMULA;
968
969 FUNCTION CF_ROUNDING_AMTSFORMULA(TRANSACTION_ID IN NUMBER
970 ,ROUNDING_ID IN NUMBER
971 ,DR_INVOICE_NO IN VARCHAR2) RETURN NUMBER IS
972 CURSOR C_ROUNDING_DTL(CP_ROUNDING_ID IN NUMBER) IS
973 SELECT
974 REGISTER_ID
975 FROM
976 JAI_CMN_RG_ROUND_HDRS
977 WHERE ROUNDING_ID = CP_ROUNDING_ID;
978 CURSOR C_PLA_RND_AMTS(CP_REGISTER_ID IN NUMBER) IS
979 SELECT
980 - NVL(CR_BASIC_ED
981 ,0) + NVL(DR_BASIC_ED
982 ,0) BASIC_ED,
983 - NVL(CR_ADDITIONAL_ED
984 ,0) + NVL(DR_ADDITIONAL_ED
985 ,0) ADDITIONAL_ED,
986 - NVL(CR_OTHER_ED
987 ,0) + NVL(DR_OTHER_ED
988 ,0) OTHER_ED
989 FROM
990 JAI_CMN_RG_PLA_TRXS
991 WHERE REGISTER_ID = CP_REGISTER_ID;
992 CURSOR C_RND_CESS_AMT(CP_REGISTER_ID IN NUMBER,CP_SOURCE_TYPE IN NUMBER,P_TAX_TYPE IN VARCHAR2) IS
993 SELECT
994 SUM(NVL(DEBIT
995 ,0) - NVL(CREDIT
996 ,0))
997 FROM
998 JAI_CMN_RG_OTHERS
999 WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
1000 AND SOURCE_TYPE = CP_SOURCE_TYPE
1001 AND TAX_TYPE = P_TAX_TYPE;
1002 V_RND_BASIC_ED NUMBER;
1003 V_RND_ADDITIONAL_ED NUMBER;
1004 V_RND_OTHER_ED NUMBER;
1005 V_RND_EDU_CESS NUMBER;
1006 V_RND_CVD_CESS NUMBER;
1007 V_REGISTER_ID NUMBER;
1008 V_RND_SHE_EDU_CESS NUMBER;
1009 V_RND_SHE_CVD_CESS NUMBER;
1010 BEGIN
1011 IF TRANSACTION_ID = 19 AND ROUNDING_ID > 0 THEN
1012 OPEN C_ROUNDING_DTL(ROUNDING_ID);
1013 FETCH C_ROUNDING_DTL
1014 INTO V_REGISTER_ID;
1015 CLOSE C_ROUNDING_DTL;
1016 OPEN C_PLA_RND_AMTS(V_REGISTER_ID);
1017 FETCH C_PLA_RND_AMTS
1018 INTO V_RND_BASIC_ED,V_RND_ADDITIONAL_ED,V_RND_OTHER_ED;
1019 CLOSE C_PLA_RND_AMTS;
1020 OPEN C_RND_CESS_AMT(V_REGISTER_ID,2,'EXCISE_EDUCATION_CESS');
1021 FETCH C_RND_CESS_AMT
1022 INTO V_RND_EDU_CESS;
1023 CLOSE C_RND_CESS_AMT;
1024 OPEN C_RND_CESS_AMT(V_REGISTER_ID,2,'CVD_EDUCATION_CESS');
1025 FETCH C_RND_CESS_AMT
1026 INTO V_RND_CVD_CESS;
1027 CLOSE C_RND_CESS_AMT;
1028 OPEN C_RND_CESS_AMT(V_REGISTER_ID,2,'EXCISE_SH_EDU_CESS');
1029 FETCH C_RND_CESS_AMT
1030 INTO V_RND_SHE_EDU_CESS;
1031 CLOSE C_RND_CESS_AMT;
1032 OPEN C_RND_CESS_AMT(V_REGISTER_ID,2,'CVD_SH_EDU_CESS');
1033 FETCH C_RND_CESS_AMT
1034 INTO V_RND_SHE_CVD_CESS;
1035 CLOSE C_RND_CESS_AMT;
1036 END IF;
1037 CP_RND_BASIC_ED := NVL(V_RND_BASIC_ED
1038 ,0);
1039 CP_RND_OTHER_ED := NVL(V_RND_OTHER_ED
1040 ,0);
1041 CP_RND_ADDL_ED := NVL(V_RND_ADDITIONAL_ED
1042 ,0);
1043 CP_RND_EDU_CESS := NVL(V_RND_EDU_CESS
1044 ,0);
1045 CP_RND_CVD_CESS := NVL(V_RND_CVD_CESS
1046 ,0);
1047 CP_RND_SHE_EDU_CESS := NVL(V_RND_EDU_CESS
1048 ,0);
1049 CP_RND_SHE_CVD_CESS := NVL(V_RND_CVD_CESS
1050 ,0);
1051 --raise_application_error(-20101, ' value of CP_RND_BASIC_ED=' ||CP_RND_BASIC_ED );
1052 /*SRW.MESSAGE(1001
1053 ,'ExInv:' || DR_INVOICE_NO || ',rndBasic:' || CP_RND_BASIC_ED || ',rndOth:' || CP_RND_OTHER_ED || ',rndAddl:' || CP_RND_ADDL_ED || ',
1054 rndEdCess:' || CP_RND_EDU_CESS || ',rndCvd:' || CP_RND_CVD_CESS)*/NULL;
1055 RETURN (CP_RND_BASIC_ED + CP_RND_OTHER_ED + CP_RND_ADDL_ED + CP_RND_EDU_CESS + CP_RND_CVD_CESS + CP_RND_SHE_EDU_CESS + CP_RND_SHE_CVD_CESS);
1056 END CF_ROUNDING_AMTSFORMULA;
1057
1058 FUNCTION CF_DR_BASIC_EDFORMULA(DR_BASIC_ED IN NUMBER) RETURN NUMBER IS
1059 BEGIN
1060 --raise_application_error(-20101, 'value of DR_BASIC_ED= '||DR_BASIC_ED||' value of CP_RND_BASIC_ED=' ||CP_RND_BASIC_ED );
1061 RETURN (NVL(DR_BASIC_ED ,0) + CP_RND_BASIC_ED);
1062
1063
1064 END CF_DR_BASIC_EDFORMULA;
1065
1066 FUNCTION CF_DR_ADDL_EDFORMULA(DR_ADDITIONAL_ED IN NUMBER) RETURN NUMBER IS
1067 BEGIN
1068 RETURN (NVL(DR_ADDITIONAL_ED
1069 ,0) + CP_RND_ADDL_ED);
1070 END CF_DR_ADDL_EDFORMULA;
1071
1072 FUNCTION CF_DR_OTHER_EDFORMULA(DR_OTHER_ED IN NUMBER) RETURN NUMBER IS
1073 BEGIN
1074 RETURN (NVL(DR_OTHER_ED
1075 ,0) + CP_RND_OTHER_ED);
1076 END CF_DR_OTHER_EDFORMULA;
1077
1078 PROCEDURE OPENING_BALANCE IS
1079 BASIC_BAL NUMBER;
1080 ADDITIONAL_BAL NUMBER;
1081 OTHER_BAL NUMBER;
1082 CURSOR CUR_FIN_YEAR(CP_ORG_ID IN JAI_CMN_FIN_YEARS.ORGANIZATION_ID%TYPE,CP_FROM_DATE IN DATE) IS
1083 SELECT
1084 JCFY.FIN_YEAR
1085 FROM
1086 JAI_CMN_FIN_YEARS JCFY
1087 WHERE JCFY.ORGANIZATION_ID = CP_ORG_ID
1088 AND CP_FROM_DATE BETWEEN JCFY.FIN_YEAR_START_DATE
1089 AND JCFY.FIN_YEAR_END_DATE;
1090 CURSOR CUR_REG_ID(CP_TAX_TYPE IN VARCHAR2,CP_FIN_YEAR IN NUMBER) IS
1091 SELECT
1092 JCRPT.REGISTER_ID
1093 FROM
1094 JAI_CMN_RG_PLA_TRXS JCRPT
1095 WHERE JCRPT.LOCATION_ID = P_LOCATION_ID
1096 AND JCRPT.ORGANIZATION_ID = P_ORGANIZATION_ID
1097 AND JCRPT.SLNO = (
1098 SELECT
1099 MAX(JCRPT1.SLNO)
1100 FROM
1101 JAI_CMN_RG_PLA_TRXS JCRPT1
1102 WHERE JCRPT1.LOCATION_ID = P_LOCATION_ID
1103 AND JCRPT1.ORGANIZATION_ID = P_ORGANIZATION_ID
1104 AND JCRPT1.CREATION_DATE < P_TRN_FROM_DATE
1105 AND JCRPT1.FIN_YEAR = CP_FIN_YEAR
1106 AND EXISTS (
1107 SELECT
1108 1
1109 FROM
1110 JAI_CMN_RG_OTHERS JCRG
1111 WHERE JCRG.SOURCE_REGISTER_ID = JCRPT1.REGISTER_ID
1112 AND JCRG.SOURCE_TYPE = 2
1113 AND JCRG.SOURCE_REGISTER = 'PLA'
1114 AND JCRG.TAX_TYPE = CP_TAX_TYPE ) )
1115 AND JCRPT.FIN_YEAR = CP_FIN_YEAR;
1116 CURSOR CUR_OPEN_BAL(CP_REGISTER_ID IN NUMBER,CP_TAX_TYPE IN VARCHAR2) IS
1117 SELECT
1118 CLOSING_BALANCE
1119 FROM
1120 JAI_CMN_RG_OTHERS
1121 WHERE SOURCE_REGISTER_ID = CP_REGISTER_ID
1122 AND TAX_TYPE = CP_TAX_TYPE
1123 AND SOURCE_REGISTER = 'PLA';
1124 LN_EXC_CESS_OPEN_BAL NUMBER := 0;
1125 LN_CVD_CESS_OPEN_BAL NUMBER := 0;
1126 LV_MAST_ORG_FLAG JAI_CMN_INVENTORY_ORGS.MASTER_ORG_FLAG%TYPE;
1127 LN_MIN_REG_ID NUMBER;
1128 LN_MAX_REG_ID NUMBER;
1129 LN_FIN_YEAR NUMBER;
1130 LN_REG_ID JAI_CMN_RG_PLA_TRXS.REGISTER_ID%TYPE;
1131 LN_EXC_SHE_CESS_OPEN_BAL NUMBER := 0;
1132 LN_CVD_SHE_CESS_OPEN_BAL NUMBER := 0;
1133 BEGIN
1134 OPEN CUR_FIN_YEAR(P_ORGANIZATION_ID,P_TRN_FROM_DATE);
1135 FETCH CUR_FIN_YEAR
1136 INTO LN_FIN_YEAR;
1137 CLOSE CUR_FIN_YEAR;
1138 BEGIN
1139 IF P_TRN_FROM_DATE IS NOT NULL THEN
1140 SELECT
1141 NVL(SUM(CR_BASIC_ED)
1142 ,0) - NVL(SUM(DR_BASIC_ED)
1143 ,0)
1144 INTO BASIC_BAL
1145 FROM
1146 JAI_CMN_RG_PLA_TRXS
1147 WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
1148 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1149 AND LOCATION_ID = P_LOCATION_ID;
1150 ELSE
1151 BASIC_BAL := 0;
1152 END IF;
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 BASIC_BAL := 0;
1156 END;
1157 BEGIN
1158 IF P_TRN_FROM_DATE IS NOT NULL THEN
1159 SELECT
1160 NVL(SUM(CR_ADDITIONAL_ED)
1161 ,0) - NVL(SUM(DR_ADDITIONAL_ED)
1162 ,0)
1163 INTO ADDITIONAL_BAL
1164 FROM
1165 JAI_CMN_RG_PLA_TRXS
1166 WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
1167 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1168 AND LOCATION_ID = P_LOCATION_ID;
1169 ELSE
1170 ADDITIONAL_BAL := 0;
1171 END IF;
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 ADDITIONAL_BAL := 0;
1175 END;
1176 BEGIN
1177 IF P_TRN_FROM_DATE IS NOT NULL THEN
1178 SELECT
1179 NVL(SUM(CR_OTHER_ED)
1180 ,0) - NVL(SUM(DR_OTHER_ED)
1181 ,0)
1182 INTO OTHER_BAL
1183 FROM
1184 JAI_CMN_RG_PLA_TRXS
1185 WHERE TRUNC(CREATION_DATE) < TRUNC(P_TRN_FROM_DATE)
1186 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1187 AND LOCATION_ID = P_LOCATION_ID;
1188 ELSE
1189 OTHER_BAL := 0;
1190 END IF;
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 OTHER_BAL := 0;
1194 END;
1195 BEGIN
1196 IF P_TRN_FROM_DATE IS NOT NULL THEN
1197 LN_MIN_REG_ID := 0;
1198 LN_MAX_REG_ID := 0;
1199 LN_REG_ID := NULL;
1200 OPEN CUR_REG_ID('EXCISE_EDUCATION_CESS',LN_FIN_YEAR);
1201 FETCH CUR_REG_ID
1202 INTO LN_REG_ID;
1203 IF CUR_REG_ID%NOTFOUND THEN
1204 CLOSE CUR_REG_ID;
1205 OPEN CUR_REG_ID('EXCISE_EDUCATION_CESS',LN_FIN_YEAR - 1);
1206 FETCH CUR_REG_ID
1207 INTO LN_REG_ID;
1208 CLOSE CUR_REG_ID;
1209 ELSE
1210 CLOSE CUR_REG_ID;
1211 END IF;
1212 OPEN CUR_OPEN_BAL(LN_REG_ID,'EXCISE_EDUCATION_CESS');
1213 FETCH CUR_OPEN_BAL
1214 INTO LN_EXC_CESS_OPEN_BAL;
1215 CLOSE CUR_OPEN_BAL;
1216 LN_REG_ID := NULL;
1217 OPEN CUR_REG_ID('EXCISE_SH_EDU_CESS',LN_FIN_YEAR);
1218 FETCH CUR_REG_ID
1219 INTO LN_REG_ID;
1220 IF CUR_REG_ID%NOTFOUND THEN
1221 CLOSE CUR_REG_ID;
1222 OPEN CUR_REG_ID('EXCISE_SH_EDU_CESS',LN_FIN_YEAR - 1);
1223 FETCH CUR_REG_ID
1224 INTO LN_REG_ID;
1225 CLOSE CUR_REG_ID;
1226 ELSE
1227 CLOSE CUR_REG_ID;
1228 END IF;
1229 OPEN CUR_OPEN_BAL(LN_REG_ID,'EXCISE_SH_EDU_CESS');
1230 FETCH CUR_OPEN_BAL
1231 INTO LN_EXC_SHE_CESS_OPEN_BAL;
1232 CLOSE CUR_OPEN_BAL;
1233 END IF;
1234 END;
1235 BEGIN
1236 IF P_TRN_FROM_DATE IS NOT NULL THEN
1237 LN_REG_ID := NULL;
1238 OPEN CUR_REG_ID('CVD_EDUCATION_CESS',LN_FIN_YEAR);
1239 FETCH CUR_REG_ID
1240 INTO LN_REG_ID;
1241 IF CUR_REG_ID%NOTFOUND THEN
1242 CLOSE CUR_REG_ID;
1243 OPEN CUR_REG_ID('CVD_EDUCATION_CESS',LN_FIN_YEAR - 1);
1244 FETCH CUR_REG_ID
1245 INTO LN_REG_ID;
1246 CLOSE CUR_REG_ID;
1247 ELSE
1248 CLOSE CUR_REG_ID;
1249 END IF;
1250 OPEN CUR_OPEN_BAL(LN_REG_ID,'CVD_EDUCATION_CESS');
1251 FETCH CUR_OPEN_BAL
1252 INTO LN_CVD_CESS_OPEN_BAL;
1253 CLOSE CUR_OPEN_BAL;
1254 LN_REG_ID := 0;
1255 OPEN CUR_REG_ID('CVD_SH_EDU_CESS',LN_FIN_YEAR);
1256 FETCH CUR_REG_ID
1257 INTO LN_REG_ID;
1258 IF CUR_REG_ID%NOTFOUND THEN
1259 CLOSE CUR_REG_ID;
1260 OPEN CUR_REG_ID('CVD_SH_EDU_CESS',LN_FIN_YEAR - 1);
1261 FETCH CUR_REG_ID
1262 INTO LN_REG_ID;
1263 CLOSE CUR_REG_ID;
1264 ELSE
1265 CLOSE CUR_REG_ID;
1266 END IF;
1267 OPEN CUR_OPEN_BAL(LN_REG_ID,'CVD_SH_EDU_CESS');
1268 FETCH CUR_OPEN_BAL
1269 INTO LN_CVD_SHE_CESS_OPEN_BAL;
1270 CLOSE CUR_OPEN_BAL;
1271 END IF;
1272 END;
1273 CP_CR_OPEN_BAL := (ROUND(BASIC_BAL
1274 ,2));
1275 CP_ADDITIONAL_OPEN_BAL := ROUND(ADDITIONAL_BAL
1276 ,2);
1277 CP_OTHER_OPEN_BAL := ROUND(OTHER_BAL
1278 ,2);
1279 CP_CESS_OPEN_BAL_CVD := ROUND(LN_CVD_CESS_OPEN_BAL + LN_CVD_SHE_CESS_OPEN_BAL
1280 ,2);
1281 CP_CESS_OPEN_BAL := ROUND(LN_EXC_CESS_OPEN_BAL + LN_EXC_SHE_CESS_OPEN_BAL
1282 ,2);
1283 END OPENING_BALANCE;
1284
1285 FUNCTION CP_RND_BASIC_ED_P RETURN NUMBER IS
1286 BEGIN
1287 RETURN CP_RND_BASIC_ED;
1288 END CP_RND_BASIC_ED_P;
1289
1290 FUNCTION CP_RND_ADDL_ED_P RETURN NUMBER IS
1291 BEGIN
1292 RETURN CP_RND_ADDL_ED;
1293 END CP_RND_ADDL_ED_P;
1294
1295 FUNCTION CP_RND_OTHER_ED_P RETURN NUMBER IS
1296 BEGIN
1297 RETURN CP_RND_OTHER_ED;
1298 END CP_RND_OTHER_ED_P;
1299
1300 FUNCTION CP_RND_EDU_CESS_P RETURN NUMBER IS
1301 BEGIN
1302 RETURN CP_RND_EDU_CESS;
1303 END CP_RND_EDU_CESS_P;
1304
1305 FUNCTION CP_RND_CVD_CESS_P RETURN NUMBER IS
1306 BEGIN
1307 RETURN CP_RND_CVD_CESS;
1308 END CP_RND_CVD_CESS_P;
1309
1310 FUNCTION CP_RND_SHE_EDU_CESS_P RETURN NUMBER IS
1311 BEGIN
1312 RETURN CP_RND_SHE_EDU_CESS;
1313 END CP_RND_SHE_EDU_CESS_P;
1314
1315 FUNCTION CP_RND_SHE_CVD_CESS_P RETURN NUMBER IS
1316 BEGIN
1317 RETURN CP_RND_SHE_CVD_CESS;
1318 END CP_RND_SHE_CVD_CESS_P;
1319
1320 FUNCTION CP_BED_OPEN_BAL_P RETURN NUMBER IS
1321 BEGIN
1322 RETURN CP_BED_OPEN_BAL;
1323 END CP_BED_OPEN_BAL_P;
1324
1325 FUNCTION CP_AED_OPEN_BAL_P RETURN NUMBER IS
1326 BEGIN
1327 RETURN CP_AED_OPEN_BAL;
1328 END CP_AED_OPEN_BAL_P;
1329
1330 FUNCTION CP_SED_OPEN_BAL_P RETURN NUMBER IS
1331 BEGIN
1332 RETURN CP_SED_OPEN_BAL;
1333 END CP_SED_OPEN_BAL_P;
1334
1335 FUNCTION CP_PAGE_NO_P RETURN NUMBER IS
1336 BEGIN
1337 RETURN CP_PAGE_NO;
1338 END CP_PAGE_NO_P;
1339
1340 FUNCTION CP_EXCISE_CESS_OPEN_BAL_P RETURN NUMBER IS
1341 BEGIN
1342 RETURN CP_EXCISE_CESS_OPEN_BAL;
1343 END CP_EXCISE_CESS_OPEN_BAL_P;
1344
1345 FUNCTION CP_CVD_CESS_OPEN_BAL_P RETURN NUMBER IS
1346 BEGIN
1347 RETURN CP_CVD_CESS_OPEN_BAL;
1348 END CP_CVD_CESS_OPEN_BAL_P;
1349
1350 FUNCTION CP_CR_OPEN_BAL_P RETURN NUMBER IS
1351 BEGIN
1352 RETURN CP_CR_OPEN_BAL;
1353 END CP_CR_OPEN_BAL_P;
1354
1355 FUNCTION CP_ADDITIONAL_OPEN_BAL_P RETURN NUMBER IS
1356 BEGIN
1357 RETURN CP_ADDITIONAL_OPEN_BAL;
1358 END CP_ADDITIONAL_OPEN_BAL_P;
1359
1360 FUNCTION CP_OTHER_OPEN_BAL_P RETURN NUMBER IS
1361 BEGIN
1362 RETURN CP_OTHER_OPEN_BAL;
1363 END CP_OTHER_OPEN_BAL_P;
1364
1365 FUNCTION CP_CESS_OPEN_BAL_P RETURN NUMBER IS
1366 BEGIN
1367 RETURN CP_CESS_OPEN_BAL;
1368 END CP_CESS_OPEN_BAL_P;
1369
1370 FUNCTION CP_CESS_OPEN_BAL_CVD_P RETURN NUMBER IS
1371 BEGIN
1372 RETURN CP_CESS_OPEN_BAL_CVD;
1373 END CP_CESS_OPEN_BAL_CVD_P;
1374
1375 END JA_JAINPLA_XMLP_PKG;
1376
1377
1378