[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
PROCEDURE update_ptd_details (
p_salesrep_id IN NUMBER,
p_payrun_id IN NUMBER
)
IS
l_comm_ptd number ;
SELECT SUM(nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) prior_earning,
- (SUM(nvl(balance4_bbd, 0) - nvl(balance4_bbc, 0))) - (SUM(nvl(balance4_dtd, 0) - nvl(balance4_ctd, 0)))
INTO l_bb_earn,
l_bb_pmt_recover
FROM cn_srp_periods_all s,
cn_payruns_all pr
WHERE s.salesrep_id = p_salesrep_id
AND s.org_id = pr.org_id
AND pr.payrun_id = p_payrun_id
AND s.quota_id IS NULL
AND pr.pay_period_id = s.period_id
AND s.credit_type_id = g_credit_type_id ;
SELECT SUM(CASE
WHEN quota.incentive_type_code = 'BONUS' THEN
nvl(cspq.commission_payed_ptd, 0)
ELSE
0
END) bonus_ptd,
SUM(CASE
WHEN quota.incentive_type_code = 'COMMISSION' THEN
nvl(cspq.commission_payed_ptd, 0)
ELSE
0
END) comm_ptd
INTO l_bonus_ptd,
l_comm_ptd
FROM cn_srp_period_quotas_all cspq,
cn_quotas_all quota,
cn_payruns_all pr
WHERE cspq.quota_id = quota.quota_id
AND quota.quota_id > 0
AND quota.org_id = cspq.org_id
AND pr.pay_period_id = cspq.period_id
AND quota.credit_type_id = -1000
AND pr.payrun_id = p_payrun_id
AND cspq.salesrep_id = p_salesrep_id
GROUP BY cspq.salesrep_id,
cspq.period_id;
UPDATE cn_payment_worksheets_all w
SET w.comm_ptd = l_comm_ptd,
w.bonus_ptd = l_bonus_ptd,
w.comm_due_bb = l_bb_earn
WHERE w.salesrep_id = p_salesrep_id
AND w.payrun_id = p_payrun_id
AND w.quota_id IS NULL ;
END update_ptd_details ;
SELECT MAX(p.period_id) max_period_id
FROM cn_period_statuses p,
cn_period_types pt
WHERE p.quarter_num = p_quarter_num
AND p.period_year = p_period_year
AND p.period_type = pt.period_type
AND pt.period_type_id = 0
--R12
AND p.org_id = p_org_id
AND pt.org_id = p_org_id;
SELECT MAX(p.period_id) max_period_id
FROM cn_period_statuses p,
cn_period_types pt
WHERE period_year = p_period_year
AND p.period_type = pt.period_type
AND pt.period_type_id = 0
--R12
AND p.org_id = p_org_id
AND pt.org_id = p_org_id;
UPDATE cn_payment_transactions ptrx
SET payrun_id = NULL,
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE ptrx.payrun_id = p_payrun_id
AND ptrx.credited_salesrep_id = p_salesrep_id
AND ptrx.incentive_type_code IN ('PMTPLN_REC', 'COMMISSION', 'BONUS')
AND EXISTS (SELECT 1
FROM cn_quotas_all q
WHERE q.quota_id = ptrx.quota_id
AND q.payment_group_code = p_payment_group_code);
UPDATE cn_payment_transactions ptrx
SET payrun_id = NULL,
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE ptrx.payrun_id = p_payrun_id
AND ptrx.credited_salesrep_id = p_salesrep_id
AND ptrx.incentive_type_code IN ('PMTPLN_REC', decode(p_incentive_type, 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS'))
AND EXISTS (SELECT 1
FROM cn_quotas_all q
WHERE q.quota_id = ptrx.quota_id
AND q.payment_group_code = p_payment_group_code);
SELECT MIN(start_date),
MAX(end_date)
INTO l_interval_start_date,
l_interval_end_date
FROM cn_period_statuses
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND quarter_num = p_quarter_num
AND period_year = p_period_year
--R12
AND org_id = p_org_id;
SELECT MIN(start_date),
MAX(end_date)
INTO l_interval_start_date,
l_interval_end_date
FROM cn_period_statuses
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_year = p_period_year
--R12
AND org_id = p_org_id;
SELECT COUNT(DISTINCT cnq.quota_id) num_pe
FROM cn_srp_period_quotas cspq,
cn_quotas_all cnq
WHERE cnq.payment_group_code = p_payment_group_code
AND cspq.quota_id = cnq.quota_id
AND cnq.credit_type_id = -1000
AND cspq.salesrep_id = p_salesrep_id
AND cspq.period_id = p_period_id
--R12
AND cspq.org_id = cnq.org_id
AND cspq.org_id = p_org_id
--bug 3107646, issue 4
AND cnq.incentive_type_code =
decode(nvl(p_incentive_type, cnq.incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', cnq.incentive_type_code)
-- BUG 3140343 Payee design
AND cspq.quota_id NOT IN (SELECT spayee.quota_id
FROM cn_srp_payee_assigns spayee,
cn_period_statuses ps
WHERE (spayee.salesrep_id = p_salesrep_id OR spayee.payee_id = p_salesrep_id)
AND ps.period_id = p_period_id
AND ps.end_date >= spayee.start_date
--R12
AND spayee.org_id = ps.org_id
AND spayee.org_id = p_org_id
AND ps.start_date <= nvl(spayee.end_date, ps.end_date));
SELECT DISTINCT cnq.quota_id quota_id
FROM cn_srp_period_quotas cspq,
cn_quotas_all cnq
WHERE cnq.payment_group_code = p_payment_group_code
AND cspq.quota_id = cnq.quota_id
AND cnq.credit_type_id = -1000
AND cspq.salesrep_id = p_salesrep_id
AND cspq.period_id = p_period_id
--R12
AND cspq.org_id = cnq.org_id
AND cspq.org_id = p_org_id
--bug 3107646, issue 4
AND cnq.incentive_type_code =
decode(nvl(p_incentive_type, cnq.incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', cnq.incentive_type_code)
-- BUG 3140343 Payee design
AND cspq.quota_id NOT IN (SELECT spayee.quota_id
FROM cn_srp_payee_assigns spayee,
cn_period_statuses ps
WHERE (spayee.salesrep_id = p_salesrep_id OR spayee.payee_id = p_salesrep_id)
AND ps.period_id = p_period_id
AND ps.end_date >= spayee.start_date
--R12
AND spayee.org_id = ps.org_id
AND spayee.org_id = p_org_id
AND ps.start_date <= nvl(spayee.end_date, ps.end_date));
SELECT DISTINCT v.quota_id,
v.payment_group_code
FROM (SELECT cnpt.quota_id,
cq.payment_group_code
FROM cn_payment_transactions cnpt,
cn_quotas_all cq
WHERE cnpt.credit_type_id = g_credit_type_id
AND cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.payrun_id = p_payrun_id
AND ((cnpt.incentive_type_code NOT IN ('COMMISSION', 'BONUS')) OR
(cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)))
AND cnpt.quota_id = cq.quota_id
AND cq.payment_group_code = nvl(p_payment_group_code, cq.payment_group_code)
UNION ALL
SELECT cnsp.quota_id,
cnq.payment_group_code
FROM cn_srp_period_quotas cnsp,
cn_quotas_all cnq,
cn_payruns cnp
WHERE cnsp.salesrep_id = p_salesrep_id
AND cnq.credit_type_id = g_credit_type_id
AND cnq.incentive_type_code = nvl(p_incentive_type, cnq.incentive_type_code)
AND cnp.payrun_id = p_payrun_id
AND cnp.pay_period_id = cnsp.period_id
AND cnsp.quota_id = cnq.quota_id
AND cnq.payment_group_code = nvl(p_payment_group_code, cnq.payment_group_code)
AND NOT EXISTS (
-- separate queries for performance reasons. merge cartesian reported
SELECT 1
FROM cn_srp_payee_assigns_all spayee,
cn_period_statuses_all ps
WHERE (spayee.salesrep_id = p_salesrep_id)
AND ps.period_id = cnp.pay_period_id
AND ps.end_date >= spayee.start_date
AND ps.org_id = p_org_id
AND cnsp.quota_id = spayee.quota_id
AND ps.start_date <= nvl(spayee.end_date, ps.end_date)
UNION ALL
SELECT 1
FROM cn_srp_payee_assigns_all spayee,
cn_period_statuses_all ps
WHERE spayee.payee_id = p_salesrep_id
AND ps.period_id = cnp.pay_period_id
AND ps.end_date >= spayee.start_date
AND ps.org_id = p_org_id
AND cnsp.quota_id = spayee.quota_id
AND ps.start_date <= nvl(spayee.end_date, ps.end_date))) v;
SELECT COUNT(DISTINCT v.payment_group_code) pgc_count
FROM (SELECT cnpt.quota_id,
cq.payment_group_code
FROM cn_payment_transactions cnpt,
cn_quotas_all cq
WHERE cnpt.credit_type_id = g_credit_type_id
AND cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.payrun_id = p_payrun_id
AND ((cnpt.incentive_type_code NOT IN ('COMMISSION', 'BONUS')) OR
(cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)))
AND cnpt.quota_id = cq.quota_id
AND cq.payment_group_code = nvl(p_payment_group_code, cq.payment_group_code)
UNION ALL
SELECT cnsp.quota_id,
cnq.payment_group_code
FROM cn_srp_period_quotas cnsp,
cn_quotas_all cnq,
cn_payruns cnp
WHERE cnsp.salesrep_id = p_salesrep_id
AND cnq.credit_type_id = g_credit_type_id
AND cnq.incentive_type_code = nvl(p_incentive_type, cnq.incentive_type_code)
AND cnp.payrun_id = p_payrun_id
AND cnp.pay_period_id = cnsp.period_id
AND cnsp.quota_id = cnq.quota_id
AND cnq.payment_group_code = nvl(p_payment_group_code, cnq.payment_group_code)
AND NOT EXISTS (
-- separate queries for performance reasons. merge cartesian reported
SELECT 1
FROM cn_srp_payee_assigns_all spayee,
cn_period_statuses_all ps
WHERE (spayee.salesrep_id = p_salesrep_id)
AND ps.period_id = cnp.pay_period_id
AND ps.end_date >= spayee.start_date
AND ps.org_id = p_org_id
AND cnsp.quota_id = spayee.quota_id
AND ps.start_date <= nvl(spayee.end_date, ps.end_date)
UNION ALL
SELECT 1
FROM cn_srp_payee_assigns_all spayee,
cn_period_statuses_all ps
WHERE spayee.payee_id = p_salesrep_id
AND ps.period_id = cnp.pay_period_id
AND ps.end_date >= spayee.start_date
AND ps.org_id = p_org_id
AND cnsp.quota_id = spayee.quota_id
AND ps.start_date <= nvl(spayee.end_date, ps.end_date))) v;
SELECT nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_calc,
cnpt.quota_id quota_id,
0 pmt_amount_recovery,
0 pmt_amount_adj,
0 held_amount
FROM cn_payment_transactions cnpt
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)
AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND cnpt.payrun_id = p_payrun_id
AND nvl(hold_flag, 'N') = 'N'
AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
--R12
AND cnpt.org_id = p_org_id
GROUP BY cnpt.quota_id
UNION ALL
-- Recovery to populate pmt_amount_recovery
SELECT 0 pmt_amount_calc,
cnpt.quota_id quota_id,
nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_recovery,
0 pmt_amount_adj,
0 held_amount
FROM cn_payment_transactions cnpt
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.incentive_type_code = 'PMTPLN_REC'
AND cnpt.payrun_id = p_payrun_id
AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
--R12
AND cnpt.org_id = p_org_id
GROUP BY cnpt.quota_id
UNION ALL
-- to populate manual pay adjustments in pmt_amount_adj
SELECT 0 pmt_amount_calc,
cnpt.quota_id quota_id,
0 pmt_amount_recovery,
nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_adj,
0 held_amount
FROM cn_payment_transactions cnpt
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.incentive_type_code IN ('MANUAL_PAY_ADJ')
AND cnpt.payrun_id = p_payrun_id
AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
--R12
AND cnpt.org_id = p_org_id
GROUP BY cnpt.quota_id
UNION ALL
-- to populate control payments in pmt_amount_adj
SELECT 0 pmt_amount_calc,
cnpt.quota_id quota_id,
0 pmt_amount_recovery,
nvl(SUM(nvl(cnpt.payment_amount, 0)), 0) - nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_adj,
0 held_amount
FROM cn_payment_transactions cnpt
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)
AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND nvl(cnpt.hold_flag, 'N') = 'N'
AND cnpt.payrun_id = p_payrun_id
AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
--R12
AND cnpt.org_id = p_org_id
GROUP BY cnpt.quota_id
UNION ALL
-- to populate hold in pmt_amount_adj
SELECT 0 pmt_amount_calc,
cnpt.quota_id quota_id,
0 pmt_amount_recovery,
0 pmt_amount_adj,
nvl(SUM(nvl(cnpt.payment_amount, 0)), 0) held_amount
FROM cn_payment_transactions cnpt
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)
AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND nvl(cnpt.hold_flag, 'N') = 'Y'
AND cnpt.payrun_id = p_payrun_id
AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
--R12
AND cnpt.org_id = p_org_id
GROUP BY cnpt.quota_id
UNION ALL
-- to populate waive recovery in pmt_amount_adj
-- changed recovery amount to negative for fix BUG#2545629|
SELECT 0 pmt_amount_calc,
cnpt.quota_id quota_id,
0 pmt_amount_recovery,
-nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_adj,
0 held_amount
FROM cn_payment_transactions cnpt
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.incentive_type_code = 'PMTPLN_REC'
AND nvl(cnpt.waive_flag, 'N') = 'Y'
AND cnpt.payrun_id = p_payrun_id
AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
--R12
AND cnpt.org_id = p_org_id
GROUP BY cnpt.quota_id;
SELECT SUM(cnpt.payment_amount) payment_amount
FROM cn_payment_transactions cnpt,
cn_quotas_all cnq
WHERE cnpt.credited_salesrep_id = p_salesrep_id
AND cnpt.payrun_id = p_payrun_id
AND cnpt.quota_id = cnq.quota_id
AND nvl(hold_flag, 'N') = 'N'
AND nvl(waive_flag, 'N') = 'N'
AND cnq.payment_group_code = p_payment_group_code
AND cnpt.incentive_type_code <> 'PMTPLN';
SELECT quarter_num,
period_year,
period_set_id,
period_type_id,
start_date,
end_date
FROM cn_period_statuses
WHERE period_id = p_period_id
--R12
AND org_id = p_org_id;
SELECT MIN(start_date)
FROM cn_period_statuses
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_year = p_period_year
AND quarter_num = p_quarter_num
--R12
AND org_id = p_org_id;
SELECT MAX(end_date)
FROM cn_period_statuses
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_year = p_period_year
AND quarter_num = p_quarter_num
--R12
AND org_id = p_org_id;
SELECT MIN(start_date)
FROM cn_period_statuses
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_year = p_period_year
AND org_id = p_org_id;
SELECT MAX(end_date)
FROM cn_period_statuses
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_year = p_period_year
AND org_id = p_org_id;
SELECT nvl(SUM(balance1_dtd - balance1_ctd), 0) payment
FROM cn_srp_periods csp,
cn_quotas_all q,
cn_period_statuses ps
WHERE csp.period_id = ps.period_id
AND ps.period_set_id = p_period_set_id
AND ps.period_type_id = p_period_type_id
AND ps.start_date >= p_interval_sdate
AND ps.end_date <= p_interval_edate
AND csp.salesrep_id = p_salesrep_id
AND csp.credit_type_id = g_credit_type_id
AND csp.quota_id = q.quota_id
AND q.payment_group_code = p_pg_code
AND csp.org_id = q.org_id
AND q.org_id = ps.org_id
AND ps.org_id = p_org_id;
l_stmt := 'SELECT v.pay_interval_type_id, v.recoverable_interval_type_id, ' || 'v.pay_against_commission, v.payment_group_code, v.minimum_amount, ' ||
'v.maximum_amount, v.min_rec_flag, v.max_rec_flag, v.name ' || 'FROM ' || '(SELECT ' || ' cnpp.pay_interval_type_id,' ||
' cnpp.recoverable_interval_type_id,' || ' nvl(cnpp.pay_against_commission, ''Y'') pay_against_commission,' || ' cnpp.payment_group_code,' ||
' cspp.minimum_amount,' || ' cnpp.min_rec_flag,' || ' cnpp.max_rec_flag,' || ' cspp.maximum_amount,' || ' cnps.period_id,' ||
' cspp.salesrep_id,' || ' cnps.start_date prd_start_date,' || ' cnps.end_date prd_end_date,' || ' cnpp.name,' ||
' ROW_NUMBER() over (PARTITION BY cnpp.payment_group_code' || ' ORDER BY cspp.start_date DESC) AS row_nums ,' || ' cnpp.credit_type_id' ||
' FROM cn_srp_pmt_plans cspp,cn_pmt_plans cnpp,cn_period_statuses cnps ' || ' WHERE ' || ' cspp.salesrep_id = :p_salesrep_id' ||
' AND cnpp.pmt_plan_id = cspp.pmt_plan_id ' || ' AND cnps.period_id = :p_period_id' || ' AND cnpp.credit_type_id = -1000' ||
' AND cspp.start_date <= cnps.end_date' ||
-- ' AND Nvl(cspp.end_date,cnps.start_date) >= cnps.start_date' ||
--bug 3395792 by jjhuang on 1/23/04
--' AND NVL(cspp.end_date, cnpp.end_date) >= cnps.end_date ' ||
--for bug 3395792 on 2/4/04 by jjhuang. This is to include the following test case:
--If there are two or more payment plans (with the same payment group code) within one period, for example:
--pmt_plan1 from "01-MAY-2003" to "15-MAY-2003", pmt_plan2 from "16-MAY-2003" to "28-MAY-2003".
' AND NVL(NVL(cspp.end_date, cnpp.end_date),cnps.start_date) >= cnps.start_date ' || ' AND cspp.org_id = cnpp.org_id ' || --R12
' AND cnpp.org_id = cnps.org_id ' || --R12
' AND cnps.org_id = :p_org_id ' || --R12
' ) v ' || ' WHERE row_nums = 1' || ' AND EXISTS' || ' (' || ' SELECT ''x''' ||
' FROM cn_srp_period_quotas cspq, cn_quotas_all cq' || ' WHERE decode(:p_incentive_type,''ALL'', cq.incentive_type_code,' ||
' NULL, cq.incentive_type_code,' || ' :p_incentive_type) = cq.incentive_type_code' ||
' AND v.credit_type_id = cq.credit_type_id' || ' AND v.payment_group_code = cq.payment_group_code' ||
' AND v.salesrep_id = cspq.salesrep_id' || ' AND cspq.quota_id = cq.quota_id' || ' AND cspq.org_id = cq.org_id' ||
' AND v.period_id = cspq.period_id ' || ' AND cspq.org_id = cq.org_id ' || --R12
' AND cq.org_id = :p_org_id ' || --R12
' AND cspq.quota_id NOT IN ' || ' ( SELECT spayee.quota_id ' || ' FROM cn_srp_payee_assigns spayee' ||
' WHERE (spayee.salesrep_id = v.salesrep_id OR ' || ' spayee.payee_id = v.salesrep_id)' || ' AND v.prd_end_date >= spayee.start_date' ||
' AND spayee.org_id = :p_org_id' || --R12
' AND v.prd_start_date <= Nvl(spayee.end_date, v.prd_end_date) )' || ' )';
l_applied_pgc.DELETE;
SELECT payables_flag,
payroll_flag,
payables_ccid_level
FROM cn_repositories
WHERE org_id = p_worksheet_rec.org_id;
SELECT 1
FROM cn_payment_worksheets,
cn_payruns
WHERE cn_payment_worksheets.salesrep_id = p_worksheet_rec.salesrep_id
AND cn_payment_worksheets.payrun_id = cn_payruns.payrun_id
AND quota_id IS NULL
AND cn_payruns.status <> 'PAID';
SELECT payrun_id,
pay_period_id,
incentive_type_code,
pay_date
FROM cn_payruns
WHERE payrun_id = p_worksheet_rec.payrun_id
FOR UPDATE NOWAIT;
SELECT payrun_id,
pay_period_id,
incentive_type_code,
pay_date
FROM cn_payruns
WHERE payrun_id = p_worksheet_rec.payrun_id;
SELECT quarter_num,
period_year,
period_set_id,
period_type_id,
start_date,
end_date
FROM cn_period_statuses
WHERE period_id = p_period_id
AND org_id = p_worksheet_rec.org_id;
SELECT nvl(SUM(nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0)
FROM cn_srp_periods srp
WHERE srp.period_id = p_period_id
AND srp.salesrep_id = p_worksheet_rec.salesrep_id
AND srp.credit_type_id = g_credit_type_id
AND quota_id IS NULL
AND org_id = p_worksheet_rec.org_id;
SELECT nvl(SUM(nvl(amount, 0)), 0)
FROM cn_payment_transactions pmt
WHERE pmt.pay_period_id <= p_period_id
AND pmt.credited_salesrep_id = p_worksheet_rec.salesrep_id
AND pmt.credit_type_id = g_credit_type_id
AND pmt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND (pmt.payrun_id IS NULL OR pmt.payrun_id = p_worksheet_rec.payrun_id)
--R12
AND pmt.org_id = p_worksheet_rec.org_id;
SELECT
nvl(SUM(nvl(commission_amount, 0)), 0)
FROM cn_commission_lines_all ccl
WHERE credited_salesrep_id = p_worksheet_rec.salesrep_id
AND processed_period_id <= p_period_id
AND credit_type_id = g_credit_type_id
AND status = 'CALC'
AND posting_status = 'UNPOSTED'
AND srp_payee_assign_id IS NULL
-- posting_status not set to posted yet
AND NOT EXISTS (SELECT NULL
FROM cn_payment_transactions_all pmt
WHERE pmt.credited_salesrep_id = ccl.credited_salesrep_id
AND pmt.commission_line_id = ccl.commission_line_id
AND pmt.credit_type_id = ccl.credit_type_id
AND pmt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND pmt.payrun_id = p_worksheet_rec.payrun_id)
AND ccl.org_id = p_worksheet_rec.org_id;
SELECT /*+ index(cl CN_COMMISSION_LINES_N14) */
nvl(SUM(nvl(commission_amount, 0)), 0)
FROM cn_commission_lines cl,
cn_srp_payee_assigns_all spayee
WHERE cl.srp_payee_assign_id IS NOT NULL
AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
AND spayee.payee_id = p_worksheet_rec.salesrep_id
AND cl.credited_salesrep_id = spayee.salesrep_id
AND cl.processed_period_id <= p_period_id
AND cl.status = 'CALC'
AND cl.credit_type_id = g_credit_type_id
AND cl.posting_status = 'UNPOSTED'
AND cl.org_id = spayee.org_id
AND cl.commission_line_id NOT IN (SELECT pmt.commission_line_id
FROM cn_payment_transactions pmt
WHERE pmt.credited_salesrep_id = p_worksheet_rec.salesrep_id
AND pmt.credit_type_id = g_credit_type_id
AND pmt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND pmt.payrun_id = p_worksheet_rec.payrun_id)
--R12
AND cl.org_id = p_worksheet_rec.org_id
AND spayee.org_id = p_worksheet_rec.org_id;
SELECT payment_worksheet_id
FROM cn_payment_worksheets
WHERE payrun_id = p_worksheet_rec.payrun_id
AND salesrep_id = p_worksheet_rec.salesrep_id
AND quota_id IS NULL;
x_loading_status := 'CN_INSERTED';
SELECT cn_posting_batches_s.NEXTVAL
INTO cls_posting_batch_id
FROM dual;
cn_pmt_trans_pkg.insert_record(p_pay_by_transaction => nvl(l_pbt_profile_value, 'N'),
p_salesrep_id => p_worksheet_rec.salesrep_id,
p_payrun_id => p_worksheet_rec.payrun_id,
p_pay_date => l_get_payrun_rec.pay_date,
p_incentive_type => l_incentive_type,
p_pay_period_id => l_get_payrun_rec.pay_period_id,
p_credit_type_id => g_credit_type_id,
p_posting_batch_id => cls_posting_batch_id,
p_org_id => p_worksheet_rec.org_id);
SELECT processing_status_code
INTO l_calc_status
FROM cn_srp_intel_periods
WHERE salesrep_id = p_worksheet_rec.salesrep_id
AND period_id = l_get_payrun_rec.pay_period_id
AND org_id = p_worksheet_rec.org_id;
SELECT cn_posting_batches_s.NEXTVAL
INTO recv_posting_batch_id
FROM dual;
l_batch_rec.last_updated_by := fnd_global.user_id;
l_batch_rec.last_update_date := SYSDATE;
l_batch_rec.last_update_login := fnd_global.login_id;
cn_prepostbatches.begin_record(x_operation => 'INSERT',
x_rowid => l_rowid,
x_posting_batch_rec => l_batch_rec,
x_program_type => NULL,
p_org_id => p_worksheet_rec.org_id);
cn_pmt_trans_pkg.insert_record(p_tran_rec => l_pmt_trans_rec);
cn_payment_worksheets_pkg.insert_record(x_payrun_id => p_worksheet_rec.payrun_id,
x_salesrep_id => p_worksheet_rec.salesrep_id,
x_quota_id => l_calc_rec_tbl(i).quota_id,
x_credit_type_id => g_credit_type_id,
x_calc_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0),
x_adj_pmt_amount_rec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0),
x_adj_pmt_amount_nrec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0),
x_adj_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0),
x_held_amount => nvl(l_calc_rec_tbl(i).held_amount, 0),
x_pmt_amount_recovery => nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0),
x_worksheet_status => 'UNPAID',
x_created_by => g_created_by,
x_creation_date => SYSDATE,
p_org_id => p_worksheet_rec.org_id,
p_object_version_number => 1);
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
SELECT 1
INTO l_tmp
FROM cn_payment_worksheets
WHERE payrun_id = p_worksheet_rec.payrun_id
AND salesrep_id = p_worksheet_rec.salesrep_id
AND quota_id IS NULL;
cn_payment_worksheets_pkg.insert_record(x_payrun_id => p_worksheet_rec.payrun_id,
x_salesrep_id => p_worksheet_rec.salesrep_id,
x_credit_type_id => g_credit_type_id,
x_calc_pmt_amount => nvl(l_calc_pmt_amount, 0),
x_adj_pmt_amount_rec => nvl(l_adj_pmt_amount_rec, 0),
x_adj_pmt_amount_nrec => nvl(l_adj_pmt_amount_nrec, 0),
x_adj_pmt_amount => nvl(l_pmt_amount_ctr, 0),
x_held_amount => nvl(l_held_amount, 0),
x_pmt_amount_recovery => nvl(l_pmt_amount_rec, 0),
x_worksheet_status => 'UNPAID',
x_created_by => g_created_by,
x_creation_date => SYSDATE,
p_org_id => p_worksheet_rec.org_id,
p_object_version_number => 1);
IF x_loading_status <> 'CN_INSERTED'
THEN
RAISE fnd_api.g_exc_error;
update_ptd_details (
p_salesrep_id => p_worksheet_rec.salesrep_id ,
p_payrun_id => p_worksheet_rec.payrun_id
) ;
x_loading_status := 'CN_INSERTED';
UPDATE cn_commission_lines cls
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE posting_status <> 'POSTED'
AND status = 'CALC'
AND srp_payee_assign_id IS NULL
AND commission_line_id IN (SELECT commission_line_id
FROM cn_payment_transactions
WHERE posting_batch_id = cls_posting_batch_id
AND commission_line_id IS NOT NULL);
UPDATE cn_commission_lines cls
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE posting_status <> 'POSTED'
AND status = 'CALC'
AND srp_payee_assign_id IS NOT NULL
AND commission_line_id IN (SELECT commission_line_id
FROM cn_payment_transactions
WHERE posting_batch_id = cls_posting_batch_id
AND commission_line_id IS NOT NULL);
SELECT DISTINCT pw.quota_id
BULK COLLECT INTO l_wk_plan_elements
FROM cn_payment_worksheets pw
WHERE pw.payrun_id = l_get_payrun_rec.payrun_id
AND pw.salesrep_id = p_worksheet_rec.salesrep_id
AND pw.quota_id IS NOT NULL ;
UPDATE cn_commission_lines cls
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE posting_status <> 'POSTED'
AND credit_type_id = g_credit_type_id
AND processed_period_id <= l_get_payrun_rec.pay_period_id
AND status = 'CALC'
AND srp_payee_assign_id IS NULL
AND credited_salesrep_id = p_worksheet_rec.salesrep_id
AND quota_id = l_wk_plan_elements(m) ;
UPDATE cn_commission_lines clk
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE processed_period_id <= l_get_payrun_rec.pay_period_id
AND status = 'CALC'
AND credit_type_id = g_credit_type_id
AND posting_status <> 'POSTED'
AND org_id = p_worksheet_rec.org_id
AND clk.srp_payee_assign_id IS NOT NULL
AND EXISTS (SELECT 1
FROM cn_srp_payee_assigns_all spayee,
cn_payment_worksheets wksht
WHERE clk.srp_payee_assign_id = spayee.srp_payee_assign_id
AND spayee.quota_id = wksht.quota_id
AND spayee.payee_id = p_worksheet_rec.salesrep_id
AND wksht.payrun_id = l_get_payrun_rec.payrun_id
AND wksht.salesrep_id = p_worksheet_rec.salesrep_id);
x_loading_status VARCHAR2(20) := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
FOR emp IN (SELECT salesrep_id
FROM cn_process_batches
WHERE logical_batch_id = p_logical_batch_id
AND physical_batch_id = p_batch_id)
LOOP
-- Run create worksheet for this salesrep.
l_worksheet_rec.salesrep_id := emp.salesrep_id;
SELECT cp.payrun_id,
cp.org_id,
cp.status
FROM cn_payruns cp
WHERE cp.NAME = c_name
AND cp.org_id = c_org_id;
SELECT salesrep_id,ceil(rownum / l_batch_sz)
BULK COLLECT INTO salesrep_t
FROM (SELECT DISTINCT cns.salesrep_id salesrep_id,
cns.NAME salesrep_name
FROM cn_payruns cnp,
cn_srp_pay_groups cnspg,
cn_salesreps cns,
cn_period_statuses cnps
WHERE cnp.payrun_id = l_payrun_id
AND cnp.status = 'UNPAID'
AND cnp.pay_group_id = cnspg.pay_group_id
AND cnspg.salesrep_id = cns.salesrep_id
AND cns.hold_payment = 'N'
AND cnp.pay_period_id = cnps.period_id
AND cnp.org_id = cnps.org_id
AND cnp.org_id = cnspg.org_id
AND cnp.org_id = cns.org_id
AND ((cnspg.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspg.end_date, cnps.start_date)))
AND NOT EXISTS (SELECT 1
FROM cn_payment_worksheets_all cnpw
WHERE cnpw.salesrep_id = cnspg.salesrep_id
AND cnp.payrun_id = cnpw.payrun_id)
AND (EXISTS (SELECT 1
FROM cn_srp_payee_assigns cnspa
WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
AND cnspa.payee_id = cnspg.salesrep_id
--R12
AND cnspa.org_id = cnp.org_id) OR EXISTS
(SELECT 1
FROM cn_srp_plan_assigns cnspa
WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
AND cnspa.salesrep_id = cnspg.salesrep_id
--R12
AND cnspa.org_id = cnp.org_id)));
PROCEDURE update_worksheet
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER,
p_operation IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
x_ovn IN OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Worksheet';
SELECT cnpw.salesrep_id,
cnp.payrun_id,
cnpw.worksheet_status,
cnp.pay_period_id,
decode(cnp.incentive_type_code, 'ALL', '', cnp.incentive_type_code) incentive_type_code,
cnp.pay_date,
cnpw.object_version_number ovn,
cnpw.org_id
FROM cn_payment_worksheets cnpw,
cn_payruns cnp
WHERE payment_worksheet_id = p_worksheet_id
AND cnpw.payrun_id = cnp.payrun_id;
SELECT payables_flag,
payroll_flag,
payables_ccid_level
FROM cn_repositories rp,
cn_payment_worksheets wk
WHERE rp.org_id = wk.org_id;
SAVEPOINT update_worksheet;
x_loading_status := 'CN_UPDATED';
SELECT s.status,
nvl(r.payroll_flag, 'N'),
r.payables_flag
INTO l_srp_status,
l_payroll_flag,
l_payables_flag
FROM cn_salesreps s,
cn_repositories_all r,
cn_payruns_all pr
WHERE s.salesrep_id = wksht_rec.salesrep_id
AND s.org_id = r.org_id
AND pr.org_id = r.org_id
AND pr.payrun_id = wksht_rec.payrun_id;
SELECT cn_posting_batches_s.NEXTVAL
INTO l_posting_batch_id
FROM dual;
UPDATE cn_payment_transactions cnpt
SET ( amount, payment_amount) = (SELECT SUM(balance2_bbd - balance2_bbc + balance2_dtd - balance2_ctd),
SUM(balance2_bbd - balance2_bbc + balance2_dtd - balance2_ctd)
FROM cn_srp_periods csp
WHERE csp.period_id = wksht_rec.pay_period_id
AND csp.salesrep_id = cnpt.credited_salesrep_id
AND csp.quota_id = cnpt.quota_id
AND csp.credit_type_id = cnpt.credit_type_id
--R12
AND csp.org_id = wksht_rec.org_id),
pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
FROM cn_quota_pay_elements p,
cn_rs_salesreps s,
cn_repositories r
WHERE p.quota_id = cnpt.quota_id
AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
AND s.salesrep_id = cnpt.credited_salesrep_id
AND nvl(s.status, 'A') = p.status
--R12
AND p.org_id = wksht_rec.org_id
AND s.org_id = wksht_rec.org_id
AND r.org_id = wksht_rec.org_id),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE cnpt.payrun_id = wksht_rec.payrun_id
AND cnpt.amount = cnpt.payment_amount
AND incentive_type_code IN ('COMMISSION', 'BONUS')
-- 01/03/03 gasriniv added hold flag check for bug 2710066
AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N')
AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
--R12
AND cnpt.org_id = wksht_rec.org_id;
UPDATE cn_payment_transactions cnpt
SET amount = (SELECT SUM(balance2_bbd - balance2_bbc + balance2_dtd - balance2_ctd)
FROM cn_srp_periods csp
WHERE csp.period_id = wksht_rec.pay_period_id
AND csp.salesrep_id = cnpt.credited_salesrep_id
AND csp.quota_id = cnpt.quota_id
AND csp.credit_type_id = cnpt.credit_type_id
--R12
AND csp.org_id = wksht_rec.org_id),
pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
FROM cn_quota_pay_elements p,
cn_rs_salesreps s,
cn_repositories r
WHERE p.quota_id = cnpt.quota_id
AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
AND s.salesrep_id = cnpt.credited_salesrep_id
AND nvl(s.status, 'A') = p.status
--R12
AND p.org_id = wksht_rec.org_id
AND s.org_id = wksht_rec.org_id
AND r.org_id = wksht_rec.org_id),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE cnpt.payrun_id = wksht_rec.payrun_id
AND cnpt.amount <> cnpt.payment_amount
AND incentive_type_code IN ('COMMISSION', 'BONUS')
-- 01/03/03 gasriniv added hold flag check for bug 2710066
AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N')
AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
--R12
AND cnpt.org_id = wksht_rec.org_id;
INSERT INTO cn_payment_transactions
(payment_transaction_id,
posting_batch_id,
incentive_type_code,
credit_type_id,
pay_period_id,
amount,
payment_amount,
credited_salesrep_id,
payee_salesrep_id,
paid_flag,
hold_flag,
waive_flag,
payrun_id,
quota_id,
pay_element_type_id,
created_by,
creation_date,
--R12
org_id)
SELECT cn_payment_transactions_s.NEXTVAL,
l_posting_batch_id,
v1.incentive_type_code,
v1.credit_type_id,
v1.period_id,
v1.amount,
v1.payment_amount,
v1.salesrep_id,
v1.salesrep_id,
'N',
'N',
'N',
wksht_rec.payrun_id,
v1.quota_id,
v1.pay_element_type_id,
g_created_by,
SYSDATE,
--R12
wksht_rec.org_id
FROM (SELECT q.incentive_type_code,
srp.credit_type_id,
srp.period_id,
SUM((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0))) amount,
SUM((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0))) payment_amount,
srp.salesrep_id,
srp.quota_id,
decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id
FROM cn_srp_periods srp,
cn_quotas_all q,
cn_quota_pay_elements_all qp,
cn_rs_salesreps s,
cn_repositories r
-- 01/03/03 gasriniv added hold flag check for bug 2710066
WHERE srp.salesrep_id = wksht_rec.salesrep_id
AND srp.period_id = wksht_rec.pay_period_id
AND srp.quota_id = q.quota_id
AND srp.quota_id <> -1000
-- Bug 2819874
AND srp.credit_type_id = -1000
AND q.incentive_type_code = decode(nvl(wksht_rec.incentive_type_code, q.incentive_type_code),
'COMMISSION',
'COMMISSION',
'BONUS',
'BONUS',
q.incentive_type_code)
AND qp.quota_id(+) = srp.quota_id
AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
AND s.salesrep_id = srp.salesrep_id
AND nvl(s.status, 'A') = nvl(qp.status, nvl(s.status, 'A'))
--R12
AND srp.org_id = s.org_id
AND srp.org_id = r.org_id
AND srp.org_id = wksht_rec.org_id
AND NOT EXISTS (SELECT 'X'
FROM cn_payment_transactions_all cnpt
WHERE cnpt.payrun_id = wksht_rec.payrun_id
AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
AND cnpt.quota_id = q.quota_id
AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
-- 01/03/03 gasriniv added hold flag check for bug 2710066
AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N')
--R12
AND cnpt.org_id = wksht_rec.org_id)
GROUP BY srp.quota_id,
q.incentive_type_code,
srp.credit_type_id,
srp.period_id,
srp.salesrep_id,
r.payroll_flag,
qp.pay_element_type_id) v1;
l_batch_rec.last_updated_by := fnd_global.user_id;
l_batch_rec.last_update_date := SYSDATE;
l_batch_rec.last_update_login := fnd_global.login_id;
cn_prepostbatches.begin_record(x_operation => 'INSERT',
x_rowid => l_rowid,
x_posting_batch_rec => l_batch_rec,
x_program_type => NULL,
p_org_id => wksht_rec.org_id);
SELECT cn_posting_batches_s.NEXTVAL
INTO carryover_posting_batch_id
FROM dual;
INSERT INTO cn_payment_transactions
(payment_transaction_id,
posting_batch_id,
incentive_type_code,
credit_type_id,
pay_period_id,
amount,
payment_amount,
credited_salesrep_id,
payee_salesrep_id,
paid_flag,
hold_flag,
waive_flag,
payrun_id,
quota_id,
pay_element_type_id,
created_by,
creation_date,
--R12
org_id)
SELECT cn_payment_transactions_s.NEXTVAL,
carryover_posting_batch_id,
'COMMISSION',
srp.credit_type_id,
srp.period_id,
nvl((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0),
nvl((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0),
srp.salesrep_id,
srp.salesrep_id,
'N',
'N',
'N',
wksht_rec.payrun_id,
-1000,
decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
g_created_by,
SYSDATE,
--R12
wksht_rec.org_id
FROM cn_srp_periods srp,
cn_quota_pay_elements_all qp,
cn_rs_salesreps s,
cn_repositories r
WHERE srp.salesrep_id = wksht_rec.salesrep_id
AND srp.period_id = wksht_rec.pay_period_id
AND srp.credit_type_id = -1000
AND srp.quota_id = -1000
AND nvl((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0) <> 0
AND qp.quota_id(+) = srp.quota_id
AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
AND s.salesrep_id = srp.salesrep_id
AND nvl(s.status, 'A') = nvl(qp.status, nvl(s.status, 'A'))
--R12
AND srp.org_id = s.org_id
AND srp.org_id = r.org_id
AND srp.org_id = wksht_rec.org_id
AND NOT EXISTS (SELECT 'X'
FROM cn_payment_transactions cnpt
WHERE cnpt.payrun_id = wksht_rec.payrun_id
AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
AND cnpt.quota_id = -1000
-- 07/18/03 check exist only for commission/bonus
AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N'));
l_batch_rec.last_updated_by := fnd_global.user_id;
l_batch_rec.last_update_date := SYSDATE;
l_batch_rec.last_update_login := fnd_global.login_id;
cn_prepostbatches.begin_record(x_operation => 'INSERT',
x_rowid => l_rowid,
x_posting_batch_rec => l_batch_rec,
x_program_type => NULL,
p_org_id => wksht_rec.org_id);
UPDATE cn_payment_transactions cnpt
SET ( amount, payment_amount) = (SELECT cnpt.amount - SUM(cnptheld.amount),
cnpt.payment_amount - SUM(cnptheld.amount)
FROM cn_payment_transactions cnptheld
WHERE cnptheld.payrun_id = wksht_rec.payrun_id
AND cnptheld.credited_salesrep_id = wksht_rec.salesrep_id
AND cnptheld.quota_id = cnpt.quota_id
AND cnptheld.hold_flag = 'Y'
AND cnptheld.paid_flag = 'N'
--R12
AND cnptheld.org_id = wksht_rec.org_id),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE cnpt.payrun_id = wksht_rec.payrun_id
AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
AND cnpt.hold_flag = 'N'
AND cnpt.paid_flag = 'N'
AND incentive_type_code IN ('COMMISSION', 'BONUS')
--R12
AND cnpt.org_id = wksht_rec.org_id
AND EXISTS (SELECT 'X'
FROM cn_payment_transactions cnptchk
WHERE cnptchk.payrun_id = wksht_rec.payrun_id
AND cnptchk.credited_salesrep_id = wksht_rec.salesrep_id
AND cnptchk.quota_id = cnpt.quota_id
AND cnptchk.hold_flag = 'Y'
--R12
AND cnptchk.org_id = wksht_rec.org_id);
INSERT INTO cn_payment_transactions
(payment_transaction_id,
posting_batch_id,
trx_type,
payee_salesrep_id,
role_id,
incentive_type_code,
credit_type_id,
pay_period_id,
amount,
commission_header_id,
commission_line_id,
srp_plan_assign_id,
quota_id,
credited_salesrep_id,
processed_period_id,
quota_rule_id,
event_factor,
payment_factor,
quota_factor,
input_achieved,
rate_tier_id,
payee_line_id,
commission_rate,
hold_flag,
paid_flag,
waive_flag,
recoverable_flag,
payrun_id,
payment_amount,
pay_element_type_id,
creation_date,
created_by,
--R12
org_id,
object_version_number,
processed_date)
SELECT
cn_payment_transactions_s.NEXTVAL,
l_posting_batch_id,
cl.trx_type,
cl.credited_salesrep_id,
cl.role_id,
pe.incentive_type_code,
pe.credit_type_id,
cl.pay_period_id,
nvl(cl.commission_amount, 0),
cl.commission_header_id,
cl.commission_line_id,
cl.srp_plan_assign_id,
cl.quota_id,
cl.credited_salesrep_id,
cl.processed_period_id,
cl.quota_rule_id,
cl.event_factor,
cl.payment_factor,
cl.quota_factor,
cl.input_achieved,
cl.rate_tier_id,
cl.payee_line_id,
cl.commission_rate,
'N',
'N',
'N',
'N',
wksht_rec.payrun_id,
nvl(cl.commission_amount, 0),
-- Bug 2875120 : remove cn_api function call in sql statement
decode(l_payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
SYSDATE,
fnd_global.user_id,
--R12
wksht_rec.org_id,
1,
cl.processed_date
FROM cn_commission_lines cl,
cn_quotas_all pe,
cn_quota_pay_elements qp
WHERE cl.credited_salesrep_id = wksht_rec.salesrep_id
AND cl.processed_period_id <= wksht_rec.pay_period_id
AND cl.processed_date <= wksht_rec.pay_date
AND cl.status = 'CALC'
AND cl.srp_payee_assign_id IS NULL
AND cl.posting_status = 'UNPOSTED'
AND cl.quota_id = pe.quota_id
AND cl.credit_type_id = -1000
AND pe.incentive_type_code = decode(nvl(wksht_rec.incentive_type_code, pe.incentive_type_code),
'COMMISSION',
'COMMISSION',
'BONUS',
'BONUS',
pe.incentive_type_code)
AND qp.quota_id(+) = cl.quota_id
AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
AND nvl(l_srp_status, 'A') = nvl(qp.status, nvl(l_srp_status, 'A'))
AND cl.org_id = wksht_rec.org_id;
INSERT INTO cn_payment_transactions
(payment_transaction_id,
posting_batch_id,
trx_type,
payee_salesrep_id,
role_id,
incentive_type_code,
credit_type_id,
pay_period_id,
amount,
commission_header_id,
commission_line_id,
srp_plan_assign_id,
quota_id,
credited_salesrep_id,
processed_period_id,
quota_rule_id,
event_factor,
payment_factor,
quota_factor,
input_achieved,
rate_tier_id,
payee_line_id,
commission_rate,
hold_flag,
paid_flag,
waive_flag,
recoverable_flag,
payrun_id,
payment_amount,
pay_element_type_id,
creation_date,
created_by,
--R12
org_id,
object_version_number,
processed_date)
SELECT
cn_payment_transactions_s.NEXTVAL,
l_posting_batch_id,
cl.trx_type,
spayee.payee_id,
cl.role_id,
pe.incentive_type_code,
pe.credit_type_id,
cl.pay_period_id,
nvl(cl.commission_amount, 0),
cl.commission_header_id,
cl.commission_line_id,
cl.srp_plan_assign_id,
cl.quota_id,
spayee.payee_id,
cl.processed_period_id,
cl.quota_rule_id,
cl.event_factor,
cl.payment_factor,
cl.quota_factor,
cl.input_achieved,
cl.rate_tier_id,
cl.payee_line_id,
cl.commission_rate,
'N',
'N',
'N',
'N',
wksht_rec.payrun_id,
nvl(cl.commission_amount, 0),
-- Bug 2875120 : remove cn_api function call in sql statement
decode(l_payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
SYSDATE,
fnd_global.user_id,
--R12
wksht_rec.org_id,
1,
cl.processed_date
FROM cn_commission_lines cl,
cn_srp_payee_assigns_all spayee,
cn_quotas_all pe,
cn_quota_pay_elements_all qp
WHERE cl.srp_payee_assign_id IS NOT NULL
AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
AND spayee.payee_id = wksht_rec.salesrep_id
AND cl.credited_salesrep_id = spayee.salesrep_id
AND cl.processed_period_id <= wksht_rec.pay_period_id
AND cl.processed_date <= wksht_rec.pay_date
AND cl.status = 'CALC'
AND cl.posting_status = 'UNPOSTED'
AND cl.quota_id = pe.quota_id
AND cl.credit_type_id = -1000
AND pe.incentive_type_code = decode(nvl(wksht_rec.incentive_type_code, pe.incentive_type_code),
'COMMISSION',
'COMMISSION',
'BONUS',
'BONUS',
pe.incentive_type_code)
AND qp.quota_id(+) = cl.quota_id
AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
AND nvl(l_srp_status, 'A') = nvl(qp.status, nvl(l_srp_status, 'A'))
AND cl.org_id = spayee.org_id
AND cl.org_id = wksht_rec.org_id;
UPDATE cn_payment_transactions cnpt
SET payrun_id = wksht_rec.payrun_id,
pay_element_type_id = (SELECT decode(l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
FROM cn_quota_pay_elements p
WHERE p.quota_id = cnpt.quota_id
AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
AND nvl(l_srp_status, 'A') = nvl(p.status, nvl(l_srp_status, 'A'))),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE credited_salesrep_id = wksht_rec.salesrep_id
AND pay_period_id <= wksht_rec.pay_period_id
AND incentive_type_code =
decode(nvl(wksht_rec.incentive_type_code, incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', incentive_type_code)
AND incentive_type_code IN ('COMMISSION', 'BONUS')
AND payrun_id IS NULL
AND processed_date <= wksht_rec.pay_date;
UPDATE cn_payment_transactions cnpt
SET pay_element_type_id = (SELECT decode(l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
FROM cn_quota_pay_elements p
WHERE p.quota_id = decode(cnpt.incentive_type_code, 'PMTPLN_REC', -1001, cnpt.quota_id)
AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
AND nvl(l_srp_status, 'A') = nvl(p.status, nvl(l_srp_status, 'A'))
AND p.org_id = wksht_rec.org_id),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE credited_salesrep_id = wksht_rec.salesrep_id
AND payrun_id = wksht_rec.payrun_id;
UPDATE cn_payment_transactions cnpt
SET amount = l_calc_rec_tbl(i).pmt_amount_adj_rec + l_calc_rec_tbl(i).pmt_amount_adj_nrec,
payment_amount = l_calc_rec_tbl(i).pmt_amount_adj_rec + l_calc_rec_tbl(i).pmt_amount_adj_nrec,
pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
FROM cn_quota_pay_elements p,
cn_rs_salesreps s,
cn_repositories r
WHERE p.quota_id = cnpt.quota_id
AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
AND s.salesrep_id = cnpt.credited_salesrep_id
AND nvl(s.status, 'A') = p.status
--R12
AND p.org_id = wksht_rec.org_id
AND s.org_id = wksht_rec.org_id
AND r.org_id = wksht_rec.org_id),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE credited_salesrep_id = wksht_rec.salesrep_id
AND payrun_id = wksht_rec.payrun_id
AND incentive_type_code = 'PMTPLN'
AND quota_id = l_calc_rec_tbl(i).quota_id
--R12
AND cnpt.org_id = wksht_rec.org_id;
SELECT cn_posting_batches_s.NEXTVAL
INTO recv_posting_batch_id
FROM dual;
l_batch_rec.last_updated_by := fnd_global.user_id;
l_batch_rec.last_update_date := SYSDATE;
l_batch_rec.last_update_login := fnd_global.login_id;
cn_prepostbatches.begin_record(x_operation => 'INSERT',
x_rowid => l_rowid,
x_posting_batch_rec => l_batch_rec,
x_program_type => NULL,
p_org_id => wksht_rec.org_id);
cn_pmt_trans_pkg.insert_record(p_tran_rec => l_pmt_trans_rec);
UPDATE cn_payment_transactions cnpt
SET amount = 0,
payment_amount = 0,
pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
FROM cn_quota_pay_elements p,
cn_rs_salesreps s,
cn_repositories r
WHERE p.quota_id = cnpt.quota_id
AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
AND s.salesrep_id = cnpt.credited_salesrep_id
AND nvl(s.status, 'A') = p.status
--R12
AND p.org_id = wksht_rec.org_id
AND s.org_id = wksht_rec.org_id
AND r.org_id = wksht_rec.org_id),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE incentive_type_code = 'PMTPLN'
AND payrun_id = wksht_rec.payrun_id
AND credited_salesrep_id = wksht_rec.salesrep_id
AND quota_id = l_calc_rec_tbl(i).quota_id
--R12
AND cnpt.org_id = wksht_rec.org_id;
UPDATE cn_payment_worksheets
SET pmt_amount_calc = nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0),
pmt_amount_adj_rec = nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0),
pmt_amount_adj_nrec = nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0),
pmt_amount_adj = nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0),
pmt_amount_recovery = nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0),
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE payrun_id = wksht_rec.payrun_id
AND salesrep_id = wksht_rec.salesrep_id
AND quota_id = l_calc_rec_tbl(i).quota_id;
cn_payment_worksheets_pkg.insert_record(x_payrun_id => wksht_rec.payrun_id,
x_salesrep_id => wksht_rec.salesrep_id,
x_quota_id => l_calc_rec_tbl(i).quota_id,
x_credit_type_id => -1000,
x_calc_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0),
x_adj_pmt_amount_rec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0),
x_adj_pmt_amount_nrec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0),
x_adj_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0),
x_pmt_amount_recovery => nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0),
x_worksheet_status => 'UNPAID',
x_created_by => g_created_by,
x_creation_date => SYSDATE,
p_org_id => wksht_rec.org_id,
p_object_version_number => 1);
UPDATE cn_payment_worksheets
SET pmt_amount_calc = l_calc_pmt_amount,
pmt_amount_adj_rec = l_adj_pmt_amount_rec,
pmt_amount_adj_nrec = l_adj_pmt_amount_nrec,
pmt_amount_adj = l_pmt_amount_ctr,
pmt_amount_recovery = l_pmt_amount_rec,
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
object_version_number = nvl(object_version_number, 0) + 1
WHERE payrun_id = wksht_rec.payrun_id
AND salesrep_id = wksht_rec.salesrep_id
AND quota_id IS NULL;
update_ptd_details (
p_salesrep_id => wksht_rec.salesrep_id ,
p_payrun_id => wksht_rec.payrun_id
) ;
UPDATE cn_commission_lines cls
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE posting_status <> 'POSTED'
AND status = 'CALC'
AND srp_payee_assign_id IS NULL
AND commission_line_id IN (SELECT commission_line_id
FROM cn_payment_transactions
WHERE posting_batch_id = l_posting_batch_id);
UPDATE cn_commission_lines cls
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE posting_status <> 'POSTED'
AND status = 'CALC'
AND srp_payee_assign_id IS NOT NULL
AND commission_line_id IN (SELECT commission_line_id
FROM cn_payment_transactions
WHERE posting_batch_id = l_posting_batch_id);
SELECT DISTINCT pw.quota_id
BULK COLLECT INTO l_wk_plan_elements
FROM cn_payment_worksheets pw
WHERE pw.payrun_id = wksht_rec.payrun_id
AND pw.salesrep_id = wksht_rec.salesrep_id
AND pw.quota_id IS NOT NULL ;
UPDATE cn_commission_lines cls
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE posting_status <> 'POSTED'
AND credit_type_id = g_credit_type_id
AND processed_period_id <= wksht_rec.pay_period_id
AND status = 'CALC'
AND srp_payee_assign_id IS NULL
--R12
AND org_id = wksht_rec.org_id
AND credited_salesrep_id = wksht_rec.salesrep_id
AND quota_id = l_wk_plan_elements(m);
UPDATE cn_commission_lines clk
SET posting_status = 'POSTED',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE processed_period_id <= wksht_rec.pay_period_id
AND status = 'CALC'
AND credit_type_id = g_credit_type_id
AND posting_status <> 'POSTED'
AND org_id = wksht_rec.org_id
AND clk.srp_payee_assign_id IS NOT NULL
AND EXISTS (SELECT 1
FROM cn_srp_payee_assigns_all spayee,
cn_payment_worksheets wksht
WHERE clk.srp_payee_assign_id = spayee.srp_payee_assign_id
AND spayee.quota_id = wksht.quota_id
AND spayee.payee_id = wksht_rec.salesrep_id
AND wksht.payrun_id = wksht_rec.payrun_id
AND wksht.salesrep_id = wksht_rec.salesrep_id);
UPDATE cn_payment_worksheets
SET object_version_number = nvl(object_version_number, 0) + 1,
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE (payrun_id, salesrep_id) IN (SELECT payrun_id,
salesrep_id
FROM cn_payment_worksheets
WHERE payment_worksheet_id = p_worksheet_id);
SELECT object_version_number
INTO x_ovn
FROM cn_payment_worksheets
WHERE payment_worksheet_id = p_worksheet_id;
ROLLBACK TO update_worksheet;
ROLLBACK TO update_worksheet;
ROLLBACK TO update_worksheet;
END update_worksheet;
PROCEDURE delete_worksheet
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER,
p_validation_only IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
p_ovn IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet';
SELECT wk.salesrep_id,
wk.payrun_id,
wk.org_id,
pr.payrun_mode
FROM cn_payment_worksheets wk,
cn_payruns pr
WHERE payment_worksheet_id = p_worksheet_id
AND wk.payrun_id = pr.payrun_id;
SAVEPOINT delete_worksheet;
x_loading_status := 'CN_DELETED';
UPDATE cn_payment_transactions
SET payrun_id = NULL,
waive_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE payrun_id = wksht.payrun_id
AND credited_salesrep_id = wksht.salesrep_id
AND incentive_type_code = 'PMTPLN_REC';
DELETE FROM cn_payment_transactions
WHERE incentive_type_code IN ('PMTPLN', 'MANUAL_PAY_ADJ')
AND payrun_id = wksht.payrun_id
AND credited_salesrep_id = wksht.salesrep_id;
UPDATE cn_payment_transactions
SET payrun_id = NULL,
payment_amount = amount,
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE payrun_id = wksht.payrun_id
AND credited_salesrep_id = wksht.salesrep_id
AND commission_line_id IS NOT NULL;
DELETE FROM cn_payment_transactions
WHERE payrun_id = wksht.payrun_id
AND credited_salesrep_id = wksht.salesrep_id
AND nvl(hold_flag, 'N') = 'N';
UPDATE cn_payment_transactions
SET payrun_id = '',
last_update_date = SYSDATE,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE payrun_id = wksht.payrun_id
AND credited_salesrep_id = wksht.salesrep_id
AND nvl(hold_flag, 'N') = 'Y';
DELETE FROM cn_posting_batches cnpb
WHERE cnpb.posting_batch_id IN (SELECT cnpd.posting_batch_id
FROM cn_payment_transactions cnpd
WHERE cnpd.payrun_id = wksht.payrun_id
AND cnpd.credited_salesrep_id = wksht.salesrep_id
AND nvl(cnpd.hold_flag, 'N') = 'N');
cn_payment_worksheets_pkg.delete_record(p_salesrep_id => wksht.salesrep_id, p_payrun_id => wksht.payrun_id);
ROLLBACK TO delete_worksheet;
ROLLBACK TO delete_worksheet;
ROLLBACK TO delete_worksheet;
END delete_worksheet;
SELECT w.worksheet_status wksht_status,
w.quota_id,
w.salesrep_id,
p.status payrun_status,
p.pay_period_id,
p.payrun_id,
w.org_id --R12
FROM cn_payment_worksheets w,
cn_payruns p
WHERE w.payment_worksheet_id = p_worksheet_id
AND w.payrun_id = p.payrun_id
--R12
AND w.org_id = p.org_id;
SELECT bb_prior_period_adj,
bb_pmt_recovery_plans,
current_earnings
INTO x_bb_prior_period_adj,
x_bb_pmt_recovery_plans,
x_curr_earnings
FROM cn_payment_worksheets
WHERE payment_worksheet_id = p_worksheet_id;
SELECT SUM(nvl(balance2_dtd, 0) - nvl(balance2_ctd, 0)) curr_earnings
INTO x_curr_earnings
FROM cn_srp_periods srp
WHERE srp.salesrep_id = l_wksht_rec.salesrep_id
AND srp.period_id = l_wksht_rec.pay_period_id
AND srp.quota_id IS NOT NULL
AND srp.credit_type_id = g_credit_type_id
--R12
AND srp.org_id = l_wksht_rec.org_id;
SELECT SUM(nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) pri_adj,
- (SUM(nvl(balance4_bbd, 0) - nvl(balance4_bbc, 0))) - (SUM(nvl(balance4_dtd, 0) - nvl(balance4_ctd, 0))) pmt_recovery
INTO x_bb_prior_period_adj,
x_bb_pmt_recovery_plans
FROM cn_srp_periods srp
WHERE srp.quota_id IS NULL
AND srp.salesrep_id = l_wksht_rec.salesrep_id
AND srp.period_id = l_wksht_rec.pay_period_id
AND srp.credit_type_id = g_credit_type_id
--R12
AND srp.org_id = l_wksht_rec.org_id;
SELECT SUM(nvl(amount,0))
INTO l_held_amount_prior
FROM cn_payment_transactions cnpt
WHERE cnpt.quota_id IS NOT NULL
AND cnpt.credited_salesrep_id = l_wksht_rec.salesrep_id
AND cnpt.pay_period_id < l_wksht_rec.pay_period_id
AND cnpt.credit_type_id = G_credit_type_id
AND cnpt.hold_flag = 'Y'
AND cnpt.paid_flag ='N'
;
SELECT w.worksheet_status wksht_status,
w.salesrep_id,
p.status payrun_status,
p.pay_period_id,
p.payrun_id,
w.org_id
FROM cn_payment_worksheets w,
cn_payruns p
WHERE w.payment_worksheet_id = p_worksheet_id
AND w.payrun_id = p.payrun_id;
SELECT w.payment_worksheet_id,
w.quota_id,
w.salesrep_id,
w.object_version_number
FROM cn_payment_worksheets w
WHERE w.payrun_id = l_payrun_id
AND w.salesrep_id = l_srp_id
AND w.quota_id IS NULL
AND w.org_id = p_org_id;
SELECT SUM(nvl(balance2_dtd, 0) - nvl(balance2_ctd, 0)) curr_earnings
INTO l_curr_earnings
FROM cn_srp_periods srp
WHERE srp.salesrep_id = l_wksht_sum_rec.salesrep_id
AND srp.period_id = l_status_rec.pay_period_id
AND srp.quota_id IS NOT NULL
AND srp.credit_type_id = g_credit_type_id
AND srp.org_id = l_status_rec.org_id;
SELECT SUM(nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) pri_adj,
- (SUM(nvl(balance4_bbd, 0) - nvl(balance4_bbc, 0))) - (SUM(nvl(balance4_dtd, 0) - nvl(balance4_ctd, 0))) pmt_recovery
INTO s_bb_prior_period_adj,
s_bb_pmt_recovery_plans
FROM cn_srp_periods srp
WHERE srp.quota_id IS NULL
AND srp.salesrep_id = l_wksht_sum_rec.salesrep_id
AND srp.period_id = l_status_rec.pay_period_id
AND srp.credit_type_id = g_credit_type_id
AND srp.org_id = l_status_rec.org_id;
UPDATE cn_payment_worksheets
SET bb_prior_period_adj = s_bb_prior_period_adj,
bb_pmt_recovery_plans = s_bb_pmt_recovery_plans,
current_earnings = l_curr_earnings,
current_earnings_due = s_curr_earnings_due,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id,
object_version_number = l_wksht_sum_rec.object_version_number + 1
WHERE payment_worksheet_id = l_wksht_sum_rec.payment_worksheet_id;
SELECT pr.PAYRUN_ID
INTO l_payrun_id
FROM cn_payruns pr
WHERE pr.PAYRUN_ID = p_payrun_id
FOR UPDATE NOWAIT;
SELECT cn_process_batches_s2.NEXTVAL
INTO l_logical_batch_id
FROM sys.dual;
SELECT cn_process_batches_s3.NEXTVAL
INTO l_physical_batch_id
FROM sys.dual;
INSERT INTO cn_process_batches
(process_batch_id,
logical_batch_id,
physical_batch_id,
srp_period_id,
period_id,
salesrep_id,
status_code,
org_id,
process_batch_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(cn_process_batches_s1.NEXTVAL,
l_logical_batch_id,
l_physical_batch_id,
1,
1,
p_salesrep_tbl(kk).salesrep_id,
'VOID',
p_org_id,
p_params.conc_program_name,
mysysdate,
fnd_global.user_id,
mysysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
mysysdate);
SELECT COUNT(0)
INTO l_runner_count
FROM fnd_concurrent_requests fcr
WHERE fcr.parent_request_id = l_conc_request_id
AND fcr.phase_code <> 'C';
FOR rs_errors IN (SELECT fcr.request_id,
fcr.actual_completion_date,
fcr.completion_text
FROM fnd_concurrent_requests fcr
WHERE parent_request_id = l_conc_request_id
AND upper(status_code) = 'E')
LOOP
l_error_count := l_error_count + 1;
SELECT COUNT(0)
INTO l_warning_count
FROM fnd_concurrent_requests fcr
WHERE parent_request_id = l_conc_request_id
AND upper(status_code) = 'G';
SELECT payrun_id,
status
FROM cn_payruns pr
WHERE NAME = p_name
AND org_id = mo_global.get_current_org_id;
SELECT salesrep_id,
ceil(rownum / l_batch_sz) BULK COLLECT
INTO salesrep_t
FROM (SELECT DISTINCT wk.salesrep_id
FROM cn_payment_worksheets wk
WHERE wk.worksheet_status = 'UNPAID'
AND wk.quota_id IS NULL
AND wk.payrun_id = l_payrun_id
AND wk.org_id = mo_global.get_current_org_id);
x_loading_status VARCHAR2(20) := 'CN_UPDATED';
FOR emp IN (SELECT salesrep_id
FROM cn_process_batches
WHERE logical_batch_id = p_logical_batch_id
AND physical_batch_id = p_batch_id)
LOOP
-- Run refresh worksheet for this salesrep.
l_worksheet_rec.salesrep_id := emp.salesrep_id;
SELECT wk.payment_worksheet_id,wk.object_version_number
INTO l_worksheet_rec.worksheet_id,l_ovn
FROM cn_payment_worksheets_all wk
WHERE wk.payrun_id = l_worksheet_rec.payrun_id
AND wk.salesrep_id = l_worksheet_rec.salesrep_id
AND quota_id IS NULL;
cn_payment_worksheet_pvt.update_worksheet(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'F',
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_worksheet_id => l_worksheet_rec.worksheet_id,
p_operation => 'REFRESH',
x_loading_status => x_loading_status,
x_status => x_status,
x_ovn => l_ovn
);