1 PACKAGE BODY BOM_Validate AS
2 /* $Header: BOMSVATB.pls 120.3 2006/05/31 08:25:05 vhymavat noship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMSVATB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 01-JUL-99 Rahul Chitko Initial Creation
21 --
22 -- 08-MAY-01 Refai Farook EAM changes
23 --
24 -- 08-JUL-01 Refai Farook Support for '0' serial number(EAM changes)
25 --
26 -- 04-SEP-01 Refai Farook One To Many support changes
27 ****************************************************************************/
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Bom_Validate';
29
30 /********************************************************************
31 * Function : Alternate_Designator
32 * Returns : Boolean
33 * Parameters IN : Alternate_bom_code
34 * Organization_id
35 * Parameters OUT: None
36 * Purpose : Function will verify if the alternate bom
37 * designator exits. If it does then the function wil
38 * return a TRUE otherwise a FALSE.
39 *********************************************************************/
40 FUNCTION Alternate_Designator( p_alternate_bom_code IN VARCHAR2
41 , p_organization_id IN NUMBER
42 ) RETURN BOOLEAN
43 IS
44 l_dummy NUMBER;
45 BEGIN
46 SELECT 1
47 INTO l_dummy
48 FROM bom_alternate_designators
49 WHERE alternate_designator_code = p_alternate_bom_code
50 AND organization_id = p_organization_id;
51
52 RETURN TRUE;
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 RETURN FALSE;
57
58 END Alternate_Designator;
59
60
61 /*******************************************************************
62 * Function : End_Item_Unit_Number
63 * Parameters IN : p_From_End_Item_Unit_Number
64 * Parameters OUT: Error Text which will be pouplated in case of an
65 * unexpected error.
66 *
67 * Return : True if the from end item unit number is valid else
68 * False
69 * Purpose : Verify that the from end item unit number exists
70 * in the table PJM_MODEL_UNIT_NUMBERS.
71 *******************************************************************/
72 FUNCTION End_Item_Unit_Number
73 ( p_from_end_item_unit_number IN VARCHAR2
74 , p_revised_item_id IN VARCHAR2
75 , x_err_text IN OUT NOCOPY VARCHAR2 )
76 RETURN BOOLEAN
77 IS
78 l_dummy VARCHAR2(10);
79 l_err_text VARCHAR2(2000) := NULL;
80 BEGIN
81
82 IF p_from_end_item_unit_number IS NULL OR
83 p_from_end_item_unit_number = FND_API.G_MISS_CHAR
84 THEN
85 RETURN TRUE;
86 END IF;
87
88 SELECT 'VALID'
89 INTO l_dummy
90 FROM pjm_unit_numbers
91 WHERE
92 -- end_item_id = p_revised_item_id AND
93 unit_number = p_from_end_item_unit_number;
94
95 RETURN TRUE;
96
97 EXCEPTION
98
99 WHEN NO_DATA_FOUND THEN
100 RETURN FALSE;
101
102 WHEN OTHERS THEN
103 x_err_text :=
104 'An unexpected error occured in ' ||
105 G_PKG_NAME ||
106 ' and procedure From End Item Unit Number'
107 || SQLERRM ;
108
109 RETURN FALSE;
110
111 END End_Item_Unit_Number;
112
113
114 /********************************************************************
115 * Function : Wip_Supply_Type
116 * Parameters IN : Wip_Supply_Type value
117 * Parameters OUT: Error Text which will be populated in case of an
118 * unexpected error.
119 * Returns : True if the Wip_supply_Type exist else False
120 * Purpose : Verify that the value of Wip_Supply_Type is valid,
121 * by looking in the Table MFG_LOOKUPS with a Lookup
122 * Type of 'WIP_SUPPLY'
123 *********************************************************************/
124 FUNCTION Wip_Supply_Type ( p_wip_supply_type IN NUMBER
125 , x_err_text IN OUT NOCOPY VARCHAR2 )
126 RETURN BOOLEAN
127 IS
128 l_dummy VARCHAR2(10);
129 BEGIN
130
131 IF p_wip_supply_type IS NULL OR
132 p_wip_supply_type = FND_API.G_MISS_NUM
133 THEN
134 RETURN TRUE;
135 END IF;
136
137 SELECT 'VALID'
138 INTO l_dummy
139 FROM mfg_lookups
140 WHERE lookup_code = p_wip_supply_type
141 AND lookup_type = 'WIP_SUPPLY' ;
142
143 RETURN TRUE;
144
145 EXCEPTION
146
147 WHEN NO_DATA_FOUND THEN
148 RETURN FALSE;
149
150 WHEN OTHERS THEN
151 x_err_text :=
152 'An unexpected error occured in ' || G_PKG_NAME ||
153 ' and function Wip_Supply_Type' || SQLERRM ;
154
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156
157 END Wip_Supply_Type;
158
159 /*******************************************************************
160 * Function : Asset_Group_Serial_Number
161 * Parameters IN : p_Assembly_Item_Id
162 * Parameters OUT: Error Text which will be pouplated in case of an
163 * unexpected error.
164 *
165 * Return : True if the serial number is valid else
166 * False
167 * Purpose : Verify that the serial number is valid for the
168 * asset group in the table MTL_SERIAL_NUMBERS.
169 *******************************************************************/
170
171 FUNCTION Asset_Group_Serial_Number
172 ( p_assembly_item_id IN NUMBER,
173 p_organization_id IN NUMBER,
174 p_serial_number IN VARCHAR2,
175 x_err_text IN OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
176
177 l_dummy VARCHAR2(1);
178
179 BEGIN
180
181 IF p_assembly_item_id IS NULL OR p_serial_number IS NULL THEN
182 RETURN (FALSE);
183 END IF;
184
185 IF p_serial_number = '0' THEN
186 RETURN (TRUE);
187 END IF;
188
189 SELECT 'x' INTO l_dummy FROM dual WHERE EXISTS (
190 SELECT NULL FROM mtl_serial_numbers WHERE
191 inventory_item_id = p_assembly_item_id AND
192 current_organization_id = p_organization_id AND
193 serial_number = p_serial_number);
194
195 RETURN (TRUE);
196
197 EXCEPTION WHEN NO_DATA_FOUND THEN
198 RETURN (FALSE);
199
200 WHEN OTHERS THEN
201 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
202 ' and function Asset_Group_Serial_Number' || SQLERRM ;
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204
205 END Asset_Group_Serial_Number;
206
207
208 /*******************************************************************
209 * Function : Is_Preferred_Structure
210 * Parameters IN : p_Assembly_Item_Id
211 * p_organization_id
212 * p_alternate_bom_code
213 * Parameters OUT: Error Text which will be pouplated in case of an
214 * unexpected error.
215 *
216 * Return : 'Y' if there exists no other preferred structure
217 * of that type.
218 * 'N' if there exists another preferred structure
219 * Purpose :
220 *******************************************************************/
221
222 FUNCTION Is_Preferred_Structure
223 ( p_assembly_item_id IN NUMBER,
224 p_organization_id IN NUMBER,
225 p_alternate_bom_code IN VARCHAR2,
226 x_err_text IN OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
227
228 l_dummy VARCHAR2(1);
229
230 BEGIN
231 IF p_assembly_item_id IS NULL OR p_organization_id IS NULL THEN
232 RETURN 'N' ;
233 END IF;
234
235 SELECT 'x' INTO l_dummy FROM dual WHERE EXISTS (
236 select NULL from bom_bill_of_materials a, bom_alternate_designators b
237 where a.assembly_item_id = p_assembly_item_id
238 and a.organization_id = p_organization_id
239 and a.is_preferred = 'Y'
240 and a.structure_type_id = b.structure_type_id
241 and b.alternate_designator_code = p_alternate_bom_code );
242
243 RETURN 'N';
244
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 RETURN 'Y';
248
249 WHEN OTHERS THEN
250 x_err_text := 'An unexpected error occured in ' || G_PKG_NAME ||
251 ' and function Is Preferred Structure' || SQLERRM ;
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253
254 END Is_Preferred_Structure;
255
256 /*******************************************************************
257 * Function : Object_Exists
258 * Parameters IN : p_object_type
259 * p_object_name
260 *
261 * Return : 'Y' if there exists no other preferred structure
262 * of that type.
263 * 'N' if there exists another preferred structure
264 * Purpose : checks for the existence of object in database.
265 *******************************************************************/
266
267 FUNCTION Object_Exists(p_object_type VARCHAR2,p_object_name VARCHAR2)
268 RETURN VARCHAR2 IS
269 l_exists VARCHAR2(1) := 'N';
270 schema_name VARCHAR2(5):='APPS';
271 CURSOR c_check_object(cp_object_type VARCHAR2, cp_object_name VARCHAR2)IS
272 SELECT 'Y'
273 FROM all_objects
274 WHERE object_type = cp_object_type
275 AND owner = schema_name
276 AND object_name = cp_object_name
277 AND status = 'VALID';
278 BEGIN
279 OPEN c_check_object(cp_object_type => p_object_type
280 ,cp_object_name => p_object_name);
281
282 FETCH c_check_object INTO l_exists;
283 CLOSE c_check_object;
284
285 RETURN l_exists;
286 END Object_Exists;
287
288 END Bom_Validate;
[Home] [Help]