[Home] [Help]
PACKAGE BODY: APPS.FEM_PARTY_PROFITABILITY_PUB
Source
1 package body FEM_PARTY_PROFITABILITY_PUB AS
2 /* $Header: femprfSB.pls 120.0 2005/06/06 20:46:56 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_PARTY_PROFITABILITY_PUB';
5
6 procedure do_create_profitability(
7 p_profitability_rec IN PROFITABILITY_REC_TYPE,
8 x_party_id OUT NOCOPY NUMBER,
9 x_return_status IN OUT NOCOPY VARCHAR2
10 );
11
12
13 procedure do_update_profitability(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
16 p_commit IN VARCHAR2:= FND_API.G_TRUE,
17 p_profitability_rec IN PROFITABILITY_REC_TYPE,
18 p_last_update_date IN OUT NOCOPY DATE,
19 x_return_status IN OUT NOCOPY VARCHAR2,
20 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL
21 );
22
23 procedure validate_profitability(
24 p_profitability_rec IN FEM_PARTY_profitability_pub.profitability_rec_type,
25 create_update_flag IN VARCHAR2,
26 x_return_status IN OUT NOCOPY VARCHAR2
27 );
28
29 procedure get_current_profitability(
30 p_party_id IN NUMBER,
31 x_profitability_rec OUT NOCOPY FEM_PARTY_profitability_pub.profitability_rec_type
32 );
33
34
35
36
37
38 /*===========================================================================+
39 | PROCEDURE
40 | create_profitability.
41 |
42 | DESCRIPTION
43 | Creates profitability.
44 |
45 | SCOPE - PUBLIC
46 |
47 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
48 |
49 | ARGUMENTS : IN:
50 | p_api_version
51 | p_init_msg_list
52 | p_commit
53 | p_profitability_rec
54 | p_validation_level
55 | OUT:
56 | x_return_status
57 | x_msg_count
58 | x_msg_data
59 | x_party_id
60 | IN/ OUT:
61 |
62 | RETURNS : NONE
63 |
64 | NOTES
65 |
66 | MODIFICATION HISTORY
67 | Steven Wasserman 27-DEC-00 Created
68 |
69 +===========================================================================*/
70
71
72 procedure create_profitability (
73 p_api_version IN NUMBER :=1.0,
74 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
75 p_commit IN VARCHAR2:= FND_API.G_TRUE,
76 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
77 p_profitability_rec IN PROFITABILITY_REC_TYPE,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_count OUT NOCOPY NUMBER,
80 x_msg_data OUT NOCOPY VARCHAR2,
81 x_party_id OUT NOCOPY NUMBER
82 ) IS
83 l_api_name CONSTANT VARCHAR2(30) := 'create_profitability';
84 l_api_version CONSTANT NUMBER := 1.0;
85
86
87 BEGIN
88 --Standard start of API savepoint
89 SAVEPOINT create_profitability_pub;
90
91 --Standard call to check for call compatibility.
92 IF NOT FND_API.Compatible_API_Call(
93 l_api_version,
94 p_api_version,
95 l_api_name,
96 G_PKG_NAME)
97 THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100
101 --Initialize message list if p_init_msg_list is set to TRUE.
102 IF FND_API.to_Boolean(p_init_msg_list) THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 --Initialize API return status to success.
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108
109 --Call to User-Hook pre Processing Procedure
110
111 /* IF (fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y') THEN
112 hz_profitability_crmhk.create_profitability_pre(
113 l_profitability_rec,
114 x_return_status,
115 x_msg_count,
116 x_msg_data);
117
118 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
119 RAISE FND_API.G_EXC_ERROR;
120 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
121 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
122 FND_MESSAGE.SET_TOKEN('PROCEDURE',
123 'HZ_PROFITABILITY_CRMHK.CREATE_PROFITABILITY_PRE');
124 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
125 FND_MSG_PUB.ADD;
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128
129 END IF;
130 */
131 --Call to business logic.
132 do_create_profitability(
133 p_profitability_rec,
134 x_party_id,
135 x_return_status);
136
137 --Call to User-Hook post Processing Procedure
138 /*
139
140 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' THEN
141 hz_profitability_crmhk.create_profitability_post(
142 l_profitability_rec,
143 x_return_status,
144 x_msg_count,
145 x_msg_data);
146
147 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
148 RAISE FND_API.G_EXC_ERROR;
149 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
150 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
151 FND_MESSAGE.SET_TOKEN('PROCEDURE',
152 'HZ_PROFITABILITY_CRMHK.CREATE_PROFITABILITY_POST');
153 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
154 FND_MSG_PUB.ADD;
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 END IF;
157
158 END IF;
159 */
160
161 --Standard check of p_commit.
162 IF FND_API.to_Boolean(p_commit) THEN
163 commit;
164 END IF;
165
166 --Standard call to get message count and if count is 1, get message info.
167 FND_MSG_PUB.Count_And_Get(
168 p_encoded => FND_API.G_FALSE,
169 p_count => x_msg_count,
170 p_data => x_msg_data);
171
172 EXCEPTION
173 WHEN FND_API.G_EXC_ERROR THEN
174 ROLLBACK TO create_location_pub;
175 x_return_status := FND_API.G_RET_STS_ERROR;
176 FND_MSG_PUB.Count_And_Get(
177 p_encoded => FND_API.G_FALSE,
178 p_count => x_msg_count,
179 p_data => x_msg_data);
180
181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
182 ROLLBACK TO create_profitability_pub;
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184 FND_MSG_PUB.Count_And_Get(
185 p_encoded => FND_API.G_FALSE,
186 p_count => x_msg_count,
187 p_data => x_msg_data);
188
189 WHEN OTHERS THEN
190 ROLLBACK TO create_profitability_pub;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192
193 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
194 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
195 FND_MSG_PUB.ADD;
196
197 FND_MSG_PUB.Count_And_Get(
198 p_encoded => FND_API.G_FALSE,
199 p_count => x_msg_count,
200 p_data => x_msg_data);
201
202 END create_profitability;
203
204 /*===========================================================================+
205 | PROCEDURE
206 | update_profitability
207 |
208 | DESCRIPTION
209 | Updates profitability.
210 |
211 | SCOPE - PUBLIC
212 |
213 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
214 |
215 | ARGUMENTS : IN:
216 | p_api_version
217 | p_init_msg_list
218 | p_commit
219 | p_profitability_rec
220 | p_validation_level
221 | OUT:
222 | x_return_status
223 | x_msg_count
224 | x_msg_data
225 | IN/ OUT:
226 | p_last_update_date
227 |
228 | RETURNS : NONE
229 |
230 | NOTES
231 |
232 | MODIFICATION HISTORY
233 | Steven Wasserman 27-DEC-00 Created
234 |
235 +===========================================================================*/
236
237 procedure update_profitability (
238 p_api_version IN NUMBER,
239 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
240 p_commit IN VARCHAR2:=FND_API.G_TRUE,
241 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
242 p_profitability_rec IN PROFITABILITY_REC_TYPE,
243 p_last_update_date IN OUT NOCOPY DATE,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2
247 ) IS
248 l_api_name CONSTANT VARCHAR2(30) := 'update_profitability';
249 l_api_version CONSTANT NUMBER := 1.0;
250
251
252 BEGIN
253 --Standard start of API savepoint
254 SAVEPOINT update_profitability_pub;
255
256 --Standard call to check for call compatibility.
257 IF NOT FND_API.Compatible_API_Call(
258 l_api_version,
259 p_api_version,
260 l_api_name,
261 G_PKG_NAME)
262 THEN
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265
266 --Initialize message list if p_init_msg_list is set to TRUE.
267 IF FND_API.to_Boolean(p_init_msg_list) THEN
268 FND_MSG_PUB.initialize;
269 END IF;
270
271 --Initialize API return status to success.
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273
274 --------------------------------------------------------------
275 -- Since we aren't doing any pre processing at this time,
276 -- there is no need to get the current record or perform
277 -- any of the other pre-processing steps
278 --------------------------------------------------------------
279 /*
280 --Get the old record.
281 get_current_profitability(
282 l_profitability_rec.party_id,
283 l_old_profitability_rec);
284
285
286 --Call to User-Hook Pre Processing Procedure
287
288 IF (fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y') THEN
289 hz_profitability_crmhk.update_profitability_pre(l_profitability_rec,
290 l_old_profitability_rec,
291 x_return_status,
292 x_msg_count,
293 x_msg_data);
294
295 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
296 RAISE FND_API.G_EXC_ERROR;
297 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
298 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
299 FND_MESSAGE.SET_TOKEN('PROCEDURE',
300 'HZ_PROFITABILITY_CRMHK.UPDATE_PROFITABILITY_PRE');
301 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
302 FND_MSG_PUB.ADD;
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305
306 END IF;
307 */
308 -----------------------------------------------------------------
309 -- End of the pre-processing steps
310 -----------------------------------------------------------------
311 --Call to business logic.
312
313 do_update_profitability(
314 p_api_version,
315 p_init_msg_list,
316 p_commit,
317 p_profitability_rec,
318 p_last_update_date,
319 x_return_status,
320 p_validation_level);
321
322 /*
323
324 --------------------------------------------------------------
325 -- Since we aren't doing any Post processing at this time,
326 -- there is no need to get the current record or perform
327 -- any of the other post-processing steps
328 --------------------------------------------------------------
329 --Call to User-Hook Pre Processing Procedure
330 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' THEN
331 hz_location_crmhk.update_profitability_post(l_profitability_rec,
332 x_return_status,
333 x_msg_count,
334 x_msg_data);
335
336 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
337 RAISE FND_API.G_EXC_ERROR;
338 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
339 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
340 FND_MESSAGE.SET_TOKEN('PROCEDURE',
341 'HZ_PROFITABILITY_CRMHK.UPDATE_PROFITABILITY_POST');
342 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
343 FND_MSG_PUB.ADD;
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346
347 END IF;
348
349 -----------------------------------------------------------------
350 -- End of the post-processing steps
351 -----------------------------------------------------------------
352 */
353
354 --Standard check of p_commit.
355
356 IF FND_API.to_Boolean(p_commit) THEN
357 commit;
358 END IF;
359
360 --Standard call to get message count and if count is 1, get message info.
361 FND_MSG_PUB.Count_And_Get(
362 p_encoded => FND_API.G_FALSE,
363 p_count => x_msg_count,
364 p_data => x_msg_data);
365
366 EXCEPTION
367 WHEN FND_API.G_EXC_ERROR THEN
368 ROLLBACK TO update_profitability_pub;
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 FND_MSG_PUB.Count_And_Get(
371 p_encoded => FND_API.G_FALSE,
372 p_count => x_msg_count,
373 p_data => x_msg_data);
374
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376 ROLLBACK TO update_profitability_pub;
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 FND_MSG_PUB.Count_And_Get(
379 p_encoded => FND_API.G_FALSE,
380 p_count => x_msg_count,
381 p_data => x_msg_data);
382
383 WHEN OTHERS THEN
384 ROLLBACK TO update_profitability_pub;
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386
387 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
388 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
389 FND_MSG_PUB.ADD;
390
391 FND_MSG_PUB.Count_And_Get(
392 p_encoded => FND_API.G_FALSE,
393 p_count => x_msg_count,
394 p_data => x_msg_data);
395
399 | PROCEDURE
396 END update_profitability;
397
398 /*===========================================================================+
400 | get_current_profitability
401 |
402 | DESCRIPTION
403 | Gets current record.
404 |
405 | SCOPE - PRIVATE
406 |
407 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
408 |
409 | ARGUMENTS : IN:
410 | p_party_id
411 | OUT:
412 | x_profitability_rec
413 | IN/ OUT:
414 |
415 | RETURNS : NONE
416 |
417 | NOTES
418 |
419 | MODIFICATION HISTORY
420 | Steven Wasserman 28-DEC-00 Created
421 |
422 +===========================================================================*/
423
424 procedure get_current_profitability(
425 p_party_id IN NUMBER,
426 x_profitability_rec OUT NOCOPY FEM_PARTY_profitability_pub.profitability_rec_type
427 ) IS
428 LAST_UPDATE_DATE DATE;
429 LAST_UPDATED_BY NUMBER;
430 CREATION_DATE DATE;
431 CREATED_BY NUMBER;
432 LAST_UPDATE_LOGIN NUMBER;
433
434 BEGIN
435 null;
436 /*
437 SELECT
438 PARTY_ID,
439 PROFIT,
440 PROFIT_PCT,
441 RELATIONSHIP_EXPENSE,
442 TOTAL_EQUITY,
443 TOTAL_GROSS_CONTRIB,
444 TOTAL_ROE,
445 TOTAL_TRANSACTIONS,
446 CONTRIB_AFTER_CPTL_CHG,
447 PARTNER_VALUE_INDEX,
448 CREATED_BY,
449 CREATION_DATE,
450 LAST_UPDATED_BY,
451 LAST_UPDATE_DATE,
452 LAST_UPDATE_LOGIN
453 INTO
454 x_profitability_rec.PARTY_ID,
455 x_profitability_rec.PROFIT,
456 x_profitability_rec.PROFIT_PCT,
457 x_profitability_rec.RELATIONSHIP_EXPENSE,
458 x_profitability_rec.TOTAL_EQUITY,
459 x_profitability_rec.TOTAL_GROSS_CONTRIB,
460 x_profitability_rec.TOTAL_ROE,
461 x_profitability_rec.TOTAL_TRANSACTIONS,
462 x_profitability_rec.CONTRIB_AFTER_CPTL_CHG,
463 x_profitability_rec.PARTNER_VALUE_INDEX,
464 CREATED_BY,
465 CREATION_DATE,
466 LAST_UPDATED_BY,
467 LAST_UPDATE_DATE,
468 LAST_UPDATE_LOGIN
469 FROM FEM_PARTY_profitability
470 WHERE party_id = p_party_id;
471 */
472 END get_current_profitability;
473
474 /*===========================================================================+
475 | PROCEDURE
476 | do_create_profitability
477 |
478 | DESCRIPTION
479 | Creates profitability.
480 |
481 | SCOPE - PRIVATE
482 |
483 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
484 |
485 | ARGUMENTS : IN:
486 | OUT:
487 | x_party_id
488 | IN/ OUT:
489 | p_profitability_rec
490 | x_return_status
491 |
492 | RETURNS : NONE
493 |
494 | NOTES
495 |
496 | MODIFICATION HISTORY
497 | Kielley Fon-Ndikum 15-FEB-01 Add new columns and drop 2.
498 | Steven Wasserman 27-DEC-00 Created
499 | |
500 +===========================================================================*/
501
502 procedure do_create_profitability(
503 p_profitability_rec IN PROFITABILITY_REC_TYPE,
504 x_party_id OUT NOCOPY NUMBER,
505 x_return_status IN OUT NOCOPY VARCHAR2
506 ) IS
507 l_party_id NUMBER := p_profitability_rec.party_id;
508 l_rowid ROWID := NULL;
509 BEGIN
510
511 validate_profitability(p_profitability_rec, 'C', x_return_status);
512
513 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
514 RAISE FND_API.G_EXC_ERROR;
515 END IF;
516
517 --Call table-handler.
518
519 FEM_PARTY_PROFITABILITY_PKG.INSERT_ROW(
520 x_Rowid => l_Rowid,
521 x_PARTY_ID => p_profitability_rec.PARTY_ID,
522 X_LAST_UPDATE_DATE => p_profitability_rec.LAST_UPDATE_DATE,
523 X_LAST_UPDATED_BY => p_profitability_rec.LAST_UPDATED_BY,
524 X_CREATION_DATE => p_profitability_rec.CREATION_DATE,
525 X_CREATED_BY => p_profitability_rec.CREATED_BY,
526 X_LAST_UPDATE_LOGIN => p_profitability_rec.LAST_UPDATE_LOGIN,
527 x_PROFIT => p_profitability_rec.PROFIT,
528 x_PROFIT_PCT => p_profitability_rec.PROFIT_PCT,
529 x_RELATIONSHIP_EXPENSE => p_profitability_rec.RELATIONSHIP_EXPENSE,
530 x_TOTAL_EQUITY => p_profitability_rec.TOTAL_EQUITY,
531 x_TOTAL_GROSS_CONTRIB => p_profitability_rec.TOTAL_GROSS_CONTRIB,
532 x_TOTAL_ROE => p_profitability_rec.TOTAL_ROE,
533 x_CONTRIB_AFTER_CPTL_CHG => p_profitability_rec.CONTRIB_AFTER_CPTL_CHG,
534 x_PARTNER_VALUE_INDEX => p_profitability_rec.PARTNER_VALUE_INDEX,
535 x_ISO_CURRENCY_CD => p_profitability_rec.ISO_CURRENCY_CD,
536 x_REVENUE1 => p_profitability_rec.REVENUE1,
537 x_REVENUE2 => p_profitability_rec.REVENUE2,
541 x_REVENUE_TOTAL => p_profitability_rec.REVENUE_TOTAL,
538 x_REVENUE3 => p_profitability_rec.REVENUE3,
539 x_REVENUE4 => p_profitability_rec.REVENUE4,
540 x_REVENUE5 => p_profitability_rec.REVENUE5,
542 x_EXPENSE1 => p_profitability_rec.EXPENSE1,
543 x_EXPENSE2 => p_profitability_rec.EXPENSE2,
544 x_EXPENSE3 => p_profitability_rec.EXPENSE3,
545 x_EXPENSE4 => p_profitability_rec.EXPENSE4,
546 x_EXPENSE5 => p_profitability_rec.EXPENSE5,
547 x_EXPENSE_TOTAL => p_profitability_rec.EXPENSE_TOTAL,
548 x_PROFIT1 => p_profitability_rec.PROFIT1,
549 x_PROFIT2 => p_profitability_rec.PROFIT2,
550 x_PROFIT3 => p_profitability_rec.PROFIT3,
551 x_PROFIT4 => p_profitability_rec.PROFIT4,
552 x_PROFIT5 => p_profitability_rec.PROFIT5,
553 x_PROFIT_TOTAL => p_profitability_rec.PROFIT_TOTAL,
554 x_CACC1 => p_profitability_rec.CACC1,
555 x_CACC2 => p_profitability_rec.CACC2,
556 x_CACC3 => p_profitability_rec.CACC3,
557 x_CACC4 => p_profitability_rec.CACC4,
558 x_CACC5 => p_profitability_rec.CACC5,
559 x_CACC_TOTAL => p_profitability_rec.CACC_TOTAL,
560 x_BALANCE1 => p_profitability_rec.BALANCE1,
561 x_BALANCE2 => p_profitability_rec.BALANCE2,
562 x_BALANCE3 => p_profitability_rec.BALANCE3,
563 x_BALANCE4 => p_profitability_rec.BALANCE4,
564 x_BALANCE5 => p_profitability_rec.BALANCE5,
565 x_ACCOUNTS1 => p_profitability_rec.ACCOUNTS1,
566 x_ACCOUNTS2 => p_profitability_rec.ACCOUNTS2,
567 x_ACCOUNTS3 => p_profitability_rec.ACCOUNTS3,
568 x_ACCOUNTS4 => p_profitability_rec.ACCOUNTS4,
569 x_ACCOUNTS5 => p_profitability_rec.ACCOUNTS5,
570 x_TRANSACTION1 => p_profitability_rec.TRANSACTION1,
571 x_TRANSACTION2 => p_profitability_rec.TRANSACTION2,
572 x_TRANSACTION3 => p_profitability_rec.TRANSACTION3,
573 x_TRANSACTION4 => p_profitability_rec.TRANSACTION4,
574 x_TRANSACTION5 => p_profitability_rec.TRANSACTION5,
575 x_RATIO1 => p_profitability_rec.RATIO1,
576 x_RATIO2 => p_profitability_rec.RATIO2,
577 x_RATIO3 => p_profitability_rec.RATIO3,
578 x_RATIO4 => p_profitability_rec.RATIO4,
579 x_RATIO5 => p_profitability_rec.RATIO5,
580 x_VALUE1 => p_profitability_rec.VALUE1,
581 x_VALUE2 => p_profitability_rec.VALUE2,
582 x_VALUE3 => p_profitability_rec.VALUE3,
583 x_VALUE4 => p_profitability_rec.VALUE4,
584 x_VALUE5 => p_profitability_rec.VALUE5,
585 x_YTD1 => p_profitability_rec.YTD1,
586 x_YTD2 => p_profitability_rec.YTD2,
587 x_YTD3 => p_profitability_rec.YTD3,
588 x_YTD4 => p_profitability_rec.YTD4,
589 x_YTD5 => p_profitability_rec.YTD5,
590 x_LTD1 => p_profitability_rec.LTD1,
591 x_LTD2 => p_profitability_rec.LTD2,
592 x_LTD3 => p_profitability_rec.LTD3,
593 x_LTD4 => p_profitability_rec.LTD4,
594 x_LTD5 => p_profitability_rec.LTD5
595 );
596
597 END do_create_profitability;
598
599 /*===========================================================================+
600 | PROCEDURE
601 | do_update_profitability
602 |
603 | DESCRIPTION
604 | Updates profitability.
605 |
606 | SCOPE - PRIVATE
607 |
608 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
609 |
610 | ARGUMENTS : IN:
611 | OUT:
612 | IN/ OUT:
613 | p_profitability_rec
614 | p_last_update_date
615 | x_return_status
616 |
617 | RETURNS : NONE
618 |
619 | NOTES
620 |
621 | MODIFICATION HISTORY
622 | Kielley Fon-Ndikum 15-FEB-01 Add new columns and drop 2.
623 | Steven Wasserman 27-DEC-00 Created
624 |
625 +===========================================================================*/
626
627 procedure do_update_profitability(
628 p_api_version IN NUMBER,
629 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
630 p_commit IN VARCHAR2:= FND_API.G_TRUE,
631 p_profitability_rec IN PROFITABILITY_REC_TYPE,
632 p_last_update_date IN OUT NOCOPY DATE,
633 x_return_status IN OUT NOCOPY VARCHAR2,
634 p_validation_level NUMBER:= FND_API.G_VALID_LEVEL_FULL
635 ) IS
636 exist_last_update_date DATE; -- last_update_date of existing
637 -- record in FEM_PARTY_PROFITABILITY for
638 -- same party_id
639 l_rowid ROWID;
640 l_key VARCHAR2(2000);
641 l_party_id NUMBER;
642 insert_flag NUMBER:=0; -- identifies that we have upserted
643 -- and don't need to perform any update
644 update_flag NUMBER:=0; -- identifies that we need to update
645 -- because the last_update_date in apps
646 -- is less than the source record from FDM
647
648
649 ----------------------------------------------------------
650 -- Return variables for calling create_profitability
651 -- if record to update does not exist
652 ----------------------------------------------------------
656 -----------------------------------------------------------
653 x_msg_count NUMBER;
654 x_msg_data VARCHAR2(2000);
655 x_party_id NUMBER;
657
658 BEGIN
659
660
661 --Check whether primary key has been passed in.
662 IF p_profitability_rec.party_id IS NULL OR
663 p_profitability_rec.party_id = FND_API.G_MISS_NUM THEN
664
665 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
666 FND_MESSAGE.SET_TOKEN('COLUMN', 'party id');
667 FND_MSG_PUB.ADD;
668 RAISE FND_API.G_EXC_ERROR;
669 END IF;
670
671 -------------------------------------------------------------
672 -- Don't need the check last_update_date logic because null
673 -- last_update_date means that we will insert since existing
674 -- record does not exist
675 --------------------------------------------------------------
676 /*--Check whether last_update_date has been passed in.
677 IF p_last_update_date IS NULL OR
678 p_last_update_date = FND_API.G_MISS_DATE THEN
679
680 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
681 FND_MESSAGE.SET_TOKEN('COLUMN', 'profitability last update date');
682 FND_MSG_PUB.ADD;
683 RAISE FND_API.G_EXC_ERROR;
684 END IF; */
685 ---------------------------------------------------------------
686
687
688 --Check whether record exists matching primary key
689 --If not, then call create_profitability to insert
690 IF p_last_update_date IS NULL THEN
691
692 create_profitability(p_api_version,p_init_msg_list,p_commit,p_validation_level,
693 p_profitability_rec,
694 x_return_status,x_msg_count,x_msg_data,x_party_id);
695 insert_flag := 1;
696 END IF;
697
698 --------------------------------------------------------------
699 -- This section of code only performed if we are truly
700 -- updating a record. If we have "upserted", then we can
701 -- skip this entire section
702 --------------------------------------------------------------
703
704 IF insert_flag <> 1 THEN
705 --Check whether last_update_date of record is < last_update_date from
706 --the FDM database
707 BEGIN
708
709 IF p_last_update_date < p_profitability_rec.last_update_date THEN
710
711 ----------------------------------------------------------------------
712 /*
713 SELECT last_update_date INTO l_last_update_date
714 FROM FEM_PARTY_profitability
715 WHERE party_id = p_profitability_rec.party_id
716 AND to_char(p_last_update_date, 'DD-MON-YYYY HH:MI:SS') =
717 to_char(last_update_date, 'DD-MON-YYYY HH:MI:SS')
718 FOR UPDATE OF party_id NOWAIT;
719
720 EXCEPTION WHEN NO_DATA_FOUND THEN
721 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
722 FND_MESSAGE.SET_TOKEN('TABLE', 'FEM_PARTY_PROFITABILITY');
723 FND_MSG_PUB.ADD;
724 RAISE FND_API.G_EXC_ERROR; */
725 -----------------------------------------------------------------------
726
727 --Call for validations.
728 validate_profitability(p_profitability_rec, 'U', x_return_status);
729
730 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733
734 --Set value for geometry and rowid
735 SELECT rowid INTO l_rowid
736 FROM FEM_PARTY_profitability
737 WHERE party_id = p_profitability_rec.party_id;
738
739 ----------------------------------------------------------------------
740 /*
741 --Pass back last_update_date.
742 p_last_update_date := hz_utility_pub.LAST_UPDATE_DATE; */
743 ----------------------------------------------------------------------
744
745 --Call to table-handler.
746
747 FEM_PARTY_PROFITABILITY_PKG.UPDATE_ROW(
748 x_Rowid => l_Rowid,
749 x_PARTY_ID => p_profitability_rec.PARTY_ID,
750 X_LAST_UPDATE_DATE => p_profitability_rec.last_update_date,
751 X_LAST_UPDATED_BY => p_profitability_rec.LAST_UPDATED_BY,
752 X_CREATION_DATE => p_profitability_rec.CREATION_DATE,
753 X_CREATED_BY => p_profitability_rec.CREATED_BY,
754 X_LAST_UPDATE_LOGIN => p_profitability_rec.LAST_UPDATE_LOGIN,
755 x_PROFIT => p_profitability_rec.PROFIT,
756 x_PROFIT_PCT => p_profitability_rec.PROFIT_PCT,
757 x_RELATIONSHIP_EXPENSE => p_profitability_rec.RELATIONSHIP_EXPENSE,
758 x_TOTAL_EQUITY => p_profitability_rec.TOTAL_EQUITY,
759 x_TOTAL_GROSS_CONTRIB => p_profitability_rec.TOTAL_GROSS_CONTRIB,
760 x_TOTAL_ROE => p_profitability_rec.TOTAL_ROE,
761 x_CONTRIB_AFTER_CPTL_CHG => p_profitability_rec.CONTRIB_AFTER_CPTL_CHG,
762 x_PARTNER_VALUE_INDEX => p_profitability_rec.PARTNER_VALUE_INDEX,
763 x_ISO_CURRENCY_CD => p_profitability_rec.ISO_CURRENCY_CD,
764 x_REVENUE1 => p_profitability_rec.REVENUE1,
765 x_REVENUE2 => p_profitability_rec.REVENUE2,
766 x_REVENUE3 => p_profitability_rec.REVENUE3,
767 x_REVENUE4 => p_profitability_rec.REVENUE4,
768 x_REVENUE5 => p_profitability_rec.REVENUE5,
769 x_REVENUE_TOTAL => p_profitability_rec.REVENUE_TOTAL,
770 x_EXPENSE1 => p_profitability_rec.EXPENSE1,
771 x_EXPENSE2 => p_profitability_rec.EXPENSE2,
775 x_EXPENSE_TOTAL => p_profitability_rec.EXPENSE_TOTAL,
772 x_EXPENSE3 => p_profitability_rec.EXPENSE3,
773 x_EXPENSE4 => p_profitability_rec.EXPENSE4,
774 x_EXPENSE5 => p_profitability_rec.EXPENSE5,
776 x_PROFIT1 => p_profitability_rec.PROFIT1,
777 x_PROFIT2 => p_profitability_rec.PROFIT2,
778 x_PROFIT3 => p_profitability_rec.PROFIT3,
779 x_PROFIT4 => p_profitability_rec.PROFIT4,
780 x_PROFIT5 => p_profitability_rec.PROFIT5,
781 x_PROFIT_TOTAL => p_profitability_rec.PROFIT_TOTAL,
782 x_CACC1 => p_profitability_rec.CACC1,
783 x_CACC2 => p_profitability_rec.CACC2,
784 x_CACC3 => p_profitability_rec.CACC3,
785 x_CACC4 => p_profitability_rec.CACC4,
786 x_CACC5 => p_profitability_rec.CACC5,
787 x_CACC_TOTAL => p_profitability_rec.CACC_TOTAL,
788 x_BALANCE1 => p_profitability_rec.BALANCE1,
789 x_BALANCE2 => p_profitability_rec.BALANCE2,
790 x_BALANCE3 => p_profitability_rec.BALANCE3,
791 x_BALANCE4 => p_profitability_rec.BALANCE4,
792 x_BALANCE5 => p_profitability_rec.BALANCE5,
793 x_ACCOUNTS1 => p_profitability_rec.ACCOUNTS1,
794 x_ACCOUNTS2 => p_profitability_rec.ACCOUNTS2,
795 x_ACCOUNTS3 => p_profitability_rec.ACCOUNTS3,
796 x_ACCOUNTS4 => p_profitability_rec.ACCOUNTS4,
797 x_ACCOUNTS5 => p_profitability_rec.ACCOUNTS5,
798 x_TRANSACTION1 => p_profitability_rec.TRANSACTION1,
799 x_TRANSACTION2 => p_profitability_rec.TRANSACTION2,
800 x_TRANSACTION3 => p_profitability_rec.TRANSACTION3,
801 x_TRANSACTION4 => p_profitability_rec.TRANSACTION4,
802 x_TRANSACTION5 => p_profitability_rec.TRANSACTION5,
803 x_RATIO1 => p_profitability_rec.RATIO1,
804 x_RATIO2 => p_profitability_rec.RATIO2,
805 x_RATIO3 => p_profitability_rec.RATIO3,
806 x_RATIO4 => p_profitability_rec.RATIO4,
807 x_RATIO5 => p_profitability_rec.RATIO5,
808 x_VALUE1 => p_profitability_rec.VALUE1,
809 x_VALUE2 => p_profitability_rec.VALUE2,
810 x_VALUE3 => p_profitability_rec.VALUE3,
811 x_VALUE4 => p_profitability_rec.VALUE4,
812 x_VALUE5 => p_profitability_rec.VALUE5,
813 x_YTD1 => p_profitability_rec.YTD1,
814 x_YTD2 => p_profitability_rec.YTD2,
815 x_YTD3 => p_profitability_rec.YTD3,
816 x_YTD4 => p_profitability_rec.YTD4,
817 x_YTD5 => p_profitability_rec.YTD5,
818 x_LTD1 => p_profitability_rec.LTD1,
819 x_LTD2 => p_profitability_rec.LTD2,
820 x_LTD3 => p_profitability_rec.LTD3,
821 x_LTD4 => p_profitability_rec.LTD4,
822 x_LTD5 => p_profitability_rec.LTD5
823 );
824
825 END IF;
826 END;
827 END IF; -- Insert flag
828
829 END do_update_profitability;
830
831 /*===========================================================================+
832 | PROCEDURE
833 | validate_profitability
834 |
835 | DESCRIPTION
836 | Validates profitability. Checks for:
837 |
838 | SCOPE - PUBLIC
839 |
840 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
841 |
842 | ARGUMENTS : IN:
843 | p_profitability_rec
844 | create_update_flag
845 | OUT:
846 | IN/ OUT:
847 |
848 | RETURNS : NONE
849 |
850 | NOTES
851 |
852 | MODIFICATION HISTORY
853 | Rob Flippo 08-JAN-00 Modified validate_profitability to use
854 | G_MISS_NUM in party_id comparison
855 | Steven Wasserman 27-DEC-00 Created
856 |
857 |
858 +===========================================================================*/
859
860 procedure validate_profitability(
861 p_profitability_rec IN FEM_PARTY_profitability_pub.profitability_rec_type,
862 create_update_flag IN VARCHAR2,
863 x_return_status IN OUT NOCOPY VARCHAR2
864 ) IS
865
866 BEGIN
867
868 --Check for mandatory, but updateable columns
869 IF (create_update_flag = 'C' AND (p_profitability_rec.party_id = FND_API.G_MISS_NUM OR
870 p_profitability_rec.party_id IS NULL)) OR (create_update_flag = 'U' AND
871 p_profitability_rec.party_id IS NULL) THEN
872 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
873 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
874 FND_MSG_PUB.ADD;
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 END IF;
877
878 END validate_profitability;
879
880
881
882 END FEM_PARTY_PROFITABILITY_PUB;