[Home] [Help]
169: l_tplv_tbl tplv_tbl_type;
170: -- smadhava - Bug#5200033 - Added - End
171:
172: -- required to create funding request for subsidy
173: -- sjalasut, modified the below cursor to have khr_id referred to okl_txl_ap_inv_lns_all_b
174: -- instead of okl_trx_ap_invoices_b. changes made as part of OKLR12B disbursements
175: -- project
176: CURSOR c_khr (p_contract_id NUMBER)
177: IS
184: tap.PAYMENT_METHOD_CODE,
185: -SUM(NVL(OKL_FUNDING_PVT.get_partial_subsidy_amount(tpl.KLE_ID, tpl.AMOUNT),0)) SUBSIDY_TOT_AMT
186: from okc_k_headers_b khr,
187: okl_trx_ap_invoices_b tap,
188: okl_txl_ap_inv_lns_all_b tpl
189: where khr.id = tpl.khr_id
190: and tap.id = tpl.tap_id
191: and tap.id = p_fund_id
192: and tap.funding_type_code = 'ASSET'
902: and g.dnz_chr_id = p_MLA_id
903: ;
904:
905: --4. Check if associated funding requests exist
906: -- sjalasut, modified the cursor to have khr_id referred from okl_txl_ap_inv_lns_all_b
907: -- instead of okl_trx_ap_invoices_b. changes made as part of OKLR12B disburesments
908: -- project.
909: cursor c_fund_req(p_chr_id NUMBER) is
910: select fr.id
908: -- project.
909: cursor c_fund_req(p_chr_id NUMBER) is
910: select fr.id
911: from OKL_TRX_AP_INVOICES_B fr
912: ,okl_txl_ap_inv_lns_all_b b
913: where fr.id = b.tap_id
914: and b.khr_id = p_chr_id
915: and fr.trx_status_code = 'ENTERED'
916: ;
926: --end modified abhsaxen for performance SQLID 20562295
927:
928:
929: --6. Check if associated approved funding requests exist
930: -- sjalasut, modified the cursor to have khr_id referred from okl_txl_ap_inv_lns_all_b
931: -- instead of okl_trx_ap_invoices_b. changes made as part of OKLR12B disburesments
932: -- project.
933: cursor c_approved_req(p_chr_id NUMBER) is
934: select 1
932: -- project.
933: cursor c_approved_req(p_chr_id NUMBER) is
934: select 1
935: from OKL_TRX_AP_INVOICES_B fr
936: ,okl_txl_ap_inv_lns_all_b b
937: where fr.id = b.tap_id
938: and b.khr_id = p_chr_id
939: and fr.trx_status_code in ('APPROVED','PROCESSED')
940: ;
1253:
1254: --end:| 21-May-2007 cklee OKLR12B Accounting CR |
1255:
1256: -- sjalasut, modified the below cursor to have khr_id being selected
1257: -- from okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b
1258: -- changes made as part of OKLR12B disbursements project
1259: CURSOR c (p_fund_id NUMBER)
1260: IS
1261: select b.khr_id,
1266: a.date_invoiced,
1267: --end:| 21-May-2007 cklee OKLR12B Accounting CR |
1268: a.org_id
1269: from okl_trx_ap_invoices_b a
1270: ,okl_txl_ap_inv_lns_all_b b
1271: where a.id = b.tap_id
1272: and a.id = p_fund_id;
1273:
1274: CURSOR pdt_c (p_chr_id NUMBER)
1385: CURSOR c_fund_lines (p_fund_id NUMBER) IS
1386: SELECT tpl.id,
1387: tpl.sty_id,
1388: tpl.amount
1389: FROM okl_txl_ap_inv_lns_all_b tpl
1390: WHERE tpl.tap_id = p_fund_id;
1391:
1392: l_date_invoiced okl_trx_ap_invs_all_b.date_invoiced%type;
1393: l_tap_amount okl_trx_ap_invs_all_b.amount%type;
1390: WHERE tpl.tap_id = p_fund_id;
1391:
1392: l_date_invoiced okl_trx_ap_invs_all_b.date_invoiced%type;
1393: l_tap_amount okl_trx_ap_invs_all_b.amount%type;
1394: -- l_fund_line_id okl_txl_ap_inv_lns_all_b.id%type;
1395: l_sty_id okl_txl_ap_inv_lns_all_b.sty_id%type;
1396: -- l_amount okl_txl_ap_inv_lns_all_b.amount%type;
1397: cnt NUMBER;
1398:
1391:
1392: l_date_invoiced okl_trx_ap_invs_all_b.date_invoiced%type;
1393: l_tap_amount okl_trx_ap_invs_all_b.amount%type;
1394: -- l_fund_line_id okl_txl_ap_inv_lns_all_b.id%type;
1395: l_sty_id okl_txl_ap_inv_lns_all_b.sty_id%type;
1396: -- l_amount okl_txl_ap_inv_lns_all_b.amount%type;
1397: cnt NUMBER;
1398:
1399: --end:| 21-May-2007 cklee OKLR12B Accounting CR |
1392: l_date_invoiced okl_trx_ap_invs_all_b.date_invoiced%type;
1393: l_tap_amount okl_trx_ap_invs_all_b.amount%type;
1394: -- l_fund_line_id okl_txl_ap_inv_lns_all_b.id%type;
1395: l_sty_id okl_txl_ap_inv_lns_all_b.sty_id%type;
1396: -- l_amount okl_txl_ap_inv_lns_all_b.amount%type;
1397: cnt NUMBER;
1398:
1399: --end:| 21-May-2007 cklee OKLR12B Accounting CR |
1400:
1864: IS
1865: l_amount NUMBER := 0;
1866:
1867: -- sjalasut, modified the below cursor to make khr_id referred from
1868: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
1869: -- as part of OKLR12B disbursements project
1870: CURSOR c (p_contract_id NUMBER)
1871: IS
1872: select nvl(sum(a.amount),0)
1870: CURSOR c (p_contract_id NUMBER)
1871: IS
1872: select nvl(sum(a.amount),0)
1873: from okl_trx_ap_invoices_b a
1874: ,okl_txl_ap_inv_lns_all_b b
1875: where a.id = b.tap_id
1876: and a.funding_type_code = 'PREFUNDING'
1877: and a.trx_status_code in ('APPROVED', 'PROCESSED')
1878: and a.amount < 0 -- adjustments
1878: and a.amount < 0 -- adjustments
1879: and b.khr_id = p_contract_id;
1880:
1881: -- sjalasut, modified the below cursor to make khr_id referred from
1882: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
1883: -- as part of OKLR12B disbursements project
1884: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
1885: IS
1886: select nvl(sum(a.amount),0)
1884: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
1885: IS
1886: select nvl(sum(a.amount),0)
1887: from okl_trx_ap_invoices_b a
1888: ,okl_txl_ap_inv_lns_all_b b
1889: where a.id = b.tap_id
1890: and a.funding_type_code = 'PREFUNDING'
1891: and a.trx_status_code in ('APPROVED', 'PROCESSED')
1892: and a.amount < 0 -- adjustments
2120:
2121: -- get approved amount for Asset
2122: -- sjalasut, made changes to the below cursor to have khr_id be referred
2123: -- from okl_txl_inv_lns_all_b instead of okl_trx_ap_invoices_b.
2124: -- also changed the from clause to okl_txl_ap_inv_lns_all_b
2125: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
2126: IS
2127: select nvl(sum(b.amount),0)
2128: from okl_trx_ap_invoices_b a,
2125: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
2126: IS
2127: select nvl(sum(b.amount),0)
2128: from okl_trx_ap_invoices_b a,
2129: okl_txl_ap_inv_lns_all_b b
2130: where a.id = b.tap_id
2131: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2132: and a.funding_type_code ='ASSET'
2133: and b.amount > 0 --?
2135: ;
2136:
2137: -- sjalasut, made changes to the below cursor to have khr_id be referred
2138: -- from okl_txl_inv_lns_all_b instead of okl_trx_ap_invoices_b.
2139: -- also changed the from clause to okl_txl_ap_inv_lns_all_b
2140: CURSOR c_tot_asset_fund_ven (p_contract_id NUMBER, p_vendor_site_id NUMBER)
2141: IS
2142: select nvl(sum(b.amount),0)
2143: from okl_trx_ap_invoices_b a,
2140: CURSOR c_tot_asset_fund_ven (p_contract_id NUMBER, p_vendor_site_id NUMBER)
2141: IS
2142: select nvl(sum(b.amount),0)
2143: from okl_trx_ap_invoices_b a,
2144: okl_txl_ap_inv_lns_all_b b
2145: where a.id = b.tap_id
2146: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2147: and a.funding_type_code ='ASSET'
2148: and b.amount > 0 --?
2221: l_amount NUMBER := 0;
2222:
2223: -- get approved amount for Expense by specific vendor
2224: -- sjalasut, modified the below cursor to have khr_id be referred from
2225: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices. changes made
2226: -- as part of OKLR12B disbursements project.
2227: CURSOR c_tot_expense_fund (p_contract_id NUMBER, p_vendor_site_id NUMBER)
2228: IS
2229: select nvl(sum(b.amount),0)
2267: l_amount NUMBER := 0;
2268:
2269: -- get approved amount for Expense
2270: -- sjalasut, made changes to the below cursor to have khr_id referred to
2271: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. change made
2272: -- as part of OKLR12B disbursements project.
2273: CURSOR c_tot_expense_fund (p_contract_id NUMBER)
2274: IS
2275: select nvl(sum(b.amount),0)
3325: -- check FA line
3326: -- OKL_FUNDING_PVT.get_contract_line_funded_amt(a.CHR_ID, a.CLE_ID)
3327: -- will return 0 if user has not been funded FA line yet
3328: -- sjalasut, modified the below cursor to have khr_id be referred from
3329: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
3330: -- as part of OKLR12B disbursements project
3331: CURSOR c_invalid_fund (p_fund_id NUMBER)
3332: IS
3333: SELECT
3367: ;
3368:
3369: -- get approved amount for Asset
3370: -- sjalasut, made changes to the below cursor to have khr_id be referred from
3371: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. change made
3372: -- as part of OKLR12B disbursements project.
3373: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
3374: IS
3375: select nvl(sum(b.amount),0)
3373: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
3374: IS
3375: select nvl(sum(b.amount),0)
3376: from okl_trx_ap_invoices_b a,
3377: okl_txl_ap_inv_lns_all_b b
3378: where a.id = b.tap_id
3379: and a.trx_status_code in ('APPROVED', 'PROCESSED')
3380: and a.funding_type_code ='ASSET'
3381: and b.amount > 0 --?
3382: and b.khr_id = p_contract_id;
3383:
3384: -- get approved amount for Expense by specific vendor
3385: -- sjalasut, made changes to the below cursor to have khr_id be referred from
3386: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. change made
3387: -- as part of OKLR12B disbursements project.
3388: CURSOR c_tot_expense_fund (p_contract_id NUMBER, p_vendor_site_id NUMBER)
3389: IS
3390: select nvl(sum(b.amount),0)
3388: CURSOR c_tot_expense_fund (p_contract_id NUMBER, p_vendor_site_id NUMBER)
3389: IS
3390: select nvl(sum(b.amount),0)
3391: from okl_trx_ap_invoices_b a,
3392: okl_txl_ap_inv_lns_all_b b
3393: where a.id = b.tap_id
3394: and a.trx_status_code in ('APPROVED', 'PROCESSED')
3395: and a.funding_type_code ='EXPENSE'
3396: and b.amount > 0 --?
4286: and khr.deal_type = 'LOAN-REVOLVING';
4287:
4288: -- cklee 09-24-03
4289: -- sjalasut, modified the below cursor to have khr_id referred from
4290: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b.
4291: -- also not using okl_cnsld_ap_invoices_all as this cursor only checks
4292: -- for a pre-funding request.
4293: Cursor c_reverse_chk(p_contract_id number)
4294: is
4293: Cursor c_reverse_chk(p_contract_id number)
4294: is
4295: select 1
4296: from okl_trx_ap_invoices_b a
4297: ,okl_txl_ap_inv_lns_all_b b
4298: where a.id = b.tap_id
4299: and a.vendor_invoice_number = a.invoice_number
4300: and b.khr_id = p_contract_id;
4301:
5157: where name = p_name
5158: ;
5159:
5160: -- sjalasut, modified the cursor below to have khr_id referred from
5161: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b.
5162: -- changes made as part of OKLR12B disbursements project.
5163: --vpanwar added 28/02/2007 for provide khr_id from p_tplv_rec.khr_id ..start
5164: /*CURSOR c_funding_type (p_tap_id NUMBER)
5165: IS
5164: /*CURSOR c_funding_type (p_tap_id NUMBER)
5165: IS
5166: select tap.funding_type_code , tpl.khr_id
5167: from okl_trx_ap_invoices_b tap
5168: ,okl_txl_ap_inv_lns_all_b tpl
5169: where tap.id = p_tap_id
5170: and tap.id = tpl.tap_id;*/
5171:
5172: CURSOR c_funding_type (p_tap_id NUMBER)
5172: CURSOR c_funding_type (p_tap_id NUMBER)
5173: IS
5174: select tap.funding_type_code /*, tpl.khr_id*/
5175: from okl_trx_ap_invoices_b tap
5176: /*,okl_txl_ap_inv_lns_all_b tpl*/
5177: where tap.id = p_tap_id
5178: /*and tap.id = tpl.tap_id*/;
5179: --vpanwar added 28/02/2007 end
5180:
6952: where a.id = p_contract_id
6953: ;
6954:
6955: -- sjalasut, modified the below cursor to have p_contract_id joined with
6956: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
6957: -- as part of OKLR12B disbursements project
6958: CURSOR c2 (p_contract_id NUMBER)
6959: IS
6960: select a.ipvs_id,
6959: IS
6960: select a.ipvs_id,
6961: nvl(sum(OKL_FUNDING_PVT.get_contract_line_funded_amt(a.id,a.funding_type_code)),0)
6962: from okl_trx_ap_invoices_b a
6963: ,okl_txl_ap_inv_lns_all_b b
6964: where a.id = b.tap_id
6965: and b.khr_id = p_contract_id
6966: and a.trx_status_code in ('APPROVED', 'PROCESSED')
6967: and a.funding_type_code IS NOT NULL -- cklee 09-24-03
7755: IS
7756: l_amount NUMBER := 0;
7757:
7758: -- sjalasut, modified the cursor below to have khr_id referred from
7759: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7760: -- as part of OKLR12B disbursements project.
7761: CURSOR c (p_khr_id NUMBER,
7762: p_kle_id NUMBER)
7763: IS
7763: IS
7764:
7765: SELECT SUM(tl.amount)
7766: FROM okl_trx_ap_invoices_b th,
7767: okl_txl_ap_inv_lns_all_b tl
7768: WHERE th.id = tl.tap_id
7769: AND tl.khr_id = p_khr_id
7770: AND tl.kle_id = p_kle_id
7771: -- fixed bug 3007875
7771: -- fixed bug 3007875
7772: AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED');
7773:
7774: -- sjalasut, modified the cursor below to have khr_id referred from
7775: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7776: -- as part of OKLR12B disbursements project.
7777: CURSOR ct (p_khr_id NUMBER,
7778: p_kle_id NUMBER,
7779: p_funding_type_code VARCHAR2)
7779: p_funding_type_code VARCHAR2)
7780: IS
7781: SELECT SUM(tl.amount)
7782: FROM okl_trx_ap_invoices_b th,
7783: okl_txl_ap_inv_lns_all_b tl
7784: WHERE th.id = tl.tap_id
7785: AND tl.khr_id = p_khr_id
7786: AND tl.kle_id = p_kle_id
7787: AND th.funding_type_code = p_funding_type_code
7871: l_result VARCHAR2(1) := OKL_API.G_TRUE;
7872: l_dummy VARCHAR2(1) := '?';
7873:
7874: -- sjalasut, modified the cursor below to have khr_id referred from
7875: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7876: -- as part of OKLR12B disbursements project.
7877: CURSOR c (p_fund_number VARCHAR2,
7878: p_org_id number,
7879: p_vendor_id number)
7879: p_vendor_id number)
7880: IS
7881: SELECT 'X'
7882: FROM okl_trx_ap_invoices_b th,
7883: okl_txl_ap_inv_lns_all_b tl,
7884: okc_k_headers_b chr,
7885: PO_VENDOR_SITES_ALL VS
7886: WHERE th.id = tl.tap_id
7887: AND tl.khr_id = chr.id
8047: IS
8048: l_amount NUMBER := 0;
8049:
8050: -- sjalasut, modified the cursor below to have khr_id referred from
8051: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8052: -- as part of OKLR12B disbursements project.
8053: CURSOR c (p_contract_id NUMBER)
8054: IS
8055: select nvl(sum(a.amount),0)
8053: CURSOR c (p_contract_id NUMBER)
8054: IS
8055: select nvl(sum(a.amount),0)
8056: from okl_trx_ap_invoices_b a
8057: ,okl_txl_ap_inv_lns_all_b b
8058: where a.id = b.tap_id
8059: and a.funding_type_code = 'PREFUNDING'
8060: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8061: and b.khr_id = p_contract_id
8063: and a.amount > 0
8064: ;
8065:
8066: -- sjalasut, modified the cursor below to have khr_id referred from
8067: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8068: -- as part of OKLR12B disbursements project.
8069: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
8070: IS
8071: select nvl(sum(a.amount),0)
8070: IS
8071: select nvl(sum(a.amount),0)
8072:
8073: from okl_trx_ap_invoices_b a
8074: ,okl_txl_ap_inv_lns_all_b b
8075: where a.id = b.tap_id
8076: and a.funding_type_code = 'PREFUNDING'
8077: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8078: and b.khr_id = p_contract_id
8126: l_amount NUMBER := 0;
8127: x_amount NUMBER := 0;
8128:
8129: -- sjalasut, modified the cursor below to have khr_id referred from
8130: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8131: -- as part of OKLR12B disbursements project.
8132: CURSOR c (p_contract_id NUMBER)
8133: IS
8134: select nvl(sum(b.amount),0)
8132: CURSOR c (p_contract_id NUMBER)
8133: IS
8134: select nvl(sum(b.amount),0)
8135: from okl_trx_ap_invoices_b a,
8136: okl_txl_ap_inv_lns_all_b b
8137: where a.id = b.tap_id
8138: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8139: and a.funding_type_code IN ('ASSET','EXPENSE', G_ASSET_SUBSIDY) -- cklee 11.5.10 subsidy
8140: and b.khr_id = p_contract_id
8140: and b.khr_id = p_contract_id
8141: UNION
8142: select nvl(sum(a.amount),0)
8143: from okl_trx_ap_invoices_b a
8144: ,okl_txl_ap_inv_lns_all_b b
8145: where a.id = b.tap_id
8146: and a.funding_type_code in ('PREFUNDING', 'BORROWER_PAYMENT') -- fixed bug# 2604862
8147: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8148: and b.khr_id = p_contract_id
8189: IS
8190: l_amount NUMBER := 0;
8191:
8192: -- sjalasut, modified the cursor below to have khr_id referred from
8193: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8194: -- as part of OKLR12B disbursements project.
8195: CURSOR c (p_contract_id NUMBER)
8196: IS
8197: select nvl(sum(b.amount),0)
8195: CURSOR c (p_contract_id NUMBER)
8196: IS
8197: select nvl(sum(b.amount),0)
8198: from okl_trx_ap_invoices_b a,
8199: okl_txl_ap_inv_lns_all_b b
8200: where a.id = b.tap_id
8201: and b.khr_id = p_contract_id
8202: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8203: and a.funding_type_code = 'SUPPLIER_RETENTION';
8239: IS
8240: l_amount NUMBER := 0;
8241:
8242: -- sjalasut, modified the cursor below to have khr_id referred from
8243: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8244: -- as part of OKLR12B disbursements project.
8245: CURSOR c (p_contract_id NUMBER)
8246: IS
8247: select nvl(sum(a.amount),0)
8245: CURSOR c (p_contract_id NUMBER)
8246: IS
8247: select nvl(sum(a.amount),0)
8248: from okl_trx_ap_invoices_b a
8249: ,okl_txl_ap_inv_lns_all_b b
8250: where a.id = b.tap_id
8251: and b.khr_id = p_contract_id
8252: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8253: and a.funding_type_code = 'BORROWER_PAYMENT';
8291: IS
8292: l_amount NUMBER := 0;
8293:
8294: -- sjalasut, modified the cursor below to have khr_id referred from
8295: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8296: -- as part of OKLR12B disbursements project.
8297: CURSOR c (p_contract_id NUMBER)
8298: IS
8299: select nvl(sum(a.amount),0)
8297: CURSOR c (p_contract_id NUMBER)
8298: IS
8299: select nvl(sum(a.amount),0)
8300: from okl_trx_ap_invoices_b a
8301: ,okl_txl_ap_inv_lns_all_b b
8302: where a.id = b.tap_id
8303: and a.khr_id = p_contract_id
8304: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8305: and a.funding_type_code = G_ASSET_SUBSIDY;
8304: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8305: and a.funding_type_code = G_ASSET_SUBSIDY;
8306:
8307: -- sjalasut, modified the cursor below to have khr_id referred from
8308: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8309: -- as part of OKLR12B disbursements project.
8310: CURSOR c_sub (p_contract_id NUMBER, p_contract_line_id NUMBER)
8311: IS
8312: select nvl(sum(subln.amount),0)
8310: CURSOR c_sub (p_contract_id NUMBER, p_contract_line_id NUMBER)
8311: IS
8312: select nvl(sum(subln.amount),0)
8313: from okl_trx_ap_invoices_b sub,
8314: okl_txl_ap_inv_lns_all_b subln
8315: where sub.id = subln.tap_id
8316: and subln.khr_id = p_contract_id
8317: and subln.kle_id = p_contract_line_id -- fixed asset ID
8318: and sub.trx_status_code in ('APPROVED', 'PROCESSED')