[Home] [Help]
PACKAGE: APPS.WSH_INTERFACE_EXT_GRP
Source
4
1 PACKAGE WSH_INTERFACE_EXT_GRP AUTHID CURRENT_USER as
2 /* $Header: WSHEXGPS.pls 120.4 2007/12/21 14:57:57 mvudugul noship $ */
3
5 --lpn conv
6 FUNCTION Handle_missing_info
7 (p_value IN NUMBER,
8 p_entity IN VARCHAR2 DEFAULT NULL,
9 p_action IN VARCHAR2 DEFAULT NULL
10 ) RETURN NUMBER;
11 FUNCTION Handle_missing_info
12 (p_value IN VARCHAR2,
13 p_entity IN VARCHAR2 DEFAULT NULL,
14 p_action IN VARCHAR2 DEFAULT NULL
15 ) RETURN VARCHAR2;
16 FUNCTION Handle_missing_info
17 (p_value IN DATE,
18 p_entity IN VARCHAR2 DEFAULT NULL,
19 p_action IN VARCHAR2 DEFAULT NULL
20 ) RETURN DATE;
21 /***************************************************************************************************
22 * IMPORTANT NOTE
23 *
24 * While calling following API, please use FND_API.G_MISS_XXXX to set the value to NULL.
25 * API Parameter
26 * -- ---------
27 * Create_Update_Delivery p_rec_attr_tab
28 * Create_Update_Delivery_Detail p_detail_info_tab
29 * Create_Update_Trip p_trip_info_tab
30 * Create_Update_Stop p_rec_attr_tab
31 * Create_Update_Freight_Costs p_freight_info_tab
32 ***************************************************************************************************/
33
34 --===================
35 -- PUBLIC VARS
36 --===================
37 TYPE del_action_parameters_rectype is RECORD (
38 caller VARCHAR2(50),
39 phase NUMBER,
40 action_code VARCHAR2(30),
41 trip_id NUMBER , --|
42 trip_name VARCHAR2(30) , --|
43 pickup_stop_id NUMBER , --|
44 pickup_loc_id NUMBER , --|
45 pickup_stop_seq NUMBER , --|
46 pickup_loc_code VARCHAR2(80) , --|
47 pickup_arr_date DATE , --| All these parameters
48 pickup_dep_date DATE , --| are used for the
49 pickup_stop_status VARCHAR2(2), --|
50 dropoff_stop_id NUMBER , --| Action
51 dropoff_loc_id NUMBER , --| 'UNASSIGN-TRIP'
52 dropoff_stop_seq NUMBER , --|
53 dropoff_loc_code VARCHAR2(80) , --|
54 dropoff_arr_date DATE , --|
55 dropoff_dep_date DATE , --|
56 dropoff_stop_status VARCHAR2(2), --|
57 action_flag VARCHAR2(1),
58 intransit_flag VARCHAR2(1),
59 close_trip_flag VARCHAR2(1) ,
60 stage_del_flag VARCHAR2(1),
61 bill_of_lading_flag VARCHAR2(1),
62 mc_bill_of_lading_flag VARCHAR2(1),
63 override_flag VARCHAR2(1),
64 defer_interface_flag VARCHAR2(1),
65 ship_method_code VARCHAR2(240) ,
66 actual_dep_date DATE ,
67 report_set_id NUMBER ,
68 report_set_name VARCHAR2(30) ,
69 send_945_flag VARCHAR2(1) ,
70 action_type VARCHAR2(1) ,
71 document_type VARCHAR2(2) ,
72 organization_id NUMBER ,
73 reason_of_transport VARCHAR2(30),
74 description VARCHAR2(30),
75 event VARCHAR2(1) /* Pack J deliveryMerge */ );
76
77
78
79 TYPE Del_Action_Out_Rec_Type is RECORD (
80 packing_slip_number VARCHAR2(50),
81 valid_ids_tab wsh_util_core.id_tab_type,
82 result_id_tab wsh_util_core.id_tab_type,
83 selection_issue_flag VARCHAR2(1)
84 );
85
86
87 TYPE Del_In_Rec_Type is RECORD (
88 caller VARCHAR2(50),
89 phase NUMBER,
90 action_code VARCHAR2(30));
91
92 TYPE Del_Out_Rec_Type is RECORD (
93 delivery_id NUMBER,
94 name VARCHAR2(30),
95 rowid VARCHAR2(4000));
96
97 TYPE Del_Out_Tbl_Type IS Table of Del_Out_Rec_Type INDEX BY BINARY_INTEGER;
98
99
100 TYPE Delivery_Rec_Type IS RECORD (
101 DELIVERY_ID NUMBER,
102 NAME VARCHAR2(30),
103 PLANNED_FLAG VARCHAR2(1),
104 STATUS_CODE VARCHAR2(2),
105 DELIVERY_TYPE VARCHAR2(30),
106 LOADING_SEQUENCE NUMBER,
107 LOADING_ORDER_FLAG VARCHAR2(2),
108 INITIAL_PICKUP_DATE DATE,
109 INITIAL_PICKUP_LOCATION_ID NUMBER,
110 ORGANIZATION_ID NUMBER,
111 ULTIMATE_DROPOFF_LOCATION_ID NUMBER,
112 ULTIMATE_DROPOFF_DATE DATE,
113 CUSTOMER_ID NUMBER,
114 INTMED_SHIP_TO_LOCATION_ID NUMBER,
115 POOLED_SHIP_TO_LOCATION_ID NUMBER,
116 CARRIER_ID NUMBER,
117 SHIP_METHOD_CODE VARCHAR2(30),
118 FREIGHT_TERMS_CODE VARCHAR2(30),
119 FOB_CODE VARCHAR2(30),
120 FOB_LOCATION_ID NUMBER,
121 WAYBILL VARCHAR2(30),
122 DOCK_CODE VARCHAR2(30),
123 ACCEPTANCE_FLAG VARCHAR2(1),
124 ACCEPTED_BY VARCHAR2(150),
125 ACCEPTED_DATE DATE,
126 ACKNOWLEDGED_BY VARCHAR2(150),
127 CONFIRMED_BY VARCHAR2(150),
128 CONFIRM_DATE DATE,
129 ASN_DATE_SENT DATE,
130 ASN_STATUS_CODE VARCHAR2(15),
131 ASN_SEQ_NUMBER NUMBER,
132 GROSS_WEIGHT NUMBER,
133 NET_WEIGHT NUMBER,
134 WEIGHT_UOM_CODE VARCHAR2(3),
135 VOLUME NUMBER,
136 VOLUME_UOM_CODE VARCHAR2(3),
137 ADDITIONAL_SHIPMENT_INFO VARCHAR2(500),
138 CURRENCY_CODE VARCHAR2(15),
139 ATTRIBUTE_CATEGORY VARCHAR2(150),
140 ATTRIBUTE1 VARCHAR2(150),
141 ATTRIBUTE2 VARCHAR2(150),
142 ATTRIBUTE3 VARCHAR2(150),
143 ATTRIBUTE4 VARCHAR2(150),
144 ATTRIBUTE5 VARCHAR2(150),
145 ATTRIBUTE6 VARCHAR2(150),
146 ATTRIBUTE7 VARCHAR2(150),
147 ATTRIBUTE8 VARCHAR2(150),
148 ATTRIBUTE9 VARCHAR2(150),
149 ATTRIBUTE10 VARCHAR2(150),
150 ATTRIBUTE11 VARCHAR2(150),
154 ATTRIBUTE15 VARCHAR2(150),
151 ATTRIBUTE12 VARCHAR2(150),
152 ATTRIBUTE13 VARCHAR2(150),
153 ATTRIBUTE14 VARCHAR2(150),
155 TP_ATTRIBUTE_CATEGORY VARCHAR2(150),
156 TP_ATTRIBUTE1 VARCHAR2(150),
157 TP_ATTRIBUTE2 VARCHAR2(150),
158 TP_ATTRIBUTE3 VARCHAR2(150),
159 TP_ATTRIBUTE4 VARCHAR2(150),
160 TP_ATTRIBUTE5 VARCHAR2(150),
161 TP_ATTRIBUTE6 VARCHAR2(150),
162 TP_ATTRIBUTE7 VARCHAR2(150),
163 TP_ATTRIBUTE8 VARCHAR2(150),
164 TP_ATTRIBUTE9 VARCHAR2(150),
165 TP_ATTRIBUTE10 VARCHAR2(150),
166 TP_ATTRIBUTE11 VARCHAR2(150),
167 TP_ATTRIBUTE12 VARCHAR2(150),
168 TP_ATTRIBUTE13 VARCHAR2(150),
169 TP_ATTRIBUTE14 VARCHAR2(150),
170 TP_ATTRIBUTE15 VARCHAR2(150),
171 GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(30),
172 GLOBAL_ATTRIBUTE1 VARCHAR2(150),
173 GLOBAL_ATTRIBUTE2 VARCHAR2(150),
174 GLOBAL_ATTRIBUTE3 VARCHAR2(150),
175 GLOBAL_ATTRIBUTE4 VARCHAR2(150),
176 GLOBAL_ATTRIBUTE5 VARCHAR2(150),
177 GLOBAL_ATTRIBUTE6 VARCHAR2(150),
178 GLOBAL_ATTRIBUTE7 VARCHAR2(150),
179 GLOBAL_ATTRIBUTE8 VARCHAR2(150),
180 GLOBAL_ATTRIBUTE9 VARCHAR2(150),
181 GLOBAL_ATTRIBUTE10 VARCHAR2(150),
182 GLOBAL_ATTRIBUTE11 VARCHAR2(150),
183 GLOBAL_ATTRIBUTE12 VARCHAR2(150),
184 GLOBAL_ATTRIBUTE13 VARCHAR2(150),
185 GLOBAL_ATTRIBUTE14 VARCHAR2(150),
186 GLOBAL_ATTRIBUTE15 VARCHAR2(150),
187 GLOBAL_ATTRIBUTE16 VARCHAR2(150),
188 GLOBAL_ATTRIBUTE17 VARCHAR2(150),
189 GLOBAL_ATTRIBUTE18 VARCHAR2(150),
190 GLOBAL_ATTRIBUTE19 VARCHAR2(150),
191 GLOBAL_ATTRIBUTE20 VARCHAR2(150),
192 CREATION_DATE DATE,
193 CREATED_BY NUMBER,
194 LAST_UPDATE_DATE DATE,
195 LAST_UPDATED_BY NUMBER,
196 LAST_UPDATE_LOGIN NUMBER,
197 PROGRAM_APPLICATION_ID NUMBER,
198 PROGRAM_ID NUMBER,
199 PROGRAM_UPDATE_DATE DATE,
200 REQUEST_ID NUMBER,
201 BATCH_ID NUMBER,
202 HASH_VALUE NUMBER,
203 SOURCE_HEADER_ID NUMBER,
204 NUMBER_OF_LPN NUMBER,--bugfix 1426086: added number_of_lpn
205 /* Changes for the Shipping Data Model Bug#1918342*/
206 COD_AMOUNT NUMBER,
207 COD_CURRENCY_CODE VARCHAR2(15),
208 COD_REMIT_TO VARCHAR2(150),
209 COD_CHARGE_PAID_BY VARCHAR2(150),
210 PROBLEM_CONTACT_REFERENCE VARCHAR2(500),
211 PORT_OF_LOADING VARCHAR2(150),
212 PORT_OF_DISCHARGE VARCHAR2(150),
213 FTZ_NUMBER VARCHAR2(35),
214 ROUTED_EXPORT_TXN VARCHAR2(1),
215 ENTRY_NUMBER VARCHAR2(35),
216 ROUTING_INSTRUCTIONS VARCHAR2(120),
217 IN_BOND_CODE VARCHAR2(35),
218 SHIPPING_MARKS VARCHAR2(100),
219 /* H Integration: datamodel changes wrudge */
220 SERVICE_LEVEL VARCHAR2(30),
221 MODE_OF_TRANSPORT VARCHAR2(30),
222 ASSIGNED_TO_FTE_TRIPS VARCHAR2(1),
223 /* I Quickship : datamodel changes sperera */
224 AUTO_SC_EXCLUDE_FLAG VARCHAR2(1),
225 AUTO_AP_EXCLUDE_FLAG VARCHAR2(1),
226 AP_BATCH_ID NUMBER,
227 /* I Harmonization: Non database Columns added rvishnuv */
228 ROWID VARCHAR2(4000),
229 LOADING_ORDER_DESC VARCHAR2(80),
230 ORGANIZATION_CODE VARCHAR2(3),
231 ULTIMATE_DROPOFF_LOCATION_CODE VARCHAR2(500),
232 INITIAL_PICKUP_LOCATION_CODE VARCHAR2(500),
233 CUSTOMER_NUMBER VARCHAR2(30),
234 INTMED_SHIP_TO_LOCATION_CODE VARCHAR2(500),
235 POOLED_SHIP_TO_LOCATION_CODE VARCHAR2(500),
236 CARRIER_CODE VARCHAR2(360),
237 SHIP_METHOD_NAME VARCHAR2(240),
238 FREIGHT_TERMS_NAME VARCHAR2(80),
239 FOB_NAME VARCHAR2(80),
240 FOB_LOCATION_CODE VARCHAR2(500),
241 WEIGHT_UOM_DESC VARCHAR2(25),
242 VOLUME_UOM_DESC VARCHAR2(25),
243 CURRENCY_NAME VARCHAR2(80),
244 /* J Inbound Logistics New columns jckwok */
245 VENDOR_ID NUMBER,
246 PARTY_ID NUMBER,
247 SHIPMENT_DIRECTION VARCHAR2(30),
248 ROUTING_RESPONSE_ID NUMBER,
249 RCV_SHIPMENT_HEADER_ID NUMBER,
250 ASN_SHIPMENT_HEADER_ID NUMBER,
251 SHIPPING_CONTROL VARCHAR2(30)
252 );
253
254 TYPE Delivery_Attr_Tbl_Type is TABLE of Delivery_Rec_Type index by binary_integer;
255
256
257
258
259
260
261
262
263 --========================================================================
264 -- PROCEDURE : Delivery_Action Wrapper API PUBLIC
265 --
266 -- PARAMETERS: p_api_version_number known api version error number
267 -- p_init_msg_list FND_API.G_TRUE to reset list
268 -- x_return_status return status
269 -- x_msg_count number of messages in the list
270 -- x_msg_data text of messages
271 -- p_action_prms Record of caller, phase, action_code and other
272 -- parameters specific to the actions.
273 -- p_rec_attr_tab Table of Attributes for the delivery entity
274 -- x_delivery_out_rec Record of output parameters based on the actions.
275 -- VERSION : current version 1.0
276 -- initial version 1.0
277 -- COMMENT : This procedure is used to perform an action specified
278 -- in p_action_prms.action_code on an existing delivery identified
279 -- by p_rec_attr.delivery_id/p_rec_attr.name.
280 --========================================================================
281 PROCEDURE Delivery_Action
282 ( p_api_version_number IN NUMBER,
283 p_init_msg_list IN VARCHAR2,
284 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
285 p_action_prms IN WSH_INTERFACE_EXT_GRP.del_action_parameters_rectype,
286 p_delivery_id_tab IN wsh_util_core.id_tab_type,
287 x_delivery_out_rec OUT NOCOPY WSH_INTERFACE_EXT_GRP.Del_Action_Out_Rec_Type,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2);
291 --========================================================================
292
293 -- I Harmonization: rvishnuv ******* Actions ******
294
295 -- I Harmonization: rvishnuv ******* Create/Update ******
296 --========================================================================
297 -- PROCEDURE : Create_Update_Delivery Wrapper API PUBLIC
298 --
299 -- PARAMETERS: p_api_version_number known api version error buffer
300 -- p_init_msg_list FND_API.G_TRUE to reset list
301 -- x_return_status return status
302 -- x_msg_count number of messages in the list
303 -- x_msg_data text of messages
304 -- p_in_rec Record for caller, phase
305 -- and action_code ( CREATE-UPDATE )
306 -- p_rec_attr_tab Table of Attributes for the delivery entity
307 -- x_del_out_rec Record of delivery_id, and name of new deliveries,
308 -- VERSION : current version 1.0
309 -- initial version 1.0
310 -- COMMENT : Creates or updates a record in wsh_new_deliveries table with information
311 -- specified in p_delivery_info
312 --========================================================================
313 /***************************************************************************************************
314 * IMPORTANT NOTE
315 *
316 * While calling this API, please use FND_API.G_MISS_XXXX to set the value to NULL for parameter
317 * p_rec_attr_tab.
318 ***************************************************************************************************/
319 PROCEDURE Create_Update_Delivery
320 ( p_api_version_number IN NUMBER,
321 p_init_msg_list IN VARCHAR2,
322 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
323 p_in_rec IN WSH_INTERFACE_EXT_GRP.Del_In_Rec_Type,
324 p_rec_attr_tab IN WSH_INTERFACE_EXT_GRP.Delivery_Attr_Tbl_Type,
325 x_del_out_rec_tab OUT NOCOPY WSH_INTERFACE_EXT_GRP.Del_Out_Tbl_Type,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2);
329 --========================================================================
330
331 -- I Harmonization: rvishnuv ******* Create/Update ******
332
333
334
335
336 TYPE det_action_parameters_rec_type IS RECORD
337 (
338 -- Generic
339 Caller VARCHAR2(50),
340 Action_Code VARCHAR2(30),
344 delivery_name VARCHAR2(32767),
341 Phase NUMBER,
342 -- Assign/Unassign
343 delivery_id NUMBER ,
345 -- Calculate weight and volume
346 wv_override_flag VARCHAR2(32767),
347 -- Cycle Count
348 quantity_to_split NUMBER,
349 quantity2_to_split NUMBER,
350 -- Pack, Unpack
351 container_name VARCHAR2(32767),
352 container_instance_id NUMBER,
353 container_flag VARCHAR2(1),
354 delivery_flag VARCHAR2(1),
355 -- Autopack
356 group_id_tab wsh_util_core.id_tab_type,
357 -- Split Line
358 split_quantity NUMBER,
359 split_quantity2 NUMBER
360 );
361 --
362
363
364 TYPE det_action_out_rec_type IS RECORD
365 (
366 valid_id_tab WSH_UTIL_CORE.id_tab_type,
367 selection_issue_flag VARCHAR2(1),
368 delivery_id_tab WSH_UTIL_CORE.id_tab_type,
369 result_id_tab WSH_UTIL_CORE.id_tab_type,
370 split_quantity NUMBER,
371 split_quantity2 NUMBER);
372
373 --
374
375 TYPE Det_actionsInOutRecType IS RECORD
376 (
377 split_quantity NUMBER,
378 split_quantity2 NUMBER
379 );
380
381
382
383 TYPE Delivery_Details_Rec_Type IS RECORD
384 (delivery_detail_id NUMBER,
385 source_code VARCHAR2(30),
386 source_header_id NUMBER,
387 source_line_id NUMBER,
388 customer_id NUMBER,
389 sold_to_contact_id NUMBER,
390 inventory_item_id NUMBER,
391 item_description VARCHAR2(250),
392 hazard_class_id NUMBER,
393 country_of_origin VARCHAR2(50),
394 classification VARCHAR2(30),
395 ship_from_location_id NUMBER,
396 ship_to_location_id NUMBER,
397 ship_to_contact_id NUMBER,
398 ship_to_site_use_id NUMBER,
399 deliver_to_location_id NUMBER,
400 deliver_to_contact_id NUMBER,
401 deliver_to_site_use_id NUMBER,
402 intmed_ship_to_location_id NUMBER,
403 intmed_ship_to_contact_id NUMBER,
404 hold_code VARCHAR2(1),
405 ship_tolerance_above NUMBER,
406 ship_tolerance_below NUMBER,
407 requested_quantity NUMBER,
408 shipped_quantity NUMBER,
409 delivered_quantity NUMBER,
410 requested_quantity_uom VARCHAR2(3),
411 subinventory VARCHAR2(10),
412 revision VARCHAR2(3),
413 -- HW OPMCONV. Need to expand length of lot_number to 80
414 lot_number VARCHAR2(80),
415 customer_requested_lot_flag VARCHAR2(1),
416 serial_number VARCHAR2(30),
417 locator_id NUMBER,
418 date_requested DATE,
419 date_scheduled DATE,
420 master_container_item_id NUMBER,
421 detail_container_item_id NUMBER,
422 load_seq_number NUMBER,
423 ship_method_code VARCHAR2(30),
424 carrier_id NUMBER,
425 freight_terms_code VARCHAR2(30),
426 shipment_priority_code VARCHAR2(30),
427 fob_code VARCHAR2(30),
428 customer_item_id NUMBER,
429 dep_plan_required_flag VARCHAR2(1),
430 customer_prod_seq VARCHAR2(50),
431 customer_dock_code VARCHAR2(50),
432 cust_model_serial_number VARCHAR2(50),
433 customer_job VARCHAR2(50),
434 customer_production_line VARCHAR2(50),
435 net_weight NUMBER,
436 weight_uom_code VARCHAR2(3),
437 volume NUMBER,
438 volume_uom_code VARCHAR2(3),
439 tp_attribute_category VARCHAR2(240),
440 tp_attribute1 VARCHAR2(240),
441 tp_attribute2 VARCHAR2(240),
442 tp_attribute3 VARCHAR2(240),
443 tp_attribute4 VARCHAR2(240),
444 tp_attribute5 VARCHAR2(240),
445 tp_attribute6 VARCHAR2(240),
446 tp_attribute7 VARCHAR2(240),
447 tp_attribute8 VARCHAR2(240),
448 tp_attribute9 VARCHAR2(240),
449 tp_attribute10 VARCHAR2(240),
450 tp_attribute11 VARCHAR2(240),
451 tp_attribute12 VARCHAR2(240),
452 tp_attribute13 VARCHAR2(240),
456 attribute1 VARCHAR2(150),
453 tp_attribute14 VARCHAR2(240),
454 tp_attribute15 VARCHAR2(240),
455 attribute_category VARCHAR2(150),
457 attribute2 VARCHAR2(150),
458 attribute3 VARCHAR2(150),
459 attribute4 VARCHAR2(150),
460 attribute5 VARCHAR2(150),
461 attribute6 VARCHAR2(150),
462 attribute7 VARCHAR2(150),
463 attribute8 VARCHAR2(150),
464 attribute9 VARCHAR2(150),
465 attribute10 VARCHAR2(150),
466 attribute11 VARCHAR2(150),
467 attribute12 VARCHAR2(150),
468 attribute13 VARCHAR2(150),
469 attribute14 VARCHAR2(150),
470 attribute15 VARCHAR2(150),
471 created_by NUMBER,
472 creation_date DATE,
473 last_update_date DATE,
474 last_update_login NUMBER,
475 last_updated_by NUMBER,
476 program_application_id NUMBER,
477 program_id NUMBER,
478 program_update_date DATE,
479 request_id NUMBER,
480 mvt_stat_status VARCHAR2(30),
481 released_flag VARCHAR2(1),
482 organization_id NUMBER,
483 transaction_temp_id NUMBER,
484 ship_set_id NUMBER,
485 arrival_set_id NUMBER,
486 ship_model_complete_flag VARCHAR2(1),
487 top_model_line_id NUMBER,
488 source_header_number VARCHAR2(150),
489 source_header_type_id NUMBER,
490 source_header_type_name VARCHAR2(240),
491 cust_po_number VARCHAR2(50),
492 ato_line_id NUMBER,
493 src_requested_quantity NUMBER,
494 src_requested_quantity_uom VARCHAR2(3),
495 move_order_line_id NUMBER,
496 cancelled_quantity NUMBER,
497 quality_control_quantity NUMBER,
498 cycle_count_quantity NUMBER,
499 tracking_number VARCHAR2(30),
500 movement_id NUMBER,
501 shipping_instructions VARCHAR2(2000),
502 packing_instructions VARCHAR2(2000),
503 project_id NUMBER,
504 task_id NUMBER,
505 org_id NUMBER,
506 oe_interfaced_flag VARCHAR2(1),
507 split_from_detail_id NUMBER,
508 inv_interfaced_flag VARCHAR2(1),
509 source_line_number VARCHAR2(150),
510 inspection_flag VARCHAR2(1),
511 released_status VARCHAR2(1),
512 container_flag VARCHAR2(1),
513 container_type_code VARCHAR2(30),
514 container_name VARCHAR2(30),
515 fill_percent NUMBER,
516 gross_weight NUMBER,
517 master_serial_number VARCHAR2(30),
518 maximum_load_weight NUMBER,
519 maximum_volume NUMBER,
520 minimum_fill_percent NUMBER,
521 seal_code VARCHAR2(30),
522 unit_number VARCHAR2(30),
523 unit_price NUMBER,
524 currency_code VARCHAR2(15),
525 freight_class_cat_id NUMBER,
526 commodity_code_cat_id NUMBER,
527 -- hverddin 26-jun-2000 start of OPM changes
528 -- HW OPMCONV. Need to expand length of grade to 150
529 preferred_grade VARCHAR2(150),
530 src_requested_quantity2 NUMBER,
531 src_requested_quantity_uom2 VARCHAR2(3),
532 requested_quantity2 NUMBER,
533 shipped_quantity2 NUMBER,
534 delivered_quantity2 NUMBER,
535 cancelled_quantity2 NUMBER,
536 quality_control_quantity2 NUMBER,
537 cycle_count_quantity2 NUMBER,
538 requested_quantity_uom2 VARCHAR2(3),
539 -- HW OPMCONV. No need for sublot anymore
540 -- sublot_number VARCHAR2(32) ,
541 -- hverddin 26-jun-2000 end of OPM changes
542 lpn_id NUMBER ,
543 pickable_flag VARCHAR2(1),
544 original_subinventory VARCHAR2(10),
545 to_serial_number VARCHAR2(30),
546 picked_quantity NUMBER,
547 picked_quantity2 NUMBER,
548 /* H Integration: datamodel changes wrudge */
549 received_quantity NUMBER,
550 received_quantity2 NUMBER,
551 source_line_set_id NUMBER,
552 -- 2678601
556 ship_from_site_id NUMBER,
553 transaction_id NUMBER,
554 /* J Inbound Logistics Changes jckwok */
555 vendor_id NUMBER,
557 line_direction VARCHAR2(30),
558 party_id NUMBER,
559 routing_req_id NUMBER,
560 shipping_control VARCHAR2(30),
561 source_blanket_reference_id NUMBER,
562 source_blanket_reference_num NUMBER,
563 po_shipment_line_id NUMBER,
564 po_shipment_line_number NUMBER,
565 returned_quantity NUMBER,
566 returned_quantity2 NUMBER,
567 rcv_shipment_line_id NUMBER,
568 source_line_type_code VARCHAR2(30),
569 supplier_item_number VARCHAR2(50),
570 batch_id NUMBER, -- X-dock requirement
571 replenishment_status VARCHAR2(1) --bug# 6689448 (replenishment project)
572 );
573
574
575
576 TYPE Delivery_Details_Attr_Tbl_Type is TABLE of Delivery_Details_Rec_Type index by binary_integer;
577
578
579
580
581
582 TYPE detailInRecType IS RECORD
583 (
584 --
585 caller VARCHAR2(50),
586 action_code VARCHAR2(30),
587 phase NUMBER,
588 container_item_id NUMBER,
589 container_item_name VARCHAR2(32767),
590 container_item_seg FND_FLEX_EXT.SegmentArray,
591 organization_id NUMBER,
592 organization_code VARCHAR2(32767),
593 name_prefix VARCHAR2(32767),
594 name_suffix VARCHAR2(32767),
595 base_number NUMBER,
596 num_digits NUMBER,
597 quantity NUMBER,
598 container_name VARCHAR2(32767),
599 lpn_ids wsh_util_core.id_tab_type
600 );
601
602 --
603 TYPE detailOutRecType IS RECORD
604 (
605 --
606 detail_ids WSH_UTIL_CORE.Id_Tab_Type
607 );
608
609 -- ---------------------------------------------------------------------
610 -- Procedure: Delivery_Detail_Action Wrapper API
611 --
612 -- Parameters:
613 --
614 -- Description: This procedure is the wrapper(overloaded) version for the
615 -- main delivery_detail_group API. This is for use by public APIs
616 -- and by other product APIs. This signature does not have
617 -- the form(UI) specific parameters
618 -- Created : Patchset I : Harmonziation Project
619 -- Created by: KVENKATE
620 -- -----------------------------------------------------------------------
621 PROCEDURE Delivery_Detail_Action
622 (
623 -- Standard Parameters
624 p_api_version_number IN NUMBER,
625 p_init_msg_list IN VARCHAR2,
626 p_commit IN VARCHAR2,
627 x_return_status OUT NOCOPY VARCHAR2,
628 x_msg_count OUT NOCOPY NUMBER,
629 x_msg_data OUT NOCOPY VARCHAR2,
630
631 -- Procedure specific Parameters
632 p_detail_id_tab IN WSH_UTIL_CORE.id_tab_type,
633 p_action_prms IN WSH_INTERFACE_EXT_GRP.det_action_parameters_rec_type,
634 x_action_out_rec OUT NOCOPY WSH_INTERFACE_EXT_GRP.det_action_out_rec_type
635 );
636 -- ---------------------------------------------------------------------
637 -- Procedure: Create_Update_Delivery_Detail Wrapper API
638 --
639 -- Parameters:
640 --
641 -- Description: This procedure is the new API for wrapping the logic of CREATE/UPDATE of delivery details
642 -- Created : Patchset I - Harmonization Project
643 -- Created By : KVENKATE
644 -- -----------------------------------------------------------------------
645
646 /***************************************************************************************************
647 * IMPORTANT NOTE
648 *
649 * While calling this API, please use FND_API.G_MISS_XXXX to set the value to NULL for parameter
650 * p_detail_info_tab.
651 ***************************************************************************************************/
652 PROCEDURE Create_Update_Delivery_Detail
653 (
654 -- Standard Parameters
655 p_api_version_number IN NUMBER,
656 p_init_msg_list IN VARCHAR2,
657 p_commit IN VARCHAR2,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2,
661
662 -- Procedure Specific Parameters
663 p_detail_info_tab IN WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type,
664 p_IN_rec IN WSH_INTERFACE_EXT_GRP.detailInRecType,
665 x_OUT_rec OUT NOCOPY WSH_INTERFACE_EXT_GRP.detailOutRecType
666 );
667
668
669
670 TYPE TripInRecType is RECORD(
671 caller VARCHAR2(50),
672 phase NUMBER,
673 action_code VARCHAR2(30));
674
675 TYPE tripActionInRecType
676 IS
677 RECORD
681 );
678 (
679 action_code VARCHAR2(32767),
680 wv_override_flag VARCHAR2(1) DEFAULT 'N'
682
683 TYPE tripOutRecType IS RECORD
684 (
685 rowid VARCHAR2(32767),
686 trip_id NUMBER,
687 trip_name VARCHAR2(32767)
688 );
689
690 TYPE trip_out_tab_type IS TABLE OF TripOutRecType INDEX BY BINARY_INTEGER;
691
692 TYPE tripActionOutRecType
693 IS
694 RECORD
695 (
696 result_id_tab wsh_util_core.id_tab_type,
697 valid_ids_tab wsh_util_core.id_tab_type,
698 selection_issue_flag VARCHAR2(1)
699 );
700
701 TYPE trip_action_parameters_rectype IS RECORD(
702 caller VARCHAR2(50)
703 ,phase NUMBER
704 ,action_code VARCHAR2(30)
705 ,organization_id NUMBER
706 ,report_set_id NUMBER
707 ,override_flag VARCHAR2(500)
708 ,trip_name VARCHAR2(30)
709 ,actual_date DATE
710 ,stop_id NUMBER --] These parameters
711 ,action_flag VARCHAR2(1) --] are used for
712 ,autointransit_flag VARCHAR2(1) --] action_code = 'CONFIRM'
713 ,autoclose_flag VARCHAR2(1) --]
714 ,stage_del_flag VARCHAR2(1) --]
715 ,ship_method VARCHAR2(30) --]
716 ,bill_of_lading_flag VARCHAR2(1) --]
717 ,defer_interface_flag VARCHAR2(1) --]
718 ,actual_departure_date DATE --]
719 );
720
721 TYPE trip_rec_type IS RECORD (
722 TRIP_ID NUMBER,
723 NAME VARCHAR2(30),
724 PLANNED_FLAG VARCHAR2(1),
725 ARRIVE_AFTER_TRIP_ID NUMBER,
726 STATUS_CODE VARCHAR2(2),
727 VEHICLE_ITEM_ID NUMBER,
728 VEHICLE_ORGANIZATION_ID NUMBER,
729 VEHICLE_NUMBER VARCHAR2(30),
730 VEHICLE_NUM_PREFIX VARCHAR2(10),
731 CARRIER_ID NUMBER,
732 SHIP_METHOD_CODE VARCHAR2(30),
733 ROUTE_ID NUMBER,
734 ROUTING_INSTRUCTIONS VARCHAR2(2000),
735 ATTRIBUTE_CATEGORY VARCHAR2(150),
736 ATTRIBUTE1 VARCHAR2(150),
737 ATTRIBUTE2 VARCHAR2(150),
738 ATTRIBUTE3 VARCHAR2(150),
739 ATTRIBUTE4 VARCHAR2(150),
740 ATTRIBUTE5 VARCHAR2(150),
741 ATTRIBUTE6 VARCHAR2(150),
742 ATTRIBUTE7 VARCHAR2(150),
743 ATTRIBUTE8 VARCHAR2(150),
744 ATTRIBUTE9 VARCHAR2(150),
745 ATTRIBUTE10 VARCHAR2(150),
746 ATTRIBUTE11 VARCHAR2(150),
747 ATTRIBUTE12 VARCHAR2(150),
748 ATTRIBUTE13 VARCHAR2(150),
749 ATTRIBUTE14 VARCHAR2(150),
750 ATTRIBUTE15 VARCHAR2(150),
751 CREATION_DATE DATE,
752 CREATED_BY NUMBER,
753 LAST_UPDATE_DATE DATE,
754 LAST_UPDATED_BY NUMBER,
755 LAST_UPDATE_LOGIN NUMBER,
756 PROGRAM_APPLICATION_ID NUMBER,
757 PROGRAM_ID NUMBER,
758 PROGRAM_UPDATE_DATE DATE,
759 REQUEST_ID NUMBER,
760 /* H Integration: datamodel changes wrudge */
761 SERVICE_LEVEL VARCHAR2(30),
762 MODE_OF_TRANSPORT VARCHAR2(30),
763 FREIGHT_TERMS_CODE VARCHAR2(30),
764 CONSOLIDATION_ALLOWED VARCHAR2(1),
765 /* I WSH-FTE Integration , update to 30 */
766 LOAD_TENDER_STATUS VARCHAR2(30),
767 ROUTE_LANE_ID NUMBER,
768 LANE_ID NUMBER,
769 SCHEDULE_ID NUMBER,
770 BOOKING_NUMBER VARCHAR2(30),
771 /* I Harmonization: Non Database Columns Added rvishnuv */
772 ROWID VARCHAR2(4000),
773 ARRIVE_AFTER_TRIP_NAME VARCHAR2(30),
774 SHIP_METHOD_NAME VARCHAR2(240),
775 VEHICLE_ITEM_DESC VARCHAR2(240),
776 VEHICLE_ORGANIZATION_CODE VARCHAR2(3),
777 /* I WSH-FTE LOAD TENDER Integration */
778 LOAD_TENDER_NUMBER NUMBER,
779 VESSEL VARCHAR2(100),
780 VOYAGE_NUMBER VARCHAR2(100),
781 PORT_OF_LOADING VARCHAR2(240),
782 PORT_OF_DISCHARGE VARCHAR2(240),
783 WF_NAME VARCHAR2(8),
784 WF_PROCESS_NAME VARCHAR2(30),
785 WF_ITEM_KEY VARCHAR2(240),
786 CARRIER_CONTACT_ID NUMBER,
787 SHIPPER_WAIT_TIME NUMBER,
788 WAIT_TIME_UOM VARCHAR2(3),
792 SHIPMENTS_TYPE_FLAG VARCHAR2(30)
789 LOAD_TENDERED_TIME DATE,
790 CARRIER_RESPONSE VARCHAR2(2000),
791 /* J Inbound Logistics new columns jckwok */
793 );
794
795
796 TYPE Trip_Attr_Tbl_Type is TABLE of trip_rec_type index by binary_integer;
797 --
798
799
800
801 PROCEDURE Trip_Action
802 ( p_api_version_number IN NUMBER,
803 p_init_msg_list IN VARCHAR2,
804 p_commit IN VARCHAR2,
805 p_entity_id_tab IN wsh_util_core.id_tab_type,
806 p_action_prms IN WSH_INTERFACE_EXT_GRP.trip_action_parameters_rectype,
807 x_trip_out_rec OUT NOCOPY WSH_INTERFACE_EXT_GRP.tripActionOutRecType,
808 x_return_status OUT NOCOPY VARCHAR2,
809 x_msg_count OUT NOCOPY NUMBER,
810 x_msg_data OUT NOCOPY VARCHAR2);
811
812 /***************************************************************************************************
813 * IMPORTANT NOTE
814 *
815 * While calling this API, please use FND_API.G_MISS_XXXX to set the value to NULL for parameter
816 * p_trip_info_tab.
817 ***************************************************************************************************/
818 PROCEDURE Create_Update_Trip(
819 p_api_version_number IN NUMBER,
820 p_init_msg_list IN VARCHAR2,
821 p_commit IN VARCHAR2,
822 x_return_status OUT NOCOPY VARCHAR2,
823 x_msg_count OUT NOCOPY NUMBER,
824 x_msg_data OUT NOCOPY VARCHAR2,
825 p_trip_info_tab IN WSH_INTERFACE_EXT_GRP.Trip_Attr_Tbl_Type,
826 p_In_rec IN WSH_INTERFACE_EXT_GRP.tripInRecType,
827 x_Out_Tab OUT NOCOPY WSH_INTERFACE_EXT_GRP.Trip_Out_Tab_Type);
828
829 TYPE stop_action_parameters_rectype IS RECORD (
830 caller VARCHAR2(50)
831 ,phase NUMBER
832 ,action_code VARCHAR2(30)
833 ,stop_action VARCHAR2(30)
834 ,organization_id NUMBER
835 ,actual_date DATE
836 ,defer_interface_flag VARCHAR2(500)
837 ,report_set_id NUMBER
838 ,override_flag VARCHAR2(500)
839 );
840
841
842
843 TYPE stopInRecType is RECORD(
844 caller VARCHAR2(50),
845 phase NUMBER,
846 action_code VARCHAR2(30));
847
848 TYPE stopOutRecType IS RECORD (
849 parameter1 VARCHAR2(32767) DEFAULT FND_API.G_MISS_CHAR,
850 rowid VARCHAR2(32767),
851 stop_id NUMBER
852 );
853
854 TYPE stop_out_tab_type IS TABLE OF StopOutRecType INDEX BY BINARY_INTEGER;
855
856 TYPE stopActionInRecType
857 IS
858 RECORD
859 (
860 action_code VARCHAR2(32767),
861 actual_date DATE DEFAULT FND_API.G_MISS_DATE,
862 defer_interface_flag VARCHAR2(1) DEFAULT 'Y'
863 );
864
865
866 TYPE stopActionOutRecType
867 IS
868 RECORD
869 (
870 result_id_tab wsh_util_core.id_tab_type,
871 valid_ids_tab wsh_util_core.id_tab_type,
872 selection_issue_flag VARCHAR2(1)
873 );
874
875 TYPE trip_stop_rec_type IS RECORD (
876 STOP_ID NUMBER,
877 TRIP_ID NUMBER,
878 STOP_LOCATION_ID NUMBER,
879 STATUS_CODE VARCHAR2(2),
880 STOP_SEQUENCE_NUMBER NUMBER,
881 PLANNED_ARRIVAL_DATE DATE,
882 PLANNED_DEPARTURE_DATE DATE,
883 ACTUAL_ARRIVAL_DATE DATE,
884 ACTUAL_DEPARTURE_DATE DATE,
885 DEPARTURE_GROSS_WEIGHT NUMBER,
886 DEPARTURE_NET_WEIGHT NUMBER,
887 WEIGHT_UOM_CODE VARCHAR2(3),
888 DEPARTURE_VOLUME NUMBER,
889 VOLUME_UOM_CODE VARCHAR2(3),
890 DEPARTURE_SEAL_CODE VARCHAR2(30),
891 DEPARTURE_FILL_PERCENT NUMBER,
892 TP_ATTRIBUTE_CATEGORY VARCHAR2(150),
893 TP_ATTRIBUTE1 VARCHAR2(150),
894 TP_ATTRIBUTE2 VARCHAR2(150),
895 TP_ATTRIBUTE3 VARCHAR2(150),
896 TP_ATTRIBUTE4 VARCHAR2(150),
897 TP_ATTRIBUTE5 VARCHAR2(150),
898 TP_ATTRIBUTE6 VARCHAR2(150),
899 TP_ATTRIBUTE7 VARCHAR2(150),
900 TP_ATTRIBUTE8 VARCHAR2(150),
901 TP_ATTRIBUTE9 VARCHAR2(150),
902 TP_ATTRIBUTE10 VARCHAR2(150),
903 TP_ATTRIBUTE11 VARCHAR2(150),
904 TP_ATTRIBUTE12 VARCHAR2(150),
905 TP_ATTRIBUTE13 VARCHAR2(150),
906 TP_ATTRIBUTE14 VARCHAR2(150),
907 TP_ATTRIBUTE15 VARCHAR2(150),
908 ATTRIBUTE_CATEGORY VARCHAR2(150),
909 ATTRIBUTE1 VARCHAR2(150),
910 ATTRIBUTE2 VARCHAR2(150),
911 ATTRIBUTE3 VARCHAR2(150),
912 ATTRIBUTE4 VARCHAR2(150),
916 ATTRIBUTE8 VARCHAR2(150),
913 ATTRIBUTE5 VARCHAR2(150),
914 ATTRIBUTE6 VARCHAR2(150),
915 ATTRIBUTE7 VARCHAR2(150),
917 ATTRIBUTE9 VARCHAR2(150),
918 ATTRIBUTE10 VARCHAR2(150),
919 ATTRIBUTE11 VARCHAR2(150),
920 ATTRIBUTE12 VARCHAR2(150),
921 ATTRIBUTE13 VARCHAR2(150),
922 ATTRIBUTE14 VARCHAR2(150),
923 ATTRIBUTE15 VARCHAR2(150),
924 CREATION_DATE DATE,
925 CREATED_BY NUMBER,
926 LAST_UPDATE_DATE DATE,
927 LAST_UPDATED_BY NUMBER,
928 LAST_UPDATE_LOGIN NUMBER,
929 PROGRAM_APPLICATION_ID NUMBER,
930 PROGRAM_ID NUMBER,
931 PROGRAM_UPDATE_DATE DATE,
932 REQUEST_ID NUMBER,
933 /* H Integration: datamodel changes wrudge */
934 WSH_LOCATION_ID NUMBER,
935 TRACKING_DRILLDOWN_FLAG VARCHAR2(1),
936 TRACKING_REMARKS VARCHAR2(4000),
937 CARRIER_EST_DEPARTURE_DATE DATE,
938 CARRIER_EST_ARRIVAL_DATE DATE,
939 LOADING_START_DATETIME DATE,
940 LOADING_END_DATETIME DATE,
941 UNLOADING_START_DATETIME DATE,
942 UNLOADING_END_DATETIME DATE,
943 /* I Harmonization: Non Database Columns Added rvishnuv */
944 ROWID VARCHAR2(4000),
945 TRIP_NAME VARCHAR2(30),
946 STOP_LOCATION_CODE VARCHAR2(80),
947 WEIGHT_UOM_DESC VARCHAR2(25),
948 VOLUME_UOM_DESC VARCHAR2(25),
949 LOCK_STOP_ID NUMBER,
950 PENDING_INTERFACE_FLAG VARCHAR2(1),
951 TRANSACTION_HEADER_ID NUMBER,
952 /* J Inbound Logistics new columns jckwok */
953 SHIPMENTS_TYPE_FLAG VARCHAR2(30)
954 );
955
956 TYPE Stop_Attr_Tbl_Type is TABLE of trip_stop_rec_type index by binary_integer;
957
958
959
960
961
962
963 PROCEDURE Stop_Action
964 ( p_api_version_number IN NUMBER,
965 p_init_msg_list IN VARCHAR2,
966 p_commit IN VARCHAR2,
967 p_entity_id_tab IN wsh_util_core.id_tab_type,
968 p_action_prms IN WSH_INTERFACE_EXT_GRP.stop_action_parameters_rectype,
969 x_stop_out_rec OUT NOCOPY WSH_INTERFACE_EXT_GRP.stopActionOutRecType,
970 x_return_status OUT NOCOPY VARCHAR2,
971 x_msg_count OUT NOCOPY NUMBER,
972 x_msg_data OUT NOCOPY VARCHAR2);
973
974
975 --heali
976 /***************************************************************************************************
977 * IMPORTANT NOTE
978 *
979 * While calling this API, please use FND_API.G_MISS_XXXX to set the value to NULL for parameter
980 * p_rec_attr_tab.
981 ***************************************************************************************************/
982 PROCEDURE CREATE_UPDATE_STOP(
983 p_api_version_number IN NUMBER,
984 p_init_msg_list IN VARCHAR2,
985 p_commit IN VARCHAR2,
986 p_in_rec IN WSH_INTERFACE_EXT_GRP.stopInRecType,
987 p_rec_attr_tab IN WSH_INTERFACE_EXT_GRP.Stop_Attr_Tbl_Type,
988 x_stop_out_tab OUT NOCOPY WSH_INTERFACE_EXT_GRP.stop_out_tab_type,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_msg_count OUT NOCOPY NUMBER,
991 x_msg_data OUT NOCOPY VARCHAR2);
992
993
994
995 TYPE FreightInRecType IS RECORD
996 (
997 caller VARCHAR2(50),
998 action_code VARCHAR2(30),
999 phase NUMBER
1000 );
1001
1002 TYPE FreightOutRecType IS RECORD
1003 (
1004 freight_cost_id NUMBER,
1005 rowid VARCHAR2(4000)
1006 );
1007
1008
1009
1010
1011 TYPE Freight_Cost_Rec_Type IS RECORD (
1012 FREIGHT_COST_ID NUMBER
1013 , FREIGHT_COST_TYPE_ID NUMBER
1014 , UNIT_AMOUNT NUMBER
1015 /* H Integration: datamodel changes wrudge (15->30) */
1016 , CALCULATION_METHOD VARCHAR2(30)
1017 , UOM VARCHAR2(15)
1018 , QUANTITY NUMBER
1019 , TOTAL_AMOUNT NUMBER
1020 , CURRENCY_CODE VARCHAR2(15)
1021 , CONVERSION_DATE DATE
1022 , CONVERSION_RATE NUMBER
1023 , CONVERSION_TYPE_CODE VARCHAR2(30)
1024 , TRIP_ID NUMBER
1025 , STOP_ID NUMBER
1026 , DELIVERY_ID NUMBER
1027 , DELIVERY_LEG_ID NUMBER
1028 , DELIVERY_DETAIL_ID NUMBER
1029 , ATTRIBUTE_CATEGORY VARCHAR2(150)
1030 , ATTRIBUTE1 VARCHAR2(150)
1031 , ATTRIBUTE2 VARCHAR2(150)
1032 , ATTRIBUTE3 VARCHAR2(150)
1033 , ATTRIBUTE4 VARCHAR2(150)
1034 , ATTRIBUTE5 VARCHAR2(150)
1035 , ATTRIBUTE6 VARCHAR2(150)
1036 , ATTRIBUTE7 VARCHAR2(150)
1037 , ATTRIBUTE8 VARCHAR2(150)
1038 , ATTRIBUTE9 VARCHAR2(150)
1039 , ATTRIBUTE10 VARCHAR2(150)
1040 , ATTRIBUTE11 VARCHAR2(150)
1044 , ATTRIBUTE15 VARCHAR2(150)
1041 , ATTRIBUTE12 VARCHAR2(150)
1042 , ATTRIBUTE13 VARCHAR2(150)
1043 , ATTRIBUTE14 VARCHAR2(150)
1045 , CREATION_DATE DATE
1046 , CREATED_BY NUMBER
1047 , LAST_UPDATE_DATE DATE
1048 , LAST_UPDATE_LOGIN NUMBER
1049 , PROGRAM_APPLICATION_ID NUMBER
1050 , PROGRAM_ID NUMBER
1051 , PROGRAM_UPDATE_DATE DATE
1052 , REQUEST_ID NUMBER
1053 /* H Integration: datamodel changes wrudge */
1054 , PRICING_LIST_HEADER_ID NUMBER
1055 , PRICING_LIST_LINE_ID NUMBER
1056 , APPLIED_TO_CHARGE_ID NUMBER
1057 , CHARGE_UNIT_VALUE NUMBER
1058 , CHARGE_SOURCE_CODE VARCHAR2(30)
1059 , LINE_TYPE_CODE VARCHAR2(30)
1060 , ESTIMATED_FLAG VARCHAR2(1)
1061 /* Harmonizing project I: heali */
1062 , FREIGHT_CODE VARCHAR2(30)
1063 , TRIP_NAME VARCHAR2(30)
1064 , DELIVERY_NAME VARCHAR2(30)
1065 , FREIGHT_COST_TYPE VARCHAR2(30)
1066 , STOP_LOCATION_ID NUMBER
1067 , PLANNED_DEP_DATE DATE
1068 , COMMODITY_CATEGORY_ID NUMBER
1069 );
1070
1071 TYPE freight_rec_tab_type IS TABLE OF Freight_Cost_Rec_Type INDEX BY BINARY_INTEGER;
1072 TYPE freight_out_tab_type IS TABLE OF FreightOutRecType INDEX BY BINARY_INTEGER;
1073
1074 /***************************************************************************************************
1075 * IMPORTANT NOTE
1076 *
1077 * While calling this API, please use FND_API.G_MISS_XXXX to set the value to NULL for parameter
1078 * p_freight_info_tab.
1079 ***************************************************************************************************/
1080 PROCEDURE Create_Update_Freight_Costs(
1081 p_api_version_number IN NUMBER,
1082 p_init_msg_list IN VARCHAR2,
1083 p_commit IN VARCHAR2,
1084 x_return_status OUT NOCOPY VARCHAR2,
1085 x_msg_count OUT NOCOPY NUMBER,
1086 x_msg_data OUT NOCOPY VARCHAR2,
1087 p_freight_info_tab IN WSH_INTERFACE_EXT_GRP.freight_rec_tab_type,
1088 p_in_rec IN WSH_INTERFACE_EXT_GRP.freightInRecType,
1089 x_out_tab OUT NOCOPY WSH_INTERFACE_EXT_GRP.freight_out_tab_type);
1090 --heali
1091
1092 TYPE XC_REC_TYPE IS RECORD
1093 (exception_id NUMBER,
1094 exception_name VARCHAR2(30),
1095 status VARCHAR2(30)
1096 );
1097
1098 TYPE XC_TAB_TYPE IS TABLE OF XC_REC_TYPE INDEX BY BINARY_INTEGER;
1099
1100 TYPE XC_ACTION_REC_TYPE IS RECORD
1101 (
1102 -- The following fields are used for Logging exceptions
1103 request_id NUMBER, -- Also used for Purge
1104 batch_id NUMBER,
1105 exception_id NUMBER,
1106 exception_name VARCHAR2(30), -- Also used for Purge, Change_Status
1107 logging_entity VARCHAR2(30), -- Also used for Purge, Change_Status
1108 logging_entity_id NUMBER, -- Also used for Change_Status
1109 manually_logged VARCHAR2(1),
1110 message VARCHAR2(2000),
1111 logged_at_location_code VARCHAR2(50), -- Also used for Purge
1112 exception_location_code VARCHAR2(50), -- Also used for Purge
1113 severity VARCHAR2(10), -- Also used for Purge
1114 delivery_name VARCHAR2(30), -- Also used for Purge
1115 trip_name VARCHAR2(30),
1116 stop_location_id NUMBER,
1117 delivery_detail_id NUMBER,
1118 container_name VARCHAR2(50),
1119 org_id NUMBER,
1120 inventory_item_id NUMBER,
1121 -- HW OPMCONV. Need to expand length of lot_number to 80
1122 lot_number VARCHAR2(80),
1123 -- HW OPMCONV. No need for sublot anymore
1124 -- sublot_number VARCHAR2(32),
1125 revision VARCHAR2(3),
1126 serial_number VARCHAR2(30),
1127 unit_of_measure VARCHAR2(5),
1128 quantity NUMBER,
1129 unit_of_measure2 VARCHAR2(3),
1130 quantity2 NUMBER,
1131 subinventory VARCHAR2(10),
1132 locator_id NUMBER,
1133 error_message VARCHAR2(500),
1134 attribute_category VARCHAR2(150),
1135 attribute1 VARCHAR2(150),
1136 attribute2 VARCHAR2(150),
1137 attribute3 VARCHAR2(150),
1138 attribute4 VARCHAR2(150),
1139 attribute5 VARCHAR2(150),
1140 attribute6 VARCHAR2(150),
1141 attribute7 VARCHAR2(150),
1142 attribute8 VARCHAR2(150),
1143 attribute9 VARCHAR2(150),
1144 attribute10 VARCHAR2(150),
1145 attribute11 VARCHAR2(150),
1146 attribute12 VARCHAR2(150),
1147 attribute13 VARCHAR2(150),
1148 attribute14 VARCHAR2(150),
1149 attribute15 VARCHAR2(150),
1150 departure_date DATE, -- Also used for Purge
1151 arrival_date DATE, -- Also used for Purge
1152
1153 -- These fields are used for the Purge action.
1154 exception_type VARCHAR2(25),
1155 status VARCHAR2(30),
1156 departure_date_to DATE,
1157 arrival_date_to DATE,
1158 creation_date DATE,
1159 creation_date_to DATE,
1160 data_older_no_of_days NUMBER,
1161
1162 -- This field is used for Change_Status action.
1163 new_status VARCHAR2(30),
1164
1165 caller VARCHAR2(100),
1166 phase NUMBER
1167 );
1168
1169
1170 ------------------------------------------------------------------------------
1171 -- Procedure: Get_Exceptions
1172 --
1173 -- Parameters: 1) p_logging_entity_id - entity id for a particular entity name
1174 -- 2) p_logging_entity_name - can be 'TRIP', 'STOP', 'DELIVERY',
1175 -- 'DETAIL', or 'CONTAINER'
1176 -- 3) x_exceptions_tab - list of exceptions
1177 --
1178 -- Description: This procedure takes in a logging entity id and logging entity
1179 -- name and create an exception table.
1180 ------------------------------------------------------------------------------
1181
1182 PROCEDURE Get_Exceptions (
1183 -- Standard parameters
1184 p_api_version IN NUMBER,
1185 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_count OUT NOCOPY NUMBER,
1188 x_msg_data OUT NOCOPY VARCHAR2,
1189
1190 -- program specific parameters
1191 p_logging_entity_id IN NUMBER,
1192 p_logging_entity_name IN VARCHAR2,
1193
1194 -- program specific out parameters
1195 x_exceptions_tab OUT NOCOPY WSH_INTERFACE_EXT_GRP.XC_TAB_TYPE
1196 );
1197
1198 PROCEDURE Exception_Action (
1199 p_api_version IN NUMBER,
1200 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1201 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1202 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1203 x_msg_count OUT NOCOPY NUMBER,
1204 x_msg_data OUT NOCOPY VARCHAR2,
1205 x_return_status OUT NOCOPY VARCHAR2,
1206
1207 p_exception_rec IN OUT NOCOPY WSH_INTERFACE_EXT_GRP.XC_ACTION_REC_TYPE,
1208 p_action IN VARCHAR2
1209 );
1210
1211
1212 TYPE dlvy_leg_action_prms_rectype IS RECORD(
1213 caller VARCHAR2(50),
1214 action_code VARCHAR2(30),
1215 phase NUMBER,
1216 p_Pick_Up_Location_Id NUMBER,
1217 p_Ship_Method VARCHAR2(30),
1218 p_Drop_Off_Location_Id NUMBER,
1219 p_Carrier_Id NUMBER
1220 );
1221
1222
1223 TYPE dlvy_leg_action_out_rec_type IS RECORD(
1224 valid_id_tab WSH_UTIL_CORE.id_tab_type,
1225 x_trip_id NUMBER,
1226 x_trip_name VARCHAR2(30),
1227 x_delivery_id NUMBER,
1228 x_bol_number VARCHAR2(50));
1229
1230
1231 PROCEDURE Delivery_Leg_Action(
1232 p_api_version_number IN NUMBER,
1233 p_init_msg_list IN VARCHAR2,
1234 p_commit IN VARCHAR2,
1235 p_dlvy_leg_id_tab IN wsh_util_core.id_tab_type,
1236 p_action_prms IN WSH_INTERFACE_EXT_GRP.dlvy_leg_action_prms_rectype,
1237 x_action_out_rec IN OUT NOCOPY WSH_INTERFACE_EXT_GRP.dlvy_leg_action_out_rec_type,
1238 x_return_status OUT NOCOPY VARCHAR2,
1239 x_msg_count OUT NOCOPY NUMBER,
1240 x_msg_data OUT NOCOPY VARCHAR2
1241 );
1242
1243 -- deliveryMerge
1244 G_NO_APPENDING VARCHAR2(1) := 'N';
1245 G_START_OF_STAGING VARCHAR2(1) := 'S';
1246 G_END_OF_STAGING VARCHAR2(1) := 'E';
1247 G_START_OF_PACKING VARCHAR2(1) := 'A';
1248 G_START_OF_SHIPPING VARCHAR2(1) := 'W';
1249
1250 --OTM R12
1251 PROCEDURE OTM_PRE_SHIP_CONFIRM
1252 (p_delivery_id IN NUMBER,
1253 p_delivery_name IN VARCHAR2 DEFAULT NULL,
1254 p_tms_interface_flag IN VARCHAR2,
1255 p_trip_id IN NUMBER,
1256 x_return_status OUT NOCOPY VARCHAR2);
1257 --
1258
1259 END WSH_INTERFACE_EXT_GRP;