[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HOU.name
FROM HR_OPERATING_UNITS HOU
WHERE HOU.organization_id = p_org_id;
SELECT CHR.id,
CHR.contract_number contract_number,
CHR.START_DATE,
CHR.end_date,
CHR.sts_code,
CHR.scs_code,
CHR.date_terminated
FROM OKC_K_HEADERS_B CHR,
OKL_K_HEADERS KHR
WHERE CHR.id = p_khr_id
AND CHR.id = KHR.id;
SELECT TRN.id
FROM OKL_TRX_AR_ADJSTS_V BAL,
OKL_TRX_CONTRACTS TRN
WHERE TRN.khr_id = p_khr_id
AND TRN.tcn_type = 'TMT'
AND BAL.tcn_id = TRN.id;
SELECT TRN.id
FROM OKL_TRX_CONTRACTS TRN
WHERE TRN.khr_id = p_khr_id
AND TRN.tcn_type = 'BWO';
SELECT SUM(amount_due_remaining)
FROM OKL_BPD_LEASING_PAYMENT_TRX_V
WHERE contract_id = p_khr_id
AND invoice_date <= sysdate;
SELECT SUM(amount_due_remaining)
FROM OKL_BPD_TLD_AR_LINES_V
WHERE khr_id = p_khr_id
AND invoice_date <= sysdate;
SELECT OBLP.amount_due_remaining AMOUNT,
OBLP.stream_type_id STREAM_TYPE_ID,
OSTY.name STREAM_MEANING,
OBLP.payment_schedule_id SCHEDULE_ID,
OBLP.receivables_invoice_number AR_INVOICE_NUMBER,
OTIL.id TIL_ID,
-999 TLD_ID
FROM OKL_BPD_LEASING_PAYMENT_TRX_V OBLP,
OKL_TXL_AR_INV_LNS_V OTIL,
OKL_STRM_TYPE_V OSTY
WHERE OBLP.contract_id = p_khr_id
AND OBLP.receivables_invoice_id = OTIL.receivables_invoice_id
AND OBLP.stream_type_id = OSTY.id
AND OBLP.amount_due_remaining > 0
AND OBLP.invoice_date <= p_trn_date
UNION
SELECT OBLP.amount_due_remaining AMOUNT,
OBLP.stream_type_id STREAM_TYPE_ID,
OSTY.name STREAM_MEANING,
OBLP.payment_schedule_id SCHEDULE_ID,
OBLP.receivables_invoice_number AR_INVOICE_NUMBER,
OTAI.til_id_details TIL_ID,
OTAI.id TLD_ID
FROM OKL_BPD_LEASING_PAYMENT_TRX_V OBLP,
OKL_TXD_AR_LN_DTLS_V OTAI,
OKL_STRM_TYPE_V OSTY
WHERE OBLP.contract_id = p_khr_id
AND OBLP.receivables_invoice_id = OTAI.receivables_invoice_id
AND OBLP.stream_type_id = OSTY.id
AND OBLP.amount_due_remaining > 0
AND OBLP.invoice_date <= p_trn_date;
SELECT RACTRL.amount_due_remaining AMOUNT,
RACTRL.STY_ID STREAM_TYPE_ID,
--Bug 6316320 dpsingh start
RACTRL.KLE_ID KLE_ID,
--Bug 6316320 dpsingh end
RACTRL.STREAM_TYPE STREAM_MEANING,
APS.payment_schedule_id SCHEDULE_ID,
RACTRL.CUSTOMER_TRX_ID AR_INVOICE_NUMBER,
RACTRL.til_id_details TIL_ID,
RACTRL.TLD_ID TLD_ID
FROM OKL_BPD_TLD_AR_LINES_V RACTRL,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE RACTRL.khr_id = p_khr_id
AND RACTRL.amount_due_remaining > 0
AND RACTRL.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RACTRL.invoice_date <= p_trn_date;
SELECT pdt_id
FROM OKL_K_HEADERS_V
WHERE id = p_khr_id;
SELECT DST.code_combination_id
FROM OKL_TRNS_ACC_DSTRS DST
WHERE DST.source_id = p_source_id
AND DST.source_table = p_source_table
AND DST.cr_dr_flag = 'D'
AND DST.percentage = 100;
SELECT user_profile_option_name
FROM fnd_profile_options_vl
WHERE profile_option_name = 'OKL_SMALL_BALANCE_TOLERANCE';
SELECT ACCOUNT_DERIVATION
FROM OKL_SYS_ACCT_OPTS;
OKL_TRX_AR_ADJSTS_PUB.insert_trx_ar_adjsts(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_adjv_rec => lp_adjv_rec,
x_adjv_rec => lx_adjv_rec);
'OKL_TRX_AR_ADJSTS_PUB.insert_trx_ar_adjsts = '||l_return_status );
OKL_TXL_ADJSTS_LNS_PUB.insert_txl_adjsts_lns(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ajlv_rec => lp_ajlv_rec,
x_ajlv_rec => lx_ajlv_rec);
'OKL_TRX_AR_ADJSTS_PUB.insert_txl_adjsts_lns = '||l_return_status );
OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
p_api_version=> p_api_version,
p_init_msg_list=> OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_rec => lpp_tcnv_rec,
x_tcnv_rec => lxx_tcnv_rec);
OKL_TXL_ADJSTS_LNS_PUB.update_txl_adjsts_lns(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ajlv_rec => lp_ajlv_rec,
x_ajlv_rec => lx_ajlv_rec);
'OKL_TXL_ADJSTS_LNS_PUB.update_txl_adjsts_lns = '||l_return_status );
l_update_status VARCHAR2(3);
SELECT sysdate INTO l_sys_date FROM DUAL;
SELECT CHR.id,
CHR.contract_number contract_number
FROM OKC_K_HEADERS_B CHR,
OKL_K_HEADERS KHR
WHERE KHR.ID = CHR.ID
AND CHR.sts_code IN ('TERMINATED','EXPIRED') -- TERMINATED/EXPIRED
AND trunc(CHR.date_terminated) <= TRUNC(p_date) -- Ended
AND CHR.id NOT IN (-- balance writeoffs already done when contract was fully terminated
SELECT TRN.khr_id
FROM OKL_TRX_AR_ADJSTS_V BAL,
OKL_TRX_CONTRACTS TRN
WHERE BAL.tcn_id = TRN.id
AND TRN.tcn_type = 'TMT' -- full termination
AND TRN.khr_id = CHR.id
-- No need to check for actual step as tcn_id is recorded in AR_ADJSTS table
)
AND CHR.id NOT IN (-- new balance writeoffs done as part of this concurrent program
SELECT TRN.khr_id
FROM OKL_TRX_CONTRACTS TRN
WHERE TRN.tcn_type = 'BWO' -- new transaction type SEED ***
AND TRN.khr_id = CHR.id
)
AND 0 < ( -- Check that invoices with balances greater than 0 exists and dated before today
/* rmunjulu R12 Fixes - Billing fixes -- replaced with new select below
SELECT sum(BPD.amount_due_remaining)
FROM OKL_BPD_LEASING_PAYMENT_TRX_V BPD
WHERE BPD.invoice_date <= sysdate
AND BPD.contract_id = CHR.id
*/
-- rmunjulu R12 Fixes - Billing fixes -- changes to this select as old bpd view does not work anymore
SELECT sum(RACTRL.amount_due_remaining)
FROM OKL_BPD_TLD_AR_LINES_V RACTRL
WHERE RACTRL.invoice_date <= sysdate
AND RACTRL.khr_id = CHR.id
);