[Home] [Help]
110: l_salesrep_id ,
111: l_srp_plan_assign_id ,
112: l_quota_id ,
113: l_org_id
114: FROM cn_srp_period_quotas_all
115: WHERE srp_period_quota_id = x_start_srp_period_quota_id;
116: SELECT interval_type_id
117: INTO l_interval_type_id
118: FROM cn_quotas_all
148: -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
149: l_itd_target ,
150: l_itd_payment,
151: l_performance_goal_itd
152: FROM cn_srp_period_quotas_all spq
153: WHERE salesrep_id = l_salesrep_id
154: AND srp_plan_assign_id = l_srp_plan_assign_id
155: AND quota_id = l_quota_id
156: AND period_id = l_previous_period_id;
156: AND period_id = l_previous_period_id;
157:
158: FOR i_period_id IN max_period_csr(l_interval_type_id,l_start_period_id,l_org_id)
159: LOOP
160: UPDATE cn_srp_period_quotas_all
161: SET input_achieved_itd = nvl(input_achieved_ptd,0) + l_input_achieved_itd ,
162: output_achieved_itd = nvl(output_achieved_ptd,0) + l_output_achieved_itd ,
163: perf_achieved_itd = nvl(perf_achieved_ptd,0) + l_perf_achieved_itd ,
164: commission_payed_itd = nvl(commission_payed_ptd,0) + l_commission_payed_itd ,
198: -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
199: l_itd_target ,
200: l_itd_payment,
201: l_performance_goal_itd
202: FROM cn_srp_period_quotas_all spq
203: WHERE salesrep_id = l_salesrep_id
204: AND srp_plan_assign_id = l_srp_plan_assign_id
205: AND quota_id = l_quota_id
206: AND period_id = i_period_id.cal_period_id;
239: NVL(spq.comm_pend_ptd,0) comm_pend_ptd ,
240: NVL(spq.target_amount,0) target_amount ,
241: NVL(spq.period_payment,0) period_payment ,
242: NVL(spq.performance_goal_ptd,0) performance_goal_ptd
243: FROM cn_srp_period_quotas_all spq,
244: cn_period_statuses_all cp ,
245: cn_cal_per_int_types_all cpit ,
246: cn_quotas_all cq
247: WHERE spq.quota_id = x_quota_id
323: l_advance_recovered_total := l_advance_recovered_total + pq_rec.advance_recovered_ptd;
324: l_advance_to_rec_total := l_advance_to_rec_total + pq_rec.advance_to_rec_ptd;
325: l_recovery_amount_total := l_recovery_amount_total + pq_rec.recovery_amount_ptd;
326: l_comm_pend_total := l_comm_pend_total + pq_rec.comm_pend_ptd;
327: UPDATE cn_srp_period_quotas_all
328: SET itd_target = NVL(l_target_total,0) ,
329: itd_payment = NVL(l_payment_total,0) ,
330: performance_goal_itd = NVL(l_performance_goal_total,0) ,
331: input_achieved_itd = NVL(l_input_achieved_total, 0) ,
410: -- is null condition of the cursor.
411: CURSOR srp_period_quota_ids1(l_quota_id NUMBER, l_srp_plan_assign_id NUMBER)
412: IS
413: SELECT srp_period_quota_id
414: FROM cn_srp_period_quotas_all
415: WHERE quota_id = l_quota_id
416: AND srp_plan_assign_id = l_srp_plan_assign_id ;
417: CURSOR srp_period_quota_ids2(l_quota_id NUMBER)
418: IS
416: AND srp_plan_assign_id = l_srp_plan_assign_id ;
417: CURSOR srp_period_quota_ids2(l_quota_id NUMBER)
418: IS
419: SELECT srp_period_quota_id
420: FROM cn_srp_period_quotas_all
421: WHERE quota_id = l_quota_id;
422: --bugfix for #2874991 starts
423: CURSOR start_period_quota_id_csr1(p_srp_plan_assign_id NUMBER,p_quota_id NUMBER,x_start_period_id NUMBER)
424: IS
422: --bugfix for #2874991 starts
423: CURSOR start_period_quota_id_csr1(p_srp_plan_assign_id NUMBER,p_quota_id NUMBER,x_start_period_id NUMBER)
424: IS
425: SELECT srp_period_quota_id
426: FROM cn_srp_period_quotas_all
427: WHERE srp_plan_assign_id = p_srp_plan_assign_id
428: AND quota_id = p_quota_id
429: AND period_id = x_start_period_id;
430: CURSOR start_period_quota_id_csr2(p_quota_id NUMBER,x_start_period_id NUMBER)
429: AND period_id = x_start_period_id;
430: CURSOR start_period_quota_id_csr2(p_quota_id NUMBER,x_start_period_id NUMBER)
431: IS
432: SELECT MAX(srp_period_quota_id)
433: FROM cn_srp_period_quotas_all
434: WHERE quota_id = p_quota_id
435: AND period_id = x_start_period_id
436: GROUP BY srp_plan_assign_id;
437:
468: LOOP
469: -- enhancement, clku, 2431086,we do not check if the PE is ITD or not.
470: --IF (itd_p_rec.itd_flag = 'Y') THEN
471: INSERT
472: INTO cn_srp_period_quotas_all
473: (
474: srp_period_quota_id ,
475: srp_plan_assign_id ,
476: srp_quota_assign_id ,
525: AND pq.quota_id = itd_p_rec.quota_id
526: AND greatest(p.start_date, NVL(x_start_date, pa.start_date)) <= least(p.end_date, NVL(x_end_date, NVL(pa.end_date,p.end_date)))
527: AND NOT EXISTS
528: (SELECT 'srp_period_quota already exists'
529: FROM cn_srp_period_quotas_all spq
530: WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
531: AND spq.period_id = p.period_id
532: )
533: -- bug 2460926, clku, check if all the open period ends before the specified start_date
583: -- case 1: called from cn_srp_quota_assigns_pkg.
584: -- enhancement, clku, 2431086, we do not check if the PE is ITD or not.
585: --IF x_itd_flag_checked = 'Y' THEN
586: INSERT
587: INTO cn_srp_period_quotas_all
588: (
589: srp_period_quota_id ,
590: srp_plan_assign_id ,
591: srp_quota_assign_id ,
639: AND pq.org_id = p.org_id
640: AND pq.quota_id = qa.quota_id
641: AND NOT EXISTS
642: (SELECT 'srp_period_quota already exists'
643: FROM cn_srp_period_quotas_all spq
644: WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
645: AND spq.period_id = p.period_id
646: )
647: AND EXISTS
682: ELSIF x_srp_plan_assign_id IS NULL AND x_quota_id IS NOT NULL THEN
683: -- Quota's period range has been changed and we are inserting a
684: -- new set of records based on the period interval
685: INSERT
686: INTO cn_srp_period_quotas_all
687: (
688: srp_period_quota_id ,
689: srp_plan_assign_id ,
690: srp_quota_assign_id ,
755: AND p.period_set_id = p3.period_set_id )
756: AND greatest(p.start_date, x_start_date) <= least(p.end_date, NVL(x_end_date, p.end_date))
757: AND NOT EXISTS
758: (SELECT 'srp_quota_assign already exists'
759: FROM cn_srp_period_quotas_all pq
760: WHERE pq.srp_quota_assign_id = qa.srp_quota_assign_id
761: AND pq.period_id = p.period_id
762: )
763: -- bug 2460926, check if all the open period ends before the specified start_date
814: IS
815: CURSOR c
816: IS
817: SELECT target_amount
818: FROM cn_srp_period_quotas_all
819: WHERE srp_period_quota_id = x_srp_period_quota_id FOR UPDATE OF srp_period_quota_id NOWAIT;
820:
821: recinfo c%ROWTYPE;
822: BEGIN
941: srp.name ,
942: acc.start_date ,
943: acc.end_date ,
944: srp.org_id
945: FROM cn_srp_period_quotas_all period,
946: cn_salesreps srp ,
947: cn_period_statuses acc
948: WHERE period.srp_period_quota_id = x_srp_period_quota_id
949: AND acc.period_id = period.period_id
993: l_temp_org_id;
994:
995: CLOSE l_get_intel_temp_csr;
996: -- end of addition
997: UPDATE cn_srp_period_quotas_all
998: SET target_amount = ROUND(NVL(x_target_amount, 0), g_ext_precision),
999: period_payment = ROUND(NVL(x_period_payment,0), g_ext_precision),
1000: performance_goal_ptd = ROUND(NVL(x_performance_goal,0), g_ext_precision)
1001: WHERE srp_period_quota_id = x_srp_period_quota_id ;
1017: LOOP
1018: l_target_total := l_target_total + pq_rec.target_amount;
1019: l_payment_total := l_payment_total + pq_rec.period_payment;
1020: l_performance_goal_total := l_performance_goal_total + pq_rec.performance_goal_ptd;
1021: UPDATE cn_srp_period_quotas_all
1022: SET itd_target = ROUND(NVL(l_target_total,0), g_ext_precision) ,
1023: itd_payment = ROUND(NVL(l_payment_total,0), g_ext_precision),
1024: performance_goal_itd = ROUND(NVL(l_performance_goal_total,0),g_ext_precision)
1025: WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
1027: END LOOP;
1028: ELSE
1029: -- only in the case of bonus at the time payee run Bonus commission update
1030: IF x_salesrep_id IS NOT NULL AND x_end_date IS NOT NULL AND x_quota_id IS NOT NULL THEN
1031: UPDATE cn_srp_period_quotas_all spq
1032: SET spq.commission_payed_ptd = x_commission_payed_ptd +spq.commission_payed_ptd
1033: WHERE spq.salesrep_id = x_salesrep_id
1034: AND spq.quota_id = x_quota_id
1035: AND EXISTS
1102: CURSOR srp_period_quota_ids(l_quota_id NUMBER)
1103: IS
1104: SELECT srp_period_quota_id,
1105: org_id
1106: FROM cn_srp_period_quotas_all
1107: WHERE quota_id = l_quota_id
1108: AND srp_plan_assign_id = NVL(x_srp_plan_assign_id, srp_plan_assign_id)
1109: AND EXISTS
1110: (SELECT 1
1110: (SELECT 1
1111: FROM cn_period_statuses p
1112: WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1113: AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1114: AND cn_srp_period_quotas_all.period_id = p.period_id
1115: AND p.period_status IN ('O', 'F')
1116: AND cn_srp_period_quotas_all.org_id = p.org_id
1117: );
1118:
1112: WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1113: AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1114: AND cn_srp_period_quotas_all.period_id = p.period_id
1115: AND p.period_status IN ('O', 'F')
1116: AND cn_srp_period_quotas_all.org_id = p.org_id
1117: );
1118:
1119: l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
1120: CURSOR l_bal_id
1203: IF x_quota_id IS NULL THEN
1204: IF x_start_date IS NULL THEN
1205: -- Deleted plan assignment
1206: DELETE
1207: FROM cn_srp_period_quotas_all
1208: WHERE srp_plan_assign_id = x_srp_plan_assign_id ;
1209: ELSE
1210: -- make sure we have the right org ID
1211: SELECT org_id
1215: -- get end date period of x_end_date
1216: l_end_date_pd := TRUNC(cn_end_date_period(x_end_date, l_org_id));
1217: -- plan assignment range changed
1218: DELETE
1219: FROM cn_srp_period_quotas_all
1220: WHERE srp_plan_assign_id = x_srp_plan_assign_id
1221: AND EXISTS
1222: (SELECT 1
1223: FROM cn_period_statuses p
1225: -- following line changed for bug 4424669, 4885986
1226: AND TRUNC(p.end_date) <= NVL(l_end_date_pd, p.end_date)
1227: --AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
1228: AND p.period_status IN ('O', 'F')
1229: AND cn_srp_period_quotas_all.period_id = p.period_id
1230: AND cn_srp_period_quotas_all.org_id = p.org_id
1231: AND ( ( p.start_date <> p.end_date)
1232: OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
1233: );
1226: AND TRUNC(p.end_date) <= NVL(l_end_date_pd, p.end_date)
1227: --AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
1228: AND p.period_status IN ('O', 'F')
1229: AND cn_srp_period_quotas_all.period_id = p.period_id
1230: AND cn_srp_period_quotas_all.org_id = p.org_id
1231: AND ( ( p.start_date <> p.end_date)
1232: OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
1233: );
1234: END IF;
1235: ELSE -- Quota id IS NOT NULL
1236: -- quota is no longer assigned to the comp plan
1237: -- same as the start date us null
1238: DELETE
1239: FROM cn_srp_period_quotas_all
1240: WHERE srp_plan_assign_id = x_srp_plan_assign_id
1241: AND quota_id = x_quota_id
1242: AND NVL(x_start_period_id, period_id) <= period_id -- Bug 3848446, Fixed by Jagpreet Singh.
1243: ;
1269: END IF;
1270: -- quota date range changed. remove the old periods in preparation
1271: -- for insert of new range
1272: DELETE
1273: FROM cn_srp_period_quotas_all
1274: WHERE quota_id = x_quota_id
1275: AND EXISTS
1276: (SELECT 1
1277: FROM cn_period_statuses p
1276: (SELECT 1
1277: FROM cn_period_statuses p
1278: WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1279: AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1280: AND cn_srp_period_quotas_all.period_id = p.period_id
1281: AND p.period_status IN ('O', 'F')
1282: AND cn_srp_period_quotas_all.org_id = p.org_id
1283: AND ( ( p.start_date <> p.end_date)
1284: OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
1278: WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1279: AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1280: AND cn_srp_period_quotas_all.period_id = p.period_id
1281: AND p.period_status IN ('O', 'F')
1282: AND cn_srp_period_quotas_all.org_id = p.org_id
1283: AND ( ( p.start_date <> p.end_date)
1284: OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
1285: );
1286: END IF;
1366: -- only use this if number_dim not used
1367: CURSOR DIM_NUMBER_CUR
1368: IS
1369: SELECT ccf.number_dim
1370: FROM cn_srp_period_quotas_all cspq,
1371: cn_quotas_all cq ,
1372: cn_calc_formulas_all ccf
1373: WHERE cspq.srp_period_quota_id = x_srp_period_quota_id
1374: AND cq.quota_id = cspq.quota_id
1442: IS
1443: BEGIN
1444: SELECT NVL(SUM(target_amount),0)
1445: INTO x_total
1446: FROM cn_srp_period_quotas_all
1447: WHERE srp_quota_assign_id = x_srp_quota_assign_id ;
1448:
1449: x_total_rtot_db := x_total;
1450: EXCEPTION
1598: l_period_payment := ( ( l_quota_payment * (l_period_count / interval_rec.interval_count) ) - l_running_total_payment );
1599: l_running_total_payment := l_running_total_payment + l_period_payment;
1600: l_period_performance_goal := ( ( l_performance_goal * (l_period_count / interval_rec.interval_count) ) - l_running_performance_goal );
1601: l_running_performance_goal := l_running_performance_goal + l_period_performance_goal;
1602: UPDATE cn_srp_period_quotas_all
1603: SET target_amount = ROUND(NVL(l_period_target, 0), g_ext_precision) ,
1604: itd_target = ROUND(NVL(l_running_total_target,0), g_ext_precision) ,
1605: period_payment = ROUND(NVL(l_period_payment,0), g_ext_precision) ,
1606: itd_payment = ROUND(NVL(l_running_total_payment,0), g_ext_precision) ,