1 Package Body Wip_Subs_Merge as
2 /* $Header: wipsbcpb.pls 115.11 2002/11/29 15:34:42 rmahidha ship $ */
3
4 /*********************************************************
5 -- Private Procedures
6 *********************************************************/
7
8 -- Procedure to delete the Children
9 function Delete_Children(Interface_id in number)
10 return number is
11 begin
12
13 delete from mtl_transaction_lots_interface
14 where transaction_interface_id = Interface_Id ;
15
16 delete from mtl_serial_numbers_interface
17 where transaction_interface_id = Interface_Id ;
18
19 delete from mtl_serial_numbers_interface msni
20 where msni.transaction_interface_id in
21 (select serial_transaction_temp_id
22 from mtl_transaction_lots_interface mtli
23 where mtli.transaction_interface_id = Interface_Id);
24
25 return 1;
26
27 exception
28 when others then
29 return 0;
30
31 end Delete_Children ;
32
33
34 -- Procedure to delete the substitutes at the end
35 -- of the compare merge
36 Function Delete_Substitutes(p_parent_id number)
37 return number is
38 begin
39
40 delete from mtl_transactions_interface
41 where parent_id = p_parent_id
42 and substitution_type_id is not null;
43
44 return 1 ;
45
46 exception
47 when others then
48 return 0 ;
49 end Delete_Substitutes;
50
51
52
53 /* This function is a set processing after the Sub Merge */
54
55 function Post_SubMerge(p_interface_id in number,
56 p_org_id in number,
57 p_src_prj_id in number,
58 p_src_tsk_id in number,
59 p_wip_entity_id in number,
60 p_transaction_date in varchar2,
61 p_txn_hdr_id in number,
62 p_err_num in out nocopy number,
63 p_err_mesg in out nocopy varchar2) return number is
64 x_txn_act_id NUMBER ;
65 x_direction NUMBER;
66 begin
67 -- CFM Scrap. This function has been modified
68
69 select transaction_action_id, Sign(transaction_quantity)
70 into x_txn_act_id, x_direction
71 from mtl_transactions_interface
72 where transaction_interface_id = p_interface_id ;
73
74 /* Direction is used to distinguish a scrap transaction from a return from scrap transaction. here is what the decode statement below is...
75 Scrap Transaction , Component Quantity = +ve => Comp. Issue.
76 Scrap Transaction , Component Quantity = -ve => Neg. Comp. Issue.
77 Return from Scrap Txn. , Component Quantity = +ve => Comp. Return.
78 Return from Scrap Txn. , Component Quantity = -ve => Neg. Comp. Return.
79
80 */
81 Update mtl_transactions_interface
82 set transaction_source_type_id = nvl(transaction_source_type_id,5),
83 flow_schedule = nvl(flow_schedule,'Y'),
84 transaction_action_id = Decode( x_txn_act_id,
85 31,Decode(Sign(transaction_quantity),
86 -1, 1,
87 33),
88 32,Decode(Sign(transaction_quantity),
89 -1,34 ,
90 27),
91 30,Decode(x_direction,
92 1,Decode(Sign(transaction_quantity),
93 -1, 1,
94 33),
95 -1,Decode(Sign(transaction_quantity),
96 -1,34 ,
97 27))),
98 transaction_type_id = Decode( x_txn_act_id,
99 31, Decode(Sign(transaction_quantity),
100 -1, 35,
101 38),
102 32, Decode(Sign(transaction_quantity),
103 -1, 48,
104 43),
105 30, Decode(x_direction,
106 1,Decode(Sign(transaction_quantity),
107 -1, 35,
108 38),
109 -1,Decode(Sign(transaction_quantity),
110 -1, 48,
111 43))),
112 source_project_id = decode(p_src_prj_id, -1, null,
113 p_src_prj_id),
114 source_task_id = decode(p_src_tsk_id, -1, null,
115 p_src_tsk_id),
116 transaction_header_id = p_txn_hdr_id,
117 transaction_source_id = p_wip_entity_id,
118 transaction_date = to_date(p_transaction_date,WIP_CONSTANTS.DT_NOSEC_FMT)
119 where
120 substitution_type_id is NULL
121 AND process_flag = 2
122 AND parent_id = p_interface_id
123 AND organization_id = p_org_id ;
124
125
126 return 1;
127
128 exception
129
130 when others then
131 p_err_num := 0;
132 fnd_message.set_name('WIP', 'WIP_ERROR_POST_MERGE');
133 fnd_message.set_token('ENTITY1',
134 to_char(p_interface_id) );
135 p_err_mesg := fnd_message.get ;
136 return 0;
137
138 end Post_SubMerge ;
139
140
141 -- Public Procedures and functions
142 /************************************************************************
143 * Public Procedures and functions
144 *
145 * 1. Cmp_Merge_Subs :
146 * This function merges the Substitutes and the Backflushed Components
147 * It further maintains the relation between the Parent and the children
148 * records. The following are the cases for maintaining the parent
149 * child relationship :
150 *
151 * 1. Replace :
152 * - As the Original backflushed item will not
153 * - have any information in MLTI and MSNI
154 * - update the originals txn_id to the txn_id of
155 * - the substitutes
156
157 * 2. Delete :
158 * - Make sure the children are deleted.
159 *
160 * 3. Addition :
161 * - There is no impact for this, as the child
162 * - Information will be stored already.
163 *
164 * 4. Lot/Serial :
165 * - The transaction interface id of the original
166 * should be modfied to that of the substitution
167 *
168 * At the end of it all, it makes sure that the Substitutes are deleted
169 * In case of exception the calling function would be returned, so the
170 * calling program can perform the Roll Back.
171 ************************************************************************/
172
173 function Cmp_Merge_Subs(
174 interface_id in number,
175 organization_id in number,
176 err_num in out nocopy number,
177 err_mesg in out nocopy varchar2
178 ) return number is
179
180 /************************************************************************
181 -- THis Cursor is used for going through the substitutes one after the other
182 -- the order in which it would go through is :
183 -- 1. Initially Replacment
184 -- 2. Deletion
185 -- 3. Addition and
186 -- 4. Replace
187 ************************************************************************/
188 CURSOR Substitute_Cursor(interface_id NUMBER,
189 Org_Id NUMBER) is
190 Select
191 SUBSTITUTION_TYPE_ID,
192 TRANSACTION_INTERFACE_ID,
193 OPERATION_SEQ_NUM,
194 INVENTORY_ITEM_ID,
195 SUBSTITUTION_ITEM_ID,
196 REVISION,
197 SUBINVENTORY_CODE,
198 LOCATOR_ID,
199 TRANSACTION_UOM,
200 TRANSACTION_QUANTITY,
201 REASON_ID,
202 TRANSACTION_REFERENCE,
203 ORGANIZATION_ID
204 from mtl_transactions_interface
205 where parent_id = interface_id
206 and substitution_type_id is not null
207 and process_flag = 2
208 and organization_id = Org_Id
209 order by substitution_type_id;
210
211 /************************************************************************
212 -- This cursor is used to go through the backflushed transaction one after the
213 -- other, and return the OP Seq and the Item ID, if a particular Item exists in
214 -- a Operation Sequence for that parent_id
215 ************************************************************************/
216 CURSOR BackFlush_Cursor(Interface_Id NUMBER, Op_Seq NUMBER, Source_Item NUMBER,
217 Org_Id NUMBER) is
218 SELECT Transaction_Interface_Id,
219 operation_seq_num,
220 inventory_item_id,
221 transaction_quantity
222 FROM mtl_transactions_interface
223 WHERE parent_id = interface_Id
224 AND substitution_type_id is NULL
225 AND process_flag = 2
226 AND operation_seq_num = Op_Seq
227 AND inventory_item_id = Source_Item
228 AND organization_id = Org_Id ;
229
230 Op_Seq NUMBER;
231 Result_Item VARCHAR2(2000);
232 Item_Source_Id NUMBER;
233 Txn_Interface_Id NUMBER;
234 x_transaction_qty NUMBER;
235 x_pri_uom VARCHAR2(3);
236 Deletion_Exception EXCEPTION;
237 Replacement_Exception EXCEPTION;
238 Lot_Serial_Exception EXCEPTION;
239
240 BEGIN
241
242
243
244 /* **************************************************
245 -- Note the newly seed WIP_SUBSTITUTION_TYPE data are
246 -- 1 Add
247 -- 2 Replace
248 -- 3 Delete
249 -- 4 Lot/Serial
250
251 -- I execute this cursor first as I believe that the
252 -- substitions information will be small compared
253 -- to the backflushed information
254
255 -- Note: The enteries for the Substitution Item Id and the
256 -- Inventory Item for the various kind of operation is
257 -- listed below :
258 -- Operation Subst. Item Inv Item
259 --
260 -- Replace X X
261 -- Deletion X
262 -- Addition X
263 -- Lot/Serial X
264
265 *****************************************************/
266
267 -- Get Reason, Op_Seq, Source, Substitute, Revision,
268 -- Supply_Locator, Supply_Subinv, Quantity, UOM, Department
269 For Substitute_Record IN
270 Substitute_Cursor(Interface_Id, Organization_Id) LOOP
271
272
273 -- This is for Replacement
274 if (Substitute_Record.Substitution_Type_Id = 1 ) then
275
276 /******************************************************
277 -- Conditions for Replacement
278 -- 1. Replace
279 -- a. If Op Seq and Item Exists
280 -- - Replace the Item
281 -- b. Else
282 -- (Cases: Op. Seq doesn't exist,
283 -- Op. Seq Exists but item doesn't)
284 -- - Error it out
285 ******************************************************/
286
287 OPEN BackFlush_Cursor( interface_id,
288 Substitute_Record.operation_seq_num,
289 Substitute_Record.Inventory_Item_Id,
290 Substitute_Record.Organization_Id );
291
292 FETCH BackFlush_Cursor INTO Txn_Interface_Id, Op_Seq,
293 Result_Item, x_transaction_qty ;
294
295 -- Operation ID and the Item is found.
296 if(BackFlush_Cursor%FOUND) then
297
298 DELETE from mtl_transactions_interface
299 WHERE OPERATION_SEQ_NUM
300 = Substitute_Record.operation_seq_num
301 AND INVENTORY_ITEM_ID
302 = Substitute_Record.inventory_item_id
303 AND ORGANIZATION_ID
304 = Substitute_Record.organization_id
305 AND parent_id
306 = interface_id
307 AND Transaction_Interface_Id
308 = Txn_Interface_Id
309 AND Substitution_Type_Id is NULL;
310
311 UPDATE mtl_transactions_interface
312 SET INVENTORY_ITEM_ID =
313 Substitute_Record.Substitution_item_id,
314 Substitution_item_id =
315 NULL,
316 Substitution_type_id =
317 NULL ,
318 transaction_quantity =
319 NVL(transaction_quantity, x_transaction_qty)
320 where Transaction_Interface_id =
321 Substitute_Record.transaction_interface_id ;
322
323 CLOSE BackFlush_Cursor ;
324
325 else
326 CLOSE BackFlush_Cursor ;
327 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_REPLACE');
328 fnd_message.set_token('ENTITY1',
329 to_char(Substitute_Record.operation_seq_num));
330 Raise Replacement_Exception ;
331
332 end if;
333
334
335 -- This is a deletion
336 elsif (Substitute_Record.substitution_type_id = 2 ) then
337
338 /******************************************************
339 -- Conditions for Deletion
340 -- 2. Delete
341 -- a. If Op Seq and Item Exists
342 -- - Delete1 the Item
343 -- b. Else
344 -- (Cases: Op. Seq doesn't exist,
345 -- Op. Seq Exists but item doesn't)
346 -- - Error it out
347 ******************************************************/
348
349 OPEN BackFlush_Cursor( interface_id,
350 Substitute_Record.operation_seq_num,
351 Substitute_Record.inventory_item_id,
352 Substitute_Record.Organization_Id) ;
353
354 FETCH BackFlush_Cursor INTO Txn_Interface_Id, Op_Seq,
355 Result_Item, x_transaction_qty;
356
357 -- The Item exists at a particular Operation
358 -- Sequence Number
359 if(BackFlush_Cursor%FOUND) then
360 DELETE from mtl_transactions_interface
361 WHERE OPERATION_SEQ_NUM
362 = Substitute_Record.operation_seq_num
363 AND INVENTORY_ITEM_ID
364 = Substitute_Record.inventory_item_id
365 AND ORGANIZATION_ID
366 = Substitute_Record.organization_id
367 AND parent_id
368 = interface_id
369 AND Transaction_Interface_Id
370 = Txn_Interface_Id
371 AND Substitution_Type_Id is NULL;
372
373 CLOSE BackFlush_Cursor ;
374
375 if(Delete_Children(Txn_Interface_Id)=0) then
376 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_DELETE');
377 fnd_message.set_token('ENTITY2',
378 to_char(Substitute_Record.operation_seq_num));
379 Raise Deletion_Exception;
380 end if;
381
382 else
383 CLOSE BackFlush_Cursor ;
384 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_DELETE');
385 fnd_message.set_token('ENTITY2',
386 to_char(Substitute_Record.operation_seq_num));
387 Raise Deletion_Exception ;
388
389 end if ;
390
391 -- This is addition
392 elsif(Substitute_Record.substitution_type_id = 3) then
393
394 /******************************************************
395 -- In the case of addition we copy the Substitute to the
396 -- Source. The cases allowed are :
397 -- 3. Addition:
398 -- a. If Op Seq Exists - Add the Item
399 -- b. If Op. Seq and Item Exists -
400 -- Treat it like additional issue
401 -- c. If the Op. Seq doesn't Exist
402 -- Don't Error it out, Issue it.
403 -- Note : We didn't Merge the Additions into one
404 -- Mtl Isssue as inorder to merge the transactions
405 -- they have to be in the same UOM and we should not
406 -- overwrite the transaction Information without the
407 -- user's knowledge (so we decided to have it as 2
408 -- separate transactions) dsoosai,jgu
409 ******************************************************/
410
411 UPDATE mtl_transactions_interface
412 SET SUBSTITUTION_TYPE_ID = NULL,
413 INVENTORY_ITEM_ID =
414 Substitute_Record.Substitution_Item_Id,
415 SUBSTITUTION_ITEM_ID =
416 NULL
417 where TRANSACTION_INTERFACE_ID =
418 Substitute_Record.Transaction_Interface_Id;
419
420 -- This is Lot/Serial
421 elsif(Substitute_Record.substitution_type_id = 4) then
422
423 /******************************************************
424 -- In the case of Lot/Serial we set the txn_interface_id
425 -- of the Orignal to that of the original.
426 -- The cases allowed are :
427 -- 4. Lot/Serial:
428 -- a. If Op Seq and Item Exists - Replace the
429 -- the Item Lot/Serial Association.
430 -- b. Else -
431 -- (If Op Seq. doesn't exist or the
432 -- Op Seq and Item does not exist
433 -- then we will fail it).
434 --
435 -- Note: we will error it out if the Substitution Lot
436 -- serial information is in a UOM other than the Primary
437 -- UOM (this willhappen only when the user enters info.
438 -- through the interface).
439 ******************************************************/
440
441 if (Wip_Common.Is_Primary_UOM(
442 p_item_id => Substitute_Record.inventory_item_id,
443 p_org_id => Substitute_Record.Organization_Id,
444 p_txn_uom => Substitute_Record.Transaction_Uom,
445 p_pri_uom => x_pri_uom) = 1 ) then
446
447 OPEN BackFlush_Cursor( interface_id,
448 Substitute_Record.operation_seq_num,
449 Substitute_Record.inventory_item_id,
450 Substitute_Record.Organization_Id) ;
451
452 FETCH BackFlush_Cursor INTO Txn_Interface_Id, Op_Seq,
453 Result_Item, x_transaction_qty;
454
455 if(BackFlush_Cursor%FOUND) then
456 DELETE from mtl_transactions_interface
457 WHERE transaction_interface_id =
458 Substitute_Record.transaction_interface_id;
459
460 -- Fix bug#1054753, take the substitution subinventory
461 UPDATE mtl_transactions_interface
462 SET transaction_interface_id =
463 Substitute_Record.transaction_interface_id,
464 subinventory_code =
465 Substitute_Record.subinventory_code
466 where TRANSACTION_INTERFACE_ID =
467 Txn_Interface_Id ;
468
469 CLOSE BackFlush_Cursor ;
470
471 else
472
473 CLOSE BackFlush_Cursor ;
474 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_LOT_SERIAL');
475 fnd_message.set_token('ENTITY1',
476 to_char(Substitute_Record.operation_seq_num));
477 Raise Lot_Serial_Exception ;
478 end if;
479
480 else
481
482 fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_LOT_UOM');
483 fnd_message.set_token('ENTITY1',
484 to_char(Substitute_Record.inventory_item_id));
485 fnd_message.set_token('ENTITY2',
486 Substitute_Record.Transaction_uom);
487 fnd_message.set_token('ENTITY3',
488 x_pri_uom);
489 Raise Lot_Serial_Exception ;
490
491 end if ;
492 -- Transaction UOM
493
494 end If;
495 -- For Substitute Type
496
497 END LOOP ;
498
499
500 -- do the deletion here
501 if(Delete_Substitutes(Interface_Id) = 0) then
502 return 0;
503 end if ;
504
505 return 1;
506
507
508 EXCEPTION
509
510 When Deletion_Exception then
511 err_mesg := fnd_message.get ;
512 return 0;
513
514 When Replacement_Exception then
515 err_mesg := fnd_message.get ;
516 return 0;
517
518 When Lot_Serial_Exception then
519 err_mesg := fnd_message.get ;
520 return 0;
521
522 When NO_DATA_FOUND then
523 -- This is not an error
524 return 1;
525 when others then
526 err_mesg := 'SQL Error in Wip_Subs_Merge.Cmp_Merge_Subs: ';
527 err_mesg := err_mesg || SUBSTR(SQLERRM,1,130);
528 return 0;
529
530 end Cmp_Merge_Subs ;
531
532
533 end Wip_Subs_Merge;
[Home] [Help]