[Home] [Help]
63: END) comm_ptd
64: INTO l_bonus_ptd,
65: l_comm_ptd
66: FROM cn_srp_period_quotas_all cspq,
67: cn_quotas_all quota,
68: cn_payruns_all pr
69: WHERE cspq.quota_id = quota.quota_id
70: AND quota.quota_id > 0
71: AND quota.org_id = cspq.org_id
246: WHERE ptrx.payrun_id = p_payrun_id
247: AND ptrx.credited_salesrep_id = p_salesrep_id
248: AND ptrx.incentive_type_code IN ('PMTPLN_REC', 'COMMISSION', 'BONUS')
249: AND EXISTS (SELECT 1
250: FROM cn_quotas_all q
251: WHERE q.quota_id = ptrx.quota_id
252: AND q.payment_group_code = p_payment_group_code);
253: ELSE
254: UPDATE cn_payment_transactions ptrx
259: WHERE ptrx.payrun_id = p_payrun_id
260: AND ptrx.credited_salesrep_id = p_salesrep_id
261: AND ptrx.incentive_type_code IN ('PMTPLN_REC', decode(p_incentive_type, 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS'))
262: AND EXISTS (SELECT 1
263: FROM cn_quotas_all q
264: WHERE q.quota_id = ptrx.quota_id
265: AND q.payment_group_code = p_payment_group_code);
266: END IF;
267: END reset_payrun_id;
376: p_pmt_amount_adj_rec IN NUMBER,
377: p_pmt_amount_adj_nrec IN NUMBER,
378: p_payment_group_code IN cn_srp_pmt_plans_v.payment_group_code%TYPE,
379: p_period_id IN cn_payruns.pay_period_id%TYPE,
380: p_incentive_type IN cn_quotas.incentive_type_code%TYPE,
381: x_calc_rec_tbl IN OUT NOCOPY calc_rec_tbl_type,
382: --R12
383: p_org_id IN cn_payruns.org_id%TYPE
384: ) IS
384: ) IS
385: CURSOR get_pe_pg_count(p_payment_group_code VARCHAR2) IS
386: SELECT COUNT(DISTINCT cnq.quota_id) num_pe
387: FROM cn_srp_period_quotas cspq,
388: cn_quotas_all cnq
389: WHERE cnq.payment_group_code = p_payment_group_code
390: AND cspq.quota_id = cnq.quota_id
391: AND cnq.credit_type_id = -1000
392: AND cspq.salesrep_id = p_salesrep_id
410: AND ps.start_date <= nvl(spayee.end_date, ps.end_date));
411: CURSOR get_pe_pg(p_payment_group_code VARCHAR2) IS
412: SELECT DISTINCT cnq.quota_id quota_id
413: FROM cn_srp_period_quotas cspq,
414: cn_quotas_all cnq
415: WHERE cnq.payment_group_code = p_payment_group_code
416: AND cspq.quota_id = cnq.quota_id
417: AND cnq.credit_type_id = -1000
418: AND cspq.salesrep_id = p_salesrep_id
493: -- to take care srp with no transaction but want to apply pmt plan
494: -- Bug 3140343 : Payee Design.
495: -- Bug 3198445 by jjhuang: Added payment_group_code for cursor get_srp_pe
496: CURSOR get_srp_pe IS
497: --Added cn_quotas_all for bug 3175375 by jjhuang.
498: SELECT DISTINCT v.quota_id,
499: v.payment_group_code
500: FROM (SELECT cnpt.quota_id,
501: cq.payment_group_code
499: v.payment_group_code
500: FROM (SELECT cnpt.quota_id,
501: cq.payment_group_code
502: FROM cn_payment_transactions cnpt,
503: cn_quotas_all cq
504: WHERE cnpt.credit_type_id = g_credit_type_id
505: AND cnpt.credited_salesrep_id = p_salesrep_id
506: AND cnpt.payrun_id = p_payrun_id
507: AND ((cnpt.incentive_type_code NOT IN ('COMMISSION', 'BONUS')) OR
511: UNION ALL
512: SELECT cnsp.quota_id,
513: cnq.payment_group_code
514: FROM cn_srp_period_quotas cnsp,
515: cn_quotas_all cnq,
516: cn_payruns cnp
517: WHERE cnsp.salesrep_id = p_salesrep_id
518: AND cnq.credit_type_id = g_credit_type_id
519: AND cnq.incentive_type_code = nvl(p_incentive_type, cnq.incentive_type_code)
543: AND cnsp.quota_id = spayee.quota_id
544: AND ps.start_date <= nvl(spayee.end_date, ps.end_date))) v;
545:
546: -- Bug 3198445 by jjhuang: get distinct payment group code count.
547: --Added cn_quotas_all for bug 3175375 by jjhuang.
548: CURSOR get_pgc_count IS
549: SELECT COUNT(DISTINCT v.payment_group_code) pgc_count
550: FROM (SELECT cnpt.quota_id,
551: cq.payment_group_code
549: SELECT COUNT(DISTINCT v.payment_group_code) pgc_count
550: FROM (SELECT cnpt.quota_id,
551: cq.payment_group_code
552: FROM cn_payment_transactions cnpt,
553: cn_quotas_all cq
554: WHERE cnpt.credit_type_id = g_credit_type_id
555: AND cnpt.credited_salesrep_id = p_salesrep_id
556: AND cnpt.payrun_id = p_payrun_id
557: AND ((cnpt.incentive_type_code NOT IN ('COMMISSION', 'BONUS')) OR
561: UNION ALL
562: SELECT cnsp.quota_id,
563: cnq.payment_group_code
564: FROM cn_srp_period_quotas cnsp,
565: cn_quotas_all cnq,
566: cn_payruns cnp
567: WHERE cnsp.salesrep_id = p_salesrep_id
568: AND cnq.credit_type_id = g_credit_type_id
569: AND cnq.incentive_type_code = nvl(p_incentive_type, cnq.incentive_type_code)
592: AND ps.org_id = p_org_id
593: AND cnsp.quota_id = spayee.quota_id
594: AND ps.start_date <= nvl(spayee.end_date, ps.end_date))) v;
595:
596: -- remove join to cn_quotas_all since can get quota_id from cnpt
597: -- 03/24/03 -9999 is used in cnupsp2.sql, change to -9990
598: CURSOR get_earnings_total_by_pe(p_quota_id NUMBER) IS
599: -- earnings to populate pmt_amount_calc
600: SELECT nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_calc,
705: l_post_action NUMBER := 0; --0 is false, 1 is true;
706: l_count NUMBER := 0;
707:
708: TYPE quotas_rec_type IS RECORD(
709: quota_id cn_quotas.quota_id%TYPE);
710:
711: TYPE quotas_rec_tbl_type IS TABLE OF quotas_rec_type INDEX BY BINARY_INTEGER;
712:
713: l_quota_tbl quotas_rec_tbl_type;
899: ) IS
900: CURSOR get_earnings_total(p_payment_group_code VARCHAR2) IS
901: SELECT SUM(cnpt.payment_amount) payment_amount
902: FROM cn_payment_transactions cnpt,
903: cn_quotas_all cnq
904: WHERE cnpt.credited_salesrep_id = p_salesrep_id
905: AND cnpt.payrun_id = p_payrun_id
906: AND cnpt.quota_id = cnq.quota_id
907: AND nvl(hold_flag, 'N') = 'N'
970: -- get the amount paid at the payment group code level
971: CURSOR get_itd_amount_paid(p_period_set_id NUMBER, p_period_type_id NUMBER, p_interval_sdate DATE, p_interval_edate DATE, p_pg_code cn_pmt_plans.payment_group_code%TYPE) IS
972: SELECT nvl(SUM(balance1_dtd - balance1_ctd), 0) payment
973: FROM cn_srp_periods csp,
974: cn_quotas_all q,
975: cn_period_statuses ps
976: WHERE csp.period_id = ps.period_id
977: AND ps.period_set_id = p_period_set_id
978: AND ps.period_type_id = p_period_type_id
1051: ' AND NVL(NVL(cspp.end_date, cnpp.end_date),cnps.start_date) >= cnps.start_date ' || ' AND cspp.org_id = cnpp.org_id ' || --R12
1052: ' AND cnpp.org_id = cnps.org_id ' || --R12
1053: ' AND cnps.org_id = :p_org_id ' || --R12
1054: ' ) v ' || ' WHERE row_nums = 1' || ' AND EXISTS' || ' (' || ' SELECT ''x''' ||
1055: ' FROM cn_srp_period_quotas cspq, cn_quotas_all cq' || ' WHERE decode(:p_incentive_type,''ALL'', cq.incentive_type_code,' ||
1056: ' NULL, cq.incentive_type_code,' || ' :p_incentive_type) = cq.incentive_type_code' ||
1057: ' AND v.credit_type_id = cq.credit_type_id' || ' AND v.payment_group_code = cq.payment_group_code' ||
1058: ' AND v.salesrep_id = cspq.salesrep_id' || ' AND cspq.quota_id = cq.quota_id' || ' AND cspq.org_id = cq.org_id' ||
1059: ' AND v.period_id = cspq.period_id ' || ' AND cspq.org_id = cq.org_id ' || --R12
2609: srp.salesrep_id,
2610: srp.quota_id,
2611: decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id
2612: FROM cn_srp_periods srp,
2613: cn_quotas_all q,
2614: cn_quota_pay_elements_all qp,
2615: cn_rs_salesreps s,
2616: cn_repositories r
2617: -- 01/03/03 gasriniv added hold flag check for bug 2710066
2874: wksht_rec.org_id,
2875: 1,
2876: cl.processed_date
2877: FROM cn_commission_lines cl,
2878: cn_quotas_all pe,
2879: cn_quota_pay_elements qp
2880: WHERE cl.credited_salesrep_id = wksht_rec.salesrep_id
2881: AND cl.processed_period_id <= wksht_rec.pay_period_id
2882: AND cl.processed_date <= wksht_rec.pay_date
2974: 1,
2975: cl.processed_date
2976: FROM cn_commission_lines cl,
2977: cn_srp_payee_assigns_all spayee,
2978: cn_quotas_all pe,
2979: cn_quota_pay_elements_all qp
2980: WHERE cl.srp_payee_assign_id IS NOT NULL
2981: AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
2982: AND spayee.payee_id = wksht_rec.salesrep_id