[Home] [Help]
112: -- End of comments
113: PROCEDURE create_contract_val(
114: p_api_version IN NUMBER,
115: p_init_msg_list IN VARCHAR2,
116: p_quote_id IN OKL_LEASE_QUOTES_B.ID%TYPE,
117: x_return_status OUT NOCOPY VARCHAR2,
118: x_msg_count OUT NOCOPY NUMBER,
119: x_msg_data OUT NOCOPY VARCHAR2)
120: IS
129: IS
130: SELECT LAB.REFERENCE_NUMBER LSE_APP
131: , CHR.CONTRACT_NUMBER CONTRACT_NUMBER
132: FROM OKL_LEASE_APPLICATIONS_B LAB
133: , OKL_LEASE_QUOTES_B QTE
134: , OKC_K_HEADERS_B CHR
135: , OKC_STATUSES_V CSTS
136: WHERE LAB.LEASE_OPPORTUNITY_ID = QTE.PARENT_OBJECT_ID
137: AND QTE.PARENT_OBJECT_CODE = 'LEASEOPP'
148: IS
149: SELECT LSQ.REFERENCE_NUMBER LSE_QTE
150: , LOP.REFERENCE_NUMBER LSE_OPP
151: , CHR.CONTRACT_NUMBER CONTRACT_NUMBER
152: FROM OKL_LEASE_QUOTES_B LSQ
153: , OKL_LEASE_OPPORTUNITIES_B LOP
154: , OKC_K_HEADERS_B CHR
155: , OKC_STATUSES_V CSTS
156: WHERE CHR.ORIG_SYSTEM_ID1 = LOP.ID
2036: l_exp_counter NUMBER := 0;
2037: l_payment_levels_tbl payment_levels_tbl_type;
2038: l_expense_levels_tbl payment_levels_tbl_type;
2039:
2040: CURSOR c_get_service_dtls(p_qte_id okl_lease_quotes_b.ID%TYPE) IS
2041: SELECT ID,
2042: INV_ITEM_ID,
2043: EFFECTIVE_FROM,
2044: SUPPLIER_ID
2365: l_exp_frequency VARCHAR2(1);
2366: l_pymnt_counter NUMBER := 0;
2367: l_payment_levels_tbl payment_levels_tbl_type;
2368:
2369: CURSOR c_get_fee_dtls(p_qte_id okl_lease_quotes_b.ID%TYPE) IS
2370: SELECT ID,
2371: STREAM_TYPE_ID,
2372: FEE_TYPE ,
2373: RATE_CARD_ID,
2962: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2963: l_debug_enabled VARCHAR2(10);
2964:
2965: -- Start : Added by rravikir for Bug#6707125
2966: CURSOR c_get_asset_id(p_qte_id okl_lease_quotes_b.ID%TYPE) IS
2967: SELECT oab.ID asset_id
2968: FROM okl_assets_v oab,
2969: okl_asset_components_b oacb
2970: WHERE oab.ID = oacb.ASSET_ID
3462: l_ti_desc OKL_COST_ADJUSTMENTS_V.description%TYPE;
3463:
3464: --Cursor declarations
3465: --Get asset line details
3466: CURSOR c_get_asset_dtls(p_qte_id okl_lease_quotes_b.ID%TYPE) IS
3467: SELECT oab.ID,
3468: oab.ASSET_NUMBER,
3469: oab.INSTALL_SITE_ID,
3470: oab.RATE_CARD_ID,
3546: AND cfo.OTY_CODE = 'QUOTED_ASSET_PROPERTY_TAX'
3547: AND cfo.SOURCE_ID = p_ast_id;
3548:
3549: --Get quote level payment details
3550: CURSOR c_get_quote_payment_dtls(p_qte_id okl_lease_quotes_b.ID%TYPE) IS
3551: SELECT ocf.STY_ID payment_type_id,
3552: ocf.DUE_ARREARS_YN Arrears_YN,
3553: cfl.FQY_CODE Frequency_Code,
3554: cfl.START_DATE,
3560: OKL_CASH_FLOWS ocf,
3561: OKL_CASH_FLOW_LEVELS cfl
3562: WHERE cfl.caf_id = ocf.ID
3563: AND ocf.CFO_ID = cfo.ID
3564: AND cfo.SOURCE_TABLE = 'OKL_LEASE_QUOTES_B'
3565: AND cfo.SOURCE_ID = p_qte_id;
3566:
3567: --get the item description
3568: CURSOR itm_dtls_csr(p_inv_itm_id IN NUMBER, p_organization_id IN NUMBER) IS
3572: AND TL.INVENTORY_ITEM_ID = p_inv_itm_id
3573: AND TL.LANGUAGE = USERENV('LANG');
3574:
3575: --get the eot type
3576: CURSOR c_get_eot_type(p_qte_id IN okl_lease_quotes_b.ID%TYPE) IS
3577: SELECT eoth.eot_type_code
3578: FROM OKL_FE_EO_TERM_VERS EOTV,
3579: OKL_FE_EO_TERMS_ALL_B EOTH,
3580: OKL_LEASE_QUOTES_B QTE
3576: CURSOR c_get_eot_type(p_qte_id IN okl_lease_quotes_b.ID%TYPE) IS
3577: SELECT eoth.eot_type_code
3578: FROM OKL_FE_EO_TERM_VERS EOTV,
3579: OKL_FE_EO_TERMS_ALL_B EOTH,
3580: OKL_LEASE_QUOTES_B QTE
3581: WHERE QTE.END_OF_TERM_OPTION_ID = EOTV.END_OF_TERM_VER_ID
3582: AND EOTV.END_OF_TERM_ID = EOTH.END_OF_TERM_ID
3583: AND QTE.ID = p_qte_id;
3584:
3582: AND EOTV.END_OF_TERM_ID = EOTH.END_OF_TERM_ID
3583: AND QTE.ID = p_qte_id;
3584:
3585: --cursor to fetch total trade in amount and description
3586: CURSOR get_ti_amt(p_qte_id IN okl_lease_quotes_b.ID%TYPE) IS
3587: SELECT sum(cdj.value) total_tradein
3588: FROM OKL_COST_ADJUSTMENTS_b cdj,
3589: OKL_ASSETS_B ast
3590: WHERE cdj.parent_object_id = ast.id
3593: AND ast.parent_object_id = p_qte_id
3594: AND ast.parent_object_code ='LEASEQUOTE';
3595:
3596: --cursor to fetch trade in description for any one asset
3597: CURSOR get_ti_desc(p_qte_id IN okl_lease_quotes_b.ID%TYPE) IS
3598: SELECT cdj.description
3599: FROM OKL_COST_ADJUSTMENTS_V cdj,
3600: OKL_ASSETS_B ast
3601: WHERE cdj.parent_object_id = ast.id
5050: WHERE contract_number like p_contract_number || '%';
5051:
5052: CURSOR c_get_leaseopp IS
5053: SELECT parent_object_id
5054: FROM okl_lease_quotes_b
5055: WHERE id = p_parent_object_id;
5056:
5057: BEGIN
5058: x_return_status := OKL_API.G_RET_STS_SUCCESS;