[Home] [Help]
PACKAGE: APPS.WSH_PR_CRITERIA
Source
1 PACKAGE WSH_PR_CRITERIA AS
2 /* $Header: WSHPRCRS.pls 120.5 2007/12/21 14:11:55 mvudugul ship $ */
3 --
4 -- Package
5 -- WSH_PR_CRITERIA
6 --
7 -- Purpose
8 -- This package does the following:
9 -- - Initializes the picking criteria
10 -- - Initializes the grouping rule
11 -- - Open and fetch unreleased and/or backordered line details
12 --
13
14 --
15 -- PACKAGE TYPES
16 --
17 TYPE relRecTyp IS RECORD (
18 source_line_id NUMBER,
19 source_header_id NUMBER,
20 organization_id NUMBER,
21 inventory_item_id NUMBER,
22 move_order_line_id NUMBER,
23 delivery_detail_id NUMBER,
24 ship_model_complete_flag VARCHAR2(1),
25 top_model_line_id NUMBER,
26 ship_from_location_id NUMBER,
27 ship_method_code VARCHAR2(30),
28 shipment_priority VARCHAR2(30),
29 date_scheduled DATE,
30 requested_quantity NUMBER,
31 requested_quantity_uom VARCHAR2(3),
32 project_id NUMBER,
33 task_id NUMBER,
34 from_sub VARCHAR2(10),
35 to_sub VARCHAR2(10),
36 planned_departure_date DATE,
37 delivery_id NUMBER,
38 unit_number VARCHAR2(30),
39 source_doc_type NUMBER,
40 -- hverddin 27-Jun-00 Start Of OPM Changes
41 -- HW OPMCONV grade is 150
42 preferred_grade VARCHAR2(150),
43 requested_quantity2 NUMBER,
44 requested_quantity_uom2 VARCHAR2(3),
45 -- hverddin 27-Jun-00 End Of OPM Changes
46 demand_source_header_id NUMBER,
47 released_status VARCHAR2(1),
48 ship_set_id NUMBER,
49 top_model_quantity NUMBER,
50 source_code VARCHAR2(30),
51 source_header_number VARCHAR2(150),
52 line_number NUMBER,
53 customer_id WSH_DELIVERY_DETAILS.CUSTOMER_ID%TYPE, -- X-dock, as requested by INV team
54 non_reservable_flag VARCHAR2(1) -- ECO 5220234
55 );
56
57 TYPE relRecTabTyp IS TABLE OF relRecTyp INDEX BY BINARY_INTEGER;
58
59 --
60 -- PUBLIC CONSTANTS
61 --
62 -- Indicates whether the process is online or conccuent
63 C_CONCUR CONSTANT BINARY_INTEGER := 1;
64 C_ONLINE CONSTANT BINARY_INTEGER := 2;
65
66 C_IMMEDIATE_PRINT_PS CONSTANT BINARY_INTEGER := 3;
67 C_DEFERRED_PRINT_PS CONSTANT BINARY_INTEGER := 4;
68
69 --
70 -- PUBLIC VARIABLES
71 --
72 release_table relRecTabTyp;
73 g_application_id NUMBER;
74 g_program_id NUMBER;
75 g_request_id NUMBER;
76 g_user_id NUMBER;
77 g_login_id NUMBER;
78 g_batch_name VARCHAR2(30);
79 g_to_subinventory VARCHAR2(10);
80 g_to_locator NUMBER;
81 g_from_subinventory VARCHAR2(10);
82 g_from_locator NUMBER;
83 g_trip_id NUMBER;
84 g_trip_stop_id NUMBER;
85 g_ship_from_loc_id NUMBER;
86 g_delivery_id NUMBER;
87 g_del_detail_id NUMBER;
88 g_order_type_id NUMBER; --Bugfix 3604021
89 g_order_header_id NUMBER;
90 g_organization_id NUMBER;
91 g_autodetail_flag VARCHAR2(1);
92 g_auto_pick_confirm_flag VARCHAR2(1);
93 g_pick_seq_rule_id NUMBER;
94 g_pick_grouping_rule_id NUMBER;
95 g_autocreate_deliveries VARCHAR2(1);
96 g_autopack_flag VARCHAR2(1);
97 g_autopack_level NUMBER;
98 g_auto_ship_confirm_rule_id NUMBER;
99 g_non_picking_flag VARCHAR2(1);
100 g_use_autocreate_del_orders VARCHAR2(1) := 'Y';
101 g_primary_psr VARCHAR2(30);
102 g_submission NUMBER := C_CONCUR;
103 g_suppress_print VARCHAR2(1) := FND_API.G_FALSE;
104 g_doc_set_id NUMBER;
105 g_existing_rsvs_only_flag VARCHAR2(1);
106 -- To set flag that at least one Non-Reservable Item exists
107 g_nonreservable_item VARCHAR2(1) := 'N';
108 g_task_planning_flag VARCHAR2(1) := 'N';
109 --
110 -- rlanka : Pack J Pick Release enhancement
111 --
112 g_CategoryID NUMBER;
113 g_CategorySetID NUMBER;
114 g_RelSubInventory VARCHAR2(10);
115 g_RegionID NUMBER;
116 g_ZoneID NUMBER;
117 g_acDelivCriteria VARCHAR2(1);
118 g_from_request_date DATE;
119 g_to_request_date DATE;
120 g_from_sched_ship_date DATE;
121 g_to_sched_ship_date DATE;
122 -- deliveryMerge
123 g_append_flag VARCHAR2(1);
124
125 -- Bug #3266659 : Shipset/SMC Criteria
126 g_ship_set_smc_flag VARCHAR2(1);
127
128 -- To set if Delivery is part of Pick Slip Grouping rule
129 g_use_delivery_ps VARCHAR2(1) := 'N';
130
131 -- X-dock
132 g_allocation_method VARCHAR2(1);
133 g_crossdock_criteria_id NUMBER;
134
135 g_actual_departure_date DATE;
136
137 g_credit_check_option VARCHAR2(1) := NULL;
138
139 g_sql_stmt VARCHAR2(32767);
140
141 MAX_LINES NUMBER := 52;
142
143 -- Bug 4775539
144 g_honor_pick_from VARCHAR2(1) :='Y';
145 --bug# 6689448 (replenishment project)
146 g_dynamic_replenishment_flag VARCHAR2(1);
147
148 --
149 -- PUBLIC FUNCTIONS/PROCEDURES
150 --
151
152 -- Start of comments
153 -- API name : Init
154 -- Type : Public
155 -- Pre-reqs : None.
156 -- Procedure: API to initializes session and criteria for pick release. Api does
157 -- 1. Initializes variables for the session
158 -- 2. Retrieves criteria for the batch and sets up session variables.
159 -- 3. Locks row for the batch
160 -- 4. Update who columns for the batch
161 -- Parameters :
162 -- IN:
163 -- p_batch_id IN batch to be processed
164 -- p_worker_id IN batch to be processed
165 -- OUT:
166 -- x_api_status OUT NOCOPY Standard to output api status.
167 -- End of comments
168 Procedure Init (
169 p_batch_id IN NUMBER,
170 p_worker_id IN NUMBER,
171 x_api_status OUT NOCOPY VARCHAR2
172 );
173
174
175 -- Start of comments
176 -- API name : Init_Rules
177 -- Type : Public
178 -- Pre-reqs : None.
179 -- Procedure: API to retrieves sequencing information based on sequence rule and
180 -- group information based on grouping rule.
181 -- Parameters :
182 -- IN:
183 -- p_pick_seq_rule_id IN pick sequence rule id.
184 -- p_pick_grouping_rule_id IN pick grouping rule id.
185 -- OUT:
186 -- x_api_status OUT NOCOPY Standard to output api status.
187 -- End of comments
188 PROCEDURE Init_Rules (
189 p_pick_seq_rule_id IN NUMBER,
190 p_pick_grouping_rule_id IN NUMBER,
191 x_api_status OUT NOCOPY VARCHAR2
192 );
193
194 -- Start of comments
195 -- API name : Get_Worker_Records
196 -- Type : Public
197 -- Pre-reqs : None.
198 -- Procedure: API to get worker records for a specific batch_id and organization_id combination
199 -- based on the mode (PICK-SS / PICK)
200 -- Parameters :
201 -- IN:
202 -- p_mode IN Mode (Valid Values : PICK-SS and PICK)
203 -- p_batch_id IN batch to be processed
204 -- p_organization_id IN Organization to be processed
205 -- OUT:
206 -- x_api_status OUT NOCOPY Standard to output api status.
207 -- End of comments
208 PROCEDURE Get_Worker_Records (
209 p_mode IN VARCHAR2,
210 p_batch_id IN NUMBER,
211 p_organization_id IN NUMBER,
212 x_api_status OUT NOCOPY VARCHAR2
213 );
214
215
216 -- Start of comments
217 -- API name : Init_Cursor
218 -- Type : Public
219 -- Pre-reqs : None.
220 -- Procedure: API to creates a dynamic SQL statement for delivery lines based on release criteria .
221 --
222 -- Parameters :
223 -- IN:
224 -- p_organization_id IN Organization id.
225 -- p_mode IN Mode, valid value SUMMARY/WORKER.
226 -- p_wms_org IN Is Organization WMS enabled, valid value Y/N.
227 -- p_mo_header_id IN Move Order Header id.
228 -- p_inv_item_id IN Inventory Item id.
229 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
230 -- p_print_flag IN If need to print the value in log file.
231 -- p_express_pick IN If express pick, valid value Y/N.
232 -- p_batch_id IN Batch to be processed.
233 -- OUT:
234 -- x_worker_count OUT NOCOPY Worker Records Count.
235 -- x_smc_worker_count OUT NOCOPY SMC Worker Records Count.
236 -- x_dd_count OUT NOCOPY Delivery Details Records Count.
237 -- x_api_status OUT NOCOPY Standard to output api status.
238 -- End of comments
239
240 Procedure Init_Cursor (
241 p_organization_id IN NUMBER,
242 p_mode IN VARCHAR2,
243 p_wms_org IN VARCHAR2,
244 p_mo_header_id IN NUMBER,
245 p_inv_item_id IN NUMBER,
246 p_enforce_ship_set_and_smc IN VARCHAR2,
247 p_print_flag IN VARCHAR2,
248 p_express_pick IN VARCHAR2,
249 p_batch_id IN NUMBER,
250 x_worker_count OUT NOCOPY NUMBER,
251 x_smc_worker_count OUT NOCOPY NUMBER,
252 x_dd_count OUT NOCOPY NUMBER,
253 x_api_status OUT NOCOPY VARCHAR2
254 ) ;
255
256 -- Start of comments
257 -- API name : Get_Lines
258 -- Type : Public
259 -- Pre-reqs : None.
260 -- Procedure: API This routine returns information about the lines that
261 -- are eligible for release.
262 -- 1.Open the dynamic cursor for sql generated in api Init_Cursor for fetching.
263 -- 2. It fetches rows from the cursor for unreleased and backordered lines, and inserts the each
264 -- row in the release_table based on the release sequence rule.
265 -- Parameters :
266 -- IN:
267 -- p_enforce_ship_set_and_smc IN Whether to enforce Ship Set and SMC validate value Y/N.
268 -- p_wms_flag IN Org is WMS enabled or not. Valid values Y/N.
269 -- p_express_pick_flag IN Express Pick is enabled or not , Valid Values Y/N.
270 -- p_batch_id IN Pick Release Batch ID.
271 -- OUT:
272 -- x_done_flag OUT NOCOPY whether all lines have been fetched
273 -- x_api_status OUT NOCOPY Standard to output api status.
274 -- End of comments
275 Procedure Get_Lines (
276 p_enforce_ship_set_and_smc IN VARCHAR2,
277 p_wms_flag IN VARCHAR2,
278 p_express_pick_flag IN VARCHAR2,
279 p_batch_id IN NUMBER,
280 x_done_flag OUT NOCOPY VARCHAR2,
281 x_api_status OUT NOCOPY VARCHAR2
282 );
283
284 END WSH_PR_CRITERIA;