[Home] [Help]
1149: select 'Y'
1150: from okl_subsidies_b sub
1151: where sub.id = p_subsidy_id
1152: and exists (select 1
1153: from okl_subsidy_criteria suc
1154: where suc.subsidy_id = sub.id);
1155:
1156: --cursor : to check that inv check is required
1157: cursor l_invreq_csr (p_subsidy_id in number) is
1158: select 'Y'
1159: from okl_subsidies_b sub
1160: Where sub.id = p_subsidy_id
1161: and exists (select 1
1162: from okl_subsidy_criteria suc
1163: where suc.organization_id is not null
1164: and suc.subsidy_id = sub.id);
1165:
1166: --cursor : check for inv item
1172: okc_k_lines_b cleb,
1173: okc_k_lines_b cle_model,
1174: okc_line_styles_b lse_model,
1175: okc_k_items cim_model,
1176: okl_subsidy_criteria suc
1177: where cim_model.cle_id = cle_model.id
1178: And cim_model.dnz_chr_id = cleb.dnz_chr_id
1179: And cle_model.cle_id = cleb.id
1180: And cle_model.dnz_chr_id = cleb.dnz_chr_id
1192: select 'Y'
1193: from okl_subsidies_b sub
1194: Where sub.id = p_subsidy_id
1195: and exists (select 1
1196: from okl_subsidy_criteria suc
1197: where suc.credit_classification_code is not null
1198: -- start: okl.h cklee
1199: -- And suc.id = sub.id);
1200: And suc.subsidy_id = sub.id);
1206: select 'Y'
1207: from okc_k_headers_b chrb,
1208: hz_cust_accounts cust,
1209: okc_k_lines_b cleb,
1210: okl_subsidy_criteria suc
1211: where chrb.id = cleb.chr_id
1212: And cleb.dnz_chr_id = chrb.id
1213: And cleb.id = p_asset_cle_id
1214: -- start: okl.h cklee
1225: select 'Y'
1226: from okl_subsidies_b sub
1227: Where sub.id = p_subsidy_id
1228: and exists (select 1
1229: from okl_subsidy_criteria suc
1230: --cklee 03/16/2004
1231: -- where suc.SALES_TERRITORY_CODE is not null
1232: where suc.SALES_TERRITORY_ID is not null
1233: And suc.subsidy_id = sub.id);
1248: okc_line_styles_b lse_ib,
1249: okc_k_lines_b cle_inst,
1250: okc_line_styles_b lse_inst,
1251: OKC_K_LINES_B cleb,
1252: okl_subsidy_criteria suc
1253: Where cle_inst.cle_id = cleb.id
1254: And cle_inst.dnz_chr_id = cleb.dnz_chr_id
1255: And cle_inst.lse_id = lse_inst.id
1256: And lse_inst.lty_code = 'FREE_FORM2'--'FREE_FORM1' cklee 21-Jan-04 bug#3375789
1278: from RA_SALESREP_TERRITORIES rst,
1279: OKC_CONTACTS cro,
1280: OKC_K_PARTY_ROLES_B cplb,
1281: OKC_K_LINES_B cleb,
1282: okl_subsidy_criteria suc
1283: Where
1284: rst.salesrep_id = cro.object1_id1
1285: And cro.object1_id2 = '#'
1286: And cro.jtot_object1_code = 'OKX_SALEPERS'
1301: select 'Y'
1302: from okl_subsidies_b sub
1303: Where sub.id = p_subsidy_id
1304: and exists (select 1
1305: from okl_subsidy_criteria suc
1306: where suc.PRODUCT_ID is not null
1307: And suc.subsidy_id = sub.id);
1308:
1309: --cursor to check financial product
1311: p_asset_cle_id in number) is
1312: select 'Y'
1313: from okl_k_headers khr,
1314: okc_k_lines_b cleb,
1315: okl_subsidy_criteria suc
1316: Where khr.id = cleb.chr_id
1317: And SUC.subsidy_id = p_subsidy_id
1318: And SUC.product_id = khr.pdt_id
1319: And SUC.product_id is not null
1324: select 'Y'
1325: from okl_subsidies_b sub
1326: Where sub.id = p_subsidy_id
1327: and exists (select 1
1328: from okl_subsidy_criteria suc
1329: where suc.INDUSTRY_CODE is not null
1330: And suc.INDUSTRY_CODE_TYPE is not null
1331: And suc.subsidy_id = sub.id);
1332:
1337: from hz_parties hp,
1338: hz_cust_accounts_all hca,
1339: okc_k_lines_b cleb,
1340: okc_k_headers_b chrb,
1341: okl_subsidy_criteria suc
1342: where hp.party_id = hca.party_id
1343: And hca.CUST_ACCOUNT_ID = chrb.cust_acct_id
1344: And chrb.id = cleb.chr_id
1345: And SUC.subsidy_id = p_subsidy_id
1765: select 'Y'
1766: from okl_subsidies_b sub
1767: where sub.id = p_subsidy_id
1768: and exists (select 1
1769: from okl_subsidy_criteria suc
1770: where suc.subsidy_id = sub.id);
1771:
1772: --cursor : to check that inv check is required
1773: cursor l_invreq_csr (p_subsidy_id in number) is
1774: select 'Y'
1775: from okl_subsidies_b sub
1776: Where sub.id = p_subsidy_id
1777: and exists (select 1
1778: from okl_subsidy_criteria suc
1779: where suc.organization_id is not null
1780: and suc.subsidy_id = sub.id);
1781:
1782: --cursor : check for inv item
1785: p_inv_org_id in number) is
1786: Select 'Y'
1787: From
1788: --inv item and org
1789: okl_subsidy_criteria suc
1790: where (suc.organization_id = p_inv_org_id
1791: And nvl(suc.inventory_item_id,p_inv_item_id) = p_inv_item_id
1792: )
1793: And suc.subsidy_id = p_subsidy_id
1798: select 'Y'
1799: from okl_subsidies_b sub
1800: Where sub.id = p_subsidy_id
1801: and exists (select 1
1802: from okl_subsidy_criteria suc
1803: where suc.credit_classification_code is not null
1804: -- start: okl.h cklee
1805: -- And suc.id = sub.id);
1806: And suc.subsidy_id = sub.id);
1813: p_chr_id in number) is
1814: select 'Y'
1815: from okc_k_headers_all_b chrb,
1816: hz_cust_accounts cust,
1817: okl_subsidy_criteria suc
1818: where chrb.id = p_chr_id
1819: And chrb.cust_acct_id = cust.cust_account_id
1820: And suc.subsidy_id = p_subsidy_id
1821: And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
1827: select 'Y'
1828: from okl_subsidies_b sub
1829: Where sub.id = p_subsidy_id
1830: and exists (select 1
1831: from okl_subsidy_criteria suc
1832: where suc.SALES_TERRITORY_CODE is not null
1833: And suc.subsidy_id = sub.id);
1834:
1835: --cursor to check territory
1838: select 'Y'
1839: from hz_locations loc,
1840: hz_party_sites hzps,
1841: hz_party_site_uses hzpsu,
1842: okl_subsidy_criteria suc
1843: Where hzpsu.party_site_use_id = p_install_site_use_id
1844: And hzps.party_site_id = hzpsu.party_site_id
1845: And loc.location_id = hzps.location_id
1846: And SUC.SUBSIDY_ID = p_subsidy_id
1852: select 'Y'
1853: from okl_subsidies_b sub
1854: Where sub.id = p_subsidy_id
1855: and exists (select 1
1856: from okl_subsidy_criteria suc
1857: where suc.PRODUCT_ID is not null
1858: And suc.subsidy_id = sub.id);
1859:
1860: --cursor to check financial product
1861: cursor l_pdt_csr (p_subsidy_id in number,
1862: p_chr_id in number) is
1863: select 'Y'
1864: from okl_k_headers khr,
1865: okl_subsidy_criteria suc
1866: Where khr.id = p_chr_id
1867: And SUC.subsidy_id = p_subsidy_id
1868: And SUC.product_id = khr.pdt_id
1869: And SUC.product_id is not null;
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.INDUSTRY_CODE is not null
1879: And suc.INDUSTRY_CODE_TYPE is not null
1880: And suc.subsidy_id = sub.id);
1881:
1885: select 'Y'
1886: from hz_parties hp,
1887: hz_cust_accounts_all hca,
1888: okc_k_headers_b chrb,
1889: okl_subsidy_criteria suc
1890: where hp.party_id = hca.party_id
1891: And hca.CUST_ACCOUNT_ID = chrb.cust_acct_id
1892: And chrb.id = p_chr_id
1893: And SUC.subsidy_id = p_subsidy_id
2271: select 'Y'
2272: from okl_subsidies_b sub
2273: where sub.id = p_subsidy_id
2274: and exists (select 1
2275: from okl_subsidy_criteria suc
2276: where suc.subsidy_id = sub.id);
2277:
2278: --cursor : to check that inv check is required
2279: cursor l_invreq_csr (p_subsidy_id in number) is
2280: select 'Y'
2281: from okl_subsidies_b sub
2282: Where sub.id = p_subsidy_id
2283: and exists (select 1
2284: from okl_subsidy_criteria suc
2285: where suc.organization_id is not null
2286: and suc.subsidy_id = sub.id);
2287:
2288: --cursor : check for inv item
2291: p_inv_org_id in number) is
2292: Select 'Y'
2293: From
2294: --inv item and org
2295: okl_subsidy_criteria suc
2296: where (suc.organization_id = p_inv_org_id
2297: And nvl(suc.inventory_item_id,p_inv_item_id) = p_inv_item_id
2298: )
2299: And suc.subsidy_id = p_subsidy_id
2304: select 'Y'
2305: from okl_subsidies_b sub
2306: Where sub.id = p_subsidy_id
2307: and exists (select 1
2308: from okl_subsidy_criteria suc
2309: where suc.credit_classification_code is not null
2310: -- start: okl.h cklee
2311: -- And suc.id = sub.id);
2312: And suc.subsidy_id = sub.id);
2318: p_chr_id in number) is
2319: select 'Y'
2320: from okc_k_headers_b chrb,
2321: hz_cust_accounts cust,
2322: okl_subsidy_criteria suc
2323: where chrb.id = p_chr_id
2324: And chrb.cust_acct_id = to_char(cust.cust_account_id)
2325: And suc.subsidy_id = p_subsidy_id
2326: And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
2330: cursor l_cclass_csr (p_subsidy_id in number,
2331: p_cust_account_id in number) is
2332: select 'Y'
2333: from hz_cust_accounts cust,
2334: okl_subsidy_criteria suc
2335: where cust.cust_account_id = p_cust_account_id
2336: And suc.subsidy_id = p_subsidy_id
2337: And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
2338: And SUC.CREDIT_CLASSIFICATION_CODE is not null;
2344: select 'Y'
2345: from okl_subsidies_b sub
2346: Where sub.id = p_subsidy_id
2347: and exists (select 1
2348: from okl_subsidy_criteria suc
2349: where suc.SALES_TERRITORY_ID is not null
2350: And suc.subsidy_id = sub.id);
2351:
2352: -- start: okl.h cklee
2356: select 'Y'
2357: from hz_locations loc,
2358: hz_party_sites hzps,
2359: hz_party_site_uses hzpsu,
2360: okl_subsidy_criteria suc
2361: Where hzpsu.party_site_use_id = p_install_site_use_id
2362: And hzps.party_site_id = hzpsu.party_site_id
2363: And loc.location_id = hzps.location_id
2364: And SUC.SUBSIDY_ID = p_subsidy_id
2368: cursor l_terr_csr (p_subsidy_id in number,
2369: p_sales_rep_id in number) is
2370: select 'Y'
2371: from RA_SALESREP_TERRITORIES rst,
2372: okl_subsidy_criteria suc
2373: Where rst.salesrep_id = p_sales_rep_id
2374: And SUC.SUBSIDY_ID = p_subsidy_id
2375: And SUC.SALES_TERRITORY_ID = rst.territory_id;
2376: -- end: okl.h cklee
2380: select 'Y'
2381: from okl_subsidies_b sub
2382: Where sub.id = p_subsidy_id
2383: and exists (select 1
2384: from okl_subsidy_criteria suc
2385: where suc.PRODUCT_ID is not null
2386: And suc.subsidy_id = sub.id);
2387:
2388: -- start: okl.h cklee
2390: /* cursor l_pdt_csr (p_subsidy_id in number,
2391: p_chr_id in number) is
2392: select 'Y'
2393: from okl_k_headers khr,
2394: okl_subsidy_criteria suc
2395: Where khr.id = p_chr_id
2396: And SUC.subsidy_id = p_subsidy_id
2397: And SUC.product_id = khr.pdt_id
2398: And SUC.product_id is not null;
2400: --cursor to check financial product
2401: cursor l_pdt_csr (p_subsidy_id in number,
2402: p_pdt_id in number) is
2403: select 'Y'
2404: from okl_subsidy_criteria suc
2405: where SUC.subsidy_id = p_subsidy_id
2406: And SUC.product_id = p_pdt_id
2407: And SUC.product_id is not null;
2408: -- end: okl.h cklee
2412: select 'Y'
2413: from okl_subsidies_b sub
2414: Where sub.id = p_subsidy_id
2415: and exists (select 1
2416: from okl_subsidy_criteria suc
2417: where suc.INDUSTRY_CODE is not null
2418: And suc.INDUSTRY_CODE_TYPE is not null
2419: And suc.subsidy_id = sub.id);
2420:
2424: p_chr_id in number) is
2425: select 'Y'
2426: from ra_customers rac,
2427: okc_k_headers_b chrb,
2428: okl_subsidy_criteria suc
2429: where rac.customer_id = chrb.cust_acct_id
2430: And chrb.id = p_chr_id
2431: And SUC.subsidy_id = p_subsidy_id
2432: And SUC.industry_code = rac.sic_code
2439: p_cust_account_id in number) is
2440: select 'Y'
2441: from hz_parties hp,
2442: hz_cust_accounts_all hca,
2443: okl_subsidy_criteria suc
2444: where hp.party_id = hca.party_id
2445: And hca.CUST_ACCOUNT_ID = p_cust_account_id
2446: And SUC.subsidy_id = p_subsidy_id
2447: And SUC.industry_code = hp.sic_code