[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_I_TRXS_PKG
Source
1 PACKAGE BODY jai_cmn_rg_i_trxs_pkg AS
2 /* $Header: jai_cmn_rg_i.plb 120.3.12010000.4 2010/04/28 11:48:17 vkaranam ship $ */
3
4
5 PROCEDURE validate_rg1_balances(
6 P_ORGANIZATION_ID IN NUMBER,
7 P_LOCATION_ID IN NUMBER,
8 P_INVENTORY_ITEM_ID IN NUMBER,
9 P_FIN_YEAR IN NUMBER,
10 P_QUANTITY IN NUMBER,
11 P_TRANSACTION_UOM_CODE IN VARCHAR2,
12 P_TRANSACTION_TYPE IN VARCHAR2,
13 P_ERR_BUF OUT NOCOPY VARCHAR2
14 ) IS
15
16 /* Added by Ramananda for bug#4407165 */
17 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.validate_rg1_balances';
18
19 v_primary_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
20 v_transaction_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
21 CURSOR c_item_primary_uom(p_organization_id NUMBER, p_inventory_item_id NUMBER) IS
22 SELECT primary_uom_code
23 FROM mtl_system_items
24 WHERE organization_id = p_organization_id
25 AND inventory_item_id = p_inventory_item_id;
26
27 vTransToPrimaryUOMConv NUMBER;
28 vMaxSlno NUMBER;
29
30 -- Quantity field used in the insert statement
31 vBalanceLoose NUMBER;
32 vBalancePacked NUMBER;
33 v_quantity NUMBER ; -- := NVL(p_quantity, 0) File.Sql.35 by Brathod
34 v_manufactured_qty NUMBER;
35
36 /*bug 9122545*/
37 CURSOR c_org_addl_rg_flag (cp_organization_id jai_cmn_inventory_orgs.organization_id%type,
38 cp_location_id jai_cmn_inventory_orgs.location_id%type)
39 IS
40 SELECT nvl(allow_negative_rg_flag,'N')
41 FROM jai_cmn_inventory_orgs
42 WHERE organization_id = cp_organization_id
43 AND location_id = cp_location_id;
44
45 lv_allow_negative_rg_flag jai_cmn_inventory_orgs.allow_negative_rg_flag%TYPE;
46 /*end bug 9122545*/
47
48
49 BEGIN
50
51 /*-------------------------------------------------------------------------------------------------------------
52 Functionality of the Package
53 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
54 - PROCEDURE validate_rg1_balances
55 This procedure is used to validate whether required amount of balances are available to issue the goods.
56 Populates P_ERR_BUF variable with proper message if balances are not sufficient to hit RG1 register
57
58 - FUNCTION get_rg1_transaction_id
59 This function returns a UNIQUE NUMBER used to identify the RG1 transaction type in JAI_CMN_RG_I_TRXS table
60
61 - PROCEDURE create_rg1_entry
62 This procedure takes in all the values that has to be populated into database columns.
63 Generates the sequence no, serial no for REGISTER_ID, SLNO columns. Calculates the quantity balance to
64 be populated into BALANCE_LOOSE column of the table and finally inserts data into JAI_CMN_RG_I_TRXS table.
65
66
67
68 Change History
69 ~~~~~~~~~~~~~~
70
71 S.No DD/MM/YYYY Author and Details
72 ---------------------------------------------------------------------------------------------------------------
73 1 30/04/2004 Nagaraj.s for Bug # 3535729 File Version : 619.1
74 In case of RECEIPTS, and Transaction Type ='CR' transaction_id=18 is set.
75 2. 8-Jun-2005 Version 116.2 jai_cmn_rg_i -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
76 as required for CASE COMPLAINCE.
77 3. 14/07/2005 4485801 Brathod, File Version 117.1
78 Issue: Inventory Convergence Uptake for R12 Initiative
79 4. 16/04/2007 bduvarag for the Bug#5989740, file version 120.2
80 Forward porting the changes done in 11i bug#5907436
81
82
83 5. 16/08/2007 vkaranam for bug#6030615,File version 120.3
84 forward porting the changes done in 115 bug#2942973(Interorg).
85 6. 27-Nov-2009 Bug 9122545 File version 120.1.12000000.4 / 120.3.12010000.2 / 120.4
86 Checked the setup option to allow negative balance in quantity registers before
87 raising the error "Enough RG1 balance is not available to Issue the Goods".
88
89 7. 06/04/2010 Bug 9550254
90 The opening balance for the RG I has been derived from the previous
91 financial year closing balance, if no entries found for the current year.
92 8. 27-apr-2010 bug#9466919
93 issue :quantity in rg registers are not in sync with the inventory.
94 fix:
95 added the rounding precision of 5 to the quantity fields while inserting.
96 -------------------------------------------------------------------------------------------------------------*/
97 v_quantity := NVL(p_quantity, 0); -- File.Sql.35 by Brathod
98 IF p_transaction_type IN ('R', 'RA', 'IOR', 'PR', 'CR') THEN
99 -- No need to test for balances for these transactions types
100 -- as these are receipt transactions which increase the balances
101 RETURN;
102 END IF;
103
104 OPEN c_item_primary_uom(p_organization_id, p_inventory_item_id);
105 FETCH c_item_primary_uom INTO v_primary_uom_code;
106 CLOSE c_item_primary_uom;
107
108 IF p_transaction_uom_code IS NULL THEN
109 v_transaction_uom_code := v_primary_uom_code;
110 ELSE
111 v_transaction_uom_code := p_transaction_uom_code;
112 END IF;
113
114 IF v_transaction_uom_code <> v_primary_uom_code THEN
115 INV_CONVERT.inv_um_conversion(
116 v_transaction_uom_code, v_primary_uom_code,
117 p_inventory_item_id, vTransToPrimaryUOMConv
118 );
119
120 IF nvl(vTransToPrimaryUOMConv, 0) <= 0 THEN
121 INV_CONVERT.inv_um_conversion(
122 v_transaction_uom_code, v_primary_uom_code,
123 0, vTransToPrimaryUOMConv
124 );
125 IF nvl(vTransToPrimaryUOMConv, 0) <= 0 THEN
126 vTransToPrimaryUOMConv := 1;
127 END IF;
128 END IF;
129
130 ELSE
131 vTransToPrimaryUOMConv := 1;
132 END IF;
133
134 v_quantity := nvl(p_quantity, 0) * vTransToPrimaryUOMConv;
135 /*Bug 9550254 - Start*/
136 /*
137 SELECT max(slno) INTO vMaxSlno
138 FROM JAI_CMN_RG_I_TRXS
139 WHERE organization_id = p_organization_id
140 AND location_id = p_location_id
141 AND inventory_item_id = p_inventory_item_id
142 AND fin_year = p_fin_year;
143
144 IF vMaxSlno IS NOT NULL THEN
145 SELECT NVL(balance_packed,0), NVL(balance_loose,0) INTO vBalancePacked, vBalanceLoose
146 FROM JAI_CMN_RG_I_TRXS
147 WHERE organization_id = p_organization_id
148 and location_id = p_location_id
149 and inventory_item_id = p_inventory_item_id
150 AND fin_year = p_fin_year
151 AND slno = vMaxSlno;
152
153 ELSE
154 -- If execution comes here, then it means it is an ISSUE type of transaction and no balances available
155 p_err_buf := 'Enough RG1 balance is not available to Issue the Goods';
156 END IF;
157 */
158 /*Code modified to fetch the Opening Balance when no transactions currently exist in JAI_CMN_RG_I_TRXS*/
159 vBalanceLoose := jai_om_rg_pkg.ja_in_rgi_balance(p_organization_id,p_location_id,p_inventory_item_id,p_fin_year,
160 vMaxSlno,vBalancePacked);
161 /*Bug 9550254 - End*/
162
163 IF p_transaction_type IN ('I', 'IA', 'IOI', 'PI') THEN
164 /*bug 9122545*/
165 OPEN c_org_addl_rg_flag(p_organization_id, p_location_id );
166 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag;
167 CLOSE c_org_addl_rg_flag;
168
169 IF lv_allow_negative_rg_flag = 'Y'
170 THEN
171 p_err_buf := NULL;
172 ELSIF lv_allow_negative_rg_flag ='N'
173 THEN
174 IF vBalanceLoose < v_quantity THEN
175 p_err_buf := 'Enough RG1 balance is not available to Issue the Goods';
176 END IF;
177 END IF;
178 /*end bug 9122545*/
179 END IF;
180
181 /* Added by Ramananda for bug#4407165 */
182 EXCEPTION
183 WHEN OTHERS THEN
184 P_ERR_BUF := null;
185 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
186 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
187 app_exception.raise_exception;
188
189 END validate_rg1_balances;
190
191 FUNCTION get_rg1_transaction_id(
192 P_TRANSACTION_TYPE IN VARCHAR2,
193 P_ISSUE_TYPE IN VARCHAR2,
194 P_CALLED_FROM IN VARCHAR2
195 ) RETURN NUMBER IS
196
197 v_transaction_id NUMBER := -1;
198
199 /* Added by Ramananda for bug#4407165 */
200 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.get_rg1_transaction_id';
201
202 BEGIN
203 IF p_called_from = 'MANUAL ENTRY' THEN
204 IF p_transaction_type = 'R' THEN
205 v_transaction_id := 91;
206 ELSIF p_transaction_type = 'IOR' THEN
207 v_transaction_id := 92;
208 ELSIF p_transaction_type = 'RA' THEN
209 v_transaction_id := 93;
210 ELSIF p_transaction_type = 'PR' THEN
211 v_transaction_id := 94;
212 ELSIF p_transaction_type = 'CR' THEN
213 v_transaction_id := 95;
214 ELSIF p_transaction_type = 'I' THEN
215 v_transaction_id := 100;
216 ELSIF p_transaction_type = 'IOI' THEN
217 v_transaction_id := 110;
218 ELSIF p_transaction_type = 'IA' THEN
219 v_transaction_id := 120;
220 ELSIF p_transaction_type = 'PI' THEN
221 v_transaction_id := 130;
222 ELSE
223 v_transaction_id := 99;
224 END IF;
225
226 IF p_issue_type = 'HU' THEN
227 v_transaction_id := v_transaction_id + 1;
228 ELSIF p_issue_type = 'EWE' THEN
229 v_transaction_id := v_transaction_id + 2;
230 ELSIF p_issue_type = 'ENE' THEN
231 v_transaction_id := v_transaction_id + 3;
232 ELSIF p_issue_type = 'OF' THEN
233 v_transaction_id := v_transaction_id + 4;
234 ELSIF p_issue_type = 'OPWE' THEN
235 v_transaction_id := v_transaction_id + 5;
236 ELSIF p_issue_type = 'OPNE' THEN
237 v_transaction_id := v_transaction_id + 6;
238 END IF;
239
240 ELSIF p_called_from = 'RECEIPTS' THEN
241 IF p_transaction_type in ( 'R' ,'CR') THEN --3535729
242 v_transaction_id := 18;
243 ELSE
244 v_transaction_id := 98;
245 END IF;
246
247 ELSIF p_called_from = 'AAA' THEN
248 v_transaction_id := 97;
249 -- Added by Brathod, for Inv.Convergence
250 ELSIF p_called_from = 'jai_cmn_rg_opm_pkg.create_rg_i_entry'
251 AND p_transaction_type = 'R' THEN
252 v_transaction_id := 202;
253 ELSIF p_called_from = 'jai_cmn_rg_opm_pkg.create_rg_i_entry'
254 AND p_transaction_type = 'I' THEN
255 v_transaction_id := 201;
256
257 END IF;
258
259 RETURN v_transaction_id;
260
261 /* Added by Ramananda for bug#4407165 */
262 EXCEPTION
263 WHEN OTHERS THEN
264 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
265 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
266 app_exception.raise_exception;
267
268 END get_rg1_transaction_id;
269
270 PROCEDURE create_rg1_entry(
271 P_REGISTER_ID OUT NOCOPY NUMBER,
272 P_REGISTER_ID_PART_II IN NUMBER,
273 P_FIN_YEAR IN NUMBER,
274 P_SLNO OUT NOCOPY NUMBER,
275 P_TRANSACTION_ID IN NUMBER,
276 P_ORGANIZATION_ID IN NUMBER,
277 P_LOCATION_ID IN NUMBER,
278 P_TRANSACTION_DATE IN DATE,
279 P_INVENTORY_ITEM_ID IN NUMBER,
280 P_TRANSACTION_TYPE IN VARCHAR2,
281 P_REF_DOC_ID IN VARCHAR2,
282 P_QUANTITY IN NUMBER,
283 P_TRANSACTION_UOM_CODE IN VARCHAR2,
284 P_ISSUE_TYPE IN VARCHAR2,
285 P_EXCISE_DUTY_AMOUNT IN NUMBER,
286 P_EXCISE_INVOICE_NUMBER IN VARCHAR2,
287 P_EXCISE_INVOICE_DATE IN DATE,
288 P_PAYMENT_REGISTER IN VARCHAR2,
289 P_CHARGE_ACCOUNT_ID IN NUMBER,
290 P_RANGE_NO IN VARCHAR2,
291 P_DIVISION_NO IN VARCHAR2,
292 P_REMARKS IN VARCHAR2,
293 P_BASIC_ED IN NUMBER,
294 P_ADDITIONAL_ED IN NUMBER,
295 P_OTHER_ED IN NUMBER,
296 P_ASSESSABLE_VALUE IN NUMBER,
297 P_EXCISE_DUTY_RATE IN NUMBER,
298 P_VENDOR_ID IN NUMBER,
299 P_VENDOR_SITE_ID IN NUMBER,
300 P_CUSTOMER_ID IN NUMBER,
301 P_CUSTOMER_SITE_ID IN NUMBER,
302 P_CREATION_DATE IN DATE,
303 P_CREATED_BY IN NUMBER,
304 P_LAST_UPDATE_DATE IN DATE,
305 P_LAST_UPDATED_BY IN NUMBER,
306 P_LAST_UPDATE_LOGIN IN NUMBER,
307 P_CALLED_FROM IN VARCHAR2,
308 P_CESS_AMOUNT IN NUMBER DEFAULT NULL,/*Bug 2942973. To
309 resolve compilation error- bduvarag*/
310 P_SH_CESS_AMOUNT IN NUMBER DEFAULT NULL/*Bug 5989740 bduvarag*/
311 ) IS
312
313 /* Added by Ramananda for bug#4407165 */
314 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.create_rg1_entry';
315
316 vMaxSlno NUMBER;
317 v_transaction_id JAI_CMN_RG_I_TRXS.TRANSACTION_SOURCE_NUM%TYPE;
318 v_primary_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
319 v_transaction_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
320 vTransToPrimaryUOMConv NUMBER;
321
322 CURSOR c_item_primary_uom(p_organization_id NUMBER, p_inventory_item_id NUMBER) IS
323 SELECT primary_uom_code
324 FROM mtl_system_items
325 WHERE organization_id = p_organization_id
326 AND inventory_item_id = p_inventory_item_id;
327
328 -- Quantity field used in the insert statement
329 v_quantity NUMBER ; --:= NVL(p_quantity, 0); File.Sql.35 by Brathod
330
331 vBalanceLoose NUMBER;
332 vBalancePacked NUMBER;
333 v_manufactured_qty NUMBER;
334 v_manufactured_packed_qty NUMBER;
335 v_manufactured_loose_qty NUMBER;
336 v_for_home_use_pay_ed_qty NUMBER;
337 v_for_home_use_pay_ed_val NUMBER;
338 v_for_export_pay_ed_qty NUMBER;
339 v_for_export_pay_ed_val NUMBER;
340 v_for_export_n_pay_ed_qty NUMBER;
341 v_for_export_n_pay_ed_val NUMBER;
342 v_other_purpose NUMBER;
343 v_to_other_fac_n_pay_ed_qty NUMBER;
344 v_to_other_fac_n_pay_ed_val NUMBER;
345 v_other_purpose_n_pay_ed_qty NUMBER;
346 v_other_purpose_n_pay_ed_val NUMBER;
347 v_other_purpose_pay_ed_qty NUMBER;
348 v_other_purpose_pay_ed_val NUMBER;
349
350 /*bug 9122545*/
351 CURSOR c_org_addl_rg_flag (cp_organization_id jai_cmn_inventory_orgs.organization_id%type,
352 cp_location_id jai_cmn_inventory_orgs.location_id%type)
353 IS
354 SELECT nvl(allow_negative_rg_flag,'N')
355 FROM jai_cmn_inventory_orgs
356 WHERE organization_id = cp_organization_id
357 AND location_id = cp_location_id;
358
359 lv_allow_negative_rg_flag jai_cmn_inventory_orgs.allow_negative_rg_flag%TYPE;
360 /*end bug 9122545*/
361
362 BEGIN
363 v_quantity := NVL(p_quantity, 0); -- File.Sql.35 by Brathod
364 OPEN c_item_primary_uom(p_organization_id, p_inventory_item_id);
365 FETCH c_item_primary_uom INTO v_primary_uom_code;
366 CLOSE c_item_primary_uom;
367
368 IF p_transaction_uom_code IS NULL THEN
369 v_transaction_uom_code := v_primary_uom_code;
370 ELSE
371 v_transaction_uom_code := p_transaction_uom_code;
372 END IF;
373
374 IF v_transaction_uom_code <> v_primary_uom_code THEN
375 INV_CONVERT.inv_um_conversion(
376 v_transaction_uom_code, v_primary_uom_code,
377 p_inventory_item_id, vTransToPrimaryUOMConv
378 );
379
380 IF nvl(vTransToPrimaryUOMConv, 0) <= 0 THEN
381 INV_CONVERT.inv_um_conversion(
382 v_transaction_uom_code, v_primary_uom_code,
383 0, vTransToPrimaryUOMConv
384 );
385 IF nvl(vTransToPrimaryUOMConv, 0) <= 0 THEN
386 vTransToPrimaryUOMConv := 1;
387 END IF;
388 END IF;
389
390 ELSE
391 vTransToPrimaryUOMConv := 1;
392 END IF;
393
394 v_quantity := nvl(p_quantity, 0) * vTransToPrimaryUOMConv;
395
396 /*Bug 9550254 - Start*/
397 /*
398 SELECT max(slno) INTO vMaxSlno
399 FROM JAI_CMN_RG_I_TRXS
400 WHERE organization_id = p_organization_id
401 AND location_id = p_location_id
402 AND inventory_item_id = p_inventory_item_id
403 AND fin_year = p_fin_year;
404 */
405 vBalanceLoose := jai_om_rg_pkg.ja_in_rgi_balance(p_organization_id,p_location_id,p_inventory_item_id,p_fin_year,
406 vMaxSlno,vBalancePacked);
407
408 IF vMaxSlno IS NOT NULL THEN
409 SELECT NVL(balance_packed,0), NVL(balance_loose,0) INTO vBalancePacked, vBalanceLoose
410 FROM JAI_CMN_RG_I_TRXS
411 WHERE organization_id = p_organization_id
412 and location_id = p_location_id
413 and inventory_item_id = p_inventory_item_id
414 AND fin_year = p_fin_year
415 AND slno = vMaxSlno;
416 /*
417 ELSE
418 vBalancePacked := 0;
419 vBalanceLoose := 0;
420 */
421 END IF;
422 /*Bug 9550254 - End*/
423
424 IF p_transaction_type IN ('R', 'RA', 'IOR', 'PR', 'CR') THEN
425
426 vBalanceLoose := vBalanceLoose + v_quantity;
427
428 v_manufactured_qty := v_quantity;
429 v_manufactured_loose_qty := v_quantity;
430
431 ELSIF p_transaction_type IN ('I', 'IA', 'IOI', 'PI') THEN
432 IF vBalanceLoose >= v_quantity THEN
433 vBalanceLoose := vBalanceLoose - v_quantity;
434 ELSE
435 /*bug 9122545*/
436 OPEN c_org_addl_rg_flag(p_organization_id, p_location_id );
437 FETCH c_org_addl_rg_flag INTO lv_allow_negative_rg_flag;
438 CLOSE c_org_addl_rg_flag;
439
440 IF lv_allow_negative_rg_flag = 'Y'
441 THEN
442 vBalanceLoose := vBalanceLoose - v_quantity;
443 ELSIF lv_allow_negative_rg_flag = 'N'
444 THEN
445 -- p_err_buf := 'Enough RG1 balance is not available to Issue the Goods';
446 RAISE_APPLICATION_ERROR(-20199, 'Enough RG1 balance is not available to Issue the Goods');
447 -- RETURN;
448 END IF;
449 /*end bug 9122545*/
450
451 END IF;
452
453 IF p_issue_type = 'HU' THEN
454 v_for_home_use_pay_ed_qty := v_quantity;
455 v_for_home_use_pay_ed_val := p_assessable_value;
456 ELSIF p_issue_type = 'EWE' THEN
457 v_for_export_pay_ed_qty := v_quantity;
458 v_for_export_pay_ed_val := p_assessable_value;
459 ELSIF p_issue_type = 'ENE' THEN
460 v_for_export_n_pay_ed_qty := v_quantity;
461 v_for_export_n_pay_ed_val := p_assessable_value;
462 -- v_for_export_n_pay_ed_val := p_excise_duty_amount;
463 ELSIF p_issue_type = 'OF' THEN
464 v_to_other_fac_n_pay_ed_qty := v_quantity;
465 v_to_other_fac_n_pay_ed_val := p_assessable_value;
466 -- v_to_other_fac_n_pay_ed_val := p_excise_duty_amount;
467 ELSIF p_issue_type = 'OPWE' THEN
468 v_other_purpose_pay_ed_qty := v_quantity;
469 v_other_purpose_pay_ed_val := p_assessable_value;
470 ELSIF p_issue_type = 'OPNE' THEN
471 v_other_purpose_n_pay_ed_qty := v_quantity;
472 v_other_purpose_n_pay_ed_val := p_assessable_value;
473 -- v_other_purpose_n_pay_ed_val := p_excise_duty_amount;
474 END IF;
475
476 END IF;
477
478 IF vMaxSlno is NULL THEN
479 P_SLNO := 1;
480 ELSE
481 P_SLNO := vMaxSlno + 1;
482 END IF;
483
484 SELECT JAI_CMN_RG_I_TRXS_S.nextval INTO P_REGISTER_ID FROM DUAL;
485
486 v_transaction_id := get_rg1_transaction_id(
487 p_transaction_type,
488 p_issue_type,
489 p_called_from
490 );
491
492 --added rounding precision with 5 digits for bug#9466919
493 INSERT INTO JAI_CMN_RG_I_TRXS(
494 REGISTER_ID,
495 REGISTER_ID_PART_II,
496 FIN_YEAR,
497 SLNO,
498 TRANSACTION_SOURCE_NUM,
499 ORGANIZATION_ID,
500 LOCATION_ID,
501 TRANSACTION_DATE,
502 INVENTORY_ITEM_ID,
503 TRANSACTION_TYPE,
504 REF_DOC_NO,
505 MANUFACTURED_QTY,
506 MANUFACTURED_PACKED_QTY,
507 MANUFACTURED_LOOSE_QTY,
508 FOR_HOME_USE_PAY_ED_QTY,
509 FOR_HOME_USE_PAY_ED_VAL,
510 FOR_EXPORT_PAY_ED_QTY,
511 FOR_EXPORT_PAY_ED_VAL,
512 FOR_EXPORT_N_PAY_ED_QTY,
513 FOR_EXPORT_N_PAY_ED_VAL,
514 OTHER_PURPOSE,
515 TO_OTHER_FACTORY_N_PAY_ED_QTY,
516 TO_OTHER_FACTORY_N_PAY_ED_VAL,
517 OTHER_PURPOSE_N_PAY_ED_QTY,
518 OTHER_PURPOSE_N_PAY_ED_VAL,
519 OTHER_PURPOSE_PAY_ED_QTY,
520 OTHER_PURPOSE_PAY_ED_VAL,
521 PRIMARY_UOM_CODE,
522 TRANSACTION_UOM_CODE,
523 BALANCE_PACKED,
524 BALANCE_LOOSE,
525 ISSUE_TYPE,
526 EXCISE_DUTY_AMOUNT,
527 EXCISE_INVOICE_NUMBER,
528 EXCISE_INVOICE_DATE,
529 PAYMENT_REGISTER,
530 CHARGE_ACCOUNT_ID,
531 RANGE_NO,
532 DIVISION_NO,
533 REMARKS,
534 BASIC_ED,
535 ADDITIONAL_ED,
536 OTHER_ED,
537 EXCISE_DUTY_RATE,
538 VENDOR_ID,
539 VENDOR_SITE_ID,
540 CUSTOMER_ID,
541 CUSTOMER_SITE_ID,
542 CREATION_DATE,
543 CREATED_BY,
544 LAST_UPDATE_DATE,
545 LAST_UPDATED_BY,
546 LAST_UPDATE_LOGIN,
547 POSTED_FLAG,
548 MASTER_FLAG,
549 CESS_AMT,/*BUG *6030615*/
550 SH_CESS_AMT/*Bug 5989740 bduvarag*/
551 ) VALUES (
552 P_REGISTER_ID,
553 P_REGISTER_ID_PART_II,
554 P_FIN_YEAR,
555 P_SLNO,
556 V_TRANSACTION_ID,
557 P_ORGANIZATION_ID,
558 P_LOCATION_ID,
559 P_TRANSACTION_DATE,
560 P_INVENTORY_ITEM_ID,
561 P_TRANSACTION_TYPE,
562 P_REF_DOC_ID,
563 round(V_MANUFACTURED_QTY,5),
564 round(V_MANUFACTURED_PACKED_QTY,5),
565 round(V_MANUFACTURED_LOOSE_QTY,5),
566 round(V_FOR_HOME_USE_PAY_ED_QTY,5),
567 V_FOR_HOME_USE_PAY_ED_VAL,
568 round(V_FOR_EXPORT_PAY_ED_QTY,5),
569 V_FOR_EXPORT_PAY_ED_VAL,
570 round(V_FOR_EXPORT_N_PAY_ED_QTY,5),
571 V_FOR_EXPORT_N_PAY_ED_VAL,
572 V_OTHER_PURPOSE,
573 round(V_TO_OTHER_FAC_N_PAY_ED_QTY,5),
574 V_TO_OTHER_FAC_N_PAY_ED_VAL,
575 round(V_OTHER_PURPOSE_N_PAY_ED_QTY,5),
576 V_OTHER_PURPOSE_N_PAY_ED_VAL,
577 round(V_OTHER_PURPOSE_PAY_ED_QTY,5),
578 V_OTHER_PURPOSE_PAY_ED_VAL,
579 V_PRIMARY_UOM_CODE,
580 P_TRANSACTION_UOM_CODE,
581 round(vBalancePacked,5),
582 round(vBalanceLoose,5),
583 P_ISSUE_TYPE,
584 P_EXCISE_DUTY_AMOUNT,
585 P_EXCISE_INVOICE_NUMBER,
586 P_EXCISE_INVOICE_DATE,
587 P_PAYMENT_REGISTER,
588 P_CHARGE_ACCOUNT_ID,
589 P_RANGE_NO,
590 P_DIVISION_NO,
591 P_REMARKS,
592 P_BASIC_ED,
593 P_ADDITIONAL_ED,
594 P_OTHER_ED,
595 P_EXCISE_DUTY_RATE,
596 P_VENDOR_ID,
597 P_VENDOR_SITE_ID,
598 P_CUSTOMER_ID,
599 P_CUSTOMER_SITE_ID,
600 P_CREATION_DATE,
601 P_CREATED_BY,
602 P_LAST_UPDATE_DATE,
603 P_LAST_UPDATED_BY,
604 P_LAST_UPDATE_LOGIN,
605 'N',
606 'N',
607 P_CESS_AMOUNT,/*BUG *6030615*/
608 P_SH_CESS_AMOUNT/*Bug 5989740 bduvarag*/
609 );
610
611 /* Added by Ramananda for bug#4407165 */
612 EXCEPTION
613 WHEN OTHERS THEN
614 P_REGISTER_ID := null;
615 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
616 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
617 app_exception.raise_exception;
618
619 END create_rg1_entry;
620
621 END jai_cmn_rg_i_trxs_pkg;