The following lines contain the word 'select', 'insert', 'update' or 'delete':
Okl_Gts_Pvt.insert_row(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_gtsv_rec => l_gtsv_rec_in
,x_gtsv_rec => l_gtsv_rec_out
);
Okl_Gtt_Pvt.insert_row(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_gttv_rec => l_gttv_rec_in
,x_gttv_rec => l_gttv_rec_out
);
Okl_Gtp_Pvt.insert_row(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_gtpv_tbl => l_gtpv_tbl_in
,x_gtpv_tbl => l_gtpv_tbl_out
);
Okl_Gtl_Pvt.insert_row(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_gtlv_tbl => l_gtlv_tbl_in
,x_gtlv_tbl => l_gtlv_tbl_out
);
PROCEDURE insert_template_lines(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_gtlv_tbl IN gtlv_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(40) := 'create_strm_gen_template';
Okl_Gtl_Pvt.insert_row(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_gtlv_tbl => l_gtlv_tbl_in
,x_gtlv_tbl => l_gtlv_tbl_out);
END insert_template_lines;
SELECT
DISTINCT
kle.fee_type
FROM
okc_k_items cim,
okl_k_lines kle,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE
cim.cle_id = cleb.id AND
cleb.lse_id = lseb.id AND
lseb.lty_code = 'FEE' AND
cleb.chr_id = chrb.id AND
kle.id = cleb.id AND
chrb.scs_code IN ('LEASE','QUOTE') AND
kle.fee_type = 'PASSTHROUGH' AND
chrb.id = khr.id AND
khr.pdt_id = pdt.id AND
cim.object1_id1 = l_sty_id AND
pdt.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE') AND
pdt.TAX_OWNER IN ('LESSOR', 'LESSEE');
SELECT
DISTINCT
kle.fee_type
FROM
okc_k_items cim,
okl_k_lines kle,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE
cim.cle_id = cleb.id AND
cleb.lse_id = lseb.id AND
lseb.lty_code = 'FEE' AND
cleb.chr_id = chrb.id AND
kle.id = cleb.id AND
chrb.scs_code IN ('LEASE','QUOTE') AND
kle.fee_type <> 'PASSTHROUGH' AND
chrb.id = khr.id AND
khr.pdt_id = pdt.id AND
cim.object1_id1 = l_sty_id AND
pdt.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE') AND
pdt.TAX_OWNER IN ('LESSOR', 'LESSEE');
SELECT rul.object1_id1
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASTRM'
AND rgp.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LAPSTH'
AND rul.dnz_chr_id = chrb.id
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'SOLD_SERVICE'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.TAX_OWNER IN ('LESSOR', 'LESSEE');
SELECT stream_type_class
FROM okl_strm_type_v
WHERE stream_type_class = 'SUBSIDY'
AND id = l_sty_id;
SELECT '1'
FROM okl_sgn_translations sgn
WHERE sgn.jtot_object1_code = 'OKL_STRMTYP' AND
sgn.value = TO_CHAR(l_sty_id);
SELECT rul.object1_id1 --strm_type_id,
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'SOLD_SERVICE'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
UNION ALL
--Link Service Payments
SELECT rul.object1_id1 --strm_type_id,
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'LINK_SERV_ASSET'
AND cleb.dnz_chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.tax_owner IN ('LESSOR', 'LESSEE');
SELECT rul.object1_id1 --strm_type_id,
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FEE'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
UNION ALL
--Link Fee Payments
SELECT rul.object1_id1 --strm_type_id,
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'LINK_FEE_ASSET'
AND cleb.dnz_chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
UNION ALL
--Contract level payments
SELECT rul.object1_id1 --strm_type_id,
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.chr_id = chrb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.tax_owner IN ('LESSOR', 'LESSEE')
UNION ALL
--Asset level payments not RENT
SELECT rul.object1_id1 --strm_type_id,
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FREE_FORM1'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 NOT IN (SELECT id FROM
okl_strm_type_b WHERE code = 'RENT')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = l_sty_id
AND pdt.deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST', 'LOAN', 'LOAN-REVOLVING', 'SALE')
AND pdt.tax_owner IN ('LESSOR', 'LESSEE');
SELECT
id,
name,
code,
stream_type_subclass,
stream_type_purpose,
start_date,
stream_type_class
FROM
okl_strm_type_v
WHERE
short_description IS NULL;
UPDATE OKL_STRM_TYPE_B
SET STREAM_TYPE_PURPOSE =l_new_sty_purpose,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = Fnd_Global.login_id
WHERE ID = strm_type_rec.id;
UPDATE OKL_STRM_TYPE_TL
SET SHORT_DESCRIPTION ='UPGRADED SUCCESSFULLY',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = Fnd_Global.login_id
WHERE ID = strm_type_rec.id
AND LANGUAGE = USERENV('LANG');
SELECT
'Y'
FROM
okl_st_gen_tmpt_lns gtl,
okl_st_gen_templates gtt,
okl_st_gen_tmpt_sets gts
WHERE
gtl.primary_sty_id = p_sty_id AND
gtl.gtt_id = gtt.id AND
gtt.gts_id = gts.id AND
gts.name = p_book_class || '-' || p_tax_owner;
SELECT
'Y'
FROM
okc_k_items cim,
okl_k_lines kle,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE
cim.cle_id = cleb.id AND
cleb.lse_id = lseb.id AND
lseb.lty_code = 'FEE' AND
cleb.chr_id = chrb.id AND
kle.id = cleb.id AND
chrb.scs_code IN ('LEASE','QUOTE') AND
kle.fee_type = 'PASSTHROUGH' AND
chrb.id = khr.id AND
khr.pdt_id = pdt.id AND
cim.object1_id1 = p_sty_id AND
pdt.DEAL_TYPE = p_book_class AND
pdt.TAX_OWNER = p_tax_owner;
SELECT
'Y'
FROM
okc_k_items cim,
okl_k_lines kle,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE
cim.cle_id = cleb.id AND
cleb.lse_id = lseb.id AND
lseb.lty_code = 'FEE' AND
cleb.chr_id = chrb.id AND
kle.id = cleb.id AND
chrb.scs_code IN ('LEASE','QUOTE') AND
kle.fee_type <> 'PASSTHROUGH' AND
chrb.id = khr.id AND
khr.pdt_id = pdt.id AND
cim.object1_id1 = p_sty_id AND
pdt.DEAL_TYPE = p_book_class AND
pdt.TAX_OWNER = p_tax_owner;
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASTRM'
AND rgp.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LAPSTH'
AND rul.dnz_chr_id = chrb.id
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'SOLD_SERVICE'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND rul.object1_id1 = p_sty_id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner;
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'SOLD_SERVICE'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 = p_sty_id
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner
UNION ALL
--Link Service Payments
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'LINK_SERV_ASSET'
AND cleb.dnz_chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 = p_sty_id
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner;
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FEE'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 = p_sty_id
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner
UNION ALL
--Link Fee Payments
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'LINK_FEE_ASSET'
AND cleb.dnz_chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 = p_sty_id
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner
UNION ALL
--Contract level payments
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.chr_id = chrb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 = p_sty_id
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner
UNION ALL
--Asset level payments not RENT
SELECT 'Y'
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okl_k_headers khr,
okl_product_parameters_v pdt
WHERE rgp.cle_id = cleb.id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LASLH'
AND rgp.dnz_chr_id = chrb.id
AND rul.dnz_chr_id = chrb.id
AND rgp.rgd_code = 'LALEVL'
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FREE_FORM1'
AND cleb.chr_id = chrb.id
AND chrb.scs_code IN ('LEASE','QUOTE')
AND rul.object1_id1 NOT IN (SELECT id FROM
okl_strm_type_b WHERE code = 'RENT')
AND rul.object1_id1 = p_sty_id
AND chrb.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.DEAL_TYPE = p_book_class
AND pdt.TAX_OWNER = p_tax_owner;
SELECT 'Y'
FROM okl_subsidies_v sub
WHERE sub.stream_type_id = p_sty_id;
SELECT id, code, stream_type_purpose
FROM okl_strm_type_b
WHERE id = p_sty_id;
SELECT ID, CODE , stream_type_purpose
FROM OKL_STRM_TYPE_V
WHERE CODE IN
('ADJUSTED PROPERTY TAX');
SELECT ID, CODE , stream_type_purpose
FROM OKL_STRM_TYPE_V
WHERE CODE IN
('RENEWAL PROPERTY TAX');
SELECT ID, CODE , stream_type_purpose
FROM OKL_STRM_TYPE_V
WHERE CODE IN
('ACCRUED FEE EXPENSE',
'AMORTIZED EXPENSE');
SELECT ID, CODE , stream_type_purpose
FROM OKL_STRM_TYPE_V
WHERE CODE IN
(
'FEE INCOME',
'AMORTIZED FEE INCOME',
'FEE RENEWAL',
'PASS THROUGH EXPENSE ACCRUAL',
'PASS THROUGH EVERGREEN FEE',
'LOAN PAYMENT',
'PRINCIPAL PAYMENT',
'INTEREST PAYMENT',
'PRINCIPAL BALANCE',
'INTEREST INCOME',
'PASS THROUGH REVENUE ACCRUAL');
SELECT ID, CODE , stream_type_purpose
FROM OKL_STRM_TYPE_V
WHERE CODE IN
('PASS THROUGH SERVICE EXPENSE ACCRUAL');
SELECT ID, CODE , stream_type_purpose
FROM OKL_STRM_TYPE_V
WHERE CODE IN
('PASS THROUGH EVERGREEN SERVICE',
'PASS THROUGH SERVICE REVENUE ACCRUAL',
'SERVICE INCOME',
'SERVICE AND MAINTENANCE EVERGREEN');
SELECT STY.ID, STY.CODE , STY.stream_type_purpose
FROM OKL_STRM_TYPE_V STY, okl_sgn_translations sgn
WHERE sgn.object1_id1 = TO_CHAR(p_sty_id)
AND sgn.value = TO_CHAR(sty.id);
SELECT id
FROM okl_strm_type_b
WHERE code = p_sty_code
AND stream_type_purpose = p_sty_purpose;
SELECT id, code, stream_type_purpose FROM okl_strm_type_v
WHERE id NOT IN
(SELECT primary_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
AND code IN (
'ASSET REPAIR CHARGE',
'BILLING ADJUSTMENT',
'BOOK DEPRECIATION',
'CURE',
'DOCUMENT REQUEST FEE - AMORTIZATION SCHEDULE',
'DOCUMENT REQUEST FEE - AUDIT LETTER',
'EQUIPMENT EXCHANGE REQUEST FEE',
'INTEREST RATE CONVERSION FEE',
'DOCUMENTS REQUEST FEE - INVOICE ON DEMAND',
'DOCUMENTS REQUEST FEE - INVOICE REPRINT',
'PAYMENT SETUP CHANGE FEES',
'RESTRUCTURE REQUEST FEE',
'TERMINATION REQUEST FEE',
'TRANSFER FEE',
'DOCUMENT REQUEST FEE - VARIABLE RATE STATEMENT',
'DOCUMENT REQUEST FEE - VAT SCHEDULE',
'SERVICE FEE - DOCUMENT REQUEST',
'SERVICE FEE',
'FEDERAL DEPRECIATION',
'FUNDING',
'INSURANCE ADJUSTMENT',
'INSURANCE ACCRUAL ADJUSTMENT',
'INSURANCE EXPENSE',
'INSURANCE INCOME',
'INSURANCE PAYABLE',
'INSURANCE RECEIVABLE',
'INSURANCE REFUND',
'INTEREST RATE CONVERSION FEE',
'INVESTOR PRE-TAX INCOME',
'INVESTOR RENTAL ACCRUAL',
'LATE FEE',
'LATE INTEREST',
'PRE-FUNDING',
'RENT',
'RESIDUAL VALUE',
'STATE DEPRECIATION',
'INTERIM INTEREST')
AND created_by = 1;
SELECT id, code, stream_type_purpose FROM okl_strm_type_v
WHERE id NOT IN
(SELECT primary_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
AND code IN (
'BILLING ADJUSTMENT',
'CURE',
'FUNDING',
'INSURANCE ADJUSTMENT',
'INSURANCE ACCRUAL ADJUSTMENT',
'INSURANCE EXPENSE',
'INSURANCE INCOME',
'INSURANCE PAYABLE',
'INSURANCE RECEIVABLE',
'INSURANCE REFUND',
'LATE FEE',
'LATE INTEREST',
'PRE-FUNDING',
'VARIABLE INTEREST SCHEDULE',
'ASSET REPAIR CHARGE',
'DOCUMENT REQUEST FEE - AMORTIZATION SCHEDULE',
'DOCUMENT REQUEST FEE - AUDIT LETTER',
'EQUIPMENT EXCHANGE REQUEST FEE',
'INTEREST RATE CONVERSION FEE',
'DOCUMENTS REQUEST FEE - INVOICE ON DEMAND',
'DOCUMENTS REQUEST FEE - INVOICE REPRINT',
'PAYMENT SETUP CHANGE FEES',
'RESTRUCTURE REQUEST FEE',
'TERMINATION REQUEST FEE',
'TRANSFER FEE',
'DOCUMENT REQUEST FEE - VARIABLE RATE STATEMENT',
'DOCUMENT REQUEST FEE - VAT SCHEDULE',
'SERVICE FEE - DOCUMENT REQUEST',
'SERVICE FEE',
'RENT',
'VARIABLE INTEREST CHARGE',
'INTERIM INTEREST')
AND created_by = 1;
SELECT id, code FROM okl_strm_type_v
WHERE id NOT IN
(SELECT primary_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
AND code IN (
'INVESTOR CONTRACT OBLIGATION PAYABLE',
'INVESTOR DISBURSEMENT ADJUSTMENT',
'INVESTOR EVERGREEN RENT PAYABLE',
'INVESTOR INTEREST PAYABLE',
'INVESTOR LATE FEE PAYABLE',
'INVESTOR LATE INTEREST PAYABLE',
'INVESTOR PAYABLE',
'INVESTOR PRINCIPAL PAYABLE',
'INVESTOR RECEIVABLE',
'INVESTOR RENT BUYBACK',
'INVESTOR RENT DISBURSEMENT BASIS',
'INVESTOR RENT PAYABLE',
'INVESTOR RESIDUAL BUYBACK',
'INVESTOR RESIDUAL DISBURSEMENT BASIS',
'INVESTOR RESIDUAL PAYABLE',
'PRESENT VALUE SECURITIZED RENT',
'PRESENT VALUE SECURITIZED RESIDUAL')
AND created_by = 1;
SELECT id, code FROM okl_strm_type_v
WHERE id NOT IN
(SELECT dependent_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
AND code IN (
'RENTAL ACCRUAL',
'ADVANCED RENTALS',
'PRESENT VALUE RENT',
'EVERGREEN RENT',
'PASS THROUGH RENEWAL RENT',
'STIP LOSS VALUE',
'TERMINATION VALUE',
'PRESENT VALUE RESIDUAL',
'GUARANTEED RESIDUAL THIRD PARTY',
--'GUARANTEED RESIDUAL INSURED',
--'PV GUARANTEE',
--'PV UNGUARANTEED RESIDUAL',
'PV GUARANTEED RESIDUAL',
'RESIDUAL VALUE INSURANCE PREMIUM',
'PRESENT VALUE UNINSURED RESIDUAL',
'PRESENT VALUE INSURED RESIDUAL',
'PRESENT VALUE UNGUARANTEED RESIDUAL')
AND created_by = 1;
SELECT id, code FROM okl_strm_type_v
WHERE id NOT IN
(SELECT dependent_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
AND code IN (
'ADVANCED RENTALS',
'PRESENT VALUE RENT',
'EVERGREEN RENT',
'STIP LOSS VALUE',
'TERMINATION VALUE',
'PRE-TAX INCOME',
'PASS THROUGH RENEWAL RENT',
'PRESENT VALUE RESIDUAL',
--'GUARANTEED RESIDUAL INSURED',
--'PV GUARANTEE',
--'PV UNGUARANTEED RESIDUAL',
'GUARANTEED RESIDUAL THIRD PARTY',
'PV GUARANTEED RESIDUAL',
'RESIDUAL VALUE INSURANCE PREMIUM',
'PRESENT VALUE INSURED RESIDUAL',
'PRESENT VALUE UNINSURED RESIDUAL',
'PRESENT VALUE UNGUARANTEED RESIDUAL')
AND created_by = 1;
SELECT id, code FROM okl_strm_type_v
WHERE id NOT IN
(SELECT dependent_sty_id FROM okl_st_gen_tmpt_lns WHERE gtt_id = l_gtt_id)
AND code IN (
'VARIABLE INCOME NON-ACCRUAL',
'VARIABLE INCOME ACCRUAL',
'ADVANCED RENTALS',
'PRINCIPAL CATCH UP',
'INTEREST INCOME',
'INTEREST PAYMENT',
'LOAN PAYMENT',
'UNSCHEDULED PRINCIPAL PAYMENT',
'PRINCIPAL BALANCE',
'PRINCIPAL PAYMENT')
AND created_by = 1;
SELECT
id,
name,
code,
stream_type_subclass,
stream_type_purpose,
start_date,
stream_type_class
FROM
okl_strm_type_v;
l_dep_sty_tbl.DELETE;
SELECT
DISTINCT
q1.value deal_type,
q2.value tax_owner
FROM
okl_products_v p,
okl_pdt_pqy_vals_uv q1,
okl_pdt_pqy_vals_uv q2
WHERE
p.id = q1.pdt_id AND q1.name = 'LEASE' AND q1.value IS NOT NULL AND
p.id = q2.pdt_id AND q2.name = 'TAXOWNER' AND q2.value IS NOT NULL
UNION
SELECT
DISTINCT
q1.value deal_type,
'LESSEE' tax_owner
FROM
okl_products_v p,
okl_pdt_pqy_vals_uv q1
WHERE
p.id = q1.pdt_id AND q1.name = 'INVESTOR' AND q1.value IS NOT NULL;
SELECT
gts.id gts_id,
gts.name,
gtt.id gtt_id,
gtt.tmpt_status
FROM
OKL_ST_GEN_TMPT_SETS gts,
OKL_ST_GEN_TEMPLATES gtt
WHERE
gts.id = gtt.gts_id AND
gts.name = l_name;
SELECT
id,
name,
code,
stream_type_subclass,
stream_type_purpose,
start_date,
stream_type_class
FROM
okl_strm_type_v;
SELECT '1'
FROM OKL_STRM_TYPE_V STY
WHERE STY.SHORT_DESCRIPTION <> 'UPGRADED SUCCESSFULLY';
SELECT
DISTINCT aes.org_id
FROM
okl_products_v p,
okl_ae_tmpt_sets aes,
okl_pdt_pqy_vals_uv q1,
okl_pdt_pqy_vals_uv q2
WHERE
p.aes_id = aes.id AND
p.id = q1.pdt_id AND q1.name = 'LEASE' AND q1.value = l_deal_type AND
p.id = q2.pdt_id AND q2.name = 'TAXOWNER' AND q2.value = l_tax_owner
UNION
SELECT
DISTINCT aes.org_id
FROM
okl_products_v p,
okl_pdt_pqy_vals_uv q1,
okl_ae_tmpt_sets aes
WHERE
p.aes_id = aes.id AND
p.id = q1.pdt_id AND q1.name = 'INVESTOR' AND q1.value = l_deal_type;
SELECT name
FROM hr_operating_units
WHERE organization_id = l_org_id;
SELECT MIN(aes.start_date)
FROM okl_ae_tmpt_sets aes;
l_gtlv_tbl.DELETE;
insert_template_lines(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gtlv_tbl => l_gtlv_tbl);
l_gtlv_tbl.DELETE;
insert_template_lines(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gtlv_tbl => l_gtlv_tbl);
SELECT DISTINCT org_id
FROM okl_ae_tmpt_sets;
SELECT
p.id product_id,
p.name product_name,
p.aes_id aes_id,
p.ptl_id,
q1.value deal_type,
q2.value tax_owner,
aes.name aes_name
FROM
okl_products_v p,
okl_ae_tmpt_sets_v aes,
okl_pdt_pqy_vals_uv q1,
okl_pdt_pqy_vals_uv q2
WHERE
p.aes_id = aes.id AND
aes.gts_id IS NULL AND
p.id = q1.pdt_id AND q1.name = 'LEASE' AND q1.value IS NOT NULL AND
p.id = q2.pdt_id AND q2.name = 'TAXOWNER' AND q2.value IS NOT NULL
UNION
SELECT
p.id product_id,
p.name product_name,
p.aes_id aes_id,
p.ptl_id,
q1.value deal_type,
'LESSEE' tax_owner,
aes.name aes_name
FROM
okl_products_v p,
okl_ae_tmpt_sets_v aes,
okl_pdt_pqy_vals_uv q1
WHERE
p.aes_id = aes.id AND
aes.gts_id IS NULL AND
p.id = q1.pdt_id AND q1.name = 'INVESTOR' AND q1.value IS NOT NULL
ORDER BY aes_id, deal_type, tax_owner,product_id;
SELECT gts.id, gts.name
FROM OKL_ST_GEN_TMPT_SETS gts, OKL_ST_GEN_TEMPLATES gtt
WHERE gts.id = gtt.gts_id AND
gts.deal_type = p_deal_type
AND gts.tax_owner = p_tax_owner AND
gts.name = l_name AND
gtt.tmpt_status = 'ACTIVE';
SELECT
id,
object_version_number,
name,
description,
version,
start_date,
end_date,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
gts_id
FROM okl_ae_tmpt_sets_v
WHERE okl_ae_tmpt_sets_v.id = p_id;
SELECT
id,
object_version_number,
try_id,
aes_id,
sty_id,
fma_id,
set_of_books_id,
fac_code,
syt_code,
post_to_gl,
advance_arrears,
memo_yn,
prior_year_yn,
name,
description,
version,
factoring_synd_flag,
start_date,
end_date,
Accrual_Yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
inv_code
FROM OKL_AE_TEMPLATES
WHERE OKL_AE_TEMPLATES.aes_id = p_aes_id;
SELECT
id,
object_version_number,
avl_id,
crd_code,
code_combination_id,
ae_line_type,
sequence_number,
description,
percentage,
account_builder_yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM OKL_AE_TMPT_LNES
WHERE OKL_AE_TMPT_LNES.avl_id = p_avl_id;
SELECT name
FROM hr_operating_units
WHERE organization_id = l_org_id;
l_aesv_rec.last_updated_by,
l_aesv_rec.last_update_date,
l_aesv_rec.last_update_login,
l_aesv_rec.gts_id;
l_avlv_rec.last_updated_by := avlv_pk_rec.last_updated_by;
l_avlv_rec.last_update_date := avlv_pk_rec.last_update_date;
l_avlv_rec.last_update_login := avlv_pk_rec.last_update_login;
l_atlv_rec.last_updated_by := atlv_pk_rec.last_updated_by;
l_atlv_rec.last_update_date := atlv_pk_rec.last_update_date;
l_atlv_rec.last_update_login := atlv_pk_rec.last_update_login;
Okl_Products_Pub.update_products(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdtv_rec => l_pdtv_rec,
x_pdtv_rec => x_pdtv_rec );
Okl_Setupproducts_Pvt.update_product_status(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdt_status => Okl_Setupproducts_Pvt.G_PDT_STS_APPROVED,
p_pdt_id => x_pdtv_rec.id);
l_aesv_rec.last_updated_by,
l_aesv_rec.last_update_date,
l_aesv_rec.last_update_login,
l_aesv_rec.gts_id;
Okl_Process_Tmpt_Set_Pub.update_tmpt_set(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_aesv_rec => l_aesv_rec,
x_aesv_rec => x_aesv_rec);
Okl_Products_Pub.update_products(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdtv_rec => l_pdtv_rec,
x_pdtv_rec => x_pdtv_rec);
Okl_Setupproducts_Pvt.update_product_status(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdt_status => Okl_Setupproducts_Pvt.G_PDT_STS_APPROVED,
p_pdt_id => x_pdtv_rec.id);
Okl_Setupproducts_Pvt.update_product_status(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdt_status => Okl_Setupproducts_Pvt.G_PDT_STS_INVALID,
p_pdt_id => l_product_id);