[Home] [Help]
PACKAGE BODY: APPS.CN_PROCESS_TAE_TRX_PUB
Source
1 PACKAGE BODY CN_PROCESS_TAE_TRX_PUB AS
2 --$Header: cnpptxwb.pls 120.2.12010000.2 2009/01/29 07:06:19 gmarwah ship $
3
4
5 /*-------------------------------------------------------------------------*
6 | PRIVATE CONSTANTS
7 *-------------------------------------------------------------------------*/
8 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_PROCESS_TAE_TRX_PUB';
9 G_FILE_NAME CONSTANT VARCHAR2(12):='cnpptxwb.pls';
10
11
12 /*-------------------------------------------------------------------------*
13 | PRIVATE DATATYPES
14 *-------------------------------------------------------------------------*/
15
16 /*-------------------------------------------------------------------------*
17 | PRIVATE VARIABLES
18 *-------------------------------------------------------------------------*/
19
20 /*-------------------------------------------------------------------------*
21 | PRIVATE ROUTINES SPECIFICATION
22 *-------------------------------------------------------------------------*/
23
24 FUNCTION get_adjusted_by
25 RETURN VARCHAR2 IS
26 l_adjusted_by VARCHAR2(100) := '0';
27 BEGIN
28 SELECT user_name
29 INTO l_adjusted_by
30 FROM fnd_user
31 WHERE user_id = fnd_profile.value('USER_ID');
32 RETURN l_adjusted_by;
33 EXCEPTION
34 WHEN OTHERS THEN
35 RETURN l_adjusted_by;
36 END;
37
38
39 /*-------------------------------------------------------------------------*
40 | PUBLIC ROUTINES
41 *-------------------------------------------------------------------------*/
42
43 PROCEDURE Process_Trx_Records(
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
46 p_commit IN VARCHAR2 := FND_API.G_FALSE,
47 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
48
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_org_id IN NUMBER)
53 IS
54 CURSOR trx_records IS
55 SELECT WIN.TRANS_OBJECT_ID,
56 WIN.org_id,
57 WIN.role,
58 WIN.resource_id
59 FROM
60 JTF_TAE_1001_SC_WINNERS WIN
61
62 WHERE
63 WIN.SOURCE_ID = -1001 AND
64 WIN.TRANS_OBJECT_TYPE_ID = -1002 AND
65 WIN.ORG_ID=p_org_id;
66
67 TYPE api_id_list is TABLE of cn_comm_lines_api.comm_lines_api_id%TYPE;
68 TYPE org_id_list is TABLE of cn_comm_lines_api.org_id%TYPE;
69 TYPE role_list is TABLE of JTF_TAE_1001_SC_WINNERS.role%TYPE;
70 TYPE resource_id_list is TABLE of JTF_TAE_1001_SC_WINNERS.resource_id%TYPE;
71
72 l_api_id api_id_list;
73 l_org_id org_id_list;
74 l_role role_list;
75 l_resource_id resource_id_list;
76
77 l_max_rows NUMBER := 10000;
78 l_attempts NUMBER := 0;
79 l_exceptions BOOLEAN := FALSE;
80
81 l_api_name CONSTANT VARCHAR2(30) := 'process_trx_records';
82 l_api_version CONSTANT NUMBER := 1.0;
83 l_adjusted_by VARCHAR2(30);
84
85 BEGIN
86
87 l_adjusted_by := get_adjusted_by;
88
89 -- Standard Start of API savepoint
90 SAVEPOINT process_trx_records;
91
92 -- Standard call to check for call compatibility.
93 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
94 p_api_version ,
95 l_api_name,
96 G_PKG_NAME ) THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100 -- Initialize message list if p_init_msg_list is set to TRUE.
101 IF FND_API.to_Boolean( p_init_msg_list ) THEN
102 FND_MSG_PUB.initialize;
103 END IF;
104
105 -- Initialize API return status to success
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108
109 l_attempts := 1;
110 l_exceptions := FALSE;
111
112 --dbms_output.put_line('Before Insert Statement');
113
114 INSERT into CN_COMM_LINES_API
115 ( SALESREP_ID,
116 PROCESSED_DATE,
117 PROCESSED_PERIOD_ID,
118 TRANSACTION_AMOUNT,
119 TRX_TYPE,
120 REVENUE_CLASS_ID,
121 LOAD_STATUS,
122 ATTRIBUTE_CATEGORY,
123 ATTRIBUTE1,
124 ATTRIBUTE2,
125 ATTRIBUTE3,
126 ATTRIBUTE4,
127 ATTRIBUTE5,
128 ATTRIBUTE6,
129 ATTRIBUTE7,
130 ATTRIBUTE8,
131 ATTRIBUTE9,
132 ATTRIBUTE10,
133 ATTRIBUTE11,
134 ATTRIBUTE12,
135 ATTRIBUTE13,
136 ATTRIBUTE14,
137 ATTRIBUTE15,
138 ATTRIBUTE16,
139 ATTRIBUTE17,
140 ATTRIBUTE18,
141 ATTRIBUTE19,
142 ATTRIBUTE20,
143 ATTRIBUTE21,
144 ATTRIBUTE22,
145 ATTRIBUTE23,
146 ATTRIBUTE24,
147 ATTRIBUTE25,
148 ATTRIBUTE26,
149 ATTRIBUTE27,
150 ATTRIBUTE28,
151 ATTRIBUTE29,
152 ATTRIBUTE30,
153 ATTRIBUTE31,
154 ATTRIBUTE32,
155 ATTRIBUTE33,
156 ATTRIBUTE34,
157 ATTRIBUTE35,
158 ATTRIBUTE36,
159 ATTRIBUTE37,
160 ATTRIBUTE38,
161 ATTRIBUTE39,
162 ATTRIBUTE40,
163 ATTRIBUTE41,
164 ATTRIBUTE42,
165 ATTRIBUTE43,
166 ATTRIBUTE44,
167 ATTRIBUTE45,
168 ATTRIBUTE46,
169 ATTRIBUTE47,
170 ATTRIBUTE48,
171 ATTRIBUTE49,
172 ATTRIBUTE50,
173 ATTRIBUTE51,
174 ATTRIBUTE52,
175 ATTRIBUTE53,
176 ATTRIBUTE54,
177 ATTRIBUTE55,
178 ATTRIBUTE56,
179 ATTRIBUTE57,
180 ATTRIBUTE58,
181 ATTRIBUTE59,
182 ATTRIBUTE60,
183 ATTRIBUTE61,
184 ATTRIBUTE62,
185 ATTRIBUTE63,
186 ATTRIBUTE64,
187 ATTRIBUTE65,
188 ATTRIBUTE66,
189 ATTRIBUTE67,
190 ATTRIBUTE68,
191 ATTRIBUTE69,
192 ATTRIBUTE70,
193 ATTRIBUTE71,
194 ATTRIBUTE72,
195 ATTRIBUTE73,
196 ATTRIBUTE74,
197 ATTRIBUTE75,
198 ATTRIBUTE76,
199 ATTRIBUTE77,
200 ATTRIBUTE78,
201 ATTRIBUTE79,
202 ATTRIBUTE80,
203 ATTRIBUTE81,
204 ATTRIBUTE82,
205 ATTRIBUTE83,
206 ATTRIBUTE84,
207 ATTRIBUTE85,
208 ATTRIBUTE86,
209 ATTRIBUTE87,
210 ATTRIBUTE88,
211 ATTRIBUTE89,
212 ATTRIBUTE90,
213 ATTRIBUTE91,
214 ATTRIBUTE92,
215 ATTRIBUTE93,
216 ATTRIBUTE94,
217 ATTRIBUTE95,
218 ATTRIBUTE96,
219 ATTRIBUTE97,
220 ATTRIBUTE98,
221 ATTRIBUTE99,
222 ATTRIBUTE100,
223 COMM_LINES_API_ID,
224 CONC_BATCH_ID,
225 PROCESS_BATCH_ID,
226 SALESREP_NUMBER,
227 ROLLUP_DATE,
228 SOURCE_DOC_ID,
229 SOURCE_DOC_TYPE,
230 CREATED_BY,
231 CREATION_DATE,
232 LAST_UPDATED_BY,
233 LAST_UPDATE_DATE,
234 LAST_UPDATE_LOGIN,
235 TRANSACTION_CURRENCY_CODE,
236 EXCHANGE_RATE,
237 ACCTD_TRANSACTION_AMOUNT,
238 TRX_ID,
239 TRX_LINE_ID,
240 TRX_SALES_LINE_ID,
241 QUANTITY,
242 SOURCE_TRX_NUMBER,
243 DISCOUNT_PERCENTAGE,
244 MARGIN_PERCENTAGE,
245 SOURCE_TRX_ID,
246 SOURCE_TRX_LINE_ID,
247 SOURCE_TRX_SALES_LINE_ID,
248 NEGATED_FLAG,
249 CUSTOMER_ID,
250 INVENTORY_ITEM_ID,
251 ORDER_NUMBER,
252 BOOKED_DATE,
253 INVOICE_NUMBER,
254 INVOICE_DATE,
255 ADJUST_DATE,
256 ADJUSTED_BY,
257 REVENUE_TYPE,
258 ADJUST_ROLLUP_FLAG,
259 ADJUST_COMMENTS,
260 ADJUST_STATUS,
261 LINE_NUMBER,
262 BILL_TO_ADDRESS_ID,
263 SHIP_TO_ADDRESS_ID,
264 BILL_TO_CONTACT_ID,
265 SHIP_TO_CONTACT_ID,
266 ADJ_COMM_LINES_API_ID,
267 PRE_DEFINED_RC_FLAG,
268 ROLLUP_FLAG,
269 FORECAST_ID,
270 UPSIDE_QUANTITY,
271 UPSIDE_AMOUNT,
272 UOM_CODE,
273 REASON_CODE,
274 TYPE,
275 PRE_PROCESSED_CODE,
276 QUOTA_ID,
277 SRP_PLAN_ASSIGN_ID,
278 ROLE_ID,
279 COMP_GROUP_ID,
280 COMMISSION_AMOUNT,
281 EMPLOYEE_NUMBER,
282 REVERSAL_FLAG,
283 REVERSAL_HEADER_ID,
284 SALES_CHANNEL,
285 OBJECT_VERSION_NUMBER,
286 SPLIT_PCT,
287 SPLIT_STATUS,
288 ORG_ID)
289 SELECT ILV.SALESREP_ID,
290 CCLA.PROCESSED_DATE,
291 CCLA.PROCESSED_PERIOD_ID,
292 --CCLA.TRANSACTION_AMOUNT,
293 ILV.net_trx_amount,
294 CCLA.TRX_TYPE,
295 CCLA.REVENUE_CLASS_ID,
296 'UNLOADED',
297 CCLA.ATTRIBUTE_CATEGORY,
298 CCLA.ATTRIBUTE1,
299 CCLA.ATTRIBUTE2,
300 CCLA.ATTRIBUTE3,
301 CCLA.ATTRIBUTE4,
302 CCLA.ATTRIBUTE5,
303 CCLA.ATTRIBUTE6,
304 CCLA.ATTRIBUTE7,
305 CCLA.ATTRIBUTE8,
306 CCLA.ATTRIBUTE9,
307 CCLA.ATTRIBUTE10,
308 CCLA.ATTRIBUTE11,
309 CCLA.ATTRIBUTE12,
310 CCLA.ATTRIBUTE13,
311 CCLA.ATTRIBUTE14,
312 CCLA.ATTRIBUTE15,
313 CCLA.ATTRIBUTE16,
314 CCLA.ATTRIBUTE17,
315 CCLA.ATTRIBUTE18,
316 CCLA.ATTRIBUTE19,
317 CCLA.ATTRIBUTE20,
318 CCLA.ATTRIBUTE21,
319 CCLA.ATTRIBUTE22,
320 CCLA.ATTRIBUTE23,
321 CCLA.ATTRIBUTE24,
322 CCLA.ATTRIBUTE25,
323 CCLA.ATTRIBUTE26,
324 CCLA.ATTRIBUTE27,
325 CCLA.ATTRIBUTE28,
326 CCLA.ATTRIBUTE29,
327 CCLA.ATTRIBUTE30,
328 CCLA.ATTRIBUTE31,
329 CCLA.ATTRIBUTE32,
330 CCLA.ATTRIBUTE33,
331 CCLA.ATTRIBUTE34,
332 CCLA.ATTRIBUTE35,
333 CCLA.ATTRIBUTE36,
334 CCLA.ATTRIBUTE37,
335 CCLA.ATTRIBUTE38,
336 CCLA.ATTRIBUTE39,
337 CCLA.ATTRIBUTE40,
338 CCLA.ATTRIBUTE41,
339 CCLA.ATTRIBUTE42,
340 CCLA.ATTRIBUTE43,
341 CCLA.ATTRIBUTE44,
342 CCLA.ATTRIBUTE45,
343 CCLA.ATTRIBUTE46,
344 CCLA.ATTRIBUTE47,
345 CCLA.ATTRIBUTE48,
346 CCLA.ATTRIBUTE49,
347 CCLA.ATTRIBUTE50,
348 CCLA.ATTRIBUTE51,
349 CCLA.ATTRIBUTE52,
350 CCLA.ATTRIBUTE53,
351 CCLA.ATTRIBUTE54,
352 CCLA.ATTRIBUTE55,
353 CCLA.ATTRIBUTE56,
354 CCLA.ATTRIBUTE57,
355 CCLA.ATTRIBUTE58,
356 CCLA.ATTRIBUTE59,
357 CCLA.ATTRIBUTE60,
358 CCLA.ATTRIBUTE61,
359 CCLA.ATTRIBUTE62,
360 CCLA.ATTRIBUTE63,
361 CCLA.ATTRIBUTE64,
362 CCLA.ATTRIBUTE65,
363 CCLA.ATTRIBUTE66,
364 CCLA.ATTRIBUTE67,
365 CCLA.ATTRIBUTE68,
366 CCLA.ATTRIBUTE69,
367 CCLA.ATTRIBUTE70,
368 CCLA.ATTRIBUTE71,
369 CCLA.ATTRIBUTE72,
370 CCLA.ATTRIBUTE73,
371 CCLA.ATTRIBUTE74,
372 CCLA.ATTRIBUTE75,
373 CCLA.ATTRIBUTE76,
374 CCLA.ATTRIBUTE77,
375 CCLA.ATTRIBUTE78,
376 CCLA.ATTRIBUTE79,
377 CCLA.ATTRIBUTE80,
378 CCLA.ATTRIBUTE81,
379 CCLA.ATTRIBUTE82,
380 CCLA.ATTRIBUTE83,
381 CCLA.ATTRIBUTE84,
382 CCLA.ATTRIBUTE85,
383 CCLA.ATTRIBUTE86,
384 CCLA.ATTRIBUTE87,
385 CCLA.ATTRIBUTE88,
386 CCLA.ATTRIBUTE89,
387 CCLA.ATTRIBUTE90,
388 CCLA.ATTRIBUTE91,
389 CCLA.ATTRIBUTE92,
390 CCLA.ATTRIBUTE93,
391 CCLA.ATTRIBUTE94,
392 CCLA.ATTRIBUTE95,
393 CCLA.ATTRIBUTE96,
394 CCLA.ATTRIBUTE97,
395 CCLA.ATTRIBUTE98,
396 CCLA.ATTRIBUTE99,
397 CCLA.ATTRIBUTE100,
398 cn_comm_lines_api_s.NEXTVAL,
399 CCLA.CONC_BATCH_ID,
400 CCLA.PROCESS_BATCH_ID,
401 NULL,
402 CCLA.ROLLUP_DATE,
403 CCLA.SOURCE_DOC_ID,
404 CCLA.SOURCE_DOC_TYPE,
405 fnd_global.user_id,
406 Sysdate,
407 fnd_global.user_id,
408 Sysdate,
409 fnd_global.login_id,
410 CCLA.TRANSACTION_CURRENCY_CODE,
411 CCLA.EXCHANGE_RATE,
412 CCLA.ACCTD_TRANSACTION_AMOUNT,
413 CCLA.TRX_ID,
414 CCLA.TRX_LINE_ID,
415 CCLA.TRX_SALES_LINE_ID,
416 CCLA.QUANTITY,
417 CCLA.SOURCE_TRX_NUMBER,
418 CCLA.DISCOUNT_PERCENTAGE,
419 CCLA.MARGIN_PERCENTAGE,
420 CCLA.SOURCE_TRX_ID,
421 CCLA.SOURCE_TRX_LINE_ID,
422 CCLA.SOURCE_TRX_SALES_LINE_ID,
423 CCLA.NEGATED_FLAG,
424 CCLA.CUSTOMER_ID,
425 CCLA.INVENTORY_ITEM_ID,
426 CCLA.ORDER_NUMBER,
427 CCLA.BOOKED_DATE,
428 CCLA.INVOICE_NUMBER,
429 CCLA.INVOICE_DATE,
430 SYSDATE,
431 l_adjusted_by,
432 CCLA.REVENUE_TYPE,
433 CCLA.ADJUST_ROLLUP_FLAG,
434 'Created by TAE',
435 NVL(CCLA.ADJUST_STATUS,'NEW'),
436 CCLA.LINE_NUMBER,
437 CCLA.BILL_TO_ADDRESS_ID,
438 CCLA.SHIP_TO_ADDRESS_ID,
439 CCLA.BILL_TO_CONTACT_ID,
440 CCLA.SHIP_TO_CONTACT_ID,
441 CCLA.COMM_LINES_API_ID,
442 CCLA.PRE_DEFINED_RC_FLAG,
443 CCLA.ROLLUP_FLAG,
444 CCLA.FORECAST_ID,
445 CCLA.UPSIDE_QUANTITY,
446 CCLA.UPSIDE_AMOUNT,
447 CCLA.UOM_CODE,
448 CCLA.REASON_CODE,
449 CCLA.TYPE,
450 CCLA.PRE_PROCESSED_CODE,
451 CCLA.QUOTA_ID,
452 CCLA.SRP_PLAN_ASSIGN_ID,
453 --CR.ROLE_ID,
454 --JR.ROLE_ID, -- Added for 4438001
455 ILV.role_id,
456 CCLA.COMP_GROUP_ID,
457 CCLA.COMMISSION_AMOUNT,
458 ILV.EMPLOYEE_NUMBER,
459 CCLA.REVERSAL_FLAG,
460 CCLA.REVERSAL_HEADER_ID,
461 CCLA.SALES_CHANNEL,
462 CCLA.OBJECT_VERSION_NUMBER,
463 CCLA.SPLIT_PCT,
464 CCLA.SPLIT_STATUS,
465 ILV.ORG_ID
466 FROM (
467 -- Starting of ILV
468 SELECT org_id,
469 comm_lines_api_id,
470 resource_id,
471 role_id,
472 salesrep_id,
473 employee_number,
474 split_trx_amout - LAG(split_trx_amout, 1, 0)
475 OVER (PARTITION BY comm_lines_api_id ORDER BY rn) net_trx_amount
476 FROM (
477 SELECT a.org_id,
478 a.comm_lines_api_id,
479 b.resource_id,
480 JR.role_id,
481 CS.salesrep_id,
482 CS.employee_number,
483 ROUND(a.transaction_amount *
484 CUME_DIST() OVER (PARTITION BY a.comm_lines_api_id
485 ORDER BY b.resource_id), 2) split_trx_amout,
486 ROW_NUMBER() OVER (PARTITION BY a.comm_lines_api_id
487 ORDER BY b.resource_id) rn
488 FROM cn_comm_lines_api a,
489 JTF_TAE_1001_SC_WINNERS b,
490 jtf_rs_roles_vl JR,
491 cn_salesreps CS
492 WHERE a.comm_lines_api_id = b.trans_object_id
493 AND NVL(a.org_id, -777) = NVL(b.org_id, -777)
494 AND b.org_id=CS.org_id
495 AND JR.role_code = b.role
496 AND CS.resource_id = b.resource_id AND
497 a.org_id=p_org_id) result) ILV,
498 -- End of the ILV
499 cn_comm_lines_api CCLA
500 WHERE ILV.comm_lines_api_id = CCLA.comm_lines_api_id and
501 ILV.ORG_ID=CCLA.ORG_ID AND
502 CCLA.ORG_ID=p_org_id;
503
504 --dbms_output.put_line('After Insert Statement');
505 --dbms_output.put_line('SQL%ROWCOUNT :'||SQL%ROWCOUNT);
506
507 OPEN trx_records;
508 FETCH trx_records
509 BULK COLLECT INTO l_api_id, l_org_id, l_role, l_resource_id limit 1000;
510 CLOSE trx_records;
511
512 IF l_api_id.count > 0 THEN
513
514 FORALL j IN 1..l_api_id.COUNT
515
516 UPDATE cn_comm_lines_api api
517 SET load_status = 'OBSOLETE',
518 adjust_status = 'FROZEN',
519 adjust_date = sysdate,
520 adjusted_by = l_adjusted_by,
521 adjust_comments = 'Negated for TAE'
522 WHERE comm_lines_api_id = l_api_id(j)
523 AND NVL(org_id, -777) = NVL(l_org_id(j), -777);
524
525 END IF;
526
527 --dbms_output.put_line('After the UPDATE statement ');
528
529 EXCEPTION
530 WHEN FND_API.G_EXC_ERROR THEN
531 ROLLBACK TO process_trx_records;
532 x_return_status := FND_API.G_RET_STS_ERROR ;
533 FND_MSG_PUB.Count_And_Get(
534 p_count => x_msg_count ,
535 p_data => x_msg_data ,
536 p_encoded => FND_API.G_FALSE);
537
538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
539 ROLLBACK TO process_trx_records;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
541 FND_MSG_PUB.Count_And_Get(
542 p_count => x_msg_count ,
543 p_data => x_msg_data ,
544 p_encoded => FND_API.G_FALSE);
545
546 WHEN OTHERS THEN
547 ROLLBACK TO process_trx_records;
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
549 IF FND_MSG_PUB.Check_Msg_Level(
550 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
551 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
552 END IF;
553 FND_MSG_PUB.Count_And_Get(
554 p_count => x_msg_count ,
555 p_data => x_msg_data ,
556 p_encoded => FND_API.G_FALSE);
557
558 END Process_Trx_Records;
559
560 END CN_PROCESS_TAE_TRX_PUB;