1 PACKAGE WSH_NEW_DELIVERY_ACTIONS AUTHID CURRENT_USER as
2 /* $Header: WSHDEACS.pls 120.4.12020000.1 2012/06/27 11:46:27 appldev ship $ */
3
4 G_USER_NAME VARCHAR2(200);
5 g_error_level VARCHAR2(1);
6
7 /********************************************************************************
8 * The following global variables are added as part of Ship Message
9 * Customization Project. These varibles are accessed from WSHDEACB.pls and
10 * WSHDEVLB.pls. Variables ending with _act -> activity and _msg -> message
11 ******************************************************************************/
12 g_ship_confirm_act CONSTANT VARCHAR2(200) := 'SHIP_CONFIRM_MESSAGE';
13 g_missing_inv_cntl_msg CONSTANT VARCHAR2(200) := 'MISSING_CONTROLS';
14 g_break_ship_set_msg CONSTANT VARCHAR2(200) := 'BREAK_SHIP_SET';
15 g_break_smc_msg CONSTANT VARCHAR2(200) := 'BREAK_SMC';
16 g_invalid_material_status_msg CONSTANT VARCHAR2(200) := 'INVALID_MATERIAL_STATUS';
17
18 /*The following variable is checked for count in CONFIRM_DELIVERY Procedure.
19 Value for this varibale is set in WSH_DELIVERY_VALIDATIONS.CHECK_CONFIRM Procedure*/
20 g_break_ship_set_or_smc NUMBER := 0;
21
22 /********************* Ship Message Customization Changes End ****************/
23
24
25 TYPE ship_method_type is TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
26
27 /* J TP Release */
28 --
29 -- Procedure: FIRM
30 -- Parameters: p_del_rows - Delivery_ids to be firmed
31 -- x_return_status - status of procedure call
32 -- Description: This procedure will firm a delivery
33 --
34 PROCEDURE FIRM
35 (p_del_rows IN wsh_util_core.id_tab_type,
36 x_return_status OUT NOCOPY VARCHAR2);
37
38 --
39 -- Procedure: Plan
40 -- Parameters: p_del_rows - Delivery_ids to be planned
41 -- x_return_status - status of procedure call
42 -- Description: This procedure will Plan a delivery for shipment
43 --
44 PROCEDURE Plan
45 (p_del_rows IN wsh_util_core.id_tab_type,
46 x_return_status OUT NOCOPY VARCHAR2,
47 p_called_for_sc IN BOOLEAN default false);
48
49 --
50 -- Procedure: Unplan
51 -- Parameters: p_del_rows - Delivery_ids of deliveries to be unplanned
52 -- x_return_status - status of procedure call
53 -- Description: This procedure will unplan a delivery
54 --
55
56 PROCEDURE Unplan
57 (p_del_rows IN wsh_util_core.id_tab_type,
58 x_return_status OUT NOCOPY VARCHAR2);
59
60
61 -- Bug: 2052963
62 -- Procedure: Get_Delivery_Defaults
63 -- Parameters: p_del_rows - Delivery_ids of deliveries to be confirmed
64 -- Parameters: p_org_ids - Organization ids of deliveries to be confirmed
65 -- x_autocreate_flag - enables/disables trip information
66 -- x_autointransit_flag - enables/disables setting in-transit for trip
67 -- x_autoclose_flag - enables/disables setting trip closure
68 -- x_return_status - status of procedure call
69 -- x_sc_rule_id - if all deliveries belong to same org and hence
70 -- have a common ship confirm rule specified
71 -- x_ac_bol_flag - along with Ship Confirm Rule,what is the
72 -- value of BOL flag
73 -- x_defer_interface_flag - Value for the Ship Confirm Rule
74 -- x_sc_rule_name - Ship Confirm Rule Name
75
76 -- Description: Gets the Default Delivery Parameters
77 -- Including Document Set(id and Name) for the Org.
78 -- associated with the Delivery(s).
79 --
80
81 PROCEDURE Get_Delivery_Defaults
82 (p_del_rows IN wsh_util_core.id_tab_type,
83 p_org_ids IN wsh_util_core.id_tab_type,
84 p_client_ids IN wsh_util_core.id_tab_type, --Modified R12.1.1 LSP PROJECT
85 p_ship_method_code_vals IN ship_method_type,
86 x_autointransit_flag OUT NOCOPY VARCHAR2,
87 x_autoclose_flag OUT NOCOPY VARCHAR2,
88 x_report_set_id OUT NOCOPY NUMBER,
89 x_report_set_name OUT NOCOPY VARCHAR2,
90 x_ship_method_name OUT NOCOPY VARCHAR2,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_sc_rule_id OUT NOCOPY NUMBER,
93 x_ac_bol_flag OUT NOCOPY VARCHAR2,
94 x_defer_interface_flag OUT NOCOPY VARCHAR2,
95 x_sc_rule_name OUT NOCOPY VARCHAR2
96
97 );
98
99
100 -- Procedure: Confirm_Delivery
101 -- Parameters: p_del_rows - Delivery_ids of deliveries to be confirmed
102 -- p_action_flag - 'S' for Ship Entered, Ship Unspecified Full
103 -- 'B' for Ship Entered, Backorder Unspecified
104 -- 'A' Ship All
108 -- p_report_set_id - report set for delivery
105 -- p_intransit_flag - 'Y' for autocreate_trip closes first stop
106 -- p_autoclose_flag - 'Y' closes autocreated trip and stops
107 -- p_stage_del_flag - 'Y' creates a new delivery for the staged lines
109 -- p_ship_method - ship method for autocreated trip
110 -- p_actual_dep_date - actual departure date for pickup stop on autocreated trip
111 -- p_defer_interface_flag - 'Y' to skip concurrent program submission, bug 1578251
112 -- p_send_945_flag - 'Y' to trigger outbound shipment advice for delivery with WSH lines
113 -- x_return_status - status of procedure call
114 -- Description: This procedure will update shipped quantities of the details
115 -- on each delivery and confirms each delivery
116
117
118 PROCEDURE Confirm_Delivery
119 (p_del_rows IN wsh_util_core.id_tab_type,
120 p_action_flag IN VARCHAR2,
121 p_intransit_flag IN VARCHAR2,
122 p_close_flag IN VARCHAR2,
123 p_stage_del_flag IN VARCHAR2,
124 p_report_set_id IN NUMBER,
125 p_ship_method IN VARCHAR2,
126 p_actual_dep_date IN DATE,
127 p_bol_flag IN VARCHAR2,
128 p_mc_bol_flag IN VARCHAR2 DEFAULT 'N',
129 p_defer_interface_flag IN VARCHAR2,
130 p_send_945_flag IN VARCHAR2 DEFAULT NULL,
131 p_autocreate_trip_flag IN varchar2 default 'Y',
132 x_return_status OUT NOCOPY VARCHAR2,
133 --tkt
134 p_caller IN VARCHAR2 DEFAULT NULL) ;
135
136 --
137 -- Procedure: Change_Status
138 -- Parameters: p_del_rows - Delivery_ids of deliveries to be planned
139 -- p_action - action to be performed
140 -- p_actual_date - date the action is performed
141 -- NOTE: this is used to populate the initial and ultimate dates
142 -- always pass NULL if calling directly
143 -- x_return_status - status of procedure call
144 -- Description: This procedure will Change Status of deliveries
145 -- Values for p_action are
146 -- - PACK
147 -- - REOPEN
148 -- - IN-TRANSIT
149 -- - CLOSE
150 -- NOTE: For CONFIRM use confirm_delivery procedure
151 --
152
153 PROCEDURE Change_Status
154 (p_del_rows IN wsh_util_core.id_tab_type,
155 p_action IN VARCHAR2,
156 p_actual_date IN DATE DEFAULT NULL,
157 x_return_status OUT NOCOPY VARCHAR2,
158 --tkt
159 p_caller IN VARCHAR2 DEFAULT NULL) ;
160
161
162
163 --
164 -- Procedure: Update_Leg_Sequence
165 -- Parameters: p_delivery_id - Delivery_id of delivery to be planned
166 -- x_return_status - status of procedure call
167 -- Description: This procedure will update sequence number of delivery legs
168 --
169 /* H integration - anxsharm */
170 PROCEDURE Update_Leg_Sequence
171 (p_delivery_id IN NUMBER,
172 p_update_flag IN VARCHAR2 DEFAULT 'Y',
173 x_return_status OUT NOCOPY VARCHAR2);
174
175
176 --
177 -- Procedure: Set_Load_Tender
178 -- Parameters: p_del_rows - Delivery ids to be tendered
179 -- x_return_status - status of procedure call
180 -- Description: This procedure is used to Tender/Cancel loads to carriers. It
181 -- calls the wsh_delivery_legs_actions API for delivery legs on
182 -- each delivery
183 -- p_action - 'TENDER', 'CANCEL'
184 --
185 -- COMMENTING OUT AS LOAD TENDER FUNCTIONALITY IS TEMPORARILY REMOVED
186 /*
187 PROCEDURE Set_Load_Tender
188 (p_del_rows IN wsh_util_core.id_tab_type,
189 p_action IN VARCHAR2,
190 x_return_status OUT NOCOPY VARCHAR2);
191 */
192 --
193 -- Procedure: Generate_Loading_Seq
194 -- Parameters: p_del_rows - Delivery ids
195 -- x_return_status - status of procedure call
196 -- Description: This procedure is used to generate loading sequence for deliveries
197 --
198
199 PROCEDURE Generate_Loading_Seq
200 (p_del_rows IN wsh_util_core.id_tab_type,
201 x_return_status OUT NOCOPY VARCHAR2);
202
203
204 --
205 -- Procedure: Assign_Delivery_Update
206 -- Parameters: p_delivery_id - Delivery id
207 -- p_del_params - Parameters to update the delivery with
208 -- x_return_status - status of procedure call
209 -- Description: This procedure is used to update the delivery with grouping
210 -- attribute values from lines, while assigning lines to delivery
211 --
212
213 PROCEDURE Assign_Delivery_Update
214 (p_delivery_id IN NUMBER,
215 p_del_params IN wsh_delivery_autocreate.grp_attr_rec_type,
216 x_return_status OUT NOCOPY VARCHAR2);
217
218
219
220 -- **************************************************************************
221 -- PATCHSET H CHANGES FOR FTE INTEGRATION with CARRIER SELECTION
222 --
223 -- [AAB]
224 -- [03/02/2002]
225 --
226
227 TYPE TableNumbers is TABLE of NUMBER INDEX BY BINARY_INTEGER; -- table number type
228 TYPE TableVarchar30 is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER; -- table varchar(30) type
229 TYPE TableVarchar3 is TABLE of VARCHAR2(3) INDEX BY BINARY_INTEGER; -- table varchar(3) type
230 TYPE TableDate is TABLE of DATE INDEX BY BINARY_INTEGER;
231
232
233 PROCEDURE PROCESS_CARRIER_SELECTION(p_delivery_id_tab IN OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
234 p_batch_id IN NUMBER,
235 p_form_flag IN VARCHAR2,
239 x_return_message OUT NOCOPY VARCHAR2,
236 p_organization_id IN NUMBER DEFAULT NULL,
237 -- csun deliveryMerge
238 p_caller IN VARCHAR2 DEFAULT NULL,
240 x_return_status OUT NOCOPY VARCHAR2);
241
242 -- deliveryMerge
243 G_NO_APPENDING VARCHAR2(1) := 'N';
244 G_START_OF_STAGING VARCHAR2(1) := 'S';
245 G_END_OF_STAGING VARCHAR2(1) := 'E';
246 G_START_OF_PACKING VARCHAR2(1) := 'A';
247 G_START_OF_SHIPPING VARCHAR2(1) := 'W';
248
249 PROCEDURE Adjust_Planned_Flag(
250 p_delivery_ids IN wsh_util_core.id_tab_type,
251 p_caller IN VARCHAR2,
252 p_force_appending_limit IN VARCHAR2,
253 p_call_lcss IN VARCHAR2 DEFAULT 'N',
254 p_event IN VARCHAR2 DEFAULT NULL,
255 x_return_status OUT NOCOPY VARCHAR2,
256 p_called_for_sc IN BOOLEAN default false);
257
258
259 -- J-IB-NPARIKH-{
260 -- ----------------------------------------------------------------------
261 -- Procedure: update_freight_terms
262 -- Parameters: p_delivery_id in number
263 -- p_action_code in varchar2
264 -- 'ASSIGN' : Assign lines to delivery
265 -- 'UNASSIGN' : Unassign lines from delivery
266 -- p_line_freight_terms_code in varchar2
267 -- NULL : Caller did not pass value
268 -- 'NULL' : Lines assigned/unassigned have mixed/null freight terms
269 -- Other value: All lines assigned/unassigned have same freight term , equal to this value
270 -- Description: This procedure can be called after assign/unassign lines from delivery.
271 -- ----------------------------------------------------------------------
272 PROCEDURE update_freight_terms
273 (
274 p_delivery_id IN NUMBER,
275 p_action_code IN VARCHAR2 DEFAULT 'UNASSIGN',
276 p_line_freight_terms_Code IN VARCHAR2 DEFAULT NULL,
277 x_freight_terms_Code OUT NOCOPY VARCHAR2,
278 x_return_status OUT NOCOPY VARCHAR2
279 ) ;
280 --
281 --
282 PROCEDURE setClose
283 (
284 p_in_rec IN WSH_DELIVERY_VALIDATIONS.ChgStatus_in_rec_type,
285 x_return_status OUT NOCOPY VARCHAR2
286 ) ;
287 PROCEDURE setInTransit
288 (
289 p_in_rec IN WSH_DELIVERY_VALIDATIONS.ChgStatus_in_rec_type,
290 x_return_status OUT NOCOPY VARCHAR2
291 ) ;
292
293
294 PROCEDURE update_ship_from_location
295 (
296 p_delivery_id IN NUMBER,
297 p_location_id IN NUMBER,
298 x_return_status OUT NOCOPY VARCHAR2
299 ) ;
300
301
302 -- J-IB-NPARIKH-}
303
304 -- J-IB-HEALI-{
305 PROCEDURE Process_Leg_Sequence
306 ( p_delivery_id IN NUMBER,
307 p_update_del_flag IN VARCHAR2,
308 p_update_leg_flag IN VARCHAR2,
309 x_leg_complete OUT NOCOPY boolean,
310 x_return_status OUT NOCOPY VARCHAR2);
311 -- J-IB-HEALI-}
312
313 /**________________________________________________________________________
314 --
315 -- Name:
316 -- Assign_Del_to_Consol_Del
317 --
318 -- Purpose:
319 -- This API assigns a deliveries to a parent (consolidation)
320 -- delivery. If the caller is FTE consolidation SRS, we
321 -- assume that the child deliveries have already been validated
322 -- as eligible to be assigned to the parent delivery.
323 -- Parameters:
324 -- p_del_tab: Table of deliveries that need to be assigned
325 -- p_parent_del: Parent delivery id that will be assigne to
326 -- p_caller: Calling entity/action
327 -- x_return_status: status
328 **/
329
330 Procedure Assign_Del_to_Consol_Del(
331 p_del_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
332 p_parent_del_id IN NUMBER,
333 p_caller IN VARCHAR2,
334 x_return_status OUT NOCOPY VARCHAR2);
335
336
337 --
338 -- Name:
339 -- Unassign_Dels_from_Consol_Del
340 --
341 -- Purpose:
342 -- This API unassigns deliveries from a parent (consolidation)
343 -- delivery. If the parent delivery becomes empty we delete the
344 -- parent delivery. Currently this will be called with
345 -- assumption that all and only all deliveries in the parent
346 -- delivery will be unassigned all at the same time.
347 --
348 -- Parameters:
349 -- p_del_tab: Table of deliveries that need to be unassigned
350 -- p_parent_del_ids: Parent deliveries that will be unassigned from
351 -- and eventually deleted.
352 -- p_caller: Calling entity/action
353 -- x_return_status: status
354
355
356 Procedure Unassign_Dels_from_Consol_Del(
357 p_parent_del IN NUMBER,
358 p_caller IN VARCHAR2,
359 p_del_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
360 x_return_status OUT NOCOPY VARCHAR2);
361
362
363 --OTM R12, function to check if delivery is empty or not
364 FUNCTION IS_DELIVERY_EMPTY (p_delivery_id IN NUMBER) RETURN VARCHAR2;
365 --
366
367 END WSH_NEW_DELIVERY_ACTIONS;