[Home] [Help]
PACKAGE BODY: APPS.POS_ASN_CREATE_PVT
Source
1 PACKAGE BODY POS_ASN_CREATE_PVT AS
2 /* $Header: POSVASNB.pls 120.7.12020000.3 2013/02/09 13:38:08 hvutukur 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 , p_lot_exp_dt IN DATE DEFAULT NULL -- Added for bug7137189
317 )
318 IS
319 l_api_name varchar2(50) := 'insert_mtli';
320 l_progress varchar2(20) := '000';
321 l_primary_quantity number;
322 l_org_id number;
323 l_inventory_item_id number;
324 l_expiration_date date := p_lot_exp_dt; -- Added for bug8404937
325 l_serial_txn_temp_id number;
326 l_uom po_lines_all.UNIT_MEAS_LOOKUP_CODE%type;
327 BEGIN
328 -- Bug 7476612 - Start
329 select
330 plla.ship_to_organization_id,
331 pla.item_id
332 --pla.UNIT_MEAS_LOOKUP_CODE
333 into
334 l_org_id,
335 l_inventory_item_id
336 --l_uom
337 from
338 po_lines_all pla,
339 po_line_locations_all plla
340 where plla.line_location_id = p_po_line_loc_id
341 and plla.po_line_id = pla.po_line_id;
342
343 l_uom := po_uom_s.get_primary_uom(l_inventory_item_id,l_org_id,p_transaction_uom);
344 -- Bug 7476612 - End
345
346 select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_serial_txn_temp_id from dual;
347
348 PO_UOM_S.uom_convert (
349 from_quantity => p_transaction_quantity,
350 from_uom => p_transaction_uom,
351 item_id => l_inventory_item_id ,
352 to_uom => l_uom,
353 to_quantity => l_primary_quantity );
354
355
356 l_progress := '001'||p_po_line_loc_id;
357 inv_rcv_integration_apis.insert_mtli(
358 p_api_version => p_api_version
359 , x_return_status => x_return_status
360 , x_msg_count => x_msg_count
361 , x_msg_data => x_msg_data
362 , p_transaction_interface_id => p_transaction_interface_id
363 , p_lot_number => p_lot_number
364 , p_transaction_quantity => p_transaction_quantity
365 , p_primary_quantity => l_primary_quantity
366 , p_organization_id => l_org_id
367 , p_inventory_item_id => l_inventory_item_id
368 , p_expiration_date => l_expiration_date -- bug8404937
369 , x_serial_transaction_temp_id => x_serial_transaction_temp_id
370 , p_product_transaction_id => p_product_transaction_id
371 , p_product_code => 'RCV'
372 , p_vendor_id => p_vendor_id
373 , p_grade_code => p_grade_code
374 , p_origination_date => p_origination_date
375 , p_date_code => p_date_code
376 , p_status_id => p_status_id
377 , p_change_date => p_change_date
378 , p_age => p_age
379 , p_retest_date => p_retest_date
380 , p_maturity_date => p_maturity_date
381 , p_item_size => p_item_size
382 , p_color => p_color
383 , p_volume => p_volume
384 , p_volume_uom => p_volume_uom
385 , p_place_of_origin => p_place_of_origin
386 , p_best_by_date => p_best_by_date
387 , p_length => p_length
388 , p_length_uom => p_length_uom
389 , p_recycled_content => p_recycled_content
390 , p_thickness => p_thickness
391 , p_thickness_uom => p_thickness_uom
392 , p_width => p_width
393 , p_width_uom => p_width_uom
394 , p_curl_wrinkle_fold => p_curl_wrinkle_fold
395 , p_supplier_lot_number => p_supplier_lot_number
396 , p_territory_code => p_territory_code
397 , p_vendor_name => p_vendor_name
398 , p_lot_attribute_category => p_lot_attribute_category
399 , p_c_attribute1 => p_c_attribute1
400 , p_c_attribute2 => p_c_attribute2
401 , p_c_attribute3 => p_c_attribute3
402 , p_c_attribute4 => p_c_attribute4
403 , p_c_attribute5 => p_c_attribute5
404 , p_c_attribute6 => p_c_attribute6
405 , p_c_attribute7 => p_c_attribute7
406 , p_c_attribute8 => p_c_attribute8
407 , p_c_attribute9 => p_c_attribute9
408 , p_c_attribute10 => p_c_attribute10
409 , p_c_attribute11 => p_c_attribute11
410 , p_c_attribute12 => p_c_attribute12
411 , p_c_attribute13 => p_c_attribute13
412 , p_c_attribute14 => p_c_attribute14
413 , p_c_attribute15 => p_c_attribute15
414 , p_c_attribute16 => p_c_attribute16
415 , p_c_attribute17 => p_c_attribute17
416 , p_c_attribute18 => p_c_attribute18
417 , p_c_attribute19 => p_c_attribute19
418 , p_c_attribute20 => p_c_attribute20
419 , p_d_attribute1 => p_d_attribute1
420 , p_d_attribute2 => p_d_attribute2
421 , p_d_attribute3 => p_d_attribute3
422 , p_d_attribute4 => p_d_attribute4
423 , p_d_attribute5 => p_d_attribute5
424 , p_d_attribute6 => p_d_attribute6
425 , p_d_attribute7 => p_d_attribute7
426 , p_d_attribute8 => p_d_attribute8
427 , p_d_attribute9 => p_d_attribute9
428 , p_d_attribute10 => p_d_attribute10
429 , p_n_attribute1 => p_n_attribute1
430 , p_n_attribute2 => p_n_attribute2
431 , p_n_attribute3 => p_n_attribute3
432 , p_n_attribute4 => p_n_attribute4
433 , p_n_attribute5 => p_n_attribute5
434 , p_n_attribute6 => p_n_attribute6
435 , p_n_attribute7 => p_n_attribute7
436 , p_n_attribute8 => p_n_attribute8
437 , p_n_attribute9 => p_n_attribute9
438 , p_n_attribute10 => p_n_attribute10
439 , p_attribute_category => p_attribute_category
440 , p_attribute1 => p_attribute1
441 , p_attribute2 => p_attribute2
442 , p_attribute3 => p_attribute3
443 , p_attribute4 => p_attribute4
444 , p_attribute5 => p_attribute5
445 , p_attribute6 => p_attribute6
446 , p_attribute7 => p_attribute7
447 , p_attribute8 => p_attribute8
448 , p_attribute9 => p_attribute9
449 , p_attribute10 => p_attribute10
450 , p_attribute11 => p_attribute11
451 , p_attribute12 => p_attribute12
452 , p_attribute13 => p_attribute13
453 , p_attribute14 => p_attribute14
454 , p_attribute15 => p_attribute15
455 , p_att_exist => 'N'
456 );
457
458
459 exception when others then
460 x_return_status := 'U';
461 x_msg_data := 'Unexpected Error:'||sqlerrm;
462 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
463 END insert_mtli;
464
465 procedure insert_wlpni
466 (p_api_version IN NUMBER
467 , x_return_status OUT NOCOPY VARCHAR2
468 , x_msg_count OUT NOCOPY NUMBER
469 , x_msg_data OUT NOCOPY VARCHAR2
470 , p_po_line_loc_ID IN NUMBER
471 , p_license_plate_number IN VARCHAR2
472 , p_LPN_GROUP_ID IN NUMBER
473 , p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
474 )
475 IS
476 l_api_name varchar2(50) := 'insert_wlpni';
477 l_progress varchar2(20) := '000';
478 l_org_id number;
479 l_exist number;
480 l_parent_lpn wms_lpn_interface.parent_license_plate_number%type;
481
482 cursor l_lpn_exist_csr(p_lpn varchar2, p_grp_id number)
483 is
484 select 1, parent_license_plate_number
485 from wms_lpn_interface
486 where license_plate_number = p_lpn
487 and source_group_id = p_grp_id;
488
489 BEGIN
490 select plla.ship_to_organization_id
491 into l_org_id
492 from po_line_locations_all plla
493 where line_location_id = p_po_line_loc_id;
494
495 if(p_parent_license_plate_number is null) then
496 --Parent LPN is null ==> Insert LPN record, if not exist yet
497 --Check if LPN already exist
498 open l_lpn_exist_csr(p_license_plate_number, p_lpn_group_id);
499 fetch l_lpn_exist_csr into l_exist, l_parent_lpn;
500 close l_lpn_exist_csr;
501
502 if(l_exist is null) then
503 --If LPN does not exist, insert
504 inv_rcv_integration_apis.insert_wlpni(
505 p_api_version => p_api_version,
506 x_return_status => x_return_status,
507 x_msg_count => x_msg_count,
508 x_msg_data => x_msg_data,
509 p_ORGANIZATION_ID => l_ORG_ID,
510 p_LPN_ID => null,
511 p_license_plate_number => p_license_plate_number,
512 p_LPN_GROUP_ID => p_LPN_GROUP_ID,
513 p_PARENT_LICENSE_PLATE_NUMBER => null);
514 end if;
515 else
516 --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
517 -- 2. Insert new record for Parent LPN if does not exist
518 --Check if LPN already exist
519 l_exist := null;
520 open l_lpn_exist_csr(p_license_plate_number, p_lpn_group_id);
521 fetch l_lpn_exist_csr into l_exist, l_parent_lpn;
522 close l_lpn_exist_csr;
523
524 if(l_exist is null) then
525 --If LPN does not exist, insert
526 inv_rcv_integration_apis.insert_wlpni(
527 p_api_version => p_api_version,
528 x_return_status => x_return_status,
529 x_msg_count => x_msg_count,
530 x_msg_data => x_msg_data,
531 p_ORGANIZATION_ID => l_ORG_ID,
532 p_LPN_ID => null,
533 p_license_plate_number => p_license_plate_number,
534 p_LPN_GROUP_ID => p_LPN_GROUP_ID,
535 p_PARENT_LICENSE_PLATE_NUMBER => p_parent_license_plate_number);
536 elsif(l_parent_lpn is not null AND l_parent_lpn <> p_parent_license_plate_number) then
537 --Existing LPN has different parent_lpn
538 x_return_status := 'E';
539 x_msg_data := 'This LPN Child Parent relationship error should have been caught in the UI. A-->X, A-->Y exist';
540 else
541 --Existing LPN has NO Parent LPN
542 update wms_lpn_interface
543 set parent_license_plate_number = p_parent_license_plate_number
544 where source_group_id = p_lpn_group_id
545 and license_plate_number = p_license_plate_number;
546 end if;
547
548 --To see if we need to insert new record for the Parent LPN
549 l_exist := null;
550 open l_lpn_exist_csr(p_parent_license_plate_number, p_lpn_group_id);
551 fetch l_lpn_exist_csr into l_exist, l_parent_lpn;
552 close l_lpn_exist_csr;
553 if(l_exist is null) then
554 --Parent LPN as LPN does NOT exist ==> Insert
555 inv_rcv_integration_apis.insert_wlpni(
556 p_api_version => p_api_version,
557 x_return_status => x_return_status,
558 x_msg_count => x_msg_count,
559 x_msg_data => x_msg_data,
560 p_ORGANIZATION_ID => l_ORG_ID,
561 p_LPN_ID => null,
562 p_license_plate_number => p_parent_license_plate_number,
563 p_LPN_GROUP_ID => p_LPN_GROUP_ID,
564 p_PARENT_LICENSE_PLATE_NUMBER => null);
565
566 end if;
567
568 end if;
569
570 EXCEPTION WHEN OTHERS THEN
571 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
572 x_return_status := 'U';
573 x_msg_data := 'Unexpected Error:'||sqlerrm;
574 END insert_wlpni;
575
576 procedure ValidateSerialRange( p_api_version in number,
577 x_return_status out nocopy varchar2,
578 p_fm_serial_number in varchar2,
579 p_to_serial_number in varchar2,
580 p_quantity in number,
581 p_lot_number in varchar2,
582 p_line_loc_id in number,
583 x_return_code out nocopy varchar2,
584 x_return_msg out nocopy varchar2)
585 IS
586 l_api_name varchar2(50) := 'ValidateSerialRange';
587 l_progress varchar2(20) := '000';
588 l_valid_sr boolean;
589 l_ret_status varchar2(1);
590 l_ret_count number;
591 l_ret_data varchar2(2000);
592 l_val_mode number;
593 l_org_id number;
594 l_item_id number;
595 l_revision po_lines_all.item_revision%type; /* Bug 9734095 */
596 l_to_serial_number mtl_serial_numbers_interface.to_serial_number%type;
597 BEGIN
598 l_to_serial_number := p_to_serial_number;
599
600 select
601 plla.ship_to_organization_id,
602 pla.item_id,
603 pla.item_revision,
604 decode(msi.serial_number_control_code,2,inv_rcv_integration_apis.G_EXISTS_ONLY,inv_rcv_integration_apis.G_EXISTS_OR_CREATE)
605 into
606 l_org_id,
607 l_item_id,
608 l_revision,
609 l_val_mode
610 from
611 po_lines_all pla,
612 mtl_system_items msi,
613 po_line_locations_all plla
614 where pla.item_id = msi.inventory_item_id
615 and plla.ship_to_organization_id = msi.organization_id
616 and plla.line_location_id = p_line_loc_id
617 and plla.po_line_id = pla.po_line_id;
618
619 l_valid_sr := inv_rcv_integration_apis.validate_serial_range (
620 p_api_version => 1.0
621 , x_return_status => l_ret_status
622 , x_msg_count => l_ret_count
623 , x_msg_data => l_ret_data
624 , p_validation_mode => l_val_mode
625 , p_org_id => l_org_id
626 , p_inventory_item_id => l_item_id
627 , p_quantity => p_quantity
628 , p_revision => l_revision
629 , p_lot_number => p_lot_number
630 , p_fm_serial_number => p_fm_serial_number
631 , p_to_serial_number => l_to_serial_number
632 , p_txn_type => inv_rcv_integration_apis.G_SHIP) ;
633
634 if(l_valid_sr = true) then
635 x_return_code := 'T';
636 else
637 x_return_code := 'F';
638 x_return_msg := fnd_msg_pub.get(l_ret_count,'F');
639 end if;
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641
642 exception when others then
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 x_return_msg := 'Unexpected Error at POSVASNB.pls.ValidateSerialRange:'||sqlerrm;
645 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
646 END ValidateSerialRange;
647
648 procedure ValidateLpn( p_api_version in number,
649 x_return_status out nocopy varchar2,
650 p_lpn in varchar2,
651 p_line_loc_id in number,
652 x_return_code out nocopy varchar2,
653 x_return_msg out nocopy varchar2)
654 IS
655 l_api_name varchar2(50) := 'ValidateLpn';
656 l_progress varchar2(20) := '000';
657 l_return_status varchar2(1);
658 l_msg_count number;
659 l_msg_data varchar2(2000);
660 l_ans boolean;
661 l_org_id number;
662 l_lpn_id number;
663 l_lpn_context wms_license_plate_numbers.lpn_context%type;
664 l_error_msg varchar2(2000);
665 BEGIN
666
667 select
668 plla.ship_to_organization_id
669 into
670 l_org_id
671 from
672 po_line_locations_all plla
673 where plla.line_location_id = p_line_loc_id;
674
675 l_ans := inv_rcv_integration_apis.validate_lpn
676 (p_api_version => 1.0
677 , x_return_status => l_return_status
678 , x_msg_count => l_msg_count
679 , x_msg_data => l_msg_data
680 , p_validation_mode => inv_rcv_integration_apis.G_EXISTS_OR_CREATE
681 , p_org_id => l_org_id
682 , p_lpn_id => l_lpn_id
683 , p_lpn => p_lpn);
684
685 --the inv api does not check for context value
686 if(l_ans = true) then
687 select lpn_context into l_lpn_context
688 from wms_license_plate_numbers
689 where lpn_id = l_lpn_id;
690
691 --can only reuse the LPNs that have a context of 5
692 if(l_lpn_context is not null AND l_lpn_context <> 5) then
693 fnd_message.set_name('WMS', 'WMS_CONT_DUPLICATE_LPN');
694 fnd_msg_pub.ADD;
695 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
696 fnd_msg_pub.ADD;
697 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
698 p_data => l_msg_data);
699 l_ans := false;
700 end if;
701 end if;
702
703 if(l_ans = true ) then
704 x_return_code := 'T';
705 else
706 x_return_code := 'F';
707 for i in 1..l_msg_count
708 loop
709 l_error_msg := l_error_msg || ' ' || fnd_msg_pub.get(l_msg_count-i+1,'F');
710 end loop;
711 x_return_msg := l_error_msg;
712 end if;
713
714 x_return_status := FND_API.G_RET_STS_SUCCESS;
715 exception when others then
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 x_return_msg := 'Unexpected Error at POSVASNB.pls.ValidateLpn:'||sqlerrm;
718 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
719 END ValidateLpn;
720
721 procedure ValidateLot(
722 p_api_version in number,
723 x_return_status out nocopy varchar2,
724 p_lot_number in varchar2,
725 p_line_loc_id in number,
726 p_validation_mode in number,
727
728 p_lot_attribute_category in varchar2 ,
729 p_c_attributes_tbl in PO_TBL_VARCHAR2000,
730 p_n_attributes_tbl in PO_TBL_NUMBER,
731 p_d_attributes_tbl in PO_TBL_DATE,
732 p_grade_code in varchar2,
733 p_origination_date in date,
734 p_date_code in varchar2,
735 p_status_id in number,
736 p_change_date in date,
737 p_age in number,
738 p_retest_date in date,
739 p_maturity_date in date,
740 p_item_size in number,
741 p_color in varchar2,
742 p_volume in number,
743 p_volume_uom in varchar2,
744 p_place_of_origin in varchar2,
745 p_best_by_date in date,
746 p_length in number,
747 p_length_uom in varchar2,
748 p_recycled_content in number,
749 p_thickness in number,
750 p_thickness_uom in varchar2,
751 p_width in number,
752 p_width_uom in varchar2,
753 p_territory_code in varchar2,
754 p_supplier_lot_number in varchar2,
755 p_vendor_name in varchar2,
756 p_lot_exp_dt in date default null, --Added for bug 7137189
757 x_return_code out nocopy varchar2,
758 x_return_msg out nocopy varchar2,
759 x_is_new_lot out nocopy varchar2)
760 IS
761 l_c_attributes_tbl inv_lot_api_pub.char_tbl;
762 l_n_attributes_tbl inv_lot_api_pub.number_tbl;
763 l_d_attributes_tbl inv_lot_api_pub.date_tbl;
764 l_api_name varchar2(50) := 'ValidateLot';
765 l_progress varchar2(20) := '000';
766 l_return_status varchar2(1);
767 l_msg_count number;
768 l_msg_data varchar2(2000);
769 l_ans boolean;
770 l_org_id number;
771 l_item_id number;
772 l_exp_date date;
773 BEGIN
774
775 select
776 plla.ship_to_organization_id,
777 pla.item_id,
778 pla.expiration_date
779 into
780 l_org_id,
781 l_item_id,
782 l_exp_date
783 from
784 po_lines_all pla,
785 po_line_locations_all plla
786 where plla.line_location_id = p_line_loc_id
787 and plla.po_line_id = pla.po_line_id;
788
789 l_c_attributes_tbl(1) := p_c_attributes_tbl(1);
790 l_c_attributes_tbl(2) := p_c_attributes_tbl(2);
791 l_c_attributes_tbl(3) := p_c_attributes_tbl(3);
792 l_c_attributes_tbl(4) := p_c_attributes_tbl(4);
793 l_c_attributes_tbl(5) := p_c_attributes_tbl(5);
794 l_c_attributes_tbl(6) := p_c_attributes_tbl(6);
795 l_c_attributes_tbl(7) := p_c_attributes_tbl(7);
796 l_c_attributes_tbl(8) := p_c_attributes_tbl(8);
797 l_c_attributes_tbl(9) := p_c_attributes_tbl(9);
798 l_c_attributes_tbl(10) := p_c_attributes_tbl(10);
799 l_c_attributes_tbl(11) := p_c_attributes_tbl(11);
800 l_c_attributes_tbl(12) := p_c_attributes_tbl(12);
801 l_c_attributes_tbl(13) := p_c_attributes_tbl(13);
802 l_c_attributes_tbl(14) := p_c_attributes_tbl(14);
803 l_c_attributes_tbl(15) := p_c_attributes_tbl(15);
804 l_c_attributes_tbl(16) := p_c_attributes_tbl(16);
805 l_c_attributes_tbl(17) := p_c_attributes_tbl(17);
806 l_c_attributes_tbl(18) := p_c_attributes_tbl(18);
807 l_c_attributes_tbl(19) := p_c_attributes_tbl(19);
808 l_c_attributes_tbl(20) := p_c_attributes_tbl(20);
809
810 l_n_attributes_tbl(1) := p_n_attributes_tbl(1);
811 l_n_attributes_tbl(2) := p_n_attributes_tbl(2);
812 l_n_attributes_tbl(3) := p_n_attributes_tbl(3);
813 l_n_attributes_tbl(4) := p_n_attributes_tbl(4);
814 l_n_attributes_tbl(5) := p_n_attributes_tbl(5);
815 l_n_attributes_tbl(6) := p_n_attributes_tbl(6);
816 l_n_attributes_tbl(7) := p_n_attributes_tbl(7);
817 l_n_attributes_tbl(8) := p_n_attributes_tbl(8);
818 l_n_attributes_tbl(9) := p_n_attributes_tbl(9);
819 l_n_attributes_tbl(10) := p_n_attributes_tbl(10);
820
821
822 l_d_attributes_tbl(1) := p_d_attributes_tbl(1);
823 l_d_attributes_tbl(2) := p_d_attributes_tbl(2);
824 l_d_attributes_tbl(3) := p_d_attributes_tbl(3);
825 l_d_attributes_tbl(4) := p_d_attributes_tbl(4);
826 l_d_attributes_tbl(5) := p_d_attributes_tbl(5);
827 l_d_attributes_tbl(6) := p_d_attributes_tbl(6);
828 l_d_attributes_tbl(7) := p_d_attributes_tbl(7);
829 l_d_attributes_tbl(8) := p_d_attributes_tbl(8);
830 l_d_attributes_tbl(9) := p_d_attributes_tbl(9);
831 l_d_attributes_tbl(10) := p_d_attributes_tbl(10);
832
833
834
835 l_ans := inv_rcv_integration_apis.validate_lot_number(
836 p_api_version => 1
837 , p_init_msg_lst => fnd_api.g_false
838 , x_return_status => l_return_status
839 , x_msg_count => l_msg_count
840 , x_msg_data => l_msg_data
841 , x_is_new_lot => x_is_new_lot
842 , p_validation_mode => p_validation_mode
843 , p_org_id => l_org_id
844 , p_inventory_item_id => l_item_id
845 , p_lot_number => p_lot_number
846 , p_expiration_date => p_lot_exp_dt --Added for bug 8404937
847 , p_txn_type => inv_rcv_integration_apis.G_SHIP
848
849 , p_lot_attribute_category => p_lot_attribute_category
850 , p_c_attributes_tbl => l_c_attributes_tbl
851 , p_n_attributes_tbl => l_n_attributes_tbl
852 , p_d_attributes_tbl => l_d_attributes_tbl
853 , p_grade_code => p_grade_code
854 , p_origination_date => p_origination_date
855 , p_date_code => p_date_code
856 , p_status_id => p_status_id
857 , p_change_date => p_change_date
858 , p_age => p_age
859 , p_retest_date => p_retest_date
860 , p_maturity_date => p_maturity_date
861 , p_item_size => p_item_size
862 , p_color => p_color
863 , p_volume => p_volume
864 , p_volume_uom => p_volume_uom
865 , p_place_of_origin => p_place_of_origin
866 , p_best_by_date => p_best_by_date
867 , p_length => p_length
868 , p_length_uom => p_length_uom
869 , p_recycled_content => p_recycled_content
870 , p_thickness => p_thickness
871 , p_thickness_uom => p_thickness_uom
872 , p_width => p_width
873 , p_width_uom => p_width_uom
874 , p_territory_code => p_territory_code
875 , p_supplier_lot_number => p_supplier_lot_number
876 , p_vendor_name => p_vendor_name
877 );
878 if(l_ans = true) then
879 x_return_code := 'T';
880 else
881 x_return_code := 'F';
882 x_return_msg := fnd_msg_pub.get(l_msg_count,'F');
883 end if;
884
885 x_return_status := FND_API.G_RET_STS_SUCCESS;
886 EXCEPTION WHEN OTHERS THEN
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888 x_return_msg := 'Unexpected Error at POSVASNB.pls.ValidateLot:'||sqlerrm;
889 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
890 END ValidateLot;
891
892 procedure findLlsCode(p_line_location_id in number, x_llsCode out nocopy varchar2)
893 IS
894 l_api_name varchar2(50) := 'findLlsCode';
895 l_progress varchar2(20) := '000';
896 l_lot_code mtl_system_items.lot_control_code%type;
897 l_serial_code mtl_system_items.serial_number_control_code%type;
898 l_item_id number;
899 l_org_id po_line_locations_all.ship_to_organization_id%type;
900 BEGIN
901 l_progress := '001'||p_line_location_id;
902
903 select pla.item_id,
904 plla.ship_to_organization_id
905 into l_item_id,
906 l_org_id
907 from po_lines_all pla ,po_line_locations_all plla
908 where plla.line_location_id = p_line_location_id
909 and pla.po_line_id=plla.po_line_id
910 and pla.po_header_id=plla.po_header_id ;
911
912 if(l_item_id is null) then
913 x_llsCode := 'LPN';
914 else
915
916 select
917 msi.lot_control_code,
918 msi.serial_number_control_code
919 into
920 l_lot_code,
921 l_serial_code
922 from
923 mtl_system_items msi
924 where msi.inventory_item_id=l_item_id
925 and msi.organization_id=l_org_id;
926
927
928 if(l_lot_code = 2 and l_serial_code in (2,5)) then
929 x_llsCode := 'LAS';
930 elsif(l_lot_code = 2) then
931 x_llsCode := 'LOT';
932 elsif(l_serial_code in (2,5)) then
933 x_llsCode := 'SER';
934 else
935 x_llsCode := 'LPN';
936 end if;
937 end if;
938 exception when others then
939 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
940 x_llsCode := 'ERR:'||sqlerrm;
941
942 END findLlsCode;
943
944
945
946 PROCEDURE start_wip_workflow (
947 P_LINE_LOCATION_ID IN NUMBER,
948 P_QUANTITY_T IN NUMBER,
949 P_UNIT_OF_MEASURE_T IN VARCHAR2,
950 P_SHIPPED_DATE IN DATE,
951 P_EXPECTED_RECEIPT_DATE IN DATE,
952 P_PACKING_SLIP_T IN VARCHAR2,
953 P_WAYBILL_AIRBILL_NUM IN VARCHAR2,
954 P_BILL_OF_LADING IN VARCHAR2,
955 P_PACKAGING_CODE IN VARCHAR2,
956 P_NUM_OF_CONTAINERS_T IN NUMBER,
957 p_net_weight IN NUMBER ,
958 p_net_weight_uom IN VARCHAR2 ,
959 p_tar_weight IN NUMBER ,
960 p_tar_weight_uom IN VARCHAR2 ,
961 P_SPECIAL_HANDLING_CODE IN VARCHAR2 ,
962 P_FREIGHT_CARRIER_CODE IN VARCHAR2,
963 P_FREIGHT_TERMS IN VARCHAR2)
964
965 IS
966
967 cursor dis_details_cur is
968 select WIP_ENTITY_ID,
969 WIP_LINE_ID,
970 WIP_OPERATION_SEQ_NUM,
971 PO_DISTRIBUTION_ID
972 from po_distributions_all
973 where line_location_id = p_line_location_id;
974
975 dis_details_rec dis_details_cur%rowtype;
976
977
978 BEGIN
979
980 OPEN dis_details_cur;
981 LOOP
982 fetch dis_details_cur into dis_details_rec;
983 exit when dis_details_cur%notfound;
984
985 -- The wip workflow needs to be called only for wip jobs
986 IF dis_details_rec.wip_entity_id is not null THEN
987
988 wip_osp_shp_i_wf.StartWFProcToAnotherSupplier (
989 dis_details_rec.po_distribution_id,
990 P_QUANTITY_T,
991 P_UNIT_OF_MEASURE_T,
992 P_SHIPPED_DATE,
993 P_EXPECTED_RECEIPT_DATE,
994 P_PACKING_SLIP_T,
995 P_WAYBILL_AIRBILL_NUM,
996 p_bill_of_lading,
997 p_packaging_code,
998 p_num_of_containers_t,
999 null, /* p_gross_weight */
1000 null, /* p_gross_weight_uom */
1001 p_net_weight,
1002 p_net_weight_uom,
1003 p_tar_weight,
1004 p_tar_weight_uom,
1005 null, /* p_hazard_class */
1006 null, /* p_hazard_code */
1007 null, /* p_hazard_desc */
1008 p_special_handling_code,
1009 p_freight_carrier_code,
1010 p_freight_terms,
1011 null, /* p_carrier_equipment */
1012 null, /* p_carrier_method */
1013 null, /* p_freight_bill_num */
1014 null, /* p_receipt_num */
1015 null /* p_ussgl_txn_code */
1016 );
1017
1018 END IF;
1019
1020 END LOOP;
1021
1022 CLOSE dis_details_cur;
1023
1024
1025 EXCEPTION
1026
1027 WHEN OTHERS THEN
1028 raise;
1029 END start_wip_workflow;
1030
1031
1032
1033 FUNCTION getAvailableShipmentQuantity (p_lineLocationID IN NUMBER)
1034 RETURN NUMBER IS
1035 v_availableQuantity NUMBER;
1036 v_tolerableQuantity NUMBER;
1037 v_unitOfMeasure VARCHAR2(25);
1038 x_progress VARCHAR2(3);
1039
1040 BEGIN
1041
1042 x_progress := '001';
1043
1044 getShipmentQuantity( p_lineLocationID,
1045 v_availableQuantity,
1046 v_tolerableQuantity,
1047 v_unitOfMeasure);
1048
1049 RETURN v_availableQuantity;
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 po_message_s.sql_error('getAvailableShipmentQuantity', x_progress, sqlcode);
1054 RAISE;
1055
1056 END getAvailableShipmentQuantity;
1057
1058
1059 FUNCTION getTolerableShipmentQuantity(p_lineLocationID IN NUMBER)
1060 RETURN NUMBER IS
1061 v_availableQuantity NUMBER;
1062 v_tolerableQuantity NUMBER;
1063 v_unitOfMeasure VARCHAR2(25);
1064 x_progress VARCHAR2(3);
1065
1066 BEGIN
1067
1068 x_progress := '001';
1069
1070 getShipmentQuantity( p_lineLocationID,
1071 v_availableQuantity,
1072 v_tolerableQuantity,
1073 v_unitOfMeasure);
1074
1075 RETURN v_tolerableQuantity;
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 po_message_s.sql_error('getTolerableShipmentQuantity', x_progress, sqlcode);
1080 RAISE;
1081
1082 END getTolerableShipmentQuantity;
1083
1084
1085 PROCEDURE getShipmentQuantity ( p_line_location_id IN NUMBER,
1086 p_available_quantity IN OUT nocopy NUMBER,
1087 p_tolerable_quantity IN OUT nocopy NUMBER,
1088 p_unit_of_measure IN OUT nocopy VARCHAR2) IS
1089
1090 x_progress VARCHAR2(3) := NULL;
1091 x_quantity_ordered NUMBER := 0;
1092 x_quantity_received NUMBER := 0;
1093 x_quantity_shipped NUMBER := 0;
1094 x_interface_quantity NUMBER := 0; /* in primary_uom */
1095 x_quantity_cancelled NUMBER := 0;
1096 x_qty_rcv_tolerance NUMBER := 0;
1097 x_qty_rcv_exception_code VARCHAR2(26);
1098 x_po_uom VARCHAR2(26);
1099 x_item_id NUMBER;
1100 x_primary_uom VARCHAR2(26);
1101 x_interface_qty_in_po_uom NUMBER := 0;
1102
1103 BEGIN
1104
1105 x_progress := '005';
1106
1107
1108 /*
1109 ** Get PO quantity information.
1110 */
1111
1112 SELECT nvl(pll.quantity, 0),
1113 nvl(pll.quantity_received, 0),
1114 nvl(pll.quantity_shipped, 0),
1115 nvl(pll.quantity_cancelled,0),
1116 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
1117 pll.qty_rcv_exception_code,
1118 pl.item_id,
1119 pl.unit_meas_lookup_code
1120 INTO x_quantity_ordered,
1121 x_quantity_received,
1122 x_quantity_shipped,
1123 x_quantity_cancelled,
1124 x_qty_rcv_tolerance,
1125 x_qty_rcv_exception_code,
1126 x_item_id,
1127 x_po_uom
1128 FROM po_line_locations_all pll,
1129 po_lines_all pl
1130 WHERE pll.line_location_id = p_line_location_id
1131 AND pll.po_line_id = pl.po_line_id;
1132
1133
1134 x_progress := '010';
1135
1136 /*
1137 ** Get any unprocessed receipt or match transaction against the
1138 ** PO shipment. x_interface_quantity is in primary uom.
1139 **
1140 ** The min(primary_uom) is neccessary because the
1141 ** select may return multiple rows and we only want one value
1142 ** to be returned. Having a sum and min group function in the
1143 ** select ensures that this sql statement will not raise a
1144 ** no_data_found exception even if no rows are returned.
1145 */
1146
1147 SELECT nvl(sum(primary_quantity),0),
1148 min(primary_unit_of_measure)
1149 INTO x_interface_quantity,
1150 x_primary_uom
1151 FROM rcv_transactions_interface
1152 WHERE processing_status_code = 'PENDING'
1153 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
1154 AND po_line_location_id = p_line_location_id;
1155
1156 IF (x_interface_quantity = 0) THEN
1157
1158 /*
1159 ** There is no unprocessed quantity. Simply set the
1160 ** x_interface_qty_in_po_uom to 0. There is no need for uom
1161 ** conversion.
1162 */
1163
1164 x_interface_qty_in_po_uom := 0;
1165
1166 ELSE
1167
1168 /*
1169 ** There is unprocessed quantity. Convert it to the PO uom
1170 ** so that the available quantity can be calculated in the PO uom
1171 */
1172
1173 x_progress := '015';
1174 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1175 x_po_uom, x_interface_qty_in_po_uom);
1176
1177 END IF;
1178
1179 /*
1180 ** Calculate the quantity available to be received.
1181 */
1182
1183 p_available_quantity := x_quantity_ordered - x_quantity_received - x_quantity_shipped -
1184 x_quantity_cancelled - x_interface_qty_in_po_uom;
1185
1186 /*
1187 ** p_available_quantity can be negative if this shipment has been over
1188 ** received. In this case, the available quantity that needs to be passed
1189 ** back should be 0.
1190 */
1191
1192 IF (p_available_quantity < 0) THEN
1193 p_available_quantity := 0;
1194 END IF;
1195
1196 /*
1197 ** Calculate the maximum quantity that can be received allowing for
1198 ** tolerance.
1199 */
1200
1201 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
1202 x_quantity_received - x_quantity_shipped - x_quantity_cancelled -
1203 x_interface_qty_in_po_uom;
1204
1205 /*
1206 ** p_tolerable_quantity can be negative if this shipment has been over
1207 ** received. In this case, the tolerable quantity that needs to be passed
1208 ** back should be 0.
1209 */
1210
1211 IF (p_tolerable_quantity < 0) THEN
1212 p_tolerable_quantity := 0;
1213 END IF;
1214
1215 /*
1216 ** Return the PO unit of measure
1217 */
1218 p_unit_of_measure := x_po_uom;
1219
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 po_message_s.sql_error('getShipmentQuantity', x_progress, sqlcode);
1223 RAISE;
1224
1225 END getShipmentQuantity;
1226
1227
1228
1229 /* procedure added to get converted quantity based on new UOM */
1230
1231 PROCEDURE getConvertedQuantity ( p_line_location_id IN NUMBER,
1232 p_available_quantity IN NUMBER,
1233 p_new_unit_of_measure IN VARCHAR2,
1234 p_converted_quantity OUT nocopy NUMBER ) IS
1235
1236 /* p_available_quantity is in new UOM */
1237
1238 x_converted_quantity NUMBER := 0;
1239 x_po_uom VARCHAR2(26);
1240 x_item_id NUMBER;
1241
1242 BEGIN
1243
1244 SELECT pl.item_id,
1245 pl.unit_meas_lookup_code
1246 INTO x_item_id,
1247 x_po_uom
1248 FROM po_line_locations_all pll,
1249 po_lines_all pl
1250 WHERE pll.line_location_id = p_line_location_id
1251 AND pll.po_line_id = pl.po_line_id;
1252
1253
1254 IF (x_po_uom = p_new_unit_of_measure) THEN
1255 p_converted_quantity := p_available_quantity;
1256
1257 ELSE
1258
1259 po_uom_s.uom_convert(p_available_quantity, p_new_unit_of_measure, x_item_id,
1260 x_po_uom, x_converted_quantity);
1261 p_converted_quantity := x_converted_quantity;
1262
1263 END IF;
1264
1265
1266 END getConvertedQuantity;
1267
1268 /* end of procedure added to get converted quantity based on new UOM */
1269
1270
1271
1272
1273 FUNCTION get_invoice_qty (
1274 p_line_location_id IN NUMBER,
1275 p_asn_unit_of_measure IN VARCHAR2,
1276 p_item_id IN NUMBER,
1277 p_quantity IN NUMBER)
1278 RETURN NUMBER IS
1279
1280 l_conversion_rate number := 0;
1281 l_asn_uom_code varchar2(30);
1282 l_po_uom_code varchar2(30);
1283
1284 BEGIN
1285
1286 IF (p_asn_unit_of_measure is not null) THEN
1287
1288 SELECT uom_code
1289 INTO l_asn_uom_code
1290 FROM mtl_units_of_measure
1291 WHERE unit_of_measure = p_asn_unit_of_measure;
1292
1293 SELECT uom_code
1294 INTO l_po_uom_code
1295 FROM mtl_units_of_measure
1296 WHERE unit_of_measure =
1297 ( select nvl(poll.UNIT_MEAS_LOOKUP_CODE, pol.UNIT_MEAS_LOOKUP_CODE)
1298 from po_line_locations_all poll,
1299 po_lines_all pol
1300 where poll.line_location_id = p_line_location_id
1301 and poll.po_line_id = pol.po_line_id );
1302
1303 INV_CONVERT.inv_um_conversion(l_asn_uom_code,
1304 l_po_uom_code,
1305 p_item_id,
1306 l_conversion_rate);
1307
1308 END IF;
1309
1310 return (l_conversion_rate * p_quantity);
1311
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 RAISE;
1315
1316 END get_invoice_qty;
1317
1318
1319
1320 FUNCTION check_wms_install (
1321 p_api_version IN NUMBER,
1322 x_return_status OUT NOCOPY VARCHAR2)
1323 RETURN VARCHAR2 IS
1324
1325
1326 BEGIN
1327
1328 IF (INV_CONTROL.Get_Current_Release_Level >= INV_RELEASE.Get_J_Release_Level) THEN
1329 return 'Y';
1330
1331 ELSE
1332 return 'N';
1333
1334 END IF;
1335
1336 x_return_status := 'S';
1337
1338 EXCEPTION
1339 WHEN OTHERS THEN
1340 x_return_status := 'E';
1341 RAISE;
1342
1343 END check_wms_install;
1344
1345
1346 FUNCTION check_lpnlotserial_valid (
1347 p_asn_line_id IN NUMBER,
1348 p_lls_code IN VARCHAR2,
1349 p_processing_stage IN VARCHAR2)
1350
1351 RETURN VARCHAR2 IS /*returns 'Y' or 'N' or 'E' on error*/
1352
1353 l_rows number :=0;
1354 l_temp number :=0;
1355
1356 BEGIN
1357
1358 IF ((p_lls_code = 'LAS') or (p_lls_code = 'LOT')) THEN
1359
1360 IF (p_processing_stage = 'I') THEN
1361 /*only for I*/
1362
1363 select count(*)
1364 into l_rows
1365 from rcv_transactions_interface rti,
1366 mtl_transaction_lots_interface mtli
1367 where rti.INTERFACE_TRANSACTION_ID = mtli.PRODUCT_TRANSACTION_ID
1368 and mtli.PRODUCT_CODE = 'RCV'
1369 and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
1370 and mtli.LOT_NUMBER is not null;
1371
1372
1373 ELSE
1374
1375
1376 select count(*)
1377 into l_rows
1378 from rcv_shipment_lines rsl, rcv_transactions rt,
1379 mtl_transaction_lot_numbers mtln
1380 where rsl.shipment_line_id = rt.shipment_line_id
1381 and rt.transaction_type = 'RECEIVE'
1382 and rt.transaction_id = mtln.PRODUCT_TRANSACTION_ID
1383 and mtln.PRODUCT_CODE = 'RCV'
1384 and rsl.shipment_line_id = p_asn_line_id
1385 and mtln.LOT_NUMBER is not null;
1386
1387 select count(*)
1388 into l_temp
1389 from rcv_shipment_lines rsl, rcv_lots_supply rcvls, mtl_lot_numbers mln
1390 where rsl.SHIPMENT_LINE_ID= rcvls.SHIPMENT_LINE_ID
1391 and rsl.to_organization_id = mln.ORGANIZATION_ID
1392 and rsl.ITEM_ID = mln.INVENTORY_ITEM_ID
1393 and rcvls.LOT_NUM = mln.LOT_NUMBER
1394 and rsl.shipment_line_id = p_asn_line_id
1395 and rcvls.LOT_NUM is not null;
1396
1397 l_rows := l_rows + l_temp;
1398
1399
1400
1401 END IF;
1402
1403 ELSIF p_lls_code = 'SER' THEN
1404
1405 IF (p_processing_stage = 'I') THEN
1406 /*only for I*/
1407 select count(*)
1408 into l_rows
1409 from mtl_serial_numbers_interface msni,
1410 rcv_transactions_interface rti
1411 where rti.INTERFACE_TRANSACTION_ID = msni.PRODUCT_TRANSACTION_ID
1412 and msni.PRODUCT_CODE = 'RCV'
1413 and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
1414 and msni.FM_SERIAL_NUMBER is not null;
1415
1416 ELSE
1417
1418
1419 /*only for S*/
1420 select count(*)
1421 into l_rows
1422 from rcv_shipment_lines rsl, rcv_transactions rt,
1423 mtl_unit_transactions mut
1424 where rsl.shipment_line_id = rt.shipment_line_id
1425 and rt.transaction_type = 'RECEIVE'
1426 and rt.transaction_id = mut.PRODUCT_TRANSACTION_ID
1427 and mut.PRODUCT_CODE = 'RCV'
1428 and rsl.shipment_line_id = p_asn_line_id
1429 and mut.SERIAL_NUMBER is not null;
1430
1431 select count(*)
1432 into l_temp
1433 from rcv_serials_supply rss, rcv_shipment_lines rsl,
1434 mtl_serial_numbers msn
1435 where rsl.SHIPMENT_LINE_ID = rss.SHIPMENT_LINE_ID
1436 and rsl.to_organization_id = msn.CURRENT_ORGANIZATION_ID
1437 and rsl.ITEM_ID = msn.INVENTORY_ITEM_ID
1438 and rss.SERIAL_NUM = msn.SERIAL_NUMBER
1439 and rsl.SHIPMENT_LINE_ID = p_asn_line_id
1440 and rss.SERIAL_NUM is not null;
1441
1442 l_rows := l_rows + l_temp;
1443
1444 END IF;
1445
1446 END IF;
1447
1448
1449 IF(l_rows=0) THEN
1450
1451
1452 IF (p_processing_stage = 'I') THEN
1453 /*only for I*/
1454
1455 select count(*)
1456 into l_rows
1457 from rcv_transactions_interface rti, po_headers_all poh, po_releases_all por,
1458 po_line_locations_all pll
1459 where rti.po_header_id = poh.po_header_id
1460 and rti.po_release_id = por.po_release_id(+)
1461 and rti.po_line_location_id = pll.line_location_id
1462 and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
1463 and rti.LICENSE_PLATE_NUMBER is not null;
1464
1465 ELSE
1466 /*only for S*/
1467
1468 select count(*)
1469 into l_rows
1470 from rcv_shipment_lines rsl, po_headers_all poh, po_releases_all por,
1471 wms_license_plate_numbers wlpn, po_line_locations_all pll
1472 where rsl.po_header_id = poh.po_header_id
1473 and rsl.po_release_id = por.po_release_id(+)
1474 and rsl.ASN_LPN_ID = wlpn.LPN_ID
1475 and rsl.po_line_location_id = pll.line_location_id
1476 and rsl.SHIPMENT_LINE_ID = p_asn_line_id
1477 and wlpn.LICENSE_PLATE_NUMBER is not null;
1478
1479 END IF;
1480
1481 END IF;
1482
1483
1484 IF l_rows>0 THEN
1485 RETURN 'Y';
1486 ELSE
1487 RETURN 'N';
1488 END IF;
1489
1490 EXCEPTION
1491
1492 WHEN OTHERS THEN
1493 RETURN 'E';
1494
1495 END check_lpnlotserial_valid;
1496
1497 /* Inbound Logistics */
1498 PROCEDURE validate_ship_from (
1499 p_api_version_number IN NUMBER,
1500 p_init_msg_list IN VARCHAR2,
1501 x_return_status OUT NOCOPY VARCHAR2,
1502 p_ship_from_locationId IN NUMBER,
1503 p_po_line_id_tbl IN po_tbl_number,
1504 p_po_line_loc_id_tbl IN po_tbl_number,
1505 x_out_invalid_tbl OUT NOCOPY po_tbl_varchar2000) IS
1506
1507
1508 l_msg_count NUMBER;
1509 l_msg_data VARCHAR2(2000);
1510
1511 l_wsh_in_rec WSH_PO_INTEGRATION_GRP.validateSF_in_rec_type;
1512 l_wsh_out_rec WSH_PO_INTEGRATION_GRP.validateSF_out_rec_type;
1513 BEGIN
1514
1515 -- Clear global message table.
1516 IF (p_init_msg_list = 'Y') THEN
1517 FND_MSG_PUB.initialize;
1518 END IF;
1519
1520
1521 -- Construct the record to pass in WSH api.
1522 for i in p_po_line_id_tbl.first .. p_po_line_id_tbl.last loop
1523 l_wsh_in_rec.po_line_id_tbl(i) := p_po_line_id_tbl(i);
1524 end loop;
1525
1526 for j in p_po_line_loc_id_tbl.first .. p_po_line_loc_id_tbl.last loop
1527 l_wsh_in_rec.po_shipment_line_id_tbl(j) := p_po_line_loc_id_tbl(j);
1528 end loop;
1529
1530 l_wsh_in_rec.ship_from_location_id := p_ship_from_locationid;
1531
1532
1533 -- Call WSH API
1534 -- TODO : uncomment the following call after applying the WSH package to the instance
1535 WSH_PO_INTEGRATION_GRP.validateASNReceiptShipFrom (
1536 p_api_version_number => p_api_version_number,
1537 p_init_msg_list => p_init_msg_list,
1538 p_in_rec => l_wsh_in_rec,
1539 p_commit => fnd_api.g_false,
1540 x_return_status => x_return_status,
1541 x_out_rec => l_wsh_out_rec ,
1542 x_msg_count => l_msg_count,
1543 x_msg_data => l_msg_data );
1544
1545 x_out_invalid_tbl := po_tbl_varchar2000();
1546 -- Construct the error message table as out parameter.
1547 IF (NOT l_wsh_out_rec.is_valid) THEN
1548
1549 -- If error message count is 1, l_msg_data contains the error message.
1550 IF (l_msg_count = 1) THEN
1551
1552 x_out_invalid_tbl.extend;
1553 x_out_invalid_tbl(x_out_invalid_tbl.last) := l_msg_data;
1554
1555 -- Otherwise we need to get the error messages from the global FND table.
1556 ELSE
1557
1558 FOR l_index IN 1.. l_msg_count LOOP
1559
1560 x_out_invalid_tbl.extend;
1561 x_out_invalid_tbl(x_out_invalid_tbl.last) :=
1562 fnd_msg_pub.get( p_encoded => FND_API.G_FALSE, p_msg_index => l_index );
1563 END LOOP;
1564
1565 END IF;
1566
1567 END IF;
1568
1569
1570 END validate_ship_from;
1571
1572 /**************************************Added for bug:13680427*******************************************/
1573
1574 PROCEDURE get_po_quantity(p_line_location_id IN NUMBER,
1575 p_available_quantity IN OUT NOCOPY NUMBER,
1576 p_interface_qty_in_po_uom IN OUT NOCOPY NUMBER,
1577 p_return_msg out nocopy varchar2,
1578 p_return_status OUT NOCOPY VARCHAR2
1579
1580 )
1581 as
1582
1583
1584 p_tolerable_quantity NUMBER;
1585 p_unit_of_measure VARCHAR2(200);
1586 x_quantity_ordered NUMBER := 0;
1587 x_quantity_received NUMBER := 0;
1588 x_interface_quantity NUMBER := 0; /* in primary_uom */
1589 x_quantity_cancelled NUMBER := 0;
1590
1591 x_qty_rcv_tolerance NUMBER := 0;
1592 x_qty_rcv_exception_code VARCHAR2(26);
1593 x_po_uom VARCHAR2(26);
1594 x_item_id NUMBER;
1595 x_primary_uom VARCHAR2(26);
1596 l_api_name varchar2(50):='get_po_quantity';
1597 l_progress varchar2(20) := '000';
1598
1599
1600 BEGIN
1601
1602 /*
1603 ** Get PO quantity information.
1604 */
1605
1606 SELECT nvl(pll.quantity, 0),
1607 nvl(pll.quantity_received, 0),
1608 nvl(pll.quantity_cancelled,0),
1609 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
1610 pll.qty_rcv_exception_code,
1611 pl.item_id,
1612 pl.unit_meas_lookup_code
1613 INTO x_quantity_ordered,
1614 x_quantity_received,
1615 x_quantity_cancelled,
1616 x_qty_rcv_tolerance,
1617 x_qty_rcv_exception_code,
1618 x_item_id,
1619 x_po_uom
1620 FROM po_line_locations_all pll, --<Shared Proc FPJ>
1621 po_lines_all pl --<Shared Proc FPJ>
1622 WHERE pll.line_location_id = p_line_location_id
1623 AND pll.po_line_id = pl.po_line_id;
1624
1625
1626 l_progress :='001'|| p_line_location_id;
1627
1628 /*
1629 ** Get any unprocessed receipt or match transaction against the
1630 ** PO shipment. x_interface_quantity is in primary uom.
1631 **
1632 ** The min(primary_uom) is neccessary because the
1633 ** select may return multiple rows and we only want one value
1634 ** to be returned. Having a sum and min group function in the
1635 ** select ensures that this sql statement will not raise a
1636 ** no_data_found exception even if no rows are returned.
1637 Primary Unit of Measure cannot have value
1638 for One time Items. So Added a decode statement to fetch
1639 unit_of_measure in case of One Time Items and Primary
1640 Unit of Measure for Inventory Items.
1641 */
1642
1643
1644 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
1645 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
1646 INTO x_interface_quantity,
1647 x_primary_uom
1648 FROM rcv_transactions_interface rti
1649 WHERE (transaction_status_code = 'PENDING'
1650 and processing_status_code <> 'ERROR')
1651 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP','CANCEL') -- bug 657347 should include 'SHIP'
1652 -- when calculating total quantity
1653 -- in the interface table
1654 AND NOT EXISTS(SELECT 1 FROM rcv_transactions rt -- bug 9583207 should not include
1655 WHERE rt.transaction_type='DELIVER' -- Correction to Deliver transaction
1656 AND rt.transaction_id = rti.parent_transaction_id
1657 AND rti.transaction_type = 'CORRECT')
1658 AND po_line_location_id = p_line_location_id;
1659
1660
1661 l_progress :='002'|| p_line_location_id||x_interface_quantity;
1662
1663
1664
1665 IF (x_interface_quantity = 0) THEN
1666
1667 p_interface_qty_in_po_uom := 0;
1668
1669 ELSE
1670
1671 /*
1672 ** There is unprocessed quantity. Convert it to the PO uom
1673 ** so that the available quantity can be calculated in the PO uom
1674 */
1675
1676 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1677 x_po_uom, p_interface_qty_in_po_uom);
1678
1679 END IF;
1680
1681
1682 /*
1683 ** Calculate the quantity available to be received.
1684 */
1685
1686 p_available_quantity := x_quantity_ordered - x_quantity_received -
1687 x_quantity_cancelled - p_interface_qty_in_po_uom;
1688
1689
1690 /*
1691 ** p_available_quantity can be negative if this shipment has been over
1692 ** received. In this case, the available quantity that needs to be passed
1693 ** back should be 0.
1694 */
1695
1696 IF (p_available_quantity < 0) THEN
1697
1698 p_available_quantity := 0;
1699
1700 END IF;
1701
1702 /*
1703 ** Calculate the maximum quantity that can be received allowing for
1704 ** tolerance.
1705 */
1706 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
1707 x_quantity_received - x_quantity_cancelled -
1708 p_interface_qty_in_po_uom;
1709 /*
1710 ** p_tolerable_quantity can be negative if this shipment has been over
1711 ** received. In this case, the tolerable quantity that needs to be passed
1712 ** back should be 0.
1713 */
1714
1715 IF (p_tolerable_quantity < 0) THEN
1716
1717 p_tolerable_quantity := 0;
1718
1719 END IF;
1720
1721 p_return_status := 'S';
1722
1723 EXCEPTION WHEN OTHERS THEN
1724 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
1725 p_return_status := 'E';
1726 p_return_msg := 'Unexpected Error:'||sqlerrm;
1727
1728
1729 END get_po_quantity;
1730
1731
1732
1733 FUNCTION get_po_pending_asn_quantity(p_line_location_id IN NUMBER) return NUMBER AS
1734 p_interface_quantity NUMBER:=0;
1735 p_available_quantity NUMBER:=0;
1736 p_interface_qty_in_po_uom NUMBER:=0;
1737 x_return_msg varchar2(100);
1738 x_return_status varchar2(100);
1739
1740
1741 begin
1742 get_po_quantity(p_line_location_id ,p_available_quantity , p_interface_qty_in_po_uom,x_return_msg,x_return_status);
1743 if (x_return_status='S') then
1744 return p_interface_qty_in_po_uom;
1745 else
1746 return null;
1747 END IF;
1748
1749 exception when others
1750 then return null;
1751
1752 end get_po_pending_asn_quantity;
1753
1754
1755 FUNCTION get_total_shippedquantity(p_line_location_id IN NUMBER) RETURN NUMBER AS
1756
1757
1758 x_interface_qty_in_po_uom NUMBER;
1759 p_quantity_shipped NUMBER:=0;
1760 p_unit_of_measure VARCHAR2(200);
1761
1762 x_quantity_shipped NUMBER :=0;
1763 x_interface_quantity NUMBER := 0;
1764
1765 x_qty_rcv_exception_code VARCHAR2(26);
1766 x_po_uom VARCHAR2(26);
1767 x_item_id NUMBER;
1768 x_primary_uom VARCHAR2(26);
1769 l_api_name varchar2(50):='get_po_quantity';
1770 l_progress varchar2(20) := '000';
1771
1772
1773 BEGIN
1774
1775 /*
1776 ** Get PO quantity information.
1777 */
1778
1779 SELECT nvl(pll.quantity_shipped, 0),
1780 pll.qty_rcv_exception_code,
1781 pl.item_id,
1782 pl.unit_meas_lookup_code
1783 INTO x_quantity_shipped,
1784 x_qty_rcv_exception_code,
1785 x_item_id,
1786 x_po_uom
1787 FROM po_line_locations_all pll,
1788 po_lines_all pl
1789 WHERE pll.line_location_id = p_line_location_id
1790 AND pll.po_line_id = pl.po_line_id;
1791
1792
1793 l_progress :='001'|| p_line_location_id;
1794
1795 /*
1796 ** Get any unprocessed receipt or match transaction against the
1797 ** PO shipment. x_interface_quantity is in primary uom.
1798 **
1799 ** The min(primary_uom) is neccessary because the
1800 ** select may return multiple rows and we only want one value
1801 ** to be returned. Having a sum and min group function in the
1802 ** select ensures that this sql statement will not raise a
1803 ** no_data_found exception even if no rows are returned.
1804 Primary Unit of Measure cannot have value
1805 for One time Items. So Added a decode statement to fetch
1806 unit_of_measure in case of One Time Items and Primary
1807 Unit of Measure for Inventory Items.
1808 */
1809
1810
1811 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
1812 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
1813 INTO x_interface_quantity,
1814 x_primary_uom
1815 FROM rcv_transactions_interface rti
1816 WHERE (transaction_status_code = 'PENDING'
1817 and processing_status_code <> 'ERROR')
1818 AND transaction_type IN ('SHIP')
1819 AND NOT EXISTS(SELECT 1 FROM rcv_transactions rt
1820 WHERE rt.transaction_type='DELIVER'
1821 AND rt.transaction_id = rti.parent_transaction_id
1822 AND rti.transaction_type = 'CORRECT')
1823 AND po_line_location_id = p_line_location_id;
1824
1825
1826 l_progress :='001'|| p_line_location_id||x_interface_quantity;
1827
1828
1829
1830 IF (x_interface_quantity = 0) THEN
1831
1832 x_interface_qty_in_po_uom := 0;
1833
1834 ELSE
1835
1836 /*
1837 ** There is unprocessed quantity. Convert it to the PO uom
1838 ** so that the available quantity can be calculated in the PO uom
1839 */
1840
1841 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1842 x_po_uom, x_interface_qty_in_po_uom);
1843 l_progress := '002'|| p_line_location_id|| x_interface_qty_in_po_uom;
1844
1845 END IF;
1846
1847 p_quantity_shipped:=Nvl((x_interface_qty_in_po_uom+Nvl(x_quantity_shipped,0)),0);
1848
1849 RETURN p_quantity_shipped ;
1850
1851
1852
1853 EXCEPTION WHEN OTHERS THEN
1854 LOG(FND_LOG.LEVEL_UNEXPECTED,l_api_name,l_progress||':'||sqlerrm);
1855 RETURN NULL;
1856
1857 END get_total_shippedquantity;
1858
1859
1860 END POS_ASN_CREATE_PVT;