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