The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_TRXN_REFERENCE_NUMBER IN OUT NOCOPY NUMBER,
X_SETTLE_BY_SYSTEM_FLAG VARCHAR2,
X_TRANSACTION_TYPE VARCHAR2,
X_TRXN_SUBTYPE_CODE_ID NUMBER,
X_TRANSACTION_DATE DATE,
X_ANTICIPATED_VALUE_DATE DATE,
X_TRANSACTION_DESCRIPTION VARCHAR2,
X_PAYMENT_CURRENCY_CODE VARCHAR2,
X_PAYMENT_AMOUNT NUMBER,
X_SOURCE_PARTY_ID NUMBER,
X_SOURCE_LEGAL_ENTITY_ID NUMBER,
X_SOURCE_BANK_ACCOUNT_ID NUMBER,
X_DEST_PARTY_ID NUMBER,
X_DEST_LEGAL_ENTITY_ID NUMBER,
X_DEST_BANK_ACCOUNT_ID NUMBER,
X_DEST_PARTY_SITE_ID NUMBER,
X_REPETITIVE_PAYMENT_CODE VARCHAR2,
X_TRXN_STATUS_CODE VARCHAR2,
X_PAYMENT_METHOD_CODE VARCHAR2,
X_AUTHORIZE_FLAG VARCHAR2,
X_BANK_CHARGE_BEARER VARCHAR2,
X_PAYMENT_REASON_CODE VARCHAR2,
X_PAYMENT_REASON_COMMENTS VARCHAR2,
X_REMITTANCE_MESSAGE1 VARCHAR2,
X_REMITTANCE_MESSAGE2 VARCHAR2,
X_REMITTANCE_MESSAGE3 VARCHAR2,
X_CREATED_FROM_DIR VARCHAR2,
X_CREATE_FROM_STMTLINE_ID NUMBER,
X_BANK_TRXN_NUMBER VARCHAR2,
X_PAYMENT_REQUEST_NUMBER NUMBER,
X_PAPER_DOCUMENT_NUMBER NUMBER,
X_DOC_SEQUENCE_ID NUMBER,
X_DOC_SEQUENCE_VALUE NUMBER,
X_DOC_CATEGORY_CODE VARCHAR2,
X_PAYMENT_OFFSET_CCID NUMBER,
X_RECEIPT_OFFSET_CCID NUMBER,
X_CASHPOOL_ID NUMBER,
X_EXT_BANK_ACCOUNT_ID NUMBER,
X_ATTRIBUTE_CATEGORY VARCHAR2,
X_ATTRIBUTE1 VARCHAR2,
X_ATTRIBUTE2 VARCHAR2,
X_ATTRIBUTE3 VARCHAR2,
X_ATTRIBUTE4 VARCHAR2,
X_ATTRIBUTE5 VARCHAR2,
X_ATTRIBUTE6 VARCHAR2,
X_ATTRIBUTE7 VARCHAR2,
X_ATTRIBUTE8 VARCHAR2,
X_ATTRIBUTE9 VARCHAR2,
X_ATTRIBUTE10 VARCHAR2,
X_ATTRIBUTE11 VARCHAR2,
X_ATTRIBUTE12 VARCHAR2,
X_ATTRIBUTE13 VARCHAR2,
X_ATTRIBUTE14 VARCHAR2,
X_ATTRIBUTE15 VARCHAR2,
X_CREATED_BY NUMBER,
X_CREATION_DATE DATE,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATE_LOGIN NUMBER
) IS
CURSOR C IS SELECT ROWID FROM CE_PAYMENT_TRANSACTIONS
WHERE TRXN_REFERENCE_NUMBER = X_TRXN_REFERENCE_NUMBER;
CURSOR C1 IS SELECT CE_PAYMENT_TRANSACTIONS_S.NEXTVAL FROM DUAL;
INSERT INTO CE_PAYMENT_TRANSACTIONS
( TRXN_REFERENCE_NUMBER,
SETTLE_BY_SYSTEM_FLAG,
TRANSACTION_TYPE,
TRXN_SUBTYPE_CODE_ID,
TRANSACTION_DATE,
ANTICIPATED_VALUE_DATE,
TRANSACTION_DESCRIPTION,
PAYMENT_CURRENCY_CODE,
PAYMENT_AMOUNT,
SOURCE_PARTY_ID,
SOURCE_LEGAL_ENTITY_ID,
SOURCE_BANK_ACCOUNT_ID,
DESTINATION_PARTY_ID,
DESTINATION_LEGAL_ENTITY_ID,
DESTINATION_BANK_ACCOUNT_ID,
DESTINATION_PARTY_SITE_ID,
REPETITIVE_PAYMENT_CODE,
TRXN_STATUS_CODE,
PAYMENT_METHOD_CODE,
AUTHORIZE_FLAG,
BANK_CHARGE_BEARER,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
CREATED_FROM_DIR,
CREATE_FROM_STMTLINE_ID,
BANK_TRXN_NUMBER,
PAYMENT_REQUEST_NUMBER,
PAPER_DOCUMENT_NUMBER,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
DOC_CATEGORY_CODE,
PAYMENT_OFFSET_CCID,
RECEIPT_OFFSET_CCID,
CASHPOOL_ID,
EXT_BANK_ACCOUNT_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
) VALUES
( X_TRXN_REFERENCE_NUMBER,
X_SETTLE_BY_SYSTEM_FLAG,
X_TRANSACTION_TYPE,
X_TRXN_SUBTYPE_CODE_ID,
X_TRANSACTION_DATE,
X_ANTICIPATED_VALUE_DATE,
X_TRANSACTION_DESCRIPTION,
X_PAYMENT_CURRENCY_CODE,
X_PAYMENT_AMOUNT,
X_SOURCE_PARTY_ID,
X_SOURCE_LEGAL_ENTITY_ID,
X_SOURCE_BANK_ACCOUNT_ID,
X_DEST_PARTY_ID,
X_DEST_LEGAL_ENTITY_ID,
X_DEST_BANK_ACCOUNT_ID,
X_DEST_PARTY_SITE_ID,
X_REPETITIVE_PAYMENT_CODE,
X_TRXN_STATUS_CODE,
X_PAYMENT_METHOD_CODE,
X_AUTHORIZE_FLAG,
X_BANK_CHARGE_BEARER,
X_PAYMENT_REASON_CODE,
X_PAYMENT_REASON_COMMENTS,
X_REMITTANCE_MESSAGE1,
X_REMITTANCE_MESSAGE2,
X_REMITTANCE_MESSAGE3,
X_CREATED_FROM_DIR,
X_CREATE_FROM_STMTLINE_ID,
X_BANK_TRXN_NUMBER,
X_PAYMENT_REQUEST_NUMBER,
X_PAPER_DOCUMENT_NUMBER,
X_DOC_SEQUENCE_ID,
X_DOC_SEQUENCE_VALUE,
X_DOC_CATEGORY_CODE,
X_PAYMENT_OFFSET_CCID,
X_RECEIPT_OFFSET_CCID,
X_CASHPOOL_ID,
X_EXT_BANK_ACCOUNT_ID,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
'1' );
END insert_row;
PROCEDURE UPDATE_ROW (
X_ROWID VARCHAR2,
X_TRXN_REFERENCE_NUMBER NUMBER,
X_SETTLE_BY_SYSTEM_FLAG VARCHAR2,
X_TRANSACTION_TYPE VARCHAR2,
X_TRXN_SUBTYPE_CODE_ID NUMBER,
X_TRANSACTION_DATE DATE,
X_ANTICIPATED_VALUE_DATE DATE,
X_TRANSACTION_DESCRIPTION VARCHAR2,
X_PAYMENT_CURRENCY_CODE VARCHAR2,
X_PAYMENT_AMOUNT NUMBER,
X_SOURCE_PARTY_ID NUMBER,
X_SOURCE_LEGAL_ENTITY_ID NUMBER,
X_SOURCE_BANK_ACCOUNT_ID NUMBER,
X_DEST_PARTY_ID NUMBER,
X_DEST_LEGAL_ENTITY_ID NUMBER,
X_DEST_BANK_ACCOUNT_ID NUMBER,
X_DEST_PARTY_SITE_ID NUMBER,
X_REPETITIVE_PAYMENT_CODE VARCHAR2,
X_TRXN_STATUS_CODE VARCHAR2,
X_PAYMENT_METHOD_CODE VARCHAR2,
X_AUTHORIZE_FLAG VARCHAR2,
X_BANK_CHARGE_BEARER VARCHAR2,
X_PAYMENT_REASON_CODE VARCHAR2,
X_PAYMENT_REASON_COMMENTS VARCHAR2,
X_REMITTANCE_MESSAGE1 VARCHAR2,
X_REMITTANCE_MESSAGE2 VARCHAR2,
X_REMITTANCE_MESSAGE3 VARCHAR2,
X_CREATED_FROM_DIR VARCHAR2,
X_CREATE_FROM_STMTLINE_ID NUMBER,
X_BANK_TRXN_NUMBER VARCHAR2,
X_PAYMENT_REQUEST_NUMBER NUMBER,
X_PAPER_DOCUMENT_NUMBER NUMBER,
X_DOC_SEQUENCE_ID NUMBER,
X_DOC_SEQUENCE_VALUE NUMBER,
X_DOC_CATEGORY_CODE VARCHAR2,
X_PAYMENT_OFFSET_CCID NUMBER,
X_RECEIPT_OFFSET_CCID NUMBER,
X_CASHPOOL_ID NUMBER,
X_EXT_BANK_ACCOUNT_ID NUMBER,
X_ATTRIBUTE_CATEGORY VARCHAR2,
X_ATTRIBUTE1 VARCHAR2,
X_ATTRIBUTE2 VARCHAR2,
X_ATTRIBUTE3 VARCHAR2,
X_ATTRIBUTE4 VARCHAR2,
X_ATTRIBUTE5 VARCHAR2,
X_ATTRIBUTE6 VARCHAR2,
X_ATTRIBUTE7 VARCHAR2,
X_ATTRIBUTE8 VARCHAR2,
X_ATTRIBUTE9 VARCHAR2,
X_ATTRIBUTE10 VARCHAR2,
X_ATTRIBUTE11 VARCHAR2,
X_ATTRIBUTE12 VARCHAR2,
X_ATTRIBUTE13 VARCHAR2,
X_ATTRIBUTE14 VARCHAR2,
X_ATTRIBUTE15 VARCHAR2,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATE_LOGIN NUMBER,
X_OBJECT_VERSION_NUMBER NUMBER ) IS
BEGIN
UPDATE CE_PAYMENT_TRANSACTIONS
SET
TRXN_REFERENCE_NUMBER = X_TRXN_REFERENCE_NUMBER,
SETTLE_BY_SYSTEM_FLAG = X_SETTLE_BY_SYSTEM_FLAG,
TRANSACTION_TYPE = X_TRANSACTION_TYPE,
TRXN_SUBTYPE_CODE_ID = X_TRXN_SUBTYPE_CODE_ID,
TRANSACTION_DATE = X_TRANSACTION_DATE,
ANTICIPATED_VALUE_DATE = X_ANTICIPATED_VALUE_DATE,
TRANSACTION_DESCRIPTION = X_TRANSACTION_DESCRIPTION,
PAYMENT_CURRENCY_CODE = X_PAYMENT_CURRENCY_CODE,
PAYMENT_AMOUNT = X_PAYMENT_AMOUNT,
SOURCE_PARTY_ID = X_SOURCE_PARTY_ID,
SOURCE_LEGAL_ENTITY_ID = X_SOURCE_LEGAL_ENTITY_ID,
SOURCE_BANK_ACCOUNT_ID = X_SOURCE_BANK_ACCOUNT_ID,
DESTINATION_PARTY_ID = X_DEST_PARTY_ID,
DESTINATION_LEGAL_ENTITY_ID = X_DEST_LEGAL_ENTITY_ID,
DESTINATION_BANK_ACCOUNT_ID = X_DEST_BANK_ACCOUNT_ID,
DESTINATION_PARTY_SITE_ID = X_DEST_PARTY_SITE_ID,
REPETITIVE_PAYMENT_CODE = X_REPETITIVE_PAYMENT_CODE,
TRXN_STATUS_CODE = X_TRXN_STATUS_CODE,
PAYMENT_METHOD_CODE = X_PAYMENT_METHOD_CODE,
AUTHORIZE_FLAG = X_AUTHORIZE_FLAG,
BANK_CHARGE_BEARER = X_BANK_CHARGE_BEARER,
PAYMENT_REASON_CODE = X_PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS = X_PAYMENT_REASON_COMMENTS,
REMITTANCE_MESSAGE1 = X_REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2 = X_REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3 = X_REMITTANCE_MESSAGE3,
CREATED_FROM_DIR = X_CREATED_FROM_DIR,
CREATE_FROM_STMTLINE_ID = X_CREATE_FROM_STMTLINE_ID,
BANK_TRXN_NUMBER = X_BANK_TRXN_NUMBER,
PAYMENT_REQUEST_NUMBER = X_PAYMENT_REQUEST_NUMBER,
PAPER_DOCUMENT_NUMBER = X_PAPER_DOCUMENT_NUMBER,
DOC_SEQUENCE_ID = X_DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE = X_DOC_SEQUENCE_VALUE,
DOC_CATEGORY_CODE = X_DOC_CATEGORY_CODE,
PAYMENT_OFFSET_CCID = X_PAYMENT_OFFSET_CCID,
RECEIPT_OFFSET_CCID = X_RECEIPT_OFFSET_CCID,
CASHPOOL_ID = X_CASHPOOL_ID,
EXT_BANK_ACCOUNT_ID = X_EXT_BANK_ACCOUNT_ID,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER AND
ROWID = X_ROWID;
END UPDATE_ROW;
PROCEDURE Delete_Row( X_Row_id VARCHAR2 ) IS
BEGIN
DELETE FROM CE_PAYMENT_TRANSACTIONS
WHERE rowid = X_Row_Id;
END Delete_Row;
PROCEDURE Update_Transfer_Status ( X_TRXN_NUMBER NUMBER, X_STATUS VARCHAR2) IS
BEGIN
-- Bug 10029478 added who columns in the update
UPDATE CE_PAYMENT_TRANSACTIONS
SET TRXN_STATUS_CODE = X_STATUS,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.user_id
WHERE TRXN_REFERENCE_NUMBER = X_TRXN_NUMBER;
END Update_Transfer_Status;