[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT days_in_a_year_code,
days_in_a_month_code,
DECODE(rate_params.days_in_a_month_code,'30','360',
rate_params.days_in_a_month_code) esg_days_in_month_code
FROM OKL_K_RATE_PARAMS rate_params
WHERE khr_id = khrId;
SELECT gts.days_in_month_code days_in_a_month_code,
gts.days_in_yr_code days_in_a_year_code,
DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code)
esg_days_in_month_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_khr_id;
SELECT ID
,REFERENCE_NUMBER
,EXPECTED_START_DATE
,CURRENCY_CODE
,TERM
,SALES_TERRITORY_ID
,END_OF_TERM_OPTION_ID
,PRICING_METHOD
,STRUCTURED_PRICING
,LINE_LEVEL_PRICING
,LEASE_RATE_FACTOR
,RATE_CARD_ID
,RATE_TEMPLATE_ID
,TARGET_RATE_TYPE
,TARGET_RATE
,TARGET_AMOUNT -- Need to solve for this amount only ...!
,TARGET_FREQUENCY
,TARGET_ARREARS
,TARGET_PERIODS
FROM OKL_QUICK_QUOTES_B
WHERE ID = p_qq_id;
SELECT value
,basis
,nvl(nvl(end_of_term_value, end_of_term_value_default), 0) end_of_term_amount
,percentage_of_total_cost
FROM OKL_QUICK_QUOTE_LINES_B qql
WHERE qql.quick_quote_id = p_qq_id
AND TYPE = G_ITEMCATEGORY_TYPE; -- Item Category Type
SELECT qq.id
,qq.reference_number
,eot.end_of_term_name
,eot.eot_type_code eot_type_code
,eot.end_of_term_id end_of_term_id
,eotversion.end_of_term_ver_id
FROM OKL_QUICK_QUOTES_B qq,
okl_fe_eo_term_vers eotversion,
okl_fe_eo_terms_all_b eot
WHERE qq.END_OF_TERM_OPTION_ID = eotversion.end_of_term_ver_id
AND eot.end_of_term_id = eotversion.end_of_term_id
AND qq.id = p_qq_id;
SELECT type
,basis
,value
FROM OKL_QUICK_QUOTE_LINES_B qql
WHERE qql.quick_quote_id = p_qq_id
AND TYPE IN ( G_DOWNPAYMENT_TYPE, -- Down Payment Type
G_TRADEIN_TYPE, -- Trade in Type
G_SUBSIDY_TYPE -- Subsidy Type
)
ORDER BY TYPE;
SELECT gts.days_in_month_code days_in_month,
gts.days_in_yr_code days_in_year,
gts.id gts_id,
pdt.id pdt_id
FROM okl_fe_eo_terms_all_b eot
,okl_fe_eo_term_vers eov
,okl_quick_quotes_b qqh
,okl_products pdt
,okl_ae_tmpt_sets aes
,okl_st_gen_tmpt_sets gts
WHERE qqh.end_of_term_option_id = eov.end_of_term_ver_id
AND eov.end_of_term_id = eot.end_of_term_id
AND eot.product_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND qqh.id = p_qq_id;
SELECT gts.days_in_month_code days_in_month,
gts.days_in_yr_code days_in_year,
gts.id gts_id,
pdt.id pdt_id
FROM okl_lease_quotes_b lqh
,okl_products pdt
,okl_ae_tmpt_sets aes
,okl_st_gen_tmpt_sets gts
WHERE lqh.product_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND lqh.id = p_lq_id;
SELECT cf.id caf_id
,dnz_khr_id khr_id
,dnz_qte_id qte_id
,cfo_id cfo_id
,sts_code sts_code
,sty_id sty_id
,cft_code cft_code
,due_arrears_yn due_arrears_yn
,start_date start_date
,number_of_advance_periods number_of_advance_periods
,oty_code oty_code
FROM OKL_CASH_FLOWS cf,
OKL_CASH_FLOW_OBJECTS cfo
WHERE cf.cfo_id = cfo.id
AND cfo.source_table = p_cf_source_type
AND cfo.source_id = p_qq_id
AND cf.sts_code = 'CURRENT';
SELECT id cfl_id
,caf_id
,fqy_code
,rate -- No rate is defined at Cash Flows Level.. Need to confirm
,stub_days
,stub_amount
,number_of_periods
,amount
,start_date
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id = p_caf_id
ORDER BY start_date;
SELECT ast.parent_object_id lq_id
FROM okl_assets_b ast
WHERE ast.id = p_ast_id;
SELECT fee.parent_object_id lq_id
FROM okl_fees_b fee
WHERE fee.id = p_fee_id;
SELECT qql.id qq_line_id
,qql.type type
,qql.basis basis
,qql.value value
FROM OKL_QUICK_QUOTE_LINES_B qql
WHERE qql.quick_quote_id = p_qq_id
AND TYPE IN (G_QQ_FEE_EXPENSE,
G_QQ_FEE_PAYMENT)
ORDER BY TYPE;
SELECT cf.id caf_id
,dnz_khr_id khr_id
,dnz_qte_id qte_id
,cfo_id cfo_id
,sts_code sts_code
,sty_id sty_id
,cft_code cft_code
,due_arrears_yn due_arrears_yn
,start_date start_date
,number_of_advance_periods number_of_advance_periods
,oty_code oty_code
FROM OKL_CASH_FLOWS cf,
OKL_CASH_FLOW_OBJECTS cfo
WHERE cf.cfo_id = cfo.id
AND cfo.source_table = 'OKL_QUICK_QUOTE_LINES_B'
AND cfo.source_id = p_qq_line_id;
SELECT id cfl_id
,caf_id
,fqy_code
,rate
,stub_days
,stub_amount
,number_of_periods
,amount
,start_date
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id = p_caf_id
ORDER BY start_date;
l_cash_flow_level_tbl.DELETE;
l_temp_cf_strms_tbl.DELETE;
SELECT NVL(cur.precision,0) precision
FROM fnd_currencies cur
WHERE cur.currency_code = p_currency_code;
SELECT
hdr.id header_id,
ver.rate_set_version_id version_id,
hdr.NAME name,
hdr.lrs_type_code lrs_type_code,
hdr.FRQ_CODE frq_code,
hdr.currency_code currency_code,
ver.sts_code sts_code,
ver.effective_from_date effective_from_date,
ver.effective_to_date effective_to_date,
ver.arrears_yn arrears_yn,
ver.end_of_term_ver_id end_of_term_ver_id,
ver.std_rate_tmpl_ver_id std_rate_tmpl_ver_id,
ver.adj_mat_version_id adj_mat_version_id,
ver.version_number version_number,
ver.lrs_rate lrs_version_rate,
ver.rate_tolerance rate_tolerance,
ver.residual_tolerance residual_tolerance,
ver.deferred_pmts deferred_pmts,
ver.advance_pmts advance_pmts
FROM okl_ls_rt_fctr_sets_v hdr,
okl_fe_rate_set_versions_v ver
WHERE ver.rate_set_id = hdr.id
AND ver.rate_set_version_id = p_lrs_version_id
AND trunc(ver.effective_from_date) <= p_date
AND nvl(trunc(ver.effective_to_date), p_date) >= p_date;
SELECT fac.id factor_id,
fac.term_in_months term_in_months,
fac.residual_value_percent residual_value_percent
FROM okl_ls_rt_fctr_ents_v fac
WHERE fac.rate_set_version_id = p_lrs_version_id
AND fac.term_in_months = p_term
AND p_eot_percentage BETWEEN fac.residual_value_percent - nvl(p_res_tolerance, 0) AND
fac.residual_value_percent + nvl(p_res_tolerance, 0);
SELECT lvl.sequence_number sequence_number,
lvl.periods periods,
lvl.lease_rate_factor lease_rate_factor
FROM okl_fe_rate_set_levels lvl
WHERE lvl.rate_set_factor_id = p_lrs_factor_id
ORDER BY sequence_number ASC;
SELECT hdr.std_rate_tmpl_id srt_header_id,
ver.std_rate_tmpl_ver_id srt_version_id,
hdr.template_name template_name,
hdr.currency_code currency_code,
ver.version_number version_number,
ver.effective_from_date effective_from_date,
ver.effective_to_date effective_to_date,
ver.sts_code sts_code,
hdr.pricing_engine_code pricing_engine_code,
hdr.rate_type_code rate_type_code,
ver.srt_rate srt_rate,
hdr.index_id index_id,
ver.spread spread,
ver.day_convention_code day_convention_code,
hdr.frequency_code frequency_code,
ver.adj_mat_version_id adj_mat_version_id,
ver.min_adj_rate min_adj_rate,
ver.max_adj_rate max_adj_rate
FROM okl_fe_std_rt_tmp_v hdr,
okl_fe_std_rt_tmp_vers ver
WHERE ver.std_rate_tmpl_ver_id = p_srt_version_id AND
hdr.std_rate_tmpl_id = ver.std_rate_tmpl_id AND
ver.effective_from_date <= p_date AND
nvl(ver.effective_to_date, p_date) >= p_date;
SELECT indx.value srt_rate
FROM okl_fe_std_rt_tmp_all_b hdr,
okl_index_values indx
WHERE hdr.std_rate_tmpl_id = p_srt_header_id
AND hdr.index_id = indx.idx_id
AND p_date BETWEEN indx.datetime_valid AND NVL(indx.datetime_invalid,p_date+1);
l_pre_tax_income_tbl.DELETE;
l_termination_val_tbl.DELETE;
SELECT id
,item_category_id
,value
,basis
,nvl(nvl(end_of_term_value, end_of_term_value_default), 0) end_of_term_amount
,lease_rate_factor
FROM OKL_QUICK_QUOTE_LINES_B qql
WHERE qql.quick_quote_id = p_qq_id
AND TYPE = G_ITEMCATEGORY_TYPE; -- Item Category Type
SELECT category_concat_segs name,
description
FROM mtl_categories_v
WHERE category_id = p_category_id;
SELECT TYPE type
,SUM(CASE basis
WHEN 'FIXED' THEN value
WHEN 'ASSET_COST' THEN value * p_tot_item_cost * .01
END ) AS fin_value
FROM OKL_QUICK_QUOTE_LINES_B qql
WHERE qql.quick_quote_id = p_qq_id
AND TYPE IN ( G_DOWNPAYMENT_TYPE, -- Down Payment Type
G_TRADEIN_TYPE, -- Trade in Type
G_SUBSIDY_TYPE ) -- Subsidy Type
GROUP BY TYPE
ORDER BY TYPE;
SELECT qq.id
,qq.reference_number
,eot.end_of_term_name
,eot.eot_type_code eot_type_code
,eot.end_of_term_id end_of_term_id
,eotversion.end_of_term_ver_id
FROM OKL_QUICK_QUOTES_B qq,
okl_fe_eo_term_vers eotversion,
okl_fe_eo_terms_all_b eot
WHERE qq.END_OF_TERM_OPTION_ID = eotversion.end_of_term_ver_id
AND eot.end_of_term_id = eotversion.end_of_term_id
AND qq.id = p_qq_id;
l_lrs_levels.DELETE;
SELECT sum(VALUE) deal_size
FROM OKL_QUICK_QUOTE_LINES_B qql
WHERE qql.quick_quote_id = p_qq_id
AND qql.TYPE = G_ITEMCATEGORY_TYPE; -- Item Category Type
SELECT qte.expected_start_date expected_start_date,
qte.target_rate_type target_rate_type,
qte.target_frequency target_frequency,
qte.target_arrears_yn target_arrears,
qte.target_rate target_rate,
qte.target_periods target_periods,
qte.target_amount target_amount,
qte.term term,
qte.structured_pricing structured_pricing,
qte.pricing_method pricing_method
FROM okl_lease_quotes_b qte
WHERE qte.id = qteid;
SELECT cfl.ID cfl_id,
cfh.id caf_id,
cfo.id cfo_id,
cfh.sty_id stream_type_id
FROM OKL_CASH_FLOW_LEVELS cfl,
OKL_CASH_FLOWS cfh,
OKL_CASH_FLOW_OBJECTS cfo
WHERE cfl.caf_id = cfh.id
AND cfh.cfo_id = cfo.id
AND cfo.source_table = 'OKL_LEASE_QUOTES_B'
AND cfo.source_id = qteId
ORDER BY cfl.start_date;
SELECT target_arrears,
target_amount,
parent_object_id
FROM OKL_ASSETS_B
WHERE id = p_astId;
SELECT target_arrears,
target_amount,
parent_object_id,
payment_type_id
FROM OKL_FEES_B
WHERE id = p_feeId;
SELECT cf.id caf_id
,dnz_khr_id khr_id
,dnz_qte_id qte_id
,cfo_id cfo_id
,sts_code sts_code
,sty_id sty_id
,cft_code cft_code
,due_arrears_yn due_arrears_yn
,start_date start_date
,number_of_advance_periods number_of_advance_periods
,oty_code oty_code
FROM OKL_CASH_FLOWS cf,
OKL_CASH_FLOW_OBJECTS cfo
WHERE cf.cfo_id = cfo.id
AND cfo.source_table = p_cf_source_type
AND cfo.source_id = p_id;
SELECT id cfl_id
,caf_id
,fqy_code
,rate -- No rate is defined at Cash Flows Level.. Need to confirm
,stub_days
,stub_amount
,number_of_periods
,amount
,start_date
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id = p_caf_id
ORDER BY start_date;
SELECT qq.id
,qq.reference_number
,eot.end_of_term_name
,eot.eot_type_code eot_type_code
,eot.end_of_term_id end_of_term_id
,eotversion.end_of_term_ver_id
FROM OKL_QUICK_QUOTES_B qq,
okl_fe_eo_term_vers eotversion,
okl_fe_eo_terms_all_b eot
WHERE qq.END_OF_TERM_OPTION_ID = eotversion.end_of_term_ver_id
AND eot.end_of_term_id = eotversion.end_of_term_id
AND qq.id = p_qq_id;
SELECT PDT.NAME PRODUCTNAME
FROM OKL_QUICK_QUOTES_B QQ
, OKL_FE_EO_TERM_VERS EOTVERSION
, OKL_FE_EO_TERMS_ALL_B EOT
, OKL_PRODUCTS PDT
WHERE QQ.END_OF_TERM_OPTION_ID = EOTVERSION.END_OF_TERM_VER_ID
AND EOT.END_OF_TERM_ID = EOTVERSION.END_OF_TERM_ID
AND PDT.ID = EOT.PRODUCT_ID
AND QQ.ID = P_QQ_ID;
p_msg_name => 'OKL_LLA_PMT_SELECT');
p_msg_name => 'OKL_LLA_PMT_SELECT');
l_strm_ele_tbl.DELETE;
l_strm_ele_tbl.DELETE;
l_tmp_prc_params_tbl.DELETE;
SELECT qte.pricing_method pricing_method,
qte.rate_template_id rate_template_id,
qte.structured_pricing structured_pricing,
qte.line_level_pricing line_level_pricing,
qte.target_arrears_yn target_arrears
FROM OKL_LEASE_QUOTES_B qte
WHERE qte.id = qteid;
SELECT ast.id ast_id,
ast.asset_number asset_number,
ast.oec oec,
ast.oec_percentage oec_percentage,
ast.structured_pricing structured_pricing,
ast.rate_template_id rate_template_id,
ast.target_arrears target_arrears
FROM okl_assets_b ast,
okl_lease_quotes_b qte
WHERE ast.parent_object_code = 'LEASEQUOTE' AND
ast.parent_object_id = qte.id AND
qte.id = qteid;
SELECT
id
,primary_component
,unit_cost
,number_of_units
FROM okl_asset_components_v
WHERE asset_id = p_asset_id
AND primary_component = 'YES';
l_component_tbl.DELETE;
l_component_tbl(1).record_mode := 'update';
'Updated l_component_tbl(1).unit_cost = ' || l_component_tbl(1).unit_cost );
'Before OKL_LEASE_QUOTE_ASSET_PVT.update_asset ');
OKL_LEASE_QUOTE_ASSET_PVT.update_asset (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => 'T',
p_asset_rec => l_asset_tbl(t),
p_component_tbl => l_component_tbl,
p_cf_hdr_rec => l_cf_hdr_rec,
p_cf_level_tbl => l_cf_level_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After OKL_LEASE_QUOTE_ASSET_PVT.update_asset ' || l_return_status );
SELECT amount
FROM okl_subsidies_b
WHERE id = subId;
SELECT qte.pricing_method pricing_method,
qte.rate_template_id rate_template_id,
qte.expected_start_date expected_start_date,
qte.expected_delivery_date expected_delivery_date,
qte.term term,
qte.lease_rate_factor,
qte.structured_pricing structured_pricing,
qte.line_level_pricing line_level_pricing,
qte.rate_card_id,
qte.id,
qte.parent_object_code,
qte.target_frequency target_frequency,
qte.target_arrears_yn target_arrears,
qte.product_id,
qte.sub_iir -- Yield used while calculating the payments @ Asset Level
FROM OKL_LEASE_QUOTES_B qte
WHERE qte.id = qteid;
SELECT PDT.NAME PRODUCTNAME
FROM OKL_LEASE_QUOTES_B QTE
, OKL_PRODUCTS PDT
WHERE QTE.PRODUCT_ID = PDT.ID
AND QTE.ID = QTEID;
SELECT stytl.name
FROM okl_fees_b fee,
okl_strm_type_tl stytl
WHERE fee.parent_object_code = 'LEASEQUOTE'
AND fee.parent_object_id = qteid
AND fee.id = fee_id
AND fee.STREAM_TYPE_ID = stytl.id
AND stytl.LANGUAGE = USERENV('LANG');
SELECT ast.asset_number,
ast.id ast_id,
TO_NUMBER(NULL) fee_id,
ast.rate_card_id,
ast.rate_template_id rate_template_id,
ast.structured_pricing,
'FREE_FORM1' fee_type,
TO_NUMBER(NULL) fee_amount,
ast.lease_rate_factor lease_rate_factor,
ast.asset_number name,
ast.target_frequency target_frequency,
ast.target_arrears target_arrears
FROM okl_assets_b ast,
okl_lease_quotes_b qte
WHERE ast.parent_object_code = 'LEASEQUOTE'
AND ast.parent_object_id = qte.id
AND qte.id = qteid
AND ast.id = ast_fee_id
AND p_line_type = 'FREE_FORM1'
UNION
SELECT NULL asset_number,
TO_NUMBER(NULL) ast_id,
fee.id fee_id,
fee.rate_card_id,
fee.rate_template_id,
fee.structured_pricing,
fee.fee_type,
fee.fee_amount,
fee.lease_rate_factor lease_rate_factor,
fee.fee_type || ' FEE' name,
fee.target_frequency target_frequency,
fee.target_arrears target_arrears
FROM okl_fees_b fee,
okl_lease_quotes_b qte
WHERE fee.parent_object_code = 'LEASEQUOTE'
AND fee.parent_object_id = qte.id
AND qte.id = qteid
AND fee.id = ast_fee_id
AND p_line_type IN ( 'FINANCED', 'ROLLOVER' );
SELECT ast.asset_number,
ast.install_site_id,
ast.rate_card_id,
ast.rate_template_id,
ast.oec,
nvl(nvl(ast.end_of_term_value, ast.end_of_term_value_default),0) end_of_term_value,
ast.oec_percentage,
cmp.unit_cost,
cmp.number_of_units,
cmp.primary_component
FROM okl_assets_b ast,
okl_lease_quotes_b qte,
okl_asset_components_b cmp
WHERE ast.parent_object_code = 'LEASEQUOTE' AND
ast.parent_object_id = qte.id AND
qte.id = qteid AND
ast.id = astid AND
cmp.primary_component = 'YES' AND
cmp.asset_id = ast.id;
SELECT adj.adjustment_source_type,
adj.adjustment_source_id,
adj.basis,
-- Start : DJANASWA : Bug# 6347118
nvl(adj.value,adj.default_subsidy_amount) value
-- End : DJANASWA : Bug# 6347118
FROM okl_assets_b ast,
okl_lease_quotes_b qte,
okl_cost_adjustments_b adj
WHERE ast.parent_object_code = 'LEASEQUOTE' AND
ast.parent_object_id = qte.id AND
qte.id = qteid AND
ast.id = astid AND
adj.parent_object_id = ast.id;
SELECT lopp.id parent_id
,lopp.prospect_id prospect_id
,lopp.cust_acct_id cust_acct_id
,lopp.sales_territory_id sales_territory_id
,lopp.currency_code currency_code
FROM okl_lease_quotes_b lq,
okl_lease_opportunities_b lopp
WHERE parent_object_code = 'LEASEOPP'
AND parent_object_id = lopp.id
AND lq.id = p_lq_id;
SELECT lapp.id parent_id
,lapp.prospect_id prospect_id
,lapp.cust_acct_id cust_acct_id
,lapp.sales_territory_id sales_territory_id
,lapp.currency_code currency_code
FROM okl_lease_quotes_b lq,
okl_lease_applications_b lapp
WHERE parent_object_code = 'LEASEAPP'
AND parent_object_id = lapp.id
AND lq.id = p_lq_id;
SELECT
id
,primary_component
,unit_cost
,number_of_units
FROM okl_asset_components_v
WHERE asset_id = p_asset_id
AND primary_component = 'YES';
SELECT 'YES' cfo_exists,
cfo.id cfo_id,
caf.id caf_id,
caf.sty_id sty_id
FROM OKL_CASH_FLOW_OBJECTS cfo,
OKL_CASH_FLOWS caf
WHERE OTY_CODE = p_oty_code
AND SOURCE_TABLE = p_source_table
AND SOURCE_ID = p_source_id
AND caf.cfo_id = cfo.id;
SELECT STRM.STY_ID PAYMENT_TYPE_ID,
STRM.STY_NAME PAYMENT_TYPE,
STRM.START_DATE,
STRM.END_DATE,
STRM.STY_PURPOSE
FROM OKL_STRM_TMPT_PRIMARY_UV STRM
WHERE STY_PURPOSE = strm_purpose
AND START_DATE <= expStartDate
AND NVL(END_DATE, expStartDate) >= expStartDate
AND STRM.PDT_ID = pdtId;
SELECT STRM.STY_ID PAYMENT_TYPE_ID,
STRM.STY_NAME PAYMENT_TYPE,
STRM.START_DATE,
STRM.END_DATE,
STRM.STY_PURPOSE
FROM OKL_STRM_TMPT_PRIMARY_UV STRM
WHERE STY_PURPOSE = strm_purpose
AND START_DATE <= expStartDate
AND NVL(END_DATE, expStartDate ) >= expStartDate
AND NVL(STRM.CAPITALIZE_YN, 'N') = 'N'
AND STRM.BILLABLE_YN = 'Y'
AND STRM.PDT_ID = pdtId;
SELECT lq.id
,lq.reference_number
,eot.end_of_term_name
,eot.eot_type_code eot_type_code
,eot.end_of_term_id end_of_term_id
,eotversion.end_of_term_ver_id
FROM OKL_LEASE_QUOTES_B lq,
okl_fe_eo_term_vers eotversion,
okl_fe_eo_terms_all_b eot
WHERE lq.END_OF_TERM_OPTION_ID = eotversion.end_of_term_ver_id
AND eot.end_of_term_id = eotversion.end_of_term_id
AND lq.id = p_lq_id;
SELECT SUM(amount) capitalized_amount
FROM okl_line_relationships_v lre
WHERE source_line_type = p_source_type
AND related_line_type = 'CAPITALIZED'
AND source_line_id = p_source_id;
p_msg_name => 'OKL_LLA_PMT_SELECT');
p_msg_name => 'OKL_LLA_PMT_SELECT');
okl_lease_quote_cashflow_pvt.delete_cashflows (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_source_object_code => l_cashflow_header_rec.parent_object_code,
p_source_object_id => p_ast_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_component_tbl(1).record_mode := 'update';
'Updated l_component_tbl(1).unit_cost = ' || l_component_tbl(1).unit_cost );
'Before OKL_LEASE_QUOTE_ASSET_PVT.update_asset ');
OKL_LEASE_QUOTE_ASSET_PVT.update_asset (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => 'T',
p_asset_rec => l_asset_rec,
p_component_tbl => l_component_tbl,
p_cf_hdr_rec => l_cf_hdr_rec,
p_cf_level_tbl => l_cf_level_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After OKL_LEASE_QUOTE_ASSET_PVT.update_asset ' || l_return_status );
l_ass_adj_tbl.DELETE;
SELECT cf.id caf_id
,dnz_khr_id khr_id
,dnz_qte_id qte_id
,cfo_id cfo_id
,sts_code sts_code
,sty_id sty_id
,cft_code cft_code
,due_arrears_yn due_arrears_yn
,start_date start_date
,number_of_advance_periods number_of_advance_periods
,cfo.oty_code oty_code
FROM OKL_CASH_FLOWS cf,
OKL_CASH_FLOW_OBJECTS cfo
WHERE cf.cfo_id = cfo.id
AND cfo.source_table = p_cf_source
AND cfo.source_id = p_id
AND cf .cft_code = p_cft_code ;
SELECT id cfl_id
,caf_id
,fqy_code
,rate -- No rate is defined at Cash Flows Level.. Need to confirm
,stub_days
,stub_amount
,number_of_periods
,amount
,start_date
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id = p_caf_id
ORDER BY start_date;
p_msg_name => 'OKL_LLA_PMT_SELECT');
SELECT cf.id caf_id
,dnz_khr_id khr_id
,dnz_qte_id qte_id
,cfo_id cfo_id
,sts_code sts_code
,sty_id sty_id
,cft_code cft_code
,due_arrears_yn due_arrears_yn
,start_date start_date
,number_of_advance_periods number_of_advance_periods
,cfo.oty_code oty_code
FROM OKL_CASH_FLOWS cf,
OKL_CASH_FLOW_OBJECTS cfo
WHERE cf.cfo_id = cfo.id
AND cfo.source_table = p_cf_source
AND cfo.source_id = p_id
AND cf .cft_code = p_cft_code ;
SELECT id cfl_id
,caf_id
,fqy_code
,rate -- No rate is defined at Cash Flows Level.. Need to confirm
,stub_days
,stub_amount
,number_of_periods
,amount
,start_date
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id = p_caf_id
ORDER BY start_date;
p_msg_name => 'OKL_LLA_PMT_SELECT');
SELECT ast.id ast_id,
ast.asset_number ast_number
FROM okl_assets_b ast,
okl_lease_quotes_b qte
WHERE ast.parent_object_code = 'LEASEQUOTE'
AND ast.parent_object_id = qte.id
AND qte.id = p_qte_id;
SELECT cfl.fqy_code frequency,
caf.due_arrears_yn adv_arrears,
caf.sty_id sty_id,
cfl.start_date,
cfl.rate,
cfl.stub_days,
cfl.stub_amount,
cfl.number_of_periods periods,
cfl.amount periodic_amount
FROM okl_assets_b ast,
okl_cash_flow_objects cfo,
okl_cash_flows caf,
okl_cash_flow_levels cfl,
okl_strm_type_b sty
WHERE ast.id = p_ast_id
AND ast.parent_object_id = p_qte_id
AND cfo.source_id = ast.id
AND caf.cfo_id = cfo.id
AND cfl.caf_id = caf.id
AND cfo.source_table = 'OKL_ASSETS_B'
AND cfo.oty_code = 'QUOTED_ASSET'
AND caf.sts_code IN ( 'CURRENT', 'WORK')
AND caf.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
ORDER BY cfl.start_date;
PROCEDURE delete_quote_streams(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quote_id IN NUMBER
) IS
l_return_status VARCHAR2(1):= G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) DEFAULT 'delete_quote_streams';
SELECT QTE_SEL.QUOTE_STRM_ELEMENT_ID
FROM OKL_QUOTE_STREAMS QTE_STM,
OKL_QUOTE_STRM_ELEMENTS QTE_SEL
WHERE QTE_STM.QUOTE_STREAM_ID = QTE_SEL.QUOTE_STREAM_ID
AND QTE_STM.QUOTE_ID = p_quote_id;
SELECT QTE_STM.QUOTE_STREAM_ID
FROM OKL_QUOTE_STREAMS QTE_STM
WHERE QTE_STM.QUOTE_ID = p_quote_id;
OKL_QSL_PVT.delete_row(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_qsl_tbl => l_strm_elements_tbl
);
OKL_QSH_PVT.delete_row(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_qsh_tbl => l_streams_tbl
);
END delete_quote_streams;
PROCEDURE insert_quote_streams(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quote_id IN NUMBER,
p_quote_type IN VARCHAR2,
p_currency IN VARCHAR2,
p_pricing_param_tbl IN OKL_PRICING_UTILS_PVT.pricing_parameter_tbl_type
) IS
l_return_status VARCHAR2(1):= G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) DEFAULT 'insert_quote_streams';
SELECT LSE_QT.PRODUCT_ID,
LSE_QT.EXPECTED_START_DATE,
PDT.DEAL_TYPE
FROM OKL_LEASE_QUOTES_B LSE_QT,
OKL_PRODUCT_PARAMETERS_V PDT
WHERE LSE_QT.PRODUCT_ID = PDT.ID
AND LSE_QT.ID = p_quote_id;
SELECT OTY_CODE,
SOURCE_TABLE,
SOURCE_ID,
STY_ID,
CFT_CODE
FROM OKL_CASH_FLOW_OBJECTS CFLOW_OBJ,
OKL_CASH_FLOWS CFLOW
WHERE CFLOW.CFO_ID = CFLOW_OBJ.ID
AND CFLOW_OBJ.ID = p_cfo_id;
SELECT PRIMARY_STY_ID
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_start_date)
AND (STL.END_DATE >= l_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = 'RESIDUAL_VALUE';
'Before Calling the API OKL_QSH_PVT.insert_row ' );
OKL_QSH_PVT.insert_row(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_qsh_rec => l_stream_rec,
x_qsh_rec => x_stream_rec
);
'After Calling the API OKL_QSH_PVT.insert_row ' || l_return_status );
l_stm_element_tbl.delete;
'Before Calling the API OKL_QSL_PVT.insertv_tbl ' );
OKL_QSL_PVT.insert_row(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_qsl_tbl => l_stm_element_tbl,
x_qsl_tbl => x_stm_element_tbl
);
'After Calling the API OKL_QSL_PVT.insertv_tbl x_return_status=' || l_return_status );
'Before executing the API OKL_QSH_PVT.insert_row ' );
OKL_QSH_PVT.insert_row(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_qsh_rec => l_stream_rec,
x_qsh_rec => x_stream_rec
);
'After executing the API OKL_QSH_PVT.insert_row. x_return_status=' || l_return_status );
'Before executing the API OKL_QSL_PVT.insert_row. ' );
OKL_QSL_PVT.insert_row(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_qsl_rec => l_stm_element_rec,
x_qsl_rec => x_stm_element_rec
);
'After executing the API OKL_QSL_PVT.insert_row. x_return_status=' || l_return_status );
END insert_quote_streams;
SELECT qte.term term,
qte.pricing_method,
qte.rate_template_id,
qte.expected_start_date,
qte.expected_delivery_date,
qte.structured_pricing structured_pricing,
qte.line_level_pricing line_level_pricing,
qte.target_rate_type target_rate_type,
qte.target_frequency target_frequency,
qte.target_arrears_yn target_arrears,
qte.target_rate target_rate,
qte.target_periods target_periods,
qte.lease_rate_factor,
qte.rate_card_id,
qte.id,
qte.parent_object_code,
qte.parent_object_id,
qte.object_version_number,
qte.reference_number,
qte.product_id
FROM okl_lease_quotes_b qte
WHERE qte.id = qteid;
SELECT STRM.STY_ID PAYMENT_TYPE_ID,
STRM.STY_NAME PAYMENT_TYPE,
STRM.START_DATE,
STRM.END_DATE,
STRM.STY_PURPOSE
FROM OKL_STRM_TMPT_PRIMARY_UV STRM,
OKL_LEASE_QUOTES_B QUOTE
WHERE STY_PURPOSE = 'RENT'
AND START_DATE <= expStartDate
AND NVL(END_DATE, expStartDate) >= expStartDate
AND STRM.PDT_ID = QUOTE.PRODUCT_ID
AND QUOTE.ID = qteID;
SELECT ast.asset_number,
ast.id ast_id,
TO_NUMBER(NULL) fee_id,
ast.rate_card_id,
ast.rate_template_id rate_template_id,
ast.structured_pricing,
'FREE_FORM1' fee_type,
TO_NUMBER(NULL) fee_amount,
qte.expected_start_date line_start_date,
qte.expected_delivery_date line_end_date,
ast.lease_rate_factor lease_rate_factor,
ast.target_arrears target_arrears,
ast.oec_percentage oec_percentage
FROM okl_assets_b ast,
okl_lease_quotes_b qte
WHERE ast.parent_object_code = 'LEASEQUOTE'
AND ast.parent_object_id = qte.id
AND qte.id = qteid
UNION
SELECT NULL asset_number,
TO_NUMBER(NULL) ast_id,
fee.id fee_id,
fee.rate_card_id,
fee.rate_template_id,
fee.structured_pricing,
fee.fee_type,
fee.fee_amount,
fee.effective_from line_start_date,
fee.effective_to line_end_date,
fee.lease_rate_factor lease_rate_factor,
fee.target_arrears target_arrears,
NULL oec_percentage
FROM okl_fees_b fee,
okl_lease_quotes_b qte
WHERE fee.parent_object_code = 'LEASEQUOTE'
AND fee.parent_object_id = qte.id
AND qte.id = qteid;
SELECT srvc.id srvc_id,
srvc.effective_from line_start_date
FROM okl_services_b srvc,
okl_lease_quotes_b qte
WHERE srvc.parent_object_code = 'LEASEQUOTE'
AND srvc.parent_object_id = qte.id
AND qte.id = qteid;
SELECT ast.asset_number,
ast.install_site_id,
ast.rate_card_id,
ast.rate_template_id,
ast.oec,
nvl(nvl(ast.end_of_term_value, ast.end_of_term_value_default),0) end_of_term_value,
ast.oec_percentage,
cmp.unit_cost,
cmp.number_of_units,
cmp.primary_component
FROM okl_assets_b ast,
okl_lease_quotes_b qte,
okl_asset_components_b cmp
WHERE ast.parent_object_code = 'LEASEQUOTE' AND
ast.parent_object_id = qte.id AND
qte.id = qteid AND
ast.id = astid AND
cmp.primary_component = 'YES' AND
cmp.asset_id = ast.id;
SELECT adj.adjustment_source_type,
adj.adjustment_source_id,
adj.basis,
-- Start : DJANASWA : Bug# 6347118
nvl(adj.value,adj.default_subsidy_amount) value
-- End : DJANASWA : Bug# 6347118
FROM okl_assets_b ast,
okl_lease_quotes_b qte,
okl_cost_adjustments_b adj
WHERE ast.parent_object_code = 'LEASEQUOTE' AND
ast.parent_object_id = qte.id AND
qte.id = qteid AND
ast.id = astid AND
adj.parent_object_id = ast.id;
Select amount, SUBSIDY_CALC_BASIS
-- Bug 6622178 : End
From okl_subsidies_b
where id = subId;
SELECT lopp.id parent_id
,lopp.prospect_id prospect_id
,lopp.cust_acct_id cust_acct_id
,lopp.sales_territory_id sales_territory_id
,lopp.currency_code currency_code
FROM okl_lease_quotes_b lq,
okl_lease_opportunities_b lopp
WHERE parent_object_code = 'LEASEOPP'
AND parent_object_id = lopp.id
AND lq.id = p_lq_id;
SELECT lapp.id parent_id
,lapp.prospect_id prospect_id
,lapp.cust_acct_id cust_acct_id
,lapp.sales_territory_id sales_territory_id
,lapp.currency_code currency_code
FROM okl_lease_quotes_b lq,
okl_lease_applications_b lapp
WHERE parent_object_code = 'LEASEAPP'
AND parent_object_id = lapp.id
AND lq.id = p_lq_id;
SELECT 'YES' cfo_exists,
cfo.id cfo_id,
caf.id caf_id
FROM OKL_CASH_FLOW_OBJECTS cfo,
OKL_CASH_FLOWS caf
WHERE OTY_CODE = p_oty_code
AND SOURCE_TABLE = p_source_table
AND SOURCE_ID = p_source_id
AND caf.cfo_id = cfo.id;
SELECT lq.id
,lq.reference_number
,eot.end_of_term_name
,eot.eot_type_code eot_type_code
,eot.end_of_term_id end_of_term_id
,eotversion.end_of_term_ver_id
FROM OKL_LEASE_QUOTES_B lq,
okl_fe_eo_term_vers eotversion,
okl_fe_eo_terms_all_b eot
WHERE lq.END_OF_TERM_OPTION_ID = eotversion.end_of_term_ver_id
AND eot.end_of_term_id = eotversion.end_of_term_id
AND lq.id = p_lq_id;
SELECT SUM(amount) capitalized_amount
FROM okl_line_relationships_v lre
WHERE source_line_type = p_source_type
AND related_line_type = 'CAPITALIZED'
AND source_line_id = p_source_id;
SELECT PDT.NAME PRODUCTNAME
FROM OKL_LEASE_QUOTES_B QTE
, OKL_PRODUCTS PDT
WHERE QTE.PRODUCT_ID = PDT.ID
AND QTE.ID = qteid;
SELECT SUM(AMOUNT),
COUNT(*)
FROM OKL_LINE_RELATIONSHIPS_B
WHERE SOURCE_LINE_TYPE = 'ASSET'
AND RELATED_LINE_TYPE = P_LINE_TYPE
AND RELATED_LINE_ID = P_FEE_SRVC_ID;
SELECT SOURCE_LINE_ID,
AMOUNT
FROM OKL_LINE_RELATIONSHIPS_B
WHERE SOURCE_LINE_TYPE = 'ASSET'
AND RELATED_LINE_TYPE = P_LINE_TYPE
AND RELATED_LINE_ID = P_FEE_SRVC_ID;
SELECT SOURCE_ID
FROM OKL_CASH_FLOW_OBJECTS
WHERE ID = P_CFO_ID;
delete_quote_streams(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_quote_id => p_qte_id
);
l_lq_payment_level_tbl(t_index).record_mode := 'UPDATE';
l_non_overiding_assets_tbl.DELETE;
okl_lease_quote_cashflow_pvt.delete_cashflows (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_source_object_code => 'LEASE_QUOTE',
p_source_object_id => p_qte_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After update_cashflow call ' || l_Return_Status );
l_fee_outflow_cfl_tbl.DELETE;
l_fee_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_cash_inflows.DELETE;
l_non_overiding_assets_tbl.DELETE;
okl_lease_quote_cashflow_pvt.delete_cashflows (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_source_object_code => 'LEASE_QUOTE',
p_source_object_id => p_qte_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After update_cashflow call ' || l_Return_Status );
l_fee_outflow_cfl_tbl.DELETE;
l_fee_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_cash_inflows.DELETE;
l_non_overiding_assets_tbl.DELETE;
l_ass_adj_tbl.DELETE;
okl_lease_quote_cashflow_pvt.delete_cashflows (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_source_object_code => 'LEASE_QUOTE',
p_source_object_id => p_qte_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After update_cashflow call ' || l_Return_Status );
l_fee_outflow_cfl_tbl.DELETE;
l_fee_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_cash_inflows.DELETE;
l_non_overiding_assets_tbl.DELETE;
l_pp_non_sub_iir_tbl.DELETE;
l_pp_non_sub_irr_tbl.DELETE;
l_fee_outflow_cfl_tbl.DELETE;
l_fee_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_cash_inflows.DELETE;
'Before OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow l_lq_payment_header_rec '
|| l_lq_payment_header_rec.stream_type_id
|| ' status_code ' || l_lq_payment_header_rec.status_code );
OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow (
p_api_version => G_API_VERSION,
p_init_msg_list => p_init_msg_list,
p_transaction_control => G_FALSE,
p_cashflow_header_rec => l_lq_payment_header_rec,
p_cashflow_level_tbl => l_lq_payment_level_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow ' || l_return_status );
'After OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow l_lq_payment_header_rec '
|| l_lq_payment_header_rec.stream_type_id
|| ' status_code ' || l_lq_payment_header_rec.status_code );
'Updated the Payment amount back in the Cash Inflows ');
l_fee_outflow_cfl_tbl.DELETE;
l_fee_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_cash_inflows.DELETE;
l_non_overiding_assets_tbl.DELETE;
l_fee_outflow_cfl_tbl.DELETE;
l_fee_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_cash_inflows.DELETE;
l_pp_non_sub_irr_tbl.DELETE;
'Update the Yields on the Lease Quote' );
okl_lease_quote_pvt.update_lease_qte(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_lease_qte_rec => l_lease_qte_rec,
x_lease_qte_rec => x_lease_qte_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
okl_lease_quote_cashflow_pvt.delete_cashflows (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_source_object_code => 'QUOTED_ASSET',
p_source_object_id => l_non_overiding_assets_tbl(t),
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_cash_inflows.DELETE;
l_noa_cash_flow_det_tbl.DELETE;
okl_lease_quote_cashflow_pvt.delete_cashflows (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_control => NULL,
p_source_object_code => 'LEASE_QUOTE_CONSOLIDATED',
p_source_object_id => p_qte_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Deleted the Consolidated Cash flow levels @ LQ levels. Status: ' || l_return_status );
l_srvc_inflow_cfl_tbl.DELETE;
l_cash_inflows.DELETE;
l_amount_tbl.DELETE;
l_amount_tbl.DELETE;
'Before Calling the API insert_quote_streams ');
insert_quote_streams(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_quote_id => p_qte_id,
p_quote_type => 'LEASE_QUOTE',
p_currency => l_currency,
p_pricing_param_tbl => l_pp_non_sub_irr_tbl);
'After Calling the API insert_quote_streams x_return_status= ' || x_return_status);