[Home] [Help]
PACKAGE BODY: APPS.WMA_MATERIAL
Source
1 PACKAGE BODY wma_material AS
2 /* $Header: wmapmtlb.pls 120.2 2011/09/15 22:11:22 liawei ship $ */
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 --Bug12912518 Shouldn't allow Same Assembly item to be issued to itself as Component for standard WO
174 --which is consistent with Form/MES
175 if (job.primaryItemID is not null
176 AND job.primaryItemID = item.invItemID
177 AND job.jobType = 1) then -- for standard Job don't allow issue assembly as components
178 fnd_message.set_name ('WIP', 'WIP_NO_SAME_ASSEMBLY_ISSUE');
179 errMsg := fnd_message.get;
180 return false;
181 end if;
182
183 -- get the accounting period
184 invttmtx.tdatechk(
185 org_id => param.environment.orgID,
186 transaction_date => sysdate,
187 period_id => periodID,
188 open_past_period => openPastPeriod);
189
190 if (periodID = -1 or periodID = 0) then
191 fnd_message.set_name(
192 application => 'INV',
193 name => 'INV_NO_OPEN_PERIOD');
194 errMsg := fnd_message.get;
195 return false;
196 end if;
197
198 mtlRec.final_completion_flag := 'N';
199 mtlRec.transaction_header_id := param.transactionHeaderID;
200 mtlRec.transaction_interface_id := param.transactionIntID;
201
202 if(wma_derive.getTxnMode(param.environment.orgID) = wip_constants.online OR
203 param.isFromSerializedPage = 1 ) then
204 mtlRec.transaction_mode := WIP_CONSTANTS.ONLINE;
205 else
206 mtlRec.transaction_mode := WIP_CONSTANTS.BACKGROUND;
207 end if;
208 -- mtlRec.lock_flag := 'N';
209 mtlRec.inventory_item_id := param.itemID;
210 mtlRec.subinventory_code := param.subinventoryCode;
211 mtlRec.transaction_date := sysdate;
212 mtlRec.organization_id := param.environment.orgID;
213 mtlRec.acct_period_id := periodID;
214 mtlRec.last_update_date := sysdate;
215 mtlRec.last_updated_by := param.environment.userID;
216 mtlRec.creation_date := sysdate;
217 mtlRec.created_by := param.environment.userID;
218 mtlRec.transaction_source_id := param.jobID;
219 mtlRec.transaction_source_type_id := 5;
220
221 if ( param.isFromSerializedPage = 1 ) then
222 mtlRec.source_code := wma_common.SERIALIZATION_SOURCE_CODE;
223 else
224 mtlRec.source_code := wma_common.SOURCE_CODE;
225 end if;
226
227 mtlRec.source_line_id := -1;
228 mtlRec.source_header_id := -1;
229 -- set up the transaction action id and trx quantity
230 mtlRec.transaction_quantity := param.transactionQty;
231 mtlRec.primary_quantity := param.transactionQty;
232 mtlRec.negative_req_flag := 1;
233 if ( param.transactionType = WIP_CONSTANTS.ISSCOMP_TYPE ) then
234 mtlRec.transaction_action_id := WIP_CONSTANTS.ISSCOMP_ACTION;
235 mtlRec.transaction_quantity := param.transactionQty * -1;
236 mtlRec.primary_quantity := param.transactionQty * -1;
237 elsif ( param.transactionType = WIP_CONSTANTS.RETCOMP_TYPE ) then
238 mtlRec.transaction_action_id := WIP_CONSTANTS.RETCOMP_ACTION;
239 elsif ( param.transactionType = WIP_CONSTANTS.ISSNEGC_TYPE ) then
240 mtlRec.transaction_action_id := WIP_CONSTANTS.ISSNEGC_ACTION;
241 mtlRec.negative_req_flag := -1;
242 elsif ( param.transactionType = WIP_CONSTANTS.RETNEGC_TYPE ) then
243 mtlRec.transaction_action_id := WIP_CONSTANTS.RETNEGC_ACTION;
244 mtlRec.transaction_quantity := param.transactionQty * -1;
245 mtlRec.primary_quantity := param.transactionQty * -1;
246 mtlRec.negative_req_flag := -1;
247 end if;
248 mtlRec.transaction_type_id := param.transactionType;
249 mtlRec.wip_entity_type := WIP_CONSTANTS.DISCRETE;
250
251 mtlRec.locator_id := param.locatorID;
252 mtlRec.operation_seq_num := param.opSeqNum;
253 mtlRec.department_id := param.deptID;
254 -- mtlRec.row.item_trx_enabled_flag := item.mtlTxnsEnabled;
255 -- mtlRec.row.item_description := item.description;
256 -- mtlRec.row.item_location_control_code := item.locationControlCode;
257 -- mtlRec.row.item_restrict_subinv_code := item.restrictSubinvCode;
258 -- mtlRec.row.item_restrict_locators_code := item.restrictLocatorsCode;
259 -- mtlRec.row.item_revision_qty_control_code := item.revQtyControlCode;
260 mtlRec.revision := param.revision;
261 -- mtlRec.item_primary_uom_code := item.primaryUOMCode;
262 mtlRec.transaction_uom := param.transactionUOM;
263 -- mtlRec.row.item_inventory_asset_flag := item.invAssetFlag;
264 -- mtlRec.row.allowed_units_lookup_code := item.allowedUnitsLookupCode;
265 -- mtlRec.row.item_shelf_life_code := item.shelfLifeCode;
266 -- mtlRec.row.item_shelf_life_days := item.shelfLifeDays;
267 -- mtlRec.row.item_serial_control_code := item.serialNumberControlCode;
268 -- mtlRec.row.item_lot_control_code := item.lotControlCode;
269 -- mtlRec.row.posting_flag := 'Y';
270
271 mtlRec.process_flag := wip_constants.mti_inventory;
272
273 mtlRec.project_id := param.projectID;
274 mtlRec.task_id := param.taskID;
275 mtlRec.source_project_id := job.projectID;
276 mtlRec.source_task_id := job.taskID;
277 mtlRec.qa_collection_id := param.qualityID;
278 mtlRec.wip_entity_type := param.wipEntityType;
279 if (param.wipEntityType = WIP_CONSTANTS.EAM) then
280 mtlRec.rebuild_item_id := param.itemID;
281 else
282 mtlRec.rebuild_item_id := NULL;
283 end if;
284 return true;
285 End derive;
286
287
288 /**
289 * This function is used to insert the record encapsulated in mtlRec to
290 * table MMTT and some furthur validation and processing.
291 *
292 * HISTORY:
293 * 30-DEC-2004 spondalu Bug 4093569: eAM-WMS Integration enhancements:
294 * Insert rebuild_item_id into mti.
295 */
296 Function put(mtlRec MtlRecord, errMsg OUT NOCOPY VARCHAR2) return boolean IS
297 l_retStatus VARCHAR2(1);
298 l_logLevel NUMBER;
299 Begin
300 l_logLevel := fnd_log.g_current_runtime_level;
301
302 INSERT INTO mtl_transactions_interface
303 (wip_supply_type,
304 final_completion_flag,
305 transaction_header_id,
306 transaction_interface_id,
307 transaction_mode,
308 -- lock_flag,
309 inventory_item_id,
310 subinventory_code,
311 primary_quantity,
312 transaction_quantity,
313 transaction_date,--10
314 organization_id,
315 acct_period_id,
316 last_update_date,
317 last_updated_by,
318 creation_date,
319 created_by,
320 transaction_source_id,
321 transaction_source_type_id,
322 transaction_type_id,
323 transaction_action_id,--20
324 wip_entity_type,
325 locator_id,
326 operation_seq_num,
327 department_id,
328 -- item_trx_enabled_flag,
329 -- item_description,
330 -- item_location_control_code,
331 -- item_restrict_subinv_code,
332 -- item_restrict_locators_code,
333 -- item_revision_qty_control_code, --30
334 revision,
335 -- item_primary_uom_code,
336 transaction_uom,
337 -- item_inventory_asset_flag,
338 -- allowed_units_lookup_code,
339 -- item_shelf_life_code,
340 -- item_shelf_life_days,
341 -- item_serial_control_code,
342 -- item_lot_control_code,
343 negative_req_flag,
344 -- posting_flag,
345 process_flag,
346 project_id,
347 task_id,
348 source_project_id,
349 source_task_id,
350 qa_collection_id,
351 source_code,
352 source_line_id,
353 source_header_id,
354 rebuild_item_id)
355 VALUES
356 (wip_constants.push, --always a push item
357 mtlRec.final_completion_flag,
358 mtlRec.transaction_header_id,
359 mtlRec.transaction_interface_id,
360 mtlRec.transaction_mode,
361 -- mtlRec.lock_flag,
362 mtlRec.inventory_item_id,
363 mtlRec.subinventory_code,
364 mtlRec.primary_quantity,
365 mtlRec.transaction_quantity,
366 mtlRec.transaction_date,--10
367 mtlRec.organization_id,
368 mtlRec.acct_period_id,
369 mtlRec.last_update_date,
370 mtlRec.last_updated_by,
371 mtlRec.creation_date,
372 mtlRec.created_by,
373 mtlRec.transaction_source_id,
374 mtlRec.transaction_source_type_id,
375 mtlRec.transaction_type_id,
376 mtlRec.transaction_action_id,--20
377 mtlRec.wip_entity_type,
378 mtlRec.locator_id,
379 mtlRec.operation_seq_num,
380 mtlRec.department_id,
381 -- mtlRec.item_trx_enabled_flag,
382 -- mtlRec.item_description,
383 -- mtlRec.item_location_control_code,
384 -- mtlRec.item_restrict_subinv_code,
385 -- mtlRec.item_restrict_locators_code,
386 -- mtlRec.item_revision_qty_control_code,
387 mtlRec.revision,
388 -- mtlRec.item_primary_uom_code,
389 mtlRec.transaction_uom,
390 -- mtlRec.item_inventory_asset_flag,
391 -- mtlRec.allowed_units_lookup_code,
392 -- mtlRec.item_shelf_life_code,
393 -- mtlRec.item_shelf_life_days,
394 -- mtlRec.item_serial_control_code,
395 -- mtlRec.item_lot_control_code,
396 mtlRec.negative_req_flag,
397 -- mtlRec.posting_flag,
398 mtlRec.process_flag,
399 mtlRec.project_id,
400 mtlRec.task_id,
401 mtlRec.source_project_id,
402 mtlRec.source_task_id,
403 mtlRec.qa_collection_id,
404 mtlRec.source_code,
405 mtlRec.source_line_id,
406 mtlRec.source_header_id,
407 mtlRec.rebuild_item_id);
408
409 wip_mtlTempProc_priv.validateInterfaceTxns(p_txnHdrID => mtlRec.transaction_header_id,
410 p_initMsgList => fnd_api.g_true,
411 x_returnStatus => l_retStatus);
412
413 if(l_retStatus <> fnd_api.g_ret_sts_success) then
414 if (l_logLevel <= wip_constants.full_logging) then
415 wip_logger.log('put: error from validateInterfaceTxns', l_retStatus);
416 end if;
417 wip_utilities.get_message_stack(p_msg => errMsg);
418 return false;
419 end if;
420
421 return true;
422
423 EXCEPTION
424 when others then
425 fnd_message.set_name ('WIP', 'GENERIC_ERROR');
426 fnd_message.set_token ('FUNCTION', 'wma_material.derive');
427 fnd_message.set_token ('ERROR', SQLERRM);
428 errMsg := fnd_message.get;
429 return false;
430 End put;
431
432 /**
433 * This is a private function used by this pacakge only. It checks the qty entered
434 * for material txn to make sure the qty > 0.
435 */
436 Function checkQty(txnType NUMBER,
437 txnQty NUMBER,
438 errMessage OUT NOCOPY VARCHAR2) RETURN boolean IS
439 Begin
440 if ( txnQty <= 0 ) then
441 fnd_message.set_name('INV', 'INV_GREATER_THAN_ZERO');
442 errMessage := fnd_message.get;
443 return false;
444 end if;
445
446 return true;
447 End checkQty;
448
449 procedure validateIssueProject(p_orgID in number,
450 p_wipEntityID in number,
451 p_locatorID in number,
452 p_allowCrossIssue in number,
453 x_projectID out nocopy number,
454 x_taskID out nocopy number,
455 x_projectNum out nocopy varchar2,
456 x_taskNum out nocopy varchar2,
457 x_returnStatus out nocopy varchar2,
458 x_returnMsg out nocopy varchar2) is
459 l_jobProjectID number;
460 l_jobTaskID number;
461 l_locProjectID number;
462 l_locTaskID number;
463 l_num number;
464 begin
465 x_returnStatus := fnd_api.g_ret_sts_success;
466 x_projectID := null;
467 x_taskID := null;
468 x_projectNum := null;
469 x_taskNum := null;
470 x_returnMsg := null;
471
472 /* Bug 5111027 - The following SQL will raise a
473 NO DATA FOUND exception for PJM organization with no locator
474 control at org/subinventory/item level. Added EXCEPTION
475 block to handle it.*/
476 BEGIN
477 select project_id, task_id
478 into l_locProjectID, l_locTaskID
479 from mtl_item_locations_kfv
480 where organization_id = p_orgID
481 and inventory_location_id = p_locatorID;
482 EXCEPTION
483 when NO_DATA_FOUND THEN
484 RETURN;
485 END;
486
487
488 -- no project/task reference on the locator
489 if ( l_locProjectID is null and l_locTaskID is null ) then
490 return;
491 end if;
492
493 x_projectID := l_locProjectID;
494 x_taskID := l_locTaskID;
495 x_projectNum := pjm_project.all_proj_idtonum(l_locProjectID);
496 x_taskNum := pjm_project.all_task_idtonum(l_locTaskID);
497
498 select project_id, task_id
499 into l_jobProjectID, l_jobTaskID
500 from wip_discrete_jobs
501 where organization_id = p_orgID
502 and wip_entity_id = p_wipEntityID;
503
504 -- you can't issue project controlled item to non project controlled job
505 if ( l_jobProjectID is null ) then
506 x_returnStatus := fnd_api.g_ret_sts_error;
507 x_projectID := null;
508 x_taskID := null;
509 x_projectNum := null;
510 x_taskNum := null;
511 fnd_message.set_name('WIP', 'JOB_NOT_PROJ_CNTL');
512 x_returnMsg := fnd_message.get;
513 return;
514 end if;
515
516
517 -- project/task are the same with job project/task
518 if ( (l_locProjectID = l_jobProjectID) and (l_locTaskID = l_jobTaskID) ) then
519 return;
520 end if;
521
522 -- project is the same and all cross project issue
523 if ( (l_locProjectID = l_jobProjectID) and (p_allowCrossIssue = 1) ) then
524 return;
525 end if;
526
527
528 -- diff project with the same cost group and planning group when
529 -- allow cross issue
530 if ( l_locProjectID <> l_jobProjectID and p_allowCrossIssue = 1 ) then
531 select count(*)
532 into l_num
533 from pjm_project_parameters pp,
534 pjm_project_parameters jpp
535 where pp.organization_id = p_orgID
536 and pp.project_id = l_locProjectID
537 and jpp.organization_id = p_orgID
538 and jpp.project_id = l_jobProjectID
539 and pp.costing_group_id = jpp.costing_group_id
540 and pp.planning_group = jpp.planning_group;
541 if ( l_num > 0 ) then
542 return;
543 end if;
544 end if;
545
546 x_returnStatus := fnd_api.g_ret_sts_error;
547 x_projectID := null;
548 x_taskID := null;
549 x_projectNum := null;
550 x_taskNum := null;
551 fnd_message.set_name('WIP', 'INVALID_SER_PROJECT_TASK');
552 x_returnMsg := fnd_message.get;
553 end validateIssueProject;
554
555
556 END wma_material;