eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JA_JAINRGCR_XMLP_PKG

Source


1 PACKAGE BODY JA_JAINRGCR_XMLP_PKG AS
2 /* $Header: JAINRGCRB.pls 120.1 2007/12/25 16:28:07 dwkrishn noship $ */
3   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4     CURSOR CUR_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
5       SELECT
6         CONCURRENT_PROGRAM_ID,
7         NVL(ENABLE_TRACE
8            ,'N')
9       FROM
10         FND_CONCURRENT_REQUESTS
11       WHERE REQUEST_ID = P_REQUEST_ID;
12     CURSOR CUR_GET_AUDSID IS
13       SELECT
14         A.SID,
15         A.SERIAL#,
16         B.SPID
17       FROM
18         V$SESSION A,
19         V$PROCESS B
20       WHERE AUDSID = USERENV('SESSIONID')
21         AND A.PADDR = B.ADDR;
22     CURSOR CUR_GET_DBNAME IS
23       SELECT
24         NAME
25       FROM
26         V$DATABASE;
27     AUDSID NUMBER := USERENV('SESSIONID');
28     SID NUMBER;
29     SERIAL NUMBER;
30     SPID VARCHAR2(9);
31     NAME1 VARCHAR2(25);
32     V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
33     V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
34   BEGIN
35 
36     /*CP_FROM_DATE := TO_CHAR(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YY');
37     CP_TO_DATE := TO_CHAR(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YY');*/
38     CP_FROM_DATE := to_char(P_FROM_DATE,'DD-MON-YY');
39     CP_TO_DATE := to_char(P_TO_DATE,'DD-MON-YY');
40 
41     /*SRW.MESSAGE(1275
42                ,'Report Version is 120.3 Last modified date is 20/06/007')*/NULL;
43     P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
44     /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
45     BEGIN
46       OPEN CUR_PROGRAM_ID(P_CONC_REQUEST_ID);
47       FETCH CUR_PROGRAM_ID
48        INTO V_PROGRAM_ID,V_ENABLE_TRACE;
49       CLOSE CUR_PROGRAM_ID;
50       /*SRW.MESSAGE(1275
51                  ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
52       IF V_ENABLE_TRACE = 'Y' THEN
53         OPEN CUR_GET_AUDSID;
54         FETCH CUR_GET_AUDSID
55          INTO SID,SERIAL,SPID;
56         CLOSE CUR_GET_AUDSID;
57         OPEN CUR_GET_DBNAME;
58         FETCH CUR_GET_DBNAME
59          INTO NAME1;
60         CLOSE CUR_GET_DBNAME;
61         /*SRW.MESSAGE(1275
62                    ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
63         EXECUTE IMMEDIATE
64           'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
65       END IF;
66     EXCEPTION
67       WHEN OTHERS THEN
68         /*SRW.MESSAGE(1275
69                    ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg ->' || SQLERRM)*/NULL;
70     END;
71     RETURN (TRUE);
72   END BEFOREREPORT;
73 
74   FUNCTION CF_CREDIT_TAKENFORMULA(CS_SERVICE_CREDIT IN NUMBER
75                                  ,CS_EDU_CREDIT IN NUMBER) RETURN NUMBER IS
76   BEGIN
77     RETURN (NVL(CS_SERVICE_CREDIT
78               ,0) + NVL(CS_EDU_CREDIT
79               ,0));
80   END CF_CREDIT_TAKENFORMULA;
81 
82   FUNCTION CF_CREDIT_UTILIZEDFORMULA RETURN NUMBER IS
83     CURSOR CUR_AR_UTIL_CREDIT(CP_SOURCE IN JAI_RGM_TRX_REFS.SOURCE%TYPE) IS
84       SELECT
85         SUM(RECOVERED_AMOUNT)
86       FROM
87         JAI_RGM_TRX_REFS
88       WHERE SOURCE = CP_SOURCE
89         AND TAX_TYPE in ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
90         AND ORGANIZATION_ID IN (
91         SELECT
92           DISTINCT
93           ORGANIZATION_ID
94         FROM
95           JAI_RGM_ORG_REGNS_V
96         WHERE REGIME_CODE = LV_SERVICE_REGIME
97           AND REGISTRATION_TYPE = LV_OTH_REG_TYPE
98           AND ATTRIBUTE_TYPE_CODE = LV_PRIM_ATT_TYPE_CODE
99           AND ATTRIBUTE_CODE = LV_SERVICE_ATT_CODE
100           AND ATTRIBUTE_VALUE = P_REGM_PRMY_REGN )
101         AND ( NVL(TRUNC(CREATION_DATE)
102          ,SYSDATE) ) BETWEEN ( NVL(P_FROM_DATE
103          ,SYSDATE) )
104         AND ( NVL(P_TO_DATE
105          ,SYSDATE) );
106     CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
107       SELECT
108         NVL(SUM(DEBIT_AMOUNT)
109            ,0)
110       FROM
111         JAI_RGM_TRX_RECORDS
112       WHERE SOURCE = LV_SERVICE_SRC_DISTRIBUTE_OUT
113         AND REGIME_CODE = LV_SERVICE_REGIME
114         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
115         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
116         AND ( NVL(TRUNC(CREATION_DATE)
117          ,SYSDATE) ) BETWEEN ( NVL(P_FROM_DATE
118          ,SYSDATE) )
119         AND ( NVL(P_TO_DATE
120          ,SYSDATE) );
121     CURSOR CUR_MANUAL_DEBIT(CP_SOURCE IN JAI_RGM_TRX_REFS.SOURCE%TYPE) IS
122       SELECT
123         NVL(SUM(DEBIT_AMOUNT)
124            ,0)
125       FROM
126         JAI_RGM_TRX_RECORDS
127       WHERE SOURCE = CP_SOURCE
128         AND REGIME_CODE = LV_SERVICE_REGIME
129         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
130         AND SOURCE_TRX_TYPE IN ( LV_ADJUST_LIABILITY , LV_LIABILITY )
131         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
132         AND ( NVL(TRUNC(CREATION_DATE)
133          ,SYSDATE) ) BETWEEN ( NVL(P_FROM_DATE
134          ,SYSDATE) )
135         AND ( NVL(P_TO_DATE
136          ,SYSDATE) );
137     CURSOR CUR_PAYMENT(CP_SOURCE IN JAI_RGM_TRX_RECORDS.SOURCE%TYPE,CP_TRX_TYPE IN JAI_RGM_TRX_RECORDS.SOURCE_TRX_TYPE%TYPE) IS
138       SELECT
139         NVL(SUM(DEBIT_AMOUNT)
140            ,0)
141       FROM
142         JAI_RGM_TRX_RECORDS
143       WHERE SOURCE = CP_SOURCE
144         AND REGIME_CODE = LV_SERVICE_REGIME
145         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
146         AND SOURCE_TRX_TYPE = CP_TRX_TYPE
147         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
148         AND ( NVL(TRUNC(CREATION_DATE)
149          ,SYSDATE) ) BETWEEN ( NVL(P_FROM_DATE
150          ,SYSDATE) )
151         AND ( NVL(P_TO_DATE
152          ,SYSDATE) );
153     LN_AR_UTIL_CREDIT NUMBER := 0;
154     LN_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
155     LV_MANUAL_DEBIT NUMBER := 0;
156     LV_PAYMENT NUMBER := 0;
157   BEGIN
158     OPEN CUR_AR_UTIL_CREDIT('AR');
159     FETCH CUR_AR_UTIL_CREDIT
160      INTO LN_AR_UTIL_CREDIT;
161     CLOSE CUR_AR_UTIL_CREDIT;
162     OPEN CUR_AR_SER_DIST_OUT_DEBIT;
163     FETCH CUR_AR_SER_DIST_OUT_DEBIT
164      INTO LN_AR_SER_DIST_OUT_DEBIT;
165     CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
166     OPEN CUR_MANUAL_DEBIT('MANUAL');
167     FETCH CUR_MANUAL_DEBIT
168      INTO LV_MANUAL_DEBIT;
169     CLOSE CUR_MANUAL_DEBIT;
170     OPEN CUR_PAYMENT('MANUAL','PAYMENT');
171     FETCH CUR_PAYMENT
172      INTO LV_PAYMENT;
173     CLOSE CUR_PAYMENT;
174     RETURN (NVL(LN_AR_UTIL_CREDIT
175               ,0) + NVL(LN_AR_SER_DIST_OUT_DEBIT
176               ,0) + NVL(LV_MANUAL_DEBIT
177               ,0) - NVL(LV_PAYMENT
178               ,0));
179   END CF_CREDIT_UTILIZEDFORMULA;
180 
181   FUNCTION CF_CLOSING_BALFORMULA(CF_OPENING_BAL IN NUMBER
182                                 ,CF_CREDIT_TAKEN IN NUMBER
183                                 ,CF_CREDIT_UTILIZED IN NUMBER) RETURN NUMBER IS
184   BEGIN
185     RETURN (NVL(CF_OPENING_BAL
186               ,0) + NVL(CF_CREDIT_TAKEN
187               ,0) - NVL(CF_CREDIT_UTILIZED
188               ,0));
189   END CF_CLOSING_BALFORMULA;
190 
191   FUNCTION CF_OPENING_BALFORMULA RETURN NUMBER IS
192     CURSOR CUR_INVOICE_OPEN_BAL(CP_SOURCE IN JAI_RGM_TRX_REFS.SOURCE%TYPE) IS
193       SELECT
194         SUM(RECOVERED_AMOUNT)
195       FROM
196         JAI_RGM_TRX_REFS
197       WHERE SOURCE = CP_SOURCE
198         AND TAX_TYPE in ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
199         AND TRUNC(CREATION_DATE) < P_FROM_DATE
200         AND ORGANIZATION_ID in (
201         SELECT
202           DISTINCT
203           ORGANIZATION_ID
204         FROM
205           JAI_RGM_ORG_REGNS_V
206         WHERE REGIME_CODE = LV_SERVICE_REGIME
207           AND REGISTRATION_TYPE = LV_OTH_REG_TYPE
208           AND ATTRIBUTE_TYPE_CODE = LV_PRIM_ATT_TYPE_CODE
209           AND ATTRIBUTE_CODE = LV_SERVICE_ATT_CODE
210           AND ATTRIBUTE_VALUE = P_REGM_PRMY_REGN );
211     CURSOR CUR_DIST_IN IS
212       SELECT
213         SUM(CREDIT_AMOUNT)
214       FROM
215         JAI_RGM_TRX_RECORDS
216       WHERE SOURCE = LV_SERVICE_SRC_DISTRIBUTE_IN
217         AND REGIME_CODE = LV_SERVICE_REGIME
218         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
219         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
220         AND ( NVL(TRUNC(CREATION_DATE)
221          ,TRUNC(SYSDATE)) ) < ( NVL(P_FROM_DATE
222          ,TRUNC(SYSDATE)) );
223     CURSOR CUR_MANUAL_IN(CP_SOURCE IN JAI_RGM_TRX_RECORDS.SOURCE%TYPE) IS
224       SELECT
225         SUM(CREDIT_AMOUNT)
226       FROM
227         JAI_RGM_TRX_RECORDS
228       WHERE SOURCE = CP_SOURCE
229         AND REGIME_CODE = LV_SERVICE_REGIME
230         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
231         AND SOURCE_TRX_TYPE IN ( LV_ADJUST_RECOVERY , LV_RECOVERY )
232         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
233         AND ( NVL(TRUNC(CREATION_DATE)
234          ,TRUNC(SYSDATE)) ) < ( NVL(P_FROM_DATE
235          ,TRUNC(SYSDATE)) );
236     CURSOR CUR_AR_UTIL_CREDIT(CP_SOURCE IN JAI_RGM_TRX_REFS.SOURCE%TYPE) IS
237       SELECT
238         SUM(RECOVERED_AMOUNT)
239       FROM
240         JAI_RGM_TRX_REFS
241       WHERE SOURCE = CP_SOURCE
242         AND TAX_TYPE in ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
243         AND TRUNC(CREATION_DATE) < P_FROM_DATE
244         AND ORGANIZATION_ID IN (
245         SELECT
246           DISTINCT
247           ORGANIZATION_ID
248         FROM
249           JAI_RGM_ORG_REGNS_V
250         WHERE REGIME_CODE = LV_SERVICE_REGIME
251           AND REGISTRATION_TYPE = LV_OTH_REG_TYPE
252           AND ATTRIBUTE_TYPE_CODE = LV_PRIM_ATT_TYPE_CODE
253           AND ATTRIBUTE_CODE = LV_SERVICE_ATT_CODE
254           AND ATTRIBUTE_VALUE = P_REGM_PRMY_REGN );
255     CURSOR CUR_AR_SER_DIST_OUT_DEBIT IS
256       SELECT
257         NVL(SUM(DEBIT_AMOUNT)
258            ,0)
259       FROM
260         JAI_RGM_TRX_RECORDS
261       WHERE SOURCE = LV_SERVICE_SRC_DISTRIBUTE_OUT
262         AND REGIME_CODE = LV_SERVICE_REGIME
263         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
264         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
265         AND ( NVL(TRUNC(CREATION_DATE)
266          ,TRUNC(SYSDATE)) ) < ( NVL(P_FROM_DATE
267          ,TRUNC(SYSDATE)) );
268     CURSOR CUR_MANUAL_DEBIT(CP_SOURCE IN JAI_RGM_TRX_RECORDS.SOURCE%TYPE) IS
269       SELECT
270         NVL(SUM(DEBIT_AMOUNT)
271            ,0)
272       FROM
273         JAI_RGM_TRX_RECORDS
274       WHERE SOURCE = CP_SOURCE
275         AND REGIME_CODE = LV_SERVICE_REGIME
276         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SH_SER_EDU_CESS , LV_TAX_TYPE_SERVICE_EDU_CESS )
277         AND SOURCE_TRX_TYPE IN ( LV_LIABILITY , LV_ADJUST_LIABILITY )
278         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
279         AND ( NVL(TRUNC(CREATION_DATE)
280          ,TRUNC(SYSDATE)) ) < ( NVL(P_FROM_DATE
281          ,TRUNC(SYSDATE)) );
282     CURSOR CUR_PAYMENT(CP_SOURCE IN JAI_RGM_TRX_RECORDS.SOURCE%TYPE,CP_SOURCE_TRX_TYPE IN JAI_RGM_TRX_RECORDS.SOURCE_TRX_TYPE%TYPE) IS
283       SELECT
284         NVL(SUM(DEBIT_AMOUNT)
285            ,0)
286       FROM
287         JAI_RGM_TRX_RECORDS
288       WHERE SOURCE = CP_SOURCE
289         AND REGIME_CODE = LV_SERVICE_REGIME
290         AND TAX_TYPE IN ( LV_TAX_TYPE_SERVICE , LV_TAX_TYPE_SERVICE_EDU_CESS , LV_TAX_TYPE_SH_SER_EDU_CESS )
291         AND SOURCE_TRX_TYPE = CP_SOURCE_TRX_TYPE
292         AND REGIME_PRIMARY_REGNO = P_REGM_PRMY_REGN
293         AND ( NVL(TRUNC(CREATION_DATE)
294          ,TRUNC(SYSDATE)) ) < ( NVL(P_FROM_DATE
295          ,TRUNC(SYSDATE)) );
296     LV_INV_OPEN_BAL NUMBER := 0;
297     LV_OPEN_DIST_BAL NUMBER := 0;
298     LV_AR_UTIL_CREDIT NUMBER := 0;
299     LV_AR_SER_DIST_OUT_DEBIT NUMBER := 0;
300     LV_MANUAL_BAL NUMBER := 0;
301     LV_MANUAL_DEBIT_BAL NUMBER := 0;
302     LV_MANUAL_PAYMENT NUMBER := 0;
303   BEGIN
304     OPEN CUR_INVOICE_OPEN_BAL('AP');
305     FETCH CUR_INVOICE_OPEN_BAL
306      INTO LV_INV_OPEN_BAL;
307     CLOSE CUR_INVOICE_OPEN_BAL;
308     OPEN CUR_DIST_IN;
309     FETCH CUR_DIST_IN
310      INTO LV_OPEN_DIST_BAL;
311     CLOSE CUR_DIST_IN;
312     OPEN CUR_MANUAL_IN('MANUAL');
313     FETCH CUR_MANUAL_IN
314      INTO LV_MANUAL_BAL;
315     CLOSE CUR_MANUAL_IN;
316     OPEN CUR_MANUAL_DEBIT('MANUAL');
317     FETCH CUR_MANUAL_DEBIT
318      INTO LV_MANUAL_DEBIT_BAL;
319     CLOSE CUR_MANUAL_DEBIT;
320     OPEN CUR_AR_UTIL_CREDIT('AR');
321     FETCH CUR_AR_UTIL_CREDIT
322      INTO LV_AR_UTIL_CREDIT;
323     CLOSE CUR_AR_UTIL_CREDIT;
324     OPEN CUR_AR_SER_DIST_OUT_DEBIT;
325     FETCH CUR_AR_SER_DIST_OUT_DEBIT
326      INTO LV_AR_SER_DIST_OUT_DEBIT;
327     CLOSE CUR_AR_SER_DIST_OUT_DEBIT;
328     OPEN CUR_PAYMENT('MANUAL','PAYMENT');
329     FETCH CUR_PAYMENT
330      INTO LV_MANUAL_PAYMENT;
331     CLOSE CUR_PAYMENT;
332     RETURN (NVL(LV_OPEN_DIST_BAL
333               ,0) + NVL(LV_INV_OPEN_BAL
334               ,0) + NVL(LV_MANUAL_BAL
335               ,0) - NVL(LV_AR_UTIL_CREDIT
336               ,0) - NVL(LV_AR_SER_DIST_OUT_DEBIT
337               ,0) - NVL(LV_MANUAL_DEBIT_BAL
338               ,0) + NVL(LV_MANUAL_PAYMENT
339               ,0));
340   END CF_OPENING_BALFORMULA;
341 
342   FUNCTION AFTERPFORM RETURN BOOLEAN IS
343     LV_REPORTING_LEVEL VARCHAR2(2000);
344     LN_REPORTING_ENTITY_ID NUMBER;
345     LV_PREDICATE_TYPE VARCHAR2(2000);
346   BEGIN
347     LV_REPORTING_LEVEL := P_REPORTING_LEVEL;
348     LN_REPORTING_ENTITY_ID := P_REPORTING_ENTITY_ID;
349     FND_MO_REPORTING_API.INITIALIZE(LV_REPORTING_LEVEL
350                                    ,LN_REPORTING_ENTITY_ID
351                                    ,LV_PREDICATE_TYPE);
352     P_ORG_WHERE := P_ORG_WHERE || FND_MO_REPORTING_API.GET_PREDICATE('JPVS'
353                                                      ,NULL
354                                                      ,NULL);
355     P_ORG_WHERE := P_ORG_WHERE || FND_MO_REPORTING_API.GET_PREDICATE('APA'
356                                                      ,NULL
357                                                      ,NULL);
358     P_ORG_WHERE := P_ORG_WHERE || FND_MO_REPORTING_API.GET_PREDICATE('PVSA'
359                                                      ,NULL
360                                                      ,NULL);
361     P_ORG_WHERE := P_ORG_WHERE || FND_MO_REPORTING_API.GET_PREDICATE('APSA'
362                                                      ,NULL
363                                                      ,NULL);
364     RETURN (TRUE);
365   END AFTERPFORM;
366 
367   FUNCTION AFTERREPORT RETURN BOOLEAN IS
368   BEGIN
369     /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
370     RETURN (TRUE);
371   END AFTERREPORT;
372 
373   FUNCTION CF_SERVICE_TYPEFORMULA(SERVICE_TYPE_CODE IN VARCHAR2) RETURN CHAR IS
374     CURSOR GET_SERVICE_TYPE_CUR IS
375       SELECT
376         DESCRIPTION DISPLAYED_FIELD
377       FROM
378         JA_LOOKUPS
379       WHERE LOOKUP_TYPE = 'JAI_SERVICE_TYPE'
380         AND LOOKUP_CODE = SERVICE_TYPE_CODE;
381     LV_SERVICE_TYPE VARCHAR2(80);
382   BEGIN
383     OPEN GET_SERVICE_TYPE_CUR;
384     FETCH GET_SERVICE_TYPE_CUR
385      INTO LV_SERVICE_TYPE;
386     CLOSE GET_SERVICE_TYPE_CUR;
387     RETURN LV_SERVICE_TYPE;
388   END CF_SERVICE_TYPEFORMULA;
389 
390   FUNCTION CF_VALUEFORMULA(INVOICE_ID IN NUMBER
391                           ,SOURCE_TYPE IN VARCHAR2
392                           ,ST_RATE IN NUMBER
393                           ,ST IN NUMBER
394                           ,VALUE IN NUMBER) RETURN NUMBER IS
395     LV_TAX_AMT NUMBER := 0;
396     LV_RET_VAL NUMBER := 0;
397     LV_VALUE NUMBER := 0;
398     LN_AMOUNT_REMAINING NUMBER;
399     CURSOR C_GET_AMT_REMAIN IS
400       SELECT
401         SUM(AMOUNT_REMAINING) AMOUNT_REMAINING
402       FROM
403         AP_PAYMENT_SCHEDULES_ALL
404       WHERE INVOICE_ID = CF_VALUEFORMULA.INVOICE_ID;
405   BEGIN
406     OPEN C_GET_AMT_REMAIN;
407     FETCH C_GET_AMT_REMAIN
408      INTO LN_AMOUNT_REMAINING;
409     CLOSE C_GET_AMT_REMAIN;
410     IF SOURCE_TYPE = 'AP' THEN
411       IF NVL(ST_RATE
412          ,0) <> 0 THEN
413         LV_TAX_AMT := (ST * 100) / ST_RATE;
414       END IF;
415       LV_VALUE := NVL(VALUE
416                      ,0) - NVL(LN_AMOUNT_REMAINING
417                      ,0);
418       IF NVL(VALUE
419          ,0) <> 0 THEN
420         LV_RET_VAL := (NVL(LV_VALUE
421                          ,0) * NVL(LV_TAX_AMT
422                          ,0)) / VALUE;
423       END IF;
424     ELSE
425       LV_RET_VAL := VALUE;
426     END IF;
427     RETURN (ROUND(NVL(LV_RET_VAL
428                     ,0)
429                 ,2));
430   END CF_VALUEFORMULA;
431 
432   FUNCTION CF_DESCRIPTIONFORMULA(SRC_DOC_ID IN NUMBER
433                                 ,ITEM_ID IN NUMBER
434                                 ,SOURCE_TYPE IN VARCHAR2) RETURN CHAR IS
435     CURSOR CUR_DESCRIPTION IS
436       SELECT
437         DISTINCT
438         HAOU1.NAME FROM_ORG,
439         HAOU2.NAME TO_ORG
440       FROM
441         JAI_RGM_DIS_SRC_HDRS SRC_HDRS,
442         JAI_RGM_DIS_SRC_TAXES SRC_TAXS,
443         JAI_RGM_DIS_DES_HDRS DES_HDRS,
444         JAI_RGM_DIS_DES_TAXES DES_TAXS,
445         HR_ALL_ORGANIZATION_UNITS HAOU1,
446         HR_ALL_ORGANIZATION_UNITS HAOU2
447       WHERE SRC_HDRS.TRANSFER_ID = DES_HDRS.TRANSFER_ID
448         AND SRC_HDRS.TRANSFER_ID = SRC_TAXS.TRANSFER_ID
449         AND DES_HDRS.TRANSFER_DESTINATION_ID = DES_TAXS.TRANSFER_DESTINATION_ID
450         AND DES_TAXS.TRANSFER_SOURCE_ID = SRC_TAXS.TRANSFER_SOURCE_ID
451         AND SRC_HDRS.TRANSFER_ID = SRC_DOC_ID
452         AND SRC_HDRS.PARTY_ID = HAOU1.ORGANIZATION_ID
453         AND DES_HDRS.DESTINATION_PARTY_ID = HAOU2.ORGANIZATION_ID;
454     CURSOR CUR_ITEM_DESC IS
455       SELECT
456         DISTINCT
457         DESCRIPTION
458       FROM
459         MTL_SYSTEM_ITEMS
460       WHERE INVENTORY_ITEM_ID = ITEM_ID;
461     LV_DESC VARCHAR2(300);
462     LV_TO_ORG VARCHAR2(100);
463     LV_FROM_ORG VARCHAR2(100);
464   BEGIN
465     IF SOURCE_TYPE = 'DISTRIBUTION' THEN
466       OPEN CUR_DESCRIPTION;
467       FETCH CUR_DESCRIPTION
468        INTO LV_FROM_ORG,LV_TO_ORG;
469       CLOSE CUR_DESCRIPTION;
470       LV_DESC := 'Service Distribute In' ||  'FROM' ||  LV_FROM_ORG ||  'TO' ||  LV_TO_ORG;
471     ELSIF SOURCE_TYPE = 'MANUAL' THEN
472       LV_DESC := 'MANUAL';
473     ELSIF SOURCE_TYPE = 'AP' THEN
474       OPEN CUR_ITEM_DESC;
475       FETCH CUR_ITEM_DESC
476        INTO LV_DESC;
477       CLOSE CUR_ITEM_DESC;
478     END IF;
479     RETURN LV_DESC;
480   END CF_DESCRIPTIONFORMULA;
481 
482 END JA_JAINRGCR_XMLP_PKG;
483 
484