[Home] [Help]
PACKAGE BODY: APPS.GMI_ITEM_PUB
Source
1 PACKAGE BODY GMI_ITEM_PUB AS
2 --$Header: GMIPITMB.pls 115.13 2002/10/28 15:09:05 jdiiorio gmigapib.pls $
3 -- Body start of comments
4 --+==========================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+==========================================================================+
9 --| FILE NAME |
10 --| GMIPITMB.pls |
11 --| |
12 --| PACKAGE NAME |
13 --| GMI_ITEM_PUB |
14 --| |
15 --| DESCRIPTION |
16 --| This package conatains all APIs related to the Business Object Item |
17 --| |
18 --| CONTENTS |
19 --| Create_Item |
20 --| Validate_Item |
21 --| |
22 --| HISTORY |
23 --| 15-FEB-1999 M.Godfrey Upgraded to R11. |
24 --| 16-AUG-1999 Liz Enstone B965832(1) Several changes to fix |
25 --| problems with status_ctl |
26 --| 20/AUG/1999 H.Verdding Bug 951828 Change GMS package Calls to GMA |
27 --| 02/Mar/2000 Liz Enstone Bug 1222126 qc_grade,lot_status not being |
28 --| written to ic_item_mst
29 --| 13/Mar/2000 Liz Enstone Bug 1231196 Don't write records to ic_item_ |
30 --| cpg as CPG is not supported from 11.5 |
31 --| Fix has also got rid of superfluous debugging messages |
32 --| 23/Oct/2001 Joe DiIorio Bug 1989860 11.5.1H - Removed references to |
33 --| Intrastat and commodity code. i.e. SY$INTRASTAT. |
34 --| 28/Oct/2002 Joe DiIorio Bug 2643440 11.5.1J - Added nocopy. |
35 --+==========================================================================+
36 -- Body end of comments
37 -- Global variables
38 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_ITEM_PUB';
39 IC$DEFAULT_LOT VARCHAR2(255);
40 -- Api start of comments
41 --+==========================================================================+
42 --| PROCEDURE NAME |
43 --| Create_Item |
44 --| |
45 --| TYPE |
46 --| Public |
47 --| |
48 --| USAGE |
49 --| Create a new Item in Item Master |
50 --| |
51 --| DESCRIPTION |
52 --| This procedure creates a new inventory item |
53 --| |
54 --| PARAMETERS |
55 --| p_api_version IN NUMBER - Api Version |
56 --| p_init_msg_list IN VARCHAR2 - Message Initialization Ind. |
57 --| p_commit IN VARCHAR2 - Commit Indicator |
58 --| p_validation_level IN VARCHAR2 - Validation Level Indicator |
59 --| p_item_rec IN item_rec_typ - Item Master details |
60 --| x_return_status OUT VARCHAR2 - Return Status |
61 --| x_msg_count OUT NUMBER - Number of messages |
62 --| x_msg_data OUT VARCHAR2 - Messages in encoded format |
63 --| |
64 --| RETURNS |
65 --| None |
66 --| |
67 --| HISTORY |
68 --| |
69 --+==========================================================================+
70 -- Api end of comments
71 PROCEDURE Create_item
72 ( p_api_version IN NUMBER
73 , p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE
74 , p_commit IN VARCHAR2 :=FND_API.G_FALSE
75 , p_validation_level IN VARCHAR2 :=FND_API.G_VALID_LEVEL_FULL
76 , p_item_rec IN item_rec_typ
77 , x_return_status OUT NOCOPY VARCHAR2
78 , x_msg_count OUT NOCOPY NUMBER
79 , x_msg_data OUT NOCOPY VARCHAR2
80 )
81 IS
82 l_api_name CONSTANT VARCHAR2 (30) :='Create_Item';
83 l_api_version CONSTANT NUMBER :=2.0;
84 l_msg_count NUMBER;
85 l_msg_data VARCHAR2(2000);
86 l_return_status VARCHAR2(1);
87 l_qcitem_no VARCHAR2(32);
88 l_whse_item_no VARCHAR2(32);
89 l_item_id ic_item_mst.item_id%TYPE :=0;
90 l_item_no ic_item_mst.item_no%TYPE;
91 l_item_desc1 ic_item_mst.item_desc1%TYPE;
92 l_item_um ic_item_mst.item_um%TYPE;
93 l_item_um2 ic_item_mst.item_um2%TYPE;
94 l_qcitem_id ic_item_mst.qcitem_id%TYPE;
95 l_whse_item_id ic_item_mst.whse_item_id%TYPE;
96 l_user_name fnd_user.user_name%TYPE;
97 l_user_id fnd_user.user_id%TYPE;
98 l_ic_item_mst_rec ic_item_mst%ROWTYPE;
99 l_ic_item_cpg_rec ic_item_cpg%ROWTYPE;
100 l_lot_rec GMI_LOTS_PUB.lot_rec_typ;
101 --B965832(1) Add the following variables
102 l_lot_status ic_item_mst.lot_status%TYPE;
103 l_qc_grade ic_item_mst.qc_grade%TYPE;
104
105 BEGIN
106
107 -- Standard Start OF API savepoint
108 SAVEPOINT Create_Item;
109 -- Standard call to check for call compatibility.
110 IF NOT FND_API.Compatible_API_CALL ( l_api_version
111 , p_api_version
112 , l_api_name
113 , G_PKG_NAME
114 )
115 THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END IF;
118 -- Initialize message list if p_int_msg_list is set TRUE.
119 IF FND_API.to_boolean(p_init_msg_list)
120 THEN
121 FND_MSG_PUB.Initialize;
122 END IF;
123 -- Initialize API return status to sucess
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 -- Populate WHO columns
127 GMA_GLOBAL_GRP.Get_who( p_user_name => p_item_rec.user_name
128 , x_user_id => l_user_id
129 );
130 IF l_user_id = 0
131 THEN
132 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_USER_NAME');
133 FND_MESSAGE.SET_TOKEN('USER_NAME',l_user_name);
134 FND_MSG_PUB.Add;
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138 -- Get required system constants
139 IC$DEFAULT_LOT := FND_PROFILE.Value_Specific( name => 'IC$DEFAULT_LOT'
140 , user_id => l_user_id
141 );
142 IF (IC$DEFAULT_LOT IS NULL)
143 THEN
144 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
145 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$DEFAULT_LOT');
146 FND_MSG_PUB.Add;
147 RAISE FND_API.G_EXC_ERROR;
148 END IF;
149
150
151 l_qcitem_no := UPPER(p_item_rec.qcitem_no);
152 l_whse_item_no := UPPER(p_item_rec.whse_item_no);
153 l_user_name := UPPER(p_item_rec.user_name);
154
155 -- Ensure Upper-case columns are converted
156
157 l_item_no := UPPER(p_item_rec.item_no);
158 l_item_um := p_item_rec.item_um;
159 l_item_um2 := p_item_rec.item_um2;
160
161
162 -- Perform Validation
163 GMI_ITEM_PUB.Validate_Item ( p_api_version => 2.0
164 , p_init_msg_list => FND_API.G_FALSE
165 , p_item_rec =>p_item_rec
166 , x_return_status =>l_return_status
167 , x_msg_count =>l_msg_count
168 , x_msg_data =>l_msg_data
169 );
170
171 -- If errors were found then raise exception
172 IF (l_return_status = FND_API.G_RET_STS_ERROR)
173 THEN
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176 -- If no errors were found then proceed with the item create
177
178 -- First get the surrogate key (item_id) for the item
179 SELECT gem5_item_id_s.nextval INTO l_item_id FROM dual;
180 IF (l_item_id <=0)
181 THEN
182 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
183 FND_MESSAGE.SET_TOKEN('SKEY','item_id');
184 FND_MSG_PUB.Add;
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 -- Get item_id of warehouse item and QC reference item if required
189 l_qcitem_id := GMI_VALID_GRP.Validate_item_existance(l_qcitem_no);
190 IF (l_qcitem_id = 0)
191 THEN
192 l_qcitem_id := NULL;
193 END IF;
194
195 l_whse_item_id := GMI_VALID_GRP.Validate_item_existance(l_whse_item_no);
196
197 IF (l_whse_item_id = 0)
198 THEN
199 l_whse_item_id := l_item_id;
200 END IF;
201
202 -- Set up PL/SQL record and insert item into ic_item_mst
203
204 l_ic_item_mst_rec.item_id := l_item_id;
205 l_ic_item_mst_rec.item_no := l_item_no;
206 l_ic_item_mst_rec.item_desc1 := p_item_rec.item_desc1;
207 l_ic_item_mst_rec.item_desc2 := p_item_rec.item_desc2;
208 l_ic_item_mst_rec.alt_itema := UPPER(p_item_rec.alt_itema);
209 l_ic_item_mst_rec.alt_itemb := UPPER(p_item_rec.alt_itemb);
210 l_ic_item_mst_rec.item_um := l_item_um;
211 l_ic_item_mst_rec.dualum_ind := p_item_rec.dualum_ind;
212 l_ic_item_mst_rec.item_um2 := l_item_um2;
213 l_ic_item_mst_rec.deviation_lo := p_item_rec.deviation_lo;
214 l_ic_item_mst_rec.deviation_hi := p_item_rec.deviation_hi;
215 l_ic_item_mst_rec.level_code := p_item_rec.level_code;
216 l_ic_item_mst_rec.lot_ctl := p_item_rec.lot_ctl;
217 l_ic_item_mst_rec.lot_indivisible := p_item_rec.lot_indivisible;
218 l_ic_item_mst_rec.sublot_ctl := p_item_rec.sublot_ctl;
219 l_ic_item_mst_rec.loct_ctl := p_item_rec.loct_ctl;
220 l_ic_item_mst_rec.noninv_ind := p_item_rec.noninv_ind;
221 l_ic_item_mst_rec.match_type := p_item_rec.match_type;
222 l_ic_item_mst_rec.inactive_ind := p_item_rec.inactive_ind;
223 l_ic_item_mst_rec.inv_type := p_item_rec.inv_type;
224 l_ic_item_mst_rec.shelf_life := p_item_rec.shelf_life;
225 l_ic_item_mst_rec.retest_interval := p_item_rec.retest_interval;
226 l_ic_item_mst_rec.item_abccode := UPPER(p_item_rec.item_abccode);
227 l_ic_item_mst_rec.gl_class := UPPER(p_item_rec.gl_class);
228 l_ic_item_mst_rec.inv_class := UPPER(p_item_rec.inv_class);
229 l_ic_item_mst_rec.sales_class := UPPER(p_item_rec.sales_class);
230 l_ic_item_mst_rec.ship_class := UPPER(p_item_rec.ship_class);
231 l_ic_item_mst_rec.frt_class := UPPER(p_item_rec.frt_class);
232 l_ic_item_mst_rec.price_class := UPPER(p_item_rec.price_class);
233 l_ic_item_mst_rec.storage_class := UPPER(p_item_rec.storage_class);
234 l_ic_item_mst_rec.purch_class := UPPER(p_item_rec.purch_class);
235 l_ic_item_mst_rec.tax_class := UPPER(p_item_rec.tax_class);
236 l_ic_item_mst_rec.customs_class := UPPER(p_item_rec.customs_class);
237 l_ic_item_mst_rec.alloc_class := UPPER(p_item_rec.alloc_class);
238 l_ic_item_mst_rec.planning_class := UPPER(p_item_rec.planning_class);
239 l_ic_item_mst_rec.itemcost_class := UPPER(p_item_rec.itemcost_class);
240 l_ic_item_mst_rec.cost_mthd_code := UPPER(p_item_rec.cost_mthd_code);
241 l_ic_item_mst_rec.upc_code := p_item_rec.upc_code;
242 l_ic_item_mst_rec.grade_ctl := p_item_rec.grade_ctl;
243 l_ic_item_mst_rec.status_ctl := p_item_rec.status_ctl;
244 --B965832(1) Comment these 2 variables out here
245 --B1222126 Restore these 2 lines. qc_grade and lot_status are not being
246 --saved
247 l_ic_item_mst_rec.qc_grade := UPPER(p_item_rec.qc_grade);
248 l_ic_item_mst_rec.lot_status := UPPER(p_item_rec.lot_status);
249 l_ic_item_mst_rec.bulk_id := p_item_rec.bulk_id;
250 l_ic_item_mst_rec.pkg_id := p_item_rec.pkg_id;
251 l_ic_item_mst_rec.qcitem_id := l_qcitem_id;
252 l_ic_item_mst_rec.qchold_res_code := UPPER(p_item_rec.qchold_res_code);
253 l_ic_item_mst_rec.expaction_code := UPPER(p_item_rec.expaction_code);
254 l_ic_item_mst_rec.fill_qty := p_item_rec.fill_qty;
255 l_ic_item_mst_rec.fill_um := p_item_rec.fill_um;
256 l_ic_item_mst_rec.expaction_interval := p_item_rec.expaction_interval;
257 l_ic_item_mst_rec.phantom_type := p_item_rec.phantom_type;
258 l_ic_item_mst_rec.whse_item_id := l_whse_item_id;
259 l_ic_item_mst_rec.experimental_ind := p_item_rec.experimental_ind;
260 l_ic_item_mst_rec.exported_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
261 l_ic_item_mst_rec.creation_date := SYSDATE;
262 l_ic_item_mst_rec.last_update_date := SYSDATE;
263 l_ic_item_mst_rec.created_by := l_user_id;
264 l_ic_item_mst_rec.last_updated_by := l_user_id;
265 l_ic_item_mst_rec.last_update_login := TO_NUMBER(FND_PROFILE.Value(
266 'LOGIN_ID'));
267 l_ic_item_mst_rec.trans_cnt := 1;
268 l_ic_item_mst_rec.delete_mark := 0;
269 l_ic_item_mst_rec.text_code := NULL;
270 l_ic_item_mst_rec.seq_dpnd_class := UPPER(p_item_rec.seq_dpnd_class);
271 l_ic_item_mst_rec.commodity_code := p_item_rec.commodity_code;
272 l_ic_item_mst_rec.attribute1 := UPPER(p_item_rec.attribute1);
273 l_ic_item_mst_rec.attribute2 := UPPER(p_item_rec.attribute2);
274 l_ic_item_mst_rec.attribute3 := UPPER(p_item_rec.attribute3);
275 l_ic_item_mst_rec.attribute4 := UPPER(p_item_rec.attribute4);
276 l_ic_item_mst_rec.attribute5 := UPPER(p_item_rec.attribute5);
277 l_ic_item_mst_rec.attribute6 := UPPER(p_item_rec.attribute6);
278 l_ic_item_mst_rec.attribute7 := UPPER(p_item_rec.attribute7);
279 l_ic_item_mst_rec.attribute8 := UPPER(p_item_rec.attribute8);
280 l_ic_item_mst_rec.attribute9 := UPPER(p_item_rec.attribute9);
281 l_ic_item_mst_rec.attribute10 := UPPER(p_item_rec.attribute10);
282 l_ic_item_mst_rec.attribute11 := UPPER(p_item_rec.attribute11);
283 l_ic_item_mst_rec.attribute12 := UPPER(p_item_rec.attribute12);
284 l_ic_item_mst_rec.attribute13 := UPPER(p_item_rec.attribute13);
285 l_ic_item_mst_rec.attribute14 := UPPER(p_item_rec.attribute14);
286 l_ic_item_mst_rec.attribute15 := UPPER(p_item_rec.attribute15);
287 l_ic_item_mst_rec.attribute16 := UPPER(p_item_rec.attribute16);
288 l_ic_item_mst_rec.attribute17 := UPPER(p_item_rec.attribute17);
289 l_ic_item_mst_rec.attribute18 := UPPER(p_item_rec.attribute18);
290 l_ic_item_mst_rec.attribute19 := UPPER(p_item_rec.attribute19);
291 l_ic_item_mst_rec.attribute20 := UPPER(p_item_rec.attribute20);
292 l_ic_item_mst_rec.attribute21 := UPPER(p_item_rec.attribute21);
293 l_ic_item_mst_rec.attribute22 := UPPER(p_item_rec.attribute22);
294 l_ic_item_mst_rec.attribute23 := UPPER(p_item_rec.attribute23);
295 l_ic_item_mst_rec.attribute24 := UPPER(p_item_rec.attribute24);
296 l_ic_item_mst_rec.attribute25 := UPPER(p_item_rec.attribute25);
297 l_ic_item_mst_rec.attribute26 := UPPER(p_item_rec.attribute26);
298 l_ic_item_mst_rec.attribute27 := UPPER(p_item_rec.attribute27);
299 l_ic_item_mst_rec.attribute28 := UPPER(p_item_rec.attribute28);
300 l_ic_item_mst_rec.attribute29 := UPPER(p_item_rec.attribute29);
301 l_ic_item_mst_rec.attribute30 := UPPER(p_item_rec.attribute30);
302 l_ic_item_mst_rec.attribute_category := UPPER(p_item_rec.attribute_category);
303
304 -- dbms_output.put_line('item_id '||'!'||l_item_id||'!');
305 -- dbms_output.put_line('item_no '||'!'||l_item_no||'!');
306 -- dbms_output.put_line('item_desc1 '||'!'||p_item_rec.item_desc1||'!');
307 -- dbms_output.put_line('item_desc2 '||'!'||p_item_rec.item_desc2||'!');
308 -- dbms_output.put_line('alt_itema '||'!'||UPPER(p_item_rec.alt_itema)||'!');
309 -- dbms_output.put_line('alt_itemb '||'!'||UPPER(p_item_rec.alt_itemb)||'!');
310 -- dbms_output.put_line('item_um '||'!'||l_item_um||'!');
311 -- dbms_output.put_line('dualum_ind '||'!'||p_item_rec.dualum_ind||'!');
312 -- dbms_output.put_line('item_um2 '||'!'||l_item_um2||'!');
313 -- dbms_output.put_line('deviation_lo '||'!'||p_item_rec.deviation_lo||'!');
314 -- dbms_output.put_line('deviation_hi '||'!'||p_item_rec.deviation_hi||'!');
315 -- dbms_output.put_line('level_code '||'!'||p_item_rec.level_code||'!');
316 -- dbms_output.put_line('lot_ctl '||'!'||p_item_rec.lot_ctl||'!');
317 -- dbms_output.put_line('lot_indivisible '||'!'||p_item_rec.lot_indivisible||'!');
318 -- dbms_output.put_line('sublot_ctl '||'!'||p_item_rec.sublot_ctl||'!');
319 -- dbms_output.put_line('loct_ctl '||'!'||p_item_rec.loct_ctl||'!');
320 -- dbms_output.put_line('noninv_ind '||'!'||p_item_rec.noninv_ind||'!');
321 -- dbms_output.put_line('match_type '||'!'||p_item_rec.match_type||'!');
322 -- dbms_output.put_line('inactive_ind '||'!'||p_item_rec.inactive_ind||'!');
323 -- dbms_output.put_line('inv_type '||'!'||p_item_rec.inv_type||'!');
324 -- dbms_output.put_line('shelf_life '||'!'||p_item_rec.shelf_life||'!');
325 -- dbms_output.put_line('retest_interval '||'!'||p_item_rec.retest_interval||'!');
326 -- dbms_output.put_line('item_abccode '||'!'||UPPER(p_item_rec.item_abccode)||'!');
327 -- dbms_output.put_line('gl_class '||'!'||UPPER(p_item_rec.gl_class)||'!');
328 -- dbms_output.put_line('inv_class '||'!'||UPPER(p_item_rec.inv_class)||'!');
329 -- dbms_output.put_line('sales_class '||'!'||UPPER(p_item_rec.sales_class)||'!');
330 -- dbms_output.put_line('ship_class '||'!'||UPPER(p_item_rec.ship_class)||'!');
331 -- dbms_output.put_line('frt_class '||'!'||UPPER(p_item_rec.frt_class)||'!');
332 -- dbms_output.put_line('price_class '||'!'||UPPER(p_item_rec.price_class)||'!');
333 -- dbms_output.put_line('storage_class '||'!'||UPPER(p_item_rec.storage_class)||'!');
334 -- dbms_output.put_line('purch_class '||'!'||UPPER(p_item_rec.purch_class)||'!');
335 -- dbms_output.put_line('tax_class '||'!'||UPPER(p_item_rec.tax_class)||'!');
336 -- dbms_output.put_line('customs_class '||'!'||UPPER(p_item_rec.customs_class)||'!');
337 -- dbms_output.put_line('alloc_class '||'!'||UPPER(p_item_rec.alloc_class)||'!');
338 -- dbms_output.put_line('planning_class '||'!'||UPPER(p_item_rec.planning_class)||'!');
339 -- dbms_output.put_line('itemcost_class '||'!'||UPPER(p_item_rec.itemcost_class)||'!');
340 -- dbms_output.put_line('cost_mthd_code '||'!'||UPPER(p_item_rec.cost_mthd_code)||'!');
341 -- dbms_output.put_line('upc_code '||'!'||p_item_rec.upc_code||'!');
342 -- dbms_output.put_line('user_class1 '||'!'||p_item_rec.user_class1||'!');
343 -- dbms_output.put_line('user_class2 '||'!'||p_item_rec.user_class2||'!');
344 -- dbms_output.put_line('user_class3 '||'!'||p_item_rec.user_class3||'!');
345 -- dbms_output.put_line('user_class4 '||'!'||p_item_rec.user_class4||'!');
346 -- dbms_output.put_line('user_class5 '||'!'||p_item_rec.user_class5||'!');
347 -- dbms_output.put_line('user_class6 '||'!'||p_item_rec.user_class6||'!');
348 -- dbms_output.put_line('grade_ctl '||'!'||p_item_rec.grade_ctl||'!');
349 -- dbms_output.put_line('status_ctl '||'!'||p_item_rec.status_ctl||'!');
350 -- dbms_output.put_line('qc_grade '||'!'||UPPER(p_item_rec.qc_grade)||'!');
351 -- dbms_output.put_line('lot_status '||'!'||UPPER(p_item_rec.lot_status)||'!');
352 -- dbms_output.put_line('bulk_id '||'!'||p_item_rec.bulk_id||'!');
353 -- dbms_output.put_line('pkg_id '||'!'||p_item_rec.pkg_id||'!');
354 -- dbms_output.put_line('qcitem_id '||'!'||l_qcitem_id||'!');
355 -- dbms_output.put_line('qchold_res_code '||'!'||UPPER(p_item_rec.qchold_res_code)||'!');
356 -- dbms_output.put_line('expaction_code '||'!'||UPPER(p_item_rec.expaction_code)||'!');
357 -- dbms_output.put_line('fill_qty '||'!'||p_item_rec.fill_qty||'!');
358 -- dbms_output.put_line('fill_um '||'!'||UPPER(p_item_rec.fill_um)||'!');
359 -- dbms_output.put_line('expaction_interval'||'!'||p_item_rec.expaction_interval||'!');
360 -- dbms_output.put_line('phantom_type '||'!'||p_item_rec.phantom_type||'!');
361 -- dbms_output.put_line('whse_item_id '||'!'||l_whse_item_id||'!');
362 -- dbms_output.put_line('experimental_ind '||'!'||p_item_rec.experimental_ind||'!');
363 -- dbms_output.put_line('exported_date '||'!'||GMA_GLOBAL_GRP.SY$MIN_DATE||'!');
364 -- dbms_output.put_line('date_added '||'!'||SYSDATE||'!');
365 -- dbms_output.put_line('date_modified '||'!'||SYSDATE||'!');
366 -- dbms_output.put_line('added_by '||'!'||l_op_code||'!');
367 -- dbms_output.put_line('modified_by '||'!'||l_op_code||'!');
368 -- dbms_output.put_line('trans_cnt '||'!'||1||'!');
369 -- dbms_output.put_line('delete_mark '||'!'||0||'!');
370 -- dbms_output.put_line('text_code '||'!'||0||'!');
371 -- dbms_output.put_line('seq_dpnd_class '||'!'||UPPER(p_item_rec.seq_dpnd_class)||'!');
372 -- dbms_output.put_line('commodity_code '||'!'||p_item_rec.commodity_code||'!');
373 IF NOT GMI_ITEM_PVT.insert_ic_item_mst(l_ic_item_mst_rec)
374 THEN
375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
376 END IF;
377
378 -- Set up PL/SQL record and insert item into ic_item_cpg
379
380 l_ic_item_cpg_rec.item_id := l_item_id;
381 l_ic_item_cpg_rec.ic_matr_days := p_item_rec.ic_matr_days;
382 l_ic_item_cpg_rec.ic_hold_days := p_item_rec.ic_hold_days;
383 l_ic_item_cpg_rec.creation_date := SYSDATE;
384 l_ic_item_cpg_rec.last_update_date := SYSDATE;
385 l_ic_item_cpg_rec.created_by := l_user_id;
386 l_ic_item_cpg_rec.last_updated_by := l_user_id;
387 l_ic_item_cpg_rec.last_update_login := TO_NUMBER(FND_PROFILE.Value(
388 'LOGIN_ID'));
389 --B1231196 Don't write record to ic_item_cpg. All parameters, variables etc
390 --associated with cpg tables should eventually be removed to save on
391 --processing time
392
393 -- IF NOT GMI_ITEM_PVT.insert_ic_item_cpg(l_ic_item_cpg_rec)
394 -- THEN
395 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
396 -- END IF;
397 --B1231196 End
398
399 -- Set up PL/SQL record and call lot create API
400 -- Set lot_no to 'NEWITEM' which will be replaced by
401 -- IC$DEFAULT_LOT in lot create API
402
403 l_lot_rec.item_no := l_item_no;
404 l_lot_rec.lot_no := 'NEWITEM';
405 l_lot_rec.sublot_no := NULL;
406 l_lot_rec.expaction_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
407 l_lot_rec.lot_created := GMA_GLOBAL_GRP.SY$MIN_DATE;
408 l_lot_rec.expire_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
409 l_lot_rec.retest_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
410 l_lot_rec.strength := 100;
411 l_lot_rec.inactive_ind := 0;
412 l_lot_rec.origination_type := 0;
413 l_lot_rec.ic_matr_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
414 l_lot_rec.ic_hold_date := GMA_GLOBAL_GRP.SY$MIN_DATE;
415 l_lot_rec.user_name := l_user_name;
416
417 -- Call lot create API
418
419 GMI_LOTS_PUB.create_lot ( p_api_version => 2.0
420 , p_commit => FND_API.G_FALSE
421 , p_init_msg_list => FND_API.G_FALSE
422 , x_return_status => l_return_status
423 , x_msg_count => l_msg_count
424 , x_msg_data => l_msg_data
425 , p_lot_rec => l_lot_rec
426 );
427
428 -- If errors were found then raise exception
429 IF (l_return_status = FND_API.G_RET_STS_ERROR)
430 THEN
431 RAISE FND_API.G_EXC_ERROR;
432 ELSIF
433 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436
437 -- END of API Body
438
439 -- Standard Check of p_commit.
440 IF FND_API.to_boolean(p_commit)
441 THEN
442 COMMIT WORK;
443 END IF;
444 -- Success message
445 FND_MESSAGE.SET_NAME('GMI','IC_API_ITEM_CREATED');
446 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
447 FND_MSG_PUB.Add;
448 -- Standard Call to get message count and if count is 1,
449 -- get message info.
450
451 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
452 , p_data => x_msg_data
453 );
454
455 EXCEPTION
456 WHEN FND_API.G_EXC_ERROR THEN
457 ROLLBACK TO Create_Item;
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
460 , p_data => x_msg_data
461 );
462
463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
464 ROLLBACK TO Create_Item;
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
467 , p_data => x_msg_data
468 );
469 WHEN OTHERS THEN
470 ROLLBACK TO Create_Item;
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 -- IF FND_MSG_PUB.check_msg_level
473 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
474 -- THEN
475
476 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
477 , l_api_name
478 );
479 -- END IF;
480 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
481 , p_data => x_msg_data
482 );
483 END Create_Item;
484
485 -- Api start of comments
486 --+==========================================================================+
487 --| PROCEDURE NAME |
488 --| Validate_Item |
489 --| |
490 --| TYPE |
491 --| Public |
492 --| |
493
494 --| USAGE |
495 --| Perform all validation functions associated with creation of a new |
496 --| inventory item |
497 --| |
498 --| DESCRIPTION |
499 --| This procedure validates all data associated with creation of |
500 --| a new inventory item |
501 --| |
502 --| PARAMETERS |
503 --| p_api_version IN NUMBER - Api Version |
504 --| p_init_msg_list IN VARCHAR2 - Message Initialization Ind. |
505 --| p_commit IN VARCHAR2 - Commit Indicator |
506 --| p_validation_level IN VARCHAR2 - Validation Level Indicator |
507
508 --| p_item_rec IN item_rec_typ - Item Master details |
509 --| x_return_status OUT VARCHAR2 - Return Status |
510 --| x_msg_count OUT NUMBER - Number of messages |
511 --| x_msg_data OUT VARCHAR2 - Messages in encoded format |
512 --| |
513 --| RETURNS |
514 --| None |
515 --| |
516 --| HISTORY |
517 --| |
518 --+==========================================================================+
519 -- Api end of comments
520 PROCEDURE Validate_Item
521
522 ( p_api_version IN NUMBER
523 , p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE
524 , p_validation_level IN VARCHAR2 :=FND_API.G_VALID_LEVEL_FULL
525 , p_item_rec IN item_rec_typ
526 , x_return_status OUT NOCOPY VARCHAR2
527 , x_msg_count OUT NOCOPY NUMBER
528 , x_msg_data OUT NOCOPY VARCHAR2
529 )
530 IS
531 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Item';
532 l_api_version CONSTANT NUMBER := 2.0;
533 l_item_id ic_item_mst.item_id%TYPE;
534 l_item_no ic_item_mst.item_no%TYPE;
535
536 l_item_desc1 ic_item_mst.item_desc1%TYPE;
537 l_item_um ic_item_mst.item_um%TYPE;
538 l_item_um2 ic_item_mst.item_um2%TYPE;
539 l_qcitem_id ic_item_mst.qcitem_id%TYPE;
540 l_whse_item_id ic_item_mst.whse_item_id%TYPE;
541 l_user_name fnd_user.user_name%TYPE;
542 l_msg_count NUMBER;
543 l_msg_data VARCHAR2(2000);
544 l_return_status VARCHAR2(1);
545 l_ic_item_mst_rec ic_item_mst%ROWTYPE;
546 l_ic_item_cpg_rec ic_item_cpg%ROWTYPE;
547 --B965832(1) Add these 2 variables
548 l_lot_status ic_item_mst.lot_status%TYPE;
549 l_qc_grade ic_item_mst.qc_grade%TYPE;
550
551 BEGIN
552
553
554 -- Standard call to check for call compatibility.
555 IF NOT FND_API.Compatible_API_CALL ( l_api_version
556 , p_api_version
557 , l_api_name
558 , G_PKG_NAME
559 )
560 THEN
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF;
563
564 -- Initialize message list if p_int_msg_list is set TRUE.
565 IF FND_API.to_boolean(p_init_msg_list)
566
567 THEN
568 FND_MSG_PUB.Initialize;
569 END IF;
570 -- Initialize API return status to sucess
571 x_return_status := FND_API.G_RET_STS_SUCCESS;
572
573 -- Ensure Upper-case columns are converted
574
575 l_item_no := UPPER(p_item_rec.item_no);
576 l_item_um := p_item_rec.item_um;
577 l_item_um2 := p_item_rec.item_um2;
578 l_user_name := UPPER(p_item_rec.user_name);
579
580
581 -- Check item does not already exist
582 IF (GMI_VALID_GRP.Validate_item_existance(l_item_no) <> 0)
583 THEN
584 FND_MESSAGE.SET_NAME('GMI','IC_API_ITEM_ALREADY_EXISTS');
585 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
586 FND_MSG_PUB.Add;
587 RAISE FND_API.G_EXC_ERROR;
588 END IF;
589
590 -- Check description
591 IF (p_item_rec.item_desc1 = ' ' OR p_item_rec.item_desc1 IS NULL)
592 THEN
593 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_DESC1');
594
595 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
596 FND_MSG_PUB.Add;
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599
600
601 -- Primary Unit of Measure
602 IF NOT GMA_VALID_GRP.Validate_um(l_item_um)
603 THEN
604 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
605 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
606 FND_MESSAGE.SET_TOKEN('UOM',l_item_um);
607 FND_MSG_PUB.Add;
608 RAISE FND_API.G_EXC_ERROR;
609 END IF;
610
611 -- Dual unit of measure indicator
612 IF NOT GMI_VALID_GRP.Validate_dualum_ind(p_item_rec.dualum_ind)
613 THEN
614 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_DUALUM_IND');
615 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
616 FND_MSG_PUB.Add;
617 RAISE FND_API.G_EXC_ERROR;
618 END IF;
619
620 -- Secondary Unit of Measure
621
622 IF NOT GMI_VALID_GRP.Validate_item_um2(p_item_rec.dualum_ind,l_item_um2)
623 THEN
624 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
625 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
626 FND_MESSAGE.SET_TOKEN('UOM',l_item_um2);
627 FND_MSG_PUB.Add;
628 RAISE FND_API.G_EXC_ERROR;
629 END IF;
630
631 -- Deviation factor lo
632 IF NOT GMI_VALID_GRP.Validate_deviation ( p_item_rec.dualum_ind
633 , p_item_rec.deviation_lo
634 )
635
636 THEN
637 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_DEVIATION');
638 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
639 FND_MSG_PUB.Add;
640 RAISE FND_API.G_EXC_ERROR;
641 END IF;
642
643 -- Deviation factor hi
644 IF NOT GMI_VALID_GRP.Validate_deviation ( p_item_rec.dualum_ind
645 , p_item_rec.deviation_hi
646 )
647 THEN
648 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_DEVIATION');
649
650 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
651 FND_MSG_PUB.Add;
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;
654
655 -- lot control flag
656 IF NOT GMI_VALID_GRP.Validate_lot_ctl(p_item_rec.lot_ctl)
657 THEN
658 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_CTL');
659 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
660 FND_MSG_PUB.Add;
661 RAISE FND_API.G_EXC_ERROR;
662 END IF;
663
664
665 -- lot indivisible flag
666 IF NOT GMI_VALID_GRP.Validate_lot_indivisible ( p_item_rec.lot_ctl
667 , p_item_rec.lot_indivisible
668 )
669 THEN
670 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_INDIVISIBLE');
671 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
672 FND_MSG_PUB.Add;
673 RAISE FND_API.G_EXC_ERROR;
674 END IF;
675
676 -- Sublot control flag
677
678 IF NOT GMI_VALID_GRP.Validate_sublot_ctl ( p_item_rec.lot_ctl
679 , p_item_rec.sublot_ctl
680 )
681 THEN
682 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SUBLOT_CTL');
683 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
684 FND_MSG_PUB.Add;
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687
688 -- Location control flag
689 IF NOT GMI_VALID_GRP.Validate_loct_ctl(p_item_rec.loct_ctl)
690 THEN
691
692 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCT_CTL');
693 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
694 FND_MSG_PUB.Add;
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697
698 -- Non-inventory flag
699 IF NOT GMI_VALID_GRP.Validate_noninv_ind ( p_item_rec.noninv_ind
700 , p_item_rec.lot_ctl
701 )
702 THEN
703 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_NONINV_IND');
704 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
705
706 FND_MSG_PUB.Add;
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709
710 -- Match type
711 -- IF NOT GMI_VALID_GRP.Validate_Type( 'MATCH_TYPE'
712 -- , p_item_rec.match_type)
713 -- THEN
714 -- FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_MATCH_TYPE');
715 -- FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
716 --FND_MSG_PUB.Add;
717 -- RAISE FND_API.G_EXC_ERROR;
718 -- END IF;
719
720
721 -- Inactive indicator
722 IF NOT GMI_VALID_GRP.Validate_inactive_ind(p_item_rec.inactive_ind)
723 THEN
724 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_INACTIVE_IND');
725 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
726 FND_MSG_PUB.Add;
727 RAISE FND_API.G_EXC_ERROR;
728 END IF;
729
730 -- Inventory Type
731 IF (p_item_rec.inv_type <> ' ' AND p_item_rec.inv_type IS NOT NULL)
732 THEN
733
734 IF NOT GMI_VALID_GRP.Validate_inv_type(p_item_rec.inv_type)
735 THEN
736 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_INV_TYPE');
737 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
738 FND_MSG_PUB.Add;
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 END IF;
742
743 -- Shelf Life
744 IF NOT GMI_VALID_GRP.Validate_shelf_life ( p_item_rec.shelf_life
745 , p_item_rec.grade_ctl
746 )
747
748 THEN
749 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SHELF_LIFE');
750 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
751 FND_MSG_PUB.Add;
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754
755 -- Retest Interval
756 IF NOT GMI_VALID_GRP.Validate_retest_interval ( p_item_rec.retest_interval
757 , p_item_rec.grade_ctl
758 )
759 THEN
760 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_RETEST_INTERVAL');
761
762 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
763 FND_MSG_PUB.Add;
764 RAISE FND_API.G_EXC_ERROR;
765 END IF;
766
767 -- GL class
768
769 IF (p_item_rec.gl_class <> ' ' AND p_item_rec.gl_class IS NOT NULL)
770 THEN
771 IF NOT GMI_VALID_GRP.Validate_gl_class(p_item_rec.gl_class)
772 THEN
773 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_GL_CLASS');
774 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
775
776 FND_MSG_PUB.Add;
777 RAISE FND_API.G_EXC_ERROR;
778 END IF;
779 END IF;
780
781 -- Inventory class
782
783 IF (p_item_rec.inv_class <> ' ' AND p_item_rec.inv_class IS NOT NULL)
784 THEN
785 IF NOT GMI_VALID_GRP.Validate_inv_class(p_item_rec.inv_class)
786 THEN
787 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_INV_CLASS');
788 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
789
790 FND_MSG_PUB.Add;
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793 END IF;
794
795 -- Sales class
796
797 IF (p_item_rec.sales_class <> ' ' AND p_item_rec.sales_class IS NOT NULL)
798 THEN
799 IF NOT GMI_VALID_GRP.Validate_sales_class(p_item_rec.sales_class)
800 THEN
801 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SALES_CLASS');
802 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
803
804 FND_MSG_PUB.Add;
805 RAISE FND_API.G_EXC_ERROR;
806 END IF;
807 END IF;
808
809 -- Ship class
810
811 IF (p_item_rec.ship_class <> ' ' AND p_item_rec.ship_class IS NOT NULL)
812 THEN
813 IF NOT GMI_VALID_GRP.Validate_ship_class(p_item_rec.ship_class)
814 THEN
815 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SHIP_CLASS');
816 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
817
818 FND_MSG_PUB.Add;
819 RAISE FND_API.G_EXC_ERROR;
820 END IF;
821 END IF;
822
823 -- Freight class
824
825 IF (p_item_rec.frt_class <> ' ' AND p_item_rec.frt_class IS NOT NULL)
826 THEN
827 IF NOT GMI_VALID_GRP.Validate_frt_class(p_item_rec.frt_class)
828 THEN
829 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_FRT_CLASS');
830 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
831
832 FND_MSG_PUB.Add;
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835 END IF;
836
837 -- Price class
838
839 IF (p_item_rec.price_class <> ' ' AND p_item_rec.price_class IS NOT NULL)
840 THEN
841 IF NOT GMI_VALID_GRP.Validate_price_class(p_item_rec.price_class)
842 THEN
843 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_PRICE_CLASS');
844 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
845
846 FND_MSG_PUB.Add;
847 RAISE FND_API.G_EXC_ERROR;
848 END IF;
849 END IF;
850
851 -- Storage class
852
853 IF (p_item_rec.storage_class <> ' ' AND p_item_rec.storage_class IS NOT NULL)
854 THEN
855 IF NOT GMI_VALID_GRP.Validate_storage_class(p_item_rec.storage_class)
856 THEN
857 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_STORAGE_CLASS');
858 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
859
860 FND_MSG_PUB.Add;
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863 END IF;
864
865 -- Purchase class
866
867 IF (p_item_rec.purch_class <> ' ' AND p_item_rec.purch_class IS NOT NULL)
868 THEN
869 IF NOT GMI_VALID_GRP.Validate_purch_class(p_item_rec.purch_class)
870 THEN
871 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_PURCH_CLASS');
872 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
873
874 FND_MSG_PUB.Add;
875 RAISE FND_API.G_EXC_ERROR;
876 END IF;
877 END IF;
878
879 -- Tax class
880
881 IF (p_item_rec.tax_class <> ' ' AND p_item_rec.tax_class IS NOT NULL)
882 THEN
883 IF NOT GMI_VALID_GRP.Validate_tax_class(p_item_rec.tax_class)
884 THEN
885 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_TAX_CLASS');
886 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
887
888 FND_MSG_PUB.Add;
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891 END IF;
892
893 -- Customs class
894
895 IF (p_item_rec.customs_class <> ' ' AND p_item_rec.customs_class IS NOT NULL)
896 THEN
897 IF NOT GMI_VALID_GRP.Validate_customs_class(p_item_rec.customs_class)
898 THEN
899 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_CUSTOMS_CLASS');
900 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
901
902 FND_MSG_PUB.Add;
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905 END IF;
906
907 -- Allocation class
908
909 IF (p_item_rec.alloc_class <> ' ' AND p_item_rec.alloc_class IS NOT NULL)
910 THEN
911 IF NOT GMI_VALID_GRP.Validate_alloc_class(p_item_rec.alloc_class)
912 THEN
913 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ALLOC_CLASS');
914 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
915
916 FND_MSG_PUB.Add;
917 RAISE FND_API.G_EXC_ERROR;
918 END IF;
919 END IF;
920
921 -- Planning class
922
923 IF (p_item_rec.planning_class <> ' ' AND
924 p_item_rec.planning_class IS NOT NULL)
925 THEN
926 IF NOT GMI_VALID_GRP.Validate_planning_class(p_item_rec.planning_class)
927 THEN
928 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_PLANNING_CLASS');
929
930 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
931 FND_MSG_PUB.Add;
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934 END IF;
935
936 -- item Cost class
937 IF (p_item_rec.itemcost_class <> ' ' AND
938 p_item_rec.itemcost_class IS NOT NULL)
939 THEN
940 IF NOT GMI_VALID_GRP.Validate_itemcost_class(p_item_rec.itemcost_class)
941 THEN
942 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEMCOST_CLASS');
943
944 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
945 FND_MSG_PUB.Add;
946 RAISE FND_API.G_EXC_ERROR;
947 END IF;
948 END IF;
949
950 -- Cost Method Code
951 IF (p_item_rec.cost_mthd_code <> ' ' AND
952 p_item_rec.cost_mthd_code IS NOT NULL)
953 THEN
954 IF NOT GMI_VALID_GRP.Validate_cost_mthd_code(p_item_rec.cost_mthd_code)
955 THEN
956 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_COST_MTHD_CODE');
957
958 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
959 FND_MSG_PUB.Add;
960 RAISE FND_API.G_EXC_ERROR;
961 END IF;
962 END IF;
963
964 -- Grade control
965 IF NOT GMI_VALID_GRP.Validate_grade_ctl ( p_item_rec.grade_ctl
966 , p_item_rec.lot_ctl
967 )
968 THEN
969 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_GRADE_CTL');
970 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
971
972 FND_MSG_PUB.Add;
973 RAISE FND_API.G_EXC_ERROR;
974 END IF;
975
976 -- lot status control
977 IF NOT GMI_VALID_GRP.Validate_status_ctl ( p_item_rec.status_ctl
978 , p_item_rec.lot_ctl
979 )
980 THEN
981 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_STATUS_CTL');
982 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
983 FND_MSG_PUB.Add;
984 RAISE FND_API.G_EXC_ERROR;
985
986 END IF;
987
988 -- QC Grade
989 --B965832(1) Add check for grade control status
990 IF ((p_item_rec.grade_ctl=0) OR (p_item_rec.qc_grade= ' '))
991 THEN
992 l_qc_grade :='';
993 ELSE
994 l_qc_grade := UPPER(p_item_rec.qc_grade);
995 END IF;
996 IF NOT GMI_VALID_GRP.Validate_qc_grade ( l_qc_grade
997 , p_item_rec.grade_ctl
998 )
999 --B965832(1) end
1000 THEN
1001 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QC_GRADE');
1002 FND_MESSAGE.SET_TOKEN('QC_GRADE',p_item_rec.qc_grade);
1003 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1004 FND_MSG_PUB.Add;
1005 RAISE FND_API.G_EXC_ERROR;
1006 END IF;
1007
1008
1009 -- lot Status
1010
1011 --B965832(1) Add these lines to check for status control
1012
1013 IF ((p_item_rec.status_ctl = 0) OR (p_item_rec.lot_status=' '))
1014 THEN
1015 l_lot_status := '';
1016 ELSE
1017 l_lot_status := UPPER(p_item_rec.lot_status);
1018 END IF;
1019 IF NOT GMI_VALID_GRP.Validate_lot_status ( l_lot_status
1020 , p_item_rec.status_ctl
1021 )
1022 --B965832(1) end
1023 THEN
1024 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_STATUS');
1025 FND_MESSAGE.SET_TOKEN('LOT_STATUS',p_item_rec.lot_status);
1026 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1027 FND_MSG_PUB.Add;
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031
1032 -- QC reference item
1033 IF (p_item_rec.grade_ctl <> 1 AND
1034 p_item_rec.qcitem_no <> ' ' AND
1035 p_item_rec.qcitem_no IS NOT NULL)
1036 THEN
1037 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QCITEM_NO');
1038 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1039 FND_MSG_PUB.Add;
1040 RAISE FND_API.G_EXC_ERROR;
1041 ELSIF (p_item_rec.qcitem_no <> ' ' AND p_item_rec.qcitem_no IS NOT NULL)
1042 THEN
1043 GMI_GLOBAL_GRP.Get_Item ( p_item_no => p_item_rec.qcitem_no
1044 , x_ic_item_mst => l_ic_item_mst_rec
1045
1046 , x_ic_item_cpg => l_ic_item_cpg_rec
1047 );
1048 IF (l_ic_item_mst_rec.item_id < 0)
1049 THEN
1050 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1051 ELSIF (l_ic_item_mst_rec.item_id = 0) OR
1052 (l_ic_item_mst_rec.delete_mark = 1)
1053 THEN
1054 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QCITEM_NO');
1055 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1056 FND_MSG_PUB.Add;
1057 RAISE FND_API.G_EXC_ERROR;
1058 ELSIF (l_ic_item_mst_rec.noninv_ind = 1)
1059
1060 THEN
1061 FND_MESSAGE.SET_NAME('GMI','IC_API_NONINV_ITEM_NO');
1062 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1063 FND_MSG_PUB.Add;
1064 RAISE FND_API.G_EXC_ERROR;
1065 ELSIF (l_ic_item_mst_rec.inactive_ind = 1)
1066 THEN
1067 FND_MESSAGE.SET_NAME('GMI','IC_API_INACTIVE_ITEM_NO');
1068 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1069 FND_MSG_PUB.Add;
1070 RAISE FND_API.G_EXC_ERROR;
1071 END IF;
1072 END IF;
1073
1074
1075 -- QC Hold reason code
1076 -- IF NOT GMI_VALID_GRP.Validate_qchold_res_code(p_item_rec.qchold_res_code,
1077 -- p_item_rec.qc_grade) THEN
1078 -- FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QCHOLD_RES_CODE');
1079 -- FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1080 -- FND_MSG_PUB.Add;
1081 -- RAISE FND_API.G_EXC_ERROR;
1082 -- END IF;
1083
1084 -- Expiry Action Code
1085 IF (p_item_rec.expaction_code <> ' ' AND
1086 p_item_rec.expaction_code IS NOT NULL)
1087
1088 THEN
1089 IF NOT GMI_VALID_GRP.Validate_expaction_code ( p_item_rec.expaction_code
1090 , p_item_rec.grade_ctl
1091 )
1092 THEN
1093 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPACTION_CODE');
1094 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1095 FND_MSG_PUB.Add;
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098 END IF;
1099
1100 -- Expiry Action Interval
1101
1102 IF NOT GMI_VALID_GRP.Validate_expaction_interval(p_item_rec.expaction_interval
1103
1104
1105 , p_item_rec.grade_ctl
1106 )
1107 THEN
1108 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPACTION_CODE');
1109 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1110 FND_MSG_PUB.Add;
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113
1114 -- Warehouse item no.
1115
1116
1117 IF (p_item_rec.whse_item_no <> ' ' AND p_item_rec.whse_item_no IS NOT NULL)
1118 THEN
1119 GMI_GLOBAL_GRP.Get_Item ( p_item_no => p_item_rec.whse_item_no
1120 , x_ic_item_mst => l_ic_item_mst_rec
1121 , x_ic_item_cpg => l_ic_item_cpg_rec
1122 );
1123 IF (l_ic_item_mst_rec.item_id < 0)
1124 THEN
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 ELSIF (l_ic_item_mst_rec.item_id = 0) OR
1127 (l_ic_item_mst_rec.delete_mark = 1)
1128 THEN
1129
1130 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_WHSE_ITEM_NO');
1131 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1132 FND_MSG_PUB.Add;
1133 RAISE FND_API.G_EXC_ERROR;
1134 ELSIF (l_ic_item_mst_rec.noninv_ind = 1)
1135 THEN
1136 FND_MESSAGE.SET_NAME('GMI','IC_API_NONINV_ITEM_NO');
1137 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1138 FND_MSG_PUB.Add;
1139 RAISE FND_API.G_EXC_ERROR;
1140 ELSIF (l_ic_item_mst_rec.inactive_ind = 1)
1141 THEN
1142 FND_MESSAGE.SET_NAME('GMI','IC_API_INACTIVE_ITEM_NO');
1143
1144 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1145 FND_MSG_PUB.Add;
1146 RAISE FND_API.G_EXC_ERROR;
1147 END IF;
1148 END IF;
1149
1150 -- Experimental Indicator
1151 IF NOT GMI_VALID_GRP.Validate_experimental_ind(p_item_rec.experimental_ind)
1152 THEN
1153 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPERIMENTAL');
1154 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1155 FND_MSG_PUB.Add;
1156 RAISE FND_API.G_EXC_ERROR;
1157
1158 END IF;
1159
1160 -- Sequence Dependent class
1161
1162 IF (p_item_rec.seq_dpnd_class <> ' ' AND
1163 p_item_rec.seq_dpnd_class IS NOT NULL)
1164 THEN
1165 IF NOT GMI_VALID_GRP.Validate_seq_dpnd_class(p_item_rec.seq_dpnd_class)
1166 THEN
1167 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_SEQ_DPND_CLASS');
1168 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1169 FND_MSG_PUB.Add;
1170 RAISE FND_API.G_EXC_ERROR;
1171
1172 END IF;
1173 END IF;
1174
1175 -- Maturity days (CPG)
1176 IF (NOT GMI_VALID_GRP.Validate_ic_matr_days(p_item_rec.ic_matr_days) OR
1177 p_item_rec.ic_matr_days IS NULL)
1178 THEN
1179 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_MATR_DAYS');
1180 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1181 FND_MSG_PUB.Add;
1182 RAISE FND_API.G_EXC_ERROR;
1183 END IF;
1184
1185 -- Hold release days (CPG)
1186 IF (NOT GMI_VALID_GRP.Validate_ic_hold_days(p_item_rec.ic_hold_days) OR
1187 p_item_rec.ic_hold_days IS NULL)
1188 THEN
1189
1190 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_HOLD_DAYS');
1191 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
1192 FND_MSG_PUB.Add;
1193 RAISE FND_API.G_EXC_ERROR;
1194 END IF;
1195
1196 EXCEPTION
1197 WHEN FND_API.G_EXC_ERROR THEN
1198 x_return_status := FND_API.G_RET_STS_ERROR;
1199 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1200 , p_data => x_msg_data
1201 );
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1206 , p_data => x_msg_data
1207 );
1208 WHEN OTHERS THEN
1209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210 -- IF FND_MSG_PUB.check_msg_level
1211 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1212 -- THEN
1213
1214 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1215 , l_api_name
1216 );
1217
1218 -- END IF;
1219 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1220 , p_data => x_msg_data
1221 );
1222
1223 END Validate_Item;
1224 END GMI_ITEM_PUB;