[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.irm_id
,a.iba_id
,a.ile_id
,a.amount -- rcpt currency
,a.currency_code
FROM okl_trx_csh_receipt_b a
WHERE a.id = cp_rct_id;
CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
SELECT trunc(cp_date) gl_date, 1 Counter
FROM gl_period_statuses
WHERE application_id = 222
-- BEGIN abindal bug 4356410 --
AND closing_status IN ('F','O')
-- END abindal bug 4356410 --
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND trunc(cp_date) between start_date and end_date
AND adjustment_period_flag = 'N'
UNION
SELECT MAX(end_date) gl_date, 2 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND end_date <= trunc(cp_date)
AND adjustment_period_flag = 'N'
UNION
SELECT MIN(start_date) gl_date, 3 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND start_date >= trunc(cp_date)
AND adjustment_period_flag = 'N'
)
where gl_date is not null
order by counter;
SELECT receivables_invoice_id INTO l_customer_trx_id
FROM okl_cnsld_ar_strms_v
WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
SELECT bill_to_site_use_id INTO l_customer_site_use_id
FROM ra_customer_trx_all
WHERE customer_trx_id = l_customer_trx_id;
SELECT receivables_invoice_id INTO l_customer_trx_id
FROM okl_cnsld_ar_strms_v
WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
/* Modified select statement to address bug 4510824 */
SELECT max(trx_date), invoice_number INTO l_xcav_tbl(i).trx_date, ar_invoice_num
FROM okl_xtl_csh_apps_v
WHERE lsm_id = l_xcav_tbl(i).lsm_id
AND rca_id = l_xcav_tbl(i).rca_id
GROUP BY invoice_number;
SELECT invoice_date INTO ar_invoice_date
FROM OKL_BPD_LEASING_PAYMENT_TRX_V
WHERE receivables_invoice_number = ar_invoice_num;
IF l_over_pay = 'O' THEN -- APPLY TO ON ACCOUNT IF SELECTED.
Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_cash_receipt_id => l_cash_receipt_id
-- ,p_amount_applied => l_amount_unapplied -- not required. we will just write off remaining rcpt.
,p_apply_date => l_xcrv_rec.receipt_date
,p_apply_gl_date => l_rec_gl_date
,p_org_id => mo_global.get_current_org_id()
);
SELECT a.irm_id
,a.iba_id
,a.ile_id
,a.amount -- rcpt currency
,a.currency_code
FROM okl_trx_csh_receipt_b a
WHERE a.id = cp_rct_id;
CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
SELECT end_date gl_date, 1 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND trunc(cp_date) between start_date and end_date
AND adjustment_period_flag = 'N'
UNION
SELECT MAX(end_date) gl_date, 2 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND end_date <= trunc(cp_date)
AND adjustment_period_flag = 'N'
UNION
SELECT MIN(end_date) gl_date, 3 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND start_date >= trunc(cp_date)
AND adjustment_period_flag = 'N'
)
where gl_date is not null
order by counter;
CURSOR c_get_gl_date_start(cp_date IN DATE) IS SELECT * from (
SELECT start_date gl_date, 1 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND trunc(cp_date) between start_date and end_date
AND adjustment_period_flag = 'N'
UNION
SELECT MAX(start_date) gl_date, 2 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND end_date <= trunc(cp_date)
AND adjustment_period_flag = 'N'
UNION
SELECT MIN(start_date) gl_date, 3 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND start_date >= trunc(cp_date)
AND adjustment_period_flag = 'N'
)
where gl_date is not null
order by counter;
SELECT TO_CHAR(sysdate, 'MONTH') INTO l_applic_month
FROM DUAL;
SELECT TO_CHAR(l_gl_date, 'MONTH') INTO l_gl_month
FROM DUAL;
SELECT receivables_invoice_id INTO l_customer_trx_id
FROM okl_cnsld_ar_strms_v
WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
SELECT bill_to_site_use_id INTO l_customer_site_use_id
FROM ra_customer_trx_all
WHERE customer_trx_id = l_customer_trx_id;
SELECT trx_date INTO l_xcav_tbl(i).trx_date
FROM okl_xtl_csh_apps_v
WHERE lsm_id = l_xcav_tbl(i).lsm_id
AND rca_id = l_xcav_tbl(i).rca_id;
SELECT receivables_invoice_id INTO l_customer_trx_id
FROM okl_cnsld_ar_strms_v
WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
SELECT trx_date INTO l_xcav_tbl(i).trx_date
FROM okl_xtl_csh_apps_v
WHERE lsm_id = l_xcav_tbl(i).lsm_id
AND rca_id = l_xcav_tbl(i).rca_id;
IF l_over_pay = 'O' THEN -- APPLY TO ON ACCOUNT IF SELECTED.
Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_cash_receipt_id => l_cash_receipt_id
-- ,p_amount_applied => l_amount_unapplied -- not required. we will just write off remaining rcpt.
,p_apply_date => l_xcrv_rec.receipt_date
,p_apply_gl_date => l_gl_date
,p_org_id => mo_global.get_current_org_id()
);