[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row(
P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_23AC_I_TRXS.register_id%TYPE,
-- P_FIN_YEAR IN JAI_CMN_RG_23AC_I_TRXS.fin_year%TYPE,
P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23AC_I_TRXS.inventory_item_id%TYPE,
P_ORGANIZATION_ID IN JAI_CMN_RG_23AC_I_TRXS.organization_id%TYPE,
P_QUANTITY_RECEIVED IN JAI_CMN_RG_23AC_I_TRXS.quantity_received%TYPE,
P_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.RECEIPT_REF%TYPE,
P_TRANSACTION_TYPE IN JAI_CMN_RG_23AC_I_TRXS.transaction_type%TYPE,
P_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.receipt_date%TYPE,
P_PO_HEADER_ID IN JAI_CMN_RG_23AC_I_TRXS.po_header_id%TYPE,
P_PO_HEADER_DATE IN JAI_CMN_RG_23AC_I_TRXS.po_header_date%TYPE,
P_PO_LINE_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_id%TYPE,
P_PO_LINE_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_location_id%TYPE,
P_VENDOR_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_id%TYPE,
P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_site_id%TYPE,
P_CUSTOMER_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_id%TYPE,
P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_site_id%TYPE,
P_GOODS_ISSUE_ID IN JAI_CMN_RG_23AC_I_TRXS.GOODS_ISSUE_ID_REF%TYPE,
P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_date%TYPE,
P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_quantity%TYPE,
P_SALES_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.SALES_INVOICE_NO%TYPE,
P_SALES_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_date%TYPE,
P_SALES_INVOICE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_quantity%TYPE,
P_EXCISE_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.EXCISE_INVOICE_NO%TYPE,
P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.excise_invoice_date%TYPE,
P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_quantity%TYPE,
P_OTH_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.OTH_RECEIPT_ID_REF%TYPE,
P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_date%TYPE,
P_REGISTER_TYPE IN JAI_CMN_RG_23AC_I_TRXS.register_type%TYPE,
P_IDENTIFICATION_NO IN JAI_CMN_RG_23AC_I_TRXS.identification_no%TYPE,
P_IDENTIFICATION_MARK IN JAI_CMN_RG_23AC_I_TRXS.identification_mark%TYPE,
P_BRAND_NAME IN JAI_CMN_RG_23AC_I_TRXS.brand_name%TYPE,
P_DATE_OF_VERIFICATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_verification%TYPE,
P_DATE_OF_INSTALLATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_installation%TYPE,
P_DATE_OF_COMMISSION IN JAI_CMN_RG_23AC_I_TRXS.date_of_commission%TYPE,
P_REGISER_ID_PART_II IN JAI_CMN_RG_23AC_I_TRXS.REGISTER_ID_PART_II%TYPE,
P_PLACE_OF_INSTALL IN JAI_CMN_RG_23AC_I_TRXS.place_of_install%TYPE,
P_REMARKS IN JAI_CMN_RG_23AC_I_TRXS.remarks%TYPE,
P_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.location_id%TYPE,
P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23AC_I_TRXS.transaction_uom_code%TYPE,
P_TRANSACTION_DATE IN JAI_CMN_RG_23AC_I_TRXS.transaction_date%TYPE,
P_BASIC_ED IN JAI_CMN_RG_23AC_I_TRXS.basic_ed%TYPE,
P_ADDITIONAL_ED IN JAI_CMN_RG_23AC_I_TRXS.additional_ed%TYPE,
P_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_I_TRXS.additional_cvd%TYPE DEFAULT NULL, /* Bug 5228046 added by sacsethi */
P_OTHER_ED IN JAI_CMN_RG_23AC_I_TRXS.other_ed%TYPE,
P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_I_TRXS.charge_account_id%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);
So Commented the Call to get_trxn_entry_cnt in jai_cmn_rg_23ac_i_trxs_pkg.insert_row
5. 31/10/2006 SACSETHI for bug 5228046, File version 120.3
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
----------------------------------------------------------------------------------------------------------------------------*/
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_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_DATE,
SALES_INVOICE_QUANTITY,
EXCISE_INVOICE_NO,
EXCISE_INVOICE_DATE,
OTH_RECEIPT_QUANTITY,
OTH_RECEIPT_ID_REF,
OTH_RECEIPT_DATE,
REGISTER_TYPE,
IDENTIFICATION_NO,
IDENTIFICATION_MARK,
BRAND_NAME,
DATE_OF_VERIFICATION,
DATE_OF_INSTALLATION,
DATE_OF_COMMISSION,
REGISTER_ID_PART_II,
PLACE_OF_INSTALL,
REMARKS,
LOCATION_ID,
PRIMARY_UOM_CODE,
TRANSACTION_UOM_CODE,
TRANSACTION_DATE,
BASIC_ED,
ADDITIONAL_ED,
ADDITIONAL_CVD, -- Bug 5228046 added by sacsethi
OTHER_ED,
OPENING_BALANCE_QTY,
CLOSING_BALANCE_QTY,
CHARGE_ACCOUNT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
POSTED_FLAG,
MASTER_FLAG
) VALUES (
JAI_CMN_RG_23AC_I_TRXS_S.nextval,
ln_fin_year,
ln_slno,
ln_transaction_id,
P_INVENTORY_ITEM_ID,
P_ORGANIZATION_ID,
P_QUANTITY_RECEIVED,
P_RECEIPT_ID,
lv_transaction_type,
P_RECEIPT_DATE,
lv_range,
lv_division,
P_PO_HEADER_ID,
P_PO_HEADER_DATE,
P_PO_LINE_ID,
P_PO_LINE_LOCATION_ID,
P_VENDOR_ID,
P_VENDOR_SITE_ID,
P_CUSTOMER_ID,
P_CUSTOMER_SITE_ID,
P_GOODS_ISSUE_ID,
P_GOODS_ISSUE_DATE,
P_GOODS_ISSUE_QUANTITY,
P_SALES_INVOICE_ID,
P_SALES_INVOICE_DATE,
P_SALES_INVOICE_QUANTITY,
P_EXCISE_INVOICE_ID,
P_EXCISE_INVOICE_DATE,
P_OTH_RECEIPT_QUANTITY,
P_OTH_RECEIPT_ID,
P_OTH_RECEIPT_DATE,
P_REGISTER_TYPE,
P_IDENTIFICATION_NO,
P_IDENTIFICATION_MARK,
P_BRAND_NAME,
P_DATE_OF_VERIFICATION,
P_DATE_OF_INSTALLATION,
P_DATE_OF_COMMISSION,
P_REGISER_ID_PART_II,
P_PLACE_OF_INSTALL,
P_REMARKS,
P_LOCATION_ID,
lv_primary_uom_code,
P_TRANSACTION_UOM_CODE,
P_TRANSACTION_DATE,
P_BASIC_ED,
P_ADDITIONAL_ED,
P_ADDITIONAL_CVD, -- Bug 5228046 added by sacsethi
P_OTHER_ED,
ln_opening_balance_qty,
ln_closing_balance_qty,
P_CHARGE_ACCOUNT_ID,
ld_creation_date,
ln_created_by,
ld_last_update_date,
ln_last_updated_by,
ln_last_update_login,
'N',
lv_master_flag
) RETURNING register_id INTO P_REGISTER_ID;
p_process_message := 'RG23_PART_I_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
END insert_row;
PROCEDURE update_row(
P_REGISTER_ID IN JAI_CMN_RG_23AC_I_TRXS.register_id%TYPE DEFAULT NULL,
P_QUANTITY_RECEIVED IN JAI_CMN_RG_23AC_I_TRXS.quantity_received%TYPE DEFAULT NULL,
P_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.RECEIPT_REF%TYPE DEFAULT NULL,
P_TRANSACTION_TYPE IN JAI_CMN_RG_23AC_I_TRXS.transaction_type%TYPE DEFAULT NULL,
P_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.receipt_date%TYPE DEFAULT NULL,
P_RANGE_NO IN JAI_CMN_RG_23AC_I_TRXS.range_no%TYPE DEFAULT NULL,
P_DIVISION_NO IN JAI_CMN_RG_23AC_I_TRXS.division_no%TYPE DEFAULT NULL,
P_PO_HEADER_ID IN JAI_CMN_RG_23AC_I_TRXS.po_header_id%TYPE DEFAULT NULL,
P_PO_HEADER_DATE IN JAI_CMN_RG_23AC_I_TRXS.po_header_date%TYPE DEFAULT NULL,
P_PO_LINE_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_id%TYPE DEFAULT NULL,
P_PO_LINE_LOCATION_ID IN JAI_CMN_RG_23AC_I_TRXS.po_line_location_id%TYPE DEFAULT NULL,
P_VENDOR_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_id%TYPE DEFAULT NULL,
P_VENDOR_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.vendor_site_id%TYPE DEFAULT NULL,
P_CUSTOMER_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_id%TYPE DEFAULT NULL,
P_CUSTOMER_SITE_ID IN JAI_CMN_RG_23AC_I_TRXS.customer_site_id%TYPE DEFAULT NULL,
P_GOODS_ISSUE_ID IN JAI_CMN_RG_23AC_I_TRXS.GOODS_ISSUE_ID_REF%TYPE DEFAULT NULL,
P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_date%TYPE DEFAULT NULL,
P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.goods_issue_quantity%TYPE DEFAULT NULL,
P_SALES_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.SALES_INVOICE_NO%TYPE DEFAULT NULL,
P_SALES_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_date%TYPE DEFAULT NULL,
P_SALES_INVOICE_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.sales_invoice_quantity%TYPE DEFAULT NULL,
P_EXCISE_INVOICE_ID IN JAI_CMN_RG_23AC_I_TRXS.EXCISE_INVOICE_NO%TYPE DEFAULT NULL,
P_EXCISE_INVOICE_DATE IN JAI_CMN_RG_23AC_I_TRXS.excise_invoice_date%TYPE DEFAULT NULL,
P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_quantity%TYPE DEFAULT NULL,
P_OTH_RECEIPT_ID IN JAI_CMN_RG_23AC_I_TRXS.OTH_RECEIPT_ID_REF%TYPE DEFAULT NULL,
P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23AC_I_TRXS.oth_receipt_date%TYPE DEFAULT NULL,
P_REGISTER_TYPE IN JAI_CMN_RG_23AC_I_TRXS.register_type%TYPE DEFAULT NULL,
P_IDENTIFICATION_NO IN JAI_CMN_RG_23AC_I_TRXS.identification_no%TYPE DEFAULT NULL,
P_IDENTIFICATION_MARK IN JAI_CMN_RG_23AC_I_TRXS.identification_mark%TYPE DEFAULT NULL,
P_BRAND_NAME IN JAI_CMN_RG_23AC_I_TRXS.brand_name%TYPE DEFAULT NULL,
P_DATE_OF_VERIFICATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_verification%TYPE DEFAULT NULL,
P_DATE_OF_INSTALLATION IN JAI_CMN_RG_23AC_I_TRXS.date_of_installation%TYPE DEFAULT NULL,
P_DATE_OF_COMMISSION IN JAI_CMN_RG_23AC_I_TRXS.date_of_commission%TYPE DEFAULT NULL,
P_REGISER_ID_PART_II IN JAI_CMN_RG_23AC_I_TRXS.REGISTER_ID_PART_II%TYPE DEFAULT NULL,
P_PLACE_OF_INSTALL IN JAI_CMN_RG_23AC_I_TRXS.place_of_install%TYPE DEFAULT NULL,
P_REMARKS IN JAI_CMN_RG_23AC_I_TRXS.remarks%TYPE DEFAULT NULL,
P_BASIC_ED IN JAI_CMN_RG_23AC_I_TRXS.basic_ed%TYPE DEFAULT NULL,
P_ADDITIONAL_ED IN JAI_CMN_RG_23AC_I_TRXS.additional_ed%TYPE DEFAULT NULL,
P_ADDITIONAL_CVD IN JAI_CMN_RG_23AC_I_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Bug 5228046 added by sacsethi
P_OTHER_ED IN JAI_CMN_RG_23AC_I_TRXS.other_ed%TYPE DEFAULT NULL,
P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23AC_I_TRXS.charge_account_id%TYPE DEFAULT NULL,
P_POSTED_FLAG IN JAI_CMN_RG_23AC_I_TRXS.posted_flag%TYPE DEFAULT NULL,
P_MASTER_FLAG IN JAI_CMN_RG_23AC_I_TRXS.master_flag%TYPE DEFAULT NULL,
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_last_update_date DATE;
ln_last_updated_by NUMBER(15);
ln_last_update_login NUMBER(15);
ld_last_update_date := SYSDATE;
ln_last_updated_by := FND_GLOBAL.user_id;
ln_last_update_login := FND_GLOBAL.login_id;
UPDATE JAI_CMN_RG_23AC_I_TRXS SET
QUANTITY_RECEIVED = nvl(P_QUANTITY_RECEIVED, QUANTITY_RECEIVED),
RECEIPT_REF = nvl(P_RECEIPT_ID, RECEIPT_REF),
TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
RECEIPT_DATE = nvl(P_RECEIPT_DATE, RECEIPT_DATE),
RANGE_NO = nvl(P_RANGE_NO, RANGE_NO),
DIVISION_NO = nvl(P_DIVISION_NO, DIVISION_NO),
PO_HEADER_ID = nvl(P_PO_HEADER_ID, PO_HEADER_ID),
PO_HEADER_DATE = nvl(P_PO_HEADER_DATE, PO_HEADER_DATE),
PO_LINE_ID = nvl(P_PO_LINE_ID, PO_LINE_ID),
PO_LINE_LOCATION_ID = nvl(P_PO_LINE_LOCATION_ID, PO_LINE_LOCATION_ID),
VENDOR_ID = nvl(P_VENDOR_ID, VENDOR_ID),
VENDOR_SITE_ID = nvl(P_VENDOR_SITE_ID, VENDOR_SITE_ID),
CUSTOMER_ID = nvl(P_CUSTOMER_ID, CUSTOMER_ID),
CUSTOMER_SITE_ID = nvl(P_CUSTOMER_SITE_ID, CUSTOMER_SITE_ID),
GOODS_ISSUE_ID_REF = nvl(P_GOODS_ISSUE_ID, GOODS_ISSUE_ID_REF),
GOODS_ISSUE_DATE = nvl(P_GOODS_ISSUE_DATE, GOODS_ISSUE_DATE),
GOODS_ISSUE_QUANTITY = nvl(P_GOODS_ISSUE_QUANTITY, GOODS_ISSUE_QUANTITY),
SALES_INVOICE_NO = nvl(P_SALES_INVOICE_ID, SALES_INVOICE_NO),
SALES_INVOICE_DATE = nvl(P_SALES_INVOICE_DATE, SALES_INVOICE_DATE),
SALES_INVOICE_QUANTITY = nvl(P_SALES_INVOICE_QUANTITY, SALES_INVOICE_QUANTITY),
EXCISE_INVOICE_NO = nvl(P_EXCISE_INVOICE_ID, EXCISE_INVOICE_NO),
EXCISE_INVOICE_DATE = nvl(P_EXCISE_INVOICE_DATE, EXCISE_INVOICE_DATE),
OTH_RECEIPT_QUANTITY = nvl(P_OTH_RECEIPT_QUANTITY, OTH_RECEIPT_QUANTITY),
OTH_RECEIPT_ID_REF = nvl(P_OTH_RECEIPT_ID, OTH_RECEIPT_ID_REF),
OTH_RECEIPT_DATE = nvl(P_OTH_RECEIPT_DATE, OTH_RECEIPT_DATE),
REGISTER_TYPE = nvl(P_REGISTER_TYPE, REGISTER_TYPE),
IDENTIFICATION_NO = nvl(P_IDENTIFICATION_NO, IDENTIFICATION_NO),
IDENTIFICATION_MARK = nvl(P_IDENTIFICATION_MARK, IDENTIFICATION_MARK),
BRAND_NAME = nvl(P_BRAND_NAME, BRAND_NAME),
DATE_OF_VERIFICATION = nvl(P_DATE_OF_VERIFICATION, DATE_OF_VERIFICATION),
DATE_OF_INSTALLATION = nvl(P_DATE_OF_INSTALLATION, DATE_OF_INSTALLATION),
DATE_OF_COMMISSION = nvl(P_DATE_OF_COMMISSION, DATE_OF_COMMISSION),
REGISTER_ID_PART_II = nvl(P_REGISER_ID_PART_II, REGISTER_ID_PART_II),
PLACE_OF_INSTALL = nvl(P_PLACE_OF_INSTALL, PLACE_OF_INSTALL),
REMARKS = nvl(P_REMARKS, REMARKS),
BASIC_ED = nvl(P_BASIC_ED, BASIC_ED),
ADDITIONAL_ED = nvl(P_ADDITIONAL_ED, ADDITIONAL_ED),
ADDITIONAL_CVD = nvl(P_ADDITIONAL_CVD, ADDITIONAL_CVD), --Bug 5228046 added by sacsethi
OTHER_ED = nvl(P_OTHER_ED, OTHER_ED),
OPENING_BALANCE_QTY = ln_opening_balance_qty,
CLOSING_BALANCE_QTY = ln_closing_balance_qty,
CHARGE_ACCOUNT_ID = nvl(P_CHARGE_ACCOUNT_ID, CHARGE_ACCOUNT_ID),
LAST_UPDATE_DATE = ld_last_update_date,
LAST_UPDATED_BY = ln_last_updated_by,
LAST_UPDATE_LOGIN = ln_last_update_login,
POSTED_FLAG = nvl(P_POSTED_FLAG, POSTED_FLAG),
MASTER_FLAG = nvl(P_MASTER_FLAG, MASTER_FLAG)
WHERE register_id = p_register_id;
END update_row;
PROCEDURE update_payment_details(
p_register_id IN NUMBER,
p_register_id_part_ii IN NUMBER,
p_charge_account_id IN NUMBER
) IS
BEGIN
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET
REGISTER_ID_PART_II = p_register_id_part_ii,
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_23AC_I_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND register_type = p_register_type
AND receipt_ref = p_receipt_id
AND TRANSACTION_SOURCE_NUM = p_transaction_id;