[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
INV_ORGANIZATION_ID,
STS_CODE,
QCL_ID,
SCS_CODE,
CONTRACT_NUMBER,
CURRENCY_CODE,
CONTRACT_NUMBER_MODIFIER,
ARCHIVED_YN,
DELETED_YN,
CUST_PO_NUMBER_REQ_YN,
PRE_PAY_REQ_YN,
CUST_PO_NUMBER,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
DPAS_RATING,
COGNOMEN,
TEMPLATE_YN,
TEMPLATE_USED,
DATE_APPROVED,
DATETIME_CANCELLED,
AUTO_RENEW_DAYS,
DATE_ISSUED,
DATETIME_RESPONDED,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
RFP_TYPE,
CHR_TYPE,
KEEP_ON_MAIL_LIST,
SET_ASIDE_REASON,
SET_ASIDE_PERCENT,
RESPONSE_COPIES_REQ,
DATE_CLOSE_PROJECTED,
DATETIME_PROPOSED,
DATE_SIGNED,
DATE_TERMINATED,
DATE_RENEWED,
TRN_CODE,
START_DATE,
END_DATE,
AUTHORING_ORG_ID,
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ESTIMATED_AMOUNT,
ESTIMATED_AMOUNT_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
APPLICATION_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM okc_k_headers_v chrv
WHERE chrv.id = p_id;
x_chrv_rec.DELETED_YN,
x_chrv_rec.CUST_PO_NUMBER_REQ_YN,
x_chrv_rec.PRE_PAY_REQ_YN,
x_chrv_rec.CUST_PO_NUMBER,
x_chrv_rec.SHORT_DESCRIPTION,
x_chrv_rec.COMMENTS,
x_chrv_rec.DESCRIPTION,
x_chrv_rec.DPAS_RATING,
x_chrv_rec.COGNOMEN,
x_chrv_rec.TEMPLATE_YN,
x_chrv_rec.TEMPLATE_USED,
x_chrv_rec.DATE_APPROVED,
x_chrv_rec.DATETIME_CANCELLED,
x_chrv_rec.AUTO_RENEW_DAYS,
x_chrv_rec.DATE_ISSUED,
x_chrv_rec.DATETIME_RESPONDED,
x_chrv_rec.NON_RESPONSE_REASON,
x_chrv_rec.NON_RESPONSE_EXPLAIN,
x_chrv_rec.RFP_TYPE,
x_chrv_rec.CHR_TYPE,
x_chrv_rec.KEEP_ON_MAIL_LIST,
x_chrv_rec.SET_ASIDE_REASON,
x_chrv_rec.SET_ASIDE_PERCENT,
x_chrv_rec.RESPONSE_COPIES_REQ,
x_chrv_rec.DATE_CLOSE_PROJECTED,
x_chrv_rec.DATETIME_PROPOSED,
x_chrv_rec.DATE_SIGNED,
x_chrv_rec.DATE_TERMINATED,
x_chrv_rec.DATE_RENEWED,
x_chrv_rec.TRN_CODE,
x_chrv_rec.START_DATE,
x_chrv_rec.END_DATE,
x_chrv_rec.AUTHORING_ORG_ID,
x_chrv_rec.BUY_OR_SELL,
x_chrv_rec.ISSUE_OR_RECEIVE,
x_chrv_rec.ESTIMATED_AMOUNT,
x_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
x_chrv_rec.CURRENCY_CODE_RENEWED,
x_chrv_rec.UPG_ORIG_SYSTEM_REF,
x_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
x_chrv_rec.APPLICATION_ID,
x_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
x_chrv_rec.ORIG_SYSTEM_ID1,
x_chrv_rec.ORIG_SYSTEM_REFERENCE1,
x_chrv_rec.ATTRIBUTE_CATEGORY,
x_chrv_rec.ATTRIBUTE1,
x_chrv_rec.ATTRIBUTE2,
x_chrv_rec.ATTRIBUTE3,
x_chrv_rec.ATTRIBUTE4,
x_chrv_rec.ATTRIBUTE5,
x_chrv_rec.ATTRIBUTE6,
x_chrv_rec.ATTRIBUTE7,
x_chrv_rec.ATTRIBUTE8,
x_chrv_rec.ATTRIBUTE9,
x_chrv_rec.ATTRIBUTE10,
x_chrv_rec.ATTRIBUTE11,
x_chrv_rec.ATTRIBUTE12,
x_chrv_rec.ATTRIBUTE13,
x_chrv_rec.ATTRIBUTE14,
x_chrv_rec.ATTRIBUTE15,
x_chrv_rec.CREATED_BY,
x_chrv_rec.CREATION_DATE,
x_chrv_rec.LAST_UPDATED_BY,
x_chrv_rec.LAST_UPDATE_DATE,
x_chrv_rec.LAST_UPDATE_LOGIN;
SELECT
ID,
OBJECT_VERSION_NUMBER,
ISG_ID,
KHR_ID,
PDT_ID,
AMD_CODE,
DATE_FIRST_ACTIVITY,
GENERATE_ACCRUAL_YN,
GENERATE_ACCRUAL_OVERRIDE_YN,
DATE_REFINANCED,
CREDIT_ACT_YN,
TERM_DURATION,
CONVERTED_ACCOUNT_YN,
DATE_CONVERSION_EFFECTIVE,
SYNDICATABLE_YN,
SALESTYPE_YN,
DATE_DEAL_TRANSFERRED,
DATETIME_PROPOSAL_EFFECTIVE,
DATETIME_PROPOSAL_INEFFECTIVE,
DATE_PROPOSAL_ACCEPTED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PRE_TAX_YIELD,
AFTER_TAX_YIELD,
IMPLICIT_INTEREST_RATE,
IMPLICIT_NON_IDC_INTEREST_RATE,
TARGET_PRE_TAX_YIELD,
TARGET_AFTER_TAX_YIELD,
TARGET_IMPLICIT_INTEREST_RATE,
TARGET_IMPLICIT_NONIDC_INTRATE,
DATE_LAST_INTERIM_INTEREST_CAL,
DEAL_TYPE,
PRE_TAX_IRR,
AFTER_TAX_IRR,
EXPECTED_DELIVERY_DATE,
ACCEPTED_DATE,
PREFUNDING_ELIGIBLE_YN,
REVOLVING_CREDIT_YN
FROM OKL_K_HEADERS_V
WHERE OKL_K_HEADERS_V.id = p_id;
l_khrv_rec.LAST_UPDATED_BY,
l_khrv_rec.LAST_UPDATE_DATE,
l_khrv_rec.LAST_UPDATE_LOGIN,
l_khrv_rec.PRE_TAX_YIELD,
l_khrv_rec.AFTER_TAX_YIELD,
l_khrv_rec.IMPLICIT_INTEREST_RATE,
l_khrv_rec.IMPLICIT_NON_IDC_INTEREST_RATE,
l_khrv_rec.TARGET_PRE_TAX_YIELD,
l_khrv_rec.TARGET_AFTER_TAX_YIELD,
l_khrv_rec.TARGET_IMPLICIT_INTEREST_RATE,
l_khrv_rec.TARGET_IMPLICIT_NONIDC_INTRATE,
l_khrv_rec.DATE_LAST_INTERIM_INTEREST_CAL,
l_khrv_rec.DEAL_TYPE,
l_khrv_rec.PRE_TAX_IRR,
l_khrv_rec.AFTER_TAX_IRR,
l_khrv_rec.EXPECTED_DELIVERY_DATE,
l_khrv_rec.ACCEPTED_DATE,
l_khrv_rec.PREFUNDING_ELIGIBLE_YN,
l_khrv_rec.REVOLVING_CREDIT_YN
;
SELECT 'X'
FROM FND_LOOKUPS
WHERE lookup_type = 'OKL_REBOOK_REASON'
AND lookup_code = p_rebook_reason_code;
SELECT
str.id,
str.sty_id,
str.khr_id,
str.kle_id,
str.sgn_code,
str.say_code,
str.transaction_number,
str.active_yn,
str.object_version_number,
str.created_by,
str.creation_date,
str.last_updated_by,
str.last_update_date,
str.date_current,
str.date_working,
str.date_history,
str.comments,
str.program_id,
str.request_id,
str.program_application_id,
str.program_update_date,
str.last_update_login,
str.purpose_code,
sty.stream_type_purpose,
--Bug# 4212626
trx_id,
link_hist_stream_id
-- sty_code -- dropped after inclusion
FROM okl_streams str,
okl_strm_type_v sty
WHERE str.khr_id = p_rebook_chr_id
AND str.sty_id = sty.id
AND str.say_code = 'CURR';
SELECT id
FROM okl_streams
WHERE khr_id = p_khr_id
AND sty_id = p_sty_id
AND NVL(kle_id,-1) = NVL(p_kle_id, -1)
AND say_code <> 'HIST'
-- AND sgn_code <> 'INTC' -- bug 4737555
AND sgn_code NOT IN ('INTC','LATE_CALC') -- Bug 6472228
AND NVL(purpose_code,'XXXX') = NVL(p_purpose_code, 'XXXX');
SELECT *
FROM okl_strm_elements
WHERE stm_id = p_stm_id;
SELECT orig_system_id1
FROM okc_k_lines_b
WHERE id = p_line_id;
SELECT id
FROM okc_k_lines_b
WHERE orig_system_id1 = p_line_id;
SELECT NVL(ele.amount,0) amount
FROM okl_strm_elements ele,
okl_streams str,
--okl_strm_type_tl sty
okl_strm_type_v sty
WHERE NVL(str.kle_id,-1) = kleid
AND str.khr_id = khrId
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
--AND sty.name = 'BILLING ADJUSTMENT'
AND sty.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT'
--AND sty.LANGUAGE = 'US'
AND ele.stm_id = str.id
AND ele.date_billed IS NULL;
SELECT NVL(ele.amount,0) amount
FROM okl_strm_elements ele,
okl_streams str,
--okl_strm_type_tl sty
okl_strm_type_v sty
WHERE NVL(str.kle_id,-1) = kleid
AND str.khr_id = khrId
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
--AND sty.name = 'INVESTOR DISBURSEMENT ADJUSTMENT'
AND sty.stream_type_purpose = 'INVESTOR_DISB_ADJUSTMENT'
--AND sty.LANGUAGE = 'US'
AND ele.stm_id = str.id
AND ele.date_billed IS NULL;
SELECT code
FROM okl_strm_type_v
WHERE id = p_sty_id;
CURSOR deleted_streams_csr(p_orig_chr_id IN NUMBER,
p_rebook_chr_id IN NUMBER) IS
SELECT orig_stm.id orig_stm_id,
sty.stream_type_purpose,
cle.lse_id
FROM okl_streams orig_stm,
okc_k_lines_b cle,
okl_strm_type_v sty
WHERE orig_stm.khr_id = p_orig_chr_id
AND orig_stm.say_code = 'CURR'
--AND orig_stm.sgn_code <> 'INTC' -- bug 4737555
AND orig_stm.sgn_code NOT IN ('INTC','LATE_CALC') -- Bug6472228
AND cle.id(+) = orig_stm.kle_id
AND cle.dnz_chr_id(+) = orig_stm.khr_id
AND sty.id = orig_stm.sty_id
AND NOT EXISTS (SELECT 1
FROM okl_streams new_stm
WHERE new_stm.khr_id = p_rebook_chr_id
AND new_stm.say_code = 'CURR'
AND new_stm.link_hist_stream_id = orig_stm.id);
SELECT 1
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NULL
AND rownum = 1;
l_orig_strm_tbl.DELETE;
FOR deleted_streams_rec IN deleted_streams_csr
(p_orig_chr_id => p_orig_chr_id,
p_rebook_chr_id => p_rebook_chr_id)
LOOP
-- Ignore Insurance Streams
IF (NVL(deleted_streams_rec.lse_id,-1) = G_INSURANCE_LSE_ID) THEN
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Ignore Insurance Stream Line ID: '||deleted_streams_rec.orig_stm_id);
ELSIF (deleted_streams_rec.stream_type_purpose = 'INVESTOR_DISB_ADJUSTMENT') THEN
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Ignore Inv Disb Adj Stream Line ID: '||deleted_streams_rec.orig_stm_id);
ELSIF (deleted_streams_rec.stream_type_purpose IN
('INVESTOR_CNTRCT_OBLIGATION_PAY','INVESTOR_DISB_ADJUSTMENT','INVESTOR_EVERGREEN_RENT_PAY',
'INVESTOR_INTEREST_INCOME','INVESTOR_INTEREST_PAYABLE','INVESTOR_LATE_FEE_PAYABLE',
'INVESTOR_LATE_INTEREST_PAY','INVESTOR_PAYABLE','INVESTOR_PRETAX_INCOME',
'INVESTOR_PRINCIPAL_PAYABLE','INVESTOR_RECEIVABLE','INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_RENT_BUYBACK','INVESTOR_RENT_DISB_BASIS','INVESTOR_RENT_PAYABLE',
'INVESTOR_RESIDUAL_BUYBACK','INVESTOR_RESIDUAL_DISB_BASIS','INVESTOR_RESIDUAL_PAY',
'INVESTOR_VARIABLE_INTEREST','PV_RENT_SECURITIZED','PV_RV_SECURITIZED'))THEN
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Ignore Investor Stream Line ID: '||deleted_streams_rec.orig_stm_id);
ELSIF (deleted_streams_rec.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT') THEN
OPEN stm_not_billed_csr(p_stm_id => deleted_streams_rec.orig_stm_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Ignore Billing Adj Stream Line ID: '||deleted_streams_rec.orig_stm_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Historize Billing Adj Stream Line ID: '||deleted_streams_rec.orig_stm_id);
l_orig_strm_tbl(l_orig_strm_count).id := deleted_streams_rec.orig_stm_id;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Historize Deleted Stream Line ID: '||deleted_streams_rec.orig_stm_id);
l_orig_strm_tbl(l_orig_strm_count).id := deleted_streams_rec.orig_stm_id;
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_orig_strm_tbl,
x_stmv_tbl => x_orig_strm_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Deleted Orig strm to HIST '||x_return_status);
l_stmv_rec.last_updated_by := strm_hdr_rec.last_updated_by;
l_stmv_rec.last_update_date := strm_hdr_rec.last_update_date;
l_stmv_rec.program_update_date := strm_hdr_rec.program_update_date;
l_stmv_rec.last_update_login := strm_hdr_rec.last_update_login;
l_orig_strm_tbl.DELETE;
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_orig_strm_tbl,
x_stmv_tbl => x_orig_strm_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Orig strm to HIST '||x_return_status);
l_selv_tbl.DELETE; -- initialize table
l_selv_tbl(l_no_of_ele).program_update_date := strm_element_rec.program_update_date;
SELECT orig_system_id1
FROM OKC_K_HEADERS_V
WHERE id = p_chr_id;
SELECT id
FROM okl_streams
WHERE khr_id = p_chr_id
-- BUg 4737555
-- AND SGN_CODE <> 'INTC';
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_rec => l_stmv_rec,
x_stmv_rec => x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update API Call');
SELECT
rule.ID,
rule.OBJECT_VERSION_NUMBER,
rule.SFWT_FLAG,
rule.OBJECT1_ID1,
rule.OBJECT2_ID1,
rule.OBJECT3_ID1,
rule.OBJECT1_ID2,
rule.OBJECT2_ID2,
rule.OBJECT3_ID2,
rule.JTOT_OBJECT1_CODE,
rule.JTOT_OBJECT2_CODE,
rule.JTOT_OBJECT3_CODE,
rule.DNZ_CHR_ID,
rule.RGP_ID,
rule.PRIORITY,
rule.STD_TEMPLATE_YN,
rule.COMMENTS,
rule.WARN_YN,
rule.ATTRIBUTE_CATEGORY,
rule.ATTRIBUTE1,
rule.ATTRIBUTE2,
rule.ATTRIBUTE3,
rule.ATTRIBUTE4,
rule.ATTRIBUTE5,
rule.ATTRIBUTE6,
rule.ATTRIBUTE7,
rule.ATTRIBUTE8,
rule.ATTRIBUTE9,
rule.ATTRIBUTE10,
rule.ATTRIBUTE11,
rule.ATTRIBUTE12,
rule.ATTRIBUTE13,
rule.ATTRIBUTE14,
rule.ATTRIBUTE15,
rule.CREATED_BY,
rule.CREATION_DATE,
rule.LAST_UPDATED_BY,
rule.LAST_UPDATE_DATE,
rule.LAST_UPDATE_LOGIN,
--rule.TEXT,
rule.RULE_INFORMATION_CATEGORY,
rule.RULE_INFORMATION1,
rule.RULE_INFORMATION2,
rule.RULE_INFORMATION3,
rule.RULE_INFORMATION4,
rule.RULE_INFORMATION5,
rule.RULE_INFORMATION6,
rule.RULE_INFORMATION7,
rule.RULE_INFORMATION8,
rule.RULE_INFORMATION9,
rule.RULE_INFORMATION10,
rule.RULE_INFORMATION11,
rule.RULE_INFORMATION12,
rule.RULE_INFORMATION13,
rule.RULE_INFORMATION14,
rule.RULE_INFORMATION15,
rule.TEMPLATE_YN,
rule.ans_set_jtot_object_code,
rule.ans_set_jtot_object_id1,
rule.ans_set_jtot_object_id2,
rule.DISPLAY_SEQUENCE
FROM OKC_RULE_GROUPS_V rgp,
Okc_Rules_V rule
WHERE rgp.id = rule.rgp_id
AND rgp.rgd_code = p_rgd_code
AND RULE_INFORMATION_CATEGORY = p_rule_code
AND rgp.dnz_chr_id = p_chr_id
AND NVL(rgp.cle_id, -1) = p_cle_id;
l_rulv_rec.LAST_UPDATED_BY,
l_rulv_rec.LAST_UPDATE_DATE,
l_rulv_rec.LAST_UPDATE_LOGIN,
--l_rulv_rec.TEXT,
l_rulv_rec.RULE_INFORMATION_CATEGORY,
l_rulv_rec.RULE_INFORMATION1,
l_rulv_rec.RULE_INFORMATION2,
l_rulv_rec.RULE_INFORMATION3,
l_rulv_rec.RULE_INFORMATION4,
l_rulv_rec.RULE_INFORMATION5,
l_rulv_rec.RULE_INFORMATION6,
l_rulv_rec.RULE_INFORMATION7,
l_rulv_rec.RULE_INFORMATION8,
l_rulv_rec.RULE_INFORMATION9,
l_rulv_rec.RULE_INFORMATION10,
l_rulv_rec.RULE_INFORMATION11,
l_rulv_rec.RULE_INFORMATION12,
l_rulv_rec.RULE_INFORMATION13,
l_rulv_rec.RULE_INFORMATION14,
l_rulv_rec.RULE_INFORMATION15,
l_rulv_rec.TEMPLATE_YN,
l_rulv_rec.ans_set_jtot_object_code,
l_rulv_rec.ans_set_jtot_object_id1,
l_rulv_rec.ans_set_jtot_object_id2,
l_rulv_rec.DISPLAY_SEQUENCE ;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
OBJECT1_ID1,
OBJECT2_ID1,
OBJECT3_ID1,
OBJECT1_ID2,
OBJECT2_ID2,
OBJECT3_ID2,
JTOT_OBJECT1_CODE,
JTOT_OBJECT2_CODE,
JTOT_OBJECT3_CODE,
DNZ_CHR_ID,
RGP_ID,
PRIORITY,
STD_TEMPLATE_YN,
COMMENTS,
WARN_YN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
--TEXT,
RULE_INFORMATION_CATEGORY,
RULE_INFORMATION1,
RULE_INFORMATION2,
RULE_INFORMATION3,
RULE_INFORMATION4,
RULE_INFORMATION5,
RULE_INFORMATION6,
RULE_INFORMATION7,
RULE_INFORMATION8,
RULE_INFORMATION9,
RULE_INFORMATION10,
RULE_INFORMATION11,
RULE_INFORMATION12,
RULE_INFORMATION13,
RULE_INFORMATION14,
RULE_INFORMATION15,
TEMPLATE_YN,
ans_set_jtot_object_code,
ans_set_jtot_object_id1,
ans_set_jtot_object_id2,
DISPLAY_SEQUENCE
FROM Okc_Rules_V
WHERE okc_rules_v.rgp_id = p_rgp_id
AND okc_rules_v.object2_id1 = p_slh_id
AND RULE_INFORMATION_CATEGORY = DECODE(p_rdf_code,NULL,RULE_INFORMATION_CATEGORY,p_rdf_code);
l_rulv_rec.LAST_UPDATED_BY,
l_rulv_rec.LAST_UPDATE_DATE,
l_rulv_rec.LAST_UPDATE_LOGIN,
--l_rulv_rec.TEXT,
l_rulv_rec.RULE_INFORMATION_CATEGORY,
l_rulv_rec.RULE_INFORMATION1,
l_rulv_rec.RULE_INFORMATION2,
l_rulv_rec.RULE_INFORMATION3,
l_rulv_rec.RULE_INFORMATION4,
l_rulv_rec.RULE_INFORMATION5,
l_rulv_rec.RULE_INFORMATION6,
l_rulv_rec.RULE_INFORMATION7,
l_rulv_rec.RULE_INFORMATION8,
l_rulv_rec.RULE_INFORMATION9,
l_rulv_rec.RULE_INFORMATION10,
l_rulv_rec.RULE_INFORMATION11,
l_rulv_rec.RULE_INFORMATION12,
l_rulv_rec.RULE_INFORMATION13,
l_rulv_rec.RULE_INFORMATION14,
l_rulv_rec.RULE_INFORMATION15,
l_rulv_rec.TEMPLATE_YN,
l_rulv_rec.ans_set_jtot_object_code,
l_rulv_rec.ans_set_jtot_object_id1,
l_rulv_rec.ans_set_jtot_object_id2,
l_rulv_rec.DISPLAY_SEQUENCE ;
SELECT
id
,object_version_number
,sfwt_flag
,rgd_code
,sat_code
,rgp_type
,cle_id
,chr_id
,dnz_chr_id
,parent_rgp_id
,comments
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND rgd_code = 'LALEVL';
SELECT
id
,object_version_number
,sfwt_flag
,rgd_code
,sat_code
,rgp_type
,cle_id
,chr_id
,dnz_chr_id
,parent_rgp_id
,comments
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND rgd_code = 'LALEVL';
SELECT orig_system_id1
FROM okc_k_lines_v
WHERE id = p_cle_id;
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND ((cle_id = p_cle_id AND p_cle_id IS NOT NULL)
OR
cle_id IS NULL AND p_cle_id IS NULL)
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rules_v
WHERE rgp_id = p_rgp_id;
okl_rule_pub.delete_rule(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_tbl => l_rulv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update LASTCL rule: '||x_rbk_lastcl_rec.id);
okl_rule_pub.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => x_rbk_lastcl_rec,
x_rulv_rec => x_rulv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update LASTPR rule: '||x_rbk_lastpr_rec.id);
okl_rule_pub.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => x_rbk_lastpr_rec,
x_rulv_rec => x_rulv_rec
);
SELECT rbk.id,
rbk.object1_id1,
rbk.object1_id2,
rbk.jtot_object1_code,
rbk.rle_code
FROM okc_k_party_roles_b rbk
WHERE rbk.chr_id = p_rebook_chr_id
AND rbk.jtot_object1_code = 'OKX_VENDOR' -- Bug# 3311403
AND NOT EXISTS (
SELECT 'Y'
FROM okc_k_party_roles_b orig
WHERE orig.object1_id1 = rbk.object1_id1
AND orig.object1_id2 = rbk.object1_id2
AND orig.jtot_object1_code = rbk.jtot_object1_code
AND orig.rle_code = rbk.rle_code
AND orig.chr_id = p_orig_chr_id
AND orig.jtot_object1_code = 'OKX_VENDOR' -- Bug# 3311403
);
SELECT rgp_id
FROM okc_rg_party_roles_v
WHERE cpl_id = p_cpl_id
AND dnz_chr_id = p_chr_id;
SELECT *
FROM okl_party_payment_hdr
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL;
SELECT id
FROM okl_party_payment_hdr
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL;
SELECT party.id party_id,
party.object1_id1,
pyd.ID pyd_id,
pyd.CPL_ID,
pyd.VENDOR_ID,
pyd.PAY_SITE_ID,
pyd.PAYMENT_TERM_ID,
pyd.PAYMENT_METHOD_CODE,
pyd.PAY_GROUP_CODE,
pyd.PAYMENT_HDR_ID,
pyd.PAYMENT_START_DATE,
pyd.PAYMENT_FREQUENCY,
pyd.REMIT_DAYS,
pyd.DISBURSEMENT_BASIS,
pyd.DISBURSEMENT_FIXED_AMOUNT,
pyd.DISBURSEMENT_PERCENT,
pyd.PROCESSING_FEE_BASIS,
pyd.PROCESSING_FEE_FIXED_AMOUNT,
pyd.PROCESSING_FEE_PERCENT,
pyd.PAYMENT_BASIS
FROM okc_k_party_roles_b party,
okl_party_payment_dtls pyd
WHERE party.dnz_chr_id = p_chr_id
AND party.rle_code = 'OKL_VENDOR'
AND party.cle_id IS NULL
AND party.id = pyd.cpl_id (+);
select *
from okl_party_payment_dtls
where payment_hdr_id = p_pmnt_hdr_id
and vendor_id = p_vendor_id;
l_rbk_evg_hdr_id := rbk_evg_hdr_rec.id; -- saved for use during detail update/create
okl_party_payments_pvt.update_party_payment_hdr(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pphv_rec => l_pphv_rec,
x_pphv_rec => x_pphv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update party payment detail for Vendor: '||orig_party_rec.object1_id1);
okl_party_payments_pvt.update_party_payment_dtls(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ppydv_rec => l_ppydv_u_rec,
x_ppydv_rec => x_ppydv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update Status: '||x_return_status);
SELECT start_date,
end_date,
term_duration,
pre_tax_yield,
after_tax_yield,
target_pre_tax_yield,
target_after_tax_yield,
pre_tax_irr,
after_tax_irr,
implicit_interest_rate,
--Bug# 4558486
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
--Bug# 4558486
-- sjalasut, added for rebook change control enhancement. START
,date_tradein
,tradein_amount
,tradein_description
-- sjalasut, added for rebook change control enhancement. END
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
SELECT hdr.id hdr_id,
hdr.passthru_term,
dtl.*
FROM okl_party_payment_dtls dtl,
okl_party_payment_hdr hdr
WHERE cpl_id = p_cpl_id
AND cle_id = p_cle_id
AND hdr.id = dtl.payment_hdr_id;
SELECT dtl.*
FROM okl_party_payment_dtls dtl,
okl_party_payment_hdr hdr
WHERE vendor_id = p_vendor_id
AND cle_id = p_cle_id
AND hdr.passthru_term = p_term
AND hdr.id = dtl.payment_hdr_id;
SELECT id
FROM okl_party_payment_hdr
WHERE cle_id = p_cle_id
AND passthru_term = p_term;
okl_party_payments_pvt.update_party_payment_dtls(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ppydv_rec => l_ppydv_rec,
x_ppydv_rec => x_ppydv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'>>>> Pth detail updated: '||x_return_status);
SELECT okc.id, okc.orig_system_id1, okc.lse_id
FROM okc_k_lines_b okc,
okl_k_lines okl
WHERE dnz_chr_id = p_chr_id
AND lse_id = 52 -- FEE top line
AND okl.fee_type = 'PASSTHROUGH'
AND okc.id = okl.id
AND okc.orig_system_id1 IS NOT NULL -- only old lines
--Bug# 4959361
AND okc.sts_code <> 'TERMINATED'
UNION -- Service line
SELECT okc.id, okc.orig_system_id1, okc.lse_id
FROM okc_k_lines_b okc
WHERE dnz_chr_id = p_chr_id
AND lse_id = 48 -- SERVICE top line
AND okc.orig_system_id1 IS NOT NULL -- only old lines
--Bug# 4959361
AND okc.sts_code <> 'TERMINATED'
UNION -- Asset line
SELECT okc.id, okc.orig_system_id1, okc.lse_id
FROM okc_k_lines_b okc
WHERE dnz_chr_id = p_chr_id
AND lse_id = 33 -- Fin. Asset top line
AND okc.orig_system_id1 IS NOT NULL -- only old lines
--Bug# 4959361
AND okc.sts_code <> 'TERMINATED';
SELECT *
FROM okl_party_payment_hdr
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id;
SELECT id
FROM okl_party_payment_hdr
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND passthru_term = p_pth_term;
SELECT object1_id1 vendor_id,
id
FROM okc_k_party_roles_b
WHERE cle_id = p_cle_id
AND rle_code = 'OKL_VENDOR'
AND dnz_chr_id = p_chr_id;
SELECT id
FROM okc_k_party_roles_b
WHERE cle_id = p_cle_id
AND object1_id1 = p_vendor_id
AND rle_code = 'OKL_VENDOR';
SELECT orig.object1_id1,
orig.id
FROM okc_k_party_roles_b orig
WHERE orig.cle_id = p_orig_cle_id
AND orig.dnz_chr_id = p_orig_chr_id
AND orig.rle_code = 'OKL_VENDOR'
AND NOT EXISTS (
SELECT 'Y'
FROM okc_k_party_roles_b rbk
WHERE rbk.rle_code = 'OKL_VENDOR'
AND rbk.dnz_chr_id = p_rbk_chr_id
AND rbk.cle_id = p_rbk_cle_id
AND orig.object1_id1 = rbk.object1_id1
);
l_c_pphv_tbl.DELETE;
l_u_pphv_tbl.DELETE;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PTH header insert for: '||pth_rec.orig_system_id1||': '||x_return_status);
okl_party_payments_pvt.update_party_payment_hdr(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pphv_tbl => l_u_pphv_tbl,
x_pphv_tbl => x_pphv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PTH header update for: '||pth_rec.orig_system_id1||': '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'>> Vendor to be deleted: '||rbk_del_vendor_rec.object1_id1);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'>> Vendor to be deleted cpl_id: '||rbk_del_vendor_rec.id);
okl_maintain_fee_pvt.delete_passthru_party(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpl_id => rbk_del_vendor_rec.id
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Vendor deleted: '||x_return_status);
SELECT line.*
FROM okl_k_Lines_full_v line,
okc_line_styles_v style
WHERE line.sts_code = 'BOOKED'
AND style.lty_code = 'FEE'
AND line.dnz_chr_id = p_chr_id
AND line.lse_id = style.id
AND line.fee_type = 'GENERAL';
SELECT id
FROM okc_k_lines_v
WHERE cle_id = p_cle_id
AND sts_code = 'BOOKED';
SELECT line.*
FROM okl_k_Lines_full_v line,
okc_line_styles_v style
WHERE style.lty_code = 'FEE'
AND line.dnz_chr_id = p_chr_id
AND line.lse_id = style.id
AND line.orig_system_id1 IS NULL;
SELECT id
FROM okl_streams
WHERE kle_id = p_kle_id
AND say_code <> 'HIST';
SELECT trx_number,
rbr_code,
date_transaction_occurred
FROM okl_trx_contracts
WHERE khr_id = p_chr_id
AND tcn_type = 'TRBK'
AND tsu_code = 'ENTERED'
AND representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT okc.id
,okc.dnz_chr_id
,item.id item_id
,item.object1_id1
,item.object1_id2
,okc.start_date
,okc.end_date
,okl.amount
,okl.initial_direct_cost
,okl.qte_id
,okl.funding_date
,okc.orig_system_id1
,okl.fee_type
--,partyb.id role_id -- change added for Rebook Change Control Enhancement
--,partyb.object1_id1 vendor_party_id -- change added for Rebook Change Control Enhancement
--,partyb.rle_code -- change added for Rebook Change Control Enhancement
--,partyb.jtot_object1_code -- change added for Rebook Change Control Enhancement
--Bug# 4558486
,okl.attribute_category
,okl.attribute1
,okl.attribute2
,okl.attribute3
,okl.attribute4
,okl.attribute5
,okl.attribute6
,okl.attribute7
,okl.attribute8
,okl.attribute9
,okl.attribute10
,okl.attribute11
,okl.attribute12
,okl.attribute13
,okl.attribute14
,okl.attribute15
FROM okc_k_lines_b okc
,okl_k_lines okl
,okc_k_items item
,okc_line_styles_b style
--,okc_k_party_roles_b partyb -- change added for Rebook Change Control Enhancement
WHERE okc.id = okl.id
AND okc.id = item.cle_id
AND okc.dnz_chr_id = item.dnz_chr_id
AND okl.id = item.cle_id
AND okc.dnz_chr_id = cp_chr_id
AND okc.lse_id = style.id
AND item.jtot_object1_code = 'OKL_STRMTYP'
AND style.lty_code = 'FEE'
AND okc.orig_system_id1 IS NOT NULL
AND (okl.fee_purpose_code = 'SALESTAX' -- Only sales tax related fee line
OR okl.fee_purpose_code IS NULL)
--Bug# 4959361
AND okc.sts_code <> 'TERMINATED'; -- change added for Rebook Change Control Enhancement
SELECT okc.id,
item.id item_id,
item.object1_id1,
item.object1_id2,
okc.start_date,
okc.end_date,
okl.amount,
okl.initial_direct_cost,
okl.qte_id,
okl.funding_date,
okc.orig_system_id1,
--partyb.id role_id,
--partyb.object1_id1 vendor_party_id,
--partyb.rle_code,
--partyb.jtot_object1_code,
okl.fee_type,
--Bug# 4558486
okl.attribute_category,
okl.attribute1,
okl.attribute2,
okl.attribute3,
okl.attribute4,
okl.attribute5,
okl.attribute6,
okl.attribute7,
okl.attribute8,
okl.attribute9,
okl.attribute10,
okl.attribute11,
okl.attribute12,
okl.attribute13,
okl.attribute14,
okl.attribute15
FROM okc_k_lines_b okc,
okl_k_lines okl,
okc_k_items item,
okc_line_styles_b style
--okc_k_party_roles_b partyb
WHERE okc.id = okl.id
AND okc.id = item.cle_id
AND okc.dnz_chr_id = item.dnz_chr_id
AND okl.id = item.cle_id
AND okc.dnz_chr_id = cp_chr_id
AND okc.id = cp_cle_id
AND okc.lse_id = style.id
AND item.jtot_object1_code = 'OKL_STRMTYP'
AND style.lty_code = 'FEE';
SELECT id, object1_id1, object1_id2,name
FROM okc_k_party_roles_b cpl,
okx_vendors_v ven
WHERE cpl.cle_id = p_line_id
AND cpl.dnz_chr_id = p_chr_id
AND cpl.rle_code = 'OKL_VENDOR'
AND ven.id1 = cpl.object1_id1;
SELECT id, object1_id1
FROM okc_k_party_roles_b
WHERE cle_id = p_line_id
AND dnz_chr_id = p_chr_id
AND rle_code = 'OKL_VENDOR';
SELECT id
FROM okc_k_items
WHERE cle_id = p_line_id
AND dnz_chr_id = p_chr_id
AND jtot_object1_code = 'OKL_STRMTYP';
SELECT okc_cov_ast.orig_system_id1, okc_cov_ast.id line_id, okc_fin.name, SUBSTR(okc_fin.item_description,1,200) item_description,
okl.capital_amount, okl.amount,
item.id item_id, item.object1_id1, item.object1_id2, item.jtot_object1_code
FROM okc_k_lines_b okc_cov_ast,
okl_k_lines okl,
okc_line_styles_b style,
okc_k_items item,
okc_k_lines_v okc_fin
WHERE okc_cov_ast.cle_id = p_top_line_id
AND okc_cov_ast.dnz_chr_id = p_chr_id
AND okc_cov_ast.id = okl.id
AND okc_cov_ast.id = item.cle_id
AND okc_cov_ast.dnz_chr_id = item.dnz_chr_id
AND item.jtot_object1_code = 'OKX_COVASST'
AND okc_cov_ast.lse_id = style.id
AND style.lty_code = 'LINK_FEE_ASSET'
AND okc_fin.id = TO_NUMBER(item.object1_id1)
AND okc_fin.dnz_chr_id = p_chr_id
--Bug# 4959361
AND okc_cov_ast.sts_code <> 'TERMINATED';
SELECT okc.orig_system_id1, okc.id line_id, okc.name, okc.item_description,
okl.capital_amount,
item.id item_id, item.object1_id1, item.object1_id2, item.jtot_object1_code
FROM okc_k_lines_v okc,
okl_k_lines okl,
okc_line_styles_b style,
okc_k_items item
WHERE okc.id = p_sub_line_id
AND okc.dnz_chr_id = p_chr_id
AND okc.id = okl.id
AND okc.id = item.cle_id
AND okc.dnz_chr_id = item.dnz_chr_id
AND item.jtot_object1_code = 'OKX_COVASST'
AND okc.lse_id = style.id
AND style.lty_code = 'LINK_FEE_ASSET';
select origl.id orig_link_fee_id,
origk.id orig_link_fee_item_id,
origp.id orig_parent_fee_id
from okc_k_lines_b origl,
okc_k_lines_b origp,
okc_k_items_v origk
where origl.dnz_chr_id = p_orig_chr_id
and origl.lse_id = 53 -- LINK_FEE_ASSET
and origl.cle_id = origp.id
and origl.dnz_chr_id = origp.dnz_chr_id
and origp.lse_id = 52 -- FEE
and origp.dnz_chr_id = p_orig_chr_id
and origl.id = origk.cle_id
and origk.jtot_object1_code = 'OKX_COVASST'
and not exists (
select 'Y'
from okc_k_lines_b rbkl
where to_char(origl.id) = rbkl.orig_system_id1
and rbkl.dnz_chr_id = p_rbk_chr_id
);
SELECT okc.id,
okl.amount,
okc.orig_system_id1 orig_fee_id,
--Bug# 4558486
okl.attribute_category,
okl.attribute1,
okl.attribute2,
okl.attribute3,
okl.attribute4,
okl.attribute5,
okl.attribute6,
okl.attribute7,
okl.attribute8,
okl.attribute9,
okl.attribute10,
okl.attribute11,
okl.attribute12,
okl.attribute13,
okl.attribute14,
okl.attribute15
FROM okc_k_lines_b okc,
okl_k_lines okl,
okc_line_styles_b style
WHERE okc.id = okl.id
AND okc.dnz_chr_id = p_rbk_chr_id
AND okc.lse_id = style.id
AND style.lty_code = 'FEE'
AND okl.fee_type = 'ABSORBED' -- Absorbed Fee only
AND okl.fee_purpose_code = 'RVI' -- Only RVI related fee line
AND okc.orig_system_id1 IS NOT NULL
AND okc.cle_id IS NULL; -- Only top line
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Line deleted');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Sub line delete: '||fee_subline_rec.id);
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Sub line deleted');
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_orig_fee_strm_tbl,
x_stmv_tbl => x_orig_fee_strm_tbl
);
l_rbk_fee_tbl.DELETE;
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_rvi_clev_rec,
p_klev_rec => l_rvi_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RVI Fee Amount updated: '||x_return_status);
okl_maintain_fee_pvt.update_fee_type(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_fee_types_rec => l_orig_st_fee_rec,
x_fee_types_rec => x_orig_st_fee_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL.H*** After Update Top line: '||x_return_status);
END IF; -- end of attribute comparision and calling update_fee_type API
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update Freq to: '||x_rulv_freq_rec.object1_id1);
okl_rule_pub.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => x_rulv_freq_rec,
x_rulv_rec => x_rulv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update LAFREQ : '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update period to: '||x_rulv_exp_rec.rule_information1);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update amount to: '||x_rulv_exp_rec.rule_information2);
okl_rule_pub.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => x_rulv_exp_rec,
x_rulv_rec => x_rulv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update LAFEXP : '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update Party...');
okl_okc_migration_pvt.update_k_party_role(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec,
x_cplv_rec => x_cplv_rec);
okl_k_party_roles_pvt.update_k_party_role(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec,
x_cplv_rec => x_cplv_rec,
p_kplv_rec => l_kplv_rec,
x_kplv_rec => x_kplv_rec );
upd := 0; -- update line count
l_u_line_item_tbl.DELETE;
ins := 0; -- insert line count
l_c_line_item_tbl.DELETE;
l_cov_ast_tbl.DELETE;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update link asset for: '||c_fee_rbk_rec.id);
IF (rbk_fee_asset_rec.orig_system_id1 IS NOT NULL) THEN -- update the line
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'--->Update Rebook Asset: '||rbk_fee_asset_rec.line_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update record: '||upd||': '||l_u_line_item_tbl(upd).name);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'--->Insert Rebook Asset: '||rbk_fee_asset_rec.line_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Insert record: '||ins||': '||rbk_fee_asset_rec.name);
debug_message('Insert record: '||ins||': '||l_c_line_item_tbl(ins).name);
okl_contract_line_item_pvt.update_contract_line_item(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_item_tbl => l_u_line_item_tbl,
x_line_item_tbl => x_u_line_item_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Link Fee asset updated; '||l_u_line_item_tbl.COUNT);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Link Fee asset inserted; '||l_cov_ast_tbl.COUNT);
debug_message('Link Fee asset inserted; '||l_c_line_item_tbl.COUNT);
SELECT *
FROM okl_k_lines_full_v
WHERE dnz_chr_id = p_chr_id
AND orig_system_id1 IS NOT NULL;
SELECT line.*
FROM okl_k_lines_full_v line,
okc_line_styles_v style
WHERE line.dnz_chr_id = p_chr_id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
AND orig_system_id1 IS NULL;
SELECT line.*,
parent_cle.orig_system_id1 parent_orig_system_id1,
parent_cle.start_date parent_orig_start_date
FROM okl_k_lines_full_v line,
okc_line_styles_v style,
okc_k_lines_b parent_cle
WHERE line.dnz_chr_id = p_chr_id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM2'
AND line.orig_system_id1 IS NULL
AND parent_cle.orig_system_id1 IS NOT NULL
AND line.cle_id = parent_cle.id
AND line.dnz_chr_id = parent_cle.dnz_chr_id;
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rules_v
WHERE rgp_id = p_rgp_id;
SELECT id,
start_date
FROM okc_k_lines_b
CONNECT BY PRIOR id = cle_id
START WITH id = p_top_line_id;
SELECT cim.id,
cim.cle_id,
cim.number_of_items,
cim.object1_id1,
cim.object1_id2,
cim.jtot_object1_code,
cle.lse_id
FROM okc_k_items cim,
okc_k_lines_b cle
WHERE cim.cle_id = cle.id
AND cle.id = p_cle_id;
SELECT cpl.id,
cpl.object1_id1,
cpl.object1_id2,
cpl.jtot_object1_code,
cpl.rle_code
FROM okc_k_party_roles_b cpl
WHERE cpl.cle_id = p_cle_id
AND cpl.dnz_chr_id = p_chr_id
AND cpl.jtot_object1_code = 'OKX_VENDOR';
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Rule Deleted: '||l_rulv_tbl(i).id);
okl_rule_pub.delete_rule(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_tbl => l_rulv_tbl
);
IF (rebook_line_rec.orig_system_id1 IS NOT NULL) THEN -- Old Line, Update
l_clev_tbl(i).id := rebook_line_rec.orig_system_id1;
okl_okc_migration_pvt.update_k_party_role(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec,
x_cplv_rec => x_cplv_rec);
okl_debug_pub.logmessage(' okl_okc_migration_pvt.update_contract_item sync_failed ');
okl_okc_migration_pvt.update_contract_item(
p_api_version => 1.0,
p_init_msg_list => okc_api.g_false,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_cimv_rec =>l_cim_rec,
x_cimv_rec =>x_cim_rec);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' okc_cim_pvt.update_row sync_failed= ');
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => l_clev_tbl,
p_klev_tbl => l_klev_tbl,
x_clev_tbl => x_clev_tbl,
x_klev_tbl => x_klev_tbl
);
l_tran_date := rebook_new_line_rec.start_date; --Used later to update line
l_tran_date := rebook_new_ib_line_rec.parent_orig_start_date; --Used later to update line
INSERT INTO dd_dummy VALUES (x_new_clev_tbl(i).id||', '||x_new_clev_tbl(i).start_date);
PROCEDURE check_and_update_date(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_orig_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
p_rebook_chr_id IN OKC_K_HEADERS_V.ID%TYPE
) IS
CURSOR term_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
SELECT term_duration,
start_date
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
SELECT start_date,
end_date,
term_duration
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
l_proc_name VARCHAR2(35) := 'CHECK_AND_UPDATE_DATE';
check_update_failed EXCEPTION;
RAISE check_update_failed;
RAISE check_update_failed;
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => OKL_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
RAISE check_update_failed;
RAISE check_update_failed;
WHEN check_update_failed THEN
x_return_status := OKL_API.G_RET_STS_ERROR;
END check_and_update_date;
l_update_clev_tbl clev_tbl_type;
l_update_klev_tbl klev_tbl_type;
x_update_clev_tbl clev_tbl_type;
x_update_klev_tbl klev_tbl_type;
SELECT id
FROM OKL_TRX_CONTRACTS
WHERE khr_id_new = p_chr_id_new
AND representation_type = 'PRIMARY'; -- MGAAP 7263041
check_and_update_date(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_orig_chr_id => l_orig_chr_id,
p_rebook_chr_id => p_rebook_chr_id
);
x_new_klev_tbl => l_update_klev_tbl,
x_new_clev_tbl => l_update_clev_tbl
);
IF l_update_clev_tbl.COUNT > 0 THEN
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => l_update_clev_tbl,
p_klev_tbl => l_update_klev_tbl,
x_clev_tbl => x_update_clev_tbl,
x_klev_tbl => x_update_klev_tbl);
SELECT date_transaction_occurred
FROM okl_trx_contracts
WHERE khr_id = p_chr_id
AND tcn_type = 'TRBK'
AND tsu_code = 'ENTERED'
AND representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT strm.id
FROM okl_streams strm,
okl_strm_type_v TYPE
WHERE TYPE.id = strm.sty_id
AND TYPE.stream_type_subclass = 'INVESTOR_DISBURSEMENT'
AND strm.khr_id = p_chr_id
AND strm.say_code = 'CURR';
SELECT strm.id
FROM okl_streams strm,
okl_strm_type_v TYPE
WHERE TYPE.id = strm.sty_id
/*
AND TYPE.name IN (
'INVESTOR RENTAL ACCRUAL',
'INVESTOR PRE-TAX INCOME',
'INVESTOR INTEREST INCOME',
'INVESTOR VARIABLE INTEREST'
)
*/
AND TYPE.stream_type_purpose IN (
'INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_PRE_TAX_INCOME',
'INVESTOR_INTEREST_INCOME',
'INVESTOR_VARIABLE_INTEREST'
)
AND strm.khr_id = p_chr_id
AND strm.say_code = 'CURR';
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_disb_strm_tbl,
x_stmv_tbl => x_disb_strm_tbl
);
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_accu_strm_tbl,
x_stmv_tbl => x_accu_strm_tbl
);
SELECT ktrx.date_transaction_occurred,ktrx.id,
khr.multi_gaap_yn, pdt.reporting_pdt_id -- MGAAP 7263041
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx,
okl_k_headers khr,
okl_products pdt
WHERE ktrx.khr_id_new = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND CHR.id = p_chr_id
AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK'
-- MGAAP start 7263041
AND ktrx.representation_type = 'PRIMARY'
AND chr.id = khr.id
AND khr.pdt_id = pdt.id;
SELECT contract_number
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT contract_number, sts_code
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT id
FROM okc_k_lines_v
WHERE chr_id = p_chr_id;
SELECT okl_rbk_seq.NEXTVAL
INTO l_seq_no
FROM DUAL;
Okl_Okc_Migration_Pvt.update_contract_header(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
x_chrv_rec => x_chrv_rec
);
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
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_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
SELECT '!',chr.orig_system_id1, ktrx.id
FROM okc_k_headers_b chr,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND chr.id = p_chr_id
AND chr.orig_system_source_code = 'OKL_REBOOK'
AND ktrx.representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT '!', ktrx.id
FROM okc_k_headers_b chr,
okl_trx_contracts ktrx
WHERE chr.id = p_chr_id
AND ktrx.khr_id = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.representation_type = 'PRIMARY' -- MGAAP 7263041
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.khr_id = chr.id
AND rbk_khr.status <> 'PROCESSED');
SELECT new_stm.id new_stm_id,
new_stm.kle_id
FROM okl_streams new_stm
WHERE new_stm.khr_id = p_khr_id
AND new_stm.say_code = 'WORK';
SELECT orig_stm.id orig_stm_id
FROM okl_streams new_stm,
okc_k_lines_b cle,
okl_streams orig_stm
WHERE new_stm.id = p_new_stm_id
AND cle.id = new_stm.kle_id
AND cle.dnz_chr_id = p_rbk_khr_id
AND orig_stm.khr_id = p_orig_khr_id
AND orig_stm.kle_id = cle.orig_system_id1
AND orig_stm.sty_id = new_stm.sty_id
AND NVL(orig_stm.purpose_code,'ORIG') = NVL(new_stm.purpose_code,'ORIG')
AND orig_stm.say_code = 'CURR';
SELECT orig_stm.id orig_stm_id
FROM okl_streams new_stm,
okl_streams orig_stm
WHERE new_stm.id = p_new_stm_id
AND orig_stm.khr_id = p_orig_khr_id
AND orig_stm.kle_id IS NULL
AND orig_stm.sty_id = new_stm.sty_id
AND NVL(orig_stm.purpose_code,'ORIG') = NVL(new_stm.purpose_code,'ORIG')
AND orig_stm.say_code = 'CURR';
SELECT orig_stm.id orig_stm_id
FROM okl_streams new_stm,
okl_streams orig_stm
WHERE new_stm.id = p_new_stm_id
AND orig_stm.khr_id = new_stm.khr_id
AND NVL(orig_stm.kle_id,-1) = NVL(new_stm.kle_id,-1)
AND orig_stm.sty_id = new_stm.sty_id
AND NVL(orig_stm.purpose_code,'ORIG') = NVL(new_stm.purpose_code,'ORIG')
AND orig_stm.say_code = 'CURR';
SELECT tas.id
FROM OKL_TXL_ASSETS_B txl, OKL_TRX_ASSETS tas
WHERE txl.tal_type= 'ALI'
AND txl.dnz_khr_id = p_khr_id
AND txl.tas_id = tas.id
AND tas.tas_type = 'ALI'
AND tas.tsu_code = 'ENTERED';
l_new_strm_tbl.DELETE;
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_new_strm_tbl,
x_stmv_tbl => x_new_strm_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of New Strms - Trx ID and Hist ID '||x_return_status);
SELECT new_stm.id new_stm_id,
orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id
FROM okl_streams new_stm,
okl_strm_type_b sty,
okl_streams orig_stm,
okl_party_payment_hdr pph
WHERE new_stm.khr_id = p_khr_id
AND new_stm.say_code = 'CURR'
AND new_stm.sty_id = sty.id
AND sty.billable_yn = 'Y'
AND orig_stm.id = new_stm.link_hist_stream_id
AND pph.dnz_chr_id = new_stm.khr_id
AND pph.cle_id = new_stm.kle_id
AND pph.payout_basis = 'DUE_DATE'
AND pph.passthru_term = 'BASE';
SELECT MAX(sel.stream_element_date) stream_element_date
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_disbursed IS NOT NULL;
SELECT NVL(SUM(sel.amount),0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_disbursed IS NOT NULL;
SELECT sel.id sel_id,
NVL(sel.amount,0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.stream_element_date <= p_max_disb_date;
SELECT orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
cle.lse_id
FROM okl_streams orig_stm,
okl_strm_type_b sty,
okc_k_lines_b cle,
okl_party_payment_hdr pph
WHERE orig_stm.khr_id = p_orig_khr_id
AND orig_stm.say_code = 'CURR'
AND orig_stm.sty_id = sty.id
AND orig_stm.sgn_code NOT IN ('INTC','LATE_CALC') -- Bug6472228
AND sty.billable_yn = 'Y'
AND cle.id = orig_stm.kle_id
AND cle.dnz_chr_id = orig_stm.khr_id
AND pph.dnz_chr_id = orig_stm.khr_id
AND pph.cle_id = orig_stm.kle_id
AND pph.payout_basis = 'DUE_DATE'
AND pph.passthru_term = 'BASE'
AND NOT EXISTS (SELECT 1
FROM okl_streams new_stm
WHERE new_stm.khr_id = p_rbk_khr_id
AND new_stm.say_code = 'CURR'
AND new_stm.link_hist_stream_id = orig_stm.id);
l_rebook_adj_tbl.DELETE;
l_selv_tbl.DELETE;
okl_streams_pub.update_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_tbl => l_selv_tbl,
x_selv_tbl => x_selv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Stream Elements - Date Disbursed '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Deleted Orig Stream Id: '||del_billable_streams_rec.orig_stm_id);
SELECT new_stm.id new_stm_id,
orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id
FROM okl_streams new_stm,
okl_strm_type_b sty,
okl_streams orig_stm
WHERE new_stm.khr_id = p_khr_id
AND new_stm.say_code = 'CURR'
AND new_stm.sty_id = sty.id
AND sty.billable_yn = 'Y'
AND orig_stm.id = new_stm.link_hist_stream_id;
SELECT MAX(sel.stream_element_date) stream_element_date
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NOT NULL;
SELECT NVL(SUM(sel.amount),0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NOT NULL;
SELECT sel.id sel_id,
NVL(sel.amount,0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.stream_element_date <= p_max_bill_date;
SELECT orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
cle.lse_id
FROM okl_streams orig_stm,
okl_strm_type_b sty,
okc_k_lines_b cle
WHERE orig_stm.khr_id = p_orig_khr_id
AND orig_stm.say_code = 'CURR'
AND orig_stm.sty_id = sty.id
AND orig_stm.sgn_code NOT IN ('INTC','LATE_CALC') -- Bug 6472228
-- gboomina Bug 6129455 - Start
AND sty.STREAM_TYPE_PURPOSE <>'ACTUAL_PROPERTY_TAX'
-- gboomina Bug 6129455 - End
AND sty.billable_yn = 'Y'
AND cle.id(+) = orig_stm.kle_id
AND cle.dnz_chr_id(+) = orig_stm.khr_id
AND NOT EXISTS (SELECT 1
FROM okl_streams new_stm
WHERE new_stm.khr_id = p_rbk_khr_id
AND new_stm.say_code = 'CURR'
AND new_stm.link_hist_stream_id = orig_stm.id);
l_rebook_adj_tbl.DELETE;
l_selv_tbl.DELETE;
okl_streams_pub.update_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_tbl => l_selv_tbl,
x_selv_tbl => x_selv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Stream Elements - Date Billed and Bill Adj Flag '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Deleted Orig Stream Id: '||del_billable_streams_rec.orig_stm_id);
SELECT new_stm.id new_stm_id,
orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
styt.name
FROM okl_streams new_stm,
okl_prod_strm_types psty,
okl_k_headers khr,
okl_streams orig_stm,
okl_strm_type_tl styt
WHERE khr.id = p_khr_id
AND psty.pdt_id = khr.pdt_id
AND psty.accrual_yn = 'Y'
AND psty.sty_id = new_stm.sty_id
AND new_stm.khr_id = khr.id
AND new_stm.say_code = 'CURR'
AND orig_stm.id = new_stm.link_hist_stream_id
AND styt.id = orig_stm.sty_id
AND styt.language = USERENV('LANG');
SELECT MAX(sel.stream_element_date) stream_element_date
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.accrued_yn IS NOT NULL;
SELECT NVL(SUM(sel.amount),0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.accrued_yn IS NOT NULL;
SELECT sel.id sel_id,
NVL(sel.amount,0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.stream_element_date <= p_max_accrual_date;
SELECT orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
styt.name,
cle.lse_id
FROM okl_streams orig_stm,
okl_prod_strm_types psty,
okl_k_headers khr,
okc_k_lines_b cle,
okl_strm_type_tl styt
WHERE khr.id = p_orig_khr_id
AND psty.pdt_id = khr.pdt_id
AND psty.accrual_yn = 'Y'
AND psty.sty_id = orig_stm.sty_id
AND orig_stm.khr_id = khr.id
AND orig_stm.say_code = 'CURR'
AND cle.id(+) = orig_stm.kle_id
AND cle.dnz_chr_id(+) = orig_stm.khr_id
AND NOT EXISTS (SELECT 1
FROM okl_streams new_stm
WHERE new_stm.khr_id = p_rbk_khr_id
AND new_stm.say_code = 'CURR'
AND new_stm.link_hist_stream_id = orig_stm.id)
AND styt.id = orig_stm.sty_id
AND styt.language = USERENV('LANG');
l_stream_tbl.DELETE;
l_selv_tbl.DELETE;
okl_streams_pub.update_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_tbl => l_selv_tbl,
x_selv_tbl => x_selv_tbl
);
debug_message('After Update of Stream Elements - Accrued Yn and Accrual Adj Flag '||x_return_status);
debug_message('Deleted Orig Stream Id: '||del_accrual_streams_rec.orig_stm_id);
SELECT new_stm.id new_stm_id,
orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
sty.name
--FROM okl_streams new_stm,
FROM okl_streams_rep_v new_stm, -- MGAAP 7263041
okl_prod_strm_types psty,
okl_k_headers khr,
--okl_streams orig_stm,
okl_streams_rep_v orig_stm, -- MGAAP 7263041
okl_strm_type_v sty
WHERE khr.id = p_khr_id
--AND psty.pdt_id = khr.pdt_id
AND psty.pdt_id = DECODE(OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY,
'PRIMARY', khr.pdt_id, p_reporting_pdt_id) -- MGAAP 7263041
AND psty.accrual_yn = 'Y'
AND psty.sty_id = new_stm.sty_id
AND new_stm.khr_id = khr.id
AND new_stm.say_code = 'CURR'
AND orig_stm.id = new_stm.link_hist_stream_id
AND sty.id = orig_stm.sty_id
-- Bug# 4775555: Exclude Investor accrual streams
AND sty.stream_type_purpose NOT IN
('INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_PRETAX_INCOME',
'INVESTOR_INTEREST_INCOME',
'INVESTOR_VARIABLE_INTEREST');
SELECT MAX(sel.stream_element_date) stream_element_date
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.accrued_yn IS NOT NULL;
SELECT NVL(SUM(sel.amount),0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.accrued_yn IS NOT NULL;
SELECT sel.id sel_id,
NVL(sel.amount,0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.stream_element_date <= p_max_accrual_date;
SELECT orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
sty.name,
cle.lse_id
--FROM okl_streams orig_stm,
FROM okl_streams_rep_v orig_stm, -- MGAAP 7263041
okl_prod_strm_types psty,
okl_k_headers khr,
okc_k_lines_b cle,
okl_strm_type_v sty
WHERE khr.id = p_orig_khr_id
--AND psty.pdt_id = khr.pdt_id
AND psty.pdt_id = DECODE(OKL_STREAMS_SEC_PVT.GET_STREAMS_POLICY,
'PRIMARY', khr.pdt_id, p_reporting_pdt_id) -- MGAAP 7263041
AND psty.accrual_yn = 'Y'
AND psty.sty_id = orig_stm.sty_id
AND orig_stm.khr_id = khr.id
AND orig_stm.say_code = 'CURR'
AND orig_stm.sgn_code NOT IN ('INTC','LATE_CALC') -- Bug6472228
AND cle.id(+) = orig_stm.kle_id
AND cle.dnz_chr_id(+) = orig_stm.khr_id
AND NOT EXISTS (SELECT 1
--FROM okl_streams new_stm
FROM okl_streams_rep_v new_stm -- MGAAP 7263041
WHERE new_stm.khr_id = p_rbk_khr_id
AND new_stm.say_code = 'CURR'
AND new_stm.link_hist_stream_id = orig_stm.id)
AND sty.id = orig_stm.sty_id
-- Bug# 4775555: Exclude Investor accrual streams
AND sty.stream_type_purpose NOT IN
('INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_PRETAX_INCOME',
'INVESTOR_INTEREST_INCOME',
'INVESTOR_VARIABLE_INTEREST');
SELECT b.reporting_pdt_id, a.multi_gaap_yn
FROM okl_k_headers a,
okl_products b
WHERE a.ID = p_chr_id
AND a.PDT_ID = b.ID;
l_stream_tbl.DELETE;
l_selv_tbl.DELETE;
okl_streams_pub.update_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_tbl => l_selv_tbl,
x_selv_tbl => x_selv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Stream Elements - Accrued Yn and Accrual Adj Flag '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Deleted Orig Stream Id: '||del_accrual_streams_rec.orig_stm_id);
SELECT id cle_id
FROM okc_k_lines_b cle
WHERE cle.chr_id = p_khr_id
AND cle.dnz_chr_id = p_khr_id
AND cle.lse_id = 33;
SELECT pol.khr_id agreement_id,
new_stm.khr_id khr_id,
new_stm.kle_id kle_id,
new_stm.sty_id sty_id,
new_stm.id new_stm_id,
orig_stm.id orig_stm_id
FROM okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_b sty,
okl_streams new_stm,
okl_streams orig_stm
WHERE pcn.sty_id = sty.id
AND pcn.pol_id = pol.id
AND new_stm.kle_id = pcn.kle_id
AND new_stm.khr_id = pcn.khr_id
AND new_stm.sty_id = pcn.sty_id
AND new_stm.say_code = 'CURR'
AND new_stm.active_yn = 'Y'
AND pol.status_code = 'ACTIVE'
AND pcn.status_code = 'ACTIVE'
AND new_stm.khr_id = p_khr_id
AND new_stm.kle_id = p_kle_id
AND sty.billable_yn = 'Y'
AND orig_stm.id = new_stm.link_hist_stream_id;
SELECT NVL(SUM(inv_sel.amount),0) amount
FROM okl_strm_elements inv_sel,
okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NOT NULL
AND inv_sel.sel_id = sel.id;
SELECT inv_sel.id sel_id,
NVL(inv_sel.amount,0) amount
FROM okl_strm_elements inv_sel,
okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NOT NULL
AND inv_sel.sel_id = sel.id;
SELECT OKL_SIF_SEQ.NEXTVAL
FROM dual;
SELECT stm.id stm_id
FROM okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND stm.kle_id = p_kle_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.stream_type_purpose = 'INVESTOR_DISB_ADJUSTMENT';
SELECT NVL(SUM(sel.AMOUNT),0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NULL;
SELECT 'Y'
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NULL;
l_disb_adj_tbl.DELETE;
l_selv_tbl.DELETE;
okl_streams_pub.update_stream_elements(
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_selv_tbl => l_selv_tbl,
x_selv_tbl => x_selv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Stream Elements - Date Billed and Bill Adj Flag '||x_return_status);
l_selv_tbl_new_disb.DELETE;
Okl_Streams_Pub.update_streams(
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_stmv_rec => l_stmv_rec_old_disb,
x_stmv_rec => lx_stmv_rec_old_disb);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After calling OKL_STREAMS_PUB.update_streams'||x_return_status);
Okl_Streams_Pub.update_streams(
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_stmv_rec => l_stmv_rec_old_disb,
x_stmv_rec => lx_stmv_rec_old_disb);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After calling OKL_STREAMS_PUB.update_streams'||x_return_status);
SELECT '!', ktrx.id
FROM okc_k_headers_b chr,
okl_trx_contracts ktrx,
okc_k_headers_b cpy_chr
WHERE ktrx.khr_id = chr.id
AND ktrx.khr_id_new = cpy_chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND chr.id = p_chr_id
AND cpy_chr.orig_system_id1 = chr.id
AND cpy_chr.orig_system_source_code = 'OKL_REBOOK'
AND ktrx.representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT '!', ktrx.id
FROM okc_k_headers_b chr,
okl_trx_contracts ktrx
WHERE chr.id = p_chr_id
AND ktrx.khr_id = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.representation_type = 'PRIMARY' -- MGAAP 7263041
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.khr_id = chr.id
AND rbk_khr.status <> 'PROCESSED');
SELECT new_stm.id new_stm_id,
new_stm.kle_id
FROM okl_streams new_stm,
okl_strm_type_b sty
WHERE new_stm.khr_id = p_khr_id
AND new_stm.say_code = 'WORK'
AND new_stm.sty_id = sty.id
AND sty.stream_type_purpose IN
('INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_PRETAX_INCOME',
'INVESTOR_INTEREST_INCOME',
'INVESTOR_VARIABLE_INTEREST');
SELECT orig_stm.id orig_stm_id
FROM okl_streams new_stm,
okl_streams orig_stm
WHERE new_stm.id = p_new_stm_id
AND orig_stm.khr_id = new_stm.khr_id
AND NVL(orig_stm.kle_id,-1) = NVL(new_stm.kle_id,-1)
AND orig_stm.sty_id = new_stm.sty_id
AND NVL(orig_stm.purpose_code,'ORIG') = NVL(new_stm.purpose_code,'ORIG')
AND orig_stm.say_code = 'CURR';
SELECT tas.id
FROM OKL_TXL_ASSETS_B txl, OKL_TRX_ASSETS tas
WHERE txl.tal_type= 'ALI'
AND txl.dnz_khr_id = p_khr_id
AND txl.tas_id = tas.id
AND tas.tas_type = 'ALI'
AND tas.tsu_code = 'ENTERED';
l_new_strm_tbl.DELETE;
okl_streams_pub.update_streams(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_new_strm_tbl,
x_stmv_tbl => x_new_strm_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of New Strms - Trx ID and Hist ID '||x_return_status);
SELECT new_stm.id new_stm_id,
orig_stm.id orig_stm_id,
orig_stm.sty_id,
orig_stm.kle_id,
sty.name
--FROM okl_streams new_stm,
FROM okl_streams_rep_v new_stm, -- MGAAP 7263041
okl_prod_strm_types psty,
okl_k_headers khr,
--okl_streams orig_stm,
okl_streams_rep_v orig_stm, -- MGAAP 7263041
okl_strm_type_v sty
WHERE khr.id = p_khr_id
--AND psty.pdt_id = khr.pdt_id
AND psty.pdt_id = NVL(p_product_id, khr.pdt_id) -- MGAAP 7263041
AND psty.accrual_yn = 'Y'
AND psty.sty_id = new_stm.sty_id
AND new_stm.khr_id = khr.id
AND new_stm.say_code = 'CURR'
AND orig_stm.id = new_stm.link_hist_stream_id
AND sty.id = orig_stm.sty_id
AND sty.stream_type_purpose IN
('INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_PRETAX_INCOME',
'INVESTOR_INTEREST_INCOME',
'INVESTOR_VARIABLE_INTEREST');
SELECT MAX(sel.stream_element_date) stream_element_date
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.accrued_yn IS NOT NULL;
SELECT NVL(SUM(sel.amount),0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.accrued_yn IS NOT NULL;
SELECT sel.id sel_id,
NVL(sel.amount,0) amount
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.stream_element_date <= p_max_accrual_date;
l_stream_tbl.DELETE;
l_selv_tbl.DELETE;
okl_streams_pub.update_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_tbl => l_selv_tbl,
x_selv_tbl => x_selv_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update of Stream Elements - Accrued Yn and Accrual Adj Flag '||x_return_status);