[Home] [Help]
150: x_loading_status := p_loading_status ;
151:
152: -- Check if already exist( duplicate assigned,unique key violation check)
153: SELECT COUNT(1) INTO l_dummy
154: FROM cn_srp_pmt_plans_all
155: WHERE salesrep_id = p_salesrep_id
156: AND pmt_plan_id = p_pmt_plan_id
157: AND start_date = p_start_date
158: AND ( (end_date = p_end_date) OR
282: -- Ensure pmt plan assignments do not overlap each other in same role
283: -- only 1 payment plan is active at each date
284: -- Returns an error message and raises an exception if overlap occurs.
285: SELECT COUNT(1) INTO l_dummy
286: FROM cn_srp_pmt_plans_all cspp, cn_pmt_plans_all cpp
287: WHERE (((cspp.end_date IS NULL)
288: AND (p_end_date IS NULL))
289: OR
290: ((cspp.end_date IS NULL) AND
393:
394: CURSOR get_del_payruns IS
395: SELECT DISTINCT prun.name
396: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
397: cn_payruns_all prun, cn_srp_pmt_plans_all spp
398: WHERE w.salesrep_id = spp.salesrep_id
399: AND w.quota_id is null
400: AND prun.pay_period_id = prd.period_id
401: AND prun.org_id = prd.org_id
411:
412: CURSOR get_paid_del_payruns IS
413: SELECT 'ERROR' as estatus
414: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
415: cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
416: WHERE w.salesrep_id = spp.salesrep_id
417: AND w.quota_id is null
418: AND prun.pay_period_id = prd.period_id
419: AND prun.org_id = prd.org_id
431:
432: CURSOR get_adj_del_payruns IS
433: SELECT 'ERROR' as estatus
434: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
435: cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
436: -- cn_payment_transactions pt
437: WHERE w.salesrep_id = spp.salesrep_id
438: AND w.quota_id is null
439: AND prun.pay_period_id = prd.period_id
503: AND prd.end_date > l_fixed_end_date;
504:
505: CURSOR get_date_range(param_srp_pmt_plan_id NUMBER) IS
506: SELECT start_date, Nvl(end_date, l_end_of_time) as end_date
507: FROM cn_srp_pmt_plans_all
508: WHERE srp_pmt_plan_id = param_srp_pmt_plan_id;
509:
510: --Added by Christina------------------------------------------------------------
511: -- This cursor returns an error status if there are any paid/unpaid worksheets
514:
515: CURSOR get_adj_upd_payruns IS
516: SELECT 'ERROR' as estatus
517: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
518: cn_payruns_all prun, cn_srp_pmt_plans_all spp
519: -- cn_payment_transactions pt
520: WHERE w.salesrep_id = spp.salesrep_id
521: AND w.quota_id is null
522: AND prun.pay_period_id = prd.period_id
1018:
1019: -- populate ovn
1020: SELECT object_version_number
1021: INTO p_pmt_plan_assign_rec.object_version_number
1022: FROM cn_srp_pmt_plans_all
1023: WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1024:
1025: -- End of API body
1026:
1246: ,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
1247: ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag);
1248:
1249: -- sync ID back
1250: update cn_srp_pmt_plans_all
1251: set srp_pmt_plan_id = l_oldrec.srp_pmt_plan_id
1252: where srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1253:
1254: p_pmt_plan_assign_rec.srp_pmt_plan_id := l_oldrec.srp_pmt_plan_id;
1308: );
1309:
1310: -- if the lock_flag is being set, then blow away role_pmt_plan_id
1311: IF p_pmt_plan_assign_rec.lock_flag = 'Y' THEN
1312: UPDATE cn_srp_pmt_plans_all
1313: SET role_pmt_plan_id = NULL,
1314: srp_role_id = NULL
1315: WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1316: END IF;
1361:
1362: -- pick up new object version number
1363: SELECT object_version_number
1364: INTO p_pmt_plan_assign_rec.object_version_number
1365: FROM cn_srp_pmt_plans_all
1366: WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1367: -- End of API body.
1368:
1369: -- Standard check of p_commit.
1536: l_pmt_plan_assign_rec pmt_plan_assign_rec;
1537:
1538: CURSOR spp_info_cur IS
1539: select p.name, r.name role_name, spp.start_date, spp.end_date, spp.minimum_amount, spp.maximum_amount, spp.lock_flag, spp.salesrep_id
1540: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r
1541: where spp.srp_pmt_plan_id = p_srp_pmt_plan_id
1542: and spp.role_pmt_plan_id = rpp.role_pmt_plan_id(+)
1543: and spp.pmt_plan_id = p.pmt_plan_id
1544: and rpp.role_id = r.role_id(+);
1962: IF (l_count <> 0)
1963: THEN
1964: --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
1965: --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1966: --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)
1967: select spp.start_date, spp.end_date, spp.salesrep_id,
1968: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1969: into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1970: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
1967: select spp.start_date, spp.end_date, spp.salesrep_id,
1968: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1969: into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1970: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
1971: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all cpp, cn_role_pmt_plans_all crpp
1972: where spp.srp_role_id = p_srp_role_id
1973: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1974: AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
1975: AND cpp.pmt_plan_id = spp.pmt_plan_id;
2043: /* commented below code by Naren to fix bug 12821986
2044:
2045: SELECT count(*) into l_worksheets
2046: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2047: cn_payruns_all prun, cn_srp_pmt_plans_all spp
2048: WHERE w.salesrep_id = spp.salesrep_id
2049: AND w.quota_id is null
2050: AND prun.pay_period_id = prd.period_id
2051: AND prun.org_id = prd.org_id
2065: /* Added by Naren to fix bug 12821986*/
2066:
2067: SELECT count(*) into l_worksheets
2068: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2069: cn_payruns_all prun, cn_srp_pmt_plans_all spp
2070: WHERE w.salesrep_id = spp.salesrep_id
2071: AND w.quota_id is null
2072: AND prun.pay_period_id = prd.period_id
2073: AND prun.org_id = prd.org_id
2308: BEGIN
2309:
2310: --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
2311: --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2312: --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)
2313: select spp.start_date, spp.end_date, spp.salesrep_id,
2314: cpp.minimum_amount, cpp.maximum_amount,
2315: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2316: into l_start_date, l_end_date, l_salesrep_id,