[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
output (' Date Submitted: '||TO_CHAR(p_BatchRec.last_update_date, 'MM/DD/YYYY HH24:MI:SS'));
debug_msg (l_module_name, 'last_updated_by => '|| p_ARBatchRec.last_updated_by);
debug_msg (l_module_name, 'last_update_date => '|| TO_CHAR(p_ARBatchRec.last_update_date, 'MM/DD/YYYY HH24:MI:SS'));
debug_msg (l_module_name, 'last_update_login => '|| p_ARBatchRec.last_update_login);
PROCEDURE insert_ar_batch
(
p_BatchRec IN fv_ar_batches%ROWTYPE,
p_ErrorCode OUT NOCOPY VARCHAR2,
p_ErrorDesc OUT NOCOPY VARCHAR2,
p_ErrorLoc OUT NOCOPY VARCHAR2
) IS
l_module_name VARCHAR2(30) := 'insert_ar_batch';
l_ARBatchRec.last_updated_by := p_BatchRec.last_updated_by;
l_ARBatchRec.last_update_date := p_BatchRec.last_update_date;
l_ARBatchRec.last_update_login := p_BatchRec.last_update_login;
debug_msg (l_module_name, 'Inserting data into ar_batches');
INSERT INTO ar_batches
(
batch_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
name,
batch_date,
gl_date,
status,
deposit_date,
type,
batch_source_id,
control_count,
control_amount,
batch_applied_status,
currency_code,
exchange_rate,
exchange_date,
exchange_rate_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
receipt_method_id,
remit_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
receipt_class_id,
set_of_books_id,
org_id
)
VALUES
(
l_ARBatchRec.batch_id,
l_ARBatchRec.last_updated_by,
l_ARBatchRec.last_update_date,
l_ARBatchRec.last_update_login,
l_ARBatchRec.created_by,
l_ARBatchRec.creation_date,
l_ARBatchRec.name,
l_ARBatchRec.batch_date,
l_ARBatchRec.gl_date,
l_ARBatchRec.status,
l_ARBatchRec.deposit_date,
l_ARBatchRec.type,
l_ARBatchRec.batch_source_id,
l_ARBatchRec.control_count,
l_ARBatchRec.control_amount,
l_ARBatchRec.batch_applied_status,
l_ARBatchRec.currency_code,
l_ARBatchRec.exchange_rate,
l_ARBatchRec.exchange_date,
l_ARBatchRec.exchange_rate_type,
l_ARBatchRec.attribute_category,
l_ARBatchRec.attribute1,
l_ARBatchRec.attribute2,
l_ARBatchRec.attribute3,
l_ARBatchRec.attribute4,
l_ARBatchRec.attribute5,
l_ARBatchRec.attribute6,
l_ARBatchRec.attribute7,
l_ARBatchRec.attribute8,
l_ARBatchRec.attribute9,
l_ARBatchRec.attribute10,
l_ARBatchRec.attribute11,
l_ARBatchRec.attribute12,
l_ARBatchRec.attribute13,
l_ARBatchRec.attribute14,
l_ARBatchRec.attribute15,
l_ARBatchRec.receipt_method_id,
l_ARBatchRec.remit_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
l_ARBatchRec.receipt_class_id,
l_ARBatchRec.set_of_books_id,
l_ARBatchRec.org_id
);
END insert_ar_batch;
SELECT ara.amount_applied,
ara.receivable_application_id
INTO l_PreviousAmount,
l_ReceivableApplicationId
FROM ar_receivable_applications ara
WHERE ara.cash_receipt_id = p_Receiptid
AND ara.applied_customer_trx_id = p_InvoiceId;
p_ErrorLoc := 'SELECT ar_receivable_applications';
PROCEDURE update_cash_receipt_hist
(
p_BatchId IN NUMBER,
p_CashReceiptId IN NUMBER,
p_ErrorCode OUT NOCOPY VARCHAR2,
p_ErrorDesc OUT NOCOPY VARCHAR2,
p_ErrorLoc OUT NOCOPY VARCHAR2
) IS
l_module_name VARCHAR2(30) := 'update_cash_receipt_hist';
UPDATE ar_cash_receipt_history_all
SET batch_id = p_BatchId
WHERE cash_receipt_id = p_CashReceiptId;
log_msg (l_module_name,'Successfully Updated Cash Receipt History');
debug_msg (l_module_name, 'Updated '||SQL%ROWCOUNT||' rows.');
p_ErrorLoc := 'UPDATE ar_cash_receipt_history_all';
END update_cash_receipt_hist;
PROCEDURE update_fv_batch_status
(
p_BatchId IN NUMBER,
p_Status IN VARCHAR2,
p_ErrorCode OUT NOCOPY VARCHAR2,
p_ErrorDesc OUT NOCOPY VARCHAR2,
p_ErrorLoc OUT NOCOPY VARCHAR2
) IS
l_module_name VARCHAR2(30) := 'update_fv_batch_status';
UPDATE fv_ar_batches_all
SET transfer_status = p_status
WHERE batch_id = p_BatchId;
debug_msg (l_module_name, 'Updated '||SQL%ROWCOUNT||' rows.');
p_ErrorLoc := 'UPDATE fv_ar_batches_all';
END update_fv_batch_status;
debug_msg (l_module_name, 'Calling update_cash_receipt_hist.');
update_cash_receipt_hist
(
p_BatchId => p_BatchId,
p_CashReceiptId => p_CashReceiptId,
p_ErrorCode => p_ErrorCode,
p_ErrorDesc => p_ErrorDesc,
p_ErrorLoc => p_ErrorLoc
);
SELECT ctl.extended_amount * nvl(tl.relative_amount,1)/ nvl(t.base_amount,1) original_line_amount
INTO l_LineAmount
FROM ra_customer_trx_lines ctl ,
ra_terms t,
ra_terms_lines tl,
ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_PaymentScheduleId
AND ctl.customer_trx_id = p_InvoiceId
AND ctl.line_type = 'LINE'
AND tl.term_id(+) = ps.term_id
AND tl.sequence_num(+) = ps.terms_sequence_number
AND t.term_id(+) = tl.term_id
AND ctl.customer_trx_line_id = p_InvoiceLineId;
p_ErrorLoc := 'SELECT ra_customer_trx_lines, ra_terms...';
SELECT DISTINCT ussgl_transaction_code
INTO l_DebitMemoTxnCode
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_DebitMemoId
AND account_class = 'REV';
p_ErrorLoc := 'SELECT ra_cust_trx_line_gl_dist';
SELECT receipt_txn_code
INTO p_ReceiptTxnCode
FROM fv_tc_map_dtl ftmd,
fv_tc_map_hdr ftmh
WHERE ftmh.document_type = 'RECEIPT'
AND ftmd.tc_map_hdr_id = ftmh.tc_map_hdr_id
AND ftmd.debit_memo_txn_code = l_DebitMemoTxnCode
AND p_EffectiveDate BETWEEN ftmd.start_date AND NVL(ftmd.end_date, SYSDATE);
p_ErrorLoc := 'SELECT fv_tc_map_dtl';
SELECT distinct aps.customer_trx_id invoice_id,
aps.amount_due_remaining amount_due,
fcc.priority,
aps.payment_schedule_id,
aps.cust_trx_type_id,
aps.due_date,
rct.trx_date invoice_date,
rct.trx_number invoice_number,
rct.invoice_currency_code
FROM ra_customer_trx rct,
ar_payment_schedules aps,
fv_finance_charge_controls fcc
WHERE rct.related_customer_trx_id = c_invoice_id
AND aps.customer_trx_id = rct.customer_trx_id
AND rct.interface_header_attribute3 = fcc.charge_type
AND aps.amount_due_remaining > 0
ORDER BY fcc.priority ;
SELECT ficr.receipt_number,
ficr.customer_id,
hzp.party_name customer_name,
trunc(ficr.receipt_date) receipt_date,
ficr.site_use_id,
sum(ficr.amount) amount
FROM fv_interim_cash_receipts ficr,
hz_parties hzp, hz_cust_accounts hzca
WHERE ficr.batch_id = c_batch_id
AND hzp.party_id = hzca.party_id
AND ficr.customer_id = hzca.cust_account_id
GROUP BY ficr.receipt_number,
ficr.customer_id,
hzp.party_name,
ficr.receipt_date,
ficr.site_use_id
ORDER BY ficr.receipt_number;
SELECT ficr.batch_id,
ficr.currency_code,
ficr.receipt_number,
ficr.customer_id,
ficr.special_type,
ficr.status,
ficr.customer_trx_id,
trunc(ficr.gl_date) gl_date,
SUM(ficr.amount) amount,
ficr.site_use_id,
ficr.ce_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
ficr.set_of_books_id,
trunc(ficr.receipt_date) receipt_date,
ficr.related_invoice_id,
ficr.receipt_method_id,
ficr.payment_schedule_id,
-- ficr.ussgl_transaction_code,
ficr.org_id,
ficr.customer_trx_line_id,
rct.trx_number invoice_number,
rct.invoice_currency_code,
rct.exchange_rate_type invoice_exchange_rate_type,
rctl.line_number line_number,
hzp.party_name,
rctl.extended_amount line_amount
FROM fv_interim_cash_receipts ficr,
ra_customer_trx rct,
ra_customer_trx_lines rctl,
hz_parties hzp, hz_cust_accounts hzca
WHERE ficr.batch_id = c_batch_id
AND hzp.party_id = hzca.party_id
AND ficr.receipt_number = c_receipt_number
AND ficr.customer_id = c_customer_id
AND ficr.receipt_date = c_receipt_date
AND rct.customer_trx_id (+) = ficr.customer_trx_id
AND rctl.customer_trx_line_id (+) = ficr.customer_trx_line_id
AND hzca.cust_account_id (+) =ficr.customer_id
GROUP BY
ficr.batch_id,
ficr.currency_code,
ficr.receipt_number,
ficr.customer_id,
ficr.special_type,
ficr.status,
ficr.customer_trx_id,
trunc(ficr.gl_date),
ficr.site_use_id,
ficr.ce_bank_acct_use_id, --PSKI changes for BA and MOAC Uptake
ficr.set_of_books_id,
trunc(ficr.receipt_date) ,
ficr.related_invoice_id,
ficr.receipt_method_id,
ficr.payment_schedule_id,
-- ficr.ussgl_transaction_code,
ficr.org_id,
ficr.customer_trx_line_id,
rct.trx_number ,
rct.invoice_currency_code,
rct.exchange_rate_type ,
rctl.line_number ,
hzp.party_name,
rctl.extended_amount
ORDER BY rct.trx_number ASC,
rctl.line_number DESC;
SELECT aps.customer_trx_id,
aps.amount_due_remaining amount_due,
aps.payment_schedule_id,
aps.cust_trx_type_id,
aps.due_date,
aps.trx_number invoice_number,
rac.invoice_currency_code
FROM ar_payment_schedules aps,
ra_cust_trx_types rct,
ra_customer_trx rac
WHERE aps.amount_due_remaining > 0
AND aps.status = 'OP'
AND aps.customer_id = NVL(c_cust_no,aps.customer_id)
AND aps.customer_trx_id = NVL(c_invoice_id,aps.customer_trx_id)
AND aps.cust_trx_type_id = rct.cust_trx_type_id
AND rct.type = 'INV'
AND aps.customer_trx_id = rac.customer_trx_id
AND rac.bill_to_site_use_id = nvl(c_site_use_id,rac.bill_to_site_use_id)
AND rac.set_of_books_id = c_sob
AND rac.invoice_currency_code = c_currency
ORDER BY aps.customer_trx_id,
payment_schedule_id;
debug_msg (l_module_name, 'Calling insert_ar_batch');
insert_ar_batch
(
p_BatchRec => p_BatchRec,
p_ErrorCode => p_ErrorCode,
p_ErrorDesc => p_ErrorDesc,
p_ErrorLoc => p_ErrorLoc
);
SELECT *
INTO l_BatchRec
FROM fv_ar_batches fab
WHERE batch_name = p_batch_name;
l_ErrorLoc := l_module_name || ':' || 'SELECT fv_ar_batches';
l_ErrorLoc := l_module_name || ':' || 'SELECT fv_ar_batches';
debug_msg (l_module_name, 'Calling update_fv_batch_status with SUCCESS');
update_fv_batch_status
(
p_BatchId => l_BatchRec.batch_id,
p_Status => 'COMPLETED',
p_ErrorCode => l_ErrorCode,
p_ErrorDesc => l_ErrorDesc,
p_ErrorLoc => l_ErrorLoc
);
debug_msg (l_module_name, 'Calling update_fv_batch_status with FAILURE');
update_fv_batch_status
(
p_BatchId => l_BatchRec.batch_id,
p_Status => 'NEEDS RESUBMISSION',
p_ErrorCode => l_ErrorCode,
p_ErrorDesc => l_ErrorDesc,
p_ErrorLoc => l_ErrorLoc
);