[Home] [Help]
PACKAGE BODY: APPS.WMS_ATF_DEST_LOCATOR
Source
1 package body wms_atf_dest_locator as
2 /* $Header: WMSADELB.pls 120.1 2005/05/25 17:33:52 appldev $ */
3 --
4 -- ---------------------------------------------------------------------------|
5 -- |-------------------------------< trace >----------------------------------|
6 -- ---------------------------------------------------------------------------|
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- Wrapper around the tracing utility.
11 --
12 -- Prerequisites:
13 -- None
14 --
15 -- In Parameters:
16 -- Name Reqd Type Description
17 -- p_message Yes varchar2 Message to be displayed in the log file.
18 -- p_prompt Yes varchar2 Prompt.
19 -- p_level No number Level.
20 --
21 -- Post Success:
22 -- None.
23 --
24 -- Post Failure:
25 -- None
26 --
27 -- Access Status:
28 -- Internal Development Use Only.
29 --
30 -- {End Of Comments}
31 --
32 Procedure trace(
33 p_message in varchar2
34 , p_level in number
35 ) is
36 begin
37 INV_LOG_UTIL.trace(p_message, 'WMS_ATF_DEST_LOCATOR', p_level);
38 end trace;
39 --
40 -- -----------------------------------------------------------------------------|
41 -- |------------------------< get_dest_locator >--------------------------------|
42 -- -----------------------------------------------------------------------------|
43 -- API name : Get_destination_Loc_LPN
44 -- Type : Private
45 -- Function : Returns Sub/Loc, LPN or Validation Status.
46 -- Input Parameters :
47 -- As shown below.
48 --
49 -- Output Parameters:
50 -- As shown below.
51 -- Version :
52 -- Current version 1.0
53 --
54 -- Notes :
55 -- Date Modification Author
56 -- ------------ ------------ ------------------
57 --
58 PROCEDURE get_dest_locator(
59 x_return_status out nocopy varchar2
60 , x_msg_count out nocopy number
61 , x_msg_data out nocopy varchar2
62 , x_locator_id out nocopy number
63 , x_zone_id out nocopy number
64 , x_subinventory_code out nocopy varchar2
65 , x_loc_valid out nocopy varchar2
66 , p_mode in number -- 1. Selection 2. Validation
67 , p_task_id in number -- MMTT.transaction_temp_id
68 , p_activity_type_id in number -- 1. Inbound 2. Outbound
69 , p_hook_call_id in number -- Seeded/Custom API call hook id in the wms_api_hook_calls.
70 , p_locator_id in number -- Locator_id for Validation pruposes.
71 , p_item_id in number -- Item ID for Validation purposes.
72 , p_api_version in number
73 , p_init_msg_list in varchar2
74 , p_commit in varchar2
75 )
76 is
77
78 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
79 l_current_package_cntr number;
80 l_proc varchar2(72) := 'GET_DEST_LOCATOR :';
81 l_prog float;
82 l_effective_to_date date;
83
84 -- Added the wahc.effective_to_date to the select on December 24th 2003.
85 cursor c_get_hook_call_details is
86 select wahe.current_package_cntr, wahc.effective_to_date
87 from wms_api_hook_calls wahc, wms_api_hooked_entities wahe
88 where wahe.module_hook_id = wahc.module_hook_id
89 -- Restricts the output to "Locator Selection/Validation" only
90 and wahe.short_name_id = 1
91 and wahc.hook_call_id = p_hook_call_id;
92
93 begin
94 -- ### Initialize API return status to success
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97 if (l_debug = 1) then
98 trace(l_proc ||' Entering package wms_atf_dest_locator '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
99 trace(l_proc ||' Entering procedure get_dest_locator '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
100 trace(l_proc ||' p_mode => ' || p_mode);
101 trace(l_proc ||' p_task_id => ' || p_task_id);
102 trace(l_proc ||' p_activity_type_id => ' || p_activity_type_id);
103 trace(l_proc ||' p_hook_call_id => ' || p_hook_call_id);
104 trace(l_proc ||' p_locator_id => ' || p_locator_id);
105 trace(l_proc ||' p_item_id => ' || p_item_id);
106 end if;
107
108 -- ### Derive hook call details to start with.
109 if (l_debug = 1) then
110 trace(l_proc ||' Opening Cursor c_get_hook_call_details '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
111 end if;
112 -- Opening Cursor...
113 open c_get_hook_call_details;
114 if (l_debug = 1) then
115 trace(l_proc ||' Fetching Cursor c_get_hook_call_details '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
116 end if;
117
118 fetch c_get_hook_call_details
119 into l_current_package_cntr, l_effective_to_date;
120
121 if c_get_hook_call_details%NOTFOUND then
122 if (l_debug = 1) then
123 trace(l_proc ||' c_get_hook_call_details%NOTFOUND...Invalid p_hook_call_id passed in ...Cannot Proceed');
124 end if;
125 close c_get_hook_call_details;
126 else
127 -- ### Short Name is maintained as an MFG_LOOKUP.
128 -- ### 1. Loc Determination
129 -- ### 2. LPN Determination
130 if (l_debug = 1) then
131 trace(l_proc ||' Cursor c_get_hook_call_details FOUND...');
132 trace(l_proc ||' Values after fetching Cursor c_get_hook_call_details.. ', 4);
133 trace(l_proc ||' l_current_package_cntr '|| l_current_package_cntr, 4);
134 trace(l_proc ||' l_effective_to_date '|| l_effective_to_date, 4);
135 end if;
136
137 -- Added after TOI Session on Dec.23rd 2003.
138 -- As per the latest design, a check is being done against the 'p_hook_call_id' passed in to test if its
139 -- valid w.r.to 'effective_to_date'. If the effective_to_date is less than the current date, then an error
140 -- is passed bask to the calling program and the message stack is also populated accordingly.
141 if (trunc(l_effective_to_date) < sysdate) then
142 trace(l_proc ||' Effective to date of the relationship is less than the current date...');
143 trace(l_proc ||' This renders this relationship ineffective for the current run though this hook call..');
144 trace(l_proc ||' may have been valid when the operation plan was set up. ');
145 fnd_message.set_name('WMS', 'WMS_EXPIRED_HOOK_CALL_ID');
146 fnd_msg_pub.ADD;
147 raise fnd_api.g_exc_error;
148 end if;
149
150
151 -- ### Check if p_mode is 'Validation' and the p_locator_id is null.
152 if (p_mode = 2 and p_locator_id is null)
153 then
154 if (l_debug =1) then
155 trace(l_proc ||' Locator ID cannot be null if "wms_atf_dest_locator.get_dest_locator" is called in validation mode');
156 end if;
157 return;
158 end if;
159 -- ###
160 -- ### This means that the call is to Select/Validate Location.
161 -- ### Also call the current package based on the counter.
162 -- ### Procedure GET_LOC(
163 -- ### x_return_status OUT VARCHAR2,
164 -- ### x_msg_count OUT NUMBER,
165 -- ### x_msg_data OUT VARCHAR2,
166 -- ### x_locator_id OUT NUMBER,
167 -- ### x_subinventory_code OUT VARCHAR2,
168 -- ### x_loc_valid OUT VARCHAR2,
169 -- ### p_mode IN NUMBER,
170 -- ### p_activity_type_id IN NUMBER,
171 -- ### p_task_id IN NUMBER,
172 -- ### p_locator_id IN NUMBER,
173 -- ### p_item_id IN NUMBER,
174 -- ### p_hook_call_id IN NUMBER
175 -- ### );
176
177 if l_current_package_cntr = 1
178 then
179 if (l_debug =1) then
180 trace(l_proc ||' Within "l_current_package_cntr = 1" clause...');
181 trace(l_proc ||' Before calling package "wms_api_loc_package_1.get_loc"....', 1);
182 trace(l_proc ||' Input parameters...');
183 trace(l_proc ||' p_mode = ' || p_mode);
184 trace(l_proc ||' p_activity_type_id = ' || p_activity_type_id);
185 trace(l_proc ||' p_task_id = ' || p_task_id);
186 trace(l_proc ||' p_locator_id = ' || p_locator_id);
187 trace(l_proc ||' p_item_id = ' || p_item_id);
188 trace(l_proc ||' p_hook_call_id = ' || p_hook_call_id);
189 end if;
190
191 wms_api_loc_package_1.get_loc(
192 x_return_status => x_return_status
193 , x_msg_count => x_msg_count
194 , x_msg_data => x_msg_data
195 , x_locator_id => x_locator_id
196 , x_zone_id => x_zone_id
197 , x_subinventory_code => x_subinventory_code
198 , x_loc_valid => x_loc_valid
199 , p_mode => p_mode
200 , p_activity_type_id => p_activity_type_id
201 , p_task_id => p_task_id
202 , p_locator_id => p_locator_id
203 , p_item_id => p_item_id
204 , p_hook_call_id => p_hook_call_id
205 , p_api_version => p_api_version
206 , p_init_msg_list => p_init_msg_list
207 , p_commit => p_commit
208 );
209
210 if (l_debug =1) then
211 trace(l_proc ||' After calling package "wms_api_loc_package_1.get_loc"....', 1);
212 trace(l_proc ||' Out parameters...');
213 trace(l_proc ||' x_return_status = ' || x_return_status);
214 trace(l_proc ||' x_locator_id = ' || x_locator_id);
215 trace(l_proc ||' x_zone_id = ' || x_zone_id);
216 trace(l_proc ||' x_subinventory_code = ' || x_subinventory_code);
217 trace(l_proc ||' x_loc_valid = ' || x_loc_valid);
218 end if;
219 elsif l_current_package_cntr = 2
220 then
221 if (l_debug =1) then
222 trace(l_proc ||' Within "l_current_package_cntr = 2" clause...');
223 trace(l_proc ||' Before calling package "wms_api_loc_package_2.get_loc"....', 1);
224 trace(l_proc ||' Input parameters...');
225 trace(l_proc ||' p_mode = ' || p_mode);
226 trace(l_proc ||' p_activity_type_id = ' || p_activity_type_id);
227 trace(l_proc ||' p_task_id = ' || p_task_id);
228 trace(l_proc ||' p_locator_id = ' || p_locator_id);
229 trace(l_proc ||' p_item_id = ' || p_item_id);
230 trace(l_proc ||' p_hook_call_id = ' || p_hook_call_id);
231 end if;
232
233 wms_api_loc_package_2.get_loc(
234 x_return_status => x_return_status
235 , x_msg_count => x_msg_count
236 , x_msg_data => x_msg_data
237 , x_locator_id => x_locator_id
238 , x_zone_id => x_zone_id
239 , x_subinventory_code => x_subinventory_code
240 , x_loc_valid => x_loc_valid
241 , p_mode => p_mode
242 , p_activity_type_id => p_activity_type_id
243 , p_task_id => p_task_id
244 , p_locator_id => p_locator_id
245 , p_item_id => p_item_id
246 , p_hook_call_id => p_hook_call_id
247 , p_api_version => p_api_version
248 , p_init_msg_list => p_init_msg_list
249 , p_commit => p_commit
250 );
251
252 if (l_debug =1) then
253 trace(l_proc ||' After calling package "wms_api_loc_package_1.get_loc"....', 1);
254 trace(l_proc ||' Out parameters...');
255 trace(l_proc ||' x_return_status = ' || x_return_status);
256 trace(l_proc ||' x_locator_id = ' || x_locator_id);
257 trace(l_proc ||' x_zone_id = ' || x_zone_id);
258 trace(l_proc ||' x_subinventory_code = ' || x_subinventory_code);
259 trace(l_proc ||' x_loc_valid = ' || x_loc_valid);
260 end if;
261 end if;
262 if (l_debug = 1) then
263 trace(' Exiting package wms_atf_dest_loc_lpn_wrap '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
264 trace(' Exiting procedure get_dest_loc_lpn_wrap '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
265 trace( ' x_locator_id => ' || x_locator_id
266 ||' x_subinventory_code => ' || x_subinventory_code
267 ||' x_loc_valid => ' || x_loc_valid, 4);
268 end if;
269 end if;
270 close c_get_hook_call_details;
271 return;
272 exception
273 when fnd_api.g_exc_error then
274 x_return_status := fnd_api.g_ret_sts_error;
275
276 if (l_debug = 1) then
277 trace(' Progress at the time of failure is ' || l_prog, 1);
278 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
279 end if;
280
281 when others then
282 close c_get_hook_call_details;
283
284 if (l_debug = 1) then
285 trace('Error within "When Others" of the Outermost block in the get_dest_loc_lpn_wrap package is ..'|| sqlerrm(sqlcode));
286 end if;
287 end get_dest_locator;
288
289 end wms_atf_dest_locator;