[Home] [Help]
400: AND cnq.incentive_type_code =
401: decode(nvl(p_incentive_type, cnq.incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', cnq.incentive_type_code)
402: -- BUG 3140343 Payee design
403: AND cspq.quota_id NOT IN (SELECT spayee.quota_id
404: FROM cn_srp_payee_assigns spayee,
405: cn_period_statuses ps
406: WHERE (spayee.salesrep_id = p_salesrep_id OR spayee.payee_id = p_salesrep_id)
407: AND ps.period_id = p_period_id
408: AND ps.end_date >= spayee.start_date
426: AND cnq.incentive_type_code =
427: decode(nvl(p_incentive_type, cnq.incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', cnq.incentive_type_code)
428: -- BUG 3140343 Payee design
429: AND cspq.quota_id NOT IN (SELECT spayee.quota_id
430: FROM cn_srp_payee_assigns spayee,
431: cn_period_statuses ps
432: WHERE (spayee.salesrep_id = p_salesrep_id OR spayee.payee_id = p_salesrep_id)
433: AND ps.period_id = p_period_id
434: AND ps.end_date >= spayee.start_date
525: AND cnq.payment_group_code = nvl(p_payment_group_code, cnq.payment_group_code)
526: AND NOT EXISTS (
527: -- separate queries for performance reasons. merge cartesian reported
528: SELECT 1
529: FROM cn_srp_payee_assigns_all spayee,
530: cn_period_statuses_all ps
531: WHERE (spayee.salesrep_id = p_salesrep_id)
532: AND ps.period_id = cnp.pay_period_id
533: AND ps.end_date >= spayee.start_date
535: AND cnsp.quota_id = spayee.quota_id
536: AND ps.start_date <= nvl(spayee.end_date, ps.end_date)
537: UNION ALL
538: SELECT 1
539: FROM cn_srp_payee_assigns_all spayee,
540: cn_period_statuses_all ps
541: WHERE spayee.payee_id = p_salesrep_id
542: AND ps.period_id = cnp.pay_period_id
543: AND ps.end_date >= spayee.start_date
575: AND cnq.payment_group_code = nvl(p_payment_group_code, cnq.payment_group_code)
576: AND NOT EXISTS (
577: -- separate queries for performance reasons. merge cartesian reported
578: SELECT 1
579: FROM cn_srp_payee_assigns_all spayee,
580: cn_period_statuses_all ps
581: WHERE (spayee.salesrep_id = p_salesrep_id)
582: AND ps.period_id = cnp.pay_period_id
583: AND ps.end_date >= spayee.start_date
585: AND cnsp.quota_id = spayee.quota_id
586: AND ps.start_date <= nvl(spayee.end_date, ps.end_date)
587: UNION ALL
588: SELECT 1
589: FROM cn_srp_payee_assigns_all spayee,
590: cn_period_statuses_all ps
591: WHERE spayee.payee_id = p_salesrep_id
592: AND ps.period_id = cnp.pay_period_id
593: AND ps.end_date >= spayee.start_date
1059: ' AND v.credit_type_id = cq.credit_type_id' || ' AND v.payment_group_code = cq.payment_group_code' ||
1060: ' AND v.salesrep_id = cspq.salesrep_id' || ' AND cspq.quota_id = cq.quota_id' || ' AND cspq.org_id = cq.org_id' ||
1061: ' AND v.period_id = cspq.period_id ' || ' AND cspq.org_id = cq.org_id ' || --R12
1062: ' AND cq.org_id = :p_org_id ' || --R12
1063: ' AND cspq.quota_id NOT IN ' || ' ( SELECT spayee.quota_id ' || ' FROM cn_srp_payee_assigns spayee' ||
1064: ' WHERE (spayee.salesrep_id = v.salesrep_id OR ' || ' spayee.payee_id = v.salesrep_id)' || ' AND v.prd_end_date >= spayee.start_date' ||
1065: ' AND spayee.org_id = :p_org_id' || --R12
1066: ' AND v.prd_start_date <= Nvl(spayee.end_date, v.prd_end_date) )' || ' )';
1067: -- Bug 3140343 : Payee Design. Check if this salesrep is a Payee
1467: CURSOR get_comm_total_payee(p_period_id NUMBER) IS
1468: SELECT /*+ index(cl CN_COMMISSION_LINES_N14) */
1469: nvl(SUM(nvl(commission_amount, 0)), 0)
1470: FROM cn_commission_lines cl,
1471: cn_srp_payee_assigns_all spayee
1472: WHERE cl.srp_payee_assign_id IS NOT NULL
1473: AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
1474: AND spayee.payee_id = p_worksheet_rec.salesrep_id
1475: AND cl.credited_salesrep_id = spayee.salesrep_id
1995: AND posting_status <> 'POSTED'
1996: AND org_id = p_worksheet_rec.org_id
1997: AND clk.srp_payee_assign_id IS NOT NULL
1998: AND EXISTS (SELECT 1
1999: FROM cn_srp_payee_assigns_all spayee,
2000: cn_payment_worksheets wksht
2001: WHERE clk.srp_payee_assign_id = spayee.srp_payee_assign_id
2002: AND spayee.quota_id = wksht.quota_id
2003: AND spayee.payee_id = p_worksheet_rec.salesrep_id
2227: FROM cn_payment_worksheets_all cnpw
2228: WHERE cnpw.salesrep_id = cnspg.salesrep_id
2229: AND cnp.payrun_id = cnpw.payrun_id)
2230: AND (EXISTS (SELECT 1
2231: FROM cn_srp_payee_assigns cnspa
2232: WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
2233: AND cnspa.payee_id = cnspg.salesrep_id
2234: --R12
2235: AND cnspa.org_id = cnp.org_id) OR EXISTS
2312: FROM cn_payment_worksheets_all cnpw
2313: WHERE cnpw.salesrep_id = cnspg.salesrep_id
2314: AND cnp.payrun_id = cnpw.payrun_id)
2315: AND (EXISTS (SELECT 1
2316: FROM cn_srp_payee_assigns cnspa
2317: WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
2318: AND cnspa.payee_id = cnspg.salesrep_id
2319: --R12
2320: AND cnspa.org_id = cnp.org_id) OR EXISTS
3019: wksht_rec.org_id,
3020: 1,
3021: cl.processed_date
3022: FROM cn_commission_lines cl,
3023: cn_srp_payee_assigns_all spayee,
3024: cn_quotas_all pe,
3025: cn_quota_pay_elements_all qp
3026: WHERE cl.srp_payee_assign_id IS NOT NULL
3027: AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
3346: AND posting_status <> 'POSTED'
3347: AND org_id = wksht_rec.org_id
3348: AND clk.srp_payee_assign_id IS NOT NULL
3349: AND EXISTS (SELECT 1
3350: FROM cn_srp_payee_assigns_all spayee,
3351: cn_payment_worksheets wksht
3352: WHERE clk.srp_payee_assign_id = spayee.srp_payee_assign_id
3353: AND spayee.quota_id = wksht.quota_id
3354: AND spayee.payee_id = wksht_rec.salesrep_id