[Home] [Help]
PACKAGE BODY: APPS.JAI_INV_MMT_TRIGGER_PKG
Source
1 PACKAGE BODY JAI_INV_MMT_TRIGGER_PKG AS
2 /* $Header: jai_inv_mmt_t.plb 120.17 2012/06/08 17:08:21 mmurtuza ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_INV_MMT_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_INV_MMT_ARI_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARI_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15 v_excise_flag CHAR;
16 v_item_class VARCHAR2 (4);
17 v_f_year NUMBER (4);
18 v_srno NUMBER (15);
19 v_range_no VARCHAR2 (50);
20 v_division_no VARCHAR2 (50);
21 v_opening_qty NUMBER;
22 v_closing_qty NUMBER;
23 v_op_qty NUMBER;
24 v_cl_qty NUMBER;
25 v_reg_type CHAR;
26 v_trans_qty NUMBER;
27 v_mis_qty NUMBER; ---added by Sriram on 04-may-01
28 v_t_qty NUMBER;
29 v_sr_no NUMBER;
30 v_t_type VARCHAR2 (10);
31 v_iss_qty NUMBER;
32 v_iss_id VARCHAR2 (30); -- modified by subbu and sri on 28-nov-00
33 v_rec_qty NUMBER;
34 v_rec_id VARCHAR2 (30);
35 v_iss_date DATE;
36 v_rec_date DATE;
37 v_loc_id NUMBER (15);
38 v_bonded_curr VARCHAR2 (50);
39 v_manufactured_qty NUMBER;
40 v_manufactured_pack_qty NUMBER;
41 v_oth_pur_npe_qty NUMBER;
42 v_temp_item_class VARCHAR2 (4);
43 v_temp_excise_flag CHAR;
44 v_temp_loc_id NUMBER;
45 v_count_temp NUMBER;
46 v_temp_subinv_code VARCHAR2 (20);
47 v_temp_organization_id NUMBER;
48 v_temp_trans_qty NUMBER;
49 v_temp_inv_item_id NUMBER;
50 v_bonded_temp VARCHAR2 (50);
51 v_ins_from_temp_flag NUMBER := 0;
52 v_ins_from_curr_flag NUMBER := 0;
53 v_temp_trans_type_id NUMBER;
54 v_temp_trans_date DATE;
55 v_temp_trans_uom VARCHAR2 (3);
56 v_temp_range_no VARCHAR2 (50);
57 v_temp_division_no VARCHAR2 (50);
58 v_insert_flag NUMBER := 0;
59 v_rg23_flag NUMBER := 0;
60 v_rg_flag NUMBER := 0;
61 v_manu_pkd_qty NUMBER;
62 v_manu_loose_qty NUMBER;
63 v_oth_purpose VARCHAR2 (30);
64 v_bal_packed NUMBER;
65 v_bal_loose NUMBER;
66 v_pr_uom_code VARCHAR2 (3);
67 v_temp_pr_uom_code VARCHAR2 (3);
68 v_uom_qty NUMBER;
69 v_pr_uom_class VARCHAR2 (45);
70 v_trans_uom_class VARCHAR2 (45);
71 v_temp_transaction_set_id NUMBER;
72 v_temp_transaction_id NUMBER; -- cbabu 25/07/02 for Bug#2480524
73 v_manu_qty NUMBER;
74
75 /* --Ramananda for File.Sql.35 */
76 v_trans_uom_code VARCHAR2 (3); -- := pr_new.transaction_uom;
77 v_item_id NUMBER; -- := pr_new.inventory_item_id;
78 v_new_trans_qty NUMBER ; -- := pr_new.transaction_quantity;
79 v_new_adjust_qty NUMBER ; -- := pr_new.quantity_adjusted;
80 v_debug VARCHAR2(1); -- :='Y';
81 /* --Ramananda for File.Sql.35 */
82
83 v_temp_modvat_flag VARCHAR2 (1); --for Sub inventory transfer first record
84 v_modvat_flag VARCHAR2 (1);
85 v_trading_flag VARCHAR2 (1);
86 v_trading_curr VARCHAR2 (1);
87 v_trading_temp VARCHAR2 (1);
88 v_item_trading_flag VARCHAR2 (1);
89 v_temp_item_trading_flag VARCHAR2 (1);
90 v_manufacturing_flag VARCHAR2 (1);
91 v_sub_qty NUMBER; --- Start adding on 29-Mar-2001
92 v_sr_no1 NUMBER; ---added by Sriram on 18-may-2001
93 v_srno1 NUMBER (15) := 0;
94 v_manu_home_qty NUMBER; --Added by satya on 23-oct-01
95 v_hit_rg23_qty NUMBER; --Added by Nagaraj.s for Bug2649405
96 v_hit_rg1_qty NUMBER; --Added by Nagaraj.s for Bug2649405
97 v_other_npe_qty Number; -- Sriram -- Bug # 3258066
98
99 ln_last_updated_by JAI_CMN_TRANSACTIONS_T.LAST_UPDATED_BY%TYPE;
100 ln_created_by JAI_CMN_TRANSACTIONS_T.CREATED_BY%TYPE;
101 ln_last_update_login JAI_CMN_TRANSACTIONS_T.LAST_UPDATE_LOGIN%TYPE;
102
103 CURSOR loc_id_cur (p_subinv_code IN VARCHAR2, p_organization_id IN NUMBER)
104 IS
105 SELECT location_id
106 FROM JAI_INV_SUBINV_DTLS
107 WHERE sub_inventory_name = p_subinv_code
108 AND organization_id = p_organization_id;
109
110 CURSOR item_class_cur (p_inv_item_id IN NUMBER, p_organization_id IN NUMBER)
111 IS
112 SELECT a.excise_flag, a.item_class, a.modvat_flag, a.item_trading_flag
113 FROM JAI_INV_ITM_SETUPS a
114 WHERE a.inventory_item_id = p_inv_item_id
115 AND a.organization_id = p_organization_id;
116
117 CURSOR fin_year_cur (p_org_id NUMBER)
118 IS
119 SELECT MAX (a.fin_year)
120 FROM JAI_CMN_FIN_YEARS a
121 WHERE fin_active_flag = 'Y' AND organization_id = p_org_id;
122
123 CURSOR range_division_cur (p_organization_id IN NUMBER, p_loc_id IN NUMBER)
124 IS
125 SELECT NVL (excise_duty_range, ' '), NVL (excise_duty_division, ' ')
126 FROM JAI_CMN_INVENTORY_ORGS
127 WHERE organization_id = p_organization_id AND location_id = p_loc_id;
128
129 CURSOR srno_i_cur (
130 p_organization_id IN NUMBER,
131 p_inv_item_id IN NUMBER,
132 p_loc_id IN NUMBER,
133 p_reg_type IN CHAR,
134 p_f_year IN NUMBER
135 )
136 IS
137 SELECT MAX (slno)
138 FROM JAI_CMN_RG_23AC_I_TRXS
139 WHERE organization_id = p_organization_id
140 AND location_id = p_loc_id
141 AND inventory_item_id = p_inv_item_id
142 AND register_type = p_reg_type
143 AND fin_year = p_f_year;
144
145
146 /*
147 CURSOR SRNO_II_CUR ( p_loc_id IN NUMBER, p_f_year IN NUMBER) IS
148 SELECT MAX(slno)
149 FROM JAI_CMN_RG_I_TRXS
150 WHERE organization_id = pr_new.organization_id and
151 location_id = p_loc_id and
152 inventory_item_id = pr_new.inventory_item_id and
153 fin_year = p_f_year;
154 */
155
156 CURSOR srno_ii_cur (p_loc_id IN NUMBER, p_f_year IN NUMBER)
157 IS
158 SELECT slno, balance_packed, balance_loose
159 FROM JAI_CMN_RG_I_TRXS
160 WHERE organization_id = pr_new.organization_id
161 AND location_id = p_loc_id
162 AND inventory_item_id = pr_new.inventory_item_id
163 AND fin_year = p_f_year
164 AND slno = (SELECT MAX (slno)
165 FROM JAI_CMN_RG_I_TRXS
166 WHERE organization_id = pr_new.organization_id
167 AND location_id = p_loc_id
168 AND inventory_item_id = pr_new.inventory_item_id
169 AND fin_year = p_f_year);
170
171 CURSOR opening_balance_cur (
172 p_organization_id IN NUMBER,
173 p_inv_item_id IN NUMBER,
174 p_sr_no IN NUMBER,
175 p_loc_id IN NUMBER,
176 p_reg_type IN CHAR,
177 p_f_year IN NUMBER
178 )
179 IS
180 SELECT opening_balance_qty, closing_balance_qty
181 FROM JAI_CMN_RG_23AC_I_TRXS
182 WHERE slno = p_sr_no
183 AND organization_id = p_organization_id
184 AND location_id = p_loc_id
185 AND register_type = p_reg_type
186 AND fin_year = p_f_year
187 AND inventory_item_id = p_inv_item_id;
188
189 CURSOR count_temp_cur
190 IS
191 SELECT COUNT (*)
192 FROM JAI_CMN_TRANSACTIONS_T
193 WHERE transaction_set_id = pr_new.transaction_set_id;
194
195 CURSOR retrieve_temp_cur
196 IS
197 SELECT subinventory_code, organization_id, inventory_item_id,
198 transaction_quantity, transaction_type_id, transaction_date,
199 transaction_uom, transaction_set_id,
200 transaction_id -- cbabu for Bug# 2480584
201 FROM JAI_CMN_TRANSACTIONS_T
202 WHERE transaction_set_id = pr_new.transaction_set_id;
203
204
205 /*
206 CURSOR c_subinv_flags(p_subinv_code IN Varchar2, p_organization_id IN NUMBER) IS
207 SELECT bonded
208 FROM JAI_INV_SUBINV_DTLS
209 WHERE sub_inventory_name = p_subinv_code AND
210 organization_id = p_organization_id;
211 */
212 --added on 04/99/99
213 CURSOR c_subinv_flags (
214 p_subinv_code IN VARCHAR2,
215 p_organization_id IN NUMBER
216 )
217 IS
218 SELECT bonded, trading
219 FROM JAI_INV_SUBINV_DTLS
220 WHERE sub_inventory_name = p_subinv_code
221 AND organization_id = p_organization_id;
222
223 CURSOR fetch_type_name(v_transaction_type_id IN NUMBER)
224 IS
225 SELECT transaction_type_name
226 FROM mtl_transaction_types
227 WHERE transaction_source_type_id = v_transaction_type_id;
228
229 -- cbabu 25/07/02 for Bug#2480524, start
230 /*Commented for bug #12615773 Start */
231 /*v_misc_recpt_rg_update NUMBER;
232 v_misc_issue_rg_update NUMBER;
233 CURSOR c_txn_type_id(v_transaction_type_name IN VARCHAR2)
234 IS
235 SELECT transaction_type_id
236 FROM mtl_transaction_types
237 WHERE transaction_type_name = v_transaction_type_name;
238 -- cbabu 25/07/02 for Bug#2480524, end
239
240 --Start, PROJECTS COSTING IMPL Bug#6012567(5765161)
241 CURSOR c_projects_flag(cp_transaction_type_id IN number)
242 IS
243 SELECT nvl(type_class,-1) projects_flag
244 FROM mtl_transaction_types
245 WHERE transaction_type_id = cp_transaction_type_id;
246
247 ln_projects_flag mtl_transaction_types.type_class%type; /* PROJECTS COSTING IMPL */
248 /* End, PROJECTS COSTING IMPL Bug#6012567 (5765161)*/
249 /*Commented for bug #12615773 End */
250
251 CURSOR get_pr_uom_cur (v_item_id IN NUMBER, v_org_id IN NUMBER)
252 IS
253 SELECT primary_uom_code
254 FROM mtl_system_items
255 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
256
257 CURSOR chk_uom_cur (uom IN VARCHAR2)
258 IS
259 SELECT uom_class
260 FROM mtl_units_of_measure
261 WHERE unit_of_measure = uom;
262
263 /* -- cbabu 25/07/02 for Bug#2480524
264 CURSOR get_subinv_dtl_cur
265 IS
266 SELECT manufacturing, trading
267 FROM JAI_CMN_INVENTORY_ORGS
268 WHERE organization_id = pr_new.organization_id AND location_id = 0;
269 */
270 -- cbabu, to execute this trigger only for indian operating units
271 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%TYPE;
272 v_currency_code gl_sets_of_books.currency_code%TYPE;
273
274 /* Bug 5413264. Added by Lakshmi Gopalsami
275 Removed the cursor Fetch_Book_Id_Cur
276 */
277 CURSOR Sob_Cur(p_gl_set_of_bks_id IN NUMBER) IS
278 SELECT Currency_code
279 FROM gl_sets_of_books
280 WHERE set_of_books_id = p_gl_set_of_bks_id;
281
282 -- cbabu
283
284 --start additions for bug#8530264
285
286 cursor get_qty_update_flag
287 is
288 select quantity_register_flag
289 from jai_rcv_transactions
290 where transaction_id in(select parent_transaction_id from jai_rcv_transactions where transaction_id=pr_new.source_line_id) ;
291
292 lv_qty_register_flag VARCHAR2(1);
293 --end additions for bug#8530264
294
295 /*bug 9122545*/
296 CURSOR c_org_addl_rg_flag(cp_organization_id jai_cmn_inventory_orgs.organization_id%TYPE,
297 cp_location_id jai_cmn_inventory_orgs.location_id%TYPE)
298 IS
299 SELECT nvl(allow_negative_rg_flag,'N')
300 FROM jai_cmn_inventory_orgs
301 WHERE organization_id = cp_organization_id
302 AND location_id = cp_location_id;
303
304 lv_allow_negative_rg_flag jai_cmn_inventory_orgs.allow_negative_rg_flag%TYPE;
305 /*end bug 9122545*/
306 BEGIN
307 pv_return_code := jai_constants.successful ;
308 /*-------------------------------------------------------------------------------------------------------------------------
309 S.No Date(DD/MM/YY) Author and Details of Changes
310 ---- -------------- -----------------------------
311 1 25/07/02 Vijay Shankar, Bug# 2480584, Version: 615.1
312 Added Code for back tracking from RG1. From now onwards deleveloper can back track RG1 record corresponding
313 to MTL_MATERIAL_TRANSACTIONS as JAI_CMN_RG_I_TRXS.REF_DOC_NO = to_char(MTL_MATERIAL_TRANSACTIONS.transaction_id)
314 Code is modified to take care of bonded and trading flags, if the subinventories are not defined in localization setup.
315 During Subinventory transacfer transaction(i.e transaction_action_id = 2), the serial number and balances
316 are not calculated properly, which is resolved in this bug.
317
318 2 29/07/02 Vijay Shankar, Bug# 2480584, Version: 615.2
319 Code added to hit RG register during pick release/staging transfer
320
321 3 30/08/02 Vijay Shankar, Bug# 2541366, Version: 615.3
322 When a subinventory transfer is made from Bonded to Nonbonded subinventory, then balances are updated wrongly
323 in RG1 register. The reason for problem: location_id used to fetch the serial number should be temp location id
324 instead of present transaction location id.
325
326 4 09/12/02 Nagaraj.s, Bug# 2649405, Version: 615.4
327 As per the functional requirement, is needed that for CCIN, CCEX
328 classes, in case of an Miscellaneous Receipt(RG Update). RG1 Register
329 is to be hit and in case of an Miscellaneous Issue(RG Update), it should
330 first hit to the tune of the Closing Quantity available in RG23 PART I
331 and the rest should be hit in RG 1 Register.
332 This has been taken care by commenting pieces of code with Issue and with
333 ITEM Class CCIN and replaced by an elsif clause which checks the balances
334 in RG23 PART I Register and hits RG23 PART I and the rest Quantity hits
335 RG1 Register.
336
337 5 31/12/02 cbabu for Bug# 2728521, Version: 615.5
338 Coding is done as per the functional inputs specified in the above bug for subinvetory transfer transaction
339
340 6 13/01/03 Nagaraj.s for Bug#2744695 Version : 615.6
341 Changed the Insert statement of JAI_CMN_RG_I_TRXS table from
342 --to_other_factory_n_pay_ed_qty to other_purpose_n_pay_ed_qty as per
343 the Functional Requirement.
344 and also previously for FGIN and CCIN class of Items, the v_trans_qty
345 was null which is changed to the Abs(v_new_trans_qty) for CCIN class.
346
347 7 03/04/03 Vijay Shankar for Bug# 2851028 Version : 615.7
348 Move Order Issue Transaction is taken care with this bug. If specified transcation is done, then it is routed to
349 execute the code corresponding to Misc. Issue (RG Update) transaction.
350
351 8 23/04/03 Vijay Shankar for Bug# 2915814, FileVersion : 615.8
352 Coding is done as per the functional inputs specified in Bug# 2649405 for 'WIP Issue' transaction.
353 i.e when a WIP Issue transaction is done for CCIN items, then it should hit RG23 Part I register first
354 and then if quantity is not available with RG23 Part I it should hit RG1
355
356 9. 03/12/2003 Ssumaith - Bug # 3258066 and 3258269 File Version 617.1
357 For Production Input and Production Receipt , The manufactured_qty and other_qty_n_pay_ed fields wee
358 not updated correctly.The requirement was that for Production Issue , the way it needed to be done was
359 Only Other_purpose_n_pay_ed column needed to be updated with the transaction Quantity.
360
361 For Production Receipt
362 Only Manufactured_loose_qty needed to be updated
363 These changes have been done by writing an If condition which selectively makes the variables Null
364
365 10. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.1
366 Check whether india localization is being used was done using a INR check in every trigger.
367 This check has now been moved into a new package and calls made to this package from this trigger
368 If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
369 Hence if this function returns FALSE , control should return.
370
371 11. 19-jan-2005 ssumaith - bug#4130515 file version 115.2
372 An exit condition was commented which was causing the control to go into an infinite loop.
373 This exit condition has been uncommented and it resolves the issue.
374
375 12 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
376 DB Entity as required for CASE COMPLAINCE. Version 116.1
377
378 13. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
379 Removal of SQL LITERALs is done
380 14. 28-Nov-2005 Aiyer for bug# 4764510. File Version: 120.1
381 Added the who columns in the insert into statment for the table JAI_CMN_TRANSACTIONS_T.
382 Dependencies due to this change:-
383 None
384
385 15. 13-Feb-2007 bgowrava for forward porting bug#5275865 (11i bug#5217272) Version# 120.3
386 Issue : In case of a WIP Assebly transaction, data should be entered into
387 ja_in_rg_i as a "PR" type of transaction and not a "PI" type of transaction.
388
389 Fix : Moved the condition to check for Assembly Return under the "PR"
390 transaction type.
391
392 16 24-Apr-2007 cbabu for forward porting bug#6012567 (5765161) version#120.4
393 forward ported the Project Costing changes R12
394
395 17. 21-Oct-2008 CSahoo for bug#4541316, File Version 120.2.12000000.5
396 Added condition for Internal Sales Order transaction to hit RG-I
397 Internal Sales Order in mtl_material_transactions is defined as
398 (transaction_type_id =53, transaction_action_id =28, transaction_source_type_id = 8)
399 18. 19-jun-2009 vkaranam for bug#8530264 ,File Version 120.2.12000000.7
400 Issue:
401 For Correction of Deliver ,Issue Adjustment(IA) is generated due to which Onhand quantity does
402 not synchronize with quantity register.
403 Fix:
404 Issue Adjustment for Correction of Delivery should not be generated if parent
405 transaction (delivery) is not generated in qty register.
406
407 query by bug number to see the changes.
408
409 19. 27-Nov-2009 bug 9122545 File version 120.2.12000000.8 / 120.5.12010000.5 / 120.9
410 Checked the setup option to allow negative quantity in RG before raising the error
411 "Enough RG23 Part1 balances do not exist".
412
413 16 05/Apr/2010 Bug 9550254
414 The opening balance for the RG23 Part I and RG I has been derived from the
415 previous finyear closing balance, if no entries found for the current year.
416
417 20 17-Jun-2011 Bug 12615773
418 Issue : User created transaction type not updating RG registers
419 Fix : Removed the hard coding of transaction_type_id for Miscellaneous transactions,
420 so that all user created transaction types are also picked up which are created for the same
421 source and action types.
422
423 Future Dependencies For the release Of this Object:-
424 (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/
425 A datamodel change )
426 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
427 Current Version Current Bug Dependent Files Version Author Date Remarks
428 Of File On Bug/Patchset Dependent On
429
430 ja_in_tran_rg_entry_trg.sql
431 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
432 115.1 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 Ssumaith 29-Nov-2004 Call to this function.
433 ja_in_util_pkg_s.sql 115.0 Ssumaith
434
435 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
436
437 /* --Ramananda for File.Sql.35 */
438 v_trans_uom_code := pr_new.transaction_uom;
439 v_item_id := pr_new.inventory_item_id;
440 v_new_trans_qty := pr_new.transaction_quantity;
441 v_new_adjust_qty := pr_new.quantity_adjusted;
442 v_debug := 'Y';
443 ln_last_updated_by := fnd_global.user_id; /* added by aiyer for the bug 4764510 */
444 ln_created_by := ln_last_updated_by; /* added by aiyer for the bug 4764510 */
445 ln_last_update_login := fnd_global.login_id;/* added by aiyer for the bug 4764510 */
446 /* --Ramananda for File.Sql.35 */
447
448 /* Bug 5413264. Added by Lakshmi Gopalsami
449 Commented the following call as this check is already done in trigger
450 and the variable v_gl_set_of_bks_id is not used anywhere.
451 OPEN Fetch_Book_Id_Cur(pr_new.organization_id);
452 FETCH Fetch_Book_Id_Cur INTO v_gl_set_of_bks_id;
453 CLOSE Fetch_Book_Id_Cur;
454 */
455
456 --If jai_cmn_utils_pkg.check_jai_exists(P_CALLING_OBJECT => 'JA_IN_TRAN_RG_ENTRY' , P_SET_OF_BOOKS_ID => v_gl_set_of_bks_id) = false then
457 --return;
458 --end if;
459
460 /* following code commented and added the above code instead to return in case of NON-India Localization implementation */
461 /* OPEN Sob_cur(v_gl_set_of_bks_id);
462 FETCH Sob_cur INTO v_currency_code;
463 CLOSE Sob_cur;
464
465 IF NVL(v_currency_code,'###') != 'INR' THEN
466 RETURN;
467 -- insert into debug_Data values ( 'v_currency_code = '||v_currency_code);
468 END IF;
469 */
470
471 --start additions by vkaranam for bug#7605535
472 if ((PR_NEW.transaction_action_id = 27 AND PR_NEW.transaction_source_type_id = 12)
473 OR
474 (PR_NEW.transaction_action_id = 1 AND PR_NEW.transaction_source_type_id = 12)
475 )
476 then
477 UPDATE jai_om_oe_rma_lines
478 SET received_flag = 'Y',
479 last_update_date = pr_new.last_update_date,
480 last_updated_by = pr_new.last_updated_by,
481 last_update_login = pr_new.last_update_login
482 WHERE rma_line_id = pr_new.TRX_SOURCE_LINE_ID; --added for bug#7605535,17 dec
483
484 end if;
485 --end additions by vkaranam for bug#7605535
486
487 OPEN get_pr_uom_cur (pr_new.inventory_item_id, pr_new.organization_id);
488 FETCH get_pr_uom_cur INTO v_pr_uom_code;
489 CLOSE get_pr_uom_cur;
490
491 IF NVL (v_pr_uom_code, '@##') = v_trans_uom_code
492 THEN
493 v_new_trans_qty := pr_new.transaction_quantity;
494 v_new_adjust_qty := pr_new.quantity_adjusted;
495 ELSIF NVL (v_pr_uom_code, '@##') <> v_trans_uom_code
496 THEN
497 inv_convert.inv_um_conversion (
498 v_trans_uom_code,
499 v_pr_uom_code,
500 v_item_id,
501 v_uom_qty
502 );
503
504 IF NVL (v_uom_qty, 0) <= 0
505 THEN
506 OPEN chk_uom_cur (v_pr_uom_code);
507 FETCH chk_uom_cur INTO v_pr_uom_class;
508 CLOSE chk_uom_cur;
509
510 OPEN chk_uom_cur (v_trans_uom_code);
511 FETCH chk_uom_cur INTO v_trans_uom_class;
512 CLOSE chk_uom_cur;
513
514 IF v_pr_uom_class <> v_trans_uom_class
515 THEN
516 v_uom_qty := 0;
517 ELSE
518 inv_convert.inv_um_conversion (
519 v_trans_uom_code,
520 v_pr_uom_code,
521 0,
522 v_uom_qty
523 );
524 END IF;
525 END IF;
526
527 v_new_trans_qty := NVL (v_new_trans_qty, 0) * NVL (v_uom_qty, 0);
528 v_new_adjust_qty := NVL (v_new_adjust_qty, 0) * NVL (v_uom_qty, 0);
529 END IF;
530
531 OPEN c_subinv_flags (pr_new.subinventory_code, pr_new.organization_id);
532 FETCH c_subinv_flags INTO v_bonded_curr, v_trading_flag;
533
534 -- cbabu 25/07/02 for Bug#2480524
535 IF c_subinv_flags%NOTFOUND THEN
536 v_bonded_curr := 'N';
537 v_trading_curr := 'N';
538 ELSE
539 IF nvl(v_trading_flag,'N') = 'Y' THEN
540 v_trading_curr := 'Y';
541 v_bonded_curr := 'N';
542 ELSIF nvl(v_bonded_curr,'N') = 'Y' THEN
543 v_bonded_curr := 'Y';
544 v_trading_curr := 'N';
545 ELSE
546 v_bonded_curr := 'N';
547 v_trading_curr := 'N';
548 END IF;
549 END IF;
550 CLOSE c_subinv_flags;
551
552 OPEN loc_id_cur (pr_new.subinventory_code, pr_new.organization_id);
553 FETCH loc_id_cur INTO v_loc_id;
554 CLOSE loc_id_cur;
555
556 IF v_loc_id IS NULL THEN
557 v_loc_id := 0;
558 END IF;
559
560 OPEN item_class_cur (pr_new.inventory_item_id, pr_new.organization_id);
561 FETCH item_class_cur INTO v_excise_flag, v_item_class, v_modvat_flag, v_item_trading_flag;
562 CLOSE item_class_cur;
563
564 OPEN fin_year_cur (pr_new.organization_id);
565 FETCH fin_year_cur INTO v_f_year;
566 CLOSE fin_year_cur;
567
568 OPEN range_division_cur (pr_new.organization_id, v_loc_id);
569 FETCH range_division_cur INTO v_range_no, v_division_no;
570 CLOSE range_division_cur;
571
572
573 ------3rd case starts here for
574 /* 3. Inventory sub transfer/ Replenish supply subinventory.
575 3a. Staging transfer of a sales order (transaction_type_id = 52, transaction_action_id = 28, transaction_source_type_id = 2)
576 */
577 IF ( pr_new.transaction_action_id = 2 AND pr_new.transaction_source_type_id = 13 ) --sub inventory
578 OR
579 ( pr_new.transaction_action_id = 2 AND pr_new.transaction_source_type_id = 4 ) --added by satya
580 OR
581 ( pr_new.transaction_action_id = 28 AND pr_new.transaction_source_type_id = 2 ) -- cbabu 29/07/2002 for Bug#2428044
582 OR
583 ( pr_new.transaction_type_id = 53 AND pr_new.transaction_action_id = 28 AND pr_new.transaction_source_type_id = 8 ) --added for bug#4541316
584 THEN
585
586 --Insert into debug_Data values ( to_char(pr_new.creation_date, 'dd-mon-yyyy hh24:mi:ss')||', T_type_id = '||pr_new.transaction_type_id||', t_id = '||pr_new.transaction_id
587 -- ||', t_action_id = '||pr_new.transaction_action_id||', subinventory = '||pr_new.subinventory_code
588 -- ||', o_id = '||pr_new.organization_id||', t_src_type_id = '||pr_new.transaction_source_type_id
589 -- ||', t_src_id = '||pr_new.transaction_source_id );
590
591 OPEN count_temp_cur;
592 FETCH count_temp_cur INTO v_count_temp;
593 CLOSE count_temp_cur;
594
595 IF v_count_temp = 0 THEN
596 /*
597 || Added the who columns in the insert statement
598 */
599 INSERT INTO JAI_CMN_TRANSACTIONS_T
600 ( transaction_set_id ,
601 inventory_item_id ,
602 organization_id ,
603 subinventory_code ,
604 transaction_type_id ,
605 transaction_id ,
606 transaction_date ,
607 transaction_quantity ,
608 transaction_uom ,
609 created_by ,
610 creation_date ,
611 last_updated_by ,
612 last_update_date ,
613 last_update_login
614 )
615 VALUES ( pr_new.transaction_set_id ,
616 pr_new.inventory_item_id ,
617 pr_new.organization_id ,
618 pr_new.subinventory_code ,
619 pr_new.transaction_type_id ,
620 pr_new.transaction_id ,
621 pr_new.transaction_date ,
622 v_new_trans_qty ,
623 pr_new.transaction_uom ,
624 ln_created_by , /* Added by aiyer for the bug 4764510 */
625 sysdate , /* Added by aiyer for the bug 4764510 */
626 ln_last_updated_by , /* Added by aiyer for the bug 4764510 */
627 sysdate , /* Added by aiyer for the bug 4764510 */
628 ln_last_update_login
629 );
630 ELSE
631 OPEN retrieve_temp_cur;
632 FETCH retrieve_temp_cur INTO v_temp_subinv_code, v_temp_organization_id, v_temp_inv_item_id,
633 v_temp_trans_qty, v_temp_trans_type_id, v_temp_trans_date, v_temp_trans_uom, v_temp_transaction_set_id,
634 v_temp_transaction_id; -- cbabu 25/07/02 for Bug#2480524
635 CLOSE retrieve_temp_cur;
636
637 -- cbabu 25/07/02 for Bug#2480524
638 OPEN c_subinv_flags (v_temp_subinv_code, v_temp_organization_id);
639 FETCH c_subinv_flags INTO v_bonded_temp, v_trading_flag;
640
641 IF c_subinv_flags%NOTFOUND THEN
642 v_bonded_temp := 'N';
643 v_trading_temp := 'N';
644 ELSE
645 IF nvl(v_trading_flag,'N') = 'Y' THEN
646 v_trading_temp := 'Y';
647 v_bonded_temp := 'N';
648 ELSIF nvl(v_bonded_temp,'N') = 'Y' THEN
649 v_bonded_temp := 'Y';
650 v_trading_temp := 'N';
651 ELSE
652 v_bonded_temp := 'N';
653 v_trading_temp := 'N';
654 END IF;
655 END IF;
656 ---------
657 CLOSE c_subinv_flags;
658
659 OPEN loc_id_cur (v_temp_subinv_code, v_temp_organization_id);
660 FETCH loc_id_cur INTO v_temp_loc_id;
661 CLOSE loc_id_cur;
662
663 IF v_temp_loc_id IS NULL THEN
664 v_temp_loc_id := 0;
665 END IF;
666
667 IF v_trading_temp = 'Y' THEN
668 IF v_trading_curr = 'Y' THEN
669 IF v_temp_loc_id <> v_loc_id THEN
670 v_ins_from_temp_flag := 1;
671 v_ins_from_curr_flag := 1;
672
673 ELSIF v_temp_loc_id = v_loc_id THEN
674 v_ins_from_temp_flag := 0;
675 v_ins_from_curr_flag := 0;
676 END IF;
677
678 ELSIF v_bonded_curr = 'Y' THEN
679 v_ins_from_temp_flag := 1;
680 v_ins_from_curr_flag := 1;
681
682 ELSE --bonded_curr is 'N'
683 v_ins_from_temp_flag := 1;
684 v_ins_from_curr_flag := 0;
685 END IF;
686
687 ELSIF v_trading_curr = 'Y' THEN
688 IF v_bonded_temp = 'Y' THEN
689 v_ins_from_temp_flag := 1;
690 v_ins_from_curr_flag := 1;
691 ELSE -- bonded_temp is 'N' then temp table transaction should not hit RG
692 v_ins_from_temp_flag := 0;
693 v_ins_from_curr_flag := 1;
694 END IF;
695
696 ELSIF v_bonded_temp = 'Y' THEN -- temp transaction is bonded
697 IF v_bonded_curr = 'Y' THEN -- current transaction is bonded
698 IF v_temp_loc_id = v_loc_id THEN
699 v_ins_from_temp_flag := 0;
700 v_ins_from_curr_flag := 0;
701 ELSE -- IF v_temp_loc_id = v_loc_id THEN
702 v_ins_from_temp_flag := 1;
703 v_ins_from_curr_flag := 1;
704 END IF;
705 ELSE -- IF v_bonded_curr = 'N' THEN
706 v_ins_from_temp_flag := 1;
707 v_ins_from_curr_flag := 0;
708 END IF;
709
710 -- ELSIF v_bonded_temp = 'N' THEN
711 ELSE --if the execution comes here, then it means v_trading_temp = 'N', v_trading_curr = 'N', v_bonded_temp = 'N'
712 IF v_bonded_curr = 'Y' THEN
713 v_ins_from_temp_flag := 0; -- cbabu 25/07/02 for Bug#2480524 , when transaction is done from NB -> B Subinventory then RG is hitting twice which is wrong
714 --v_ins_from_temp_flag := 1; --added by sriram on 12-may-01 and commented by vijay shankar for -- cbabu 25/07/02 for Bug#2480524
715 v_ins_from_curr_flag := 1;
716 ELSE -- IF v_bonded_curr = 'N' THEN
717 v_ins_from_temp_flag := 0;
718 v_ins_from_curr_flag := 0;
719 END IF;
720 END IF;
721
722 WHILE(TRUE) LOOP --To handle both entries with single insert
723
724 -- If the temp transaction which is in temp table has to go into RG, then the following condition is TRUE
725 IF v_ins_from_temp_flag = 1 THEN --aaa
726
727 v_iss_qty := ABS (v_temp_trans_qty);
728 v_iss_id := v_temp_transaction_set_id;
729 v_iss_date := v_temp_trans_date;
730 v_rec_id := NULL;
731 v_rec_qty := NULL;
732 v_rec_date := NULL;
733
734 OPEN item_class_cur ( v_temp_inv_item_id, v_temp_organization_id );
735 FETCH item_class_cur INTO v_temp_excise_flag, v_temp_item_class, v_temp_modvat_flag, v_temp_item_trading_flag;
736 CLOSE item_class_cur;
737
738 OPEN range_division_cur ( v_temp_organization_id, v_temp_loc_id );
739 FETCH range_division_cur INTO v_temp_range_no, v_temp_division_no;
740 CLOSE range_division_cur;
741
742 OPEN get_pr_uom_cur ( v_temp_inv_item_id, v_temp_organization_id );
743 FETCH get_pr_uom_cur INTO v_temp_pr_uom_code;
744 CLOSE get_pr_uom_cur;
745
746 IF v_temp_trans_qty < 0 THEN
747 v_t_type := 'I';
748 ELSE
749 v_t_type := 'R';
750 END IF;
751
752 IF v_trading_temp = 'Y' THEN
753 IF NVL (v_temp_item_trading_flag, 'N') = 'Y' THEN
754 jai_cmn_rg_23d_trxs_pkg.make_entry (
755 pr_new.organization_id,
756 v_loc_id,
757 v_t_type,
758 pr_new.inventory_item_id,
759 pr_new.subinventory_code,
760 v_temp_pr_uom_code,
761 pr_new.transaction_uom,
762 v_rec_id,
763 v_rec_date,
764 v_rec_qty,
765 pr_new.transaction_type_id,
766 v_iss_id,
767 v_iss_date,
768 v_iss_qty,
769 pr_new.transaction_date,
770 pr_new.creation_date,
771 pr_new.created_by,
772 pr_new.last_update_date,
773 pr_new.last_update_login,
774 pr_new.last_updated_by
775 );
776 END IF;
777 ELSIF ( v_temp_excise_flag = 'Y'
778 AND v_temp_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX', 'CCIN', 'CCEX')
779 AND v_t_type = 'I'
780 )
781 OR ( v_temp_modvat_flag = 'Y'
782 AND v_temp_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX', 'CCIN', 'CCEX')
783 AND v_t_type = 'R'
784 )
785 THEN
786 IF ( v_temp_excise_flag = 'Y'
787 AND v_temp_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX')
788 AND v_t_type = 'I'
789 )
790 OR ( v_temp_modvat_flag = 'Y'
791 AND v_temp_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX')
792 AND v_t_type = 'R'
793 )
794 THEN
795 v_reg_type := 'A';
796 ELSIF ( v_temp_excise_flag = 'Y'
797 AND v_temp_item_class IN ('CGIN', 'CGEX')
798 AND v_t_type = 'I'
799 )
800 OR ( v_temp_modvat_flag = 'Y'
801 AND v_temp_item_class IN ('CGIN', 'CGEX')
802 AND v_t_type = 'R'
803 )
804 THEN
805 v_reg_type := 'C';
806 END IF;
807 /*Bug 9550254 - Start*/
808 /*
809 OPEN srno_i_cur ( v_temp_organization_id, v_temp_inv_item_id, v_temp_loc_id, v_reg_type, v_f_year );
810 FETCH srno_i_cur INTO v_srno;
811 CLOSE srno_i_cur;
812 */
813 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
814 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(v_temp_organization_id,v_temp_loc_id,v_temp_inv_item_id,v_f_year,v_reg_type,v_srno);
815 /*Bug 9550254 - End*/
816 IF NVL (v_srno, 0) = 0 THEN
817 v_srno := 1;
818 v_sr_no := 0;
819 ELSE
820 v_sr_no := v_srno;
821 v_srno := v_srno + 1;
822 END IF;
823
824 IF v_temp_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX') THEN
825
826 IF v_sr_no = 0 THEN
827 /*Bug 9550254 - Start*/
828 -- v_opening_qty := 0;
829 -- v_closing_qty := v_temp_trans_qty;
830 v_closing_qty := v_opening_qty + v_temp_trans_qty;
831 /*Bug 9550254 - End*/
832
833 ELSE
834 OPEN opening_balance_cur ( v_temp_organization_id, v_temp_inv_item_id, v_sr_no,
835 v_temp_loc_id, v_reg_type, v_f_year);
836 FETCH opening_balance_cur INTO v_op_qty, v_cl_qty;
837 CLOSE opening_balance_cur;
838
839 IF NVL (v_cl_qty, 0) <> 0 THEN
840 v_opening_qty := v_cl_qty;
841 v_closing_qty := v_cl_qty + v_temp_trans_qty;
842
843 ELSE
844 v_opening_qty := 0;
845 v_closing_qty := v_temp_trans_qty;
846 END IF;
847
848 END IF;
849 v_rg23_flag := 1;
850
851 ELSE -- IF v_temp_item_class in ('CCIN', 'CCEX')
852
853 --Start, cbabu for Bug# 2728521
854 IF v_sr_no = 0 THEN
855 IF v_temp_trans_qty < 0 THEN
856 v_iss_qty := null;
857 v_hit_rg1_qty := v_temp_trans_qty;
858 ELSE
859 /*Bug 9550254 - Start*/
860 --v_opening_qty := 0;
861 --v_closing_qty := v_temp_trans_qty;
862 v_closing_qty := v_opening_qty + v_temp_trans_qty;
863 /*Bug 9550254 - End*/
864 v_rg23_flag := 1;
865 END IF;
866
867 ELSE
868
869 OPEN opening_balance_cur ( v_temp_organization_id, v_temp_inv_item_id, v_sr_no,
870 v_temp_loc_id, v_reg_type, v_f_year);
871 FETCH opening_balance_cur INTO v_op_qty, v_cl_qty;
872 CLOSE opening_balance_cur;
873
874 IF v_cl_qty IS NOT NULL THEN -- NVL (v_cl_qty, 0) != 0 THEN
875 v_closing_qty := v_cl_qty + v_temp_trans_qty;
876
877 --Start, cbabu for Bug# 2728521
878 IF v_cl_qty <= 0 THEN
879 v_iss_qty := null;
880 v_hit_rg1_qty := v_temp_trans_qty;
881
882 ELSIF v_closing_qty < 0 THEN
883 v_hit_rg1_qty := v_closing_qty;
884
885 v_iss_qty := v_cl_qty;
886 v_closing_qty := 0;
887
888 v_rg23_flag := 1;
889
890 ELSE -- v_closing_qty > 0
891 -- Enough closing balance quantity is there, so hit RG23 and done hit RG1
892 v_rg23_flag := 1;
893 END IF;
894 --End, cbabu for Bug# 2728521
895
896 v_opening_qty := v_cl_qty;
897
898 ELSE
899
900 v_iss_qty := null;
901 v_hit_rg1_qty := v_temp_trans_qty;
902 -- v_opening_qty := 0; -- cbabu for Bug# 2728521
903 -- v_closing_qty := v_temp_trans_qty; -- cbabu for Bug# 2728521
904 END IF;
905
906
907 END IF;
908 END IF;
909
910 -- v_rg23_flag := 1; -- cbabu for Bug# 2728521
911
912 END IF; ------ADDED BY SRIRAM ON 18-APR-2001
913
914 --The changes are made to solve the problem of CCIN items are not
915 --appearing in the RG1 register when an Sub-inventory transfer is made.
916
917 IF ( v_temp_excise_flag = 'Y'
918 AND v_item_class IN ('FGIN', 'FGEX', 'CCIN'
919 , 'CCEX' ) -- cbabu for Bug# 2728521
920 AND v_t_type = 'I'
921 ) ---ADDED BY SRIRAM ON 18-APR-2001
922 OR ( v_temp_modvat_flag = 'Y'
923 AND v_item_class IN ('FGIN', 'FGEX', 'CCIN'
924 , 'CCEX' ) -- cbabu for Bug# 2728521
925 AND v_t_type = 'R'
926 )
927 THEN
928
929 -- cbabu for Bug# 2728521
930 IF v_item_class IN ('CCIN', 'CCEX') AND v_hit_rg1_qty IS NOT NULL THEN
931 v_temp_trans_qty := v_hit_rg1_qty;
932 ELSIF v_item_class IN ('CCIN', 'CCEX') THEN
933 GOTO skip_rg1_hit;
934 END IF;
935
936 IF v_t_type = 'I' THEN
937 v_trans_qty := v_temp_trans_qty;
938 ELSIF v_t_type = 'R' THEN
939 v_trans_qty := NULL;
940 END IF;
941 /*Bug 9550254 - Start*/
942 /*
943 -- OPEN srno_ii_cur (v_loc_id, v_f_year); -- commented by cbabu for Bug#2541366
944 OPEN srno_ii_cur (v_temp_loc_id, v_f_year); -- cbabu for Bug#2541366
945 --FETCH SRNO_II_CUR INTO v_srno,v_bal_packed,v_bal_loose; --commented by --sriram on 18-may-01
946 FETCH srno_ii_cur INTO v_srno1, v_bal_packed, v_bal_loose;
947 CLOSE srno_ii_cur;
948 */
949 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
950 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_temp_loc_id,pr_new.inventory_item_id,v_f_year,
951 v_srno1,v_bal_packed);
952 /*Bug 9550254 - End*/
953
954 --Start adding by Sriram on 18-may-01
955 IF NVL (v_srno1, 0) = 0 THEN
956 v_srno1 := 1;
957 v_sr_no1 := 0;
958
959 ELSE
960 v_sr_no1 := v_srno1;
961 v_srno1 := v_srno1 + 1;
962 END IF;
963
964 --end adding by Sriram on 18-may-01
965 v_manu_qty := 0;
966 v_manu_pkd_qty := NULL;
967 v_manu_loose_qty := NULL;
968
969 -- v_manu_qty := v_temp_trans_qty; --commented by sriram on 11-may-01
970 v_manu_pkd_qty := 0; --added by sriram on 11-may-01
971
972 /**start added by sriram on 15-may-01**/
973 IF v_t_type = 'R' THEN
974 v_manu_qty := v_temp_trans_qty; --added by sriram on 23-may-01
975 v_manu_loose_qty := ABS (v_temp_trans_qty); --added by sriram on 11-may-01
976 ELSIF v_t_type = 'I' THEN
977 v_manu_qty := 0; --added by sriram on 23-may-01
978 v_manu_loose_qty := 0;
979 END IF;
980
981 /**end added by sriram on 15-may-01**/
982 IF ( NVL (v_bal_packed, 0)
983 + NVL (v_bal_loose, 0)
984 ) >= ABS (v_temp_trans_qty)
985 THEN
986 IF NVL (v_bal_loose, 0) >= ABS (v_temp_trans_qty) THEN
987 v_bal_loose := NVL (v_bal_loose, 0)
988 - ABS (NVL (v_temp_trans_qty, 0));
989 ELSE
990 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_temp_trans_qty, 0));
991 v_bal_packed := NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0);
992 v_bal_loose := 0;
993 END IF;
994 ELSE
995 v_bal_packed := NVL (v_bal_packed, 0) - ABS (NVL (v_temp_trans_qty, 0));
996 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_bal_packed, 0);
997 v_bal_packed := 0;
998 END IF;
999
1000 v_rg_flag := 1;
1001
1002 <<skip_rg1_hit>>
1003 null;
1004 END IF; ---ADDED BY SRIRAM ON 18-APR-2001
1005
1006 ---END IF; ---COMMENTED BY SRIRAM ON 18-APR-2001
1007 v_ins_from_temp_flag := 0;
1008 v_insert_flag := 1;
1009 END IF; --aaa
1010
1011 IF v_insert_flag = 1 THEN --bbb
1012 IF v_rg23_flag = 1 THEN
1013 /*bug 9122545*/
1014 OPEN loc_id_cur(pr_new.subinventory_code, pr_new.organization_id);
1015 FETCH loc_id_cur INTO v_loc_id;
1016 CLOSE loc_id_cur;
1017
1018 IF v_loc_id IS NULL THEN
1019 v_loc_id := 0;
1020 END IF;
1021
1022 OPEN c_org_addl_rg_flag(pr_new.organization_id, v_loc_id) ;
1023 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag ;
1024 CLOSE c_org_addl_rg_flag ;
1025
1026 IF lv_allow_negative_rg_flag = 'N'
1027 THEN
1028 if v_closing_qty < 0 then
1029 APP_EXCEPTION.RAISE_EXCEPTION( 'JA', -20109, 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||v_reg_type||','||v_temp_organization_id||','||v_temp_loc_id);
1030 end if;
1031 END IF;
1032 /*end bug 9122545*/
1033 --added rounding precision of 5 fro bug 9466919
1034 INSERT INTO JAI_CMN_RG_23AC_I_TRXS(
1035 register_id, fin_year, slno, last_update_date, last_updated_by,
1036 creation_date, created_by, last_update_login, TRANSACTION_SOURCE_NUM, inventory_item_id,
1037 organization_id, transaction_type, range_no, division_no, GOODS_ISSUE_ID_REF,
1038 goods_issue_date, goods_issue_quantity, OTH_RECEIPT_ID_REF, oth_receipt_date, oth_receipt_quantity,
1039 register_type, location_id, transaction_uom_code, transaction_date,
1040 opening_balance_qty, closing_balance_qty, primary_uom_code
1041 ) VALUES (
1042 JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno, pr_new.last_update_date, pr_new.last_updated_by,
1043 SYSDATE, pr_new.created_by, pr_new.last_update_login, v_temp_trans_type_id, v_temp_inv_item_id,
1044 v_temp_organization_id, v_t_type, v_temp_range_no, v_temp_division_no, v_iss_id,
1045 v_iss_date, round(v_iss_qty,5), v_rec_id, v_rec_date, round(v_rec_qty,5),
1046 v_reg_type, v_temp_loc_id, v_temp_trans_uom, TRUNC (v_temp_trans_date),
1047 round(v_opening_qty,5), round(v_closing_qty,5), v_temp_pr_uom_code
1048 );
1049
1050 v_rg23_flag := 0;
1051 END IF;
1052
1053 IF v_rg_flag = 1 THEN
1054 -- -- jai_cmn_utils_pkg.print_log('rg1.log','before 1');
1055 --added rounding precision of 5 for bug 9466919
1056 INSERT INTO JAI_CMN_RG_I_TRXS(
1057 register_id, fin_year, slno, manufactured_qty, manufactured_packed_qty,
1058 manufactured_loose_qty, balance_packed, balance_loose,
1059 to_other_factory_n_pay_ed_qty,
1060 for_export_n_pay_ed_qty, other_purpose_n_pay_ed_qty, last_update_date, last_updated_by,
1061 creation_date, created_by, last_update_login, TRANSACTION_SOURCE_NUM,
1062 inventory_item_id, organization_id, transaction_type, range_no,
1063 division_no, location_id, transaction_uom_code, transaction_date,
1064 primary_uom_code, REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1065 ) VALUES (
1066 JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno1,round( ABS(v_manu_qty),5),
1067 round(ABS(v_manu_pkd_qty),5), round(ABS(v_manu_loose_qty),5),round( v_bal_packed,5), round(v_bal_loose,5), --abs(v_trans_qty), commented by Sriram on 26-Dec-2001
1068 NULL, -- abs(v_t_qty), commented by Sriram on 26-Dec-2001
1069 NULL, round(ABS(v_trans_qty),5), pr_new.last_update_date, pr_new.last_updated_by,
1070 SYSDATE, pr_new.created_by, pr_new.last_update_login, v_temp_trans_type_id,
1071 v_temp_inv_item_id, v_temp_organization_id, v_t_type, v_temp_range_no,
1072 v_temp_division_no, v_temp_loc_id, v_temp_trans_uom, TRUNC(v_temp_trans_date),
1073 v_pr_uom_code, v_temp_transaction_id -- cbabu 25/07/02 for Bug#2480524
1074 );
1075
1076 v_rg_flag := 0;
1077 END IF;
1078
1079 v_insert_flag := 0;
1080 END IF; --bbb
1081
1082 v_hit_rg1_qty := null; -- cbabu for Bug# 2728521
1083
1084 IF v_ins_from_curr_flag = 1 THEN --ccc
1085
1086 -- Re - initialization for second Entry i.e current record for which the trigger is fired
1087 v_reg_type := NULL;
1088 v_srno := NULL;
1089 v_sr_no := NULL;
1090 v_opening_qty := NULL;
1091 v_closing_qty := NULL;
1092 v_op_qty := NULL;
1093 v_cl_qty := NULL;
1094 v_t_type := NULL;
1095 v_iss_qty := NULL;
1096 v_iss_id := NULL;
1097 v_iss_date := NULL;
1098 v_rec_id := pr_new.transaction_id;
1099 v_rec_qty := v_new_trans_qty;
1100 v_rec_date := pr_new.transaction_date;
1101 v_temp_trans_type_id := pr_new.transaction_type_id;
1102 v_temp_inv_item_id := pr_new.inventory_item_id;
1103 v_temp_organization_id := pr_new.organization_id;
1104 v_temp_range_no := v_range_no;
1105 v_temp_division_no := v_division_no;
1106 v_temp_loc_id := v_loc_id;
1107 v_temp_trans_uom := pr_new.transaction_uom;
1108 v_temp_trans_date := pr_new.transaction_date;
1109 v_temp_trans_qty := v_new_trans_qty;
1110
1111 v_temp_transaction_id := pr_new.transaction_id; -- cbabu 25/07/02 for Bug#2480524
1112
1113 v_bal_packed := 0;
1114 v_bal_loose := 0;
1115 v_temp_pr_uom_code := v_pr_uom_code;
1116 v_manu_qty := v_new_trans_qty;
1117
1118 OPEN range_division_cur ( v_temp_organization_id, v_temp_loc_id );
1119 FETCH range_division_cur INTO v_temp_range_no, v_temp_division_no;
1120 CLOSE range_division_cur;
1121
1122 IF v_new_trans_qty < 0 THEN
1123 v_t_type := 'I';
1124 ELSE
1125 v_t_type := 'R';
1126 END IF;
1127
1128 IF NVL (v_trading_temp, 'N') = 'Y' THEN
1129 IF NVL (v_item_trading_flag, 'N') = 'Y' THEN
1130 --cal proc for RG23D entry
1131 jai_cmn_rg_23d_trxs_pkg.make_entry (
1132 pr_new.organization_id,
1133 v_loc_id,
1134 v_t_type,
1135 pr_new.inventory_item_id,
1136 pr_new.subinventory_code,
1137 v_pr_uom_code,
1138 pr_new.transaction_uom,
1139 v_rec_id,
1140 v_rec_date,
1141 v_rec_qty,
1142 pr_new.transaction_type_id,
1143 v_iss_id,
1144 v_iss_date,
1145 v_iss_qty,
1146 pr_new.transaction_date,
1147 pr_new.creation_date,
1148 pr_new.created_by,
1149 pr_new.last_update_date,
1150 pr_new.last_update_login,
1151 pr_new.last_updated_by
1152 );
1153 END IF;
1154 ELSIF (v_excise_flag = 'Y' AND v_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX', 'CCIN', 'CCEX')
1155 AND v_t_type = 'I')
1156 -- OR ( v_modvat_flag = 'Y' AND v_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX', 'CCIN', 'CCEX') -- cbabu for Bug# 2728521
1157 OR ( v_modvat_flag = 'Y' AND v_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX') -- cbabu for Bug# 2728521
1158 AND v_t_type = 'R' )
1159 THEN
1160 IF (v_excise_flag = 'Y' AND v_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX') AND v_t_type = 'I')
1161 -- OR (v_modvat_flag = 'Y' AND v_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX') AND v_t_type = 'R' ) -- cbabu for Bug# 2728521
1162 OR (v_modvat_flag = 'Y' AND v_item_class IN ('RMIN', 'RMEX') AND v_t_type = 'R' ) -- cbabu for Bug# 2728521
1163 THEN
1164 v_reg_type := 'A';
1165 ELSIF ( v_excise_flag = 'Y' AND v_item_class IN ('CGIN', 'CGEX') AND v_t_type = 'I' )
1166 OR (v_modvat_flag = 'Y' AND v_item_class IN ('CGIN', 'CGEX') AND v_t_type = 'R' )
1167 THEN
1168 v_reg_type := 'C';
1169 END IF;
1170
1171 /*Bug 9550254 - Start*/
1172 /*
1173 OPEN srno_i_cur ( pr_new.organization_id, pr_new.inventory_item_id, v_loc_id, v_reg_type, v_f_year );
1174 FETCH srno_i_cur INTO v_srno;
1175 CLOSE srno_i_cur;
1176 */
1177 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
1178 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,
1179 v_f_year,v_reg_type,v_srno);
1180 /*Bug 9550254 - End*/
1181
1182 IF NVL (v_srno, 0) = 0
1183 THEN
1184 v_srno := 1;
1185 v_sr_no := 0;
1186 ELSE
1187 v_sr_no := v_srno;
1188 v_srno := v_srno + 1;
1189 END IF;
1190
1191 IF v_sr_no = 0 THEN
1192 /*Bug 9550254 - Start*/
1193 --v_opening_qty := 0;
1194 --v_closing_qty := v_new_trans_qty;
1195 v_closing_qty := v_new_trans_qty + v_opening_qty;
1196 /*Bug 9550254 - End*/
1197 ELSE
1198 OPEN opening_balance_cur ( pr_new.organization_id, pr_new.inventory_item_id, v_sr_no, v_loc_id,
1199 v_reg_type, v_f_year );
1200 FETCH opening_balance_cur INTO v_op_qty, v_cl_qty;
1201 CLOSE opening_balance_cur;
1202
1203 IF NVL (v_cl_qty, 0) <> 0 THEN
1204 v_opening_qty := v_cl_qty;
1205 v_closing_qty := v_cl_qty + v_new_trans_qty;
1206 ELSE
1207 v_opening_qty := 0;
1208 v_closing_qty := v_new_trans_qty;
1209 END IF;
1210 END IF;
1211
1212 v_rg23_flag := 1;
1213
1214 ELSIF ( v_excise_flag = 'Y'
1215 AND v_item_class IN ('FGIN', 'FGEX')
1216 AND v_t_type = 'I'
1217 )
1218 OR ( v_modvat_flag = 'Y'
1219 -- AND v_item_class IN ('FGIN', 'FGEX') -- cbabu for Bug# 2728521
1220 AND v_item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX') -- cbabu for Bug# 2728521
1221 AND v_t_type = 'R'
1222 )
1223 THEN
1224 -- IF v_item_class IN ('FGIN') THEN -- cbabu for Bug# 2728521
1225 IF v_item_class IN ('FGIN', 'CCIN') THEN -- cbabu for Bug# 2728521
1226 v_trans_qty := NULL;
1227 -- ELSIF v_item_class IN ('FGEX', 'CCEX') THEN -- cbabu for Bug# 2728521
1228 ELSIF v_item_class IN ('FGEX', 'CCEX') THEN -- cbabu for Bug# 2728521
1229 v_t_qty := NULL;
1230 END IF;
1231
1232 /*Bug 9550254 - Start*/
1233 /*
1234 OPEN srno_ii_cur (v_loc_id, v_f_year);
1235 -- FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
1236 FETCH srno_ii_cur INTO v_srno1, v_bal_packed, v_bal_loose;
1237 CLOSE srno_ii_cur;
1238 */
1239 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
1240 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
1241 v_srno1,v_bal_packed);
1242 /*Bug 9550254 - End*/
1243
1244 /*
1245 IF NVL (v_srno, 0) = 0 THEN
1246 v_srno := 1;
1247 v_sr_no := 0;
1248 v_bal_packed := 0;
1249 v_bal_loose := 0;
1250 ELSE
1251 v_sr_no := v_srno;
1252 v_srno := v_srno + 1;
1253 END IF;
1254 */
1255 IF NVL (v_srno1, 0) = 0 THEN
1256 v_srno1 := 1;
1257 v_sr_no := 0;
1258 /*Commented for Bug 9550254 as it is already calculated by jai_om_rg_pkg.ja_in_rgi_balance above*/
1259 -- v_bal_packed := 0;
1260 -- v_bal_loose := 0;
1261 ELSE
1262 v_sr_no := v_srno1;
1263 v_srno1 := v_srno1 + 1;
1264 END IF;
1265
1266 v_manu_qty := v_new_trans_qty;
1267 v_manu_pkd_qty := 0;
1268 v_manu_loose_qty := v_new_trans_qty;
1269
1270 -- v_bal_packed := NVL(v_bal_packed,0) + NVL(v_manu_pkd_qty,0);
1271 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_new_trans_qty, 0);
1272 v_rg_flag := 1;
1273 END IF;
1274
1275 v_ins_from_curr_flag := 0;
1276 v_insert_flag := 1;
1277
1278 END IF; --ccc
1279
1280 IF v_ins_from_temp_flag = 0 AND v_ins_from_curr_flag = 0 AND v_insert_flag = 0 THEN
1281 DELETE FROM JAI_CMN_TRANSACTIONS_T WHERE transaction_set_id = pr_new.transaction_set_id;
1282 EXIT; /* Uncommented by ssumaith - bug# 4130515*/
1283 END IF;
1284 END LOOP;
1285 END IF;
1286 RETURN; -- cbabu 25/07/02 for Bug#2480524, required processing is done. so simply return
1287 END IF;
1288
1289 --- 1st and 2nd case starts here
1290 /* 1. Misc Issue(RG Update).
1291 2. Misc Receipt(RG Update).
1292 3. Move Order Issue (added for Bug # 2851028)
1293 */
1294
1295 -- cbabu 25/07/02 for Bug#2480524, start
1296 /*Commented for bug #12615773 Start */
1297 /*OPEN c_txn_type_id('Miscellaneous Issue(RG Update)');
1298 FETCH c_txn_type_id INTO v_misc_issue_rg_update;
1299 CLOSE c_txn_type_id;
1300
1301 OPEN c_txn_type_id('Miscellaneous Recpt(RG Update)');
1302 FETCH c_txn_type_id INTO v_misc_recpt_rg_update;
1303 CLOSE c_txn_type_id;
1304 -- cbabu 25/07/02 for Bug#2480524, end
1305
1306 --following cursor added for PROJECTS COSTING IMPL Bug#6012567(5765161)
1307 OPEN c_projects_flag(pr_new.transaction_type_id);
1308 FETCH c_projects_flag INTO ln_projects_flag;
1309 CLOSE c_projects_flag;
1310 /*Commented for bug #12615773 End */
1311
1312
1313 IF ( ( pr_new.transaction_action_id = 1
1314 AND pr_new.transaction_source_type_id = 13
1315 --AND pr_new.transaction_type_id = 93 -- cbabu 25/07/02 for Bug#2480524
1316 /*Commented for bug #12615773 Start */
1317 /*AND (pr_new.transaction_type_id = v_misc_issue_rg_update -- cbabu 25/07/02 for Bug#2480524
1318 OR ln_projects_flag = 1 --condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)
1319 ) */
1320 /*Commented for bug #12615773 End */
1321 )
1322 OR ( pr_new.transaction_action_id = 27
1323 AND pr_new.transaction_source_type_id = 13
1324 --AND pr_new.transaction_type_id = 94 -- cbabu 25/07/02 for Bug#2480524
1325 /*Commented for bug #12615773 Start */
1326 /*AND (pr_new.transaction_type_id = v_misc_recpt_rg_update -- cbabu 25/07/02 for Bug#2480524
1327 OR ln_projects_flag = 1 --condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)
1328 )*/
1329 /*Commented for bug #12615773 End */
1330 )
1331 -- cbabu for Bug# 2851028, to handle Move Order Issue transaction
1332 OR ( pr_new.transaction_action_id = 1
1333 AND pr_new.transaction_source_type_id = 4
1334 AND pr_new.transaction_type_id = 63
1335 )
1336 )
1337 THEN
1338 IF v_bonded_curr = 'N' AND v_trading_curr = 'N' THEN
1339 RETURN;
1340 END IF;
1341
1342 IF ( pr_new.transaction_action_id = 1
1343 AND pr_new.transaction_source_type_id = 13
1344 --AND pr_new.transaction_type_id = 93 -- cbabu 25/07/02 for Bug#2480524
1345 /*Commented for bug #12615773 Start */
1346 /*AND (pr_new.transaction_type_id = v_misc_issue_rg_update -- cbabu 25/07/02 for Bug#2480524
1347 OR ln_projects_flag = 1 --condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)
1348 )*/
1349 /*Commented for bug #12615773 End */
1350 )
1351 -- cbabu for Bug# 2851028, to handle Move Order Issue transaction
1352 OR ( pr_new.transaction_action_id = 1
1353 AND pr_new.transaction_source_type_id = 4
1354 AND pr_new.transaction_type_id = 63
1355 )
1356 THEN
1357 v_t_type := 'I';
1358 v_iss_qty := ABS (v_new_trans_qty);
1359 v_iss_id := pr_new.transaction_id;
1360 v_iss_date := pr_new.transaction_date;
1361 v_rec_id := NULL;
1362 v_rec_qty := NULL;
1363 v_rec_date := NULL;
1364 ELSIF pr_new.transaction_action_id = 27
1365 AND pr_new.transaction_source_type_id = 13
1366 --AND pr_new.transaction_type_id = 94 -- cbabu 25/07/02 for Bug#2480524
1367 /*Commented for bug #12615773 Start */
1368 /*AND (pr_new.transaction_type_id = v_misc_recpt_rg_update -- cbabu 25/07/02 for Bug#2480524
1369 OR ln_projects_flag = 1 --condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)
1370 )*/
1371 /*Commented for bug #12615773 End */
1372 THEN
1373 v_t_type := 'R';
1374 v_iss_qty := NULL;
1375 v_iss_id := NULL;
1376 v_iss_date := NULL;
1377 v_rec_id := pr_new.transaction_id;
1378 v_rec_qty := v_new_trans_qty;
1379 v_rec_date := pr_new.transaction_date;
1380 END IF;
1381
1382 IF NVL (v_trading_curr, 'N') = 'Y' THEN
1383 IF NVL (v_item_trading_flag, 'N') = 'Y' THEN
1384 --cal proc for RG23D entry
1385 jai_cmn_rg_23d_trxs_pkg.make_entry (
1386 pr_new.organization_id,
1387 v_loc_id,
1388 v_t_type,
1389 pr_new.inventory_item_id,
1390 pr_new.subinventory_code,
1391 v_pr_uom_code,
1392 pr_new.transaction_uom,
1393 v_rec_id,
1394 v_rec_date,
1395 v_rec_qty,
1396 pr_new.transaction_type_id,
1397 v_iss_id,
1398 v_iss_date,
1399 v_iss_qty,
1400 pr_new.transaction_date,
1401 pr_new.creation_date,
1402 pr_new.created_by,
1403 pr_new.last_update_date,
1404 pr_new.last_update_login,
1405 pr_new.last_updated_by
1406 );
1407 END IF;
1408 ELSE
1409 IF ( v_excise_flag = 'Y'
1410 AND v_item_class IN ('RMIN', 'RMEX')
1411 AND v_t_type = 'I'
1412 )
1413 OR ( v_modvat_flag = 'Y'
1414 AND v_item_class IN ('RMIN', 'RMEX')
1415 AND v_t_type = 'R'
1416 )
1417 THEN
1418 v_reg_type := 'A';
1419 ELSIF v_excise_flag = 'Y' AND v_item_class IN ('CGIN', 'CGEX') THEN
1420 v_reg_type := 'C';
1421 END IF;
1422
1423 IF ( v_excise_flag = 'Y'
1424 AND v_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX')
1425 AND v_t_type = 'I'
1426 )
1427 OR ( v_modvat_flag = 'Y'
1428 AND v_item_class IN ('CGIN', 'CGEX', 'RMIN', 'RMEX')
1429 AND v_t_type = 'R'
1430 )
1431 THEN
1432 /*Bug 9550254 - Start*/
1433 /*
1434 OPEN srno_i_cur(pr_new.organization_id, pr_new.inventory_item_id, v_loc_id, v_reg_type, v_f_year );
1435 FETCH srno_i_cur INTO v_srno;
1436 CLOSE srno_i_cur;
1437 */
1438 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
1439 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,
1440 v_f_year,v_reg_type,v_srno);
1441
1442 /*Bug 9550254 - End*/
1443
1444 IF NVL (v_srno, 0) = 0 THEN
1445 v_srno := 1;
1446 v_sr_no := 0;
1447 ELSE
1448 v_sr_no := v_srno;
1449 v_srno := v_srno + 1;
1450 END IF;
1451
1452 IF v_sr_no = 0 THEN
1453 /*Bug 9550254 - Start*/
1454 -- v_opening_qty := 0;
1455 -- v_closing_qty := v_new_trans_qty;
1456 v_closing_qty := v_opening_qty + v_new_trans_qty;
1457 /*Bug 9550254 - End*/
1458 ELSE
1459 OPEN opening_balance_cur ( pr_new.organization_id, pr_new.inventory_item_id, v_sr_no, v_loc_id,
1460 v_reg_type, v_f_year );
1461 FETCH opening_balance_cur INTO v_op_qty, v_cl_qty;
1462 CLOSE opening_balance_cur;
1463
1464 IF NVL (v_cl_qty, 0) <> 0 THEN
1465 v_opening_qty := v_cl_qty;
1466 v_closing_qty := v_cl_qty + v_new_trans_qty;
1467 ELSE
1468 v_opening_qty := 0;
1469 v_closing_qty := v_new_trans_qty;
1470 END IF;
1471 END IF;
1472
1473 /*Bug 9122545*/
1474 OPEN loc_id_cur (pr_new.subinventory_code, pr_new.organization_id);
1475 FETCH loc_id_cur INTO v_loc_id;
1476 CLOSE loc_id_cur;
1477
1478 IF v_loc_id IS NULL THEN
1479 v_loc_id := 0;
1480 END IF;
1481
1482 OPEN c_org_addl_rg_flag(pr_new.organization_id, v_loc_id) ;
1483 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag ;
1484 CLOSE c_org_addl_rg_flag ;
1485
1486 IF lv_allow_negative_rg_flag = 'N'
1487 THEN
1488 if v_closing_qty < 0 then
1489 APP_EXCEPTION.RAISE_EXCEPTION('JA', -20110, 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||v_reg_type||','||pr_new.organization_id||','||v_loc_id);
1490 end if;
1491 END IF;
1492 /*End bug 9122545*/
1493 INSERT INTO JAI_CMN_RG_23AC_I_TRXS
1494 (register_id, fin_year, slno,
1495 last_update_date, last_updated_by, creation_date,
1496 created_by, last_update_login,
1497 TRANSACTION_SOURCE_NUM, inventory_item_id,
1498 organization_id, transaction_type, range_no,
1499 division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
1500 goods_issue_quantity, OTH_RECEIPT_ID_REF,
1501 oth_receipt_date, oth_receipt_quantity,
1502 register_type, location_id, transaction_uom_code,
1503 transaction_date, opening_balance_qty,
1504 closing_balance_qty, primary_uom_code)
1505 VALUES (JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
1506 pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
1507 pr_new.created_by, pr_new.last_update_login,
1508 pr_new.transaction_type_id, pr_new.inventory_item_id,
1509 pr_new.organization_id, v_t_type, v_range_no,
1510 v_division_no, v_iss_id, v_iss_date,
1511 round(v_iss_qty,5), v_rec_id,
1512 v_rec_date, round(v_rec_qty,5),
1513 v_reg_type, v_loc_id, pr_new.transaction_uom,
1514 TRUNC (pr_new.transaction_date), round(v_opening_qty,5),
1515 round(v_closing_qty,5), v_pr_uom_code);
1516 END IF; /** ADDED BY SRIRAM ON 04-MAY-2001 **/
1517
1518
1519 /** ELSIF (v_excise_flag = 'Y' AND v_item_class IN ('FGIN','FGEX','CCIN') AND v_t_type = 'I')
1520 OR
1521 (v_modvat_flag = 'Y' AND v_item_class IN ('FGIN','FGEX','CCIN') AND v_t_type = 'R')
1522 */ -- END ADDITION BY SRIRAM ON 04-MAY-2001
1523
1524 --The above changes are made to resolve the problem of CCIN items
1525 --are not appearing in RG1 register when an miscelaneous issue or receipt is made
1526
1527 IF ( v_excise_flag = 'Y'
1528 --AND v_item_class IN ('FGIN', 'FGEX', 'CCIN') --Changed by Nagaraj.s for Bug#2649405
1529 --These lines are incorporated by Nagaraj.s for Bug#2649405
1530 AND v_item_class IN ('FGIN', 'FGEX')
1531 AND v_t_type = 'I'
1532 )
1533 OR ( v_modvat_flag = 'Y'
1534 --AND v_item_class IN ('FGIN', 'FGEX', 'CCIN') --Commented by Nagaraj.s for Bug#2649405
1535 AND v_item_class IN ('FGIN', 'FGEX', 'CCIN','CCEX') --Added by Nagaraj.s for Bug#2649405
1536 AND v_t_type = 'R'
1537 )
1538 THEN
1539
1540 IF v_item_class IN ('FGIN') THEN
1541
1542 IF v_t_type = 'I' THEN
1543 v_trans_qty := ABS (v_new_trans_qty);
1544 ELSIF v_t_type = 'R' THEN
1545 v_trans_qty := NULL;
1546 END IF;
1547
1548 ELSIF v_item_class IN ('FGEX') THEN
1549
1550 IF v_t_type = 'I' THEN
1551 v_t_qty := v_new_trans_qty;
1552 v_trans_qty := v_t_qty;
1553 ELSIF v_t_type = 'R' THEN
1554 v_t_qty := NULL;
1555 END IF;
1556
1557 ---start addition by Sriram on 04-may-2001
1558
1559 ELSIF v_item_class IN ('CCIN','CCEX') THEN
1560
1561 IF v_t_type = 'I' THEN
1562 v_mis_qty := v_new_trans_qty;
1563 v_trans_qty := v_mis_qty;
1564 ELSIF v_t_type = 'R' THEN
1565 v_mis_qty := NULL;
1566 END IF;
1567
1568 --- end addition by Sriram on 04-may-2001
1569
1570 END IF;
1571
1572 /*Bug 9550254 - Start*/
1573 /*
1574 OPEN srno_ii_cur (v_loc_id, v_f_year);
1575 FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
1576 CLOSE srno_ii_cur;
1577 */
1578 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
1579 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,v_srno,v_bal_packed);
1580 /*Bug 9550254 - Start*/
1581
1582 IF NVL (v_srno, 0) = 0 THEN
1583 v_srno := 1;
1584 v_sr_no := 0;
1585 ELSE
1586 v_sr_no := v_srno;
1587 v_srno := v_srno + 1;
1588 END IF;
1589
1590 IF v_t_type = 'I' THEN
1591
1592 /** v_manu_qty := NULL;
1593 v_manu_pkd_qty := NULL;
1594 v_manu_loose_qty := NULL;
1595 **/ --COMMENTED BY SRIRAM ON 07-MAY-2001
1596
1597 --ADDED BY SRIRAM ON 07-MAY-2001
1598
1599 -- v_manu_qty := v_new_trans_qty; commented by Sriram on 24-Dec-01
1600 v_manu_qty := 0; --add by Sriram on 24-Dec-01
1601 v_manu_pkd_qty := 0;
1602 v_manu_loose_qty := 0;
1603 --END ADDING BY SRIRAM ON 07-MAY-2001
1604
1605 IF ( NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0) ) >= ABS (v_new_trans_qty) THEN
1606 IF NVL (v_bal_loose, 0) > ABS (v_new_trans_qty) THEN
1607 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_new_trans_qty, 0));
1608
1609 ELSE
1610 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_new_trans_qty, 0));
1611 v_bal_packed := NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0);
1612 v_bal_loose := 0;
1613
1614 END IF;
1615
1616 ELSE
1617 v_bal_packed := NVL (v_bal_packed, 0) - ABS (NVL (v_new_trans_qty, 0));
1618 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_bal_packed, 0);
1619 v_bal_packed := 0;
1620 END IF;
1621
1622 ELSIF v_t_type = 'R' THEN
1623 v_manu_qty := v_new_trans_qty;
1624 v_manu_pkd_qty := 0;
1625 v_manu_loose_qty := v_new_trans_qty;
1626
1627 -- v_bal_packed := NVL(v_bal_packed,0) + NVL(v_manu_pkd_qty,0);
1628 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_new_trans_qty, 0);
1629 END IF;
1630 -- jai_cmn_utils_pkg.print_log('rg1.log','before 2');
1631 INSERT INTO JAI_CMN_RG_I_TRXS(
1632 register_id, fin_year, slno, manufactured_qty,
1633 manufactured_packed_qty, manufactured_loose_qty,
1634 balance_packed, balance_loose,
1635 to_other_factory_n_pay_ed_qty,
1636 for_export_n_pay_ed_qty, last_update_date,
1637 last_updated_by, creation_date, created_by,
1638 last_update_login, TRANSACTION_SOURCE_NUM,
1639 inventory_item_id, organization_id,
1640 transaction_type, range_no, division_no, location_id,
1641 transaction_uom_code, transaction_date,
1642 primary_uom_code,
1643 REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1644 ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, round(ABS(v_manu_qty),5),
1645 round(ABS(v_manu_pkd_qty),5), round(ABS(v_manu_loose_qty),5),
1646 round(v_bal_packed,5), round(v_bal_loose,5),
1647 round(ABS (v_trans_qty),5),
1648 round(v_t_qty,5), pr_new.last_update_date,
1649 pr_new.last_updated_by, SYSDATE, pr_new.created_by,
1650 pr_new.last_update_login, pr_new.transaction_type_id,
1651 pr_new.inventory_item_id, pr_new.organization_id,
1652 v_t_type, v_range_no, v_division_no, v_loc_id,
1653 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
1654 v_pr_uom_code,
1655 pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
1656 );
1657
1658 --This Whole Block is included by Nagaraj.s for Bug#2649405
1659
1660 ELSIF ( v_excise_flag = 'Y'
1661 AND v_item_class IN ('CCIN', 'CCEX')
1662 AND v_t_type = 'I'
1663 ) THEN
1664
1665 v_reg_type := 'A';
1666 -- This is assigned as 'A' as per the Functional Input of this bug.
1667 /*Bug 9550254 - Start*/
1668 /*
1669 OPEN srno_i_cur(pr_new.organization_id, pr_new.inventory_item_id, v_loc_id, v_reg_type, v_f_year );
1670 FETCH srno_i_cur INTO v_srno;
1671 CLOSE srno_i_cur;
1672 */
1673 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
1674 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,
1675 v_f_year,v_reg_type,v_srno);
1676 /*Bug 9550254 - End*/
1677 IF NVL (v_srno, 0) = 0 THEN
1678 v_srno := 1;
1679 v_sr_no := 0;
1680 ELSE
1681 v_sr_no := v_srno;
1682 v_srno := v_srno + 1;
1683 END IF;
1684
1685 IF v_sr_no = 0 THEN
1686 /*Bug 9550254 - Start*/
1687 -- v_opening_qty := 0;
1688 -- v_closing_qty := v_new_trans_qty;
1689 v_closing_qty := v_opening_qty;
1690 /*Bug 9550254 - End*/
1691 ELSE
1692 OPEN opening_balance_cur ( pr_new.organization_id, pr_new.inventory_item_id, v_sr_no, v_loc_id,
1693 v_reg_type, v_f_year );
1694 FETCH opening_balance_cur INTO v_opening_qty, v_closing_qty;
1695 CLOSE opening_balance_cur;
1696
1697 v_opening_qty := v_closing_qty;
1698 END IF;
1699
1700 IF v_closing_qty >0 then
1701 -- There exists some balance in RG23 part I A
1702
1703 IF v_closing_qty >= ABS(v_new_trans_qty) then
1704
1705 -- balance is enough in RG23 Part I A
1706 v_hit_rg23_qty := ABS(v_new_trans_qty);
1707 v_hit_rg1_qty := 0;
1708
1709 ELSIF v_closing_qty <ABS(v_new_trans_qty) then
1710 -- balance is not enough in RG23 Part I A, find how much can be hit from here and the rest
1711 -- from RG1. RG1 can go negative also(functional input for this bug) ,
1712 -- so no need to check balance there.
1713
1714 v_hit_rg23_qty := v_closing_qty;
1715 v_hit_rg1_qty := ABS(v_new_trans_qty) - v_hit_rg23_qty;
1716
1717 END IF;
1718
1719 ELSE
1720
1721 -- No balance in Rg23 Part 1 A, so the whole quantity to hit RG1.
1722 v_hit_rg23_qty :=0;
1723 v_hit_rg1_qty := ABS(v_new_trans_qty);
1724
1725 END IF;
1726
1727 IF v_hit_rg23_qty > 0 THEN
1728 /*bug 9122545*/
1729 OPEN loc_id_cur (pr_new.subinventory_code, pr_new.organization_id);
1730 FETCH loc_id_cur INTO v_loc_id;
1731 CLOSE loc_id_cur;
1732
1733 IF v_loc_id IS NULL THEN
1734 v_loc_id := 0;
1735 END IF;
1736
1737 OPEN c_org_addl_rg_flag(pr_new.organization_id, v_loc_id) ;
1738 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag ;
1739 CLOSE c_org_addl_rg_flag ;
1740
1741 IF lv_allow_negative_rg_flag = 'N'
1742 THEN
1743 if NVL(v_opening_qty,0) -NVL(v_hit_rg23_qty,0) < 0 then
1744 APP_EXCEPTION.RAISE_EXCEPTION('JA', -20111, 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||v_reg_type||','||pr_new.organization_id||','||v_loc_id);
1745 end if;
1746 END IF;
1747 /*end bug 9122545*/
1748 INSERT INTO JAI_CMN_RG_23AC_I_TRXS
1749 (register_id,
1750 fin_year,
1751 slno,
1752 last_update_date,
1753 last_updated_by,
1754 creation_date,
1755 created_by,
1756 last_update_login,
1757 TRANSACTION_SOURCE_NUM,
1758 inventory_item_id,
1759 organization_id,
1760 transaction_type, range_no,
1761 division_no,
1762 GOODS_ISSUE_ID_REF,
1763 goods_issue_date,
1764 goods_issue_quantity,
1765 OTH_RECEIPT_ID_REF,
1766 oth_receipt_date,
1767 oth_receipt_quantity,
1768 register_type,
1769 location_id,
1770 transaction_uom_code,
1771 transaction_date,
1772 opening_balance_qty,
1773 closing_balance_qty,
1774 primary_uom_code)
1775 VALUES (JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL,
1776 v_f_year,
1777 v_srno,
1778 pr_new.last_update_date,
1779 pr_new.last_updated_by,
1780 SYSDATE,
1781 pr_new.created_by,
1782 pr_new.last_update_login,
1783 pr_new.transaction_type_id,
1784 pr_new.inventory_item_id,
1785 pr_new.organization_id,
1786 v_t_type,
1787 v_range_no,
1788 v_division_no,
1789 v_iss_id,
1790 v_iss_date,
1791 round(v_hit_rg23_qty,5),
1792 v_rec_id,
1793 v_rec_date,
1794 0,
1795 v_reg_type,
1796 v_loc_id,
1797 pr_new.transaction_uom,
1798 TRUNC (pr_new.transaction_date),
1799 round( v_opening_qty,5),
1800 round( NVL(v_opening_qty,0) -NVL(v_hit_rg23_qty,0),5),
1801 v_pr_uom_code);
1802 END IF; -- End if for v_hit_rg23_qty
1803
1804
1805 IF v_hit_rg1_qty >0 THEN
1806 v_mis_qty := v_new_trans_qty;
1807 v_trans_qty := v_mis_qty;
1808 /*Bug 9550254 - Start*/
1809 /*
1810 OPEN srno_ii_cur (v_loc_id, v_f_year);
1811 FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
1812 CLOSE srno_ii_cur;
1813 */
1814 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
1815 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
1816 v_srno,v_bal_packed);
1817 /*Bug 9550254 - End*/
1818
1819 IF NVL (v_srno, 0) = 0 THEN
1820 v_srno := 1;
1821 v_sr_no := 0;
1822 ELSE
1823 v_sr_no := v_srno;
1824 v_srno := v_srno + 1;
1825 END IF;
1826 v_manu_qty := 0; --add by Sriram on 24-Dec-01
1827 v_manu_pkd_qty := 0;
1828 v_manu_loose_qty := 0;
1829
1830 IF ( NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0) ) >= ABS (v_hit_rg1_qty) THEN
1831 IF NVL (v_bal_loose, 0) > ABS (v_hit_rg1_qty) THEN
1832 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_hit_rg1_qty, 0));
1833
1834 ELSE
1835 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_hit_rg1_qty, 0));
1836 v_bal_packed := NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0);
1837 v_bal_loose := 0;
1838
1839 END IF;
1840
1841 ELSE
1842 v_bal_packed := NVL (v_bal_packed, 0) - ABS (NVL (v_hit_rg1_qty, 0));
1843 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_bal_packed, 0);
1844 v_bal_packed := 0;
1845 END IF;
1846
1847
1848 -- jai_cmn_utils_pkg.print_log('rg1.log','before 3');
1849 INSERT INTO JAI_CMN_RG_I_TRXS(
1850 register_id, fin_year, slno, manufactured_qty,
1851 manufactured_packed_qty, manufactured_loose_qty,
1852 balance_packed, balance_loose,
1853 --to_other_factory_n_pay_ed_qty, Commented by Nagaraj.s for Bug#2744695
1854 other_purpose_n_pay_ed_qty, --Added by Nagaraj.s for Bug#2744695
1855 for_export_n_pay_ed_qty, last_update_date,
1856 last_updated_by, creation_date, created_by,
1857 last_update_login, TRANSACTION_SOURCE_NUM,
1858 inventory_item_id, organization_id,
1859 transaction_type, range_no, division_no, location_id,
1860 transaction_uom_code, transaction_date,
1861 primary_uom_code,
1862 REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
1863 ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, round(ABS(v_manu_qty),5),
1864 round(ABS(v_manu_pkd_qty),5),round( ABS(v_manu_loose_qty),5),
1865 round(v_bal_packed,5), round(v_bal_loose,5),
1866 round(ABS (v_hit_rg1_qty),5),
1867 round(v_t_qty,5), pr_new.last_update_date,
1868 pr_new.last_updated_by, SYSDATE, pr_new.created_by,
1869 pr_new.last_update_login, pr_new.transaction_type_id,
1870 pr_new.inventory_item_id, pr_new.organization_id,
1871 v_t_type, v_range_no, v_division_no, v_loc_id,
1872 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
1873 v_pr_uom_code,
1874 pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
1875 );
1876 END IF; -- End if for v_hit_rg1_qty
1877 --Ends here by Nagaraj.s for Bug#2649405....
1878
1879 END IF;
1880
1881 END IF;
1882
1883 -- KKK
1884 ---4th, 5th, 6th, 7th, 8th, 9th,10th case starts here
1885 /*
1886 4. WIP component issue. : transaction_action_id -> 1, transaction_source_type_id -> 5, transaction_type_id -> 35
1887 5. WIP Component Return.
1888 6. WIP Assembly Completion.
1889 7. WIP Assembly Return.
1890 8. WIP Negative Component Issue.
1891 9. WIP Negative Component Return.
1892 10.WIP Scrap Transaction.
1893 */
1894 ELSIF ( ( pr_new.transaction_action_id = 1
1895 AND pr_new.transaction_source_type_id = 5
1896 )
1897 OR ( pr_new.transaction_action_id = 27
1898 AND pr_new.transaction_source_type_id = 5
1899 )
1900 OR ( pr_new.transaction_action_id = 31
1901 AND pr_new.transaction_source_type_id = 5
1902 )
1903 OR ( pr_new.transaction_action_id = 32
1904 AND pr_new.transaction_source_type_id = 5
1905 )
1906 OR ( pr_new.transaction_action_id = 33
1907 AND pr_new.transaction_source_type_id = 5
1908 )
1909 OR ( pr_new.transaction_action_id = 34
1910 AND pr_new.transaction_source_type_id = 5
1911 )
1912 OR ( pr_new.transaction_action_id = 30
1913 AND pr_new.transaction_source_type_id = 5
1914 )
1915 )
1916 THEN
1917
1918 IF v_trading_curr = 'Y' THEN
1919 /* raise_application_error (-20108, 'Subinventory Cannot be Trading');
1920 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Subinventory Cannot be Trading' ; return ;
1921 END IF;
1922
1923 IF v_bonded_curr = 'N' THEN
1924 RETURN;
1925 END IF;
1926
1927 IF ( ( pr_new.transaction_action_id = 1
1928 AND pr_new.transaction_source_type_id = 5
1929 )
1930
1931 --Commented by bgowrava for Forward porting bug#5275865
1932 /* OR ( pr_new.transaction_action_id = 32
1933 AND pr_new.transaction_source_type_id = 5
1934 ) */
1935 OR ( pr_new.transaction_action_id = 34
1936 AND pr_new.transaction_source_type_id = 5
1937 )
1938 )
1939 THEN
1940 v_t_type := 'PI';
1941 v_iss_qty := ABS (v_new_trans_qty);
1942 v_iss_id := pr_new.transaction_id;
1943 v_iss_date := pr_new.transaction_date;
1944 v_rec_id := NULL;
1945 v_rec_qty := NULL;
1946 v_rec_date := NULL;
1947
1948 ELSIF ( ( pr_new.transaction_action_id = 27
1949 AND pr_new.transaction_source_type_id = 5
1950 )
1951 OR ( pr_new.transaction_action_id = 31
1952 AND pr_new.transaction_source_type_id = 5
1953 )
1954 OR ( pr_new.transaction_action_id = 33
1955 AND pr_new.transaction_source_type_id = 5
1956 )
1957 OR ( pr_new.transaction_action_id = 30
1958 AND pr_new.transaction_source_type_id = 5
1959 )
1960 -- added by bgowrava for Forward porting bug#5275865
1961 OR ( pr_new.transaction_action_id = 32
1962 AND pr_new.transaction_source_type_id = 5
1963 )
1964 )
1965 THEN
1966
1967 /* redundant Check Commented by Vijay Shankar for Bug# 2915814Z
1968 IF ( ( pr_new.transaction_action_id = 27
1969 AND pr_new.transaction_source_type_id = 5
1970 )
1971 OR ( pr_new.transaction_action_id = 33
1972 AND pr_new.transaction_source_type_id = 5
1973 )
1974 OR ( pr_new.transaction_action_id = 31
1975 AND pr_new.transaction_source_type_id = 5
1976 )
1977 OR ( pr_new.transaction_action_id = 30
1978 AND pr_new.transaction_source_type_id = 5
1979 )
1980 )
1981 THEN
1982 v_t_type := 'PR';
1983
1984 -- commented on 12-feb-00 for wip assembly completion to be PR
1985 -- ELSIF pr_new.transaction_action_id = 31 and pr_new.transaction_source_type_id = 5
1986 -- THEN
1987 -- v_t_type := 'R';
1988 --
1989 END IF;
1990 */
1991 v_t_type := 'PR';
1992 v_iss_qty := NULL;
1993 v_iss_id := NULL;
1994 v_iss_date := NULL;
1995 v_rec_id := pr_new.transaction_id;
1996 v_rec_qty := v_new_trans_qty;
1997 v_rec_date := pr_new.transaction_date;
1998 END IF;
1999
2000 IF ( v_excise_flag = 'Y'
2001 AND v_item_class IN ('RMIN', 'RMEX', 'CGIN', 'CGEX')
2002 AND v_t_type = 'PI'
2003 )
2004 OR ( v_modvat_flag = 'Y'
2005 AND v_item_class IN ('RMIN', 'RMEX', 'CGIN', 'CGEX')
2006 AND v_t_type IN ('PR', 'R')
2007 )
2008 THEN
2009 IF v_item_class IN ('RMIN', 'RMEX') THEN
2010 v_reg_type := 'A';
2011
2012 ELSIF v_item_class IN ('CGIN', 'CGEX') THEN
2013 v_reg_type := 'C';
2014 END IF;
2015 /*Bug 9550254 - Start*/
2016 /*
2017 OPEN srno_i_cur ( pr_new.organization_id, pr_new.inventory_item_id, v_loc_id, v_reg_type, v_f_year );
2018 FETCH srno_i_cur INTO v_srno;
2019 CLOSE srno_i_cur;
2020 */
2021 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
2022 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,
2023 v_f_year,v_reg_type,v_srno);
2024 /*Bug 9550254 - End*/
2025 IF NVL (v_srno, 0) = 0 THEN
2026 v_srno := 1;
2027 v_sr_no := 0;
2028 ELSE
2029 v_sr_no := v_srno;
2030 v_srno := v_srno + 1;
2031 END IF;
2032
2033 IF v_sr_no = 0 THEN
2034 /*Bug 9550254 - Start*/
2035 -- v_opening_qty := 0;
2036 -- v_closing_qty := v_new_trans_qty;
2037 v_closing_qty := v_opening_qty + v_new_trans_qty;
2038 /*Bug 9550254 - End*/
2039 ELSE
2040 OPEN opening_balance_cur( pr_new.organization_id, pr_new.inventory_item_id, v_sr_no, v_loc_id, v_reg_type, v_f_year );
2041 FETCH opening_balance_cur INTO v_op_qty, v_cl_qty;
2042 CLOSE opening_balance_cur;
2043
2044 IF NVL (v_cl_qty, 0) <> 0 THEN
2045 v_opening_qty := v_cl_qty;
2046 v_closing_qty := v_cl_qty
2047 + v_new_trans_qty;
2048 ELSE
2049 v_opening_qty := 0;
2050 v_closing_qty := v_new_trans_qty;
2051 END IF;
2052 END IF;
2053
2054 /*bug 9122545*/
2055 OPEN loc_id_cur (pr_new.subinventory_code, pr_new.organization_id);
2056 FETCH loc_id_cur INTO v_loc_id;
2057 CLOSE loc_id_cur;
2058
2059 IF v_loc_id IS NULL THEN
2060 v_loc_id := 0;
2061 END IF;
2062
2063 OPEN c_org_addl_rg_flag(pr_new.organization_id, v_loc_id) ;
2064 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag ;
2065 CLOSE c_org_addl_rg_flag ;
2066
2067 IF lv_allow_negative_rg_flag = 'N'
2068 THEN
2069 if v_closing_qty < 0 then
2070 APP_EXCEPTION.RAISE_EXCEPTION('JA', -20112, 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||v_reg_type||','||pr_new.organization_id||','||v_loc_id);
2071 end if;
2072 END IF;
2073 /*end bug 9122545*/
2074 INSERT INTO JAI_CMN_RG_23AC_I_TRXS (
2075 register_id, fin_year, slno,
2076 last_update_date, last_updated_by, creation_date,
2077 created_by, last_update_login,
2078 TRANSACTION_SOURCE_NUM, inventory_item_id,
2079 organization_id, transaction_type, range_no,
2080 division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
2081 goods_issue_quantity, OTH_RECEIPT_ID_REF, oth_receipt_date,
2082 oth_receipt_quantity, register_type, location_id,
2083 transaction_uom_code, transaction_date,
2084 opening_balance_qty, closing_balance_qty,
2085 primary_uom_code
2086 ) VALUES (
2087 JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2088 pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
2089 pr_new.created_by, pr_new.last_update_login,
2090 pr_new.transaction_type_id, pr_new.inventory_item_id,
2091 pr_new.organization_id, v_t_type, v_range_no,
2092 v_division_no, v_iss_id, v_iss_date,
2093 round(v_iss_qty,5), v_rec_id, v_rec_date,
2094 round(v_rec_qty,5), v_reg_type, v_loc_id,
2095 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
2096 round(v_opening_qty,5),round( v_closing_qty,5),
2097 v_pr_uom_code
2098 );
2099
2100 ELSIF ( v_excise_flag = 'Y'
2101 -- AND v_item_class IN ('CCIN', 'CCEX', 'FGIN', 'FGEX') commented by Vijay Shankar for Bug# 2915814Z
2102 AND v_item_class IN ('FGIN', 'FGEX')
2103 AND v_t_type = 'PI'
2104 )
2105 OR ( v_modvat_flag = 'Y'
2106 AND v_item_class IN ('CCIN', 'CCEX', 'FGIN', 'FGEX')
2107 AND v_t_type IN ('PR', 'R')
2108 )
2109 THEN
2110 --This IF Clause is commented and replace by the One below
2111 --By Nagaraj.s for Bug#2744695.
2112 /*
2113 IF v_item_class IN ('FGIN', 'CCIN')
2114 THEN
2115 --v_trans_qty := v_new_trans_qty; --commented by Sriram on 23-may-01
2116
2117 v_trans_qty := NULL; --Start addition by Sriram on 23-may-01
2118 */
2119
2120 IF v_item_class IN ('FGIN') THEN
2121 V_TRANS_QTY := NULL;
2122
2123 ELSIF v_item_class IN ('CCIN') THEN
2124 V_TRANS_QTY := ABS(V_NEW_TRANS_QTY);
2125
2126 ELSIF v_item_class IN ('FGEX', 'CCEX') THEN
2127 /**v_t_qty := v_new_trans_qty;**/ --commented by sriram on 23-may-01
2128
2129 v_t_qty := NULL; --Start addition by Sriram on 23-may-01
2130 END IF;
2131 /*Bug 9550254 - Start*/
2132 /*
2133 OPEN srno_ii_cur (v_loc_id, v_f_year);
2134 FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
2135 CLOSE srno_ii_cur;
2136 */
2137 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2138 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
2139 v_srno,v_bal_packed);
2140
2141 /*Bug 9550254 - End*/
2142
2143 IF NVL (v_srno, 0) = 0 THEN
2144 v_srno := 1;
2145 v_sr_no := 0;
2146 /*Commented for Bug 9550254 as they are calculated via jai_om_rg_pkg.ja_in_rgi_balance above*/
2147 --v_bal_packed := 0;
2148 --v_bal_loose := 0;
2149 ELSE
2150 v_sr_no := v_srno;
2151 v_srno := v_srno + 1;
2152 END IF;
2153
2154 IF v_new_trans_qty < 0 THEN
2155 v_manufactured_qty := 0;
2156 v_manufactured_pack_qty := NULL;
2157 v_manu_loose_qty := NULL;
2158 OPEN fetch_type_name (pr_new.transaction_type_id);
2159 FETCH fetch_type_name INTO v_oth_purpose;
2160 CLOSE fetch_type_name;
2161
2162 -- v_oth_pur_npe_qty := abs(v_new_trans_qty); commented by Sriram on 22-may-01
2163
2164 /** Added by Sriram on 15-OCT-01 **/
2165 v_manu_qty := 0; --add by Sriram on 23-dec-01
2166 -- v_manu_qty := v_new_trans_qty;
2167 v_manu_pkd_qty := 0;
2168 v_manu_loose_qty := ABS (v_new_trans_qty);
2169
2170 /** End addition by Sriram on 15-OCT-01 **/
2171
2172 /* Added by Satya on 23-Oct-01 */
2173 IF ( pr_new.transaction_action_id = 32 AND pr_new.transaction_source_type_id = 5 ) THEN
2174 v_manu_loose_qty := v_new_trans_qty;
2175 v_manu_home_qty := NULL;
2176 v_manu_pkd_qty := 0;
2177 v_manufactured_qty := v_new_trans_qty; --adde by Sriram on 26-Dec-2001
2178 END IF;
2179
2180 IF ( NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0) ) >= ABS (v_new_trans_qty) THEN
2181 IF NVL (v_bal_loose, 0) >= ABS (v_new_trans_qty) THEN
2182 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_new_trans_qty, 0));
2183
2184 ELSE
2185 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_new_trans_qty, 0));
2186 v_bal_packed := NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0);
2187 END IF;
2188
2189 ELSE
2190 v_bal_packed := NVL (v_bal_packed, 0) - ABS (NVL (v_new_trans_qty, 0));
2191 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_bal_packed, 0);
2192 v_bal_packed := 0;
2193 END IF;
2194
2195 /* End of Addition by Satya on 23-Oct-01 */
2196
2197 ELSE
2198 v_manufactured_qty := v_new_trans_qty;
2199 v_manufactured_pack_qty := 0;
2200 v_manu_loose_qty := v_manufactured_qty;
2201 v_bal_packed :=
2202 NVL (v_bal_packed, 0)
2203 + NVL (v_manufactured_pack_qty, 0);
2204 v_bal_loose := NVL (v_bal_loose, 0)
2205 + NVL (v_manu_loose_qty, 0);
2206 v_oth_pur_npe_qty := 0;
2207
2208 /** Added by Sriram on 15-OCT-01 **/
2209 v_manu_qty := v_new_trans_qty;
2210 v_manu_pkd_qty := 0;
2211 v_manu_loose_qty := v_new_trans_qty;
2212
2213 /** End addition by Sriram on 15-OCT-01 **/
2214
2215 END IF;
2216
2217 -- start added by bug#3258066
2218 if v_t_type IN ('PR') then
2219 v_manu_loose_qty := v_new_trans_qty;
2220 v_other_npe_qty := Null;
2221 elsif v_t_type IN ('PI') then
2222 v_other_npe_qty := v_manu_loose_qty;
2223 v_manu_loose_qty:= Null;
2224 else
2225 v_other_npe_qty := v_trans_qty;
2226 end if;
2227 -- End added by bug#3258066
2228
2229 -- jai_cmn_utils_pkg.print_log('rg1.log','before 4');
2230
2231 INSERT INTO JAI_CMN_RG_I_TRXS (
2232 register_id, fin_year, slno,
2233 manufactured_qty, manufactured_packed_qty,
2234 manufactured_loose_qty, balance_packed, balance_loose,
2235 -- other_purpose_n_pay_ed_qty, -- by sriram
2236 to_other_factory_n_pay_ed_qty, other_purpose,
2237 -- to_other_factory_n_pay_ed_qty, --bby sriram
2238 other_purpose_n_pay_ed_qty, for_export_n_pay_ed_qty,
2239 for_home_use_pay_ed_qty, --Added by Satya on 23-Oct-01
2240 last_update_date,
2241 last_updated_by, creation_date, created_by,
2242 last_update_login, TRANSACTION_SOURCE_NUM,
2243 inventory_item_id, organization_id, transaction_type,
2244 range_no, division_no, location_id,
2245 transaction_uom_code, transaction_date,
2246 primary_uom_code,
2247 REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2248 ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2249 round(v_manufactured_qty,5), round(v_manufactured_pack_qty,5),
2250 round(v_manu_loose_qty,5), round(v_bal_packed,5), round(v_bal_loose,5), -- v_manu_loose_qty changed to null by sriram bug # 3258086on product management advice.
2251 round(v_oth_pur_npe_qty,5), v_oth_purpose,
2252 round(v_other_npe_qty,5), NULL, --earlier 'PI' corresponding field is number -- put v_manu_loose_qty instead of v_trans_qty - sriram
2253 round(v_manu_home_qty,5), --Added by Satya on 23-Oct-01
2254 pr_new.last_update_date,
2255 pr_new.last_updated_by, SYSDATE, pr_new.created_by,
2256 pr_new.last_update_login, pr_new.transaction_type_id,
2257 pr_new.inventory_item_id, pr_new.organization_id, v_t_type,
2258 v_range_no, v_division_no, v_loc_id,
2259 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
2260 v_pr_uom_code,
2261 pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
2262 );
2263
2264
2265 -- Start, Vijay Shankar for Bug# 2915814Z
2266 ELSIF ( v_excise_flag = 'Y'
2267 AND v_item_class IN ('CCIN', 'CCEX')
2268 AND v_t_type = 'PI'
2269 )
2270 THEN
2271
2272 v_iss_id := pr_new.transaction_id;
2273 v_iss_date := pr_new.transaction_date;
2274
2275 IF v_item_class IN ('CCIN') THEN
2276 V_TRANS_QTY := ABS(V_NEW_TRANS_QTY);
2277
2278 ELSIF v_item_class IN ('CCEX') THEN
2279 /**v_t_qty := v_new_trans_qty;**/ --commented by sriram on 23-may-01
2280
2281 v_t_qty := NULL; --Start addition by Sriram on 23-may-01
2282 END IF;
2283
2284 v_reg_type := 'A';
2285 -- This is assigned as 'A' as per the Functional Input of this bug.
2286 /*Bug 9550254 - Start*/
2287 /*
2288 OPEN srno_i_cur(pr_new.organization_id, pr_new.inventory_item_id, v_loc_id, v_reg_type, v_f_year );
2289 FETCH srno_i_cur INTO v_srno;
2290 CLOSE srno_i_cur;
2291 */
2292 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
2293 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,
2294 v_f_year,v_reg_type,v_srno);
2295 /*Bug 9550254 - End*/
2296 IF v_srno = 0 THEN
2297 /*Bug 9550254 - Start*/
2298 -- v_opening_qty := 0;
2299 -- v_closing_qty := v_new_trans_qty;
2300 v_closing_qty := v_opening_qty ;
2301 /*Bug 9550254 - End*/
2302 v_srno := 1;
2303 ELSE
2304 OPEN opening_balance_cur ( pr_new.organization_id, pr_new.inventory_item_id, v_srno, v_loc_id,
2305 v_reg_type, v_f_year );
2306 FETCH opening_balance_cur INTO v_opening_qty, v_closing_qty;
2307 CLOSE opening_balance_cur;
2308
2309 v_opening_qty := v_closing_qty;
2310 v_srno := v_srno + 1;
2311 END IF;
2312
2313 IF v_closing_qty > 0 THEN
2314 -- There exists some balance in RG23 part I A
2315
2316 IF v_closing_qty >= ABS(v_new_trans_qty) THEN
2317
2318 -- balance is enough in RG23 Part I A
2319 v_hit_rg23_qty := ABS(v_new_trans_qty);
2320 v_hit_rg1_qty := 0;
2321 v_closing_qty := v_closing_qty - abs(v_new_trans_qty);
2322
2323 -- this is the partial case
2324 ELSIF v_closing_qty < ABS(v_new_trans_qty) THEN
2325 -- balance is not enough in RG23 Part I A, find how much can be hit from here and the rest
2326 -- from RG1. RG1 can go negative also(functional input for this bug) ,
2327 -- so no need to check balance there.
2328
2329 v_hit_rg23_qty := v_closing_qty;
2330 v_hit_rg1_qty := ABS(v_new_trans_qty) - v_hit_rg23_qty;
2331 v_closing_qty := 0;
2332
2333 END IF;
2334
2335 ELSE
2336
2337 -- No balance in Rg23 Part 1 A, so the whole quantity to hit RG1.
2338 v_hit_rg23_qty :=0;
2339 v_hit_rg1_qty := ABS(v_new_trans_qty);
2340
2341 -- here closing quantity for RG23 need not be set because if the execution comes here, then it is not going to hit RG23 Part I
2342 END IF;
2343
2344 IF v_hit_rg23_qty > 0 THEN
2345
2346 v_iss_qty := v_hit_rg23_qty;
2347
2348 /*bug 9122545*/
2349 OPEN loc_id_cur (pr_new.subinventory_code, pr_new.organization_id);
2350 FETCH loc_id_cur INTO v_loc_id;
2351 CLOSE loc_id_cur;
2352
2353 IF v_loc_id IS NULL THEN
2354 v_loc_id := 0;
2355 END IF;
2356
2357 OPEN c_org_addl_rg_flag(pr_new.organization_id, v_loc_id) ;
2358 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag ;
2359 CLOSE c_org_addl_rg_flag ;
2360
2361 IF lv_allow_negative_rg_flag = 'N'
2362 THEN
2363 if v_closing_qty < 0 then
2364 APP_EXCEPTION.RAISE_EXCEPTION('JA', -20113, 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||v_reg_type||','||pr_new.organization_id||','||v_loc_id);
2365 end if;
2366 END IF;
2367 /*end bug 9122545*/
2368 INSERT INTO JAI_CMN_RG_23AC_I_TRXS (
2369 register_id, fin_year, slno,
2370 last_update_date, last_updated_by, creation_date,
2371 created_by, last_update_login,
2372 TRANSACTION_SOURCE_NUM, inventory_item_id,
2373 organization_id, transaction_type, range_no,
2374 division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
2375 goods_issue_quantity, OTH_RECEIPT_ID_REF, oth_receipt_date,
2376 oth_receipt_quantity, register_type, location_id,
2377 transaction_uom_code, transaction_date,
2378 opening_balance_qty, closing_balance_qty,
2379 primary_uom_code
2380 ) VALUES (
2381 JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2382 pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
2383 pr_new.created_by, pr_new.last_update_login,
2384 pr_new.transaction_type_id, pr_new.inventory_item_id,
2385 pr_new.organization_id, v_t_type, v_range_no,
2386 v_division_no, v_iss_id, v_iss_date,
2387 round(v_iss_qty,5), null, null,
2388 null, v_reg_type, v_loc_id,
2389 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
2390 round(v_opening_qty,5), round( v_closing_qty,5),
2391 v_pr_uom_code
2392 );
2393
2394 END IF;
2395
2396 IF v_hit_rg1_qty >0 THEN
2397
2398 v_new_trans_qty := -v_hit_rg1_qty; -- quantity should be negetive because this is an issue transaction
2399
2400 IF v_item_class IN ('CCIN') THEN
2401 v_trans_qty := abs(v_new_trans_qty);
2402 END IF;
2403
2404 v_sr_no := null;
2405 v_srno := null;
2406 v_bal_packed := null;
2407 v_bal_loose := null;
2408 /*Bug 9550254 - Start*/
2409 /*
2410 OPEN srno_ii_cur (v_loc_id, v_f_year);
2411 FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
2412 CLOSE srno_ii_cur;
2413 */
2414 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2415 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
2416 v_srno,v_bal_packed);
2417 /*Bug 9550254 - End*/
2418 IF NVL (v_srno, 0) = 0 THEN
2419 v_srno := 1;
2420 /*Bug 9550254 - Commented the below as it is already calculated via jai_om_rg_pkg.ja_in_rgi_balance*/
2421 -- v_bal_packed := 0;
2422 -- v_bal_loose := 0;
2423 ELSE
2424 v_srno := v_srno + 1;
2425 END IF;
2426
2427 v_manufactured_qty := 0;
2428 v_manufactured_pack_qty := NULL;
2429 v_manu_loose_qty := NULL;
2430
2431 OPEN fetch_type_name (pr_new.transaction_type_id);
2432 FETCH fetch_type_name INTO v_oth_purpose;
2433 CLOSE fetch_type_name;
2434
2435 -- v_oth_pur_npe_qty := abs(v_new_trans_qty); commented by Sriram on 22-may-01
2436
2437 /** Added by Sriram on 15-OCT-01 **/
2438 v_manu_qty := 0; --add by Sriram on 23-dec-01
2439 -- v_manu_qty := v_new_trans_qty;
2440 v_manu_pkd_qty := 0;
2441 v_manu_loose_qty := ABS (v_new_trans_qty);
2442
2443 /** End addition by Sriram on 15-OCT-01 **/
2444
2445 /* Added by Satya on 23-Oct-01 */
2446 IF ( pr_new.transaction_action_id = 32 AND pr_new.transaction_source_type_id = 5 ) THEN
2447 v_manu_loose_qty := v_new_trans_qty;
2448 v_manu_home_qty := NULL;
2449 v_manu_pkd_qty := 0;
2450 v_manufactured_qty := v_new_trans_qty; --adde by Sriram on 26-Dec-2001
2451 END IF;
2452
2453
2454 IF ( NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0) ) >= ABS (v_new_trans_qty) THEN
2455 IF NVL (v_bal_loose, 0) >= ABS (v_new_trans_qty) THEN
2456 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_new_trans_qty, 0));
2457
2458 ELSE
2459 v_bal_loose := NVL (v_bal_loose, 0) - ABS (NVL (v_new_trans_qty, 0));
2460 v_bal_packed := NVL (v_bal_packed, 0) + NVL (v_bal_loose, 0);
2461 END IF;
2462
2463 ELSE
2464 v_bal_packed := NVL (v_bal_packed, 0) - ABS (NVL (v_new_trans_qty, 0));
2465 v_bal_loose := NVL (v_bal_loose, 0) + NVL (v_bal_packed, 0);
2466 v_bal_packed := 0;
2467 END IF;
2468
2469
2470 -- jai_cmn_utils_pkg.print_log('rg1.log','before 5');
2471 INSERT INTO JAI_CMN_RG_I_TRXS (
2472 register_id, fin_year, slno,
2473 manufactured_qty, manufactured_packed_qty,
2474 manufactured_loose_qty, balance_packed, balance_loose,
2475 -- other_purpose_n_pay_ed_qty, -- by sriram
2476 to_other_factory_n_pay_ed_qty, other_purpose,
2477 -- to_other_factory_n_pay_ed_qty, --bby sriram
2478 other_purpose_n_pay_ed_qty, for_export_n_pay_ed_qty,
2479 for_home_use_pay_ed_qty, --Added by Satya on 23-Oct-01
2480 last_update_date,
2481 last_updated_by, creation_date, created_by,
2482 last_update_login, TRANSACTION_SOURCE_NUM,
2483 inventory_item_id, organization_id, transaction_type,
2484 range_no, division_no, location_id,
2485 transaction_uom_code, transaction_date,
2486 primary_uom_code,
2487 REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2488 ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2489 round(v_manufactured_qty,5), round(v_manufactured_pack_qty,5),
2490 Null, round(v_bal_packed,5), round(v_bal_loose,5), -- v_manu_loose_qty set to null by sriram - bug # 3258066 based on product management advice.
2491 round(v_oth_pur_npe_qty,5), v_oth_purpose,
2492 round( v_manu_loose_qty,5), NULL, --earlier 'PI' corresponding field is number -- v_trans_qty changed to v_manu_loose_qty by sriram - bug # 3258066 based on p.m advice.
2493 round(v_manu_home_qty,5), --Added by Satya on 23-Oct-01
2494 pr_new.last_update_date,
2495 pr_new.last_updated_by, SYSDATE, pr_new.created_by,
2496 pr_new.last_update_login, pr_new.transaction_type_id,
2497 pr_new.inventory_item_id, pr_new.organization_id, v_t_type,
2498 v_range_no, v_division_no, v_loc_id,
2499 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
2500 v_pr_uom_code,
2501 pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
2502 );
2503
2504 END IF; -- If v_hit_rg1_qty > 0
2505 -- End, Vijay Shankar for Bug# 2915814Z
2506
2507 END IF;
2508 -- KKK
2509
2510 ---11th, 12th, 13th case starts here for WIP Component Return
2511 /*
2512 11. Inventory Delivery Adjustment
2513 12. Cycle Count Adjustment.
2514 13. Physical Inventory Adjustment.
2515 In this part if item class is in RMIN,RMEX,CCIN,CGEX then record will be inserted in
2516 RG23 Part I with transaction type as Issue Adjustment 'IA' or Receipt Adjustemt 'RA'
2517 based on adjusted quantity.
2518 If adjusted quantity is Negative then transaction type is IA otherwise for positive
2519 adjusted quantity the transaction type is RA.
2520 In this part if item class is in FGIN, FGEX then record will be inserted in RG I
2521 register with transaction type as Issue Adjustment 'IA' or Receipt Adjustemt 'RA'
2522 based on adjusted quantity.
2523 If adjusted quantity is Negative then transaction type is IA otherwise for positive
2524 adjusted quantity the transaction type is RA.
2525 */
2526 ELSIF ( ( pr_new.transaction_action_id = 29
2527 AND pr_new.transaction_source_type_id = 13
2528 )
2529 OR ( pr_new.transaction_action_id = 4
2530 AND pr_new.transaction_source_type_id = 9
2531 )
2532 OR ( pr_new.transaction_action_id = 29
2533 AND pr_new.transaction_source_type_id = 1
2534 )
2535 OR --added for phase 2 enhancement 15/10/99 Gaurav
2536 ( pr_new.transaction_action_id = 29
2537 AND pr_new.transaction_source_type_id = 7
2538 )
2539 OR --added for phase 2 enhancement 15/10/99 Gaurav
2540 ( pr_new.transaction_action_id = 8
2541 AND pr_new.transaction_source_type_id = 10
2542 )
2543 )
2544 THEN
2545 IF v_bonded_curr = 'N' AND v_trading_curr = 'N'
2546 THEN
2547 RETURN;
2548 END IF;
2549 ---start additions for bug# 8530264
2550 /*this trigger will fire only at the time of delivery,
2551
2552 here the issue is that the delivery adjustment hitting qty register ,irrespective of the qty register entry
2553 has been made for the parent delivery
2554
2555
2556 correction to the delivery shall not make any register entry if the parent delivery doesnot have the
2557 corresponding register entry.
2558
2559 Added a condition to check whether the parenty delivery has register entry or not.
2560 if it is not register entry will not be made for the correction to the delivery.
2561
2562 :NEW.transaction_action_id = 29 AND :NEW.transaction_source_type_id = 1 --> (Delivery adjustments on a Purchase order receipt)
2563
2564 *****/
2565
2566
2567
2568 if ( pr_new.transaction_action_id = 29
2569 AND pr_new.transaction_source_type_id = 1 )
2570 THEN
2571 open get_qty_update_flag;
2572 fetch get_qty_update_flag into lv_qty_register_flag;
2573 close get_qty_update_flag;
2574
2575 if NVL(lv_qty_register_flag,'N')='N'
2576 then
2577 return;
2578 end if;
2579
2580 end if;
2581
2582 --end additions for bug#8530264
2583
2584
2585 -- IF pr_new.quantity_adjusted < 0 -- comminted by subbu and sri on 30th nov 2000
2586 IF v_new_trans_qty < 0 -- added by subbu and sri on 30th nov 2000
2587 THEN
2588 v_t_type := 'IA';
2589 -- v_iss_qty := abs(v_new_adjust_qty); -- comminted by subbu and Sri on 30-NOV-00
2590 v_iss_qty := ABS (v_new_trans_qty); -- added by subbu and Sri on 30-NOV-00
2591 v_iss_id := pr_new.transaction_id;
2592 v_iss_date := pr_new.transaction_date;
2593 v_rec_id := NULL;
2594 v_rec_qty := NULL;
2595 v_rec_date := NULL;
2596 ELSE
2597 v_t_type := 'RA';
2598 v_iss_qty := NULL;
2599 v_iss_id := NULL;
2600 v_iss_date := NULL;
2601 v_rec_id := pr_new.transaction_id;
2602 v_rec_qty := v_new_trans_qty; --v_new_adjust_qty;ssumaith -bug#6609191
2603 v_rec_date := pr_new.transaction_date;
2604 END IF;
2605
2606 IF NVL (v_trading_curr, 'N') = 'Y'
2607 THEN
2608 IF NVL (v_item_trading_flag, 'N') = 'Y'
2609 THEN
2610 --cal proc for RG23D entry
2611 jai_cmn_rg_23d_trxs_pkg.make_entry (
2612 pr_new.organization_id,
2613 v_loc_id,
2614 v_t_type,
2615 pr_new.inventory_item_id,
2616 pr_new.subinventory_code,
2617 v_pr_uom_code,
2618 pr_new.transaction_uom,
2619 v_rec_id,
2620 v_rec_date,
2621 v_rec_qty,
2622 pr_new.transaction_type_id,
2623 v_iss_id,
2624 v_iss_date,
2625 v_iss_qty,
2626 pr_new.transaction_date,
2627 pr_new.creation_date,
2628 pr_new.created_by,
2629 pr_new.last_update_date,
2630 pr_new.last_update_login,
2631 pr_new.last_updated_by
2632 );
2633 END IF;
2634 ELSE
2635 IF ( v_excise_flag = 'Y'
2636 AND v_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX')
2637 AND v_t_type = 'IA'
2638 )
2639 OR ( v_modvat_flag = 'Y'
2640 AND v_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX')
2641 AND v_t_type = 'RA'
2642 )
2643 THEN
2644 v_reg_type := 'A';
2645 ELSIF ( v_excise_flag = 'Y'
2646 AND v_item_class IN ('CGIN', 'CGEX')
2647 AND v_t_type = 'IA'
2648 )
2649 OR ( v_modvat_flag = 'Y'
2650 AND v_item_class IN ('CGIN', 'CGEX')
2651 AND v_t_type = 'RA'
2652 )
2653 THEN
2654 v_reg_type := 'C';
2655 END IF;
2656
2657 IF ( v_excise_flag = 'Y'
2658 AND v_item_class IN
2659 ('CGIN', 'CGEX', 'RMIN', 'RMEX', 'CCIN', 'CCEX')
2660 AND v_t_type = 'IA'
2661 )
2662 OR ( v_modvat_flag = 'Y'
2663 AND v_item_class IN
2664 ('CGIN', 'CGEX', 'RMIN', 'RMEX', 'CCIN', 'CCEX')
2665 AND v_t_type = 'RA'
2666 )
2667 THEN
2668 /*Bug 9550254 - Start*/
2669 /*
2670 OPEN srno_i_cur (
2671 pr_new.organization_id,
2672 pr_new.inventory_item_id,
2673 v_loc_id,
2674 v_reg_type,
2675 v_f_year
2676 );
2677 FETCH srno_i_cur INTO v_srno;
2678 CLOSE srno_i_cur;
2679 */
2680 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_23AC_I_TRXS*/
2681 v_opening_qty := jai_om_rg_pkg.ja_in_rg23i_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,
2682 v_f_year,v_reg_type,v_srno);
2683 /*Bug 9550254 - End*/
2684
2685 IF NVL (v_srno, 0) = 0
2686 THEN
2687 v_srno := 1;
2688 v_sr_no := 0;
2689 ELSE
2690 v_sr_no := v_srno;
2691 v_srno := v_srno
2692 + 1;
2693 END IF;
2694
2695 IF v_sr_no = 0
2696 THEN
2697 /*Bug 9550254 - Start*/
2698 -- v_opening_qty := 0;
2699 -- v_closing_qty := v_new_trans_qty;
2700 v_closing_qty := v_opening_qty + v_new_trans_qty;
2701 /*Bug 9550254 - End*/
2702 ELSE
2703 OPEN opening_balance_cur (
2704 pr_new.organization_id,
2705 pr_new.inventory_item_id,
2706 v_sr_no,
2707 v_loc_id,
2708 v_reg_type,
2709 v_f_year
2710 );
2711 FETCH opening_balance_cur INTO v_op_qty, v_cl_qty;
2712 CLOSE opening_balance_cur;
2713
2714 IF NVL (v_cl_qty, 0) <> 0
2715 THEN
2716 v_opening_qty := v_cl_qty;
2717 v_closing_qty := v_cl_qty
2718 + v_new_trans_qty;
2719 ELSE
2720 v_opening_qty := 0;
2721 v_closing_qty := v_new_trans_qty;
2722 END IF;
2723 END IF;
2724
2725 /*bug 9122545*/
2726 OPEN loc_id_cur (pr_new.subinventory_code,pr_new.organization_id);
2727 FETCH loc_id_cur INTO v_loc_id;
2728 CLOSE loc_id_cur;
2729
2730 IF v_loc_id IS NULL THEN
2731 v_loc_id := 0;
2732 END IF;
2733
2734 OPEN c_org_addl_rg_flag(pr_new.organization_id, v_loc_id) ;
2735 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag ;
2736 CLOSE c_org_addl_rg_flag ;
2737
2738 IF lv_allow_negative_rg_flag = 'N'
2739 THEN
2740 if v_closing_qty < 0 then
2741 APP_EXCEPTION.RAISE_EXCEPTION('JA', -20114, 'Enough RG23 Part1 balances do not exist. Register Type,Org,Loc-'||v_reg_type||','||pr_new.organization_id||','||v_loc_id);
2742 end if;
2743 END IF;
2744 /*end bug 9122545*/
2745 INSERT INTO JAI_CMN_RG_23AC_I_TRXS
2746 (register_id, fin_year, slno,
2747 last_update_date, last_updated_by, creation_date,
2748 created_by, last_update_login,
2749 TRANSACTION_SOURCE_NUM, inventory_item_id,
2750 organization_id, transaction_type, range_no,
2751 division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
2752 goods_issue_quantity, OTH_RECEIPT_ID_REF,
2753 oth_receipt_date, oth_receipt_quantity,
2754 register_type, location_id, transaction_uom_code,
2755 transaction_date, opening_balance_qty,
2756 closing_balance_qty, primary_uom_code)
2757 VALUES (JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2758 pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
2759 pr_new.created_by, pr_new.last_update_login,
2760 pr_new.transaction_type_id, pr_new.inventory_item_id,
2761 pr_new.organization_id, v_t_type, v_range_no,
2762 v_division_no, v_iss_id, v_iss_date,
2763 round( v_iss_qty,5), v_rec_id,
2764 v_rec_date, round( v_rec_qty,5),
2765 v_reg_type, v_loc_id, pr_new.transaction_uom,
2766 TRUNC (pr_new.transaction_date), round(v_opening_qty,5),
2767 round( v_closing_qty,5), v_pr_uom_code);
2768 ELSIF ( v_excise_flag = 'Y'
2769 AND v_item_class IN ('FGIN', 'FGEX')
2770 AND v_t_type = 'IA'
2771 )
2772 OR ( v_modvat_flag = 'Y'
2773 AND v_item_class IN ('FGIN', 'FGEX')
2774 AND v_t_type = 'RA'
2775 )
2776 THEN
2777 IF v_t_type = 'IA'
2778 THEN
2779 IF v_item_class IN ('FGIN')
2780 THEN
2781 -- v_trans_qty := v_new_adjust_qty; --comminted by Subbu and Sri ON 30-NOV-00
2782 v_trans_qty := v_new_trans_qty; --added by subbu and Sri on 30-NOV-00
2783 ELSIF v_item_class IN ('FGEX')
2784 THEN
2785 v_t_qty := v_new_trans_qty; -- ssumaith -6609191
2786 END IF;
2787 END IF;
2788 /*Bug 9550254 - Start*/
2789 /*
2790 OPEN srno_ii_cur (v_loc_id, v_f_year);
2791 FETCH srno_ii_cur INTO v_srno, v_bal_packed, v_bal_loose;
2792 CLOSE srno_ii_cur;
2793 */
2794 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
2795 v_bal_loose := jai_om_rg_pkg.ja_in_rgi_balance(pr_new.organization_id,v_loc_id,pr_new.inventory_item_id,v_f_year,
2796 v_srno,v_bal_packed);
2797 /*Bug 9550254 - End*/
2798
2799 IF NVL (v_srno, 0) = 0
2800 THEN
2801 v_srno := 1;
2802 v_sr_no := 0;
2803 ELSE
2804 v_sr_no := v_srno;
2805 v_srno := v_srno
2806 + 1;
2807 END IF;
2808
2809 IF v_new_trans_qty < 0
2810 THEN
2811 v_manufactured_qty := 0;
2812 v_manufactured_pack_qty := NULL;
2813 v_manu_loose_qty := NULL;
2814
2815 IF ( NVL (v_bal_packed, 0)
2816 + NVL (v_bal_loose, 0)
2817 ) >= ABS (v_new_trans_qty)
2818 THEN
2819 IF NVL (v_bal_loose, 0) >= ABS (v_new_trans_qty)
2820 THEN
2821 v_bal_loose :=
2822 NVL (v_bal_loose, 0)
2823 - ABS (NVL (v_new_trans_qty, 0));
2824 ELSE
2825 v_bal_loose :=
2826 NVL (v_bal_loose, 0)
2827 - ABS (NVL (v_new_trans_qty, 0));
2828 v_bal_packed :=
2829 NVL (v_bal_packed, 0)
2830 + NVL (v_bal_loose, 0);
2831 v_bal_loose := 0;
2832 END IF;
2833 ELSE
2834 v_bal_packed :=
2835 NVL (v_bal_packed, 0)
2836 - ABS (NVL (v_new_trans_qty, 0));
2837 v_bal_loose := NVL (v_bal_loose, 0)
2838 + NVL (v_bal_packed, 0);
2839 v_bal_packed := 0;
2840 END IF;
2841 ELSE
2842 v_manufactured_qty := v_new_trans_qty;
2843 v_manufactured_pack_qty := 0;
2844 v_manu_loose_qty := v_new_trans_qty;
2845 v_bal_loose := NVL (v_bal_loose, 0)
2846 + NVL (v_new_trans_qty, 0);
2847 END IF;
2848
2849 -- jai_cmn_utils_pkg.print_log('rg1.log','before 6');
2850 INSERT INTO JAI_CMN_RG_I_TRXS
2851 (register_id, fin_year, slno,
2852 manufactured_qty, manufactured_packed_qty,
2853 manufactured_loose_qty, balance_packed,
2854 balance_loose, to_other_factory_n_pay_ed_qty,
2855 for_export_n_pay_ed_qty, last_update_date,
2856 last_updated_by, creation_date, created_by,
2857 last_update_login, TRANSACTION_SOURCE_NUM,
2858 inventory_item_id, organization_id,
2859 transaction_type, range_no, division_no, location_id,
2860 transaction_uom_code, transaction_date,
2861 primary_uom_code,
2862 REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
2863 ) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
2864 round(v_manufactured_qty,5), round(v_manufactured_pack_qty,5),
2865 round(v_manu_loose_qty,5), round( v_bal_packed,5),
2866 round( v_bal_loose,5), round( v_trans_qty,5),
2867 round(v_t_qty,5), pr_new.last_update_date,
2868 pr_new.last_updated_by, SYSDATE, pr_new.created_by,
2869 pr_new.last_update_login, pr_new.transaction_type_id,
2870 pr_new.inventory_item_id, pr_new.organization_id,
2871 v_t_type, v_range_no, v_division_no, v_loc_id,
2872 pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
2873 v_pr_uom_code,
2874 pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
2875 );
2876 END IF;
2877 END IF;
2878 END IF;
2879 /* Added an exception block by Ramananda for bug#4570303 */
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 Pv_return_code := jai_constants.unexpected_error;
2883 Pv_return_message := 'Encountered an error in JAI_INV_MMT_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
2884 END ARI_T1 ;
2885
2886 END JAI_INV_MMT_TRIGGER_PKG ;