[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 OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Vendor_Id NUMBER,
X_Invoice_Num VARCHAR2,
X_Invoice_Amount NUMBER,
X_Vendor_Site_Id NUMBER,
X_Amount_Paid NUMBER,
X_Discount_Amount_Taken NUMBER,
X_Invoice_Date DATE,
X_Source VARCHAR2,
X_Invoice_Type_Lookup_Code VARCHAR2,
X_Description VARCHAR2,
X_Batch_Id NUMBER,
X_Amt_Applicable_To_Discount NUMBER,
X_Terms_Id NUMBER,
X_Terms_Date DATE,
X_Goods_Received_Date DATE,
X_Invoice_Received_Date DATE,
X_Voucher_Num VARCHAR2,
X_Approved_Amount NUMBER,
X_Approval_Status VARCHAR2,
X_Approval_Description VARCHAR2,
X_Pay_Group_Lookup_Code VARCHAR2,
X_Set_Of_Books_Id NUMBER,
X_Accts_Pay_CCId NUMBER,
X_Recurring_Payment_Id NUMBER,
X_Invoice_Currency_Code VARCHAR2,
X_Payment_Currency_Code VARCHAR2,
X_Exchange_Rate NUMBER,
X_Payment_Amount_Total NUMBER,
X_Payment_Status_Flag VARCHAR2,
X_Posting_Status VARCHAR2,
X_Authorized_By VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Vendor_Prepay_Amount NUMBER,
X_Base_Amount NUMBER,
X_Exchange_Rate_Type VARCHAR2,
X_Exchange_Date DATE,
X_Payment_Cross_Rate NUMBER,
X_Payment_Cross_Rate_Type VARCHAR2,
X_Payment_Cross_Rate_Date Date,
X_Pay_Curr_Invoice_Amount NUMBER,
X_Last_Update_Login NUMBER,
X_Original_Prepayment_Amount NUMBER,
X_Earliest_Settlement_Date DATE,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Cancelled_Date DATE,
X_Cancelled_By NUMBER,
X_Cancelled_Amount NUMBER,
X_Temp_Cancelled_Amount NUMBER,
X_Exclusive_Payment_Flag VARCHAR2,
X_Po_Header_Id NUMBER,
X_Doc_Sequence_Id NUMBER,
X_Doc_Sequence_Value NUMBER,
X_Doc_Category_Code VARCHAR2,
X_Expenditure_Item_Date DATE,
X_Expenditure_Organization_Id NUMBER,
X_Expenditure_Type VARCHAR2,
X_Pa_Default_Dist_Ccid NUMBER,
X_Pa_Quantity NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_Awt_Flag VARCHAR2,
X_Awt_Group_Id NUMBER,
X_Pay_Awt_Group_Id NUMBER,--bug6639866
X_Reference_1 VARCHAR2,
X_Reference_2 VARCHAR2,
X_Org_Id NUMBER,
X_global_attribute_category VARCHAR2 DEFAULT NULL,
X_global_attribute1 VARCHAR2 DEFAULT NULL,
X_global_attribute2 VARCHAR2 DEFAULT NULL,
X_global_attribute3 VARCHAR2 DEFAULT NULL,
X_global_attribute4 VARCHAR2 DEFAULT NULL,
X_global_attribute5 VARCHAR2 DEFAULT NULL,
X_global_attribute6 VARCHAR2 DEFAULT NULL,
X_global_attribute7 VARCHAR2 DEFAULT NULL,
X_global_attribute8 VARCHAR2 DEFAULT NULL,
X_global_attribute9 VARCHAR2 DEFAULT NULL,
X_global_attribute10 VARCHAR2 DEFAULT NULL,
X_global_attribute11 VARCHAR2 DEFAULT NULL,
X_global_attribute12 VARCHAR2 DEFAULT NULL,
X_global_attribute13 VARCHAR2 DEFAULT NULL,
X_global_attribute14 VARCHAR2 DEFAULT NULL,
X_global_attribute15 VARCHAR2 DEFAULT NULL,
X_global_attribute16 VARCHAR2 DEFAULT NULL,
X_global_attribute17 VARCHAR2 DEFAULT NULL,
X_global_attribute18 VARCHAR2 DEFAULT NULL,
X_global_attribute19 VARCHAR2 DEFAULT NULL,
X_global_attribute20 VARCHAR2 DEFAULT NULL,
X_calling_sequence IN VARCHAR2,
X_gl_date DATE,
X_Award_Id NUMBER,
X_APPROVAL_ITERATION NUMBER DEFAULT NULL,
X_APPROVAL_READY_FLAG VARCHAR2 DEFAULT 'Y',
X_WFAPPROVAL_STATUS VARCHAR2 DEFAULT 'NOT REQUIRED',
X_REQUESTER_ID NUMBER DEFAULT NULL,
-- Invoice Lines Project Stage 1
X_QUICK_CREDIT VARCHAR2 DEFAULT NULL,
X_CREDITED_INVOICE_ID NUMBER DEFAULT NULL,
X_DISTRIBUTION_SET_ID NUMBER DEFAULT NULL,
--ETAX: Invwkb
X_FORCE_REVALIDATION_FLAG VARCHAR2 DEFAULT NULL,
X_CONTROL_AMOUNT NUMBER DEFAULT NULL,
X_TAX_RELATED_INVOICE_ID NUMBER DEFAULT NULL,
X_TRX_BUSINESS_CATEGORY VARCHAR2 DEFAULT NULL,
X_USER_DEFINED_FISC_CLASS VARCHAR2 DEFAULT NULL,
X_TAXATION_COUNTRY VARCHAR2 DEFAULT NULL,
X_DOCUMENT_SUB_TYPE VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_NUMBER VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_DATE DATE DEFAULT NULL,
X_SUPPLIER_TAX_EXCHANGE_RATE NUMBER DEFAULT NULL,
X_TAX_INVOICE_RECORDING_DATE DATE DEFAULT NULL,
X_TAX_INVOICE_INTERNAL_SEQ VARCHAR2 DEFAULT NULL, -- bug 8912305: modify
X_LEGAL_ENTITY_ID NUMBER DEFAULT NULL,
X_QUICK_PO_HEADER_ID NUMBER DEFAULT NULL,
x_PAYMENT_METHOD_CODE varchar2 ,
x_PAYMENT_REASON_CODE varchar2 default null,
X_PAYMENT_REASON_COMMENTS varchar2 default null,
x_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
x_URI_CHECK_DIGIT varchar2 default null,
x_BANK_CHARGE_BEARER varchar2 default null,
x_DELIVERY_CHANNEL_CODE varchar2 default null,
x_SETTLEMENT_PRIORITY varchar2 default null,
x_NET_OF_RETAINAGE_FLAG varchar2 default null,
x_RELEASE_AMOUNT_NET_OF_TAX number default null,
x_PORT_OF_ENTRY_CODE varchar2 default null,
x_external_bank_account_id number default null,
x_party_id number default null,
x_party_site_id number default null,
/* bug 4931755. Exclude Tax and Freight from Discount */
x_disc_is_inv_less_tax_flag varchar2 default null,
x_exclude_freight_from_disc varchar2 default null,
x_remit_msg1 varchar2 default null,
x_remit_msg2 varchar2 default null,
x_remit_msg3 varchar2 default null,
x_cust_registration_number varchar2 default null,
/* Third Party Payments*/
x_remit_to_supplier_name varchar2 default null,
x_remit_to_supplier_id number default null,
x_remit_to_supplier_site varchar2 default null,
x_remit_to_supplier_site_id number default null,
x_relationship_id number default null,
/* Bug 7831073 */
x_original_invoice_amount number default null,
x_dispute_reason varchar2 default null
) IS
current_calling_sequence VARCHAR2(2000);
'AP_INVOICES_PKG.INSERT_ROW<-'||X_calling_sequence;
AP_AI_TABLE_HANDLER_PKG.Insert_Row
(X_Rowid,
X_Invoice_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Vendor_Id,
X_Invoice_Num,
X_Invoice_Amount,
X_Vendor_Site_Id,
X_Amount_Paid,
X_Discount_Amount_Taken,
X_Invoice_Date,
X_Source,
X_Invoice_Type_Lookup_Code,
X_Description,
X_Batch_Id,
X_Amt_Applicable_To_Discount,
X_Terms_Id,
X_Terms_Date,
X_Goods_Received_Date,
X_Invoice_Received_Date,
X_Voucher_Num,
X_Approved_Amount,
X_Approval_Status,
X_Approval_Description,
X_Pay_Group_Lookup_Code,
X_Set_Of_Books_Id,
X_Accts_Pay_CCId,
X_Recurring_Payment_Id,
X_Invoice_Currency_Code,
X_Payment_Currency_Code,
X_Exchange_Rate,
X_Payment_Amount_Total,
X_Payment_Status_Flag,
X_Posting_Status,
X_Authorized_By,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Creation_Date,
X_Created_By,
X_Vendor_Prepay_Amount,
X_Base_Amount,
X_Exchange_Rate_Type,
X_Exchange_Date,
X_Payment_Cross_Rate,
X_Payment_Cross_Rate_Type,
X_Payment_Cross_Rate_Date,
X_Pay_Curr_Invoice_Amount,
X_Last_Update_Login,
X_Original_Prepayment_Amount,
X_Earliest_Settlement_Date,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute15,
X_Cancelled_Date,
X_Cancelled_By,
X_Cancelled_Amount,
X_Temp_Cancelled_Amount,
X_Exclusive_Payment_Flag,
X_Po_Header_Id,
X_Doc_Sequence_Id,
X_Doc_Sequence_Value,
X_Doc_Category_Code,
X_Expenditure_Item_Date,
X_Expenditure_Organization_Id,
X_Expenditure_Type,
X_Pa_Default_Dist_Ccid,
X_Pa_Quantity,
X_Project_Id,
X_Task_Id,
X_Awt_Flag,
X_Awt_Group_Id,
X_Pay_Awt_Group_Id,--bug6639866
X_Reference_1,
X_Reference_2,
X_Org_id,
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,
current_calling_sequence,
X_gl_date,
X_Award_Id,
X_APPROVAL_ITERATION,
X_approval_ready_flag,
X_wfapproval_status,
NULL,
NULL,
NULL,
X_requester_id, --2289496
-- Invoice Lines Project Stage 1
X_quick_credit,
X_credited_invoice_id,
X_distribution_set_id,
--Etax: Invwkb
X_force_revalidation_flag,
X_CONTROL_AMOUNT,
X_TAX_RELATED_INVOICE_ID,
X_TRX_BUSINESS_CATEGORY,
X_USER_DEFINED_FISC_CLASS,
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,
NVL(X_LEGAL_ENTITY_ID,l_le_id),
X_QUICK_PO_HEADER_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_NET_OF_RETAINAGE_FLAG,
x_RELEASE_AMOUNT_NET_OF_TAX,
x_PORT_OF_ENTRY_CODE,
x_external_bank_account_id,
x_party_id,
x_party_site_id,
x_disc_is_inv_less_tax_flag,
x_exclude_freight_from_disc,
x_remit_msg1,
x_remit_msg2,
x_remit_msg3,
x_cust_registration_number,
x_remit_to_supplier_name,
x_remit_to_supplier_id,
x_remit_to_supplier_site,
x_remit_to_supplier_site_id,
x_relationship_id,
/* Bug 7831073 */
x_original_invoice_amount,
x_dispute_reason
);
END Insert_Row;
SELECT
INVOICE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_ID,
rtrim(INVOICE_NUM) INVOICE_NUM,
SET_OF_BOOKS_ID,
rtrim(INVOICE_CURRENCY_CODE) INVOICE_CURRENCY_CODE,
rtrim(PAYMENT_CURRENCY_CODE) PAYMENT_CURRENCY_CODE,
PAYMENT_CROSS_RATE,
INVOICE_AMOUNT,
VENDOR_SITE_ID,
AMOUNT_PAID,
DISCOUNT_AMOUNT_TAKEN,
INVOICE_DATE,
rtrim(SOURCE) SOURCE,
rtrim(INVOICE_TYPE_LOOKUP_CODE) INVOICE_TYPE_LOOKUP_CODE,
rtrim(DESCRIPTION) DESCRIPTION,
BATCH_ID,
AMOUNT_APPLICABLE_TO_DISCOUNT,
TERMS_ID,
TERMS_DATE,
rtrim(PAY_GROUP_LOOKUP_CODE) PAY_GROUP_LOOKUP_CODE,
ACCTS_PAY_CODE_COMBINATION_ID,
PAYMENT_STATUS_FLAG,
CREATION_DATE,
CREATED_BY,
BASE_AMOUNT,
LAST_UPDATE_LOGIN,
EXCLUSIVE_PAYMENT_FLAG,
PO_HEADER_ID,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
rtrim(VOUCHER_NUM) VOUCHER_NUM,
APPROVED_AMOUNT,
RECURRING_PAYMENT_ID,
EXCHANGE_RATE,
rtrim(EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
EARLIEST_SETTLEMENT_DATE,
ORIGINAL_PREPAYMENT_AMOUNT,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
DOC_CATEGORY_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
rtrim(ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY,
rtrim(APPROVAL_STATUS) APPROVAL_STATUS,
rtrim(APPROVAL_DESCRIPTION) APPROVAL_DESCRIPTION,
POSTING_STATUS,
AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID) POSTING_FLAG,
AUTHORIZED_BY,
CANCELLED_DATE,
CANCELLED_BY,
CANCELLED_AMOUNT,
TEMP_CANCELLED_AMOUNT,
PROJECT_ID,
TASK_ID,
rtrim(EXPENDITURE_TYPE) EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
EXPENDITURE_ORGANIZATION_ID,
PA_DEFAULT_DIST_CCID,
VENDOR_PREPAY_AMOUNT,
PAYMENT_AMOUNT_TOTAL,
AWT_FLAG,
AWT_GROUP_ID,
PAY_AWT_GROUP_ID, --bug6639866
REFERENCE_1,
REFERENCE_2,
ORG_ID,
rtrim(GLOBAL_ATTRIBUTE_CATEGORY) 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,
rtrim(PAYMENT_CROSS_RATE_TYPE) PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE,
PAY_CURR_INVOICE_AMOUNT,
MRC_BASE_AMOUNT,
MRC_EXCHANGE_RATE,
rtrim(MRC_EXCHANGE_RATE_TYPE) MRC_EXCHANGE_RATE_TYPE,
MRC_EXCHANGE_DATE,
GL_DATE,
AWARD_ID,
APPROVAL_ITERATION,
APPROVAL_READY_FLAG,
rtrim(WFAPPROVAL_STATUS) WFAPPROVAL_STATUS,
REQUESTER_ID, --2289496
-- Invoice Lines Project Stage 1
QUICK_CREDIT,
CREDITED_INVOICE_ID,
DISTRIBUTION_SET_ID,
FORCE_REVALIDATION_FLAG,
CONTROL_AMOUNT,
TAX_RELATED_INVOICE_ID,
rtrim(TRX_BUSINESS_CATEGORY) TRX_BUSINESS_CATEGORY,
rtrim(USER_DEFINED_FISC_CLASS) USER_DEFINED_FISC_CLASS,
rtrim(TAXATION_COUNTRY) TAXATION_COUNTRY,
rtrim(DOCUMENT_SUB_TYPE) DOCUMENT_SUB_TYPE,
rtrim(SUPPLIER_TAX_INVOICE_NUMBER) SUPPLIER_TAX_INVOICE_NUMBER,
SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_TAX_EXCHANGE_RATE,
TAX_INVOICE_RECORDING_DATE,
TAX_INVOICE_INTERNAL_SEQ,
QUICK_PO_HEADER_ID,
rtrim(PAYMENT_METHOD_CODE) PAYMENT_METHOD_CODE,
rtrim(PAYMENT_REASON_CODE) PAYMENT_REASON_CODE,
rtrim(PAYMENT_REASON_COMMENTS) PAYMENT_REASON_COMMENTS,
rtrim(UNIQUE_REMITTANCE_IDENTIFIER) UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
rtrim(BANK_CHARGE_BEARER) BANK_CHARGE_BEARER,
rtrim(DELIVERY_CHANNEL_CODE) DELIVERY_CHANNEL_CODE,
rtrim(SETTLEMENT_PRIORITY) SETTLEMENT_PRIORITY,
NET_OF_RETAINAGE_FLAG,
RELEASE_AMOUNT_NET_OF_TAX,
rtrim(PORT_OF_ENTRY_CODE) PORT_OF_ENTRY_CODE,
external_bank_account_id,
party_id,
party_site_id,
disc_is_inv_less_tax_flag,
exclude_freight_from_discount,
rtrim(REMITTANCE_MESSAGE1) REMITTANCE_MESSAGE1,
rtrim(REMITTANCE_MESSAGE2) REMITTANCE_MESSAGE2,
rtrim(REMITTANCE_MESSAGE3) REMITTANCE_MESSAGE3,
rtrim(REMIT_TO_SUPPLIER_NAME) REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
rtrim(REMIT_TO_SUPPLIER_SITE) REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID,
/* Bug 7831073 */
original_invoice_amount,
rtrim(dispute_reason) dispute_reason
FROM ap_invoices_all
WHERE rowid = X_Rowid
FOR UPDATE of Invoice_Id NOWAIT;
SELECT
INVOICE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_ID,
INVOICE_NUM,
SET_OF_BOOKS_ID,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
PAYMENT_CROSS_RATE,
INVOICE_AMOUNT,
VENDOR_SITE_ID,
AMOUNT_PAID,
DISCOUNT_AMOUNT_TAKEN,
INVOICE_DATE,
SOURCE,
INVOICE_TYPE_LOOKUP_CODE,
DESCRIPTION,
BATCH_ID,
AMOUNT_APPLICABLE_TO_DISCOUNT,
TERMS_ID,
TERMS_DATE,
PAY_GROUP_LOOKUP_CODE,
ACCTS_PAY_CODE_COMBINATION_ID,
PAYMENT_STATUS_FLAG,
CREATION_DATE,
CREATED_BY,
BASE_AMOUNT,
LAST_UPDATE_LOGIN,
EXCLUSIVE_PAYMENT_FLAG,
PO_HEADER_ID,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
VOUCHER_NUM,
APPROVED_AMOUNT,
RECURRING_PAYMENT_ID,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
EARLIEST_SETTLEMENT_DATE,
ORIGINAL_PREPAYMENT_AMOUNT,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
DOC_CATEGORY_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
APPROVAL_STATUS,
APPROVAL_DESCRIPTION,
POSTING_STATUS,
AUTHORIZED_BY,
CANCELLED_DATE,
CANCELLED_BY,
CANCELLED_AMOUNT,
TEMP_CANCELLED_AMOUNT,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
PA_QUANTITY,
EXPENDITURE_ORGANIZATION_ID,
PA_DEFAULT_DIST_CCID,
VENDOR_PREPAY_AMOUNT,
PAYMENT_AMOUNT_TOTAL,
AWT_FLAG,
AWT_GROUP_ID,
PAY_AWT_GROUP_ID, -- bug6639866
REFERENCE_1,
REFERENCE_2,
ORG_ID,
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,
PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE,
PAY_CURR_INVOICE_AMOUNT,
MRC_BASE_AMOUNT,
MRC_EXCHANGE_RATE,
MRC_EXCHANGE_RATE_TYPE,
MRC_EXCHANGE_DATE,
GL_DATE,
AWARD_ID,
APPROVAL_ITERATION,
APPROVAL_READY_FLAG,
WFAPPROVAL_STATUS,
REQUESTER_ID, --2289496
-- Invoice Lines Project Stage 1
QUICK_CREDIT,
CREDITED_INVOICE_ID,
DISTRIBUTION_SET_ID,
QUICK_PO_HEADER_ID,
PAYMENT_METHOD_CODE,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
BANK_CHARGE_BEARER,
DELIVERY_CHANNEL_CODE,
SETTLEMENT_PRIORITY,
NET_OF_RETAINAGE_FLAG,
RELEASE_AMOUNT_NET_OF_TAX,
PORT_OF_ENTRY_CODE,
external_bank_account_id,
party_id,
party_site_id,
disc_is_inv_less_tax_flag,
exclude_freight_from_discount,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID,
/* Bug 7831073 */
original_invoice_amount,
dispute_reason
FROM ap_invoices_all
WHERE invoice_id = X_Invoice_id
FOR UPDATE of Invoice_Id NOWAIT;
PROCEDURE Update_Row(
X_Rowid VARCHAR2,
X_Invoice_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Vendor_Id NUMBER,
X_Invoice_Num VARCHAR2,
X_Invoice_Amount NUMBER,
X_Vendor_Site_Id NUMBER,
X_Amount_Paid NUMBER,
X_Discount_Amount_Taken NUMBER,
X_Invoice_Date DATE,
X_Source VARCHAR2,
X_Invoice_Type_Lookup_Code VARCHAR2,
X_Description VARCHAR2,
X_Batch_Id NUMBER,
X_Amt_Applicable_To_Discount NUMBER,
X_Terms_Id NUMBER,
X_Terms_Date DATE,
X_Goods_Received_Date DATE,
X_Invoice_Received_Date DATE,
X_Voucher_Num VARCHAR2,
X_Approved_Amount NUMBER,
X_Approval_Status VARCHAR2,
X_Approval_Description VARCHAR2,
X_Pay_Group_Lookup_Code VARCHAR2,
X_Set_Of_Books_Id NUMBER,
X_Accts_Pay_CCId NUMBER,
X_Recurring_Payment_Id NUMBER,
X_Invoice_Currency_Code VARCHAR2,
X_Payment_Currency_Code VARCHAR2,
X_Exchange_Rate NUMBER,
X_Payment_Amount_Total NUMBER,
X_Payment_Status_Flag VARCHAR2,
X_Posting_Status VARCHAR2,
X_Authorized_By VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Vendor_Prepay_Amount NUMBER,
X_Base_Amount NUMBER,
X_Exchange_Rate_Type VARCHAR2,
X_Exchange_Date DATE,
X_Payment_Cross_Rate NUMBER,
X_Payment_Cross_Rate_Type VARCHAR2,
X_Payment_Cross_Rate_Date DATE,
X_Pay_Curr_Invoice_Amount NUMBER,
X_Last_Update_Login NUMBER,
X_Original_Prepayment_Amount NUMBER,
X_Earliest_Settlement_Date DATE,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Cancelled_Date DATE,
X_Cancelled_By NUMBER,
X_Cancelled_Amount NUMBER,
X_Temp_Cancelled_Amount NUMBER,
X_Exclusive_Payment_Flag VARCHAR2,
X_Po_Header_Id NUMBER,
X_Doc_Sequence_Id NUMBER,
X_Doc_Sequence_Value NUMBER,
X_Doc_Category_Code VARCHAR2,
X_Expenditure_Item_Date DATE,
X_Expenditure_Organization_Id NUMBER,
X_Expenditure_Type VARCHAR2,
X_Pa_Default_Dist_Ccid NUMBER,
X_Pa_Quantity NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_Awt_Flag VARCHAR2,
X_Awt_Group_Id NUMBER,
X_Pay_Awt_Group_Id NUMBER,--bug6639866
X_Reference_1 VARCHAR2,
X_Reference_2 VARCHAR2,
X_Org_Id NUMBER,
X_global_attribute_category VARCHAR2 DEFAULT NULL,
X_global_attribute1 VARCHAR2 DEFAULT NULL,
X_global_attribute2 VARCHAR2 DEFAULT NULL,
X_global_attribute3 VARCHAR2 DEFAULT NULL,
X_global_attribute4 VARCHAR2 DEFAULT NULL,
X_global_attribute5 VARCHAR2 DEFAULT NULL,
X_global_attribute6 VARCHAR2 DEFAULT NULL,
X_global_attribute7 VARCHAR2 DEFAULT NULL,
X_global_attribute8 VARCHAR2 DEFAULT NULL,
X_global_attribute9 VARCHAR2 DEFAULT NULL,
X_global_attribute10 VARCHAR2 DEFAULT NULL,
X_global_attribute11 VARCHAR2 DEFAULT NULL,
X_global_attribute12 VARCHAR2 DEFAULT NULL,
X_global_attribute13 VARCHAR2 DEFAULT NULL,
X_global_attribute14 VARCHAR2 DEFAULT NULL,
X_global_attribute15 VARCHAR2 DEFAULT NULL,
X_global_attribute16 VARCHAR2 DEFAULT NULL,
X_global_attribute17 VARCHAR2 DEFAULT NULL,
X_global_attribute18 VARCHAR2 DEFAULT NULL,
X_global_attribute19 VARCHAR2 DEFAULT NULL,
X_global_attribute20 VARCHAR2 DEFAULT NULL,
X_calling_sequence IN VARCHAR2,
X_gl_date DATE,
X_award_Id NUMBER,
X_approval_iteration NUMBER,
X_approval_ready_flag VARCHAR2,
X_wfapproval_status VARCHAR2,
X_requester_id NUMBER DEFAULT NULL,
-- Invoice Lines Project Stage 1
X_quick_credit VARCHAR2 DEFAULT NULL,
X_credited_invoice_id NUMBER DEFAULT NULL,
X_distribution_set_id NUMBER DEFAULT NULL,
--ETAX: Invwkb
X_FORCE_REVALIDATION_FLAG VARCHAR2 DEFAULT NULL,
X_CONTROL_AMOUNT NUMBER DEFAULT NULL,
X_TAX_RELATED_INVOICE_ID NUMBER DEFAULT NULL,
X_TRX_BUSINESS_CATEGORY VARCHAR2 DEFAULT NULL,
X_USER_DEFINED_FISC_CLASS VARCHAR2 DEFAULT NULL,
X_TAXATION_COUNTRY VARCHAR2 DEFAULT NULL,
X_DOCUMENT_SUB_TYPE VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_NUMBER VARCHAR2 DEFAULT NULL,
X_SUPPLIER_TAX_INVOICE_DATE DATE DEFAULT NULL,
X_SUPPLIER_TAX_EXCHANGE_RATE NUMBER DEFAULT NULL,
X_TAX_INVOICE_RECORDING_DATE DATE DEFAULT NULL,
X_TAX_INVOICE_INTERNAL_SEQ VARCHAR2 DEFAULT NULL, -- bug 8912305: modify
X_QUICK_PO_HEADER_ID NUMBER DEFAULT NULL,
x_PAYMENT_METHOD_CODE varchar2 ,
x_PAYMENT_REASON_CODE varchar2 default null,
X_PAYMENT_REASON_COMMENTS varchar2 default null,
x_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
x_URI_CHECK_DIGIT varchar2 default null,
x_BANK_CHARGE_BEARER varchar2 default null,
x_DELIVERY_CHANNEL_CODE varchar2 default null,
x_SETTLEMENT_PRIORITY varchar2 default null,
x_NET_OF_RETAINAGE_FLAG varchar2 default null,
x_RELEASE_AMOUNT_NET_OF_TAX number default null,
x_PORT_OF_ENTRY_CODE varchar2 default null,
x_external_bank_account_id number default null,
x_party_id number default null,
x_party_site_id number default null,
/* bug 4931755. Exclude Tax and Freight from Discount */
x_disc_is_inv_less_tax_flag varchar2 default null,
x_exclude_freight_from_disc varchar2 default null,
x_remit_msg1 varchar2 default null,
x_remit_msg2 varchar2 default null,
x_remit_msg3 varchar2 default null,
/* Third Party Payments*/
x_remit_to_supplier_name varchar2 default null,
x_remit_to_supplier_id number default null,
x_remit_to_supplier_site varchar2 default null,
x_remit_to_supplier_site_id number default null,
x_relationship_id number default null,
/* Bug 7831073 */
x_original_invoice_amount number default null,
x_dispute_reason varchar2 default null
) IS
current_calling_sequence VARCHAR2(2000);
l_is_update_required VARCHAR2(2000) ;
l_api_name CONSTANT VARCHAR2(100) := 'Update_Row';
current_calling_sequence := 'AP_INVOICES_PKG.UPDATE_ROW<-'||
X_calling_sequence;
SELECT CASE WHEN NULLIF(X_Invoice_Num,ai.invoice_num) IS NOT NULL
AND EXISTS ( SELECT 1
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND aid.accounting_event_id IS NOT NULL
AND rownum = 1
)
THEN 'TRUE'
ELSE 'FALSE'
END
INTO l_is_update_required
FROM ap_invoices ai
WHERE rowid = X_Rowid ;
debug_info := 'l_is_update_required = ' || l_is_update_required ;
debug_info := 'Update ap_invoices';
AP_AI_TABLE_HANDLER_PKG.Update_Row
(X_Rowid,
X_Invoice_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Vendor_Id,
X_Invoice_Num,
X_Invoice_Amount,
X_Vendor_Site_Id,
X_Amount_Paid,
X_Discount_Amount_Taken,
X_Invoice_Date,
X_Source,
X_Invoice_Type_Lookup_Code,
X_Description,
X_Batch_Id,
X_Amt_Applicable_To_Discount,
X_Terms_Id,
X_Terms_Date,
X_Goods_Received_Date,
X_Invoice_Received_Date,
X_Voucher_Num,
X_Approved_Amount,
X_Approval_Status,
X_Approval_Description,
X_Pay_Group_Lookup_Code,
X_Set_Of_Books_Id,
X_Accts_Pay_CCId,
X_Recurring_Payment_Id,
X_Invoice_Currency_Code,
X_Payment_Currency_Code,
X_Exchange_Rate,
X_Payment_Amount_Total,
X_Payment_Status_Flag,
X_Posting_Status,
X_Authorized_By,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Vendor_Prepay_Amount,
X_Base_Amount,
X_Exchange_Rate_Type,
X_Exchange_Date,
X_Payment_Cross_Rate,
X_Payment_Cross_Rate_Type,
X_Payment_Cross_Rate_Date,
X_Pay_Curr_Invoice_Amount,
X_Last_Update_Login,
X_Original_Prepayment_Amount,
X_Earliest_Settlement_Date,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute15,
X_Cancelled_Date,
X_Cancelled_By,
X_Cancelled_Amount,
X_Temp_Cancelled_Amount,
X_Exclusive_Payment_Flag,
X_Po_Header_Id,
X_Doc_Sequence_Id,
X_Doc_Sequence_Value,
X_Doc_Category_Code,
X_Expenditure_Item_Date,
X_Expenditure_Organization_Id,
X_Expenditure_Type,
X_Pa_Default_Dist_Ccid,
X_Pa_Quantity,
X_Project_Id,
X_Task_Id,
X_Awt_Flag,
X_Awt_Group_Id,
X_Pay_Awt_Group_Id,--bug6639866
X_Reference_1,
X_Reference_2,
X_Org_id,
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,
current_calling_sequence,
X_gl_date,
X_Award_Id,
X_approval_iteration,
X_approval_ready_flag,
X_wfapproval_status,
X_requester_id , --2289496
-- Invoice Lines Project Stage 1
X_quick_credit,
X_credited_invoice_id,
X_distribution_set_id,
X_FORCE_REVALIDATION_FLAG,
X_CONTROL_AMOUNT,
X_TAX_RELATED_INVOICE_ID,
X_TRX_BUSINESS_CATEGORY,
X_USER_DEFINED_FISC_CLASS,
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_QUICK_PO_HEADER_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_NET_OF_RETAINAGE_FLAG,
x_RELEASE_AMOUNT_NET_OF_TAX,
x_PORT_OF_ENTRY_CODE,
x_external_bank_account_id,
x_party_id,
x_party_site_id,
x_disc_is_inv_less_tax_flag,
x_exclude_freight_from_disc,
x_remit_msg1,
x_remit_msg2,
x_remit_msg3,
x_remit_to_supplier_name,
x_remit_to_supplier_id,
x_remit_to_supplier_site,
x_remit_to_supplier_site_id,
x_relationship_id,
/* Bug 7831073 */
x_original_invoice_amount,
x_dispute_reason
);
IF l_is_update_required = 'TRUE' THEN
debug_info := 'Calling Get_Invoice_LE';
debug_info:= 'Before calling XLA_EVENTS_PUB_PKG.update_transaction_number()';
XLA_EVENTS_PUB_PKG.update_transaction_number( l_event_source_info,
X_invoice_num,
NULL,
l_event_security_context,
NULL
);
debug_info:= 'After calling XLA_EVENTS_PUB_PKG.update_transaction_number()';
END Update_Row;
PROCEDURE Delete_Row(
X_Rowid VARCHAR2,
X_calling_sequence IN VARCHAR2)
IS
l_prepayments_applied_flag VARCHAR2(1);
l_selected_for_payment_flag VARCHAR2(1);
current_calling_sequence := 'AP_INVOICES_PKG.DELETE_ROW<-'||
X_calling_sequence;
SELECT invoice_id
INTO l_invoice_id
FROM ap_invoices
WHERE rowid = X_rowid;
SELECT
ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
ap_invoices_pkg.get_encumbered_flag(invoice_id),
ap_invoices_pkg.get_payments_exist_flag(invoice_id),
ap_invoices_pkg.selected_for_payment_flag(invoice_id),
ap_invoices_pkg.get_posting_status(invoice_id),
ap_invoices_pkg.get_po_number(invoice_id),
ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
payment_status_flag, invoice_type_lookup_code), -- Bug 5497262
invoice_type_lookup_code
INTO
l_prepayments_applied_flag,
l_encumbered_flag,
l_payments_exist_flag,
l_selected_for_payment_flag,
l_posting_flag,
l_po_number,
l_prepay_amount_applied,
l_approval_status,
l_invoice_type
FROM ap_invoices
WHERE rowid = X_Rowid;
l_message_name := 'AP_INV_NO_UPDATE_APPROVED_INV';
l_message_name := 'AP_INV_NO_UPDATE_PAID_INV';
ELSIF (l_selected_for_payment_flag = 'Y') THEN
l_message_name := 'AP_INV_SELECTED_INVOICE';
AP_AI_TABLE_HANDLER_PKG.Delete_Row(
X_Rowid,
current_calling_sequence);
END Delete_Row;
SELECT nvl(max(line_number),0)
INTO l_max_line_number
FROM ap_invoice_lines
WHERE invoice_id = X_invoice_id;
SELECT decode(destination_type_code, 'EXPENSE',
expenditure_item_date,
NULL)
INTO l_po_date
FROM po_distributions
WHERE po_distribution_id = X_po_dist_id;
SELECT transaction_date
INTO l_rcv_date
FROM rcv_transactions
WHERE transaction_id = X_rcv_trx_id;
SELECT NVL(P_Batch_GL_Date,
DECODE(SP.gl_date_from_receipt_flag,
'S',TRUNC(SYSDATE),
'Y',y_date,
'N',n_date,
TRUNC(P_Date)))
INTO l_current_date
FROM ap_system_parameters_all SP --5126689
WHERE sp.org_id = p_org_id;
FUNCTION selected_for_payment_flag (
P_invoice_id IN NUMBER) RETURN VARCHAR2
IS
l_flag VARCHAR2(1) := 'N';
l_flag := AP_INVOICES_UTILITY_PKG.selected_for_payment_flag (P_invoice_id);
END selected_for_payment_flag;
PROCEDURE insert_children (
X_invoice_id IN NUMBER,
X_Payment_Priority IN NUMBER,
X_Hold_count IN OUT NOCOPY NUMBER,
X_Line_count IN OUT NOCOPY NUMBER,
X_Line_Total IN OUT NOCOPY NUMBER,
X_calling_sequence IN VARCHAR2,
X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
IS
BEGIN
AP_INVOICES_POST_PROCESS_PKG.insert_children (
X_invoice_id,
X_Payment_Priority,
X_Hold_count,
X_Line_count,
X_Line_Total,
X_calling_sequence,
X_Sched_Hold_count); --bug 5334577
END insert_children;
X_event IN VARCHAR2 DEFAULT 'UPDATE',
X_update_base IN VARCHAR2 DEFAULT 'N',
X_vendor_changed_flag IN VARCHAR2 DEFAULT 'N',
X_calling_sequence IN VARCHAR2)
IS
BEGIN
AP_INVOICES_POST_PROCESS_PKG.create_holds (
X_invoice_id,
X_event,
X_update_base,
X_vendor_changed_flag,
X_calling_sequence);
PROCEDURE invoice_pre_update (
X_invoice_id IN NUMBER,
X_invoice_amount IN NUMBER,
X_payment_status_flag IN OUT NOCOPY VARCHAR2,
X_invoice_type_lookup_code IN VARCHAR2,
X_last_updated_by IN NUMBER,
X_accts_pay_ccid IN NUMBER,
X_terms_id IN NUMBER,
X_terms_date IN DATE,
X_discount_amount IN NUMBER,
X_exchange_rate_type IN VARCHAR2,
X_exchange_date IN DATE,
X_exchange_rate IN NUMBER,
X_vendor_id IN NUMBER,
X_payment_method_code IN VARCHAR2,
X_message1 IN OUT NOCOPY VARCHAR2,
X_message2 IN OUT NOCOPY VARCHAR2,
X_reset_match_status IN OUT NOCOPY VARCHAR2,
X_vendor_changed_flag IN OUT NOCOPY VARCHAR2,
X_recalc_pay_sched IN OUT NOCOPY VARCHAR2,
X_liability_adjusted_flag IN OUT NOCOPY VARCHAR2,
X_external_bank_account_id IN NUMBER, --bug 7714053
X_payment_currency_code IN VARCHAR2, --Bug9294551
X_calling_sequence IN VARCHAR2,
X_revalidate_ps IN OUT NOCOPY VARCHAR2)
IS
BEGIN
AP_INVOICES_POST_PROCESS_PKG.invoice_pre_update (
X_invoice_id,
X_invoice_amount,
X_payment_status_flag,
X_invoice_type_lookup_code,
X_last_updated_by,
X_accts_pay_ccid,
X_terms_id,
X_terms_date,
X_discount_amount,
X_exchange_rate_type,
X_exchange_date,
X_exchange_rate,
X_vendor_id,
X_payment_method_code,
X_message1,
X_message2,
X_reset_match_status,
X_vendor_changed_flag,
X_recalc_pay_sched,
X_liability_adjusted_flag,
X_external_bank_account_id, --bug 7714053
X_payment_currency_code, --Bug9294551
X_calling_sequence,
X_revalidate_ps);
END invoice_pre_update;
PROCEDURE invoice_post_update (
X_invoice_id IN NUMBER,
X_payment_priority IN NUMBER,
X_recalc_pay_sched IN OUT NOCOPY VARCHAR2,
X_Hold_count IN OUT NOCOPY NUMBER,
X_update_base IN VARCHAR2,
X_vendor_changed_flag IN VARCHAR2,
X_calling_sequence IN VARCHAR2,
X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
IS
BEGIN
AP_INVOICES_POST_PROCESS_PKG.invoice_post_update (
X_invoice_id,
X_payment_priority,
X_recalc_pay_sched,
X_Hold_count,
X_update_base,
X_vendor_changed_flag,
X_calling_sequence,
X_Sched_Hold_count); --bug 5334577
END invoice_post_update;
X_update_base IN OUT NOCOPY VARCHAR2,
X_reset_match_status IN OUT NOCOPY VARCHAR2,
X_update_occurred IN OUT NOCOPY VARCHAR2,
X_approval_status_lookup_code IN OUT NOCOPY VARCHAR2,
X_holds_count IN OUT NOCOPY NUMBER,
X_posting_flag IN OUT NOCOPY VARCHAR2,
X_amount_paid IN OUT NOCOPY NUMBER,
X_highest_line_num IN OUT NOCOPY NUMBER,
X_line_total IN OUT NOCOPY NUMBER,
X_actual_invoice_count IN OUT NOCOPY NUMBER,
X_actual_invoice_total IN OUT NOCOPY NUMBER,
X_calling_sequence IN VARCHAR2,
X_sched_holds_count IN OUT NOCOPY NUMBER) IS --bug 5334577
BEGIN
AP_INVOICES_POST_PROCESS_PKG.post_forms_commit (
X_invoice_id,
NULL,
X_type_1099,
X_income_tax_region,
X_vendor_changed_flag,
X_update_base,
X_reset_match_status,
x_update_occurred,
X_approval_status_lookup_code,
X_holds_count,
X_posting_flag,
X_amount_paid,
X_highest_line_num,
X_line_total,
X_actual_invoice_count,
X_actual_invoice_total,
X_calling_sequence,
X_sched_holds_count); --bug 5334577
PROCEDURE Select_Summary(
X_Batch_ID IN NUMBER,
X_Total IN OUT NOCOPY NUMBER,
X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
X_Calling_Sequence IN VARCHAR2)
IS
BEGIN
AP_INVOICES_POST_PROCESS_PKG.Select_Summary(
X_Batch_ID,
X_Total,
X_Total_Rtot_DB,
X_Calling_Sequence);
END Select_Summary;
select SUM(decode(wfapproval_status,'NOT REQUIRED',1,0)) ,
SUM(decode(wfapproval_status,'APPROVED',1,0)) ,
SUM(decode(wfapproval_status,'NEEDS WFREAPPROVAL',1,0)) , /* Bug 11655111 */
SUM(decode(wfapproval_status,'REJECTED',1,0)),
SUM(decode(wfapproval_status,'INITIATED',1,0))
into l_not_required,l_approved,l_reapprove,l_rejected, l_initiated
from ap_invoice_lines_all
where invoice_id=p_invoice_id
and org_id=p_org_id;
select wfapproval_status
into header_approval_status
from ap_invoices_all
where invoice_id=p_invoice_id
and org_id=p_org_id;
cursor docs_to_be_inserted is
select
IBY_DOCS_PAYABLE_GT_S.nextval,
200,
ai.invoice_id,
aps.payment_num,
-- ai.invoice_num,
nvl(ai.pay_proc_trxn_type_code, decode(ai.invoice_type_lookup_code,'EXPENSE REPORT',
'EMPLOYEE_EXP','PAYABLES_DOC')) ,
APS.PAYMENT_METHOD_CODE, --4705834
aps.gross_amount,
nvl(ai.EXCLUSIVE_PAYMENT_FLAG,'N'),
-- As per the discussion with Omar/Jayanta, we will only
-- have payables payment function and no more employee expenses
-- payment function.
nvl(ai.PAYMENT_FUNCTION,'PAYABLES_DISB'),
ai.invoice_date,
ai.invoice_type_lookup_code,
ai.description,
aps.gross_amount ,
aps.EXTERNAL_BANK_ACCOUNT_ID, --4705834
nvl(ai.PARTY_ID,pv.party_id),
nvl(ai.PARTY_SITE_ID, pvs.party_site_id),
decode(sign(ai.vendor_site_id),1,ai.vendor_site_id,null),
ai.LEGAL_ENTITY_ID,
ai.ORG_ID ,
'OPERATING_UNIT',
ai.invoice_currency_code,
ai.PAYMENT_CURRENCY_CODE,
ai.BANK_CHARGE_BEARER ,
ai.PAYMENT_REASON_CODE ,
ai.PAYMENT_REASON_COMMENTS,
ai.SETTLEMENT_PRIORITY ,
aps.REMITTANCE_MESSAGE1 ,
aps.REMITTANCE_MESSAGE2 ,
aps.REMITTANCE_MESSAGE3 ,
ai.UNIQUE_REMITTANCE_IDENTIFIER ,
ai.URI_CHECK_DIGIT ,
ai.DELIVERY_CHANNEL_CODE ,
aps.DISCOUNT_DATE,
aps.CREATED_BY ,
sysdate ,
aps.LAST_UPDATED_BY ,
sysdate,
1,
aps.iby_hold_reason,
aps.hold_flag
from ap_invoices_all ai,
ap_payment_schedules_all aps,
ap_suppliers pv,
ap_supplier_sites_all pvs
where ai.invoice_id = p_invoice_id
and ai.invoice_id = aps.invoice_id
and nvl(p_payment_num, aps.payment_num) = aps.payment_num
and aps.payment_status_flag in ('N','P')
and aps.checkrun_id is null
/* Bug 5612834. Added outer-join for Payment request */
and ai.party_id = pv.party_id (+)
and ai.vendor_site_id = pvs.vendor_site_id(+);
l_LAST_UPDATED_BY number;
l_LAST_UPDATE_DATE date;
select error_message,
transaction_id
from IBY_TRANSACTION_ERRORS_GT
where transaction_id = p_document_payable_id;
open docs_to_be_inserted;
fetch docs_to_be_inserted into
l_DOCUMENT_PAYABLE_ID,
l_CALLING_APP_ID,
l_CALLING_APP_DOC_UNIQUE_REF1,
l_CALLING_APP_DOC_UNIQUE_REF2,
-- l_CALLING_APP_DOC_REF_NUMBER,
l_PAY_PROC_TRXN_TYPE_CODE,
l_PAYMENT_METHOD_CODE,
l_PAYMENT_AMOUNT,
l_EXCLUSIVE_PAYMENT_FLAG,
l_PAYMENT_FUNCTION,
l_DOCUMENT_DATE,
l_DOCUMENT_TYPE,
l_DOCUMENT_DESCRIPTION,
l_DOCUMENT_AMOUNT ,
l_EXTERNAL_BANK_ACCOUNT_ID,
l_PAYEE_PARTY_ID,
l_PAYEE_PARTY_SITE_ID,
l_SUPPLIER_SITE_ID,
l_LEGAL_ENTITY_ID,
l_ORG_ID ,
l_ORG_TYPE,
l_DOCUMENT_CURRENCY_CODE,
l_PAYMENT_CURRENCY_CODE,
l_BANK_CHARGE_BEARER ,
l_PAYMENT_REASON_CODE ,
l_PAYMENT_REASON_COMMENTS,
l_SETTLEMENT_PRIORITY ,
l_REMITTANCE_MESSAGE1 ,
l_REMITTANCE_MESSAGE2 ,
l_REMITTANCE_MESSAGE3 ,
l_UNIQUE_REMITTANCE_IDENTIFIER ,
l_URI_CHECK_DIGIT ,
l_DELIVERY_CHANNEL_CODE ,
l_DISCOUNT_DATE,
l_CREATED_BY ,
l_CREATION_DATE ,
l_LAST_UPDATED_BY ,
l_LAST_UPDATE_DATE,
l_OBJECT_VERSION_NUMBER,
l_iby_hold_reason,
l_hold_flag;
exit when docs_to_be_inserted%notfound;
insert into IBY_DOCS_PAYABLE_GT(
DOCUMENT_PAYABLE_ID,
CALLING_APP_ID,
CALLING_APP_DOC_UNIQUE_REF1,
CALLING_APP_DOC_UNIQUE_REF2,
-- CALLING_APP_DOC_REF_NUMBER,
PAY_PROC_TRXN_TYPE_CODE,
PAYMENT_METHOD_CODE,
PAYMENT_AMOUNT,
EXCLUSIVE_PAYMENT_FLAG,
PAYMENT_FUNCTION,
DOCUMENT_DATE,
DOCUMENT_TYPE,
DOCUMENT_DESCRIPTION,
DOCUMENT_AMOUNT ,
EXTERNAL_BANK_ACCOUNT_ID,
PAYEE_PARTY_ID,
PAYEE_PARTY_SITE_ID,
SUPPLIER_SITE_ID,
LEGAL_ENTITY_ID,
ORG_ID ,
ORG_TYPE,
DOCUMENT_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
BANK_CHARGE_BEARER ,
PAYMENT_REASON_CODE ,
PAYMENT_REASON_COMMENTS,
SETTLEMENT_PRIORITY ,
REMITTANCE_MESSAGE1 ,
REMITTANCE_MESSAGE2 ,
REMITTANCE_MESSAGE3 ,
UNIQUE_REMITTANCE_IDENTIFIER ,
URI_CHECK_DIGIT ,
DELIVERY_CHANNEL_CODE ,
DISCOUNT_DATE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
ALLOW_REMOVING_DOCUMENT_FLAG)
values (
l_DOCUMENT_PAYABLE_ID,
l_CALLING_APP_ID,
l_CALLING_APP_DOC_UNIQUE_REF1,
l_CALLING_APP_DOC_UNIQUE_REF2,
-- l_CALLING_APP_DOC_REF_NUMBER,
l_PAY_PROC_TRXN_TYPE_CODE,
l_PAYMENT_METHOD_CODE,
l_PAYMENT_AMOUNT,
l_EXCLUSIVE_PAYMENT_FLAG,
l_PAYMENT_FUNCTION,
l_DOCUMENT_DATE,
l_DOCUMENT_TYPE,
l_DOCUMENT_DESCRIPTION,
l_DOCUMENT_AMOUNT ,
l_EXTERNAL_BANK_ACCOUNT_ID,
l_PAYEE_PARTY_ID,
l_PAYEE_PARTY_SITE_ID,
l_SUPPLIER_SITE_ID,
l_LEGAL_ENTITY_ID,
l_ORG_ID ,
l_ORG_TYPE,
l_DOCUMENT_CURRENCY_CODE,
l_PAYMENT_CURRENCY_CODE,
l_BANK_CHARGE_BEARER ,
l_PAYMENT_REASON_CODE ,
l_PAYMENT_REASON_COMMENTS,
l_SETTLEMENT_PRIORITY ,
l_REMITTANCE_MESSAGE1 ,
l_REMITTANCE_MESSAGE2 ,
l_REMITTANCE_MESSAGE3 ,
l_UNIQUE_REMITTANCE_IDENTIFIER ,
l_URI_CHECK_DIGIT ,
l_DELIVERY_CHANNEL_CODE ,
l_DISCOUNT_DATE,
l_CREATED_BY ,
l_CREATION_DATE ,
l_LAST_UPDATED_BY ,
l_LAST_UPDATE_DATE,
l_OBJECT_VERSION_NUMBER,
'N');
Update Ap_Payment_Schedules_all
Set hold_flag = 'Y',
iby_hold_reason = l_iby_error_msg_str
Where invoice_id = l_calling_app_doc_unique_ref1
And payment_num = l_calling_app_doc_unique_ref2;
Update Ap_Payment_Schedules_All
Set hold_flag = 'N',
iby_hold_reason = Null
Where invoice_id = l_calling_app_doc_unique_ref1
And payment_num = l_calling_app_doc_unique_ref2;
close docs_to_be_inserted;
SELECT 'Y'
INTO l_invoice_includes_prepay_flag
FROM ap_invoices_all ai
WHERE ai.invoice_id = P_invoice_id
AND EXISTS (SELECT ail.invoice_includes_prepay_flag
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = ai.invoice_id
AND ail.invoice_includes_prepay_flag = 'Y');
SELECT SUM(NVL(ail.amount,0))
INTO l_total
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = P_invoice_id
AND (ail.line_type_lookup_code NOT IN ('PREPAY', 'AWT')
AND ail.prepay_invoice_id IS NULL
AND ail.prepay_line_number IS NULL);
SELECT COUNT(1)
INTO l_count
FROM ap_invoices_all ai
WHERE ai.invoice_id = P_invoice_id
AND ai.historical_flag = 'Y'
AND ai.payment_status_flag = 'Y'
AND NVL(ai.force_revalidation_flag,'N') = 'N'
AND EXISTS
(SELECT 1
FROM AP_INVOICE_LINES_ALL AIL
WHERE AIL.INVOICE_ID = AI.INVOICE_ID
AND NVL(AIL.DISCARDED_FLAG, 'N') <> 'Y'
AND NVL(AIL.CANCELLED_FLAG, 'N') <> 'Y'
AND (AIL.AMOUNT <> 0
OR (AIL.AMOUNT = 0
AND AIL.GENERATE_DISTS = 'Y'))
AND NOT EXISTS
(SELECT
/*+ NO_UNNEST */
'distributed line'
FROM AP_INVOICE_DISTRIBUTIONS_ALL D5
WHERE D5.INVOICE_ID = AIL.INVOICE_ID
AND D5.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
)
UNION ALL
SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL D,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE D.INVOICE_ID = AI.INVOICE_ID
AND FSP.ORG_ID = AI.ORG_ID
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'Y'
AND NVL(D.MATCH_STATUS_FLAG,'N') <> 'A'
OR (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'N'
AND NVL(D.MATCH_STATUS_FLAG,'N') NOT IN ('A','T')))
UNION ALL
SELECT 1
FROM AP_SELF_ASSESSED_TAX_DIST_ALL D,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE D.INVOICE_ID = AI.INVOICE_ID
AND FSP.ORG_ID = AI.ORG_ID
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'Y'
AND NVL(D.MATCH_STATUS_FLAG,'N') <> 'A'
OR (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'N'
AND NVL(D.MATCH_STATUS_FLAG,'N') NOT IN ('A','T')))
AND NOT EXISTS
(SELECT 'Cancelled distributions'
FROM AP_SELF_ASSESSED_TAX_DIST_ALL D2
WHERE D2.INVOICE_ID = D.INVOICE_ID
AND D2.CANCELLATION_FLAG = 'Y'
)
);
If(P_event = 'ON-INSERT') then
update ap_invoices_all
set force_revalidation_flag = 'Y'
where invoice_id = p_invoice_id;