[Home] [Help]
PACKAGE BODY: APPS.JA_JAINST3_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINST3_XMLP_PKG AS
2 /* $Header: JAINST3B.pls 120.1 2007/12/25 16:29:08 dwkrishn noship $ */
3 FUNCTION CF_REALISED_AMOUNTFORMULA(TAX_AMOUNT IN NUMBER
4 ,RECOVERED_AMOUNT IN NUMBER
5 ,CHARGED_BILLED IN NUMBER) RETURN NUMBER IS
6 BEGIN
7 IF TAX_AMOUNT <> 0 THEN
8 RETURN ROUND(((RECOVERED_AMOUNT * CHARGED_BILLED) / NVL(TAX_AMOUNT
9 ,1))
10 ,3);
11 ELSE
12 RETURN NULL;
13 END IF;
14 END CF_REALISED_AMOUNTFORMULA;
15 FUNCTION CF_1FORMULA RETURN CHAR IS
16 BEGIN
17 IF TO_CHAR(PRM_FROMDT
18 ,'MON') in ('APR','MAY','JUN','JUL','AUG','SEP') THEN
19 RETURN '*';
20 ELSE
21 RETURN '';
22 END IF;
23 END CF_1FORMULA;
24 FUNCTION CF_2FORMULA RETURN CHAR IS
25 BEGIN
26 IF TO_CHAR(PRM_FROMDT
27 ,'MON') in ('OCT','NOV','DEC','JAN','FEB','MAR') THEN
28 RETURN '*';
29 ELSE
30 RETURN '';
31 END IF;
32 END CF_2FORMULA;
36 CURSOR C_SERVICE IS
33 FUNCTION CF_SERVICEFORMULA(MONTH IN VARCHAR2
34 ,YEAR IN VARCHAR2) RETURN NUMBER IS
35 LV_MON_YEAR VARCHAR2(8);
37 SELECT
38 SUM(DECODE(TAX_TYPE
39 ,'SERVICE_EDUCATION_CESS'
40 ,TRXRECS.CREDIT_AMOUNT
41 ,0)) SERVICE_CESS_AMOUNT,
42 SUM(DECODE(TAX_TYPE
43 ,'Service'
44 ,TRXRECS.CREDIT_AMOUNT
45 ,0)) SERVICE_AMOUNT
46 FROM
47 JAI_RGM_TRX_RECORDS TRXRECS
48 WHERE TRXRECS.SOURCE in ( 'AP' , 'MANUAL' )
49 AND TRXRECS.SOURCE_TRX_TYPE = 'PAYMENT'
50 AND TRXRECS.REGIME_CODE = 'SERVICE'
51 AND TRUNC(TRXRECS.TRANSACTION_DATE) between PRM_FROMDT
52 AND PRM_TODT
53 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
54 ,TRXRECS.ORGANIZATION_ID)
55 AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
56 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
57 ,'MON-YYYY') = LV_MON_YEAR
58 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
59 ,SERVICE_TYPE_CODE);
60 LN_SERVICE_AMOUNT JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
61 BEGIN
62 LV_MON_YEAR := MONTH || '-' || YEAR;
63 OPEN C_SERVICE;
64 FETCH C_SERVICE
65 INTO CP_SERVICE_CESS_AMOUNT,LN_SERVICE_AMOUNT;
66 CLOSE C_SERVICE;
67 RETURN LN_SERVICE_AMOUNT;
68 END CF_SERVICEFORMULA;
69 FUNCTION CF_INVOICE_NUMFORMULA(MONTH IN VARCHAR2
70 ,YEAR IN VARCHAR2) RETURN VARCHAR2 IS
71 LV_MON_YEAR VARCHAR2(8);
72 CURSOR C_INVOICE IS
73 SELECT
74 INVC.INVOICE_NUM INVOICE_NUM,
75 INVC.INVOICE_DATE INVOICE_DATE
76 FROM
77 AP_INVOICE_PAYMENTS_ALL INVCPAY,
78 AP_INVOICES_ALL INVC,
79 PO_VENDOR_SITES_ALL VENDSITES,
80 PO_VENDORS VENDS
81 WHERE INVCPAY.INVOICE_ID = INVC.INVOICE_ID
82 AND INVC.VENDOR_ID = VENDSITES.VENDOR_ID
83 AND VENDSITES.VENDOR_ID = VENDS.VENDOR_ID
84 AND INVCPAY.CREATION_DATE BETWEEN PRM_FROMDT
85 AND PRM_TODT
86 AND VENDSITES.ORG_ID = NVL(PRM_ORG_ID
87 ,VENDSITES.ORG_ID)
88 AND VENDS.VENDOR_TYPE_LOOKUP_CODE = 'Service Tax Authorities'
89 AND TO_CHAR(INVCPAY.CREATION_DATE
90 ,'MON-YYYY') = LV_MON_YEAR
91 AND ROWNUM = 1;
92 LV_INVOICE_NUM AP_INVOICES_ALL.INVOICE_NUM%TYPE;
93 BEGIN
94 LV_MON_YEAR := MONTH || '-' || YEAR;
95 OPEN C_INVOICE;
96 FETCH C_INVOICE
97 INTO LV_INVOICE_NUM,CP_INVOICE_DATE;
98 CLOSE C_INVOICE;
99 RETURN LV_INVOICE_NUM;
100 END CF_INVOICE_NUMFORMULA;
101 FUNCTION CF_ST_SERVICE_AMOUNTFORMULA(MONTH IN VARCHAR2
102 ,YEAR IN VARCHAR2) RETURN NUMBER IS
103 LV_MON_YEAR VARCHAR2(8);
104 CURSOR C_ST_SERVICE IS
105 SELECT
106 SUM(DECODE(TAX_TYPE
107 ,'SERVICE_EDUCATION_CESS'
108 ,TRXRECS.CREDIT_AMOUNT
109 ,0)) SERVICE_CESS_ST_AMOUNT,
110 SUM(DECODE(TAX_TYPE
111 ,'Service'
112 ,TRXRECS.CREDIT_AMOUNT
113 ,0)) SERVICE_ST_AMOUNT
114 FROM
115 JAI_RGM_TRX_RECORDS TRXRECS
116 WHERE ( TRXRECS.SOURCE in ( 'AP' , 'SERVICE_DISTRIBUTE_IN' )
117 OR TRXRECS.SOURCE in ( 'MANUAL' )
118 AND TRXRECS.SOURCE_TRX_TYPE in ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' ) )
119 AND TRXRECS.REGIME_CODE = 'SERVICE'
120 AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
121 AND TRUNC(PRM_TODT)
122 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
123 ,TRXRECS.ORGANIZATION_ID)
124 AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
125 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
126 ,'MON-YYYY') = LV_MON_YEAR
127 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
128 ,SERVICE_TYPE_CODE);
129 LN_SERVICE_ST_AMOUNT JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
130 BEGIN
131 LV_MON_YEAR := MONTH || '-' || YEAR;
132 OPEN C_ST_SERVICE;
133 FETCH C_ST_SERVICE
134 INTO CP_ST_SERVICE_CESS_AMOUNT,LN_SERVICE_ST_AMOUNT;
135 CLOSE C_ST_SERVICE;
136 RETURN LN_SERVICE_ST_AMOUNT;
137 END CF_ST_SERVICE_AMOUNTFORMULA;
138 FUNCTION CF_RETURN_NULL RETURN CHAR IS
139 BEGIN
140 RETURN ' ';
141 END CF_RETURN_NULL;
142 FUNCTION CF_OPENING_BALANCEFORMULA(MONTH1 IN VARCHAR2
143 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
144 LV_DATE VARCHAR2(11);
145 CURSOR CUR_INVOICE_OPEN_BAL IS
146 SELECT
147 SUM(RECOVERED_AMOUNT)
148 FROM
149 JAI_RGM_TRX_REFS
150 WHERE SOURCE = 'AP'
151 AND TAX_TYPE in ( 'Service' )
152 AND TRUNC(CREATION_DATE) < LV_DATE
153 AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
154 AND ORGANIZATION_ID in (
155 SELECT
156 DISTINCT
157 ORGANIZATION_ID
158 FROM
159 JAI_RGM_ORG_REGNS_V
160 WHERE REGIME_CODE = 'SERVICE'
161 AND REGISTRATION_TYPE = 'OTHERS'
162 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
163 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
164 AND ATTRIBUTE_VALUE = PRM_RGM_PRIM_REGNO );
165 CURSOR CUR_DIST_IN IS
166 SELECT
167 SUM(CREDIT_AMOUNT)
168 FROM
169 JAI_RGM_TRX_RECORDS
170 WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
174 AND ( NVL(TRUNC(CREATION_DATE)
171 AND REGIME_CODE = 'SERVICE'
172 AND TAX_TYPE IN ( 'Service' )
173 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
175 ,TRUNC(SYSDATE)) ) < LV_DATE
176 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
177 ,ORGANIZATION_ID)
178 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
179 ,SERVICE_TYPE_CODE);
180 CURSOR CUR_MANUAL_IN IS
181 SELECT
182 SUM(CREDIT_AMOUNT)
183 FROM
184 JAI_RGM_TRX_RECORDS
185 WHERE SOURCE = 'MANUAL'
186 AND REGIME_CODE = 'SERVICE'
187 AND TAX_TYPE IN ( 'Service' )
188 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
189 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
190 AND ( NVL(TRUNC(CREATION_DATE)
191 ,TRUNC(SYSDATE)) ) < LV_DATE
192 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
193 ,ORGANIZATION_ID)
194 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
195 ,SERVICE_TYPE_CODE);
196 CURSOR CUR_AR_UTIL_CREDIT IS
197 SELECT
198 SUM(RECOVERED_AMOUNT)
199 FROM
200 JAI_RGM_TRX_REFS
201 WHERE SOURCE = 'AR'
202 AND TAX_TYPE in ( 'Service' )
203 AND TRUNC(CREATION_DATE) < LV_DATE
204 AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
205 AND ORGANIZATION_ID IN (
206 SELECT
207 DISTINCT
208 ORGANIZATION_ID
209 FROM
210 JAI_RGM_ORG_REGNS_V
211 WHERE REGIME_CODE = 'SERVICE'
212 AND REGISTRATION_TYPE = 'OTHERS'
213 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
214 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
215 AND ATTRIBUTE_VALUE = PRM_RGM_PRIM_REGNO );
216 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
217 SELECT
218 NVL(SUM(DEBIT_AMOUNT)
219 ,0)
220 FROM
221 JAI_RGM_TRX_RECORDS
222 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
223 AND REGIME_CODE = 'SERVICE'
224 AND TAX_TYPE IN ( 'Service' )
225 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
226 AND ( NVL(TRUNC(CREATION_DATE)
227 ,TRUNC(SYSDATE)) ) < LV_DATE
228 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
229 ,ORGANIZATION_ID)
230 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
231 ,SERVICE_TYPE_CODE);
232 CURSOR CUR_MANUAL_DEBIT IS
233 SELECT
234 NVL(SUM(DEBIT_AMOUNT)
235 ,0)
236 FROM
237 JAI_RGM_TRX_RECORDS
238 WHERE SOURCE = 'MANUAL'
239 AND REGIME_CODE = 'SERVICE'
240 AND TAX_TYPE IN ( 'Service' )
241 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
242 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
243 AND ( NVL(TRUNC(CREATION_DATE)
244 ,TRUNC(SYSDATE)) ) < LV_DATE
245 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
246 ,ORGANIZATION_ID)
247 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
248 ,SERVICE_TYPE_CODE);
249 CURSOR CUR_PAYMENT IS
250 SELECT
251 NVL(SUM(DEBIT_AMOUNT)
252 ,0)
253 FROM
254 JAI_RGM_TRX_RECORDS
255 WHERE SOURCE = 'MANUAL'
256 AND REGIME_CODE = 'SERVICE'
257 AND TAX_TYPE IN ( 'Service' )
258 AND SOURCE_TRX_TYPE = 'PAYMENT'
259 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
260 AND ( NVL(TRUNC(CREATION_DATE)
261 ,TRUNC(SYSDATE)) ) < LV_DATE
262 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
263 ,ORGANIZATION_ID)
264 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
265 ,SERVICE_TYPE_CODE);
266 LV_INV_OPEN_BAL NUMBER := 0;
267 LV_OPEN_DIST_BAL NUMBER := 0;
268 LV_AR_UTIL_CREDIT NUMBER := 0;
269 LV_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
270 LV_MANUAL_BAL NUMBER := 0;
271 LV_MANUAL_DEBIT_BAL NUMBER := 0;
272 LV_MANUAL_PAYMENT NUMBER := 0;
273 BEGIN
274 LV_DATE := '01' || '-' || MONTH1 || '-' || YEAR1;
275 OPEN CUR_INVOICE_OPEN_BAL;
276 FETCH CUR_INVOICE_OPEN_BAL
277 INTO LV_INV_OPEN_BAL;
278 CLOSE CUR_INVOICE_OPEN_BAL;
279 OPEN CUR_DIST_IN;
280 FETCH CUR_DIST_IN
281 INTO LV_OPEN_DIST_BAL;
282 CLOSE CUR_DIST_IN;
283 OPEN CUR_MANUAL_IN;
284 FETCH CUR_MANUAL_IN
285 INTO LV_MANUAL_BAL;
286 CLOSE CUR_MANUAL_IN;
287 OPEN CUR_MANUAL_DEBIT;
288 FETCH CUR_MANUAL_DEBIT
289 INTO LV_MANUAL_DEBIT_BAL;
290 CLOSE CUR_MANUAL_DEBIT;
291 OPEN CUR_AR_UTIL_CREDIT;
292 FETCH CUR_AR_UTIL_CREDIT
293 INTO LV_AR_UTIL_CREDIT;
294 CLOSE CUR_AR_UTIL_CREDIT;
295 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
296 FETCH CUR_AR_SER_DIST_OUT_DEBIT
297 INTO LV_AR_SER_DIST_OUT_DEBIT;
298 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
299 OPEN CUR_PAYMENT;
300 FETCH CUR_PAYMENT
301 INTO LV_MANUAL_PAYMENT;
302 CLOSE CUR_PAYMENT;
303 RETURN (NVL(LV_OPEN_DIST_BAL
304 ,0) + NVL(LV_INV_OPEN_BAL
305 ,0) + NVL(LV_MANUAL_BAL
306 ,0) - NVL(LV_AR_UTIL_CREDIT
307 ,0) - NVL(LV_AR_SER_DIST_OUT_DEBIT
308 ,0) - NVL(LV_MANUAL_DEBIT_BAL
309 ,0) + NVL(LV_MANUAL_PAYMENT
310 ,0));
311 END CF_OPENING_BALANCEFORMULA;
312 FUNCTION CF_CREDIT_AVAILEDFORMULA(MONTH1 IN VARCHAR2
313 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
314 LV_DATE VARCHAR2(8);
315 CURSOR C_SERV_CREDIT(CPV_ITEM_CLASS IN VARCHAR2) IS
316 SELECT
317 SUM(TRXRECS.CREDIT_AMOUNT) SERVICE_CREDIT
318 FROM
322 JAI_CMN_VENDOR_SITES JPVS,
319 JAI_RGM_TRX_RECORDS TRXRECS,
320 JAI_RGM_TRX_REFS TRXREFS,
321 AP_INVOICES_ALL APA,
323 PO_VENDOR_SITES_ALL PVSA,
324 JAI_INV_ITM_SETUPS JMSI
325 WHERE TRXREFS.REFERENCE_ID = TRXRECS.REFERENCE_ID
326 AND APA.CANCELLED_DATE IS NULL
327 AND APA.CANCELLED_BY IS NULL
328 AND APA.CANCELLED_AMOUNT IS NULL
329 AND JPVS.VENDOR_ID = APA.VENDOR_ID
330 AND JPVS.VENDOR_SITE_ID = APA.VENDOR_SITE_ID
331 AND APA.VENDOR_ID = PVSA.VENDOR_ID
332 AND APA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID
333 AND APA.INVOICE_ID = TRXREFS.INVOICE_ID
334 AND TRXREFS.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
335 AND TRXREFS.ITEM_ID = JMSI.INVENTORY_ITEM_ID
336 AND JMSI.ITEM_CLASS = CPV_ITEM_CLASS
337 AND TRXRECS.SOURCE in ( 'AP' )
338 AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
339 AND TRXRECS.REGIME_CODE = 'SERVICE'
340 AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
341 AND TRUNC(PRM_TODT)
342 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
343 ,'MON-YYYY') = LV_DATE
344 AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
345 ,TRXRECS.SERVICE_TYPE_CODE)
346 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
347 ,TRXRECS.ORGANIZATION_ID)
348 AND TRXREFS.TAX_TYPE = 'Service';
349 CURSOR C_SERV_CREDIT_1(CPV_ITEM_CLASS IN VARCHAR2) IS
350 SELECT
351 SUM(JRTR.CREDIT_AMOUNT) SERVICE_CREDIT
352 FROM
353 JAI_RGM_TRX_RECORDS JRTR,
354 JAI_RGM_MANUAL_TRXS JMT,
355 PO_VENDOR_SITES_ALL PVSA
356 WHERE JRTR.SOURCE = 'MANUAL'
357 AND JRTR.REGIME_CODE = 'SERVICE'
358 AND JRTR.SOURCE_DOCUMENT_ID = JMT.TRANSACTION_NUMBER
359 AND PVSA.VENDOR_ID = JMT.PARTY_ID
360 AND PVSA.VENDOR_SITE_ID = JMT.PARTY_SITE_ID
361 AND JMT.ITEM_CLASS = CPV_ITEM_CLASS
362 AND JRTR.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
363 AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
364 AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
365 AND TRUNC(PRM_TODT)
366 AND TO_CHAR(JRTR.TRANSACTION_DATE
367 ,'MON-YYYY') = LV_DATE
368 AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
369 ,JRTR.SERVICE_TYPE_CODE)
370 AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
371 ,JRTR.ORGANIZATION_ID)
372 AND JRTR.TAX_TYPE = 'Service';
373 LN_SERV_CREDIT_RMIN JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
374 LN_SERV_CREDIT_RM JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
375 LN_SERV_CREDIT_CGIN JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
376 LN_SERV_CREDIT_FG JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
377 BEGIN
378 LV_DATE := MONTH1 || '-' || YEAR1;
379 OPEN C_SERV_CREDIT('RMIN');
380 FETCH C_SERV_CREDIT
381 INTO LN_SERV_CREDIT_RMIN;
382 CLOSE C_SERV_CREDIT;
383 OPEN C_SERV_CREDIT_1('RM');
384 FETCH C_SERV_CREDIT_1
385 INTO LN_SERV_CREDIT_RM;
386 CLOSE C_SERV_CREDIT_1;
387 OPEN C_SERV_CREDIT('CGIN');
388 FETCH C_SERV_CREDIT
389 INTO LN_SERV_CREDIT_CGIN;
390 CLOSE C_SERV_CREDIT;
391 OPEN C_SERV_CREDIT_1('CG');
392 FETCH C_SERV_CREDIT_1
393 INTO LN_SERV_CREDIT_FG;
394 CLOSE C_SERV_CREDIT_1;
395 CP_CREDIT_AVAILED := NVL(LN_SERV_CREDIT_CGIN
396 ,0) + NVL(LN_SERV_CREDIT_FG
397 ,0);
398 RETURN NVL(LN_SERV_CREDIT_RMIN
399 ,0) + NVL(LN_SERV_CREDIT_RM
400 ,0);
401 END CF_CREDIT_AVAILEDFORMULA;
402 FUNCTION CF_CREDIT_RECEIVEDFORMULA(MONTH1 IN VARCHAR2
403 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
404 LV_DATE VARCHAR2(8);
405 CURSOR C_CREDIT_RECEIVED IS
406 SELECT
407 SUM(JRTR.CREDIT_AMOUNT) SERVICE_CREDIT
408 FROM
409 JAI_RGM_TRX_RECORDS JRTR
410 WHERE JRTR.SOURCE = 'SERVICE_DISTRIBUTE_IN'
411 AND JRTR.REGIME_CODE = 'SERVICE'
412 AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
413 AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
414 AND TRUNC(PRM_TODT)
415 AND TO_CHAR(JRTR.TRANSACTION_DATE
416 ,'MON-YYYY') = LV_DATE
417 AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
418 ,JRTR.SERVICE_TYPE_CODE)
419 AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
420 ,JRTR.ORGANIZATION_ID)
421 AND JRTR.TAX_TYPE = 'Service';
422 LN_SERV_CREDIT JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
423 BEGIN
424 LV_DATE := MONTH1 || '-' || YEAR1;
425 OPEN C_CREDIT_RECEIVED;
426 FETCH C_CREDIT_RECEIVED
427 INTO LN_SERV_CREDIT;
428 CLOSE C_CREDIT_RECEIVED;
429 RETURN NVL(LN_SERV_CREDIT
430 ,0);
431 END CF_CREDIT_RECEIVEDFORMULA;
432 FUNCTION CF_CREDIT_UTILIZEDFORMULA(MONTH1 IN VARCHAR2
433 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
434 LV_DATE VARCHAR2(8);
435 CURSOR CUR_AR_UTIL_CREDIT IS
436 SELECT
437 SUM(RECOVERED_AMOUNT)
438 FROM
439 JAI_RGM_TRX_REFS TRXREFS
440 WHERE SOURCE = 'AR'
441 AND TAX_TYPE in ( 'Service' , 'SERVICE_EDUCATION_CESS' )
442 AND ORGANIZATION_ID IN (
443 SELECT
444 DISTINCT
445 ORGANIZATION_ID
446 FROM
447 JAI_RGM_ORG_REGNS_V
448 WHERE REGIME_CODE = 'SERVICE'
449 AND REGISTRATION_TYPE = 'OTHERS'
450 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
451 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
452 AND ATTRIBUTE_VALUE = PRM_RGM_PRIM_REGNO )
456 AND ( NVL(PRM_TODT
453 AND ( NVL(TRUNC(CREATION_DATE)
454 ,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
455 ,SYSDATE) )
457 ,SYSDATE) )
458 AND TO_CHAR(TRXREFS.CREATION_DATE
459 ,'MON-YYYY') = LV_DATE
460 AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE;
461 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
462 SELECT
463 NVL(SUM(DEBIT_AMOUNT)
464 ,0)
465 FROM
466 JAI_RGM_TRX_RECORDS TRXRECS
467 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
468 AND REGIME_CODE = 'SERVICE'
469 AND TAX_TYPE IN ( 'Service' , 'SERVICE_EDUCATION_CESS' )
470 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
471 AND ( NVL(TRUNC(CREATION_DATE)
472 ,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
473 ,SYSDATE) )
474 AND ( NVL(PRM_TODT
475 ,SYSDATE) )
476 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
477 ,'MON-YYYY') = LV_DATE
478 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
479 ,TRXRECS.ORGANIZATION_ID)
480 AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
481 ,TRXRECS.SERVICE_TYPE_CODE);
482 CURSOR CUR_MANUAL_DEBIT IS
483 SELECT
484 NVL(SUM(DEBIT_AMOUNT)
485 ,0)
486 FROM
487 JAI_RGM_TRX_RECORDS TRXRECS
488 WHERE SOURCE = 'MANUAL'
489 AND REGIME_CODE = 'SERVICE'
490 AND TAX_TYPE IN ( 'Service' , 'SERVICE_EDUCATION_CESS' )
491 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
492 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
493 AND ( NVL(TRUNC(CREATION_DATE)
494 ,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
495 ,SYSDATE) )
496 AND ( NVL(PRM_TODT
497 ,SYSDATE) )
498 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
499 ,'MON-YYYY') = LV_DATE
500 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
501 ,TRXRECS.ORGANIZATION_ID)
502 AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
503 ,TRXRECS.SERVICE_TYPE_CODE);
504 CURSOR CUR_PAYMENT IS
505 SELECT
506 NVL(SUM(DEBIT_AMOUNT)
507 ,0)
508 FROM
509 JAI_RGM_TRX_RECORDS TRXRECS
510 WHERE SOURCE = 'MANUAL'
511 AND REGIME_CODE = 'SERVICE'
512 AND TAX_TYPE IN ( 'Service' , 'SERVICE_EDUCATION_CESS' )
513 AND SOURCE_TRX_TYPE = 'PAYMENT'
514 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
515 AND ( NVL(TRUNC(CREATION_DATE)
516 ,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
517 ,SYSDATE) )
518 AND ( NVL(PRM_TODT
519 ,SYSDATE) )
520 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
521 ,'MON-YYYY') = LV_DATE
522 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
523 ,TRXRECS.ORGANIZATION_ID)
524 AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
525 ,TRXRECS.SERVICE_TYPE_CODE);
526 LN_AR_UTIL_CREDIT NUMBER := 0;
527 LN_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
528 LV_MANUAL_DEBIT NUMBER := 0;
529 LV_PAYMENT NUMBER := 0;
530 BEGIN
531 LV_DATE := MONTH1 || '-' || YEAR1;
532 OPEN CUR_AR_UTIL_CREDIT;
533 FETCH CUR_AR_UTIL_CREDIT
534 INTO LN_AR_UTIL_CREDIT;
535 CLOSE CUR_AR_UTIL_CREDIT;
536 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
537 FETCH CUR_AR_SER_DIST_OUT_DEBIT
538 INTO LN_AR_SER_DIST_OUT_DEBIT;
539 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
540 OPEN CUR_MANUAL_DEBIT;
541 FETCH CUR_MANUAL_DEBIT
542 INTO LV_MANUAL_DEBIT;
543 CLOSE CUR_MANUAL_DEBIT;
544 OPEN CUR_PAYMENT;
545 FETCH CUR_PAYMENT
546 INTO LV_PAYMENT;
547 CLOSE CUR_PAYMENT;
548 RETURN (NVL(LN_AR_UTIL_CREDIT
549 ,0) + NVL(LN_AR_SER_DIST_OUT_DEBIT
550 ,0) + NVL(LV_MANUAL_DEBIT
551 ,0) - NVL(LV_PAYMENT
552 ,0));
553 END CF_CREDIT_UTILIZEDFORMULA;
554 FUNCTION CF_CREDIT_INPUT_SERVICESFORMUL(MONTH1 IN VARCHAR2
555 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
556 LV_DATE VARCHAR2(8);
557 CURSOR C_SERV_CREDIT IS
558 SELECT
559 SUM(TRXRECS.CREDIT_AMOUNT) SERVICE_CREDIT
560 FROM
561 JAI_RGM_TRX_RECORDS TRXRECS,
562 JAI_RGM_TRX_REFS TRXREFS,
563 AP_INVOICES_ALL APA,
564 JAI_CMN_VENDOR_SITES JPVS,
565 PO_VENDOR_SITES_ALL PVSA,
566 JAI_INV_ITM_SETUPS JMSI
567 WHERE TRXREFS.REFERENCE_ID = TRXRECS.REFERENCE_ID
568 AND APA.CANCELLED_DATE IS NULL
569 AND APA.CANCELLED_BY IS NULL
570 AND APA.CANCELLED_AMOUNT IS NULL
571 AND JPVS.VENDOR_ID = APA.VENDOR_ID
572 AND JPVS.VENDOR_SITE_ID = APA.VENDOR_SITE_ID
573 AND APA.VENDOR_ID = PVSA.VENDOR_ID
574 AND APA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID
575 AND APA.INVOICE_ID = TRXREFS.INVOICE_ID
576 AND TRXREFS.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
577 AND TRXREFS.ITEM_ID IS NULL
578 AND TRXRECS.SOURCE in ( 'AP' )
579 AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
580 AND TRXRECS.REGIME_CODE = 'SERVICE'
581 AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
582 AND TRUNC(PRM_TODT)
583 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
584 ,'MON-YYYY') = LV_DATE
585 AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
586 ,TRXRECS.SERVICE_TYPE_CODE)
587 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
588 ,TRXRECS.ORGANIZATION_ID)
589 AND TRXREFS.TAX_TYPE = 'Service';
590 CURSOR C_SERV_CREDIT_1 IS
591 SELECT
592 SUM(JRTR.CREDIT_AMOUNT) SERVICE_CREDIT
593 FROM
597 WHERE JRTR.SOURCE = 'MANUAL'
594 JAI_RGM_TRX_RECORDS JRTR,
595 JAI_RGM_MANUAL_TRXS JMT,
596 PO_VENDOR_SITES_ALL PVSA
598 AND JRTR.REGIME_CODE = 'SERVICE'
599 AND JRTR.SOURCE_DOCUMENT_ID = JMT.TRANSACTION_NUMBER
600 AND PVSA.VENDOR_ID = JMT.PARTY_ID
601 AND PVSA.VENDOR_SITE_ID = JMT.PARTY_SITE_ID
602 AND JRTR.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
603 AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
604 AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
605 AND TRUNC(PRM_TODT)
606 AND TO_CHAR(JRTR.TRANSACTION_DATE
607 ,'MON-YYYY') = LV_DATE
608 AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
609 ,JRTR.SERVICE_TYPE_CODE)
610 AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
611 ,JRTR.ORGANIZATION_ID)
612 AND JRTR.TAX_TYPE = 'Service';
613 CURSOR C_TAX_AMOUNT IS
614 SELECT
615 SUM(JRTR.CREDIT_AMOUNT) TAX_AMOUNT
616 FROM
617 JAI_RGM_TRX_RECORDS JRTR
618 WHERE JRTR.SOURCE = 'SERVICE_DISTRIBUTE_IN'
619 AND JRTR.REGIME_CODE = 'SERVICE'
620 AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
621 AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
622 AND TRUNC(PRM_TODT)
623 AND TO_CHAR(JRTR.TRANSACTION_DATE
624 ,'MON-YYYY') = LV_DATE
625 AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
626 ,JRTR.SERVICE_TYPE_CODE)
627 AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
628 ,JRTR.ORGANIZATION_ID);
629 LN_SERV_CREDIT JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
630 LN_SERV_CREDIT1 JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
631 LN_TAX_AMOUNT JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
632 BEGIN
633 LV_DATE := MONTH1 || '-' || YEAR1;
634 OPEN C_SERV_CREDIT;
635 FETCH C_SERV_CREDIT
636 INTO LN_SERV_CREDIT;
637 CLOSE C_SERV_CREDIT;
638 OPEN C_SERV_CREDIT_1;
639 FETCH C_SERV_CREDIT_1
640 INTO LN_SERV_CREDIT1;
641 CLOSE C_SERV_CREDIT_1;
642 OPEN C_TAX_AMOUNT;
643 FETCH C_TAX_AMOUNT
644 INTO LN_TAX_AMOUNT;
645 CLOSE C_TAX_AMOUNT;
646 RETURN NVL(LN_SERV_CREDIT
647 ,0) + NVL(LN_SERV_CREDIT1
648 ,0) + NVL(LN_TAX_AMOUNT
649 ,0);
650 END CF_CREDIT_INPUT_SERVICESFORMUL;
651 FUNCTION CF_TOTAL_CREDITFORMULA(CF_CREDIT_AVAILED IN NUMBER
652 ,CF_CREDIT_RECEIVED IN NUMBER) RETURN NUMBER IS
653 BEGIN
654 RETURN CF_CREDIT_AVAILED + CP_CREDIT_AVAILED + CF_CREDIT_RECEIVED;
655 END CF_TOTAL_CREDITFORMULA;
656 FUNCTION CF_CLOSING_BALANCEFORMULA(CF_OPENING_BALANCE IN NUMBER
657 ,CF_TOTAL_CREDIT IN NUMBER
658 ,CF_CREDIT_UTILIZED IN NUMBER) RETURN NUMBER IS
659 BEGIN
660 RETURN CF_OPENING_BALANCE + CF_TOTAL_CREDIT - CF_CREDIT_UTILIZED;
661 END CF_CLOSING_BALANCEFORMULA;
662 FUNCTION CF_EDUCESS_OPENING_BALANCEFORM(MONTH1 IN VARCHAR2
663 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
664 LV_DATE VARCHAR2(11);
665 CURSOR CUR_INVOICE_OPEN_BAL IS
666 SELECT
667 SUM(RECOVERED_AMOUNT)
668 FROM
669 JAI_RGM_TRX_REFS
670 WHERE SOURCE = 'AP'
671 AND TAX_TYPE in ( 'SERVICE_EDUCATION_CESS' )
672 AND TRUNC(CREATION_DATE) < LV_DATE
673 AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
674 AND ORGANIZATION_ID in (
675 SELECT
676 DISTINCT
677 ORGANIZATION_ID
678 FROM
679 JAI_RGM_ORG_REGNS_V
680 WHERE REGIME_CODE = 'SERVICE'
681 AND REGISTRATION_TYPE = 'OTHERS'
682 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
683 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
684 AND ATTRIBUTE_VALUE = PRM_RGM_PRIM_REGNO );
685 CURSOR CUR_DIST_IN IS
686 SELECT
687 SUM(CREDIT_AMOUNT)
688 FROM
689 JAI_RGM_TRX_RECORDS
690 WHERE SOURCE = 'SERVICE_DISTRIBUTE_IN'
691 AND REGIME_CODE = 'SERVICE'
692 AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
693 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
694 AND ( NVL(TRUNC(CREATION_DATE)
695 ,TRUNC(SYSDATE)) ) < LV_DATE
696 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
697 ,ORGANIZATION_ID)
698 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
699 ,SERVICE_TYPE_CODE);
700 CURSOR CUR_MANUAL_IN IS
701 SELECT
702 SUM(CREDIT_AMOUNT)
703 FROM
704 JAI_RGM_TRX_RECORDS
705 WHERE SOURCE = 'MANUAL'
706 AND REGIME_CODE = 'SERVICE'
707 AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
708 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
709 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
710 AND ( NVL(TRUNC(CREATION_DATE)
711 ,TRUNC(SYSDATE)) ) < LV_DATE
712 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
713 ,ORGANIZATION_ID)
714 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
715 ,SERVICE_TYPE_CODE);
716 CURSOR CUR_AR_UTIL_CREDIT IS
717 SELECT
718 SUM(RECOVERED_AMOUNT)
719 FROM
720 JAI_RGM_TRX_REFS
721 WHERE SOURCE = 'AR'
722 AND TAX_TYPE in ( 'SERVICE_EDUCATION_CESS' )
723 AND TRUNC(CREATION_DATE) < LV_DATE
724 AND JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(REFERENCE_ID) = PRM_SERVICE_TYPE
725 AND ORGANIZATION_ID IN (
726 SELECT
727 DISTINCT
728 ORGANIZATION_ID
729 FROM
730 JAI_RGM_ORG_REGNS_V
731 WHERE REGIME_CODE = 'SERVICE'
732 AND REGISTRATION_TYPE = 'OTHERS'
733 AND ATTRIBUTE_TYPE_CODE = 'PRIMARY'
734 AND ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO'
735 AND ATTRIBUTE_VALUE = PRM_RGM_PRIM_REGNO );
736 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
737 SELECT
738 NVL(SUM(DEBIT_AMOUNT)
739 ,0)
740 FROM
741 JAI_RGM_TRX_RECORDS
742 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
743 AND REGIME_CODE = 'SERVICE'
744 AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
745 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
746 AND ( NVL(TRUNC(CREATION_DATE)
747 ,TRUNC(SYSDATE)) ) < LV_DATE
748 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
749 ,ORGANIZATION_ID)
750 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
751 ,SERVICE_TYPE_CODE);
752 CURSOR CUR_MANUAL_DEBIT IS
753 SELECT
754 NVL(SUM(DEBIT_AMOUNT)
755 ,0)
756 FROM
757 JAI_RGM_TRX_RECORDS
758 WHERE SOURCE = 'MANUAL'
759 AND REGIME_CODE = 'SERVICE'
760 AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
761 AND SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
762 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
763 AND ( NVL(TRUNC(CREATION_DATE)
764 ,TRUNC(SYSDATE)) ) < LV_DATE
765 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
766 ,ORGANIZATION_ID)
767 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
768 ,SERVICE_TYPE_CODE);
769 CURSOR CUR_PAYMENT IS
770 SELECT
771 NVL(SUM(DEBIT_AMOUNT)
772 ,0)
773 FROM
774 JAI_RGM_TRX_RECORDS
775 WHERE SOURCE = 'MANUAL'
776 AND REGIME_CODE = 'SERVICE'
777 AND TAX_TYPE IN ( 'SERVICE_EDUCATION_CESS' )
778 AND SOURCE_TRX_TYPE = 'PAYMENT'
779 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
780 AND ( NVL(TRUNC(CREATION_DATE)
781 ,TRUNC(SYSDATE)) ) < LV_DATE
782 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
783 ,ORGANIZATION_ID)
784 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
785 ,SERVICE_TYPE_CODE);
786 LV_INV_OPEN_BAL NUMBER := 0;
787 LV_OPEN_DIST_BAL NUMBER := 0;
788 LV_AR_UTIL_CREDIT NUMBER := 0;
789 LV_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
790 LV_MANUAL_BAL NUMBER := 0;
791 LV_MANUAL_DEBIT_BAL NUMBER := 0;
792 LV_MANUAL_PAYMENT NUMBER := 0;
793 BEGIN
794 LV_DATE := '01' || '-' || MONTH1 || '-' || YEAR1;
795 OPEN CUR_INVOICE_OPEN_BAL;
796 FETCH CUR_INVOICE_OPEN_BAL
797 INTO LV_INV_OPEN_BAL;
798 CLOSE CUR_INVOICE_OPEN_BAL;
799 OPEN CUR_DIST_IN;
800 FETCH CUR_DIST_IN
801 INTO LV_OPEN_DIST_BAL;
802 CLOSE CUR_DIST_IN;
803 OPEN CUR_MANUAL_IN;
804 FETCH CUR_MANUAL_IN
805 INTO LV_MANUAL_BAL;
806 CLOSE CUR_MANUAL_IN;
807 OPEN CUR_MANUAL_DEBIT;
808 FETCH CUR_MANUAL_DEBIT
809 INTO LV_MANUAL_DEBIT_BAL;
810 CLOSE CUR_MANUAL_DEBIT;
811 OPEN CUR_AR_UTIL_CREDIT;
812 FETCH CUR_AR_UTIL_CREDIT
813 INTO LV_AR_UTIL_CREDIT;
814 CLOSE CUR_AR_UTIL_CREDIT;
815 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
816 FETCH CUR_AR_SER_DIST_OUT_DEBIT
817 INTO LV_AR_SER_DIST_OUT_DEBIT;
818 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
819 OPEN CUR_PAYMENT;
820 FETCH CUR_PAYMENT
821 INTO LV_MANUAL_PAYMENT;
822 CLOSE CUR_PAYMENT;
823 RETURN (NVL(LV_OPEN_DIST_BAL
824 ,0) + NVL(LV_INV_OPEN_BAL
825 ,0) + NVL(LV_MANUAL_BAL
826 ,0) - NVL(LV_AR_UTIL_CREDIT
827 ,0) - NVL(LV_AR_SER_DIST_OUT_DEBIT
828 ,0) - NVL(LV_MANUAL_DEBIT_BAL
829 ,0) + NVL(LV_MANUAL_PAYMENT
830 ,0));
831 END CF_EDUCESS_OPENING_BALANCEFORM;
832 FUNCTION CF_EDUCESS_CREDIT_AVAILEDFORMU(MONTH1 IN VARCHAR2
833 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
834 LV_DATE VARCHAR2(8);
835 CURSOR C_SERV_CREDIT(CPV_ITEM_CLASS IN VARCHAR2) IS
836 SELECT
837 SUM(TRXRECS.CREDIT_AMOUNT) EDU_CESS_CREDIT
838 FROM
839 JAI_RGM_TRX_RECORDS TRXRECS,
840 JAI_RGM_TRX_REFS TRXREFS,
841 AP_INVOICES_ALL APA,
842 JAI_CMN_VENDOR_SITES JPVS,
843 PO_VENDOR_SITES_ALL PVSA,
844 JAI_INV_ITM_SETUPS JMSI
845 WHERE TRXREFS.REFERENCE_ID = TRXRECS.REFERENCE_ID
846 AND APA.CANCELLED_DATE IS NULL
847 AND APA.CANCELLED_BY IS NULL
848 AND APA.CANCELLED_AMOUNT IS NULL
852 AND APA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID
849 AND JPVS.VENDOR_ID = APA.VENDOR_ID
850 AND JPVS.VENDOR_SITE_ID = APA.VENDOR_SITE_ID
851 AND APA.VENDOR_ID = PVSA.VENDOR_ID
853 AND APA.INVOICE_ID = TRXREFS.INVOICE_ID
854 AND TRXREFS.ORGANIZATION_ID = JMSI.ORGANIZATION_ID
855 AND TRXREFS.ITEM_ID = JMSI.INVENTORY_ITEM_ID
856 AND JMSI.ITEM_CLASS = CPV_ITEM_CLASS
857 AND TRXRECS.SOURCE in ( 'AP' )
858 AND TRXRECS.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
859 AND TRXRECS.REGIME_CODE = 'SERVICE'
860 AND TRUNC(TRXRECS.TRANSACTION_DATE) between TRUNC(PRM_FROMDT)
861 AND TRUNC(PRM_TODT)
862 AND TO_CHAR(TRXRECS.TRANSACTION_DATE
863 ,'MON-YYYY') = LV_DATE
864 AND TRXRECS.ORGANIZATION_ID = NVL(PRM_ORG_ID
865 ,TRXRECS.ORGANIZATION_ID)
866 AND TRXRECS.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
867 ,TRXRECS.SERVICE_TYPE_CODE)
868 AND TRXREFS.TAX_TYPE = 'SERVICE_EDUCATION_CESS';
869 CURSOR C_SERV_CREDIT_1(CPV_ITEM_CLASS IN VARCHAR2) IS
870 SELECT
871 SUM(JRTR.CREDIT_AMOUNT) EDU_CESS_CREDIT
872 FROM
873 JAI_RGM_TRX_RECORDS JRTR,
874 JAI_RGM_MANUAL_TRXS JMT,
875 PO_VENDOR_SITES_ALL PVSA
876 WHERE JRTR.SOURCE = 'MANUAL'
877 AND JRTR.REGIME_CODE = 'SERVICE'
878 AND JRTR.SOURCE_DOCUMENT_ID = JMT.TRANSACTION_NUMBER
879 AND PVSA.VENDOR_ID = JMT.PARTY_ID
880 AND PVSA.VENDOR_SITE_ID = JMT.PARTY_SITE_ID
881 AND JMT.ITEM_CLASS = CPV_ITEM_CLASS
882 AND JRTR.SOURCE_TRX_TYPE IN ( 'ADJUSTMENT-RECOVERY' , 'RECOVERY' )
883 AND JRTR.REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
884 AND TRUNC(JRTR.TRANSACTION_DATE) BETWEEN TRUNC(PRM_FROMDT)
885 AND TRUNC(PRM_TODT)
886 AND TO_CHAR(JRTR.TRANSACTION_DATE
887 ,'MON-YYYY') = LV_DATE
888 AND JRTR.ORGANIZATION_ID = NVL(PRM_ORG_ID
889 ,JRTR.ORGANIZATION_ID)
890 AND JRTR.SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
891 ,JRTR.SERVICE_TYPE_CODE)
892 AND JRTR.TAX_TYPE = 'SERVICE_EDUCATION_CESS';
893 LN_SERV_CREDIT_RMIN JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
894 LN_SERV_CREDIT_RM JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
895 LN_SERV_CREDIT_CGIN JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
896 LN_SERV_CREDIT_FG JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE;
897 BEGIN
898 LV_DATE := MONTH1 || '-' || YEAR1;
899 OPEN C_SERV_CREDIT('RMIN');
900 FETCH C_SERV_CREDIT
901 INTO LN_SERV_CREDIT_RMIN;
902 CLOSE C_SERV_CREDIT;
903 OPEN C_SERV_CREDIT_1('RM');
904 FETCH C_SERV_CREDIT_1
905 INTO LN_SERV_CREDIT_RM;
906 CLOSE C_SERV_CREDIT_1;
907 OPEN C_SERV_CREDIT('CGIN');
908 FETCH C_SERV_CREDIT
909 INTO LN_SERV_CREDIT_CGIN;
910 CLOSE C_SERV_CREDIT;
911 OPEN C_SERV_CREDIT_1('CG');
912 FETCH C_SERV_CREDIT_1
913 INTO LN_SERV_CREDIT_FG;
914 CLOSE C_SERV_CREDIT_1;
915 RETURN NVL(LN_SERV_CREDIT_RMIN
916 ,0) + NVL(LN_SERV_CREDIT_RM
917 ,0) + NVL(LN_SERV_CREDIT_CGIN
918 ,0) + NVL(LN_SERV_CREDIT_FG
919 ,0);
920 END CF_EDUCESS_CREDIT_AVAILEDFORMU;
921 FUNCTION CF_EDUCESS_CLOSING_BALANCEFORM(CF_EDUCESS_OPENING_BALANCE IN NUMBER
922 ,CF_CREDIT_UTILIZED IN NUMBER) RETURN NUMBER IS
923 BEGIN
924 RETURN NVL(CF_EDUCESS_OPENING_BALANCE
925 ,0) + NVL(CP_EDUCESS_CREDIT_AVAILED
926 ,0) - NVL(CF_CREDIT_UTILIZED
927 ,0);
928 END CF_EDUCESS_CLOSING_BALANCEFORM;
929 FUNCTION CF_CCRD_ST_DISTRIBUTEDFORMULA(MONTH1 IN VARCHAR2
930 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
931 LV_DATE VARCHAR2(8);
932 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
933 SELECT
934 NVL(SUM(DEBIT_AMOUNT)
935 ,0) DEBIT
936 FROM
937 JAI_RGM_TRX_RECORDS
938 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
939 AND REGIME_CODE = 'SERVICE'
940 AND TAX_TYPE = 'Service'
941 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
942 AND ( NVL(TRUNC(CREATION_DATE)
943 ,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
944 ,SYSDATE) )
945 AND ( NVL(PRM_TODT
946 ,SYSDATE) )
947 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
948 ,SERVICE_TYPE_CODE)
949 AND TO_CHAR(CREATION_DATE
950 ,'MON-YYYY') = LV_DATE
951 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
952 ,ORGANIZATION_ID);
953 LN_DEBIT_AMOUNT JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE;
954 BEGIN
955 LV_DATE := MONTH1 || '-' || YEAR1;
956 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
957 FETCH CUR_AR_SER_DIST_OUT_DEBIT
958 INTO LN_DEBIT_AMOUNT;
959 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
960 RETURN LN_DEBIT_AMOUNT;
961 END CF_CCRD_ST_DISTRIBUTEDFORMULA;
962 FUNCTION CF_CCRD_CLOSING_BALANCEFORMULA(CF_OPENING_BALANCE IN NUMBER
963 ,CF_TOTAL_CREDIT IN NUMBER
964 ,CF_CCRD_ST_DISTRIBUTED IN NUMBER) RETURN NUMBER IS
965 BEGIN
966 RETURN CF_OPENING_BALANCE + CF_TOTAL_CREDIT - CF_CCRD_ST_DISTRIBUTED;
967 END CF_CCRD_CLOSING_BALANCEFORMULA;
968 FUNCTION CF_ECRD_EC_DISTRIBUTEDFORMULA(MONTH1 IN VARCHAR2
969 ,YEAR1 IN VARCHAR2) RETURN NUMBER IS
970 LV_DATE VARCHAR2(8);
971 CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
972 SELECT
973 NVL(SUM(DEBIT_AMOUNT)
974 ,0) DEBIT
975 FROM
976 JAI_RGM_TRX_RECORDS
977 WHERE SOURCE = 'SERVICE_DISTRIBUTE_OUT'
978 AND REGIME_CODE = 'SERVICE'
979 AND TAX_TYPE = 'SERVICE_EDUCATION_CESS'
980 AND REGIME_PRIMARY_REGNO = PRM_RGM_PRIM_REGNO
981 AND ( NVL(TRUNC(CREATION_DATE)
982 ,SYSDATE) ) BETWEEN ( NVL(PRM_FROMDT
983 ,SYSDATE) )
984 AND ( NVL(PRM_TODT
985 ,SYSDATE) )
986 AND TO_CHAR(CREATION_DATE
987 ,'MON-YYYY') = LV_DATE
988 AND SERVICE_TYPE_CODE = NVL(PRM_SERVICE_TYPE
989 ,SERVICE_TYPE_CODE)
990 AND ORGANIZATION_ID = NVL(PRM_ORG_ID
991 ,ORGANIZATION_ID);
992 LN_DEBIT_AMOUNT JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE;
993 BEGIN
994 LV_DATE := MONTH1 || '-' || YEAR1;
995 /*SRW.MESSAGE(1275
996 ,LV_DATE)*/NULL;
997 OPEN CUR_AR_SER_DIST_OUT_DEBIT;
998 FETCH CUR_AR_SER_DIST_OUT_DEBIT
999 INTO LN_DEBIT_AMOUNT;
1000 CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
1001 RETURN LN_DEBIT_AMOUNT;
1002 END CF_ECRD_EC_DISTRIBUTEDFORMULA;
1003 FUNCTION CF_ECRD_EC_RECEIVEDFORMULA(CF_EDUCESS_CREDIT_AVAILED IN NUMBER) RETURN NUMBER IS
1004 BEGIN
1005 RETURN CF_EDUCESS_CREDIT_AVAILED;
1006 END CF_ECRD_EC_RECEIVEDFORMULA;
1007 FUNCTION CF_ECRD_CLOSING_BALANCEFORMULA(CF_EDUCESS_OPENING_BALANCE IN NUMBER
1008 ,CF_ECRD_EC_RECEIVED IN NUMBER
1009 ,CF_ECRD_EC_DISTRIBUTED IN NUMBER) RETURN NUMBER IS
1010 BEGIN
1011 RETURN CF_EDUCESS_OPENING_BALANCE + CF_ECRD_EC_RECEIVED - CF_ECRD_EC_DISTRIBUTED;
1012 END CF_ECRD_CLOSING_BALANCEFORMULA;
1013 FUNCTION CF_GET_SERVICE_TYPE(LPN_REFERENCE_ID IN NUMBER) RETURN VARCHAR2 IS
1014 LV_SERVICE_TYPE VARCHAR2(30);
1015 LV_ORG NUMBER;
1016 LV_LOC NUMBER;
1017 LV_PROCESS_FLG VARCHAR2(30);
1018 LV_PROCESS_MSG VARCHAR2(40);
1019 BEGIN
1020 RETURN LV_SERVICE_TYPE;
1021 END CF_GET_SERVICE_TYPE;
1022 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
1023 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
1024 SELECT
1025 CONCURRENT_PROGRAM_ID,
1026 NVL(ENABLE_TRACE
1027 ,'N')
1028 FROM
1029 FND_CONCURRENT_REQUESTS
1030 WHERE REQUEST_ID = P_REQUEST_ID;
1031 CURSOR GET_AUDSID IS
1032 SELECT
1033 A.SID,
1034 A.SERIAL#,
1035 B.SPID
1036 FROM
1037 V$SESSION A,
1038 V$PROCESS B
1039 WHERE AUDSID = USERENV('SESSIONID')
1040 AND A.PADDR = B.ADDR;
1041 CURSOR GET_DBNAME IS
1042 SELECT
1043 NAME
1044 FROM
1045 V$DATABASE;
1046 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
1047 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
1048 AUDSID NUMBER := USERENV('SESSIONID');
1049 SID NUMBER;
1050 SERIAL NUMBER;
1051 SPID VARCHAR2(9);
1052 NAME1 VARCHAR2(25);
1053 BEGIN
1054 /*SRW.MESSAGE(1275
1055 ,'Report Version is 120.1 last modified date is 02/05/2007')*/NULL;
1056 BEGIN
1057 PRM_FROMDT1 := to_char(PRM_FROMDT,'dd-mm-yyyy');
1058 PRM_TODT1 := to_char(PRM_TODT,'dd-mm-yyyy');
1059 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
1060 FETCH C_PROGRAM_ID
1061 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
1062 CLOSE C_PROGRAM_ID;
1063 /*SRW.MESSAGE(1275
1064 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
1065 IF V_ENABLE_TRACE = 'Y' THEN
1066 OPEN GET_AUDSID;
1067 FETCH GET_AUDSID
1068 INTO SID,SERIAL,SPID;
1069 CLOSE GET_AUDSID;
1070 OPEN GET_DBNAME;
1071 FETCH GET_DBNAME
1072 INTO NAME1;
1073 CLOSE GET_DBNAME;
1074 /*SRW.MESSAGE(1275
1075 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
1076 EXECUTE IMMEDIATE
1077 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
1078 END IF;
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 /*SRW.MESSAGE(1275
1082 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
1083 END;
1084 RETURN (TRUE);
1085 END BEFOREREPORT;
1086 FUNCTION CF_COUNTFORMULA(CS_COUNT IN NUMBER) RETURN NUMBER IS
1087 BEGIN
1088 RETURN CS_COUNT + 1;
1089 END CF_COUNTFORMULA;
1090 FUNCTION CF_COUNT_RECORDSFORMULA(CS_COUNT_RECORDS IN NUMBER) RETURN NUMBER IS
1091 BEGIN
1092 RETURN CS_COUNT_RECORDS + 1;
1093 END CF_COUNT_RECORDSFORMULA;
1094 FUNCTION CF_EDUCESS_SERVICESFORMULA RETURN CHAR IS
1095 BEGIN
1096 RETURN ' ';
1097 END CF_EDUCESS_SERVICESFORMULA;
1098 FUNCTION CF_CCRD_ELIGIBLEFORMULA RETURN CHAR IS
1099 BEGIN
1100 RETURN ' ';
1101 END CF_CCRD_ELIGIBLEFORMULA;
1102 FUNCTION CF_ECRD_ELIGIBLEFORMULA RETURN CHAR IS
1103 BEGIN
1104 RETURN ' ';
1105 END CF_ECRD_ELIGIBLEFORMULA;
1106 FUNCTION CF_1FORMULA0012(CS_1 IN NUMBER) RETURN NUMBER IS
1107 BEGIN
1108 RETURN CS_1 + 2;
1109 END CF_1FORMULA0012;
1110 FUNCTION CF_SERVICE_TYPE_DESCFORMULA(SERVICE_TYPE_CODE IN VARCHAR2) RETURN CHAR IS
1111 DESCRIPTION JA_LOOKUPS.DESCRIPTION%TYPE;
1112 BEGIN
1113 SELECT
1114 DESCRIPTION
1115 INTO DESCRIPTION
1116 FROM
1117 JA_LOOKUPS
1118 WHERE LOOKUP_TYPE = 'JAI_SERVICE_TYPE'
1119 AND LOOKUP_CODE = SERVICE_TYPE_CODE;
1120 RETURN DESCRIPTION;
1121 END CF_SERVICE_TYPE_DESCFORMULA;
1122 FUNCTION CP_SERVICE_CESS_AMOUNT_P RETURN NUMBER IS
1123 BEGIN
1124 RETURN CP_SERVICE_CESS_AMOUNT;
1125 END CP_SERVICE_CESS_AMOUNT_P;
1126 FUNCTION CP_INVOICE_DATE_P RETURN DATE IS
1127 BEGIN
1128 RETURN CP_INVOICE_DATE;
1129 END CP_INVOICE_DATE_P;
1130 FUNCTION CP_ST_SERVICE_CESS_AMOUNT_P RETURN NUMBER IS
1131 BEGIN
1132 RETURN CP_ST_SERVICE_CESS_AMOUNT;
1133 END CP_ST_SERVICE_CESS_AMOUNT_P;
1134 FUNCTION CP_CREDIT_AVAILED_P RETURN NUMBER IS
1135 BEGIN
1136 RETURN CP_CREDIT_AVAILED;
1137 END CP_CREDIT_AVAILED_P;
1138 FUNCTION CP_EDUCESS_CREDIT_AVAILED_P RETURN NUMBER IS
1139 BEGIN
1140 RETURN CP_EDUCESS_CREDIT_AVAILED;
1141 END CP_EDUCESS_CREDIT_AVAILED_P;
1142 END JA_JAINST3_XMLP_PKG;
1143
1144