[Home] [Help]
PACKAGE BODY: APPS.BOM_OPEN_INTERFACE_API
Source
1 PACKAGE BODY Bom_Open_Interface_Api AS
2 /* $Header: BOMPBOIB.pls 120.6.12010000.3 2010/03/24 23:43:02 umajumde ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMPBOIB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Open_Interface_Api
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 22-NOV-02 Vani Hymavathi Initial Creation
21 -- 06-May-05 Abhishek Rudresh Common BOM Attr Update
22 -- 01-JUN-05 Bhavnesh Patel Added Batch Id
23 -- 13-JUL-06 Bhavnesh Patel Added support for Structure Type
24 ***************************************************************************/
25
26 l_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
27 l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
28 l_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
29 l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
30 l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
31 l_bom_comp_ops_tbl Bom_Bo_Pub.Bom_Comp_Ops_Tbl_Type;
32
33 --for updating interface tables
34 l_bom_revision_rec Bom_Bo_Pub.Bom_Revision_Rec_Type;
35 l_bom_component_rec Bom_Bo_Pub.Bom_Comps_Rec_Type;
36 l_bom_ref_designator_rec Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type;
37 l_bom_sub_component_rec Bom_Bo_Pub.Bom_Sub_Component_Rec_Type;
38 l_bom_comp_ops_rec Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type;
39
40 Function Update_Interface_tables (
41 err_text IN OUT NOCOPY VARCHAR2)
42 return integer;
43
44
45 FUNCTION Delete_Bom_OI (
46 err_text IN OUT NOCOPY VARCHAR2,
47 p_batch_id IN NUMBER
48 )return integer;
49
50 /*--------------------------Import_BOM----------------------------------------
51
52 NAME
53 Import_BOM
54 DESCRIPTION
55 Import Bill, Components, Substitute Components, Reference Designators
56 and Component Operations for null batch id .
57 RETURNS
58 0 if successful
59 SQLCODE if unsuccessful
60 NOTES
61 -----------------------------------------------------------------------------*/
62 FUNCTION Import_BOM
63 (org_id IN NUMBER ,
64 all_org IN NUMBER:=1,
65 user_id IN NUMBER:=-1,
66 login_id IN NUMBER:=-1,
67 prog_appid IN NUMBER:=-1,
68 prog_id IN NUMBER:=-1,
69 req_id IN NUMBER:=-1,
70 del_rec_flag IN NUMBER:=1,
71 err_text IN OUT NOCOPY VARCHAR2)
72 return integer
73 IS
74 l_return_status INTEGER := 0;
75 BEGIN
76
77 --call the import_bom with null batch id.
78 l_return_status := Import_BOM
79 (org_id => org_id,
80 all_org => all_org,
81 user_id => user_id,
82 login_id => login_id,
83 prog_appid => prog_appid,
84 prog_id => prog_id,
85 req_id => req_id,
86 del_rec_flag => del_rec_flag,
87 err_text => err_text,
88 p_batch_id => NULL);
89
90 RETURN l_return_status;
91 END;
92
93
94 /*--------------------------Import_BOM----------------------------------------
95
96 NAME
97 Import_BOM
98 DESCRIPTION
99 Import Bill, Components, Substitute Components, Reference Designators
100 and Component Operations for given batch id .
101 RETURNS
102 0 if successful
103 SQLCODE if unsuccessful
104 NOTES
105 -----------------------------------------------------------------------------*/
106 FUNCTION Import_BOM
107 (org_id IN NUMBER ,
108 all_org IN NUMBER:=1,
109 user_id IN NUMBER:=-1,
110 login_id IN NUMBER:=-1,
111 prog_appid IN NUMBER:=-1,
112 prog_id IN NUMBER:=-1,
113 req_id IN NUMBER:=-1,
114 del_rec_flag IN NUMBER:=1,
115 err_text IN OUT NOCOPY VARCHAR2,
116 p_batch_id IN NUMBER)
117 return integer IS
118
119 cursor get_bills is
120 select * from BOM_BILL_OF_MTLS_INTERFACE
121 where process_flag = 1
122 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
123 and transaction_id is not null
124 and
125 (
126 ( (p_batch_id is null) and (batch_id is null) )
127 or ( p_batch_id = batch_id )
128 )
129 order by alternate_bom_designator DESC;
130
131 bill_rec get_bills%ROWTYPE;
132
133 cursor get_comps (cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_des varchar2) is
134 select /*+ index(a BOM_INV_COMPS_INTERFACE_N6) */ * from BOM_INVENTORY_COMPS_INTERFACE A /*Bug 8213562: Added hint*/
135 where process_flag = 1
136 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
137 and assembly_item_number = cp_ass_item_name
138 and organization_code = cp_org_code
139 and (alternate_bom_designator is NULL or
140 (alternate_bom_designator is not Null and
141 alternate_bom_designator= cp_alt_des))
142 and transaction_id is not null
143 and change_notice is null --added for bug 9447664
144 and
145 (
146 ( (p_batch_id is null) and (batch_id is null) )
147 or ( p_batch_id = batch_id )
148 );
149
150 comp_rec get_comps%ROWTYPE;
151
152 cursor get_orphan_comps is
153 select * from BOM_INVENTORY_COMPS_INTERFACE
154 where process_flag = 1
155 and all_org = 1
156 and transaction_id is not null
157 and change_notice is null --added for bug 9447664
158 and
159 (
160 ( (p_batch_id is null) and (batch_id is null) )
161 or ( p_batch_id = batch_id )
162 )
163 and rownum = 1
164 UNION ALL
165 SELECT * FROM BOM_INVENTORY_COMPS_INTERFACE
166 WHERE process_flag = 1
167 AND all_org = 2 and organization_id = org_id
168 AND transaction_id is not null
169 and change_notice is null --added for bug 9447664
170 AND rownum =1
171 AND
172 (
173 ( (p_batch_id is null) and (batch_id is null) )
174 or ( p_batch_id = batch_id )
175 );
176
177 orp_cmp_rec get_orphan_comps%ROWTYPE;
178
179 cursor get_revs (cp_ass_item_name varchar2, cp_org_code varchar2) is
180 select * from mtl_item_revisions_interface
181 where process_flag = 1
182 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
183 and item_number = cp_ass_item_name
184 and organization_code = cp_org_code
185 and transaction_id is not null
186 and change_notice is null --added for bug 9447664
187 and set_process_id = nvl(p_batch_id,0); -- Replace NULL batch id with 0 - table level default for set_process_id
188
189 rev_rec get_revs%ROWTYPE;
190
191 cursor get_orphan_revs is
192 select * from mtl_item_revisions_interface
193 where process_flag = 1
194 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
195 and transaction_id is not null
196 and change_notice is null --added for bug 9447664
197 and set_process_id = nvl(p_batch_id,0);
198
199 orp_rev_rec get_orphan_revs%ROWTYPE;
200
201 cursor get_ref_desg (cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_des varchar2) is
202 select * from BOM_REF_DESGS_INTERFACE
203 where process_flag = 1
204 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
205 and assembly_item_number = cp_ass_item_name
206 and organization_code = cp_org_code
207 and (alternate_bom_designator is NULL or
208 (alternate_bom_designator is not Null and
209 alternate_bom_designator= cp_alt_des))
210 and transaction_id is not null
211 and change_notice is null --added for bug 9447664
212 and
213 (
214 ( (p_batch_id is null) and (batch_id is null) )
215 or ( p_batch_id = batch_id )
216 );
217
218 ref_rec get_ref_desg%ROWTYPE;
219
220 cursor get_orphan_ref_desg is
221 SELECT *
222 FROM BOM_REF_DESGS_INTERFACE
223 where process_flag = 1
224 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
225 and transaction_id is not null
226 and change_notice is null --added for bug 9447664
227 and
228 (
229 ( (p_batch_id is null) and (batch_id is null) )
230 or ( p_batch_id = batch_id )
231 );
232
233 orp_ref_rec get_orphan_ref_desg%ROWTYPE;
234
235 cursor get_sub_comps (cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_des varchar2) is
236 select * from bom_sub_comps_interface
237 where process_flag = 1
238 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
239 and assembly_item_number = cp_ass_item_name
240 and organization_code = cp_org_code
241 and (alternate_bom_designator is NULL or
242 (alternate_bom_designator is not Null and
243 alternate_bom_designator= cp_alt_des))
244 and transaction_id is not null
245 and change_notice is null --added for bug 9447664
246 and
247 (
248 ( (p_batch_id is null) and (batch_id is null) )
249 or ( p_batch_id = batch_id )
250 );
251
252 sub_rec get_sub_comps%ROWTYPE;
253
254 cursor get_orphan_sub_comps is
255 SELECT * FROM BOM_SUB_COMPS_INTERFACE
256 where process_flag = 1
257 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
258 and transaction_id is not null
259 and change_notice is null --added for bug 9447664
260 and
261 (
262 ( (p_batch_id is null) and (batch_id is null) )
263 or ( p_batch_id = batch_id )
264 );
265
266 orp_sub_rec get_orphan_sub_comps%ROWTYPE;
267
268 cursor get_comp_ops (cp_ass_item_name varchar2, cp_org_code varchar2, cp_alt_des varchar2) is
269 SELECT * FROM BOM_COMPONENT_OPS_INTERFACE
270 where process_flag = 1
271 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
272 and assembly_item_number = cp_ass_item_name
273 and organization_code = cp_org_code
274 and (alternate_bom_designator is NULL or
275 (alternate_bom_designator is not Null and
276 alternate_bom_designator= cp_alt_des))
277 and transaction_id is not null
278 and
279 (
280 ( (p_batch_id is null) and (batch_id is null) )
281 or ( p_batch_id = batch_id )
282 );
283
284 ops_rec get_comp_ops%ROWTYPE;
285
286 cursor get_orphan_comp_ops is
287 SELECT * FROM BOM_COMPONENT_OPS_INTERFACE
288 where process_flag = 1
289 and (all_org = 1 or (all_org = 2 and organization_id = org_id))
290 and transaction_id is not null
291 and
292 (
293 ( (p_batch_id is null) and (batch_id is null) )
294 or ( p_batch_id = batch_id )
295 );
296
297 orp_ops_rec get_orphan_comp_ops%ROWTYPE;
298
299 l_return_status Varchar2(100);
300 l_func_ret_status INTEGER;
301 l_msg_count Number;
302 l_bills_exists Varchar2(3) := 'YES';
303 l_comps_exists Varchar2(3) := 'YES';
304 l_revs_exists Varchar2(3) := 'YES';
305 l_ref_desgs_exists Varchar2(3) :='YES';
306 l_sub_comps_exists Varchar2(3) :='YES';
307 l_comp_ops_exists Varchar2(3) :='YES';
308 -- l_assembly_item_name Varchar2(81);
309 l_assembly_item_name Varchar2(240); -- bug 2947642
310 l_organization_code Varchar2(3);
311 l_alternate_designator Varchar2(10);
312 i NUMBER;
313 stmt_num NUMBER;
314 debug_on Varchar2(1) := 'Y';
315 empty_bo Varchar2(3) := 'YES';
316 BEGIN
317 l_func_ret_status := 0;
318 stmt_num := 0;
319 IF FND_PROFILE.VALUE('MRP_DEBUG') = 'Y' then
320 debug_on := 'Y';
321 else
322 debug_on :='N';
323 end if;
324
325 stmt_num := 1;
326 --commented by vhymavat for bug 3179687
327 /*
328 fnd_global.apps_initialize
329 ( user_id => user_id,
330 resp_id =>FND_PROFILE.value('RESP_ID'),
331 resp_appl_id => prog_appid
332 );
333 */
334 BOM_GLOBALS.G_BATCH_ID := p_batch_id; -- Bug 4306013
335
336 stmt_num :=2;
337 -- Initialize the error handling table
338 ERROR_HANDLER.INITIALIZE ;
339
340 stmt_num :=3;
341
342 -- Set the Global Variable to BOM Open Interface type.
343 Error_Handler.set_bom_oi;
344
345 stmt_num :=4;
346 -- Convert the Derived columns to User friendly columns
347
348 l_return_status := Bom_Open_Interface_Utl.Process_Header_Info
349 (org_id,all_org,user_id,login_id,
350 prog_appid,prog_id,req_id,err_text,p_batch_id);
351 IF (l_return_status <> 0) THEN
352 RETURN(l_return_status);
353 END IF;
354 l_return_status := Bom_Open_Interface_Utl.Process_Comps_Info
355 (org_id,all_org,user_id,login_id,
356 prog_appid,prog_id,req_id,err_text,p_batch_id);
357 IF (l_return_status <> 0) THEN
358 RETURN(l_return_status);
359 END IF;
360 l_return_status := Bom_Open_Interface_Utl.Process_Ref_Degs_Info
361 (org_id,all_org,user_id,login_id,
362 prog_appid,prog_id,req_id,err_text,p_batch_id);
363 IF (l_return_status <> 0) THEN
364 RETURN(l_return_status);
365 END IF;
366 l_return_status := Bom_Open_Interface_Utl.Process_Sub_Comps_Info
367 (org_id,all_org,user_id,login_id,
368 prog_appid,prog_id,req_id,err_text,p_batch_id);
369 IF (l_return_status <> 0) THEN
370 RETURN(l_return_status);
371 END IF;
372 l_return_status := Bom_Open_Interface_Utl.Process_Comp_Ops_Info
373 (org_id,all_org,user_id,login_id,
377 END IF;
374 prog_appid,prog_id,req_id,err_text,p_batch_id);
375 IF (l_return_status <> 0) THEN
376 RETURN(l_return_status);
378 l_return_status := Bom_Open_Interface_Utl.Process_Revision_Info
379 (org_id,all_org,user_id,login_id,
380 prog_appid,prog_id,req_id,err_text,p_batch_id);
381 IF (l_return_status <> 0) THEN
382 RETURN(l_return_status);
383 END IF;
384
385 stmt_num :=5;
386
387 OPEN get_bills;
388 LOOP
389
390 /* Initialize all the variables in business object */
391
392 l_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
393 l_bom_revision_tbl := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
394 l_bom_component_tbl := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
395 l_bom_ref_designator_tbl := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
396 l_bom_sub_component_tbl := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
397 l_bom_comp_ops_tbl := Bom_Bo_Pub.G_MISS_BOM_COMP_OPS_TBL;
398 l_assembly_item_name := NULL;
399 l_organization_code := NULL;
400 l_alternate_designator := NULL;
401 empty_bo :='YES';
402
403 /* Get the parent header information l_assembly_item_name
404 l_organization_code, l_alternate_bom_designator */
405
406 If (l_bills_exists = 'YES') then
407 FETCH get_bills into bill_rec;
408 If (get_bills %NOTFOUND) then
409 l_bills_exists := 'NO';
410 CLOSE get_bills;
411 else
412 l_bills_exists :='YES';
413 l_assembly_item_name := bill_rec.item_number;
414 l_organization_code :=bill_rec.organization_code;
415 l_alternate_designator :=bill_rec.alternate_bom_designator;
416 END IF; -- get_bills%notfound
417
418 ELSIF (l_comps_exists ='YES') then
419 OPEN get_orphan_comps;
420 FETCH get_orphan_comps into orp_cmp_rec;
421 If (get_orphan_comps%NOTFOUND) then
422 l_comps_exists := 'NO';
423 else
424 l_comps_exists :='YES';
425 l_assembly_item_name := orp_cmp_rec.assembly_item_number;
426 l_organization_code :=orp_cmp_rec.organization_code;
427 l_alternate_designator :=orp_cmp_rec.alternate_bom_designator;
428
429 end if; --get_orphan_comps%NOTFOUND
430 CLOSE get_orphan_comps;
431 ELSIF (l_revs_exists ='YES') then
432 OPEN get_orphan_revs;
433 FETCH get_orphan_revs into orp_rev_rec;
434 If (get_orphan_revs%NOTFOUND) then
435 l_revs_exists := 'NO';
436 else
437 l_revs_exists :='YES';
438 l_assembly_item_name := orp_rev_rec.item_number;
439 l_organization_code :=orp_rev_rec.organization_code;
440 --l_alternate_designator :=orp_rev_rec.alternate_bom_designator;
441
442 end if; --get_orphan_revs%NOTFOUND
443 CLOSE get_orphan_revs;
444
445 ELSIF (l_ref_desgs_exists ='YES') then
446 OPEN get_orphan_ref_desg;
447 FETCH get_orphan_ref_desg into orp_ref_rec;
448
449 If (get_orphan_ref_desg%NOTFOUND) then
450 l_ref_desgs_exists := 'NO';
451 else
452 l_ref_desgs_exists :='YES';
453 l_assembly_item_name := orp_ref_rec.assembly_item_number;
454 l_organization_code :=orp_ref_rec.organization_code;
455 l_alternate_designator :=orp_ref_rec.alternate_bom_designator;
456 end if; --get_orphan_ref_desg %NOTFOUND
457 CLOSE get_orphan_ref_desg;
458
459 ELSIF (l_sub_comps_exists ='YES') then
460 OPEN get_orphan_sub_comps;
461 FETCH get_orphan_sub_comps into orp_sub_rec;
462
463 If (get_orphan_sub_comps%NOTFOUND) then
464 l_sub_comps_exists := 'NO';
465 else
466 l_sub_comps_exists :='YES';
467 l_assembly_item_name := orp_sub_rec.assembly_item_number;
468 l_organization_code :=orp_sub_rec.organization_code;
469 l_alternate_designator :=orp_sub_rec.alternate_bom_designator;
470
471 end if; --get_orphan_sub_comps %NOTFOUND
472 CLOSE get_orphan_sub_comps;
473 ELSIF (l_comp_ops_exists ='YES') then
474 OPEN get_orphan_comp_ops;
475 FETCH get_orphan_comp_ops into orp_ops_rec;
476
477 If (get_orphan_comp_ops%NOTFOUND) then
478 l_comp_ops_exists := 'NO';
479 EXIT ; -- MAIN LOOP
480 else
481 l_comp_ops_exists :='YES';
482 l_assembly_item_name := orp_ops_rec.assembly_item_number;
483 l_organization_code :=orp_ops_rec.organization_code;
484 l_alternate_designator :=orp_ops_rec.alternate_bom_designator;
485
486 end if; --get_orphan_comp_ops%NOTFOUND
487 CLOSE get_orphan_comp_ops;
488 END IF; -- l_bills_exists,l_comp_exists,l_ref_desgs_exists,l_sub_comps_exists,l_comp_ops_exists
489 /* Populate the interface records into the BO variables and call BO */
490
494 If (l_bills_exists = 'YES' ) then
491 stmt_num :=6;
492 IF ( l_assembly_item_name IS NOT NULL and l_organization_code is not null) then
493
495 empty_bo := 'NO';
496
497 IF(bill_rec.Transaction_Type <> 'NO_OP')
498 THEN
499 l_bom_header_rec.Assembly_item_name := bill_rec.item_number;
500 l_bom_header_rec.Organization_Code := bill_rec.Organization_Code;
501 l_bom_header_rec.Alternate_Bom_Code := bill_rec.ALTERNATE_BOM_DESIGNATOR;
502 l_bom_header_rec.Common_Assembly_Item_Name := bill_rec.COMMON_ITEM_NUMBER;
503 l_bom_header_rec.Common_Organization_Code := bill_rec.COMMON_ORG_CODE;
504 l_bom_header_rec.Assembly_Comment := bill_rec.SPECIFIC_ASSEMBLY_COMMENT;
505 l_bom_header_rec.Assembly_Type := nvl( bill_rec.Assembly_Type,1);
506 l_bom_header_rec.Transaction_Type := bill_rec.Transaction_Type;
507 l_bom_header_rec.Return_Status := '';
508 l_bom_header_rec.Attribute_category := bill_rec.Attribute_category;
509 l_bom_header_rec.Attribute1 := bill_rec.Attribute1;
510 l_bom_header_rec.Attribute2 := bill_rec.Attribute2;
511 l_bom_header_rec.Attribute3 := bill_rec.Attribute3;
512 l_bom_header_rec.Attribute4 := bill_rec.Attribute4;
513 l_bom_header_rec.Attribute5 := bill_rec.Attribute5 ;
514 l_bom_header_rec.Attribute6 := bill_rec.Attribute6;
515 l_bom_header_rec.Attribute7 := bill_rec.Attribute7;
516 l_bom_header_rec.Attribute8 := bill_rec.Attribute8;
517 l_bom_header_rec.Attribute9 := bill_rec.Attribute9;
518 l_bom_header_rec.Attribute10 := bill_rec.Attribute10;
519 l_bom_header_rec.Attribute11 := bill_rec.Attribute11;
520 l_bom_header_rec.Attribute12 := bill_rec.Attribute12;
521 l_bom_header_rec.Attribute13 := bill_rec.Attribute13;
522 l_bom_header_rec.Attribute14 := bill_rec.Attribute14;
523 l_bom_header_rec.Attribute15 := bill_rec.Attribute15;
524 l_bom_header_rec.Original_System_Reference:= bill_rec.Original_System_Reference;
525 l_bom_header_rec.Delete_Group_Name := bill_rec.Delete_Group_Name;
526 l_bom_header_rec.DG_Description := bill_rec.DG_Description;
527 l_bom_header_rec.bom_implementation_date := bill_rec.IMPLEMENTATION_DATE;
528 l_bom_header_rec.row_identifier := bill_rec.transaction_id;
529 l_bom_header_rec.enable_attrs_update := bill_rec.enable_attrs_update;
530 l_bom_header_rec.Structure_Type_Name := bill_rec.Structure_Type_Name;
531 END IF;
532
533 stmt_num:=7;
534 END IF;
535
536 If (l_bills_exists = 'YES' or l_comps_exists = 'YES' or l_revs_exists = 'YES') then
537
538 OPEN get_revs (l_assembly_item_name, l_organization_code);
539 i := 0;
540 LOOP
541 FETCH get_revs into rev_rec;
542 EXIT WHEN get_revs%NOTFOUND;
543
544 i:=i+1;
545 if (i=1) then empty_bo := 'NO'; end if;
546 l_bom_revision_tbl(i).Organization_Code := l_organization_code ;
547 l_bom_revision_tbl(i).Assembly_Item_Name :=l_assembly_item_name ;
548 l_bom_revision_tbl(i).Alternate_BOM_Code := l_alternate_designator;
549 l_bom_revision_tbl(i).Revision := rev_rec.Revision;
550 l_bom_revision_tbl(i).Revision_Label := rev_rec.Revision_Label;
551 l_bom_revision_tbl(i).Revision_Reason := rev_rec.Revision_Reason;
552 l_bom_revision_tbl(i).Start_Effective_Date := rev_rec.Effectivity_Date;
553 l_bom_revision_tbl(i).Description := rev_rec.Description;
554 l_bom_revision_tbl(i).Attribute_category :=rev_rec.Attribute_category;
555 l_bom_revision_tbl(i).Attribute1 := rev_rec.Attribute1;
556 l_bom_revision_tbl(i).Attribute2 := rev_rec.Attribute2;
557 l_bom_revision_tbl(i).Attribute3 := rev_rec.Attribute3;
558 l_bom_revision_tbl(i).Attribute4 := rev_rec.Attribute4;
559 l_bom_revision_tbl(i).Attribute5 := rev_rec.Attribute5;
560 l_bom_revision_tbl(i).Attribute6 := rev_rec.Attribute6;
561 l_bom_revision_tbl(i).Attribute7 := rev_rec.Attribute7;
562 l_bom_revision_tbl(i).Attribute8 := rev_rec.Attribute8;
563 l_bom_revision_tbl(i).Attribute9 := rev_rec.Attribute9;
564 l_bom_revision_tbl(i).Attribute10 := rev_rec.Attribute10;
565 l_bom_revision_tbl(i).Attribute11 := rev_rec.Attribute11;
566 l_bom_revision_tbl(i).Attribute12 := rev_rec.Attribute12;
567 l_bom_revision_tbl(i).Attribute13 := rev_rec.Attribute13;
568 l_bom_revision_tbl(i).Attribute14 := rev_rec.Attribute14;
569 l_bom_revision_tbl(i).Attribute15 := rev_rec.Attribute15;
570 l_bom_revision_tbl(i).Return_Status := '';
571 l_bom_revision_tbl(i).Transaction_Type := rev_rec.Transaction_Type;
572 --l_bom_revision_tbl(i).Original_System_Reference:= rev_rec.Original_System_Reference;
573 l_bom_revision_tbl(i).row_identifier := rev_rec.transaction_id;
574
575 END LOOP;
576 CLOSE get_revs;
577 stmt_num:=8;
578 OPEN get_comps (l_assembly_item_name, l_organization_code, l_alternate_designator);
579
580 i := 0;
581 LOOP
582 FETCH get_comps into comp_rec;
583 EXIT WHEN get_comps%NOTFOUND;
584
585 i:=i+1;
586 if (i=1) then empty_bo := 'NO'; end if;
587 l_bom_component_tbl(i).Organization_Code := l_organization_code ;
591 */
588 l_bom_component_tbl(i).Assembly_Item_Name :=l_assembly_item_name;
589 /* commented for bug3242208
590 l_bom_component_tbl(i).Alternate_BOM_Code := l_alternate_designator;
592 l_bom_component_tbl(i).Alternate_BOM_Code := comp_rec.alternate_bom_designator;
593 l_bom_component_tbl(i).Start_Effective_Date := comp_rec.Effectivity_Date;
594 l_bom_component_tbl(i).Disable_Date :=comp_rec.Disable_Date;
595 l_bom_component_tbl(i).Operation_Sequence_Number := comp_rec.OPERATION_SEQ_NUM;
596 l_bom_component_tbl(i).Component_Item_Name := comp_rec.Component_Item_Number;
597 l_bom_component_tbl(i).New_Effectivity_Date := comp_rec.New_Effectivity_Date;
598 l_bom_component_tbl(i).New_Operation_Sequence_Number := comp_rec.New_Operation_Seq_Num;
599 l_bom_component_tbl(i).Item_Sequence_Number := comp_rec.ITEM_NUM;
600 l_bom_component_tbl(i).Basis_Type := comp_rec.BASIS_TYPE;
601 l_bom_component_tbl(i).Quantity_Per_Assembly := comp_rec.COMPONENT_QUANTITY;
602 l_bom_component_tbl(i).Inverse_Quantity := comp_rec.Inverse_Quantity;
603 l_bom_component_tbl(i).Planning_Percent := comp_rec.Planning_Factor;
604 l_bom_component_tbl(i).Projected_Yield := comp_rec.COMPONENT_YIELD_FACTOR;
605 l_bom_component_tbl(i).Include_In_Cost_Rollup := comp_rec.Include_In_Cost_Rollup;
606 l_bom_component_tbl(i).Wip_Supply_Type := comp_rec.Wip_Supply_Type;
607 l_bom_component_tbl(i).So_Basis :=comp_rec.So_Basis;
608 l_bom_component_tbl(i).Optional := comp_rec.Optional;
609 l_bom_component_tbl(i).Mutually_Exclusive := comp_rec.Mutually_Exclusive_options;
610 l_bom_component_tbl(i).Check_Atp :=comp_rec.Check_Atp;
611 l_bom_component_tbl(i).Shipping_Allowed := comp_rec.Shipping_Allowed;
612 l_bom_component_tbl(i).Required_To_Ship := comp_rec.Required_To_Ship;
613 l_bom_component_tbl(i).Required_For_Revenue := comp_rec.Required_For_Revenue;
614 l_bom_component_tbl(i).Include_On_Ship_Docs := comp_rec.Include_On_Ship_Docs;
615 l_bom_component_tbl(i).Quantity_Related := comp_rec.Quantity_Related;
616 l_bom_component_tbl(i).Supply_Subinventory :=comp_rec.Supply_Subinventory;
617 l_bom_component_tbl(i).Location_Name := comp_rec.Location_Name;
618 l_bom_component_tbl(i).Minimum_Allowed_Quantity := comp_rec.low_Quantity;
619 l_bom_component_tbl(i).Maximum_Allowed_Quantity := comp_rec.high_Quantity;
620 l_bom_component_tbl(i).Comments := comp_rec.Component_remarks;
621 l_bom_component_tbl(i).Attribute_category := comp_rec.Attribute_category;
622 l_bom_component_tbl(i).Attribute1 :=comp_rec.Attribute1;
623 l_bom_component_tbl(i).Attribute2 := comp_rec.Attribute2;
624 l_bom_component_tbl(i).Attribute3 := comp_rec.Attribute3;
625 l_bom_component_tbl(i).Attribute4 :=comp_rec.Attribute4;
626 l_bom_component_tbl(i).Attribute5 := comp_rec.Attribute5;
627 l_bom_component_tbl(i).Attribute6 := comp_rec.Attribute6;
628 l_bom_component_tbl(i).Attribute7 := comp_rec.Attribute7;
629 l_bom_component_tbl(i).Attribute8 :=comp_rec.Attribute8;
630 l_bom_component_tbl(i).Attribute9 := comp_rec.Attribute9;
631 l_bom_component_tbl(i).Attribute10 :=comp_rec.Attribute10;
632 l_bom_component_tbl(i).Attribute11 :=comp_rec.Attribute11;
633 l_bom_component_tbl(i).Attribute12 := comp_rec.Attribute12;
634 l_bom_component_tbl(i).Attribute13 := comp_rec.Attribute13;
635 l_bom_component_tbl(i).Attribute14 := comp_rec.Attribute14;
636 l_bom_component_tbl(i).Attribute15 :=comp_rec.Attribute15;
637 l_bom_component_tbl(i).From_End_Item_Unit_Number :=comp_rec.From_End_Item_Unit_Number;
638 l_bom_component_tbl(i).New_From_End_Item_Unit_Number := comp_rec.New_From_End_Item_Unit_Number;
639 l_bom_component_tbl(i).To_End_Item_Unit_Number := comp_rec.To_End_Item_Unit_Number;
640 l_bom_component_tbl(i).Suggested_Vendor_Name := comp_rec.Suggested_Vendor_Name; --- Deepu
641 -- l_bom_component_tbl(i).Vendor_Id := comp_rec.Vendor_Id; --- Deepu
642 l_bom_component_tbl(i).Unit_Price := comp_rec.Unit_Price; --- Deepu
643 l_bom_component_tbl(i).Return_Status := '';
644 l_bom_component_tbl(i).Transaction_Type := comp_rec.Transaction_Type;
645 l_bom_component_tbl(i).Original_System_Reference := comp_rec.Original_System_Reference;
646 l_bom_component_tbl(i).Delete_Group_Name :=comp_rec.Delete_Group_Name;
647 l_bom_component_tbl(i).DG_Description := comp_rec.DG_Description;
648 l_bom_component_tbl(i).row_identifier := comp_rec.transaction_id;
649 l_bom_component_tbl(i).Enforce_Int_Requirements := comp_rec.ENFORCE_INT_REQUIREMENTS;
650 l_bom_component_tbl(i).Auto_Request_Material := comp_rec.Auto_request_Material; -- Bug 5257896(5252452)
651
652 END LOOP;
653 CLOSE get_comps;
654 stmt_num:=9;
655 END IF; -- l_bills_exists or l_comps_exists
656
657 OPEN get_ref_desg (l_assembly_item_name, l_organization_code, l_alternate_designator);
658 i := 0;
659 LOOP
660 FETCH get_ref_desg into ref_rec;
661 EXIT WHEN get_ref_desg%NOTFOUND;
662 i:=i+1;
663
664 if (i=1) then empty_bo := 'NO'; end if;
665 l_bom_ref_designator_tbl(i).Organization_Code := l_organization_code ;
666 l_bom_ref_designator_tbl(i).Assembly_Item_Name := l_assembly_item_name ;
667 l_bom_ref_designator_tbl(i).Start_Effective_Date := ref_rec.EFFECTIVITY_DATE;
671 l_bom_ref_designator_tbl(i).Reference_Designator_Name :=ref_rec.COMPONENT_REFERENCE_DESIGNATOR;
668 l_bom_ref_designator_tbl(i).Operation_Sequence_Number := ref_rec.OPERATION_SEQ_NUM;
669 l_bom_ref_designator_tbl(i).Component_Item_Name := ref_rec.Component_Item_Number;
670 l_bom_ref_designator_tbl(i).Alternate_BOM_Code :=l_alternate_designator;
672 l_bom_ref_designator_tbl(i).Ref_Designator_Comment := ref_rec.REF_DESIGNATOR_COMMENT;
673 l_bom_ref_designator_tbl(i).Attribute_category := ref_rec.Attribute_category;
674 l_bom_ref_designator_tbl(i).Attribute1 := ref_rec.Attribute1;
675 l_bom_ref_designator_tbl(i).Attribute2 := ref_rec.Attribute2;
676 l_bom_ref_designator_tbl(i).Attribute3 := ref_rec.Attribute3;
677 l_bom_ref_designator_tbl(i).Attribute4 := ref_rec.Attribute4;
678 l_bom_ref_designator_tbl(i).Attribute5 := ref_rec.Attribute5;
679 l_bom_ref_designator_tbl(i).Attribute6 := ref_rec.Attribute6;
680 l_bom_ref_designator_tbl(i).Attribute7 := ref_rec.Attribute7;
681 l_bom_ref_designator_tbl(i).Attribute8 := ref_rec.Attribute8;
682 l_bom_ref_designator_tbl(i).Attribute9 := ref_rec.Attribute9;
683 l_bom_ref_designator_tbl(i).Attribute10 := ref_rec.Attribute10;
684 l_bom_ref_designator_tbl(i).Attribute11 := ref_rec.Attribute11;
685 l_bom_ref_designator_tbl(i).Attribute12 := ref_rec.Attribute12;
686 l_bom_ref_designator_tbl(i).Attribute13 := ref_rec.Attribute13;
687 l_bom_ref_designator_tbl(i).Attribute14 := ref_rec.Attribute14;
688 l_bom_ref_designator_tbl(i).Attribute15 := ref_rec.Attribute15;
689 l_bom_ref_designator_tbl(i).From_End_Item_Unit_Number := ref_rec.From_End_Item_Unit_Number;
690 l_bom_ref_designator_tbl(i).New_Reference_Designator := ref_rec.New_Designator;
691 l_bom_ref_designator_tbl(i).Return_Status := '';
692 l_bom_ref_designator_tbl(i).Transaction_Type := ref_rec.Transaction_Type;
693 l_bom_ref_designator_tbl(i).Original_System_Reference := ref_rec.Original_System_Reference;
694 l_bom_ref_designator_tbl(i).row_identifier := ref_rec.transaction_id;
695 END LOOP;
696 CLOSE get_ref_desg;
697
698
699 OPEN get_sub_comps(l_assembly_item_name, l_organization_code, l_alternate_designator);
700 i := 0;
701 LOOP
702 FETCH get_sub_comps into sub_rec;
703 EXIT WHEN get_sub_comps%NOTFOUND;
704 i:=i+1;
705 if (i=1) then empty_bo := 'NO'; end if;
706 l_bom_sub_component_tbl(i).Organization_Code := l_organization_code;
707 l_bom_sub_component_tbl(i).Assembly_Item_Name := l_assembly_item_name;
708 l_bom_sub_component_tbl(i).Start_Effective_Date := sub_rec.EFFECTIVITY_DATE;
709 l_bom_sub_component_tbl(i).Operation_Sequence_Number :=sub_rec.OPERATION_SEQ_NUM;
710 l_bom_sub_component_tbl(i).Component_Item_Name := sub_rec.COMPONENT_ITEM_NUMBER;
711 l_bom_sub_component_tbl(i).Alternate_BOM_Code := l_alternate_designator;
712 l_bom_sub_component_tbl(i).Substitute_Component_Name :=sub_rec.SUBSTITUTE_COMP_NUMBER;
713 l_bom_sub_component_tbl(i).new_Substitute_Component_Name :=sub_rec.new_SUB_COMP_NUMBER;
714 l_bom_sub_component_tbl(i).Substitute_Item_Quantity :=sub_rec.Substitute_Item_Quantity;
715 l_bom_sub_component_tbl(i).Inverse_Quantity := sub_rec.SUB_COMP_INVERSE_QUANTITY;
716 l_bom_sub_component_tbl(i).Attribute_category := sub_rec.Attribute_category;
717 l_bom_sub_component_tbl(i).Attribute1 := sub_rec.Attribute1;
718 l_bom_sub_component_tbl(i).Attribute2 := sub_rec.Attribute2;
719 l_bom_sub_component_tbl(i).Attribute3 := sub_rec.Attribute3;
720 l_bom_sub_component_tbl(i).Attribute4 := sub_rec.Attribute4;
721 l_bom_sub_component_tbl(i).Attribute5 := sub_rec.Attribute5;
722 l_bom_sub_component_tbl(i).Attribute6 := sub_rec.Attribute6;
723 l_bom_sub_component_tbl(i).Attribute7 := sub_rec.Attribute7;
724 l_bom_sub_component_tbl(i).Attribute8 := sub_rec.Attribute8;
725 l_bom_sub_component_tbl(i).Attribute9 := sub_rec.Attribute9;
726 l_bom_sub_component_tbl(i).Attribute10 := sub_rec.Attribute10;
727 l_bom_sub_component_tbl(i).Attribute11 := sub_rec.Attribute11;
728 l_bom_sub_component_tbl(i).Attribute12 := sub_rec.Attribute12;
729 l_bom_sub_component_tbl(i).Attribute13 := sub_rec.Attribute13;
730 l_bom_sub_component_tbl(i).Attribute14 := sub_rec.Attribute14;
731 l_bom_sub_component_tbl(i).Attribute15 := sub_rec.Attribute15;
732 l_bom_sub_component_tbl(i).From_End_Item_Unit_Number :=sub_rec.From_End_Item_Unit_Number;
733 l_bom_sub_component_tbl(i).Return_Status:= '';
734 l_bom_sub_component_tbl(i).Transaction_Type := sub_rec.Transaction_Type;
735 l_bom_sub_component_tbl(i).Original_System_Reference:=sub_rec.Original_System_Reference;
736 l_bom_sub_component_tbl(i).row_identifier :=sub_rec.transaction_id;
737 l_bom_sub_component_tbl(i).Enforce_Int_Requirements :=sub_rec.Enforce_Int_Requirements;
738
739 END LOOP;
740
741 CLOSE get_sub_comps;
742
743
744 OPEN get_comp_ops (l_assembly_item_name, l_organization_code, l_alternate_designator);
745 i := 0;
746 LOOP
747 FETCH get_comp_ops into ops_rec;
748 EXIT WHEN get_comp_ops%NOTFOUND;
749 i:=i+1;
750 if (i=1) then empty_bo := 'NO'; end if;
751 l_bom_comp_ops_tbl(i).Organization_Code := l_organization_code;
755 l_bom_comp_ops_tbl(i).To_End_Item_Unit_Number := ops_rec.To_End_Item_Unit_Number;
752 l_bom_comp_ops_tbl(i).Assembly_Item_Name := l_assembly_item_name ;
753 l_bom_comp_ops_tbl(i).Start_Effective_Date := ops_rec.EFFECTIVITY_DATE;
754 l_bom_comp_ops_tbl(i).From_End_Item_Unit_Number := ops_rec.From_End_Item_Unit_Number;
756 l_bom_comp_ops_tbl(i).Operation_Sequence_Number :=ops_rec.Operation_Seq_Num;
757 l_bom_comp_ops_tbl(i).Component_Item_Name :=ops_rec.Component_Item_Number;
758 l_bom_comp_ops_tbl(i).Additional_Operation_Seq_Num :=ops_rec.Additional_Operation_Seq_Num;
759 l_bom_comp_ops_tbl(i).New_Additional_Op_Seq_Num :=ops_rec.New_Additional_Op_Seq_Num;
760 l_bom_comp_ops_tbl(i).Alternate_BOM_Code := l_alternate_designator;
761 l_bom_comp_ops_tbl(i).Attribute_category := ops_rec.Attribute_category;
762 l_bom_comp_ops_tbl(i).Attribute1 := ops_rec.Attribute1;
763 l_bom_comp_ops_tbl(i).Attribute2 :=ops_rec.Attribute2;
764 l_bom_comp_ops_tbl(i).Attribute3 :=ops_rec.Attribute3;
765 l_bom_comp_ops_tbl(i).Attribute4 := ops_rec.Attribute4;
766 l_bom_comp_ops_tbl(i).Attribute5 := ops_rec.Attribute5;
767 l_bom_comp_ops_tbl(i).Attribute6 :=ops_rec.Attribute6;
768 l_bom_comp_ops_tbl(i).Attribute7 := ops_rec.Attribute7;
769 l_bom_comp_ops_tbl(i).Attribute8 :=ops_rec.Attribute8;
770 l_bom_comp_ops_tbl(i).Attribute9 := ops_rec.Attribute9;
771 l_bom_comp_ops_tbl(i).Attribute10 := ops_rec.Attribute10;
772 l_bom_comp_ops_tbl(i).Attribute11 := ops_rec.Attribute11;
773 l_bom_comp_ops_tbl(i).Attribute12 := ops_rec.Attribute12;
774 l_bom_comp_ops_tbl(i).Attribute13 := ops_rec.Attribute13;
775 l_bom_comp_ops_tbl(i).Attribute14 :=ops_rec.Attribute14;
776 l_bom_comp_ops_tbl(i).Attribute15 := ops_rec.Attribute15;
777 l_bom_comp_ops_tbl(i).Return_Status :='';
778 l_bom_comp_ops_tbl(i).Transaction_Type :=ops_rec.Transaction_Type;
779 l_bom_comp_ops_tbl(i).row_identifier :=ops_rec.transaction_id;
780
781
782 END LOOP;
783 CLOSE get_comp_ops;
784
785 stmt_num:=10;
786
787 if (empty_bo ='NO') then
788 bom_bo_pub.Process_Bom
789 ( p_bo_identifier => 'BOM'
790 , p_api_version_number => 1.0
791 , p_init_msg_list => TRUE
792 , p_bom_header_rec => l_bom_header_rec
793 , p_bom_revision_tbl => l_bom_revision_tbl
794 , p_bom_component_tbl => l_bom_component_tbl
795 , p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
796 , p_bom_sub_component_tbl => l_bom_sub_component_tbl
797 , p_bom_comp_ops_tbl => l_bom_comp_ops_tbl
798 , x_bom_header_rec => l_bom_header_rec
799 , x_bom_revision_tbl => l_bom_revision_tbl
800 , x_bom_component_tbl => l_bom_component_tbl
801 , x_bom_ref_designator_tbl => l_bom_ref_designator_tbl
802 , x_bom_sub_component_tbl => l_bom_sub_component_tbl
803 , x_bom_comp_ops_tbl => l_bom_comp_ops_tbl
804 , x_return_status => l_return_status
805 , x_msg_count => l_msg_count
806 , p_debug => debug_on
807 , p_output_dir => 'none'
808 , p_debug_filename => 'none'
809 );
810 else
811 if (debug_on = 'Y') then
812 fnd_file.put_line (Which => FND_FILE.LOG,
813 buff => 'Data Error, Empty BO is getting passed') ;
814 end if;
815 EXIT;
816 end if;
817
818 stmt_num:=11;
819 -- Error_handling for the openInterface
820 Error_handler.Write_To_ConcurrentLog;
821 Error_handler.Write_To_InterfaceTable;
822 -- Error handling for the openInterface
823
824 stmt_num :=12;
825 l_return_status := Update_Interface_tables (err_text);
826 IF ( l_return_status NOT IN (0,1) ) THEN
827 RETURN(l_return_status);
828 ELSIF ( l_return_status = 1 ) THEN
829 l_func_ret_status := 1;
830 END IF;
831
832
833 COMMIT;
834 END IF; --ASSEMBLY_ITEM_NUMBER AND ORGANIZATION_CODE NOT NULL.
835 END LOOP;
836
837 Error_Handler.unset_bom_oi;
838 stmt_num :=13;
839 if(del_rec_flag = 1) then
840 l_return_status := Delete_Bom_OI(err_text,p_batch_id);
841 IF (l_return_status <> 0) THEN
842 RETURN(l_return_status);
843 END IF;
844 end if;
845
846 stmt_num :=14;
847 --check if the main cursor is open and then close it
848 If (get_bills%ISOPEN) then
849 close get_bills;
850 end if;
851
852 --return (0);
853 return (l_func_ret_status);
854 EXCEPTION
855 WHEN others THEN
856 err_text := 'Bom_Open_Interface_Api :'||stmt_num||substrb(SQLERRM,1,500);
857 Error_Handler.unset_bom_oi;
858 RETURN(SQLCODE);
859 End;
860
861
862
863 Function Update_Interface_tables (err_text IN OUT NOCOPY VARCHAR2)
864 return Integer
865 Is
866 l_process_flag Number;
867 stmt_num Number;
868 l_ret_status NUMBER;
869 begin
870 --bug:5235742 When import completes with one or more entities having errors, return 1.
871 l_ret_status := 0;
872 stmt_num :=0;
876 l_process_flag := 7;
873 if l_bom_header_rec.Return_Status IS NULL then
874 l_process_flag := 1;
875 elsif (l_bom_header_rec.Return_Status = 'S') then
877 else
878 l_process_flag := 3;
879 l_ret_status := 1;
880 end if;
881
882 Update bom_bill_of_mtls_interface
883 set process_flag = l_process_flag,
884 REQUEST_ID = Fnd_Global.Conc_Request_Id,
885 PROGRAM_ID = Fnd_Global.Conc_program_Id,
886 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
887 PROGRAM_UPDATE_DATE = sysdate
888 where transaction_id = l_bom_header_rec.Row_Identifier;
889
890 stmt_num :=1;
891 FOR I IN 1..l_bom_revision_tbl.COUNT LOOP
892
893 l_bom_revision_rec := l_bom_revision_tbl(I);
894
895 if l_bom_revision_rec.Return_Status IS NULL then
896 l_process_flag := 1;
897 elsif (l_bom_revision_rec.Return_Status = 'S') then
898 l_process_flag := 7;
899 else
900 l_process_flag := 3;
901 l_ret_status := 1;
902 end if;
903
904 Update mtl_item_revisions_interface
905 set process_flag = l_process_flag,
906 REQUEST_ID = Fnd_Global.Conc_Request_Id,
907 PROGRAM_ID = Fnd_Global.Conc_program_Id,
908 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
909 PROGRAM_UPDATE_DATE = sysdate
910 where transaction_id = l_bom_revision_rec.row_identifier;
911 END LOOP;
912
913 stmt_num :=2;
914 FOR I IN 1..l_bom_component_tbl.COUNT LOOP
915
916 l_bom_component_rec := l_bom_component_tbl(I);
917
918 if l_bom_component_rec.Return_Status IS NULL then
919 l_process_flag := 1;
920 elsif (l_bom_component_rec.Return_Status = 'S') then
921 l_process_flag := 7;
922 else
923 l_process_flag := 3;
924 l_ret_status := 1;
925 end if;
926
927 Update bom_inventory_comps_interface
928 set process_flag = l_process_flag,
929 REQUEST_ID = Fnd_Global.Conc_Request_Id,
930 PROGRAM_ID = Fnd_Global.Conc_program_Id,
931 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
932 PROGRAM_UPDATE_DATE = sysdate
933 where transaction_id = l_bom_component_rec.row_identifier;
934 END LOOP;
935
936 stmt_num :=3;
937 FOR I IN 1..l_bom_ref_designator_tbl.COUNT LOOP
938
939 l_bom_ref_designator_rec := l_bom_ref_designator_tbl(I);
940
941 if l_bom_ref_designator_rec.Return_Status IS NULL then
942 l_process_flag := 1;
943 elsif (l_bom_ref_designator_rec.Return_Status = 'S') then
944 l_process_flag := 7;
945 else
946 l_process_flag := 3;
947 l_ret_status := 1;
948 end if;
949
950 Update bom_ref_desgs_interface
951 set process_flag = l_process_flag,
952 REQUEST_ID = Fnd_Global.Conc_Request_Id,
953 PROGRAM_ID = Fnd_Global.Conc_program_Id,
954 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
955 PROGRAM_UPDATE_DATE = sysdate
956 where transaction_id = l_bom_ref_designator_rec.row_identifier;
957 END LOOP;
958
959 stmt_num :=4;
960 FOR I IN 1..l_bom_sub_component_tbl.COUNT LOOP
961 l_bom_sub_component_rec := l_bom_sub_component_tbl(I);
962
963 if l_bom_sub_component_rec.Return_Status IS NULL then
964 l_process_flag := 1;
965 elsif (l_bom_sub_component_rec.Return_Status = 'S') then
966 l_process_flag := 7;
967 else
968 l_process_flag := 3;
969 l_ret_status := 1;
970 end if;
971
972 Update BOM_SUB_COMPS_INTERFACE
973 set process_flag = l_process_flag,
974 REQUEST_ID = Fnd_Global.Conc_Request_Id,
975 PROGRAM_ID = Fnd_Global.Conc_program_Id,
976 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
977 PROGRAM_UPDATE_DATE = sysdate
978 where transaction_id = l_bom_sub_component_rec.row_identifier;
979 END LOOP;
980
981 stmt_num :=5;
982 FOR I IN 1..l_bom_comp_ops_tbl.COUNT LOOP
983 l_bom_comp_ops_rec := l_bom_comp_ops_tbl(I);
984
985 if l_bom_comp_ops_rec.Return_Status IS NULL
986 then l_process_flag := 1;
987 elsif (l_bom_comp_ops_rec.Return_Status = 'S') then
988 l_process_flag := 7;
989 else
990 l_process_flag := 3;
991 l_ret_status := 1;
992 end if;
993
994 Update BOM_COMPONENT_OPS_INTERFACE
995 set process_flag = l_process_flag,
996 REQUEST_ID = Fnd_Global.Conc_Request_Id,
997 PROGRAM_ID = Fnd_Global.Conc_program_Id,
998 PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
999 PROGRAM_UPDATE_DATE = sysdate
1000 where transaction_id = l_bom_comp_ops_rec.row_identifier;
1001 END LOOP;
1002
1003 return (l_ret_status);
1004
1005 EXCEPTION
1006 WHEN others THEN
1007 err_text := 'Update_Interface_Tables'||stmt_num||substrb(SQLERRM,1,500);
1008 RETURN(SQLCODE);
1009
1010 end Update_Interface_tables;
1011
1012
1013
1014 FUNCTION Delete_Bom_OI (
1015 err_text IN OUT NOCOPY VARCHAR2,
1016 p_batch_id IN NUMBER
1017 )
1018 return INTEGER
1019 IS
1020 stmt_num NUMBER;
1021 BEGIN
1022
1023 stmt_num := 1;
1024 loop
1025 DELETE FROM BOM_BILL_OF_MTLS_INTERFACE
1026 WHERE PROCESS_FLAG = 7
1027 AND
1028 (
1029 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1030 OR ( p_batch_id = BATCH_ID )
1031 )
1032 AND rownum < 500;
1033 exit when SQL%NOTFOUND ;
1034 commit;
1035 end loop;
1036
1037 stmt_num := 2;
1038 loop
1039 DELETE FROM BOM_INVENTORY_COMPS_INTERFACE
1040 WHERE PROCESS_FLAG = 7
1041 AND
1042 (
1043 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1044 OR ( p_batch_id = BATCH_ID )
1045 )
1046 AND rownum < 500;
1047 exit when SQL%NOTFOUND ;
1048 commit;
1049 end loop;
1050
1051 stmt_num := 3;
1052 loop
1053 DELETE FROM BOM_REF_DESGS_INTERFACE
1054 WHERE PROCESS_FLAG = 7
1055 AND
1056 (
1057 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1058 OR ( p_batch_id = BATCH_ID )
1059 )
1060 AND rownum < 500;
1061 exit when SQL%NOTFOUND ;
1062 commit;
1063 end loop;
1064
1065 stmt_num := 4;
1066 loop
1067 DELETE FROM BOM_COMPONENT_OPS_INTERFACE
1068 WHERE PROCESS_FLAG = 7
1069 AND
1070 (
1071 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1072 OR ( p_batch_id = BATCH_ID )
1073 )
1074 AND rownum < 500;
1075 exit when SQL%NOTFOUND ;
1076 commit;
1077 end loop;
1078
1079 stmt_num := 5;
1080 loop
1081 DELETE FROM BOM_SUB_COMPS_INTERFACE
1082 WHERE PROCESS_FLAG = 7
1083 AND
1084 (
1085 ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
1086 OR ( p_batch_id = BATCH_ID )
1087 )
1088 AND rownum < 500;
1089 exit when SQL%NOTFOUND ;
1090 commit;
1091 end loop;
1092
1093 stmt_num := 6;
1094 loop
1095 DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
1096 WHERE PROCESS_FLAG = 7
1097 AND SET_PROCESS_ID = NVL(p_batch_id,0)
1098 AND rownum < 500;
1099 exit when SQL%NOTFOUND ;
1100 commit;
1101 end loop;
1102
1103 return(0);
1104
1105 EXCEPTION
1106 when OTHERS THEN
1107 err_text := 'delete_bom_oi(' || stmt_num || ')' || substrb(SQLERRM,1,240);
1108 return(SQLCODE);
1109 END Delete_Bom_OI;
1110
1111
1112 END BOM_OPEN_INTERFACE_API;