[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pmttrxeo.payment_transaction_id,
pmttrxeo.quota_id quotaId,
pmttrxeo.hold_flag,
pmttrxeo.amount,
pmttrxeo.payment_amount,
pmttrxeo.payrun_id,
pmttrxeo.credited_salesrep_id,
pmttrxeo.org_id,
pmttrxeo.object_version_number,
pmttrxeo.incentive_type_code,
pmttrxeo.waive_flag,
pmttrxeo.recoverable_flag
FROM
cn_payment_transactions pmttrxeo,
cn_commission_headers ch,
(select cust_acct.cust_account_id customer_id,
party.party_name customer_name
from hz_parties party, hz_cust_accounts cust_acct
where cust_acct.party_id = party.party_id) cus
WHERE
pmttrxeo.payrun_id = c_payrun_id
and pmttrxeo.credited_salesrep_id = c_salesrep_id
and pmttrxeo.incentive_type_code in ('COMMISSION','BONUS')
and pmttrxeo.commission_header_id = ch.commission_header_id
and pmttrxeo.quota_id = NVL(c_quota_id, pmttrxeo.quota_id)
and (ch.revenue_class_id = NVL(c_revenue_class_id, ch.revenue_class_id)
OR (ch.revenue_class_id IS NULL and c_revenue_class_id IS NULL))
and pmttrxeo.hold_flag LIKE NVL(c_hold_flag, pmttrxeo.hold_flag)
-- hold or release only when necessary
and pmttrxeo.hold_flag = DECODE(c_action, 'HOLD_ALL', 'N', 'RELEASE_ALL', 'Y', pmttrxeo.hold_flag)
and NVL(ch.invoice_number, '%') LIKE NVL(c_invoice_number, NVL(ch.invoice_number, '%'))
and (ch.order_number = nvl(c_order_number,ch.order_number)
OR (c_order_number IS NULL and ch.order_number IS NULL))
and nvl(ch.customer_id,-0.9999) = cus.customer_id(+)
and NVL(cus.customer_name, '%') LIKE NVL(c_customer_name, NVL(cus.customer_name, '%')) ;
SELECT worksheet_status,
request_id,
payment_worksheet_id,
object_version_number,
org_id
INTO l_status, l_request_id, p_rec.worksheet_id,l_ovn, p_rec.org_id
FROM cn_payment_worksheets
WHERE quota_id is null
AND payrun_id = p_rec.payrun_id
AND salesrep_id = p_rec.salesrep_id ;
PROCEDURE update_pmt_transactions (
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_payment_transaction_id IN cn_payment_transactions.payment_transaction_id%TYPE,
p_hold_flag IN cn_payment_transactions.hold_flag%TYPE,
p_recoverable_flag IN cn_payment_transactions.recoverable_flag%TYPE,
p_payment_amount IN cn_payment_transactions.payment_amount%TYPE,
p_waive_flag IN cn_payment_transactions.waive_flag%TYPE,
p_incentive_type_code IN cn_payment_transactions.incentive_type_code%TYPE,
p_payrun_id IN cn_payment_transactions.payrun_id%TYPE,
p_salesrep_id IN cn_payment_transactions.credited_salesrep_id%TYPE,
x_status OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
--R12
p_org_id IN cn_payment_transactions.org_id%TYPE,
p_object_version_number IN OUT NOCOPY cn_payment_transactions.object_version_number%TYPE
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Pmt_Transactions';
SELECT payment_transaction_id,
amount,
payment_amount,
hold_flag,
recoverable_flag,
quota_id,
payrun_id,
credited_salesrep_id,
--R12
org_id,
object_version_number ovn
FROM cn_payment_transactions
WHERE payment_transaction_id = p_payment_transaction_id;
SELECT NVL (SUM (NVL (amount, 0)), 0) earn_amount
-- 12/25/02 RC bug 2710066
-- Commenting out waive_flag and quota_id from group by
-- quota_id
--, waive_flag
FROM cn_payment_transactions
WHERE payrun_id = p_payrun_id
AND credited_salesrep_id = p_salesrep_id
AND NVL (paid_flag, 'N') = 'N'
AND NVL (hold_flag, 'N') = 'N'
AND incentive_type_code IN ('COMMISSION', 'BONUS')
AND quota_id = p_quota_id
--R12
AND org_id = p_org_id;
SELECT NVL (SUM (NVL (pmt_amount_calc, 0)), 0) earn_amount
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id IS NULL;
SELECT NVL (SUM (NVL (pmt_amount_calc, 0)), 0) earn_amount
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id AND quota_id IS NOT NULL
AND org_id = p_org_id;
SELECT *
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND EXISTS (
SELECT 1
FROM cn_payment_transactions
WHERE payrun_id = p_payrun_id
AND credited_salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND NVL (waive_flag, 'N') = 'N'
AND incentive_type_code <> 'PMTPLN');
SELECT *
FROM cn_payment_worksheets
WHERE salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND payrun_id = p_payrun_id;
SELECT NVL (SUM (NVL (amount, 0)), 0) mpa_amount
FROM cn_payment_transactions
WHERE credited_salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND payrun_id = p_payrun_id
AND incentive_type_code = 'MANUAL_PAY_ADJ' ;
SELECT NVL (SUM (NVL (payment_amount, 0) - NVL (amount, 0)), 0) cp_amount
FROM cn_payment_transactions
WHERE credited_salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND payrun_id = p_payrun_id
AND incentive_type_code NOT IN ('PMTPLN', 'PMTPLN_REC')
AND hold_flag <> 'Y'
;
SELECT NVL (SUM (NVL (payment_amount, 0)), 0) ph_amount
FROM cn_payment_transactions
WHERE credited_salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND payrun_id = p_payrun_id
AND hold_flag = 'Y'
;
SELECT -NVL (SUM (NVL (payment_amount, 0)), 0) wv_amount
FROM cn_payment_transactions
WHERE credited_salesrep_id = p_salesrep_id
-- AND quota_id is null
AND payrun_id = p_payrun_id
AND waive_flag = 'Y'
AND quota_id = p_quota_id
;
SELECT NVL (SUM (NVL (amount, 0)), 0) amount,
NVL (SUM (NVL (payment_amount, 0)), 0) payment_amount
FROM cn_payment_transactions
WHERE credited_salesrep_id = p_salesrep_id
AND quota_id = p_quota_id
AND payrun_id = p_payrun_id
AND (hold_flag = 'N' OR hold_flag IS NULL)
AND incentive_type_code IN ('COMMISSION', 'BONUS')
;
SELECT -NVL (SUM (NVL (payment_amount, 0)), 0) payment_amount,
quota_id
FROM cn_payment_transactions
WHERE payrun_id = p_payrun_id
AND credited_salesrep_id = p_salesrep_id
AND credit_type_id = -1000
AND incentive_type_code = 'PMTPLN_REC'
GROUP BY quota_id;
SELECT NVL (waive_flag, 'N') waive_flag,
object_version_number ovn
FROM cn_payment_transactions
WHERE payrun_id = p_payrun_id
AND credited_salesrep_id = p_salesrep_id
AND credit_type_id = -1000
AND incentive_type_code = 'PMTPLN_REC'
AND ROWNUM < 2;
SAVEPOINT update_pmt_transactions;
x_loading_status := 'UPDATED';
UPDATE cn_payment_transactions
SET hold_flag = p_hold_flag,
recoverable_flag = DECODE (p_incentive_type_code, 'MANUAL_PAY_ADJ', p_recoverable_flag, 'N'),
-- bug 3146137
amount = DECODE (p_incentive_type_code, 'MANUAL_PAY_ADJ', p_payment_amount, amount),
payment_amount = p_payment_amount,
waive_flag = DECODE (p_incentive_type_code, 'PMTPLN_REC', p_waive_flag, 'N'),
-- bug 3080846
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = nvl(object_version_number,1) + 1
WHERE payment_transaction_id = p_payment_transaction_id;
SELECT object_version_number
INTO l_wksht_ovn
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id IS NULL;
UPDATE cn_payment_worksheets
SET pmt_amount_adj = NVL (pmt_amount_adj, 0) + l_waive_amount,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id = waive_per_quota.quota_id
;
UPDATE cn_payment_worksheets
SET pmt_amount_adj = NVL (pmt_amount_adj, 0) + l_waive_amount_total,
object_version_number = nvl(object_version_number,0) + 1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id IS NULL
;
UPDATE cn_payment_transactions
SET waive_flag = p_waive_flag,
object_version_number = nvl(object_version_number,0) + 1,
-- bug 3080846
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE incentive_type_code = 'PMTPLN_REC'
AND credited_salesrep_id = p_salesrep_id
AND payrun_id = p_payrun_id
;
DELETE FROM cn_payment_transactions
WHERE payment_transaction_id IN (
SELECT payment_transaction_id
FROM cn_payment_transactions
WHERE quota_id = pmt_trans_rec.quota_id
AND payrun_id = p_payrun_id
AND credited_salesrep_id = p_salesrep_id
AND (hold_flag = 'N' OR hold_flag IS NULL)
AND incentive_type_code IN ('COMMISSION', 'BONUS')
)
AND payment_transaction_id <> p_payment_transaction_id;
UPDATE cn_payment_transactions
SET amount = pmt_trans_rec_amount.amount,
payment_amount = pmt_trans_rec_amount.payment_amount,
object_version_number = object_version_number + 1,
-- bug 3080846
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_transaction_id = p_payment_transaction_id
AND payrun_id = p_payrun_id
AND credited_salesrep_id = p_salesrep_id
AND (hold_flag = 'N' OR hold_flag IS NULL) ;
UPDATE cn_payment_worksheets
SET pmt_amount_adj = l_wv + l_cp + l_mpa,
held_amount = l_ph,
pmt_amount_calc = l_earn,
object_version_number = object_version_number + 1,
-- bug 3080846
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_worksheet_id = worksheet.payment_worksheet_id;
UPDATE cn_payment_worksheets
SET pmt_amount_adj =
(SELECT NVL (SUM (NVL (pmt_amount_adj, 0)), 0)
FROM cn_payment_worksheets
WHERE quota_id IS NOT NULL
AND salesrep_id = pmt_trans_rec.credited_salesrep_id
AND payrun_id = pmt_trans_rec.payrun_id
),
held_amount =
(SELECT NVL (SUM (NVL (held_amount, 0)), 0)
FROM cn_payment_worksheets
WHERE quota_id IS NOT NULL
AND salesrep_id = pmt_trans_rec.credited_salesrep_id
AND payrun_id = pmt_trans_rec.payrun_id
),
pmt_amount_calc =
(SELECT l_delta_earn + NVL (SUM (NVL (pmt_amount_calc, 0)), 0)
FROM cn_payment_worksheets
WHERE quota_id IS NOT NULL
AND salesrep_id = pmt_trans_rec.credited_salesrep_id
AND payrun_id = pmt_trans_rec.payrun_id
),
object_version_number = object_version_number + 1,
-- bug 3080846
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quota_id IS NULL
AND salesrep_id = pmt_trans_rec.credited_salesrep_id
AND payrun_id = pmt_trans_rec.payrun_id
AND org_id = p_org_id;
SELECT object_version_number
INTO p_object_version_number
FROM cn_payment_transactions
WHERE payment_transaction_id = p_payment_transaction_id;
SELECT object_version_number
INTO p_object_version_number
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id IS NULL;
ROLLBACK TO update_pmt_transactions;
ROLLBACK TO update_pmt_transactions;
ROLLBACK TO update_pmt_transactions;
END update_pmt_transactions;
SELECT payables_flag
FROM cn_repositories
--R12
WHERE org_id = p_org_id;
SELECT pay_period_id,
pay_date
FROM cn_payruns
WHERE payrun_id = p_payrun_id
AND status = 'UNPAID';
x_loading_status := 'CN_INSERTED';
SELECT quota_id
INTO l_quota_id
FROM cn_quotas_v
WHERE quota_id = p_quota_id;
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_org_id
);
cn_pmt_trans_pkg.INSERT_RECORD (p_tran_rec => l_pmt_trans_rec);
UPDATE cn_payment_worksheets
SET pmt_amount_adj = NVL (pmt_amount_adj, 0) + NVL (l_rec_amount, 0) + NVL (l_nrec_amount, 0),
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = NVL(object_version_number+1,1)
WHERE salesrep_id = p_salesrep_id
AND payrun_id = p_payrun_id
AND quota_id = p_quota_id
;
cn_payment_worksheets_pkg.INSERT_RECORD (x_payrun_id => p_payrun_id,
x_salesrep_id => p_salesrep_id,
x_quota_id => p_quota_id,
x_credit_type_id => g_credit_type_id,
x_calc_pmt_amount => 0,
x_adj_pmt_amount_rec => 0,
x_adj_pmt_amount_nrec => 0,
x_adj_pmt_amount => l_rec_amount + l_nrec_amount,
x_pmt_amount_recovery => 0,
x_worksheet_status => 'UNPAID',
x_created_by => fnd_global.user_id,
x_creation_date => SYSDATE,
p_org_id => p_org_id,
p_object_version_number => 1
);
UPDATE cn_payment_worksheets
SET pmt_amount_adj = NVL (pmt_amount_adj, 0) + NVL (l_rec_amount, 0) + NVL (l_nrec_amount, 0),
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = NVL(object_version_number+1,1)
WHERE salesrep_id = p_salesrep_id
AND payrun_id = p_payrun_id
AND quota_id IS NULL
;
PROCEDURE delete_pmt_transactions (
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_payment_transaction_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_Pmt_Transactions';
SELECT payrun_id,
credited_salesrep_id,
payment_amount,
incentive_type_code,
recoverable_flag,
posting_batch_id,
quota_id,
org_id,
object_version_number ovn
FROM cn_payment_transactions
WHERE payment_transaction_id = p_payment_transaction_id;
SAVEPOINT delete_pmt_transactions;
x_loading_status := 'CN_DELETED';
DELETE FROM cn_posting_batches cnpb
WHERE cnpb.posting_batch_id = trans_rec.posting_batch_id;
cn_pmt_trans_pkg.DELETE_RECORD (p_payment_transaction_id);
UPDATE cn_payment_worksheets
SET pmt_amount_adj = NVL (pmt_amount_adj, 0) - NVL (l_adj_rec, 0) - NVL (l_adj_nrec, 0),
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
-- bug 3080846
last_update_login = fnd_global.login_id
WHERE salesrep_id = trans_rec.credited_salesrep_id
AND payrun_id = trans_rec.payrun_id
AND (quota_id = trans_rec.quota_id OR quota_id IS NULL)
;
ROLLBACK TO delete_pmt_transactions;
ROLLBACK TO delete_pmt_transactions;
ROLLBACK TO delete_pmt_transactions;
END delete_pmt_transactions;
SELECT cpt.payment_transaction_id,
cpt.hold_flag,
cpt.recoverable_flag,
cpt.payment_amount,
cpt.waive_flag,
cpt.incentive_type_code,
cp.payrun_id,
cpw.salesrep_id,
--R12
cpw.org_id,
cpt.object_version_number ovn
FROM cn_payruns cp,
cn_payment_worksheets cpw,
cn_payment_transactions cpt
WHERE cpw.payment_worksheet_id = p_payment_worksheet_id
AND cp.payrun_id = cpw.payrun_id
AND cp.payrun_id = cpt.payrun_id
AND cpw.salesrep_id = cpt.credited_salesrep_id
AND cpt.hold_flag = 'Y'
--R12
AND cpw.org_id = cp.org_id
AND cpw.org_id = cpt.org_id;
update_pmt_transactions (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payment_transaction_id => i.payment_transaction_id,
p_hold_flag => 'N',
p_recoverable_flag => i.recoverable_flag,
p_payment_amount => i.payment_amount,
p_waive_flag => i.waive_flag,
p_incentive_type_code => i.incentive_type_code,
p_payrun_id => i.payrun_id,
p_salesrep_id => i.salesrep_id,
x_status => l_status, --Not used by caller anymore
x_loading_status => l_loading_status, --Not used by caller anymore
--R12
p_org_id => i.org_id,
p_object_version_number => i.ovn
);
SELECT
payment_worksheet_id,object_version_number, name
FROM
cn_payment_worksheets wk, cn_salesreps srp
WHERE
quota_id is null
AND wk.payrun_id = p_payrun_id
AND wk.salesrep_id = p_salesrep_id
AND wk.salesrep_id = srp.salesrep_id ;
update_pmt_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,--changed to false
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_payment_transaction_id => transactions_details_rec.payment_transaction_id,
p_hold_flag => l_flag,
p_recoverable_flag => transactions_details_rec.recoverable_flag,
p_payment_amount => transactions_details_rec.payment_amount,
p_waive_flag => transactions_details_rec.waive_flag,
p_incentive_type_code => transactions_details_rec.incentive_type_code,
p_payrun_id => p_payrun_id,
p_salesrep_id => p_salesrep_id,
x_status => l_status,
x_loading_status => l_loading_status,
p_org_id => transactions_details_rec.org_id,
p_object_version_number => transactions_details_rec.object_version_number);
cn_payment_worksheets_pkg.update_status(p_salesrep_id,p_payrun_id,l_new_status) ;
cn_payment_worksheets_pkg.update_status(p_salesrep_id,p_payrun_id, 'FAILED') ;
UPDATE cn_payment_worksheets
SET request_id = p_rec.request_id,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payrun_id = p_rec.payrun_id
AND salesrep_id = p_rec.salesrep_id
AND quota_id IS NULL ;
cn_payment_worksheet_pvt.update_worksheet (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_worksheet_id => p_rec.worksheet_id,
p_operation => p_rec.p_action,
x_status => l_status,
x_loading_status => l_status,
x_ovn => p_rec.object_version_number
) ;