[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_loading_status := 'CN_INSERTED';
SELECT status, NVL(posting_status, C_UNPOSTED)
FROM cn_commission_lines
WHERE commission_line_id = p_posting_detail_rec.commission_line_id;
(SELECT CN_API.G_MISS_ID payment_transaction_id,
CN_PREPOSTBATCHES.G_BATCH_ID posting_batch_id,
--C_EXPENSE posting_type,
cl.credited_salesrep_id,
cl.credited_salesrep_id payee_salesrep_id,
cl.quota_id,
cl.pay_period_id,
pe.incentive_type_code,
cl.credit_type_id,
NULL, -- payrun_id
nvl(cl.commission_amount,0) amount,
nvl(cl.commission_amount,0) payment_amount, -- default
'N' hold_flag, -- default N
'N' paid_flag, -- default N
'N' waive_flag, -- default N
'N' recoverable_flag, -- default N
cl.commission_header_id,
cl.commission_line_id,
null, -- pay_element_type_id
cl.srp_plan_assign_id,
cl.processed_date,
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,
cl.trx_type,
cl.role_id,
pe.expense_account_id expense_ccid,
pe.liability_account_id liability_ccid,
NULL, --cl.attribute_category,
NULL, --cl.attribute1,
null, --cl.attribute2,
null, --cl.attribute3,
null, --cl.attribute4,
null, --cl.attribute5,
null, --cl.attribute6,
null, --cl.attribute7,
null, --cl.attribute8,
null, --cl.attribute9,
null, --cl.attribute10,
null, --cl.attribute11,
null, --cl.attribute12,
null, --cl.attribute13,
null, --cl.attribute14,
null, --cl.attribute15
cl.org_id,
0
/*C_UNLOADED status, --default UNLOADED
FND_API.G_MISS_DATE loaded_date,
cl.pending_status,
cl.status cl_status,
cl.created_during,
FND_GLOBAL.USER_ID created_by,
SYSDATE creation_date,
FND_GLOBAL.LOGIN_ID last_update_login,
SYSDATE last_update_date,
FND_GLOBAL.USER_ID last_updated_by*/
FROM cn_commission_lines_all cl,
-- cn_srp_plan_assigns srcp,
--cn_srp_payee_assigns srpa, removed for payee assigns bug #2495614
-- cn_role_plans rcp,
cn_quotas_all pe
WHERE cl.commission_line_id = p_commission_line_id
AND cl.quota_id = pe.quota_id
AND cl.srp_payee_assign_id IS NULL)
UNION --this is added for assign payees for fixing bug#2495614
(SELECT CN_API.G_MISS_ID payment_transaction_id,
CN_PREPOSTBATCHES.G_BATCH_ID posting_batch_id,
payee.payee_id credited_salesrep_id,
payee.payee_id payee_salesrep_id,
cl.quota_id,
cl.pay_period_id,
pe.incentive_type_code,
cl.credit_type_id,
NULL, -- payrun_id
nvl(cl.commission_amount,0) amount,
nvl(cl.commission_amount,0) payment_amount, -- default
'N' hold_flag, -- default N
'N' paid_flag, -- default N
'N' waive_flag, -- default N
'N' recoverable_flag, -- default N
cl.commission_header_id,
cl.commission_line_id,
null, -- pay_element_type_id
cl.srp_plan_assign_id,
cl.processed_date,
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,
cl.trx_type,
54,--cl.role_id
pe.expense_account_id expense_ccid,
pe.liability_account_id liability_ccid,
NULL, --cl.attribute_category,
NULL, --cl.attribute1,
null, --cl.attribute2,
null, --cl.attribute3,
null, --cl.attribute4,
null, --cl.attribute5,
null, --cl.attribute6,
null, --cl.attribute7,
null, --cl.attribute8,
null, --cl.attribute9,
null, --cl.attribute10,
null, --cl.attribute11,
null, --cl.attribute12,
null, --cl.attribute13,
null, --cl.attribute14,
null, --cl.attribute15
cl.org_id,
0
FROM cn_commission_lines_all cl,
-- cn_srp_plan_assigns srcp,
cn_srp_payee_assigns_all payee,
-- cn_role_plans rcp,
cn_quotas_all pe
WHERE cl.commission_line_id = p_commission_line_id
AND cl.quota_id = pe.quota_id
AND cl.srp_payee_assign_id IS NOT NULL
AND payee.srp_payee_assign_id = cl.srp_payee_assign_id);
l_loading_status := 'CN_INSERTED';
DELETE FROM cn_pmt_trans
WHERE posting_type = l_pmt_trans_rec.posting_type
AND payee_salesrep_id = l_pmt_trans_rec.payee_salesrep_id
AND role_id = l_pmt_trans_rec.role_id
AND credit_type_id = l_pmt_trans_rec.credit_type_id
AND pay_period_id = l_pmt_trans_rec.pay_period_id
AND amount = (0 - l_pmt_trans_rec.amount)
AND commission_header_id = l_pmt_trans_rec.commission_header_id
AND srp_plan_assign_id = l_pmt_trans_rec.srp_plan_assign_id
AND quota_id = l_pmt_trans_rec.quota_id
AND status = l_pmt_trans_rec.status
AND credited_salesrep_id = l_pmt_trans_rec.credited_salesrep_id
AND paid_flag = l_pmt_trans_rec.paid_flag
AND ROWNUM = 1;
CN_PMT_TRANS_PKG.Insert_Record(l_pmt_trans_rec);
UPDATE cn_commission_lines
SET posting_status = C_POSTED
WHERE commission_line_id = p_commission_line_id;*/
UPDATE cn_commission_lines_all
SET posting_status = C_REVERTED
WHERE commission_line_id = p_commission_line_id;
l_loading_status := 'CN_INSERTED';
x_operation => 'INSERT',
x_rowid => G_ROWID,
x_posting_detail_rec => p_posting_detail_rec_tbl(i),
x_program_type => G_PROGRAM_TYPE
);
SELECT ws.PAYMENT_WORKSHEET_ID ,
-- ws.PAYRUN_ID ,
ws.SALESREP_ID , -- payee_salesrep_id
ws.ROLE_ID ,
ws.CREDIT_TYPE_ID ,
pr.PAY_DATE , -- posted_date
pr.PAY_PERIOD_ID ,
ws.COMM_NREC COMM_NREC,
ws.DRAW_PAID COMM_REC, -- comm rec
ws.COMM_DRAW COMM_TO_REC, -- to recov w pp
ws.REG_BONUS_REC BONUS_REC,
ws.REG_BONUS_TO_REC BONUS_TO_REC
-- ws.POSTING_STATUS
FROM cn_payment_worksheets ws,
cn_payruns pr,
cn_posting_batches pb,
cn_period_statuses pp
WHERE pr.payrun_id = ws.payrun_id
and pr.pay_period_id = pp.period_id
AND pp.start_date BETWEEN pb.start_date AND pb.end_date
AND ( pr.status = 'PAID'
OR pr.status = 'PAID_WITH_RETURNS')
AND ws.posting_status = C_UNPOSTED
AND pb.load_status = C_UNLOADED
AND pb.posting_batch_id = CN_PREPOSTBATCHES.G_BATCH_ID;
SELECT wsb.quota_id ,
wsb.srp_plan_assign_id ,
wsb.amount
FROM cn_worksheet_bonuses wsb
WHERE wsb.payment_worksheet_id = v_payment_worksheet_id;
select pr.payrun_id
from cn_payruns pr,
cn_period_statuses ps,
cn_posting_batches pb
where pr.pay_period_id = ps.period_id
and (pr.status = 'PAID' or pr.status = 'PAID_WITH_RETURNS')
and pb.load_status = C_UNLOADED
and pb.posting_batch_id = CN_PREPOSTBATCHES.G_BATCH_ID
and ps.start_date between pb.start_date and pb.end_date
and not exists ( select 1
from cn_payment_worksheets ws
where ws.payrun_id = pr.payrun_id
and ws.posting_status = C_UNPOSTED
);
l_loading_status := 'CN_INSERTED'; -- Set worksheet as properly inserted
cn_payment_worksheets_pkg.update_record
(
x_payment_worksheet_id => l_pay_ws_rec.payment_worksheet_id,
x_posting_status => c_posted,
x_last_update_date => SYSDATE,
x_last_updated_by => FND_GLOBAL.USER_ID,
x_last_update_login => FND_GLOBAL.LOGIN_ID);
cn_payruns_pkg.update_record(x_payrun_id => l_posted_payrun.payrun_id,
x_status => C_POSTED,
x_last_updated_by => FND_GLOBAL.USER_ID,
x_last_update_date => SYSDATE,
x_last_update_login => FND_GLOBAL.LOGIN_ID);