1 PACKAGE BODY WSH_OTM_SYNC_ITEM_PKG AS
2 /* $Header: WSHTMITB.pls 120.0.12000000.2 2007/04/02 17:42:21 schennal noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_OTM_SYNC_ITEM_PKG';
5
6 -----------------------------------------------------------------------------
7 --
8 -- Function :get_EBS_item_info
9 -- Parameters :p_entity_in_rec is the input rec type.
10 -- It has the entity_type, entity id and parent entity id
11 -- x_transmission_id Transmission id passed to the caller
12 -- x_return_status Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
13 -- Description :This Function takes input from the txn service and passes
14 -- the item data back. The item data is passed in the form of
15 -- of collection WSH_OTM_GLOG_ITEM_TBL thats maps to
16 -- GLOG Schema ITEMMASTER
17 -----------------------------------------------------------------------------
18 FUNCTION get_EBS_item_info( p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
19 x_transmission_id OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2
21 ) RETURN WSH_OTM_GLOG_ITEM_TBL IS
22
23
24 --Bug#5961151: added substrb for item description as OTM can accept only 120 characters.
25 CURSOR c_get_delivery_items(p_delivery_id NUMBER) IS
26 SELECT mtlb.inventory_item_id,
27 mtlk.concatenated_segments,
28 substrb(mtlt.description,1,120),
29 mtlb.last_update_date,
30 mtlb.organization_id
31 FROM wsh_delivery_assignments wda,
32 wsh_delivery_details wdd,
33 mtl_system_items_b mtlb,
34 mtl_system_items_tl mtlt,
35 mtl_system_items_kfv mtlk
36 WHERE wda.delivery_id = p_delivery_id
37 AND wda.delivery_detail_id = wdd.delivery_detail_id
38 AND wdd.inventory_item_id = mtlb.inventory_item_id
39 AND wdd.inventory_item_id = mtlt.inventory_item_id
40 AND wdd.inventory_item_id = mtlk.inventory_item_id
41 AND wdd.organization_id = mtlb.organization_id
42 AND wdd.organization_id = mtlk.organization_id
43 AND wdd.organization_id = mtlt.organization_id
44 AND mtlb.shippable_item_flag = 'Y'
45 AND mtlk.shippable_item_flag = 'Y'
46 AND mtlt.language = userenv('LANG');
47
48 --Bug#5961151: added substrb for item description as OTM can accept only 120 characters.
49 CURSOR c_get_trip_items(p_trip_id NUMBER) IS
50 SELECT mtlb.inventory_item_id,
51 mtlk.concatenated_segments,
52 substrb(mtlt.description,1,120),
53 mtlb.last_update_date,
54 mtlb.organization_id
55 FROM wsh_delivery_assignments wda,
56 wsh_delivery_details wdd,
57 wsh_delivery_legs wdl,
58 wsh_trip_stops wts,
59 mtl_system_items_b mtlb,
60 mtl_system_items_tl mtlt,
61 mtl_system_items_kfv mtlk
62 WHERE wts.trip_id = p_trip_id
63 AND wts.stop_id = wdl.pick_up_stop_id
64 AND wda.delivery_id = wdl.delivery_id
65 AND wda.delivery_detail_id = wdd.delivery_detail_id
66 AND wdd.inventory_item_id = mtlb.inventory_item_id
67 AND wdd.inventory_item_id = mtlt.inventory_item_id
68 AND wdd.inventory_item_id = mtlk.inventory_item_id
69 AND wdd.organization_id = mtlb.organization_id
70 AND wdd.organization_id = mtlk.organization_id
71 AND wdd.organization_id = mtlt.organization_id
72 AND mtlb.shippable_item_flag = 'Y'
73 AND mtlk.shippable_item_flag = 'Y'
74 AND mtlt.language = userenv('LANG');
75
76 --Cursor to get the new transmission Id
77 CURSOR c_get_transmission_id IS
78 SELECT wsh_otm_sync_ref_data_log_s.NEXTVAL
79 FROM dual;
80
81 --Declare are local variables of GLOG record and table types
82 l_tbl_send_item_info WSH_OTM_GLOG_ITEM_TBL;
83 l_rec_itemmaster WSH_OTM_ITEMMASTER;
84
85 l_rec_item item_info;
86 l_tbl_item item_info_tbl;
87
88 l_delivery_id NUMBER;
89 l_trip_id NUMBER;
90
91 l_item_id NUMBER;
92 l_item_name VARCHAR2(40);
93 l_item_description VARCHAR2(240);
94 l_last_update_date DATE;
95 l_org_id NUMBER;
96
97 l_domain_name VARCHAR2(50);
98 l_xid VARCHAR2(50);
99
100 l_substitute_entity VARCHAR2(50);
101 l_transmission_id NUMBER;
102 l_send_allowed BOOLEAN;
103 l_send_count NUMBER := 0;
104
105 e_null_id_error EXCEPTION;
106 e_entity_type_error EXCEPTION;
107
108 l_return_status VARCHAR2(1);
109 l_debug_on BOOLEAN ;
110 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_EBS_ITEM_INFO';
111
112 BEGIN
113
114 -- Debug Statements
115 --
116 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
117 --
118 IF l_debug_on IS NULL THEN
119 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
120 END IF;
121
122 IF l_debug_on THEN
123 WSH_DEBUG_SV.push(l_module_name);
124 WSH_DEBUG_SV.log(l_module_name,' p_entity_in_rec.ENTITY_TYPE ', p_entity_in_rec.ENTITY_TYPE);
125 END IF;
126
127 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
128
129 --Check for number of ids in the input table and if 0 then raise error.
130 IF p_entity_in_rec.entity_id_tbl.COUNT = 0 THEN
131 RAISE e_null_id_error;
132 END IF;
133
134 l_tbl_send_item_info := WSH_OTM_GLOG_ITEM_TBL();
135 --l_my_table := glog_item_tbl();
136
137 --Get the new transmission Id
138 OPEN c_get_transmission_id;
139 FETCH c_get_transmission_id INTO l_transmission_id;
140 CLOSE c_get_transmission_id;
141
142 --Get the domain name from the profile value
143 FND_PROFILE.Get('WSH_OTM_DOMAIN_NAME',l_domain_name);
144 IF (l_domain_name IS NULL) THEN
145 --{
146 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
147 FND_MESSAGE.SET_TOKEN('PRF_NAME','WSH_OTM_DOMAIN_NAME');
148 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
149 wsh_util_core.add_message(x_return_status, l_module_name);
150 IF l_debug_on THEN
151 WSH_DEBUG_SV.logmsg(l_module_name,'Error: The profile WSH_OTM_DOMAIN_NAME is set to NULL. Please correct the profile value');
152 END IF;
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 --}
155 END IF;
156
157 --For each delivery and trip get the item info and put it into the local table l_tbl_item
158 IF p_entity_in_rec.ENTITY_TYPE = 'DELIVERY' THEN
159 FOR l_loop_count IN p_entity_in_rec.entity_id_tbl.FIRST .. p_entity_in_rec.entity_id_tbl.LAST
160 LOOP
161 OPEN c_get_delivery_items(p_entity_in_rec.entity_id_tbl(l_loop_count));
162 LOOP
163 FETCH c_get_delivery_items into l_rec_item;
164 EXIT WHEN c_get_delivery_items%NOTFOUND;
165 l_tbl_item(l_tbl_item.COUNT+1) := l_rec_item;
166 END LOOP;
167 CLOSE c_get_delivery_items;
168 END LOOP;
169 ELSIF p_entity_in_rec.ENTITY_TYPE = 'TRIP' THEN
170 FOR l_loop_count IN p_entity_in_rec.entity_id_tbl.FIRST .. p_entity_in_rec.entity_id_tbl.LAST
171 LOOP
172 OPEN c_get_trip_items(p_entity_in_rec.entity_id_tbl(l_loop_count));
173 LOOP
174 FETCH c_get_trip_items into l_rec_item;
175 EXIT WHEN c_get_trip_items%NOTFOUND;
176 l_tbl_item(l_tbl_item.COUNT+1) := l_rec_item;
177 END LOOP;
178 CLOSE c_get_trip_items;
179 END LOOP;
180 ELSE
181 RAISE e_entity_type_error;
182 END IF;
183
184 --Search the table l_tbl_item for duplicates and if found then remove them.
185 IF l_debug_on THEN
186 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_ITEM_PKG.REMOVE_DUPLICATE_ITEMS', WSH_DEBUG_SV.C_PROC_LEVEL);
187 END IF;
188
189 IF l_tbl_item.COUNT <> 0 THEN
190 remove_duplicate_items(p_item_tbl => l_tbl_item,
191 x_return_status => l_return_status);
192 END IF;
193
194 IF l_tbl_item.COUNT <> 0 THEN
195 FOR l_loop_index in l_tbl_item.FIRST .. l_tbl_item.LAST
196 LOOP
197 l_item_id := l_tbl_item(l_loop_index).item_id;
198 l_last_update_date := l_tbl_item(l_loop_index).last_update_date;
199 l_item_name := l_tbl_item(l_loop_index).item_name;
200 l_item_description := l_tbl_item(l_loop_index).item_description;
201 l_org_id := l_tbl_item(l_loop_index).org_id;
202
203 --For each item find whether it has to be sent to GLOG
204 IF l_debug_on THEN
205 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD', WSH_DEBUG_SV.C_PROC_LEVEL);
206 END IF;
207
208 WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD(P_ENTITY_ID => l_item_id,
209 P_PARENT_ENTITY_ID => l_org_id,
210 P_ENTITY_TYPE => 'ITEM',
211 P_ENTITY_UPDATED_DATE => l_last_update_date,
212 X_SUBSTITUTE_ENTITY => l_substitute_entity,
213 P_TRANSMISSION_ID => l_transmission_id ,
214 X_SEND_ALLOWED => l_send_allowed,
215 X_RETURN_STATUS => l_return_status
216 );
217 --If l_send_allowed is TRUE then populate l_tbl_send_item_info with that item info
218 IF l_send_allowed THEN
219 --Construct the XID
220 l_xid := to_char(l_org_id) || '-' || to_char(l_item_id);
221 --Extend the collection.
222 l_tbl_send_item_info.EXTEND;
223 l_send_count := l_send_count + 1;
224 l_tbl_send_item_info(l_send_count) := WSH_OTM_ITEMMASTER(
225 WSH_OTM_ITEM_TYPE('IU',
226 WSH_OTM_GID_TYPE(WSH_OTM_GID_T(l_domain_name,l_xid)),
227 l_item_name,
228 l_item_description),
229 WSH_OTM_PACKAGING_TYPE(WSH_OTM_GID_TYPE(WSH_OTM_GID_T(l_domain_name,l_xid)),
230 l_item_description));
231 END IF;
232 END LOOP;
233
234 END IF;
235
236 --Delete the local table l_tbl_item.
237 l_tbl_item.DELETE;
238
239 IF l_debug_on THEN
240 WSH_DEBUG_SV.pop(l_module_name);
241 END IF;
242
243 --Check for the number of rows in the table.
244 IF l_send_count = 0 THEN
245 -- This means that there are not items to be send and in this case pass the transmission_id = NULL
246 x_transmission_id := NULL;
247 ELSE
248 x_transmission_id := l_transmission_id;
249 END IF;
250
251 return l_tbl_send_item_info;
252
253 EXCEPTION
254 WHEN e_null_id_error THEN
255 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.logmsg(l_module_name,'p_Ids cannot be null',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
258 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NULL_IDS');
259 raise;
260 END IF;
261 WHEN e_entity_type_error THEN
262 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
263 IF l_debug_on THEN
264 WSH_DEBUG_SV.logmsg(l_module_name,'wrong entity type passed',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
265 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WRONG_ENTITY_TYPE');
266 raise;
267 END IF;
268 WHEN OTHERS THEN
269 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
270 IF l_debug_on THEN
271 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
272 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
273 raise;
274 END IF;
275
276 END get_EBS_item_info;
277
278
279 -----------------------------------------------------------------------------
280 --
281 -- Procedure :remove_duplicate_items
282 -- Parameters :p_item_tbl is the input table of item_info_tbl.
283 -- x_return_status Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
284 -- Description :This procedure take in the input table and removes all the
285 -- duplicate rows.
286 -----------------------------------------------------------------------------
287 PROCEDURE remove_duplicate_items(p_item_tbl IN OUT NOCOPY item_info_tbl,
288 x_return_status OUT NOCOPY VARCHAR2)IS
289
290 l_item_id NUMBER;
291 l_org_id NUMBER;
292 l_item_tbl item_info_tbl;
293 l_count NUMBER;
294
295 l_debug_on BOOLEAN ;
296 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REMOVE_DUPLICATE_ITEMS';
297
298 BEGIN
299
300 -- Debug Statements
301 --
302 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
303 --
304 IF l_debug_on IS NULL THEN
305 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
306 END IF;
307
308 IF l_debug_on THEN
309 WSH_DEBUG_SV.push(l_module_name);
310 WSH_DEBUG_SV.log(l_module_name,'No of rows in item_info_tbl ',p_item_tbl.COUNT);
311 END IF;
312
313 l_count := p_item_tbl.COUNT;
314
315 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
316
317 FOR l_loop_count IN p_item_tbl.FIRST .. p_item_tbl.LAST
318 LOOP
319 l_item_id := p_item_tbl(l_loop_count).item_id;
320 l_org_id := p_item_tbl(l_loop_count).org_id;
321 IF l_item_id IS NOT NULL THEN
322 l_item_tbl(l_item_tbl.COUNT+1) := p_item_tbl(l_loop_count);
323
324 FOR l_inner_count IN l_loop_count .. p_item_tbl.LAST
325 LOOP
326 --Bug 5079207: Added condition to check for org_id also.
327 IF p_item_tbl(l_inner_count).item_id = l_item_id AND p_item_tbl(l_inner_count).org_id = l_org_id THEN
328 p_item_tbl(l_inner_count) := NULL;
329 END IF;
330 END LOOP;
331 END IF;
332 END LOOP;
333
334 p_item_tbl := l_item_tbl;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
339 IF l_debug_on THEN
340 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
341 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
342 raise;
343 END IF;
344 END remove_duplicate_items;
345
346 END WSH_OTM_SYNC_ITEM_PKG;
[Home] [Help]