[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_insert_flag NUMBER := 0;
ln_last_updated_by JAI_CMN_TRANSACTIONS_T.LAST_UPDATED_BY%TYPE;
ln_last_update_login JAI_CMN_TRANSACTIONS_T.LAST_UPDATE_LOGIN%TYPE;
SELECT location_id
FROM JAI_INV_SUBINV_DTLS
WHERE sub_inventory_name = p_subinv_code
AND organization_id = p_organization_id;
SELECT a.excise_flag, a.item_class, a.modvat_flag, a.item_trading_flag
FROM JAI_INV_ITM_SETUPS a
WHERE a.inventory_item_id = p_inv_item_id
AND a.organization_id = p_organization_id;
SELECT MAX (a.fin_year)
FROM JAI_CMN_FIN_YEARS a
WHERE fin_active_flag = 'Y' AND organization_id = p_org_id;
SELECT NVL (excise_duty_range, ' '), NVL (excise_duty_division, ' ')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id AND location_id = p_loc_id;
SELECT MAX (slno)
FROM JAI_CMN_RG_23AC_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_loc_id
AND inventory_item_id = p_inv_item_id
AND register_type = p_reg_type
AND fin_year = p_f_year;
SELECT MAX(slno)
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = pr_new.organization_id and
location_id = p_loc_id and
inventory_item_id = pr_new.inventory_item_id and
fin_year = p_f_year;
SELECT slno, balance_packed, balance_loose
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = pr_new.organization_id
AND location_id = p_loc_id
AND inventory_item_id = pr_new.inventory_item_id
AND fin_year = p_f_year
AND slno = (SELECT MAX (slno)
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = pr_new.organization_id
AND location_id = p_loc_id
AND inventory_item_id = pr_new.inventory_item_id
AND fin_year = p_f_year);
SELECT opening_balance_qty, closing_balance_qty
FROM JAI_CMN_RG_23AC_I_TRXS
WHERE slno = p_sr_no
AND organization_id = p_organization_id
AND location_id = p_loc_id
AND register_type = p_reg_type
AND fin_year = p_f_year
AND inventory_item_id = p_inv_item_id;
SELECT COUNT (*)
FROM JAI_CMN_TRANSACTIONS_T
WHERE transaction_set_id = pr_new.transaction_set_id;
SELECT subinventory_code, organization_id, inventory_item_id,
transaction_quantity, transaction_type_id, transaction_date,
transaction_uom, transaction_set_id,
transaction_id -- cbabu for Bug# 2480584
FROM JAI_CMN_TRANSACTIONS_T
WHERE transaction_set_id = pr_new.transaction_set_id;
SELECT bonded
FROM JAI_INV_SUBINV_DTLS
WHERE sub_inventory_name = p_subinv_code AND
organization_id = p_organization_id;
SELECT bonded, trading
FROM JAI_INV_SUBINV_DTLS
WHERE sub_inventory_name = p_subinv_code
AND organization_id = p_organization_id;
SELECT transaction_type_name
FROM mtl_transaction_types
WHERE transaction_source_type_id = v_transaction_type_id;
v_misc_recpt_rg_update NUMBER;
v_misc_issue_rg_update NUMBER;
SELECT transaction_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = v_transaction_type_name;
SELECT nvl(type_class,-1) projects_flag
FROM mtl_transaction_types
WHERE transaction_type_id = cp_transaction_type_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT uom_class
FROM mtl_units_of_measure
WHERE unit_of_measure = uom;
SELECT manufacturing, trading
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = pr_new.organization_id AND location_id = 0;
SELECT Currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_gl_set_of_bks_id;
When a subinventory transfer is made from Bonded to Nonbonded subinventory, then balances are updated wrongly
in RG1 register. The reason for problem: location_id used to fetch the serial number should be temp location id
instead of present transaction location id.
4 09/12/02 Nagaraj.s, Bug# 2649405, Version: 615.4
As per the functional requirement, is needed that for CCIN, CCEX
classes, in case of an Miscellaneous Receipt(RG Update). RG1 Register
is to be hit and in case of an Miscellaneous Issue(RG Update), it should
first hit to the tune of the Closing Quantity available in RG23 PART I
and the rest should be hit in RG 1 Register.
This has been taken care by commenting pieces of code with Issue and with
ITEM Class CCIN and replaced by an elsif clause which checks the balances
in RG23 PART I Register and hits RG23 PART I and the rest Quantity hits
RG1 Register.
5 31/12/02 cbabu for Bug# 2728521, Version: 615.5
Coding is done as per the functional inputs specified in the above bug for subinvetory transfer transaction
6 13/01/03 Nagaraj.s for Bug#2744695 Version : 615.6
Changed the Insert statement of JAI_CMN_RG_I_TRXS table from
--to_other_factory_n_pay_ed_qty to other_purpose_n_pay_ed_qty as per
the Functional Requirement.
and also previously for FGIN and CCIN class of Items, the v_trans_qty
was null which is changed to the Abs(v_new_trans_qty) for CCIN class.
7 03/04/03 Vijay Shankar for Bug# 2851028 Version : 615.7
Move Order Issue Transaction is taken care with this bug. If specified transcation is done, then it is routed to
execute the code corresponding to Misc. Issue (RG Update) transaction.
8 23/04/03 Vijay Shankar for Bug# 2915814, FileVersion : 615.8
Coding is done as per the functional inputs specified in Bug# 2649405 for 'WIP Issue' transaction.
i.e when a WIP Issue transaction is done for CCIN items, then it should hit RG23 Part I register first
and then if quantity is not available with RG23 Part I it should hit RG1
9. 03/12/2003 Ssumaith - Bug # 3258066 and 3258269 File Version 617.1
For Production Input and Production Receipt , The manufactured_qty and other_qty_n_pay_ed fields wee
not updated correctly.The requirement was that for Production Issue , the way it needed to be done was
Only Other_purpose_n_pay_ed column needed to be updated with the transaction Quantity.
For Production Receipt
Only Manufactured_loose_qty needed to be updated
These changes have been done by writing an If condition which selectively makes the variables Null
10. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.1
Check whether india localization is being used was done using a INR check in every trigger.
This check has now been moved into a new package and calls made to this package from this trigger
If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
Hence if this function returns FALSE , control should return.
11. 19-jan-2005 ssumaith - bug#4130515 file version 115.2
An exit condition was commented which was causing the control to go into an infinite loop.
This exit condition has been uncommented and it resolves the issue.
12 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
13. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
Removal of SQL LITERALs is done
14. 28-Nov-2005 Aiyer for bug# 4764510. File Version: 120.1
Added the who columns in the insert into statment for the table JAI_CMN_TRANSACTIONS_T.
Dependencies due to this change:-
None
15. 13-Feb-2007 bgowrava for forward porting bug#5275865 (11i bug#5217272) Version# 120.3
Issue : In case of a WIP Assebly transaction, data should be entered into
ja_in_rg_i as a "PR" type of transaction and not a "PI" type of transaction.
Fix : Moved the condition to check for Assembly Return under the "PR"
transaction type.
16 24-Apr-2007 cbabu for forward porting bug#6012567 (5765161) version#120.4
forward ported the Project Costing changes R12
17. 21-Oct-2008 CSahoo for bug#4541316, File Version 120.2.12000000.5
Added condition for Internal Sales Order transaction to hit RG-I
Internal Sales Order in mtl_material_transactions is defined as
(transaction_type_id =53, transaction_action_id =28, transaction_source_type_id = 8)
Future Dependencies For the release Of this Object:-
(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/
A datamodel change )
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
ja_in_tran_rg_entry_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 Ssumaith 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 Ssumaith
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/* --Ramananda for File.Sql.35 */
v_trans_uom_code := pr_new.transaction_uom;
ln_last_updated_by := fnd_global.user_id; /* added by aiyer for the bug 4764510 */
ln_created_by := ln_last_updated_by; /* added by aiyer for the bug 4764510 */
ln_last_update_login := fnd_global.login_id;/* added by aiyer for the bug 4764510 */
|| Added the who columns in the insert statement
*/
INSERT INTO JAI_CMN_TRANSACTIONS_T
( transaction_set_id ,
inventory_item_id ,
organization_id ,
subinventory_code ,
transaction_type_id ,
transaction_id ,
transaction_date ,
transaction_quantity ,
transaction_uom ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES ( pr_new.transaction_set_id ,
pr_new.inventory_item_id ,
pr_new.organization_id ,
pr_new.subinventory_code ,
pr_new.transaction_type_id ,
pr_new.transaction_id ,
pr_new.transaction_date ,
v_new_trans_qty ,
pr_new.transaction_uom ,
ln_created_by , /* Added by aiyer for the bug 4764510 */
sysdate , /* Added by aiyer for the bug 4764510 */
ln_last_updated_by , /* Added by aiyer for the bug 4764510 */
sysdate , /* Added by aiyer for the bug 4764510 */
ln_last_update_login
);
WHILE(TRUE) LOOP --To handle both entries with single insert
-- If the temp transaction which is in temp table has to go into RG, then the following condition is TRUE
IF v_ins_from_temp_flag = 1 THEN --aaa
v_iss_qty := ABS (v_temp_trans_qty);
pr_new.last_update_date,
pr_new.last_update_login,
pr_new.last_updated_by
);
v_insert_flag := 1;
IF v_insert_flag = 1 THEN --bbb
IF v_rg23_flag = 1 THEN
INSERT INTO JAI_CMN_RG_23AC_I_TRXS(
register_id, fin_year, slno, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, TRANSACTION_SOURCE_NUM, inventory_item_id,
organization_id, transaction_type, range_no, division_no, GOODS_ISSUE_ID_REF,
goods_issue_date, goods_issue_quantity, OTH_RECEIPT_ID_REF, oth_receipt_date, oth_receipt_quantity,
register_type, location_id, transaction_uom_code, transaction_date,
opening_balance_qty, closing_balance_qty, primary_uom_code
) VALUES (
JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno, pr_new.last_update_date, pr_new.last_updated_by,
SYSDATE, pr_new.created_by, pr_new.last_update_login, v_temp_trans_type_id, v_temp_inv_item_id,
v_temp_organization_id, v_t_type, v_temp_range_no, v_temp_division_no, v_iss_id,
v_iss_date, v_iss_qty, v_rec_id, v_rec_date, v_rec_qty,
v_reg_type, v_temp_loc_id, v_temp_trans_uom, TRUNC (v_temp_trans_date),
v_opening_qty, v_closing_qty, v_temp_pr_uom_code
);
INSERT INTO JAI_CMN_RG_I_TRXS(
register_id, fin_year, slno, manufactured_qty, manufactured_packed_qty,
manufactured_loose_qty, balance_packed, balance_loose,
to_other_factory_n_pay_ed_qty,
for_export_n_pay_ed_qty, other_purpose_n_pay_ed_qty, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, TRANSACTION_SOURCE_NUM,
inventory_item_id, organization_id, transaction_type, range_no,
division_no, location_id, transaction_uom_code, transaction_date,
primary_uom_code, REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
) VALUES (
JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno1, ABS(v_manu_qty),
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
NULL, -- abs(v_t_qty), commented by Sriram on 26-Dec-2001
NULL, ABS(v_trans_qty), pr_new.last_update_date, pr_new.last_updated_by,
SYSDATE, pr_new.created_by, pr_new.last_update_login, v_temp_trans_type_id,
v_temp_inv_item_id, v_temp_organization_id, v_t_type, v_temp_range_no,
v_temp_division_no, v_temp_loc_id, v_temp_trans_uom, TRUNC(v_temp_trans_date),
v_pr_uom_code, v_temp_transaction_id -- cbabu 25/07/02 for Bug#2480524
);
v_insert_flag := 0;
pr_new.last_update_date,
pr_new.last_update_login,
pr_new.last_updated_by
);
v_insert_flag := 1;
IF v_ins_from_temp_flag = 0 AND v_ins_from_curr_flag = 0 AND v_insert_flag = 0 THEN
DELETE FROM JAI_CMN_TRANSACTIONS_T WHERE transaction_set_id = pr_new.transaction_set_id;
/* 1. Misc Issue(RG Update).
2. Misc Receipt(RG Update).
3. Move Order Issue (added for Bug # 2851028)
*/
-- cbabu 25/07/02 for Bug#2480524, start
OPEN c_txn_type_id('Miscellaneous Issue(RG Update)');
FETCH c_txn_type_id INTO v_misc_issue_rg_update;
OPEN c_txn_type_id('Miscellaneous Recpt(RG Update)');
FETCH c_txn_type_id INTO v_misc_recpt_rg_update;
AND (pr_new.transaction_type_id = v_misc_issue_rg_update -- cbabu 25/07/02 for Bug#2480524
OR ln_projects_flag = 1 /* condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)*/
)
)
OR ( pr_new.transaction_action_id = 27
AND pr_new.transaction_source_type_id = 13
--AND pr_new.transaction_type_id = 94 -- cbabu 25/07/02 for Bug#2480524
AND (pr_new.transaction_type_id = v_misc_recpt_rg_update -- cbabu 25/07/02 for Bug#2480524
OR ln_projects_flag = 1 /* condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)*/
)
)
-- cbabu for Bug# 2851028, to handle Move Order Issue transaction
OR ( pr_new.transaction_action_id = 1
AND pr_new.transaction_source_type_id = 4
AND pr_new.transaction_type_id = 63
)
)
THEN
IF v_bonded_curr = 'N' AND v_trading_curr = 'N' THEN
RETURN;
AND (pr_new.transaction_type_id = v_misc_issue_rg_update -- cbabu 25/07/02 for Bug#2480524
OR ln_projects_flag = 1 /* condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)*/
)
)
-- cbabu for Bug# 2851028, to handle Move Order Issue transaction
OR ( pr_new.transaction_action_id = 1
AND pr_new.transaction_source_type_id = 4
AND pr_new.transaction_type_id = 63
)
THEN
v_t_type := 'I';
AND (pr_new.transaction_type_id = v_misc_recpt_rg_update -- cbabu 25/07/02 for Bug#2480524
OR ln_projects_flag = 1 /* condition added for PROJECTS COSTING IMPL Bug#6012567(5765161)*/
)
THEN
v_t_type := 'R';
pr_new.last_update_date,
pr_new.last_update_login,
pr_new.last_updated_by
);
INSERT INTO JAI_CMN_RG_23AC_I_TRXS
(register_id, fin_year, slno,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
TRANSACTION_SOURCE_NUM, inventory_item_id,
organization_id, transaction_type, range_no,
division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
goods_issue_quantity, OTH_RECEIPT_ID_REF,
oth_receipt_date, oth_receipt_quantity,
register_type, location_id, transaction_uom_code,
transaction_date, opening_balance_qty,
closing_balance_qty, primary_uom_code)
VALUES (JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
pr_new.created_by, pr_new.last_update_login,
pr_new.transaction_type_id, pr_new.inventory_item_id,
pr_new.organization_id, v_t_type, v_range_no,
v_division_no, v_iss_id, v_iss_date,
v_iss_qty, v_rec_id,
v_rec_date, v_rec_qty,
v_reg_type, v_loc_id, pr_new.transaction_uom,
TRUNC (pr_new.transaction_date), v_opening_qty,
v_closing_qty, v_pr_uom_code);
INSERT INTO JAI_CMN_RG_I_TRXS(
register_id, fin_year, slno, manufactured_qty,
manufactured_packed_qty, manufactured_loose_qty,
balance_packed, balance_loose,
to_other_factory_n_pay_ed_qty,
for_export_n_pay_ed_qty, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, TRANSACTION_SOURCE_NUM,
inventory_item_id, organization_id,
transaction_type, range_no, division_no, location_id,
transaction_uom_code, transaction_date,
primary_uom_code,
REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, ABS(v_manu_qty),
ABS(v_manu_pkd_qty), ABS(v_manu_loose_qty),
v_bal_packed, v_bal_loose,
ABS (v_trans_qty),
v_t_qty, pr_new.last_update_date,
pr_new.last_updated_by, SYSDATE, pr_new.created_by,
pr_new.last_update_login, pr_new.transaction_type_id,
pr_new.inventory_item_id, pr_new.organization_id,
v_t_type, v_range_no, v_division_no, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_pr_uom_code,
pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
);
INSERT INTO JAI_CMN_RG_23AC_I_TRXS
(register_id,
fin_year,
slno,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
TRANSACTION_SOURCE_NUM,
inventory_item_id,
organization_id,
transaction_type, range_no,
division_no,
GOODS_ISSUE_ID_REF,
goods_issue_date,
goods_issue_quantity,
OTH_RECEIPT_ID_REF,
oth_receipt_date,
oth_receipt_quantity,
register_type,
location_id,
transaction_uom_code,
transaction_date,
opening_balance_qty,
closing_balance_qty,
primary_uom_code)
VALUES (JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL,
v_f_year,
v_srno,
pr_new.last_update_date,
pr_new.last_updated_by,
SYSDATE,
pr_new.created_by,
pr_new.last_update_login,
pr_new.transaction_type_id,
pr_new.inventory_item_id,
pr_new.organization_id,
v_t_type,
v_range_no,
v_division_no,
v_iss_id,
v_iss_date,
v_hit_rg23_qty,
v_rec_id,
v_rec_date,
0,
v_reg_type,
v_loc_id,
pr_new.transaction_uom,
TRUNC (pr_new.transaction_date),
v_opening_qty,
NVL(v_opening_qty,0) -NVL(v_hit_rg23_qty,0),
v_pr_uom_code);
INSERT INTO JAI_CMN_RG_I_TRXS(
register_id, fin_year, slno, manufactured_qty,
manufactured_packed_qty, manufactured_loose_qty,
balance_packed, balance_loose,
--to_other_factory_n_pay_ed_qty, Commented by Nagaraj.s for Bug#2744695
other_purpose_n_pay_ed_qty, --Added by Nagaraj.s for Bug#2744695
for_export_n_pay_ed_qty, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, TRANSACTION_SOURCE_NUM,
inventory_item_id, organization_id,
transaction_type, range_no, division_no, location_id,
transaction_uom_code, transaction_date,
primary_uom_code,
REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno, ABS(v_manu_qty),
ABS(v_manu_pkd_qty), ABS(v_manu_loose_qty),
v_bal_packed, v_bal_loose,
ABS (v_hit_rg1_qty),
v_t_qty, pr_new.last_update_date,
pr_new.last_updated_by, SYSDATE, pr_new.created_by,
pr_new.last_update_login, pr_new.transaction_type_id,
pr_new.inventory_item_id, pr_new.organization_id,
v_t_type, v_range_no, v_division_no, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_pr_uom_code,
pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
);
INSERT INTO JAI_CMN_RG_23AC_I_TRXS (
register_id, fin_year, slno,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
TRANSACTION_SOURCE_NUM, inventory_item_id,
organization_id, transaction_type, range_no,
division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
goods_issue_quantity, OTH_RECEIPT_ID_REF, oth_receipt_date,
oth_receipt_quantity, register_type, location_id,
transaction_uom_code, transaction_date,
opening_balance_qty, closing_balance_qty,
primary_uom_code
) VALUES (
JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
pr_new.created_by, pr_new.last_update_login,
pr_new.transaction_type_id, pr_new.inventory_item_id,
pr_new.organization_id, v_t_type, v_range_no,
v_division_no, v_iss_id, v_iss_date,
v_iss_qty, v_rec_id, v_rec_date,
v_rec_qty, v_reg_type, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_opening_qty, v_closing_qty,
v_pr_uom_code
);
INSERT INTO JAI_CMN_RG_I_TRXS (
register_id, fin_year, slno,
manufactured_qty, manufactured_packed_qty,
manufactured_loose_qty, balance_packed, balance_loose,
-- other_purpose_n_pay_ed_qty, -- by sriram
to_other_factory_n_pay_ed_qty, other_purpose,
-- to_other_factory_n_pay_ed_qty, --bby sriram
other_purpose_n_pay_ed_qty, for_export_n_pay_ed_qty,
for_home_use_pay_ed_qty, --Added by Satya on 23-Oct-01
last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, TRANSACTION_SOURCE_NUM,
inventory_item_id, organization_id, transaction_type,
range_no, division_no, location_id,
transaction_uom_code, transaction_date,
primary_uom_code,
REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
v_manufactured_qty, v_manufactured_pack_qty,
v_manu_loose_qty, v_bal_packed, v_bal_loose, -- v_manu_loose_qty changed to null by sriram bug # 3258086on product management advice.
v_oth_pur_npe_qty, v_oth_purpose,
v_other_npe_qty, NULL, --earlier 'PI' corresponding field is number -- put v_manu_loose_qty instead of v_trans_qty - sriram
v_manu_home_qty, --Added by Satya on 23-Oct-01
pr_new.last_update_date,
pr_new.last_updated_by, SYSDATE, pr_new.created_by,
pr_new.last_update_login, pr_new.transaction_type_id,
pr_new.inventory_item_id, pr_new.organization_id, v_t_type,
v_range_no, v_division_no, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_pr_uom_code,
pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
);
INSERT INTO JAI_CMN_RG_23AC_I_TRXS (
register_id, fin_year, slno,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
TRANSACTION_SOURCE_NUM, inventory_item_id,
organization_id, transaction_type, range_no,
division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
goods_issue_quantity, OTH_RECEIPT_ID_REF, oth_receipt_date,
oth_receipt_quantity, register_type, location_id,
transaction_uom_code, transaction_date,
opening_balance_qty, closing_balance_qty,
primary_uom_code
) VALUES (
JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
pr_new.created_by, pr_new.last_update_login,
pr_new.transaction_type_id, pr_new.inventory_item_id,
pr_new.organization_id, v_t_type, v_range_no,
v_division_no, v_iss_id, v_iss_date,
v_iss_qty, null, null,
null, v_reg_type, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_opening_qty, v_closing_qty,
v_pr_uom_code
);
INSERT INTO JAI_CMN_RG_I_TRXS (
register_id, fin_year, slno,
manufactured_qty, manufactured_packed_qty,
manufactured_loose_qty, balance_packed, balance_loose,
-- other_purpose_n_pay_ed_qty, -- by sriram
to_other_factory_n_pay_ed_qty, other_purpose,
-- to_other_factory_n_pay_ed_qty, --bby sriram
other_purpose_n_pay_ed_qty, for_export_n_pay_ed_qty,
for_home_use_pay_ed_qty, --Added by Satya on 23-Oct-01
last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, TRANSACTION_SOURCE_NUM,
inventory_item_id, organization_id, transaction_type,
range_no, division_no, location_id,
transaction_uom_code, transaction_date,
primary_uom_code,
REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
v_manufactured_qty, v_manufactured_pack_qty,
Null, v_bal_packed, v_bal_loose, -- v_manu_loose_qty set to null by sriram - bug # 3258066 based on product management advice.
v_oth_pur_npe_qty, v_oth_purpose,
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.
v_manu_home_qty, --Added by Satya on 23-Oct-01
pr_new.last_update_date,
pr_new.last_updated_by, SYSDATE, pr_new.created_by,
pr_new.last_update_login, pr_new.transaction_type_id,
pr_new.inventory_item_id, pr_new.organization_id, v_t_type,
v_range_no, v_division_no, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_pr_uom_code,
pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
);
In this part if item class is in RMIN,RMEX,CCIN,CGEX then record will be inserted in
RG23 Part I with transaction type as Issue Adjustment 'IA' or Receipt Adjustemt 'RA'
based on adjusted quantity.
If adjusted quantity is Negative then transaction type is IA otherwise for positive
adjusted quantity the transaction type is RA.
In this part if item class is in FGIN, FGEX then record will be inserted in RG I
register with transaction type as Issue Adjustment 'IA' or Receipt Adjustemt 'RA'
based on adjusted quantity.
If adjusted quantity is Negative then transaction type is IA otherwise for positive
adjusted quantity the transaction type is RA.
*/
ELSIF ( ( pr_new.transaction_action_id = 29
AND pr_new.transaction_source_type_id = 13
)
OR ( pr_new.transaction_action_id = 4
AND pr_new.transaction_source_type_id = 9
)
OR ( pr_new.transaction_action_id = 29
AND pr_new.transaction_source_type_id = 1
)
OR --added for phase 2 enhancement 15/10/99 Gaurav
( pr_new.transaction_action_id = 29
AND pr_new.transaction_source_type_id = 7
)
OR --added for phase 2 enhancement 15/10/99 Gaurav
( pr_new.transaction_action_id = 8
AND pr_new.transaction_source_type_id = 10
)
)
THEN
IF v_bonded_curr = 'N' AND v_trading_curr = 'N'
THEN
RETURN;
pr_new.last_update_date,
pr_new.last_update_login,
pr_new.last_updated_by
);
INSERT INTO JAI_CMN_RG_23AC_I_TRXS
(register_id, fin_year, slno,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login,
TRANSACTION_SOURCE_NUM, inventory_item_id,
organization_id, transaction_type, range_no,
division_no, GOODS_ISSUE_ID_REF, goods_issue_date,
goods_issue_quantity, OTH_RECEIPT_ID_REF,
oth_receipt_date, oth_receipt_quantity,
register_type, location_id, transaction_uom_code,
transaction_date, opening_balance_qty,
closing_balance_qty, primary_uom_code)
VALUES (JAI_CMN_RG_23AC_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
pr_new.last_update_date, pr_new.last_updated_by, SYSDATE,
pr_new.created_by, pr_new.last_update_login,
pr_new.transaction_type_id, pr_new.inventory_item_id,
pr_new.organization_id, v_t_type, v_range_no,
v_division_no, v_iss_id, v_iss_date,
v_iss_qty, v_rec_id,
v_rec_date, v_rec_qty,
v_reg_type, v_loc_id, pr_new.transaction_uom,
TRUNC (pr_new.transaction_date), v_opening_qty,
v_closing_qty, v_pr_uom_code);
INSERT INTO JAI_CMN_RG_I_TRXS
(register_id, fin_year, slno,
manufactured_qty, manufactured_packed_qty,
manufactured_loose_qty, balance_packed,
balance_loose, to_other_factory_n_pay_ed_qty,
for_export_n_pay_ed_qty, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, TRANSACTION_SOURCE_NUM,
inventory_item_id, organization_id,
transaction_type, range_no, division_no, location_id,
transaction_uom_code, transaction_date,
primary_uom_code,
REF_DOC_NO -- cbabu 25/07/02 for Bug#2480524
) VALUES (JAI_CMN_RG_I_TRXS_S.NEXTVAL, v_f_year, v_srno,
v_manufactured_qty, v_manufactured_pack_qty,
v_manu_loose_qty, v_bal_packed,
v_bal_loose, v_trans_qty,
v_t_qty, pr_new.last_update_date,
pr_new.last_updated_by, SYSDATE, pr_new.created_by,
pr_new.last_update_login, pr_new.transaction_type_id,
pr_new.inventory_item_id, pr_new.organization_id,
v_t_type, v_range_no, v_division_no, v_loc_id,
pr_new.transaction_uom, TRUNC (pr_new.transaction_date),
v_pr_uom_code,
pr_new.transaction_id -- cbabu 25/07/02 for Bug#2480524
);