[Home] [Help]
PACKAGE BODY: APPS.MTL_CYCLE_COUNT_ENTRIES_PKG
Source
1 PACKAGE BODY MTL_CYCLE_COUNT_ENTRIES_PKG as
2 /* $Header: INVATCEB.pls 120.1.12000000.2 2007/07/18 10:29:20 abaid ship $ */
3 --Added NOCOPY hint to X_Rowid IN OUT parameter to comply with
4 --GSCC File.Sql.39 standard Bug:4410902
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Cycle_Count_Entry_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Count_List_Sequence NUMBER,
13 X_Count_Date_First DATE,
14 X_Count_Date_Current DATE,
15 X_Count_Date_Prior DATE,
16 X_Count_Date_Dummy DATE,
17 X_Counted_By_Employee_Id_First NUMBER,
18 X_Counted_By_Employee_Id_Curr NUMBER,
19 X_Counted_By_Employee_Id_Prior NUMBER,
20 X_Counted_By_Employee_Id_Dummy NUMBER,
21 X_Count_Uom_First VARCHAR2,
22 X_Count_Uom_Current VARCHAR2,
23 X_Count_Uom_Prior VARCHAR2,
24 X_Count_Quantity_First NUMBER,
25 X_Count_Quantity_Current NUMBER,
26 X_Count_Quantity_Prior NUMBER,
27 X_Inventory_Item_Id NUMBER,
28 X_Subinventory VARCHAR2,
29 X_Entry_Status_Code NUMBER,
30 X_Count_Due_Date DATE,
31 X_Organization_Id NUMBER,
32 X_Cycle_Count_Header_Id NUMBER,
33 X_Number_Of_Counts NUMBER,
34 X_Locator_Id NUMBER,
35 X_Adjustment_Quantity NUMBER,
36 X_Adjustment_Date DATE,
37 X_Adjustment_Amount NUMBER,
38 X_Item_Unit_Cost NUMBER,
39 X_Inventory_Adjustment_Account NUMBER,
40 X_Approval_Date DATE,
41 X_Approver_Employee_Id NUMBER,
42 X_Revision VARCHAR2,
43 X_Lot_Number VARCHAR2,
44 X_Lot_Control VARCHAR2,
45 X_System_Quantity_First NUMBER,
46 X_System_Quantity_Current NUMBER,
47 X_System_Quantity_Prior NUMBER,
48 X_Reference_First VARCHAR2,
49 X_Reference_Current VARCHAR2,
50 X_Reference_Prior VARCHAR2,
51 X_Primary_Uom_Quantity_First NUMBER,
52 X_Primary_Uom_Quantity_Current NUMBER,
53 X_Primary_Uom_Quantity_Prior NUMBER,
54 X_Count_Type_Code NUMBER,
55 X_Transaction_Reason_Id NUMBER,
56 X_Approval_Type NUMBER,
57 X_Attribute_Category VARCHAR2,
58 X_Attribute1 VARCHAR2,
59 X_Attribute2 VARCHAR2,
60 X_Attribute3 VARCHAR2,
61 X_Attribute4 VARCHAR2,
62 X_Attribute5 VARCHAR2,
63 X_Attribute6 VARCHAR2,
64 X_Attribute7 VARCHAR2,
65 X_Attribute8 VARCHAR2,
66 X_Attribute9 VARCHAR2,
67 X_Attribute10 VARCHAR2,
68 X_Attribute11 VARCHAR2,
69 X_Attribute12 VARCHAR2,
70 X_Attribute13 VARCHAR2,
71 X_Attribute14 VARCHAR2,
72 X_Attribute15 VARCHAR2,
73 X_Serial_Number VARCHAR2,
74 X_Serial_Detail NUMBER,
75 X_Approval_Condition NUMBER,
76 X_Neg_Adjustment_Quantity NUMBER,
77 X_Neg_Adjustment_Amount NUMBER,
78 X_Outermost_Lpn_ID NUMBER DEFAULT NULL,
79 X_Parent_Lpn_ID NUMBER DEFAULT NULL,
80 X_Cost_Group_ID NUMBER DEFAULT NULL,
81 -- BEGIN INVCONV
82 X_Count_Secondary_Uom_First VARCHAR2 DEFAULT NULL,
83 X_Count_Secondary_Uom_Current VARCHAR2 DEFAULT NULL,
84 X_Count_Secondary_Uom_Prior VARCHAR2 DEFAULT NULL,
85 X_Secondary_Uom_Quantity_First NUMBER DEFAULT NULL,
86 X_Secondary_Uom_Quantity_Curr NUMBER DEFAULT NULL,
87 X_Secondary_Uom_Quantity_Prior NUMBER DEFAULT NULL,
88 X_Secondary_System_Qty_First NUMBER DEFAULT NULL,
89 X_Secondary_System_Qty_Current NUMBER DEFAULT NULL,
90 X_Secondary_System_Qty_Prior NUMBER DEFAULT NULL,
91 X_Secondary_Adjustment_Qty NUMBER DEFAULT NULL
92 -- END INVCONV
93 ) IS
94 CURSOR C IS SELECT rowid FROM mtl_cycle_count_entries
95 WHERE ( (cycle_count_header_id = X_Cycle_Count_Header_Id)
96 or (cycle_count_header_id is NULL and X_Cycle_Count_Header_Id is NULL));
97
98 BEGIN
99 INSERT INTO mtl_cycle_count_entries(
100 cycle_count_entry_id,
101 last_update_date,
102 last_updated_by,
103 creation_date,
104 created_by,
105 last_update_login,
106 count_list_sequence,
107 count_date_first,
108 count_date_current,
109 count_date_prior,
110 count_date_dummy,
111 counted_by_employee_id_first,
112 counted_by_employee_id_current,
113 counted_by_employee_id_prior,
114 counted_by_employee_id_dummy,
115 count_uom_first,
116 count_uom_current,
117 count_uom_prior,
118 count_quantity_first,
119 count_quantity_current,
120 count_quantity_prior,
121 inventory_item_id,
122 subinventory,
123 entry_status_code,
124 count_due_date,
125 organization_id,
126 cycle_count_header_id,
127 number_of_counts,
128 locator_id,
129 adjustment_quantity,
130 adjustment_date,
131 adjustment_amount,
132 item_unit_cost,
133 inventory_adjustment_account,
134 approval_date,
135 approver_employee_id,
136 revision,
137 lot_number,
138 lot_control,
139 system_quantity_first,
140 system_quantity_current,
141 system_quantity_prior,
142 reference_first,
143 reference_current,
144 reference_prior,
145 primary_uom_quantity_first,
146 primary_uom_quantity_current,
147 primary_uom_quantity_prior,
148 count_type_code,
149 transaction_reason_id,
150 approval_type,
151 attribute_category,
152 attribute1,
153 attribute2,
154 attribute3,
155 attribute4,
156 attribute5,
157 attribute6,
158 attribute7,
159 attribute8,
160 attribute9,
161 attribute10,
162 attribute11,
163 attribute12,
164 attribute13,
165 attribute14,
166 attribute15,
167 serial_number,
168 serial_detail,
169 approval_condition,
170 neg_adjustment_quantity,
171 neg_adjustment_amount,
172 outermost_lpn_id,
173 parent_lpn_id,
174 cost_group_id,
175 -- BEGIN INVCONV
176 Count_Secondary_Uom_First,
177 Count_Secondary_Uom_Current,
178 Count_Secondary_Uom_Prior,
179 Secondary_Uom_Quantity_First,
180 Secondary_Uom_Quantity_Current,
181 Secondary_Uom_Quantity_Prior,
182 Secondary_System_Qty_First,
183 Secondary_System_Qty_Current,
184 Secondary_System_Qty_Prior,
185 Secondary_Adjustment_Quantity
186 -- END INVCONV
187 )
188 VALUES (
189 X_Cycle_Count_Entry_Id,
190 X_Last_Update_Date,
191 X_Last_Updated_By,
192 X_Creation_Date,
193 X_Created_By,
194 X_Last_Update_Login,
195 X_Count_List_Sequence,
196 X_Count_Date_First,
197 X_Count_Date_Current,
198 X_Count_Date_Prior,
199 X_Count_Date_Dummy,
200 X_Counted_By_Employee_Id_First,
201 X_Counted_By_Employee_Id_Curr,
202 X_Counted_By_Employee_Id_Prior,
203 X_Counted_By_Employee_Id_Dummy,
204 X_Count_Uom_First,
205 X_Count_Uom_Current,
206 X_Count_Uom_Prior,
207 X_Count_Quantity_First,
208 X_Count_Quantity_Current,
209 X_Count_Quantity_Prior,
210 X_Inventory_Item_Id,
211 X_Subinventory,
212 X_Entry_Status_Code,
213 X_Count_Due_Date,
214 X_Organization_Id,
215 X_Cycle_Count_Header_Id,
216 X_Number_Of_Counts,
217 X_Locator_Id,
218 X_Adjustment_Quantity,
219 X_Adjustment_Date,
220 X_Adjustment_Amount,
221 X_Item_Unit_Cost,
222 X_Inventory_Adjustment_Account,
223 X_Approval_Date,
224 X_Approver_Employee_Id,
225 X_Revision,
226 X_Lot_Number,
227 X_Lot_Control,
228 X_System_Quantity_First,
229 X_System_Quantity_Current,
230 X_System_Quantity_Prior,
231 X_Reference_First,
232 X_Reference_Current,
233 X_Reference_Prior,
234 X_Primary_Uom_Quantity_First,
235 X_Primary_Uom_Quantity_Current,
236 X_Primary_Uom_Quantity_Prior,
237 X_Count_Type_Code,
238 X_Transaction_Reason_Id,
239 X_Approval_Type,
240 X_Attribute_Category,
241 X_Attribute1,
242 X_Attribute2,
243 X_Attribute3,
244 X_Attribute4,
245 X_Attribute5,
246 X_Attribute6,
247 X_Attribute7,
248 X_Attribute8,
249 X_Attribute9,
250 X_Attribute10,
251 X_Attribute11,
252 X_Attribute12,
253 X_Attribute13,
254 X_Attribute14,
255 X_Attribute15,
256 X_Serial_Number,
257 X_Serial_Detail,
258 X_Approval_Condition,
259 X_Neg_Adjustment_Quantity,
260 X_Neg_Adjustment_Amount,
261 X_Outermost_Lpn_ID,
262 X_Parent_Lpn_ID,
263 X_Cost_Group_ID,
264 -- BEGIN INVCONV
265 X_Count_Secondary_Uom_First,
266 X_Count_Secondary_Uom_Current,
267 X_Count_Secondary_Uom_Prior,
268 X_Secondary_Uom_Quantity_First,
269 X_Secondary_Uom_Quantity_Curr,
270 X_Secondary_Uom_Quantity_Prior,
271 X_Secondary_System_Qty_First,
272 X_Secondary_System_Qty_Current,
273 X_Secondary_System_Qty_Prior,
274 X_Secondary_Adjustment_Qty
275 -- END INVCONV
276 );
277
278 OPEN C;
279 FETCH C INTO X_Rowid;
280 if (C%NOTFOUND) then
281 CLOSE C;
282 Raise NO_DATA_FOUND;
283 end if;
284 CLOSE C;
285 END Insert_Row;
286
287
288 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
289 X_Cycle_Count_Entry_Id NUMBER,
290 X_Count_List_Sequence NUMBER,
291 X_Count_Date_First DATE,
292 X_Count_Date_Current DATE,
293 X_Count_Date_Prior DATE,
294 X_Count_Date_Dummy DATE,
295 X_Counted_By_Employee_Id_First NUMBER,
296 X_Counted_By_Employee_Id_Curr NUMBER,
297 X_Counted_By_Employee_Id_Prior NUMBER,
298 X_Counted_By_Employee_Id_Dummy NUMBER,
299 X_Count_Uom_First VARCHAR2,
300 X_Count_Uom_Current VARCHAR2,
301 X_Count_Uom_Prior VARCHAR2,
302 X_Count_Quantity_First NUMBER,
303 X_Count_Quantity_Current NUMBER,
304 X_Count_Quantity_Prior NUMBER,
305 X_Inventory_Item_Id NUMBER,
306 X_Subinventory VARCHAR2,
307 X_Entry_Status_Code NUMBER,
308 X_Count_Due_Date DATE,
309 X_Organization_Id NUMBER,
310 X_Cycle_Count_Header_Id NUMBER,
311 X_Number_Of_Counts NUMBER,
312 X_Locator_Id NUMBER,
313 X_Adjustment_Quantity NUMBER,
314 X_Adjustment_Date DATE,
315 X_Adjustment_Amount NUMBER,
316 X_Item_Unit_Cost NUMBER,
317 X_Inventory_Adjustment_Account NUMBER,
318 X_Approval_Date DATE,
319 X_Approver_Employee_Id NUMBER,
320 X_Revision VARCHAR2,
321 X_Lot_Number VARCHAR2,
322 X_Lot_Control VARCHAR2,
323 X_System_Quantity_First NUMBER,
324 X_System_Quantity_Current NUMBER,
325 X_System_Quantity_Prior NUMBER,
326 X_Reference_First VARCHAR2,
327 X_Reference_Current VARCHAR2,
328 X_Reference_Prior VARCHAR2,
329 X_Primary_Uom_Quantity_First NUMBER,
330 X_Primary_Uom_Quantity_Current NUMBER,
331 X_Primary_Uom_Quantity_Prior NUMBER,
332 X_Count_Type_Code NUMBER,
333 X_Transaction_Reason_Id NUMBER,
334 X_Approval_Type NUMBER,
335 X_Attribute_Category VARCHAR2,
336 X_Attribute1 VARCHAR2,
337 X_Attribute2 VARCHAR2,
338 X_Attribute3 VARCHAR2,
339 X_Attribute4 VARCHAR2,
340 X_Attribute5 VARCHAR2,
341 X_Attribute6 VARCHAR2,
342 X_Attribute7 VARCHAR2,
343 X_Attribute8 VARCHAR2,
344 X_Attribute9 VARCHAR2,
345 X_Attribute10 VARCHAR2,
346 X_Attribute11 VARCHAR2,
347 X_Attribute12 VARCHAR2,
348 X_Attribute13 VARCHAR2,
349 X_Attribute14 VARCHAR2,
350 X_Attribute15 VARCHAR2,
351 X_Serial_Number VARCHAR2,
352 X_Serial_Detail NUMBER,
353 X_Approval_Condition NUMBER,
354 X_Neg_Adjustment_Quantity NUMBER,
355 X_Neg_Adjustment_Amount NUMBER,
356 X_Outermost_Lpn_ID NUMBER DEFAULT NULL,
357 X_Parent_Lpn_ID NUMBER DEFAULT NULL,
358 X_Cost_Group_ID NUMBER DEFAULT NULL,
359 -- BEGIN INVCONV
360 X_Count_Secondary_Uom_First VARCHAR2 DEFAULT NULL,
361 X_Count_Secondary_Uom_Current VARCHAR2 DEFAULT NULL,
362 X_Count_Secondary_Uom_Prior VARCHAR2 DEFAULT NULL,
363 X_Secondary_Uom_Quantity_First NUMBER DEFAULT NULL,
364 X_Secondary_Uom_Quantity_Curr NUMBER DEFAULT NULL,
365 X_Secondary_Uom_Quantity_Prior NUMBER DEFAULT NULL,
366 X_Secondary_System_Qty_First NUMBER DEFAULT NULL,
367 X_Secondary_System_Qty_Current NUMBER DEFAULT NULL,
368 X_Secondary_System_Qty_Prior NUMBER DEFAULT NULL,
369 X_Secondary_Adjustment_Qty NUMBER DEFAULT NULL
370 -- END INVCONV
371 ) IS
372 CURSOR C IS
373 SELECT *
374 FROM mtl_cycle_count_entries
375 WHERE rowid = X_Rowid
376 FOR UPDATE of Cycle_Count_Header_Id NOWAIT;
377 Recinfo C%ROWTYPE;
378 RECORD_CHANGED EXCEPTION;
379
380
381 BEGIN
382 OPEN C;
383 FETCH C INTO Recinfo;
384 if (C%NOTFOUND) then
385 CLOSE C;
386 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
387 APP_EXCEPTION.Raise_Exception;
388 end if;
389 CLOSE C;
390 if not ( (Recinfo.cycle_count_entry_id = X_Cycle_Count_Entry_Id)
391 AND ( (Recinfo.count_list_sequence = X_Count_List_Sequence)
392 OR ( (Recinfo.count_list_sequence IS NULL)
393 AND (X_Count_List_Sequence IS NULL)))
394 AND ( (Recinfo.count_date_first = X_Count_Date_First)
395 OR ( (Recinfo.count_date_first IS NULL)
396 AND (X_Count_Date_First IS NULL)))
397 AND ( (Recinfo.count_date_current = X_Count_Date_Current)
398 OR ( (Recinfo.count_date_current IS NULL)
399 AND (X_Count_Date_Current IS NULL)))
400 AND ( (Recinfo.count_date_prior = X_Count_Date_Prior)
401 OR ( (Recinfo.count_date_prior IS NULL)
402 AND (X_Count_Date_Prior IS NULL)))
403 AND ( (Recinfo.count_date_dummy = X_Count_Date_Dummy)
404 OR ( (Recinfo.count_date_dummy IS NULL)
405 AND (X_Count_Date_Dummy IS NULL)))
406 AND ( (Recinfo.counted_by_employee_id_first = X_Counted_By_Employee_Id_First)
407 OR ( (Recinfo.counted_by_employee_id_first IS NULL)
408 AND (X_Counted_By_Employee_Id_First IS NULL)))
409 AND ( (Recinfo.counted_by_employee_id_current = X_Counted_By_Employee_Id_Curr)
410 OR ( (Recinfo.counted_by_employee_id_current IS NULL)
411 AND (X_Counted_By_Employee_Id_Curr IS NULL)))
412 AND ( (Recinfo.counted_by_employee_id_prior = X_Counted_By_Employee_Id_Prior)
413 OR ( (Recinfo.counted_by_employee_id_prior IS NULL)
414 AND (X_Counted_By_Employee_Id_Prior IS NULL)))
415 AND ( (Recinfo.counted_by_employee_id_dummy = X_Counted_By_Employee_Id_Dummy)
416 OR ( (Recinfo.counted_by_employee_id_dummy IS NULL)
417 AND (X_Counted_By_Employee_Id_Dummy IS NULL)))
418 AND ( (Recinfo.count_uom_first = X_Count_Uom_First)
419 OR ( (Recinfo.count_uom_first IS NULL)
420 AND (X_Count_Uom_First IS NULL)))
421 AND ( (Recinfo.count_uom_current = X_Count_Uom_Current)
422 OR ( (Recinfo.count_uom_current IS NULL)
423 AND (X_Count_Uom_Current IS NULL)))
424 AND ( (Recinfo.count_uom_prior = X_Count_Uom_Prior)
425 OR ( (Recinfo.count_uom_prior IS NULL)
426 AND (X_Count_Uom_Prior IS NULL)))
427 AND ( (Recinfo.count_quantity_first = X_Count_Quantity_First)
428 OR ( (Recinfo.count_quantity_first IS NULL)
429 AND (X_Count_Quantity_First IS NULL)))
430 AND ( (Recinfo.count_quantity_current = X_Count_Quantity_Current)
431 OR ( (Recinfo.count_quantity_current IS NULL)
432 AND (X_Count_Quantity_Current IS NULL)))
433 AND ( (Recinfo.count_quantity_prior = X_Count_Quantity_Prior)
434 OR ( (Recinfo.count_quantity_prior IS NULL)
435 AND (X_Count_Quantity_Prior IS NULL)))
436 AND (Recinfo.inventory_item_id = X_Inventory_Item_Id)
437 AND (Recinfo.subinventory = X_Subinventory)
438 AND ( (Recinfo.entry_status_code = X_Entry_Status_Code)
439 OR ( (Recinfo.entry_status_code IS NULL)
440 AND (X_Entry_Status_Code IS NULL)))
441 AND ( (Recinfo.count_due_date = X_Count_Due_Date)
442 OR ( (Recinfo.count_due_date IS NULL)
443 AND (X_Count_Due_Date IS NULL)))
444 AND (Recinfo.organization_id = X_Organization_Id)
445 AND ( (Recinfo.cycle_count_header_id = X_Cycle_Count_Header_Id)
446 OR ( (Recinfo.cycle_count_header_id IS NULL)
447 AND (X_Cycle_Count_Header_Id IS NULL)))
448 AND ( (Recinfo.number_of_counts = X_Number_Of_Counts)
449 OR ( (Recinfo.number_of_counts IS NULL)
450 AND (X_Number_Of_Counts IS NULL)))
451 AND ( (Recinfo.locator_id = X_Locator_Id)
452 OR ( (Recinfo.locator_id IS NULL)
453 AND (X_Locator_Id IS NULL)))
454 AND ( (Recinfo.adjustment_quantity = X_Adjustment_Quantity)
455 OR ( (Recinfo.adjustment_quantity IS NULL)
456 AND (X_Adjustment_Quantity IS NULL)))
457 AND ( (Recinfo.adjustment_date = X_Adjustment_Date)
458 OR ( (Recinfo.adjustment_date IS NULL)
459 AND (X_Adjustment_Date IS NULL)))
460 AND ( (Recinfo.adjustment_amount = X_Adjustment_Amount)
461 OR ( (Recinfo.adjustment_amount IS NULL)
462 AND (X_Adjustment_Amount IS NULL)))
463 AND ( (Recinfo.item_unit_cost = X_Item_Unit_Cost)
464 OR ( (Recinfo.item_unit_cost IS NULL)
465 AND (X_Item_Unit_Cost IS NULL)))
466 AND ( (Recinfo.inventory_adjustment_account = X_Inventory_Adjustment_Account)
467 OR ( (Recinfo.inventory_adjustment_account IS NULL)
468 AND (X_Inventory_Adjustment_Account IS NULL)))
469 AND ( (Recinfo.approval_date = X_Approval_Date)
470 OR ( (Recinfo.approval_date IS NULL)
471 AND (X_Approval_Date IS NULL)))
472 AND ( (Recinfo.approver_employee_id = X_Approver_Employee_Id)
473 OR ( (Recinfo.approver_employee_id IS NULL)
474 AND (X_Approver_Employee_Id IS NULL)))
475 AND ( (Recinfo.revision = X_Revision)
476 OR ( (Recinfo.revision IS NULL)
477 AND (X_Revision IS NULL)))
478 AND ( (Recinfo.lot_number = X_Lot_Number)
479 OR ( (Recinfo.lot_number IS NULL)
480 AND (X_Lot_Number IS NULL)))
481 AND ( (Recinfo.lot_control = X_Lot_Control)
482 OR ( (Recinfo.lot_control IS NULL)
483 AND (X_Lot_Control IS NULL)))
484 AND ( (Recinfo.system_quantity_first = X_System_Quantity_First)
485 OR ( (Recinfo.system_quantity_first IS NULL)
486 AND (X_System_Quantity_First IS NULL)))
487 AND ( (Recinfo.system_quantity_current = X_System_Quantity_Current
488 )
489 OR ( (Recinfo.system_quantity_current IS NULL)
490 AND (X_System_Quantity_Current IS NULL)))
491 AND ( (Recinfo.system_quantity_prior = X_System_Quantity_Prior)
492 OR ( (Recinfo.system_quantity_prior IS NULL)
493 AND (X_System_Quantity_Prior IS NULL)))
494 AND ( (Recinfo.reference_first = X_Reference_First)
495 OR ( (Recinfo.reference_first IS NULL)
496 AND (X_Reference_First IS NULL)))
497 AND ( (Recinfo.reference_current = X_Reference_Current)
498 OR ( (Recinfo.reference_current IS NULL)
499 AND (X_Reference_Current IS NULL)))
500 AND ( (Recinfo.reference_prior = X_Reference_Prior)
501 OR ( (Recinfo.reference_prior IS NULL)
502 AND (X_Reference_Prior IS NULL)))
503 AND ( (Recinfo.primary_uom_quantity_first = X_Primary_Uom_Quantity_First)
504 OR ( (Recinfo.primary_uom_quantity_first IS NULL)
505 AND (X_Primary_Uom_Quantity_First IS NULL)))
506 AND ( (Recinfo.primary_uom_quantity_current = X_Primary_Uom_Quantity_Current)
507 OR ( (Recinfo.primary_uom_quantity_current IS NULL)
508 AND (X_Primary_Uom_Quantity_Current IS NULL)))) then
509 RAISE RECORD_CHANGED;
510 end if;
511
512 if not (( (Recinfo.primary_uom_quantity_prior = X_Primary_Uom_Quantity_Prior)
513 OR ( (Recinfo.primary_uom_quantity_prior IS NULL)
514 AND (X_Primary_Uom_Quantity_Prior IS NULL)))
515 AND ( (Recinfo.count_type_code = X_Count_Type_Code)
516 OR ( (Recinfo.count_type_code IS NULL)
517 AND (X_Count_Type_Code IS NULL)))
518 AND ( (Recinfo.transaction_reason_id = X_Transaction_Reason_Id)
519 OR ( (Recinfo.transaction_reason_id IS NULL)
520 AND (X_Transaction_Reason_Id IS NULL)))
521 AND ( (Recinfo.approval_type = X_Approval_Type)
522 OR ( (Recinfo.approval_type IS NULL)
523 AND (X_Approval_Type IS NULL)))
524 AND ( (Recinfo.attribute_category = X_Attribute_Category)
525 OR ( (Recinfo.attribute_category IS NULL)
526 AND (X_Attribute_Category IS NULL)))
527 AND ( (Recinfo.attribute1 = X_Attribute1)
528 OR ( (Recinfo.attribute1 IS NULL)
529 AND (X_Attribute1 IS NULL)))
530 AND ( (Recinfo.attribute2 = X_Attribute2)
531 OR ( (Recinfo.attribute2 IS NULL)
532 AND (X_Attribute2 IS NULL)))
533 AND ( (Recinfo.attribute3 = X_Attribute3)
534 OR ( (Recinfo.attribute3 IS NULL)
535 AND (X_Attribute3 IS NULL)))
536 AND ( (Recinfo.attribute4 = X_Attribute4)
537 OR ( (Recinfo.attribute4 IS NULL)
538 AND (X_Attribute4 IS NULL)))
539 AND ( (Recinfo.attribute5 = X_Attribute5)
540 OR ( (Recinfo.attribute5 IS NULL)
541 AND (X_Attribute5 IS NULL)))
542 AND ( (Recinfo.attribute6 = X_Attribute6)
543 OR ( (Recinfo.attribute6 IS NULL)
544 AND (X_Attribute6 IS NULL)))
545 AND ( (Recinfo.attribute7 = X_Attribute7)
546 OR ( (Recinfo.attribute7 IS NULL)
547 AND (X_Attribute7 IS NULL)))
548 AND ( (Recinfo.attribute8 = X_Attribute8)
549 OR ( (Recinfo.attribute8 IS NULL)
550 AND (X_Attribute8 IS NULL)))
551 AND ( (Recinfo.attribute9 = X_Attribute9)
552 OR ( (Recinfo.attribute9 IS NULL)
553 AND (X_Attribute9 IS NULL)))
554 AND ( (Recinfo.attribute10 = X_Attribute10)
555 OR ( (Recinfo.attribute10 IS NULL)
556 AND (X_Attribute10 IS NULL)))
557 AND ( (Recinfo.attribute11 = X_Attribute11)
558 OR ( (Recinfo.attribute11 IS NULL)
559 AND (X_Attribute11 IS NULL)))
560 AND ( (Recinfo.attribute12 = X_Attribute12)
561 OR ( (Recinfo.attribute12 IS NULL)
562 AND (X_Attribute12 IS NULL)))
563 AND ( (Recinfo.attribute13 = X_Attribute13)
564 OR ( (Recinfo.attribute13 IS NULL)
565 AND (X_Attribute13 IS NULL)))
566 AND ( (Recinfo.attribute14 = X_Attribute14)
567 OR ( (Recinfo.attribute14 IS NULL)
568 AND (X_Attribute14 IS NULL)))
569 AND ( (Recinfo.attribute15 = X_Attribute15)
570 OR ( (Recinfo.attribute15 IS NULL)
571 AND (X_Attribute15 IS NULL)))
572 AND ( (Recinfo.serial_number = X_Serial_Number)
573 OR ( (Recinfo.serial_number IS NULL)
574 AND (X_Serial_Number IS NULL)))
575 AND ( (Recinfo.serial_detail = X_Serial_Detail)
576 OR ( (Recinfo.serial_detail IS NULL)
577 AND (X_Serial_Detail IS NULL)))
578 AND ( (Recinfo.approval_condition = X_Approval_Condition)
579 OR ( (Recinfo.approval_condition IS NULL)
580 AND (X_Approval_Condition IS NULL)))
581 AND ( (Recinfo.neg_adjustment_quantity = X_Neg_Adjustment_Quantity)
582 OR ( (Recinfo.neg_adjustment_quantity IS NULL)
583 AND (X_Neg_Adjustment_Quantity IS NULL)))
584 AND ( (Recinfo.neg_adjustment_amount = X_Neg_Adjustment_Amount)
585 OR ( (Recinfo.neg_adjustment_amount IS NULL)
586 AND (X_Neg_Adjustment_Amount IS NULL)))
587 AND ( (Recinfo.parent_lpn_id = X_Parent_Lpn_ID)
588 OR ( (Recinfo.parent_lpn_id IS NULL)
589 AND (X_Parent_Lpn_ID IS NULL)))
590 AND ( (Recinfo.outermost_lpn_id = X_Outermost_Lpn_ID)
591 OR ( (Recinfo.Outermost_lpn_id IS NULL)
592 AND (X_Outermost_Lpn_ID IS NULL)))
593 AND ( (Recinfo.cost_group_id = X_Cost_Group_ID)
594 OR ( (Recinfo.cost_group_id IS NULL)
595 AND (X_Cost_Group_ID IS NULL)))
596 ) then
597 Raise RECORD_CHANGED;
598 end if;
599
600 -- BEGIN INVCONV
601 if not (( (Recinfo.count_secondary_uom_first = X_Count_Secondary_Uom_First)
602 OR ( (Recinfo.count_secondary_uom_first IS NULL)
603 AND (X_Count_Secondary_Uom_First IS NULL)))
604 AND ( (Recinfo.count_secondary_uom_current = X_Count_Secondary_Uom_Current)
605 OR ( (Recinfo.count_secondary_uom_current IS NULL)
606 AND (X_Count_Secondary_Uom_Current IS NULL)))
607 AND ( (Recinfo.count_secondary_uom_prior = X_Count_Secondary_Uom_Prior)
608 OR ( (Recinfo.count_secondary_uom_prior IS NULL)
609 AND (X_Count_Secondary_Uom_Prior IS NULL)))
610 AND ( (Recinfo.secondary_uom_quantity_first = X_Secondary_Uom_Quantity_First)
611 OR ( (Recinfo.secondary_uom_quantity_first IS NULL)
612 AND (X_Secondary_Uom_Quantity_First IS NULL)))
613 AND ( (Recinfo.secondary_uom_quantity_current = X_Secondary_Uom_Quantity_Curr)
614 OR ( (Recinfo.secondary_uom_quantity_current IS NULL)
615 AND (X_Secondary_Uom_Quantity_Curr IS NULL)))
616 AND ( (Recinfo.secondary_uom_quantity_prior = X_Secondary_Uom_Quantity_Prior)
617 OR ( (Recinfo.secondary_uom_quantity_prior IS NULL)
618 AND (X_Secondary_Uom_Quantity_Prior IS NULL)))
619 AND ( (Recinfo.secondary_system_qty_first = X_Secondary_System_Qty_First)
620 OR ( (Recinfo.secondary_system_qty_first IS NULL)
621 AND (X_Secondary_System_Qty_First IS NULL)))
622 AND ( (Recinfo.secondary_system_qty_current = X_Secondary_System_Qty_Current)
623 OR ( (Recinfo.secondary_system_qty_current IS NULL)
624 AND (X_Secondary_System_Qty_Current IS NULL)))
625 AND ( (Recinfo.secondary_system_qty_prior = X_Secondary_System_Qty_Prior)
626 OR ( (Recinfo.secondary_system_qty_prior IS NULL)
627 AND (X_Secondary_System_Qty_Prior IS NULL)))
628 AND ( (Recinfo.secondary_adjustment_quantity = X_Secondary_Adjustment_Qty)
629 OR ( (Recinfo.secondary_adjustment_quantity IS NULL)
630 AND (X_Secondary_Adjustment_Qty IS NULL)))
631 ) then
632 Raise RECORD_CHANGED;
633 end if;
634 -- END INVCONV
635
636 EXCEPTION
637 WHEN RECORD_CHANGED THEN
638 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
639 APP_EXCEPTION.Raise_Exception;
640 WHEN OTHERS THEN
641 raise;
642 END Lock_Row;
643
644 PROCEDURE Update_Row(X_Rowid VARCHAR2,
645 X_Cycle_Count_Entry_Id NUMBER,
646 X_Last_Update_Date DATE,
647 X_Last_Updated_By NUMBER,
648 X_Last_Update_Login NUMBER,
649 X_Count_List_Sequence NUMBER,
650 X_Count_Date_First DATE,
651 X_Count_Date_Current DATE,
652 X_Count_Date_Prior DATE,
653 X_Count_Date_Dummy DATE,
654 X_Counted_By_Employee_Id_First NUMBER,
655 X_Counted_By_Employee_Id_Curr NUMBER,
656 X_Counted_By_Employee_Id_Prior NUMBER,
657 X_Counted_By_Employee_Id_Dummy NUMBER,
658 X_Count_Uom_First VARCHAR2,
659 X_Count_Uom_Current VARCHAR2,
660 X_Count_Uom_Prior VARCHAR2,
661 X_Count_Quantity_First NUMBER,
662 X_Count_Quantity_Current NUMBER,
663 X_Count_Quantity_Prior NUMBER,
664 X_Inventory_Item_Id NUMBER,
665 X_Subinventory VARCHAR2,
666 X_Entry_Status_Code NUMBER,
667 X_Count_Due_Date DATE,
668 X_Organization_Id NUMBER,
669 X_Cycle_Count_Header_Id NUMBER,
670 X_Number_Of_Counts NUMBER,
671 X_Locator_Id NUMBER,
672 X_Adjustment_Quantity NUMBER,
673 X_Adjustment_Date DATE,
674 X_Adjustment_Amount NUMBER,
675 X_Item_Unit_Cost NUMBER,
676 X_Inventory_Adjustment_Account NUMBER,
677 X_Approval_Date DATE,
678 X_Approver_Employee_Id NUMBER,
679 X_Revision VARCHAR2,
680 X_Lot_Number VARCHAR2,
681 X_Lot_Control VARCHAR2,
682 X_System_Quantity_First NUMBER,
683 X_System_Quantity_Current NUMBER,
684 X_System_Quantity_Prior NUMBER,
685 X_Reference_First VARCHAR2,
686 X_Reference_Current VARCHAR2,
687 X_Reference_Prior VARCHAR2,
688 X_Primary_Uom_Quantity_First NUMBER,
689 X_Primary_Uom_Quantity_Current NUMBER,
690 X_Primary_Uom_Quantity_Prior NUMBER,
691 X_Count_Type_Code NUMBER,
692 X_Transaction_Reason_Id NUMBER,
693 X_Approval_Type NUMBER,
694 X_Attribute_Category VARCHAR2,
695 X_Attribute1 VARCHAR2,
696 X_Attribute2 VARCHAR2,
697 X_Attribute3 VARCHAR2,
698 X_Attribute4 VARCHAR2,
699 X_Attribute5 VARCHAR2,
700 X_Attribute6 VARCHAR2,
701 X_Attribute7 VARCHAR2,
702 X_Attribute8 VARCHAR2,
703 X_Attribute9 VARCHAR2,
704 X_Attribute10 VARCHAR2,
705 X_Attribute11 VARCHAR2,
706 X_Attribute12 VARCHAR2,
707 X_Attribute13 VARCHAR2,
708 X_Attribute14 VARCHAR2,
709 X_Attribute15 VARCHAR2,
710 X_Serial_Number VARCHAR2,
711 X_Serial_Detail NUMBER,
712 X_Approval_Condition NUMBER,
713 X_Neg_Adjustment_Quantity NUMBER,
714 X_Neg_Adjustment_Amount NUMBER,
715 X_Outermost_Lpn_ID NUMBER DEFAULT NULL,
716 X_Parent_Lpn_ID NUMBER DEFAULT NULL,
717 X_Cost_Group_ID NUMBER DEFAULT NULL,
718 -- BEGIN INVCONV
719 X_Count_Secondary_Uom_First VARCHAR2 DEFAULT NULL,
720 X_Count_Secondary_Uom_Current VARCHAR2 DEFAULT NULL,
721 X_Count_Secondary_Uom_Prior VARCHAR2 DEFAULT NULL,
722 X_Secondary_Uom_Quantity_First NUMBER DEFAULT NULL,
723 X_Secondary_Uom_Quantity_Curr NUMBER DEFAULT NULL,
724 X_Secondary_Uom_Quantity_Prior NUMBER DEFAULT NULL,
725 X_Secondary_System_Qty_First NUMBER DEFAULT NULL,
726 X_Secondary_System_Qty_Current NUMBER DEFAULT NULL,
727 X_Secondary_System_Qty_Prior NUMBER DEFAULT NULL,
728 X_Secondary_Adjustment_Qty NUMBER DEFAULT NULL
729 -- END INVCONV
730
731 ) IS
732 --Bug 6012343 -Added the following variables
733 l_standard_operation_id NUMBER;
734 l_wms_installed boolean;
735 l_return_status VARCHAR2(300);
736 l_msg_count NUMBER;
737 l_msg_data VARCHAR2(300);
738 BEGIN
739 /* Bug 6012343 */
740 l_standard_operation_id := NULL;
741 l_wms_installed := WMS_INSTALL.check_install(l_return_status,
742 l_msg_count,
743 l_msg_data,
744 x_organization_id);
745
746 IF ((l_wms_installed) AND X_Entry_Status_Code=3) THEN
747
748 BEGIN
749 SELECT STANDARD_OPERATION_ID
750 INTO l_standard_operation_id
751 FROM BOM_STANDARD_OPERATIONS
752 WHERE WMS_TASK_TYPE = 3
753 AND ORGANIZATION_ID = x_organization_id
754 AND ROWNUM = 1;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 l_standard_operation_id := NULL ;
759 END;
760
761 END IF ;
762
763 /* Bug 6012343 */
764 UPDATE mtl_cycle_count_entries
765 SET
766 cycle_count_entry_id = X_Cycle_Count_Entry_Id,
767 last_update_date = X_Last_Update_Date,
768 last_updated_by = X_Last_Updated_By,
769 last_update_login = X_Last_Update_Login,
770 count_list_sequence = X_Count_List_Sequence,
771 count_date_first = X_Count_Date_First,
772 count_date_current = X_Count_Date_Current,
773 count_date_prior = X_Count_Date_Prior,
774 count_date_dummy = X_Count_Date_Dummy,
775 counted_by_employee_id_first = X_Counted_By_Employee_Id_First,
776 counted_by_employee_id_current = X_Counted_By_Employee_Id_Curr,
777 counted_by_employee_id_prior = X_Counted_By_Employee_Id_Prior,
778 counted_by_employee_id_dummy = X_Counted_By_Employee_Id_Dummy,
779 count_uom_first = X_Count_Uom_First,
780 count_uom_current = X_Count_Uom_Current,
781 count_uom_prior = X_Count_Uom_Prior,
782 count_quantity_first = X_Count_Quantity_First,
783 count_quantity_current = X_Count_Quantity_Current,
784 count_quantity_prior = X_Count_Quantity_Prior,
785 inventory_item_id = X_Inventory_Item_Id,
786 subinventory = X_Subinventory,
787 entry_status_code = X_Entry_Status_Code,
788 count_due_date = X_Count_Due_Date,
789 organization_id = X_Organization_Id,
790 cycle_count_header_id = X_Cycle_Count_Header_Id,
791 number_of_counts = X_Number_Of_Counts,
792 locator_id = X_Locator_Id,
793 adjustment_quantity = X_Adjustment_Quantity,
794 adjustment_date = X_Adjustment_Date,
795 adjustment_amount = X_Adjustment_Amount,
796 item_unit_cost = X_Item_Unit_Cost,
797 inventory_adjustment_account = X_Inventory_Adjustment_Account,
798 approval_date = X_Approval_Date,
799 approver_employee_id = X_Approver_Employee_Id,
800 revision = X_Revision,
801 lot_number = X_Lot_Number,
802 lot_control = X_Lot_Control,
803 system_quantity_first = X_System_Quantity_First,
804 system_quantity_current = X_System_Quantity_Current,
805 system_quantity_prior = X_System_Quantity_Prior,
806 reference_first = X_Reference_First,
807 reference_current = X_Reference_Current,
808 reference_prior = X_Reference_Prior,
809 primary_uom_quantity_first = X_Primary_Uom_Quantity_First,
810 primary_uom_quantity_current = X_Primary_Uom_Quantity_Current,
811 primary_uom_quantity_prior = X_Primary_Uom_Quantity_Prior,
812 count_type_code = X_Count_Type_Code,
813 transaction_reason_id = X_Transaction_Reason_Id,
814 approval_type = X_Approval_Type,
815 attribute_category = X_Attribute_Category,
816 attribute1 = X_Attribute1,
817 attribute2 = X_Attribute2,
818 attribute3 = X_Attribute3,
819 attribute4 = X_Attribute4,
820 attribute5 = X_Attribute5,
821 attribute6 = X_Attribute6,
822 attribute7 = X_Attribute7,
823 attribute8 = X_Attribute8,
824 attribute9 = X_Attribute9,
825 attribute10 = X_Attribute10,
826 attribute11 = X_Attribute11,
827 attribute12 = X_Attribute12,
828 attribute13 = X_Attribute13,
829 attribute14 = X_Attribute14,
830 attribute15 = X_Attribute15,
831 serial_number = X_Serial_Number,
832 serial_detail = X_Serial_Detail,
833 approval_condition = X_Approval_Condition,
834 neg_adjustment_quantity = X_Neg_Adjustment_Quantity,
835 neg_adjustment_Amount = X_Neg_Adjustment_Amount,
836 outermost_lpn_id = X_Outermost_Lpn_ID,
837 parent_lpn_id = X_Parent_Lpn_ID,
838 cost_group_id = X_Cost_Group_ID,
839 -- BEGIN INVCONV
840 Count_Secondary_Uom_First = X_Count_Secondary_Uom_First,
841 Count_Secondary_Uom_Current = X_Count_Secondary_Uom_Current,
842 Count_Secondary_Uom_Prior = X_Count_Secondary_Uom_Prior,
843 Secondary_Uom_Quantity_First = X_Secondary_Uom_Quantity_First,
844 Secondary_Uom_Quantity_Current = X_Secondary_Uom_Quantity_Curr,
845 Secondary_Uom_Quantity_Prior = X_Secondary_Uom_Quantity_Prior,
846 Secondary_System_Qty_First = X_Secondary_System_Qty_First,
847 Secondary_System_Qty_Current = X_Secondary_System_Qty_Current,
848 Secondary_System_Qty_Prior = X_Secondary_System_Qty_Prior,
849 Secondary_Adjustment_Quantity = X_Secondary_Adjustment_Qty,
850 -- END INVCONV
851 standard_operation_id = l_standard_operation_id --End of fix of Bug 6012343
852 WHERE rowid = X_Rowid;
853
854 if (SQL%NOTFOUND) then
855 Raise NO_DATA_FOUND;
856 end if;
857 END Update_Row;
858 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
859 BEGIN
860 DELETE FROM mtl_cycle_count_entries
861 WHERE rowid = X_Rowid;
862
863 if (SQL%NOTFOUND) then
864 Raise NO_DATA_FOUND;
865 end if;
866 END Delete_Row;
867
868
869 END MTL_CYCLE_COUNT_ENTRIES_PKG;