4 --
1 PACKAGE WSH_UTIL_CORE AUTHID CURRENT_USER as
2 /* $Header: WSHUTCOS.pls 120.9.12020000.2 2013/03/14 16:00:16 suppal ship $ */
3
5 -- PACKAGE TYPES
6 --
7
8 -- Description: Generic tab of numbers for passing _id information
9 TYPE Id_Tab_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10
11 -- Description: Generic tab of varchar2 for passing column information
12 TYPE Column_Tab_Type IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
13
14 -- Description: Generic tab of date for passing column information
15 TYPE Date_Tab_Type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
16
17 TYPE Loc_Info_rec IS REcord (
18 wsh_location_id NUMBER
19 ,source_location_id NUMBER
20 ,location_source_code wsh_locations.location_source_code%TYPE
21 ,location_code wsh_locations.location_code%TYPE
22 ,address1 wsh_locations.address1%TYPE
23 ,city wsh_locations.city%TYPE
24 ,state wsh_locations.state%TYPE
25 ,country wsh_locations.country%TYPE
26 ,postal_code wsh_locations.postal_code%TYPE
27 ,ui_location_code wsh_locations.ui_location_code%TYPE
28 ,hr_location_code hr_locations_all.location_code%TYPE
29 );
30 TYPE Loc_Info_Tab IS TABLE OF Loc_Info_rec INDEX BY BINARY_INTEGER;
31
32 -- Description: record of number of errors, warning, unexpected errors, and successes
33 TYPE MsgCountType is RECORD (
34 e_count NUMBER,
35 u_count NUMBER,
36 w_count NUMBER,
37 s_count NUMBER
38 );
39
40 TYPE key_value_rec_type IS RECORD(
41 key NUMBER,
42 value NUMBER
43 );
44
45 TYPE key_char500_rec_type IS RECORD(
46 key NUMBER,
47 value VARCHAR2(500)
48 );
49
50 TYPE key_boolean_rec_type IS RECORD(
51 key NUMBER,
52 value BOOLEAN
53 );
54
55 TYPE key_value_tab_type IS TABLE OF key_value_rec_type INDEX BY BINARY_INTEGER;
56 TYPE char500_tab_type IS TABLE OF key_char500_rec_type INDEX BY
57 BINARY_INTEGER;
58 TYPE boolean_tab_type IS TABLE OF key_boolean_rec_type INDEX
59 BY BINARY_INTEGER;
60 -- HVOP heali
61 TYPE tbl_varchar IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
62 TYPE RefCurType IS REF CURSOR;
63 -- HVOP heali
64
65 TYPE operating_unit_info_rec_type IS RECORD(
66 org_id NUMBER,
67 ledger_id NUMBER, -- LE Uptake
68 currency_code VARCHAR2(15)
69 --currency_code gl_ledgers_public_v.currency_code%TYPE
70 );
71
72 -- cached global variables
73 G_OPERATING_UNIT_INFO operating_unit_info_rec_type;
74 --
75
76 -- cached global variables for Bugfix 4070732
77 G_START_OF_SESSION_API VARCHAR2(1000);
78 G_CALL_FTE_LOAD_TENDER_API BOOLEAN;
79 G_STOP_IDS_STOP_IDS_CACHE WSH_UTIL_CORE.key_value_tab_type;
80 G_STOP_IDS_STOP_IDS_EXT_CACHE WSH_UTIL_CORE.key_value_tab_type;
81 --
82
83 --
84 -- PACKAGE CONSTANTS
85 --
86
87 -- Description: Constants used to allow the user to differentiate
88 -- between errors, warning and success
89 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
90 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
91 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
92 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
93 --
94 --
95 -- Description: This exception can be used to raise when the return
96 -- is a warning and if the api needs to exit out
97 -- immediately.
98 --
102 C_HASH_SIZE CONSTANT NUMBER := 33554432 ; -- power(2, 25)
99 G_EXC_WARNING EXCEPTION;
100 --
101 C_HASH_BASE CONSTANT NUMBER := 1;
103 C_INDEX_LIMIT CONSTANT NUMBER := 2147483648; -- power(2,31)
104
105 -- OTM R12
106 G_GC3_IS_INSTALLED VARCHAR2(1) := NULL;
107 -- End of OTM R12
108
109 --
110 --
111 -- J-IB-NPARIKH-{
112
113 C_NULL_SF_LOCN_ID CONSTANT NUMBER := -1;
114 C_NOTNULL_SF_LOCN_ID CONSTANT NUMBER := -2;
115 C_SPLIT_DLVY_SUFFIX CONSTANT VARCHAR2(30) := '_SPLIT_DLVY';
116 C_IB_ASN_PREFIX CONSTANT VARCHAR2(30) := 'WSH_IB_ASN';
117 C_IB_RECEIPT_PREFIX CONSTANT VARCHAR2(30) := 'WSH_IB_RECEIPT';
118 C_IB_PO_PREFIX CONSTANT VARCHAR2(30) := 'WSH_IB_PO';
119
120 e_not_allowed EXCEPTION;
121 e_not_allowed_warning EXCEPTION;
122
123 -- J-IB-NPARIKH-}
124
125
126 C_MAX_DECIMAL_DIGITS CONSTANT NUMBER := 38;
127 C_MAX_DECIMAL_DIGITS_INV CONSTANT NUMBER := 5;
128 C_MAX_REAL_DIGITS CONSTANT NUMBER := 10;
129
130 -- HW OPMCONV - No need for OPM specific precision
131 --C_MAX_DECIMAL_DIGITS_OPM CONSTANT NUMBER := 9;
132
133 --
134 -- FUNCTION: Get_Location_Description
135 -- Purpose: Function gives a description of the location
136 -- based on the location_id
137 -- Arguments: p_location_id - Location identifier
138 -- p_format - Format for description
139 -- Return Values: Returns a description of the location in VARCHAR2
140 -- Notes: p_format supports the following:
141 --
142
143 FUNCTION Get_Location_Description (
144 p_location_id IN NUMBER,
145 p_format IN VARCHAR2
146 ) RETURN VARCHAR2;
147
148
149 --
150 -- PROCEDURE: Site_Code_to_Site_id
151 -- PURPOSE : Maps site_code to site_id
152 -- ARGUMENTS : p_site_code - site code that needs to be mapped
153 -- p_site_id - site id for the code
154 -- x_return_status - WSH_UTIL_CORE.G_RET_STS_SUCCESS or NOT
155 --
156
157 PROCEDURE Site_Code_to_Site_id(p_site_code IN VARCHAR2,
158 p_site_id OUT NOCOPY NUMBER,
159 x_return_status OUT NOCOPY VARCHAR2);
160
161 --
162 -- PROCEDURE: Get_Location_Id
163 -- Purpose: Convert Organization_id or ship_to_site_id to
164 -- a location_id
165 -- Arguments: p_mode - 'CUSTOMER SITE', 'VENDOR SITE' or 'ORG'
166 -- p_source_id - organization_id or site_id to convert
167 -- based on p_mode
168 -- x_location_id - Converted to location_id
169 -- x_api_status - FND_API.G_RET_STS_SUCCESS
170 -- FND_API.G_RET_STS_ERROR
171 -- If Error message can be retrieved using FND_MESSAGE.GET
172 -- Description: Gets location information for a particular inventory
173 -- organization using hr_locations view
174 --
175
176 PROCEDURE Get_Location_Id (
177 p_mode IN VARCHAR2,
178 p_source_id IN NUMBER,
179 x_location_id OUT NOCOPY NUMBER,
180 x_api_status OUT NOCOPY VARCHAR2,
181 p_transfer_location IN BOOLEAN DEFAULT TRUE
182 );
183
184 --
185 -- PROCEDURE: get_master_from_org
186 -- PURPOSE: Obtain master organization id for an organization_id
187 -- Arguments: p_org_id - organization_id
188 -- x_master_org_id - Master organization id for input organization_id
189 -- x_return_status -
190 -- WSH_UTIL_CORE.G_RET_STS_SUCCESS
191 -- WSH_UTIL_CORE.G_RET_STS_ERROR
192 -- Notes: Throws exception when fails
193 --
194
195 PROCEDURE get_master_from_org(
196 p_org_id IN NUMBER,
197 x_master_org_id OUT NOCOPY NUMBER,
198 x_return_status OUT NOCOPY VARCHAR2);
199
200 --
201 -- FUNCTION: Org_To_Location
202 -- PURPOSE: Convert organization_id to location_id
203 -- Arguments: p_org_id - organization_id
204 -- Return Values: Location_id
205 -- Notes: Throws exception when failing to convert
206 --
207
208 FUNCTION Org_To_Location (
209 p_org_id IN NUMBER,
210 p_transfer_location IN BOOLEAN DEFAULT FALSE
211 ) RETURN NUMBER;
212
213 --
214 -- FUNCTION: Cust_Site_To_Location
215 -- PURPOSE: Convert customer site_id to location_id
216 -- Arguments: p_site_id - site_id
217 -- Return Values: Location_id
218 -- Notes: Throws exception when failing to convert
219 --
220
221 FUNCTION Cust_Site_To_Location (
222 p_site_id IN NUMBER,
223 p_transfer_location IN BOOLEAN DEFAULT TRUE
224 ) RETURN NUMBER;
225
226 --
227 -- FUNCTION: Vendor_Site_To_Location
228 -- PURPOSE: Convert Vendor site_id to location_id
229 -- Arguments: p_site_id - site_id
230 -- Return Values: Location_id
231 -- Notes: Throws exception when failing to convert
232 --
233
234 FUNCTION Vendor_Site_To_Location (
235 p_site_id IN NUMBER,
236 p_transfer_location IN BOOLEAN DEFAULT TRUE
237 ) RETURN NUMBER;
238
239 --
240 -- FUNCTION: Ship_Method_To_Freight
241 -- PURPOSE: Convert Ship_Method_Code to Freight_Code
242 -- Arguments: p_ship_method_code, p_organization_id
243 -- Return Values: Freight_Code
244 -- Notes: Throws exception when failing to convert
245 --
246
250 ) RETURN VARCHAR2;
247 FUNCTION Ship_Method_To_Freight (
248 p_ship_method_code IN VARCHAR2,
249 p_organization_id IN NUMBER
251
252 -- PRAGMA RESTRICT_REFERENCES (Ship_Method_To_Freight, WNDS);
253
254 --
255 -- This set of functions and procedures can be used by the concurrent
256 -- programs to print messages to the log file. The following are
257 -- supported:
258 --
259
260
261 --
262 -- Procedure: Enable_Concurrent_Log_Print
263 -- Purpose: Enable printing of log messages to concurrent
264 -- program log files
265 -- Arguments: None
266 --
267
268 PROCEDURE Enable_Concurrent_Log_Print;
269
270 --
271 -- Procedure: Set_Log_Level
272 -- Purpose: Set Appropriate log level to print
273 -- debug messages to the concurrent program log file
274 -- Arguments: p_log_level
275 --
276
277 PROCEDURE Set_Log_Level(
278 p_log_level IN NUMBER
279 );
280
281
282
283 --
284 -- Procedure: Print
285 -- Purpose: Prints a line of message text to the log file
286 -- and does not insert a new line at the end
287 -- program log files
288 -- Arguments: p_msg - message text to print
289 --
290
291 PROCEDURE Print(
292 p_msg IN VARCHAR2
293 );
294
295 --
296 -- Procedure: Println
297 -- Purpose: Prints a line of message text to the log file
298 -- and inserts a new line at the end
299 -- program log files
300 -- Arguments: p_msg - message text to print
301 --
302
303 PROCEDURE Println(
304 p_msg IN VARCHAR2
305 );
306
307 --
308 -- Procedure: Println
309 -- Purpose: Prints a new line character to the log file
310 -- program log files
311 -- Arguments: None
312 --
313
314 PROCEDURE Println;
315
316
317
318 --
319 -- Procedure: PrintMsg
320 -- Purpose: Prints a line of message text to the log file
321 -- and inserts a new line at the end
322 -- program log files irrespective of the debug level
323 -- Should be used for the debug messages which need to
324 -- printed always
325 -- Arguments: p_msg - message text to print
326 --
327
328 PROCEDURE PrintMsg(
329 p_msg IN VARCHAR2
330 );
331
332 --
333 -- Procedure: PrintMsg
334 -- Purpose: Prints a new line character to the log file
335 -- program log files irrespective of the Debug Level
336 -- Arguments: None
337 --
338
339 PROCEDURE PrintMsg;
340
341 --
342 -- Procedure: PrintDateTime
343 -- Purpose: Prints system date and time to the log file
344 -- Arguments: None
345 --
346
347 PROCEDURE PrintDateTime;
348
349 --
350 -- Name
351 -- Gen_Check_Unique
352 -- Purpose
353 -- Checks for duplicates in database
354 -- Arguments
355 -- query_text query to execute to test for uniqueness
356 -- prod_name product name to send message for
357 -- msg_name message to print if duplicate found
358 --
359 -- Notes
360 -- uses DBMS_SQL package
361
362 PROCEDURE Gen_Check_Unique
363 (p_table_name IN VARCHAR2,
364 p_pkey1 IN VARCHAR2 DEFAULT NULL,
365 p_pkey1_value IN VARCHAR2 DEFAULT NULL,
366 p_is_1_char IN VARCHAR2 DEFAULT NULL,
367 p_pkey2 IN VARCHAR2 DEFAULT NULL,
368 p_pkey2_value IN VARCHAR2 DEFAULT NULL,
369 p_is_2_char IN VARCHAR2 DEFAULT NULL,
370 p_pkey3 IN VARCHAR2 DEFAULT NULL,
371 p_pkey3_value IN VARCHAR2 DEFAULT NULL,
372 p_is_3_char IN VARCHAR2 DEFAULT NULL,
373 p_pkey4 IN VARCHAR2 DEFAULT NULL,
374 p_pkey4_value IN VARCHAR2 DEFAULT NULL,
375 p_is_4_char IN VARCHAR2 DEFAULT NULL,
376 p_pkey5 IN VARCHAR2 DEFAULT NULL,
377 p_pkey5_value IN VARCHAR2 DEFAULT NULL,
378 p_is_5_char IN VARCHAR2 DEFAULT NULL,
379 p_pkey6 IN VARCHAR2 DEFAULT NULL,
380 p_pkey6_value IN VARCHAR2 DEFAULT NULL,
381 p_is_6_char IN VARCHAR2 DEFAULT NULL,
382 p_pkey7 IN VARCHAR2 DEFAULT NULL,
383 p_pkey7_value IN VARCHAR2 DEFAULT NULL,
384 p_is_7_char IN VARCHAR2 DEFAULT NULL,
385 p_pkey8 IN VARCHAR2 DEFAULT NULL,
386 p_pkey8_value IN VARCHAR2 DEFAULT NULL,
387 p_is_8_char IN VARCHAR2 DEFAULT NULL,
388 p_row_id IN VARCHAR2 DEFAULT NULL,
389 p_prod_name IN VARCHAR2,
390 p_msg_name IN VARCHAR2);
391
392
393 PROCEDURE GET_ACTIVE_DATE(P_TABLE_NAME IN varchar2,
394 P_COLUMN_NAME IN varchar2,
395 P_ROW_ID IN varchar2,
399 date_fetched OUT NOCOPY DATE);
396 X_DATE_FETCHED OUT NOCOPY DATE);
397
398 PROCEDURE Get_Active_Date( query_text IN VARCHAR2,
400
401 --
402 -- Name
403 -- Add_Message
404 -- Purpose
405 -- Adds a message to the FND_MSG_PUB table of messages. Also,
406 -- concatenates a message type of 'Warning:', 'Error:' or 'Unexpected Error:'
407 -- to the translated message and sets it back on the stack.
408 -- Arguments
409 -- p_message_type - values are
410 -- 'S' - if successful
411 -- 'W' - if warning
412 -- 'E' - if error
413 -- 'U' - if unexpected error
414 --
415
416 -- Overloaded the procedure to set message and tokens
417 -- Harmonization Project I **heali
418 PROCEDURE Add_Message (
419 p_message_type IN VARCHAR2,
420 p_module_name IN VARCHAR2,
421 p_error_name IN VARCHAR2,
422 p_token1 IN VARCHAR2 DEFAULT NULL,
423 p_value1 IN VARCHAR2 DEFAULT NULL,
424 p_token2 IN VARCHAR2 DEFAULT NULL,
425 p_value2 IN VARCHAR2 DEFAULT NULL,
426 p_token3 IN VARCHAR2 DEFAULT NULL,
427 p_value3 IN VARCHAR2 DEFAULT NULL,
428 p_token4 IN VARCHAR2 DEFAULT NULL,
429 p_value4 IN VARCHAR2 DEFAULT NULL,
430 p_token5 IN VARCHAR2 DEFAULT NULL,
431 p_value5 IN VARCHAR2 DEFAULT NULL,
432 p_token6 IN VARCHAR2 DEFAULT NULL,
433 p_value6 IN VARCHAR2 DEFAULT NULL,
434 p_token7 IN VARCHAR2 DEFAULT NULL,
435 p_value7 IN VARCHAR2 DEFAULT NULL,
436 p_token8 IN VARCHAR2 DEFAULT NULL,
437 p_value8 IN VARCHAR2 DEFAULT NULL);
438 -- Harmonization Project I **heali
439
440 -- Overloaded the procedure to log messages to debug file as well.
441 PROCEDURE Add_Message ( p_message_type IN VARCHAR2,
442 p_module_name IN VARCHAR2
443 );
444 PROCEDURE Add_Message ( p_message_type IN VARCHAR2 := NULL
445 );
446
447
448 --
449 -- Name
450 -- Add_Summary_Message
451 -- Purpose
452 -- Adds a summary message.
453 --
454 -- Arguments
455 -- p_message Summary message, which should have tokens for
456 -- successes, warnings, and errors.
457 -- p_total Total number of entities processed.
458 -- p_warnings Number of entities with warnings.
459 -- p_errors Number of entities with errors.
460 -- p_return_status Return status derived from warnings and errors.
461 --
462
463 -- Overloaded the procedure to log messages to debug file as well.
464 PROCEDURE Add_Summary_Message(
465 p_message fnd_new_messages.message_name%type,
466 p_total number,
467 p_warnings number,
468 p_errors number,
469 p_return_status out NOCOPY varchar2,
470 p_module_name in varchar2
471 );
472 PROCEDURE Add_Summary_Message(
473 p_message fnd_new_messages.message_name%type,
474 p_total number,
475 p_warnings number,
476 p_errors number,
477 p_return_status out NOCOPY varchar2
478 );
479
480
481 --
482 -- Name
483 -- Get_Messages
484 -- Purpose
485 -- Gets messages from the FND_MSG_PUB table, in the form of a
486 -- concatenated string with separators.
487 -- Arguments
488 -- p_init_msg_list - initializes the FND_MSG_PUB table
489 -- x_summary - summary message (topmost)
490 -- x_details - concatenated messages (excluding summary)
491 -- x_count - total messages (including summary)
492 --
493
494 PROCEDURE Get_Messages ( p_init_msg_list IN VARCHAR2,
495 x_summary OUT NOCOPY VARCHAR2,
496 x_details OUT NOCOPY VARCHAR2,
497 x_count OUT NOCOPY NUMBER);
498
499 --
500 -- Name
501 -- Default_Handler
502 -- Purpose
503 -- Sets message for an unexpected error in a procedure
504 -- Arguments
505 -- p_routine_name - name of package.procedure where failure occurs
506 --
507
508 -- Overloaded the procedure to log messages to debug file as well.
509 PROCEDURE Default_Handler ( p_routine_name IN VARCHAR2,
510 p_module_name IN VARCHAR2
511 );
512 PROCEDURE Default_Handler ( p_routine_name IN VARCHAR2
513 );
514
515 --
516 -- Name
517 -- Clear_FND_Messages
518 -- Purpose
519 -- Clears the server-side FND message stack.
520 -- Arguments
521 -- None.
522 --
523
524 PROCEDURE Clear_FND_Messages;
525
526 --
527 -- Function: Get_Org_Name
528 -- Parameters: p_organization_id
529 -- Description: This procedure will return Organization Name for a Org Id
530 --
531
532 FUNCTION Get_Org_Name
533 (p_organization_id IN NUMBER
534 ) RETURN VARCHAR2;
535
536
537 --
538 -- Function: Get_Item_Name
539 -- Parameters: p_item_id, p_organization_id, p_flex_code, p_struct_num
540 -- Description: This procedure will return Item Name for a Item Id
541 --
542 -- LSP PROJECT : Added new parameter p_remove_client_code which specify
543 -- whether client code value from item name should be removed or not.
547 -- This parameter is required as the this API is being called from
544 -- Parameter value 'Y' means remove and 'N' means not.
545 -- This parameter value is being considered only when the deployment mode
546 -- is LSP.
548 -- many reports out of which some needs client code some or not.
549
550 FUNCTION Get_Item_Name
551 (p_item_id IN NUMBER,
552 p_organization_id IN NUMBER,
553 p_flex_code IN VARCHAR2 := 'MSTK',
554 p_struct_num IN NUMBER := 101,
555 p_remove_client_code IN VARCHAR2 DEFAULT 'N'
556 ) RETURN VARCHAR2;
557
558
559 -- Name generic_flex_name
560 -- Purpose converts entity_id into its name
561 -- Arguments
562 -- entity_id
563 -- warehouse_id
564 -- app_name (short app name; e.g. 'INV')
565 -- k_flex_code (key flexfield code; e.g., 'MSTK')
566 -- struct_num (structure number; e.g., 101)
567 -- Assumption The parameters are valid.
568 -- RETURN VARCHAR2 if name not found, '?' will be returned.
569
570
571 FUNCTION generic_flex_name
572 (entity_id IN NUMBER,
573 warehouse_id IN NUMBER,
574 app_name IN VARCHAR2,
575 k_flex_code IN VARCHAR2,
576 struct_num IN NUMBER)
577 RETURN VARCHAR2;
578
579
580
581 --
582 -- Procedure: Delete
583 --
584 -- Parameters: p_type - type of entities to delete
585 -- p_del_rows - ids to be deleted
586 -- When returned, id is negated if the delete failed
587 -- x_return_status - status of procedure call
588 --
589 -- Description: Deletes multiple entities
590 --
591
592 PROCEDURE Delete(
593 p_type wsh_saved_queries_vl.entity_type%type,
594 p_rows IN OUT NOCOPY wsh_util_core.id_tab_type,
595 p_caller IN VARCHAR2 DEFAULT NULL,
596 x_return_status OUT NOCOPY VARCHAR2);
597
598
599 -- Name city_region_postal
600 -- Purpose concatenates the three fields for the reports
601 -- Input Arguments
602 -- p_city
603 -- p_region (state)
604 -- p_postal_code (zip)
605 -- RETURN VARCHAR2
606 --
607
608 FUNCTION city_region_postal(
609 p_city in varchar2,
610 p_region in varchar2,
611 p_postal_code in varchar2)
612 RETURN VARCHAR2;
613
614 -- Name derive_shipment_priority
615 -- Purpose returns the shipment priority code
616 -- Input Arguments
617 -- p_delivery_id
618 -- RETURN VARCHAR2
619 --
620
621 FUNCTION derive_shipment_priority(p_delivery_id IN NUMBER)
622 RETURN VARCHAR2;
623
624
625 -- changes / additional function added due to Bug: 2120604
626 -- Name Get_Ledger_id_func_currency
627 -- Purpose Gets the Ledger id, by passing the Org id
628 -- And also get the Functional Currency corresponding to this Ledger_id
629 -- Input Arguments
630 -- p_org_id (Org Id)
631 --
632 PROCEDURE Get_Ledger_id_Func_Currency(
633 p_org_id IN NUMBER,
634 x_ledger_id OUT NOCOPY NUMBER ,
635 x_func_currency OUT NOCOPY VARCHAR2 ,
636 x_return_status OUT NOCOPY VARCHAR2);
637
638
639 --
640 -- Name Print_Label
641 -- Purpose This procedure takes a table of delivery ids or
642 -- trip stop ids and print label for these deliveries
643 --
644 -- Input Arguments
645 -- p_delivery
646 --
647 PROCEDURE Print_Label(
648 p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
649 p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
650 x_return_status OUT NOCOPY VARCHAR2);
651
652 /* H integration: Pricing integration csun
653 */
654 --
655 -- Name FTE_Is_Installed
656 -- Purpose This procedure check whether FTE is installed.
657 -- It returns 'Y' if FTE is installed, 'N' if FTE
658 -- is not installed
659 --
660 -- Input Arguments
661 -- No input argument
662 --
663 FUNCTION FTE_Is_Installed return VARCHAR2;
664
665 --
666 -- Name TP_Is_Installed
667 -- Purpose This procedure check whether TP is installed.
668 -- It returns 'Y' if TP is installed, 'N' if TP
669 -- is not installed
670 --
671 -- Input Arguments
672 -- No input argument
673 --
674 FUNCTION TP_Is_Installed RETURN VARCHAR2;
675
676 --
677 -- Name Get_Trip_Name
678 -- Purpose This procedure gets the trip name from a delivery leg id
679 -- Input Arguments
680 -- delivery_leg_id
681 --
682 PROCEDURE Get_Trip_Name_by_Leg(
683 p_delivery_leg_id IN NUMBER,
684 x_trip_name OUT NOCOPY VARCHAR2,
685 x_reprice_required OUT NOCOPY VARCHAR2,
686 x_return_status OUT NOCOPY VARCHAR2);
687
688 --Harmonization Project I
689 PROCEDURE api_post_call(
690 p_return_status IN VARCHAR2,
691 x_num_warnings IN OUT NOCOPY NUMBER,
692 x_num_errors IN OUT NOCOPY NUMBER,
693 p_msg_data IN VARCHAR2 DEFAULT NULL,
694 p_raise_error_flag IN BOOLEAN DEFAULT TRUE
695 );
696
697 --Harmonization Project I **heali
698 PROCEDURE api_post_call(
699 p_return_status IN VARCHAR2,
703 p_msg_data IN VARCHAR2,
700 x_num_warnings IN OUT NOCOPY NUMBER,
701 x_num_errors IN OUT NOCOPY NUMBER,
702 p_module_name IN VARCHAR2,
704 p_token1 IN VARCHAR2 DEFAULT NULL,
705 p_value1 IN VARCHAR2 DEFAULT NULL,
706 p_token2 IN VARCHAR2 DEFAULT NULL,
707 p_value2 IN VARCHAR2 DEFAULT NULL,
708 p_token3 IN VARCHAR2 DEFAULT NULL,
709 p_value3 IN VARCHAR2 DEFAULT NULL,
710 p_token4 IN VARCHAR2 DEFAULT NULL,
711 p_value4 IN VARCHAR2 DEFAULT NULL,
712 p_token5 IN VARCHAR2 DEFAULT NULL,
713 p_value5 IN VARCHAR2 DEFAULT NULL,
714 p_token6 IN VARCHAR2 DEFAULT NULL,
715 p_value6 IN VARCHAR2 DEFAULT NULL,
716 p_token7 IN VARCHAR2 DEFAULT NULL,
717 p_value7 IN VARCHAR2 DEFAULT NULL,
718 p_token8 IN VARCHAR2 DEFAULT NULL,
719 p_value8 IN VARCHAR2 DEFAULT NULL,
720 p_raise_error_flag IN BOOLEAN DEFAULT TRUE );
721 --Harmonization Project I **heali
722
723 FUNCTION get_operatingUnit_id ( p_delivery_id IN NUMBER )
724 RETURN NUMBER;
725
726 --
727 -- Name Store_Msg_In_Table
728 -- Purpose This procedure takes a table of messages and push
729 -- them to the FND stack and also returns number of errors,
730 -- warns, unexpected errors, and successes.
731 --
732 -- Input Arguments
733 -- p_store_flag
734 --
735 PROCEDURE Store_Msg_In_Table (
736 p_store_flag IN Boolean,
737 x_msg_rec_count OUT NOCOPY WSH_UTIL_CORE.MsgCountType,
738 x_return_status OUT NOCOPY VARCHAR2);
739
740
741 --========================================================================
742 -- PROCEDURE : get_cached_value
743 --
744 -- PARAMETERS: p_cache_tbl this table is used to hold the cache
745 -- values, which key is less than 2^31
746 -- p_cache_ext_tbl This table is used to hold the cache
747 -- values, which key is more then 2^31
748 -- p_value This the value to be either inserted
749 -- or reterived from the cache.
750 -- p_key This is the key that we use to access
751 -- the cache table.
752 -- p_action if 'PUT' is passed, then the p_value
753 -- is put into the cache. If 'GET'is passed
754 -- then the value will be retrieved from
755 -- cache.
756 -- x_return_status return status
757 --
758 -- COMMENT : This table will manage a cache (storing integer values)
759 -- IF value 'PUT' is passed to p_action, then p_value will be set
760 -- into the cache, where p_key is used to access the cache table.
761 -- IF value 'GET' is passed to p_action, then the information
762 -- on the cache is retrieved. The p_key is used to access the
763 -- cache table.
764 -- If the get operation is a miss, then a warning will be
765 -- returned.
766 --========================================================================
767
768 PROCEDURE get_cached_value(
769 p_cache_tbl IN OUT NOCOPY key_value_tab_type,
770 p_cache_ext_tbl IN OUT NOCOPY key_value_tab_type,
771 p_value IN OUT NOCOPY NUMBER,
772 p_key IN NUMBER,
773 p_action IN VARCHAR2,
774 x_return_status OUT NOCOPY VARCHAR2
775 ) ;
776
777
778 --========================================================================
779 -- PROCEDURE : get_cached_value
780 --
781 -- PARAMETERS: p_cache_tbl this table is used to hold the cache
782 -- values, which key is less than 2^31
783 -- p_cache_ext_tbl This table is used to hold the cache
784 -- values, which key is more then 2^31
785 -- p_value This the value to be either inserted
786 -- or reterived from the cache.
787 -- p_key This is the key that we use to access
788 -- the cache table.
789 -- p_action if 'PUT' is passed, then the p_value
790 -- is put into the cache. If 'GET'is passed
791 -- then the value will be retrieved from
792 -- cache.
793 -- x_return_status return status
794 --
795 -- COMMENT : This table will manage a cache (storing varchar2(500))
796 -- IF value 'PUT' is passed to p_action, then p_value will be set
797 -- into the cache, where p_key is used to access the cache table.
798 -- IF value 'GET' is passed to p_action, then the information
799 -- on the cache is retrieved. The p_key is used to access the
800 -- cache table.
801 -- If the get operation is a miss, then a warning will be
802 -- returned.
803 --========================================================================
804
805 PROCEDURE get_cached_value(
806 p_cache_tbl IN OUT NOCOPY char500_tab_type,
807 p_cache_ext_tbl IN OUT NOCOPY char500_tab_type,
811 x_return_status OUT NOCOPY VARCHAR2
808 p_value IN OUT NOCOPY VARCHAR2,
809 p_key IN NUMBER,
810 p_action IN VARCHAR2,
812 ) ;
813
814 --========================================================================
815 -- PROCEDURE : get_cached_value
816 --
817 -- PARAMETERS: p_cache_tbl this table is used to hold the cache
818 -- values, which key is less than 2^31
819 -- p_cache_ext_tbl This table is used to hold the cache
820 -- values, which key is more then 2^31
821 -- p_value This the value to be either inserted
822 -- or reterived from the cache.
823 -- p_key This is the key that we use to access
824 -- the cache table.
825 -- p_action if 'PUT' is passed, then the p_value
826 -- is put into the cache. If 'GET'is passed
827 -- then the value will be retrieved from
828 -- cache.
829 -- x_return_status return status
830 --
831 -- COMMENT : This table will manage a cache (storing boolean)
832 -- IF value 'PUT' is passed to p_action, then p_value will be set
833 -- into the cache, where p_key is used to access the cache table.
834 -- IF value 'GET' is passed to p_action, then the information
835 -- on the cache is retrieved. The p_key is used to access the
836 -- cache table.
837 -- If the get operation is a miss, then a warning will be
838 -- returned.
839 --========================================================================
840
841 PROCEDURE get_cached_value(
842 p_cache_tbl IN OUT NOCOPY boolean_tab_type,
843 p_cache_ext_tbl IN OUT NOCOPY boolean_tab_type,
844 p_value IN OUT NOCOPY boolean,
845 p_key IN NUMBER,
846 p_action IN VARCHAR2,
847 x_return_status OUT NOCOPY VARCHAR2
848 ) ;
849
850
851 --========================================================================
852 -- PROCEDURE : OpenDynamicCursor
853 --
854 -- PARAMETERS: p_cursor Ref cursor to the SQL query
855 -- p_statement SQL statement to be executed
856 -- p_dynamic_tab Table containing bind variables.
857 --
858 --========================================================================
859 PROCEDURE OpenDynamicCursor(
860 p_cursor IN OUT NOCOPY RefCurType,
861 p_statement IN VARCHAR2,
862 p_dynamic_tab IN tbl_varchar);
863
864
865
866 -- Start of comments
867 -- API name : Get_Lookup_Meaning
868 -- Type : Public
869 -- Pre-reqs : None.
870 -- Function : API to get meaning for lookup code and type.
871 -- Parameters :
872 -- IN:
873 -- p_lookup_type IN Lookup Type.
874 -- P_lookup_code IN Lookup Code.
875 -- OUT:
876 -- Api return meaning for lookup code and type.
877 -- End of comments
878 FUNCTION Get_Lookup_Meaning(p_lookup_type IN VARCHAR2,
879 P_lookup_code IN VARCHAR2)
880 return VARCHAR2;
881
882
883 -- Start of comments
884 -- API name : Get_Action_Meaning
885 -- Type : Public
886 -- Pre-reqs : None.
887 -- Function : API to get meaning for action code and type.
888 -- Parameters :
889 -- IN:
890 -- p_entity IN Entity DLVB/DLVY/STOP/TRIP.
891 -- P_action_code IN Action Code.
892 -- OUT:
893 -- Api return meaning for lookup code and type.
894 -- End of comments
895
896 FUNCTION Get_Action_Meaning(p_entity IN VARCHAR2,
897 p_action_code IN VARCHAR2)
898 return VARCHAR2;
899
900
901 --Sbakshi
902 --
903 -- Procedure : Get_string_from_idtab
904 -- Purpose : Used to convert a PL/SQL table of numbers to comma-separated list of form '1,2,3,4'
905 --
906
907 PROCEDURE get_string_from_idtab(
908 p_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
909 x_string OUT NOCOPY VARCHAR2,
910 x_return_status OUT NOCOPY VARCHAR2);
911
912 --
913 --Procedure : Get_idtab_from_string
914 --Purpose : Is used to Convert a comma-separated list of Ids of form '1,2,3,4'to
915 -- a PL/SQL table numbers;
916
917 PROCEDURE get_idtab_from_string(
918 p_string IN VARCHAR2,
919 x_id_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
920 x_return_status OUT NOCOPY VARCHAR2);
921
922 -- Bug#3947506: Adding a new procedure Get_Entity_name
923 --========================================================================
924 -- PROCEDURE : Get_entity_name
925 --
926 -- COMMENT : This procedure will return the entity name for Trip, Stop,
927 -- Delivery. For Line, Line_id will be returned.
928 --========================================================================
929
930 PROCEDURE Get_Entity_name
931 (p_in_entity_id in NUMBER,
932 p_in_entity_name in VARCHAR2,
933 p_out_entity_id out NOCOPY VARCHAR2,
934 p_out_entity_name out NOCOPY VARCHAR2,
935 p_return_status out NOCOPY VARCHAR2);
936
937
938 --Bug 4070732 : The following two prcoedures added for this Bugfix
939
943 -- COMMENT : This procedure will call the WSH_TRIPS_ACTIONS.Fte_Load_Tender
940 --========================================================================
941 -- PROCEDURE : Process_stops_for_load_tender
942 --
944 -- for the Stop ID's present in the global cache table
945 -- G_STOP_IDS_STOP_IDS_CACHE and G_STOP_IDS_STOP_IDS_EXT_CACHE.
946 -- Once processed, this will call the API Reset_stops_for_load_tender
947 -- to reset the global variables.
948 --========================================================================
949
950
951 PROCEDURE Process_stops_for_load_tender(p_reset_flags IN BOOLEAN,x_return_status OUT NOCOPY VARCHAR2);
952
953
954 --========================================================================
955 -- PROCEDURE : Reset_stops_for_load_tender
956 --
957 -- COMMENT : This procedure will delete the contents of the gloabal cache
958 -- tables G_STOP_IDS_STOP_IDS_CACHE and G_STOP_IDS_STOP_IDS_EXT_CACHE
959 -- and also set the Boolean Global Variable G_CALL_FTE_LOAD_TENDER_API
960 -- to TRUE.
961 --========================================================================
962
963 PROCEDURE Reset_stops_for_load_tender (p_reset_flags IN BOOLEAN,x_return_status OUT NOCOPY VARCHAR2);
964
965
966 /*======================================================================
967 FUNCTION : ValidateActualDepartureDate
968
969 COMMENT : This function is called from
970 - WSHASCSRS concurrent program
971 - WSHPSRS concurrent program
972 - WSHPRREL library
973
974 This function checks whether users can enter a future date for
975 the Actual Departure Date parameter/field.
976 This function returns
977 - FALSE : if Global parameter Allow Future Ship Date = 'N'
978 and Ship Confirm Rule indicates Set Delivery Intransit
979 and Actual Departure Date is > SYSDATE
980 - TRUE : under all other conditions
981
982 HISTORY : rlanka 03/01/2005 Created
983 =======================================================================*/
984 FUNCTION ValidateActualDepartureDate(p_ship_confirm_rule_id IN NUMBER,
985 p_actual_departure_date IN DATE)
986 RETURN BOOLEAN;
987
988
989 /*======================================================================
990 FUNCTION : GetShipConfirmRule
991
992 COMMENT : This function is called from the
993 - WSHPSRS concurrent program
994
995 This function is used to obtain the Ship Confirm Rule tied
996 to a particular picking rule. This function is used to
997 populate a hidden parameter "Ship Confirm Rule ID" in WSHPSRS
998 concurrent program.
999
1000 HISTORY : rlanka 03/01/2005 Created
1001 =======================================================================*/
1002 FUNCTION GetShipConfirmRule(p_picking_rule_id IN NUMBER) RETURN NUMBER;
1003
1004 --
1005 -- Name WMS_Is_Installed
1006 -- Purpose This procedure check whether WMS is installed.
1007 -- It returns 'Y' if WMS is installed, 'N' if WMS
1008 -- is not installed
1009 --
1010 -- Input Arguments
1011 -- No input argument
1012 --
1013 FUNCTION WMS_Is_Installed return VARCHAR2;
1014
1015 --***************************************************************************--
1016 --========================================================================
1017 -- PROCEDURE : get_customer_from_loc PRIVATE
1018 --
1019 -- PARAMETERS: p_location_id Input Location id
1020 -- x_customer_id_tab List of customers at the input location
1021 -- x_return_status Return status
1022 -- COMMENT :
1023 -- Returns the customer id of the customer
1024 -- having a location at input wsh location id
1025 --========================================================================
1026
1027 PROCEDURE get_customer_from_loc(
1028 p_location_id IN NUMBER,
1029 x_customer_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1030 x_return_status OUT NOCOPY VARCHAR2);
1031
1032 --***************************************************************************--
1033 --========================================================================
1034 -- PROCEDURE : get_org_from_location PRIVATE
1035 --
1036 -- PARAMETERS: p_location_id Input Location id
1037 -- x_organization_tab List of Organizations for the input location
1038 -- x_return_status Return status
1039 -- COMMENT :
1040 -- Returns table of organizations for location.
1041 --========================================================================
1042 PROCEDURE get_org_from_location(
1043 p_location_id IN NUMBER,
1044 x_organization_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1045 x_return_status OUT NOCOPY VARCHAR2);
1046 --***************************************************************************--
1047 --========================================================================
1048 -- PROCEDURE : Get_Delivery_Status PRIVATE
1049 --
1050 -- PARAMETERS:
1051 -- p_entity_type either DELIVERY/DELIVERY DETAIL/LPN
1052 -- p_entity_id either delivery_id/delivery_detail_id/lpn_id
1053 -- x_status_code Status of delivery for the entity_type and
1054 -- entity id passed
1055 -- x_return_status return status
1056 --========================================================================
1057 -- API added for bug 4632726
1058 PROCEDURE Get_Delivery_Status (
1059 p_entity_type IN VARCHAR2,
1063
1060 p_entity_id IN NUMBER,
1061 x_status_code OUT NOCOPY VARCHAR2,
1062 x_return_status OUT NOCOPY VARCHAR2 );
1064 -- OTM R12
1065 --***************************************************************************--
1066 --
1067 -- Name Get_Otm_Install_Profile_Value
1068 -- Purpose This function returns the value of
1069 -- profile WSH_OTM_INSTALLED
1070 -- It returns 'P' if OTM is integrated for Inbound Purchasing
1071 -- 'O' if OTM is integrated for Outbound Sales Order
1072 -- 'Y' if OTM is integrated for both of the above
1073 -- 'N' if OTM is integrated for non of the above
1074 -- or if the profile value is NULL
1075 --
1076 -- Input Arguments
1077 -- No input argument
1078 --
1079 --***************************************************************************--
1080
1081 FUNCTION Get_Otm_Install_Profile_Value return VARCHAR2;
1082
1083 --***************************************************************************--
1084 --
1085 -- Name GC3_Is_Installed
1086 -- Purpose This function returns whether OTM is integrated for
1087 -- Outbound Sales Order flow by looking at the
1088 -- value of profile WSH_OTM_INSTALLED
1089 -- It returns 'Y' if OTM is integrated for Outbound Sales Order
1090 -- 'N' otherwise
1091 --
1092 -- Input Arguments
1093 -- No input argument
1094 --
1095 --***************************************************************************--
1096
1097 FUNCTION GC3_Is_Installed return VARCHAR2;
1098
1099 --
1100 --========================================================================
1101 -- PROCEDURE : GET_CURRENCY_CONVERSION_TYPE
1102 --
1103 -- API added for R12 Glog Integration Currency Conversion ECO
1104 --
1105 -- PURPOSE : To get the value for profile option WSH_OTM_CURR_CONV_TYPE
1106 -- (WSH: Currency Conversion Type for OTM)
1107 -- It returns the cached value if it is avaiable, otherwise
1108 -- fnd_profile.value api is called to get the profile value
1109 -- PARAMETERS:
1110 -- x_curr_conv_type currency conversion type
1111 -- x_return_status return status
1112 --========================================================================
1113 PROCEDURE Get_Currency_Conversion_Type (
1114 x_curr_conv_type OUT NOCOPY VARCHAR2,
1115 x_return_status OUT NOCOPY VARCHAR2 );
1116
1117 /*
1118 --========================================================================
1119 -- FUNCTION : get_trip_organization
1120 --
1121 -- PARAMETERS: p_trip_id Input Trip Id
1122 --
1123 -- COMMENT :
1124 -- This procedure returns back organiation id that is associated with the trip.
1125 -- Steps
1126 -- For Outbound and Mixed trip's see if there is a organization at the location of first stop
1127 -- For inbound see if there is a organization at the location of the last stop.
1128 -- If there are no organizations associated then get the organization id of the delivery with
1129 -- least delivery id
1130 --========================================================================
1131 */
1132
1133 FUNCTION GET_TRIP_ORGANIZATION_ID(p_trip_id NUMBER) RETURN NUMBER;
1134
1135 /*
1136 --========================================================================
1137 -- PROCEDURE : GET_FIRST_LAST_STOP_INFO
1138 --
1139 -- PARAMETERS: p_trip_id Input Trip id
1140 -- x_arrival_date Last Stop's Arrival Date
1141 -- x_departure_date First Stop's Departure Date
1142 -- x_first_stop_id First Stop Id
1143 -- x_last_stop_id Last Stop Id
1144 -- x_first_stop_loc_id First Stop Location Id
1145 -- x_last_stop_loc_id Last Stop Location Id
1146 -- x_return_status Return status
1147 -- COMMENT :
1148 -- Returns the first and the last Stop's information of a trip
1149 --========================================================================
1150 */
1151
1152 PROCEDURE GET_FIRST_LAST_STOP_INFO(x_return_status OUT NOCOPY VARCHAR2,
1153 x_arrival_date OUT NOCOPY DATE,
1154 x_departure_date OUT NOCOPY DATE,
1155 x_first_stop_id OUT NOCOPY NUMBER,
1156 x_last_stop_id OUT NOCOPY NUMBER,
1157 x_first_stop_loc_id OUT NOCOPY NUMBER,
1158 x_last_stop_loc_id OUT NOCOPY NUMBER,
1159 p_trip_id NUMBER);
1160
1161
1162 -- End of OTM R12
1163
1164 -- Standalone Project - Start
1165 --
1166 --=============================================================================
1167 -- PUBLIC FUNCTION :
1168 -- Get_Operating_Unit
1169 --
1170 -- PARAMETERS:
1171 -- p_organization_id => Organization Id
1172 --
1173 -- COMMENT:
1174 -- Function to return Operating Unit corresponding to organization passed.
1175 -- HISTORY :
1176 -- ueshanka 10/Feb/2009 Created
1177 --=============================================================================
1178 --
1179 FUNCTION Get_Operating_Unit( p_organization_id NUMBER) RETURN NUMBER;
1180 --
1181 -- Standalone Project - End
1182
1183 --Added for bug 9011125
1184 --========================================================================
1185 -- PROCEDURE : SET_FND_PROFILE
1189 -- COMMENT :
1186 --
1187 -- PARAMETERS: p_name - Name of the profile to be set.
1188 -- p_value - Value for the profile to be set.
1190 -- This will set the FND_PROFILE for the DB cache.
1191 --========================================================================
1192
1193 PROCEDURE SET_FND_PROFILE(p_name IN VARCHAR2,
1194 p_value IN VARCHAR2);
1195
1196 -- muom
1197 --***************************************************************************--
1198 --
1199 -- Name Get_Line_Fulfillment_Base
1200 -- Purpose: Wrapper api, on top of OM api, to return fulfillment base
1201 -- It returns 'P', if fulfillment is by Primary
1202 -- 'S', if fulfillment is by Secondary
1203 -- Arguments: p_source_code - Source Code
1204 -- p_source_line_id - Source Line Id
1205 --
1206 --***************************************************************************--
1207
1208 FUNCTION Get_Line_Fulfillment_Base(p_source_code IN varchar2,
1209 p_source_line_id IN number) RETURN VARCHAR2;
1210
1211 END WSH_UTIL_CORE;
[Home] [Help]