1 PACKAGE WSH_DELIVERY_AUTOCREATE AUTHID CURRENT_USER as
2 /* $Header: WSHDEAUS.pls 120.5 2010/08/06 16:19:21 anvarshn ship $ */
3 --<TPA_PUBLIC_NAME=WSH_TPA_DELIVERY_PKG>
4 --<TPA_PUBLIC_FILE_NAME=WSHTPDE>
5
6 --
7 -- Package type definitions
8 --
9
10
11
12
13 --
14 -- Type: Group_by_flags_rec_type
15 -- Definition: Record of group by attribute flags
16 -- Use: To store group by information for delivery details
17 --
18
19 TYPE group_by_flags_rec_type IS RECORD (
20 organization_id NUMBER,
21 customer VARCHAR2(1) := 'Y',
22 intmed VARCHAR2(1) := 'Y',
23 fob VARCHAR2(1) := 'Y',
24 freight_terms VARCHAR2(1) := 'Y',
25 ship_method VARCHAR2(1) := 'Y',
26 carrier VARCHAR2(1) := 'Y',
27 header VARCHAR2(1) := 'N',
28 deliver_to VARCHAR2(1) := 'N',
29 delivery_id VARCHAR2(1) := 'N'
30 );
31
32
33
34 --
35 -- Type: Group_by_flags_tab_type
36 -- Definition: Table of group_by_flags_rec_type
37 -- Use: To store group by information for delivery details per
38 -- organization. This is used to obviate querying up this
39 -- information for each delivery detail, thereby making the
40 -- autocreate delivery process faster for large detail processing.
41 --
42
43 TYPE group_by_flags_tab_type IS TABLE OF group_by_flags_rec_type
44 INDEX BY BINARY_INTEGER;
45
46 group_by_info_tab group_by_flags_tab_type;
47 group_by_info group_by_flags_rec_type;
48
49 -- Variable: Detail_Num
50 -- Use: To commit changes once a maximum commit number has been
51 -- reached (useful when called from Pick Release)
52
53 detail_num NUMBER := 0;
54
55 --
56 -- Function: Check_Sch_Date_Match
57 -- Parameters: p_delivery_id, p_del_date, p_detail_date
58 -- Description: Checks if scheduled date on line matches initial pickup date on delivery
59 -- FOR THE PRESENT, FUNCTION SIMPLY RETURNS TRUE
60 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
61 --
62
63 FUNCTION Check_Sch_Date_Match ( p_delivery_id IN NUMBER,
64 p_del_date IN DATE,
65 p_detail_date IN DATE) RETURN BOOLEAN;
66 --<TPA_PUBLIC_NAME>
67 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
68
69 --
70 -- Function: Check_Req_Date_Match
71 -- Parameters: p_delivery_id, p_del_date, p_detail_date
72 -- Description: Checks if requested date on line matches ultimate dropoff date on delivery
73 -- FOR THE PRESENT, FUNCTION SIMPLY RETURNS TRUE
74 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
75 --
76
77 FUNCTION Check_Req_Date_Match ( p_delivery_id IN NUMBER,
78 p_del_date IN DATE,
79 p_detail_date IN DATE) RETURN BOOLEAN;
80 --<TPA_PUBLIC_NAME>
81 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
82
83 --
84 -- Procedure: Autocreate_deliveries
85 -- Parameters: p_line_rows, p_line_info_rows, p_init_flag,
86 -- p_use_header_flag, p_max_detail_commit, p_del_rows
87 -- Description: Used to automatically create deliveries
88 -- p_line_rows - Table of delivery detail ids
89 -- p_init_flag - 'Y' initializes the table of deliveries
90 -- p_pick_release_flag - 'Y' means use header_id for grouping
91 -- p_container_flag - 'Y' means call Autopack routine
92 -- p_check_flag - 'Y' means delivery details will be
93 -- grouped without creating deliveries
94 -- p_caller - R12 introduction for Routing Guide integration
95 -- p_generate_carton_group_id - 'Y' means api called for generate
96 -- carton group id only
97 -- p_max_detail_commit - Commits data after delivery detail
98 -- count reaches this value
99 -- p_firm_deliveries - 'Y' : plan the delivery at creation, when
100 -- doing autopack or auto ship confirm during
101 -- pick release , to prevent appending
102 -- 'N' : do not plan the delivery at creation
103 -- p_get_autocreate_del_criteria - 'Y' get the autocreate_del_criteria from
104 -- shipping parameters for grouping attributes
105 -- - 'N' do not check autocreate_del_criteria of
106 -- shipping parameters
107 -- p_del_rows - Created delivery ids
108 -- p_grouping_rows - returns group ids for each detail,
109 -- when p_check_flag is set to 'Y'
110 -- x_return_status - Status of execution
111
112
113 PROCEDURE autocreate_deliveries(
114 p_line_rows IN wsh_util_core.id_tab_type,
118 p_check_flag IN VARCHAR2 := 'N',
115 p_init_flag IN VARCHAR2,
116 p_pick_release_flag IN VARCHAR2,
117 p_container_flag IN VARCHAR2 := 'N',
119 p_caller IN VARCHAR2 DEFAULT NULL,
120 p_generate_carton_group_id IN VARCHAR2 := 'N',
121 p_max_detail_commit IN NUMBER := 1000,
122 x_del_rows OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
123 x_grouping_rows OUT NOCOPY wsh_util_core.id_tab_type,
124 x_return_status OUT NOCOPY VARCHAR2
125 );
126
127 -- bug 1668578
128 --
129 -- Procedure: Autocreate_del_across_orgs
130 -- Parameters: p_line_rows, p_line_info_rows, p_init_flag,
131 -- p_use_header_flag, p_max_detail_commit, p_del_rows
132 -- Description: Used to automatically create deliveries
133 -- p_line_rows - Table of delivery detail ids
134 -- p_org_rows - a table of organization_ids. If this
135 -- - table is not available to pass
136 -- - then pass a dummy value in. the table
137 -- - will get regenerated when calling
138 -- - WSH_DELIVERY_AUTOCREATE.autocreate_del_across_orgs
139 -- p_container_flag - 'Y' means call Autopack routine
140 -- p_check_flag - 'Y' means delivery details will be
141 -- grouped without creating deliveries
142 -- p_caller - R12 introduction for Routing Guide integration
143 -- p_max_detail_commit - Commits data after delivery detail
144 -- count reaches this value
145 -- p_del_rows - Created delivery ids
146 -- p_grouping_rows - returns group ids for each detail,
147 -- when p_check_flag is set to 'Y'
148 -- x_return_status - Status of execution
149
150 PROCEDURE autocreate_del_across_orgs(
151 p_line_rows IN wsh_util_core.id_tab_type,
152 p_org_rows IN wsh_util_core.id_tab_type,
153 p_container_flag IN VARCHAR2 := 'N',
154 p_check_flag IN VARCHAR2 := 'N',
155 p_caller IN VARCHAR2 DEFAULT NULL,
156 p_max_detail_commit IN NUMBER := 1000,
157 p_group_by_header_flag IN VARCHAR2 DEFAULT NULL,
158 x_del_rows OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
159 x_grouping_rows OUT NOCOPY wsh_util_core.id_tab_type,
160 x_return_status OUT NOCOPY VARCHAR2
161 );
162 -- end bug 1668578
163
164 PROCEDURE delete_empty_deliveries(
165 p_batch_id IN NUMBER,
166 x_return_status OUT NOCOPY VARCHAR2 );
167
168 --------------------------------------------------------------------------
169 --
170 -- Procedure: unassign_empty_containers
171 -- Parameters: p_delivery_id
172 -- x_return_status
173 --
174 -- Description: Used to unassign empty containers from delivery after Pick Release
175 -- p_delivery_ids - table index by delivery ids
176 -- x_return_status - Status of execution
177 --------------------------------------------------------------------------
178 PROCEDURE unassign_empty_containers(
179 p_delivery_ids IN WSH_PICK_LIST.unassign_delivery_id_type,
180 x_return_status OUT NOCOPY VARCHAR2 );
181
182
183 TYPE GRP_ATTR_REC_TYPE IS RECORD (
184 batch_id number,
185 group_id number,
186 entity_id number,
187 entity_type varchar2(30),
188 status_code varchar2(30),
189 planned_flag varchar2(1),
190 ship_to_location_id wsh_delivery_details.ship_to_location_id%type,
191 ship_from_location_id wsh_delivery_details.ship_from_location_id%type,
192 customer_id wsh_delivery_details.customer_id%type,
193 intmed_ship_to_location_id wsh_delivery_details.intmed_ship_to_location_id%type,
194 fob_code wsh_delivery_details.fob_code%type,
195 freight_terms_code wsh_delivery_details.freight_terms_code%type,
196 ship_method_code wsh_delivery_details.ship_method_code%type,
197 carrier_id wsh_delivery_details.carrier_id%type,
198 source_header_id wsh_delivery_details.source_header_id%type,
199 deliver_to_location_id wsh_delivery_details.deliver_to_location_id%type,
200 organization_id wsh_delivery_details.organization_id%type,
201 date_scheduled wsh_delivery_details.date_scheduled%type,
202 date_requested wsh_delivery_details.date_requested%type,
203 delivery_id wsh_new_deliveries.delivery_id%type,
204 ignore_for_planning wsh_delivery_details.ignore_for_planning%type DEFAULT 'N',--J TP Release
205 line_direction wsh_delivery_details.line_direction%TYPE, -- J-IB-NPARIKH
206 shipping_control wsh_delivery_details.shipping_control%TYPE, -- J-IB-NPARIKH
207 vendor_id wsh_delivery_details.vendor_id%TYPE, -- J-IB-NPARIKH
208 party_id wsh_delivery_details.party_id%TYPE, -- J-IB-NPARIKH
209 mode_of_transport wsh_delivery_details.mode_of_transport%TYPE,
210 service_level wsh_delivery_details.service_level%TYPE,
211 lpn_id wsh_delivery_details.lpn_id%TYPE,
212 inventory_item_id wsh_delivery_details.inventory_item_id%TYPE,
213 source_code wsh_delivery_details.source_code%TYPE,
214 container_flag wsh_delivery_details.container_flag%TYPE,
215 l1_hash_string varchar2(1000),
216 l1_hash_value number,
217 is_xdocked_flag varchar2(1) DEFAULT 'N',--X-dock
221
218 client_id number, -- LSP PROJECT
219 consignee_flag wsh_delivery_details.consignee_flag%TYPE --RTV changes
220 );
222 TYPE grp_attr_tab_type IS TABLE OF GRP_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
223
224 -----------------------------------------------------------------------------
225 --
226 -- Procedure: Get_Group_By_Attr
227 -- Parameters: p_organization_id, x_group_by_flags, x_return_status
228 -- Description: Gets group by attributes for the delivery organization
229 -- and stores this in a temporary table for future comparison
230 -- p_delivery_id - Delivery ID
231 -- x_group_by_flags - group by attributes record
232 --
233 -- LSP PROJECT : Added client Id parameter : Get the group by attributes from client
234 -- if cleint_id is not null. If client_id is null then grouping paramters
235 -- for the organization
236
237 -----------------------------------------------------------------------------
238
239 PROCEDURE get_group_by_attr (
240 p_organization_id IN NUMBER,
241 p_client_id IN NUMBER DEFAULT NULL,
242 x_group_by_flags OUT NOCOPY group_by_flags_rec_type,
243 x_return_status OUT NOCOPY VARCHAR2,
244 p_group_by_header_flag IN VARCHAR2 DEFAULT 'N');
245
246 -- Create_Hash: This API will create a hash_string and generate corresponding hash value based on the
247 -- grouping attributes of the input records. It will not append the ship method
248 -- code or its components to the hash string.
249 -- p_grouping_attributes: record of attributes or entity that needs hash generated.
250
251 Procedure Create_Hash(p_grouping_attributes IN OUT NOCOPY grp_attr_tab_type,
252 p_group_by_header IN varchar2,
253 p_action_code IN varchar2,
254 x_return_status OUT NOCOPY VARCHAR2);
255
256
257
258 Procedure Create_Update_Hash(p_delivery_rec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
259 x_return_status out NOCOPY varchar2);
260
261
262 type action_rec_type is record (action varchar2(30),
263 caller varchar2(30),
264 group_by_header_flag varchar2(1),
265 group_by_delivery_flag varchar2(1),
266 output_format_type varchar2(30),
267 output_entity_type varchar2(30),
268 check_single_grp varchar2(1));
269
270 type out_rec_type is record (query_string varchar2(4000),
271 single_group varchar2(1),
272 bind_hash_value number,
273 bind_hash_string varchar2(1000),
274 bind_batch_id number,
275 bind_header_id number,
276 bind_carrier_id number,
277 bind_mode_of_transport varchar2(30),
278 bind_service_level varchar2(30),
279 bind_ship_method_code varchar2(30), --bug6074966
280 bind_client_id number); -- LSP PROJECT
281
282
283
284
285 -- Find_Matching_Groups: This API will find entities (deliveries/containers) that
286 -- match the grouping criteria of the input table of entities.
287 -- p_attr_tab: Table of entities or record of grouping criteria that need to be matched.
288 -- p_action_rec: Record of specific actions and their corresponding parameters.
289 -- check_single_grp_only: ('Y', 'N') will check only of the records can be
290 -- grouped together.
291 -- output_entity_type: ('DLVY', 'CONT') the entity type that the input records
292 -- need to be matched with.
293 -- output_format_type: Format of the output.
294 -- 'ID_TAB': table of id's of the matched entities
295 -- 'TEMP_TAB': The output will be inserted into wsh_temp (wsh_temp
296 -- needs to be cleared after this API has been used).
297 -- 'SQL_STRING': Will return a SQL query to find the matching entities
298 -- as a string and values of the variables that will
299 -- need to be bound to the string.
300 -- p_target_rec: Entity or grouping attributes that need to be matched with (if necessary)
301 -- x_matched_entities: table of ids of the matched entities
302 -- x_out_rec: Record of output values based on the actions and output format.
303 -- query_string: String to query for matching entities. The following
304 -- will have to be bound to the string before executing the query.
305 -- bind_hash_value
306 -- bind_hash_string
307 -- bind_batch_id
308 -- bind_carrier_id
309 -- bind_mode_of_transport
310 -- bind_service_level
311 -- bind_ship_method_code
312 -- bind_client_id -- LSP PROJECT
313 -- x_return_status: 'S', 'E', 'U'.
314
315
316 PROCEDURE Find_Matching_Groups(p_attr_tab IN OUT NOCOPY grp_attr_tab_type,
317 p_action_rec IN action_rec_type,
318 p_target_rec IN grp_attr_rec_type,
319 p_group_tab IN OUT NOCOPY grp_attr_tab_type,
320 x_matched_entities OUT NOCOPY wsh_util_core.id_tab_type,
321 x_out_rec out NOCOPY out_rec_type,
322 x_return_status out NOCOPY varchar2);
323
324 PROCEDURE Reset_WSH_TMP;
325
326 /**________________________________________________________________________
327 --
328 -- Name:
329 -- Autocreate_Consol_Del
330 --
331 -- Purpose:
332 -- This API takes in a table of child deliveries and delivery attributes,
333 -- and creates a consolidation delivery. It currently assumes that
334 -- all the child deliveries can be grouped together and assigned to
335 -- a single parent delivery when called by the WSH CONSOL SRS.
336 -- Parameters:
337 -- p_del_attributes_tab: Table of deliveries and attributes that need to
338 -- have parent delivery autocreated.
339 -- p_caller: Calling entity/action
340 -- x_parent_del_tab: Delivery ids of the newly created parent deliveries.
341 -- x_return_status: status.
342 **/
343
344 PROCEDURE Autocreate_Consol_Delivery(
345 p_del_attributes_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
346 p_caller IN VARCHAR2,
347 p_trip_prefix IN VARCHAR2 DEFAULT NULL,
348 x_parent_del_id OUT NOCOPY NUMBER,
349 x_parent_trip_id OUT NOCOPY NUMBER,
350 x_return_status OUT NOCOPY VARCHAR2);
351
352
353
354 END WSH_DELIVERY_AUTOCREATE;
355