[Home] [Help]
36: and :b1 <= nvl(assign.end_date, :b2)
37: and :b3 >= assign.start_date and s.org_id = :b4';
38:
39: cursor get_year is
40: select period_year from cn_period_statuses
41: where period_id = p_period_id and org_id=p_org_id;
42:
43: cursor get_salesrep (a_user_id number,a_org_id number) is
44: select s.salesrep_id
117: * Fix made for bug 4368747
118: */
119:
120: /*
121: select start_date into l_year_start_date from cn_period_statuses
122: where period_id = p_period_id - mod(p_period_id, 1000) + 1;
123: */
124:
125: select min(start_date) into l_year_start_date from cn_period_statuses cps,cn_repositories cr
121: select start_date into l_year_start_date from cn_period_statuses
122: where period_id = p_period_id - mod(p_period_id, 1000) + 1;
123: */
124:
125: select min(start_date) into l_year_start_date from cn_period_statuses cps,cn_repositories cr
126: where cps.period_year=l_year and cr.period_set_id=cps.period_set_id
127: and cr.period_type_id=cps.period_type_id and cr.org_id=cps.org_id
128: and cr.org_id=p_org_id;
129: /**
128: and cr.org_id=p_org_id;
129: /**
130: * End of Fix made for bug 4368747
131: */
132: select end_date into l_period_end_date from cn_period_statuses
133: where period_id = p_period_id and org_id=p_org_id;
134:
135: open main_cursor for l_query using l_year_start_date, l_year_start_date,
136: l_period_end_date, p_org_id, l_user_id;
355: BEGIN
356: -- get start date of year and end date of period
357: select min(p2.start_date), min(p2.period_id)
358: into l_year_start_date, l_year_start_period
359: from cn_period_statuses p1, cn_period_statuses p2
360: where p1.period_id = p_period_id
361: and p1.period_year = p2.period_year
362: and p1.period_set_id = p2.period_set_id
363: and p1.period_type_id = p2.period_type_id
362: and p1.period_set_id = p2.period_set_id
363: and p1.period_type_id = p2.period_type_id
364: and p1.org_id = p2.org_id and p1.org_id = p_org_id;
365:
366: select end_date into l_period_end_date from cn_period_statuses
367: where period_id = p_period_id and org_id=p_org_id;
368:
369: for p in get_plans loop
370: l_count := l_count + 1;
415: nvl(inv.credit,0) credit,
416: nvl(inv.earnings,0) earnings,
417: nvl(inv.target,0) target,
418: nvl(inv.itd_target,0) itd_target
419: from cn_period_statuses ps, cn_repositories r, cn_period_statuses ps2,
420: (SELECT ps.period_id period_id,
421: nvl(sum(cspq.perf_achieved_ptd),0) credit,
422: nvl(sum(cspq.commission_payed_ptd),0) earnings,
423: nvl(sum(cspq.target_amount),0) target,
423: nvl(sum(cspq.target_amount),0) target,
424: nvl(sum(cspq.itd_target),0) itd_target
425: FROM cn_srp_period_quotas cspq,
426: cn_quotas_all quota,
427: cn_period_statuses ps,
428: cn_repositories r,
429: cn_period_statuses ps2
430: WHERE cspq.srp_plan_assign_id = p_srp_plan_assign_id
431: AND cspq.quota_id = quota.quota_id
425: FROM cn_srp_period_quotas cspq,
426: cn_quotas_all quota,
427: cn_period_statuses ps,
428: cn_repositories r,
429: cn_period_statuses ps2
430: WHERE cspq.srp_plan_assign_id = p_srp_plan_assign_id
431: AND cspq.quota_id = quota.quota_id
432: AND quota.credit_type_id = p_credit_type_id
433: AND quota.quota_group_code = p_quota_group_code
464: SELECT nvl(sum(nvl(sqa.target * it.ct,0)),0) target
465: from cn_srp_quota_assigns sqa,
466: cn_quotas q,
467: (select count(distinct interval_number) ct, it.interval_type_id
468: from cn_cal_per_int_types it, cn_period_statuses ps
469: where it.cal_period_id = ps.period_id
470: and it.org_id = ps.org_id
471: and ps.period_year = a_period_year
472: and ps.org_id = p_org_id
539:
540: x_itd_unachieved_quota := l_rollover;
541: x_itd_tot_target := l_itd_target + l_total_rollover;
542:
543: SELECT period_year into l_period_year FROM cn_period_statuses p
544: WHERE p.period_id=p_period_id and p.org_id = p_org_id;
545: -- annual_quota
546: open annual_quota_cur(l_period_year);
547: fetch annual_quota_cur into x_annual_quota;
658: BEGIN
659: -- get start date of year and end date of period
660: select min(p2.start_date), min(p2.period_id)
661: into l_year_start_date, l_year_start_period
662: from cn_period_statuses p1, cn_period_statuses p2
663: where p1.period_id = p_period_id
664: and p1.period_year = p2.period_year
665: and p1.period_set_id = p2.period_set_id
666: and p1.period_type_id = p2.period_type_id
665: and p1.period_set_id = p2.period_set_id
666: and p1.period_type_id = p2.period_type_id
667: and p1.org_id = p2.org_id and p1.org_id =p_org_id;
668:
669: select end_date into l_period_end_date from cn_period_statuses
670: where period_id = p_period_id and org_id =p_org_id;
671:
672: open get_all_plans(l_year_start_date, l_period_end_date,p_org_id, p_period_id, p_period_id,l_year_start_period, p_credit_type_id);
673: fetch get_all_plans bulk collect into get_all_plans_tbl;
751: BEGIN
752: -- get start date of year and end date of period
753: select min(p2.start_date), min(p2.period_id)
754: into l_year_start_date, l_year_start_period
755: from cn_period_statuses p1, cn_period_statuses p2
756: where p1.period_id = p_period_id
757: and p1.period_year = p2.period_year
758: and p1.period_set_id = p2.period_set_id
759: and p1.period_type_id = p2.period_type_id
758: and p1.period_set_id = p2.period_set_id
759: and p1.period_type_id = p2.period_type_id
760: and p1.org_id = p2.org_id and p1.org_id=p_org_id;
761:
762: select end_date into l_period_end_date from cn_period_statuses
763: where period_id = p_period_id and org_id =p_org_id;
764:
765: open get_all_plans(l_year_start_date, l_period_end_date,p_org_id, p_period_id, p_period_id, p_credit_type_id, p_analyst_id);
766: fetch get_all_plans bulk collect into get_all_plans_tbl;
826: and quota_id > 0 and org_id=p_org_id;
827: begin
828: select min(p2.start_date), min(p2.period_id)
829: into l_year_start_date, l_year_start_period
830: from cn_period_statuses p1, cn_period_statuses p2
831: where p1.period_id = p_period_id
832: and p1.period_year = p2.period_year
833: and p1.period_set_id = p2.period_set_id
834: and p1.period_type_id = p2.period_type_id
834: and p1.period_type_id = p2.period_type_id
835: and p1.org_id = p2.org_id
836: and p1.org_id = p_org_id;
837:
838: select end_date into l_period_end_date from cn_period_statuses
839: where period_id = p_period_id and org_id = p_org_id;
840:
841: for s in cur_srp_plan_assign_id
842: loop
941:
942: begin
943: select min(p2.start_date), min(p2.period_id)
944: into l_year_start_date, l_year_start_period
945: from cn_period_statuses p1, cn_period_statuses p2
946: where p1.period_id = p_period_id
947: and p1.period_year = p2.period_year
948: and p1.period_set_id = p2.period_set_id
949: and p1.period_type_id = p2.period_type_id
948: and p1.period_set_id = p2.period_set_id
949: and p1.period_type_id = p2.period_type_id
950: and p1.org_id = p2.org_id and p1.org_id=p_org_id;
951:
952: select end_date into l_period_end_date from cn_period_statuses
953: where period_id = p_period_id and org_id = p_org_id;
954:
955: open quota_groups(p_org_id);
956: fetch quota_groups bulk collect into quota_groups_tbl;
1062:
1063: begin
1064: select min(p2.start_date), min(p2.period_id)
1065: into l_year_start_date, l_year_start_period
1066: from cn_period_statuses p1, cn_period_statuses p2
1067: where p1.period_id = p_period_id
1068: and p1.period_year = p2.period_year
1069: and p1.period_set_id = p2.period_set_id
1070: and p1.period_type_id = p2.period_type_id
1069: and p1.period_set_id = p2.period_set_id
1070: and p1.period_type_id = p2.period_type_id
1071: and p1.org_id = p2.org_id and p1.org_id=p_org_id;
1072:
1073: select end_date into l_period_end_date from cn_period_statuses
1074: where period_id = p_period_id and org_id = p_org_id;
1075:
1076: open quota_groups(p_org_id);
1077: fetch quota_groups bulk collect into quota_groups_tbl;