[Home] [Help]
PACKAGE BODY: APPS.CN_PMT_TRANS_PKG
Source
1 PACKAGE BODY cn_pmt_trans_pkg AS
2 -- $Header: cntpmtrb.pls 120.9.12000000.2 2007/06/08 21:22:10 fmburu ship $ --+
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_PMT_TRANS_PKG';
4 --G_LAST_UPDATE_DATE DATE := SYSDATE;
5 g_last_updated_by NUMBER := fnd_global.user_id;
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
15 --==============================================================================
16 PROCEDURE get_uid (
17 x_payment_transaction_id IN OUT NOCOPY NUMBER
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
27 FETCH get_id
28 INTO x_payment_transaction_id;
29
30 CLOSE get_id;
31
32 --Bug 3866089 (the same as 11.5.8 bug 3841926, 11.5.10 3866116) by jjhuang on 11/1/04
33 g_payment_transaction_id := x_payment_transaction_id;
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
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2 (30) := 'Insert_Record';
46 l_payment_transaction_id NUMBER;
47 BEGIN
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,
57 quota_id,
58 pay_period_id,
59 incentive_type_code,
60 credit_type_id,
61 payrun_id,
62 amount,
63 payment_amount,
64 hold_flag,
65 paid_flag,
66 waive_flag,
67 recoverable_flag,
68 commission_header_id,
69 commission_line_id,
70 pay_element_type_id,
71 srp_plan_assign_id,
72 processed_date,
73 processed_period_id,
74 quota_rule_id,
75 event_factor,
76 payment_factor,
77 quota_factor,
78 input_achieved,
79 rate_tier_id,
80 payee_line_id,
81 commission_rate,
82 trx_type,
83 role_id,
84 expense_ccid,
85 liability_ccid,
86 attribute_category,
87 attribute1,
88 attribute2,
89 attribute3,
90 attribute4,
91 attribute5,
92 attribute6,
93 attribute7,
94 attribute8,
95 attribute9,
96 attribute10,
97 attribute11,
98 attribute12,
99 attribute13,
100 attribute14,
101 attribute15,
102 last_update_date,
103 last_updated_by,
104 last_update_login,
105 creation_date,
106 created_by,
107 --R12
108 org_id,
109 object_version_number
110 )
111 (SELECT l_payment_transaction_id,
112 DECODE (p_tran_rec.posting_batch_id, cn_api.g_miss_id, NULL, p_tran_rec.posting_batch_id),
113 DECODE (p_tran_rec.credited_salesrep_id, cn_api.g_miss_id, NULL, p_tran_rec.credited_salesrep_id),
114 DECODE (p_tran_rec.payee_salesrep_id, cn_api.g_miss_id, NULL, p_tran_rec.payee_salesrep_id),
115 DECODE (p_tran_rec.quota_id, cn_api.g_miss_id, NULL, p_tran_rec.quota_id),
116 DECODE (p_tran_rec.pay_period_id, cn_api.g_miss_id, NULL, p_tran_rec.pay_period_id),
117 DECODE (p_tran_rec.incentive_type_code, fnd_api.g_miss_char, NULL, p_tran_rec.incentive_type_code),
118 DECODE (p_tran_rec.credit_type_id, cn_api.g_miss_id, -1000, p_tran_rec.credit_type_id),
119 DECODE (p_tran_rec.payrun_id, cn_api.g_miss_id, NULL, p_tran_rec.payrun_id),
120 DECODE (p_tran_rec.amount, cn_api.g_miss_num, 0, p_tran_rec.amount),
121 DECODE (p_tran_rec.payment_amount, cn_api.g_miss_num, 0, p_tran_rec.payment_amount),
122 DECODE (p_tran_rec.hold_flag, fnd_api.g_miss_char, 'N', p_tran_rec.hold_flag),
123 DECODE (p_tran_rec.paid_flag, fnd_api.g_miss_char, 'N', p_tran_rec.paid_flag),
124 DECODE (p_tran_rec.waive_flag, fnd_api.g_miss_char, 'N', p_tran_rec.waive_flag),
125 DECODE (p_tran_rec.recoverable_flag, fnd_api.g_miss_char, 'N', p_tran_rec.recoverable_flag),
126 DECODE (p_tran_rec.commission_header_id, cn_api.g_miss_id, NULL, p_tran_rec.commission_header_id),
127 DECODE (p_tran_rec.commission_line_id, cn_api.g_miss_id, NULL, p_tran_rec.commission_line_id),
128 DECODE (p_tran_rec.pay_element_type_id, cn_api.g_miss_id, NULL, p_tran_rec.pay_element_type_id),
129 DECODE (p_tran_rec.srp_plan_assign_id, cn_api.g_miss_id, NULL, p_tran_rec.srp_plan_assign_id),
130 DECODE (p_tran_rec.processed_date, fnd_api.g_miss_date, NULL, p_tran_rec.processed_date),
131 DECODE (p_tran_rec.processed_period_id, cn_api.g_miss_id, NULL, p_tran_rec.processed_period_id),
132 DECODE (p_tran_rec.quota_rule_id, cn_api.g_miss_id, NULL, p_tran_rec.quota_rule_id),
133 DECODE (p_tran_rec.event_factor, cn_api.g_miss_num, NULL, p_tran_rec.event_factor),
134 DECODE (p_tran_rec.payment_factor, cn_api.g_miss_num, NULL, p_tran_rec.payment_factor),
135 DECODE (p_tran_rec.quota_factor, cn_api.g_miss_num, NULL, p_tran_rec.quota_factor),
136 DECODE (p_tran_rec.input_achieved, cn_api.g_miss_num, NULL, p_tran_rec.input_achieved),
137 DECODE (p_tran_rec.rate_tier_id, cn_api.g_miss_id, NULL, p_tran_rec.rate_tier_id),
138 DECODE (p_tran_rec.payee_line_id, cn_api.g_miss_id, NULL, p_tran_rec.payee_line_id),
139 DECODE (p_tran_rec.commission_rate, cn_api.g_miss_num, NULL, p_tran_rec.commission_rate),
140 DECODE (p_tran_rec.trx_type, fnd_api.g_miss_char, NULL, p_tran_rec.trx_type),
141 DECODE (p_tran_rec.role_id, cn_api.g_miss_id, NULL, p_tran_rec.role_id),
142 DECODE (p_tran_rec.expense_ccid, cn_api.g_miss_id, NULL, p_tran_rec.expense_ccid),
143 DECODE (p_tran_rec.liability_ccid, cn_api.g_miss_id, NULL, p_tran_rec.liability_ccid),
144 DECODE (p_tran_rec.attribute_category, fnd_api.g_miss_char, NULL, p_tran_rec.attribute_category),
145 DECODE (p_tran_rec.attribute1, fnd_api.g_miss_char, NULL, p_tran_rec.attribute1),
146 DECODE (p_tran_rec.attribute2, fnd_api.g_miss_char, NULL, p_tran_rec.attribute2),
147 DECODE (p_tran_rec.attribute3, fnd_api.g_miss_char, NULL, p_tran_rec.attribute3),
148 DECODE (p_tran_rec.attribute4, fnd_api.g_miss_char, NULL, p_tran_rec.attribute4),
149 DECODE (p_tran_rec.attribute5, fnd_api.g_miss_char, NULL, p_tran_rec.attribute5),
150 DECODE (p_tran_rec.attribute6, fnd_api.g_miss_char, NULL, p_tran_rec.attribute6),
151 DECODE (p_tran_rec.attribute7, fnd_api.g_miss_char, NULL, p_tran_rec.attribute7),
152 DECODE (p_tran_rec.attribute8, fnd_api.g_miss_char, NULL, p_tran_rec.attribute8),
153 DECODE (p_tran_rec.attribute9, fnd_api.g_miss_char, NULL, p_tran_rec.attribute9),
154 DECODE (p_tran_rec.attribute10, fnd_api.g_miss_char, NULL, p_tran_rec.attribute10),
155 DECODE (p_tran_rec.attribute11, fnd_api.g_miss_char, NULL, p_tran_rec.attribute11),
156 DECODE (p_tran_rec.attribute12, fnd_api.g_miss_char, NULL, p_tran_rec.attribute12),
157 DECODE (p_tran_rec.attribute13, fnd_api.g_miss_char, NULL, p_tran_rec.attribute13),
158 DECODE (p_tran_rec.attribute14, fnd_api.g_miss_char, NULL, p_tran_rec.attribute14),
159 DECODE (p_tran_rec.attribute15, fnd_api.g_miss_char, NULL, p_tran_rec.attribute15),
160 SYSDATE,
161 fnd_global.user_id,
162 fnd_global.login_id,
163 SYSDATE,
164 fnd_global.user_id,
165 p_tran_rec.org_id,
166 nvl(p_tran_rec.object_version_number,1)
167 FROM DUAL);
168 END INSERT_RECORD;
169
170 --============================================================================
171 -- Procedure Name : Delete_Record
172 -- Purpose : Delete the Payment Transactions
173 --============================================================================
174 PROCEDURE DELETE_RECORD (
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 --============================================================================
184 -- Procedure Name: Insert Record ( Batch Insert record
185 -- Description: Insert Record API called from Create Worksheet
186 -- If the User calls this Table Hander
187 -- we need need to check the pay by transaction value
188 -- if the value is N we need to insert the record from
189 -- cn_commission_lines
190 -- if the value is Y then we need to insert from
191 -- cn_srp_periods
192 -- for all Quota ID and the Null QUOTA ID
193 -- Called from : cnvwkshb.pls ( Worksheet Creation )
194 --============================================================================
195 PROCEDURE INSERT_RECORD (
196 p_pay_by_transaction IN VARCHAR2,
197 p_salesrep_id IN NUMBER,
198 p_payrun_id IN NUMBER,
199 p_pay_date IN DATE,
200 p_incentive_type IN VARCHAR2,
201 p_pay_period_id IN NUMBER,
202 p_credit_type_id IN NUMBER,
203 p_posting_batch_id IN NUMBER,
204 --R12
205 p_org_id IN NUMBER
206 )
207 IS
208 -- Bug 2875120/2892822 : remove cn_api function call in sql statement
209 -- Bug 2972172: Added distinct to get only one quota_id.
210 CURSOR get_quotas
211 IS
212 SELECT DISTINCT q.quota_id,
213 q.incentive_type_code,
214 DECODE (r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id
215 FROM cn_srp_periods srp,
216 cn_quotas_all q,
217 cn_quota_pay_elements_all qp,
218 cn_rs_salesreps s,
219 cn_repositories r
220 WHERE srp.salesrep_id = p_salesrep_id
221 AND srp.period_id = p_pay_period_id
222 AND srp.quota_id = q.quota_id
223 AND srp.credit_type_id = p_credit_type_id
224 AND q.incentive_type_code =
225 DECODE (NVL (p_incentive_type, q.incentive_type_code),
226 'COMMISSION', 'COMMISSION',
227 'BONUS', 'BONUS',
228 q.incentive_type_code
229 )
230 AND qp.quota_id(+) = q.quota_id
231 AND p_pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
232 AND s.salesrep_id = srp.salesrep_id
233 AND NVL (s.status, 'A') = NVL (qp.status, NVL (s.status, 'A'))
234 --R12
235 AND srp.org_id = q.org_id
236 AND q.org_id = s.org_id
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 ;
246 l_period_set_id cn_repositories.period_set_id%TYPE ;
247 l_period_type_id cn_repositories.period_type_id%TYPE ;
248
249 BEGIN
250
251 SELECT s.org_id, s.status, r.period_set_id, r.period_type_id, NVL(r.payroll_flag,'N')
252 INTO l_org_id, l_status, l_period_set_id, l_period_type_id, l_payroll_flag
253 FROM cn_salesreps s, cn_repositories_all r, cn_payruns_all pr
254 WHERE s.salesrep_id = p_salesrep_id
255 AND s.org_id = r.org_id
256 AND pr.org_id = r.org_id
257 AND pr.payrun_id = p_payrun_id
258 ;
259
260 IF p_pay_by_transaction = 'N'
261 THEN
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
271 AND pay_period_id <= p_pay_period_id
272 AND payrun_id IS NULL
273 --R12
274 AND org_id = p_org_id;
275
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
285 AND NVL (hold_flag, 'N') = 'Y'
286 AND NVL (paid_flag, 'N') = 'N' ;
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,
296 pay_period_id,
297 amount,
298 payment_amount,
299 credited_salesrep_id,
300 payee_salesrep_id,
301 paid_flag,
302 hold_flag,
303 waive_flag,
304 payrun_id,
305 quota_id,
306 pay_element_type_id,
307 created_by,
308 creation_date,
309 last_update_date,
310 --Bug 3080846 for who columns
311 last_updated_by,
312 --Bug 3080846 for who columns
313 last_update_login,
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,
323 v1.amount,
324 v1.payment_amount,
325 v1.salesrep_id,
326 v1.salesrep_id,
327 'N',
328 'N',
329 'N',
330 p_payrun_id,
331 v1.quota_id,
332 v1.pay_element_type_id,
333 g_created_by,
334 SYSDATE,
335 SYSDATE,
336 --Bug 3080846 for who columns
337 g_last_updated_by,
338 g_last_update_login,
339 --Bug 3080846 for who columns
340 --R12
341 v1.org_id,
342 1
343 FROM (SELECT each_quota.incentive_type_code incentive_type_code,
344 srp.credit_type_id,
345 srp.period_id,
346 SUM ( (NVL (srp.balance2_dtd, 0) - NVL (srp.balance2_ctd, 0) + NVL (srp.balance2_bbd, 0) - NVL (srp.balance2_bbc, 0))
347 - l_held_amount
348 ) amount,
349 SUM ( (NVL (srp.balance2_dtd, 0) - NVL (srp.balance2_ctd, 0) + NVL (srp.balance2_bbd, 0) - NVL (srp.balance2_bbc, 0))
350 - l_held_amount
351 ) payment_amount,
352 srp.salesrep_id,
353 srp.quota_id,
354 each_quota.pay_element_type_id pay_element_type_id,
355 --R12
356 srp.org_id
357 FROM cn_srp_periods srp
358 WHERE srp.salesrep_id = p_salesrep_id
359 AND srp.period_id = p_pay_period_id
360 AND srp.quota_id = each_quota.quota_id
361 AND srp.quota_id <> -1000
362 -- Bug 2819874:add carry over record
363 AND srp.credit_type_id = p_credit_type_id
364 --R12
365 AND srp.org_id = p_org_id
366 GROUP BY srp.quota_id,
367 srp.credit_type_id,
368 srp.period_id,
369 srp.salesrep_id,
370 srp.org_id) v1;
371 END LOOP;
372
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
382 AND NVL (hold_flag, 'N') = 'Y'
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,
392 pay_period_id,
393 amount,
394 payment_amount,
395 credited_salesrep_id,
396 payee_salesrep_id,
397 paid_flag,
398 hold_flag,
399 waive_flag,
400 payrun_id,
401 quota_id,
402 pay_element_type_id,
403 -- Bug 2880233 : add pay_element_type_id
404 created_by,
405 creation_date,
406 last_update_date,
407 --Bug 3080846 for who columns
408 last_updated_by,
409 --Bug 3080846 for who columns
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,
419 NVL ((NVL (srp.balance2_dtd, 0) - NVL (srp.balance2_ctd, 0) + NVL (srp.balance2_bbd, 0) - NVL (srp.balance2_bbc, 0)), 0)
420 - l_held_amount,
421 NVL ((NVL (srp.balance2_dtd, 0) - NVL (srp.balance2_ctd, 0) + NVL (srp.balance2_bbd, 0) - NVL (srp.balance2_bbc, 0)), 0)
422 - l_held_amount,
423 srp.salesrep_id,
424 srp.salesrep_id,
425 'N',
426 'N',
427 'N',
428 p_payrun_id,
429 -1000,
430 DECODE (r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
431 g_created_by,
432 SYSDATE,
433 SYSDATE,
434 --Bug 3080846 for who columns
435 g_last_updated_by,
436 --Bug 3080846 for who columns
437 g_last_update_login,--Bug 3080846 for who columns
438 srp.org_id,
439 1
440 FROM cn_srp_periods srp,
441 cn_quota_pay_elements_all qp,
442 cn_rs_salesreps s,
443 cn_repositories r
444 WHERE srp.salesrep_id = p_salesrep_id
445 AND srp.period_id = p_pay_period_id
446 AND srp.credit_type_id = p_credit_type_id
447 AND srp.quota_id = -1000
448 AND NVL ((NVL (srp.balance2_dtd, 0) - NVL (srp.balance2_ctd, 0) + NVL (srp.balance2_bbd, 0) - NVL (srp.balance2_bbc, 0)), 0)
449 - l_held_amount <> 0
450 AND qp.quota_id(+) = srp.quota_id
451 AND p_pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
452 AND s.salesrep_id = srp.salesrep_id
453 AND NVL (s.status, 'A') = NVL (qp.status, NVL (s.status, 'A'))
454 --R12
455 AND srp.org_id = r.org_id
456 AND s.org_id = p_org_id
457 AND r.org_id = p_org_id;
458 ELSE
459 -- pay by transaction
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,
469 role_id,
470 incentive_type_code,
471 credit_type_id,
472 pay_period_id,
473 amount,
474 commission_header_id,
475 commission_line_id,
476 srp_plan_assign_id,
477 quota_id,
478 credited_salesrep_id,
479 processed_period_id,
480 quota_rule_id,
481 event_factor,
482 payment_factor,
483 quota_factor,
484 input_achieved,
485 rate_tier_id,
486 payee_line_id,
487 commission_rate,
488 hold_flag,
489 paid_flag,
490 waive_flag,
491 recoverable_flag,
492 payrun_id,
493 payment_amount,
494 pay_element_type_id,
495 creation_date,
496 created_by,
497 last_update_date,
498 last_updated_by,
499 last_update_login,
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,
509 pe.incentive_type_code,
510 cl.credit_type_id,
511 cl.pay_period_id,
512 NVL (cl.commission_amount, 0),
513 cl.commission_header_id,
514 cl.commission_line_id,
515 cl.srp_plan_assign_id,
516 cl.quota_id,
517 spayee.payee_id,
518 cl.processed_period_id,
519 cl.quota_rule_id,
520 cl.event_factor,
521 cl.payment_factor,
522 cl.quota_factor,
523 cl.input_achieved,
524 cl.rate_tier_id,
525 cl.payee_line_id,
526 cl.commission_rate,
527 'N',
528 'N',
529 'N',
530 'N',
531 p_payrun_id,
532 NVL (cl.commission_amount, 0),
533 DECODE (l_payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
534 SYSDATE,
535 fnd_global.user_id,
536 SYSDATE,
537 g_last_updated_by,
538 g_last_update_login,
539 cl.org_id,
540 1,
541 cl.processed_date
542 FROM cn_commission_lines cl,
543 cn_quotas_all pe,
544 cn_quota_pay_elements_all qp,
545 cn_srp_payee_assigns_all spayee
546 WHERE cl.srp_payee_assign_id IS NOT NULL
547 AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
548 AND spayee.payee_id = p_salesrep_id
549 AND cl.credited_salesrep_id = spayee.salesrep_id
550 AND cl.processed_period_id <= p_pay_period_id
551 AND cl.status = 'CALC'
552 AND cl.posting_status = 'UNPOSTED'
553 AND cl.quota_id = pe.quota_id
554 AND cl.credit_type_id = p_credit_type_id
555 AND pe.incentive_type_code =
556 DECODE (NVL (p_incentive_type, pe.incentive_type_code),
557 'COMMISSION', 'COMMISSION',
558 'BONUS', 'BONUS',
559 pe.incentive_type_code
560 )
561 AND qp.quota_id(+) = cl.quota_id
562 AND p_pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
563 AND cl.org_id = spayee.org_id
564 AND NVL (l_status, 'A') = NVL (qp.status, NVL (l_status, 'A'))
565 AND cl.processed_date <= p_pay_date
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,
575 role_id,
576 incentive_type_code,
577 credit_type_id,
578 pay_period_id,
579 amount,
580 commission_header_id,
581 commission_line_id,
582 srp_plan_assign_id,
583 quota_id,
584 credited_salesrep_id,
585 processed_period_id,
586 quota_rule_id,
587 event_factor,
588 payment_factor,
589 quota_factor,
590 input_achieved,
591 rate_tier_id,
592 payee_line_id,
593 commission_rate,
594 hold_flag,
595 paid_flag,
596 waive_flag,
597 recoverable_flag,
598 payrun_id,
599 payment_amount,
600 pay_element_type_id,
601 creation_date,
602 created_by,
603 last_update_date,
604 last_updated_by,
605 last_update_login,
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,
615 pe.incentive_type_code,
616 cl.credit_type_id,
617 -- 2/7/03 AC Bug 2792037
618 cl.pay_period_id,
619 NVL (cl.commission_amount, 0),
620 cl.commission_header_id,
621 cl.commission_line_id,
622 cl.srp_plan_assign_id,
623 cl.quota_id,
624 cl.credited_salesrep_id,
625 cl.processed_period_id,
626 cl.quota_rule_id,
627 cl.event_factor,
628 cl.payment_factor,
629 cl.quota_factor,
630 cl.input_achieved,
631 cl.rate_tier_id,
632 cl.payee_line_id,
633 cl.commission_rate,
634 'N',
635 'N',
636 'N',
637 'N',
638 p_payrun_id,
639 NVL (cl.commission_amount, 0),
640 -- Bug 2875120 : remove cn_api function call in sql statement
641 DECODE (l_payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
642 SYSDATE,
643 fnd_global.user_id,
644 SYSDATE,
645 --Bug 3080846 for who columns
646 g_last_updated_by,
647 g_last_update_login,
648 --Bug 3080846 for who columns
649 cl.org_id,
650 1,
651 cl.processed_date
652 FROM cn_commission_lines cl,
653 cn_quotas_all pe,
654 cn_quota_pay_elements_all qp
655 WHERE cl.credited_salesrep_id = p_salesrep_id
656 AND cl.processed_period_id <= p_pay_period_id
657 AND cl.status = 'CALC'
658 AND cl.posting_status = 'UNPOSTED'
659 AND cl.quota_id = pe.quota_id
660 AND cl.credit_type_id = p_credit_type_id
661 AND pe.incentive_type_code =
662 DECODE (NVL (p_incentive_type, pe.incentive_type_code),
663 'COMMISSION', 'COMMISSION',
664 'BONUS', 'BONUS',
665 pe.incentive_type_code
666 )
667 AND qp.quota_id(+) = cl.quota_id
668 AND p_pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
669 AND NVL (l_status, 'A') = NVL (qp.status, NVL (l_status, 'A'))
670 -- Payee bug 3140343.
671 AND cl.srp_payee_assign_id IS NULL
672 -- 3/16/04 Julia Huang for bug 3486328
673 AND cl.processed_date <= p_pay_date
674 AND cl.org_id = p_org_id;
675
676 END IF;
677 --end of checking if it's a payee for payee bug 3140343.
678 END IF;
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
688 WHERE p.quota_id = ptx.quota_id
689 AND p_pay_date BETWEEN p.start_date AND p.end_date
690 AND NVL (l_status, 'A') = p.status),
691 --bug 3080846
692 last_update_date = SYSDATE,
693 last_updated_by = g_last_updated_by,
694 last_update_login = g_last_update_login
695 WHERE credited_salesrep_id = p_salesrep_id
696 AND pay_period_id <= p_pay_period_id
697 AND incentive_type_code =
698 DECODE (NVL (p_incentive_type, incentive_type_code),
699 'COMMISSION', 'COMMISSION',
700 'BONUS', 'BONUS',
701 incentive_type_code
702 )
703 AND incentive_type_code IN ('COMMISSION', 'BONUS')
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
713 WHERE p.quota_id = ptx.quota_id
714 AND p_pay_date BETWEEN p.start_date AND p.end_date
715 AND NVL (l_status, 'A') = p.status),
716 --bug 3080846
717 last_update_date = SYSDATE,
718 last_updated_by = g_last_updated_by,
719 last_update_login = g_last_update_login
720 WHERE credited_salesrep_id = p_salesrep_id
721 AND pay_period_id <= p_pay_period_id
722 AND incentive_type_code =
723 DECODE (NVL (p_incentive_type, incentive_type_code),
724 'COMMISSION', 'COMMISSION',
725 'BONUS', 'BONUS',
726 incentive_type_code
727 )
728 AND incentive_type_code IN ('COMMISSION', 'BONUS')
729 AND payrun_id IS NULL
730 --- bug5170930 instead of looking at the header for processed date
731 AND processed_date <= p_pay_date
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
741 WHERE p.quota_id = -1001
742 AND p_pay_date BETWEEN p.start_date AND p.end_date
743 AND NVL (l_status, 'A') = p.status
744 AND p.org_id = p_org_id),
745 last_update_date = SYSDATE,
746 last_updated_by = g_last_updated_by,
747 last_update_login = g_last_update_login
748 WHERE credited_salesrep_id = p_salesrep_id
749 AND pay_period_id <= p_pay_period_id
750 AND incentive_type_code = 'PMTPLN_REC'
751 AND payrun_id IS NULL
752 --Added by Julia Huang on 10/1/03 for 'COMMISSON' or 'BONUS' type payrun.
753 AND ptx.quota_id IN (
754 SELECT quota_id
755 FROM cn_quotas_all cqa
756 WHERE cqa.quota_id = ptx.quota_id
757 AND cqa.incentive_type_code =
758 DECODE (NVL (p_incentive_type, cqa.incentive_type_code),
759 'COMMISSION', 'COMMISSION',
760 'BONUS', 'BONUS',
761 cqa.incentive_type_code
762 )
763 AND cqa.org_id = p_org_id);
764 END INSERT_RECORD;
765
766 --============================================================================
767 -- Function : get_pmt_tran_id
768 -- Description : Main update procedure to update payment
769 -- transactions
770 -- Called From : cnvpmtrb.pls ( Create_Pmt_Transactions )
771 -- Bug 3866089 (the same as 11.5.8 bug 3841926, 11.5.10 3866116) by jjhuang on 11/1/04
772 --============================================================================
773 FUNCTION get_pmt_tran_id
774 RETURN NUMBER
775 IS
776 BEGIN
777 RETURN g_payment_transaction_id;
778 END get_pmt_tran_id;
779 END cn_pmt_trans_pkg;