1 PACKAGE BODY WMS_DEPLOY AS
2 /* $Header: WMSDEPLB.pls 120.0.12010000.12 2010/01/25 14:35:33 abasheer noship $ */
3
4 -- Global constant holding package name
5 g_pkg_name constant varchar2(50) := 'WMS_DEPLOY';
6 -- g_item_flex_delimiter caches the item flexfield delimiter
7 g_item_flex_delimiter VARCHAR2(2);
8 -- g_item_segment_count caches the count of item flexfield segments
9 g_item_segment_count NUMBER;
10 --g_item_id caches the item_id passed to function get_client_code
11 g_item_id NUMBER;
12 --g_client_code caches the ct code of g_item_id.
13 g_client_code mtl_client_parameters.client_code%TYPE;
14
15
16 /* function returns the deployment mode based on the profile WMS_DEPLOYMENT_MODE
17 * 'I' - Integrated Deployment
18 * 'D' - Distributed (Standalone) Deployment
19 * 'L' - LSP Deployment
20 */
21 FUNCTION wms_deployment_mode RETURN VARCHAR2
22 IS
23 BEGIN
24 IF WMS_DEPLOY.g_wms_deployment_mode IS NULL THEN
25
26 CASE NVL(FND_PROFILE.VALUE('WMS_DEPLOYMENT_MODE'), 1)
27 WHEN 1 THEN g_wms_deployment_mode := 'I'; -- Integrated Mode
28 WHEN 2 THEN g_wms_deployment_mode := 'D'; -- Distributed Mode
29 WHEN 3 THEN g_wms_deployment_mode := 'L'; -- LSP Mode
30 ELSE g_wms_deployment_mode := 'I';
31 END CASE;
32 END IF;
33
34 RETURN g_wms_deployment_mode;
35 END wms_deployment_mode;
36
37 FUNCTION get_item_flex_delimiter
38 RETURN VARCHAR2 AS
39 BEGIN
40 IF g_item_flex_delimiter IS NULL THEN
41 BEGIN
42 SELECT concatenated_segment_delimiter
43 INTO g_item_flex_delimiter
44 FROM fnd_id_flex_structures_vl
45 WHERE (application_id=401)
46 AND (id_flex_code ='MSTK');
47 EXCEPTION
48 WHEN OTHERS THEN
49 g_item_flex_delimiter := NULL;
50 END;
51 END IF;
52 RETURN g_item_flex_delimiter;
53 END get_item_flex_delimiter;
54
55
56 FUNCTION get_item_flex_segment_count
57 RETURN NUMBER AS
58 BEGIN
59 IF g_item_segment_count IS NULL THEN
60 BEGIN
61 SELECT count(1)
62 INTO g_item_segment_count
63 FROM fnd_id_flex_segments_vl
64 WHERE (id_flex_num =101)
65 AND (id_flex_code ='MSTK')
66 AND (application_id=401);
67 EXCEPTION
68 WHEN OTHERS THEN
69 g_item_segment_count := 0;
70 END;
71 END IF;
72 RETURN g_item_segment_count;
73 END get_item_flex_segment_count;
74
75 /* function returns whether the item / transaction can be costed or not (Y/N)
76 * Takes input a record structure with inventory_item_id and organization_id
77 */
78 FUNCTION Costed_Txn (p_in_txn_rec IN t_in_txn_rec) RETURN VARCHAR2
79 IS
80 costed_flag VARCHAR2(1) := 'Y';
81 BEGIN
82 CASE wms_deployment_mode
83 WHEN 'I' THEN -- for Integration mode, always return 'Y'
84 RETURN costed_flag;
85 WHEN 'D' THEN -- for Distributed mode, transactions should never be costed
86 costed_flag := 'N';
87 RETURN costed_flag;
88 WHEN 'L' THEN -- for LSP mode check for Item Costed/Invoiced flag and (Outsourcer Definition)
89 BEGIN
90 SELECT 'N'
91 INTO costed_flag
92 FROM mtl_system_items
93 WHERE inventory_item_id = p_in_txn_rec.inventory_item_id
94 AND organization_id = p_in_txn_rec.organization_id
95 AND costing_enabled_flag = 'N'
96 AND invoiceable_item_flag = 'N';
97 EXCEPTION WHEN OTHERS THEN
98 costed_flag := 'Y';
99 END;
100 RETURN costed_flag;
101 END CASE;
102 END Costed_Txn;
103
104 /* Wrapper of Costed_Txn to obtain whether the item / transaction can be costed or not (Y/N)
105 * given Item_id and Org_id
106 */
107 FUNCTION Costed_Txn_For_Item (p_organization_id NUMBER,
108 p_inventory_item_id NUMBER
109 ) RETURN VARCHAR2 IS
110 l_in_txn_rec t_in_txn_rec;
111 BEGIN
112
113 l_in_txn_rec.inventory_item_id := p_inventory_item_id;
114 l_in_txn_rec.organization_id := p_organization_id;
115
116 RETURN (Costed_Txn (l_in_txn_rec));
117
118 END;
119
120 /* procedure returns the outsourcer/client information pertaining to transaction details provided
121 * Takes input a record structure with inventory_item_id and organization_id
122 */
123 procedure Get_Client_Info ( p_in_txn_rec IN t_in_txn_rec,
124 x_client_rec OUT NOCOPY t_client_rec,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2
128 ) IS
129
130 BEGIN
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 CASE wms_deployment_mode
134 WHEN 'I' THEN
135 x_client_rec.client_id := NULL;
136 x_client_rec.client_name := NULL;
137 WHEN 'D' THEN
138 x_client_rec.client_id := NULL;
139 x_client_rec.client_name := NULL;
140 WHEN 'L' THEN
141 x_client_rec.client_id := NULL;
142 x_client_rec.client_name := NULL;
143 END CASE;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
148 IF (FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
149 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, 'Get_Client_Info');
150 END IF;
151 END Get_Client_Info;
152
153
154
155 /* Wrapper of Get_Client_Info to obtain Outsourcer_id for a
156 * given Item_id and Org_id
157 */
158 procedure Get_Client_Info_For_Item (x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2,
161 p_organization_id NUMBER,
162 p_inventory_item_id NUMBER,
163 x_outsourcer_id OUT NOCOPY NUMBER
164 ) IS
165 l_in_txn_rec t_in_txn_rec;
166 l_client_rec t_client_rec;
167
168 BEGIN
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 l_in_txn_rec.inventory_item_id := p_inventory_item_id;
172 l_in_txn_rec.organization_id := p_organization_id;
173
174 Get_Client_Info(l_in_txn_rec, l_client_rec, x_return_status, x_msg_count, x_msg_data);
175
176 x_outsourcer_id := l_client_rec.client_id;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 IF (FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
182 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, 'Get_Client_Info_For_Item');
183 END IF;
184
185 END;
186
187
188
189 /* Returns the Item Category Id for a given outsourcer_id
190 */
191 procedure Get_Category_Info (x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2,
194 p_outsourcer_id NUMBER,
195 x_item_category_id OUT NOCOPY NUMBER
196 )IS
197 BEGIN
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200 CASE wms_deployment_mode
201 WHEN 'I' THEN
202 x_item_category_id := NULL; --API will not return anything in Integrated Mode
203 WHEN 'D' THEN
204 x_item_category_id := NULL; --API will not return anything in Distributed Mode
205 WHEN 'L' THEN
206 x_item_category_id := NULL; --API will return the Item Category Id stored in the Outcourcer schema
207 END CASE;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 IF (FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
213 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, 'Get_Category_Info');
214 END IF;
215 END;
216
217 /* ==================================================================================*
218 | Function : get_client_code returns the client code for the Item ID |
219 | Added for LSP Project |
220 | |
221 | Description : For getting the client code , to be used internally by Inventory |
222 | team and shipping team |
223 | Input Parameters: |
224 | p_item_id - The item ID for which the client code is needed |
225 *================================================================================== */
226
227 FUNCTION get_client_code
228 ( p_item_id NUMBER)
229 RETURN VARCHAR2
230 as
231 l_delimiter VARCHAR2(1);
232 l_client_code mtl_client_parameters.client_code%TYPE := NULL;
233 l_item_name mtl_system_items_b_kfv.concatenated_segments%TYPE;
234 begin
235 IF wms_deployment_mode = 'L' THEN
236 IF g_item_id = p_item_id THEN
237 -- returned the cached value
238 RETURN g_client_code;
239 ELSE
240
241 Begin
242 select concatenated_segments
243 into l_item_name
244 from mtl_system_items_b_kfv
245 where inventory_item_id=p_item_id
246 and rownum < 2;
247 Exception
248 when others then
249 l_item_name:=null;
250 End;
251 If l_item_name is not null then
252 l_delimiter := get_item_flex_delimiter;
253
254 if l_delimiter is not null THEN
255 IF (instr(l_item_name,l_delimiter,-1) <> 0) THEN
256 l_client_code := substr(l_item_name, instr(l_item_name,l_delimiter,-1)+1);
257 -- cache the value for next run
258 g_client_code := l_client_code;
259 g_item_id := p_item_id;
260 return l_client_code;
261 END IF;
262 end if;
263 end if;
264 END if; -- end IF g_item_id = p_item_id
265 end if; --end if wms_deployment_mode = 'L'
266 return l_client_code;
267 END get_client_code;
268
269 /* ==================================================================================*
270 | Procedure : get_client_details |
271 | Added for LSP Project |
272 | |
273 | Description : To validate passed client id, code and return name also, |
274 | to be used by Shipping Team |
275 | Input Parameters: |
276 | x_client_id - The client ID for which the details needs to be passed |
277 | x_client_code - The client Code for which the details needs to be passed|
278 | Output Parameters: |
279 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
280 | fnd_api.g_ret_sts_error, if error occurred |
281 | x_client_id - The client ID for which the details needs to be passed |
282 | x_client_code - The client Code for which the details needs to be passed|
283 | x_client_name - The client name corresponding to client ID fetched from |
284 | hz_parties |
285 *================================================================================== */
286
287
288 PROCEDURE get_client_details
289 (
290 x_client_id IN OUT NOCOPY MTL_CLIENT_PARAMETERS.CLIENT_ID%TYPE
291 , x_client_code IN OUT NOCOPY MTL_CLIENT_PARAMETERS.CLIENT_CODE%TYPE
292 , x_client_name OUT NOCOPY HZ_PARTIES.PARTY_NAME%TYPE
293 , x_return_status OUT NOCOPY VARCHAR2
294 )as
295
296 BEGIN
297 x_return_status := fnd_api.g_ret_sts_success;
298
299 BEGIN
300
301 IF x_client_id <> FND_API.G_MISS_NUM
302 THEN
303 BEGIN
304 SELECT client_id , client_code
305 INTO x_client_id , x_client_code
306 FROM mtl_client_parameters
307 WHERE client_id = x_client_id;
308 END;
309 ELSIF x_client_code <> FND_API.G_MISS_CHAR
310 THEN
311 BEGIN
312 SELECT client_id , client_code
313 INTO x_client_id , x_client_code
314 FROM mtl_client_parameters
315 WHERE client_code = x_client_code;
316 END;
317 ELSE
318 RAISE fnd_api.g_exc_error;
319 End IF;
320
321 BEGIN
322 SELECT client.party_name
323 INTO x_client_name
324 FROM hz_parties client, hz_cust_accounts cust_account
325 WHERE client.party_id = cust_account.party_id
326 AND cust_account.cust_account_id = x_client_id;
327 END;
328
329 EXCEPTION
330 WHEN OTHERS THEN
334 END get_client_details;
331 x_return_status := fnd_api.g_ret_sts_error;
332 END;
333
335
336 FUNCTION get_client_item
337 (
338 p_org_id NUMBER,
339 p_item_id NUMBER)
340 RETURN VARCHAR2 AS
341 l_delimiter VARCHAR2(1);
342 l_item_name VARCHAR2(800);
343 BEGIN
344 BEGIN
345 SELECT concatenated_segments
346 INTO l_item_name
347 FROM mtl_system_items_b_kfv
348 WHERE inventory_item_id = p_item_id
349 AND organization_id = p_org_id;
350 EXCEPTION
351 WHEN OTHERS THEN
352 l_item_name := NULL;
353 END;
354 IF wms_deployment_mode='L' THEN
355 IF l_item_name IS NOT NULL THEN
356 l_delimiter := get_item_flex_delimiter;
357 IF l_delimiter IS NOT NULL THEN
358 l_item_name := SUBSTR(l_item_name, 1, INSTR(l_item_name,l_delimiter,-1)-1);
359 END IF;
360 END IF;
361 END IF;
362 RETURN l_item_name;
363 END get_client_item;
364
365 FUNCTION get_item_client_name( p_item_id NUMBER)
366 RETURN VARCHAR2 AS
367 l_client_id mtl_client_parameters.client_id%TYPE;
368 l_client_code mtl_client_parameters.client_code%TYPE;
369 l_client_name varchar2(360);
370 l_return_status VARCHAR2(1);
371 BEGIN
372 IF wms_deployment_mode='L' THEN
373 IF p_item_id IS NOT NULL THEN
374 l_client_code:=get_client_code(p_item_id);
375 get_client_details(l_client_id,l_client_code,l_client_name,l_return_status);
376 END IF;
377 END IF;
378 RETURN l_client_name;
379 END get_item_client_name;
380
381 FUNCTION get_item_suffix_for_lov(p_concatenated_segments VARCHAR2)
382 RETURN VARCHAR2 AS
383 l_append varchar(2):='';
384 l_delimiter varchar2(1);
385 l_segcount number(2);
386 BEGIN
387 IF wms_deployment_mode ='L' THEN
388 l_delimiter := get_item_flex_delimiter;
389 l_segcount := get_item_flex_segment_count;
390 IF (LENGTH(p_concatenated_segments)-LENGTH(REPLACE(p_concatenated_segments,l_delimiter,''))) < (l_segcount -1) THEN
391 l_append := l_delimiter||'%';
392 ELSE
393 l_append := '';
394 END IF;
395 END IF;
396 RETURN l_append;
397 END get_item_suffix_for_lov;
398
399 FUNCTION get_po_client_code(p_po_header_id NUMBER)
400 RETURN VARCHAR2 AS
401 l_po_name po_headers_all.segment1%TYPE;
402 l_client_code mtl_client_parameters.client_code%TYPE := NULL;
403 l_delimiter VARCHAR2(1) := '-' ;
404 l_item_name VARCHAR2(800);
405 BEGIN
406 BEGIN
407 SELECT segment1
408 INTO l_po_name
409 FROM po_headers_all
410 WHERE po_header_id = p_po_header_id;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 l_po_name := NULL;
415 END;
416
417 IF wms_deployment_mode='L' then
418 IF l_po_name IS NOT NULL THEN
419 /* Bug 9255222: Deriving l_delimiter from Item KFF */
420
421 l_delimiter := get_item_flex_delimiter;
422
423 IF l_delimiter IS NOT NULL THEN
424 IF INSTR(l_po_name,l_delimiter,-1) <> 0 THEN
425 l_client_code := SUBSTR(l_po_name, INSTR(l_po_name,l_delimiter,-1)+1);
426 END IF;
427 END IF;
428 END IF;
429 END IF;
430 RETURN l_client_code;
431 END get_po_client_code;
432
433
434 FUNCTION get_po_client_name(p_po_header_id number)
435 RETURN VARCHAR2 AS
436 l_client_id mtl_client_parameters.client_id%TYPE;
437 l_client_code mtl_client_parameters.client_code%TYPE;
438 l_client_name VARCHAR2(360):='';
439 l_return_status VARCHAR2(1);
440 BEGIN
441 IF wms_deployment_mode='L' THEN
442 IF p_po_header_id IS NOT NULL THEN
443 l_client_code := get_po_client_code(p_po_header_id);
444 get_client_details(l_client_id,l_client_code,l_client_name,l_return_status);
445 END IF;
446 END IF;
447 RETURN l_client_name;
448 END get_po_client_name;
449
450 /* End of changes for LSP Project */
451
452 /* Changes for LSP Integration project */
453 procedure Get_Client_Item_Name ( x_item_id NUMBER,
454 x_org_id NUMBER,
455 x_item_name OUT NOCOPY VARCHAR2
456 ) IS
457
458 BEGIN
459 x_item_name := NULL;
460 IF wms_deployment_mode='L' then
461 x_item_name := get_client_item(x_item_id,x_org_id);
462 END IF;
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 x_item_name := NULL;
467 IF (FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
468 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, 'Get_Client_Info');
469 END IF;
470 END Get_Client_Item_Name;
471
472
473 FUNCTION get_client_po_num (p_po_header_id NUMBER)
474 RETURN VARCHAR2 AS
475 l_delimiter VARCHAR2(1);
476 l_po_num VARCHAR2(800);
477 BEGIN
478 BEGIN
479 SELECT segment1
480 INTO l_po_num
481 FROM po_headers_all
482 WHERE po_header_id = p_po_header_id;
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 l_po_num := NULL;
487 END;
488 IF wms_deployment_mode='L' THEN
489 IF l_po_num IS NOT NULL THEN
490 l_delimiter := get_item_flex_delimiter;
491 IF l_delimiter IS NOT NULL THEN
492 l_po_num := SUBSTR(l_po_num, 1, INSTR(l_po_num,l_delimiter,-1)-1);
493 END IF;
494 END IF;
495 END IF;
496 RETURN l_po_num;
497 END get_client_po_num;
498
499
500 procedure Get_Client_PONum_Info ( x_po_header_id NUMBER,
501 x_po_num OUT NOCOPY VARCHAR2
502 ) IS
503
504 BEGIN
505 x_po_num := NULL;
506 IF wms_deployment_mode='L' then
507 x_po_num := get_client_po_num(x_po_header_id);
508 ELSE SELECT segment1
509 INTO x_po_num
510 FROM po_headers_all
511 WHERE po_header_id = x_po_header_id;
512 END IF;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 x_po_num := NULL;
517
518 IF (FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
519 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, 'Get_Client_Info');
520 END IF;
521 END Get_Client_PONum_Info;
522 /* End of changes for LSP Integration project */
523
524 /*
525 ** Added function for bug 9274233
526 */
527
528 FUNCTION get_po_number (p_segment1 VARCHAR2) RETURN NUMBER
529 IS
530 l_delimiter VARCHAR2(1);
531 l_client_code mtl_client_parameters.client_code%TYPE;
532 l_po_number NUMBER;
533 BEGIN
534 IF wms_deployment_mode = 'L' THEN
535
536 l_delimiter := wms_deploy.get_item_flex_delimiter;
537
538 IF INSTR(p_segment1,l_delimiter) = 0 THEN
539
540 l_po_number := to_number(p_segment1);
541
542 ELSE
543
544 l_client_code := SUBSTR(p_segment1, INSTR(p_segment1,l_delimiter,-1)+1);
545
546 IF l_client_code IS NOT NULL THEN
547
548 l_po_number := to_number(substr(p_segment1,1,instr(p_segment1,l_delimiter,-1)-1));
549
550 ELSE
551
552 l_po_number := to_number(p_segment1);
553
554 END IF;
555 END IF;
556
557 ELSE
558 l_po_number := to_number(p_segment1);
559 END IF;
560
561 RETURN l_po_number;
562 EXCEPTION
563 WHEN INVALID_NUMBER THEN
564 RETURN -1;
565 WHEN OTHERS THEN
566 IF SQLCODE = '-6502' THEN
567 RETURN -1;
568 END IF;
569 END get_po_number;
570
571 /*
572 **End of bug 9274233
573 */
574
575 end WMS_DEPLOY;
[Home] [Help]