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_JAINTSLS_XMLP_PKG

Source


1 PACKAGE BODY JA_JAINTSLS_XMLP_PKG AS
2 /* $Header: JAINTSLSB.pls 120.1 2007/12/25 16:32:25 dwkrishn noship $ */
3   FUNCTION CF_1FORMULA(LINE_AMOUNT IN NUMBER) RETURN NUMBER IS
4     LINE_AMT NUMBER(17) := 0;
5   BEGIN
6     LINE_AMT := LINE_AMT + LINE_AMOUNT;
7     RETURN (LINE_AMT);
8   END CF_1FORMULA;
9 
10   FUNCTION C_LINE_TOTAL_W_TAXFORMULA(CS_TAX_TOTAL_1 IN NUMBER
11                                     ,CS_LINE_TOTAL_WO_TAX_1 IN NUMBER) RETURN NUMBER IS
12     LINE_TOTAL NUMBER(17) := 0;
13   BEGIN
14     LINE_TOTAL := LINE_TOTAL + CS_TAX_TOTAL_1 + CS_LINE_TOTAL_WO_TAX_1;
15     RETURN (LINE_TOTAL);
16   END C_LINE_TOTAL_W_TAXFORMULA;
17 
18   FUNCTION P_END_DATEVALIDTRIGGER RETURN BOOLEAN IS
19   BEGIN
20     IF P_END_DATE IS NULL THEN
21       P_END_DATE := TRUNC(SYSDATE);
22     END IF;
23     RETURN (TRUE);
24   END P_END_DATEVALIDTRIGGER;
25 
26   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
27     CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
28       SELECT
29         CONCURRENT_PROGRAM_ID,
30         NVL(ENABLE_TRACE
31            ,'N')
32       FROM
33         FND_CONCURRENT_REQUESTS
34       WHERE REQUEST_ID = P_REQUEST_ID;
35     V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
36     V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
37     V_ORG_ID NUMBER;
38     LV_SALES JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
39     LV_CST JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
40     a boolean;
41   BEGIN
42   a:=P_END_DATEVALIDTRIGGER;
43   LP_END_DATE:=P_END_DATE;
44     V_ORG_ID := FND_PROFILE.VALUE('ORG_ID');
45     P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
46 
47     BEGIN
48       OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
49       FETCH C_PROGRAM_ID
50        INTO V_PROGRAM_ID,V_ENABLE_TRACE;
51       CLOSE C_PROGRAM_ID;
52             IF V_ENABLE_TRACE = 'Y' THEN
53         EXECUTE IMMEDIATE
54           'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
55       END IF;
56     EXCEPTION
57       WHEN OTHERS THEN
58        NULL;
59     END;
60     IF P_START_DATE IS NULL THEN
61       LV_SALES := '%SALES%';
62       LV_CST := '%CST%';
63       SELECT
64         MIN(CUTX.TRX_DATE)
65       INTO P_START_DATE
66       FROM
67         RA_CUSTOMER_TRX_ALL CUTX,
68         JAI_AR_TRXS JCUTX,
69         RA_CUSTOMER_TRX_LINES_ALL CUTXL,
70         JAI_CMN_CUS_ADDRESSES JCUAD,
71         JAI_AR_TRX_TAX_LINES JRCTAXL,
72         JAI_CMN_TAXES_ALL JITC,
73         AR_PAYMENT_SCHEDULES_ALL ARPS
74       WHERE JCUTX.ORGANIZATION_ID = P_ORGANIZATION_ID
75         AND CUTX.CUSTOMER_TRX_ID = JCUTX.CUSTOMER_TRX_ID
76         AND CUTXL.CUSTOMER_TRX_ID = CUTX.CUSTOMER_TRX_ID
77         AND JRCTAXL.LINK_TO_CUST_TRX_LINE_ID = CUTXL.CUSTOMER_TRX_LINE_ID
78         AND JITC.TAX_ID = JRCTAXL.TAX_ID
79         AND ( UPPER(JITC.TAX_TYPE) like LV_SALES
80       OR UPPER(JITC.TAX_TYPE) like LV_CST )
81         AND UPPER(CUTX.COMPLETE_FLAG) = 'Y'
82         AND ARPS.CUSTOMER_TRX_ID = CUTX.CUSTOMER_TRX_ID
83         AND jcuad.customer_id (+) = CUTX.SOLD_TO_CUSTOMER_ID;
84     END IF;
85     RETURN (TRUE);
86   END BEFOREREPORT;
87 
88   FUNCTION CF_1FORMULA0004(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
89   BEGIN
90     DECLARE
91       EXCISE_TAX NUMBER;
92       LV_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
93       LV_ADDL_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
94       LV_OTHER_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
95       LV_EXC_EDU_CESS JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
96       LV_SH_EXC_EDU_CESS JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
97     BEGIN
98       LV_EXCISE := 'EXCISE';
99       LV_ADDL_EXCISE := 'ADDL. EXCISE';
100       LV_OTHER_EXCISE := 'OTHER EXCISE';
101       LV_EXC_EDU_CESS := 'EXCISE_EDUCATION_CESS';
102       LV_SH_EXC_EDU_CESS := 'EXCISE_SH_EDU_CESS';
103       SELECT
104         SUM(A.TAX_AMOUNT)
105       INTO EXCISE_TAX
106       FROM
107         JAI_AR_TRX_TAX_LINES A,
108         JAI_CMN_TAXES_ALL B
109       WHERE A.LINK_TO_CUST_TRX_LINE_ID = CF_1FORMULA0004.CUSTOMER_TRX_LINE_ID
110         AND A.TAX_ID = B.TAX_ID
111         AND UPPER(B.TAX_TYPE) IN ( LV_EXCISE , LV_ADDL_EXCISE , LV_OTHER_EXCISE , LV_EXC_EDU_CESS , LV_SH_EXC_EDU_CESS );
112       CP_EXCISE_1 := EXCISE_TAX;
113       RETURN (NVL(EXCISE_TAX
114                 ,0));
115     END;
116   END CF_1FORMULA0004;
117 
118   FUNCTION CF_2FORMULA(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
119   BEGIN
120     DECLARE
121       OTHER_TAX NUMBER;
122       LV_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
123       LV_ADDL_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
124       LV_OTHER_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
125       LV_EXC_EDU_CESS JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
126       LV_SALES_TAX JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
127       LV_CST JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
128       LV_SH_EXC_EDU_CESS JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
129     BEGIN
130       LV_EXCISE := 'EXCISE';
131       LV_ADDL_EXCISE := 'ADDL. EXCISE';
132       LV_OTHER_EXCISE := 'OTHER EXCISE';
133       LV_EXC_EDU_CESS := 'EXCISE_EDUCATION_CESS';
134       LV_SALES_TAX := 'SALES TAX';
135       LV_CST := 'CST';
136       LV_SH_EXC_EDU_CESS := 'EXCISE_SH_EDU_CESS';
137       SELECT
138         SUM(A.TAX_AMOUNT)
139       INTO OTHER_TAX
140       FROM
141         JAI_AR_TRX_TAX_LINES A,
142         JAI_CMN_TAXES_ALL B
143       WHERE A.LINK_TO_CUST_TRX_LINE_ID = CF_2FORMULA.CUSTOMER_TRX_LINE_ID
144         AND A.TAX_ID = B.TAX_ID
145         AND UPPER(B.TAX_TYPE) NOT IN ( LV_EXCISE , LV_ADDL_EXCISE , LV_OTHER_EXCISE , LV_EXC_EDU_CESS , LV_SALES_TAX , LV_CST , LV_SH_EXC_EDU_CESS );
146       CP_ADDL_OTHER_TAXES_1 := NVL(OTHER_TAX
147                                   ,0);
148       RETURN (NVL(OTHER_TAX
149                 ,0));
150     END;
151   END CF_2FORMULA;
152 
153   FUNCTION CF_1FORMULA0013(CUSTOMER_TRX_ID IN NUMBER) RETURN NUMBER IS
154     V_TOTAL_AMOUNT NUMBER;
155   BEGIN
156     SELECT
157       SUM(NVL(LINE_AMOUNT
158              ,0)) + SUM(NVL(TAX_AMOUNT
159              ,0))
160     INTO V_TOTAL_AMOUNT
161     FROM
162       JAI_AR_TRX_LINES
163     WHERE CUSTOMER_TRX_ID = CF_1FORMULA0013.CUSTOMER_TRX_ID;
164     RETURN (V_TOTAL_AMOUNT);
165   EXCEPTION
166     WHEN OTHERS THEN
167       RETURN (0);
168   END CF_1FORMULA0013;
169 
170   FUNCTION CF_2FORMULA0008(TAX_ACCOUNT_ID IN NUMBER) RETURN CHAR IS
171     CURSOR C_ACCOUNT(P_CODE_COMBINATION_ID IN NUMBER) IS
172       SELECT
173         CONCATENATED_SEGMENTS
174       FROM
175         GL_CODE_COMBINATIONS_KFV
176       WHERE CODE_COMBINATION_ID = P_CODE_COMBINATION_ID;
177     V_ACCOUNT_CODE VARCHAR2(285);
178   BEGIN
179     OPEN C_ACCOUNT(TAX_ACCOUNT_ID);
180     FETCH C_ACCOUNT
181      INTO V_ACCOUNT_CODE;
182     CLOSE C_ACCOUNT;
183     RETURN V_ACCOUNT_CODE;
184   END CF_2FORMULA0008;
185 
186   FUNCTION CF_SALES_TAXABLE_AMT_2FORMULA(TAX_TYPE IN VARCHAR2
187                                         ,STAX_RATE IN NUMBER
188                                         ,STAX_AMT IN NUMBER) RETURN NUMBER IS
189     V_SALES_TAX_BASE_AMT NUMBER := 0;
190   BEGIN
191     IF TAX_TYPE in ('Sales Tax','CST') AND NVL(STAX_RATE
192        ,0) <> 0 THEN
193       V_SALES_TAX_BASE_AMT := (STAX_AMT * 100) / STAX_RATE;
194     END IF;
195     RETURN V_SALES_TAX_BASE_AMT;
196   END CF_SALES_TAXABLE_AMT_2FORMULA;
197 
198   FUNCTION CF_TRX_NUMBER_DATE_1FORMULA(TRX_NUMBER IN VARCHAR2
199                                       ,TRX_DATE IN DATE) RETURN CHAR IS
200   BEGIN
201     RETURN TRX_NUMBER || ', ' || TO_CHAR(TRX_DATE
202                   ,'DD-MON-YYYY');
203   END CF_TRX_NUMBER_DATE_1FORMULA;
204 
205   FUNCTION CF_1FORMULA0005(CUSTOMER_TRX_ID IN NUMBER
206                           ,ORDER_LINE_ID IN VARCHAR2
207                           ,LINE_AMOUNT IN NUMBER) RETURN NUMBER IS
208     V_LINE_AMOUNT RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT%TYPE;
209     CURSOR C_LINE_AMOUNT(CP_LINE_TYPE IN RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%TYPE) IS
210       SELECT
211         SUM(NVL(EXTENDED_AMOUNT
212                ,0))
213       FROM
214         RA_CUSTOMER_TRX_LINES_ALL
215       WHERE CUSTOMER_TRX_ID = CF_1FORMULA0005.CUSTOMER_TRX_ID
216         AND LINE_TYPE = CP_LINE_TYPE
217         AND SALES_ORDER_LINE = ORDER_LINE_ID
218       GROUP BY
219         SALES_ORDER_LINE;
220     CURSOR C_CREATED_FROM IS
221       SELECT
222         CREATED_FROM
223       FROM
224         RA_CUSTOMER_TRX_ALL
225       WHERE CUSTOMER_TRX_ID = CF_1FORMULA0005.CUSTOMER_TRX_ID;
226     V_CREATED_FROM RA_CUSTOMER_TRX_ALL.CREATED_FROM%TYPE;
227     MY_EXCEPTION EXCEPTION;
228   BEGIN
229     OPEN C_CREATED_FROM;
230     FETCH C_CREATED_FROM
231      INTO V_CREATED_FROM;
232     CLOSE C_CREATED_FROM;
233     IF V_CREATED_FROM = 'RAXTRX' THEN
234       OPEN C_LINE_AMOUNT('LINE');
235       FETCH C_LINE_AMOUNT
236        INTO V_LINE_AMOUNT;
237       CLOSE C_LINE_AMOUNT;
238       IF V_LINE_AMOUNT IS NULL THEN
239         /*SRW.MESSAGE(100
240                    ,'created from ' || V_CREATED_FROM)*/NULL;
241         /*SRW.MESSAGE(101
242                    ,' Customer trx id  ' || CUSTOMER_TRX_ID)*/NULL;
243         /*SRW.MESSAGE(102
244                    ,' Sales order no  ' || ORDER_LINE_ID)*/NULL;
245         RAISE MY_EXCEPTION;
246       END IF;
247       RETURN V_LINE_AMOUNT;
248     ELSIF V_CREATED_FROM = 'ARXTWMAI' THEN
249       IF LINE_AMOUNT IS NULL THEN
250         /*SRW.MESSAGE(100
251                    ,'created from ' || V_CREATED_FROM)*/NULL;
252         /*SRW.MESSAGE(101
253                    ,' Customer trx id  ' || CUSTOMER_TRX_ID)*/NULL;
254         /*SRW.MESSAGE(102
255                    ,' Sales order no  ' || ORDER_LINE_ID)*/NULL;
256         RAISE MY_EXCEPTION;
257       END IF;
258       RETURN LINE_AMOUNT;
259     END IF;
260     RETURN NULL;
261   EXCEPTION
262     WHEN MY_EXCEPTION THEN
263       /*SRW.MESSAGE(100
264                  ,'created from ' || V_CREATED_FROM)*/NULL;
265       /*SRW.MESSAGE(101
266                  ,' Customer trx id  ' || CUSTOMER_TRX_ID)*/NULL;
267       /*SRW.MESSAGE(102
268                  ,' Sales order no  ' || ORDER_LINE_ID)*/NULL;
269       RETURN NULL;
270   END CF_1FORMULA0005;
271 
272   FUNCTION AFTERREPORT RETURN BOOLEAN IS
273   BEGIN
274     NULL;
275     RETURN (TRUE);
276   END AFTERREPORT;
277 
278   FUNCTION CF_VAT_TAXES_1(CUSTOMER_TRX_LINE_ID IN NUMBER) RETURN NUMBER IS
279     VAT_TAX NUMBER;
280   BEGIN
281     SELECT
282       SUM(A.FUNC_TAX_AMOUNT)
283     INTO VAT_TAX
284     FROM
285       JAI_AR_TRX_TAX_LINES A,
286       JAI_CMN_TAXES_ALL B
287     WHERE A.LINK_TO_CUST_TRX_LINE_ID = CF_VAT_TAXES_1.CUSTOMER_TRX_LINE_ID
288       AND A.TAX_ID = B.TAX_ID
289       AND UPPER(B.TAX_TYPE) IN (
290       SELECT
291         TAX_TYPE
292       FROM
293         JAI_REGIME_TAX_TYPES_V
294       WHERE REGIME_CODE = 'VAT' );
295     CP_VAT_TAXES_1 := NVL(VAT_TAX
296                          ,0);
297     RETURN (NVL(VAT_TAX
298               ,0));
299   END CF_VAT_TAXES_1;
300 
301   FUNCTION CP_EXCISE_1_P RETURN NUMBER IS
302   BEGIN
303     RETURN CP_EXCISE_1;
304   END CP_EXCISE_1_P;
305 
306   FUNCTION CP_ADDL_OTHER_TAXES_1_P RETURN NUMBER IS
307   BEGIN
308     RETURN CP_ADDL_OTHER_TAXES_1;
309   END CP_ADDL_OTHER_TAXES_1_P;
310 
311   FUNCTION CP_VAT_TAXES_1_P RETURN NUMBER IS
312   BEGIN
313     RETURN CP_VAT_TAXES_1;
314   END CP_VAT_TAXES_1_P;
315 
316 END JA_JAINTSLS_XMLP_PKG;
317 
318