1 PACKAGE BODY MTL_INV_VALIDATE_GRP AS
2 /* $Header: INVGIVVB.pls 120.2 2005/06/22 09:57:14 appldev ship $ */
3 Current_Error_Code VARCHAR2(30) := NULL;
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_INV_VALIDATE_GRP';
6
7
8 procedure mdebug(msg in varchar2)
9 is
10 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 begin
12 --dbms_output.put_line(msg);
13 null;
14 --inv_debug.message(msg);
15 end;
16 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data
17 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
18 procedure Get_Offset_Date(
19 p_api_version IN NUMBER ,
20 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
21 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
22 x_return_status OUT NOCOPY VARCHAR2 ,
23 x_msg_count OUT NOCOPY NUMBER ,
24 x_msg_data OUT NOCOPY VARCHAR2 ,
25 p_start_date IN DATE,
26 p_offset_days IN NUMBER,
27 p_calendar_code IN VARCHAR2,
28 p_exception_set_id IN NUMBER,
29 x_result_date OUT NOCOPY DATE)
30 -- Start OF comments
31 -- API name : Get_Offset_Date
32 -- TYPE : Group
33 -- Pre-reqs : None
34 -- FUNCTION :
35 -- Computes work day which is p_offset work days away from p_start_date
36 --
37 -- Parameters:
38 -- IN :
39 -- p_api_version IN NUMBER (required)
40 -- API Version of this procedure
41 --
42 -- p_init_msg_list IN VARCHAR2 (optional)
43 -- DEFAULT = FND_API.G_FALSE,
44 --
45 -- p_commit IN VARCHAR2 (optional)
46 -- DEFAULT = FND_API.G_FALSE
47 --
48 -- p_start_date IN DATE (Required)
49 -- date from which offset is computed
50 -- p_offset_days IN NUMBER (Required)
51 -- number of work days till the date we're looking for
52 --
53 -- p_calendar_code IN VARCHAR (Required) valid calendar code
54 -- p_exception_set_id IN NUMBER (Required) valid exception set
55 -- if no such date can be found (incorrect calendar_code,exception_set_id,
56 -- date out of calendar range, etc.)
57 -- then x_return_status will be set to ret_sts_unexp_error
58 -- the RECORD parameter includes the
59 -- OUT :
60 -- X_return_status OUT NUMBER
61 -- Result of all the operations
62 --
63 -- x_msg_count OUT NUMBER,
64 --
65 -- x_msg_data OUT VARCHAR2,
66 --
67 -- x_result_date OUT DATE - work date which is p_offset_days working days
68 -- away from p_start_date
69 -- Version: Current Version 0.9
70 -- Changed : Nothing
71 -- No Previous Version 0.0
72 -- Initial version 0.9
73 -- Notes : Note text
74 -- END OF comments
75 is
76
77 l_api_version NUMBER := 0.9;
78 l_api_name VARCHAR2(30) := 'Get_Offset_Date';
79
80 l_counter NUMBER := 0;
81
82 cursor l_date_csr is
83 select calendar_date
84 from bom_calendar_dates
85 where calendar_code = p_calendar_code
86 and exception_set_id = p_exception_set_id
87 and seq_num in
88 (select seq_num + p_offset_days
89 from bom_calendar_dates
90 where calendar_code = p_calendar_code
91 and exception_set_id = p_exception_set_id
92 and next_date = trunc(p_start_date));
93
94
95 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
96 begin
97 -- Standard start of API savepoint
98 SAVEPOINT Get_Offset_Date;
99 --
100 -- Standard Call to check for call compatibility
101 IF NOT FND_API.Compatible_API_Call(l_api_version
102 , p_api_version
103 , l_api_name
104 , G_PKG_NAME) THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106 END IF;
107 --
108 -- Initialize message list if p_init_msg_list is set to true
109 IF FND_API.to_Boolean(p_init_msg_list) THEN
110 FND_MSG_PUB.initialize;
111 END IF;
112 --
113 -- Initialisize API return status to access
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115 --
116 -- API body
117 --
118
119 for c_rec in l_date_csr loop
120 x_result_date := c_rec.calendar_date;
121 l_counter := l_counter + 1;
122 end loop;
123
124 if (l_counter <> 1) then
125 raise fnd_api.g_exc_unexpected_error;
126 end if;
127
128 --
129 -- END of API body
130 -- Standard check of p_commit
131 IF FND_API.to_Boolean(p_commit) THEN
132 COMMIT;
133 END IF;
134 -- Standard call to get message count and if count is 1, get message info
135 FND_MSG_PUB.Count_And_Get
136 (p_count => x_msg_count
137 , p_data => x_msg_data);
138
139 EXCEPTION
140 WHEN FND_API.G_EXC_ERROR THEN
141 --
142 ROLLBACK TO Get_Offset_Date;
143 --
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 --
146 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
147 , p_data => x_msg_data);
148 --
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 --
151 ROLLBACK TO Get_Offset_Date;
152 --
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 --
155 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
156 , p_data => x_msg_data);
157 --
158 WHEN OTHERS THEN
159 --
160 ROLLBACK TO Get_Offset_Date;
161 --
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 --
164 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
165 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
166 END IF;
167 --
168 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
169 , p_data => x_msg_data);
170 end;
171
172
173 -- XXX this procedure is not ever used and looks rather silly
174 -- we should get rid of that
175 -- Derive Count Uom
176 PROCEDURE Get_CountUom(
177 p_uom_code IN VARCHAR2 )
178 IS
179 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
180 BEGIN
181 -- Start OF comments
182 -- API name :Get_CountUom
183 -- TYPE : Private
184 -- Pre-reqs : None
185 -- FUNCTION :
186 -- Stores the COUNT UOM IN
187 -- package variables to use the information within this PGA session
188 -- Parameters:
189 -- IN :
190 -- p_uom_code IN VARCHAR2 (required)
191 -- Cycle COUNT UOM code
192 --
193 -- END OF comments
194 DECLARE
195 --
196 BEGIN
197 --
198 IF (l_debug = 1) THEN
199 MDEBUG( 'Getting UOM');
200 END IF;
201 MTL_CCEOI_VAR_PVT.G_UOM_CODE := p_uom_code;
202 --
203 END;
204 END;
205 --
206 -- Dervies Item and SKU information from the given Count List Sequence
207 PROCEDURE Get_Item_SKU(
208 p_cycle_count_entry_rec IN MTL_CYCLE_COUNT_ENTRIES%ROWTYPE )
209 IS
210 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
211 BEGIN
212 -- Start OF comments
213 -- API name :Get_Item_SKU
214 -- TYPE : Private
215 -- Pre-reqs : None
216 -- FUNCTION :
217 -- Stores the cycle COUNT entries item and SKU information IN
218 -- package variables to use the information within this PGA session
219 -- Parameters:
220 -- IN :
221 -- p_cycle_count_entry_rec IN mtl_cycle_count_entries%rowtype (required)
222 -- Cycle COUNT entries RECORD information
223 --
224 -- END OF comments
225 DECLARE
226 --
227 CURSOR L_Item_Csr(org NUMBER, id NUMBER) IS
228 SELECT *
229 FROM mtl_system_items
230 WHERE
231 organization_id = org
232 AND inventory_item_id = id;
233 --
234 BEGIN
235 --
236 FOR c_rec IN L_Item_Csr(p_cycle_count_entry_rec.organization_id,
237 p_cycle_count_entry_rec.inventory_item_id) LOOP
238 --
239 IF (l_debug = 1) THEN
240 MDEBUG( 'Getting Control Codes');
241 END IF;
242
243 -- flag indicating inventory item
244 MTL_CCEOI_VAR_PVT.G_SKU_REC.INVENTORY_ITEM_FLAG :=
245 c_rec.INVENTORY_ITEM_FLAG;
246
247 -- lot control code for this item (1 - none, 2 - full)
248 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE := c_rec.LOT_CONTROL_CODE;
249 -- revision qty control code (1 - not under revision qty control,
250 -- 2 under revision qty control)
251 MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION_QTY_CONTROL_CODE :=
252 c_rec.REVISION_QTY_CONTROL_CODE;
253
254 -- serial number control code
255 -- 1 - no serial control, 2 - predefined, 5 - dynamic at receipt
256 -- 6 - dynamic at issue
257 MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE :=
258 c_rec.SERIAL_NUMBER_CONTROL_CODE;
259
260 -- allowed unit of measure conversion type
261 -- 1 - item specific, 2 - standard, 3 - both standard and item spec
262 MTL_CCEOI_VAR_PVT.G_SKU_REC.ALLOWED_UNITS_LOOKUP_CODE :=
263 c_rec.ALLOWED_UNITS_LOOKUP_CODE;
264 --
265 END LOOP;
266 --
267 END;
268 END;
269 --
270 -- Get the STOCK_LOCATOR_CONTROL_CODE from the given ORG_ID
271 PROCEDURE Get_StockLocatorControlCode(
272 p_organization_id IN NUMBER )
273 IS
274 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
275 BEGIN
276 -- Start OF comments
277 -- API name : Get_StockLocatorControlCode
278 -- TYPE : Private
279 -- Pre-reqs : None
280 -- FUNCTION :
281 -- Gets the stock_locator_control_code FOR the given
282 -- organization
283 -- Parameters:
284 -- IN :
285 -- p_organization_id IN NUMBER (required)
286 -- organization id OF the current cycle COUNT
287 -- END OF comments
288 DECLARE
289 L_Stock_Locator_Control_code NUMBER;
290 BEGIN
291 --
292 SELECT
293 stock_locator_control_code
294 INTO
295 L_Stock_Locator_Control_code
296 FROM
297 MTL_PARAMETERS
298 WHERE
299 organization_id = p_organization_id;
300 --
301 IF (l_debug = 1) THEN
302 MDEBUG( 'Getting Locator Control code');
303 END IF;
304 MTL_CCEOI_VAR_PVT.G_STOCK_LOCATOR_CONTROL_CODE :=
305 L_Stock_Locator_Control_code;
306 --
307 END;
308 END;
309 --
310 -- Validates the adjustment account
311 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data
312 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
313 PROCEDURE Validate_AdjustAccount(
314 p_api_version IN NUMBER ,
315 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
316 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
317 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
318 x_return_status OUT NOCOPY VARCHAR2 ,
319 x_msg_count OUT NOCOPY NUMBER ,
320 x_msg_data OUT NOCOPY VARCHAR2 ,
321 p_adjustaccount_rec IN MTL_CCEOI_VAR_PVT.ADJUSTACCOUNT_REC_TYPE )
322 IS
323 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
324 BEGIN
325 -- Start OF comments
326 -- API name : Validate_AdjustAccount
327 -- TYPE : Private
328 -- Pre-reqs : None
329 -- FUNCTION :
330 -- Validates the adjust account information against
331 -- the TABLE gl_code_combinations
332 -- Parameters:
333 -- IN :
334 -- p_api_version IN NUMBER (required)
335 -- API Version of this procedure
336 --
337 -- p_init_msg_list IN VARCHAR2 (optional)
338 -- DEFAULT = FND_API.G_FALSE,
339 --
340 -- p_commit IN VARCHAR2 (optional)
341 -- DEFAULT = FND_API.G_FALSE
342 --
343 -- p_validation_level IN NUMBER (optional)
344 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
345 --
346 -- p_adjustaccount_rec IN MTL_CCEOI_VAR_PVT.ADJUSTACCOUNT_REC_TYPE (required)
347 -- the RECORD parameter includes the
348 -- adjustment account
349 -- segments
350 -- OUT :
351 -- X_return_status OUT NUMBER
352 -- Result of all the operations
353 --
354 -- x_msg_count OUT NUMBER,
355 --
356 -- x_msg_data OUT VARCHAR2,
357 --
358 -- Version: Current Version 0.9
359 -- Changed : Nothing
360 -- No Previous Version 0.0
361 -- Initial version 0.9
362 -- Notes : Note text
363 -- END OF comments
364 DECLARE
365 --
366 -- CURSOR FOR the adjustment_account_id
367 CURSOR L_AdjAccountID_Crs(ID IN NUMBER) IS
368 SELECT
369 code_combination_id
370 FROM gl_code_combinations
371 WHERE
372 code_combination_id = id;
373 --
374 -- curosr FOR the individual segments
375 CURSOR L_AdjAccountSeg_Crs(seg1 IN VARCHAR2,
376 seg2 IN VARCHAR2,
377 seg3 IN VARCHAR2,
378 seg4 IN VARCHAR2,
379 seg5 IN VARCHAR2,
380 seg6 IN VARCHAR2,
381 seg7 IN VARCHAR2,
382 seg8 IN VARCHAR2,
383 seg9 IN VARCHAR2,
384 seg10 IN VARCHAR2,
385 seg11 IN VARCHAR2,
386 seg12 IN VARCHAR2,
387 seg13 IN VARCHAR2,
388 seg14 IN VARCHAR2,
389 seg15 IN VARCHAR2,
390 seg16 IN VARCHAR2,
391 seg17 IN VARCHAR2,
392 seg18 IN VARCHAR2,
393 seg19 IN VARCHAR2,
394 seg20 IN VARCHAR2,
395 seg21 IN VARCHAR2,
396 seg22 IN VARCHAR2,
397 seg23 IN VARCHAR2,
398 seg24 IN VARCHAR2,
399 seg25 IN VARCHAR2,
400 seg26 IN VARCHAR2,
401 seg27 IN VARCHAR2,
402 seg28 IN VARCHAR2,
403 seg29 IN VARCHAR2,
404 seg30 IN VARCHAR2) IS
405 SELECT
406 code_combination_id
407 FROM gl_code_combinations
408 WHERE
409 NVL(segment1, '@') = NVL(seg1, '@')
410 AND NVL(segment2, '@') = NVL(seg2, '@')
411 AND NVL(segment3, '@') = NVL(seg3, '@')
412 AND NVL(segment4, '@') = NVL(seg4, '@')
413 AND NVL(segment5, '@') = NVL(seg5, '@')
414 AND NVL(segment6, '@') = NVL(seg6, '@')
415 AND NVL(segment7, '@') = NVL(seg7, '@')
416 AND NVL(segment8, '@') = NVL(seg8, '@')
417 AND NVL(segment9, '@') = NVL(seg9, '@')
418 AND NVL(segment10, '@') = NVL(seg10, '@')
419 AND NVL(segment11, '@') = NVL(seg11, '@')
420 AND NVL(segment12, '@') = NVL(seg12, '@')
421 AND NVL(segment13, '@') = NVL(seg13, '@')
422 AND NVL(segment14, '@') = NVL(seg14, '@')
423 AND NVL(segment15, '@') = NVL(seg15, '@')
424 AND NVL(segment16, '@') = NVL(seg16, '@')
425 AND NVL(segment17, '@') = NVL(seg17, '@')
426 AND NVL(segment18, '@') = NVL(seg18, '@')
427 AND NVL(segment19, '@') = NVL(seg19, '@')
428 AND NVL(segment20, '@') = NVL(seg20, '@')
429 AND NVL(segment21, '@') = NVL(seg21, '@')
430 AND NVL(segment22, '@') = NVL(seg22, '@')
431 AND NVL(segment23, '@') = NVL(seg23, '@')
432 AND NVL(segment24, '@') = NVL(seg24, '@')
433 AND NVL(segment25, '@') = NVL(seg25, '@')
434 AND NVL(segment26, '@') = NVL(seg26, '@')
435 AND NVL(segment27, '@') = NVL(seg27, '@')
436 AND NVL(segment28, '@') = NVL(seg28, '@')
437 AND NVL(segment29, '@') = NVL(seg29, '@')
438 AND NVL(segment30, '@') = NVL(seg30, '@');
439 --
440 L_counter integer := 0;
441 L_api_version CONSTANT NUMBER := 0.9;
442 L_api_name CONSTANT VARCHAR2(30) := 'Validate_AdjustAccount';
443 BEGIN
444 -- Standard start of API savepoint
445 SAVEPOINT Validate_AdjustAccount;
446 --
447 -- Standard Call to check for call compatibility
448 IF NOT FND_API.Compatible_API_Call(l_api_version
449 , p_api_version
450 , l_api_name
451 , G_PKG_NAME) THEN
452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453 END IF;
454 --
455 -- Initialize message list if p_init_msg_list is set to true
456 IF FND_API.to_Boolean(p_init_msg_list) THEN
457 FND_MSG_PUB.initialize;
458 END IF;
459 --
460 -- Initialisize API return status to access
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462 --
463 -- API body
464 --
465 -- Check OF existence
466 IF p_adjustaccount_rec.adjustment_account_id IS NOT NULL THEN
467 --
468 FOR c_rec IN L_AdjAccountID_Crs(
469 p_adjustaccount_rec.adjustment_account_id) LOOP
470 --
471 IF (l_debug = 1) THEN
472 MDEBUG( 'Validating Adj Account ID');
473 END IF;
474 MTL_CCEOI_VAR_PVT.G_ADJUST_ACCOUNT_ID :=
475 c_rec. code_combination_id;
476 --
477 L_counter := L_counter + 1;
478 IF L_counter > 1 THEN
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 END IF;
481 --
482 END LOOP;
483 --
484 ELSE
485 --
486 FOR c_rec IN L_AdjAccountSeg_Crs(p_adjustaccount_rec.account_segment1,
487 p_adjustaccount_rec.account_segment2,
488 p_adjustaccount_rec.account_segment3,
489 p_adjustaccount_rec.account_segment4,
490 p_adjustaccount_rec.account_segment5,
491 p_adjustaccount_rec.account_segment6,
492 p_adjustaccount_rec.account_segment7,
493 p_adjustaccount_rec.account_segment8,
494 p_adjustaccount_rec.account_segment9,
495 p_adjustaccount_rec.account_segment10,
496 p_adjustaccount_rec.account_segment11,
497 p_adjustaccount_rec.account_segment12,
498 p_adjustaccount_rec.account_segment13,
499 p_adjustaccount_rec.account_segment14,
500 p_adjustaccount_rec.account_segment15,
501 p_adjustaccount_rec.account_segment16,
502 p_adjustaccount_rec.account_segment17,
503 p_adjustaccount_rec.account_segment18,
504 p_adjustaccount_rec.account_segment19,
505 p_adjustaccount_rec.account_segment20,
506 p_adjustaccount_rec.account_segment21,
507 p_adjustaccount_rec.account_segment22,
508 p_adjustaccount_rec.account_segment23,
509 p_adjustaccount_rec.account_segment24,
510 p_adjustaccount_rec.account_segment25,
511 p_adjustaccount_rec.account_segment26,
512 p_adjustaccount_rec.account_segment27,
513 p_adjustaccount_rec.account_segment28,
514 p_adjustaccount_rec.account_segment29,
515 p_adjustaccount_rec.account_segment30) LOOP
516 --
517 MTL_CCEOI_VAR_PVT.G_ADJUST_ACCOUNT_ID :=
518 c_rec. code_combination_id;
519 --
520 L_counter := L_counter + 1;
521 IF L_counter > 1 THEN
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523 END IF;
524 --
525 END LOOP;
526 --
527 END IF;
528 --
529 IF L_counter = 0 THEN
530 -- the Adjustment account does NOT exist
531 RAISE FND_API.G_EXC_ERROR;
532 END IF;
533 --
534 -- END of API body
535 -- Standard check of p_commit
536 IF FND_API.to_Boolean(p_commit) THEN
537 COMMIT;
538 END IF;
539 -- Standard call to get message count and if count is 1, get message info
540 FND_MSG_PUB.Count_And_Get
541 (p_count => x_msg_count
542 , p_data => x_msg_data);
543 EXCEPTION
544 WHEN FND_API.G_EXC_ERROR THEN
545 --
546 ROLLBACK TO Validate_AdjustAccount;
547 --
548 x_return_status := FND_API.G_RET_STS_ERROR;
549 --
550 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
551 , p_data => x_msg_data);
552 --
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 --
555 ROLLBACK TO Validate_AdjustAccount;
556 --
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 --
559 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
560 , p_data => x_msg_data);
561 --
562 WHEN OTHERS THEN
563 --
564 ROLLBACK TO Validate_AdjustAccount;
565 --
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 --
568 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
569 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
570 END IF;
571 --
572 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
573 , p_data => x_msg_data);
574 END;
575 END;
576 --
577 -- Validates the count date (good for any date)
578 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
579 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
580 PROCEDURE Validate_CountDate(
581 p_api_version IN NUMBER ,
582 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
583 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
584 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
585 x_return_status OUT NOCOPY VARCHAR2 ,
586 x_msg_count OUT NOCOPY NUMBER ,
587 x_msg_data OUT NOCOPY VARCHAR2 ,
588 x_errorcode OUT NOCOPY NUMBER ,
589 p_count_date IN DATE )
590 IS
591 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
592 BEGIN
593 -- Start OF comments
594 -- API name : Validate_CountDate
595 -- TYPE : Private
596 -- Pre-reqs : None
597 -- FUNCTION :
598 -- Validates the COUNT date.
599 -- Parameters:
600 -- IN :
601 -- p_api_version IN NUMBER (required)
602 -- API Version of this procedure
603 --
604 -- p_init_msg_list IN VARCHAR2 (optional)
605 -- DEFAULT = FND_API.G_FALSE,
606 --
607 -- p_commit IN VARCHAR2 (optional)
608 -- DEFAULT = FND_API.G_FALSE
609 --
610 -- p_validation_level IN NUMBER (optional)
611 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
612 --
613 -- p_count_date IN DATE (required)
614 -- DATE OF the COUNT
615 --
616 --
617 -- OUT :
618 -- X_return_status OUT NUMBER
619 -- Result of all the operations
620 --
621 -- x_msg_count OUT NUMBER,
622 --
623 -- x_msg_data OUT VARCHAR2,
624 --
625 -- X_ErrorCode OUT NUMBER
626 -- Version: Current Version 0.9
627 -- Changed : Nothing
628 -- No Previous Version 0.0
629 -- Initial version 0.9
630 -- Notes : Note text
631 -- END OF comments
632 DECLARE
633 --
634 L_period_id NUMBER;
635 L_open_past_period BOOLEAN := FALSE;
636 L_profile_value NUMBER := 0;
637 --
638 L_api_version CONSTANT NUMBER := 0.9;
639 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountDate';
640 BEGIN
641 -- Standard start of API savepoint
642 SAVEPOINT Validate_CountDate;
643 --
644 -- Standard Call to check for call compatibility
645 IF NOT FND_API.Compatible_API_Call(l_api_version
646 , p_api_version
647 , l_api_name
648 , G_PKG_NAME) THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651 --
652 -- Initialize message list if p_init_msg_list is set to true
653 IF FND_API.to_Boolean(p_init_msg_list) THEN
654 FND_MSG_PUB.initialize;
655 END IF;
656 --
657 -- Initialisize API return status to access
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659 x_errorcode := 0;
660 --
661 -- API body
662 --
663 IF (l_debug = 1) THEN
664 MDEBUG( 'Validating Count Date ');
665 END IF;
666 -- no null DATE
667 IF p_count_date IS NULL THEN
668 x_errorcode := 59;
669 FND_MESSAGE.SET_NAME('INV', 'INV_COUNT_DATE_FUTURE');
670 FND_MSG_PUB.Add;
671 RAISE FND_API.G_EXC_ERROR;
672 END IF;
673 -- no future DATE
674 IF TRUNC(p_count_date) > TRUNC(SYSDATE) THEN
675 x_errorcode := 23;
676 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_COUNT_DATE_FUTURE');
677 FND_MSG_PUB.Add;
678 RAISE FND_API.G_EXC_ERROR;
679 END IF;
680 --
681 -- within a accounting period
682 IF(fnd_profile.defined('TRANSACTION_DATE')) THEN
683 L_profile_value := TO_NUMBER(fnd_profile.value('TRANSACTION_DATE'));
684 /* Profile value of:
685 1 = Any open period
686 2 = No past date
687 3 = No past periods
688 4 = Warn when past period
689 */
690 IF L_profile_value = 3 THEN
691 L_open_past_period := TRUE;
692 END IF;
693 ELSE
694 x_errorcode := 24;
695 FND_MESSAGE.SET_NAME('FND', 'PROFILES-CANNOT READ');
696 FND_MESSAGE.SET_TOKEN('OPTION', 'TRANSACTION_DATE', TRUE);
697 FND_MESSAGE.SET_TOKEN('ROUTINE',
698 'MTL_CC_TRANSACT_PKG.CC_TRANSACT ', TRUE);
699 FND_MSG_PUB.Add;
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 IF L_profile_value = 2 THEN
704 IF trunc(p_count_date) < TRUNC(sysdate) THEN
705 x_errorcode := 24;
706 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PAST_TXN_DATES');
707 FND_MSG_PUB.Add;
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710 END IF;
711
712 INVTTMTX.TDATECHK(
713 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID,
714 p_count_date,
715 L_period_id,
716 L_open_past_period);
717 IF L_period_id = 0 THEN
718 x_errorcode := 24;
719 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
720 FND_MSG_PUB.Add;
721 RAISE FND_API.G_EXC_ERROR;
722 ELSIF L_period_id = -1 THEN
723 x_errorcode := 24;
724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
725 ELSE
726 IF(L_profile_value = 3) AND
727 NOT(L_open_past_period) THEN
728 x_errorcode := 24;
729 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PAST_TXN_PERIODS');
730 FND_MSG_PUB.Add;
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733 END IF;
734
735
736 -- if we're here then the validation went ok so we can store the date
737 MTL_CCEOI_VAR_PVT.G_COUNT_DATE := p_count_date;
738 --
739 -- END of API body
740 -- Standard check of p_commit
741 IF FND_API.to_Boolean(p_commit) THEN
742 COMMIT;
743 END IF;
744 -- Standard call to get message count and if count is 1, get message info
745 FND_MSG_PUB.Count_And_Get
746 (p_count => x_msg_count
747 , p_data => x_msg_data);
748 EXCEPTION
749 WHEN FND_API.G_EXC_ERROR THEN
750 --
751 ROLLBACK TO Validate_CountDate;
752 --
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 --
755 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
756 , p_data => x_msg_data);
757 --
758 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
759 --
760 ROLLBACK TO Validate_CountDate;
761 --
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763 --
764 x_errorcode := -1;
765 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
766 , p_data => x_msg_data);
767 --
768 WHEN OTHERS THEN
769 --
770 ROLLBACK TO Validate_CountDate;
771 --
772 x_errorcode := -1;
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 --
775 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
776 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
777 END IF;
778 --
779 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
780 , p_data => x_msg_data);
781 END;
782 END;
783 --
784 -- Validate count header
785 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
786 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
787 PROCEDURE Validate_CountHeader(
788 p_api_version IN NUMBER ,
789 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
790 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
791 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
792 x_return_status OUT NOCOPY VARCHAR2 ,
793 x_msg_count OUT NOCOPY NUMBER ,
794 x_msg_data OUT NOCOPY VARCHAR2 ,
795 x_errorcode OUT NOCOPY VARCHAR2 ,
796 p_cycle_count_header_id IN NUMBER DEFAULT NULL,
797 p_cycle_count_header_name IN VARCHAR2 DEFAULT NULL)
798 IS
799 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
800 BEGIN
801 -- Start OF comments
802 -- API name : Validate_CountHeader
803 -- TYPE : Private
804 -- Pre-reqs : None
805 -- FUNCTION :
806 -- Validates the cycle COUNT header information. IF this cycle COUNT exists
807 -- IN the system, THEN error = 0, ELSE error = 1,2
808 -- Parameters:
809 -- IN :
810 -- p_api_version IN NUMBER (required)
811 -- API Version of this procedure
812 --
813 -- p_init_msg_level IN VARCHAR2 (optional)
814 -- DEFAULT = FND_API.G_FALSE,
815 --
816 -- p_commit IN VARCHAR2 (optional)
817 -- DEFAULT = FND_API.G_FALSE
818 --
819 -- p_validation_level IN NUMBER (optional)
820 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
821 --
822 -- p_cycle_count_header_id IN NUMBER default NULL (required - defaulted NULL)
823 -- Cycle COUNT header ID
824 --
825 -- p_cycle_count_header_name IN VARCHAR2 (optional)
826 -- Default = NULL
827 -- cycle COUNT header name, only IF ID IS missing
828 --
829 -- OUT :
830 -- X_return_status OUT NUMBER
831 -- Result of all the operations
832 --
833 -- x_msg_count OUT NUMBER,
834 --
835 -- x_msg_data OUT VARCHAR2,
836 --
837 -- X_ErrorCode OUT NUMBER
838 -- RETURN value OF the Error status
839 -- 0 = exists
840 -- -1= all other error exceptions
841 -- 1 = don't exists
842 -- 2 = invalid header
843 -- 45 = more THEN one cycle COUNT
844 -- Version: Current Version 0.9
845 -- Changed : Nothing
846 -- No Previous Version 0.0
847 -- Initial version 0.9
848 -- Notes : Note text
849 -- END OF comments
850 DECLARE
851 --
852 CURSOR L_CCHeader_Csr(ID NUMBER, NAME VARCHAR2) IS
853 SELECT *
854 FROM mtl_cycle_count_headers
855 WHERE
856 (cycle_count_header_id = ID
857 OR cycle_count_header_name= NAME);
858 --
859 L_Cycle_Count_Header_ID MTL_CYCLE_COUNT_HEADERS.Cycle_Count_Header_ID%type;
860 L_Cycle_Count_Header_Name
861 MTL_CYCLE_COUNT_HEADERS.Cycle_Count_Header_Name%type;
862 rec_counter integer := 0;
863 --
864 L_api_version CONSTANT NUMBER := 0.9;
865 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountHeader';
866 BEGIN
867 -- Standard start of API savepoint
868 SAVEPOINT Validate_CountHeader;
869 --
870 -- Standard Call to check for call compatibility
871 IF NOT FND_API.Compatible_API_Call(l_api_version
872 , p_api_version
873 , l_api_name
874 , G_PKG_NAME) THEN
875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876 END IF;
877 --
878 -- Initialize message list if p_init_msg_list is set to true
879 IF FND_API.to_Boolean(p_init_msg_list) THEN
880 FND_MSG_PUB.initialize;
881 END IF;
882 --
883 -- Initialisize API return status to access
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885 x_errorcode := 0;
886 --
887 -- API body
888 --
889 --
890 IF (l_debug = 1) THEN
891 MDEBUG( 'Validating CC Header ID');
892 END IF;
893 -- The ID gets presedence FOR the name
894 IF(P_Cycle_Count_Header_ID IS NOT NULL) THEN
895 L_Cycle_Count_Header_ID := P_Cycle_Count_Header_ID;
896 L_Cycle_Count_Header_Name := NULL;
897 ELSE
898 L_Cycle_Count_Header_ID := NULL;
899 L_Cycle_Count_Header_Name := P_Cycle_Count_Header_Name;
900 END IF;
901 --
902 FOR c_rec IN L_CCHeader_Csr(L_Cycle_Count_Header_ID,
903 L_Cycle_Count_Header_Name) LOOP
904 --
905 MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID := c_rec.cycle_count_header_id;
906 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC := c_rec;
907 --
908 rec_counter := rec_counter + 1;
909 IF(rec_counter > 1) THEN
910 -- error Cycle COUNT must be unique
911 EXIT;
912 END IF;
913 END LOOP;
914 --
915 IF(rec_counter = 1) THEN
916 -- Cycle COUNT must be valid, IF NOT error OUT
917 IF(MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.DISABLE_DATE IS NOT NULL AND
918 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.DISABLE_DATE <= sysdate)
919 THEN
920 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_HEADER');
921 FND_MSG_PUB.Add;
922 x_errorcode :=2;
923 RAISE FND_API.G_EXC_ERROR;
924 ELSE
925 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_SUCCESS');
926 FND_MSG_PUB.Add;
927 END IF;
928 ELSIF
929 (rec_counter = 0) THEN
930 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_HEADER');
931 FND_MSG_PUB.Add;
932 x_errorcode := 1;
933 RAISE FND_API.G_EXC_ERROR;
934 ELSIF
935 (rec_counter > 1) THEN
936 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_MULT_HEADER');
937 FND_MSG_PUB.Add;
938 x_errorcode := 45;
939 RAISE FND_API.G_EXC_ERROR;
940 END IF;
941 --
942 --
943 -- END of API body
944 -- Standard check of p_commit
945 IF FND_API.to_Boolean(p_commit) THEN
946 COMMIT;
947 END IF;
948 -- Standard call to get message count and if count is 1, get message info
949 FND_MSG_PUB.Count_And_Get
950 (p_count => x_msg_count
951 , p_data => x_msg_data);
952 EXCEPTION
953 WHEN FND_API.G_EXC_ERROR THEN
954 --
955 ROLLBACK TO Validate_CountHeader;
956 --
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 --
959 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
960 , p_data => x_msg_data);
961 --
962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
963 --
964 ROLLBACK TO Validate_CountHeader;
965 --
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 --
968 x_errorcode := -1;
969 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
970 , p_data => x_msg_data);
971 --
972 WHEN OTHERS THEN
973 --
974 ROLLBACK TO Validate_CountHeader;
975 --
976 x_errorcode := -1;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 --
979 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
980 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
981 END IF;
982 --
983 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
984 , p_data => x_msg_data);
985 END;
986 END;
987 --
988 -- Validate count_list_sequence
989 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
990 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
991 PROCEDURE Validate_CountListSequence(
992 p_api_version NUMBER ,
993 p_init_msg_list VARCHAR2 DEFAULT FND_API.G_FALSE,
994 p_commit VARCHAR2 DEFAULT FND_API.G_FALSE,
995 p_validation_level NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
996 x_return_status OUT NOCOPY VARCHAR2 ,
997 x_msg_count OUT NOCOPY NUMBER ,
998 x_msg_data OUT NOCOPY VARCHAR2 ,
999 x_errorcode OUT NOCOPY number ,
1000 p_cycle_count_header_id IN number ,
1001 p_cycle_count_entry_id IN number ,
1002 p_count_list_sequence IN number ,
1003 p_organization_id IN NUMBER )
1004 IS
1005 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1006 BEGIN
1007 -- Start OF comments
1008 -- API name : Validate_CountListSequence
1009 -- TYPE : Private
1010 -- Pre-reqs : None
1011 -- FUNCTION :
1012 -- Validates the cycle COUNT list sequence for the specified
1013 -- header information.
1014 -- Parameters:
1015 -- IN :
1016 -- p_api_version IN NUMBER (required)
1017 -- API Version of this procedure
1018 --
1019 -- p_init_msg_level IN VARCHAR2 (optional)
1020 -- DEFAULT = FND_API.G_FALSE,
1021 --
1022 -- p_commit IN VARCHAR2 (optional)
1023 -- DEFAULT = FND_API.G_FALSE
1024 --
1025 -- p_validation_level IN NUMBER (optional)
1026 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1027 -- 0 = FOR Export validation
1028 --
1029 -- p_cycle_count_header_id IN NUMBER (required -)
1030 -- Cycle COUNT header ID
1031 --
1032 -- p_count_list_sequence IN NUMBER (required)
1033 -- COUNT list sequence
1034 --
1035 -- p_organization_id IN NUMBER (required)
1036 -- ID OF the organization
1037 --
1038 -- OUT :
1039 -- X_return_status OUT NUMBER
1040 -- Result of all the operations
1041 --
1042 -- x_msg_count OUT NUMBER,
1043 --
1044 -- x_msg_data OUT VARCHAR2,
1045 --
1046 -- X_ErrorCode OUT NUMBER
1047 -- RETURN value OF the Error status
1048 -- 0 = exists
1049 -- -1= all other error exceptions
1050 -- 3 = multiple matches found
1051 -- 46 = do NOT exist
1052 -- 65 = Unschedule Entry Seq can be null
1053 -- 66 = Unschedule Entry Seq can be New
1054 -- Version: Current Version 0.9
1055 -- Changed : Nothing
1056 -- No Previous Version 0.0
1057 -- Initial version 0.9
1058 -- Notes : Note text
1059 -- END OF comments
1060 DECLARE
1061 --
1062 -- Exists an OPEN request Status (uncounted=1,recount=3)
1063 CURSOR L_CCListSequence_Csr(ID NUMBER, Seq NUMBER, org NUMBER) IS
1064 SELECT *
1065 FROM mtl_cycle_count_entries
1066 WHERE
1067 cycle_count_header_id = ID
1068 AND organization_id = org
1069 AND count_list_sequence= seq
1070 AND entry_status_code IN(1, 2, 3);
1071 --
1072 rec_counter integer := 0;
1073 --
1074 L_api_version CONSTANT NUMBER := 0.9;
1075 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountListSequence';
1076 BEGIN
1077 -- Standard start of API savepoint
1078 SAVEPOINT Validate_CountListSequence;
1079 --
1080 -- Standard Call to check for call compatibility
1081 IF NOT FND_API.Compatible_API_Call(l_api_version
1082 , p_api_version
1083 , l_api_name
1084 , G_PKG_NAME) THEN
1085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1086 END IF;
1087 --
1088 -- Initialize message list if p_init_msg_list is set to true
1089 IF FND_API.to_Boolean(p_init_msg_list) THEN
1090 FND_MSG_PUB.initialize;
1091 END IF;
1092 --
1093 -- Initialisize API return status to access
1094 x_return_status := FND_API.G_RET_STS_SUCCESS;
1095 x_errorcode := 0;
1096 --
1097 -- API body
1098 --
1099 IF P_Count_List_Sequence <= 0 THEN
1100 FND_MESSAGE.SET_NAME('INV', 'INV_POSITIVE_NUMBER');
1101 FND_MSG_PUB.Add;
1102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1103 END IF;
1104 --
1105 IF (l_debug = 1) THEN
1106 MDEBUG( 'Validating Seq '||to_char(P_Count_List_Sequence));
1107 MDEBUG( 'Validating Seq '||to_char(P_Cycle_Count_Header_ID));
1108 MDEBUG( 'Validating Seq '||to_char(p_organization_id));
1109 END IF;
1110 FOR c_rec IN L_CCListSequence_Csr(P_Cycle_Count_Header_ID,
1111 P_Count_List_Sequence, p_organization_id) LOOP
1112 --
1113 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC := c_rec;
1114 MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION := c_rec.revision;
1115 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER := c_rec.LOT_NUMBER;
1116 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE := c_rec.LOT_CONTROL;
1117 MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER := c_rec.SERIAL_NUMBER;
1118 MTL_CCEOI_VAR_PVT.G_OPEN_REQUEST := TRUE;
1119
1120 --
1121 rec_counter := rec_counter + 1;
1122 IF(rec_counter > 1 or c_rec.entry_status_code = 2) THEN
1123 -- error Cycle COUNT must be unique
1124 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_MULT_MATCH_REQ');
1125 FND_MSG_PUB.Add;
1126 x_errorcode := 3;
1127 RAISE FND_API.G_EXC_ERROR;
1128 END IF;
1129 END LOOP;
1130 --
1131 IF(rec_counter = 0) THEN
1132 IF p_cycle_count_entry_id IS NULL THEN
1133 IF P_Count_List_Sequence is NULL THEN
1134 IF (l_debug = 1) THEN
1135 MDEBUG( 'Validating Seq Assign 65 ');
1136 END IF;
1137 x_errorcode := 65;
1138 ELSE
1139 IF (l_debug = 1) THEN
1140 MDEBUG( 'Validating Seq Assign 66 ');
1141 END IF;
1142 x_errorcode := 66;
1143 END IF;
1144 ELSE
1145 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_UNMATCH_LISTSEQ');
1146 FND_MSG_PUB.Add;
1147 x_errorcode := 46;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150 ELSIF -- XXX why do we post success message?
1151 (rec_counter = 1) THEN
1152 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_SUCCESS');
1153 FND_MSG_PUB.Add;
1154 x_errorcode := 0;
1155 END IF;
1156 --
1157 --
1158 -- END of API body
1159 -- Standard check of p_commit
1160 IF FND_API.to_Boolean(p_commit) THEN
1161 COMMIT;
1162 END IF;
1163 -- Standard call to get message count and if count is 1, get message info
1164 FND_MSG_PUB.Count_And_Get
1165 (p_count => x_msg_count
1166 , p_data => x_msg_data);
1167 EXCEPTION
1168 WHEN FND_API.G_EXC_ERROR THEN
1169 --
1170 ROLLBACK TO Validate_CountListSequence;
1171 --
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 --
1174 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1175 , p_data => x_msg_data);
1176 --
1177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178 --
1179 ROLLBACK TO Validate_CountListSequence;
1180 --
1181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1182 --
1183 x_errorcode := -1;
1184 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1185 , p_data => x_msg_data);
1186 --
1187 WHEN OTHERS THEN
1188 --
1189 ROLLBACK TO Validate_CountListSequence;
1190 --
1191 x_errorcode := -1;
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1193 --
1194 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1195 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1196 END IF;
1197 --
1198 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1199 , p_data => x_msg_data);
1200 END;
1201 END;
1202 --
1203 -- Validate the count quantity (if negative)
1204 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
1205 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
1206 PROCEDURE Validate_CountQuantity(
1207 p_api_version IN NUMBER ,
1208 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1209 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1210 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1211 x_return_status OUT NOCOPY VARCHAR2 ,
1212 x_msg_count OUT NOCOPY NUMBER ,
1213 x_msg_data OUT NOCOPY VARCHAR2 ,
1214 x_errorcode OUT NOCOPY NUMBER ,
1215 p_count_quantity IN NUMBER )
1216 IS
1217 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1218 BEGIN
1219 -- Start OF comments
1220 -- API name : Validate_CountQuantity
1221 -- TYPE : Private
1222 -- Pre-reqs : None
1223 -- FUNCTION :
1224 -- Validates IF the COUNT quanitity IS negative. IF NOT
1225 -- it will be stored INTO the package variable
1226 -- MTL_CCEOI_VAR_PVT.G_COUNT_QUANTITY
1227 -- Parameters:
1228 -- IN :
1229 -- p_api_version IN NUMBER (required)
1230 -- API Version of this procedure
1231 --
1232 -- p_init_msg_level IN VARCHAR2 (optional)
1233 -- DEFAULT = FND_API.G_FALSE,
1234 --
1235 -- p_commit IN VARCHAR2 (optional)
1236 -- DEFAULT = FND_API.G_FALSE
1237 --
1238 -- p_validation_level IN NUMBER (optional)
1239 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1240 --
1241 -- p_count_quantity IN NUMBER (required)
1242 -- the COUNT quantity
1243 --
1244 -- OUT :
1245 -- X_return_status OUT NUMBER
1246 -- Result of all the operations
1247 --
1248 -- x_msg_count OUT NUMBER,
1249 --
1250 -- x_msg_data OUT VARCHAR2,
1251 --
1252 -- X_ErrorCode OUT NUMBER
1253 -- Version: Current Version 0.9
1254 -- Changed : Nothing
1255 -- No Previous Version 0.0
1256 -- Initial version 0.9
1257 -- Notes : Note text
1258 -- END OF comments
1259 DECLARE
1260 --
1261 L_api_version CONSTANT NUMBER := 0.9;
1262 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountQuantity';
1263 BEGIN
1264 -- Standard start of API savepoint
1265 SAVEPOINT Validate_CountQuantity;
1266 --
1267 -- Standard Call to check for call compatibility
1268 IF NOT FND_API.Compatible_API_Call(l_api_version
1269 , p_api_version
1270 , l_api_name
1271 , G_PKG_NAME) THEN
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273 END IF;
1274 --
1275 -- Initialize message list if p_init_msg_list is set to true
1276 IF FND_API.to_Boolean(p_init_msg_list) THEN
1277 FND_MSG_PUB.initialize;
1278 END IF;
1279 --
1280 -- Initialisize API return status to access
1281 x_return_status := FND_API.G_RET_STS_SUCCESS;
1282 x_errorcode := 0;
1283 --
1284 -- API body
1285 --
1286 -- With Serialized items
1287 IF (l_debug = 1) THEN
1288 MDEBUG( 'Validating Count Qty');
1289 MDEBUG('SRLNoCCD '||to_char(MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE ));
1290 END IF;
1291 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE in (2,5)
1292 THEN
1293 IF p_count_quantity > 1 or p_count_quantity IS NULL THEN
1294 IF (l_debug = 1) THEN
1295 MDEBUG( 'Validating CQty > 1 or NULL');
1296 END IF;
1297 x_errorcode := 60;
1298 FND_MESSAGE.SET_NAME('INV', 'INV_SERIAL_QTY_MUST_BE_1');
1299 FND_MSG_PUB.Add;
1300 RAISE FND_API.G_EXC_ERROR;
1301 END IF;
1302 ELSE
1303 IF p_count_quantity IS NULL THEN
1304 IF (l_debug = 1) THEN
1305 MDEBUG( 'Validating CQty is NULL');
1306 END IF;
1307 x_errorcode := 61;
1308 FND_MESSAGE.SET_NAME('INV', 'INV_GREATER_EQUAL_ZERO');
1309 FND_MSG_PUB.Add;
1310 RAISE FND_API.G_EXC_ERROR;
1311 END IF;
1312 END IF;
1313 --
1314 IF p_count_quantity < 0 THEN
1315 IF (l_debug = 1) THEN
1316 MDEBUG( 'Validating CQty < 0');
1317 END IF;
1318 x_errorcode := 22;
1319 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NEG_QTY');
1320 FND_MSG_PUB.Add;
1321 RAISE FND_API.G_EXC_ERROR;
1322 ELSE
1323 MTL_CCEOI_VAR_PVT.G_COUNT_QUANTITY := p_count_quantity;
1324 END IF;
1325 --
1326 -- END of API body
1327 -- Standard check of p_commit
1328 IF FND_API.to_Boolean(p_commit) THEN
1329 COMMIT;
1330 END IF;
1331 -- Standard call to get message count and if count is 1, get message info
1332 FND_MSG_PUB.Count_And_Get
1333 (p_count => x_msg_count
1334 , p_data => x_msg_data);
1335 EXCEPTION
1336 WHEN FND_API.G_EXC_ERROR THEN
1337 --
1338 ROLLBACK TO Validate_CountQuantity;
1339 --
1340 x_return_status := FND_API.G_RET_STS_ERROR;
1341 IF (l_debug = 1) THEN
1342 MDEBUG( 'Error CntQty-Stat= '||x_return_status);
1343 END IF;
1344 --
1345 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1346 , p_data => x_msg_data);
1347 --
1348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1349 --
1350 ROLLBACK TO Validate_CountQuantity;
1351 --
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1353 --
1354 x_errorcode := -1;
1355 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1356 , p_data => x_msg_data);
1357 --
1358 WHEN OTHERS THEN
1359 --
1360 ROLLBACK TO Validate_CountQuantity;
1361 --
1362 x_errorcode := -1;
1363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 --
1365 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1366 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1367 END IF;
1368 --
1369 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1370 , p_data => x_msg_data);
1371 END;
1372 END;
1373 --
1374 -- Validates Control information this item
1375 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
1376 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
1377 PROCEDURE Validate_Ctrol(
1378 p_api_version IN NUMBER ,
1379 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1380 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1381 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1382 x_return_status OUT NOCOPY VARCHAR2 ,
1383 x_msg_count OUT NOCOPY NUMBER ,
1384 x_msg_data OUT NOCOPY VARCHAR2 ,
1385 x_errorcode OUT NOCOPY NUMBER ,
1386 p_inventory_item_id IN NUMBER ,
1387 p_organization_id IN NUMBER ,
1388 p_locator_rec IN MTL_CCEOI_VAR_PVT.INV_LOCATOR_REC_TYPE ,
1389 p_lot_number IN VARCHAR2 ,
1390 p_revision IN VARCHAR2 ,
1391 p_serial_number IN VARCHAR2 ,
1392 p_locator_control IN NUMBER ) -- XXX not used inside!
1393 IS
1394 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1395 BEGIN
1396 -- Start OF comments
1397 -- API name : Validate_Ctrol
1398 -- TYPE : Private
1399 -- Pre-reqs : None
1400 -- FUNCTION :
1401 -- Validates SKU information. It will be determined IF the item level control
1402 -- IS specified AND the input parameter are populated.
1403 -- E.g. IF the item IS under lot control, so the lot NUMBER
1404 -- must be populated.
1405 -- The control information will be selected FROM the TABLE
1406 -- MTL_SYSTEM_ITEMS, but IF the global variables OF the
1407 -- package MTL_CCEOI_VAR_PVT are populated, no selection
1408 -- IS neccessary.
1409 -- Parameters:
1410 -- IN :
1411 -- p_api_version IN NUMBER (required)
1412 -- API Version of this procedure
1413 --
1414 -- p_init_msg_level IN VARCHAR2 (optional)
1415 -- DEFAULT = FND_API.G_FALSE,
1416 --
1417 -- p_commit IN VARCHAR2 (optional)
1418 -- DEFAULT = FND_API.G_FALSE
1419 --
1420 -- p_validation_level IN NUMBER (optional)
1421 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1422 --
1423 -- p_inventory _item_id IN NUMBER (required)
1424 -- inventory item id
1425 --
1426 -- p_organization_id IN NUMBER (required)
1427 --
1428 -- p_locator_rec MTL_CCEOI_VAR_PVT.INV_LOCATOR_REC (required)
1429 -- Locator information with segments
1430 --
1431 -- p_lot_number IN VARCHAR2 (required)
1432 -- Lot NUMBER
1433 --
1434 -- p_revision IN VARCHAR2 (required)
1435 -- Revision information
1436 --
1437 -- p_serial_number IN VARCHAR2 (required)
1438 -- serial NUMBER
1439 --
1440 -- p_locator_control IN NUMBER (required)
1441 -- IS item unter locator control
1442 -- This flag IS used to know IF the item is under locator control
1443 -- no care, IF it IS at organization-, subinventory- , OR item level
1444 --
1445 -- OUT :
1446 -- X_return_status OUT NUMBER
1447 -- Result of all the operations
1448 --
1449 -- x_msg_count OUT NUMBER,
1450 --
1451 -- x_msg_data OUT VARCHAR2,
1452 --
1453 -- X_ErrorCode OUT NUMBER
1454 -- -1= Unexpected error
1455 -- 9 = No Locator
1456 -- 11 = No revision
1457 -- 13 = No Lot
1458 -- 15 = No Serial
1459 -- Version: Current Version 0.9
1460 -- Changed : Nothing
1461 -- No Previous Version 0.0
1462 -- Initial version 0.9
1463 -- Notes : Note text
1464 -- END OF comments
1465 DECLARE
1466 --
1467 CURSOR L_ItemCtrlInfo_Csr(id IN NUMBER, org in number) IS
1468 SELECT
1469 location_control_code,
1470 serial_number_control_code,
1471 revision_qty_control_code,
1472 lot_control_code FROM mtl_system_items
1473 WHERE
1474 inventory_item_id = id
1475 and organization_id = org;
1476 --
1477 L_api_version CONSTANT NUMBER := 0.9;
1478 L_api_name CONSTANT VARCHAR2(30) := 'Validate_Ctrol';
1479 BEGIN
1480 -- Standard start of API savepoint
1481 SAVEPOINT Validate_Ctrol;
1482 --
1483 -- Standard Call to check for call compatibility
1484 IF NOT FND_API.Compatible_API_Call(l_api_version
1485 , p_api_version
1486 , l_api_name
1487 , G_PKG_NAME) THEN
1488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1489 END IF;
1490 --
1491 -- Initialize message list if p_init_msg_list is set to true
1492 IF FND_API.to_Boolean(p_init_msg_list) THEN
1493 FND_MSG_PUB.initialize;
1494 END IF;
1495 --
1496 -- Initialisize API return status to access
1497 x_return_status := FND_API.G_RET_STS_SUCCESS;
1498 x_errorcode := 0;
1499 --
1500 -- API body
1501 --
1502 IF (l_debug = 1) THEN
1503 MDEBUG( 'Validating all control ');
1504 MDEBUG( 'Validating all control-Loc '||to_char(p_locator_rec.locator_id));
1505 MDEBUG( 'Validating all control -Rev'||p_revision);
1506 END IF;
1507 -- SELECT information FROM MTL_SYSTEM_ITEMS
1508 FOR x IN L_ItemCtrlInfo_Csr(p_inventory_item_id,
1509 p_organization_id) LOOP
1510 IF (l_debug = 1) THEN
1511 MDEBUG( 'Loc Ctrl1'||to_char(MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE));
1512 END IF;
1513 if MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE
1514 is null then
1515 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE := x.LOCATION_CONTROL_CODE;
1516 IF (l_debug = 1) THEN
1517 MDEBUG( 'Loc Ctrl2'||to_char(MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE));
1518 END IF;
1519 END IF;
1520 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE := x.LOCATION_CONTROL_CODE;
1521 IF (l_debug = 1) THEN
1522 MDEBUG( 'Loc Ctrl X '||to_char(x.LOCATION_CONTROL_CODE));
1523 END IF;
1524 --
1525 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE
1526 IS NULL THEN
1527 MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE :=
1528 x.SERIAL_NUMBER_CONTROL_CODE;
1529 END IF;
1530 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION_QTY_CONTROL_CODE
1531 IS NULL THEN
1532 MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION_QTY_CONTROL_CODE :=
1533 x.REVISION_QTY_CONTROL_CODE;
1534 END IF;
1535 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE IS NULL THEN
1536 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE :=
1537 x.LOT_CONTROL_CODE;
1538 END IF;
1539 IF (l_debug = 1) THEN
1540 MDEBUG( 'Validating Ctrl Loop');
1541 END IF;
1542 END LOOP;
1543 IF (l_debug = 1) THEN
1544 mdebug('Locator Ctrl Code '||to_char(p_locator_control));
1545 mdebug('segments are1 '||p_locator_rec.locator_segment1);
1546 mdebug('segments are2 '||p_locator_rec.locator_segment2);
1547 mdebug('segments are3 '||p_locator_rec.locator_segment3);
1548 mdebug('segments are19 '||p_locator_rec.locator_segment19);
1549 mdebug('segments are20 '||p_locator_rec.locator_segment20);
1550 END IF;
1551
1552 --
1553 IF (l_debug = 1) THEN
1554 MDEBUG( 'Validating Ctrl Loop'||to_char(p_locator_rec.locator_id));
1555 END IF;
1556 --
1557 -- Check IF all VALUES are populated
1558 -- IF MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE IN(2, 3)
1559 IF p_locator_control in (2, 3) then
1560 IF (p_locator_rec.locator_id IS NULL AND
1561 p_locator_rec.locator_segment1 IS NULL and
1562 p_locator_rec.locator_segment2 IS NULL and
1563 p_locator_rec.locator_segment3 IS NULL and
1564 p_locator_rec.locator_segment4 IS NULL and
1565 p_locator_rec.locator_segment5 IS NULL and
1566 p_locator_rec.locator_segment6 IS NULL and
1567 p_locator_rec.locator_segment7 IS NULL and
1568 p_locator_rec.locator_segment8 IS NULL and
1569 p_locator_rec.locator_segment9 IS NULL and
1570 p_locator_rec.locator_segment10 IS NULL and
1571 p_locator_rec.locator_segment11 IS NULL and
1572 p_locator_rec.locator_segment12 IS NULL and
1573 p_locator_rec.locator_segment13 IS NULL and
1574 p_locator_rec.locator_segment14 IS NULL and
1575 p_locator_rec.locator_segment15 IS NULL and
1576 p_locator_rec.locator_segment16 IS NULL and
1577 p_locator_rec.locator_segment17 IS NULL and
1578 p_locator_rec.locator_segment18 IS NULL and
1579 p_locator_rec.locator_segment19 IS NULL and
1580 p_locator_rec.locator_segment20 IS NULL) THEN
1581 --
1582 IF (l_debug = 1) THEN
1583 MDEBUG( 'Validating Locator_control but no locator');
1584 END IF;
1585 x_errorcode := 9;
1586 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_LOC');
1587 FND_MSG_PUB.Add;
1588 RAISE FND_API.G_EXC_ERROR;
1589 --
1590 END IF;
1591 END IF;
1592 IF (l_debug = 1) THEN
1593 MDEBUG( 'End of Validating Ctrl'||to_char(p_locator_rec.locator_id));
1594 END IF;
1595 -- XXX should we make g_locator_id null in else clause
1596
1597
1598
1599 -- SERIAL
1600
1601
1602 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE IN (2,5)
1603 AND p_serial_number IS NULL THEN
1604
1605 IF (l_debug = 1) THEN
1606 MDEBUG( 'Validating serial_control but no serial number');
1607 mdebug('serial_control but no serial number');
1608 END IF;
1609 x_errorcode := 15;
1610 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_SERIAL');
1611 FND_MSG_PUB.Add;
1612 RAISE FND_API.G_EXC_ERROR;
1613
1614 ELSIF MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE = 0 THEN
1615 --XXX it should not ever be 0
1616 MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER := NULL;
1617 END IF;
1618
1619
1620 -- REVISION
1621
1622
1623 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION_QTY_CONTROL_CODE = 2
1624 AND p_revision IS NULL THEN
1625
1626 IF (l_debug = 1) THEN
1627 MDEBUG( 'Validating revision_control but no revision');
1628 END IF;
1629 x_errorcode := 11;
1630 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_REV');
1631 FND_MSG_PUB.Add;
1632 RAISE FND_API.G_EXC_ERROR;
1633 --
1634 ELSIF MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION_QTY_CONTROL_CODE = 1 THEN
1635 MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION := NULL;
1636 END IF;
1637
1638
1639 -- LOT
1640
1641
1642 IF MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE= 2
1643 AND p_lot_number IS NULL THEN
1644 --
1645 IF (l_debug = 1) THEN
1646 MDEBUG( 'Validating lot_control but no lot');
1647 END IF;
1648 x_errorcode := 13;
1649 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_LOT');
1650 FND_MSG_PUB.Add;
1651 RAISE FND_API.G_EXC_ERROR;
1652 --
1653 ELSIF MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE= 1 THEN
1654 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER := NULL;
1655 END IF;
1656 --
1657 -- END of API body
1658 -- Standard check of p_commit
1659 IF FND_API.to_Boolean(p_commit) THEN
1660 COMMIT;
1661 END IF;
1662 -- Standard call to get message count and if count is 1, get message info
1663 FND_MSG_PUB.Count_And_Get
1664 (p_count => x_msg_count
1665 , p_data => x_msg_data);
1666 EXCEPTION
1667 WHEN FND_API.G_EXC_ERROR THEN
1668 --
1669 ROLLBACK TO Validate_Ctrol;
1670 --
1671 x_return_status := FND_API.G_RET_STS_ERROR;
1672 --
1673 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1674 , p_data => x_msg_data);
1675 --
1676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1677 --
1678 ROLLBACK TO Validate_Ctrol;
1679 --
1680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1681 --
1682 x_errorcode := -1;
1683 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1684 , p_data => x_msg_data);
1685 --
1686 WHEN OTHERS THEN
1687 --
1688 ROLLBACK TO Validate_Ctrol;
1689 --
1690 x_errorcode := -1;
1691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1692 --
1693 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1694 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1695 END IF;
1696 --
1697 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1698 , p_data => x_msg_data);
1699 END;
1700 END;
1701 --
1702 -- Validates Count UOM or/and Unit of Measure (not specific to cc)
1703 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
1704 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
1705 PROCEDURE Validate_CountUOM(
1706 p_api_version IN NUMBER ,
1707 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1708 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1709 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1710 x_return_status OUT NOCOPY VARCHAR2 ,
1711 x_msg_count OUT NOCOPY NUMBER ,
1712 x_msg_data OUT NOCOPY VARCHAR2 ,
1713 x_errorcode OUT NOCOPY NUMBER ,
1714 p_count_uom IN VARCHAR2 DEFAULT NULL,
1715 p_count_unit_of_measure IN VARCHAR2 DEFAULT NULL,
1716 p_organization_id IN NUMBER ,
1717 p_inventory_item_id IN NUMBER )
1718 IS
1719 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1720 BEGIN
1721 -- Start OF comments
1722 -- API name : Validate_CountUOM
1723 -- TYPE : Private
1724 -- Pre-reqs : None
1725 -- FUNCTION :
1726 -- Look IN the TABLE MTL_ITEM_UOMS_VIEW
1727 -- IF the count_uom OR count_unit_of_measure IS
1728 -- presented, IF NOT it errors out.
1729 -- Parameters:
1730 -- IN :
1731 -- p_api_version IN NUMBER (required)
1732 -- API Version of this procedure
1733 --
1734 -- p_init_msg_level IN VARCHAR2 (optional)
1735 -- DEFAULT = FND_API.G_FALSE,
1736 --
1737 -- p_commit IN VARCHAR2 (optional)
1738 -- DEFAULT = FND_API.G_FALSE
1739 --
1740 -- p_validation_level IN NUMBER (optional)
1741 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1742 --
1743 -- p_count_uom IN VARCHAR2 (required - defaulted)
1744 -- default = NULL
1745 -- unit OF measure code OF the current cycle COUNT entry
1746 --
1747 -- p_count_unit_of_measure IN VARCHAR2 (optional- defaulted)
1748 -- default NULL
1749 -- Name OF the unit OF measure
1750 --
1751 -- p_organization_id IN NUMBER (required)
1752 -- ID OF the organization
1753 --
1754 -- p_inventory_item_id IN NUMBER (required)
1755 -- ID OF the inventory item
1756 --
1757 -- OUT :
1758 -- X_return_status OUT NUMBER
1759 -- Result of all the operations
1760 --
1761 -- x_msg_count OUT NUMBER,
1762 --
1763 -- x_msg_data OUT VARCHAR2,
1764 --
1765 -- X_ErrorCode OUT NUMBER
1766 -- 19 = no uom populated
1767 --
1768 -- Version: Current Version 0.9
1769 -- Changed : Nothing
1770 -- No Previous Version 0.0
1771 -- Initial version 0.9
1772 -- Notes : Note text
1773 -- END OF comments
1774 DECLARE
1775 --
1776 L_unit_of_measure MTL_ITEM_UOMS_VIEW.unit_of_measure%type;
1777 L_uom MTL_ITEM_UOMS_VIEW.uom_code%type;
1778 L_counter integer := 0;
1779 --
1780 CURSOR L_ItemUom_Csr(code IN VARCHAR2, name IN VARCHAR2,
1781 org IN NUMBER, itemid IN NUMBER) IS
1782 SELECT
1783 UOM_CODE
1784 FROM mtl_item_uoms_view
1785 WHERE
1786 organization_id = org
1787 AND inventory_item_id = itemid
1788 AND(uom_code = code OR
1789 unit_of_measure = name);
1790 --
1791 L_api_version CONSTANT NUMBER := 0.9;
1792 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountUOM';
1793 BEGIN
1794 -- Standard start of API savepoint
1795 SAVEPOINT Validate_CountUOM;
1796 --
1797 -- Standard Call to check for call compatibility
1798 IF NOT FND_API.Compatible_API_Call(l_api_version
1799 , p_api_version
1800 , l_api_name
1801 , G_PKG_NAME) THEN
1802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1803 END IF;
1804 --
1805 -- Initialize message list if p_init_msg_list is set to true
1806 IF FND_API.to_Boolean(p_init_msg_list) THEN
1807 FND_MSG_PUB.initialize;
1808 END IF;
1809 --
1810 -- Initialisize API return status to access
1811 x_return_status := FND_API.G_RET_STS_SUCCESS;
1812 x_errorcode := 0;
1813 --
1814 -- API body
1815 --
1816 IF (l_debug = 1) THEN
1817 MDEBUG( 'Validating CountUOM');
1818 END IF;
1819 IF p_count_uom IS NULL AND p_count_unit_of_measure IS NULL THEN
1820 --
1821 x_errorcode := 19;
1822 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_UOM');
1823 FND_MSG_PUB.Add;
1824 RAISE FND_API.G_EXC_ERROR;
1825 --
1826 ELSIF
1827 p_count_uom IS NOT NULL AND p_count_unit_of_measure IS NULL THEN
1828 --
1829 L_uom := p_count_uom;
1830 L_unit_of_measure := NULL;
1831 --
1832 ELSIF
1833 p_count_uom IS NULL AND p_count_unit_of_measure IS NOT NULL THEN
1834 --
1835 L_uom := NULL;
1836 L_unit_of_measure := p_count_unit_of_measure;
1837 --
1838 END IF;
1839 --
1840 FOR c_rec IN L_ItemUom_Csr(L_uom, L_unit_of_measure, p_organization_id,
1841 p_inventory_item_id) LOOP
1842 --
1843 MTL_CCEOI_VAR_PVT.G_UOM_CODE := c_rec.uom_code;
1844 L_counter := L_counter + 1;
1845 --
1846 END LOOP;
1847 --
1848 IF L_counter < 1 THEN
1849 x_errorcode := 20;
1850 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_UOM');
1851 FND_MSG_PUB.Add;
1852 RAISE FND_API.G_EXC_ERROR;
1853 END IF;
1854 --
1855 -- END of API body
1856 -- Standard check of p_commit
1857 IF FND_API.to_Boolean(p_commit) THEN
1858 COMMIT;
1859 END IF;
1860 -- Standard call to get message count and if count is 1, get message info
1861 FND_MSG_PUB.Count_And_Get
1862 (p_count => x_msg_count
1863 , p_data => x_msg_data);
1864 EXCEPTION
1865 WHEN FND_API.G_EXC_ERROR THEN
1866 --
1867 ROLLBACK TO Validate_CountUOM;
1868 --
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 --
1871 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1872 , p_data => x_msg_data);
1873 --
1874 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1875 --
1876 ROLLBACK TO Validate_CountUOM;
1877 --
1878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1879 --
1880 x_errorcode := -1;
1881 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1882 , p_data => x_msg_data);
1883 --
1884 WHEN OTHERS THEN
1885 --
1886 ROLLBACK TO Validate_CountUOM;
1887 --
1888 x_errorcode := -1;
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890 --
1891 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1892 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1893 END IF;
1894 --
1895 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1896 , p_data => x_msg_data);
1897 END;
1898 END;
1899 --
1900 -- Validates Item information
1901 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
1902 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
1903 PROCEDURE Validate_Item(
1904 p_api_version IN NUMBER ,
1905 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1906 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1907 P_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1908 x_return_status OUT NOCOPY VARCHAR2 ,
1909 x_msg_count OUT NOCOPY NUMBER ,
1910 x_msg_data OUT NOCOPY VARCHAR2 ,
1911 x_errorcode OUT NOCOPY NUMBER ,
1912 p_inventory_item_rec IN MTL_CCEOI_VAR_PVT.INV_ITEM_REC_TYPE ,
1913 p_organization_id IN NUMBER ,
1914 p_cycle_count_header_id IN NUMBER DEFAULT NULL)
1915 IS
1916 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1917 BEGIN
1918 -- Start OF comments
1919 -- API name : Validate_Item
1920 -- TYPE : Private
1921 -- Pre-reqs : None
1922 -- FUNCTION :
1923 -- This PROCEDURE validates the item information AND store the control
1924 -- information to package variables.
1925 -- The id takes presedence over the concatenated segment, which
1926 -- takes presedence over the individual segments. IS the item present
1927 -- IN the TABLE mtl_system_items, it will be checked IF it IS present IN TABLE
1928 -- mtl_cycle_count_items.
1929 -- Parameters:
1930 -- IN :
1931 -- p_api_version IN NUMBER (required)
1932 -- API Version of this procedure
1933 --
1934 -- p_init_msg_list IN VARCHAR2 (optional)
1935 -- DEFAULT = FND_API.G_FALSE,
1936 --
1937 -- p_commit IN VARCHAR2 (optional)
1938 -- DEFAULT = FND_API.G_FALSE
1939 --
1940 -- p_validation_level IN NUMBER (optional)
1941 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1942 --
1943 -- p_inventory_item_rec IN MTL_CCEOI_VAR_PVT.INV_ITEM_REC_TYPE (required)
1944 -- Item information with segments
1945 --
1946 -- p_organization_id IN NUMBER (required)
1947 -- organization ID
1948 --
1949 -- p_cycle_count_header_id IN NUMBER (optional - defaulted)
1950 -- Cycle count header id. If a values is given, check if this item
1951 -- exists for this cycle count
1952 --
1953 -- OUT :
1954 -- X_return_status OUT NUMBER
1955 -- Result of all the operations
1956 --
1957 -- x_msg_count OUT NUMBER,
1958 --
1959 -- x_msg_data OUT VARCHAR2,
1960 --
1961 -- X_ErrorCode OUT NUMBER
1962 -- 4 = NOT item
1963 -- 5 = item NOT specified with cycle COUNT
1964 -- Version: Current Version 0.9
1965 -- Changed : Nothing
1966 -- No Previous Version 0.0
1967 -- Initial version 0.9
1968 -- Notes : Note text
1969 -- END OF comments
1970 DECLARE
1971 --
1972 CURSOR L_CCItemsID_Crs(id IN VARCHAR2, cchead IN NUMBER) IS
1973 SELECT
1974 inventory_item_id
1975 FROM MTL_CYCLE_COUNT_ITEMS
1976 WHERE
1977 inventory_item_id = id
1978 AND cycle_count_header_id = cchead;
1979 --
1980 L_org INV_Validate.ORG;
1981 L_item INV_Validate.ITEM;
1982 --
1983 L_counter integer := 0;
1984 --
1985 L_api_version CONSTANT NUMBER := 0.9;
1986 L_api_name CONSTANT VARCHAR2(30) := 'Validate_Item';
1987 BEGIN
1988 -- Standard start of API savepoint
1989 SAVEPOINT Validate_Item;
1990 --
1991 -- Standard Call to check for call compatibility
1992 IF NOT FND_API.Compatible_API_Call(l_api_version
1993 , p_api_version
1994 , l_api_name
1995 , G_PKG_NAME) THEN
1996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1997 END IF;
1998 --
1999 -- Initialize message list if p_init_msg_list is set to true
2000 IF FND_API.to_Boolean(p_init_msg_list) THEN
2001 FND_MSG_PUB.initialize;
2002 END IF;
2003 --
2004 -- Initialisize API return status to access
2005 x_return_status := FND_API.G_RET_STS_SUCCESS;
2006 x_errorcode := 0;
2007 --
2008 -- API body
2009 IF (l_debug = 1) THEN
2010 MDEBUG( 'Validating Item');
2011 END IF;
2012 --
2013 -- Assigning variables to l_item record type to call
2014 -- INV_Validate.inventory_item procedure
2015 l_org.organization_id := p_organization_id;
2016 l_item.organization_id := p_organization_id;
2017 IF (l_debug = 1) THEN
2018 MDEBUG( 'Validating Item-1');
2019 END IF;
2020 l_item.inventory_item_id := p_inventory_item_rec.inventory_item_id;
2021 l_item.segment1 := p_inventory_item_rec.item_segment1;
2022 l_item.segment2 := p_inventory_item_rec.item_segment2;
2023 l_item.segment3 := p_inventory_item_rec.item_segment3;
2024 l_item.segment4 := p_inventory_item_rec.item_segment4;
2025 l_item.segment5 := p_inventory_item_rec.item_segment5;
2026 l_item.segment6 := p_inventory_item_rec.item_segment6;
2027 l_item.segment7 := p_inventory_item_rec.item_segment7;
2028 l_item.segment8 := p_inventory_item_rec.item_segment8;
2029 l_item.segment9 := p_inventory_item_rec.item_segment9;
2030 l_item.segment10 := p_inventory_item_rec.item_segment10;
2031 l_item.segment11 := p_inventory_item_rec.item_segment11;
2032 l_item.segment12 := p_inventory_item_rec.item_segment12;
2033 l_item.segment13 := p_inventory_item_rec.item_segment13;
2034 l_item.segment14 := p_inventory_item_rec.item_segment14;
2035 l_item.segment15 := p_inventory_item_rec.item_segment15;
2036 l_item.segment16 := p_inventory_item_rec.item_segment16;
2037 l_item.segment17 := p_inventory_item_rec.item_segment17;
2038 l_item.segment18 := p_inventory_item_rec.item_segment18;
2039 l_item.segment19 := p_inventory_item_rec.item_segment19;
2040 l_item.segment20 := p_inventory_item_rec.item_segment20;
2041 IF (l_debug = 1) THEN
2042 MDEBUG( 'Validating Item-2');
2043 END IF;
2044 --
2045 IF INV_Validate.Inventory_Item(L_item,
2046 L_org
2047 ) = INV_Validate.T then
2048
2049 IF (l_debug = 1) THEN
2050 MDEBUG( 'Validating Item-3');
2051 END IF;
2052 MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID :=
2053 l_item.inventory_item_id;
2054 MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION_QTY_CONTROL_CODE :=
2055 l_item.REVISION_QTY_CONTROL_CODE;
2056 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE :=
2057 l_item.LOT_CONTROL_CODE;
2058 MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER_CONTROL_CODE :=
2059 l_item.SERIAL_NUMBER_CONTROL_CODE;
2060 MTL_CCEOI_VAR_PVT.G_SKU_REC.ALLOWED_UNITS_LOOKUP_CODE :=
2061 l_item.ALLOWED_UNITS_LOOKUP_CODE;
2062 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOCATION_CONTROL_CODE :=
2063 l_item.LOCATION_CONTROL_CODE;
2064 MTL_CCEOI_VAR_PVT.G_SKU_REC.RESTRICT_LOCATORS_CODE :=
2065 l_item.RESTRICT_LOCATORS_CODE;
2066 MTL_CCEOI_VAR_PVT.G_PRIMARY_UOM_CODE := l_item.primary_uom_code;
2067 L_counter := L_counter + 1;
2068 x_errorcode := 0;
2069 END IF;
2070 IF (l_debug = 1) THEN
2071 MDEBUG( 'Validating Item-4');
2072 END IF;
2073 IF L_counter = 0 THEN
2074 x_errorcode := 4;
2075 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_ITEM');
2076 FND_MSG_PUB.Add;
2077 RAISE FND_API.G_EXC_ERROR;
2078 ELSIF
2079 l_counter = 1 THEN
2080 L_counter := 0;
2081 --
2082 -- check IF the item IS present IN mtl_cycle_count_items
2083 if p_cycle_count_header_id is not null then
2084 FOR c_rec IN L_CCItemsID_Crs(MTL_CCEOI_VAR_PVT.G_inventory_item_id,
2085 p_cycle_count_header_id) LOOP
2086 --
2087 L_counter := L_counter +1;
2088 IF L_counter > 1 THEN
2089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2090 END IF;
2091 --
2092 END LOOP;
2093 --
2094 IF L_counter = 0 THEN
2095 x_errorcode := 5;
2096 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_ITEM');
2097 FND_MSG_PUB.Add;
2098 ELSE
2099 x_errorcode := 0;
2100 END IF;
2101 end if;
2102 END IF;
2103 --
2104 -- END of API body
2105 -- Standard check of p_commit
2106 IF FND_API.to_Boolean(p_commit) THEN
2107 COMMIT;
2108 END IF;
2109 -- Standard call to get message count and if count is 1, get message info
2110 FND_MSG_PUB.Count_And_Get
2111 (p_count => x_msg_count
2112 , p_data => x_msg_data);
2113 EXCEPTION
2114 WHEN FND_API.G_EXC_ERROR THEN
2115 --
2116 ROLLBACK TO Validate_Item;
2117 --
2118 x_return_status := FND_API.G_RET_STS_ERROR;
2119 --
2120 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2121 , p_data => x_msg_data);
2122 --
2123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2124 --
2125 ROLLBACK TO Validate_Item;
2126 --
2127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2128 --
2129 x_errorcode := -1;
2130 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2131 , p_data => x_msg_data);
2132 --
2133 WHEN OTHERS THEN
2134 --
2135 ROLLBACK TO Validate_Item;
2136 --
2137 x_errorcode := -1;
2138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2139 --
2140 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2141 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2142 END IF;
2143 --
2144 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2145 , p_data => x_msg_data);
2146 END;
2147 END;
2148 --
2149 -- Validates locator information
2150 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
2151 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
2152 PROCEDURE Validate_Locator(
2153 p_api_version IN NUMBER ,
2154 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2155 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2156 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
2157 x_return_status OUT NOCOPY VARCHAR2 ,
2158 x_msg_count OUT NOCOPY NUMBER ,
2159 x_msg_data OUT NOCOPY VARCHAR2 ,
2160 x_errorcode OUT NOCOPY NUMBER ,
2161 p_locator_rec IN MTL_CCEOI_VAR_PVT.INV_LOCATOR_REC_TYPE ,
2162 p_organization_id IN NUMBER ,
2163 P_subinventory IN VARCHAR2 ,
2164 p_inventory_item_id IN NUMBER ,
2165 p_locator_control IN NUMBER ,
2166 p_control_level IN NUMBER ,
2167 p_restrict_control IN NUMBER,
2168 p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE)
2169 IS
2170 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2171 BEGIN
2172 -- Start OF comments
2173 -- API name : Validate_Locator
2174 -- TYPE : Private
2175 -- Pre-reqs : None
2176 -- FUNCTION :
2177 -- Parameters:
2178 -- IN :
2179 -- p_api_version IN NUMBER (required)
2180 -- API Version of this procedure
2181 --
2182 -- p_init_msg_level IN VARCHAR2 (optional)
2183 -- DEFAULT = FND_API.G_FALSE,
2184 --
2185 -- p_commit IN VARCHAR2 (optional)
2186 -- DEFAULT = FND_API.G_FALSE
2187 --
2188 -- p_validation_level IN NUMBER (optional)
2189 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
2190 --
2191 -- p_locator_rec MTL_CCEOI_VAR_PVT.INV_LOCATOR_REC_TYPE (required)
2192 -- Locator information with segments
2193 --
2194 -- p_organization_id IN NUMBER (required)
2195 -- organization ID
2196 --
2197 -- p_subinventory IN VARCHAR2 (required)
2198 -- Subinventory OF the item
2199 --
2200 -- p_inventory_item_id IN NUMBER (required)
2201 -- Item ID
2202 --
2203 -- p_locator_control IN NUMBER (required)
2204 -- IS the item under locator control
2205 --
2206 -- p_control_level IN NUMBER (required)
2207 -- which level controlled the locator
2208 --
2209 -- p_restrict_control IN NUMBER
2210 -- IS the item under rstrict locator control
2211 --
2212 -- OUT :
2213 -- X_return_status OUT NUMBER
2214 -- Result of all the operations
2215 --
2216 -- x_msg_count OUT NUMBER,
2217 --
2218 -- x_msg_data OUT VARCHAR2,
2219 --
2220 -- X_ErrorCode OUT NUMBER
2221 -- Version: Current Version 0.9
2222 -- Changed : Nothing
2223 -- No Previous Version 0.0
2224 -- Initial version 0.9
2225 -- Notes : Note text
2226 -- END OF comments
2227 DECLARE
2228 --
2229 L_org INV_VALIDATE.ORG;
2230 L_item INV_VALIDATE.ITEM;
2231 L_sub INV_VALIDATE.SUB;
2232 L_loc INV_VALIDATE.LOCATOR;
2233 --
2234 L_dynamic_ok CONSTANT VARCHAR2(20) := INV_Validate.EXISTS_OR_CREATE;
2235 L_dynamic_not_ok CONSTANT VARCHAR2(20) := INV_Validate.EXISTS_ONLY;
2236 --
2237 L_counter NUMBER := 0;
2238 L_Location_Id NUMBER;
2239 --
2240 L_api_version CONSTANT NUMBER := 0.9;
2241 L_api_name CONSTANT VARCHAR2(30) := 'Validate_Locator';
2242 BEGIN
2243 -- Standard start of API savepoint
2244 SAVEPOINT Validate_Locator;
2245 --
2246 -- Standard Call to check for call compatibility
2247 IF NOT FND_API.Compatible_API_Call(l_api_version
2248 , p_api_version
2249 , l_api_name
2250 , G_PKG_NAME) THEN
2251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2252 END IF;
2253 --
2254 -- Initialize message list if p_init_msg_list is set to true
2255 IF FND_API.to_Boolean(p_init_msg_list) THEN
2256 FND_MSG_PUB.initialize;
2257 END IF;
2258 --
2259 -- Initialisize API return status to access
2260 x_return_status := FND_API.G_RET_STS_SUCCESS;
2261 x_errorcode := 0;
2262 --
2263 -- API body
2264 l_org.organization_id := p_organization_id;
2265 l_sub.secondary_inventory_name := p_subinventory;
2266 l_item.inventory_item_id := MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID;
2267 l_loc.inventory_location_id := p_locator_rec.locator_id;
2268 l_loc.segment1 := p_locator_rec.locator_segment1;
2269 l_loc.segment2 := p_locator_rec.locator_segment2;
2270 l_loc.segment3 := p_locator_rec.locator_segment3;
2271 l_loc.segment4 := p_locator_rec.locator_segment4;
2272 l_loc.segment5 := p_locator_rec.locator_segment5;
2273 l_loc.segment6 := p_locator_rec.locator_segment6;
2274 l_loc.segment7 := p_locator_rec.locator_segment7;
2275 l_loc.segment8 := p_locator_rec.locator_segment8;
2276 l_loc.segment9 := p_locator_rec.locator_segment9;
2277 l_loc.segment10 := p_locator_rec.locator_segment10;
2278 l_loc.segment11 := p_locator_rec.locator_segment11;
2279 l_loc.segment12 := p_locator_rec.locator_segment12;
2280 l_loc.segment13 := p_locator_rec.locator_segment13;
2281 l_loc.segment14 := p_locator_rec.locator_segment14;
2282 l_loc.segment15 := p_locator_rec.locator_segment15;
2283 l_loc.segment16 := p_locator_rec.locator_segment16;
2284 l_loc.segment17 := p_locator_rec.locator_segment17;
2285 l_loc.segment18 := p_locator_rec.locator_segment18;
2286 l_loc.segment19 := p_locator_rec.locator_segment19;
2287 l_loc.segment20 := p_locator_rec.locator_segment20;
2288 IF (l_debug = 1) THEN
2289 mdebug(l_loc.inventory_location_id);
2290 mdebug ('one='||l_loc.segment1);
2291 mdebug ('two='||l_loc.segment2);
2292 mdebug ('thr='||l_loc.segment3);
2293 mdebug ('four='||l_loc.segment4);
2294 mdebug ('five='||l_loc.segment5);
2295 mdebug ('six='||l_loc.segment6);
2296 mdebug ('19 ='||l_loc.segment19);
2297 mdebug ('20 ='||l_loc.segment20);
2298 END IF;
2299 --
2300 IF (l_debug = 1) THEN
2301 MDEBUG( 'Validating Locator Id and Segments ');
2302 END IF;
2303 IF INV_Validate.validateLocator(l_loc,l_org,l_sub,L_dynamic_not_ok)
2304 = INV_Validate.T then
2305 MTL_CCEOI_VAR_PVT.G_LOCATOR_ID := l_loc.inventory_location_id;
2306 IF (l_debug = 1) THEN
2307 MDEBUG( 'Valid Locator Id and Segments ');
2308 END IF;
2309 L_counter := L_counter + 1;
2310 END IF;
2311 --
2312 IF (l_debug = 1) THEN
2313 MDEBUG( 'Validating Locator DynLoc1'||to_char(p_locator_control));
2314 MDEBUG( 'Validating Locator DynLoc2'||to_char(MTL_CCEOI_VAR_PVT.G_STOCK_LOCATOR_CONTROL_CODE));
2315 END IF;
2316 IF L_counter = 0 THEN
2317 IF (l_debug = 1) THEN
2318 MDEBUG( 'Validating Locator DynLocLvl '||to_char(p_restrict_control));
2319 END IF;
2320 -- the locator does NOT exist AND no dynamic entry allowed
2321 -- dynmaic NOT allowed FOR restriced locators on item level
2322 IF p_locator_control = 2 OR
2323 (p_restrict_control = 1 AND p_control_level=1) THEN
2324 x_errorcode := 10;
2325 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_LOC');
2326 FND_MSG_PUB.Add;
2327 RAISE FND_API.G_EXC_ERROR;
2328 END IF;
2329 -- Dynamic locator creation
2330 IF p_locator_control = 3 THEN
2331 IF (l_debug = 1) THEN
2332 MDEBUG( 'Validating Locator DynLoc - Yes' );
2333 END IF;
2334 IF INV_Validate.validateLocator(l_loc,l_org,l_sub,l_dynamic_ok)
2335 = INV_Validate.T then
2336 MTL_CCEOI_VAR_PVT.G_LOCATOR_ID := l_loc.inventory_location_id;
2337 ELSE
2338 IF (l_debug = 1) THEN
2339 mdebug('Error ');
2340 END IF;
2341 x_errorcode := 10;
2342 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_LOC');
2343 FND_MSG_PUB.Add;
2344 RAISE FND_API.G_EXC_ERROR;
2345 END IF;
2346 END IF;
2347 ELSE
2348 --
2349 IF (l_debug = 1) THEN
2350 MDEBUG( 'Validating Locator Restricted Loc ' );
2351 END IF;
2352 -- IF restriced locators on item level, check IF the locator IS
2353 -- present IN TABLE MTL_SECONDARY_LOCATORS
2354 IF p_restrict_control = 1 AND p_control_level = 1 THEN
2355 -- the locator must be present IN a predefined locator list
2356 L_counter := 0;
2357 IF (l_debug = 1) THEN
2358 MDEBUG( 'Validating Locator Id and Segments ');
2359 END IF;
2360 IF INV_Validate.validateLocator(l_loc,l_org,l_sub,l_item)
2361 = INV_Validate.T then
2362 L_counter := L_counter + 1;
2363 END IF;
2364
2365 IF L_counter = 0 THEN
2366 IF (l_debug = 1) THEN
2367 MDEBUG( 'Validating Locator Restricted Loc - Error ' );
2368 END IF;
2369 x_errorcode := 47;
2370 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_LOC_NOT_IN_LIST');
2371 FND_MSG_PUB.Add;
2372 RAISE FND_API.G_EXC_ERROR;
2373 END IF;
2374 END IF;
2375 x_errorcode := 0;
2376 END IF;
2377 --
2378 -- END of API body
2379 -- Standard check of p_commit
2380 IF FND_API.to_Boolean(p_commit) THEN
2381 COMMIT;
2382 END IF;
2383 -- Standard call to get message count and if count is 1, get message info
2384 FND_MSG_PUB.Count_And_Get
2385 (p_count => x_msg_count
2386 , p_data => x_msg_data);
2387 EXCEPTION
2388 WHEN FND_API.G_EXC_ERROR THEN
2389 --
2390 ROLLBACK TO Validate_Locator;
2391 --
2392 x_return_status := FND_API.G_RET_STS_ERROR;
2393 --
2394 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2395 , p_data => x_msg_data);
2396 --
2397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2398 --
2399 ROLLBACK TO Validate_Locator;
2400 --
2401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2402 --
2403 x_errorcode := -1;
2404 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2405 , p_data => x_msg_data);
2406 --
2407 WHEN OTHERS THEN
2408 --
2409 ROLLBACK TO Validate_Locator;
2410 --
2411 x_errorcode := -1;
2412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2413 --
2414 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2415 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2416 END IF;
2417 --
2418 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2419 , p_data => x_msg_data);
2420 END;
2421 END;
2422 --
2423 -- Validate the primary uom quantity
2424 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
2425 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
2426 PROCEDURE Validate_PrimaryUomQuantity(
2427 p_api_version IN NUMBER ,
2428 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2429 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2430 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
2431 x_return_status OUT NOCOPY VARCHAR2 ,
2432 x_msg_count OUT NOCOPY NUMBER ,
2433 x_msg_data OUT NOCOPY VARCHAR2 ,
2434 x_errorcode OUT NOCOPY NUMBER ,
2435 p_primary_uom_quantity IN NUMBER ,
2436 p_primary_uom_code IN VARCHAR2 )
2437 IS
2438 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2439 BEGIN
2440 -- Start OF comments
2441 -- API name : Validate_PrimaryUomQuantity
2442 -- TYPE : Private
2443 -- Pre-reqs : None
2444 -- FUNCTION :
2445 -- Validates the primary quantity.
2446 -- Parameters:
2447 -- IN :
2448 -- p_api_version IN NUMBER (required)
2449 -- API Version of this procedure
2450 --
2451 -- p_init_msg_level IN VARCHAR2 (optional)
2452 -- DEFAULT = FND_API.G_FALSE,
2453 --
2454 -- p_commit IN VARCHAR2 (optional)
2455 -- DEFAULT = FND_API.G_FALSE
2456 --
2457 -- p_validation_level IN NUMBER (optional)
2458 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
2459 --
2460 -- p_primary_uom_quantity IN NUMBER (required)
2461 -- Primary quantity
2462 --
2463 -- p_primary_uom_code IN varchar2 (required)
2464 -- Primary uom code of the current item
2465 --
2466 -- OUT :
2467 -- X_return_status OUT NUMBER
2468 -- Result of all the operations
2469 --
2470 -- x_msg_count OUT NUMBER,
2471 --
2472 -- x_msg_data OUT VARCHAR2,
2473 --
2474 -- X_ErrorCode OUT NUMBER
2475 -- 19 = no primary uom quantity
2476 -- 22 = negative primary uom quantity was specified
2477 -- 60 = Count Qty is more than 1 for Single Serialized item
2478 -- 61 = Count Qty is NULL
2479 --
2480 -- Version: Current Version 0.9
2481 -- Changed : Nothing
2482 -- No Previous Version 0.0
2483 -- Initial version 0.9
2484 -- Notes : Note text
2485 -- END OF comments
2486 DECLARE
2487 --
2488 L_api_version CONSTANT NUMBER := 0.9;
2489 L_api_name CONSTANT VARCHAR2(30) := 'Validate_PrimaryUomQuantity';
2490 BEGIN
2491 -- Standard start of API savepoint
2492 SAVEPOINT Validate_PrimaryUomQuantity;
2493 --
2494 -- Standard Call to check for call compatibility
2495 IF NOT FND_API.Compatible_API_Call(l_api_version
2496 , p_api_version
2497 , l_api_name
2498 , G_PKG_NAME) THEN
2499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2500 END IF;
2501 --
2502 -- Initialize message list if p_init_msg_list is set to true
2503 IF FND_API.to_Boolean(p_init_msg_list) THEN
2504 FND_MSG_PUB.initialize;
2505 END IF;
2506 --
2507 -- Initialisize API return status to access
2508 x_return_status := FND_API.G_RET_STS_SUCCESS;
2509 x_errorcode := 0;
2510 --
2511 -- API body
2512 IF (l_debug = 1) THEN
2513 MDEBUG( 'Validating PUOM Qty');
2514 END IF;
2515
2516 IF p_primary_uom_quantity is NULL THEN
2517 x_errorcode := 19;
2518 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_UOM');
2519 FND_MSG_PUB.Add;
2520 RAISE FND_API.G_EXC_ERROR;
2521 ELSE
2522 MTL_CCEOI_VAR_PVT.G_UOM_CODE := MTL_CCEOI_VAR_PVT.G_PRIMARY_UOM_CODE;
2523 Validate_CountQuantity(
2524 p_api_version => 0.9,
2525 x_return_status => x_return_status,
2526 x_msg_count => x_msg_count,
2527 x_msg_data => x_msg_data,
2528 x_errorcode => x_errorcode,
2529 p_count_quantity => p_primary_uom_quantity);
2530 END IF;
2531
2532 --
2533 -- END of API body
2534 -- Standard check of p_commit
2535 IF FND_API.to_Boolean(p_commit) THEN
2536 COMMIT;
2537 END IF;
2538 -- Standard call to get message count and if count is 1, get message info
2539 FND_MSG_PUB.Count_And_Get
2540 (p_count => x_msg_count
2541 , p_data => x_msg_data);
2542 EXCEPTION
2543 WHEN FND_API.G_EXC_ERROR THEN
2544 --
2545 ROLLBACK TO Validate_PrimaryUomQuantity;
2546 --
2547 x_return_status := FND_API.G_RET_STS_ERROR;
2548 --
2549 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2550 , p_data => x_msg_data);
2551 --
2552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2553 --
2554 ROLLBACK TO Validate_PrimaryUomQuantity;
2555 --
2556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2557 --
2558 x_errorcode := -1;
2559 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2560 , p_data => x_msg_data);
2561 --
2562 WHEN OTHERS THEN
2563 --
2564 ROLLBACK TO Validate_PrimaryUomQuantity;
2565 --
2566 x_errorcode := -1;
2567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2568 --
2569 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2570 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2571 END IF;
2572 --
2573 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2574 , p_data => x_msg_data);
2575 END;
2576 END;
2577 --
2578 -- Validates subinventory
2579 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
2580 --OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
2581 PROCEDURE Validate_Subinv(
2582 p_api_version IN NUMBER ,
2583 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2584 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2585 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
2586 x_return_status OUT NOCOPY VARCHAR2 ,
2587 x_msg_count OUT NOCOPY NUMBER ,
2588 x_msg_data OUT NOCOPY VARCHAR2 ,
2589 x_errorcode OUT NOCOPY NUMBER ,
2590 p_subinventory IN VARCHAR2 ,
2591 p_organization_id IN NUMBER,
2592 p_orientation_code IN NUMBER DEFAULT MTL_CCEOI_VAR_PVT.G_ORIENTATION_CODE,
2593 p_cycle_count_header_id IN NUMBER DEFAULT MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID)
2594 IS
2595 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2596 BEGIN
2597 -- Start OF comments
2598 -- API name : Validate_SubInv
2599 -- TYPE : Private
2600 -- Pre-reqs : None
2601 -- FUNCTION :
2602 -- This PROCEDURE validates the subinventory. There two level
2603 -- validate on organization level OR on subinventory level
2604 -- Parameters:
2605 -- IN :
2606 -- p_api_version IN NUMBER (required)
2607 -- API Version of this procedure
2608 --
2609 -- p_init_msg_level IN VARCHAR2 (optional)
2610 -- DEFAULT = FND_API.G_FALSE,
2611 --
2612 -- p_commit IN VARCHAR2 (optional)
2613 -- DEFAULT = FND_API.G_FALSE
2614 --
2615 -- p_validation_level IN NUMBER (optional)
2616 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
2617 --
2618 -- p_subinventory IN NUMBER (required)
2619 -- subinventory OF the item
2620 --
2621 -- p_orientation_code IN NUMBER (required - defaulted)
2622 -- defaulted =
2623 -- MTL_CCEOI_VAR_PVT.G_ORIENTATION_CODE,
2624 -- 1 = organization level
2625 -- 2 = subinventory level
2626 --
2627 -- p_cycle_count_header_id IN NUMBER (required - defaulted)
2628 -- default =
2629 -- MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID
2630 --
2631 --
2632 -- OUT :
2633 -- X_return_status OUT NUMBER
2634 -- Result of all the operations
2635 --
2636 -- x_msg_count OUT NUMBER,
2637 --
2638 -- x_msg_data OUT VARCHAR2,
2639 --
2640 -- X_ErrorCode OUT NUMBER
2641 -- 6 = Subinv IS missing
2642 -- 7 = NOT assiociated with this cycle COUNT
2643 -- 8 = NOT quantity tracked
2644 --
2645 -- Version: Current Version 0.9
2646 -- Changed : Nothing
2647 -- No Previous Version 0.0
2648 -- Initial version 0.9
2649 -- Notes : Note text
2650 -- END OF comments
2651 DECLARE
2652 --
2653 CURSOR L_CCSubs_Csr(sub IN VARCHAR2, CCId IN NUMBER) IS
2654 SELECT
2655 SUBINVENTORY
2656 FROM MTL_CC_SUBINVENTORIES
2657 WHERE
2658 SUBINVENTORY = sub
2659 AND cycle_count_header_id = CCId;
2660 --
2661 l_org INV_Validate.ORG;
2662 l_sub INV_Validate.SUB;
2663 L_counter integer := 0;
2664 --
2665 L_api_version CONSTANT NUMBER := 0.9;
2666 L_api_name CONSTANT VARCHAR2(30) := 'Validate_SubInv';
2667 BEGIN
2668 -- Standard start of API savepoint
2669 SAVEPOINT Validate_SubInv;
2670 --
2671 -- Standard Call to check for call compatibility
2672 IF NOT FND_API.Compatible_API_Call(l_api_version
2673 , p_api_version
2674 , l_api_name
2675 , G_PKG_NAME) THEN
2676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2677 END IF;
2678 --
2679 -- Initialize message list if p_init_msg_list is set to true
2680 IF FND_API.to_Boolean(p_init_msg_list) THEN
2681 FND_MSG_PUB.initialize;
2682 END IF;
2683 --
2684 -- Initialisize API return status to access
2685 x_return_status := FND_API.G_RET_STS_SUCCESS;
2686 x_errorcode := 0;
2687 --
2688 -- API body
2689 IF p_subinventory IS NULL THEN
2690 x_errorcode := 6;
2691 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_SUB');
2692 FND_MSG_PUB.Add;
2693 RAISE FND_API.G_EXC_ERROR;
2694 END IF;
2695 --
2696 IF (l_debug = 1) THEN
2697 MDEBUG( 'Validating Subinv ');
2698 END IF;
2699 -- Cycle COUNT organization level
2700 IF p_orientation_code=1 or p_orientation_code=2 THEN
2701 --
2702 l_org.organization_id := p_organization_id;
2703 l_sub.secondary_inventory_name := p_subinventory ;
2704 --
2705 IF INV_Validate.subinventory(L_sub,L_org) = INV_Validate.T then
2706 MTL_CCEOI_VAR_PVT.G_SUB_LOCATOR_TYPE := l_sub.LOCATOR_TYPE;
2707 IF l_sub.QUANTITY_TRACKED <> 1 THEN
2708 x_errorcode := 8;
2709 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NON_QTY_TRKD_SUB');
2710 FND_MSG_PUB.Add;
2711 RAISE FND_API.G_EXC_ERROR;
2712 END IF;
2713 L_counter := L_counter + 1;
2714 END IF;
2715
2716 --
2717 IF L_counter < 1 THEN
2718 x_errorcode := 6;
2719 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_SUB');
2720 FND_MSG_PUB.Add;
2721 RAISE FND_API.G_EXC_ERROR;
2722 END IF;
2723 --
2724 END IF;
2725 -- Cycle COUNT subinventory
2726 IF p_orientation_code=2 THEN
2727 --
2728 FOR c_rec IN L_CCSubs_Csr(p_subinventory,
2729 p_cycle_count_header_id) LOOP
2730 L_counter := L_counter + 1;
2731 --
2732 END LOOP;
2733 --
2734 IF L_counter < 1 THEN
2735 x_errorcode := 7;
2736 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_SUB');
2737 FND_MSG_PUB.Add;
2738 RAISE FND_API.G_EXC_ERROR;
2739 END IF;
2740 END IF;
2741
2742 -- if validation went ok set global subinventory variable to
2743 -- the current validated subinventory
2744 mtl_cceoi_var_pvt.G_SUBINVENTORY := p_subinventory;
2745 --
2746 --
2747 -- END of API body
2748 -- Standard check of p_commit
2749 IF FND_API.to_Boolean(p_commit) THEN
2750 COMMIT;
2751 END IF;
2752 -- Standard call to get message count and if count is 1, get message info
2753 FND_MSG_PUB.Count_And_Get
2754 (p_count => x_msg_count
2755 , p_data => x_msg_data);
2756 EXCEPTION
2757 WHEN FND_API.G_EXC_ERROR THEN
2758 --
2759 ROLLBACK TO Validate_SubInv;
2760 --
2761 x_return_status := FND_API.G_RET_STS_ERROR;
2762 --
2763 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2764 , p_data => x_msg_data);
2765 --
2766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2767 --
2768 ROLLBACK TO Validate_SubInv;
2769 --
2770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2771 --
2772 x_errorcode := -1;
2773 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2774 , p_data => x_msg_data);
2775 --
2776 WHEN OTHERS THEN
2777 --
2778 ROLLBACK TO Validate_SubInv;
2779 --
2780 x_errorcode := -1;
2781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2782 --
2783 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2784 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2785 END IF;
2786 --
2787 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2788 , p_data => x_msg_data);
2789 END;
2790 END;
2791
2792 -- This function seems to be pasted from INVCORE.pld
2793 -- along with no_neg_balance and it seems that they carried over some
2794 -- extra unnecessary stuff (no_neg_balance always returns G_FALSE because of
2795 -- the way this function is called it is never passed p_restrict, p_action
2796 -- p_neg_balance. What exactly did we try to achieve here?
2797
2798 -- Is the item under Locator control
2799 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data
2800 --x_locator_control,x_level OUT parameters to comply with GSCC File.Sql.39
2801 --standard. Bug:4410902
2802 PROCEDURE Locator_Control(
2803 p_api_version IN NUMBER ,
2804 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2805 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2806 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
2807 x_return_status OUT NOCOPY VARCHAR2 ,
2808 x_msg_count OUT NOCOPY NUMBER ,
2809 x_msg_data OUT NOCOPY VARCHAR2 ,
2810 p_org_control IN NUMBER ,
2811 p_sub_control IN NUMBER ,
2812 p_item_control IN NUMBER DEFAULT NULL,
2813 p_restrict_flag IN NUMBER DEFAULT NULL,
2814 p_neg_flag IN NUMBER DEFAULT NULL,
2815 p_action IN NUMBER DEFAULT NULL,
2816 x_locator_control OUT NOCOPY NUMBER ,
2817 x_level OUT NOCOPY NUMBER )
2818 IS
2819 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2820 BEGIN
2821 -- Start OF comments
2822 -- API name : Locator_Control
2823 -- TYPE : Private
2824 -- Pre-reqs : None
2825 -- FUNCTION :
2826 -- Parameters:
2827 -- IN :
2828 -- p_api_version IN NUMBER (required)
2829 -- API Version of this procedure
2830 --
2831 -- p_init_msg_list IN VARCHAR2 (optional)
2832 -- DEFAULT = FND_API.G_FALSE,
2833 --
2834 -- p_commit IN VARCHAR2 (optional)
2835 -- DEFAULT = FND_API.G_FALSE
2836 --
2837 -- p_validation_level IN NUMBER (optional)
2838 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
2839 --
2840 -- p_org_control IN NUMBER (required)
2841 -- org level control (stock_locator_control_code)
2842 --
2843 -- p_sub_control IN number (required)
2844 -- Subinventory locator control (locator_type)
2845 --
2846 -- p_item_control IN number (optional)
2847 -- default NULL
2848 -- item locator control
2849 --
2850 -- p_restrict_flag IN Number (optional)
2851 -- default NULL
2852 --
2853 -- p_Neg_flag IN Number (optional)
2854 -- default NULL
2855 --
2856 -- p_action IN Number (optional)
2857 -- default NULL
2858 --
2859 -- OUT :
2860 -- X_return_status OUT NUMBER
2861 -- Result of all the operations
2862 --
2863 -- x_msg_count OUT NUMBER,
2864 --
2865 -- x_msg_data OUT VARCHAR2,
2866 --
2867 -- x_locator_control OUT NUMBER
2868 -- Locator control statement
2869 --
2870 -- x_level OUT NUMBER
2871 -- 1 = organization level
2872 -- 2 = Subinventory level
2873 -- 3 = Item level
2874 --
2875 -- Version: Current Version 0.9
2876 -- Changed : Nothing
2877 -- No Previous Version 0.0
2878 -- Initial version 0.9
2879 -- Notes : Note text
2880 -- END OF comments
2881 DECLARE
2882 --
2883 VALUE VARCHAR2(2000);
2884 locator_control NUMBER := 0;
2885 control_level integer := 0;
2886 --
2887 L_api_version CONSTANT NUMBER := 0.9;
2888 L_api_name CONSTANT VARCHAR2(30) := 'Locator_Control';
2889 BEGIN
2890 -- Standard start of API savepoint
2891 SAVEPOINT Locator_Control;
2892 --
2893 -- Standard Call to check for call compatibility
2894 IF NOT FND_API.Compatible_API_Call(l_api_version
2895 , p_api_version
2896 , l_api_name
2897 , G_PKG_NAME) THEN
2898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2899 END IF;
2900 --
2901 -- Initialize message list if p_init_msg_list is set to true
2902 IF FND_API.to_Boolean(p_init_msg_list) THEN
2903 FND_MSG_PUB.initialize;
2904 END IF;
2905 --
2906 -- Initialisize API return status to access
2907 x_return_status := FND_API.G_RET_STS_SUCCESS;
2908 --
2909 -- API body
2910 --
2911 IF (l_debug = 1) THEN
2912 MDEBUG( 'Validating locator control ');
2913 mdebug('org_control '||to_char(p_org_control));
2914 mdebug('sub_control'||to_char(p_sub_control));
2915 mdebug('item_control'||to_char(p_item_control));
2916 END IF;
2917 IF(p_org_control = 1) THEN
2918 locator_control := 1;
2919 control_level := 1;
2920 ELSIF
2921 (p_org_control = 2) THEN
2922 locator_control := 2;
2923 control_level := 1;
2924 ELSIF
2925 (p_org_control = 3) THEN
2926 locator_control := 3;
2927 IF(no_neg_balance(p_restrict_flag,
2928 p_neg_flag, p_action)= FND_API.G_TRUE) THEN
2929 locator_control := 2;
2930 IF (l_debug = 1) THEN
2931 mdebug('2.LOCATOR control (locator control) '||x_return_status);
2932 END IF;
2933 END IF;
2934 control_level := 1;
2935 ELSIF
2936 (p_org_control = 4) THEN
2937 IF(p_sub_control = 1) THEN
2938 locator_control := 1;
2939 control_level := 2;
2940 ELSIF
2941 (p_sub_control = 2) THEN
2942 locator_control := 2;
2943 control_level := 2;
2944 ELSIF
2945 (p_sub_control = 3) THEN
2946 locator_control := 3;
2947 IF(no_neg_balance(p_restrict_flag,
2948 p_neg_flag, p_action)= FND_API.G_TRUE) THEN
2949 locator_control := 2;
2950 IF (l_debug = 1) THEN
2951 mdebug('3.LOCATOR control (locator control) '||x_return_status);
2952 END IF;
2953 END IF;
2954 control_level := 2;
2955 ELSIF
2956 (p_sub_control = 5) THEN
2957 IF(p_item_control = 1) THEN
2958 locator_control := 1;
2959 control_level := 3;
2960 ELSIF
2961 (p_item_control = 2) THEN
2962 locator_control := 2;
2963 control_level := 3;
2964 ELSIF
2965 (p_item_control = 3) THEN
2966 locator_control := 3;
2967 IF(no_neg_balance(p_restrict_flag,
2968 p_neg_flag, p_action)= FND_API.G_TRUE) THEN
2969 locator_control := 2;
2970 IF (l_debug = 1) THEN
2971 mdebug('4.LOCATOR control (locator control) '||x_return_status);
2972 END IF;
2973 END IF;
2974 control_level := 3;
2975 ELSIF
2976 (p_item_control IS NULL) THEN
2977 locator_control := p_sub_control;
2978 control_level := 2;
2979 ELSE
2980 VALUE := p_item_control;
2981 app_exception.invalid_argument('LOCATOR.CONTROL',
2982 'ITEM_LOCATOR_CONTROL',
2983 VALUE);
2984 END IF;
2985 ELSE
2986 VALUE := p_sub_control;
2987 app_exception.invalid_argument('LOCATOR.CONTROL',
2988 'SUB_LOCATOR_CONTROL',
2989 VALUE);
2990 END IF;
2991
2992 ELSE
2993 VALUE := p_org_control;
2994 app_exception.invalid_argument('LOCATOR.CONTROL',
2995 'ORG_LOCATOR_CONTROL',
2996 VALUE);
2997 END IF;
2998 x_locator_control := locator_control;
2999 x_level := control_level;
3000 IF (l_debug = 1) THEN
3001 mdebug('2.LOCATOR control (locator control) '||x_return_status);
3002 END IF;
3003 --
3004 -- END of API body
3005 -- Standard check of p_commit
3006 IF FND_API.to_Boolean(p_commit) THEN
3007 COMMIT;
3008 END IF;
3009 -- Standard call to get message count and if count is 1, get message info
3010 FND_MSG_PUB.Count_And_Get
3011 (p_count => x_msg_count
3012 , p_data => x_msg_data);
3013 EXCEPTION
3014 WHEN FND_API.G_EXC_ERROR THEN
3015 --
3016 ROLLBACK TO Locator_Control;
3017 --
3018 x_return_status := FND_API.G_RET_STS_ERROR;
3019 --
3020 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3021 , p_data => x_msg_data);
3022 --
3023 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3024 --
3025 ROLLBACK TO Locator_Control;
3026 --
3027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028 --
3029 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3030 , p_data => x_msg_data);
3031 --
3032 WHEN OTHERS THEN
3033 --
3034 ROLLBACK TO Locator_Control;
3035 --
3036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3037 --
3038 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3039 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3040 END IF;
3041 --
3042 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3043 , p_data => x_msg_data);
3044 END;
3045 END;
3046
3047 --
3048 -- XXX ??? very strange function
3049 -- the name does not seem to coincide with whatever it is doing
3050 FUNCTION No_Neg_Balance(
3051 restrict_flag IN NUMBER ,
3052 neg_flag IN NUMBER DEFAULT 38,
3053 action IN NUMBER DEFAULT 38)
3054 RETURN VARCHAR2 IS
3055 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3056 BEGIN
3057 DECLARE
3058 VALUE VARCHAR2(2000);
3059 DO_NOT VARCHAR2(10);
3060 BEGIN
3061 IF (l_debug = 1) THEN
3062 MDEBUG( 'Validating No Neg Bal ');
3063 END IF;
3064 IF(restrict_flag = 2 OR restrict_flag IS NULL) THEN
3065 IF(neg_flag = 2) THEN
3066 IF(action = 1 OR action = 2 OR action = 3 OR
3067 action = 21 OR action = 30 OR action = 32) THEN
3068 DO_NOT := FND_API.G_TRUE;
3069 ELSE
3070 DO_NOT := FND_API.G_FALSE;
3071 END IF;
3072 ELSE
3073 DO_NOT := FND_API.G_FALSE;
3074 -- VALUE := neg_flag;
3075 -- app_exception.invalid_argument('LOCATOR.NO_NEG_BALACE',
3076 -- 'NEG_FLAG',VALUE);
3077 END IF;
3078 ELSIF
3079 (restrict_flag = 1) THEN
3080 DO_NOT := FND_API.G_TRUE;
3081 ELSE
3082 VALUE := restrict_flag;
3083 app_exception.invalid_argument('LOCATOR.NO_NEG_BALANCE',
3084 'RESTRICT_FLAG',
3085 VALUE);
3086 END IF;
3087 RETURN DO_NOT;
3088 END;
3089 END;
3090 --
3091 --
3092 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data,x_errorcode
3093 --P_Location_id OUT parameters to comply with GSCC File.Sql.39 standard .Bug:4410902
3094 PROCEDURE Inv_Dlocator_Create(
3095 P_Api_Version IN NUMBER ,
3096 P_Init_Msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3097 P_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3098 P_Validation_Level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
3099 X_Return_Status OUT NOCOPY VARCHAR2 ,
3100 X_Msg_Count OUT NOCOPY NUMBER ,
3101 X_Msg_Data OUT NOCOPY VARCHAR2 ,
3102 X_Errorcode OUT NOCOPY NUMBER ,
3103 P_Location_id OUT NOCOPY NUMBER,
3104 P_Segment1 IN VARCHAR2 DEFAULT NULL,
3105 P_Segment2 IN VARCHAR2 DEFAULT NULL,
3106 P_Segment3 IN VARCHAR2 DEFAULT NULL,
3107 P_Segment4 IN VARCHAR2 DEFAULT NULL,
3108 P_Segment5 IN VARCHAR2 DEFAULT NULL,
3109 P_Segment6 IN VARCHAR2 DEFAULT NULL,
3110 P_Segment7 IN VARCHAR2 DEFAULT NULL,
3111 P_Segment8 IN VARCHAR2 DEFAULT NULL,
3112 P_Segment9 IN VARCHAR2 DEFAULT NULL,
3113 P_Segment10 IN VARCHAR2 DEFAULT NULL,
3114 P_Segment11 IN VARCHAR2 DEFAULT NULL,
3115 P_Segment12 IN VARCHAR2 DEFAULT NULL,
3116 P_Segment13 IN VARCHAR2 DEFAULT NULL,
3117 P_Segment14 IN VARCHAR2 DEFAULT NULL,
3118 P_Segment15 IN VARCHAR2 DEFAULT NULL,
3119 P_Segment16 IN VARCHAR2 DEFAULT NULL,
3120 P_Segment17 IN VARCHAR2 DEFAULT NULL,
3121 P_Segment18 IN VARCHAR2 DEFAULT NULL,
3122 P_Segment19 IN VARCHAR2 DEFAULT NULL,
3123 P_Segment20 IN VARCHAR2 DEFAULT NULL,
3124 P_Subinv IN VARCHAR2,
3125 P_Organization_Id IN NUMBER,
3126 p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE
3127 ) IS
3128 -- end of parameter
3129 --
3130 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3131 BEGIN
3132 -- Start OF comments
3133 -- API name : Get_Item_Cost
3134 -- TYPE : Private
3135 -- Pre-reqs : None
3136 -- FUNCTION :
3137 -- selects the cost OF the specific item
3138 -- Parameters:
3139 -- IN :
3140 -- p_api_version IN NUMBER (required)
3141 -- API Version of this procedure
3142 --
3143 -- p_init_msg_level IN VARCHAR2 (optional)
3144 -- DEFAULT = FND_API.G_FALSE
3145 --
3146 -- p_commit IN VARCHAR2 (optional)
3147 -- DEFAULT = FND_API.G_FALSE
3148 --
3149 -- p_validation_level IN NUMBER (optional)
3150 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
3151 --
3152 -- Locator Segments 1..20 (optional)
3153 --
3154 -- P_Subinv IN VARCHAR2 (required)
3155 -- p_organization_id IN NUMBER (required)
3156 -- ID OF the organization
3157 --
3158 -- OUT :
3159 -- P_Location_Id OUT NUMBER
3160 -- X_return_status OUT NUMBER
3161 -- Result of all the operations
3162 --
3163 -- x_msg_count OUT NUMBER,
3164 --
3165 -- x_msg_data OUT VARCHAR2,
3166 --
3167 -- x_errorcode OUT NUMBER ,
3168 --
3169 -- Version: Current Version 0.9
3170 -- Changed : Nothing
3171 -- No Previous Version 0.0
3172 -- Initial version 0.9
3173 -- Notes : Note text
3174 -- END OF comments
3175
3176
3177 DECLARE
3178 --
3179 L_api_version CONSTANT NUMBER := 0.9;
3180 L_api_name CONSTANT VARCHAR2(30) := 'Inv_Dlocator_Create';
3181 L_structure_number NUMBER := 101;
3182 L_success BOOLEAN;
3183 L_appl_short_name VARCHAR2(10) := 'INV';
3184 L_new_ccid NUMBER;
3185 L_keyval_mode VARCHAR2(20); -- := 'CREATE_COMBINATION';
3186 L_key_flex_code VARCHAR2(20) := 'MTLL';
3187 L_keystat_val BOOLEAN ;
3188 L_concat_segs VARCHAR2(2000) ;
3189 L_n_segments NUMBER ;
3190 L_i NUMBER := 0;
3191 L_j NUMBER := 0;
3192 L_Tsegment_array FND_FLEX_EXT.SegmentArray;
3193 L_segment_array FND_FLEX_EXT.SegmentArray;
3194 L_delim varchar2(10) := fnd_flex_ext.get_delimiter(L_appl_short_name,
3195 L_key_flex_code,
3196 L_structure_number);
3197 BEGIN
3198
3199 -- Standard start of API savepoint
3200 SAVEPOINT Inv_Dlocator_Create;
3201 --
3202 -- for Testing marked by suresh
3203 -- Standard Call to check for call compatibility
3204 IF NOT FND_API.Compatible_API_Call(l_api_version
3205 , p_api_version
3206 , l_api_name
3207 , G_PKG_NAME) THEN
3208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3209 END IF;
3210
3211 -- Initialize message list if p_init_msg_list is set to true
3212 IF FND_API.to_Boolean(p_init_msg_list) THEN
3213 FND_MSG_PUB.initialize;
3214 END IF;
3215
3216
3217 -- Initialisize API return status to access
3218 x_return_status := FND_API.G_RET_STS_SUCCESS;
3219 --
3220 -- API body
3221 --
3222 BEGIN
3223
3224 -- in case of simulation just check validity of locator
3225 IF FND_API.to_Boolean(p_simulate) THEN
3226 l_keyval_mode := 'CHECK_COMBINATION';
3227 ELSE
3228 --l_keyval_mode := 'CREATE_COMBINATION';
3229 l_keyval_mode := 'CREATE_COMB_NO_AT';
3230 END IF;
3231 /* Commented the above statement of l_keyval_mode for bug 1881366 .
3232 INstead of CREATE_COMBINATIO,used new operation code CREATE_COMB_NO_AT*/
3233
3234 SELECT count(segment_num) into L_n_segments from
3235 fnd_id_flex_segments
3236 where application_id = 401
3237 and id_flex_code = L_key_flex_code
3238 and enabled_flag = 'Y'
3239 order by segment_num;
3240 EXCEPTION
3241 WHEN OTHERS THEN NULL;
3242 END;
3243 IF L_delim is NULL then
3244 IF (l_debug = 1) THEN
3245 mdebug('Delimeter is NULL...Error');
3246 END IF;
3247 END IF;
3248 IF (l_debug = 1) THEN
3249 mdebug('6');
3250 END IF;
3251 L_Tsegment_array(1):= P_segment1;
3252 L_Tsegment_array(2):= P_segment2;
3253 L_Tsegment_array(3):= P_segment3;
3254 L_Tsegment_array(4):= P_segment4;
3255 L_Tsegment_array(5):= P_segment5;
3256 L_Tsegment_array(6):= P_segment6;
3257 L_Tsegment_array(7):= P_segment7;
3258 L_Tsegment_array(8):= P_segment8;
3259 L_Tsegment_array(9):= P_segment9;
3260 L_Tsegment_array(10):= P_segment10;
3261 L_Tsegment_array(11):= P_segment11;
3262 L_Tsegment_array(12):= P_segment12;
3263 L_Tsegment_array(13):= P_segment13;
3264 L_Tsegment_array(14):= P_segment14;
3265 L_Tsegment_array(15):= P_segment15;
3266 L_Tsegment_array(16):= P_segment16;
3267 L_Tsegment_array(17):= P_segment17;
3268 L_Tsegment_array(18):= P_segment18;
3269 L_Tsegment_array(19):= P_segment19;
3270 L_Tsegment_array(20):= P_segment20;
3271 --
3272 L_j := 1;
3273 LOOP
3274 EXIT WHEN L_j > L_n_segments;
3275 L_segment_array(L_j) := NULL ;
3276 L_j := L_j + 1;
3277 END LOOP;
3278 --
3279 L_i := 1;
3280 L_j := 1;
3281 LOOP
3282 EXIT WHEN L_i > 20;
3283 IF L_Tsegment_array(L_i) IS NOT NULL THEN
3284 L_segment_array(L_j) := L_Tsegment_array(L_i);
3285 L_j := L_j + 1;
3286 END IF;
3287 L_i := L_i + 1;
3288 END LOOP;
3289 -- Use the FND_FLEX_EXT pacakge to concatenate the segments
3290 --
3291 L_concat_segs := fnd_flex_ext.concatenate_segments(L_n_segments,
3292 L_segment_array,
3293 L_delim);
3294 IF (l_debug = 1) THEN
3295 mdebug('Concat_segs : '||L_concat_segs);
3296 mdebug('Concat_segs Delim : '||L_delim);
3297 mdebug('Concat_segs Nsegments: '||to_char(L_n_segments));
3298 END IF;
3299 --
3300
3301 L_keystat_val := FND_FLEX_KEYVAL.Validate_Segs(
3302 OPERATION => L_keyval_mode,
3303 APPL_SHORT_NAME => 'INV',
3304 KEY_FLEX_CODE => L_key_flex_code,
3305 STRUCTURE_NUMBER=> L_structure_number,
3306 CONCAT_SEGMENTS => L_Concat_Segs,
3307 VALUES_OR_IDS => 'V',
3308 DATA_SET => P_Organization_Id
3309 );
3310
3311 x_msg_data := fnd_flex_keyval.error_segment;
3312 x_msg_data := fnd_flex_keyval.error_message;
3313 IF (l_debug = 1) THEN
3314 mdebug('Error Mess- If - '||x_msg_data);
3315 END IF;
3316
3317 if L_keystat_val then
3318 L_new_ccid := FND_FLEX_KEYVAL.combination_id;
3319
3320 IF (l_debug = 1) THEN
3321 mdebug('Validate Seg CCid: '||to_char(L_new_ccid));
3322 END IF;
3323 IF NOT FND_API.to_Boolean(p_simulate) THEN
3324 UPDATE mtl_item_locations
3325 SET subinventory_code = p_subinv
3326 WHERE inventory_location_id = l_new_ccid
3327 AND organization_id = P_Organization_Id ;
3328 IF SQL%NOTFOUND THEN
3329 IF (l_debug = 1) THEN
3330 mdebug('Table is not Updated');
3331 END IF;
3332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3333 END IF;
3334 END IF;
3335 IF (l_debug = 1) THEN
3336 mdebug('Create New Seg CCid: '||to_char(L_new_ccid));
3337 END IF;
3338 else
3339
3340 x_msg_data := fnd_flex_keyval.error_segment;
3341 IF (l_debug = 1) THEN
3342 mdebug('Errored out procedure');
3343 END IF;
3344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3345
3346 end if;
3347 P_Location_Id := L_new_ccid ;
3348 --
3349 -- END of API body
3350 -- Standard check of p_commit
3351 IF FND_API.to_Boolean(p_commit) THEN
3352 COMMIT;
3353 END IF;
3354 -- Standard call to get message count and if count is 1, get message info
3355 FND_MSG_PUB.Count_And_Get
3356 (p_count => x_msg_count
3357 , p_data => x_msg_data);
3358 EXCEPTION
3359 WHEN FND_API.G_EXC_ERROR THEN
3360 --
3361 ROLLBACK TO Inv_Dlocator_Create;
3362 --
3363 --MDEBUG( 'Exception Error ');
3364 x_return_status := FND_API.G_RET_STS_ERROR;
3365 --
3366 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3367 , p_data => x_msg_data);
3368 --
3369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3370 --
3371 ROLLBACK TO Inv_Dlocator_Create;
3372 --
3373 --MDEBUG( 'UNexp Exception Error ');
3374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3375 --
3376 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3377 , p_data => x_msg_data);
3378 --
3379 WHEN OTHERS THEN
3380 --
3381 --MDEBUG( 'Others Exception Error ');
3382 ROLLBACK TO Inv_Dlocator_Create;
3383 --
3384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3385 --
3386 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3387 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3388 END IF;
3389 --
3390 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3391 , p_data => x_msg_data);
3392 END;
3393 END;
3394
3395 -- BEGIN INVCONV
3396 PROCEDURE validate_secondarycountuom (
3397 p_api_version IN NUMBER
3398 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
3399 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
3400 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
3401 , x_return_status OUT NOCOPY VARCHAR2
3402 , x_msg_count OUT NOCOPY NUMBER
3403 , x_msg_data OUT NOCOPY VARCHAR2
3404 , x_errorcode OUT NOCOPY NUMBER
3405 , p_organization_id IN NUMBER
3406 , p_inventory_item_id IN NUMBER
3407 , p_secondary_uom IN VARCHAR2
3408 , p_secondary_unit_of_measure IN VARCHAR2
3409 , p_tracking_quantity_ind IN VARCHAR2) IS
3410 --
3411 CURSOR l_itemuom_csr (
3412 code IN VARCHAR2
3413 , NAME IN VARCHAR2
3414 , org IN NUMBER
3415 , itemid IN NUMBER) IS
3416 SELECT uom_code
3417 FROM mtl_item_uoms_view
3418 WHERE organization_id = org
3419 AND inventory_item_id = itemid
3420 AND (uom_code = code OR unit_of_measure = NAME);
3421
3422 --
3423 l_api_version CONSTANT NUMBER := 0.9;
3424 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_SecondaryCountUOM';
3425 l_secondary_unit_of_measure mtl_item_uoms_view.unit_of_measure%TYPE;
3426 l_secondary_uom mtl_item_uoms_view.uom_code%TYPE;
3427 BEGIN
3428 -- Standard start of API savepoint
3429 SAVEPOINT validate_secondarycountuom;
3430
3431 --
3432 -- Standard Call to check for call compatibility
3433 IF NOT fnd_api.compatible_api_call (l_api_version
3434 , p_api_version
3435 , l_api_name
3436 , g_pkg_name) THEN
3437 RAISE fnd_api.g_exc_unexpected_error;
3438 END IF;
3439
3440 --
3441 -- Initialize message list if p_init_msg_list is set to true
3442 IF fnd_api.to_boolean (p_init_msg_list) THEN
3443 fnd_msg_pub.initialize;
3444 END IF;
3445
3446 --
3447 -- Initialisize API return status to success
3448 x_return_status := fnd_api.g_ret_sts_success;
3449 x_errorcode := 0;
3450
3451 --
3452 IF p_secondary_uom IS NULL AND p_secondary_unit_of_measure IS NULL THEN
3453 MTL_CCEOI_VAR_PVT.g_secondary_count_uom := MTL_CCEOI_VAR_PVT.g_secondary_uom_code;
3454 ELSIF p_secondary_uom IS NOT NULL AND p_secondary_unit_of_measure IS NULL THEN
3455 l_secondary_uom := p_secondary_uom;
3456 l_secondary_unit_of_measure := NULL;
3457 ELSIF p_secondary_uom IS NULL AND p_secondary_unit_of_measure IS NOT NULL THEN
3458 l_secondary_uom := NULL;
3459 l_secondary_unit_of_measure := p_secondary_unit_of_measure;
3460
3461 OPEN l_itemuom_csr (l_secondary_uom
3462 , l_secondary_unit_of_measure
3463 , p_organization_id
3464 , p_inventory_item_id);
3465
3466 FETCH l_itemuom_csr INTO l_secondary_uom;
3467 CLOSE l_itemuom_csr;
3468 END IF;
3469
3470 IF l_secondary_uom <> MTL_CCEOI_VAR_PVT.g_secondary_uom_code THEN
3471 x_errorcode := 20;
3472 fnd_message.set_name ('INV', 'INV_INCORRECT_SECONDARY_UOM');
3473 fnd_msg_pub.ADD;
3474 RAISE fnd_api.g_exc_error;
3475 END IF;
3476
3477 --
3478 MTL_CCEOI_VAR_PVT.g_secondary_count_uom := l_secondary_uom;
3479
3480 IF fnd_api.to_boolean (p_commit) THEN
3481 COMMIT;
3482 END IF;
3483
3484 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3485 EXCEPTION
3486 WHEN fnd_api.g_exc_error THEN
3487 ROLLBACK TO validate_secondarycountuom;
3488 x_return_status := fnd_api.g_ret_sts_error;
3489 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3490 WHEN fnd_api.g_exc_unexpected_error THEN
3491 ROLLBACK TO validate_secondarycountuom;
3492 x_return_status := fnd_api.g_ret_sts_unexp_error;
3493 x_errorcode := -1;
3494 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3495 WHEN OTHERS THEN
3496 ROLLBACK TO validate_secondarycountuom;
3497 x_errorcode := -1;
3498 x_return_status := fnd_api.g_ret_sts_unexp_error;
3499
3500 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3501 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3502 END IF;
3503
3504 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3505 END validate_secondarycountuom;
3506
3507 PROCEDURE validate_secondarycountqty (
3508 p_api_version IN NUMBER
3509 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
3510 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
3511 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
3512 , p_precision IN NUMBER DEFAULT 5
3513 , x_return_status OUT NOCOPY VARCHAR2
3514 , x_msg_count OUT NOCOPY NUMBER
3515 , x_msg_data OUT NOCOPY VARCHAR2
3516 , x_errorcode OUT NOCOPY NUMBER
3517 , p_organization_id IN NUMBER
3518 , p_inventory_item_id IN NUMBER
3519 , p_lot_number IN VARCHAR2
3520 , p_count_uom IN VARCHAR2
3521 , p_count_quantity IN NUMBER
3522 , p_secondary_uom IN VARCHAR2
3523 , p_secondary_quantity IN VARCHAR2
3524 , p_tracking_quantity_ind IN VARCHAR2
3525 , p_secondary_default_ind IN VARCHAR2)
3526 IS
3527 --
3528 l_api_version CONSTANT NUMBER := 0.9;
3529 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_SecondaryCountQty';
3530 l_converted_qty NUMBER;
3531 l_error_message VARCHAR2(2000);
3532
3533 BEGIN
3534 -- Standard start of API savepoint
3535 SAVEPOINT validate_secondarycountqty;
3536
3537 -- Standard Call to check for call compatibility
3538 IF NOT fnd_api.compatible_api_call (l_api_version
3539 , p_api_version
3540 , l_api_name
3541 , g_pkg_name) THEN
3542 RAISE fnd_api.g_exc_unexpected_error;
3543 END IF;
3544
3545 -- Initialize message list if p_init_msg_list is set to true
3546 IF fnd_api.to_boolean (p_init_msg_list) THEN
3547 fnd_msg_pub.initialize;
3548 END IF;
3549
3550 -- Initialisize API return status to success
3551 x_return_status := fnd_api.g_ret_sts_success;
3552 x_errorcode := 0;
3553 --
3554 IF p_secondary_quantity IS NULL OR p_secondary_quantity = 0 THEN
3555 l_converted_qty := INV_CONVERT.inv_um_convert(
3556 organization_id => p_organization_id
3557 , item_id => p_inventory_item_id
3558 , lot_number => p_lot_number
3559 , precision => p_precision
3560 , from_quantity => p_count_quantity
3561 , from_unit => p_count_uom
3562 , to_unit => p_secondary_uom
3563 , from_name => NULL
3564 , to_name => NULL
3565 );
3566
3567 IF (l_converted_qty = -99999) THEN
3568 x_errorcode := 50;
3569 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
3570 fnd_msg_pub.ADD;
3571 RAISE fnd_api.g_exc_error;
3572 END IF;
3573
3574 MTL_CCEOI_VAR_PVT.g_secondary_count_quantity := l_converted_qty;
3575
3576 ELSE -- p_secondary_quantity IS NOT NULL
3577 IF p_secondary_quantity < 0 THEN
3578 x_errorcode := 52;
3579 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NEG_QTY');
3580 FND_MSG_PUB.Add;
3581 RAISE FND_API.G_EXC_ERROR;
3582 END IF;
3583
3584 IF ( INV_CONVERT.within_deviation(
3585 p_organization_id => p_organization_id
3586 , p_inventory_item_id => p_inventory_item_id
3587 , p_lot_number => p_lot_number
3588 , p_precision => p_precision
3589 , p_quantity => p_count_quantity
3590 , p_uom_code1 => p_count_uom
3591 , p_quantity2 => p_secondary_quantity
3592 , p_uom_code2 => p_secondary_uom
3593 , p_unit_of_measure1 => NULL
3594 , p_unit_of_measure2 => NULL) = 0) THEN
3595
3596 x_errorcode := 51;
3597 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_CHECK_ERR');
3598 -- An error occurred in call to INV_CONVERT.within_deviation
3599 fnd_msg_pub.ADD;
3600 RAISE fnd_api.g_exc_error;
3601 END IF;
3602
3603 MTL_CCEOI_VAR_PVT.g_secondary_count_quantity := p_secondary_quantity;
3604 END IF;
3605
3606 IF fnd_api.to_boolean (p_commit) THEN
3607 COMMIT;
3608 END IF;
3609
3610 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3611 EXCEPTION
3612 WHEN fnd_api.g_exc_error THEN
3613 ROLLBACK TO validate_secondarycountqty;
3614 x_return_status := fnd_api.g_ret_sts_error;
3615 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3616 WHEN fnd_api.g_exc_unexpected_error THEN
3617 ROLLBACK TO validate_secondarycountqty;
3618 x_return_status := fnd_api.g_ret_sts_unexp_error;
3619 x_errorcode := -1;
3620 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3621 WHEN OTHERS THEN
3622 ROLLBACK TO validate_secondarycountqty;
3623 x_errorcode := -1;
3624 x_return_status := fnd_api.g_ret_sts_unexp_error;
3625
3626 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3627 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3628 END IF;
3629
3630 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3631 END validate_secondarycountqty;
3632
3633 -- END INVCONV
3634
3635 END MTL_INV_VALIDATE_GRP;
[Home] [Help]