1 PACKAGE BODY jai_om_wsh_processing_pkg AS
2 /* $Header: jai_om_wsh_prc.plb 120.2.12010000.2 2008/08/11 10:12:21 lgopalsa ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_om_wsh_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 13-Jun-2005 File Version: 116.3
15 Ramananda for bug#4428980. Removal of SQL LITERALs is done
16 --------------------------------------------------------------------------------------*/
17
18 FUNCTION excise_balance_check
19 (
20 p_pref_rg23a NUMBER ,
21 p_pref_rg23c NUMBER ,
22 p_pref_pla in NUMBER ,
23 p_ssi_unit_flag VARCHAR2 ,
24 p_tot_excise_amt NUMBER ,
25 p_rg23a_balance NUMBER ,
26 p_rg23c_balance NUMBER ,
27 p_pla_balance NUMBER ,
28 p_basic_pla_balance NUMBER ,
29 p_additional_pla_balance NUMBER ,
30 p_other_pla_balance NUMBER ,
31 p_basic_excise_duty_amount NUMBER ,
32 p_add_excise_duty_amount NUMBER ,
33 p_oth_excise_duty_amount NUMBER ,
34 p_export_oriented_unit VARCHAR2 ,
35 p_register_code VARCHAR2 ,
36 p_delivery_id NUMBER ,
37 p_organization_id NUMBER ,
38 p_location_id NUMBER ,
39 p_cess_amount NUMBER ,
40 p_sh_cess_amount NUMBER , /* added by ssawant for bug 5989740 */
41 p_process_flag OUT NOCOPY VARCHAR2 ,
42 p_process_msg OUT NOCOPY VARCHAR2
43 )
44 RETURN VARCHAR2
45 IS
46
47 --Variable Declaration starts here................
48 v_pref_rg23a JAI_CMN_INVENTORY_ORGS.pref_rg23a%type;
49 v_pref_rg23c JAI_CMN_INVENTORY_ORGS.pref_rg23c%type;
50 v_pref_pla JAI_CMN_INVENTORY_ORGS.pref_pla%type;
51 v_ssi_unit_flag JAI_CMN_INVENTORY_ORGS.ssi_unit_flag%type;
52 v_reg_type VARCHAR2(10);
53 v_tot_excise_amt NUMBER;
54 v_rg23a_balance NUMBER;
55 v_rg23c_balance NUMBER;
56 v_pla_balance NUMBER;
57 v_output NUMBER;
58 v_basic_pla_balance NUMBER;
59 v_additional_pla_balance number;
60 v_other_pla_balance number;
61 v_basic_excise_duty_amount number;
62 v_add_excise_duty_amount number;
63 v_oth_excise_duty_amount number;
64 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.export_oriented_unit%type;
65 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%type;
66 v_debug_flag varchar2(1); --'N'; --Ramananda for File.Sql.35
67 v_utl_location VARCHAR2(512); --For Log file.
68 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
69 v_trip_id wsh_delivery_trips_v.trip_id%type;
70 lv_process_flag VARCHAR2(2);
71 lv_process_message VARCHAR2(1996);
72 lv_register_type VARCHAR2(5);
73 lv_rg23a_cess_avlbl VARCHAR2(10);
74 lv_rg23c_cess_avlbl VARCHAR2(10);
75 lv_pla_cess_avlbl VARCHAR2(10);
76 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_wsh_prc_pkg.excise_balance_check';
77 lv_rg23a_sh_cess_avlbl VARCHAR2(10); /* added by ssawant for bug 5989740 */
78 lv_rg23c_sh_cess_avlbl VARCHAR2(10); /* added by ssawant for bug 5989740 */
79 lv_pla_sh_cess_avlbl VARCHAR2(10); /* added by ssawant for bug 5989740 */
80
81
82 --Variable Declaration Ends here......................
83
84 BEGIN
85
86 /*------------------------------------------------------------------------------------------
87 FILENAME: excise_balance_check_F.sql
88 CHANGE HISTORY:
89
90 1. 2002/07/03 Nagaraj.s - For Enh#2415656.
91 Function created for checking the register preferences in case of an Non-
92 Export Oriented Unit and in case of an Export Oriented Unit, the component
93 balances are checked and if balances does not exist, the function will raise an
94 application error and if balances do exist, then the function will return the register
95 type. This Function is called from ja_in_wsh_dlry_dtls_au_trg.sql and jai_om_wsh_pkg.process_delivery.sql.
96 This Function is a prerequisite patch with the above mentioned trigger and procedure.
97 Also Alter table scripts with this patch should be available before sending this patch.
98 Otherwise the patch would certainly fail.
99
100 2. 2005/02/11 ssumaith - bug# 4171272 - File version 115.1
101
102 Shipment needs to be stopped if education cess is not available. This has been
103 coded in this function. Five new parameters have been added to the function , hence it introduces
104 dependency.
105
106 The basic business logic validation is that both cess and excise should be available as
107 part of the same register type and the precedence setup at the organization additional information
108 needs to be considered for picking up the correct register order.
109
110 These functions returns the register only if excise balance and cess balance is enough to
111 cover the current transaction.
112 Signature of the function has been changed because we needed to pass the additional
113 parameters fo comparision.
114
115 Dependency Due to this bug:
116 Please include all objects of the patch 4171272 along with this object whenever changed,
117 because of change in object signature.
118
119
120 3. 13-April-2007 ssawant for bug 5989740 ,File version 120.2
121 Forward porting Budget07-08 changes of handling secondary and
122 Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
123
124 Future Dependencies For the release Of this Object:-
125 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
126 A datamodel change )
127
128 ----------------------------------------------------------------------------------------------------------------------------------------------------
129 Current Version Current Bug Dependent Files Version Author Date Remarks
130 Of File On Bug/Patchset Dependent On
131 excise_balance_check_f.sql
132 ----------------------------------------------------------------------------------------------------------------------------------------------------
133
134
135 --------------------------------------------------------------------------------------------*/
136 v_pref_rg23a := p_pref_rg23a;
137 v_pref_rg23c := p_pref_rg23c;
138 v_pref_pla := p_pref_pla;
139 v_ssi_unit_flag := p_ssi_unit_flag;
140 v_tot_excise_amt := p_tot_excise_amt;
141 v_rg23a_balance := p_rg23a_balance;
142 v_rg23c_balance := p_rg23c_balance;
143 v_pla_balance := p_pla_balance;
144 v_basic_pla_balance := p_basic_pla_balance;
145 v_additional_pla_balance := p_additional_pla_balance;
146 v_other_pla_balance := p_other_pla_balance;
147 v_basic_excise_duty_amount := p_basic_excise_duty_amount;
148 v_add_excise_duty_amount := p_add_excise_duty_amount;
149 v_oth_excise_duty_amount := p_oth_excise_duty_amount;
150 v_export_oriented_unit := p_export_oriented_unit;
151 v_register_code := p_register_code;
152
153 v_debug_flag := jai_constants.no; --Ramananda for File.Sql.35
154
155 If v_debug_flag = 'Y' THEN
156 --For Fetching UTIL File.......
157 BEGIN
158 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
159 Value,SUBSTR (value,1,INSTR(value,',') -1))
160 INTO v_utl_location
161 FROM v$parameter
162 WHERE name = 'utl_file_dir';
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 v_debug_flag := 'N';
167 END;
168 END IF;
169
170 IF v_debug_flag = 'Y' THEN
171 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'excise_balance_check_f.log','A');
172 UTL_FILE.PUT_LINE(v_myfilehandle,'************************Start************************************');
173 UTL_FILE.PUT_LINE(v_myfilehandle,'The Time Stamp this Entry is Created is ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
174 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pref_rg23a is ' || v_pref_rg23a);
175 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pref_rg23c is ' || v_pref_rg23c);
176 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pref_pla is ' || v_pref_pla);
177 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_rg23a_balance is ' ||v_rg23a_balance);
178 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_rg23c_balance is ' ||v_rg23c_balance);
179 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pla_balance is ' ||v_pla_balance);
180 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_ssi_unit_flag is ' ||v_ssi_unit_flag);
181 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_tot_excise_amt is ' ||v_tot_excise_amt);
182 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_basic_pla_balance is ' ||v_basic_pla_balance);
183 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_additional_pla_balance is ' ||v_additional_pla_balance);
184 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_other_pla_balance is ' ||v_other_pla_balance);
185 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_basic_excise_duty_amount is ' ||v_basic_excise_duty_amount);
186 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_add_excise_duty_amount is ' ||v_add_excise_duty_amount);
187 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_oth_excise_duty_amount is ' ||v_oth_excise_duty_amount);
188 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_export_oriented_unit is ' || v_export_oriented_unit);
189 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_register_code is ' || v_register_code);
190 END IF;
191
192 fnd_file.put_line(FND_FILE.LOG,'************************Start************************************');
193 fnd_file.put_line(FND_FILE.LOG,'The Time Stamp this Entry is Created is ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
194 fnd_file.put_line(FND_FILE.LOG,'The Value of v_pref_rg23a is ' || v_pref_rg23a);
195 fnd_file.put_line(FND_FILE.LOG,'The Value of v_pref_rg23c is ' || v_pref_rg23c);
196 fnd_file.put_line(FND_FILE.LOG,'The Value of v_pref_pla is ' || v_pref_pla);
197 fnd_file.put_line(FND_FILE.LOG,'The Value of v_rg23a_balance is ' ||v_rg23a_balance);
198 fnd_file.put_line(FND_FILE.LOG,'The Value of v_rg23c_balance is ' ||v_rg23c_balance);
199 fnd_file.put_line(FND_FILE.LOG,'The Value of v_pla_balance is ' ||v_pla_balance);
200 fnd_file.put_line(FND_FILE.LOG,'The Value of v_ssi_unit_flag is ' ||v_ssi_unit_flag);
201 fnd_file.put_line(FND_FILE.LOG,'The Value of v_tot_excise_amt is ' ||v_tot_excise_amt);
202 fnd_file.put_line(FND_FILE.LOG,'The Value of v_basic_pla_balance is ' ||v_basic_pla_balance);
203 fnd_file.put_line(FND_FILE.LOG,'The Value of v_additional_pla_balance is ' ||v_additional_pla_balance);
204 fnd_file.put_line(FND_FILE.LOG,'The Value of v_other_pla_balance is ' ||v_other_pla_balance);
205 fnd_file.put_line(FND_FILE.LOG,'The Value of v_basic_excise_duty_amount is ' ||v_basic_excise_duty_amount);
206 fnd_file.put_line(FND_FILE.LOG,'The Value of v_add_excise_duty_amount is ' ||v_add_excise_duty_amount);
207 fnd_file.put_line(FND_FILE.LOG,'The Value of v_oth_excise_duty_amount is ' ||v_oth_excise_duty_amount);
208 fnd_file.put_line(FND_FILE.LOG,'The Value of v_export_oriented_unit is ' || v_export_oriented_unit);
209 fnd_file.put_line(FND_FILE.LOG,'The Value of v_register_code is ' || v_register_code);
210 fnd_file.put_line(FND_FILE.LOG,'The Value of delivery id '|| p_delivery_id);
211 fnd_file.put_line(FND_FILE.LOG,'The Value of organization id '|| p_organization_id);
212 fnd_file.put_line(FND_FILE.LOG,'The Value of location id '|| p_location_id);
213 fnd_file.put_line(FND_FILE.LOG,'The Value of cess amount'|| p_cess_amount);
214 fnd_file.put_line(FND_FILE.LOG,'The Value of sh cess amount'|| p_sh_cess_amount);
215 -----------------------------------------------------------------------------------------------------------------
216 BEGIN
217
218 fnd_file.put_line(FND_FILE.LOG, ' Call RG others check balances');
219
220 lv_register_type := 'RG23A';
221 jai_cmn_rg_others_pkg.check_balances(
222 p_organization_id => p_organization_id ,
223 p_location_id => p_location_id ,
224 p_register_type => lv_register_type ,
225 p_trx_amount => p_cess_amount ,
226 p_process_flag => lv_process_flag ,
227 p_process_message => lv_process_message
228 );
229
230 if lv_process_flag <> jai_constants.successful then
231 lv_rg23a_cess_avlbl := 'FALSE';
232 fnd_file.put_line(FND_FILE.LOG, ' Problem in cess balance');
233 else
234 lv_rg23a_cess_avlbl := 'TRUE';
235 end if;
236
237 fnd_file.put_line(FND_FILE.LOG, ' call RG others sh check balances');
238 /* added by ssawant for bug 5989740 */
239 jai_cmn_rg_others_pkg .check_sh_balances(
240 p_organization_id => p_organization_id ,
241 p_location_id => p_location_id ,
242 p_register_type => lv_register_type ,
243 p_trx_amount => p_sh_cess_amount ,
244 p_process_flag => lv_process_flag ,
245 p_process_message => lv_process_message
246 );
247
248 if lv_process_flag <> jai_constants.successful then
249 lv_rg23a_sh_cess_avlbl := 'FALSE';
250 fnd_file.put_line(FND_FILE.LOG, ' Problem in SH cess balance');
251 else
252 lv_rg23a_sh_cess_avlbl := 'TRUE';
253 end if;
254
255 fnd_file.put_line(FND_FILE.LOG, ' call rg23 c check balances');
256
257 lv_register_type := 'RG23C';
258 jai_cmn_rg_others_pkg.check_balances(
259 p_organization_id => p_organization_id ,
260 p_location_id => p_location_id ,
261 p_register_type => lv_register_type ,
262 p_trx_amount => p_cess_amount ,
263 p_process_flag => lv_process_flag ,
264 p_process_message => lv_process_message
265 );
266
267 if lv_process_flag <> jai_constants.successful then
268 lv_rg23c_cess_avlbl := 'FALSE';
269 fnd_file.put_line(FND_FILE.LOG, ' Problem in cess balance');
270 else
271 lv_rg23c_cess_avlbl := 'TRUE';
272 end if;
273 fnd_file.put_line(FND_FILE.LOG, ' call rg 23 c sh check balances');
274
275 /* added by ssawant for bug 5989740 */
276 lv_register_type := 'RG23C';
277 jai_cmn_rg_others_pkg .check_sh_balances(
278 p_organization_id => p_organization_id ,
279 p_location_id => p_location_id ,
280 p_register_type => lv_register_type ,
281 p_trx_amount => p_sh_cess_amount ,
282 p_process_flag => lv_process_flag ,
283 p_process_message => lv_process_message
284 );
285
286 if lv_process_flag <> jai_constants.successful then
287 lv_rg23c_sh_cess_avlbl := 'FALSE';
288 else
289 lv_rg23c_sh_cess_avlbl := 'TRUE';
290 end if;
291
292 fnd_file.put_line(FND_FILE.LOG, ' call pla check balances');
293
294 lv_register_type := 'PLA';
295 jai_cmn_rg_others_pkg.check_balances(
296 p_organization_id => p_organization_id ,
297 p_location_id => p_location_id ,
298 p_register_type => lv_register_type ,
299 p_trx_amount => p_cess_amount ,
300 p_process_flag => lv_process_flag ,
301 p_process_message => lv_process_message
302 );
303
304 if lv_process_flag <> jai_constants.successful then
305 lv_pla_cess_avlbl := 'FALSE';
306 else
307 lv_pla_cess_avlbl := 'TRUE';
308 end if;
309 fnd_file.put_line(FND_FILE.LOG, ' call pla sh check balances');
310
311 /* added by ssawant for bug 5989740 */
312 jai_cmn_rg_others_pkg .check_sh_balances(
313 p_organization_id => p_organization_id ,
314 p_location_id => p_location_id ,
315 p_register_type => lv_register_type ,
316 p_trx_amount => p_sh_cess_amount ,
317 p_process_flag => lv_process_flag ,
318 p_process_message => lv_process_message
319 );
320
321 if lv_process_flag <> jai_constants.successful then
322 lv_pla_sh_cess_avlbl := 'FALSE';
323 fnd_file.put_line(FND_FILE.LOG, ' Problem in SH cess balance');
324 else
325 lv_pla_sh_cess_avlbl := 'TRUE';
326 end if;
327
328
329 --Balance Validations if Eou is No.....
330 IF v_export_oriented_unit = 'N' Then
331 IF v_pref_rg23a = 1 THEN -------------------------------------------------------7
332 IF v_rg23a_balance >= NVL(v_tot_excise_amt,0) and lv_rg23a_cess_avlbl = 'TRUE' and lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ---------------------------8/* added by ssawant for bug 5989740 */
333 v_reg_type := 'RG23A';
334 ELSIF v_pref_rg23c = 2 THEN
335 IF v_rg23c_balance >= NVL(v_tot_excise_amt,0) and lv_rg23c_cess_avlbl = 'TRUE' and lv_rg23c_sh_cess_avlbl = 'TRUE' THEN ------------------9/* added by ssawant for bug 5989740 */
336 v_reg_type := 'RG23C';
337 ELSIF v_pref_pla =3 THEN
338 IF v_pla_balance >= NVL(v_tot_excise_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN --------------10/* added by ssawant for bug 5989740 */
339 v_reg_type := 'PLA';
340 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
341 v_reg_type := 'PLA';
342 ELSE
343 v_reg_type := 'ERROR';
344 END IF;--------------------------------------------------------10
345 ELSE
346 v_reg_type := 'ERROR';
347 END IF;---------------------------------------------------------------9
348 ELSIF v_pref_pla = 2 THEN
349 IF v_pla_balance >= NVL(v_tot_excise_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN -------------------11/* added by ssawant for bug 5989740 */
350 v_reg_type := 'PLA';
351 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
352 v_reg_type := 'PLA';
353 ELSIF v_pref_rg23c = 3 THEN
354 IF v_rg23c_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN ----------12/* added by ssawant for bug 5989740 */
355 v_reg_type := 'RG23C';
356 ELSE
357 v_reg_type := 'ERROR';
358 END IF;--------------------------------------------------------12
359 ELSE
360 v_reg_type := 'ERROR';
361 END IF;------------------------------------------------------------ 11
362 ELSE
363 v_reg_type :='ERROR';
364 END IF;------------------------------------------------------------------8
365 -------------------------------------------------------------------------------------------------------------------
366 ELSIF v_pref_rg23c = 1 THEN -------------------------------------------------------7
367 IF v_rg23c_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN ---------------------------8/* added by ssawant for bug 5989740 */
368 v_reg_type := 'RG23C';
369 ELSIF v_pref_rg23a = 2 THEN
370 IF v_rg23a_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ------------------9/* added by ssawant for bug 5989740 */
371 v_reg_type := 'RG23A';
372 ELSIF v_pref_pla =3 THEN
373 IF v_pla_balance >= NVL(v_tot_excise_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN --------------10/* added by ssawant for bug 5989740 */
374 v_reg_type := 'PLA';
375 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
376 v_reg_type := 'PLA';
377 ELSE
378 v_reg_type := 'ERROR';
379 END IF;--------------------------------------------------------10
380 ELSE
381 v_reg_type := 'ERROR';
382 END IF;---------------------------------------------------------------9
383 ELSIF v_pref_pla = 2 THEN
384 IF v_pla_balance >= NVL(v_tot_excise_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN -------------------11/* added by ssawant for bug 5989740 */
385 v_reg_type := 'PLA';
386 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
387 v_reg_type := 'PLA';
388 ELSIF v_pref_rg23a = 3 THEN
389 IF v_rg23a_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ----------12/* added by ssawant for bug 5989740 */
390 v_reg_type := 'RG23A';
391 ELSE
392 v_reg_type := 'ERROR';
393 END IF;--------------------------------------------------------12
394 ELSE
395 v_reg_type := 'ERROR';
396 END IF;------------------------------------------------------------ 11
397 ELSE
398 v_reg_type :='ERROR';
399 END IF;------------------------------------------------------------------8
400 -------------------------------------------------------------------------------------------------------------------
401 ELSIF v_pref_pla = 1 THEN
402 IF v_pla_balance >= NVL(v_tot_excise_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN ---------------------------13/* added by ssawant for bug 5989740 */
403 v_reg_type := 'PLA';
404 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
405 v_reg_type := 'PLA';
406 ELSIF v_pref_rg23a = 2 THEN
407 IF v_rg23a_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ------------------14/* added by ssawant for bug 5989740 */
408 v_reg_type := 'RG23A';
409 ELSIF v_pref_rg23c =3 THEN
410 IF v_rg23c_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN --------------15/* added by ssawant for bug 5989740 */
411 v_reg_type := 'RG23C';
412 ELSE
413 v_reg_type := 'ERROR';
414 END IF;--------------------------------------------------------15
415 ELSE
416 v_reg_type := 'ERROR';
417 END IF;---------------------------------------------------------------14
418 ELSIF v_pref_rg23c = 2 THEN
419 IF v_rg23c_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN -------------------16/* added by ssawant for bug 5989740 */
420 v_reg_type := 'RG23C';
421 ELSIF v_pref_rg23a = 3 THEN
422 IF v_rg23a_balance >= NVL(v_tot_excise_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ----------17/* added by ssawant for bug 5989740 */
423 v_reg_type := 'RG23A';
424 ELSE
425 v_reg_type := 'ERROR';
426 END IF;--------------------------------------------------------17
427 ELSE
428 v_reg_type := 'ERROR';
429 END IF;------------------------------------------------------------ 16
430 ELSE
431 v_reg_type :='ERROR';
432 END IF;------------------------------------------------------------------13
433 ELSE
434 v_reg_type :='ERROR';
435 END IF;---------------------------------------------------------------------------7
436
437 --Balance Validations if EOU is Yes.....
438 ELSIF v_export_oriented_unit ='Y' and v_register_code='EXPORT_EXCISE' then
439
440 fnd_file.put_line(FND_FILE.LOG, ' validation for export excise ');
441
442 --Validation for Basic Excise Duty Amount.
443 IF nvl(v_basic_excise_duty_amount,0) >0 THEN
444 IF v_basic_pla_balance >= v_basic_excise_duty_amount AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' then /* added by ssawant for bug 5989740 */
445 v_reg_type := 'PLA';
446 ELSE
447 v_reg_type := 'ERROR';
448 END IF;
449 END IF;
450
451 fnd_file.put_line(FND_FILE.LOG, ' Basic - reg type '|| v_reg_type);
452
453 --Validation for Additional Excise Duty Amount.
454 IF v_reg_type<>'ERROR' THEN
455 IF nvl(v_add_excise_duty_amount,0) >0 THEN
456
457 IF v_additional_pla_balance >= v_add_excise_duty_amount AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN /* added by ssawant for bug 5989740 */
458 v_reg_type := 'PLA';
459 ELSE
460 v_reg_type := 'ERROR';
461 END IF;
462 END IF;
463 END IF;
464
465 fnd_file.put_line(FND_FILE.LOG, ' Additional - reg type '|| v_reg_type);
466
467 --Validation for Other Excise Duty Amount.
468 IF v_reg_type<>'ERROR' THEN
469
470 IF nvl(v_oth_excise_duty_amount,0) >0 THEN
471 IF v_other_pla_balance >= v_oth_excise_duty_amount AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' then /* added by ssawant for bug 5989740 */
472 v_reg_type := 'PLA';
473 ELSE
474 v_reg_type := 'ERROR';
475 END IF;
476 END IF;
477 END IF;
478
479 END IF; --End of Export Oriented Check......
480 fnd_file.put_line(FND_FILE.LOG, ' Other reg type '|| v_reg_type);
481 -----------------------------------------------------------------------------------------------------------------
482 EXCEPTION
483 when others then
484 fnd_file.put_line(FND_FILE.LOG, ' Error raised ni excise_balance_check function');
485 raise_application_error(-20001,'Error Raised in excise_balance_check function');
486 END;
487
488 --To Raise an Application Error in the Function only rather than in the Trigger or Procedure........
489 IF v_reg_type='ERROR' THEN
490 BEGIN
491 SELECT TRIP_ID
492 into v_trip_id
493 FROM WSH_DELIVERY_TRIPS_V
494 WHERE DELIVERY_ID=p_delivery_id;
495 EXCEPTION
496 WHEN OTHERS THEN
497 NULL;
498 END;
499
500 IF v_debug_flag = 'Y' THEN
501 UTL_FILE.PUT_LINE(v_myfilehandle,'Transaction failed as balances are not sufficient');
502 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_trip_id for which transaction failed is ' || v_trip_id);
503 UTL_FILE.PUT_LINE(v_myfilehandle,'************************END************************************');
504 UTL_FILE.FCLOSE(v_myfileHandle);
505 END IF;
506 IF v_export_oriented_unit ='N' THEN
507 fnd_file.put_line(FND_FILE.LOG, 'None of the Register Have Balances Greater OR Equal TO the Excisable Amount ->'
508 || TO_CHAR(v_tot_excise_amt )||' OR Education Cess Amount => ' || to_char(p_cess_amount) );
509 RAISE_APPLICATION_ERROR(-20120, 'None of the Register Have Balances Greater OR Equal TO the Excisable Amount ->'
510 || TO_CHAR(v_tot_excise_amt )||' OR Education Cess Amount => ' || to_char(p_cess_amount) );
511 ELSIF v_export_oriented_unit ='Y' and v_register_code='EXPORT_EXCISE' THEN
512 fnd_file.put_line(FND_FILE.LOG, 'The Excise Component Balances are not sufficient' );
513 RAISE_APPLICATION_ERROR(-20120, 'The Excise Component Balances are not sufficient');
514 fnd_file.put_line(FND_FILE.LOG, ' The Excise Component Balances are not sufficient ');
515 END IF;
516 END IF;
517
518 RETURN(v_reg_type);
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
523 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
524 app_exception.raise_exception;
525 END excise_balance_check;
526
527 END jai_om_wsh_processing_pkg;
[Home] [Help]