[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_BOM_REVISION
Source
1 PACKAGE BODY BOM_Validate_Bom_Revision AS
2 /* $Header: BOMLREVB.pls 120.0 2005/05/25 04:57:08 appldev noship $ */
3 /*************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLCMPS.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Bom_Revision
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUL-99 Rahul Chitko Initial Creation
21 --
22 -- 08-MAY-2001 Refai Farook EAM related changes
23 --
24 **************************************************************************/
25
26 PROCEDURE Check_Entity
27 ( x_return_status IN OUT NOCOPY VARCHAR2
28 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
29 , p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
30 , p_bom_rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
31 , p_old_bom_revision_Rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
32 , p_old_bom_Rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
33 )
34 IS
35 CURSOR c_Get_Revision IS
36 SELECT revision, effectivity_date
37 FROM mtl_item_revisions
38 WHERE inventory_item_id = p_bom_rev_unexp_rec.assembly_item_id
39 AND organization_id = p_bom_rev_unexp_rec.organization_id
40 ORDER BY effectivity_date desc, revision desc;
41
42 l_current_rev VARCHAR2(3);
43 l_current_rev_date date;
44 l_token_tbl Error_Handler.Token_Tbl_Type;
45 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
46 l_dummy NUMBER;
47 BEGIN
48 --
49 -- Check if the user has entered a revision that is greater than
50 -- the most current revision. If not then it is an error
51 --
52 IF p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE
53 THEN
54 OPEN c_Get_Revision;
55 FETCH c_Get_Revision INTO l_current_rev, l_current_rev_date;
56
57 IF (l_current_rev is not null and
58 p_bom_revision_rec.revision <= l_current_rev)
59
60 THEN
61 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
62 l_token_tbl(1).token_value :=
63 p_bom_revision_rec.assembly_item_name;
64 l_token_tbl(2).token_name := 'REVISION';
65 l_token_tbl(2).token_value := p_bom_revision_rec.revision;
66 l_token_tbl(3).token_name := 'CURRENT_REVISION';
67 l_token_tbl(3).token_value := l_current_rev;
68
69 Error_Handler.Add_Error_Token
70 ( p_message_name => 'BOM_NEXT_REVISION'
71 , x_mesg_token_tbl => l_mesg_token_tbl
72 , p_token_tbl => l_token_tbl
73 );
74 x_return_status := FND_API.G_RET_STS_ERROR;
75 END IF;
76
77
78 IF (Bom_Globals.get_caller_type()= 'MIGRATION' AND
79 NVL(p_bom_revision_rec.start_effective_date, SYSDATE) < l_current_rev_date AND
80 NVL(p_bom_revision_rec.start_effective_date, SYSDATE) < SYSDATE )
81
82 THEN
83
84 Error_Handler.Add_Error_Token
85 ( p_message_name => 'INV_ITM_REV_OUT_EFF_DATE'
86 , x_mesg_token_tbl => l_mesg_token_tbl
87 , p_token_tbl => l_token_tbl
88 );
89 x_return_status := FND_API.G_RET_STS_ERROR;
90 END IF;
91
92
93 --
94 -- If the user is attempting to create a new revision, then the
95 -- bill for the item must exist for the user to be able to create a
96 -- revision through BOM
97
98 --
99 -- If the user is attempting to create a new revision, then the
100 -- bill for the item must exist for the user to be able to create a
101 -- revision through BOM
102 --
103 BEGIN
104 SELECT 1
105 INTO l_dummy
106 FROM SYS.DUAL
107 WHERE EXISTS ( SELECT bill_sequence_id
108 FROM bom_bill_of_materials
109 WHERE assembly_item_id
110 = p_bom_rev_Unexp_Rec.assembly_item_id
111 AND organization_id
112 = p_bom_rev_Unexp_Rec.organization_id
113 ) ;
114
115
116 /* Comment out due to an error when the SQL return multiple rec.
117 SELECT bill_sequence_id
118 INTO l_dummy
119 FROM bom_bill_of_materials
120 WHERE assembly_item_id = p_bom_rev_Unexp_Rec.assembly_item_id
121 AND organization_id = p_bom_rev_Unexp_Rec.organization_id;
122 */
123
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 l_token_tbl(1).token_name :=
128 'ASSEMBLY_ITEM_NAME';
129 l_token_tbl(1).token_value :=
130 p_bom_revision_rec.assembly_item_name;
131 Error_Handler.Add_Error_Token
132 ( p_mesg_token_tbl => l_mesg_token_tbl
133 ,x_mesg_token_tbl => l_mesg_token_tbl
134 ,p_message_name => 'BOM_REV_BILL_MISS'
135 ,p_token_tbl => l_token_tbl
136 );
137
138 END;
139 END IF;
140
141 -- If the user is attempting to create or update effective date of the
142 -- revision and the date is less than the current date then it should get
143 -- an error.
144 --Error_Handler.Write_Debug( 'Current '||to_char(p_bom_revision_rec.start_effective_date));
145 --Error_Handler.Write_Debug(' Old '||to_char(p_old_bom_revision_rec.start_effective_date));
146 --Error_Handler.Write_Debug(p_bom_revision_rec.transaction_type );
147 IF ( p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE AND
148 Bom_Globals.get_caller_type()<> 'MIGRATION' AND -- bug 2869453
149 NVL(p_bom_revision_rec.start_effective_date, SYSDATE) < SYSDATE
150 ) OR
151 ( p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_UPDATE AND
152 p_old_bom_revision_Rec.start_effective_date <>
153 p_bom_revision_rec.start_effective_date AND
154 ( NVL(p_bom_revision_rec.start_effective_date,SYSDATE) < SYSDATE
155 OR p_old_bom_revision_Rec.start_effective_date < SYSDATE )
156 )
157 THEN
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 l_token_tbl(1).token_name := 'REVISION';
160 l_token_tbl(1).token_value :=
161 p_bom_revision_rec.revision;
162 l_token_tbl(2).token_name := 'OLD_EFFECTIVE_DATE';
163 l_token_tbl(2).token_value :=
164 to_char(p_old_bom_revision_rec.start_effective_date);
165 l_token_tbl(3).token_name := 'START_EFFECTIVE_DATE';
166 l_token_tbl(3).token_value :=
167 to_char(p_bom_revision_rec.start_effective_date);
168 l_token_tbl(4).token_name := 'ASSEMBLY_ITEM_NAME';
169 l_token_tbl(4).token_value := p_bom_revision_rec.assembly_item_name;
170 Error_Handler.Add_Error_Token
171 ( p_message_name => 'BOM_REV_START_DATE_LESS_CURR'
172 , p_mesg_token_tbl => l_mesg_token_tbl
173 , x_mesg_token_tbl => l_mesg_token_tbl
174 , p_token_tbl => l_token_tbl
175 );
176 END IF;
177
178 x_mesg_token_tbl := l_mesg_token_tbl;
179
180 END Check_Entity;
181
182 PROCEDURE Check_Required
183 ( x_return_status IN OUT NOCOPY VARCHAR2
184 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
185 , p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
186 )
187 IS
188 BEGIN
189 IF p_bom_revision_rec.revision IS NULL OR
190 p_bom_revision_rec.revision = FND_API.G_MISS_CHAR
191 THEN
192 Error_Handler.Add_Error_Token
193 ( p_message_name => 'BOM_REVISION_REQUIRED'
194 , p_message_text => NULL
195 , x_mesg_token_tbl => x_mesg_token_tbl
196 );
197
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 END IF;
200
201 END Check_Required;
202
203
204 PROCEDURE Check_Existence
205 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_revision_Rec_Type
206 , p_bom_rev_unexp_rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
207 , x_old_bom_revision_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Revision_Rec_Type
208 , x_old_bom_rev_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
209 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
210 , x_return_status IN OUT NOCOPY VARCHAR2
211 )
212 IS
213 l_return_status VARCHAR2(1);
214 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
215 l_token_tbl Error_Handler.Token_Tbl_Type;
216 BEGIN
217 Bom_Bom_Revision_Util.Query_Row
218 ( p_revision => p_bom_revision_rec.revision
219 , p_assembly_item_id => p_bom_rev_unexp_rec.assembly_item_id
220 , p_organization_id => p_bom_rev_unexp_rec.organization_id
221 , x_bom_revision_rec => x_old_bom_revision_rec
222 , x_bom_rev_unexp_rec => x_old_bom_rev_unexp_rec
223 , x_return_status => l_return_status
224 );
225
226
227 l_token_tbl(1).token_name := 'REVISION';
228 l_token_tbl(1).token_value := p_bom_revision_rec.revision;
229 l_token_tbl(2).token_name := 'ASSEMBLY_ITEM_NAME';
230 l_token_tbl(2).token_value := p_bom_revision_rec.assembly_item_name;
231
232 IF l_return_status = Bom_Globals.G_RECORD_FOUND AND
233 p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE
234 THEN
235 Error_Handler.Add_Error_Token
236 ( p_message_name => 'BOM_REVISION_ALREADY_EXISTS'
237 , p_message_text => NULL
238 , p_token_tbl => l_token_tbl
239 , p_mesg_token_tbl => l_mesg_token_tbl
240 , x_mesg_token_tbl => l_mesg_token_tbl
241 );
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 ELSIF l_return_status = BOM_Globals.G_RECORD_NOT_FOUND AND
244 p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
245 THEN
246 Error_Handler.Add_Error_Token
247 ( p_message_name => 'BOM_REVISION_DOESNOT_EXIST'
248 , p_message_text => NULL
249 , p_mesg_token_tbl => l_mesg_token_tbl
250 , p_token_tbl => l_token_tbl
251 , x_mesg_token_tbl => l_mesg_token_tbl
252 );
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
255 THEN
256 Error_Handler.Add_Error_Token
257 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
258 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
259 , p_message_name => NULL
260 , p_message_text =>
261 'Unexpected error while existence verification of ' ||
262 'Item Revision '||
263 p_bom_revision_rec.revision
264 , p_token_tbl => l_token_tbl
265 );
266
267 x_return_status := l_return_status;
268 ELSE
269
270 /* Assign the relevant transaction type for SYNC operations */
271
272 IF p_bom_revision_rec.transaction_type = 'SYNC' THEN
273 IF l_return_status = Bom_Globals.G_RECORD_FOUND THEN
274 x_old_bom_revision_rec.transaction_type :=
275 Bom_Globals.G_OPR_UPDATE;
276 ELSE
277 x_old_bom_revision_rec.transaction_type :=
278 Bom_Globals.G_OPR_CREATE;
279 END IF;
280 END IF;
281 x_return_status := FND_API.G_RET_STS_SUCCESS;
282
283 END IF;
284
285 x_mesg_token_tbl := l_mesg_token_tbl;
286
287 END Check_Existence;
288
289 END Bom_Validate_Bom_Revision;