1 PACKAGE BODY JA_JAINSITR_XMLP_PKG AS
2 /* $Header: JAINSITRB.pls 120.1 2007/12/25 16:28:37 dwkrishn noship $ */
3 FUNCTION SERIAL_FFORMULA RETURN NUMBER IS
4 BEGIN
5 SERIAL_NUMBER := NVL(SERIAL_NUMBER
6 ,0) + 1;
7 RETURN 1;
8 END SERIAL_FFORMULA;
9
10 FUNCTION EXCISE_TAXFORMULA(L_CUSTOMER_TRX_ID IN NUMBER
11 ,L_SUPP_INV_TYPE IN VARCHAR2) RETURN NUMBER IS
12 CURSOR EXCISE(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
13 SELECT
14 SUM(DIFF_AMT) EXCISE
15 FROM
16 JAI_AR_SUP_TAXES A,
17 JAI_AR_SUP_LINES B,
18 JAI_AR_SUP_HDRS_ALL C
19 WHERE C.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
20 AND C.SUPP_INV_TYPE = B.SUP_INV_TYPE
21 AND B.CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
22 AND B.SUP_INV_TYPE = A.SUP_INV_TYPE
23 AND C.CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID
24 AND C.SUPP_INV_TYPE = L_SUPP_INV_TYPE
25 AND EXISTS (
26 SELECT
27 1
28 FROM
29 JAI_CMN_TAXES_ALL D
30 WHERE TAX_TYPE LIKE CP_TAX_TYPE
31 AND D.TAX_ID = A.NEW_TAX_ID )
32 GROUP BY
33 C.CUSTOMER_TRX_ID,
34 C.SUPP_INV_TYPE;
35 V_EXCISE NUMBER;
36 BEGIN
37 OPEN EXCISE('%Excise%');
38 FETCH EXCISE
39 INTO V_EXCISE;
40 CLOSE EXCISE;
41 RETURN NVL(V_EXCISE
42 ,0);
43 END EXCISE_TAXFORMULA;
44
45 FUNCTION CF_5FORMULA(L_CUSTOMER_TRX_ID IN NUMBER
46 ,L_SUPP_INV_TYPE IN VARCHAR2) RETURN NUMBER IS
47 CURSOR SALES(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
48 SELECT
49 SUM(DIFF_AMT) SALES
50 FROM
51 JAI_AR_SUP_TAXES A,
52 JAI_AR_SUP_LINES B,
53 JAI_AR_SUP_HDRS_ALL C
54 WHERE C.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
55 AND C.SUPP_INV_TYPE = B.SUP_INV_TYPE
56 AND B.CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
57 AND B.SUP_INV_TYPE = A.SUP_INV_TYPE
58 AND C.CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID
59 AND C.SUPP_INV_TYPE = L_SUPP_INV_TYPE
60 AND EXISTS (
61 SELECT
62 1
63 FROM
64 JAI_CMN_TAXES_ALL D
65 WHERE TAX_TYPE LIKE CP_TAX_TYPE
66 AND D.TAX_ID = A.NEW_TAX_ID )
67 GROUP BY
68 C.CUSTOMER_TRX_ID,
69 C.SUPP_INV_TYPE;
70 V_SALES NUMBER;
71 BEGIN
72 OPEN SALES('%Sales Tax%');
73 FETCH SALES
74 INTO V_SALES;
75 CLOSE SALES;
76 RETURN NVL(V_SALES
77 ,0);
78 END CF_5FORMULA;
79
80 FUNCTION CSTFORMULA(L_CUSTOMER_TRX_ID IN NUMBER
81 ,L_SUPP_INV_TYPE IN VARCHAR2) RETURN NUMBER IS
82 CURSOR CST(CP_TAX_TYPE IN JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE) IS
83 SELECT
84 SUM(DIFF_AMT) EXCISE
85 FROM
86 JAI_AR_SUP_TAXES A,
87 JAI_AR_SUP_LINES B,
88 JAI_AR_SUP_HDRS_ALL C
89 WHERE C.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
90 AND C.SUPP_INV_TYPE = B.SUP_INV_TYPE
91 AND B.CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
92 AND B.SUP_INV_TYPE = A.SUP_INV_TYPE
93 AND C.CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID
94 AND C.SUPP_INV_TYPE = L_SUPP_INV_TYPE
95 AND EXISTS (
96 SELECT
97 1
98 FROM
99 JAI_CMN_TAXES_ALL D
100 WHERE TAX_TYPE LIKE CP_TAX_TYPE
101 AND D.TAX_ID = A.NEW_TAX_ID )
102 GROUP BY
103 C.CUSTOMER_TRX_ID,
104 C.SUPP_INV_TYPE;
105 V_CST NUMBER;
106 BEGIN
107 OPEN CST('%CST%');
108 FETCH CST
109 INTO V_CST;
110 CLOSE CST;
111 RETURN NVL(V_CST
112 ,0);
113 END CSTFORMULA;
114
115 FUNCTION OTHERS_FORMULA(L_CUSTOMER_TRX_ID IN NUMBER
116 ,L_SUPP_INV_TYPE IN VARCHAR2) RETURN NUMBER IS
117 LV_EXCISE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
118 LV_CST JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
119 LV_SALES_TAX JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE;
120 CURSOR OTHERS IS
121 SELECT
122 SUM(DIFF_AMT) OTHER
123 FROM
124 JAI_AR_SUP_TAXES A,
125 JAI_AR_SUP_LINES B,
126 JAI_AR_SUP_HDRS_ALL C
127 WHERE C.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
128 AND C.SUPP_INV_TYPE = B.SUP_INV_TYPE
129 AND B.CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
130 AND B.SUP_INV_TYPE = A.SUP_INV_TYPE
131 AND C.CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID
132 AND C.SUPP_INV_TYPE = L_SUPP_INV_TYPE
133 AND EXISTS (
134 SELECT
135 1
136 FROM
137 JAI_CMN_TAXES_ALL D
138 WHERE TAX_TYPE not LIKE LV_EXCISE
139 AND TAX_TYPE NOT LIKE LV_CST
140 AND TAX_TYPE not like LV_SALES_TAX
141 AND D.TAX_ID = A.NEW_TAX_ID )
142 GROUP BY
143 C.CUSTOMER_TRX_ID,
144 C.SUPP_INV_TYPE;
145 V_OTHERS NUMBER;
146 BEGIN
147 LV_EXCISE := '%Excise%';
148 LV_CST := '%CST%';
149 LV_SALES_TAX := '%Sales Tax%';
150 OPEN OTHERS;
151 FETCH OTHERS
152 INTO V_OTHERS;
153 CLOSE OTHERS;
154 RETURN NVL(V_OTHERS
155 ,0);
156 END OTHERS_FORMULA;
157
158 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
159 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
160 SELECT
161 CONCURRENT_PROGRAM_ID,
162 NVL(ENABLE_TRACE
163 ,'N')
164 FROM
165 FND_CONCURRENT_REQUESTS
166 WHERE REQUEST_ID = P_REQUEST_ID;
167 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
168 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
169 BEGIN
170 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
171 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
172 /*SRW.MESSAGE(1275
173 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
174 BEGIN
175 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
176 FETCH C_PROGRAM_ID
177 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
178 CLOSE C_PROGRAM_ID;
179 /*SRW.MESSAGE(1275
180 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
181 IF V_ENABLE_TRACE = 'Y' THEN
182 EXECUTE IMMEDIATE
183 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
184 END IF;
185 RETURN (TRUE);
186 EXCEPTION
187 WHEN OTHERS THEN
188 /*SRW.MESSAGE(1275
189 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
190 END;
191 END BEFOREREPORT;
192
193 FUNCTION AFTERREPORT RETURN BOOLEAN IS
194 BEGIN
195 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
196 RETURN (TRUE);
197 END AFTERREPORT;
198
199 FUNCTION SERIAL_NUMBER_P RETURN NUMBER IS
200 BEGIN
201 RETURN SERIAL_NUMBER;
202 END SERIAL_NUMBER_P;
203
204 END JA_JAINSITR_XMLP_PKG;
205
206
[Home] [Help]