1 PACKAGE PO_SUBINVENTORIES_S AUTHID CURRENT_USER AS
2 /* $Header: POXCOS1S.pls 115.2 2002/11/25 23:38:08 sbull ship $*/
3
4 /* create client package */
5 /*
6 PACKAGE PO_SUBINVENTORIES_S IS
7 */
8
9 /*===========================================================================
10 FUNCTION NAME: val_subinventory()
11
12 DESCRIPTION:
13 This function is for receiving transactions. So transaction date,
14 destination type, item id, and organization id must be passed. It
15 does not do a generic subinventory validation. (1)
16
17 Valify if subinventory is required for a given destincation type.
18 'EXPENSE', 'SHOP FLOOR', 'INVENTORY'. 'RECEIVING', 'MULTIPLE'. (2)
19 Always required if destination type is inventory.
20
21 If subinventory is required, check whether subinventory field is
22 entered. MESSAGE_NAME="RCV_ALL_MISSING_SUBINVENTORY"
23
24 Check if given subinventory is valid based on item subinventory
25 restriction, subinventory in-active date, etc.
26
27
28 PARAMETERS:
29 x_subinventory IN VARCHAR2(10),
30 x_organization_id IN NUMBER,
31 x_transaction_date IN DATE, (3)
32 x_item_id IN NUMBER,
33 x_destination_type IN VARCHAR2
34
35 DESIGN REFERENCES: RCVRCERC.dd
36 RCVTXERT.dd
37
38 ALGORITHM:
39 if x_destination_type is not inventory, then
40 return ok
41 end if
42
43 if x_subinventory = '' then
44 return fail
45 end if
46
47 if subinventory active date expired then
48 return fail
49 end if
50
51 get restrict_subinventories_code from mtl_system_items for the
52 ->current x_item_id (2)
53 if x_subinventory is not in valid subinventory list, then
54 return fail
55 end if
56
57 return ok. looks like everything is good.
58
59 NOTES:
60
61 OPEN ISSUES:
62 1. Should we implement this function a more generic validation
63 routing. For example, some one want to check a subinventory
64 is valid without item info, destination info, etc.
65
66 2. No type 'MULTIPLE' should be passed in. If 'MULTIPLE', calling
67 procedure should get exactly destination type and then call
68 this function.
69
70 3. Since enter receipts and enter receipt transactions can set
71 transaction date to be different than sysdate, a transaction
72 date is needed to compare with inventory's inactive date.
73 Need to check/discuss with other products' implementations.
74
75 CLOSED ISSUES:
76
77 CHANGE HISTORY:
78 dropped parameters:
79 x_po_subinventory, x_distribution_id, x_source_type,
80 x_shipment_line_id
81 added parameters:
82 x_transaction_date - because sub inactive date is compared
83 with the transaction date.
84 ===========================================================================*/
85
86 FUNCTION val_subinventory
87 (
88 x_subinventory IN VARCHAR2,
89 x_organization_id IN NUMBER,
90 x_transaction_date IN DATE,
91 x_item_id IN NUMBER,
92 x_destination_type IN VARCHAR2
93 )
94 RETURN NUMBER;
95
96
97
98 /*===========================================================================
99 FUNCTION NAME: val_locator()
100
101 DESCRIPTION:
102 Verify if locator is needed based on locator control. To get locator
103 control, use get_locator_control.
104 1 - No locator control
105 2 - Prespecified locator control
106 3 - Dynamic entry locator control
107
108
109 PARAMETERS:
110 x_org_locator_control IN NUMBER, (3)
111 x_sub_locator_control IN NUMBER, (3)
112 x_item_locator_control IN NUMBER, (3)
113 x_transaction_date IN DATE, (5)
114 x_locator IN NUMBER,
115 x_item_id IN NUMBER,
116 x_subinventory_id IN NUMBER,
117 x_organization_id IN NUMBER
118
119
120 DESIGN REFERENCES: RCVRCERC.dd
121 RCVTXERT.dd
122
123 ALGORITHM:
124 get locator control using get_locator_control
125
126 if locator control = 1 (no locator control) then
127 return SUCCESS
128 end if
129
130 if x_locator = '' then
131 return FAIL
132 end if
133
134 case locator control
135 :if 2 (predefined locator control)
136 return inventory validate_locator (1)
137 :if 3 (dynamic entry locator control)
138 return inventory insert_locator (2)
139 :if others
140 return FAIL
141 end case
142
143 NOTES:
144 A. Inventory has the locator key-flex api. use fnd key flex update
145 definition to control key flex locator field in forms.
146
147 OPEN ISSUES:
148 1. Does inventory has a function/procedure check if a locator is in
149 predefined locator?
150
151 2. Does inventory has a function/procedure which insert a new locator
152 into defined locator list?
153
154 3. These parameters may be required in inventory functions/procedures
155 described in issue 1 and 2.
156
157 4. See CLOSED ISSUES.
158
159 5. Again like val_subinventory, does inactive_date compare against
160 sysdate or transaction_date which could differ from sysdata.
161
162 CLOSED ISSUES:
163 4. If get locator control returned no locator control at organization
164 level, but mtl_system_item has restrict locator control set, which
165 one takes precedance?
166
167 -- get_locator_control should resolve this conflict. If an item has
168 restrict locator control, then the item is under restrict locator
169 control even organization, subinventory and item itself does not have
170 locator control set.
171
172 CHANGE HISTORY:
173 ===========================================================================*/
174
175 FUNCTION val_locator
176 (
177 x_locator IN NUMBER,
178 x_item_id IN NUMBER,
179 x_subinventory IN VARCHAR2,
180 x_organization_id IN NUMBER
181 )
182 RETURN NUMBER;
183
184 /*===========================================================================
185 PROCEDURE NAME: get_locator_control()
186
187 DESCRIPTION:
188 Get a locator control for an item under a subinventory, and a given
189 organization.
190
191 This function evaluate locator controls at all level in the order of
192 organization, subinventory and item. Higher level has the precedence
193 than lower level. For example, if locator control at organization
194 level is set to no locator control, then no more checks needed at
195 lower levels. (1)
196
197 1 - No locator control
198 2 - Prespecified locator control
199 3 - Dynamic entry locator control
200 4 - Locator control determined at subinventory level
201 5 - Locator control determined at item level
202
203 PARAMETERS:
204 x_organization_id IN NUMBER,
205 x_subinventory IN VARCHAR2,
206 x_item_id IN NUMBER,
207 x_locator OUT NUMBER
208
209
210 DESIGN REFERENCES: RCVRCERC.dd
211 RCVTXERT.dd
212
213 ALGORITHM:
214 get organization locator control. the organization locator control
215 is defined in mtl_parameters by joining organization id.
216 select nvl(stock_locator_control_code, 1)
217 into organization locator control
218 from mtl_parameters
219 where organization_id = current organization id
220
221 if item has restrict locator control, return 2
222
223 if organization locator control = 1 or 2 or 3 then
224 locator_control = organization locator control
225 end proc
226 end if
227
228 if organization locator control = 4 then
229 get subinventory locator control. the subinventory locator control
230 is defined in mtl_secondary_inventories.
231 select nvl(locator_type, 1)
232 into subinventory locator control
233 from mtl_secondary_inventories
234 where organization_id = current organization id
235 and secondary_inventory_name = current subinventory name
236
237 if subinventory locator control = 1 or 2 or 3 then
238 locator_control = organization locator control
239 end proc
240 end if
241
242 if subinventory locator control != 5 then
243 end proc w/ error
244 end if
245 end if
246
247 get item locator control. the item locator control is defined in
248 mtl_system_items.
249 select nvl(msi.location_control_code, 1),
250 into item_locator_control,
251 from mtl_system_items msi,
252 where msi.organization_id = organization_id
253 and msi.inventory_item_id = transactions.item_id
254
255 locator control = item locator control
256 end proc.
257
258 NOTES:
259
260 OPEN ISSUES:
261 1. See CLOSED ISSUES.
262
263 2. Inventory has client side api LOCATOR.LOCATOR which takes
264 org_control, sub_control, item_control, restrict_code, neg_inv_code,
265 and trx_action-id. The problem with this is that you have to call
266 those different controls first. Need to find out what values can be
267 used for neg_inv_code, and trx_action_id.
268
269 CLOSED ISSUES:
270 1. If from org -> sub -> item, the locator control is 1. But the
271 item has restrict locator control. Return which locator control?
272
273 -- Always check item level restrict locator control, return 2 for
274 predefined control if an item has restrict locator control.
275
276 CHANGE HISTORY:
277 ===========================================================================*/
278
279 PROCEDURE get_locator_control
280 (
281 x_organization_id IN NUMBER,
282 x_subinventory IN VARCHAR2,
283 x_item_id IN NUMBER,
284 x_locator IN OUT NOCOPY NUMBER,
285 x_restrict_locator IN OUT NOCOPY NUMBER
286 );
287
288 PROCEDURE get_locator_control
289 (
290 x_organization_id IN NUMBER,
291 x_subinventory IN VARCHAR2,
292 x_item_id IN NUMBER,
293 x_locator IN OUT NOCOPY NUMBER
294 );
295
296 /*===========================================================================
297 FUNCTION NAME: get_default_subinventory()
298
299 DESCRIPTION:
300 Gets the default sub for a given item and org. This is used in
301 express and when you're starting up the form.
302
303 PARAMETERS:
304 x_organization_id IN NUMBER,
305 x_item_id IN NUMBER,
306 x_subinventory IN VARCHAR2(10),
307
308 DESIGN REFERENCES: RCVRCERC.dd
309 RCVTXERT.dd
310
311 ALGORITHM:
312
313 NOTES:
314
315 OPEN ISSUES:
316
317 CLOSED ISSUES:
318
319 CHANGE HISTORY:
320 GK 06/02/95 Created Function
321 ===========================================================================*/
322
323 PROCEDURE get_default_subinventory
324 (
325 x_organization_id IN NUMBER,
326 x_item_id IN NUMBER,
327 x_subinventory IN OUT NOCOPY VARCHAR2
328 );
329
330 /*===========================================================================
331 FUNCTION NAME: get_default_locator()
332
333 DESCRIPTION:
334 Gets the default locator for a given item and org. This is used in
335 express and when you're starting up the form.
336
337 PARAMETERS:
338 x_organization_id IN NUMBER,
339 x_item_id IN NUMBER,
340 x_subinventory IN VARCHAR2,
341 x_locator_id IN OUT NUMBER
342
343 DESIGN REFERENCES: RCVRCERC.dd
344 RCVTXERT.dd
345
346 ALGORITHM:
347
348 NOTES:
349
350 OPEN ISSUES:
351
352 CLOSED ISSUES:
353
354 CHANGE HISTORY:
355 GK 06/02/95 Created Function
356 ===========================================================================*/
357
358 PROCEDURE get_default_locator
359 (
360 x_organization_id IN NUMBER,
361 x_item_id IN NUMBER,
362 x_subinventory IN VARCHAR2,
363 x_locator_id IN OUT NOCOPY NUMBER
364 ) ;
365
366
367 /*===========================================================================
368 PROCEDURE NAME: check_sub_transfer
369
370 DESCRIPTION: This procedure checks to make sure that
371 we do not allow transfer of ASSET items from
372 an expense subinventory to an asset
373 subinventory.
374
375
376
377
378 PARAMETERS: x_source_organization_id IN NUMBER
379 x_destination_organization_id IN NUMBER
380 x_destination_subinventory IN VARCHAR2
381 x_item_id IN NUMBER
382 x_allow_expense_source OUT VARCHAR2
383
384 DESIGN REFERENCES: POXRQERQ.doc
385
386
387 ALGORITHM: - Get the asset_flag for the item in the
388 destination organization.
389 - If the item is not an asset item, set
390 allow_expense_source to 'Y' and return.
391 - Get the in_transit type for the orgs
392 involved in the transfer.
393 - If in_transit, set allow_expense_source
394 to 'N' and return.
395 - get the default destination subinventory
396 if not provided.
397 - get the subinventory type for the
398 subinventory and destination org.
399 - if the subinventory is an ASSET sub, set
400 allow_expense_source to 'N' else set
401 allow_expense_source to 'Y'.
402
403 NOTES:
404
405 OPEN ISSUES:
406
407
408 CLOSED ISSUES:
409
410 CHANGE HISTORY: 06/19 Ramana Y Mulpury
411 ===========================================================================*/
412
413 PROCEDURE check_sub_transfer (x_source_organization_id IN NUMBER,
414 x_destination_organization_id IN NUMBER,
415 x_destination_subinventory IN NUMBER,
416 x_item_id IN NUMBER,
417 x_allow_expense_source OUT NOCOPY VARCHAR2
418 );
419
420
421 PROCEDURE test_check_sub_transfer (x_source_organization_id IN NUMBER,
422 x_destination_organization_id IN NUMBER,
423 x_destination_subinventory IN NUMBER,
424 x_item_id IN NUMBER);
425
426
427
428 /*===========================================================================
429 FUNCTION NAME: val_src_subinventory
430
431 DESCRIPTION: This procedure validates the
432 source subinventory based on the
433 destination and item information.
434
435 This function returns FALSE if the
436 source subinventory is not valid.
437
438
439 PARAMETERS: x_src_sub IN VARCHAR2
440 x_dest_org_id IN NUMBER
441 x_dest_type IN VARCHAR2
442 x_dest_org_id IN NUMBER
443 x_dest_sub IN VARCHAR2
444 x_item_id IN NUMBER
445
446
447 DESIGN REFERENCES: POXRQERQ.doc
448
449
450 ALGORITHM:
451
452 NOTES:
453
454 OPEN ISSUES: DEBUG: Need to review this with Kevin.
455
456
457 CLOSED ISSUES:
458
459 CHANGE HISTORY: 07/06 Ramana Y Mulpury
460 ===========================================================================*/
461
462
463 FUNCTION val_src_subinventory ( x_src_sub IN VARCHAR2,
464 x_src_org_id IN INTEGER,
465 x_dest_type IN VARCHAR2,
466 x_dest_org_id IN INTEGER,
467 x_dest_sub IN VARCHAR2,
468 x_item_id IN NUMBER
469 )
470 RETURN BOOLEAN;
471
472
473
474 /*===========================================================================
475 FUNCTION NAME: val_locator_control()
476
477 DESCRIPTION:
478 See if org/sub/item is under locator control. If it is then make
479 sure that the locator is specified accordingly: If locator control
480 is 2 which means it is under predefined locator contol or 3
481 which means it's under dynamic (any value)
482 locator control then you need to go get the default locator id
483
484 PARAMETERS:
485 X_to_organization_id IN NUMBER
486 X_item_id IN NUMBER
487 X_subinventory IN VARCHAR2
488 X_locator_id IN NUMBER
489 x_organization_id IN NUMBER
490
491 RETURN:
492 BOOLEAN - Is the locator valid for the sub destination
493
494 DESIGN REFERENCES: RCVRCERC.dd
495 RCVTXERT.dd
496
497 ALGORITHM:
498
499 NOTES:
500
501 OPEN ISSUES:
502
503 CLOSED ISSUES:
504
505 CHANGE HISTORY:
506 GK 06/02/95 Created Function
507 ===========================================================================*/
508
509 FUNCTION val_locator_control (
510 X_to_organization_id IN NUMBER,
511 X_item_id IN NUMBER,
512 X_subinventory IN VARCHAR2,
513 X_locator_id IN NUMBER)
514 RETURN BOOLEAN;
515
516
517 /*===========================================================================
518 PROCEDURE NAME: get_subinventory_details
519
520 DESCRIPTION: Obtain asset_inventory information
521 for the subinventory.
522
523
524 1 - asset subinventory
525 2 - non asset subinventory
526
527 PARAMETERS: x_subinventory IN OUT VARCHAR2
528 x_asset_inventory IN OUT NUMBER
529
530
531 DESIGN REFERENCES: POXRQERQ.doc
532
533
534 ALGORITHM:
535
536 NOTES:
537
538 OPEN ISSUES:
539
540
541 CLOSED ISSUES:
542
543 CHANGE HISTORY: 10/30 Ramana Y Mulpury
544 ===========================================================================*/
545
546 PROCEDURE get_subinventory_details (x_subinventory IN OUT NOCOPY VARCHAR2,
547 x_organization_id IN OUT NOCOPY NUMBER,
548 x_asset_inventory IN OUT NOCOPY NUMBER);
549
550
551
552 END PO_SUBINVENTORIES_S;
[Home] [Help]