[Home] [Help]
PACKAGE BODY: APPS.WSH_SC_TRX_INTERFACE
Source
1 PACKAGE BODY WSH_SC_TRX_INTERFACE as
2 /* $Header: WSHSDOIB.pls 115.17 99/08/19 17:01:12 porting s $ */
3 completion_status varchar2(20);
4 conc_request_id number;
5
6 --****************************************************************************
7 -- PACKAGE NAME: WSH_SC_TRX_INTERFACE
8 -- DESCRIPTION: Ship Confirm Open Interface
9 -- Main Function: Confirm_Interface_Data
10 --
11 -- DEPENDANCIES
12 -- Calls the folowing packages:
13 -- WSH_SC_VALIDATION
14 -- SHP_SC_SERIAL_VALIDATION_PKG.check_serial_number
15 -- SHP_SC_DETAILS_REQUIRED_PKG.details_required
16 -- SHP_SC_CLOSE_HEADER_PKG.validate_ship_date
17 -- SHP_SC_CLOSE_HEADER_PKG.close_headers
18 -- calls SHP_SC_QUANTITY_PKG.Cascade_Quantity
19 --****************************************************************************
20
21
22 -----------------------------------------------------------------------------------
23 -- Name : Duplicate_sn_in_interface
24 -- Purpose: vaidate against duplicates in interface data
25 -- Arguments
26 -- x_serial_number_control_code
27 -- x_warehouse_id input org id
28 -- x_item_id input item_id
29 -- x_rowid the rowid of the detail line being processed
30 -- with this sn
31 -- x_sn the serial number
32 -- RETURNS TRUE if duplicate exists else FALSE
33 -- Notes
34 -- this checks interface data for duplicates.
35 --
36 -----------------------------------------------------------------------------------
37
38 FUNCTION Duplicate_SN_in_Interface(X_serial_number_control_code in varchar2,
39 X_warehouse_id in number,
40 X_item_id in number,
41 X_rowid in varchar2,
42 X_sn in varchar2) return BOOLEAN is
43
44 BEGIN
45
46 DECLARE
47
48 check_item varchar2(1);
49 check_org varchar2(1);
50 x_sn_type varchar2(1);
51 x_transaction_id number;
52 x_item_concat_segs varchar2(2000);
53 valid_flag boolean;
54
55 CURSOR DUP_CHECK is
56 SELECT del.transaction_id
57 FROM wsh_deliveries_interface del,
58 wsh_picking_details_interface dtl
59 WHERE nvl( dtl.shipped_quantity, 0 ) <> 0
60 AND dtl.rowid <> chartorowid(x_rowid)
61 AND del.transaction_id = dtl.transaction_id
62 AND del.process_flag = 1
63 AND dtl.serial_number = X_sn
64 AND (check_item = 'Y' and (dtl.inventory_item_id = X_item_id
65 OR dtl.inventory_item = X_item_concat_segs)
66 OR check_item <> 'Y')
67 AND dtl.warehouse_id = decode(check_org, 'Y', X_warehouse_id, dtl.warehouse_id);
68
69 CURSOR IN_MTL_PARAM IS
70 SELECT serial_number_type
71 FROM MTL_PARAMETERS
72 WHERE organization_id = X_warehouse_id;
73
74 BEGIN
75 check_org := 'N';
76 check_item := 'N';
77 if X_SERIAL_NUMBER_CONTROL_CODE = 'Y' then
78 check_org := 'Y';
79 check_item := 'Y';
80 elsif X_SERIAL_NUMBER_CONTROL_CODE = 'D' then
81 open IN_MTL_PARAM;
82 fetch IN_MTL_PARAM into X_sn_type;
83 if IN_MTL_PARAM%FOUND then
84 if X_sn_type = '1' then
85 check_item := 'Y';
86 elsif X_sn_type = '2' then
87 check_org := 'Y';
88 end if;
89 end if;
90 close IN_MTL_PARAM;
91 end if;
92
93 if check_item = 'Y' then
94 valid_flag := fnd_flex_keyval.validate_ccid(
95 appl_short_name=>'INV',
96 key_flex_code=>'MSTK',
97 structure_number=>101,
98 combination_id=>x_item_id,
99 data_set=>x_warehouse_id);
100 x_item_concat_segs := fnd_flex_keyval.concatenated_values;
101 end if;
102
103 open DUP_CHECK;
104 fetch DUP_CHECK into X_transaction_id;
105 close DUP_CHECK;
106
107 if x_transaction_id is not null then
108 return (TRUE);
109 end if;
110 return (FALSE);
111
112 END;
113
114 END Duplicate_SN_In_Interface;
115
116
117
118
119 -----------------------------------------------------------------------------------
120 -- Name
121 -- Check_Serial_Number
122 -- Purpose
123 -- ensure any SN in SN range is valid by dissallowing any duplicates
124 -- that satisfy any one of the following 3 condtions
125 -- 1. current status not in (1,3) (ie allow 'instore' to permit RMAs that
126 -- have been returned/reshelved and 'defined but not used' to permit
127 -- serial numbers for predefined control)
128 -- 2. not yet interfaced to inventory
129 -- 3. exist in inventory interface tables (ie they have been interfaced
130 -- but haven't been processed by inventory yet)
131 --
132 -- Assumptions
133 -- MTL_SER_NUM_INT.FM_SN = MTL_SER_NUM_INT.to_SN therefore we do not bother
134 -- with range checking on this table.
135 --
136 -- Arguments
137 -- X_Mode : either post-change or commit.
138 -- X_to_sn is required.
139 --
140 -- X_Serial_number_control : either N, Y or D
141 -- N = No serial checking, procedure returns success
142 -- Y = either predefined serial numbers or dynamic at inv receipt.
143 -- D = dynamic entry at sales issue
144 --
145 -- X_ERROR_CODE: 0 = success
146 -- -1 = error
147 -- >1 = program error
148 --
149 -- Notes
150 -- 1) dynamic sql is built according to x_mode and whether to check for
151 -- uniqueness at item level, org level, neither (ie across orgs) or
152 -- both (ie for dynamic entry at sales issue).
153 --
154 -- 2) Dynamic entry checks for uniqueness within org and item.
155 -- When using dynamic, serial numbers should never exist in MTL_SN
156 -- and any record that is found will be an error (other than status 1,3).
157 -- Therfore we must use an outer join in these cases.
158 --
159 -- 3) Post-change checking excludes entire picking line from duplicate checking.
160 -- Commit checking is tighter/more granular by excluding detail_line only.
161 -- In this way, the user can update a picking line by swapping SN within
162 -- it and not receive errors. Otherwise user would have to null out one SN,
163 -- change the other SN and then set the nulled out SN
164 -----------------------------------------------------------------------------------
165
166
167 PROCEDURE Check_Serial_Number(X_SERIAL_NUMBER_CONTROL_CODE IN VARCHAR2,
168 X_WAREHOUSE_ID IN NUMBER,
169 X_ITEM_ID IN NUMBER,
170 X_LINE_ID IN NUMBER,
171 X_LINE_DETAIL_ID IN NUMBER,
172 X_SN IN VARCHAR2,
173 X_ERROR_CODE IN OUT NUMBER) IS
174
175 Cursor IN_MTL_PARAM IS
176 select serial_number_type
177 from MTL_PARAMETERS
178 where organization_id = X_warehouse_id;
179
180 X_Cursor NUMBER;
181 X_Stmt VARCHAR2(5000);
182 X_Stmt_Num NUMBER;
183 SN_header_id NUMBER;
184 SN_line_number NUMBER;
185 x_rows NUMBER;
186 check_item BOOLEAN := FALSE;
187 check_org BOOLEAN := FALSE;
188 x_sn_type VARCHAR2(1);
189
190 BEGIN
191 x_error_code := 0;
192
193 if X_SERIAL_NUMBER_CONTROL_CODE = 'Y' then
194 check_org := TRUE;
195 check_item := TRUE;
196 elsif X_SERIAL_NUMBER_CONTROL_CODE = 'D' then
197 open IN_MTL_PARAM;
198 fetch IN_MTL_PARAM into X_sn_type;
199 if IN_MTL_PARAM%FOUND then
200 if X_sn_type = '1' then
201 check_item := TRUE;
202 elsif X_sn_type = '2' then
203 check_org := TRUE;
204 end if;
205 end if;
206 close IN_MTL_PARAM;
207 end if;
208
209
210 /*** for debugging this is the statement so far
211
212 SELECT SOPLS.PICKING_HEADER_ID, SOPLS.SEQUENCE_NUMBER
213 FROM mtl_serial_numbers s,
214 so_picking_lines_all sopls,
215 so_picking_line_details_all sopld
216 WHERE sopld.picking_line_id = sopls.picking_line_id
217 and sopls.picking_line_ID <> :X_line_id
218 and sopls.inventory_item_id = :X_item_id
219 and nvl( sopls.warehouse_id,:X_warehouse_id ) = :X_warehouse_id
220 and nvl( sopld.shipped_quantity, 0 ) <> 0
221 and (sopld.fm_serial_number <= :X_to_sn
222 and sopld.to_serial_number >= :x_sn)
223 and s.inventory_item_id (+)= :X_item_id
224 and s.serial_number (+) between greatest(sopld.fm_serial_number,:x_sn)
225 and least (sopld.to_serial_number,:X_to_sn)
226 and (nvl(s.current_status,1) not in (1,3)
227 or nvl(sopls.inventory_status, 'NO VALUE') <> 'INTERFACED'
228 or exists (select 'serial number not yet yet interfaced'
229 FROM mtl_serial_numbers_interface si,
230 mtl_transactions_interface ti
231 WHERE si.fm_serial_number between greatest(sopld.fm_serial_number,:x_sn)
232 and least (sopld.to_serial_number,:X_to_sn)
233 and ti.source_code = si.source_code
234 and ti.source_line_id = si.source_line_id
235 and ti.inventory_item_id = sopls.inventory_item_id
236 and ti.organization_id = sopld.warehouse_id ))
237 ORDER BY sopls.picking_header_id desc
238 ***/
239
240 if X_SERIAL_NUMBER_CONTROL_CODE in ('Y','D') then
241 X_Stmt := X_Stmt || ' SELECT SOPLS.PICKING_HEADER_ID, ';
242 X_Stmt := X_Stmt || ' SOPLS.SEQUENCE_NUMBER ';
243 X_Stmt := X_Stmt || ' FROM mtl_serial_numbers s, ';
244 X_Stmt := X_Stmt || ' so_picking_lines_all sopls, ';
245 X_Stmt := X_Stmt || ' so_picking_line_details sopld ';
246 X_Stmt := X_Stmt || ' WHERE sopld.picking_line_id = sopls.picking_line_id ';
247 X_Stmt := X_Stmt || ' AND sopld.picking_line_detail_id <> :X_line_detail_id ';
248
249 if check_item then
250 X_Stmt := X_Stmt || ' AND sopls.inventory_item_id = :X_item_id ';
251 -- if dynamic generation of SN then MTL_SN wont contain values so use outer join
252 if X_SERIAL_NUMBER_CONTROL_CODE = 'D' then
253 X_Stmt := X_Stmt || ' AND s.inventory_item_id(+) = :X_item_id ';
254 else
255 X_Stmt := X_Stmt || ' AND s.inventory_item_id = :X_item_id ';
256 end if;
257 end if;
258
259 if check_org then
260 X_Stmt := X_Stmt || ' AND nvl(sopls.warehouse_id,:X_warehouse_id) = :X_warehouse_id ';
261 end if;
262
263 X_Stmt := X_Stmt || ' AND nvl( sopld.shipped_quantity, 0 ) <> 0 ';
264 X_Stmt := X_Stmt || ' AND sopld.serial_number = :x_sn ';
265
266 -- if dynamic generation of SN then MTL_SN wont contain values so use outer join
267 if X_SERIAL_NUMBER_CONTROL_CODE = 'D' then
268 X_Stmt := X_Stmt || ' AND s.serial_number (+) = :x_sn ';
269 else
270 X_Stmt := X_Stmt || ' AND s.serial_number = :x_sn ';
271 end if;
272
273 X_Stmt := X_Stmt || ' AND (nvl(s.current_status,1) not in (1,3) ';
274 X_Stmt := X_Stmt || ' OR nvl(sopls.inventory_status, ''NO VALUE'') <> ''INTERFACED'' ';
275 X_Stmt := X_Stmt || ' OR exists (SELECT ''serial number not yet yet interfaced'' ';
276 X_Stmt := X_Stmt || ' FROM mtl_serial_numbers_interface si ';
277
278 if check_item or check_org then
279 X_Stmt := X_Stmt || ' ,mtl_transactions_interface ti ';
280 end if;
281
282 X_Stmt := X_Stmt || ' WHERE si.fm_serial_number = :x_sn ';
283
284 if check_item or check_org then
285 X_Stmt := X_Stmt || ' AND ti.source_code = si.source_code ';
286 X_Stmt := X_Stmt || ' AND ti.source_line_id = si.source_line_id ';
287
288 if check_item then
289 X_Stmt := X_Stmt || ' AND ti.inventory_item_id = sopls.inventory_item_id ';
290 end if;
291 if check_org then
292 X_Stmt := X_Stmt || ' AND ti.organization_id = sopld.warehouse_id ';
293 end if;
294
295 end if;
296
297 X_Stmt := X_Stmt || ' )) ';
298 X_Stmt := X_Stmt || ' ORDER BY sopls.picking_header_id DESC';
299 X_Stmt_Num := 40;
300 X_Cursor := dbms_sql.open_cursor;
301 X_Stmt_Num := 50;
302 dbms_sql.parse(X_Cursor,X_Stmt,dbms_sql.v7);
303 X_Stmt_Num := 60;
304 if check_org then
305 dbms_sql.bind_variable (X_Cursor,'X_warehouse_id',X_warehouse_id);
306 end if;
307 X_Stmt_Num := 61;
308 if check_item then
309 dbms_sql.bind_variable (X_Cursor,'X_item_id',X_item_id);
310 end if;
311 X_Stmt_Num := 62;
312 dbms_sql.bind_variable (X_Cursor,'x_sn',X_sn);
313 X_Stmt_Num := 65;
314 dbms_sql.bind_variable (X_Cursor,'X_line_detail_id',X_line_detail_id);
318 X_Rows := dbms_sql.fetch_rows(X_Cursor);
315 X_Stmt_Num := 80;
316 X_Rows := dbms_sql.execute(X_Cursor);
317 X_Stmt_Num := 90;
319 X_Stmt_Num := 110;
320 if X_Rows <> 0 THEN
321 X_error_code := -1;
322 Return;
323 end if;
324 X_stmt_Num := 120;
325 if dbms_sql.is_open(X_Cursor) then
326 dbms_sql.close_cursor(X_Cursor);
327 end if;
328 end if;
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
333 FND_MESSAGE.Set_Token('PACKAGE','SHP_SC_SERIAL_VALIDATION_PKG.Check_Serial_Numbers');
334 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
335 FND_MESSAGE.Set_Token('ORA_TEXT',X_Stmt_Num ||':'|| X_Stmt);
336 APP_EXCEPTION.Raise_Exception;
337 END Check_serial_number;
338
339
340
341 -----------------------------------------------------------------------------------
342 -- Name: Update_Sopld_Row
343 -- Purpose: To update SOPLD with new inventory controls and
344 -- shipped quantity. Inventory controls cannot be
345 -- overwritten if not null, instead a new value will be input.
346 --
347 -- Arguments:
348 -- X_picking_line_detail_id optional: see notes
349 --
350 -- Notes
351 -- Picking_line_detail_id may be null, in which case we update the
352 -- first detail line for this picking_line with the same inventory controls.
353 -- we use rownum to ensure we dont update more than one record but there
354 -- shouldnt be more than one.
355 -----------------------------------------------------------------------------------
356
357
358 PROCEDURE Update_SOPLD_Row
359 (X_picking_line_id in number,
360 X_picking_line_detail_id in number,
361 X_requested_quantity in number,
362 X_shipped_quantity in number,
363 X_warehouse in number,
364 X_sn in varchar2,
365 x_lot in varchar2,
366 x_revision in varchar2,
367 x_subinventory in varchar2,
368 x_locator_id in number,
369 x_departure_id in number,
370 x_delivery_id in number,
371 x_container_id in number,
372 x_context in varchar2,
373 x_dpw_assigned_flag in varchar2,
374 x_att1 in varchar2, x_att2 in varchar2, x_att3 in varchar2, x_att4 in varchar2,
375 x_att5 in varchar2, x_att6 in varchar2, x_att7 in varchar2, x_att8 in varchar2,
376 x_att9 in varchar2, x_att10 in varchar2, x_att11 in varchar2, x_att12 in varchar2,
377 x_att13 in varchar2, x_att14 in varchar2, x_att15 in varchar2,
378 error_code in out varchar2) is
379
380 BEGIN
381
382 BEGIN
383 error_code:='0';
384 --------------------------------------------------------------------------
385 -- this is called with either picking line detail id and no inventory controls
386 -- or the opposite. Either way, it can update a record.
387 -- Only one record should fit this criteria but to be sure, use rownum.
388 --------------------------------------------------------------------------
389 wsh_del_oi_core.println('updating pld with pld_id:'||to_char(X_picking_line_detail_id)||
390 ' pl_id:'||to_char(X_picking_line_id)||
391 ' req_qty:'||to_char(X_requested_quantity)||
392 ' shp_qty:'||to_char(X_shipped_quantity)||
393 ' warehouse:'||to_char(X_warehouse)||
394 ' srl:'||X_sn|| ' lot:'||x_lot||
395 ' rev:'||x_revision||' sub:'||x_subinventory||
396 ' loc:'|| to_char(x_locator_id)||
397 ' dep:'||to_char(x_departure_id)||
398 ' del:'||to_char(x_delivery_id)||
399 ' assg_flag:'||x_dpw_assigned_flag||
400 ' container:'||to_char(x_container_id) );
401
402 BEGIN
403 /* BUG 820933 : Changed the logic for dpw_assigned_flag. Populate the dpw_assigned_flag
404 with null (which means assigned) if any of X_DELIVERY_ID,X_DEPARTURE_ID,
405 pld.Departure_id,pld.Delivery_id is not null. Otherwise populate it with
406 x_dpw_assigned_flag.
407 */
408 UPDATE SO_PICKING_LINE_DETAILS pld
409 SET Last_update_date = sysdate,
410 Last_updated_by = fnd_global.user_id,
411 Last_update_login = fnd_global.user_id,
412 Warehouse_id = nvl(X_warehouse, warehouse_id),
413 Requested_quantity = requested_quantity + x_requested_quantity,
414 Shipped_quantity = nvl(SHIPPED_QUANTITY,0) + x_shipped_quantity,
415 Serial_number = nvl(X_SN, serial_number),
416 Lot_number = nvl(X_LOT, lot_number),
417 Revision = nvl(X_REVISION, revision),
418 Subinventory = nvl(X_SUBINVENTORY,subinventory),
419 Inventory_location_id = nvl(X_LOCATOR_ID,inventory_location_id),
420 Departure_id = nvl(X_DEPARTURE_ID,departure_id),
421 Delivery_id = nvl(X_DELIVERY_ID, delivery_id),
422 Container_id = nvl(X_CONTAINER_ID,container_id),
423 Context = x_context,
424 DPW_assigned_flag = decode(nvl(nvl(X_DELIVERY_ID, delivery_id),nvl(X_DEPARTURE_ID,departure_id)),
425 null, x_dpw_assigned_flag,null),
426 Attribute1 = x_att1, Attribute2 = x_att2, Attribute3 = x_att3, Attribute4 = x_att4,
430 ( Segment1, Segment2, Segment3, Segment4, Segment5,
427 Attribute5 = x_att5, Attribute6 = x_att6, Attribute7 = x_att7, Attribute8 = x_att8,
428 Attribute9 = x_att9, Attribute10 = x_att10, Attribute11 = x_att11, Attribute12 = x_att12,
429 Attribute13 = x_att13, Attribute14 = x_att14, Attribute15 = x_att15,
431 Segment6, Segment7, Segment8, Segment9, Segment10,
432 Segment11, Segment12, Segment13, Segment14, Segment15,
433 Segment16, Segment17, Segment18, Segment19, Segment20)
434 = ( SELECT Segment1, Segment2, Segment3, Segment4, Segment5,
435 Segment6, Segment7, Segment8, Segment9, Segment10,
436 Segment11, Segment12, Segment13, Segment14, Segment15,
437 Segment16, Segment17, Segment18, Segment19, Segment20
438 FROM MTL_ITEM_LOCATIONS
439 WHERE inventory_location_id = nvl(x_locator_id,pld.inventory_location_id))
440 WHERE pld.picking_line_id = X_picking_line_id
441 and pld.picking_line_detail_id = nvl(X_picking_line_detail_id, pld.picking_line_detail_id)
442 and ( (nvl(serial_number,nvl(x_sn,'~')) = nvl(X_sn,'~') and requested_quantity = x_shipped_quantity)
443 or
444 nvl(serial_number,'~') = nvl(X_sn,'~') ) -- vms
445 and nvl(lot_number,nvl(X_lot,'~')) = nvl(X_lot,'~')
446 and nvl(revision,nvl(X_revision,'~')) = nvl(X_revision,'~')
447 and nvl(subinventory,nvl(X_subinventory,'~')) = nvl(X_subinventory,'~')
448 and nvl(inventory_location_id,nvl(X_locator_id,'-1')) = nvl(X_locator_id,'-1')
449 and nvl(delivery_id, nvl(x_delivery_id,-1)) = nvl(x_delivery_id,-1)
450 and nvl(container_id, nvl(x_container_id,-1)) = nvl(x_container_id,-1)
451 and rownum < 2;
452 EXCEPTION when others then
453 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
454 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.update_sopld');
455 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
456 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
457 APP_EXCEPTION.Raise_Exception;
458 END;
459
460 if sql%notfound then
461 wsh_del_oi_core.println('BUT could not update so_picking_line_details.');
462 error_code := '1';
463 end if;
464
465 END;
466
467 END Update_SOPLD_Row;
468
469
470
471
472 -----------------------------------------------------------------------------------
473 -- Name
474 -- Insert_Sopld_Row
475 -- Arguments
476 -- Line Ids are required.
477 -- Inventory controls are optional. If they are not supplied then a new
478 -- line is created with the inventory controls of the existing line id
479 --
480 -- Purpose
481 -- Called when creating a remainder
482 -- OR when line detail was picked from more than one set of inventory
483 -- controls hence requiring a split eg:
484 -- 1) Serial numbers. By default, only one record is created in SOPL
485 -- but if SN are interfaced then multiple records must be input
486 -- 2) Different Inventory controls: If reservations are OFF, the user
487 -- could pick from a variety of inventory controls. Each one requires
488 -- a new record.
489 --
490 -- Notes
491 --
492 -----------------------------------------------------------------------------------
493
494 -- 905046. Added pick_slip_number as input to Insert_Sopld_Row.
495
496 PROCEDURE Insert_Sopld_Row
497 (X_parent_detail_id in number,
498 new_pld_id in out number,
499 X_pick_slip_number in number,
500 X_requested_quantity in number,
501 X_shipped_quantity in number,
502 X_warehouse_id in number,
503 X_sn in varchar2,
504 x_lot in varchar2,
505 x_revision in varchar2,
506 x_subinventory in varchar2,
507 x_locator_id in number,
508 x_departure_id in number,
509 x_delivery_id in number,
510 x_container_id in number,
511 x_context in varchar2,
512
513 x_att1 in varchar2, x_att2 in varchar2, x_att3 in varchar2, x_att4 in varchar2,
514 x_att5 in varchar2, x_att6 in varchar2, x_att7 in varchar2, x_att8 in varchar2,
515 x_att9 in varchar2, x_att10 in varchar2, x_att11 in varchar2, x_att12 in varchar2,
516 x_att13 in varchar2, x_att14 in varchar2, x_att15 in varchar2) is
517 BEGIN
518
519 SELECT SO_PICKING_LINE_DETAILS_S.NEXTVAL into new_pld_id from DUAL;
520
521 wsh_del_oi_core.println('creating pld with pld_id:'||to_char(new_pld_id)||
522 'pick_slip_number:'||to_char(X_pick_slip_number)||
523 ' parent_id:'||to_char(X_parent_detail_id)||
524 ' req_qty:'||to_char(X_requested_quantity)||
525 ' shp_qty:'||to_char(X_shipped_quantity)||
526 ' warehouse:'||to_char(X_warehouse_id)||
527 ' srl:'||X_sn|| ' lot:'||x_lot||
528 ' rev:'||x_revision||' sub:'||x_subinventory||
529 ' loc:'|| to_char(x_locator_id)||
530 ' dep:'||to_char(x_departure_id)||
531 ' del:'||to_char(x_delivery_id)||
532 ' container:'||to_char(x_container_id) );
533
534 -- 905046. Inserting pick_slip_number into so_picking_line_details.
535
536 INSERT INTO SO_PICKING_LINE_DETAILS(
537 PICKING_LINE_DETAIL_ID
541 , LAST_UPDATE_LOGIN
538 , PICK_SLIP_NUMBER
539 , LAST_UPDATE_DATE
540 , LAST_UPDATED_BY
542 , CREATION_DATE
543 , CREATED_BY
544 , PICKING_LINE_ID
545 , DETAIL_TYPE_CODE
546 , WAREHOUSE_ID
547 , REQUESTED_QUANTITY
548 , SHIPPED_QUANTITY
549 , SERIAL_NUMBER
550 , LOT_NUMBER
551 , CUSTOMER_REQUESTED_LOT_FLAG
552 , REVISION
553 , SUBINVENTORY
554 , INVENTORY_LOCATION_ID
555 , SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
556 SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
557 SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
558 SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,
559 INVENTORY_LOCATION_SEGMENTS
560 , CONTEXT
561 , ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
562 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
563 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
564 RELEASED_FLAG
565 , SCHEDULE_DATE
566 , SCHEDULE_STATUS_CODE
567 , SCHEDULE_LEVEL
568 , TRANSACTABLE_FLAG
569 , RESERVABLE_FLAG
570 , LATEST_ACCEPTABLE_DATE
571 , AUTOSCHEDULED_FLAG
572 , DEMAND_ID
573 , DELIVERY
574 , DEMAND_CLASS_CODE
575 , UPDATE_FLAG
576 , SUPPLY_SOURCE_TYPE
577 , SUPPLY_SOURCE_HEADER_ID
578 , DEPARTURE_ID
579 , DELIVERY_ID
580 , CONTAINER_ID
581 , DPW_ASSIGNED_FLAG)
582 SELECT new_pld_id,
583 X_pick_slip_number,
584 SYSDATE,
585 FND_GLOBAL.USER_ID,
586 FND_GLOBAL.USER_ID,
587 SYSDATE,
588 FND_GLOBAL.LOGIN_ID,
589 pld.picking_line_id,
590 detail_type_code,
591 nvl(x_warehouse_id, pld.warehouse_id),
592 x_requested_quantity,
593 x_shipped_quantity,
594 x_sn,
595 nvl(x_lot, pld.lot_number),
596 pld.customer_requested_lot_flag,
597 nvl(x_revision, pld.revision),
598 nvl(x_subinventory, pld.subinventory),
599 nvl(x_locator_id, pld.inventory_location_id),
600 m.Segment1, m.Segment2, m.Segment3, m.Segment4, m.Segment5,
601 m.Segment6, m.Segment7, m.Segment8, m.Segment9, m.Segment10,
602 m.Segment11, m.Segment12, m.Segment13, m.Segment14, m.Segment15,
603 m.Segment16, m.Segment17, m.Segment18, m.Segment19, m.Segment20,
604 pld.inventory_location_segments,
605 nvl(x_context,pld.context),
606 nvl(x_att1,pld.attribute1), nvl(x_att1,pld.attribute2),
607 nvl(x_att1,pld.attribute3), nvl(x_att1,pld.attribute4),
608 nvl(x_att1,pld.attribute5), nvl(x_att1,pld.attribute6),
609 nvl(x_att1,pld.attribute7), nvl(x_att1,pld.attribute8),
610 nvl(x_att1,pld.attribute9), nvl(x_att1,pld.attribute10),
611 nvl(x_att1,pld.attribute11), nvl(x_att1,pld.attribute12),
612 nvl(x_att1,pld.attribute13), nvl(x_att1,pld.attribute14),
613 nvl(x_att1,pld.attribute15),
614 pld.released_flag,
615 pld.schedule_date,
616 pld.schedule_status_code,
617 pld.schedule_level,
618 pld.transactable_flag,
619 pld.reservable_flag,
620 pld.latest_acceptable_date,
621 pld.autoscheduled_flag,
622 pld.demand_id,
623 pld.delivery,
624 pld.demand_class_code,
625 pld.update_flag,
626 pld.supply_source_type,
627 pld.supply_source_header_id,
628 x_departure_id,
629 x_delivery_id,
630 x_container_id,
631 NULL
632 FROM SO_PICKING_LINE_DETAILS PLD,
633 MTL_ITEM_LOCATIONS M
634 WHERE PLD.PICKING_LINE_DETAIL_ID = X_Parent_Detail_Id
635 AND m.inventory_location_id (+) = nvl(x_locator_id,pld.inventory_location_id);
636
637 EXCEPTION when others then
638 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
639 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.Insert_sopld');
640 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
641 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
642 APP_EXCEPTION.Raise_Exception;
643
644 END Insert_Sopld_Row;
645
646
647 -----------------------------------------------------------------------------------
648 -- Name Update_requested_quantity
649 --
650 -- Arguments
651 --
652 -- Notes If the X_backorder_flag = TRUE (ie action_code in (9,10,11,12,13)), ie
653 -- the remaining quantity is to be backordered, then the shipped_qty,
654 -- departure_id, delivery_id & dpw_assigned_flag should not be nulled out,
655 -- If the departure_id & delivery_id are nulled in the original pld_id,
656 -- and since the insert_sopld_row will insert new rows in SOPLD
657 -- with delivery_id as NOT NULL, it will result in splitting of the
658 -- corresponding picking_line in SOPL.
659 -- NOTE: Splitting is done if all the pld for a picking line do NOT
660 -- have the same delivery_id.
661 -----------------------------------------------------------------------------------
662
663
664 PROCEDURE Update_requested_quantity
665 (X_picking_line_detail_id in number,
666 X_shipped_quantity in number,
667 X_backorder_flag in boolean,
668 error_code in out varchar2) is
669 BEGIN
670 DECLARE
671 X_bo varchar2(10) := 'FALSE';
672 BEGIN
673
674 BEGIN
675 error_code:='0';
676 IF ( X_backorder_flag = TRUE ) THEN
677 X_bo := 'TRUE';
678 wsh_del_oi_core.println('Update requested_quantity will NULL out the dep & del in sopld.');
679 END IF;
683 shipped_quantity = decode(X_bo, 'FALSE' ,NULL,shipped_quantity),
680 BEGIN
681 UPDATE SO_PICKING_LINE_DETAILS pld
682 SET requested_quantity = requested_quantity - X_shipped_quantity,
684 delivery_id = decode(X_bo, 'FALSE' ,NULL,delivery_id),
685 departure_id = decode(X_bo, 'FALSE' ,NULL, departure_id),
686 dpw_assigned_flag = decode(X_bo, 'FALSE' ,'N',dpw_assigned_flag)
687 WHERE pld.picking_line_detail_id = X_picking_line_detail_id;
688
689 wsh_del_oi_core.println('Reduced req_qty in pl:'|| to_char(X_picking_line_detail_id)||
690 ' by:'||to_char(X_shipped_quantity));
691
692 EXCEPTION when others then
693 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
694 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.update_requested_quantity');
695 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
696 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
697 APP_EXCEPTION.Raise_Exception;
698 END;
699 if sql%notfound then
700 wsh_del_oi_core.println('Unexpected error on picking detail '||to_char(x_picking_line_detail_id));
701 error_code := '1';
702 end if;
703 END;
704 END;
705 END update_requested_quantity;
706
707
708
709 -----------------------------------------------------------------------------------
710 -- Name: Split_SOPLD_row
711 -- Purpose
712 -- This is called when we want to update SOPLD but have found that the line detail
713 -- has different inventory controls. This procedure creates a second line detail
714 -- with the new inventory controls or updates any existing line detail for this
715 -- pickslip with these inventory controls.
716 -- Notes
717 -----------------------------------------------------------------------------------
718
719 -- 905046. Added pick_slip_number as input to Split_SOPLD_Row.
720
721 PROCEDURE Split_SOPLD_Row
722 (X_picking_line_id in number,
723 X_picking_line_detail_id in number,
724 X_pick_slip_number in number,
725 new_pld_id in out number,
726 X_shipped_quantity in number,
727 X_warehouse_id in number,
728 X_sn in varchar2,
729 X_lot_number in varchar2,
730 X_revision in varchar2,
731 X_sub in varchar2,
732 X_loc in varchar2,
733 x_departure_id in number,
734 x_delivery_id in number,
735 x_container_id in number,
736 x_backorder_flag in boolean,
737 x_context in varchar2,
738 x_att1 in varchar2, x_att2 in varchar2, x_att3 in varchar2, x_att4 in varchar2,
739 x_att5 in varchar2, x_att6 in varchar2, x_att7 in varchar2, x_att8 in varchar2,
740 x_att9 in varchar2, x_att10 in varchar2, x_att11 in varchar2, x_att12 in varchar2,
741 x_att13 in varchar2, x_att14 in varchar2, x_att15 in varchar2,
742 error_code in out varchar) is
743
744 BEGIN
745 error_code := '0';
746
747 wsh_del_oi_core.println('Inside Split_SOPLD_Row: x_shipped_qty:'||to_char(X_shipped_quantity));
748
749 --------------------------------------------------------------------------
750 -- decrement the requested qty on the original detail line by the ship qty
751 -- of the detail line we are trying to insert/update
752 --------------------------------------------------------------------------
753 WSH_SC_TRX_INTERFACE.update_requested_quantity (X_picking_line_detail_id, X_shipped_quantity,
754 X_backorder_flag, error_code);
755
756 --------------------------------------------------------------------------
757 -- before inserting a new detail line, try updating any line detail
758 -- for this picking line with the same inventory controls
759 -- if serial_number exists we assume there is no such line and skip this
760 --------------------------------------------------------------------------
761 if error_code = '0' then
762 WSH_SC_TRX_INTERFACE.insert_sopld_row
763 (X_picking_line_detail_id,
764 new_pld_id,
765 X_pick_slip_number,
766 X_shipped_quantity,
767 X_shipped_quantity,
768 X_warehouse_id,
769 X_sn,
770 X_lot_number,
771 X_revision,
772 X_sub,
773 X_loc,
774 x_departure_id,
775 x_delivery_id,
776 x_container_id,
777 x_context,
778 x_att1, x_att2, x_att3, x_att4,
779 x_att5, x_att6, x_att7, x_att8,
780 x_att9, x_att10, x_att11, x_att12,
781 x_att13, x_att14, x_att15);
782 end if;
783
784 EXCEPTION when others then
785 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
786 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.split_sopld_row');
787 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
788 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
789 APP_EXCEPTION.Raise_Exception;
790
791 END split_SOPLD_row;
792
793
794
795
796
797 -----------------------------------------------------------------------------------
798
799 PROCEDURE PROCESS_PICKING_DETAILS_INTER(X_TRANSACTION_ID IN NUMBER,
800 X_departure_id in number,
801 X_delivery_id in number,
802 X_warehouse_id in number,
803 X_rowid in out varchar2,
807
804 X_backorder_flag in boolean ,
805 x_error_code in out varchar2) is
806 BEGIN
808 DECLARE
809 last_item_id number;
810 last_warehouse_id number;
811 last_sub varchar2(10);
812
813 valid_item_id number;
814 valid_warehouse_id number;
815 valid_revision varchar2(3);
816 valid_sub varchar2(10);
817 valid_lot varchar2(30);
818 valid_loc number;
819
820
821 default_sub varchar2(10);
822 default_loc number;
823 valid_flag boolean;
824
825 x_reservations varchar2(1);
826 x_subinv_restricted_flag varchar2(1);
827 x_revision_control_flag varchar2(1);
828 x_lot_control_flag varchar2(1);
829 x_location_control_flag varchar2(1);
830 x_location_restricted_flag varchar2(1);
831 x_serial_number_control_flag varchar2(1);
832 x_picking_header_id number;
833 x_picking_line_id number;
834 x_container_id number;
835 x_picking_line_detail_id number;
836
837 del_status varchar2(2);
838 result boolean;
839 result_num number;
840 token_name varchar2(30);
841
842 processed_records boolean;
843
844 error_line number;
845 error_code varchar2(240);
846
847 stop_pld_processing EXCEPTION;
848
849 seg_array FND_FLEX_EXT.SegmentArray;
850
851
852 pl_pld_id number;
853 pl_shp_qty number;
854 pl_req_qty number;
855 new_pld_id number;
856 pl_del number;
857 pl_cont number;
858
859 -- Reservations Transfer Variables
860
861 cannot_transfer EXCEPTION;
862 online_no_manager EXCEPTION;
863 online_error EXCEPTION;
864 ret_val NUMBER := 0;
865 success BOOLEAN;
866 outcome VARCHAR2(30);
867 message VARCHAR2(128);
868 a1 VARCHAR2(80);
869 a2 VARCHAR2(30);
870 a3 VARCHAR2(30);
871 a4 VARCHAR2(30);
872 a5 VARCHAR2(30);
873 a6 VARCHAR2(30);
874 a7 VARCHAR2(30);
875 a8 VARCHAR2(30);
876 a9 VARCHAR2(30);
877 a10 VARCHAR2(30);
878 a11 VARCHAR2(30);
879 a12 VARCHAR2(30);
880 a13 VARCHAR2(30);
881 a14 VARCHAR2(30);
882 a15 VARCHAR2(30);
883 a16 VARCHAR2(30);
884 a17 VARCHAR2(30);
885 a18 VARCHAR2(30);
886 a19 VARCHAR2(30);
887 a20 VARCHAR2(30);
888 return_msg1 VARCHAR2(2000);
889
890
891 CURSOR Open_Interface is
892 select sopldi.transaction_id,
893 sopldi.picking_line_detail_id,
894
895 ----------------------------------------------------------------------
896 -- items to validate
897 ----------------------------------------------------------------------
898 sopldi.inventory_item_id item_id,
899 sopldi.inventory_item item_concat_segments,
900 sopldi.warehouse_id warehouse_id,
901 sopldi.serial_number sn,
902 sopldi.lot_number lot_number,
903 sopldi.revision revision,
904 sopldi.subinventory subinventory,
905 sopldi.locator_id locator_id,
906 sopldi.locator_name locator_concat_segments,
907 sopldi.container_id container_id,
908 sopldi.container_sequence container_sequence,
909 sopldi.attribute_category context,
910 sopldi.attribute1,
911 sopldi.attribute2,
912 sopldi.attribute3,
913 sopldi.attribute4,
914 sopldi.attribute5,
915 sopldi.attribute6,
916 sopldi.attribute7,
917 sopldi.attribute8,
918 sopldi.attribute9,
919 sopldi.attribute10,
920 sopldi.attribute11,
921 sopldi.attribute12,
922 sopldi.attribute13,
923 sopldi.attribute14,
924 sopldi.attribute15,
925
926 ----------------------------------------------------------------------
927 -- Inventory Item Segments
928 ----------------------------------------------------------------------
929 sopldi.item_segment1,
930 sopldi.item_segment2,
931 sopldi.item_segment3,
932 sopldi.item_segment4,
933 sopldi.item_segment5,
934 sopldi.item_segment6,
935 sopldi.item_segment7,
936 sopldi.item_segment8,
937 sopldi.item_segment9,
938 sopldi.item_segment10,
939 sopldi.item_segment11,
940 sopldi.item_segment12,
941 sopldi.item_segment13,
942 sopldi.item_segment14,
943 sopldi.item_segment15,
944 ----------------------------------------------------------------------
945 -- Location Segments
946 ----------------------------------------------------------------------
950 sopldi.loc_segment4,
947 sopldi.loc_segment1,
948 sopldi.loc_segment2,
949 sopldi.loc_segment3,
951 sopldi.loc_segment5,
952 sopldi.loc_segment6,
953 sopldi.loc_segment7,
954 sopldi.loc_segment8,
955 sopldi.loc_segment9,
956 sopldi.loc_segment10,
957 sopldi.loc_segment11,
958 sopldi.loc_segment12,
959 sopldi.loc_segment13,
960 sopldi.loc_segment14,
961 sopldi.loc_segment15,
962
963 sopldi.shipped_quantity shipped_quantity,
964 rowidtochar(sopldi.rowid) row_id,
965
966 ----------------------------------------------------------------------
967 -- Valid items from so tables
968 ----------------------------------------------------------------------
969 sopl.picking_header_id,
970 sopl.picking_line_id,
971 sopl.inventory_item_id sopld_item_id,
972 sopld.warehouse_id sopld_warehouse_id,
973 sopld.pick_slip_number,
974 sopld.delivery_id sopld_delivery_id,
975 sopld.serial_number sopld_sn,
976 sopld.lot_number sopld_lot_number,
977 sopld.revision sopld_revision,
978 sopld.subinventory sopld_subinventory,
979 sopld.inventory_location_id sopld_locator_id,
980 sopld.dpw_assigned_flag sopld_dpw_assigned_flag,
981
982 DECODE(X_Reservations,'Y',
983 DECODE(sopld.reservable_flag,'Y','Y','N'),'N') RESERVATION_PLACED
984
985 FROM so_picking_lines_all SOPL,
986 so_picking_line_details SOPLD,
987 wsh_picking_details_interface SOPLDI
988 WHERE sopldi.transaction_id = X_transaction_id
989 and sopldi.picking_line_detail_id = sopld.picking_line_detail_id
990 and sopl.picking_line_id = sopld.picking_line_id
991 ORDER BY sopl.inventory_item_id;
992
993
994 BEGIN
995 ------------------------------------------------------------------------------
996 -- Get reservations profile
997 ------------------------------------------------------------------------------
998 fnd_profile.get( 'SO_RESERVATIONS', X_Reservations);
999 wsh_del_oi_core.println('profile option SO_RESERVATIONS:'||X_Reservations );
1000
1001
1002 ------------------------------------------------------------------------------
1003 -- Define local variables
1004 ------------------------------------------------------------------------------
1005 last_item_id := -1 ;
1006 last_warehouse_id := -1 ;
1007 last_sub := 'a';
1008 processed_records := FALSE;
1009
1010 wsh_del_oi_core.println('START OF PROCESS_SOPLD_INTERFACE' );
1011
1012 ------------------------------------------------------------------------------
1013 -- Loop on interface records
1014 ------------------------------------------------------------------------------
1015 FOR oirec IN Open_Interface LOOP
1016
1017 X_rowid := oirec.row_id;
1018 processed_records := TRUE;
1019
1020 wsh_del_oi_core.println('START OF LOOP oirec.sopld_subinventory: '||oirec.sopld_subinventory||
1021 ' pld_id: '|| to_char(oirec.picking_line_detail_id) );
1022
1023 ------------------------------------------------------------------------------
1024 -- VALIDATE WAREHOUSE
1025 -- Warehouse will never be null.
1026 ------------------------------------------------------------------------------
1027 wsh_del_oi_core.println(' ');
1028 wsh_del_oi_core.println('Validating warehouse IDs: warehouse: '|| to_char(oirec.warehouse_id) ||
1029 ' sopld_warehouse: '||to_char(oirec.sopld_warehouse_id) );
1030 wsh_del_oi_core.println(' ');
1031 error_line := 100;
1032 error_code := '0';
1033
1034 wsh_oi_validate.not_equal(oirec.sopld_warehouse_id, oirec.warehouse_id, 'WAREHOUSE', error_code);
1035 if error_code <> '0' then
1036 wsh_del_oi_core.println(' ');
1037 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 100 ');
1038 wsh_del_oi_core.println(' ');
1039 FND_MESSAGE.SET_NAME('OE', error_code);
1040 raise stop_pld_processing;
1041 end if;
1042
1043
1044 ------------------------------------------------------------------------------
1045 -- VALIDATE LINES WITH WAREHOUSE
1046 -- Ensure lines belong with this delivery's warehouse
1047 ------------------------------------------------------------------------------
1048 wsh_del_oi_core.println(' ');
1049 wsh_del_oi_core.println('Validating lines belong with this delivery warehouse '||
1050 ' x_warehouse: '|| to_char(x_warehouse_id) );
1051 wsh_del_oi_core.println(' ');
1052 error_line := 110;
1053 error_code := '0';
1054
1055 wsh_oi_validate.not_equal(oirec.sopld_warehouse_id, x_warehouse_id, 'WAREHOUSE', error_code);
1056 if error_code <> '0' then
1057 wsh_del_oi_core.println(' ');
1058 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 110 ');
1059 wsh_del_oi_core.println(' ');
1060 error_code := 'WSH_OI_LINES_NOT_IN_WHS';
1061 FND_MESSAGE.SET_NAME('OE', error_code);
1062 raise stop_pld_processing;
1063 end if;
1064
1065
1066 ------------------------------------------------------------------------------
1067 -- VALIDATE INVENTORY ITEM
1068 ------------------------------------------------------------------------------
1069 wsh_del_oi_core.println(' ');
1070 wsh_del_oi_core.println('Validating item id: '|| to_char(oirec.item_id) );
1071 wsh_del_oi_core.println(' ');
1072 error_line := 120;
1073 error_code := '0';
1074
1075 seg_array(1) := oirec.item_segment1; seg_array(2) := oirec.item_segment2;
1076 seg_array(3) := oirec.item_segment3; seg_array(4) := oirec.item_segment4;
1077 seg_array(5) := oirec.item_segment5; seg_array(6) := oirec.item_segment6;
1078 seg_array(7) := oirec.item_segment7; seg_array(8) := oirec.item_segment8;
1079 seg_array(9) := oirec.item_segment9; seg_array(10) := oirec.item_segment10;
1080 seg_array(11) := oirec.item_segment11; seg_array(12) := oirec.item_segment12;
1081 seg_array(13) := oirec.item_segment13; seg_array(14) := oirec.item_segment14;
1082 seg_array(15) := oirec.item_segment15;
1083
1084 wsh_oi_validate.inventory_item(oirec.item_id, oirec.item_concat_segments,
1085 oirec.warehouse_id, oirec.sopld_item_id,
1086 valid_item_id, seg_array, error_code);
1087
1088 if error_code <> '0' then
1089 wsh_del_oi_core.println(' ');
1090 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 120 ');
1091 wsh_del_oi_core.println(' ');
1092 FND_MESSAGE.SET_NAME('OE', error_code);
1093 raise stop_pld_processing;
1094 end if;
1095
1096
1097 ------------------------------------------------------------------------------
1098 -- VALIDATE VARIABLES
1099 -- Validate reseting variables if org/item have changed
1100 ------------------------------------------------------------------------------
1101 wsh_del_oi_core.println(' ');
1102 wsh_del_oi_core.println('Validating reseting variables if org/item changed ');
1103 wsh_del_oi_core.println(' ');
1104 error_line := 130;
1105 error_code := '0';
1106 wsh_oi_validate.changed_item_org(oirec.warehouse_id,
1107 last_warehouse_id,
1108 valid_item_id,
1109 last_item_id,
1110 X_subinv_restricted_flag,
1111 X_revision_control_flag,
1112 X_lot_control_flag,
1113 X_serial_number_control_flag,
1114 error_code);
1115 if error_code <> '0' then
1116 wsh_del_oi_core.println(' ');
1117 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 130 ');
1118 wsh_del_oi_core.println(' ');
1119 FND_MESSAGE.SET_NAME('OE', error_code);
1120 raise stop_pld_processing;
1121 end if;
1122
1123
1124 ------------------------------------------------------------------------------
1125 -- VALIDATE SHIPPED QUANTITIES
1126 -- Validate negative or decimal shipped quantities
1127 ------------------------------------------------------------------------------
1128 wsh_del_oi_core.println(' ');
1129 wsh_del_oi_core.println('Validating negative or decimal shipped quantities: '||
1130 to_char( oirec.shipped_quantity) );
1131 wsh_del_oi_core.println(' ');
1132 error_line := 140;
1133 error_code := '0';
1134 wsh_oi_validate.qty(oirec.shipped_quantity, error_code);
1135
1136 if error_code <> '0' then
1137 wsh_del_oi_core.println(' ');
1138 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 140 ');
1139 wsh_del_oi_core.println(' ');
1140 FND_MESSAGE.SET_NAME('OE', error_code);
1141 raise stop_pld_processing;
1142 end if;
1143
1144
1145 ------------------------------------------------------------------------------
1146 -- set all valid-variables to null. These will store
1147 -- all validated input data and are used for the final update
1148 -- of Oracle Shipping tables.
1149 ------------------------------------------------------------------------------
1150
1151 valid_warehouse_id := oirec.sopld_warehouse_id;
1152 valid_revision := NULL;
1153 valid_sub := NULL;
1154 valid_lot := NULL;
1155 valid_loc := NULL;
1156
1157 ------------------------------------------------------------------------------
1158 -- if a reservation, ensure no interface controls, if input are the same
1159 ------------------------------------------------------------------------------
1160
1161 if oirec.reservation_placed = 'Y' then
1162
1163 --------------------------------------------------------------------------
1164 -- Validate if a reservation, ensure no interface controls, if input are
1165 -- the same if any inventory controls have changed from the reservation
1166 -- then raise error
1167 --------------------------------------------------------------------------
1168 wsh_del_oi_core.println(' ');
1169 wsh_del_oi_core.println('Validating if any inventory controls have changed');
1170 wsh_del_oi_core.println(' ');
1171 error_line := 150;
1172 error_code := '0';
1173
1174 wsh_oi_validate.res_inv_ctrl_change(oirec.lot_number, oirec.sopld_lot_number,
1175 oirec.revision, oirec.sopld_revision,
1176 oirec.subinventory, oirec.sopld_subinventory,
1177 oirec.locator_id, oirec.sopld_locator_id,
1178 error_code);
1179 if error_code <> '0' then
1180 wsh_del_oi_core.println(' ');
1181 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 150 ');
1182 wsh_del_oi_core.println(' ');
1183 FND_MESSAGE.SET_NAME('OE', error_code);
1184 raise stop_pld_processing;
1185 end if;
1186
1187 --------------------------------------------------------------------------
1188
1189 valid_revision := nvl(oirec.revision,oirec.sopld_revision);
1190 valid_sub := nvl(oirec.subinventory,oirec.sopld_subinventory);
1191 valid_loc := nvl(oirec.locator_id,oirec.sopld_locator_id);
1192 valid_lot := nvl(oirec.lot_number,oirec.sopld_lot_number);
1193
1194 -- Bug 916149: Loc Control flags should be set even if reservation
1195 -- is placed. else check_serial_number routine would fail, if item
1196 -- is serial & locator controlled. Get locator_control if
1197 -- item/org/sub have changed
1198 if valid_warehouse_id <> last_warehouse_id
1199 or valid_item_id <> last_item_id
1200 or valid_sub <> last_sub
1201 then
1202 WSH_DEL_OI_CORE.get_locator_controls
1203 ( valid_warehouse_id,
1204 valid_item_id,
1205 valid_sub,
1206 X_location_control_flag,
1207 X_location_restricted_flag,
1208 error_code);
1209 if error_code <> '0' then
1210 EXIT;
1211 end if;
1212 wsh_del_oi_core.println('Loc Ctrl:'||X_location_control_flag||
1213 ' loc restricted:'||X_location_restricted_flag);
1214 end if;
1215 -- Bug: 916149 (701829)
1216
1217 wsh_del_oi_core.println('Reservation is set, Inv Controls set as Sub '||valid_sub||
1218 ' revison='||valid_revision||' loc='||to_char(valid_loc)
1219 ||' lot='||valid_lot);
1220
1221
1222 --------------------------------------------------------------------------
1223 -- else this is NOTa reservation, so validate the interface controls
1224 -- if shipped quantity exists
1225 --------------------------------------------------------------------------
1226
1227 elsif nvl(oirec.shipped_quantity,0) > 0 then
1228
1229 --------------------------------------------------------------------------
1230 -- VALIDATE PLD SUBINVENTORY
1231 --------------------------------------------------------------------------
1232 wsh_del_oi_core.println(' ');
1233 wsh_del_oi_core.println('Validating Picking Line subinventory'||
1234 ' sub: '|| oirec.subinventory||
1235 ' warehouse:'||to_char(valid_warehouse_id)||
1236 ' restrict_flag:'||X_subinv_restricted_flag||
1237 ' item:'||to_char(valid_item_id)||
1238 ' valid_sub:'||valid_sub||
1239 ' default_sub:'||valid_sub);
1240 wsh_del_oi_core.println(' ');
1241 error_line := 160;
1242 error_code := '0';
1243 wsh_oi_validate.pld_subinventory(oirec.subinventory,
1244 oirec.sopld_subinventory,
1245 valid_warehouse_id,
1246 valid_item_id,
1247 X_subinv_restricted_flag,
1248 valid_sub,
1249 default_sub,
1250 error_code);
1251 if error_code <> '0' then
1252 wsh_del_oi_core.println(' ');
1253 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 160 ');
1254 wsh_del_oi_core.println(' ');
1255 FND_MESSAGE.SET_NAME('OE', error_code);
1256 raise stop_pld_processing;
1257 end if;
1258
1259 --------------------------------------------------------------------------
1260 -- VALIDATE PLD LOT NUMBER
1261 --------------------------------------------------------------------------
1262 wsh_del_oi_core.println(' ');
1263 wsh_del_oi_core.println('Validating Picking Line Lot Number'||
1264 ' valid_lot: '||valid_lot);
1265 wsh_del_oi_core.println(' ');
1266 error_line := 170;
1267 error_code := '0';
1268 wsh_oi_validate.pld_lot_number(oirec.lot_number,
1269 X_lot_control_flag,
1270 oirec.sopld_lot_number,
1271 valid_warehouse_id,
1272 valid_item_id,
1273 valid_sub,
1274 valid_lot,
1275 error_code);
1276 if error_code <> '0' then
1277 wsh_del_oi_core.println(' ');
1278 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 170 ');
1279 wsh_del_oi_core.println(' ');
1280 FND_MESSAGE.SET_NAME('OE', error_code);
1281 raise stop_pld_processing;
1282 end if;
1283
1284 --------------------------------------------------------------------------
1285 -- VALIDATE PLD REVISION
1286 --------------------------------------------------------------------------
1287 wsh_del_oi_core.println(' ');
1288 wsh_del_oi_core.println('Validating Picking Line Revision' ||
1289 ' revision:'|| valid_revision);
1290 wsh_del_oi_core.println(' ');
1291 error_line := 180;
1292 error_code := '0';
1293 wsh_oi_validate.pld_revision(oirec.revision,
1294 oirec.sopld_revision,
1295 valid_warehouse_id,
1296 valid_item_id,
1297 valid_revision,
1298 x_revision_control_flag,
1299 error_code);
1300 if error_code <> '0' then
1301 wsh_del_oi_core.println(' ');
1302 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 180 ');
1303 wsh_del_oi_core.println(' ');
1304 FND_MESSAGE.SET_NAME('OE', error_code);
1305 raise stop_pld_processing;
1306 end if;
1307
1308
1309 --------------------------------------------------------------------------
1310 -- VALIDATE PLD ITEM LOCATION
1311 --------------------------------------------------------------------------
1312 wsh_del_oi_core.println(' ');
1313 wsh_del_oi_core.println('Validating Picking Line Item Loc'||
1314 ' loc_control_flag: '||X_location_control_flag||
1315 ' valid_loc:'||valid_loc);
1316 wsh_del_oi_core.println(' ');
1317 error_line := 190;
1318 error_code := '0';
1319 wsh_oi_validate.pld_item_location(valid_warehouse_id,
1320 last_warehouse_id,
1321 valid_item_id,
1322 last_item_id,
1323 valid_sub,
1324 last_sub,
1325 X_location_control_flag,
1326 X_location_restricted_flag,
1327 valid_loc,
1328 error_code);
1329
1330
1331 if error_code <> '0' then
1332 wsh_del_oi_core.println(' ');
1333 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 190 ');
1334 wsh_del_oi_core.println(' ');
1335 FND_MESSAGE.SET_NAME('OE', error_code);
1336 raise stop_pld_processing;
1337 end if;
1338
1339 --------------------------------------------------------------------------
1340 -- VALIDATE PLD LOCATOR
1341 --------------------------------------------------------------------------
1342 wsh_del_oi_core.println(' ');
1343 wsh_del_oi_core.println('Validating Picking Line Locator');
1344 wsh_del_oi_core.println(' ');
1345 error_line := 200;
1346 error_code := '0';
1347
1348 seg_array(1) := oirec.loc_segment1; seg_array(2) := oirec.loc_segment2;
1349 seg_array(3) := oirec.loc_segment3; seg_array(4) := oirec.loc_segment4;
1350 seg_array(5) := oirec.loc_segment5; seg_array(6) := oirec.loc_segment6;
1351 seg_array(7) := oirec.loc_segment7; seg_array(8) := oirec.loc_segment8;
1352 seg_array(9) := oirec.loc_segment9; seg_array(10) := oirec.loc_segment10;
1353 seg_array(11) := oirec.loc_segment11; seg_array(12) := oirec.loc_segment12;
1354 seg_array(13) := oirec.loc_segment13; seg_array(14) := oirec.loc_segment14;
1355 seg_array(15) := oirec.loc_segment15;
1356
1357 dbms_output.enable(1000000);
1358 wsh_oi_validate.pld_locator(oirec.locator_id,
1359 oirec.sopld_locator_id,
1360 valid_loc,
1361 default_loc,
1362 oirec.locator_concat_segments,
1363 X_location_control_flag,
1364 valid_warehouse_id,
1365 valid_item_id,
1366 valid_sub,
1367 X_location_restricted_flag,
1368 valid_flag,
1369 seg_array,
1370 error_code);
1371
1372 if error_code <> '0' then
1373 wsh_del_oi_core.println(' ');
1374 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 200 ');
1375 wsh_del_oi_core.println(' ');
1376 FND_MESSAGE.SET_NAME('OE', error_code);
1377 raise stop_pld_processing;
1378 end if;
1379 -------------------------------------------------------------------------
1380
1381
1382 end if; -- ending reservation_placed else statement
1383
1384
1385 --------------------------------------------------------------------------
1386 -- VALIDATE PLD SERIAL NUMBER
1387 -- If present irrespective of reservations
1388 --------------------------------------------------------------------------
1389 wsh_del_oi_core.println(' ');
1390 wsh_del_oi_core.println('Validating Picking Line Serial Numbers:'||oirec.sn);
1391 wsh_del_oi_core.println(' ');
1392 error_line := 210;
1393 error_code := '0';
1394 wsh_oi_validate.pld_serial_number(oirec.sn,
1395 X_serial_number_control_flag,
1396 oirec.shipped_quantity,
1397 valid_warehouse_id,
1398 valid_item_id,
1399 valid_sub,
1400 valid_revision,
1401 valid_lot,
1402 valid_loc,
1403 X_location_restricted_flag,
1404 X_location_control_flag,
1405 oirec.row_id,
1406 oirec.picking_line_id,
1407 oirec.picking_line_detail_id,
1408 error_code);
1409 if error_code <> '0' then
1410 wsh_del_oi_core.println(' ');
1411 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 210 ');
1412 wsh_del_oi_core.println(' ');
1413 FND_MESSAGE.SET_NAME('OE', error_code);
1414 raise stop_pld_processing;
1415 end if;
1416
1417 --------------------------------------------------------------------------
1418 -- VALIDATE PLD CONTAINER
1419 --------------------------------------------------------------------------
1420 wsh_del_oi_core.println(' ');
1421 wsh_del_oi_core.println('Validating Picking Line Container:'|| to_char(oirec.container_id)||
1422 ' seq:'||to_char(oirec.container_sequence) );
1423 wsh_del_oi_core.println(' ');
1424 error_line := 220;
1425 error_code := '0';
1426
1427 wsh_oi_validate.container(oirec.container_id,
1428 oirec.container_sequence,
1429 x_delivery_id,
1430 x_container_id,
1431 error_code);
1432 if error_code <> '0' then
1433 wsh_del_oi_core.println(' ');
1434 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 220 ');
1435 wsh_del_oi_core.println(' ');
1436 FND_MESSAGE.SET_NAME('OE', error_code);
1437 raise stop_pld_processing;
1438 end if;
1439
1440
1441 --------------------------------------------------------------------------
1442 -- VALIDATE PLD LINE ADDITION
1443 --------------------------------------------------------------------------
1444 wsh_del_oi_core.println(' ');
1445 wsh_del_oi_core.println('Validating Picking Line Addition of Line');
1446 wsh_del_oi_core.println(' ');
1447 error_line := 230;
1448 error_code := '0';
1449 wsh_oi_validate.pld_line_add(x_delivery_id,
1450 oirec.sopld_delivery_id,
1451 del_status,
1452 result,
1453 result_num,
1454 oirec.picking_line_detail_id,
1455 token_name,
1456 error_code);
1457 if error_code <> '0' then
1458 wsh_del_oi_core.println(' ');
1459 wsh_del_oi_core.println('Validation Error in PROCESS_SOPLD_INTERFACE Error Line = 230 ');
1460 wsh_del_oi_core.println(' ');
1461 FND_MESSAGE.SET_NAME('OE', error_code);
1462 raise stop_pld_processing;
1463 end if;
1464
1465
1466 --------------------------------------------------------------------------
1467 -- At this point we have validated the data and want to update SOPLD.
1468 -- Update the picking_line_detail_id directly and check it has the same inventory
1469 -- controls. If it doesnt then call split_SOPLD_row to handle the update.
1470 --------------------------------------------------------------------------
1471
1472 SELECT picking_line_detail_id, shipped_quantity, requested_quantity, delivery_id, container_id
1473 INTO pl_pld_id, pl_shp_qty, pl_req_qty, pl_del, pl_cont
1474 FROM SO_PICKING_LINE_DETAILS
1475 WHERE picking_line_detail_id = oirec.picking_line_detail_id;
1476
1477 wsh_del_oi_core.println('-----After selection from pld');
1478
1479 -- if oirec.picking_line_detail_id = pl_pld_id then
1480 if ((oirec.shipped_quantity + NVL(pl_shp_qty, 0)) > pl_req_qty) then
1481 error_code := '1';
1482 wsh_del_oi_core.println('Shipped Quantity > Requested Quantity');
1483 raise stop_pld_processing;
1484 elsif oirec.shipped_quantity = pl_req_qty then
1485 wsh_del_oi_core.println('Going to update_line label');
1486 goto update_line;
1487 else -- Split Lines
1488 wsh_del_oi_core.println('Going for split_line');
1489 if x_backorder_flag = false then
1490 wsh_del_oi_core.println('BACKORDER FLAG is FALSE.Going for split of:'||
1491 ' pld:'|| to_char(oirec.picking_line_detail_id));
1492 WSH_SC_TRX_INTERFACE.split_sopld_row
1493 (oirec.picking_line_id,
1494 oirec.picking_line_detail_id,
1495 oirec.pick_slip_number,
1496 new_pld_id,
1497 oirec.shipped_quantity,
1498 valid_warehouse_id,
1499 oirec.sn,
1500 valid_lot,
1501 valid_revision,
1502 valid_sub,
1503 valid_loc,
1504 x_departure_id,
1505 x_delivery_id,
1506 x_container_id,
1507 x_backorder_flag,
1508 oirec.context,
1509 oirec.attribute1, oirec.attribute2, oirec.attribute3, oirec.attribute4,
1510 oirec.attribute5, oirec.attribute6, oirec.attribute7, oirec.attribute8,
1511 oirec.attribute9, oirec.attribute10, oirec.attribute11, oirec.attribute12,
1512 oirec.attribute13, oirec.attribute14, oirec.attribute15,
1513 error_code);
1514
1515 if error_code = '0' and x_reservations = 'Y' then
1516 -- Reservations transfer
1517
1518 COMMIT;
1519
1520 wsh_del_oi_core.println('Calling Reservations transfer 1');
1521 ret_val := Fnd_Transaction.synchronous( 1000,
1522 outcome,
1523 message,
1524 'OE',
1525 'WSHURTF',
1526 TO_CHAR(oirec.picking_line_detail_id),
1527 TO_CHAR(new_pld_id),
1528 TO_CHAR(oirec.shipped_quantity),
1529 TO_CHAR(x_delivery_id));
1530 if (ret_val = 2) then
1531 error_code := 'SHP_ONLINE_NO_MANAGER';
1532 FND_MESSAGE.SET_NAME('OE', error_code);
1533 RAISE online_no_manager;
1534 elsif (ret_val <> 0) then
1535 error_code := 'SHP_AOL_ONLINE_FAILED';
1536 FND_MESSAGE.SET_NAME('OE', error_code);
1537 RAISE online_error;
1538 else
1539 if (message = 'FAILURE') then
1540 error_code := 'WSH_SC_CANNOT_TRANSFER_PLD';
1541 FND_MESSAGE.SET_NAME('OE', error_code);
1542 RAISE cannot_transfer;
1543 end if;
1544 end if;
1545 else
1546 wsh_del_oi_core.println('CALLING goto_end_processing');
1547 goto end_processing;
1548 end if; -- Reservations transfer
1549
1550 -- After reservation transfer the departure & Delivery_id dpw_assigned_flag
1551 -- and the serial_number in the new pld is NULLED,
1552
1553 BEGIN
1554 wsh_del_oi_core.println('Into reseting pld after reservation split. pld:'||
1555 to_char(new_pld_id) );
1556 UPDATE SO_PICKING_LINE_DETAILS SET
1557 DELIVERY_ID = nvl(DELIVERY_ID,x_delivery_id),
1558 DEPARTURE_ID = nvl(DEPARTURE_ID,x_departure_id),
1559 SERIAL_NUMBER = nvl(SERIAL_NUMBER,oirec.sn),
1560 SHIPPED_QUANTITY = NVL(SHIPPED_QUANTITY,0) + oirec.shipped_quantity,
1561 DPW_ASSIGNED_FLAG = NULL,
1562 CONTAINER_ID = nvl(CONTAINER_ID,x_container_id),
1563 WAREHOUSE_ID = nvl(WAREHOUSE_ID,valid_warehouse_id),
1564 LAST_UPDATE_DATE = sysdate,
1565 LAST_UPDATED_BY = fnd_global.user_id,
1566 LAST_UPDATE_LOGIN = fnd_global.user_id
1567 WHERE PICKING_LINE_DETAIL_ID = new_pld_id;
1568
1569 if sql%notfound then
1570 wsh_del_oi_core.println('Could not find record in PLD. pld:'||
1571 to_char(new_pld_id) );
1572 error_code := '1';
1573 FND_MESSAGE.SET_NAME('OE' , error_code);
1574 raise stop_pld_processing;
1575 end if;
1576
1577 EXCEPTION when others then
1578 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1579 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.update_sopld1');
1580 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1581 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1582 APP_EXCEPTION.Raise_Exception;
1583 END;
1584
1585 -- oirec.picking_line_detail_id := new_pld_id;
1586 -- oirec.shipped_quantity := pl_shp_qty;
1587 -- oirec.sopld_dpw_assigned_flag := NULL;
1588
1589 goto end_processing;
1590
1591 end if; -- a_backorder_flag = FALSE
1592
1593 end if; -- oirec.shipped_quantity + pl_shp_qty > pl_req_qty
1594
1595
1596 <<update_line>>
1597
1598 wsh_del_oi_core.println('Into the Update_line label.');
1599 wsh_del_oi_core.println('Going for update of pld:'||
1600 to_char(oirec.picking_line_detail_id) );
1601
1602 -- Control will reach here ONLY in case of backorder_flag=TRUE
1603 -- this update will fail only if the inventory controls are different & the
1604 -- pld splitting will take place by the next call.
1605
1606 WSH_SC_TRX_INTERFACE.update_sopld_row
1607 (oirec.picking_line_id,
1608 oirec.picking_line_detail_id,
1609 0,
1610 oirec.shipped_quantity,
1611 valid_warehouse_id,
1612 oirec.sn,
1613 valid_lot,
1614 valid_revision,
1615 valid_sub,
1616 valid_loc,
1617 x_departure_id,
1618 x_delivery_id,
1619 x_container_id,
1620 oirec.context,
1621 oirec.sopld_dpw_assigned_flag,
1622 oirec.attribute1, oirec.attribute2, oirec.attribute3, oirec.attribute4,
1623 oirec.attribute5, oirec.attribute6, oirec.attribute7, oirec.attribute8,
1624 oirec.attribute9, oirec.attribute10, oirec.attribute11, oirec.attribute12,
1625 oirec.attribute13, oirec.attribute14, oirec.attribute15,
1626 error_code);
1627
1628
1629 --------------------------------------------------------------------------
1630 -- will return 1 when we werent successful with the insert because the line
1631 -- already exists with different inventory controls so we must split the line
1632 --------------------------------------------------------------------------
1633 if error_code = '1' then
1634 --------------------------------------------------------------------------
1635 -- dont permit zero quantity when something already exists because
1636 -- we're probably trying to reset it to zero
1637 --------------------------------------------------------------------------
1638 if nvl(oirec.shipped_quantity,0) = 0 then
1639 error_code := 'WSH_OI_USE_FORM';
1640 wsh_del_oi_core.println('Shipped qty equal to ZERO.');
1641 EXIT;
1642 end if;
1643
1644 wsh_del_oi_core.println(substr(('Could not update SOPLD using detail_id:: splitting row '||
1645 to_char(oirec.picking_line_id)||','||to_char(oirec.picking_line_detail_id)||','||
1646 to_char(valid_warehouse_id)||','||
1647 valid_lot||','||valid_revision||','||
1648 valid_sub||','||valid_loc),1,2000));
1649
1650 WSH_SC_TRX_INTERFACE.split_sopld_row
1651 (oirec.picking_line_id,
1652 oirec.picking_line_detail_id,
1653 oirec.pick_slip_number,
1654 new_pld_id,
1655 oirec.shipped_quantity,
1656 valid_warehouse_id,
1657 oirec.sn,
1658 valid_lot,
1659 valid_revision,
1660 valid_sub,
1661 valid_loc,
1662 x_departure_id,
1663 x_delivery_id,
1664 x_container_id,
1665 x_backorder_flag,
1666 oirec.context,
1667 oirec.attribute1, oirec.attribute2, oirec.attribute3, oirec.attribute4,
1668 oirec.attribute5, oirec.attribute6, oirec.attribute7, oirec.attribute8,
1669 oirec.attribute9, oirec.attribute10, oirec.attribute11, oirec.attribute12,
1670 oirec.attribute13, oirec.attribute14, oirec.attribute15,
1671 error_code);
1672
1673 end if; -- if error_code = 1
1674
1675 <<end_processing>>
1676
1677 error_code := '0';
1678
1679 DELETE from wsh_picking_details_interface
1680 WHERE picking_line_detail_id = oirec.picking_line_detail_id and rowid = oirec.row_id;
1681
1682 COMMIT;
1683
1684 --------------------------------------------------------------------------
1685 -- update last_variables
1686 --------------------------------------------------------------------------
1687 last_item_id := valid_item_id;
1688 last_warehouse_id := oirec.warehouse_id;
1689 -- remember valid_sub <> oirec.subinventory when default sub is used
1690 last_sub := valid_sub;
1691 x_picking_header_id := oirec.picking_header_id;
1692
1693 end loop;
1694
1695 --------------------------------------------------------------------------
1696 -- PS based prossing did extra stuff here but in DEL we do it at PACK time
1697 --------------------------------------------------------------------------
1698 EXCEPTION
1699
1700 WHEN stop_pld_processing then
1701 wsh_del_oi_core.println('PLD Process EXCEPTION stop_pld_processing');
1702 x_error_code := error_code;
1703 -- at this point error_code should be set and an error is on the stack
1704 -- it will be handled by the calling program.
1705 null;
1706
1707 WHEN online_no_manager THEN
1708 wsh_del_oi_core.println('PLD Process EXCEPTION online_no_manager');
1709 --FND_MESSAGE.SET_NAME('OE','SHP_ONLINE_NO_MANAGER');
1710 x_error_code := error_code;
1711
1712 WHEN online_error THEN
1713 wsh_del_oi_core.println('PLD Process EXCEPTION online_error');
1714 --FND_MESSAGE.SET_NAME('OE','SHP_ONLINE_NO_MANAGER');
1715 --FND_MESSAGE.SET_NAME('OE','SHP_AOL_ONLINE_FAILED');
1716 --FND_MESSAGE.SET_TOKEN('PROGRAM', 'WSHURTF');
1717 x_error_code := error_code;
1718
1719 WHEN cannot_transfer THEN
1720 wsh_del_oi_core.println('PLD Process EXCEPTION cannot_transfer');
1721 ret_val := Fnd_Transaction.get_values( a1, a2, a3, a4, a5, a6,
1722 a7, a8, a9, a10, a11, a12, a13, a14,
1723 a15, a16, a17, a18, a19, a20);
1724 --FND_MESSAGE.Set_Name('OE','WSH_SC_CANNOT_TRANSFER_PLD');
1725 --FND_MESSAGE.Set_Token('PLD_ID', TO_CHAR(new_pld_id));
1726 --FND_MESSAGE.Set_Token('REASON', a1);
1727 x_error_code := error_code;
1728
1729 WHEN others then
1730 return_msg1 := FND_MESSAGE.get;
1731 wsh_del_oi_core.println('msg ='|| return_msg1);
1732 x_error_code := 'OE_QUERY_ERROR';
1733 FND_MESSAGE.SET_NAME('OE', x_error_code);
1734 wsh_del_oi_core.println('PLD Process EXCEPTION others ');
1735 --FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1736 --FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.process_sopld_interface');
1737 --FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1738 --FND_MESSAGE.Set_Token('ORA_TEXT','Insert statement');
1739 --APP_EXCEPTION.Raise_Exception;
1740 END;
1741
1742 END PROCESS_PICKING_DETAILS_INTER;
1743
1744
1745
1746
1747
1748
1749 --------------------------------------------------------------------------
1750 -- Name : PRCOCESS_FREIGHT_CHARGES_INTER
1751 -- Arguments:
1752 -- x_transaction_id interface transaction_id
1753 -- x_delivery_id the delivery we are processing
1754 -- x_del_currency the currency code for DELIVERY CURRENCY
1755 -- x_rowid rowid of the last fc processed.
1756 -- an error will terminate processing of fc
1757 -- so this rowid indicates the record in error.
1758 -- error_code any error code
1759 -- Notes
1760 -- inserts all freight charges for a given transaction.
1761 -- called from WSH_SC_TRX_INTERFACE.Process_Interface_Data
1762 -- once for each transaction to process
1763 -- This can be called any time prior to AR Interface
1764 --------------------------------------------------------------------------
1765
1766
1767 PROCEDURE PROCESS_FREIGHT_CHARGES_INTER(X_transaction_id in number,
1768 X_delivery_id in number,
1769 X_del_currency in out varchar2,
1770 X_rowid out char,
1771 x_error_code in out varchar2) is
1772 BEGIN
1773 DECLARE
1774
1775 cursor Freight_Charge_Cursor is
1776 select creation_date,
1777 created_by,
1778 last_update_date,
1779 last_updated_by,
1780 last_update_login,
1781 freight_charge_type_id,
1782 freight_charge_type_desc,
1783 amount,
1784 currency_code,
1785 currency_name,
1786 decode(delivery_flag,NULL,NULL,x_delivery_id) delivery_id,
1787 container_id, container_sequence,
1788 order_header_id,
1789 picking_line_detail_id,
1790 attribute_category,
1791 attribute1, attribute2, attribute3, attribute4, attribute5,
1792 attribute6, attribute7, attribute8, attribute9, attribute10,
1793 attribute11, attribute12, attribute13, attribute14, attribute15,
1794 ac_attribute_category,
1795 ac_attribute1, ac_attribute2, ac_attribute3, ac_attribute4, ac_attribute5,
1796 ac_attribute6, ac_attribute7, ac_attribute8, ac_attribute9, ac_attribute10,
1797 ac_attribute11,ac_attribute12,ac_attribute13,ac_attribute14,ac_attribute15,
1798 rowidtochar(rowid) char_rowid
1799 from wsh_freight_charges_interface sfci
1800 where transaction_id = X_transaction_id;
1801
1802 fcrec Freight_Charge_Cursor%rowtype;
1803 first_time boolean := TRUE;
1804 stop_fc_processing EXCEPTION;
1805
1806 x_sysdate date;
1807 x_created_by number;
1808 x_login number;
1809 x_type_id number;
1810 x_container_id number;
1811 x_order_header_id number;
1812 x_picking_header_id number;
1813 valid_cur_code varchar2(15);
1814 valid_cur_name varchar2(50);
1815 default_delivery_charge_id number;
1816 delivery_charge_id number;
1817 error_code varchar(70);
1818 return_msg1 varchar(2000);
1819
1820 BEGIN
1821
1822 error_code := '0';
1823 X_sysdate := sysdate;
1824 X_Created_By := FND_GLOBAL.USER_ID;
1825 X_login := FND_GLOBAL.LOGIN_ID;
1826 valid_cur_code := null;
1827
1828 OPEN Freight_Charge_Cursor ;
1829
1830 LOOP
1831
1832 wsh_del_oi_core.println(' ');
1833 wsh_del_oi_core.println('Fetching a record from Freight Charge Interface table');
1834 wsh_del_oi_core.println(' ');
1835
1836 fetch Freight_Charge_cursor into fcrec;
1837
1838 exit when Freight_Charge_Cursor%notfound;
1839
1840 --------------------------------------------------------------------------------------
1841 -- Assign rowid: this will identify the rec if an error occurs
1842 --------------------------------------------------------------------------------------
1843
1844 x_rowid := fcrec.char_rowid;
1845
1846 --------------------------------------------------------------------------------------
1847 -- Validate if the delivery under consideration is already AR Interfaced
1848 -- This basically finds out, if there are any Picking Lines, that have the AR interface
1849 -- status set to Not Null and have the AR Interfaced Flag ( s5 ) set to either 5 =
1850 -- A/R Interfaced Partial, 8 = A/R Interfaced Not Applicable or 9 = A/R Interfaced.
1851 --------------------------------------------------------------------------------------
1852
1853 wsh_del_oi_core.println(' ');
1854 wsh_del_oi_core.println('Validating if the Delivery is already A/R Interfaced');
1855 wsh_del_oi_core.println(' ');
1856 error_code := '0';
1857 if first_time then
1858 wsh_oi_validate.if_ar_intfaced(x_delivery_id, error_code );
1859
1860 if error_code <> '0' then
1861 wsh_del_oi_core.println(' ');
1862 wsh_del_oi_core.println('This Delivery is already A/R Interfaced');
1863 wsh_del_oi_core.println(' ');
1864 FND_MESSAGE.set_name('OE', error_code);
1865 raise stop_fc_processing;
1866 end if;
1867 first_time := FALSE;
1868 end if;
1869
1870
1871 --------------------------------------------------------------------------------------
1872 -- Validate the freight charge type id and desc , if either one or both of them are
1873 -- specified
1874 --------------------------------------------------------------------------------------
1875
1876 wsh_del_oi_core.println(' ');
1877 wsh_del_oi_core.println('Validating Freight Charge Type');
1878 wsh_del_oi_core.println(' ');
1879 error_code := '0';
1880 wsh_oi_validate.freight_charge_type(fcrec.freight_charge_type_id ,
1881 fcrec.freight_charge_type_desc ,
1882 x_type_id , -- Return valid Freight Charge Type ID
1883 error_code );
1884
1885 if (error_code <> '0' ) then
1886 wsh_del_oi_core.println(' ');
1887 wsh_del_oi_core.println('Invalid Freight Charge');
1888 wsh_del_oi_core.println(' ');
1889 FND_MESSAGE.set_name('OE', error_code);
1890 raise stop_fc_processing;
1891 end if;
1892
1893 ----------------------------------------------------------------------------
1894 -- Validate the currency code, when at least the currency code or name, have
1895 -- to be specified
1896 ----------------------------------------------------------------------------
1897
1898 wsh_del_oi_core.println(' ');
1899 wsh_del_oi_core.println('Validating Freight Charge Currency');
1900 wsh_del_oi_core.println(' ');
1901 error_code := '0' ;
1902 wsh_oi_validate.frt_currency_code(fcrec.currency_code ,
1903 fcrec.currency_name ,
1904 fcrec.amount,
1905 valid_cur_code, -- Return valid Currency Code
1906 valid_cur_name,
1907 error_code );
1908
1909 if error_code <> '0' then
1910 wsh_del_oi_core.println(' ');
1911 wsh_del_oi_core.println('Invalid Freight Charge Currency');
1912 wsh_del_oi_core.println(' ');
1913 -- message already on the error stack
1914 raise stop_fc_processing;
1915 end if;
1916
1917 ----------------------------------------------------------------------------
1918 -- Validate if the Freight Charge Currency is the same as the Delivery
1919 -- Currency
1920 ----------------------------------------------------------------------------
1921
1922 wsh_del_oi_core.println(' ');
1923 wsh_del_oi_core.println('Validating if Delivery Currency is same as Freight Charge Currency');
1924 wsh_del_oi_core.println(' ');
1925 error_code := '0';
1926
1927 if nvl(valid_cur_code,'') <> nvl(x_del_currency,'') then
1928 wsh_del_oi_core.println(' ');
1929 wsh_del_oi_core.println('Delivery Currency is not the same as Freight Charge Currency');
1930 wsh_del_oi_core.println(' ');
1931 fnd_message.set_name('OE','WSH_OI_CURRENCY_NOT_SAME'); -- Error Message Not Defined
1932 raise stop_fc_processing;
1933 end if;
1934
1935
1936 ----------------------------------------------------------------------------------
1937 -- Validate the container when either the container_id or sequence_number or both
1938 -- have been specified. If the container_id is specified, then it takes precedence
1939 -- and if not found in WSH_PACKED_CONTAINERS, then it is a validation error. If
1940 -- only the sequence_number of the container has been specified, and it does not
1941 -- exist in WSH_PACKED_CONTAINERS table, then it is a validation error as well.
1942 ----------------------------------------------------------------------------------
1943
1944 wsh_del_oi_core.println(' ');
1945 wsh_del_oi_core.println('Validating Freight Charge Container');
1946 wsh_del_oi_core.println(' ');
1947 error_code := '0' ;
1948 x_container_id := NULL ;
1949
1950 wsh_oi_validate.container(fcrec.container_id,
1951 fcrec.container_sequence,
1952 x_delivery_id,
1953 x_container_id,
1954 error_code);
1955
1956 if error_code <> '0' then
1957 wsh_del_oi_core.println(' ');
1958 wsh_del_oi_core.println('Invalid Freight Charge Container');
1959 wsh_del_oi_core.println(' ');
1960 fnd_message.set_name('OE',error_code);
1961 raise stop_fc_processing;
1962 end if;
1963
1964 ----------------------------------------------------------------------------------
1965 -- Validate if the Freight Charge Amount is Negative or Zero
1966 ----------------------------------------------------------------------------------
1967
1968 wsh_del_oi_core.println(' ');
1969 wsh_del_oi_core.println('Validating Freight Charge Amount');
1970 wsh_del_oi_core.println(' ');
1971 error_code := '0' ;
1972 wsh_oi_validate.qty(fcrec.amount, error_code );
1973
1974 if error_code <> '0' then
1975 wsh_del_oi_core.println(' ');
1976 wsh_del_oi_core.println('Invalid Freight Charge Amount. Either Negative or Zero');
1977 wsh_del_oi_core.println(' ');
1978 fnd_message.set_name('OE',error_code);
1979 raise stop_fc_processing;
1980 end if;
1981
1982 --------------------------------------------------------------------------------
1983 -- Validate for Duplicate AETCs, this is a check specifically for Automotive
1984 --------------------------------------------------------------------------------
1985
1986 wsh_del_oi_core.println(' ');
1987 wsh_del_oi_core.println('Validating if this Delivery has a Duplicate AETC');
1988 wsh_del_oi_core.println(' ');
1989 error_code := '0' ;
1990 wsh_oi_validate.duplicate_aetc(fcrec.ac_attribute_category ,
1991 x_delivery_id, error_code );
1992
1993 if error_code <> '0' then
1994 wsh_del_oi_core.println(' ');
1995 wsh_del_oi_core.println('This Delivery has a Duplicate AETC');
1996 wsh_del_oi_core.println(' ');
1997 fnd_message.set_name('OE',error_code);
1998 raise stop_fc_processing;
1999 end if;
2000
2001 --------------------------------------------------------------------------------
2002 -- Populate order header ID
2003 --------------------------------------------------------------------------------
2004 x_order_header_id := fcrec.order_header_id;
2005
2006 if x_order_header_id is NOT NULL then
2007 select max(ph.picking_header_id) into x_picking_header_id
2008 from so_picking_line_details pld,
2009 so_picking_lines_all pl,
2010 so_picking_headers_all ph,
2011 so_headers_all h
2012 where pld.delivery_id = x_delivery_id
2013 and pld.picking_line_id = pl.picking_line_id
2014 and pl.picking_header_id + 0 > 0
2015 and pl.picking_header_id = ph.picking_header_id
2016 and ph.order_header_id = h.header_id
2017 and h.header_id = x_order_header_id;
2018
2019 if SQL%NOTFOUND then
2020 -- fail transaction
2021 wsh_del_oi_core.println(' ');
2022 wsh_del_oi_core.println('Process failed while selecting order header id');
2023 wsh_del_oi_core.println(' ');
2024 fnd_message.set_name('OE',error_code);
2025 raise stop_fc_processing;
2026 end if;
2027 end if;
2028
2029 --------------------------------------------------------------------------------
2030 wsh_del_oi_core.println(' ');
2031 wsh_del_oi_core.println('Creating a new Freight Charges record');
2032 wsh_del_oi_core.println(' ');
2033 --------------------------------------------------------------------------------
2034
2035 begin
2036 INSERT INTO SO_FREIGHT_CHARGES(
2037 picking_header_id,
2038 freight_charge_id,
2039 creation_date,
2040 created_by,
2041 last_update_date,
2042 last_updated_by,
2043 last_update_login,
2044 freight_charge_type_id,
2045 amount,
2046 currency_code,
2047 delivery_id,
2048 container_id,
2049 picking_line_detail_id,
2050 interco_invoice_status,
2051 context,
2052 attribute1, attribute2, attribute3, attribute4, attribute5,
2053 attribute6, attribute7, attribute8, attribute9, attribute10,
2054 attribute11, attribute12, attribute13, attribute14, attribute15,
2055 ac_attribute_category,
2056 ac_attribute1, ac_attribute2, ac_attribute3, ac_attribute4, ac_attribute5,
2057 ac_attribute6, ac_attribute7, ac_attribute8, ac_attribute9, ac_attribute10,
2058 ac_attribute11,ac_attribute12,ac_attribute13,ac_attribute14,ac_attribute15
2059 ) VALUES (
2060 x_picking_header_id,
2061 so_freight_charges_s.nextval,
2062 sysdate,
2063 x_Created_By,
2064 sysdate,
2065 x_Created_By,
2066 x_login,
2067 x_Type_Id,
2068 nvl(fcrec.amount,0) ,
2069 valid_cur_code,
2070 x_delivery_id,
2071 x_container_id,
2072 fcrec.picking_line_detail_id,
2073 'NOT INVOICED',
2074 fcrec.attribute_category,
2075 fcrec.attribute1, fcrec.attribute2, fcrec.attribute3,
2076 fcrec.attribute4, fcrec.attribute5, fcrec.attribute6,
2077 fcrec.attribute7, fcrec.attribute8, fcrec.attribute9,
2078 fcrec.attribute10, fcrec.attribute11, fcrec.attribute12,
2079 fcrec.attribute13, fcrec.attribute14, fcrec.attribute15,
2080 fcrec.ac_attribute_category,
2081 fcrec.ac_attribute1, fcrec.ac_attribute2, fcrec.ac_attribute3,
2082 fcrec.ac_attribute4, fcrec.ac_attribute5, fcrec.ac_attribute6,
2083 fcrec.ac_attribute7, fcrec.ac_attribute8, fcrec.ac_attribute9,
2084 fcrec.ac_attribute10,fcrec.ac_attribute11,fcrec.ac_attribute12,
2085 fcrec.ac_attribute13,fcrec.ac_attribute14,fcrec.ac_attribute15);
2086
2087 EXCEPTION
2088 when others then
2089 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
2090 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.insert_freight_charges statement');
2091 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
2092 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
2093 APP_EXCEPTION.Raise_Exception;
2094 EXIT;
2095
2096 if ( sql%found ) then
2097 wsh_del_oi_core.println(' ');
2098 wsh_del_oi_core.println('Freight Charge record created successfully');
2099 wsh_del_oi_core.println(' ');
2100 end if ;
2101
2102 end;
2103
2104 DELETE from wsh_freight_charges_interface
2105 WHERE freight_charge_type_id = fcrec.freight_charge_type_id and rowid = fcrec.char_rowid;
2106
2107 wsh_del_oi_core.println('DELETED freight_charge_type_id = '||to_char(fcrec.freight_charge_type_id));
2108
2109 COMMIT;
2110
2111 END LOOP;
2112
2113 EXCEPTION
2114
2115 when stop_fc_processing then
2116 --------------------------------------------------------------------------
2117 -- at this point error_code should be set and an error is on the stack
2118 -- it will be handled by the calling program.
2119 --------------------------------------------------------------------------
2120 x_error_code := error_code;
2121 null;
2122
2123 when others then
2124 return_msg1 := FND_MESSAGE.get;
2125 wsh_del_oi_core.println('msg ='|| return_msg1);
2126 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
2127 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_TRX_INTERFACE.insert_freight_charges');
2128 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
2129 FND_MESSAGE.Set_Token('ORA_TEXT','Insert statement');
2130 APP_EXCEPTION.Raise_Exception;
2131
2132 end;
2133
2134 --x_error_code := error_code;
2135
2136 END PROCESS_FREIGHT_CHARGES_INTER;
2137
2138
2139
2140 end WSH_SC_TRX_INTERFACE;