The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT nvl(allow_negative_rg_flag,'N')
FROM jai_cmn_inventory_orgs
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
be populated into BALANCE_LOOSE column of the table and finally inserts data into JAI_CMN_RG_I_TRXS table.
Change History
~~~~~~~~~~~~~~
S.No DD/MM/YYYY Author and Details
---------------------------------------------------------------------------------------------------------------
1 30/04/2004 Nagaraj.s for Bug # 3535729 File Version : 619.1
In case of RECEIPTS, and Transaction Type ='CR' transaction_id=18 is set.
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
as required for CASE COMPLAINCE.
3. 14/07/2005 4485801 Brathod, File Version 117.1
Issue: Inventory Convergence Uptake for R12 Initiative
4. 16/04/2007 bduvarag for the Bug#5989740, file version 120.2
Forward porting the changes done in 11i bug#5907436
5. 16/08/2007 vkaranam for bug#6030615,File version 120.3
forward porting the changes done in 115 bug#2942973(Interorg).
6. 27-Nov-2009 Bug 9122545 File version 120.1.12000000.4 / 120.3.12010000.2 / 120.4
Checked the setup option to allow negative balance in quantity registers before
raising the error "Enough RG1 balance is not available to Issue the Goods".
7. 06/04/2010 Bug 9550254
The opening balance for the RG I has been derived from the previous
financial year closing balance, if no entries found for the current year.
8. 27-apr-2010 bug#9466919
issue :quantity in rg registers are not in sync with the inventory.
fix:
added the rounding precision of 5 to the quantity fields while inserting.
-------------------------------------------------------------------------------------------------------------*/
v_quantity := NVL(p_quantity, 0); -- File.Sql.35 by Brathod
SELECT max(slno) INTO vMaxSlno
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND fin_year = p_fin_year;
SELECT NVL(balance_packed,0), NVL(balance_loose,0) INTO vBalancePacked, vBalanceLoose
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
and location_id = p_location_id
and inventory_item_id = p_inventory_item_id
AND fin_year = p_fin_year
AND slno = vMaxSlno;
P_LAST_UPDATE_DATE IN DATE,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
P_CALLED_FROM IN VARCHAR2,
P_CESS_AMOUNT IN NUMBER DEFAULT NULL,/*Bug 2942973. To
resolve compilation error- bduvarag*/
P_SH_CESS_AMOUNT IN NUMBER DEFAULT NULL/*Bug 5989740 bduvarag*/
) IS
/* Added by Ramananda for bug#4407165 */
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_i_trxs_pkg.create_rg1_entry';
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT nvl(allow_negative_rg_flag,'N')
FROM jai_cmn_inventory_orgs
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
SELECT max(slno) INTO vMaxSlno
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND fin_year = p_fin_year;
SELECT NVL(balance_packed,0), NVL(balance_loose,0) INTO vBalancePacked, vBalanceLoose
FROM JAI_CMN_RG_I_TRXS
WHERE organization_id = p_organization_id
and location_id = p_location_id
and inventory_item_id = p_inventory_item_id
AND fin_year = p_fin_year
AND slno = vMaxSlno;
SELECT JAI_CMN_RG_I_TRXS_S.nextval INTO P_REGISTER_ID FROM DUAL;
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,
CESS_AMT,/*BUG *6030615*/
SH_CESS_AMT/*Bug 5989740 bduvarag*/
) VALUES (
P_REGISTER_ID,
P_REGISTER_ID_PART_II,
P_FIN_YEAR,
P_SLNO,
V_TRANSACTION_ID,
P_ORGANIZATION_ID,
P_LOCATION_ID,
P_TRANSACTION_DATE,
P_INVENTORY_ITEM_ID,
P_TRANSACTION_TYPE,
P_REF_DOC_ID,
round(V_MANUFACTURED_QTY,5),
round(V_MANUFACTURED_PACKED_QTY,5),
round(V_MANUFACTURED_LOOSE_QTY,5),
round(V_FOR_HOME_USE_PAY_ED_QTY,5),
V_FOR_HOME_USE_PAY_ED_VAL,
round(V_FOR_EXPORT_PAY_ED_QTY,5),
V_FOR_EXPORT_PAY_ED_VAL,
round(V_FOR_EXPORT_N_PAY_ED_QTY,5),
V_FOR_EXPORT_N_PAY_ED_VAL,
V_OTHER_PURPOSE,
round(V_TO_OTHER_FAC_N_PAY_ED_QTY,5),
V_TO_OTHER_FAC_N_PAY_ED_VAL,
round(V_OTHER_PURPOSE_N_PAY_ED_QTY,5),
V_OTHER_PURPOSE_N_PAY_ED_VAL,
round(V_OTHER_PURPOSE_PAY_ED_QTY,5),
V_OTHER_PURPOSE_PAY_ED_VAL,
V_PRIMARY_UOM_CODE,
P_TRANSACTION_UOM_CODE,
round(vBalancePacked,5),
round(vBalanceLoose,5),
P_ISSUE_TYPE,
P_EXCISE_DUTY_AMOUNT,
P_EXCISE_INVOICE_NUMBER,
P_EXCISE_INVOICE_DATE,
P_PAYMENT_REGISTER,
P_CHARGE_ACCOUNT_ID,
P_RANGE_NO,
P_DIVISION_NO,
P_REMARKS,
P_BASIC_ED,
P_ADDITIONAL_ED,
P_OTHER_ED,
P_EXCISE_DUTY_RATE,
P_VENDOR_ID,
P_VENDOR_SITE_ID,
P_CUSTOMER_ID,
P_CUSTOMER_SITE_ID,
P_CREATION_DATE,
P_CREATED_BY,
P_LAST_UPDATE_DATE,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN,
'N',
'N',
P_CESS_AMOUNT,/*BUG *6030615*/
P_SH_CESS_AMOUNT/*Bug 5989740 bduvarag*/
);