[Home] [Help]
63: PROCEDURE raise_business_event(p_api_version IN NUMBER,
64: p_init_msg_list IN VARCHAR2,
65: p_chr_id IN okc_k_headers_b.id%TYPE,
66: p_asset_id IN okc_k_lines_b.id%TYPE,
67: p_subsidy_id IN okl_subsidies_b.id%TYPE,
68: p_event_name IN VARCHAR2,
69: x_return_status OUT NOCOPY VARCHAR2,
70: x_msg_count OUT NOCOPY NUMBER,
71: x_msg_data OUT NOCOPY VARCHAR2
214: subb.name,
215: subt.short_description
216: from
217: okl_subsidies_tl subt,
218: okl_subsidies_b subb
219: where subt.id = subb.id
220: and subt.language = userenv('LANG')
221: and subb.id = p_sub_id;
222:
249:
250: --cursor to get subsidy id from subsidy name
251: cursor l_subname_csr (p_subsidy_name in varchar2) is
252: select id
253: from okl_subsidies_b subb
254: where name = ltrim(rtrim(p_subsidy_name,' '),' ');
255:
256: l_subsidy_id Number;
257:
432: subb.name,
433: subt.short_description
434: from
435: okl_subsidies_tl subt,
436: okl_subsidies_b subb
437: where subt.id = subb.id
438: and subt.language = userenv('LANG')
439: and subb.id = p_sub_id;
440:
487:
488: --cursor to get subsidy id from subsidy name
489: cursor l_subname_csr (p_subsidy_name in varchar2) is
490: select id
491: from okl_subsidies_b subb
492: where name = ltrim(rtrim(p_subsidy_name,' '),' ');
493:
494: l_subsidy_id Number;
495:
1070: --cursor : to check applicability at contract header ORG_ID
1071: cursor l_chr_csr (p_subsidy_id in number,
1072: p_asset_cle_id in number) is
1073: Select 'Y'
1074: from okl_subsidies_b sub,
1075: okc_k_headers_b chrb,
1076: okc_k_lines_b cleb
1077: where sub.id = p_subsidy_id
1078: and chrb.id = cleb.chr_id
1088: -- on release
1089: cursor l_relk_csr (p_subsidy_id in number,
1090: p_asset_cle_id in number) is
1091: Select 'Y'
1092: from okl_subsidies_b sub,
1093: okc_k_headers_b chrb,
1094: okc_k_lines_b cleb
1095: where sub.id = p_subsidy_id
1096: and chrb.id = cleb.chr_id
1109: p_asset_cle_id in number) is
1110: -- Bug# 15992711 : Commented Original query
1111: /*
1112: Select 'Y'
1113: from okl_subsidies_b sub,
1114: okc_rules_b rulb,
1115: okc_k_lines_b cleb
1116: where sub.id = p_subsidy_id
1117: and rulb.dnz_chr_id = cleb.chr_id
1122: sub.APPLICABLE_TO_RELEASE_YN) = sub.APPLICABLE_TO_RELEASE_YN
1123: union
1124: -- to take care of S and O where release yes-no flag is not applicable
1125: Select 'Y'
1126: from okl_subsidies_b sub,
1127: okc_k_lines_b cleb
1128: where sub.id = p_subsidy_id
1129: and cleb.id = p_asset_cle_id
1130: and not exists (select 1
1132: where rulb.dnz_chr_id = cleb.chr_id
1133: and rulb.rule_information_category = 'LARLES');
1134: */
1135: Select 'Y'
1136: from okl_subsidies_b sub,
1137: okc_k_lines_b cleb,
1138: okl_k_lines kle_fin
1139: where sub.id = p_subsidy_id
1140: and kle_fin.id = cleb.id
1149: --cursor : to check applicability at line dates
1150: cursor l_cle_csr (p_subsidy_id in number,
1151: p_asset_cle_id in number) is
1152: Select 'Y'
1153: from okl_subsidies_b sub,
1154: okc_k_lines_b cleb
1155: where sub.id = p_subsidy_id
1156: and cleb.id = p_asset_cle_id
1157: -- start: okl.h cklee
1163:
1164: --cursor : to check existence of criteria
1165: cursor l_suc_csr (p_subsidy_id in number) is
1166: select 'Y'
1167: from okl_subsidies_b sub
1168: where sub.id = p_subsidy_id
1169: and exists (select 1
1170: from okl_subsidy_criteria suc
1171: where suc.subsidy_id = sub.id);
1172:
1173: --cursor : to check that inv check is required
1174: cursor l_invreq_csr (p_subsidy_id in number) is
1175: select 'Y'
1176: from okl_subsidies_b sub
1177: Where sub.id = p_subsidy_id
1178: and exists (select 1
1179: from okl_subsidy_criteria suc
1180: where suc.organization_id is not null
1206:
1207: --cursor : to check that credit class check is required
1208: cursor l_clsreq_csr (p_subsidy_id in number) is
1209: select 'Y'
1210: from okl_subsidies_b sub
1211: Where sub.id = p_subsidy_id
1212: and exists (select 1
1213: from okl_subsidy_criteria suc
1214: where suc.credit_classification_code is not null
1239:
1240: --cursor : to check that territory check is required
1241: cursor l_terrreq_csr (p_subsidy_id in number) is
1242: select 'Y'
1243: from okl_subsidies_b sub
1244: Where sub.id = p_subsidy_id
1245: and exists (select 1
1246: from okl_subsidy_criteria suc
1247: --cklee 03/16/2004
1315:
1316: --cursor : to check that product check is required
1317: cursor l_pdtreq_csr (p_subsidy_id in number) is
1318: select 'Y'
1319: from okl_subsidies_b sub
1320: Where sub.id = p_subsidy_id
1321: and exists (select 1
1322: from okl_subsidy_criteria suc
1323: where suc.PRODUCT_ID is not null
1338:
1339: --cursor : to check that sic_code check is required
1340: cursor l_sicreq_csr (p_subsidy_id in number) is
1341: select 'Y'
1342: from okl_subsidies_b sub
1343: Where sub.id = p_subsidy_id
1344: and exists (select 1
1345: from okl_subsidy_criteria suc
1346: where suc.INDUSTRY_CODE is not null
1369:
1370: --cursor : to check that subsidy expiration
1371: cursor l_not_expire_csr (p_subsidy_id in number) is
1372: select 'Y'
1373: from okl_subsidies_b sub
1374: Where sub.id = p_subsidy_id
1375:
1376: -- Start : Bug 6050165 : prasjain
1377: -- and TRUNC(nvl(sub.EFFECTIVE_TO_DATE,sysdate) + nvl(sub.EXPIRE_AFTER_DAYS,0)) >= TRUNC(sysdate);
1760: --cursor : to check applicability at contract header ORG_ID
1761: cursor l_chr_csr (p_subsidy_id in number,
1762: p_chr_id in number) is
1763: Select 'Y'
1764: from okl_subsidies_b sub,
1765: okc_k_headers_b chrb
1766: where sub.id = p_subsidy_id
1767: and chrb.id = p_chr_id
1768: --check for authoring org id
1775: cursor l_cle_csr (p_subsidy_id in number,
1776: p_start_date in date
1777: ) is
1778: Select 'Y'
1779: from okl_subsidies_b sub
1780: where sub.id = p_subsidy_id
1781: and p_start_date between sub.effective_from_date
1782: and nvl(sub.effective_to_date,p_start_date);
1783:
1783:
1784: --cursor : to check existence of criteria
1785: cursor l_suc_csr (p_subsidy_id in number) is
1786: select 'Y'
1787: from okl_subsidies_b sub
1788: where sub.id = p_subsidy_id
1789: and exists (select 1
1790: from okl_subsidy_criteria suc
1791: where suc.subsidy_id = sub.id);
1792:
1793: --cursor : to check that inv check is required
1794: cursor l_invreq_csr (p_subsidy_id in number) is
1795: select 'Y'
1796: from okl_subsidies_b sub
1797: Where sub.id = p_subsidy_id
1798: and exists (select 1
1799: from okl_subsidy_criteria suc
1800: where suc.organization_id is not null
1816:
1817: --cursor : to check that credit class check is required
1818: cursor l_clsreq_csr (p_subsidy_id in number) is
1819: select 'Y'
1820: from okl_subsidies_b sub
1821: Where sub.id = p_subsidy_id
1822: and exists (select 1
1823: from okl_subsidy_criteria suc
1824: where suc.credit_classification_code is not null
1845:
1846: --cursor : to check that territory check is required
1847: cursor l_terrreq_csr (p_subsidy_id in number) is
1848: select 'Y'
1849: from okl_subsidies_b sub
1850: Where sub.id = p_subsidy_id
1851: and exists (select 1
1852: from okl_subsidy_criteria suc
1853: where suc.SALES_TERRITORY_CODE is not null
1870:
1871: --cursor : to check that product check is required
1872: cursor l_pdtreq_csr (p_subsidy_id in number) is
1873: select 'Y'
1874: from okl_subsidies_b sub
1875: Where sub.id = p_subsidy_id
1876: and exists (select 1
1877: from okl_subsidy_criteria suc
1878: where suc.PRODUCT_ID is not null
1891:
1892: --cursor : to check that sic_code check is required
1893: cursor l_sicreq_csr (p_subsidy_id in number) is
1894: select 'Y'
1895: from okl_subsidies_b sub
1896: Where sub.id = p_subsidy_id
1897: and exists (select 1
1898: from okl_subsidy_criteria suc
1899: where suc.INDUSTRY_CODE is not null
2249: /* --cursor : to check applicability at contract header ORG_ID
2250: cursor l_chr_csr (p_subsidy_id in number,
2251: p_chr_id in number) is
2252: Select 'Y'
2253: from okl_subsidies_b sub,
2254: okc_k_headers_b chrb
2255: where sub.id = p_subsidy_id
2256: and chrb.id = p_chr_id
2257: --check for authoring org id
2263: cursor l_chr_csr (p_subsidy_id in number,
2264: p_authoring_org_id in number,
2265: p_currency_code in varchar2) is
2266: Select 'Y'
2267: from okl_subsidies_b sub
2268: where sub.id = p_subsidy_id
2269: --check for authoring org id
2270: and sub.org_id = p_authoring_org_id
2271: --check for currency code
2277: cursor l_cle_csr (p_subsidy_id in number,
2278: p_start_date in date
2279: ) is
2280: Select 'Y'
2281: from okl_subsidies_b sub
2282: where sub.id = p_subsidy_id
2283: -- start: okl.h cklee
2284: -- and p_start_date between sub.effective_from_date
2285: -- and nvl(sub.effective_to_date,p_start_date);
2289:
2290: --cursor : to check existence of criteria
2291: cursor l_suc_csr (p_subsidy_id in number) is
2292: select 'Y'
2293: from okl_subsidies_b sub
2294: where sub.id = p_subsidy_id
2295: and exists (select 1
2296: from okl_subsidy_criteria suc
2297: where suc.subsidy_id = sub.id);
2298:
2299: --cursor : to check that inv check is required
2300: cursor l_invreq_csr (p_subsidy_id in number) is
2301: select 'Y'
2302: from okl_subsidies_b sub
2303: Where sub.id = p_subsidy_id
2304: and exists (select 1
2305: from okl_subsidy_criteria suc
2306: where suc.organization_id is not null
2322:
2323: --cursor : to check that credit class check is required
2324: cursor l_clsreq_csr (p_subsidy_id in number) is
2325: select 'Y'
2326: from okl_subsidies_b sub
2327: Where sub.id = p_subsidy_id
2328: and exists (select 1
2329: from okl_subsidy_criteria suc
2330: where suc.credit_classification_code is not null
2362:
2363: --cursor : to check that territory check is required
2364: cursor l_terrreq_csr (p_subsidy_id in number) is
2365: select 'Y'
2366: from okl_subsidies_b sub
2367: Where sub.id = p_subsidy_id
2368: and exists (select 1
2369: from okl_subsidy_criteria suc
2370: where suc.SALES_TERRITORY_ID is not null
2398:
2399: --cursor : to check that product check is required
2400: cursor l_pdtreq_csr (p_subsidy_id in number) is
2401: select 'Y'
2402: from okl_subsidies_b sub
2403: Where sub.id = p_subsidy_id
2404: and exists (select 1
2405: from okl_subsidy_criteria suc
2406: where suc.PRODUCT_ID is not null
2430:
2431: --cursor : to check that sic_code check is required
2432: cursor l_sicreq_csr (p_subsidy_id in number) is
2433: select 'Y'
2434: from okl_subsidies_b sub
2435: Where sub.id = p_subsidy_id
2436: and exists (select 1
2437: from okl_subsidy_criteria suc
2438: where suc.INDUSTRY_CODE is not null
2765: -- 2. the subsidy pool has decision_status_code = 'ACTIVE' and sysdate between effective dates of the subsidy pool
2766: -- 3. the subsidy pool is logically active as on the start date of the asset
2767: -- 4. there exists a valid currency conversion basis between the pool and the asset/contract
2768: -- 5. the pool balance is valid before addition of the subsidy amount
2769: FUNCTION validate_subsidy_pool_applic(p_subsidy_id IN okl_subsidies_b.id%TYPE,
2770: p_asset_cle_id IN okc_k_lines_b.id%TYPE,
2771: p_ast_date_sq IN okc_k_lines_b.start_date%TYPE,
2772: p_trx_curr_code_sq IN okc_k_lines_b.currency_code%TYPE,
2773: --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2792: WHERE line.id = p_asset_cle_id;
2793:
2794: CURSOR c_get_subsidy_name_csr(p_subsidy_id number) IS
2795: SELECT sub.name
2796: FROM okl_subsidies_b sub
2797: WHERE sub.id = p_subsidy_id;
2798:
2799: CURSOR c_get_subsidy_pool_name_csr(p_subsidy_pool_id number) IS
2800: SELECT sub.subsidy_pool_name
2814: lv_asset_curr_code okc_k_lines_b.currency_code%TYPE;
2815: lv_start_date okc_k_lines_b.start_date%TYPE;
2816: --START: 09/29/2005 bug#4634871
2817: lv_asset_number okc_k_lines_v.name%TYPE;
2818: lv_subsidy_name okl_subsidies_b.name%TYPE;
2819: lv_subsidy_pool_name okl_subsidy_pools_v.subsidy_pool_name%TYPE;
2820: --END: 09/29/2005 bug#4634871
2821:
2822: l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_ASSET_SUBSIDY_PVT.VALIDATE_SUBSIDY_POOL_APPLIC';
3048: RETURN(l_applicable);
3049: END validate_subsidy_pool_applic;
3050:
3051:
3052: FUNCTION is_sub_assoc_with_pool(p_subsidy_id IN okl_subsidies_b.id%TYPE
3053: ,x_subsidy_pool_id OUT NOCOPY okl_subsidy_pools_b.id%TYPE
3054: ,x_sub_pool_curr_code OUT NOCOPY okl_subsidy_pools_b.currency_code%TYPE) RETURN VARCHAR2 IS
3055: CURSOR c_subsidy_csr IS
3056: SELECT sub.subsidy_pool_id,pool.currency_code
3053: ,x_subsidy_pool_id OUT NOCOPY okl_subsidy_pools_b.id%TYPE
3054: ,x_sub_pool_curr_code OUT NOCOPY okl_subsidy_pools_b.currency_code%TYPE) RETURN VARCHAR2 IS
3055: CURSOR c_subsidy_csr IS
3056: SELECT sub.subsidy_pool_id,pool.currency_code
3057: FROM okl_subsidies_b sub
3058: ,okl_subsidy_pools_b pool
3059: WHERE sub.id = p_subsidy_id
3060: AND sub.subsidy_pool_id = pool.id;
3061: cv_subsidy c_subsidy_csr%ROWTYPE;
3171: END IF;
3172: return lv_return_status;
3173: END is_balance_valid_before_add;
3174:
3175: PROCEDURE is_balance_valid_after_add (p_subsidy_id okl_subsidies_b.id%TYPE,
3176: p_asset_id okc_k_lines_b.id%TYPE,
3177: p_subsidy_amount NUMBER,
3178: p_subsidy_name okl_subsidies_b.name%TYPE
3179: ,x_return_status OUT NOCOPY VARCHAR2
3174:
3175: PROCEDURE is_balance_valid_after_add (p_subsidy_id okl_subsidies_b.id%TYPE,
3176: p_asset_id okc_k_lines_b.id%TYPE,
3177: p_subsidy_amount NUMBER,
3178: p_subsidy_name okl_subsidies_b.name%TYPE
3179: ,x_return_status OUT NOCOPY VARCHAR2
3180: ,x_msg_count OUT NOCOPY NUMBER
3181: ,x_msg_data OUT NOCOPY VARCHAR2
3182: ) IS
3190: kle_sub.SUBSIDY_OVERRIDE_AMOUNT))
3191: from okc_k_lines_b cleb_sub,
3192: okc_line_styles_b lseb_sub,
3193: okl_k_lines kle_sub,
3194: okl_subsidies_b sub
3195: where kle_sub.id = cleb_sub.id And
3196: cleb_sub.lse_id = lseb_sub.id And
3197: sub.id = kle_sub.subsidy_id And
3198: sub.subsidy_pool_id = p_subsidy_pool_id And
3218:
3219: --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3220: CURSOR c_subsidy_name_csr(p_subsidy_id number) IS
3221: select sub.name
3222: from okl_subsidies_b sub
3223: where sub.id = p_subsidy_id;
3224:
3225: l_subsidy_name okl_subsidies_b.name%TYPE;
3226: --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3221: select sub.name
3222: from okl_subsidies_b sub
3223: where sub.id = p_subsidy_id;
3224:
3225: l_subsidy_name okl_subsidies_b.name%TYPE;
3226: --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3227:
3228: cv_pool_amount c_get_pool_amount_csr%ROWTYPE;
3229:
3411: -- Version : 1.0
3412: -- History : 07-Dec-2005 cklee created
3413: -- End of comments
3414:
3415: PROCEDURE is_balance_valid_after_add (p_subsidy_id IN okl_subsidies_b.id%TYPE
3416: ,p_currency_code IN VARCHAR2
3417: ,p_subsidy_amount IN NUMBER
3418: ,p_tot_subsidy_amount IN NUMBER
3419: ,p_dnz_asset_number IN VARCHAR2
3431: WHERE id = p_subsidy_pool_id;
3432:
3433: CURSOR c_subsidy_name_csr(p_subsidy_id number) IS
3434: select sub.name
3435: from okl_subsidies_b sub
3436: where sub.id = p_subsidy_id;
3437:
3438: l_subsidy_name okl_subsidies_b.name%TYPE;
3439:
3434: select sub.name
3435: from okl_subsidies_b sub
3436: where sub.id = p_subsidy_id;
3437:
3438: l_subsidy_name okl_subsidies_b.name%TYPE;
3439:
3440: cv_pool_amount c_get_pool_amount_csr%ROWTYPE;
3441:
3442: lv_return_status VARCHAR2(1);
3580: x_return_status OUT NOCOPY VARCHAR2,
3581: p_subsidy_id IN NUMBER) IS
3582: cursor l_sub_csr (p_subsidy_id in number) is
3583: select 'Y'
3584: from okl_subsidies_b subb
3585: where subb.id = p_subsidy_id;
3586:
3587: l_exists varchar2(1) default 'N';
3588: BEGIN
3895: p_subsidy_id in number,
3896: p_subsidy_cle_id in number) is
3897: Select 'Y',
3898: subb.name
3899: from okl_subsidies_b subb
3900: where subb.id = p_subsidy_id
3901: and nvl(subb.exclusive_yn,'N') = 'Y'
3902: and exists (select '1'
3903: from okc_k_lines_b sub_cleb,
4086: p_subsidy_id in number,
4087: p_subsidy_cle_id in number) is
4088: Select 'Y',
4089: subb.name
4090: from okl_subsidies_b subb
4091: where subb.id = p_subsidy_id
4092: and nvl(subb.exclusive_yn,'N') = 'Y'
4093: and exists (select '1'
4094: from okc_k_lines_b sub_cleb,
4103: --cursor to check if subsidy attached is exclusive
4104: cursor l_exclusive_csr(p_asset_cle_id in number) is
4105: Select 'Y',
4106: subb.name
4107: from okl_subsidies_b subb,
4108: okc_k_lines_b sub_cleb,
4109: okc_line_styles_b sub_lseb,
4110: okl_k_lines sub_kleb
4111: where sub_cleb.cle_id = p_asset_cle_id
4119: -- cursor to check if multiple rows exist for
4120: -- a p_asset_cle_id ('FREE_FORM1')
4121: cursor l_exclusive_csr2(p_asset_cle_id in number) is
4122: select count(1)
4123: from okl_subsidies_b subb,
4124: okc_k_lines_b sub_cleb,
4125: okc_line_styles_b sub_lseb,
4126: okl_k_lines sub_kleb
4127: where sub_cleb.cle_id = p_asset_cle_id
4274: l_asset_number okc_k_lines_tl.name%TYPE;
4275:
4276: cursor l_sub_csr(p_subsidy_id in number) is
4277: select subb.name
4278: from okl_subsidies_b subb
4279: where id = p_subsidy_id;
4280:
4281: l_subsidy_name okl_subsidies_b.name%TYPE;
4282:
4277: select subb.name
4278: from okl_subsidies_b subb
4279: where id = p_subsidy_id;
4280:
4281: l_subsidy_name okl_subsidies_b.name%TYPE;
4282:
4283: begin
4284: -----
4285: x_return_status := OKL_API.G_RET_STS_SUCCESS;