[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sty_select_basis VARCHAR2(2000);
CURSOR sty_select_basis_csr IS
SELECT validate_khr_start_date
FROM OKL_SYS_ACCT_OPTS;
SELECT end_date +1 accrual_reversal_date
FROM gl_period_statuses
WHERE application_id = 540
AND set_of_books_id =p_ledger_id
AND p_accrual_date BETWEEN start_date AND end_date;
override_status OKL_TRX_CONTRACTS.UPDATE_STATUS_YN%TYPE,
start_date OKL_K_HEADERS_FULL_V.CONTRACT_NUMBER%TYPE,
deal_type OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE,
khr_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE,
currency_conv_type OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_TYPE%TYPE,
currency_conv_date OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_DATE%TYPE,
currency_conv_rate OKL_TRX_CONTRACTS.CURRENCY_CONVERSION_RATE%TYPE,
func_currency_code OKL_TRX_CONTRACTS.CURRENCY_CODE%TYPE,
try_id OKL_TRX_TYPES_V.ID%TYPE,
reverse_date_to DATE,
batch_name VARCHAR2(2000),
sob_id OKL_TRX_CONTRACTS.SET_OF_BOOKS_ID%TYPE,
accrual_date DATE,
period_end_date DATE,
period_start_date DATE,
source_trx_id OKL_TRX_CONTRACTS.SOURCE_TRX_ID%TYPE,
source_trx_type OKL_TRX_CONTRACTS.SOURCE_TRX_TYPE%TYPE,
submission_mode VARCHAR2(2000),
rev_rec_method OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_METHOD%TYPE);
SELECT NVL(SUM(ste.amount),0)
FROM OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste
WHERE stm.khr_id = p_ctr_id
AND stm.sty_id = p_sty_id
AND stm.active_yn = 'Y'
AND ste.stm_id = stm.id
AND TRUNC(ste.stream_element_date) > TRUNC(l_sysdate)
GROUP BY stm.sty_id;
SELECT MIN(DUE_DATE) min_due_date, COUNT(*) total_os
FROM OKL_BPD_CONTRACT_INVOICES_V
WHERE contract_id = p_ctr_id
AND revenue_rec_basis <> 'CASH_RECEIPT'
AND status = 'OP';
SELECT 'Y' FROM okc_k_headers_b chr
WHERE id = p_contract_id
AND EXISTS
(
SELECT 'x' FROM okc_k_items cim
WHERE cim.object1_id1 = to_char(chr.id)
AND EXISTS
(
SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND lse.lty_code = 'SHARED'
AND cle.id = cim.cle_id
)
AND EXISTS
(
SELECT 'x' FROM okc_k_headers_b chr2
WHERE chr2.id = cim.dnz_chr_id
AND chr2.scs_code = 'SYNDICATION'
AND chr2.sts_code not in ('TERMINATED','ABANDONED')
)
)
AND chr.scs_code in ('LEASE','LOAN');
SELECT rule_information1
FROM OKC_RULES_B r
WHERE r.dnz_chr_id = p_ctr_id
AND r.rule_information_category = 'LAFCTG';
SELECT pdt.quality_val revenue_recognition_method
FROM OKL_PROD_QLTY_VAL_UV pdt,
OKL_K_HEADERS_FULL_V khr
WHERE khr.id = p_chr_id
AND khr.pdt_id = pdt.pdt_id
AND pdt.quality_name = 'REVENUE_RECOGNITION_METHOD'
AND khr.scs_code = 'LEASE'
UNION
SELECT 'STREAMS' revenue_recognition_method
FROM OKL_K_HEADERS_FULL_V khr
WHERE khr.id = p_chr_id
AND khr.scs_code = 'INVESTOR'
;
SELECT a.contract_number,
nvl(b.multi_gaap_yn, 'N') multi_gaap_yn,
c.reporting_pdt_id -- MGAAP 7263041
FROM OKC_K_HEADERS_B a, OKL_K_HEADERS B,
OKL_PRODUCTS C
WHERE A.id = p_chr_id
AND A.id = B.id
AND B.PDT_ID = C.ID;
select secondary_rep_method
from okl_sys_acct_opts;
SELECT 'N'
FROM OKC_K_HEADERS_B CHR
WHERE id = p_chr_id
AND EXISTS (
SELECT 1
FROM OKL_STRM_TYPE_B sty,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, -- MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr,
OKL_PRODUCTS pdt
WHERE khr.id = p_chr_id
AND stm.khr_id = khr.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
--AND stm.purpose_code IS NULL --MGAAP
AND ( stm.purpose_code IS NULL OR stm.purpose_code = 'REPORT' )
AND stm.sty_id = sty.id
AND sty.id = psty.sty_id
AND psty.accrual_yn = 'Y'
AND psty.pdt_id = DECODE(p_reporting_pdt_id,
NULL, pdt.id, p_reporting_pdt_id) -- MGAAP
AND pdt.id = khr.pdt_id
AND stm.id = ste.stm_id
AND TRUNC(ste.stream_element_date) <= TRUNC(p_accrue_till_date)
AND ste.amount <> 0
AND ste.accrued_yn IS NULL);
SELECT TRUNC(DATE_LAST_INTERIM_INTEREST_CAL)
FROM OKL_K_HEADERS
WHERE ID = p_khr_id;
SELECT TRUNC(start_date)
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id;
SELECT TRUNC(MAX(trx.date_transaction_occurred)) last_accrual_date
FROM OKL_TRX_CONTRACTS trx,
OKL_TRX_TYPES_V try,
OKL_TXL_CNTRCT_LNS txl,
OKL_STRM_TYPE_V sty
WHERE trx.khr_id = p_khr_id
AND trx.try_id = try.id
AND try.name = 'Accrual'
--Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
AND trx.tsu_code = 'PROCESSED'
AND trx.id = txl.tcn_id
AND trx.representation_type = 'PRIMARY' -- MGAAP 7263041
AND txl.sty_id = sty.id
AND sty.stream_type_purpose = 'ACTUAL_INCOME_ACCRUAL';
SELECT OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING_LANG('OKL_ACCRUAL_RULE_LOGICAL_OP',arlo_code,540,0,'US') logical_op_meaning
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING_LANG('OKL_PARENTHESIS',left_parentheses,540,0,'US') left_parentheses_meaning
,aro_code
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING_LANG('OKL_ACCRUAL_RULE_OPERATOR',acro_code,540,0,'US') relational_op_meaning
,right_operand_literal
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING_LANG('OKL_PARENTHESIS',right_parentheses,540,0,'US') right_parentheses_meaning
,from_date
,to_date
FROM OKL_ACCRUAL_GNRTNS
WHERE to_number(VERSION) = (SELECT MAX(to_number(version))
FROM OKL_ACCRUAL_GNRTNS)
AND TO_DATE IS NULL
ORDER BY LINE_NUMBER;
l_string := 'SELECT '||'''N'''||' FROM DUAL WHERE '|| l_string;
CURSOR select_streams_csr(p_ctr_id NUMBER, p_accrue_from_date DATE, p_accrue_till_date DATE) IS
SELECT sty.id,
stytl.name,
stm.id stream_id,
ste.id stream_element_id,
ste.amount,
stm.kle_id
FROM OKL_STRM_TYPE_B sty,
OKL_STRM_TYPE_TL stytl,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, -- MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr
WHERE stm.khr_id = p_ctr_id
AND khr.id = stm.khr_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
--AND stm.purpose_code IS NULL MGAAP 7263041
AND (stm.purpose_code IS NULL OR stm.purpose_code='REPORT')
AND stm.sty_id = sty.id
AND sty.id = stytl.id
AND stytl.LANGUAGE = USERENV('LANG')
AND sty.accrual_yn = p_accrual_rule_yn
AND sty.id = psty.sty_id
--AND psty.pdt_id = khr.pdt_id
AND psty.pdt_id = p_product_id -- MGAAP 7263041
AND psty.accrual_yn = 'Y'
AND stm.id = ste.stm_id
AND TRUNC(ste.stream_element_date) BETWEEN TRUNC(p_accrue_from_date) AND TRUNC(p_accrue_till_date)
AND ste.amount <> 0 -- bug 2804913
AND ste.accrued_yn IS NULL;
CURSOR select_streams_csr2(p_ctr_id NUMBER, p_accrue_from_date DATE, p_accrue_till_date DATE) IS
SELECT sty.id,
stytl.name,
stm.id stream_id,
ste.id stream_element_id,
ste.amount,
stm.kle_id
FROM OKL_STRM_TYPE_B sty,
OKL_STRM_TYPE_TL stytl,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, -- MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr
WHERE stm.khr_id = p_ctr_id
AND khr.id = stm.khr_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
--AND stm.purpose_code IS NULL MGAAP 7263041
AND ( stm.purpose_code IS NULL OR stm.purpose_code='REPORT')
AND stm.sty_id = sty.id
AND sty.id = stytl.id
AND stytl.LANGUAGE = USERENV('LANG')
AND sty.accrual_yn = p_accrual_rule_yn
AND sty.id = psty.sty_id
--AND psty.pdt_id = khr.pdt_id
AND psty.pdt_id = p_product_id -- MGAAP 7263041
AND psty.accrual_yn = 'Y'
AND stm.id = ste.stm_id
AND ste.stream_element_date <= p_accrue_till_date
AND ste.amount <> 0 -- bug 2804913
AND ste.accrued_yn IS NULL;
IF l_sty_select_basis IS NULL THEN
Okl_Api.set_message(p_app_name => g_app_name,
p_msg_name => 'OKL_AGN_KHR_VALD_ERROR');
IF l_sty_select_basis = 'KHR_START_DATE' THEN
OPEN select_streams_csr(p_khr_id, p_ctr_start_date, p_period_end_date);
FETCH select_streams_csr BULK COLLECT INTO l_stream_tbl;
CLOSE select_streams_csr;
ELSIF l_sty_select_basis = 'BEFORE_KHR_START_DATE' THEN
OPEN select_streams_csr2(p_khr_id, p_ctr_start_date, p_period_end_date);
FETCH select_streams_csr2 BULK COLLECT INTO l_stream_tbl;
CLOSE select_streams_csr2;
SELECT con.object1_id1
FROM OKC_K_HEADERS_B CHR,
OKC_CONTACT_SOURCES cso,
OKC_K_PARTY_ROLES_B kpr,
OKC_CONTACTS con
WHERE CHR.id = cp_chr_id
AND cso.cro_code = 'SALESPERSON'
AND cso.rle_code = 'LESSOR'
AND cso.buy_or_sell = CHR.buy_or_sell
AND kpr.chr_id = CHR.id
AND kpr.dnz_chr_id = CHR.id
AND kpr.rle_code = cso.rle_code
AND con.cpl_id = kpr.id
AND con.dnz_chr_id = CHR.id
AND con.cro_code = cso.cro_code
AND con.jtot_object1_code = cso.jtot_object_code;
SELECT mo_global.get_current_org_id()
FROM dual;
SELECT ctt.cust_trx_type_id
FROM ra_cust_trx_types ctt
WHERE ctt.name = 'Invoice-OKL';
SELECT bill_to_site_use_id
FROM OKC_K_HEADERS_B
WHERE id = p_chr_id;
SELECT id
FROM OKL_TRX_TYPES_TL
WHERE name = 'Accrual'
AND LANGUAGE = 'US';
-- Cursor to select the number of days for a reverse transaction
CURSOR accrual_reversal_days_csr IS
SELECT accrual_reversal_days
FROM OKL_SYS_ACCT_OPTS;
SELECT name
FROM hr_operating_units
WHERE organization_id = p_org_id;
SELECT RPAD (ite.object1_id1, 50, ' ') || khr.inv_organization_id
FROM okc_k_lines_b kle_fa,
okc_line_styles_b lse_fa,
okc_k_lines_b kle_ml,
okc_line_styles_b lse_ml,
okc_k_items ite,
okl_k_headers_full_v khr
WHERE kle_fa.id = cp_cle_id
AND kle_fa.chr_id = cp_chr_id
AND lse_fa.id = kle_fa.lse_id
AND lse_fa.lty_code = 'FREE_FORM1'
AND kle_ml.cle_id = kle_fa.id
AND lse_ml.id = kle_ml.lse_id
AND lse_ml.lty_code = 'ITEM'
AND ite.cle_id = kle_ml.id
AND kle_fa.dnz_chr_id = khr.id
AND ite.jtot_object1_code = 'OKX_SYSITEM';
SELECT bill_to_site_use_id
FROM OKC_K_LINES_B
WHERE id = p_cle_id
AND dnz_chr_id = p_chr_id;
SELECT lsy.lty_code
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lsy
WHERE cle.dnz_chr_id = p_khr_id
AND cle.id = p_kle_id
AND cle.lse_id = lsy.id;
SELECT object1_id1
FROM okc_k_items
WHERE cle_id = p_kle_id;
SELECT khr.attribute_category
,khr.attribute1
,khr.attribute2
,khr.attribute3
,khr.attribute4
,khr.attribute5
,khr.attribute6
,khr.attribute7
,khr.attribute8
,khr.attribute9
,khr.attribute10
,khr.attribute11
,khr.attribute12
,khr.attribute13
,khr.attribute14
,khr.attribute15
FROM okl_k_headers khr
WHERE khr.id = p_khr_id;
SELECT kle.attribute_category
,kle.attribute1
,kle.attribute2
,kle.attribute3
,kle.attribute4
,kle.attribute5
,kle.attribute6
,kle.attribute7
,kle.attribute8
,kle.attribute9
,kle.attribute10
,kle.attribute11
,kle.attribute12
,kle.attribute13
,kle.attribute14
,kle.attribute15
FROM okl_k_lines kle
WHERE kle.id = p_kle_id;
SELECT ID
FROM OKL_TRX_CONTRACTS
WHERE TRX_NUMBER = p_trx_number
AND REPRESENTATION_TYPE = 'PRIMARY';
l_tcnv_rec.update_status_yn := p_accrual_rec.override_status;
WRITE_TO_LOG('l_tcnv_rec.update_status_yn :'||l_tcnv_rec.update_status_yn);
l_dist_info_tbl.DELETE;
l_acc_gen_primary_key_tbl.DELETE;
UPDATE okl_strm_elements
SET accrued_yn = 'Y' where id = l_selv_tbl(i);
SELECT chr.contract_number,
chr.currency_code,
khr.currency_conversion_type,
khr.currency_conversion_date,
khr.currency_conversion_rate,
khr.pdt_id,
chr.start_date,
chr.sts_code,
khr.generate_accrual_yn,
khr.generate_accrual_override_yn,
khr.deal_type,
pdt.reporting_pdt_id -- MGAAP 7263014
FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr,
OKL_PRODUCTS pdt
WHERE chr.id = p_ctr_id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id;
SELECT accrual_yn
FROM OKL_STRM_TYPE_B
WHERE id = p_sty_id;
SELECT id
FROM OKL_TRX_TYPES_TL
WHERE name = l_try_name
AND LANGUAGE = 'US';
SELECT chr.contract_number
,khr.pdt_id
,NVL(khr.generate_accrual_override_yn, 'N') override_yn
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
,pdt.reporting_pdt_id
FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr,
OKL_PRODUCTS pdt --MGAAP 7263041
WHERE chr.id = p_khr_id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id;
SELECT sty.id sty_id,
ste.id ste_id,
ste.amount amount,
stm.kle_id
FROM OKL_STRM_TYPE_B sty,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, --MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr,
OKL_PRODUCTS pdt
WHERE sty.id = p_acceleration_rec.sty_id
AND stm.khr_id = p_acceleration_rec.khr_id
AND stm.kle_id = p_acceleration_rec.kle_id
AND khr.id = stm.khr_id
AND stm.sty_id = sty.id
AND sty.id = psty.sty_id
--AND psty.pdt_id = khr.pdt_id
AND ((psty.pdt_id = khr.pdt_id AND --MGAAP 7263041
khr.pdt_id = pdt.ID AND
p_representation_type = 'PRIMARY') OR
(khr.pdt_id = pdt.ID AND
pdt.reporting_pdt_id = psty.pdt_id AND
p_representation_type = 'SECONDARY')
)
AND khr.id = stm.khr_id
AND stm.id = ste.stm_id
AND ste.amount <> 0
-- AND ste.accrued_yn IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND psty.accrual_yn = 'Y'
AND TRUNC(ste.stream_element_date) >= TRUNC(p_acceleration_rec.accelerate_from_date)
AND TRUNC(ste.stream_element_date) <= TRUNC(p_acceleration_rec.accelerate_till_date);
SELECT sty.id sty_id,
ste.id ste_id,
ste.amount amount,
stm.kle_id
FROM OKL_STRM_TYPE_B sty,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, --MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr,
OKL_PRODUCTS pdt
WHERE sty.id = p_acceleration_rec.sty_id
AND stm.khr_id = p_acceleration_rec.khr_id
AND stm.kle_id = p_acceleration_rec.kle_id
AND khr.id = stm.khr_id
AND stm.sty_id = sty.id
AND sty.id = psty.sty_id
--AND psty.pdt_id = khr.pdt_id
AND ((psty.pdt_id = khr.pdt_id AND --MGAAP 7263041
khr.pdt_id = pdt.ID AND
p_representation_type = 'PRIMARY') OR
(khr.pdt_id = pdt.ID AND
pdt.reporting_pdt_id = psty.pdt_id AND
p_representation_type = 'SECONDARY')
)
AND khr.id = stm.khr_id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.accrued_yn IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND psty.accrual_yn = 'Y'
AND TRUNC(ste.stream_element_date) <= TRUNC(p_acceleration_rec.accelerate_till_date);
SELECT sty.id sty_id,
ste.id ste_id,
ste.amount amount,
stm.kle_id
FROM OKL_STRM_TYPE_B sty,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, --MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr,
OKL_PRODUCTS pdt
WHERE stm.khr_id = p_acceleration_rec.khr_id
AND khr.id = stm.khr_id
AND sty.id = p_acceleration_rec.sty_id
AND stm.sty_id = sty.id
AND sty.id = psty.sty_id
--AND psty.pdt_id = khr.pdt_id
AND ((psty.pdt_id = khr.pdt_id AND --MGAAP 7263041
khr.pdt_id = pdt.ID AND
p_representation_type = 'PRIMARY') OR
(khr.pdt_id = pdt.ID AND
pdt.reporting_pdt_id = psty.pdt_id AND
p_representation_type = 'SECONDARY')
)
AND khr.id = stm.khr_id
AND stm.id = ste.stm_id
AND ste.amount <> 0
-- AND ste.accrued_yn IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND psty.accrual_yn = 'Y'
AND TRUNC(ste.stream_element_date) >= TRUNC(p_acceleration_rec.accelerate_from_date)
AND TRUNC(ste.stream_element_date) <= TRUNC(p_acceleration_rec.accelerate_till_date);
SELECT sty.id sty_id,
ste.id ste_id,
ste.amount amount,
stm.kle_id
FROM OKL_STRM_TYPE_B sty,
--OKL_STREAMS stm,
OKL_STREAMS_REP_V stm, --MGAAP 7263041
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES psty,
OKL_K_HEADERS khr,
OKL_PRODUCTS pdt
WHERE stm.khr_id = p_acceleration_rec.khr_id
AND khr.id = stm.khr_id
AND sty.id = p_acceleration_rec.sty_id
AND stm.sty_id = sty.id
AND sty.id = psty.sty_id
--AND psty.pdt_id = khr.pdt_id
AND ((psty.pdt_id = khr.pdt_id AND --MGAAP 7263041
khr.pdt_id = pdt.ID AND
p_representation_type = 'PRIMARY') OR
(khr.pdt_id = pdt.ID AND
pdt.reporting_pdt_id = psty.pdt_id AND
p_representation_type = 'SECONDARY')
)
AND khr.id = stm.khr_id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.accrued_yn IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND psty.accrual_yn = 'Y'
AND TRUNC(ste.stream_element_date) <= TRUNC(p_acceleration_rec.accelerate_till_date);
-- Cursor to select transaction headers for reversal
-- modified cursor for bug# 2455956
-- removed to_date function in between statement as parameters are already of date datatype.
CURSOR reverse_trx_csr(p_khr_id NUMBER, p_reversal_date_to DATE, p_reversal_date_from DATE) IS
SELECT id, transaction_date,
trx_number -- MGAAP 7263041
FROM OKL_TRX_CONTRACTS
WHERE khr_id = p_khr_id
AND TRUNC(date_transaction_occurred) BETWEEN TRUNC(p_reversal_date_to) AND TRUNC(p_reversal_date_from)
AND tsu_code = l_tsu_code_ent
AND tcn_type = l_tcn_type
/* AND accrual_activity = l_accrual_activity */ -- MGAAP 7263041
AND representation_type = p_representation_type; -- MGAAP 7263041
SELECT id
FROM OKL_TXL_CNTRCT_LNS
WHERE tcn_id = p_tcn_id;
SELECT sty_id, amount, kle_id
FROM OKL_TXL_CNTRCT_LNS
WHERE tcn_id = p_tcn_id;
WRITE_TO_LOG('Prior to the call to Okl_Trx_Contracts_Pub.update_trx_contracts');
Okl_Trx_Contracts_Pub.update_trx_contracts
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_tbl => l_tcnv_tbl,
x_tcnv_tbl => x_rev_tcnv_tbl);
WRITE_TO_LOG('Return status after the call to Okl_Trx_Contracts_Pub.update_trx_contracts :'||x_return_status);
l_stream_tbl.DELETE;
-- Cursor to select Contract Number
CURSOR get_contract_number_csr(p_ctr_id NUMBER) IS
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id;
-- Cursor to select accrual transactions for cancellation using specified tcn_type
CURSOR reverse_trx_csr IS
SELECT trx.id,trx.transaction_date
FROM OKL_TRX_CONTRACTS trx, okl_trx_types_v typ
WHERE trx.khr_id = p_khr_id
AND trx.tsu_code = l_tsu_code_ent
AND trx.tcn_type = NVL(p_tcn_type,trx.tcn_type)
AND trx.try_id = typ.id
AND typ.name = 'Accrual'
AND TRUNC(trx.date_transaction_occurred) >= TRUNC(p_reverse_from)
AND TRUNC(trx.date_transaction_occurred) <= NVL(TRUNC(p_reverse_to), TRUNC(trx.date_transaction_occurred));
SELECT id
FROM OKL_TXL_CNTRCT_LNS
WHERE tcn_id = p_tcn_id;
Okl_Trx_Contracts_Pub.update_trx_contracts
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_tbl => l_tcnv_tbl,
x_tcnv_tbl => x_tcnv_tbl);
-- Cursor to select Contract Number
CURSOR get_contract_number_csr(p_ctr_id NUMBER) IS
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id;
-- Cursor to select accrual transactions for cancellation
CURSOR reverse_trx_csr(p_khr_id NUMBER) IS
SELECT id, transaction_date
FROM OKL_TRX_CONTRACTS
WHERE khr_id = p_khr_id
AND tsu_code = l_tsu_code_ent
AND tcn_type = l_tcn_type;
SELECT id
FROM OKL_TXL_CNTRCT_LNS
WHERE tcn_id = p_tcn_id;
Okl_Trx_Contracts_Pub.update_trx_contracts
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_tbl => l_tcnv_tbl,
x_tcnv_tbl => x_tcnv_tbl);
-- Cursor to select transaction headers for accrual catchup
CURSOR catchup_trx_csr(p_khr_id NUMBER) IS
SELECT id, transaction_date,
trx_number -- MGAAP 7263041
FROM OKL_TRX_CONTRACTS
WHERE khr_id = p_khr_id
AND tcn_type = l_tcn_type
AND tsu_code = l_tsu_code_ent
AND accrual_activity = l_non_accrual_activity
AND representation_type = p_representation_type; -- MGAAP 7263041
SELECT id
FROM OKL_TXL_CNTRCT_LNS
WHERE tcn_id = p_tcn_id;
SELECT sty_id, amount, kle_id
FROM OKL_TXL_CNTRCT_LNS
WHERE tcn_id = p_tcn_id;
WRITE_TO_LOG('Prior to the call to Okl_Trx_Contracts_Pub.update_trx_contracts');
Okl_Trx_Contracts_Pub.update_trx_contracts
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_tbl => l_tcnv_tbl,
x_tcnv_tbl => x_rev_tcnv_tbl);
WRITE_TO_LOG('Return status after the call to Okl_Trx_Contracts_Pub.update_trx_contracts :'||x_return_status);
l_stream_tbl.DELETE;
SELECT khr.attribute_category
,khr.attribute1
,khr.attribute2
,khr.attribute3
,khr.attribute4
,khr.attribute5
,khr.attribute6
,khr.attribute7
,khr.attribute8
,khr.attribute9
,khr.attribute10
,khr.attribute11
,khr.attribute12
,khr.attribute13
,khr.attribute14
,khr.attribute15
FROM okl_k_headers khr
WHERE khr.id = p_khr_id;
l_tcnv_rec.update_status_yn := p_accrual_rec.override_status;
WRITE_TO_LOG('l_tcnv_rec.update_status_yn :'||l_tcnv_rec.update_status_yn);
WRITE_TO_LOG('Prior to the call to Okl_Trx_Contracts_Pub.update_trx_contracts');
Okl_Trx_Contracts_Pub.update_trx_contracts
(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_tcnv_rec => l_tcnv_rec
,p_tclv_tbl => l_tclv_tbl
,x_tcnv_rec => x_tcnv_rec
,x_tclv_tbl => x_tclv_tbl );
WRITE_TO_LOG('Return Status after the call to Okl_Trx_Contracts_Pub.update_trx_contracts is '||x_return_status);
PROCEDURE UPDATE_BALANCES (p_khr_id IN NUMBER
,p_khr_number IN VARCHAR2
,p_amount IN NUMBER
,p_date IN DATE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR get_balances_id_csr (p_khr_id NUMBER) IS
SELECT id
FROM OKL_CONTRACT_BALANCES
WHERE khr_id = p_khr_id
AND kle_id IS NULL;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_BALANCES';
OKL_CONTRACT_BALANCES_PVT.update_contract_balance(
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_cblv_rec => l_cblv_rec
, x_cblv_rec => x_cblv_rec);
END UPDATE_BALANCES;
l_previous_override_status OKL_TRX_CONTRACTS.update_status_yn%TYPE;
-- Cursor to select the previous accrual and override statuses
-- bug 2474969, modified subquery from okl_trx_contracts to okl_trx_contracts_v
-- bug 2870449, 25-Mar-03, SGIYER, added khr_id = p_khr_id to outer query.
CURSOR last_status_csr(p_khr_id NUMBER) IS
SELECT accrual_status_yn, update_status_yn
FROM OKL_TRX_CONTRACTS
WHERE date_transaction_occurred = (SELECT MAX(date_transaction_occurred)
FROM OKL_TRX_CONTRACTS
WHERE khr_id = p_khr_id
AND tcn_type = l_tcn_type
AND tsu_code = l_tsu_code_ent
AND representation_type = 'PRIMARY') -- MGAAP 7263041
AND tcn_type = l_tcn_type
AND khr_id = p_khr_id
AND tsu_code = l_tsu_code_ent
AND representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT 'Y'
FROM OKL_TRX_CONTRACTS
WHERE tcn_type = l_tcn_type
AND khr_id = p_khr_id
AND tsu_code = l_tsu_code_ent
AND representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT TRUNC(DATE_LAST_INTERIM_INTEREST_CAL)
FROM OKL_K_HEADERS
WHERE ID = p_khr_id;
SELECT conversion_rate
FROM GL_DAILY_RATES
WHERE conversion_type = p_conversion_type
AND conversion_date = p_conversion_date
AND from_currency = p_from_currency
AND to_currency = p_to_currency
AND status_code = 'C';
SELECT 'Y'
FROM OKL_TRX_CONTRACTS trx, OKL_TRX_TYPES_V try, OKL_TXL_CNTRCT_LNS txl, OKL_STRM_TYPE_V sty
WHERE trx.khr_id = p_khr_id
AND trx.try_id = try.id
AND try.name = 'Accrual'
--Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
AND trx.tsu_code = 'PROCESSED'
AND TRUNC(last_day(trx.date_transaction_occurred)) = TRUNC(p_date)
AND trx.id = txl.tcn_id
AND txl.sty_id = sty.id
AND sty.stream_type_purpose = 'VARIABLE_INTEREST_INCOME'
AND trx.representation_type = 'PRIMARY';
SELECT TRUNC(MAX(trx.date_transaction_occurred)) last_accrual_date
FROM OKL_TRX_CONTRACTS trx,
OKL_TRX_TYPES_V try,
OKL_TXL_CNTRCT_LNS txl,
OKL_STRM_TYPE_V sty
WHERE trx.khr_id = p_khr_id
AND trx.try_id = try.id
AND try.name = 'Accrual'
--Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
AND trx.tsu_code = 'PROCESSED'
-- AND TRUNC(trx.date_transaction_occurred) = TRUNC(p_date)
AND trx.id = txl.tcn_id
AND txl.sty_id = sty.id
AND sty.stream_type_purpose = 'ACTUAL_INCOME_ACCRUAL'
AND trx.representation_type = 'PRIMARY';
SELECT accrual_yn
FROM OKL_STRM_TYPE_V
WHERE id = p_sty_id;
SELECT NVL(a.multi_gaap_yn,'N') multi_gaap_yn,
b.reporting_pdt_id
FROM OKL_K_HEADERS a,
OKL_PRODUCTS b
WHERE a.ID = p_contract_id
AND a.PDT_ID = b.ID;
select secondary_rep_method
from okl_sys_acct_opts;
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
G_trx_number_tbl.DELETE;
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
G_trx_number_tbl.DELETE;
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
G_trx_number_tbl.DELETE;
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_can_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_can_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_can_tcnv_tbl(x).amount,l_can_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_tbl(x).update_status_yn,0,0),16)||
RPAD(l_tcnv_tbl(x).currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_tbl(x).amount,l_tcnv_tbl(x).currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
G_trx_number_tbl.DELETE;
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
l_stream_tbl.DELETE(l_stream_tbl.COUNT);
l_stream_tbl_rep.DELETE(l_stream_tbl_rep.COUNT); -- MGAAP 7263041
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
UPDATE_BALANCES (l_contract_id
,l_contract_number
,l_tcnv_rec.amount
,l_tcnv_rec.date_transaction_occurred
,l_return_status
,l_msg_count
,l_msg_data);
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
UPDATE_BALANCES (l_contract_id
,l_contract_number
,l_tcnv_rec.amount
,l_tcnv_rec.date_transaction_occurred
,l_return_status
,l_msg_count
,l_msg_data);
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
UPDATE_BALANCES (l_contract_id
,l_contract_number
,l_tcnv_rec.amount
,l_tcnv_rec.date_transaction_occurred
,l_return_status
,l_msg_count
,l_msg_data);
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','PRIMARY'), 8));
RPAD(Okl_Accounting_Util.Get_Lookup_Meaning('YES_NO',l_tcnv_rec.update_status_yn,0,0),16)||
RPAD(l_tcnv_rec.currency_code,9)||
LPAD(Okl_Accounting_Util.FORMAT_AMOUNT(l_tcnv_rec.amount,l_tcnv_rec.currency_code),17) ||
LPAD(Okl_Accounting_Util.Get_Lookup_Meaning('OKL_REPRESENTATION_TYPE','SECONDARY'), 10));
UPDATE_BALANCES (l_contract_id
,l_contract_number
,l_tcnv_rec.amount
,l_tcnv_rec.date_transaction_occurred
,l_return_status
,l_msg_count
,l_msg_data);
-- Select the contract for error reporting
FND_FILE.PUT_LINE(FND_FILE.LOG,l_contract_number||', '||
FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_ERROR_STATUS')||' '||
x_return_status);
FND_MSG_PUB.Delete_Msg;
-- Select the contract for error reporting
FND_FILE.PUT_LINE(FND_FILE.LOG,l_contract_number||', '||
FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_ERROR_STATUS')||' '||
x_return_status);
FND_MSG_PUB.Delete_Msg;
-- Select the contract for error reporting
FND_FILE.PUT_LINE(FND_FILE.LOG,l_contract_number||', '||
FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_ERROR_STATUS')||' '||
x_return_status);
FND_MSG_PUB.Delete_Msg;
CURSOR chk_update_header_csr is
SELECT chr.contract_number
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr,
OKL_PROD_QLTY_VAL_UV pdt
WHERE chr.scs_code = 'LEASE'
AND chr.sts_code IN ('BOOKED','EVERGREEN')
AND chr.id = khr.id
AND khr.pdt_id = pdt.pdt_id
AND pdt.quality_name = 'REVENUE_RECOGNITION_METHOD'
AND pdt.quality_val = p_rev_rec_method
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
UNION
SELECT chr.contract_number
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE chr.scs_code = 'INVESTOR'
AND chr.sts_code = 'ACTIVE'
AND chr.id = khr.id
;
SELECT chr.contract_number contract_number
,count(cle.id) line_count
FROM OKC_K_HEADERS_B chr, OKC_K_LINES_B cle, okl_parallel_processes opp
WHERE opp.object_value = chr.contract_number
AND opp.assigned_process = p_seq_next
AND opp.object_type = 'ACCRUAL_CONTRACT'
AND chr.scs_code = 'LEASE'
AND chr.sts_code IN ('BOOKED','EVERGREEN')
AND chr.id = cle.chr_id
GROUP BY chr.contract_number
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
UNION
SELECT chr.contract_number contract_number
,count(cle.id) line_count
FROM OKC_K_HEADERS_B chr, OKC_K_LINES_B cle, okl_parallel_processes opp
WHERE opp.object_value = chr.contract_number
AND opp.assigned_process = p_seq_next
AND opp.object_type = 'ACCRUAL_CONTRACT'
AND chr.scs_code = 'INVESTOR'
AND chr.sts_code = 'ACTIVE'
AND chr.id = cle.chr_id
GROUP BY chr.contract_number;
SELECT chr.id
,chr.contract_number
,chr.start_date
,chr.sts_code
,khr.pdt_id
,khr.generate_accrual_yn
,khr.generate_accrual_override_yn
,khr.deal_type
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr,
OKL_PROD_QLTY_VAL_UV pdt
WHERE chr.contract_number = p_contract_number
AND chr.id = khr.id
AND chr.scs_code = 'LEASE'
AND chr.sts_code IN ('BOOKED','EVERGREEN')
AND khr.pdt_id = pdt.pdt_id
AND pdt.quality_name = 'REVENUE_RECOGNITION_METHOD'
AND pdt.quality_val = p_rev_rec_method
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
UNION
SELECT chr.id
,chr.contract_number
,chr.start_date
,chr.sts_code
,khr.pdt_id
,khr.generate_accrual_yn
,khr.generate_accrual_override_yn
,khr.deal_type
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE chr.contract_number = p_contract_number
AND chr.id = khr.id
AND chr.scs_code = 'INVESTOR'
AND chr.sts_code = 'ACTIVE'
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 end
;
-- Cursor to select the billing rule i.e. whether advance or arrears
-- commenting for bug# 2388940 04-Jun-02 SGIYER
-- as per discussion with PM. Advance/arrears must get a value NULL.
--CURSOR billing_rule_csr(p_ctr_id NUMBER) IS
--SELECT object1_id1
--FROM OKC_RULES_B r
--WHERE r.dnz_chr_id = p_ctr_id
--AND r.jtot_object1_code = 'OKX_INVRULE'
--AND r.rule_information_category = 'IRE';
select okl_opp_seq.nextval
into l_seq_next
from dual ;
for chk_update_header_csr_rec in chk_update_header_csr loop
INSERT INTO OKL_PARALLEL_PROCESSES
(object_type, object_value, assigned_process, process_status, start_date)
VALUES
('ACCRUAL_CONTRACT',chk_update_header_csr_rec.contract_number, to_char(l_seq_next),'PENDING_ASSIGNMENT', sysdate);
l_contract_tab.DELETE(i);
l_contract_tab.DELETE(i);
UPDATE OKL_PARALLEL_PROCESSES
SET
assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
volume = l_sort_tab1(i).line_count,
process_status = 'ASSIGNED'
WHERE object_Type = 'ACCRUAL_CONTRACT'
AND object_value = l_sort_tab1(i).contract_number
AND process_status = 'PENDING_ASSIGNMENT';
l_sort_tab1.DELETE(i);
SELECT OBJECT_VALUE
FROM OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_worker_id;
SELECT chr.id
,chr.contract_number
,chr.start_date
,chr.sts_code
,khr.pdt_id
,khr.generate_accrual_yn
,khr.generate_accrual_override_yn
,khr.deal_type
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr
WHERE chr.contract_number = p_khr_num
AND chr.id = khr.id
AND chr.scs_code = 'LEASE'
AND chr.sts_code IN ('BOOKED','EVERGREEN')
-- modified by zrehman for Bug#6788005 on 11-Feb-2008 start
UNION
SELECT chr.id
,chr.contract_number
,chr.start_date
,chr.sts_code
,khr.pdt_id
,khr.generate_accrual_yn
,khr.generate_accrual_override_yn
,khr.deal_type
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr
WHERE chr.contract_number = p_khr_num
AND chr.id = khr.id
AND chr.scs_code = 'INVESTOR'
AND chr.sts_code = 'ACTIVE'
-- modified by zrehman for Bug#6788005 on 11-Feb-2008 end
;
l_accrual_contracts_tbl.DELETE;
DELETE FROM OKL_PARALLEL_PROCESSES
WHERE OBJECT_VALUE = l_contract_num_tbl(i);
l_contract_num_tbl.DELETE;
SELECT chr.contract_number
,chr.start_date
,chr.sts_code
,khr.pdt_id
,khr.generate_accrual_yn
,khr.generate_accrual_override_yn
,khr.deal_type
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
,pdt.quality_val revenue_recognition_method
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr,
OKL_PROD_QLTY_VAL_UV pdt
WHERE chr.id = p_khr_id
AND chr.id = khr.id
AND chr.scs_code = 'LEASE'
AND chr.sts_code IN ('BOOKED','EVERGREEN')
AND khr.pdt_id = pdt.pdt_id
AND pdt.quality_name = 'REVENUE_RECOGNITION_METHOD'
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
UNION
SELECT chr.contract_number
,chr.start_date
,chr.sts_code
,khr.pdt_id
,khr.generate_accrual_yn
,khr.generate_accrual_override_yn
,khr.deal_type
,chr.currency_code
,khr.currency_conversion_type
,khr.currency_conversion_rate
,khr.currency_conversion_date
,'STREAMS' revenue_recognition_method
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE chr.id = p_khr_id
AND chr.id = khr.id
AND chr.scs_code = 'INVESTOR'
AND chr.sts_code = 'ACTIVE'
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 end
;
FOR x IN sty_select_basis_csr
LOOP
l_sty_select_basis := x.validate_khr_start_date;