[Home] [Help]
136: null, --cl.attribute14,
137: null, --cl.attribute15
138: cl.org_id,
139: 0
140: FROM cn_commission_lines_all cl,
141: cn_quotas_all pe
142: WHERE cl.commission_line_id = p_commission_line_id
143: AND cl.quota_id = pe.quota_id
144: AND cl.srp_payee_assign_id IS NULL)
193: null, --cl.attribute14,
194: null, --cl.attribute15
195: cl.org_id,
196: 0
197: FROM cn_commission_lines_all cl,
198: cn_srp_payee_assigns_all payee,
199: cn_quotas_all pe
200: WHERE cl.commission_line_id = p_commission_line_id
201: AND cl.quota_id = pe.quota_id
221: -- insert record into CN_PAYMENT_TRANSACTIONS
222: CN_PMT_TRANS_PKG.Insert_Record(l_pmt_trans_rec);
223:
224: -- make sure it is not reverted twice
225: update cn_commission_lines
226: set posting_status = 'REVERTED',
227: last_update_date = sysdate
228: where commission_line_id = p_commission_line_id;
229: END IF;
809: p_period_id NUMBER ) IS
810: CURSOR l_itd_trx_csr IS
811: SELECT commission_line_id, commission_header_id,
812: posting_status, commission_amount
813: FROM cn_commission_lines_all
814: WHERE credited_salesrep_id = p_salesrep_id
815: AND srp_plan_assign_id = p_srp_plan_assign_id
816: AND processed_period_id = p_period_id
817: AND quota_id = p_quota_id
830:
831: DELETE cn_commission_headers_all
832: WHERE commission_header_id = l_itd_trx.commission_header_id;
833:
834: DELETE cn_commission_lines_all
835: WHERE commission_line_id = l_itd_trx.commission_line_id;
836:
837: UPDATE cn_srp_period_quotas_all
838: SET commission_payed_ptd = commission_payed_ptd - l_itd_trx.commission_amount,
1203: SUM(line.input_achieved), SUM(line.output_achieved),
1204: SUM(line.perf_achieved), SUM(line.commission_amount)
1205: INTO l_input_achieved, l_output_achieved,
1206: l_perf_achieved, l_commission_achieved
1207: FROM cn_commission_lines_all line
1208: WHERE line.credited_salesrep_id = p_salesrep_id
1209: AND line.quota_id = p_quota_id
1210: AND line.srp_plan_assign_id = p_srp_plan_assign_id
1211: AND line.status = 'CALC'
1873: l_calc_sub_batch_id := l_calc_sub_batch_rec.calc_sub_batch_id;
1874: l_interval_type_id := l_calc_sub_batch_rec.interval_type_id;
1875:
1876: IF (g_calc_type = 'COMMISSION') THEN
1877: UPDATE cn_commission_lines_all line
1878: SET line.status = 'XCALC',
1879: line.error_reason = 'skip calc with null commission_amount',
1880: last_update_date = sysdate,
1881: last_updated_by = g_last_updated_by,
1882: last_update_login = g_last_update_login
1883: WHERE line.commission_line_id in
1884: (SELECT line2.commission_line_id
1885: FROM cn_process_batches_all batch,
1886: cn_commission_lines_all line2,
1887: cn_commission_headers_all ch
1888: WHERE batch.physical_batch_id = p_physical_batch_id
1889: AND line2.commission_header_id = ch.commission_header_id
1890: AND line2.credited_salesrep_id = batch.salesrep_id
1896: AND substr(line2.pre_processed_code, 4, 1) = 'N'
1897: AND ch.commission_amount is null );
1898: commit;
1899:
1900: UPDATE cn_commission_lines_all line
1901: SET line.status = 'CALC',
1902: line.commission_amount = (select amthead.commission_amount
1903: from cn_commission_headers_all amthead,
1904: cn_commission_lines_all amtline
1900: UPDATE cn_commission_lines_all line
1901: SET line.status = 'CALC',
1902: line.commission_amount = (select amthead.commission_amount
1903: from cn_commission_headers_all amthead,
1904: cn_commission_lines_all amtline
1905: where amthead.commission_header_id = amtline.commission_header_id
1906: and amtline.commission_line_id = line.commission_line_id
1907: ),
1908: line.credit_type_id = (select credit_type_id from cn_quotas_all where quota_id = line.quota_id),
1911: last_update_login = g_last_update_login
1912: WHERE line.commission_line_id in
1913: (SELECT line2.commission_line_id
1914: FROM cn_process_batches_all batch,
1915: cn_commission_lines_all line2,
1916: cn_commission_headers_all ch
1917: WHERE batch.physical_batch_id = p_physical_batch_id
1918: AND line2.commission_header_id = ch.commission_header_id
1919: AND line2.credited_salesrep_id = batch.salesrep_id
2075: END IF ;
2076:
2077: IF (g_calc_type = 'COMMISSION') THEN
2078: -- update all leftover 'POP' status trx to be 'XCALC'.
2079: UPDATE cn_commission_lines_all line
2080: SET line.status = 'XCALC',
2081: last_update_date = sysdate,
2082: last_updated_by = g_last_updated_by,
2083: last_update_login = g_last_update_login
2083: last_update_login = g_last_update_login
2084: WHERE line.commission_line_id IN
2085: (SELECT line2.commission_line_id
2086: FROM cn_process_batches_all batch,
2087: cn_commission_lines_all line2
2088: WHERE batch.physical_batch_id = p_physical_batch_id
2089: AND line2.org_id = batch.org_id
2090: AND line2.credited_salesrep_id = batch.salesrep_id
2091: AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
2214: PROCEDURE handle_reversal_trx ( p_commission_header_id NUMBER) IS
2215:
2216: CURSOR c_affected_reps IS
2217: select distinct credited_salesrep_id, processed_date, processed_period_id, org_id
2218: FROM cn_commission_lines_all
2219: WHERE status = 'OBSOLETE'
2220: and posting_status = 'UNPOSTED'
2221: and (commission_header_id = (SELECT reversal_header_id
2222: FROM cn_commission_headers_all
2228: WHERE commission_header_id = p_commission_header_id)));
2229:
2230: CURSOR l_orig_posted_trx IS
2231: SELECT commission_line_id
2232: FROM cn_commission_lines_all
2233: WHERE (commission_header_id = (SELECT reversal_header_id
2234: FROM cn_commission_headers_all
2235: WHERE commission_header_id = p_commission_header_id)
2236: OR commission_header_id = (SELECT parent_header_id
2248: END LOOP;
2249:
2250: -- update all trx generated from the original commission_header line
2251: -- to be obsolete
2252: UPDATE cn_commission_lines_all
2253: SET status = 'OBSOLETE', posting_status = 'UNPOSTED'
2254: WHERE (commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
2255: WHERE commission_header_id = p_commission_header_id)
2256: OR commission_header_id = (SELECT parent_header_id
2271: p_org_id => rep.org_id);
2272: end loop;
2273:
2274: -- create a negative copy of the above lines
2275: INSERT INTO cn_commission_lines_all
2276: ( commission_line_id, credited_salesrep_id,
2277: processed_period_id, processed_date,
2278: quota_id, credit_type_id, quota_rule_id,
2279: event_factor, payment_factor,
2288: posting_status, input_achieved,
2289: output_achieved, perf_achieved,
2290: pay_period_id, pending_status,
2291: role_id, pending_date, credited_comp_group_id, org_id )
2292: SELECT cn_commission_lines_s.nextval, line.credited_salesrep_id,
2293: line.processed_period_id, line.processed_date,
2294: line.quota_id, line.credit_type_id, line.quota_rule_id,
2295: line.event_factor, line.payment_factor,
2296: line.quota_factor, -( Nvl(line.commission_amount, 0) ),
2305: line.posting_status, -( Nvl(line.input_achieved,0) ),
2306: -( Nvl(line.output_achieved,0)), -( Nvl(line.perf_achieved,0)),
2307: line.pay_period_id, line.pending_status,
2308: line.role_id, line.pending_date, line.credited_comp_group_id, line.org_id
2309: FROM cn_commission_lines_all line
2310: WHERE (line.commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
2311: WHERE commission_header_id = p_commission_header_id)
2312: OR line.commission_header_id = (SELECT parent_header_id
2313: FROM cn_commission_headers_all
2362: -- so in this procedure, we don't have to worry about that.
2363: PROCEDURE revert_header_lines( p_commission_header_id NUMBER, p_revert_state VARCHAR2) IS
2364: CURSOR l_posted_trxs_csr IS
2365: SELECT commission_line_id
2366: FROM cn_commission_lines_all
2367: WHERE commission_header_id = p_commission_header_id
2368: AND posting_status = 'POSTED'
2369: AND status = 'CALC';
2370:
2369: AND status = 'CALC';
2370:
2371: CURSOR l_posted_trxs_csr2 IS
2372: SELECT commission_line_id
2373: FROM cn_commission_lines_all
2374: WHERE commission_header_id = (SELECT parent_header_id
2375: FROM cn_commission_headers_all
2376: WHERE commission_header_id = p_commission_header_id)
2377: AND posting_status = 'POSTED'
2386: FOR l_line IN l_posted_trxs_csr2 LOOP
2387: revert_posting_line( l_line.commission_line_id);
2388: END LOOP;
2389:
2390: DELETE cn_commission_lines_all
2391: WHERE commission_header_id = p_commission_header_id;
2392:
2393: DELETE cn_commission_lines_all
2394: WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2389:
2390: DELETE cn_commission_lines_all
2391: WHERE commission_header_id = p_commission_header_id;
2392:
2393: DELETE cn_commission_lines_all
2394: WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2395: WHERE commission_header_id = p_commission_header_id);
2396:
2397: DELETE cn_commission_headers_all
2415: FOR l_line IN l_posted_trxs_csr2 LOOP
2416: revert_posting_line( l_line.commission_line_id);
2417: END LOOP;
2418:
2419: DELETE cn_commission_lines_all
2420: WHERE commission_header_id = p_commission_header_id
2421: AND created_during IN ( 'POP', 'CALC');
2422:
2423: DELETE cn_commission_lines_all
2419: DELETE cn_commission_lines_all
2420: WHERE commission_header_id = p_commission_header_id
2421: AND created_during IN ( 'POP', 'CALC');
2422:
2423: DELETE cn_commission_lines_all
2424: WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2425: WHERE commission_header_id = p_commission_header_id);
2426:
2427: DELETE cn_commission_headers_all
2435: last_update_login = G_LAST_UPDATE_LOGIN
2436: WHERE parent_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2437: WHERE commission_header_id = p_commission_header_id);
2438:
2439: UPDATE cn_commission_lines_all
2440: SET status = 'ROLL',
2441: posting_status = 'UNPOSTED',
2442: event_factor = NULL,
2443: payment_factor = NULL,
2522: l_quota_id NUMBER )IS
2523: SELECT line.commission_line_id,
2524: line.posting_status,
2525: line.created_during
2526: FROM cn_commission_lines_all line
2527: WHERE line.credited_salesrep_id = l_salesrep_id
2528: AND line.processed_period_id = l_period_id
2529: AND line.quota_id = l_quota_id
2530: AND line.processed_date BETWEEN l_start_date AND l_end_date
2537: l_end_date DATE ) IS
2538: SELECT line.commission_line_id,
2539: line.posting_status,
2540: line.created_during
2541: FROM cn_commission_lines line
2542: WHERE line.credited_salesrep_id = l_salesrep_id
2543: AND line.processed_period_id = l_period_id
2544: AND line.processed_date BETWEEN l_start_date AND l_end_date
2545: AND line.status <> 'OBSOLETE'
2550: l_period_id NUMBER,
2551: l_start_date DATE,
2552: l_end_date DATE ) IS
2553: SELECT line.commission_line_id
2554: FROM cn_commission_lines_all line
2555: WHERE line.commission_header_id
2556: IN ( SELECT header.commission_header_id
2557: FROM cn_commission_headers_all header
2558: WHERE header.direct_salesrep_id = l_salesrep_id
2568: l_period_id NUMBER,
2569: l_quota_id NUMBER,
2570: l_revert_state VARCHAR2 ) IS
2571: SELECT line.commission_line_id, line.posting_status
2572: FROM cn_commission_lines_all line
2573: WHERE line.credited_salesrep_id = l_salesrep_id
2574: AND line.processed_period_id = l_period_id
2575: AND line.trx_type IN ('ITD', 'GRP')
2576: AND line.org_id = l_org_id
2582: p_start_date DATE, p_end_date DATE, p_role_id NUMBER, p_base_comp_group_id NUMBER,
2583: p_base_salesrep_id NUMBER)
2584: IS
2585: SELECT commission_line_id
2586: FROM cn_commission_lines_all cl
2587: WHERE cl.credited_salesrep_id = p_salesrep_id
2588: AND cl.credited_comp_group_id = p_comp_group_id
2589: AND cl.processed_period_id = p_period_id
2590: and cl.status = 'CALC'
2601: CURSOR revert_lines_del_within(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2602: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2603: IS
2604: SELECT commission_line_id
2605: FROM cn_commission_lines_all cl
2606: WHERE cl.posting_status = 'POSTED'
2607: and cl.status = 'CALC'
2608: AND cl.credited_comp_group_id = p_comp_group_id
2609: AND cl.processed_period_id = p_period_id
2624: CURSOR revert_lines_delete_dest(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2625: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2626: IS
2627: SELECT commission_line_id
2628: FROM cn_commission_lines_all cl
2629: WHERE cl.credited_salesrep_id = p_salesrep_id
2630: and cl.posting_status = 'POSTED'
2631: and cl.status = 'CALC'
2632: AND cl.credited_comp_group_id = p_comp_group_id
2638: CURSOR revert_lines_delete_dest2(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2639: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2640: IS
2641: SELECT commission_line_id
2642: FROM cn_commission_lines_all cl
2643: WHERE cl.credited_salesrep_id = p_salesrep_id
2644: and cl.posting_status = 'POSTED'
2645: and cl.status = 'CALC'
2646: AND cl.credited_comp_group_id = p_comp_group_id
2659: CURSOR revert_lines_delete_dest3(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2660: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2661: IS
2662: SELECT commission_line_id
2663: FROM cn_commission_lines_all cl
2664: WHERE cl.credited_salesrep_id = p_salesrep_id
2665: and cl.posting_status = 'POSTED'
2666: and cl.status = 'CALC'
2667: and cl.org_id = l_org_id
2685:
2686: CURSOR revert_lines_delete_team_memb(p_salesrep_id NUMBER, p_period_id NUMBER,
2687: p_start_date DATE, p_end_date DATE) IS
2688: SELECT commission_line_id
2689: from cn_commission_lines_all
2690: where posting_status = 'POSTED'
2691: and (commission_header_id, credited_salesrep_id) in
2692: (select commission_header_id, credited_salesrep_id
2693: FROM cn_commission_lines_all cl
2689: from cn_commission_lines_all
2690: where posting_status = 'POSTED'
2691: and (commission_header_id, credited_salesrep_id) in
2692: (select commission_header_id, credited_salesrep_id
2693: FROM cn_commission_lines_all cl
2694: WHERE cl.credited_salesrep_id = p_salesrep_id
2695: AND cl.processed_period_id = p_period_id
2696: AND cl.created_during = 'TROLL'
2697: AND cl.org_id = l_org_id
2738: revert_posting_line(line.commission_line_id);
2739: END LOOP;
2740:
2741:
2742: DELETE cn_commission_lines_all
2743: WHERE (commission_header_id, credited_salesrep_id) in
2744: (select commission_header_id, credited_salesrep_id
2745: from cn_commission_lines_all cl
2746: where cl.credited_salesrep_id = l_log.salesrep_id
2741:
2742: DELETE cn_commission_lines_all
2743: WHERE (commission_header_id, credited_salesrep_id) in
2744: (select commission_header_id, credited_salesrep_id
2745: from cn_commission_lines_all cl
2746: where cl.credited_salesrep_id = l_log.salesrep_id
2747: AND cl.processed_period_id = l_log.period_id
2748: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2749: AND cl.created_during = 'TROLL'
2755: LOOP
2756: revert_posting_line(line.commission_line_id);
2757: END LOOP;
2758:
2759: DELETE cn_commission_lines_all cl
2760: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2761: AND cl.credited_comp_group_id = l_log.comp_group_id
2762: AND cl.processed_period_id = l_log.period_id
2763: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2787: LOOP
2788: revert_posting_line(line.commission_line_id);
2789: END LOOP;
2790:
2791: DELETE cn_commission_lines_all cl
2792: WHERE cl.credited_comp_group_id = l_log.comp_group_id
2793: AND cl.processed_period_id = l_log.period_id
2794: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2795: AND cl.org_id = l_org_id
2810: LOOP
2811: revert_posting_line(line.commission_line_id);
2812: END LOOP;
2813:
2814: DELETE cn_commission_lines_all cl
2815: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2816: AND cl.credited_comp_group_id = l_log.comp_group_id
2817: AND cl.processed_period_id = l_log.period_id
2818: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2838: LOOP
2839: revert_posting_line(line.commission_line_id);
2840: END LOOP;
2841:
2842: DELETE cn_commission_lines_all cl
2843: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2844: AND cl.credited_comp_group_id = l_log.comp_group_id
2845: AND cl.processed_period_id = l_log.period_id
2846: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2854: AND org_id = cl.org_id
2855: AND cl.processed_date BETWEEN start_date_active AND Nvl(end_date_active, cl.processed_date));
2856:
2857:
2858: UPDATE cn_commission_lines_all cl
2859: SET created_during = 'ROLL'
2860: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2861: AND cl.credited_comp_group_id = l_log.comp_group_id
2862: AND cl.processed_period_id = l_log.period_id
2863: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2864: AND cl.created_during = 'POP'
2865: AND org_id = l_org_id
2866: AND NOT exists (SELECT 1
2867: FROM cn_commission_lines_all
2868: WHERE commission_header_id = cl.commission_header_id
2869: AND credited_salesrep_id = cl.credited_salesrep_id
2870: AND credited_comp_group_id = l_log.comp_group_id
2871: AND org_id = cl.org_id
2870: AND credited_comp_group_id = l_log.comp_group_id
2871: AND org_id = cl.org_id
2872: AND created_during = 'ROLL')
2873: AND cl.commission_line_id IN (SELECT MIN(commission_line_id)
2874: FROM cn_commission_lines_all
2875: WHERE credited_salesrep_id = l_log.salesrep_id
2876: AND credited_comp_group_id = l_log.comp_group_id
2877: AND processed_period_id = l_log.period_id
2878: AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2885: LOOP
2886: revert_posting_line(line.commission_line_id);
2887: END LOOP;
2888:
2889: DELETE cn_commission_lines_all cl
2890: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2891: AND cl.credited_comp_group_id = l_log.comp_group_id
2892: AND cl.direct_salesrep_id <> l_log.salesrep_id
2893: AND cl.processed_period_id = l_log.period_id
2912: -- handle reverts here.
2913: FOR l_log IN l_log_rep_periods_csr LOOP
2914: IF l_log.revert_state = 'POP' THEN
2915: -- 1). delete 'UNPOSTED' and created_during 'CALC'
2916: DELETE cn_commission_lines_all line
2917: WHERE line.credited_salesrep_id = l_log.salesrep_id
2918: AND line.processed_period_id = l_log.period_id
2919: AND line.quota_id = l_log.quota_id
2920: AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
2935: revert_posting_line ( l_line.commission_line_id);
2936: END IF;
2937: END LOOP;
2938:
2939: DELETE FROM cn_commission_lines_all
2940: WHERE credited_salesrep_id = l_log.salesrep_id
2941: AND processed_period_id = l_log.period_id
2942: AND quota_id = l_log.quota_id
2943: AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2944: AND trx_type NOT IN ('FORECAST', 'BONUS')
2945: AND status <> 'OBSOLETE'
2946: AND created_during = 'CALC';
2947:
2948: UPDATE cn_commission_lines_all
2949: SET status = 'POP', -- and more
2950: posting_status = 'UNPOSTED',
2951: event_factor = NULL,
2952: payment_factor = NULL,
2972: AND status NOT IN ('XPOP', 'OBSOLETE');
2973:
2974: ELSIF l_log.revert_state = 'ROLL' THEN
2975: -- 1). delete trx created during 'POP', 'CALC' and 'UNPOSTED'
2976: DELETE cn_commission_lines_all line
2977: WHERE line.credited_salesrep_id = l_log.salesrep_id
2978: AND line.processed_period_id = l_log.period_id
2979: AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
2980: AND line.trx_type NOT IN ('FORECAST', 'BONUS')
2993: revert_posting_line ( l_line.commission_line_id);
2994: END IF;
2995: END LOOP;
2996:
2997: DELETE FROM cn_commission_lines_all
2998: WHERE credited_salesrep_id = l_log.salesrep_id
2999: AND processed_period_id = l_log.period_id
3000: AND processed_date BETWEEN l_log.start_date AND l_log.end_date
3001: AND trx_type NOT IN ('FORECAST', 'BONUS')
3002: AND status <> 'OBSOLETE'
3003: AND created_during in ('POP', 'CALC')
3004: AND org_id = l_org_id;
3005:
3006: UPDATE cn_commission_lines_all
3007: SET status = 'ROLL', -- and more
3008: posting_status = 'UNPOSTED',
3009: event_factor = NULL,
3010: payment_factor = NULL,
3041: END LOOP;
3042:
3043: -- 2). delete trx created during 'ROLL' 'POP', 'CALC',
3044: -- basically everything from lines table
3045: DELETE cn_commission_lines_all line
3046: WHERE line.org_id = l_org_id
3047: AND line.commission_header_id
3048: IN ( SELECT header.commission_header_id
3049: FROM cn_commission_headers header
3081: END IF;
3082:
3083: END LOOP;
3084:
3085: DELETE FROM cn_commission_lines_all line
3086: WHERE line.credited_salesrep_id =l_log.salesrep_id
3087: AND line.processed_period_id = l_log.period_id
3088: AND line.trx_type IN ('ITD', 'GRP')
3089: AND ((l_log.revert_state = 'POP' AND line.quota_id = l_log.quota_id) OR
3124: PROCEDURE Revert_Batch_nonintel(p_batch_id cn_process_batches.physical_batch_id%TYPE,
3125: p_calc_type cn_calc_submission_batches.calc_type%TYPE) IS
3126: CURSOR l_post_lines_csr IS
3127: SELECT line.commission_line_id
3128: FROM cn_commission_lines_all line,
3129: cn_process_batches_all batch
3130: WHERE batch.physical_batch_id = p_batch_id
3131: AND line.org_id = batch.org_id
3132: AND line.credited_salesrep_id = batch.salesrep_id
3196: CLOSE check_unique_tuple;
3197: l_unique_flag := 'Y';
3198:
3199: loop
3200: DELETE /*+ index(line cn_commission_lines_n7) */ cn_commission_lines_all line
3201: WHERE line.credited_salesrep_id IN (SELECT salesrep_id
3202: FROM cn_process_batches_all
3203: WHERE physical_batch_id = p_batch_id)
3204: AND line.processed_period_id BETWEEN l_period_id AND l_end_period_id
3214: end loop;
3215: ELSE
3216: CLOSE check_unique_tuple;
3217: loop
3218: DELETE cn_commission_lines_all del_line
3219: WHERE del_line.commission_line_id IN
3220: (SELECT line.commission_line_id
3221: FROM cn_commission_lines_all line,
3222: cn_process_batches_all batch
3217: loop
3218: DELETE cn_commission_lines_all del_line
3219: WHERE del_line.commission_line_id IN
3220: (SELECT line.commission_line_id
3221: FROM cn_commission_lines_all line,
3222: cn_process_batches_all batch
3223: WHERE batch.physical_batch_id = p_batch_id
3224: AND line.org_id = batch.org_id
3225: AND line.credited_salesrep_id = batch.salesrep_id
3321: WHERE calc_sub_batch_id = l_calc_sub_batch_id;
3322:
3323: CURSOR l_post_lines IS
3324: select cl.commission_line_id
3325: FROM cn_commission_lines_all cl,
3326: cn_process_batches_all batch
3327: WHERE batch.physical_batch_id = p_batch_id
3328: AND batch.salesrep_id = cl.credited_salesrep_id
3329: and cl.org_id = l_org_id
3362: -- delete header lines
3363: delete from cn_commission_headers_all
3364: where commission_header_id in (
3365: select cl.commission_header_id
3366: from cn_commission_lines_all cl,
3367: cn_process_batches_all batch
3368: where batch.physical_batch_id = p_batch_id
3369: and batch.salesrep_id = cl.credited_salesrep_id
3370: and cl.org_id = l_org_id
3378: cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3379: where calc_sub_batch_id = l_calc_sub_batch_id)));
3380:
3381: -- delete detail lines
3382: delete from cn_commission_lines_all
3383: where commission_line_id in (
3384: select cl.commission_line_id
3385: from cn_commission_lines_all cl,
3386: cn_process_batches_all batch
3381: -- delete detail lines
3382: delete from cn_commission_lines_all
3383: where commission_line_id in (
3384: select cl.commission_line_id
3385: from cn_commission_lines_all cl,
3386: cn_process_batches_all batch
3387: where batch.physical_batch_id = p_batch_id
3388: and batch.salesrep_id = cl.credited_salesrep_id
3389: and cl.org_id =l_org_id
3483: END IF;
3484:
3485:
3486:
3487: UPDATE cn_commission_lines_all
3488: SET commission_amount = p_trx_rec_new.commission_amount,
3489: commission_rate = p_trx_rec_new.commission_rate,
3490: rate_tier_id = p_trx_rec_new.rate_tier_id,
3491: tier_split = p_trx_rec_new.tier_split,
3511: l_counter NUMBER := 0;
3512:
3513: CURSOR l_chk_pending_trx_csr IS
3514: SELECT 1
3515: FROM cn_commission_lines_all
3516: WHERE credited_salesrep_id = p_salesrep_id
3517: AND srp_plan_assign_id = p_srp_plan_assign_id
3518: AND quota_id = p_quota_id
3519: AND processed_period_id BETWEEN l_start_period_id AND p_period_id
3564:
3565: -- then create a line in commission_lines
3566: -- the pending status is determined by checking all trx in all periods?????
3567:
3568: INSERT INTO cn_commission_lines_all
3569: (commission_line_id, credited_salesrep_id, commission_header_id,
3570: quota_id, credit_type_id, srp_plan_assign_id, role_id, status ,
3571: commission_amount, commission_rate, rate_tier_id, tier_split,
3572: input_achieved, output_achieved,
3575: trx_type, created_during, error_reason,
3576: last_update_date, last_updated_by, creation_date,
3577: created_by, last_update_login, org_id )
3578: VALUES
3579: (cn_commission_lines_s.NEXTVAL, p_trx_rec.salesrep_id, l_header_id,
3580: p_trx_rec.quota_id, p_trx_rec.credit_type_id, p_trx_rec.srp_plan_assign_id, l_role_id, p_trx_rec.status,
3581: Round(Nvl(p_trx_rec.commission_amount,0), g_ext_precision),
3582: Round(Nvl(p_trx_rec.commission_rate,0), CN_GLOBAL_VAR.g_ext_precision ),
3583: Nvl(p_trx_rec.rate_tier_id, 0), Nvl(p_trx_rec.tier_split, 0),
3590: sysdate, g_last_updated_by, g_creation_date,
3591: g_created_by, g_last_update_login, l_org_id)
3592: return commission_line_id into l_commission_line_id;
3593:
3594: update cn_commission_lines_all cl
3595: set srp_payee_assign_id = (SELECT spa.srp_payee_assign_id
3596: FROM cn_srp_quota_assigns_all sqa,
3597: cn_srp_payee_assigns_all spa
3598: WHERE sqa.srp_plan_assign_id = cl.srp_plan_assign_id
3625: cl.output_achieved, cl.perf_achieved,
3626: cl.posting_status, cl.pending_status,
3627: cl.created_during, cl.trx_type,
3628: cl.error_reason, cl.status
3629: FROM cn_commission_lines cl
3630: WHERE cl.credited_salesrep_id = p_grp_trx_rec.salesrep_id
3631: AND cl.quota_id = p_grp_trx_rec.quota_id
3632: AND cl.srp_plan_assign_id = p_grp_trx_rec.srp_plan_assign_id
3633: AND cl.created_during = 'CALC'
3748: RETURN;
3749: END IF;
3750:
3751: FOR salesrep IN salesreps LOOP
3752: UPDATE cn_commission_lines_all cl
3753: SET (payment_factor,quota_factor) =
3754: (SELECT squ.payment_factor/100,
3755: squ.quota_factor/100
3756: FROM cn_srp_quota_rules_all sqr,
3795: p_period_id NUMBER,
3796: p_quota_id NUMBER ) RETURN BOOLEAN IS
3797: CURSOR l_itd_calc_trx_csr IS
3798: SELECT 1
3799: FROM cn_commission_lines_all
3800: WHERE credited_salesrep_id = p_salesrep_id
3801: AND srp_plan_assign_id = p_srp_plan_assign_id
3802: AND processed_period_id = p_period_id
3803: AND quota_id = p_quota_id