[Home] [Help]
111: x_return_status OUT NOCOPY VARCHAR2,
112: x_msg_count OUT NOCOPY NUMBER,
113: x_msg_data OUT NOCOPY VARCHAR2,
114: p_khr_id IN okc_k_headers_b.id%TYPE,
115: p_id IN OKL_TRX_AP_INVOICES_B.id%TYPE,
116: p_event_name IN wf_events.name%TYPE) IS
117:
118: l_parameter_list wf_parameter_list_t;
119: BEGIN
147: p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
148: x_return_status OUT NOCOPY VARCHAR2,
149: x_msg_count OUT NOCOPY NUMBER,
150: x_msg_data OUT NOCOPY VARCHAR2,
151: p_status IN OKL_TRX_AP_INVOICES_B.trx_status_code%TYPE,
152: p_fund_id IN OKL_TRX_AP_INVOICES_B.ID%TYPE)
153: IS
154:
155: l_api_name CONSTANT VARCHAR2(30) := 'create_fund_asset_subsidies';
148: x_return_status OUT NOCOPY VARCHAR2,
149: x_msg_count OUT NOCOPY NUMBER,
150: x_msg_data OUT NOCOPY VARCHAR2,
151: p_status IN OKL_TRX_AP_INVOICES_B.trx_status_code%TYPE,
152: p_fund_id IN OKL_TRX_AP_INVOICES_B.ID%TYPE)
153: IS
154:
155: l_api_name CONSTANT VARCHAR2(30) := 'create_fund_asset_subsidies';
156: l_api_version CONSTANT NUMBER := 1.0;
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
178: select khr.ID KHR_ID,
183: tap.IPVS_ID,
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
200: tap.PAYMENT_METHOD_CODE
201: ;
202:
203: -- assets request
204: cursor c_fund_asset(p_fund_id OKL_TRX_AP_INVOICES_B.ID%TYPE) is
205: select ast.KLE_ID,
206: ast.AMOUNT
207: from OKL_TXL_AP_INV_LNS_B ast
208: where ast.TAP_ID = p_fund_id;
484: ,x_return_status OUT NOCOPY VARCHAR2
485: ,x_msg_count OUT NOCOPY NUMBER
486: ,x_msg_data OUT NOCOPY VARCHAR2
487: ,p_chr_id IN okc_k_headers_b.id%type
488: ,p_fund_req_id IN okl_trx_ap_invoices_b.id%type
489: ,p_creditline_id IN okc_k_headers_b.id%type DEFAULT NULL
490: )
491: is
492: l_api_name CONSTANT VARCHAR2(30) := 'create_funding_chklst_tpl';
871: i NUMBER;
872: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
873: l_old_credit_id okc_k_headers_b.id%TYPE;
874: l_funded_amout NUMBER := 0;
875: l_fund_req_id okl_trx_ap_invoices_b.id%type;
876:
877: l_refresh_flag boolean := false;
878: l_rule_id okc_rules_b.id%type;
879: ldel_rulv_rec rulv_rec_type;
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
911: from OKL_TRX_AP_INVOICES_B fr
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
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'
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
935: from OKL_TRX_AP_INVOICES_B fr
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
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')
1192: p_init_msg_list IN VARCHAR2,
1193: x_return_status OUT NOCOPY VARCHAR2,
1194: x_msg_count OUT NOCOPY NUMBER,
1195: x_msg_data OUT NOCOPY VARCHAR2,
1196: p_status IN OKL_TRX_AP_INVOICES_B.trx_status_code%TYPE,
1197: --start:| 21-May-2007 cklee OKLR12B Accounting CR |
1198: p_fund_id IN OKL_TRX_AP_INVOICES_B.ID%TYPE ) IS --,
1199: /*
1200: -- cklee 11.5.10 subsidy
1194: x_msg_count OUT NOCOPY NUMBER,
1195: x_msg_data OUT NOCOPY VARCHAR2,
1196: p_status IN OKL_TRX_AP_INVOICES_B.trx_status_code%TYPE,
1197: --start:| 21-May-2007 cklee OKLR12B Accounting CR |
1198: p_fund_id IN OKL_TRX_AP_INVOICES_B.ID%TYPE ) IS --,
1199: /*
1200: -- cklee 11.5.10 subsidy
1201: p_fund_line_id IN OKL_TXL_AP_INV_LNS_B.ID%TYPE,
1202: p_subsidy_amt IN NUMBER,
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,
1265: a.amount,
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:
1689: ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl
1690: ,x_template_tbl => l_template_tbl
1691: ,x_amount_tbl => l_amount_tbl
1692: ,p_trx_header_id => p_fund_id
1693: ,p_trx_header_table => 'OKL_TRX_AP_INVOICES_B');
1694:
1695: IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1696: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1697: ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
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)
1869: -- as part of OKLR12B disbursements project
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
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)
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)
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')
2119: l_amount NUMBER := 0;
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)
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,
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'
2134: and b.khr_id = p_contract_id
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)
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,
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'
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)
2230: from okl_trx_ap_invoices_b a,
2231: okl_txl_ap_inv_lns_b b
2232: where a.id = b.tap_id
2233: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2234: and a.funding_type_code ='EXPENSE'
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)
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)
2276: from okl_trx_ap_invoices_b a,
2277: okl_txl_ap_inv_lns_b b
2278: where a.id = b.tap_id
2279: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2280: and a.funding_type_code ='EXPENSE'
3001:
3002: ---------------------------------------------------------------------------------------------------------
3003: -- 3. check funding checklist if funding checklist has not been setup
3004: ---------------------------------------------------------------------------------------------------------
3005: CURSOR c_chklst_chk(p_req_id okl_trx_ap_invoices_b.id%type)
3006: IS
3007: select 1
3008: from okl_funding_checklists_uv chk
3009: where fund_req_id = TO_CHAR(p_req_id) -- cklee: 11/04/2004
3012: ---------------------------------------------------------------------------------------------------------
3013: -- 4. check checklist required items
3014: ---------------------------------------------------------------------------------------------------------
3015:
3016: CURSOR c_chklst (p_chr_id okc_k_headers_b.id%type, p_fund_req_id okl_trx_ap_invoices_b.id%type)
3017: IS
3018: select 1
3019: from okc_rules_b rult
3020: where rult.rule_information_category = G_FUNDING_CHKLST_TPL_RULE1--'LAFCLD'
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
3332: IS
3333: SELECT
3334: nvl(a.ASSET_NUMBER,'X') ASSET_NUMBER, a.CHR_ID, a.CLE_ID, b.ipvs_id
3335: FROM okl_assets_lov_uv a,
3336: okl_trx_ap_invoices_b b
3337: WHERE a.chr_id = b.khr_id
3338: and b.id = p_fund_id;
3339: /*bug#5600694 veramach 29-Jun-2007
3340: commented and changed the cursor as above to improve the performance
3341: SELECT
3342: nvl(a.ASSET_NUMBER,'X')
3343: FROM okl_assets_lov_uv a,
3344: --START:| 13-Apr-2006 cklee -- Fixed bug#5160342 |
3345: okl_trx_ap_invoices_b b,
3346: OKL_TXL_AP_INV_LNS_V c
3347: WHERE a.chr_id = c.khr_id
3348: and b.id = c.TAP_ID
3349: and a.cle_id = c.kle_id
3358:
3359: CURSOR c_curr (p_fund_id NUMBER)
3360: IS
3361: select nvl(sum(b.amount),0)
3362: from okl_trx_ap_invoices_b a,
3363: okl_txl_ap_inv_lns_b b
3364: where a.id = b.tap_id
3365: and b.tap_id = p_fund_id
3366: and a.trx_status_code IN ('ENTERED','SUBMITTED')
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)
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)
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'
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)
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)
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'
4258: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4259: l_loan_rev NUMBER := 0;
4260: l_loan_row_found boolean := false;
4261: l_prefunding_eligible_yn okl_k_headers.PREFUNDING_ELIGIBLE_YN%type;
4262: l_trx_status_code okl_trx_ap_invoices_b.trx_status_code%type;
4263: l_reverse_row_notfound boolean := false;
4264: l_dummy number;
4265:
4266: CURSOR c_prefund (p_contract_id NUMBER)
4272:
4273: CURSOR c_curr_trx_sts (p_req_id NUMBER)
4274: IS
4275: select trx_status_code
4276: from OKL_TRX_AP_INVOICES_B
4277: where id = p_req_id
4278: ;
4279:
4280:
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
4292: -- for a pre-funding request.
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;
4579:
4580: CURSOR c (p_tap_id NUMBER)
4581: IS
4582: SELECT 'X'
4583: FROM OKL_TRX_AP_INVOICES_B
4584: WHERE id = p_tap_id
4585: AND funding_type_code = 'ASSET'
4586: ;
4587: BEGIN
4661:
4662: IS
4663: SELECT 'X'
4664:
4665: FROM OKL_TRX_AP_INVOICES_B
4666: WHERE id = p_tap_id
4667: AND funding_type_code = 'ASSET'
4668: ;
4669: BEGIN
4720:
4721: CURSOR c (p_tap_id NUMBER)
4722: IS
4723: SELECT 'X'
4724: FROM OKL_TRX_AP_INVOICES_B
4725: WHERE id = p_tap_id
4726: AND funding_type_code in ('SUPPLIER_RETENTION', 'EXPENSE')
4727: ;
4728: BEGIN
4783: l_results_amount NUMBER := 0;
4784: l_message_name VARCHAR2(30);
4785: --start: cklee 3/01/07 added invoice type and amount sign check at line level
4786:
4787: l_invoice_type okl_trx_ap_invoices_b.invoice_type%type;
4788: cursor c_invoice_type (p_tap_id number)is
4789: select invoice_type
4790: from okl_trx_ap_invoices_b
4791: where id = p_tap_id;
4786:
4787: l_invoice_type okl_trx_ap_invoices_b.invoice_type%type;
4788: cursor c_invoice_type (p_tap_id number)is
4789: select invoice_type
4790: from okl_trx_ap_invoices_b
4791: where id = p_tap_id;
4792:
4793: --end: cklee 3/01/07 added invoice type and amount sign check at line level
4794:
5147: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5148: l_stream_id NUMBER;
5149: l_chr_id NUMBER;
5150: l_name VARCHAR2(30);
5151: l_funding_type_code okl_trx_ap_invoices_b.funding_type_code%TYPE;
5152:
5153: CURSOR stream_c(p_name VARCHAR2)
5154: IS
5155: SELECT id
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
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
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:
5171:
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
5272: l_tapv_rec tapv_rec_type;
5273: x_tapv_rec tapv_rec_type;
5274: j BINARY_INTEGER;
5275: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5276: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5277: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5273: x_tapv_rec tapv_rec_type;
5274: j BINARY_INTEGER;
5275: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5276: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5277: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5281: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5274: j BINARY_INTEGER;
5275: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5276: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5277: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5281: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5282: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5275: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5276: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5277: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5281: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5282: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5283:
5276: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5277: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5281: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5282: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5283:
5284: CURSOR c (p_id NUMBER)
5277: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5281: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5282: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5283:
5284: CURSOR c (p_id NUMBER)
5285: IS
5278: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5279: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5280: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5281: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5282: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5283:
5284: CURSOR c (p_id NUMBER)
5285: IS
5286: SELECT h.id,
5288: h.VENDOR_INVOICE_NUMBER,
5289: h.PAY_GROUP_LOOKUP_CODE,
5290: h.NETTABLE_YN,
5291: h.INVOICE_TYPE
5292: FROM OKL_TRX_AP_INVOICES_B h
5293: WHERE h.id = p_id
5294: ;
5295:
5296: BEGIN
6089: i NUMBER;
6090: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6091:
6092: -- smadhava - Bug#5200033 - Added - Start
6093: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6094:
6095: --- vpanwar added 28/02/2007 start
6096: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6097: --- vpanwar added 28/02/2007 end
6092: -- smadhava - Bug#5200033 - Added - Start
6093: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6094:
6095: --- vpanwar added 28/02/2007 start
6096: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6097: --- vpanwar added 28/02/2007 end
6098:
6099: -- Cursor to get the currency code from the header record
6100: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6096: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6097: --- vpanwar added 28/02/2007 end
6098:
6099: -- Cursor to get the currency code from the header record
6100: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6101: SELECT CURRENCY_CODE
6102: FROM OKL_TRX_AP_INVOICES_B
6103: WHERE ID = cp_tap_id;
6104: -- smadhava - Bug#5200033 - Added - End
6098:
6099: -- Cursor to get the currency code from the header record
6100: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6101: SELECT CURRENCY_CODE
6102: FROM OKL_TRX_AP_INVOICES_B
6103: WHERE ID = cp_tap_id;
6104: -- smadhava - Bug#5200033 - Added - End
6105:
6106: -- vpanwar Added --28/02/2007 -start
6103: WHERE ID = cp_tap_id;
6104: -- smadhava - Bug#5200033 - Added - End
6105:
6106: -- vpanwar Added --28/02/2007 -start
6107: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6108: SELECT KHR_ID
6109: FROM OKL_TRX_AP_INVOICES_B
6110: WHERE ID = p_tap_id;
6111: -- vpanwar Added --28/02/2007 -end
6105:
6106: -- vpanwar Added --28/02/2007 -start
6107: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6108: SELECT KHR_ID
6109: FROM OKL_TRX_AP_INVOICES_B
6110: WHERE ID = p_tap_id;
6111: -- vpanwar Added --28/02/2007 -end
6112:
6113: -- dcshanmu added - 23-Nov-2007 - bug # 6639928 - start
6110: WHERE ID = p_tap_id;
6111: -- vpanwar Added --28/02/2007 -end
6112:
6113: -- dcshanmu added - 23-Nov-2007 - bug # 6639928 - start
6114: CURSOR c_get_max_line_number(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6115: SELECT MAX(LINE_NUMBER)
6116: FROM OKL_TXL_AP_INV_LNS_B
6117: WHERE TAP_ID = P_TAP_ID;
6118:
6329: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_FUNDING_LINES';
6330:
6331: l_api_version CONSTANT NUMBER := 1.0;
6332: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6333: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6334: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6335: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6336:
6337: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6330:
6331: l_api_version CONSTANT NUMBER := 1.0;
6332: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6333: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6334: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6335: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6336:
6337: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6338: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6331: l_api_version CONSTANT NUMBER := 1.0;
6332: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6333: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6334: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6335: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6336:
6337: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6338: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6339: p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6334: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6335: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6336:
6337: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6338: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6339: p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6340: SELECT a.cle_id cle_id,
6341: a.chr_id chr_id,
6342: a.asset_number kle_num,
6335: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6336:
6337: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6338: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6339: p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6340: SELECT a.cle_id cle_id,
6341: a.chr_id chr_id,
6342: a.asset_number kle_num,
6343: a.description kle_name,
6360: AND cpl.cle_id = LN.ID
6361: AND LN.cle_id = a.cle_id;
6362:
6363: -- cursor to fetch org_id from khr_id
6364: CURSOR c_get_org_id(p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6365: SELECT AUTHORING_ORG_ID
6366: FROM OKC_K_HEADERS_ALL_B
6367: WHERE ID = p_khr_id;
6368:
6478: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6479: l_sty_id number;
6480:
6481: --- vpanwar added 28/02/2007 start
6482: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6483: --- vpanwar added 28/02/2007 end
6484:
6485: cursor c_sty_id(p_tpl_id number) is
6486: select sty_id
6488: where id = p_tpl_id
6489: ;
6490:
6491: -- smadhava - Bug#5200033 - Added - Start
6492: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6493:
6494: -- Cursor to get the currency code from the header record
6495: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6496: SELECT CURRENCY_CODE
6491: -- smadhava - Bug#5200033 - Added - Start
6492: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6493:
6494: -- Cursor to get the currency code from the header record
6495: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6496: SELECT CURRENCY_CODE
6497: FROM OKL_TRX_AP_INVOICES_B
6498: WHERE ID = cp_tap_id;
6499: -- smadhava - Bug#5200033 - Added - End
6493:
6494: -- Cursor to get the currency code from the header record
6495: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6496: SELECT CURRENCY_CODE
6497: FROM OKL_TRX_AP_INVOICES_B
6498: WHERE ID = cp_tap_id;
6499: -- smadhava - Bug#5200033 - Added - End
6500:
6501: -- vpanwar Added --28/02/2007 -start
6498: WHERE ID = cp_tap_id;
6499: -- smadhava - Bug#5200033 - Added - End
6500:
6501: -- vpanwar Added --28/02/2007 -start
6502: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6503: SELECT KHR_ID
6504: FROM OKL_TRX_AP_INVOICES_B
6505: WHERE ID = p_tap_id;
6506: -- vpanwar Added --28/02/2007 -end
6500:
6501: -- vpanwar Added --28/02/2007 -start
6502: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6503: SELECT KHR_ID
6504: FROM OKL_TRX_AP_INVOICES_B
6505: WHERE ID = p_tap_id;
6506: -- vpanwar Added --28/02/2007 -end
6507:
6508: BEGIN
6676: l_asset_number VARCHAR2(150);
6677:
6678:
6679: -- smadhava - Bug#5200033 - Modified - Start
6680: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6681:
6682: --- vpanwar added 28/02/2007 start
6683: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6684: --- vpanwar added 28/02/2007 end
6679: -- smadhava - Bug#5200033 - Modified - Start
6680: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6681:
6682: --- vpanwar added 28/02/2007 start
6683: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6684: --- vpanwar added 28/02/2007 end
6685:
6686:
6687: -- Modifed cursor to get the currency code
6688: CURSOR c (p_fund_id NUMBER)
6689: IS
6690: select a.org_id
6691: , a.currency_code
6692: from okl_trx_ap_invoices_b a
6693: where a.id = p_fund_id
6694: and a.funding_type_code = 'ASSET'
6695: ;
6696: -- smadhava - Bug#5200033 - Modified - End
6718: --(
6719: select a.cle_id,
6720: (select OKL_FUNDING_PVT.get_contract_line_amt(a.CHR_ID, a.CLE_ID, b.ipvs_id) from dual) KLE_AMT
6721: from OKL_ASSETS_LOV_UV A,
6722: OKL_TRX_AP_INVOICES_B b
6723: WHERE a.chr_id = b.khr_id
6724: AND b.ID = p_fund_id
6725: and NOT EXISTS
6726: (select 1
6732: ;
6733: --veramach 5600694 end
6734:
6735: -- vpanwar Added --28/02/2007 -start
6736: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6737: SELECT KHR_ID
6738: FROM OKL_TRX_AP_INVOICES_B
6739: WHERE ID = p_tap_id;
6740: -- vpanwar Added --28/02/2007 -end
6734:
6735: -- vpanwar Added --28/02/2007 -start
6736: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6737: SELECT KHR_ID
6738: FROM OKL_TRX_AP_INVOICES_B
6739: WHERE ID = p_tap_id;
6740: -- vpanwar Added --28/02/2007 -end
6741:
6742:
6924: l_currency_code okc_k_headers_b.CURRENCY_CODE%TYPE;
6925: l_org_id okc_k_headers_b.AUTHORING_ORG_ID%TYPE;
6926: l_contract_number okc_k_headers_b.CONTRACT_NUMBER%TYPE;
6927:
6928: l_amount okl_trx_ap_invoices_b.AMOUNT%TYPE;
6929:
6930: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6931: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6932:
6926: l_contract_number okc_k_headers_b.CONTRACT_NUMBER%TYPE;
6927:
6928: l_amount okl_trx_ap_invoices_b.AMOUNT%TYPE;
6929:
6930: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6931: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6932:
6933: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
6934: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
6929:
6930: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6931: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6932:
6933: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
6934: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
6935:
6936:
6937: CURSOR cu (p_id NUMBER)
6930: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6931: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6932:
6933: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
6934: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
6935:
6936:
6937: CURSOR cu (p_id NUMBER)
6938: IS
6937: CURSOR cu (p_id NUMBER)
6938: IS
6939: SELECT h.PAY_GROUP_LOOKUP_CODE,
6940: h.NETTABLE_YN
6941: FROM OKL_TRX_AP_INVOICES_B h
6942: WHERE h.id = p_id
6943: ;
6944:
6945: --
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,
6958: CURSOR c2 (p_contract_id NUMBER)
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')
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
7762: p_kle_id NUMBER)
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
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)
7778: p_kle_id NUMBER,
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
7815: l_amount NUMBER := 0;
7816: CURSOR c (p_fund_id NUMBER)
7817: IS
7818: SELECT nvl(SUM(tl.amount),0)
7819: FROM okl_trx_ap_invoices_b th,
7820: okl_txl_ap_inv_lns_b tl
7821: WHERE tl.tap_id = th.id
7822: AND th.id = p_fund_id
7823: -- no need for this function. this is used for display at UI site only
7825: ;
7826: CURSOR c2 (p_fund_id NUMBER)
7827: IS
7828: SELECT nvl(th.amount,0)
7829: FROM okl_trx_ap_invoices_b th
7830: WHERE th.id = p_fund_id
7831: -- no need for this function. this is used for display at UI site only
7832: -- AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED')
7833: ;
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)
7878: p_org_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
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)
8052: -- as part of OKLR12B disbursements project.
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')
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)
8069: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
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')
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)
8131: -- as part of OKLR12B disbursements project.
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
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
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')
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)
8194: -- as part of OKLR12B disbursements project.
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')
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)
8244: -- as part of OKLR12B disbursements project.
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')
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)
8296: -- as part of OKLR12B disbursements project.
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')
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)
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)
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
8505: IS
8506: -- select nvl(sum(decode(sub.invoice_type, 'CREDIT', -subln.amount, subln.amount)),0)
8507: -- sjalasut, commented the above select as part of OKLR12B disbursements project
8508: select nvl(sum(subln.amount),0)
8509: from okl_trx_ap_invoices_b sub,
8510: okl_txl_ap_inv_lns_b subln
8511: where sub.id = subln.tap_id
8512: and sub.trx_status_code in ('APPROVED', 'PROCESSED')
8513: and sub.funding_type_code = G_MANUAL_DISB