[Home] [Help]
1: PACKAGE BODY CN_SRP_PERIOD_QUOTAS_PKG as
2: /* $Header: cnsrpqob.pls 120.9 2005/12/20 10:45:06 mblum ship $ */
3:
4: --Date Name Description
5: ---------------------------------------------------------------------------+
117: BEGIN
118:
119: SELECT period_id, salesrep_id, srp_plan_assign_id, quota_id, org_id
120: INTO l_start_period_id, l_salesrep_id, l_srp_plan_assign_id, l_quota_id, l_org_id
121: FROM cn_srp_period_quotas_all
122: WHERE srp_period_quota_id = x_start_srp_period_quota_id;
123:
124: SELECT interval_type_id INTO l_interval_type_id
125: FROM cn_quotas_all WHERE quota_id = l_quota_id;
157: -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
158: l_itd_target,
159: l_itd_payment,
160: l_performance_goal_itd
161: FROM cn_srp_period_quotas_all spq
162: WHERE salesrep_id = l_salesrep_id
163: AND srp_plan_assign_id = l_srp_plan_assign_id
164: AND quota_id = l_quota_id
165: AND period_id = l_previous_period_id;
164: AND quota_id = l_quota_id
165: AND period_id = l_previous_period_id;
166:
167: FOR i_period_id in max_period_csr(l_interval_type_id,l_start_period_id,l_org_id) LOOP
168: UPDATE cn_srp_period_quotas_all
169: SET input_achieved_itd = l_input_achieved_itd,
170: output_achieved_itd = l_output_achieved_itd,
171: perf_achieved_itd = l_perf_achieved_itd,
172: commission_payed_itd = l_commission_payed_itd,
224: nvl(spq.target_amount,0) target_amount,
225: nvl(spq.period_payment,0) period_payment,
226: nvl(spq.performance_goal_ptd,0) performance_goal_ptd
227: FROM
228: cn_srp_period_quotas_all spq,
229: cn_period_statuses_all cp,
230: cn_cal_per_int_types_all cpit,
231: cn_quotas_all cq
232: WHERE spq.quota_id = x_quota_id
251: p.srp_quota_assign_id srp_quota_assign_id,
252: count(p.srp_period_quota_id) interval_count,
253: cpit.interval_number interval_number,
254: p.period_year period_year
255: FROM cn_srp_period_quotas_v p,
256: cn_period_statuses cp,
257: cn_cal_per_int_types_all cpit,
258: cn_quotas_all cq
259: WHERE p.quota_id = x_quota_id
322: l_recovery_amount_total := l_recovery_amount_total + pq_rec.recovery_amount_ptd;
323: l_comm_pend_total:= l_comm_pend_total + pq_rec.comm_pend_ptd;
324:
325:
326: UPDATE cn_srp_period_quotas_all
327: SET
328: 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),
372: -- |-----Plan Assignment Active range---|
373: -- |--------- Comp Plan active Range--------|
374: -- |-----|----------Quota Active Range--|-------|
375:
376: -- o All quota types have cn_srp_period_quotas althouhg revenue types
377: -- do not display or allow the user to maintain a target value
378:
379: -- New Comments Added on 10/JUN/99
380: -- Start period id and End period is not used any more in the sales comp
422:
423: -- clku bug 2845024, performance fix, avoid full table scan by avoiding
424: -- is null condition of the cursor.
425: CURSOR srp_period_quota_ids1(l_quota_id NUMBER, l_srp_plan_assign_id NUMBER) is
426: SELECT srp_period_quota_id from cn_srp_period_quotas_all
427: where quota_id = l_quota_id
428: and srp_plan_assign_id = l_srp_plan_assign_id ;
429:
430: CURSOR srp_period_quota_ids2(l_quota_id NUMBER) is
427: where quota_id = l_quota_id
428: and srp_plan_assign_id = l_srp_plan_assign_id ;
429:
430: CURSOR srp_period_quota_ids2(l_quota_id NUMBER) is
431: SELECT srp_period_quota_id from cn_srp_period_quotas_all
432: where quota_id = l_quota_id;
433:
434: --bugfix for #2874991 starts
435: cursor start_period_quota_id_csr1(p_srp_plan_assign_id NUMBER,p_quota_id NUMBER,x_start_period_id NUMBER) is
433:
434: --bugfix for #2874991 starts
435: cursor start_period_quota_id_csr1(p_srp_plan_assign_id NUMBER,p_quota_id NUMBER,x_start_period_id NUMBER) is
436: Select srp_period_quota_id
437: from cn_srp_period_quotas_all
438: where srp_plan_assign_id = p_srp_plan_assign_id
439: and quota_id = p_quota_id
440: and period_id = x_start_period_id;
441:
441:
442:
443: cursor start_period_quota_id_csr2(p_quota_id NUMBER,x_start_period_id NUMBER) is
444: Select max(srp_period_quota_id)
445: from cn_srp_period_quotas_all
446: where quota_id = p_quota_id
447: and period_id = x_start_period_id
448: group by srp_plan_assign_id;
449:
485:
486: -- enhancement, clku, 2431086,we do not check if the PE is ITD or not.
487: --IF (itd_p_rec.itd_flag = 'Y') THEN
488:
489: INSERT INTO cn_srp_period_quotas_all
490: ( srp_period_quota_id
491: ,srp_plan_assign_id
492: ,srp_quota_assign_id
493: ,salesrep_id
506: ,last_updated_by
507: ,last_update_login
508: ,org_id )
509: SELECT
510: cn_srp_period_quotas_s.nextval
511: ,qa.srp_plan_assign_id
512: ,qa.srp_quota_assign_id
513: ,pa.salesrep_id
514: ,p.period_id
541: AND greatest(p.start_date, nvl(x_start_date, pa.start_date)) <=
542: least(p.end_date, nvl(x_end_date,
543: Nvl(pa.end_date,p.end_date)))
544: AND NOT EXISTS (SELECT 'srp_period_quota already exists'
545: FROM cn_srp_period_quotas_all spq
546: WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
547: AND spq.period_id = p.period_id)
548: -- bug 2460926, clku, check if all the open period ends before the specified start_date
549: -- 2479359, Nvl(x_start_date, pa.start_date) added to deal with NULL x_start_date
597:
598: -- enhancement, clku, 2431086, we do not check if the PE is ITD or not.
599: --IF x_itd_flag_checked = 'Y' THEN
600:
601: INSERT INTO cn_srp_period_quotas_all
602: ( srp_period_quota_id
603: ,srp_plan_assign_id
604: ,srp_quota_assign_id
605: ,salesrep_id
618: ,last_updated_by
619: ,last_update_login
620: ,org_id )
621: SELECT
622: cn_srp_period_quotas_s.nextval
623: ,qa.srp_plan_assign_id
624: ,qa.srp_quota_assign_id
625: ,pa.salesrep_id
626: ,p.period_id
651: AND p.period_status IN ('O', 'F')
652: AND pq.org_id = p.org_id
653: AND pq.quota_id = qa.quota_id
654: AND NOT EXISTS (SELECT 'srp_period_quota already exists'
655: FROM cn_srp_period_quotas_all spq
656: WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
657: AND spq.period_id = p.period_id)
658: AND EXISTS(select r1.end_date
659: from cn_acc_period_statuses_v r1
689:
690: -- Quota's period range has been changed and we are inserting a
691: -- new set of records based on the period interval
692:
693: INSERT INTO cn_srp_period_quotas_all
694: ( srp_period_quota_id
695: ,srp_plan_assign_id
696: ,srp_quota_assign_id
697: ,salesrep_id
709: ,last_update_date
710: ,last_updated_by
711: ,last_update_login
712: ,org_id )
713: SELECT cn_srp_period_quotas_s.nextval
714: ,qa.srp_plan_assign_id
715: ,qa.srp_quota_assign_id
716: ,pa.salesrep_id
717: ,p.period_id
762:
763: AND greatest(p.start_date, x_start_date) <=
764: least(p.end_date, nvl(x_end_date, p.end_date))
765: AND NOT EXISTS (SELECT 'srp_quota_assign already exists'
766: FROM cn_srp_period_quotas_all pq
767: WHERE pq.srp_quota_assign_id = qa.srp_quota_assign_id
768: AND pq.period_id = p.period_id)
769: -- bug 2460926, check if all the open period ends before the specified start_date
770:
818: ,x_target_amount NUMBER) IS
819:
820: CURSOR c IS
821: SELECT target_amount
822: FROM cn_srp_period_quotas_all
823: WHERE srp_period_quota_id = x_srp_period_quota_id
824: FOR UPDATE OF srp_period_quota_id NOWAIT;
825:
826: recinfo c%ROWTYPE;
872: CURSOR quart_counts IS
873: SELECT count(srp_period_quota_id) quart_yr_count
874: ,quarter_num
875: ,period_year
876: FROM cn_srp_period_quotas_v
877: WHERE srp_quota_assign_id = x_srp_quota_assign_id
878: GROUP BY quarter_num,period_year
879: ;
880: quart_rec quart_counts%ROWTYPE;
883: -- covers.
884: CURSOR year_counts IS
885: SELECT count(srp_period_quota_id) year_count
886: ,period_year
887: FROM cn_srp_period_quotas_v
888: WHERE srp_quota_assign_id = x_srp_quota_assign_id
889: GROUP BY period_year
890: ;
891: year_rec year_counts%ROWTYPE;
894: SELECT spq.srp_period_quota_id
895: ,spq.target_amount
896: ,spq.period_payment
897: ,spq.performance_goal_ptd
898: FROM cn_srp_period_quotas_v spq,
899: cn_period_statuses cp,
900: cn_cal_per_int_types_all cpit,
901: cn_quotas_all cq
902: WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
921: CURSOR interval_counts IS
922: SELECT count(spq.srp_period_quota_id) interval_count,
923: cpit.interval_number interval_number,
924: spq.period_year period_year
925: FROM cn_srp_period_quotas_v spq,
926: cn_period_statuses cp,
927: cn_cal_per_int_types_all cpit,
928: cn_quotas_all cq
929: WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
942: -- added for intelligent calculation
943: CURSOR l_get_intel_temp_csr IS
944: SELECT period.target_amount, period.period_payment,
945: srp.name, acc.start_date, acc.end_date, srp.org_id
946: FROM cn_srp_period_quotas_all period, 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
950: AND acc.org_id = period.org_id
976:
977: -- get precision
978: select c.extended_precision INTO g_ext_precision
979: from cn_repositories r, gl_sets_of_books b, fnd_currencies c,
980: cn_srp_period_quotas spq
981: where r.org_id = spq.org_id
982: and r.set_of_books_id = b.set_of_books_id
983: and b.currency_code = c.currency_code
984: AND spq.srp_period_quota_id = x_srp_period_quota_id;
997: l_temp_org_id;
998: CLOSE l_get_intel_temp_csr;
999: -- end of addition
1000:
1001: UPDATE cn_srp_period_quotas_all
1002: SET
1003: target_amount = round(nvl(x_target_amount, 0), g_ext_precision),
1004: period_payment = round(nvl(x_period_payment,0), g_ext_precision),
1005: performance_goal_ptd = round(Nvl(x_performance_goal,0), g_ext_precision)
1044: l_payment_total := l_payment_total + pq_rec.period_payment;
1045: l_performance_goal_total := l_performance_goal_total
1046: + pq_rec.performance_goal_ptd;
1047:
1048: UPDATE cn_srp_period_quotas_all
1049: SET
1050: itd_target = round(nvl(l_target_total,0), g_ext_precision),
1051: itd_payment = round(nvl(l_payment_total,0), g_ext_precision),
1052: performance_goal_itd = round(nvl(l_performance_goal_total,0),g_ext_precision)
1060: -- only in the case of bonus at the time payee run Bonus commission update
1061: if x_salesrep_id IS NOT NULL AND
1062: x_end_date IS NOT NULL AND
1063: x_quota_id IS NOT NULL THEN
1064: UPDATE cn_srp_period_quotas_all spq
1065: SET
1066: spq.commission_payed_ptd = x_commission_payed_ptd +spq.commission_payed_ptd
1067: WHERE spq.salesrep_id = x_salesrep_id
1068: AND spq.quota_id = x_quota_id
1138: l_loading_status VARCHAR2(2000);
1139: l_org_id NUMBER;
1140:
1141: CURSOR srp_period_quota_ids(l_quota_id number) IS
1142: SELECT srp_period_quota_id, org_id from cn_srp_period_quotas_all
1143: WHERE quota_id = l_quota_id
1144: AND srp_plan_assign_id = nvl(x_srp_plan_assign_id, srp_plan_assign_id)
1145: AND EXISTS ( SELECT 1 from cn_period_statuses p
1146: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1144: AND srp_plan_assign_id = nvl(x_srp_plan_assign_id, srp_plan_assign_id)
1145: AND EXISTS ( SELECT 1 from cn_period_statuses p
1146: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1147: AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1148: AND cn_srp_period_quotas_all.period_id = p.period_id
1149: AND p.period_status IN ('O', 'F')
1150: AND cn_srp_period_quotas_all.org_id = p.org_id);
1151:
1152: l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
1146: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1147: AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1148: AND cn_srp_period_quotas_all.period_id = p.period_id
1149: AND p.period_status IN ('O', 'F')
1150: AND cn_srp_period_quotas_all.org_id = p.org_id);
1151:
1152: l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
1153:
1154: CURSOR l_bal_id IS
1218: IF x_quota_id IS NULL THEN
1219:
1220: IF x_start_date IS NULL THEN
1221: -- Deleted plan assignment
1222: DELETE FROM cn_srp_period_quotas_all
1223: WHERE srp_plan_assign_id = x_srp_plan_assign_id
1224: ;
1225:
1226: ELSE
1232: -- get end date period of x_end_date
1233: l_end_date_pd := Trunc(cn_end_date_period(x_end_date, l_org_id));
1234:
1235: -- plan assignment range changed
1236: DELETE FROM cn_srp_period_quotas_all
1237: WHERE srp_plan_assign_id = x_srp_plan_assign_id
1238: AND EXISTS
1239: ( SELECT 1 FROM cn_period_statuses p
1240: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1241: -- following line changed for bug 4424669, 4885986
1242: AND trunc(p.end_date) <= Nvl(l_end_date_pd, p.end_date)
1243: --AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
1244: AND p.period_status IN ('O', 'F')
1245: AND cn_srp_period_quotas_all.period_id = p.period_id
1246: AND cn_srp_period_quotas_all.org_id = p.org_id);
1247: END IF;
1248:
1249: ELSE -- Quota id IS NOT NULL
1242: AND trunc(p.end_date) <= Nvl(l_end_date_pd, p.end_date)
1243: --AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
1244: AND p.period_status IN ('O', 'F')
1245: AND cn_srp_period_quotas_all.period_id = p.period_id
1246: AND cn_srp_period_quotas_all.org_id = p.org_id);
1247: END IF;
1248:
1249: ELSE -- Quota id IS NOT NULL
1250: -- quota is no longer assigned to the comp plan
1248:
1249: ELSE -- Quota id IS NOT NULL
1250: -- quota is no longer assigned to the comp plan
1251: -- same as the start date us null
1252: DELETE FROM cn_srp_period_quotas_all
1253: WHERE srp_plan_assign_id = x_srp_plan_assign_id
1254: AND quota_id = x_quota_id
1255: AND NVL(x_start_period_id, period_id) <= period_id -- Bug 3848446, Fixed by Jagpreet Singh.
1256: ;
1302: END IF;
1303:
1304: -- quota date range changed. remove the old periods in preparation
1305: -- for insert of new range
1306: DELETE FROM cn_srp_period_quotas_all
1307: WHERE quota_id = x_quota_id
1308: AND EXISTS ( SELECT 1 from cn_period_statuses p
1309: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1310: AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1307: WHERE quota_id = x_quota_id
1308: AND EXISTS ( SELECT 1 from cn_period_statuses p
1309: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1310: AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1311: AND cn_srp_period_quotas_all.period_id = p.period_id
1312: AND p.period_status IN ('O', 'F')
1313: AND cn_srp_period_quotas_all.org_id = p.org_id);
1314: END IF;
1315:
1309: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1310: AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1311: AND cn_srp_period_quotas_all.period_id = p.period_id
1312: AND p.period_status IN ('O', 'F')
1313: AND cn_srp_period_quotas_all.org_id = p.org_id);
1314: END IF;
1315:
1316:
1317: END Delete_Record;
1393: -- Populate_srp_period_quotas_ext
1394: -- Purpose
1395: -- Populate cn_srp_period_quota_ext table
1396: -- We take the following operations as parameters:
1397: -- INSERT : insert records in cn_srp_period_quotas_ext if necessary
1398: -- DELETE : delete all records in cn_srp_period_quotas_ext for certain srp_period_quota_id
1399:
1400:
1401: PROCEDURE populate_srp_period_quotas_ext
1394: -- Purpose
1395: -- Populate cn_srp_period_quota_ext table
1396: -- We take the following operations as parameters:
1397: -- INSERT : insert records in cn_srp_period_quotas_ext if necessary
1398: -- DELETE : delete all records in cn_srp_period_quotas_ext for certain srp_period_quota_id
1399:
1400:
1401: PROCEDURE populate_srp_period_quotas_ext
1402: (x_operation VARCHAR2,
1410:
1411: -- only use this if number_dim not used
1412: CURSOR DIM_NUMBER_CUR IS
1413: select ccf.number_dim
1414: from cn_srp_period_quotas_all cspq, cn_quotas_all cq,
1415: cn_calc_formulas_all ccf
1416: where cspq.srp_period_quota_id = x_srp_period_quota_id
1417: and cq.quota_id = cspq.quota_id
1418: and cq.calc_formula_id = ccf.calc_formula_id ;
1434: l_number_dim := x_number_dim;
1435: END IF;
1436:
1437: FOR i_seq in 2..l_number_dim LOOP
1438: insert into cn_srp_period_quotas_ext_all
1439: (
1440: srp_period_quota_ext_id,
1441: srp_period_quota_id,
1442: input_sequence,
1445: last_update_login,
1446: last_update_date,
1447: last_updated_by,
1448: org_id)
1449: select cn_srp_period_quotas_ext_s.nextval,
1450: x_srp_period_quota_id,
1451: i_seq,
1452: l_user_id,
1453: sysdate,
1454: l_login_id,
1455: sysdate,
1456: l_user_id,
1457: x_org_id
1458: from dual where not exists ( select 1 from cn_srp_period_quotas_ext_all
1459: where srp_period_quota_id = x_srp_period_quota_id
1460: and input_sequence = i_seq );
1461: END LOOP;
1462: ELSIF x_operation = 'DELETE' THEN
1459: where srp_period_quota_id = x_srp_period_quota_id
1460: and input_sequence = i_seq );
1461: END LOOP;
1462: ELSIF x_operation = 'DELETE' THEN
1463: DELETE FROM cn_srp_period_quotas_ext_all
1464: WHERE srp_period_quota_id = x_srp_period_quota_id;
1465: END IF;
1466: END;
1467:
1480: BEGIN
1481:
1482: SELECT nvl(sum(target_amount),0)
1483: INTO x_total
1484: FROM cn_srp_period_quotas_all
1485: WHERE srp_quota_assign_id = x_srp_quota_assign_id
1486: ;
1487: x_total_rtot_db := x_total;
1488:
1527: ,x_period_target_unit_code VARCHAR2) IS
1528:
1529: CURSOR period_quotas(l_interval_number NUMBER, l_period_year NUMBER) IS
1530: SELECT spq.srp_period_quota_id
1531: FROM cn_srp_period_quotas_v spq,
1532: cn_period_statuses cp,
1533: cn_cal_per_int_types_all cpit,
1534: cn_quotas_all cq
1535: WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
1554: CURSOR interval_counts IS
1555: SELECT count(spq.srp_period_quota_id) interval_count,
1556: cpit.interval_number interval_number,
1557: spq.period_year period_year
1558: FROM cn_srp_period_quotas_v spq,
1559: cn_period_statuses cp,
1560: cn_cal_per_int_types_all cpit,
1561: cn_quotas_all cq
1562: WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
1672:
1673: l_running_performance_goal := l_running_performance_goal +
1674: l_period_performance_goal;
1675:
1676: UPDATE cn_srp_period_quotas_all
1677: SET
1678: target_amount = round(nvl(l_period_target, 0), g_ext_precision),
1679: itd_target = round(nvl(l_running_total_target,0), g_ext_precision),
1680: period_payment = round(nvl(l_period_payment,0), g_ext_precision),
1702: x_quota_id NUMBER) IS
1703:
1704: BEGIN
1705:
1706: cn_srp_period_quotas_pkg.delete_record
1707: (
1708: x_srp_plan_assign_id => x_srp_plan_assign_id
1709: ,x_quota_id => x_quota_id
1710: ,x_start_period_id => null
1709: ,x_quota_id => x_quota_id
1710: ,x_start_period_id => null
1711: ,x_end_period_id => null);
1712:
1713: cn_srp_period_quotas_pkg.insert_record
1714: (
1715: x_srp_plan_assign_id => x_srp_plan_assign_id
1716: ,x_quota_id => x_quota_id
1717: ,x_start_period_id => null
1718: ,x_end_period_id => null);
1719:
1720: END synch_target;
1721:
1722: END CN_SRP_PERIOD_QUOTAS_PKG;