[Home] [Help]
6: --G_CREATION_DATE DATE := SYSDATE;
7: g_created_by NUMBER := fnd_global.user_id;
8: g_last_update_login NUMBER := fnd_global.login_id;
9: --Bug 3866089 (the same as 11.5.8 bug 3841926, 11.5.10 3866116) by jjhuang on 11/1/04
10: g_payment_transaction_id cn_payment_transactions.payment_transaction_id%TYPE;
11:
12: --==============================================================================
13: -- Procedure : Get_UID
14: -- Description : Get the sequence number to create a new Payment Transactions
18: )
19: IS
20: CURSOR get_id
21: IS
22: SELECT cn_payment_transactions_s.NEXTVAL
23: FROM DUAL;
24: BEGIN
25: OPEN get_id;
26:
34: END get_uid;
35:
36: --==============================================================================
37: -- Procedure : Insert_Record
38: -- Description : Insert Record into Cn_payment_Transactions
39: -- Called From: cnvpmtrb.pls ( Create Manual Transactions )
40: --==============================================================================
41: PROCEDURE INSERT_RECORD (
42: p_tran_rec IN pmt_trans_rec_type
48: -- Get Unique ID for posting detail
49: get_uid (l_payment_transaction_id);
50:
51: -- Insert Record
52: INSERT INTO cn_payment_transactions
53: (payment_transaction_id,
54: posting_batch_id,
55: credited_salesrep_id,
56: payee_salesrep_id,
175: p_payment_transaction_id NUMBER
176: )
177: IS
178: BEGIN
179: DELETE FROM cn_payment_transactions
180: WHERE payment_transaction_id = p_payment_transaction_id;
181: END DELETE_RECORD;
182:
183: --============================================================================
237: AND s.org_id = r.org_id
238: AND r.org_id = p_org_id;
239:
240: l_held_amount NUMBER;
241: l_quota_id cn_payment_transactions.quota_id%TYPE;
242:
243: l_payroll_flag cn_repositories.payroll_flag%TYPE;
244: l_org_id cn_repositories.org_id%TYPE ;
245: l_status cn_salesreps.status%TYPE ;
262: -- 11/21/02 RC Added the following code to remove
263: -- non payment plan recovery transactions
264: -- when profile is changed from pay by transaction
265: -- to pay by summary
266: DELETE FROM cn_payment_transactions
267: WHERE incentive_type_code <> 'PMTPLN_REC'
268: AND NVL (hold_flag, 'N') = 'N'
269: AND NVL (paid_flag, 'N') = 'N'
270: AND credited_salesrep_id = p_salesrep_id
276: FOR each_quota IN get_quotas
277: LOOP
278: SELECT NVL (SUM (NVL (amount, 0)), 0)
279: INTO l_held_amount
280: FROM cn_payment_transactions
281: WHERE credited_salesrep_id = p_salesrep_id
282: AND pay_period_id <= p_pay_period_id
283: AND quota_id = each_quota.quota_id
284: AND payrun_id IS NULL
287:
288: -- Bug 2868584 :Add SUM and Group By clause
289: -- handle scenarios where a salesrep has multiple role assignments
290: -- during the same period, with an overlapping quota assignment
291: INSERT INTO cn_payment_transactions
292: (payment_transaction_id,
293: posting_batch_id,
294: incentive_type_code,
295: credit_type_id,
314: --Bug 3080846 for who columns
315: org_id,
316: object_version_number
317: )
318: SELECT cn_payment_transactions_s.NEXTVAL,
319: p_posting_batch_id,
320: v1.incentive_type_code,
321: v1.credit_type_id,
322: v1.period_id,
373: -- Bug 2819874 - Insert the carry over Record,regardless incentive type
374: -- code
375: SELECT NVL (SUM (NVL (amount, 0)), 0)
376: INTO l_held_amount
377: FROM cn_payment_transactions
378: WHERE credited_salesrep_id = p_salesrep_id
379: AND pay_period_id <= p_pay_period_id
380: AND quota_id = -1000
381: AND payrun_id IS NULL
383: AND NVL (paid_flag, 'N') = 'N'
384: --R12
385: AND org_id = p_org_id;
386:
387: INSERT INTO cn_payment_transactions
388: (payment_transaction_id,
389: posting_batch_id,
390: incentive_type_code,
391: credit_type_id,
410: last_update_login,
411: org_id,
412: object_version_number
413: )
414: SELECT cn_payment_transactions_s.NEXTVAL,
415: p_posting_batch_id,
416: 'COMMISSION',
417: srp.credit_type_id,
418: srp.period_id,
460: --Payee bug 3140343.
461: IF cn_api.is_payee (p_salesrep_id, p_pay_period_id, p_org_id) = 1
462: THEN
463: --New insert here for payee
464: INSERT INTO cn_payment_transactions
465: (payment_transaction_id,
466: posting_batch_id,
467: trx_type,
468: payee_salesrep_id,
500: org_id,
501: object_version_number,
502: processed_date
503: )
504: SELECT cn_payment_transactions_s.NEXTVAL,
505: p_posting_batch_id,
506: cl.trx_type,
507: spayee.payee_id,
508: cl.role_id,
566: AND cl.org_id = spayee.org_id
567: AND cl.org_id = p_org_id ;
568:
569: ELSE
570: INSERT INTO cn_payment_transactions
571: (payment_transaction_id,
572: posting_batch_id,
573: trx_type,
574: payee_salesrep_id,
606: org_id,
607: object_version_number,
608: processed_date
609: )
610: SELECT cn_payment_transactions_s.NEXTVAL,
611: p_posting_batch_id,
612: cl.trx_type,
613: cl.credited_salesrep_id,
614: cl.role_id,
679:
680: --PBS
681: IF p_pay_by_transaction = 'N'
682: THEN
683: UPDATE cn_payment_transactions ptx
684: SET payrun_id = p_payrun_id,
685: pay_element_type_id =
686: (SELECT DECODE (l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
687: FROM cn_quota_pay_elements p
704: AND payrun_id IS NULL
705: --R12
706: AND org_id = p_org_id;
707: ELSE --PBT
708: UPDATE cn_payment_transactions ptx
709: SET payrun_id = p_payrun_id,
710: pay_element_type_id =
711: (SELECT DECODE (l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
712: FROM cn_quota_pay_elements p
732: AND ptx.org_id = p_org_id ;
733:
734: END IF;
735:
736: UPDATE cn_payment_transactions ptx
737: SET payrun_id = p_payrun_id,
738: pay_element_type_id =
739: (SELECT DECODE (l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
740: FROM cn_quota_pay_elements p