[Home] [Help]
PACKAGE BODY: APPS.POS_EXCELASN_PVT
Source
1 PACKAGE BODY Pos_ExcelAsn_PVT AS
2 /* $Header: POSVEXAB.pls 120.7.12010000.3 2008/11/11 09:32:53 sthoppan 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 number;
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
945 BEGIN
946 --L1
947 open l_checkUOM_csr;
948 loop
949 fetch l_checkUOM_csr into l_error_ln, l_error_field;
950 exit when l_checkUOM_csr%NOTFOUND;
951 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
952 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_QTYUOM'));
953 fnd_message.set_token('LOV_VALUE',l_error_field);
954 fnd_message.set_token('LINE_NUM',l_error_ln);
955 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
956 raise l_25errors;
957 end if;
958 end loop;
959 close l_checkUOM_csr;
960
961 --L2
962 open l_checkCountryOO_csr;
963 loop
964 fetch l_checkCountryOO_csr into l_error_ln, l_error_field;
965 exit when l_checkCountryOO_csr%NOTFOUND;
966 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
967 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_COORIGIN'));
968 fnd_message.set_token('LOV_VALUE',l_error_field);
969 fnd_message.set_token('LINE_NUM',l_error_ln);
970 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
971 raise l_25errors;
972 end if;
973 end loop;
974 close l_checkCountryOO_csr;
975
976
977 --L3: Organization Name
978 update pos_exasn_lines plnt
979 set plnt.org_id = (
980 select hou.organization_id
981 from hr_operating_units hou
982 where hou.name = plnt.operating_unit);
983
984
985 open l_checkOrgName_csr;
986 loop
987 fetch l_checkOrgName_csr into l_error_ln, l_error_field;
988 exit when l_checkOrgName_csr%notfound;
989 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
990 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_ORGUNIT'));
991 fnd_message.set_token('LOV_VALUE',l_error_field);
992 fnd_message.set_token('LINE_NUM',l_error_ln);
993 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
994 raise l_25errors;
995 end if;
996 end loop;
997 close l_checkOrgName_csr;
998
999 --L4: Valid Org/PO/Line/Shipment Number
1000 update pos_exasn_lines plnt
1001 set (
1002 po_header_id,
1003 po_line_id,
1004 po_line_location_id,
1005 vendor_id,
1006 vendor_site_id,
1007 vendor_contact_id,
1008 ship_to_org_id,
1009 vendor_name,
1010 vendor_site_code,
1011 rate_type,
1012 rate,
1013 rate_date,
1014 item_id,
1015 item_revision,
1016 unit_price,
1017 vendor_product_num,
1018 currency_code,
1019 primary_uom,
1020 ship_to_location_code,
1021 ship_to_location_id,
1022 item_description
1023 )
1024 =
1025 (
1026 select
1027 pha.po_header_id,
1028 pla.po_line_id,
1029 plla.line_location_id,
1030 pv.vendor_id,
1031 pvs.vendor_site_id,
1032 pha.vendor_contact_id,
1033 plla.ship_to_organization_id,
1034 pv.vendor_name,
1035 pvs.vendor_site_code,
1036 pha.rate_type,
1037 pha.rate,
1038 pha.rate_date,
1039 pla.item_id,
1040 pla.item_revision,
1041 pla.unit_price,
1042 pla.vendor_product_num,
1043 pha.currency_code,
1044 pla.unit_meas_lookup_code,
1045 nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)),
1046 plla.ship_to_location_id,
1047 pla.item_description
1048
1049 from
1050 po_headers_all pha,
1051 po_lines_all pla,
1052 po_line_locations_all plla,
1053 po_vendors pv,
1054 po_vendor_sites_all pvs,
1055 hr_locations_all_tl hrl,
1056 hz_locations hz
1057 where pha.segment1 = plnt.po_number
1058 and pha.org_id = plnt.org_id
1059 and pha.revision_num = plnt.po_revision
1060 and pha.po_header_id = pla.po_header_id
1061 and pla.line_num = plnt.po_line
1062 and pla.po_line_id = plla.po_line_id
1063 and plla.shipment_num = plnt.po_shipment
1064 and pv.vendor_id = pha.vendor_id
1065 and pvs.vendor_site_id = pha.vendor_site_id
1066 and hrl.location_id(+) = plla.ship_to_location_id
1067 and hrl.LANGUAGE(+) = USERENV('LANG')
1068 and hz.location_id(+) = plla.ship_to_location_id
1069 and pha.type_lookup_code = 'STANDARD'
1070 and pha.AUTHORIZATION_STATUS = 'APPROVED'
1071 and NVL(plla.approved_flag, 'N') = 'Y'
1072 and NVL(plla.CANCEL_FLAG, 'N') = 'N'
1073 and NVL(pha.FROZEN_FLAG, 'N') = 'N'
1074 and NVL(pha.CONSIGNED_CONSUMPTION_FLAG, 'N') <> 'Y'
1075 and NVL(plla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED')
1076 )
1077 where org_id is not null
1078 and po_release_num is null;
1079
1080 select count(1) into l_error_ln from pos_exasn_lines where po_line_location_id is null;
1081
1082
1083 update pos_exasn_lines plnt
1084 set (
1085 po_header_id,
1086 po_release_id,
1087 po_line_id,
1088 po_line_location_id,
1089 vendor_id,
1090 vendor_site_id,
1091 vendor_contact_id,
1092 ship_to_org_id,
1093 vendor_name,
1094 vendor_site_code,
1095 rate_type,
1096 rate,
1097 rate_date,
1098 item_id,
1099 item_revision,
1100 unit_price,
1101 vendor_product_num,
1102 currency_code,
1103 primary_uom,
1104 ship_to_location_code,
1105 ship_to_location_id,
1106 item_description
1107 )
1108 =
1109 (
1110 select
1111 pha.po_header_id,
1112 pra.po_release_id,
1113 pla.po_line_id,
1114 plla.line_location_id,
1115 pv.vendor_id,
1116 pvs.vendor_site_id,
1117 pha.vendor_contact_id,
1118 plla.ship_to_organization_id,
1119 pv.vendor_name,
1120 pvs.vendor_site_code,
1121 pha.rate_type,
1122 pha.rate,
1123 pha.rate_date,
1124 pla.item_id,
1125 pla.item_revision,
1126 pla.unit_price,
1127 pla.vendor_product_num,
1128 pha.currency_code,
1129 pla.unit_meas_lookup_code,
1130 nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)),
1131 plla.ship_to_location_id,
1132 pla.item_description
1133 from
1134 po_headers_all pha,
1135 po_releases_all pra,
1136 po_lines_all pla,
1137 po_line_locations_all plla,
1138 po_vendors pv,
1139 po_vendor_sites_all pvs,
1140 hr_locations_all_tl hrl,
1141 hz_locations hz
1142 where pha.segment1 = plnt.po_number
1143 and pha.org_id = plnt.org_id
1144 and pha.po_header_id = pra.po_header_id
1145 and pra.release_num = plnt.po_release_num
1146 and pra.revision_num = plnt.po_revision
1147 and pra.po_release_id = plla.po_release_id
1148 and plla.shipment_num = plnt.po_shipment
1149 and pha.vendor_id = pv.vendor_id
1150 and pha.vendor_site_id = pvs.vendor_site_id
1151 and hrl.location_id(+) = plla.ship_to_location_id
1152 and hrl.LANGUAGE(+) = USERENV('LANG')
1153 and hz.location_id(+) = plla.ship_to_location_id
1154 and pla.po_line_id = plla.po_line_id
1155 and pra.AUTHORIZATION_STATUS = 'APPROVED'
1156 and NVL(plla.approved_flag, 'N') = 'Y'
1157 and NVL(plla.CANCEL_FLAG, 'N') = 'N'
1158 and NVL(pra.FROZEN_FLAG, 'N') = 'N'
1159 and NVL(pra.CONSIGNED_CONSUMPTION_FLAG, 'N') <> 'Y'
1160 and NVL(plla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED')
1161
1162 )
1163 where org_id is not null
1164 and po_release_num is not null;
1165
1166 l_failPORelCheck := 'F';
1167 open l_checkPO_csr;
1168 loop
1169 fetch l_checkPO_csr into
1170 l_error_ln,
1171 l_doc_num,
1172 l_revision_num,
1173 l_po_line,
1174 l_po_shipment,
1175 l_org_name;
1176 exit when l_checkPO_csr%NOTFOUND;
1177 l_failPORelCheck := 'T';
1178 fnd_message.set_name('POS','POS_EXASN_PO_NOEXIST');
1179 fnd_message.set_token('DOC_NUM',l_doc_num);
1180 fnd_message.set_token('REV_NUM',l_revision_num);
1181 fnd_message.set_token('LINENUM',l_po_line);
1182 fnd_message.set_token('SHIP_NUM',l_po_shipment);
1183 fnd_message.set_token('LINE_NUM',l_error_ln);
1184 fnd_message.set_token('OP_UNIT',l_org_name);
1185 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1186 raise l_25errors;
1187 end if;
1188 end loop;
1189 close l_checkPO_csr;
1190
1191 open l_checkREL_csr;
1192 loop
1193 fetch l_checkREL_csr into
1194 l_error_ln,
1195 l_doc_num,
1196 l_rel_num,
1197 l_revision_num,
1198 l_po_shipment,
1199 l_org_name;
1200 exit when l_checkREL_csr%NOTFOUND;
1201 l_failPORelCheck := 'T';
1202 fnd_message.set_name('POS','POS_EXASN_REL_NO_EXIST');
1203 fnd_message.set_token('DOC_NUM',l_doc_num);
1204 fnd_message.set_token('REL_NUM',l_rel_num);
1205 fnd_message.set_token('REV_NUM',l_revision_num);
1206 fnd_message.set_token('SHIP_NUM',l_po_shipment);
1207 fnd_message.set_token('LINE_NUM',l_error_ln);
1208 fnd_message.set_token('OP_UNIT',l_org_name);
1209 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1210 raise l_25errors;
1211 end if;
1212 end loop;
1213 close l_checkREL_csr;
1214
1215 if(l_failPORelCheck = 'T') then
1216 raise l_early_exp;
1217 end if;
1218
1219 l_failSecure := 'F';
1220
1221 --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,
1222 --ICX_SUPPLIER_ORG_ID
1223
1224 --For each vendor id of each line, we check that against the retrieved set
1225 --of secured org id attributes to make sure it is in the set. Otherwise,
1226 --throw an error message
1227 OPEN l_vendors_csr;
1228 LOOP
1229 FETCH l_vendors_csr into l_error_ln, l_vendor_id;
1230 EXIT WHEN l_vendors_csr%NOTFOUND;
1231
1232 l_ind_start := p_user_vendor_id_tbl.first();
1233 l_ind_end := p_user_vendor_id_tbl.last();
1234 l_vendor_id_not_secured := 'T';
1235
1236 --make sure all vendor ids are secured
1237 FOR l_vendor_ind IN l_ind_start .. l_ind_end
1238 LOOP
1239
1240 IF(l_vendor_id = p_user_vendor_id_tbl(l_vendor_ind)) THEN
1241 l_vendor_id_not_secured := 'F';
1242 END IF;
1243
1244 END LOOP;
1245
1246 IF(l_vendor_id_not_secured = 'T') THEN
1247
1248 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1249 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1250 MODULE =>'pos.plsql.pos_asn_create_pvt.ValidateLines',
1251 MESSAGE => 'Line : ' || l_error_ln || 'Vendor Id: ' ||
1252 l_vendor_id||' is not secured');
1253 END IF;
1254
1255 l_failSecure := 'T';
1256 fnd_message.set_name('POS','POS_EXASN_NOT_SEC');
1257 fnd_message.set_token('LINE_NUM',l_error_ln);
1258 IF(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) THEN
1259 raise l_25errors;
1260 END IF;
1261 END IF;
1262
1263 END LOOP;
1264 CLOSE l_vendors_csr;
1265
1266
1267 if(p_secure_by_site = 'T') then
1268 open l_checkVendorSites_csr(fnd_global.user_id);
1269 loop
1270 fetch l_checkVendorSites_csr into l_error_ln;
1271 exit when l_checkVendorSites_csr%NOTFOUND;
1272 l_failSecure := 'T';
1273 fnd_message.set_name('POS','POS_EXASN_NOT_SEC');
1274 fnd_message.set_token('LINE_NUM',l_error_ln);
1275 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1276 raise l_25errors;
1277 end if;
1278 end loop;
1279 close l_checkVendorSites_csr;
1280 end if;
1281
1282 if(p_secure_by_contact = 'T') then
1283 open l_checkVendorContacts_csr(fnd_global.user_id);
1284 loop
1285 fetch l_checkVendorContacts_csr into l_error_ln;
1286 exit when l_checkVendorContacts_csr%NOTFOUND;
1287 l_failSecure := 'T';
1288 fnd_message.set_name('POS','POS_EXASN_NOT_SEC');
1289 fnd_message.set_token('LINE_NUM',l_error_ln);
1290 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1291 raise l_25errors;
1292 end if;
1293 end loop;
1294 close l_checkVendorContacts_csr;
1295 end if;
1296
1297 if(l_failSecure = 'T') then
1298 raise l_early_exp;
1299 end if;
1300
1301 --L6+L8 (to be done before creating new Lines, because we cannot split up the shipments into multiple RTIs and then do validations
1302
1303 open l_allLines_csr;
1304 loop
1305 fetch l_allLines_csr into
1306 l_line_number,
1307 l_quantity,
1308 l_uom,
1309 l_po_line_location_id,
1310 l_item_id,
1311 l_header_id;
1312 exit when l_allLines_csr%NOTFOUND;
1313 select
1314 PLL.qty_rcv_exception_code,
1315 PLL.receipt_days_exception_code,
1316 PLL.DAYS_EARLY_RECEIPT_ALLOWED,
1317 PLL.DAYS_LATE_RECEIPT_ALLOWED,
1318 NVL(PLL.PROMISED_DATE,PLL.NEED_BY_DATE),
1319 PLL.outsourced_assembly
1320 into
1321 l_qty_rcv_exception_code,
1322 l_receipt_days_exception_code,
1323 l_days_early,
1324 l_days_late,
1325 l_due_date,
1326 l_outsourced_assembly
1327 from po_line_locations_all PLL
1328 where line_location_id = l_po_line_location_id;
1329
1330 if(l_qty_rcv_exception_code = 'REJECT') then
1331
1332 l_primary_qty := getConvertedQuantity(l_po_line_location_id, l_quantity, l_uom);
1333
1334 l_tolerableShipQty := POS_CREATE_ASN.getTolerableShipmentQuantity(l_po_line_location_id);
1335
1336 if(l_primary_Qty = -1) then
1337 fnd_message.set_name('POS','POS_EXASN_EXCEPT_UOM');
1338 --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.
1339 fnd_message.set_token('LINE_NUM',l_line_number);
1340 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1341 raise l_25errors;
1342 end if;
1343 elsif(l_tolerableShipQty < l_primary_qty ) then
1344
1345 fnd_message.set_name('POS','POS_EXASN_QTY_GRT_REM');
1346 fnd_message.set_token('QTY',l_quantity);
1347 fnd_message.set_token('LINE_NUM',l_line_number);
1348 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1349 raise l_25errors;
1350 end if;
1351 end if;
1352 end if;
1353
1354 if(l_receipt_days_exception_code = 'REJECT') then
1355 select
1356 expected_receipt_date,
1357 line_number
1358 into
1359 l_exp_rec_date,
1360 l_header_line_number
1361 from pos_exasn_headers
1362 where header_id = l_header_id;
1363
1364 if(l_exp_rec_date > l_due_date+l_days_late OR l_exp_rec_date < l_due_date-l_days_early) then
1365 fnd_message.set_name('POS','POS_EXASN_ERDATE_TOL');
1366 fnd_message.set_token('HDR_LINE_NUM',l_header_line_number);
1367 fnd_message.set_token('LINE_NUM',l_line_number);
1368 --the expected receipt date defined in line HDR_LINE_NUM will violate the tolerance for PO Shipment at line LINE_NUM
1369 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1370 raise l_25errors;
1371 end if;
1372 end if;
1373 end if;
1374
1375 if ( l_outsourced_assembly = 1 ) then
1376 fnd_message.set_name('POS','POS_EXASN_ERR_SHIKYU');
1377 fnd_message.set_token('LINE_NUM',l_line_number);
1378 -- Shipments can not be created using Outsourced assembly items
1379 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1380 raise l_25errors;
1381 end if;
1382 end if;
1383 end loop;
1384 close l_allLines_csr;
1385
1386 --L7
1387
1388 open l_checkExistAsn_csr;
1389 loop
1390 fetch l_checkExistAsn_csr into l_error_ln, l_error_field;
1391 exit when l_checkExistAsn_csr%NOTFOUND;
1392
1393 if(l_previous_sn is null OR l_previous_sn <> l_error_field) then
1394 l_new_sn := 'T';
1395 l_previous_sn := l_error_field;
1396 else
1397 l_new_sn := 'F';
1398 end if;
1399
1400 if(l_new_sn = 'T') then
1401 fnd_message.set_name('POS','POS_EXASN_DUPE_SHIP');
1402 --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.
1403 fnd_message.set_token('LINE_NUM',l_error_ln);
1404 fnd_message.set_token('SHIP_NUM',l_error_field);
1405 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
1406 raise l_25errors;
1407 end if;
1408 end if;
1409
1410 end loop;
1411 close l_checkExistAsn_csr;
1412
1413 x_return_status := 'S';
1414 exception when l_early_exp then
1415 x_return_status := 'E';
1416 when l_25errors then
1417 x_return_status := 'E';
1418 when others then
1419 x_return_status := 'U';
1420 END ValidateLines;
1421
1422
1423 procedure ValidateHeaders(x_return_status out nocopy varchar2,
1424 p_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
1425 p_error_pointer in out nocopy number)
1426 IS
1427 l_error_ln number;
1428 l_error_field varchar2(200);
1429 l_25errors exception;
1430
1431
1432 --H1: Freight Carrier Code (TO BE DONE IN FixHeadersAndLines, because we'll need pht.ship_to_org_id)
1433
1434 --H2: GROSS WEIGHT UOM
1435 cursor l_checkGrossWtUOM_csr
1436 is
1437 select pht.line_number, pht.gross_weight_uom
1438 from pos_exasn_headers pht
1439 where pht.gross_weight_uom is not null
1440 and not exists
1441 (select 1 from por_unit_of_measure_lov_v puomv
1442 where puomv.unit_of_measure = pht.gross_weight_uom);
1443
1444
1445 --H3: NET WEIGHT UOM
1446 cursor l_checkNetWtUOM_csr
1447 is
1448 select pht.line_number, pht.net_weight_uom
1449 from pos_exasn_headers pht
1450 where pht.net_weight_uom is not null
1451 and not exists
1452 (select 1 from por_unit_of_measure_lov_v puomv
1453 where puomv.unit_of_measure = pht.net_weight_uom);
1454
1455 --H4: TAR WEIGHT UOM
1456 cursor l_checkTarWtUOM_csr
1457 is
1458 select pht.line_number, pht.tar_weight_uom
1459 from pos_exasn_headers pht
1460 where pht.tar_weight_uom is not null
1461 and not exists
1462 (select 1 from por_unit_of_measure_lov_v puomv
1463 where puomv.unit_of_measure = pht.tar_weight_uom);
1464
1465 --H5: FREIGHT TERMS
1466 cursor l_checkFreightTerms_csr
1467 is
1468 select pht.line_number, pht.freight_terms
1469 from pos_exasn_headers pht
1470 where pht.freight_terms is not null
1471 and not exists
1472 (select 1 from po_lookup_codes plc
1473 where plc.lookup_type = 'FREIGHT TERMS'and sysdate < nvl(plc.inactive_date, sysdate + 1)
1474 and plc.lookup_code = pht.freight_terms);
1475
1476 --H7: SHIPMENT DATE has to be < SYSDATE
1477 cursor l_checkShipDate_csr
1478 is
1479 select pht.line_number, shipment_date
1480 from pos_exasn_headers pht
1481 where shipment_date > sysdate;
1482
1483 --H8: INVOICE PAYMENT TERMS
1484 cursor l_checkInvPT_csr
1485 is
1486 select pht.line_number, pht.payment_terms
1487 from pos_exasn_headers pht
1488 where pht.payment_terms is not null
1489 and not exists
1490 (select 1 from ap_terms_val_v av
1491 where av.name = pht.payment_terms);
1492
1493 --H9: Bad if expected receipt Date is BEFORE Shipment Date
1494 cursor l_checkER_Ship_csr
1495 is
1496 select pht.line_number
1497 from pos_exasn_headers pht
1498 where expected_receipt_date < shipment_date;
1499
1500
1501
1502 BEGIN
1503
1504 --H2
1505 open l_checkGrossWtUOM_csr;
1506 loop
1507 fetch l_checkGrossWtUOM_csr into l_error_ln, l_error_field;
1508 exit when l_checkGrossWtUOM_csr%NOTFOUND;
1509 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1510 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_GWUOM'));
1511 fnd_message.set_token('LOV_VALUE',l_error_field);
1512 fnd_message.set_token('LINE_NUM',l_error_ln);
1513 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1514 raise l_25errors;
1515 end if;
1516 end loop;
1517 close l_checkGrossWtUOM_csr;
1518
1519 --Convert Gross Weight UOM to code
1520 update pos_exasn_headers
1521 set gross_weight_uom =
1522 (select puomv.uom_code
1523 from por_unit_of_measure_lov_v puomv
1524 where gross_weight_uom is not null
1525 and gross_weight_uom = puomv.unit_of_measure);
1526
1527 --H3
1528 open l_checkNetWtUOM_csr;
1529 loop
1530 fetch l_checkNetWtUOM_csr into l_error_ln, l_error_field;
1531 exit when l_checkNetWtUOM_csr%NOTFOUND;
1532 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1533 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_NWUOM'));
1534 fnd_message.set_token('LOV_VALUE',l_error_field);
1535 fnd_message.set_token('LINE_NUM',l_error_ln);
1536 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1537 raise l_25errors;
1538 end if;
1539 end loop;
1540 close l_checkNetWtUOM_csr;
1541
1542 --Convert Net Weight UOM to code
1543 update pos_exasn_headers
1544 set net_weight_uom =
1545 (select puomv.uom_code
1546 from por_unit_of_measure_lov_v puomv
1547 where net_weight_uom is not null
1548 and net_weight_uom = puomv.unit_of_measure);
1549
1550 --H4
1551 open l_checkTarWtUOM_csr;
1552 loop
1553 fetch l_checkTarWtUOM_csr into l_error_ln, l_error_field;
1554 exit when l_checkTarWtUOM_csr%NOTFOUND;
1555 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1556 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_TWUOM'));
1557 fnd_message.set_token('LOV_VALUE',l_error_field);
1558 fnd_message.set_token('LINE_NUM',l_error_ln);
1559 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1560 raise l_25errors;
1561 end if;
1562 end loop;
1563 close l_checkTarWtUOM_csr;
1564
1565 --Convert Tar Weight UOM to code
1566 update pos_exasn_headers
1567 set tar_weight_uom =
1568 (select puomv.uom_code
1569 from por_unit_of_measure_lov_v puomv
1570 where tar_weight_uom is not null
1571 and tar_weight_uom = puomv.unit_of_measure);
1572
1573
1574 --H5
1575 open l_checkFreightTerms_csr;
1576 loop
1577 fetch l_checkFreightTerms_csr into l_error_ln, l_error_field;
1578 exit when l_checkFreightTerms_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_FRTERM'));
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_checkFreightTerms_csr;
1588
1589 --H7
1590 open l_checkShipDate_csr;
1591 loop
1592 fetch l_checkShipDate_csr into l_error_ln, l_error_field;
1593 exit when l_checkShipDate_csr%NOTFOUND;
1594 fnd_message.set_name('POS','POS_EXASN_BAD_SHP_DATE');
1595 fnd_message.set_token('LINE_NUM',l_error_ln);
1596 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1597 raise l_25errors;
1598 end if;
1599 end loop;
1600 close l_checkShipDate_csr;
1601
1602 --H8
1603 open l_checkInvPT_csr;
1604 loop
1605 fetch l_checkInvPT_csr into l_error_ln, l_error_field;
1606 exit when l_checkInvPT_csr%NOTFOUND;
1607 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
1608 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_PAYTERM'));
1609 fnd_message.set_token('LOV_VALUE',l_error_field);
1610 fnd_message.set_token('LINE_NUM',l_error_ln);
1611 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1612 raise l_25errors;
1613 end if;
1614 end loop;
1615 close l_checkInvPT_csr;
1616
1617 --H9
1618 open l_checkER_Ship_csr;
1619 loop
1620 fetch l_checkER_Ship_csr into l_error_ln;
1621 exit when l_checkER_Ship_csr%NOTFOUND;
1622 fnd_message.set_name('POS','POS_EXASN_ERDATE_SDATE');
1623 fnd_message.set_token('LINE_NUM',l_error_ln);
1624 if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
1625 raise l_25errors;
1626 end if;
1627 end loop;
1628 close l_checkER_Ship_csr;
1629
1630 x_return_status := 'S';
1631 exception when l_25errors then
1632 x_return_status := 'E';
1633 when others then
1634 x_return_status := 'U';
1635 end ValidateHeaders;
1636
1637
1638 procedure UpdateLinesAndLls(x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
1639 l_error_pointer in out NOCOPY number)
1640 IS
1641 l_uom pos_exasn_lines.uom%type;
1642 l_po_line_location_id number;
1643 l_tolerableShipQty number;
1644 l_item_id number;
1645 l_quantity number;
1646 l_convQty number;
1647 l_primary_qty number;
1648 l_line_number number;
1649 l_line_id number;
1650
1651 cursor l_allLines_csr
1652 is
1653 select line_number, quantity, uom, po_line_location_id, item_id, line_id
1654 from pos_exasn_lines;
1655
1656 BEGIN
1657 -- Update All lines with Primary Quantity and Invoiced Quantity
1658 open l_allLines_csr;
1659 loop
1660 fetch l_allLines_csr into l_line_number,
1661 l_quantity,
1662 l_uom,
1663 l_po_line_location_id,
1664 l_item_id,
1665 l_line_id;
1666 exit when l_allLines_csr%NOTFOUND;
1667
1668 l_primary_qty := getConvertedQuantity(l_po_line_location_id, l_quantity, l_uom);
1669
1670 if(l_primary_Qty = -1) then
1671 if(InsertError(x_error_tbl, 'Unexpected Error while finding primary quantity for new lines', l_error_pointer)=1) then
1672 null;
1673 end if;
1674 else
1675
1676 update pos_exasn_lines
1677 set primary_quantity = l_primary_qty
1678 where line_id = l_line_id;
1679
1680 update pos_exasn_lines
1681 set invoiced_quantity = POS_QUANTITIES_S.get_invoice_qty
1682 (l_po_line_location_id,
1683 l_uom,
1684 l_item_id,
1685 l_quantity)
1686 where line_id = l_line_id;
1687 end if;
1688 end loop;
1689 close l_allLines_csr;
1690
1691
1692
1693 update pos_exasn_lines plnt
1694 set lpn_group_id = (select pht.lpn_group_id
1695 from pos_exasn_headers pht
1696 where pht.header_id = plnt.header_id)
1697 where
1698 (
1699 plnt.lls_code in ('LAS','LOT')
1700
1701 and exists(
1702 select 1 from pos_exasn_lots plot
1703 where plot.line_id = plnt.line_id
1704 and plot.license_plate_number is not null))
1705 or
1706 (
1707 plnt.lls_code = 'LPN'
1708 and exists(
1709 select 1 from pos_exasn_lpns plpn
1710 where plpn.line_id = plnt.line_id
1711 and plpn.quantity is not null))
1712 or
1713 (
1714 plnt.lls_code = 'SER'
1715 and exists(
1716 select 1 from pos_exasn_serials pst
1717 where pst.line_id = plnt.line_id
1718 and pst.license_plate_number is not null));
1719
1720
1721
1722 update pos_exasn_lots plot
1723 set (plot.product_transaction_id, plot.uom) =(select plnt.interface_transaction_id, plnt.uom
1724 from pos_exasn_lines plnt
1725 where plnt.header_id = plot.header_id
1726 and plnt.line_id = plot.line_id);
1727
1728
1729 update pos_exasn_serials pst
1730 set (pst.product_transaction_id) = (select plnt.interface_transaction_id
1731 from pos_exasn_lines plnt
1732 where plnt.header_id = pst.header_id
1733 and plnt.line_id = pst.line_id);
1734
1735 END UpdateLinesAndLls;
1736
1737
1738 procedure CreateRTI4Ser
1739 is
1740 cursor l_distinctLpn_ser_csr(x_header_id number, x_line_id number)
1741 is
1742 select
1743 distinct
1744 license_plate_number
1745 from pos_exasn_serials
1746 where header_id = x_header_id
1747 and line_id = x_line_id
1748 and lot_id = 0;
1749 --assuming lot_id = 0 ==> SER items
1750
1751 cursor l_SerLines_csr
1752 is
1753 select
1754 plnt.header_id,
1755 plnt.line_id,
1756 plnt.quantity
1757 from pos_exasn_lines plnt
1758 where lls_code = 'SER';
1759 /*
1760 and (select count(distinct nvl(license_plate_number,'null'))
1761 from pos_exasn_serials pst
1762 where pst.line_id = plnt.line_id) >1;
1763 */
1764 l_ln_header_id number;
1765
1766 l_first_ser varchar2(1);
1767
1768 l_ln_quantity number;
1769 l_total_ser_qty number;
1770 l_ser_lpn varchar2(60);
1771 l_ser_lpn_sum number;
1772 l_new_line number;
1773 l_remain_qty number;
1774
1775 l_line_id number;
1776
1777 begin
1778 select max(line_id)+1 into l_new_line from pos_exasn_lines;
1779
1780 open l_SerLines_csr;
1781 loop
1782 fetch l_SerLines_csr into
1783 l_ln_header_id,
1784 l_line_id,
1785 l_ln_quantity;
1786 exit when l_SerLines_csr%NOTFOUND;
1787 l_first_Ser := 'T';
1788 open l_distinctLpn_ser_csr(l_ln_header_id, l_line_id);
1789 loop
1790 fetch l_distinctLpn_ser_csr into
1791 l_ser_lpn;
1792 exit when l_distinctLpn_ser_csr%NOTFOUND;
1793 if(l_first_ser = 'T') then
1794 l_first_ser := 'F';
1795 update pos_exasn_lines
1796 set license_plate_number = l_ser_lpn
1797 where line_id = l_line_id;
1798
1799 if(l_ser_lpn is null) then
1800 update pos_exasn_lines plnt
1801 set plnt.quantity =
1802 (select sum(quantity)
1803 from pos_exasn_serials pst
1804 where pst.header_id = plnt.header_id
1805 and pst.line_id = plnt.line_id
1806 and pst.license_plate_number is null)
1807 where plnt.line_id = l_line_id;
1808 else
1809 update pos_exasn_lines plnt
1810 set plnt.quantity =
1811 (select sum(quantity)
1812 from pos_exasn_serials pst
1813 where pst.header_id = plnt.header_id
1814 and pst.line_id = plnt.line_id
1815 and pst.license_plate_number = l_ser_lpn)
1816 where plnt.line_id = l_line_id;
1817 end if;
1818 else
1819 if(l_ser_lpn is null) then
1820 select sum(quantity)
1821 into l_ser_lpn_sum
1822 from pos_exasn_serials
1823 where header_id = l_ln_header_id
1824 and line_id = l_line_id
1825 and license_plate_number is null;
1826 else
1827 select sum(quantity)
1828 into l_ser_lpn_sum
1829 from pos_exasn_serials
1830 where header_id = l_ln_header_id
1831 and line_id = l_line_id
1832 and license_plate_number = l_ser_lpn;
1833 end if;
1834
1835 CreateNewLine(l_ser_lpn_sum, l_ser_lpn, l_new_line, l_line_id);
1836
1837 if(l_ser_lpn is null) then
1838 update pos_exasn_serials
1839 set line_id = l_new_line
1840 where line_id = l_line_id
1841 and license_plate_number is null;
1842 else
1843 update pos_exasn_serials
1844 set line_id = l_new_line
1845 where line_id = l_line_id
1846 and license_plate_number = l_ser_lpn;
1847 end if;
1848
1849 l_new_line := l_new_line + 1;
1850
1851 end if;
1852 end loop;
1853 close l_distinctLpn_ser_csr;
1854
1855 select sum(quantity)
1856 into l_total_ser_qty
1857 from pos_exasn_serials
1858 where header_id = l_ln_header_id
1859 and line_id >= l_line_id;
1860
1861 if(l_total_ser_qty < l_ln_quantity) then
1862 l_remain_qty := l_ln_quantity - l_total_ser_qty;
1863 --Create Extra Line for the remaining Quantity with NO Serial information
1864 CreateNewLine(l_remain_qty, null, l_new_line, l_line_id);
1865 l_new_line := l_new_line + 1;
1866 end if;
1867 end loop;
1868 close l_SerLines_csr;
1869 /*
1870 update pos_exasn_lines plnt
1871 set license_plate_number = (select distinct pst2.license_plate_number
1872 from pos_exasn_serials pst2
1873 where pst2.line_id = plnt.line_id
1874 and pst2.license_plate_number is not null)
1875 where lls_code = 'SER'
1876 and (select count(distinct pst.license_plate_number)
1877 from pos_exasn_serials pst
1878 where pst.line_id = plnt.line_id
1879 and pst.license_plate_number is not null) = 1;
1880 */
1881 end CreateRTI4Ser;
1882
1883
1884 procedure CreateRTI4Lpn
1885 is
1886 cursor l_distinctLpn_csr(x_header_id number, x_line_id number)
1887 is
1888 select
1889 distinct
1890 license_plate_number
1891 from pos_exasn_lpns
1892 where header_id = x_header_id
1893 and line_id = x_line_id
1894 and quantity is not null;
1895
1896 cursor l_LpnLines_ML_csr
1897 is
1898 select
1899 plnt.header_id,
1900 plnt.line_id,
1901 plnt.quantity
1902 from pos_exasn_lines plnt
1903 where lls_code = 'LPN';
1904 /*
1905 and (select count(distinct nvl(license_plate_number,'null'))
1906 from pos_exasn_lpns plpn
1907 where plpn.line_id = plnt.line_id
1908 and quantity is null) >1;
1909 */
1910 l_ln_header_id number;
1911
1912 l_first_lpn varchar2(1);
1913
1914 l_ln_quantity number;
1915 l_total_lpn_qty number;
1916 l_lpn varchar2(60);
1917 l_lpn_sum number;
1918 l_new_line number;
1919 l_remain_qty number;
1920
1921 l_line_id number;
1922
1923 begin
1924 select max(line_id)+1 into l_new_line from pos_exasn_lines;
1925
1926 open l_LpnLines_ML_csr;
1927 loop
1928 fetch l_LpnLines_ML_csr into
1929 l_ln_header_id,
1930 l_line_id,
1931 l_ln_quantity;
1932 exit when l_LpnLines_ML_csr%NOTFOUND;
1933 l_first_lpn := 'T';
1934 open l_distinctLpn_csr(l_ln_header_id, l_line_id);
1935 loop
1936 fetch l_distinctLpn_csr into
1937 l_lpn;
1938 exit when l_distinctLpn_csr%NOTFOUND;
1939 if(l_first_lpn = 'T') then
1940 l_first_lpn := 'F';
1941 update pos_exasn_lines
1942 set license_plate_number = l_lpn
1943 where line_id = l_line_id;
1944
1945 update pos_exasn_lines plnt
1946 set plnt.quantity = (select plpn.quantity
1947 from pos_exasn_lpns plpn
1948 where plpn.header_id = plnt.header_id
1949 and plpn.line_id = plnt.line_id
1950 and plpn.license_plate_number = l_lpn
1951 and plpn.quantity is not null)
1952 where plnt.line_id = l_line_id;
1953 else
1954
1955 --Get LPN quantity for the the new Line
1956 select plpn.quantity into l_lpn_sum
1957 from pos_exasn_lpns plpn
1958 where plpn.license_plate_number = l_lpn
1959 and plpn.header_id = l_ln_header_id
1960 and plpn.line_id = l_line_id
1961 and plpn.quantity is not null;
1962
1963 CreateNewLine(l_lpn_sum, l_lpn, l_new_line, l_line_id);
1964
1965 update pos_exasn_lpns
1966 set line_id = l_new_line
1967 where line_id = l_line_id
1968 and license_plate_number = l_lpn;
1969
1970
1971 l_new_line := l_new_line + 1;
1972
1973 end if;
1974 end loop;
1975 close l_distinctLpn_csr;
1976
1977 select sum(quantity)
1978 into l_total_lpn_qty
1979 from pos_exasn_lpns
1980 where header_id = l_ln_header_id
1981 and line_id >= l_line_id;
1982
1983 if(l_total_lpn_qty < l_ln_quantity) then
1984 l_remain_qty := l_ln_quantity - l_total_lpn_qty;
1985 --Create Extra Line for the remaining Quantity with NO Lpn information
1986 CreateNewLine(l_remain_qty, null, l_new_line, l_line_id);
1987 l_new_line := l_new_line + 1;
1988 end if;
1989 end loop;
1990 close l_LpnLines_ML_csr;
1991 /*
1992 update pos_exasn_lines plnt
1993 set license_plate_number = (select distinct plpn2.license_plate_number
1994 from pos_exasn_lpns plpn2
1995 where plpn2.line_id = plnt.line_id
1996 and plpn2.license_plate_number is not null)
1997 where lls_code = 'LPN'
1998 and (select count(distinct plpn.license_plate_number)
1999 from pos_exasn_lpns plpn
2000 where plpn.line_id = plnt.line_id
2001 and plpn.license_plate_number is not null) = 1;
2002 */
2003 end CreateRTI4Lpn;
2004
2005
2006 procedure CreateRTI4Lot
2007 is
2008 cursor l_distinctLpn_lot_csr(x_header_id number, x_line_id number)
2009 is
2010 select
2011 distinct
2012 license_plate_number
2013 from pos_exasn_lots
2014 where header_id = x_header_id
2015 and line_id = x_line_id;
2016
2017 cursor l_LotLines_ML_csr
2018 is
2019 select
2020 plnt.header_id,
2021 plnt.line_id,
2022 plnt.quantity
2023 from pos_exasn_lines plnt
2024 where lls_code in ('LOT','LAS');
2025 /*
2026 and (select count(distinct nvl(license_plate_number,'null'))
2027 from pos_exasn_lots plot
2028 where plot.line_id = plnt.line_id) >1;
2029 */
2030 l_ln_header_id number;
2031
2032 l_first_lot varchar2(1);
2033
2034 l_ln_quantity number;
2035 l_total_lot_qty number;
2036 l_lot_lpn varchar2(60);
2037 l_lot_lpn_sum number;
2038 l_new_line number;
2039 l_remain_qty number;
2040
2041 l_line_id number;
2042
2043 begin
2044 select max(line_id)+1 into l_new_line from pos_exasn_lines;
2045
2046 open l_LotLines_ML_csr;
2047 loop
2048 fetch l_LotLines_ML_csr into
2049 l_ln_header_id,
2050 l_line_id,
2051 l_ln_quantity;
2052 exit when l_LotLines_ML_csr%NOTFOUND;
2053 l_first_lot := 'T';
2054
2055 open l_distinctLpn_lot_csr(l_ln_header_id, l_line_id);
2056 loop
2057 fetch l_distinctLpn_lot_csr into
2058 l_lot_lpn;
2059 exit when l_distinctLpn_lot_csr%NOTFOUND;
2060
2061 if(l_first_lot = 'T') then
2062 l_first_lot := 'F';
2063
2064 update pos_exasn_lines
2065 set license_plate_number = l_lot_lpn
2066 where line_id = l_line_id;
2067
2068 if(l_lot_lpn is null) then
2069 update pos_exasn_lines plnt
2070 set plnt.quantity =
2071 (select sum(quantity)
2072 from pos_exasn_lots plot
2073 where plot.header_id = plnt.header_id
2074 and plot.line_id = plnt.line_id
2075 and plot.license_plate_number is null)
2076 where plnt.line_id = l_line_id;
2077 else
2078 update pos_exasn_lines plnt
2079 set plnt.quantity =
2080 (select sum(quantity)
2081 from pos_exasn_lots plot
2082 where plot.header_id = plnt.header_id
2083 and plot.line_id = plnt.line_id
2084 and plot.license_plate_number = l_lot_lpn)
2085 where plnt.line_id = l_line_id;
2086 end if;
2087 else
2088 if(l_lot_lpn is null) then
2089 select sum(quantity)
2090 into l_lot_lpn_sum
2091 from pos_exasn_lots
2092 where header_id = l_ln_header_id
2093 and line_id = l_line_id
2094 and license_plate_number is null;
2095 else
2096 select sum(quantity)
2097 into l_lot_lpn_sum
2098 from pos_exasn_lots
2099 where header_id = l_ln_header_id
2100 and line_id = l_line_id
2101 and license_plate_number = l_lot_lpn;
2102 end if;
2103
2104 CreateNewLine(l_lot_lpn_sum, l_lot_lpn, l_new_line, l_line_id);
2105
2106 if(l_lot_lpn is null) then
2107 update pos_exasn_lots
2108 set line_id = l_new_line
2109 where line_id = l_line_id
2110 and license_plate_number is null;
2111
2112 update pos_exasn_serials
2113 set line_id = l_new_line
2114 where lot_id in(select lot_id
2115 from pos_exasn_lots
2116 where line_id = l_new_line
2117 and license_plate_number is null);
2118
2119 else
2120 update pos_exasn_lots
2121 set line_id = l_new_line
2122 where line_id = l_line_id
2123 and license_plate_number = l_lot_lpn;
2124
2125 update pos_exasn_serials
2126 set line_id = l_new_line
2127 where lot_id in (select lot_id
2128 from pos_exasn_lots
2129 where line_id = l_new_line
2130 and license_plate_number = l_lot_lpn);
2131 end if;
2132
2133 l_new_line := l_new_line + 1;
2134
2135 end if;
2136 end loop;
2137 close l_distinctLpn_lot_csr;
2138
2139 select sum(quantity)
2140 into l_total_lot_qty
2141 from pos_exasn_lots
2142 where header_id = l_ln_header_id
2143 and line_id >= l_line_id;--All lines created at this moment, with line_id > l_line_id is for l_line_id
2144
2145 if(l_total_lot_qty < l_ln_quantity) then
2146
2147 l_remain_qty := l_ln_quantity - l_total_lot_qty;
2148 --Create Extra Line for the remaining Quantity with NO Lot information
2149
2150 CreateNewLine(l_remain_qty, null, l_new_line, l_line_id);
2151 l_new_line := l_new_line + 1;
2152 end if;
2153 end loop;
2154 close l_LotLines_ML_csr;
2155 /*
2156 update pos_exasn_lines plnt
2157 set license_plate_number = (select distinct plot2.license_plate_number
2158 from pos_exasn_lots plot2
2159 where plot2.line_id = plnt.line_id
2160 and plot2.license_plate_number is not null)
2161 where lls_code = 'LOT'
2162 and (select count(distinct plot.license_plate_number)
2163 from pos_exasn_lots plot
2164 where plot.line_id = plnt.line_id
2165 and plot.license_plate_number is not null) = 1;
2166 */
2167 end CreateRTI4Lot;
2168
2169 procedure InsertIntoLLS(x_return_status out nocopy varchar2,
2170 p_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
2171 p_error_pointer in out nocopy number)
2172 is
2173 cursor l_allLots_csr
2174 is
2175 select
2176 plot.lot_id,
2177 plot.transaction_interface_id,
2178 plot.lot_number,
2179 plot.quantity,
2180 plot.po_line_loc_id,
2181 plot.uom,
2182 plot.license_plate_number,
2183 plnt.lpn_group_id,
2184 plot.line_number,
2185 plot.product_transaction_id,
2186 plot.vendor_id,
2187 plot.grade_code,
2188 plot.origination_date,
2189 plot.date_code,
2190 plot.status_id,
2191 plot.change_date,
2192 plot.age,
2193 plot.retest_date,
2194 plot.maturity_date,
2195 plot.item_size,
2196 plot.color,
2197 plot.volume,
2198 plot.volume_uom,
2199 plot.place_of_origin,
2200 plot.best_by_date,
2201 plot.length,
2202 plot.length_uom,
2203 plot.recycled_content,
2204 plot.thickness,
2205 plot.thickness_uom,
2206 plot.width,
2207 plot.width_uom,
2208 plot.curl_wrinkle_fold,
2209 plot.supplier_lot_number,
2210 plot.territory_code,
2211 plot.vendor_name,
2212 plot.LOT_ATTRIBUTE_CATEGORY,
2213 plot.CATTRIBUTE1,
2214 plot.CATTRIBUTE2,
2215 plot.CATTRIBUTE3,
2216 plot.CATTRIBUTE4,
2217 plot.CATTRIBUTE5,
2218 plot.CATTRIBUTE6,
2219 plot.CATTRIBUTE7,
2220 plot.CATTRIBUTE8,
2221 plot.CATTRIBUTE9,
2222 plot.CATTRIBUTE10,
2223 plot.CATTRIBUTE11,
2224 plot.CATTRIBUTE12,
2225 plot.CATTRIBUTE13,
2226 plot.CATTRIBUTE14,
2227 plot.CATTRIBUTE15,
2228 plot.CATTRIBUTE16,
2229 plot.CATTRIBUTE17,
2230 plot.CATTRIBUTE18,
2231 plot.CATTRIBUTE19,
2232 plot.CATTRIBUTE20,
2233 plot.DATTRIBUTE1,
2234 plot.DATTRIBUTE2,
2235 plot.DATTRIBUTE3,
2236 plot.DATTRIBUTE4,
2237 plot.DATTRIBUTE5,
2238 plot.DATTRIBUTE6,
2239 plot.DATTRIBUTE7,
2240 plot.DATTRIBUTE8,
2241 plot.DATTRIBUTE9,
2242 plot.DATTRIBUTE10,
2243 plot.NATTRIBUTE1,
2244 plot.NATTRIBUTE2,
2245 plot.NATTRIBUTE3,
2246 plot.NATTRIBUTE4,
2247 plot.NATTRIBUTE5,
2248 plot.NATTRIBUTE6,
2249 plot.NATTRIBUTE7,
2250 plot.NATTRIBUTE8,
2251 plot.NATTRIBUTE9,
2252 plot.NATTRIBUTE10
2253 from pos_exasn_lots plot,
2254 pos_exasn_lines plnt
2255 where plot.line_id = plnt.line_id;
2256
2257 cursor l_allSerials_csr
2258 is
2259 select
2260 pst.from_serial,
2261 pst.to_serial,
2262 pst.po_line_loc_id,
2263 pst.transaction_interface_id,
2264 pst.license_plate_number,
2265 plnt.lpn_group_id,
2266 pst.line_number,
2267 pst.product_transaction_id,
2268 pst.origination_date,
2269 pst.status_id,
2270 pst.territory_code,
2271 pst.SERIAL_ATTRIBUTE_CATEGORY,
2272 pst.CATTRIBUTE1,
2273 pst.CATTRIBUTE2,
2274 pst.CATTRIBUTE3,
2275 pst.CATTRIBUTE4,
2276 pst.CATTRIBUTE5,
2277 pst.CATTRIBUTE6,
2278 pst.CATTRIBUTE7,
2279 pst.CATTRIBUTE8,
2280 pst.CATTRIBUTE9,
2281 pst.CATTRIBUTE10,
2282 pst.CATTRIBUTE11,
2283 pst.CATTRIBUTE12,
2284 pst.CATTRIBUTE13,
2285 pst.CATTRIBUTE14,
2286 pst.CATTRIBUTE15,
2287 pst.CATTRIBUTE16,
2288 pst.CATTRIBUTE17,
2289 pst.CATTRIBUTE18,
2290 pst.CATTRIBUTE19,
2291 pst.CATTRIBUTE20,
2292 pst.DATTRIBUTE1,
2293 pst.DATTRIBUTE2,
2294 pst.DATTRIBUTE3,
2295 pst.DATTRIBUTE4,
2296 pst.DATTRIBUTE5,
2297 pst.DATTRIBUTE6,
2298 pst.DATTRIBUTE7,
2299 pst.DATTRIBUTE8,
2300 pst.DATTRIBUTE9,
2301 pst.DATTRIBUTE10,
2302 pst.NATTRIBUTE1,
2303 pst.NATTRIBUTE2,
2304 pst.NATTRIBUTE3,
2305 pst.NATTRIBUTE4,
2306 pst.NATTRIBUTE5,
2307 pst.NATTRIBUTE6,
2308 pst.NATTRIBUTE7,
2309 pst.NATTRIBUTE8,
2310 pst.NATTRIBUTE9,
2311 pst.NATTRIBUTE10
2312 from pos_exasn_serials pst,
2313 pos_exasn_lines plnt
2314 where pst.line_id = plnt.line_id;
2315
2316
2317 cursor l_allLpns_csr
2318 is
2319 select
2320 plpn.license_plate_number,
2321 plpn.po_line_loc_id,
2322 plpn.parent_lpn,
2323 plnt.lpn_group_id,
2324 plpn.line_number
2325 from pos_exasn_lpns plpn,
2326 pos_exasn_lines plnt
2327 where plnt.line_id = plpn.line_id;
2328 l_txn_intf_id number;
2329 l_ser_intf_id number;
2330 l_lot_number pos_exasn_lots.lot_number%type;
2331 l_vendor_id pos_exasn_lots.vendor_id%type;
2332 l_grade_code pos_exasn_lots.grade_code%type;
2333 l_origination_date pos_exasn_lots.origination_date%type;
2334 l_date_code pos_exasn_lots.date_code%type;
2335 l_status_id pos_exasn_lots.status_id%type;
2336 l_change_date pos_exasn_lots.change_date%type;
2337 l_age pos_exasn_lots.age%type;
2338 l_retest_date pos_exasn_lots.retest_date%type;
2339 l_maturity_date pos_exasn_lots.maturity_date%type;
2340 l_item_size pos_exasn_lots.item_size%type;
2341 l_color pos_exasn_lots.color%type;
2342 l_volume pos_exasn_lots.volume%type;
2343 l_volume_uom pos_exasn_lots.volume_uom%type;
2344 l_place_of_origin pos_exasn_lots.place_of_origin%type;
2345 l_best_by_date pos_exasn_lots.best_by_date%type;
2346 l_length pos_exasn_lots.length%type;
2347 l_length_uom pos_exasn_lots.length_uom%type;
2348 l_recycled_content pos_exasn_lots.recycled_content%type;
2349 l_thickness pos_exasn_lots.thickness%type;
2350 l_thickness_uom pos_exasn_lots.thickness_uom%type;
2351 l_width pos_exasn_lots.width%type;
2352 l_width_uom pos_exasn_lots.width_uom%type;
2353 l_curl_wrinkle_fold pos_exasn_lots.curl_wrinkle_fold%type;
2354 l_supplier_lot_number pos_exasn_lots.supplier_lot_number%type;
2355 l_territory_code pos_exasn_lots.territory_code%type;
2356 l_vendor_name pos_exasn_lots.vendor_name%type;
2357 l_qty number;
2358 l_po_line_loc_id number;
2359 l_pdt_txn_id number;
2360 l_fm_serial pos_exasn_serials.from_serial%type;
2361 l_to_serial pos_exasn_serials.to_serial%type;
2362 l_ser_origination_date pos_exasn_serials.origination_date%type;
2363 l_ser_status_id pos_exasn_serials.status_id%type;
2364 l_ser_territory_code pos_exasn_serials.territory_code%type;
2365 l_lpn pos_exasn_lpns.license_plate_number%type;
2366 l_parent_lpn pos_exasn_lpns.parent_lpn%type;
2367 l_lpn_Group_id number;
2368
2369 l_lot_status varchar2(1);
2370 l_lot_msg_data varchar2(2000);
2371 l_lot_msg_count number;
2372
2373 l_ser_status varchar2(1);
2374 l_ser_msg_count number;
2375 l_ser_msg_data varchar2(2000);
2376
2377 l_lpn_status varchar2(1);
2378 l_lpn_msg_count number;
2379 l_lpn_msg_data varchar2(2000);
2380 l_serial_txn_temp_id number;
2381 l_txn_uom pos_exasn_lots.uom%type;
2382 l_lot_id number;
2383
2384 l_25errors exception;
2385 l_lot_ln number;
2386 l_lpn_ln number;
2387 l_ser_ln number;
2388 l_LOT_ATTRIBUTE_CATEGORY VARCHAR2(60);
2389 l_SERIAL_ATTRIBUTE_CATEGORY VARCHAR2(60);
2390 l_CATTRIBUTE1 VARCHAR2(2000);
2391 l_CATTRIBUTE2 VARCHAR2(2000);
2392 l_CATTRIBUTE3 VARCHAR2(2000);
2393 l_CATTRIBUTE4 VARCHAR2(2000);
2394 l_CATTRIBUTE5 VARCHAR2(2000);
2395 l_CATTRIBUTE6 VARCHAR2(2000);
2396 l_CATTRIBUTE7 VARCHAR2(2000);
2397 l_CATTRIBUTE8 VARCHAR2(2000);
2398 l_CATTRIBUTE9 VARCHAR2(2000);
2399 l_CATTRIBUTE10 VARCHAR2(2000);
2400 l_CATTRIBUTE11 VARCHAR2(2000);
2401 l_CATTRIBUTE12 VARCHAR2(2000);
2402 l_CATTRIBUTE13 VARCHAR2(2000);
2403 l_CATTRIBUTE14 VARCHAR2(2000);
2404 l_CATTRIBUTE15 VARCHAR2(2000);
2405 l_CATTRIBUTE16 VARCHAR2(2000);
2406 l_CATTRIBUTE17 VARCHAR2(2000);
2407 l_CATTRIBUTE18 VARCHAR2(2000);
2408 l_CATTRIBUTE19 VARCHAR2(2000);
2409 l_CATTRIBUTE20 VARCHAR2(2000);
2410 l_DATTRIBUTE1 DATE;
2411 l_DATTRIBUTE2 DATE;
2412 l_DATTRIBUTE3 DATE;
2413 l_DATTRIBUTE4 DATE;
2414 l_DATTRIBUTE5 DATE;
2415 l_DATTRIBUTE6 DATE;
2416 l_DATTRIBUTE7 DATE;
2417 l_DATTRIBUTE8 DATE;
2418 l_DATTRIBUTE9 DATE;
2419 l_DATTRIBUTE10 DATE;
2420 l_NATTRIBUTE1 NUMBER;
2421 l_NATTRIBUTE2 NUMBER;
2422 l_NATTRIBUTE3 NUMBER;
2423 l_NATTRIBUTE4 NUMBER;
2424 l_NATTRIBUTE5 NUMBER;
2425 l_NATTRIBUTE6 NUMBER;
2426 l_NATTRIBUTE7 NUMBER;
2427 l_NATTRIBUTE8 NUMBER;
2428 l_NATTRIBUTE9 NUMBER;
2429 l_NATTRIBUTE10 NUMBER;
2430 begin
2431
2432 open l_allLots_csr;
2433 loop
2434 fetch l_allLots_csr into
2435 l_lot_id,
2436 l_txn_intf_id,
2437 l_lot_number,
2438 l_qty,
2439 l_po_line_loc_id,
2440 l_txn_uom,
2441 l_lpn,
2442 l_lpn_group_id,
2443 l_lot_ln,
2444 l_pdt_txn_id,
2445 l_vendor_id,
2446 l_grade_code,
2447 l_origination_date,
2448 l_date_code,
2449 l_status_id,
2450 l_change_date,
2451 l_age,
2452 l_retest_date,
2453 l_maturity_date,
2454 l_item_size,
2455 l_color,
2456 l_volume,
2457 l_volume_uom,
2458 l_place_of_origin,
2459 l_best_by_date,
2460 l_length,
2461 l_length_uom,
2462 l_recycled_content,
2463 l_thickness,
2464 l_thickness_uom,
2465 l_width,
2466 l_width_uom,
2467 l_curl_wrinkle_fold,
2468 l_supplier_lot_number,
2469 l_territory_code,
2470 l_vendor_name,
2471 l_LOT_ATTRIBUTE_CATEGORY,
2472 l_CATTRIBUTE1,
2473 l_CATTRIBUTE2,
2474 l_CATTRIBUTE3,
2475 l_CATTRIBUTE4,
2476 l_CATTRIBUTE5,
2477 l_CATTRIBUTE6,
2478 l_CATTRIBUTE7,
2479 l_CATTRIBUTE8,
2480 l_CATTRIBUTE9,
2481 l_CATTRIBUTE10,
2482 l_CATTRIBUTE11,
2483 l_CATTRIBUTE12,
2484 l_CATTRIBUTE13,
2485 l_CATTRIBUTE14,
2486 l_CATTRIBUTE15,
2487 l_CATTRIBUTE16,
2488 l_CATTRIBUTE17,
2489 l_CATTRIBUTE18,
2490 l_CATTRIBUTE19,
2491 l_CATTRIBUTE20,
2492 l_DATTRIBUTE1,
2493 l_DATTRIBUTE2,
2494 l_DATTRIBUTE3,
2495 l_DATTRIBUTE4,
2496 l_DATTRIBUTE5,
2497 l_DATTRIBUTE6,
2498 l_DATTRIBUTE7,
2499 l_DATTRIBUTE8,
2500 l_DATTRIBUTE9,
2501 l_DATTRIBUTE10,
2502 l_NATTRIBUTE1,
2503 l_NATTRIBUTE2,
2504 l_NATTRIBUTE3,
2505 l_NATTRIBUTE4,
2506 l_NATTRIBUTE5,
2507 l_NATTRIBUTE6,
2508 l_NATTRIBUTE7,
2509 l_NATTRIBUTE8,
2510 l_NATTRIBUTE9,
2511 l_NATTRIBUTE10;
2512 exit when l_allLots_csr%NOTFOUND;
2513
2514 pos_asn_create_pvt.insert_mtli(
2515 p_api_version => 1.0
2516 , x_return_status => l_lot_status
2517 , x_msg_count => l_lot_msg_count
2518 , x_msg_data => l_lot_msg_data
2519 , p_transaction_interface_id => l_txn_intf_id
2520 , p_lot_number => l_lot_number
2521 , p_transaction_quantity => l_qty
2522 , p_transaction_uom => l_txn_uom
2523 , p_po_line_loc_id => l_po_line_loc_id
2524 , x_serial_transaction_temp_id => l_serial_txn_temp_id
2525 , p_product_transaction_id => l_pdt_txn_id
2526 , p_vendor_id => l_vendor_id
2527 , p_grade_Code => l_grade_code
2528 , p_origination_date => l_origination_date
2529 , p_date_code => l_date_code
2530 , p_status_id => l_status_id
2531 , p_change_date => l_change_date
2532 , p_age => l_age
2533 , p_retest_date => l_retest_date
2534 , p_maturity_date => l_maturity_date
2535 , p_item_size => l_item_size
2536 , p_color => l_color
2537 , p_volume => l_volume
2538 , p_volume_uom => l_volume_uom
2539 , p_place_of_origin => l_place_of_origin
2540 , p_best_by_date => l_best_by_date
2541 , p_length => l_length
2542 , p_length_uom => l_length_uom
2543 , p_recycled_content => l_recycled_content
2544 , p_thickness => l_thickness
2545 , p_thickness_uom => l_thickness_uom
2546 , p_width => l_width
2547 , p_width_uom => l_width_uom
2548 , p_curl_wrinkle_fold => l_curl_wrinkle_fold
2549 , p_supplier_lot_number => l_supplier_lot_number
2550 , p_territory_code => l_territory_code
2551 , p_vendor_name => l_vendor_name
2552 , p_lot_attribute_category => l_LOT_ATTRIBUTE_CATEGORY
2553 , p_c_attribute1 => l_CATTRIBUTE1
2554 , p_c_attribute2 => l_CATTRIBUTE2
2555 , p_c_attribute3 => l_CATTRIBUTE3
2556 , p_c_attribute4 => l_CATTRIBUTE4
2557 , p_c_attribute5 => l_CATTRIBUTE5
2558 , p_c_attribute6 => l_CATTRIBUTE6
2559 , p_c_attribute7 => l_CATTRIBUTE7
2560 , p_c_attribute8 => l_CATTRIBUTE8
2561 , p_c_attribute9 => l_CATTRIBUTE9
2562 , p_c_attribute10 => l_CATTRIBUTE10
2563 , p_c_attribute11 => l_CATTRIBUTE11
2564 , p_c_attribute12 => l_CATTRIBUTE12
2565 , p_c_attribute13 => l_CATTRIBUTE13
2566 , p_c_attribute14 => l_CATTRIBUTE14
2567 , p_c_attribute15 => l_CATTRIBUTE15
2568 , p_c_attribute16 => l_CATTRIBUTE16
2569 , p_c_attribute17 => l_CATTRIBUTE17
2570 , p_c_attribute18 => l_CATTRIBUTE18
2571 , p_c_attribute19 => l_CATTRIBUTE19
2572 , p_c_attribute20 => l_CATTRIBUTE20
2573 , p_d_attribute1 => l_DATTRIBUTE1
2574 , p_d_attribute2 => l_DATTRIBUTE2
2575 , p_d_attribute3 => l_DATTRIBUTE3
2576 , p_d_attribute4 => l_DATTRIBUTE4
2577 , p_d_attribute5 => l_DATTRIBUTE5
2578 , p_d_attribute6 => l_DATTRIBUTE6
2579 , p_d_attribute7 => l_DATTRIBUTE7
2580 , p_d_attribute8 => l_DATTRIBUTE8
2581 , p_d_attribute9 => l_DATTRIBUTE9
2582 , p_d_attribute10 => l_DATTRIBUTE10
2583 , p_n_attribute1 => l_NATTRIBUTE1
2584 , p_n_attribute2 => l_NATTRIBUTE2
2585 , p_n_attribute3 => l_NATTRIBUTE3
2586 , p_n_attribute4 => l_NATTRIBUTE4
2587 , p_n_attribute5 => l_NATTRIBUTE5
2588 , p_n_attribute6 => l_NATTRIBUTE6
2589 , p_n_attribute7 => l_NATTRIBUTE7
2590 , p_n_attribute8 => l_NATTRIBUTE8
2591 , p_n_attribute9 => l_NATTRIBUTE9
2592 , p_n_attribute10 => l_NATTRIBUTE10
2593 );
2594
2595 if(l_lot_status <> FND_API.G_RET_STS_SUCCESS) then
2596 if(InsertError(p_error_tbl, 'Error while inserting Lot at line '||l_lot_ln, p_error_pointer)=1) then
2597 raise l_25errors;
2598 end if;
2599 end if;
2600
2601
2602 update pos_exasn_lots
2603 set TRANSACTION_INTERFACE_ID = l_txn_intf_id, serial_transaction_temp_id = l_serial_txn_temp_id
2604 where lot_id = l_lot_id;
2605
2606 if(l_lpn is not null) then
2607 pos_asn_create_pvt.insert_wlpni
2608 ( p_api_version => 1.0
2609 , x_return_status => l_lpn_status
2610 , x_msg_count => l_lpn_msg_count
2611 , x_msg_data => l_lpn_msg_data
2612 , p_po_line_loc_id => l_po_line_loc_id
2613 , p_license_plate_number => l_lpn
2614 , p_LPN_GROUP_ID => l_lpn_group_id
2615 , p_PARENT_LICENSE_PLATE_NUMBER => null
2616 );
2617 if(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
2618 if(InsertError(p_error_tbl, 'Error while inserting LPN at line '||l_lot_ln, p_error_pointer)=1) then
2619 raise l_25errors;
2620 end if;
2621 end if;
2622 end if;
2623
2624
2625
2626 end loop;
2627 close l_allLots_csr;
2628
2629 --update
2630 update pos_exasn_serials pst
2631 set pst.transaction_interface_id =
2632 (select plot.serial_transaction_temp_id
2633 from pos_exasn_lots plot
2634 where plot.lot_id = pst.lot_id);
2635
2636
2637
2638 open l_allSerials_csr;
2639 loop
2640 fetch l_allSerials_csr into
2641 l_fm_serial,
2642 l_to_serial,
2643 l_po_line_loc_id,
2644 l_ser_intf_id,
2645 l_lpn,
2646 l_lpn_group_id,
2647 l_ser_ln,
2648 l_pdt_txn_id,
2649 l_ser_origination_date,
2650 l_ser_status_id,
2651 l_ser_territory_code,
2652 l_SERIAL_ATTRIBUTE_CATEGORY,
2653 l_CATTRIBUTE1,
2654 l_CATTRIBUTE2,
2655 l_CATTRIBUTE3,
2656 l_CATTRIBUTE4,
2657 l_CATTRIBUTE5,
2658 l_CATTRIBUTE6,
2659 l_CATTRIBUTE7,
2660 l_CATTRIBUTE8,
2661 l_CATTRIBUTE9,
2662 l_CATTRIBUTE10,
2663 l_CATTRIBUTE11,
2664 l_CATTRIBUTE12,
2665 l_CATTRIBUTE13,
2666 l_CATTRIBUTE14,
2667 l_CATTRIBUTE15,
2668 l_CATTRIBUTE16,
2669 l_CATTRIBUTE17,
2670 l_CATTRIBUTE18,
2671 l_CATTRIBUTE19,
2672 l_CATTRIBUTE20,
2673 l_DATTRIBUTE1,
2674 l_DATTRIBUTE2,
2675 l_DATTRIBUTE3,
2676 l_DATTRIBUTE4,
2677 l_DATTRIBUTE5,
2678 l_DATTRIBUTE6,
2679 l_DATTRIBUTE7,
2680 l_DATTRIBUTE8,
2681 l_DATTRIBUTE9,
2682 l_DATTRIBUTE10,
2683 l_NATTRIBUTE1,
2684 l_NATTRIBUTE2,
2685 l_NATTRIBUTE3,
2686 l_NATTRIBUTE4,
2687 l_NATTRIBUTE5,
2688 l_NATTRIBUTE6,
2689 l_NATTRIBUTE7,
2690 l_NATTRIBUTE8,
2691 l_NATTRIBUTE9,
2692 l_NATTRIBUTE10;
2693 exit when l_allSerials_csr%NOTFOUND;
2694
2695
2696 pos_asn_create_pvt.insert_msni (
2697 p_api_version => 1.0
2698 , x_return_status => l_ser_status
2699 , x_msg_count => l_ser_msg_count
2700 , x_msg_data => l_ser_msg_data
2701 , p_transaction_interface_id => l_ser_intf_id
2702 , p_fm_serial_number => l_fm_serial
2703 , p_to_serial_number => l_to_serial
2704 , p_po_line_loc_id => l_po_line_loc_id
2705 , p_product_transaction_id => l_pdt_txn_id
2706 , p_origination_date => l_ser_origination_date
2707 , p_status_id => l_ser_status_id
2708 , p_territory_code => l_ser_territory_code
2709 , p_serial_attribute_category => l_SERIAL_ATTRIBUTE_CATEGORY
2710 , p_c_attribute1 => l_CATTRIBUTE1
2711 , p_c_attribute2 => l_CATTRIBUTE2
2712 , p_c_attribute3 => l_CATTRIBUTE3
2713 , p_c_attribute4 => l_CATTRIBUTE4
2714 , p_c_attribute5 => l_CATTRIBUTE5
2715 , p_c_attribute6 => l_CATTRIBUTE6
2716 , p_c_attribute7 => l_CATTRIBUTE7
2717 , p_c_attribute8 => l_CATTRIBUTE8
2718 , p_c_attribute9 => l_CATTRIBUTE9
2719 , p_c_attribute10 => l_CATTRIBUTE10
2720 , p_c_attribute11 => l_CATTRIBUTE11
2721 , p_c_attribute12 => l_CATTRIBUTE12
2722 , p_c_attribute13 => l_CATTRIBUTE13
2723 , p_c_attribute14 => l_CATTRIBUTE14
2724 , p_c_attribute15 => l_CATTRIBUTE15
2725 , p_c_attribute16 => l_CATTRIBUTE16
2726 , p_c_attribute17 => l_CATTRIBUTE17
2727 , p_c_attribute18 => l_CATTRIBUTE18
2728 , p_c_attribute19 => l_CATTRIBUTE19
2729 , p_c_attribute20 => l_CATTRIBUTE20
2730 , p_d_attribute1 => l_DATTRIBUTE1
2731 , p_d_attribute2 => l_DATTRIBUTE2
2732 , p_d_attribute3 => l_DATTRIBUTE3
2733 , p_d_attribute4 => l_DATTRIBUTE4
2734 , p_d_attribute5 => l_DATTRIBUTE5
2735 , p_d_attribute6 => l_DATTRIBUTE6
2736 , p_d_attribute7 => l_DATTRIBUTE7
2737 , p_d_attribute8 => l_DATTRIBUTE8
2738 , p_d_attribute9 => l_DATTRIBUTE9
2739 , p_d_attribute10 => l_DATTRIBUTE10
2740 , p_n_attribute1 => l_NATTRIBUTE1
2741 , p_n_attribute2 => l_NATTRIBUTE2
2742 , p_n_attribute3 => l_NATTRIBUTE3
2743 , p_n_attribute4 => l_NATTRIBUTE4
2744 , p_n_attribute5 => l_NATTRIBUTE5
2745 , p_n_attribute6 => l_NATTRIBUTE6
2746 , p_n_attribute7 => l_NATTRIBUTE7
2747 , p_n_attribute8 => l_NATTRIBUTE8
2748 , p_n_attribute9 => l_NATTRIBUTE9
2749 , p_n_attribute10 => l_NATTRIBUTE10
2750 );
2751
2752
2753 if(l_ser_status <> FND_API.G_RET_STS_SUCCESS) then
2754 if(InsertError(p_error_tbl, 'Error while inserting Serial at line '||l_ser_ln, p_error_pointer)=1) then
2755 raise l_25errors;
2756 end if;
2757 end if;
2758
2759 if(l_lpn is not null) then
2760 pos_asn_create_pvt.insert_wlpni
2761 ( p_api_version => 1.0
2762 , x_return_status => l_lpn_status
2763 , x_msg_count => l_lpn_msg_count
2764 , x_msg_data => l_lpn_msg_data
2765 , p_po_line_loc_id => l_po_line_loc_id
2766 , p_license_plate_number => l_lpn
2767 , p_LPN_GROUP_ID => l_lpn_group_id
2768 , p_PARENT_LICENSE_PLATE_NUMBER => null
2769 );
2770 if(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
2771 if(InsertError(p_error_tbl, 'Error while inserting Lpn at line '||l_ser_ln, p_error_pointer)=1) then
2772 raise l_25errors;
2773 end if;
2774 end if;
2775 end if;
2776 end loop;
2777 close l_allSerials_csr;
2778
2779 open l_allLpns_csr;
2780 loop
2781 fetch l_allLpns_csr into
2782 l_lpn,
2783 l_po_line_loc_id,
2784 l_parent_lpn,
2785 l_lpn_group_id,
2786 l_lpn_ln;
2787 exit when l_allLpns_csr%NOTFOUND;
2788
2789 pos_asn_create_pvt.insert_wlpni
2790 ( p_api_version => 1.0
2791 , x_return_status => l_lpn_status
2792 , x_msg_count => l_lpn_msg_count
2793 , x_msg_data => l_lpn_msg_data
2794 , p_po_line_loc_id => l_po_line_loc_id
2795 , p_license_plate_number => l_lpn
2796 , p_LPN_GROUP_ID => l_lpn_group_id
2797 , p_PARENT_LICENSE_PLATE_NUMBER => l_parent_lpn
2798 );
2799
2800 if(l_lpn_status <> FND_API.G_RET_STS_SUCCESS) then
2801 if(InsertError(p_error_tbl, 'Error while inserting Lpn at line '||l_lpn_ln, p_error_pointer)=1) then
2802 raise l_25errors;
2803 end if;
2804 end if;
2805
2806 end loop;
2807 close l_allLpns_csr;
2808
2809
2810 exception when l_25errors then
2811 null;
2812 when others then
2813 if(InsertError(p_error_tbl, 'Unexpected Error in InsertIntoLLS', p_error_pointer)=1) then
2814 null;
2815 end if;
2816 end InsertIntoLLS;
2817
2818
2819 function InsertError(p_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
2820 p_error_msg in varchar2,
2821 p_error_index in out NOCOPY number)
2822 return number
2823 is
2824 begin
2825 p_error_tbl.extend(1);
2826 p_error_tbl(p_error_index) := p_error_msg;
2827 p_error_index := p_error_index + 1;
2828
2829 if(p_error_index > 25) then
2830 return 1;
2831 else
2832 return 0;
2833 end if;
2834 end InsertError;
2835
2836
2837 procedure FixHeadersAndLines(x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
2838 l_error_pointer in out NOCOPY number)
2839 is
2840 l_asn_header_id number;
2841 l_ex_header_id number;
2842 l_ex_vendor_id number;
2843 l_ex_vendor_site_id number;
2844 l_ex_ship_to_org_id number;
2845 l_ex_first varchar2(1);
2846 l_ex_currency_code varchar2(15);
2847
2848
2849 --CREATE EXTRA RHIs
2850 cursor l_findExtraRhi_csr
2851 is
2852 select HEADER_ID from
2853 (
2854 select
2855 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
2856 from pos_exasn_headers pht,
2857 pos_exasn_lines plnt
2858 where pht.header_id = plnt.header_id
2859 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
2860 )
2861 group by HEADER_ID
2862 having count(1) > 1;
2863
2864 cursor l_createExtraRhi_csr(x_header_id number) is
2865 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
2866 from pos_exasn_lines plnt
2867 where header_id = x_header_Id;
2868
2869 l_asn_asbn varchar2(10);
2870 l_early_exit exception;
2871 l_asbn_bad varchar2(1);
2872 l_error_ln number;
2873 cursor l_checkNotPaySite_csr is
2874 select peh.line_number
2875 from pos_exasn_headers peh,
2876 po_vendor_sites_all pvsa
2877 where pvsa.vendor_site_id = peh.vendor_site_id
2878 AND getvendorpaysiteid(peh.vendor_id,nvl(peh.vendor_site_id, -9999),peh.currency_code) IS null;
2879 --Refer the bug7338353 for more details
2880
2881 --H1: Freight Carrier Code
2882 l_fcc pos_exasn_headers.freight_carrier_code%type;
2883 cursor l_checkFreightCC_csr
2884 is
2885 select pht.line_number, pht.freight_carrier_code
2886 from pos_exasn_headers pht
2887 where pht.freight_carrier_code is not null
2888 and 0=
2889 (select count(*) from org_freight oft
2890 where nvl(oft.disable_date, sysdate) >= sysdate
2891 and oft.freight_code = pht.freight_carrier_code
2892 and organization_id = pht.ship_to_org_id
2893 );
2894
2895 --H10: Bad Ship From Location
2896 l_ship_from_loc pos_exasn_headers.ship_from_location_code%type;
2897 cursor l_shipFrom_Loc_csr is
2898 select pht.line_number,pht.ship_from_location_code
2899 from pos_exasn_headers pht
2900 where pht.ship_from_location_code is not null
2901 and not exists (
2902 select 1 from hz_party_sites ps,hz_party_site_uses psu,po_vendors pov
2903 where ps.party_site_id = psu.party_site_id
2904 and psu.site_use_type = 'SUPPLIER_SHIP_FROM'
2905 and ps.party_id = pov.party_id
2906 and pov.vendor_id= pht.vendor_id
2907 and substr(ps.party_site_number,1,instr(ps.party_site_number,'|')-1) = pht.ship_from_location_code);
2908
2909
2910 begin
2911
2912 l_asbn_bad := 'F';
2913
2914 select max(header_id) into l_asn_header_id from pos_exasn_headers;
2915
2916 select decode(count(1),0,'ASN','ASBN')
2917 into l_asn_asbn
2918 from pos_exasn_headers
2919 where invoice_number is not null;
2920
2921
2922
2923
2924 open l_findExtraRhi_csr;
2925 loop
2926 fetch l_findExtraRhi_csr into
2927 l_ex_header_id;
2928 exit when l_findExtraRhi_csr%NOTFOUND;
2929 l_ex_first := 'T';
2930 open l_createExtraRhi_csr(l_ex_header_id);
2931 loop
2932 fetch l_createExtraRhi_csr into
2933 l_ex_vendor_id,
2934 l_ex_ship_to_org_id,
2935 l_ex_vendor_site_id,
2936 l_ex_currency_code;
2937 exit when l_createExtraRhi_csr%NOTFOUND;
2938 if(l_ex_vendor_site_id = -9999) then
2939 l_ex_vendor_site_id := null;
2940 end if;
2941 if(l_ex_first = 'T') then
2942 l_ex_first := 'F';
2943 else
2944 if(l_asn_asbn = 'ASBN') then
2945 l_asbn_bad := 'T';
2946 select line_number
2947 into l_error_ln
2948 from pos_exasn_headers
2949 where header_id = l_ex_header_id;
2950
2951 fnd_message.set_name('POS','POS_EXASN_ASBN_XHDR');
2952 fnd_message.set_token('LINE_NUM',l_error_ln);
2953 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
2954 raise l_early_exit;
2955 end if;
2956 end if;
2957
2958 l_asn_header_id := l_asn_header_id + 1;
2959 CreateNewHeader(l_asn_header_id, l_ex_header_id, l_ex_vendor_id, l_ex_ship_to_org_id, l_ex_vendor_site_id );
2960
2961 update pos_exasn_lines
2962 set header_id = l_asn_header_id
2963 where header_id = l_ex_header_id
2964 and vendor_id = l_ex_vendor_id
2965 and ship_to_org_id = l_ex_ship_to_org_id
2966 and vendor_site_id = l_ex_vendor_site_id;
2967 end if;
2968 end loop;
2969 close l_createExtraRhi_csr;
2970 end loop;
2971 close l_findExtraRhi_csr;
2972
2973 if(l_asbn_bad = 'T') then
2974 raise l_early_exit;
2975 end if;
2976
2977 update pos_exasn_headers pht
2978 set (
2979 pht.vendor_id,
2980 pht.ship_to_org_id,
2981 pht.vendor_site_id,
2982 pht.currency_code,
2983 pht.rate,
2984 pht.rate_type,
2985 pht.rate_date
2986 )
2987 =
2988 (select
2989 plnt.vendor_id,
2990 plnt.ship_to_org_id,
2991 plnt.vendor_site_id,
2992 plnt.currency_code,
2993 plnt.rate,
2994 plnt.rate_type,
2995 plnt.rate_date
2996 from pos_exasn_lines plnt
2997 where plnt.header_id = pht.header_id
2998 and plnt.line_id =
2999 (select min(plnt2.line_id)
3000 from pos_exasn_lines plnt2
3001 where plnt2.header_id = pht.header_id)
3002 );
3003
3004 --H1 (Has to be done after PHT.SHIP_TO_ORG_ID is populated)
3005 open l_checkFreightCC_csr;
3006 loop
3007 fetch l_checkFreightCC_csr into l_error_ln, l_fcc;
3008 exit when l_checkFreightCC_csr%NOTFOUND;
3009 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
3010 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_FCCODE'));
3011 fnd_message.set_token('LOV_VALUE',l_fcc);
3012 fnd_message.set_token('LINE_NUM',l_error_ln);
3013 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3014 raise l_early_exit;
3015 end if;
3016 end loop;
3017 close l_checkFreightCC_csr;
3018
3019 --H10 ShipFromLocationCode Validation
3020 open l_shipFrom_Loc_csr;
3021 loop
3022 fetch l_shipFrom_Loc_csr into l_error_ln,l_ship_from_loc;
3023 exit when l_shipFrom_Loc_csr%NOTFOUND;
3024 fnd_message.set_name('POS','POS_EXASN_INVALID_LOV');
3025 fnd_message.set_token('LOV_NAME',fnd_message.get_string('POS','POS_EXASN_SHP_FROM'));
3026 fnd_message.set_token('LOV_VALUE',l_ship_from_loc);
3027 fnd_message.set_token('LINE_NUM',l_error_ln);
3028 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3029 raise l_early_exit;
3030 end if;
3031 end loop;
3032 close l_shipFrom_Loc_csr;
3033
3034
3035
3036 --Check to make sure vendor_site_id is pay site
3037 if(l_asn_asbn = 'ASBN') then
3038 open l_checkNotPaySite_csr;
3039 loop
3040 fetch l_checkNotPaySite_csr into l_error_ln;
3041 exit when l_checkNotPaySite_csr%notfound;
3042 fnd_message.set_name('POS','POS_EXASN_NOTPAYSITE');
3043 fnd_message.set_token('LINE_NUM',l_error_ln);
3044 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3045 raise l_early_exit;
3046 end if;
3047 end loop;
3048 close l_checkNotPaySite_csr;
3049 end if;
3050
3051
3052
3053 -- Update Payment Term ID, if any
3054 update pos_exasn_headers pht
3055 set payment_term_id =
3056 (select atv.term_id
3057 from ap_terms_val_v atv
3058 where atv.name = pht.payment_terms)
3059 where payment_terms is not null;
3060 -------------------
3061
3062
3063
3064
3065 update pos_exasn_lines plnt
3066 set (header_interface_id, group_id, expected_receipt_date) = (select pht.header_interface_id, pht.group_id, pht.expected_receipt_date
3067 from pos_exasn_headers pht
3068 where pht.header_id = plnt.header_id);
3069 exception when l_early_exit then
3070 null;
3071 end FixHeadersAndLines;
3072
3073 procedure CreateNewLine(p_qty in number, p_lpn in varchar2, p_line_id in number, p_old_ln in number)
3074 is
3075 l_intf_txn_id number;
3076 begin
3077 insert into pos_exasn_lines(line_id, quantity, license_plate_number) values(p_line_id, p_qty, p_lpn );
3078 select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_intf_txn_id from dual;
3079 update pos_exasn_lines
3080 set (
3081 PRIMARY_UOM,
3082 LPN_GROUP_ID,
3083 EXPECTED_RECEIPT_DATE,
3084 HEADER_INTERFACE_ID,
3085 INTERFACE_TRANSACTION_ID,
3086 GROUP_ID,
3087 HEADER_ID,
3088 OPERATING_UNIT,
3089 PO_NUMBER,
3090 PO_REVISION,
3091 PO_RELEASE_NUM,
3092 PO_LINE,
3093 PO_SHIPMENT,
3094 ORG_ID,
3095 PO_HEADER_ID,
3096 PO_RELEASE_ID,
3097 PO_LINE_ID,
3098 PO_LINE_LOCATION_ID,
3099 VENDOR_ID,
3100 VENDOR_SITE_ID,
3101 VENDOR_CONTACT_ID,
3102 SHIP_TO_ORG_ID,
3103 VENDOR_NAME,
3104 VENDOR_SITE_CODE,
3105 RATE_TYPE,
3106 RATE,
3107 RATE_DATE,
3108 ITEM_ID,
3109 ITEM_REVISION,
3110 UNIT_PRICE,
3111 CURRENCY_CODE,
3112 VENDOR_PRODUCT_NUM,
3113 UOM,
3114 BILL_OF_LADING,
3115 PACKING_SLIP,
3116 NUM_OF_CONTAINERS,
3117 WAYBILL_NUM,
3118 BARCODE_LABEL,
3119 COUNTRY_OF_ORIGIN,
3120 CONTAINER_NUMBER,
3121 TRUCK_NUMBER,
3122 VENDOR_LOT,
3123 COMMENTS,
3124 LINE_NUMBER,
3125 ATTRIBUTE_CATEGORY,
3126 ATTRIBUTE1,
3127 ATTRIBUTE2,
3128 ATTRIBUTE3,
3129 ATTRIBUTE4,
3130 ATTRIBUTE5,
3131 ATTRIBUTE6,
3132 ATTRIBUTE7,
3133 ATTRIBUTE8,
3134 ATTRIBUTE9,
3135 ATTRIBUTE10,
3136 ATTRIBUTE11,
3137 ATTRIBUTE12,
3138 ATTRIBUTE13,
3139 ATTRIBUTE14,
3140 ATTRIBUTE15,
3141 SH_ATTRIBUTE_CATEGORY,
3142 SH_ATTRIBUTE1,
3143 SH_ATTRIBUTE2,
3144 SH_ATTRIBUTE3,
3145 SH_ATTRIBUTE4,
3146 SH_ATTRIBUTE5,
3147 SH_ATTRIBUTE6,
3148 SH_ATTRIBUTE7,
3149 SH_ATTRIBUTE8,
3150 SH_ATTRIBUTE9,
3151 SH_ATTRIBUTE10,
3152 SH_ATTRIBUTE11,
3153 SH_ATTRIBUTE12,
3154 SH_ATTRIBUTE13,
3155 SH_ATTRIBUTE14,
3156 SH_ATTRIBUTE15,
3157 SL_ATTRIBUTE_CATEGORY,
3158 SL_ATTRIBUTE1,
3159 SL_ATTRIBUTE2,
3160 SL_ATTRIBUTE3,
3161 SL_ATTRIBUTE4,
3162 SL_ATTRIBUTE5,
3163 SL_ATTRIBUTE6,
3164 SL_ATTRIBUTE7,
3165 SL_ATTRIBUTE8,
3166 SL_ATTRIBUTE9,
3167 SL_ATTRIBUTE10,
3168 SL_ATTRIBUTE11,
3169 SL_ATTRIBUTE12,
3170 SL_ATTRIBUTE13,
3171 SL_ATTRIBUTE14,
3172 SL_ATTRIBUTE15,
3173 SHIP_TO_LOCATION_CODE,
3174 SHIP_TO_LOCATION_ID,
3175 LLS_CODE,
3176 ITEM_DESCRIPTION
3177 )
3178 = (
3179 select
3180 PRIMARY_UOM,
3181 LPN_GROUP_ID,
3182 EXPECTED_RECEIPT_DATE,
3183 HEADER_INTERFACE_ID,
3184 l_intf_txn_id,
3185 GROUP_ID,
3186 HEADER_ID,
3187 OPERATING_UNIT,
3188 PO_NUMBER,
3189 PO_REVISION,
3190 PO_RELEASE_NUM,
3191 PO_LINE,
3192 PO_SHIPMENT,
3193 ORG_ID,
3194 PO_HEADER_ID,
3195 PO_RELEASE_ID,
3196 PO_LINE_ID,
3197 PO_LINE_LOCATION_ID,
3198 VENDOR_ID,
3199 VENDOR_SITE_ID,
3200 VENDOR_CONTACT_ID,
3201 SHIP_TO_ORG_ID,
3202 VENDOR_NAME,
3203 VENDOR_SITE_CODE,
3204 RATE_TYPE,
3205 RATE,
3206 RATE_DATE,
3207 ITEM_ID,
3208 ITEM_REVISION,
3209 UNIT_PRICE,
3210 CURRENCY_CODE,
3211 VENDOR_PRODUCT_NUM,
3212 UOM,
3213 BILL_OF_LADING,
3214 PACKING_SLIP,
3215 NUM_OF_CONTAINERS,
3216 WAYBILL_NUM,
3217 BARCODE_LABEL,
3218 COUNTRY_OF_ORIGIN,
3219 CONTAINER_NUMBER,
3220 TRUCK_NUMBER,
3221 VENDOR_LOT,
3222 COMMENTS,
3223 LINE_NUMBER,
3224 ATTRIBUTE_CATEGORY,
3225 ATTRIBUTE1,
3226 ATTRIBUTE2,
3227 ATTRIBUTE3,
3228 ATTRIBUTE4,
3229 ATTRIBUTE5,
3230 ATTRIBUTE6,
3231 ATTRIBUTE7,
3232 ATTRIBUTE8,
3233 ATTRIBUTE9,
3234 ATTRIBUTE10,
3235 ATTRIBUTE11,
3236 ATTRIBUTE12,
3237 ATTRIBUTE13,
3238 ATTRIBUTE14,
3239 ATTRIBUTE15,
3240 SH_ATTRIBUTE_CATEGORY,
3241 SH_ATTRIBUTE1,
3242 SH_ATTRIBUTE2,
3243 SH_ATTRIBUTE3,
3244 SH_ATTRIBUTE4,
3245 SH_ATTRIBUTE5,
3246 SH_ATTRIBUTE6,
3247 SH_ATTRIBUTE7,
3248 SH_ATTRIBUTE8,
3249 SH_ATTRIBUTE9,
3250 SH_ATTRIBUTE10,
3251 SH_ATTRIBUTE11,
3252 SH_ATTRIBUTE12,
3253 SH_ATTRIBUTE13,
3254 SH_ATTRIBUTE14,
3255 SH_ATTRIBUTE15,
3256 SL_ATTRIBUTE_CATEGORY,
3257 SL_ATTRIBUTE1,
3258 SL_ATTRIBUTE2,
3259 SL_ATTRIBUTE3,
3260 SL_ATTRIBUTE4,
3261 SL_ATTRIBUTE5,
3262 SL_ATTRIBUTE6,
3263 SL_ATTRIBUTE7,
3264 SL_ATTRIBUTE8,
3265 SL_ATTRIBUTE9,
3266 SL_ATTRIBUTE10,
3267 SL_ATTRIBUTE11,
3268 SL_ATTRIBUTE12,
3269 SL_ATTRIBUTE13,
3270 SL_ATTRIBUTE14,
3271 SL_ATTRIBUTE15,
3272 SHIP_TO_LOCATION_CODE,
3273 SHIP_TO_LOCATION_ID,
3274 LLS_CODE,
3275 ITEM_DESCRIPTION
3276 from pos_exasn_lines
3277 where line_id = p_old_ln)
3278 where line_id = p_line_id;
3279 end CreateNewLine;
3280
3281 procedure CreateNewHeader(p_asn_header_id in number, p_ex_header_id in number,
3282 p_ex_vendor_id in number, p_ex_ship_to_org_id in number, p_ex_vendor_site_id in number)
3283 IS
3284 l_lpn_group_id number;
3285 l_header_interface_id number;
3286 BEGIN
3287 select rcv_interface_groups_s.nextval into l_lpn_group_id from dual;
3288 select rcv_headers_interface_s.nextval into l_header_interface_id from dual;
3289
3290 insert into pos_exasn_headers(
3291 header_id,
3292 lpn_group_id,
3293 vendor_id,
3294 ship_to_org_id,
3295 vendor_site_id,
3296 header_interface_id
3297 )
3298 values(
3299 p_asn_header_id,
3300 l_lpn_group_id,
3301 p_ex_vendor_id,
3302 p_ex_ship_to_org_id,
3303 p_ex_vendor_site_id,
3304 l_header_interface_id);
3305
3306 update pos_exasn_headers
3307 set (
3308 PAYMENT_TERM_ID,
3309 CURRENCY_CODE,
3310 RATE,
3311 RATE_TYPE,
3312 RATE_DATE,
3313 ASN_REQUEST_ID ,
3314 GROUP_ID,
3315 SHIPMENT_NUMBER,
3316 SHIPMENT_DATE,
3317 EXPECTED_RECEIPT_DATE,
3318 BILL_OF_LADING,
3319 PACKING_SLIP,
3320 FREIGHT_CARRIER_CODE,
3321 NUM_OF_CONTAINERS,
3322 WAYBILL_NUM,
3323 GROSS_WEIGHT_UOM,
3324 GROSS_WEIGHT,
3325 NET_WEIGHT_UOM,
3326 NET_WEIGHT,
3327 TAR_WEIGHT_UOM,
3328 TAR_WEIGHT,
3329 PACKAGING_CODE,
3330 CARRIER_METHOD,
3331 SPECIAL_HANDLING_CODE,
3332 HAZARD_CODE,
3333 HAZARD_CLASS,
3334 FREIGHT_TERMS,
3335 COMMENTS,
3336 INVOICE_NUMBER,
3337 INVOICE_DATE,
3338 INVOICE_AMOUNT ,
3339 TAX_AMOUNT,
3340 FREIGHT_AMOUNT ,
3341 PAYMENT_TERMS,
3342 LINE_NUMBER,
3343 ATTRIBUTE_CATEGORY,
3344 SHIP_FROM_LOCATION_CODE,
3345 ATTRIBUTE1,
3346 ATTRIBUTE2,
3347 ATTRIBUTE3,
3348 ATTRIBUTE4,
3349 ATTRIBUTE5,
3350 ATTRIBUTE6,
3351 ATTRIBUTE7,
3352 ATTRIBUTE8,
3353 ATTRIBUTE9,
3354 ATTRIBUTE10,
3355 ATTRIBUTE11,
3356 ATTRIBUTE12,
3357 ATTRIBUTE13,
3358 ATTRIBUTE14,
3359 ATTRIBUTE15
3360 ) = (select
3361 PAYMENT_TERM_ID,
3362 CURRENCY_CODE,
3363 RATE,
3364 RATE_TYPE,
3365 RATE_DATE,
3366 ASN_REQUEST_ID ,
3367 GROUP_ID,
3368 SHIPMENT_NUMBER,
3369 SHIPMENT_DATE,
3370 EXPECTED_RECEIPT_DATE,
3371 BILL_OF_LADING,
3372 PACKING_SLIP,
3373 FREIGHT_CARRIER_CODE,
3374 NUM_OF_CONTAINERS,
3375 WAYBILL_NUM,
3376 GROSS_WEIGHT_UOM,
3377 GROSS_WEIGHT,
3378 NET_WEIGHT_UOM,
3379 NET_WEIGHT,
3380 TAR_WEIGHT_UOM,
3381 TAR_WEIGHT,
3382 PACKAGING_CODE,
3383 CARRIER_METHOD,
3384 SPECIAL_HANDLING_CODE,
3385 HAZARD_CODE,
3386 HAZARD_CLASS,
3387 FREIGHT_TERMS,
3388 COMMENTS,
3389 INVOICE_NUMBER,
3390 INVOICE_DATE,
3391 INVOICE_AMOUNT ,
3392 TAX_AMOUNT,
3393 FREIGHT_AMOUNT ,
3394 PAYMENT_TERMS,
3395 LINE_NUMBER,
3396 ATTRIBUTE_CATEGORY,
3397 SHIP_FROM_LOCATION_CODE,
3398 ATTRIBUTE1,
3399 ATTRIBUTE2,
3400 ATTRIBUTE3,
3401 ATTRIBUTE4,
3402 ATTRIBUTE5,
3403 ATTRIBUTE6,
3404 ATTRIBUTE7,
3405 ATTRIBUTE8,
3406 ATTRIBUTE9,
3407 ATTRIBUTE10,
3408 ATTRIBUTE11,
3409 ATTRIBUTE12,
3410 ATTRIBUTE13,
3411 ATTRIBUTE14,
3412 ATTRIBUTE15
3413 from pos_exasn_headers
3414 where header_id = p_ex_header_id)
3415 where header_id = p_asn_header_id;
3416
3417
3418 --remember to have new lpn_group_id
3419 END;
3420
3421
3422 procedure CheckLlsControl(x_return_status out nocopy varchar2,
3423 x_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
3424 l_error_pointer in out nocopy number)
3425 IS
3426 l_25errors exception;
3427 l_error_ln number;
3428 cursor l_checkLotControl_csr is
3429 select distinct plnt.line_number
3430 from pos_exasn_lines plnt,
3431 pos_exasn_lots plot
3432 where plnt.lls_code not in ('LOT','LAS')
3433 and plnt.line_id = plot.line_id;
3434
3435 cursor l_checkSerialControl_csr is
3436 select distinct plnt.line_number
3437 from pos_exasn_lines plnt,
3438 pos_exasn_serials pst
3439 where plnt.lls_code not in ('SER','LAS')
3440 and plnt.line_id = pst.line_id;
3441
3442 --To Check that for all the child-parent lpn relationship, the child lpn exists
3443 --as either a parent in some other row, or as a lpn in the lot/serial/lpn row (with quantity specified)
3444 l_lpn pos_exasn_lpns.license_plate_number%type;
3445 cursor l_check_lpnref_csr
3446 is
3447 select
3448 LPN, LINE_NUM from
3449 (
3450 select
3451 a1.license_plate_number LPN,
3452 a1.line_number LINE_NUM
3453 from
3454 pos_exasn_lpns a1,
3455 pos_exasn_lines ln
3456 where a1.line_id = ln.line_id
3457 and ln.lls_code in ('LOT','LAS')
3458 and not exists
3459 ( select 1
3460 from pos_exasn_lots t
3461 where t.line_id = a1.line_id
3462 and t.license_plate_number = a1.license_plate_number)
3463 and not exists
3464 ( select 1
3465 from pos_exasn_lpns a2
3466 where a2.line_id = a1.line_id
3467 and a2.parent_lpn = a1.license_plate_number)
3468
3469 union all
3470
3471 select
3472 a1.license_plate_number LPN,
3473 a1.line_number LINE_NUM
3474 from
3475 pos_exasn_lpns a1,
3476 pos_exasn_lines ln
3477 where a1.line_id = ln.line_id
3478 and ln.lls_code = 'SER'
3479 and not exists
3480 ( select 1
3481 from pos_exasn_serials s
3482 where s.line_id = a1.line_id
3483 and s.license_plate_number = a1.license_plate_number)
3484 and not exists
3485 ( select 1
3486 from pos_exasn_lpns a2
3487 where a2.line_id = a1.line_id
3488 and a2.parent_lpn = a1.license_plate_number)
3489
3490 union all
3491
3492 -- if there is quantity defined, the LPN must not be defined as parent on some other line
3493 select
3494 a1.license_plate_number LPN,
3495 a1.line_number LINE_NUM
3496
3497 from
3498 pos_exasn_lpns a1,
3499 pos_exasn_lines ln
3500 where a1.line_id = ln.line_id
3501 and ln.lls_code = 'LPN'
3502 and a1.quantity is not null
3503 and exists ( select 1
3504 from pos_exasn_lpns p
3505 where p.line_id = a1.line_id
3506 and a1.quantity is not null
3507 and p.parent_lpn = a1.license_plate_number)
3508
3509 union all
3510
3511 --If there is no quantity defined, the LPN line must define child-parent relationship
3512 select
3513 a1.license_plate_number LPN,
3514 a1.line_number LINE_NUM
3515 from
3516 pos_exasn_lpns a1,
3517 pos_exasn_lines ln
3518 where a1.line_id = ln.line_id
3519 and ln.lls_code = 'LPN'
3520 and a1.quantity is null
3521 and not exists ( select 1
3522 from pos_exasn_lpns a2
3523 where a2.line_id = a1.line_id
3524 and a1.quantity is null
3525 and a2.parent_lpn = a1.license_plate_number)
3526
3527
3528 );
3529
3530 BEGIN
3531 update pos_exasn_lines plnt
3532 set plnt.lls_Code = 'LAS'
3533 where exists(
3534 select /*+ INDEX (msi, mtl_system_items_b_u1) */
3535 1 from mtl_system_items msi
3536 where msi.inventory_item_id = plnt.item_id
3537 and msi.organization_id = plnt.org_id
3538 and msi.lot_control_code = 2
3539 and msi.serial_number_control_code in (2,5));
3540
3541 update pos_exasn_lines plnt
3542 set plnt.lls_Code = 'LOT'
3543 where plnt.lls_code is null
3544 and exists(
3545 select /*+ INDEX (msi, mtl_system_items_b_u1) */
3546 1 from mtl_system_items msi
3547 where msi.inventory_item_id = plnt.item_id
3548 and msi.organization_id = plnt.org_id
3549 and msi.lot_control_code = 2
3550 and msi.serial_number_control_code not in (2,5));
3551
3552 update pos_exasn_lines plnt
3553 set plnt.lls_Code = 'SER'
3554 where plnt.lls_code is null
3555 and exists(
3556 select /*+ INDEX (msi, mtl_system_items_b_u1) */
3557 1 from mtl_system_items msi
3558 where msi.inventory_item_id = plnt.item_id
3559 and msi.organization_id = plnt.org_id
3560 and msi.lot_control_code = 1
3561 and msi.serial_number_control_code in (2,5));
3562
3563 update pos_exasn_lines plnt
3564 set plnt.lls_Code = 'LPN'
3565 where plnt.lls_code is null;
3566
3567
3568
3569
3570 -- CHECK THAT LOT INFO ONLY FOR LOT CONTROLLED, etc...
3571 open l_checkLotControl_csr;
3572 loop
3573 fetch l_checkLotControl_csr into l_error_ln;
3574 exit when l_checkLotControl_csr%NOTFOUND;
3575 --Item at line LINE_NUM is not lot controlled, therefore shall not have any lot information.
3576 fnd_message.set_name('POS','POS_EXASN_NT_LC');
3577 fnd_message.set_token('LINE_NUM',l_error_ln);
3578 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3579 raise l_25errors;
3580 end if;
3581 end loop;
3582 close l_checkLotControl_csr;
3583
3584 open l_checkSerialControl_csr;
3585 loop
3586 fetch l_checkSerialControl_csr into l_error_ln;
3587 exit when l_checkSerialControl_csr%NOTFOUND;
3588 --Item at line LINE_NUM is not serial controlled, therefore shall not have any serial information.
3589 fnd_message.set_name('POS','POS_EXASN_NT_SC');
3590 fnd_message.set_token('LINE_NUM',l_error_ln);
3591 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3592 raise l_25errors;
3593 end if;
3594 end loop;
3595 close l_checkSerialControl_csr;
3596
3597 open l_check_lpnref_csr;
3598 loop
3599 fetch l_check_lpnref_csr into l_lpn, l_error_ln;
3600 exit when l_check_lpnref_csr%NOTFOUND;
3601 --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".
3602 fnd_message.set_name('POS','POS_EXASN_LPN_NOREF');
3603 fnd_message.set_token('LINE_NUM',l_error_ln);
3604 fnd_message.set_token('LPN',l_lpn);
3605 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
3606 raise l_25errors;
3607 end if;
3608 end loop;
3609 close l_check_lpnref_csr;
3610
3611
3612 x_return_status := 'S';
3613 exception when l_25errors then
3614 x_return_status := 'E';
3615 when others then
3616 x_return_status := 'U';
3617 END CheckLlsControl;
3618
3619
3620 procedure InsertIntoRTI
3621 is
3622 begin
3623 insert into rcv_transactions_interface
3624 ( INTERFACE_TRANSACTION_ID ,
3625 HEADER_INTERFACE_ID ,
3626 GROUP_ID ,
3627 TRANSACTION_TYPE ,
3628 TRANSACTION_DATE ,
3629 PROCESSING_STATUS_CODE ,
3630 PROCESSING_MODE_CODE ,
3631 TRANSACTION_STATUS_CODE ,
3632 AUTO_TRANSACT_CODE ,
3633 RECEIPT_SOURCE_CODE ,
3634 SOURCE_DOCUMENT_CODE ,
3635 PO_HEADER_ID ,
3636 PO_LINE_ID ,
3637 PO_LINE_LOCATION_ID ,
3638 QUANTITY ,
3639 PRIMARY_QUANTITY ,
3640 UNIT_OF_MEASURE ,
3641 PRIMARY_UNIT_OF_MEASURE ,
3642 LAST_UPDATE_DATE ,
3643 LAST_UPDATED_BY ,
3644 LAST_UPDATE_LOGIN ,
3645 CREATION_DATE ,
3646 CREATED_BY ,
3647 ITEM_ID ,
3648 ITEM_REVISION ,
3649 EXPECTED_RECEIPT_DATE ,
3650 COMMENTS ,
3651 BARCODE_LABEL ,
3652 CONTAINER_NUM ,
3653 COUNTRY_OF_ORIGIN_CODE ,
3654 VENDOR_ITEM_NUM ,
3655 VENDOR_LOT_NUM ,
3656 TRUCK_NUM ,
3657 NUM_OF_CONTAINERS ,
3658 PACKING_SLIP ,
3659 VALIDATION_FLAG ,
3660 WIP_ENTITY_ID ,
3661 WIP_LINE_ID ,
3662 WIP_OPERATION_SEQ_NUM ,
3663 PO_DISTRIBUTION_ID ,
3664 DOCUMENT_LINE_NUM ,
3665 DOCUMENT_SHIPMENT_LINE_NUM ,
3666 VENDOR_ID ,
3667 VENDOR_SITE_ID ,
3668 QUANTITY_INVOICED ,
3669 SHIP_TO_LOCATION_CODE ,
3670 SHIP_TO_LOCATION_ID ,
3671 PO_RELEASE_ID,
3672 license_plate_number,
3673 lpn_group_id,
3674 document_num,
3675 item_description)
3676 select
3677 interface_transaction_id,
3678 header_interface_id,
3679 group_id,
3680 'SHIP',
3681 sysdate,
3682 'PENDING',
3683 'BATCH',
3684 'PENDING',
3685 'SHIP',
3686 'VENDOR',
3687 'PO',
3688 po_header_id,
3689 po_line_id,
3690 po_line_location_id,
3691 quantity,
3692 primary_quantity,
3693 uom,
3694 primary_uom,
3695 sysdate,
3696 fnd_global.user_id,
3697 fnd_global.user_id,
3698 sysdate,
3699 fnd_global.user_id,
3700 item_id,
3701 item_revision,
3702 expected_receipt_date,
3703 comments,
3704 barcode_label,
3705 container_number,
3706 country_of_origin,
3707 vendor_product_num,
3708 vendor_lot,
3709 truck_number,
3710 num_of_containers,
3711 packing_slip,
3712 'Y',
3713 null,--wip stuff ???
3714 null,--wip stuff
3715 null,--wip stuff
3716 null,--wip stuff
3717 po_line,
3718 po_shipment,
3719 vendor_id,
3720 vendor_site_id,
3721 null, -- invoiced amount???
3722 ship_to_location_code,
3723 ship_to_location_id,
3724 po_release_id,
3725 license_plate_number,
3726 lpn_group_id,
3727 po_number,
3728 item_description
3729 from pos_exasn_lines;
3730
3731 end InsertIntoRTI;
3732
3733
3734
3735 procedure InsertIntoRHI
3736 is
3737 begin
3738 insert into rcv_headers_interface
3739 (HEADER_INTERFACE_ID ,
3740 GROUP_ID ,
3741 PROCESSING_STATUS_CODE ,
3742 RECEIPT_SOURCE_CODE ,
3743 TRANSACTION_TYPE ,
3744 LAST_UPDATE_DATE ,
3745 LAST_UPDATED_BY ,
3746 LAST_UPDATE_LOGIN ,
3747 CREATION_DATE ,
3748 CREATED_BY ,
3749 SHIP_TO_ORGANIZATION_ID ,
3750 VENDOR_ID ,
3751 VENDOR_SITE_ID ,
3752 SHIPPED_DATE ,
3753 ASN_TYPE ,
3754 SHIPMENT_NUM ,
3755 EXPECTED_RECEIPT_DATE ,
3756 PACKING_SLIP ,
3757 WAYBILL_AIRBILL_NUM ,
3758 BILL_OF_LADING ,
3759 FREIGHT_CARRIER_CODE ,
3760 FREIGHT_TERMS ,
3761 NUM_OF_CONTAINERS ,
3762 COMMENTS ,
3763 CARRIER_METHOD ,
3764 CARRIER_EQUIPMENT ,
3765 PACKAGING_CODE ,
3766 SPECIAL_HANDLING_CODE ,
3767 INVOICE_NUM ,
3768 INVOICE_DATE ,
3769 TOTAL_INVOICE_AMOUNT ,
3770 FREIGHT_AMOUNT ,
3771 TAX_NAME ,
3772 TAX_AMOUNT ,
3773 CURRENCY_CODE ,
3774 CONVERSION_RATE_TYPE ,
3775 CONVERSION_RATE ,
3776 CONVERSION_RATE_DATE ,
3777 PAYMENT_TERMS_ID ,
3778 PAYMENT_TERMS_NAME ,
3779 VALIDATION_FLAG,
3780 GROSS_WEIGHT_UOM_CODE,
3781 GROSS_WEIGHT,
3782 TAR_WEIGHT_UOM_CODE,
3783 TAR_WEIGHT,
3784 NET_WEIGHT_UOM_CODE,
3785 NET_WEIGHT,
3786 REMIT_TO_SITE_ID,
3787 SHIP_FROM_LOCATION_CODE
3788 )
3789
3790 select
3791 header_interface_id,
3792 group_id,
3793 'PENDING',
3794 'VENDOR',
3795 'NEW',
3796 sysdate,
3797 fnd_global.user_id,
3798 fnd_global.user_id,
3799 sysdate,
3800 fnd_global.user_id,
3801 ship_to_org_id,
3802 vendor_id,
3803 vendor_site_id,
3804 shipment_date,
3805 decode(INVOICE_NUMBER,null,'ASN','ASBN'),
3806 shipment_number,
3807 expected_receipt_date,
3808 packing_slip,
3809 waybill_num,
3810 bill_of_lading,
3811 freight_carrier_code,
3812 freight_terms,
3813 num_of_containers,
3814 comments,
3815 carrier_method,
3816 null,
3817 packaging_code,
3818 special_handling_code,
3819 invoice_number,
3820 invoice_date,
3821 invoice_amount,
3822 freight_amount,
3823 null,
3824 tax_amount,
3825 currency_code,
3826 rate_type,
3827 rate,
3828 rate_date,
3829 payment_term_id,
3830 payment_terms,
3831 'Y',
3832 gross_weight_uom,
3833 gross_weight,
3834 tar_weight_uom,
3835 tar_weight,
3836 net_weight_uom,
3837 net_weight,
3838 decode(invoice_number,null,null,getvendorpaysiteid(vendor_id,nvl(vendor_site_id,-9999),currency_code)),
3839 ship_from_location_code
3840 from pos_exasn_headers;
3841
3842
3843
3844 end InsertIntoRHI;
3845 procedure CheckSecuringAtt(
3846 x_return_status out nocopy varchar2,
3847 x_user_vendor_id_tbl out nocopy vendor_id_tbl_type,
3848 x_secure_by_site out nocopy varchar2,
3849 x_secure_by_contact out nocopy varchar2,
3850 x_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
3851 x_error_pointer in out nocopy number
3852 )
3853 IS
3854 l_num number;
3855 l_user_vendor_id number;
3856 l_index number;
3857
3858
3859 cursor l_vendorId_csr is
3860 SELECT NUMBER_VALUE
3861 FROM AK_WEB_USER_SEC_ATTR_VALUES
3862 WHERE WEB_USER_ID = FND_GLOBAL.USER_ID
3863 AND ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
3864 AND ATTRIBUTE_APPLICATION_ID = 177;
3865
3866
3867 cursor l_orgSecure_csr(p_resp_id number) is
3868 select 1
3869 from ak_resp_security_attributes arsa
3870 WHERE arsa.responsibility_id = p_resp_id
3871 AND arsa.attribute_application_id = 177
3872 and arsa.attribute_code = 'ICX_SUPPLIER_ORG_ID';
3873
3874 cursor l_siteSecure_csr(p_resp_id number) is
3875 select 1
3876 from ak_resp_security_attributes arsa
3877 WHERE arsa.responsibility_id = p_resp_id
3878 AND arsa.attribute_application_id = 177
3879 and arsa.attribute_code = 'ICX_SUPPLIER_SITE_ID';
3880
3881 cursor l_contactSecure_csr(p_resp_id number) is
3882 select 1
3883 from ak_resp_security_attributes arsa
3884 WHERE arsa.responsibility_id = p_resp_id
3885 AND arsa.attribute_application_id = 177
3886 and arsa.attribute_code = 'ICX_SUPPLIER_CONTACT_ID';
3887
3888
3889 cursor l_vendorSite_csr(p_user_id number) is
3890 SELECT number_value
3891 FROM ak_web_user_sec_attr_values
3892 WHERE web_user_id = p_user_id
3893 AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
3894 AND attribute_application_id = 177;
3895
3896 cursor l_vendorContact_csr(p_user_id number) is
3897 SELECT number_value
3898 FROM ak_web_user_sec_attr_values
3899 WHERE web_user_id = p_user_id
3900 AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
3901 AND attribute_application_id = 177;
3902
3903 BEGIN
3904 x_secure_by_site := 'F';
3905 x_secure_by_contact := 'F';
3906 open l_orgSecure_csr(fnd_global.resp_id);
3907 fetch l_orgSecure_csr into l_num;
3908
3909 l_num := null;
3910 open l_siteSecure_csr(fnd_global.resp_id);
3911 fetch l_siteSecure_csr into l_num;
3912 if(l_num = 1) then
3913 x_secure_by_site := 'T';
3914 end if;
3915
3916 l_num := null;
3917 open l_contactSecure_csr(fnd_global.resp_id);
3918 fetch l_contactSecure_csr into l_num;
3919 if(l_num = 1) then
3920 x_secure_by_contact := 'T';
3921 end if;
3922
3923
3924 --The following code segment retrieves and records all secured attributes
3925 --for org id, ICX_SUPPLIER_ORG_ID. The recorded set of attributes will be
3926 --used in ValidateLines to check against the vendor ids of all the lines.
3927 BEGIN
3928
3929 l_index := 1;
3930
3931 OPEN l_vendorId_csr;
3932 LOOP
3933 FETCH l_vendorId_csr INTO l_user_vendor_id;
3934 EXIT WHEN l_vendorId_csr%NOTFOUND;
3935
3936 x_user_vendor_id_tbl(l_index) := l_user_vendor_id;
3937 l_index := l_index+1;
3938
3939 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3940 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
3941 MODULE => 'pos.plsql.pos_asn_create_pvt.CheckSecuringAtt',
3942 MESSAGE => 'Retrieved Vendor Id ' || l_index || ':' ||
3943 l_user_vendor_id);
3944 END IF;
3945
3946 END LOOP;
3947
3948 EXCEPTION
3949 WHEN NO_DATA_FOUND THEN
3950 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3951 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
3952 MODULE => 'pos.plsql.pos_asn_create_pvt.CheckSecuringAtt',
3953 MESSAGE => 'No data found for cursor: l_vendorId_csr');
3954 END IF;
3955
3956 fnd_message.set_name('POS','POS_EXASN_NOT_ORG_SEC');
3957 IF(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) THEN
3958 null;
3959 END IF;
3960 x_return_status := 'E';
3961 END;
3962
3963
3964 --Check Securing Attributes by Site
3965 if(x_secure_by_site = 'T') then
3966 l_num := null;
3967 begin
3968 --Find out if -9999 is defined for Site for user's responsibility
3969 SELECT number_value
3970 into l_num
3971 FROM AK_RESP_SECURITY_ATTR_VALUES
3972 WHERE responsibility_id = fnd_global.resp_id
3973 AND attribute_application_id = 177
3974 AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
3975 AND number_value = -9999;
3976
3977 l_num := null;
3978 --IF -9999 is defined, and user is not secured by Vendor_Site_id, then it is the same as not be secured by site
3979 open l_vendorSite_csr(fnd_global.user_id);
3980 fetch l_vendorSite_csr into l_num;
3981 close l_vendorSite_csr;
3982 if(l_num is null) then
3983 x_secure_by_site := 'F';
3984 end if;
3985
3986 exception when others then
3987 --If -9999 is NOT defined, then user has to be secured by Site
3988 l_num := null;
3989 open l_vendorSite_csr(fnd_global.user_id);
3990 fetch l_vendorSite_csr into l_num;
3991 close l_vendorSite_csr;
3992 if(l_num is null) then
3993 fnd_message.set_name('POS','POS_EXASN_NOT_SITE_SEC');
3994 if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer) = 1) then
3995 null;
3996 end if;
3997 x_return_status := 'E';
3998 end if;
3999 end;
4000 end if;
4001
4002 --Check Securing Attributes by Contact
4003 if(x_secure_by_contact = 'T') then
4004 l_num := null;
4005 begin
4006 --Find out if -9999 is defined for Site for user's responsibility
4007 SELECT number_value
4008 into l_num
4009 FROM AK_RESP_SECURITY_ATTR_VALUES
4010 WHERE responsibility_id = fnd_global.resp_id
4011 AND attribute_application_id = 177
4012 AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
4013 AND number_value = -9999;
4014
4015 l_num := null;
4016 --IF -9999 is defined, and user is not secured by Contact, then it is the same as not be secured by contact
4017 open l_vendorContact_csr(fnd_global.user_id);
4018 fetch l_vendorContact_csr into l_num;
4019 close l_vendorContact_csr;
4020 if(l_num is null) then
4021 x_secure_by_contact := 'F';
4022 end if;
4023
4024 exception when others then
4025 --If -9999 is NOT defined, then user has to be secured by Contact
4026 open l_vendorContact_csr(fnd_global.user_id);
4027 fetch l_vendorContact_csr into l_num;
4028 close l_vendorContact_csr;
4029 if(l_num is null) then
4030 fnd_message.set_name('POS','POS_EXASN_NOT_CT_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
4036 end if;
4037 end;
4038 end if;
4039 exception when others then
4040 x_return_status := 'E';
4041 if(InsertError(x_error_tbl, 'Unknown exception when checking Securing Attributes:'||sqlerrm, x_error_pointer) = 1) then
4042 null;
4043 end if;
4044 END CheckSecuringAtt;
4045
4046
4047
4048
4049 function getConvertedQuantity(p_line_location_id in number,
4050 p_quantity in number,
4051 p_uom in varchar2
4052 ) return number
4053 IS
4054 l_converted_qty number;
4055 BEGIN
4056 POS_CREATE_ASN.getConvertedQuantity ( p_line_location_id ,
4057 p_quantity ,
4058 p_uom ,
4059 l_converted_qty );
4060 return l_converted_qty;
4061 exception when others then
4062 return -1;
4063 END;
4064
4065
4066 procedure ProcessExcelAsn(p_api_version in number,
4067 x_return_status out nocopy varchar2,
4068 x_return_code out nocopy varchar2,
4069 x_return_msg out nocopy varchar2,
4070 x_error_tbl out NOCOPY POS_EXCELASN_ERROR_TABLE,
4071 x_user_vendor_id out nocopy number)
4072 is
4073 x_progress varchar2(10);
4074 l_header_status varchar2(1);
4075 l_line_status varchar2(1);
4076 l_lls_status varchar2(1);
4077 l_llsControl_status varchar2(1);
4078 l_user_name varchar2(100);
4079
4080
4081
4082 -- MISC
4083
4084
4085 l_invalOrg_ln number;
4086 l_25errors exception;
4087 l_early_exp exception;
4088
4089 l_error_pointer number := 1;
4090 l_error_ln number;
4091
4092 --Securing Attributes
4093 l_secure_by_site varchar2(1);
4094 l_secure_by_contact varchar2(1);
4095 l_secure_status varchar2(1);
4096
4097 l_insertlls_status varchar2(1);
4098 l_error_ln2 number;
4099
4100 cursor l_checkLpnContra_csr
4101 is
4102 select plpn1.line_number, plpn2.line_number
4103 from pos_exasn_lpns plpn1, pos_exasn_lpns plpn2,
4104 pos_exasn_lines plnt1, pos_exasn_lines plnt2
4105 where plpn1.license_plate_number = plpn2.license_plate_number
4106 and plpn1.parent_lpn <> plpn2.parent_lpn
4107 and plnt1.line_id = plpn1.line_id
4108 and plnt2.line_id = plpn2.line_id
4109 and plnt1.org_id = plnt2.org_id ;
4110
4111 cursor l_checkDocAsn_csr
4112 is
4113 select SHIPMENT_NUMBER
4114 from pos_Exasn_headers
4115 group by SHIPMENT_NUMBER, nvl(VENDOR_ID,-9999), nvl(VENDOR_SITE_ID,-9999)
4116 having count(1) > 1;
4117
4118
4119 cursor l_buyerNotif_csr
4120 is
4121 select SHIPMENT_NUMBER, VENDOR_ID, VENDOR_SITE_ID
4122 from pos_exasn_headers;
4123
4124 l_shipment_number pos_exasn_headers.shipment_number%type;
4125 l_vendor_id pos_exasn_headers.vendor_id%type;
4126 l_vendor_site_id pos_exasn_headers.vendor_site_id%type;
4127 l_user_vendor_id_tbl vendor_id_tbl_type;
4128
4129 /* Inbound Logistics : Validate Ship From Location Code */
4130 Cursor l_shipFrom is
4131 Select /*+ USE_NL(pht,plt,ps) LEADING(pht) */
4132 pht.header_id,
4133 plt.po_line_id,
4134 plt.po_line_location_id,
4135 pht.ship_from_location_code,
4136 ps.location_id as ship_from_location_id
4137 from pos_exasn_headers pht,
4138 pos_exasn_lines plt,
4139 hz_party_sites ps
4140 where pht.header_id = plt.header_id
4141 and pht.ship_from_location_code is not null
4142 and ps.party_site_number = pht.ship_from_location_code||'|'||pht.vendor_id
4143 order by pht.header_id;
4144
4145 l_lineIdTbl po_tbl_number := po_tbl_number();
4146 l_lineLocIdTbl po_tbl_number := po_tbl_number();
4147 l_count NUMBER := 0;
4148 l_return_status VARCHAR2(2000);
4149 l_prev_header_id pos_exasn_headers.header_id%type := -1;
4150 l_prev_ship_from VARCHAR2(30) := '';
4151 l_header_id pos_exasn_headers.header_id%type;
4152 l_line_id pos_exasn_lines.po_line_id%type;
4153 l_line_location_id pos_exasn_lines.po_line_location_id%type;
4154 l_ship_from_location_code pos_exasn_headers.ship_from_location_code%type;
4155 --l_ship_from_location_id hz_party_sites.location_id%type;
4156 l_ship_from_location_id number;
4157 l_err_tbl po_tbl_varchar2000 ;
4158
4159
4160 begin
4161
4162 x_progress := '000';
4163 x_return_status := FND_API.G_RET_STS_SUCCESS;
4164 x_error_tbl := POS_EXCELASN_ERROR_TABLE();
4165
4166 --x_user_vendor_id will no longer be retrieved from the secured attribute,
4167 --ICX_SUPPLIER_ORG_ID
4168 SELECT FND_GLOBAL.USER_NAME INTO l_user_name FROM DUAL;
4169 x_user_vendor_id :=POS_VENDOR_UTIL_PKG.GET_PO_VENDOR_ID_FOR_USER(l_user_name);
4170
4171 -- Check Securing Attributes
4172
4173 CheckSecuringAtt(
4174 l_secure_status,
4175 l_user_vendor_id_tbl,
4176 l_secure_by_site,
4177 l_secure_by_contact,
4178 x_error_tbl,
4179 l_error_pointer);
4180 if(l_secure_status = 'E') then
4181 raise l_early_exp;
4182 end if;
4183
4184
4185
4186 x_progress := '001';
4187 --Validate Headers
4188 ValidateHeaders(l_header_status, x_error_tbl, l_error_pointer);
4189 if(l_header_status <> 'S') then
4190 raise l_early_exp;
4191 end if;
4192
4193
4194 x_progress := '002';
4195 --Validate Lines
4196 ValidateLines(l_line_status, l_user_vendor_id_tbl, l_secure_by_site, l_secure_by_contact, x_error_tbl, l_error_pointer);
4197 if(l_line_status <> 'S') then
4198 raise l_early_exp;
4199 end if;
4200
4201 x_progress := '003';
4202 FixHeadersAndLines(x_error_tbl,l_error_pointer);
4203 if(x_error_tbl.count > 0) then
4204 raise l_early_exp;
4205 end if;
4206
4207 --Check for Duplicate Shipment Number in the Upload Document
4208 --Invalid if same shipment number for > 2 ASNs with the same vendor_id and vendor_site_id
4209 --This validations has to be done after FixHeadersAndLines, which will populate vendor_id and vendor_site_id at header level
4210 open l_checkDocAsn_csr;
4211 loop
4212 fetch l_checkDocAsn_csr into l_shipment_number;
4213 exit when l_checkDocAsn_csr%NOTFOUND;
4214 fnd_message.set_name('POS','POS_EXASN_NEW_DUPE_SHIP');
4215 --Shipment number SHIP_NUM is used more than once for shipments with the same vendor, vendor site.
4216 fnd_message.set_token('SHIP_NUM',l_shipment_number);
4217 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
4218 raise l_25errors;
4219 end if;
4220 end loop;
4221 close l_checkDocAsn_csr;
4222
4223 /*Inbound Logistics Project : Ship From Location Validation */
4224 open l_shipFrom;
4225 loop
4226 fetch l_shipFrom into l_header_id,l_line_id,l_line_location_id,
4227 l_ship_from_location_code, l_ship_from_location_id;
4228 exit when l_shipFrom%NOTFOUND;
4229
4230 if (l_prev_header_id <> -1 AND l_prev_header_id <> l_header_id) THEN
4231 -- the first time they are not equal, we dont need to do validations
4232
4233 POS_ASN_CREATE_PVT.validate_ship_from
4234 (p_api_version_number => 1.0,
4235 p_init_msg_list => 'T',
4236 x_return_status => l_return_status,
4237 p_ship_from_locationid=> l_ship_from_location_id,
4238 p_po_line_id_tbl => l_lineIdTbl,
4239 p_po_line_loc_id_tbl => l_lineLocIdTbl,
4240 x_out_invalid_tbl => l_err_tbl
4241 );
4242 if (l_err_tbl.count() > 0) then
4243 if(InsertError(x_error_tbl,l_err_tbl(l_error_pointer), l_error_pointer)=1) then
4244 raise l_25errors;
4245 end if;
4246
4247 else
4248 -- empty the linelocid table and shiplocid table and prepare for new validation
4249 l_LineIdTbl := po_tbl_number();
4250 l_LineLocIdTbl := po_tbl_number();
4251 end if;
4252 end if;
4253 l_lineIdTbl.EXTEND;
4254 l_lineLocIdTbl.EXTEND;
4255 l_count := l_count + 1;
4256 l_lineIdTbl(l_count) := l_line_id;
4257 l_lineLocIdTbl(l_count) := l_line_location_id;
4258 l_prev_header_id := l_header_id;
4259 l_prev_ship_From := l_ship_from_location_code;
4260 end loop;
4261 close l_shipFrom;
4262
4263 x_progress := '004';
4264 CheckLlsControl(l_llsControl_status, x_error_tbl, l_error_pointer);
4265 if(l_llsControl_status <> 'S') then
4266 raise l_early_exp;
4267 end if;
4268
4269
4270
4271 x_progress := '005';
4272 -- CHECK LPN: No a-->b, a-->c
4273 open l_checkLpnContra_csr;
4274 loop
4275 fetch l_checkLpnContra_csr into
4276 l_error_ln,
4277 l_error_ln2;
4278 exit when l_checkLpnContra_csr%NOTFOUND;
4279 --The Parent-Child relationships for license plate number at line LINE_NUM1 and LINE_NUM2 contradict each other.
4280 fnd_message.set_name('POS','POS_EXASN_PLPN_CONTRA');
4281 fnd_message.set_token('LINE_NUM1',l_error_ln);
4282 fnd_message.set_token('LINE_NUM2',l_error_ln2);
4283 if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
4284 raise l_25errors;
4285 end if;
4286 end loop;
4287 close l_checkLpnContra_csr;
4288
4289
4290
4291
4292 x_progress := '006';
4293 ValidateLls(l_lls_status, x_error_tbl, l_error_pointer);
4294 if(l_lls_status <> 'S') then
4295 raise l_early_exp;
4296 end if;
4297
4298
4299
4300 if(x_error_tbl.count > 0) then
4301 raise l_early_exp;
4302 end if;
4303
4304 --Create Extra RTI for Lot Lines
4305 x_progress := '007';
4306 CreateRTI4Lot;
4307
4308 --Create Extra RTI for Serial Lines
4309 x_progress := '008';
4310 CreateRTI4Ser;
4311
4312 --Create Extra RTI for Lpn Lines
4313 x_progress := '009';
4314 CreateRTI4Lpn;
4315
4316
4317 x_progress := '010';
4318 UpdateLinesAndLls(x_error_tbl,l_error_pointer);
4319
4320 --For LAS, need to update Child Serial in many ways ==> CreateRTI4Lot
4321
4322
4323
4324 if(x_error_tbl.count > 0) then
4325 raise l_early_exp;
4326 end if;
4327
4328 x_progress := '011';
4329 InsertIntoRHI;
4330 x_progress := '012';
4331 InsertIntoRTI;
4332
4333
4334 x_progress := '013';
4335 InsertIntoLLS(l_insertlls_status, x_error_tbl, l_error_pointer);
4336 if(x_error_tbl.count > 0) then
4337 raise l_early_exp;
4338 end if;
4339
4340 --Send Notifications to Buyer for each ASN Header
4341 open l_buyerNotif_csr;
4342 loop
4343 fetch l_buyerNotif_csr into
4344 l_shipment_number, l_vendor_id, l_vendor_site_id;
4345 exit when l_buyerNotif_csr%NOTFOUND;
4346
4347 POS_ASN_NOTIF.GENERATE_NOTIF ( p_shipment_num => l_shipment_number,
4348 p_notif_type => 'CREATE',
4349 p_vendor_id => l_vendor_id,
4350 p_vendor_site_id => l_vendor_site_id,
4351 p_user_id => fnd_global.user_id
4352 );
4353 end loop;
4354 close l_buyerNotif_csr;
4355
4356
4357 exception when l_early_exp then
4358 x_return_status := FND_API.G_RET_STS_ERROR;
4359
4360 when l_25errors then
4361 x_return_status := FND_API.G_RET_STS_ERROR;
4362
4363 when others then
4364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4365 x_return_msg := 'Unexpected error in ProcessExcelAsn:'||x_progress||':'||sqlerrm;
4366 if(InsertError(x_error_tbl, 'Unexpected error in ProcessExcelAsn:'||x_progress||':'||sqlerrm, l_error_pointer)=1) then
4367 null;
4368 end if;
4369 end ProcessExcelAsn;
4370
4371 -------------------------------------------------------------------------------
4372 --Start of Comments
4373 --Name: getvendorpaysiteid
4374 --Pre-reqs:
4375 -- None.
4376 --Function:
4377 -- It returns the vendor site id depends on the Paysite Defaulting logic
4378 --Function Usage:
4379 -- This function is used for both paysite validation and defaulting the
4380 -- value in Remit-To-Site of the ASBN that is being uploaded
4381 --Logic Implemented:
4382 -- Paysite Defaulting Logic:
4383 -- 1.Check whether the primary paysite is available or not for the vendor
4384 -- If it is available, returns the vendor site id of the primary paysite
4385 -- else go to step2
4386 -- 2.Check whether the purchasing site itself,is a paysite or not
4387 -- If it is, returns the vendor site id of the purchasing paysite
4388 -- else go to step3
4389 -- 3.Check whether the PO's purchasing site has any alternate paysite or not
4390 -- If it has, returns the vendor site id of the Alternate paysite
4391 -- else go to step4
4392 -- 4. check whether any paysites available for the vendor or not
4393 -- If it has, returns the vendor site id of the first created paysite
4394 -- else returns NULL
4395 --Parameters:
4396 -- Vendor id, Vendor Site id, Currency code
4397 --IN:
4398 -- p_vendor_id,p_vendor_site_id,p_currency_code
4399 --OUT:
4400 -- l_vendor_site_id
4401 --Bug Number for reference:
4402 -- Base Bug 6718930,7196781
4403 -- Bug 7338353
4404 --End of Comments
4405 ------------------------------------------------------------------------------
4406
4407 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
4408 IS
4409 --Local Variable Declaration
4410 l_vendor_site_id PO_VENDOR_SITES_ALL.vendor_site_id%type:=0;
4411 l_DEFAULT_PAY_SITE_ID PO_VENDOR_SITES_ALL.default_pay_site_id%type;
4412 l_org_id PO_VENDOR_SITES_ALL.default_pay_site_id%type;
4413
4414 BEGIN
4415
4416 -- Getting the org id of the vendor site
4417 SELECT org_id
4418 INTO l_org_id
4419 FROM po_vendor_sites_all
4420 WHERE vendor_site_id= p_vendor_site_id;
4421
4422 -- Check for primary paysite exists
4423 BEGIN
4424 select vendor_site_id
4425 into l_vendor_site_id
4426 from PO_VENDOR_SITES_ALL PVS
4427 where
4428 SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
4429 AND PVS.org_id = l_org_id
4430 AND PVS.vendor_id = p_vendor_id
4431 AND PVS.primary_pay_site_flag = 'Y'
4432 AND ROWNUM=1;
4433
4434 EXCEPTION
4435 WHEN NO_DATA_FOUND THEN
4436 --if no primary paysite exists, then check whether the purchasing site is paysite or not
4437 BEGIN
4438
4439 select vendor_site_id
4440 into l_vendor_site_id
4441 from PO_VENDOR_SITES_ALL PVS
4442 WHERE
4443 SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
4444 AND PVS.org_id = l_org_id
4445 AND PVS.vendor_id = p_vendor_id
4446 AND PVS.vendor_site_id = p_vendor_site_id
4447 AND PVS.pay_site_flag = 'Y'
4448 AND ROWNUM=1;
4449
4450 EXCEPTION
4451 WHEN NO_DATA_FOUND THEN
4452 BEGIN
4453 --if the purchasing site is not a pay site, then check whether it has default pay site
4454 select default_pay_site_id
4455 into l_DEFAULT_PAY_SITE_ID
4456 from PO_VENDOR_SITES_ALL PVS
4457 where PVS.org_id = l_org_id
4458 AND PVS.vendor_id = p_vendor_id
4459 AND PVS.vendor_site_id = p_vendor_site_id
4460 AND ROWNUM=1;
4461
4462 EXCEPTION
4463 WHEN NO_DATA_FOUND THEN
4464 l_DEFAULT_PAY_SITE_ID :=0;
4465 END;
4466
4467 IF l_DEFAULT_PAY_SITE_ID > 0 then
4468 BEGIN
4469 --If it has default paysite, then check default paysite id is paysite or not
4470 select vendor_site_id
4471 into l_vendor_site_id
4472 from PO_VENDOR_SITES_ALL PVS
4473 where org_id = l_org_id
4474 AND PVS.vendor_id = p_vendor_id
4475 AND PVS.vendor_site_id = l_DEFAULT_PAY_SITE_ID
4476 AND PVS.pay_site_flag = 'Y'
4477 AND ROWNUM=1;
4478 EXCEPTION
4479 WHEN NO_DATA_FOUND THEN
4480 l_vendor_site_id :=0;
4481 END;
4482 End if;
4483 WHEN too_many_rows then
4484 null;
4485 end;
4486 END;
4487 --If default paysite is not available for the purchasing site, then check whether it has any other paysites
4488 --available for the particular Org and Vendor
4489 IF l_vendor_site_id =0 THEN
4490
4491 SELECT VENDOR_SITE_ID
4492 into l_vendor_site_id
4493 FROM (select
4494 ABA.CURRENCY_CODE,
4495 PVS.VENDOR_ID,
4496 PVS.VENDOR_SITE_ID,
4497 PVS.VENDOR_SITE_CODE,
4498 PVS.PAYMENT_METHOD_LOOKUP_CODE,
4499 NVL(PVS.ORG_ID, -99) ORG_ID
4500 FROM
4501 AP_BANK_ACCOUNT_USES_ALL ABAU,
4502 AP_BANK_ACCOUNTS_ALL ABA,
4503 AP_BANK_BRANCHES ABB,
4504 PO_VENDOR_SITES_ALL PVS
4505 WHERE
4506 ABAU.EXTERNAL_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID(+) AND
4507 ABA.BANK_BRANCH_ID = ABB.BANK_BRANCH_ID(+) AND
4508 ABAU.VENDOR_ID(+) = PVS.VENDOR_ID AND
4509 ABAU.VENDOR_SITE_ID(+) = PVS.VENDOR_SITE_ID AND
4510 NVL(PVS.PAY_SITE_FLAG, 'N') = 'Y' AND
4511 (NVL(ABAU.PRIMARY_FLAG, 'N') = 'Y' OR ABAU.BANK_ACCOUNT_USES_ID is null) AND
4512 PVS.PAYMENT_METHOD_LOOKUP_CODE = 'EFT' AND
4513 SYSDATE < NVL(ABB.END_DATE, SYSDATE+1) AND
4514 SYSDATE < NVL(ABA.INACTIVE_DATE, SYSDATE+1)
4515 UNION
4516 select
4517 NULL CURRENCY_CODE,
4518 PVS.VENDOR_ID,
4519 PVS.VENDOR_SITE_ID,
4520 PVS.VENDOR_SITE_CODE,
4521 PVS.PAYMENT_METHOD_LOOKUP_CODE,
4522 NVL(PVS.ORG_ID, -99) ORG_ID
4523 FROM
4524 PO_VENDOR_SITES_ALL PVS
4525 WHERE
4526 SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
4527 AND NVL(PVS.PAY_SITE_FLAG, 'N') = 'Y' AND
4528 PVS.PAYMENT_METHOD_LOOKUP_CODE <> 'EFT') QRSLT WHERE (ORG_ID = NVL(l_org_id, -99) AND VENDOR_ID = p_vendor_id AND
4529 DECODE(PAYMENT_METHOD_LOOKUP_CODE, 'EFT', CURRENCY_CODE, p_currency_code) = p_currency_code AND ROWNUM=1);
4530 END IF;
4531
4532 RETURN l_vendor_site_id;
4533
4534 END getvendorpaysiteid;
4535
4536
4537 END Pos_ExcelAsn_PVT;