4 * The Cycle Count Interface procedures allow users to perform online processing to the Cycle Count
1 PACKAGE MTL_CCEOI_ACTION_PUB AUTHID CURRENT_USER AS
2 /* $Header: INVPCCAS.pls 120.0 2005/05/25 06:00:00 appldev noship $ */
3 /*#
5 * Interface records and let the user import the records from the Cycle Count Open Interface
6 * table.
7 * @rep:scope public
8 * @rep:product INV
9 * @rep:lifecycle active
10 * @rep:displayname Cycle Count Interface API
11 * @rep:category BUSINESS_ENTITY INV_COUNT
12 */
13
14 -- Online processing for one record
15 /*#
16 * This procedure allows users to perform online processing to the Cycle Count Interface records
17 * and lets the user import the records from the Cycle Count Open Interface table. The procedure will
18 * process those interface records that have the process flag set to ready for processing and will first validate
19 * all such interface rows before importing the record.
20 *
21 * @param x_return_status return Variable holding the status of the procedure call
22 * @param x_msg_count return Variable holding the number of error messages returned
23 * @param x_msg_data return Variable holding the error message
24 * @param x_errorcode return Variable that holds the error code. This code holds the reason why record was not imported.
25 * @param x_interface_id return This variable holds Interface Id of record processed
26 * @param p_api_version Current API version number
27 * @param p_init_msg_list The value of this variable decides whether to initialize the message list.
28 * @param p_commit The value of this parameter is checked to call commit.
29 * @param p_validation_level the value of this parameter decides the level of validation to be done on Interface records.
30 * @param p_interface_rec This will hold the complete interface record.
31 * @rep:scope public
32 * @rep:lifecycle active
33 * @rep:displayname Import Cycle Count Entries
34 */
35 PROCEDURE Import_CountRequest(
36 p_api_version IN NUMBER ,
37 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
38 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
39 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
40 X_return_status OUT NOCOPY VARCHAR2 ,
41 x_errorcode OUT NOCOPY NUMBER,
42 x_msg_count OUT NOCOPY NUMBER ,
43 x_msg_data OUT NOCOPY VARCHAR2 ,
44 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE,
45 x_interface_id OUT NOCOPY NUMBER );
46
47 -- Start OF comments
48 -- API name : Import_CountRequest
49 -- TYPE : Public
50 -- Pre-reqs : None
51 -- FUNCTION : Performs a specified type of processing on an interface record
52 --
53 -- Parameters:
54 -- IN : p_api_version IN NUMBER (required)
55 -- API Version of this procedure
56 -- p_init_msg_level IN VARCHAR2 (optional)
57 -- DEFAULT = FND_API.G_FALSE,
58 -- p_commit IN VARCHAR2 (optional)
59 -- DEFAULT = FND_API.G_FALSE,
60 -- p_validation_level IN NUMBER (optional)
61 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
62 -- (not used at present time)
63 -- p_interface_rec IN CCEOI_Rec_Type (required)
64 -- complete interface RECORD
65 -- See description below for instructions on setting this parameter
66 --
67 -- OUT: x_msg_count OUT NUMBER,
68 -- number of messages in the message list
69 -- x_msg_data OUT VARCHAR2,
70 -- if number of messages is 1, then this parameter
71 -- contains the message itself
72 --
73 -- x_interface_id OUT NUMBER
74 -- returns interface id of record processed
75 --
76 -- X_return_status OUT NUMBER
77 -- Result of all the operations
78 -- FND_API.G_RET_STS_SUCCESS if success
79 -- FND_API.G_RET_STS_ERROR if error
80 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
81 --
82 -- X_ErrorCode OUT NUMBER
83
84 -- RETURN value OF the x_errorcode
85 -- check only if x_return_status <> fnd_api.g_ret_sts_success
86 --
87 -- These errors are unrecoverable and interface record was not
88 -- inserted as a result of this thing
89 -- 200 - Interface row is locked by someone else
90 -- 201 - Interface row is trying to process cycle count entry that
91 -- has been exported to some other interface record
92 -- 202 - Interface record is trying to process cycle count entry
93 -- of not open status
94 -- 203 - field cycle_count_entry_id inside interface record points to
98 -- 205 - passed interface record id points to non-existing
95 -- non exising cycle count entry
96 -- 204 - Interface row has already been marked for deletion, therefore
97 -- no more processing should be done
99 -- record in the table
100 -- 206 - Interface record has already been processed, and no
101 -- additional processing should be done
102 -- 207 - Interface record is marked as not ready
103 -- -1 - unexpected error - all operations have been rollbacked
104 --
105 --
106 -- Version: Current Version 0.9
107 -- Changed
108 -- Previous Version Y.X
109 -- Initial version 0.9
110 -- Notes :
111 /*
112 Description:
113
114 This API is designed to automate manual processing of count entries
115 info previously done through cycle count count forms.
116 Its functionality is almost equivalent to that of forms with few
117 exceptions which are listed in "Processing" section of this document.
118
119 It is possible to process existing cycle count requests, create and/or
120 process new unscheduled cycle count entries, validate data in interface,
121 and, finally, simulate processing of a count request (no tables will be
122 affected except for cycle count open interface tables)
123
124
125 This procedure takes an interface row passed through
126 p_interface_rec parameter and processes it, based on values of
127 the parameter's fields.
128 p_interface_rec is of type MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE that is
129 composed of the fields corresponding to columns of
130 MTL_CC_ENTRIES_INTERFACE table.
131
132 Processing starts from inserting/updating p_interface_rec into the
133 interface table, locking it, and for processing scheduled requests
134 exporting corresponding cycle count entry.
135
136 If this operation fails then interface record is not
137 inserted/updated into a the inteface table and error code is returned.
138 It could fail for few main reasons:
139 1) interface record is locked => someone else is processing that same
140 interface record
141 2) Corresponding cycle count entry is not an open request/does not exist
142 3) Trying to re-process processed interface record
143 4) some non-null columns are missing (e.g. organization_id) and, therefore,
144 interface record cannot be inserted
145
146 -------------------------------------------------------------------------
147 Use of fields of p_interface_rec:
148
149 CC_ENTRY_INTERFACE_ID is a primary key of the cc entries interface table
150 if it is filled out then we base processing on this record in the interface.
151 we will check whether it points to a valid id, and if not error will be
152 reported.
153 if cc_entry_intreface_id is left blank then a new interface record
154 will be created unless there is a an unprocessed interface record inside
155 the cc interface that corresponds to the same count request, in which
156 case we find and use interface id of that already exising record and try
157 to finish processing it.
158
159 ORGANIZATION_ID always has to be filled out
160
161 Two controlling fields are PROCESS_MODE, and ACTION_CODE.
162 PROCESS_MODE defines whether a record should be processed in online mode
163 (process_mode = 1), or in background_mode (process_mode = 3).
164
165 The procedure operates in any mode, but concurrent import program
166 will only pickup record marked for background mode.
167 Background mode processing is done using concurrent programs accessible
168 through INVCCEOI form. The concurrent programs call onto the same base code
169 as Open Interface public API, and have the same functionality.
170
171 ACTION_CODE determines type of processing that is going to be done
172
173 11 = G_VALIDATE - will check validity of data in the record
174 The validation functionality will also be invoked from
175 G_PROCESS, G_CREATE, and G_VALSIM. The validation consists of five
176 major parts:
177 1) validating cycle count header
178 2) validating count list sequence
179 3) validating SKU info
180 4) validating UOM and quantity,
181 5) validating count date and counter info.
182
183 If validation is called directly or invoked as part of G_CREATE then
184 only first 3 parts of validation will be executed
185 (only fields of p_interface_rec that need to be filled out are
186 the fields mentioned below in steps 1-3)
187 If action_code was G_PROCESS or G_VALSIM then all 5 validation steps
188 will be done (all mentioned fields need to be filled out).
189
190 if cycle_count_entry_id is not null and points to a open and unexported
191 cycle count entry then fields mentioned in steps 1 - 3 will be
192 derived from that entry (their passed values will be ignored), if it
193 contains invalid data then an error will be thrown and interface
194 record will not be inserted/updated into interface
195
196 1) Validate Cycle Count Header:
197 either Cycle_Count_Header_id or Cycle_Count_Header_Name should
198 correspond to an existing cycle count
199
200 2) Validate Count List Sequence:
201 Count_List_Sequence should be either:
202 a) existing list sequence within the cycle count for open request
203
204 b) non existing count list sequence id
205
206 c) null
207 in which case a new count list sequence will be automatically
208 generated for it
209
210 In case of a) Item and SKU info will be derived automatically from
211 MTL_CYCLE_COUNT_ENTRIES, and, therefore, item and sku fields do not
212 have to be filled out. Their values will be ignored and
216
213 overwritten by data drawn from the cycle count entries table.
214
215 In case of b) and c) step 3 will have to be performed
217
218 3) Validate Item and SKU info:
219 for that the following fields need to be filled out:
220 a) Inventory_Item_Id and/or Item_Segment1..Item_Segment20
221 b) Subinventory
222 c) Revision
223 d) Lot_Number
224 e) Serial_Number
225 f) Locator_Id and/or Locator_Segment1..Locator_Segment20
226
227 note: id parameter always takes precedence over values specified
228 in segments
229
230 The following checks will be run only if the validation is called
231 with processing option (if called through G_PROCESS, or G_VALSIM)
232
233 4) Validate UOM and quantity
234 the following fields need to be filled out:
235 a) Primary_Uom_Quantity and/or
236 ((Count_Uom and/or Count_Unit_Of_Measure) and Count_quantity)
237
238 note: specified primary_uom_quantity takes precedence over specified
239 count_uom and count quantity
240
241 b) System_Quantity may also be filled out (if left null,
242 it will be computed automatically).
243 Warning: providing system quantity in count UOM will make
244 the program run slightly faster, but it will also mean that
245 the program will not compute the actual system quantity and
246 thus may create incorrect adjustments if user did not enter
247 system quantity correctly. If this field is left blank then
248 system quantity at the time of processing will be computed.
249
250 5) Validate count date and counter info
251 the following fields are to be filled out:
252 a) Count_Date
253 b) Employee_Id and/or
254 Employee_Full_Name which can be either the full name or
255 just the last name
256
257 employee_id takes precedence over employee name
258
259 12 = G_CREATE - will create corresponding cycle count entry request in
260 MTL_CYCLE_COUNT_ENTRIES. It can be called directly, or it may
261 be automatically invoked from G_PROCESS, or G_VALSIM if processing of
262 the record requires creating a new count request.
263 At first, it will try to validate data (first 3 steps), and
264 then try to create a corresponding count request. See G_VALIDATE
265 for p_interface_rec fields to be filled out
266
267 13 = G_VALSIM - will simulate full validation and processing of
268 an interface record. This mode is equivalent to the process mode
269 with exception that no modifications will be made to tables other
270 than interface tables.
271
272 14 = G_PROCESS - will validate and process the interface record.
273 If corresponding count request does not exist
274 in MTL_CYCLE_COUNT_ENTRIES then it will be
275 created, and then processed
276
277
278 Brief description of other fields:
279
280 project_id - project id
281 task_id - task id
282
283
284 fields whose value should not be modified (API ignores supplied values):
285
286 process_flag - indicates to interface whether a record is ready for
287 processing
288 1, NULL - ready
289 2 - not ready
290 it is useful for marking records as unavailable in which case
291 those interface entries will not be picked up by a call to concurrent
292 request to process some or all interface records. The flag is manipulated
293 through Inquire/Update CC Interface Entries Form.
294
295 error_flag - indicates whether an error has occured during processing
296 1 - error
297 2 - no error
298
299 status_flag - indicates processing status of the record
300 0 - process completed
301 1 - processed with warnings
302 2 - processed with errors
303 3 - marked for recounting
304 4 - marked for reprocessing
305 5 - validated
306 6 - processing simulated
307 NULL - no status
308 public api will only process interface records whose status is NULL or 3,4
309 other statuses mean that the record is already processed
310
311 valid_flag - indicates whether data inside the interface record is valid
312 1 - valid
313 2, NULL - invalid (invalid could is set for new data or
314 data that errored out
315
316 lock_flag - indicates whether record is locked by a process for processing
317 1 - locked
318 2, NULL - not locked
319
320 cc_entry_interface_group_id - used by concurrent manager to divide processing
321 to different workers
322
323 program_application_id - program application id
324 program_id - program id
325 program_update_date - program update date
326
327 last_update_date - standard who column
328 last_updated_by - standard who column
329 creation_date - standard who column
330 created_by - standard who column
331 last_update_login - standard who column
332 -------------------------------------------------------------------------
333 Processing notes:
334 Do not populate interface manually, the only means of populating it
335 should be either through this API or using CC Export form.
336
337 If during processing we adjustment of system quantitities has to
338 be made then transaction is entered into
339 mtl_material_transactions_temp from where it will be picked up
340 by concurrent manager (it is not immediate).
341
342 Serial numbers are only partially validated during validation since
343 for complete validation of serial number we need to know whether it
347 number. Other issues can be caught only during processing.
344 is going to be issued, received, or kept where it is now. The only
345 problems that we are able to catch during initial validation of
346 serial number are issues related to ability to create this serial
348
349 If processing serial number requires transferring it from one location
350 to another then CC Open Interface will not be able to resolve that
351 and processing cycle count entry we will have to be completed manually
352 through the cycle count approval form (INVAMCAP).
353
354 Unscheduled entries will be created in mtl_cycle_count_entries and
355 marked as exported.
356
357 -------------------------------------------------------------------------
358 Post Processing:
359
360 After completion of processing error, delete, status and valid flags
361 will be set to values that correspond their state after
362 successful or unsuccessful processing(1 = TRUE, 2 or NULL = FALSE).
363 The interface record will also be unlocked.
364
365 In case of successful processing the corresponding cycle count entry
366 will be unexported.
367
368 If procedure does not complete successfuly, then the corresponding errors
369 are saved in the interface's errors table, and if a corresponding
370 cycle count entry was exported then it will remain exported.
371
372 If record was inserted updated (error code returned was not above 199)
373 then it's cc_entry_interface_id will be contained in x_interface_id.
374 It is useful for reprocessing of the record if it errored out and user
375 does not wish to create a whole new interface record with fixed data.
376
377 Interface records that are successfully processed are marked processed
378 or processed with warnings
379 and may later be removed using Purge CC Interface concurrent program.
380
381 ----------------------------------------------------------------------------
382 Example of usage:
383
384 create a new interface record INV_CCEOI_TYPE following the above
385 mentioned rules or retrieve data into interface record from the
386 the interface table (the table can be populated it by running
387 Inventory's export). Set process_mode, action_code, and any other
388 fields necessary. Call the procedure. Check for errors upon return.
389
390 Example:
391 Process an unscheduled count request for item SC55437, counted by Mr. Hat
392 in the Stores subinventory for organization 207. Counting revealed that there
393 were 100000 pieces of SC55437, and it was done on December 23, 1998.
394
395 declare
396 lstatus VARCHAR2(1);
397 lmsg_count NUMBER;
398 lmsg_data VARCHAR2(240);
399 lrec mtl_cceoi_var_pvt.inv_cceoi_type;
400 l_errorcode number;
401 l_interface_id number;
402
403 begin
404
405 -- set online processing mode
406 lrec.PROCESS_MODE := 1;
407
408 -- import request into the application and do any adjustments necessary
409 lrec.ACTION_CODE := mtl_cceoi_var_pvt.G_PROCESS;
410
411 -- necessary info
412
413 -- 1) cycle count header info
414 lrec.cycle_count_header_name := 'MCCOI';
415
416 -- 2) count list sequence of the enrty (null = don't know/unscheduled)
417 lrec.count_list_sequence := null;
418
419
420 -- 3) item and sku info
421
422 -- lrec.item_segment1 :=;
423 lrec.inventory_item_id = 151;
424
425 lrec.subinventory := 'Engineer';
426 lrec.revision := null;
427 lrec.lot_number := null;
428 lrec.serial_number := null;
429 lrec.locator_id := null;
430
431 lrec.organization_id := 207;
432
433
434 -- 4) validate and UOM qunatity
435 lrec.count_uom := 'Ea';
436 lrec.count_quantity := 100000;
437
438 -- 5) Date and Counter info
439 lrec.count_date := to_date('23-DEC-1998', 'DD-MON-YYYY');
440 lrec.employee_full_name := 'Hat, Mr.';
441
442 -- this record is not in the interface
443 lrec.cc_entry_interface_id := null;
444
445
446 -- call API
447 mtl_cceoi_action_pub.import_CountRequest(
448 p_api_version => 0.9,
449 p_commit => FND_API.G_TRUE,
450 x_return_status => lstatus,
451 x_errorcode => l_errorcode
452 x_msg_count => lmsg_count,
453 x_msg_data => lmsg_data,
454 p_interface_rec => lrec,
455 x_interface_id => l_interface_id);
456
457 if (lstatus = fnd_api.g_ret_sts_success) then
458 ....
459 else
460 ....
461 end if;
462
463
464 end;
465 */
466 -- END OF comments
467
468 /*All parameters are same as Import_CountRequest, with the exception of the out
469 variable. In this case the procedure will returns a list of interface ids. The
470 procedure acts as a wrapper for Import_CountRequest. In case where an lpn or
471 lpn id is specified in the interface record, Process_LPN_CountRequest will run
472 and Import_countrequest for each item within that lpn, returning the interface ids
473 given by Import_CountRequests in the out parameter list.
474 */
475 --
476 PROCEDURE Process_LPN_CountRequest
477 (
478 p_api_version IN NUMBER
479 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
480 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
481 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
482 , x_return_status OUT NOCOPY VARCHAR2
483 , x_errorcode OUT NOCOPY NUMBER
484 , x_msg_count OUT NOCOPY NUMBER
485 , x_msg_data OUT NOCOPY VARCHAR2
486 , p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE
487 , x_interface_id_list OUT NOCOPY MTL_CCEOI_VAR_PVT.INV_CCEOI_ID_TABLE_TYPE
488 );
489
490 END MTL_CCEOI_ACTION_PUB;
[Home] [Help]