[Home] [Help]
PACKAGE BODY: APPS.JA_JAINEXCLIABILITY_XMLP_PKG
Source
1 PACKAGE BODY JA_JAINEXCLIABILITY_XMLP_PKG AS
2 /* $Header: JAINEXCLIABILITYB.pls 120.1 2007/12/25 16:18:32 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.2 Last modified date is 25/07/2005')*/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 FOR ORG_REC IN (SELECT
36 ORGANIZATION_NAME
37 FROM
38 ORG_ORGANIZATION_DEFINITIONS
39 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID) LOOP
40 P_ORGANIZATION_NAME := ORG_REC.ORGANIZATION_NAME;
41 END LOOP;
42 FOR EC_REC IN (SELECT
43 EC_CODE,
44 EXCISE_DUTY_COMM,
45 EXCISE_DUTY_RANGE,
46 EXCISE_DUTY_DIVISION,
47 EXCISE_DUTY_CIRCLE
48 FROM
49 JAI_CMN_INVENTORY_ORGS
50 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
51 AND LOCATION_ID = P_LOCATION_ID) LOOP
52 P_EC_CODE := (EC_REC.EC_CODE);
53 P_COLLECT := (EC_REC.EXCISE_DUTY_COMM);
54 P_RANGE := (EC_REC.EXCISE_DUTY_RANGE);
55 P_DIVISION := (EC_REC.EXCISE_DUTY_DIVISION);
56 P_CIRCLE := (EC_REC.EXCISE_DUTY_CIRCLE);
57 END LOOP;
58 FOR LOC_REC IN (SELECT
59 DESCRIPTION,
60 ADDRESS_LINE_1,
61 ADDRESS_LINE_2,
62 ADDRESS_LINE_3
63 FROM
64 HR_LOCATIONS
65 WHERE LOCATION_ID = P_LOCATION_ID) LOOP
66 P_DESCRIPTION := LOC_REC.DESCRIPTION;
67 P_ADDRESS_LINE_1 := LOC_REC.ADDRESS_LINE_1;
68 P_ADDRESS_LINE_2 := LOC_REC.ADDRESS_LINE_2;
69 P_ADDRESS_LINE_3 := LOC_REC.ADDRESS_LINE_3;
70 END LOOP;
71 FOR SLNO_REC IN (SELECT
72 MIN(REGISTER_ID) LO
73 FROM
74 JAI_CMN_RG_COMP_DTLS
75 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
76 AND LOCATION_ID = P_LOCATION_ID
77 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
78 ,TRUNC(TRANSACTION_DATE))
79 AND NVL(TRUNC(P_TRN_TO_DATE)
80 ,TRUNC(TRANSACTION_DATE))
81 AND REGISTER_TYPE = 'A') LOOP
82 FOR VAL_REC IN (SELECT
83 BASIC_OPENING_BALANCE BASICOB,
84 ADDITIONAL_OPENING_BALANCE ADLOB,
85 OTHER_OPENING_BALANCE OTHOB
86 FROM
87 JAI_CMN_RG_COMP_DTLS
88 WHERE REGISTER_ID = SLNO_REC.LO) LOOP
89 P_CENOBRG23A := NVL(VAL_REC.BASICOB
90 ,0);
91 P_ADLOBRG23A := NVL(VAL_REC.ADLOB
92 ,0);
93 P_SEDOBRG23A := NVL(VAL_REC.OTHOB
94 ,0);
95 END LOOP;
96 END LOOP;
97 P_CENOBRG23A := ROUND(NVL(P_CENOBRG23A
98 ,0)
99 ,2);
100 P_ADLOBRG23A := ROUND(NVL(P_ADLOBRG23A
101 ,0)
102 ,2);
103 P_SEDOBRG23A := ROUND(NVL(P_SEDOBRG23A
104 ,0)
105 ,2);
106 FOR CR_REC IN (SELECT
107 NVL(SUM(CR_BASIC_ED)
108 ,0) CENCT,
109 NVL(SUM(CR_OTHER_ED)
110 ,0) SEDCT,
111 NVL(SUM(CR_ADDITIONAL_ED)
112 ,0) ADLCT
113 FROM
114 JAI_CMN_RG_COMP_DTLS
115 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
116 AND LOCATION_ID = P_LOCATION_ID
117 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
118 ,TRUNC(TRANSACTION_DATE))
119 AND NVL(TRUNC(P_TRN_TO_DATE)
120 ,TRUNC(TRANSACTION_DATE))
121 AND REGISTER_TYPE = 'A') LOOP
122 P_CENCTRG23A := ROUND(NVL(CR_REC.CENCT
123 ,0)
124 ,2);
125 P_SEDCTRG23A := ROUND(NVL(CR_REC.SEDCT
126 ,0)
127 ,2);
128 P_ADLCTRG23A := ROUND(NVL(CR_REC.ADLCT
129 ,0)
130 ,2);
131 END LOOP;
132 FOR DR_REC IN (SELECT
133 NVL(SUM(DR_BASIC_ED)
134 ,0) CENCU,
135 NVL(SUM(DR_OTHER_ED)
136 ,0) SEDCU,
137 NVL(SUM(DR_ADDITIONAL_ED)
138 ,0) ADLCU
139 FROM
140 JAI_CMN_RG_COMP_DTLS
141 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
142 AND LOCATION_ID = P_LOCATION_ID
143 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
144 ,TRUNC(TRANSACTION_DATE))
145 AND NVL(TRUNC(P_TRN_TO_DATE)
146 ,TRUNC(TRANSACTION_DATE))
147 AND REGISTER_TYPE = 'A') LOOP
148 P_CENCURG23A := ROUND(NVL(DR_REC.CENCU
149 ,0)
150 ,2);
151 P_SEDCURG23A := ROUND(NVL(DR_REC.SEDCU
152 ,0)
153 ,2);
154 P_ADLCURG23A := ROUND(NVL(DR_REC.ADLCU
155 ,0)
156 ,2);
157 END LOOP;
158 P_CENCBRG23A := NVL(P_CENOBRG23A
159 ,0) + NVL(P_CENCTRG23A
160 ,0) - NVL(P_CENCURG23A
161 ,0);
162 P_SEDCBRG23A := NVL(P_SEDOBRG23A
163 ,0) + NVL(P_SEDCTRG23A
164 ,0) - NVL(P_SEDCURG23A
165 ,0);
166 P_ADLCBRG23A := NVL(P_ADLOBRG23A
167 ,0) + NVL(P_ADLCTRG23A
168 ,0) - NVL(P_ADLCURG23A
169 ,0);
170 FOR SLNO_REC IN (SELECT
171 MIN(REGISTER_ID) LO
172 FROM
173 JAI_CMN_RG_COMP_DTLS
174 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
175 AND LOCATION_ID = P_LOCATION_ID
176 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
177 ,TRUNC(TRANSACTION_DATE))
178 AND NVL(TRUNC(P_TRN_TO_DATE)
179 ,TRUNC(TRANSACTION_DATE))
180 AND REGISTER_TYPE = 'C') LOOP
181 FOR VAL_REC IN (SELECT
182 BASIC_OPENING_BALANCE BASICOB,
183 ADDITIONAL_OPENING_BALANCE ADLOB,
184 OTHER_OPENING_BALANCE OTHOB
185 FROM
186 JAI_CMN_RG_COMP_DTLS
187 WHERE REGISTER_ID = SLNO_REC.LO) LOOP
188 P_CENOBRG23C := NVL(VAL_REC.BASICOB
189 ,0);
190 P_ADLOBRG23C := NVL(VAL_REC.ADLOB
191 ,0);
192 P_SEDOBRG23C := NVL(VAL_REC.OTHOB
193 ,0);
194 END LOOP;
195 END LOOP;
196 P_CENOBRG23C := ROUND(NVL(P_CENOBRG23C
197 ,0)
198 ,2);
199 P_ADLOBRG23C := ROUND(NVL(P_ADLOBRG23C
200 ,0)
201 ,2);
202 P_SEDOBRG23C := ROUND(NVL(P_SEDOBRG23C
203 ,0)
204 ,2);
205 FOR CR_REC IN (SELECT
206 NVL(SUM(CR_BASIC_ED)
207 ,0) CENCT,
208 NVL(SUM(CR_OTHER_ED)
209 ,0) SEDCT,
210 NVL(SUM(CR_ADDITIONAL_ED)
211 ,0) ADLCT
212 FROM
213 JAI_CMN_RG_COMP_DTLS
214 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
215 AND LOCATION_ID = P_LOCATION_ID
216 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
217 ,TRUNC(TRANSACTION_DATE))
218 AND NVL(TRUNC(P_TRN_TO_DATE)
219 ,TRUNC(TRANSACTION_DATE))
220 AND REGISTER_TYPE = 'C') LOOP
221 P_CENCTRG23C := ROUND(NVL(CR_REC.CENCT
222 ,0)
223 ,2);
224 P_SEDCTRG23C := ROUND(NVL(CR_REC.SEDCT
225 ,0)
226 ,2);
227 P_ADLCTRG23C := ROUND(NVL(CR_REC.ADLCT
228 ,0)
229 ,2);
230 END LOOP;
231 FOR DR_REC IN (SELECT
232 NVL(SUM(DR_BASIC_ED)
233 ,0) CENCU,
234 NVL(SUM(DR_OTHER_ED)
235 ,0) SEDCU,
236 NVL(SUM(DR_ADDITIONAL_ED)
237 ,0) ADLCU
238 FROM
239 JAI_CMN_RG_COMP_DTLS
240 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
241 AND LOCATION_ID = P_LOCATION_ID
242 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
243 ,TRUNC(TRANSACTION_DATE))
244 AND NVL(TRUNC(P_TRN_TO_DATE)
245 ,TRUNC(TRANSACTION_DATE))
246 AND REGISTER_TYPE = 'C') LOOP
247 P_CENCURG23C := ROUND(NVL(DR_REC.CENCU
248 ,0)
249 ,2);
250 P_SEDCURG23C := ROUND(NVL(DR_REC.SEDCU
251 ,0)
252 ,2);
253 P_ADLCURG23C := ROUND(NVL(DR_REC.ADLCU
254 ,0)
255 ,2);
256 END LOOP;
257 P_CENCBRG23C := NVL(P_CENOBRG23C
258 ,0) + NVL(P_CENCTRG23C
259 ,0) - NVL(P_CENCURG23C
260 ,0);
261 P_SEDCBRG23C := NVL(P_SEDOBRG23C
262 ,0) + NVL(P_SEDCTRG23C
263 ,0) - NVL(P_SEDCURG23C
264 ,0);
265 P_ADLCBRG23C := NVL(P_ADLOBRG23C
266 ,0) + NVL(P_ADLCTRG23C
267 ,0) - NVL(P_ADLCURG23C
268 ,0);
269 FOR SLNO_REC IN (SELECT
270 MIN(REGISTER_ID) LO
271 FROM
272 JAI_CMN_RG_PLA_CMP_DTLS
273 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
274 AND LOCATION_ID = P_LOCATION_ID
275 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
276 ,TRUNC(TRANSACTION_DATE))
277 AND NVL(TRUNC(P_TRN_TO_DATE)
278 ,TRUNC(TRANSACTION_DATE))) LOOP
279 FOR VAL_REC IN (SELECT
280 BASIC_OPENING_BALANCE BASICOB,
281 ADDITIONAL_OPENING_BALANCE ADLOB,
282 OTHER_OPENING_BALANCE OTHOB
283 FROM
284 JAI_CMN_RG_PLA_CMP_DTLS
285 WHERE REGISTER_ID = SLNO_REC.LO) LOOP
286 P_CENOBPLA := NVL(VAL_REC.BASICOB
287 ,0);
288 P_ADLOBPLA := NVL(VAL_REC.ADLOB
289 ,0);
290 P_SEDOBPLA := NVL(VAL_REC.OTHOB
291 ,0);
292 END LOOP;
293 END LOOP;
294 P_CENOBPLA := ROUND(NVL(P_CENOBPLA
295 ,0)
296 ,2);
297 P_ADLOBPLA := ROUND(NVL(P_ADLOBPLA
298 ,0)
299 ,2);
300 P_SEDOBPLA := ROUND(NVL(P_SEDOBPLA
301 ,0)
302 ,2);
303 FOR CR_REC IN (SELECT
304 NVL(SUM(CR_BASIC_ED)
305 ,0) CENCT,
306 NVL(SUM(CR_OTHER_ED)
307 ,0) SEDCT,
308 NVL(SUM(CR_ADDITIONAL_ED)
309 ,0) ADLCT
310 FROM
311 JAI_CMN_RG_PLA_CMP_DTLS
312 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
313 AND LOCATION_ID = P_LOCATION_ID
314 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
315 ,TRUNC(TRANSACTION_DATE))
316 AND NVL(TRUNC(P_TRN_TO_DATE)
317 ,TRUNC(TRANSACTION_DATE))) LOOP
318 P_CENCTPLA := ROUND(NVL(CR_REC.CENCT
319 ,0)
320 ,2);
321 P_SEDCTPLA := ROUND(NVL(CR_REC.SEDCT
322 ,0)
323 ,2);
324 P_ADLCTPLA := ROUND(NVL(CR_REC.ADLCT
325 ,0)
326 ,2);
327 END LOOP;
328 FOR DR_REC IN (SELECT
329 NVL(SUM(DR_BASIC_ED)
330 ,0) CENCU,
331 NVL(SUM(DR_OTHER_ED)
332 ,0) SEDCU,
333 NVL(SUM(DR_ADDITIONAL_ED)
334 ,0) ADLCU
335 FROM
336 JAI_CMN_RG_PLA_CMP_DTLS
337 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
338 AND LOCATION_ID = P_LOCATION_ID
339 AND TRUNC(TRANSACTION_DATE) BETWEEN NVL(TRUNC(P_TRN_FROM_DATE)
340 ,TRUNC(TRANSACTION_DATE))
341 AND NVL(TRUNC(P_TRN_TO_DATE)
342 ,TRUNC(TRANSACTION_DATE))) LOOP
343 P_CENCUPLA := ROUND(NVL(DR_REC.CENCU
344 ,0)
345 ,2);
346 P_SEDCUPLA := ROUND(NVL(DR_REC.SEDCU
347 ,0)
348 ,2);
349 P_ADLCUPLA := ROUND(NVL(DR_REC.ADLCU
350 ,0)
351 ,2);
352 END LOOP;
353 P_CENCBPLA := NVL(P_CENOBPLA
354 ,0) + NVL(P_CENCTPLA
355 ,0) - NVL(P_CENCUPLA
356 ,0);
357 P_SEDCBPLA := NVL(P_SEDOBPLA
358 ,0) + NVL(P_SEDCTPLA
359 ,0) - NVL(P_SEDCUPLA
360 ,0);
361 P_ADLCBPLA := NVL(P_ADLOBPLA
362 ,0) + NVL(P_ADLCTPLA
363 ,0) - NVL(P_ADLCUPLA
364 ,0);
365 RETURN (TRUE);
366 END BEFOREREPORT;
367
368 FUNCTION AFTERREPORT RETURN BOOLEAN IS
369 BEGIN
370 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
371 RETURN (TRUE);
372 END AFTERREPORT;
373
374 END JA_JAINEXCLIABILITY_XMLP_PKG;
375