[Home] [Help]
PACKAGE BODY: APPS.BOM_ROUTINGINTERFACE_PUB
Source
1 PACKAGE BODY BOM_RoutingInterface_PUB AS
2 /* $Header: BOMPRTGB.pls 120.1 2005/06/21 02:58:37 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='BOM_RoutingInterface_PUB';
5 g_yes CONSTANT number := 1;
6 g_no CONSTANT number := 2;
7
8 g_CommitRows number;
9 g_Commit boolean;
10 g_OrgId number;
11 g_OrgCode varchar2(3);
12 g_AllOrgs number;
13 g_DeleteRows boolean;
14 g_UserId number := -1;
15 g_LoginId number;
16 g_RequestId number;
17 g_ProgramId number;
18 g_ApplicationId number;
19
20 PROCEDURE ImportHeader(x_return_status IN OUT NOCOPY VARCHAR2) is
21 cursor l_interface_csr is
22 Select rowid,
23 routing_sequence_id,
24 assembly_item_id,
25 organization_id,
26 alternate_routing_designator,
27 routing_type,
28 common_assembly_item_id,
29 common_routing_sequence_id,
30 routing_comment,
31 completion_subinventory,
32 completion_locator_id,
33 attribute_category,
34 attribute1,
35 attribute2,
36 attribute3,
37 attribute4,
38 attribute5,
39 attribute6,
40 attribute7,
41 attribute8,
42 attribute9,
43 attribute10,
44 attribute11,
45 attribute12,
46 attribute13,
47 attribute14,
48 attribute15,
49 process_revision,
50 organization_code,
51 assembly_item_number,
52 common_item_number,
53 location_name,
54 transaction_type,
55 line_id,
56 line_code,
57 mixed_model_map_flag,
58 priority,
59 cfm_routing_flag,
60 total_product_cycle_time,
61 ctp_flag
62 From bom_op_routings_interface
63 Where process_flag = 1
64 And (g_AllOrgs = g_yes
65 or organization_id = g_OrgId
66 or organization_code = g_OrgCode
67 );
68 l_phase NUMBER := 1; -- scan table twice in case commons point to new routings
69 l_return_status VARCHAR2(1);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72 l_msg_name VARCHAR2(80);
73 l_msg_app VARCHAR2(3);
74 l_DelGrpFound BOOLEAN := false;
75 l_ret_code NUMBER := 0;
76 cursor l_RtgDeleteGroup_csr is
77 Select delete_group_name,
78 description
79 From bom_interface_delete_groups
80 Where upper(entity_name) = G_RtgDelEntity
81 And rownum = 1;
82 cursor l_Transaction_csr is
83 Select mtl_system_items_interface_s.nextval transaction_id
84 From dual;
85 l_api_name CONSTANT VARCHAR2(30) := 'ImportHeader';
86 Begin
87
88
89 While l_phase <= 2 loop -- two passes
90 For l_interface_rec in l_interface_csr loop
91 Declare
92 l_routing_rec BOM_RoutingHeader_PVT.routing_rec_type;
93 Begin -- nested block within loop
94 if (upper(l_interface_rec.transaction_type) = 'CREATE') then
95 l_interface_rec.transaction_type := G_Insert ;
96 else
97 l_interface_rec.transaction_type := upper(l_interface_rec.transaction_type);
98 end if ;
99
100 -- primary keys
101 If l_interface_rec.routing_sequence_id = G_NullNum then
102 l_routing_rec.routing_sequence_id := null;
103 Else
104 l_routing_rec.routing_sequence_id :=
105 l_interface_rec.routing_sequence_id;
106 End if;
107 If l_interface_rec.assembly_item_id = G_NullNum then
108 l_routing_rec.assembly_item_id := null;
109 Else
110 l_routing_rec.assembly_item_id := l_interface_rec.assembly_item_id;
111 End if;
112 If l_interface_rec.organization_id = G_NullNum then
113 l_routing_rec.organization_id := null;
114 Else
115 l_routing_rec.organization_id := l_interface_rec.organization_id;
116 End if;
117 If l_interface_rec.alternate_routing_designator = G_NullChar then
118 l_routing_rec.alternate_routing_designator := null;
119 Else
120 l_routing_rec.alternate_routing_designator :=
121 l_interface_rec.alternate_routing_designator;
122 End if;
123 -- end primary keys
124
125
126 If l_interface_rec.routing_type = G_NullNum then
127 l_routing_rec.routing_type := null;
128 Elsif l_interface_rec.transaction_type = G_Update then
129 l_routing_rec.routing_type :=
130 nvl(l_interface_rec.routing_type, Fnd_Api.G_Miss_Num);
131 Else
132 l_routing_rec.routing_type := l_interface_rec.routing_type;
133 End if;
134
135 If l_interface_rec.common_assembly_item_id = G_NullNum then
136 l_routing_rec.common_assembly_item_id := null;
137 Elsif l_interface_rec.transaction_type = G_Update then
138 l_routing_rec.common_assembly_item_id :=
139 nvl(l_interface_rec.common_assembly_item_id, Fnd_Api.G_Miss_Num);
140 Else
141 l_routing_rec.common_assembly_item_id :=
142 l_interface_rec.common_assembly_item_id;
143 End if;
144
145 If l_interface_rec.common_routing_sequence_id = G_NullNum then
146 l_routing_rec.common_routing_sequence_id := null;
147 Elsif l_interface_rec.transaction_type = G_Update then
148 l_routing_rec.common_routing_sequence_id :=
149 nvl(l_interface_rec.common_routing_sequence_id, Fnd_Api.G_Miss_Num);
150 Else
151 l_routing_rec.common_routing_sequence_id :=
152 l_interface_rec.common_routing_sequence_id;
153 End if;
154
155 If l_interface_rec.routing_comment = G_NullChar then
156 l_routing_rec.routing_comment := null;
157 Elsif l_interface_rec.transaction_type = G_Update then
158 l_routing_rec.routing_comment :=
159 nvl(l_interface_rec.routing_comment, Fnd_Api.G_Miss_Char);
160 Else
161 l_routing_rec.routing_comment := l_interface_rec.routing_comment;
162 End if;
163
164 If l_interface_rec.completion_subinventory = G_NullChar then
165 l_routing_rec.completion_subinventory := null;
166 Elsif l_interface_rec.transaction_type = G_Update then
167 l_routing_rec.completion_subinventory :=
168 nvl(l_interface_rec.completion_subinventory, Fnd_Api.G_Miss_Char);
169 Else
170 l_routing_rec.completion_subinventory :=
171 l_interface_rec.completion_subinventory;
172 End if;
173
174 If l_interface_rec.completion_locator_id = G_NullNum then
175 l_routing_rec.completion_locator_id := null;
176 Elsif l_interface_rec.transaction_type = G_Update then
177 l_routing_rec.completion_locator_id :=
178 nvl(l_interface_rec.completion_locator_id, Fnd_Api.G_Miss_Num);
179 Else
180 l_routing_rec.completion_locator_id :=
181 l_interface_rec.completion_locator_id;
182 End if;
183
184 If l_interface_rec.attribute_category = G_NullChar then
185 l_routing_rec.attribute_category := null;
186 Elsif l_interface_rec.transaction_type = G_Update then
187 l_routing_rec.attribute_category :=
188 nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
189 Else
190 l_routing_rec.attribute_category :=
191 l_interface_rec.attribute_category;
192 End if;
193
194 If l_interface_rec.attribute1 = G_NullChar then
195 l_routing_rec.attribute1 := null;
196 Elsif l_interface_rec.transaction_type = G_Update then
197 l_routing_rec.attribute1 :=
198 nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
199 Else
200 l_routing_rec.attribute1 := l_interface_rec.attribute1;
201 End if;
202
203 If l_interface_rec.attribute2 = G_NullChar then
204 l_routing_rec.attribute2 := null;
205 Elsif l_interface_rec.transaction_type = G_Update then
206 l_routing_rec.attribute2 :=
207 nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
208 Else
209 l_routing_rec.attribute2 := l_interface_rec.attribute2;
210 End if;
211
212 If l_interface_rec.attribute3 = G_NullChar then
213 l_routing_rec.attribute3 := null;
214 Elsif l_interface_rec.transaction_type = G_Update then
215 l_routing_rec.attribute3 :=
216 nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
217 Else
218 l_routing_rec.attribute3 := l_interface_rec.attribute3;
219 End if;
220
221 If l_interface_rec.attribute4 = G_NullChar then
222 l_routing_rec.attribute4 := null;
223 Elsif l_interface_rec.transaction_type = G_Update then
224 l_routing_rec.attribute4 :=
225 nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
226 Else
227 l_routing_rec.attribute4 := l_interface_rec.attribute4;
228 End if;
229
230 If l_interface_rec.attribute5 = G_NullChar then
231 l_routing_rec.attribute5 := null;
232 Elsif l_interface_rec.transaction_type = G_Update then
233 l_routing_rec.attribute5 :=
234 nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
235 Else
236 l_routing_rec.attribute5 := l_interface_rec.attribute5;
237 End if;
238
239 If l_interface_rec.attribute6 = G_NullChar then
240 l_routing_rec.attribute6 := null;
241 Elsif l_interface_rec.transaction_type = G_Update then
242 l_routing_rec.attribute6 :=
243 nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
244 Else
245 l_routing_rec.attribute6 := l_interface_rec.attribute6;
246 End if;
247
248 If l_interface_rec.attribute7 = G_NullChar then
249 l_routing_rec.attribute7 := null;
250 Elsif l_interface_rec.transaction_type = G_Update then
251 l_routing_rec.attribute7 :=
252 nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
253 Else
254 l_routing_rec.attribute7 := l_interface_rec.attribute7;
255 End if;
256
257 If l_interface_rec.attribute8 = G_NullChar then
258 l_routing_rec.attribute8 := null;
259 Elsif l_interface_rec.transaction_type = G_Update then
260 l_routing_rec.attribute8 :=
261 nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
262 Else
263 l_routing_rec.attribute8 := l_interface_rec.attribute8;
264 End if;
265
266 If l_interface_rec.attribute9 = G_NullChar then
267 l_routing_rec.attribute9 := null;
268 Elsif l_interface_rec.transaction_type = G_Update then
269 l_routing_rec.attribute9 :=
270 nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
271 Else
272 l_routing_rec.attribute9 := l_interface_rec.attribute9;
273 End if;
274
275 If l_interface_rec.attribute10 = G_NullChar then
276 l_routing_rec.attribute10 := null;
277 Elsif l_interface_rec.transaction_type = G_Update then
278 l_routing_rec.attribute10 :=
279 nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
280 Else
281 l_routing_rec.attribute10 := l_interface_rec.attribute10;
282 End if;
283
284 If l_interface_rec.attribute11 = G_NullChar then
285 l_routing_rec.attribute11 := null;
286 Elsif l_interface_rec.transaction_type = G_Update then
287 l_routing_rec.attribute11 :=
288 nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
289 Else
290 l_routing_rec.attribute11 := l_interface_rec.attribute11;
291 End if;
292
293 If l_interface_rec.attribute12 = G_NullChar then
294 l_routing_rec.attribute12 := null;
295 Elsif l_interface_rec.transaction_type = G_Update then
296 l_routing_rec.attribute12 :=
297 nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
298 Else
299 l_routing_rec.attribute12 := l_interface_rec.attribute12;
300 End if;
301
302 If l_interface_rec.attribute13 = G_NullChar then
303 l_routing_rec.attribute13 := null;
304 Elsif l_interface_rec.transaction_type = G_Update then
305 l_routing_rec.attribute13 :=
306 nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
307 Else
308 l_routing_rec.attribute13 := l_interface_rec.attribute13;
309 End if;
310
311 If l_interface_rec.attribute14 = G_NullChar then
312 l_routing_rec.attribute14 := null;
313 Elsif l_interface_rec.transaction_type = G_Update then
314 l_routing_rec.attribute14 :=
315 nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
316 Else
317 l_routing_rec.attribute14 := l_interface_rec.attribute14;
318 End if;
319
320 If l_interface_rec.attribute15 = G_NullChar then
321 l_routing_rec.attribute15 := null;
322 Elsif l_interface_rec.transaction_type = G_Update then
323 l_routing_rec.attribute15 :=
324 nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
325 Else
326 l_routing_rec.attribute15 := l_interface_rec.attribute15;
327 End if;
328
329 If l_interface_rec.organization_code = G_NullChar then
330 l_routing_rec.organization_code := null;
331 Elsif l_interface_rec.transaction_type = G_Update then
332 l_routing_rec.organization_code :=
333 nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
334 Else
335 l_routing_rec.organization_code := l_interface_rec.organization_code;
336 End if;
337
338 If l_interface_rec.assembly_item_number = G_NullChar then
339 l_routing_rec.assembly_item_number := null;
340 Elsif l_interface_rec.transaction_type = G_Update then
341 l_routing_rec.assembly_item_number :=
342 nvl(l_interface_rec.assembly_item_number, Fnd_Api.G_Miss_Char);
343 Else
344 l_routing_rec.assembly_item_number :=
345 l_interface_rec.assembly_item_number;
346 End if;
347
348 If l_interface_rec.common_item_number = G_NullChar then
349 l_routing_rec.common_item_number := null;
350 Elsif l_interface_rec.transaction_type = G_Update then
351 l_routing_rec.common_item_number :=
352 nvl(l_interface_rec.common_item_number, Fnd_Api.G_Miss_Char);
353 Else
354 l_routing_rec.common_item_number :=
355 l_interface_rec.common_item_number;
356 End if;
357
358 If l_interface_rec.location_name = G_NullChar then
359 l_routing_rec.location_name := null;
360 Elsif l_interface_rec.transaction_type = G_Update then
361 l_routing_rec.location_name :=
362 nvl(l_interface_rec.location_name, Fnd_Api.G_Miss_Char);
363 Else
364 l_routing_rec.location_name := l_interface_rec.location_name;
365 End if;
366
367 If l_interface_rec.line_id = G_NullNum then
368 l_routing_rec.line_id := null;
369 Elsif l_interface_rec.transaction_type = G_Update then
370 l_routing_rec.line_id :=
371 nvl(l_interface_rec.line_id, Fnd_Api.G_Miss_Num);
372 Else
373 l_routing_rec.line_id := l_interface_rec.line_id;
374 End if;
375
376 If l_interface_rec.line_code = G_NullChar then
377 l_routing_rec.line_code := null;
378 Elsif l_interface_rec.transaction_type = G_Update then
379 l_routing_rec.line_code :=
380 nvl(l_interface_rec.line_code, Fnd_Api.G_Miss_Char);
381 Else
382 l_routing_rec.line_code := l_interface_rec.line_code;
383 End if;
384
385 If l_interface_rec.mixed_model_map_flag = G_NullNum then
386 l_routing_rec.mixed_model_map_flag := null;
387 Elsif l_interface_rec.transaction_type = G_Update then
388 l_routing_rec.mixed_model_map_flag :=
389 nvl(l_interface_rec.mixed_model_map_flag, Fnd_Api.G_Miss_Num);
390 Else
391 l_routing_rec.mixed_model_map_flag :=
392 l_interface_rec.mixed_model_map_flag;
393 End if;
394
395 If l_interface_rec.priority = G_NullNum then
396 l_routing_rec.priority := null;
397 Elsif l_interface_rec.transaction_type = G_Update then
398 l_routing_rec.priority :=
399 nvl(l_interface_rec.priority, Fnd_Api.G_Miss_Num);
400 Else
401 l_routing_rec.priority := l_interface_rec.priority;
402 End if;
403
404 If l_interface_rec.cfm_routing_flag = G_NullNum then
405 l_routing_rec.cfm_routing_flag := null;
406 Elsif l_interface_rec.transaction_type = G_Update then
407 l_routing_rec.cfm_routing_flag :=
408 nvl(l_interface_rec.cfm_routing_flag, Fnd_Api.G_Miss_Num);
409 Else
410 l_routing_rec.cfm_routing_flag := l_interface_rec.cfm_routing_flag;
411 End if;
412
413 If l_interface_rec.total_product_cycle_time = G_NullNum then
414 l_routing_rec.total_product_cycle_time := null;
415 Elsif l_interface_rec.transaction_type = G_Update then
416 l_routing_rec.total_product_cycle_time :=
417 nvl(l_interface_rec.total_product_cycle_time, Fnd_Api.G_Miss_Num);
418 Else
419 l_routing_rec.total_product_cycle_time :=
420 l_interface_rec.total_product_cycle_time;
421 End if;
422
423 If l_interface_rec.ctp_flag = G_NullNum then
424 l_routing_rec.ctp_flag := null;
425 Elsif l_interface_rec.transaction_type = G_Update then
426 l_routing_rec.ctp_flag :=
427 nvl(l_interface_rec.ctp_flag, Fnd_Api.G_Miss_Num);
428 Else
429 l_routing_rec.ctp_flag := l_interface_rec.ctp_flag;
430 End if;
431
432 If l_interface_rec.transaction_type = G_Insert then
433
434 BOM_RoutingHeader_PVT.CreateRouting (
435 p_api_version => 1.0,
436 p_init_msg_list => FND_API.G_TRUE,
437 p_commit => FND_API.G_FALSE,
438 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
439 x_return_status => l_return_status,
440 x_msg_count => l_msg_count,
441 x_msg_data => l_msg_data,
442 p_routing_rec => l_routing_rec,
443 x_routing_rec => l_routing_rec
444 );
445
446 -- insert given process_revision
447 If nvl(l_interface_rec.process_revision, G_NullChar) <>
448 G_NullChar and l_phase = 1 then
449 Insert into mtl_rtg_item_revs_interface
450 (inventory_item_id,
451 inventory_item_number,
452 organization_id,
453 organization_code,
454 process_revision,
455 process_flag,
456 effectivity_date,
457 implementation_date,
458 transaction_type)
459 values (l_interface_rec.assembly_item_id,
460 l_interface_rec.assembly_item_number,
461 l_interface_rec.organization_id,
462 l_interface_rec.organization_code,
463 upper(l_interface_rec.process_revision),
464 1,
465 sysdate,
466 sysdate,
467 G_Insert);
468 End if; -- new routing revision
469
470 Elsif l_interface_rec.transaction_type = G_Update then
471
472 BOM_RoutingHeader_PVT.UpdateRouting(
473 p_api_version => 1.0,
474 p_init_msg_list => FND_API.G_TRUE,
475 p_commit => FND_API.G_FALSE,
476 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
477 x_return_status => l_return_status,
478 x_msg_count => l_msg_count,
479 x_msg_data => l_msg_data,
480 p_routing_rec => l_routing_rec,
481 x_routing_rec => l_routing_rec);
482
483 Elsif l_interface_rec.transaction_type = G_Delete then
484
485 l_DelGrpFound := false;
486 For l_DelGrp_rec in l_RtgDeleteGroup_csr loop
487 l_DelGrpFound := true;
488 BOM_RoutingHeader_PVT.DeleteRouting(
489 p_api_version => 1.0,
490 p_init_msg_list => FND_API.G_TRUE,
491 p_commit => FND_API.G_FALSE,
492 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
493 x_return_status => l_return_status,
494 x_msg_count => l_msg_count,
495 x_msg_data => l_msg_data,
496 p_delete_group => l_DelGrp_rec.delete_group_name,
497 p_description => l_DelGrp_rec.description,
498 p_routing_rec => l_routing_rec,
499 x_routing_rec => l_routing_rec
500 );
501 End loop; -- delete routing
502 If not l_DelGrpFound then
503 Fnd_Message.Set_Name('BOM', 'BOM_DELETE_GROUP_NULL');
504 l_return_status := FND_API.G_RET_STS_ERROR;
505 FND_MSG_PUB.Initialize;
506 FND_MSG_PUB.Add;
507 FND_MSG_PUB.Count_And_Get(
508 p_count => l_msg_count,
509 p_data => l_msg_data
510 );
511 End if; -- Delete group unspecified
512 End if; -- insert, update and delete
513
514 If l_return_status = Fnd_Api.G_RET_STS_SUCCESS then
515 If g_DeleteRows then
516 Delete from bom_op_routings_interface
517 Where rowid = l_interface_rec.rowid;
518 Else
519 Update bom_op_routings_interface
520 Set process_flag = 7,
521 transaction_id = mtl_system_items_interface_s.nextval,
522 request_id = nvl(request_id,g_RequestId),
523 program_id = nvl(program_id,g_ProgramId),
524 program_application_id = nvl(program_application_id,g_ApplicationId),
525 program_update_date = nvl(program_update_date,sysdate),
526 created_by = nvl(created_by,g_UserId),
527 last_updated_by = nvl(last_updated_by,g_UserId),
528 creation_date = nvl(creation_date,sysdate),
529 last_update_date = nvl(last_update_date,sysdate),
530 last_update_login = nvl(last_update_login,g_LoginId)
531 Where rowid = l_interface_rec.rowid;
532 End if;
533 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR then
534 If l_phase = 2 then
535 If l_msg_count > 1 then
536 l_msg_data := FND_MSG_PUB.Get;
537 End if;
538 Fnd_Message.Parse_Encoded(
539 ENCODED_MESSAGE => l_msg_data,
540 APP_SHORT_NAME => l_msg_app,
541 MESSAGE_NAME => l_msg_name);
542 For l_transaction_rec in l_Transaction_csr loop
543 l_ret_code := INVPUOPI.mtl_log_interface_err(
544 org_id => g_OrgId,
545 user_id => g_UserId,
546 login_id => g_LoginId,
547 prog_appid => g_ApplicationId,
548 prog_id => g_ProgramId,
549 req_id => g_RequestId,
550 trans_id => l_transaction_rec.transaction_id,
551 error_text => l_msg_data,
552 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
553 msg_name => l_msg_name,
554 err_text => l_msg_data);
555 If l_ret_code <> 0 then
556 raise FND_API.G_EXC_UNEXPECTED_ERROR;
557 end if;
558 Update bom_op_routings_interface
559 Set process_flag = 3,
560 transaction_id = l_transaction_rec.transaction_id
561 Where rowid = l_interface_rec.rowid;
562 End loop; -- log error
563 End if; -- final phase
564 Elsif l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR then
565 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
566 End if; -- process return status
567 End; -- nested block
568
569 If g_Commit then
570 If mod(l_interface_csr%rowcount, g_CommitRows) = 0 then
571 COMMIT WORK;
572 End if;
573 End if; -- periodic commits
574 End loop; -- scan interface table
575 If g_Commit then
576 COMMIT WORK;
577 End if; -- commit remaining rows
578 -- rescan table in case of commons pointing to new routings
579 l_phase := l_phase + 1;
580 End loop; -- phase
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582 EXCEPTION
583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 WHEN OTHERS THEN
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
588 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
589 END IF;
590 END ImportHeader;
591
592 PROCEDURE ImportOperation(x_return_status IN OUT NOCOPY VARCHAR2) is
593 cursor l_interface_csr is
594 Select rowid,
595 operation_sequence_id,
596 routing_sequence_id,
597 operation_seq_num,
598 standard_operation_id,
599 department_id,
600 operation_lead_time_percent,
601 minimum_transfer_quantity,
602 count_point_type,
603 operation_description,
604 effectivity_date,
605 disable_date,
606 backflush_flag,
607 option_dependent_flag,
608 attribute_category,
609 attribute1,
610 attribute2,
611 attribute3,
612 attribute4,
613 attribute5,
614 attribute6,
615 attribute7,
616 attribute8,
617 attribute9,
618 attribute10,
619 attribute11,
620 attribute12,
621 attribute13,
622 attribute14,
623 attribute15,
624 assembly_item_id,
625 organization_id,
626 alternate_routing_designator,
627 organization_code,
628 assembly_item_number,
629 department_code,
630 operation_code,
631 resource_id1,
632 resource_id2,
633 resource_id3,
634 resource_code1,
635 resource_code2,
636 resource_code3,
637 transaction_type,
638 new_operation_seq_num,
639 new_effectivity_date,
640 operation_type,
641 reference_flag,
642 process_op_seq_id,
643 line_op_seq_id,
644 yield,
645 cumulative_yield,
646 reverse_cumulative_yield,
647 labor_time_calc,
648 machine_time_calc,
649 total_time_calc,
650 labor_time_user,
651 machine_time_user,
652 total_time_user,
653 net_planning_percent,
654 include_in_rollup,
655 operation_yield_enabled
656 From bom_op_sequences_interface
657 Where process_flag = 1
658 And (g_AllOrgs = g_yes
659 or organization_id = g_OrgId
660 or organization_code = g_OrgCode
661 );
662 l_phase NUMBER := 1; -- scan table twice in case events point to new parents
663 l_return_status VARCHAR2(1);
664 l_msg_count NUMBER;
665 l_msg_data VARCHAR2(2000);
666 l_msg_name VARCHAR2(80);
667 l_msg_app VARCHAR2(3);
668 l_DelGrpFound BOOLEAN := false;
669 l_ret_code NUMBER := 0;
670 cursor l_OprDeleteGroup_csr is
671 Select delete_group_name,
672 description
673 From bom_interface_delete_groups
674 Where upper(entity_name) = G_OprDelEntity
675 And rownum = 1;
676 cursor l_Transaction_csr is
677 Select mtl_system_items_interface_s.nextval transaction_id
678 From dual;
679 l_api_name CONSTANT VARCHAR2(30) := 'ImportOperation';
680 Begin
681 While l_phase <= 2 loop -- two passes
682 For l_interface_rec in l_interface_csr loop
683 Declare
684 l_operation_rec BOM_Operation_PVT.operation_rec_type;
685 Begin -- nested block within loop
686 if (upper(l_interface_rec.transaction_type) = 'CREATE') then
687 l_interface_rec.transaction_type := G_Insert ;
688 else
689 l_interface_rec.transaction_type := upper(l_interface_rec.transaction_type);
690 end if ;
691
692 If l_interface_rec.operation_sequence_id = G_NullNum then
693 l_operation_rec.operation_sequence_id := null;
694 Elsif l_interface_rec.transaction_type = G_Update then
695 l_operation_rec.operation_sequence_id :=
696 nvl(l_interface_rec.operation_sequence_id, Fnd_Api.G_Miss_Num);
697 Else
698 l_operation_rec.operation_sequence_id :=
699 l_interface_rec.operation_sequence_id;
700 End if;
701
702 If l_interface_rec.routing_sequence_id = G_NullNum then
703 l_operation_rec.routing_sequence_id := null;
704 Elsif l_interface_rec.transaction_type = G_Update then
705 l_operation_rec.routing_sequence_id :=
706 nvl(l_interface_rec.routing_sequence_id, Fnd_Api.G_Miss_Num);
707 Else
708 l_operation_rec.routing_sequence_id :=
709 l_interface_rec.routing_sequence_id;
710 End if;
711
712 If l_interface_rec.assembly_item_id = G_NullNum then
713 l_operation_rec.assembly_item_id := null;
714 Elsif l_interface_rec.transaction_type = G_Update then
715 l_operation_rec.assembly_item_id :=
716 nvl(l_interface_rec.assembly_item_id, Fnd_Api.G_Miss_Num);
717 Else
718 l_operation_rec.assembly_item_id := l_interface_rec.assembly_item_id;
719 End if;
720
721 If l_interface_rec.assembly_item_number = G_NullChar then
722 l_operation_rec.assembly_item_number := null;
723 Elsif l_interface_rec.transaction_type = G_Update then
724 l_operation_rec.assembly_item_number :=
725 nvl(l_interface_rec.assembly_item_number, Fnd_Api.G_Miss_Char);
726 Else
727 l_operation_rec.assembly_item_number :=
728 l_interface_rec.assembly_item_number;
729 End if;
730
731 If l_interface_rec.organization_id = G_NullNum then
732 l_operation_rec.organization_id := null;
733 Elsif l_interface_rec.transaction_type = G_Update then
734 l_operation_rec.organization_id :=
735 nvl(l_interface_rec.organization_id, Fnd_Api.G_Miss_Num);
736 Else
737 l_operation_rec.organization_id := l_interface_rec.organization_id;
738 End if;
739
740 If l_interface_rec.organization_code = G_NullChar then
741 l_operation_rec.organization_code := null;
742 Elsif l_interface_rec.transaction_type = G_Update then
743 l_operation_rec.organization_code :=
744 nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
745 Else
746 l_operation_rec.organization_code :=
747 l_interface_rec.organization_code;
748 End if;
749
750 If l_interface_rec.alternate_routing_designator = G_NullChar then
751 l_operation_rec.alternate_routing_designator := null;
752 Elsif l_interface_rec.transaction_type = G_Update then
753 l_operation_rec.alternate_routing_designator :=
754 nvl(l_interface_rec.alternate_routing_designator,
755 Fnd_Api.G_Miss_Char);
756 Else
757 l_operation_rec.alternate_routing_designator :=
758 l_interface_rec.alternate_routing_designator;
759 End if;
760
761 If l_interface_rec.operation_seq_num = G_NullNum then
762 l_operation_rec.operation_seq_num := null;
763 Elsif l_interface_rec.transaction_type = G_Update then
764 l_operation_rec.operation_seq_num :=
765 nvl(l_interface_rec.operation_seq_num, Fnd_Api.G_Miss_Num);
766 Else
767 l_operation_rec.operation_seq_num :=
768 l_interface_rec.operation_seq_num;
769 End if;
770
771 If l_interface_rec.new_operation_seq_num = G_NullNum then
772 l_operation_rec.new_operation_seq_num := null;
773 Elsif l_interface_rec.transaction_type = G_Update then
774 l_operation_rec.new_operation_seq_num :=
775 nvl(l_interface_rec.new_operation_seq_num, Fnd_Api.G_Miss_Num);
776 Else
777 l_operation_rec.new_operation_seq_num :=
778 l_interface_rec.new_operation_seq_num;
779 End if;
780
781 If l_interface_rec.standard_operation_id = G_NullNum then
782 l_operation_rec.standard_operation_id := null;
783 Elsif l_interface_rec.transaction_type = G_Update then
784 l_operation_rec.standard_operation_id :=
785 nvl(l_interface_rec.standard_operation_id, Fnd_Api.G_Miss_Num);
786 Else
787 l_operation_rec.standard_operation_id :=
788 l_interface_rec.standard_operation_id;
789 End if;
790
791 If l_interface_rec.operation_code = G_NullChar then
792 l_operation_rec.operation_code := null;
793 Elsif l_interface_rec.transaction_type = G_Update then
794 l_operation_rec.operation_code :=
795 nvl(l_interface_rec.operation_code, Fnd_Api.G_Miss_Char);
796 Else
797 l_operation_rec.operation_code := l_interface_rec.operation_code;
798 End if;
799
800 If l_interface_rec.department_id = G_NullNum then
801 l_operation_rec.department_id := null;
802 Elsif l_interface_rec.transaction_type = G_Update then
803 l_operation_rec.department_id :=
804 nvl(l_interface_rec.department_id, Fnd_Api.G_Miss_Num);
805 Else
806 l_operation_rec.department_id := l_interface_rec.department_id;
807 End if;
808
809 If l_interface_rec.department_code = G_NullChar then
810 l_operation_rec.department_code := null;
811 Elsif l_interface_rec.transaction_type = G_Update then
812 l_operation_rec.department_code :=
813 nvl(l_interface_rec.department_code, Fnd_Api.G_Miss_Char);
814 Else
815 l_operation_rec.department_code := l_interface_rec.department_code;
816 End if;
817
818 If l_interface_rec.operation_lead_time_percent = G_NullNum then
819 l_operation_rec.operation_lead_time_percent := null;
820 Elsif l_interface_rec.transaction_type = G_Update then
821 l_operation_rec.operation_lead_time_percent :=
822 nvl(l_interface_rec.operation_lead_time_percent,
823 Fnd_Api.G_Miss_Num);
824 Else
825 l_operation_rec.operation_lead_time_percent :=
826 l_interface_rec.operation_lead_time_percent;
827 End if;
828
829 If l_interface_rec.minimum_transfer_quantity = G_NullNum then
830 l_operation_rec.minimum_transfer_quantity := null;
831 Elsif l_interface_rec.transaction_type = G_Update then
832 l_operation_rec.minimum_transfer_quantity :=
833 nvl(l_interface_rec.minimum_transfer_quantity, Fnd_Api.G_Miss_Num);
834 Else
835 l_operation_rec.minimum_transfer_quantity :=
836 NVL(l_interface_rec.minimum_transfer_quantity, 0);
837 End if;
838
839 If l_interface_rec.count_point_type = G_NullNum then
840 l_operation_rec.count_point_type := null;
841 Elsif l_interface_rec.transaction_type = G_Update then
842 l_operation_rec.count_point_type :=
843 nvl(l_interface_rec.count_point_type, Fnd_Api.G_Miss_Num);
844 Else
845 l_operation_rec.count_point_type := l_interface_rec.count_point_type;
846 End if;
847
848 If l_interface_rec.operation_description = G_NullChar then
849 l_operation_rec.operation_description := null;
850 Elsif l_interface_rec.transaction_type = G_Update then
851 l_operation_rec.operation_description :=
852 nvl(l_interface_rec.operation_description, Fnd_Api.G_Miss_Char);
853 Else
854 l_operation_rec.operation_description :=
855 l_interface_rec.operation_description;
856 End if;
857
858 If l_interface_rec.effectivity_date = G_NullDate then
859 l_operation_rec.effectivity_date := null;
860 Elsif l_interface_rec.transaction_type = G_Update then
861 l_operation_rec.effectivity_date :=
862 nvl(l_interface_rec.effectivity_date, Fnd_Api.G_Miss_Date);
863 Else
864 l_operation_rec.effectivity_date := l_interface_rec.effectivity_date;
865 End if;
866
867 If l_interface_rec.new_effectivity_date = G_NullDate then
868 l_operation_rec.new_effectivity_date := null;
869 Elsif l_interface_rec.transaction_type = G_Update then
870 l_operation_rec.new_effectivity_date :=
871 nvl(l_interface_rec.new_effectivity_date, Fnd_Api.G_Miss_Date);
872 Else
873 l_operation_rec.new_effectivity_date :=
874 l_interface_rec.new_effectivity_date;
875 End if;
876
877 If l_interface_rec.disable_date = G_NullDate then
878 l_operation_rec.disable_date := null;
879 Elsif l_interface_rec.transaction_type = G_Update then
880 l_operation_rec.disable_date :=
881 nvl(l_interface_rec.disable_date, Fnd_Api.G_Miss_Date);
882 Else
883 l_operation_rec.disable_date := l_interface_rec.disable_date;
884 End if;
885
886 If l_interface_rec.backflush_flag = G_NullNum then
887 l_operation_rec.backflush_flag := null;
888 Elsif l_interface_rec.transaction_type = G_Update then
889 l_operation_rec.backflush_flag :=
890 nvl(l_interface_rec.backflush_flag, Fnd_Api.G_Miss_Num);
891 Else
892 l_operation_rec.backflush_flag := l_interface_rec.backflush_flag;
893 End if;
894
895 If l_interface_rec.option_dependent_flag = G_NullNum then
896 l_operation_rec.option_dependent_flag := null;
897 Elsif l_interface_rec.transaction_type = G_Update then
898 l_operation_rec.option_dependent_flag :=
899 nvl(l_interface_rec.option_dependent_flag, Fnd_Api.G_Miss_Num);
900 Else
901 l_operation_rec.option_dependent_flag :=
902 l_interface_rec.option_dependent_flag;
903 End if;
904
905 If l_interface_rec.attribute_category = G_NullChar then
906 l_operation_rec.attribute_category := null;
907 Elsif l_interface_rec.transaction_type = G_Update then
908 l_operation_rec.attribute_category :=
909 nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
910 Else
911 l_operation_rec.attribute_category :=
912 l_interface_rec.attribute_category;
913 End if;
914
915 If l_interface_rec.attribute1 = G_NullChar then
916 l_operation_rec.attribute1 := null;
917 Elsif l_interface_rec.transaction_type = G_Update then
918 l_operation_rec.attribute1 :=
919 nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
920 Else
921 l_operation_rec.attribute1 := l_interface_rec.attribute1;
922 End if;
923
924 If l_interface_rec.attribute2 = G_NullChar then
925 l_operation_rec.attribute2 := null;
926 Elsif l_interface_rec.transaction_type = G_Update then
927 l_operation_rec.attribute2 :=
928 nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
929 Else
930 l_operation_rec.attribute2 := l_interface_rec.attribute2;
931 End if;
932
933 If l_interface_rec.attribute3 = G_NullChar then
934 l_operation_rec.attribute3 := null;
935 Elsif l_interface_rec.transaction_type = G_Update then
936 l_operation_rec.attribute3 :=
937 nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
938 Else
939 l_operation_rec.attribute3 := l_interface_rec.attribute3;
940 End if;
941
942 If l_interface_rec.attribute4 = G_NullChar then
943 l_operation_rec.attribute4 := null;
944 Elsif l_interface_rec.transaction_type = G_Update then
945 l_operation_rec.attribute4 :=
946 nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
947 Else
948 l_operation_rec.attribute4 := l_interface_rec.attribute4;
949 End if;
950
951 If l_interface_rec.attribute5 = G_NullChar then
952 l_operation_rec.attribute5 := null;
953 Elsif l_interface_rec.transaction_type = G_Update then
954 l_operation_rec.attribute5 :=
955 nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
956 Else
957 l_operation_rec.attribute5 := l_interface_rec.attribute5;
958 End if;
959
960 If l_interface_rec.attribute6 = G_NullChar then
961 l_operation_rec.attribute6 := null;
962 Elsif l_interface_rec.transaction_type = G_Update then
963 l_operation_rec.attribute6 :=
964 nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
965 Else
966 l_operation_rec.attribute6 := l_interface_rec.attribute6;
967 End if;
968
969 If l_interface_rec.attribute7 = G_NullChar then
970 l_operation_rec.attribute7 := null;
971 Elsif l_interface_rec.transaction_type = G_Update then
972 l_operation_rec.attribute7 :=
973 nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
974 Else
975 l_operation_rec.attribute7 := l_interface_rec.attribute7;
976 End if;
977
978 If l_interface_rec.attribute8 = G_NullChar then
979 l_operation_rec.attribute8 := null;
980 Elsif l_interface_rec.transaction_type = G_Update then
981 l_operation_rec.attribute8 :=
982 nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
983 Else
984 l_operation_rec.attribute8 := l_interface_rec.attribute8;
985 End if;
986
987 If l_interface_rec.attribute9 = G_NullChar then
988 l_operation_rec.attribute9 := null;
989 Elsif l_interface_rec.transaction_type = G_Update then
990 l_operation_rec.attribute9 :=
991 nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
992 Else
993 l_operation_rec.attribute9 := l_interface_rec.attribute9;
994 End if;
995
996 If l_interface_rec.attribute10 = G_NullChar then
997 l_operation_rec.attribute10 := null;
998 Elsif l_interface_rec.transaction_type = G_Update then
999 l_operation_rec.attribute10 :=
1000 nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
1001 Else
1002 l_operation_rec.attribute10 := l_interface_rec.attribute10;
1003 End if;
1004
1005 If l_interface_rec.attribute11 = G_NullChar then
1006 l_operation_rec.attribute11 := null;
1007 Elsif l_interface_rec.transaction_type = G_Update then
1008 l_operation_rec.attribute11 :=
1009 nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
1010 Else
1011 l_operation_rec.attribute11 := l_interface_rec.attribute11;
1012 End if;
1013
1014 If l_interface_rec.attribute12 = G_NullChar then
1015 l_operation_rec.attribute12 := null;
1016 Elsif l_interface_rec.transaction_type = G_Update then
1017 l_operation_rec.attribute12 :=
1018 nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
1019 Else
1020 l_operation_rec.attribute12 := l_interface_rec.attribute12;
1021 End if;
1022
1023 If l_interface_rec.attribute13 = G_NullChar then
1024 l_operation_rec.attribute13 := null;
1025 Elsif l_interface_rec.transaction_type = G_Update then
1026 l_operation_rec.attribute13 :=
1027 nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
1028 Else
1029 l_operation_rec.attribute13 := l_interface_rec.attribute13;
1030 End if;
1031
1032 If l_interface_rec.attribute14 = G_NullChar then
1033 l_operation_rec.attribute14 := null;
1034 Elsif l_interface_rec.transaction_type = G_Update then
1035 l_operation_rec.attribute14 :=
1036 nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
1037 Else
1038 l_operation_rec.attribute14 := l_interface_rec.attribute14;
1039 End if;
1040
1041 If l_interface_rec.attribute15 = G_NullChar then
1042 l_operation_rec.attribute15 := null;
1043 Elsif l_interface_rec.transaction_type = G_Update then
1044 l_operation_rec.attribute15 :=
1045 nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
1046 Else
1047 l_operation_rec.attribute15 := l_interface_rec.attribute15;
1048 End if;
1049
1050 If l_interface_rec.operation_type = G_NullNum then
1051 l_operation_rec.operation_type := null;
1052 Elsif l_interface_rec.transaction_type = G_Update then
1053 l_operation_rec.operation_type :=
1054 nvl(l_interface_rec.operation_type, Fnd_Api.G_Miss_Num);
1055 Else
1056 l_operation_rec.operation_type := l_interface_rec.operation_type;
1057 End if;
1058
1059 If l_interface_rec.reference_flag = G_NullNum then
1060 l_operation_rec.reference_flag := null;
1061 Elsif l_interface_rec.transaction_type = G_Update then
1062 l_operation_rec.reference_flag :=
1063 nvl(l_interface_rec.reference_flag, Fnd_Api.G_Miss_Num);
1064 Else
1065 l_operation_rec.reference_flag := l_interface_rec.reference_flag;
1066 End if;
1067
1068 If l_interface_rec.process_op_seq_id = G_NullNum then
1069 l_operation_rec.process_op_seq_id := null;
1070 Elsif l_interface_rec.transaction_type = G_Update then
1071 l_operation_rec.process_op_seq_id :=
1072 nvl(l_interface_rec.process_op_seq_id, Fnd_Api.G_Miss_Num);
1073 Else
1074 l_operation_rec.process_op_seq_id :=
1075 l_interface_rec.process_op_seq_id;
1076 End if;
1077
1078 If l_interface_rec.line_op_seq_id = G_NullNum then
1079 l_operation_rec.line_op_seq_id := null;
1080 Elsif l_interface_rec.transaction_type = G_Update then
1081 l_operation_rec.line_op_seq_id :=
1082 nvl(l_interface_rec.line_op_seq_id, Fnd_Api.G_Miss_Num);
1083 Else
1084 l_operation_rec.line_op_seq_id := l_interface_rec.line_op_seq_id;
1085 End if;
1086
1087 If l_interface_rec.yield = G_NullNum then
1088 l_operation_rec.yield := null;
1089 Elsif l_interface_rec.transaction_type = G_Update then
1090 l_operation_rec.yield :=
1091 nvl(l_interface_rec.yield, Fnd_Api.G_Miss_Num);
1092 Else
1093 l_operation_rec.yield := l_interface_rec.yield;
1094 End if;
1095
1096 If l_interface_rec.cumulative_yield = G_NullNum then
1097 l_operation_rec.cumulative_yield := null;
1098 Elsif l_interface_rec.transaction_type = G_Update then
1099 l_operation_rec.cumulative_yield :=
1100 nvl(l_interface_rec.cumulative_yield, Fnd_Api.G_Miss_Num);
1101 Else
1102 l_operation_rec.cumulative_yield := l_interface_rec.cumulative_yield;
1103 End if;
1104
1105 If l_interface_rec.reverse_cumulative_yield = G_NullNum then
1106 l_operation_rec.reverse_cumulative_yield := null;
1107 Elsif l_interface_rec.transaction_type = G_Update then
1108 l_operation_rec.reverse_cumulative_yield :=
1109 nvl(l_interface_rec.reverse_cumulative_yield, Fnd_Api.G_Miss_Num);
1110 Else
1111 l_operation_rec.reverse_cumulative_yield :=
1112 l_interface_rec.reverse_cumulative_yield;
1113 End if;
1114
1115 If l_interface_rec.labor_time_calc = G_NullNum then
1116 l_operation_rec.labor_time_calc := null;
1117 Elsif l_interface_rec.transaction_type = G_Update then
1118 l_operation_rec.labor_time_calc :=
1119 nvl(l_interface_rec.labor_time_calc, Fnd_Api.G_Miss_Num);
1120 Else
1121 l_operation_rec.labor_time_calc := l_interface_rec.labor_time_calc;
1122 End if;
1123
1124 If l_interface_rec.machine_time_calc = G_NullNum then
1125 l_operation_rec.machine_time_calc := null;
1126 Elsif l_interface_rec.transaction_type = G_Update then
1127 l_operation_rec.machine_time_calc :=
1128 nvl(l_interface_rec.machine_time_calc, Fnd_Api.G_Miss_Num);
1129 Else
1130 l_operation_rec.machine_time_calc :=
1131 l_interface_rec.machine_time_calc;
1132 End if;
1133
1134 If l_interface_rec.total_time_calc = G_NullNum then
1135 l_operation_rec.total_time_calc := null;
1136 Elsif l_interface_rec.transaction_type = G_Update then
1137 l_operation_rec.total_time_calc :=
1138 nvl(l_interface_rec.total_time_calc, Fnd_Api.G_Miss_Num);
1139 Else
1140 l_operation_rec.total_time_calc := l_interface_rec.total_time_calc;
1141 End if;
1142
1143 If l_interface_rec.labor_time_user = G_NullNum then
1144 l_operation_rec.labor_time_user := null;
1145 Elsif l_interface_rec.transaction_type = G_Update then
1146 l_operation_rec.labor_time_user :=
1147 nvl(l_interface_rec.labor_time_user, Fnd_Api.G_Miss_Num);
1148 Else
1149 l_operation_rec.labor_time_user := l_interface_rec.labor_time_user;
1150 End if;
1151
1152 If l_interface_rec.machine_time_user = G_NullNum then
1153 l_operation_rec.machine_time_user := null;
1154 Elsif l_interface_rec.transaction_type = G_Update then
1155 l_operation_rec.machine_time_user :=
1156 nvl(l_interface_rec.machine_time_user, Fnd_Api.G_Miss_Num);
1157 Else
1158 l_operation_rec.machine_time_user :=
1159 l_interface_rec.machine_time_user;
1160 End if;
1161
1162 If l_interface_rec.total_time_user = G_NullNum then
1163 l_operation_rec.total_time_user := null;
1164 Elsif l_interface_rec.transaction_type = G_Update then
1165 l_operation_rec.total_time_user :=
1166 nvl(l_interface_rec.total_time_user, Fnd_Api.G_Miss_Num);
1167 Else
1168 l_operation_rec.total_time_user := l_interface_rec.total_time_calc;
1169 End if;
1170
1171 If l_interface_rec.net_planning_percent = G_NullNum then
1172 l_operation_rec.net_planning_percent := null;
1173 Elsif l_interface_rec.transaction_type = G_Update then
1174 l_operation_rec.net_planning_percent :=
1175 nvl(l_interface_rec.net_planning_percent, Fnd_Api.G_Miss_Num);
1176 Else
1177 l_operation_rec.net_planning_percent :=
1178 l_interface_rec.net_planning_percent;
1179 End if;
1180
1181 If l_interface_rec.include_in_rollup = G_NullNum then
1182 l_operation_rec.include_in_rollup := null;
1183 Elsif l_interface_rec.transaction_type = G_Update then
1184 l_operation_rec.include_in_rollup :=
1185 nvl(l_interface_rec.include_in_rollup, Fnd_Api.G_Miss_Num);
1186 Else
1187 l_operation_rec.include_in_rollup := l_interface_rec.include_in_rollup;
1188 End if;
1189
1190 If l_interface_rec.operation_yield_enabled = G_NullNum then
1191 l_operation_rec.operation_yield_enabled := null;
1192 Elsif l_interface_rec.transaction_type = G_Update then
1193 l_operation_rec.operation_yield_enabled :=
1194 nvl(l_interface_rec.operation_yield_enabled, Fnd_Api.G_Miss_Num);
1195 Else
1196 l_operation_rec.operation_yield_enabled := l_interface_rec.operation_yield_enabled;
1197 End if;
1198
1199 If l_interface_rec.transaction_type = G_Insert then
1200
1201 BOM_Operation_PVT.CreateOperation(
1202 p_api_version => 1.0,
1203 p_init_msg_list => FND_API.G_TRUE,
1204 p_commit => FND_API.G_FALSE,
1205 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1206 x_return_status => l_return_status,
1207 x_msg_count => l_msg_count,
1208 x_msg_data => l_msg_data,
1209 p_operation_rec => l_operation_rec,
1210 x_operation_rec => l_operation_rec
1211 );
1212
1213 -- insert given operation resources into interface table
1214 If l_return_status = FND_API.G_RET_STS_SUCCESS then
1215 If nvl(l_interface_rec.resource_code1, G_NullChar) <> G_NullChar
1216 or nvl(l_interface_rec.resource_id1, G_NullNum) <> G_NullNum then
1217 insert into bom_op_resources_interface(
1218 OPERATION_SEQUENCE_ID,
1219 ASSEMBLY_ITEM_ID,
1220 ASSEMBLY_ITEM_NUMBER,
1221 ORGANIZATION_ID,
1222 ORGANIZATION_CODE,
1223 ALTERNATE_ROUTING_DESIGNATOR,
1224 OPERATION_SEQ_NUM,
1225 EFFECTIVITY_DATE,
1226 RESOURCE_SEQ_NUM,
1227 RESOURCE_ID,
1228 RESOURCE_CODE,
1229 PROCESS_FLAG,
1230 TRANSACTION_TYPE)
1231 values (
1232 l_operation_rec.operation_sequence_id,
1233 l_operation_rec.assembly_item_id,
1234 l_operation_rec.assembly_item_number,
1235 l_operation_rec.organization_id,
1236 l_operation_rec.organization_code,
1237 l_operation_rec.alternate_routing_designator,
1238 l_operation_rec.operation_seq_num,
1239 l_operation_rec.effectivity_date,
1240 10,
1241 l_interface_rec.resource_id1,
1242 l_interface_rec.resource_code1,
1243 1,
1244 G_Insert);
1245 end if; -- resource 1
1246 If nvl(l_interface_rec.resource_code2, G_NullChar) <> G_NullChar
1247 or nvl(l_interface_rec.resource_id2, G_NullNum) <> G_NullNum then
1248 insert into bom_op_resources_interface (
1249 OPERATION_SEQUENCE_ID,
1250 ASSEMBLY_ITEM_ID,
1251 ASSEMBLY_ITEM_NUMBER,
1252 ORGANIZATION_ID,
1253 ORGANIZATION_CODE,
1254 ALTERNATE_ROUTING_DESIGNATOR,
1255 OPERATION_SEQ_NUM,
1256 EFFECTIVITY_DATE,
1257 RESOURCE_SEQ_NUM,
1258 RESOURCE_ID,
1259 RESOURCE_CODE,
1260 PROCESS_FLAG,
1261 TRANSACTION_TYPE)
1262 values (
1263 l_operation_rec.operation_sequence_id,
1264 l_operation_rec.assembly_item_id,
1265 l_operation_rec.assembly_item_number,
1266 l_operation_rec.organization_id,
1267 l_operation_rec.organization_code,
1268 l_operation_rec.alternate_routing_designator,
1269 l_operation_rec.operation_seq_num,
1270 l_operation_rec.effectivity_date,
1271 20,
1272 l_interface_rec.resource_id2,
1273 l_interface_rec.resource_code2,
1274 1,
1275 G_Insert);
1276 end if; -- resource 2
1277 If nvl(l_interface_rec.resource_code3, G_NullChar) <> G_NullChar
1278 or nvl(l_interface_rec.resource_id3, G_NullNum) <> G_NullNum then
1279 insert into bom_op_resources_interface (
1280 OPERATION_SEQUENCE_ID,
1281 ASSEMBLY_ITEM_ID,
1282 ASSEMBLY_ITEM_NUMBER,
1283 ORGANIZATION_ID,
1284 ORGANIZATION_CODE,
1285 ALTERNATE_ROUTING_DESIGNATOR,
1286 OPERATION_SEQ_NUM,
1287 EFFECTIVITY_DATE,
1288 RESOURCE_SEQ_NUM,
1289 RESOURCE_ID,
1290 RESOURCE_CODE,
1291 PROCESS_FLAG,
1292 TRANSACTION_TYPE)
1293 values(
1294 l_operation_rec.operation_sequence_id,
1295 l_operation_rec.assembly_item_id,
1296 l_operation_rec.assembly_item_number,
1297 l_operation_rec.organization_id,
1298 l_operation_rec.organization_code,
1299 l_operation_rec.alternate_routing_designator,
1300 l_operation_rec.operation_seq_num,
1301 l_operation_rec.effectivity_date,
1302 30,
1303 l_interface_rec.resource_id3,
1304 l_interface_rec.resource_code3,
1305 1,
1306 G_Insert);
1307 end if; -- resource 3
1308 end if; -- denormalized resources
1309
1310 Elsif l_interface_rec.transaction_type = G_Update then
1311
1312 BOM_Operation_PVT.UpdateOperation(
1313 p_api_version => 1.0,
1314 p_init_msg_list => FND_API.G_TRUE,
1315 p_commit => FND_API.G_FALSE,
1316 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1317 x_return_status => l_return_status,
1318 x_msg_count => l_msg_count,
1319 x_msg_data => l_msg_data,
1320 p_operation_rec => l_operation_rec,
1321 x_operation_rec => l_operation_rec);
1322
1323 Elsif l_interface_rec.transaction_type = G_Delete then
1324
1325 l_DelGrpFound := false;
1326 For l_DelGrp_rec in l_OprDeleteGroup_csr loop
1327 l_DelGrpFound := true;
1328 BOM_Operation_Pvt.DeleteOperation(
1329 p_api_version => 1.0,
1330 p_init_msg_list => FND_API.G_TRUE,
1331 p_commit => FND_API.G_FALSE,
1332 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1333 x_return_status => l_return_status,
1334 x_msg_count => l_msg_count,
1335 x_msg_data => l_msg_data,
1336 p_delete_group => l_DelGrp_rec.delete_group_name,
1337 p_description => l_DelGrp_rec.description,
1338 p_operation_rec => l_operation_rec,
1339 x_operation_rec => l_operation_rec
1340 );
1341 End loop; -- delete operation
1342 If not l_DelGrpFound then
1343 Fnd_Message.Set_Name('BOM', 'BOM_DELETE_GROUP_NULL');
1344 l_return_status := FND_API.G_RET_STS_ERROR;
1345 FND_MSG_PUB.Initialize;
1346 FND_MSG_PUB.Add;
1347 FND_MSG_PUB.Count_And_Get(
1348 p_count => l_msg_count,
1349 p_data => l_msg_data
1350 );
1351 End if; -- Delete group unspecified
1352 End if; -- insert, update and delete
1353
1354 If l_return_status = Fnd_Api.G_RET_STS_SUCCESS then
1355 If g_DeleteRows then
1356 Delete from bom_op_sequences_interface
1357 Where rowid = l_interface_rec.rowid;
1358 Else
1359 Update bom_op_sequences_interface
1360 Set process_flag = 7,
1361 transaction_id = mtl_system_items_interface_s.nextval,
1362 request_id = nvl(request_id,g_RequestId),
1363 program_id = nvl(program_id,g_ProgramId),
1364 program_application_id = nvl(program_application_id,g_ApplicationId),
1365 program_update_date = nvl(program_update_date,sysdate),
1366 created_by = nvl(created_by,g_UserId),
1367 last_updated_by = nvl(last_updated_by,g_UserId),
1368 creation_date = nvl(creation_date,sysdate),
1369 last_update_date = nvl(last_update_date,sysdate),
1370 last_update_login = nvl(last_update_login,g_LoginId)
1371 Where rowid = l_interface_rec.rowid;
1372 End if;
1373 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR then
1374 If l_phase = 2 then
1375 If l_msg_count > 1 then
1376 l_msg_data := FND_MSG_PUB.Get;
1377 End if;
1378 Fnd_Message.Parse_Encoded(
1379 ENCODED_MESSAGE => l_msg_data,
1380 APP_SHORT_NAME => l_msg_app,
1381 MESSAGE_NAME => l_msg_name);
1382 For l_transaction_rec in l_Transaction_csr loop
1383 l_ret_code := INVPUOPI.mtl_log_interface_err(
1384 org_id => g_OrgId,
1385 user_id => g_UserId,
1386 login_id => g_LoginId,
1387 prog_appid => g_ApplicationId,
1388 prog_id => g_ProgramId,
1389 req_id => g_RequestId,
1390 trans_id => l_transaction_rec.transaction_id,
1391 error_text => l_msg_data,
1392 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1393 msg_name => l_msg_name,
1394 err_text => l_msg_data);
1395 If l_ret_code <> 0 then
1396 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1397 end if;
1398 Update bom_op_sequences_interface
1399 Set process_flag = 3,
1400 transaction_id = l_transaction_rec.transaction_id
1401 Where rowid = l_interface_rec.rowid;
1402 End loop; -- log error
1403 End if; -- final phase
1404 Elsif l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR then
1405 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1406 End if; -- process return status
1407 End; -- nested block
1408
1409 If g_Commit then
1410 If mod(l_interface_csr%rowcount, g_CommitRows) = 0 then
1411 COMMIT WORK;
1412 End if;
1413 End if; -- periodic commits
1414 End loop; -- scan interface table
1415 If g_Commit then
1416 COMMIT WORK;
1417 End if; -- commit remaining rows
1418 -- rescan table in case of new event parents
1419 l_phase := l_phase + 1;
1420 End loop; -- phase
1421 x_return_status := FND_API.G_RET_STS_SUCCESS;
1422 EXCEPTION
1423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1425 WHEN OTHERS THEN
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1428 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1429 END IF;
1430 END ImportOperation;
1431
1432 PROCEDURE ImportResource(x_return_status IN OUT NOCOPY VARCHAR2) is
1433 cursor l_interface_csr is
1434 Select rowid,
1435 operation_sequence_id,
1436 routing_sequence_id,
1437 assembly_item_id,
1438 assembly_item_number,
1439 organization_id,
1440 organization_code,
1441 alternate_routing_designator,
1442 operation_seq_num,
1443 effectivity_date,
1444 resource_seq_num,
1445 new_resource_seq_num,
1446 resource_id,
1447 resource_code,
1448 activity_id,
1449 activity,
1450 standard_rate_flag,
1451 assigned_units,
1452 usage_rate_or_amount,
1453 usage_rate_or_amount_inverse,
1454 basis_type,
1455 schedule_flag,
1456 resource_offset_percent,
1457 autocharge_type,
1458 attribute_category,
1459 attribute1,
1460 attribute2,
1461 attribute3,
1462 attribute4,
1463 attribute5,
1464 attribute6,
1465 attribute7,
1466 attribute8,
1467 attribute9,
1468 attribute10,
1469 attribute11,
1470 attribute12,
1471 attribute13,
1472 attribute14,
1473 attribute15,
1474 transaction_type,
1475 principle_flag, -- 2514018
1476 schedule_seq_num -- 2514018
1477 From bom_op_resources_interface
1478 Where process_flag = 1
1479 And (g_AllOrgs = g_yes
1480 or organization_id = g_OrgId
1481 or organization_code = g_OrgCode
1482 );
1483 l_phase NUMBER := 1; -- scan table twice in case inserting and updating same
1484 -- resource
1485 l_return_status VARCHAR2(1);
1486 l_msg_count NUMBER;
1487 l_msg_data VARCHAR2(2000);
1488 l_msg_name VARCHAR2(80);
1489 l_msg_app VARCHAR2(3);
1490 l_ret_code NUMBER := 0;
1491 cursor l_Transaction_csr is
1492 Select mtl_system_items_interface_s.nextval transaction_id
1493 From dual;
1494 l_api_name CONSTANT VARCHAR2(30) := 'ImportResource';
1495 Begin
1496 While l_phase <= 2 loop -- two passes
1497 For l_interface_rec in l_interface_csr loop
1498 Declare
1499 l_resource_rec Bom_OpResource_Pvt.resource_rec_type;
1500 Begin -- nested block within loop
1501 if (upper(l_interface_rec.transaction_type) = 'CREATE') then
1502 l_interface_rec.transaction_type := G_Insert ;
1503 else
1504 l_interface_rec.transaction_type := upper(l_interface_rec.transaction_type);
1505 end if ;
1506
1507 If l_interface_rec.operation_sequence_id = G_NullNum then
1508 l_resource_rec.operation_sequence_id := null;
1509 Elsif l_interface_rec.transaction_type = G_Update then
1510 l_resource_rec.operation_sequence_id :=
1511 nvl(l_interface_rec.operation_sequence_id, Fnd_Api.G_Miss_Num);
1512 Else
1513 l_resource_rec.operation_sequence_id :=
1514 l_interface_rec.operation_sequence_id;
1515 End if;
1516
1517 If l_interface_rec.routing_sequence_id = G_NullNum then
1518 l_resource_rec.routing_sequence_id := null;
1519 Elsif l_interface_rec.transaction_type = G_Update then
1520 l_resource_rec.routing_sequence_id :=
1521 nvl(l_interface_rec.routing_sequence_id, Fnd_Api.G_Miss_Num);
1522 Else
1523 l_resource_rec.routing_sequence_id :=
1524 l_interface_rec.routing_sequence_id;
1525 End if;
1526
1527 If l_interface_rec.assembly_item_id = G_NullNum then
1528 l_resource_rec.assembly_item_id := null;
1529 Elsif l_interface_rec.transaction_type = G_Update then
1530 l_resource_rec.assembly_item_id :=
1531 nvl(l_interface_rec.assembly_item_id, Fnd_Api.G_Miss_Num);
1532 Else
1533 l_resource_rec.assembly_item_id := l_interface_rec.assembly_item_id;
1534 End if;
1535
1536 If l_interface_rec.assembly_item_number = G_NullChar then
1537 l_resource_rec.assembly_item_number := null;
1538 Elsif l_interface_rec.transaction_type = G_Update then
1539 l_resource_rec.assembly_item_number :=
1540 nvl(l_interface_rec.assembly_item_number, Fnd_Api.G_Miss_Char);
1541 Else
1542 l_resource_rec.assembly_item_number :=
1543 l_interface_rec.assembly_item_number;
1544 End if;
1545
1546 If l_interface_rec.organization_id = G_NullNum then
1547 l_resource_rec.organization_id := null;
1548 Elsif l_interface_rec.transaction_type = G_Update then
1549 l_resource_rec.organization_id :=
1550 nvl(l_interface_rec.organization_id, Fnd_Api.G_Miss_Num);
1551 Else
1552 l_resource_rec.organization_id := l_interface_rec.organization_id;
1553 End if;
1554
1555 If l_interface_rec.organization_code = G_NullChar then
1556 l_resource_rec.organization_code := null;
1557 Elsif l_interface_rec.transaction_type = G_Update then
1558 l_resource_rec.organization_code :=
1559 nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
1560 Else
1561 l_resource_rec.organization_code := l_interface_rec.organization_code;
1562 End if;
1563
1564 If l_interface_rec.alternate_routing_designator = G_NullChar then
1565 l_resource_rec.alternate_routing_designator := null;
1566 Elsif l_interface_rec.transaction_type = G_Update then
1567 l_resource_rec.alternate_routing_designator :=
1568 nvl(l_interface_rec.alternate_routing_designator,
1569 Fnd_Api.G_Miss_Char);
1570 Else
1571 l_resource_rec.alternate_routing_designator :=
1572 l_interface_rec.alternate_routing_designator;
1573 End if;
1574
1575 If l_interface_rec.operation_seq_num = G_NullNum then
1576 l_resource_rec.operation_seq_num := null;
1577 Elsif l_interface_rec.transaction_type = G_Update then
1578 l_resource_rec.operation_seq_num :=
1579 nvl(l_interface_rec.operation_seq_num, Fnd_Api.G_Miss_Num);
1580 Else
1581 l_resource_rec.operation_seq_num :=
1582 l_interface_rec.operation_seq_num;
1583 End if;
1584
1585 If l_interface_rec.effectivity_date = G_NullDate then
1586 l_resource_rec.effectivity_date := null;
1587 Elsif l_interface_rec.transaction_type = G_Update then
1588 l_resource_rec.effectivity_date :=
1589 nvl(l_interface_rec.effectivity_date, Fnd_Api.G_Miss_Date);
1590 Else
1591 l_resource_rec.effectivity_date := l_interface_rec.effectivity_date;
1592 End if;
1593
1594 If l_interface_rec.resource_seq_num = G_NullNum then
1595 l_resource_rec.resource_seq_num := null;
1596 Elsif l_interface_rec.transaction_type = G_Update then
1597 l_resource_rec.resource_seq_num :=
1598 nvl(l_interface_rec.resource_seq_num, Fnd_Api.G_Miss_Num);
1599 Else
1600 l_resource_rec.resource_seq_num := l_interface_rec.resource_seq_num;
1601 End if;
1602
1603 If l_interface_rec.new_resource_seq_num = G_NullNum then
1604 l_resource_rec.new_resource_seq_num := null;
1605 Elsif l_interface_rec.transaction_type = G_Update then
1606 l_resource_rec.new_resource_seq_num :=
1607 nvl(l_interface_rec.new_resource_seq_num, Fnd_Api.G_Miss_Num);
1608 Else
1609 l_resource_rec.new_resource_seq_num :=
1610 l_interface_rec.new_resource_seq_num;
1611 End if;
1612
1613 If l_interface_rec.resource_id = G_NullNum then
1614 l_resource_rec.resource_id := null;
1615 Elsif l_interface_rec.transaction_type = G_Update then
1616 l_resource_rec.resource_id :=
1617 nvl(l_interface_rec.resource_id, Fnd_Api.G_Miss_Num);
1618 Else
1619 l_resource_rec.resource_id := l_interface_rec.resource_id;
1620 End if;
1621
1622 If l_interface_rec.resource_code = G_NullChar then
1623 l_resource_rec.resource_code := null;
1624 Elsif l_interface_rec.transaction_type = G_Update then
1625 l_resource_rec.resource_code :=
1626 nvl(l_interface_rec.resource_code, Fnd_Api.G_Miss_Char);
1627 Else
1628 l_resource_rec.resource_code := l_interface_rec.resource_code;
1629 End if;
1630
1631 If l_interface_rec.activity_id = G_NullNum then
1632 l_resource_rec.activity_id := null;
1633 Elsif l_interface_rec.transaction_type = G_Update then
1634 l_resource_rec.activity_id :=
1635 nvl(l_interface_rec.activity_id, Fnd_Api.G_Miss_Num);
1636 Else
1637 l_resource_rec.activity_id := l_interface_rec.activity_id;
1638 End if;
1639
1640 If l_interface_rec.activity = G_NullChar then
1641 l_resource_rec.activity := null;
1642 Elsif l_interface_rec.transaction_type = G_Update then
1643 l_resource_rec.activity :=
1644 nvl(l_interface_rec.activity, Fnd_Api.G_Miss_Char);
1645 Else
1646 l_resource_rec.activity := l_interface_rec.activity;
1647 End if;
1648
1649 If l_interface_rec.standard_rate_flag = G_NullNum then
1650 l_resource_rec.standard_rate_flag := null;
1651 Elsif l_interface_rec.transaction_type = G_Update then
1652 l_resource_rec.standard_rate_flag :=
1653 nvl(l_interface_rec.standard_rate_flag, Fnd_Api.G_Miss_Num);
1654 Else
1655 l_resource_rec.standard_rate_flag :=
1656 l_interface_rec.standard_rate_flag;
1657 End if;
1658
1659 If l_interface_rec.assigned_units = G_NullNum then
1660 l_resource_rec.assigned_units := null;
1661 Elsif l_interface_rec.transaction_type = G_Update then
1662 l_resource_rec.assigned_units :=
1663 nvl(l_interface_rec.assigned_units, Fnd_Api.G_Miss_Num);
1664 Else
1665 l_resource_rec.assigned_units := l_interface_rec.assigned_units;
1666 End if;
1667
1668 If l_interface_rec.usage_rate_or_amount = G_NullNum then
1669 l_resource_rec.usage_rate_or_amount := null;
1670 Elsif l_interface_rec.transaction_type = G_Update then
1671 l_resource_rec.usage_rate_or_amount :=
1672 nvl(l_interface_rec.usage_rate_or_amount, Fnd_Api.G_Miss_Num);
1673 Else
1674 l_resource_rec.usage_rate_or_amount :=
1675 l_interface_rec.usage_rate_or_amount;
1676 End if;
1677
1678 If l_interface_rec.usage_rate_or_amount_inverse = G_NullNum then
1679 l_resource_rec.usage_rate_or_amount_inverse := null;
1680 Elsif l_interface_rec.transaction_type = G_Update then
1681 l_resource_rec.usage_rate_or_amount_inverse :=
1682 nvl(l_interface_rec.usage_rate_or_amount_inverse,
1683 Fnd_Api.G_Miss_Num);
1684 Else
1685 l_resource_rec.usage_rate_or_amount_inverse :=
1686 l_interface_rec.usage_rate_or_amount_inverse;
1687 End if;
1688
1689 If l_interface_rec.basis_type = G_NullNum then
1690 l_resource_rec.basis_type := null;
1691 Elsif l_interface_rec.transaction_type = G_Update then
1692 l_resource_rec.basis_type :=
1693 nvl(l_interface_rec.basis_type, Fnd_Api.G_Miss_Num);
1694 Else
1695 l_resource_rec.basis_type := l_interface_rec.basis_type;
1696 End if;
1697
1698 If l_interface_rec.schedule_flag = G_NullNum then
1699 l_resource_rec.schedule_flag := null;
1700 Elsif l_interface_rec.transaction_type = G_Update then
1701 l_resource_rec.schedule_flag :=
1702 nvl(l_interface_rec.schedule_flag, Fnd_Api.G_Miss_Num);
1703 Else
1704 l_resource_rec.schedule_flag := l_interface_rec.schedule_flag;
1705 End if;
1706
1707 If l_interface_rec.resource_offset_percent = G_NullNum then
1708 l_resource_rec.resource_offset_percent := null;
1709 Elsif l_interface_rec.transaction_type = G_Update then
1710 l_resource_rec.resource_offset_percent :=
1711 nvl(l_interface_rec.resource_offset_percent, Fnd_Api.G_Miss_Num);
1712 Else
1713 l_resource_rec.resource_offset_percent :=
1714 l_interface_rec.resource_offset_percent;
1715 End if;
1716
1717 If l_interface_rec.autocharge_type = G_NullNum then
1718 l_resource_rec.autocharge_type := null;
1719 Elsif l_interface_rec.transaction_type = G_Update then
1720 l_resource_rec.autocharge_type :=
1721 nvl(l_interface_rec.autocharge_type, Fnd_Api.G_Miss_Num);
1722 Else
1723 l_resource_rec.autocharge_type := l_interface_rec.autocharge_type;
1724 End if;
1725
1726 If l_interface_rec.attribute_category = G_NullChar then
1727 l_resource_rec.attribute_category := null;
1728 Elsif l_interface_rec.transaction_type = G_Update then
1729 l_resource_rec.attribute_category :=
1730 nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
1731 Else
1732 l_resource_rec.attribute_category :=
1733 l_interface_rec.attribute_category;
1734 End if;
1735
1736 If l_interface_rec.attribute1 = G_NullChar then
1737 l_resource_rec.attribute1 := null;
1738 Elsif l_interface_rec.transaction_type = G_Update then
1739 l_resource_rec.attribute1 :=
1740 nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
1741 Else
1742 l_resource_rec.attribute1 := l_interface_rec.attribute1;
1743 End if;
1744
1745 If l_interface_rec.attribute2 = G_NullChar then
1746 l_resource_rec.attribute2 := null;
1747 Elsif l_interface_rec.transaction_type = G_Update then
1748 l_resource_rec.attribute2 :=
1749 nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
1750 Else
1751 l_resource_rec.attribute2 := l_interface_rec.attribute2;
1752 End if;
1753
1754 If l_interface_rec.attribute3 = G_NullChar then
1755 l_resource_rec.attribute3 := null;
1756 Elsif l_interface_rec.transaction_type = G_Update then
1757 l_resource_rec.attribute3 :=
1758 nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
1759 Else
1760 l_resource_rec.attribute3 := l_interface_rec.attribute3;
1761 End if;
1762
1763 If l_interface_rec.attribute4 = G_NullChar then
1764 l_resource_rec.attribute4 := null;
1765 Elsif l_interface_rec.transaction_type = G_Update then
1766 l_resource_rec.attribute4 :=
1767 nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
1768 Else
1769 l_resource_rec.attribute4 := l_interface_rec.attribute4;
1770 End if;
1771
1772 If l_interface_rec.attribute5 = G_NullChar then
1773 l_resource_rec.attribute5 := null;
1774 Elsif l_interface_rec.transaction_type = G_Update then
1775 l_resource_rec.attribute5 :=
1776 nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
1777 Else
1778 l_resource_rec.attribute5 := l_interface_rec.attribute5;
1779 End if;
1780
1781 If l_interface_rec.attribute6 = G_NullChar then
1782 l_resource_rec.attribute6 := null;
1783 Elsif l_interface_rec.transaction_type = G_Update then
1784 l_resource_rec.attribute6 :=
1785 nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
1786 Else
1787 l_resource_rec.attribute6 := l_interface_rec.attribute6;
1788 End if;
1789
1790 If l_interface_rec.attribute7 = G_NullChar then
1791 l_resource_rec.attribute7 := null;
1792 Elsif l_interface_rec.transaction_type = G_Update then
1793 l_resource_rec.attribute7 :=
1794 nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
1795 Else
1796 l_resource_rec.attribute7 := l_interface_rec.attribute7;
1797 End if;
1798
1799 If l_interface_rec.attribute8 = G_NullChar then
1800 l_resource_rec.attribute8 := null;
1801 Elsif l_interface_rec.transaction_type = G_Update then
1802 l_resource_rec.attribute8 :=
1803 nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
1804 Else
1805 l_resource_rec.attribute8 := l_interface_rec.attribute8;
1806 End if;
1807
1808 If l_interface_rec.attribute9 = G_NullChar then
1809 l_resource_rec.attribute9 := null;
1810 Elsif l_interface_rec.transaction_type = G_Update then
1811 l_resource_rec.attribute9 :=
1812 nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
1813 Else
1814 l_resource_rec.attribute9 := l_interface_rec.attribute9;
1815 End if;
1816
1817 If l_interface_rec.attribute10 = G_NullChar then
1818 l_resource_rec.attribute10 := null;
1819 Elsif l_interface_rec.transaction_type = G_Update then
1820 l_resource_rec.attribute10 :=
1821 nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
1822 Else
1823 l_resource_rec.attribute10 := l_interface_rec.attribute10;
1824 End if;
1825
1826 If l_interface_rec.attribute11 = G_NullChar then
1827 l_resource_rec.attribute11 := null;
1828 Elsif l_interface_rec.transaction_type = G_Update then
1829 l_resource_rec.attribute11 :=
1830 nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
1831 Else
1832 l_resource_rec.attribute11 := l_interface_rec.attribute11;
1833 End if;
1834
1835 If l_interface_rec.attribute12 = G_NullChar then
1836 l_resource_rec.attribute12 := null;
1837 Elsif l_interface_rec.transaction_type = G_Update then
1838 l_resource_rec.attribute12 :=
1839 nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
1840 Else
1841 l_resource_rec.attribute12 := l_interface_rec.attribute12;
1842 End if;
1843
1844 If l_interface_rec.attribute13 = G_NullChar then
1845 l_resource_rec.attribute13 := null;
1846 Elsif l_interface_rec.transaction_type = G_Update then
1847 l_resource_rec.attribute13 :=
1848 nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
1849 Else
1850 l_resource_rec.attribute13 := l_interface_rec.attribute13;
1851 End if;
1852
1853 If l_interface_rec.attribute14 = G_NullChar then
1854 l_resource_rec.attribute14 := null;
1855 Elsif l_interface_rec.transaction_type = G_Update then
1856 l_resource_rec.attribute14 :=
1857 nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
1858 Else
1859 l_resource_rec.attribute14 := l_interface_rec.attribute14;
1860 End if;
1861
1862 If l_interface_rec.attribute15 = G_NullChar then
1863 l_resource_rec.attribute15 := null;
1864 Elsif l_interface_rec.transaction_type = G_Update then
1865 l_resource_rec.attribute15 :=
1866 nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
1867 Else
1868 l_resource_rec.attribute15 := l_interface_rec.attribute15;
1869 End if;
1870
1871 --Bug 2514018
1872 If l_interface_rec.principle_flag = G_NullNum then
1873 l_resource_rec.principle_flag := null;
1874 Elsif l_interface_rec.transaction_type = G_Update then
1875 l_resource_rec.principle_flag :=
1876 nvl(l_interface_rec.principle_flag,Fnd_Api.G_Miss_Num);
1877 Else
1878 l_resource_rec.principle_flag := l_interface_rec.principle_flag;
1879 End if;
1880 --Bug 2514018
1881
1882 --Bug 2514018
1883 If l_interface_rec.schedule_seq_num = G_NullNum then
1884 l_resource_rec.schedule_seq_num := null;
1885 Elsif l_interface_rec.transaction_type = G_Update then
1886 l_resource_rec.schedule_seq_num :=
1887 nvl(l_interface_rec.schedule_seq_num,Fnd_Api.G_Miss_Num);
1888 Else
1889 l_resource_rec.schedule_seq_num := l_interface_rec.schedule_seq_num;
1890 End if;
1891 --Bug 2514018
1892
1893 If l_interface_rec.transaction_type = G_Insert then
1894
1895 Bom_OpResource_Pvt.CreateResource(
1896 p_api_version => 1.0,
1897 p_init_msg_list => FND_API.G_TRUE,
1898 p_commit => FND_API.G_FALSE,
1899 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1900 x_return_status => l_return_status,
1901 x_msg_count => l_msg_count,
1902 x_msg_data => l_msg_data,
1903 p_resource_rec => l_resource_rec,
1904 x_resource_rec => l_resource_rec
1905 );
1906
1907 Elsif l_interface_rec.transaction_type = G_Update then
1908
1909 Bom_OpResource_Pvt.UpdateResource(
1910 p_api_version => 1.0,
1911 p_init_msg_list => FND_API.G_TRUE,
1912 p_commit => FND_API.G_FALSE,
1913 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1914 x_return_status => l_return_status,
1915 x_msg_count => l_msg_count,
1916 x_msg_data => l_msg_data,
1917 p_resource_rec => l_resource_rec,
1918 x_resource_rec => l_resource_rec
1919 );
1920
1921 Elsif l_interface_rec.transaction_type = G_Delete then
1922
1923 Bom_OpResource_Pvt.DeleteResource(
1924 p_api_version => 1.0,
1925 p_init_msg_list => FND_API.G_TRUE,
1926 p_commit => FND_API.G_FALSE,
1927 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1928 x_return_status => l_return_status,
1929 x_msg_count => l_msg_count,
1930 x_msg_data => l_msg_data,
1931 p_resource_rec => l_resource_rec,
1932 x_resource_rec => l_resource_rec
1933 );
1934 End if; -- insert, update and delete
1935
1936 If l_return_status = Fnd_Api.G_RET_STS_SUCCESS then
1937 If g_DeleteRows then
1938 Delete from bom_op_resources_interface
1939 Where rowid = l_interface_rec.rowid;
1940 Else
1941 Update bom_op_resources_interface
1942 Set process_flag = 7,
1943 transaction_id = mtl_system_items_interface_s.nextval,
1944 request_id = nvl(request_id,g_RequestId),
1945 program_id = nvl(program_id,g_ProgramId),
1946 program_application_id = nvl(program_application_id,g_ApplicationId),
1947 program_update_date = nvl(program_update_date,sysdate),
1948 created_by = nvl(created_by,g_UserId),
1949 last_updated_by = nvl(last_updated_by,g_UserId),
1950 creation_date = nvl(creation_date,sysdate),
1951 last_update_date = nvl(last_update_date,sysdate),
1952 last_update_login = nvl(last_update_login,g_LoginId)
1953 Where rowid = l_interface_rec.rowid;
1954 End if;
1955 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR then
1956 If l_phase = 2 then
1957 If l_msg_count > 1 then
1958 l_msg_data := FND_MSG_PUB.Get;
1959 End if;
1960 Fnd_Message.Parse_Encoded(
1961 ENCODED_MESSAGE => l_msg_data,
1962 APP_SHORT_NAME => l_msg_app,
1963 MESSAGE_NAME => l_msg_name);
1964 For l_transaction_rec in l_Transaction_csr loop
1965 l_ret_code := INVPUOPI.mtl_log_interface_err(
1966 org_id => g_OrgId,
1967 user_id => g_UserId,
1968 login_id => g_LoginId,
1969 prog_appid => g_ApplicationId,
1970 prog_id => g_ProgramId,
1971 req_id => g_RequestId,
1972 trans_id => l_transaction_rec.transaction_id,
1973 error_text => l_msg_data,
1974 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1975 msg_name => l_msg_name,
1976 err_text => l_msg_data);
1977 If l_ret_code <> 0 then
1978 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1979 end if;
1980 Update bom_op_resources_interface
1981 Set process_flag = 3,
1982 transaction_id = l_transaction_rec.transaction_id
1983 Where rowid = l_interface_rec.rowid;
1984 End loop; -- log error
1985 End if; -- final phase
1986 Elsif l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR then
1987 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1988 End if; -- process return status
1989 End; -- nested block
1990
1991 If g_Commit then
1992 If mod(l_interface_csr%rowcount, g_CommitRows) = 0 then
1993 COMMIT WORK;
1994 End if;
1995 End if; -- periodic commits
1996 End loop; -- scan interface table
1997 If g_Commit then
1998 COMMIT WORK;
1999 End if; -- commit remaining rows
2000 -- rescan table in case updating new operation resources
2001 l_phase := l_phase + 1;
2002 End loop; -- phase
2003 x_return_status := FND_API.G_RET_STS_SUCCESS;
2004 EXCEPTION
2005 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2007 WHEN OTHERS THEN
2008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2009 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2010 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2011 END IF;
2012 END ImportResource;
2013
2014 PROCEDURE ImportRevision(x_return_status IN OUT NOCOPY VARCHAR2) is
2015 cursor l_interface_csr is
2016 Select rowid,
2017 inventory_item_id,
2018 inventory_item_number,
2019 organization_id,
2020 organization_code,
2021 process_revision,
2022 change_notice,
2023 ecn_initiation_date,
2024 implementation_date,
2025 implemented_serial_number,
2026 effectivity_date,
2027 attribute_category,
2028 attribute1,
2029 attribute2,
2030 attribute3,
2031 attribute4,
2032 attribute5,
2033 attribute6,
2034 attribute7,
2035 attribute8,
2036 attribute9,
2037 attribute10,
2038 attribute11,
2039 attribute12,
2040 attribute13,
2041 attribute14,
2042 attribute15,
2043 transaction_type
2044 From mtl_rtg_item_revs_interface
2045 Where process_flag = 1
2046 And (g_AllOrgs = g_yes
2047 or organization_id = g_OrgId
2048 or organization_code = g_OrgCode
2049 );
2050 l_phase NUMBER := 1; -- scan table twice in case inserting and updating same
2051 -- revision
2052 l_return_status VARCHAR2(1);
2053 l_msg_count NUMBER;
2054 l_msg_data VARCHAR2(2000);
2055 l_msg_name VARCHAR2(80);
2056 l_msg_app VARCHAR2(3);
2057 l_ret_code NUMBER := 0;
2058 cursor l_Transaction_csr is
2059 Select mtl_system_items_interface_s.nextval transaction_id
2060 From dual;
2061 l_api_name CONSTANT VARCHAR2(30) := 'ImportRevision';
2062 Begin
2063 While l_phase <= 2 loop -- two passes
2064 For l_interface_rec in l_interface_csr loop
2065 Declare
2066 l_revision_rec Bom_RoutingRevision_Pvt.rtg_revision_rec_type;
2067 Begin -- nested block within loop
2068 if (upper(l_interface_rec.transaction_type) = 'CREATE') then
2069 l_interface_rec.transaction_type := G_Insert ;
2070 else
2071 l_interface_rec.transaction_type := upper(l_interface_rec.transaction_type);
2072 end if ;
2073
2074 If l_interface_rec.inventory_item_id = G_NullNum then
2075 l_revision_rec.inventory_item_id := null;
2076 Elsif l_interface_rec.transaction_type = G_Update then
2077 l_revision_rec.inventory_item_id :=
2078 nvl(l_interface_rec.inventory_item_id, Fnd_Api.G_Miss_Num);
2079 Else
2080 l_revision_rec.inventory_item_id := l_interface_rec.inventory_item_id;
2081 End if;
2082
2083 If l_interface_rec.inventory_item_number = G_NullChar then
2084 l_revision_rec.inventory_item_number := null;
2085 Elsif l_interface_rec.transaction_type = G_Update then
2086 l_revision_rec.inventory_item_number :=
2087 nvl(l_interface_rec.inventory_item_number, Fnd_Api.G_Miss_Char);
2088 Else
2089 l_revision_rec.inventory_item_number :=
2090 l_interface_rec.inventory_item_number;
2091 End if;
2092
2093 If l_interface_rec.organization_id = G_NullNum then
2094 l_revision_rec.organization_id := null;
2095 Elsif l_interface_rec.transaction_type = G_Update then
2096 l_revision_rec.organization_id :=
2097 nvl(l_interface_rec.organization_id, Fnd_Api.G_Miss_Num);
2098 Else
2099 l_revision_rec.organization_id := l_interface_rec.organization_id;
2100 End if;
2101
2102 If l_interface_rec.organization_code = G_NullChar then
2103 l_revision_rec.organization_code := null;
2104 Elsif l_interface_rec.transaction_type = G_Update then
2105 l_revision_rec.organization_code :=
2106 nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
2107 Else
2108 l_revision_rec.organization_code := l_interface_rec.organization_code;
2109 End if;
2110
2111 If l_interface_rec.process_revision = G_NullChar then
2112 l_revision_rec.process_revision := null;
2113 Elsif l_interface_rec.transaction_type = G_Update then
2114 l_revision_rec.process_revision :=
2115 nvl(l_interface_rec.process_revision, Fnd_Api.G_Miss_Char);
2116 Else
2117 l_revision_rec.process_revision := l_interface_rec.process_revision;
2118 End if;
2119
2120 If l_interface_rec.change_notice = G_NullChar then
2121 l_revision_rec.change_notice := null;
2122 Elsif l_interface_rec.transaction_type = G_Update then
2123 l_revision_rec.change_notice :=
2124 nvl(l_interface_rec.change_notice, Fnd_Api.G_Miss_Char);
2125 Else
2126 l_revision_rec.change_notice := l_interface_rec.change_notice;
2127 End if;
2128
2129 If l_interface_rec.ecn_initiation_date = G_NullDate then
2130 l_revision_rec.ecn_initiation_date := null;
2131 Elsif l_interface_rec.transaction_type = G_Update then
2132 l_revision_rec.ecn_initiation_date :=
2133 nvl(l_interface_rec.ecn_initiation_date, Fnd_Api.G_Miss_Date);
2134 Else
2135 l_revision_rec.ecn_initiation_date :=
2136 l_interface_rec.ecn_initiation_date;
2137 End if;
2138
2139 If l_interface_rec.implementation_date = G_NullDate then
2140 l_revision_rec.implementation_date := null;
2141 Elsif l_interface_rec.transaction_type = G_Update then
2142 l_revision_rec.implementation_date :=
2143 nvl(l_interface_rec.implementation_date, Fnd_Api.G_Miss_Date);
2144 Else
2145 l_revision_rec.implementation_date :=
2146 l_interface_rec.implementation_date;
2147 End if;
2148
2149 If l_interface_rec.implemented_serial_number = G_NullChar then
2150 l_revision_rec.implemented_serial_number := null;
2151 Elsif l_interface_rec.transaction_type = G_Update then
2152 l_revision_rec.implemented_serial_number :=
2153 nvl(l_interface_rec.implemented_serial_number, Fnd_Api.G_Miss_Char);
2154 Else
2155 l_revision_rec.implemented_serial_number :=
2156 l_interface_rec.implemented_serial_number;
2157 End if;
2158
2159 If l_interface_rec.effectivity_date = G_NullDate then
2160 l_revision_rec.effectivity_date := null;
2161 Elsif l_interface_rec.transaction_type = G_Update then
2162 l_revision_rec.effectivity_date :=
2163 nvl(l_interface_rec.effectivity_date, Fnd_Api.G_Miss_Date);
2164 Else
2165 l_revision_rec.effectivity_date := l_interface_rec.effectivity_date;
2166 End if;
2167
2168 If l_interface_rec.attribute_category = G_NullChar then
2169 l_revision_rec.attribute_category := null;
2170 Elsif l_interface_rec.transaction_type = G_Update then
2171 l_revision_rec.attribute_category :=
2172 nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
2173 Else
2174 l_revision_rec.attribute_category :=
2175 l_interface_rec.attribute_category;
2176 End if;
2177
2178 If l_interface_rec.attribute1 = G_NullChar then
2179 l_revision_rec.attribute1 := null;
2180 Elsif l_interface_rec.transaction_type = G_Update then
2181 l_revision_rec.attribute1 :=
2182 nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
2183 Else
2184 l_revision_rec.attribute1 := l_interface_rec.attribute1;
2185 End if;
2186
2187 If l_interface_rec.attribute2 = G_NullChar then
2188 l_revision_rec.attribute2 := null;
2189 Elsif l_interface_rec.transaction_type = G_Update then
2190 l_revision_rec.attribute2 :=
2191 nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
2192 Else
2193 l_revision_rec.attribute2 := l_interface_rec.attribute2;
2194 End if;
2195
2196 If l_interface_rec.attribute3 = G_NullChar then
2197 l_revision_rec.attribute3 := null;
2198 Elsif l_interface_rec.transaction_type = G_Update then
2199 l_revision_rec.attribute3 :=
2200 nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
2201 Else
2202 l_revision_rec.attribute3 := l_interface_rec.attribute3;
2203 End if;
2204
2205 If l_interface_rec.attribute4 = G_NullChar then
2206 l_revision_rec.attribute4 := null;
2207 Elsif l_interface_rec.transaction_type = G_Update then
2208 l_revision_rec.attribute4 :=
2209 nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
2210 Else
2211 l_revision_rec.attribute4 := l_interface_rec.attribute4;
2212 End if;
2213
2214 If l_interface_rec.attribute5 = G_NullChar then
2215 l_revision_rec.attribute5 := null;
2216 Elsif l_interface_rec.transaction_type = G_Update then
2217 l_revision_rec.attribute5 :=
2218 nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
2219 Else
2220 l_revision_rec.attribute5 := l_interface_rec.attribute5;
2221 End if;
2222
2223 If l_interface_rec.attribute6 = G_NullChar then
2224 l_revision_rec.attribute6 := null;
2225 Elsif l_interface_rec.transaction_type = G_Update then
2226 l_revision_rec.attribute6 :=
2227 nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
2228 Else
2229 l_revision_rec.attribute6 := l_interface_rec.attribute6;
2230 End if;
2231
2232 If l_interface_rec.attribute7 = G_NullChar then
2233 l_revision_rec.attribute7 := null;
2234 Elsif l_interface_rec.transaction_type = G_Update then
2235 l_revision_rec.attribute7 :=
2236 nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
2237 Else
2238 l_revision_rec.attribute7 := l_interface_rec.attribute7;
2239 End if;
2240
2241 If l_interface_rec.attribute8 = G_NullChar then
2242 l_revision_rec.attribute8 := null;
2243 Elsif l_interface_rec.transaction_type = G_Update then
2244 l_revision_rec.attribute8 :=
2245 nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
2246 Else
2247 l_revision_rec.attribute8 := l_interface_rec.attribute8;
2248 End if;
2249
2250 If l_interface_rec.attribute9 = G_NullChar then
2251 l_revision_rec.attribute9 := null;
2252 Elsif l_interface_rec.transaction_type = G_Update then
2253 l_revision_rec.attribute9 :=
2254 nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
2255 Else
2256 l_revision_rec.attribute9 := l_interface_rec.attribute9;
2257 End if;
2258
2259 If l_interface_rec.attribute10 = G_NullChar then
2260 l_revision_rec.attribute10 := null;
2261 Elsif l_interface_rec.transaction_type = G_Update then
2262 l_revision_rec.attribute10 :=
2263 nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
2264 Else
2265 l_revision_rec.attribute10 := l_interface_rec.attribute10;
2266 End if;
2267
2268 If l_interface_rec.attribute11 = G_NullChar then
2269 l_revision_rec.attribute11 := null;
2270 Elsif l_interface_rec.transaction_type = G_Update then
2271 l_revision_rec.attribute11 :=
2272 nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
2273 Else
2274 l_revision_rec.attribute11 := l_interface_rec.attribute11;
2275 End if;
2276
2277 If l_interface_rec.attribute12 = G_NullChar then
2278 l_revision_rec.attribute12 := null;
2279 Elsif l_interface_rec.transaction_type = G_Update then
2280 l_revision_rec.attribute12 :=
2281 nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
2282 Else
2283 l_revision_rec.attribute12 := l_interface_rec.attribute12;
2284 End if;
2285
2286 If l_interface_rec.attribute13 = G_NullChar then
2287 l_revision_rec.attribute13 := null;
2288 Elsif l_interface_rec.transaction_type = G_Update then
2289 l_revision_rec.attribute13 :=
2290 nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
2291 Else
2292 l_revision_rec.attribute13 := l_interface_rec.attribute13;
2293 End if;
2294
2295 If l_interface_rec.attribute14 = G_NullChar then
2296 l_revision_rec.attribute14 := null;
2297 Elsif l_interface_rec.transaction_type = G_Update then
2298 l_revision_rec.attribute14 :=
2299 nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
2300 Else
2301 l_revision_rec.attribute14 := l_interface_rec.attribute14;
2302 End if;
2303
2304 If l_interface_rec.attribute15 = G_NullChar then
2305 l_revision_rec.attribute15 := null;
2306 Elsif l_interface_rec.transaction_type = G_Update then
2307 l_revision_rec.attribute15 :=
2308 nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
2309 Else
2310 l_revision_rec.attribute15 := l_interface_rec.attribute15;
2311 End if;
2312
2313 If l_interface_rec.transaction_type = G_Insert then
2314
2315 Bom_RoutingRevision_Pvt.CreateRtgRevision(
2316 p_api_version => 1.0,
2317 p_init_msg_list => FND_API.G_TRUE,
2318 p_commit => FND_API.G_FALSE,
2319 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2320 x_return_status => l_return_status,
2321 x_msg_count => l_msg_count,
2322 x_msg_data => l_msg_data,
2323 p_RtgRevision_rec => l_revision_rec,
2324 x_RtgRevision_rec => l_revision_rec
2325 );
2326
2327 Elsif l_interface_rec.transaction_type = G_Update then
2328
2329 Bom_RoutingRevision_Pvt.UpdateRtgRevision(
2330 p_api_version => 1.0,
2331 p_init_msg_list => FND_API.G_TRUE,
2332 p_commit => FND_API.G_FALSE,
2333 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2334 x_return_status => l_return_status,
2335 x_msg_count => l_msg_count,
2336 x_msg_data => l_msg_data,
2337 p_RtgRevision_rec => l_revision_rec,
2338 x_RtgRevision_rec => l_revision_rec
2339 );
2340
2341 Elsif l_interface_rec.transaction_type = G_Delete then
2342
2343 Bom_RoutingRevision_Pvt.DeleteRtgRevision(
2344 p_api_version => 1.0,
2345 p_init_msg_list => FND_API.G_TRUE,
2346 p_commit => FND_API.G_FALSE,
2347 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2348 x_return_status => l_return_status,
2349 x_msg_count => l_msg_count,
2350 x_msg_data => l_msg_data,
2351 p_RtgRevision_rec => l_revision_rec,
2352 x_RtgRevision_rec => l_revision_rec
2353 );
2354 End if; -- insert, update and delete
2355
2356 If l_return_status = Fnd_Api.G_RET_STS_SUCCESS then
2357 If g_DeleteRows then
2358 Delete from mtl_rtg_item_revs_interface
2359 Where rowid = l_interface_rec.rowid;
2360 Else
2361 Update mtl_rtg_item_revs_interface
2362 Set process_flag = 7,
2363 transaction_id = mtl_system_items_interface_s.nextval,
2364 request_id = nvl(request_id,g_RequestId),
2365 program_id = nvl(program_id,g_ProgramId),
2366 program_application_id = nvl(program_application_id,g_ApplicationId),
2367 program_update_date = nvl(program_update_date,sysdate),
2368 created_by = nvl(created_by,g_UserId),
2369 last_updated_by = nvl(last_updated_by,g_UserId),
2370 creation_date = nvl(creation_date,sysdate),
2371 last_update_date = nvl(last_update_date,sysdate),
2372 last_update_login = nvl(last_update_login,g_LoginId)
2373 Where rowid = l_interface_rec.rowid;
2374 End if;
2375 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR then
2376 If l_phase = 2 then
2377 If l_msg_count > 1 then
2378 l_msg_data := FND_MSG_PUB.Get;
2379 End if;
2380 Fnd_Message.Parse_Encoded(
2381 ENCODED_MESSAGE => l_msg_data,
2382 APP_SHORT_NAME => l_msg_app,
2383 MESSAGE_NAME => l_msg_name);
2384 For l_transaction_rec in l_Transaction_csr loop
2385 l_ret_code := INVPUOPI.mtl_log_interface_err(
2386 org_id => g_OrgId,
2387 user_id => g_UserId,
2388 login_id => g_LoginId,
2389 prog_appid => g_ApplicationId,
2390 prog_id => g_ProgramId,
2391 req_id => g_RequestId,
2392 trans_id => l_transaction_rec.transaction_id,
2393 error_text => l_msg_data,
2394 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
2395 msg_name => l_msg_name,
2396 err_text => l_msg_data);
2397 If l_ret_code <> 0 then
2398 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2399 end if;
2400 Update mtl_rtg_item_revs_interface
2401 Set process_flag = 3,
2402 transaction_id = l_transaction_rec.transaction_id
2403 Where rowid = l_interface_rec.rowid;
2404 End loop; -- log error
2405 End if; -- final phase
2406 Elsif l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR then
2407 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2408 End if; -- process return status
2409 End; -- nested block
2410
2411 If g_Commit then
2412 If mod(l_interface_csr%rowcount, g_CommitRows) = 0 then
2413 COMMIT WORK;
2414 End if;
2415 End if; -- periodic commits
2416 End loop; -- scan interface table
2417 If g_Commit then
2418 COMMIT WORK;
2419 End if; -- commit remaining rows
2420 -- rescan table in case updating new revisions
2421 l_phase := l_phase + 1;
2422 End loop; -- phase
2423 x_return_status := FND_API.G_RET_STS_SUCCESS;
2424 EXCEPTION
2425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2427 WHEN OTHERS THEN
2428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2429 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2430 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2431 END IF;
2432 END ImportRevision;
2433
2434 PROCEDURE ImportRouting(
2435 p_api_version IN NUMBER,
2436 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2437 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2438 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2439 x_return_status IN OUT NOCOPY VARCHAR2,
2440 x_msg_count IN OUT NOCOPY NUMBER,
2441 x_msg_data IN OUT NOCOPY VARCHAR2,
2442 p_organization_id IN NUMBER,
2443 p_all_organizations IN VARCHAR2 := FND_API.G_TRUE,
2444 p_commit_rows IN NUMBER := 500,
2445 p_delete_rows IN VARCHAR2 := FND_API.G_FALSE
2446 )IS
2447 l_api_name CONSTANT VARCHAR2(30) := 'ImportRouting';
2448 l_api_version CONSTANT NUMBER := 1.0;
2449 l_return_status VARCHAR2(1);
2450 cursor l_parameter_cursor is
2451 Select organization_code
2452 From mtl_parameters
2453 Where organization_id = p_organization_id;
2454 BEGIN
2455 -- Standard call to check for call compatibility.
2456 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2457 G_PKG_NAME) THEN
2458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2459 END IF;
2460 -- Initialize message list if p_init_msg_list is set to TRUE.
2461 IF FND_API.to_Boolean(p_init_msg_list) THEN
2462 FND_MSG_PUB.initialize;
2463 END IF;
2464 -- Initialize API return status to success
2465 x_return_status := FND_API.G_RET_STS_SUCCESS;
2466
2467 -- API body
2468
2469 g_CommitRows := p_commit_rows;
2470 g_Commit := Fnd_Api.to_boolean(p_commit);
2471 g_DeleteRows := Fnd_Api.to_boolean(p_delete_rows);
2472 g_OrgId := p_organization_id;
2473 For l_parameter_rec in l_parameter_cursor loop
2474 g_OrgCode := l_parameter_rec.organization_code;
2475 End loop;
2476 If p_all_organizations = FND_API.G_FALSE then
2477 g_AllOrgs := g_no;
2478 Else
2479 g_AllOrgs := g_yes;
2480 End if;
2481
2482 -- who columns
2483 g_UserId := nvl(Fnd_Global.USER_ID, -1);
2484 g_LoginId := Fnd_Global.LOGIN_ID;
2485 g_RequestId := Fnd_Global.CONC_REQUEST_ID;
2486 g_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
2487 g_ApplicationId := Fnd_Global.PROG_APPL_ID;
2488
2489 ImportHeader(x_return_status => l_return_status);
2490 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2491 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2492 End if;
2493 ImportOperation(x_return_status => l_return_status);
2494 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2495 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2496 End if;
2497 ImportResource(x_return_status => l_return_status);
2498 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2499 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2500 End if;
2501 ImportRevision(x_return_status => l_return_status);
2502 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2503 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2504 End if;
2505
2506 -- End of API body.
2507 -- Standard call to get message count and if count is 1, get message info.
2508 FND_MSG_PUB.Count_And_Get(
2509 p_count => x_msg_count,
2510 p_data => x_msg_data
2511 );
2512 EXCEPTION
2513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2514 ROLLBACK;
2515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2516 FND_MSG_PUB.Count_And_Get(
2517 p_count => x_msg_count,
2518 p_data => x_msg_data
2519 );
2520 WHEN OTHERS THEN
2521 ROLLBACK;
2522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2523 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2524 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2525 END IF;
2526 FND_MSG_PUB.Count_And_Get(
2527 p_count => x_msg_count,
2528 p_data => x_msg_data
2529 );
2530 END ImportRouting;
2531 END BOM_RoutingInterface_PUB;