1 PACKAGE BODY JA_JAINSTPR_XMLP_PKG AS
2 /* $Header: JAINSTPRB.pls 120.1 2007/12/25 16:31:18 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(CUSTOMER_TRX_ID IN NUMBER
11 ,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 = excise_taxformula.CUSTOMER_TRX_ID
24 AND C.SUPP_INV_TYPE = excise_taxformula.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(CUSTOMER_TRX_ID IN NUMBER
46 ,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 = cf_5formula.CUSTOMER_TRX_ID
59 AND C.SUPP_INV_TYPE = cf_5formula.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(CUSTOMER_TRX_ID IN NUMBER
81 ,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 = cstformula.CUSTOMER_TRX_ID
94 AND C.SUPP_INV_TYPE = cstformula.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(CUSTOMER_TRX_ID IN NUMBER
116 ,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 = others_formula.CUSTOMER_TRX_ID
132 AND C.SUPP_INV_TYPE = others_formula.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 GET_AUDSID IS
160 SELECT
161 A.SID,
162 A.SERIAL#,
163 B.SPID
164 FROM
165 V$SESSION A,
166 V$PROCESS B
167 WHERE AUDSID = USERENV('SESSIONID')
168 AND A.PADDR = B.ADDR;
169 CURSOR GET_DBNAME IS
170 SELECT
171 NAME
172 FROM
173 V$DATABASE;
174 AUDSID NUMBER := USERENV('SESSIONID');
175 SID NUMBER;
176 SERIAL NUMBER;
177 SPID VARCHAR2(9);
178 NAME1 VARCHAR2(25);
179 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
180 SELECT
181 CONCURRENT_PROGRAM_ID,
182 NVL(ENABLE_TRACE
183 ,'N')
184 FROM
185 FND_CONCURRENT_REQUESTS
186 WHERE REQUEST_ID = P_REQUEST_ID;
187 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
188 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
189 BEGIN
190 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
191 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
192 /*SRW.MESSAGE(1275
193 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
194 BEGIN
195 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
196 FETCH C_PROGRAM_ID
197 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
198 CLOSE C_PROGRAM_ID;
199 /*SRW.MESSAGE(1275
200 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
201 IF V_ENABLE_TRACE = 'Y' OR (NVL(P_DETAIL_TRACE
202 ,'N') = 'Y' OR NVL(P_TRACE
203 ,'N') = 'Y') THEN
204 OPEN GET_AUDSID;
205 FETCH GET_AUDSID
206 INTO SID,SERIAL,SPID;
207 CLOSE GET_AUDSID;
208 OPEN GET_DBNAME;
209 FETCH GET_DBNAME
210 INTO NAME1;
211 CLOSE GET_DBNAME;
212 /*SRW.MESSAGE(599
213 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
214 EXECUTE IMMEDIATE
215 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
216 END IF;
217 EXCEPTION
218 WHEN OTHERS THEN
219 /*SRW.MESSAGE(1275
220 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
221 END;
222 JAI_AR_SUP_TRX_PKG.PROCESS_REPORT_STPR(JAINBATCHID);
223 RETURN (TRUE);
224 END BEFOREREPORT;
225
226 FUNCTION AFTERREPORT RETURN BOOLEAN IS
227 BEGIN
228 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
229 UPDATE
230 JAI_AR_SUP_HDRS_ALL
231 SET
232 PROCESS_DATE = SYSDATE
233 WHERE PROCESS_DATE is null;
234 RETURN (TRUE);
235 END AFTERREPORT;
236
237 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
238 BEGIN
239 /*SRW.MESSAGE(202
240 ,'BEFORE PARAMETER')*/NULL;
241 RETURN (TRUE);
242 END BEFOREPFORM;
243
244 FUNCTION P_TRACEVALIDTRIGGER RETURN BOOLEAN IS
245 BEGIN
246 RETURN (TRUE);
247 END P_TRACEVALIDTRIGGER;
248
249 FUNCTION SERIAL_NUMBER_P RETURN NUMBER IS
250 BEGIN
251 RETURN SERIAL_NUMBER;
252 END SERIAL_NUMBER_P;
253
254 END JA_JAINSTPR_XMLP_PKG;
255
256
[Home] [Help]