[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT set_of_books_id,
base_currency_code
INTO l_set_of_books_id,
l_base_currency_code
FROM ap_system_parameters
WHERE org_id = p_check_rec.org_id;
SELECT SEQ.DB_SEQUENCE_NAME,
SEQ.DOC_SEQUENCE_ID
INTO l_dbseqnm, l_dbseqid
FROM FND_DOCUMENT_SEQUENCES SEQ,
FND_DOC_SEQUENCE_ASSIGNMENTS SA
WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
AND SA.APPLICATION_ID = 200
AND SA.CATEGORY_CODE = 'NETTING'
AND NVL(SA.METHOD_CODE,'A') = 'A'
AND SA.SET_OF_BOOKS_ID = l_set_of_books_id
AND nvl(p_check_rec.check_date, sysdate) BETWEEN SA.START_DATE
AND nvl(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
SELECT ap_checks_s.nextval
INTO l_check_id
FROM DUAL;
AP_AC_TABLE_HANDLER_PKG.Insert_Row(
l_rowid,
p_check_rec.amount,
p_check_rec.ce_bank_acct_use_id,
p_check_rec.bank_account_name,
p_check_rec.check_date,
l_check_id,
p_check_rec.check_number,
p_check_rec.currency_code,
p_check_rec.last_updated_by,
p_check_rec.last_update_date,
l_payment_type,
p_check_rec.address_line1,
p_check_rec.address_line2,
p_check_rec.address_line3,
p_check_rec.checkrun_name,
p_check_rec.check_format_id,
p_check_rec.check_stock_id,
p_check_rec.city,
p_check_rec.country,
p_check_rec.created_by,
p_check_rec.creation_date,
p_check_rec.last_update_login,
l_payment_status,
p_check_rec.vendor_name,
p_check_rec.vendor_site_code,
p_check_rec.external_bank_account_id,
p_check_rec.zip,
p_check_rec.bank_account_num,
p_check_rec.bank_account_type,
p_check_rec.bank_num,
p_check_rec.check_voucher_num,
p_check_rec.cleared_amount,
p_check_rec.cleared_date,
l_doc_category_code,
l_dbseqid,
l_doc_sequence_value,
p_check_rec.province,
p_check_rec.released_date,
p_check_rec.released_by,
p_check_rec.state,
p_check_rec.stopped_date,
p_check_rec.stopped_by,
p_check_rec.void_date,
p_check_rec.attribute1,
p_check_rec.attribute10,
p_check_rec.attribute11,
p_check_rec.attribute12,
p_check_rec.attribute13,
p_check_rec.attribute14,
p_check_rec.attribute15,
p_check_rec.attribute2,
p_check_rec.attribute3,
p_check_rec.attribute4,
p_check_rec.attribute5,
p_check_rec.attribute6,
p_check_rec.attribute7,
p_check_rec.attribute8,
p_check_rec.attribute9,
p_check_rec.attribute_category,
p_check_rec.future_pay_due_date,
p_check_rec.treasury_pay_date,
p_check_rec.treasury_pay_number,
p_check_rec.withholding_status_lookup_code,
p_check_rec.reconciliation_batch_id,
p_check_rec.cleared_base_amount,
p_check_rec.cleared_exchange_rate,
p_check_rec.cleared_exchange_date,
p_check_rec.cleared_exchange_rate_type,
p_check_rec.address_line4,
p_check_rec.county,
p_check_rec.address_style,
p_check_rec.org_id,
p_check_rec.vendor_id,
p_check_rec.vendor_site_id,
p_check_rec.exchange_rate,
p_check_rec.exchange_date,
p_check_rec.exchange_rate_type,
p_check_rec.base_amount,
p_check_rec.checkrun_id,
p_check_rec.global_attribute_category,
p_check_rec.global_attribute1,
p_check_rec.global_attribute2,
p_check_rec.global_attribute3,
p_check_rec.global_attribute4,
p_check_rec.global_attribute5,
p_check_rec.global_attribute6,
p_check_rec.global_attribute7,
p_check_rec.global_attribute8,
p_check_rec.global_attribute9,
p_check_rec.global_attribute10,
p_check_rec.global_attribute11,
p_check_rec.global_attribute12,
p_check_rec.global_attribute13,
p_check_rec.global_attribute14,
p_check_rec.global_attribute15,
p_check_rec.global_attribute16,
p_check_rec.global_attribute17,
p_check_rec.global_attribute18,
p_check_rec.global_attribute19,
p_check_rec.global_attribute20,
p_check_rec.transfer_priority,
p_check_rec.maturity_exchange_rate_type,
p_check_rec.maturity_exchange_date,
p_check_rec.maturity_exchange_rate,
p_check_rec.description,
p_check_rec.anticipated_value_date,
p_check_rec.actual_value_date,
l_payment_method_code,
p_check_rec.payment_profile_id,
p_check_rec.bank_charge_bearer,
p_check_rec.settlement_priority,
p_check_rec.payment_document_id,
p_check_rec.party_id,
p_check_rec.party_site_id,
p_check_rec.legal_entity_id,
p_check_rec.payment_id,
l_curr_calling_sequence);
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => l_check_id,
x_transaction_type => 'PAYMENT CREATED',
x_accounting_date => l_gl_date, /* SYSDATE, Changed Sysdate to l_gl_date for bug#7663371 */
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => NULL,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => p_check_rec.amount,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => p_check_rec.currency_code,
x_pmt_to_base_xrate_type => p_check_rec.exchange_rate_type,
x_pmt_to_base_xrate_date => p_check_Rec.exchange_date,
x_pmt_to_base_xrate => p_check_Rec.exchange_rate,
x_trx_base_amount => p_check_rec.base_amount,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_org_id => p_check_rec.org_id,
x_creation_date => p_check_rec.creation_date,
x_created_by => p_check_rec.created_by,
x_last_update_date => p_check_rec.last_update_date,
x_last_updated_by => p_check_rec.last_updated_by,
x_last_update_login => p_check_rec.last_update_login,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id
);
P_last_updated_by => p_check_Rec.last_updated_by,
P_last_update_login => p_check_Rec.last_update_login,
P_accounting_event_id => l_accounting_event_id,
P_org_id => p_check_rec.org_id,
P_calling_sequence => l_curr_calling_sequence
);
l_debug_info := 'Insert Clearing Records';
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => p_check_rec.created_by,
X_LAST_UPDATE_LOGIN => p_check_rec.last_update_login,
X_CREATED_BY => p_check_rec.created_by,
X_CREATION_DATE => SYSDATE,
X_PROGRAM_UPDATE_DATE => NULL,
X_PROGRAM_APPLICATION_ID => NULL,
X_PROGRAM_ID => NULL,
X_REQUEST_ID => NULL,
X_CALLING_SEQUENCE => l_curr_calling_sequence);
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_accounting_event_id IN NUMBER,
P_org_id IN NUMBER,
P_calling_sequence IN VARCHAR2
)
IS
l_debug_info VARCHAR2(240);
SELECT APS.payment_num,
AIRP.invoice_type_lookup_code,
AIRP.invoice_num,
AIRP.vendor_id,
AIRP.vendor_site_id,
AIRP.exclusive_payment_flag,
AIRP.accts_pay_code_combination_id,
APS.amount_remaining,
APS.attribute1,
APS.attribute2,
APS.attribute3,
APS.attribute4,
APS.attribute5,
APS.attribute6,
APS.attribute7,
APS.attribute8,
APS.attribute9,
APS.attribute10,
APS.attribute11,
APS.attribute12,
APS.attribute13,
APS.attribute14,
APS.attribute15,
APS.attribute_category
INTO
l_payment_num,
l_invoice_type,
l_invoice_num,
l_vendor_id,
l_vendor_site_id,
l_exclusive_payment_flag,
l_accts_pay_ccid,
l_amount_remaining,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_attribute_category
FROM ap_invoices AIRP,
ap_payment_schedules APS
WHERE AIRP.invoice_id = P_Invoice_Payment_Info_Tab(i).invoice_id
AND APS.payment_num = P_Invoice_Payment_Info_Tab(i).payment_schedule_num
AND APS.checkrun_id = P_checkrun_id
AND APS.invoice_id = AIRP.invoice_id;
SELECT ap_invoice_payments_s.nextval
INTO l_invoice_payment_id
FROM sys.dual;
P_last_updated_by => P_last_updated_by,
P_last_update_login => P_last_update_login,
P_currency_code => P_currency_code,
P_base_currency_code => P_base_currency_code,
P_exchange_rate => P_exchange_rate,
P_exchange_rate_type => P_exchange_rate_type,
P_exchange_date => P_exchange_date,
P_ce_bank_acct_use_id => P_ce_bank_acct_use_id,
P_bank_account_num => P_bank_account_num,
P_bank_account_type => P_bank_account_type,
P_bank_num => P_bank_num,
P_future_pay_posted_flag => l_future_pay_posted_flag,
P_exclusive_payment_flag => l_exclusive_payment_flag,
P_accts_pay_ccid => l_accts_pay_ccid,
P_gain_ccid => NULL,
P_loss_ccid => NULL,
P_future_pay_ccid => NULL,
P_asset_ccid => NULL,
P_payment_dists_flag => 'N',
P_payment_mode => 'PAY',
P_replace_flag => 'N',
P_attribute1 => l_attribute1,
P_attribute2 => l_attribute2,
P_attribute3 => l_attribute3,
P_attribute4 => l_attribute4,
P_attribute5 => l_attribute5,
P_attribute6 => l_attribute6,
P_attribute7 => l_attribute7,
P_attribute8 => l_attribute8,
P_attribute9 => l_attribute9,
P_attribute10 => l_attribute10,
P_attribute11 => l_attribute11,
P_attribute12 => l_attribute12,
P_attribute13 => l_attribute13,
P_attribute14 => l_attribute14,
P_attribute15 => l_attribute15,
P_attribute_category => l_attribute_category,
P_calling_sequence => l_curr_calling_sequence,
P_accounting_event_id => P_accounting_event_id,
P_org_id => P_org_id);
' P_last_updated_by = ' || P_last_updated_by ||
' P_last_update_login = ' || P_last_update_login
);
SELECT
DECODE(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
0,
DECODE(PS.GROSS_AMOUNT,
0, 0,
DECODE(asi.ALWAYS_TAKE_DISC_FLAG,
'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
GREATEST(DECODE(SIGN(c_date
- NVL(PS.DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
DECODE(SIGN(c_date
- NVL(PS.SECOND_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
DECODE(SIGN(c_date
- NVL(PS.THIRD_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
* (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
0, 1,
PS.GROSS_AMOUNT))))
FROM ap_payment_schedules_all PS,
ap_invoices_all ai,
ap_supplier_sites_all asi
WHERE ai.invoice_id = ps.invoice_id
AND ai.vendor_id = asi.vendor_id
AND ai.vendor_site_id = asi.vendor_site_id
AND ai.invoice_id = c_invoice_id
AND ps.payment_num = c_payment_num;
SELECT amount_remaining
INTO l_amount_remaining
FROM ap_payment_schedules_all
WHERE invoice_id = P_Invoice_Id
AND payment_num = P_Payment_Num;
SELECT invoice_currency_code
INTO l_inv_curr
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;