1 PACKAGE INV_Validate AUTHID CURRENT_USER AS
2 /* $Header: INVSVATS.pls 120.7 2011/05/23 11:33:37 rdudani 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 /***Added overloaded function to pass the transaction_type_id, because
183 we are allowing delivery of PO receipt for expense items as part of this bug***/
184 FUNCTION inventory_item(p_item IN OUT nocopy item,
185 p_org IN org,
186 p_transaction_type IN NUMBER)RETURN NUMBER;--bug9267446
187
188 /** Validates locator in the context of an org and sub p_validation_mode
189 can be null. It is useful if one needs to validate the item using the
190 item-flexfield then one needs to give the mode of validation chosen
191 from the two modes listed above***/
192 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
193 p_org IN org,
194 p_sub IN sub,
195 p_validation_mode IN VARCHAR2 DEFAULT EXISTS_ONLY,
196 p_value_or_id IN VARCHAR2 DEFAULT 'V'
197 ) RETURN NUMBER;
198
199 /*** Validates locator in the context of an org,sub and a particular item ***/
200 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
201 p_org IN org,
202 p_sub IN SUB,
203 p_item IN item) RETURN NUMBER;
204
205 /* Bug# 6633612
206 * Added Overloaded validateLocator function for Material Status Enhancement Project
207 * This function would call the existing validateLocator function and then
208 * call inv_material_status_grp.is_status_applicable() API to check validity of status
209 * for this subinventory, locator for a given transaction type.
210 */
211 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
212 p_org IN org,
213 p_sub IN SUB,
214 p_item IN item,
215 p_trx_type_id IN NUMBER, -- For Bug# 6633612
216 p_object_type IN VARCHAR2 DEFAULT 'L' -- For Bug# 6633612
217 ) RETURN NUMBER;
218
219
220 /*** Validates a lot in the context of an org,sub,item,location and item revision ***/
221 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
222 p_org IN ORG,
223 p_item IN ITEM,
224 p_from_sub IN SUB,
225 p_loc in LOCATOR,
226 p_revision in VARCHAR)RETURN NUMBER;
227
228 /* Bug# 6633612
229 * Added Overloaded Lot_Number function for Material Status Enhancement Project
230 * This function would call the existing Lot_Number function and then
231 * call inv_material_status_grp.is_status_applicable() API to check validity of status
232 * for this subinventory, locator, lot for a given transaction type.
233 */
234 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
235 p_org IN ORG,
236 p_item IN ITEM,
237 p_from_sub IN SUB,
238 p_loc in LOCATOR,
239 p_revision in VARCHAR,
240 p_trx_type_id IN NUMBER, -- For Bug# 6633612
241 p_object_type IN VARCHAR2 DEFAULT 'O', -- For Bug# 6633612
242 p_lpn_id IN NUMBER DEFAULT NULL -- bug 12569798
243 )RETURN NUMBER;
244
245
246 /*** Validates a lot in the context of an org and item ***/
247 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
248 p_org IN ORG,
249 p_item IN ITEM)RETURN NUMBER;
250
251
252 /*** Validates a project ***/
253 FUNCTION Project(p_project_id IN NUMBER)RETURN NUMBER;
254
255 FUNCTION Quantity(p_quantity IN NUMBER)RETURN NUMBER;
256
257 FUNCTION Reason(p_reason_id IN NUMBER)RETURN NUMBER;
258
259 FUNCTION Reference(p_reference IN VARCHAR2)RETURN NUMBER;
260
261 FUNCTION Reference(p_reference_id IN NUMBER,
262 p_reference_type_code IN NUMBER) RETURN NUMBER;
263
264 FUNCTION Reference_Type(p_reference_type_code IN NUMBER)RETURN NUMBER;
265
266 /*** Validates revision of an item in the context of org and item ***/
267 FUNCTION Revision(p_revision IN VARCHAR2,
268 p_org IN ORG,
269 p_item IN ITEM)RETURN NUMBER;
270
271 /*** Validates serial numbers in context of org,item,sub,lot,loc. This is
272 mainly an internal routine used by other public api functions. So, avoid
273 using this. ***/
274 function check_serial(p_serial IN OUT nocopy serial,
275 p_org in ORG,
276 p_item IN ITEM,
277 p_from_sub IN sub,
278 p_lot in lot,
279 p_loc in locator,
280 p_revision in VARCHAR2,
281 p_msg IN VARCHAR2,
282 p_txn_type_id IN NUMBER DEFAULT NULL) RETURN NUMBER;
283
284 /*** Validates serial number in reference to org,item,sub,lot,loc***/
285 function validate_serial(p_serial IN OUT nocopy serial,
286 p_org in ORG,
287 p_item IN ITEM,
288 p_from_sub IN sub,
289 p_lot in lot,
290 p_loc in locator,
291 p_revision in VARCHAR2,
292 p_txn_type_id IN NUMBER DEFAULT NULL) RETURN NUMBER;
293
294 /* Bug# 6633612
295 * Added Overloaded validate_serial function for Material Status Enhancement Project
296 * This function would call the existing validate_serial function and then
297 * call inv_material_status_grp.is_status_applicable() API to check validity of status
298 * for this subinventory, locator, lot, serial for a given transaction type.
299 */
300 function validate_serial(p_serial IN OUT nocopy serial,
301 p_org in ORG,
302 p_item IN ITEM,
303 p_from_sub IN sub,
304 p_lot in lot,
305 p_loc in locator,
306 p_revision in VARCHAR2,
307 p_trx_type_id IN NUMBER, -- For Bug# 6633612
308 p_object_type IN VARCHAR2 DEFAULT 'S' -- For Bug# 6633612
309 ) RETURN NUMBER;
310
311 /* Bug# 6633612
312 * Added validate_serial_range function for Material Status Enhancement Project
313 * This function would call the existing validate_serial function and then
314 * call inv_material_status_grp.is_status_applicable() API to check validity of status
315 * for this subinventory, locator, lot, serial for a given transaction type.
316 */
320 p_item IN ITEM,
317 function validate_serial_range(p_fm_serial IN OUT nocopy SERIAL_NUMBER_TBL,
318 p_to_serial IN OUT nocopy SERIAL_NUMBER_TBL,
319 p_org in ORG,
321 p_from_sub IN sub,
322 p_lot in lot,
323 p_loc in locator,
324 p_revision in VARCHAR2,
325 p_trx_type_id IN NUMBER, -- For Bug# 6633612
326 p_object_type IN VARCHAR2 DEFAULT 'S', -- For Bug# 6633612
327 x_errored_serials OUT nocopy SERIAL_NUMBER_TBL -- For Bug# 6633612
328 ) RETURN NUMBER;
329
330 /*** Validates starting serial number in reference to org,item,sub,lot,loc***/
331 FUNCTION Serial_Number_End(p_serial IN OUT nocopy serial,
332 p_org in ORG,
333 p_item IN ITEM,
334 p_from_sub IN sub,
335 p_lot in lot,
336 p_loc in Locator,
337 p_revision in VARCHAR2) RETURN NUMBER;
338
339 /*** Validates ending serial number in reference to org,item,sub,lot,loc***/
340 FUNCTION Serial_Number_Start(p_serial IN OUT nocopy serial,
341 p_org IN org,
342 p_item in item,
343 p_from_sub in sub,
344 p_lot in lot,
345 p_loc in Locator,
346 p_revision in VARCHAR2)RETURN NUMBER;
347
348 FUNCTION subinventory(p_sub IN OUT nocopy sub,
349 p_org IN org) RETURN NUMBER;
350
351 FUNCTION subinventory(p_sub IN OUT nocopy sub,
352 p_org IN org,
353 p_item IN item)RETURN NUMBER;
354
355 FUNCTION Task(p_task_id IN NUMBER, p_project_id IN NUMBER)RETURN NUMBER;
356
357 /*** Validates to locator in the context of an org,item,sub,project,task
358 and a transaction_action ***/
359 FUNCTION To_Locator(p_locator IN OUT nocopy locator,
360 p_org IN ORG,
361 p_item IN ITEM,
362 p_to_sub IN SUB,
363 p_project_id IN NUMBER,
364 p_task_id IN NUMBER,
365 p_txn_action_id IN number)RETURN NUMBER;
366
367 FUNCTION Transaction_Header(p_transaction_header_id IN NUMBER)RETURN NUMBER;
368
369 FUNCTION HR_Location(p_hr_location IN NUMBER) RETURN NUMBER;
370
371 /*** Validates the txn uom in the context of an org and an item ***/
372 FUNCTION Uom(p_uom_code IN VARCHAR2,
373 p_org IN ORG,
374 p_item IN ITEM)RETURN NUMBER;
375
376 -- END GEN validate
377 PROCEDURE NUMBER_FROM_SEQUENCE (
378 p_sequence IN VARCHAR2,
379 x_prefix OUT NOCOPY VARCHAR2,
380 x_number OUT NOCOPY NUMBER
381 );
382
383 FUNCTION Cost_Group(p_cost_group_id IN NUMBER,
384 p_org_id IN NUMBER) return NUMBER;
385
386 FUNCTION LPN(p_lpn_id IN NUMBER) RETURN NUMBER;
387 --INVCONV
388 FUNCTION Secondary_Quantity(p_secondary_quantity IN NUMBER)RETURN NUMBER;
389 --INVCONV
390
391 --Start of new code added as part of eIB Build. Bug# 4348541
392 PROCEDURE check_pending_transaction(
393 p_transaction_type_id IN NUMBER,
394 p_pending_tran_flag OUT NOCOPY NUMBER);
395 PROCEDURE check_location_required_setup(
396 p_transaction_type_id IN NUMBER,
397 p_required_flag OUT NOCOPY VARCHAR2);
398 --End of new code added as part of eIB Build. Bug# 4348541
399
400 END INV_Validate;
[Home] [Help]