[Home] [Help]
199:
200: CURSOR l_get_period_dates_csr IS
201: SELECT start_date
202: , end_date
203: FROM cn_period_statuses_all
204: WHERE period_id = p_period_id AND org_id = p_org_id;
205:
206: l_counter NUMBER;
207: l_start_date DATE;
1126: FUNCTION get_period(p_date DATE, p_period_set_id NUMBER, p_period_type_id NUMBER, p_org_id NUMBER)
1127: RETURN NUMBER IS
1128: CURSOR l_date_period_csr IS
1129: SELECT period_id
1130: FROM cn_period_statuses_all
1131: WHERE period_set_id = p_period_set_id
1132: AND period_type_id = p_period_type_id
1133: AND p_date BETWEEN start_date AND end_date
1134: AND org_id = p_org_id;
1134: AND org_id = p_org_id;
1135:
1136: CURSOR l_null_date_period_csr IS
1137: SELECT MAX(period_id)
1138: FROM cn_period_statuses_all
1139: WHERE period_set_id = p_period_set_id
1140: AND period_type_id = p_period_type_id
1141: AND period_status = 'O'
1142: AND org_id = p_org_id;
1185: SELECT acc.period_id
1186: , DECODE(acc.period_id, l_start_period_id, p_start_date, acc.start_date) start_date
1187: , DECODE(acc.period_id, l_end_period_id, NVL(p_end_date, acc.end_date), acc.end_date)
1188: end_date
1189: FROM cn_period_statuses_all acc
1190: WHERE acc.period_set_id = p_period_set_id
1191: AND acc.period_type_id = p_period_type_id
1192: AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
1193: AND acc.period_status = 'O'
1285: , p_org_id IN NUMBER
1286: ) IS
1287: CURSOR l_rollup_flag_periods_csr IS
1288: SELECT period_id, start_date, end_date
1289: FROM cn_period_statuses_all
1290: WHERE period_set_id = p_period_set_id
1291: AND period_type_id = p_period_type_id
1292: AND period_status = 'O'
1293: AND org_id = p_org_id;
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
1858: AND sip.period_id = spq.period_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
2208: AND sip.period_id = spq.period_id
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
2445: AND sip.period_id = spq.period_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
3600: CURSOR affected_periods IS
3601: SELECT cpit.cal_period_id
3602: , ps.start_date
3603: , ps.end_date
3604: FROM cn_cal_per_int_types_all cpit, cn_period_statuses_all ps
3605: WHERE (
3606: cpit.interval_number = p_old_interval_number
3607: OR cpit.interval_number = p_new_interval_number
3608: )
3689:
3690: CURSOR dates(p_org_id NUMBER) IS
3691: SELECT start_date
3692: , end_date
3693: FROM cn_period_statuses_all
3694: WHERE period_id = rec.cal_period_id AND org_id = p_org_id;
3695:
3696: l_start_date DATE;
3697: l_end_date DATE;
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
3761: AND (
4023: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
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
4181: SELECT spq.salesrep_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
4623: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
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