The following lines contain the word 'select', 'insert', 'update' or 'delete':
organization gets inserted with the creation_date as sysdate. But the RG23
part II report is filtering the rows based on creation date. Due to this
the report output becomes wrong.
Resolution : When populating jai_cmn_rg_23ac_ii_trxs and jai_cmn_rg_others
table for the master org, creation_date (and other who columns) are copied from
the child org record instead of using the session values.
Following procedures are modified:
1. consolidate_rg23_part_ii
2. insert_rg23_others
Feb 18, 2010 Bug 9382720
Added Additional CVD as it is not getting consolidated in the Master Org
Inserted Addl CVD into JAI_CMN_RG_23AC_II_TRXS and JAI_CMN_RG_23AC_I_TRXS
Apr 06, 2010 Bug 9550254
The opening balance for the RG23 Part I has been derived
from the previous financial year closing balance,
if no entries found for the current year.
--------------------------------------------------------------------------------------*/
PROCEDURE insert_rg23_others
(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_previous_serial_no IN JAI_CMN_RG_23AC_II_TRXS.slno%TYPE,
p_tax_type IN JAI_CMN_RG_OTHERS.tax_type%TYPE,
p_register_id IN JAI_CMN_RG_23AC_II_TRXS.register_id%TYPE)
AS
Cursor rg_others_cur( p_register_id IN Number, p_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE ) IS
Select *
from JAI_CMN_RG_OTHERS
where source_register_id = p_register_id
and source_type = 1
and tax_type = p_tax_type;
previous check in. This has been updated.
--------------------------------------------------------------------------------------------*/
-- Get the details of the existing record
OPEN rg_others_cur(p_register_id, p_tax_type);
insert into JAI_CMN_RG_OTHERS
(
rg_other_id,
source_type,
source_register,
source_register_id,
tax_type,
credit,
debit,
created_by,
creation_date,
last_updated_by,
last_update_date
)
values
(
-rg_others_rec.rg_other_id,
rg_others_rec.source_type,
rg_others_rec.source_register,
-rg_others_rec.source_register_id,
rg_others_rec.tax_type,
rg_others_rec.credit,
rg_others_rec.debit,
/*start changes for bug 6118417 (FP for bug 6112850)*/
rg_others_rec.created_by, --FND_GLOBAL.USER_ID,
rg_others_rec.creation_date, --SYSDATE,
rg_others_rec.last_updated_by, --FND_GLOBAL.USER_ID,
rg_others_rec.last_update_date --SYSDATE
/*end bug 6118417*/
);
ERRBUF := ' Error Encountered in - jai_cmn_rg_master_org_pkg.insert_rg23_others ' || substr(SQLERRM,1,1000);
END insert_rg23_others;
PROCEDURE insert_pla_others
(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_previous_serial_no IN JAI_CMN_RG_PLA_TRXS.slno%TYPE,
p_tax_type IN JAI_CMN_RG_OTHERS.tax_type%TYPE,
p_register_id IN JAI_CMN_RG_PLA_TRXS.register_id%TYPE)
AS
Cursor rg_others_cur( p_register_id IN Number, p_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE ) IS
Select * from JAI_CMN_RG_OTHERS
where source_register_id = p_register_id
and source_type = 2
and tax_type = p_tax_type;
insert into JAI_CMN_RG_OTHERS
(
rg_other_id,
source_type,
source_register,
source_register_id,
tax_type,
credit,
debit,
created_by,
creation_date,
last_updated_by,
last_update_date
)
values
(
-rg_others_rec.rg_other_id,
rg_others_rec.source_type,
rg_others_rec.source_register,
-rg_others_rec.source_register_id,
rg_others_rec.tax_type,
rg_others_rec.credit,
rg_others_rec.debit,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE
);
END insert_pla_others;
Select ec_code
from JAI_CMN_INVENTORY_ORGS
Where organization_id = p_organization_id
And location_id = nvl(p_location_id,0);
Select *
From JAI_CMN_RG_23AC_I_TRXS a
Where a.register_id = p_register_id;
Select register_id
From JAI_CMN_RG_23AC_I_TRXS a, JAI_CMN_INVENTORY_ORGS b
Where ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
And ( a.master_flag IS NULL OR a.master_flag = 'N') --rchandan for bug#4428980
And a.organization_id = b.organization_id
And a.location_id = b.location_id
And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2708516
And b.ec_code = p_ec_code
Order by a.Register_Id;
Select nvl(MAX(slno),0) , nvl(MAX(slno),0) + 1
From JAI_CMN_RG_23AC_I_TRXS
Where organization_id = p_organization_id
And location_id = nvl(p_location_id,0)
And inventory_item_id = p_inventory_item_id
And fin_year = p_fin_year
And register_type = p_register_type;
Select nvl(opening_balance_qty,0), nvl(closing_balance_qty,0)
From JAI_CMN_RG_23AC_I_TRXS
Where slno = p_previous_serial_no
And organization_id = p_organization_id
And location_id = nvl(p_location_id,0)
And register_type = p_register_type
And fin_year = p_fin_year
And inventory_item_id = p_inventory_item_id;
INSERT INTO JAI_CMN_RG_23AC_I_TRXS
(register_id,
fin_year,
slno,
TRANSACTION_SOURCE_NUM,
inventory_item_id,
organization_id,
quantity_received,
RECEIPT_REF,
transaction_type,
receipt_date,
range_no,
division_no,
po_header_id,
po_header_date,
po_line_id,
po_line_location_id,
vendor_id,
vendor_site_id,
customer_id,
customer_site_id,
GOODS_ISSUE_ID_REF,
goods_issue_date,
goods_issue_quantity,
SALES_INVOICE_NO,
sales_invoice_quantity,
EXCISE_INVOICE_NO,
excise_invoice_date,
OTH_RECEIPT_ID_REF,
oth_receipt_quantity,
oth_receipt_date,
register_type,
identification_no,
identification_mark,
brand_name,
date_of_verification,
date_of_installation,
date_of_commission,
REGISTER_ID_PART_II,
additional_cvd, -- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
place_of_install,
remarks,
location_id,
primary_uom_code,
transaction_uom_code,
transaction_date,
basic_ed,
other_ed,
additional_ed,
opening_balance_qty,
closing_balance_qty,
charge_account_id,
posted_flag,
master_flag,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
VALUES
(
-rg23_rec.register_id,
rg23_rec.fin_year,
v_serial_no,
rg23_rec.TRANSACTION_SOURCE_NUM,
rg23_rec.inventory_item_id,
p_organization_id,
rg23_rec.quantity_received,
rg23_rec.receipt_ref,
rg23_rec.transaction_type,
rg23_rec.receipt_date,
rg23_rec.range_no,
rg23_rec.division_no,
rg23_rec.po_header_id,
rg23_rec.po_header_date,
rg23_rec.po_line_id,
rg23_rec.po_line_location_id,
rg23_rec.vendor_id,
rg23_rec.vendor_site_id,
rg23_rec.customer_id,
rg23_rec.customer_site_id,
rg23_rec.goods_issue_id_ref,
rg23_rec.goods_issue_date,
rg23_rec.goods_issue_quantity,
rg23_rec.sales_invoice_no,
rg23_rec.sales_invoice_quantity,
rg23_rec.excise_invoice_no,
rg23_rec.excise_invoice_date,
rg23_rec.OTH_RECEIPT_ID_REF,
rg23_rec.oth_receipt_quantity,
rg23_rec.oth_receipt_date,
rg23_rec.register_type,
rg23_rec.identification_no,
rg23_rec.identification_mark,
rg23_rec.brand_name,
rg23_rec.date_of_verification,
rg23_rec.date_of_installation,
rg23_rec.date_of_commission,
-rg23_rec.REGISTER_ID_PART_II,
rg23_rec.additional_cvd, -- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
rg23_rec.place_of_install,
lv_remarks, --rchandan for bug#4428980
nvl(p_location_id,0),
rg23_rec.primary_uom_code,
rg23_rec.transaction_uom_code,
rg23_rec.transaction_date,
rg23_rec.basic_ed,
rg23_rec.other_ed,
rg23_rec.additional_ed,
v_opening_balance_qty, ----Changed by Nagaraj.s for Bug2708516 Previously : rg23_rec.opening_balance_qty
v_closing_balance_qty, --Changed by Nagaraj.s for Bug2708516 Previously : rg23_rec.closing_balance_qty
rg23_rec.charge_account_id,
'N',
'Y',
sysdate,
rg23_rec.created_by,
rg23_rec.last_update_login,
rg23_rec.last_update_date,
rg23_rec.last_updated_by);
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET posted_flag = 'Y',
master_flag = 'N'
WHERE register_id = rg23_rec.register_id;
Select ec_code
from JAI_CMN_INVENTORY_ORGS
Where organization_id = p_organization_id
And location_id = nvl(p_location_id,0);
Select *
From JAI_CMN_RG_23AC_II_TRXS a
Where a.register_id = p_register_id;
Select register_id
From JAI_CMN_RG_23AC_II_TRXS a, JAI_CMN_INVENTORY_ORGS b
Where ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
And ( a.master_flag IS NULL OR a.master_flag = 'N' ) --rchandan for bug#4428980
And a.organization_id = b.organization_id
And a.location_id = b.location_id
And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2636714
And b.ec_code = p_ec_code
Order by a.Register_Id;
Select nvl(MAX(slno),0) , nvl(MAX(slno),0) + 1
From JAI_CMN_RG_23AC_II_TRXS
Where organization_id = p_organization_id
And location_id = nvl(p_location_id,0)
And fin_year = p_fin_year
And register_type = p_register_type;
Select nvl(opening_balance,0), nvl(closing_balance,0)
From JAI_CMN_RG_23AC_II_TRXS
Where slno = p_previous_serial_no
And organization_id = p_organization_id
And location_id = nvl(p_location_id,0)
And register_type = p_register_type
And fin_year = p_fin_year ;
SELECT FIN_YEAR FROM
JAI_CMN_FIN_YEARS
WHERE ORGANIZATION_ID=p_organization_id
and FIN_ACTIVE_FLAG='Y';
SELECT NVL(CLOSING_BALANCE,0)
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND REGISTER_TYPE = P_REGISTER_TYPE
AND FIN_YEAR = P_FIN_YEAR
AND SLNO IN
(SELECT NVL(MAX(SLNO),0) FROM JAI_CMN_RG_23AC_II_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND FIN_YEAR = P_FIN_YEAR
AND REGISTER_TYPE =P_REGISTER_TYPE);
Procedure jai_cmn_rg_master_org_pkg.insert_rg23_others is called for each record that has been consolidated
with the master to consolidate the taxes for CESS.
Base Bug #4106633
3. 2005/01/28 Harshita.J - For Bug #410667 Version - 115.1
Base Bug has been changed.
Base Bug #4146708. This bug creates all the database objects.
--------------------------------------------------------------------------------------------*/
v_debug_flag := 'Y'; -- File.Sql.35 by Brathod
INSERT INTO JAI_CMN_RG_23AC_II_TRXS (register_id,
fin_year,
slno,
TRANSACTION_SOURCE_NUM,
inventory_item_id,
organization_id,
RECEIPT_REF,
receipt_date,
range_no,
division_no,
cr_basic_ed,
cr_additional_Ed,
cr_other_ed,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
excise_invoice_no,
excise_invoice_date,
register_type,
remarks,
vendor_id,
vendor_site_id,
customer_id,
customer_site_id,
location_id,
transaction_date,
opening_balance,
closing_balance,
charge_account_id,
register_id_part_i,
posted_flag,
master_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
other_tax_credit,
other_tax_debit,
-- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
additional_cvd_amt,
cr_additional_cvd,
dr_additional_cvd
)
VALUES
(
-rg23_rec.register_id,
rg23_rec.fin_year,
v_serial_no,
rg23_rec.transaction_source_num,
rg23_rec.inventory_item_id,
p_organization_id,
rg23_rec.receipt_ref,
rg23_rec.receipt_date,
rg23_rec.range_no,
rg23_rec.division_no,
rg23_rec.cr_basic_ed,
rg23_rec.cr_additional_Ed,
rg23_rec.cr_other_ed,
rg23_rec.dr_basic_ed,
rg23_rec.dr_additional_ed,
rg23_rec.dr_other_ed,
rg23_rec.excise_invoice_no,
rg23_rec.excise_invoice_date,
rg23_rec.register_type,
rg23_rec.remarks,
rg23_rec.vendor_id,
rg23_rec.vendor_site_id,
rg23_rec.customer_id,
rg23_rec.customer_site_id,
nvl(p_location_id,0),
rg23_rec.transaction_date,
v_opening_balance,
v_closing_balance,
rg23_rec.charge_account_id,
-rg23_rec.register_id_part_i,
'N',
'Y',
rg23_rec.creation_date, --sysdate,/*changed for bug 6118417 (FP for bug 6112850)*/
rg23_rec.created_by,
rg23_rec.last_update_date,
rg23_rec.last_updated_by,
rg23_rec.last_update_login,
rg23_rec.other_tax_credit,
rg23_rec.other_tax_debit,
-- Bug 9382720 - Added Additional CVD as it is not getting consolidated in the Master Org
rg23_rec.additional_cvd_amt,
rg23_rec.cr_additional_cvd,
rg23_rec.dr_additional_cvd );
FND_FILE.PUT_LINE(FND_FILE.LOG, '1.9 After Insert into JAI_CMN_RG_23AC_II_TRXS table');
UPDATE JAI_CMN_RG_23AC_II_TRXS
SET posted_flag = 'Y',
master_flag = 'N'
WHERE register_id = rg23_rec.register_id;
(select tax_type
from JAI_CMN_RG_OTHERS
where source_register_id = rg23_rec.register_id
and source_type = 1
)
LOOP
if v_debug_flag = 'Y' THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, '1.812 . Before calling other taxes ');
jai_cmn_rg_master_org_pkg.insert_rg23_others
(errbuf => lv_buffer,
retcode => lv_retcode,
p_previous_serial_no => v_previous_serial_no,
p_tax_type => tax_types_rec.tax_type,
p_register_id => rg23_rec.register_id ) ;
UPDATE JAI_CMN_RG_BALANCES
SET RG23A_BALANCE = v_rg23a_final_balance, RG23C_BALANCE=v_rg23c_final_balance
where organization_id=p_organization_id
and location_id =p_location_id;
Select ec_code
from JAI_CMN_INVENTORY_ORGS
Where organization_id = p_organization_id
And location_id = nvl(p_location_id,0);
Select *
From JAI_CMN_RG_PLA_TRXS a
Where a.register_id = p_register_id;
Select register_id
From JAI_CMN_RG_PLA_TRXS a, JAI_CMN_INVENTORY_ORGS b
Where ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
And ( a.master_flag IS NULL OR a.master_flag = 'N' ) --rchandan for bug#4428980
And a.organization_id = b.organization_id
And a.location_id = b.location_id
And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2708514
And b.ec_code = p_ec_code
Order by a.Register_Id;
Select nvl(MAX(slno),0) , nvl(MAX(slno),0) + 1
From JAI_CMN_RG_PLA_TRXS
Where organization_id = p_organization_id
And location_id = nvl(p_location_id,0)
And fin_year = p_fin_year ;
Select nvl(opening_balance,0), nvl(closing_balance,0)
From JAI_CMN_RG_PLA_TRXS
Where slno = p_previous_serial_no
And organization_id = p_organization_id
And location_id = nvl(p_location_id,0)
-- And register_type = p_register_type
And fin_year = p_fin_year;
SELECT FIN_YEAR FROM
JAI_CMN_FIN_YEARS
WHERE ORGANIZATION_ID=p_organization_id
and FIN_ACTIVE_FLAG='Y';
SELECT NVL(CLOSING_BALANCE,0)
FROM JAI_CMN_RG_PLA_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND FIN_YEAR = P_FIN_YEAR
AND SLNO IN
(SELECT NVL(MAX(SLNO),0) FROM JAI_CMN_RG_PLA_TRXS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND FIN_YEAR = P_FIN_YEAR
);
Procedure jai_cmn_rg_master_org_pkg.insert_rg23_others is called for each record that has been consolidated
with the master to consolidate the taxes for CESS.
Base Bug #4106633
3. 2005/01/28 Harshita.J - For Bug #410667 Version - 115.1
Base Bug has been changed.
Base Bug #4146708. This bug creates all the database objects.
--------------------------------------------------------------------------------------------*/
v_debug_flag :='Y'; -- File.Sql.35 by Brathod
INSERT INTO JAI_CMN_RG_PLA_TRXS (register_id,
fin_year,
slno,
tr6_challan_no,
tr6_challan_date,
cr_basic_ed,
cr_additional_ed,
cr_other_ed,
TRANSACTION_SOURCE_NUM,
ref_document_id,
ref_document_date,
DR_INVOICE_NO,
dr_invoice_date,
dr_basic_ed,
dr_additional_ed,
dr_other_ed,
organization_id,
location_id,
bank_branch_id,
entry_date,
inventory_item_id,
vendor_cust_flag,
vendor_id,
vendor_site_id,
range_no,
division_no,
excise_invoice_no,
remarks,
transaction_date,
opening_balance,
closing_balance,
charge_account_id,
posted_flag,
master_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
other_tax_credit,
other_tax_debit)
VALUES( -1 * pla_rec.register_id,
pla_rec.fin_year,
v_serial_no,
pla_rec.tr6_challan_no,
pla_rec.tr6_challan_date,
pla_rec.cr_basic_ed,
pla_rec.cr_additional_ed,
pla_rec.cr_other_ed,
pla_rec.TRANSACTION_SOURCE_NUM,
pla_rec.ref_document_id,
pla_rec.ref_document_date,
pla_rec.DR_INVOICE_NO,
pla_rec.dr_invoice_date,
pla_rec.dr_basic_ed,
pla_rec.dr_additional_ed,
pla_rec.dr_other_ed,
p_organization_id,
nvl(p_location_id,0),
pla_rec.bank_branch_id,
pla_rec.entry_date,
pla_rec.inventory_item_id,
pla_rec.vendor_cust_flag,
pla_rec.vendor_id,
pla_rec.vendor_site_id,
pla_rec.range_no,
pla_rec.division_no,
pla_rec.excise_invoice_no,
pla_rec.remarks,
pla_rec.transaction_date,
v_opening_balance,
v_closing_balance,
pla_rec.charge_account_id,
'N', --posted_flag,
'Y', --master_flag,
pla_rec.creation_date,
pla_rec.created_by,
pla_rec.last_update_date,
pla_rec.last_updated_by,
pla_rec.last_update_login,
pla_rec.other_tax_credit,
pla_rec.other_tax_debit);
UPDATE JAI_CMN_RG_PLA_TRXS
SET posted_flag = 'Y',
master_flag = 'N'
WHERE register_id = pla_rec.register_id;
( select tax_type
from JAI_CMN_RG_OTHERS
where
source_register_id = pla_rec.register_id
and source_type = 2
)
LOOP
jai_cmn_rg_master_org_pkg.insert_pla_others
( errbuf => lv_buffer,
retcode => lv_retcode,
p_previous_serial_no => v_previous_serial_no,
p_tax_type => tax_types_rec.tax_type,
p_register_id => pla_rec.register_id
);
FND_FILE.PUT_LINE(FND_FILE.LOG, '1.83 The pla balance that is updated in JAI_CMN_RG_BALANCES is ' || v_pla_balance);
UPDATE JAI_CMN_RG_BALANCES
SET PLA_BALANCE = v_pla_balance
where organization_id=p_organization_id
and location_id =p_location_id;
SELECT ec_code
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = nvl(p_location_id,0);
SELECT register_id
FROM JAI_CMN_RG_I_TRXS a, JAI_CMN_INVENTORY_ORGS b
WHERE ( a.posted_flag IS NULL OR a.posted_flag = 'N' ) --rchandan for bug#4428980
AND ( a.master_flag IS NULL OR a.master_flag = 'N' ) --rchandan for bug#4428980
AND a.organization_id = b.organization_id
AND a.location_id = b.location_id
And b.master_org_flag ='N' --Changed by Nagaraj.s for Bug2708518
AND b.ec_code = p_ec_code
ORDER BY a.inventory_item_id, a.Register_Id;
SELECT *
FROM JAI_CMN_RG_I_TRXS a
WHERE a.register_id = p_register_id;
SELECT NVL(balance_packed, 0), NVL(balance_loose, 0)
, nvl(manufactured_qty, 0) -- Vijay Shankar for Bug# 3165687
FROM JAI_CMN_RG_I_TRXS
WHERE slno = p_serial_no
AND organization_id = p_organization_id
AND location_id = nvl(p_location_id,0)
AND fin_year = p_fin_year
AND inventory_item_id = p_inventory_item_id;
SELECT max(fin_year)
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = nvl(p_location_id,0)
AND inventory_item_id = p_inventory_item_id;
SELECT nvl(max(slno), 0)
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = nvl(p_location_id,0)
AND inventory_item_id = p_inventory_item_id
AND fin_year = p_fin_year;
INSERT INTO JAI_CMN_RG_I_TRXS ( register_id,
register_id_part_ii,
fin_year,
slno,
TRANSACTION_SOURCE_NUM,
organization_id,
location_id,
transaction_date,
inventory_item_id,
transaction_type,
REF_DOC_NO,
manufactured_qty,
manufactured_packed_qty,
manufactured_loose_qty,
for_home_use_pay_ed_qty,
for_home_use_pay_ed_val,
for_export_pay_ed_qty,
for_export_pay_ed_val,
for_export_n_pay_ed_qty,
for_export_n_pay_ed_val,
other_purpose,
to_other_factory_n_pay_ed_qty,
to_other_factory_n_pay_ed_val,
other_purpose_n_pay_ed_qty,
other_purpose_n_pay_ed_val,
other_purpose_pay_ed_qty,
other_purpose_pay_ed_val,
primary_uom_code,
transaction_uom_code,
balance_packed,
balance_loose,
issue_type,
excise_duty_amount,
excise_invoice_number,
excise_invoice_date,
payment_register,
charge_account_id,
range_no,
division_no,
remarks,
basic_ed,
additional_ed,
other_ed,
excise_duty_rate,
vendor_id,
vendor_site_id,
customer_id,
customer_site_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
posted_flag,
master_flag )
VALUES( -1 * rgi_rec.register_id,
-1 * rgi_rec.register_id_part_ii,
rgi_rec.fin_year,
v_serial_no,
rgi_rec.transaction_source_num,
p_organization_id,
nvl(p_location_id,0),
rgi_rec.transaction_date,
rgi_rec.inventory_item_id,
rgi_rec.transaction_type,
rgi_rec.REF_DOC_NO,
-- Modified by Vijay Shankar for Bug# 3165687
v_manu_qty, -- rgi_rec.manufactured_qty,
rgi_rec.manufactured_packed_qty,
rgi_rec.manufactured_loose_qty,
rgi_rec.for_home_use_pay_ed_qty,
rgi_rec.for_home_use_pay_ed_val,
rgi_rec.for_export_pay_ed_qty,
rgi_rec.for_export_pay_ed_val,
rgi_rec.for_export_n_pay_ed_qty,
rgi_rec.for_export_n_pay_ed_val,
rgi_rec.other_purpose,
rgi_rec.to_other_factory_n_pay_ed_qty,
rgi_rec.to_other_factory_n_pay_ed_val,
rgi_rec.other_purpose_n_pay_ed_qty,
rgi_rec.other_purpose_n_pay_ed_val,
rgi_rec.other_purpose_pay_ed_qty,
rgi_rec.other_purpose_pay_ed_val,
rgi_rec.primary_uom_code,
rgi_rec.transaction_uom_code,
nvl(v_balance_packed,0),--nvl(rgi_rec.balance_packed,0),--By Nagaraj.s for Bug2708518
nvl(v_balance_loose,0), --nvl(rgi_rec.balance_loose,0),--By Nagaraj.s for Bug2708518
rgi_rec.issue_type,
rgi_rec.excise_duty_amount,
rgi_rec.excise_invoice_number,
rgi_rec.excise_invoice_date,
rgi_rec.payment_register,
rgi_rec.charge_account_id,
rgi_rec.range_no,
rgi_rec.division_no,
rgi_rec.remarks,
rgi_rec.basic_ed,
rgi_rec.additional_ed,
rgi_rec.other_ed,
rgi_rec.excise_duty_rate,
rgi_rec.vendor_id,
rgi_rec.vendor_site_id,
rgi_rec.customer_id,
rgi_rec.customer_site_id,
rgi_rec.creation_date,
rgi_rec.created_by,
rgi_rec.last_update_date,
rgi_rec.last_updated_by,
rgi_rec.last_update_login,
'N',
'Y' );
UPDATE JAI_CMN_RG_I_TRXS
SET posted_flag = 'Y',
master_flag = 'N'
WHERE register_id = rgi_rec.register_id;