1 Package WMS_SEARCH_ORDER_GLOBALS_PVT AUTHID CURRENT_USER AS
2 /* $Header: WMSSOGBS.pls 120.1 2005/07/18 05:21:47 ajohnson noship $ */
3
4 -- File : WMSSOGBS.pls
5 -- Content : WMS_SEARCH_ORDER_GLOBALS_PVT package body
6 -- Description : This API is created to store Rules Engine Process flow
7 -- Variabls. This API Gobal Variable will be updated by
8 --- WMS_RULES_ENGINE_PVT and to be refrenced by WMSRLSIM.fmb
9
10 -- Notes :
11 -- List of Pl/SQL Tables,Functions and Procedures
12 --
13 -- TYPE pre_suggestions_record IS RECORD
14 -- TYPE pre_suggestions_record_tbl IS TABLE
15 -- FUNCTION get_object_type ( engine_type IN VARCHAR2) RETURN VARCHAR2;
16 -- FUNCTION get_object_name ( engine_type IN VARCHAR2, org_id number) RETURN VARCHAR2;
17 -- FUNCTION get_strategy_name ( engine_type IN VARCHAR2, org_id IN NUMBER) RETURN VARCHAR2;
18 -- FUNCTION get_rule_name ( engine_type IN VARCHAR2, org_id IN NUMBER) RETURN VARCHAR2;
19 -- FUNCTION get_costgroup_name ( engine_type IN VARCHAR2 , org_id IN NUMBER ) RETURN VARCHAR2;
20 -- FUNCTION get_costgroup_desc( engine_type IN VARCHAR2, org_id IN NUMBER ) RETURN VARCHAR2;
21 -- FUNCTION get_costgroup_desc( engine_type IN VARCHAR2, org_id IN NUMBER ) RETURN VARCHAR2;
22 -- FUNCTION IS_Object_selected(..) return ;
23 -- FUNCTION IS_BO_Object_selected(..) return ;
24 -- Procedure init_global_variables;
25 -- procedure simulate_rules( mo_line_id ..);
26 -- procedure insert_trace_header(..);
27 -- procedure insert_trace_lines(..)
28 -- Procedure insert_headers_row;
29 -- Procedure insert_lines_row;
30 -- procedure delete_trace_rows;
31 -- procedure insert_txn_trace_rows(p_txn_header_id, p_insert_lot_flag, p_insert_serial_flag);
32 -- Procedure set_global_variables (p_move_order_line_id );
33 -- Created By : Grao 06/21/01 Created
34 -- --------- ------ ------------------------------------------
35
36 ---- Trace Global Variables ------------
37
38 G_PICK_HEADER_ID NUMBER := 0 ;
39 G_PUTAWAY_HEADER_ID NUMBER := 0 ;
40
41
42
43
44 --- Pick Search order Global Variables
45
46 G_PICK_BUSINESS_OBJECT_ID NUMBER := NULL;
47 G_PICK_OBJECT VARCHAR2(50);
48 G_PICK_PK1_VALUE VARCHAR2(150);
49 G_PICK_PK2_VALUE VARCHAR2(150);
50 G_PICK_PK3_VALUE VARCHAR2(150);
51 G_PICK_PK4_VALUE VARCHAR2(150);
52 G_PICK_PK5_VALUE VARCHAR2(150);
53 G_PICK_STRATEGY_ID NUMBER := NULL;
54 G_PICK_RULE_ID NUMBER := NULL;
55
56 G_PICK_SEQ_NUM NUMBER := NULL;
57
58
59 --- Putaway Search order Global Variables
60
61 G_PUTAWAY_BUSINESS_OBJECT_ID NUMBER := NULL;
62 G_PUTAWAY_OBJECT VARCHAR2(50);
63 G_PUTAWAY_PK1_VALUE VARCHAR2(150);
64 G_PUTAWAY_PK2_VALUE VARCHAR2(150);
65 G_PUTAWAY_PK3_VALUE VARCHAR2(150);
66 G_PUTAWAY_PK4_VALUE VARCHAR2(150);
67 G_PUTAWAY_PK5_VALUE VARCHAR2(150);
68 G_PUTAWAY_STRATEGY_ID NUMBER := NULL;
69 G_PUTAWAY_RULE_ID NUMBER := NULL;
70
71 G_PUTAWAY_SEQ_NUM NUMBER := NULL;
72
73
74 --- Cost Group Search order Global Variables
75
76 G_COSTGROUP_BUSINESS_OBJECT_ID NUMBER := NULL;
77 G_COSTGROUP_OBJECT VARCHAR2(50);
78 G_COSTGROUP_PK1_VALUE VARCHAR2(150);
79 G_COSTGROUP_PK2_VALUE VARCHAR2(150);
80 G_COSTGROUP_PK3_VALUE VARCHAR2(150);
81 G_COSTGROUP_PK4_VALUE VARCHAR2(150);
82 G_COSTGROUP_PK5_VALUE VARCHAR2(150);
83 G_COSTGROUP_STRATEGY_ID NUMBER := NULL;
84 G_COSTGROUP_RULE_ID NUMBER := NULL;
85
86 G_COSTGROUP_SEQ_NUM NUMBER := NULL;
87
88
89 G_COSTGROUP_ID NUMBER := NULL;
90 -------------------------- To identify if Trace records or created for Simulation mode or Rule Execution Mode
91 ---- Default Non Simulation mode 'N' and for Simulation mode 'Y'
92 G_SIMULATION_MODE VARCHAR2(1) DEFAULT 'N' ;
93
94 ---------------------------------------------------------------------
95 -- Record type definition for a rule trace record in cache
96 -- Values for
97 --
98 -- Other Value used for these Flags are 'P' Partial Success and 'V' Default Value
99
100 -- same_subinv_loc_flag Default NULL, Success - 'Y' Failure - 'N'
101 -- ATT_qty_flag Default NULL, Success - 'Y' Failure - 'N'
102 -- consist_string_flag Default NULL, Success - 'Y' Failure - 'N'
103 -- order_string_flag Default NULL, Success - 'Y' Failure - 'N'
104 -- Material_status_flag Default NULL, Success - 'Y' Failure - 'N'
105 -- Pick_UOM_flag Default NULL, Success - 'Y' Failure - 'N'
106 -- partial_pick_flag Default NULL, Success - 'Y' Failure - 'N'
107 -- Serial_number_used_flag Default NULL, Success - 'Y' Failure - 'N'
108 -- CG_comingle_flag Default NULL, Success - 'Y' Failure - 'N'
109
110 TYPE pre_suggestions_record IS RECORD
111 (
112 revision wms_transactions_temp.revision%TYPE,
113 quantity wms_transactions_temp.transaction_quantity%TYPE,
114 lot_number wms_transactions_temp.lot_number%TYPE,
115 lot_expiration_date wms_transactions_temp.lot_expiration_date%TYPE,
116 serial_number wms_transactions_temp.serial_number%TYPE,
117 subinventory_code wms_transactions_temp.from_subinventory_code%TYPE,
118 locator_id wms_transactions_temp.from_locator_id%TYPE,
119 lpn_id wms_transactions_temp.lpn_id%TYPE,
120 cost_group_id wms_transactions_temp.to_cost_group_id%TYPE,
121 uom_code VARCHAR2(3),
122 remaining_qty wms_transactions_temp.transaction_quantity%TYPE,
123 ATT_qty wms_transactions_temp.transaction_quantity%TYPE,
124 suggested_qty wms_transactions_temp.transaction_quantity%TYPE,
125 -- LG convergence added
126 secondary_qty wms_transactions_temp.secondary_quantity%TYPE,
127 secondary_remaining_qty wms_transactions_temp.secondary_quantity%TYPE,
128 secondary_att_qty wms_transactions_temp.secondary_quantity%TYPE,
129 secondary_suggested_qty wms_transactions_temp.secondary_quantity%TYPE,
130 secondary_uom_code VARCHAR2(3),
131 grade_code VARCHAR2(150),
132 -- End of LG convergence
133 same_subinv_loc_flag VARCHAR2(1),
134 ATT_qty_flag VARCHAR2(1),
135 consist_string_flag VARCHAR2(1),
136 order_string_flag VARCHAR2(1),
137 Material_status_flag VARCHAR2(1),
138 Pick_UOM_flag VARCHAR2(1),
139 partial_pick_flag VARCHAR2(1),
140 Serial_number_used_flag VARCHAR2(1),
141 entire_lpn_flag VARCHAR2(1),
142 CG_comingle_flag VARCHAR2(1),
143 comments VARCHAR2(2000)
144 );
145
146
147 --------------------------------------------------------------------
148 -- Definition of table types for the caches
149 TYPE pre_suggestions_record_tbl IS TABLE OF pre_suggestions_record
150 INDEX BY BINARY_INTEGER;
151
152 -- LG convergence
153 TYPE available_inventory_record IS RECORD
154 (
155 revision VARCHAR2(30)
156 ,lot_number VARCHAR2(80)
157 ,lot_expiration_date date
158 ,subinventory_code varchar2(30)
159 ,locator_id NUMBER
160 ,cost_group_id NUMBER
161 ,transaction_uom varchar2(5)
162 ,lpn_id NUMBER
163 ,serial_number VARCHAR2(30)
164 ,onhand_qty NUMBER
165 ,secondary_onhand_qty NUMBER
166 ,grade_code VARCHAR2(150)
167 ,consist_string VARCHAR2(200)
168 ,order_by_string VARCHAR2(200)
169 );
170
171 -- Definition of table types for the caches
172 TYPE available_inventory_tbl IS TABLE OF available_inventory_record
173 INDEX BY BINARY_INTEGER;
174
175 g_available_inv_tbl WMS_SEARCH_ORDER_GLOBALS_PVT.available_inventory_tbl;
176 -- end of LG convergence
177
178 -----------------------------------------
179 --- Function to get the business_object_type name based on the G_PICK_BUSINESS_OBJECT_ID /
180 ---- G_PUTAWAY_BUSINESS_OBJECT_ID/G_COSTGROUP_BUSINESS_OBJECT_ID
181 --- G_PICK_BUSINESS_OBJECT_ID / G_PUTAWAY_BUSINESS_OBJECT_IDis /
182 ---- G_COSTGROUP_BUSINESS_OBJECT_ID get updated by Rules Engine API
183
184
185 FUNCTION get_object_type
186 ( engine_type IN VARCHAR2)
187 RETURN VARCHAR2;
188 -----------------------------------------
189 --- Function to get the object_ name based on the G_PICK_BUSINESS_OBJECT_ID /
190 ---- G_PUTAWAY_BUSINESS_OBJECT_ID/G_COSTGROUP_BUSINESS_OBJECT_ID
191 --- G_PICK_BUSINESS_OBJECT_ID / G_PUTAWAY_BUSINESS_OBJECT_IDis /
192 ---- G_COSTGROUP_BUSINESS_OBJECT_ID , ORGANIZATIONS_ID, PK_VALUES get updated by Rules Engine API
193
194
195
196 FUNCTION get_object_name
197 ( engine_type IN VARCHAR2,
198 org_id number)
199 RETURN VARCHAR2;
200
201 --- Function to get the Strategy name based on the G_PICK_STRATEGY_ID /G_PUTAWAY_STRATEGY_ID
202 --- G_PICK_STRATEGY_ID /G_PUTAWAY_STRATEGY_ID is get updated by Rules Engine API
203
204 FUNCTION get_strategy_name
205 ( engine_type IN VARCHAR2
206 , org_id IN NUMBER
207 )
208 RETURN VARCHAR2;
209
210
211 -- Function to get the Rule name based on the G_COSTGROUP_RULE_ID
212 -- G_COSTGROUP_RULE_ID is get updated by Rules Engine API
213
214
215 FUNCTION get_rule_name
216 ( engine_type IN VARCHAR2
217 , org_id IN NUMBER
218 )
219 RETURN VARCHAR2;
220
221
222 -- Function to get the Cost Group name based on the G_COSTGROUP_ID
223 -- G_COSTGROUP_ID is updated by Rules Engine API
224
225
226 FUNCTION get_costgroup_name
227 ( engine_type IN VARCHAR2
228 , org_id IN NUMBER
229 )
230 RETURN VARCHAR2;
231
232 -- Function to get the Cost Group Desc based on the G_COSTGROUP_ID
233 -- G_COSTGROUP_ID is updated by Rules Engine API
234
235
236 FUNCTION get_costgroup_desc
237 ( engine_type IN VARCHAR2
238 , org_id IN NUMBER
239 )
240 RETURN VARCHAR2;
241
242 ---- Initilize All Global Variables
243 ----
244 Procedure init_global_variables;
245
246 ---- Call Rules Engine - Create Suggestions ---
247 ---------------------------------------------
248
249 procedure simulate_rules
250 ( p_mo_line_id IN VARCHAR2,
251 p_simulation_flag IN NUMBER,
252 p_simulation_id IN NUMBER,
253 x_msg_data OUT NOCOPY varchar2,
254 x_return_status OUT NOCOPY varchar2,
255 x_return_status_qty OUT NOCOPY varchar2
256 );
257
258 ----------------Insert into Trace header table ------
259
260 procedure insert_trace_header
261 (
262 p_api_version in NUMBER
263 ,p_init_msg_list in VARCHAR2 DEFAULT fnd_api.g_false
264 ,p_validation_level in NUMBER DEFAULT fnd_api.g_valid_level_full
265 ,x_return_status out NOCOPY VARCHAR2
266 ,x_msg_count out NOCOPY NUMBER
267 ,x_msg_data out NOCOPY VARCHAR2
268 ,x_header_id out NOCOPY NUMBER
269 ,p_pick_header_id in NUMBER
270 ,p_move_order_line_id in NUMBER
271 ,p_total_qty in NUMBER
272 ,p_secondary_total_qty in NUMBER
273 ,p_type_code in NUMBER
274 ,p_business_object_id in NUMBER
275 ,p_object_id in NUMBER
276 ,p_strategy_id in NUMBER
277 );
278
279 --------------- Insert into trace lines table -------
280
281 procedure insert_trace_lines
282 (
283 p_api_version in NUMBER
284 ,p_init_msg_list in VARCHAR2 DEFAULT fnd_api.g_false
285 ,p_validation_level in NUMBER DEFAULT fnd_api.g_valid_level_full
286 ,x_return_status out NOCOPY VARCHAR2
287 ,x_msg_count out NOCOPY number
288 ,x_msg_data out NOCOPY varchar2
289 ,p_header_id in NUMBER
290 ,p_rule_id in NUMBER
291 ,p_pre_suggestions IN WMS_SEARCH_ORDER_GLOBALS_PVT.pre_suggestions_record_tbl
292 );
293 --------------------- called internally to insert a single row into trace headers
294 Procedure insert_headers_row
295 (
296 x_header_id IN NUMBER,
297 x_pick_header_id IN NUMBER,
298 x_move_order_line_id IN NUMBER,
299 x_total_qty IN NUMBER,
300 x_secondary_total_qty IN NUMBER, -- new
301 x_type_code IN NUMBER,
302 x_business_object_id IN NUMBER,
303 x_object_id IN NUMBER,
304 x_strategy_id IN NUMBER,
305 x_last_updated_by IN NUMBER,
306 x_last_update_date IN DATE ,
307 x_created_by IN NUMBER ,
308 x_creation_date IN DATE ,
309 x_last_update_login IN NUMBER ,
310 x_object_name IN VARCHAR2,
311 x_simulation_mode IN VARCHAR2,
312 x_sid IN NUMBER
313 ) ;
314
315 --------------------- called internally to insert a single row into trace lines
316 Procedure insert_lines_row
317 (
318 x_header_id IN NUMBER
319 ,x_line_id IN NUMBER
320 ,x_rule_id IN NUMBER
321 ,x_quantity IN NUMBER
322 ,x_revision IN VARCHAR2
323 ,x_lot_number IN VARCHAR2
324 ,x_lot_expiration_date IN DATE
325 ,x_serial_number IN VARCHAR2
326 ,x_subinventory_code IN VARCHAR2
327 ,x_locator_id IN NUMBER
328 ,x_lpn_id IN NUMBER
329 ,x_cost_group_id IN NUMBER
330 ,x_uom_code IN VARCHAR2
331 ,x_remaining_qty IN NUMBER
332 ,x_ATT_qty IN NUMBER
333 ,x_suggested_qty IN NUMBER
334 ,x_sec_uom_code IN VARCHAR2 -- new
335 ,x_sec_qty IN NUMBER -- new
336 ,x_sec_ATT_qty IN NUMBER -- new
337 ,x_sec_suggested_qty IN NUMBER -- new
338 ,x_grade_code IN VARCHAR2 -- new
339 ,x_same_subinv_loc_flag IN VARCHAR2
340 ,x_ATT_qty_flag IN VARCHAR2
341 , x_consist_string_flag IN VARCHAR2
342 , x_order_string_flag IN VARCHAR2
343 ,x_Material_status_flag IN VARCHAR2
344 ,x_Pick_UOM_flag IN VARCHAR2
345 ,x_partial_pick_flag IN VARCHAR2
346 ,x_Serial_number_used_flag IN VARCHAR2
347 ,x_CG_comingle_flag IN VARCHAR2
348 ,x_entire_lpn_flag IN VARCHAR2
349 ,x_comments IN VARCHAR2
350 ,x_creation_date IN DATE
351 ,x_created_by IN NUMBER
352 ,x_last_update_date IN DATE
353 ,x_last_updated_by IN NUMBER
354 ,x_last_update_login IN NUMBER
355 ) ;
356 -----------------------------------
357 -- Function that return 'TRUE' and 'FALSE' if the passed item_id is
358 -- in Global Variables
359
360 FUNCTION IS_Object_selected ( p_move_order_line_id number,
361 p_engine_type Varchar2,
362 p_object_type varchar2,
363 p_object_id number )
364 RETURN VARCHAR2;
365 -------------- Overloaded Function --------------
366 FUNCTION IS_BO_Object_selected ( p_move_order_line_id number,
367 p_engine_type Varchar2,
368 p_object_type varchar2,
369 p_object Varchar2 )
370 RETURN VARCHAR2;
371
372 PROCEDURE DELETE_TRACE_ROWS;
373
374 ---------------------------------------------------
375 --- This procedure call is used to populate records into
376 --- Three temp table one for each material suggestions, lot numbers
377 --- and serial number tables. Lot and Serial Tables will be populated
378 --- based on the lot_insert_flag and serial_insert_flags
379 --- '0' - for no records to be inserted and '1' for records to be inserted
380 --- The data in these three tables will be used by Run Time trace form
381
382 procedure insert_txn_trace_rows(
383 p_api_version in NUMBER
384 ,p_init_msg_list in VARCHAR2 DEFAULT fnd_api.g_false
385 ,p_validation_level in NUMBER DEFAULT fnd_api.g_valid_level_full
386 ,x_return_status out NOCOPY VARCHAR2
387 ,x_msg_count out NOCOPY number
388 ,x_msg_data out NOCOPY varchar2
389 ,p_txn_header_id in number
390 ,p_insert_lot_flag in number
391 ,p_insert_serial_flag in number);
392
393 ---------------------------------------------------------------------------------------
394 ---- This procedure is used by 'Run time trace form' to set the
395 ---- global variables based on WMS_RULE_TRACE_HEADERS record for a given move order
396 ---- so that the actual traceed path could be shown in the form.
397
398 procedure set_global_variables(
399 p_move_order_line_id in NUMBER
400 ,p_trace_date in DATE
401 ,x_return_status out NOCOPY VARCHAR2);
402 -------------------------------------------------
403 --- get Pick or Putaway header id from global variables
404
405 FUNCTION get_trace_line_header_id
406 ( engine_type IN VARCHAR2 )
407 RETURN NUMBER;
408
409 FUNCTION get_strategy_id( p_rule_type IN NUMBER )
410 RETURN NUMBER;
411
412 FUNCTION get_rule_id( p_rule_type IN NUMBER )
413 RETURN NUMBER;
414
415
416 FUNCTION get_seq_num ( p_rule_type IN NUMBER )
417 RETURN NUMBER;
418
419
420
421 END; -- Package Specification WMS_SEARCH_ORDER_GLOBALS_PVT
[Home] [Help]