1 PACKAGE WSH_FTE_CONSTRAINT_FRAMEWORK AUTHID CURRENT_USER as
2 /* $Header: WSHFCFWS.pls 120.2 2006/02/01 06:15:20 jnpinto noship $ */
3
4 -- Global Variables
5
6 g_hash_base NUMBER := 1;
7 g_hash_size NUMBER := power(2, 25);
8 g_is_fte_installed VARCHAR2(1) := WSH_UTIL_CORE.FTE_Is_Installed;
9 g_is_tp_installed VARCHAR2(1) := NULL;
10 g_session_id NUMBER;
11
12 G_SHIPORG_FACILITY VARCHAR2(30) := 'ORG_FAC';
13 G_SHIPORG_FACILITY_NUM NUMBER := 101;
14 G_CUSTOMER_FACILITY VARCHAR2(30) := 'CUS_FAC';
15 G_CUSTOMER_FACILITY_NUM NUMBER := 102;
16 G_SUPPLIER_FACILITY VARCHAR2(30) := 'SUP_FAC';
17 G_SUPPLIER_FACILITY_NUM NUMBER := 103;
18 G_FACILITY_CARRIER VARCHAR2(30) := 'FAC_CAR';
19 G_FACILITY_CARRIER_NUM NUMBER := 104;
20 G_FACILITY_MODE VARCHAR2(30) := 'FAC_MOD';
21 G_FACILITY_MODE_NUM NUMBER := 105;
22 G_FACILITY_VEHICLE VARCHAR2(30) := 'FAC_VEH';
23 G_FACILITY_VEHICLE_NUM NUMBER := 106;
24 G_ITEM_FACILITY VARCHAR2(30) := 'ITM_FAC';
25 G_ITEM_FACILITY_NUM NUMBER := 107;
26 G_ITEMCAT_FACILITY VARCHAR2(30) := 'ITC_FAC';
27 G_ITEMCAT_FACILITY_NUM NUMBER := 108;
28 G_CUSTOMER_CUSTOMER VARCHAR2(30) := 'CUS_CUS';
29 G_CUSTOMER_CUSTOMER_NUM NUMBER := 109;
30 G_ITEM_MODE VARCHAR2(30) := 'ITM_MOD';
31 G_ITEM_MODE_NUM NUMBER := 110;
32 G_ITEMCAT_MODE VARCHAR2(30) := 'ITC_MOD';
33 G_ITEMCAT_MODE_NUM NUMBER := 111;
34 G_ITEM_CARRIER VARCHAR2(30) := 'ITM_CAR';
35 G_ITEM_CARRIER_NUM NUMBER := 112;
36 G_ITEMCAT_CARRIER VARCHAR2(30) := 'ITC_CAR';
37 G_ITEMCAT_CARRIER_NUM NUMBER := 113;
38 G_ITEM_VEHICLE VARCHAR2(30) := 'ITM_VEH';
39 G_ITEM_VEHICLE_NUM NUMBER := 114;
40 G_ITEMCAT_VEHICLE VARCHAR2(30) := 'ITC_VEH';
41 G_ITEMCAT_VEHICLE_NUM NUMBER := 115;
42 G_ITEMCAT_ITEMCAT VARCHAR2(30) := 'ITC_ITC';
43 G_ITEMCAT_ITEMCAT_NUM NUMBER := 116;
44 G_ITEM_ITEM VARCHAR2(30) := 'ITM_ITM';
45 G_ITEM_ITEM_NUM NUMBER := 117;
46 G_ITEM_ITEMCAT VARCHAR2(30) := 'ITM_ITC';
47 G_ITEM_ITEMCAT_NUM NUMBER := 118;
48
49 --#REG-ZON
50 G_REGION_FACILITY VARCHAR2(30) := 'REG_FAC';
51 G_REGION_FACILITY_NUM NUMBER := 119;
52 --#REG-ZON
53
54 G_AUTOCRT_DLVY VARCHAR2(30) := 'ACD';
55 G_ASSIGN_DLVB_DLVY VARCHAR2(30) := 'ADD';
56 G_AUTOCRT_DLVY_TRIP VARCHAR2(30) := 'ACT';
57 G_AUTOCRT_MDC VARCHAR2(30) := 'ACM';
58 G_ASSIGN_DLVY_TRIP VARCHAR2(30) := 'ADT';
59 G_UNASSIGN_DLVY_TRIP VARCHAR2(30) := 'UDT';
60 G_PACK_DLVB VARCHAR2(30) := 'PKG';
61 G_CRT_TRIP_STOP VARCHAR2(30) := 'CTS';
62 G_DLEG_LANE_SEARCH VARCHAR2(30) := 'DLS';
63 G_DLEG_TRIP_SEARCH VARCHAR2(30) := 'DST';
64 G_DLEG_CRT VARCHAR2(30) := 'CDL';
65 G_CREATE_DLVY VARCHAR2(30) := 'CRD';
66 G_UPDATE_DLVY VARCHAR2(30) := 'UPD';
67 G_UPDATE_DLEG VARCHAR2(30) := 'UDL';
68 G_UPDATE_TRIP VARCHAR2(30) := 'UPT';
69 G_UPDATE_STOP VARCHAR2(30) := 'UPS';
70 G_DELETE_STOP VARCHAR2(30) := 'DTS';
71
72 G_FACILITY VARCHAR2(30) := 'FAC';
73 G_COMP_ORG VARCHAR2(30) := 'ORG';
74 G_COMP_CUSTOMER VARCHAR2(30) := 'CUS';
75 G_COMP_SUPPLIER VARCHAR2(30) := 'SUP';
76 G_COMP_CARRIER VARCHAR2(30) := 'CAR';
77
78 G_DELIVERY VARCHAR2(30) := 'DLVY';
79 G_DELIVERY_LEG VARCHAR2(30) := 'DLEG';
80 G_TRIP VARCHAR2(30) := 'TRIP';
81 G_STOP VARCHAR2(30) := 'STOP';
82 G_DEL_DETAIL VARCHAR2(30) := 'DLVB';
83 G_CAR_SERVICE VARCHAR2(30) := 'CRSV';
84 G_LANE VARCHAR2(30) := 'LANE';
85 G_LOCATION VARCHAR2(30) := 'LOCATION';
86 G_CALLING_API VARCHAR2(60) := NULL;
87 -- MDC Constraints
88 TYPE deconsol_output_rec_type IS RECORD (
89 deconsol_location NUMBER,
90 entity_id NUMBER,
91 validation_status VARCHAR2(1));
92
93 TYPE deconsol_output_tab_type IS TABLE OF deconsol_output_rec_type INDEX BY BINARY_INTEGER;
94 -- MDC Constraints end
95
96 TYPE comp_constraint_tab_type IS TABLE OF WSH_FTE_COMP_CONSTRAINTS%ROWTYPE INDEX BY BINARY_INTEGER;
97
98 TYPE comp_class_tab_type IS TABLE OF WSH_FTE_COMP_CLASSES%ROWTYPE INDEX BY BINARY_INTEGER;
99
100 TYPE line_constraint_rec_type IS RECORD (
101 line_constraint_index NUMBER
102 , entity_type VARCHAR2(30)
103 , entity_line_id NUMBER
104 , constraint_id NUMBER
105 , constraint_class_code VARCHAR2(30)
106 , violation_type VARCHAR2(1)
107 );
108
109 TYPE line_constraint_tab_type IS TABLE OF line_constraint_rec_type INDEX BY BINARY_INTEGER;
110
111 -- Indexed by hash of comp_class code
112 g_comp_class_tab comp_class_tab_type;
113
114 -- Indexed by hash of attributes that constitute a unique combination
115 g_comp_constraint_tab comp_constraint_tab_type;
116
117 /* Following are entity based record structures
118 to be used for constraint validations */
119
120 TYPE detail_ccinfo_rec_type IS RECORD (
121 DELIVERY_DETAIL_ID NUMBER
122 , DELIVERY_ID NUMBER DEFAULT NULL
123 , EXISTS_IN_DATABASE VARCHAR2(1)
124 , CUSTOMER_ID NUMBER
125 , INVENTORY_ITEM_ID NUMBER
126 , SHIP_FROM_LOCATION_ID NUMBER
127 , ORGANIZATION_ID NUMBER
128 , SHIP_TO_LOCATION_ID NUMBER
129 , INTMED_SHIP_TO_LOCATION_ID NUMBER
130 , RELEASED_STATUS VARCHAR2(1)
131 , CONTAINER_FLAG VARCHAR2(1)
132 , DATE_REQUESTED DATE
133 , DATE_SCHEDULED DATE
134 , SHIP_METHOD_CODE VARCHAR2(30)
135 , CARRIER_ID NUMBER
136 , PARTY_ID NUMBER
137 , LINE_DIRECTION VARCHAR2(30)
138 , SHIPPING_CONTROL VARCHAR2(30)
139 --#DUM_LOC(S) Will be populated with PHYSICAL Location id
140 --when Ship to location is dummy.
141 , PHYSICAL_SHIP_TO_LOCATION_ID NUMBER DEFAULT NULL
142 --#DUM_LOC(E)
143 );
144
145
146 TYPE detail_ccinfo_tab_type IS TABLE OF detail_ccinfo_rec_type INDEX BY BINARY_INTEGER;
147
148 TYPE delivery_ccinfo_rec_type IS RECORD (
149 DELIVERY_ID NUMBER
150 , TRIP_ID NUMBER DEFAULT NULL
151 , EXISTS_IN_DATABASE VARCHAR2(1)
152 , NAME VARCHAR2(30)
153 , PLANNED_FLAG VARCHAR2(1)
154 , STATUS_CODE VARCHAR2(2)
155 , INITIAL_PICKUP_DATE DATE
156 , INITIAL_PICKUP_LOCATION_ID NUMBER
157 , ULTIMATE_DROPOFF_LOCATION_ID NUMBER
158 , ULTIMATE_DROPOFF_DATE DATE
159 , CUSTOMER_ID NUMBER
160 , INTMED_SHIP_TO_LOCATION_ID NUMBER
161 , SHIP_METHOD_CODE VARCHAR2(30)
162 --alksharm
163 , DELIVERY_TYPE VARCHAR2(30)
164 , CARRIER_ID NUMBER
165 , ORGANIZATION_ID NUMBER
166 , SERVICE_LEVEL VARCHAR2(30)
167 , MODE_OF_TRANSPORT VARCHAR2(30)
168 , PARTY_ID NUMBER
169 , SHIPMENT_DIRECTION VARCHAR2(30)
170 , SHIPPING_CONTROL VARCHAR2(30)
171 --#DUM_LOC(S) Will be populated with PHYSICAL location id
172 --when drop off location id is dummy.
173 , PHYSICAL_DROPOFF_LOCATION_ID NUMBER DEFAULT NULL
174 --#DUM_LOC(E)
175 );
176
177
178 TYPE delivery_ccinfo_tab_type IS TABLE OF delivery_ccinfo_rec_type INDEX BY BINARY_INTEGER;
179
180 TYPE dleg_ccinfo_rec_type IS RECORD (
181 DELIVERY_LEG_ID NUMBER
182 , EXISTS_IN_DATABASE VARCHAR2(1)
183 , DELIVERY_ID NUMBER
184 , PARENT_DELIVERY_LEG_ID NUMBER
185 , SEQUENCE_NUMBER NUMBER
186 , CARRIER_ID NUMBER
187 , SERVICE_LEVEL VARCHAR2(30)
188 , MODE_OF_TRANSPORT VARCHAR2(30)
189 , PICK_UP_STOP_ID NUMBER
190 , DROP_OFF_STOP_ID NUMBER
191 , PICKUPSTOP_LOCATION_ID NUMBER
192 , DROPOFFSTOP_LOCATION_ID NUMBER
193 , PICKUP_STOP_PA_DATE DATE
194 , DROPOFF_STOP_PA_DATE DATE
195 );
196
197 TYPE dleg_ccinfo_tab_type IS TABLE OF dleg_ccinfo_rec_type INDEX BY BINARY_INTEGER;
198
199 TYPE trip_ccinfo_rec_type IS RECORD (
200 TRIP_ID NUMBER
201 , EXISTS_IN_DATABASE VARCHAR2(1)
202 , NAME VARCHAR2(30)
203 , PLANNED_FLAG VARCHAR2(1)
204 , STATUS_CODE VARCHAR2(2)
205 , VEHICLE_ITEM_ID NUMBER
206 , VEHICLE_NUMBER VARCHAR2(30)
207 , CARRIER_ID NUMBER
208 , SHIP_METHOD_CODE VARCHAR2(30)
209 , VEHICLE_ORGANIZATION_ID NUMBER
210 , VEHICLE_NUM_PREFIX VARCHAR2(10)
211 , SERVICE_LEVEL VARCHAR2(30)
212 , MODE_OF_TRANSPORT VARCHAR2(30)
213 );
214
215 TYPE trip_ccinfo_tab_type IS TABLE OF trip_ccinfo_rec_type INDEX BY BINARY_INTEGER;
216
217 TYPE stop_ccinfo_rec_type IS RECORD (
218 STOP_ID NUMBER
219 , EXISTS_IN_DATABASE VARCHAR2(1)
220 , TRIP_ID NUMBER DEFAULT NULL
221 , STOP_LOCATION_ID NUMBER
222 , STATUS_CODE VARCHAR2(2)
223 , STOP_SEQUENCE_NUMBER NUMBER
224 , PLANNED_ARRIVAL_DATE DATE
225 , PLANNED_DEPARTURE_DATE DATE
226 , ACTUAL_ARRIVAL_DATE DATE
227 , ACTUAL_DEPARTURE_DATE DATE
228 --#DUM_LOC(S)
229 , PHYSICAL_LOCATION_ID NUMBER DEFAULT NULL
230 , PHYSICAL_STOP_ID NUMBER DEFAULT NULL
231 --#DUM_LOC(E)
232 );
233
234 TYPE stop_ccinfo_tab_type IS TABLE OF stop_ccinfo_rec_type INDEX BY BINARY_INTEGER;
235
236 TYPE lane_ccinfo_rec_type IS RECORD (
237 LANE_ID NUMBER
238 , LANE_NUMBER VARCHAR2(30)
239 , LANE_TYPE VARCHAR2(20)
240 , OWNER_ID NUMBER
241 , CARRIER_ID NUMBER
242 , ORIGIN_ID NUMBER
243 , DESTINATION_ID NUMBER
244 , MODE_OF_TRANSPORTATION_CODE VARCHAR2(30)
245 );
246
247 TYPE lane_ccinfo_tab_type IS TABLE OF lane_ccinfo_rec_type INDEX BY BINARY_INTEGER;
248
249 TYPE target_tripstop_cc_rec_type IS RECORD (
250 PICKUP_STOP_ID NUMBER
251 , PICKUP_STOP_SEQ NUMBER
252 , PICKUP_STOP_PA_DATE DATE
253 , PICKUP_STOP_PD_DATE DATE
254 , DROPOFF_STOP_ID NUMBER
255 , DROPOFF_STOP_SEQ NUMBER
256 , DROPOFF_STOP_PA_DATE DATE
257 , DROPOFF_STOP_PD_DATE DATE
258 , PICKUP_LOCATION_ID NUMBER
259 , DROPOFF_LOCATION_ID NUMBER
260 );
261
262 TYPE target_tripstop_cc_tab_type IS TABLE of target_tripstop_cc_rec_type INDEX BY BINARY_INTEGER;
263
264 TYPE item_item_mustuse_rec_type IS RECORD (
265 input_item_id NUMBER
266 , input_itemorg_id NUMBER
267 , target_item_id NUMBER
268 , target_itemorg_id NUMBER
269 , carrier_result VARCHAR2(1)
270 , mode_result VARCHAR2(1)
271 , vehicle_result VARCHAR2(1)
272 , hash_string VARCHAR2(200)
273 );
274
275 TYPE item_item_mustuse_tab_type IS TABLE of item_item_mustuse_rec_type INDEX BY BINARY_INTEGER;
276
277 TYPE item_location_mustuse_rec_type IS RECORD (
278 target_location_id NUMBER
279 , input_item_id NUMBER
280 , input_itemorg_id NUMBER
281 , carrier_result VARCHAR2(1)
282 , mode_result VARCHAR2(1)
283 , vehicle_result VARCHAR2(1)
284 , hash_string VARCHAR2(200)
285 );
286
287 TYPE item_location_mustuse_tab_type IS TABLE of item_location_mustuse_rec_type INDEX BY BINARY_INTEGER;
288
289 TYPE item_exclusive_rec_type IS RECORD (
290 comp_class_code VARCHAR2(30)
291 , item_id NUMBER
292 , itemorg_id NUMBER
293 , object2_id NUMBER
294 , object2_char VARCHAR2(30)
295 , validate_result VARCHAR2(1)
296 , hash_string VARCHAR2(200)
297 );
298
299 TYPE item_exclusive_tab_type IS TABLE of item_exclusive_rec_type INDEX BY BINARY_INTEGER;
300
301 TYPE fac_exclusive_rec_type IS RECORD (
302 comp_class_code VARCHAR2(30)
303 , object1_type VARCHAR2(30)
304 , object1_id NUMBER
305 , object2_id NUMBER
306 , object2_char VARCHAR2(30)
307 , validate_result VARCHAR2(1)
308 , hash_string VARCHAR2(200)
309 );
310
311 TYPE fac_exclusive_tab_type IS TABLE of fac_exclusive_rec_type INDEX BY BINARY_INTEGER;
312
313 --#REG-ZON
314 TYPE loc_reg_constraint_rec_type IS RECORD (
315 location_id NUMBER
316 , object2_id NUMBER
317 , constraint_id NUMBER
318 , constraint_type VARCHAR2(1)
319 , hash_string VARCHAR2(200)
320 );
321
322 TYPE loc_reg_constraint_tab_type IS TABLE of loc_reg_constraint_rec_type INDEX BY BINARY_INTEGER;
323 --#REG-ZON
324
325 /* END entity record structures */
326
327 -- Public APIs for Shipping/Transportation events
328
329 -- Whenever a calling application calls any of the following entity dependent
330 -- APIs, they should index p_exception_list by the appropriate
331 -- global variables for the specific compability class code provided here
332 -- the API will skip the compatibility classes that have been mentioned in p_exception_list
333 -- If nothing is provided, the API will perform all checks
334 -- Can be useful if constraint checks are done in stages
335
336 --***************************************************************************--
337
338 --========================================================================
339 -- PROCEDURE : validate_constraint_dlvy Called by constraint Wrapper API
340 -- and the Group API.
341 --
342 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
343 -- p_action_code Predefined action code
344 -- p_exception_list Compatibility classes to skip if any
345 -- indexed by class code numbers
346 -- p_in_ids Table of delivery ids to process
347 -- Either of the next two should be passed
348 -- p_delivery_info Table of delivery records to process
349 -- Only one of p_in_ids and p_delivery_info should be passed
350 -- p_dlvy_assigned_lines Table of assigned delivery details of
351 -- input deliveries. Pass if queried information available
352 -- If not passed, the API will query
353 -- p_target_trip Applicable for Assign delivery to Trip only
354 -- Record of target trip information
355 -- p_target_tripstops Input pickup and dropoff stop/location of the delivery(s)
356 -- in that target trip
357 -- p_target_trip_assign_dels Table of deliveries in target trip
358 -- If not passed, the API will query
359 -- p_target_trip_dlvy_lines Table of delivery details in target trip
360 -- If not passed, the API will query
361 -- p_target_trip_incl_stops Table of tripstops already in the target trip_
362 -- If not passed, the API will query
363 -- x_validate_result Constraint Validation result : S / F
364 -- x_line_groups Includes Successful and warning lines
365 -- after constraint check
366 -- Contains information of which input delivery should
367 -- be grouped in which output group for trip creation
368 -- x_group_info Output groups for input deliveries
369 -- x_failed_lines Table of input delivery lines that failed
370 -- constraint check
371 -- x_msg_count Number of messages in the list
372 -- x_msg_data Text of messages
373 -- x_return_status Return status
374 -- COMMENT : This procedure is used to perform for following actions
375 -- Can be called for
376 -- A. Autocreate Trip (ACT)
377 -- B. Assign Delivery to Trip (ADT)
378 -- C. Update a delivery (UPD)
379 -- D. Manually create a delivery (CRD)
380 -- When specifying a target trip, group all deliveries that are being planned
381 -- to the target trip
382 --========================================================================
383
384
385 PROCEDURE validate_constraint_dlvy(
386 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
387 p_action_code IN VARCHAR2 DEFAULT NULL,
388 p_exception_list IN WSH_UTIL_CORE.Column_Tab_Type,
389 p_in_ids IN WSH_UTIL_CORE.id_tab_type,
390 p_delivery_info IN delivery_ccinfo_tab_type,
391 p_dlvy_assigned_lines IN detail_ccinfo_tab_type,
392 p_target_trip IN trip_ccinfo_rec_type,
393 p_target_tripstops IN OUT NOCOPY target_tripstop_cc_rec_type,
394 p_target_trip_assign_dels IN delivery_ccinfo_tab_type,
395 p_target_trip_dlvy_lines IN detail_ccinfo_tab_type,
396 p_target_trip_incl_stops IN stop_ccinfo_tab_type,
397 x_validate_result OUT NOCOPY VARCHAR2,
398 x_line_groups OUT NOCOPY WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type,
399 x_group_info OUT NOCOPY WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type,
400 x_failed_lines OUT NOCOPY WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type,
401 x_msg_count OUT NOCOPY NUMBER,
402 x_msg_data OUT NOCOPY VARCHAR2,
403 x_return_status OUT NOCOPY VARCHAR2);
404
405
406 --***************************************************************************--
407
408 --========================================================================
409 -- PROCEDURE : validate_constraint_dleg Called by constraint Wrapper API
410 -- and the Lane search API.
411 --
412 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
413 -- p_action_code Predefined action code
414 -- p_exception_list Compatibility classes to skip if any
415 -- indexed by class code numbers
416 -- p_delivery_leg_rec Input delivery leg record
417 -- p_target_trip Table of target trips for delivery leg trip search
418 -- p_target_lane Table of target lane for delivery leg lane search
419 -- x_succ_trips List of input trips that passed constraint check
420 -- x_succ_lanes List of input lanes that passed constraint check
421 -- x_validate_result Constraint Validation result : S / F
422 -- x_msg_count Number of messages in the list
423 -- x_msg_data Text of messages
424 -- x_return_status Return status
425 -- COMMENT : This procedure is used to perform for following actions
426 -- A. Lane search for delivery leg (DLS)
427 --========================================================================
428
429 PROCEDURE validate_constraint_dleg(
430 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
431 p_action_code IN VARCHAR2 DEFAULT NULL,
432 p_exception_list IN WSH_UTIL_CORE.Column_Tab_Type,
433 p_delivery_leg_rec IN dleg_ccinfo_rec_type,
434 p_target_trip IN trip_ccinfo_tab_type,
435 p_target_lane IN lane_ccinfo_tab_type,
436 x_succ_trips OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
437 x_succ_lanes OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
438 x_validate_result OUT NOCOPY VARCHAR2,
439 x_msg_count OUT NOCOPY NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2,
441 x_return_status OUT NOCOPY VARCHAR2);
442
443
444 --***************************************************************************--
445
446 --========================================================================
447 -- PROCEDURE : validate_constraint_dlvb Called by constraint Wrapper API
448 -- and the Group API.
449 --
450 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
451 -- p_action_code Predefined action code
452 -- p_exception_list Compatibility classes to skip if any
453 -- indexed by class code numbers
454 -- p_in_ids Table of delivery ids to process
455 -- Either of the next two should be passed
456 -- p_del_detail_info Table of delivery detail records to process
457 -- Only one of p_in_ids and p_del_detail_info should be passed
458 -- p_target_delivery Applicable for Assign delivery detail to delivery only
459 -- Record of target delivery information
460 -- p_target_container Applicable for Assign delivery detail to container only
461 -- Record of target container information
462 -- If not passed, the API will query
463 -- p_dlvy_assigned_lines Table of delivery details already in target
464 -- delivery or target container
465 -- If not passed, the API will query
466 -- x_validate_result Constraint Validation result : S / F
467 -- x_failed_lines Table of input delivery lines that failed
468 -- constraint check
469 -- x_line_groups Includes Successful and warning lines
470 -- after constraint check
471 -- Contains information of which input delivery should
472 -- be grouped in which output group for trip creation
473 -- x_group_info Output groups for input deliveries
474 -- x_msg_count Number of messages in the list
475 -- x_msg_data Text of messages
476 -- x_return_status Return status
477 -- COMMENT : This procedure is used to perform for following actions
478 -- A. Autocreate Delivery (ACD)
479 -- B. Assign detail to Delivery (ADD)
480 -- C. Packing (PKG)
481 -- When specifying a target delivery, group all delivery lines that are being planned
482 -- to the target delivery
483 --========================================================================
484
485 PROCEDURE validate_constraint_dlvb(
486 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
487 p_action_code IN VARCHAR2 DEFAULT NULL,
488 p_exception_list IN WSH_UTIL_CORE.Column_Tab_Type,
489 p_in_ids IN WSH_UTIL_CORE.id_tab_type,
490 p_del_detail_info IN detail_ccinfo_tab_type,
491 p_target_delivery IN delivery_ccinfo_rec_type,
492 p_target_container IN detail_ccinfo_rec_type,
493 p_dlvy_assigned_lines IN detail_ccinfo_tab_type,
494 x_validate_result OUT NOCOPY VARCHAR2,
495 x_failed_lines OUT NOCOPY WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type,
496 x_line_groups OUT NOCOPY WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type,
497 x_group_info OUT NOCOPY WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type,
498 x_msg_count OUT NOCOPY NUMBER,
499 x_msg_data OUT NOCOPY VARCHAR2,
500 x_return_status OUT NOCOPY VARCHAR2);
501
502
503 --***************************************************************************--
504
505 --========================================================================
506 -- PROCEDURE : validate_constraint_trip Called by constraint Wrapper API
507 -- and the Group API.
508 --
509 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
510 -- p_action_code Predefined action code
511 -- p_exception_list Compatibility classes to skip if any
512 -- indexed by class code numbers
513 -- p_trip_info Table of Trip records to process
514 -- p_trip_assigned_dels Table of delivery records already in the trip
515 -- If not passed, the API will query
516 -- p_trip_dlvy_lines Table of delivery details already in the trip
517 -- If not passed, the API will query
518 -- p_trip_incl_stops Table of Stop records already in the trip
519 -- If not passed, the API will query
520 -- x_fail_trips Table of input trips that failed constraint check
521 -- x_validate_result Constraint Validation result : S / F
522 -- x_msg_count Number of messages in the list
523 -- x_msg_data Text of messages
524 -- x_return_status Return status
525 -- COMMENT : This procedure is used to perform for following actions
526 -- A. Update an existing trip (UPT)
527 --========================================================================
528
529 PROCEDURE validate_constraint_trip(
530 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
531 p_action_code IN VARCHAR2 DEFAULT NULL,
532 p_exception_list IN WSH_UTIL_CORE.Column_Tab_Type,
533 p_trip_info IN trip_ccinfo_tab_type,
534 p_trip_assigned_dels IN delivery_ccinfo_tab_type,
535 p_trip_dlvy_lines IN detail_ccinfo_tab_type,
536 p_trip_incl_stops IN stop_ccinfo_tab_type,
537 x_fail_trips OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
538 x_validate_result OUT NOCOPY VARCHAR2,
539 x_msg_count OUT NOCOPY NUMBER,
540 x_msg_data OUT NOCOPY VARCHAR2,
541 x_return_status OUT NOCOPY VARCHAR2);
542
543 --***************************************************************************--
544
545 --========================================================================
546 -- PROCEDURE : validate_constraint_stop Called by constraint Wrapper API
547 -- and the Group API.
548 --
549 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
550 -- p_action_code Predefined action code
551 -- p_exception_list Compatibility classes to skip if any
552 -- indexed by class code numbers
553 -- p_stop_info Table of Stop records to process
554 -- p_parent_trip_info Table of Parent Trip records
555 -- If not passed, the API will query
556 -- x_fail_stops Table of input stops that failed constraint check
557 -- x_validate_result Constraint Validation result : S / F
558 -- x_msg_count Number of messages in the list
559 -- x_msg_data Text of messages
560 -- x_return_status Return status
561 -- COMMENT : This procedure is used to perform for following actions
562 -- A. Create a new stop ( CTS)
563 -- B. Update an existing stop (UPS)
564 -- C. Delete a stop (DTS)
565 --========================================================================
566
567 PROCEDURE validate_constraint_stop(
568 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
569 p_action_code IN VARCHAR2 DEFAULT NULL,
570 p_exception_list IN WSH_UTIL_CORE.Column_Tab_Type,
571 p_stop_info IN stop_ccinfo_tab_type,
572 p_parent_trip_info IN trip_ccinfo_tab_type,
573 x_fail_stops OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
574 x_validate_result OUT NOCOPY VARCHAR2,
575 x_msg_count OUT NOCOPY NUMBER,
576 x_msg_data OUT NOCOPY VARCHAR2,
577 x_return_status OUT NOCOPY VARCHAR2);
578
579 -- Utility APIs
580
581 --***************************************************************************--
582
583 --========================================================================
584 -- PROCEDURE : get_constraint_display Called by constraint Wrapper API
585 -- and FTE constraint UI
586 --
587 -- PARAMETERS: p_constraint_id Constraint id to get information about
588 -- x_obj1_display Display name of constraint object1 in input constraint
589 -- x_obj1_parent_display Display name of constraint object1 parent
590 -- x_obj2_display Display name of constraint object2 in input constraint
591 -- x_obj2_parent_display Display name of constraint object2 parent
592 -- x_condition_display Display name of constraint type
593 -- (Exclusive / Inclusive) in input constraint
594 -- x_fac1_company_type Display name of company type if constraint object1
595 -- is a facility
596 -- x_fac1_company_name Display name of company name if constraint object1
597 -- is a facility
598 -- x_fac2_company_type Display name of company type if constraint object2
599 -- is a facility
600 -- x_fac2_company_name Display name of company name if constraint object2
601 -- is a facility
602 -- x_comp_class_code Display meaning of compatibility class code
603 -- for the input constraint
604 -- x_severity Display meaning of severity setting of the
605 -- compatibility class for the input constraint
606 -- x_return_status Return status
607 -- COMMENT : This procedure is used to perform for following actions
608 -- Takes as input a constraint id
609 -- and returns the names of the objects
610 -- which are associated by this constraint definition
611 --========================================================================
612
613 PROCEDURE get_constraint_display(
614 p_constraint_id IN NUMBER,
615 x_obj1_display OUT NOCOPY VARCHAR2,
616 x_obj1_parent_display OUT NOCOPY VARCHAR2,
617 x_obj2_display OUT NOCOPY VARCHAR2,
618 x_obj2_parent_display OUT NOCOPY VARCHAR2,
619 x_condition_display OUT NOCOPY VARCHAR2,
620 x_fac1_company_type OUT NOCOPY VARCHAR2,
621 x_fac1_company_name OUT NOCOPY VARCHAR2,
622 x_fac2_company_type OUT NOCOPY VARCHAR2,
623 x_fac2_company_name OUT NOCOPY VARCHAR2,
624 x_comp_class_code OUT NOCOPY VARCHAR2,
625 x_severity OUT NOCOPY VARCHAR2,
626 x_return_status OUT NOCOPY VARCHAR2 );
627
628
629 --***************************************************************************--
630
631 --========================================================================
632 -- FUNCTION : is_last_trip PUBLIC
633 --
634 -- PARAMETERS: p_delivery_id Input delivery
635 -- p_initial_pu_loc_id Delivery's initial pickup location
636 -- p_ultimate_do_loc_id Delivery's ultimate dropoff location
637 -- Above two are queried if any of them found to be NULL
638 -- p_target_stops_in_trip Input pickup and dropoff stop/location of the delivery(s)
639 -- in the target trip in case of assign delivery to trip
640 -- p_target_trip_id Target trip
641 -- x_return_status Return status
642 -- COMMENT :
643 -- Applicable for Assign delivery to Trip
644 -- Determines whether the target trip is the last trip for an input delivery
645 -- in order to finish the multileg delivery
646 --========================================================================
647
648 FUNCTION is_last_trip (
649 p_delivery_id IN NUMBER,
650 p_initial_pu_loc_id IN NUMBER DEFAULT NULL,
651 p_ultimate_do_loc_id IN NUMBER DEFAULT NULL,
652 p_target_trip_id IN NUMBER,
653 p_target_stops_in_trip IN target_tripstop_cc_rec_type,
654 x_return_status OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN;
655
656 PROCEDURE populate_constraint_cache(
657 p_comp_class_code IN VARCHAR2 DEFAULT NULL,
658 x_return_status OUT NOCOPY VARCHAR2);
659
660
661 /*--========================================================================
662 -- PROCEDURE : validate_constraint PRIVATE
663 --
664 -- PARAMETERS: p_comp_class_code Compatibility class code
665 -- p_constraint_type Constraint Type : Exclusive / Inclusive
666 -- p_object1_type Constraint Object1 Type
667 -- p_object1_parent_id Constraint Object1 Parent id
668 -- Required only if object1 = Item
669 -- p_object1_val_num Constraint Object1 id
670 -- p_object1_val_char Constraint Object1 character code
671 -- p_object1_physical_id Physical Location Id - Passed in only for CUS_FAC for Customer Facility
672 -- p_object2_type Constraint Object2 Type
673 -- p_object2_parent_id Constraint Object2 Parent id
674 -- p_object2_val_num Constraint Object2 id
675 -- p_object2_val_char Constraint Object2 character code
676 -- Currently used for Mode of Transport code only
677 -- p_direct_shipment Is the shipment multileg
678 -- x_validate_result Constraint Validation result : S / E / W
679 -- x_failed_constraint failed constraint id in case of failure, null if success
680 -- x_return_status Return status
681 -- COMMENT :
682 -- This is an internal procedure to determine if two objects
683 -- have a constraint defined between them under the scope of a particular compatibility class
684 -- Hence, returns constraint severity
685 -- 1. Passed I : if a constraint for this combination exists of type 'I'
686 -- 2. Passed E : if a constraint for this combination exists of type 'E'
687
688 -- Assumes object1 and object2 have been passed as the class requires/defines
689 --========================================================================
690
691 PROCEDURE validate_constraint(
692 p_comp_class_code IN VARCHAR2,
693 p_constraint_type IN VARCHAR2 DEFAULT 'E',
694 p_object1_type IN VARCHAR2 DEFAULT NULL,
695 p_object1_parent_id IN NUMBER DEFAULT NULL,
696 p_object1_val_char IN VARCHAR2 DEFAULT NULL,
697 p_object1_val_num IN NUMBER DEFAULT NULL,
698 p_object1_physical_id IN NUMBER DEFAULT NULL,
699 p_object2_type IN VARCHAR2 DEFAULT NULL,
700 p_object2_parent_id IN NUMBER DEFAULT NULL,
701 p_object2_val_char IN VARCHAR2 DEFAULT NULL,
702 p_object2_val_num IN NUMBER DEFAULT NULL,
703 p_direct_shipment IN BOOLEAN DEFAULT FALSE,
704 x_validate_result OUT NOCOPY VARCHAR2,
705 x_failed_constraint OUT NOCOPY line_constraint_rec_type,
706 x_return_status OUT NOCOPY VARCHAR2);*/
707
708 -- MDC Constraints
709
710 --***************************************************************************--
711 --========================================================================
712 -- PROCEDURE : VALIDATE_CONSTRAINT_DECONSOL
713 --
714 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
715 -- p_delivery_info Table of delivery records to process
716 -- Only one of p_in_ids and p_delivery_info should be passed
717 -- p_in_ids Table of delivery ids to process
718 -- p_rule_deconsol_location Default deconsolidation location passed
719 -- p_rule_override_deconsol If true, default deconsolidation location passed
720 -- takes precedence.
721 -- x_output_id_tab Output tab with deconsol location specified for deliveries
722 -- x_msg_count Number of messages in the list
723 -- x_msg_data Text of messages
724 -- x_return_status Return status
725 -- COMMENT : This procedure stamps each of the deliveries with deconsolidation locations,
726 -- procedure is to find deconsolidation locations for a group of deliveries passed
727 --========================================================================
728 PROCEDURE validate_constraint_deconsol( p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
729 p_delivery_info IN OUT NOCOPY delivery_ccinfo_tab_type,
730 p_in_ids IN wsh_util_core.id_tab_type,
731 p_rule_deconsol_location IN NUMBER default NULL,
732 p_rule_override_deconsol IN BOOLEAN DEFAULT FALSE,
733 p_rule_to_zone_id IN NUMBER DEFAULT NULL,
734 p_caller IN VARCHAR2 DEFAULT NULL,
735 x_output_id_tab OUT NOCOPY deconsol_output_tab_type,
736 x_return_status OUT NOCOPY VARCHAR2,
737 x_msg_count OUT NOCOPY NUMBER,
738 x_msg_data OUT NOCOPY VARCHAR2);
739
740 -- MDC Constraints end
741
742 --***************************************************************************--
743 --========================================================================
744 -- PROCEDURE : get_customer_from_loc PRIVATE
745 --
746 -- PARAMETERS: p_location_id Input Location id
747 -- x_customer_id Carrier at the input location
748 -- x_return_status Return status
749 -- COMMENT :
750 -- Returns the customer id of the customer
751 -- having a location at input wsh location id
752 --========================================================================
753
754 PROCEDURE get_customer_from_loc(
755 p_location_id IN NUMBER,
756 --x_customer_id OUT NOCOPY NUMBER,
757 x_customer_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
758 x_return_status OUT NOCOPY VARCHAR2);
759
760 --***************************************************************************--
761 --========================================================================
762 -- PROCEDURE : get_org_from_location PRIVATE
763 --
764 -- PARAMETERS: p_location_id Input Location id
765 -- x_organization_tab Organizations for the input location
766 -- x_return_status Return status
767 -- COMMENT :
768 -- Returns table of organizations for location.
769 --========================================================================
770 PROCEDURE get_org_from_location(
771 p_location_id IN NUMBER,
772 x_organization_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
773 x_return_status OUT NOCOPY VARCHAR2);
774
775 END WSH_FTE_CONSTRAINT_FRAMEWORK;
776
[Home] [Help]