[Home] [Help]
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
233: AND spq.quota_id = cq.quota_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
260: AND p.quota_id = cq.quota_id
528: ,qa.org_id
529: FROM cn_srp_quota_assigns_all qa
530: ,cn_period_quotas_all pq
531: ,cn_srp_plan_assigns_all pa
532: ,cn_period_statuses p
533: WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
534: AND pa.srp_plan_assign_id = x_srp_plan_assign_id
535: AND pa.srp_plan_assign_id = qa.srp_plan_assign_id --bugfix3633222
536: AND qa.quota_id = pq.quota_id
640: ,qa.org_id
641: FROM cn_srp_quota_assigns_all qa
642: ,cn_period_quotas_all pq
643: ,cn_srp_plan_assigns_all pa
644: ,cn_period_statuses p
645: WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
646: AND pa.srp_plan_assign_id = qa.srp_plan_assign_id
647: AND qa.quota_id = x_quota_id
648: AND greatest(pa.start_date, p.start_date) <=
733: ,cn_quotas_all q
734: ,cn_srp_plan_assigns_all pa
735: ,cn_acc_period_statuses_v p
736: -- bug fix 4042235
737: ,cn_period_statuses p2
738: ,cn_period_statuses p3
739:
740: WHERE qa.srp_plan_assign_id = pa.srp_plan_assign_id
741: AND qa.quota_id = x_quota_id
734: ,cn_srp_plan_assigns_all pa
735: ,cn_acc_period_statuses_v p
736: -- bug fix 4042235
737: ,cn_period_statuses p2
738: ,cn_period_statuses p3
739:
740: WHERE qa.srp_plan_assign_id = pa.srp_plan_assign_id
741: AND qa.quota_id = x_quota_id
742: AND q.quota_id = x_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
903: AND spq.quota_id = cq.quota_id
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
930: AND spq.quota_id = cq.quota_id
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
951: AND acc.period_status IN ('O', 'F')
1067: WHERE spq.salesrep_id = x_salesrep_id
1068: AND spq.quota_id = x_quota_id
1069: AND exists
1070: (Select 1
1071: from cn_period_statuses aps
1072: where x_end_date between aps.start_date and aps.end_date
1073: AND aps.period_id = spq.period_id
1074: AND aps.period_status IN ('O', 'F')
1075: AND aps.org_id = spq.org_id)
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))
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')
1156: balance2_dtd, balance3_ctd, srp_period_id
1157: from cn_srp_periods_all
1158: where quota_id = x_quota_id
1159: AND org_id = l_org_id
1160: AND EXISTS ( SELECT 1 from cn_period_statuses p
1161: WHERE trunc(p.start_date) >= trunc(nvl(x_start_date,p.start_date))
1162: AND trunc(p.end_date) <= trunc(nvl(x_end_date ,p.end_date))
1163: AND cn_srp_periods_all.period_id = p.period_id
1164: AND p.period_status IN ('O', 'F')
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))
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))
1311: AND cn_srp_period_quotas_all.period_id = p.period_id
1312: AND p.period_status IN ('O', 'F')
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
1536: AND spq.quota_id = cq.quota_id
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
1563: AND spq.quota_id = cq.quota_id