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