[Home] [Help]
PACKAGE BODY: APPS.POS_EXCELASN_PVT
Source
1 PACKAGE BODY Pos_ExcelAsn_PVT AS
2 /* $Header: POSVEXAB.pls 120.14.12020000.3 2013/02/09 14:12:02 hvutukur ship $ */
3
4
5 procedure InsertIntoRHI;
6 procedure InsertIntoRTI;
7
8 procedure CheckSecuringAtt(
9 x_return_status out nocopy varchar2,
10 x_user_vendor_id_tbl out nocopy vendor_id_tbl_type,
11 x_secure_by_site out nocopy varchar2,
12 x_secure_by_contact out nocopy varchar2,
13 x_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
14 x_error_pointer in out nocopy number
15 );
16 ------------------------------------------------------------------
17
18 /*
19 * N: Not available
20 * P: Pending
21 * E: Completed with errors
22 * EP: Completed with errors (Error information is purged)
23 * S: Success
24 * U: Unknown
25 */
26 function get_status(p_group_id in number) return varchar2
27 IS
28 cursor l_rhiError_csr(x_group_id in number)
29 is
30 select 1
31 from rcv_headers_interface
32 where group_id = x_group_id
33 and processing_status_code = 'ERROR';
34
35 cursor l_rtiError_csr(x_group_id in number)
36 is
37 select 1
38 from rcv_transactions_interface
39 where group_id = x_group_id
40 and (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR');
41
42 cursor l_rhiPending_csr(x_group_id in number)
43 is
44 select 1
45 from rcv_headers_interface
46 where group_id = x_group_id
47 and processing_status_code in ('PENDING','RUNNING');
48
49 cursor l_rtiPending_csr(x_group_id in number)
50 is
51 select 1
52 from rcv_transactions_interface
53 where group_id = x_group_id
54 and (processing_status_code in ('PENDING','RUNNING') or transaction_status_code = 'ERROR');
55
56 cursor l_rhiExist_csr(x_group_id in number)
57 is
58 select 1
59 from rcv_headers_interface
60 where group_id = x_group_id;
61
62 cursor l_pieExist_csr(x_group_id in number)
63 is
64 select 1
65 from po_interface_errors pie1,
66 rcv_headers_interface rhi
67 where rhi.group_id = x_group_id
68 and rhi.header_interface_id = pie1.interface_header_id
69 union all
70 select 1
71 from po_interface_errors pie2,
72 rcv_transactions_interface rti
73 where rti.group_id = x_group_id
74 and rti.interface_transaction_id = pie2.interface_line_id;
75
76
77 l_rhi_exist number := null;
78 l_rti_exist number := null;
79 l_pie_exist number;
80 l_error_exp exception;
81 Begin
82 begin
83 --Check if Error at Header Level Occur
84 open l_rhiError_csr(p_group_id);
85 fetch l_rhiError_csr into l_rhi_exist;
86 close l_rhiError_csr;
87
88 if(l_rhi_exist = 1) then
89 raise l_error_exp;
90 end if;
91
92
93 --Check if Pending,Running at Header Level Exist
94 l_rhi_exist := null;
95 open l_rhiPending_csr(p_group_id);
96 fetch l_rhiPending_csr into l_rhi_exist;
97 close l_rhiPending_csr;
98
99 if(l_rhi_exist = 1) then
100 return 'P';
101 end if;
102
103 --Check if Header Intf Record still exist
104 l_rhi_exist := null;
105 open l_rhiExist_csr(p_group_id);
106 fetch l_rhiExist_csr into l_rhi_exist;
107 CLOSE l_rhiExist_csr;
108
109 if(l_rhi_exist is null) then
110 return 'N';
111 end if;
112
113 --At this stage, all records in Header Interface table is in 'SUCCESS' status => Check Line Records
114 --Check if Line Error Exist
115 open l_rtiError_csr(p_group_id);
116 fetch l_rtiError_csr into l_rti_exist;
117 close l_rtiError_csr;
118
119 if(l_rti_exist = 1) then
120 raise l_error_exp;
121 end if;
122
123 --Check if Line Pending/Running Exist
124 l_rti_exist := null;
125 open l_rtiPending_csr(p_group_id);
126 fetch l_rtiPending_csr into l_rti_exist;
127 close l_rtiPending_csr;
128
129 if(l_rti_exist = 1) then
130 return 'P';
131 end if;
132
133
134
135 exception when l_error_exp then
136 --At this stage, errors at Header or Line level occur. Need to check if PIE exist
137 open l_pieExist_csr(p_group_id);
138 fetch l_pieExist_csr into l_pie_exist;
139 close l_pieExist_csr;
140
141 if(l_pie_exist = 1) then
142 return 'E';
143 else
144 return 'EP';
145 end if;
146
147 end;
148
149 return 'S';
150
151 exception when others then
152 return 'U';
153 end get_status;
154
155
156 procedure ValidateLls(x_return_status out nocopy varchar2,
157 x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
158 x_error_pointer in out NOCOPY number)
159 IS
160 cursor l_ParentQtyNull_csr
161 is
162 select plpn.line_number
163 from pos_exasn_lpns plpn
164 where parent_lpn is null
165 and quantity is null;
166
167
168 cursor l_LineVsLotQty_csr
169 is
170 select plnt.line_number
171 from pos_exasn_lines plnt, pos_exasn_lots plot
172 where plnt.line_id = plot.line_id
173 group by plnt.line_number, plnt.quantity
174 having plnt.quantity <> sum(plot.quantity)
175 and sum(plot.quantity) > 0;
176
177 cursor l_LineVsSerialQty_csr
178 is
179 select plnt.line_number
180 from pos_exasn_lines plnt, pos_exasn_serials pst
181 where plnt.line_id = pst.line_id
182 group by plnt.line_number, plnt.quantity
183 having plnt.quantity <> sum(pst.quantity)
184 and sum(pst.quantity) > 0;
185
186 cursor l_lineVsLpnQty_csr
187 is
188 select plnt.line_number
189 from pos_exasn_lines plnt, pos_exasn_lpns plpn
190 where plnt.line_id = plpn.line_id
191 group by plnt.line_number, plnt.quantity
192 having plnt.quantity <> sum(plpn.quantity)
193 and sum(plpn.quantity) > 0;
194
195 cursor l_lotVsSerialQty_csr
196 is
197 select plot.line_number
198 from pos_exasn_lots plot, pos_exasn_serials pst
199 where plot.lot_id = pst.lot_id
200 group by plot.line_number, plot.quantity
201 having plot.quantity <> sum(pst.quantity);
202
203 cursor l_checkLot_csr
204 is
205 select line_number, lot_number, license_plate_number, po_line_loc_id,
206 lot_attribute_category, cattribute1, cattribute2, cattribute3,
207 cattribute4, cattribute5, cattribute6, cattribute7, cattribute8,
208 cattribute9, cattribute10, cattribute11, cattribute12, cattribute13,
209 cattribute14, cattribute15, cattribute16, cattribute17, cattribute18,
210 cattribute19, cattribute20,
211 dattribute1, dattribute2, dattribute3, dattribute4, dattribute5,
212 dattribute6, dattribute7, dattribute8, dattribute9, dattribute10,
213 nattribute1, nattribute2, nattribute3, nattribute4, nattribute5,
214 nattribute6, nattribute7, nattribute8, nattribute9, nattribute10,
215 grade_code, origination_date, date_code, status_id, change_date,
216 age, retest_date, maturity_date, item_size, color, volume,
217 volume_uom, place_of_origin, best_by_date, length, length_uom,
218 recycled_content, thickness, thickness_uom, width, width_uom,
219 territory_code, supplier_lot_number, vendor_name
220 from pos_exasn_lots;
221
222 cursor l_checkSerial_csr
223 is
224 select line_number, from_serial, to_serial, quantity, license_plate_number, po_line_loc_id, lot_number
225 from pos_exasn_serials;
226
227 cursor l_checkLpn_csr
228 is
229 select line_number, license_plate_number, parent_lpn, po_line_loc_id
230 from pos_exasn_lpns;
231
232
233 l_error_ln number;
234 l_is_new_lot varchar2(60);
235 l_lot_ln number;
236 l_lot_number pos_exasn_lots.lot_number%type;
237 l_lpn pos_exasn_lpns.license_plate_number%type;
238 l_po_line_loc_id number;
239
240 l_lot_attribute_category varchar2(60);
241 l_cattribute1 varchar2(2000);
242 l_cattribute2 varchar2(2000);
243 l_cattribute3 varchar2(2000);
244 l_cattribute4 varchar2(2000);
245 l_cattribute5 varchar2(2000);
246 l_cattribute6 varchar2(2000);
247 l_cattribute7 varchar2(2000);
248 l_cattribute8 varchar2(2000);
249 l_cattribute9 varchar2(2000);
250 l_cattribute10 varchar2(2000);
251 l_cattribute11 varchar2(2000);
252 l_cattribute12 varchar2(2000);
253 l_cattribute13 varchar2(2000);
254 l_cattribute14 varchar2(2000);
255 l_cattribute15 varchar2(2000);
256 l_cattribute16 varchar2(2000);
257 l_cattribute17 varchar2(2000);
258 l_cattribute18 varchar2(2000);
259 l_cattribute19 varchar2(2000);
260 l_cattribute20 varchar2(2000);
261 l_dattribute1 date;
262 l_dattribute2 date;
263 l_dattribute3 date;
264 l_dattribute4 date;
265 l_dattribute5 date;
266 l_dattribute6 date;
267 l_dattribute7 date;
268 l_dattribute8 date;
269 l_dattribute9 date;
270 l_dattribute10 date;
271 l_nattribute1 number;
272 l_nattribute2 number;
273 l_nattribute3 number;
274 l_nattribute4 number;
275 l_nattribute5 number;
276 l_nattribute6 number;
277 l_nattribute7 number;
278 l_nattribute8 number;
279 l_nattribute9 number;
280 l_nattribute10 number;
281
282 l_c_attributes_tbl po_tbl_varchar2000 := po_tbl_varchar2000();
283 l_n_attributes_tbl po_tbl_number := po_tbl_number();
284 l_d_attributes_tbl po_tbl_date := po_tbl_date();
285
286 l_grade_code varchar2(2000);
287 l_origination_date date;
288 l_date_code varchar2(2000);
289 l_status_id number;
290 l_change_date date;
291 l_age number;
292 l_retest_date date;
293 l_maturity_date date;
294 l_item_size number;
295 l_color varchar2(2000);
296 l_volume number;
297 l_volume_uom varchar2(60);
298 l_place_of_origin varchar2(2000);
299 l_best_by_date date;
300 l_length number;
301 l_length_uom varchar2(60);
302 l_recycled_content number;
303 l_thickness number;
304 l_thickness_uom varchar2(60);
305 l_width number;
306 l_width_uom varchar2(60);
307 l_territory_code varchar2(60);
308 l_supplier_lot_number varchar2(2000);
309 l_vendor_name varchar2(2000);
310
311 l_lot_status varchar2(1);
312 l_lot_return_code varchar2(1);
313 l_lot_return_msg varchar2(2000);
314 l_ser_ln number;
315 l_ser_status varchar2(1);
316 l_ser_return_code varchar2(1);
317 l_ser_return_msg varchar2(2000);
318 l_lpn_status varchar2(1);
319 l_lpn_return_code varchar2(1);
320 l_lpn_return_msg varchar2(2000);
321 l_fm_serial pos_exasn_serials.from_serial%type;
322 l_to_serial pos_exasn_serials.to_serial%type;
323 l_ser_qty number;
324 l_lpn_ln number;
325 l_parent_lpn varchar2(60);
326 l_25errors exception;
327 BEGIN
328
329 open l_ParentQtyNull_csr;
330 loop
331 fetch l_ParentQtyNull_csr into l_error_ln;
332 exit when l_ParentQtyNull_csr%NOTFOUND;
333 --Line LINE_NUM is invalid because both parent license plate number and quantity are empty
334 fnd_message.set_name('POS','POS_EXASN_PLPNQTYNULL');
335 fnd_message.set_token('LINE_NUM',l_error_ln);
336 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
337 raise l_25errors;
338 end if;
339 end loop;
340 close l_ParentQtyNull_csr;
341
342 open l_LineVsLotQty_csr;
343 loop
344 fetch l_LineVsLotQty_csr into l_error_ln;
345 exit when l_LineVsLotQty_csr%NOTFOUND;
346 --Total lot quantity is not equal to the shipment quantity at line LINE_NUM
347 fnd_message.set_name('POS','POS_EXASN_LOT_NE_LN');
348 fnd_message.set_token('LINE_NUM',l_error_ln);
349 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
350 raise l_25errors;
351 end if;
352 end loop;
353 close l_LineVsLotQty_csr;
354
355
356 open l_lineVsSerialQty_csr;
357 loop
358 fetch l_lineVsSerialQty_csr into l_error_ln;
359 exit when l_lineVsSerialQty_csr%NOTFOUND;
360 --Total serial quantity is not equal to the shipment quantity at line LINE_NUM
361 fnd_message.set_name('POS','POS_EXASN_SER_NE_LN');
362 fnd_message.set_token('LINE_NUM',l_error_ln);
363 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
364 raise l_25errors;
365 end if;
366 end loop;
367 close l_lineVsSerialQty_csr;
368
369 open l_lineVsLpnQty_csr;
370 loop
371 fetch l_lineVsLpnQty_csr into l_error_ln;
372 exit when l_lineVsLpnQty_csr%NOTFOUND;
373 --Total license plate number quantity is not equal to the shipment quantity at line LINE_NUM
374 fnd_message.set_name('POS','POS_EXASN_LPN_NE_LN');
375 fnd_message.set_token('LINE_NUM',l_error_ln);
376 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
377 raise l_25errors;
378 end if;
379
380 end loop;
381 close l_lineVsLpnQty_csr;
382
383 open l_LotVsSerialQty_csr;
384 loop
385 fetch l_LotVsSerialQty_csr into l_error_ln;
386 exit when l_LotVsSerialQty_csr%NOTFOUND;
387 --Total serial quantity is not equal to the lot quanttiy at line LINE_NUM
388 fnd_message.set_name('POS','POS_EXASN_SER_NE_LOT');
389 fnd_message.set_token('LINE_NUM',l_error_ln);
390 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
391 raise l_25errors;
392 end if;
393 end loop;
394 close l_LotVsSerialQty_csr;
395
396
397 --Update lot info of Serial Records which are children of Lot Records
398 update pos_exasn_serials pst
399 set pst.lot_number = (select plot.lot_number
400 from pos_exasn_lots plot
401 where plot.header_id = pst.header_id
402 and plot.line_id = pst.line_id
403 and plot.lot_id = pst.lot_id);
404
405 update pos_exasn_lpns plpn
406 set (plpn.po_line_loc_id) = (select plnt.po_line_location_id
407 from pos_exasn_lines plnt
408 where plnt.header_id = plpn.header_id
409 and plnt.line_id = plpn.line_id);
410
411 update pos_exasn_lots plot
412 set (plot.po_line_loc_id) = (select plnt.po_line_location_id
413 from pos_exasn_lines plnt
414 where plnt.header_id = plot.header_id
415 and plnt.line_id = plot.line_id);
416 update pos_exasn_serials pst
417 set (pst.po_line_loc_id) = (select plnt.po_line_location_id
418 from pos_exasn_lines plnt
419 where plnt.header_id = pst.header_id
420 and plnt.line_id = pst.line_id);
421
422 open l_checkLot_csr;
423 loop
424 fetch l_checkLot_csr into
425 l_lot_ln,
426 l_lot_number,
427 l_lpn,
428 l_po_line_loc_id,
429 l_lot_attribute_category,
430 l_cattribute1,
431 l_cattribute2,
432 l_cattribute3,
433 l_cattribute4,
434 l_cattribute5,
435 l_cattribute6,
436 l_cattribute7,
437 l_cattribute8,
438 l_cattribute9,
439 l_cattribute10,
440 l_cattribute11,
441 l_cattribute12,
442 l_cattribute13,
443 l_cattribute14,
444 l_cattribute15,
445 l_cattribute16,
446 l_cattribute17,
447 l_cattribute18,
448 l_cattribute19,
449 l_cattribute20,
450 l_dattribute1,
451 l_dattribute2,
452 l_dattribute3,
453 l_dattribute4,
454 l_dattribute5,
455 l_dattribute6,
456 l_dattribute7,
457 l_dattribute8,
458 l_dattribute9,
459 l_dattribute10,
460 l_nattribute1,
461 l_nattribute2,
462 l_nattribute3,
463 l_nattribute4,
464 l_nattribute5,
465 l_nattribute6,
466 l_nattribute7,
467 l_nattribute8,
468 l_nattribute9,
469 l_nattribute10,
470 l_grade_code,
471 l_origination_date,
472 l_date_code,
473 l_status_id,
474 l_change_date,
475 l_age,
476 l_retest_date,
477 l_maturity_date,
478 l_item_size,
479 l_color,
480 l_volume,
481 l_volume_uom,
482 l_place_of_origin,
483 l_best_by_date,
484 l_length,
485 l_length_uom,
486 l_recycled_content,
487 l_thickness,
488 l_thickness_uom,
489 l_width,
490 l_width_uom,
491 l_territory_code,
492 l_supplier_lot_number,
493 l_vendor_name;
494 exit when l_checkLot_csr%NOTFOUND;
495
496 l_c_attributes_tbl.extend(20);
497 l_n_attributes_tbl.extend(10);
498 l_d_attributes_tbl.extend(10);
499
500 l_c_attributes_tbl(1) := l_cattribute1;
501 l_c_attributes_tbl(2) := l_cattribute2;
502 l_c_attributes_tbl(3) := l_cattribute3;
503 l_c_attributes_tbl(4) := l_cattribute4;
504 l_c_attributes_tbl(5) := l_cattribute5;
505 l_c_attributes_tbl(6) := l_cattribute6;
506 l_c_attributes_tbl(7) := l_cattribute7;
507 l_c_attributes_tbl(8) := l_cattribute8;
508 l_c_attributes_tbl(9) := l_cattribute9;
509 l_c_attributes_tbl(10) := l_cattribute10;
510 l_c_attributes_tbl(11) := l_cattribute11;
511 l_c_attributes_tbl(12) := l_cattribute12;
512 l_c_attributes_tbl(13) := l_cattribute13;
513 l_c_attributes_tbl(14) := l_cattribute14;
514 l_c_attributes_tbl(15) := l_cattribute15;
515 l_c_attributes_tbl(16) := l_cattribute16;
516 l_c_attributes_tbl(17) := l_cattribute17;
517 l_c_attributes_tbl(18) := l_cattribute18;
518 l_c_attributes_tbl(19) := l_cattribute19;
519 l_c_attributes_tbl(20) := l_cattribute20;
520 l_d_attributes_tbl(1) := l_dattribute1;
521 l_d_attributes_tbl(2) := l_dattribute2;
522 l_d_attributes_tbl(3) := l_dattribute3;
523 l_d_attributes_tbl(4) := l_dattribute4;
524 l_d_attributes_tbl(5) := l_dattribute5;
525 l_d_attributes_tbl(6) := l_dattribute6;
526 l_d_attributes_tbl(7) := l_dattribute7;
527 l_d_attributes_tbl(8) := l_dattribute8;
528 l_d_attributes_tbl(9) := l_dattribute9;
529 l_d_attributes_tbl(10) := l_dattribute10;
530 l_n_attributes_tbl(1) := l_nattribute1;
531 l_n_attributes_tbl(2) := l_nattribute2;
532 l_n_attributes_tbl(3) := l_nattribute3;
533 l_n_attributes_tbl(4) := l_nattribute4;
534 l_n_attributes_tbl(5) := l_nattribute5;
535 l_n_attributes_tbl(6) := l_nattribute6;
536 l_n_attributes_tbl(7) := l_nattribute7;
537 l_n_attributes_tbl(8) := l_nattribute8;
538 l_n_attributes_tbl(9) := l_nattribute9;
539 l_n_attributes_tbl(10) := l_nattribute10;
540
541
542 POS_ASN_CREATE_PVT.ValidateLot(
543 p_api_version => 1
544 , x_return_status => l_lot_status
545 , p_validation_mode => inv_rcv_integration_apis.G_EXISTS_OR_CREATE
546 , x_is_new_lot => l_is_new_lot
547 , p_lot_number => l_lot_number
548 , p_line_loc_id => l_po_line_loc_id
549 , p_lot_attribute_category => l_lot_attribute_category
550 , p_c_attributes_tbl => l_c_attributes_tbl
551 , p_n_attributes_tbl => l_n_attributes_tbl
552 , p_d_attributes_tbl => l_d_attributes_tbl
553 , p_grade_code => l_grade_code
554 , p_origination_date => l_origination_date
555 , p_date_code => l_date_code
556 , p_status_id => l_status_id
557 , p_change_date => l_change_date
558 , p_age => l_age
559 , p_retest_date => l_retest_date
560 , p_maturity_date => l_maturity_date
561 , p_item_size => l_item_size
562 , p_color => l_color
563 , p_volume => l_volume
564 , p_volume_uom => l_volume_uom
565 , p_place_of_origin => l_place_of_origin
566 , p_best_by_date => l_best_by_date
567 , p_length => l_length
568 , p_length_uom => l_length_uom
569 , p_recycled_content => l_recycled_content
570 , p_thickness => l_thickness
571 , p_thickness_uom => l_thickness_uom
572 , p_width => l_width
573 , p_width_uom => l_width_uom
574 , p_territory_code => l_territory_code
575 , p_supplier_lot_number => l_supplier_lot_number
576 , p_vendor_name => l_vendor_name
577 , x_return_code => l_lot_return_code
578 , x_return_msg => l_lot_return_msg
579 );
580
581 if(l_lot_status= FND_API.G_RET_STS_SUCCESS AND l_lot_return_code= 'F')
582 then
583
584 --Lot LOT at line LINE_NUM is invalid: ERROR_MSG
585 fnd_message.set_name('POS','POS_EXASN_INVALID_LOT');
586 fnd_message.set_token('LOT',l_lot_number);
587 fnd_message.set_token('LINE_NUM',l_lot_ln);
588 fnd_message.set_token('ERROR_MSG',l_lot_return_msg);
589 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
590 raise l_25errors;
591 end if;
592 elsif(l_lot_status <> FND_API.G_RET_STS_SUCCESS) then
593 if(InsertError(x_error_tbl, 'Unexpected error when validating Lot Number at line: '||l_lot_ln||':'||l_lot_return_msg, x_error_pointer)=1) then
594 raise l_25errors;
595 end if;
596 end if;
597
598 if(l_lpn is not null) then
599 POS_ASN_CREATE_PVT.ValidateLpn(
600 p_api_version => 1.0
601 , x_return_status => l_lpn_status
602 , p_lpn => l_lpn
603 , p_line_loc_id => l_po_line_loc_id
604 , x_return_code => l_lpn_return_code
605 , x_return_msg => l_lpn_return_msg
606 );
607
608 if(l_lpn_status = FND_API.G_RET_STS_SUCCESS AND l_lpn_return_code = 'F')
609 then
610 --License plate number LPN at line LINE_NUM is invalid: ERROR_MSG
611 fnd_message.set_name('POS','POS_EXASN_INVALID_LPN');
612 fnd_message.set_token('LPN',l_lpn);
613 fnd_message.set_token('LINE_NUM',l_lot_ln);
614 fnd_message.set_token('ERROR_MSG',l_lpn_return_msg);
615 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
616 raise l_25errors;
617 end if;
618 elsif(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
619 if(InsertError(x_error_tbl, 'Error while validating License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
620 raise l_25errors;
621 end if;
622 end if;
623 end if;
624
625 l_c_attributes_tbl.trim(20);
626 l_d_attributes_tbl.trim(10);
627 l_n_attributes_tbl.trim(10);
628
629 end loop;
630 close l_checkLot_csr;
631
632
633 open l_checkSerial_csr;
634 loop
635 fetch l_checkSerial_csr into
636 l_ser_ln,
637 l_fm_serial,
638 l_to_serial,
639 l_ser_qty,
640 l_lpn,
641 l_po_line_loc_id,
642 l_lot_number;
643 exit when l_checkSerial_csr%NOTFOUND;
644 POS_ASN_CREATE_PVT.ValidateSerialRange(
645 p_api_version => 1.0
646 , x_return_status => l_ser_status
647 , p_fm_serial_number => l_fm_serial
648 , p_to_serial_number => l_to_serial
649 , p_quantity => l_ser_qty
650 , p_lot_number => l_lot_number
651 , p_line_loc_id => l_po_line_loc_id
652 , x_return_code => l_ser_return_code
653 , x_return_msg => l_ser_return_msg);
654
655 if(l_ser_status = FND_API.G_RET_STS_SUCCESS and l_ser_return_code = 'F')
656 then
657 --Serial range (From FMSERIAL to TOSERIAL with quantity QTY) at line LINE_NUM is invalid: ERROR_MSG
658 fnd_message.set_name('POS','POS_EXASN_INVALID_SER');
659 fnd_message.set_token('FMSERIAL',l_fm_serial);
660 fnd_message.set_token('TOSERIAL',l_to_serial);
661 fnd_message.set_token('QTY',l_ser_qty);
662 fnd_message.set_token('LINE_NUM',l_ser_ln);
663 fnd_message.set_token('ERROR_MSG',l_ser_return_msg);
664 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
665 raise l_25errors;
666 end if;
667 elsif(l_ser_status <> FND_API.G_RET_STS_SUCCESS) then
668 if(InsertError(x_error_tbl, 'Error while validating Serial Range/Quantity at line: '||l_ser_ln||':'||l_ser_return_msg, x_error_pointer)=1) then
669 raise l_25errors;
670 end if;
671 end if;
672 if(l_lpn is not null) then
673 POS_ASN_CREATE_PVT.ValidateLpn(
674 p_api_version => 1.0
675 , x_return_status => l_lpn_status
676 , p_lpn => l_lpn
677 , p_line_loc_id => l_po_line_loc_id
678 , x_return_code => l_lpn_return_code
679 , x_return_msg => l_lpn_return_msg
680 );
681
682 if(l_lpn_status = FND_API.G_RET_STS_SUCCESS AND l_lpn_return_code = 'F')
683 then
684 --License plate number LPN at line LINE_NUM is invalid: ERROR_MSG
685 fnd_message.set_name('POS','POS_EXASN_INVALID_LPN');
686 fnd_message.set_token('LPN',l_lpn);
687 fnd_message.set_token('LINE_NUM',l_ser_ln);
688 fnd_message.set_token('ERROR_MSG',l_lpn_return_msg);
689 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
690 raise l_25errors;
691 end if;
692 elsif(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
693 if(InsertError(x_error_tbl, 'Error while validating License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
694 raise l_25errors;
695 end if;
696 end if;
697 end if;
698
699
700
701 end loop;
702 close l_checkSerial_csr;
703
704 open l_checkLpn_csr;
705 loop
706 fetch l_checkLpn_csr into
707 l_lpn_ln,
708 l_lpn,
709 l_parent_lpn,
710 l_po_line_loc_id;
711 exit when l_checkLpn_csr%NOTFOUND;
712 POS_ASN_CREATE_PVT.ValidateLpn(
713 p_api_version => 1.0
714 , x_return_status => l_lpn_status
715 , p_lpn => l_lpn
716 , p_line_loc_id => l_po_line_loc_id
717 , x_return_code => l_lpn_return_code
718 , x_return_msg => l_lpn_return_msg
719 );
720
721 if(l_lpn_status = FND_API.G_RET_STS_SUCCESS AND l_lpn_return_code = 'F')
722 then
723
724 --License plate number LPN at line LINE_NUM is invalid: ERROR_MSG
725 fnd_message.set_name('POS','POS_EXASN_INVALID_LPN');
726 fnd_message.set_token('LPN',l_lpn);
727 fnd_message.set_token('LINE_NUM',l_lpn_ln);
728 fnd_message.set_token('ERROR_MSG',l_lpn_return_msg);
729 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
730 raise l_25errors;
731 end if;
732
733 elsif(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
734 if(InsertError(x_error_tbl, 'Error while validating License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
735 raise l_25errors;
736 end if;
737 end if;
738
739 if(l_parent_lpn is not null) then
740 POS_ASN_CREATE_PVT.ValidateLpn(
741 p_api_version => 1.0
742 , x_return_status => l_lpn_status
743 , p_lpn => l_parent_lpn
744 , p_line_loc_id => l_po_line_loc_id
745 , x_return_code => l_lpn_return_code
746 , x_return_msg => l_lpn_return_msg
747 );
748
749 if(l_lpn_status = FND_API.G_RET_STS_SUCCESS AND l_lpn_return_code = 'F')
750 then
751 --Parent license plate number LPN at line LINE_NUM is invalid: ERROR_MSG
752 fnd_message.set_name('POS','POS_EXASN_INVALID_PLPN');
753 fnd_message.set_token('PLPN',l_parent_lpn);
754 fnd_message.set_token('LINE_NUM',l_lpn_ln);
755 fnd_message.set_token('ERROR_MSG',l_lpn_return_msg);
756 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
757 raise l_25errors;
758 end if;
759
760 elsif(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
761 if(InsertError(x_error_tbl, 'Error while validating Parent License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
762 raise l_25errors;
763 end if;
764 end if;
765
766 end if;
767
768 end loop;
769 close l_checkLpn_csr;
770 x_return_status := 'S';
771 exception when l_25errors then
772 x_return_status := 'E';
773 when others then
774 x_return_status := 'U';
775 END ValidateLls;
776
777 procedure ValidateLines(x_return_status out nocopy varchar2,
778 p_user_vendor_id_tbl in vendor_id_tbl_type,
779 p_secure_by_site in varchar2,
780 p_secure_by_contact in varchar2,
781 x_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
782 x_error_pointer in out nocopy number)
783 IS
784
785 l_doc_num po_headers_all.segment1%type;
786 l_rel_num number;
787 l_revision_num number;
788 l_po_line VARCHAR2(240);
789 l_po_shipment number;
790 l_org_name hr_operating_units.name%type;
791 l_failSecure varchar2(1);
792 l_failPORelCheck varchar2(1);
793 l_vendor_id_not_secured varchar2(1);
794 l_vendor_id number;
795 l_ind_start number;
796 l_ind_end number;
797 l_vendor_ind number;
798
799 l_early_exp exception;
800 l_25errors exception;
801 l_error_ln number;
802 l_error_field varchar2(2000);
803 --L1: UOM
804 cursor l_checkUOM_csr
805 is
806 select plnt.line_number, plnt.uom
807 from pos_exasn_lines plnt
808 where not exists
809 (select 1 from por_unit_of_measure_lov_v puomv
810 where puomv.unit_of_measure = plnt.uom);
811
812 --L2: Country of Origin
813 cursor l_checkCountryOO_csr
814 is
815 select plnt.line_number, plnt.country_of_origin
816 from pos_exasn_lines plnt
817 where plnt.country_of_origin is not null
818 and not exists
819 (select 1 from fnd_territories_vl ftv
820 where ftv.territory_code = plnt.country_of_origin);
821
822
823 --L3/L4: Check for valid Organization Name and PO/Line/Shipment Number
824 cursor l_checkOrgName_csr
825 is
826 select line_number, operating_unit
827 from pos_exasn_lines
828 where org_id is null;
829
830 cursor l_checkPO_csr
831 is
832 select
833 plnt.line_number,
834 plnt.po_number,
835 plnt.po_revision,
836 plnt.po_line,
837 plnt.po_shipment,
838 plnt.operating_unit
839 from pos_exasn_lines plnt
840 where plnt.po_header_id is null
841 and plnt.po_release_num is null;
842
843 cursor l_checkREL_csr
844 is
845 select
846 plnt.line_number,
847 plnt.po_number,
848 plnt.po_release_num,
849 plnt.po_revision,
850 plnt.po_shipment,
851 plnt.operating_unit
852 from pos_exasn_lines plnt
853 where plnt.po_header_id is null
854 and plnt.po_release_num is not null;
855
856 --L5: Check for Securing Attributes
857 cursor l_vendors_csr
858 is
859 select line_number,vendor_id
860 from pos_exasn_lines;
861
862 cursor l_checkVendorSites_csr(x_user_id number)
863 is
864 select line_number
865 from pos_exasn_lines plnt
866 where not exists(
867 select 1
868 from ak_web_user_sec_attr_values
869 WHERE web_user_id = x_user_id
870 AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
871 AND attribute_application_id = 177
872 and number_value = plnt.vendor_site_id);
873
874 cursor l_checkVendorContacts_csr(x_user_id number)
875 is
876 select line_number
877 from pos_exasn_lines plnt
878 where not exists(
879 select 1
880 from ak_web_user_sec_attr_values
881 WHERE web_user_id = x_user_id
882 AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
883 AND attribute_application_id = 177
884 and number_value = plnt.vendor_contact_id);
885
886 --L6: Check Quantity
887 l_quantity number;
888 l_convQty number;
889
890 l_primary_qty number;
891 l_line_number number;
892 l_uom pos_exasn_lines.uom%type;
893 l_po_line_location_id number;
894 l_tolerableShipQty number;
895 l_item_id number;
896 cursor l_allLines_csr
897 is
898 select line_number, quantity, uom, po_line_location_id, item_id, header_id
899 from pos_exasn_lines;
900
901
902
903 --L7: Check for existing ASN with same Shipment Number
904 l_previous_sn pos_exasn_headers.shipment_number%type;
905 l_new_sn varchar2(1);
906 cursor l_checkExistAsn_csr
907 is
908 select line_number, shipment_number
909 from
910 (
911 select pht.line_number, pht.shipment_number
912 from pos_exasn_lines plnt,
913 rcv_headers_interface rhi,
914 pos_exasn_headers pht
915 where pht.header_id = plnt.header_id
916 and pht.shipment_number = rhi.shipment_num
917 and plnt.vendor_id = rhi.vendor_id
918 and nvl(plnt.vendor_site_id, -9999) = nvl(rhi.vendor_site_id, -9999)
919 union
920 select plnt.line_number, pht.shipment_number
921 from pos_exasn_lines plnt,
922 rcv_shipment_headers rsh,
923 pos_exasn_headers pht
924 where pht.header_id = plnt.header_id
925 and pht.shipment_number = rsh.shipment_num
926 and plnt.vendor_id = rsh.vendor_id
927 and nvl(plnt.vendor_site_id, -9999) = nvl(rsh.vendor_site_id, -9999))
928 order by line_number;
929
930
931
932 l_qty_rcv_exception_code po_line_locations_all.qty_rcv_exception_code%type;
933 l_header_id number;
934
935 --L8: Check for Expected Receipt Date Tolerance per PO Shipment
936 l_receipt_days_exception_code po_line_locations_all.receipt_days_exception_code%type;
937 l_exp_rec_date pos_exasn_headers.expected_receipt_date%type;
938
939 l_days_early po_line_locations_all.DAYS_EARLY_RECEIPT_ALLOWED%type;
940 l_days_late po_line_locations_all.DAYS_LATE_RECEIPT_ALLOWED%type;
941 l_due_date date;
942 l_header_line_number number;
943 l_outsourced_assembly po_line_locations_all.outsourced_assembly%type;
944 l_po_header_id po_headers_all.po_header_id%TYPE;
945 l_clm_flag VARCHAR2(1);
946 BEGIN
947 --L1
948 open l_checkUOM_csr;
949 loop
950 fetch l_checkUOM_csr into l_error_ln, l_error_field;
951 exit when l_checkUOM_csr%NOTFOUND;
952 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
953 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_QTYUOM'));
954 fnd_message.set_token('LOV_VALUE',l_error_field);
955 fnd_message.set_token('LINE_NUM',l_error_ln);
956 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
957 raise l_25errors;
958 end if;
959 end loop;
960 close l_checkUOM_csr;
961
962 --L2
963 open l_checkCountryOO_csr;
964 loop
965 fetch l_checkCountryOO_csr into l_error_ln, l_error_field;
966 exit when l_checkCountryOO_csr%NOTFOUND;
967 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
968 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_COORIGIN'));
969 fnd_message.set_token('LOV_VALUE',l_error_field);
970 fnd_message.set_token('LINE_NUM',l_error_ln);
971 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
972 raise l_25errors;
973 end if;
974 end loop;
975 close l_checkCountryOO_csr;
976
977
978 --L3: Organization Name
979 update pos_exasn_lines plnt
980 set plnt.org_id = (
981 select hou.organization_id
982 from hr_operating_units hou
983 where hou.name = plnt.operating_unit);
984
985
986 open l_checkOrgName_csr;
987 loop
988 fetch l_checkOrgName_csr into l_error_ln, l_error_field;
989 exit when l_checkOrgName_csr%notfound;
990 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
991 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_ORGUNIT'));
992 fnd_message.set_token('LOV_VALUE',l_error_field);
993 fnd_message.set_token('LINE_NUM',l_error_ln);
994 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
995 raise l_25errors;
996 end if;
997 end loop;
998 close l_checkOrgName_csr;
999
1000 --L4: Valid Org/PO/Line/Shipment Number
1001 /*
1002 * Bug 8390933 - removed the condition 'and pha.AUTHORIZATION_STATUS = 'APPROVED''
1003 * and allows the shipments to be used to create ASN irrespective of PO status
1004 * It also allows the shipments to be used for ASN, if the PO is in 'Requires Reapproval' status
1005 */
1006 BEGIN
1007 update pos_exasn_lines plnt
1008 set (
1009 po_header_id,
1010 po_line_id,
1011 po_line_location_id,
1012 vendor_id,
1013 vendor_site_id,
1014 vendor_contact_id,
1015 ship_to_org_id,
1016 vendor_name,
1017 vendor_site_code,
1018 rate_type,
1019 rate,
1020 rate_date,
1021 item_id,
1022 item_revision,
1023 unit_price,
1024 vendor_product_num,
1025 currency_code,
1026 primary_uom,
1027 ship_to_location_code,
1028 ship_to_location_id,
1029 item_description
1030 )
1031 =
1032 (
1033 select
1034 pha.po_header_id,
1035 pla.po_line_id,
1036 plla.line_location_id,
1037 pv.vendor_id,
1038 pvs.vendor_site_id,
1039 pha.vendor_contact_id,
1040 plla.ship_to_organization_id,
1041 pv.vendor_name,
1042 pvs.vendor_site_code,
1043 pha.rate_type,
1044 pha.rate,
1045 pha.rate_date,
1046 pla.item_id,
1047 pla.item_revision,
1048 pla.unit_price,
1049 pla.vendor_product_num,
1050 pha.currency_code,
1051 (SELECT UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CLASS=
1052 (SELECT UOM_CLASS FROM MTL_UNITS_OF_MEASURE_TL WHERE
1053 UNIT_OF_MEASURE=pla.unit_meas_lookup_code
1054 AND
1055 LANGUAGE=UserEnv('Lang')
1056 )
1057 AND
1058 BASE_UOM_FLAG='Y'
1059 AND
1060 LANGUAGE=UserEnv('Lang')
1061 ),
1062 nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)),
1063 plla.ship_to_location_id,
1064 pla.item_description
1065
1066 from
1067 po_headers_all pha,
1068 po_lines_all pla,
1069 po_line_locations_all plla,
1070 po_vendors pv,
1071 po_vendor_sites_all pvs,
1072 hr_locations_all_tl hrl,
1073 hz_locations hz
1074 where pha.segment1 = plnt.po_number
1075 and pha.org_id = plnt.org_id
1076 and pha.revision_num = (SELECT pha.revision_num
1077 FROM po_headers_archive_all phaa,po_headers_all pha
1078 WHERE pha.segment1 = plnt.po_number
1079 AND pha.po_header_id =phaa.po_header_id (+)
1080 AND phaa.latest_external_flag(+) ='Y'
1081 AND phaa.revision_num =plnt.po_revision)
1082 and pha.po_header_id = pla.po_header_id
1083 and pla.line_num = plnt.po_line
1084 and pla.po_line_id = plla.po_line_id
1085 and plla.shipment_num = plnt.po_shipment
1086 and pv.vendor_id = pha.vendor_id
1087 and pvs.vendor_site_id = pha.vendor_site_id
1088 and hrl.location_id(+) = plla.ship_to_location_id
1089 and hrl.LANGUAGE(+) = USERENV('LANG')
1090 and hz.location_id(+) = plla.ship_to_location_id
1091 and pha.type_lookup_code = 'STANDARD'
1092 /*and pha.AUTHORIZATION_STATUS = 'APPROVED'*/
1093 and NVL(plla.approved_flag, 'N') = 'Y'
1094 and NVL(plla.CANCEL_FLAG, 'N') = 'N'
1095 and NVL(pha.FROZEN_FLAG, 'N') = 'N'
1096 and NVL(pha.CONSIGNED_CONSUMPTION_FLAG, 'N') <> 'Y'
1097 and NVL(plla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED')
1098 )
1099 where org_id is not null
1100 and po_release_num is null;
1101 EXCEPTION WHEN OTHERS THEN
1102 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1103 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_EXCELASN_PVT.ValidateLines.invoked','Exception');
1104 END IF;
1105 END;
1106 select count(1) into l_error_ln from pos_exasn_lines where po_line_location_id is null;
1107
1108
1109 update pos_exasn_lines plnt
1110 set (
1111 po_header_id,
1112 po_release_id,
1113 po_line_id,
1114 po_line_location_id,
1115 vendor_id,
1116 vendor_site_id,
1117 vendor_contact_id,
1118 ship_to_org_id,
1119 vendor_name,
1120 vendor_site_code,
1121 rate_type,
1122 rate,
1123 rate_date,
1124 item_id,
1125 item_revision,
1126 unit_price,
1127 vendor_product_num,
1128 currency_code,
1129 primary_uom,
1130 ship_to_location_code,
1131 ship_to_location_id,
1132 item_description
1133 )
1134 =
1135 (
1136 select
1137 pha.po_header_id,
1138 pra.po_release_id,
1139 pla.po_line_id,
1140 plla.line_location_id,
1141 pv.vendor_id,
1142 pvs.vendor_site_id,
1143 pha.vendor_contact_id,
1144 plla.ship_to_organization_id,
1145 pv.vendor_name,
1146 pvs.vendor_site_code,
1147 pha.rate_type,
1148 pha.rate,
1149 pha.rate_date,
1150 pla.item_id,
1151 pla.item_revision,
1152 pla.unit_price,
1153 pla.vendor_product_num,
1154 pha.currency_code,
1155 (SELECT UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CLASS=
1156 (SELECT UOM_CLASS FROM MTL_UNITS_OF_MEASURE_TL WHERE
1157 UNIT_OF_MEASURE=pla.unit_meas_lookup_code
1158 AND
1159 LANGUAGE=UserEnv('Lang')
1160 )
1161 AND
1162 BASE_UOM_FLAG='Y'
1163 AND
1164 LANGUAGE=UserEnv('Lang')
1165 ),
1166 nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)),
1167 plla.ship_to_location_id,
1168 pla.item_description
1169 from
1170 po_headers_all pha,
1171 po_releases_all pra,
1172 po_lines_all pla,
1173 po_line_locations_all plla,
1174 po_vendors pv,
1175 po_vendor_sites_all pvs,
1176 hr_locations_all_tl hrl,
1177 hz_locations hz
1178 where pha.segment1 = plnt.po_number
1179 and pha.org_id = plnt.org_id
1180 and pha.po_header_id = pra.po_header_id
1181 and pra.release_num = plnt.po_release_num
1182 and pra.revision_num = (SELECT pha.revision_num
1183 FROM po_headers_archive_all phaa,po_headers_all pha
1184 WHERE pha.segment1 = plnt.po_number
1185 AND pha.po_header_id =phaa.po_header_id (+)
1186 AND phaa.latest_external_flag(+) ='Y'
1187 AND phaa.revision_num =plnt.po_revision)
1188 and pra.po_release_id = plla.po_release_id
1189 and plla.shipment_num = plnt.po_shipment
1190 and pha.vendor_id = pv.vendor_id
1191 and pha.vendor_site_id = pvs.vendor_site_id
1192 and hrl.location_id(+) = plla.ship_to_location_id
1193 and hrl.LANGUAGE(+) = USERENV('LANG')
1194 and hz.location_id(+) = plla.ship_to_location_id
1195 and pla.po_line_id = plla.po_line_id
1196 /*and pra.AUTHORIZATION_STATUS = 'APPROVED'*/
1197 and NVL(plla.approved_flag, 'N') = 'Y'
1198 and NVL(plla.CANCEL_FLAG, 'N') = 'N'
1199 and NVL(pra.FROZEN_FLAG, 'N') = 'N'
1200 and NVL(pra.CONSIGNED_CONSUMPTION_FLAG, 'N') <> 'Y'
1201 and NVL(plla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED')
1202
1203 )
1204 where org_id is not null
1205 and po_release_num is not null;
1206
1207 l_failPORelCheck := 'F';
1208 open l_checkPO_csr;
1209 loop
1210 fetch l_checkPO_csr into
1211 l_error_ln,
1212 l_doc_num,
1213 l_revision_num,
1214 l_po_line,
1215 l_po_shipment,
1216 l_org_name;
1217 exit when l_checkPO_csr%NOTFOUND;
1218 l_failPORelCheck := 'T';
1219 fnd_message.set_name('POS','POS_EXASN_PO_NOEXIST');
1220 fnd_message.set_token('DOC_NUM',l_doc_num);
1221 fnd_message.set_token('REV_NUM',l_revision_num);
1222 fnd_message.set_token('LINENUM',l_po_line);
1223 fnd_message.set_token('SHIP_NUM',l_po_shipment);
1224 fnd_message.set_token('LINE_NUM',l_error_ln);
1225 fnd_message.set_token('OP_UNIT',l_org_name);
1226 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1227 raise l_25errors;
1228 end if;
1229 end loop;
1230 close l_checkPO_csr;
1231
1232 open l_checkREL_csr;
1233 loop
1234 fetch l_checkREL_csr into
1235 l_error_ln,
1236 l_doc_num,
1237 l_rel_num,
1238 l_revision_num,
1239 l_po_shipment,
1240 l_org_name;
1241 exit when l_checkREL_csr%NOTFOUND;
1242 l_failPORelCheck := 'T';
1243 fnd_message.set_name('POS','POS_EXASN_REL_NO_EXIST');
1244 fnd_message.set_token('DOC_NUM',l_doc_num);
1245 fnd_message.set_token('REL_NUM',l_rel_num);
1246 fnd_message.set_token('REV_NUM',l_revision_num);
1247 fnd_message.set_token('SHIP_NUM',l_po_shipment);
1248 fnd_message.set_token('LINE_NUM',l_error_ln);
1249 fnd_message.set_token('OP_UNIT',l_org_name);
1250 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1251 raise l_25errors;
1252 end if;
1253 end loop;
1254 close l_checkREL_csr;
1255
1256 if(l_failPORelCheck = 'T') then
1257 raise l_early_exp;
1258 end if;
1259
1260 l_failSecure := 'F';
1261
1262 --The following code segment validates all lines to make sure their vendor ids --are in the set of all supplier defined secured attributes for org id,
1263 --ICX_SUPPLIER_ORG_ID
1264
1265 --For each vendor id of each line, we check that against the retrieved set
1266 --of secured org id attributes to make sure it is in the set. Otherwise,
1267 --throw an error message
1268 OPEN l_vendors_csr;
1269 LOOP
1270 FETCH l_vendors_csr into l_error_ln, l_vendor_id;
1271 EXIT WHEN l_vendors_csr%NOTFOUND;
1272
1273 l_ind_start := p_user_vendor_id_tbl.first();
1274 l_ind_end := p_user_vendor_id_tbl.last();
1275 l_vendor_id_not_secured := 'T';
1276
1277 --make sure all vendor ids are secured
1278 FOR l_vendor_ind IN l_ind_start .. l_ind_end
1279 LOOP
1280
1281 IF(l_vendor_id = p_user_vendor_id_tbl(l_vendor_ind)) THEN
1282 l_vendor_id_not_secured := 'F';
1283 END IF;
1284
1285 END LOOP;
1286
1287 IF(l_vendor_id_not_secured = 'T') THEN
1288
1289 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1290 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1291 MODULE =>'pos.plsql.pos_asn_create_pvt.ValidateLines',
1292 MESSAGE => 'Line : ' || l_error_ln || 'Vendor Id: ' ||
1293 l_vendor_id||' is not secured');
1294 END IF;
1295
1296 l_failSecure := 'T';
1297 fnd_message.set_name('POS','POS_EXASN_NOT_SEC');
1298 fnd_message.set_token('LINE_NUM',l_error_ln);
1299 IF(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) THEN
1300 raise l_25errors;
1301 END IF;
1302 END IF;
1303
1304 END LOOP;
1305 CLOSE l_vendors_csr;
1306
1307
1308 if(p_secure_by_site = 'T') then
1309 open l_checkVendorSites_csr(fnd_global.user_id);
1310 loop
1311 fetch l_checkVendorSites_csr into l_error_ln;
1312 exit when l_checkVendorSites_csr%NOTFOUND;
1313 l_failSecure := 'T';
1314 fnd_message.set_name('POS','POS_EXASN_NOT_SEC');
1315 fnd_message.set_token('LINE_NUM',l_error_ln);
1316 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1317 raise l_25errors;
1318 end if;
1319 end loop;
1320 close l_checkVendorSites_csr;
1321 end if;
1322
1323 if(p_secure_by_contact = 'T') then
1324 open l_checkVendorContacts_csr(fnd_global.user_id);
1325 loop
1326 fetch l_checkVendorContacts_csr into l_error_ln;
1327 exit when l_checkVendorContacts_csr%NOTFOUND;
1328 l_failSecure := 'T';
1329 fnd_message.set_name('POS','POS_EXASN_NOT_SEC');
1330 fnd_message.set_token('LINE_NUM',l_error_ln);
1331 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1332 raise l_25errors;
1333 end if;
1334 end loop;
1335 close l_checkVendorContacts_csr;
1336 end if;
1337
1338 if(l_failSecure = 'T') then
1339 raise l_early_exp;
1340 end if;
1341
1342 --L6+L8 (to be done before creating new Lines, because we cannot split up the shipments into multiple RTIs and then do validations
1343
1344 open l_allLines_csr;
1345 loop
1346 fetch l_allLines_csr into
1347 l_line_number,
1348 l_quantity,
1349 l_uom,
1350 l_po_line_location_id,
1351 l_item_id,
1352 l_header_id;
1353 exit when l_allLines_csr%NOTFOUND;
1354 select
1355 PLL.qty_rcv_exception_code,
1356 PLL.receipt_days_exception_code,
1357 PLL.DAYS_EARLY_RECEIPT_ALLOWED,
1358 PLL.DAYS_LATE_RECEIPT_ALLOWED,
1359 NVL(PLL.PROMISED_DATE,PLL.NEED_BY_DATE),
1360 PLL.outsourced_assembly
1361 into
1362 l_qty_rcv_exception_code,
1363 l_receipt_days_exception_code,
1364 l_days_early,
1365 l_days_late,
1366 l_due_date,
1367 l_outsourced_assembly
1368 from po_line_locations_all PLL
1369 where line_location_id = l_po_line_location_id;
1370
1371 if(l_qty_rcv_exception_code = 'REJECT') then
1372
1373 l_primary_qty := getConvertedQuantity(l_po_line_location_id, l_quantity, l_uom);
1374
1375 l_tolerableShipQty := POS_CREATE_ASN.getTolerableShipmentQuantity(l_po_line_location_id);
1376
1377 if(l_primary_Qty = -1) then
1378 fnd_message.set_name('POS','POS_EXASN_EXCEPT_UOM');
1379 --An exception occured when trying to validate the quantity at line LINE_NUM. Please make sure the UOM you provided on that line is valid for the corresponding item.
1380 fnd_message.set_token('LINE_NUM',l_line_number);
1381 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1382 raise l_25errors;
1383 end if;
1384 elsif(l_tolerableShipQty < l_primary_qty ) then
1385
1386 fnd_message.set_name('POS','POS_EXASN_QTY_GRT_REM');
1387 fnd_message.set_token('QTY',l_quantity);
1388 fnd_message.set_token('LINE_NUM',l_line_number);
1389 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1390 raise l_25errors;
1391 end if;
1392 end if;
1393 end if;
1394
1395 if(l_receipt_days_exception_code = 'REJECT') then
1396 select
1397 expected_receipt_date,
1398 line_number
1399 into
1400 l_exp_rec_date,
1401 l_header_line_number
1402 from pos_exasn_headers
1403 where header_id = l_header_id;
1404
1405 if(l_exp_rec_date > l_due_date+l_days_late OR l_exp_rec_date < l_due_date-l_days_early) then
1406 fnd_message.set_name('POS','POS_EXASN_ERDATE_TOL');
1407 fnd_message.set_token('HDR_LINE_NUM',l_header_line_number);
1408 fnd_message.set_token('LINE_NUM',l_line_number);
1409 --the expected receipt date defined in line HDR_LINE_NUM will violate the tolerance for PO Shipment at line LINE_NUM
1410 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1411 raise l_25errors;
1412 end if;
1413 end if;
1414 end if;
1415 BEGIN
1416 SELECT po_header_id
1417 INTO l_po_header_id
1418 FROM po_line_locations_all
1419 WHERE line_location_id = l_po_line_location_id;
1420
1421 SELECT NVL(PDSH.CLM_FLAG,'N') CLM_FLAG
1422 INTO l_clm_flag
1423 FROM PO_HEADERS_ALL POH,
1424 PO_DOC_STYLE_HEADERS PDSH
1425 WHERE POH.po_header_id = l_po_header_id AND
1426 NVL(POH.STYLE_ID, 1) = PDSH.STYLE_ID (+) AND
1427 PDSH.STATUS (+) = 'ACTIVE';
1428
1429 IF(l_clm_flag = 'Y') THEN
1430 IF (l_tolerableShipQty < l_primary_qty) THEN
1431 fnd_message.set_name('POS','POS_EXASN_QTY_GRT_REM');
1432 fnd_message.set_token('QTY',l_quantity);
1433 fnd_message.set_token('LINE_NUM',l_line_number);
1434 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1435 raise l_25errors;
1436 end if;
1437 END IF;
1438 END IF;
1439 EXCEPTION WHEN OTHERS THEN
1440 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1441 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_EXCELASN_PVT.ValidateLines.invoked','Exception');
1442 END IF;
1443 END;
1444
1445 if ( l_outsourced_assembly = 1 ) then
1446 fnd_message.set_name('POS','POS_EXASN_ERR_SHIKYU');
1447 fnd_message.set_token('LINE_NUM',l_line_number);
1448 -- Shipments can not be created using Outsourced assembly items
1449 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1450 raise l_25errors;
1451 end if;
1452 end if;
1453 end loop;
1454 close l_allLines_csr;
1455
1456 --L7
1457
1458 open l_checkExistAsn_csr;
1459 loop
1460 fetch l_checkExistAsn_csr into l_error_ln, l_error_field;
1461 exit when l_checkExistAsn_csr%NOTFOUND;
1462
1463 if(l_previous_sn is null OR l_previous_sn <> l_error_field) then
1464 l_new_sn := 'T';
1465 l_previous_sn := l_error_field;
1466 else
1467 l_new_sn := 'F';
1468 end if;
1469
1470 if(l_new_sn = 'T') then
1471 fnd_message.set_name('POS','POS_EXASN_DUPE_SHIP');
1472 --Shipment number SHIP_NUM at line LINE_NUM is invalid because there is an existing shipment notice with the same vendor, vendor site, and shipment number.
1473 fnd_message.set_token('LINE_NUM',l_error_ln);
1474 fnd_message.set_token('SHIP_NUM',l_error_field);
1475 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1476 raise l_25errors;
1477 end if;
1478 end if;
1479
1480 end loop;
1481 close l_checkExistAsn_csr;
1482
1483 x_return_status := 'S';
1484 exception when l_early_exp then
1485 x_return_status := 'E';
1486 when l_25errors then
1487 x_return_status := 'E';
1488 when others then
1489 x_return_status := 'U';
1490 END ValidateLines;
1491
1492
1493 procedure ValidateHeaders(x_return_status out nocopy varchar2,
1494 p_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
1495 p_error_pointer in out nocopy number)
1496 IS
1497 l_error_ln number;
1498 l_error_field varchar2(200);
1499 l_25errors exception;
1500
1501
1502 --H1: Freight Carrier Code (TO BE DONE IN FixHeadersAndLines, because we'll need pht.ship_to_org_id)
1503
1504 --H2: GROSS WEIGHT UOM
1505 cursor l_checkGrossWtUOM_csr
1506 is
1507 select pht.line_number, pht.gross_weight_uom
1508 from pos_exasn_headers pht
1509 where pht.gross_weight_uom is not null
1510 and not exists
1511 (select 1 from por_unit_of_measure_lov_v puomv
1512 where puomv.unit_of_measure = pht.gross_weight_uom);
1513
1514
1515 --H3: NET WEIGHT UOM
1516 cursor l_checkNetWtUOM_csr
1517 is
1518 select pht.line_number, pht.net_weight_uom
1519 from pos_exasn_headers pht
1520 where pht.net_weight_uom is not null
1521 and not exists
1522 (select 1 from por_unit_of_measure_lov_v puomv
1523 where puomv.unit_of_measure = pht.net_weight_uom);
1524
1525 --H4: TAR WEIGHT UOM
1526 cursor l_checkTarWtUOM_csr
1527 is
1528 select pht.line_number, pht.tar_weight_uom
1529 from pos_exasn_headers pht
1530 where pht.tar_weight_uom is not null
1531 and not exists
1532 (select 1 from por_unit_of_measure_lov_v puomv
1533 where puomv.unit_of_measure = pht.tar_weight_uom);
1534
1535 --H5: FREIGHT TERMS
1536 cursor l_checkFreightTerms_csr
1537 is
1538 select pht.line_number, pht.freight_terms
1539 from pos_exasn_headers pht
1540 where pht.freight_terms is not null
1541 and not exists
1542 (select 1 from po_lookup_codes plc
1543 where plc.lookup_type = 'FREIGHT TERMS'and sysdate < nvl(plc.inactive_date, sysdate + 1)
1544 and plc.lookup_code = pht.freight_terms);
1545
1546 --H7: SHIPMENT DATE has to be < SYSDATE
1547 cursor l_checkShipDate_csr
1548 is
1549 select pht.line_number, shipment_date
1550 from pos_exasn_headers pht
1551 where shipment_date > sysdate;
1552
1553 --H8: INVOICE PAYMENT TERMS
1554 cursor l_checkInvPT_csr
1555 is
1556 select pht.line_number, pht.payment_terms
1557 from pos_exasn_headers pht
1558 where pht.payment_terms is not null
1559 and not exists
1560 (select 1 from ap_terms_val_v av
1561 where av.name = pht.payment_terms);
1562
1563 --H9: Bad if expected receipt Date is BEFORE Shipment Date
1564 cursor l_checkER_Ship_csr
1565 is
1566 select pht.line_number
1567 from pos_exasn_headers pht
1568 where expected_receipt_date < shipment_date;
1569
1570
1571
1572 BEGIN
1573
1574 --H2
1575 open l_checkGrossWtUOM_csr;
1576 loop
1577 fetch l_checkGrossWtUOM_csr into l_error_ln, l_error_field;
1578 exit when l_checkGrossWtUOM_csr%NOTFOUND;
1579 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1580 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_GWUOM'));
1581 fnd_message.set_token('LOV_VALUE',l_error_field);
1582 fnd_message.set_token('LINE_NUM',l_error_ln);
1583 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1584 raise l_25errors;
1585 end if;
1586 end loop;
1587 close l_checkGrossWtUOM_csr;
1588
1589 --Convert Gross Weight UOM to code
1590 update pos_exasn_headers
1591 set gross_weight_uom =
1592 (select puomv.uom_code
1593 from por_unit_of_measure_lov_v puomv
1594 where gross_weight_uom is not null
1595 and gross_weight_uom = puomv.unit_of_measure);
1596
1597 --H3
1598 open l_checkNetWtUOM_csr;
1599 loop
1600 fetch l_checkNetWtUOM_csr into l_error_ln, l_error_field;
1601 exit when l_checkNetWtUOM_csr%NOTFOUND;
1602 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1603 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_NWUOM'));
1604 fnd_message.set_token('LOV_VALUE',l_error_field);
1605 fnd_message.set_token('LINE_NUM',l_error_ln);
1606 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1607 raise l_25errors;
1608 end if;
1609 end loop;
1610 close l_checkNetWtUOM_csr;
1611
1612 --Convert Net Weight UOM to code
1613 update pos_exasn_headers
1614 set net_weight_uom =
1615 (select puomv.uom_code
1616 from por_unit_of_measure_lov_v puomv
1617 where net_weight_uom is not null
1618 and net_weight_uom = puomv.unit_of_measure);
1619
1620 --H4
1621 open l_checkTarWtUOM_csr;
1622 loop
1623 fetch l_checkTarWtUOM_csr into l_error_ln, l_error_field;
1624 exit when l_checkTarWtUOM_csr%NOTFOUND;
1625 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1626 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_TWUOM'));
1627 fnd_message.set_token('LOV_VALUE',l_error_field);
1628 fnd_message.set_token('LINE_NUM',l_error_ln);
1629 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1630 raise l_25errors;
1631 end if;
1632 end loop;
1633 close l_checkTarWtUOM_csr;
1634
1635 --Convert Tar Weight UOM to code
1636 update pos_exasn_headers
1637 set tar_weight_uom =
1638 (select puomv.uom_code
1639 from por_unit_of_measure_lov_v puomv
1640 where tar_weight_uom is not null
1641 and tar_weight_uom = puomv.unit_of_measure);
1642
1643
1644 --H5
1645 open l_checkFreightTerms_csr;
1646 loop
1647 fetch l_checkFreightTerms_csr into l_error_ln, l_error_field;
1648 exit when l_checkFreightTerms_csr%NOTFOUND;
1649 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1650 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_FRTERM'));
1651 fnd_message.set_token('LOV_VALUE',l_error_field);
1652 fnd_message.set_token('LINE_NUM',l_error_ln);
1653 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1654 raise l_25errors;
1655 end if;
1656 end loop;
1657 close l_checkFreightTerms_csr;
1658
1659 --H7
1660 open l_checkShipDate_csr;
1661 loop
1662 fetch l_checkShipDate_csr into l_error_ln, l_error_field;
1663 exit when l_checkShipDate_csr%NOTFOUND;
1664 fnd_message.set_name('POS','POS_EXASN_BAD_SHP_DATE');
1665 fnd_message.set_token('LINE_NUM',l_error_ln);
1666 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1667 raise l_25errors;
1668 end if;
1669 end loop;
1670 close l_checkShipDate_csr;
1671
1672 --H8
1673 open l_checkInvPT_csr;
1674 loop
1675 fetch l_checkInvPT_csr into l_error_ln, l_error_field;
1676 exit when l_checkInvPT_csr%NOTFOUND;
1677 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1678 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_PAYTERM'));
1679 fnd_message.set_token('LOV_VALUE',l_error_field);
1680 fnd_message.set_token('LINE_NUM',l_error_ln);
1681 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1682 raise l_25errors;
1683 end if;
1684 end loop;
1685 close l_checkInvPT_csr;
1686
1687 --H9
1688 open l_checkER_Ship_csr;
1689 loop
1690 fetch l_checkER_Ship_csr into l_error_ln;
1691 exit when l_checkER_Ship_csr%NOTFOUND;
1692 fnd_message.set_name('POS','POS_EXASN_ERDATE_SDATE');
1693 fnd_message.set_token('LINE_NUM',l_error_ln);
1694 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1695 raise l_25errors;
1696 end if;
1697 end loop;
1698 close l_checkER_Ship_csr;
1699
1700 x_return_status := 'S';
1701 exception when l_25errors then
1702 x_return_status := 'E';
1703 when others then
1704 x_return_status := 'U';
1705 end ValidateHeaders;
1706
1707
1708 procedure UpdateLinesAndLls(x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
1709 l_error_pointer in out NOCOPY number)
1710 IS
1711 l_uom pos_exasn_lines.uom%type;
1712 l_po_line_location_id number;
1713 l_tolerableShipQty number;
1714 l_item_id number;
1715 l_quantity number;
1716 l_convQty number;
1717 l_primary_qty number;
1718 l_line_number number;
1719 l_line_id number;
1720
1721 cursor l_allLines_csr
1722 is
1723 select line_number, quantity, uom, po_line_location_id, item_id, line_id
1724 from pos_exasn_lines;
1725
1726 BEGIN
1727 -- Update All lines with Primary Quantity and Invoiced Quantity
1728 open l_allLines_csr;
1729 loop
1730 fetch l_allLines_csr into l_line_number,
1731 l_quantity,
1732 l_uom,
1733 l_po_line_location_id,
1734 l_item_id,
1735 l_line_id;
1736 exit when l_allLines_csr%NOTFOUND;
1737
1738 l_primary_qty := getConvertedQuantity(l_po_line_location_id, l_quantity, l_uom);
1739
1740 if(l_primary_Qty = -1) then
1741 if(InsertError(x_error_tbl, 'Unexpected Error while finding primary quantity for new lines', l_error_pointer)=1) then
1742 null;
1743 end if;
1744 else
1745
1746 update pos_exasn_lines
1747 set primary_quantity = l_primary_qty
1748 where line_id = l_line_id;
1749
1750 update pos_exasn_lines
1751 set invoiced_quantity = POS_QUANTITIES_S.get_invoice_qty
1752 (l_po_line_location_id,
1753 l_uom,
1754 l_item_id,
1755 l_quantity)
1756 where line_id = l_line_id;
1757 end if;
1758 end loop;
1759 close l_allLines_csr;
1760
1761
1762
1763 update pos_exasn_lines plnt
1764 set lpn_group_id = (select pht.lpn_group_id
1765 from pos_exasn_headers pht
1766 where pht.header_id = plnt.header_id)
1767 where
1768 (
1769 plnt.lls_code in ('LAS','LOT')
1770
1771 and exists(
1772 select 1 from pos_exasn_lots plot
1773 where plot.line_id = plnt.line_id
1774 and plot.license_plate_number is not null))
1775 or
1776 (
1777 plnt.lls_code = 'LPN'
1778 and exists(
1779 select 1 from pos_exasn_lpns plpn
1780 where plpn.line_id = plnt.line_id
1781 and plpn.quantity is not null))
1782 or
1783 (
1784 plnt.lls_code = 'SER'
1785 and exists(
1786 select 1 from pos_exasn_serials pst
1787 where pst.line_id = plnt.line_id
1788 and pst.license_plate_number is not null));
1789
1790
1791
1792 update pos_exasn_lots plot
1793 set (plot.product_transaction_id, plot.uom) =(select plnt.interface_transaction_id, plnt.uom
1794 from pos_exasn_lines plnt
1795 where plnt.header_id = plot.header_id
1796 and plnt.line_id = plot.line_id);
1797
1798
1799 update pos_exasn_serials pst
1800 set (pst.product_transaction_id) = (select plnt.interface_transaction_id
1801 from pos_exasn_lines plnt
1802 where plnt.header_id = pst.header_id
1803 and plnt.line_id = pst.line_id);
1804
1805 END UpdateLinesAndLls;
1806
1807
1808 procedure CreateRTI4Ser
1809 is
1810 cursor l_distinctLpn_ser_csr(x_header_id number, x_line_id number)
1811 is
1812 select
1813 distinct
1814 license_plate_number
1815 from pos_exasn_serials
1816 where header_id = x_header_id
1817 and line_id = x_line_id
1818 and lot_id = 0;
1819 --assuming lot_id = 0 ==> SER items
1820
1821 cursor l_SerLines_csr
1822 is
1823 select
1824 plnt.header_id,
1825 plnt.line_id,
1826 plnt.quantity
1827 from pos_exasn_lines plnt
1828 where lls_code = 'SER';
1829 /*
1830 and (select count(distinct nvl(license_plate_number,'null'))
1831 from pos_exasn_serials pst
1832 where pst.line_id = plnt.line_id) >1;
1833 */
1834 l_ln_header_id number;
1835
1836 l_first_ser varchar2(1);
1837
1838 l_ln_quantity number;
1839 l_total_ser_qty number;
1840 l_ser_lpn varchar2(60);
1841 l_ser_lpn_sum number;
1842 l_new_line number;
1843 l_remain_qty number;
1844
1845 l_line_id number;
1846
1847 begin
1848 select max(line_id)+1 into l_new_line from pos_exasn_lines;
1849
1850 open l_SerLines_csr;
1851 loop
1852 fetch l_SerLines_csr into
1853 l_ln_header_id,
1854 l_line_id,
1855 l_ln_quantity;
1856 exit when l_SerLines_csr%NOTFOUND;
1857 l_first_Ser := 'T';
1858 open l_distinctLpn_ser_csr(l_ln_header_id, l_line_id);
1859 loop
1860 fetch l_distinctLpn_ser_csr into
1861 l_ser_lpn;
1862 exit when l_distinctLpn_ser_csr%NOTFOUND;
1863 if(l_first_ser = 'T') then
1864 l_first_ser := 'F';
1865 update pos_exasn_lines
1866 set license_plate_number = l_ser_lpn
1867 where line_id = l_line_id;
1868
1869 if(l_ser_lpn is null) then
1870 update pos_exasn_lines plnt
1871 set plnt.quantity =
1872 (select sum(quantity)
1873 from pos_exasn_serials pst
1874 where pst.header_id = plnt.header_id
1875 and pst.line_id = plnt.line_id
1876 and pst.license_plate_number is null)
1877 where plnt.line_id = l_line_id;
1878 else
1879 update pos_exasn_lines plnt
1880 set plnt.quantity =
1881 (select sum(quantity)
1882 from pos_exasn_serials pst
1883 where pst.header_id = plnt.header_id
1884 and pst.line_id = plnt.line_id
1885 and pst.license_plate_number = l_ser_lpn)
1886 where plnt.line_id = l_line_id;
1887 end if;
1888 else
1889 if(l_ser_lpn is null) then
1890 select sum(quantity)
1891 into l_ser_lpn_sum
1892 from pos_exasn_serials
1893 where header_id = l_ln_header_id
1894 and line_id = l_line_id
1895 and license_plate_number is null;
1896 else
1897 select sum(quantity)
1898 into l_ser_lpn_sum
1899 from pos_exasn_serials
1900 where header_id = l_ln_header_id
1901 and line_id = l_line_id
1902 and license_plate_number = l_ser_lpn;
1903 end if;
1904
1905 CreateNewLine(l_ser_lpn_sum, l_ser_lpn, l_new_line, l_line_id);
1906
1907 if(l_ser_lpn is null) then
1908 update pos_exasn_serials
1909 set line_id = l_new_line
1910 where line_id = l_line_id
1911 and license_plate_number is null;
1912 else
1913 update pos_exasn_serials
1914 set line_id = l_new_line
1915 where line_id = l_line_id
1916 and license_plate_number = l_ser_lpn;
1917 end if;
1918
1919 l_new_line := l_new_line + 1;
1920
1921 end if;
1922 end loop;
1923 close l_distinctLpn_ser_csr;
1924
1925 select sum(quantity)
1926 into l_total_ser_qty
1927 from pos_exasn_serials
1928 where header_id = l_ln_header_id
1929 and line_id >= l_line_id;
1930
1931 if(l_total_ser_qty < l_ln_quantity) then
1932 l_remain_qty := l_ln_quantity - l_total_ser_qty;
1933 --Create Extra Line for the remaining Quantity with NO Serial information
1934 CreateNewLine(l_remain_qty, null, l_new_line, l_line_id);
1935 l_new_line := l_new_line + 1;
1936 end if;
1937 end loop;
1938 close l_SerLines_csr;
1939 /*
1940 update pos_exasn_lines plnt
1941 set license_plate_number = (select distinct pst2.license_plate_number
1942 from pos_exasn_serials pst2
1943 where pst2.line_id = plnt.line_id
1944 and pst2.license_plate_number is not null)
1945 where lls_code = 'SER'
1946 and (select count(distinct pst.license_plate_number)
1947 from pos_exasn_serials pst
1948 where pst.line_id = plnt.line_id
1949 and pst.license_plate_number is not null) = 1;
1950 */
1951 end CreateRTI4Ser;
1952
1953
1954 procedure CreateRTI4Lpn
1955 is
1956 cursor l_distinctLpn_csr(x_header_id number, x_line_id number)
1957 is
1958 select
1959 distinct
1960 license_plate_number
1961 from pos_exasn_lpns
1962 where header_id = x_header_id
1963 and line_id = x_line_id
1964 and quantity is not null;
1965
1966 cursor l_LpnLines_ML_csr
1967 is
1968 select
1969 plnt.header_id,
1970 plnt.line_id,
1971 plnt.quantity
1972 from pos_exasn_lines plnt
1973 where lls_code = 'LPN';
1974 /*
1975 and (select count(distinct nvl(license_plate_number,'null'))
1976 from pos_exasn_lpns plpn
1977 where plpn.line_id = plnt.line_id
1978 and quantity is null) >1;
1979 */
1980 l_ln_header_id number;
1981
1982 l_first_lpn varchar2(1);
1983
1984 l_ln_quantity number;
1985 l_total_lpn_qty number;
1986 l_lpn varchar2(60);
1987 l_lpn_sum number;
1988 l_new_line number;
1989 l_remain_qty number;
1990
1991 l_line_id number;
1992
1993 begin
1994 select max(line_id)+1 into l_new_line from pos_exasn_lines;
1995
1996 open l_LpnLines_ML_csr;
1997 loop
1998 fetch l_LpnLines_ML_csr into
1999 l_ln_header_id,
2000 l_line_id,
2001 l_ln_quantity;
2002 exit when l_LpnLines_ML_csr%NOTFOUND;
2003 l_first_lpn := 'T';
2004 open l_distinctLpn_csr(l_ln_header_id, l_line_id);
2005 loop
2006 fetch l_distinctLpn_csr into
2007 l_lpn;
2008 exit when l_distinctLpn_csr%NOTFOUND;
2009 if(l_first_lpn = 'T') then
2010 l_first_lpn := 'F';
2011 update pos_exasn_lines
2012 set license_plate_number = l_lpn
2013 where line_id = l_line_id;
2014
2015 update pos_exasn_lines plnt
2016 set plnt.quantity = (select plpn.quantity
2017 from pos_exasn_lpns plpn
2018 where plpn.header_id = plnt.header_id
2019 and plpn.line_id = plnt.line_id
2020 and plpn.license_plate_number = l_lpn
2021 and plpn.quantity is not null)
2022 where plnt.line_id = l_line_id;
2023 else
2024
2025 --Get LPN quantity for the the new Line
2026 select plpn.quantity into l_lpn_sum
2027 from pos_exasn_lpns plpn
2028 where plpn.license_plate_number = l_lpn
2029 and plpn.header_id = l_ln_header_id
2030 and plpn.line_id = l_line_id
2031 and plpn.quantity is not null;
2032
2033 CreateNewLine(l_lpn_sum, l_lpn, l_new_line, l_line_id);
2034
2035 update pos_exasn_lpns
2036 set line_id = l_new_line
2037 where line_id = l_line_id
2038 and license_plate_number = l_lpn;
2039
2040
2041 l_new_line := l_new_line + 1;
2042
2043 end if;
2044 end loop;
2045 close l_distinctLpn_csr;
2046
2047 select sum(quantity)
2048 into l_total_lpn_qty
2049 from pos_exasn_lpns
2050 where header_id = l_ln_header_id
2051 and line_id >= l_line_id;
2052
2053 if(l_total_lpn_qty < l_ln_quantity) then
2054 l_remain_qty := l_ln_quantity - l_total_lpn_qty;
2055 --Create Extra Line for the remaining Quantity with NO Lpn information
2056 CreateNewLine(l_remain_qty, null, l_new_line, l_line_id);
2057 l_new_line := l_new_line + 1;
2058 end if;
2059 end loop;
2060 close l_LpnLines_ML_csr;
2061 /*
2062 update pos_exasn_lines plnt
2063 set license_plate_number = (select distinct plpn2.license_plate_number
2064 from pos_exasn_lpns plpn2
2065 where plpn2.line_id = plnt.line_id
2066 and plpn2.license_plate_number is not null)
2067 where lls_code = 'LPN'
2068 and (select count(distinct plpn.license_plate_number)
2069 from pos_exasn_lpns plpn
2070 where plpn.line_id = plnt.line_id
2071 and plpn.license_plate_number is not null) = 1;
2072 */
2073 end CreateRTI4Lpn;
2074
2075
2076 procedure CreateRTI4Lot
2077 is
2078 cursor l_distinctLpn_lot_csr(x_header_id number, x_line_id number)
2079 is
2080 select
2081 distinct
2082 license_plate_number
2083 from pos_exasn_lots
2084 where header_id = x_header_id
2085 and line_id = x_line_id;
2086
2087 cursor l_LotLines_ML_csr
2088 is
2089 select
2090 plnt.header_id,
2091 plnt.line_id,
2092 plnt.quantity
2093 from pos_exasn_lines plnt
2094 where lls_code in ('LOT','LAS');
2095 /*
2096 and (select count(distinct nvl(license_plate_number,'null'))
2097 from pos_exasn_lots plot
2098 where plot.line_id = plnt.line_id) >1;
2099 */
2100 l_ln_header_id number;
2101
2102 l_first_lot varchar2(1);
2103
2104 l_ln_quantity number;
2105 l_total_lot_qty number;
2106 l_lot_lpn varchar2(60);
2107 l_lot_lpn_sum number;
2108 l_new_line number;
2109 l_remain_qty number;
2110
2111 l_line_id number;
2112
2113 begin
2114 select max(line_id)+1 into l_new_line from pos_exasn_lines;
2115
2116 open l_LotLines_ML_csr;
2117 loop
2118 fetch l_LotLines_ML_csr into
2119 l_ln_header_id,
2120 l_line_id,
2121 l_ln_quantity;
2122 exit when l_LotLines_ML_csr%NOTFOUND;
2123 l_first_lot := 'T';
2124
2125 open l_distinctLpn_lot_csr(l_ln_header_id, l_line_id);
2126 loop
2127 fetch l_distinctLpn_lot_csr into
2128 l_lot_lpn;
2129 exit when l_distinctLpn_lot_csr%NOTFOUND;
2130
2131 if(l_first_lot = 'T') then
2132 l_first_lot := 'F';
2133
2134 update pos_exasn_lines
2135 set license_plate_number = l_lot_lpn
2136 where line_id = l_line_id;
2137
2138 if(l_lot_lpn is null) then
2139 update pos_exasn_lines plnt
2140 set plnt.quantity =
2141 (select sum(quantity)
2142 from pos_exasn_lots plot
2143 where plot.header_id = plnt.header_id
2144 and plot.line_id = plnt.line_id
2145 and plot.license_plate_number is null)
2146 where plnt.line_id = l_line_id;
2147 else
2148 update pos_exasn_lines plnt
2149 set plnt.quantity =
2150 (select sum(quantity)
2151 from pos_exasn_lots plot
2152 where plot.header_id = plnt.header_id
2153 and plot.line_id = plnt.line_id
2154 and plot.license_plate_number = l_lot_lpn)
2155 where plnt.line_id = l_line_id;
2156 end if;
2157 else
2158 if(l_lot_lpn is null) then
2159 select sum(quantity)
2160 into l_lot_lpn_sum
2161 from pos_exasn_lots
2162 where header_id = l_ln_header_id
2163 and line_id = l_line_id
2164 and license_plate_number is null;
2165 else
2166 select sum(quantity)
2167 into l_lot_lpn_sum
2168 from pos_exasn_lots
2169 where header_id = l_ln_header_id
2170 and line_id = l_line_id
2171 and license_plate_number = l_lot_lpn;
2172 end if;
2173
2174 CreateNewLine(l_lot_lpn_sum, l_lot_lpn, l_new_line, l_line_id);
2175
2176 if(l_lot_lpn is null) then
2177 update pos_exasn_lots
2178 set line_id = l_new_line
2179 where line_id = l_line_id
2180 and license_plate_number is null;
2181
2182 update pos_exasn_serials
2183 set line_id = l_new_line
2184 where lot_id in(select lot_id
2185 from pos_exasn_lots
2186 where line_id = l_new_line
2187 and license_plate_number is null);
2188
2189 else
2190 update pos_exasn_lots
2191 set line_id = l_new_line
2192 where line_id = l_line_id
2193 and license_plate_number = l_lot_lpn;
2194
2195 update pos_exasn_serials
2196 set line_id = l_new_line
2197 where lot_id in (select lot_id
2198 from pos_exasn_lots
2199 where line_id = l_new_line
2200 and license_plate_number = l_lot_lpn);
2201 end if;
2202
2203 l_new_line := l_new_line + 1;
2204
2205 end if;
2206 end loop;
2207 close l_distinctLpn_lot_csr;
2208
2209 select sum(quantity)
2210 into l_total_lot_qty
2211 from pos_exasn_lots
2212 where header_id = l_ln_header_id
2213 and line_id >= l_line_id;--All lines created at this moment, with line_id > l_line_id is for l_line_id
2214
2215 if(l_total_lot_qty < l_ln_quantity) then
2216
2217 l_remain_qty := l_ln_quantity - l_total_lot_qty;
2218 --Create Extra Line for the remaining Quantity with NO Lot information
2219
2220 CreateNewLine(l_remain_qty, null, l_new_line, l_line_id);
2221 l_new_line := l_new_line + 1;
2222 end if;
2223 end loop;
2224 close l_LotLines_ML_csr;
2225 /*
2226 update pos_exasn_lines plnt
2227 set license_plate_number = (select distinct plot2.license_plate_number
2228 from pos_exasn_lots plot2
2229 where plot2.line_id = plnt.line_id
2230 and plot2.license_plate_number is not null)
2231 where lls_code = 'LOT'
2232 and (select count(distinct plot.license_plate_number)
2233 from pos_exasn_lots plot
2234 where plot.line_id = plnt.line_id
2235 and plot.license_plate_number is not null) = 1;
2236 */
2237 end CreateRTI4Lot;
2238
2239 procedure InsertIntoLLS(x_return_status out nocopy varchar2,
2240 p_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
2241 p_error_pointer in out nocopy number)
2242 is
2243 cursor l_allLots_csr
2244 is
2245 select
2246 plot.lot_id,
2247 plot.transaction_interface_id,
2248 plot.lot_number,
2249 plot.quantity,
2250 plot.po_line_loc_id,
2251 plot.uom,
2252 plot.license_plate_number,
2253 plnt.lpn_group_id,
2254 plot.line_number,
2255 plot.product_transaction_id,
2256 plot.vendor_id,
2257 plot.grade_code,
2258 plot.origination_date,
2259 plot.date_code,
2260 plot.status_id,
2261 plot.change_date,
2262 plot.age,
2263 plot.retest_date,
2264 plot.maturity_date,
2265 plot.item_size,
2266 plot.color,
2267 plot.volume,
2268 plot.volume_uom,
2269 plot.place_of_origin,
2270 plot.best_by_date,
2271 plot.length,
2272 plot.length_uom,
2273 plot.recycled_content,
2274 plot.thickness,
2275 plot.thickness_uom,
2276 plot.width,
2277 plot.width_uom,
2278 plot.curl_wrinkle_fold,
2279 plot.supplier_lot_number,
2280 plot.territory_code,
2281 plot.vendor_name,
2282 plot.LOT_ATTRIBUTE_CATEGORY,
2283 plot.CATTRIBUTE1,
2284 plot.CATTRIBUTE2,
2285 plot.CATTRIBUTE3,
2286 plot.CATTRIBUTE4,
2287 plot.CATTRIBUTE5,
2288 plot.CATTRIBUTE6,
2289 plot.CATTRIBUTE7,
2290 plot.CATTRIBUTE8,
2291 plot.CATTRIBUTE9,
2292 plot.CATTRIBUTE10,
2293 plot.CATTRIBUTE11,
2294 plot.CATTRIBUTE12,
2295 plot.CATTRIBUTE13,
2296 plot.CATTRIBUTE14,
2297 plot.CATTRIBUTE15,
2298 plot.CATTRIBUTE16,
2299 plot.CATTRIBUTE17,
2300 plot.CATTRIBUTE18,
2301 plot.CATTRIBUTE19,
2302 plot.CATTRIBUTE20,
2303 plot.DATTRIBUTE1,
2304 plot.DATTRIBUTE2,
2305 plot.DATTRIBUTE3,
2306 plot.DATTRIBUTE4,
2307 plot.DATTRIBUTE5,
2308 plot.DATTRIBUTE6,
2309 plot.DATTRIBUTE7,
2310 plot.DATTRIBUTE8,
2311 plot.DATTRIBUTE9,
2312 plot.DATTRIBUTE10,
2313 plot.NATTRIBUTE1,
2314 plot.NATTRIBUTE2,
2315 plot.NATTRIBUTE3,
2316 plot.NATTRIBUTE4,
2317 plot.NATTRIBUTE5,
2318 plot.NATTRIBUTE6,
2319 plot.NATTRIBUTE7,
2320 plot.NATTRIBUTE8,
2321 plot.NATTRIBUTE9,
2322 plot.NATTRIBUTE10
2323 from pos_exasn_lots plot,
2324 pos_exasn_lines plnt
2325 where plot.line_id = plnt.line_id;
2326
2327 cursor l_allSerials_csr
2328 is
2329 select
2330 pst.from_serial,
2331 pst.to_serial,
2332 pst.po_line_loc_id,
2333 pst.transaction_interface_id,
2334 pst.license_plate_number,
2335 plnt.lpn_group_id,
2336 pst.line_number,
2337 pst.product_transaction_id,
2338 pst.origination_date,
2339 pst.status_id,
2340 pst.territory_code,
2341 pst.SERIAL_ATTRIBUTE_CATEGORY,
2342 pst.CATTRIBUTE1,
2343 pst.CATTRIBUTE2,
2344 pst.CATTRIBUTE3,
2345 pst.CATTRIBUTE4,
2346 pst.CATTRIBUTE5,
2347 pst.CATTRIBUTE6,
2348 pst.CATTRIBUTE7,
2349 pst.CATTRIBUTE8,
2350 pst.CATTRIBUTE9,
2351 pst.CATTRIBUTE10,
2352 pst.CATTRIBUTE11,
2353 pst.CATTRIBUTE12,
2354 pst.CATTRIBUTE13,
2355 pst.CATTRIBUTE14,
2356 pst.CATTRIBUTE15,
2357 pst.CATTRIBUTE16,
2358 pst.CATTRIBUTE17,
2359 pst.CATTRIBUTE18,
2360 pst.CATTRIBUTE19,
2361 pst.CATTRIBUTE20,
2362 pst.DATTRIBUTE1,
2363 pst.DATTRIBUTE2,
2364 pst.DATTRIBUTE3,
2365 pst.DATTRIBUTE4,
2366 pst.DATTRIBUTE5,
2367 pst.DATTRIBUTE6,
2368 pst.DATTRIBUTE7,
2369 pst.DATTRIBUTE8,
2370 pst.DATTRIBUTE9,
2371 pst.DATTRIBUTE10,
2372 pst.NATTRIBUTE1,
2373 pst.NATTRIBUTE2,
2374 pst.NATTRIBUTE3,
2375 pst.NATTRIBUTE4,
2376 pst.NATTRIBUTE5,
2377 pst.NATTRIBUTE6,
2378 pst.NATTRIBUTE7,
2379 pst.NATTRIBUTE8,
2380 pst.NATTRIBUTE9,
2381 pst.NATTRIBUTE10
2382 from pos_exasn_serials pst,
2383 pos_exasn_lines plnt
2384 where pst.line_id = plnt.line_id;
2385
2386
2387 cursor l_allLpns_csr
2388 is
2389 select
2390 plpn.license_plate_number,
2391 plpn.po_line_loc_id,
2392 plpn.parent_lpn,
2393 plnt.lpn_group_id,
2394 plpn.line_number
2395 from pos_exasn_lpns plpn,
2396 pos_exasn_lines plnt
2397 where plnt.line_id = plpn.line_id;
2398 l_txn_intf_id number;
2399 l_ser_intf_id number;
2400 l_lot_number pos_exasn_lots.lot_number%type;
2401 l_vendor_id pos_exasn_lots.vendor_id%type;
2402 l_grade_code pos_exasn_lots.grade_code%type;
2403 l_origination_date pos_exasn_lots.origination_date%type;
2404 l_date_code pos_exasn_lots.date_code%type;
2405 l_status_id pos_exasn_lots.status_id%type;
2406 l_change_date pos_exasn_lots.change_date%type;
2407 l_age pos_exasn_lots.age%type;
2408 l_retest_date pos_exasn_lots.retest_date%type;
2409 l_maturity_date pos_exasn_lots.maturity_date%type;
2410 l_item_size pos_exasn_lots.item_size%type;
2411 l_color pos_exasn_lots.color%type;
2412 l_volume pos_exasn_lots.volume%type;
2413 l_volume_uom pos_exasn_lots.volume_uom%type;
2414 l_place_of_origin pos_exasn_lots.place_of_origin%type;
2415 l_best_by_date pos_exasn_lots.best_by_date%type;
2416 l_length pos_exasn_lots.length%type;
2417 l_length_uom pos_exasn_lots.length_uom%type;
2418 l_recycled_content pos_exasn_lots.recycled_content%type;
2419 l_thickness pos_exasn_lots.thickness%type;
2420 l_thickness_uom pos_exasn_lots.thickness_uom%type;
2421 l_width pos_exasn_lots.width%type;
2422 l_width_uom pos_exasn_lots.width_uom%type;
2423 l_curl_wrinkle_fold pos_exasn_lots.curl_wrinkle_fold%type;
2424 l_supplier_lot_number pos_exasn_lots.supplier_lot_number%type;
2425 l_territory_code pos_exasn_lots.territory_code%type;
2426 l_vendor_name pos_exasn_lots.vendor_name%type;
2427 l_qty number;
2428 l_po_line_loc_id number;
2429 l_pdt_txn_id number;
2430 l_fm_serial pos_exasn_serials.from_serial%type;
2431 l_to_serial pos_exasn_serials.to_serial%type;
2432 l_ser_origination_date pos_exasn_serials.origination_date%type;
2433 l_ser_status_id pos_exasn_serials.status_id%type;
2434 l_ser_territory_code pos_exasn_serials.territory_code%type;
2435 l_lpn pos_exasn_lpns.license_plate_number%type;
2436 l_parent_lpn pos_exasn_lpns.parent_lpn%type;
2437 l_lpn_Group_id number;
2438
2439 l_lot_status varchar2(1);
2440 l_lot_msg_data varchar2(2000);
2441 l_lot_msg_count number;
2442
2443 l_ser_status varchar2(1);
2444 l_ser_msg_count number;
2445 l_ser_msg_data varchar2(2000);
2446
2447 l_lpn_status varchar2(1);
2448 l_lpn_msg_count number;
2449 l_lpn_msg_data varchar2(2000);
2450 l_serial_txn_temp_id number;
2451 l_txn_uom pos_exasn_lots.uom%type;
2452 l_lot_id number;
2453
2454 l_25errors exception;
2455 l_lot_ln number;
2456 l_lpn_ln number;
2457 l_ser_ln number;
2458 l_LOT_ATTRIBUTE_CATEGORY VARCHAR2(60);
2459 l_SERIAL_ATTRIBUTE_CATEGORY VARCHAR2(60);
2460 l_CATTRIBUTE1 VARCHAR2(2000);
2461 l_CATTRIBUTE2 VARCHAR2(2000);
2462 l_CATTRIBUTE3 VARCHAR2(2000);
2463 l_CATTRIBUTE4 VARCHAR2(2000);
2464 l_CATTRIBUTE5 VARCHAR2(2000);
2465 l_CATTRIBUTE6 VARCHAR2(2000);
2466 l_CATTRIBUTE7 VARCHAR2(2000);
2467 l_CATTRIBUTE8 VARCHAR2(2000);
2468 l_CATTRIBUTE9 VARCHAR2(2000);
2469 l_CATTRIBUTE10 VARCHAR2(2000);
2470 l_CATTRIBUTE11 VARCHAR2(2000);
2471 l_CATTRIBUTE12 VARCHAR2(2000);
2472 l_CATTRIBUTE13 VARCHAR2(2000);
2473 l_CATTRIBUTE14 VARCHAR2(2000);
2474 l_CATTRIBUTE15 VARCHAR2(2000);
2475 l_CATTRIBUTE16 VARCHAR2(2000);
2476 l_CATTRIBUTE17 VARCHAR2(2000);
2477 l_CATTRIBUTE18 VARCHAR2(2000);
2478 l_CATTRIBUTE19 VARCHAR2(2000);
2479 l_CATTRIBUTE20 VARCHAR2(2000);
2480 l_DATTRIBUTE1 DATE;
2481 l_DATTRIBUTE2 DATE;
2482 l_DATTRIBUTE3 DATE;
2483 l_DATTRIBUTE4 DATE;
2484 l_DATTRIBUTE5 DATE;
2485 l_DATTRIBUTE6 DATE;
2486 l_DATTRIBUTE7 DATE;
2487 l_DATTRIBUTE8 DATE;
2488 l_DATTRIBUTE9 DATE;
2489 l_DATTRIBUTE10 DATE;
2490 l_NATTRIBUTE1 NUMBER;
2491 l_NATTRIBUTE2 NUMBER;
2492 l_NATTRIBUTE3 NUMBER;
2493 l_NATTRIBUTE4 NUMBER;
2494 l_NATTRIBUTE5 NUMBER;
2495 l_NATTRIBUTE6 NUMBER;
2496 l_NATTRIBUTE7 NUMBER;
2497 l_NATTRIBUTE8 NUMBER;
2498 l_NATTRIBUTE9 NUMBER;
2499 l_NATTRIBUTE10 NUMBER;
2500 begin
2501
2502 open l_allLots_csr;
2503 loop
2504 fetch l_allLots_csr into
2505 l_lot_id,
2506 l_txn_intf_id,
2507 l_lot_number,
2508 l_qty,
2509 l_po_line_loc_id,
2510 l_txn_uom,
2511 l_lpn,
2512 l_lpn_group_id,
2513 l_lot_ln,
2514 l_pdt_txn_id,
2515 l_vendor_id,
2516 l_grade_code,
2517 l_origination_date,
2518 l_date_code,
2519 l_status_id,
2520 l_change_date,
2521 l_age,
2522 l_retest_date,
2523 l_maturity_date,
2524 l_item_size,
2525 l_color,
2526 l_volume,
2527 l_volume_uom,
2528 l_place_of_origin,
2529 l_best_by_date,
2530 l_length,
2531 l_length_uom,
2532 l_recycled_content,
2533 l_thickness,
2534 l_thickness_uom,
2535 l_width,
2536 l_width_uom,
2537 l_curl_wrinkle_fold,
2538 l_supplier_lot_number,
2539 l_territory_code,
2540 l_vendor_name,
2541 l_LOT_ATTRIBUTE_CATEGORY,
2542 l_CATTRIBUTE1,
2543 l_CATTRIBUTE2,
2544 l_CATTRIBUTE3,
2545 l_CATTRIBUTE4,
2546 l_CATTRIBUTE5,
2547 l_CATTRIBUTE6,
2548 l_CATTRIBUTE7,
2549 l_CATTRIBUTE8,
2550 l_CATTRIBUTE9,
2551 l_CATTRIBUTE10,
2552 l_CATTRIBUTE11,
2553 l_CATTRIBUTE12,
2554 l_CATTRIBUTE13,
2555 l_CATTRIBUTE14,
2556 l_CATTRIBUTE15,
2557 l_CATTRIBUTE16,
2558 l_CATTRIBUTE17,
2559 l_CATTRIBUTE18,
2560 l_CATTRIBUTE19,
2561 l_CATTRIBUTE20,
2562 l_DATTRIBUTE1,
2563 l_DATTRIBUTE2,
2564 l_DATTRIBUTE3,
2565 l_DATTRIBUTE4,
2566 l_DATTRIBUTE5,
2567 l_DATTRIBUTE6,
2568 l_DATTRIBUTE7,
2569 l_DATTRIBUTE8,
2570 l_DATTRIBUTE9,
2571 l_DATTRIBUTE10,
2572 l_NATTRIBUTE1,
2573 l_NATTRIBUTE2,
2574 l_NATTRIBUTE3,
2575 l_NATTRIBUTE4,
2576 l_NATTRIBUTE5,
2577 l_NATTRIBUTE6,
2578 l_NATTRIBUTE7,
2579 l_NATTRIBUTE8,
2580 l_NATTRIBUTE9,
2581 l_NATTRIBUTE10;
2582 exit when l_allLots_csr%NOTFOUND;
2583
2584 pos_asn_create_pvt.insert_mtli(
2585 p_api_version => 1.0
2586 , x_return_status => l_lot_status
2587 , x_msg_count => l_lot_msg_count
2588 , x_msg_data => l_lot_msg_data
2589 , p_transaction_interface_id => l_txn_intf_id
2590 , p_lot_number => l_lot_number
2591 , p_transaction_quantity => l_qty
2592 , p_transaction_uom => l_txn_uom
2593 , p_po_line_loc_id => l_po_line_loc_id
2594 , x_serial_transaction_temp_id => l_serial_txn_temp_id
2595 , p_product_transaction_id => l_pdt_txn_id
2596 , p_vendor_id => l_vendor_id
2597 , p_grade_Code => l_grade_code
2598 , p_origination_date => l_origination_date
2599 , p_date_code => l_date_code
2600 , p_status_id => l_status_id
2601 , p_change_date => l_change_date
2602 , p_age => l_age
2603 , p_retest_date => l_retest_date
2604 , p_maturity_date => l_maturity_date
2605 , p_item_size => l_item_size
2606 , p_color => l_color
2607 , p_volume => l_volume
2608 , p_volume_uom => l_volume_uom
2609 , p_place_of_origin => l_place_of_origin
2610 , p_best_by_date => l_best_by_date
2611 , p_length => l_length
2612 , p_length_uom => l_length_uom
2613 , p_recycled_content => l_recycled_content
2614 , p_thickness => l_thickness
2615 , p_thickness_uom => l_thickness_uom
2616 , p_width => l_width
2617 , p_width_uom => l_width_uom
2618 , p_curl_wrinkle_fold => l_curl_wrinkle_fold
2619 , p_supplier_lot_number => l_supplier_lot_number
2620 , p_territory_code => l_territory_code
2621 , p_vendor_name => l_vendor_name
2622 , p_lot_attribute_category => l_LOT_ATTRIBUTE_CATEGORY
2623 , p_c_attribute1 => l_CATTRIBUTE1
2624 , p_c_attribute2 => l_CATTRIBUTE2
2625 , p_c_attribute3 => l_CATTRIBUTE3
2626 , p_c_attribute4 => l_CATTRIBUTE4
2627 , p_c_attribute5 => l_CATTRIBUTE5
2628 , p_c_attribute6 => l_CATTRIBUTE6
2629 , p_c_attribute7 => l_CATTRIBUTE7
2630 , p_c_attribute8 => l_CATTRIBUTE8
2631 , p_c_attribute9 => l_CATTRIBUTE9
2632 , p_c_attribute10 => l_CATTRIBUTE10
2633 , p_c_attribute11 => l_CATTRIBUTE11
2634 , p_c_attribute12 => l_CATTRIBUTE12
2635 , p_c_attribute13 => l_CATTRIBUTE13
2636 , p_c_attribute14 => l_CATTRIBUTE14
2637 , p_c_attribute15 => l_CATTRIBUTE15
2638 , p_c_attribute16 => l_CATTRIBUTE16
2639 , p_c_attribute17 => l_CATTRIBUTE17
2640 , p_c_attribute18 => l_CATTRIBUTE18
2641 , p_c_attribute19 => l_CATTRIBUTE19
2642 , p_c_attribute20 => l_CATTRIBUTE20
2643 , p_d_attribute1 => l_DATTRIBUTE1
2644 , p_d_attribute2 => l_DATTRIBUTE2
2645 , p_d_attribute3 => l_DATTRIBUTE3
2646 , p_d_attribute4 => l_DATTRIBUTE4
2647 , p_d_attribute5 => l_DATTRIBUTE5
2648 , p_d_attribute6 => l_DATTRIBUTE6
2649 , p_d_attribute7 => l_DATTRIBUTE7
2650 , p_d_attribute8 => l_DATTRIBUTE8
2651 , p_d_attribute9 => l_DATTRIBUTE9
2652 , p_d_attribute10 => l_DATTRIBUTE10
2653 , p_n_attribute1 => l_NATTRIBUTE1
2654 , p_n_attribute2 => l_NATTRIBUTE2
2655 , p_n_attribute3 => l_NATTRIBUTE3
2656 , p_n_attribute4 => l_NATTRIBUTE4
2657 , p_n_attribute5 => l_NATTRIBUTE5
2658 , p_n_attribute6 => l_NATTRIBUTE6
2659 , p_n_attribute7 => l_NATTRIBUTE7
2660 , p_n_attribute8 => l_NATTRIBUTE8
2661 , p_n_attribute9 => l_NATTRIBUTE9
2662 , p_n_attribute10 => l_NATTRIBUTE10
2663 );
2664
2665 if(l_lot_status <> FND_API.G_RET_STS_SUCCESS) then
2666 if(InsertError(p_error_tbl, 'Error while inserting Lot at line '||l_lot_ln, p_error_pointer)=1) then
2667 raise l_25errors;
2668 end if;
2669 end if;
2670
2671
2672 update pos_exasn_lots
2673 set TRANSACTION_INTERFACE_ID = l_txn_intf_id, serial_transaction_temp_id = l_serial_txn_temp_id
2674 where lot_id = l_lot_id;
2675
2676 if(l_lpn is not null) then
2677 pos_asn_create_pvt.insert_wlpni
2678 ( p_api_version => 1.0
2679 , x_return_status => l_lpn_status
2680 , x_msg_count => l_lpn_msg_count
2681 , x_msg_data => l_lpn_msg_data
2682 , p_po_line_loc_id => l_po_line_loc_id
2683 , p_license_plate_number => l_lpn
2684 , p_LPN_GROUP_ID => l_lpn_group_id
2685 , p_PARENT_LICENSE_PLATE_NUMBER => null
2686 );
2687 if(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
2688 if(InsertError(p_error_tbl, 'Error while inserting LPN at line '||l_lot_ln, p_error_pointer)=1) then
2689 raise l_25errors;
2690 end if;
2691 end if;
2692 end if;
2693
2694
2695
2696 end loop;
2697 close l_allLots_csr;
2698
2699 --update
2700 update pos_exasn_serials pst
2701 set pst.transaction_interface_id =
2702 (select plot.serial_transaction_temp_id
2703 from pos_exasn_lots plot
2704 where plot.lot_id = pst.lot_id);
2705
2706
2707
2708 open l_allSerials_csr;
2709 loop
2710 fetch l_allSerials_csr into
2711 l_fm_serial,
2712 l_to_serial,
2713 l_po_line_loc_id,
2714 l_ser_intf_id,
2715 l_lpn,
2716 l_lpn_group_id,
2717 l_ser_ln,
2718 l_pdt_txn_id,
2719 l_ser_origination_date,
2720 l_ser_status_id,
2721 l_ser_territory_code,
2722 l_SERIAL_ATTRIBUTE_CATEGORY,
2723 l_CATTRIBUTE1,
2724 l_CATTRIBUTE2,
2725 l_CATTRIBUTE3,
2726 l_CATTRIBUTE4,
2727 l_CATTRIBUTE5,
2728 l_CATTRIBUTE6,
2729 l_CATTRIBUTE7,
2730 l_CATTRIBUTE8,
2731 l_CATTRIBUTE9,
2732 l_CATTRIBUTE10,
2733 l_CATTRIBUTE11,
2734 l_CATTRIBUTE12,
2735 l_CATTRIBUTE13,
2736 l_CATTRIBUTE14,
2737 l_CATTRIBUTE15,
2738 l_CATTRIBUTE16,
2739 l_CATTRIBUTE17,
2740 l_CATTRIBUTE18,
2741 l_CATTRIBUTE19,
2742 l_CATTRIBUTE20,
2743 l_DATTRIBUTE1,
2744 l_DATTRIBUTE2,
2745 l_DATTRIBUTE3,
2746 l_DATTRIBUTE4,
2747 l_DATTRIBUTE5,
2748 l_DATTRIBUTE6,
2749 l_DATTRIBUTE7,
2750 l_DATTRIBUTE8,
2751 l_DATTRIBUTE9,
2752 l_DATTRIBUTE10,
2753 l_NATTRIBUTE1,
2754 l_NATTRIBUTE2,
2755 l_NATTRIBUTE3,
2756 l_NATTRIBUTE4,
2757 l_NATTRIBUTE5,
2758 l_NATTRIBUTE6,
2759 l_NATTRIBUTE7,
2760 l_NATTRIBUTE8,
2761 l_NATTRIBUTE9,
2762 l_NATTRIBUTE10;
2763 exit when l_allSerials_csr%NOTFOUND;
2764
2765
2766 pos_asn_create_pvt.insert_msni (
2767 p_api_version => 1.0
2768 , x_return_status => l_ser_status
2769 , x_msg_count => l_ser_msg_count
2770 , x_msg_data => l_ser_msg_data
2771 , p_transaction_interface_id => l_ser_intf_id
2772 , p_fm_serial_number => l_fm_serial
2773 , p_to_serial_number => l_to_serial
2774 , p_po_line_loc_id => l_po_line_loc_id
2775 , p_product_transaction_id => l_pdt_txn_id
2776 , p_origination_date => l_ser_origination_date
2777 , p_status_id => l_ser_status_id
2778 , p_territory_code => l_ser_territory_code
2779 , p_serial_attribute_category => l_SERIAL_ATTRIBUTE_CATEGORY
2780 , p_c_attribute1 => l_CATTRIBUTE1
2781 , p_c_attribute2 => l_CATTRIBUTE2
2782 , p_c_attribute3 => l_CATTRIBUTE3
2783 , p_c_attribute4 => l_CATTRIBUTE4
2784 , p_c_attribute5 => l_CATTRIBUTE5
2785 , p_c_attribute6 => l_CATTRIBUTE6
2786 , p_c_attribute7 => l_CATTRIBUTE7
2787 , p_c_attribute8 => l_CATTRIBUTE8
2788 , p_c_attribute9 => l_CATTRIBUTE9
2789 , p_c_attribute10 => l_CATTRIBUTE10
2790 , p_c_attribute11 => l_CATTRIBUTE11
2791 , p_c_attribute12 => l_CATTRIBUTE12
2792 , p_c_attribute13 => l_CATTRIBUTE13
2793 , p_c_attribute14 => l_CATTRIBUTE14
2794 , p_c_attribute15 => l_CATTRIBUTE15
2795 , p_c_attribute16 => l_CATTRIBUTE16
2796 , p_c_attribute17 => l_CATTRIBUTE17
2797 , p_c_attribute18 => l_CATTRIBUTE18
2798 , p_c_attribute19 => l_CATTRIBUTE19
2799 , p_c_attribute20 => l_CATTRIBUTE20
2800 , p_d_attribute1 => l_DATTRIBUTE1
2801 , p_d_attribute2 => l_DATTRIBUTE2
2802 , p_d_attribute3 => l_DATTRIBUTE3
2803 , p_d_attribute4 => l_DATTRIBUTE4
2804 , p_d_attribute5 => l_DATTRIBUTE5
2805 , p_d_attribute6 => l_DATTRIBUTE6
2806 , p_d_attribute7 => l_DATTRIBUTE7
2807 , p_d_attribute8 => l_DATTRIBUTE8
2808 , p_d_attribute9 => l_DATTRIBUTE9
2809 , p_d_attribute10 => l_DATTRIBUTE10
2810 , p_n_attribute1 => l_NATTRIBUTE1
2811 , p_n_attribute2 => l_NATTRIBUTE2
2812 , p_n_attribute3 => l_NATTRIBUTE3
2813 , p_n_attribute4 => l_NATTRIBUTE4
2814 , p_n_attribute5 => l_NATTRIBUTE5
2815 , p_n_attribute6 => l_NATTRIBUTE6
2816 , p_n_attribute7 => l_NATTRIBUTE7
2817 , p_n_attribute8 => l_NATTRIBUTE8
2818 , p_n_attribute9 => l_NATTRIBUTE9
2819 , p_n_attribute10 => l_NATTRIBUTE10
2820 );
2821
2822
2823 if(l_ser_status <> FND_API.G_RET_STS_SUCCESS) then
2824 if(InsertError(p_error_tbl, 'Error while inserting Serial at line '||l_ser_ln, p_error_pointer)=1) then
2825 raise l_25errors;
2826 end if;
2827 end if;
2828
2829 if(l_lpn is not null) then
2830 pos_asn_create_pvt.insert_wlpni
2831 ( p_api_version => 1.0
2832 , x_return_status => l_lpn_status
2833 , x_msg_count => l_lpn_msg_count
2834 , x_msg_data => l_lpn_msg_data
2835 , p_po_line_loc_id => l_po_line_loc_id
2836 , p_license_plate_number => l_lpn
2837 , p_LPN_GROUP_ID => l_lpn_group_id
2838 , p_PARENT_LICENSE_PLATE_NUMBER => null
2839 );
2840 if(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
2841 if(InsertError(p_error_tbl, 'Error while inserting Lpn at line '||l_ser_ln, p_error_pointer)=1) then
2842 raise l_25errors;
2843 end if;
2844 end if;
2845 end if;
2846 end loop;
2847 close l_allSerials_csr;
2848
2849 open l_allLpns_csr;
2850 loop
2851 fetch l_allLpns_csr into
2852 l_lpn,
2853 l_po_line_loc_id,
2854 l_parent_lpn,
2855 l_lpn_group_id,
2856 l_lpn_ln;
2857 exit when l_allLpns_csr%NOTFOUND;
2858
2859 pos_asn_create_pvt.insert_wlpni
2860 ( p_api_version => 1.0
2861 , x_return_status => l_lpn_status
2862 , x_msg_count => l_lpn_msg_count
2863 , x_msg_data => l_lpn_msg_data
2864 , p_po_line_loc_id => l_po_line_loc_id
2865 , p_license_plate_number => l_lpn
2866 , p_LPN_GROUP_ID => l_lpn_group_id
2867 , p_PARENT_LICENSE_PLATE_NUMBER => l_parent_lpn
2868 );
2869
2870 if(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
2871 if(InsertError(p_error_tbl, 'Error while inserting Lpn at line '||l_lpn_ln, p_error_pointer)=1) then
2872 raise l_25errors;
2873 end if;
2874 end if;
2875
2876 end loop;
2877 close l_allLpns_csr;
2878
2879
2880 exception when l_25errors then
2881 null;
2882 when others then
2883 if(InsertError(p_error_tbl, 'Unexpected Error in InsertIntoLLS', p_error_pointer)=1) then
2884 null;
2885 end if;
2886 end InsertIntoLLS;
2887
2888
2889 function InsertError(p_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
2890 p_error_msg in varchar2,
2891 p_error_index in out NOCOPY number)
2892 return number
2893 is
2894 begin
2895 p_error_tbl.extend(1);
2896 p_error_tbl(p_error_index) := p_error_msg;
2897 p_error_index := p_error_index + 1;
2898
2899 if(p_error_index > 25) then
2900 return 1;
2901 else
2902 return 0;
2903 end if;
2904 end InsertError;
2905
2906
2907 procedure FixHeadersAndLines(x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
2908 l_error_pointer in out NOCOPY number)
2909 is
2910 l_asn_header_id number;
2911 l_ex_header_id number;
2912 l_ex_vendor_id number;
2913 l_ex_vendor_site_id number;
2914 l_ex_ship_to_org_id number;
2915 l_ex_first varchar2(1);
2916 l_ex_currency_code varchar2(15);
2917
2918
2919 --CREATE EXTRA RHIs
2920 cursor l_findExtraRhi_csr
2921 is
2922 select HEADER_ID from
2923 (
2924 select
2925 count(1), pht.header_id HEADER_ID, pht.shipment_number SHIPMENT_NUMBER, plnt.vendor_id VENDOR_ID, plnt.ship_to_org_id SHIP_TO_ORG_ID, nvl(plnt.vendor_site_id, -9999) VENDOR_SITE_ID, plnt.currency_code
2926 from pos_exasn_headers pht,
2927 pos_exasn_lines plnt
2928 where pht.header_id = plnt.header_id
2929 group by pht.header_id, pht.shipment_number, plnt.vendor_id, plnt.ship_to_org_id, nvl(plnt.vendor_site_id, -9999), plnt.currency_code
2930 )
2931 group by HEADER_ID
2932 having count(1) > 1;
2933
2934 cursor l_createExtraRhi_csr(x_header_id number) is
2935 select distinct plnt.vendor_id VENDOR_ID, plnt.ship_to_org_id SHIP_TO_ORG_ID, nvl(plnt.vendor_site_id, -9999) VENDOR_SITE_ID, plnt.currency_code CURRENCY_CODE
2936 from pos_exasn_lines plnt
2937 where header_id = x_header_Id;
2938
2939 l_asn_asbn varchar2(10);
2940 l_early_exit exception;
2941 l_asbn_bad varchar2(1);
2942 l_error_ln number;
2943 cursor l_checkNotPaySite_csr is
2944 select peh.line_number
2945 from pos_exasn_headers peh,
2946 po_vendor_sites_all pvsa
2947 where pvsa.vendor_site_id = peh.vendor_site_id
2948 AND getvendorpaysiteid(peh.vendor_id,nvl(peh.vendor_site_id, -9999),peh.currency_code) IS null;
2949 --Refer the bug7338353 for more details
2950
2951 --H1: Freight Carrier Code
2952 l_fcc pos_exasn_headers.freight_carrier_code%type;
2953 cursor l_checkFreightCC_csr
2954 is
2955 select pht.line_number, pht.freight_carrier_code
2956 from pos_exasn_headers pht
2957 where pht.freight_carrier_code is not null
2958 and 0=
2959 (select count(*) from org_freight oft
2960 where nvl(oft.disable_date, sysdate) >= sysdate
2961 and oft.freight_code = pht.freight_carrier_code
2962 and organization_id = pht.ship_to_org_id
2963 );
2964
2965 --H10: Bad Ship From Location
2966 l_ship_from_loc pos_exasn_headers.ship_from_location_code%type;
2967 cursor l_shipFrom_Loc_csr is
2968 select pht.line_number,pht.ship_from_location_code
2969 from pos_exasn_headers pht
2970 where pht.ship_from_location_code is not null
2971 and not exists (
2972 select 1 from hz_party_sites ps,hz_party_site_uses psu,po_vendors pov
2973 where ps.party_site_id = psu.party_site_id
2974 and psu.site_use_type = 'SUPPLIER_SHIP_FROM'
2975 and ps.party_id = pov.party_id
2976 and pov.vendor_id= pht.vendor_id
2977 and substr(ps.party_site_number,1,instr(ps.party_site_number,'|')-1) = pht.ship_from_location_code);
2978
2979
2980 begin
2981
2982 l_asbn_bad := 'F';
2983
2984 select max(header_id) into l_asn_header_id from pos_exasn_headers;
2985
2986 select decode(count(1),0,'ASN','ASBN')
2987 into l_asn_asbn
2988 from pos_exasn_headers
2989 where invoice_number is not null;
2990
2991
2992
2993
2994 open l_findExtraRhi_csr;
2995 loop
2996 fetch l_findExtraRhi_csr into
2997 l_ex_header_id;
2998 exit when l_findExtraRhi_csr%NOTFOUND;
2999 l_ex_first := 'T';
3000 open l_createExtraRhi_csr(l_ex_header_id);
3001 loop
3002 fetch l_createExtraRhi_csr into
3003 l_ex_vendor_id,
3004 l_ex_ship_to_org_id,
3005 l_ex_vendor_site_id,
3006 l_ex_currency_code;
3007 exit when l_createExtraRhi_csr%NOTFOUND;
3008 if(l_ex_vendor_site_id = -9999) then
3009 l_ex_vendor_site_id := null;
3010 end if;
3011 if(l_ex_first = 'T') then
3012 l_ex_first := 'F';
3013 else
3014 if(l_asn_asbn = 'ASBN') then
3015 l_asbn_bad := 'T';
3016 select line_number
3017 into l_error_ln
3018 from pos_exasn_headers
3019 where header_id = l_ex_header_id;
3020
3021 fnd_message.set_name('POS','POS_EXASN_ASBN_XHDR');
3022 fnd_message.set_token('LINE_NUM',l_error_ln);
3023 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3024 raise l_early_exit;
3025 end if;
3026 end if;
3027
3028 l_asn_header_id := l_asn_header_id + 1;
3029 CreateNewHeader(l_asn_header_id, l_ex_header_id, l_ex_vendor_id, l_ex_ship_to_org_id, l_ex_vendor_site_id );
3030
3031 update pos_exasn_lines
3032 set header_id = l_asn_header_id
3033 where header_id = l_ex_header_id
3034 and vendor_id = l_ex_vendor_id
3035 and ship_to_org_id = l_ex_ship_to_org_id
3036 and vendor_site_id = l_ex_vendor_site_id;
3037 end if;
3038 end loop;
3039 close l_createExtraRhi_csr;
3040 end loop;
3041 close l_findExtraRhi_csr;
3042
3043 if(l_asbn_bad = 'T') then
3044 raise l_early_exit;
3045 end if;
3046
3047 update pos_exasn_headers pht
3048 set (
3049 pht.vendor_id,
3050 pht.ship_to_org_id,
3051 pht.vendor_site_id,
3052 pht.currency_code,
3053 pht.rate,
3054 pht.rate_type,
3055 pht.rate_date
3056 )
3057 =
3058 (select
3059 plnt.vendor_id,
3060 plnt.ship_to_org_id,
3061 plnt.vendor_site_id,
3062 plnt.currency_code,
3063 plnt.rate,
3064 plnt.rate_type,
3065 plnt.rate_date
3066 from pos_exasn_lines plnt
3067 where plnt.header_id = pht.header_id
3068 and plnt.line_id =
3069 (select min(plnt2.line_id)
3070 from pos_exasn_lines plnt2
3071 where plnt2.header_id = pht.header_id)
3072 );
3073
3074 --H1 (Has to be done after PHT.SHIP_TO_ORG_ID is populated)
3075 open l_checkFreightCC_csr;
3076 loop
3077 fetch l_checkFreightCC_csr into l_error_ln, l_fcc;
3078 exit when l_checkFreightCC_csr%NOTFOUND;
3079 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
3080 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_FCCODE'));
3081 fnd_message.set_token('LOV_VALUE',l_fcc);
3082 fnd_message.set_token('LINE_NUM',l_error_ln);
3083 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3084 raise l_early_exit;
3085 end if;
3086 end loop;
3087 close l_checkFreightCC_csr;
3088
3089 --H10 ShipFromLocationCode Validation
3090 open l_shipFrom_Loc_csr;
3091 loop
3092 fetch l_shipFrom_Loc_csr into l_error_ln,l_ship_from_loc;
3093 exit when l_shipFrom_Loc_csr%NOTFOUND;
3094 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
3095 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_SHP_FROM'));
3096 fnd_message.set_token('LOV_VALUE',l_ship_from_loc);
3097 fnd_message.set_token('LINE_NUM',l_error_ln);
3098 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3099 raise l_early_exit;
3100 end if;
3101 end loop;
3102 close l_shipFrom_Loc_csr;
3103
3104
3105
3106 --Check to make sure vendor_site_id is pay site
3107 if(l_asn_asbn = 'ASBN') then
3108 open l_checkNotPaySite_csr;
3109 loop
3110 fetch l_checkNotPaySite_csr into l_error_ln;
3111 exit when l_checkNotPaySite_csr%notfound;
3112 fnd_message.set_name('POS','POS_EXASN_NOTPAYSITE');
3113 fnd_message.set_token('LINE_NUM',l_error_ln);
3114 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3115 raise l_early_exit;
3116 end if;
3117 end loop;
3118 close l_checkNotPaySite_csr;
3119 end if;
3120
3121
3122
3123 -- Update Payment Term ID, if any
3124 update pos_exasn_headers pht
3125 set payment_term_id =
3126 (select atv.term_id
3127 from ap_terms_val_v atv
3128 where atv.name = pht.payment_terms)
3129 where payment_terms is not null;
3130 -------------------
3131
3132
3133
3134
3135 update pos_exasn_lines plnt
3136 set (header_interface_id, group_id, expected_receipt_date) = (select pht.header_interface_id, pht.group_id, pht.expected_receipt_date
3137 from pos_exasn_headers pht
3138 where pht.header_id = plnt.header_id);
3139 exception when l_early_exit then
3140 null;
3141 end FixHeadersAndLines;
3142
3143 procedure CreateNewLine(p_qty in number, p_lpn in varchar2, p_line_id in number, p_old_ln in number)
3144 is
3145 l_intf_txn_id number;
3146 begin
3147 insert into pos_exasn_lines(line_id, quantity, license_plate_number) values(p_line_id, p_qty, p_lpn );
3148 select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_intf_txn_id from dual;
3149 update pos_exasn_lines
3150 set (
3151 PRIMARY_UOM,
3152 LPN_GROUP_ID,
3153 EXPECTED_RECEIPT_DATE,
3154 HEADER_INTERFACE_ID,
3155 INTERFACE_TRANSACTION_ID,
3156 GROUP_ID,
3157 HEADER_ID,
3158 OPERATING_UNIT,
3159 PO_NUMBER,
3160 PO_REVISION,
3161 PO_RELEASE_NUM,
3162 PO_LINE,
3163 PO_SHIPMENT,
3164 ORG_ID,
3165 PO_HEADER_ID,
3166 PO_RELEASE_ID,
3167 PO_LINE_ID,
3168 PO_LINE_LOCATION_ID,
3169 VENDOR_ID,
3170 VENDOR_SITE_ID,
3171 VENDOR_CONTACT_ID,
3172 SHIP_TO_ORG_ID,
3173 VENDOR_NAME,
3174 VENDOR_SITE_CODE,
3175 RATE_TYPE,
3176 RATE,
3177 RATE_DATE,
3178 ITEM_ID,
3179 ITEM_REVISION,
3180 UNIT_PRICE,
3181 CURRENCY_CODE,
3182 VENDOR_PRODUCT_NUM,
3183 UOM,
3184 BILL_OF_LADING,
3185 PACKING_SLIP,
3186 NUM_OF_CONTAINERS,
3187 WAYBILL_NUM,
3188 BARCODE_LABEL,
3189 COUNTRY_OF_ORIGIN,
3190 CONTAINER_NUMBER,
3191 TRUCK_NUMBER,
3192 VENDOR_LOT,
3193 COMMENTS,
3194 LINE_NUMBER,
3195 ATTRIBUTE_CATEGORY,
3196 ATTRIBUTE1,
3197 ATTRIBUTE2,
3198 ATTRIBUTE3,
3199 ATTRIBUTE4,
3200 ATTRIBUTE5,
3201 ATTRIBUTE6,
3202 ATTRIBUTE7,
3203 ATTRIBUTE8,
3204 ATTRIBUTE9,
3205 ATTRIBUTE10,
3206 ATTRIBUTE11,
3207 ATTRIBUTE12,
3208 ATTRIBUTE13,
3209 ATTRIBUTE14,
3210 ATTRIBUTE15,
3211 SH_ATTRIBUTE_CATEGORY,
3212 SH_ATTRIBUTE1,
3213 SH_ATTRIBUTE2,
3214 SH_ATTRIBUTE3,
3215 SH_ATTRIBUTE4,
3216 SH_ATTRIBUTE5,
3217 SH_ATTRIBUTE6,
3218 SH_ATTRIBUTE7,
3219 SH_ATTRIBUTE8,
3220 SH_ATTRIBUTE9,
3221 SH_ATTRIBUTE10,
3222 SH_ATTRIBUTE11,
3223 SH_ATTRIBUTE12,
3224 SH_ATTRIBUTE13,
3225 SH_ATTRIBUTE14,
3226 SH_ATTRIBUTE15,
3227 SL_ATTRIBUTE_CATEGORY,
3228 SL_ATTRIBUTE1,
3229 SL_ATTRIBUTE2,
3230 SL_ATTRIBUTE3,
3231 SL_ATTRIBUTE4,
3232 SL_ATTRIBUTE5,
3233 SL_ATTRIBUTE6,
3234 SL_ATTRIBUTE7,
3235 SL_ATTRIBUTE8,
3236 SL_ATTRIBUTE9,
3237 SL_ATTRIBUTE10,
3238 SL_ATTRIBUTE11,
3239 SL_ATTRIBUTE12,
3240 SL_ATTRIBUTE13,
3241 SL_ATTRIBUTE14,
3242 SL_ATTRIBUTE15,
3243 SHIP_TO_LOCATION_CODE,
3244 SHIP_TO_LOCATION_ID,
3245 LLS_CODE,
3246 ITEM_DESCRIPTION
3247 )
3248 = (
3249 select
3250 PRIMARY_UOM,
3251 LPN_GROUP_ID,
3252 EXPECTED_RECEIPT_DATE,
3253 HEADER_INTERFACE_ID,
3254 l_intf_txn_id,
3255 GROUP_ID,
3256 HEADER_ID,
3257 OPERATING_UNIT,
3258 PO_NUMBER,
3259 PO_REVISION,
3260 PO_RELEASE_NUM,
3261 PO_LINE,
3262 PO_SHIPMENT,
3263 ORG_ID,
3264 PO_HEADER_ID,
3265 PO_RELEASE_ID,
3266 PO_LINE_ID,
3267 PO_LINE_LOCATION_ID,
3268 VENDOR_ID,
3269 VENDOR_SITE_ID,
3270 VENDOR_CONTACT_ID,
3271 SHIP_TO_ORG_ID,
3272 VENDOR_NAME,
3273 VENDOR_SITE_CODE,
3274 RATE_TYPE,
3275 RATE,
3276 RATE_DATE,
3277 ITEM_ID,
3278 ITEM_REVISION,
3279 UNIT_PRICE,
3280 CURRENCY_CODE,
3281 VENDOR_PRODUCT_NUM,
3282 UOM,
3283 BILL_OF_LADING,
3284 PACKING_SLIP,
3285 NUM_OF_CONTAINERS,
3286 WAYBILL_NUM,
3287 BARCODE_LABEL,
3288 COUNTRY_OF_ORIGIN,
3289 CONTAINER_NUMBER,
3290 TRUCK_NUMBER,
3291 VENDOR_LOT,
3292 COMMENTS,
3293 LINE_NUMBER,
3294 ATTRIBUTE_CATEGORY,
3295 ATTRIBUTE1,
3296 ATTRIBUTE2,
3297 ATTRIBUTE3,
3298 ATTRIBUTE4,
3299 ATTRIBUTE5,
3300 ATTRIBUTE6,
3301 ATTRIBUTE7,
3302 ATTRIBUTE8,
3303 ATTRIBUTE9,
3304 ATTRIBUTE10,
3305 ATTRIBUTE11,
3306 ATTRIBUTE12,
3307 ATTRIBUTE13,
3308 ATTRIBUTE14,
3309 ATTRIBUTE15,
3310 SH_ATTRIBUTE_CATEGORY,
3311 SH_ATTRIBUTE1,
3312 SH_ATTRIBUTE2,
3313 SH_ATTRIBUTE3,
3314 SH_ATTRIBUTE4,
3315 SH_ATTRIBUTE5,
3316 SH_ATTRIBUTE6,
3317 SH_ATTRIBUTE7,
3318 SH_ATTRIBUTE8,
3319 SH_ATTRIBUTE9,
3320 SH_ATTRIBUTE10,
3321 SH_ATTRIBUTE11,
3322 SH_ATTRIBUTE12,
3323 SH_ATTRIBUTE13,
3324 SH_ATTRIBUTE14,
3325 SH_ATTRIBUTE15,
3326 SL_ATTRIBUTE_CATEGORY,
3327 SL_ATTRIBUTE1,
3328 SL_ATTRIBUTE2,
3329 SL_ATTRIBUTE3,
3330 SL_ATTRIBUTE4,
3331 SL_ATTRIBUTE5,
3332 SL_ATTRIBUTE6,
3333 SL_ATTRIBUTE7,
3334 SL_ATTRIBUTE8,
3335 SL_ATTRIBUTE9,
3336 SL_ATTRIBUTE10,
3337 SL_ATTRIBUTE11,
3338 SL_ATTRIBUTE12,
3339 SL_ATTRIBUTE13,
3340 SL_ATTRIBUTE14,
3341 SL_ATTRIBUTE15,
3342 SHIP_TO_LOCATION_CODE,
3343 SHIP_TO_LOCATION_ID,
3344 LLS_CODE,
3345 ITEM_DESCRIPTION
3346 from pos_exasn_lines
3347 where line_id = p_old_ln)
3348 where line_id = p_line_id;
3349 end CreateNewLine;
3350
3351 procedure CreateNewHeader(p_asn_header_id in number, p_ex_header_id in number,
3352 p_ex_vendor_id in number, p_ex_ship_to_org_id in number, p_ex_vendor_site_id in number)
3353 IS
3354 l_lpn_group_id number;
3355 l_header_interface_id number;
3356 BEGIN
3357 select rcv_interface_groups_s.nextval into l_lpn_group_id from dual;
3358 select rcv_headers_interface_s.nextval into l_header_interface_id from dual;
3359
3360 insert into pos_exasn_headers(
3361 header_id,
3362 lpn_group_id,
3363 vendor_id,
3364 ship_to_org_id,
3365 vendor_site_id,
3366 header_interface_id
3367 )
3368 values(
3369 p_asn_header_id,
3370 l_lpn_group_id,
3371 p_ex_vendor_id,
3372 p_ex_ship_to_org_id,
3373 p_ex_vendor_site_id,
3374 l_header_interface_id);
3375
3376 update pos_exasn_headers
3377 set (
3378 PAYMENT_TERM_ID,
3379 CURRENCY_CODE,
3380 RATE,
3381 RATE_TYPE,
3382 RATE_DATE,
3383 ASN_REQUEST_ID ,
3384 GROUP_ID,
3385 SHIPMENT_NUMBER,
3386 SHIPMENT_DATE,
3387 EXPECTED_RECEIPT_DATE,
3388 BILL_OF_LADING,
3389 PACKING_SLIP,
3390 FREIGHT_CARRIER_CODE,
3391 NUM_OF_CONTAINERS,
3392 WAYBILL_NUM,
3393 GROSS_WEIGHT_UOM,
3394 GROSS_WEIGHT,
3395 NET_WEIGHT_UOM,
3396 NET_WEIGHT,
3397 TAR_WEIGHT_UOM,
3398 TAR_WEIGHT,
3399 PACKAGING_CODE,
3400 CARRIER_METHOD,
3401 SPECIAL_HANDLING_CODE,
3402 HAZARD_CODE,
3403 HAZARD_CLASS,
3404 FREIGHT_TERMS,
3405 COMMENTS,
3406 INVOICE_NUMBER,
3407 INVOICE_DATE,
3408 INVOICE_AMOUNT ,
3409 TAX_AMOUNT,
3410 FREIGHT_AMOUNT ,
3411 PAYMENT_TERMS,
3412 LINE_NUMBER,
3413 ATTRIBUTE_CATEGORY,
3414 SHIP_FROM_LOCATION_CODE,
3415 ATTRIBUTE1,
3416 ATTRIBUTE2,
3417 ATTRIBUTE3,
3418 ATTRIBUTE4,
3419 ATTRIBUTE5,
3420 ATTRIBUTE6,
3421 ATTRIBUTE7,
3422 ATTRIBUTE8,
3423 ATTRIBUTE9,
3424 ATTRIBUTE10,
3425 ATTRIBUTE11,
3426 ATTRIBUTE12,
3427 ATTRIBUTE13,
3428 ATTRIBUTE14,
3429 ATTRIBUTE15
3430 ) = (select
3431 PAYMENT_TERM_ID,
3432 CURRENCY_CODE,
3433 RATE,
3434 RATE_TYPE,
3435 RATE_DATE,
3436 ASN_REQUEST_ID ,
3437 GROUP_ID,
3438 SHIPMENT_NUMBER,
3439 SHIPMENT_DATE,
3440 EXPECTED_RECEIPT_DATE,
3441 BILL_OF_LADING,
3442 PACKING_SLIP,
3443 FREIGHT_CARRIER_CODE,
3444 NUM_OF_CONTAINERS,
3445 WAYBILL_NUM,
3446 GROSS_WEIGHT_UOM,
3447 GROSS_WEIGHT,
3448 NET_WEIGHT_UOM,
3449 NET_WEIGHT,
3450 TAR_WEIGHT_UOM,
3451 TAR_WEIGHT,
3452 PACKAGING_CODE,
3453 CARRIER_METHOD,
3454 SPECIAL_HANDLING_CODE,
3455 HAZARD_CODE,
3456 HAZARD_CLASS,
3457 FREIGHT_TERMS,
3458 COMMENTS,
3459 INVOICE_NUMBER,
3460 INVOICE_DATE,
3461 INVOICE_AMOUNT ,
3462 TAX_AMOUNT,
3463 FREIGHT_AMOUNT ,
3464 PAYMENT_TERMS,
3465 LINE_NUMBER,
3466 ATTRIBUTE_CATEGORY,
3467 SHIP_FROM_LOCATION_CODE,
3468 ATTRIBUTE1,
3469 ATTRIBUTE2,
3470 ATTRIBUTE3,
3471 ATTRIBUTE4,
3472 ATTRIBUTE5,
3473 ATTRIBUTE6,
3474 ATTRIBUTE7,
3475 ATTRIBUTE8,
3476 ATTRIBUTE9,
3477 ATTRIBUTE10,
3478 ATTRIBUTE11,
3479 ATTRIBUTE12,
3480 ATTRIBUTE13,
3481 ATTRIBUTE14,
3482 ATTRIBUTE15
3483 from pos_exasn_headers
3484 where header_id = p_ex_header_id)
3485 where header_id = p_asn_header_id;
3486
3487
3488 --remember to have new lpn_group_id
3489 END;
3490
3491
3492 procedure CheckLlsControl(x_return_status out nocopy varchar2,
3493 x_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
3494 l_error_pointer in out nocopy number)
3495 IS
3496 l_25errors exception;
3497 l_error_ln number;
3498 cursor l_checkLotControl_csr is
3499 select distinct plnt.line_number
3500 from pos_exasn_lines plnt,
3501 pos_exasn_lots plot
3502 where plnt.lls_code not in ('LOT','LAS')
3503 and plnt.line_id = plot.line_id;
3504
3505 cursor l_checkSerialControl_csr is
3506 select distinct plnt.line_number
3507 from pos_exasn_lines plnt,
3508 pos_exasn_serials pst
3509 where plnt.lls_code not in ('SER','LAS')
3510 and plnt.line_id = pst.line_id;
3511
3512 --To Check that for all the child-parent lpn relationship, the child lpn exists
3513 --as either a parent in some other row, or as a lpn in the lot/serial/lpn row (with quantity specified)
3514 l_lpn pos_exasn_lpns.license_plate_number%type;
3515 cursor l_check_lpnref_csr
3516 is
3517 select
3518 LPN, LINE_NUM from
3519 (
3520 select
3521 a1.license_plate_number LPN,
3522 a1.line_number LINE_NUM
3523 from
3524 pos_exasn_lpns a1,
3525 pos_exasn_lines ln
3526 where a1.line_id = ln.line_id
3527 and ln.lls_code in ('LOT','LAS')
3528 and not exists
3529 ( select 1
3530 from pos_exasn_lots t
3531 where t.line_id = a1.line_id
3532 and t.license_plate_number = a1.license_plate_number)
3533 and not exists
3534 ( select 1
3535 from pos_exasn_lpns a2
3536 where a2.line_id = a1.line_id
3537 and a2.parent_lpn = a1.license_plate_number)
3538
3539 union all
3540
3541 select
3542 a1.license_plate_number LPN,
3543 a1.line_number LINE_NUM
3544 from
3545 pos_exasn_lpns a1,
3546 pos_exasn_lines ln
3547 where a1.line_id = ln.line_id
3548 and ln.lls_code = 'SER'
3549 and not exists
3550 ( select 1
3551 from pos_exasn_serials s
3552 where s.line_id = a1.line_id
3553 and s.license_plate_number = a1.license_plate_number)
3554 and not exists
3555 ( select 1
3556 from pos_exasn_lpns a2
3557 where a2.line_id = a1.line_id
3558 and a2.parent_lpn = a1.license_plate_number)
3559
3560 union all
3561
3562 -- if there is quantity defined, the LPN must not be defined as parent on some other line
3563 select
3564 a1.license_plate_number LPN,
3565 a1.line_number LINE_NUM
3566
3567 from
3568 pos_exasn_lpns a1,
3569 pos_exasn_lines ln
3570 where a1.line_id = ln.line_id
3571 and ln.lls_code = 'LPN'
3572 and a1.quantity is not null
3573 and exists ( select 1
3574 from pos_exasn_lpns p
3575 where p.line_id = a1.line_id
3576 and a1.quantity is not null
3577 and p.parent_lpn = a1.license_plate_number)
3578
3579 union all
3580
3581 --If there is no quantity defined, the LPN line must define child-parent relationship
3582 select
3583 a1.license_plate_number LPN,
3584 a1.line_number LINE_NUM
3585 from
3586 pos_exasn_lpns a1,
3587 pos_exasn_lines ln
3588 where a1.line_id = ln.line_id
3589 and ln.lls_code = 'LPN'
3590 and a1.quantity is null
3591 and not exists ( select 1
3592 from pos_exasn_lpns a2
3593 where a2.line_id = a1.line_id
3594 and a1.quantity is null
3595 and a2.parent_lpn = a1.license_plate_number)
3596
3597
3598 );
3599
3600 BEGIN
3601 update pos_exasn_lines plnt
3602 set plnt.lls_Code = 'LAS'
3603 where exists(
3604 select /*+ INDEX (msi, mtl_system_items_b_u1) */
3605 1 from mtl_system_items msi
3606 where msi.inventory_item_id = plnt.item_id
3607 and msi.organization_id = plnt.ship_to_org_id
3608 and msi.lot_control_code = 2
3609 and msi.serial_number_control_code in (2,5));
3610
3611 update pos_exasn_lines plnt
3612 set plnt.lls_Code = 'LOT'
3613 where plnt.lls_code is null
3614 and exists(
3615 select /*+ INDEX (msi, mtl_system_items_b_u1) */
3616 1 from mtl_system_items msi
3617 where msi.inventory_item_id = plnt.item_id
3618 and msi.organization_id = plnt.ship_to_org_id
3619 and msi.lot_control_code = 2
3620 and msi.serial_number_control_code not in (2,5));
3621
3622 update pos_exasn_lines plnt
3623 set plnt.lls_Code = 'SER'
3624 where plnt.lls_code is null
3625 and exists(
3626 select /*+ INDEX (msi, mtl_system_items_b_u1) */
3627 1 from mtl_system_items msi
3628 where msi.inventory_item_id = plnt.item_id
3629 and msi.organization_id = plnt.ship_to_org_id
3630 and msi.lot_control_code = 1
3631 and msi.serial_number_control_code in (2,5));
3632
3633 update pos_exasn_lines plnt
3634 set plnt.lls_Code = 'LPN'
3635 where plnt.lls_code is null;
3636
3637
3638
3639
3640 -- CHECK THAT LOT INFO ONLY FOR LOT CONTROLLED, etc...
3641 open l_checkLotControl_csr;
3642 loop
3643 fetch l_checkLotControl_csr into l_error_ln;
3644 exit when l_checkLotControl_csr%NOTFOUND;
3645 --Item at line LINE_NUM is not lot controlled, therefore shall not have any lot information.
3646 fnd_message.set_name('POS','POS_EXASN_NT_LC');
3647 fnd_message.set_token('LINE_NUM',l_error_ln);
3648 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3649 raise l_25errors;
3650 end if;
3651 end loop;
3652 close l_checkLotControl_csr;
3653
3654 open l_checkSerialControl_csr;
3655 loop
3656 fetch l_checkSerialControl_csr into l_error_ln;
3657 exit when l_checkSerialControl_csr%NOTFOUND;
3658 --Item at line LINE_NUM is not serial controlled, therefore shall not have any serial information.
3659 fnd_message.set_name('POS','POS_EXASN_NT_SC');
3660 fnd_message.set_token('LINE_NUM',l_error_ln);
3661 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3662 raise l_25errors;
3663 end if;
3664 end loop;
3665 close l_checkSerialControl_csr;
3666
3667 open l_check_lpnref_csr;
3668 loop
3669 fetch l_check_lpnref_csr into l_lpn, l_error_ln;
3670 exit when l_check_lpnref_csr%NOTFOUND;
3671 --The license plate number (LPN) defined in line LINE_NUM is invalid. The license plate number has to be defined in the Lot/Serial section or defined as a "Contained in License Plate Number".
3672 fnd_message.set_name('POS','POS_EXASN_LPN_NOREF');
3673 fnd_message.set_token('LINE_NUM',l_error_ln);
3674 fnd_message.set_token('LPN',l_lpn);
3675 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3676 raise l_25errors;
3677 end if;
3678 end loop;
3679 close l_check_lpnref_csr;
3680
3681
3682 x_return_status := 'S';
3683 exception when l_25errors then
3684 x_return_status := 'E';
3685 when others then
3686 x_return_status := 'U';
3687 END CheckLlsControl;
3688
3689
3690 procedure InsertIntoRTI
3691 is
3692 begin
3693
3694 /* Bug 10191272 ,Inserting Ship_To_Org_Id also */
3695 insert into rcv_transactions_interface
3696 ( INTERFACE_TRANSACTION_ID ,
3697 HEADER_INTERFACE_ID ,
3698 GROUP_ID ,
3699 TRANSACTION_TYPE ,
3700 TRANSACTION_DATE ,
3701 PROCESSING_STATUS_CODE ,
3702 PROCESSING_MODE_CODE ,
3703 TRANSACTION_STATUS_CODE ,
3704 AUTO_TRANSACT_CODE ,
3705 RECEIPT_SOURCE_CODE ,
3706 SOURCE_DOCUMENT_CODE ,
3707 PO_HEADER_ID ,
3708 PO_LINE_ID ,
3709 PO_LINE_LOCATION_ID ,
3710 QUANTITY ,
3711 PRIMARY_QUANTITY ,
3712 UNIT_OF_MEASURE ,
3713 PRIMARY_UNIT_OF_MEASURE ,
3714 LAST_UPDATE_DATE ,
3715 LAST_UPDATED_BY ,
3716 LAST_UPDATE_LOGIN ,
3717 CREATION_DATE ,
3718 CREATED_BY ,
3719 ITEM_ID ,
3720 ITEM_REVISION ,
3721 EXPECTED_RECEIPT_DATE ,
3722 COMMENTS ,
3723 BARCODE_LABEL ,
3724 CONTAINER_NUM ,
3725 COUNTRY_OF_ORIGIN_CODE ,
3726 VENDOR_ITEM_NUM ,
3727 VENDOR_LOT_NUM ,
3728 TRUCK_NUM ,
3729 NUM_OF_CONTAINERS ,
3730 PACKING_SLIP ,
3731 VALIDATION_FLAG ,
3732 WIP_ENTITY_ID ,
3733 WIP_LINE_ID ,
3734 WIP_OPERATION_SEQ_NUM ,
3735 PO_DISTRIBUTION_ID ,
3736 DOCUMENT_LINE_NUM ,
3737 DOCUMENT_SHIPMENT_LINE_NUM ,
3738 VENDOR_ID ,
3739 VENDOR_SITE_ID ,
3740 QUANTITY_INVOICED ,
3741 SHIP_TO_LOCATION_CODE ,
3742 SHIP_TO_LOCATION_ID ,
3743 PO_RELEASE_ID,
3744 license_plate_number,
3745 lpn_group_id,
3746 document_num,
3747 item_description,
3748 to_organization_id)
3749 select
3750 interface_transaction_id,
3751 header_interface_id,
3752 group_id,
3753 'SHIP',
3754 sysdate,
3755 'PENDING',
3756 'BATCH',
3757 'PENDING',
3758 'SHIP',
3759 'VENDOR',
3760 'PO',
3761 po_header_id,
3762 po_line_id,
3763 po_line_location_id,
3764 quantity,
3765 primary_quantity,
3766 uom,
3767 primary_uom,
3768 sysdate,
3769 fnd_global.user_id,
3770 fnd_global.user_id,
3771 sysdate,
3772 fnd_global.user_id,
3773 item_id,
3774 item_revision,
3775 expected_receipt_date,
3776 comments,
3777 barcode_label,
3778 container_number,
3779 country_of_origin,
3780 vendor_product_num,
3781 vendor_lot,
3782 truck_number,
3783 num_of_containers,
3784 packing_slip,
3785 'Y',
3786 null,--wip stuff ???
3787 null,--wip stuff
3788 null,--wip stuff
3789 null,--wip stuff
3790 po_line,
3791 po_shipment,
3792 vendor_id,
3793 vendor_site_id,
3794 null, -- invoiced amount???
3795 ship_to_location_code,
3796 ship_to_location_id,
3797 po_release_id,
3798 license_plate_number,
3799 lpn_group_id,
3800 po_number,
3801 item_description,
3802 SHIP_TO_ORG_ID
3803 from pos_exasn_lines;
3804
3805 end InsertIntoRTI;
3806
3807
3808
3809 procedure InsertIntoRHI
3810 is
3811 begin
3812 insert into rcv_headers_interface
3813 (HEADER_INTERFACE_ID ,
3814 GROUP_ID ,
3815 PROCESSING_STATUS_CODE ,
3816 RECEIPT_SOURCE_CODE ,
3817 TRANSACTION_TYPE ,
3818 LAST_UPDATE_DATE ,
3819 LAST_UPDATED_BY ,
3820 LAST_UPDATE_LOGIN ,
3821 CREATION_DATE ,
3822 CREATED_BY ,
3823 SHIP_TO_ORGANIZATION_ID ,
3824 VENDOR_ID ,
3825 VENDOR_SITE_ID ,
3826 SHIPPED_DATE ,
3827 ASN_TYPE ,
3828 SHIPMENT_NUM ,
3829 EXPECTED_RECEIPT_DATE ,
3830 PACKING_SLIP ,
3831 WAYBILL_AIRBILL_NUM ,
3832 BILL_OF_LADING ,
3833 FREIGHT_CARRIER_CODE ,
3834 FREIGHT_TERMS ,
3835 NUM_OF_CONTAINERS ,
3836 COMMENTS ,
3837 CARRIER_METHOD ,
3838 CARRIER_EQUIPMENT ,
3839 PACKAGING_CODE ,
3840 SPECIAL_HANDLING_CODE ,
3841 INVOICE_NUM ,
3842 INVOICE_DATE ,
3843 TOTAL_INVOICE_AMOUNT ,
3844 FREIGHT_AMOUNT ,
3845 TAX_NAME ,
3846 TAX_AMOUNT ,
3847 CURRENCY_CODE ,
3848 CONVERSION_RATE_TYPE ,
3849 CONVERSION_RATE ,
3850 CONVERSION_RATE_DATE ,
3851 PAYMENT_TERMS_ID ,
3852 PAYMENT_TERMS_NAME ,
3853 VALIDATION_FLAG,
3854 GROSS_WEIGHT_UOM_CODE,
3855 GROSS_WEIGHT,
3856 TAR_WEIGHT_UOM_CODE,
3857 TAR_WEIGHT,
3858 NET_WEIGHT_UOM_CODE,
3859 NET_WEIGHT,
3860 REMIT_TO_SITE_ID,
3861 SHIP_FROM_LOCATION_CODE
3862 )
3863
3864 select
3865 header_interface_id,
3866 group_id,
3867 'PENDING',
3868 'VENDOR',
3869 'NEW',
3870 sysdate,
3871 fnd_global.user_id,
3872 fnd_global.user_id,
3873 sysdate,
3874 fnd_global.user_id,
3875 ship_to_org_id,
3876 vendor_id,
3877 vendor_site_id,
3878 shipment_date,
3879 decode(INVOICE_NUMBER,null,'ASN','ASBN'),
3880 shipment_number,
3881 expected_receipt_date,
3882 packing_slip,
3883 waybill_num,
3884 bill_of_lading,
3885 freight_carrier_code,
3886 freight_terms,
3887 num_of_containers,
3888 comments,
3889 carrier_method,
3890 null,
3891 packaging_code,
3892 special_handling_code,
3893 invoice_number,
3894 invoice_date,
3895 invoice_amount,
3896 freight_amount,
3897 null,
3898 tax_amount,
3899 currency_code,
3900 rate_type,
3901 rate,
3902 rate_date,
3903 payment_term_id,
3904 payment_terms,
3905 'Y',
3906 gross_weight_uom,
3907 gross_weight,
3908 tar_weight_uom,
3909 tar_weight,
3910 net_weight_uom,
3911 net_weight,
3912 decode(invoice_number,null,null,getvendorpaysiteid(vendor_id,nvl(vendor_site_id,-9999),currency_code)),
3913 ship_from_location_code
3914 from pos_exasn_headers;
3915
3916
3917
3918 end InsertIntoRHI;
3919 procedure CheckSecuringAtt(
3920 x_return_status out nocopy varchar2,
3921 x_user_vendor_id_tbl out nocopy vendor_id_tbl_type,
3922 x_secure_by_site out nocopy varchar2,
3923 x_secure_by_contact out nocopy varchar2,
3924 x_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
3925 x_error_pointer in out nocopy number
3926 )
3927 IS
3928 l_num number;
3929 l_user_vendor_id number;
3930 l_index number;
3931
3932
3933 cursor l_vendorId_csr is
3934 SELECT NUMBER_VALUE
3935 FROM AK_WEB_USER_SEC_ATTR_VALUES
3936 WHERE WEB_USER_ID = FND_GLOBAL.USER_ID
3937 AND ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
3938 AND ATTRIBUTE_APPLICATION_ID = 177;
3939
3940
3941 cursor l_orgSecure_csr(p_resp_id number) is
3942 select 1
3943 from ak_resp_security_attributes arsa
3944 WHERE arsa.responsibility_id = p_resp_id
3945 AND arsa.attribute_application_id = 177
3946 and arsa.attribute_code = 'ICX_SUPPLIER_ORG_ID';
3947
3948 cursor l_siteSecure_csr(p_resp_id number) is
3949 select 1
3950 from ak_resp_security_attributes arsa
3951 WHERE arsa.responsibility_id = p_resp_id
3952 AND arsa.attribute_application_id = 177
3953 and arsa.attribute_code = 'ICX_SUPPLIER_SITE_ID';
3954
3955 cursor l_contactSecure_csr(p_resp_id number) is
3956 select 1
3957 from ak_resp_security_attributes arsa
3958 WHERE arsa.responsibility_id = p_resp_id
3959 AND arsa.attribute_application_id = 177
3960 and arsa.attribute_code = 'ICX_SUPPLIER_CONTACT_ID';
3961
3962
3963 cursor l_vendorSite_csr(p_user_id number) is
3964 SELECT number_value
3965 FROM ak_web_user_sec_attr_values
3966 WHERE web_user_id = p_user_id
3967 AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
3968 AND attribute_application_id = 177;
3969
3970 cursor l_vendorContact_csr(p_user_id number) is
3971 SELECT number_value
3972 FROM ak_web_user_sec_attr_values
3973 WHERE web_user_id = p_user_id
3974 AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
3975 AND attribute_application_id = 177;
3976
3977 BEGIN
3978 x_secure_by_site := 'F';
3979 x_secure_by_contact := 'F';
3980 open l_orgSecure_csr(fnd_global.resp_id);
3981 fetch l_orgSecure_csr into l_num;
3982
3983 l_num := null;
3984 open l_siteSecure_csr(fnd_global.resp_id);
3985 fetch l_siteSecure_csr into l_num;
3986 if(l_num = 1) then
3987 x_secure_by_site := 'T';
3988 end if;
3989
3990 l_num := null;
3991 open l_contactSecure_csr(fnd_global.resp_id);
3992 fetch l_contactSecure_csr into l_num;
3993 if(l_num = 1) then
3994 x_secure_by_contact := 'T';
3995 end if;
3996
3997
3998 --The following code segment retrieves and records all secured attributes
3999 --for org id, ICX_SUPPLIER_ORG_ID. The recorded set of attributes will be
4000 --used in ValidateLines to check against the vendor ids of all the lines.
4001 BEGIN
4002
4003 l_index := 1;
4004
4005 OPEN l_vendorId_csr;
4006 LOOP
4007 FETCH l_vendorId_csr INTO l_user_vendor_id;
4008 EXIT WHEN l_vendorId_csr%NOTFOUND;
4009
4010 x_user_vendor_id_tbl(l_index) := l_user_vendor_id;
4011 l_index := l_index+1;
4012
4013 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4014 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
4015 MODULE => 'pos.plsql.pos_asn_create_pvt.CheckSecuringAtt',
4016 MESSAGE => 'Retrieved Vendor Id ' || l_index || ':' ||
4017 l_user_vendor_id);
4018 END IF;
4019
4020 END LOOP;
4021
4022 EXCEPTION
4023 WHEN NO_DATA_FOUND THEN
4024 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4025 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
4026 MODULE => 'pos.plsql.pos_asn_create_pvt.CheckSecuringAtt',
4027 MESSAGE => 'No data found for cursor: l_vendorId_csr');
4028 END IF;
4029
4030 fnd_message.set_name('POS','POS_EXASN_NOT_ORG_SEC');
4031 IF(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) THEN
4032 null;
4033 END IF;
4034 x_return_status := 'E';
4035 END;
4036
4037
4038 --Check Securing Attributes by Site
4039 if(x_secure_by_site = 'T') then
4040 l_num := null;
4041 begin
4042 --Find out if -9999 is defined for Site for user's responsibility
4043 SELECT number_value
4044 into l_num
4045 FROM AK_RESP_SECURITY_ATTR_VALUES
4046 WHERE responsibility_id = fnd_global.resp_id
4047 AND attribute_application_id = 177
4048 AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
4049 AND number_value = -9999;
4050
4051 l_num := null;
4052 --IF -9999 is defined, and user is not secured by Vendor_Site_id, then it is the same as not be secured by site
4053 open l_vendorSite_csr(fnd_global.user_id);
4054 fetch l_vendorSite_csr into l_num;
4055 close l_vendorSite_csr;
4056 if(l_num is null) then
4057 x_secure_by_site := 'F';
4058 end if;
4059
4060 exception when others then
4061 --If -9999 is NOT defined, then user has to be secured by Site
4062 l_num := null;
4063 open l_vendorSite_csr(fnd_global.user_id);
4064 fetch l_vendorSite_csr into l_num;
4065 close l_vendorSite_csr;
4066 if(l_num is null) then
4067 fnd_message.set_name('POS','POS_EXASN_NOT_SITE_SEC');
4068 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer) = 1) then
4069 null;
4070 end if;
4071 x_return_status := 'E';
4072 end if;
4073 end;
4074 end if;
4075
4076 --Check Securing Attributes by Contact
4077 if(x_secure_by_contact = 'T') then
4078 l_num := null;
4079 begin
4080 --Find out if -9999 is defined for Site for user's responsibility
4081 SELECT number_value
4082 into l_num
4083 FROM AK_RESP_SECURITY_ATTR_VALUES
4084 WHERE responsibility_id = fnd_global.resp_id
4085 AND attribute_application_id = 177
4086 AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
4087 AND number_value = -9999;
4088
4089 l_num := null;
4090 --IF -9999 is defined, and user is not secured by Contact, then it is the same as not be secured by contact
4091 open l_vendorContact_csr(fnd_global.user_id);
4092 fetch l_vendorContact_csr into l_num;
4093 close l_vendorContact_csr;
4094 if(l_num is null) then
4095 x_secure_by_contact := 'F';
4096 end if;
4097
4098 exception when others then
4099 --If -9999 is NOT defined, then user has to be secured by Contact
4100 open l_vendorContact_csr(fnd_global.user_id);
4101 fetch l_vendorContact_csr into l_num;
4102 close l_vendorContact_csr;
4103 if(l_num is null) then
4104 fnd_message.set_name('POS','POS_EXASN_NOT_CT_SEC');
4105 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer) = 1) then
4106 null;
4107 end if;
4108 x_return_status := 'E';
4109
4110 end if;
4111 end;
4112 end if;
4113 exception when others then
4114 x_return_status := 'E';
4115 if(InsertError(x_error_tbl, 'Unknown exception when checking Securing Attributes:'||sqlerrm, x_error_pointer) = 1) then
4116 null;
4117 end if;
4118 END CheckSecuringAtt;
4119
4120
4121
4122
4123 function getConvertedQuantity(p_line_location_id in number,
4124 p_quantity in number,
4125 p_uom in varchar2
4126 ) return number
4127 IS
4128 l_converted_qty number;
4129 BEGIN
4130 POS_CREATE_ASN.getConvertedQuantity ( p_line_location_id ,
4131 p_quantity ,
4132 p_uom ,
4133 l_converted_qty );
4134 return l_converted_qty;
4135 exception when others then
4136 return -1;
4137 END;
4138
4139
4140 procedure ProcessExcelAsn(p_api_version in number,
4141 x_return_status out nocopy varchar2,
4142 x_return_code out nocopy varchar2,
4143 x_return_msg out nocopy varchar2,
4144 x_error_tbl out NOCOPY POS_EXCELASN_ERROR_TABLE,
4145 x_user_vendor_id out nocopy number)
4146 is
4147 x_progress varchar2(10);
4148 l_header_status varchar2(1);
4149 l_line_status varchar2(1);
4150 l_lls_status varchar2(1);
4151 l_llsControl_status varchar2(1);
4152 l_user_name varchar2(100);
4153
4154
4155
4156 -- MISC
4157
4158
4159 l_invalOrg_ln number;
4160 l_25errors exception;
4161 l_early_exp exception;
4162
4163 l_error_pointer number := 1;
4164 l_error_ln number;
4165
4166 --Securing Attributes
4167 l_secure_by_site varchar2(1);
4168 l_secure_by_contact varchar2(1);
4169 l_secure_status varchar2(1);
4170
4171 l_insertlls_status varchar2(1);
4172 l_error_ln2 number;
4173
4174 cursor l_checkLpnContra_csr
4175 is
4176 select plpn1.line_number, plpn2.line_number
4177 from pos_exasn_lpns plpn1, pos_exasn_lpns plpn2,
4178 pos_exasn_lines plnt1, pos_exasn_lines plnt2
4179 where plpn1.license_plate_number = plpn2.license_plate_number
4180 and plpn1.parent_lpn <> plpn2.parent_lpn
4181 and plnt1.line_id = plpn1.line_id
4182 and plnt2.line_id = plpn2.line_id
4183 and plnt1.org_id = plnt2.org_id ;
4184
4185 cursor l_checkDocAsn_csr
4186 is
4187 select SHIPMENT_NUMBER
4188 from pos_Exasn_headers
4189 group by SHIPMENT_NUMBER, nvl(VENDOR_ID,-9999), nvl(VENDOR_SITE_ID,-9999)
4190 having count(1) > 1;
4191
4192
4193 cursor l_buyerNotif_csr
4194 is
4195 select SHIPMENT_NUMBER, VENDOR_ID, VENDOR_SITE_ID
4196 from pos_exasn_headers;
4197
4198 l_shipment_number pos_exasn_headers.shipment_number%type;
4199 l_vendor_id pos_exasn_headers.vendor_id%type;
4200 l_vendor_site_id pos_exasn_headers.vendor_site_id%type;
4201 l_user_vendor_id_tbl vendor_id_tbl_type;
4202
4203 /* Inbound Logistics : Validate Ship From Location Code */
4204 Cursor l_shipFrom is
4205 Select /*+ USE_NL(pht,plt,ps) LEADING(pht) */
4206 pht.header_id,
4207 plt.po_line_id,
4208 plt.po_line_location_id,
4209 pht.ship_from_location_code,
4210 ps.location_id as ship_from_location_id
4211 from pos_exasn_headers pht,
4212 pos_exasn_lines plt,
4213 hz_party_sites ps
4214 where pht.header_id = plt.header_id
4215 and pht.ship_from_location_code is not null
4216 and ps.party_site_number = pht.ship_from_location_code||'|'||pht.vendor_id
4217 order by pht.header_id;
4218
4219 l_lineIdTbl po_tbl_number := po_tbl_number();
4220 l_lineLocIdTbl po_tbl_number := po_tbl_number();
4221 l_count NUMBER := 0;
4222 l_return_status VARCHAR2(2000);
4223 l_prev_header_id pos_exasn_headers.header_id%type := -1;
4224 l_prev_ship_from VARCHAR2(30) := '';
4225 l_header_id pos_exasn_headers.header_id%type;
4226 l_line_id pos_exasn_lines.po_line_id%type;
4227 l_line_location_id pos_exasn_lines.po_line_location_id%type;
4228 l_ship_from_location_code pos_exasn_headers.ship_from_location_code%type;
4229 --l_ship_from_location_id hz_party_sites.location_id%type;
4230 l_ship_from_location_id number;
4231 l_err_tbl po_tbl_varchar2000 ;
4232
4233
4234 begin
4235
4236 x_progress := '000';
4237 x_return_status := FND_API.G_RET_STS_SUCCESS;
4238 x_error_tbl := POS_EXCELASN_ERROR_TABLE();
4239
4240 --x_user_vendor_id will no longer be retrieved from the secured attribute,
4241 --ICX_SUPPLIER_ORG_ID
4242 SELECT FND_GLOBAL.USER_NAME INTO l_user_name FROM DUAL;
4243 x_user_vendor_id :=POS_VENDOR_UTIL_PKG.GET_PO_VENDOR_ID_FOR_USER(l_user_name);
4244
4245 -- Check Securing Attributes
4246
4247 CheckSecuringAtt(
4248 l_secure_status,
4249 l_user_vendor_id_tbl,
4250 l_secure_by_site,
4251 l_secure_by_contact,
4252 x_error_tbl,
4253 l_error_pointer);
4254 if(l_secure_status = 'E') then
4255 raise l_early_exp;
4256 end if;
4257
4258
4259
4260 x_progress := '001';
4261 --Validate Headers
4262 ValidateHeaders(l_header_status, x_error_tbl, l_error_pointer);
4263 if(l_header_status <> 'S') then
4264 raise l_early_exp;
4265 end if;
4266
4267
4268 x_progress := '002';
4269 --Validate Lines
4270 ValidateLines(l_line_status, l_user_vendor_id_tbl, l_secure_by_site, l_secure_by_contact, x_error_tbl, l_error_pointer);
4271 if(l_line_status <> 'S') then
4272 raise l_early_exp;
4273 end if;
4274
4275 x_progress := '003';
4276 FixHeadersAndLines(x_error_tbl,l_error_pointer);
4277 if(x_error_tbl.count > 0) then
4278 raise l_early_exp;
4279 end if;
4280
4281 --Check for Duplicate Shipment Number in the Upload Document
4282 --Invalid if same shipment number for > 2 ASNs with the same vendor_id and vendor_site_id
4283 --This validations has to be done after FixHeadersAndLines, which will populate vendor_id and vendor_site_id at header level
4284 open l_checkDocAsn_csr;
4285 loop
4286 fetch l_checkDocAsn_csr into l_shipment_number;
4287 exit when l_checkDocAsn_csr%NOTFOUND;
4288 fnd_message.set_name('POS','POS_EXASN_NEW_DUPE_SHIP');
4289 --Shipment number SHIP_NUM is used more than once for shipments with the same vendor, vendor site.
4290 fnd_message.set_token('SHIP_NUM',l_shipment_number);
4291 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
4292 raise l_25errors;
4293 end if;
4294 end loop;
4295 close l_checkDocAsn_csr;
4296
4297 /*Inbound Logistics Project : Ship From Location Validation */
4298 open l_shipFrom;
4299 loop
4300 fetch l_shipFrom into l_header_id,l_line_id,l_line_location_id,
4301 l_ship_from_location_code, l_ship_from_location_id;
4302 exit when l_shipFrom%NOTFOUND;
4303
4304 if (l_prev_header_id <> -1 AND l_prev_header_id <> l_header_id) THEN
4305 -- the first time they are not equal, we dont need to do validations
4306
4307 POS_ASN_CREATE_PVT.validate_ship_from
4308 (p_api_version_number => 1.0,
4309 p_init_msg_list => 'T',
4310 x_return_status => l_return_status,
4311 p_ship_from_locationid=> l_ship_from_location_id,
4312 p_po_line_id_tbl => l_lineIdTbl,
4313 p_po_line_loc_id_tbl => l_lineLocIdTbl,
4314 x_out_invalid_tbl => l_err_tbl
4315 );
4316 if (l_err_tbl.count() > 0) then
4317 if(InsertError(x_error_tbl,l_err_tbl(l_error_pointer), l_error_pointer)=1) then
4318 raise l_25errors;
4319 end if;
4320
4321 else
4322 -- empty the linelocid table and shiplocid table and prepare for new validation
4323 l_LineIdTbl := po_tbl_number();
4324 l_LineLocIdTbl := po_tbl_number();
4325 end if;
4326 end if;
4327 l_lineIdTbl.EXTEND;
4328 l_lineLocIdTbl.EXTEND;
4329 l_count := l_count + 1;
4330 l_lineIdTbl(l_count) := l_line_id;
4331 l_lineLocIdTbl(l_count) := l_line_location_id;
4332 l_prev_header_id := l_header_id;
4333 l_prev_ship_From := l_ship_from_location_code;
4334 end loop;
4335 close l_shipFrom;
4336
4337 x_progress := '004';
4338 CheckLlsControl(l_llsControl_status, x_error_tbl, l_error_pointer);
4339 if(l_llsControl_status <> 'S') then
4340 raise l_early_exp;
4341 end if;
4342
4343
4344
4345 x_progress := '005';
4346 -- CHECK LPN: No a-->b, a-->c
4347 open l_checkLpnContra_csr;
4348 loop
4349 fetch l_checkLpnContra_csr into
4350 l_error_ln,
4351 l_error_ln2;
4352 exit when l_checkLpnContra_csr%NOTFOUND;
4353 --The Parent-Child relationships for license plate number at line LINE_NUM1 and LINE_NUM2 contradict each other.
4354 fnd_message.set_name('POS','POS_EXASN_PLPN_CONTRA');
4355 fnd_message.set_token('LINE_NUM1',l_error_ln);
4356 fnd_message.set_token('LINE_NUM2',l_error_ln2);
4357 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
4358 raise l_25errors;
4359 end if;
4360 end loop;
4361 close l_checkLpnContra_csr;
4362
4363
4364
4365
4366 x_progress := '006';
4367 ValidateLls(l_lls_status, x_error_tbl, l_error_pointer);
4368 if(l_lls_status <> 'S') then
4369 raise l_early_exp;
4370 end if;
4371
4372
4373
4374 if(x_error_tbl.count > 0) then
4375 raise l_early_exp;
4376 end if;
4377
4378 --Create Extra RTI for Lot Lines
4379 x_progress := '007';
4380 CreateRTI4Lot;
4381
4382 --Create Extra RTI for Serial Lines
4383 x_progress := '008';
4384 CreateRTI4Ser;
4385
4386 --Create Extra RTI for Lpn Lines
4387 x_progress := '009';
4388 CreateRTI4Lpn;
4389
4390
4391 x_progress := '010';
4392 UpdateLinesAndLls(x_error_tbl,l_error_pointer);
4393
4394 --For LAS, need to update Child Serial in many ways ==> CreateRTI4Lot
4395
4396
4397
4398 if(x_error_tbl.count > 0) then
4399 raise l_early_exp;
4400 end if;
4401
4402 x_progress := '011';
4403 InsertIntoRHI;
4404 x_progress := '012';
4405 InsertIntoRTI;
4406
4407
4408 x_progress := '013';
4409 InsertIntoLLS(l_insertlls_status, x_error_tbl, l_error_pointer);
4410 if(x_error_tbl.count > 0) then
4411 raise l_early_exp;
4412 end if;
4413
4414 --Send Notifications to Buyer for each ASN Header
4415 open l_buyerNotif_csr;
4416 loop
4417 fetch l_buyerNotif_csr into
4418 l_shipment_number, l_vendor_id, l_vendor_site_id;
4419 exit when l_buyerNotif_csr%NOTFOUND;
4420
4421 POS_ASN_NOTIF.GENERATE_NOTIF ( p_shipment_num => l_shipment_number,
4422 p_notif_type => 'CREATE',
4423 p_vendor_id => l_vendor_id,
4424 p_vendor_site_id => l_vendor_site_id,
4425 p_user_id => fnd_global.user_id
4426 );
4427 end loop;
4428 close l_buyerNotif_csr;
4429
4430
4431 exception when l_early_exp then
4432 x_return_status := FND_API.G_RET_STS_ERROR;
4433
4434 when l_25errors then
4435 x_return_status := FND_API.G_RET_STS_ERROR;
4436
4437 when others then
4438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4439 x_return_msg := 'Unexpected error in ProcessExcelAsn:'||x_progress||':'||sqlerrm;
4440 if(InsertError(x_error_tbl, 'Unexpected error in ProcessExcelAsn:'||x_progress||':'||sqlerrm, l_error_pointer)=1) then
4441 null;
4442 end if;
4443 end ProcessExcelAsn;
4444
4445 -------------------------------------------------------------------------------
4446 --Start of Comments
4447 --Name: getvendorpaysiteid
4448 --Pre-reqs:
4449 -- None.
4450 --Function:
4451 -- It returns the vendor site id depends on the Paysite Defaulting logic
4452 --Function Usage:
4453 -- This function is used for both paysite validation and defaulting the
4454 -- value in Remit-To-Site of the ASBN that is being uploaded
4455 --Logic Implemented:
4456 -- Paysite Defaulting Logic:
4457 -- 1.Check whether the primary paysite is available or not for the vendor
4458 -- If it is available, returns the vendor site id of the primary paysite
4459 -- else go to step2
4460 -- 2.Check whether the purchasing site itself,is a paysite or not
4461 -- If it is, returns the vendor site id of the purchasing paysite
4462 -- else go to step3
4463 -- 3.Check whether the PO's purchasing site has any alternate paysite or not
4464 -- If it has, returns the vendor site id of the Alternate paysite
4465 -- else go to step4
4466 -- 4. check whether any paysites available for the vendor or not
4467 -- If it has, returns the vendor site id of the first created paysite
4468 -- else returns NULL
4469 --Parameters:
4470 -- Vendor id, Vendor Site id, Currency code
4471 --IN:
4472 -- p_vendor_id,p_vendor_site_id,p_currency_code
4473 --OUT:
4474 -- l_vendor_site_id
4475 --Bug Number for reference:
4476 -- Base Bug 6718930,7196781
4477 -- Bug 7338353
4478 --End of Comments
4479 ------------------------------------------------------------------------------
4480
4481 function getvendorpaysiteid(p_vendor_id in varchar2,p_vendor_site_id IN varchar2,p_currency_code IN varchar2) RETURN PO_VENDOR_SITES_ALL.vendor_site_id%type
4482 IS
4483 --Local Variable Declaration
4484 l_vendor_site_id PO_VENDOR_SITES_ALL.vendor_site_id%type:=0;
4485 l_DEFAULT_PAY_SITE_ID PO_VENDOR_SITES_ALL.default_pay_site_id%type;
4486 l_org_id PO_VENDOR_SITES_ALL.default_pay_site_id%type;
4487
4488 BEGIN
4489
4490 -- Getting the org id of the vendor site
4491 SELECT org_id
4492 INTO l_org_id
4493 FROM po_vendor_sites_all
4494 WHERE vendor_site_id= p_vendor_site_id;
4495
4496 -- Check for primary paysite exists
4497 BEGIN
4498 select vendor_site_id
4499 into l_vendor_site_id
4500 from PO_VENDOR_SITES_ALL PVS
4501 where
4502 SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
4503 AND PVS.org_id = l_org_id
4504 AND PVS.vendor_id = p_vendor_id
4505 AND PVS.primary_pay_site_flag = 'Y'
4506 AND ROWNUM=1;
4507
4508 EXCEPTION
4509 WHEN NO_DATA_FOUND THEN
4510 --if no primary paysite exists, then check whether the purchasing site is paysite or not
4511 BEGIN
4512
4513 select vendor_site_id
4514 into l_vendor_site_id
4515 from PO_VENDOR_SITES_ALL PVS
4516 WHERE
4517 SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
4518 AND PVS.org_id = l_org_id
4519 AND PVS.vendor_id = p_vendor_id
4520 AND PVS.vendor_site_id = p_vendor_site_id
4521 AND PVS.pay_site_flag = 'Y'
4522 AND ROWNUM=1;
4523
4524 EXCEPTION
4525 WHEN NO_DATA_FOUND THEN
4526 BEGIN
4527 --if the purchasing site is not a pay site, then check whether it has default pay site
4528 select default_pay_site_id
4529 into l_DEFAULT_PAY_SITE_ID
4530 from PO_VENDOR_SITES_ALL PVS
4531 where PVS.org_id = l_org_id
4532 AND PVS.vendor_id = p_vendor_id
4533 AND PVS.vendor_site_id = p_vendor_site_id
4534 AND ROWNUM=1;
4535
4536 EXCEPTION
4537 WHEN NO_DATA_FOUND THEN
4538 l_DEFAULT_PAY_SITE_ID :=0;
4539 END;
4540
4541 IF l_DEFAULT_PAY_SITE_ID > 0 then
4542 BEGIN
4543 --If it has default paysite, then check default paysite id is paysite or not
4544 select vendor_site_id
4545 into l_vendor_site_id
4546 from PO_VENDOR_SITES_ALL PVS
4547 where org_id = l_org_id
4548 AND PVS.vendor_id = p_vendor_id
4549 AND PVS.vendor_site_id = l_DEFAULT_PAY_SITE_ID
4550 AND PVS.pay_site_flag = 'Y'
4551 AND ROWNUM=1;
4552 EXCEPTION
4553 WHEN NO_DATA_FOUND THEN
4554 l_vendor_site_id :=0;
4555 END;
4556 End if;
4557 WHEN too_many_rows then
4558 null;
4559 end;
4560 END;
4561 --If default paysite is not available for the purchasing site, then check whether it has any other paysites
4562 --available for the particular Org and Vendor
4563 IF l_vendor_site_id =0 THEN
4564
4565 SELECT VENDOR_SITE_ID
4566 into l_vendor_site_id
4567 FROM (select
4568 CURRENCY_CODE,
4569 PVS.VENDOR_ID,
4570 PVS.VENDOR_SITE_ID,
4571 PVS.VENDOR_SITE_CODE,
4572 /*PVS.PAYMENT_METHOD_LOOKUP_CODE,*/
4573 Nvl(PRT.PAYMENT_METHOD_CODE, PAYEE.DEFAULT_PAYMENT_METHOD_CODE) PAYMENT_METHOD_LOOKUP_CODE,
4574 NVL(PVS.ORG_ID, -99) ORG_ID
4575 FROM
4576 /* AP_BANK_ACCOUNT_USES_ALL ABAU,
4577 AP_BANK_ACCOUNTS_ALL ABA,
4578 AP_BANK_BRANCHES ABB,*/
4579 PO_VENDOR_SITES_ALL PVS
4580 /*Added for bug#13554162 */
4581 ,
4582 ap_suppliers sup,
4583 IBY_EXTERNAL_PAYEES_ALL PAYEE,
4584 IBY_EXT_PARTY_PMT_MTHDS PRT,
4585 iby_pmt_instr_uses_all ipi,
4586 iby_ext_bank_accounts ieb,
4587 ce_bank_branches_v bankbranch
4588 WHERE
4589 /*ABAU.EXTERNAL_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID(+) AND
4590 ABA.BANK_BRANCH_ID = ABB.BANK_BRANCH_ID(+) AND
4591 ABAU.VENDOR_ID(+) = PVS.VENDOR_ID AND
4592 ABAU.VENDOR_SITE_ID(+) = PVS.VENDOR_SITE_ID AND
4593 (NVL(ABAU.PRIMARY_FLAG, 'N') = 'Y' OR ABAU.BANK_ACCOUNT_USES_ID is null) AND
4594 PVS.PAYMENT_METHOD_LOOKUP_CODE = 'EFT' AND
4595 SYSDATE < NVL(ABB.END_DATE, SYSDATE+1) AND
4596 SYSDATE < NVL(ABA.INACTIVE_DATE, SYSDATE+1)*/
4597 /*Added for bug#13554162*/
4598 NVL(PVS.PAY_SITE_FLAG, 'N') = 'Y' AND
4599 SYSDATE < Nvl(ipi.END_DATE, SYSDATE + 1)
4600 AND Decode (PRT.PAYMENT_METHOD_CODE, NULL,
4601 Nvl(PAYEE.DEFAULT_PAYMENT_METHOD_CODE, -1),PRT.PAYMENT_METHOD_CODE) = 'EFT'
4602 AND pvs.vendor_id = sup.vendor_id
4603 AND payee.payee_party_id = sup.party_id
4604 AND payee.supplier_site_id = pvs.vendor_site_id
4605 AND payee.org_id IS NOT NULL
4606 AND payee.org_type IS NOT NULL
4607 AND payee.payment_function = prt.payment_function(+)
4608 AND payee.ext_payee_id = prt.ext_pmt_party_id(+)
4609 AND prt.primary_flag(+) = 'Y'
4610 AND PAYEE.ext_payee_id = ipi.ext_pmt_party_id
4611 AND ipi.payment_flow = 'DISBURSEMENTS'
4612 AND ipi.instrument_type = 'BANKACCOUNT'
4613 AND ipi.payment_function = 'PAYABLES_DISB'
4614 AND ipi.instrument_id = ieb.ext_bank_account_id
4615 AND ieb.bank_id = bankbranch.bank_party_id
4616 AND ieb.branch_id = bankbranch.branch_party_id
4617 UNION
4618 select
4619 NULL CURRENCY_CODE,
4620 PVS.VENDOR_ID,
4621 PVS.VENDOR_SITE_ID,
4622 PVS.VENDOR_SITE_CODE,
4623 /*PVS.PAYMENT_METHOD_LOOKUP_CODE,*/
4624 Nvl(PRT.PAYMENT_METHOD_CODE, PAYEE.DEFAULT_PAYMENT_METHOD_CODE) PAYMENT_METHOD_LOOKUP_CODE,
4625 NVL(PVS.ORG_ID, -99) ORG_ID
4626 FROM
4627 PO_VENDOR_SITES_ALL PVS ,
4628 ap_suppliers sup,
4629 IBY_EXTERNAL_PAYEES_ALL PAYEE,
4630 IBY_EXT_PARTY_PMT_MTHDS PRT
4631 WHERE
4632 SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
4633 AND NVL(PVS.PAY_SITE_FLAG, 'N') = 'Y'
4634 /*added for bug#13554162*/
4635 AND Decode (PRT.PAYMENT_METHOD_CODE, NULL,
4636 Nvl(PAYEE.DEFAULT_PAYMENT_METHOD_CODE, -1),PRT.PAYMENT_METHOD_CODE) <> 'EFT'
4637 AND pvs.vendor_id = sup.vendor_id
4638 AND PAYEE.PAYEE_PARTY_ID = sup.party_id
4639 AND PAYEE.supplier_site_id = PVS.VENDOR_SITE_ID
4640 AND PAYEE.org_id IS NOT NULL
4641 AND PAYEE.org_type IS NOT NULL
4642 AND PAYEE.PAYMENT_FUNCTION = PRT.PAYMENT_FUNCTION(+)
4643 AND PAYEE.EXT_PAYEE_ID = PRT.EXT_PMT_PARTY_ID(+)
4644 AND PRT.PRIMARY_FLAG(+) = 'Y') QRSLT WHERE (ORG_ID = NVL(l_org_id, -99) AND VENDOR_ID = p_vendor_id AND
4645 DECODE(PAYMENT_METHOD_LOOKUP_CODE, 'EFT', CURRENCY_CODE, p_currency_code) = p_currency_code AND ROWNUM=1);
4646 END IF;
4647
4648 RETURN l_vendor_site_id;
4649
4650 END getvendorpaysiteid;
4651
4652
4653 END Pos_ExcelAsn_PVT;