1 PACKAGE INV_Validate AS
2 /* $Header: INVSVATS.pls 120.3 2008/01/10 11:07:03 sdpaul ship $ */
3
4 -- Procedure Get_Attr_Tbl;
5 --
6 -- Used by generator to avoid overriding or duplicating existing
7 -- validation functions.
8 --
9 -- DO NOT MODIFY
10
11 PROCEDURE Get_Attr_Tbl;
12
13 -- Prototypes for validate functions.
14
15 -- START GEN validate
16
17 -- Generator will append new prototypes before end generate comment.
18
19 T CONSTANT NUMBER := 1;
20 F CONSTANT NUMBER := 0;
21
22 /*** Various row types for the IN OUT parameters ***/
23 SUBTYPE ORG IS MTL_PARAMETERS%ROWTYPE;
24 SUBTYPE ITEM IS MTL_SYSTEM_ITEMS%ROWTYPE;
25 SUBTYPE SUB IS MTL_SECONDARY_INVENTORIES%ROWTYPE;
26 SUBTYPE LOCATOR IS MTL_ITEM_LOCATIONS%ROWTYPE;
27 SUBTYPE LOT IS MTL_LOT_NUMBERS%ROWTYPE;
28 SUBTYPE SERIAL IS MTL_SERIAL_NUMBERS%ROWTYPE;
29 SUBTYPE transaction IS mtl_transaction_types%ROWTYPE;
30
31 /* Added the below types for Bug# 6633612
32 */
33 TYPE SERIAL_NUMBER_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
34
35 g_kf_segment_values FND_FLEX_EXT.SegmentArray;
36 EXISTS_ONLY CONSTANT VARCHAR2(20) := 'FIND_COMBINATION';
37
38 /* Bug# 3595460. Changed the operation CREATE_COMBINATION to CREATE_COMB_NO_AT */
39 --EXISTS_OR_CREATE CONSTANT VARCHAR2(20) := 'CREATE_COMBINATION';
40 EXISTS_OR_CREATE CONSTANT VARCHAR2(20) := 'CREATE_COMB_NO_AT';
41
42 /***generate the concatenated segment given the application short name like
43 'INV' OR 'FND' AND the key flex field code LIKE 'MTLL' and the structure
44 NUMBER LIKE 101 ***/
45 FUNCTION concat_segments(p_appl_short_name IN VARCHAR2,
46 p_key_flex_code IN VARCHAR2,
47 p_structure_number IN NUMBER) RETURN VARCHAR2;
48
49 FUNCTION Desc_Flex ( p_flex_name IN VARCHAR2 )RETURN NUMBER;
50
51 function check_creation_updation(p_created_updated_by in number,
52 p_is_creation in number)return NUMBER;
53
54 FUNCTION Created_By(p_created_by IN NUMBER)RETURN NUMBER;
55
56 function check_date(p_date in date, p_msg in varchar2)return NUMBER;
57
58 FUNCTION Creation_Date(p_creation_date IN DATE)RETURN NUMBER;
59
60 -- Bug 4373226 added parameter transaction_date for
61 -- checking the conversion rate on the basis of
62 -- transaction date but not on sysdate
63 FUNCTION conversion_rate(from_org IN NUMBER,
64 to_org IN NUMBER, transaction_date DATE DEFAULT SYSDATE) RETURN NUMBER;
65
66
67 FUNCTION Description(p_description IN VARCHAR2)RETURN NUMBER;
68
69 /*** validates employee based on either employee id or the employee
70 name. If need to validate based on employee name then employee id should be
71 null. It returns F if all three are null. Also, if name results in multiple
72 records, gives a error asking for employee id. Passing any of the three
73 will return the other two if the validation is successful.
74 ***/
75 FUNCTION Employee(p_employee_id IN OUT NOCOPY NUMBER,
76 p_last_name IN OUT NOCOPY VARCHAR2,
77 p_full_name IN OUT NOCOPY VARCHAR2,
78 p_org IN org)RETURN NUMBER;
79
80 /*** Validates a from subinventory in the context of an org and item.
81 if it is an account transfer then p_acct_txn should be 1. else 0.***/
82 FUNCTION From_Subinventory(p_sub IN OUT NOCOPY SUB,
83 p_org IN ORG,
84 p_item IN ITEM,
85 p_acct_txn IN NUMBER)RETURN NUMBER;
86
87 /* Bug# 6633612
88 * Added Overloaded From_Subinventory function for Material Status Enhancement Project
89 * This function would call the existing From_Subinventory function and then
90 * call inv_material_status_grp.is_status_applicable() API to check validity of status
91 * for this subinventory for a given transaction type.
92 */
93 FUNCTION From_Subinventory(p_sub IN OUT NOCOPY SUB,
94 p_org IN ORG,
95 p_item IN ITEM,
96 p_acct_txn IN NUMBER,
97 p_trx_type_id IN NUMBER, -- For Bug# 6633612
98 p_object_type IN VARCHAR2 DEFAULT 'Z'-- For Bug# 6633612
99 )RETURN NUMBER;
100
101
102 FUNCTION Last_Updated_By(p_last_updated_by IN NUMBER)RETURN NUMBER;
103
104 FUNCTION Last_Update_Date(p_last_update_date IN DATE)RETURN NUMBER;
105
106 FUNCTION Last_Update_Login(p_last_update_login IN NUMBER)RETURN NUMBER;
107
108 /*** Validates organization. ***/
109 FUNCTION Organization(p_org IN OUT nocopy ORG)RETURN NUMBER;
110
111 FUNCTION Program_Application(p_program_application_id IN NUMBER)RETURN NUMBER;
112
113 FUNCTION Program(p_program_id IN NUMBER)RETURN NUMBER;
114
115 FUNCTION Program_Update_Date(p_program_update_date IN DATE)RETURN NUMBER;
116
117 FUNCTION To_Account(p_to_account_id IN NUMBER)RETURN NUMBER;
118
119 /*** Validates a from subinventory in the context of an org and item.
120 if it is an account transfer then p_acct_txn should be 1. else 0.***/
121 FUNCTION To_Subinventory(p_sub IN OUT NOCOPY SUB,
122 p_org IN ORG,
123 p_item IN ITEM,
124 p_from_sub IN SUB,
125 p_acct_txn IN NUMBER)RETURN NUMBER;
126
127 /* Bug# 6633612
128 * Added Overloaded To_Subinventory function for Material Status Enhancement Project
129 * This function would call the existing To_Subinventory function and then
130 * call inv_material_status_grp.is_status_applicable() API to check validity of status
131 * for this subinventory for a given transaction type.
132 */
133 FUNCTION To_Subinventory(p_sub IN OUT NOCOPY SUB,
134 p_org IN ORG,
135 p_item IN ITEM,
136 p_from_sub IN SUB,
137 p_acct_txn IN NUMBER,
138 p_trx_type_id IN NUMBER, -- For Bug# 6633612
139 p_object_type IN VARCHAR2 DEFAULT 'Z' -- For Bug# 6633612
140 )RETURN NUMBER;
141
142
143 /*** Validates a given transaction_type_id and if valid returns the corresponding
144 transaction_action_id and transaction_source_type_id***/
145 FUNCTION Transaction_Type(p_transaction_type_id IN NUMBER,
146 x_transaction_action_id OUT NOCOPY NUMBER,
147 x_transaction_source_type_id OUT NOCOPY NUMBER) RETURN NUMBER;
148
149 /*** Validates a given transaction_type ***/
150 FUNCTION transaction_type(x_transaction IN OUT nocopy transaction)RETURN
151 NUMBER;
152
153 /*** Validates locator in context of org,item,sub. This is mainly an internal
154 routine used by other public api functions. So, avoid using this. ***/
155 function check_locator(p_locator IN OUT nocopy locator,
156 p_org IN ORG,
157 p_item IN ITEM,
158 p_sub IN SUB,
159 p_project_id IN NUMBER,
160 p_task_id IN NUMBER,
161 p_txn_action_id IN number,
162 p_is_from_locator in NUMBER,
163 p_dynamic_ok IN BOOLEAN)RETURN NUMBER;
164
165 /*** Validates from locator in the context of an org,item,sub,project,task
166 and a transaction_action ***/
167 FUNCTION From_Locator(p_locator IN OUT nocopy locator,
168 p_org IN ORG,
169 p_item IN ITEM,
170 p_from_sub IN SUB,
171 p_project_id IN NUMBER,
172 p_task_id IN NUMBER,
173 p_txn_action_id IN number
174 )RETURN NUMBER;
175
176 /*** Validates item in context of an org. p_validation_mode can be null. It
177 is useful if one needs to validate the item using the item-flexfield
178 then one needs to give the mode of validation chosen from the two
179 modes listed above***/
180 FUNCTION inventory_item(p_item IN OUT nocopy item,
181 p_org IN org)RETURN NUMBER;
182
183 /** Validates locator in the context of an org and sub p_validation_mode
184 can be null. It is useful if one needs to validate the item using the
185 item-flexfield then one needs to give the mode of validation chosen
186 from the two modes listed above***/
187 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
188 p_org IN org,
189 p_sub IN sub,
190 p_validation_mode IN VARCHAR2 DEFAULT EXISTS_ONLY,
191 p_value_or_id IN VARCHAR2 DEFAULT 'V'
192 ) RETURN NUMBER;
193
194 /*** Validates locator in the context of an org,sub and a particular item ***/
195 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
196 p_org IN org,
197 p_sub IN SUB,
198 p_item IN item) RETURN NUMBER;
199
200 /* Bug# 6633612
201 * Added Overloaded validateLocator function for Material Status Enhancement Project
202 * This function would call the existing validateLocator function and then
203 * call inv_material_status_grp.is_status_applicable() API to check validity of status
204 * for this subinventory, locator for a given transaction type.
205 */
206 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
207 p_org IN org,
208 p_sub IN SUB,
209 p_item IN item,
210 p_trx_type_id IN NUMBER, -- For Bug# 6633612
211 p_object_type IN VARCHAR2 DEFAULT 'L' -- For Bug# 6633612
212 ) RETURN NUMBER;
213
214
215 /*** Validates a lot in the context of an org,sub,item,location and item revision ***/
216 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
217 p_org IN ORG,
218 p_item IN ITEM,
219 p_from_sub IN SUB,
220 p_loc in LOCATOR,
221 p_revision in VARCHAR)RETURN NUMBER;
222
223 /* Bug# 6633612
224 * Added Overloaded Lot_Number function for Material Status Enhancement Project
225 * This function would call the existing Lot_Number function and then
226 * call inv_material_status_grp.is_status_applicable() API to check validity of status
227 * for this subinventory, locator, lot for a given transaction type.
228 */
229 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
230 p_org IN ORG,
231 p_item IN ITEM,
232 p_from_sub IN SUB,
233 p_loc in LOCATOR,
234 p_revision in VARCHAR,
235 p_trx_type_id IN NUMBER, -- For Bug# 6633612
236 p_object_type IN VARCHAR2 DEFAULT 'O' -- For Bug# 6633612
237 )RETURN NUMBER;
238
239
240 /*** Validates a lot in the context of an org and item ***/
241 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
242 p_org IN ORG,
243 p_item IN ITEM)RETURN NUMBER;
244
245
246 /*** Validates a project ***/
247 FUNCTION Project(p_project_id IN NUMBER)RETURN NUMBER;
248
249 FUNCTION Quantity(p_quantity IN NUMBER)RETURN NUMBER;
250
251 FUNCTION Reason(p_reason_id IN NUMBER)RETURN NUMBER;
252
253 FUNCTION Reference(p_reference IN VARCHAR2)RETURN NUMBER;
254
255 FUNCTION Reference(p_reference_id IN NUMBER,
256 p_reference_type_code IN NUMBER) RETURN NUMBER;
257
258 FUNCTION Reference_Type(p_reference_type_code IN NUMBER)RETURN NUMBER;
259
260 /*** Validates revision of an item in the context of org and item ***/
261 FUNCTION Revision(p_revision IN VARCHAR2,
262 p_org IN ORG,
263 p_item IN ITEM)RETURN NUMBER;
264
265 /*** Validates serial numbers in context of org,item,sub,lot,loc. This is
266 mainly an internal routine used by other public api functions. So, avoid
267 using this. ***/
268 function check_serial(p_serial IN OUT nocopy serial,
269 p_org in ORG,
270 p_item IN ITEM,
271 p_from_sub IN sub,
272 p_lot in lot,
273 p_loc in locator,
274 p_revision in VARCHAR2,
275 p_msg IN VARCHAR2) RETURN NUMBER;
276
277 /*** Validates serial number in reference to org,item,sub,lot,loc***/
278 function validate_serial(p_serial IN OUT nocopy serial,
279 p_org in ORG,
280 p_item IN ITEM,
281 p_from_sub IN sub,
282 p_lot in lot,
283 p_loc in locator,
284 p_revision in VARCHAR2) RETURN NUMBER;
285
286 /* Bug# 6633612
287 * Added Overloaded validate_serial function for Material Status Enhancement Project
288 * This function would call the existing validate_serial function and then
289 * call inv_material_status_grp.is_status_applicable() API to check validity of status
290 * for this subinventory, locator, lot, serial for a given transaction type.
291 */
292 function validate_serial(p_serial IN OUT nocopy serial,
293 p_org in ORG,
294 p_item IN ITEM,
295 p_from_sub IN sub,
296 p_lot in lot,
297 p_loc in locator,
298 p_revision in VARCHAR2,
299 p_trx_type_id IN NUMBER, -- For Bug# 6633612
300 p_object_type IN VARCHAR2 DEFAULT 'S' -- For Bug# 6633612
301 ) RETURN NUMBER;
302
303 /* Bug# 6633612
304 * Added validate_serial_range function for Material Status Enhancement Project
305 * This function would call the existing validate_serial function and then
306 * call inv_material_status_grp.is_status_applicable() API to check validity of status
307 * for this subinventory, locator, lot, serial for a given transaction type.
308 */
309 function validate_serial_range(p_fm_serial IN OUT nocopy SERIAL_NUMBER_TBL,
310 p_to_serial IN OUT nocopy SERIAL_NUMBER_TBL,
311 p_org in ORG,
312 p_item IN ITEM,
313 p_from_sub IN sub,
314 p_lot in lot,
315 p_loc in locator,
316 p_revision in VARCHAR2,
317 p_trx_type_id IN NUMBER, -- For Bug# 6633612
318 p_object_type IN VARCHAR2 DEFAULT 'S', -- For Bug# 6633612
319 x_errored_serials OUT nocopy SERIAL_NUMBER_TBL -- For Bug# 6633612
320 ) RETURN NUMBER;
321
322 /*** Validates starting serial number in reference to org,item,sub,lot,loc***/
323 FUNCTION Serial_Number_End(p_serial IN OUT nocopy serial,
324 p_org in ORG,
325 p_item IN ITEM,
326 p_from_sub IN sub,
327 p_lot in lot,
328 p_loc in Locator,
329 p_revision in VARCHAR2) RETURN NUMBER;
330
331 /*** Validates ending serial number in reference to org,item,sub,lot,loc***/
332 FUNCTION Serial_Number_Start(p_serial IN OUT nocopy serial,
333 p_org IN org,
334 p_item in item,
335 p_from_sub in sub,
336 p_lot in lot,
337 p_loc in Locator,
338 p_revision in VARCHAR2)RETURN NUMBER;
339
340 FUNCTION subinventory(p_sub IN OUT nocopy sub,
341 p_org IN org) RETURN NUMBER;
342
343 FUNCTION subinventory(p_sub IN OUT nocopy sub,
344 p_org IN org,
345 p_item IN item)RETURN NUMBER;
346
347 FUNCTION Task(p_task_id IN NUMBER, p_project_id IN NUMBER)RETURN NUMBER;
348
349 /*** Validates to locator in the context of an org,item,sub,project,task
350 and a transaction_action ***/
351 FUNCTION To_Locator(p_locator IN OUT nocopy locator,
352 p_org IN ORG,
353 p_item IN ITEM,
354 p_to_sub IN SUB,
355 p_project_id IN NUMBER,
356 p_task_id IN NUMBER,
357 p_txn_action_id IN number)RETURN NUMBER;
358
359 FUNCTION Transaction_Header(p_transaction_header_id IN NUMBER)RETURN NUMBER;
360
361 FUNCTION HR_Location(p_hr_location IN NUMBER) RETURN NUMBER;
362
363 /*** Validates the txn uom in the context of an org and an item ***/
364 FUNCTION Uom(p_uom_code IN VARCHAR2,
365 p_org IN ORG,
366 p_item IN ITEM)RETURN NUMBER;
367
368 -- END GEN validate
369 PROCEDURE NUMBER_FROM_SEQUENCE (
370 p_sequence IN VARCHAR2,
371 x_prefix OUT NOCOPY VARCHAR2,
372 x_number OUT NOCOPY NUMBER
373 );
374
375 FUNCTION Cost_Group(p_cost_group_id IN NUMBER,
376 p_org_id IN NUMBER) return NUMBER;
377
378 FUNCTION LPN(p_lpn_id IN NUMBER) RETURN NUMBER;
379 --INVCONV
380 FUNCTION Secondary_Quantity(p_secondary_quantity IN NUMBER)RETURN NUMBER;
381 --INVCONV
382
383 --Start of new code added as part of eIB Build. Bug# 4348541
384 PROCEDURE check_pending_transaction(
385 p_transaction_type_id IN NUMBER,
386 p_pending_tran_flag OUT NOCOPY NUMBER);
387 PROCEDURE check_location_required_setup(
388 p_transaction_type_id IN NUMBER,
389 p_required_flag OUT NOCOPY VARCHAR2);
390 --End of new code added as part of eIB Build. Bug# 4348541
391
392 END INV_Validate;