[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_OPEN_INTERFACE
Source
1 PACKAGE BODY BOM_RTG_OPEN_INTERFACE AS
2 /* $Header: BOMPROIB.pls 120.7.12000000.2 2007/04/11 10:00:31 shchandr ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMPROIB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_RTG_OPEN_INTERFACE
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 12-DEC-02 Deepak Jebar Initial Creation
21 -- 15-JUN-05 Abhishek Bhardwaj Added Batch Id
22 --
23 ***************************************************************************/
24
25 g_UserId number := -1;
26 g_LoginId number;
27 g_RequestId number;
28 g_ProgramId number;
29 g_ApplicationId number;
30 g_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
31 g_rtg_revs_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
32 g_op_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
33 g_op_res_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
34 g_sub_op_res_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
35 g_nwk_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
36
37 --for updating interface tables
38 g_rtg_revision_rec Bom_Rtg_Pub.Rtg_Revision_Rec_Type;
39 g_op_rec Bom_Rtg_Pub.Operation_Rec_Type;
40 g_op_res_rec Bom_Rtg_Pub.Op_Resource_Rec_Type;
41 g_sub_op_res_rec Bom_Rtg_Pub.Sub_Resource_Rec_Type;
42 g_nwk_rec Bom_Rtg_Pub.Op_Network_Rec_Type;
43
44
45 FUNCTION Update_Rtg_Interface_Tables(
46 x_err_text IN OUT NOCOPY VARCHAR2)
47 return integer;
48
49 FUNCTION Delete_Rtg_OI (
50 x_err_text IN OUT NOCOPY VARCHAR2
51 , p_batch_id IN NUMBER) -- Added parameter p_batch_id for Batch Import
52 return integer;
53
54 -- Overloaded IMPORT_RTG for Batch Import
55 FUNCTION IMPORT_RTG
56 ( p_organization_id IN NUMBER
57 , p_all_org IN NUMBER
58 , p_delete_rows IN NUMBER
59 , x_err_text IN OUT NOCOPY VARCHAR2
60 ) RETURN INTEGER
61 IS
62 l_return_status INTEGER := 0;
63 BEGIN
64 l_return_status := IMPORT_RTG
65 (p_organization_id => p_organization_id,
66 p_all_org => p_all_org,
67 p_delete_rows => p_delete_rows,
68 x_err_text => x_err_text,
69 p_batch_id => NULL);
70 RETURN l_return_status;
71 END;
72
73 FUNCTION IMPORT_RTG
74 ( p_organization_id IN NUMBER
75 , p_all_org IN NUMBER
76 , p_delete_rows IN NUMBER
77 , x_err_text IN OUT NOCOPY VARCHAR2
78 , p_batch_id IN NUMBER
79 ) RETURN INTEGER IS
80
81 cursor get_rtg_header is
82 select * from BOM_OP_ROUTINGS_INTERFACE
83 Where process_flag = 1
84 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
85 and transaction_id is not null
86 and
87 (
88 ( (p_batch_id is null) and (batch_id is null) )
89 or ( p_batch_id = batch_id )
90 )
91 order by alternate_routing_designator desc; -- bug 3684819, we need primary routings to be processed first
92 rtg_header_rec get_rtg_header%ROWTYPE;
93
94 cursor get_rtg_revs (cp_ass_item_name varchar2, cp_org_code varchar2) is
95 select * from MTL_RTG_ITEM_REVS_INTERFACE
96 Where process_flag = 1
97 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
98 and inventory_item_number = cp_ass_item_name
99 and organization_code = cp_org_code
100 and transaction_id is not null
101 and
102 (
103 ( (p_batch_id is null) and (batch_id is null) )
104 or ( p_batch_id = batch_id )
105 )
106 order by process_revision; -- bug fix 3693102 we need to prorcess revs in alpha numeric sort order
107
108 rtg_revs_rec get_rtg_revs%ROWTYPE;
109
110 cursor get_orphan_revs is
111 select * from MTL_RTG_ITEM_REVS_INTERFACE
112 Where process_flag = 1
113 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
114 and transaction_id is not null
115 and
116 (
117 ( (p_batch_id is null) and (batch_id is null) )
118 or ( p_batch_id = batch_id )
119 );
120
121 orphan_revs_rec get_orphan_revs%ROWTYPE;
122
123 cursor get_op_seqs(cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_rtg_des varchar2) is
124 select * from BOM_OP_SEQUENCES_INTERFACE A
125 Where process_flag = 1
126 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
127 and assembly_item_number = cp_ass_item_name
128 and organization_code = cp_org_code
129 and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
130 and transaction_id is not null
131 and
132 (
133 ( (p_batch_id is null) and (batch_id is null) )
134 or ( p_batch_id = batch_id )
135 )
136 order by operation_type desc;
137
138 op_seqs_rec get_op_seqs%ROWTYPE;
139
140 /* Modified cursor for bug 4350033 */
141 cursor get_orphan_op_seqs is
142 select * from BOM_OP_SEQUENCES_INTERFACE A
143 Where process_flag = 1
144 and p_all_org = 1
145 and transaction_id is not null
146 and
147 (
148 ( (p_batch_id is null) and (batch_id is null) )
149 or ( p_batch_id = batch_id )
150 )
151 and rownum = 1
152 UNION ALL
153 SELECT * from BOM_OP_SEQUENCES_INTERFACE A
154 Where process_flag = 1
155 and p_all_org = 2 and organization_Id = p_organization_id
156 and transaction_id is not null
157 and rownum = 1
158 and
159 (
160 ( (p_batch_id is null) and (batch_id is null) )
161 or ( p_batch_id = batch_id )
162 );
163
164
165
166 orphan_op_seqs_rec get_orphan_op_seqs%ROWTYPE;
167
168 cursor get_op_nwk(cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_rtg_des varchar2) is
169 select * from BOM_OP_NETWORKS_INTERFACE
170 Where process_flag = 1
171 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
172 and assembly_item_number = cp_ass_item_name
173 and organization_code = cp_org_code
174 and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
175 and transaction_id is not null
176 and
177 (
178 ( (p_batch_id is null) and (batch_id is null) )
179 or ( p_batch_id = batch_id )
180 );
181 op_nwk_rec get_op_nwk%ROWTYPE;
182
183 cursor get_orphan_op_nwk is
184 select * from BOM_OP_NETWORKS_INTERFACE
185 Where process_flag = 1
186 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
187 and transaction_id is not null
188 and
189 (
190 ( (p_batch_id is null) and (batch_id is null) )
191 or ( p_batch_id = batch_id )
192 );
193
194 orphan_op_nwk_rec get_orphan_op_nwk%ROWTYPE;
195
196 cursor get_op_resources(cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_rtg_des varchar2) is
197 select * from BOM_OP_RESOURCES_INTERFACE A
198 where process_flag = 1
199 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
200 and assembly_item_number = cp_ass_item_name
201 and organization_code = cp_org_code
202 and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
203 and transaction_id is not null
204 and
205 (
206 ( (p_batch_id is null) and (batch_id is null) )
207 or ( p_batch_id = batch_id )
208 );
209 op_res_rec get_op_resources%ROWTYPE;
210
211 cursor get_orphan_op_resources is
212 select * from BOM_OP_RESOURCES_INTERFACE
213 where process_flag = 1
214 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
215 and transaction_id is not null
216 and
217 (
218 ( (p_batch_id is null) and (batch_id is null) )
219 or ( p_batch_id = batch_id )
220 );
221 orphan_op_res_rec get_orphan_op_resources%ROWTYPE;
222
223 cursor get_sub_op_resources(cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_rtg_des varchar2) is
224 select * from BOM_SUB_OP_RESOURCES_INTERFACE
225 where process_flag = 1
226 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
227 and assembly_item_number = cp_ass_item_name
228 and organization_code = cp_org_code
229 and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
230 and transaction_id is not null
231 and
232 (
233 ( (p_batch_id is null) and (batch_id is null) )
234 or ( p_batch_id = batch_id )
235 );
236 sub_op_res_rec get_sub_op_resources%ROWTYPE;
237
238 cursor get_orphan_sub_op_resources is
239 select * from BOM_SUB_OP_RESOURCES_INTERFACE
240 where process_flag = 1
241 and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
242 and transaction_id is not null
243 and
244 (
245 ( (p_batch_id is null) and (batch_id is null) )
246 or ( p_batch_id = batch_id )
247 );
248 orphan_sub_op_res_rec get_orphan_sub_op_resources%ROWTYPE;
249
250 i NUMBER;
251 stmt_num NUMBER;
252 debug_on Varchar2(1) := 'Y';
253 l_msg_count NUMBER;
254 ret_status Varchar2(1);
255 l_return_status INTEGER;
256 l_func_ret_status INTEGER;
257 -- l_assembly_item_name Varchar2(81);
258 l_assembly_item_name Varchar2(240); -- bug 2947642
259 l_org_code Varchar2(3);
260 l_alt_rtg_desig Varchar2(10);
261 empty_bo Varchar2(1) := 'Y';
262 l_rtg_header_exists Varchar2(1) := 'Y';
263 l_revs_exists Varchar2(1) := 'Y';
264 l_op_exists Varchar2(1) := 'Y';
265 l_op_res_exists Varchar2(1) :='Y';
266 l_sub_op_res_exists Varchar2(1) :='Y';
267 l_nwk_exists Varchar2(1) :='Y';
268
269 BEGIN
270 l_func_ret_status := 0;
271 stmt_num := 0;
272 IF FND_PROFILE.VALUE('MRP_DEBUG') = 'Y' then
273 debug_on := 'Y';
274 else
275 debug_on :='N';
276 end if;
277
278 stmt_num := 1;
279 -- who columns
280 g_UserId := nvl(Fnd_Global.USER_ID, -1);
281 g_LoginId := Fnd_Global.LOGIN_ID;
282 g_RequestId := Fnd_Global.CONC_REQUEST_ID;
283 g_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
284 g_ApplicationId := Fnd_Global.PROG_APPL_ID;
285 --commented by vhymavat for bug 3179687
286 /*
287 fnd_global.apps_initialize
288 ( user_id => g_UserId,
289 resp_id => FND_PROFILE.value('RESP_ID'),
290 resp_appl_id => g_ApplicationId
291 );
292 */
293
294
295 -- Initialize API return status to success
296 l_return_status := 0;
297 -- Set the Global Variable to Routing Open Interface type.
298 Error_Handler.set_bom_oi;
299
300 stmt_num := 2;
301 -- Convert the Derived columns to User friendly columns
302 l_return_status := BOM_RTG_OI_UTIL.Process_Rtg_Header
303 (p_organization_id,p_all_org,g_UserId,g_LoginId,
304 g_ApplicationId,g_ProgramId,g_RequestId,x_err_text,p_batch_id);
305 IF (l_return_status <> 0) THEN
306 RETURN(l_return_status);
307 END IF;
308 l_return_status := BOM_RTG_OI_UTIL.Process_Op_Seqs
309 (p_organization_id,p_all_org,g_UserId,g_LoginId,
310 g_ApplicationId,g_ProgramId,g_RequestId,x_err_text,p_batch_id);
311 IF (l_return_status <> 0) THEN
312 RETURN(l_return_status);
313 END IF;
314 l_return_status := BOM_RTG_OI_UTIL.Process_Op_Nwks
315 (p_organization_id,p_all_org,g_UserId,g_LoginId,
316 g_ApplicationId,g_ProgramId,g_RequestId,x_err_text,p_batch_id);
317 IF (l_return_status <> 0) THEN
318 RETURN(l_return_status);
319 END IF;
320 l_return_status := BOM_RTG_OI_UTIL.Process_Op_Resources
321 (p_organization_id,p_all_org,g_UserId,g_LoginId,
322 g_ApplicationId,g_ProgramId,g_RequestId,x_err_text,p_batch_id);
323 IF (l_return_status <> 0) THEN
324 RETURN(l_return_status);
325 END IF;
326 l_return_status := BOM_RTG_OI_UTIL.Process_Sub_Op_Resources
327 (p_organization_id,p_all_org,g_UserId,g_LoginId,
328 g_ApplicationId,g_ProgramId,g_RequestId,x_err_text,p_batch_id);
329 IF (l_return_status <> 0) THEN
330 RETURN(l_return_status);
331 END IF;
332 l_return_status := BOM_RTG_OI_UTIL.Process_Rtg_Revisions
333 (p_organization_id,p_all_org,g_UserId,g_LoginId,
334 g_ApplicationId,g_ProgramId,g_RequestId,x_err_text,p_batch_id);
335 IF (l_return_status <> 0) THEN
336 RETURN(l_return_status);
337 END IF;
338
339 stmt_num :=3;
340 OPEN get_rtg_header;
341 LOOP
342
343 /* Initialize all the variables in business object */
344
345 g_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
346 g_rtg_revs_tbl := Bom_Rtg_Pub.G_MISS_RTG_REVISION_TBL;
347 g_op_tbl := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
348 g_op_res_tbl := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
349 g_sub_op_res_tbl := Bom_Rtg_Pub.G_MISS_SUB_RESOURCE_TBL;
350 g_nwk_tbl := Bom_Rtg_Pub.G_MISS_OP_NETWORK_TBL;
351 l_assembly_item_name := NULL;
352 l_org_code := NULL;
353 l_alt_rtg_desig := NULL;
354 empty_bo :='Y';
355
356 /* Get the parent header information l_assembly_item_name
357 l_org_code, l_alt_rtg_desig */
358
359 If (l_rtg_header_exists = 'Y') then
360 FETCH get_rtg_header into rtg_header_rec;
361 If (get_rtg_header%NOTFOUND) then
362 l_rtg_header_exists := 'N';
363 CLOSE get_rtg_header;
364 else
365 l_rtg_header_exists :='Y';
366 l_assembly_item_name := rtg_header_rec.assembly_item_number;
367 l_org_code := rtg_header_rec.organization_code;
368 l_alt_rtg_desig := rtg_header_rec.alternate_routing_designator;
369 END IF; -- get_rtg_header%notfound
370 ELSIF (l_revs_exists ='Y') then
371 OPEN get_orphan_revs;
372 FETCH get_orphan_revs into orphan_revs_rec;
373 If (get_orphan_revs%NOTFOUND) then
374 l_revs_exists := 'N';
375 else
376 l_revs_exists :='Y';
377 l_assembly_item_name := orphan_revs_rec.INVENTORY_ITEM_NUMBER;
378 l_org_code := orphan_revs_rec.organization_code;
379 end if; --get_orphan_revs%NOTFOUND
380 CLOSE get_orphan_revs;
381 ELSIF (l_op_exists ='Y') then
382 OPEN get_orphan_op_seqs;
383 FETCH get_orphan_op_seqs into orphan_op_seqs_rec;
384 If (get_orphan_op_seqs%NOTFOUND) then
385 l_op_exists := 'N';
386 else
387 l_op_exists :='Y';
388 l_assembly_item_name := orphan_op_seqs_rec.assembly_item_number;
389 l_org_code := orphan_op_seqs_rec.organization_code;
390 l_alt_rtg_desig := orphan_op_seqs_rec.alternate_routing_designator;
391 end if; --get_orphan_op_seqs%NOTFOUND
392 CLOSE get_orphan_op_seqs;
393 ELSIF (l_op_res_exists ='Y') then
394 OPEN get_orphan_op_resources;
395 FETCH get_orphan_op_resources into orphan_op_res_rec;
396
397 If (get_orphan_op_resources%NOTFOUND) then
398 l_op_res_exists := 'N';
399 else
400 l_op_res_exists :='Y';
401 l_assembly_item_name := orphan_op_res_rec.assembly_item_number;
402 l_org_code := orphan_op_res_rec.organization_code;
403 l_alt_rtg_desig := orphan_op_res_rec.alternate_routing_designator;
404 end if; -- get_orphan_op_resources%NOTFOUND
405 CLOSE get_orphan_op_resources;
406 ELSIF (l_sub_op_res_exists ='Y') then
407 OPEN get_orphan_sub_op_resources;
408 FETCH get_orphan_sub_op_resources into orphan_sub_op_res_rec;
409
410 If (get_orphan_sub_op_resources%NOTFOUND) then
411 l_sub_op_res_exists := 'N';
412 else
413 l_sub_op_res_exists :='Y';
414 l_assembly_item_name := orphan_sub_op_res_rec.assembly_item_number;
415 l_org_code := orphan_sub_op_res_rec.organization_code;
416 l_alt_rtg_desig := orphan_sub_op_res_rec.alternate_routing_designator;
417 end if; -- get_orphan_sub_op_resources%NOTFOUND
418 CLOSE get_orphan_sub_op_resources;
419 ELSIF (l_nwk_exists ='Y') then
420 OPEN get_orphan_op_nwk;
421 FETCH get_orphan_op_nwk into orphan_op_nwk_rec;
422
423 If (get_orphan_op_nwk%NOTFOUND) then
424 l_nwk_exists := 'N';
425 EXIT ; -- MAIN LOOP
426 else
427 l_nwk_exists :='Y';
428 l_assembly_item_name := orphan_op_nwk_rec.assembly_item_number;
429 l_org_code := orphan_op_nwk_rec.organization_code;
430 l_alt_rtg_desig := orphan_op_nwk_rec.alternate_routing_designator;
431 end if; --get_orphan_op_nwk%NOTFOUND
432 CLOSE get_orphan_op_nwk;
433 END IF;
434
435 /* Populate the interface records into the BO variables and call BO */
436 stmt_num := 4;
437 IF (l_assembly_item_name IS NOT NULL and l_org_code is not null) THEN
438
439 If (l_rtg_header_exists = 'Y' ) THEN
440 empty_bo := 'N';
441
442 IF ( rtg_header_rec.TRANSACTION_TYPE <> 'NO_OP' )
443 THEN -- Bug 3411601
444
445 g_rtg_header_rec.Assembly_Item_Name := l_assembly_item_name;--rtg_header_rec.ASSEMBLY_ITEM_NUMBER
446 g_rtg_header_rec.Organization_Code := l_org_code;--rtg_header_rec.ORGANIZATION_CODE
447 g_rtg_header_rec.Alternate_Routing_Code := l_alt_rtg_desig;--rtg_header_rec.ALTERNATE_ROUTING_DESIGNATOR
448 IF rtg_header_rec.ROUTING_TYPE = 1 THEN-- 2=Engineering routing 1=Manufacturing routing
449 g_rtg_header_rec.Eng_Routing_Flag := 2;
450 ELSIF rtg_header_rec.ROUTING_TYPE = 2 THEN
451 g_rtg_header_rec.Eng_Routing_Flag := 1;
452 END IF;
453 g_rtg_header_rec.Common_Assembly_Item_Name := rtg_header_rec.COMMON_ITEM_NUMBER;
454 g_rtg_header_rec.Routing_Comment := rtg_header_rec.ROUTING_COMMENT;
455 g_rtg_header_rec.Completion_Subinventory := rtg_header_rec.COMPLETION_SUBINVENTORY;
456 g_rtg_header_rec.Completion_Location_Name := rtg_header_rec.LOCATION_NAME;
457 g_rtg_header_rec.Line_Code := rtg_header_rec.LINE_CODE;
458 g_rtg_header_rec.CFM_Routing_Flag := rtg_header_rec.CFM_ROUTING_FLAG;
459 g_rtg_header_rec.Mixed_Model_Map_Flag := rtg_header_rec.MIXED_MODEL_MAP_FLAG;
460 g_rtg_header_rec.Priority := rtg_header_rec.PRIORITY;
461 g_rtg_header_rec.Total_Cycle_Time := rtg_header_rec.TOTAL_PRODUCT_CYCLE_TIME;
462 g_rtg_header_rec.CTP_Flag := rtg_header_rec.CTP_FLAG;
463 g_rtg_header_rec.Attribute_category := rtg_header_rec.ATTRIBUTE_CATEGORY;
464 g_rtg_header_rec.Attribute1 := rtg_header_rec.ATTRIBUTE1;
465 g_rtg_header_rec.Attribute2 := rtg_header_rec.ATTRIBUTE2;
466 g_rtg_header_rec.Attribute3 := rtg_header_rec.ATTRIBUTE3;
467 g_rtg_header_rec.Attribute4 := rtg_header_rec.ATTRIBUTE4;
468 g_rtg_header_rec.Attribute5 := rtg_header_rec.ATTRIBUTE5;
469 g_rtg_header_rec.Attribute6 := rtg_header_rec.ATTRIBUTE6;
470 g_rtg_header_rec.Attribute7 := rtg_header_rec.ATTRIBUTE7;
471 g_rtg_header_rec.Attribute8 := rtg_header_rec.ATTRIBUTE8;
472 g_rtg_header_rec.Attribute9 := rtg_header_rec.ATTRIBUTE9;
473 g_rtg_header_rec.Attribute10 := rtg_header_rec.ATTRIBUTE10;
474 g_rtg_header_rec.Attribute11 := rtg_header_rec.ATTRIBUTE11;
475 g_rtg_header_rec.Attribute12 := rtg_header_rec.ATTRIBUTE12;
476 g_rtg_header_rec.Attribute13 := rtg_header_rec.ATTRIBUTE13;
477 g_rtg_header_rec.Attribute14 := rtg_header_rec.ATTRIBUTE14;
478 g_rtg_header_rec.Attribute15 := rtg_header_rec.ATTRIBUTE15;
479 g_rtg_header_rec.Original_System_Reference := rtg_header_rec.ORIGINAL_SYSTEM_REFERENCE; -- newly added
480 g_rtg_header_rec.Transaction_Type := rtg_header_rec.TRANSACTION_TYPE;
481 g_rtg_header_rec.Delete_Group_Name := rtg_header_rec.DELETE_GROUP_NAME; -- newly added
482 g_rtg_header_rec.DG_Description := rtg_header_rec.DG_DESCRIPTION; -- newly added
483 g_rtg_header_rec.Ser_Start_Op_Seq := rtg_header_rec.SERIALIZATION_START_OP; -- newly added
484 g_rtg_header_rec.Row_Identifier := rtg_header_rec.TRANSACTION_ID; -- newly added
485 g_rtg_header_rec.Return_Status := '';
486
487 END IF;
488 END IF;
489 stmt_num := 5;
490
491 If (l_rtg_header_exists = 'Y' or l_op_exists = 'Y' or l_revs_exists = 'Y') THEN
492
493 OPEN get_rtg_revs (l_assembly_item_name, l_org_code);
494 i := 0;
495 LOOP
496 FETCH get_rtg_revs into rtg_revs_rec;
497 EXIT WHEN get_rtg_revs%NOTFOUND;
498
499 i:=i+1;
500 if (i=1) then empty_bo := 'N';
501 end if;
502
503 -- Bug 5970070. Added nvl condition for start_effective_date
504 g_rtg_revs_tbl(i).Assembly_Item_Name := l_assembly_item_name; --rtg_revs_rec.ITEM_NUMBER
505 g_rtg_revs_tbl(i).Organization_Code := l_org_code; --rtg_revs_rec.ORGANIZATION_CODE
506 g_rtg_revs_tbl(i).Alternate_Routing_Code := l_alt_rtg_desig; --rtg_revs_rec.
507 g_rtg_revs_tbl(i).Revision := rtg_revs_rec.PROCESS_REVISION;
508 g_rtg_revs_tbl(i).Start_Effective_Date := nvl(rtg_revs_rec.EFFECTIVITY_DATE, sysdate + 1/1440);
509 g_rtg_revs_tbl(i).Attribute_category := rtg_revs_rec.ATTRIBUTE_CATEGORY;
510 g_rtg_revs_tbl(i).Attribute1 := rtg_revs_rec.ATTRIBUTE1;
511 g_rtg_revs_tbl(i).Attribute2 := rtg_revs_rec.ATTRIBUTE2;
512 g_rtg_revs_tbl(i).Attribute3 := rtg_revs_rec.ATTRIBUTE3;
513 g_rtg_revs_tbl(i).Attribute4 := rtg_revs_rec.ATTRIBUTE4;
514 g_rtg_revs_tbl(i).Attribute5 := rtg_revs_rec.ATTRIBUTE5;
515 g_rtg_revs_tbl(i).Attribute6 := rtg_revs_rec.ATTRIBUTE6;
516 g_rtg_revs_tbl(i).Attribute7 := rtg_revs_rec.ATTRIBUTE7;
517 g_rtg_revs_tbl(i).Attribute8 := rtg_revs_rec.ATTRIBUTE8;
518 g_rtg_revs_tbl(i).Attribute9 := rtg_revs_rec.ATTRIBUTE9;
519 g_rtg_revs_tbl(i).Attribute10 := rtg_revs_rec.ATTRIBUTE10;
520 g_rtg_revs_tbl(i).Attribute11 := rtg_revs_rec.ATTRIBUTE11;
521 g_rtg_revs_tbl(i).Attribute12 := rtg_revs_rec.ATTRIBUTE12;
522 g_rtg_revs_tbl(i).Attribute13 := rtg_revs_rec.ATTRIBUTE13;
523 g_rtg_revs_tbl(i).Attribute14 := rtg_revs_rec.ATTRIBUTE14;
524 g_rtg_revs_tbl(i).Attribute15 := rtg_revs_rec.ATTRIBUTE15;
525 g_rtg_revs_tbl(i).Original_System_Reference := rtg_revs_rec.ORIGINAL_SYSTEM_REFERENCE;
526 g_rtg_revs_tbl(i).Transaction_Type := rtg_revs_rec.TRANSACTION_TYPE;
527 g_rtg_revs_tbl(i).Row_Identifier := rtg_revs_rec.TRANSACTION_ID;
528 g_rtg_revs_tbl(i).Return_Status:='';
529
530 END LOOP;
531 CLOSE get_rtg_revs;
532 stmt_num:= 6;
533 OPEN get_op_seqs (l_assembly_item_name, l_org_code, l_alt_rtg_desig);
534
535 i := 0;
536 LOOP
537 FETCH get_op_seqs into op_seqs_rec;
538 EXIT WHEN get_op_seqs%NOTFOUND;
539
540 i:=i+1;
541 if (i=1) then empty_bo := 'N';
542 end if;
543
544 g_op_tbl(i).Assembly_Item_Name := l_assembly_item_name; --op_seqs_rec.ASSEMBLY_ITEM_NUMBER
545 g_op_tbl(i).Organization_Code := l_org_code; --op_seqs_rec.ORGANIZATION_CODE
546 g_op_tbl(i).Alternate_Routing_Code := l_alt_rtg_desig; --op_seqs_rec.ALTERNATE_ROUTING_DESIGNATOR
547 g_op_tbl(i).Operation_Sequence_Number := op_seqs_rec.OPERATION_SEQ_NUM;
548 g_op_tbl(i).Operation_Type := op_seqs_rec.OPERATION_TYPE;
549 g_op_tbl(i).Start_Effective_Date := op_seqs_rec.EFFECTIVITY_DATE;
550 g_op_tbl(i).New_Operation_Sequence_Number := op_seqs_rec.NEW_OPERATION_SEQ_NUM;
551 g_op_tbl(i).New_Start_Effective_Date := op_seqs_rec.NEW_EFFECTIVITY_DATE;
552 g_op_tbl(i).Standard_Operation_Code := op_seqs_rec.OPERATION_CODE;
553 g_op_tbl(i).Department_Code := op_seqs_rec.DEPARTMENT_CODE;
554 g_op_tbl(i).Op_Lead_Time_Percent := op_seqs_rec.OPERATION_LEAD_TIME_PERCENT;
555 g_op_tbl(i).Minimum_Transfer_Quantity := op_seqs_rec.MINIMUM_TRANSFER_QUANTITY;
556 g_op_tbl(i).Count_Point_Type := op_seqs_rec.COUNT_POINT_TYPE;
557 g_op_tbl(i).Operation_Description := op_seqs_rec.OPERATION_DESCRIPTION;
558 g_op_tbl(i).Disable_Date := op_seqs_rec.DISABLE_DATE;
559 g_op_tbl(i).Backflush_Flag := op_seqs_rec.BACKFLUSH_FLAG;
560 g_op_tbl(i).Option_Dependent_Flag := op_seqs_rec.OPTION_DEPENDENT_FLAG;
561 g_op_tbl(i).Reference_Flag := op_seqs_rec.REFERENCE_FLAG;
562 g_op_tbl(i).Process_Seq_Number := op_seqs_rec.PROCESS_SEQ_NUMBER; --newly added
563 g_op_tbl(i).Process_Code := op_seqs_rec.PROCESS_CODE; --newly added
564 g_op_tbl(i).Line_Op_Seq_Number := op_seqs_rec.LINE_OP_SEQ_NUMBER; --newly added
565 g_op_tbl(i).Line_Op_Code := op_seqs_rec.LINE_OP_CODE; --newly added
566 g_op_tbl(i).Yield := op_seqs_rec.YIELD;
567 g_op_tbl(i).Cumulative_Yield := op_seqs_rec.CUMULATIVE_YIELD;
568 g_op_tbl(i).Reverse_CUM_Yield := op_seqs_rec.REVERSE_CUMULATIVE_YIELD;
569 g_op_tbl(i).User_Labor_Time := op_seqs_rec.LABOR_TIME_USER;
570 g_op_tbl(i).User_Machine_Time := op_seqs_rec.MACHINE_TIME_USER;
571 g_op_tbl(i).Net_Planning_Percent := op_seqs_rec.NET_PLANNING_PERCENT;
572 g_op_tbl(i).Include_In_Rollup := op_seqs_rec.INCLUDE_IN_ROLLUP;
573 g_op_tbl(i).Op_Yield_Enabled_Flag := op_seqs_rec.OPERATION_YIELD_ENABLED;
574 g_op_tbl(i).Shutdown_Type := op_seqs_rec.SHUTDOWN_TYPE; --newly added
575 g_op_tbl(i).Attribute_category := op_seqs_rec.ATTRIBUTE_CATEGORY;
576 g_op_tbl(i).Attribute1 := op_seqs_rec.ATTRIBUTE1;
577 g_op_tbl(i).Attribute2 := op_seqs_rec.ATTRIBUTE2;
578 g_op_tbl(i).Attribute3 := op_seqs_rec.ATTRIBUTE3;
579 g_op_tbl(i).Attribute4 := op_seqs_rec.ATTRIBUTE4;
580 g_op_tbl(i).Attribute5 := op_seqs_rec.ATTRIBUTE5;
581 g_op_tbl(i).Attribute6 := op_seqs_rec.ATTRIBUTE6;
582 g_op_tbl(i).Attribute7 := op_seqs_rec.ATTRIBUTE7;
583 g_op_tbl(i).Attribute8 := op_seqs_rec.ATTRIBUTE8;
584 g_op_tbl(i).Attribute9 := op_seqs_rec.ATTRIBUTE9;
585 g_op_tbl(i).Attribute10 := op_seqs_rec.ATTRIBUTE10;
586 g_op_tbl(i).Attribute11 := op_seqs_rec.ATTRIBUTE11;
587 g_op_tbl(i).Attribute12 := op_seqs_rec.ATTRIBUTE12;
588 g_op_tbl(i).Attribute13 := op_seqs_rec.ATTRIBUTE13;
589 g_op_tbl(i).Attribute14 := op_seqs_rec.ATTRIBUTE14;
590 g_op_tbl(i).Attribute15 := op_seqs_rec.ATTRIBUTE15;
591 g_op_tbl(i).Original_System_Reference := op_seqs_rec.ORIGINAL_SYSTEM_REFERENCE; --newly added
592 g_op_tbl(i).Transaction_Type := op_seqs_rec.TRANSACTION_TYPE;
593 g_op_tbl(i).Delete_Group_Name := op_seqs_rec.DELETE_GROUP_NAME; --newly added
594 g_op_tbl(i).DG_Description := op_seqs_rec.DG_DESCRIPTION; --newly added
595 g_op_tbl(i).Long_Description := op_seqs_rec.LONG_DESCRIPTION; --newly added
596 g_op_tbl(i).Row_Identifier := op_seqs_rec.TRANSACTION_ID; --newly added
597 g_op_tbl(i).Return_Status:='';
598
599 END LOOP;
600 CLOSE get_op_seqs;
601 stmt_num:= 7;
602 END IF; -- l_rtg_header_exists or l_op_exists or l_revs_exists
603
604 OPEN get_op_resources (l_assembly_item_name, l_org_code, l_alt_rtg_desig);
605 i := 0;
606 LOOP
607 FETCH get_op_resources into op_res_rec;
608 EXIT WHEN get_op_resources%NOTFOUND;
609 i:=i+1;
610
611 if (i=1) then empty_bo := 'N';
612 end if;
613
614 g_op_res_tbl(i).Assembly_Item_Name := l_assembly_item_name; --op_res_rec.
615 g_op_res_tbl(i).Organization_Code := l_org_code; --op_res_rec.
616 g_op_res_tbl(i).Alternate_Routing_Code := l_alt_rtg_desig; --op_res_rec.
617 g_op_res_tbl(i).Operation_Sequence_Number := op_res_rec.OPERATION_SEQ_NUM;
618 g_op_res_tbl(i).Operation_Type := op_res_rec.OPERATION_TYPE;
619 g_op_res_tbl(i).Op_Start_Effective_Date := op_res_rec.EFFECTIVITY_DATE;
620 g_op_res_tbl(i).Resource_Sequence_Number := op_res_rec.RESOURCE_SEQ_NUM;
621 g_op_res_tbl(i).Resource_Code := op_res_rec.RESOURCE_CODE;
622 g_op_res_tbl(i).Activity := op_res_rec.ACTIVITY;
623 g_op_res_tbl(i).Standard_Rate_Flag := op_res_rec.STANDARD_RATE_FLAG;
624 g_op_res_tbl(i).Assigned_Units := op_res_rec.ASSIGNED_UNITS;
625 g_op_res_tbl(i).Usage_Rate_Or_Amount := ROUND(op_res_rec.USAGE_RATE_OR_AMOUNT,6);
626 g_op_res_tbl(i).Usage_Rate_Or_Amount_Inverse := ROUND(op_res_rec.USAGE_RATE_OR_AMOUNT_INVERSE,6);
627 g_op_res_tbl(i).Basis_Type := op_res_rec.BASIS_TYPE;
628 g_op_res_tbl(i).Schedule_Flag := op_res_rec.SCHEDULE_FLAG;
629 g_op_res_tbl(i).Resource_Offset_Percent := op_res_rec.RESOURCE_OFFSET_PERCENT;
630 g_op_res_tbl(i).Autocharge_Type := op_res_rec.AUTOCHARGE_TYPE;
631 g_op_res_tbl(i).Schedule_Sequence_Number := op_res_rec.SCHEDULE_SEQ_NUM;
632 g_op_res_tbl(i).Substitute_Group_Number := op_res_rec.SUBSTITUTE_GROUP_NUM;
633 g_op_res_tbl(i).Principle_Flag := op_res_rec.PRINCIPLE_FLAG;
634 g_op_res_tbl(i).Attribute_category := op_res_rec.ATTRIBUTE_CATEGORY;
635 g_op_res_tbl(i).Attribute1 := op_res_rec.ATTRIBUTE1;
636 g_op_res_tbl(i).Attribute2 := op_res_rec.ATTRIBUTE2;
637 g_op_res_tbl(i).Attribute3 := op_res_rec.ATTRIBUTE3;
638 g_op_res_tbl(i).Attribute4 := op_res_rec.ATTRIBUTE4;
639 g_op_res_tbl(i).Attribute5 := op_res_rec.ATTRIBUTE5;
640 g_op_res_tbl(i).Attribute6 := op_res_rec.ATTRIBUTE6;
641 g_op_res_tbl(i).Attribute7 := op_res_rec.ATTRIBUTE7;
642 g_op_res_tbl(i).Attribute8 := op_res_rec.ATTRIBUTE8;
643 g_op_res_tbl(i).Attribute9 := op_res_rec.ATTRIBUTE9;
644 g_op_res_tbl(i).Attribute10 := op_res_rec.ATTRIBUTE10;
645 g_op_res_tbl(i).Attribute11 := op_res_rec.ATTRIBUTE11;
646 g_op_res_tbl(i).Attribute12 := op_res_rec.ATTRIBUTE12;
647 g_op_res_tbl(i).Attribute13 := op_res_rec.ATTRIBUTE13;
648 g_op_res_tbl(i).Attribute14 := op_res_rec.ATTRIBUTE14;
649 g_op_res_tbl(i).Attribute15 := op_res_rec.ATTRIBUTE15;
650 g_op_res_tbl(i).Original_System_Reference := op_res_rec.ORIGINAL_SYSTEM_REFERENCE;
651 g_op_res_tbl(i).Transaction_Type := op_res_rec.TRANSACTION_TYPE;
652 g_op_res_tbl(i).Setup_Type := op_res_rec.SETUP_CODE;
653 g_op_res_tbl(i).Row_Identifier := op_res_rec.TRANSACTION_ID;
654 g_op_res_tbl(i).Return_Status := '';
655
656 END LOOP;
657 CLOSE get_op_resources;
658
659 stmt_num:= 8;
660
661 OPEN get_sub_op_resources(l_assembly_item_name, l_org_code, l_alt_rtg_desig);
662 i := 0;
663 LOOP
664 FETCH get_sub_op_resources into sub_op_res_rec;
665 EXIT WHEN get_sub_op_resources%NOTFOUND;
666 i:=i+1;
667 if (i=1) then empty_bo := 'N';
668 end if;
669
670 g_sub_op_res_tbl(i).Assembly_Item_Name := l_assembly_item_name; --sub_op_res_rec.
671 g_sub_op_res_tbl(i).Organization_Code := l_org_code; --sub_op_res_rec.
672 g_sub_op_res_tbl(i).Alternate_Routing_Code := l_alt_rtg_desig; --sub_op_res_rec.
673 g_sub_op_res_tbl(i).Operation_Sequence_Number := sub_op_res_rec.OPERATION_SEQ_NUM;
674 g_sub_op_res_tbl(i).Operation_Type := sub_op_res_rec.OPERATION_TYPE;
675 g_sub_op_res_tbl(i).Op_Start_Effective_Date := sub_op_res_rec.EFFECTIVITY_DATE;
676 g_sub_op_res_tbl(i).Sub_Resource_Code := sub_op_res_rec.SUB_RESOURCE_CODE;
677 g_sub_op_res_tbl(i).New_Sub_Resource_Code := sub_op_res_rec.NEW_SUB_RESOURCE_CODE;
678 --g_sub_op_res_tbl(i).Schedule_Sequence_Number := nvl(sub_op_res_rec.SCHEDULE_SEQ_NUM, sub_op_res_rec.SUBSTITUTE_GROUP_NUM);
679
680 g_sub_op_res_tbl(i).Schedule_Sequence_Number := sub_op_res_rec.SCHEDULE_SEQ_NUM;
681 g_sub_op_res_tbl(i).Substitute_Group_Number := sub_op_res_rec.SUBSTITUTE_GROUP_NUM;
682 g_sub_op_res_tbl(i).Replacement_Group_Number := sub_op_res_rec.REPLACEMENT_GROUP_NUM;
683 g_sub_op_res_tbl(i).New_Replacement_Group_Number := sub_op_res_rec.NEW_REPLACEMENT_GROUP_NUM; -- bug 3741570
684 g_sub_op_res_tbl(i).Activity := sub_op_res_rec.ACTIVITY;
685 g_sub_op_res_tbl(i).Standard_Rate_Flag := sub_op_res_rec.STANDARD_RATE_FLAG;
686 g_sub_op_res_tbl(i).Assigned_Units := sub_op_res_rec.ASSIGNED_UNITS;
687 g_sub_op_res_tbl(i).Usage_Rate_Or_Amount := ROUND(sub_op_res_rec.USAGE_RATE_OR_AMOUNT,6);
688 g_sub_op_res_tbl(i).Usage_Rate_Or_Amount_Inverse := ROUND(sub_op_res_rec.USAGE_RATE_OR_AMOUNT_INVERSE,6);
689 g_sub_op_res_tbl(i).Basis_Type := sub_op_res_rec.BASIS_TYPE;
690 g_sub_op_res_tbl(i).New_Basis_Type := sub_op_res_rec.New_BASIS_TYPE; /*Added for bug 4689856 */
691 g_sub_op_res_tbl(i).Schedule_Flag := sub_op_res_rec.SCHEDULE_FLAG;
692 g_sub_op_res_tbl(i).Resource_Offset_Percent := sub_op_res_rec.RESOURCE_OFFSET_PERCENT;
693 g_sub_op_res_tbl(i).Autocharge_Type := sub_op_res_rec.AUTOCHARGE_TYPE;
694 g_sub_op_res_tbl(i).Principle_Flag := sub_op_res_rec.PRINCIPLE_FLAG;
695 g_sub_op_res_tbl(i).Attribute_category := sub_op_res_rec.ATTRIBUTE_CATEGORY;
696 g_sub_op_res_tbl(i).Attribute1 := sub_op_res_rec.ATTRIBUTE1;
697 g_sub_op_res_tbl(i).Attribute2 := sub_op_res_rec.ATTRIBUTE2;
698 g_sub_op_res_tbl(i).Attribute3 := sub_op_res_rec.ATTRIBUTE3;
699 g_sub_op_res_tbl(i).Attribute4 := sub_op_res_rec.ATTRIBUTE4;
700 g_sub_op_res_tbl(i).Attribute5 := sub_op_res_rec.ATTRIBUTE5;
701 g_sub_op_res_tbl(i).Attribute6 := sub_op_res_rec.ATTRIBUTE6;
702 g_sub_op_res_tbl(i).Attribute7 := sub_op_res_rec.ATTRIBUTE7;
703 g_sub_op_res_tbl(i).Attribute8 := sub_op_res_rec.ATTRIBUTE8;
704 g_sub_op_res_tbl(i).Attribute9 := sub_op_res_rec.ATTRIBUTE9;
705 g_sub_op_res_tbl(i).Attribute10 := sub_op_res_rec.ATTRIBUTE10;
706 g_sub_op_res_tbl(i).Attribute11 := sub_op_res_rec.ATTRIBUTE11;
707 g_sub_op_res_tbl(i).Attribute12 := sub_op_res_rec.ATTRIBUTE12;
708 g_sub_op_res_tbl(i).Attribute13 := sub_op_res_rec.ATTRIBUTE13;
709 g_sub_op_res_tbl(i).Attribute14 := sub_op_res_rec.ATTRIBUTE14;
710 g_sub_op_res_tbl(i).Attribute15 := sub_op_res_rec.ATTRIBUTE15;
711 g_sub_op_res_tbl(i).Original_System_Reference := sub_op_res_rec.ORIGINAL_SYSTEM_REFERENCE;
712 g_sub_op_res_tbl(i).Transaction_Type := sub_op_res_rec.TRANSACTION_TYPE;
713 g_sub_op_res_tbl(i).Setup_Type := sub_op_res_rec.SETUP_CODE;
714 g_sub_op_res_tbl(i).Row_Identifier := sub_op_res_rec.TRANSACTION_ID;
715 g_sub_op_res_tbl(i).Return_Status := '';
716
717 END LOOP;
718 CLOSE get_sub_op_resources;
719
720 stmt_num:= 9;
721
722 OPEN get_op_nwk(l_assembly_item_name, l_org_code, l_alt_rtg_desig);
723 i := 0;
724 LOOP
725 FETCH get_op_nwk into op_nwk_rec;
726 EXIT WHEN get_op_nwk%NOTFOUND;
727 i:=i+1;
728 if (i=1) then empty_bo := 'N';
729 end if;
730
731 g_nwk_tbl(i).Assembly_Item_Name := l_assembly_item_name;
732 g_nwk_tbl(i).Organization_Code := l_org_code;
733 g_nwk_tbl(i).Alternate_Routing_Code := l_alt_rtg_desig;
734 g_nwk_tbl(i).Operation_Type := op_nwk_rec.OPERATION_TYPE;
735 g_nwk_tbl(i).From_Op_Seq_Number := op_nwk_rec.FROM_OP_SEQ_NUMBER;
736 g_nwk_tbl(i).From_X_Coordinate := op_nwk_rec.FROM_X_COORDINATE;
737 g_nwk_tbl(i).From_Y_Coordinate := op_nwk_rec.FROM_Y_COORDINATE;
738 g_nwk_tbl(i).From_Start_Effective_Date := op_nwk_rec.FROM_START_EFFECTIVE_DATE;
739 g_nwk_tbl(i).To_Op_Seq_Number := op_nwk_rec.TO_OP_SEQ_NUMBER;
740 g_nwk_tbl(i).To_X_Coordinate := op_nwk_rec.TO_X_COORDINATE;
741 g_nwk_tbl(i).To_Y_Coordinate := op_nwk_rec.TO_Y_COORDINATE;
742 g_nwk_tbl(i).To_Start_Effective_Date := op_nwk_rec.TO_START_EFFECTIVE_DATE;
743 g_nwk_tbl(i).New_From_Op_Seq_Number := op_nwk_rec.NEW_FROM_OP_SEQ_NUMBER;
744 g_nwk_tbl(i).New_From_Start_Effective_Date := op_nwk_rec.NEW_FROM_START_EFFECTIVE_DATE;
745 g_nwk_tbl(i).New_To_Op_Seq_Number := op_nwk_rec.NEW_TO_OP_SEQ_NUMBER;
746 g_nwk_tbl(i).New_To_Start_Effective_Date := op_nwk_rec.NEW_TO_START_EFFECTIVE_DATE;
747 g_nwk_tbl(i).Connection_Type := op_nwk_rec.TRANSITION_TYPE;
748 g_nwk_tbl(i).Planning_Percent := op_nwk_rec.PLANNING_PCT;
749 g_nwk_tbl(i).Attribute_category := op_nwk_rec.ATTRIBUTE_CATEGORY;
750 g_nwk_tbl(i).Attribute1 := op_nwk_rec.ATTRIBUTE1;
751 g_nwk_tbl(i).Attribute2 := op_nwk_rec.ATTRIBUTE2;
752 g_nwk_tbl(i).Attribute3 := op_nwk_rec.ATTRIBUTE3;
753 g_nwk_tbl(i).Attribute4 := op_nwk_rec.ATTRIBUTE4;
754 g_nwk_tbl(i).Attribute5 := op_nwk_rec.ATTRIBUTE5;
755 g_nwk_tbl(i).Attribute6 := op_nwk_rec.ATTRIBUTE6;
756 g_nwk_tbl(i).Attribute7 := op_nwk_rec.ATTRIBUTE7;
757 g_nwk_tbl(i).Attribute8 := op_nwk_rec.ATTRIBUTE8;
758 g_nwk_tbl(i).Attribute9 := op_nwk_rec.ATTRIBUTE9;
759 g_nwk_tbl(i).Attribute10 := op_nwk_rec.ATTRIBUTE10;
760 g_nwk_tbl(i).Attribute11 := op_nwk_rec.ATTRIBUTE11;
761 g_nwk_tbl(i).Attribute12 := op_nwk_rec.ATTRIBUTE12;
762 g_nwk_tbl(i).Attribute13 := op_nwk_rec.ATTRIBUTE13;
763 g_nwk_tbl(i).Attribute14 := op_nwk_rec.ATTRIBUTE14;
764 g_nwk_tbl(i).Attribute15 := op_nwk_rec.ATTRIBUTE15;
765 g_nwk_tbl(i).Original_System_Reference := op_nwk_rec.ORIGINAL_SYSTEM_REFERENCE;
766 g_nwk_tbl(i).Transaction_Type := op_nwk_rec.TRANSACTION_TYPE;
767 g_nwk_tbl(i).Row_Identifier := op_nwk_rec.TRANSACTION_ID;
768 g_nwk_tbl(i).Return_Status := '';
769
770 END LOOP;
771 CLOSE get_op_nwk;
772
773 stmt_num:=10;
774
775 SAVEPOINT osfm_rtg_check ; --for osfm routings bug#3134027
776
777 if (empty_bo ='N') then -- Calling the RBO public API
778 bom_rtg_pub.process_rtg
779 ( p_bo_identifier => 'RTG'
780 , p_api_version_number => 1.0
781 , p_init_msg_list => TRUE
782 , p_rtg_header_rec => g_rtg_header_rec
783 , p_rtg_revision_tbl => g_rtg_revs_tbl
784 , p_operation_tbl => g_op_tbl
785 , p_op_resource_tbl => g_op_res_tbl
786 , p_sub_resource_tbl => g_sub_op_res_tbl
787 , p_op_network_tbl => g_nwk_tbl
788 , x_rtg_header_rec => g_rtg_header_rec
789 , x_rtg_revision_tbl => g_rtg_revs_tbl
790 , x_operation_tbl => g_op_tbl
791 , x_op_resource_tbl => g_op_res_tbl
792 , x_sub_resource_tbl => g_sub_op_res_tbl
793 , x_op_network_tbl => g_nwk_tbl
794 , x_return_status => ret_status
795 , x_msg_count => l_msg_count
796 , p_debug => debug_on
797 , p_output_dir => 'none'
798 , p_debug_filename => 'none'
799 );
800
801 end if;
802 --for osfm routings bug#3134027
803
804 IF ret_status = 'E' AND BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_LOT_RTG
805 THEN
806 ROLLBACK TO osfm_rtg_check ;
807 ELSE
808 /*Even if the BO has returned Error, some of the rows might be successfull.
809 All the sucessful rows will be commited since bo will not update errored rows*/
810 COMMIT;
811 END IF;
812 stmt_num:= 11;
813 -- Error_handling for the openInterface
814 Error_handler.Write_To_ConcurrentLog;
815 Error_handler.Write_To_InterfaceTable;
816
817 stmt_num := 12;
818 l_return_status := Update_Rtg_Interface_tables (x_err_text);
819 IF (l_return_status NOT IN (0,1) ) THEN
820 RETURN(l_return_status);
821 ELSIF ( l_return_status = 1 ) THEN
822 l_func_ret_status := 1;
823 END IF;
824
825 COMMIT;
826 END IF; -- L_ASSEMBLY_ITEM_NUMBER AND L_ORG_CODE NOT NULL.
827 END LOOP;
828
829 -- Unset the Global Variable to Routing Open Interface type
830 Error_Handler.unset_bom_oi;
831
832 stmt_num := 13;
833 if(p_delete_rows = 1) then
834 l_return_status := Delete_Rtg_OI(x_err_text, p_batch_id);
835 IF (l_return_status <> 0) THEN
836 RETURN(l_return_status);
837 END IF;
838 end if;
839
840 stmt_num := 14;
841 -- check if the main cursor is open and then close it
842 If (get_rtg_header%ISOPEN) then
843 close get_rtg_header;
844 end if;
845
846 --return (0);
847 return (l_func_ret_status);
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 x_err_text := 'IMPORT_RTG :'||stmt_num||substrb(SQLERRM,1,500);
852 RETURN(SQLCODE);
853 End IMPORT_RTG;
854
855
856 Function Update_Rtg_Interface_tables (x_err_text IN OUT NOCOPY VARCHAR2)
857 return Integer
858 Is
859 l_process_flag Number;
860 stmt_num Number;
861 l_ret_status NUMBER;
862 begin
863 --bug:5235742 When import completes with one or more entities having errors, return 1.
864 l_ret_status := 0;
865 stmt_num := 0;
866 if g_rtg_header_rec.Return_Status IS NULL then
867 l_process_flag := 1;
868 elsif (g_rtg_header_rec.Return_Status = 'S') then
869 l_process_flag := 7;
870 else
871 l_process_flag := 3;
872 l_ret_status := 1;
873 end if;
874
875 Update BOM_OP_ROUTINGS_INTERFACE
876 set process_flag = l_process_flag,
877 REQUEST_ID = Fnd_Global.Conc_Request_Id,
878 PROGRAM_ID = Fnd_Global.Conc_program_Id,
879 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
880 PROGRAM_UPDATE_DATE = sysdate
881 where transaction_id = g_rtg_header_rec.Row_Identifier;
882
883 stmt_num := 1;
884 FOR I IN 1..g_rtg_revs_tbl.COUNT LOOP
885
886 g_rtg_revision_rec := g_rtg_revs_tbl(I);
887
888 if g_rtg_revision_rec.Return_Status IS NULL then
889 l_process_flag := 1;
890 elsif (g_rtg_revision_rec.Return_Status = 'S') then
891 l_process_flag := 7;
892 else
893 l_process_flag := 3;
894 l_ret_status := 1;
895 end if;
896
897 Update MTL_RTG_ITEM_REVS_INTERFACE
898 set process_flag = l_process_flag,
899 REQUEST_ID = Fnd_Global.Conc_Request_Id,
900 PROGRAM_ID = Fnd_Global.Conc_program_Id,
901 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
902 PROGRAM_UPDATE_DATE = sysdate
903 where transaction_id = g_rtg_revision_rec.row_identifier;
904 END LOOP;
905
906 stmt_num := 2;
907 FOR I IN 1..g_op_tbl.COUNT LOOP
908
909 g_op_rec := g_op_tbl(I);
910
911 if g_op_rec.Return_Status IS NULL then
912 l_process_flag := 1;
913 elsif (g_op_rec.Return_Status = 'S') then
914 l_process_flag := 7;
915 else
916 l_process_flag := 3;
917 l_ret_status := 1;
918 end if;
919
920 Update BOM_OP_SEQUENCES_INTERFACE
921 set process_flag = l_process_flag,
922 REQUEST_ID = Fnd_Global.Conc_Request_Id,
923 PROGRAM_ID = Fnd_Global.Conc_program_Id,
924 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
925 PROGRAM_UPDATE_DATE = sysdate
926 where transaction_id = g_op_rec.row_identifier;
927 END LOOP;
928
929 stmt_num := 3;
930 FOR I IN 1..g_op_res_tbl.COUNT LOOP
931
932 g_op_res_rec := g_op_res_tbl(I);
933
934 if g_op_res_rec.Return_Status IS NULL then
935 l_process_flag := 1;
936 elsif (g_op_res_rec.Return_Status = 'S') then
937 l_process_flag := 7;
938 else
939 l_process_flag := 3;
940 l_ret_status := 1;
941 end if;
942
943 Update BOM_OP_RESOURCES_INTERFACE
944 set process_flag = l_process_flag,
945 REQUEST_ID = Fnd_Global.Conc_Request_Id,
946 PROGRAM_ID = Fnd_Global.Conc_program_Id,
947 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
948 PROGRAM_UPDATE_DATE = sysdate
949 where transaction_id = g_op_res_rec.row_identifier;
950 END LOOP;
951
952 stmt_num := 4;
953 FOR I IN 1..g_sub_op_res_tbl.COUNT LOOP
954 g_sub_op_res_rec := g_sub_op_res_tbl(I);
955
956 if g_sub_op_res_rec.Return_Status IS NULL then
957 l_process_flag := 1;
958 elsif (g_sub_op_res_rec.Return_Status = 'S') then
959 l_process_flag := 7;
960 else
961 l_process_flag := 3;
962 l_ret_status := 1;
963 end if;
964
965 Update BOM_SUB_OP_RESOURCES_INTERFACE
966 set process_flag = l_process_flag,
967 REQUEST_ID = Fnd_Global.Conc_Request_Id,
968 PROGRAM_ID = Fnd_Global.Conc_program_Id,
969 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
970 PROGRAM_UPDATE_DATE = sysdate
971 where transaction_id = g_sub_op_res_rec.row_identifier;
972 END LOOP;
973
974 stmt_num := 5;
975 FOR I IN 1..g_nwk_tbl.COUNT LOOP
976 g_nwk_rec := g_nwk_tbl(I);
977
978 if g_nwk_rec.Return_Status IS NULL then
979 l_process_flag := 1;
980 elsif (g_nwk_rec.Return_Status = 'S') then
981 l_process_flag := 7;
982 else
983 l_process_flag := 3;
984 l_ret_status := 1;
985 end if;
986
987 Update BOM_OP_NETWORKS_INTERFACE
988 set process_flag = l_process_flag,
989 REQUEST_ID = Fnd_Global.Conc_Request_Id,
990 PROGRAM_ID = Fnd_Global.Conc_program_Id,
991 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
992 PROGRAM_UPDATE_DATE = sysdate
993 where transaction_id = g_nwk_rec.row_identifier;
994 END LOOP;
995
996 return (l_ret_status);
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 x_err_text := 'Update_Rtg_Interface_Tables'||stmt_num||substrb(SQLERRM,1,500);
1001 RETURN(SQLCODE);
1002
1003 end Update_Rtg_Interface_tables;
1004
1005 FUNCTION Delete_Rtg_OI (
1006 x_err_text IN OUT NOCOPY VARCHAR2
1007 , p_batch_id IN NUMBER
1008 )
1009 return INTEGER
1010 IS
1011 stmt_num NUMBER;
1012 BEGIN
1013 stmt_num := 1;
1014 loop
1015 DELETE FROM BOM_OP_ROUTINGS_INTERFACE
1016 WHERE PROCESS_FLAG = 7
1017 AND
1018 (
1019 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1020 OR ( p_batch_id = BATCH_ID )
1021 )
1022 AND rownum < 500;
1023 exit when SQL%NOTFOUND ;
1024 commit;
1025 end loop;
1026
1027 stmt_num := 2;
1028 loop
1029 DELETE FROM BOM_OP_SEQUENCES_INTERFACE
1030 WHERE PROCESS_FLAG = 7
1031 AND
1032 (
1033 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1034 OR ( p_batch_id = BATCH_ID )
1035 )
1036 AND rownum < 500;
1037 exit when SQL%NOTFOUND ;
1038 commit;
1039 end loop;
1040
1041 stmt_num := 3;
1042 loop
1043 DELETE FROM BOM_OP_RESOURCES_INTERFACE
1044 WHERE PROCESS_FLAG = 7
1045 AND
1046 (
1047 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1048 OR ( p_batch_id = BATCH_ID )
1049 )
1050 AND rownum < 500;
1051 exit when SQL%NOTFOUND ;
1052 commit;
1053 end loop;
1054
1055 stmt_num := 4;
1056 loop
1057 DELETE FROM BOM_SUB_OP_RESOURCES_INTERFACE
1058 WHERE PROCESS_FLAG = 7
1059 AND
1060 (
1061 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1062 OR ( p_batch_id = BATCH_ID )
1063 )
1064 AND rownum < 500;
1065 exit when SQL%NOTFOUND ;
1066 commit;
1067 end loop;
1068
1069 stmt_num := 5;
1070 loop
1071 DELETE FROM BOM_OP_NETWORKS_INTERFACE
1072 WHERE PROCESS_FLAG = 7
1073 AND
1074 (
1075 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1076 OR ( p_batch_id = BATCH_ID )
1077 )
1078 AND rownum < 500;
1079 exit when SQL%NOTFOUND ;
1080 commit;
1081 end loop;
1082
1083 stmt_num := 6;
1084 loop
1085 DELETE FROM MTL_RTG_ITEM_REVS_INTERFACE
1086 WHERE PROCESS_FLAG = 7
1087 AND
1088 (
1089 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1090 OR ( p_batch_id = BATCH_ID )
1091 )
1092 AND rownum < 500;
1093 exit when SQL%NOTFOUND ;
1094 commit;
1095 end loop;
1096
1097 return(0);
1098
1099 EXCEPTION
1100 when OTHERS THEN
1101 x_err_text := 'DELETE_RTG_OI(' || stmt_num || ')' || substrb(SQLERRM,1,240);
1102 return(SQLCODE);
1103 END Delete_Rtg_OI;
1104
1105 END BOM_RTG_OPEN_INTERFACE;