[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
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)
1847: -- as part of OKLR12B disbursements project
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
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)
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)
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')
2093: l_amount NUMBER := 0;
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)
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,
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'
2108: and b.khr_id = p_contract_id
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)
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,
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'
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)
2204: from okl_trx_ap_invoices_b a,
2205: okl_txl_ap_inv_lns_b b
2206: where a.id = b.tap_id
2207: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2208: and a.funding_type_code ='EXPENSE'
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)
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)
2250: from okl_trx_ap_invoices_b a,
2251: okl_txl_ap_inv_lns_b b
2252: where a.id = b.tap_id
2253: and a.trx_status_code in ('APPROVED', 'PROCESSED')
2254: and a.funding_type_code ='EXPENSE'
2871:
2872: ---------------------------------------------------------------------------------------------------------
2873: -- 3. check funding checklist if funding checklist has not been setup
2874: ---------------------------------------------------------------------------------------------------------
2875: CURSOR c_chklst_chk(p_req_id okl_trx_ap_invoices_b.id%type)
2876: IS
2877: select 1
2878: from okl_funding_checklists_uv chk
2879: where fund_req_id = TO_CHAR(p_req_id) -- cklee: 11/04/2004
2882: ---------------------------------------------------------------------------------------------------------
2883: -- 4. check checklist required items
2884: ---------------------------------------------------------------------------------------------------------
2885:
2886: CURSOR c_chklst (p_chr_id okc_k_headers_b.id%type, p_fund_req_id okl_trx_ap_invoices_b.id%type)
2887: IS
2888: select 1
2889: from okc_rules_b rult
2890: where rult.rule_information_category = G_FUNDING_CHKLST_TPL_RULE1--'LAFCLD'
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
3202: IS
3203: SELECT
3204: nvl(a.ASSET_NUMBER,'X') ASSET_NUMBER, a.CHR_ID, a.CLE_ID, b.ipvs_id
3205: FROM okl_assets_lov_uv a,
3206: okl_trx_ap_invoices_b b
3207: WHERE a.chr_id = b.khr_id
3208: and b.id = p_fund_id;
3209: /*bug#5600694 veramach 29-Jun-2007
3210: commented and changed the cursor as above to improve the performance
3211: SELECT
3212: nvl(a.ASSET_NUMBER,'X')
3213: FROM okl_assets_lov_uv a,
3214: --START:| 13-Apr-2006 cklee -- Fixed bug#5160342 |
3215: okl_trx_ap_invoices_b b,
3216: OKL_TXL_AP_INV_LNS_V c
3217: WHERE a.chr_id = c.khr_id
3218: and b.id = c.TAP_ID
3219: and a.cle_id = c.kle_id
3228:
3229: CURSOR c_curr (p_fund_id NUMBER)
3230: IS
3231: select nvl(sum(b.amount),0)
3232: from okl_trx_ap_invoices_b a,
3233: okl_txl_ap_inv_lns_b b
3234: where a.id = b.tap_id
3235: and b.tap_id = p_fund_id
3236: and a.trx_status_code IN ('ENTERED','SUBMITTED')
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)
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)
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'
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)
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)
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'
4122: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4123: l_loan_rev NUMBER := 0;
4124: l_loan_row_found boolean := false;
4125: l_prefunding_eligible_yn okl_k_headers.PREFUNDING_ELIGIBLE_YN%type;
4126: l_trx_status_code okl_trx_ap_invoices_b.trx_status_code%type;
4127: l_reverse_row_notfound boolean := false;
4128: l_dummy number;
4129:
4130: CURSOR c_prefund (p_contract_id NUMBER)
4136:
4137: CURSOR c_curr_trx_sts (p_req_id NUMBER)
4138: IS
4139: select trx_status_code
4140: from OKL_TRX_AP_INVOICES_B
4141: where id = p_req_id
4142: ;
4143:
4144:
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
4156: -- for a pre-funding request.
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;
4443:
4444: CURSOR c (p_tap_id NUMBER)
4445: IS
4446: SELECT 'X'
4447: FROM OKL_TRX_AP_INVOICES_B
4448: WHERE id = p_tap_id
4449: AND funding_type_code = 'ASSET'
4450: ;
4451: BEGIN
4525:
4526: IS
4527: SELECT 'X'
4528:
4529: FROM OKL_TRX_AP_INVOICES_B
4530: WHERE id = p_tap_id
4531: AND funding_type_code = 'ASSET'
4532: ;
4533: BEGIN
4584:
4585: CURSOR c (p_tap_id NUMBER)
4586: IS
4587: SELECT 'X'
4588: FROM OKL_TRX_AP_INVOICES_B
4589: WHERE id = p_tap_id
4590: AND funding_type_code in ('SUPPLIER_RETENTION', 'EXPENSE')
4591: ;
4592: BEGIN
4647: l_results_amount NUMBER := 0;
4648: l_message_name VARCHAR2(30);
4649: --start: cklee 3/01/07 added invoice type and amount sign check at line level
4650:
4651: l_invoice_type okl_trx_ap_invoices_b.invoice_type%type;
4652: cursor c_invoice_type (p_tap_id number)is
4653: select invoice_type
4654: from okl_trx_ap_invoices_b
4655: where id = p_tap_id;
4650:
4651: l_invoice_type okl_trx_ap_invoices_b.invoice_type%type;
4652: cursor c_invoice_type (p_tap_id number)is
4653: select invoice_type
4654: from okl_trx_ap_invoices_b
4655: where id = p_tap_id;
4656:
4657: --end: cklee 3/01/07 added invoice type and amount sign check at line level
4658:
5010: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5011: l_stream_id NUMBER;
5012: l_chr_id NUMBER;
5013: l_name VARCHAR2(30);
5014: l_funding_type_code okl_trx_ap_invoices_b.funding_type_code%TYPE;
5015:
5016: CURSOR stream_c(p_name VARCHAR2)
5017: IS
5018: SELECT id
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
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
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:
5034:
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
5135: l_tapv_rec tapv_rec_type;
5136: x_tapv_rec tapv_rec_type;
5137: j BINARY_INTEGER;
5138: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5139: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5140: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5136: x_tapv_rec tapv_rec_type;
5137: j BINARY_INTEGER;
5138: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5139: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5140: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5144: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5137: j BINARY_INTEGER;
5138: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5139: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5140: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5144: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5145: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5138: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5139: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5140: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5144: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5145: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5146:
5139: l_id OKL_TRX_AP_INVOICES_B.ID%TYPE;
5140: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5144: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5145: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5146:
5147: CURSOR c (p_id NUMBER)
5140: l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE;
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5144: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5145: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5146:
5147: CURSOR c (p_id NUMBER)
5148: IS
5141: l_VENDOR_INVOICE_NUMBER OKL_TRX_AP_INVOICES_B.VENDOR_INVOICE_NUMBER%TYPE;
5142: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
5143: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
5144: l_amount OKL_TRX_AP_INVOICES_B.AMOUNT%TYPE := 0;
5145: l_INVOICE_TYPE OKL_TRX_AP_INVOICES_B.INVOICE_TYPE%TYPE;
5146:
5147: CURSOR c (p_id NUMBER)
5148: IS
5149: SELECT h.id,
5151: h.VENDOR_INVOICE_NUMBER,
5152: h.PAY_GROUP_LOOKUP_CODE,
5153: h.NETTABLE_YN,
5154: h.INVOICE_TYPE
5155: FROM OKL_TRX_AP_INVOICES_B h
5156: WHERE h.id = p_id
5157: ;
5158:
5159: BEGIN
5932: i NUMBER;
5933: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5934:
5935: -- smadhava - Bug#5200033 - Added - Start
5936: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
5937:
5938: --- vpanwar added 28/02/2007 start
5939: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
5940: --- vpanwar added 28/02/2007 end
5935: -- smadhava - Bug#5200033 - Added - Start
5936: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
5937:
5938: --- vpanwar added 28/02/2007 start
5939: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
5940: --- vpanwar added 28/02/2007 end
5941:
5942: -- Cursor to get the currency code from the header record
5943: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
5939: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
5940: --- vpanwar added 28/02/2007 end
5941:
5942: -- Cursor to get the currency code from the header record
5943: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
5944: SELECT CURRENCY_CODE
5945: FROM OKL_TRX_AP_INVOICES_B
5946: WHERE ID = cp_tap_id;
5947: -- smadhava - Bug#5200033 - Added - End
5941:
5942: -- Cursor to get the currency code from the header record
5943: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
5944: SELECT CURRENCY_CODE
5945: FROM OKL_TRX_AP_INVOICES_B
5946: WHERE ID = cp_tap_id;
5947: -- smadhava - Bug#5200033 - Added - End
5948:
5949: -- vpanwar Added --28/02/2007 -start
5946: WHERE ID = cp_tap_id;
5947: -- smadhava - Bug#5200033 - Added - End
5948:
5949: -- vpanwar Added --28/02/2007 -start
5950: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
5951: SELECT KHR_ID
5952: FROM OKL_TRX_AP_INVOICES_B
5953: WHERE ID = p_tap_id;
5954: -- vpanwar Added --28/02/2007 -end
5948:
5949: -- vpanwar Added --28/02/2007 -start
5950: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
5951: SELECT KHR_ID
5952: FROM OKL_TRX_AP_INVOICES_B
5953: WHERE ID = p_tap_id;
5954: -- vpanwar Added --28/02/2007 -end
5955:
5956: -- dcshanmu added - 23-Nov-2007 - bug # 6639928 - start
5953: WHERE ID = p_tap_id;
5954: -- vpanwar Added --28/02/2007 -end
5955:
5956: -- dcshanmu added - 23-Nov-2007 - bug # 6639928 - start
5957: CURSOR c_get_max_line_number(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
5958: SELECT MAX(LINE_NUMBER)
5959: FROM OKL_TXL_AP_INV_LNS_B
5960: WHERE TAP_ID = P_TAP_ID;
5961:
6172: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_FUNDING_LINES';
6173:
6174: l_api_version CONSTANT NUMBER := 1.0;
6175: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6176: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6177: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6178: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6179:
6180: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6173:
6174: l_api_version CONSTANT NUMBER := 1.0;
6175: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6176: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6177: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6178: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6179:
6180: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6181: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6174: l_api_version CONSTANT NUMBER := 1.0;
6175: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6176: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6177: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6178: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6179:
6180: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6181: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6182: p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6177: l_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE;
6178: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6179:
6180: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6181: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6182: p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6183: SELECT a.cle_id cle_id,
6184: a.chr_id chr_id,
6185: a.asset_number kle_num,
6178: l_vendor_id OKL_TRX_AP_INVOICES_B.VENDOR_ID%TYPE;
6179:
6180: -- cursor to fetch assets, which has supplier invoice for a given khr_id and vendor_site_id
6181: CURSOR c_get_assets(p_vendor_site_id OKL_TRX_AP_INVOICES_B.IPVS_ID%TYPE,
6182: p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6183: SELECT a.cle_id cle_id,
6184: a.chr_id chr_id,
6185: a.asset_number kle_num,
6186: a.description kle_name,
6203: AND cpl.cle_id = LN.ID
6204: AND LN.cle_id = a.cle_id;
6205:
6206: -- cursor to fetch org_id from khr_id
6207: CURSOR c_get_org_id(p_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE) IS
6208: SELECT AUTHORING_ORG_ID
6209: FROM OKC_K_HEADERS_ALL_B
6210: WHERE ID = p_khr_id;
6211:
6321: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
6322: l_sty_id number;
6323:
6324: --- vpanwar added 28/02/2007 start
6325: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6326: --- vpanwar added 28/02/2007 end
6327:
6328: cursor c_sty_id(p_tpl_id number) is
6329: select sty_id
6331: where id = p_tpl_id
6332: ;
6333:
6334: -- smadhava - Bug#5200033 - Added - Start
6335: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6336:
6337: -- Cursor to get the currency code from the header record
6338: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6339: SELECT CURRENCY_CODE
6334: -- smadhava - Bug#5200033 - Added - Start
6335: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6336:
6337: -- Cursor to get the currency code from the header record
6338: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6339: SELECT CURRENCY_CODE
6340: FROM OKL_TRX_AP_INVOICES_B
6341: WHERE ID = cp_tap_id;
6342: -- smadhava - Bug#5200033 - Added - End
6336:
6337: -- Cursor to get the currency code from the header record
6338: CURSOR c_get_currency_code(cp_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6339: SELECT CURRENCY_CODE
6340: FROM OKL_TRX_AP_INVOICES_B
6341: WHERE ID = cp_tap_id;
6342: -- smadhava - Bug#5200033 - Added - End
6343:
6344: -- vpanwar Added --28/02/2007 -start
6341: WHERE ID = cp_tap_id;
6342: -- smadhava - Bug#5200033 - Added - End
6343:
6344: -- vpanwar Added --28/02/2007 -start
6345: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6346: SELECT KHR_ID
6347: FROM OKL_TRX_AP_INVOICES_B
6348: WHERE ID = p_tap_id;
6349: -- vpanwar Added --28/02/2007 -end
6343:
6344: -- vpanwar Added --28/02/2007 -start
6345: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6346: SELECT KHR_ID
6347: FROM OKL_TRX_AP_INVOICES_B
6348: WHERE ID = p_tap_id;
6349: -- vpanwar Added --28/02/2007 -end
6350:
6351: BEGIN
6519: l_asset_number VARCHAR2(150);
6520:
6521:
6522: -- smadhava - Bug#5200033 - Modified - Start
6523: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6524:
6525: --- vpanwar added 28/02/2007 start
6526: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6527: --- vpanwar added 28/02/2007 end
6522: -- smadhava - Bug#5200033 - Modified - Start
6523: l_currency_code OKL_TRX_AP_INVOICES_B.CURRENCY_CODE%TYPE;
6524:
6525: --- vpanwar added 28/02/2007 start
6526: l_khr_id OKL_TRX_AP_INVOICES_B.KHR_ID%TYPE;
6527: --- vpanwar added 28/02/2007 end
6528:
6529:
6530: -- Modifed cursor to get the currency code
6531: CURSOR c (p_fund_id NUMBER)
6532: IS
6533: select a.org_id
6534: , a.currency_code
6535: from okl_trx_ap_invoices_b a
6536: where a.id = p_fund_id
6537: and a.funding_type_code = 'ASSET'
6538: ;
6539: -- smadhava - Bug#5200033 - Modified - End
6561: --(
6562: select a.cle_id,
6563: (select OKL_FUNDING_PVT.get_contract_line_amt(a.CHR_ID, a.CLE_ID, b.ipvs_id) from dual) KLE_AMT
6564: from OKL_ASSETS_LOV_UV A,
6565: OKL_TRX_AP_INVOICES_B b
6566: WHERE a.chr_id = b.khr_id
6567: AND b.ID = p_fund_id
6568: and NOT EXISTS
6569: (select 1
6575: ;
6576: --veramach 5600694 end
6577:
6578: -- vpanwar Added --28/02/2007 -start
6579: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6580: SELECT KHR_ID
6581: FROM OKL_TRX_AP_INVOICES_B
6582: WHERE ID = p_tap_id;
6583: -- vpanwar Added --28/02/2007 -end
6577:
6578: -- vpanwar Added --28/02/2007 -start
6579: CURSOR c_get_khr_id(p_tap_id OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
6580: SELECT KHR_ID
6581: FROM OKL_TRX_AP_INVOICES_B
6582: WHERE ID = p_tap_id;
6583: -- vpanwar Added --28/02/2007 -end
6584:
6585:
6767: l_currency_code okc_k_headers_b.CURRENCY_CODE%TYPE;
6768: l_org_id okc_k_headers_b.AUTHORING_ORG_ID%TYPE;
6769: l_contract_number okc_k_headers_b.CONTRACT_NUMBER%TYPE;
6770:
6771: l_amount okl_trx_ap_invoices_b.AMOUNT%TYPE;
6772:
6773: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6774: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6775:
6769: l_contract_number okc_k_headers_b.CONTRACT_NUMBER%TYPE;
6770:
6771: l_amount okl_trx_ap_invoices_b.AMOUNT%TYPE;
6772:
6773: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6774: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6775:
6776: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
6777: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
6772:
6773: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6774: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6775:
6776: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
6777: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
6778:
6779:
6780: CURSOR cu (p_id NUMBER)
6773: l_ipvs_id okl_trx_ap_invoices_b.IPVS_ID%TYPE;
6774: l_vendor_site_code OKX_VENDOR_SITES_V.NAME%TYPE;
6775:
6776: l_PAY_GROUP_LOOKUP_CODE OKL_TRX_AP_INVOICES_B.PAY_GROUP_LOOKUP_CODE%TYPE;
6777: l_NETTABLE_YN OKL_TRX_AP_INVOICES_B.NETTABLE_YN%TYPE;
6778:
6779:
6780: CURSOR cu (p_id NUMBER)
6781: IS
6780: CURSOR cu (p_id NUMBER)
6781: IS
6782: SELECT h.PAY_GROUP_LOOKUP_CODE,
6783: h.NETTABLE_YN
6784: FROM OKL_TRX_AP_INVOICES_B h
6785: WHERE h.id = p_id
6786: ;
6787:
6788: --
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,
6801: CURSOR c2 (p_contract_id NUMBER)
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')
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
7545: p_kle_id NUMBER)
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
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)
7561: p_kle_id NUMBER,
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
7598: l_amount NUMBER := 0;
7599: CURSOR c (p_fund_id NUMBER)
7600: IS
7601: SELECT nvl(SUM(tl.amount),0)
7602: FROM okl_trx_ap_invoices_b th,
7603: okl_txl_ap_inv_lns_b tl
7604: WHERE tl.tap_id = th.id
7605: AND th.id = p_fund_id
7606: -- no need for this function. this is used for display at UI site only
7608: ;
7609: CURSOR c2 (p_fund_id NUMBER)
7610: IS
7611: SELECT nvl(th.amount,0)
7612: FROM okl_trx_ap_invoices_b th
7613: WHERE th.id = p_fund_id
7614: -- no need for this function. this is used for display at UI site only
7615: -- AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED')
7616: ;
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)
7661: p_org_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
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)
7835: -- as part of OKLR12B disbursements project.
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')
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)
7852: CURSOR c2 (p_contract_id NUMBER, p_vendor_site_id NUMBER)
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')
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)
7914: -- as part of OKLR12B disbursements project.
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
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
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')
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)
7977: -- as part of OKLR12B disbursements project.
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')
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)
8027: -- as part of OKLR12B disbursements project.
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')
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)
8079: -- as part of OKLR12B disbursements project.
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')
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)
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)
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
8288: IS
8289: -- select nvl(sum(decode(sub.invoice_type, 'CREDIT', -subln.amount, subln.amount)),0)
8290: -- sjalasut, commented the above select as part of OKLR12B disbursements project
8291: select nvl(sum(subln.amount),0)
8292: from okl_trx_ap_invoices_b sub,
8293: okl_txl_ap_inv_lns_b subln
8294: where sub.id = subln.tap_id
8295: and sub.trx_status_code in ('APPROVED', 'PROCESSED')
8296: and sub.funding_type_code = G_MANUAL_DISB