[Home] [Help]
621: AND cpit2.cal_period_id > p_period_id
622: AND cpit2.org_id = p_org_id
623: AND EXISTS(
624: SELECT 1
625: FROM cn_srp_intel_periods_all
626: WHERE salesrep_id = p_salesrep_id
627: AND period_id = cpit2.cal_period_id
628: AND org_id = p_org_id
629: AND processing_status_code <> 'CLEAN');
657: )))
658: AND cpit2.cal_period_id > p_period_id
659: AND EXISTS(
660: SELECT 1
661: FROM cn_srp_intel_periods_all intel
662: WHERE intel.salesrep_id = p_salesrep_id
663: AND intel.org_id = p_org_id
664: AND intel.period_id = cpit2.cal_period_id
665: AND intel.processing_status_code <> 'CLEAN');
1849: , l_end_period_id, NVL(l_end_date, acc.end_date)
1850: , acc.end_date
1851: ) end_date
1852: FROM cn_srp_period_quotas_all spq
1853: , cn_srp_intel_periods_all sip
1854: , cn_period_statuses_all acc
1855: WHERE spq.quota_id = p_object_id
1856: AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
1857: AND sip.salesrep_id = spq.salesrep_id
2199: SELECT DISTINCT spq.salesrep_id
2200: , spq.period_id
2201: , spq.quota_id
2202: FROM cn_srp_period_quotas_all spq
2203: , cn_srp_intel_periods_all sip
2204: , cn_period_statuses_all acc
2205: WHERE spq.quota_id = p_object_id
2206: AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2207: AND sip.salesrep_id = spq.salesrep_id
2436: , l_end_period_id, NVL(l_end_date, acc.end_date)
2437: , acc.end_date
2438: ) end_date
2439: FROM cn_srp_period_quotas_all spq
2440: , cn_srp_intel_periods_all sip
2441: , cn_period_statuses_all acc
2442: WHERE spq.quota_id = p_object_id
2443: AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2444: AND sip.salesrep_id = spq.salesrep_id
2750: , acc.period_id period_id
2751: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2752: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2753: end_date
2754: FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2755: WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2756: AND acc.org_id = p_org_id
2757: AND acc.period_status = 'O'
2758: AND intel.salesrep_id = l_salesrep_id
2884: , acc.period_id period_id
2885: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2886: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2887: end_date
2888: FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2889: WHERE acc.org_id = p_org_id
2890: AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2891: AND acc.period_status = 'O'
2892: AND intel.salesrep_id = l_salesrep_id
3015: SELECT DISTINCT spq.salesrep_id
3016: , spq.period_id
3017: , spq.quota_id
3018: FROM cn_quotas_all cq, cn_srp_period_quotas_all spq
3019: , cn_srp_intel_periods_all intel
3020: WHERE cq.calc_formula_id = p_object_id
3021: AND cq.org_id = p_org_id
3022: AND spq.quota_id = cq.quota_id
3023: AND intel.salesrep_id = spq.salesrep_id
3166: CURSOR affected_srp_period_quotas IS
3167: SELECT DISTINCT spq.salesrep_id
3168: , spq.period_id
3169: , spq.quota_id
3170: FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3171: WHERE spq.quota_id IN(
3172: SELECT rt_assign.quota_id
3173: FROM cn_rate_sch_dims_all rt, cn_rt_quota_asgns_all rt_assign
3174: WHERE rt.rate_dimension_id = p_object_id
3465: CURSOR affected_srp_period_quotas IS
3466: SELECT DISTINCT spq.salesrep_id
3467: , spq.period_id
3468: , spq.quota_id
3469: FROM cn_quotas_all q, cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3470: WHERE q.interval_type_id = p_interval_type_id
3471: AND q.org_id = p_org_id
3472: AND p_end_date >= q.start_date
3473: AND (q.end_date IS NULL OR p_start_date <= q.end_date)
3752: , acc.period_id
3753: , acc.start_date
3754: , acc.end_date
3755: FROM cn_srp_plan_assigns_all spa
3756: , cn_srp_intel_periods_all intel
3757: , cn_period_statuses_all acc
3758: WHERE spa.comp_plan_id = p_object_id -- comp_plan_id
3759: AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
3760: AND acc.org_id = spa.org_id
4024: end_date
4025: FROM cn_srp_quota_rules_all rule
4026: , cn_srp_period_quotas_all spq
4027: , cn_period_statuses_all acc
4028: , cn_srp_intel_periods_all intel
4029: WHERE rule.srp_quota_rule_id = p_srp_object_id --p_srp_quota_rule_id
4030: AND spq.srp_plan_assign_id = rule.srp_plan_assign_id
4031: AND spq.srp_quota_assign_id = rule.srp_quota_assign_id
4032: AND acc.period_id = spq.period_id
4182: , spq.period_id
4183: , spq.quota_id
4184: FROM cn_srp_period_quotas_all spq
4185: , cn_period_statuses_all acc
4186: , cn_srp_intel_periods_all intel
4187: WHERE spq.srp_quota_assign_id = p_srp_object_id
4188: AND acc.period_id = spq.period_id
4189: AND acc.org_id = spq.org_id
4190: AND (acc.period_id BETWEEN l_srp_start_period_id AND l_srp_end_period_id)
4337: CURSOR affected_srp_period_quotas IS
4338: SELECT spq.salesrep_id
4339: , spq.period_id
4340: , spq.quota_id
4341: FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
4342: WHERE spq.srp_period_quota_id = p_srp_object_id -- p_srp_period_quota_id
4343: AND intel.salesrep_id = spq.salesrep_id
4344: AND intel.period_id = spq.period_id
4345: AND intel.org_id = spq.org_id
4624: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
4625: end_date
4626: FROM cn_srp_period_quotas_all spq
4627: , cn_period_statuses_all acc
4628: , cn_srp_intel_periods_all intel
4629: WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
4630: AND acc.period_id = spq.period_id
4631: AND acc.org_id = spq.org_id
4632: AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
4877: ) IS
4878: SELECT p.period_id
4879: , GREATEST(p_start_date, p.start_date) start_date
4880: , DECODE(p_end_date, NULL, p.end_date, LEAST(p_end_date, p.end_date)) end_date
4881: FROM cn_srp_intel_periods_all p
4882: WHERE p.salesrep_id = p_salesrep_id
4883: AND p.org_id = p_org_id
4884: AND (p_end_date IS NULL OR p.start_date <= p_end_date)
4885: AND (p.end_date >= p_start_date);