[Home] [Help]
29: BEGIN
30: IF (p_quota_count = 1) AND (x_role_count = 1) THEN
31: x_role_count := 2;
32:
33: UPDATE cn_commission_lines_all cl
34: SET cl.srp_plan_assign_id = p_srp_plan_assign_id,
35: cl.quota_id = p_quota_id,
36: cl.quota_rule_id = p_quota_rule_id,
37: cl.status = 'POP',
53: cl.direct_salesrep_id <> cl.credited_salesrep_id ) );
54: ELSE
55:
56: -- create new transaction lines for plan_quota
57: INSERT INTO cn_commission_lines_all
58: ( commission_line_id,
59: commission_header_id,
60: CREDITED_SALESREP_ID,
61: credited_comp_group_id,
101: g_creation_date,
102: g_created_by,
103: g_org_id,
104: rollup_level
105: FROM cn_commission_lines_all cl
106: WHERE cl.credited_salesrep_id = p_salesrep_id
107: AND cl.processed_date = p_processed_date
108: AND cl.processed_period_id = p_processed_period_id
109: AND cl.credited_comp_group_id = p_comp_group_id
247: pb.end_period_id,
248: pb.start_date,
249: pb.end_date,
250: cl.revenue_class_id
251: from cn_commission_lines_all cl,
252: cn_process_batches_all pb
253: where pb.physical_batch_id = p_physical_batch_id
254: and cl.credited_salesrep_id = pb.salesrep_id
255: and cl.processed_period_id between pb.period_id AND pb.end_period_id
377: cl.processed_date,
378: ch.role_id,
379: ch.quota_id,
380: ch.revenue_class_id
381: FROM cn_commission_lines_all cl,
382: cn_commission_headers_all ch,
383: cn_process_batches_all pb
384: WHERE pb.physical_batch_id = p_physical_batch_id
385: AND cl.credited_salesrep_id = pb.salesrep_id
466:
467: -- get credits from team members
468: FOR srp IN team_mbrs LOOP
469: FOR mbr IN other_mbrs(srp.comp_team_id, srp.salesrep_id, srp.start_date, srp.end_date) LOOP
470: INSERT INTO cn_commission_lines_all
471: (commission_line_id,
472: commission_header_id,
473: direct_salesrep_id,
474: pre_processed_code,
504: fnd_global.user_id,
505: sysdate,
506: g_org_id,
507: 0
508: from cn_commission_lines_all cl
509: where cl.credited_salesrep_id = mbr.salesrep_id
510: and cl.processed_date between mbr.start_date and mbr.end_date
511: and cl.created_during = 'ROLL'
512: AND cl.status <> 'OBSOLETE'
511: and cl.created_during = 'ROLL'
512: AND cl.status <> 'OBSOLETE'
513: and cl.org_id = g_org_id
514: and not exists (select 1
515: from cn_commission_lines_all
516: where commission_header_id = cl.commission_header_id
517: and credited_salesrep_id = srp.salesrep_id));
518: IF (SQL%found) THEN
519: cn_mark_events_pkg.mark_notify
565: l_start_date := plan.start_date;
566: l_end_date := plan.end_date;
567: g_end_date := l_end_date;
568:
569: update /*+ index(cl cn_commission_lines_n7) */ cn_commission_lines_all cl
570: set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
571: cl.quota_id = plan.quota_id,
572: cl.quota_rule_id = plan.quota_rule_id,
573: cl.status = 'POP',
597: else
598: l_end_date := plan.end_date;
599: end if;
600:
601: merge into cn_commission_lines_all cl
602: using (select commission_line_id,
603: commission_header_id,
604: direct_salesrep_id,
605: pre_processed_code,
621: creation_date,
622: created_by,
623: org_id,
624: rollup_level
625: from cn_commission_lines_all cl2
626: where credited_salesrep_id = rep.credited_salesrep_id
627: and processed_date between l_start_date and l_end_date
628: and processed_period_id between rep.period_id and rep.end_period_id
629: and credited_comp_group_id = rep.credited_comp_group_id
630: and created_during in ('ROLL', 'TROLL')
631: and status <> 'OBSOLETE'
632: and org_id = g_org_id
633: and revenue_class_id = rep.revenue_class_id
634: and not exists (select 1 from cn_commission_lines_all
635: where credited_salesrep_id = cl2.credited_salesrep_id
636: and commission_header_id = cl2.commission_header_id
637: and srp_plan_assign_id = plan.srp_plan_assign_id
638: and quota_id = plan.quota_id)
694: g_org_id,
695: s.rollup_level);
696:
697:
698: UPDATE cn_commission_lines_all cl2
699: SET cl2.srp_plan_assign_id = plan.srp_plan_assign_id,
700: cl2.quota_id = plan.quota_id,
701: cl2.quota_rule_id = plan.quota_rule_id,
702: cl2.status = 'POP',
711: AND org_id = g_org_id
712: AND revenue_class_id = rep.revenue_class_id
713: AND NOT EXISTS
714: (SELECT 1
715: FROM cn_commission_lines_all
716: WHERE credited_salesrep_id = cl2.credited_salesrep_id
717: AND commission_header_id = cl2.commission_header_id
718: AND srp_plan_assign_id = plan.srp_plan_assign_id
719: AND quota_id = plan.quota_id
739: end if;
740: l_end_date := plan.end_date;
741: g_end_date := plan.end_date;
742:
743: update /*+ index(cl cn_commission_lines_n7) */ cn_commission_lines_all cl
744: set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
745: cl.quota_id = plan.quota_id,
746: cl.quota_rule_id = plan.quota_rule_id,
747: cl.status = 'POP',
777: if (i = 1) then
778: l_start_date := plan.start_date;
779: l_end_date := plan.end_date;
780: g_end_date := l_end_date;
781: update cn_commission_lines_all cl
782: set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
783: cl.quota_id = plan.quota_id,
784: cl.quota_rule_id = plan.quota_rule_id,
785: cl.status = 'POP',
804: l_end_date := g_end_date;
805: else
806: l_end_date := plan.end_date;
807: end if;
808: merge into cn_commission_lines_all cl
809: using (select commission_line_id,
810: commission_header_id,
811: direct_salesrep_id,
812: pre_processed_code,
828: creation_date,
829: created_by,
830: org_id,
831: rollup_level
832: from cn_commission_lines_all cl2
833: where credited_salesrep_id = rep.credited_salesrep_id
834: and processed_date between l_start_date and l_end_date
835: and processed_period_id between rep.period_id and rep.end_period_id
836: and credited_comp_group_id = rep.credited_comp_group_id
837: and created_during in ('ROLL', 'TROLL')
838: and status <> 'OBSOLETE'
839: and org_id = g_org_id
840: and revenue_class_id = rep.revenue_class_id
841: and not exists (select 1 from cn_commission_lines_all
842: where credited_salesrep_id = cl2.credited_salesrep_id
843: and commission_header_id = cl2.commission_header_id
844: and srp_plan_assign_id = plan.srp_plan_assign_id
845: and quota_id = plan.quota_id)
901: g_org_id,
902: s.rollup_level);
903:
904:
905: UPDATE cn_commission_lines_all cl2
906: SET cl2.srp_plan_assign_id = plan.srp_plan_assign_id,
907: cl2.quota_id = plan.quota_id,
908: cl2.quota_rule_id = plan.quota_rule_id,
909: cl2.status = 'POP',
918: AND org_id = g_org_id
919: AND revenue_class_id = rep.revenue_class_id
920: AND NOT EXISTS
921: (SELECT 1
922: FROM cn_commission_lines_all
923: WHERE credited_salesrep_id = cl2.credited_salesrep_id
924: AND commission_header_id = cl2.commission_header_id
925: AND srp_plan_assign_id = plan.srp_plan_assign_id
926: AND quota_id = plan.quota_id
946: l_start_date := g_end_date + 1;
947: end if;
948: l_end_date := plan.end_date;
949: g_end_date := plan.end_date;
950: update cn_commission_lines_all cl
951: set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
952: cl.quota_id = plan.quota_id,
953: cl.quota_rule_id = plan.quota_rule_id,
954: cl.status = 'POP',
1040: END IF;
1041: END IF;
1042: */
1043: IF l_srp_plan_assign_id IS NOT NULL AND l_quota_rule_id IS NOT NULL THEN
1044: UPDATE cn_commission_lines_all cl
1045: SET cl.status = 'POP',
1046: cl.role_id = srp.role_id,
1047: cl.srp_plan_assign_id = l_srp_plan_assign_id,
1048: cl.quota_id = srp.quota_id,
1082: 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
1083: 'Updating unpopulated transactions to XPOP.');
1084: end if;
1085:
1086: UPDATE cn_commission_lines_all cl
1087: SET cl.status = 'XPOP',
1088: quota_id = NULL,
1089: quota_rule_id = NULL,
1090: role_id =NULL,
1090: role_id =NULL,
1091: srp_plan_assign_id = NULL
1092: WHERE cl.commission_line_id IN
1093: (SELECT line.commission_line_id
1094: FROM cn_commission_lines_all line,
1095: cn_process_batches_all pb
1096: WHERE pb.physical_batch_id = p_physical_batch_id
1097: AND line.credited_salesrep_id = pb.salesrep_id
1098: AND line.processed_period_id BETWEEN pb.period_id AND pb.end_period_id