[Home] [Help]
PACKAGE BODY: APPS.INV_PROCESS_TEMP
Source
1 PACKAGE BODY INV_PROCESS_TEMP AS
2 /* $Header: INVMMTTB.pls 120.1 2005/12/09 16:56:04 kdong noship $ */
3
4 -- presently has only TIMEBASED validation
5 -- eventually we will provide the complete validation
6 FUNCTION processTransaction(headerID IN NUMBER,
7 validationLevel IN NUMBER,
8 errorTolerance IN NUMBER) RETURN NUMBER
9 IS
10 status NUMBER := 1;
11 hasErrors BOOLEAN := FALSE;
12 l_txnrecs TXNRECS;
13 l_txnrecord TXNREC;
14 l_org INV_Validate.ORG;
15 l_item INV_Validate.ITEM;
16 l_openAcctPeriod NUMBER;
17
18 l_row_count NUMBER;
19 l_errorTolerance NUMBER;
20 BEGIN
21 -- get the user information and populate
22 -- this info is used to stamp the transaction records incase of errors
23 userid := fnd_global.user_id;
24 loginid := fnd_global.login_id;
25 applid := fnd_global.prog_appl_id;
26 reqstid := fnd_global.conc_request_id;
27 progid := fnd_global.conc_program_id;
28 INV_PROCESS_TEMP.validationLevel := validationLevel;
29
30 header_id := headerID;
31 l_errorTolerance := errorTolerance;
32 -- Bug 2574288 added rownum < 2 to the where clause
33
34 if(l_errorTolerance <> IGNORE_NONE) then
35 select count(1)
36 into l_row_count
37 from mtl_material_transactions_temp
38 where transaction_header_id = header_id
39 and process_flag = 'Y'
40 and transaction_status = TS_PROCESS
41 and rownum < 2;
42 if(l_row_count = 1) then l_errorTolerance := IGNORE_NONE; end if;
43 end if;
44
45 status := validateSupportedTxns(validationLevel);
46 if(not hasErrors and status <> 1)
47 then
48 hasErrors := TRUE;
49 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
50 end if;
51
52 status := validateFromOrganization;
53 if(not hasErrors and status <> 1)
54 then
55 hasErrors := TRUE;
56 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
57 end if;
58
59 status := validateToOrganization;
60 if(not hasErrors and status <> 1)
61 then
62 hasErrors := TRUE;
63 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
64 end if;
65
66 status := validateItem;
67 if(not hasErrors and status <> 1)
68 then
69 hasErrors := TRUE;
70 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
71 end if;
72
73 status := validateFromSubinventory;
74 if(not hasErrors and status <> 1)
75 then
76 hasErrors := TRUE;
77 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
78 end if;
79
80 status := validateFromLocator;
81 if(not hasErrors and status <> 1)
82 then
83 hasErrors := TRUE;
84 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
85 end if;
86
87 status := validateToSubinventory;
88 if(not hasErrors and status <> 1)
89 then
90 hasErrors := TRUE;
91 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
92 end if;
93
94 status := validateToLocator;
95 if(not hasErrors and status <> 1)
96 then
97 hasErrors := TRUE;
98 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
99 end if;
100
101 status := validateTxnUOM;
102 if(not hasErrors and status <> 1)
103 then
104 hasErrors := TRUE;
105 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
106 end if;
107
108 status := validateTransactionSource;
109 if(not hasErrors and status <> 1)
110 then
111 hasErrors := TRUE;
112 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
113 end if;
114
115 status := validateSourceProject;
116 if(not hasErrors and status <> 1)
117 then
118 hasErrors := TRUE;
119 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
120 end if;
121
122 status := validateSourceTask;
123 if(not hasErrors and status <> 1)
124 then
125 hasErrors := TRUE;
126 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
127 end if;
128
129 status := validateCostGroups;
130 if(not hasErrors and status <> 1)
131 then
132 hasErrors := TRUE;
133 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
134 end if;
135
136 status := validateExpenditureType;
137 if(not hasErrors and status <> 1)
138 then
139 hasErrors := TRUE;
140 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
141 end if;
142
143 status := validateExpenditureOrg;
144 if(not hasErrors and status <> 1)
145 then
146 hasErrors := TRUE;
147 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
148 end if;
149
150 status := validateToOrgItem;
151 if(not hasErrors and status <> 1)
152 then
153 hasErrors := TRUE;
154 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
155 end if;
156
157 status := validateToOrgItemRevision;
158 if(not hasErrors and status <> 1)
159 then
160 hasErrors := TRUE;
161 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
162 end if;
163
164 status := validateInterOrgItemControls;
165 if(not hasErrors and status <> 1)
166 then
167 hasErrors := TRUE;
168 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
169 end if;
170
171 status := validateFreightInfo;
172 if(not hasErrors and status <> 1)
173 then
174 hasErrors := TRUE;
175 if(l_errorTolerance = IGNORE_NONE) then return 0; end if;
176 end if;
177
178 -- Bug 4200332
179 -- Round transaction/primary quantities to 5 decimals
180
181 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
182 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
183 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
184 WHERE TRANSACTION_HEADER_ID = header_id
185 AND PROCESS_FLAG = 'Y'
186 AND TRANSACTION_STATUS = 3;
187
188 -- validation for individual records
189 OPEN l_TXNRECS FOR
190 SELECT MMTT.*,ROWID
191 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
192 WHERE TRANSACTION_HEADER_ID = headerID
193 AND PROCESS_FLAG='Y'
194 AND TRANSACTION_STATUS = TS_PROCESS
195 ORDER BY ORGANIZATION_ID,INVENTORY_ITEM_ID,REVISION,
196 SUBINVENTORY_CODE,LOCATOR_ID;
197 LOOP
198 FETCH l_txnrecs INTO l_txnrecord;
199 EXIT WHEN l_TXNRECS%NOTFOUND;
200 -- get key entity objects
201 l_org.organization_id := l_txnrecord.organization_id;
202 status := INV_Validate.Organization(l_org);
203
204 l_item.organization_id := l_txnrecord.organization_id;
205 l_item.inventory_item_id := l_txnrecord.inventory_item_id;
206 status := INV_Validate.Inventory_Item(l_item,l_org);
207
208 status := validateLOT(l_txnrecord,l_org,l_item);
209 status := validateUnitNumber(l_txnrecord);
210
211 l_openAcctPeriod := getAccountPeriodId(l_txnrecord.organization_id,
212 l_txnrecord.transaction_date);
213 if(l_openAcctPeriod <= 0) then
214 loadmsg('INV_INT_PRDCODE','INV_INT_PRDCODE');
215 if(l_openAcctPeriod = 0) then
216 loadmsg('INV_INT_PRDCODE','INV_NO_OPEN_PERIOD');
217 end if;
218 errupdate(l_txnrecord.rowid);
219 end if;
220
221 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
222 SET LAST_UPDATE_DATE = SYSDATE,
223 LAST_UPDATED_BY = userid,
224 LAST_UPDATE_LOGIN = loginid,
225 PROGRAM_UPDATE_DATE = SYSDATE,
226 ACCT_PERIOD_ID = l_openAcctPeriod
227 WHERE ROWID = l_txnrecord.rowid;
228 END LOOP;
229
230 if hasErrors then return 0; else return 1; end if;
231 END processTransaction;
232
233 /* validates a transaction against supported list of transactions
234 based on the validation Level. Presently supported transactions are
235
236 TIME BASED:
237 TXN SRC TXN TYPE TXN ACTION
238 13 2 2
239 2 52 28
240 8 53 28
241 4 64 2
242 4 63 1
243 FULL eventually everything should be supported for this.
244 TXN SRC TXN TYPE TXN ACTION
245 */
246 FUNCTION validateSupportedTxns(validationLevel IN NUMBER) RETURN NUMBER
247 IS
248 BEGIN
249 loadmsg('INV_TXN_NOT_SUPPORTED','INV_TXN_NOT_SUPPORTED_VLEVEL');
250 if(validationLevel = INV_PROCESS_TEMP.TIMEBASED)
251 then
252 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
253 SET LAST_UPDATE_DATE = SYSDATE,
254 LAST_UPDATED_BY = userid,
255 LAST_UPDATE_LOGIN = loginid,
256 PROGRAM_UPDATE_DATE = SYSDATE,
257 PROCESS_FLAG = 'E',
258 TRANSACTION_STATUS = 1,
259 LOCK_FLAG = 'N',
260 ERROR_CODE = substr(err_code,1,240),
261 ERROR_EXPLANATION = substr(error_exp,1,240)
262 WHERE TRANSACTION_HEADER_ID = header_id
263 AND PROCESS_FLAG = 'Y'
264 AND TRANSACTION_STATUS = 3
265 AND (NOT (TRANSACTION_SOURCE_TYPE_ID IN (13, 2, 4)
266 AND TRANSACTION_ACTION_ID IN (2, 28, 1)
267 AND TRANSACTION_TYPE_ID not in (33)));
268 end if;
269 if(SQL%FOUND) then return 0; else return 1; end if;
270 END validateSupportedTxns;
271
272 /* validates from organization */
273 FUNCTION validateFromOrganization RETURN NUMBER
274 IS
275 BEGIN
276 loadmsg('INV_INT_ORGCODE','INV_INT_ORGEXP');
277
278 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
279 SET LAST_UPDATE_DATE = SYSDATE,
280 LAST_UPDATED_BY = userid,
281 LAST_UPDATE_LOGIN = loginid,
282 PROGRAM_UPDATE_DATE = SYSDATE,
283 PROCESS_FLAG = 'E',
284 TRANSACTION_STATUS = 1,
285 LOCK_FLAG = 'N',
286 ERROR_CODE = substr(err_code,1,240),
287 ERROR_EXPLANATION = substr(error_exp,1,240)
288 WHERE TRANSACTION_HEADER_ID = header_id
289 AND PROCESS_FLAG = 'Y'
290 AND TRANSACTION_STATUS = 3
291 AND NOT EXISTS (
292 SELECT NULL
293 FROM ORG_ORGANIZATION_DEFINITIONS OOD
294 WHERE OOD.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
295 AND NVL(OOD.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
296 if(SQL%FOUND) then return 0; else return 1; end if;
297 END validateFromOrganization;
298
299 /* validates to organization */
300 FUNCTION validateToOrganization RETURN NUMBER
301 IS
302 BEGIN
303 loadmsg('INV_INT_XORGCODE','INV_INT_XORGEXP');
304 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
305 SET LAST_UPDATE_DATE = SYSDATE,
306 LAST_UPDATED_BY = userid,
307 LAST_UPDATE_LOGIN = loginid,
308 PROGRAM_UPDATE_DATE = SYSDATE,
309 PROCESS_FLAG = 'E',
310 TRANSACTION_STATUS = 1,
311 LOCK_FLAG = 'N',
312 ERROR_CODE = substr(err_code,1,240),
313 ERROR_EXPLANATION = substr(error_exp,1,240)
314 WHERE TRANSACTION_HEADER_ID = header_id
315 AND TRANSACTION_ACTION_ID in (3,21)
316 AND PROCESS_FLAG = 'Y'
317 AND TRANSACTION_STATUS = 3
318 AND (NOT EXISTS (
319 SELECT NULL
320 FROM ORG_ORGANIZATION_DEFINITIONS OOD
321 WHERE OOD.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
322 AND NVL(OOD.DISABLE_DATE, SYSDATE + 1) > SYSDATE)
323 OR NOT EXISTS (
324 SELECT NULL
325 FROM MTL_INTERORG_PARAMETERS MIP
326 WHERE MIP.TO_ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
327 AND MIP.FROM_ORGANIZATION_ID = MMTT.ORGANIZATION_ID));
328 if(SQL%FOUND) then return 0; else return 1; end if;
329 END validateToOrganization;
330
331 /* validates item */
332 FUNCTION validateItem RETURN NUMBER
333 IS
334 l_status NUMBER;
335 BEGIN
336 if validationLevel = TIMEBASED then return 1; end if;
337
338 loadmsg('INV_INT_ITMCODE','INV_INT_ITMEXP');
339 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
340 SET LAST_UPDATE_DATE = SYSDATE,
341 LAST_UPDATED_BY = userid,
342 LAST_UPDATE_LOGIN = loginid,
343 PROGRAM_UPDATE_DATE = SYSDATE,
344 PROCESS_FLAG = 'E',
345 TRANSACTION_STATUS = 1,
346 LOCK_FLAG = 'N',
347 ERROR_CODE = substr(err_code,1,240),
348 ERROR_EXPLANATION = substr(error_exp,1,240)
349 WHERE TRANSACTION_HEADER_ID = header_id
350 AND PROCESS_FLAG = 'Y'
351 AND TRANSACTION_STATUS = 3
352 AND ((INVENTORY_ITEM_ID IS NOT NULL
353 AND (TRANSACTION_ACTION_ID NOT IN (1, 27, 33, 34)
354 OR TRANSACTION_SOURCE_TYPE_ID <> 5)) OR
355 (TRANSACTION_ACTION_ID <> 24
356 AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'))
357 AND NOT EXISTS (
358 SELECT NULL
359 FROM MTL_SYSTEM_ITEMS MSI
360 WHERE MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
361 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
362 AND MSI.INVENTORY_ITEM_FLAG = 'Y');
363 if(SQL%FOUND) then l_status := 0; else l_status := 1; end if;
364
365 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
366 SET LAST_UPDATE_DATE = SYSDATE,
367 LAST_UPDATED_BY = userid,
368 LAST_UPDATE_LOGIN = loginid,
369 PROGRAM_UPDATE_DATE = SYSDATE,
370 PROCESS_FLAG = 'E',
371 LOCK_FLAG = 'N',
372 ERROR_CODE = substrb(err_code,1,240),
373 ERROR_EXPLANATION = substrb(error_exp,1,240)
374 WHERE TRANSACTION_HEADER_ID = header_id
375 AND PROCESS_FLAG = 'Y'
376 AND TRANSACTION_STATUS = 3
377 AND TRANSACTION_ACTION_ID = 24
378 AND NOT EXISTS (
379 SELECT NULL
380 FROM MTL_SYSTEM_ITEMS MSI
381 WHERE MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
382 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
383 AND MSI.INVENTORY_ITEM_FLAG = 'Y'
384 AND MSI.INVENTORY_ASSET_FLAG = 'Y'
385 AND MSI.COSTING_ENABLED_FLAG = 'Y');
386 if SQL%FOUND then l_status := 0; end if;
387 return l_status;
388 END validateItem;
389
390 /* validates item's revision */
391 FUNCTION validateItemRevision RETURN NUMBER
392 IS
393 BEGIN
394 if validationLevel = TIMEBASED then return 1; end if;
395 loadmsg('INV_INT_REVCODE','INV_INT_REVEXP');
396 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
397 SET LAST_UPDATE_DATE = SYSDATE,
398 LAST_UPDATED_BY = userid,
399 LAST_UPDATE_LOGIN = loginid,
400 PROGRAM_UPDATE_DATE = SYSDATE,
401 PROCESS_FLAG = 3,
402 LOCK_FLAG = 'N',
403 ERROR_CODE = substrb(err_code,1,240),
404 ERROR_EXPLANATION = substrb(error_exp,1,240)
405 WHERE TRANSACTION_HEADER_ID = header_id
406 AND PROCESS_FLAG = 1
407 AND TRANSACTION_ACTION_ID NOT IN (24,33,34)
408 AND NOT EXISTS (
409 SELECT NULL
410 FROM MTL_ITEM_REVISIONS MIR,
411 MTL_SYSTEM_ITEMS MSI
412 WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
413 AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
414 AND MIR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
415 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
416 AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
417 AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
418 AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
419 AND MIR.REVISION = MMTT.REVISION
420 UNION
421 SELECT NULL
422 FROM MTL_SYSTEM_ITEMS ITM
423 WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
424 AND MMTT.REVISION IS NULL
425 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
426 AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID);
427 if SQL%FOUND then return 0; else return 1; end if;
428 END validateItemRevision;
429
430 /* validates item in to org context */
431 FUNCTION validateToOrgItem RETURN NUMBER
432 IS
433 BEGIN
434 -- Bug 3951494
435 -- The validation should happen for the MMTT record not the MTI record.
436 -- Changing the below sql validation against MMTT.
437 if validationLevel = TIMEBASED then return 1; end if;
438 loadmsg('INV_INT_ITEMCODE','INV_INT_XFRITEMEXP');
439 /* UPDATE MTL_TRANSACTIONS_INTERFACE MTI
440 SET LAST_UPDATE_DATE = SYSDATE,
441 LAST_UPDATED_BY = userid,
442 LAST_UPDATE_LOGIN = loginid,
443 PROGRAM_UPDATE_DATE = SYSDATE,
444 PROCESS_FLAG = 3,
445 LOCK_FLAG = 2,
446 ERROR_CODE = substrb(err_code,1,240),
447 ERROR_EXPLANATION = substrb(error_exp,1,240)
448 WHERE TRANSACTION_HEADER_ID = header_id
449 AND TRANSACTION_ACTION_ID = 3
450 AND PROCESS_FLAG = 1
451 AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'
452 AND NOT EXISTS (
453 SELECT NULL
454 FROM MTL_SYSTEM_ITEMS MSI
455 WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
456 AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
457 AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'); */
458
459 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
460 SET LAST_UPDATE_DATE = SYSDATE,
461 LAST_UPDATED_BY = userid,
462 LAST_UPDATE_LOGIN = loginid,
463 PROGRAM_UPDATE_DATE = SYSDATE,
464 PROCESS_FLAG = 'E',
465 LOCK_FLAG = 'N',
466 ERROR_CODE = substrb(err_code,1,240),
467 ERROR_EXPLANATION = substrb(error_exp,1,240)
468 WHERE TRANSACTION_HEADER_ID = header_id
469 AND TRANSACTION_ACTION_ID = 3
470 AND PROCESS_FLAG = 'Y'
471 AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'
472 AND NOT EXISTS (
473 SELECT NULL
474 FROM MTL_SYSTEM_ITEMS MSI
475 WHERE MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
476 AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
477 AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y');
478 if SQL%FOUND then return 0; else return 1; end if;
479 END validateToOrgItem;
480
481 /* validates item's revision in to org context */
482 FUNCTION validateToOrgItemRevision RETURN NUMBER
483 IS
484 BEGIN
485 -- Bug 3951494
486 -- The validation should happen for the MMTT record not the MTI record.
487 -- Changing the below sql validation against MMTT.
488 if validationLevel = TIMEBASED then return 1; end if;
489 loadmsg('INV_INT_REVCODE','INV_INT_REVXFREXP');
490 /* UPDATE MTL_TRANSACTIONS_INTERFACE MTI
491 SET LAST_UPDATE_DATE = SYSDATE,
492 LAST_UPDATED_BY = userid,
493 LAST_UPDATE_LOGIN = loginid,
494 PROGRAM_UPDATE_DATE = SYSDATE,
495 PROCESS_FLAG = 3,
496 LOCK_FLAG = 2,
497 ERROR_CODE = substrb(err_code,1,240),
498 ERROR_EXPLANATION = substrb(error_exp,1,240)
499 WHERE TRANSACTION_HEADER_ID = header_id
500 AND PROCESS_FLAG = 1
501 AND TRANSACTION_ACTION_ID = 3
502 AND NOT EXISTS (
503 SELECT NULL
504 FROM MTL_ITEM_REVISIONS MIR,
505 MTL_SYSTEM_ITEMS MSI
506 WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
507 AND MIR.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
508 AND MIR.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
509 AND MIR.REVISION = MTI.REVISION
510 AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
511 AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
512 AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
513 AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
514 UNION
515 SELECT NULL
516 FROM MTL_SYSTEM_ITEMS ITM
517 WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
518 AND MTI.REVISION IS NULL
519 AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
520 AND ITM.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION); */
521
522 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
523 SET LAST_UPDATE_DATE = SYSDATE,
524 LAST_UPDATED_BY = userid,
525 LAST_UPDATE_LOGIN = loginid,
526 PROGRAM_UPDATE_DATE = SYSDATE,
527 PROCESS_FLAG = 'E',
528 LOCK_FLAG = 'N',
529 ERROR_CODE = substrb(err_code,1,240),
530 ERROR_EXPLANATION = substrb(error_exp,1,240)
531 WHERE TRANSACTION_HEADER_ID = header_id
532 AND PROCESS_FLAG = 'Y'
533 AND TRANSACTION_ACTION_ID = 3
534 AND NOT EXISTS (
535 SELECT NULL
536 FROM MTL_ITEM_REVISIONS MIR,
537 MTL_SYSTEM_ITEMS MSI
538 WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
539 AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
540 AND MIR.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
541 AND MIR.REVISION = MMTT.REVISION
542 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
543 AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
544 AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
545 AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
546 UNION
547 SELECT NULL
548 FROM MTL_SYSTEM_ITEMS ITM
549 WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
550 -- AND MTI.REVISION IS NULL not required as per bug 3285134
551 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
552 AND ITM.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION);
553 if SQL%FOUND then return 0; else return 1; end if;
554 END validateToOrgItemRevision;
555
556 /* validates subinventory code */
557 FUNCTION validateFromSubinventory RETURN NUMBER
558 IS
559 l_status NUMBER;
560 BEGIN
561 loadmsg('INV_INT_SUBCODE','INV_INT_SUBEXP');
562
563 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
564 SET LAST_UPDATE_DATE = SYSDATE,
565 LAST_UPDATED_BY = userid,
566 LAST_UPDATE_LOGIN = loginid,
567 PROGRAM_UPDATE_DATE = SYSDATE,
568 PROCESS_FLAG = 'E',
569 TRANSACTION_STATUS = 1,
570 LOCK_FLAG = 'N',
571 ERROR_CODE = substr(err_code,1,240),
572 ERROR_EXPLANATION = substr(error_exp,1,240)
573 WHERE TRANSACTION_HEADER_ID = header_id
574 AND PROCESS_FLAG = 'Y'
575 AND TRANSACTION_STATUS = 3
576 AND TRANSACTION_ACTION_ID NOT IN (24, 30) /* CFM Scrap Transactions */
577 AND (NVL(SHIPPABLE_FLAG,'Y') = 'Y'
578 AND NOT EXISTS (
579 SELECT NULL
580 FROM MTL_SECONDARY_INVENTORIES MSI
581 WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
582 AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
583 AND NVL(DISABLE_DATE,SYSDATE+1) >= SYSDATE)
584 OR (SHIPPABLE_FLAG = 'N'
585 AND SUBINVENTORY_CODE IS NOT NULL
586 AND NOT EXISTS (
587 SELECT NULL
588 FROM MTL_SECONDARY_INVENTORIES MSI
589 WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
590 AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE)));
591 -- should we use disable state here?
592
593 if(SQL%FOUND) then l_status := 0; else l_status := 1; end if;
594
595 if validationLevel = TIMEBASED then return l_status; end if;
596
597 loadmsg('INV_INT_SUBCODE','INV_INT_RESUBEXP');
598 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
599 SET LAST_UPDATE_DATE = SYSDATE,
600 LAST_UPDATED_BY = userid,
601 LAST_UPDATE_LOGIN = loginid,
602 PROGRAM_UPDATE_DATE = SYSDATE,
603 PROCESS_FLAG = 'E',
604 LOCK_FLAG = 'N',
605 ERROR_CODE = substrb(err_code,1,240),
606 ERROR_EXPLANATION = substrb(error_exp,1,240)
607 WHERE TRANSACTION_HEADER_ID = header_id
608 AND SUBINVENTORY_CODE IS NOT NULL
609 AND PROCESS_FLAG = 'Y'
610 AND TRANSACTION_STATUS = 3
611 AND NOT EXISTS (
612 SELECT NULL
613 FROM MTL_ITEM_SUB_INVENTORIES MIS,
614 MTL_SYSTEM_ITEMS MSI
615 WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
616 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
617 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
618 AND MIS.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
619 AND MIS.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
620 AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
621 AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
622 AND MIS.SECONDARY_INVENTORY = MMTT.SUBINVENTORY_CODE
623 UNION
624 SELECT NULL
625 FROM MTL_SYSTEM_ITEMS ITM
626 WHERE ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
627 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
628 AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
629 if SQL%FOUND then l_status := 0; end if;
630 return l_status;
631 END validateFromSubinventory;
632
633 /* validates from locator */
634 FUNCTION validateFromLocator RETURN NUMBER
635 IS
636 l_status NUMBER;
637 BEGIN
638 loadmsg('INV_INT_LOCCODE','INV_INT_LOCEXP');
639 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
640 SET LAST_UPDATE_DATE = SYSDATE,
641 LAST_UPDATED_BY = userid,
642 LAST_UPDATE_LOGIN = loginid,
643 PROGRAM_UPDATE_DATE = SYSDATE,
644 PROCESS_FLAG = 'E',
645 LOCK_FLAG = 'N',
646 ERROR_CODE = substrb(err_code,1,240),
647 ERROR_EXPLANATION = substrb(error_exp,1,240)
648 WHERE TRANSACTION_HEADER_ID = header_id
649 AND PROCESS_FLAG = 'Y'
650 AND TRANSACTION_STATUS = 3
651 AND LOCATOR_ID IS NOT NULL
652 AND NOT EXISTS (
653 SELECT NULL
654 FROM MTL_ITEM_LOCATIONS MIL
655 WHERE MIL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
656 AND MIL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
657 AND MIL.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID
658 AND NVL(DISABLE_DATE,SYSDATE+1) >= SYSDATE);
659 if SQL%FOUND then l_status := 0; else l_status := 1; end if;
660 if validationLevel = TIMEBASED then return l_status; end if;
661
662 loadmsg('INV_INT_LOCCODE','INV_INT_RESLOCEXP');
663 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
664 SET LAST_UPDATE_DATE = SYSDATE,
665 LAST_UPDATED_BY = userid,
666 LAST_UPDATE_LOGIN = loginid,
667 PROGRAM_UPDATE_DATE = SYSDATE,
668 PROCESS_FLAG = 'E',
669 LOCK_FLAG = 'N',
670 ERROR_CODE = substrb(err_code,1,240),
671 ERROR_EXPLANATION = substrb(error_exp,1,240)
672 WHERE TRANSACTION_HEADER_ID = header_id
673 AND PROCESS_FLAG = 'Y'
674 AND TRANSACTION_STATUS = 3
675 AND LOCATOR_ID IS NOT NULL
676 AND NOT EXISTS (
677 SELECT NULL
678 FROM MTL_SECONDARY_LOCATORS MSL,
679 MTL_SYSTEM_ITEMS MSI
680 WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
681 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
682 AND MSI.RESTRICT_LOCATORS_CODE = 1
683 AND MSL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
684 AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
685 AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
686 AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
687 AND MSL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
688 AND MSL.SECONDARY_LOCATOR = MMTT.LOCATOR_ID
689 UNION
690 SELECT NULL
691 FROM MTL_SYSTEM_ITEMS ITM
692 WHERE ITM.RESTRICT_LOCATORS_CODE = 2
693 AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
694 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID);
695 if SQL%FOUND then l_status := 0; end if;
696 return l_status;
697 END validateFromLocator;
698
699 /* validates to subinventory */
700 FUNCTION validateToSubinventory RETURN NUMBER
701 IS
702 l_status NUMBER;
703 BEGIN
704 loadmsg('INV_INT_XSUBCODE','INV_INT_XSUBEXP');
705 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
706 SET LAST_UPDATE_DATE = SYSDATE,
707 LAST_UPDATED_BY = userid,
708 LAST_UPDATE_LOGIN = loginid,
709 PROGRAM_UPDATE_DATE = SYSDATE,
710 PROCESS_FLAG = 'E',
711 TRANSACTION_STATUS = 1,
712 LOCK_FLAG = 'N',
713 ERROR_CODE = substr(err_code,1,240),
714 ERROR_EXPLANATION = substr(error_exp,1,240)
715 WHERE TRANSACTION_HEADER_ID = header_id
716 AND PROCESS_FLAG = 'Y'
717 AND TRANSACTION_STATUS = 3
718 AND (TRANSACTION_ACTION_ID IN (2,3,21)
719 AND TRANSFER_SUBINVENTORY IS NOT NULL)
720 AND ((NVL(SHIPPABLE_FLAG,'Y') = 'Y'
721 AND NOT EXISTS (
722 SELECT NULL
723 FROM MTL_SECONDARY_INVENTORIES MSI
724 WHERE MSI.ORGANIZATION_ID =
725 DECODE(MMTT.TRANSACTION_ACTION_ID,2,
726 MMTT.ORGANIZATION_ID,MMTT.TRANSFER_ORGANIZATION)
727 AND MSI.SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY
728 AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > SYSDATE)))
729 OR (SHIPPABLE_FLAG = 'N'
730 AND TRANSFER_SUBINVENTORY IS NOT NULL
731 AND NOT EXISTS (
732 SELECT NULL
733 FROM MTL_SECONDARY_INVENTORIES MSI
734 WHERE MSI.ORGANIZATION_ID =
735 DECODE(MMTT.TRANSACTION_ACTION_ID,3,
736 MMTT.TRANSFER_ORGANIZATION,21,
737 MMTT.TRANSFER_ORGANIZATION,MMTT.ORGANIZATION_ID)
738 AND MSI.SECONDARY_INVENTORY_NAME =
739 MMTT.TRANSFER_SUBINVENTORY));
740 if(SQL%FOUND) then l_status := 0; else l_status := 1; end if;
741
742 if validationLevel = TIMEBASED then return l_status; end if;
743
744 loadmsg('INV_INT_XSUBCODE','INV_INT_RESXFRSUBEXP');
745 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
746 SET LAST_UPDATE_DATE = SYSDATE,
747 LAST_UPDATED_BY = userid,
748 LAST_UPDATE_LOGIN = loginid,
749 PROGRAM_UPDATE_DATE = SYSDATE,
750 PROCESS_FLAG = 'E',
751 LOCK_FLAG = 'N',
752 ERROR_CODE = substrb(err_code,1,240),
753 ERROR_EXPLANATION = substrb(error_exp,1,240)
754 WHERE TRANSACTION_HEADER_ID = header_id
755 AND PROCESS_FLAG = 'Y'
756 AND TRANSACTION_STATUS = 3
757 AND TRANSFER_SUBINVENTORY IS NOT NULL
758 AND TRANSACTION_ACTION_ID in (2,21,3)
759 AND NOT EXISTS (
760 SELECT NULL
761 FROM MTL_ITEM_SUB_INVENTORIES MIS,
762 MTL_SYSTEM_ITEMS MSI
763 WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
764 MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
765 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
766 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
767 AND MIS.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
768 MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
769 AND MIS.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
770 AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
771 AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
772 AND MIS.SECONDARY_INVENTORY = MMTT.TRANSFER_SUBINVENTORY
773 UNION
774 SELECT NULL
775 FROM MTL_SYSTEM_ITEMS ITM
776 WHERE ITM.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
777 MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
778 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
779 AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
780 if SQL%FOUND then l_status := 0; end if;
781 return l_status;
782 END validateToSubinventory;
783
784 /* validates to locator */
785 FUNCTION validateToLocator RETURN NUMBER
786 IS
787 l_status NUMBER;
788 BEGIN
789 loadmsg('INV_INT_XLOCCODE','INV_INT_XFRLOCEXP');
790 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
791 SET LAST_UPDATE_DATE = SYSDATE,
792 LAST_UPDATED_BY = userid,
793 LAST_UPDATE_LOGIN = loginid,
794 PROGRAM_UPDATE_DATE = SYSDATE,
795 PROCESS_FLAG = 'E',
796 LOCK_FLAG = 'N',
797 ERROR_CODE = substrb(err_code,1,240),
798 ERROR_EXPLANATION = substrb(error_exp,1,240)
799 WHERE TRANSACTION_HEADER_ID = header_id
800 AND PROCESS_FLAG = 'Y'
801 AND TRANSACTION_STATUS = 3
802 AND TRANSACTION_ACTION_ID IN (2,3)
803 AND TRANSFER_TO_LOCATION IS NOT NULL
804 AND NOT EXISTS (
805 SELECT NULL
806 FROM MTL_ITEM_LOCATIONS MIL
807 WHERE MIL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,3,
808 MMTT.TRANSFER_ORGANIZATION,MMTT.ORGANIZATION_ID)
809 AND MIL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
810 AND MIL.INVENTORY_LOCATION_ID = MMTT.TRANSFER_TO_LOCATION);
811 if(SQL%FOUND) then l_status := 0; else l_status := 1; end if;
812
813 if validationLevel = TIMEBASED then return l_status; end if;
814
815 loadmsg('INV_INT_XLOCCODE','INV_INT_RESXFRLOCEXP');
816 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
817 SET LAST_UPDATE_DATE = SYSDATE,
818 LAST_UPDATED_BY = userid,
819 LAST_UPDATE_LOGIN = loginid,
820 PROGRAM_UPDATE_DATE = SYSDATE,
821 PROCESS_FLAG = 'E',
822 LOCK_FLAG = 'N',
823 ERROR_CODE = substrb(err_code,1,240),
824 ERROR_EXPLANATION = substrb(error_exp,1,240)
825 WHERE TRANSACTION_HEADER_ID = header_id
826 AND PROCESS_FLAG = 'Y'
827 AND TRANSACTION_STATUS = 3
828 AND TRANSACTION_ACTION_ID in (2,21,3)
829 AND TRANSFER_TO_LOCATION IS NOT NULL
830 AND NOT EXISTS (
831 SELECT NULL
832 FROM MTL_SECONDARY_LOCATORS MSL,
833 MTL_SYSTEM_ITEMS MSI
834 WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
835 MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
836 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
837 AND MSI.RESTRICT_LOCATORS_CODE = 1
838 AND MSL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
839 MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
840 AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
841 AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
842 AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
843 AND MSL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
844 AND MSL.SECONDARY_LOCATOR = MMTT.TRANSFER_TO_LOCATION
845 UNION
846 SELECT NULL
847 FROM MTL_SYSTEM_ITEMS MSI
848 WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
849 MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
850 AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
851 AND MSI.RESTRICT_LOCATORS_CODE = 2);
852 if SQL%FOUND then l_status := 0; end if;
853 return l_status;
854 END validateToLocator;
855
856 /* validates source project */
857 FUNCTION validateSourceProject RETURN NUMBER
858 IS
859 BEGIN
860 loadmsg('INV_PRJ_ERR','INV_PRJ_ERR');
861 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
862 SET LAST_UPDATE_DATE = SYSDATE,
863 LAST_UPDATED_BY = userid,
864 LAST_UPDATE_LOGIN = loginid,
865 PROGRAM_UPDATE_DATE = SYSDATE,
866 PROCESS_FLAG = 'E',
867 TRANSACTION_STATUS = 1,
868 LOCK_FLAG = 'N',
869 ERROR_CODE = substr(err_code,1,240),
870 ERROR_EXPLANATION = substr(error_exp,1,240)
871 WHERE TRANSACTION_HEADER_ID = header_id
872 AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
873 (TRANSACTION_SOURCE_TYPE_ID > 100 ) )
874 AND TRANSACTION_ACTION_ID IN (1, 27 )
875 AND PROCESS_FLAG = 'Y'
876 AND TRANSACTION_STATUS = 3
877 AND EXISTS (
878 SELECT NULL
879 FROM MTL_TRANSACTION_TYPES MTTY
880 WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
881 AND MTTY.TYPE_CLASS = 1 )
882 AND NOT EXISTS (
883 SELECT NULL
884 FROM pa_projects_expend_v prj1
885 WHERE prj1.project_id = mmtt.source_project_id ) ;
886 if(SQL%FOUND) then return 0; else return 1; end if;
887 END validateSourceProject;
888
889 /* validates source task */
890 FUNCTION validateSourceTask RETURN NUMBER
891 IS
892 BEGIN
893 loadmsg('INV_TASK_ERR','INV_TASK_ERR');
894 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
895 SET LAST_UPDATE_DATE = SYSDATE,
896 LAST_UPDATED_BY = userid,
897 LAST_UPDATE_LOGIN = loginid,
898 PROGRAM_UPDATE_DATE = SYSDATE,
899 PROCESS_FLAG = 'E',
900 TRANSACTION_STATUS = 1,
901 LOCK_FLAG = 'N',
902 ERROR_CODE = substr(err_code,1,240),
903 ERROR_EXPLANATION = substr(error_exp,1,240)
904 WHERE TRANSACTION_HEADER_ID = header_id
905 AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
906 (TRANSACTION_SOURCE_TYPE_ID > 100 ) )
907 AND TRANSACTION_ACTION_ID IN (1, 27 )
908 AND PROCESS_FLAG = 'Y'
909 AND TRANSACTION_STATUS = 3
910 AND EXISTS (
911 SELECT NULL
912 FROM MTL_TRANSACTION_TYPES MTTY
913 WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
914 AND MTTY.TYPE_CLASS = 1 )
915 AND NOT EXISTS (
916 SELECT NULL
917 FROM PA_TASKS_LOWEST_V TSK
918 WHERE TSK.PROJECT_ID = MMTT.SOURCE_PROJECT_ID AND
919 TSK.TASK_ID = MMTT.SOURCE_TASK_ID );
920 if(SQL%FOUND) then return 0; else return 1; end if;
921 END validateSourceTask;
922
923
924 /* validates cost group ids */
925 FUNCTION validateCostGroups RETURN NUMBER
926 IS
927 BEGIN
928 loadmsg('INV_COST_GROUP_ERROR', 'INV_COST_GROUP_ERROR');
929 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
930 SET LAST_UPDATE_DATE = SYSDATE,
931 LAST_UPDATED_BY = userid,
932 LAST_UPDATE_LOGIN = loginid,
933 PROGRAM_UPDATE_DATE = SYSDATE,
934 PROCESS_FLAG = 'E',
935 TRANSACTION_STATUS = 1,
936 LOCK_FLAG = 'N',
937 ERROR_CODE = substr(err_code,1,240),
938 ERROR_EXPLANATION = substr(error_exp,1,240)
939 WHERE TRANSACTION_HEADER_ID = header_id
940 AND PROCESS_FLAG = 'Y'
941 AND TRANSACTION_STATUS = 3
942 AND TRANSACTION_ACTION_ID = 24
943 AND TRANSACTION_SOURCE_TYPE_ID = 13
944 AND COST_GROUP_ID IS NOT NULL
945 AND NOT EXISTS (
946 SELECT NULL
947 FROM CST_COST_GROUPS CCG
948 WHERE CCG.COST_GROUP_ID = MMTT.COST_GROUP_ID
949 AND NVL(CCG.ORGANIZATION_ID, MMTT.ORGANIZATION_ID) = MMTT.ORGANIZATION_ID
950 AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE) ) ;
951 if(SQL%FOUND) then return 0; else return 1; end if;
952 END validateCostGroups;
953
954 /* validates expenditure type */
955 FUNCTION validateExpenditureType RETURN NUMBER
956 IS
957 exp_type_required NUMBER;
958 BEGIN
959 exp_type_required := to_number(nvl(fnd_profile.value('INV_PROJ_MISC_TXN_EXP_TYPE'),1));
960
961 loadmsg('INV_ETYPE_ERR','INV_ETYPE_ERR');
962 if ( exp_type_required = 2 ) then
963
964 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
965 SET LAST_UPDATE_DATE = SYSDATE,
966 LAST_UPDATED_BY = userid,
967 LAST_UPDATE_LOGIN = loginid,
968 PROGRAM_UPDATE_DATE = SYSDATE,
969 PROCESS_FLAG = 'E',
970 TRANSACTION_STATUS = 1,
971 LOCK_FLAG = 'N',
972 ERROR_CODE = substrb(err_code,1,240),
973 ERROR_EXPLANATION = substrb(error_exp,1,240)
974 WHERE TRANSACTION_HEADER_ID = header_id
975 AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
976 (TRANSACTION_SOURCE_TYPE_ID > 100 ) )
977 AND TRANSACTION_ACTION_ID IN (1, 27 )
978 AND PROCESS_FLAG = 'Y'
979 AND TRANSACTION_STATUS = 3
980 AND EXISTS (
981 SELECT NULL
982 FROM MTL_TRANSACTION_TYPES MTTY
983 WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
984 AND MTTY.TYPE_CLASS = 1 )
985 AND NOT EXISTS (
986 SELECT NULL
987 FROM CST_PROJ_EXP_TYPES_VAL_V CET
988 WHERE CET.EXPENDITURE_TYPE = MMTT.EXPENDITURE_TYPE
989 AND CET.COST_ELEMENT_ID = 1
990 AND TRUNC(MMTT.TRANSACTION_DATE) >= CET.SYS_LINK_START_DATE
991 AND TRUNC(MMTT.TRANSACTION_DATE) <= NVL(SYS_LINK_END_DATE,
992 MMTT.TRANSACTION_DATE + 1)
993 AND TRUNC(MMTT.TRANSACTION_DATE) >= CET.EXP_TYPE_START_DATE
994 AND TRUNC(MMTT.TRANSACTION_DATE) >= NVL(EXP_TYPE_END_DATE,
995 MMTT.TRANSACTION_DATE+1)) ;
996 else
997 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
998 SET LAST_UPDATE_DATE = SYSDATE,
999 LAST_UPDATED_BY = userid,
1000 LAST_UPDATE_LOGIN = loginid,
1001 PROGRAM_UPDATE_DATE = SYSDATE,
1002 PROCESS_FLAG = 'E',
1003 TRANSACTION_STATUS = 1,
1004 LOCK_FLAG = 'N',
1005 ERROR_CODE = substrb(err_code,1,240),
1006 ERROR_EXPLANATION = substrb(error_exp,1,240)
1007 WHERE TRANSACTION_HEADER_ID = header_id
1008 AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
1009 (TRANSACTION_SOURCE_TYPE_ID > 100 ) )
1010 AND TRANSACTION_ACTION_ID IN (1, 27 )
1011 AND PROCESS_FLAG = 'Y'
1012 AND TRANSACTION_STATUS = 3
1013 AND EXISTS (
1014 SELECT NULL
1015 FROM MTL_TRANSACTION_TYPES MTTY
1016 WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
1017 AND MTTY.TYPE_CLASS = 1 )
1018 AND MMTT.EXPENDITURE_TYPE IS NOT NULL ;
1019 end if;
1020 if(SQL%FOUND) then return 0; else return 1; end if;
1021 END validateExpenditureType;
1022
1023 /* validates expenditure org */
1024 FUNCTION validateExpenditureOrg RETURN NUMBER
1025 IS
1026 BEGIN
1027 loadmsg('INV_PAORG_ERR','INV_PAORG_ERR');
1028
1029 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1030 SET LAST_UPDATE_DATE = SYSDATE,
1031 LAST_UPDATED_BY = userid,
1032 LAST_UPDATE_LOGIN = loginid,
1033 PROGRAM_UPDATE_DATE = SYSDATE,
1034 PROCESS_FLAG = 'E',
1035 TRANSACTION_STATUS = 1,
1036 LOCK_FLAG = 'N',
1037 ERROR_CODE = substrb(err_code,1,240),
1038 ERROR_EXPLANATION = substrb(error_exp,1,240)
1039 WHERE TRANSACTION_HEADER_ID = header_id
1040 AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
1041 (TRANSACTION_SOURCE_TYPE_ID > 100 ) )
1042 AND TRANSACTION_ACTION_ID IN (1, 27 )
1043 AND PROCESS_FLAG = 'Y'
1044 AND TRANSACTION_STATUS = 3
1045 AND EXISTS (
1046 SELECT NULL
1047 FROM MTL_TRANSACTION_TYPES MTTY
1048 WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
1049 AND MTTY.TYPE_CLASS = 1 )
1050 AND NOT EXISTS (
1051 SELECT NULL
1052 FROM PA_ORGANIZATIONS_EXPEND_V POE
1053 WHERE POE.ORGANIZATION_ID = MMTT.PA_EXPENDITURE_ORG_ID
1054 AND TRUNC(SYSDATE) BETWEEN POE.DATE_FROM
1055 AND NVL(POE.DATE_TO, TRUNC(SYSDATE)));
1056 /* should we check if txn date is betwe en org active date range ? */
1057 if(SQL%FOUND) then return 0; else return 1; end if;
1058
1059 END validateExpenditureOrg;
1060
1061 /* validates transaction UOM */
1062 FUNCTION validateTxnUOM RETURN NUMBER
1063 IS
1064 BEGIN
1065 loadmsg('INV_INT_UOMCODE','INV_INT_UOMEXP');
1066
1067 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1068 SET LAST_UPDATE_DATE = SYSDATE,
1069 LAST_UPDATED_BY = userid,
1070 LAST_UPDATE_LOGIN = loginid,
1071 PROGRAM_UPDATE_DATE = SYSDATE,
1072 PROCESS_FLAG = 'E',
1073 TRANSACTION_STATUS = 1,
1074 LOCK_FLAG = 'N',
1075 ERROR_CODE = substrb(err_code,1,240),
1076 ERROR_EXPLANATION = substrb(error_exp,1,240)
1077 WHERE TRANSACTION_HEADER_ID = header_id
1078 AND INVENTORY_ITEM_ID IS NOT NULL
1079 AND PROCESS_FLAG = 'Y'
1080 AND TRANSACTION_STATUS = 3
1081 AND NOT EXISTS (
1082 SELECT NULL
1083 FROM MTL_ITEM_UOMS_VIEW MIUV
1084 WHERE MIUV.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1085 AND MIUV.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1086 AND MIUV.UOM_CODE = MMTT.TRANSACTION_UOM);
1087 if(SQL%FOUND) then return 0; else return 1; end if;
1088 END validateTxnUOM;
1089
1090 /* validates lot,serial and revision controlled items for interorg transactions */
1091 FUNCTION validateInterOrgItemControls RETURN NUMBER
1092 IS
1093 BEGIN
1094 loadmsg('INV_INT_ITMCTRL','INV_INT_ITMECTRL');
1095 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1096 SET LAST_UPDATE_DATE = SYSDATE,
1097 LAST_UPDATED_BY = userid,
1098 LAST_UPDATE_LOGIN = loginid,
1099 PROGRAM_UPDATE_DATE = SYSDATE,
1100 PROCESS_FLAG = 'E',
1101 LOCK_FLAG = 'N',
1102 ERROR_CODE = substrb(err_code,1,240),
1103 ERROR_EXPLANATION = substrb(error_exp,1,240)
1104 WHERE TRANSACTION_HEADER_ID = header_id
1105 AND PROCESS_FLAG = 'Y'
1106 AND TRANSACTION_STATUS = 3
1107 AND TRANSACTION_ACTION_ID = 3
1108 AND EXISTS (
1109 SELECT NULL
1110 FROM MTL_SYSTEM_ITEMS MS1,
1111 MTL_SYSTEM_ITEMS MS2
1112 WHERE MS1.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1113 AND MS1.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1114 AND MS2.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
1115 AND MS2.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
1116 AND ((MS1.LOT_CONTROL_CODE = 1 AND
1117 MS2.LOT_CONTROL_CODE = 2)
1118 OR (MS1.SERIAL_NUMBER_CONTROL_CODE IN (1,6)
1119 AND MS2.SERIAL_NUMBER_CONTROL_CODE IN (2,3,5))
1120 OR (MS1.REVISION_QTY_CONTROL_CODE = 1 AND
1121 MS2.REVISION_QTY_CONTROL_CODE = 2)));
1122 if(SQL%FOUND) then return 0; else return 1; end if;
1123 END validateInterOrgItemControls;
1124
1125 /* validates transaction sources */
1126 FUNCTION validateTransactionSource RETURN NUMBER
1127 IS
1128 l_status NUMBER;
1129 BEGIN
1130 loadmsg('INV_INT_SRCCODE','INV_INT_SALEXP');
1131 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1132 SET LAST_UPDATE_DATE = SYSDATE,
1133 LAST_UPDATED_BY = userid,
1134 LAST_UPDATE_LOGIN = loginid,
1135 PROGRAM_UPDATE_DATE = SYSDATE,
1136 PROCESS_FLAG = 'E',
1137 LOCK_FLAG = 'N',
1138 ERROR_CODE = substrb(err_code,1,240),
1139 ERROR_EXPLANATION = substrb(error_exp,1,240)
1140 WHERE TRANSACTION_HEADER_ID = header_id
1141 AND PROCESS_FLAG = 'Y'
1142 AND TRANSACTION_STATUS = 3
1143 AND TRANSACTION_SOURCE_TYPE_ID in (2,8)
1144 AND NOT EXISTS (
1145 SELECT NULL
1146 FROM MTL_SALES_ORDERS MSO
1147 WHERE MSO.SALES_ORDER_ID = MMTT.TRANSACTION_SOURCE_ID
1148 AND NVL(START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1149 AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
1150 AND ENABLED_FLAG = 'Y');
1151 if(SQL%FOUND) then l_status := 0; else l_status := 1; end if;
1152
1153 --bugfix 4750835 added trunc on the effectivity date validation. we are to take the account effectivity date
1154 --which does not have timestamp as date with timestamp 23:59:59
1155
1156 loadmsg('INV_INT_SRCCODE','INV_INT_ACCTEXP');
1157 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1158 SET LAST_UPDATE_DATE = SYSDATE,
1159 LAST_UPDATED_BY = userid,
1160 LAST_UPDATE_LOGIN = loginid,
1161 PROGRAM_UPDATE_DATE = SYSDATE,
1162 PROCESS_FLAG = 'E',
1163 LOCK_FLAG = 'N',
1164 ERROR_CODE = substrb(err_code,1,240),
1165 ERROR_EXPLANATION = substrb(error_exp,1,240)
1166 WHERE TRANSACTION_HEADER_ID = header_id
1167 AND PROCESS_FLAG = 'Y'
1168 AND TRANSACTION_STATUS = 3
1169 AND TRANSACTION_SOURCE_TYPE_ID = 3
1170 AND NOT EXISTS (
1171 SELECT NULL
1172 FROM GL_CODE_COMBINATIONS GCC,
1173 ORG_ORGANIZATION_DEFINITIONS OOD
1174 WHERE GCC.CODE_COMBINATION_ID = MMTT.TRANSACTION_SOURCE_ID
1175 AND GCC.CHART_OF_ACCOUNTS_ID = OOD.CHART_OF_ACCOUNTS_ID
1176 AND OOD.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1177 AND GCC.ENABLED_FLAG = 'Y'
1178 AND trunc(NVL(GCC.START_DATE_ACTIVE, SYSDATE - 1)) <= trunc(SYSDATE)
1179 AND trunc(NVL(GCC.END_DATE_ACTIVE, SYSDATE + 1)) > trunc(SYSDATE));
1180
1181 if(SQL%FOUND) then l_status := 0; end if;
1182
1183 loadmsg('INV_INT_SRCCODE','INV_INT_ALIASEXP');
1184 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1185 SET LAST_UPDATE_DATE = SYSDATE,
1186 LAST_UPDATED_BY = userid,
1187 LAST_UPDATE_LOGIN = loginid,
1188 PROGRAM_UPDATE_DATE = SYSDATE,
1189 PROCESS_FLAG = 'E',
1190 LOCK_FLAG = 'N',
1191 ERROR_CODE = substrb(err_code,1,240),
1192 ERROR_EXPLANATION = substrb(error_exp,1,240)
1193 WHERE TRANSACTION_HEADER_ID = header_id
1194 AND PROCESS_FLAG = 'Y'
1195 AND TRANSACTION_STATUS = 3
1196 AND TRANSACTION_SOURCE_TYPE_ID = 6
1197 AND NOT EXISTS (
1198 SELECT NULL
1199 FROM MTL_GENERIC_DISPOSITIONS MGD
1200 WHERE MGD.DISPOSITION_ID = MMTT.TRANSACTION_SOURCE_ID
1201 AND MGD.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1202 AND MGD.ENABLED_FLAG = 'Y'
1203 AND NVL(MGD.START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1204 AND NVL(MGD.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE );
1205 if(SQL%FOUND) then l_status := 0; end if;
1206 return l_status;
1207 END validateTransactionSource;
1208
1209 /* validates transaction reason */
1210 FUNCTION validateTransactionReason RETURN NUMBER
1211 IS
1212 BEGIN
1213 loadmsg('INV_INT_REACODE','INV_INT_REAEXP');
1214 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1215 SET LAST_UPDATE_DATE = SYSDATE,
1216 LAST_UPDATED_BY = userid,
1217 LAST_UPDATE_LOGIN = loginid,
1218 PROGRAM_UPDATE_DATE = SYSDATE,
1219 PROCESS_FLAG = 'E',
1220 LOCK_FLAG = 'N',
1221 ERROR_CODE = substrb(err_code,1,240),
1222 ERROR_EXPLANATION = substrb(error_exp,1,240)
1223 WHERE TRANSACTION_HEADER_ID = header_id
1224 AND PROCESS_FLAG = 'Y'
1225 AND TRANSACTION_STATUS = 3
1226 AND REASON_ID IS NOT NULL
1227 AND NOT EXISTS (
1228 SELECT NULL
1229 FROM MTL_TRANSACTION_REASONS MTR
1230 WHERE MTR.REASON_ID = MMTT.REASON_ID
1231 AND NVL(MTR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
1232 if SQL%FOUND then return 0; else return 1; end if;
1233 END validateTransactionReason;
1234
1235 /* validates freight and freight account */
1236 FUNCTION validateFreightInfo RETURN NUMBER
1237 IS
1238 l_status NUMBER;
1239 BEGIN
1240 loadmsg('INV_INT_FRTCODE','INV_INT_FRTEXP');
1241 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1242 SET LAST_UPDATE_DATE = SYSDATE,
1243 LAST_UPDATED_BY = userid,
1244 LAST_UPDATE_LOGIN = loginid,
1245 PROGRAM_UPDATE_DATE = SYSDATE,
1246 PROCESS_FLAG = 'E',
1247 LOCK_FLAG = 'N',
1248 ERROR_CODE = substrb(err_code,1,240),
1249 ERROR_EXPLANATION = substrb(error_exp,1,240)
1250 WHERE TRANSACTION_HEADER_ID = header_id
1251 AND TRANSACTION_ACTION_ID in (3,21)
1252 AND FREIGHT_CODE IS NOT NULL
1253 AND PROCESS_FLAG = 'Y'
1254 AND TRANSACTION_STATUS = 3
1255 AND NOT EXISTS (
1256 SELECT NULL
1257 FROM ORG_FREIGHT FR
1258 WHERE FR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1259 AND FR.FREIGHT_CODE = MMTT.FREIGHT_CODE
1260 AND NVL(FR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
1261 if SQL%FOUND then l_status := 0; else l_status := 1; end if;
1262
1263 loadmsg('INV_INT_FRTACTCODE','INV_INT_FRTACTEXP');
1264 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1265 SET LAST_UPDATE_DATE = SYSDATE,
1266 LAST_UPDATED_BY = userid,
1267 LAST_UPDATE_LOGIN = loginid,
1268 PROGRAM_UPDATE_DATE = SYSDATE,
1269 PROCESS_FLAG = 'E',
1270 LOCK_FLAG = 'N',
1271 ERROR_CODE = substrb(err_code,1,240),
1272 ERROR_EXPLANATION = substrb(error_exp,1,240)
1273 WHERE TRANSACTION_HEADER_ID = header_id
1274 AND TRANSACTION_ACTION_ID in (3,21)
1275 AND TRANSPORTATION_ACCOUNT IS NOT NULL
1276 AND PROCESS_FLAG = 'Y'
1277 AND TRANSACTION_STATUS = 3
1278 AND NOT EXISTS (
1279 SELECT NULL
1280 FROM ORG_FREIGHT FR
1281 WHERE FR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
1282 AND FR.FREIGHT_CODE = MMTT.FREIGHT_CODE
1283 AND FR.DISTRIBUTION_ACCOUNT = MMTT.TRANSPORTATION_ACCOUNT
1284 AND NVL(FR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
1285 if SQL%FOUND then l_status := 0; end if;
1286 return l_status;
1287 END validateFreightInfo;
1288
1289 /* validates lot details */
1290 FUNCTION validateLOT(txnrec IN TXNREC,
1291 org IN INV_Validate.ORG,
1292 item IN INV_Validate.ITEM) RETURN NUMBER
1293 IS
1294 CURSOR LOT_DETAILS(txnTempID
1295 MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_TEMP_ID%TYPE) IS
1296 SELECT LOT_NUMBER,
1297 TRANSACTION_QUANTITY,
1298 SERIAL_TRANSACTION_TEMP_ID,
1299 fnd_date.date_to_canonical(LOT_EXPIRATION_DATE) LOT_EXPIRATION_DATE,
1300 ROWID
1301 FROM MTL_TRANSACTION_LOTS_TEMP
1302 WHERE TRANSACTION_TEMP_ID = txnTempID;
1303
1304 l_dummy NUMBER;
1305 BEGIN
1306 if(not (item.lot_control_code = 2 AND txnrec.transaction_action_id = 24))
1307 then
1308 if(txnrec.transaction_temp_id <> NULL) then
1309 DELETE FROM MTL_TRANSACTION_LOTS_TEMP
1310 WHERE TRANSACTION_temp_id = txnrec.transaction_temp_id;
1311 end if;
1312 if((item.serial_number_control_code in (2,5) OR
1313 (item.serial_number_control_code = 6 AND
1314 txnrec.transaction_source_type_id = 2 AND
1315 txnrec.transaction_action_id = 1)) AND
1316 txnrec.transaction_action_id <> 24) then
1317 if txnrec.transaction_temp_id IS NULL then
1318 loadmsg('INV_INT_SERMISCODE','INV_INT_SERMISEXP');
1319 errupdate(txnrec.rowid);
1320 return 0;
1321 else
1322 BEGIN
1323 SELECT 1
1324 INTO l_dummy
1325 FROM MTL_SERIAL_NUMBERS_TEMP
1326 WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id
1327 AND rownum < 2;
1328 EXCEPTION
1329 WHEN NO_DATA_FOUND THEN
1330 loadmsg('INV_INT_SERMISCODE','INV_INT_SERMISEXP');
1331 errupdate(txnrec.rowid);
1332 END;
1333 end if;
1334 else
1335 if txnrec.transaction_temp_id IS NOT NULL then
1336 DELETE FROM MTL_SERIAL_NUMBERS_TEMP
1337 WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id;
1338 end if;
1339 end if;
1340 else
1341 loadmsg('INV_INT_LOTCODE','INV_INT_LOTEXP');
1342 if txnrec.transaction_temp_id IS NULL then
1343 errupdate(txnrec.rowid);
1344 return 0;
1345 else
1346
1347 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1348 SET LAST_UPDATE_DATE = SYSDATE,
1349 LAST_UPDATED_BY = userid,
1350 LAST_UPDATE_LOGIN = loginid,
1351 PROGRAM_UPDATE_DATE = SYSDATE,
1352 PROCESS_FLAG = 'E',
1353 TRANSACTION_STATUS = 1,
1354 LOCK_FLAG = 'N',
1355 ERROR_CODE = substrb(err_code,1,240),
1356 ERROR_EXPLANATION = substrb(error_exp,1,240)
1357 WHERE ROWID = txnrec.rowid
1358 AND ABS(TRANSACTION_QUANTITY) <>
1359 (SELECT ABS(SUM(TRANSACTION_QUANTITY))
1360 FROM MTL_TRANSACTION_LOTS_TEMP MTLT
1361 WHERE MTLT.TRANSACTION_TEMP_ID = txnrec.transaction_temp_id);
1362
1363 if(SQL%FOUND) then return 0; else return 1; end if;
1364
1365 if org.lot_number_uniqueness = 1 then
1366 loadmsg('INV_INT_LOTUNIQCODE','INV_INT_LOTUNIQEXP');
1367 UPDATE MTL_TRANSACTION_LOTS_TEMP MTLT
1368 SET LAST_UPDATE_DATE = SYSDATE,
1369 LAST_UPDATED_BY = userid,
1370 LAST_UPDATE_LOGIN = loginid,
1371 PROGRAM_APPLICATION_ID = applid,
1372 PROGRAM_ID = progid,
1373 PROGRAM_UPDATE_DATE = SYSDATE,
1374 REQUEST_ID = reqstid,
1375 ERROR_CODE = substrb(err_code,1,240)
1376 WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id
1377 AND EXISTS (
1378 SELECT NULL
1379 FROM MTL_LOT_NUMBERS MLN
1380 WHERE MLN.LOT_NUMBER = MTLT.LOT_NUMBER
1381 AND MLN.INVENTORY_ITEM_ID <> item.inventory_item_id);
1382
1383 if(SQL%ROWCOUNT > 1) then
1384 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MTT
1385 SET LAST_UPDATE_DATE = SYSDATE,
1386 LAST_UPDATED_BY = userid,
1387 LAST_UPDATE_LOGIN = loginid,
1388 PROGRAM_UPDATE_DATE = SYSDATE,
1389 PROCESS_FLAG = 'E',
1390 TRANSACTION_STATUS = 1,
1391 LOCK_FLAG = 'N',
1392 ERROR_CODE = substrb(err_code,1,240),
1393 ERROR_EXPLANATION = substrb(error_exp,1,240)
1394 WHERE ROWID = txnrec.rowid;
1395 return 0;
1396 end if;
1397 end if;
1398 FOR lotrec IN LOT_DETAILS(txnrec.transaction_temp_id)
1399 LOOP
1400 -- uom conversion
1401 if (item.shelf_life_code <> 1 AND
1402 lotrec.lot_expiration_date IS NULL)
1403 then
1404 SELECT fnd_date.date_to_canonical(EXPIRATION_DATE)
1405 INTO lotrec.lot_expiration_date
1406 FROM MTL_LOT_NUMBERS
1407 WHERE INVENTORY_ITEM_ID = item.inventory_item_id
1408 AND ORGANIZATION_ID = org.organization_id
1409 AND LOT_NUMBER = lotrec.lot_number;
1410 if(item.shelf_life_code = 2 AND
1411 lotrec.lot_expiration_date IS NULL) then
1412 lotrec.lot_expiration_date :=
1413 fnd_date.date_to_canonical(SYSDATE+item.shelf_life_days);
1414 end if;
1415 if(item.shelf_life_code = 4 AND
1416 lotrec.lot_expiration_date IS NULL) then
1417 loadmsg('INV_LOT_EXPREQD','INV_LOT_EXPREQD');
1418 UPDATE MTL_TRANSACTION_LOTS_TEMP MTLT
1419 SET LAST_UPDATE_DATE = SYSDATE,
1420 LAST_UPDATED_BY = userid,
1421 LAST_UPDATE_LOGIN = loginid,
1422 PROGRAM_APPLICATION_ID = applid,
1423 PROGRAM_ID = progid,
1424 PROGRAM_UPDATE_DATE = SYSDATE,
1425 REQUEST_ID = reqstid,
1426 ERROR_CODE = substrb(err_code,1,240)
1427 WHERE ROWID = lotrec.rowid;
1428 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
1429 SET ERROR_CODE = substrb(err_code,1,240),
1430 ERROR_EXPLANATION = substrb(error_exp,1,240),
1431 LAST_UPDATE_DATE = sysdate,
1432 LAST_UPDATED_BY = userid,
1433 LAST_UPDATE_LOGIN = loginid,
1434 PROGRAM_UPDATE_DATE = SYSDATE,
1435 PROCESS_FLAG = 'E',
1436 TRANSACTION_STATUS = 1,
1437 LOCK_FLAG = 'N'
1438 WHERE ROWID = txnrec.rowid;
1439 return 0;
1440 end if;
1441 end if;
1442
1443 if(item.serial_number_control_code in (2,5,6) and
1444 txnrec.transaction_source_type_id = 2 and
1445 txnrec.transaction_action_id = 1) then
1446 BEGIN
1447 SELECT 1
1448 INTO l_dummy
1449 FROM MTL_SERIAL_NUMBERS_TEMP
1450 WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id
1451 AND rownum < 2;
1452 EXCEPTION
1453 WHEN NO_DATA_FOUND THEN
1454 loadmsg('INV_INT_SERMISCODE','INV_INT_SERMISEXP');
1455 UPDATE MTL_TRANSACTION_LOTS_TEMP MTLT
1456 SET LAST_UPDATE_DATE = SYSDATE,
1457 LAST_UPDATED_BY = userid,
1458 LAST_UPDATE_LOGIN = loginid,
1459 PROGRAM_APPLICATION_ID = applid,
1460 PROGRAM_ID = progid,
1461 PROGRAM_UPDATE_DATE = SYSDATE,
1462 REQUEST_ID = reqstid,
1463 ERROR_CODE = substrb(err_code,1,240)
1464 WHERE ROWID = lotrec.rowid;
1465
1466 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
1467 SET LAST_UPDATE_DATE = SYSDATE,
1468 LAST_UPDATED_BY = userid,
1469 LAST_UPDATE_LOGIN = loginid,
1470 PROGRAM_UPDATE_DATE = SYSDATE,
1471 PROCESS_FLAG = 'E',
1472 TRANSACTION_STATUS = 1,
1473 LOCK_FLAG = 'N',
1474 ERROR_CODE = substrb(err_code,1,240),
1475 ERROR_EXPLANATION = substrb(error_exp,1,240)
1476 WHERE ROWID = txnrec.rowid;
1477 return 0;
1478 END;
1479 else
1480 if(lotrec.SERIAL_TRANSACTION_TEMP_ID IS NOT NULL) then
1481 DELETE FROM MTL_SERIAL_NUMBERS_TEMP
1482 WHERE TRANSACTION_TEMP_ID = lotrec.SERIAL_TRANSACTION_TEMP_ID;
1483 lotrec.SERIAL_TRANSACTION_TEMP_ID := NULL;
1484 end if;
1485 end if;
1486 UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
1487 SET LAST_UPDATE_DATE = SYSDATE,
1488 LAST_UPDATED_BY = userid,
1489 LAST_UPDATE_LOGIN = loginid,
1490 PROGRAM_APPLICATION_ID = applid,
1491 PROGRAM_ID = progid,
1492 PROGRAM_UPDATE_DATE = SYSDATE,
1493 REQUEST_ID = reqstid,
1494 -- PRIMARY_QUANTITY = l_priqty,
1495 LOT_EXPIRATION_DATE =
1496 fnd_date.canonical_to_date(lotrec.lot_expiration_date),
1497 SERIAL_TRANSACTION_TEMP_ID = lotrec.SERIAL_TRANSACTION_TEMP_ID
1498 WHERE ROWID = lotrec.rowid;
1499 END LOOP;
1500 end if;
1501 end if;
1502 return 1;
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 return 0;
1506 END validateLOT;
1507
1508 /* validates unit number */
1509 FUNCTION validateUnitNumber(txnrec IN TXNREC) RETURN NUMBER
1510 IS
1511 l_dummy NUMBER;
1512 BEGIN
1513 IF(NVL(PJM_UNIT_EFF.ENABLED,'N') = 'Y') THEN
1514 IF(PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM(txnrec.inventory_item_id,
1515 txnrec.organization_id) = 'Y') then
1516 IF(txnrec.transaction_source_type_id = 3 AND
1517 txnrec.transaction_action_id IN (3,21) AND
1518 txnrec.end_item_unit_number IS NOT NULL) THEN
1519 BEGIN
1520 SELECT 1
1521 INTO l_dummy
1522 FROM PJM_UNIT_NUMBERS_LOV_V
1523 WHERE UNIT_NUMBER = txnrec.end_item_unit_number;
1524 EXCEPTION
1525 WHEN NO_DATA_FOUND THEN
1526 loadmsg('INV_INT_UNITNUMBER','INV_INT_UNITNUMBER');
1527 return 0;
1528 END;
1529 END IF;
1530 END IF;
1531 END IF;
1532 return 1;
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 return 0;
1536 END validateUnitNumber;
1537
1538 FUNCTION getAccountPeriodId(orgID IN NUMBER,txndate IN DATE) RETURN NUMBER
1539 IS
1540 l_period_id NUMBER;
1541 l_open_past_period BOOLEAN := FALSE;
1542 BEGIN
1543 INVTTMTX.tdatechk(orgId,txndate,l_period_id,l_open_past_period);
1544 return l_period_id;
1545
1546 END getAccountPeriodId;
1547
1548 PROCEDURE loadmsg(errorCode IN VARCHAR2,errorExplanation IN VARCHAR2)
1549 IS
1550 BEGIN
1551 FND_MESSAGE.SET_NAME('INV',errorCode);
1552 err_code := FND_MESSAGE.GET;
1553 FND_MESSAGE.CLEAR;
1554 FND_MESSAGE.SET_NAME('INV',errorExplanation);
1555 error_exp := FND_MESSAGE.GET;
1556 FND_MESSAGE.CLEAR;
1557 END loadmsg;
1558
1559 PROCEDURE errupdate(err_row_id IN ROWID)
1560 IS
1561 BEGIN
1562 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
1563 SET ERROR_CODE = substrb(err_code,1,240),
1564 ERROR_EXPLANATION = substrb(error_exp,1,240),
1565 LAST_UPDATE_DATE = sysdate,
1566 LAST_UPDATED_BY = userid,
1567 LAST_UPDATE_LOGIN = loginid,
1568 PROGRAM_UPDATE_DATE = SYSDATE,
1569 PROCESS_FLAG = 'E',
1570 TRANSACTION_STATUS = 1,
1571 LOCK_FLAG = 'N'
1572 WHERE ROWID = err_row_id;
1573 END errupdate;
1574
1575 END INV_PROCESS_TEMP;