[Home] [Help]
29: l_rolequery_result number;
30:
31: l_query varchar2(4000) :=
32: 'SELECT distinct s.name, s.salesrep_id FROM jtf_rs_resource_extns re,
33: cn_salesreps s,cn_srp_plan_assigns assign WHERE re.resource_id = s.resource_id
34: and s.org_id = assign.org_id and re.category <> ''TBH''
35: and s.salesrep_id > 0 and s.salesrep_id = assign.salesrep_id
36: and :b1 <= nvl(assign.end_date, :b2)
37: and :b3 >= assign.start_date and s.org_id = :b4';
322: r.name role_name,
323: cp.name plan_name,
324: assign.start_date start_date,
325: assign.end_date end_date
326: FROM cn_srp_plan_assigns assign,
327: cn_roles r,
328: cn_comp_plans cp
329: WHERE l_year_start_date <= nvl(assign.end_date, l_year_start_date)
330: AND l_period_end_date >= assign.start_date
339: l_start_pd NUMBER,
340: l_end_pd NUMBER,
341: l_org_id NUMBER) IS
342: select nvl(sum(balance2_dtd),0)
343: from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
344: where spa.srp_plan_assign_id = l_srp_plan_assign_id
345: and sp.srp_plan_assign_id = spa.srp_plan_assign_id
346: and sp.salesrep_id = spa.salesrep_id
347: and sp.quota_id = q.quota_id
600: assign.end_date end_date,
601: assign.salesrep_id salesrep_id,
602: nvl(inv_ptd.earnings,0) ptd_earnings,
603: nvl(inv_ytd.earnings,0) ytd_earnings
604: FROM cn_srp_plan_assigns assign,
605: cn_roles r,
606: cn_comp_plans cp,
607: (select spa.srp_plan_assign_id srp_plan_assign_id,
608: nvl(sum(balance2_dtd),0) earnings
605: cn_roles r,
606: cn_comp_plans cp,
607: (select spa.srp_plan_assign_id srp_plan_assign_id,
608: nvl(sum(balance2_dtd),0) earnings
609: from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
610: where sp.srp_plan_assign_id = spa.srp_plan_assign_id
611: and sp.salesrep_id = spa.salesrep_id
612: and sp.org_id = spa.org_id
613: and sp.org_id = q.org_id
618: and period_id between c_period_start_pd and c_end_pd
619: group by spa.srp_plan_assign_id) inv_ptd ,
620: (select spa.srp_plan_assign_id srp_plan_assign_id,
621: nvl(sum(balance2_dtd),0) earnings
622: from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
623: where sp.srp_plan_assign_id = spa.srp_plan_assign_id
624: and sp.salesrep_id = spa.salesrep_id
625: and sp.org_id = spa.org_id
626: and sp.org_id = q.org_id
715: assign.start_date start_date,
716: assign.end_date end_date,
717: assign.salesrep_id salesrep_id,
718: nvl(inv.earnings,0) earnings
719: FROM cn_srp_plan_assigns assign,
720: cn_roles r,
721: cn_comp_plans cp,
722: (select spa.srp_plan_assign_id srp_plan_assign_id,
723: nvl(sum(balance2_dtd),0) earnings
720: cn_roles r,
721: cn_comp_plans cp,
722: (select spa.srp_plan_assign_id srp_plan_assign_id,
723: nvl(sum(balance2_dtd),0) earnings
724: from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
725: where sp.srp_plan_assign_id = spa.srp_plan_assign_id
726: and sp.salesrep_id = spa.salesrep_id
727: and spa.org_id = p_org_id--and sp.org_id = spa.org_id
728: and q.org_id = p_org_id --and sp.org_id = q.org_id
807: x_quota_id number := null;
808: cursor cur_srp_plan_assign_id
809: is
810: SELECT assign.srp_plan_assign_id
811: FROM cn_srp_plan_assigns assign,
812: cn_roles r,
813: cn_comp_plans cp
814: WHERE l_year_start_date <= nvl(assign.end_date, l_year_start_date)
815: AND l_period_end_date >= assign.start_date
903: c_period_end_date DATE,
904: c_org_id NUMBER)
905: is
906: SELECT assign.srp_plan_assign_id
907: FROM cn_srp_plan_assigns assign,
908: cn_roles r,
909: cn_comp_plans cp
910: WHERE c_year_start_date <= nvl(assign.end_date, c_year_start_date)
911: AND c_period_end_date >= assign.start_date
1031: c_org_id NUMBER,
1032: c_analyst_id NUMBER)
1033: is
1034: SELECT assign.srp_plan_assign_id
1035: FROM cn_srp_plan_assigns assign,
1036: cn_roles r,
1037: cn_comp_plans cp
1038: WHERE c_year_start_date <= nvl(assign.end_date, c_year_start_date)
1039: AND c_period_end_date >= assign.start_date
1152: assign.start_date start_date,
1153: assign.end_date end_date,
1154: q.quota_group_code quota_group_code
1155: FROM cn_srp_periods srp,
1156: cn_srp_plan_assigns assign,
1157: cn_roles r,
1158: cn_comp_plans cp,
1159: cn_payment_worksheets w,
1160: cn_quotas_all q