[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
2197: SELECT DISTINCT spq.salesrep_id
2198: , spq.period_id
2199: , spq.quota_id
2200: FROM cn_srp_period_quotas_all spq
2201: , cn_srp_intel_periods_all sip
2202: , cn_period_statuses_all acc
2203: WHERE spq.quota_id = p_object_id
2204: AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2205: AND sip.salesrep_id = spq.salesrep_id
2434: , l_end_period_id, NVL(l_end_date, acc.end_date)
2435: , acc.end_date
2436: ) end_date
2437: FROM cn_srp_period_quotas_all spq
2438: , cn_srp_intel_periods_all sip
2439: , cn_period_statuses_all acc
2440: WHERE spq.quota_id = p_object_id
2441: AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
2442: AND sip.salesrep_id = spq.salesrep_id
2611: , acc.period_id period_id
2612: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2613: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2614: end_date
2615: FROM cn_srp_roles sr, cn_srp_intel_periods intel, cn_period_statuses acc
2616: WHERE sr.role_id = l_role_id
2617: AND sr.org_id = p_org_id
2618: AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2619: AND acc.period_status = 'O'
2748: , acc.period_id period_id
2749: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2750: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2751: end_date
2752: FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2753: WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2754: AND acc.org_id = p_org_id
2755: AND acc.period_status = 'O'
2756: AND intel.salesrep_id = l_salesrep_id
2882: , acc.period_id period_id
2883: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
2884: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
2885: end_date
2886: FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
2887: WHERE acc.org_id = p_org_id
2888: AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
2889: AND acc.period_status = 'O'
2890: AND intel.salesrep_id = l_salesrep_id
3013: SELECT DISTINCT spq.salesrep_id
3014: , spq.period_id
3015: , spq.quota_id
3016: FROM cn_quotas_all cq, cn_srp_period_quotas_all spq
3017: , cn_srp_intel_periods_all intel
3018: WHERE cq.calc_formula_id = p_object_id
3019: AND cq.org_id = p_org_id
3020: AND spq.quota_id = cq.quota_id
3021: AND intel.salesrep_id = spq.salesrep_id
3164: CURSOR affected_srp_period_quotas IS
3165: SELECT DISTINCT spq.salesrep_id
3166: , spq.period_id
3167: , spq.quota_id
3168: FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3169: WHERE spq.quota_id IN(
3170: SELECT rt_assign.quota_id
3171: FROM cn_rate_sch_dims_all rt, cn_rt_quota_asgns_all rt_assign
3172: WHERE rt.rate_dimension_id = p_object_id
3322: CURSOR affected_srp_period_quotas IS
3323: SELECT DISTINCT spq.salesrep_id
3324: , spq.period_id
3325: , spq.quota_id
3326: FROM cn_srp_period_quotas spq, cn_srp_intel_periods intel
3327: WHERE spq.quota_id IN(
3328: SELECT rt_assign.quota_id
3329: FROM cn_rt_quota_asgns rt_assign
3330: WHERE rt_assign.rate_schedule_id = p_dep_object_id)
3463: CURSOR affected_srp_period_quotas IS
3464: SELECT DISTINCT spq.salesrep_id
3465: , spq.period_id
3466: , spq.quota_id
3467: FROM cn_quotas_all q, cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
3468: WHERE q.interval_type_id = p_interval_type_id
3469: AND q.org_id = p_org_id
3470: AND p_end_date >= q.start_date
3471: AND (q.end_date IS NULL OR p_start_date <= q.end_date)
3750: , acc.period_id
3751: , acc.start_date
3752: , acc.end_date
3753: FROM cn_srp_plan_assigns_all spa
3754: , cn_srp_intel_periods_all intel
3755: , cn_period_statuses_all acc
3756: WHERE spa.comp_plan_id = p_object_id -- comp_plan_id
3757: AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
3758: AND acc.org_id = spa.org_id
3864: CURSOR affected_srp_period_quotas IS
3865: SELECT spq.salesrep_id
3866: , spq.period_id
3867: , spq.quota_id
3868: FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods intel
3869: WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
3870: AND intel.salesrep_id = spq.salesrep_id
3871: AND intel.period_id = spq.period_id
3872: AND intel.org_id = spq.org_id
4020: end_date
4021: FROM cn_srp_quota_rules_all rule
4022: , cn_srp_period_quotas_all spq
4023: , cn_period_statuses_all acc
4024: , cn_srp_intel_periods_all intel
4025: WHERE rule.srp_quota_rule_id = p_srp_object_id --p_srp_quota_rule_id
4026: AND spq.srp_plan_assign_id = rule.srp_plan_assign_id
4027: AND spq.srp_quota_assign_id = rule.srp_quota_assign_id
4028: AND acc.period_id = spq.period_id
4178: , spq.period_id
4179: , spq.quota_id
4180: FROM cn_srp_period_quotas_all spq
4181: , cn_period_statuses_all acc
4182: , cn_srp_intel_periods_all intel
4183: WHERE spq.srp_quota_assign_id = p_srp_object_id
4184: AND acc.period_id = spq.period_id
4185: AND acc.org_id = spq.org_id
4186: AND (acc.period_id BETWEEN l_srp_start_period_id AND l_srp_end_period_id)
4333: CURSOR affected_srp_period_quotas IS
4334: SELECT spq.salesrep_id
4335: , spq.period_id
4336: , spq.quota_id
4337: FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
4338: WHERE spq.srp_period_quota_id = p_srp_object_id -- p_srp_period_quota_id
4339: AND intel.salesrep_id = spq.salesrep_id
4340: AND intel.period_id = spq.period_id
4341: AND intel.org_id = spq.org_id
4619: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
4620: end_date
4621: FROM cn_srp_period_quotas_all spq
4622: , cn_period_statuses_all acc
4623: , cn_srp_intel_periods_all intel
4624: WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
4625: AND acc.period_id = spq.period_id
4626: AND acc.org_id = spq.org_id
4627: AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
4872: ) IS
4873: SELECT p.period_id
4874: , GREATEST(p_start_date, p.start_date) start_date
4875: , DECODE(p_end_date, NULL, p.end_date, LEAST(p_end_date, p.end_date)) end_date
4876: FROM cn_srp_intel_periods_all p
4877: WHERE p.salesrep_id = p_salesrep_id
4878: AND p.org_id = p_org_id
4879: AND (p_end_date IS NULL OR p.start_date <= p_end_date)
4880: AND (p.end_date >= p_start_date);