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