1 PACKAGE WSH_DELIVERY_AUTOCREATE as
2 /* $Header: WSHDEAUS.pls 120.3.12010000.1 2008/07/29 05:59:55 appldev 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,
115 p_init_flag IN VARCHAR2,
116 p_pick_release_flag IN VARCHAR2,
117 p_container_flag IN VARCHAR2 := 'N',
118 p_check_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
218 );
219
220 TYPE grp_attr_tab_type IS TABLE OF GRP_ATTR_REC_TYPE INDEX BY BINARY_INTEGER;
221
222 -----------------------------------------------------------------------------
223 --
224 -- Procedure: Get_Group_By_Attr
225 -- Parameters: p_organization_id, x_group_by_flags, x_return_status
226 -- Description: Gets group by attributes for the delivery organization
227 -- and stores this in a temporary table for future comparison
228 -- p_delivery_id - Delivery ID
229 -- x_group_by_flags - group by attributes record
230 --
231 -----------------------------------------------------------------------------
232
233 PROCEDURE get_group_by_attr (
234 p_organization_id IN NUMBER,
235 x_group_by_flags OUT NOCOPY group_by_flags_rec_type,
236 x_return_status OUT NOCOPY VARCHAR2,
237 p_group_by_header_flag IN VARCHAR2 DEFAULT 'N');
238
239 -- Create_Hash: This API will create a hash_string and generate corresponding hash value based on the
240 -- grouping attributes of the input records. It will not append the ship method
241 -- code or its components to the hash string.
242 -- p_grouping_attributes: record of attributes or entity that needs hash generated.
243
244 Procedure Create_Hash(p_grouping_attributes IN OUT NOCOPY grp_attr_tab_type,
245 p_group_by_header IN varchar2,
246 p_action_code IN varchar2,
247 x_return_status OUT NOCOPY VARCHAR2);
248
249
250
251 Procedure Create_Update_Hash(p_delivery_rec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
252 x_return_status out NOCOPY varchar2);
253
254
255 type action_rec_type is record (action varchar2(30),
256 caller varchar2(30),
257 group_by_header_flag varchar2(1),
258 group_by_delivery_flag varchar2(1),
259 output_format_type varchar2(30),
260 output_entity_type varchar2(30),
261 check_single_grp varchar2(1));
262
263 type out_rec_type is record (query_string varchar2(4000),
264 single_group varchar2(1),
265 bind_hash_value number,
266 bind_hash_string varchar2(1000),
267 bind_batch_id number,
268 bind_header_id number,
269 bind_carrier_id number,
270 bind_mode_of_transport varchar2(30),
271 bind_service_level varchar2(30),
272 bind_ship_method_code varchar2(30)); --bug6074966
273
274
275
276
277 -- Find_Matching_Groups: This API will find entities (deliveries/containers) that
278 -- match the grouping criteria of the input table of entities.
279 -- p_attr_tab: Table of entities or record of grouping criteria that need to be matched.
280 -- p_action_rec: Record of specific actions and their corresponding parameters.
281 -- check_single_grp_only: ('Y', 'N') will check only of the records can be
282 -- grouped together.
283 -- output_entity_type: ('DLVY', 'CONT') the entity type that the input records
284 -- need to be matched with.
285 -- output_format_type: Format of the output.
286 -- 'ID_TAB': table of id's of the matched entities
287 -- 'TEMP_TAB': The output will be inserted into wsh_temp (wsh_temp
288 -- needs to be cleared after this API has been used).
289 -- 'SQL_STRING': Will return a SQL query to find the matching entities
290 -- as a string and values of the variables that will
291 -- need to be bound to the string.
292 -- p_target_rec: Entity or grouping attributes that need to be matched with (if necessary)
293 -- x_matched_entities: table of ids of the matched entities
294 -- x_out_rec: Record of output values based on the actions and output format.
295 -- query_string: String to query for matching entities. The following
296 -- will have to be bound to the string before executing the query.
297 -- bind_hash_value
298 -- bind_hash_string
299 -- bind_batch_id
300 -- bind_carrier_id
301 -- bind_mode_of_transport
302 -- bind_service_level
303 -- bind_ship_method_code
304 -- x_return_status: 'S', 'E', 'U'.
305
306
307 PROCEDURE Find_Matching_Groups(p_attr_tab IN OUT NOCOPY grp_attr_tab_type,
308 p_action_rec IN action_rec_type,
309 p_target_rec IN grp_attr_rec_type,
310 p_group_tab IN OUT NOCOPY grp_attr_tab_type,
311 x_matched_entities OUT NOCOPY wsh_util_core.id_tab_type,
312 x_out_rec out NOCOPY out_rec_type,
313 x_return_status out NOCOPY varchar2);
314
315 PROCEDURE Reset_WSH_TMP;
316
317 /**________________________________________________________________________
318 --
319 -- Name:
320 -- Autocreate_Consol_Del
321 --
322 -- Purpose:
323 -- This API takes in a table of child deliveries and delivery attributes,
324 -- and creates a consolidation delivery. It currently assumes that
325 -- all the child deliveries can be grouped together and assigned to
326 -- a single parent delivery when called by the WSH CONSOL SRS.
327 -- Parameters:
328 -- p_del_attributes_tab: Table of deliveries and attributes that need to
329 -- have parent delivery autocreated.
330 -- p_caller: Calling entity/action
331 -- x_parent_del_tab: Delivery ids of the newly created parent deliveries.
332 -- x_return_status: status.
333 **/
334
335 PROCEDURE Autocreate_Consol_Delivery(
336 p_del_attributes_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
337 p_caller IN VARCHAR2,
338 p_trip_prefix IN VARCHAR2 DEFAULT NULL,
339 x_parent_del_id OUT NOCOPY NUMBER,
340 x_parent_trip_id OUT NOCOPY NUMBER,
341 x_return_status OUT NOCOPY VARCHAR2);
342
343
344
345 END WSH_DELIVERY_AUTOCREATE;
346
[Home] [Help]