1 PACKAGE WSH_MAP_LOCATION_REGION_PKG AS
2 /* $Header: WSHMLORS.pls 120.4.12010000.2 2008/08/04 12:31:52 suppal ship $ */
3
4 g_loc_commit VARCHAR2(1) := 'Y';
5
6 TYPE loc_rec_type IS RECORD(
7 WSH_LOCATION_ID WSH_LOCATIONS.WSH_LOCATION_ID%TYPE,
8 SOURCE_LOCATION_ID WSH_LOCATIONS.SOURCE_LOCATION_ID%TYPE,
9 LOCATION_SOURCE_CODE WSH_LOCATIONS.LOCATION_SOURCE_CODE%TYPE,
10 LOCATION_CODE WSH_LOCATIONS.LOCATION_CODE%TYPE,
11 UI_LOCATION_CODE WSH_LOCATIONS.UI_LOCATION_CODE%TYPE,
12 ADDRESS1 WSH_LOCATIONS.ADDRESS1%TYPE,
13 ADDRESS2 WSH_LOCATIONS.ADDRESS2%TYPE,
14 ADDRESS3 WSH_LOCATIONS.ADDRESS3%TYPE,
15 ADDRESS4 WSH_LOCATIONS.ADDRESS4%TYPE,
16 COUNTRY WSH_LOCATIONS.COUNTRY%TYPE,
17 STATE WSH_LOCATIONS.STATE%TYPE,
18 PROVINCE WSH_LOCATIONS.PROVINCE%TYPE,
19 COUNTY WSH_LOCATIONS.COUNTY%TYPE,
20 CITY WSH_LOCATIONS.CITY%TYPE,
21 POSTAL_CODE WSH_LOCATIONS.POSTAL_CODE%TYPE,
22 INACTIVE_DATE WSH_LOCATIONS.INACTIVE_DATE%TYPE);
23
24 TYPE TableNumbers is TABLE of NUMBER INDEX BY BINARY_INTEGER; -- table number type
25 TYPE TableVarchar is TABLE of VARCHAR2(120) INDEX BY BINARY_INTEGER; -- table varchar(120) type
26 TYPE TableDate is TABLE of DATE INDEX BY BINARY_INTEGER; -- table date type
27 TYPE TableBoolean is TABLE of BOOLEAN INDEX BY BINARY_INTEGER; -- table boolean type
28
29 /*===========================================================================+
30 | PROCEDURE |
31 | Map_Locations |
32 | |
33 | DESCRIPTION |
34 | This procedure selects the minimum and maximum location id |
35 | and fires the child concurrent program depending on the |
36 | value of parameter p_num_of_instances |
37 | |
38 +===========================================================================*/
39
40 PROCEDURE Map_Locations (
41 p_errbuf OUT NOCOPY VARCHAR2,
42 p_retcode OUT NOCOPY NUMBER,
43 p_map_regions IN VARCHAR2,
44 p_location_type IN VARCHAR2,
45 p_num_of_instances IN NUMBER,
46 p_start_date IN VARCHAR2,
47 p_end_date IN VARCHAR2,
48 p_fte_installed IN VARCHAR2 default NULL,
49 p_create_facilities IN VARCHAR2 default NULL);
50
51 /*===========================================================================+
52 | PROCEDURE |
53 | Map_Location_Child_Program |
54 | |
55 | DESCRIPTION |
56 | This is just a wrapper routine and call the main processing |
57 | API Mapping_Main. This procedure is also by the TCA Callout |
58 | API Rule_Location. |
59 | |
60 +===========================================================================*/
61
62 PROCEDURE Map_Locations_Child_Program (
63 p_errbuf OUT NOCOPY VARCHAR2,
64 p_retcode OUT NOCOPY NUMBER,
65 p_location_type IN VARCHAR2,
66 p_map_regions IN VARCHAR2,
67 p_from_location IN NUMBER,
68 p_to_location IN NUMBER,
69 p_start_date IN VARCHAR2,
70 p_end_date IN VARCHAR2,
71 p_create_facilities IN VARCHAR2 default NULL) ;
72
73 /*===========================================================================+
74 | PROCEDURE |
75 | Mapping_Regions_Main |
76 | |
77 | DESCRIPTION |
78 | This API selects all the location data into PL/SQL table |
79 | types and calls the Map_Location_To_Region by passing the |
80 | location information |
81 | |
82 +===========================================================================*/
83
84 PROCEDURE Mapping_Regions_Main (
85 p_location_type IN VARCHAR2,
86 p_from_location IN NUMBER,
87 p_to_location IN NUMBER,
88 p_start_date IN VARCHAR2,
89 p_end_date IN VARCHAR2,
90 p_insert_flag IN BOOLEAN default TRUE, -- Bug 4722963
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_sqlcode OUT NOCOPY NUMBER,
93 x_sqlerr OUT NOCOPY VARCHAR2);
94
95 /*===========================================================================+
96 | FUNCTION |
97 | Insert_Record |
98 | |
99 | DESCRIPTION |
100 | This API inserts the record into intersection table |
101 | |
102 +===========================================================================*/
103
104 PROCEDURE Insert_Record
105 (
106 p_location_id IN NUMBER,
107 p_region_id IN NUMBER,
108 p_region_type IN NUMBER,
109 p_exception IN VARCHAR2,
110 p_location_source IN VARCHAR2,
111 p_parent_region IN VARCHAR2,
112 x_return_status OUT NOCOPY VARCHAR2
113 );
114
115 /*===========================================================================+
116 | PROCEDURE |
117 | Map_Location_To_Region |
118 | |
119 | DESCRIPTION |
120 | This API does the main mapping process. It calls the API |
121 | WSH_REGIONS_SEARCH_PKG.Get_Region_Info which inturn returns |
122 | the region id. For this particuar region, the parent regions |
123 | are also obtained and all these are inserted into the |
124 | intersection table. |
125 | |
126 +===========================================================================*/
127
128 PROCEDURE Map_Location_To_Region (
129 p_country IN VARCHAR2,
130 p_country_code IN VARCHAR2,
131 p_state IN VARCHAR2,
132 p_city IN VARCHAR2,
133 p_postal_code IN VARCHAR2,
134 p_location_id IN NUMBER,
135 p_location_source IN VARCHAR2,
136 p_inactive_date IN DATE,
137 p_insert_flag IN BOOLEAN DEFAULT TRUE, -- Bug 4722963
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_sqlcode OUT NOCOPY NUMBER,
140 x_sqlerr OUT NOCOPY VARCHAR2);
141
142 /*===========================================================================+
143 | FUNCTION |
144 | Rule_Location |
145 | |
146 | DESCRIPTION |
147 | This is the rule function for the following TCA events : |
148 | # oracle.apps.ar.hz.Location.create |
149 | # oracle.apps.ar.hz.Location.update |
150 | This calls the Mapping_Main API to recreate the mapping once |
151 | a location gets created on a location gets updated. |
152 | |
153 +===========================================================================*/
154
155 FUNCTION Rule_Location(
156 p_subscription_guid in raw,
157 p_event in out NOCOPY wf_event_t)
158 RETURN VARCHAR2;
159
160 PROCEDURE Transfer_Location (
161 p_source_type IN VARCHAR2,
162 p_source_location_id IN NUMBER,
163 p_transfer_location IN BOOLEAN DEFAULT TRUE,
164 p_online_region_mapping IN BOOLEAN,
165 p_caller IN VARCHAR2 DEFAULT NULL,
166 x_loc_rec OUT NOCOPY loc_rec_type,
167 x_return_status OUT NOCOPY VARCHAR2 );
168
169 PROCEDURE Location_User_Hook_API(
170 p_location_id IN NUMBER);
171
172 PROCEDURE Get_Transit_Time(p_ship_from_loc_id IN NUMBER,
173 p_ship_to_site_id IN NUMBER,
174 p_ship_method_code IN VARCHAR2 DEFAULT NULL,
175 p_carrier_id IN NUMBER,
176 p_service_code IN VARCHAR2,
177 p_mode_code IN VARCHAR2,
178 p_from IN VARCHAR2,
179 x_transit_time OUT NOCOPY NUMBER,
180 x_return_status OUT NOCOPY VARCHAR2);
181
182
183 -- will cache transit time given a ship method by maintaining ship method
184 -- and transit time with same index for corresponding ship method-transit time
185 -- combination
186 TYPE t_ship_from_loc_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
187 TYPE t_ship_to_site_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
188 TYPE t_ship_method_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
189 TYPE t_transit_time_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
190
191 g_ship_from_loc_tab t_ship_from_loc_tab;
192 g_ship_to_site_tab t_ship_to_site_tab;
193 g_ship_method_tab t_ship_method_tab;
194 g_transit_time_tab t_transit_time_tab;
195
196 --==============================================================================
197 -- PROCEDURE : PREDEL_LOC_VALIDATION Added for bug Bug 6940375
198 --
199 -- PARAMETERS : p_location_id Input location id
200 -- DESCRIPTION : This procedure checks if an Internal location is eligible
201 -- for deletion. Shipping raises error if the location exists
202 -- in shipping tables.
203 --===============================================================================
204 PROCEDURE PREDEL_LOC_VALIDATION (p_location_id IN NUMBER);
205
206 END WSH_MAP_LOCATION_REGION_PKG;
207
[Home] [Help]