1 PACKAGE INV_CYC_LOVS AS
2 /* $Header: INVCYCLS.pls 120.2.12010000.1 2008/07/24 01:28:21 appldev ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 /* Cycle Count Entry Record subtype */
7 SUBTYPE cc_entry IS MTL_CYCLE_COUNT_ENTRIES%ROWTYPE;
8
9 /* Serial number cycle count entry record subtype */
10 SUBTYPE cc_serial_entry IS MTL_CC_SERIAL_NUMBERS%ROWTYPE;
11
12
13 -- Name: GET_CYC_LOV
14 --
15 -- Input parameters:
16 -- p_cycle_count Restricts LOV SQL to the user input text
17 -- p_organization_id Organization ID
18 --
19 -- Output parameters:
20 -- x_cyc_lov Returns LOV rows as a reference cursor
21 --
22 -- Functions: This API returns valid cycle counts
23 --
24 PROCEDURE get_cyc_lov
25 (x_cyc_lov OUT NOCOPY t_genref,
26 p_cycle_count IN VARCHAR2,
27 p_organization_id IN NUMBER);
28
29
30 PROCEDURE process_entry
31 (p_cycle_count_header_id IN NUMBER ,
32 p_organization_id IN NUMBER ,
33 p_subinventory IN VARCHAR2 ,
34 p_locator_id IN NUMBER := NULL ,
35 p_parent_lpn_id IN NUMBER := NULL ,
36 p_inventory_item_id IN NUMBER ,
37 p_revision IN VARCHAR2 := NULL ,
38 p_lot_number IN VARCHAR2 := NULL ,
39 p_from_serial_number IN VARCHAR2 := NULL ,
40 p_to_serial_number IN VARCHAR2 := NULL ,
41 p_count_quantity IN NUMBER ,
42 p_count_uom IN VARCHAR2 ,
43 p_unscheduled_count_entry IN NUMBER ,
44 p_user_id IN NUMBER ,
45 p_cost_group_id IN NUMBER := NULL
46 ,p_secondary_uom IN VARCHAR2 := NULL -- INVCONV, NSRIVAST
47 ,p_secondary_qty IN NUMBER := NULL -- INVCONV, NSRIVAST
48 );
49
50 /* start of fix for 4539926 */
51 /* Added this procedure to delete orphan wms_dispatched_tasks records
52 which will exist if the cycle count has been queued and is performed
53 through cycle count menu */
54 PROCEDURE delete_wdt
55 (p_cycle_count_header_id IN NUMBER ,
56 p_organization_id IN NUMBER ,
57 p_subinventory IN VARCHAR2 ,
58 p_locator_id IN NUMBER ,
59 p_parent_lpn_id IN NUMBER ,
60 p_inventory_item_id IN NUMBER ,
61 p_revision IN VARCHAR2 ,
62 p_lot_number IN VARCHAR2 ,
63 p_from_serial_number IN VARCHAR2 ,
64 p_to_serial_number IN VARCHAR2 ,
65 p_count_quantity IN NUMBER ,
66 p_count_uom IN VARCHAR2 ,
67 p_unscheduled_count_entry IN NUMBER ,
68 p_user_id IN NUMBER ,
69 p_cost_group_id IN NUMBER );
70 /* end of fix for 4539926 */
71
72 PROCEDURE insert_row;
73
74 PROCEDURE update_row;
75
76 PROCEDURE current_to_prior;
77
78 PROCEDURE current_to_first;
79
80 PROCEDURE entry_to_current
81 (p_count_date IN DATE ,
82 p_counted_by_employee_id IN NUMBER ,
83 p_system_quantity IN NUMBER ,
84 p_reference IN VARCHAR2 ,
85 p_primary_uom_quantity IN NUMBER ,
86 p_sec_system_quantity IN NUMBER DEFAULT NULL -- nsinghi Bug#6052831 Added this parameter.
87 );
88
89 PROCEDURE zero_count_logic;
90
91 PROCEDURE get_tolerances
92 (pre_approve_flag IN VARCHAR2,
93 x_approval_tolerance_positive OUT NOCOPY NUMBER,
94 x_approval_tolerance_negative OUT NOCOPY NUMBER,
95 x_cost_tolerance_positive OUT NOCOPY NUMBER,
96 x_cost_tolerance_negative OUT NOCOPY NUMBER);
97
98
99 PROCEDURE recount_logic
100 (p_approval_tolerance_positive IN NUMBER,
101 p_approval_tolerance_negative IN NUMBER,
102 p_cost_tolerance_positive IN NUMBER,
103 p_cost_tolerance_negative IN NUMBER
104 );
105
106 PROCEDURE tolerance_logic
107 (p_approval_tolerance_positive IN NUMBER,
108 p_approval_tolerance_negative IN NUMBER,
109 p_cost_tolerance_positive IN NUMBER,
110 p_cost_tolerance_negative IN NUMBER);
111
112 PROCEDURE valids;
113
114 PROCEDURE in_tolerance;
115
116 PROCEDURE out_tolerance;
117
118 PROCEDURE no_adj_req;
119
120 PROCEDURE pre_insert;
121
122 PROCEDURE pre_update;
123
124 PROCEDURE final_preupdate_logic;
125
126 PROCEDURE delete_reservation;
127
128 PROCEDURE duplicate_entries;
129
130 PROCEDURE post_commit;
131
132 PROCEDURE system_quantity
133 (x_system_quantity OUT NOCOPY NUMBER);
134
135 -- nsinghi bug#6052831. Created overloaded procedure to handle secondary qty.
136 PROCEDURE system_quantity (
137 x_system_quantity OUT NOCOPY NUMBER
138 , x_sec_system_quantity OUT NOCOPY NUMBER
139 );
140
141 PROCEDURE value_variance
142 (x_value_variance OUT NOCOPY NUMBER);
143
144 FUNCTION wms_is_installed
145 (p_organization_id IN NUMBER) RETURN BOOLEAN;
146
147 FUNCTION get_item_cost
148 (in_org_id NUMBER,
149 in_item_id NUMBER,
150 in_locator_id NUMBER)
151 RETURN NUMBER;
152
153 PROCEDURE is_serial_entered
154 (event IN VARCHAR2,
155 entered OUT NOCOPY NUMBER);
156
157 PROCEDURE new_serial_number;
158
159 PROCEDURE existing_serial_number;
160
161 FUNCTION check_serial_number_location (issue_receipt VARCHAR2) RETURN
162 BOOLEAN;
163
164 FUNCTION is_serial_loaded ( p_organization_id IN NUMBER,
165 p_inventory_item_id IN NUMBER,
166 p_serial_number IN VARCHAR2,
167 p_lpn_id IN NUMBER
168 )
169 RETURN number;
170
171 PROCEDURE perform_serial_adj_txn;
172
173 PROCEDURE count_entry_status_code;
174
175 PROCEDURE update_serial_row;
176
177 PROCEDURE mark;
178
179 PROCEDURE unmark (cycle_cnt_entry_id NUMBER);
180
181 PROCEDURE get_profiles;
182
183 PROCEDURE get_employee
184 (p_organization_id IN NUMBER);
185
186 PROCEDURE process_summary
187 (p_cycle_count_header_id IN NUMBER ,
188 p_organization_id IN NUMBER ,
189 p_subinventory IN VARCHAR2 ,
190 p_locator_id IN NUMBER := NULL ,
191 p_parent_lpn_id IN NUMBER := NULL ,
192 p_unscheduled_count_entry IN NUMBER ,
193 p_user_id IN NUMBER);
194
195 PROCEDURE inv_serial_info
196 (p_from_serial_number IN VARCHAR2,
197 p_to_serial_number IN VARCHAR2,
198 x_prefix OUT NOCOPY VARCHAR2,
199 x_quantity OUT NOCOPY VARCHAR2,
200 x_from_number OUT NOCOPY VARCHAR2,
201 x_to_number OUT NOCOPY VARCHAR2,
202 x_errorcode OUT NOCOPY NUMBER);
203
204 PROCEDURE get_default_cost_group_id
205 (p_organization_id IN NUMBER,
206 p_subinventory IN VARCHAR2,
207 x_out OUT NOCOPY NUMBER);
208
209 PROCEDURE get_cost_group_id
210 (p_organization_id IN NUMBER ,
211 p_subinventory IN VARCHAR2 ,
212 p_locator_id IN NUMBER := NULL ,
213 p_parent_lpn_id IN NUMBER := NULL ,
214 p_inventory_item_id IN NUMBER ,
215 p_revision IN VARCHAR2 := NULL ,
216 p_lot_number IN VARCHAR2 := NULL ,
217 p_serial_number IN VARCHAR2 := NULL ,
218 x_out OUT NOCOPY NUMBER);
219
220 PROCEDURE ok_proc;
221
222 PROCEDURE serial_tolerance_logic
223 (p_serial_adj_qty IN NUMBER,
224 p_app_tol_pos IN NUMBER,
225 p_app_tol_neg IN NUMBER,
226 p_cost_tol_pos IN NUMBER,
227 p_cost_tol_neg IN NUMBER);
228
229 PROCEDURE get_final_count_info;
230
231 -- This gets the number of scheduled cycle count
232 -- entries left for the given cycle count header ID
233 PROCEDURE get_scheduled_entry
234 (p_cycle_count_header_id IN NUMBER,
235 x_count OUT NOCOPY NUMBER);
236
237 -- This is called for inserting dynamic lots which
238 -- was entered/typed in instead of dynamically generating it
239 PROCEDURE insert_dynamic_lot
240 (p_api_version IN NUMBER,
241 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
242 p_commit IN VARCHAR2 := FND_API.G_FALSE,
243 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
244 p_inventory_item_id IN NUMBER,
245 p_organization_id IN NUMBER,
246 p_lot_number IN VARCHAR2,
247 p_expiration_date IN OUT NOCOPY DATE,
248 p_transaction_temp_id IN NUMBER DEFAULT NULL,
249 p_transaction_action_id IN NUMBER DEFAULT NULL,
250 p_transfer_organization_id IN NUMBER DEFAULT NULL,
251 p_status_id IN NUMBER,
252 p_update_status IN VARCHAR2 := 'FALSE',
253 x_object_id OUT NOCOPY NUMBER,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2);
257
258
259 -- This is called for updating the serial number status for
260 -- predefined serials
261 PROCEDURE update_serial_status
262 (p_api_version IN NUMBER,
263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
264 p_commit IN VARCHAR2 := FND_API.G_FALSE,
265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
266 p_inventory_item_id IN NUMBER,
267 p_organization_id IN NUMBER,
268 p_from_serial_number IN VARCHAR2,
269 p_to_serial_number IN VARCHAR2,
270 p_current_status IN NUMBER,
271 p_serial_status_id IN NUMBER,
272 p_update_serial_status IN VARCHAR2,
273 p_lot_number IN VARCHAR2,
274 x_return_status OUT NOCOPY VARCHAR2,
275 x_msg_count OUT NOCOPY NUMBER,
276 x_msg_data OUT NOCOPY VARCHAR2);
277
278 -- This is a wrapper to call inventory insert_range_serial
279 PROCEDURE insert_range_serial
280 (p_api_version IN NUMBER,
281 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
282 p_commit IN VARCHAR2 := FND_API.G_FALSE,
283 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
284 p_inventory_item_id IN NUMBER,
285 p_organization_id IN NUMBER,
286 p_from_serial_number IN VARCHAR2,
287 p_to_serial_number IN VARCHAR2,
288 p_revision IN VARCHAR2,
289 p_lot_number IN VARCHAR2,
290 p_current_status IN NUMBER,
291 p_serial_status_id IN NUMBER,
292 p_update_serial_status IN VARCHAR2,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2);
296
297 -- This gets the system quantity for the item given
298 -- the available input information
299 PROCEDURE get_system_quantity
300 (p_organization_id IN NUMBER ,
301 p_subinventory IN VARCHAR2 ,
302 p_locator_id IN NUMBER ,
303 p_parent_lpn_id IN NUMBER ,
304 p_inventory_item_id IN NUMBER ,
305 p_revision IN VARCHAR2 := NULL ,
306 p_lot_number IN VARCHAR2 := NULL ,
307 p_uom_code IN VARCHAR2 ,
308 x_system_quantity OUT NOCOPY NUMBER);
309
310 -- This will clean up any outstanding cycle count tasks
311 -- that were completed as a result of performing a summary
312 -- count. The result can be such that the user is performing
313 -- task A but when doing a summary count on an LPN, could have
314 -- finished a different task B for which he/she was dispatched
315 -- to perform it but he/she was not explicitly doing it.
316 -- Without this call, we can have the case where the user still has
317 -- a task that has been dispatched to him/her which is no longer
318 -- active since it has already been completed.
319 PROCEDURE clean_up_tasks
320 (p_transaction_temp_id IN NUMBER);
321
322 END INV_CYC_LOVS;
[Home] [Help]