1 PACKAGE BODY jai_cmn_setup_pkg AS
2 /* $Header: jai_cmn_setup.plb 120.5.12010000.4 2009/01/28 11:03:53 jmeena ship $ */
3
4 PROCEDURE generate_excise_invoice_no
5 (
6 P_ORGANIZATION_ID Number,
7 P_LOCATION_ID Number,
8 P_CALLED_FROM VARCHAR2,
9 P_ORDER_INVOICE_TYPE_ID NUMBER,
10 P_FIN_YEAR Number,
11 P_EXCISE_INV_NO OUT NOCOPY Varchar2,
12 P_Errbuf OUT NOCOPY Varchar2
13 )
14 As
15
16 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%type;
17 v_register_meaning ja_lookups.meaning%type;
18 v_order_type ra_batch_sources_all.name%type; --Ramananda bug#4171671
19 v_invoice_type ra_batch_sources_all.name%type;
20 v_start_number Number;
21 v_prefix JAI_CMN_RG_EXC_INV_NOS.prefix%type;
22 v_jump_by Number;
23 v_end_number Number;
24 v_ec_code Varchar2(50);
25 v_master_org_flag char(1);
26 v_master_organization_id Number;
27 v_ec_code_gen Char(1);
28 v_location_id Number;
29 v_excise_inv_no Varchar2(100);
30 v_gp1 Number;
31 v_gp2 Number;
32 v_trans_type_up Varchar2(20);
33
34 v_act_organization_id Number; -- these variables hold the value of organization id
35 v_act_location_id Number; -- location id which will be used for excise invoice generation
36
37 /*added by vkaranam for bug #6030615*/
38 --start
39 cursor c_org_type (cp_organization_id in number , cp_location_id in number ) IS
40 select manufacturing , trading
41 from jai_cmn_inventory_orgs
42 where organization_id = cp_organization_id
43 and location_id = cp_location_id;
44
45 r_org_type c_org_type%rowtype;
46 --end
47
48
49 Cursor c_Get_order_type is
50 select name
51 from oe_transaction_types_tl
52 where transaction_type_id = p_order_invoice_type_id;
53
54 Cursor c_get_invoice_type is
55 Select name
56 from ra_batch_sources_all
57 where batch_source_id = p_order_invoice_type_id;
58
59 Cursor c_get_register(p_orgn_id number,p_locn_id number,p_order_flag varchar2) is
60 select a.register_code
61 from JAI_OM_OE_BOND_REG_HDRS a , JAI_OM_OE_BOND_REG_DTLS b
62 where a.organization_id = p_orgn_id
63 and a.location_id = p_locn_id
64 and a.register_id = b.register_id
65 and b.order_flag = p_order_flag
66 and b.order_type_id = p_order_invoice_type_id;
67
68
69 Cursor c_register_meaning_cur(v_lookup_code JAI_OM_OE_BOND_REG_HDRS.register_code%type, cp_lookup_type ja_lookups.lookup_type%type) is
70 SELECT meaning
71 FROM ja_lookups
72 WHERE lookup_type = cp_lookup_type --'JAI_REGISTER_TYPE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
73 AND lookup_code = v_lookup_code;
74
75 Cursor c_def_excise_cur(p_orgn_id Number , p_loc_id number) is
76 Select start_number , prefix , jump_by , end_number
77 From JAI_CMN_RG_EXC_INV_NOS
78 where organization_id = p_orgn_id
79 and location_id = p_loc_id
80 and fin_year = p_fin_year
81 and order_invoice_type = v_order_type
82 and register_code = v_register_meaning
83 for update;
84
85 CURSOR c_Tr_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
86 SELECT A.Organization_Id, A.Location_Id
87 FROM JAI_CMN_INVENTORY_ORGS A
88 WHERE A.Tr_Ec_Code IN
89 (
90 SELECT B.Tr_Ec_Code
91 FROM JAI_CMN_INVENTORY_ORGS B
92 WHERE B.Organization_Id = p_organization_id
93 AND B.Location_Id = p_location_id
94 );
95
96 /* Removed below condition as EXCISE_INVNO_AT_EC_CODE flag is not for trading organization, JMEENA for bug#7719911
97 --AND nvl(EXCISE_INVNO_AT_EC_CODE, 'N') = 'Y' --Added by nprashar for bug # 7319628;
98 */
99 CURSOR c_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
100 SELECT A.Organization_Id, A.Location_Id
101 FROM JAI_CMN_INVENTORY_ORGS A
102 WHERE A.Ec_Code IN
103 (
104 SELECT B.Ec_Code
105 FROM JAI_CMN_INVENTORY_ORGS B
106 WHERE B.Organization_Id = p_organization_id
107 AND B.Location_Id = p_location_id
108 )
109 AND nvl(EXCISE_INVNO_AT_EC_CODE, 'N') = 'Y' /*Added by nprashar for bug # 7319628*/;
110
111
112 Cursor c_excise_cur(p_orgn_id Number , p_loc_id number) is
113 Select nvl(gp1,0) , nvl(gp2,0)
114 From JAI_CMN_RG_EXC_INV_NOS
115 where organization_id = p_orgn_id
116 and location_id = p_loc_id
117 and fin_year = p_fin_year
118 AND order_invoice_type IS NULL
119 AND register_code IS NULL;
120
121
122 Cursor c_master_org is
123 Select ec_code , master_org_flag , master_organization_id, EXCISE_INVNO_AT_EC_CODE
124 from JAI_CMN_INVENTORY_ORGS
125 where organization_id = p_organization_id
126 and location_id = p_location_id;
127
128
129 Cursor c_mstr_org(p_orgn_id Number, p_ec_code Varchar2) is
130 select organization_id , EXCISE_INVNO_AT_EC_CODE , location_id
131 from JAI_CMN_INVENTORY_ORGS
132 where organization_id = p_orgn_id
133 and ec_code = p_ec_code
134 and master_org_flag = 'Y';
135
136 --JMEENA for bug#7719911 FP of bug#7505975
137 Cursor c_master_trade_org is
138 Select tr_ec_code , master_org_flag , master_organization_id
139 from JAI_CMN_INVENTORY_ORGS
140 where organization_id = p_organization_id
141 and location_id = p_location_id;
142
143
144 Cursor c_trade_org(p_orgn_id Number, p_ec_code Varchar2) is
145 select organization_id , location_id
146 from JAI_CMN_INVENTORY_ORGS
147 where organization_id = p_orgn_id
148 and tr_ec_code = p_ec_code
149 and master_org_flag = 'Y';
150
151 r_master_trade_org c_master_trade_org%rowtype;
152
153 --end for bug#7719911 FP of bug#7505975
154
155 BEGIN
156
157 --Change History :
158 /*************************************************************************************************************
159 File Name : ja_in_excise_invoice_no_gen_p.sql
160
161 Slno Date Description
162
163 1 26/075/2005 Ramananda for bug#4514367. File Version 120.2
164 RTV Invoice number should be generated based on additional info setup.
165 The following should be the RTV behaviour:
166
167 1. When Excise Invoice sequence setup for RTV is specifically done, then RTV
168 Excise Invoice sequence is picked up from this setup.
169 2. When no specific setup for RTV Excise Invoice sequence is made, Excise
170 Invoice should be generated from the Domestic series.
171 3.If this setup is also missing then it should pick from GP1 respectively.
172
173 Its observed that Invoice is picked based on additional info setup for RTV else from
174 GP1 of ja_in_excise_invoice_no.It was not considering the Domestic Series.
175
176 Fix:
177 ----
178 Code for considering Domestic series when RTV setup is present has been added.Actually
179 Manual RG23 uses domestic series. So the same code is used if RTV setup is absent and
180 Domestic is present.
181
182 2 24/04/2007 Vijay Shankar for Bug# 6012570 (5876390), Version:120.4 (115.10)
183 FP: Modified the code to generate excise invoice number for Projects Billing.
184
185 3. 28-Jan-2009 JMEENA for bug#7719911
186 1) Removed the condition of EXCISE_INVNO_AT_EC_CODE flag from cursor c_tr_ec_code_cur as this should not be checked for trading organizations.
187 2) Added if condition to update table JAI_CMN_RG_EXC_INV_NOS for the records where EXCISE_INVNO_AT_EC_CODE = 'N'
188 for the manufacturing organizations because such records will not be fetched by cursor c_ec_code_cur so invoice number
189 will not be updated to next number for that organization.
190 3) FP of bug#7505975
191 Issue: Excise Invoice Number for a Trading organization with Master-- Child setup
192 is not using its Master Organization Excise sequence defined.
193 Fix: Changes are done to ensure that Trading Org with master- child relation ship will use the
194 master org excise sequence.
195
196 Future Dependencies For the release Of this Object:-
197 (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/
198 A datamodel change )
199
200 --------------------------------------------------------------------------------------------------------------
201 Version Bug Dependencies
202 ---------------------------------------------------------------------------------------------------------------
203 616.1 3071342 IN60104D1
204 619.1 3439480 No dependencies introduced - IN60105D2
205
206 *************************************************************************************************************/
207 fnd_file.put_line(fnd_file.log,'Starting Excise Invoice Generation Prg');--bug#7719911
208 --start for bug#7719911 FP of 7505975, JMEENA
209 OPEN c_org_type (p_organization_id , p_location_id);
210 fetch c_org_type into r_org_type;
211 CLOSe c_org_type;
212 fnd_file.put_line(fnd_file.log,'Manufacturing Organization --> '||r_org_type.manufacturing
213 || ' Trading Organization -->'||r_org_type.trading);--7507579
214 if nvl(r_org_type.manufacturing,'N')='Y' --bug#7719911
215 then
216
217 -- code to get the organization id location id of the master org
218 Open c_master_org;
219 Fetch c_master_org into v_ec_code , v_master_org_flag,v_master_organization_id,v_ec_code_gen;
220 Close c_master_org;
221
222 if upper(v_master_org_flag) = 'Y' then
223
224 -- the transacting organization is the master org
225 v_act_organization_id := p_organization_id;
226 v_act_location_id := p_location_id;
227 fnd_File.PUT_LINE(Fnd_File.LOG,' Inside Master Org Flag = Y');
228 fnd_File.PUT_LINE(Fnd_File.LOG,' Organziation Id : '|| v_act_organization_id);
229 fnd_File.PUT_LINE(Fnd_File.LOG,' Location Id: '|| v_act_location_id);
230
231 else
232
233 -- this is not the master org. get the master org for this org
234
235 if v_master_organization_id is not null then
236 -- we have the master organization id for the transacting organization.
237 -- need to get the location id of the master org organization id , where the master org flag is 'Y'
238
239 Open c_mstr_org(v_master_organization_id,v_ec_code);
240 fetch c_mstr_org into v_master_organization_id,v_ec_code_gen,v_location_id;
241 close c_mstr_org;
242
243 if NVL(v_ec_code_gen,'N') = 'Y' then
244
245 -- use the master org setup to generate the excise invo no
246 -- which means use the v_master_organization_id v_location_id fields as retreived previously.
247 v_act_organization_id := v_master_organization_id;
248 v_act_location_id := v_location_id;
249
250 fnd_File.PUT_LINE(Fnd_File.LOG,' Inside ECCode gen = Y');
251 fnd_File.PUT_LINE(Fnd_File.LOG,' Organziation Id : '|| v_act_organization_id);
252 fnd_File.PUT_LINE(Fnd_File.LOG,' Location Id: '|| v_act_location_id);
253
254 else
255
256 -- need to use the transacting organizations organization id and location id
257 -- for generating excise invoice number.
258 v_act_organization_id := p_organization_id;
259 v_act_location_id := p_location_id;
260
261 fnd_File.PUT_LINE(Fnd_File.LOG,' Else of ECCode gen');
262 fnd_File.PUT_LINE(Fnd_File.LOG,' Organziation Id : '|| v_act_organization_id);
263 fnd_File.PUT_LINE(Fnd_File.LOG,' Location Id: '|| v_act_location_id);
264
265 end if;
266
267 -- now we have got the organization id,location id of the master org of this transacting org
268 -- and also whether excise invoice generation should be based at master org level .
269
270 ELSE
271 /* Bug 5365346. Added by Lakshmi Gopalsami
272 * Assigned the value of p_organization_id and p_location_id.
273 * This is required for generating Excise invoice number Else
274 * the value is coming as NULL.
275 */
276 v_act_organization_id := p_organization_id;
277 v_act_location_id := p_location_id;
278 fnd_File.PUT_LINE(Fnd_File.LOG,' Else of Master organization id is not null ');
279 fnd_File.PUT_LINE(Fnd_File.LOG,' Organziation Id : '|| v_act_organization_id);
280 fnd_File.PUT_LINE(Fnd_File.LOG,' Location Id: '|| v_act_location_id);
281
282 end if; -- v_master_organization_id is not null then
283
284
285 end if; -- upper(v_master_org_flag) = 'Y'
286
287 --start for bug#7719911 FP of 7505975
288 elsif nvl(r_org_type.trading,'N')='Y' then
289 Open c_master_trade_org;
290 Fetch c_master_trade_org into r_master_trade_org ;
291 Close c_master_trade_org;
292 fnd_file.put_line(fnd_file.log,'Master org flag --> '||r_master_trade_org.master_org_flag ||
293 ' Master Organization -->'||nvl(r_master_trade_org.master_organization_id,p_organization_id)||
294 ' Ec Code --> '||r_master_trade_org.tr_ec_code
295 );--7507579
296 if upper(r_master_trade_org.master_org_flag) = 'Y' then
297
298 -- the transacting organization is the master org
299 v_act_organization_id := p_organization_id;
300 v_act_location_id := p_location_id;
301
302 else
303
304 if r_master_trade_org.master_organization_id is not null then
305 -- we have the master organization id for the transacting organization.
306 -- need to get the location id of the master org organization id , where the master org flag is 'Y'
307
308 Open c_trade_org(r_master_trade_org.master_organization_id,r_master_trade_org.tr_ec_code);
309 fetch c_trade_org into v_master_organization_id,v_location_id;
310 close c_trade_org;
311
312
313 v_act_organization_id := v_master_organization_id;
314 v_act_location_id := v_location_id;
315 else
316 v_act_organization_id := p_organization_id;
317 v_act_location_id := p_location_id;
318
319 end if;
320 end if;
321
322 end if;
323 --end bug#7719911 FP of 7505975
324
325 fnd_file.put_line(fnd_file.log,'Organization used for Excise Invoice Sequence '|| v_act_organization_id);--7719911
326 -- when the control comes here , we should have the organization id , location id , fin year , register type
327 -- and order invoice type , so that excise invoice number logic can be simple
328
329 IF P_CALLED_FROM = 'O' then -- if it is called from OM
330
331 Open c_get_register(v_act_organization_id,v_act_location_id,'Y');
332 Fetch c_get_register into v_register_code;
333 close c_get_register;
334
335 Open c_Get_order_type;
336 Fetch c_Get_order_type into v_order_type;
337 Close c_Get_order_type;
338
339 ELSIF P_CALLED_FROM = 'I' then -- if it is called from AR
340
341 Open c_get_invoice_type;
342 Fetch c_get_invoice_type into v_order_type;
343 close c_get_invoice_type;
344
345 Open c_get_register(v_act_organization_id,v_act_location_id,'N');
346 Fetch c_get_register into v_register_code;
347 close c_get_register;
348
349 End if;
350
351 if v_register_code is not null then
352
353 Open c_register_meaning_cur(v_register_code, 'JAI_REGISTER_TYPE');
354 Fetch c_register_meaning_cur into v_register_meaning;
355 Close c_register_meaning_cur;
356
357 elsif P_CALLED_FROM = 'P' then
358 -- in the case of RTV transactions , the value 'RTV' is hard coded in the JAI_CMN_RG_EXC_INV_NOS
359 -- when preferences are setup for a RTV transaction.
360 v_register_code := 'RTV';
361 v_order_type := 'RTV';
362 v_register_meaning := 'RTV';
363
364
365 End if;
366
367 -- the following update was written to lock all the records that will
368 -- get updated after excise invoice generation has occured and this is used to prevent a deadlock.
369
370 Fnd_File.PUT_LINE(Fnd_File.LOG,'Before update time is ' || to_char(sysdate,'dd-mon-yyyy hh:mi:ss'));
371
375 AND order_invoice_type IS NULL
372 update JAI_CMN_RG_EXC_INV_NOS
373 set last_update_date = last_update_date
374 where fin_year = p_fin_year
376 AND register_code IS NULL
377 and (organization_id, location_id)
378 in
379 (SELECT A.Organization_Id, A.Location_Id
380 FROM JAI_CMN_INVENTORY_ORGS A
381 WHERE A.Ec_Code IN
382 (
383 SELECT B.Ec_Code
384 FROM JAI_CMN_INVENTORY_ORGS B
385 WHERE B.Organization_Id = v_act_organization_id
386 AND B.Location_Id = v_act_location_id
387 )
388 );
389
390 Fnd_File.PUT_LINE(Fnd_File.LOG,'After update time is ' || to_char(sysdate,'dd-mon-yyyy hh:mi:ss'));
391
392 -- using the following cursor we get gp1 and gp2 values which can be used
393 Open c_excise_cur(v_act_organization_id, v_act_location_id);
394 fetch c_excise_cur into v_gp1,v_gp2;
395 close c_excise_cur;
396
397
398 -- using the following cursor , we get start number and other values
399 -- to be used when register type and order type exist.
400 Open c_def_excise_cur(v_act_organization_id,v_act_location_id);
401 Fetch c_def_excise_cur into v_start_number , v_prefix , v_jump_by , v_end_number;
402 close c_def_excise_cur;
403
404
405 if v_start_number is not null then
406
407 if v_end_number is not null then
408
409 if ((v_start_number + nvl(v_jump_by,1)) > v_end_number ) then
410
411 RAISE_APPLICATION_ERROR(-20107,
412 'Excise Invoice Numbers have been exhausted ... Reset them and perform the transaction');
413 end if;
414
415 end if;
416
417 -- excise invoice number can be generated.
418
419 if v_prefix is not null then
420 v_excise_inv_no := v_prefix || '/' || nvl(v_start_number,0);
421 else
422 v_excise_inv_no := nvl(v_start_number,0);
423 end if;
424
425 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,1);
426
427 else
428 -- need to generate for Domestic or Excise based on the register type
429 /*
430 Changed by aiyer for the bug #3071342. As the excise invoice generation should not be done in case Domestic
431 Without Excise fpr trading and manufacturing organizations and hence removing the check of v_register_code i
432 'DOM_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE' from the if statement below
433 */
434 -- Start of bug 3071342
435
436 /* Start, cbabu for Project Billing. Bug# 6012570 (5876390) */
437 if p_called_from = jai_pa_billing_pkg.gv_source_projects then
438
439 v_excise_inv_no := NVL(v_gp1,0);
440 v_gp1 := NVL(v_gp1,0) + 1;
441 /* End, cbabu for Project Billing. Bug# 6012570 (5876390) */
442
443 elsif ( (v_register_code in ('DOMESTIC_EXCISE' , '23D_DOMESTIC_EXCISE') )
444 OR (P_CALLED_FROM = 'MANUAL_RG1:DOMESTIC')
445 OR (P_CALLED_FROM = 'MANUAL_PLA:DOMESTIC')
446 OR (P_CALLED_FROM = 'MANUAL_RG23:DOMESTIC')
447 OR (p_called_from = 'INTERORG_XFER') /*added by vkaranam for bug #6030615*/
448 ) then
449 -- End of bug 3071342
450 -- Condition of P_CALLED_FROM = 'MANUAL_RG1:DOMESTIC' added by bug#3290999
451 -- condition of (P_CALLED_FROM = 'MANUAL_PLA:DOMESTIC') added by sriram - bug# 3439480
452 -- (P_CALLED_FROM = 'MANUAL_RG23:DOMESTIC') added by sriram - bug# 3439480
453 v_order_type := 'DOMESTIC';
454 v_register_meaning := 'DOMESTIC';
455 v_trans_type_up := 'DOM';
456
457 Open c_def_excise_cur(v_act_organization_id,v_act_location_id);
458 Fetch c_def_excise_cur into v_start_number , v_prefix , v_jump_by , v_end_number;
459 close c_def_excise_cur;
460
461 if v_start_number is not null then
462
463 if v_end_number is not null then
464 if ((v_start_number + nvl(v_jump_by,1)) > v_end_number ) then
465 RAISE_APPLICATION_ERROR(-20107,
466 'Excise Invoice Numbers have been exhausted ... Reset them and perform the transaction');
467 end if;
468 end if;
469
470 if v_prefix is not null then
471 v_excise_inv_no := v_prefix || '/' || nvl(v_start_number,0);
472 else
473 v_excise_inv_no := nvl(v_start_number,0);
474 end if;
475
476 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,1);
477 else
478 -- need to use gp1 here
479 v_excise_inv_no := NVL(v_gp1 ,0);
480 v_gp1 := NVL(v_gp1,0) + 1;
481
482 end if;
483
484 -- Vijay Shankar for bug#3393133
485 -- elsif ( (v_register_code in ('EXPORT_EXCISE','23D_EXPORT_ EXCISE') ) OR (P_CALLED_FROM = 'MANUAL_RG1:EXPORT') ) then
486 elsif ( (v_register_code in ('EXPORT_EXCISE','23D_EXPORT_ EXCISE', 'BOND_REG') )
487 OR (P_CALLED_FROM = 'MANUAL_RG1:EXPORT')
488 OR (P_CALLED_FROM = 'MANUAL_PLA:EXPORT')
489 OR (P_CALLED_FROM = 'MANUAL_RG23:EXPORT')
490 OR (p_called_from = 'INTERORG_XFER') /*added by vkaranam for bug #6030615*/
491 ) then
492 -- condition of P_CALLED_FROM = 'MANUAL_RG1:EXPORT' added for bug#3290999
493 -- condition of (P_CALLED_FROM = 'MANUAL_PLA:EXPORT') added for bug# 3439480
494 -- condition of (P_CALLED_FROM = 'MANUAL_RG23:EXPORT') added for bug# 3439480
495 v_order_type := 'EXPORT';
496 v_register_meaning := 'EXPORT';
497 v_trans_type_up := 'EXP';
498
502
499 Open c_def_excise_cur(v_act_organization_id,v_act_location_id);
500 Fetch c_def_excise_cur into v_start_number , v_prefix , v_jump_by , v_end_number;
501 close c_def_excise_cur;
503 if v_start_number is not null then
504 if v_end_number is not null then
505 if ((v_start_number + nvl(v_jump_by,1)) > v_end_number ) then
506 RAISE_APPLICATION_ERROR(-20107,
507 'Excise Invoice Numbers have been exhausted ... Reset them and perform the transaction');
508 end if;
509 end if;
510
511 if v_prefix is not null then
512 v_excise_inv_no := v_prefix || '/' || nvl(v_start_number,0);
513 else
514 v_excise_inv_no := nvl(v_start_number,0);
515 end if;
516
517 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,1);
518
519 else
520 -- need to use gp2 here
521 v_excise_inv_no := NVL(v_gp2,0);
522 v_gp2 := NVL(v_gp2,0) + 1;
523
524 end if;
525
526 elsif v_register_code in ('RTV') or P_CALLED_FROM = 'MANUAL_RG23:RTV' then
527 -- condition of 'MANUAL_RG23:RTV' in if clause above added by sriram - bug#3439480
528 v_order_type := 'RTV';
529 v_register_meaning := 'RTV';
530 v_trans_type_up := 'RTV';
531
532 Open c_def_excise_cur(v_act_organization_id,v_act_location_id);
533 Fetch c_def_excise_cur into v_start_number , v_prefix , v_jump_by , v_end_number;
534 close c_def_excise_cur;
535
536
537 if v_start_number is not null then
538 if v_end_number is not null then
539 if ((v_start_number + nvl(v_jump_by,1)) > v_end_number ) then
540 RAISE_APPLICATION_ERROR(-20107,
541 'Excise Invoice Numbers have been exhausted ... Reset them and perform the transaction');
542 end if;
543 end if;
544
545 if v_prefix is not null then
546 v_excise_inv_no := v_prefix || '/' || nvl(v_start_number,0);
547 else
548 v_excise_inv_no := nvl(v_start_number,0);
549 end if;
550
551 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,1);
552 -- added by ssumaith - bug# 3522521
553 /* elsif P_CALLED_FROM = 'MANUAL_RG23:RTV' then */
554 elsif v_start_number is null then --Ramananda for bug#4514367
555
556 v_order_type := 'DOMESTIC';
557 v_register_meaning := 'DOMESTIC';
558 v_trans_type_up := 'DOM';
559
560 Open c_def_excise_cur(v_act_organization_id,v_act_location_id);
561 Fetch c_def_excise_cur into v_start_number , v_prefix , v_jump_by , v_end_number;
562 close c_def_excise_cur;
563
564 if v_start_number is not null then
565
566 if v_end_number is not null then
567
568 if ((v_start_number + nvl(v_jump_by,1)) > v_end_number ) then
569 RAISE_APPLICATION_ERROR(-20107,
570 'Excise Invoice Numbers have been exhausted ... Reset them and perform the transaction');
571 end if;
572 end if;
573 if v_prefix is not null then
574 v_excise_inv_no := v_prefix || '/' || nvl(v_start_number,0);
575 else
576 v_excise_inv_no := nvl(v_start_number,0);
577 end if;
578 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,1);
579
580 else
581 v_excise_inv_no := NVL(v_gp1,0);
582 v_gp1 := NVL(v_gp1,0) + 1;
583 end if;
584 else
585 v_excise_inv_no := NVL(v_gp1,0);
586 v_gp1 := NVL(v_gp1,0) + 1;
587 end if;
588 -- ends here additions by ssumaith - bug# 3522521
589 else
590 -- else of register code neither domestic nor excise
591 -- ideally flow of code should not occur here.
592 v_excise_inv_no := NVL(v_gp2,0);
593 v_gp2 := NVL(v_gp2,0) + 1;
594 end if; -- end if of v_register_code in ()
595
596 end if; -- end if of v_start_number is not null for order + register type combination.
597
598 /*added the following if condition by vkaranam for bug#6030615*/
599 IF v_excise_inv_no = '0' or v_excise_inv_no IS NULL THEN
600 raise_application_error(-20199,'Unable to generate Excise Invoice Number ! Please check the Setup');
601 ELSE
602 P_EXCISE_INV_NO := v_excise_inv_no;
603
604 END IF;
605
606
607 -- need to write updates here to update the excise invoice table so that the values of columns are incremented.
608
609 if v_excise_inv_no is not null then
610
611 if v_start_number is not null then
612
613 -- we have not used gp1 , gp2 hence should not update those columns.
614 -- instead should update the column start number instead.
615
616 IF v_trans_type_up IN ('DOM','EXP') THEN
617 UPDATE JAI_CMN_RG_EXC_INV_NOS
618 SET start_number = v_start_number,
619 last_update_date = sysdate
620 WHERE organization_id = v_act_organization_id
621 AND location_id = v_act_location_id
622 AND fin_year = p_fin_year
623 AND order_invoice_type = v_order_type
624 AND register_code =v_register_meaning
625 AND transaction_type = v_trans_type_up;
626
627 ELSIF NVL(v_ec_code_gen,'N') = 'N' Then /*Added by nprashar for bug 7344638*/
628 UPDATE JAI_CMN_RG_EXC_INV_NOS
629 SET start_number = v_start_number,
630 last_update_date = sysdate
634 AND order_invoice_type = v_order_type
631 WHERE organization_id = v_act_organization_id
632 AND location_id = v_act_location_id
633 AND fin_year = p_fin_year
635 AND register_code =v_register_meaning;
636 END IF; /*Ends here*/
637
638 /* Changed by aiyer for the bug #3071342
639 As the excise invoice generation should not be done in case Domestic Without Excise fpr trading and manufacturing organizations and hence removing the check of v_register_code in
640 'DOM_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE' from the if statement below */
641
642 -- Start of bug 3071342
643
644 IF v_register_code IN ('23D_DOMESTIC_EXCISE') THEN
645
646 -- End of bug 3071342
647 FOR master_org_rec IN c_tr_ec_code_cur(v_act_organization_id, v_act_location_id) LOOP
648
649 UPDATE JAI_CMN_RG_EXC_INV_NOS
650 SET start_number = v_start_number
651 WHERE organization_id = master_org_rec.organization_id
652 AND location_id = master_org_rec.location_id
653 AND fin_year = p_fin_year
654 AND order_invoice_type = v_order_type
655 AND register_code = v_register_meaning;
656
657 END LOOP;
658
659 ELSE
660 IF NVL(v_ec_code_gen,'N') = 'N' Then --Added by JMEENA for bug#7719911
661 UPDATE JAI_CMN_RG_EXC_INV_NOS
662 SET start_number = v_start_number
663 WHERE organization_id = v_act_organization_id
664 AND location_id = v_act_location_id
665 AND fin_year = p_fin_year
666 AND order_invoice_type = v_order_type
667 AND register_code = v_register_meaning;
668 ELSE
669 FOR master_org_rec IN c_ec_code_cur(v_act_organization_id, v_act_location_id) LOOP
670 UPDATE JAI_CMN_RG_EXC_INV_NOS
671 SET start_number = v_start_number
672 WHERE organization_id = master_org_rec.organization_id
673 AND location_id = master_org_rec.location_id
674 AND fin_year = p_fin_year
675 AND order_invoice_type = v_order_type
676 AND register_code = v_register_meaning;
677 END LOOP;
678 END IF; -- v_ec_code_gen condition bug#7719911
679 END IF;
680
681 ELSE -- v_start_number is not null
682
683 /* Changed by aiyer for the bug #3071342
684 As the excise invoice generation should not be done in case Domestic Without Excise fpr trading and manufacturing
685 organizations and hence removing the check of v_register_code in 'DOM_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE' from
686 the if statement below */
687
688 /*added by vkaranam for bug #6030615*/
689 OPEN c_org_type (v_Act_organization_id , v_Act_location_id);
690 fetch c_org_type into r_org_type;
691 CLOSe c_org_type;
692
693 -- Start of bug 3071342
694 IF v_register_code IN ('23D_DOMESTIC_EXCISE') OR (p_Called_from = 'INTERORG_XFER' AND r_org_type.trading = 'Y' )/* vkaranam for bug #6030615*/THEN
695 -- End of bug 3071342
696 FOR master_org_rec IN c_tr_ec_code_cur(v_act_organization_id, v_act_location_id) LOOP
697 UPDATE JAI_CMN_RG_EXC_INV_NOS
698 SET gp1 = v_gp1,
699 gp2 = v_gp2
700 WHERE organization_id = master_org_rec.organization_id
701 AND location_id = master_org_rec.location_id
702 AND fin_year = p_fin_year
703 AND order_invoice_type IS NULL
704 AND register_code IS NULL;
705 END LOOP;
706
707 ELSE
708 IF NVL(v_ec_code_gen,'N') = 'N' Then --Added by JMEENA for bug#7719911
709 UPDATE JAI_CMN_RG_EXC_INV_NOS
710 SET gp1 = v_gp1,
711 gp2 = v_gp2
712 WHERE organization_id =v_act_organization_id
713 AND location_id = v_act_location_id
714 AND fin_year = p_fin_year
715 AND order_invoice_type IS NULL
716 AND register_code IS NULL;
717 ELSE
718 FOR master_org_rec IN c_ec_code_cur(v_act_organization_id, v_act_location_id) LOOP
719 UPDATE JAI_CMN_RG_EXC_INV_NOS
720 SET gp1 = v_gp1,
721 gp2 = v_gp2
722 WHERE organization_id = master_org_rec.organization_id
723 AND location_id = master_org_rec.location_id
724 AND fin_year = p_fin_year
725 AND order_invoice_type IS NULL
726 AND register_code IS NULL;
727 END LOOP;
728 END IF; -- v_ec_code_gen condition bug#7719911
729 END IF;
730
731 END IF; -- v_start_number is not null
732
733 END IF; -- if v_excise_inv_no is not null
734
735 Exception
736 When Others then
737 p_errbuf := sqlerrm;
738 END generate_excise_invoice_no ;
739
740
741 /* OPM OF is obsolete with R12 Bug#4487676
742 PROCEDURE gen_opm_excise_invoice_no
743 (P_Ordid IN NUMBER ,
744 P_ORGN_CODE IN VARCHAR2 ,
745 V_ITEM_CLASS IN VARCHAR2 ,
746 P_BOL_ID IN NUMBER ,
747 P_BOLLINE_NO IN NUMBER,
748 P_EXCISE_INV_NUM IN OUT NOCOPY VARCHAR2
749 )
750 IS
751
752 --
753 -- #############################################################################################
754 -- #
755 -- # NAME
756 -- # EXCISE_NUM_GENERATION
757 -- #
758 -- # SNOPSIS
759 -- # Procedure EXCISE_NUM_GENERATION
760 -- #
761 -- # DESCRIPTION
762 -- # Procedure EXCISE_NUM_GENERATION to Generate Excise Invoice Nos
766 -- #########################################################################################*/
763 -- # CREATED BY A.RAINA ON 04/03/2000
764 -- #
765 --
767
768 /* Added by Ramananda for bug#4407165
769 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_setup_pkg.gen_opm_excise_invoice_no';
770
771 Cursor C_Order_Type_code IS
772 Select Order_Type_code
773 from op_ordr_typ a , op_ordr_hdr b
774 Where UPPER(a.order_type) = UPPER(b.order_type)
775 and b.order_id = P_Ordid ;
776
777 -- start commted by Uday on 23-0CT-2001
778 /* Cursor register_code_cur ( s_orgn_code IN Varchar2 ) Is
779 SELECT a.register_code
780 FROM JAI_OM_OE_BOND_REG_HDRS a, JAI_OM_OE_BOND_REG_DTLS b,sy_orgn_mst c
781 WHERE a.organization_id = c.organization_id
782 AND UPPER(c.orgn_code) = UPPER(s_orgn_code) ------org_changed
783 AND a.register_id = b.register_id
784 AND b.order_flag = 'Y'
785 AND b.order_type_id = (select order_type From Op_Ordr_Hdr where Order_id =
786 (select order_id From JAI_OPM_SO_PICK_LINES
787 where bol_id = P_BOL_ID
788 and bolline_no = P_BOLLINE_NO ));
789 -- ended
790
791 -- start modified above code on 23-oct-2001 by Uday.
792 Cursor register_code_cur ( s_orgn_code IN Varchar2 ) Is
793 SELECT a.register_code
794 FROM JAI_OM_OE_BOND_REG_HDRS a, JAI_OM_OE_BOND_REG_DTLS b,org_organization_definitions c
795 WHERE a.organization_id = c.organization_id
796 AND UPPER(c.organization_code) = UPPER(s_orgn_code) ------org_changed
797 AND a.register_id = b.register_id
798 AND b.order_flag = 'Y'
799 AND b.order_type_id = (select order_type From Op_Ordr_Hdr where Order_id =
800 (select order_id From JAI_OPM_SO_PICK_LINES
801 where bol_id = P_BOL_ID
802 and bolline_no = P_BOLLINE_NO ));
803 -- ended
804
805 CURSOR Register_Code_Meaning_Cur(p_register_code IN VARCHAR2, cp_register_type ja_lookups.lookup_type%type ) IS
806 Select meaning
807 From ja_lookups
808 Where UPPER(lookup_code) LIKE UPPER(p_register_code)
809 And UPPER(lookup_type) = cp_register_type ; --UPPER('JAI_REGISTER_TYPE'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980
810
811
812 CURSOR excise_invoice_cur(p_fin_year number , s_orgn_code IN Varchar2 ) IS
813 SELECT NVL(MAX(DOM_NUMBER),0),NVL(MAX(EXP_NUMBER),0)
814 FROM JAI_OPM_EXCISE_INV_NOS
815 WHERE UPPER(orgn_code) = UPPER(s_orgn_code) ---org_changed
816 AND fin_year = p_fin_year
817 AND order_invoice_type IS Null
818 AND register_code IS Null;
819
820 -- start commented by Uday on 23-OCT-2001
821 /* CURSOR fin_year_cur ( s_orgn_code IN Varchar2 ) IS
822 SELECT MAX(a.fin_year)
823 FROM JAI_CMN_FIN_YEARS a ,sy_orgn_mst b
824 WHERE a.organization_id = b.organization_id
825 and UPPER(b.orgn_code) = UPPER(s_orgn_code) -----org_changed
826 and a.fin_active_flag = 'Y';
827 -- end
828
829 -- start modified by Uday on 23-OCT-2001
830 CURSOR fin_year_cur(s_orgn_code varchar2) IS
831 SELECT MAX(a.fin_year)
832 FROM JAI_CMN_FIN_YEARS a ,org_organization_definitions b
833 WHERE a.organization_id = b.organization_id
834 and upper(b.organization_code) = upper(s_orgn_code)
835 and a.fin_active_flag = 'Y';
836 -- end
837
838
839 Cursor Def_Excise_Invoice_Cur(p_fin_year IN NUMBER,
840 p_batch_name IN VARCHAR2, p_register_code IN VARCHAR2, s_orgn_code IN Varchar2 ) IS
841 Select start_number, end_number, jump_by, prefix
842 From JAI_OPM_EXCISE_INV_NOS
843 Where UPPER(orgn_code) = UPPER(s_orgn_code) ---org_changed
844 And fin_year = p_fin_year
845 And UPPER(order_invoice_type) = UPPER(p_batch_name)
846 And UPPER(register_code) = UPPER(p_register_code);
847
848 --And UPPER(nvl(order_invoice_type,'###')) = UPPER(p_batch_name)
849 --And UPPER(nvl(register_code,'###')) = UPPER(NVL(p_register_code,'***'));
850
851
852 v_Order_Type_code Varchar2(10);
853 v_fin_year Number(4);
854 v_gp_1 Number;
855 v_gp_2 Number;
856 v_rg23a_invoice_no Number;
857 v_rg23c_invoice_no Number;
858 v_other_invoice_no Number;
859 v_excise_inv_no Varchar2(200);
860 v_register_code Varchar2(30);
861 v_start_number Number;
862 v_end_number Number;
863 v_jump_by Number;
864 v_order_invoice_type Varchar2(50);
865 v_prefix Varchar2(50);
866 v_meaning Varchar2(80);
867 v_creation_date CONSTANT Date := SYSDATE; --Added CONSTANT Ramananda for File.Sql.35
868 v_created_by Number ;
869 v_last_update_date CONSTANT Date := SYSDATE; --Added CONSTANT Ramananda for File.Sql.35
870 v_last_updated_by Number := 1774 ;
871 v_last_update_login Number := 233965 ;
872 v_exc_invoice_num Varchar2(100);
873
874 --Added for OPM India Localization on 30-05-00 by A.Raina
875 --This part of the code is added for taking care the excise invoice no generation in case the
876 -- "From warehouse" is of another organization other than in which order is made then in that case
877 --Excise Invoice Number should be generated.
881 FROM OP_ORDR_HDR
878
879 Cursor C_From_Whse Is
880 SELECT FROM_WHSE
882 WHERE ORDER_ID = P_Ordid ;
883
884 Cursor C_loc_id ( v_From_Whse IN Varchar2 ) Is
885 SELECT LOCATION_ID
886 FROM HR_LOCATIONS
887 WHERE UPPER(LOCATION_CODE) = UPPER(v_From_Whse) ;
888
889 Cursor C_From_Org_Id ( v_loc_id IN Number ) Is
890 SELECT ORGANIZATION_ID
891 FROM JAI_CMN_INVENTORY_ORGS
892 WHERE LOCATION_ID = v_loc_id ;
893
894 /* Vijay Shankar for Bug# 3151103
895 Cursor C_From_Org_code ( v_From_org_id In Number ) Is
896 SELECT SUBSTR(ORGANIZATION_CODE,1,4)
897 FROM ORG_ORGANIZATION_DEFINITIONS
898 WHERE ORGANIZATION_ID = v_From_org_id ;
899
900
901 Cursor C_From_Org_code ( v_From_whse_code In VARCHAR2 ) Is
902 SELECT B.ORGANIZATION_CODE
903 FROM ORG_ORGANIZATION_DEFINITIONS B,IC_WHSE_MST C
904 WHERE B.ORGANIZATION_CODE = C.ORGN_CODE
905 AND C.WHSE_CODE = v_From_whse_code;
906
907 v_From_whse Varchar2(10);
908 v_From_loc_id Number;
909 v_From_org_id Number;
910 v_From_Org_cod Varchar2(10) := Null ;
911 v_orgn_code Varchar2(10) := Null ;
912
913
914 BEGIN
915
916 /*-------------------------------------------------------------------------------------------------------------------------
917 Change History for File ja_in_excise_num_generation_prc.sql
918
919 Trigger to populate the RG23 Part I table upon issue or return of goods in Gemms
920
921 S.No DD/MM/YY Author and Details of Changes
922 -------------------------------------------------------------------------------------------------------------------------
923 1 22/09/2003 Vijay Shankar for Bug# 3151103, File Version : 712.1
924 When a Sales order transaction is done through child warehouse of a Process Organization, then excise invoice is
925 not getting generated. Fixed the issue by modifying c_from_org_code cursor to fetch parent organization of the
926 warehouse from where the excise invoice has to be generated. This is because localization setup is done for
927 the parent organization, not the warehouse.
928
929 --------------------------------------------------------------------------------------------------------------------------
930
931 OPEN C_Order_Type_code ;
932 FETCH C_Order_Type_code INTO v_order_invoice_type ;
933 CLOSE C_Order_Type_code ;
934
935 --Added for OPM India Localization on 30-05-00 by A.Raina
936 --This part of the code is added for taking care the excise invoice no generation in case the
937 -- "From warehouse" is of another organization other than in which order is made then in that case
938 --Excise Invoice Number should be generated.
939
940 OPEN C_From_Whse ;
941 FETCH C_From_Whse INTO v_From_whse ;
942 CLOSE C_From_Whse ;
943
944 /* Vijay Shankar for Bug# 3151103
945 OPEN C_loc_id ( v_From_whse ) ;
946 FETCH C_loc_id INTO v_From_loc_id ;
947 CLOSE C_loc_id ;
948
949
950 OPEN C_From_Org_Id (v_From_loc_id);
951 FETCH C_From_Org_Id INTO v_From_org_id ;
952 CLOSE C_From_Org_Id ;
953
954
955 -- OPEN C_From_Org_code (v_From_org_id);
956 OPEN C_From_Org_code (v_From_whse);
957 FETCH C_From_Org_code INTO v_From_Org_cod ;
958 CLOSE C_From_Org_code ;
959
960 v_orgn_code := UPPER(v_From_Org_cod) ;
961
962 --end addition.
963
964 OPEN register_code_cur ( v_orgn_code ) ;
965 FETCH register_code_cur INTO v_register_code;
966 CLOSE register_code_cur;
967
968 OPEN fin_year_cur ( v_orgn_code );
969 FETCH fin_year_cur into v_fin_year;
970 CLOSE fin_year_cur;
971
972
973 OPEN register_code_meaning_cur(v_register_code, UPPER('JAI_REGISTER_TYPE'));
974 FETCH register_code_meaning_cur INTO v_meaning;
975 CLOSE register_code_meaning_cur;
976
977 IF v_item_class IN ('CGEX','CGIN') THEN
978
979 OPEN Def_Excise_Invoice_Cur( v_fin_year, v_order_invoice_type, v_meaning ,v_orgn_code);
980 FETCH Def_Excise_Invoice_Cur INTO v_start_number, v_end_number, v_jump_by, v_prefix;
981 CLOSE Def_Excise_Invoice_Cur;
982
983 IF v_start_number IS NOT NULL THEN
984 IF v_register_code IS NOT NULL THEN
985 IF NVL(v_start_number,0) >= NVL(v_end_number,0) AND v_end_number IS NOT NULL THEN
986 RAISE_APPLICATION_ERROR(-20120, 'Excise Invoice Number has been exhausted. ' || ' Increase End Number or enter fresh Start Number and End Number.');
987 END IF;
988 v_rg23c_invoice_no := nvl(v_start_number,0);
989 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,0);
990 IF v_prefix IS NOT NULL THEN
991 v_excise_inv_no := v_prefix||'/'||to_char(v_rg23c_invoice_no);
992
993 ELSE
994 v_excise_inv_no := to_char(v_rg23c_invoice_no);
995
996 END IF;
997 END IF;
998 ELSE
999 OPEN excise_invoice_cur(v_fin_year,v_orgn_code );
1000 FETCH excise_invoice_cur INTO v_gp_1, v_gp_2;
1001 CLOSE excise_invoice_cur;
1002 IF v_register_code IS NOT NULL THEN
1003 -- added 'Bond_Reg' by K V UDAY KUMAR on 23-oct-00 in the below statement
1004
1005 -- the following if modified by Vijay Shankar for Bug# 3151103
1009 v_rg23c_invoice_no := nvl(v_gp_1,0);
1006 -- IF UPPER(v_register_code) IN ('DOMESTIC_EXCISE','EXPORT_EXCISE',
1007 -- '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','BOND_REG') THEN
1008 IF UPPER(v_register_code) IN ('DOMESTIC_EXCISE','23D_DOMESTIC_EXCISE') THEN
1010
1011 v_gp_1 := nvl(v_gp_1,0) + 1;
1012
1013 -- this else modified after consulting support.
1014 -- ELSE
1015 ELSIF UPPER(v_register_code) IN ('EXPORT_EXCISE', '23D_EXPORT_EXCISE', 'BOND_REG') THEN
1016 v_rg23c_invoice_no := nvl(v_gp_2,0);
1017
1018 v_gp_2 := nvl(v_gp_2,0) + 1;
1019
1020 END IF;
1021 v_excise_inv_no := v_rg23c_invoice_no;
1022
1023 END IF;
1024 END IF;
1025 ELSIF UPPER(v_item_class) IN ('RMIN','RMEX') THEN
1026 OPEN Def_Excise_Invoice_Cur( v_fin_year, v_order_invoice_type, v_meaning ,v_orgn_code);
1027 FETCH Def_Excise_Invoice_Cur INTO v_start_number, v_end_number, v_jump_by, v_prefix;
1028 CLOSE Def_Excise_Invoice_Cur;
1029 IF v_start_number IS NOT NULL THEN
1030 IF v_register_code IS NOT NULL THEN
1031 IF NVL(v_start_number,0) >= NVL(v_end_number,0) AND v_end_number IS NOT NULL THEN
1032 RAISE_APPLICATION_ERROR(-20120, 'Excise Invoice Number has been exhausted. ' || ' Increase End Number or enter fresh Start Number and End Number.');
1033 END IF;
1034 v_rg23a_invoice_no := nvl(v_start_number,0);
1035
1036 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,0);
1037
1038 IF v_prefix IS NOT NULL THEN
1039 v_excise_inv_no := v_prefix||'/'||to_char(v_rg23a_invoice_no);
1040
1041
1042 ELSE
1043 v_excise_inv_no := to_char(v_rg23a_invoice_no);
1044
1045 END IF;
1046 END IF;
1047 ELSE
1048 OPEN excise_invoice_cur(v_fin_year,v_orgn_code);
1049 FETCH excise_invoice_cur INTO v_gp_1, v_gp_2;
1050 CLOSE excise_invoice_cur;
1051 IF v_register_code IS NOT NULL THEN
1052 IF UPPER(v_register_code) IN
1053 -- following is modified by Vijay Shankar for Bug# 3151103
1054 -- ('DOMESTIC_EXCISE','EXPORT_EXCISE','23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','BOND_REG') THEN
1055 ('DOMESTIC_EXCISE','23D_DOMESTIC_EXCISE') THEN
1056
1057 v_rg23a_invoice_no := nvl(v_gp_1,0);
1058
1059 v_gp_1 := nvl(v_gp_1,0) + 1;
1060
1061 -- ELSE
1062 ELSIF UPPER(v_register_code) IN ('EXPORT_EXCISE', '23D_EXPORT_EXCISE','BOND_REG') THEN
1063 v_rg23a_invoice_no := nvl(v_gp_2,0);
1064
1065 v_gp_2 := nvl(v_gp_2,0) + 1;
1066 END IF;
1067 v_excise_inv_no := v_rg23a_invoice_no;
1068 END IF;
1069 END IF;
1070 ELSIF UPPER(v_item_class) IN ('FGIN','FGEX','CCIN','CCEX') THEN
1071 OPEN Def_Excise_Invoice_Cur( v_fin_year, v_order_invoice_type, v_meaning, v_orgn_code );
1072 FETCH Def_Excise_Invoice_Cur INTO v_start_number, v_end_number, v_jump_by, v_prefix;
1073 CLOSE Def_Excise_Invoice_Cur;
1074 IF v_start_number IS NOT NULL THEN
1075 IF v_register_code IS NOT NULL THEN
1076 IF NVL(v_start_number,0) >= NVL(v_end_number,0) AND v_end_number IS NOT NULL THEN
1077 RAISE_APPLICATION_ERROR(-20120, 'Excise Invoice Number has been exhausted. ' || ' Increase End Number or enter fresh Start Number and End Number.');
1078 END IF;
1079 v_other_invoice_no := nvl(v_start_number,0);
1080 v_start_number := nvl(v_start_number,0) + nvl(v_jump_by,0);
1081
1082 IF v_prefix IS NOT NULL THEN
1083 v_excise_inv_no := v_prefix||'/'||to_char(v_other_invoice_no);
1084
1085 ELSE
1086 v_excise_inv_no := to_char(v_other_invoice_no);
1087
1088 END IF;
1089 END IF;
1090 ELSE
1091 OPEN excise_invoice_cur(v_fin_year, v_orgn_code);
1092 FETCH excise_invoice_cur INTO v_gp_1, v_gp_2;
1093 CLOSE excise_invoice_cur;
1094 IF v_register_code IS NOT NULL THEN
1095 IF UPPER(v_register_code) IN
1096 -- added 'Bond_Reg' by K V UDAY KUMAR on 23-oct-00 in the below statement
1097 -- below condition modified by Vijay Shankar for Bug# 3151103
1098 -- ('DOMESTIC_EXCISE','EXPORT_EXCISE','23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','BOND_REG') THEN
1099 ('DOMESTIC_EXCISE','23D_DOMESTIC_EXCISE') THEN
1100 v_other_invoice_no := nvl(v_gp_1,0);
1101
1102 v_gp_1 := nvl(v_gp_1,0) + 1;
1103 -- ELSE
1104 ELSIF UPPER(v_register_code) IN ('EXPORT_EXCISE', '23D_EXPORT_EXCISE','BOND_REG') THEN
1105 v_other_invoice_no := nvl(v_gp_2,0);
1106 v_gp_2 := nvl(v_gp_2,0) + 1;
1107 END IF;
1108 v_excise_inv_no := v_other_invoice_no;
1109 END IF;
1110 END IF;
1111 END IF;
1112 IF v_excise_inv_no is Not Null THEN
1113 IF v_start_number IS NOT NULL THEN
1114 UPDATE JAI_OPM_EXCISE_INV_NOS
1115 SET start_number = v_start_number,
1116 last_update_date = v_last_update_date,
1117 last_updated_by = v_last_updated_by,
1118 last_update_login = v_last_update_login
1119 WHERE UPPER(orgn_code) = UPPER(V_orgn_code)
1120 AND fin_year = v_fin_year
1121 AND UPPER(order_invoice_type) = UPPER(v_order_invoice_type)
1122 AND UPPER(register_code) = UPPER(v_meaning);
1123 ELSE
1124 UPDATE JAI_OPM_EXCISE_INV_NOS
1125 SET dom_number = v_gp_1,
1126 exp_number = v_gp_2,
1127 last_update_date = v_last_update_date,
1128 last_updated_by = v_last_updated_by,
1129 last_update_login = v_last_update_login
1130 WHERE UPPER(orgn_code) = UPPER(v_orgn_code) ----org_changed
1131 AND fin_year = v_fin_year
1132 AND order_invoice_type IS Null
1133 AND register_code IS Null;
1134
1135 END IF;
1136 /*
1137 SELECT SUBSTR(v_excise_inv_no, instr(v_excise_inv_no, '/', 1, 1) + 1)
1138 into v_excise_inv_no
1139 from dual;
1140
1141 UPDATE JAI_OPM_SO_PICK_LINES
1142 SET excise_invoice_no = v_excise_inv_no
1143 WHERE UPPER(orgn_code) = UPPER(v_orgn_code) ---org_changed
1144 AND bol_id = p_bol_id
1145 AND bolline_no = P_BOLLINE_NO ;
1146 END IF;
1147 p_excise_inv_num := v_excise_inv_no ;
1148
1149
1150 /* Added by Ramananda for bug#4407165
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1154 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1155 app_exception.raise_exception;
1156
1157 End gen_opm_excise_invoice_no ;
1158 */
1159
1160 FUNCTION get_po_assessable_value(
1161 p_vendor_id IN NUMBER,
1165 ) RETURN NUMBER IS
1162 p_vendor_site_id IN NUMBER,
1163 p_inv_item_id IN NUMBER,
1164 p_line_uom IN VARCHAR2
1166
1167 v_line_uom_class VARCHAR2(45);
1168 v_tax_uom_class VARCHAR2(45);
1169 uom_rate NUMBER;
1170 v_price_list_id NUMBER;
1171 v_assessable_val NUMBER;
1172
1173 CURSOR Fetch_Price_List_Id_Cur IS
1174 SELECT Price_List_Id
1175 FROM JAI_CMN_VENDOR_SITES
1176 WHERE Vendor_Id = p_vendor_id
1177 AND Vendor_Site_Id = NVL( p_vendor_site_id, 0 );
1178
1179 CURSOR Fetch_Price_List1_Id_Cur IS
1180 SELECT Price_List_Id
1181 FROM JAI_CMN_VENDOR_SITES
1182 WHERE vendor_Id = p_vendor_id
1183 AND Vendor_Site_Id = 0;
1184
1185 CURSOR Fetch_Assessable_Val_Cur(cp_item qp_List_Lines_v.product_attribute_context%type) IS
1186 SELECT operand
1187 FROM qp_List_Lines_v
1188 WHERE List_header_id = v_price_list_id
1189 AND product_attribute_context = cp_item --'ITEM' -- cbabu for Bug# 3083335
1190 AND product_Id = p_inv_item_id
1191 AND product_uom_code = p_line_uom
1192 AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
1193 AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
1194
1195
1196 BEGIN
1197 /*-------------------------------------------------------------------------------------------------------------------------
1198 CHANGE HISTORY for File - ja_in_fetch_assessable_value_f.sql
1199 S.No dd/mm/yyyy Author and Details
1200 ---------------------------------------------------------------------------------------------------------------------------
1201 1 05/08/2003 Vijay Shankar for Bug# 3083335, Version: 616.1
1202 Fetch_Assessable_Val_Cur is failing when assessable price list is not attached to the supplier site or
1203 supplier null site and the Client has some data where in QP_LIST_LINES_V.product_id clolumn has non numeric
1204 data. This is fixed by placing an additional condition in the where clause of the cursor to filter
1205 only product_attribute_context is 'ITEM'.
1206
1207 2. 08/30/2004 Ssumaith - bug# 3814739 - File version 115.1
1208
1209 A invalid number exception was occuring in cases where the cursor Fetch_Assessable_Val_Cur was opened
1210 and no price list id was fetched because the setup of additional information is not done.
1211 Code added to return code when so that the error is not encountered.
1212
1213 3 24/04/1005 cbabu for bug#6012570 (5876390) Version: 120.4
1214 Projects Billing Enh.
1215 forward ported from R11i to R12
1216
1217 ---------------------------------------------------------------------------------------- */
1218
1219
1220
1221 --------------------------------------------------------------------------------------------------------------------------*/
1222
1223 OPEN Fetch_Price_List_Id_Cur;
1224 FETCH Fetch_Price_List_Id_Cur INTO v_price_list_id;
1225 CLOSE Fetch_Price_List_Id_Cur;
1226
1227 IF v_price_list_id IS NULL THEN
1228 OPEN Fetch_Price_List1_Id_Cur;
1229 FETCH Fetch_Price_List1_Id_Cur INTO v_price_list_id;
1230 CLOSE Fetch_Price_List1_Id_Cur;
1231 END IF;
1232
1233 -- ssumaith - bug# 3814739
1234 IF V_PRICE_LIST_ID IS NULL THEN
1235 RETURN(NULL);
1236 END IF;
1237 -- ssumaith - bug# 3814739
1238
1239 OPEN Fetch_Assessable_Val_Cur('ITEM'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1240 FETCH Fetch_Assessable_Val_Cur INTO v_assessable_val;
1241 CLOSE Fetch_Assessable_Val_Cur;
1242
1243 RETURN( v_assessable_val );
1244
1245 END get_po_assessable_value;
1246
1247 END jai_cmn_setup_pkg ;
[Home] [Help]