[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MEANING
FROM FND_LOOKUPS FND
WHERE FND.LOOKUP_TYPE = p_lookup_type
AND FND.LOOKUP_CODE = p_lookup_code;
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 update_strm_gen_template(
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_gtsv_rec IN gtsv_rec_type
,p_gttv_rec IN gttv_rec_type
,p_gtpv_tbl IN gtpv_tbl_type
,p_gtlv_tbl IN gtlv_tbl_type
,x_gttv_rec OUT NOCOPY gttv_rec_type -- Return the Template Info
)IS
l_api_name CONSTANT VARCHAR2(40) := 'update_strm_gen_template';
SELECT PDT.ID PDT_ID
,PRODUCT_STATUS_CODE
FROM OKL_PRODUCTS PDT,
OKL_AE_TMPT_SETS ATS, OKL_ST_GEN_TMPT_SETS SGT
WHERE PDT.AES_ID = ATS.ID
AND ATS.GTS_ID = SGT.ID
AND SGT.ID = p_gts_id;
SELECT GTT.GTS_ID
FROM OKL_ST_GEN_TEMPLATES GTT
WHERE GTT.ID = p_gtt_id;
SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
FROM OKL_ST_GEN_TEMPLATES GTT
WHERE GTT.ID = p_gtt_id;
SELECT id, gtt.gts_id, version, tmpt_status, start_date, end_date
FROM OKL_ST_GEN_TEMPLATES GTT
WHERE GTT.gts_ID = p_gts_id
AND version = p_version;
select max(chr.start_date) MAX_START_DATE
from okc_k_headers_b chr,
okl_k_headers khr,
okl_ae_tmpt_Sets aes,
okl_st_gen_templates gtt,
okl_products pdt
where pdt.id = khr.pdt_id
and pdt.aes_id = aes.id
and aes.gts_id = gtt.gts_id
and khr.id = chr.id
and chr.start_date >= gtt.start_date
and gtt.id = p_gtt_id
--srsreeni Bug5996170 start
and chr.sts_code in ('APPROVED','BOOKED','COMPLETE','EVERGREEN','BANKRUPTCY_HOLD','UNDER REVISION','LITIGATION_HOLD','TERMINATION_HOLD')
--srsreeni Bug5996170 end
;
okl_gts_pvt.update_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
);
3. If any new versions From date changes, UPDATE the prev versions end date to one less than this value
4. If any active versions "to date" changes, it can only be greater than old vlaue and UPDATE next versions "From date" to this val+1 and "To Date" to NULL
*/
--1.
IF l_gttv_rec_in.end_date IS NOT NULL AND l_gttv_rec_in.end_date <> G_MISS_DATE AND
trunc(l_gttv_rec_in.start_date) > trunc(l_gttv_rec_in.end_date)
THEN
okl_api.SET_MESSAGE( p_app_name => g_app_name,
p_msg_name => 'OKL_START_DT_LESS_END_DT'
);
okl_gtt_pvt.update_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_new_gttv_in_rec
,x_gttv_rec => l_new_gttv_out_rec
);
SELECT max(FND_NUMBER.CANONICAL_TO_NUMBER(version)) -- Bug 10369520
INTO l_max_version
FROM OKL_ST_GEN_TEMPLATES
WHERE gts_id = (SELECT gts_id
FROM OKL_ST_GEN_TEMPLATES
WHERE id = l_gttv_rec_in.id);
okl_gtt_pvt.update_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_new_gttv_in_rec
,x_gttv_rec => l_new_gttv_out_rec
);
okl_gtt_pvt.update_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_ins_in
,x_gtpv_tbl => l_gtpv_tbl_ins_out
);
okl_gtp_pvt.update_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_upd_in
,x_gtpv_tbl => l_gtpv_tbl_upd_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_ins_in
,x_gtlv_tbl => l_gtlv_tbl_ins_out
);
okl_gtl_pvt.update_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_upd_in
,x_gtlv_tbl => l_gtlv_tbl_upd_out
);
OKL_SETUPPRODUCTS_PVT.update_product_status(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_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 => pdt_rec.pdt_id );
SELECT ID
,NAME
,DESCRIPTION
,PRODUCT_TYPE
,TAX_OWNER
,DEAL_TYPE
,PRICING_ENGINE
,interest_calc_meth_code
,revenue_recog_meth_code
,days_in_month_code
,days_in_yr_code
-- Added new field by DPSINGH for ER 6274342
,isg_arrears_pay_dates_option
FROM OKL_ST_GEN_TMPT_SETS
WHERE ID = p_gts_id;
SELECT ID
,GTS_ID
,VERSION
,START_DATE
,END_DATE
,TMPT_STATUS
FROM OKL_ST_GEN_TEMPLATES
where ID = p_gtt_id;
SELECT ID
,NAME
,DESCRIPTION
,DISPLAY_YN
,UPDATE_YN
,PRC_ENG_IDENT
,DEFAULT_VALUE
,GTT_ID
FROM OKL_ST_GEN_PRC_PARAMS
where GTT_ID = p_gtt_id;
SELECT ID
,GTT_ID
,PRIMARY_YN
,PRIMARY_STY_ID
,DEPENDENT_STY_ID
,PRICING_NAME
FROM OKL_ST_GEN_TMPT_LNS
where GTT_ID = p_gtt_id
AND PRIMARY_YN = p_primary_yn;
select max(chr.start_date) MAX_START_DATE
from okc_k_headers_b chr,
okl_k_headers khr,
okl_ae_tmpt_Sets aes,
okl_st_gen_templates gtt,
okl_products pdt
where pdt.id = khr.pdt_id
and pdt.aes_id = aes.id
and aes.gts_id = gtt.gts_id
and khr.id = chr.id
and chr.start_date >= gtt.start_date
and gtt.id = p_gtt_id
--srsreeni Bug5996170 start
and chr.sts_code in ('APPROVED','BOOKED','COMPLETE','EVERGREEN','BANKRUPTCY_HOLD','UNDER REVISION','LITIGATION_HOLD','TERMINATION_HOLD')
--srsreeni Bug5996170 end
;
SELECT OKL_GTS_NAME_SEQ.NEXTVAL next_number
FROM DUAL;
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.update_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_prev_version_in
,x_gttv_rec => l_gttv_rec_prev_version_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
);
l_gtpv_tbl_in(i).update_yn := gtpv_rec_temp.update_yn ;
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_pri_in
,x_gtlv_tbl => l_gtlv_tbl_pri_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_dep_in
,x_gtlv_tbl => l_gtlv_tbl_dep_out
);
Procedure delete_tmpt_prc_params(
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_gtpv_tbl IN gtpv_tbl_type
) IS
l_gtt_id okl_st_gen_templates.id%type ;
l_api_name CONSTANT VARCHAR2(40) := 'delete_tmpt_prc_params';
OKL_GTP_PVT.delete_row(
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_gtpv_tbl => l_gtpv_tbl_del_in
);
Procedure delete_pri_tmpt_lns(
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_gtt_id okl_st_gen_templates.id%type;
l_api_name CONSTANT VARCHAR2(40) := 'delete_pri_tmpt_lns';
SELECT ID
,PRIMARY_STY_ID
,DEPENDENT_STY_ID
,GTT_ID
FROM OKL_ST_GEN_TMPT_LNS
WHERE PRIMARY_YN = 'N'
AND PRIMARY_STY_ID = p_primary_sty_id
AND GTT_ID = p_gtt_id;
Okl_Gtl_Pvt.delete_row(
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_gtlv_tbl => l_gtlv_dep_tbl_del_in);
l_gtlv_dep_tbl_del_in.DELETE;
Okl_Gtl_Pvt.delete_row(
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_gtlv_tbl => l_gtlv_pri_tbl_del_in);
Procedure delete_dep_tmpt_lns(
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_gtt_id okl_st_gen_templates.id%type;
l_api_name CONSTANT VARCHAR2(40) := 'delete_dep_tmpt_lns';
Okl_Gtl_Pvt.delete_row(
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_gtlv_tbl => l_gtlv_tbl_del_in);
SELECT interest_calc_meth_code,
revenue_recog_meth_code,
pricing_engine ,
days_in_month_code ,
days_in_yr_code
FROM OKL_ST_GEN_TMPT_SETS
WHERE ID = (
SELECT gts_id
FROM okl_st_gen_templates
WHERE id = p_gtt_id
);
SELECT stb.stream_type_purpose purpose,
stb.billable_yn
FROM OKL_ST_GEN_TMPT_LNS gtl
,okl_strm_type_b stb
WHERE
gtl.gtt_id = p_gtt_id
AND gtl.DEPENDENT_STY_ID = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'PRINCIPAL_PAYMENT'
,'INTEREST_PAYMENT'
,'LOAN_PAYMENT'
)
AND gtl.PRIMARY_STY_ID IN
(
SELECT id
FROM okl_strm_type_b
WHERE stream_type_purpose='RENT'
);
SELECT stb.stream_type_purpose purpose, stb.billable_yn
FROM OKL_ST_GEN_TMPT_LNS gtl
,okl_strm_type_b stb
WHERE
gtl.gtt_id = p_gtt_id
AND gtl.PRIMARY_STY_ID = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose ='RENT';
SELECT count(*) ins_strms_count
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND GTL.GTT_ID = p_gtt_id
AND GTL.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'INSURANCE_RECEIVABLE'
-- Modified by RGOOTY
-- Bug 4096853: Start
,'INSURANCE_ADJUSTMENT'
-- Bug 4096853: End
,'INSURANCE_PAYABLE'
,'INSURANCE_ACCRUAL_ADJUSTMENT'
,'INSURANCE_EXPENSE_ACCRUAL'
,'INSURANCE_INCOME_ACCRUAL'
,'INSURANCE_REFUND'
);
SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
FROM OKL_ST_GEN_TMPT_LNS gtl
,okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'ACTUAL_PROPERTY_TAX'
,'REBOOK_BILLING_ADJUSTMENT'
,'CURE'
,'INSURANCE_ACCRUAL_ADJUSTMENT'
,'INSURANCE_ADJUSTMENT'
,'INSURANCE_EXPENSE_ACCRUAL'
,'INSURANCE_INCOME_ACCRUAL'
,'INSURANCE_PAYABLE'
,'INSURANCE_RECEIVABLE'
,'INSURANCE_REFUND'
,'LATE_FEE'
,'LATE_INTEREST'
,'PREFUNDING_INTEREST_PAYMENT'
,'REPAIR_CHARGE'
,'USAGE_PAYMENT'
,'SERVICE_FEE_AMORT_SCHEDULE'
,'SERVICE_FEE_AUDIT_LETTER'
,'SERVICE_FEE_VAT_SCHEDULE'
,'SERVICE_FEE_VAR_RATE_STMNT'
,'SERVICE_FEE_INVOICE_REPRINT'
,'SERVICE_FEE_INVOICE_DEMAND'
,'SERVICE_FEE_REST_REQUEST'
,'SERVICE_FEE_TERM_REQUEST'
,'SERVICE_FEE_EXCHG_REQUEST'
,'SERVICE_FEE_TRANS_REQUEST'
,'SERVICE_FEE_PMT_CHANGE'
,'SERVICE_FEE_INTEREST_CONV'
,'SERVICE_FEE_GENERAL'
,'SERVICE_FEE_DOCUMENT_REQ'
,'AMBSPR'
,'AMAFEE'
,'AMBCOC'
,'AMYFEE'
,'AMCQDR'
,'AMPRTX'
,'AMEFEE'
,'AMFFEE'
,'AMGFEE'
,'AMIFEE'
,'AMCMIS'
,'AMMFEE'
,'AMPFEE'
,'AMCTOC'
,'AMBPOC'
,'AMCQFE'
,'AMCRFE'
,'AMCRIN'
,'AMYSAM'
,'AMCTPE'
,'AMCTUR'
--bug 4176696 fixed by smahapat
--,'BILL_ADJST'
,'VARIABLE_INTEREST'
,'BOOK_DEPRECIATION'
,'FEDERAL_DEPRECIATION'
,'INVESTOR_PRETAX_INCOME'
,'INVESTOR_RENTAL_ACCRUAL'
,'RESIDUAL_VALUE'
,'STATE_DEPRECIATION'
,'VARIABLE_INTEREST_SCHEDULE'
-- Modified by RGOOTY
-- Bug 4050701: Start
,'SERVICE_EXPENSE'
-- Bug 4050701: End
-- Bug 4062730: Start
,'RENT'
-- Bug 4062730: End
-- Bug 4110239: Start
,'GENERAL_LOSS_PROVISION'
-- Bug 4110239: End
,'VENDOR_RESIDUAL_SHARING'
--Bug 4616460 added new stream type purpose
,'ASSET_SALE_RECEIVABLE'
--Bug 4616460 end
-- Bug 5730462: Add a new purpose for EB Tax R12 IA
,'UPFRONT_TAX_FINANCED'
,'UPFRONT_TAX_CAPITALIZED'
-- Bug 5730462 End
, 'UPFRONT_TAX_BILLED' -- bug6619311
)
AND GTL.GTT_ID = p_gtt_id
group by stb.stream_type_purpose
having count(stb.stream_type_purpose) > 1;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'CURE'
,'FUNDING'
,'LATE_FEE'
,'LATE_INTEREST'
,'PREFUNDING_INTEREST_PAYMENT'
,'RENT'
,'REPAIR_CHARGE'
,'SERVICE_FEE_AMORT_SCHEDULE'
,'SERVICE_FEE_AUDIT_LETTER'
,'SERVICE_FEE_VAT_SCHEDULE'
,'SERVICE_FEE_VAR_RATE_STMNT'
,'SERVICE_FEE_INVOICE_REPRINT'
,'SERVICE_FEE_INVOICE_DEMAND'
,'SERVICE_FEE_REST_REQUEST'
,'SERVICE_FEE_TERM_REQUEST'
,'SERVICE_FEE_EXCHG_REQUEST'
,'SERVICE_FEE_TRANS_REQUEST'
,'SERVICE_FEE_PMT_CHANGE'
,'SERVICE_FEE_INTEREST_CONV'
,'SERVICE_FEE_GENERAL'
,'SERVICE_FEE_DOCUMENT_REQ'
,'QUOTE_PER_DIEM'
,'REBOOK_BILLING_ADJUSTMENT' -- VR Upgrade.
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'CURE'
,'FUNDING'
,'LATE_FEE'
,'LATE_INTEREST'
,'PREFUNDING_INTEREST_PAYMENT'
,'RENT'
,'REPAIR_CHARGE'
,'SERVICE_FEE_AMORT_SCHEDULE'
,'SERVICE_FEE_AUDIT_LETTER'
,'SERVICE_FEE_VAT_SCHEDULE'
,'SERVICE_FEE_VAR_RATE_STMNT'
,'SERVICE_FEE_INVOICE_REPRINT'
,'SERVICE_FEE_INVOICE_DEMAND'
,'SERVICE_FEE_REST_REQUEST'
,'SERVICE_FEE_TERM_REQUEST'
,'SERVICE_FEE_EXCHG_REQUEST'
,'SERVICE_FEE_TRANS_REQUEST'
,'SERVICE_FEE_PMT_CHANGE'
,'SERVICE_FEE_INTEREST_CONV'
,'SERVICE_FEE_GENERAL'
,'SERVICE_FEE_DOCUMENT_REQ'
,'QUOTE_PER_DIEM'
,'REBOOK_BILLING_ADJUSTMENT' -- VR Upgrade.
)
and GTL.GTT_ID = p_gtt_id;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'VARIABLE_INTEREST'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'VARIABLE_INTEREST'
)
and GTL.GTT_ID = p_gtt_id;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'RESIDUAL_VALUE'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'RESIDUAL_VALUE'
)
and GTL.GTT_ID = p_gtt_id;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE ='VARIABLE_INTEREST_SCHEDULE'
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose ='VARIABLE_INTEREST_SCHEDULE'
and GTL.GTT_ID = p_gtt_id;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'INVESTOR_RENTAL_ACCRUAL'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'INVESTOR_RENTAL_ACCRUAL'
)
and GTL.GTT_ID = p_gtt_id;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'INVESTOR_PRETAX_INCOME'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'INVESTOR_PRETAX_INCOME'
)
and GTL.GTT_ID = p_gtt_id;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'ADVANCE_RENT'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'ADVANCE_RENT'
)
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'INTEREST_INCOME'
,'INTEREST_PAYMENT'
,'LOAN_PAYMENT'
,'PRINCIPAL_BALANCE'
,'PRINCIPAL_CATCHUP'
,'PRINCIPAL_PAYMENT'
,'UNSCHEDULED_PRINCIPAL_PAYMENT'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'INTEREST_INCOME'
,'INTEREST_PAYMENT'
,'LOAN_PAYMENT'
,'PRINCIPAL_BALANCE'
,'PRINCIPAL_CATCHUP'
,'PRINCIPAL_PAYMENT'
,'UNSCHEDULED_PRINCIPAL_PAYMENT'
)
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'DAILY_INTEREST_PRINCIPAL'
,'DAILY_INTEREST_INTEREST'
,'UNSCHEDULED_LOAN_PAYMENT'
,'VARIABLE_LOAN_PAYMENT'
,'EXCESS_PRINCIPAL_PAID'
,'EXCESS_INTEREST_PAID'
,'EXCESS_LOAN_PAYMENT_PAID'
,'ACTUAL_INCOME_ACCRUAL'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b ptb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'DAILY_INTEREST_PRINCIPAL'
,'DAILY_INTEREST_INTEREST'
,'UNSCHEDULED_LOAN_PAYMENT'
,'VARIABLE_LOAN_PAYMENT'
,'EXCESS_PRINCIPAL_PAID'
,'EXCESS_INTEREST_PAID'
,'EXCESS_LOAN_PAYMENT_PAID'
,'ACTUAL_INCOME_ACCRUAL'
)
and GTL.GTT_ID = p_gtt_id
and ptb.id = gtl.primary_sty_id
and ptb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'FLOAT_FACTOR_ADJUSTMENT'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b ptb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'FLOAT_FACTOR_ADJUSTMENT'
)
and GTL.GTT_ID = p_gtt_id
and ptb.id = gtl.primary_sty_id
and ptb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'INTEREST_CATCHUP'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b ptb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'INTEREST_CATCHUP'
)
and GTL.GTT_ID = p_gtt_id
and ptb.id = gtl.primary_sty_id
and ptb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE = 'VARIABLE_INTEREST_INCOME'
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose = 'VARIABLE_INTEREST_INCOME'
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'VARIABLE_INTEREST'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'LEASE_INCOME'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'LEASE_INCOME'
)
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'PASS_THROUGH_EVERGREEN_RENT'
,'PV_RENT'
,'RENEWAL_RENT'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'PASS_THROUGH_EVERGREEN_RENT'
,'PV_RENT'
,'RENEWAL_RENT'
)
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'PV_RV'
,'PV_RV_GUARANTEED'
,'PV_RV_INSURED'
,'PV_RV_UNGUARANTEED'
,'PV_RV_UNINSURED'
,'RESIDUAL_GUARANTEED'
,'RV_INSURANCE_PREMIUM'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'PV_RV'
,'PV_RV_GUARANTEED'
,'PV_RV_INSURED'
,'PV_RV_UNGUARANTEED'
,'PV_RV_UNINSURED'
,'RESIDUAL_GUARANTEED'
,'RV_INSURANCE_PREMIUM'
)
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'RESIDUAL_VALUE'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'RENT_ACCRUAL'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
, okl_strm_type_b sstb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
AND stb.stream_type_purpose IN
(
'RENT_ACCRUAL'
)
and GTL.GTT_ID = p_gtt_id
and sstb.id = gtl.primary_sty_id
and sstb.stream_Type_purpose IN
(
'RENT'
)
AND EXISTS
(
SELECT 1
FROM OKL_ST_GEN_TMPT_LNS gtlpri
WHERE primary_yn = 'Y'
AND gtlpri.gtt_id = p_gtt_id
AND gtlpri.primary_sty_id = gtl.primary_sty_id
);
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND GTL.GTT_ID = p_gtt_id
UNION
-- Retrieve the List of Dependent Stream Purposes in the Template
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
and GTL.GTT_ID = p_gtt_id
)
MINUS
SELECT LOOKUP_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE in
(
'ACCOUNTING'
,'ACCRUED_FEE_EXPENSE'
,'ACCRUED_FEE_INCOME'
,'ACTUAL_PROPERTY_TAX'
,'ADJUSTED_PROPERTY_TAX'
,'ADVANCE_RENT'
,'AMORTIZED_FEE_EXPENSE'
,'AMORTIZE_FEE_INCOME'
,'REBOOK_BILLING_ADJUSTMENT'
,'CURE'
,'ESTIMATED_PROPERTY_TAX'
,'EXPENSE'
,'FEE_PAYMENT'
,'FUNDING'
,'INSURANCE_ACCRUAL_ADJUSTMENT'
,'INSURANCE_ADJUSTMENT'
,'INSURANCE_EXPENSE_ACCRUAL'
,'INSURANCE_INCOME_ACCRUAL'
,'INSURANCE_PAYABLE'
,'INSURANCE_RECEIVABLE'
,'INSURANCE_REFUND'
,'LATE_FEE'
,'LATE_INTEREST'
,'PASS_THRU_EXP_ACCRUAL'
,'PASS_THROUGH_FEE'
,'PASS_THRU_REV_ACCRUAL'
,'PASS_THROUGH_SERVICE'
,'PASS_THRU_SVC_EXP_ACCRUAL'
,'PASS_THRU_SVC_REV_ACCRUAL'
,'PREFUNDING_INTEREST_PAYMENT'
-- Bug 4110239: Start
--,'PROVISION'
,'SPECIFIC_LOSS_PROVISION'
,'GENERAL_LOSS_PROVISION'
-- Bug 4110239: End
,'RENT'
,'GENERAL'
,'REPAIR_CHARGE'
,'SECURITY_DEPOSIT'
,'SERVICE_EXPENSE'
,'SERVICE_INCOME'
,'SERVICE_PAYMENT'
,'SERVICE_RENEWAL'
,'SUBSIDY'
,'SUBSIDY_INCOME'
,'USAGE_PAYMENT'
,'SERVICE_FEE_AMORT_SCHEDULE'
,'SERVICE_FEE_AUDIT_LETTER'
,'SERVICE_FEE_VAT_SCHEDULE'
,'SERVICE_FEE_VAR_RATE_STMNT'
,'SERVICE_FEE_INVOICE_REPRINT'
,'SERVICE_FEE_INVOICE_DEMAND'
,'SERVICE_FEE_REST_REQUEST'
,'SERVICE_FEE_TERM_REQUEST'
,'SERVICE_FEE_EXCHG_REQUEST'
,'SERVICE_FEE_TRANS_REQUEST'
,'SERVICE_FEE_PMT_CHANGE'
,'SERVICE_FEE_INTEREST_CONV'
,'SERVICE_FEE_GENERAL'
,'SERVICE_FEE_DOCUMENT_REQ'
,'AMBSPR'
,'AMAFEE'
,'AMBCOC'
,'AMYFEE'
,'AMCQDR'
,'AMPRTX'
,'AMEFEE'
,'AMFFEE'
,'AMGFEE'
,'AMIFEE'
,'AMCMIS'
,'AMMFEE'
,'AMPFEE'
,'AMCTOC'
,'AMBPOC'
,'AMCQFE'
,'AMCRFE'
,'AMCRIN'
,'AMYSAM'
,'AMCTPE'
,'AMCTUR'
--bug 4176696 fixed by smahapat
--,'BILL_ADJST'
-- Missed out in the first
,'FINANCED_FEE_PAYMENT'
,'PREFUNDING'
-- Added as per Satyas Mail
,'INTEREST_INCOME'
,'INTEREST_PAYMENT'
,'LOAN_PAYMENT'
,'PRINCIPAL_BALANCE'
,'PRINCIPAL_PAYMENT'
-- Purposes specific to LS Type
,'BOOK_DEPRECIATION'
,'FEDERAL_DEPRECIATION'
,'FEE_RENEWAL'
,'INVESTOR_PRETAX_INCOME'
,'INVESTOR_RENTAL_ACCRUAL'
,'PASS_THROUGH_EVERGREEN_FEE'
,'PASS_THROUGH_EVERGREEN_RENT'
,'PASS_THROUGH_EVERGREEN_SERVICE'
,'PV_RENT'
,'PV_RENT_SECURITIZED'
,'PV_RV'
,'PV_RV_GUARANTEED'
,'PV_RV_INSURED'
,'PV_RV_SECURITIZED'
,'PV_RV_UNGUARANTEED'
,'PV_RV_UNINSURED'
,'RENEWAL_PROPERTY_TAX'
,'RENEWAL_RENT'
,'RESIDUAL_GUARANTEED'
,'RV_INSURANCE_PREMIUM'
,'RESIDUAL_VALUE'
,'STATE_DEPRECIATION'
,'STIP_LOSS_VALUE'
,'TERMINATION_VALUE'
-- Purposes specific to DF,ST type
,'LEASE_INCOME'
,'PROCESSING_FEE'
,'PROCESSING_FEE_ACCRUAL'
,'DOWN_PAYMENT'
,'INSURANCE_ESTIMATE_PAYMENT'
,'VENDOR_RESIDUAL_SHARING'
,'QUOTE_PER_DIEM'
--Bug 4616460 added new stream type purpose
,'ASSET_SALE_RECEIVABLE'
,'FLOAT_FACTOR_ADJUSTMENT'
--Bug 4616460 end
,'CAPITAL_REDUCTION'
--srsreeni 6117982 added
,'UPFRONT_TAX_FINANCED'
,'UPFRONT_TAX_CAPITALIZED'
--srsreeni 6117982 ends
, 'UPFRONT_TAX_BILLED' -- bug6619311
);
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND GTL.GTT_ID = p_gtt_id
UNION
-- Retrieve the List of Dependent Stream Purposes in the Template
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
and GTL.GTT_ID = p_gtt_id
)
MINUS
SELECT LOOKUP_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE in
(
'ACCOUNTING'
,'ACCRUED_FEE_EXPENSE'
,'ACCRUED_FEE_INCOME'
,'ACTUAL_PROPERTY_TAX'
,'ADJUSTED_PROPERTY_TAX'
,'ADVANCE_RENT'
,'AMORTIZED_FEE_EXPENSE'
,'AMORTIZE_FEE_INCOME'
,'REBOOK_BILLING_ADJUSTMENT'
,'CURE'
,'ESTIMATED_PROPERTY_TAX'
,'EXPENSE'
,'FEE_PAYMENT'
,'FUNDING'
,'INSURANCE_ACCRUAL_ADJUSTMENT'
,'INSURANCE_ADJUSTMENT'
,'INSURANCE_EXPENSE_ACCRUAL'
,'INSURANCE_INCOME_ACCRUAL'
,'INSURANCE_PAYABLE'
,'INSURANCE_RECEIVABLE'
,'INSURANCE_REFUND'
,'LATE_FEE'
,'LATE_INTEREST'
,'PASS_THRU_EXP_ACCRUAL'
,'PASS_THROUGH_FEE'
,'PASS_THRU_REV_ACCRUAL'
,'PASS_THROUGH_SERVICE'
,'PASS_THRU_SVC_EXP_ACCRUAL'
,'PASS_THRU_SVC_REV_ACCRUAL'
,'PREFUNDING_INTEREST_PAYMENT'
-- Bug 4110239: Start
--,'PROVISION'
,'SPECIFIC_LOSS_PROVISION'
,'GENERAL_LOSS_PROVISION'
-- Bug 4110239: End
,'RENT'
,'GENERAL'
,'REPAIR_CHARGE'
,'SECURITY_DEPOSIT'
,'SERVICE_EXPENSE'
,'SERVICE_INCOME'
,'SERVICE_PAYMENT'
,'SERVICE_RENEWAL'
,'SUBSIDY'
,'SUBSIDY_INCOME'
,'USAGE_PAYMENT'
,'SERVICE_FEE_AMORT_SCHEDULE'
,'SERVICE_FEE_AUDIT_LETTER'
,'SERVICE_FEE_VAT_SCHEDULE'
,'SERVICE_FEE_VAR_RATE_STMNT'
,'SERVICE_FEE_INVOICE_REPRINT'
,'SERVICE_FEE_INVOICE_DEMAND'
,'SERVICE_FEE_REST_REQUEST'
,'SERVICE_FEE_TERM_REQUEST'
,'SERVICE_FEE_EXCHG_REQUEST'
,'SERVICE_FEE_TRANS_REQUEST'
,'SERVICE_FEE_PMT_CHANGE'
,'SERVICE_FEE_INTEREST_CONV'
,'SERVICE_FEE_GENERAL'
,'SERVICE_FEE_DOCUMENT_REQ'
,'AMBSPR'
,'AMAFEE'
,'AMBCOC'
,'AMYFEE'
,'AMCQDR'
,'AMPRTX'
,'AMEFEE'
,'AMFFEE'
,'AMGFEE'
,'AMIFEE'
,'AMCMIS'
,'AMMFEE'
,'AMPFEE'
,'AMCTOC'
,'AMBPOC'
,'AMCQFE'
,'AMCRFE'
,'AMCRIN'
,'AMYSAM'
,'AMCTPE'
,'AMCTUR'
--bug 4176696 fixed by smahapat
--,'BILL_ADJST'
-- Missed out in the first
,'FINANCED_FEE_PAYMENT'
,'PREFUNDING'
-- Added as per Satyas Mail
,'INTEREST_INCOME'
,'INTEREST_PAYMENT'
,'LOAN_PAYMENT'
,'PRINCIPAL_BALANCE'
,'PRINCIPAL_PAYMENT'
-- Purposes specific to LS Type
,'BOOK_DEPRECIATION'
,'FEDERAL_DEPRECIATION'
,'FEE_RENEWAL'
,'INVESTOR_PRETAX_INCOME'
,'INVESTOR_RENTAL_ACCRUAL'
,'PASS_THROUGH_EVERGREEN_FEE'
,'PASS_THROUGH_EVERGREEN_RENT'
,'PASS_THROUGH_EVERGREEN_SERVICE'
,'PV_RENT'
,'PV_RENT_SECURITIZED'
,'PV_RV'
,'PV_RV_GUARANTEED'
,'PV_RV_INSURED'
,'PV_RV_SECURITIZED'
,'PV_RV_UNGUARANTEED'
,'PV_RV_UNINSURED'
,'RENEWAL_PROPERTY_TAX'
,'RENEWAL_RENT'
,'RESIDUAL_GUARANTEED'
,'RV_INSURANCE_PREMIUM'
,'RESIDUAL_VALUE'
,'STATE_DEPRECIATION'
,'STIP_LOSS_VALUE'
,'TERMINATION_VALUE'
-- Purposes specific to OP Type
,'RENT_ACCRUAL'
,'PROCESSING_FEE'
,'PROCESSING_FEE_ACCRUAL'
,'DOWN_PAYMENT'
,'INSURANCE_ESTIMATE_PAYMENT'
,'VENDOR_RESIDUAL_SHARING'
,'QUOTE_PER_DIEM'
--Bug 4616460 added new stream type purpose
,'ASSET_SALE_RECEIVABLE'
--Bug 4616460 end
,'FLOAT_FACTOR_ADJUSTMENT'
,'CAPITAL_REDUCTION'
--srsreeni 6117982 added
,'UPFRONT_TAX_FINANCED'
,'UPFRONT_TAX_CAPITALIZED'
--srsreeni 6117982 ends
, 'UPFRONT_TAX_BILLED' -- bug 6619311
);
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND GTL.GTT_ID = p_gtt_id
UNION
-- Retrieve the List of Dependent Stream Purposes in the Template
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.dependent_sty_id = stb.id
AND gtl.primary_yn = 'N'
and GTL.GTT_ID = p_gtt_id
)
MINUS
SELECT LOOKUP_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE in
(
'ACCOUNTING'
,'ACCRUED_FEE_EXPENSE'
,'ACCRUED_FEE_INCOME'
,'ACTUAL_PROPERTY_TAX'
,'ADJUSTED_PROPERTY_TAX'
,'ADVANCE_RENT'
,'AMORTIZED_FEE_EXPENSE'
,'AMORTIZE_FEE_INCOME'
,'REBOOK_BILLING_ADJUSTMENT'
,'CURE'
,'ESTIMATED_PROPERTY_TAX'
,'EXPENSE'
,'FEE_PAYMENT'
,'FUNDING'
,'INSURANCE_ACCRUAL_ADJUSTMENT'
,'INSURANCE_ADJUSTMENT'
,'INSURANCE_EXPENSE_ACCRUAL'
,'INSURANCE_INCOME_ACCRUAL'
,'INSURANCE_PAYABLE'
,'INSURANCE_RECEIVABLE'
,'INSURANCE_REFUND'
,'LATE_FEE'
,'LATE_INTEREST'
,'PASS_THRU_EXP_ACCRUAL'
,'PASS_THROUGH_FEE'
,'PASS_THRU_REV_ACCRUAL'
,'PASS_THROUGH_SERVICE'
,'PASS_THRU_SVC_EXP_ACCRUAL'
,'PASS_THRU_SVC_REV_ACCRUAL'
,'PREFUNDING_INTEREST_PAYMENT'
-- Bug 4110239: Start
--,'PROVISION'
,'SPECIFIC_LOSS_PROVISION'
,'GENERAL_LOSS_PROVISION'
-- Bug 4110239: End
,'RENT'
,'GENERAL'
,'REPAIR_CHARGE'
,'SECURITY_DEPOSIT'
,'SERVICE_EXPENSE'
,'SERVICE_INCOME'
,'SERVICE_PAYMENT'
,'SERVICE_RENEWAL'
,'SUBSIDY'
,'SUBSIDY_INCOME'
,'USAGE_PAYMENT'
,'SERVICE_FEE_AMORT_SCHEDULE'
,'SERVICE_FEE_AUDIT_LETTER'
,'SERVICE_FEE_VAT_SCHEDULE'
,'SERVICE_FEE_VAR_RATE_STMNT'
,'SERVICE_FEE_INVOICE_REPRINT'
,'SERVICE_FEE_INVOICE_DEMAND'
,'SERVICE_FEE_REST_REQUEST'
,'SERVICE_FEE_TERM_REQUEST'
,'SERVICE_FEE_EXCHG_REQUEST'
,'SERVICE_FEE_TRANS_REQUEST'
,'SERVICE_FEE_PMT_CHANGE'
,'SERVICE_FEE_INTEREST_CONV'
,'SERVICE_FEE_GENERAL'
,'SERVICE_FEE_DOCUMENT_REQ'
,'AMBSPR'
,'AMAFEE'
,'AMBCOC'
,'AMYFEE'
,'AMCQDR'
,'AMPRTX'
,'AMEFEE'
,'AMFFEE'
,'AMGFEE'
,'AMIFEE'
,'AMCMIS'
,'AMMFEE'
,'AMPFEE'
,'AMCTOC'
,'AMBPOC'
,'AMCQFE'
,'AMCRFE'
,'AMCRIN'
,'AMYSAM'
,'AMCTPE'
,'AMCTUR'
--bug 4176696 fixed by smahapat
--,'BILL_ADJST'
-- Missed out in the first
,'FINANCED_FEE_PAYMENT'
,'PREFUNDING'
-- Specific Purposes for Loan And Loan Revolving Deal Type
,'INTEREST_INCOME'
,'INTEREST_PAYMENT'
,'LOAN_PAYMENT'
,'PRINCIPAL_BALANCE'
,'PRINCIPAL_CATCHUP'
,'PRINCIPAL_PAYMENT'
,'UNSCHEDULED_PRINCIPAL_PAYMENT'
,'VARIABLE_INCOME_NONACCRUAL'
,'VARIABLE_INTEREST'
,'VARIABLE_INTEREST_INCOME'
,'VARIABLE_INTEREST_SCHEDULE'
-- Bug 4137045: Start
,'INVESTOR_INTEREST_INCOME'
,'INVESTOR_VARIABLE_INTEREST'
-- Bug 4137045: End
,'PROCESSING_FEE'
,'PROCESSING_FEE_ACCRUAL'
,'DOWN_PAYMENT'
,'INSURANCE_ESTIMATE_PAYMENT'
,'DAILY_INTEREST_PRINCIPAL'
,'DAILY_INTEREST_INTEREST'
,'INTEREST_CATCHUP'
,'UNSCHEDULED_LOAN_PAYMENT'
,'EXCESS_PRINCIPAL_PAID'
,'EXCESS_INTEREST_PAID'
,'EXCESS_LOAN_PAYMENT_PAID'
,'QUOTE_PER_DIEM'
--Bug 4616460 added new stream type purpose
,'ASSET_SALE_RECEIVABLE'
--Bug 4616460 end
--Bug 4664317 adds ACTUAL_INCOME_ACCRUAL
,'ACTUAL_INCOME_ACCRUAL'
--Bug 4677496 adds ACTUAL_INCOME_ACCRUAL
,'VARIABLE_LOAN_PAYMENT'
,'CAPITAL_REDUCTION'
--srsreeni 6117982 added
,'UPFRONT_TAX_FINANCED'
,'UPFRONT_TAX_CAPITALIZED'
--srsreeni 6117982 ends
, 'UPFRONT_TAX_BILLED' -- bug 6619311
);
SELECT gts.revenue_recog_meth_code revenue_recog_meth_code,
gts.interest_calc_meth_code interest_calc_meth_code
FROM OKL_ST_GEN_TMPT_SETS gts,
OKL_ST_GEN_TEMPLATES gtt
WHERE gtt.gts_id = gts.id
AND gtt.id = p_gtt_id;
fin_mandatory_pri_all_rec.purpose = 'REBOOK_BILLING_ADJUSTMENT' ) -- OKL.H Deleted this purpose from the list
THEN
-- Donot populate this Purpose to the error list.
NULL;
SELECT stb.stream_type_purpose purpose, count(stb.stream_type_purpose)
FROM OKL_ST_GEN_TMPT_LNS gtl
,okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'INVESTOR_CNTRCT_OBLIGATION_PAY'
,'INVESTOR_DISB_ADJUSTMENT'
,'INVESTOR_EVERGREEN_RENT_PAY'
,'INVESTOR_INTEREST_PAYABLE'
,'INVESTOR_LATE_FEE_PAYABLE'
,'INVESTOR_LATE_INTEREST_PAY'
,'INVESTOR_PAYABLE'
,'INVESTOR_PRINCIPAL_PAYABLE'
,'INVESTOR_RECEIVABLE'
,'INVESTOR_RENT_BUYBACK'
,'INVESTOR_RENT_DISB_BASIS'
,'INVESTOR_RENT_PAYABLE'
,'INVESTOR_RESIDUAL_BUYBACK'
,'INVESTOR_RESIDUAL_DISB_BASIS'
,'INVESTOR_RESIDUAL_PAY'
)
AND GTL.GTT_ID = p_gtt_id
group by stb.stream_type_purpose
having count(stb.stream_type_purpose) > 1;
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE IN
(
'INVESTOR_CNTRCT_OBLIGATION_PAY'
,'INVESTOR_DISB_ADJUSTMENT'
,'INVESTOR_EVERGREEN_RENT_PAY'
,'INVESTOR_LATE_FEE_PAYABLE'
,'INVESTOR_LATE_INTEREST_PAY'
,'INVESTOR_PAYABLE'
-- Modified by RGOOTY
-- Bug 4111470: Start
--,'INVESTOR_INTEREST_PAYABLE'
--,'INVESTOR_PRINCIPAL_PAYABLE'
-- Bug 4111470: End
,'INVESTOR_RECEIVABLE'
,'INVESTOR_RENT_BUYBACK'
,'INVESTOR_RENT_DISB_BASIS'
,'INVESTOR_RENT_PAYABLE'
,'INVESTOR_RESIDUAL_BUYBACK'
,'INVESTOR_RESIDUAL_DISB_BASIS'
,'INVESTOR_RESIDUAL_PAY'
-- Change requested by Satya on Nov 10
,'PV_RENT_SECURITIZED'
,'PV_RV_SECURITIZED'
)
MINUS
SELECT distinct stb.stream_type_purpose purpose
FROM OKL_ST_GEN_TMPT_LNS gtl
, okl_strm_type_b stb
WHERE gtl.primary_sty_id = stb.id
AND gtl.primary_yn = 'Y'
AND stb.stream_type_purpose IN
(
'INVESTOR_CNTRCT_OBLIGATION_PAY'
,'INVESTOR_DISB_ADJUSTMENT'
,'INVESTOR_EVERGREEN_RENT_PAY'
,'INVESTOR_LATE_FEE_PAYABLE'
,'INVESTOR_LATE_INTEREST_PAY'
,'INVESTOR_PAYABLE'
-- Modified by RGOOTY
-- Bug 4111470: Start
--,'INVESTOR_INTEREST_PAYABLE'
--,'INVESTOR_PRINCIPAL_PAYABLE'
-- Bug 4111470: End
,'INVESTOR_RECEIVABLE'
,'INVESTOR_RENT_BUYBACK'
,'INVESTOR_RENT_DISB_BASIS'
,'INVESTOR_RENT_PAYABLE'
,'INVESTOR_RESIDUAL_BUYBACK'
,'INVESTOR_RESIDUAL_DISB_BASIS'
,'INVESTOR_RESIDUAL_PAY'
-- Change requested by Satya on Nov 10
,'PV_RENT_SECURITIZED'
,'PV_RV_SECURITIZED'
)
and GTL.GTT_ID = p_gtt_id;
SELECT ID
,GTS_ID
,VERSION
,START_DATE
,END_DATE
,TMPT_STATUS
FROM OKL_ST_GEN_TEMPLATES
where ID = p_gtt_id;
okl_gtt_pvt.update_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
);
SELECT LOOKUP_CODE PURPOSE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = G_OKL_FIN_DEPENDENT_PURPOSES
AND LOOKUP_CODE IN
(
'AMORTIZE_FEE_INCOME'
,'ACCRUED_FEE_INCOME'
,'PRINCIPAL_PAYMENT'
,'INTEREST_PAYMENT'
,'PRINCIPAL_BALANCE'
,'INTEREST_INCOME'
,'LOAN_PAYMENT'
,'FEE_RENEWAL'
,'PASS_THRU_REV_ACCRUAL'
,'PASS_THRU_EXP_ACCRUAL'
)
MINUS
(
SELECT STB.STREAM_TYPE_PURPOSE PURPOSE
FROM OKL_ST_GEN_TMPT_LNS GTL
,OKL_STRM_TYPE_B STB
WHERE GTL.GTT_ID = p_gtt_id
AND GTL.PRIMARY_YN = 'N'
AND GTL.DEPENDENT_STY_ID = STB.ID
AND GTL.PRIMARY_STY_ID = p_pri_sty_id
)
MINUS
(
SELECT 'FEE_RENEWAL' PURPOSE
FROM DUAL
WHERE p_exclude_fr = 'T'
);
SELECT GTL.PRIMARY_STY_ID PRI_STY_ID
,GTS.DEAL_TYPE DEAL_TYPE
,STB.ID STRM_TYPE_ID
,STB.CODE STRM_NAME
FROM OKL_ST_GEN_TMPT_LNS GTL
,OKL_ST_GEN_TMPT_SETS GTS
,OKL_ST_GEN_TEMPLATES GTT
,OKL_STRM_TYPE_B STB
WHERE GTL.GTT_ID = p_gtt_id
AND GTL.GTT_ID = GTT.ID
AND GTS.ID = GTT.GTS_ID
AND GTL.PRIMARY_YN = 'Y'
AND GTL.PRIMARY_STY_ID = STB.ID
AND STB.STREAM_TYPE_PURPOSE = 'FEE_PAYMENT';
SELECT GTT.ID ID
,GTT.GTS_ID GTS_ID
,GTS.NAME NAME
,GTS.PRODUCT_TYPE PRODUCT_TYPE
,GTS.DEAL_TYPE DEAL_TYPE
,GTT.START_DATE START_DATE
,GTT.END_DATE END_DATE
,GTT.TMPT_STATUS TMPT_STATUS
,GTT.VERSION VERSION
FROM OKL_ST_GEN_TEMPLATES GTT
,OKL_ST_GEN_TMPT_SETS GTS
WHERE GTT.GTS_ID = GTS.ID
AND GTT.ID = p_gtt_id;
okl_gtt_pvt.update_row(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_gttv_rec => l_gttv_rec_in
,x_gttv_rec => l_gttv_rec_out
);
Procedure update_dep_strms(
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_gtt_id IN OKL_ST_GEN_TEMPLATES.ID%type
,p_pri_sty_id IN OKL_ST_GEN_TMPT_LNS.PRIMARY_STY_ID%TYPE
,p_gtlv_tbl IN gtlv_tbl_type
,x_missing_deps OUT NOCOPY VARCHAR2
,x_show_warn_flag OUT NOCOPY VARCHAR2
)IS
l_api_name CONSTANT VARCHAR2(40) := 'update_dep_strms';
SELECT STY.STREAM_TYPE_PURPOSE PURPOSE_CODE,
( SELECT MEANING FROM
FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE = STY.STREAM_TYPE_PURPOSE
) PURPOSE_MEANING,
COUNT( STY.STREAM_TYPE_PURPOSE) streams_count
FROM OKL_ST_GEN_TMPT_LNS GTL
,okl_Strm_Type_b STY
WHERE GTL.DEPENDENT_STY_ID = STY.ID
AND PRIMARY_YN = 'N'
AND GTL.GTT_ID = p_gtt_id
AND GTL.PRIMARY_STY_ID = p_pri_id
GROUP BY STY.STREAM_TYPE_PURPOSE
HAVING COUNT( STREAM_TYPE_PURPOSE) > 1;
SELECT GTS.DEAL_TYPE DEAL_TYPE
,GTT.TMPT_STATUS TMPT_STATUS
,GTS.ID GTS_ID -- Bug 4094361, Modified by RGOOTY
FROM OKL_ST_GEN_TMPT_SETS GTS
,OKL_ST_GEN_TEMPLATES GTT
WHERE GTS.ID = GTT.GTS_ID
AND GTT.ID = p_gtt_id;
SELECT STY.STREAM_TYPE_PURPOSE PRI_STRM_PURPOSE
FROM OKL_ST_GEN_TMPT_LNS GTL
,OKL_STRM_TYPE_B STY
WHERE STY.ID = GTL.PRIMARY_sTY_ID
AND GTL.PRIMARY_YN = 'Y'
AND GTL.GTT_ID = p_gtt_id
AND GTL.PRIMARY_STY_ID = p_pri_sty_id;
SELECT PDT.ID PDT_ID
,PRODUCT_STATUS_CODE
FROM OKL_PRODUCTS PDT,
OKL_AE_TMPT_SETS ATS, OKL_ST_GEN_TMPT_SETS SGT
WHERE PDT.AES_ID = ATS.ID
AND ATS.GTS_ID = SGT.ID
AND SGT.ID = p_gts_id;
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_ins_in
,x_gtlv_tbl => l_gtlv_tbl_ins_out
);
okl_gtl_pvt.update_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_upd_in
,x_gtlv_tbl => l_gtlv_tbl_upd_out
);
OKL_SETUPPRODUCTS_PVT.update_product_status(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_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 => pdt_rec.pdt_id );
PROCEDURE update_pri_dep_of_sgt(
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_gtsv_rec IN gtsv_rec_type
,p_gttv_rec IN gttv_rec_type
,p_gtpv_tbl IN gtpv_tbl_type
,p_pri_gtlv_tbl IN gtlv_tbl_type
,p_del_dep_gtlv_tbl IN gtlv_tbl_type
,p_ins_dep_gtlv_tbl IN gtlv_tbl_type
,x_gttv_rec OUT NOCOPY gttv_rec_type
,x_pri_purpose_list OUT NOCOPY VARCHAR2)
IS
-- Cursor to fetch the Stream Generation Template Set ID
CURSOR fetch_gts_id_csr( p_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE )
IS
SELECT GTT.GTS_ID
FROM OKL_ST_GEN_TEMPLATES GTT
WHERE GTT.ID = p_gtt_id;
SELECT STYP.CODE PRI_STRM_TYPE_NAME,
-- gboomina Bug 4874272 - Added - Start
STYP.STREAM_TYPE_PURPOSE PRI_STRM_TYPE_PURPOSE,
-- gboomina Bug 4874272 - Added - End
STY.STREAM_TYPE_PURPOSE DEP_PURPOSE_CODE,
( SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND LOOKUP_CODE = STY.STREAM_TYPE_PURPOSE
) DEP_PURPOSE_MEANING,
COUNT( STY.STREAM_TYPE_PURPOSE) streams_count
FROM OKL_ST_GEN_TMPT_LNS GTL
,OKL_ST_GEN_TMPT_LNS GTLP
,OKL_STRM_TYPE_B STY
,OKL_STRM_TYPE_B STYP
WHERE GTL.DEPENDENT_STY_ID = STY.ID
AND GTLP.PRIMARY_STY_ID = STYP.ID
AND GTL.PRIMARY_YN = 'N'
AND GTLP.PRIMARY_YN = 'Y'
AND GTLP.primary_sty_id = GTL.primary_sty_id
AND GTL.GTT_ID = p_gtt_id
AND GTLP.GTT_ID = p_gtt_id
-- gboomina Bug 4874272 - Added STREAM_TYPE_PURPOSE in Group by - Start
GROUP BY STYP.STREAM_TYPE_PURPOSE, STYP.CODE, STY.STREAM_TYPE_PURPOSE
-- gboomina Bug 4874272 - Added STREAM_TYPE_PURPOSE in Group by - End
HAVING COUNT( STY.STREAM_TYPE_PURPOSE) > 1;
SELECT PDT.ID PDT_ID
,PRODUCT_STATUS_CODE
FROM OKL_PRODUCTS PDT,
OKL_AE_TMPT_SETS ATS,
OKL_ST_GEN_TMPT_SETS SGT
WHERE PDT.AES_ID = ATS.ID
AND ATS.GTS_ID = SGT.ID
AND SGT.ID = p_gts_id;
l_api_name CONSTANT VARCHAR2(40) := 'update_pri_dep_of_sgt';
update_strm_gen_template(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gtsv_rec => p_gtsv_rec,
p_gttv_rec => p_gttv_rec,
p_gtpv_tbl => p_gtpv_tbl,
p_gtlv_tbl => p_pri_gtlv_tbl,
x_gttv_rec => x_gttv_rec);
delete_dep_tmpt_lns(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gtlv_tbl => p_del_dep_gtlv_tbl);
update_dep_strms(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gtt_id => x_gttv_rec.id, -- SGT Version ID
p_pri_sty_id => NULL, -- Not mandatory
p_gtlv_tbl => p_ins_dep_gtlv_tbl,
x_missing_deps => l_missing_deps,
x_show_warn_flag => l_show_warn_flag);
p_during_upd_flag => 'T' ); -- This Wrapper API will be called only in Update Mode
OKL_SETUPPRODUCTS_PVT.update_product_status(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_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 => pdt_rec.pdt_id );
END update_pri_dep_of_sgt;