[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_OPM_PKG
Source
1 PACKAGE BODY jai_cmn_rg_opm_pkg AS
2 /* $Header: jai_cmn_rg_opm.plb 120.1 2005/07/20 12:57:19 avallabh ship $ */
3
4 PROCEDURE create_rg23_entry
5 (
6 p_iss_recpt_mode varchar2,
7 --p_orgn_code varchar2,
8 p_location_id NUMBER, -- l_whse_code
9 p_ospheader number,
10 p_vendor_id number,
11 p_trans_date date,
12 p_reg_type Varchar2,
13 p_amount Number default 0,
14 p_post_rg23_i Varchar2, -- default 'Y' File.Sql.35 by Brathod
15 p_organization_id number
16 )
17 IS
18
19 /* Added by Ramananda for bug#4407165 */
20 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_opm_pkg.create_rg23_entry';
21
22 Cursor C_osp_lines(p_mode varchar2) IS
23 Select
24 a.act_quantity,
25 a.uom_code,
26 a.excise_payable,
27 a.created_by,
28 a.creation_date,
29 a.last_updated_by,
30 a.last_update_date,
31 a.last_update_login,
32 a.organization_id,
33 a.inventory_item_id
34 From
35 JAI_OPM_OSP_DTLS a,
36 JAI_OPM_OSP_HDRS b
37 Where a.osp_header_id = p_ospheader
38 and a.issue_recpt_flag = p_mode
39 and b.osp_header_id = a.osp_header_id
40 and a.trans_date <= b.extended_due_date
41 and main_rcpt_flag = 'Y';
42
43 Cursor C_vend_range_div(p_vendor_id in number) IS
44 Select
45 excise_duty_Range,
46 excise_duty_division,
47 vendor_site_id
48 From
49 JAI_CMN_VENDOR_SITES
50 Where vendor_id = p_vendor_id ;
51
52 Cursor C_item_attributes(cpn_organization_id JAI_INV_ITM_SETUPS.organization_id%TYPE ,
53 cpn_inv_itm_id JAI_INV_ITM_SETUPS.inventory_item_id%type --p_item_id NUMBER
54 )
55 IS
56 Select
57 item_class,
58 nvl(modvat_flag, 'N')
59 From
60 jai_inv_itm_setups -- JAI_OPM_ITM_MASTERS --
61 Where organization_id = cpn_organization_id
62 AND inventory_item_id = cpn_inv_itm_id ;-- item_id = p_item_id;
63
64 Cursor C_tran_date IS
65 Select transaction_date
66 From JAI_OPM_OSP_HDRS
67 Where osp_header_id = p_ospheader;
68
69 CURSOR fin_year_cur (cpn_organization_id jai_cmn_fin_years.organization_id%type)
70 IS
71 select
72 max(a.fin_year)
73 from
74 JAI_CMN_FIN_YEARS a
75 where a.organization_id = cpn_organization_id
76 and a.fin_active_flag = 'Y';
77
78 srno NUMBER;
79 v_year Number;
80 v_reg_id Number;
81 v_slno Number :=0;
82 v_slno_ii Number :=0;
83 v_slno_iii Number :=0;
84 v_folio_no_i Number :=0;
85 v_folio_no_ii Number :=0;
86 v_i_ospheader Number := NULL;
87 v_i_txndate Date := NULL;
88 v_i_quantity Number := NULL;
89 v_r_ospheader Number := NULL;
90 v_r_txndate Date := NULL;
91 v_r_quantity Number := NULL;
92 v_trans_id Number;
93 v_r_excise_amt Number := NULL;
94 v_i_excise_amt Number := NULL;
95 v_excise_amt Number := NULL;
96 v_excise_duty_Range Varchar2(50);
97 v_excise_duty_div Varchar2(50);
98 v_register_type Varchar2(1);
99 v_item_class Varchar2(10);
100 v_item_excisable Varchar2(1);
101 l_mode Varchar2(1);
102 l_org_id Number;
103 l_tran_date date;
104 excise_amt Number;
105 v_opening_balance Number;
106 v_closing_balance Number;
107 amount_flag varchar2(1); -- := 'N'; -- File.Sql.35 by Brathod
108 lv_proc_status VARCHAR2(3);
109 lv_proc_msg VARCHAR2(1000);
110 ln_vendor_site_id NUMBER (15);
111 lv_reg_id_ii NUMBER (15);
112 lv_reg_id_pla NUMBER (15);
113
114
115 BEGIN
116
117 /*------------------------------------------------------------------------------
118 Filename: create_rg23_entry_prc.sql
119
120 CHANGE HISTORY:
121
122 S.No Date Author and Details
123 ---- ------- ------------------
124 1 17/10/2004 Aparajita.
125 Merge of OPM and Discrete with Obsoletion of PO logistics.
126
127 Changed the definition of cursor C_vend_range_div to fetch the details from
128 JAI_CMN_VENDOR_SITES instead of ja_in_vendors. ja_in_vendors has been dropped
129 for the obsoletion of Obsoletion of PO logistics.
130
131 Clean up was also done for the un necessary cusrosrs and code.
132
133 2 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
134 as required for CASE COMPLAINCE. Version 116.3
135
136 3 08-Jul-2005 Brathod
137 Issue: Inventory Convergence Uptake.
138 Solution:
139 - Code is modified to remove reference of OPM Tables and include reference
140 to related Discrete tables as per the new datamodel of R12.
141 - Direct inserts in OPM tables
142 are removed and instead related discrete API are called to make the entries in RG/PLA
143 Tables.
144 -------------------------------------------------------------------------------*/
145 amount_flag := 'N'; -- File.Sql.35 by Brathod
146
147 IF p_iss_recpt_mode = 'I' THEN
148 v_i_ospheader := p_ospheader;
149 v_i_txndate := p_trans_date;
150 ELSIF p_iss_recpt_mode = 'R' THEN
151 v_r_ospheader := p_ospheader;
152 v_r_txndate := p_trans_date;
153 END IF;
154
155 OPEN C_tran_date;
156 FETCH C_tran_date into l_tran_date;
157 CLOSE C_tran_date;
158
159 /* Commented by Brathod for Inv.Convergence
160 OPEN C_Org_Id;
161 FETCH C_Org_Id INTO l_org_id;
162 CLOSE C_Org_Id;
163 */
164 l_org_id := p_organization_id;
165
166 /* Commented by Brathod for Inv.Convergence
167 OPEN C_Location_Id ;
168 FETCH C_Location_Id INTO l_location_id ;
169 CLOSE C_Location_Id ;
170 */
171
172 OPEN C_vend_range_div(p_vendor_id);
173 FETCH C_vend_range_div INTO v_excise_duty_Range,v_excise_duty_div, ln_vendor_site_id;
174 CLOSE C_vend_range_div;
175
176 OPEN fin_year_cur (l_org_id);
177 FETCH fin_year_cur into v_year;
178 CLOSE fin_year_cur;
179
180 IF p_post_rg23_i = 'N' THEN
181 l_mode := 'R';
182 ELSE
183 l_mode := p_iss_recpt_mode;
184 END IF;
185
186
187 FOR rec IN C_osp_lines(l_mode ) LOOP
188
189 v_i_excise_amt := 0;
190 amount_flag := 'N';
191 v_item_class := NULL;
192 v_item_excisable := NULL;
193
194 OPEN C_item_attributes(rec.organization_id, rec.inventory_item_id );
195 FETCH C_item_attributes INTO v_item_class,v_item_excisable;
196 CLOSE C_item_attributes;
197
198 IF NVL(v_item_excisable,'N') = 'Y' OR p_iss_recpt_mode ='R' THEN
199
200 IF (SUBSTR(v_item_class,1,2) IN ('CG', 'FG')) THEN
201 v_register_type := 'C';
202 ELSE
203 v_register_type := 'A';
204 END IF;
205
206 v_i_excise_amt:= rec.excise_payable;
207
208 IF v_i_excise_amt > 0 then
209 amount_flag := 'Y';
210 End if;
211
212
213 IF p_iss_recpt_mode = 'I' THEN
214
215 v_i_quantity := rec.act_quantity;
216
217 IF p_amount = 0 THEN
218 v_i_excise_amt := rec.excise_payable;
219 ELSE
220 v_i_excise_amt := p_amount;
221 END IF;
222
223 IF v_i_excise_amt > 0 then
224 amount_flag := 'Y';
225 End if;
226
227 v_r_quantity := null;
228
229 ELSIF p_iss_recpt_mode = 'R' THEN
230
231 v_r_quantity := rec.act_quantity;
232
233 IF p_amount = 0 THEN
234 v_r_excise_amt := rec.excise_payable;
235 ELSE
236 v_r_excise_amt := p_amount;
237 END IF;
238
239 IF v_r_excise_amt > 0 then
240 amount_flag := 'Y';
241 End if;
242
243 v_i_quantity := null;
244
245 END IF;
246
247
248 IF p_post_rg23_i = 'Y' THEN
249
250 jai_cmn_rg_23ac_i_trxs_pkg.insert_row
251 (
252 P_REGISTER_ID => v_reg_id
253 ,P_INVENTORY_ITEM_ID => rec.inventory_item_id
254 ,P_ORGANIZATION_ID => rec.organization_id
255 ,P_QUANTITY_RECEIVED => v_r_quantity
256 ,P_RECEIPT_ID => v_r_ospheader
257 ,P_TRANSACTION_TYPE => p_iss_recpt_mode
258 ,P_RECEIPT_DATE => v_r_txndate
259 ,P_PO_HEADER_ID => Null
260 ,P_PO_HEADER_DATE => Null
261 ,P_PO_LINE_ID => Null
262 ,P_PO_LINE_LOCATION_ID => Null
263 ,P_VENDOR_ID => p_vendor_id
264 ,P_VENDOR_SITE_ID => ln_vendor_site_id
265 ,P_CUSTOMER_ID => Null
266 ,P_CUSTOMER_SITE_ID => Null
267 ,P_GOODS_ISSUE_ID => v_i_ospheader
268 ,P_GOODS_ISSUE_DATE => v_i_txndate
269 ,P_GOODS_ISSUE_QUANTITY => v_i_quantity
270 ,P_SALES_INVOICE_ID => Null
271 ,P_SALES_INVOICE_DATE => Null
272 ,P_SALES_INVOICE_QUANTITY => Null
273 ,P_EXCISE_INVOICE_ID => Null
274 ,P_EXCISE_INVOICE_DATE => Null
275 ,P_OTH_RECEIPT_QUANTITY => Null
276 ,P_OTH_RECEIPT_ID => Null
277 ,P_OTH_RECEIPT_DATE => Null
278 ,P_REGISTER_TYPE => v_register_type
279 ,P_IDENTIFICATION_NO => null
280 ,P_IDENTIFICATION_MARK => null
281 ,P_BRAND_NAME => null
282 ,P_DATE_OF_VERIFICATION => null
283 ,P_DATE_OF_INSTALLATION => null
284 ,P_DATE_OF_COMMISSION => null
285 ,P_REGISER_ID_PART_II => null
286 ,P_PLACE_OF_INSTALL => null
287 ,P_REMARKS => 'OPM OSP Transaction'
288 ,P_LOCATION_ID => p_location_id
289 ,P_TRANSACTION_UOM_CODE => rec.uom_code
290 ,P_TRANSACTION_DATE => p_trans_date
291 ,P_BASIC_ED => v_excise_amt
292 ,P_ADDITIONAL_ED => null
293 ,P_OTHER_ED => null
294 ,P_CHARGE_ACCOUNT_ID => NULL
295 ,P_TRANSACTION_SOURCE => 'OPM_OSP'
296 ,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg23_entry'
297 ,P_SIMULATE_FLAG => jai_constants.no
298 ,P_PROCESS_STATUS => lv_proc_status
299 ,P_PROCESS_MESSAGE => lv_proc_msg
300 );
301
302 END IF;
303
304 IF p_reg_type LIKE 'RG%' THEN
305
306 IF p_reg_type = 'RG23A' AND p_iss_recpt_mode ='R' THEN
307 v_register_type := 'A';
308 ELSIF p_reg_type = 'RG23C' AND p_iss_recpt_mode ='R' THEN
309 v_register_type := 'C';
310 END IF;
311
312 If amount_flag = 'Y' then
313 select JAI_CMN_RG_23AC_II_TRXS_S.nextval
314 into v_reg_id
315 from dual;
316
317 if substr(p_reg_type,5,1) = 'A' THEN
318 v_register_type := 'A';
319 elsif substr(p_reg_type,5,1) = 'C' THEN
320 v_register_type := 'C';
321 end if;
322
323 jai_cmn_rg_23ac_ii_pkg.insert_row
324 (
325 P_REGISTER_ID => lv_reg_id_ii
326 ,P_INVENTORY_ITEM_ID => rec.inventory_item_id
327 ,P_ORGANIZATION_ID => rec.organization_id
328 ,P_RECEIPT_ID => v_r_ospheader
329 ,P_RECEIPT_DATE => v_r_txndate
330 ,P_CR_BASIC_ED => v_r_excise_amt
331 ,P_CR_ADDITIONAL_ED => null
332 ,P_CR_OTHER_ED => null
333 ,P_DR_BASIC_ED => v_i_excise_amt
334 ,P_DR_ADDITIONAL_ED => null
335 ,P_DR_OTHER_ED => null
336 ,P_EXCISE_INVOICE_NO => NULL
337 ,P_EXCISE_INVOICE_DATE => NULL
338 ,P_REGISTER_TYPE => v_register_type
339 ,P_REMARKS => 'OPM OSP Transaction'
340 ,P_VENDOR_ID => p_vendor_id
341 ,P_VENDOR_SITE_ID => ln_vendor_site_id
342 ,P_CUSTOMER_ID => null
343 ,P_CUSTOMER_SITE_ID => null
344 ,P_LOCATION_ID => p_location_id
345 ,P_TRANSACTION_DATE => p_trans_date
346 ,P_CHARGE_ACCOUNT_ID => null
347 ,P_REGISTER_ID_PART_I => v_reg_id
348 ,P_REFERENCE_NUM => null
349 ,P_ROUNDING_ID => null
350 ,P_OTHER_TAX_CREDIT => null
351 ,P_OTHER_TAX_DEBIT => null
352 ,P_TRANSACTION_TYPE => p_iss_recpt_mode
353 ,P_TRANSACTION_SOURCE => 'OPM_OSP'
354 ,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg23_entry'
355 ,P_SIMULATE_FLAG => jai_constants.no
356 ,P_PROCESS_STATUS => lv_proc_status
357 ,P_PROCESS_MESSAGE => lv_proc_msg
358 );
359
360 UPDATE jai_cmn_rg_23ac_i_trxs
361 SET register_id_part_ii = lv_reg_id_ii
362 WHERE register_id = v_reg_id;
363
364
365 end if;
366
367 elsif p_reg_type = 'PLA' then
368
369 If amount_flag = 'Y' then
370
371 If p_iss_recpt_mode = 'I' THEN
372 excise_amt := v_i_excise_amt;
373 Else
374 excise_amt := v_r_excise_amt;
375 End if;
376
377 jai_cmn_rg_pla_trxs_pkg.insert_row
378 (
379 P_REGISTER_ID => lv_reg_id_pla
380 ,P_TR6_CHALLAN_NO => NULL
381 ,P_TR6_CHALLAN_DATE => NULL
382 ,P_CR_BASIC_ED => v_r_excise_amt
383 ,P_CR_ADDITIONAL_ED => null
384 ,P_CR_OTHER_ED => null
385 ,P_REF_DOCUMENT_ID => p_ospheader
386 ,P_REF_DOCUMENT_DATE => sysdate
387 ,P_DR_INVOICE_ID => null
388 ,P_DR_INVOICE_DATE => null
389 ,P_DR_BASIC_ED => v_i_excise_amt
390 ,P_DR_ADDITIONAL_ED => null
391 ,P_DR_OTHER_ED => null
392 ,P_ORGANIZATION_ID => rec.organization_id
393 ,P_LOCATION_ID => p_location_id
394 ,P_BANK_BRANCH_ID => null
395 ,P_ENTRY_DATE => sysdate
396 ,P_INVENTORY_ITEM_ID => rec.inventory_item_id
397 ,P_VENDOR_CUST_FLAG => 'V'
398 ,P_VENDOR_ID => p_vendor_id
399 ,P_VENDOR_SITE_ID => ln_vendor_site_id
400 ,P_EXCISE_INVOICE_NO => NULL
401 ,P_REMARKS => 'OPM OSP Transaction'
402 ,P_TRANSACTION_DATE => nvl(l_tran_date, sysdate)
403 ,P_CHARGE_ACCOUNT_ID => null
404 ,P_OTHER_TAX_CREDIT => null
405 ,P_OTHER_TAX_DEBIT => null
406 ,P_TRANSACTION_TYPE => p_iss_recpt_mode
407 ,P_TRANSACTION_SOURCE => 'OPM OSP'
408 ,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg23_entry'
409 ,P_SIMULATE_FLAG => jai_constants.no
410 ,P_PROCESS_STATUS => lv_proc_status
411 ,P_PROCESS_MESSAGE => lv_proc_msg
412 ,P_ROUNDING_ID => NULL
413 );
414
415 end if;
416
417 end if;
418
419 end if;-- for if added by yrd above
420
421 /*
422 IF p_iss_recpt_mode = 'I' AND p_reg_type = 'RG23A' THEN
423
424 Update JAI_CMN_RG_BALANCES
425 set rg23a_balance = rg23a_balance - nvl(v_i_excise_amt,0)
426 Where organization_id = l_org_id;
427
428 ELSIF p_iss_recpt_mode = 'I' AND p_reg_type = 'RG23C' THEN
429
430 Update JAI_CMN_RG_BALANCES
431 set rg23c_balance = rg23c_balance - nvl(v_i_excise_amt,0)
432 Where organization_id = l_org_id;
433
434 ELSIF p_iss_recpt_mode = 'I' AND p_reg_type = 'PLA' THEN
435
436 Update JAI_CMN_RG_BALANCES
437 set pla_balance = pla_balance - nvl(v_i_excise_amt,0)
438 Where organization_id = l_org_id;
439
440 ELSIF p_iss_recpt_mode = 'R' AND p_reg_type = 'RG23A' THEN
441
442 Update JAI_CMN_RG_BALANCES
443 set rg23a_balance = rg23a_balance + nvl(v_r_excise_amt,0)
444 Where organization_id = l_org_id;
445
446 ELSIF p_iss_recpt_mode = 'R' AND p_reg_type = 'RG23C' THEN
447
448 Update JAI_CMN_RG_BALANCES
449 set rg23c_balance = rg23c_balance + nvl(v_r_excise_amt,0)
450 Where organization_id = l_org_id;
451
452 ELSIF p_iss_recpt_mode = 'R' AND p_reg_type = 'PLA' THEN
453
454 Update JAI_CMN_RG_BALANCES
455 set pla_balance = pla_balance + nvl(v_r_excise_amt,0)
456 Where organization_id = l_org_id;
457
458 END IF;
459 */
460
461 END LOOP;
462
463 /* Added by Ramananda for bug#4407165 */
464 EXCEPTION
465 WHEN OTHERS THEN
466 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
467 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
468 app_exception.raise_exception;
469
470 END create_rg23_entry;
471
472 /*
473 PROCEDURE calculate_pla_balances(p_org_id IN NUMBER,p_fin_year IN NUMBER,
474 p_mode VARCHAR2,excise_amt NUMBER,v_opening_balance IN OUT NOCOPY NUMBER,
475 v_closing_balance IN OUT NOCOPY NUMBER) IS
476
477 /* Added by Ramananda for bug#4407165
478 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_opm_pkg.calculate_pla_balances';
479
480 CURSOR balance_cur IS
481 SELECT NVL(pla_balance,0)
482 FROM JAI_CMN_RG_BALANCES a, JAI_CMN_INVENTORY_ORGS b
483 WHERE a.organization_id = b.organization_id
484 and a.location_id = b.location_id
485 and a.organization_id = p_org_id
486 and b.MASTER_ORG_FLAG = 'Y';
487
488 -- end
489
490 Cursor pla_balance_cur IS
491 SELECT NVL(pla_balance,0)
492 FROM JAI_CMN_RG_BALANCES a, JAI_CMN_INVENTORY_ORGS b
493 WHERE a.organization_id = b.organization_id
494 and a.location_id = b.location_id
495 and a.organization_id = p_org_id
496 and b.MASTER_ORG_FLAG = 'Y' ;
497
498 Cursor serial_no_cur IS
499 SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
500 FROM JAI_CMN_RG_PLA_TRXS
501 WHERE organization_id = p_org_id and
502 -- location_id = p_location_id and
503 fin_year = p_fin_year;
504
505 v_previous_serial_no number;
506 v_serial_no number;
507 v_rg_balance number;
508 -- added by uday
509 v_op_bl number;
510 -- end
511 BEGIN
512 OPEN serial_no_cur;
513 FETCH serial_no_cur INTO v_previous_serial_no, v_serial_no;
514 CLOSE serial_no_cur;
515
516 IF NVL(v_previous_serial_no,0) = 0
517 THEN
518 v_previous_serial_no := 0;
519 v_serial_no := 1;
520 END IF;
521
522 IF NVL(v_previous_serial_no,0) > 0
523 THEN
524
525 open balance_cur;
526 FETCH balance_cur INTO v_opening_balance;
527 CLOSE balance_cur;
528
529 v_op_bl := v_opening_balance; -- added by uday
530
531 -- v_opening_balance := v_closing_balance; -- comment by uk
532
533 IF p_mode = 'I' then
534 -- v_closing_balance := nvl(v_closing_balance,0) - nvl(excise_amt,0); -- comment by uk
535 v_closing_balance := nvl(v_op_bl,0) - nvl(excise_amt,0); -- added by uk
536
537 ELSIF p_mode = 'R' then
538 -- v_closing_balance := nvl(v_closing_balance,0) + nvl(excise_amt,0); -- commented by uk
539 v_closing_balance := nvl(v_op_bl,0) + nvl(excise_amt,0); -- added by uk
540 END IF;
541
542 ELSE
543 OPEN pla_balance_cur;
544 FETCH pla_balance_cur INTO v_rg_balance;
545 CLOSE pla_balance_cur;
546
547 -- v_opening_balance := NVL(v_rg_balance,0); -- commented by uk
548 v_op_bl := NVL(v_rg_balance,0); -- added by uk
549
550 v_closing_balance := NVL(v_rg_balance,0);
551
552 IF p_mode = 'I' then
553 v_closing_balance := nvl(v_closing_balance,0) - nvl(excise_amt,0);
554 ELSIF p_mode = 'R' then
555 v_closing_balance := nvl(v_closing_balance,0) + nvl(excise_amt,0);
556
557 END IF;
558 END IF;
559
560 /* Added by Ramananda for bug#4407165
561 EXCEPTION
562 WHEN OTHERS THEN
563 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
564 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
565 app_exception.raise_exception;
566
567 END calculate_pla_balances;
568 */
569
570 procedure create_rg_i_entry
571 (
572 p_location_id NUMBER, --p_whse_code
573 p_ospheader number,
574 p_trans_date date,
575 p_qty number,
576 p_uom_code varchar2,
577 p_created_by number,
578 p_organization_id number,
579 p_inventory_item_id number
580 )
581 IS
582
583 /* Added by Ramananda for bug#4407165 */
584 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_opm_pkg.create_rg_i_entry';
585
586 itemclass varchar2(5);
587 exciseitem varchar2(1); -- := 'N' File.Sql.35 by Brathod
588 l_po_id number(10);
589 l_shipvend_id number(10);
590 l_range_no varchar2(50);
591 l_div_no varchar2(50);
592 l1_folio number;
593 l_vend_site_id number;
594 srno number;
595
596 cursor C_itemclass is
597 select item_class
598 from JAI_INV_ITM_SETUPS --JAI_OPM_ITM_MASTERS
599 where organization_id = p_organization_id
600 AND inventory_item_id = p_inventory_item_id ;--item_id = p_item_id;
601
602 cursor C_po_id is
603 select po_id
604 from JAI_OPM_OSP_HDRS
605 where osp_header_id = p_ospheader;
606
607 cursor C_vendor is
608 select vendor_id, vendor_site_id
609 from po_headers_all --po_ordr_hdr
610 where po_header_id = l_po_id;
611
612 cursor C_vend_ran_div is
613 select excise_duty_range, excise_duty_division
614 from JAI_CMN_VENDOR_SITES
615 where vendor_id = l_shipvend_id;
616
617 cursor C_Excise_Payable IS
618 select payable_excise
619 from JAI_OPM_OSP_HDRS
620 where osp_header_id = p_ospheader ;
621
622 cursor fin_year_cur IS
623 select max(a.fin_year)
624 from JAI_CMN_FIN_YEARS a
625 where a.organization_id = p_organization_id
626 and a.fin_active_flag = 'Y';
627
628
629 l_year number := null;
630 l_slno number := null;
631 l_folio number := null;
632 l_excise number := null;
633 l1_slno number;
634 l_reg_type varchar2(1);
635 ln_reg_id number;
636 ln_login_id number;
637 lv_proc_status VARCHAR2(2);
638 lv_proc_msg VARCHAR2(1000);
639 ln_rg_i_id NUMBER;
640
641 BEGIN
642 /*--------------------------------------------------------------------------------------------------------------------------
643 Procedure to insert into Rg1 table through OSP process
644
645 Change History for FileName create_rg_i_entry_prc.sql
646
647
648 S.No DD/MM/YYYY Author and Description
649 ----------------------------------------------------------------------------------------------------------------------------
650 1 29/09/2004 Vijay Shankar for Bug# 3030446, File Version : 712.1
651
652 population of data into BALANCE_PACKED column is stopped as it was leading to datafixes
653 and also redundant.
654
655 From now on only balance_loose should be used and balance_packed is obsolete
656
657 2 17/10/2004 Aparajita.
658 Merge of OPM and Discrete with Obsoletion of PO logistics.
659
660 Changed the definition of cursor C_vend_ran_div to fetch the details from
661 JAI_CMN_VENDOR_SITES instead of ja_in_vendors. ja_in_vendors has been dropped
662 for the obsoletion of Obsoletion of PO logistics.
663
664 --------------------------------------------------------------------------------------------------------------------------*/
665
666 exciseitem := 'N'; -- File.Sql.35 by Brathod
667
668 open c_itemclass;
669 fetch c_itemclass into itemclass;
670 close c_itemclass;
671
672 open c_po_id;
673 fetch c_po_id into l_po_id;
674 close c_po_id;
675
676 open c_vendor;
677 fetch c_vendor into l_shipvend_id, l_vend_site_id;
678 close c_vendor;
679
680 open c_vend_ran_div;
681 fetch c_vend_ran_div into l_range_no, l_div_no;
682 close c_vend_ran_div;
683
684 open c_excise_payable ;
685 fetch c_excise_payable into l_excise;
686 close c_excise_payable ;
687
688
689 open fin_year_cur;
690 fetch fin_year_cur into l_year;
691 close fin_year_cur;
692
693
694 If substr(itemclass,1,2) = 'RM' OR substr(itemclass,1,2) = 'CG' then
695
696 l_reg_type := jai_general_pkg.get_rg_register_type(itemclass);
697
698 /* Commented by Brathod for Inv.Convergence
699 select max(slno) into srno
700 from JAI_OPM_RG23_I_TRXS
701 where orgn_code = p_orgn_code
702 and register_type = l_reg_type
703 and fin_year = l_year;
704
705 if srno is null then
706 l1_slno := 1;
707 else
708 l1_slno := srno + 1;
709 end if;
710
711 insert into JAI_OPM_RG23_I_TRXS
712 (
713 register_id,
714 slno,
715 fin_year,
716 last_update_date,
717 last_updated_by,
718 last_update_login,
719 creation_date,
720 created_by,
721 TRANSACTION_SOURCE_NUM,
722 transaction_date,
723 inventory_item_id,
724 orgn_code,
725 transaction_type,
726 vendor_id,
727 vendor_site_id,
728 register_type,
729 uom_code,
730 folio_no,
731 entry_date,
732 LOCATION_CODE,
733 range_no,
734 division_no,
735 quantity_received,
736 GOODS_ISSUE_ID_REF,
737 receipt_date
738 )
739 values
740 (
741 JAI_CMN_RG_23AC_I_TXNS_S.nextval,
742 l1_slno,
743 l_year,
744 sysdate,
745 p_created_by,
746 null,
747 sysdate,
748 p_created_by,
749 92,
750 p_trans_date ,
751 p_item_id,
752 p_orgn_code ,
753 'R',
754 l_shipvend_id,
755 l_vend_site_id,
756 l_reg_type,
757 p_uom_code,
758 l1_folio,
759 sysdate,
760 p_whse_code,
761 l_range_no,
762 l_div_no,
763 p_qty,
764 p_ospheader,
765 p_trans_date
766 );
767 */
768
769 jai_cmn_rg_23ac_i_trxs_pkg.insert_row
770 (
771 P_REGISTER_ID => ln_reg_id
772 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
773 ,P_ORGANIZATION_ID => p_organization_id
774 ,P_QUANTITY_RECEIVED => p_qty
775 ,P_RECEIPT_ID => NULL
776 ,P_TRANSACTION_TYPE => 'R'
777 ,P_RECEIPT_DATE => p_trans_date
778 ,P_PO_HEADER_ID => l_po_id
779 ,P_PO_HEADER_DATE => Null
780 ,P_PO_LINE_ID => Null
781 ,P_PO_LINE_LOCATION_ID => Null
782 ,P_VENDOR_ID => l_shipvend_id
783 ,P_VENDOR_SITE_ID => l_vend_site_id
784 ,P_CUSTOMER_ID => Null
785 ,P_CUSTOMER_SITE_ID => Null
786 ,P_GOODS_ISSUE_ID => p_ospheader
787 ,P_GOODS_ISSUE_DATE => null
788 ,P_GOODS_ISSUE_QUANTITY => null
789 ,P_SALES_INVOICE_ID => Null
790 ,P_SALES_INVOICE_DATE => Null
791 ,P_SALES_INVOICE_QUANTITY => Null
792 ,P_EXCISE_INVOICE_ID => Null
793 ,P_EXCISE_INVOICE_DATE => Null
794 ,P_OTH_RECEIPT_QUANTITY => Null
795 ,P_OTH_RECEIPT_ID => Null
796 ,P_OTH_RECEIPT_DATE => Null
797 ,P_REGISTER_TYPE => l_reg_type
798 ,P_IDENTIFICATION_NO => null
799 ,P_IDENTIFICATION_MARK => null
800 ,P_BRAND_NAME => null
801 ,P_DATE_OF_VERIFICATION => null
802 ,P_DATE_OF_INSTALLATION => null
803 ,P_DATE_OF_COMMISSION => null
804 ,P_REGISER_ID_PART_II => null
805 ,P_PLACE_OF_INSTALL => null
806 ,P_REMARKS => 'OPM OSP Transaction'
807 ,P_LOCATION_ID => p_location_id
808 ,P_TRANSACTION_UOM_CODE => p_uom_code
809 ,P_TRANSACTION_DATE => p_trans_date
810 ,P_BASIC_ED => null
811 ,P_ADDITIONAL_ED => null
812 ,P_OTHER_ED => null
813 ,P_CHARGE_ACCOUNT_ID => NULL
814 ,P_TRANSACTION_SOURCE => 'OPM OSP'
815 ,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg_i_entry'
816 ,P_SIMULATE_FLAG => jai_constants.no
817 ,P_PROCESS_STATUS => lv_proc_status
818 ,P_PROCESS_MESSAGE => lv_proc_msg
819 );
820
821 elsif substr(itemclass,1,2) = 'FG' then
822
823 /*select max(slno)
824 into srno
825 from JAI_OPM_RG_I_TRXS
826 where orgn_code = p_orgn_code
827 and fin_year = l_year;
828
829 if srno is null then
830 l_slno := 1;
831 else
832 l_slno := srno + 1;
833 end if;
834
835 insert into JAI_OPM_RG_I_TRXS
836 (
837 register_id,
838 slno,
839 fin_year,
840 last_update_date,
841 last_updated_by,
842 last_update_login,
843 creation_date,
844 created_by,
845 TRANSACTION_SOURCE_NUM,
846 transaction_date,
847 inventory_item_id,
848 orgn_code,
849 transaction_type,
850 --balance_packed, Commented by Vijay Shankar for Bug# 3030446
851 REF_DOC_NO,
852 uom_code,
853 transaction_uom,
854 manufactured_qty,
855 excise_duty_amount,
856 basic_excise_duty_amount,
857 entry_date,
858 LOCATION_CODE,
859 slno_part_ii,
860 folio_no_part_ii
861 )
862 values
863 (
864 JAI_CMN_RG_I_TXNS_S.nextval,
865 l_slno,
866 l_year,
867 sysdate,
868 p_created_by,
869 null,
870 sysdate,
871 p_created_by,
872 92,
873 p_trans_date,
874 p_item_id,
875 p_orgn_code,
876 'R',
877 -- p_qty, Commented by Vijay Shankar for Bug# 3030446
878 p_ospheader,
879 p_uom_code,
880 p_uom_code,
881 p_qty,
882 l_excise,
883 l_excise,
884 sysdate,
885 p_whse_code,
886 null,
887 null
888 );*/
889 ln_login_id := fnd_global.login_id;
890 jai_cmn_rg_i_trxs_pkg.create_rg1_entry
891 (
892 P_REGISTER_ID => ln_rg_i_id
893 ,P_REGISTER_ID_PART_II => null
894 ,P_FIN_YEAR => l_year
895 ,P_SLNO => l_slno
896 ,P_TRANSACTION_ID => null
897 ,P_ORGANIZATION_ID => p_organization_id
898 ,P_LOCATION_ID => p_location_id
899 ,P_TRANSACTION_DATE => p_trans_date
900 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
901 ,P_TRANSACTION_TYPE => 'R'
902 ,P_REF_DOC_ID => p_ospheader
903 ,P_QUANTITY => p_qty
904 ,P_TRANSACTION_UOM_CODE => p_uom_code
905 ,P_ISSUE_TYPE => NULL
906 ,P_EXCISE_DUTY_AMOUNT => l_excise
907 ,P_EXCISE_INVOICE_NUMBER => null
908 ,P_EXCISE_INVOICE_DATE => null
909 ,P_PAYMENT_REGISTER => null
910 ,P_CHARGE_ACCOUNT_ID => null
911 ,P_RANGE_NO => null
912 ,P_DIVISION_NO => null
913 ,P_REMARKS => 'OPM OSP Transaction'
914 ,P_BASIC_ED => null
915 ,P_ADDITIONAL_ED => null
916 ,P_OTHER_ED => null
917 ,P_ASSESSABLE_VALUE => null
918 ,P_EXCISE_DUTY_RATE => null
919 ,P_VENDOR_ID => l_shipvend_id
920 ,P_VENDOR_SITE_ID => l_vend_site_id
921 ,P_CUSTOMER_ID => null
922 ,P_CUSTOMER_SITE_ID => null
923 ,P_CREATION_DATE => SYSDATE
924 ,P_CREATED_BY => p_created_by
925 ,P_LAST_UPDATE_DATE => sysdate
926 ,P_LAST_UPDATED_BY => p_created_by
927 ,P_LAST_UPDATE_LOGIN => ln_login_id
928 ,P_CALLED_FROM => 'jai_cmn_rg_opm_pkg.create_rg_i_entry'
929 );
930
931 end if;
932
933 /* Added by Ramananda for bug#4407165 */
934 EXCEPTION
935 WHEN OTHERS THEN
936 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
937 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
938 app_exception.raise_exception;
939
940 END create_rg_i_entry;
941
942 END jai_cmn_rg_opm_pkg;