[Home] [Help]
PACKAGE BODY: APPS.POS_ASN_CREATE_PVT
Source
1 PACKAGE BODY POS_ASN_CREATE_PVT AS
2 /* $Header: POSVASNB.pls 120.4.12010000.2 2008/10/18 06:18:08 vchiran ship $*/
3 l_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 procedure log( p_level in number,
6 p_api_name in varchar2,
7 p_msg in varchar2);
8
9 procedure log( p_level in number,
10 p_api_name in varchar2,
11 p_msg in varchar2)
12 IS
13 l_module varchar2(2000);
14 BEGIN
15 /* Taken from Package FND_LOG
16 LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
17 LEVEL_ERROR CONSTANT NUMBER := 5;
18 LEVEL_EXCEPTION CONSTANT NUMBER := 4;
19 LEVEL_EVENT CONSTANT NUMBER := 3;
20 LEVEL_PROCEDURE CONSTANT NUMBER := 2;
21 LEVEL_STATEMENT CONSTANT NUMBER := 1;
22 */
23
24 IF(l_fnd_debug = 'Y')THEN
25 l_module := 'pos.plsql.pos_asn_create_pvt.'||p_api_name;
26
27 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
28 FND_LOG.string( LOG_LEVEL => p_level,
29 MODULE => l_module,
30 MESSAGE => p_msg);
31 END IF;
32
33 END IF;
34 END log;
35
36 PROCEDURE insert_msni (
37 p_api_version IN NUMBER
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_msg_count OUT NOCOPY NUMBER
40 , x_msg_data OUT NOCOPY VARCHAR2
41 , p_transaction_interface_id IN OUT NOCOPY NUMBER
42 , p_fm_serial_number IN VARCHAR2
43 , p_to_serial_number IN VARCHAR2
44 , p_po_line_loc_id IN NUMBER
45 , p_product_transaction_id IN OUT NOCOPY NUMBER
46 , p_origination_date IN DATE DEFAULT NULL
47 , p_status_id IN NUMBER DEFAULT NULL
48 , p_territory_code IN VARCHAR2 DEFAULT NULL
49 , p_serial_attribute_category IN VARCHAR2 DEFAULT NULL
50 , p_c_attribute1 IN VARCHAR2 DEFAULT NULL
51 , p_c_attribute2 IN VARCHAR2 DEFAULT NULL
52 , p_c_attribute3 IN VARCHAR2 DEFAULT NULL
53 , p_c_attribute4 IN VARCHAR2 DEFAULT NULL
54 , p_c_attribute5 IN VARCHAR2 DEFAULT NULL
55 , p_c_attribute6 IN VARCHAR2 DEFAULT NULL
56 , p_c_attribute7 IN VARCHAR2 DEFAULT NULL
57 , p_c_attribute8 IN VARCHAR2 DEFAULT NULL
58 , p_c_attribute9 IN VARCHAR2 DEFAULT NULL
59 , p_c_attribute10 IN VARCHAR2 DEFAULT NULL
60 , p_c_attribute11 IN VARCHAR2 DEFAULT NULL
61 , p_c_attribute12 IN VARCHAR2 DEFAULT NULL
62 , p_c_attribute13 IN VARCHAR2 DEFAULT NULL
63 , p_c_attribute14 IN VARCHAR2 DEFAULT NULL
64 , p_c_attribute15 IN VARCHAR2 DEFAULT NULL
65 , p_c_attribute16 IN VARCHAR2 DEFAULT NULL
66 , p_c_attribute17 IN VARCHAR2 DEFAULT NULL
67 , p_c_attribute18 IN VARCHAR2 DEFAULT NULL
68 , p_c_attribute19 IN VARCHAR2 DEFAULT NULL
69 , p_c_attribute20 IN VARCHAR2 DEFAULT NULL
70 , p_d_attribute1 IN DATE DEFAULT NULL
71 , p_d_attribute2 IN DATE DEFAULT NULL
72 , p_d_attribute3 IN DATE DEFAULT NULL
73 , p_d_attribute4 IN DATE DEFAULT NULL
74 , p_d_attribute5 IN DATE DEFAULT NULL
75 , p_d_attribute6 IN DATE DEFAULT NULL
76 , p_d_attribute7 IN DATE DEFAULT NULL
77 , p_d_attribute8 IN DATE DEFAULT NULL
78 , p_d_attribute9 IN DATE DEFAULT NULL
79 , p_d_attribute10 IN DATE DEFAULT NULL
80 , p_n_attribute1 IN NUMBER DEFAULT NULL
81 , p_n_attribute2 IN NUMBER DEFAULT NULL
82 , p_n_attribute3 IN NUMBER DEFAULT NULL
83 , p_n_attribute4 IN NUMBER DEFAULT NULL
84 , p_n_attribute5 IN NUMBER DEFAULT NULL
85 , p_n_attribute6 IN NUMBER DEFAULT NULL
86 , p_n_attribute7 IN NUMBER DEFAULT NULL
87 , p_n_attribute8 IN NUMBER DEFAULT NULL
88 , p_n_attribute9 IN NUMBER DEFAULT NULL
89 , p_n_attribute10 IN NUMBER DEFAULT NULL
90 , p_attribute_category IN VARCHAR2 DEFAULT NULL
91 , p_attribute1 IN VARCHAR2 DEFAULT NULL
92 , p_attribute2 IN VARCHAR2 DEFAULT NULL
93 , p_attribute3 IN VARCHAR2 DEFAULT NULL
94 , p_attribute4 IN VARCHAR2 DEFAULT NULL
95 , p_attribute5 IN VARCHAR2 DEFAULT NULL
96 , p_attribute6 IN VARCHAR2 DEFAULT NULL
97 , p_attribute7 IN VARCHAR2 DEFAULT NULL
98 , p_attribute8 IN VARCHAR2 DEFAULT NULL
99 , p_attribute9 IN VARCHAR2 DEFAULT NULL
100 , p_attribute10 IN VARCHAR2 DEFAULT NULL
101 , p_attribute11 IN VARCHAR2 DEFAULT NULL
102 , p_attribute12 IN VARCHAR2 DEFAULT NULL
103 , p_attribute13 IN VARCHAR2 DEFAULT NULL
104 , p_attribute14 IN VARCHAR2 DEFAULT NULL
105 , p_attribute15 IN VARCHAR2 DEFAULT NULL
106 )
107 IS
108 l_api_name varchar2(50) := 'insert_msni';
109 l_progress varchar2(20) := '000';
110 l_org_id number;
111 l_inventory_item_id number;
112 BEGIN
113 select
114 plla.ship_to_organization_id,
115 pla.item_id
116 into
117 l_org_id,
118 l_inventory_item_id
119 from po_lines_all pla,
120 po_line_locations_all plla
121 where plla.line_location_id = p_po_line_loc_id
122 and pla.po_line_id = plla.po_line_id;
123
124 l_progress := '001'||p_po_line_loc_id;
125
126 inv_rcv_integration_apis.insert_msni (
127 p_api_version => p_api_version
128 , x_return_status => x_return_status
129 , x_msg_count => x_msg_count
130 , x_msg_data => x_msg_data
131 , p_transaction_interface_id => p_transaction_interface_id
132 , p_fm_serial_number => p_fm_serial_number
133 , p_to_serial_number => p_to_serial_number
134 , p_organization_id => l_org_id
135 , p_inventory_item_id => l_inventory_item_id
136 , p_product_transaction_id => p_product_transaction_id
137 , p_product_code => 'RCV'
138 , p_origination_date => p_origination_date
139 , p_status_id => p_status_id
140 , p_territory_code => p_territory_code
141 , p_serial_attribute_category => p_serial_attribute_category
142 , p_c_attribute1 => p_c_attribute1
143 , p_c_attribute2 => p_c_attribute2
144 , p_c_attribute3 => p_c_attribute3
145 , p_c_attribute4 => p_c_attribute4
146 , p_c_attribute5 => p_c_attribute5
147 , p_c_attribute6 => p_c_attribute6
148 , p_c_attribute7 => p_c_attribute7
149 , p_c_attribute8 => p_c_attribute8
150 , p_c_attribute9 => p_c_attribute9
151 , p_c_attribute10 => p_c_attribute10
152 , p_c_attribute11 => p_c_attribute11
153 , p_c_attribute12 => p_c_attribute12
154 , p_c_attribute13 => p_c_attribute13
155 , p_c_attribute14 => p_c_attribute14
156 , p_c_attribute15 => p_c_attribute15
157 , p_c_attribute16 => p_c_attribute16
158 , p_c_attribute17 => p_c_attribute17
159 , p_c_attribute18 => p_c_attribute18
160 , p_c_attribute19 => p_c_attribute19
161 , p_c_attribute20 => p_c_attribute20
162 , p_d_attribute1 => p_d_attribute1
163 , p_d_attribute2 => p_d_attribute2
164 , p_d_attribute3 => p_d_attribute3
165 , p_d_attribute4 => p_d_attribute4
166 , p_d_attribute5 => p_d_attribute5
167 , p_d_attribute6 => p_d_attribute6
168 , p_d_attribute7 => p_d_attribute7
169 , p_d_attribute8 => p_d_attribute8
170 , p_d_attribute9 => p_d_attribute9
171 , p_d_attribute10 => p_d_attribute10
172 , p_n_attribute1 => p_n_attribute1
173 , p_n_attribute2 => p_n_attribute2
174 , p_n_attribute3 => p_n_attribute3
175 , p_n_attribute4 => p_n_attribute4
176 , p_n_attribute5 => p_n_attribute5
177 , p_n_attribute6 => p_n_attribute6
178 , p_n_attribute7 => p_n_attribute7
179 , p_n_attribute8 => p_n_attribute8
180 , p_n_attribute9 => p_n_attribute9
181 , p_n_attribute10 => p_n_attribute10
182 , p_attribute_category => p_attribute_category
183 , p_attribute1 => p_attribute1
184 , p_attribute2 => p_attribute2
185 , p_attribute3 => p_attribute3
186 , p_attribute4 => p_attribute4
187 , p_attribute5 => p_attribute5
188 , p_attribute6 => p_attribute6
189 , p_attribute7 => p_attribute7
190 , p_attribute8 => p_attribute8
191 , p_attribute9 => p_attribute9
192 , p_attribute10 => p_attribute10
193 , p_attribute11 => p_attribute11
194 , p_attribute12 => p_attribute12
195 , p_attribute13 => p_attribute13
196 , p_attribute14 => p_attribute14
197 , p_attribute15 => p_attribute15
198 , p_att_exist => 'N'
199 );
200
201 exception when others then
202 x_return_status := 'U';
203 x_msg_data := 'Unexpected Error:'||sqlerrm;
204 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
205 END insert_msni;
206
207 /**
208 * Public Procedure: insert_mtli
209 * Requires: p_api_version, p_transaction_interface_id, p_lot_number
210 * p_transaction_quantity, p_transaction_uom, p_po_line_loc_id,
211 * p_product_transaction_id
212 * Effects: This procedure inserts the record into MTL_TRANSACTION_LOTS_INTERFACE
213 * table during the creation of ASN.
214 * Returns: x_return_status, x_msg_count, x_msg_data, p_transaction_interface_id,
215 * x_serial_transaction_temp_id, p_product_transaction_id
216 *
217 * Bugs Fixed : 7476612 - Modified the code to get the Primary UOM from
218 * po_uom_s.get_primary_uom by passing item_id, org_id, and
219 * transaction_uom.
220 */
221 PROCEDURE insert_mtli (
222 p_api_version IN NUMBER
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_count OUT NOCOPY NUMBER
225 , x_msg_data OUT NOCOPY VARCHAR2
226 , p_transaction_interface_id IN OUT NOCOPY NUMBER
227 , p_lot_number IN VARCHAR2
228 , p_transaction_quantity IN NUMBER
229 , p_transaction_uom IN VARCHAR2
230 , p_po_line_loc_id IN NUMBER
231 , x_serial_transaction_temp_id OUT NOCOPY NUMBER
232 , p_product_transaction_id IN OUT NOCOPY NUMBER
233 , p_vendor_id IN NUMBER DEFAULT NULL
234 , p_grade_code IN VARCHAR2 DEFAULT NULL
235 , p_origination_date IN DATE DEFAULT NULL
236 , p_date_code IN VARCHAR2 DEFAULT NULL
237 , p_status_id IN NUMBER DEFAULT NULL
238 , p_change_date IN DATE DEFAULT NULL
239 , p_age IN NUMBER DEFAULT NULL
240 , p_retest_date IN DATE DEFAULT NULL
241 , p_maturity_date IN DATE DEFAULT NULL
242 , p_item_size IN NUMBER DEFAULT NULL
243 , p_color IN VARCHAR2 DEFAULT NULL
244 , p_volume IN NUMBER DEFAULT NULL
245 , p_volume_uom IN VARCHAR2 DEFAULT NULL
246 , p_place_of_origin IN VARCHAR2 DEFAULT NULL
247 , p_best_by_date IN DATE DEFAULT NULL
248 , p_length IN NUMBER DEFAULT NULL
249 , p_length_uom IN VARCHAR2 DEFAULT NULL
250 , p_recycled_content IN NUMBER DEFAULT NULL
251 , p_thickness IN NUMBER DEFAULT NULL
252 , p_thickness_uom IN VARCHAR2 DEFAULT NULL
253 , p_width IN NUMBER DEFAULT NULL
254 , p_width_uom IN VARCHAR2 DEFAULT NULL
255 , p_curl_wrinkle_fold IN VARCHAR2 DEFAULT NULL
256 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
257 , p_territory_code IN VARCHAR2 DEFAULT NULL
258 , p_vendor_name IN VARCHAR2 DEFAULT NULL
259 , p_lot_attribute_category IN VARCHAR2 DEFAULT NULL
260 , p_c_attribute1 IN VARCHAR2 DEFAULT NULL
261 , p_c_attribute2 IN VARCHAR2 DEFAULT NULL
262 , p_c_attribute3 IN VARCHAR2 DEFAULT NULL
263 , p_c_attribute4 IN VARCHAR2 DEFAULT NULL
264 , p_c_attribute5 IN VARCHAR2 DEFAULT NULL
265 , p_c_attribute6 IN VARCHAR2 DEFAULT NULL
266 , p_c_attribute7 IN VARCHAR2 DEFAULT NULL
267 , p_c_attribute8 IN VARCHAR2 DEFAULT NULL
268 , p_c_attribute9 IN VARCHAR2 DEFAULT NULL
269 , p_c_attribute10 IN VARCHAR2 DEFAULT NULL
270 , p_c_attribute11 IN VARCHAR2 DEFAULT NULL
271 , p_c_attribute12 IN VARCHAR2 DEFAULT NULL
272 , p_c_attribute13 IN VARCHAR2 DEFAULT NULL
273 , p_c_attribute14 IN VARCHAR2 DEFAULT NULL
274 , p_c_attribute15 IN VARCHAR2 DEFAULT NULL
275 , p_c_attribute16 IN VARCHAR2 DEFAULT NULL
276 , p_c_attribute17 IN VARCHAR2 DEFAULT NULL
277 , p_c_attribute18 IN VARCHAR2 DEFAULT NULL
278 , p_c_attribute19 IN VARCHAR2 DEFAULT NULL
279 , p_c_attribute20 IN VARCHAR2 DEFAULT NULL
280 , p_d_attribute1 IN DATE DEFAULT NULL
281 , p_d_attribute2 IN DATE DEFAULT NULL
282 , p_d_attribute3 IN DATE DEFAULT NULL
283 , p_d_attribute4 IN DATE DEFAULT NULL
284 , p_d_attribute5 IN DATE DEFAULT NULL
285 , p_d_attribute6 IN DATE DEFAULT NULL
286 , p_d_attribute7 IN DATE DEFAULT NULL
287 , p_d_attribute8 IN DATE DEFAULT NULL
288 , p_d_attribute9 IN DATE DEFAULT NULL
289 , p_d_attribute10 IN DATE DEFAULT NULL
290 , p_n_attribute1 IN NUMBER DEFAULT NULL
291 , p_n_attribute2 IN NUMBER DEFAULT NULL
292 , p_n_attribute3 IN NUMBER DEFAULT NULL
293 , p_n_attribute4 IN NUMBER DEFAULT NULL
294 , p_n_attribute5 IN NUMBER DEFAULT NULL
295 , p_n_attribute6 IN NUMBER DEFAULT NULL
296 , p_n_attribute7 IN NUMBER DEFAULT NULL
297 , p_n_attribute8 IN NUMBER DEFAULT NULL
298 , p_n_attribute9 IN NUMBER DEFAULT NULL
299 , p_n_attribute10 IN NUMBER DEFAULT NULL
300 , p_attribute_category IN VARCHAR2 DEFAULT NULL
301 , p_attribute1 IN VARCHAR2 DEFAULT NULL
302 , p_attribute2 IN VARCHAR2 DEFAULT NULL
303 , p_attribute3 IN VARCHAR2 DEFAULT NULL
304 , p_attribute4 IN VARCHAR2 DEFAULT NULL
305 , p_attribute5 IN VARCHAR2 DEFAULT NULL
306 , p_attribute6 IN VARCHAR2 DEFAULT NULL
307 , p_attribute7 IN VARCHAR2 DEFAULT NULL
308 , p_attribute8 IN VARCHAR2 DEFAULT NULL
309 , p_attribute9 IN VARCHAR2 DEFAULT NULL
310 , p_attribute10 IN VARCHAR2 DEFAULT NULL
311 , p_attribute11 IN VARCHAR2 DEFAULT NULL
312 , p_attribute12 IN VARCHAR2 DEFAULT NULL
313 , p_attribute13 IN VARCHAR2 DEFAULT NULL
314 , p_attribute14 IN VARCHAR2 DEFAULT NULL
315 , p_attribute15 IN VARCHAR2 DEFAULT NULL
316 )
317 IS
318 l_api_name varchar2(50) := 'insert_mtli';
319 l_progress varchar2(20) := '000';
320 l_primary_quantity number;
321 l_org_id number;
322 l_inventory_item_id number;
323 l_expiration_date date;
324 l_serial_txn_temp_id number;
325 l_uom po_lines_all.UNIT_MEAS_LOOKUP_CODE%type;
326 BEGIN
327 -- Bug 7476612 - Start
328 select
329 plla.ship_to_organization_id,
330 pla.item_id
331 --pla.UNIT_MEAS_LOOKUP_CODE
332 into
333 l_org_id,
334 l_inventory_item_id
335 --l_uom
336 from
337 po_lines_all pla,
338 po_line_locations_all plla
339 where plla.line_location_id = p_po_line_loc_id
340 and plla.po_line_id = pla.po_line_id;
341
342 l_uom := po_uom_s.get_primary_uom(l_inventory_item_id,l_org_id,p_transaction_uom);
343 -- Bug 7476612 - End
344
345 select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_serial_txn_temp_id from dual;
346
347 PO_UOM_S.uom_convert (
348 from_quantity => p_transaction_quantity,
349 from_uom => p_transaction_uom,
350 item_id => l_inventory_item_id ,
351 to_uom => l_uom,
352 to_quantity => l_primary_quantity );
353
354
355 l_progress := '001'||p_po_line_loc_id;
356 inv_rcv_integration_apis.insert_mtli(
357 p_api_version => p_api_version
358 , x_return_status => x_return_status
359 , x_msg_count => x_msg_count
360 , x_msg_data => x_msg_data
361 , p_transaction_interface_id => p_transaction_interface_id
362 , p_lot_number => p_lot_number
363 , p_transaction_quantity => p_transaction_quantity
364 , p_primary_quantity => l_primary_quantity
365 , p_organization_id => l_org_id
366 , p_inventory_item_id => l_inventory_item_id
367 , p_expiration_date => l_expiration_date
368 , x_serial_transaction_temp_id => x_serial_transaction_temp_id
369 , p_product_transaction_id => p_product_transaction_id
370 , p_product_code => 'RCV'
371 , p_vendor_id => p_vendor_id
372 , p_grade_code => p_grade_code
373 , p_origination_date => p_origination_date
374 , p_date_code => p_date_code
375 , p_status_id => p_status_id
376 , p_change_date => p_change_date
377 , p_age => p_age
378 , p_retest_date => p_retest_date
379 , p_maturity_date => p_maturity_date
380 , p_item_size => p_item_size
381 , p_color => p_color
382 , p_volume => p_volume
383 , p_volume_uom => p_volume_uom
384 , p_place_of_origin => p_place_of_origin
385 , p_best_by_date => p_best_by_date
386 , p_length => p_length
387 , p_length_uom => p_length_uom
388 , p_recycled_content => p_recycled_content
389 , p_thickness => p_thickness
390 , p_thickness_uom => p_thickness_uom
391 , p_width => p_width
392 , p_width_uom => p_width_uom
393 , p_curl_wrinkle_fold => p_curl_wrinkle_fold
394 , p_supplier_lot_number => p_supplier_lot_number
395 , p_territory_code => p_territory_code
396 , p_vendor_name => p_vendor_name
397 , p_lot_attribute_category => p_lot_attribute_category
398 , p_c_attribute1 => p_c_attribute1
399 , p_c_attribute2 => p_c_attribute2
400 , p_c_attribute3 => p_c_attribute3
401 , p_c_attribute4 => p_c_attribute4
402 , p_c_attribute5 => p_c_attribute5
403 , p_c_attribute6 => p_c_attribute6
404 , p_c_attribute7 => p_c_attribute7
405 , p_c_attribute8 => p_c_attribute8
406 , p_c_attribute9 => p_c_attribute9
407 , p_c_attribute10 => p_c_attribute10
408 , p_c_attribute11 => p_c_attribute11
409 , p_c_attribute12 => p_c_attribute12
410 , p_c_attribute13 => p_c_attribute13
411 , p_c_attribute14 => p_c_attribute14
412 , p_c_attribute15 => p_c_attribute15
413 , p_c_attribute16 => p_c_attribute16
414 , p_c_attribute17 => p_c_attribute17
415 , p_c_attribute18 => p_c_attribute18
416 , p_c_attribute19 => p_c_attribute19
417 , p_c_attribute20 => p_c_attribute20
418 , p_d_attribute1 => p_d_attribute1
419 , p_d_attribute2 => p_d_attribute2
420 , p_d_attribute3 => p_d_attribute3
421 , p_d_attribute4 => p_d_attribute4
422 , p_d_attribute5 => p_d_attribute5
423 , p_d_attribute6 => p_d_attribute6
424 , p_d_attribute7 => p_d_attribute7
425 , p_d_attribute8 => p_d_attribute8
426 , p_d_attribute9 => p_d_attribute9
427 , p_d_attribute10 => p_d_attribute10
428 , p_n_attribute1 => p_n_attribute1
429 , p_n_attribute2 => p_n_attribute2
430 , p_n_attribute3 => p_n_attribute3
431 , p_n_attribute4 => p_n_attribute4
432 , p_n_attribute5 => p_n_attribute5
433 , p_n_attribute6 => p_n_attribute6
434 , p_n_attribute7 => p_n_attribute7
435 , p_n_attribute8 => p_n_attribute8
436 , p_n_attribute9 => p_n_attribute9
437 , p_n_attribute10 => p_n_attribute10
438 , p_attribute_category => p_attribute_category
439 , p_attribute1 => p_attribute1
440 , p_attribute2 => p_attribute2
441 , p_attribute3 => p_attribute3
442 , p_attribute4 => p_attribute4
443 , p_attribute5 => p_attribute5
444 , p_attribute6 => p_attribute6
445 , p_attribute7 => p_attribute7
446 , p_attribute8 => p_attribute8
447 , p_attribute9 => p_attribute9
448 , p_attribute10 => p_attribute10
449 , p_attribute11 => p_attribute11
450 , p_attribute12 => p_attribute12
451 , p_attribute13 => p_attribute13
452 , p_attribute14 => p_attribute14
453 , p_attribute15 => p_attribute15
454 , p_att_exist => 'N'
455 );
456
457
458 exception when others then
459 x_return_status := 'U';
460 x_msg_data := 'Unexpected Error:'||sqlerrm;
461 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
462 END insert_mtli;
463
464 procedure insert_wlpni
465 (p_api_version IN NUMBER
466 , x_return_status OUT NOCOPY VARCHAR2
467 , x_msg_count OUT NOCOPY NUMBER
468 , x_msg_data OUT NOCOPY VARCHAR2
469 , p_po_line_loc_ID IN NUMBER
470 , p_license_plate_number IN VARCHAR2
471 , p_LPN_GROUP_ID IN NUMBER
472 , p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
473 )
474 IS
475 l_api_name varchar2(50) := 'insert_wlpni';
476 l_progress varchar2(20) := '000';
477 l_org_id number;
478 l_exist number;
479 l_parent_lpn wms_lpn_interface.parent_license_plate_number%type;
480
481 cursor l_lpn_exist_csr(p_lpn varchar2, p_grp_id number)
482 is
483 select 1, parent_license_plate_number
484 from wms_lpn_interface
485 where license_plate_number = p_lpn
486 and source_group_id = p_grp_id;
487
488 BEGIN
489 select plla.ship_to_organization_id
490 into l_org_id
491 from po_line_locations_all plla
492 where line_location_id = p_po_line_loc_id;
493
494 if(p_parent_license_plate_number is null) then
495 --Parent LPN is null ==> Insert LPN record, if not exist yet
496 --Check if LPN already exist
497 open l_lpn_exist_csr(p_license_plate_number, p_lpn_group_id);
498 fetch l_lpn_exist_csr into l_exist, l_parent_lpn;
499 close l_lpn_exist_csr;
500
501 if(l_exist is null) then
502 --If LPN does not exist, insert
503 inv_rcv_integration_apis.insert_wlpni(
504 p_api_version => p_api_version,
505 x_return_status => x_return_status,
506 x_msg_count => x_msg_count,
507 x_msg_data => x_msg_data,
508 p_ORGANIZATION_ID => l_ORG_ID,
509 p_LPN_ID => null,
510 p_license_plate_number => p_license_plate_number,
511 p_LPN_GROUP_ID => p_LPN_GROUP_ID,
512 p_PARENT_LICENSE_PLATE_NUMBER => null);
513 end if;
514 else
515 --Parent LPN is not null ==> 1. Insert new record with LPN and Parent LPN or update existing LPN with Parent LPN or return error if existing LPN has other Parent LPN
516 -- 2. Insert new record for Parent LPN if does not exist
517 --Check if LPN already exist
518 l_exist := null;
519 open l_lpn_exist_csr(p_license_plate_number, p_lpn_group_id);
520 fetch l_lpn_exist_csr into l_exist, l_parent_lpn;
521 close l_lpn_exist_csr;
522
523 if(l_exist is null) then
524 --If LPN does not exist, insert
525 inv_rcv_integration_apis.insert_wlpni(
526 p_api_version => p_api_version,
527 x_return_status => x_return_status,
528 x_msg_count => x_msg_count,
529 x_msg_data => x_msg_data,
530 p_ORGANIZATION_ID => l_ORG_ID,
531 p_LPN_ID => null,
532 p_license_plate_number => p_license_plate_number,
533 p_LPN_GROUP_ID => p_LPN_GROUP_ID,
534 p_PARENT_LICENSE_PLATE_NUMBER => p_parent_license_plate_number);
535 elsif(l_parent_lpn is not null AND l_parent_lpn <> p_parent_license_plate_number) then
536 --Existing LPN has different parent_lpn
537 x_return_status := 'E';
538 x_msg_data := 'This LPN Child Parent relationship error should have been caught in the UI. A-->X, A-->Y exist';
539 else
540 --Existing LPN has NO Parent LPN
541 update wms_lpn_interface
542 set parent_license_plate_number = p_parent_license_plate_number
543 where source_group_id = p_lpn_group_id
544 and license_plate_number = p_license_plate_number;
545 end if;
546
547 --To see if we need to insert new record for the Parent LPN
548 l_exist := null;
549 open l_lpn_exist_csr(p_parent_license_plate_number, p_lpn_group_id);
550 fetch l_lpn_exist_csr into l_exist, l_parent_lpn;
551 close l_lpn_exist_csr;
552 if(l_exist is null) then
553 --Parent LPN as LPN does NOT exist ==> Insert
554 inv_rcv_integration_apis.insert_wlpni(
555 p_api_version => p_api_version,
556 x_return_status => x_return_status,
557 x_msg_count => x_msg_count,
558 x_msg_data => x_msg_data,
559 p_ORGANIZATION_ID => l_ORG_ID,
560 p_LPN_ID => null,
561 p_license_plate_number => p_parent_license_plate_number,
562 p_LPN_GROUP_ID => p_LPN_GROUP_ID,
563 p_PARENT_LICENSE_PLATE_NUMBER => null);
564
565 end if;
566
567 end if;
568
569 EXCEPTION WHEN OTHERS THEN
570 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
571 x_return_status := 'U';
572 x_msg_data := 'Unexpected Error:'||sqlerrm;
573 END insert_wlpni;
574
575 procedure ValidateSerialRange( p_api_version in number,
576 x_return_status out nocopy varchar2,
577 p_fm_serial_number in varchar2,
578 p_to_serial_number in varchar2,
579 p_quantity in number,
580 p_lot_number in varchar2,
581 p_line_loc_id in number,
582 x_return_code out nocopy varchar2,
583 x_return_msg out nocopy varchar2)
584 IS
585 l_api_name varchar2(50) := 'ValidateSerialRange';
586 l_progress varchar2(20) := '000';
587 l_valid_sr boolean;
588 l_ret_status varchar2(1);
589 l_ret_count number;
590 l_ret_data varchar2(2000);
591 l_val_mode number;
592 l_org_id number;
593 l_item_id number;
594 l_revision number;
595 l_to_serial_number mtl_serial_numbers_interface.to_serial_number%type;
596 BEGIN
597 l_to_serial_number := p_to_serial_number;
598
599 select
600 plla.ship_to_organization_id,
601 pla.item_id,
602 pla.item_revision,
603 decode(msi.serial_number_control_code,2,inv_rcv_integration_apis.G_EXISTS_ONLY,inv_rcv_integration_apis.G_EXISTS_OR_CREATE)
604 into
605 l_org_id,
606 l_item_id,
607 l_revision,
608 l_val_mode
609 from
610 po_lines_all pla,
611 mtl_system_items msi,
612 po_line_locations_all plla
613 where pla.item_id = msi.inventory_item_id
614 and plla.ship_to_organization_id = msi.organization_id
615 and plla.line_location_id = p_line_loc_id
616 and plla.po_line_id = pla.po_line_id;
617
618 l_valid_sr := inv_rcv_integration_apis.validate_serial_range (
619 p_api_version => 1.0
620 , x_return_status => l_ret_status
621 , x_msg_count => l_ret_count
622 , x_msg_data => l_ret_data
623 , p_validation_mode => l_val_mode
624 , p_org_id => l_org_id
625 , p_inventory_item_id => l_item_id
626 , p_quantity => p_quantity
627 , p_revision => l_revision
628 , p_lot_number => p_lot_number
629 , p_fm_serial_number => p_fm_serial_number
630 , p_to_serial_number => l_to_serial_number
631 , p_txn_type => inv_rcv_integration_apis.G_SHIP) ;
632
633 if(l_valid_sr = true) then
634 x_return_code := 'T';
635 else
636 x_return_code := 'F';
637 x_return_msg := fnd_msg_pub.get(l_ret_count,'F');
638 end if;
639 x_return_status := FND_API.G_RET_STS_SUCCESS;
640
641 exception when others then
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 x_return_msg := 'Unexpected Error at POSVASNB.pls.ValidateSerialRange:'||sqlerrm;
644 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
645 END ValidateSerialRange;
646
647 procedure ValidateLpn( p_api_version in number,
648 x_return_status out nocopy varchar2,
649 p_lpn in varchar2,
650 p_line_loc_id in number,
651 x_return_code out nocopy varchar2,
652 x_return_msg out nocopy varchar2)
653 IS
654 l_api_name varchar2(50) := 'ValidateLpn';
655 l_progress varchar2(20) := '000';
656 l_return_status varchar2(1);
657 l_msg_count number;
658 l_msg_data varchar2(2000);
659 l_ans boolean;
660 l_org_id number;
661 l_lpn_id number;
662 l_lpn_context wms_license_plate_numbers.lpn_context%type;
663 l_error_msg varchar2(2000);
664 BEGIN
665
666 select
667 plla.ship_to_organization_id
668 into
669 l_org_id
670 from
671 po_line_locations_all plla
672 where plla.line_location_id = p_line_loc_id;
673
674 l_ans := inv_rcv_integration_apis.validate_lpn
675 (p_api_version => 1.0
676 , x_return_status => l_return_status
677 , x_msg_count => l_msg_count
678 , x_msg_data => l_msg_data
679 , p_validation_mode => inv_rcv_integration_apis.G_EXISTS_OR_CREATE
680 , p_org_id => l_org_id
681 , p_lpn_id => l_lpn_id
682 , p_lpn => p_lpn);
683
684 --the inv api does not check for context value
685 if(l_ans = true) then
686 select lpn_context into l_lpn_context
687 from wms_license_plate_numbers
688 where lpn_id = l_lpn_id;
689
690 --can only reuse the LPNs that have a context of 5
691 if(l_lpn_context is not null AND l_lpn_context <> 5) then
692 fnd_message.set_name('WMS', 'WMS_CONT_DUPLICATE_LPN');
693 fnd_msg_pub.ADD;
694 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
695 fnd_msg_pub.ADD;
696 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
697 p_data => l_msg_data);
698 l_ans := false;
699 end if;
700 end if;
701
702 if(l_ans = true ) then
703 x_return_code := 'T';
704 else
705 x_return_code := 'F';
706 for i in 1..l_msg_count
707 loop
708 l_error_msg := l_error_msg || ' ' || fnd_msg_pub.get(l_msg_count-i+1,'F');
709 end loop;
710 x_return_msg := l_error_msg;
711 end if;
712
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 exception when others then
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 x_return_msg := 'Unexpected Error at POSVASNB.pls.ValidateLpn:'||sqlerrm;
717 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
718 END ValidateLpn;
719
720 procedure ValidateLot(
721 p_api_version in number,
722 x_return_status out nocopy varchar2,
723 p_lot_number in varchar2,
724 p_line_loc_id in number,
725 p_validation_mode in number,
726
727 p_lot_attribute_category in varchar2 ,
728 p_c_attributes_tbl in PO_TBL_VARCHAR2000,
729 p_n_attributes_tbl in PO_TBL_NUMBER,
730 p_d_attributes_tbl in PO_TBL_DATE,
731 p_grade_code in varchar2,
732 p_origination_date in date,
733 p_date_code in varchar2,
734 p_status_id in number,
735 p_change_date in date,
736 p_age in number,
737 p_retest_date in date,
738 p_maturity_date in date,
739 p_item_size in number,
740 p_color in varchar2,
741 p_volume in number,
742 p_volume_uom in varchar2,
743 p_place_of_origin in varchar2,
744 p_best_by_date in date,
745 p_length in number,
746 p_length_uom in varchar2,
747 p_recycled_content in number,
748 p_thickness in number,
749 p_thickness_uom in varchar2,
750 p_width in number,
751 p_width_uom in varchar2,
752 p_territory_code in varchar2,
753 p_supplier_lot_number in varchar2,
754 p_vendor_name in varchar2,
755
756 x_return_code out nocopy varchar2,
757 x_return_msg out nocopy varchar2,
758 x_is_new_lot out nocopy varchar2)
759 IS
760 l_c_attributes_tbl inv_lot_api_pub.char_tbl;
761 l_n_attributes_tbl inv_lot_api_pub.number_tbl;
762 l_d_attributes_tbl inv_lot_api_pub.date_tbl;
763 l_api_name varchar2(50) := 'ValidateLot';
764 l_progress varchar2(20) := '000';
765 l_return_status varchar2(1);
766 l_msg_count number;
767 l_msg_data varchar2(2000);
768 l_ans boolean;
769 l_org_id number;
770 l_item_id number;
771 l_exp_date date;
772 BEGIN
773
774 select
775 plla.ship_to_organization_id,
776 pla.item_id,
777 pla.expiration_date
778 into
779 l_org_id,
780 l_item_id,
781 l_exp_date
782 from
783 po_lines_all pla,
784 po_line_locations_all plla
785 where plla.line_location_id = p_line_loc_id
786 and plla.po_line_id = pla.po_line_id;
787
788 l_c_attributes_tbl(1) := p_c_attributes_tbl(1);
789 l_c_attributes_tbl(2) := p_c_attributes_tbl(2);
790 l_c_attributes_tbl(3) := p_c_attributes_tbl(3);
791 l_c_attributes_tbl(4) := p_c_attributes_tbl(4);
792 l_c_attributes_tbl(5) := p_c_attributes_tbl(5);
793 l_c_attributes_tbl(6) := p_c_attributes_tbl(6);
794 l_c_attributes_tbl(7) := p_c_attributes_tbl(7);
795 l_c_attributes_tbl(8) := p_c_attributes_tbl(8);
796 l_c_attributes_tbl(9) := p_c_attributes_tbl(9);
797 l_c_attributes_tbl(10) := p_c_attributes_tbl(10);
798 l_c_attributes_tbl(11) := p_c_attributes_tbl(11);
799 l_c_attributes_tbl(12) := p_c_attributes_tbl(12);
800 l_c_attributes_tbl(13) := p_c_attributes_tbl(13);
801 l_c_attributes_tbl(14) := p_c_attributes_tbl(14);
802 l_c_attributes_tbl(15) := p_c_attributes_tbl(15);
803 l_c_attributes_tbl(16) := p_c_attributes_tbl(16);
804 l_c_attributes_tbl(17) := p_c_attributes_tbl(17);
805 l_c_attributes_tbl(18) := p_c_attributes_tbl(18);
806 l_c_attributes_tbl(19) := p_c_attributes_tbl(19);
807 l_c_attributes_tbl(20) := p_c_attributes_tbl(20);
808
809 l_n_attributes_tbl(1) := p_n_attributes_tbl(1);
810 l_n_attributes_tbl(2) := p_n_attributes_tbl(2);
811 l_n_attributes_tbl(3) := p_n_attributes_tbl(3);
812 l_n_attributes_tbl(4) := p_n_attributes_tbl(4);
813 l_n_attributes_tbl(5) := p_n_attributes_tbl(5);
814 l_n_attributes_tbl(6) := p_n_attributes_tbl(6);
815 l_n_attributes_tbl(7) := p_n_attributes_tbl(7);
816 l_n_attributes_tbl(8) := p_n_attributes_tbl(8);
817 l_n_attributes_tbl(9) := p_n_attributes_tbl(9);
818 l_n_attributes_tbl(10) := p_n_attributes_tbl(10);
819
820
821 l_d_attributes_tbl(1) := p_d_attributes_tbl(1);
822 l_d_attributes_tbl(2) := p_d_attributes_tbl(2);
823 l_d_attributes_tbl(3) := p_d_attributes_tbl(3);
824 l_d_attributes_tbl(4) := p_d_attributes_tbl(4);
825 l_d_attributes_tbl(5) := p_d_attributes_tbl(5);
826 l_d_attributes_tbl(6) := p_d_attributes_tbl(6);
827 l_d_attributes_tbl(7) := p_d_attributes_tbl(7);
828 l_d_attributes_tbl(8) := p_d_attributes_tbl(8);
829 l_d_attributes_tbl(9) := p_d_attributes_tbl(9);
830 l_d_attributes_tbl(10) := p_d_attributes_tbl(10);
831
832
833
834 l_ans := inv_rcv_integration_apis.validate_lot_number(
835 p_api_version => 1
836 , p_init_msg_lst => fnd_api.g_false
837 , x_return_status => l_return_status
838 , x_msg_count => l_msg_count
839 , x_msg_data => l_msg_data
840 , x_is_new_lot => x_is_new_lot
841 , p_validation_mode => p_validation_mode
842 , p_org_id => l_org_id
843 , p_inventory_item_id => l_item_id
844 , p_lot_number => p_lot_number
845 , p_expiration_date => l_exp_date
846 , p_txn_type => inv_rcv_integration_apis.G_SHIP
847
848 , p_lot_attribute_category => p_lot_attribute_category
849 , p_c_attributes_tbl => l_c_attributes_tbl
850 , p_n_attributes_tbl => l_n_attributes_tbl
851 , p_d_attributes_tbl => l_d_attributes_tbl
852 , p_grade_code => p_grade_code
853 , p_origination_date => p_origination_date
854 , p_date_code => p_date_code
855 , p_status_id => p_status_id
856 , p_change_date => p_change_date
857 , p_age => p_age
858 , p_retest_date => p_retest_date
859 , p_maturity_date => p_maturity_date
860 , p_item_size => p_item_size
861 , p_color => p_color
862 , p_volume => p_volume
863 , p_volume_uom => p_volume_uom
864 , p_place_of_origin => p_place_of_origin
865 , p_best_by_date => p_best_by_date
866 , p_length => p_length
867 , p_length_uom => p_length_uom
868 , p_recycled_content => p_recycled_content
869 , p_thickness => p_thickness
870 , p_thickness_uom => p_thickness_uom
871 , p_width => p_width
872 , p_width_uom => p_width_uom
873 , p_territory_code => p_territory_code
874 , p_supplier_lot_number => p_supplier_lot_number
875 , p_vendor_name => p_vendor_name
876 );
877 if(l_ans = true) then
878 x_return_code := 'T';
879 else
880 x_return_code := 'F';
881 x_return_msg := fnd_msg_pub.get(l_msg_count,'F');
882 end if;
883
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885 EXCEPTION WHEN OTHERS THEN
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887 x_return_msg := 'Unexpected Error at POSVASNB.pls.ValidateLot:'||sqlerrm;
888 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
889 END ValidateLot;
890
891 procedure findLlsCode(p_line_location_id in number, x_llsCode out nocopy varchar2)
892 IS
893 l_api_name varchar2(50) := 'findLlsCode';
894 l_progress varchar2(20) := '000';
895 l_lot_code mtl_system_items.lot_control_code%type;
896 l_serial_code mtl_system_items.serial_number_control_code%type;
897 l_item_id number;
898 l_org_id po_line_locations_all.ship_to_organization_id%type;
899 BEGIN
900 l_progress := '001'||p_line_location_id;
901
902 select pla.item_id,
903 plla.ship_to_organization_id
904 into l_item_id,
905 l_org_id
906 from po_lines_all pla ,po_line_locations_all plla
907 where plla.line_location_id = p_line_location_id
908 and pla.po_line_id=plla.po_line_id
909 and pla.po_header_id=plla.po_header_id ;
910
911 if(l_item_id is null) then
912 x_llsCode := 'LPN';
913 else
914
915 select
916 msi.lot_control_code,
917 msi.serial_number_control_code
918 into
919 l_lot_code,
920 l_serial_code
921 from
922 mtl_system_items msi
923 where msi.inventory_item_id=l_item_id
924 and msi.organization_id=l_org_id;
925
926
927 if(l_lot_code = 2 and l_serial_code in (2,5)) then
928 x_llsCode := 'LAS';
929 elsif(l_lot_code = 2) then
930 x_llsCode := 'LOT';
931 elsif(l_serial_code in (2,5)) then
932 x_llsCode := 'SER';
933 else
934 x_llsCode := 'LPN';
935 end if;
936 end if;
937 exception when others then
938 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
939 x_llsCode := 'ERR:'||sqlerrm;
940
941 END findLlsCode;
942
943
944
945 PROCEDURE start_wip_workflow (
946 P_LINE_LOCATION_ID IN NUMBER,
947 P_QUANTITY_T IN NUMBER,
948 P_UNIT_OF_MEASURE_T IN VARCHAR2,
949 P_SHIPPED_DATE IN DATE,
950 P_EXPECTED_RECEIPT_DATE IN DATE,
951 P_PACKING_SLIP_T IN VARCHAR2,
952 P_WAYBILL_AIRBILL_NUM IN VARCHAR2,
953 P_BILL_OF_LADING IN VARCHAR2,
954 P_PACKAGING_CODE IN VARCHAR2,
955 P_NUM_OF_CONTAINERS_T IN NUMBER,
956 p_net_weight IN NUMBER ,
957 p_net_weight_uom IN VARCHAR2 ,
958 p_tar_weight IN NUMBER ,
959 p_tar_weight_uom IN VARCHAR2 ,
960 P_SPECIAL_HANDLING_CODE IN VARCHAR2 ,
961 P_FREIGHT_CARRIER_CODE IN VARCHAR2,
962 P_FREIGHT_TERMS IN VARCHAR2)
963
964 IS
965
966 cursor dis_details_cur is
967 select WIP_ENTITY_ID,
968 WIP_LINE_ID,
969 WIP_OPERATION_SEQ_NUM,
970 PO_DISTRIBUTION_ID
971 from po_distributions_all
972 where line_location_id = p_line_location_id;
973
974 dis_details_rec dis_details_cur%rowtype;
975
976
977 BEGIN
978
979 OPEN dis_details_cur;
980 LOOP
981 fetch dis_details_cur into dis_details_rec;
982 exit when dis_details_cur%notfound;
983
984 -- The wip workflow needs to be called only for wip jobs
985 IF dis_details_rec.wip_entity_id is not null THEN
986
987 wip_osp_shp_i_wf.StartWFProcToAnotherSupplier (
988 dis_details_rec.po_distribution_id,
989 P_QUANTITY_T,
990 P_UNIT_OF_MEASURE_T,
991 P_SHIPPED_DATE,
992 P_EXPECTED_RECEIPT_DATE,
993 P_PACKING_SLIP_T,
994 P_WAYBILL_AIRBILL_NUM,
995 p_bill_of_lading,
996 p_packaging_code,
997 p_num_of_containers_t,
998 null, /* p_gross_weight */
999 null, /* p_gross_weight_uom */
1000 p_net_weight,
1001 p_net_weight_uom,
1002 p_tar_weight,
1003 p_tar_weight_uom,
1004 null, /* p_hazard_class */
1005 null, /* p_hazard_code */
1006 null, /* p_hazard_desc */
1007 p_special_handling_code,
1008 p_freight_carrier_code,
1009 p_freight_terms,
1010 null, /* p_carrier_equipment */
1011 null, /* p_carrier_method */
1012 null, /* p_freight_bill_num */
1013 null, /* p_receipt_num */
1014 null /* p_ussgl_txn_code */
1015 );
1016
1017 END IF;
1018
1019 END LOOP;
1020
1021 CLOSE dis_details_cur;
1022
1023
1024 EXCEPTION
1025
1026 WHEN OTHERS THEN
1027 raise;
1028 END start_wip_workflow;
1029
1030
1031
1032 FUNCTION getAvailableShipmentQuantity (p_lineLocationID IN NUMBER)
1033 RETURN NUMBER IS
1034 v_availableQuantity NUMBER;
1035 v_tolerableQuantity NUMBER;
1036 v_unitOfMeasure VARCHAR2(25);
1037 x_progress VARCHAR2(3);
1038
1039 BEGIN
1040
1041 x_progress := '001';
1042
1043 getShipmentQuantity( p_lineLocationID,
1044 v_availableQuantity,
1045 v_tolerableQuantity,
1046 v_unitOfMeasure);
1047
1048 RETURN v_availableQuantity;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 po_message_s.sql_error('getAvailableShipmentQuantity', x_progress, sqlcode);
1053 RAISE;
1054
1055 END getAvailableShipmentQuantity;
1056
1057
1058 FUNCTION getTolerableShipmentQuantity(p_lineLocationID IN NUMBER)
1059 RETURN NUMBER IS
1060 v_availableQuantity NUMBER;
1061 v_tolerableQuantity NUMBER;
1062 v_unitOfMeasure VARCHAR2(25);
1063 x_progress VARCHAR2(3);
1064
1065 BEGIN
1066
1067 x_progress := '001';
1068
1069 getShipmentQuantity( p_lineLocationID,
1070 v_availableQuantity,
1071 v_tolerableQuantity,
1072 v_unitOfMeasure);
1073
1074 RETURN v_tolerableQuantity;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 po_message_s.sql_error('getTolerableShipmentQuantity', x_progress, sqlcode);
1079 RAISE;
1080
1081 END getTolerableShipmentQuantity;
1082
1083
1084 PROCEDURE getShipmentQuantity ( p_line_location_id IN NUMBER,
1085 p_available_quantity IN OUT nocopy NUMBER,
1086 p_tolerable_quantity IN OUT nocopy NUMBER,
1087 p_unit_of_measure IN OUT nocopy VARCHAR2) IS
1088
1089 x_progress VARCHAR2(3) := NULL;
1090 x_quantity_ordered NUMBER := 0;
1091 x_quantity_received NUMBER := 0;
1092 x_quantity_shipped NUMBER := 0;
1093 x_interface_quantity NUMBER := 0; /* in primary_uom */
1094 x_quantity_cancelled NUMBER := 0;
1095 x_qty_rcv_tolerance NUMBER := 0;
1096 x_qty_rcv_exception_code VARCHAR2(26);
1097 x_po_uom VARCHAR2(26);
1098 x_item_id NUMBER;
1099 x_primary_uom VARCHAR2(26);
1100 x_interface_qty_in_po_uom NUMBER := 0;
1101
1102 BEGIN
1103
1104 x_progress := '005';
1105
1106
1107 /*
1108 ** Get PO quantity information.
1109 */
1110
1111 SELECT nvl(pll.quantity, 0),
1112 nvl(pll.quantity_received, 0),
1113 nvl(pll.quantity_shipped, 0),
1114 nvl(pll.quantity_cancelled,0),
1115 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
1116 pll.qty_rcv_exception_code,
1117 pl.item_id,
1118 pl.unit_meas_lookup_code
1119 INTO x_quantity_ordered,
1120 x_quantity_received,
1121 x_quantity_shipped,
1122 x_quantity_cancelled,
1123 x_qty_rcv_tolerance,
1124 x_qty_rcv_exception_code,
1125 x_item_id,
1126 x_po_uom
1127 FROM po_line_locations_all pll,
1128 po_lines_all pl
1129 WHERE pll.line_location_id = p_line_location_id
1130 AND pll.po_line_id = pl.po_line_id;
1131
1132
1133 x_progress := '010';
1134
1135 /*
1136 ** Get any unprocessed receipt or match transaction against the
1137 ** PO shipment. x_interface_quantity is in primary uom.
1138 **
1139 ** The min(primary_uom) is neccessary because the
1140 ** select may return multiple rows and we only want one value
1141 ** to be returned. Having a sum and min group function in the
1142 ** select ensures that this sql statement will not raise a
1143 ** no_data_found exception even if no rows are returned.
1144 */
1145
1146 SELECT nvl(sum(primary_quantity),0),
1147 min(primary_unit_of_measure)
1148 INTO x_interface_quantity,
1149 x_primary_uom
1150 FROM rcv_transactions_interface
1151 WHERE processing_status_code = 'PENDING'
1152 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
1153 AND po_line_location_id = p_line_location_id;
1154
1155 IF (x_interface_quantity = 0) THEN
1156
1157 /*
1158 ** There is no unprocessed quantity. Simply set the
1159 ** x_interface_qty_in_po_uom to 0. There is no need for uom
1160 ** conversion.
1161 */
1162
1163 x_interface_qty_in_po_uom := 0;
1164
1165 ELSE
1166
1167 /*
1168 ** There is unprocessed quantity. Convert it to the PO uom
1169 ** so that the available quantity can be calculated in the PO uom
1170 */
1171
1172 x_progress := '015';
1173 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1174 x_po_uom, x_interface_qty_in_po_uom);
1175
1176 END IF;
1177
1178 /*
1179 ** Calculate the quantity available to be received.
1180 */
1181
1182 p_available_quantity := x_quantity_ordered - x_quantity_received - x_quantity_shipped -
1183 x_quantity_cancelled - x_interface_qty_in_po_uom;
1184
1185 /*
1186 ** p_available_quantity can be negative if this shipment has been over
1187 ** received. In this case, the available quantity that needs to be passed
1188 ** back should be 0.
1189 */
1190
1191 IF (p_available_quantity < 0) THEN
1192 p_available_quantity := 0;
1193 END IF;
1194
1195 /*
1196 ** Calculate the maximum quantity that can be received allowing for
1197 ** tolerance.
1198 */
1199
1200 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
1201 x_quantity_received - x_quantity_shipped - x_quantity_cancelled -
1202 x_interface_qty_in_po_uom;
1203
1204 /*
1205 ** p_tolerable_quantity can be negative if this shipment has been over
1206 ** received. In this case, the tolerable quantity that needs to be passed
1207 ** back should be 0.
1208 */
1209
1210 IF (p_tolerable_quantity < 0) THEN
1211 p_tolerable_quantity := 0;
1212 END IF;
1213
1214 /*
1215 ** Return the PO unit of measure
1216 */
1217 p_unit_of_measure := x_po_uom;
1218
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 po_message_s.sql_error('getShipmentQuantity', x_progress, sqlcode);
1222 RAISE;
1223
1224 END getShipmentQuantity;
1225
1226
1227
1228 /* procedure added to get converted quantity based on new UOM */
1229
1230 PROCEDURE getConvertedQuantity ( p_line_location_id IN NUMBER,
1231 p_available_quantity IN NUMBER,
1232 p_new_unit_of_measure IN VARCHAR2,
1233 p_converted_quantity OUT nocopy NUMBER ) IS
1234
1235 /* p_available_quantity is in new UOM */
1236
1237 x_converted_quantity NUMBER := 0;
1238 x_po_uom VARCHAR2(26);
1239 x_item_id NUMBER;
1240
1241 BEGIN
1242
1243 SELECT pl.item_id,
1244 pl.unit_meas_lookup_code
1245 INTO x_item_id,
1246 x_po_uom
1247 FROM po_line_locations_all pll,
1248 po_lines_all pl
1249 WHERE pll.line_location_id = p_line_location_id
1250 AND pll.po_line_id = pl.po_line_id;
1251
1252
1253 IF (x_po_uom = p_new_unit_of_measure) THEN
1254 p_converted_quantity := p_available_quantity;
1255
1256 ELSE
1257
1258 po_uom_s.uom_convert(p_available_quantity, p_new_unit_of_measure, x_item_id,
1259 x_po_uom, x_converted_quantity);
1260 p_converted_quantity := x_converted_quantity;
1261
1262 END IF;
1263
1264
1265 END getConvertedQuantity;
1266
1267 /* end of procedure added to get converted quantity based on new UOM */
1268
1269
1270
1271
1272 FUNCTION get_invoice_qty (
1273 p_line_location_id IN NUMBER,
1274 p_asn_unit_of_measure IN VARCHAR2,
1275 p_item_id IN NUMBER,
1276 p_quantity IN NUMBER)
1277 RETURN NUMBER IS
1278
1279 l_conversion_rate number := 0;
1280 l_asn_uom_code varchar2(30);
1281 l_po_uom_code varchar2(30);
1282
1283 BEGIN
1284
1285 IF (p_asn_unit_of_measure is not null) THEN
1286
1287 SELECT uom_code
1288 INTO l_asn_uom_code
1289 FROM mtl_units_of_measure
1290 WHERE unit_of_measure = p_asn_unit_of_measure;
1291
1292 SELECT uom_code
1293 INTO l_po_uom_code
1294 FROM mtl_units_of_measure
1295 WHERE unit_of_measure =
1296 ( select nvl(poll.UNIT_MEAS_LOOKUP_CODE, pol.UNIT_MEAS_LOOKUP_CODE)
1297 from po_line_locations_all poll,
1298 po_lines_all pol
1299 where poll.line_location_id = p_line_location_id
1300 and poll.po_line_id = pol.po_line_id );
1301
1302 INV_CONVERT.inv_um_conversion(l_asn_uom_code,
1303 l_po_uom_code,
1304 p_item_id,
1305 l_conversion_rate);
1306
1307 END IF;
1308
1309 return (l_conversion_rate * p_quantity);
1310
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 RAISE;
1314
1315 END get_invoice_qty;
1316
1317
1318
1319 FUNCTION check_wms_install (
1320 p_api_version IN NUMBER,
1321 x_return_status OUT NOCOPY VARCHAR2)
1322 RETURN VARCHAR2 IS
1323
1324
1325 BEGIN
1326
1327 IF (INV_CONTROL.Get_Current_Release_Level >= INV_RELEASE.Get_J_Release_Level) THEN
1328 return 'Y';
1329
1330 ELSE
1331 return 'N';
1332
1333 END IF;
1334
1335 x_return_status := 'S';
1336
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 x_return_status := 'E';
1340 RAISE;
1341
1342 END check_wms_install;
1343
1344
1345 FUNCTION check_lpnlotserial_valid (
1346 p_asn_line_id IN NUMBER,
1347 p_lls_code IN VARCHAR2,
1348 p_processing_stage IN VARCHAR2)
1349
1350 RETURN VARCHAR2 IS /*returns 'Y' or 'N' or 'E' on error*/
1351
1352 l_rows number :=0;
1353 l_temp number :=0;
1354
1355 BEGIN
1356
1357 IF ((p_lls_code = 'LAS') or (p_lls_code = 'LOT')) THEN
1358
1359 IF (p_processing_stage = 'I') THEN
1360 /*only for I*/
1361
1362 select count(*)
1363 into l_rows
1364 from rcv_transactions_interface rti,
1365 mtl_transaction_lots_interface mtli
1366 where rti.INTERFACE_TRANSACTION_ID = mtli.PRODUCT_TRANSACTION_ID
1367 and mtli.PRODUCT_CODE = 'RCV'
1368 and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
1369 and mtli.LOT_NUMBER is not null;
1370
1371
1372 ELSE
1373
1374
1375 select count(*)
1376 into l_rows
1377 from rcv_shipment_lines rsl, rcv_transactions rt,
1378 mtl_transaction_lot_numbers mtln
1379 where rsl.shipment_line_id = rt.shipment_line_id
1380 and rt.transaction_type = 'RECEIVE'
1381 and rt.transaction_id = mtln.PRODUCT_TRANSACTION_ID
1382 and mtln.PRODUCT_CODE = 'RCV'
1383 and rsl.shipment_line_id = p_asn_line_id
1384 and mtln.LOT_NUMBER is not null;
1385
1386 select count(*)
1387 into l_temp
1388 from rcv_shipment_lines rsl, rcv_lots_supply rcvls, mtl_lot_numbers mln
1389 where rsl.SHIPMENT_LINE_ID= rcvls.SHIPMENT_LINE_ID
1390 and rsl.to_organization_id = mln.ORGANIZATION_ID
1391 and rsl.ITEM_ID = mln.INVENTORY_ITEM_ID
1392 and rcvls.LOT_NUM = mln.LOT_NUMBER
1393 and rsl.shipment_line_id = p_asn_line_id
1394 and rcvls.LOT_NUM is not null;
1395
1396 l_rows := l_rows + l_temp;
1397
1398
1399
1400 END IF;
1401
1402 ELSIF p_lls_code = 'SER' THEN
1403
1404 IF (p_processing_stage = 'I') THEN
1405 /*only for I*/
1406 select count(*)
1407 into l_rows
1408 from mtl_serial_numbers_interface msni,
1409 rcv_transactions_interface rti
1410 where rti.INTERFACE_TRANSACTION_ID = msni.PRODUCT_TRANSACTION_ID
1411 and msni.PRODUCT_CODE = 'RCV'
1412 and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
1413 and msni.FM_SERIAL_NUMBER is not null;
1414
1415 ELSE
1416
1417
1418 /*only for S*/
1419 select count(*)
1420 into l_rows
1421 from rcv_shipment_lines rsl, rcv_transactions rt,
1422 mtl_unit_transactions mut
1423 where rsl.shipment_line_id = rt.shipment_line_id
1424 and rt.transaction_type = 'RECEIVE'
1425 and rt.transaction_id = mut.PRODUCT_TRANSACTION_ID
1426 and mut.PRODUCT_CODE = 'RCV'
1427 and rsl.shipment_line_id = p_asn_line_id
1428 and mut.SERIAL_NUMBER is not null;
1429
1430 select count(*)
1431 into l_temp
1432 from rcv_serials_supply rss, rcv_shipment_lines rsl,
1433 mtl_serial_numbers msn
1434 where rsl.SHIPMENT_LINE_ID = rss.SHIPMENT_LINE_ID
1435 and rsl.to_organization_id = msn.CURRENT_ORGANIZATION_ID
1436 and rsl.ITEM_ID = msn.INVENTORY_ITEM_ID
1437 and rss.SERIAL_NUM = msn.SERIAL_NUMBER
1438 and rsl.SHIPMENT_LINE_ID = p_asn_line_id
1439 and rss.SERIAL_NUM is not null;
1440
1441 l_rows := l_rows + l_temp;
1442
1443 END IF;
1444
1445 END IF;
1446
1447
1448 IF(l_rows=0) THEN
1449
1450
1451 IF (p_processing_stage = 'I') THEN
1452 /*only for I*/
1453
1454 select count(*)
1455 into l_rows
1456 from rcv_transactions_interface rti, po_headers_all poh, po_releases_all por,
1457 po_line_locations_all pll
1458 where rti.po_header_id = poh.po_header_id
1459 and rti.po_release_id = por.po_release_id(+)
1460 and rti.po_line_location_id = pll.line_location_id
1461 and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
1462 and rti.LICENSE_PLATE_NUMBER is not null;
1463
1464 ELSE
1465 /*only for S*/
1466
1467 select count(*)
1468 into l_rows
1469 from rcv_shipment_lines rsl, po_headers_all poh, po_releases_all por,
1470 wms_license_plate_numbers wlpn, po_line_locations_all pll
1471 where rsl.po_header_id = poh.po_header_id
1472 and rsl.po_release_id = por.po_release_id(+)
1473 and rsl.ASN_LPN_ID = wlpn.LPN_ID
1474 and rsl.po_line_location_id = pll.line_location_id
1475 and rsl.SHIPMENT_LINE_ID = p_asn_line_id
1476 and wlpn.LICENSE_PLATE_NUMBER is not null;
1477
1478 END IF;
1479
1480 END IF;
1481
1482
1483 IF l_rows>0 THEN
1484 RETURN 'Y';
1485 ELSE
1486 RETURN 'N';
1487 END IF;
1488
1489 EXCEPTION
1490
1491 WHEN OTHERS THEN
1492 RETURN 'E';
1493
1494 END check_lpnlotserial_valid;
1495
1496 /* Inbound Logistics */
1497 PROCEDURE validate_ship_from (
1498 p_api_version_number IN NUMBER,
1499 p_init_msg_list IN VARCHAR2,
1500 x_return_status OUT NOCOPY VARCHAR2,
1501 p_ship_from_locationId IN NUMBER,
1502 p_po_line_id_tbl IN po_tbl_number,
1503 p_po_line_loc_id_tbl IN po_tbl_number,
1504 x_out_invalid_tbl OUT NOCOPY po_tbl_varchar2000) IS
1505
1506
1507 l_msg_count NUMBER;
1508 l_msg_data VARCHAR2(2000);
1509
1510 l_wsh_in_rec WSH_PO_INTEGRATION_GRP.validateSF_in_rec_type;
1511 l_wsh_out_rec WSH_PO_INTEGRATION_GRP.validateSF_out_rec_type;
1512 BEGIN
1513
1514 -- Clear global message table.
1515 IF (p_init_msg_list = 'Y') THEN
1516 FND_MSG_PUB.initialize;
1517 END IF;
1518
1519
1520 -- Construct the record to pass in WSH api.
1521 for i in p_po_line_id_tbl.first .. p_po_line_id_tbl.last loop
1522 l_wsh_in_rec.po_line_id_tbl(i) := p_po_line_id_tbl(i);
1523 end loop;
1524
1525 for j in p_po_line_loc_id_tbl.first .. p_po_line_loc_id_tbl.last loop
1526 l_wsh_in_rec.po_shipment_line_id_tbl(j) := p_po_line_loc_id_tbl(j);
1527 end loop;
1528
1529 l_wsh_in_rec.ship_from_location_id := p_ship_from_locationid;
1530
1531
1532 -- Call WSH API
1533 -- TODO : uncomment the following call after applying the WSH package to the instance
1534 WSH_PO_INTEGRATION_GRP.validateASNReceiptShipFrom (
1535 p_api_version_number => p_api_version_number,
1536 p_init_msg_list => p_init_msg_list,
1537 p_in_rec => l_wsh_in_rec,
1538 p_commit => fnd_api.g_false,
1539 x_return_status => x_return_status,
1540 x_out_rec => l_wsh_out_rec ,
1541 x_msg_count => l_msg_count,
1542 x_msg_data => l_msg_data );
1543
1544 x_out_invalid_tbl := po_tbl_varchar2000();
1545 -- Construct the error message table as out parameter.
1546 IF (NOT l_wsh_out_rec.is_valid) THEN
1547
1548 -- If error message count is 1, l_msg_data contains the error message.
1549 IF (l_msg_count = 1) THEN
1550
1551 x_out_invalid_tbl.extend;
1552 x_out_invalid_tbl(x_out_invalid_tbl.last) := l_msg_data;
1553
1554 -- Otherwise we need to get the error messages from the global FND table.
1555 ELSE
1556
1557 FOR l_index IN 1.. l_msg_count LOOP
1558
1559 x_out_invalid_tbl.extend;
1560 x_out_invalid_tbl(x_out_invalid_tbl.last) :=
1561 fnd_msg_pub.get( p_encoded => FND_API.G_FALSE, p_msg_index => l_index );
1562 END LOOP;
1563
1564 END IF;
1565
1566 END IF;
1567
1568
1569 END validate_ship_from;
1570
1571 END POS_ASN_CREATE_PVT;