[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row(
P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_23D_TRXS.register_id%TYPE,
P_ORGANIZATION_ID IN JAI_CMN_RG_23D_TRXS.organization_id%TYPE,
P_LOCATION_ID IN JAI_CMN_RG_23D_TRXS.location_id%TYPE,
P_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.transaction_type%TYPE,
P_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.RECEIPT_REF%TYPE,
P_QUANTITY_RECEIVED IN JAI_CMN_RG_23D_TRXS.quantity_received%TYPE,
P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23D_TRXS.inventory_item_id%TYPE,
P_SUBINVENTORY IN JAI_CMN_RG_23D_TRXS.subinventory%TYPE,
P_REFERENCE_LINE_ID IN JAI_CMN_RG_23D_TRXS.reference_line_id%TYPE,
P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23D_TRXS.transaction_uom_code%TYPE,
P_CUSTOMER_ID IN JAI_CMN_RG_23D_TRXS.customer_id%TYPE,
P_BILL_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.bill_to_site_id%TYPE,
P_SHIP_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.ship_to_site_id%TYPE,
P_QUANTITY_ISSUED IN JAI_CMN_RG_23D_TRXS.quantity_issued%TYPE,
P_REGISTER_CODE IN JAI_CMN_RG_23D_TRXS.register_code%TYPE,
P_RELEASED_DATE IN JAI_CMN_RG_23D_TRXS.released_date%TYPE,
P_COMM_INVOICE_NO IN JAI_CMN_RG_23D_TRXS.comm_invoice_no%TYPE,
P_COMM_INVOICE_DATE IN JAI_CMN_RG_23D_TRXS.comm_invoice_date%TYPE,
P_RECEIPT_BOE_NUM IN JAI_CMN_RG_23D_TRXS.receipt_boe_num%TYPE,
P_OTH_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.OTH_RECEIPT_ID_REF%TYPE,
P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.oth_receipt_date%TYPE,
P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23D_TRXS.oth_receipt_quantity%TYPE,
P_REMARKS IN JAI_CMN_RG_23D_TRXS.remarks%TYPE,
P_QTY_TO_ADJUST IN JAI_CMN_RG_23D_TRXS.qty_to_adjust%TYPE,
P_RATE_PER_UNIT IN JAI_CMN_RG_23D_TRXS.rate_per_unit%TYPE,
P_EXCISE_DUTY_RATE IN JAI_CMN_RG_23D_TRXS.excise_duty_rate%TYPE,
P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23D_TRXS.charge_account_id%TYPE,
P_DUTY_AMOUNT IN JAI_CMN_RG_23D_TRXS.duty_amount%TYPE,
P_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.receipt_date%TYPE,
P_GOODS_ISSUE_ID IN JAI_CMN_RG_23D_TRXS.goods_issue_id%TYPE,
P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23D_TRXS.goods_issue_date%TYPE,
P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23D_TRXS.goods_issue_quantity%TYPE,
P_TRANSACTION_DATE IN JAI_CMN_RG_23D_TRXS.transaction_date%TYPE,
P_BASIC_ED IN JAI_CMN_RG_23D_TRXS.basic_ed%TYPE,
P_ADDITIONAL_ED IN JAI_CMN_RG_23D_TRXS.additional_ed%TYPE,
P_ADDITIONAL_CVD IN JAI_CMN_RG_23D_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
P_OTHER_ED IN JAI_CMN_RG_23D_TRXS.other_ed%TYPE,
P_CVD IN JAI_CMN_RG_23D_TRXS.cvd%TYPE,
P_VENDOR_ID IN JAI_CMN_RG_23D_TRXS.vendor_id%TYPE,
P_VENDOR_SITE_ID IN JAI_CMN_RG_23D_TRXS.vendor_site_id%TYPE,
P_RECEIPT_NUM IN JAI_CMN_RG_23D_TRXS.receipt_num%TYPE,
P_ATTRIBUTE1 IN JAI_CMN_RG_23D_TRXS.attribute1%TYPE,
P_ATTRIBUTE2 IN JAI_CMN_RG_23D_TRXS.attribute2%TYPE,
P_ATTRIBUTE3 IN JAI_CMN_RG_23D_TRXS.attribute3%TYPE,
P_ATTRIBUTE4 IN JAI_CMN_RG_23D_TRXS.attribute4%TYPE,
P_ATTRIBUTE5 IN JAI_CMN_RG_23D_TRXS.attribute5%TYPE,
P_CONSIGNEE IN JAI_CMN_RG_23D_TRXS.consignee%TYPE,
P_MANUFACTURER_NAME IN JAI_CMN_RG_23D_TRXS.manufacturer_name%TYPE,
P_MANUFACTURER_ADDRESS IN JAI_CMN_RG_23D_TRXS.manufacturer_address%TYPE,
P_MANUFACTURER_RATE_AMT_PER_UN IN JAI_CMN_RG_23D_TRXS.manufacturer_rate_amt_per_unit%TYPE,
P_QTY_RECEIVED_FROM_MANUFACTUR IN JAI_CMN_RG_23D_TRXS.qty_received_from_manufacturer%TYPE,
P_TOT_AMT_PAID_TO_MANUFACTURER IN JAI_CMN_RG_23D_TRXS.tot_amt_paid_to_manufacturer%TYPE,
-- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23D_TRXS.other_tax_credit%TYPE,
P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23D_TRXS.other_tax_debit%TYPE,
P_TRANSACTION_SOURCE IN VARCHAR2,
P_CALLED_FROM IN VARCHAR2,
P_SIMULATE_FLAG IN VARCHAR2,
P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
) IS
ld_creation_date DATE;
ld_last_update_date DATE;
ln_last_updated_by NUMBER(15);
ln_last_update_login NUMBER(15);
Modified Insert and Update procedures to include p_other_tax_credit and p_other_tax_debit parameters for
Education Cess Enhancement
Dependancy:
-----------
IN60105D2 + 3496408
IN60106 + 3940588
----------------------------------------------------------------------------------------------------------------------------*/
ld_creation_date := SYSDATE;
ld_last_update_date := SYSDATE;
ln_last_updated_by := ln_created_by;
ln_last_update_login := FND_GLOBAL.login_id;
INSERT INTO JAI_CMN_RG_23D_TRXS(
REGISTER_ID,
ORGANIZATION_ID,
LOCATION_ID,
SLNO,
FIN_YEAR,
TRANSACTION_TYPE,
RECEIPT_REF,
QUANTITY_RECEIVED,
INVENTORY_ITEM_ID,
SUBINVENTORY,
REFERENCE_LINE_ID,
PRIMARY_UOM_CODE,
TRANSACTION_UOM_CODE,
CUSTOMER_ID,
BILL_TO_SITE_ID,
SHIP_TO_SITE_ID,
QUANTITY_ISSUED,
REGISTER_CODE,
RELEASED_DATE,
COMM_INVOICE_NO,
COMM_INVOICE_DATE,
RECEIPT_BOE_NUM,
OTH_RECEIPT_ID_REF,
OTH_RECEIPT_DATE,
OTH_RECEIPT_QUANTITY,
REMARKS,
QTY_TO_ADJUST,
RATE_PER_UNIT,
EXCISE_DUTY_RATE,
CHARGE_ACCOUNT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
DUTY_AMOUNT,
TRANSACTION_SOURCE_NUM,
RECEIPT_DATE,
GOODS_ISSUE_ID,
GOODS_ISSUE_DATE,
GOODS_ISSUE_QUANTITY,
TRANSACTION_DATE,
OPENING_BALANCE_QTY,
CLOSING_BALANCE_QTY,
BASIC_ED,
ADDITIONAL_ED,
ADDITIONAL_CVD,
OTHER_ED,
CVD,
VENDOR_ID,
VENDOR_SITE_ID,
RECEIPT_NUM,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
CONSIGNEE,
MANUFACTURER_NAME,
MANUFACTURER_ADDRESS,
MANUFACTURER_RATE_AMT_PER_UNIT,
QTY_RECEIVED_FROM_MANUFACTURER,
TOT_AMT_PAID_TO_MANUFACTURER,
-- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
other_tax_credit,
other_tax_debit
) VALUES (
JAI_CMN_RG_23D_TRXS_S.nextval,
P_ORGANIZATION_ID,
P_LOCATION_ID,
ln_slno, --
ln_fin_year, --
lv_transaction_type, --
P_RECEIPT_ID,
ln_quantity, -- P_QUANTITY_RECEIVED,
P_INVENTORY_ITEM_ID,
P_SUBINVENTORY,
P_REFERENCE_LINE_ID,
lv_primary_uom_code, ---
P_TRANSACTION_UOM_CODE,
P_CUSTOMER_ID,
P_BILL_TO_SITE_ID,
P_SHIP_TO_SITE_ID,
ln_quantity_issued, -- P_QUANTITY_ISSUED,
P_REGISTER_CODE,
P_RELEASED_DATE,
P_COMM_INVOICE_NO,
P_COMM_INVOICE_DATE,
P_RECEIPT_BOE_NUM,
P_OTH_RECEIPT_ID,
P_OTH_RECEIPT_DATE,
P_OTH_RECEIPT_QUANTITY,
P_REMARKS,
ln_qty_to_adjust, -- P_QTY_TO_ADJUST,
P_RATE_PER_UNIT,
P_EXCISE_DUTY_RATE,
P_CHARGE_ACCOUNT_ID,
ld_creation_date, --
ln_created_by, --
ld_last_update_date, --
ln_last_update_login, --
ln_last_updated_by, ---
P_DUTY_AMOUNT,
ln_transaction_id, -- P_TRANSACTION_ID,
P_RECEIPT_DATE,
P_GOODS_ISSUE_ID,
P_GOODS_ISSUE_DATE,
P_GOODS_ISSUE_QUANTITY,
P_TRANSACTION_DATE,
ln_opening_balance_qty, --P_OPENING_BALANCE_QTY,
ln_closing_balance_qty, --P_CLOSING_BALANCE_QTY,
P_BASIC_ED,
P_ADDITIONAL_ED,
P_ADDITIONAL_CVD,
P_OTHER_ED,
P_CVD,
P_VENDOR_ID,
P_VENDOR_SITE_ID,
P_RECEIPT_NUM,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4,
P_ATTRIBUTE5,
P_CONSIGNEE,
P_MANUFACTURER_NAME,
P_MANUFACTURER_ADDRESS,
P_MANUFACTURER_RATE_AMT_PER_UN,
P_QTY_RECEIVED_FROM_MANUFACTUR,
P_TOT_AMT_PAID_TO_MANUFACTURER,
-- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
p_other_tax_credit,
p_other_tax_debit
) RETURNING register_id INTO P_REGISTER_ID;
p_process_message := 'RG23_D_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
END insert_row;
PROCEDURE update_row(
P_REGISTER_ID IN JAI_CMN_RG_23D_TRXS.register_id%TYPE DEFAULT NULL,
P_ORGANIZATION_ID IN JAI_CMN_RG_23D_TRXS.organization_id%TYPE DEFAULT NULL,
P_LOCATION_ID IN JAI_CMN_RG_23D_TRXS.location_id%TYPE DEFAULT NULL,
P_SLNO IN JAI_CMN_RG_23D_TRXS.slno%TYPE DEFAULT NULL,
P_FIN_YEAR IN JAI_CMN_RG_23D_TRXS.fin_year%TYPE DEFAULT NULL,
P_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.transaction_type%TYPE DEFAULT NULL,
P_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.RECEIPT_REF%TYPE DEFAULT NULL,
P_QUANTITY_RECEIVED IN JAI_CMN_RG_23D_TRXS.quantity_received%TYPE DEFAULT NULL,
P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23D_TRXS.inventory_item_id%TYPE DEFAULT NULL,
P_SUBINVENTORY IN JAI_CMN_RG_23D_TRXS.subinventory%TYPE DEFAULT NULL,
P_REFERENCE_LINE_ID IN JAI_CMN_RG_23D_TRXS.reference_line_id%TYPE DEFAULT NULL,
P_PRIMARY_UOM_CODE IN JAI_CMN_RG_23D_TRXS.primary_uom_code%TYPE DEFAULT NULL,
P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23D_TRXS.transaction_uom_code%TYPE DEFAULT NULL,
P_CUSTOMER_ID IN JAI_CMN_RG_23D_TRXS.customer_id%TYPE DEFAULT NULL,
P_BILL_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.bill_to_site_id%TYPE DEFAULT NULL,
P_SHIP_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.ship_to_site_id%TYPE DEFAULT NULL,
P_QUANTITY_ISSUED IN JAI_CMN_RG_23D_TRXS.quantity_issued%TYPE DEFAULT NULL,
P_REGISTER_CODE IN JAI_CMN_RG_23D_TRXS.register_code%TYPE DEFAULT NULL,
P_RELEASED_DATE IN JAI_CMN_RG_23D_TRXS.released_date%TYPE DEFAULT NULL,
P_COMM_INVOICE_NO IN JAI_CMN_RG_23D_TRXS.comm_invoice_no%TYPE DEFAULT NULL,
P_COMM_INVOICE_DATE IN JAI_CMN_RG_23D_TRXS.comm_invoice_date%TYPE DEFAULT NULL,
P_RECEIPT_BOE_NUM IN JAI_CMN_RG_23D_TRXS.receipt_boe_num%TYPE DEFAULT NULL,
P_OTH_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.OTH_RECEIPT_ID_REF%TYPE DEFAULT NULL,
P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.oth_receipt_date%TYPE DEFAULT NULL,
P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23D_TRXS.oth_receipt_quantity%TYPE DEFAULT NULL,
P_REMARKS IN JAI_CMN_RG_23D_TRXS.remarks%TYPE DEFAULT NULL,
P_QTY_TO_ADJUST IN JAI_CMN_RG_23D_TRXS.qty_to_adjust%TYPE DEFAULT NULL,
P_RATE_PER_UNIT IN JAI_CMN_RG_23D_TRXS.rate_per_unit%TYPE DEFAULT NULL,
P_EXCISE_DUTY_RATE IN JAI_CMN_RG_23D_TRXS.excise_duty_rate%TYPE DEFAULT NULL,
P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23D_TRXS.charge_account_id%TYPE DEFAULT NULL,
P_DUTY_AMOUNT IN JAI_CMN_RG_23D_TRXS.duty_amount%TYPE DEFAULT NULL,
P_TRANSACTION_ID IN JAI_CMN_RG_23D_TRXS.TRANSACTION_SOURCE_NUM%TYPE DEFAULT NULL,
P_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.receipt_date%TYPE DEFAULT NULL,
P_GOODS_ISSUE_ID IN JAI_CMN_RG_23D_TRXS.goods_issue_id%TYPE DEFAULT NULL,
P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23D_TRXS.goods_issue_date%TYPE DEFAULT NULL,
P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23D_TRXS.goods_issue_quantity%TYPE DEFAULT NULL,
P_TRANSACTION_DATE IN JAI_CMN_RG_23D_TRXS.transaction_date%TYPE DEFAULT NULL,
P_OPENING_BALANCE_QTY IN JAI_CMN_RG_23D_TRXS.opening_balance_qty%TYPE DEFAULT NULL,
P_CLOSING_BALANCE_QTY IN JAI_CMN_RG_23D_TRXS.closing_balance_qty%TYPE DEFAULT NULL,
P_BASIC_ED IN JAI_CMN_RG_23D_TRXS.basic_ed%TYPE DEFAULT NULL,
P_ADDITIONAL_ED IN JAI_CMN_RG_23D_TRXS.additional_ed%TYPE DEFAULT NULL,
P_ADDITIONAL_CVD IN JAI_CMN_RG_23D_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
P_OTHER_ED IN JAI_CMN_RG_23D_TRXS.other_ed%TYPE DEFAULT NULL,
P_CVD IN JAI_CMN_RG_23D_TRXS.cvd%TYPE DEFAULT NULL,
P_VENDOR_ID IN JAI_CMN_RG_23D_TRXS.vendor_id%TYPE DEFAULT NULL,
P_VENDOR_SITE_ID IN JAI_CMN_RG_23D_TRXS.vendor_site_id%TYPE DEFAULT NULL,
P_RECEIPT_NUM IN JAI_CMN_RG_23D_TRXS.receipt_num%TYPE DEFAULT NULL,
P_ATTRIBUTE1 IN JAI_CMN_RG_23D_TRXS.attribute1%TYPE DEFAULT NULL,
P_ATTRIBUTE2 IN JAI_CMN_RG_23D_TRXS.attribute2%TYPE DEFAULT NULL,
P_ATTRIBUTE3 IN JAI_CMN_RG_23D_TRXS.attribute3%TYPE DEFAULT NULL,
P_ATTRIBUTE4 IN JAI_CMN_RG_23D_TRXS.attribute4%TYPE DEFAULT NULL,
P_ATTRIBUTE5 IN JAI_CMN_RG_23D_TRXS.attribute5%TYPE DEFAULT NULL,
P_CONSIGNEE IN JAI_CMN_RG_23D_TRXS.consignee%TYPE DEFAULT NULL,
P_MANUFACTURER_NAME IN JAI_CMN_RG_23D_TRXS.manufacturer_name%TYPE DEFAULT NULL,
P_MANUFACTURER_ADDRESS IN JAI_CMN_RG_23D_TRXS.manufacturer_address%TYPE DEFAULT NULL,
P_MANUFACTURER_RATE_AMT_PER_UN IN JAI_CMN_RG_23D_TRXS.manufacturer_rate_amt_per_unit%TYPE DEFAULT NULL,
P_QTY_RECEIVED_FROM_MANUFACTUR IN JAI_CMN_RG_23D_TRXS.qty_received_from_manufacturer%TYPE DEFAULT NULL,
P_TOT_AMT_PAID_TO_MANUFACTURER IN JAI_CMN_RG_23D_TRXS.tot_amt_paid_to_manufacturer%TYPE DEFAULT NULL,
-- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23D_TRXS.other_tax_credit%TYPE DEFAULT NULL,
P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23D_TRXS.other_tax_debit%TYPE DEFAULT NULL,
P_SIMULATE_FLAG IN VARCHAR2,
P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
) IS
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_23d_trxs_pkg.update_row'; /* Added by Ramananda for bug#4407165 */
UPDATE JAI_CMN_RG_23D_TRXS SET
REGISTER_ID = nvl(P_REGISTER_ID, REGISTER_ID),
ORGANIZATION_ID = nvl(P_ORGANIZATION_ID, ORGANIZATION_ID),
LOCATION_ID = nvl(P_LOCATION_ID, LOCATION_ID),
SLNO = nvl(P_SLNO, SLNO),
FIN_YEAR = nvl(P_FIN_YEAR, FIN_YEAR),
TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
RECEIPT_REF = nvl(P_RECEIPT_ID, RECEIPT_REF),
QUANTITY_RECEIVED = nvl(P_QUANTITY_RECEIVED, QUANTITY_RECEIVED),
INVENTORY_ITEM_ID = nvl(P_INVENTORY_ITEM_ID, INVENTORY_ITEM_ID),
SUBINVENTORY = nvl(P_SUBINVENTORY, SUBINVENTORY),
REFERENCE_LINE_ID = nvl(P_REFERENCE_LINE_ID, REFERENCE_LINE_ID),
PRIMARY_UOM_CODE = nvl(P_PRIMARY_UOM_CODE, PRIMARY_UOM_CODE),
TRANSACTION_UOM_CODE = nvl(P_TRANSACTION_UOM_CODE, TRANSACTION_UOM_CODE),
CUSTOMER_ID = nvl(P_CUSTOMER_ID, CUSTOMER_ID),
BILL_TO_SITE_ID = nvl(P_BILL_TO_SITE_ID, BILL_TO_SITE_ID),
SHIP_TO_SITE_ID = nvl(P_SHIP_TO_SITE_ID, SHIP_TO_SITE_ID),
QUANTITY_ISSUED = nvl(P_QUANTITY_ISSUED, QUANTITY_ISSUED),
REGISTER_CODE = nvl(P_REGISTER_CODE, REGISTER_CODE),
RELEASED_DATE = nvl(P_RELEASED_DATE, RELEASED_DATE),
COMM_INVOICE_NO = nvl(P_COMM_INVOICE_NO, COMM_INVOICE_NO),
COMM_INVOICE_DATE = nvl(P_COMM_INVOICE_DATE, COMM_INVOICE_DATE),
RECEIPT_BOE_NUM = nvl(P_RECEIPT_BOE_NUM, RECEIPT_BOE_NUM),
OTH_RECEIPT_ID_REF = nvl(P_OTH_RECEIPT_ID, OTH_RECEIPT_ID_REF),
OTH_RECEIPT_DATE = nvl(P_OTH_RECEIPT_DATE, OTH_RECEIPT_DATE),
OTH_RECEIPT_QUANTITY = nvl(P_OTH_RECEIPT_QUANTITY, OTH_RECEIPT_QUANTITY),
REMARKS = nvl(P_REMARKS, REMARKS),
QTY_TO_ADJUST = nvl(P_QTY_TO_ADJUST, QTY_TO_ADJUST),
RATE_PER_UNIT = nvl(P_RATE_PER_UNIT, RATE_PER_UNIT),
EXCISE_DUTY_RATE = nvl(P_EXCISE_DUTY_RATE, EXCISE_DUTY_RATE),
CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
DUTY_AMOUNT = nvl(P_DUTY_AMOUNT, DUTY_AMOUNT),
TRANSACTION_SOURCE_NUM = nvl(P_TRANSACTION_ID, TRANSACTION_SOURCE_NUM),
RECEIPT_DATE = nvl(P_RECEIPT_DATE, RECEIPT_DATE),
GOODS_ISSUE_ID = nvl(P_GOODS_ISSUE_ID, GOODS_ISSUE_ID),
GOODS_ISSUE_DATE = nvl(P_GOODS_ISSUE_DATE, GOODS_ISSUE_DATE),
GOODS_ISSUE_QUANTITY = nvl(P_GOODS_ISSUE_QUANTITY, GOODS_ISSUE_QUANTITY),
TRANSACTION_DATE = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
OPENING_BALANCE_QTY = nvl(P_OPENING_BALANCE_QTY, OPENING_BALANCE_QTY),
CLOSING_BALANCE_QTY = nvl(P_CLOSING_BALANCE_QTY, CLOSING_BALANCE_QTY),
BASIC_ED = nvl(P_BASIC_ED, BASIC_ED),
ADDITIONAL_ED = nvl(P_ADDITIONAL_ED, ADDITIONAL_ED),
ADDITIONAL_CVD = nvl(P_ADDITIONAL_CVD,ADDITIONAL_CVD),
OTHER_ED = nvl(P_OTHER_ED, OTHER_ED),
CVD = nvl(P_CVD, CVD),
VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
RECEIPT_NUM = nvl(P_RECEIPT_NUM, RECEIPT_NUM),
ATTRIBUTE1 = nvl(P_ATTRIBUTE1, ATTRIBUTE1),
ATTRIBUTE2 = nvl(P_ATTRIBUTE2, ATTRIBUTE2),
ATTRIBUTE3 = nvl(P_ATTRIBUTE3, ATTRIBUTE3),
ATTRIBUTE4 = nvl(P_ATTRIBUTE4, ATTRIBUTE4),
ATTRIBUTE5 = nvl(P_ATTRIBUTE5, ATTRIBUTE5),
CONSIGNEE = nvl(P_CONSIGNEE, CONSIGNEE),
MANUFACTURER_NAME = nvl(P_MANUFACTURER_NAME, MANUFACTURER_NAME),
MANUFACTURER_ADDRESS = nvl(P_MANUFACTURER_ADDRESS, MANUFACTURER_ADDRESS),
MANUFACTURER_RATE_AMT_PER_UNIT= nvl(P_MANUFACTURER_RATE_AMT_PER_UN, MANUFACTURER_RATE_AMT_PER_UNIT),
QTY_RECEIVED_FROM_MANUFACTURER= nvl(P_QTY_RECEIVED_FROM_MANUFACTUR, QTY_RECEIVED_FROM_MANUFACTURER),
TOT_AMT_PAID_TO_MANUFACTURER = nvl(P_TOT_AMT_PAID_TO_MANUFACTURER, TOT_AMT_PAID_TO_MANUFACTURER),
-- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
other_tax_credit = nvl(p_other_tax_credit, other_tax_credit),
other_tax_debit = nvl(p_other_tax_debit, other_tax_debit)
WHERE register_id = p_register_id;
END update_row;
PROCEDURE update_qty_to_adjust(
p_register_id IN NUMBER,
p_quantity IN NUMBER,
P_SIMULATE_FLAG IN VARCHAR2,
P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
) IS
BEGIN
-- QTY_TO_ADJUST is a column which will be only INSERTED with a value for RECEIVE transaction and will be reduced
-- whenever a -ve Quantity Transaction or RTV Happens for Shipment Line
-- p_quantity can be +ve incase of regular RTV and +ve CORRECTion of RTV. -ve During Deliver to Non Trading
UPDATE JAI_CMN_RG_23D_TRXS
SET qty_to_adjust = nvl(qty_to_adjust, 0) - p_quantity,
last_update_date= SYSDATE
WHERE register_id = p_register_id;
END update_qty_to_adjust;
PROCEDURE update_payment_details(
p_register_id IN NUMBER,
p_charge_account_id IN NUMBER
) IS
BEGIN
UPDATE JAI_CMN_RG_23D_TRXS
SET charge_account_id = p_charge_account_id,
last_update_date= SYSDATE
WHERE register_id = p_register_id;
END update_payment_details;
SELECT count(1)
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND receipt_ref = p_receipt_id
AND TRANSACTION_SOURCE_NUM = p_transaction_id;
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
p_last_updated_by IN NUMBER)
IS
/* Added by Ramananda for bug#4407165 */
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_23d_trxs_pkg.make_entry';
SELECT JAI_CMN_RG_23D_TRXS_S.nextval
FROM dual;
SELECT max(fin_year) fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = p_org_id
AND fin_active_flag = 'Y';
SELECT slno,NVL(closing_balance_qty, 0)
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id
AND location_id = p_location_id
AND fin_year = v_fin_year
and inventory_item_id = p_item_id --ashish 12/nov/2002;
AND slno = (SELECT max(slno)
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id
AND location_id = p_location_id
AND fin_year = v_fin_year
and inventory_item_id = p_item_id); --ashish 12/nov/2002;
as there was no check on the inventory item id. the code is added for the selection of the slno.
From now on the serial no will be generated itemwise.
Also closing balance was increasinf even if the transaction type was issue which is wrong,
It has been corrected by adding an if condition and making the quantity negative in case
the transaction type is issue.Added a variable v_issue_qty for the same.
--------------------------------------------------------------------------------------------------------------------------*/
OPEN get_regid_cur;
INSERT INTO JAI_CMN_RG_23D_TRXS
(register_id,
organization_id,
location_id,
slno,
fin_year,
transaction_type,
inventory_item_id,
subinventory,
primary_uom_code,
transaction_uom_code,
OTH_RECEIPT_ID_REF,
oth_receipt_date,
oth_receipt_quantity,
TRANSACTION_SOURCE_NUM,
goods_issue_id,
goods_issue_date,
goods_issue_quantity,
transaction_date,
opening_balance_qty,
closing_balance_qty,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by)
VALUES
(v_reg_id,
p_org_id,
p_location_id,
v_slno,
v_fin_year,
p_trans_type,
p_item_id,
p_subinv_code,
p_pr_uom_code,
p_trans_uom_code,
p_oth_receipt_id,
p_oth_receipt_date,
p_oth_receipt_qty,
p_transaction_id,
p_goods_issue_id,
p_goods_issue_date,
v_issue_qty,
p_trans_date,
v_opening_bal_qty,
v_closing_bal_qty,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_update_login,
p_last_updated_by);
Select b.Inventory_Item_Id
From Ic_Item_Mst a, Mtl_System_Items b
Where a.Item_no = b.segment1
And a.Item_Id = p_INVENTORY_ITEM_ID
And ( b.organization_Id = p_org_id
or b.organization_Id = 0 ) ;
select NVL(MAX(slno),0), NVL(MAX(slno),0) + 1 from JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id
and inventory_item_id = v_inventory_item_id
and fin_year = p_fin_year - 1;
SELECT NVL(MAX(slno),0) , NVL(MAX(slno),0) + 1
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id and
-- location_id = p_location_id and
inventory_item_id = v_inventory_item_id
and fin_year = p_fin_year ;
SELECT NVL(opening_balance_qty,0),NVL(closing_balance_qty,0)
FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id and
--location_id = p_location_id and
slno = p_previous_serial_no and
inventory_item_id = v_inventory_item_id and
fin_year = x_fin_year;
select count(*) into cnt from JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_org_id
and inventory_item_id = v_inventory_item_id
and fin_year = p_fin_year;
update JAI_CMN_RG_23D_TRXS
set qty_to_adjust= nvl(qty_to_adjust,0) - nvl(p_quantity_applied,0)
where register_id=p_receipt_id;