[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
808: p_period_id NUMBER ) IS
809: CURSOR l_itd_trx_csr IS
810: SELECT commission_line_id, commission_header_id,
811: posting_status, commission_amount
812: FROM cn_commission_lines_all
813: WHERE credited_salesrep_id = p_salesrep_id
814: AND srp_plan_assign_id = p_srp_plan_assign_id
815: AND processed_period_id = p_period_id
816: AND quota_id = p_quota_id
829:
830: DELETE cn_commission_headers_all
831: WHERE commission_header_id = l_itd_trx.commission_header_id;
832:
833: DELETE cn_commission_lines_all
834: WHERE commission_line_id = l_itd_trx.commission_line_id;
835:
836: UPDATE cn_srp_period_quotas_all
837: SET commission_payed_ptd = commission_payed_ptd - l_itd_trx.commission_amount,
1202: SUM(line.input_achieved), SUM(line.output_achieved),
1203: SUM(line.perf_achieved), SUM(line.commission_amount)
1204: INTO l_input_achieved, l_output_achieved,
1205: l_perf_achieved, l_commission_achieved
1206: FROM cn_commission_lines_all line
1207: WHERE line.credited_salesrep_id = p_salesrep_id
1208: AND line.quota_id = p_quota_id
1209: AND line.srp_plan_assign_id = p_srp_plan_assign_id
1210: AND line.status = 'CALC'
1872: l_calc_sub_batch_id := l_calc_sub_batch_rec.calc_sub_batch_id;
1873: l_interval_type_id := l_calc_sub_batch_rec.interval_type_id;
1874:
1875: IF (g_calc_type = 'COMMISSION') THEN
1876: UPDATE cn_commission_lines_all line
1877: SET line.status = 'XCALC',
1878: line.error_reason = 'skip calc with null commission_amount',
1879: last_update_date = sysdate,
1880: last_updated_by = g_last_updated_by,
1881: last_update_login = g_last_update_login
1882: WHERE line.commission_line_id in
1883: (SELECT line2.commission_line_id
1884: FROM cn_process_batches_all batch,
1885: cn_commission_lines_all line2,
1886: cn_commission_headers_all ch
1887: WHERE batch.physical_batch_id = p_physical_batch_id
1888: AND line2.commission_header_id = ch.commission_header_id
1889: AND line2.credited_salesrep_id = batch.salesrep_id
1895: AND substr(line2.pre_processed_code, 4, 1) = 'N'
1896: AND ch.commission_amount is null );
1897: commit;
1898:
1899: UPDATE cn_commission_lines_all line
1900: SET line.status = 'CALC',
1901: line.commission_amount = (select amthead.commission_amount
1902: from cn_commission_headers_all amthead,
1903: cn_commission_lines_all amtline
1899: UPDATE cn_commission_lines_all line
1900: SET line.status = 'CALC',
1901: line.commission_amount = (select amthead.commission_amount
1902: from cn_commission_headers_all amthead,
1903: cn_commission_lines_all amtline
1904: where amthead.commission_header_id = amtline.commission_header_id
1905: and amtline.commission_line_id = line.commission_line_id
1906: ),
1907: line.credit_type_id = (select credit_type_id from cn_quotas_all where quota_id = line.quota_id),
1910: last_update_login = g_last_update_login
1911: WHERE line.commission_line_id in
1912: (SELECT line2.commission_line_id
1913: FROM cn_process_batches_all batch,
1914: cn_commission_lines_all line2,
1915: cn_commission_headers_all ch
1916: WHERE batch.physical_batch_id = p_physical_batch_id
1917: AND line2.commission_header_id = ch.commission_header_id
1918: AND line2.credited_salesrep_id = batch.salesrep_id
2074: END IF ;
2075:
2076: IF (g_calc_type = 'COMMISSION') THEN
2077: -- update all leftover 'POP' status trx to be 'XCALC'.
2078: UPDATE cn_commission_lines_all line
2079: SET line.status = 'XCALC',
2080: last_update_date = sysdate,
2081: last_updated_by = g_last_updated_by,
2082: last_update_login = g_last_update_login
2082: last_update_login = g_last_update_login
2083: WHERE line.commission_line_id IN
2084: (SELECT line2.commission_line_id
2085: FROM cn_process_batches_all batch,
2086: cn_commission_lines_all line2
2087: WHERE batch.physical_batch_id = p_physical_batch_id
2088: AND line2.org_id = batch.org_id
2089: AND line2.credited_salesrep_id = batch.salesrep_id
2090: AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
2198: PROCEDURE handle_reversal_trx ( p_commission_header_id NUMBER) IS
2199:
2200: CURSOR c_affected_reps IS
2201: select distinct credited_salesrep_id, processed_date, processed_period_id, org_id
2202: FROM cn_commission_lines_all
2203: WHERE status = 'OBSOLETE'
2204: and posting_status = 'UNPOSTED'
2205: and (commission_header_id = (SELECT reversal_header_id
2206: FROM cn_commission_headers_all
2212: WHERE commission_header_id = p_commission_header_id)));
2213:
2214: CURSOR l_orig_posted_trx IS
2215: SELECT commission_line_id
2216: FROM cn_commission_lines_all
2217: WHERE (commission_header_id = (SELECT reversal_header_id
2218: FROM cn_commission_headers_all
2219: WHERE commission_header_id = p_commission_header_id)
2220: OR commission_header_id = (SELECT parent_header_id
2232: END LOOP;
2233:
2234: -- update all trx generated from the original commission_header line
2235: -- to be obsolete
2236: UPDATE cn_commission_lines_all
2237: SET status = 'OBSOLETE', posting_status = 'UNPOSTED'
2238: WHERE (commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
2239: WHERE commission_header_id = p_commission_header_id)
2240: OR commission_header_id = (SELECT parent_header_id
2255: p_org_id => rep.org_id);
2256: end loop;
2257:
2258: -- create a negative copy of the above lines
2259: INSERT INTO cn_commission_lines_all
2260: ( commission_line_id, credited_salesrep_id,
2261: processed_period_id, processed_date,
2262: quota_id, credit_type_id, quota_rule_id,
2263: event_factor, payment_factor,
2289: line.posting_status, -( Nvl(line.input_achieved,0) ),
2290: -( Nvl(line.output_achieved,0)), -( Nvl(line.perf_achieved,0)),
2291: line.pay_period_id, line.pending_status,
2292: line.role_id, line.pending_date, line.credited_comp_group_id, line.org_id
2293: FROM cn_commission_lines_all line
2294: WHERE (line.commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
2295: WHERE commission_header_id = p_commission_header_id)
2296: OR line.commission_header_id = (SELECT parent_header_id
2297: FROM cn_commission_headers_all
2346: -- so in this procedure, we don't have to worry about that.
2347: PROCEDURE revert_header_lines( p_commission_header_id NUMBER, p_revert_state VARCHAR2) IS
2348: CURSOR l_posted_trxs_csr IS
2349: SELECT commission_line_id
2350: FROM cn_commission_lines_all
2351: WHERE commission_header_id = p_commission_header_id
2352: AND posting_status = 'POSTED'
2353: AND status = 'CALC';
2354:
2353: AND status = 'CALC';
2354:
2355: CURSOR l_posted_trxs_csr2 IS
2356: SELECT commission_line_id
2357: FROM cn_commission_lines_all
2358: WHERE commission_header_id = (SELECT parent_header_id
2359: FROM cn_commission_headers_all
2360: WHERE commission_header_id = p_commission_header_id)
2361: AND posting_status = 'POSTED'
2370: FOR l_line IN l_posted_trxs_csr2 LOOP
2371: revert_posting_line( l_line.commission_line_id);
2372: END LOOP;
2373:
2374: DELETE cn_commission_lines_all
2375: WHERE commission_header_id = p_commission_header_id;
2376:
2377: DELETE cn_commission_lines_all
2378: WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2373:
2374: DELETE cn_commission_lines_all
2375: WHERE commission_header_id = p_commission_header_id;
2376:
2377: DELETE cn_commission_lines_all
2378: WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2379: WHERE commission_header_id = p_commission_header_id);
2380:
2381: DELETE cn_commission_headers_all
2399: FOR l_line IN l_posted_trxs_csr2 LOOP
2400: revert_posting_line( l_line.commission_line_id);
2401: END LOOP;
2402:
2403: DELETE cn_commission_lines_all
2404: WHERE commission_header_id = p_commission_header_id
2405: AND created_during IN ( 'POP', 'CALC');
2406:
2407: DELETE cn_commission_lines_all
2403: DELETE cn_commission_lines_all
2404: WHERE commission_header_id = p_commission_header_id
2405: AND created_during IN ( 'POP', 'CALC');
2406:
2407: DELETE cn_commission_lines_all
2408: WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2409: WHERE commission_header_id = p_commission_header_id);
2410:
2411: DELETE cn_commission_headers_all
2419: last_update_login = G_LAST_UPDATE_LOGIN
2420: WHERE parent_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
2421: WHERE commission_header_id = p_commission_header_id);
2422:
2423: UPDATE cn_commission_lines_all
2424: SET status = 'ROLL',
2425: posting_status = 'UNPOSTED',
2426: event_factor = NULL,
2427: payment_factor = NULL,
2506: l_quota_id NUMBER )IS
2507: SELECT line.commission_line_id,
2508: line.posting_status,
2509: line.created_during
2510: FROM cn_commission_lines_all line
2511: WHERE line.credited_salesrep_id = l_salesrep_id
2512: AND line.processed_period_id = l_period_id
2513: AND line.quota_id = l_quota_id
2514: AND line.processed_date BETWEEN l_start_date AND l_end_date
2534: l_period_id NUMBER,
2535: l_start_date DATE,
2536: l_end_date DATE ) IS
2537: SELECT line.commission_line_id
2538: FROM cn_commission_lines_all line
2539: WHERE line.commission_header_id
2540: IN ( SELECT header.commission_header_id
2541: FROM cn_commission_headers_all header
2542: WHERE header.direct_salesrep_id = l_salesrep_id
2552: l_period_id NUMBER,
2553: l_quota_id NUMBER,
2554: l_revert_state VARCHAR2 ) IS
2555: SELECT line.commission_line_id, line.posting_status
2556: FROM cn_commission_lines_all line
2557: WHERE line.credited_salesrep_id = l_salesrep_id
2558: AND line.processed_period_id = l_period_id
2559: AND line.trx_type IN ('ITD', 'GRP')
2560: AND line.org_id = l_org_id
2566: p_start_date DATE, p_end_date DATE, p_role_id NUMBER, p_base_comp_group_id NUMBER,
2567: p_base_salesrep_id NUMBER)
2568: IS
2569: SELECT commission_line_id
2570: FROM cn_commission_lines_all cl
2571: WHERE cl.credited_salesrep_id = p_salesrep_id
2572: AND cl.credited_comp_group_id = p_comp_group_id
2573: AND cl.processed_period_id = p_period_id
2574: and cl.status = 'CALC'
2585: CURSOR revert_lines_del_within(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2586: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2587: IS
2588: SELECT commission_line_id
2589: FROM cn_commission_lines_all cl
2590: WHERE cl.posting_status = 'POSTED'
2591: and cl.status = 'CALC'
2592: AND cl.credited_comp_group_id = p_comp_group_id
2593: AND cl.processed_period_id = p_period_id
2608: CURSOR revert_lines_delete_dest(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2609: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2610: IS
2611: SELECT commission_line_id
2612: FROM cn_commission_lines_all cl
2613: WHERE cl.credited_salesrep_id = p_salesrep_id
2614: and cl.posting_status = 'POSTED'
2615: and cl.status = 'CALC'
2616: AND cl.credited_comp_group_id = p_comp_group_id
2622: CURSOR revert_lines_delete_dest2(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2623: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2624: IS
2625: SELECT commission_line_id
2626: FROM cn_commission_lines_all cl
2627: WHERE cl.credited_salesrep_id = p_salesrep_id
2628: and cl.posting_status = 'POSTED'
2629: and cl.status = 'CALC'
2630: AND cl.credited_comp_group_id = p_comp_group_id
2643: CURSOR revert_lines_delete_dest3(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
2644: p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
2645: IS
2646: SELECT commission_line_id
2647: FROM cn_commission_lines_all cl
2648: WHERE cl.credited_salesrep_id = p_salesrep_id
2649: and cl.posting_status = 'POSTED'
2650: and cl.status = 'CALC'
2651: and cl.org_id = l_org_id
2669:
2670: CURSOR revert_lines_delete_team_memb(p_salesrep_id NUMBER, p_period_id NUMBER,
2671: p_start_date DATE, p_end_date DATE) IS
2672: SELECT commission_line_id
2673: from cn_commission_lines_all
2674: where posting_status = 'POSTED'
2675: and (commission_header_id, credited_salesrep_id) in
2676: (select commission_header_id, credited_salesrep_id
2677: FROM cn_commission_lines_all cl
2673: from cn_commission_lines_all
2674: where posting_status = 'POSTED'
2675: and (commission_header_id, credited_salesrep_id) in
2676: (select commission_header_id, credited_salesrep_id
2677: FROM cn_commission_lines_all cl
2678: WHERE cl.credited_salesrep_id = p_salesrep_id
2679: AND cl.processed_period_id = p_period_id
2680: AND cl.created_during = 'TROLL'
2681: AND cl.org_id = l_org_id
2722: revert_posting_line(line.commission_line_id);
2723: END LOOP;
2724:
2725:
2726: DELETE cn_commission_lines_all
2727: WHERE (commission_header_id, credited_salesrep_id) in
2728: (select commission_header_id, credited_salesrep_id
2729: from cn_commission_lines_all cl
2730: where cl.credited_salesrep_id = l_log.salesrep_id
2725:
2726: DELETE cn_commission_lines_all
2727: WHERE (commission_header_id, credited_salesrep_id) in
2728: (select commission_header_id, credited_salesrep_id
2729: from cn_commission_lines_all cl
2730: where cl.credited_salesrep_id = l_log.salesrep_id
2731: AND cl.processed_period_id = l_log.period_id
2732: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2733: AND cl.created_during = 'TROLL'
2739: LOOP
2740: revert_posting_line(line.commission_line_id);
2741: END LOOP;
2742:
2743: DELETE cn_commission_lines_all cl
2744: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2745: AND cl.credited_comp_group_id = l_log.comp_group_id
2746: AND cl.processed_period_id = l_log.period_id
2747: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2771: LOOP
2772: revert_posting_line(line.commission_line_id);
2773: END LOOP;
2774:
2775: DELETE cn_commission_lines_all cl
2776: WHERE cl.credited_comp_group_id = l_log.comp_group_id
2777: AND cl.processed_period_id = l_log.period_id
2778: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2779: AND cl.org_id = l_org_id
2794: LOOP
2795: revert_posting_line(line.commission_line_id);
2796: END LOOP;
2797:
2798: DELETE cn_commission_lines_all cl
2799: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2800: AND cl.credited_comp_group_id = l_log.comp_group_id
2801: AND cl.processed_period_id = l_log.period_id
2802: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2822: LOOP
2823: revert_posting_line(line.commission_line_id);
2824: END LOOP;
2825:
2826: DELETE cn_commission_lines_all cl
2827: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2828: AND cl.credited_comp_group_id = l_log.comp_group_id
2829: AND cl.processed_period_id = l_log.period_id
2830: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2838: AND org_id = cl.org_id
2839: AND cl.processed_date BETWEEN start_date_active AND Nvl(end_date_active, cl.processed_date));
2840:
2841:
2842: UPDATE cn_commission_lines_all cl
2843: SET created_during = 'ROLL'
2844: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2845: AND cl.credited_comp_group_id = l_log.comp_group_id
2846: AND cl.processed_period_id = l_log.period_id
2847: AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
2848: AND cl.created_during = 'POP'
2849: AND org_id = l_org_id
2850: AND NOT exists (SELECT 1
2851: FROM cn_commission_lines_all
2852: WHERE commission_header_id = cl.commission_header_id
2853: AND credited_salesrep_id = cl.credited_salesrep_id
2854: AND credited_comp_group_id = l_log.comp_group_id
2855: AND org_id = cl.org_id
2854: AND credited_comp_group_id = l_log.comp_group_id
2855: AND org_id = cl.org_id
2856: AND created_during = 'ROLL')
2857: AND cl.commission_line_id IN (SELECT MIN(commission_line_id)
2858: FROM cn_commission_lines_all
2859: WHERE credited_salesrep_id = l_log.salesrep_id
2860: AND credited_comp_group_id = l_log.comp_group_id
2861: AND processed_period_id = l_log.period_id
2862: AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2869: LOOP
2870: revert_posting_line(line.commission_line_id);
2871: END LOOP;
2872:
2873: DELETE cn_commission_lines_all cl
2874: WHERE cl.credited_salesrep_id = l_log.salesrep_id
2875: AND cl.credited_comp_group_id = l_log.comp_group_id
2876: AND cl.direct_salesrep_id <> l_log.salesrep_id
2877: AND cl.processed_period_id = l_log.period_id
2896: -- handle reverts here.
2897: FOR l_log IN l_log_rep_periods_csr LOOP
2898: IF l_log.revert_state = 'POP' THEN
2899: -- 1). delete 'UNPOSTED' and created_during 'CALC'
2900: DELETE cn_commission_lines_all line
2901: WHERE line.credited_salesrep_id = l_log.salesrep_id
2902: AND line.processed_period_id = l_log.period_id
2903: AND line.quota_id = l_log.quota_id
2904: AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
2919: revert_posting_line ( l_line.commission_line_id);
2920: END IF;
2921: END LOOP;
2922:
2923: DELETE FROM cn_commission_lines_all
2924: WHERE credited_salesrep_id = l_log.salesrep_id
2925: AND processed_period_id = l_log.period_id
2926: AND quota_id = l_log.quota_id
2927: AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2928: AND trx_type NOT IN ('FORECAST', 'BONUS')
2929: AND status <> 'OBSOLETE'
2930: AND created_during = 'CALC';
2931:
2932: UPDATE cn_commission_lines_all
2933: SET status = 'POP', -- and more
2934: posting_status = 'UNPOSTED',
2935: event_factor = NULL,
2936: payment_factor = NULL,
2956: AND status NOT IN ('XPOP', 'OBSOLETE');
2957:
2958: ELSIF l_log.revert_state = 'ROLL' THEN
2959: -- 1). delete trx created during 'POP', 'CALC' and 'UNPOSTED'
2960: DELETE cn_commission_lines_all line
2961: WHERE line.credited_salesrep_id = l_log.salesrep_id
2962: AND line.processed_period_id = l_log.period_id
2963: AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
2964: AND line.trx_type NOT IN ('FORECAST', 'BONUS')
2977: revert_posting_line ( l_line.commission_line_id);
2978: END IF;
2979: END LOOP;
2980:
2981: DELETE FROM cn_commission_lines_all
2982: WHERE credited_salesrep_id = l_log.salesrep_id
2983: AND processed_period_id = l_log.period_id
2984: AND processed_date BETWEEN l_log.start_date AND l_log.end_date
2985: AND trx_type NOT IN ('FORECAST', 'BONUS')
2986: AND status <> 'OBSOLETE'
2987: AND created_during in ('POP', 'CALC')
2988: AND org_id = l_org_id;
2989:
2990: UPDATE cn_commission_lines_all
2991: SET status = 'ROLL', -- and more
2992: posting_status = 'UNPOSTED',
2993: event_factor = NULL,
2994: payment_factor = NULL,
3025: END LOOP;
3026:
3027: -- 2). delete trx created during 'ROLL' 'POP', 'CALC',
3028: -- basically everything from lines table
3029: DELETE cn_commission_lines_all line
3030: WHERE line.org_id = l_org_id
3031: AND line.commission_header_id
3032: IN ( SELECT header.commission_header_id
3033: FROM cn_commission_headers header
3065: END IF;
3066:
3067: END LOOP;
3068:
3069: DELETE FROM cn_commission_lines_all line
3070: WHERE line.credited_salesrep_id =l_log.salesrep_id
3071: AND line.processed_period_id = l_log.period_id
3072: AND line.trx_type IN ('ITD', 'GRP')
3073: AND ((l_log.revert_state = 'POP' AND line.quota_id = l_log.quota_id) OR
3108: PROCEDURE Revert_Batch_nonintel(p_batch_id cn_process_batches.physical_batch_id%TYPE,
3109: p_calc_type cn_calc_submission_batches.calc_type%TYPE) IS
3110: CURSOR l_post_lines_csr IS
3111: SELECT line.commission_line_id
3112: FROM cn_commission_lines_all line,
3113: cn_process_batches_all batch
3114: WHERE batch.physical_batch_id = p_batch_id
3115: AND line.org_id = batch.org_id
3116: AND line.credited_salesrep_id = batch.salesrep_id
3180: CLOSE check_unique_tuple;
3181: l_unique_flag := 'Y';
3182:
3183: loop
3184: DELETE /*+ index(line cn_commission_lines_n7) */ cn_commission_lines_all line
3185: WHERE line.credited_salesrep_id IN (SELECT salesrep_id
3186: FROM cn_process_batches_all
3187: WHERE physical_batch_id = p_batch_id)
3188: AND line.processed_period_id BETWEEN l_period_id AND l_end_period_id
3198: end loop;
3199: ELSE
3200: CLOSE check_unique_tuple;
3201: loop
3202: DELETE cn_commission_lines_all del_line
3203: WHERE del_line.commission_line_id IN
3204: (SELECT line.commission_line_id
3205: FROM cn_commission_lines_all line,
3206: cn_process_batches_all batch
3201: loop
3202: DELETE cn_commission_lines_all del_line
3203: WHERE del_line.commission_line_id IN
3204: (SELECT line.commission_line_id
3205: FROM cn_commission_lines_all line,
3206: cn_process_batches_all batch
3207: WHERE batch.physical_batch_id = p_batch_id
3208: AND line.org_id = batch.org_id
3209: AND line.credited_salesrep_id = batch.salesrep_id
3305: WHERE calc_sub_batch_id = l_calc_sub_batch_id;
3306:
3307: CURSOR l_post_lines IS
3308: select cl.commission_line_id
3309: FROM cn_commission_lines_all cl,
3310: cn_process_batches_all batch
3311: WHERE batch.physical_batch_id = p_batch_id
3312: AND batch.salesrep_id = cl.credited_salesrep_id
3313: and cl.org_id = l_org_id
3346: -- delete header lines
3347: delete from cn_commission_headers_all
3348: where commission_header_id in (
3349: select cl.commission_header_id
3350: from cn_commission_lines_all cl,
3351: cn_process_batches_all batch
3352: where batch.physical_batch_id = p_batch_id
3353: and batch.salesrep_id = cl.credited_salesrep_id
3354: and cl.org_id = l_org_id
3362: cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3363: where calc_sub_batch_id = l_calc_sub_batch_id)));
3364:
3365: -- delete detail lines
3366: delete from cn_commission_lines_all
3367: where commission_line_id in (
3368: select cl.commission_line_id
3369: from cn_commission_lines_all cl,
3370: cn_process_batches_all batch
3365: -- delete detail lines
3366: delete from cn_commission_lines_all
3367: where commission_line_id in (
3368: select cl.commission_line_id
3369: from cn_commission_lines_all cl,
3370: cn_process_batches_all batch
3371: where batch.physical_batch_id = p_batch_id
3372: and batch.salesrep_id = cl.credited_salesrep_id
3373: and cl.org_id =l_org_id
3454: ELSE -- p_trx_rec_new.status = 'XCALC'
3455: revert_posting_line( p_trx_rec_old.commission_line_id);
3456: END IF;
3457: END IF;
3458: UPDATE cn_commission_lines_all
3459: SET commission_amount = p_trx_rec_new.commission_amount,
3460: commission_rate = p_trx_rec_new.commission_rate,
3461: rate_tier_id = p_trx_rec_new.rate_tier_id,
3462: tier_split = p_trx_rec_new.tier_split,
3482: l_counter NUMBER := 0;
3483:
3484: CURSOR l_chk_pending_trx_csr IS
3485: SELECT 1
3486: FROM cn_commission_lines_all
3487: WHERE credited_salesrep_id = p_salesrep_id
3488: AND srp_plan_assign_id = p_srp_plan_assign_id
3489: AND quota_id = p_quota_id
3490: AND processed_period_id BETWEEN l_start_period_id AND p_period_id
3535:
3536: -- then create a line in commission_lines
3537: -- the pending status is determined by checking all trx in all periods?????
3538:
3539: INSERT INTO cn_commission_lines_all
3540: (commission_line_id, credited_salesrep_id, commission_header_id,
3541: quota_id, credit_type_id, srp_plan_assign_id, role_id, status ,
3542: commission_amount, commission_rate, rate_tier_id, tier_split,
3543: input_achieved, output_achieved,
3561: sysdate, g_last_updated_by, g_creation_date,
3562: g_created_by, g_last_update_login, l_org_id)
3563: return commission_line_id into l_commission_line_id;
3564:
3565: update cn_commission_lines_all cl
3566: set srp_payee_assign_id = (SELECT spa.srp_payee_assign_id
3567: FROM cn_srp_quota_assigns_all sqa,
3568: cn_srp_payee_assigns_all spa
3569: WHERE sqa.srp_plan_assign_id = cl.srp_plan_assign_id
3719: RETURN;
3720: END IF;
3721:
3722: FOR salesrep IN salesreps LOOP
3723: UPDATE cn_commission_lines_all cl
3724: SET (payment_factor,quota_factor) =
3725: (SELECT squ.payment_factor/100,
3726: squ.quota_factor/100
3727: FROM cn_srp_quota_rules_all sqr,
3766: p_period_id NUMBER,
3767: p_quota_id NUMBER ) RETURN BOOLEAN IS
3768: CURSOR l_itd_calc_trx_csr IS
3769: SELECT 1
3770: FROM cn_commission_lines_all
3771: WHERE credited_salesrep_id = p_salesrep_id
3772: AND srp_plan_assign_id = p_srp_plan_assign_id
3773: AND processed_period_id = p_period_id
3774: AND quota_id = p_quota_id