1 PACKAGE WSH_PR_PICKING_ROWS AUTHID CURRENT_USER AS
2 /* $Header: WSHPRPRS.pls 115.2 99/07/16 08:20:09 porting ship $ */
3
4 --
5 -- Package
6 -- WSH_PR_PICKING_ROWS
7 --
8 -- Purpose
9 -- This package does the following:
10 -- - Open and fetch unreleased line details cursor
11 -- - Open and fetch backorderd line details cursor
12 -- - Open and fetch non-shippable lines cursor
13 --
14 -- History
15 -- 16-SEP-96 RSHIVRAM Created
16 --
17
18 --
19 -- PACKAGE TYPES
20 --
21 TYPE relRecTyp IS RECORD (
22 line_id BINARY_INTEGER,
23 header_id BINARY_INTEGER,
24 org_id BINARY_INTEGER,
25 ato_flag VARCHAR2(1),
26 line_detail_id BINARY_INTEGER,
27 ship_model_complete VARCHAR2(1),
28 ship_set_number BINARY_INTEGER,
29 parent_line_id BINARY_INTEGER,
30 ld_warehouse_id BINARY_INTEGER,
31 ship_to_site_use_id BINARY_INTEGER,
32 ship_to_contact_id BINARY_INTEGER,
33 ship_method_code VARCHAR2(30),
34 shipment_priority VARCHAR2(30),
35 departure_id BINARY_INTEGER,
36 delivery_id BINARY_INTEGER,
37 item_type_code VARCHAR2(30),
38 schedule_date BINARY_INTEGER,
39 ordered_quantity BINARY_INTEGER,
40 cancelled_quantity BINARY_INTEGER,
41 l_inventory_item_id BINARY_INTEGER,
42 ld_inventory_item_id BINARY_INTEGER,
43 customer_item_id BINARY_INTEGER,
44 dep_plan_required_flag VARCHAR2(1),
45 shipment_schedule_line_id BINARY_INTEGER,
46 unit_code VARCHAR2(3),
47 line_type_code VARCHAR2(30),
48 component_code VARCHAR2(1000),
49 standard_comp_freeze_date VARCHAR2(15),
50 order_number BINARY_INTEGER,
51 order_type_id BINARY_INTEGER,
52 customer_id BINARY_INTEGER,
53 invoice_to_site_use_id BINARY_INTEGER,
54 planned_departure_date_d BINARY_INTEGER, -- day component of departure date
55 planned_departure_date_t BINARY_INTEGER, -- time component of departure date
56 master_container_item_id BINARY_INTEGER,
57 detail_container_item_id BINARY_INTEGER,
58 invoice_value NUMBER,
59 load_seq_number BINARY_INTEGER,
60 backorder_line BINARY_INTEGER,
61 primary_rsr_switch BINARY_INTEGER
62 );
63
64 TYPE relRecTabTyp IS TABLE OF relRecTyp INDEX BY BINARY_INTEGER;
65 TYPE intTabTyp IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
66 TYPE cflagTabTyp IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
67 TYPE cnameTabTyp IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
68 TYPE ccodeTabTyp IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
69 TYPE cbufTabTyp IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
70 TYPE cdateTabTyp IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
71
72 --
73 -- PUBLIC VARIABLES
74 --
75 release_table relRecTabTyp;
76 sync_table relrecTabTyp;
77
78 --
79 -- PUBLIC FUNCTIONS/PROCEDURES
80 --
81
82 --
83 -- Name
84 -- FUNCTION Init
85 --
86 -- Purpose
87 -- This function, based on the mode opens appropriate cursors.
88 --
89 -- Return Values
90 -- -1 => Failure
91 -- 0 => Success
92 --
93 -- Notes
94 --
95
96 FUNCTION Init RETURN BINARY_INTEGER;
97
98 --
99 -- Name
100 -- FUNCTION Get_Size
101 --
102 -- Purpose
103 -- This function returns the number of rows returned in the
104 -- previous call to Get_Line_Details
105 --
106 -- Return Values
107 -- Number of rows fetched in previous call
108 --
109 -- Notes
110 --
111
112 FUNCTION Get_Size RETURN BINARY_INTEGER;
113
114 --
115 -- Name
116 -- PROCEDURE Get_Line_Details
117 --
118 -- Purpose
119 -- This routine returns information about the lines that
120 -- are eligible for release. They are placed in a table.
121 -- It controls the number of lines to be fetched and
122 -- provided to the client. The default value is set at 50.
123 -- This may be modified by the first parameter if it is not 0.
124 -- It also indicates whether there are any more lines to be
125 -- retrieved.
126 --
127 -- Arguments
128 -- All the column values and call_parameters
129 -- Call_parameters(2) transalates as
130 -- -1 => Failure
131 -- 0 => Success, but there are more rows to be fetched
132 -- 2 => Success, no more rows to fetch
133 -- Call_parameters(1) is the size
134 --
135 -- Notes
136 --
137
138 PROCEDURE Get_Line_Details(
139 line_id OUT intTabTyp,
140 header_id OUT intTabTyp,
141 org_id OUT intTabTyp,
142 ato_flag OUT cflagTabTyp,
143 line_detail_id OUT intTabTyp,
144 ship_model_complete OUT cflagTabTyp,
145 ship_set_number OUT intTabTyp,
146 parent_line_id OUT intTabTyp,
147 ld_warehouse_id OUT intTabTyp,
148 ship_to_site_use_id OUT intTabTyp,
149 ship_to_contact_id OUT intTabTyp,
150 ship_method_code OUT cnameTabTyp,
151 shipment_priority OUT cnameTabTyp,
152 departure_id OUT intTabTyp,
153 delivery_id OUT intTabTyp,
154 item_type_code OUT cnameTabTyp,
155 schedule_date OUT intTabTyp,
156 ordered_quantity OUT intTabTyp,
157 cancelled_quantity OUT intTabTyp,
158 l_inventory_item_id OUT intTabTyp,
159 ld_inventory_item_id OUT intTabTyp,
160 customer_item_id OUT intTabTyp,
161 dep_plan_required_flag OUT cflagTabTyp,
162 shipment_schedule_line_id OUT intTabTyp,
163 unit_code OUT ccodeTabTyp,
164 line_type_code OUT cnameTabTyp,
165 component_code OUT cbufTabTyp,
166 standard_comp_freeze_date OUT cdateTabTyp,
167 order_number OUT intTabTyp,
168 order_type_id OUT intTabTyp,
169 customer_id OUT intTabTyp,
170 invoice_to_site_use_id OUT intTabTyp,
171 master_container_item_id OUT intTabTyp,
172 detail_container_item_id OUT intTabTyp,
173 load_seq_number OUT intTabTyp,
174 backorder_line OUT intTabTyp,
175 primary_rsr_switch OUT intTabTyp,
176 call_parameters IN OUT intTabTyp
177 );
178
179
180 --
181 -- Name
182 -- FUNCTION Set_Sync_Line
183 --
184 -- Purpose
185 -- This routine sets up the sync_line_id to fetch the
186 -- records that have been demand synchronized.
187 --
188 -- Arguments
189 --
190 -- Notes
191 --
192
193 FUNCTION Set_Sync_Line(
194 p_sync_line_id IN BINARY_INTEGER
195 ) RETURN BINARY_INTEGER;
196
197
198 --
199 -- Name
200 -- PROCEDURE Get_Sync_Line_Details
201 --
202 -- Purpose
203 -- This routine returns lines that heve been BOM exploded and/or
204 -- Demand synchronized for a particular line.
205 --
206 -- Arguments
207 -- All the column values and call_parameters
208 -- Call_parameters(2) transalates as
209 -- -1 => Failure
210 -- 0 => Success, but there are more rows to be fetched
211 -- 2 => Success, no more rows to fetch
212 -- Call_parameters(1) is the size
213 --
214 -- Notes
215 --
216
217 PROCEDURE Get_Sync_Line_Details(
218 line_id OUT intTabTyp,
219 header_id OUT intTabTyp,
220 org_id OUT intTabTyp,
221 ato_flag OUT cflagTabTyp,
222 line_detail_id OUT intTabTyp,
223 ship_model_complete OUT cflagTabTyp,
224 ship_set_number OUT intTabTyp,
225 parent_line_id OUT intTabTyp,
226 ld_warehouse_id OUT intTabTyp,
227 ship_to_site_use_id OUT intTabTyp,
228 ship_to_contact_id OUT intTabTyp,
229 ship_method_code OUT cnameTabTyp,
230 shipment_priority OUT cnameTabTyp,
231 departure_id OUT intTabTyp,
232 delivery_id OUT intTabTyp,
233 schedule_date OUT intTabTyp,
234 customer_item_id OUT intTabTyp,
235 dep_plan_required_flag OUT cflagTabTyp,
236 order_number OUT intTabTyp,
237 order_type_id OUT intTabTyp,
238 customer_id OUT intTabTyp,
239 invoice_to_site_use_id OUT intTabTyp,
240 master_container_item_id OUT intTabTyp,
241 detail_container_item_id OUT intTabTyp,
242 load_seq_number OUT intTabTyp,
243 call_parameters IN OUT intTabTyp
244 );
245
246
247 --
248 -- Name
249 -- Function Set_Request_Lines
250 --
251 -- Purpose
252 -- Set request_id for non-ship lines that belong to the
253 -- same warehouse and match the same other release criteria
254 -- to be marked as Not Applicable later
255 --
256 -- Arguments
257 -- p_sync_line_id => Sync line for the mode
258 --
259 -- Return Values
260 -- 0 => Success
261 -- -1 => Failure
262 --
263
264 FUNCTION Set_Request_Lines(
265 p_sync_line_id IN BINARY_INTEGER
266 ) RETURN BINARY_INTEGER;
267
268
269 --
270 -- Name
271 -- PROCEDURE Get_Non_Ship_Lines
272 --
273 -- Purpose
274 -- This procedure fetches all the non-shippable lines that need to
275 -- be passed through Pick Release.
276 --
277 -- Arguments
278 -- All the column values and call_parameters
279 -- Call_parameters(2) transalates as
280 -- -1 => Failure
281 -- 0 => Success, but there are more rows to be fetched
282 -- 2 => Success, no more rows to fetch
283 -- Call_parameters(1) is the size
284 --
285 -- Notes
286 --
287
288 PROCEDURE Get_Non_Ship_Lines(
289 line_id OUT intTabTyp,
290 header_id OUT intTabTyp,
291 line_detail_id OUT intTabTyp,
292 org_id OUT intTabTyp,
293 call_parameters IN OUT intTabTyp
294 );
295
296
297 --
298 -- Name
299 -- PROCEDURE Close_Cursors
300 --
301 -- Purpose
302 -- This function closes all the cursors that may have been
303 -- opened to process pick release eligible lines.
304 --
305 -- Arguments
306 --
307 -- Notes
308 --
309
310 PROCEDURE Close_Cursors;
311
312 END WSH_PR_PICKING_ROWS;
[Home] [Help]