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