[Home] [Help]
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;
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). -------------------------------------------------------------------------------------------------------------*/
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 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,
V_MANUFACTURED_QTY,
V_MANUFACTURED_PACKED_QTY,
V_MANUFACTURED_LOOSE_QTY,
V_FOR_HOME_USE_PAY_ED_QTY,
V_FOR_HOME_USE_PAY_ED_VAL,
V_FOR_EXPORT_PAY_ED_QTY,
V_FOR_EXPORT_PAY_ED_VAL,
V_FOR_EXPORT_N_PAY_ED_QTY,
V_FOR_EXPORT_N_PAY_ED_VAL,
V_OTHER_PURPOSE,
V_TO_OTHER_FAC_N_PAY_ED_QTY,
V_TO_OTHER_FAC_N_PAY_ED_VAL,
V_OTHER_PURPOSE_N_PAY_ED_QTY,
V_OTHER_PURPOSE_N_PAY_ED_VAL,
V_OTHER_PURPOSE_PAY_ED_QTY,
V_OTHER_PURPOSE_PAY_ED_VAL,
V_PRIMARY_UOM_CODE,
P_TRANSACTION_UOM_CODE,
vBalancePacked,
vBalanceLoose,
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*/
);