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