[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:
1842: IS
1843: l_amount NUMBER := 0;
1844:
1845: -- sjalasut, modified the below cursor to make khr_id referred from
1846: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
1847: -- as part of OKLR12B disbursements project
1848: CURSOR c (p_contract_id NUMBER)
1849: IS
1850: select nvl(sum(a.amount),0)
1848: CURSOR c (p_contract_id NUMBER)
1849: IS
1850: select nvl(sum(a.amount),0)
1851: from okl_trx_ap_invoices_b a
1852: ,okl_txl_ap_inv_lns_all_b b
1853: where a.id = b.tap_id
1854: and a.funding_type_code = 'PREFUNDING'
1855: and a.trx_status_code in ('APPROVED', 'PROCESSED')
1856: and a.amount < 0 -- adjustments
1856: and a.amount < 0 -- adjustments
1857: and b.khr_id = p_contract_id;
1858:
1859: -- sjalasut, modified the below cursor to make khr_id referred from
1860: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
1861: -- as part of OKLR12B disbursements project
1862: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
1863: IS
1864: select nvl(sum(a.amount),0)
1862: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
1863: IS
1864: select nvl(sum(a.amount),0)
1865: from okl_trx_ap_invoices_b a
1866: ,okl_txl_ap_inv_lns_all_b b
1867: where a.id = b.tap_id
1868: and a.funding_type_code = 'PREFUNDING'
1869: and a.trx_status_code in ('APPROVED', 'PROCESSED')
1870: and a.amount < 0 -- adjustments
2094:
2095: -- get approved amount for Asset
2096: -- sjalasut, made changes to the below cursor to have khr_id be referred
2097: -- from okl_txl_inv_lns_all_b instead of okl_trx_ap_invoices_b.
2098: -- also changed the from clause to okl_txl_ap_inv_lns_all_b
2099: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
2100: IS
2101: select nvl(sum(b.amount),0)
2102: from okl_trx_ap_invoices_b a,
2099: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
2100: IS
2101: select nvl(sum(b.amount),0)
2102: from okl_trx_ap_invoices_b a,
2103: okl_txl_ap_inv_lns_all_b b
2104: where a.id = b.tap_id
2105: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2106: and a.funding_type_code ='ASSET'
2107: and b.amount > 0 --?
2109: ;
2110:
2111: -- sjalasut, made changes to the below cursor to have khr_id be referred
2112: -- from okl_txl_inv_lns_all_b instead of okl_trx_ap_invoices_b.
2113: -- also changed the from clause to okl_txl_ap_inv_lns_all_b
2114: CURSOR c_tot_asset_fund_ven (p_contract_id NUMBER, p_vendor_site_id NUMBER)
2115: IS
2116: select nvl(sum(b.amount),0)
2117: from okl_trx_ap_invoices_b a,
2114: CURSOR c_tot_asset_fund_ven (p_contract_id NUMBER, p_vendor_site_id NUMBER)
2115: IS
2116: select nvl(sum(b.amount),0)
2117: from okl_trx_ap_invoices_b a,
2118: okl_txl_ap_inv_lns_all_b b
2119: where a.id = b.tap_id
2120: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2121: and a.funding_type_code ='ASSET'
2122: and b.amount > 0 --?
2195: l_amount NUMBER := 0;
2196:
2197: -- get approved amount for Expense by specific vendor
2198: -- sjalasut, modified the below cursor to have khr_id be referred from
2199: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices. changes made
2200: -- as part of OKLR12B disbursements project.
2201: CURSOR c_tot_expense_fund (p_contract_id NUMBER, p_vendor_site_id NUMBER)
2202: IS
2203: select nvl(sum(b.amount),0)
2241: l_amount NUMBER := 0;
2242:
2243: -- get approved amount for Expense
2244: -- sjalasut, made changes to the below cursor to have khr_id referred to
2245: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. change made
2246: -- as part of OKLR12B disbursements project.
2247: CURSOR c_tot_expense_fund (p_contract_id NUMBER)
2248: IS
2249: select nvl(sum(b.amount),0)
3195: -- check FA line
3196: -- OKL_FUNDING_PVT.get_contract_line_funded_amt(a.CHR_ID, a.CLE_ID)
3197: -- will return 0 if user has not been funded FA line yet
3198: -- sjalasut, modified the below cursor to have khr_id be referred from
3199: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
3200: -- as part of OKLR12B disbursements project
3201: CURSOR c_invalid_fund (p_fund_id NUMBER)
3202: IS
3203: SELECT
3237: ;
3238:
3239: -- get approved amount for Asset
3240: -- sjalasut, made changes to the below cursor to have khr_id be referred from
3241: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. change made
3242: -- as part of OKLR12B disbursements project.
3243: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
3244: IS
3245: select nvl(sum(b.amount),0)
3243: CURSOR c_tot_asset_fund (p_contract_id NUMBER)
3244: IS
3245: select nvl(sum(b.amount),0)
3246: from okl_trx_ap_invoices_b a,
3247: okl_txl_ap_inv_lns_all_b b
3248: where a.id = b.tap_id
3249: and a.trx_status_code in ('APPROVED', 'PROCESSED')
3250: and a.funding_type_code ='ASSET'
3251: and b.amount > 0 --?
3252: and b.khr_id = p_contract_id;
3253:
3254: -- get approved amount for Expense by specific vendor
3255: -- sjalasut, made changes to the below cursor to have khr_id be referred from
3256: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. change made
3257: -- as part of OKLR12B disbursements project.
3258: CURSOR c_tot_expense_fund (p_contract_id NUMBER, p_vendor_site_id NUMBER)
3259: IS
3260: select nvl(sum(b.amount),0)
3258: CURSOR c_tot_expense_fund (p_contract_id NUMBER, p_vendor_site_id NUMBER)
3259: IS
3260: select nvl(sum(b.amount),0)
3261: from okl_trx_ap_invoices_b a,
3262: okl_txl_ap_inv_lns_all_b b
3263: where a.id = b.tap_id
3264: and a.trx_status_code in ('APPROVED', 'PROCESSED')
3265: and a.funding_type_code ='EXPENSE'
3266: and b.amount > 0 --?
4150: and khr.deal_type = 'LOAN-REVOLVING';
4151:
4152: -- cklee 09-24-03
4153: -- sjalasut, modified the below cursor to have khr_id referred from
4154: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b.
4155: -- also not using okl_cnsld_ap_invoices_all as this cursor only checks
4156: -- for a pre-funding request.
4157: Cursor c_reverse_chk(p_contract_id number)
4158: is
4157: Cursor c_reverse_chk(p_contract_id number)
4158: is
4159: select 1
4160: from okl_trx_ap_invoices_b a
4161: ,okl_txl_ap_inv_lns_all_b b
4162: where a.id = b.tap_id
4163: and a.vendor_invoice_number = a.invoice_number
4164: and b.khr_id = p_contract_id;
4165:
5020: where name = p_name
5021: ;
5022:
5023: -- sjalasut, modified the cursor below to have khr_id referred from
5024: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b.
5025: -- changes made as part of OKLR12B disbursements project.
5026: --vpanwar added 28/02/2007 for provide khr_id from p_tplv_rec.khr_id ..start
5027: /*CURSOR c_funding_type (p_tap_id NUMBER)
5028: IS
5027: /*CURSOR c_funding_type (p_tap_id NUMBER)
5028: IS
5029: select tap.funding_type_code , tpl.khr_id
5030: from okl_trx_ap_invoices_b tap
5031: ,okl_txl_ap_inv_lns_all_b tpl
5032: where tap.id = p_tap_id
5033: and tap.id = tpl.tap_id;*/
5034:
5035: CURSOR c_funding_type (p_tap_id NUMBER)
5035: CURSOR c_funding_type (p_tap_id NUMBER)
5036: IS
5037: select tap.funding_type_code /*, tpl.khr_id*/
5038: from okl_trx_ap_invoices_b tap
5039: /*,okl_txl_ap_inv_lns_all_b tpl*/
5040: where tap.id = p_tap_id
5041: /*and tap.id = tpl.tap_id*/;
5042: --vpanwar added 28/02/2007 end
5043:
6795: where a.id = p_contract_id
6796: ;
6797:
6798: -- sjalasut, modified the below cursor to have p_contract_id joined with
6799: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
6800: -- as part of OKLR12B disbursements project
6801: CURSOR c2 (p_contract_id NUMBER)
6802: IS
6803: select a.ipvs_id,
6802: IS
6803: select a.ipvs_id,
6804: nvl(sum(OKL_FUNDING_PVT.get_contract_line_funded_amt(a.id,a.funding_type_code)),0)
6805: from okl_trx_ap_invoices_b a
6806: ,okl_txl_ap_inv_lns_all_b b
6807: where a.id = b.tap_id
6808: and b.khr_id = p_contract_id
6809: and a.trx_status_code in ('APPROVED', 'PROCESSED')
6810: and a.funding_type_code IS NOT NULL -- cklee 09-24-03
7538: IS
7539: l_amount NUMBER := 0;
7540:
7541: -- sjalasut, modified the cursor below to have khr_id referred from
7542: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7543: -- as part of OKLR12B disbursements project.
7544: CURSOR c (p_khr_id NUMBER,
7545: p_kle_id NUMBER)
7546: IS
7546: IS
7547:
7548: SELECT SUM(tl.amount)
7549: FROM okl_trx_ap_invoices_b th,
7550: okl_txl_ap_inv_lns_all_b tl
7551: WHERE th.id = tl.tap_id
7552: AND tl.khr_id = p_khr_id
7553: AND tl.kle_id = p_kle_id
7554: -- fixed bug 3007875
7554: -- fixed bug 3007875
7555: AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED');
7556:
7557: -- sjalasut, modified the cursor below to have khr_id referred from
7558: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7559: -- as part of OKLR12B disbursements project.
7560: CURSOR ct (p_khr_id NUMBER,
7561: p_kle_id NUMBER,
7562: p_funding_type_code VARCHAR2)
7562: p_funding_type_code VARCHAR2)
7563: IS
7564: SELECT SUM(tl.amount)
7565: FROM okl_trx_ap_invoices_b th,
7566: okl_txl_ap_inv_lns_all_b tl
7567: WHERE th.id = tl.tap_id
7568: AND tl.khr_id = p_khr_id
7569: AND tl.kle_id = p_kle_id
7570: AND th.funding_type_code = p_funding_type_code
7654: l_result VARCHAR2(1) := OKL_API.G_TRUE;
7655: l_dummy VARCHAR2(1) := '?';
7656:
7657: -- sjalasut, modified the cursor below to have khr_id referred from
7658: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7659: -- as part of OKLR12B disbursements project.
7660: CURSOR c (p_fund_number VARCHAR2,
7661: p_org_id number,
7662: p_vendor_id number)
7662: p_vendor_id number)
7663: IS
7664: SELECT 'X'
7665: FROM okl_trx_ap_invoices_b th,
7666: okl_txl_ap_inv_lns_all_b tl,
7667: okc_k_headers_b chr,
7668: PO_VENDOR_SITES_ALL VS
7669: WHERE th.id = tl.tap_id
7670: AND tl.khr_id = chr.id
7830: IS
7831: l_amount NUMBER := 0;
7832:
7833: -- sjalasut, modified the cursor below to have khr_id referred from
7834: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7835: -- as part of OKLR12B disbursements project.
7836: CURSOR c (p_contract_id NUMBER)
7837: IS
7838: select nvl(sum(a.amount),0)
7836: CURSOR c (p_contract_id NUMBER)
7837: IS
7838: select nvl(sum(a.amount),0)
7839: from okl_trx_ap_invoices_b a
7840: ,okl_txl_ap_inv_lns_all_b b
7841: where a.id = b.tap_id
7842: and a.funding_type_code = 'PREFUNDING'
7843: and a.trx_status_code in ('APPROVED', 'PROCESSED')
7844: and b.khr_id = p_contract_id
7846: and a.amount > 0
7847: ;
7848:
7849: -- sjalasut, modified the cursor below to have khr_id referred from
7850: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7851: -- as part of OKLR12B disbursements project.
7852: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
7853: IS
7854: select nvl(sum(a.amount),0)
7853: IS
7854: select nvl(sum(a.amount),0)
7855:
7856: from okl_trx_ap_invoices_b a
7857: ,okl_txl_ap_inv_lns_all_b b
7858: where a.id = b.tap_id
7859: and a.funding_type_code = 'PREFUNDING'
7860: and a.trx_status_code in ('APPROVED', 'PROCESSED')
7861: and b.khr_id = p_contract_id
7909: l_amount NUMBER := 0;
7910: x_amount NUMBER := 0;
7911:
7912: -- sjalasut, modified the cursor below to have khr_id referred from
7913: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7914: -- as part of OKLR12B disbursements project.
7915: CURSOR c (p_contract_id NUMBER)
7916: IS
7917: select nvl(sum(b.amount),0)
7915: CURSOR c (p_contract_id NUMBER)
7916: IS
7917: select nvl(sum(b.amount),0)
7918: from okl_trx_ap_invoices_b a,
7919: okl_txl_ap_inv_lns_all_b b
7920: where a.id = b.tap_id
7921: and a.trx_status_code in ('APPROVED', 'PROCESSED')
7922: and a.funding_type_code IN ('ASSET','EXPENSE', G_ASSET_SUBSIDY) -- cklee 11.5.10 subsidy
7923: and b.khr_id = p_contract_id
7923: and b.khr_id = p_contract_id
7924: UNION
7925: select nvl(sum(a.amount),0)
7926: from okl_trx_ap_invoices_b a
7927: ,okl_txl_ap_inv_lns_all_b b
7928: where a.id = b.tap_id
7929: and a.funding_type_code in ('PREFUNDING', 'BORROWER_PAYMENT') -- fixed bug# 2604862
7930: and a.trx_status_code in ('APPROVED', 'PROCESSED')
7931: and b.khr_id = p_contract_id
7972: IS
7973: l_amount NUMBER := 0;
7974:
7975: -- sjalasut, modified the cursor below to have khr_id referred from
7976: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
7977: -- as part of OKLR12B disbursements project.
7978: CURSOR c (p_contract_id NUMBER)
7979: IS
7980: select nvl(sum(b.amount),0)
7978: CURSOR c (p_contract_id NUMBER)
7979: IS
7980: select nvl(sum(b.amount),0)
7981: from okl_trx_ap_invoices_b a,
7982: okl_txl_ap_inv_lns_all_b b
7983: where a.id = b.tap_id
7984: and b.khr_id = p_contract_id
7985: and a.trx_status_code in ('APPROVED', 'PROCESSED')
7986: and a.funding_type_code = 'SUPPLIER_RETENTION';
8022: IS
8023: l_amount NUMBER := 0;
8024:
8025: -- sjalasut, modified the cursor below to have khr_id referred from
8026: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8027: -- as part of OKLR12B disbursements project.
8028: CURSOR c (p_contract_id NUMBER)
8029: IS
8030: select nvl(sum(a.amount),0)
8028: CURSOR c (p_contract_id NUMBER)
8029: IS
8030: select nvl(sum(a.amount),0)
8031: from okl_trx_ap_invoices_b a
8032: ,okl_txl_ap_inv_lns_all_b b
8033: where a.id = b.tap_id
8034: and b.khr_id = p_contract_id
8035: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8036: and a.funding_type_code = 'BORROWER_PAYMENT';
8074: IS
8075: l_amount NUMBER := 0;
8076:
8077: -- sjalasut, modified the cursor below to have khr_id referred from
8078: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8079: -- as part of OKLR12B disbursements project.
8080: CURSOR c (p_contract_id NUMBER)
8081: IS
8082: select nvl(sum(a.amount),0)
8080: CURSOR c (p_contract_id NUMBER)
8081: IS
8082: select nvl(sum(a.amount),0)
8083: from okl_trx_ap_invoices_b a
8084: ,okl_txl_ap_inv_lns_all_b b
8085: where a.id = b.tap_id
8086: and a.khr_id = p_contract_id
8087: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8088: and a.funding_type_code = G_ASSET_SUBSIDY;
8087: and a.trx_status_code in ('APPROVED', 'PROCESSED')
8088: and a.funding_type_code = G_ASSET_SUBSIDY;
8089:
8090: -- sjalasut, modified the cursor below to have khr_id referred from
8091: -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
8092: -- as part of OKLR12B disbursements project.
8093: CURSOR c_sub (p_contract_id NUMBER, p_contract_line_id NUMBER)
8094: IS
8095: select nvl(sum(subln.amount),0)
8093: CURSOR c_sub (p_contract_id NUMBER, p_contract_line_id NUMBER)
8094: IS
8095: select nvl(sum(subln.amount),0)
8096: from okl_trx_ap_invoices_b sub,
8097: okl_txl_ap_inv_lns_all_b subln
8098: where sub.id = subln.tap_id
8099: and subln.khr_id = p_contract_id
8100: and subln.kle_id = p_contract_line_id -- fixed asset ID
8101: and sub.trx_status_code in ('APPROVED', 'PROCESSED')