[Home] [Help]
PACKAGE BODY: APPS.JA_JAINER1_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINER1_XMLP_PKG AS
2 /* $Header: JAINER1B.pls 120.1 2007/12/25 16:17:45 dwkrishn noship $ */
3 FUNCTION CENVAT_BASIC_ED RETURN NUMBER IS
4 V_BASIC_ED NUMBER;
5 BEGIN
6 SELECT
7 NVL(SUM(BASIC_ED)
8 ,0)
9 INTO V_BASIC_ED
10 FROM
11 JAI_CMN_RG_I_TRXS
12 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
13 AND LOCATION_ID = P_LOCATION_ID
14 AND PAYMENT_REGISTER in ( 'RG23A' , 'RG23C' )
15 AND TRUNC(CREATION_DATE) >= P_START_DATE
16 AND TRUNC(CREATION_DATE) <= P_END_DATE;
17 RETURN (V_BASIC_ED);
18 END CENVAT_BASIC_ED;
19
20 FUNCTION CF_ORGANIZATION_NAMEFORMULA RETURN CHAR IS
21 V_ORGANIZATION_NAME VARCHAR2(60);
22 BEGIN
23 IF P_ORGANIZATION_NAME IS NULL THEN
24 SELECT
25 ORGANIZATION_NAME
26 INTO V_ORGANIZATION_NAME
27 FROM
28 ORG_ORGANIZATION_DEFINITIONS
29 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
30 ELSE
31 V_ORGANIZATION_NAME := P_ORGANIZATION_NAME;
32 END IF;
33 RETURN (V_ORGANIZATION_NAME);
34 EXCEPTION
35 WHEN OTHERS THEN
36 /*SRW.MESSAGE(1275
37 ,'CF_organization_name:' || SQLERRM)*/NULL;
38 RETURN NULL;
39 END CF_ORGANIZATION_NAMEFORMULA;
40
41 FUNCTION CF_EC_CODEFORMULA RETURN CHAR IS
42 V_EC_CODE VARCHAR2(50);
43 BEGIN
44 SELECT
45 EC_CODE
46 INTO V_EC_CODE
47 FROM
48 JAI_CMN_INVENTORY_ORGS
49 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
50 AND LOCATION_ID = P_LOCATION_ID;
51 RETURN (V_EC_CODE);
52 EXCEPTION
53 WHEN OTHERS THEN
54 /*SRW.MESSAGE(1275
55 ,'CF_ec_code:' || SQLERRM)*/NULL;
56 RETURN NULL;
57 END CF_EC_CODEFORMULA;
58
59 FUNCTION CF_CENVAT_DUTYFORMULA(CETSH IN VARCHAR2
60 ,INVENTORY_ITEM_ID IN NUMBER
61 ,UNITS IN VARCHAR2) RETURN NUMBER IS
62 LN_BASIC_ED JAI_CMN_RG_I_TRXS.BASIC_ED%TYPE;
63 BEGIN
64 SELECT
65 NVL(SUM(BASIC_ED)
66 ,0)
67 INTO LN_BASIC_ED
68 FROM
69 JAI_CMN_RG_I_TRXS A,
70 JAI_INV_ITM_SETUPS C
71 WHERE A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
72 AND A.ORGANIZATION_ID = C.ORGANIZATION_ID
73 AND ( C.ITEM_TARIFF = CETSH
74 OR A.INVENTORY_ITEM_ID = cf_cenvat_dutyformula.INVENTORY_ITEM_ID )
75 AND A.PRIMARY_UOM_CODE = UNITS
76 AND A.PAYMENT_REGISTER in ( 'RG23A' , 'RG23C' )
77 AND A.TRANSACTION_TYPE = 'I'
78 AND A.ISSUE_TYPE in ( 'OPE' , 'HU' , 'EWE' )
79 AND A.LOCATION_ID = P_LOCATION_ID
80 AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
81 AND TRUNC(A.CREATION_DATE) >= P_START_DATE
82 AND TRUNC(A.CREATION_DATE) <= P_END_DATE
83 AND A.EXCISE_DUTY_RATE <> 0;
84 RETURN LN_BASIC_ED;
85 EXCEPTION
86 WHEN OTHERS THEN
87 /*SRW.MESSAGE(1275
88 ,'CF_cenvat_duty:' || SQLERRM)*/NULL;
89 RETURN NULL;
90 END CF_CENVAT_DUTYFORMULA;
91
92 FUNCTION CF_ASSESSABLE_VALUEFORMULA0008(CF_RATE IN NUMBER
93 ,CF_DUTY_PAYABLE IN NUMBER) RETURN NUMBER IS
94 BEGIN
95 IF NVL(CF_RATE
96 ,0) = 0 THEN
97 RETURN CF_RATE;
98 ELSE
99 RETURN ROUND(((NVL(CF_DUTY_PAYABLE
100 ,0) + NVL(CP_AED_DUTY_PAYABLE
101 ,0)) * 100) / CF_RATE
102 ,0);
103 END IF;
104 END CF_ASSESSABLE_VALUEFORMULA0008;
105
106 FUNCTION CF_CENVAT_ACC_CURRENTFORMULA RETURN NUMBER IS
107 CURSOR GET_PLA_AMOUNT IS
108 SELECT
109 SUM(NVL(CR_BASIC_ED
110 ,0) + NVL(CR_OTHER_ED
111 ,0))
112 FROM
113 JAI_CMN_RG_PLA_TRXS
114 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
115 AND LOCATION_ID = P_LOCATION_ID
116 AND CREATION_DATE >= P_START_DATE
117 AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
118 ,SYSDATE))
119 AND TRANSACTION_SOURCE_NUM = 91;
120 LN_PLA_AMOUNT NUMBER;
121 BEGIN
122 OPEN GET_PLA_AMOUNT;
123 FETCH GET_PLA_AMOUNT
124 INTO LN_PLA_AMOUNT;
125 CLOSE GET_PLA_AMOUNT;
126 RETURN ROUND(NVL(LN_PLA_AMOUNT
127 ,0)
128 ,0);
129 EXCEPTION
130 WHEN OTHERS THEN
131 /*SRW.MESSAGE(1275
132 ,'CF_cenvat_acc_current:' || SQLERRM)*/NULL;
133 RETURN NULL;
134 END CF_CENVAT_ACC_CURRENTFORMULA;
135
136 FUNCTION CF_OTHER_ACC_CURRENTFORMULA RETURN NUMBER IS
137 CURSOR GET_ADDL_AMOUNT IS
138 SELECT
139 SUM(NVL(CREDIT
140 ,0))
141 FROM
142 JAI_CMN_RG_OTHERS
143 WHERE SOURCE_TYPE = 2
144 AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS )
145 AND SOURCE_REGISTER_ID in (
146 SELECT
147 REGISTER_ID
148 FROM
149 JAI_CMN_RG_PLA_TRXS
150 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
151 AND LOCATION_ID = P_LOCATION_ID
152 AND CREATION_DATE >= P_START_DATE
153 AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
154 ,SYSDATE))
155 AND TRANSACTION_SOURCE_NUM = 91 );
156 LN_ADDL_DUTY_AMOUNT NUMBER;
157 BEGIN
158 OPEN GET_ADDL_AMOUNT;
159 FETCH GET_ADDL_AMOUNT
160 INTO LN_ADDL_DUTY_AMOUNT;
161 CLOSE GET_ADDL_AMOUNT;
162 RETURN ROUND(LN_ADDL_DUTY_AMOUNT
163 ,0);
164 EXCEPTION
165 WHEN OTHERS THEN
166 /*SRW.MESSAGE(1275
167 ,'CF_other_acc_current:' || SQLERRM)*/NULL;
168 RETURN NULL;
169 END CF_OTHER_ACC_CURRENTFORMULA;
170
171 FUNCTION CF_CENVAT_ACC_CREDITFORMULA RETURN NUMBER IS
172 LN_CR_BASIC_ED JAI_CMN_RG_23AC_II_TRXS.CR_BASIC_ED%TYPE;
173 BEGIN
174 SELECT
175 SUM(DECODE(UPPER(A.PAYMENT_REGISTER)
176 ,'RG23A'
177 ,NVL(A.BASIC_ED
178 ,0) + NVL(A.ADDITIONAL_ED
179 ,0) + NVL(A.OTHER_ED
180 ,0)
181 ,'RG23C'
182 ,NVL(A.BASIC_ED
183 ,0) + NVL(A.ADDITIONAL_ED
184 ,0) + NVL(A.OTHER_ED
185 ,0))) CENVAT_DUTY_PAYABLE
186 INTO LN_CR_BASIC_ED
187 FROM
188 JAI_CMN_RG_I_TRXS A,
189 MTL_SYSTEM_ITEMS B,
190 JAI_INV_ITM_SETUPS C
191 WHERE A.TRANSACTION_TYPE in ( 'I' , 'IA' , 'IOI' , 'PI' )
192 AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
193 AND C.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
194 AND C.ORGANIZATION_ID = B.ORGANIZATION_ID
195 AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
196 AND A.LOCATION_ID = P_LOCATION_ID
197 AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
198 AND TRUNC(A.CREATION_DATE) >= P_START_DATE
199 AND TRUNC(A.CREATION_DATE) <= NVL(P_END_DATE
200 ,SYSDATE);
201 RETURN ROUND(LN_CR_BASIC_ED
202 ,0);
203 EXCEPTION
204 WHEN OTHERS THEN
205 /*SRW.MESSAGE(1275
206 ,'CF_cenvat_acc_credit:' || SQLERRM)*/NULL;
207 RETURN NULL;
208 END CF_CENVAT_ACC_CREDITFORMULA;
209
210 FUNCTION CF_OTHER_ACC_CREDITFORMULA RETURN NUMBER IS
211 LN_CR_OTHER_ED JAI_CMN_RG_23AC_II_TRXS.CR_OTHER_ED%TYPE;
212 CURSOR CUR_GET_AMOUNT IS
213 SELECT
214 SUM(NVL(DEBIT
215 ,0))
216 FROM
217 JAI_CMN_RG_OTHERS
218 WHERE TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS )
219 AND SOURCE_TYPE = 1
220 AND SOURCE_REGISTER_ID in (
221 SELECT
222 REGISTER_ID
223 FROM
224 JAI_CMN_RG_23AC_II_TRXS
225 WHERE LOCATION_ID = P_LOCATION_ID
226 AND ORGANIZATION_ID = P_ORGANIZATION_ID
227 AND TRUNC(CREATION_DATE) >= P_START_DATE
228 AND TRUNC(CREATION_DATE) <= TRUNC(NVL(P_END_DATE
229 ,SYSDATE)) );
230 BEGIN
231 OPEN CUR_GET_AMOUNT;
232 FETCH CUR_GET_AMOUNT
233 INTO LN_CR_OTHER_ED;
234 CLOSE CUR_GET_AMOUNT;
235 RETURN ROUND(LN_CR_OTHER_ED
236 ,0);
237 EXCEPTION
238 WHEN OTHERS THEN
239 /*SRW.MESSAGE(1275
240 ,'CF_other_acc_credit:' || SQLERRM)*/NULL;
241 RETURN NULL;
242 END CF_OTHER_ACC_CREDITFORMULA;
243
244 FUNCTION CF_CENVAT_TOTALFORMULA(CF_CENVAT_ACC_CURRENT IN NUMBER
245 ,CF_CENVAT_ACC_CREDIT IN NUMBER) RETURN NUMBER IS
246 BEGIN
247 RETURN ROUND((NVL(CF_CENVAT_ACC_CURRENT
248 ,0) + NVL(CF_CENVAT_ACC_CREDIT
249 ,0))
250 ,0);
251 END CF_CENVAT_TOTALFORMULA;
252
253 FUNCTION CF_OTHER_TOTALFORMULA(CF_OTHER_ACC_CURRENT IN NUMBER
254 ,CF_OTHER_ACC_CREDIT IN NUMBER) RETURN NUMBER IS
255 BEGIN
256 RETURN ROUND((NVL(CF_OTHER_ACC_CURRENT
257 ,0) + NVL(CF_OTHER_ACC_CREDIT
258 ,0))
259 ,0);
260 END CF_OTHER_TOTALFORMULA;
261
262 FUNCTION CF_OPENING_BALANCEFORMULA RETURN NUMBER IS
263 LN_OPENING_BALANCE JAI_CMN_RG_23AC_II_TRXS.OPENING_BALANCE%TYPE;
264 BEGIN
265 SELECT
266 SUM(NVL(CR_BASIC_ED
267 ,0) + NVL(CR_ADDITIONAL_ED
268 ,0) + NVL(CR_OTHER_ED
269 ,0) - NVL(DR_BASIC_ED
270 ,0) - NVL(DR_ADDITIONAL_ED
271 ,0) - NVL(DR_OTHER_ED
272 ,0))
273 INTO LN_OPENING_BALANCE
274 FROM
275 JAI_CMN_RG_23AC_II_TRXS
276 WHERE LOCATION_ID = P_LOCATION_ID
277 AND ORGANIZATION_ID = P_ORGANIZATION_ID
278 AND CREATION_DATE < P_START_DATE;
279 RETURN ROUND(NVL(LN_OPENING_BALANCE
280 ,0)
281 ,0);
282 EXCEPTION
283 WHEN OTHERS THEN
284 /*SRW.MESSAGE(1275
285 ,'CF_opening_balance:' || SQLERRM)*/NULL;
286 RETURN NULL;
287 END CF_OPENING_BALANCEFORMULA;
288
289 FUNCTION CF_CLOSING_BALANCEFORMULA(CF_OPENING_BALANCE IN NUMBER
290 ,TOTAL_CREDIT_AVAILED IN NUMBER
291 ,CF_CR_UTILIZED IN NUMBER
292 ,CF_RTV_AMOUNT IN NUMBER) RETURN NUMBER IS
293 BEGIN
294 RETURN ROUND((NVL(CF_OPENING_BALANCE
295 ,0) + NVL(TOTAL_CREDIT_AVAILED
296 ,0) - NVL(CF_CR_UTILIZED
297 ,0) - NVL(CF_RTV_AMOUNT
298 ,0))
299 ,0);
300 END CF_CLOSING_BALANCEFORMULA;
301
302 FUNCTION CF_MONTHFORMULA RETURN VARCHAR2 IS
303 BEGIN
304 RETURN 'Return of excisable goods and availment of CENVAT credit for the month of ' || TO_CHAR(NVL(P_START_DATE
305 ,SYSDATE)
306 ,'MM YYYY');
307 END CF_MONTHFORMULA;
308
309 FUNCTION CF_CESS_EXCISE_INPUTFORMULA RETURN NUMBER IS
310 LN_EDU_CESS_EXCISE NUMBER;
311 CURSOR CUR_GET_EDU_CESS_EXCISE IS
312 SELECT
313 NVL(SUM(CREDIT)
314 ,0)
315 FROM
316 JAI_CMN_RG_OTHERS JRO,
317 JAI_CMN_RG_23AC_II_TRXS RG23
318 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
319 AND RG23.LOCATION_ID = P_LOCATION_ID
320 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
321 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
322 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
323 ,SYSDATE))
324 AND RG23.REGISTER_TYPE = 'A'
325 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
326 AND JRO.TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
327 BEGIN
328 OPEN CUR_GET_EDU_CESS_EXCISE;
329 FETCH CUR_GET_EDU_CESS_EXCISE
330 INTO LN_EDU_CESS_EXCISE;
331 CLOSE CUR_GET_EDU_CESS_EXCISE;
332 RETURN (ROUND(LN_EDU_CESS_EXCISE
333 ,0));
334 EXCEPTION
335 WHEN OTHERS THEN
336 /*SRW.MESSAGE(1275
337 ,'CF_cess_excise_input:' || SQLERRM)*/NULL;
338 RETURN NULL;
339 END CF_CESS_EXCISE_INPUTFORMULA;
340
341 FUNCTION CF_CESS_EXCISE_CAPFORMULA RETURN NUMBER IS
342 LN_EDU_CESS_CAP NUMBER;
343 CURSOR CUR_GET_EDU_CESS_CAP IS
344 SELECT
345 NVL(SUM(CREDIT)
346 ,0)
347 FROM
348 JAI_CMN_RG_OTHERS JRO,
349 JAI_CMN_RG_23AC_II_TRXS RG23
350 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
351 AND RG23.LOCATION_ID = P_LOCATION_ID
352 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
353 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
354 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
355 ,SYSDATE))
356 AND RG23.REGISTER_TYPE = 'C'
357 AND JRO.SOURCE_REGISTER = 'RG23C_P2'
358 AND JRO.TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
359 BEGIN
360 OPEN CUR_GET_EDU_CESS_CAP;
361 FETCH CUR_GET_EDU_CESS_CAP
362 INTO LN_EDU_CESS_CAP;
363 CLOSE CUR_GET_EDU_CESS_CAP;
364 RETURN (ROUND(LN_EDU_CESS_CAP
365 ,0));
366 EXCEPTION
367 WHEN OTHERS THEN
368 /*SRW.MESSAGE(1275
369 ,'CF_cess_excise_cap:' || SQLERRM)*/NULL;
370 RETURN NULL;
371 END CF_CESS_EXCISE_CAPFORMULA;
372
373 FUNCTION CF_TAX_SERVICESFORMULA RETURN NUMBER IS
374 CURSOR CUR_ST_CESS IS
375 SELECT
376 SUM(SERVICE_CREDIT),
377 SUM(EDU_CESS_CREDIT)
378 FROM
379 ( SELECT
380 JRTF1.RECOVERED_AMOUNT SERVICE_CREDIT,
381 JRTF2.RECOVERED_AMOUNT EDU_CESS_CREDIT
382 FROM
383 JAI_RGM_TRX_REFS JRTF1,
384 JAI_RGM_TRX_REFS JRTF2
385 WHERE JRTF1.SOURCE = 'AP'
386 AND JRTF1.INVOICE_ID = jrtf2.invoice_id (+)
387 AND JRTF1.TAX_TYPE = 'Service'
388 AND jrtf2.tax_type (+) = TAX_TYPE_SERVICE_EDU_CESS
389 AND ( NVL(TRUNC(JRTF1.CREATION_DATE)
390 ,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
391 ,TRUNC(JRTF1.CREATION_DATE)) )
392 AND ( NVL(P_END_DATE
393 ,TRUNC(SYSDATE)) )
394 AND JRTF1.ORGANIZATION_ID IN (
395 SELECT
396 DISTINCT
397 ORGANIZATION_ID
398 FROM
399 JAI_RGM_ORG_REGNS_V
400 WHERE REGIME_CODE = 'SERVICE'
401 AND REGISTRATION_TYPE = 'OTHERS'
402 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
403 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
404 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
405 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
406 ,ORGANIZATION_ID) )
407 UNION ALL
408 SELECT
409 JRTR1.CREDIT_AMOUNT SERVICE_CREDIT,
410 JRTR2.CREDIT_AMOUNT EDU_CESS_CREDIT
411 FROM
412 JAI_RGM_TRX_RECORDS JRTR1,
413 JAI_RGM_TRX_RECORDS JRTR2
414 WHERE JRTR1.SOURCE = 'SERVICE_DISTRIBUTE_IN'
415 AND JRTR1.REGIME_CODE = 'SERVICE'
416 AND JRTR1.TAX_TYPE = 'Service'
417 AND jrtr2.tax_type (+) = TAX_TYPE_SERVICE_EDU_CESS
418 AND JRTR1.ORGANIZATION_ID = jrtr2.organization_id (+)
419 AND JRTR1.SOURCE_DOCUMENT_ID = jrtr2.source_document_id (+)
420 AND JRTR1.REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
421 AND ( NVL(TRUNC(JRTR1.CREATION_DATE)
422 ,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
423 ,TRUNC(JRTR1.CREATION_DATE)) )
424 AND ( NVL(P_END_DATE
425 ,TRUNC(SYSDATE)) )
426 UNION ALL
427 SELECT
428 JRTR1.CREDIT_AMOUNT SERVICE_CREDIT,
429 JRTR2.CREDIT_AMOUNT EDU_CESS_CREDIT
430 FROM
431 JAI_RGM_TRX_RECORDS JRTR1,
432 JAI_RGM_TRX_RECORDS JRTR2
433 WHERE JRTR1.SOURCE = 'MANUAL'
434 AND JRTR1.REGIME_CODE = 'SERVICE'
435 AND JRTR1.TAX_TYPE = 'Service'
436 AND jrtr2.tax_type (+) = TAX_TYPE_SERVICE_EDU_CESS
437 AND JRTR1.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
438 AND JRTR1.SOURCE_TRX_TYPE = jrtr2.source_trx_type (+)
439 AND JRTR1.ORGANIZATION_ID = jrtr2.organization_id (+)
440 AND JRTR1.SOURCE_DOCUMENT_ID = jrtr2.source_document_id (+)
441 AND JRTR1.REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
442 AND ( NVL(TRUNC(JRTR1.CREATION_DATE)
443 ,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
444 ,TRUNC(JRTR1.CREATION_DATE)) )
445 AND ( NVL(P_END_DATE
446 ,TRUNC(SYSDATE)) ) );
447 CURSOR CUR_ST_SH_CESS IS
448 SELECT
449 SUM(SERVICE_CREDIT),
450 SUM(EDU_CESS_CREDIT)
451 FROM
452 ( SELECT
453 JRTF1.RECOVERED_AMOUNT SERVICE_CREDIT,
454 JRTF2.RECOVERED_AMOUNT EDU_CESS_CREDIT
455 FROM
456 JAI_RGM_TRX_REFS JRTF1,
457 JAI_RGM_TRX_REFS JRTF2
458 WHERE JRTF1.SOURCE = 'AP'
459 AND JRTF1.INVOICE_ID = jrtf2.invoice_id (+)
460 AND JRTF1.TAX_TYPE = 'Service'
461 AND jrtf2.tax_type (+) = TAX_TYPE_SH_SERVICE_EDU_CESS
462 AND ( NVL(TRUNC(JRTF1.CREATION_DATE)
463 ,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
464 ,TRUNC(JRTF1.CREATION_DATE)) )
465 AND ( NVL(P_END_DATE
466 ,TRUNC(SYSDATE)) )
467 AND JRTF1.ORGANIZATION_ID IN (
468 SELECT
469 DISTINCT
470 ORGANIZATION_ID
471 FROM
472 JAI_RGM_ORG_REGNS_V
473 WHERE REGIME_CODE = 'SERVICE'
474 AND REGISTRATION_TYPE = 'OTHERS'
475 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
476 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
477 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
478 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
479 ,ORGANIZATION_ID) )
480 UNION ALL
481 SELECT
482 JRTR1.CREDIT_AMOUNT SERVICE_CREDIT,
483 JRTR2.CREDIT_AMOUNT EDU_CESS_CREDIT
484 FROM
485 JAI_RGM_TRX_RECORDS JRTR1,
486 JAI_RGM_TRX_RECORDS JRTR2
487 WHERE JRTR1.SOURCE = 'SERVICE_DISTRIBUTE_IN'
488 AND JRTR1.REGIME_CODE = 'SERVICE'
489 AND JRTR1.TAX_TYPE = 'Service'
490 AND jrtr2.tax_type (+) = TAX_TYPE_SH_SERVICE_EDU_CESS
491 AND JRTR1.ORGANIZATION_ID = jrtr2.organization_id (+)
492 AND JRTR1.SOURCE_DOCUMENT_ID = jrtr2.source_document_id (+)
493 AND JRTR1.REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
494 AND ( NVL(TRUNC(JRTR1.CREATION_DATE)
495 ,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
496 ,TRUNC(JRTR1.CREATION_DATE)) )
497 AND ( NVL(P_END_DATE
498 ,TRUNC(SYSDATE)) )
499 UNION ALL
500 SELECT
501 JRTR1.CREDIT_AMOUNT SERVICE_CREDIT,
502 JRTR2.CREDIT_AMOUNT EDU_CESS_CREDIT
503 FROM
504 JAI_RGM_TRX_RECORDS JRTR1,
505 JAI_RGM_TRX_RECORDS JRTR2
506 WHERE JRTR1.SOURCE = 'MANUAL'
507 AND JRTR1.REGIME_CODE = 'SERVICE'
508 AND JRTR1.TAX_TYPE = 'Service'
509 AND jrtr2.tax_type (+) = TAX_TYPE_SH_SERVICE_EDU_CESS
510 AND JRTR1.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
511 AND JRTR1.SOURCE_TRX_TYPE = jrtr2.source_trx_type (+)
512 AND JRTR1.ORGANIZATION_ID = jrtr2.organization_id (+)
513 AND JRTR1.SOURCE_DOCUMENT_ID = jrtr2.source_document_id (+)
514 AND JRTR1.REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
515 AND ( NVL(TRUNC(JRTR1.CREATION_DATE)
516 ,TRUNC(SYSDATE)) ) BETWEEN ( NVL(P_START_DATE
517 ,TRUNC(JRTR1.CREATION_DATE)) )
518 AND ( NVL(P_END_DATE
519 ,TRUNC(SYSDATE)) ) );
520 LV_ST_CREDIT_AVLD NUMBER;
521 LV_CESS_CREDIT_AVLD NUMBER;
522 LV_ST_SH_CREDIT_AVLD NUMBER;
523 LV_SH_CESS_CREDIT_AVLD NUMBER;
524 BEGIN
525 OPEN CUR_ST_CESS;
526 FETCH CUR_ST_CESS
527 INTO LV_ST_CREDIT_AVLD,LV_CESS_CREDIT_AVLD;
528 CLOSE CUR_ST_CESS;
529 OPEN CUR_ST_SH_CESS;
530 FETCH CUR_ST_SH_CESS
531 INTO LV_ST_SH_CREDIT_AVLD,LV_SH_CESS_CREDIT_AVLD;
532 CLOSE CUR_ST_SH_CESS;
533 CP_CESS_CREDIT_AVLD := LV_CESS_CREDIT_AVLD;
534 CP_SH_CESS_CREDIT_AVLD := LV_SH_CESS_CREDIT_AVLD;
535 RETURN ROUND(LV_ST_CREDIT_AVLD
536 ,0);
537 END CF_TAX_SERVICESFORMULA;
538
539 FUNCTION CF_TAX_DUTY_GOODSFORMULA RETURN NUMBER IS
540 CURSOR CUR_AR_UTIL_CREDIT IS
541 SELECT
542 SUM(RECOVERED_AMOUNT)
543 FROM
544 JAI_RGM_TRX_REFS
545 WHERE SOURCE = 'AR'
546 AND TAX_TYPE = 'Service'
547 AND ORGANIZATION_ID IN (
548 SELECT
549 DISTINCT
550 ORGANIZATION_ID
551 FROM
552 JAI_RGM_ORG_REGNS_V
553 WHERE REGIME_CODE = 'SERVICE'
554 AND REGISTRATION_TYPE = 'OTHERS'
555 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
556 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
557 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
558 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
559 ,ORGANIZATION_ID) )
560 AND ( NVL(TRUNC(CREATION_DATE)
561 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
562 ,SYSDATE) )
563 AND ( NVL(P_END_DATE
564 ,SYSDATE) );
565 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
566 SELECT
567 NVL(SUM(DEBIT_AMOUNT)
568 ,0)
569 FROM
570 JAI_RGM_TRX_RECORDS
571 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
572 AND REGIME_CODE = 'SERVICE'
573 AND TAX_TYPE = 'Service'
574 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
575 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
576 ,ORGANIZATION_ID)
577 AND ( NVL(TRUNC(CREATION_DATE)
578 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
579 ,SYSDATE) )
580 AND ( NVL(P_END_DATE
581 ,SYSDATE) );
582 CURSOR CUR_MANUAL_DEBIT IS
583 SELECT
584 NVL(SUM(DEBIT_AMOUNT)
585 ,0)
586 FROM
587 JAI_RGM_TRX_RECORDS
588 WHERE SOURCE = 'MANUAL'
589 AND REGIME_CODE = 'SERVICE'
590 AND TAX_TYPE = 'Service'
591 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
592 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
593 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
594 ,ORGANIZATION_ID)
595 AND ( NVL(TRUNC(CREATION_DATE)
596 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
597 ,SYSDATE) )
598 AND ( NVL(P_END_DATE
599 ,SYSDATE) );
600 CURSOR CUR_PAYMENT IS
601 SELECT
602 NVL(SUM(DEBIT_AMOUNT)
603 ,0)
604 FROM
605 JAI_RGM_TRX_RECORDS
606 WHERE SOURCE = 'MANUAL'
607 AND REGIME_CODE = 'SERVICE'
608 AND TAX_TYPE = 'Service'
609 AND SOURCE_TRX_TYPE = 'PAYMENT'
610 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
611 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
612 ,ORGANIZATION_ID)
613 AND ( NVL(TRUNC(CREATION_DATE)
614 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
615 ,SYSDATE) )
616 AND ( NVL(P_END_DATE
617 ,SYSDATE) );
618 LN_AR_UTIL_CREDIT NUMBER := 0;
619 LN_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
620 LV_MANUAL_DEBIT NUMBER := 0;
621 LV_PAYMENT NUMBER := 0;
622 BEGIN
623 OPEN CUR_AR_UTIL_CREDIT;
624 FETCH CUR_AR_UTIL_CREDIT
625 INTO LN_AR_UTIL_CREDIT;
626 CLOSE CUR_AR_UTIL_CREDIT;
627 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
628 FETCH CUR_AR_SER_DIST_OUT_DEBIT
629 INTO LN_AR_SER_DIST_OUT_DEBIT;
630 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
631 OPEN CUR_MANUAL_DEBIT;
632 FETCH CUR_MANUAL_DEBIT
633 INTO LV_MANUAL_DEBIT;
634 CLOSE CUR_MANUAL_DEBIT;
635 OPEN CUR_PAYMENT;
636 FETCH CUR_PAYMENT
637 INTO LV_PAYMENT;
638 CLOSE CUR_PAYMENT;
639 RETURN ROUND((NVL(LN_AR_UTIL_CREDIT
640 ,0) + NVL(LN_AR_SER_DIST_OUT_DEBIT
641 ,0) + NVL(LV_MANUAL_DEBIT
642 ,0) - NVL(LV_PAYMENT
643 ,0))
644 ,0);
645 END CF_TAX_DUTY_GOODSFORMULA;
646
647 FUNCTION CF_CESS_TAX_DUTY_GOODSFORMULA RETURN NUMBER IS
648 CURSOR CUR_AR_UTIL_CREDIT IS
649 SELECT
650 SUM(RECOVERED_AMOUNT)
651 FROM
652 JAI_RGM_TRX_REFS
653 WHERE SOURCE = 'AR'
654 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
655 AND ORGANIZATION_ID IN (
656 SELECT
657 DISTINCT
658 ORGANIZATION_ID
659 FROM
660 JAI_RGM_ORG_REGNS_V
661 WHERE REGIME_CODE = 'SERVICE'
662 AND REGISTRATION_TYPE = 'OTHERS'
663 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
664 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
665 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
666 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
667 ,ORGANIZATION_ID) )
668 AND ( NVL(TRUNC(CREATION_DATE)
669 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
670 ,SYSDATE) )
671 AND ( NVL(P_END_DATE
672 ,SYSDATE) );
673 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
674 SELECT
675 NVL(SUM(DEBIT_AMOUNT)
676 ,0)
677 FROM
678 JAI_RGM_TRX_RECORDS
679 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
680 AND REGIME_CODE = 'SERVICE'
681 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
682 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
683 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
684 ,ORGANIZATION_ID)
685 AND ( NVL(TRUNC(CREATION_DATE)
686 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
687 ,SYSDATE) )
688 AND ( NVL(P_END_DATE
689 ,SYSDATE) );
690 CURSOR CUR_MANUAL_DEBIT IS
691 SELECT
692 NVL(SUM(DEBIT_AMOUNT)
693 ,0)
694 FROM
695 JAI_RGM_TRX_RECORDS
696 WHERE SOURCE = 'MANUAL'
697 AND REGIME_CODE = 'SERVICE'
698 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
699 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
700 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
701 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
702 ,ORGANIZATION_ID)
703 AND ( NVL(TRUNC(CREATION_DATE)
704 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
705 ,SYSDATE) )
706 AND ( NVL(P_END_DATE
707 ,SYSDATE) );
708 CURSOR CUR_PAYMENT IS
709 SELECT
710 NVL(SUM(DEBIT_AMOUNT)
711 ,0)
712 FROM
713 JAI_RGM_TRX_RECORDS
714 WHERE SOURCE = 'MANUAL'
715 AND REGIME_CODE = 'SERVICE'
716 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
717 AND SOURCE_TRX_TYPE = 'PAYMENT'
718 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
719 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
720 ,ORGANIZATION_ID)
721 AND ( NVL(TRUNC(CREATION_DATE)
722 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
723 ,SYSDATE) )
724 AND ( NVL(P_END_DATE
725 ,SYSDATE) );
726 LN_AR_UTIL_CREDIT NUMBER := 0;
727 LN_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
728 LV_MANUAL_DEBIT NUMBER := 0;
729 LV_PAYMENT NUMBER := 0;
730 BEGIN
731 OPEN CUR_AR_UTIL_CREDIT;
732 FETCH CUR_AR_UTIL_CREDIT
733 INTO LN_AR_UTIL_CREDIT;
734 CLOSE CUR_AR_UTIL_CREDIT;
735 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
736 FETCH CUR_AR_SER_DIST_OUT_DEBIT
737 INTO LN_AR_SER_DIST_OUT_DEBIT;
738 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
739 OPEN CUR_MANUAL_DEBIT;
740 FETCH CUR_MANUAL_DEBIT
741 INTO LV_MANUAL_DEBIT;
742 CLOSE CUR_MANUAL_DEBIT;
743 OPEN CUR_PAYMENT;
744 FETCH CUR_PAYMENT
745 INTO LV_PAYMENT;
746 CLOSE CUR_PAYMENT;
747 RETURN ROUND((NVL(LN_AR_UTIL_CREDIT
748 ,0) + NVL(LN_AR_SER_DIST_OUT_DEBIT
749 ,0) + NVL(LV_MANUAL_DEBIT
750 ,0) - NVL(LV_PAYMENT
751 ,0))
752 ,0);
753 END CF_CESS_TAX_DUTY_GOODSFORMULA;
754
755 FUNCTION CF_TAX_DUTY_SERVICESFORMULA RETURN NUMBER IS
756 LN_TAX_DUTY_SRVCS NUMBER;
757 BEGIN
758 SELECT
759 NVL(SUM(CR_BASIC_ED)
760 ,0)
761 INTO LN_TAX_DUTY_SRVCS
762 FROM
763 JAI_CMN_RG_PLA_TRXS
764 WHERE TRANSACTION_SOURCE_NUM = 151
765 AND ORGANIZATION_ID = P_ORGANIZATION_ID
766 AND LOCATION_ID = P_LOCATION_ID
767 AND CREATION_DATE >= P_START_DATE
768 AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
769 ,SYSDATE));
770 RETURN ROUND(LN_TAX_DUTY_SRVCS
771 ,0);
772 EXCEPTION
773 WHEN OTHERS THEN
774 /*SRW.MESSAGE(1275
775 ,'CF_tax_duty_services:' || SQLERRM)*/NULL;
776 RETURN NULL;
777 END CF_TAX_DUTY_SERVICESFORMULA;
778
779 FUNCTION CF_CESS_TAX_DUTY_SERVICESFORMU RETURN NUMBER IS
780 LN_CESS_TAX_DUTY_SRVCS NUMBER;
781 BEGIN
782 SELECT
783 NVL(SUM(OTHER_TAX_CREDIT)
784 ,0)
785 INTO LN_CESS_TAX_DUTY_SRVCS
786 FROM
787 JAI_CMN_RG_PLA_TRXS
788 WHERE TRANSACTION_SOURCE_NUM = 151
789 AND ORGANIZATION_ID = P_ORGANIZATION_ID
790 AND LOCATION_ID = P_LOCATION_ID
791 AND TRUNC(CREATION_DATE) >= P_START_DATE
792 AND TRUNC(CREATION_DATE) <= TRUNC(NVL(P_END_DATE
793 ,SYSDATE));
794 RETURN LN_CESS_TAX_DUTY_SRVCS;
795 EXCEPTION
796 WHEN OTHERS THEN
797 /*SRW.MESSAGE(1275
798 ,'CF_cess_tax_duty_services:' || SQLERRM)*/NULL;
799 RETURN NULL;
800 END CF_CESS_TAX_DUTY_SERVICESFORMU;
801
802 FUNCTION CF_CESS_OPENING_BLNCFORMULA RETURN NUMBER IS
803 LV_REGISTER_ID JAI_CMN_RG_23AC_II_TRXS.REGISTER_ID%TYPE;
804 CURSOR CUR_OPENING_BAL IS
805 SELECT
806 SUM(NVL(CREDIT
807 ,0) - NVL(DEBIT
808 ,0))
809 FROM
810 JAI_CMN_RG_OTHERS
811 WHERE SOURCE_TYPE = 1
812 AND SOURCE_REGISTER_ID in (
813 SELECT
814 REGISTER_ID
815 FROM
816 JAI_CMN_RG_23AC_II_TRXS
817 WHERE LOCATION_ID = P_LOCATION_ID
818 AND ORGANIZATION_ID = P_ORGANIZATION_ID
819 AND TRUNC(CREATION_DATE) < P_START_DATE )
820 AND TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
821 LN_OPENING_BALANCE NUMBER;
822 BEGIN
823 OPEN CUR_OPENING_BAL;
824 FETCH CUR_OPENING_BAL
825 INTO LN_OPENING_BALANCE;
826 CLOSE CUR_OPENING_BAL;
827 RETURN ROUND(LN_OPENING_BALANCE
828 ,0);
829 END CF_CESS_OPENING_BLNCFORMULA;
830
831 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
832 CURSOR CUR_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
833 SELECT
834 CONCURRENT_PROGRAM_ID,
835 NVL(ENABLE_TRACE
836 ,'N')
837 FROM
838 FND_CONCURRENT_REQUESTS
839 WHERE REQUEST_ID = P_REQUEST_ID;
840 CURSOR CUR_GET_AUDSID IS
841 SELECT
842 A.SID,
843 A.SERIAL#,
844 B.SPID
845 FROM
846 V$SESSION A,
847 V$PROCESS B
848 WHERE AUDSID = USERENV('SESSIONID')
849 AND A.PADDR = B.ADDR;
850 CURSOR CUR_GET_DBNAME IS
851 SELECT
852 NAME
853 FROM
854 V$DATABASE;
855 AUDSID NUMBER := USERENV('SESSIONID');
856 SID NUMBER;
857 SERIAL NUMBER;
858 SPID VARCHAR2(9);
859 NAME1 VARCHAR2(25);
860 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
861 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
862 BEGIN
863 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
864 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
865 /*SRW.MESSAGE(1275
866 ,'Report Version is 120.7 last modified date is 28/05/2007')*/NULL;
867 /*SRW.MESSAGE(1275
868 ,'Sysdate:' || SYSDATE)*/NULL;
869 BEGIN
870 OPEN CUR_PROGRAM_ID(P_CONC_REQUEST_ID);
871 FETCH CUR_PROGRAM_ID
872 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
873 CLOSE CUR_PROGRAM_ID;
874 /*SRW.MESSAGE(1275
875 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
876 IF V_ENABLE_TRACE = 'Y' THEN
877 OPEN CUR_GET_AUDSID;
878 FETCH CUR_GET_AUDSID
879 INTO SID,SERIAL,SPID;
880 CLOSE CUR_GET_AUDSID;
881 OPEN CUR_GET_DBNAME;
882 FETCH CUR_GET_DBNAME
883 INTO NAME1;
884 CLOSE CUR_GET_DBNAME;
885 /*SRW.MESSAGE(1275
886 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
887 EXECUTE IMMEDIATE
888 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
889 END IF;
890 EXCEPTION
891 WHEN OTHERS THEN
892 /*SRW.MESSAGE(1275
893 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg ->' || SQLERRM)*/NULL;
894 END;
895 RETURN (TRUE);
896 END BEFOREREPORT;
897
898 FUNCTION CF_SRVC_OPEN_BALNCFORMULA RETURN NUMBER IS
899 CURSOR CUR_INVOICE_OPEN_BAL IS
900 SELECT
901 SUM(RECOVERED_AMOUNT)
902 FROM
903 JAI_RGM_TRX_REFS
904 WHERE SOURCE = 'AP'
905 AND TAX_TYPE = 'Service'
906 AND TRUNC(CREATION_DATE) < P_START_DATE
907 AND ORGANIZATION_ID in (
908 SELECT
909 DISTINCT
910 ORGANIZATION_ID
911 FROM
912 JAI_RGM_ORG_REGNS_V
913 WHERE REGIME_CODE = 'SERVICE'
914 AND REGISTRATION_TYPE = 'OTHERS'
915 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
916 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
917 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
918 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
919 ,ORGANIZATION_ID) );
920 CURSOR CUR_DIST_IN IS
921 SELECT
922 SUM(CREDIT_AMOUNT)
923 FROM
924 JAI_RGM_TRX_RECORDS
925 WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
926 AND REGIME_CODE = 'SERVICE'
927 AND TAX_TYPE = 'Service'
928 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
929 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
930 ,ORGANIZATION_ID)
931 AND ( NVL(TRUNC(CREATION_DATE)
932 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
933 ,TRUNC(SYSDATE)) );
934 CURSOR CUR_MANUAL_IN IS
935 SELECT
936 SUM(CREDIT_AMOUNT)
937 FROM
938 JAI_RGM_TRX_RECORDS
939 WHERE SOURCE = 'MANUAL'
940 AND REGIME_CODE = 'SERVICE'
941 AND TAX_TYPE = 'Service'
942 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
943 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
944 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
945 ,ORGANIZATION_ID)
946 AND ( NVL(TRUNC(CREATION_DATE)
947 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
948 ,TRUNC(SYSDATE)) );
949 CURSOR CUR_AR_UTIL_CREDIT IS
950 SELECT
951 SUM(RECOVERED_AMOUNT)
952 FROM
953 JAI_RGM_TRX_REFS
954 WHERE SOURCE = 'AR'
955 AND TAX_TYPE = 'Service'
956 AND TRUNC(CREATION_DATE) < P_START_DATE
957 AND ORGANIZATION_ID IN (
958 SELECT
959 DISTINCT
960 ORGANIZATION_ID
961 FROM
962 JAI_RGM_ORG_REGNS_V
963 WHERE REGIME_CODE = 'SERVICE'
964 AND REGISTRATION_TYPE = 'OTHERS'
965 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
966 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
967 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
968 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
969 ,ORGANIZATION_ID) );
970 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
971 SELECT
972 NVL(SUM(DEBIT_AMOUNT)
973 ,0)
974 FROM
975 JAI_RGM_TRX_RECORDS
976 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
977 AND REGIME_CODE = 'SERVICE'
978 AND TAX_TYPE = 'Service'
979 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
980 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
981 ,ORGANIZATION_ID)
982 AND ( NVL(TRUNC(CREATION_DATE)
983 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
984 ,TRUNC(SYSDATE)) );
985 CURSOR CUR_MANUAL_DEBIT IS
986 SELECT
987 NVL(SUM(DEBIT_AMOUNT)
988 ,0)
989 FROM
990 JAI_RGM_TRX_RECORDS
991 WHERE SOURCE = 'MANUAL'
992 AND REGIME_CODE = 'SERVICE'
993 AND TAX_TYPE = 'Service'
994 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
995 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
996 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
997 ,ORGANIZATION_ID)
998 AND ( NVL(TRUNC(CREATION_DATE)
999 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1000 ,TRUNC(SYSDATE)) );
1001 CURSOR CUR_PAYMENT IS
1002 SELECT
1003 NVL(SUM(DEBIT_AMOUNT)
1004 ,0)
1005 FROM
1006 JAI_RGM_TRX_RECORDS
1007 WHERE SOURCE = 'MANUAL'
1008 AND REGIME_CODE = 'SERVICE'
1009 AND TAX_TYPE = ( 'Service' )
1010 AND SOURCE_TRX_TYPE = 'PAYMENT'
1011 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
1012 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1013 ,ORGANIZATION_ID)
1014 AND ( NVL(TRUNC(CREATION_DATE)
1015 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1016 ,TRUNC(SYSDATE)) );
1017 LV_INV_OPEN_BAL NUMBER := 0;
1018 LV_OPEN_DIST_BAL NUMBER := 0;
1019 LV_AR_UTIL_CREDIT NUMBER := 0;
1020 LV_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
1021 LV_MANUAL_BAL NUMBER := 0;
1022 LV_MANUAL_DEBIT_BAL NUMBER := 0;
1023 LV_MANUAL_PAYMENT NUMBER := 0;
1024 BEGIN
1025 OPEN CUR_INVOICE_OPEN_BAL;
1026 FETCH CUR_INVOICE_OPEN_BAL
1027 INTO LV_INV_OPEN_BAL;
1028 CLOSE CUR_INVOICE_OPEN_BAL;
1029 OPEN CUR_DIST_IN;
1030 FETCH CUR_DIST_IN
1031 INTO LV_OPEN_DIST_BAL;
1032 CLOSE CUR_DIST_IN;
1033 OPEN CUR_MANUAL_IN;
1034 FETCH CUR_MANUAL_IN
1035 INTO LV_MANUAL_BAL;
1036 CLOSE CUR_MANUAL_IN;
1037 OPEN CUR_MANUAL_DEBIT;
1038 FETCH CUR_MANUAL_DEBIT
1039 INTO LV_MANUAL_DEBIT_BAL;
1040 CLOSE CUR_MANUAL_DEBIT;
1041 OPEN CUR_AR_UTIL_CREDIT;
1042 FETCH CUR_AR_UTIL_CREDIT
1043 INTO LV_AR_UTIL_CREDIT;
1044 CLOSE CUR_AR_UTIL_CREDIT;
1045 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
1046 FETCH CUR_AR_SER_DIST_OUT_DEBIT
1047 INTO LV_AR_SER_DIST_OUT_DEBIT;
1048 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
1049 OPEN CUR_PAYMENT;
1050 FETCH CUR_PAYMENT
1051 INTO LV_MANUAL_PAYMENT;
1052 CLOSE CUR_PAYMENT;
1053 RETURN ROUND((NVL(LV_OPEN_DIST_BAL
1054 ,0) + NVL(LV_INV_OPEN_BAL
1055 ,0) + NVL(LV_MANUAL_BAL
1056 ,0) - NVL(LV_AR_UTIL_CREDIT
1057 ,0) - NVL(LV_AR_SER_DIST_OUT_DEBIT
1058 ,0) - NVL(LV_MANUAL_DEBIT_BAL
1059 ,0) + NVL(LV_MANUAL_PAYMENT
1060 ,0))
1061 ,0);
1062 END CF_SRVC_OPEN_BALNCFORMULA;
1063
1064 FUNCTION CF_SRVC_CESS_OPNGFORMULA RETURN NUMBER IS
1065 CURSOR CUR_INVOICE_OPEN_BAL IS
1066 SELECT
1067 SUM(RECOVERED_AMOUNT)
1068 FROM
1069 JAI_RGM_TRX_REFS
1070 WHERE SOURCE = 'AP'
1071 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1072 AND TRUNC(CREATION_DATE) < P_START_DATE
1073 AND ORGANIZATION_ID in (
1074 SELECT
1075 DISTINCT
1076 ORGANIZATION_ID
1077 FROM
1078 JAI_RGM_ORG_REGNS_V
1079 WHERE REGIME_CODE = 'SERVICE'
1080 AND REGISTRATION_TYPE = 'OTHERS'
1081 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
1082 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
1083 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
1084 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1085 ,ORGANIZATION_ID) );
1086 CURSOR CUR_DIST_IN IS
1087 SELECT
1088 SUM(CREDIT_AMOUNT)
1089 FROM
1090 JAI_RGM_TRX_RECORDS
1091 WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
1092 AND REGIME_CODE = 'SERVICE'
1093 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1094 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
1095 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1096 ,ORGANIZATION_ID)
1097 AND ( NVL(TRUNC(CREATION_DATE)
1098 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1099 ,TRUNC(SYSDATE)) );
1100 CURSOR CUR_MANUAL_IN IS
1101 SELECT
1102 SUM(CREDIT_AMOUNT)
1103 FROM
1104 JAI_RGM_TRX_RECORDS
1105 WHERE SOURCE = 'MANUAL'
1106 AND REGIME_CODE = 'SERVICE'
1107 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1108 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
1109 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
1110 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1111 ,ORGANIZATION_ID)
1112 AND ( NVL(TRUNC(CREATION_DATE)
1113 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1114 ,TRUNC(SYSDATE)) );
1115 CURSOR CUR_AR_UTIL_CREDIT IS
1116 SELECT
1117 SUM(RECOVERED_AMOUNT)
1118 FROM
1119 JAI_RGM_TRX_REFS
1120 WHERE SOURCE = 'AR'
1121 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1122 AND TRUNC(CREATION_DATE) < P_START_DATE
1123 AND ORGANIZATION_ID IN (
1124 SELECT
1125 DISTINCT
1126 ORGANIZATION_ID
1127 FROM
1128 JAI_RGM_ORG_REGNS_V
1129 WHERE REGIME_CODE = 'SERVICE'
1130 AND REGISTRATION_TYPE = 'OTHERS'
1131 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
1132 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
1133 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
1134 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1135 ,ORGANIZATION_ID) );
1136 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
1137 SELECT
1138 NVL(SUM(DEBIT_AMOUNT)
1139 ,0)
1140 FROM
1141 JAI_RGM_TRX_RECORDS
1142 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
1143 AND REGIME_CODE = 'SERVICE'
1144 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1145 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
1146 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1147 ,ORGANIZATION_ID)
1148 AND ( NVL(TRUNC(CREATION_DATE)
1149 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1150 ,TRUNC(SYSDATE)) );
1151 CURSOR CUR_MANUAL_DEBIT IS
1152 SELECT
1153 NVL(SUM(DEBIT_AMOUNT)
1154 ,0)
1155 FROM
1156 JAI_RGM_TRX_RECORDS
1157 WHERE SOURCE = 'MANUAL'
1158 AND REGIME_CODE = 'SERVICE'
1159 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1160 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
1161 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
1162 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1163 ,ORGANIZATION_ID)
1164 AND ( NVL(TRUNC(CREATION_DATE)
1165 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1166 ,TRUNC(SYSDATE)) );
1167 CURSOR CUR_PAYMENT IS
1168 SELECT
1169 NVL(SUM(DEBIT_AMOUNT)
1170 ,0)
1171 FROM
1172 JAI_RGM_TRX_RECORDS
1173 WHERE SOURCE = 'MANUAL'
1174 AND REGIME_CODE = 'SERVICE'
1175 AND TAX_TYPE = TAX_TYPE_SERVICE_EDU_CESS
1176 AND SOURCE_TRX_TYPE = 'PAYMENT'
1177 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
1178 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
1179 ,ORGANIZATION_ID)
1180 AND ( NVL(TRUNC(CREATION_DATE)
1181 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
1182 ,TRUNC(SYSDATE)) );
1183 LV_INV_OPEN_BAL NUMBER := 0;
1184 LV_OPEN_DIST_BAL NUMBER := 0;
1185 LV_AR_UTIL_CREDIT NUMBER := 0;
1186 LV_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
1187 LV_MANUAL_BAL NUMBER := 0;
1188 LV_MANUAL_DEBIT_BAL NUMBER := 0;
1189 LV_MANUAL_PAYMENT NUMBER := 0;
1190 BEGIN
1191 OPEN CUR_INVOICE_OPEN_BAL;
1192 FETCH CUR_INVOICE_OPEN_BAL
1193 INTO LV_INV_OPEN_BAL;
1194 CLOSE CUR_INVOICE_OPEN_BAL;
1195 OPEN CUR_DIST_IN;
1196 FETCH CUR_DIST_IN
1197 INTO LV_OPEN_DIST_BAL;
1198 CLOSE CUR_DIST_IN;
1199 OPEN CUR_MANUAL_IN;
1200 FETCH CUR_MANUAL_IN
1201 INTO LV_MANUAL_BAL;
1202 CLOSE CUR_MANUAL_IN;
1203 OPEN CUR_MANUAL_DEBIT;
1204 FETCH CUR_MANUAL_DEBIT
1205 INTO LV_MANUAL_DEBIT_BAL;
1206 CLOSE CUR_MANUAL_DEBIT;
1207 OPEN CUR_AR_UTIL_CREDIT;
1208 FETCH CUR_AR_UTIL_CREDIT
1209 INTO LV_AR_UTIL_CREDIT;
1210 CLOSE CUR_AR_UTIL_CREDIT;
1211 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
1212 FETCH CUR_AR_SER_DIST_OUT_DEBIT
1213 INTO LV_AR_SER_DIST_OUT_DEBIT;
1214 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
1215 OPEN CUR_PAYMENT;
1216 FETCH CUR_PAYMENT
1217 INTO LV_MANUAL_PAYMENT;
1218 CLOSE CUR_PAYMENT;
1219 RETURN ROUND((NVL(LV_OPEN_DIST_BAL
1220 ,0) + NVL(LV_INV_OPEN_BAL
1221 ,0) + NVL(LV_MANUAL_BAL
1222 ,0) - NVL(LV_AR_UTIL_CREDIT
1223 ,0) - NVL(LV_AR_SER_DIST_OUT_DEBIT
1224 ,0) - NVL(LV_MANUAL_DEBIT_BAL
1225 ,0) + NVL(LV_MANUAL_PAYMENT
1226 ,0))
1227 ,0);
1228 END CF_SRVC_CESS_OPNGFORMULA;
1229
1230 FUNCTION CF_CLOSING_BLNC_STFORMULA(CF_SRVC_OPEN_BALNC IN NUMBER
1231 ,CF_TOT_CR_AVAILED_ST IN NUMBER
1232 ,CF_TAX_DUTY_GOODS IN NUMBER) RETURN NUMBER IS
1233 BEGIN
1234 RETURN ROUND((NVL(CF_SRVC_OPEN_BALNC
1235 ,0) + NVL(CF_TOT_CR_AVAILED_ST
1236 ,0) - NVL(CF_TAX_DUTY_GOODS
1237 ,0))
1238 ,0);
1239 END CF_CLOSING_BLNC_STFORMULA;
1240
1241 FUNCTION CF_TOT_CR_AVAILED_ECFORMULA(CF_CESS_EXCISE_INPUT IN NUMBER
1242 ,CF_CESS_EXCISE_CAP IN NUMBER) RETURN NUMBER IS
1243 BEGIN
1244 RETURN ROUND((NVL(CF_CESS_EXCISE_INPUT
1245 ,0) + NVL(CF_CESS_EXCISE_CAP
1246 ,0))
1247 ,0);
1248 END CF_TOT_CR_AVAILED_ECFORMULA;
1249
1250 FUNCTION CF_TOT_CR_AVAILED_STFORMULA(CF_SRVC_OPEN_BALNC IN NUMBER
1251 ,CF_TAX_SERVICES IN NUMBER) RETURN NUMBER IS
1252 BEGIN
1253 RETURN ROUND((NVL(CF_SRVC_OPEN_BALNC
1254 ,0) + NVL(CF_TAX_SERVICES
1255 ,0))
1256 ,0);
1257 END CF_TOT_CR_AVAILED_STFORMULA;
1258
1259 FUNCTION CF_TOT_CR_AVAILED_SCFORMULA(CF_SRVC_CESS_OPNG IN NUMBER) RETURN NUMBER IS
1260 BEGIN
1261 RETURN ROUND((NVL(CF_SRVC_CESS_OPNG
1262 ,0) + NVL(CP_CESS_CREDIT_AVLD
1263 ,0))
1264 ,0);
1265 END CF_TOT_CR_AVAILED_SCFORMULA;
1266
1267 FUNCTION CF_CLOSING_BLNC_ECFORMULA(CF_CESS_OPENING_BLNC IN NUMBER
1268 ,CF_TOT_CR_AVAILED_EC IN NUMBER
1269 ,CF_CESS_UTIL_PAY_GOODS IN NUMBER
1270 ,CF_CR_UTIL_INP_RM_CESS IN NUMBER) RETURN NUMBER IS
1271 BEGIN
1272 RETURN ROUND(NVL(CF_CESS_OPENING_BLNC
1273 ,0) + NVL(CF_TOT_CR_AVAILED_EC
1274 ,0) - NVL(CF_CESS_UTIL_PAY_GOODS
1275 ,0) - NVL(CF_CR_UTIL_INP_RM_CESS
1276 ,0)
1277 ,0);
1278 END CF_CLOSING_BLNC_ECFORMULA;
1279
1280 FUNCTION CF_CLOSING_BLNC_SCFORMULA(CF_SRVC_CESS_OPNG IN NUMBER
1281 ,CF_TOT_CR_AVAILED_SC IN NUMBER
1282 ,CF_CESS_TAX_DUTY_GOODS IN NUMBER) RETURN NUMBER IS
1283 BEGIN
1284 RETURN ROUND((NVL(CF_SRVC_CESS_OPNG
1285 ,0) + NVL(CF_TOT_CR_AVAILED_SC
1286 ,0) - NVL(CF_CESS_TAX_DUTY_GOODS
1287 ,0))
1288 ,0);
1289 END CF_CLOSING_BLNC_SCFORMULA;
1290
1291 FUNCTION CF_1FORMULA(REGISTER IN VARCHAR2
1292 ,INVENTORY_ITEM_ID IN NUMBER
1293 ,CETSH IN VARCHAR2
1294 ,UNITS IN VARCHAR2
1295 ,EXCISE_DUTY_RATE IN NUMBER) RETURN NUMBER IS
1296 LN_QTY_MFTRD NUMBER;
1297 BEGIN
1298 IF REGISTER = 'RG1' THEN
1299 SELECT
1300 SUM(NVL(MANUFACTURED_LOOSE_QTY
1301 ,0) + NVL(FOR_HOME_USE_PAY_ED_QTY
1302 ,0) + NVL(FOR_EXPORT_PAY_ED_QTY
1303 ,0) + NVL(FOR_EXPORT_N_PAY_ED_QTY
1304 ,0) + NVL(TO_OTHER_FACTORY_N_PAY_ED_QTY
1305 ,0) + NVL(OTHER_PURPOSE_N_PAY_ED_QTY
1306 ,0) + NVL(OTHER_PURPOSE_PAY_ED_QTY
1307 ,0)) QTY_MANUFACTURED
1308 INTO LN_QTY_MFTRD
1309 FROM
1310 JAI_CMN_RG_I_TRXS JRGI,
1311 JAI_INV_ITM_SETUPS ITEMS
1312 WHERE JRGI.TRANSACTION_TYPE in ( 'R' , 'PR' , 'RA' , 'IOR' , 'CR' )
1313 AND ( JRGI.INVENTORY_ITEM_ID = CF_1FORMULA.INVENTORY_ITEM_ID
1314 OR NVL(ITEMS.ITEM_TARIFF
1315 ,'xyz') = NVL(CETSH
1316 ,'xyz') )
1317 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1318 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1319 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1320 AND NVL(JRGI.PRIMARY_UOM_CODE
1321 ,'XYZ') = NVL(UNITS
1322 ,'XYZ')
1323 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
1324 ,0)
1325 ,-999.95) = NVL(CF_1FORMULA.EXCISE_DUTY_RATE
1326 ,-999.95)
1327 AND JRGI.LOCATION_ID = P_LOCATION_ID
1328 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1329 AND TRUNC(P_END_DATE);
1330 RETURN LN_QTY_MFTRD;
1331 ELSIF REGISTER = 'RG23_PART_I' THEN
1332 RETURN TO_NUMBER(NULL);
1333 END IF;
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 /*SRW.MESSAGE(1275
1337 ,'CF_Qty_Mftrd:' || SQLERRM)*/NULL;
1338 RETURN NULL;
1339 END CF_1FORMULA;
1340
1341 FUNCTION CF_QTY_CLEAREDFORMULA(REGISTER IN VARCHAR2
1342 ,INVENTORY_ITEM_ID IN NUMBER
1343 ,CETSH IN VARCHAR2
1344 ,EXCISE_DUTY_RATE IN NUMBER
1345 ,UNITS IN VARCHAR2) RETURN NUMBER IS
1346 LN_QTY_CLRD NUMBER;
1347 BEGIN
1348 IF REGISTER = 'RG1' THEN
1349 SELECT
1350 SUM(NVL(MANUFACTURED_LOOSE_QTY
1351 ,0) + NVL(FOR_HOME_USE_PAY_ED_QTY
1352 ,0) + NVL(FOR_EXPORT_PAY_ED_QTY
1353 ,0) + NVL(FOR_EXPORT_N_PAY_ED_QTY
1354 ,0) + NVL(TO_OTHER_FACTORY_N_PAY_ED_QTY
1355 ,0) + NVL(OTHER_PURPOSE_N_PAY_ED_QTY
1356 ,0) + NVL(OTHER_PURPOSE_PAY_ED_QTY
1357 ,0)) QTY_MANUFACTURED
1358 INTO LN_QTY_CLRD
1359 FROM
1360 JAI_CMN_RG_I_TRXS JRGI,
1361 JAI_INV_ITM_SETUPS ITEMS
1362 WHERE JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
1363 AND ( JRGI.INVENTORY_ITEM_ID = CF_QTY_CLEAREDFORMULA.INVENTORY_ITEM_ID
1364 OR NVL(ITEMS.ITEM_TARIFF
1365 ,'xyz') = NVL(CETSH
1366 ,'xyz') )
1367 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1368 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1369 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1370 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
1371 ,0)
1372 ,-999.95) = NVL(CF_QTY_CLEAREDFORMULA.EXCISE_DUTY_RATE
1373 ,-999.95)
1374 AND NVL(JRGI.PRIMARY_UOM_CODE
1375 ,'xyz') = NVL(UNITS
1376 ,'xyz')
1377 AND JRGI.LOCATION_ID = P_LOCATION_ID
1378 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1379 AND TRUNC(P_END_DATE);
1380 RETURN LN_QTY_CLRD;
1381 ELSIF REGISTER = 'RG23_PART_I' THEN
1382 RETURN TO_NUMBER(NULL);
1383 END IF;
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 /*SRW.MESSAGE(1275
1387 ,'CF_Qty_Cleared:' || SQLERRM)*/NULL;
1388 RETURN NULL;
1389 END CF_QTY_CLEAREDFORMULA;
1390
1391 FUNCTION CF_TR6_CHALLAN_AMNTFORMULA RETURN NUMBER IS
1392 LN_PLA_AMOUNT NUMBER;
1393 BEGIN
1394 SELECT
1395 NVL(SUM(PLA_AMOUNT)
1396 ,0)
1397 INTO LN_PLA_AMOUNT
1398 FROM
1399 JAI_CMN_RG_PLA_HDRS A
1400 WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
1401 AND A.LOCATION_ID = P_LOCATION_ID
1402 AND TRUNC(A.TR6_DATE) >= P_START_DATE
1403 AND TRUNC(A.TR6_DATE) <= P_END_DATE
1404 AND A.ACK_RECVD_FLAG = 'Y';
1405 RETURN LN_PLA_AMOUNT;
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 /*SRW.MESSAGE(1275
1409 ,'CF_TR6_Challan_amnt:' || SQLERRM)*/NULL;
1410 RETURN NULL;
1411 END CF_TR6_CHALLAN_AMNTFORMULA;
1412
1413 FUNCTION CF_RATEFORMULA(CF_QTY_CLEARED IN NUMBER
1414 ,EXCISE_DUTY_RATE IN NUMBER) RETURN NUMBER IS
1415 BEGIN
1416 IF CF_QTY_CLEARED IS NULL THEN
1417 RETURN NULL;
1418 ELSE
1419 RETURN EXCISE_DUTY_RATE;
1420 END IF;
1421 END CF_RATEFORMULA;
1422
1423 FUNCTION CF_DUTY_PAYABLEFORMULA(INVENTORY_ITEM_ID IN NUMBER
1424 ,CETSH IN VARCHAR2
1425 ,UNITS IN VARCHAR2
1426 ,EXCISE_DUTY_RATE IN NUMBER
1427 ,REGISTER IN VARCHAR2) RETURN NUMBER IS
1428 CURSOR C_DUTY_PAYABLE IS
1429 SELECT
1430 SUM(NVL(JRGI.BASIC_ED
1431 ,0) + NVL(JRGI.OTHER_ED
1432 ,0)) DUTY_PAYABLE,
1433 SUM(NVL(JRGI.ADDITIONAL_ED
1434 ,0)) AED_DUTY_PAYABLE
1435 FROM
1436 JAI_CMN_RG_I_TRXS JRGI,
1437 JAI_INV_ITM_SETUPS ITEMS
1438 WHERE JRGI.TRANSACTION_TYPE in ( 'I' , 'PI' , 'IA' , 'IOI' )
1439 AND ( JRGI.INVENTORY_ITEM_ID = cf_duty_payableformula.INVENTORY_ITEM_ID
1440 OR ITEMS.ITEM_TARIFF = CETSH )
1441 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1442 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1443 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1444 AND NVL(JRGI.PRIMARY_UOM_CODE
1445 ,'XYZ') = NVL(UNITS
1446 ,'XYZ')
1447 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
1448 ,0)
1449 ,-999.95) = NVL(cf_duty_payableformula.EXCISE_DUTY_RATE
1450 ,-999.95)
1451 AND JRGI.LOCATION_ID = P_LOCATION_ID
1452 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1453 AND TRUNC(P_END_DATE);
1454 LN_DUTY_PAYABLE NUMBER;
1455 LN_AED_DUTY_PAYABLE NUMBER;
1456 CURSOR C_DUTY_PAYABLE_PART_I IS
1457 SELECT
1458 SUM(NVL(JRGI.BASIC_ED
1459 ,0) + NVL(JRGI.OTHER_ED
1460 ,0)) DUTY_PAYABLE,
1461 SUM(NVL(JRGI.ADDITIONAL_ED
1462 ,0)) AED_DUTY_PAYABLE
1463 FROM
1464 JAI_CMN_RG_23AC_I_TRXS JRGI,
1465 JAI_INV_ITM_SETUPS ITEMS
1466 WHERE JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
1467 AND ( JRGI.INVENTORY_ITEM_ID = cf_duty_payableformula.INVENTORY_ITEM_ID
1468 OR ITEMS.ITEM_TARIFF = CETSH )
1469 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1470 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1471 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1472 AND NVL(JRGI.PRIMARY_UOM_CODE
1473 ,'XYZ') = NVL(UNITS
1474 ,'XYZ')
1475 AND JRGI.LOCATION_ID = P_LOCATION_ID
1476 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1477 AND TRUNC(P_END_DATE);
1478 LN_DUTY_PAYABLE_PART_I NUMBER;
1479 LN_AED_DUTY_PAYABLE_PART_I NUMBER;
1480 BEGIN
1481 IF REGISTER = 'RG1' THEN
1482 OPEN C_DUTY_PAYABLE;
1483 FETCH C_DUTY_PAYABLE
1484 INTO LN_DUTY_PAYABLE,LN_AED_DUTY_PAYABLE;
1485 CLOSE C_DUTY_PAYABLE;
1486 CP_AED_DUTY_PAYABLE := ROUND(NVL(LN_AED_DUTY_PAYABLE
1487 ,0)
1488 ,0);
1489 RETURN ROUND(LN_DUTY_PAYABLE
1490 ,0);
1491 ELSIF REGISTER = 'RG23_PART_I' THEN
1492 OPEN C_DUTY_PAYABLE_PART_I;
1493 FETCH C_DUTY_PAYABLE_PART_I
1494 INTO LN_DUTY_PAYABLE_PART_I,LN_AED_DUTY_PAYABLE_PART_I;
1495 CLOSE C_DUTY_PAYABLE_PART_I;
1496 CP_AED_DUTY_PAYABLE := ROUND(NVL(LN_AED_DUTY_PAYABLE_PART_I
1497 ,0)
1498 ,0);
1499 RETURN ROUND(LN_DUTY_PAYABLE_PART_I
1500 ,0);
1501 END IF;
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 /*SRW.MESSAGE(1275
1505 ,'CF_Duty_Payable' || SQLERRM)*/NULL;
1506 RETURN NULL;
1507 END CF_DUTY_PAYABLEFORMULA;
1508
1509 FUNCTION CF_OTHER_DUTIESFORMULA(INVENTORY_ITEM_ID IN NUMBER
1510 ,CETSH IN VARCHAR2
1511 ,UNITS IN VARCHAR2
1512 ,EXCISE_DUTY_RATE IN NUMBER
1513 ,REGISTER IN VARCHAR2) RETURN NUMBER IS
1514 LN_DUTY_PAYABLE NUMBER;
1515 LN_PLA_DUTY NUMBER;
1516 LN_RG23_DUTY NUMBER;
1517 CURSOR CUR_OTHER_DUTIES_PLA IS
1518 SELECT
1519 NVL(SUM(DEBIT)
1520 ,0)
1521 FROM
1522 JAI_CMN_RG_OTHERS
1523 WHERE SOURCE_REGISTER_ID IN (
1524 SELECT
1525 REGISTER_ID_PART_II
1526 FROM
1527 JAI_CMN_RG_I_TRXS JRGI,
1528 JAI_INV_ITM_SETUPS ITEMS
1529 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
1530 OR ITEMS.ITEM_TARIFF = CETSH )
1531 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1532 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1533 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1534 AND NVL(JRGI.PRIMARY_UOM_CODE
1535 ,'XYZ') = NVL(UNITS
1536 ,'XYZ')
1537 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
1538 ,0)
1539 ,-999.95) = NVL(cf_other_dutiesformula.EXCISE_DUTY_RATE
1540 ,-999.95)
1541 AND JRGI.LOCATION_ID = P_LOCATION_ID
1542 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1543 AND TRUNC(P_END_DATE)
1544 AND JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
1545 AND PAYMENT_REGISTER = 'PLA' )
1546 AND SOURCE_TYPE = 2
1547 AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS );
1548 CURSOR CUR_OTHER_DUTIES_RG23 IS
1549 SELECT
1550 NVL(SUM(DEBIT)
1551 ,0)
1552 FROM
1553 JAI_CMN_RG_OTHERS
1554 WHERE SOURCE_REGISTER_ID IN (
1555 SELECT
1556 REGISTER_ID_PART_II
1557 FROM
1558 JAI_CMN_RG_I_TRXS JRGI,
1559 JAI_INV_ITM_SETUPS ITEMS
1560 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
1561 OR ITEMS.ITEM_TARIFF = CETSH )
1562 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1563 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1564 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1565 AND NVL(JRGI.PRIMARY_UOM_CODE
1566 ,'XYZ') = NVL(UNITS
1567 ,'XYZ')
1568 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
1569 ,0)
1570 ,-999.95) = NVL(cf_other_dutiesformula.EXCISE_DUTY_RATE
1571 ,-999.95)
1572 AND JRGI.LOCATION_ID = P_LOCATION_ID
1573 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1574 AND TRUNC(P_END_DATE)
1575 AND JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
1576 AND PAYMENT_REGISTER IN ( 'RG23A' , 'RG23C' ) )
1577 AND SOURCE_TYPE = 1
1578 AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS );
1579 CURSOR CUR_OTHER_DUTIES_PLA_PART_I IS
1580 SELECT
1581 NVL(SUM(DEBIT)
1582 ,0)
1583 FROM
1584 JAI_CMN_RG_OTHERS
1585 WHERE SOURCE_REGISTER_ID IN (
1586 SELECT
1587 REGISTER_ID_PART_II
1588 FROM
1589 JAI_CMN_RG_23AC_I_TRXS JRGI,
1590 JAI_INV_ITM_SETUPS ITEMS
1591 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
1592 OR ITEMS.ITEM_TARIFF = CETSH )
1593 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1594 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1595 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1596 AND NVL(JRGI.PRIMARY_UOM_CODE
1597 ,'XYZ') = NVL(UNITS
1598 ,'XYZ')
1599 AND JRGI.LOCATION_ID = P_LOCATION_ID
1600 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1601 AND TRUNC(P_END_DATE)
1602 AND JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
1603 AND REGISTER_TYPE = 'PLA' )
1604 AND SOURCE_TYPE = 2
1605 AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS );
1606 CURSOR CUR_OTHER_DUTIES_RG23_PART_I IS
1607 SELECT
1608 NVL(SUM(DEBIT)
1609 ,0)
1610 FROM
1611 JAI_CMN_RG_OTHERS
1612 WHERE SOURCE_REGISTER_ID IN (
1613 SELECT
1614 REGISTER_ID_PART_II
1615 FROM
1616 JAI_CMN_RG_23AC_I_TRXS JRGI,
1617 JAI_INV_ITM_SETUPS ITEMS
1618 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_other_dutiesformula.INVENTORY_ITEM_ID
1619 OR ITEMS.ITEM_TARIFF = CETSH )
1620 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
1621 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
1622 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
1623 AND NVL(JRGI.PRIMARY_UOM_CODE
1624 ,'XYZ') = NVL(UNITS
1625 ,'XYZ')
1626 AND JRGI.LOCATION_ID = P_LOCATION_ID
1627 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
1628 AND TRUNC(P_END_DATE)
1629 AND JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
1630 AND REGISTER_TYPE IN ( 'A' , 'C' ) )
1631 AND SOURCE_TYPE = 1
1632 AND TAX_TYPE in ( TAX_TYPE_EXC_EDU_CESS , TAX_TYPE_CVD_EDU_CESS );
1633 LN_PLA_DUTY_PART_I NUMBER;
1634 LN_RG23_DUTY_PART_I NUMBER;
1635 BEGIN
1636 IF REGISTER = 'RG1' THEN
1637 /*SRW.MESSAGE(1275
1638 ,'1')*/NULL;
1639 OPEN CUR_OTHER_DUTIES_PLA;
1640 FETCH CUR_OTHER_DUTIES_PLA
1641 INTO LN_PLA_DUTY;
1642 CLOSE CUR_OTHER_DUTIES_PLA;
1643 OPEN CUR_OTHER_DUTIES_RG23;
1644 FETCH CUR_OTHER_DUTIES_RG23
1645 INTO LN_RG23_DUTY;
1646 CLOSE CUR_OTHER_DUTIES_RG23;
1647 LN_DUTY_PAYABLE := ROUND((NVL(LN_PLA_DUTY
1648 ,0) + NVL(LN_RG23_DUTY
1649 ,0))
1650 ,0);
1651 /*SRW.MESSAGE(1275
1652 ,'1a')*/NULL;
1653 RETURN LN_DUTY_PAYABLE;
1654 ELSIF REGISTER = 'RG23_PART_I' THEN
1655 /*SRW.MESSAGE(1275
1656 ,'2')*/NULL;
1657 OPEN CUR_OTHER_DUTIES_PLA_PART_I;
1658 FETCH CUR_OTHER_DUTIES_PLA_PART_I
1659 INTO LN_PLA_DUTY_PART_I;
1660 CLOSE CUR_OTHER_DUTIES_PLA_PART_I;
1661 OPEN CUR_OTHER_DUTIES_RG23_PART_I;
1662 FETCH CUR_OTHER_DUTIES_RG23_PART_I
1663 INTO LN_RG23_DUTY_PART_I;
1664 CLOSE CUR_OTHER_DUTIES_RG23_PART_I;
1665 LN_DUTY_PAYABLE := ROUND((NVL(LN_PLA_DUTY_PART_I
1666 ,0) + NVL(LN_RG23_DUTY_PART_I
1667 ,0))
1668 ,0);
1669 /*SRW.MESSAGE(1275
1670 ,'2a')*/NULL;
1671 RETURN LN_DUTY_PAYABLE;
1672 END IF;
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 /*SRW.MESSAGE(1275
1676 ,'CF_OTHER_duties' || SQLERRM)*/NULL;
1677 RETURN NULL;
1678 END CF_OTHER_DUTIESFORMULA;
1679
1680 FUNCTION CF_CESS_UTIL_PAY_GOODSFORMULA(CF_CR_UTIL_INP_RM_CESS IN NUMBER) RETURN NUMBER IS
1681 LN_EDU_CESS_EXCISE NUMBER;
1682 BEGIN
1683 SELECT
1684 NVL(SUM(DEBIT)
1685 ,0)
1686 INTO LN_EDU_CESS_EXCISE
1687 FROM
1688 JAI_CMN_RG_OTHERS JRO,
1689 JAI_CMN_RG_23AC_II_TRXS RG23
1690 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
1691 AND RG23.LOCATION_ID = P_LOCATION_ID
1692 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
1693 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
1694 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1695 ,SYSDATE))
1696 AND RG23.REGISTER_TYPE IN ( 'A' , 'C' )
1697 AND JRO.SOURCE_REGISTER in ( 'RG23A_P2' , 'RG23C_P2' )
1698 AND JRO.TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
1699 RETURN (ROUND(NVL(LN_EDU_CESS_EXCISE
1700 ,0) - NVL(CF_CR_UTIL_INP_RM_CESS
1701 ,0)
1702 ,0));
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 /*SRW.MESSAGE(1275
1706 ,'CF_cess_util_pay_goods:' || SQLERRM)*/NULL;
1707 RETURN NULL;
1708 END CF_CESS_UTIL_PAY_GOODSFORMULA;
1709
1710 FUNCTION CF_ITEM_DESCRIPTION(INVENTORY_ITEM_ID IN NUMBER) RETURN CHAR IS
1711 LV_ITEM_DESC MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE;
1712 CURSOR CUR_ITEM_DESC IS
1713 SELECT
1714 MSI.DESCRIPTION
1715 FROM
1716 MTL_SYSTEM_ITEMS MSI
1717 WHERE MSI.INVENTORY_ITEM_ID = cf_item_description.INVENTORY_ITEM_ID
1718 AND MSI.ORGANIZATION_ID = P_ORGANIZATION_ID;
1719 BEGIN
1720 OPEN CUR_ITEM_DESC;
1721 FETCH CUR_ITEM_DESC
1722 INTO LV_ITEM_DESC;
1723 CLOSE CUR_ITEM_DESC;
1724 RETURN LV_ITEM_DESC;
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 CLOSE CUR_ITEM_DESC;
1728 RETURN NULL;
1729 END CF_ITEM_DESCRIPTION;
1730
1731 FUNCTION CF_CREDIT_INPUT_MANF RETURN NUMBER IS
1732 CURSOR CUR_CRDIT_INPUT_MANF IS
1733 SELECT
1734 SUM(DECODE(REGISTER_TYPE
1735 ,'A'
1736 ,NVL(CR_BASIC_ED
1737 ,0) + NVL(CR_ADDITIONAL_ED
1738 ,0) + NVL(CR_OTHER_ED
1739 ,0)
1740 ,0)) CREDIT_AVAILED_ON_INPUTS_VEND
1741 FROM
1742 JAI_CMN_RG_23AC_II_TRXS JIRP,
1743 JAI_CMN_VENDOR_SITES JIPV
1744 WHERE LOCATION_ID = P_LOCATION_ID
1745 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1746 AND JIRP.VENDOR_ID = JIPV.VENDOR_ID
1747 AND JIRP.VENDOR_SITE_ID = JIPV.VENDOR_SITE_ID
1748 AND ( JIPV.VENDOR_TYPE IN ( 'Manufacturer' , 'Importer' )
1749 OR JIPV.VENDOR_TYPE IS NULL )
1750 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
1751 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1752 ,SYSDATE))
1753 AND not exists (
1754 SELECT
1755 1
1756 FROM
1757 JAI_INV_ITM_SETUPS JMSI
1758 WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
1759 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
1760 AND ( JMSI.ITEM_CLASS = 'RMEX'
1761 OR ( JMSI.ITEM_CLASS = 'RMIN'
1762 AND exists (
1763 SELECT
1764 1
1765 FROM
1766 JAI_RCV_LINES JTL,
1767 JAI_RCV_LINE_TAXES JRTL
1768 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
1769 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
1770 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
1771 CURSOR CUR_CRDIT_INPUT_CUST IS
1772 SELECT
1773 SUM(DECODE(REGISTER_TYPE
1774 ,'A'
1775 ,NVL(CR_BASIC_ED
1776 ,0) + NVL(CR_ADDITIONAL_ED
1777 ,0) + NVL(CR_OTHER_ED
1778 ,0)
1779 ,0)) CREDIT_AVAILED_ON_INPUTS_CUST
1780 FROM
1781 JAI_CMN_RG_23AC_II_TRXS JIRP,
1782 JAI_CMN_CUS_ADDRESSES JICA,
1783 HZ_CUST_ACCT_SITES_ALL HZCAS,
1784 HZ_CUST_SITE_USES_ALL HZCSU
1785 WHERE HZCAS.CUST_ACCT_SITE_ID = HZCSU.CUST_ACCT_SITE_ID
1786 AND JICA.ADDRESS_ID = HZCSU.CUST_ACCT_SITE_ID
1787 AND HZCSU.SITE_USE_ID = JIRP.CUSTOMER_SITE_ID
1788 AND JIRP.CUSTOMER_ID = JICA.CUSTOMER_ID
1789 AND JIRP.LOCATION_ID = P_LOCATION_ID
1790 AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
1791 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
1792 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1793 ,SYSDATE))
1794 AND not exists (
1795 SELECT
1796 1
1797 FROM
1798 JAI_INV_ITM_SETUPS JMSI
1799 WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
1800 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
1801 AND ( JMSI.ITEM_CLASS = 'RMEX'
1802 OR ( JMSI.ITEM_CLASS = 'RMIN'
1803 AND exists (
1804 SELECT
1805 1
1806 FROM
1807 JAI_RCV_LINES JTL,
1808 JAI_RCV_LINE_TAXES JRTL
1809 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
1810 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
1811 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
1812 CURSOR CUR_CRDIT_INPUT_MANF_ISO IS
1813 SELECT
1814 SUM(DECODE(REGISTER_TYPE
1815 ,'A'
1816 ,NVL(CR_BASIC_ED
1817 ,0) + NVL(CR_ADDITIONAL_ED
1818 ,0) + NVL(CR_OTHER_ED
1819 ,0)
1820 ,0)) CREDIT_AVAILED_ON_INPUTS
1821 FROM
1822 JAI_CMN_RG_23AC_II_TRXS JIRP,
1823 JAI_CMN_INVENTORY_ORGS JIHO
1824 WHERE JIRP.LOCATION_ID = P_LOCATION_ID
1825 AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
1826 AND ABS(JIRP.VENDOR_ID) = JIHO.ORGANIZATION_ID
1827 AND ABS(JIRP.VENDOR_SITE_ID) = JIHO.LOCATION_ID
1828 AND JIHO.MANUFACTURING = 'Y'
1829 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
1830 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1831 ,SYSDATE))
1832 AND not exists (
1833 SELECT
1834 1
1835 FROM
1836 JAI_INV_ITM_SETUPS JMSI
1837 WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
1838 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
1839 AND ( JMSI.ITEM_CLASS = 'RMEX'
1840 OR ( JMSI.ITEM_CLASS = 'RMIN'
1841 AND exists (
1842 SELECT
1843 1
1844 FROM
1845 JAI_RCV_LINES JTL,
1846 JAI_RCV_LINE_TAXES JRTL
1847 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
1848 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
1849 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
1850 LN_CLOSED_INPUT_MANF NUMBER;
1851 LN_CLOSED_INPUT_MANF_ISO NUMBER;
1852 LN_CLOSED_INPUT_CUST NUMBER;
1853 CURSOR C_CREDIT_IMPORT_INPUT IS
1854 SELECT
1855 ROUND(NVL(SUM(DECODE(REGISTER_TYPE
1856 ,'A'
1857 ,NVL(CR_BASIC_ED
1858 ,0) + NVL(CR_ADDITIONAL_ED
1859 ,0) + NVL(CR_OTHER_ED
1860 ,0)
1861 ,0))
1862 ,0)
1863 ,0) CREDIT_ON_IMPORT_INPUTS
1864 FROM
1865 JAI_CMN_RG_23AC_II_TRXS JIRP,
1866 JAI_INV_ITM_SETUPS JMSI
1867 WHERE JIRP.LOCATION_ID = P_LOCATION_ID
1868 AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
1869 AND JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
1870 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
1871 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
1872 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1873 ,SYSDATE))
1874 AND ( ( JIRP.VENDOR_ID is not null
1875 AND JIRP.VENDOR_SITE_ID is not null )
1876 OR ( JIRP.CUSTOMER_ID is not null
1877 AND JIRP.LOCATION_ID is not null ) )
1878 AND ( JMSI.ITEM_CLASS = 'RMEX'
1879 OR ( JMSI.ITEM_CLASS = 'RMIN'
1880 AND exists (
1881 SELECT
1882 1
1883 FROM
1884 JAI_RCV_LINES JTL,
1885 JAI_RCV_LINE_TAXES JRTL
1886 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
1887 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
1888 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) );
1889 BEGIN
1890 OPEN CUR_CRDIT_INPUT_MANF;
1891 FETCH CUR_CRDIT_INPUT_MANF
1892 INTO LN_CLOSED_INPUT_MANF;
1893 CLOSE CUR_CRDIT_INPUT_MANF;
1894 OPEN CUR_CRDIT_INPUT_MANF_ISO;
1895 FETCH CUR_CRDIT_INPUT_MANF_ISO
1896 INTO LN_CLOSED_INPUT_MANF_ISO;
1897 CLOSE CUR_CRDIT_INPUT_MANF_ISO;
1898 OPEN CUR_CRDIT_INPUT_CUST;
1899 FETCH CUR_CRDIT_INPUT_CUST
1900 INTO LN_CLOSED_INPUT_CUST;
1901 CLOSE CUR_CRDIT_INPUT_CUST;
1902 OPEN C_CREDIT_IMPORT_INPUT;
1903 FETCH C_CREDIT_IMPORT_INPUT
1904 INTO CP_CRDT_IMPORT_INPUT;
1905 CLOSE C_CREDIT_IMPORT_INPUT;
1906 RETURN ROUND(NVL(LN_CLOSED_INPUT_MANF
1907 ,0) + NVL(LN_CLOSED_INPUT_MANF_ISO
1908 ,0) + NVL(LN_CLOSED_INPUT_CUST
1909 ,0)
1910 ,0);
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 CLOSE CUR_CRDIT_INPUT_MANF;
1914 CLOSE CUR_CRDIT_INPUT_MANF_ISO;
1915 CLOSE CUR_CRDIT_INPUT_CUST;
1916 RETURN NULL;
1917 END CF_CREDIT_INPUT_MANF;
1918
1919 FUNCTION CF_CREDIT_INPUT_I_II_STG RETURN NUMBER IS
1920 CURSOR CUR_CRDIT_INPUT_STG IS
1921 SELECT
1922 SUM(DECODE(REGISTER_TYPE
1923 ,'A'
1924 ,NVL(CR_BASIC_ED
1925 ,0) + NVL(CR_ADDITIONAL_ED
1926 ,0) + NVL(CR_OTHER_ED
1927 ,0)
1928 ,0)) CREDIT_AVAILED_ON_INPUTS
1929 FROM
1930 JAI_CMN_RG_23AC_II_TRXS JIRP,
1931 JAI_CMN_VENDOR_SITES JIPV
1932 WHERE LOCATION_ID = P_LOCATION_ID
1933 AND ORGANIZATION_ID = P_ORGANIZATION_ID
1934 AND JIRP.VENDOR_ID = JIPV.VENDOR_ID
1935 AND JIRP.VENDOR_SITE_ID = JIPV.VENDOR_SITE_ID
1936 AND JIPV.VENDOR_TYPE IN ( 'First Stage Dealer' , 'Second Stage Dealer' )
1937 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
1938 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1939 ,SYSDATE))
1940 AND not exists (
1941 SELECT
1942 1
1943 FROM
1944 JAI_INV_ITM_SETUPS JMSI
1945 WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
1946 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
1947 AND ( JMSI.ITEM_CLASS = 'RMEX'
1948 OR ( JMSI.ITEM_CLASS = 'RMIN'
1949 AND exists (
1950 SELECT
1951 1
1952 FROM
1953 JAI_RCV_LINES JTL,
1954 JAI_RCV_LINE_TAXES JRTL
1955 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
1956 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
1957 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
1958 CURSOR CUR_CRDIT_INPUT_STG_ISO IS
1959 SELECT
1960 SUM(DECODE(REGISTER_TYPE
1961 ,'A'
1962 ,NVL(CR_BASIC_ED
1963 ,0) + NVL(CR_ADDITIONAL_ED
1964 ,0) + NVL(CR_OTHER_ED
1965 ,0)
1966 ,0)) CREDIT_AVAILED_ON_INPUTS
1967 FROM
1968 JAI_CMN_RG_23AC_II_TRXS JIRP,
1969 JAI_CMN_INVENTORY_ORGS JIHO
1970 WHERE JIRP.LOCATION_ID = P_LOCATION_ID
1971 AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
1972 AND ABS(JIRP.VENDOR_ID) = JIHO.ORGANIZATION_ID
1973 AND ABS(JIRP.VENDOR_SITE_ID) = JIHO.LOCATION_ID
1974 AND JIHO.TRADING = 'Y'
1975 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
1976 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
1977 ,SYSDATE))
1978 AND not exists (
1979 SELECT
1980 1
1981 FROM
1982 JAI_INV_ITM_SETUPS JMSI
1983 WHERE JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
1984 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
1985 AND ( JMSI.ITEM_CLASS = 'RMEX'
1986 OR ( JMSI.ITEM_CLASS = 'RMIN'
1987 AND exists (
1988 SELECT
1989 1
1990 FROM
1991 JAI_RCV_LINES JTL,
1992 JAI_RCV_LINE_TAXES JRTL
1993 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
1994 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
1995 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) ) );
1996 LN_CLOSED_INPUT_STG NUMBER;
1997 LN_CLOSED_INPUT_STG_ISO NUMBER;
1998 BEGIN
1999 OPEN CUR_CRDIT_INPUT_STG;
2000 FETCH CUR_CRDIT_INPUT_STG
2001 INTO LN_CLOSED_INPUT_STG;
2002 CLOSE CUR_CRDIT_INPUT_STG;
2003 OPEN CUR_CRDIT_INPUT_STG_ISO;
2004 FETCH CUR_CRDIT_INPUT_STG_ISO
2005 INTO LN_CLOSED_INPUT_STG_ISO;
2006 CLOSE CUR_CRDIT_INPUT_STG_ISO;
2007 RETURN ROUND(NVL(LN_CLOSED_INPUT_STG
2008 ,0) + NVL(LN_CLOSED_INPUT_STG_ISO
2009 ,0)
2010 ,0);
2011 EXCEPTION
2012 WHEN OTHERS THEN
2013 CLOSE CUR_CRDIT_INPUT_STG;
2014 CLOSE CUR_CRDIT_INPUT_STG_ISO;
2015 RETURN NULL;
2016 END CF_CREDIT_INPUT_I_II_STG;
2017
2018 FUNCTION CF_CESS_EXCISE_INPUT_MANF RETURN NUMBER IS
2019 CURSOR CUR_CESS_EXCISE_INPUT_MANF IS
2020 SELECT
2021 NVL(SUM(CREDIT)
2022 ,0)
2023 FROM
2024 JAI_CMN_RG_OTHERS JRO,
2025 JAI_CMN_RG_23AC_II_TRXS RG23,
2026 JAI_CMN_VENDOR_SITES JIPV
2027 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2028 AND RG23.VENDOR_ID = JIPV.VENDOR_ID
2029 AND RG23.VENDOR_SITE_ID = JIPV.VENDOR_SITE_ID
2030 AND ( JIPV.VENDOR_TYPE IN ( 'Manufacturer' , 'Importer' )
2031 OR JIPV.VENDOR_TYPE IS NULL )
2032 AND RG23.LOCATION_ID = P_LOCATION_ID
2033 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2034 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2035 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2036 ,SYSDATE))
2037 AND RG23.REGISTER_TYPE = 'A'
2038 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2039 AND JRO.TAX_TYPE IN ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
2040 CURSOR CUR_CESS_EXCISE_INPUT_CUST IS
2041 SELECT
2042 NVL(SUM(CREDIT)
2043 ,0)
2044 FROM
2045 JAI_CMN_RG_OTHERS JRO,
2046 JAI_CMN_RG_23AC_II_TRXS RG23,
2047 JAI_CMN_CUS_ADDRESSES JICA,
2048 HZ_CUST_ACCT_SITES_ALL HZCAS,
2049 HZ_CUST_SITE_USES_ALL HZCSU
2050 WHERE HZCAS.CUST_ACCT_SITE_ID = HZCSU.CUST_ACCT_SITE_ID
2051 AND JICA.ADDRESS_ID = HZCSU.CUST_ACCT_SITE_ID
2052 AND HZCSU.SITE_USE_ID = RG23.CUSTOMER_SITE_ID
2053 AND RG23.CUSTOMER_ID = JICA.CUSTOMER_ID
2054 AND JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2055 AND RG23.LOCATION_ID = P_LOCATION_ID
2056 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2057 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2058 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2059 ,SYSDATE))
2060 AND RG23.REGISTER_TYPE = 'A'
2061 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2062 AND JRO.TAX_TYPE IN ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
2063 CURSOR CUR_CESS_EXCISE_INPUT_MANF_ISO IS
2064 SELECT
2065 NVL(SUM(CREDIT)
2066 ,0)
2067 FROM
2068 JAI_CMN_RG_OTHERS JRO,
2069 JAI_CMN_RG_23AC_II_TRXS RG23,
2070 JAI_CMN_INVENTORY_ORGS JIHO
2071 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2072 AND ABS(RG23.VENDOR_ID) = JIHO.ORGANIZATION_ID
2073 AND ABS(RG23.VENDOR_SITE_ID) = JIHO.LOCATION_ID
2074 AND JIHO.MANUFACTURING = 'Y'
2075 AND RG23.LOCATION_ID = P_LOCATION_ID
2076 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2077 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2078 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2079 ,SYSDATE))
2080 AND RG23.REGISTER_TYPE = 'A'
2081 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2082 AND JRO.TAX_TYPE IN ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
2083 LN_EDU_CESS_EXCISE_MANF NUMBER;
2084 LN_EDU_CESS_EXCISE_MANF_ISO NUMBER;
2085 LN_EDU_CESS_EXCISE_CUST NUMBER;
2086 BEGIN
2087 OPEN CUR_CESS_EXCISE_INPUT_MANF;
2088 FETCH CUR_CESS_EXCISE_INPUT_MANF
2089 INTO LN_EDU_CESS_EXCISE_MANF;
2090 CLOSE CUR_CESS_EXCISE_INPUT_MANF;
2091 OPEN CUR_CESS_EXCISE_INPUT_MANF_ISO;
2092 FETCH CUR_CESS_EXCISE_INPUT_MANF_ISO
2093 INTO LN_EDU_CESS_EXCISE_MANF_ISO;
2094 CLOSE CUR_CESS_EXCISE_INPUT_MANF_ISO;
2095 OPEN CUR_CESS_EXCISE_INPUT_CUST;
2096 FETCH CUR_CESS_EXCISE_INPUT_CUST
2097 INTO LN_EDU_CESS_EXCISE_CUST;
2098 CLOSE CUR_CESS_EXCISE_INPUT_CUST;
2099 RETURN ROUND(NVL(LN_EDU_CESS_EXCISE_MANF
2100 ,0) + NVL(LN_EDU_CESS_EXCISE_MANF_ISO
2101 ,0) + NVL(LN_EDU_CESS_EXCISE_CUST
2102 ,0)
2103 ,0);
2104 EXCEPTION
2105 WHEN OTHERS THEN
2106 IF CUR_CESS_EXCISE_INPUT_MANF%ISOPEN THEN
2107 CLOSE CUR_CESS_EXCISE_INPUT_MANF;
2108 END IF;
2109 IF CUR_CESS_EXCISE_INPUT_MANF_ISO%ISOPEN THEN
2110 CLOSE CUR_CESS_EXCISE_INPUT_MANF_ISO;
2111 END IF;
2112 RETURN NULL;
2113 END CF_CESS_EXCISE_INPUT_MANF;
2114
2115 FUNCTION CF_CESS_EXCISE_INPUT_STG RETURN NUMBER IS
2116 CURSOR CUR_CESS_EXCISE_INPUT_STG IS
2117 SELECT
2118 NVL(SUM(CREDIT)
2119 ,0)
2120 FROM
2121 JAI_CMN_RG_OTHERS JRO,
2122 JAI_CMN_RG_23AC_II_TRXS RG23,
2123 JAI_CMN_VENDOR_SITES JIPV
2124 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2125 AND RG23.VENDOR_ID = JIPV.vendor_id (+)
2126 AND RG23.VENDOR_SITE_ID = JIPV.vendor_site_id (+)
2127 AND JIPV.VENDOR_TYPE IN ( 'First Stage Dealer' , 'Second Stage Dealer' )
2128 AND RG23.LOCATION_ID = P_LOCATION_ID
2129 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2130 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2131 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2132 ,SYSDATE))
2133 AND RG23.REGISTER_TYPE = 'A'
2134 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2135 AND JRO.TAX_TYPE IN ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
2136 CURSOR CUR_CESS_EXCISE_INPUT_STG_ISO IS
2137 SELECT
2138 NVL(SUM(CREDIT)
2139 ,0)
2140 FROM
2141 JAI_CMN_RG_OTHERS JRO,
2142 JAI_CMN_RG_23AC_II_TRXS RG23,
2143 JAI_CMN_INVENTORY_ORGS JIHO
2144 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2145 AND ABS(RG23.VENDOR_ID) = JIHO.ORGANIZATION_ID
2146 AND ABS(RG23.VENDOR_SITE_ID) = JIHO.LOCATION_ID
2147 AND JIHO.TRADING = 'Y'
2148 AND RG23.LOCATION_ID = P_LOCATION_ID
2149 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2150 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2151 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2152 ,SYSDATE))
2153 AND RG23.REGISTER_TYPE = 'A'
2154 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2155 AND JRO.TAX_TYPE IN ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS );
2156 LN_EDU_CESS_EXCISE_STG NUMBER;
2157 LN_EDU_CESS_EXCISE_STG_ISO NUMBER;
2158 BEGIN
2159 OPEN CUR_CESS_EXCISE_INPUT_STG;
2160 FETCH CUR_CESS_EXCISE_INPUT_STG
2161 INTO LN_EDU_CESS_EXCISE_STG;
2162 CLOSE CUR_CESS_EXCISE_INPUT_STG;
2163 OPEN CUR_CESS_EXCISE_INPUT_STG_ISO;
2164 FETCH CUR_CESS_EXCISE_INPUT_STG_ISO
2165 INTO LN_EDU_CESS_EXCISE_STG_ISO;
2166 CLOSE CUR_CESS_EXCISE_INPUT_STG_ISO;
2167 RETURN ROUND(NVL(LN_EDU_CESS_EXCISE_STG
2168 ,0) + NVL(LN_EDU_CESS_EXCISE_STG_ISO
2169 ,0)
2170 ,0);
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 IF CUR_CESS_EXCISE_INPUT_STG%ISOPEN THEN
2174 CLOSE CUR_CESS_EXCISE_INPUT_STG;
2175 END IF;
2176 IF CUR_CESS_EXCISE_INPUT_STG_ISO%ISOPEN THEN
2177 CLOSE CUR_CESS_EXCISE_INPUT_STG_ISO;
2178 END IF;
2179 RETURN NULL;
2180 END CF_CESS_EXCISE_INPUT_STG;
2181
2182 FUNCTION CF_GET_CETSH(INVENTORY_ITEM_ID IN NUMBER
2183 ,CETSH_SUB IN VARCHAR2) RETURN CHAR IS
2184 LV_CETSH JAI_INV_ITM_SETUPS.ITEM_TARIFF%TYPE;
2185 CURSOR CUR_GET_CETSH IS
2186 SELECT
2187 SUBSTR(JIMSI.ITEM_TARIFF
2188 ,1
2189 ,15)
2190 FROM
2191 JAI_INV_ITM_SETUPS JIMSI
2192 WHERE JIMSI.INVENTORY_ITEM_ID = cf_get_cetsh.INVENTORY_ITEM_ID
2193 AND JIMSI.ORGANIZATION_ID = P_ORGANIZATION_ID;
2194 BEGIN
2195 IF (P_GROUP_BY = 'I') THEN
2196 OPEN CUR_GET_CETSH;
2197 FETCH CUR_GET_CETSH
2198 INTO LV_CETSH;
2199 CLOSE CUR_GET_CETSH;
2200 RETURN LV_CETSH;
2201 ELSE
2202 RETURN CETSH_SUB;
2203 END IF;
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 CLOSE CUR_GET_CETSH;
2207 RETURN NULL;
2208 END CF_GET_CETSH;
2209
2210 FUNCTION CF_CENVAT_TOTAL1(CF_CENVAT_ACC_CURRENT IN NUMBER
2211 ,CF_CENVAT_CREDIT_UTILIZED IN NUMBER) RETURN NUMBER IS
2212 BEGIN
2213 RETURN ROUND((NVL(CF_CENVAT_ACC_CURRENT
2214 ,0) + NVL(CF_CENVAT_CREDIT_UTILIZED
2215 ,0)));
2216 END CF_CENVAT_TOTAL1;
2217
2218 FUNCTION AFTERREPORT RETURN BOOLEAN IS
2219 CURSOR CUR_SETUP_MISS_VENDORS IS
2220 SELECT
2221 PVS.VENDOR_SITE_CODE,
2222 PVS.VENDOR_ID,
2223 PVS.ADDRESS_LINE1,
2224 PVS.ADDRESS_LINE2,
2225 PVS.ADDRESS_LINE3,
2226 PVS.VENDOR_SITE_ID
2227 FROM
2228 PO_VENDOR_SITES_ALL PVS,
2229 JAI_CMN_RG_23AC_II_TRXS JIRP
2230 WHERE JIRP.VENDOR_ID = PVS.VENDOR_ID
2231 AND JIRP.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
2232 AND JIRP.VENDOR_ID || JIRP.VENDOR_SITE_ID NOT IN (
2233 SELECT
2234 JIPV.VENDOR_ID || JIPV.VENDOR_SITE_ID
2235 FROM
2236 JAI_CMN_VENDOR_SITES JIPV )
2237 AND JIRP.LOCATION_ID = P_LOCATION_ID
2238 AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
2239 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
2240 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2241 ,SYSDATE));
2242 LN_DATA_EXIST VARCHAR2(1) := 'Y';
2243 BEGIN
2244 FOR i IN CUR_SETUP_MISS_VENDORS LOOP
2245 IF LN_DATA_EXIST = 'Y' THEN
2246 /*SRW.MESSAGE(1275
2247 ,'|------------------------------------------------------------------------------------------------------------------------|')*/NULL;
2248 /*SRW.MESSAGE(1275
2249 ,'|List of Vendors for whom Additional Supplier Info setup is not done |')*/NULL;
2250 /*SRW.MESSAGE(1275
2251 ,'|------------------------------------------------------------------------------------------------------------------------|')*/NULL;
2252 /*SRW.MESSAGE(1275
2253 ,RPAD('|Party Name'
2254 ,30
2255 ,' ') || RPAD('Party Id'
2256 ,30
2257 ,' ') || RPAD('Party Site/Address'
2258 ,30
2259 ,' ') || RPAD('Party Site Id'
2260 ,31
2261 ,' ') || '|')*/NULL;
2262 /*SRW.MESSAGE(1275
2263 ,'|------------------------------------------------------------------------------------------------------------------------|')*/NULL;
2264 LN_DATA_EXIST := 'N';
2265 END IF;
2266 /*SRW.MESSAGE(1275
2267 ,'|' || RPAD(I.VENDOR_SITE_CODE
2268 ,30
2269 ,' ') || RPAD(I.VENDOR_ID
2270 ,30
2271 ,' ') || RPAD(SUBSTR(I.ADDRESS_LINE1 || I.ADDRESS_LINE2 || I.ADDRESS_LINE3
2272 ,1
2273 ,30)
2274 ,30
2275 ,' ') || RPAD(I.VENDOR_SITE_ID
2276 ,31
2277 ,' ') || '|')*/NULL;
2278 END LOOP;
2279 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
2280 RETURN (TRUE);
2281 END AFTERREPORT;
2282
2283 FUNCTION CF_RTV_AMOUNTFORMULA RETURN NUMBER IS
2284 CURSOR GET_RTV_AMOUNT IS
2285 SELECT
2286 SUM(NVL(JRG23_II.DR_BASIC_ED
2287 ,0) + NVL(JRG23_II.DR_ADDITIONAL_ED
2288 ,0) + NVL(JRG23_II.DR_OTHER_ED
2289 ,0))
2290 FROM
2291 JAI_CMN_RG_23AC_II_TRXS JRG23_II,
2292 JAI_CMN_RG_23AC_I_TRXS JRG23_I
2293 WHERE JRG23_II.ORGANIZATION_ID = P_ORGANIZATION_ID
2294 AND JRG23_II.LOCATION_ID = P_LOCATION_ID
2295 AND TRUNC(JRG23_II.CREATION_DATE) >= P_START_DATE
2296 AND TRUNC(JRG23_II.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2297 ,SYSDATE))
2298 AND JRG23_I.TRANSACTION_TYPE = 'RTV'
2299 AND JRG23_II.ORGANIZATION_ID = JRG23_I.ORGANIZATION_ID
2300 AND JRG23_II.LOCATION_ID = JRG23_I.LOCATION_ID
2301 AND JRG23_II.REGISTER_ID_PART_I = JRG23_I.REGISTER_ID;
2302 CURSOR GET_CGIN_SALES IS
2303 SELECT
2304 SUM(NVL(JRG23_II.DR_BASIC_ED
2305 ,0) + NVL(JRG23_II.DR_ADDITIONAL_ED
2306 ,0) + NVL(JRG23_II.DR_OTHER_ED
2307 ,0))
2308 FROM
2309 JAI_CMN_RG_23AC_II_TRXS JRG23_II,
2310 JAI_CMN_RG_23AC_I_TRXS JRG23_I,
2311 JAI_INV_ITM_SETUPS JMSI
2312 WHERE JRG23_II.ORGANIZATION_ID = JRG23_I.ORGANIZATION_ID
2313 AND JRG23_II.LOCATION_ID = JRG23_I.LOCATION_ID
2314 AND JRG23_II.REGISTER_ID_PART_I = JRG23_I.REGISTER_ID
2315 AND JMSI.ORGANIZATION_ID = JRG23_II.ORGANIZATION_ID
2316 AND JMSI.ITEM_CLASS like 'CG%'
2317 AND JMSI.INVENTORY_ITEM_ID = JRG23_II.INVENTORY_ITEM_ID
2318 AND JMSI.ORGANIZATION_ID = P_ORGANIZATION_ID
2319 AND JRG23_II.ORGANIZATION_ID = P_ORGANIZATION_ID
2320 AND JRG23_II.LOCATION_ID = P_LOCATION_ID
2321 AND TRUNC(JRG23_II.CREATION_DATE) >= P_START_DATE
2322 AND TRUNC(JRG23_II.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2323 ,SYSDATE))
2324 AND JRG23_I.TRANSACTION_TYPE <> 'RTV';
2325 LN_RTV_AMOUNT NUMBER;
2326 LN_CGIN_SALE_AMT NUMBER;
2327 LN_AMT_INPUTS_RM NUMBER;
2328 BEGIN
2329 OPEN GET_RTV_AMOUNT;
2330 FETCH GET_RTV_AMOUNT
2331 INTO LN_RTV_AMOUNT;
2332 CLOSE GET_RTV_AMOUNT;
2333 OPEN GET_CGIN_SALES;
2334 FETCH GET_CGIN_SALES
2335 INTO LN_CGIN_SALE_AMT;
2336 CLOSE GET_CGIN_SALES;
2337 RETURN ROUND(NVL(LN_RTV_AMOUNT
2338 ,0) + NVL(LN_CGIN_SALE_AMT
2339 ,0));
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 /*SRW.MESSAGE(1275
2343 ,'CF_rtv_amountFormula:' || SQLERRM)*/NULL;
2344 RETURN NULL;
2345 END CF_RTV_AMOUNTFORMULA;
2346
2347 FUNCTION CF_CR_UTILIZEDFORMULA(CREDIT_UTILIZED IN NUMBER
2348 ,CF_RTV_AMOUNT IN NUMBER) RETURN NUMBER IS
2349 BEGIN
2350 RETURN (ROUND(NVL(CREDIT_UTILIZED
2351 ,0) - NVL(CF_RTV_AMOUNT
2352 ,0)));
2353 END CF_CR_UTILIZEDFORMULA;
2354
2355 FUNCTION CF_CR_UTIL_INP_RM_CESSFORMULA RETURN NUMBER IS
2356 CURSOR GET_RTV_CESS IS
2357 SELECT
2358 SUM(NVL(DEBIT
2359 ,0))
2360 FROM
2361 JAI_CMN_RG_OTHERS
2362 WHERE SOURCE_TYPE = 1
2363 AND TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS )
2364 AND SOURCE_REGISTER_ID in (
2365 SELECT
2366 JRG23_II.REGISTER_ID
2367 FROM
2368 JAI_CMN_RG_23AC_II_TRXS JRG23_II,
2369 JAI_CMN_RG_23AC_I_TRXS JRG23_I
2370 WHERE JRG23_II.ORGANIZATION_ID = P_ORGANIZATION_ID
2371 AND JRG23_II.LOCATION_ID = P_LOCATION_ID
2372 AND TRUNC(JRG23_II.CREATION_DATE) >= P_START_DATE
2373 AND TRUNC(JRG23_II.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2374 ,SYSDATE))
2375 AND JRG23_I.TRANSACTION_TYPE = 'RTV'
2376 AND JRG23_II.ORGANIZATION_ID = JRG23_I.ORGANIZATION_ID
2377 AND JRG23_II.LOCATION_ID = JRG23_I.LOCATION_ID
2378 AND JRG23_II.REGISTER_ID_PART_I = JRG23_I.REGISTER_ID );
2379 CURSOR GET_CGIN_SALES_CESS IS
2380 SELECT
2381 SUM(NVL(DEBIT
2382 ,0))
2383 FROM
2384 JAI_CMN_RG_OTHERS
2385 WHERE SOURCE_TYPE = 1
2386 AND TAX_TYPE in ( TAX_TYPE_CVD_EDU_CESS , TAX_TYPE_EXC_EDU_CESS )
2387 AND SOURCE_REGISTER_ID in (
2388 SELECT
2389 JRG23_II.REGISTER_ID
2390 FROM
2391 JAI_CMN_RG_23AC_II_TRXS JRG23_II,
2392 JAI_CMN_RG_23AC_I_TRXS JRG23_I,
2393 JAI_INV_ITM_SETUPS JMSI
2394 WHERE JRG23_II.ORGANIZATION_ID = JRG23_I.ORGANIZATION_ID
2395 AND JRG23_II.LOCATION_ID = JRG23_I.LOCATION_ID
2396 AND JRG23_II.REGISTER_ID_PART_I = JRG23_I.REGISTER_ID
2397 AND JMSI.ORGANIZATION_ID = JRG23_II.ORGANIZATION_ID
2398 AND JMSI.ITEM_CLASS like 'CG%'
2399 AND JMSI.INVENTORY_ITEM_ID = JRG23_II.INVENTORY_ITEM_ID
2400 AND JMSI.ORGANIZATION_ID = P_ORGANIZATION_ID
2401 AND JRG23_II.ORGANIZATION_ID = P_ORGANIZATION_ID
2402 AND JRG23_II.LOCATION_ID = P_LOCATION_ID
2403 AND TRUNC(JRG23_II.CREATION_DATE) >= P_START_DATE
2404 AND TRUNC(JRG23_II.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2405 ,SYSDATE))
2406 AND JRG23_I.TRANSACTION_TYPE <> 'RTV' );
2407 LN_RTV_CESS NUMBER;
2408 LN_CGIN_SALES_CESS NUMBER;
2409 LN_CR_UTIL_INP_RM_CESS NUMBER;
2410 BEGIN
2411 OPEN GET_RTV_CESS;
2412 FETCH GET_RTV_CESS
2413 INTO LN_RTV_CESS;
2414 CLOSE GET_RTV_CESS;
2415 OPEN GET_CGIN_SALES_CESS;
2416 FETCH GET_CGIN_SALES_CESS
2417 INTO LN_CGIN_SALES_CESS;
2418 CLOSE GET_CGIN_SALES_CESS;
2419 RETURN ROUND(NVL(LN_RTV_CESS
2420 ,0) + NVL(LN_CGIN_SALES_CESS
2421 ,0));
2422 EXCEPTION
2423 WHEN OTHERS THEN
2424 /*SRW.MESSAGE(1275
2425 ,'CF_cr_util_inp_rm_cessFormula:' || SQLERRM)*/NULL;
2426 RETURN NULL;
2427 END CF_CR_UTIL_INP_RM_CESSFORMULA;
2428
2429 FUNCTION CF_LTUFORMULA RETURN CHAR IS
2430 BEGIN
2431 RETURN 'Large Taxpayer Unit opted for (name of the city)* .-' || P_CITY;
2432 END CF_LTUFORMULA;
2433
2434 FUNCTION CF_CRDT_AVALIED_CAP_GOODSFORMU(CREDIT_AVAILED_ON_CAP_GOODS IN NUMBER) RETURN NUMBER IS
2435 CURSOR C_CREDIT_CAP_GOODS IS
2436 SELECT
2437 ROUND(SUM(DECODE(REGISTER_TYPE
2438 ,'C'
2439 ,NVL(CR_BASIC_ED
2440 ,0) + NVL(CR_ADDITIONAL_ED
2441 ,0) + NVL(CR_OTHER_ED
2442 ,0)
2443 ,0))
2444 ,0) CREDIT_AVAILED_ON_CAP_GOODS
2445 FROM
2446 JAI_CMN_RG_23AC_II_TRXS JIRP,
2447 JAI_INV_ITM_SETUPS JMSI
2448 WHERE JIRP.LOCATION_ID = P_LOCATION_ID
2449 AND JIRP.ORGANIZATION_ID = P_ORGANIZATION_ID
2450 AND JIRP.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
2451 AND JIRP.INVENTORY_ITEM_ID = JMSI.INVENTORY_ITEM_ID
2452 AND TRUNC(JIRP.CREATION_DATE) >= P_START_DATE
2453 AND TRUNC(JIRP.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2454 ,SYSDATE))
2455 AND ( JMSI.ITEM_CLASS = 'CGEX'
2456 OR ( JMSI.ITEM_CLASS = 'CGIN'
2457 AND exists (
2458 SELECT
2459 1
2460 FROM
2461 JAI_RCV_LINES JTL,
2462 JAI_RCV_LINE_TAXES JRTL
2463 WHERE JTL.TRANSACTION_ID = JIRP.RECEIPT_REF
2464 AND JTL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
2465 AND JRTL.TAX_TYPE IN ( 'ADDITIONAL_CVD' , 'CVD' ) ) ) );
2466 BEGIN
2467 OPEN C_CREDIT_CAP_GOODS;
2468 FETCH C_CREDIT_CAP_GOODS
2469 INTO CP_CRDT_IMPORT_CAP_GOODS;
2470 CLOSE C_CREDIT_CAP_GOODS;
2471 RETURN NVL(CREDIT_AVAILED_ON_CAP_GOODS
2472 ,0) - NVL(CP_CRDT_IMPORT_CAP_GOODS
2473 ,0);
2474 END CF_CRDT_AVALIED_CAP_GOODSFORMU;
2475
2476 FUNCTION CF_PLA_OPENING_BALANCEFORMULA RETURN NUMBER IS
2477 LN_OPENING_BALANCE JAI_CMN_RG_PLA_TRXS.OPENING_BALANCE%TYPE;
2478 BEGIN
2479 SELECT
2480 SUM(NVL(CR_BASIC_ED
2481 ,0) + NVL(CR_ADDITIONAL_ED
2482 ,0) + NVL(CR_OTHER_ED
2483 ,0) - NVL(DR_BASIC_ED
2484 ,0) - NVL(DR_ADDITIONAL_ED
2485 ,0) - NVL(DR_OTHER_ED
2486 ,0))
2487 INTO LN_OPENING_BALANCE
2488 FROM
2489 JAI_CMN_RG_PLA_TRXS
2490 WHERE LOCATION_ID = P_LOCATION_ID
2491 AND ORGANIZATION_ID = P_ORGANIZATION_ID
2492 AND CREATION_DATE < P_START_DATE;
2493 RETURN ROUND(NVL(LN_OPENING_BALANCE
2494 ,0)
2495 ,0);
2496 EXCEPTION
2497 WHEN OTHERS THEN
2498 /*SRW.MESSAGE(1275
2499 ,'CF_pla_opening_balance:' || SQLERRM)*/NULL;
2500 RETURN NULL;
2501 END CF_PLA_OPENING_BALANCEFORMULA;
2502
2503 FUNCTION CF_PLA_TR6_CHALLAN_AMTFORMULA RETURN NUMBER IS
2504 LN_PLA_AMOUNT NUMBER;
2505 BEGIN
2506 SELECT
2507 NVL(SUM(PLA_AMOUNT)
2508 ,0)
2509 INTO LN_PLA_AMOUNT
2510 FROM
2511 JAI_CMN_RG_PLA_HDRS A
2512 WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
2513 AND A.LOCATION_ID = P_LOCATION_ID
2514 AND TRUNC(A.TR6_DATE) >= P_START_DATE
2515 AND TRUNC(A.TR6_DATE) <= P_END_DATE
2516 AND A.ACK_RECVD_FLAG = 'Y';
2517 RETURN LN_PLA_AMOUNT;
2518 EXCEPTION
2519 WHEN OTHERS THEN
2520 /*SRW.MESSAGE(1275
2521 ,'CF_pla_TR6_Challan_Amt:' || SQLERRM)*/NULL;
2522 RETURN NULL;
2523 END CF_PLA_TR6_CHALLAN_AMTFORMULA;
2524
2525 FUNCTION CF_PLA_TOTALFORMULA(CF_PLA_OPENING_BALANCE IN NUMBER
2526 ,CF_PLA_TR6_CHALLAN_AMT IN NUMBER) RETURN NUMBER IS
2527 LN_PLA_TOTAL_AMT NUMBER;
2528 BEGIN
2529 LN_PLA_TOTAL_AMT := CF_PLA_OPENING_BALANCE + CF_PLA_TR6_CHALLAN_AMT;
2530 RETURN NVL(LN_PLA_TOTAL_AMT
2531 ,0);
2532 END CF_PLA_TOTALFORMULA;
2533
2534 FUNCTION CF_PLA_CLOSING_BALANCEFORMULA(CF_PLA_TOTAL IN NUMBER
2535 ,CF_CENVAT_ACC_CURRENT IN NUMBER) RETURN NUMBER IS
2536 BEGIN
2537 RETURN ROUND((NVL(CF_PLA_TOTAL
2538 ,0) - NVL(CF_CENVAT_ACC_CURRENT
2539 ,0))
2540 ,0);
2541 END CF_PLA_CLOSING_BALANCEFORMULA;
2542
2543 FUNCTION CF_AED_ACC_CREDITFORMULA RETURN NUMBER IS
2544 CURSOR C_AED_ACC_CREDIT IS
2545 SELECT
2546 ROUND(SUM(NVL(DR_ADDITIONAL_ED
2547 ,0))
2548 ,0) AED_CREDIT_UTILIZED
2549 FROM
2550 JAI_CMN_RG_23AC_II_TRXS
2551 WHERE LOCATION_ID = P_LOCATION_ID
2552 AND ORGANIZATION_ID = P_ORGANIZATION_ID
2553 AND TRUNC(CREATION_DATE) >= P_START_DATE
2554 AND TRUNC(CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2555 ,SYSDATE));
2556 LN_AED_ACC_CREDIT NUMBER;
2557 CURSOR C_AED_ACC_CURRENT IS
2558 SELECT
2559 ROUND(SUM(NVL(CR_ADDITIONAL_ED
2560 ,0))
2561 ,0)
2562 FROM
2563 JAI_CMN_RG_PLA_TRXS
2564 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
2565 AND LOCATION_ID = P_LOCATION_ID
2566 AND CREATION_DATE >= P_START_DATE
2567 AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
2568 ,SYSDATE))
2569 AND TRANSACTION_SOURCE_NUM = 91;
2570 BEGIN
2571 OPEN C_AED_ACC_CURRENT;
2572 FETCH C_AED_ACC_CURRENT
2573 INTO CP_AED_ACC_CURRENT;
2574 CLOSE C_AED_ACC_CURRENT;
2575 OPEN C_AED_ACC_CREDIT;
2576 FETCH C_AED_ACC_CREDIT
2577 INTO LN_AED_ACC_CREDIT;
2578 CLOSE C_AED_ACC_CREDIT;
2579 RETURN NVL(LN_AED_ACC_CREDIT
2580 ,0);
2581 END CF_AED_ACC_CREDITFORMULA;
2582
2583 FUNCTION CF_AED_TOTALFORMULA(CF_AED_ACC_CREDIT IN NUMBER) RETURN NUMBER IS
2584 BEGIN
2585 RETURN ROUND((NVL(CF_AED_ACC_CREDIT
2586 ,0) + NVL(CP_AED_ACC_CURRENT
2587 ,0))
2588 ,0);
2589 END CF_AED_TOTALFORMULA;
2590
2591 FUNCTION CF_CENVAT_CREDIT_UTILIZEDFORMU(CREDIT_UTILIZED IN NUMBER
2592 ,CF_AED_ACC_CREDIT IN NUMBER) RETURN NUMBER IS
2593 BEGIN
2594 RETURN NVL(CREDIT_UTILIZED
2595 ,0) - NVL(CF_AED_ACC_CREDIT
2596 ,0);
2597 END CF_CENVAT_CREDIT_UTILIZEDFORMU;
2598
2599 FUNCTION CF_EXCISE_UOMFORMULA(ORGANIZATION_ID IN NUMBER
2600 ,UNITS IN VARCHAR2) RETURN CHAR IS
2601 CURSOR C_EXCISE_UOM_CODE(CP_ORGANIZATION_ID IN NUMBER,CP_PRIMARY_UOM_CODE IN VARCHAR2) IS
2602 SELECT
2603 EXCISE_UOM_CODE
2604 FROM
2605 JAI_AR_EXCISE_UOM
2606 WHERE ORGANIZATION_ID = CP_ORGANIZATION_ID
2607 AND PRIMARY_UOM_CODE = CP_PRIMARY_UOM_CODE;
2608 LV_UQC VARCHAR2(8);
2609 BEGIN
2610 OPEN C_EXCISE_UOM_CODE(ORGANIZATION_ID,UNITS);
2611 FETCH C_EXCISE_UOM_CODE
2612 INTO LV_UQC;
2613 CLOSE C_EXCISE_UOM_CODE;
2614 RETURN LV_UQC;
2615 END CF_EXCISE_UOMFORMULA;
2616
2617 FUNCTION CF_SH_CESS_OPENING_BLNCFORMULA RETURN NUMBER IS
2618 LV_REGISTER_ID JAI_CMN_RG_23AC_II_TRXS.REGISTER_ID%TYPE;
2619 CURSOR CUR_OPENING_BAL IS
2620 SELECT
2621 SUM(NVL(CREDIT
2622 ,0) - NVL(DEBIT
2623 ,0))
2624 FROM
2625 JAI_CMN_RG_OTHERS
2626 WHERE SOURCE_TYPE = 1
2627 AND SOURCE_REGISTER_ID in (
2628 SELECT
2629 REGISTER_ID
2630 FROM
2631 JAI_CMN_RG_23AC_II_TRXS
2632 WHERE LOCATION_ID = P_LOCATION_ID
2633 AND ORGANIZATION_ID = P_ORGANIZATION_ID
2634 AND TRUNC(CREATION_DATE) < P_START_DATE )
2635 AND TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2636 LN_OPENING_BALANCE NUMBER;
2637 BEGIN
2638 OPEN CUR_OPENING_BAL;
2639 FETCH CUR_OPENING_BAL
2640 INTO LN_OPENING_BALANCE;
2641 CLOSE CUR_OPENING_BAL;
2642 RETURN ROUND(LN_OPENING_BALANCE
2643 ,0);
2644 END CF_SH_CESS_OPENING_BLNCFORMULA;
2645
2646 FUNCTION CF_SH_CESS_EXCISE_INPUT_MANFFO RETURN NUMBER IS
2647 CURSOR CUR_CESS_EXCISE_INPUT_MANF IS
2648 SELECT
2649 NVL(SUM(CREDIT)
2650 ,0)
2651 FROM
2652 JAI_CMN_RG_OTHERS JRO,
2653 JAI_CMN_RG_23AC_II_TRXS RG23,
2654 JAI_CMN_VENDOR_SITES JIPV
2655 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2656 AND RG23.VENDOR_ID = JIPV.VENDOR_ID
2657 AND RG23.VENDOR_SITE_ID = JIPV.VENDOR_SITE_ID
2658 AND ( JIPV.VENDOR_TYPE IN ( 'Manufacturer' , 'Importer' )
2659 OR JIPV.VENDOR_TYPE IS NULL )
2660 AND RG23.LOCATION_ID = P_LOCATION_ID
2661 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2662 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2663 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2664 ,SYSDATE))
2665 AND RG23.REGISTER_TYPE = 'A'
2666 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2667 AND JRO.TAX_TYPE IN ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2668 CURSOR CUR_CESS_EXCISE_INPUT_CUST IS
2669 SELECT
2670 NVL(SUM(CREDIT)
2671 ,0)
2672 FROM
2673 JAI_CMN_RG_OTHERS JRO,
2674 JAI_CMN_RG_23AC_II_TRXS RG23,
2675 JAI_CMN_CUS_ADDRESSES JICA,
2676 HZ_CUST_ACCT_SITES_ALL HZCAS,
2677 HZ_CUST_SITE_USES_ALL HZCSU
2678 WHERE HZCAS.CUST_ACCT_SITE_ID = HZCSU.CUST_ACCT_SITE_ID
2679 AND JICA.ADDRESS_ID = HZCSU.CUST_ACCT_SITE_ID
2680 AND HZCSU.SITE_USE_ID = RG23.CUSTOMER_SITE_ID
2681 AND RG23.CUSTOMER_ID = JICA.CUSTOMER_ID
2682 AND JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2683 AND RG23.LOCATION_ID = P_LOCATION_ID
2684 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2685 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2686 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2687 ,SYSDATE))
2688 AND RG23.REGISTER_TYPE = 'A'
2689 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2690 AND JRO.TAX_TYPE IN ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2691 CURSOR CUR_CESS_EXCISE_INPUT_MANF_ISO IS
2692 SELECT
2693 NVL(SUM(CREDIT)
2694 ,0)
2695 FROM
2696 JAI_CMN_RG_OTHERS JRO,
2697 JAI_CMN_RG_23AC_II_TRXS RG23,
2698 JAI_CMN_INVENTORY_ORGS JIHO
2699 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2700 AND ABS(RG23.VENDOR_ID) = JIHO.ORGANIZATION_ID
2701 AND ABS(RG23.VENDOR_SITE_ID) = JIHO.LOCATION_ID
2702 AND JIHO.MANUFACTURING = 'Y'
2703 AND RG23.LOCATION_ID = P_LOCATION_ID
2704 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2705 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2706 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2707 ,SYSDATE))
2708 AND RG23.REGISTER_TYPE = 'A'
2709 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2710 AND JRO.TAX_TYPE IN ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2711 LN_EDU_CESS_EXCISE_MANF NUMBER;
2712 LN_EDU_CESS_EXCISE_MANF_ISO NUMBER;
2713 LN_EDU_CESS_EXCISE_CUST NUMBER;
2714 BEGIN
2715 OPEN CUR_CESS_EXCISE_INPUT_MANF;
2716 FETCH CUR_CESS_EXCISE_INPUT_MANF
2717 INTO LN_EDU_CESS_EXCISE_MANF;
2718 CLOSE CUR_CESS_EXCISE_INPUT_MANF;
2719 OPEN CUR_CESS_EXCISE_INPUT_MANF_ISO;
2720 FETCH CUR_CESS_EXCISE_INPUT_MANF_ISO
2721 INTO LN_EDU_CESS_EXCISE_MANF_ISO;
2722 CLOSE CUR_CESS_EXCISE_INPUT_MANF_ISO;
2723 OPEN CUR_CESS_EXCISE_INPUT_CUST;
2724 FETCH CUR_CESS_EXCISE_INPUT_CUST
2725 INTO LN_EDU_CESS_EXCISE_CUST;
2726 CLOSE CUR_CESS_EXCISE_INPUT_CUST;
2727 RETURN ROUND(NVL(LN_EDU_CESS_EXCISE_MANF
2728 ,0) + NVL(LN_EDU_CESS_EXCISE_MANF_ISO
2729 ,0) + NVL(LN_EDU_CESS_EXCISE_CUST
2730 ,0)
2731 ,0);
2732 EXCEPTION
2733 WHEN OTHERS THEN
2734 IF CUR_CESS_EXCISE_INPUT_MANF%ISOPEN THEN
2735 CLOSE CUR_CESS_EXCISE_INPUT_MANF;
2736 END IF;
2737 IF CUR_CESS_EXCISE_INPUT_MANF_ISO%ISOPEN THEN
2738 CLOSE CUR_CESS_EXCISE_INPUT_MANF_ISO;
2739 END IF;
2740 RETURN NULL;
2741 END CF_SH_CESS_EXCISE_INPUT_MANFFO;
2742
2743 FUNCTION CF_SH_CESS_EXCISE_INPUT_STGFOR RETURN NUMBER IS
2744 CURSOR CUR_CESS_EXCISE_INPUT_STG IS
2745 SELECT
2746 NVL(SUM(CREDIT)
2747 ,0)
2748 FROM
2749 JAI_CMN_RG_OTHERS JRO,
2750 JAI_CMN_RG_23AC_II_TRXS RG23,
2751 JAI_CMN_VENDOR_SITES JIPV
2752 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2753 AND RG23.VENDOR_ID = JIPV.vendor_id (+)
2754 AND RG23.VENDOR_SITE_ID = JIPV.vendor_site_id (+)
2755 AND JIPV.VENDOR_TYPE IN ( 'First Stage Dealer' , 'Second Stage Dealer' )
2756 AND RG23.LOCATION_ID = P_LOCATION_ID
2757 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2758 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2759 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2760 ,SYSDATE))
2761 AND RG23.REGISTER_TYPE = 'A'
2762 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2763 AND JRO.TAX_TYPE IN ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2764 CURSOR CUR_CESS_EXCISE_INPUT_STG_ISO IS
2765 SELECT
2766 NVL(SUM(CREDIT)
2767 ,0)
2768 FROM
2769 JAI_CMN_RG_OTHERS JRO,
2770 JAI_CMN_RG_23AC_II_TRXS RG23,
2771 JAI_CMN_INVENTORY_ORGS JIHO
2772 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2773 AND ABS(RG23.VENDOR_ID) = JIHO.ORGANIZATION_ID
2774 AND ABS(RG23.VENDOR_SITE_ID) = JIHO.LOCATION_ID
2775 AND JIHO.TRADING = 'Y'
2776 AND RG23.LOCATION_ID = P_LOCATION_ID
2777 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2778 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2779 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2780 ,SYSDATE))
2781 AND RG23.REGISTER_TYPE = 'A'
2782 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2783 AND JRO.TAX_TYPE IN ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2784 LN_EDU_CESS_EXCISE_STG NUMBER;
2785 LN_EDU_CESS_EXCISE_STG_ISO NUMBER;
2786 BEGIN
2787 OPEN CUR_CESS_EXCISE_INPUT_STG;
2788 FETCH CUR_CESS_EXCISE_INPUT_STG
2789 INTO LN_EDU_CESS_EXCISE_STG;
2790 CLOSE CUR_CESS_EXCISE_INPUT_STG;
2791 OPEN CUR_CESS_EXCISE_INPUT_STG_ISO;
2792 FETCH CUR_CESS_EXCISE_INPUT_STG_ISO
2793 INTO LN_EDU_CESS_EXCISE_STG_ISO;
2794 CLOSE CUR_CESS_EXCISE_INPUT_STG_ISO;
2795 RETURN ROUND(NVL(LN_EDU_CESS_EXCISE_STG
2796 ,0) + NVL(LN_EDU_CESS_EXCISE_STG_ISO
2797 ,0)
2798 ,0);
2799 EXCEPTION
2800 WHEN OTHERS THEN
2801 IF CUR_CESS_EXCISE_INPUT_STG%ISOPEN THEN
2802 CLOSE CUR_CESS_EXCISE_INPUT_STG;
2803 END IF;
2804 IF CUR_CESS_EXCISE_INPUT_STG_ISO%ISOPEN THEN
2805 CLOSE CUR_CESS_EXCISE_INPUT_STG_ISO;
2806 END IF;
2807 RETURN NULL;
2808 END CF_SH_CESS_EXCISE_INPUT_STGFOR;
2809
2810 FUNCTION CF_SH_CESS_EXCISE_CAPFORMULA RETURN NUMBER IS
2811 LN_EDU_CESS_CAP NUMBER;
2812 CURSOR CUR_GET_EDU_CESS_CAP IS
2813 SELECT
2814 NVL(SUM(CREDIT)
2815 ,0)
2816 FROM
2817 JAI_CMN_RG_OTHERS JRO,
2818 JAI_CMN_RG_23AC_II_TRXS RG23
2819 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2820 AND RG23.LOCATION_ID = P_LOCATION_ID
2821 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2822 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2823 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2824 ,SYSDATE))
2825 AND RG23.REGISTER_TYPE = 'C'
2826 AND JRO.SOURCE_REGISTER = 'RG23C_P2'
2827 AND JRO.TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2828 BEGIN
2829 OPEN CUR_GET_EDU_CESS_CAP;
2830 FETCH CUR_GET_EDU_CESS_CAP
2831 INTO LN_EDU_CESS_CAP;
2832 CLOSE CUR_GET_EDU_CESS_CAP;
2833 RETURN (ROUND(LN_EDU_CESS_CAP
2834 ,0));
2835 EXCEPTION
2836 WHEN OTHERS THEN
2837 /*SRW.MESSAGE(1275
2838 ,'CF_cess_excise_cap:' || SQLERRM)*/NULL;
2839 RETURN NULL;
2840 END CF_SH_CESS_EXCISE_CAPFORMULA;
2841
2842 FUNCTION CF_TOT_CR_AVAILED_SH_ECFORMULA(CF_SH_CESS_EXCISE_INPUT IN NUMBER
2843 ,CF_SH_CESS_EXCISE_CAP IN NUMBER) RETURN NUMBER IS
2844 BEGIN
2845 RETURN ROUND((NVL(CF_SH_CESS_EXCISE_INPUT
2846 ,0) + NVL(CF_SH_CESS_EXCISE_CAP
2847 ,0))
2848 ,0);
2849 END CF_TOT_CR_AVAILED_SH_ECFORMULA;
2850
2851 FUNCTION CF_SH_CESS_EXCISE_INPUTFORMULA RETURN NUMBER IS
2852 LN_EDU_CESS_EXCISE NUMBER;
2853 CURSOR CUR_GET_EDU_CESS_EXCISE IS
2854 SELECT
2855 NVL(SUM(CREDIT)
2856 ,0)
2857 FROM
2858 JAI_CMN_RG_OTHERS JRO,
2859 JAI_CMN_RG_23AC_II_TRXS RG23
2860 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2861 AND RG23.LOCATION_ID = P_LOCATION_ID
2862 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2863 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2864 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2865 ,SYSDATE))
2866 AND RG23.REGISTER_TYPE = 'A'
2867 AND JRO.SOURCE_REGISTER = 'RG23A_P2'
2868 AND JRO.TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2869 BEGIN
2870 OPEN CUR_GET_EDU_CESS_EXCISE;
2871 FETCH CUR_GET_EDU_CESS_EXCISE
2872 INTO LN_EDU_CESS_EXCISE;
2873 CLOSE CUR_GET_EDU_CESS_EXCISE;
2874 RETURN (ROUND(LN_EDU_CESS_EXCISE
2875 ,0));
2876 EXCEPTION
2877 WHEN OTHERS THEN
2878 /*SRW.MESSAGE(1275
2879 ,'CF_sh_cess_excise_input:' || SQLERRM)*/NULL;
2880 RETURN NULL;
2881 END CF_SH_CESS_EXCISE_INPUTFORMULA;
2882
2883 FUNCTION CF_SH_CESS_UTIL_PAY_GOODSFORMU(CF_CR_UTIL_INP_RM_SH_CESS IN NUMBER) RETURN NUMBER IS
2884 LN_EDU_CESS_EXCISE NUMBER;
2885 CURSOR CUR_GET_EDU_CESS_EXCISE IS
2886 SELECT
2887 NVL(SUM(DEBIT)
2888 ,0)
2889 FROM
2890 JAI_CMN_RG_OTHERS JRO,
2891 JAI_CMN_RG_23AC_II_TRXS RG23
2892 WHERE JRO.SOURCE_REGISTER_ID = RG23.REGISTER_ID
2893 AND RG23.LOCATION_ID = P_LOCATION_ID
2894 AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
2895 AND TRUNC(RG23.CREATION_DATE) >= P_START_DATE
2896 AND TRUNC(RG23.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
2897 ,SYSDATE))
2898 AND RG23.REGISTER_TYPE IN ( 'A' , 'C' )
2899 AND JRO.SOURCE_REGISTER in ( 'RG23A_P2' , 'RG23C_P2' )
2900 AND JRO.TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS );
2901 BEGIN
2902 RETURN (ROUND(NVL(LN_EDU_CESS_EXCISE
2903 ,0) - NVL(CF_CR_UTIL_INP_RM_SH_CESS
2904 ,0)
2905 ,0));
2906 EXCEPTION
2907 WHEN OTHERS THEN
2908 /*SRW.MESSAGE(1275
2909 ,'CF_sh_cess_util_pay_goods:' || SQLERRM)*/NULL;
2910 RETURN NULL;
2911 END CF_SH_CESS_UTIL_PAY_GOODSFORMU;
2912
2913 FUNCTION CF_SRVC_SH_CESS_OPNGFORMULA RETURN NUMBER IS
2914 CURSOR CUR_INVOICE_OPEN_BAL IS
2915 SELECT
2916 SUM(RECOVERED_AMOUNT)
2917 FROM
2918 JAI_RGM_TRX_REFS
2919 WHERE SOURCE = 'AP'
2920 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
2921 AND TRUNC(CREATION_DATE) < P_START_DATE
2922 AND ORGANIZATION_ID in (
2923 SELECT
2924 DISTINCT
2925 ORGANIZATION_ID
2926 FROM
2927 JAI_RGM_ORG_REGNS_V
2928 WHERE REGIME_CODE = 'SERVICE'
2929 AND REGISTRATION_TYPE = 'OTHERS'
2930 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
2931 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
2932 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
2933 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
2934 ,ORGANIZATION_ID) );
2935 CURSOR CUR_DIST_IN IS
2936 SELECT
2937 SUM(CREDIT_AMOUNT)
2938 FROM
2939 JAI_RGM_TRX_RECORDS
2940 WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
2941 AND REGIME_CODE = 'SERVICE'
2942 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
2943 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
2944 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
2945 ,ORGANIZATION_ID)
2946 AND ( NVL(TRUNC(CREATION_DATE)
2947 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
2948 ,TRUNC(SYSDATE)) );
2949 CURSOR CUR_MANUAL_IN IS
2950 SELECT
2951 SUM(CREDIT_AMOUNT)
2952 FROM
2953 JAI_RGM_TRX_RECORDS
2954 WHERE SOURCE = 'MANUAL'
2955 AND REGIME_CODE = 'SERVICE'
2956 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
2957 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
2958 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
2959 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
2960 ,ORGANIZATION_ID)
2961 AND ( NVL(TRUNC(CREATION_DATE)
2962 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
2963 ,TRUNC(SYSDATE)) );
2964 CURSOR CUR_AR_UTIL_CREDIT IS
2965 SELECT
2966 SUM(RECOVERED_AMOUNT)
2967 FROM
2968 JAI_RGM_TRX_REFS
2969 WHERE SOURCE = 'AR'
2970 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
2971 AND TRUNC(CREATION_DATE) < P_START_DATE
2972 AND ORGANIZATION_ID IN (
2973 SELECT
2974 DISTINCT
2975 ORGANIZATION_ID
2976 FROM
2977 JAI_RGM_ORG_REGNS_V
2978 WHERE REGIME_CODE = 'SERVICE'
2979 AND REGISTRATION_TYPE = 'OTHERS'
2980 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
2981 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
2982 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
2983 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
2984 ,ORGANIZATION_ID) );
2985 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
2986 SELECT
2987 NVL(SUM(DEBIT_AMOUNT)
2988 ,0)
2989 FROM
2990 JAI_RGM_TRX_RECORDS
2991 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
2992 AND REGIME_CODE = 'SERVICE'
2993 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
2994 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
2995 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
2996 ,ORGANIZATION_ID)
2997 AND ( NVL(TRUNC(CREATION_DATE)
2998 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
2999 ,TRUNC(SYSDATE)) );
3000 CURSOR CUR_MANUAL_DEBIT IS
3001 SELECT
3002 NVL(SUM(DEBIT_AMOUNT)
3003 ,0)
3004 FROM
3005 JAI_RGM_TRX_RECORDS
3006 WHERE SOURCE = 'MANUAL'
3007 AND REGIME_CODE = 'SERVICE'
3008 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
3009 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
3010 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
3011 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
3012 ,ORGANIZATION_ID)
3013 AND ( NVL(TRUNC(CREATION_DATE)
3014 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
3015 ,TRUNC(SYSDATE)) );
3016 CURSOR CUR_PAYMENT IS
3017 SELECT
3018 NVL(SUM(DEBIT_AMOUNT)
3019 ,0)
3020 FROM
3021 JAI_RGM_TRX_RECORDS
3022 WHERE SOURCE = 'MANUAL'
3023 AND REGIME_CODE = 'SERVICE'
3024 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
3025 AND SOURCE_TRX_TYPE = 'PAYMENT'
3026 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
3027 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
3028 ,ORGANIZATION_ID)
3029 AND ( NVL(TRUNC(CREATION_DATE)
3030 ,TRUNC(SYSDATE)) ) < ( NVL(P_START_DATE
3031 ,TRUNC(SYSDATE)) );
3032 LV_INV_OPEN_BAL NUMBER;
3033 LV_OPEN_DIST_BAL NUMBER;
3034 LV_AR_UTIL_CREDIT NUMBER;
3035 LV_AR_SER_DIST_OUT_DEBIT NUMBER;
3036 LV_MANUAL_BAL NUMBER;
3037 LV_MANUAL_DEBIT_BAL NUMBER;
3038 LV_MANUAL_PAYMENT NUMBER;
3039 BEGIN
3040 LV_INV_OPEN_BAL := 0;
3041 LV_OPEN_DIST_BAL := 0;
3042 LV_AR_UTIL_CREDIT := 0;
3043 LV_AR_SER_DIST_OUT_DEBIT := 0;
3044 LV_MANUAL_BAL := 0;
3045 LV_MANUAL_DEBIT_BAL := 0;
3046 LV_MANUAL_PAYMENT := 0;
3047 OPEN CUR_INVOICE_OPEN_BAL;
3048 FETCH CUR_INVOICE_OPEN_BAL
3049 INTO LV_INV_OPEN_BAL;
3050 CLOSE CUR_INVOICE_OPEN_BAL;
3051 OPEN CUR_DIST_IN;
3052 FETCH CUR_DIST_IN
3053 INTO LV_OPEN_DIST_BAL;
3054 CLOSE CUR_DIST_IN;
3055 OPEN CUR_MANUAL_IN;
3056 FETCH CUR_MANUAL_IN
3057 INTO LV_MANUAL_BAL;
3058 CLOSE CUR_MANUAL_IN;
3059 OPEN CUR_MANUAL_DEBIT;
3060 FETCH CUR_MANUAL_DEBIT
3061 INTO LV_MANUAL_DEBIT_BAL;
3062 CLOSE CUR_MANUAL_DEBIT;
3063 OPEN CUR_AR_UTIL_CREDIT;
3064 FETCH CUR_AR_UTIL_CREDIT
3065 INTO LV_AR_UTIL_CREDIT;
3066 CLOSE CUR_AR_UTIL_CREDIT;
3067 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
3068 FETCH CUR_AR_SER_DIST_OUT_DEBIT
3069 INTO LV_AR_SER_DIST_OUT_DEBIT;
3070 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
3071 OPEN CUR_PAYMENT;
3072 FETCH CUR_PAYMENT
3073 INTO LV_MANUAL_PAYMENT;
3074 CLOSE CUR_PAYMENT;
3075 RETURN ROUND((NVL(LV_OPEN_DIST_BAL
3076 ,0) + NVL(LV_INV_OPEN_BAL
3077 ,0) + NVL(LV_MANUAL_BAL
3078 ,0) - NVL(LV_AR_UTIL_CREDIT
3079 ,0) - NVL(LV_AR_SER_DIST_OUT_DEBIT
3080 ,0) - NVL(LV_MANUAL_DEBIT_BAL
3081 ,0) + NVL(LV_MANUAL_PAYMENT
3082 ,0))
3083 ,0);
3084 END CF_SRVC_SH_CESS_OPNGFORMULA;
3085
3086 FUNCTION CF_CR_UTIL_INP_RM_SH_CESSFORMU RETURN NUMBER IS
3087 CURSOR GET_RTV_CESS IS
3088 SELECT
3089 SUM(NVL(DEBIT
3090 ,0))
3091 FROM
3092 JAI_CMN_RG_OTHERS
3093 WHERE SOURCE_TYPE = 1
3094 AND TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS )
3095 AND SOURCE_REGISTER_ID in (
3096 SELECT
3097 JRG23_II.REGISTER_ID
3098 FROM
3099 JAI_CMN_RG_23AC_II_TRXS JRG23_II,
3100 JAI_CMN_RG_23AC_I_TRXS JRG23_I
3101 WHERE JRG23_II.ORGANIZATION_ID = P_ORGANIZATION_ID
3102 AND JRG23_II.LOCATION_ID = P_LOCATION_ID
3103 AND TRUNC(JRG23_II.CREATION_DATE) >= P_START_DATE
3104 AND TRUNC(JRG23_II.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
3105 ,SYSDATE))
3106 AND JRG23_I.TRANSACTION_TYPE = 'RTV'
3107 AND JRG23_II.ORGANIZATION_ID = JRG23_I.ORGANIZATION_ID
3108 AND JRG23_II.LOCATION_ID = JRG23_I.LOCATION_ID
3109 AND JRG23_II.REGISTER_ID_PART_I = JRG23_I.REGISTER_ID );
3110 CURSOR GET_CGIN_SALES_CESS IS
3111 SELECT
3112 SUM(NVL(DEBIT
3113 ,0))
3114 FROM
3115 JAI_CMN_RG_OTHERS
3116 WHERE SOURCE_TYPE = 1
3117 AND TAX_TYPE in ( TAX_TYPE_SH_CVD_EDU_CESS , TAX_TYPE_SH_EXC_EDU_CESS )
3118 AND SOURCE_REGISTER_ID in (
3119 SELECT
3120 JRG23_II.REGISTER_ID
3121 FROM
3122 JAI_CMN_RG_23AC_II_TRXS JRG23_II,
3123 JAI_CMN_RG_23AC_I_TRXS JRG23_I,
3124 JAI_INV_ITM_SETUPS JMSI
3125 WHERE JRG23_II.ORGANIZATION_ID = JRG23_I.ORGANIZATION_ID
3126 AND JRG23_II.LOCATION_ID = JRG23_I.LOCATION_ID
3127 AND JRG23_II.REGISTER_ID_PART_I = JRG23_I.REGISTER_ID
3128 AND JMSI.ORGANIZATION_ID = JRG23_II.ORGANIZATION_ID
3129 AND JMSI.ITEM_CLASS like 'CG%'
3130 AND JMSI.INVENTORY_ITEM_ID = JRG23_II.INVENTORY_ITEM_ID
3131 AND JMSI.ORGANIZATION_ID = P_ORGANIZATION_ID
3132 AND JRG23_II.ORGANIZATION_ID = P_ORGANIZATION_ID
3133 AND JRG23_II.LOCATION_ID = P_LOCATION_ID
3134 AND TRUNC(JRG23_II.CREATION_DATE) >= P_START_DATE
3135 AND TRUNC(JRG23_II.CREATION_DATE) <= TRUNC(NVL(P_END_DATE
3136 ,SYSDATE))
3137 AND JRG23_I.TRANSACTION_TYPE <> 'RTV' );
3138 LN_RTV_CESS NUMBER;
3139 LN_CGIN_SALES_CESS NUMBER;
3140 LN_CR_UTIL_INP_RM_CESS NUMBER;
3141 BEGIN
3142 OPEN GET_RTV_CESS;
3143 FETCH GET_RTV_CESS
3144 INTO LN_RTV_CESS;
3145 CLOSE GET_RTV_CESS;
3146 OPEN GET_CGIN_SALES_CESS;
3147 FETCH GET_CGIN_SALES_CESS
3148 INTO LN_CGIN_SALES_CESS;
3149 CLOSE GET_CGIN_SALES_CESS;
3150 RETURN ROUND(NVL(LN_RTV_CESS
3151 ,0) + NVL(LN_CGIN_SALES_CESS
3152 ,0));
3153 EXCEPTION
3154 WHEN OTHERS THEN
3155 /*SRW.MESSAGE(1275
3156 ,'CF_cr_util_inp_rm_sh_cessFormula:' || SQLERRM)*/NULL;
3157 RETURN NULL;
3158 END CF_CR_UTIL_INP_RM_SH_CESSFORMU;
3159
3160 FUNCTION CF_CLOSING_BLNC_SH_ECFORMULA(CF_SH_CESS_OPENING_BLNC IN NUMBER
3161 ,CF_TOT_CR_AVAILED_SH_EC IN NUMBER
3162 ,CF_SH_CESS_UTIL_PAY_GOODS IN NUMBER
3163 ,CF_CR_UTIL_INP_RM_SH_CESS IN NUMBER) RETURN NUMBER IS
3164 BEGIN
3165 RETURN ROUND(NVL(CF_SH_CESS_OPENING_BLNC
3166 ,0) + NVL(CF_TOT_CR_AVAILED_SH_EC
3167 ,0) - NVL(CF_SH_CESS_UTIL_PAY_GOODS
3168 ,0) - NVL(CF_CR_UTIL_INP_RM_SH_CESS
3169 ,0)
3170 ,0);
3171 END CF_CLOSING_BLNC_SH_ECFORMULA;
3172
3173 FUNCTION CF_CLOSING_BLNC_SH_SCFORMULA(CF_SRVC_SH_CESS_OPNG IN NUMBER
3174 ,CF_TOT_CR_AVAILED_SH_SC IN NUMBER
3175 ,CF_SH_CESS_TAX_DUTY_GOODS IN NUMBER) RETURN NUMBER IS
3176 BEGIN
3177 RETURN ROUND((NVL(CF_SRVC_SH_CESS_OPNG
3178 ,0) + NVL(CF_TOT_CR_AVAILED_SH_SC
3179 ,0) - NVL(CF_SH_CESS_TAX_DUTY_GOODS
3180 ,0))
3181 ,0);
3182 END CF_CLOSING_BLNC_SH_SCFORMULA;
3183
3184 FUNCTION CF_SH_CESS_TAX_DUTY_GOODSFORMU RETURN NUMBER IS
3185 CURSOR CUR_AR_UTIL_CREDIT IS
3186 SELECT
3187 SUM(RECOVERED_AMOUNT)
3188 FROM
3189 JAI_RGM_TRX_REFS
3190 WHERE SOURCE = 'AR'
3191 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
3192 AND ORGANIZATION_ID IN (
3193 SELECT
3194 DISTINCT
3195 ORGANIZATION_ID
3196 FROM
3197 JAI_RGM_ORG_REGNS_V
3198 WHERE REGIME_CODE = 'SERVICE'
3199 AND REGISTRATION_TYPE = 'OTHERS'
3200 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
3201 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
3202 AND ATTRIBUTE_VALUE = P_REGISTRATION_NUMBER
3203 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
3204 ,ORGANIZATION_ID) )
3205 AND ( NVL(TRUNC(CREATION_DATE)
3206 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
3207 ,SYSDATE) )
3208 AND ( NVL(P_END_DATE
3209 ,SYSDATE) );
3210 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
3211 SELECT
3212 NVL(SUM(DEBIT_AMOUNT)
3213 ,0)
3214 FROM
3215 JAI_RGM_TRX_RECORDS
3216 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
3217 AND REGIME_CODE = 'SERVICE'
3218 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
3219 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
3220 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
3221 ,ORGANIZATION_ID)
3222 AND ( NVL(TRUNC(CREATION_DATE)
3223 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
3224 ,SYSDATE) )
3225 AND ( NVL(P_END_DATE
3226 ,SYSDATE) );
3227 CURSOR CUR_MANUAL_DEBIT IS
3228 SELECT
3229 NVL(SUM(DEBIT_AMOUNT)
3230 ,0)
3231 FROM
3232 JAI_RGM_TRX_RECORDS
3233 WHERE SOURCE = 'MANUAL'
3234 AND REGIME_CODE = 'SERVICE'
3235 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
3236 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
3237 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
3238 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
3239 ,ORGANIZATION_ID)
3240 AND ( NVL(TRUNC(CREATION_DATE)
3241 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
3242 ,SYSDATE) )
3243 AND ( NVL(P_END_DATE
3244 ,SYSDATE) );
3245 CURSOR CUR_PAYMENT IS
3246 SELECT
3247 NVL(SUM(DEBIT_AMOUNT)
3248 ,0)
3249 FROM
3250 JAI_RGM_TRX_RECORDS
3251 WHERE SOURCE = 'MANUAL'
3252 AND REGIME_CODE = 'SERVICE'
3253 AND TAX_TYPE = TAX_TYPE_SH_SERVICE_EDU_CESS
3254 AND SOURCE_TRX_TYPE = 'PAYMENT'
3255 AND REGIME_PRIMARY_REGNO = P_REGISTRATION_NUMBER
3256 AND ORGANIZATION_ID = NVL(P_OPERATING_UNIT
3257 ,ORGANIZATION_ID)
3258 AND ( NVL(TRUNC(CREATION_DATE)
3259 ,SYSDATE) ) BETWEEN ( NVL(P_START_DATE
3260 ,SYSDATE) )
3261 AND ( NVL(P_END_DATE
3262 ,SYSDATE) );
3263 LN_AR_UTIL_CREDIT NUMBER;
3264 LN_AR_SER_DIST_OUT_DEBIT NUMBER;
3265 LV_MANUAL_DEBIT NUMBER;
3266 LV_PAYMENT NUMBER;
3267 BEGIN
3268 LN_AR_UTIL_CREDIT := 0;
3269 LN_AR_SER_DIST_OUT_DEBIT := 0;
3270 LV_MANUAL_DEBIT := 0;
3271 LV_PAYMENT := 0;
3272 OPEN CUR_AR_UTIL_CREDIT;
3273 FETCH CUR_AR_UTIL_CREDIT
3274 INTO LN_AR_UTIL_CREDIT;
3275 CLOSE CUR_AR_UTIL_CREDIT;
3276 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
3277 FETCH CUR_AR_SER_DIST_OUT_DEBIT
3278 INTO LN_AR_SER_DIST_OUT_DEBIT;
3279 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
3280 OPEN CUR_MANUAL_DEBIT;
3281 FETCH CUR_MANUAL_DEBIT
3282 INTO LV_MANUAL_DEBIT;
3283 CLOSE CUR_MANUAL_DEBIT;
3284 OPEN CUR_PAYMENT;
3285 FETCH CUR_PAYMENT
3286 INTO LV_PAYMENT;
3287 CLOSE CUR_PAYMENT;
3288 RETURN ROUND((NVL(LN_AR_UTIL_CREDIT
3289 ,0) + NVL(LN_AR_SER_DIST_OUT_DEBIT
3290 ,0) + NVL(LV_MANUAL_DEBIT
3291 ,0) - NVL(LV_PAYMENT
3292 ,0))
3293 ,0);
3294 END CF_SH_CESS_TAX_DUTY_GOODSFORMU;
3295
3296 FUNCTION CF_TOT_CR_AVAILED_SH_SCFORMULA(CF_SRVC_SH_CESS_OPNG IN NUMBER) RETURN NUMBER IS
3297 BEGIN
3298 RETURN ROUND((NVL(CF_SRVC_SH_CESS_OPNG
3299 ,0) + NVL(CP_SH_CESS_CREDIT_AVLD
3300 ,0))
3301 ,0);
3302 END CF_TOT_CR_AVAILED_SH_SCFORMULA;
3303
3304 FUNCTION CF_OTHER_SH_ACC_CREDITFORMULA RETURN NUMBER IS
3305 LN_CR_OTHER_ED JAI_CMN_RG_23AC_II_TRXS.CR_OTHER_ED%TYPE;
3306 CURSOR CUR_GET_AMOUNT IS
3307 SELECT
3308 SUM(NVL(DEBIT
3309 ,0))
3310 FROM
3311 JAI_CMN_RG_OTHERS
3312 WHERE TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS )
3313 AND SOURCE_TYPE = 1
3314 AND SOURCE_REGISTER_ID in (
3315 SELECT
3316 REGISTER_ID
3317 FROM
3318 JAI_CMN_RG_23AC_II_TRXS
3319 WHERE LOCATION_ID = P_LOCATION_ID
3320 AND ORGANIZATION_ID = P_ORGANIZATION_ID
3321 AND TRUNC(CREATION_DATE) >= P_START_DATE
3322 AND TRUNC(CREATION_DATE) <= TRUNC(NVL(P_END_DATE
3323 ,SYSDATE)) );
3324 BEGIN
3325 OPEN CUR_GET_AMOUNT;
3326 FETCH CUR_GET_AMOUNT
3327 INTO LN_CR_OTHER_ED;
3328 CLOSE CUR_GET_AMOUNT;
3329 RETURN ROUND(LN_CR_OTHER_ED
3330 ,0);
3331 EXCEPTION
3332 WHEN OTHERS THEN
3333 /*SRW.MESSAGE(1275
3334 ,'CF_other_acc_credit:' || SQLERRM)*/NULL;
3335 RETURN NULL;
3336 END CF_OTHER_SH_ACC_CREDITFORMULA;
3337
3338 FUNCTION CF_OTHER_SH_ACC_CURRENTFORMULA RETURN NUMBER IS
3339 CURSOR CUR_GET_ADDL_AMOUNT IS
3340 SELECT
3341 SUM(NVL(CREDIT
3342 ,0))
3343 FROM
3344 JAI_CMN_RG_OTHERS
3345 WHERE SOURCE_TYPE = 2
3346 AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS )
3347 AND SOURCE_REGISTER_ID in (
3348 SELECT
3349 REGISTER_ID
3350 FROM
3351 JAI_CMN_RG_PLA_TRXS
3352 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
3353 AND LOCATION_ID = P_LOCATION_ID
3354 AND CREATION_DATE >= P_START_DATE
3355 AND CREATION_DATE <= TRUNC(NVL(P_END_DATE
3356 ,SYSDATE))
3357 AND TRANSACTION_SOURCE_NUM = 91 );
3358 LN_ADDL_DUTY_AMOUNT NUMBER;
3359 BEGIN
3360 OPEN CUR_GET_ADDL_AMOUNT;
3361 FETCH CUR_GET_ADDL_AMOUNT
3362 INTO LN_ADDL_DUTY_AMOUNT;
3363 CLOSE CUR_GET_ADDL_AMOUNT;
3364 RETURN ROUND(LN_ADDL_DUTY_AMOUNT
3365 ,0);
3366 EXCEPTION
3367 WHEN OTHERS THEN
3368 /*SRW.MESSAGE(1275
3369 ,'CF_other_acc_current:' || SQLERRM)*/NULL;
3370 RETURN NULL;
3371 END CF_OTHER_SH_ACC_CURRENTFORMULA;
3372
3373 FUNCTION CF_SH_OTHER_TOTALFORMULA(CF_OTHER_SH_ACC_CURRENT IN NUMBER
3374 ,CF_OTHER_SH_ACC_CREDIT IN NUMBER) RETURN NUMBER IS
3375 BEGIN
3376 RETURN ROUND((NVL(CF_OTHER_SH_ACC_CURRENT
3377 ,0) + NVL(CF_OTHER_SH_ACC_CREDIT
3378 ,0))
3379 ,0);
3380 END CF_SH_OTHER_TOTALFORMULA;
3381
3382 FUNCTION CF_SH_OTHER_DUTIESFORMULA(INVENTORY_ITEM_ID IN NUMBER
3383 ,CETSH IN VARCHAR2
3384 ,UNITS IN VARCHAR2
3385 ,EXCISE_DUTY_RATE IN NUMBER
3386 ,REGISTER IN VARCHAR2) RETURN NUMBER IS
3387 LN_DUTY_PAYABLE NUMBER;
3388 LN_PLA_DUTY NUMBER;
3389 LN_RG23_DUTY NUMBER;
3390 CURSOR CUR_OTHER_DUTIES_PLA IS
3391 SELECT
3392 NVL(SUM(DEBIT)
3393 ,0)
3394 FROM
3395 JAI_CMN_RG_OTHERS
3396 WHERE SOURCE_REGISTER_ID IN (
3397 SELECT
3398 REGISTER_ID_PART_II
3399 FROM
3400 JAI_CMN_RG_I_TRXS JRGI,
3401 JAI_INV_ITM_SETUPS ITEMS
3402 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
3403 OR ITEMS.ITEM_TARIFF = CETSH )
3404 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
3405 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
3406 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
3407 AND NVL(JRGI.PRIMARY_UOM_CODE
3408 ,'XYZ') = NVL(UNITS
3409 ,'XYZ')
3410 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
3411 ,0)
3412 ,-999.95) = NVL(cf_sh_other_dutiesformula.EXCISE_DUTY_RATE
3413 ,-999.95)
3414 AND JRGI.LOCATION_ID = P_LOCATION_ID
3415 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
3416 AND TRUNC(P_END_DATE)
3417 AND JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
3418 AND PAYMENT_REGISTER = 'PLA' )
3419 AND SOURCE_TYPE = 2
3420 AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS );
3421 CURSOR CUR_OTHER_DUTIES_RG23 IS
3422 SELECT
3423 NVL(SUM(DEBIT)
3424 ,0)
3425 FROM
3426 JAI_CMN_RG_OTHERS
3427 WHERE SOURCE_REGISTER_ID IN (
3428 SELECT
3429 REGISTER_ID_PART_II
3430 FROM
3431 JAI_CMN_RG_I_TRXS JRGI,
3432 JAI_INV_ITM_SETUPS ITEMS
3433 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
3434 OR ITEMS.ITEM_TARIFF = CETSH )
3435 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
3436 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
3437 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
3438 AND NVL(JRGI.PRIMARY_UOM_CODE
3439 ,'XYZ') = NVL(UNITS
3440 ,'XYZ')
3441 AND NVL(ROUND(JRGI.EXCISE_DUTY_RATE
3442 ,0)
3443 ,-999.95) = NVL(cf_sh_other_dutiesformula.EXCISE_DUTY_RATE
3444 ,-999.95)
3445 AND JRGI.LOCATION_ID = P_LOCATION_ID
3446 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
3447 AND TRUNC(P_END_DATE)
3448 AND JRGI.TRANSACTION_TYPE in ( 'I' , 'IA' , 'PI' , 'IOI' )
3449 AND PAYMENT_REGISTER IN ( 'RG23A' , 'RG23C' ) )
3450 AND SOURCE_TYPE = 1
3451 AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS );
3452 CURSOR CUR_OTHER_DUTIES_PLA_PART_I IS
3453 SELECT
3454 NVL(SUM(DEBIT)
3455 ,0)
3456 FROM
3457 JAI_CMN_RG_OTHERS
3458 WHERE SOURCE_REGISTER_ID IN (
3459 SELECT
3460 REGISTER_ID_PART_II
3461 FROM
3462 JAI_CMN_RG_23AC_I_TRXS JRGI,
3463 JAI_INV_ITM_SETUPS ITEMS
3464 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
3465 OR ITEMS.ITEM_TARIFF = CETSH )
3466 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
3467 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
3468 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
3469 AND NVL(JRGI.PRIMARY_UOM_CODE
3470 ,'XYZ') = NVL(UNITS
3471 ,'XYZ')
3472 AND JRGI.LOCATION_ID = P_LOCATION_ID
3473 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
3474 AND TRUNC(P_END_DATE)
3475 AND JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
3476 AND REGISTER_TYPE = 'PLA' )
3477 AND SOURCE_TYPE = 2
3478 AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS );
3479 CURSOR CUR_OTHER_DUTIES_RG23_PART_I IS
3480 SELECT
3481 NVL(SUM(DEBIT)
3482 ,0)
3483 FROM
3484 JAI_CMN_RG_OTHERS
3485 WHERE SOURCE_REGISTER_ID IN (
3486 SELECT
3487 REGISTER_ID_PART_II
3488 FROM
3489 JAI_CMN_RG_23AC_I_TRXS JRGI,
3490 JAI_INV_ITM_SETUPS ITEMS
3491 WHERE ( JRGI.INVENTORY_ITEM_ID = cf_sh_other_dutiesformula.INVENTORY_ITEM_ID
3492 OR ITEMS.ITEM_TARIFF = CETSH )
3493 AND ITEMS.INVENTORY_ITEM_ID = JRGI.INVENTORY_ITEM_ID
3494 AND JRGI.ORGANIZATION_ID = P_ORGANIZATION_ID
3495 AND ITEMS.ORGANIZATION_ID = JRGI.ORGANIZATION_ID
3496 AND NVL(JRGI.PRIMARY_UOM_CODE
3497 ,'XYZ') = NVL(UNITS
3498 ,'XYZ')
3499 AND JRGI.LOCATION_ID = P_LOCATION_ID
3500 AND TRUNC(JRGI.CREATION_DATE) between TRUNC(P_START_DATE)
3501 AND TRUNC(P_END_DATE)
3502 AND JRGI.TRANSACTION_TYPE in ( 'RTV' , 'I' , 'IA' , 'IOI' , 'PI' )
3503 AND REGISTER_TYPE IN ( 'A' , 'C' ) )
3504 AND SOURCE_TYPE = 1
3505 AND TAX_TYPE in ( TAX_TYPE_SH_EXC_EDU_CESS , TAX_TYPE_SH_CVD_EDU_CESS );
3506 LN_PLA_DUTY_PART_I NUMBER;
3507 LN_RG23_DUTY_PART_I NUMBER;
3508 BEGIN
3509 IF REGISTER = 'RG1' THEN
3510 /*SRW.MESSAGE(1275
3511 ,'1')*/NULL;
3512 OPEN CUR_OTHER_DUTIES_PLA;
3513 FETCH CUR_OTHER_DUTIES_PLA
3514 INTO LN_PLA_DUTY;
3515 CLOSE CUR_OTHER_DUTIES_PLA;
3516 OPEN CUR_OTHER_DUTIES_RG23;
3517 FETCH CUR_OTHER_DUTIES_RG23
3518 INTO LN_RG23_DUTY;
3519 CLOSE CUR_OTHER_DUTIES_RG23;
3520 LN_DUTY_PAYABLE := ROUND((NVL(LN_PLA_DUTY
3521 ,0) + NVL(LN_RG23_DUTY
3522 ,0))
3523 ,0);
3524 /*SRW.MESSAGE(1275
3525 ,'1a')*/NULL;
3526 RETURN LN_DUTY_PAYABLE;
3527 ELSIF REGISTER = 'RG23_PART_I' THEN
3528 /*SRW.MESSAGE(1275
3529 ,'2')*/NULL;
3530 OPEN CUR_OTHER_DUTIES_PLA_PART_I;
3531 FETCH CUR_OTHER_DUTIES_PLA_PART_I
3532 INTO LN_PLA_DUTY_PART_I;
3533 CLOSE CUR_OTHER_DUTIES_PLA_PART_I;
3534 OPEN CUR_OTHER_DUTIES_RG23_PART_I;
3535 FETCH CUR_OTHER_DUTIES_RG23_PART_I
3536 INTO LN_RG23_DUTY_PART_I;
3537 CLOSE CUR_OTHER_DUTIES_RG23_PART_I;
3538 LN_DUTY_PAYABLE := ROUND((NVL(LN_PLA_DUTY_PART_I
3539 ,0) + NVL(LN_RG23_DUTY_PART_I
3540 ,0))
3541 ,0);
3542 /*SRW.MESSAGE(1275
3543 ,'2a')*/NULL;
3544 RETURN LN_DUTY_PAYABLE;
3545 END IF;
3546 EXCEPTION
3547 WHEN OTHERS THEN
3548 /*SRW.MESSAGE(1275
3549 ,'CF_OTHER_duties' || SQLERRM)*/NULL;
3550 RETURN NULL;
3551 END CF_SH_OTHER_DUTIESFORMULA;
3552
3553 FUNCTION CP_AED_DUTY_PAYABLE_P RETURN NUMBER IS
3554 BEGIN
3555 RETURN CP_AED_DUTY_PAYABLE;
3556 END CP_AED_DUTY_PAYABLE_P;
3557
3558 FUNCTION CP_CRDT_IMPORT_CAP_GOODS_P RETURN NUMBER IS
3559 BEGIN
3560 RETURN CP_CRDT_IMPORT_CAP_GOODS;
3561 END CP_CRDT_IMPORT_CAP_GOODS_P;
3562
3563 FUNCTION CP_CRDT_IMPORT_INPUT_P RETURN NUMBER IS
3564 BEGIN
3565 RETURN CP_CRDT_IMPORT_INPUT;
3566 END CP_CRDT_IMPORT_INPUT_P;
3567
3568 FUNCTION CP_CESS_CREDIT_AVLD_P RETURN NUMBER IS
3569 BEGIN
3570 RETURN CP_CESS_CREDIT_AVLD;
3571 END CP_CESS_CREDIT_AVLD_P;
3572
3573 FUNCTION CP_SH_CESS_CREDIT_AVLD_P RETURN NUMBER IS
3574 BEGIN
3575 RETURN CP_SH_CESS_CREDIT_AVLD;
3576 END CP_SH_CESS_CREDIT_AVLD_P;
3577
3578 FUNCTION CP_AED_ACC_CURRENT_P RETURN NUMBER IS
3579 BEGIN
3580 RETURN CP_AED_ACC_CURRENT;
3581 END CP_AED_ACC_CURRENT_P;
3582
3583 END JA_JAINER1_XMLP_PKG;
3584
3585