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_JAINSTR_XMLP_PKG

Source


1 PACKAGE BODY JA_JAINSTR_XMLP_PKG AS
2 /* $Header: JAINSTRB.pls 120.1 2007/12/25 16:31:53 dwkrishn noship $ */
3   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4     CURSOR C_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     V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
13     V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
14   BEGIN
15     /*SRW.MESSAGE(1275
16                ,'Report Version is 120.4 Last modified date is 02/05/2007')*/NULL;
17     P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
18     /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
19     BEGIN
20       OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
21       FETCH C_PROGRAM_ID
22        INTO V_PROGRAM_ID,V_ENABLE_TRACE;
23       CLOSE C_PROGRAM_ID;
24       /*SRW.MESSAGE(1275
25                  ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
26       IF V_ENABLE_TRACE = 'Y' THEN
27         EXECUTE IMMEDIATE
28           'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
29       END IF;
30     EXCEPTION
31       WHEN OTHERS THEN
32         /*SRW.MESSAGE(1275
33                    ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
34     END;
35     LP_FROM_DATE:=to_char(P_FROM_DATE,'DD-MON-YYYY');
36     LP_TO_DATE:=to_char(P_TO_DATE,'DD-MON-YYYY');
37     RETURN (TRUE);
38   END BEFOREREPORT;
39 
40   FUNCTION CF_RECEIPT_AMOUNTFORMULA(INVOICE_ID IN NUMBER) RETURN NUMBER IS
41     CURSOR FETCH_RECEIPT_AMOUNT IS
42       SELECT
43         SUM(AMOUNT_APPLIED)
44       FROM
45         AR_RECEIVABLE_APPLICATIONS_ALL
46       WHERE APPLIED_CUSTOMER_TRX_ID = INVOICE_ID
47         AND APPLICATION_TYPE = 'CASH'
48         AND STATUS in ( 'APP' , 'UNAPP' );
49     LN_RECEIPT_AMOUNT AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE;
50   BEGIN
51     OPEN FETCH_RECEIPT_AMOUNT;
52     FETCH FETCH_RECEIPT_AMOUNT
53      INTO LN_RECEIPT_AMOUNT;
54     CLOSE FETCH_RECEIPT_AMOUNT;
55     IF P_DEBUG_FLAG = 'Y' THEN
56       /*SRW.MESSAGE('106'
57                  ,'Value of customer_trx_id ' || INVOICE_ID)*/NULL;
58       /*SRW.MESSAGE('106'
59                  ,'Value of receipt amount ' || LN_RECEIPT_AMOUNT)*/NULL;
60     END IF;
61     RETURN (ROUND(LN_RECEIPT_AMOUNT
62                 ,0));
63   EXCEPTION
64     WHEN OTHERS THEN
65       /*SRW.MESSAGE('106'
66                  ,' Exception in receipt maount' || SQLERRM)*/NULL;
67       RETURN (NULL);
68   END CF_RECEIPT_AMOUNTFORMULA;
69 
70   FUNCTION CF_1FORMULA(INVOICE_ID_1 IN NUMBER
71                       ,ORG_ID1 IN NUMBER
72                       ,SERVICE_BASIS IN NUMBER
73                       ,SERVICE_REC_AMOUNT IN NUMBER
74                       ,CESS_REC_AMOUNT IN NUMBER
75                       ,SERVICE_PAYABLE_AMOUNT IN NUMBER
76                       ,CESS_PAYABLE_AMOUNT IN NUMBER) RETURN CHAR IS
77     L_INVOICE_NUMBER VARCHAR2(25);
78     CURSOR FETCH_INV_NUMBER IS
79       SELECT
80         TRX_NUMBER
81       FROM
82         JAI_AR_TRXS
83       WHERE CUSTOMER_TRX_ID = INVOICE_ID_1;
84     CURSOR FETCH_AMT_DETAILS IS
85       SELECT
86         DISTINCT
87         TRX.*
88       FROM
89         RA_CUSTOMER_TRX_ALL TRX,
90         RA_CUST_TRX_TYPES_ALL TRX_TYPE,
91         JAI_AR_TRXS JTRX,
92         JAI_AR_TRX_LINES JTRXL,
93         JAI_AR_TRX_TAX_LINES JTRXTL,
94         JAI_CMN_TAXES_ALL JTC
95       WHERE TRX.ORG_ID = ORG_ID1
96         AND TRX.COMPLETE_FLAG = 'Y'
97         AND TRX.PREVIOUS_CUSTOMER_TRX_ID = INVOICE_ID_1
98         AND TRX.CUSTOMER_TRX_ID = JTRX.CUSTOMER_TRX_ID
99         AND UPPER(TRX_TYPE.TYPE) = 'CM'
100         AND TRX_TYPE.CUST_TRX_TYPE_ID = TRX.CUST_TRX_TYPE_ID
101         AND TRX_TYPE.ORG_ID = TRX.ORG_ID
102         AND JTRX.CUSTOMER_TRX_ID = JTRXL.CUSTOMER_TRX_ID
103         AND JTRXL.CUSTOMER_TRX_LINE_ID = JTRXTL.LINK_TO_CUST_TRX_LINE_ID
104         AND JTRXTL.TAX_ID = JTC.TAX_ID
105         AND UPPER(JTC.TAX_TYPE) IN ( 'SERVICE' , 'SERVICE_EDUCATION_CESS' , 'SERVICE_SH_EDU_CESS' );
106     CURSOR FETCH_INV_AMT IS
107       SELECT
108         SUM(NVL(AMOUNT
109                ,0))
110       FROM
111         RA_CUST_TRX_LINE_GL_DIST_ALL
112       WHERE CUSTOMER_TRX_ID = INVOICE_ID_1
113         AND ACCOUNT_CLASS = 'REC';
114     CURSOR CUR_SH_CESS_REC IS
115       SELECT
116         TAXABLE_BASIS SH_CESS_BASIS,
117         RECOVERABLE_PTG SH_CESS_REC_PERCENT,
118         RECOVERABLE_AMOUNT SH_CESS_REC_AMOUNT,
119         RECOVERED_AMOUNT SH_CESS_PAYABLE_AMOUNT
120       FROM
121         JAI_RGM_TRX_REFS
122       WHERE SOURCE = 'AR'
123         AND INVOICE_ID = INVOICE_ID_1
124         AND TAX_TYPE = 'SERVICE_SH_EDU_CESS';
125     LN_INV_SH_CESS_BASIS JAI_RGM_TRX_REFS.TAXABLE_BASIS%TYPE;
126     LN_INV_SH_CESS_REC_PERCENT JAI_RGM_TRX_REFS.RECOVERABLE_PTG%TYPE;
127     LN_INV_SH_CESS_AMOUNT JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
128     LN_INV_SH_CESS_PAYABLE_AMOUNT JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE;
129     LN_CM_SH_CESS_AMT JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
130     LN_INV_GROSS_AMT RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT%TYPE;
131     LN_INV_TAXABLE_BASIS JAI_RGM_TRX_REFS.TAXABLE_BASIS%TYPE;
132     LN_INV_SERVICE_AMT JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
133     LN_INV_CESS_AMT JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
134     LN_INV_SERVICE_PAY JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
135     LN_CM_GROSS_AMT RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT%TYPE;
136     LN_CM_GROSS_AMT_TMP RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT%TYPE;
137     LN_CM_TAXABLE_BASIS JAI_RGM_TRX_REFS.TAXABLE_BASIS%TYPE;
138     LN_CM_SERVICE_AMT JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
139     LN_CM_CESS_AMT JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
140     LN_CM_SERVICE_PAY JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE;
141   BEGIN
142     OPEN FETCH_INV_NUMBER;
143     FETCH FETCH_INV_NUMBER
144      INTO L_INVOICE_NUMBER;
145     CLOSE FETCH_INV_NUMBER;
146     IF P_DEBUG_FLAG = 'Y' THEN
147       /*SRW.MESSAGE('101'
148                  ,' Customer trx id ' || INVOICE_ID)*/NULL;
149       /*SRW.MESSAGE('101'
150                  ,'Invoice number ' || L_INVOICE_NUMBER)*/NULL;
151     END IF;
152     BEGIN
153       OPEN FETCH_INV_AMT;
154       FETCH FETCH_INV_AMT
155        INTO LN_INV_GROSS_AMT;
156       CLOSE FETCH_INV_AMT;
157       OPEN CUR_SH_CESS_REC;
158       FETCH CUR_SH_CESS_REC
159        INTO LN_INV_SH_CESS_BASIS,LN_INV_SH_CESS_REC_PERCENT,LN_INV_SH_CESS_AMOUNT,LN_INV_SH_CESS_PAYABLE_AMOUNT;
160       CLOSE CUR_SH_CESS_REC;
161       LN_INV_TAXABLE_BASIS := NVL(SERVICE_BASIS
162                                  ,0);
163       LN_INV_SERVICE_AMT := NVL(SERVICE_REC_AMOUNT
164                                ,0);
165       LN_INV_CESS_AMT := NVL(CESS_REC_AMOUNT
166                             ,0);
167       LN_INV_SERVICE_PAY := NVL(SERVICE_PAYABLE_AMOUNT
168                                ,0) + NVL(CESS_PAYABLE_AMOUNT
169                                ,0) + NVL(LN_INV_SH_CESS_PAYABLE_AMOUNT
170                                ,0);
171       IF P_DEBUG_FLAG = 'Y' THEN
172         /*SRW.MESSAGE('101'
173                    ,' Inv - Customer trx id ' || INVOICE_ID)*/NULL;
174         /*SRW.MESSAGE('102'
175                    ,' Inv - gross amt  ' || LN_INV_GROSS_AMT)*/NULL;
176         /*SRW.MESSAGE('103'
177                    ,' Inv - taxable basis ' || LN_INV_TAXABLE_BASIS)*/NULL;
178         /*SRW.MESSAGE('104'
179                    ,' inv - service amt ' || LN_INV_SERVICE_AMT)*/NULL;
180         /*SRW.MESSAGE('105'
181                    ,' inv - cess amt ' || LN_INV_CESS_AMT)*/NULL;
182         /*SRW.MESSAGE('106'
183                    ,' inv - cess pay ' || LN_INV_SERVICE_PAY)*/NULL;
184         /*SRW.MESSAGE('107'
185                    ,' inv - sh cess amt ' || LN_INV_SH_CESS_AMOUNT)*/NULL;
186       END IF;
187       LN_CM_GROSS_AMT := 0;
188       LN_CM_TAXABLE_BASIS := 0;
189       LN_CM_SERVICE_AMT := 0;
190       LN_CM_CESS_AMT := 0;
191       LN_CM_SERVICE_PAY := 0;
192       LN_CM_SH_CESS_AMT := 0;
193       FOR CM_inv IN FETCH_AMT_DETAILS LOOP
194         FOR CM_tot_amt IN (SELECT
195                              SUM(NVL(AMOUNT
196                                     ,0)) AMOUNT
197                            FROM
198                              RA_CUST_TRX_LINE_GL_DIST_ALL
199                            WHERE CUSTOMER_TRX_ID = CM_INV.CUSTOMER_TRX_ID
200                              AND ACCOUNT_CLASS = 'REC') LOOP
201           LN_CM_GROSS_AMT_TMP := CM_TOT_AMT.AMOUNT;
202           /*SRW.MESSAGE('108'
203                      ,' CM - Customer trx id ' || CM_INV.CUSTOMER_TRX_ID)*/NULL;
204         END LOOP;
205         LN_CM_GROSS_AMT := LN_CM_GROSS_AMT + NVL(LN_CM_GROSS_AMT_TMP
206                               ,0);
207         FOR amt_det IN (SELECT
208                           JRTR1.ORGANIZATION_ID ORG_ID1,
209                           JRTR1.INVOICE_ID INVOICE_ID,
210                           JRTR1.TAXABLE_BASIS SERVICE_BASIS,
211                           JRTR1.RECOVERABLE_PTG SERVICE_REC_PERCENT,
212                           JRTR1.RECOVERABLE_AMOUNT SERVICE_REC_AMOUNT,
213                           JRTR1.RECOVERED_AMOUNT SERVICE_PAYABLE_AMOUNT,
214                           JRTR2.TAXABLE_BASIS CESS_BASIS,
215                           JRTR2.RECOVERABLE_PTG CESS_REC_PERCENT,
216                           JRTR2.RECOVERABLE_AMOUNT CESS_REC_AMOUNT,
217                           JRTR2.RECOVERED_AMOUNT CESS_PAYABLE_AMOUNT
218                         FROM
219                           JAI_RGM_TRX_REFS JRTR1,
220                           JAI_RGM_TRX_REFS JRTR2
221                         WHERE JRTR1.SOURCE = 'AR'
222                           AND JRTR1.INVOICE_ID = jrtr2.invoice_id (+)
223                           AND JRTR1.INVOICE_ID = CM_INV.CUSTOMER_TRX_ID
224                           AND JRTR1.TAX_TYPE = 'Service'
225                           AND JRTR2.TAX_TYPE = 'SERVICE_EDUCATION_CESS') LOOP
226           LN_CM_TAXABLE_BASIS := LN_CM_TAXABLE_BASIS + NVL(AMT_DET.SERVICE_BASIS
227                                     ,0);
228           LN_CM_SERVICE_AMT := LN_CM_SERVICE_AMT + NVL(AMT_DET.SERVICE_REC_AMOUNT
229                                   ,0);
230           LN_CM_CESS_AMT := LN_CM_CESS_AMT + NVL(AMT_DET.CESS_REC_AMOUNT
231                                ,0);
232           LN_CM_SERVICE_PAY := LN_CM_SERVICE_PAY + NVL(AMT_DET.SERVICE_PAYABLE_AMOUNT
233                                   ,0) + NVL(AMT_DET.CESS_PAYABLE_AMOUNT
234                                   ,0);
235           IF P_DEBUG_FLAG = 'Y' THEN
236             /*SRW.MESSAGE('109'
237                        ,' CM - gross amt  ' || LN_CM_GROSS_AMT)*/NULL;
238             /*SRW.MESSAGE('110'
239                        ,' CM - Tax basis ' || LN_CM_TAXABLE_BASIS)*/NULL;
240             /*SRW.MESSAGE('111'
241                        ,' CM - Service  ' || LN_CM_SERVICE_AMT)*/NULL;
242             /*SRW.MESSAGE('112'
243                        ,' CM - CESS ' || LN_CM_CESS_AMT)*/NULL;
244             /*SRW.MESSAGE('113'
245                        ,' CM - Service PAY ' || LN_CM_SERVICE_PAY)*/NULL;
246           END IF;
247         END LOOP;
248         FOR sh_amt_det IN CUR_SH_CESS_REC LOOP
249           LN_CM_CESS_AMT := LN_CM_SH_CESS_AMT + NVL(SH_AMT_DET.SH_CESS_REC_AMOUNT
250                                ,0);
251           IF P_DEBUG_FLAG = 'Y' THEN
252             /*SRW.MESSAGE('114'
253                        ,' CM - SH CESS  ' || LN_CM_SH_CESS_AMT)*/NULL;
254           END IF;
255         END LOOP;
256       END LOOP;
257       LN_INV_GROSS_AMT := LN_INV_GROSS_AMT + LN_CM_GROSS_AMT;
258       LN_INV_TAXABLE_BASIS := LN_INV_TAXABLE_BASIS + LN_CM_TAXABLE_BASIS;
259       LN_INV_SERVICE_AMT := LN_INV_SERVICE_AMT + LN_CM_SERVICE_AMT;
260       LN_INV_CESS_AMT := LN_INV_CESS_AMT + LN_CM_CESS_AMT;
261       LN_INV_SERVICE_PAY := LN_INV_SERVICE_PAY + LN_CM_SERVICE_PAY;
262       LN_INV_SH_CESS_AMOUNT := LN_INV_SH_CESS_AMOUNT + LN_CM_CESS_AMT;
263       IF P_DEBUG_FLAG = 'Y' THEN
264         /*SRW.MESSAGE('115'
265                    ,' Inv - Customer trx id ' || INVOICE_ID)*/NULL;
266         /*SRW.MESSAGE('116'
267                    ,' Inv - gross amt  ' || LN_INV_GROSS_AMT)*/NULL;
268         /*SRW.MESSAGE('117'
269                    ,' Inv - Tax basis ' || LN_INV_TAXABLE_BASIS)*/NULL;
270         /*SRW.MESSAGE('118'
271                    ,' Inv - service  ' || LN_INV_SERVICE_AMT)*/NULL;
272         /*SRW.MESSAGE('119'
273                    ,' Inv - cesd ' || LN_INV_CESS_AMT)*/NULL;
274         /*SRW.MESSAGE('120'
275                    ,' Inv - Service pay ' || LN_INV_SERVICE_PAY)*/NULL;
276         /*SRW.MESSAGE('119'
277                    ,' Inv - shcesd ' || LN_INV_SH_CESS_AMOUNT)*/NULL;
278       END IF;
279       CP_GROSS_INVOICE_AMOUNT := ROUND(NVL(LN_INV_GROSS_AMT
280                                           ,0)
281                                       ,2);
282       CP_TAXABLE_BASIS := ROUND(NVL(LN_INV_TAXABLE_BASIS
283                                    ,0)
284                                ,2);
285       CP_SERVICE_TAX_AMOUNT := ROUND(NVL(LN_INV_SERVICE_AMT
286                                         ,0)
287                                     ,2);
288       CP_CESS_AMOUNT := ROUND(NVL(LN_INV_CESS_AMT
289                                  ,0)
290                              ,2);
291       CP_SERVICE_TAX_PAY := ROUND(NVL(LN_INV_SERVICE_PAY
292                                      ,0)
293                                  ,2);
294       CP_SH_CESS_AMOUNT := ROUND(NVL(LN_INV_SH_CESS_AMOUNT
295                                     ,0)
296                                 ,2);
297     EXCEPTION
298       WHEN OTHERS THEN
299         /*SRW.MESSAGE('1000'
300                    ,' In exception of amount fields' || SQLERRM)*/NULL;
301     END;
302     RETURN (L_INVOICE_NUMBER);
303   EXCEPTION
304     WHEN OTHERS THEN
305       /*SRW.MESSAGE('101'
306                  ,' Invoice number exception' || SQLERRM)*/NULL;
307       RETURN (NULL);
308   END CF_1FORMULA;
309 
310   FUNCTION CF_SERVICE_TYPEFORMULA(SERVICE_REF_ID IN NUMBER) RETURN CHAR IS
311     CURSOR GET_SERVICE_TYPE_CUR(CP_SERVICE_TYPE_CODE IN VARCHAR2) IS
312       SELECT
313         DESCRIPTION
314       FROM
315         JA_LOOKUPS
316       WHERE LOOKUP_TYPE = 'JAI_SERVICE_TYPE'
317         AND LOOKUP_CODE = CP_SERVICE_TYPE_CODE;
318     LV_SERVICE_TYPE_CODE VARCHAR2(30);
319     LV_SERVICE_TYPE VARCHAR2(80);
320   BEGIN
321     LV_SERVICE_TYPE_CODE := JAI_TRX_REPO_EXTRACT_PKG.GET_SERVICE_TYPE_FROM_REF(SERVICE_REF_ID);
322     OPEN GET_SERVICE_TYPE_CUR(LV_SERVICE_TYPE_CODE);
323     FETCH GET_SERVICE_TYPE_CUR
324      INTO LV_SERVICE_TYPE;
325     CLOSE GET_SERVICE_TYPE_CUR;
326     RETURN LV_SERVICE_TYPE;
327   END CF_SERVICE_TYPEFORMULA;
328 
329   FUNCTION AFTERREPORT RETURN BOOLEAN IS
330   BEGIN
331     /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
332     RETURN (TRUE);
333   END AFTERREPORT;
334 
335   FUNCTION P_TO_DATEVALIDTRIGGER RETURN BOOLEAN IS
336   BEGIN
337     RETURN (TRUE);
338   END P_TO_DATEVALIDTRIGGER;
339 
340   FUNCTION CP_GROSS_INVOICE_AMOUNT_P RETURN NUMBER IS
341   BEGIN
342     RETURN CP_GROSS_INVOICE_AMOUNT;
343   END CP_GROSS_INVOICE_AMOUNT_P;
344 
345   FUNCTION CP_TAXABLE_BASIS_P RETURN NUMBER IS
346   BEGIN
347     RETURN CP_TAXABLE_BASIS;
348   END CP_TAXABLE_BASIS_P;
349 
350   FUNCTION CP_SERVICE_TAX_AMOUNT_P RETURN NUMBER IS
351   BEGIN
352     RETURN CP_SERVICE_TAX_AMOUNT;
353   END CP_SERVICE_TAX_AMOUNT_P;
354 
355   FUNCTION CP_CESS_AMOUNT_P RETURN NUMBER IS
356   BEGIN
357     RETURN CP_CESS_AMOUNT;
358   END CP_CESS_AMOUNT_P;
359 
360   FUNCTION CP_SERVICE_TAX_PAY_P RETURN NUMBER IS
361   BEGIN
362     RETURN CP_SERVICE_TAX_PAY;
363   END CP_SERVICE_TAX_PAY_P;
364 
365   FUNCTION CP_SH_CESS_AMOUNT_P RETURN NUMBER IS
366   BEGIN
367     RETURN CP_SH_CESS_AMOUNT;
368   END CP_SH_CESS_AMOUNT_P;
369 
370 END JA_JAINSTR_XMLP_PKG;
371 
372