[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
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
2206: AND sip.period_id = spq.period_id
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
2443: AND sip.period_id = spq.period_id
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
3598: CURSOR affected_periods IS
3599: SELECT cpit.cal_period_id
3600: , ps.start_date
3601: , ps.end_date
3602: FROM cn_cal_per_int_types_all cpit, cn_period_statuses_all ps
3603: WHERE (
3604: cpit.interval_number = p_old_interval_number
3605: OR cpit.interval_number = p_new_interval_number
3606: )
3687:
3688: CURSOR dates(p_org_id NUMBER) IS
3689: SELECT start_date
3690: , end_date
3691: FROM cn_period_statuses_all
3692: WHERE period_id = rec.cal_period_id AND org_id = p_org_id;
3693:
3694: l_start_date DATE;
3695: l_end_date DATE;
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
3759: AND (
4019: , DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
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
4177: SELECT spq.salesrep_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
4618: , DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
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