[Home] [Help]
PACKAGE BODY: APPS.CST_UTILITY_PUB
Source
1 PACKAGE BODY CST_Utility_PUB AS
2 /* $Header: CSTUTILB.pls 120.6.12010000.2 2008/10/31 11:05:27 prashkum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_Utility_PUB';
5
6 ----------------------------------------------------------------------------
7 -- PROCEDURE --
8 -- writeLogMessages --
9 -- --
10 -- DESCRIPTION --
11 -- This API loops through the message stack and writes the messages to --
12 -- log file --
13 -- --
14 -- PURPOSE: --
15 -- Oracle Applications Rel 11i.4 --
16 -- --
17 -- --
18 -- HISTORY: --
19 -- 10/12/00 Anitha B Created --
20 ----------------------------------------------------------------------------
21 PROCEDURE writeLogMessages (p_api_version IN NUMBER,
22
23 p_msg_count IN NUMBER,
24 p_msg_data IN VARCHAR2,
25
26 x_return_status OUT NOCOPY VARCHAR2) IS
27
28 l_api_name CONSTANT VARCHAR2(30) := 'writeLogMessages';
29 l_api_version CONSTANT NUMBER := 1.0;
30
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(8000);
33
34 l_stmt_num NUMBER := 0;
35
36 BEGIN
37 -- standard start of API savepoint
38 SAVEPOINT writeLogMessages_PUB;
39
40 -- standard call to check for call compatibility
41 if not fnd_api.compatible_api_call (
42 l_api_version,
43 p_api_version,
44 l_api_name,
45 G_PKG_NAME ) then
46 raise fnd_api.g_exc_unexpected_error;
47 end if;
48
49 -- initialize api return status to success
50 x_return_status := fnd_api.g_ret_sts_success;
51
52 -- assign to local variables
53 l_msg_count := p_msg_count;
54 l_msg_data := p_msg_data;
55
56 /* obtain messages from the message list */
57 l_stmt_num := 20;
58 FND_MSG_PUB.count_and_get(
59 p_encoded => FND_API.g_false,
60 p_count => l_msg_count,
61 p_data => l_msg_data
62 );
63
64 /* write all messages in the concurrent manager log */
65 l_stmt_num := 20;
66 IF(l_msg_count > 0) THEN
67 FOR i in 1 ..l_msg_count
68 LOOP
69 l_msg_data := FND_MSG_PUB.get(i, FND_API.g_false);
70 FND_FILE.PUT_LINE(FND_FILE.LOG, i ||'-'||l_msg_data);
71 END LOOP;
72 END IF;
73
74 EXCEPTION
75 when fnd_api.g_exc_error then
76 x_return_status := fnd_api.g_ret_sts_error;
77 fnd_file.put_line(fnd_file.log,'CST_Utility_PUB.writeLogMessages(' || l_stmt_num || '): ' || x_return_status || substr(SQLERRM,1,200));
78 when fnd_api.g_exc_unexpected_error then
79 x_return_status := fnd_api.g_ret_sts_unexp_error ;
80 fnd_file.put_line(fnd_file.log,'CST_Utility_PUB.writeLogMessages(' || l_stmt_num || '): ' || x_return_status || substr(SQLERRM,1,200));
81 when others then
82 x_return_status := fnd_api.g_ret_sts_unexp_error ;
83 fnd_file.put_line(fnd_file.log,'CST_Utility_PUB.writeLogMessages(' || l_stmt_num || '): ' || x_return_status || substr(SQLERRM,1,200));
84
85 END writeLogMessages;
86
87 ----------------------------------------------------------------------------
88 -- PROCEDURE --
89 -- getTxnCategoryId --
90 -- --
91 -- DESCRIPTION --
92 -- This API loops through the message stack and writes the messages to --
93 -- log file --
94 -- --
95 -- PURPOSE: --
96 -- Oracle Applications Rel 11i.4 --
97 -- --
98 -- --
99 -- HISTORY: --
100 -- 11/03/00 Hemant G Created --
101 ----------------------------------------------------------------------------
102 PROCEDURE getTxnCategoryId (p_api_version IN NUMBER,
103 p_init_msg_list IN VARCHAR2
104 := FND_API.G_FALSE,
105 p_commit IN VARCHAR2
106 := FND_API.G_FALSE,
107 p_validation_level IN NUMBER
108 := FND_API.G_VALID_LEVEL_FULL,
109
110
111 p_txn_id IN NUMBER,
112 p_txn_action_id IN NUMBER,
113 p_txn_source_type_id IN NUMBER,
114 p_txn_source_id IN NUMBER,
115 p_item_id IN NUMBER,
116 p_organization_id IN NUMBER,
117
118 x_category_id OUT NOCOPY NUMBER,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2 ) IS
122
123 l_api_name CONSTANT VARCHAR2(30) := 'getTxnCategoryId';
124 l_api_version CONSTANT NUMBER := 1.0;
125
126 l_item_id NUMBER := 0;
127 l_category_set_id NUMBER := 0;
128 l_category_id NUMBER := 0;
129 l_statement NUMBER := 0;
130
131 BEGIN
132
133 -------------------------------------------------------------------------
134 -- standard start of API savepoint
135 -------------------------------------------------------------------------
136 SAVEPOINT getTxnCategoryId;
137
138 -------------------------------------------------------------------------
139 -- standard call to check for call compatibility
140 -------------------------------------------------------------------------
141 IF NOT fnd_api.compatible_api_call (
142 l_api_version,
143 p_api_version,
144 l_api_name,
145 G_PKG_NAME ) then
146
147 RAISE fnd_api.g_exc_unexpected_error;
148
149 END IF;
150
151 -------------------------------------------------------------------------
152 -- Initialize message list if p_init_msg_list is set to TRUE
153 -------------------------------------------------------------------------
154
155 IF FND_API.to_Boolean(p_init_msg_list) THEN
156 FND_MSG_PUB.initialize;
157 END IF;
158
159
160 -------------------------------------------------------------------------
161 -- initialize api return status to success
162 -------------------------------------------------------------------------
163 x_return_status := fnd_api.g_ret_sts_success;
164
165 -- assign to local variables
166 l_statement := 10;
167 l_item_id := p_item_id;
168
169 IF (p_txn_source_type_id = 5 AND p_txn_action_id IN (1,27,33,34)) THEN
170
171 l_statement := 20;
172
173
174 SELECT MAX(primary_item_id)
175 INTO l_item_id
176 FROM wip_entities we
177 WHERE we.wip_entity_id = p_txn_source_id;
178
179 -----------------------------------------------------------------------
180 -- Primary item id may be NULL for non-standard jobs
181 -- In this situation we should return the category id
182 -- of the component and not the assembly
183 -----------------------------------------------------------------------
184
185 IF l_item_id IS NULL THEN
186
187 l_item_id := p_item_id;
188
189 END IF;
190
191 END IF; -- check for comp txns
192
193 l_statement := 30;
194
195 SELECT category_set_id
196 INTO l_category_set_id
197 FROM mtl_default_category_sets mdcs
198 WHERE functional_area_id = 5;
199
200 -------------------------------------------------------------------------
201 -- If an item is assigned to multiple categries in the default
202 -- category set of costing functional area
203 -- get the max category id.
204 -- For costing functional area's default category set, recommendation
205 -- is to assign item to only one category.
206 -------------------------------------------------------------------------
207
208 l_statement := 40;
209
210 SELECT MAX(category_id)
211 INTO l_category_id
212 FROM mtl_item_categories mic
213 WHERE mic.inventory_item_id = l_item_id
214 AND mic.organization_id = p_organization_id
215 AND mic.category_set_id = l_category_set_id;
216
217 IF l_category_id IS NULL THEN
218 x_category_id := -1;
219 ELSE
220 x_category_id := l_category_id;
221 END IF;
222
223 ---------------------------------------------------------------------------
224 -- Standard check of p_commit
225 ---------------------------------------------------------------------------
226
227 IF FND_API.to_Boolean(p_commit) THEN
228 COMMIT WORK;
229 END IF;
230
231 ---------------------------------------------------------------------------
232 -- Standard Call to get message count and if count = 1, get message info
233 ---------------------------------------------------------------------------
234
235 FND_MSG_PUB.Count_And_Get (
236 p_count => x_msg_count,
237 p_data => x_msg_data );
238
239
240
241 EXCEPTION
242
243 WHEN fnd_api.g_exc_error THEN
244 x_return_status := fnd_api.g_ret_sts_error;
245
246 -- Get message count and data
247 fnd_msg_pub.count_and_get
248 ( p_count => x_msg_count
249 , p_data => x_msg_data
250 );
251 --
252 WHEN fnd_api.g_exc_unexpected_error THEN
253 x_return_status := fnd_api.g_ret_sts_unexp_error ;
254
255 -- Get message count and data
256 fnd_msg_pub.count_and_get
257 ( p_count => x_msg_count
258 , p_data => x_msg_data
259 );
260 --
261 WHEN OTHERS THEN
262 x_return_status := fnd_api.g_ret_sts_unexp_error ;
263 --
264 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
265 THEN
266 fnd_msg_pub.add_exc_msg
267 ( 'CST_Utility_Pub'
268 , 'getTxnCategoryId : Statement - '||to_char(l_statement)
269 );
270
271 END IF;
272
273 -- Get message count and data
274 fnd_msg_pub.count_and_get
275 ( p_count => x_msg_count
276 , p_data => x_msg_data
277 );
278
279 END getTxnCategoryId;
280
281 ----------------------------------------------------------------------------
282 -- PROCEDURE --
283 -- get_Std_CG_Acct_Flag --
284 -- --
285 -- DESCRIPTION --
286 -- This API determines if the standard costing organization follows --
287 -- cost group accounting. If yes, then it has PJM support. If the --
288 -- organization ID provided is not standard costing organization, the --
289 -- API will always return 0 --
290 -- --
291 -- PURPOSE: --
292 -- Oracle Applications Rel 11i.6 --
293 -- PJM support for Standard Costing Organizations --
294 -- --
295 -- --
296 -- HISTORY: --
297 -- 11/03/00 Anitha Dixit Created --
298 ----------------------------------------------------------------------------
299 PROCEDURE get_Std_CG_Acct_Flag (
300 p_api_version IN NUMBER,
301 p_init_msg_list IN VARCHAR2
302 := FND_API.G_FALSE,
303 p_commit IN VARCHAR2
304 := FND_API.G_FALSE,
305 p_validation_level IN NUMBER
306 := FND_API.G_VALID_LEVEL_FULL,
307
308 p_organization_id IN NUMBER,
309 p_organization_code IN VARCHAR2,
310
311 x_cg_acct_flag OUT NOCOPY NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2 ) IS
315
316 l_api_name CONSTANT VARCHAR2(30) := 'get_Std_CG_Acct_Flag';
317 l_api_version CONSTANT NUMBER := 1.0;
318
319 l_api_message VARCHAR2(240);
320
321 l_statement NUMBER := 0;
322 l_cost_method NUMBER := 0;
323 l_cg_acct_flag NUMBER := 0;
324
325
326
327 BEGIN
328 ---------------------------------------------
329 -- Standard start of API savepoint
330 ---------------------------------------------
331 SAVEPOINT get_Std_CG_Acct_Flag;
332
333 ------------------------------------------------
334 -- Standard call to check for API compatibility
335 ------------------------------------------------
336 l_statement := 10;
337 IF not fnd_api.compatible_api_call (
338 l_api_version,
339 p_api_version,
340 l_api_name,
341 G_PKG_NAME ) then
342 RAISE fnd_api.G_exc_unexpected_error;
343 END IF;
344
345 ------------------------------------------------------------
346 -- Initialize message list if p_init_msg_list is set to TRUE
347 -------------------------------------------------------------
348 l_statement := 20;
349 IF fnd_api.to_Boolean(p_init_msg_list) then
350 fnd_msg_pub.initialize;
351 end if;
352
353 -------------------------------------------------------------
354 -- Initialize API return status to Success
355 -------------------------------------------------------------
356 l_statement := 30;
357 x_return_status := fnd_api.g_ret_sts_success;
358
359
360 -------------------------------------------------
361 -- Validate input parameters
362 -------------------------------------------------
363 l_statement := 40;
364 if ((p_organization_id is null) and (p_organization_code is null)) then
365 l_api_message := 'Please specify an organization';
366 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
367 FND_MESSAGE.set_token('TEXT', l_api_message);
368 FND_MSG_PUB.add;
369
370 RAISE fnd_api.g_exc_error;
371 end if;
372
373 ---------------------------------------------
374 -- Obtain organization parameters
375 ---------------------------------------------
376 if (p_organization_code is not null) then
377 l_statement := 50;
378 select primary_cost_method,nvl(cost_group_accounting,0)
379 into l_cost_method,l_cg_acct_flag
380 from mtl_parameters
381 where organization_code = p_organization_code;
382 else
383 l_statement := 60;
384 select primary_cost_method,nvl(cost_group_accounting,0)
385 into l_cost_method,l_cg_acct_flag
386 from mtl_parameters
387 where organization_id = p_organization_id;
388 end if;
389
390 ---------------------------------------------
391 -- Validate cost method
392 ---------------------------------------------
393 if (l_cost_method = 1) then
394 l_statement := 70;
395 x_cg_acct_flag := l_cg_acct_flag;
396 else
397 l_statement := 80;
398 l_api_message := 'This function is not valid for non-standard costing organizations';
399 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
400 FND_MESSAGE.set_token('TEXT', l_api_message);
401 FND_MSG_PUB.add;
402
403 x_cg_acct_flag := 0;
404 end if;
405
406 EXCEPTION
407 WHEN fnd_api.g_exc_error then
408 x_return_status := fnd_api.g_ret_sts_error;
409
410 fnd_msg_pub.count_and_get
411 ( p_count => x_msg_count,
412 p_data => x_msg_data );
413
414 WHEN fnd_api.g_exc_unexpected_error then
415 x_return_status := fnd_api.g_ret_sts_unexp_error;
416
417 fnd_msg_pub.count_and_get
418 ( p_count => x_msg_count,
419 p_data => x_msg_data );
420
421 WHEN OTHERS THEN
422 x_return_status := fnd_api.g_ret_sts_unexp_error ;
423 If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
424 fnd_msg_pub.add_exc_msg
425 ( 'CST_Utility_PUB','get_Std_CG_Acct_Flag : Statement - ' || to_char(l_statement));
426 end if;
427
428 fnd_msg_pub.count_and_get( p_count => x_msg_count,
429 p_data => x_msg_data );
430 END get_Std_CG_Acct_Flag;
431
432
433
434 -----------------------------------------------------------------------------
435 -- Start of comments --
436 -- --
437 -- PROCEDURE --
438 -- insert_MTA Function to ensure correct insertion of data into MTA --
439 -- Can be called from user code including the --
440 -- cst_dist_hook functions. It derives the values for --
441 -- populating the table from what the user provides. --
442 -- --
443 -- VERSION 1.0 --
444 -- --
445 -- PARAMETERS --
446 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
447 -- P_INIT_MSG_LIST Initialize message list? True/False --
448 -- P_COMMIT Should the API commit before returning? True/False --
449 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
450 -- X_MSG_COUNT Message Count - # of messages placed in message list--
451 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
452 -- P_ORG_ID Organization ID - REQUIRED --
453 -- P_TXN_ID Transaction ID - REQUIRED: should exist in MMT --
454 -- P_USER_ID User ID - REQUIRED --
455 -- P_LOGIN_ID Login ID --
456 -- P_REQ_ID Request ID --
457 -- P_PRG_APPL_ID Program Application ID --
458 -- P_PRG_ID Program ID --
459 -- P_ACCOUNT Reference account - should correspond to --
460 -- gl_code_combinations.code_combination_id --
461 -- P_DBT_CRDT Debit / Credit flag - enter 1 for debit --
462 -- -1 for credit --
463 -- will be used to set the sign for both base_txn_value--
464 -- and primary_quantity in MTA --
465 -- P_LINE_TYP Accounting line type - should correspond to a --
466 -- lookup for CST_ACCOUNTING_LINE_TYPE --
467 -- P_BS_TXN_VAL Total txn value in base currency - Enter a positive --
468 -- value, the sign will be determined by the value of --
469 -- P_DBT_CRDT --
470 -- P_CST_ELEMENT Cost element ID (1-5) - 1=material, 2=MOH, ... --
471 -- P_RESOURCE_ID Resource ID from BOM_RESOURCES - should correspond --
472 -- to bom_resources.resource_id --
473 -- P_ENCUMBR_ID Encumbrance type ID - should correspond to --
474 -- gl_encumbrance_types.encumbrance_type_id --
475 -- --
476 -- HISTORY: --
477 -- 09/25/02 Bryan Kuntz Created --
478 -- End of comments
479 -----------------------------------------------------------------------------
480 procedure insert_MTA (
481 P_API_VERSION IN NUMBER,
482 P_INIT_MSG_LIST IN VARCHAR2,
483 P_COMMIT IN VARCHAR2,
484 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
485 X_MSG_COUNT OUT NOCOPY NUMBER,
486 X_MSG_DATA OUT NOCOPY VARCHAR2,
487 P_ORG_ID IN NUMBER,
488 P_TXN_ID IN NUMBER,
489 P_USER_ID IN NUMBER,
490 P_LOGIN_ID IN NUMBER,
491 P_REQ_ID IN NUMBER,
492 P_PRG_APPL_ID IN NUMBER,
493 P_PRG_ID IN NUMBER,
494 P_ACCOUNT IN NUMBER,
495 P_DBT_CRDT IN NUMBER,
496 P_LINE_TYP IN NUMBER,
497 P_BS_TXN_VAL IN NUMBER,
498 P_CST_ELEMENT IN NUMBER,
499 P_RESOURCE_ID IN NUMBER,
500 P_ENCUMBR_ID IN NUMBER
501 ) IS
502
503 /* local control variables */
504 l_api_name CONSTANT VARCHAR2(30) := 'insert_MTA';
505 l_api_version CONSTANT NUMBER := 1.0;
506 l_debug VARCHAR2(1);
507 l_stmt_num number := 0;
508
509 /* local data variables */
510 l_sob_id number;
511 l_pri_curr gl_sets_of_books.currency_code%TYPE; -- varchar2(15);
512 l_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
513 l_precision fnd_currencies.precision%TYPE;
514 l_num number;
515
516 l_api_message varchar2(150);
517
518 BEGIN
519
520 SAVEPOINT Insert_MTA_PUB;
521 -- Initialize message list if p_init_msg_list is set to TRUE
522 if FND_API.to_Boolean(P_INIT_MSG_LIST) then
523 FND_MSG_PUB.initialize;
524 end if;
525 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
526
527 -- Standard check for compatibility
528 IF NOT FND_API.Compatible_API_Call (
529 l_api_version,
530 P_API_VERSION,
531 l_api_name,
532 G_PKG_NAME ) -- line 90
533 THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536
537 -- Initialize API return status to success
538 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
539
540 -- API body
541
542 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
543 if (l_debug = 'Y') then
544 l_api_message := 'insert_MTA API: Txn ID = '||to_char(P_TXN_ID);
545 FND_FILE.PUT_LINE(fnd_file.log,l_api_message);
546 end if;
547
548 -- Check that required parameters are not null
549 if (P_ORG_ID is null OR P_TXN_ID is null OR P_USER_ID is null OR P_DBT_CRDT is null
550 OR P_LINE_TYP is null OR P_BS_TXN_VAL is null) then
551 l_api_message := 'Required parameters P_ORG_ID, P_TXN_ID, P_USER_ID, P_DBT_CRDT, P_LINE_TYP, and P_BS_TXN_VAL must not be NULL';
552 RAISE FND_API.G_EXC_ERROR;
553 end if;
554
555 -- Check P_ACCOUNT
556 l_stmt_num := 10;
557 l_api_message := 'P_ACCOUNT';
558 if P_ACCOUNT IS NOT NULL then
559 select 1
560 into l_num
561 from gl_code_combinations
562 where code_combination_id = P_ACCOUNT;
563 end if;
564
565 -- Check P_DBT_CRDT
566 l_stmt_num := 20;
567 if (P_DBT_CRDT <> -1 AND P_DBT_CRDT <> 1) then
568 l_api_message := 'Invalid P_DBT_CRDT: should be 1 or -1';
569 RAISE FND_API.G_EXC_ERROR;
570 end if;
571
572 -- Check that P_LINE_TYP exists
573 l_stmt_num := 30;
574 l_api_message := 'P_LINE_TYP';
575 select 1
576 into l_num
577 from mfg_lookups
578 where lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
579 and lookup_code = P_LINE_TYP;
580
581 -- Check P_CST_ELEMENT
582 l_stmt_num := 40;
583 if P_CST_ELEMENT IS NOT NULL then
584 l_api_message := 'P_CST_ELEMENT';
585 select 1
586 into l_num
587 from cst_cost_elements
588 where cost_element_id = P_CST_ELEMENT;
589 end if;
590
591 -- Check P_RESOURCE_ID
592 l_stmt_num := 50;
593 if P_RESOURCE_ID IS NOT NULL then
594 l_api_message := 'P_RESOURCE_ID';
595 select 1
596 into l_num
597 from bom_resources
598 where resource_id = P_RESOURCE_ID;
599 end if;
600
601 -- Check P_ENCUMBR_ID
602 l_stmt_num := 60;
603 if P_ENCUMBR_ID IS NOT NULL then
604 l_api_message := 'P_ENCUMBR_ID';
605 select 1
606 into l_num
607 from gl_encumbrance_types
608 where encumbrance_type_id = P_ENCUMBR_ID;
609 end if;
610
611 -- Get Set of Books ID
612 l_stmt_num := 70;
613 l_api_message := 'P_ORG_ID';
614 select ledger_id
615 into l_sob_id
616 from cst_acct_info_v
617 where organization_id = P_ORG_ID;
618
619 if (l_debug = 'Y') then
620 l_api_message := 'Got Set_Of_Books_ID = '||to_char(l_sob_id);
621 FND_FILE.PUT_LINE (fnd_file.log, l_api_message);
622 end if;
623
624 -- Get primary currency
625 l_stmt_num := 80;
626 select currency_code
627 into l_pri_curr
628 from gl_sets_of_books
629 where set_of_books_id = l_sob_id;
630
631 -- Get precision and minimum_accountable_unit for the primary currency
632 l_stmt_num := 90;
633 select precision, minimum_accountable_unit
634 into l_precision, l_min_acct_unit
635 from fnd_currencies
636 where currency_code = l_pri_curr;
637
638 if (l_debug = 'Y') then
639 l_api_message := 'Got currency code = '||l_pri_curr;
640 FND_FILE.PUT_LINE (fnd_file.log,l_api_message);
641 end if;
642
643 l_stmt_num := 100;
644 insert into mtl_transaction_accounts -- line 95
645 (ORGANIZATION_ID,
646 TRANSACTION_ID,
647 REFERENCE_ACCOUNT,
648 INVENTORY_ITEM_ID,
649 BASE_TRANSACTION_VALUE,
650 PRIMARY_QUANTITY,
651 ACCOUNTING_LINE_TYPE,
652 COST_ELEMENT_ID,
653 TRANSACTION_DATE,
654 TRANSACTION_SOURCE_ID,
655 TRANSACTION_SOURCE_TYPE_ID,
656 TRANSACTION_VALUE,
657 RATE_OR_AMOUNT,
658 BASIS_TYPE,
659 RESOURCE_ID,
660 ACTIVITY_ID,
661 CURRENCY_CODE,
662 CURRENCY_CONVERSION_DATE,
663 CURRENCY_CONVERSION_TYPE,
664 CURRENCY_CONVERSION_RATE,
665 ENCUMBRANCE_TYPE_ID,
666 GL_BATCH_ID,
667 CONTRA_SET_ID,
668 REPETITIVE_SCHEDULE_ID,
669 GL_SL_LINK_ID,
670 REQUEST_ID,
671 PROGRAM_APPLICATION_ID,
672 PROGRAM_ID,
673 PROGRAM_UPDATE_DATE,
674 LAST_UPDATE_DATE,
675 LAST_UPDATED_BY,
676 CREATION_DATE,
677 CREATED_BY,
678 LAST_UPDATE_LOGIN)
679 select P_ORG_ID,
680 P_TXN_ID,
681 P_ACCOUNT,
682 mmt.inventory_item_id,
683 decode(l_min_acct_unit, NULL, decode(l_precision, NULL, ABS(P_BS_TXN_VAL) * sign(P_DBT_CRDT),
684 ROUND(ABS(P_BS_TXN_VAL) * sign(P_DBT_CRDT), l_precision)),
685 ROUND(ABS(P_BS_TXN_VAL) * sign(P_DBT_CRDT) / l_min_acct_unit) * l_min_acct_unit),
686 ABS(
687 DECODE(
688 mmt.transaction_action_id,
689 24,
690 mmt.quantity_adjusted,
691 mmt.primary_quantity
692 )
693 ) * sign(P_DBT_CRDT),
694 P_LINE_TYP,
695 P_CST_ELEMENT,
696 mmt.transaction_date,
697 decode(mmt.transaction_source_type_id, 16, -1, nvl(mmt.transaction_source_id, -1)),
698 mmt.transaction_source_type_id,
699 decode(mmt.currency_code, NULL, NULL, l_pri_curr, NULL,
700 decode(mmt.currency_conversion_rate, NULL, NULL, 0, NULL,
701 decode(fc.minimum_accountable_unit, NULL,
702 decode(fc.precision, NULL, sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.currency_conversion_rate,
703 ROUND(sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.currency_conversion_rate, fc.precision)),
704 ROUND(sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.currency_conversion_rate / fc.minimum_accountable_unit) * fc.minimum_accountable_unit))),
705 decode(mmt.primary_quantity, 0, 0, sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.primary_quantity),
706 1,
707 P_RESOURCE_ID,
708 NULL,
709 decode(mmt.currency_code, l_pri_curr, NULL, mmt.currency_code),
710 decode(mmt.currency_code, l_pri_curr, NULL, NULL, NULL, nvl(mmt.currency_conversion_date, mmt.transaction_date)),
711 decode(mmt.currency_code, l_pri_curr, NULL, NULL, NULL, mmt.currency_conversion_type),
712 decode(mmt.currency_code, l_pri_curr, NULL, NULL, NULL, nvl(mmt.currency_conversion_rate, -1)),
713 P_ENCUMBR_ID,
714 -1,
715 1,
716 NULL,
717 NULL,
718 P_REQ_ID,
719 P_PRG_APPL_ID,
720 -1*P_PRG_ID,
721 sysdate,
722 sysdate,
723 P_USER_ID,
724 sysdate,
725 P_USER_ID,
726 P_LOGIN_ID
727 from mtl_material_transactions mmt, fnd_currencies fc
728 where mmt.transaction_id = P_TXN_ID
729 and (mmt.organization_id = P_ORG_ID or
730 mmt.transfer_organization_id = P_ORG_ID)
731 and fc.currency_code = nvl(mmt.currency_code, l_pri_curr);
732
733 if SQL%FOUND then -- insert succeeded
734 l_api_message := 'INSERT succeeded';
735 else
736 l_api_message := 'Insert Failed for txn_id '||to_char(P_TXN_ID)||'. Check that it exists in MMT and that P_ORG_ID is correct.';
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 end if;
739
740 if (l_debug = 'Y') then
741 FND_FILE.PUT_LINE (fnd_file.log,l_api_message);
742 end if;
743
744 FND_MESSAGE.set_token('TEXT', l_api_message);
745 FND_MSG_PUB.ADD;
746
747 -- End of API body
748
749 FND_MSG_PUB.Count_And_Get (
750 p_encoded => FND_API.G_FALSE,
751 p_count => X_MSG_COUNT,
752 p_data => X_MSG_DATA );
753
754 -- Standard check of P_COMMIT
755 IF FND_API.to_Boolean(P_COMMIT) THEN
756 COMMIT WORK;
757 END IF;
758
759 EXCEPTION
760
761 when NO_DATA_FOUND then
762 l_api_message := 'Error at statement '||to_char(l_stmt_num)||'. Invalid '||l_api_message;
763 FND_MESSAGE.set_token('TEXT', l_api_message);
764 FND_MSG_PUB.ADD;
765 FND_MSG_PUB.Count_And_Get (
766 p_encoded => FND_API.G_FALSE,
767 p_count => X_MSG_COUNT,
768 p_data => X_MSG_DATA );
769 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
770 when FND_API.G_EXC_UNEXPECTED_ERROR then
771 FND_MESSAGE.set_token('TEXT', l_api_message);
772 FND_MSG_PUB.ADD;
773 FND_MSG_PUB.Count_And_Get (
774 p_encoded => FND_API.G_FALSE,
775 p_count => X_MSG_COUNT,
776 p_data => X_MSG_DATA );
777 X_RETURN_STATUS := fnd_api.g_ret_sts_unexp_error;
778 when FND_API.G_EXC_ERROR then
779 FND_MESSAGE.set_token('TEXT', l_api_message);
780 FND_MSG_PUB.ADD;
781 FND_MSG_PUB.Count_And_Get (
782 p_encoded => FND_API.G_FALSE,
783 p_count => X_MSG_COUNT,
784 p_data => X_MSG_DATA );
785 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
786 when OTHERS then
787 l_api_message := 'Error after statement '||to_char(l_stmt_num)||'. SQLCODE '||to_char(SQLCODE)||': '|| substrb(SQLERRM,1,100);
788 FND_MESSAGE.set_token('TEXT', l_api_message);
789 FND_MSG_PUB.ADD;
790 FND_MSG_PUB.Count_And_Get (
791 p_encoded => FND_API.G_FALSE,
792 p_count => X_MSG_COUNT,
793 p_data => X_MSG_DATA );
794 X_RETURN_STATUS := fnd_api.g_ret_sts_unexp_error;
795
796 END insert_MTA;
797
798 FUNCTION get_ret_sts_success return varchar2
799 IS
800 BEGIN
801 return fnd_api.g_ret_sts_success;
802 END get_ret_sts_success;
803
804 FUNCTION get_ret_sts_error return varchar2
805 IS
806 BEGIN
807 return fnd_api.g_ret_sts_error;
808 END get_ret_sts_error;
809
810 FUNCTION get_ret_sts_unexp_error return varchar2
811 IS
812 BEGIN
813 return fnd_api.g_ret_sts_unexp_error;
814 END get_ret_sts_unexp_error;
815
816 FUNCTION get_true return varchar2
817 IS
818 BEGIN
819 return fnd_api.g_true;
820 END get_true;
821
822 FUNCTION get_false return varchar2
823 IS
824 BEGIN
825 return fnd_api.g_false;
826 END get_false;
827
828 FUNCTION get_log return number
829 IS
830 BEGIN
831 return fnd_file.log;
832 END get_log;
833
834 -----------------------------------------------------------------------------
835 -- PROCEDURE --
836 -- get_ZeroCost_Flag --
837 -- --
838 -- DESCRIPTION --
839 -- Transaction ID and organization ID are passed in to this procedure. --
840 -- With this information, check to see if: --
841 -- organization_id is EAM-enabled, --
842 -- transaction_source_type = 5, --
843 -- transaction_action_id = 1, 27, 33, 34 --
844 -- subinventory_code is an expense subinventory --
845 -- inventory item is an asset item --
846 -- entity_type of wip_entity_id = 6, 7 --
847 -- If any of these conditions are not passed, then return 0 --
848 -- After checking that all these conditions pass, then check the --
849 -- issue_zero_cost_flag in wip_discrete_jobs of the work order; --
850 -- return the value of the flag --
851 -- --
852 -- PARAMETERS --
853 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
854 -- P_INIT_MSG_LIST Initialize message list? True/False --
855 -- P_COMMIT Should the API commit before returning? True/False --
856 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
857 -- X_MSG_COUNT Message Count - # of messages placed in message list--
858 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
859 -- P_TXN_ID Transaction ID - REQUIRED: should exist in MMT --
860 -- P_ORG_ID Organization ID - REQUIRED --
861 -- X_ZERO_COST_FLAG Return 0 if none of the above conditions are met; --
862 -- Otherwise return the value of issue_zero_cost_flag --
863 -- of the work order --
864 -- --
865 -- HISTORY: --
866 -- 07/01/03 Linda Soo Created --
867 -----------------------------------------------------------------------------
868 PROCEDURE get_ZeroCostIssue_Flag (
869 P_API_VERSION IN NUMBER,
870 P_INIT_MSG_LIST IN VARCHAR2 default FND_API.G_FALSE,
871 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
872 X_MSG_COUNT OUT NOCOPY NUMBER,
873 X_MSG_DATA OUT NOCOPY VARCHAR2,
874 P_TXN_ID IN NUMBER,
875 X_ZERO_COST_FLAG OUT NOCOPY NUMBER
876 )
877 IS
878 l_api_name CONSTANT VARCHAR2(30) := 'get_ZeroCostIssue_Flag';
879 l_api_version CONSTANT NUMBER := 1.0;
880
881 l_api_message VARCHAR2(240);
882 l_statement NUMBER := 0;
883 l_debug VARCHAR2(80);
884
885 l_count NUMBER;
886 l_eam_enabled NUMBER;
887 l_txn_act_id NUMBER;
888 l_txn_src_type_id NUMBER;
889 l_item_id NUMBER;
890 l_org_id NUMBER;
891 l_wip_entity_id NUMBER;
892 l_sub_inventory VARCHAR2(30);
893 l_exp_item NUMBER;
894 l_rebuild_item NUMBER;
895 l_exp_sub NUMBER;
896 l_entity_type NUMBER;
897 l_zero_cost_flag NUMBER := 0;
898
899 BEGIN
900
901 -----------------------------------
902 -- Standard start of API savepoint
903 -----------------------------------
904 SAVEPOINT get_ZeroCost_Flag;
905
906 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
907
908 /*if (l_debug = 'Y') then
909 fnd_file.put_line(fnd_file.log,'get_ZeroCostIssue_Flag');
910 end if;*/
911
912 ------------------------------------------------
913 -- Standard call to check for API compatibility
914 ------------------------------------------------
915 l_statement := 10;
916 IF not fnd_api.compatible_api_call( l_api_version,
917 p_api_version,
918 l_api_name,
919 G_PKG_NAME ) then
920 RAISE fnd_api.G_exc_unexpected_error;
921 END IF;
922
923 -------------------------------------------------------------
924 -- Initialize message list if p_init_msg_list is set to TRUE
925 -------------------------------------------------------------
926 l_statement := 20;
927 IF fnd_api.to_Boolean(p_init_msg_list) then
928 fnd_msg_pub.initialize;
929 end if;
930
931 -------------------------------------------
932 -- Initialize API return status to Success
933 -------------------------------------------
934 l_statement := 30;
935 x_return_status := fnd_api.g_ret_sts_success;
936
937 -----------------------------
938 -- Validate input parameters
939 -----------------------------
940 l_statement := 40;
941 if (p_txn_id is null) then
942 l_api_message := 'p_txn_id is null';
943 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
944 FND_MESSAGE.set_token('TEXT', l_api_message);
945 FND_MSG_PUB.add;
946 RAISE fnd_api.g_exc_error;
947 end if;
948
949 -----------------------------------
950 -- Obtain data for transaction ID
951 -----------------------------------
952 l_statement := 50;
953 begin
954 select mmt.transaction_action_id,
955 mmt.transaction_source_type_id,
956 nvl(mmt.transaction_source_id, -1),
957 mmt.inventory_item_id,
958 mmt.subinventory_code,
959 mmt.organization_id
960 into l_txn_act_id,
961 l_txn_src_type_id,
962 l_wip_entity_id,
963 l_item_id,
964 l_sub_inventory,
965 l_org_id
966 from mtl_material_transactions mmt
967 where mmt.transaction_id = p_txn_id;
968 exception
969 when no_data_found then
970 l_api_message := 'Transaction ID does not exist in MTL_MATERIAL_TRANSACTIONS table. ';
971 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
972 FND_MESSAGE.set_token('TEXT', l_api_message);
973 FND_MSG_PUB.add;
974 RAISE fnd_api.g_exc_error;
975 end;
976
977 -------------------------------------
978 -- Check transaction source type ID
979 -------------------------------------
980 l_statement := 60;
981 if (l_txn_src_type_id <> 5) then
982 x_zero_cost_flag := l_zero_cost_flag;
983 return;
984 end if;
985
986 ----------------------
987 -- Check entity type
988 ----------------------
989 l_statement := 70;
990 select entity_type
991 into l_entity_type
992 from wip_entities
993 where wip_entity_id = l_wip_entity_id;
994
995 if (l_entity_type not in (6,7)) then
996 x_zero_cost_flag := l_zero_cost_flag;
997 return;
998 end if;
999
1000 ----------------------------------
1001 -- Check transaction action type
1002 ----------------------------------
1003 l_statement := 80;
1004 if (l_txn_act_id not in (1, 27, 33, 34)) then
1005 x_zero_cost_flag := l_zero_cost_flag;
1006 return;
1007 end if;
1008
1009 -----------------------------------------------------
1010 -- Check if item is asset or expense; or if rebuild
1011 -----------------------------------------------------
1012 l_statement := 90;
1013 select decode(inventory_asset_flag,'Y', 0, 1), nvl(eam_item_type,-1)
1014 into l_exp_item, l_rebuild_item
1015 from mtl_system_items_b
1016 where inventory_item_id = l_item_id
1017 and organization_id = l_org_id;
1018
1019 -- Item is rebuildable item or not
1020 if (l_rebuild_item <> 3) then
1021 x_zero_cost_flag := l_zero_cost_flag;
1022 return;
1023 end if;
1024
1025 -- Item is Asset or Expense
1026 if (l_exp_item = 1) then
1027 x_zero_cost_flag := l_zero_cost_flag;
1028 return;
1029 end if;
1030
1031 -------------------------------------------
1032 -- Check subinventory is asset or expense
1033 -------------------------------------------
1034 l_statement := 100;
1035 select decode(asset_inventory, 1, 0, 1)
1036 into l_exp_sub
1037 from mtl_secondary_inventories
1038 where secondary_inventory_name = l_sub_inventory
1039 and organization_id = l_org_id;
1040
1041 if (l_exp_sub = 0) then
1042 x_zero_cost_flag := l_zero_cost_flag;
1043 return;
1044 end if;
1045
1046 ---------------------------------------------
1047 -- Get zero cost flag
1048 ---------------------------------------------
1049 l_statement := 110;
1050 select decode(nvl(issue_zero_cost_flag, 'N'), 'Y', 1, 0)
1051 into l_zero_cost_flag
1052 from wip_discrete_jobs
1053 where wip_entity_id = l_wip_entity_id;
1054
1055 x_zero_cost_flag := l_zero_cost_flag;
1056
1057 -- Standard Call to get message count and if count = 1, get message info
1058 FND_MSG_PUB.Count_And_Get (
1059 p_count => x_msg_count,
1060 p_data => x_msg_data );
1061
1062 EXCEPTION
1063
1064 WHEN fnd_api.g_exc_error then
1065 x_return_status := fnd_api.g_ret_sts_error;
1066 x_zero_cost_flag:= -1;
1067
1068 fnd_msg_pub.count_and_get(
1069 p_count => x_msg_count,
1070 p_data => x_msg_data );
1071
1072 WHEN fnd_api.g_exc_unexpected_error then
1073 x_return_status := fnd_api.g_ret_sts_unexp_error;
1074 x_zero_cost_flag:= -1;
1075
1076 fnd_msg_pub.count_and_get(
1077 p_count => x_msg_count,
1078 p_data => x_msg_data );
1079
1080 WHEN OTHERS THEN
1081 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1082 x_zero_cost_flag:= -1;
1083 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
1084 fnd_msg_pub.add_exc_msg ( 'CST_Utility_PUB',
1085 ' get_ZeroCostIssue_Flag: Statement - ' || to_char(l_statement));
1086 end if;
1087
1088 fnd_msg_pub.count_and_get(
1089 p_count => x_msg_count,
1090 p_data => x_msg_data );
1091
1092 END get_ZeroCostIssue_Flag;
1093
1094
1095 ----------------------------------------------------------------------------
1096 -- PROCEDURE --
1097 -- get_Direct_Item_Charge_Acct --
1098 -- --
1099 -- DESCRIPTION --
1100 -- This API is from CST_eamCost_PUB package. Added this API to this
1101 -- package to minimize the dependencies PO would have on the API.
1102 -- Changes starting from J should be made to this API.
1103 --
1104 -- This API returns the account number given a EAM job
1105 -- (entity type = 6,7) and purchasing category. If the wip identity
1106 -- doesn't refer to an EAM job type then -1 is returned, -1 is also
1107 -- returned if no account is defined for that particular wip entity.
1108 --
1109 -- PURPOSE: --
1110 -- Oracle Applications Rel 11i.6 --
1111 -- Costing Support for EAM --
1112 -- Called by the PO account generator
1113 -- --
1114 -- --
1115 -- HISTORY: --
1116 -- 08/28/03 Linda Soo Created
1117 -- Dummy API for pre-req for PO to minimize dependencies
1118 ----------------------------------------------------------------------------
1119
1120 PROCEDURE get_Direct_Item_Charge_Acct (
1121 p_api_version IN NUMBER,
1122 p_init_msg_list IN VARCHAR2
1123 := FND_API.G_FALSE,
1124 p_commit IN VARCHAR2
1125 := FND_API.G_FALSE,
1126 p_validation_level IN NUMBER
1127 := FND_API.G_VALID_LEVEL_FULL,
1128 p_wip_entity_id IN NUMBER := NULL,
1129 x_material_acct OUT NOCOPY NUMBER,
1130 x_return_status OUT NOCOPY VARCHAR2,
1131 x_msg_count OUT NOCOPY NUMBER,
1132 x_msg_data OUT NOCOPY VARCHAR2,
1133 p_category_id IN NUMBER := -1
1134 ) IS
1135
1136 l_api_name CONSTANT VARCHAR2(30) := 'get_Direct_Item_Charge_Acct';
1137 l_api_version CONSTANT NUMBER := 1.0;
1138
1139 l_api_message VARCHAR2(240);
1140 l_statement NUMBER := 0;
1141 l_account NUMBER := -1;
1142 l_entity_type NUMBER;
1143 l_cst_element_id NUMBER := 1;
1144
1145 BEGIN
1146 ---------------------------------------------
1147 -- Standard start of API savepoint
1148 ---------------------------------------------
1149 SAVEPOINT get_Direct_Item_Charge_Acct;
1150
1151 ------------------------------------------------
1152 -- Standard call to check for API compatibility
1153 ------------------------------------------------
1154 l_statement := 10;
1155 IF not fnd_api.compatible_api_call (
1156 l_api_version,
1157 p_api_version,
1158 l_api_name,
1159 G_PKG_NAME ) then
1160 RAISE fnd_api.G_exc_unexpected_error;
1161 END IF;
1162
1163 ------------------------------------------------------------
1164 -- Initialize message list if p_init_msg_list is set to TRUE
1165 -------------------------------------------------------------
1166 l_statement := 20;
1167 IF fnd_api.to_Boolean(p_init_msg_list) then
1168 fnd_msg_pub.initialize;
1169 end if;
1170
1171 -------------------------------------------------------------
1172 -- Initialize API return status to Success
1173 -------------------------------------------------------------
1174 l_statement := 30;
1175 x_return_status := fnd_api.g_ret_sts_success;
1176
1177 -------------------------------------------------
1178 -- Validate input parameters
1179 -------------------------------------------------
1180 l_statement := 40;
1181 if (p_wip_entity_id is null) then
1182 l_api_message := 'Please specify a wip entity id';
1183 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
1184 FND_MESSAGE.set_token('TEXT', l_api_message);
1185 FND_MSG_PUB.add;
1186
1187 RAISE fnd_api.g_exc_error;
1188 end if;
1189
1190 ---------------------------------------------
1191 -- Verify if EAM job
1192 ---------------------------------------------
1193 l_statement := 50;
1194 select entity_type
1195 into l_entity_type
1196 from wip_entities
1197 where wip_entity_id = p_wip_entity_id;
1198
1199 if (l_entity_type in (6,7)) then
1200 ---------------------------------------------
1201 -- Obtain cost element based on category_id
1202 ---------------------------------------------
1203 l_statement := 60;
1204 begin
1205 select cceea.mfg_cost_element_id
1206 into l_cst_element_id
1207 from cst_cat_ele_exp_assocs cceea
1208 where cceea.category_id = p_category_id
1209 and sysdate >= cceea.start_date
1210 and sysdate <= (nvl(cceea.end_date, sysdate) + 1);
1211 exception
1212 when no_data_found then
1213 l_cst_element_id := 1;
1214 end;
1215
1216 l_statement := 70;
1217 select decode(l_cst_element_id, 1, nvl(material_account,-1),
1218 3, nvl(resource_account, -1),
1219 4, nvl(outside_processing_account, -1))
1220 into l_account
1221 from wip_discrete_jobs
1222 where wip_entity_id = p_wip_entity_id;
1223 end if;
1224
1225 x_material_acct := l_account;
1226
1227 EXCEPTION
1228 WHEN fnd_api.g_exc_error then
1229 x_return_status := fnd_api.g_ret_sts_error;
1230 x_material_acct := -1;
1231
1232 fnd_msg_pub.count_and_get
1233 ( p_count => x_msg_count,
1234 p_data => x_msg_data );
1235
1236 WHEN fnd_api.g_exc_unexpected_error then
1237 x_return_status := fnd_api.g_ret_sts_unexp_error;
1238 x_material_acct := -1;
1239
1240 fnd_msg_pub.count_and_get
1241 ( p_count => x_msg_count,
1242 p_data => x_msg_data );
1243
1244 WHEN OTHERS THEN
1245 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1246 x_material_acct := -1;
1247 If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
1248 fnd_msg_pub.add_exc_msg
1249 ( 'CST_Utility_PUB',' get_Direct_Item_Charge_Acct : Statement - ' || to_char(l_statement));
1250 end if;
1251
1252 fnd_msg_pub.count_and_get( p_count => x_msg_count,
1253 p_data => x_msg_data );
1254 END get_Direct_Item_Charge_Acct;
1255
1256 FUNCTION check_Db_Version
1257 (
1258 p_api_version IN NUMBER,
1259 p_init_msg_list IN VARCHAR2,
1260 x_return_status OUT NOCOPY VARCHAR2,
1261 x_msg_count OUT NOCOPY NUMBER,
1262 x_msg_data OUT NOCOPY VARCHAR2
1263 ) return NUMBER
1264 IS
1265 l_db_version NUMBER;
1266 l_api_name CONSTANT VARCHAR2(30) := 'check_Db_Version';
1267 l_api_version CONSTANT NUMBER := 1.0;
1268 l_statement NUMBER := 0;
1269 BEGIN
1270 ------------------------------------------------
1271 -- Standard call to check for API compatibility
1272 ------------------------------------------------
1273 l_statement := 10;
1274 IF NOT fnd_api.compatible_api_call (
1275 l_api_version,
1276 p_api_version,
1277 l_api_name,
1278 G_PKG_NAME )
1279 THEN RAISE fnd_api.G_exc_unexpected_error;
1280 END IF;
1281
1282 ------------------------------------------------------------
1283 -- Initialize message list if p_init_msg_list is set to TRUE
1284 -------------------------------------------------------------
1285 l_statement := 20;
1286 IF fnd_api.to_Boolean(p_init_msg_list)
1287 THEN fnd_msg_pub.initialize;
1288 END IF;
1289
1290 -------------------------------------------------------------
1291 -- Initialize API return status to Success
1292 -------------------------------------------------------------
1293 l_statement := 30;
1294 x_return_status := fnd_api.g_ret_sts_success;
1295
1296 SELECT replace(substr(version,1,instr(version,'.',1,2)-1),'.')
1297 INTO l_db_version
1298 FROM v$instance;
1299
1300 IF (l_db_version < 90)
1301 THEN return 0;
1302 ELSE return 1;
1303 END IF;
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1307 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1308 THEN fnd_msg_pub.add_exc_msg
1309 ( 'CST_Utility_PUB',' check_Db_Version : Statement - ' || to_char(l_statement));
1310 END IF;
1311 fnd_msg_pub.count_and_get( p_count => x_msg_count,
1312 p_data => x_msg_data );
1313 END check_Db_Version;
1314
1315 Procedure Get_Context_Value (
1316 p_api_version IN NUMBER,
1317 p_init_msg_list IN VARCHAR2 ,
1318 p_commit IN VARCHAR2 ,
1319 p_validation_level IN NUMBER ,
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER,
1322 x_msg_data OUT NOCOPY VARCHAR2,
1323 p_org_id IN NUMBER,
1324 p_ledger_id OUT NOCOPY NUMBER,
1325 p_le_id OUT NOCOPY NUMBER,
1326 p_ou_id OUT NOCOPY NUMBER)
1327 IS
1328 l_api_version CONSTANT NUMBER :=1.0;
1329 l_api_name CONSTANT VARCHAR2(30) :='Get Context Value';
1330
1331 BEGIN
1332
1333 ----------------------------------------------------------
1334 -- Standard Begin of API Savepoint
1335 ----------------------------------------------------------
1336 SAVEPOINT GET_CONTEXT_PUB;
1337
1338 ----------------------------------------------------------
1339 -- Standard call to check for call compatibility
1340 ----------------------------------------------------------
1341 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1342 p_api_version,
1343 l_api_name,
1344 G_PKG_NAME)
1345 THEN
1346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1347 END IF;
1348
1349 ----------------------------------------------------------
1350 --Check p_init_msg_list
1351 ----------------------------------------------------------
1352 IF FND_API.to_Boolean(p_init_msg_list) THEN
1353 FND_MSG_PUB.initialize;
1354 END IF;
1355
1356 ---------------------------------------------------------
1357 --Initialize API return Status to Success
1358 --------------------------------------------------------
1359 x_return_status := FND_API.G_RET_STS_SUCCESS;
1360
1361 SELECT
1362 ledger_id,
1363 legal_entity,
1364 operating_unit
1365 INTO
1366 p_ledger_id,
1367 p_le_id,
1368 p_ou_id
1369 FROM
1370 cst_acct_info_v
1371 WHERE
1372 organization_id = p_org_id;
1373
1374 IF FND_API.To_Boolean(p_commit) THEN
1375 COMMIT;
1376 END IF;
1377
1378 FND_MSG_PUB.Count_And_Get
1379 ( p_count => x_msg_count,
1380 p_data => x_msg_data
1381 );
1382
1383 EXCEPTION
1384 WHEN FND_API.G_EXC_ERROR THEN
1385 ROLLBACK TO GET_CONTEXT_PUB;
1386 x_return_status := FND_API.G_RET_STS_ERROR;
1387 FND_MSG_PUB.Count_And_Get
1388 ( p_count => x_msg_count,
1389 p_data => x_msg_data
1390 );
1391
1392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1393 ROLLBACK TO GET_CONTEXT_PUB;
1394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1395 FND_MSG_PUB.Count_And_Get
1396 ( p_count => x_msg_count,
1397 p_data => x_msg_data
1398 );
1399
1400 WHEN OTHERS THEN
1401 ROLLBACK TO GET_CONTEXT_PUB;
1402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1403
1404 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1405 FND_MSG_PUB.Add_Exc_Msg
1406 ( G_PKG_NAME,
1407 l_api_name
1408 );
1409 END IF;
1410 FND_MSG_PUB.Count_And_Get
1411 ( p_count => x_msg_count,
1412 p_data => x_msg_data
1413 );
1414
1415 end Get_Context_Value;
1416
1417 ----------------------------------------------------------------------------
1418 --
1419 -- PROCEDURE
1420 -- Get_Receipt_Event_Info:
1421 -- API provides the name of the event class and entity code for a
1422 -- receiving transaction type
1423 -- PARAMETERS
1424 -- p_api_version API version Required
1425 -- p_transaction_type Receiving Transaction Type (from RCV_TRANSACTIONS)
1426 -- p_entity_code XLA Entity Code (RCV_ACCOUNTING_EVENTS)
1427 -- p_application_id Application Identifier for Cost Management
1428 -- p_event_class_code XLA Event Class Code
1429 --------------------------------------------------------------------------
1430
1431 Procedure Get_Receipt_Event_Info (
1432 p_api_version IN NUMBER,
1433 p_transaction_type IN VARCHAR2,
1434 x_return_status OUT NOCOPY VARCHAR2,
1435 x_msg_count OUT NOCOPY NUMBER,
1436 x_msg_data OUT NOCOPY VARCHAR2,
1437 p_entity_code OUT NOCOPY VARCHAR2,
1438 p_application_id OUT NOCOPY NUMBER,
1439 p_event_class_code OUT NOCOPY VARCHAR2
1440 ) IS
1441
1442 l_api_name CONSTANT VARCHAR2(30) := 'Get_Receipt_Event_Info';
1443 l_api_version CONSTANT NUMBER := 1.0;
1444 l_stmt_num NUMBER := 0;
1445
1446 BEGIN
1447 SAVEPOINT Get_Receipt_Event_Info;
1448 IF NOT FND_API.COMPATIBLE_API_CALL ( l_api_version,
1449 p_api_version,
1450 l_api_name,
1451 G_PKG_NAME ) THEN
1452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1453 END IF;
1454
1455 x_return_status := FND_API.G_RET_STS_SUCCESS;
1456 p_entity_code := 'RCV_ACCOUNTING_EVENTS';
1457 p_application_id := 707; /* Application ID for Cost Management */
1458
1459 IF p_transaction_type IN ( 'RECEIVE', 'MATCH', 'RETURN TO VENDOR' ) THEN
1460 l_stmt_num := 10;
1461 SELECT EVENT_CLASS_CODE
1462 INTO p_event_class_code
1463 FROM CST_XLA_RCV_EVENT_MAP
1464 WHERE TRANSACTION_TYPE_ID = 1;
1465 ELSE
1466 p_event_class_code := NULL;
1467 x_return_status := fnd_api.g_ret_sts_error;
1468 END IF;
1469 EXCEPTION
1470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1471 p_event_class_code := NULL;
1472 x_return_status := fnd_api.g_ret_sts_unexp_error;
1473 WHEN OTHERS THEN
1474 p_event_class_code := NULL;
1475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1476 END Get_Receipt_Event_Info;
1477
1478 END CST_Utility_PUB;