[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_INVOICE_ID IN NUMBER,
X_INVOICE_NUM IN VARCHAR2,
X_INVOICE_TYPE_LOOKUP_CODE IN VARCHAR2,
X_INVOICE_DATE IN DATE,
X_PO_NUMBER IN VARCHAR2,
X_VENDOR_ID IN NUMBER,
X_VENDOR_SITE_ID IN NUMBER,
X_INVOICE_AMOUNT IN NUMBER,
X_INVOICE_CURRENCY_CODE IN VARCHAR2,
X_PAYMENT_CURRENCY_CODE IN VARCHAR2,
X_PAYMENT_CROSS_RATE IN NUMBER,
X_PAYMENT_CROSS_RATE_TYPE IN VARCHAR2,
X_PAYMENT_CROSS_RATE_DATE IN DATE,
X_EXCHANGE_RATE IN NUMBER,
X_EXCHANGE_RATE_TYPE IN VARCHAR2,
X_EXCHANGE_DATE IN DATE,
X_TERMS_ID IN NUMBER,
X_DESCRIPTION IN VARCHAR2,
X_AWT_GROUP_ID IN NUMBER,
X_PAY_AWT_GROUP_ID IN NUMBER DEFAULT NULL,--bug6639866
X_AMT_APPLICABLE_TO_DISCOUNT IN NUMBER,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_GLOBAL_ATTRIBUTE1 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE2 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE3 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE4 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE5 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE6 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE7 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE8 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE9 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE10 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE11 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE12 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE13 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE14 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE15 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE16 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE17 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE18 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE19 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE20 IN VARCHAR2,
X_STATUS IN VARCHAR2,
X_SOURCE IN VARCHAR2,
X_GROUP_ID IN VARCHAR2,
X_WORKFLOW_FLAG IN VARCHAR2,
X_DOC_CATEGORY_CODE IN VARCHAR2,
X_VOUCHER_NUM IN VARCHAR2,
X_PAY_GROUP_LOOKUP_CODE IN VARCHAR2,
X_GOODS_RECEIVED_DATE IN DATE,
X_INVOICE_RECEIVED_DATE IN DATE,
X_GL_DATE IN DATE,
X_ACCTS_PAY_CCID IN NUMBER,
-- X_USSGL_TRANSACTION_CODE IN VARCHAR2, - Bug 4277744
X_EXCLUSIVE_PAYMENT_FLAG IN VARCHAR2,
X_INVOICE_INCLUDES_PREPAY_FLAG IN VARCHAR2,
X_PREPAY_NUM IN VARCHAR2,
X_PREPAY_APPLY_AMOUNT IN NUMBER,
X_PREPAY_GL_DATE IN DATE,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_ORG_ID IN NUMBER,
X_MODE IN VARCHAR2 DEFAULT 'R',
X_TERMS_DATE IN DATE DEFAULT NULL,
X_REQUESTER_ID IN NUMBER DEFAULT NULL,
X_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
-- Invoice LINes Project Stage 1
X_PREPAY_LINE_NUM IN NUMBER DEFAULT NULL,
X_REQUESTER_FIRST_NAME IN VARCHAR2 DEFAULT NULL,
X_REQUESTER_LAST_NAME IN VARCHAR2 DEFAULT NULL,
X_REQUESTER_EMPLOYEE_NUM IN VARCHAR2 DEFAULT NULL,
-- eTax Uptake
X_CALC_TAX_DURING_IMPORT_FLAG IN VARCHAR2 DEFAULT NULL,
X_CONTROL_AMOUNT IN NUMBER DEFAULT NULL,
X_ADD_TAX_TO_INV_AMT_FLAG IN VARCHAR2 DEFAULT NULL,
X_TAX_RELATED_INVOICE_ID IN NUMBER DEFAULT NULL,
X_TAXATION_COUNTRY IN VARCHAR2 DEFAULT NULL,
X_DOCUMENT_SUB_TYPE IN VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_NUMBER IN VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_DATE IN DATE DEFAULT NULL,
X_SUPPLIER_TAX_EXCHANGE_RATE IN NUMBER DEFAULT NULL,
X_TAX_INVOICE_RECORDING_DATE IN DATE DEFAULT NULL,
X_TAX_INVOICE_INTERNAL_SEQ IN VARCHAR2 DEFAULT NULL,
X_LEGAL_ENTITY_ID IN NUMBER DEFAULT NULL,
x_PAYMENT_METHOD_CODE in varchar2 default null,
x_PAYMENT_REASON_CODE in varchar2 default null,
X_PAYMENT_REASON_COMMENTS in varchar2 default null,
x_UNIQUE_REMITTANCE_IDENTIFIER in varchar2 default null,
x_URI_CHECK_DIGIT in varchar2 default null,
x_BANK_CHARGE_BEARER in varchar2 default null,
x_DELIVERY_CHANNEL_CODE in varchar2 default null,
x_SETTLEMENT_PRIORITY in varchar2 default null,
x_remittance_message1 in varchar2 default null,
x_remittance_message2 in varchar2 default null,
x_remittance_message3 in varchar2 default null,
x_NET_OF_RETAINAGE_FLAG in varchar2 default null,
x_PORT_OF_ENTRY_CODE in varchar2 default null,
X_APPLICATION_ID IN NUMBER DEFAULT NULL,
X_PRODUCT_TABLE IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY1 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY2 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY3 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY4 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY5 IN VARCHAR2 DEFAULT NULL,
X_PARTY_ID IN NUMBER DEFAULT NULL,
X_PARTY_SITE_ID IN NUMBER DEFAULT NULL,
X_PAY_PROC_TRXN_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
X_PAYMENT_FUNCTION IN VARCHAR2 DEFAULT NULL,
X_PAYMENT_PRIORITY IN NUMBER DEFAULT NULL,
x_external_bank_account_id in number default null,
X_REMIT_TO_SUPPLIER_NAME IN VARCHAR2 DEFAULT NULL,
X_REMIT_TO_SUPPLIER_ID IN NUMBER DEFAULT NULL,
X_REMIT_TO_SUPPLIER_SITE IN VARCHAR2 DEFAULT NULL,
X_REMIT_TO_SUPPLIER_SITE_ID IN NUMBER DEFAULT NULL,
X_RELATIONSHIP_ID IN NUMBER DEFAULT NULL
)
IS
CURSOR C IS
SELECT ROWID
FROM AP_INVOICES_INTERFACE
WHERE INVOICE_ID = X_INVOICE_ID;
INSERT INTO AP_INVOICES_INTERFACE (
INVOICE_ID,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
PO_NUMBER,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_CROSS_RATE,
PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
TERMS_ID,
DESCRIPTION,
AWT_GROUP_ID,
PAY_AWT_GROUP_ID,--bug6639866
AMOUNT_APPLICABLE_TO_DISCOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
STATUS,
SOURCE,
GROUP_ID,
WORKFLOW_FLAG,
DOC_CATEGORY_CODE,
VOUCHER_NUM,
PAY_GROUP_LOOKUP_CODE,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
GL_DATE ,
ACCTS_PAY_CODE_COMBINATION_ID ,
-- USSGL_TRANSACTION_CODE , - Bug 4277744
EXCLUSIVE_PAYMENT_FLAG,
INVOICE_INCLUDES_PREPAY_FLAG ,
PREPAY_NUM,
PREPAY_APPLY_AMOUNT,
PREPAY_GL_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID,
TERMS_DATE ,
REQUESTER_ID,
OPERATING_UNIT,
-- Invoice Lines Project Stage 1
PREPAY_LINE_NUM,
REQUESTER_FIRST_NAME,
REQUESTER_LAST_NAME,
REQUESTER_EMPLOYEE_NUM,
-- eTax Uptake
CALC_TAX_DURING_IMPORT_FLAG,
CONTROL_AMOUNT,
ADD_TAX_TO_INV_AMT_FLAG,
TAX_RELATED_INVOICE_ID,
TAXATION_COUNTRY,
DOCUMENT_SUB_TYPE,
SUPPLIER_TAX_INVOICE_NUMBER,
SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_TAX_EXCHANGE_RATE,
TAX_INVOICE_RECORDING_DATE,
TAX_INVOICE_INTERNAL_SEQ,
LEGAL_ENTITY_ID,
PAYMENT_METHOD_CODE,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
BANK_CHARGE_BEARER,
DELIVERY_CHANNEL_CODE,
SETTLEMENT_PRIORITY,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
NET_OF_RETAINAGE_FLAG,
PORT_OF_ENTRY_CODE,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
PARTY_ID,
PARTY_SITE_ID,
PAY_PROC_TRXN_TYPE_CODE,
PAYMENT_FUNCTION,
PAYMENT_PRIORITY,
external_bank_account_id,
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID
)
VALUES (
X_INVOICE_ID,
X_INVOICE_NUM,
X_INVOICE_TYPE_LOOKUP_CODE,
X_INVOICE_DATE,
X_PO_NUMBER,
X_VENDOR_ID,
X_VENDOR_SITE_ID,
X_INVOICE_AMOUNT,
X_INVOICE_CURRENCY_CODE,
X_PAYMENT_CURRENCY_CODE,
X_PAYMENT_CROSS_RATE,
X_PAYMENT_CROSS_RATE_TYPE,
X_PAYMENT_CROSS_RATE_DATE,
X_EXCHANGE_RATE,
X_EXCHANGE_RATE_TYPE,
X_EXCHANGE_DATE,
X_TERMS_ID,
X_DESCRIPTION,
X_AWT_GROUP_ID,
X_PAY_AWT_GROUP_ID, --bug6639866
X_AMT_APPLICABLE_TO_DISCOUNT,
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_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2,
X_GLOBAL_ATTRIBUTE3,
X_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20,
X_STATUS,
X_SOURCE,
X_GROUP_ID,
X_WORKFLOW_FLAG,
X_DOC_CATEGORY_CODE,
X_VOUCHER_NUM,
X_PAY_GROUP_LOOKUP_CODE,
X_GOODS_RECEIVED_DATE,
X_INVOICE_RECEIVED_DATE,
X_GL_DATE ,
X_ACCTS_PAY_CCID ,
-- X_USSGL_TRANSACTION_CODE , - Bug 4277744
X_EXCLUSIVE_PAYMENT_FLAG ,
X_INVOICE_INCLUDES_PREPAY_FLAG ,
X_PREPAY_NUM,
X_PREPAY_APPLY_AMOUNT,
X_PREPAY_GL_DATE,
X_Creation_Date,
X_Created_By,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_ORG_ID,
X_TERMS_DATE,
X_REQUESTER_ID,
X_OPERATING_UNIT,
-- Invoice Lines Project Stage 1
X_PREPAY_LINE_NUM,
X_REQUESTER_FIRST_NAME,
X_REQUESTER_LAST_NAME,
X_REQUESTER_EMPLOYEE_NUM,
-- eTax Uptake
X_CALC_TAX_DURING_IMPORT_FLAG,
X_CONTROL_AMOUNT,
X_ADD_TAX_TO_INV_AMT_FLAG,
X_TAX_RELATED_INVOICE_ID,
X_TAXATION_COUNTRY,
X_DOCUMENT_SUB_TYPE,
X_SUPPLIER_TAX_INVOICE_NUMBER,
X_SUPPLIER_TAX_INVOICE_DATE,
X_SUPPLIER_TAX_EXCHANGE_RATE,
X_TAX_INVOICE_RECORDING_DATE,
X_TAX_INVOICE_INTERNAL_SEQ,
X_LEGAL_ENTITY_ID,
x_PAYMENT_METHOD_CODE,
x_PAYMENT_REASON_CODE,
X_PAYMENT_REASON_COMMENTS,
x_UNIQUE_REMITTANCE_IDENTIFIER,
x_URI_CHECK_DIGIT,
x_BANK_CHARGE_BEARER,
x_DELIVERY_CHANNEL_CODE,
x_SETTLEMENT_PRIORITY,
X_REMITTANCE_MESSAGE1,
X_REMITTANCE_MESSAGE2,
X_REMITTANCE_MESSAGE3,
x_NET_OF_RETAINAGE_FLAG,
x_PORT_OF_ENTRY_CODE,
X_APPLICATION_ID,
X_PRODUCT_TABLE,
X_REFERENCE_KEY1,
X_REFERENCE_KEY2,
X_REFERENCE_KEY3,
X_REFERENCE_KEY4,
X_REFERENCE_KEY5,
X_PARTY_ID,
X_PARTY_SITE_ID,
X_PAY_PROC_TRXN_TYPE_CODE,
X_PAYMENT_FUNCTION,
X_PAYMENT_PRIORITY,
x_external_bank_account_id,
X_REMIT_TO_SUPPLIER_NAME,
X_REMIT_TO_SUPPLIER_ID,
X_REMIT_TO_SUPPLIER_SITE,
X_REMIT_TO_SUPPLIER_SITE_ID,
X_RELATIONSHIP_ID);
END INSERT_ROW;
CURSOR c1 IS SELECT
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
PO_NUMBER,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_CROSS_RATE,
PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
TERMS_ID,
DESCRIPTION,
AWT_GROUP_ID,
PAY_AWT_GROUP_ID, --bug6639866
AMOUNT_APPLICABLE_TO_DISCOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
STATUS,
SOURCE,
GROUP_ID,
WORKFLOW_FLAG,
DOC_CATEGORY_CODE,
VOUCHER_NUM,
PAY_GROUP_LOOKUP_CODE,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
GL_DATE ,
ACCTS_PAY_CODE_COMBINATION_ID ,
-- USSGL_TRANSACTION_CODE , - Bug 4277744
EXCLUSIVE_PAYMENT_FLAG,
INVOICE_INCLUDES_PREPAY_FLAG,
PREPAY_NUM,
PREPAY_APPLY_AMOUNT,
PREPAY_GL_DATE,
TERMS_DATE ,
REQUESTER_ID,
OPERATING_UNIT,
-- Invoice Lines Project Stage 1
PREPAY_LINE_NUM,
REQUESTER_FIRST_NAME,
REQUESTER_LAST_NAME,
REQUESTER_EMPLOYEE_NUM,
-- eTax Uptake
CALC_TAX_DURING_IMPORT_FLAG,
CONTROL_AMOUNT,
ADD_TAX_TO_INV_AMT_FLAG,
TAX_RELATED_INVOICE_ID,
TAXATION_COUNTRY,
DOCUMENT_SUB_TYPE,
SUPPLIER_TAX_INVOICE_NUMBER,
SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_TAX_EXCHANGE_RATE,
TAX_INVOICE_RECORDING_DATE,
TAX_INVOICE_INTERNAL_SEQ,
LEGAL_ENTITY_ID,
PAYMENT_METHOD_CODE,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
BANK_CHARGE_BEARER,
DELIVERY_CHANNEL_CODE,
SETTLEMENT_PRIORITY,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
NET_OF_RETAINAGE_FLAG,
PORT_OF_ENTRY_CODE,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
PARTY_ID,
PARTY_SITE_ID,
PAY_PROC_TRXN_TYPE_CODE,
PAYMENT_FUNCTION,
PAYMENT_PRIORITY,
external_bank_account_id,
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID
FROM AP_INVOICES_INTERFACE
WHERE INVOICE_ID = X_INVOICE_ID
FOR UPDATE OF INVOICE_ID nowait;
procedure UPDATE_ROW (
X_INVOICE_ID IN NUMBER,
X_INVOICE_NUM IN VARCHAR2,
X_INVOICE_TYPE_LOOKUP_CODE IN VARCHAR2,
X_INVOICE_DATE IN DATE,
X_PO_NUMBER IN VARCHAR2,
X_VENDOR_ID IN NUMBER,
X_VENDOR_SITE_ID IN NUMBER,
X_INVOICE_AMOUNT IN NUMBER,
X_INVOICE_CURRENCY_CODE IN VARCHAR2,
X_PAYMENT_CURRENCY_CODE IN VARCHAR2,
X_PAYMENT_CROSS_RATE IN NUMBER,
X_PAYMENT_CROSS_RATE_TYPE IN VARCHAR2,
X_PAYMENT_CROSS_RATE_DATE IN DATE,
X_EXCHANGE_RATE IN NUMBER,
X_EXCHANGE_RATE_TYPE IN VARCHAR2,
X_EXCHANGE_DATE IN DATE,
X_TERMS_ID IN NUMBER,
X_DESCRIPTION IN VARCHAR2,
X_AWT_GROUP_ID IN NUMBER,
X_PAY_AWT_GROUP_ID IN NUMBER DEFAULT NULL, --bug6639866
X_AMT_APPLICABLE_TO_DISCOUNT IN NUMBER,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_GLOBAL_ATTRIBUTE1 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE2 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE3 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE4 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE5 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE6 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE7 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE8 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE9 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE10 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE11 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE12 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE13 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE14 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE15 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE16 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE17 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE18 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE19 IN VARCHAR2,
X_GLOBAL_ATTRIBUTE20 IN VARCHAR2,
X_STATUS IN VARCHAR2,
X_SOURCE IN VARCHAR2,
X_GROUP_ID IN VARCHAR2,
X_WORKFLOW_FLAG IN VARCHAR2,
X_DOC_CATEGORY_CODE IN VARCHAR2,
X_VOUCHER_NUM IN VARCHAR2,
X_PAY_GROUP_LOOKUP_CODE IN VARCHAR2,
X_GOODS_RECEIVED_DATE IN DATE,
X_INVOICE_RECEIVED_DATE IN DATE,
X_GL_DATE IN DATE,
X_ACCTS_PAY_CCID IN NUMBER,
-- X_USSGL_TRANSACTION_CODE IN VARCHAR2, - Bug 4277744
X_EXCLUSIVE_PAYMENT_FLAG IN VARCHAR2,
X_INVOICE_INCLUDES_PREPAY_FLAG IN VARCHAR2,
X_PREPAY_NUM IN VARCHAR2,
X_PREPAY_APPLY_AMOUNT IN NUMBER,
X_PREPAY_GL_DATE IN DATE,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_MODE IN VARCHAR2 DEFAULT 'R',
X_TERMS_DATE IN DATE DEFAULT NULL,
X_REQUESTER_ID IN NUMBER DEFAULT NULL,
X_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
-- Invoice LINes Project Stage 1
X_PREPAY_LINE_NUM IN NUMBER DEFAULT NULL,
X_REQUESTER_FIRST_NAME IN VARCHAR2 DEFAULT NULL,
X_REQUESTER_LAST_NAME IN VARCHAR2 DEFAULT NULL,
X_REQUESTER_EMPLOYEE_NUM IN VARCHAR2 DEFAULT NULL,
-- eTax Uptake
X_CALC_TAX_DURING_IMPORT_FLAG IN VARCHAR2 DEFAULT NULL,
X_CONTROL_AMOUNT IN NUMBER DEFAULT NULL,
X_ADD_TAX_TO_INV_AMT_FLAG IN VARCHAR2 DEFAULT NULL,
X_TAX_RELATED_INVOICE_ID IN NUMBER DEFAULT NULL,
X_TAXATION_COUNTRY IN VARCHAR2 DEFAULT NULL,
X_DOCUMENT_SUB_TYPE IN VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_NUMBER IN VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_DATE IN DATE DEFAULT NULL,
X_SUPPLIER_TAX_EXCHANGE_RATE IN NUMBER DEFAULT NULL,
X_TAX_INVOICE_RECORDING_DATE IN DATE DEFAULT NULL,
X_TAX_INVOICE_INTERNAL_SEQ IN VARCHAR2 DEFAULT NULL,
X_LEGAL_ENTITY_ID IN NUMBER DEFAULT NULL,
x_PAYMENT_METHOD_CODE in varchar2 default null,
x_PAYMENT_REASON_CODE in varchar2 default null,
X_PAYMENT_REASON_COMMENTS in varchar2 default null,
x_UNIQUE_REMITTANCE_IDENTIFIER in varchar2 default null,
x_URI_CHECK_DIGIT in varchar2 default null,
x_BANK_CHARGE_BEARER in varchar2 default null,
x_DELIVERY_CHANNEL_CODE in varchar2 default null,
x_SETTLEMENT_PRIORITY in varchar2 default null,
x_remittance_message1 in varchar2 default null,
x_remittance_message2 in varchar2 default null,
x_remittance_message3 in varchar2 default null,
x_NET_OF_RETAINAGE_FLAG in varchar2 default null,
x_PORT_OF_ENTRY_CODE in varchar2 default null,
X_APPLICATION_ID IN NUMBER DEFAULT NULL,
X_PRODUCT_TABLE IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY1 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY2 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY3 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY4 IN VARCHAR2 DEFAULT NULL,
X_REFERENCE_KEY5 IN VARCHAR2 DEFAULT NULL,
X_PARTY_ID IN NUMBER DEFAULT NULL,
X_PARTY_SITE_ID IN NUMBER DEFAULT NULL,
X_PAY_PROC_TRXN_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
X_PAYMENT_FUNCTION IN VARCHAR2 DEFAULT NULL,
X_PAYMENT_PRIORITY IN NUMBER DEFAULT NULL,
X_external_bank_account_id in number default null,
X_REMIT_TO_SUPPLIER_NAME IN VARCHAR2 DEFAULT NULL,
X_REMIT_TO_SUPPLIER_ID IN NUMBER DEFAULT NULL,
X_REMIT_TO_SUPPLIER_SITE IN VARCHAR2 DEFAULT NULL,
X_REMIT_TO_SUPPLIER_SITE_ID IN NUMBER DEFAULT NULL,
X_RELATIONSHIP_ID IN NUMBER DEFAULT NULL)
IS
BEGIN
UPDATE AP_INVOICES_INTERFACE
SET
INVOICE_NUM = X_INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE = X_INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE = X_INVOICE_DATE,
PO_NUMBER = X_PO_NUMBER,
VENDOR_ID = X_VENDOR_ID,
VENDOR_SITE_ID = X_VENDOR_SITE_ID,
INVOICE_AMOUNT = X_INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE = X_INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE = X_PAYMENT_CURRENCY_CODE,
PAYMENT_CROSS_RATE = X_PAYMENT_CROSS_RATE,
PAYMENT_CROSS_RATE_TYPE = X_PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE = X_PAYMENT_CROSS_RATE_DATE,
EXCHANGE_RATE = X_EXCHANGE_RATE,
EXCHANGE_RATE_TYPE = X_EXCHANGE_RATE_TYPE,
EXCHANGE_DATE = X_EXCHANGE_DATE,
TERMS_ID = X_TERMS_ID,
DESCRIPTION = X_DESCRIPTION,
AWT_GROUP_ID = X_AWT_GROUP_ID,
PAY_AWT_GROUP_ID = X_PAY_AWT_GROUP_ID, --bug6639866
AMOUNT_APPLICABLE_TO_DISCOUNT = X_AMT_APPLICABLE_TO_DISCOUNT,
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,
GLOBAL_ATTRIBUTE_CATEGORY = X_GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1 = X_GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2 = X_GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3 = X_GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4 = X_GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5 = X_GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6 = X_GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7 = X_GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8 = X_GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9 = X_GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10 = X_GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11 = X_GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12 = X_GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13 = X_GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14 = X_GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15 = X_GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16 = X_GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17 = X_GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18 = X_GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19 = X_GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20 = X_GLOBAL_ATTRIBUTE20,
STATUS = X_STATUS,
SOURCE = X_SOURCE,
GROUP_ID = X_GROUP_ID,
WORKFLOW_FLAG = X_WORKFLOW_FLAG,
DOC_CATEGORY_CODE = X_DOC_CATEGORY_CODE,
VOUCHER_NUM = X_VOUCHER_NUM,
PAY_GROUP_LOOKUP_CODE = X_PAY_GROUP_LOOKUP_CODE,
GOODS_RECEIVED_DATE = X_GOODS_RECEIVED_DATE,
GL_DATE = X_GL_DATE,
ACCTS_PAY_CODE_COMBINATION_ID = X_ACCTS_PAY_CCID,
-- Removed for bug 4277744
-- USSGL_TRANSACTION_CODE = X_USSGL_TRANSACTION_CODE,
EXCLUSIVE_PAYMENT_FLAG = X_EXCLUSIVE_PAYMENT_FLAG,
INVOICE_INCLUDES_PREPAY_FLAG = X_INVOICE_INCLUDES_PREPAY_FLAG,
PREPAY_NUM = X_PREPAY_NUM,
PREPAY_APPLY_AMOUNT = X_PREPAY_APPLY_AMOUNT,
PREPAY_GL_DATE = X_PREPAY_GL_DATE,
INVOICE_RECEIVED_DATE = X_INVOICE_RECEIVED_DATE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
TERMS_DATE = X_TERMS_DATE,
REQUESTER_ID = X_REQUESTER_ID,
OPERATING_UNIT = X_OPERATING_UNIT,
-- Invoice Lines Project Stage 1
PREPAY_LINE_NUM = X_PREPAY_LINE_NUM,
REQUESTER_FIRST_NAME = X_REQUESTER_FIRST_NAME,
REQUESTER_LAST_NAME = X_REQUESTER_LAST_NAME,
REQUESTER_EMPLOYEE_NUM = X_REQUESTER_EMPLOYEE_NUM,
-- eTax Uptake
CALC_TAX_DURING_IMPORT_FLAG = X_CALC_TAX_DURING_IMPORT_FLAG,
CONTROL_AMOUNT = X_CONTROL_AMOUNT,
ADD_TAX_TO_INV_AMT_FLAG = X_ADD_TAX_TO_INV_AMT_FLAG,
TAX_RELATED_INVOICE_ID = X_TAX_RELATED_INVOICE_ID,
TAXATION_COUNTRY = X_TAXATION_COUNTRY,
DOCUMENT_SUB_TYPE = X_DOCUMENT_SUB_TYPE,
SUPPLIER_TAX_INVOICE_NUMBER = X_SUPPLIER_TAX_INVOICE_NUMBER,
SUPPLIER_TAX_INVOICE_DATE = X_SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_TAX_EXCHANGE_RATE = X_SUPPLIER_TAX_EXCHANGE_RATE,
TAX_INVOICE_RECORDING_DATE = X_TAX_INVOICE_RECORDING_DATE,
TAX_INVOICE_INTERNAL_SEQ = X_TAX_INVOICE_INTERNAL_SEQ,
LEGAL_ENTITY_ID = X_LEGAL_ENTITY_ID,
PAYMENT_METHOD_CODE = x_PAYMENT_METHOD_CODE,
PAYMENT_REASON_CODE = x_PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS = X_PAYMENT_REASON_COMMENTS,
UNIQUE_REMITTANCE_IDENTIFIER = x_UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT = x_URI_CHECK_DIGIT,
BANK_CHARGE_BEARER = x_BANK_CHARGE_BEARER,
DELIVERY_CHANNEL_CODE = x_DELIVERY_CHANNEL_CODE,
SETTLEMENT_PRIORITY = x_SETTLEMENT_PRIORITY,
REMITTANCE_MESSAGE1 = X_REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2 = X_REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3 = X_REMITTANCE_MESSAGE3,
NET_OF_RETAINAGE_FLAG = X_NET_OF_RETAINAGE_FLAG,
PORT_OF_ENTRY_CODE = X_PORT_OF_ENTRY_CODE,
APPLICATION_ID = X_APPLICATION_ID,
PRODUCT_TABLE = X_PRODUCT_TABLE,
REFERENCE_KEY1 = X_REFERENCE_KEY1,
REFERENCE_KEY2 = X_REFERENCE_KEY2,
REFERENCE_KEY3 = X_REFERENCE_KEY3,
REFERENCE_KEY4 = X_REFERENCE_KEY4,
REFERENCE_KEY5 = X_REFERENCE_KEY5,
PARTY_ID = X_PARTY_ID,
PARTY_SITE_ID = X_PARTY_SITE_ID,
PAY_PROC_TRXN_TYPE_CODE = X_PAY_PROC_TRXN_TYPE_CODE,
PAYMENT_FUNCTION = X_PAYMENT_FUNCTION,
PAYMENT_PRIORITY = X_PAYMENT_PRIORITY,
external_bank_account_id = x_external_bank_account_id,
REMIT_TO_SUPPLIER_NAME = x_remit_to_supplier_name,
REMIT_TO_SUPPLIER_ID = x_remit_to_supplier_id,
REMIT_TO_SUPPLIER_SITE = x_remit_to_supplier_site,
REMIT_TO_SUPPLIER_SITE_ID = x_remit_to_supplier_site_id,
RELATIONSHIP_ID = x_relationship_id
WHERE INVOICE_ID = X_INVOICE_ID;
END UPDATE_ROW;
procedure DELETE_ROW (
X_INVOICE_ID IN NUMBER)
IS
BEGIN
-- Bug 2496745. Deleting the interface lines and rejections
-- when the invoice is deleted.
/* Delete invoice rejections from the rejections table */
DELETE FROM ap_interface_rejections
WHERE parent_id = X_INVOICE_ID AND
parent_table = 'AP_INVOICES_INTERFACE';
/* Delete invoice lines rejections from the rejections table */
DELETE FROM ap_interface_rejections
WHERE parent_id IN (SELECT invoice_line_id
FROM ap_invoice_lines_interface
WHERE invoice_id = X_INVOICE_ID)
and parent_table = 'AP_INVOICE_LINES_INTERFACE';
/* Delete invoice lines from interface */
DELETE FROM ap_invoice_lines_interface
WHERE invoice_id = X_INVOICE_ID;
/* Delete invoices from interface */
DELETE FROM AP_INVOICES_INTERFACE
WHERE INVOICE_ID = X_INVOICE_ID;
END DELETE_ROW;