[Home] [Help]
PACKAGE BODY: APPS.WMA_MATERIAL
Source
1 PACKAGE BODY wma_material AS
2 /* $Header: wmapmtlb.pls 120.1 2006/05/06 04:04:36 mraman noship $ */
3
4 /**
5 * Forward declaration
6 */
7 Function checkQty(txnType NUMBER,
8 txnQty NUMBER,
9 errMessage OUT NOCOPY VARCHAR2) RETURN boolean;
10
11
12 /**
13 * This procedure is the entry point into the Material Transaction
14 * processing code for processing.
15 * Parameters:
16 * parameters ResParams contains values from the mobile form.
17 * status Indicates success (0), failure (-1).
18 * errMessage The error or warning message, if any.
19 */
20 PROCEDURE process(param IN MtlParam,
21 status OUT NOCOPY NUMBER,
22 errMessage OUT NOCOPY VARCHAR2) IS
23 error VARCHAR2(241); -- error message
24 mtlRec MtlRecord; -- record to populate and insert
25 procMode NUMBER;
26 l_returnStatus VARCHAR2(1);
27 l_params wip_logger.param_tbl_t;
28 l_logLevel NUMBER;
29 BEGIN
30 savepoint wmapmtlb0;
31
32 l_logLevel := to_number(fnd_log.g_current_runtime_level);
33
34 if (l_logLevel <= wip_constants.trace_logging) then
35 l_params(1).paramName := 'not printing params';
36 l_params(1).paramValue := ' ';
37 wip_logger.entryPoint(p_procName => 'wma_material.process',
38 p_params => l_params,
39 x_returnStatus => l_returnStatus);
40 end if;
41
42 status := 0;
43
44 if(l_logLevel <= wip_constants.full_logging) then
45 wip_logger.log('before derive', l_returnStatus);
46 end if;
47
48 -- derive and validate all necessary fields for insertion
49 if ( derive(param, mtlRec, error) = false ) then
50 -- process error
51 status := -1;
52 errMessage := error;
53 return;
54 end if;
55
56 if(l_logLevel <= wip_constants.full_logging) then
57 wip_logger.log('before put', l_returnStatus);
58 end if;
59
60 -- insert into the interface table for background processing
61 if ( put(mtlRec, error) = false ) then
62 -- process error
63 status := -1;
64 errMessage := error;
65 return;
66 end if;
67
68 --if online, go ahead and process the txn
69 -- if(wma_derive.getTxnMode(param.environment.orgID) = wip_constants.online OR
70 -- param.isFromSerializedPage = 1 ) then
71 -- wip_mtlProc_priv.processTemp(p_initMsgList => fnd_api.g_true,
72 -- p_processInv => fnd_api.g_true,
73 -- p_endDebug => fnd_api.g_true,
74 -- p_txnTmpID => param.transactionTempID,
75 -- x_returnStatus => l_returnStatus);
76
77 if(l_logLevel <= wip_constants.full_logging) then
78 wip_logger.log('before processor', l_returnStatus);
79 end if;
80
81 wip_mtlTempProc_priv.processTemp(p_initMsgList => fnd_api.g_true,
82 p_txnHdrID => mtlRec.transaction_header_id,
83 p_txnMode => mtlRec.transaction_mode,
84 p_destroyQtyTrees => fnd_api.g_true,
85 x_returnStatus => l_returnStatus,
86 x_errorMsg => errMessage);
87 if(l_returnStatus <> fnd_api.g_ret_sts_success) then
88 raise fnd_api.g_exc_unexpected_error;
89 end if;
90
91 if (l_logLevel <= wip_constants.trace_logging) then
92 wip_logger.exitPoint(p_procName => 'wma_material.process',
93 p_procReturnStatus => status,
94 p_msg => 'success',
95 x_returnStatus => l_returnStatus);
96 end if;
97 EXCEPTION
98 when fnd_api.g_exc_unexpected_error then
99 rollback to wmapmtlb0;
100 status := -1;
101 -- wip_utilities.get_message_stack(p_msg => errMessage);
102 if (l_logLevel <= wip_constants.trace_logging) then
103 wip_logger.exitPoint(p_procName => 'wma_material.process',
104 p_procReturnStatus => status,
105 p_msg => errMessage,
106 x_returnStatus => l_returnStatus);
107 end if;
108 when others then
109 status := -1;
110 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
111 fnd_message.set_token ('FUNCTION', 'wma_material.process');
112 fnd_message.set_token ('ERROR', SQLERRM);
113 errMessage := fnd_message.get;
114 if (l_logLevel <= wip_constants.trace_logging) then
115 wip_logger.exitPoint(p_procName => 'wma_material.process',
116 p_procReturnStatus => status,
117 p_msg => errMessage,
118 x_returnStatus => l_returnStatus);
119 end if;
120 END process;
121
122
123 /**
124 * This function is used to derive the neccessary information to filled out
125 * the MtlRecord structure to passed into function put.
126 *
127 * HISTORY:
128 * 30-DEC-2004 spondalu Bug 4093569: eAM-WMS Integration enhancements: Set value
129 * for element rebuild_item_id of record mtlRec
130 */
131 Function derive(param MtlParam,
132 mtlRec OUT NOCOPY MtlRecord,
133 errMsg OUT NOCOPY VARCHAR2) Return boolean IS
134
135 periodID number;
136 job wma_common.Job;
137 item wma_common.Item;
138 openPastPeriod boolean := false;
139
140 Begin
141
142 job := wma_derive.getJob(param.jobID);
143 if (job.wipEntityID is null) then
144 fnd_message.set_name ('WIP', 'WIP_JOB_DOES_NOT_EXIST');
145 fnd_message.set_token('INTERFACE', 'wma_material.derive', TRUE);
146 errMsg := fnd_message.get;
147 return false;
148 end if;
149
150 item := wma_derive.getItem(param.itemID,
151 param.environment.orgID,
152 param.locatorID);
153
154 -- First, check qty entered.
155 if ( checkQty(param.transactionType,
156 param.transactionQty,
157 errMsg) = false ) then
158 return false;
159 end if;
160
161 -- check the project reference
162 if ( pjm_project_locator.check_project_references(
163 param.environment.orgID,
164 param.locatorID,
165 'SPECIFIC',
166 'N',
167 job.projectID,
168 job.taskID) = false ) then
169 errMsg := FND_MESSAGE.get;
170 return false;
171 end if;
172
173
174
175 -- get the accounting period
176 invttmtx.tdatechk(
177 org_id => param.environment.orgID,
178 transaction_date => sysdate,
179 period_id => periodID,
180 open_past_period => openPastPeriod);
181
182 if (periodID = -1 or periodID = 0) then
183 fnd_message.set_name(
184 application => 'INV',
185 name => 'INV_NO_OPEN_PERIOD');
186 errMsg := fnd_message.get;
187 return false;
188 end if;
189
190 mtlRec.final_completion_flag := 'N';
191 mtlRec.transaction_header_id := param.transactionHeaderID;
192 mtlRec.transaction_interface_id := param.transactionIntID;
193
194 if(wma_derive.getTxnMode(param.environment.orgID) = wip_constants.online OR
195 param.isFromSerializedPage = 1 ) then
196 mtlRec.transaction_mode := WIP_CONSTANTS.ONLINE;
197 else
198 mtlRec.transaction_mode := WIP_CONSTANTS.BACKGROUND;
199 end if;
200 -- mtlRec.lock_flag := 'N';
201 mtlRec.inventory_item_id := param.itemID;
202 mtlRec.subinventory_code := param.subinventoryCode;
203 mtlRec.transaction_date := sysdate;
204 mtlRec.organization_id := param.environment.orgID;
205 mtlRec.acct_period_id := periodID;
206 mtlRec.last_update_date := sysdate;
207 mtlRec.last_updated_by := param.environment.userID;
208 mtlRec.creation_date := sysdate;
209 mtlRec.created_by := param.environment.userID;
210 mtlRec.transaction_source_id := param.jobID;
211 mtlRec.transaction_source_type_id := 5;
212
213 if ( param.isFromSerializedPage = 1 ) then
214 mtlRec.source_code := wma_common.SERIALIZATION_SOURCE_CODE;
215 else
216 mtlRec.source_code := wma_common.SOURCE_CODE;
217 end if;
218
219 mtlRec.source_line_id := -1;
220 mtlRec.source_header_id := -1;
221 -- set up the transaction action id and trx quantity
222 mtlRec.transaction_quantity := param.transactionQty;
223 mtlRec.primary_quantity := param.transactionQty;
224 mtlRec.negative_req_flag := 1;
225 if ( param.transactionType = WIP_CONSTANTS.ISSCOMP_TYPE ) then
226 mtlRec.transaction_action_id := WIP_CONSTANTS.ISSCOMP_ACTION;
227 mtlRec.transaction_quantity := param.transactionQty * -1;
228 mtlRec.primary_quantity := param.transactionQty * -1;
229 elsif ( param.transactionType = WIP_CONSTANTS.RETCOMP_TYPE ) then
230 mtlRec.transaction_action_id := WIP_CONSTANTS.RETCOMP_ACTION;
231 elsif ( param.transactionType = WIP_CONSTANTS.ISSNEGC_TYPE ) then
232 mtlRec.transaction_action_id := WIP_CONSTANTS.ISSNEGC_ACTION;
233 mtlRec.negative_req_flag := -1;
234 elsif ( param.transactionType = WIP_CONSTANTS.RETNEGC_TYPE ) then
235 mtlRec.transaction_action_id := WIP_CONSTANTS.RETNEGC_ACTION;
236 mtlRec.transaction_quantity := param.transactionQty * -1;
237 mtlRec.primary_quantity := param.transactionQty * -1;
238 mtlRec.negative_req_flag := -1;
239 end if;
240 mtlRec.transaction_type_id := param.transactionType;
241 mtlRec.wip_entity_type := WIP_CONSTANTS.DISCRETE;
242
243 mtlRec.locator_id := param.locatorID;
244 mtlRec.operation_seq_num := param.opSeqNum;
245 mtlRec.department_id := param.deptID;
246 -- mtlRec.row.item_trx_enabled_flag := item.mtlTxnsEnabled;
247 -- mtlRec.row.item_description := item.description;
248 -- mtlRec.row.item_location_control_code := item.locationControlCode;
249 -- mtlRec.row.item_restrict_subinv_code := item.restrictSubinvCode;
250 -- mtlRec.row.item_restrict_locators_code := item.restrictLocatorsCode;
251 -- mtlRec.row.item_revision_qty_control_code := item.revQtyControlCode;
252 mtlRec.revision := param.revision;
253 -- mtlRec.item_primary_uom_code := item.primaryUOMCode;
254 mtlRec.transaction_uom := param.transactionUOM;
255 -- mtlRec.row.item_inventory_asset_flag := item.invAssetFlag;
256 -- mtlRec.row.allowed_units_lookup_code := item.allowedUnitsLookupCode;
257 -- mtlRec.row.item_shelf_life_code := item.shelfLifeCode;
258 -- mtlRec.row.item_shelf_life_days := item.shelfLifeDays;
259 -- mtlRec.row.item_serial_control_code := item.serialNumberControlCode;
260 -- mtlRec.row.item_lot_control_code := item.lotControlCode;
261 -- mtlRec.row.posting_flag := 'Y';
262
263 mtlRec.process_flag := wip_constants.mti_inventory;
264
265 mtlRec.project_id := param.projectID;
266 mtlRec.task_id := param.taskID;
267 mtlRec.source_project_id := job.projectID;
268 mtlRec.source_task_id := job.taskID;
269 mtlRec.qa_collection_id := param.qualityID;
270 mtlRec.wip_entity_type := param.wipEntityType;
271 if (param.wipEntityType = WIP_CONSTANTS.EAM) then
272 mtlRec.rebuild_item_id := param.itemID;
273 else
274 mtlRec.rebuild_item_id := NULL;
275 end if;
276 return true;
277 End derive;
278
279
280 /**
281 * This function is used to insert the record encapsulated in mtlRec to
282 * table MMTT and some furthur validation and processing.
283 *
284 * HISTORY:
285 * 30-DEC-2004 spondalu Bug 4093569: eAM-WMS Integration enhancements:
286 * Insert rebuild_item_id into mti.
287 */
288 Function put(mtlRec MtlRecord, errMsg OUT NOCOPY VARCHAR2) return boolean IS
289 l_retStatus VARCHAR2(1);
290 l_logLevel NUMBER;
291 Begin
292 l_logLevel := fnd_log.g_current_runtime_level;
293
294 INSERT INTO mtl_transactions_interface
295 (wip_supply_type,
296 final_completion_flag,
297 transaction_header_id,
298 transaction_interface_id,
299 transaction_mode,
300 -- lock_flag,
301 inventory_item_id,
302 subinventory_code,
303 primary_quantity,
304 transaction_quantity,
305 transaction_date,--10
306 organization_id,
307 acct_period_id,
308 last_update_date,
309 last_updated_by,
310 creation_date,
311 created_by,
312 transaction_source_id,
313 transaction_source_type_id,
314 transaction_type_id,
315 transaction_action_id,--20
316 wip_entity_type,
317 locator_id,
318 operation_seq_num,
319 department_id,
320 -- item_trx_enabled_flag,
321 -- item_description,
322 -- item_location_control_code,
323 -- item_restrict_subinv_code,
324 -- item_restrict_locators_code,
325 -- item_revision_qty_control_code, --30
326 revision,
327 -- item_primary_uom_code,
328 transaction_uom,
329 -- item_inventory_asset_flag,
330 -- allowed_units_lookup_code,
331 -- item_shelf_life_code,
332 -- item_shelf_life_days,
333 -- item_serial_control_code,
334 -- item_lot_control_code,
335 negative_req_flag,
336 -- posting_flag,
337 process_flag,
338 project_id,
339 task_id,
340 source_project_id,
341 source_task_id,
342 qa_collection_id,
343 source_code,
344 source_line_id,
345 source_header_id,
346 rebuild_item_id)
347 VALUES
348 (wip_constants.push, --always a push item
349 mtlRec.final_completion_flag,
350 mtlRec.transaction_header_id,
351 mtlRec.transaction_interface_id,
352 mtlRec.transaction_mode,
353 -- mtlRec.lock_flag,
354 mtlRec.inventory_item_id,
355 mtlRec.subinventory_code,
356 mtlRec.primary_quantity,
357 mtlRec.transaction_quantity,
358 mtlRec.transaction_date,--10
359 mtlRec.organization_id,
360 mtlRec.acct_period_id,
361 mtlRec.last_update_date,
362 mtlRec.last_updated_by,
363 mtlRec.creation_date,
364 mtlRec.created_by,
365 mtlRec.transaction_source_id,
366 mtlRec.transaction_source_type_id,
367 mtlRec.transaction_type_id,
368 mtlRec.transaction_action_id,--20
369 mtlRec.wip_entity_type,
370 mtlRec.locator_id,
371 mtlRec.operation_seq_num,
372 mtlRec.department_id,
373 -- mtlRec.item_trx_enabled_flag,
374 -- mtlRec.item_description,
375 -- mtlRec.item_location_control_code,
376 -- mtlRec.item_restrict_subinv_code,
377 -- mtlRec.item_restrict_locators_code,
378 -- mtlRec.item_revision_qty_control_code,
379 mtlRec.revision,
380 -- mtlRec.item_primary_uom_code,
381 mtlRec.transaction_uom,
382 -- mtlRec.item_inventory_asset_flag,
383 -- mtlRec.allowed_units_lookup_code,
384 -- mtlRec.item_shelf_life_code,
385 -- mtlRec.item_shelf_life_days,
386 -- mtlRec.item_serial_control_code,
387 -- mtlRec.item_lot_control_code,
388 mtlRec.negative_req_flag,
389 -- mtlRec.posting_flag,
390 mtlRec.process_flag,
391 mtlRec.project_id,
392 mtlRec.task_id,
393 mtlRec.source_project_id,
394 mtlRec.source_task_id,
395 mtlRec.qa_collection_id,
396 mtlRec.source_code,
397 mtlRec.source_line_id,
398 mtlRec.source_header_id,
399 mtlRec.rebuild_item_id);
400
401 wip_mtlTempProc_priv.validateInterfaceTxns(p_txnHdrID => mtlRec.transaction_header_id,
402 p_initMsgList => fnd_api.g_true,
403 x_returnStatus => l_retStatus);
404
405 if(l_retStatus <> fnd_api.g_ret_sts_success) then
406 if (l_logLevel <= wip_constants.full_logging) then
407 wip_logger.log('put: error from validateInterfaceTxns', l_retStatus);
408 end if;
409 wip_utilities.get_message_stack(p_msg => errMsg);
410 return false;
411 end if;
412
413 return true;
414
415 EXCEPTION
416 when others then
417 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
418 fnd_message.set_token ('FUNCTION', 'wma_material.derive');
419 fnd_message.set_token ('ERROR', SQLERRM);
420 errMsg := fnd_message.get;
421 return false;
422 End put;
423
424 /**
425 * This is a private function used by this pacakge only. It checks the qty entered
426 * for material txn to make sure the qty > 0.
427 */
428 Function checkQty(txnType NUMBER,
429 txnQty NUMBER,
430 errMessage OUT NOCOPY VARCHAR2) RETURN boolean IS
431 Begin
432 if ( txnQty <= 0 ) then
433 fnd_message.set_name('INV', 'INV_GREATER_THAN_ZERO');
434 errMessage := fnd_message.get;
435 return false;
436 end if;
437
438 return true;
439 End checkQty;
440
441 procedure validateIssueProject(p_orgID in number,
442 p_wipEntityID in number,
443 p_locatorID in number,
444 p_allowCrossIssue in number,
445 x_projectID out nocopy number,
446 x_taskID out nocopy number,
447 x_projectNum out nocopy varchar2,
448 x_taskNum out nocopy varchar2,
449 x_returnStatus out nocopy varchar2,
450 x_returnMsg out nocopy varchar2) is
451 l_jobProjectID number;
452 l_jobTaskID number;
453 l_locProjectID number;
454 l_locTaskID number;
455 l_num number;
456 begin
457 x_returnStatus := fnd_api.g_ret_sts_success;
458 x_projectID := null;
459 x_taskID := null;
460 x_projectNum := null;
461 x_taskNum := null;
462 x_returnMsg := null;
463
464 /* Bug 5111027 - The following SQL will raise a
465 NO DATA FOUND exception for PJM organization with no locator
466 control at org/subinventory/item level. Added EXCEPTION
467 block to handle it.*/
468 BEGIN
469 select project_id, task_id
470 into l_locProjectID, l_locTaskID
471 from mtl_item_locations_kfv
472 where organization_id = p_orgID
473 and inventory_location_id = p_locatorID;
474 EXCEPTION
475 when NO_DATA_FOUND THEN
476 RETURN;
477 END;
478
479
480 -- no project/task reference on the locator
481 if ( l_locProjectID is null and l_locTaskID is null ) then
482 return;
483 end if;
484
485 x_projectID := l_locProjectID;
486 x_taskID := l_locTaskID;
487 x_projectNum := pjm_project.all_proj_idtonum(l_locProjectID);
488 x_taskNum := pjm_project.all_task_idtonum(l_locTaskID);
489
490 select project_id, task_id
491 into l_jobProjectID, l_jobTaskID
492 from wip_discrete_jobs
493 where organization_id = p_orgID
494 and wip_entity_id = p_wipEntityID;
495
496 -- you can't issue project controlled item to non project controlled job
497 if ( l_jobProjectID is null ) then
498 x_returnStatus := fnd_api.g_ret_sts_error;
499 x_projectID := null;
500 x_taskID := null;
501 x_projectNum := null;
502 x_taskNum := null;
503 fnd_message.set_name('WIP', 'JOB_NOT_PROJ_CNTL');
504 x_returnMsg := fnd_message.get;
505 return;
506 end if;
507
508
509 -- project/task are the same with job project/task
510 if ( (l_locProjectID = l_jobProjectID) and (l_locTaskID = l_jobTaskID) ) then
511 return;
512 end if;
513
514 -- project is the same and all cross project issue
515 if ( (l_locProjectID = l_jobProjectID) and (p_allowCrossIssue = 1) ) then
516 return;
517 end if;
518
519
520 -- diff project with the same cost group and planning group when
521 -- allow cross issue
522 if ( l_locProjectID <> l_jobProjectID and p_allowCrossIssue = 1 ) then
523 select count(*)
524 into l_num
525 from pjm_project_parameters pp,
526 pjm_project_parameters jpp
527 where pp.organization_id = p_orgID
528 and pp.project_id = l_locProjectID
529 and jpp.organization_id = p_orgID
530 and jpp.project_id = l_jobProjectID
531 and pp.costing_group_id = jpp.costing_group_id
532 and pp.planning_group = jpp.planning_group;
533 if ( l_num > 0 ) then
534 return;
535 end if;
536 end if;
537
538 x_returnStatus := fnd_api.g_ret_sts_error;
539 x_projectID := null;
540 x_taskID := null;
541 x_projectNum := null;
542 x_taskNum := null;
543 fnd_message.set_name('WIP', 'INVALID_SER_PROJECT_TASK');
544 x_returnMsg := fnd_message.get;
545 end validateIssueProject;
546
547
548 END wma_material;