1 PACKAGE WSH_UTIL_CORE as
2 /* $Header: WSHUTCOS.pls 120.6.12010000.1 2008/07/29 06:20:23 appldev ship $ */
3
4 --
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 --
99 G_EXC_WARNING EXCEPTION;
100 --
101 C_HASH_BASE CONSTANT NUMBER := 1;
102 C_HASH_SIZE CONSTANT NUMBER := 33554432 ; -- power(2, 25)
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
247 FUNCTION Ship_Method_To_Freight (
248 p_ship_method_code IN VARCHAR2,
249 p_organization_id IN NUMBER
250 ) RETURN VARCHAR2;
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,
396 X_DATE_FETCHED OUT NOCOPY DATE);
397
398 PROCEDURE Get_Active_Date( query_text IN VARCHAR2,
399 date_fetched OUT NOCOPY DATE);
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 --
530 --
527 -- Function: Get_Org_Name
528 -- Parameters: p_organization_id
529 -- Description: This procedure will return Organization Name for a Org Id
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
543 FUNCTION Get_Item_Name
544 (p_item_id IN NUMBER,
545 p_organization_id IN NUMBER,
546 p_flex_code IN VARCHAR2 := 'MSTK',
547 p_struct_num IN NUMBER := 101
548 ) RETURN VARCHAR2;
549
550
551 -- Name generic_flex_name
552 -- Purpose converts entity_id into its name
553 -- Arguments
554 -- entity_id
555 -- warehouse_id
556 -- app_name (short app name; e.g. 'INV')
557 -- k_flex_code (key flexfield code; e.g., 'MSTK')
558 -- struct_num (structure number; e.g., 101)
559 -- Assumption The parameters are valid.
560 -- RETURN VARCHAR2 if name not found, '?' will be returned.
561
562
563 FUNCTION generic_flex_name
564 (entity_id IN NUMBER,
565 warehouse_id IN NUMBER,
566 app_name IN VARCHAR2,
567 k_flex_code IN VARCHAR2,
568 struct_num IN NUMBER)
569 RETURN VARCHAR2;
570
571
572
573 --
574 -- Procedure: Delete
575 --
576 -- Parameters: p_type - type of entities to delete
577 -- p_del_rows - ids to be deleted
578 -- When returned, id is negated if the delete failed
579 -- x_return_status - status of procedure call
580 --
581 -- Description: Deletes multiple entities
582 --
583
584 PROCEDURE Delete(
585 p_type wsh_saved_queries_vl.entity_type%type,
586 p_rows IN OUT NOCOPY wsh_util_core.id_tab_type,
587 p_caller IN VARCHAR2 DEFAULT NULL,
588 x_return_status OUT NOCOPY VARCHAR2);
589
590
591 -- Name city_region_postal
592 -- Purpose concatenates the three fields for the reports
593 -- Input Arguments
594 -- p_city
595 -- p_region (state)
596 -- p_postal_code (zip)
597 -- RETURN VARCHAR2
598 --
599
600 FUNCTION city_region_postal(
601 p_city in varchar2,
602 p_region in varchar2,
603 p_postal_code in varchar2)
604 RETURN VARCHAR2;
605
606 -- Name derive_shipment_priority
607 -- Purpose returns the shipment priority code
608 -- Input Arguments
609 -- p_delivery_id
610 -- RETURN VARCHAR2
611 --
612
613 FUNCTION derive_shipment_priority(p_delivery_id IN NUMBER)
614 RETURN VARCHAR2;
615
616
617 -- changes / additional function added due to Bug: 2120604
618 -- Name Get_Ledger_id_func_currency
619 -- Purpose Gets the Ledger id, by passing the Org id
620 -- And also get the Functional Currency corresponding to this Ledger_id
621 -- Input Arguments
622 -- p_org_id (Org Id)
623 --
624 PROCEDURE Get_Ledger_id_Func_Currency(
625 p_org_id IN NUMBER,
626 x_ledger_id OUT NOCOPY NUMBER ,
627 x_func_currency OUT NOCOPY VARCHAR2 ,
628 x_return_status OUT NOCOPY VARCHAR2);
629
630
631 --
632 -- Name Print_Label
633 -- Purpose This procedure takes a table of delivery ids or
634 -- trip stop ids and print label for these deliveries
635 --
636 -- Input Arguments
637 -- p_delivery
638 --
639 PROCEDURE Print_Label(
640 p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
641 p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
642 x_return_status OUT NOCOPY VARCHAR2);
643
644 /* H integration: Pricing integration csun
645 */
646 --
647 -- Name FTE_Is_Installed
648 -- Purpose This procedure check whether FTE is installed.
649 -- It returns 'Y' if FTE is installed, 'N' if FTE
650 -- is not installed
651 --
652 -- Input Arguments
653 -- No input argument
654 --
655 FUNCTION FTE_Is_Installed return VARCHAR2;
656
657 --
658 -- Name TP_Is_Installed
659 -- Purpose This procedure check whether TP is installed.
660 -- It returns 'Y' if TP is installed, 'N' if TP
661 -- is not installed
662 --
663 -- Input Arguments
664 -- No input argument
665 --
666 FUNCTION TP_Is_Installed RETURN VARCHAR2;
667
668 --
669 -- Name Get_Trip_Name
670 -- Purpose This procedure gets the trip name from a delivery leg id
671 -- Input Arguments
672 -- delivery_leg_id
673 --
674 PROCEDURE Get_Trip_Name_by_Leg(
675 p_delivery_leg_id IN NUMBER,
676 x_trip_name OUT NOCOPY VARCHAR2,
677 x_reprice_required OUT NOCOPY VARCHAR2,
678 x_return_status OUT NOCOPY VARCHAR2);
679
680 --Harmonization Project I
681 PROCEDURE api_post_call(
682 p_return_status IN VARCHAR2,
686 p_raise_error_flag IN BOOLEAN DEFAULT TRUE
683 x_num_warnings IN OUT NOCOPY NUMBER,
684 x_num_errors IN OUT NOCOPY NUMBER,
685 p_msg_data IN VARCHAR2 DEFAULT NULL,
687 );
688
689 --Harmonization Project I **heali
690 PROCEDURE api_post_call(
691 p_return_status IN VARCHAR2,
692 x_num_warnings IN OUT NOCOPY NUMBER,
693 x_num_errors IN OUT NOCOPY NUMBER,
694 p_module_name IN VARCHAR2,
695 p_msg_data IN VARCHAR2,
696 p_token1 IN VARCHAR2 DEFAULT NULL,
697 p_value1 IN VARCHAR2 DEFAULT NULL,
698 p_token2 IN VARCHAR2 DEFAULT NULL,
699 p_value2 IN VARCHAR2 DEFAULT NULL,
700 p_token3 IN VARCHAR2 DEFAULT NULL,
701 p_value3 IN VARCHAR2 DEFAULT NULL,
702 p_token4 IN VARCHAR2 DEFAULT NULL,
703 p_value4 IN VARCHAR2 DEFAULT NULL,
704 p_token5 IN VARCHAR2 DEFAULT NULL,
705 p_value5 IN VARCHAR2 DEFAULT NULL,
706 p_token6 IN VARCHAR2 DEFAULT NULL,
707 p_value6 IN VARCHAR2 DEFAULT NULL,
708 p_token7 IN VARCHAR2 DEFAULT NULL,
709 p_value7 IN VARCHAR2 DEFAULT NULL,
710 p_token8 IN VARCHAR2 DEFAULT NULL,
711 p_value8 IN VARCHAR2 DEFAULT NULL,
712 p_raise_error_flag IN BOOLEAN DEFAULT TRUE );
713 --Harmonization Project I **heali
714
715 FUNCTION get_operatingUnit_id ( p_delivery_id IN NUMBER )
716 RETURN NUMBER;
717
718 --
719 -- Name Store_Msg_In_Table
720 -- Purpose This procedure takes a table of messages and push
721 -- them to the FND stack and also returns number of errors,
722 -- warns, unexpected errors, and successes.
723 --
724 -- Input Arguments
725 -- p_store_flag
726 --
727 PROCEDURE Store_Msg_In_Table (
728 p_store_flag IN Boolean,
729 x_msg_rec_count OUT NOCOPY WSH_UTIL_CORE.MsgCountType,
730 x_return_status OUT NOCOPY VARCHAR2);
731
732
733 --========================================================================
734 -- PROCEDURE : get_cached_value
735 --
736 -- PARAMETERS: p_cache_tbl this table is used to hold the cache
737 -- values, which key is less than 2^31
738 -- p_cache_ext_tbl This table is used to hold the cache
739 -- values, which key is more then 2^31
740 -- p_value This the value to be either inserted
741 -- or reterived from the cache.
742 -- p_key This is the key that we use to access
743 -- the cache table.
744 -- p_action if 'PUT' is passed, then the p_value
745 -- is put into the cache. If 'GET'is passed
746 -- then the value will be retrieved from
747 -- cache.
748 -- x_return_status return status
749 --
750 -- COMMENT : This table will manage a cache (storing integer values)
751 -- IF value 'PUT' is passed to p_action, then p_value will be set
752 -- into the cache, where p_key is used to access the cache table.
753 -- IF value 'GET' is passed to p_action, then the information
754 -- on the cache is retrieved. The p_key is used to access the
755 -- cache table.
756 -- If the get operation is a miss, then a warning will be
757 -- returned.
758 --========================================================================
759
760 PROCEDURE get_cached_value(
761 p_cache_tbl IN OUT NOCOPY key_value_tab_type,
762 p_cache_ext_tbl IN OUT NOCOPY key_value_tab_type,
763 p_value IN OUT NOCOPY NUMBER,
764 p_key IN NUMBER,
765 p_action IN VARCHAR2,
766 x_return_status OUT NOCOPY VARCHAR2
767 ) ;
768
769
770 --========================================================================
771 -- PROCEDURE : get_cached_value
772 --
773 -- PARAMETERS: p_cache_tbl this table is used to hold the cache
774 -- values, which key is less than 2^31
775 -- p_cache_ext_tbl This table is used to hold the cache
776 -- values, which key is more then 2^31
777 -- p_value This the value to be either inserted
778 -- or reterived from the cache.
779 -- p_key This is the key that we use to access
780 -- the cache table.
781 -- p_action if 'PUT' is passed, then the p_value
782 -- is put into the cache. If 'GET'is passed
783 -- then the value will be retrieved from
784 -- cache.
785 -- x_return_status return status
786 --
790 -- IF value 'GET' is passed to p_action, then the information
787 -- COMMENT : This table will manage a cache (storing varchar2(500))
788 -- IF value 'PUT' is passed to p_action, then p_value will be set
789 -- into the cache, where p_key is used to access the cache table.
791 -- on the cache is retrieved. The p_key is used to access the
792 -- cache table.
793 -- If the get operation is a miss, then a warning will be
794 -- returned.
795 --========================================================================
796
797 PROCEDURE get_cached_value(
798 p_cache_tbl IN OUT NOCOPY char500_tab_type,
799 p_cache_ext_tbl IN OUT NOCOPY char500_tab_type,
800 p_value IN OUT NOCOPY VARCHAR2,
801 p_key IN NUMBER,
802 p_action IN VARCHAR2,
803 x_return_status OUT NOCOPY VARCHAR2
804 ) ;
805
806 --========================================================================
807 -- PROCEDURE : get_cached_value
808 --
809 -- PARAMETERS: p_cache_tbl this table is used to hold the cache
810 -- values, which key is less than 2^31
811 -- p_cache_ext_tbl This table is used to hold the cache
812 -- values, which key is more then 2^31
813 -- p_value This the value to be either inserted
814 -- or reterived from the cache.
815 -- p_key This is the key that we use to access
816 -- the cache table.
817 -- p_action if 'PUT' is passed, then the p_value
818 -- is put into the cache. If 'GET'is passed
819 -- then the value will be retrieved from
820 -- cache.
821 -- x_return_status return status
822 --
823 -- COMMENT : This table will manage a cache (storing boolean)
824 -- IF value 'PUT' is passed to p_action, then p_value will be set
825 -- into the cache, where p_key is used to access the cache table.
826 -- IF value 'GET' is passed to p_action, then the information
827 -- on the cache is retrieved. The p_key is used to access the
828 -- cache table.
829 -- If the get operation is a miss, then a warning will be
830 -- returned.
831 --========================================================================
832
833 PROCEDURE get_cached_value(
834 p_cache_tbl IN OUT NOCOPY boolean_tab_type,
835 p_cache_ext_tbl IN OUT NOCOPY boolean_tab_type,
836 p_value IN OUT NOCOPY boolean,
837 p_key IN NUMBER,
838 p_action IN VARCHAR2,
839 x_return_status OUT NOCOPY VARCHAR2
840 ) ;
841
842
843 --========================================================================
844 -- PROCEDURE : OpenDynamicCursor
845 --
846 -- PARAMETERS: p_cursor Ref cursor to the SQL query
847 -- p_statement SQL statement to be executed
848 -- p_dynamic_tab Table containing bind variables.
849 --
850 --========================================================================
851 PROCEDURE OpenDynamicCursor(
852 p_cursor IN OUT NOCOPY RefCurType,
853 p_statement IN VARCHAR2,
854 p_dynamic_tab IN tbl_varchar);
855
856
857
858 -- Start of comments
859 -- API name : Get_Lookup_Meaning
860 -- Type : Public
861 -- Pre-reqs : None.
862 -- Function : API to get meaning for lookup code and type.
863 -- Parameters :
864 -- IN:
865 -- p_lookup_type IN Lookup Type.
866 -- P_lookup_code IN Lookup Code.
867 -- OUT:
868 -- Api return meaning for lookup code and type.
869 -- End of comments
870 FUNCTION Get_Lookup_Meaning(p_lookup_type IN VARCHAR2,
871 P_lookup_code IN VARCHAR2)
872 return VARCHAR2;
873
874
875 -- Start of comments
876 -- API name : Get_Action_Meaning
877 -- Type : Public
878 -- Pre-reqs : None.
879 -- Function : API to get meaning for action code and type.
880 -- Parameters :
881 -- IN:
882 -- p_entity IN Entity DLVB/DLVY/STOP/TRIP.
883 -- P_action_code IN Action Code.
884 -- OUT:
885 -- Api return meaning for lookup code and type.
886 -- End of comments
887
888 FUNCTION Get_Action_Meaning(p_entity IN VARCHAR2,
889 p_action_code IN VARCHAR2)
890 return VARCHAR2;
891
892
893 --Sbakshi
894 --
895 -- Procedure : Get_string_from_idtab
896 -- Purpose : Used to convert a PL/SQL table of numbers to comma-separated list of form '1,2,3,4'
897 --
898
899 PROCEDURE get_string_from_idtab(
900 p_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
901 x_string OUT NOCOPY VARCHAR2,
902 x_return_status OUT NOCOPY VARCHAR2);
903
904 --
905 --Procedure : Get_idtab_from_string
909 PROCEDURE get_idtab_from_string(
906 --Purpose : Is used to Convert a comma-separated list of Ids of form '1,2,3,4'to
907 -- a PL/SQL table numbers;
908
910 p_string IN VARCHAR2,
911 x_id_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
912 x_return_status OUT NOCOPY VARCHAR2);
913
914 -- Bug#3947506: Adding a new procedure Get_Entity_name
915 --========================================================================
916 -- PROCEDURE : Get_entity_name
917 --
918 -- COMMENT : This procedure will return the entity name for Trip, Stop,
919 -- Delivery. For Line, Line_id will be returned.
920 --========================================================================
921
922 PROCEDURE Get_Entity_name
923 (p_in_entity_id in NUMBER,
924 p_in_entity_name in VARCHAR2,
925 p_out_entity_id out NOCOPY VARCHAR2,
926 p_out_entity_name out NOCOPY VARCHAR2,
927 p_return_status out NOCOPY VARCHAR2);
928
929
930 --Bug 4070732 : The following two prcoedures added for this Bugfix
931
932 --========================================================================
933 -- PROCEDURE : Process_stops_for_load_tender
934 --
935 -- COMMENT : This procedure will call the WSH_TRIPS_ACTIONS.Fte_Load_Tender
936 -- for the Stop ID's present in the global cache table
937 -- G_STOP_IDS_STOP_IDS_CACHE and G_STOP_IDS_STOP_IDS_EXT_CACHE.
938 -- Once processed, this will call the API Reset_stops_for_load_tender
939 -- to reset the global variables.
940 --========================================================================
941
942
943 PROCEDURE Process_stops_for_load_tender(p_reset_flags IN BOOLEAN,x_return_status OUT NOCOPY VARCHAR2);
944
945
946 --========================================================================
947 -- PROCEDURE : Reset_stops_for_load_tender
948 --
949 -- COMMENT : This procedure will delete the contents of the gloabal cache
950 -- tables G_STOP_IDS_STOP_IDS_CACHE and G_STOP_IDS_STOP_IDS_EXT_CACHE
951 -- and also set the Boolean Global Variable G_CALL_FTE_LOAD_TENDER_API
952 -- to TRUE.
953 --========================================================================
954
955 PROCEDURE Reset_stops_for_load_tender (p_reset_flags IN BOOLEAN,x_return_status OUT NOCOPY VARCHAR2);
956
957
958 /*======================================================================
959 FUNCTION : ValidateActualDepartureDate
960
961 COMMENT : This function is called from
962 - WSHASCSRS concurrent program
963 - WSHPSRS concurrent program
964 - WSHPRREL library
965
966 This function checks whether users can enter a future date for
967 the Actual Departure Date parameter/field.
968 This function returns
969 - FALSE : if Global parameter Allow Future Ship Date = 'N'
970 and Ship Confirm Rule indicates Set Delivery Intransit
971 and Actual Departure Date is > SYSDATE
972 - TRUE : under all other conditions
973
974 HISTORY : rlanka 03/01/2005 Created
975 =======================================================================*/
976 FUNCTION ValidateActualDepartureDate(p_ship_confirm_rule_id IN NUMBER,
977 p_actual_departure_date IN DATE)
978 RETURN BOOLEAN;
979
980
981 /*======================================================================
982 FUNCTION : GetShipConfirmRule
983
984 COMMENT : This function is called from the
985 - WSHPSRS concurrent program
986
987 This function is used to obtain the Ship Confirm Rule tied
988 to a particular picking rule. This function is used to
989 populate a hidden parameter "Ship Confirm Rule ID" in WSHPSRS
990 concurrent program.
991
992 HISTORY : rlanka 03/01/2005 Created
993 =======================================================================*/
994 FUNCTION GetShipConfirmRule(p_picking_rule_id IN NUMBER) RETURN NUMBER;
995
996 --
997 -- Name WMS_Is_Installed
998 -- Purpose This procedure check whether WMS is installed.
999 -- It returns 'Y' if WMS is installed, 'N' if WMS
1000 -- is not installed
1001 --
1002 -- Input Arguments
1003 -- No input argument
1004 --
1005 FUNCTION WMS_Is_Installed return VARCHAR2;
1006
1007 --***************************************************************************--
1008 --========================================================================
1009 -- PROCEDURE : get_customer_from_loc PRIVATE
1010 --
1011 -- PARAMETERS: p_location_id Input Location id
1012 -- x_customer_id_tab List of customers at the input location
1013 -- x_return_status Return status
1014 -- COMMENT :
1015 -- Returns the customer id of the customer
1016 -- having a location at input wsh location id
1017 --========================================================================
1018
1019 PROCEDURE get_customer_from_loc(
1020 p_location_id IN NUMBER,
1021 x_customer_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1022 x_return_status OUT NOCOPY VARCHAR2);
1023
1027 --
1024 --***************************************************************************--
1025 --========================================================================
1026 -- PROCEDURE : get_org_from_location PRIVATE
1028 -- PARAMETERS: p_location_id Input Location id
1029 -- x_organization_tab List of Organizations for the input location
1030 -- x_return_status Return status
1031 -- COMMENT :
1032 -- Returns table of organizations for location.
1033 --========================================================================
1034 PROCEDURE get_org_from_location(
1035 p_location_id IN NUMBER,
1036 x_organization_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1037 x_return_status OUT NOCOPY VARCHAR2);
1038 --***************************************************************************--
1039 --========================================================================
1040 -- PROCEDURE : Get_Delivery_Status PRIVATE
1041 --
1042 -- PARAMETERS:
1043 -- p_entity_type either DELIVERY/DELIVERY DETAIL/LPN
1044 -- p_entity_id either delivery_id/delivery_detail_id/lpn_id
1045 -- x_status_code Status of delivery for the entity_type and
1046 -- entity id passed
1047 -- x_return_status return status
1048 --========================================================================
1049 -- API added for bug 4632726
1050 PROCEDURE Get_Delivery_Status (
1051 p_entity_type IN VARCHAR2,
1052 p_entity_id IN NUMBER,
1053 x_status_code OUT NOCOPY VARCHAR2,
1054 x_return_status OUT NOCOPY VARCHAR2 );
1055
1056 -- OTM R12
1057 --***************************************************************************--
1058 --
1059 -- Name Get_Otm_Install_Profile_Value
1060 -- Purpose This function returns the value of
1061 -- profile WSH_OTM_INSTALLED
1062 -- It returns 'P' if OTM is integrated for Inbound Purchasing
1063 -- 'O' if OTM is integrated for Outbound Sales Order
1064 -- 'Y' if OTM is integrated for both of the above
1065 -- 'N' if OTM is integrated for non of the above
1066 -- or if the profile value is NULL
1067 --
1068 -- Input Arguments
1069 -- No input argument
1070 --
1071 --***************************************************************************--
1072
1073 FUNCTION Get_Otm_Install_Profile_Value return VARCHAR2;
1074
1075 --***************************************************************************--
1076 --
1077 -- Name GC3_Is_Installed
1078 -- Purpose This function returns whether OTM is integrated for
1079 -- Outbound Sales Order flow by looking at the
1080 -- value of profile WSH_OTM_INSTALLED
1081 -- It returns 'Y' if OTM is integrated for Outbound Sales Order
1082 -- 'N' otherwise
1083 --
1084 -- Input Arguments
1085 -- No input argument
1086 --
1087 --***************************************************************************--
1088
1089 FUNCTION GC3_Is_Installed return VARCHAR2;
1090
1091 --
1092 --========================================================================
1093 -- PROCEDURE : GET_CURRENCY_CONVERSION_TYPE
1094 --
1095 -- API added for R12 Glog Integration Currency Conversion ECO
1096 --
1097 -- PURPOSE : To get the value for profile option WSH_OTM_CURR_CONV_TYPE
1098 -- (WSH: Currency Conversion Type for OTM)
1099 -- It returns the cached value if it is avaiable, otherwise
1100 -- fnd_profile.value api is called to get the profile value
1101 -- PARAMETERS:
1102 -- x_curr_conv_type currency conversion type
1103 -- x_return_status return status
1104 --========================================================================
1105 PROCEDURE Get_Currency_Conversion_Type (
1106 x_curr_conv_type OUT NOCOPY VARCHAR2,
1107 x_return_status OUT NOCOPY VARCHAR2 );
1108
1109 /*
1110 --========================================================================
1111 -- FUNCTION : get_trip_organization
1112 --
1113 -- PARAMETERS: p_trip_id Input Trip Id
1114 --
1115 -- COMMENT :
1116 -- This procedure returns back organiation id that is associated with the trip.
1117 -- Steps
1118 -- For Outbound and Mixed trip's see if there is a organization at the location of first stop
1119 -- For inbound see if there is a organization at the location of the last stop.
1120 -- If there are no organizations associated then get the organization id of the delivery with
1121 -- least delivery id
1122 --========================================================================
1123 */
1124
1125 FUNCTION GET_TRIP_ORGANIZATION_ID(p_trip_id NUMBER) RETURN NUMBER;
1126
1127 /*
1128 --========================================================================
1129 -- PROCEDURE : GET_FIRST_LAST_STOP_INFO
1130 --
1131 -- PARAMETERS: p_trip_id Input Trip id
1132 -- x_arrival_date Last Stop's Arrival Date
1136 -- x_first_stop_loc_id First Stop Location Id
1133 -- x_departure_date First Stop's Departure Date
1134 -- x_first_stop_id First Stop Id
1135 -- x_last_stop_id Last Stop Id
1137 -- x_last_stop_loc_id Last Stop Location Id
1138 -- x_return_status Return status
1139 -- COMMENT :
1140 -- Returns the first and the last Stop's information of a trip
1141 --========================================================================
1142 */
1143
1144 PROCEDURE GET_FIRST_LAST_STOP_INFO(x_return_status OUT NOCOPY VARCHAR2,
1145 x_arrival_date OUT NOCOPY DATE,
1146 x_departure_date OUT NOCOPY DATE,
1147 x_first_stop_id OUT NOCOPY NUMBER,
1148 x_last_stop_id OUT NOCOPY NUMBER,
1149 x_first_stop_loc_id OUT NOCOPY NUMBER,
1150 x_last_stop_loc_id OUT NOCOPY NUMBER,
1151 p_trip_id NUMBER);
1152
1153
1154 -- End of OTM R12
1155
1156
1157 END WSH_UTIL_CORE;
[Home] [Help]