[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_COPYORG_IMP
Source
1 PACKAGE BODY BOM_BOM_COPYORG_IMP AS
2 /* $Header: BOMBOCPB.pls 120.2.12000000.2 2007/02/26 11:35:32 myerrams ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMBOCPB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_BOM_COPYORG_IMP
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 05-JUN-06 Mohan Yerramsetty Bug# 5142847, Initial Creation.
21 -- This package has PL/SQL logic of Copying
22 -- BOMs. It doesn't use Exporting to XML,
23 -- Importing from XML Logic. This will fetch
24 -- all Boms from source organization and
25 -- pass all the records to Bom Interface.
26 -- Bom Interface will do the copying.
27 -- 03-JUL-06 Mohan Yerramsetty Added ImplementationDate to bom_bill_of_materials_temp table
28 -- Added BasisType to bom_inventory_components_temp table
29 -- Bug: 5174575
30 -- 11-NOV-06 Mohan Yerramsetty Modified the code to delete the successfully processed
31 -- records after the call to BOM_BO_PUB.Process_Bom to reduce
32 -- the memory consumed by the process. Bug: 5654718
33 -- 21-FEB-07 Mohan Yerramsetty Bug# 5592181, Modified the code to delete
34 -- unnecessary Log messages and to modify the logic of
35 -- returning the value of ReturnStatus.
36 ***************************************************************************/
37 /* myerrams, Bug: 5142847 */
38 G_PKG_NAME CONSTANT VARCHAR2(50) := 'BOM_BOM_COPYORG_IMP';
39 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
40 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'copyboms.plsql.' || G_PKG_NAME || '.';
41 g_api_name CONSTANT VARCHAR2(30) := 'Import_BOM';
42 g_X_return_status VARCHAR2(10); --myerrams, Bug: 5592181
43 g_status_set BOOLEAN := FALSE; --myerrams, Bug: 5592181
44
45
46 PROCEDURE CALL_PROCESS_BOM(P_debug IN VARCHAR2,
47 P_output_dir IN VARCHAR2,
48 P_debug_filename IN VARCHAR2,
49 p_bom_header_tbl IN BOM_BO_PUB.BOM_HEADER_TBL_TYPE ,
50 p_bom_component_tbl IN BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
51 p_bom_ref_designator_tbl IN BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
52 p_bom_sub_component_tbl IN BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
53 p_bom_comp_ops_tbl IN BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
54 X_bom_header_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_HEADER_TBL_TYPE ,
55 X_bom_revisions_tbl IN OUT NOCOPY BOM_BO_PUB.Bom_Revision_Tbl_Type,
56 X_bom_comp_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
57 X_bom_ref_desig_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
58 X_bom_sub_comp_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
59 X_bom_comp_oper_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
60 X_return_status IN OUT NOCOPY VARCHAR2, --myerrams, Bug: 5592181
61 X_G_msg_data OUT NOCOPY LONG,
62 X_msg_count OUT NOCOPY NUMBER
63 );
64
65 PROCEDURE Import_BOM(P_debug IN VARCHAR2 := 'N',
66 P_output_dir IN VARCHAR2 := NULL,
67 P_debug_filename IN VARCHAR2 := 'BOM_BO_debug.log',
68 p_model_org_id NUMBER,
69 p_target_orgcode VARCHAR2,
70 X_return_status OUT NOCOPY VARCHAR2,
71 X_msg_count OUT NOCOPY NUMBER,
72 X_G_msg_data OUT NOCOPY LONG,
73 p_bomthreshold IN VARCHAR2 ) IS
74
75 l_bom_header_tbl BOM_BO_PUB.BOM_HEADER_TBL_TYPE ;
76 l_bom_comp_tbl BOM_BO_PUB.BOM_COMPS_TBL_TYPE;
77 l_bom_header_tbl_rec BOM_BO_PUB.Bom_Head_Rec_Type ;
78 l_bom_comp_tbl_rec BOM_BO_PUB.Bom_Comps_Rec_Type ;
79 l_bom_ref_desig_tbl BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE;
80 l_bom_sub_comp_tbl BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE;
81 l_bom_comp_oper_tbl BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE;
82 X_bom_header_tbl BOM_BO_PUB.BOM_HEADER_TBL_TYPE ;
83 X_bom_revisions_tbl BOM_BO_PUB.BOM_REVISION_TBL_TYPE;
84 X_bom_comp_tbl BOM_BO_PUB.BOM_COMPS_TBL_TYPE;
85 X_bom_ref_desig_tbl BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE;
86 X_bom_sub_comp_tbl BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE;
87 X_bom_comp_oper_tbl BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE;
88 /* myerrams, Bug: 5142847; End; */
89
90 CURSOR bom_header_temp_CUR IS
91 SELECT bom.BILL_SEQUENCE_ID BILL_SEQUENCE_ID, --myerrams, Bug: 5142847
92 item1.concatenated_segments AssemblyItemName,
93 p_target_orgcode OrganizationCode,
94 bom.alternate_bom_designator AlternateBomCode,
95 item2.concatenated_segments CommonAssemblyItemName,
96 org2.organization_code CommonOrganizationCode,
97 bom.specific_assembly_comment AssemblyComment,
98 bom.assembly_type AssemblyType,
99 'CREATE' TransactionType,
100 NULL ReturnStatus,
101 bom.attribute_category AttributeCategory,
102 bom.attribute1 Attribute1,
103 bom.attribute2 Attribute2,
104 bom.attribute3 Attribute3,
105 bom.attribute4 Attribute4,
106 bom.attribute5 Attribute5,
107 bom.attribute6 Attribute6,
108 bom.attribute7 Attribute7,
109 bom.attribute8 Attribute8,
110 bom.attribute9 Attribute9,
111 bom.attribute10 Attribute10,
112 bom.attribute11 Attribute11,
113 bom.attribute12 Attribute12,
114 bom.attribute13 Attribute13,
115 bom.attribute14 Attribute14,
116 bom.attribute15 Attribute15,
117 bom.original_system_reference OriginalSystemReference,
118 NULL DeleteGroupName,
119 NULL DGDescription,
120 NULL RowIdentifier,
121 to_char(bom.implementation_date,'YYYY-MM-DD HH24:MI:SS') ImplementationDate --Bug: 5174575: New attribute added for R12
122 FROM bom_bill_of_materials bom,
123 mtl_system_items_kfv item1,
124 mtl_parameters org1,
125 mtl_parameters org2,
126 mtl_system_items_kfv item2
127 WHERE org1.organization_id = bom.organization_id
128 AND item1.organization_id = bom.organization_id
129 AND item1.inventory_item_id = bom.assembly_item_id
130 AND org2.organization_id(+) = bom.common_organization_id
131 AND item2.organization_id(+) = bom.common_organization_id
132 AND item2.inventory_item_id(+) = bom.common_assembly_item_id
133 AND bom.organization_id = p_model_org_id
134 Order by bom.alternate_bom_designator desc, bom.assembly_item_id; --myerrams, Bug: 5142847
135
136 --myerrams, Bug: 5142847 CURSOR bom_comp_CUR IS
137 CURSOR bom_comp_CUR (BILL_SEQUENCE_ID_VAR IN NUMBER) IS
138 SELECT comp.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID, --myerrams, Bug: 5142847
139 p_target_orgcode OrganizationCode,
140 item1.concatenated_segments AssemblyItemName,
141 TO_CHAR(comp.effectivity_date,'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
142 TO_CHAR(comp.disable_date, 'YYYY-MM-DD HH24:MI:SS') DisableDate,
143 comp.operation_seq_num OperationSequenceNumber,
144 item2.concatenated_segments ComponentItemName,
145 bom.alternate_bom_designator AlternateBOMCode,
146 null NewEffectivityDate,
147 null NewOperationSequenceNumber,
148 item_num ItemSequenceNumber,
149 comp.component_quantity QuantityPerAssembly,
150 comp.planning_factor PlanningPercent,
151 comp.component_yield_factor ProjectedYield,
152 comp.include_in_cost_rollup IncludeInCostRollup,
153 comp.wip_supply_type WipSupplyType,
154 comp.so_basis SoBasis,
155 comp.optional Optional,
156 comp.mutually_exclusive_options MutuallyExclusive,
157 comp.check_atp CheckAtp,
158 comp.shipping_allowed ShippingAllowed,
159 comp.required_to_ship RequiredToShip,
160 comp.required_for_revenue RequiredForRevenue,
161 comp.include_on_ship_docs IncludeOnShipDocs,
162 comp.quantity_related QuantityRelated,
163 comp.supply_subinventory SupplySubinventory,
164 null LocationName,
165 comp.low_quantity MinimumAllowedQuantity,
166 comp.high_quantity MaximumAllowedQuantity,
167 comp.component_remarks Comments,
168 comp.attribute_category AttributeCategory,
169 comp.attribute1 Attribute1,
170 comp.attribute2 Attribute2,
171 comp.attribute3 Attribute3,
172 comp.attribute4 Attribute4,
173 comp.attribute5 Attribute5,
174 comp.attribute6 Attribute6,
175 comp.attribute7 Attribute7,
176 comp.attribute8 Attribute8,
177 comp.attribute9 Attribute9,
178 comp.attribute10 Attribute10,
179 comp.attribute11 Attribute11,
180 comp.attribute12 Attribute12,
181 comp.attribute13 Attribute13,
182 comp.attribute14 Attribute14,
183 comp.attribute15 Attribute15,
184 comp.from_end_item_unit_number FromEndItemUnitNumber,
185 null NewFromEndItemUnitNumber,
186 comp.to_end_item_unit_number ToEndItemUnitNumber,
187 NULL ReturnStatus,
188 'CREATE' TransactionType,
189 comp.original_system_reference OriginalSystemReference,
190 NULL DeleteGroupName,
191 NULL DGDescription,
192 comp.enforce_int_requirements EnforceIntRequirements,
193 NULL AutoRequestMaterial,
194 NULL RowIdentifier,
195 comp.suggested_vendor_name SuggestedVendorName,
196 comp.unit_price UnitPrice,
197 comp.basis_type BasisType --Bug: 5174575: New attribute added for R12
198 FROM bom_inventory_components comp,
199 mtl_system_items_kfv item1,
200 mtl_parameters org,
201 bom_bill_of_materials bom,
202 mtl_system_items_kfv item2
203 WHERE bom.bill_sequence_id = comp.bill_sequence_id
204 AND org.organization_id = bom.organization_id
205 AND item1.organization_id = bom.organization_id
206 AND item1.inventory_item_id = bom.assembly_item_id
207 AND item2.organization_id = bom.organization_id
208 AND item2.inventory_item_id = comp.component_item_id
209 AND bom.organization_id = p_model_org_id
210 and comp.BILL_SEQUENCE_ID = BILL_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
211
212 --myerrams, Bug: 5142847 CURSOR bom_sub_comp_CUR IS
213 CURSOR bom_sub_comp_CUR (COMPONENT_SEQUENCE_ID_VAR IN NUMBER) IS
214 SELECT p_target_orgcode OrganizationCode,
215 item1.concatenated_segments AssemblyItemName,
216 TO_CHAR(comp.effectivity_date, 'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
217 comp.operation_seq_num OperationSequenceNumber,
218 item3.concatenated_segments ComponentItemName,
219 bom.alternate_bom_designator AlternateBomCode,
220 item2.concatenated_segments SubstituteComponentName,
221 NULL NewSubstituteComponentName,
222 sub.substitute_item_quantity SubstituteItemQuantity,
223 sub.attribute_category AttributeCategory,
224 sub.attribute1 Attribute1,
225 sub.attribute2 Attribute2,
226 sub.attribute3 Attribute3,
227 sub.attribute4 Attribute4,
228 sub.attribute5 Attribute5,
229 sub.attribute6 Attribute6,
230 sub.attribute7 Attribute7,
231 sub.attribute8 Attribute8,
232 sub.attribute9 Attribute9,
233 sub.attribute10 Attribute10,
234 sub.attribute11 Attribute11,
235 sub.attribute12 Attribute12,
236 sub.attribute13 Attribute13,
237 sub.attribute14 Attribute14,
238 sub.attribute15 Attribute15,
239 null ProgramId,
240 null FromEndItemUnitNumber,
241 sub.enforce_int_requirements EnforceIntRequirements,
242 sub.original_system_reference OriginalSystemReference,
243 NULL ReturnStatus,
244 'CREATE' TransactionType,
245 NULL RowIdentifier
246 FROM bom_substitute_components sub,
247 mtl_system_items_kfv item1,
248 mtl_parameters org,
249 bom_bill_of_materials bom,
250 bom_inventory_components comp,
251 mtl_system_items_kfv item2,
252 mtl_system_items_kfv item3
253 WHERE comp.component_sequence_id = sub.component_sequence_id
254 AND bom.bill_sequence_id = comp.bill_sequence_id
255 AND org.organization_id = bom.organization_id
256 AND item1.inventory_item_id = bom.assembly_item_id
257 AND item1.organization_id = bom.organization_id
258 AND item2.inventory_item_id = sub.substitute_component_id
259 AND item2.organization_id = bom.organization_id
260 AND item3.organization_id = bom.organization_id
261 AND item3.inventory_item_id = comp.component_item_id
262 AND bom.organization_id = p_model_org_id
263 AND sub.COMPONENT_SEQUENCE_ID = COMPONENT_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
264
265 --myerrams, Bug: 5142847 CURSOR bom_ref_desig_CUR IS
266 CURSOR bom_ref_desig_CUR (COMPONENT_SEQUENCE_ID_VAR IN NUMBER) IS
267 SELECT p_target_orgcode OrganizationCode,
268 item1.concatenated_segments AssemblyItemName,
269 TO_CHAR(comp.effectivity_date, 'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
270 comp.operation_seq_num OperationSequenceNumber,
271 item2.concatenated_segments ComponentItemName,
272 bom.alternate_bom_designator AlternateBomCode,
273 ref.component_reference_designator ReferenceDesignatorName,
274 ref.ref_designator_comment RefDesignatorComment,
275 ref.attribute_category AttributeCategory,
276 ref.attribute1 Attribute1,
277 ref.attribute2 Attribute2,
278 ref.attribute3 Attribute3,
279 ref.attribute4 Attribute4,
280 ref.attribute5 Attribute5,
281 ref.attribute6 Attribute6,
282 ref.attribute7 Attribute7,
283 ref.attribute8 Attribute8,
284 ref.attribute9 Attribute9,
285 ref.attribute10 Attribute10,
286 ref.attribute11 Attribute11,
287 ref.attribute12 Attribute12,
288 ref.attribute13 Attribute13,
289 ref.attribute14 Attribute14,
290 ref.attribute15 Attribute15,
291 null FromEndItemUnitNumber,
292 ref.original_system_reference OriginalSystemReference,
293 null NewReferenceDesignator,
294 NULL ReturnStatus,
295 'CREATE' TransactionType,
296 NULL RowIdentifier
297 FROM bom_reference_designators ref,
298 mtl_system_items_kfv item1,
299 mtl_parameters org,
300 bom_inventory_components comp,
301 mtl_system_items_kfv item2,
302 bom_bill_of_materials bom
303 WHERE comp.component_sequence_id = ref.component_sequence_id
304 AND bom.bill_sequence_id = comp.bill_sequence_id
305 AND org.organization_id = bom.organization_id
306 AND item1.organization_id = bom.organization_id
307 AND item1.inventory_item_id = bom.assembly_item_id
308 AND item2.organization_id = bom.organization_id
312
309 AND item2.inventory_item_id = comp.component_item_id
310 AND bom.organization_id = p_model_org_id
311 AND ref.COMPONENT_SEQUENCE_ID = COMPONENT_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
313 --myerrams, Bug: 5142847 CURSOR bom_comp_oper_CUR IS
314 CURSOR bom_comp_oper_CUR (BILL_SEQUENCE_ID_VAR IN NUMBER, COMPONENT_SEQUENCE_ID_VAR IN NUMBER) IS
315 SELECT p_target_orgcode OrganizationCode,
316 item1.concatenated_segments AssemblyItemName,
317 to_char(comp.effectivity_date,'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
318 null FromEndItemUnitNumber,
319 null ToEndItemUnitNumber,
320 comp.operation_seq_num OperationSequenceNumber,
321 comp_oper.operation_seq_num AdditionalOperationSeqNum,
322 NULL NewAdditionalOpSeqNum,
323 item2.concatenated_segments ComponentItemName,
324 bom.alternate_bom_designator AlternateBOMCode,
325 comp_oper.attribute_category Attributecategory,
326 comp_oper.attribute1 Attribute1,
327 comp_oper.attribute2 Attribute2,
328 comp_oper.attribute3 Attribute3,
329 comp_oper.attribute4 Attribute4,
330 comp_oper.attribute5 Attribute5,
331 comp_oper.attribute6 Attribute6,
332 comp_oper.attribute7 Attribute7,
333 comp_oper.attribute8 Attribute8,
334 comp_oper.attribute9 Attribute9,
335 comp_oper.attribute10 Attribute10,
336 comp_oper.attribute11 Attribute11,
337 comp_oper.attribute12 Attribute12,
338 comp_oper.attribute13 Attribute13,
339 comp_oper.attribute14 Attribute14,
340 comp_oper.attribute15 Attribute15,
341 NULL ReturnStatus,
342 'CREATE' TransactionType,
343 NULL RowIdentifier
344 FROM bom_component_operations comp_oper,
345 mtl_system_items_kfv item1,
346 mtl_parameters org,
347 bom_inventory_components comp,
348 mtl_system_items_kfv item2,
349 bom_bill_of_materials bom
350 WHERE comp.component_sequence_id = comp_oper.component_sequence_id
351 AND bom.bill_sequence_id = comp.bill_sequence_id
352 AND org.organization_id = bom.organization_id
353 AND item1.organization_id = bom.organization_id
354 AND item1.inventory_item_id = bom.assembly_item_id
355 AND item2.organization_id = bom.organization_id
356 AND item2.inventory_item_id = comp.component_item_id
357 AND bom.organization_id = p_model_org_id
358 AND comp_oper.COMPONENT_SEQUENCE_ID = COMPONENT_SEQUENCE_ID_VAR
359 AND comp_oper.BILL_SEQUENCE_ID = BILL_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
360
361
362 /* myerrams, Bug: 5142847; Following Variables are used for looping through the Header and its related entities */
363 i NUMBER;
364 j NUMBER;
365 k NUMBER;
366 l NUMBER;
367 m NUMBER;
368
369 l_threshold_count NUMBER;
370 l_threshold_value NUMBER;
371 l_submit_flag BOOLEAN;
372 l_BILL_SEQUENCE_ID NUMBER;
373 l_COMPONENT_SEQUENCE_ID NUMBER;
374
375
376 /* Following variables are used for logging messages */
377 l_message VARCHAR2(2000) := NULL;
378 l_entity VARCHAR2(3) := NULL;
379 l_msg_index NUMBER;
380 l_message_type VARCHAR2(1);
381
382 BEGIN
383 /*myerrams, to make sure that Return Status is not null when the model org doesn't have any Boms to copy*/
384 X_return_status := 'S';
385 --myerrams, Bug: 5142847; Default the Threshold value to 10,000 in case if it is null
386 IF p_bomthreshold IS NOT NULL THEN
387 l_threshold_value := TO_NUMBER(p_bomthreshold);
388 ELSE
389 l_threshold_value := 10000;
390 END IF;
391 IF g_fnd_debug = 'Y' AND
392 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
393 THEN
394 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
395 , G_MODULE_PREFIX || g_api_name || '.begin'
396 , NULL);
397 END IF;
398
399 /* myerrams, Bug: 5142847; Initialize all the count variables */
400 i := 1;
401 j := 1;
402 k := 1;
403 l := 1;
404 m := 1;
405
406 l_threshold_count := 1;
407 l_submit_flag := FALSE;
408
409 FOR bom_header_rec IN bom_header_temp_CUR LOOP
410 l_BILL_SEQUENCE_ID := bom_header_rec.BILL_SEQUENCE_ID; --myerrams, Bug: 5142847
411 l_bom_header_tbl(i).assembly_item_name := bom_header_rec.AssemblyItemName;
412 l_bom_header_tbl(i).organization_code := bom_header_rec.OrganizationCode;
413 l_bom_header_tbl(i).alternate_bom_code := bom_header_rec.AlternateBomCode;
414 l_bom_header_tbl(i).common_assembly_item_name := bom_header_rec.CommonAssemblyItemName;
415 l_bom_header_tbl(i).common_organization_code := bom_header_rec.CommonOrganizationCode;
416 l_bom_header_tbl(i).assembly_comment := bom_header_rec.AssemblyComment;
417 l_bom_header_tbl(i).assembly_type := bom_header_rec.AssemblyType;
418 l_bom_header_tbl(i).transaction_type := bom_header_rec.TransactionType;
419 l_bom_header_tbl(i).return_status := bom_header_rec.ReturnStatus;
420 l_bom_header_tbl(i).attribute_category := bom_header_rec.AttributeCategory;
421 l_bom_header_tbl(i).attribute1 := bom_header_rec.Attribute1;
422 l_bom_header_tbl(i).attribute2 := bom_header_rec.Attribute2;
423 l_bom_header_tbl(i).attribute3 := bom_header_rec.Attribute3;
424 l_bom_header_tbl(i).attribute4 := bom_header_rec.Attribute4;
428 l_bom_header_tbl(i).attribute8 := bom_header_rec.Attribute8;
425 l_bom_header_tbl(i).attribute5 := bom_header_rec.Attribute5;
426 l_bom_header_tbl(i).attribute6 := bom_header_rec.Attribute6;
427 l_bom_header_tbl(i).attribute7 := bom_header_rec.Attribute7;
429 l_bom_header_tbl(i).attribute9 := bom_header_rec.Attribute9;
430 l_bom_header_tbl(i).attribute10 := bom_header_rec.Attribute10;
431 l_bom_header_tbl(i).attribute11 := bom_header_rec.Attribute11;
432 l_bom_header_tbl(i).attribute12 := bom_header_rec.Attribute12;
433 l_bom_header_tbl(i).attribute13 := bom_header_rec.Attribute13;
434 l_bom_header_tbl(i).attribute14 := bom_header_rec.Attribute14;
435 l_bom_header_tbl(i).attribute15 := bom_header_rec.Attribute15;
436 l_bom_header_tbl(i).original_system_reference := bom_header_rec.OriginalSystemReference;
437 l_bom_header_tbl(i).delete_group_name := bom_header_rec.DeleteGroupName;
438 l_bom_header_tbl(i).dg_description := bom_header_rec.DGDescription;
439 l_bom_header_tbl(i).row_identifier := bom_header_rec.RowIdentifier;
440 --Bug: 5174575: New attribute added for R12
441 IF (bom_header_rec.ImplementationDate IS NOT NULL) THEN
442 l_bom_header_tbl(i).Bom_Implementation_Date := TO_DATE(bom_header_rec.ImplementationDate, 'YYYY-MM-DD HH24:MI:SS');
443 END IF;
444
445 /* myerrams, Bug: 5142847; Check out if the threshold has reached*/
446 l_threshold_count := l_threshold_count + 1;
447 IF l_threshold_count >= l_threshold_value then
448 l_submit_flag := TRUE;
449 END IF;
450 l_bom_header_tbl_rec := l_bom_header_tbl(i);
451
452 FOR bom_comp_rec IN bom_comp_CUR (l_BILL_SEQUENCE_ID) LOOP
453 l_COMPONENT_SEQUENCE_ID := bom_comp_rec.COMPONENT_SEQUENCE_ID;
454 l_bom_comp_tbl(j).organization_code := bom_comp_rec.OrganizationCode;
455 l_bom_comp_tbl(j).assembly_item_name := bom_comp_rec.AssemblyItemName;
456 l_bom_comp_tbl(j).start_effective_date := TO_DATE(bom_comp_rec.StartEffectiveDate, 'YYYY-MM-DD HH24:MI:SS');
457 IF (bom_comp_rec.DisableDate IS NOT NULL) THEN
458 l_bom_comp_tbl(j).disable_date := TO_DATE(bom_comp_rec.DisableDate, 'YYYY-MM-DD HH24:MI:SS');
459 END IF;
460 l_bom_comp_tbl(j).operation_sequence_number := bom_comp_rec.OperationSequenceNumber;
461 l_bom_comp_tbl(j).component_item_name := bom_comp_rec.ComponentItemName;
462 l_bom_comp_tbl(j).alternate_bom_code := bom_comp_rec.AlternateBomCode;
463 IF (bom_comp_rec.NewEffectivityDate IS NOT NULL) THEN
464 l_bom_comp_tbl(j).new_effectivity_date := TO_DATE(bom_comp_rec.NewEffectivityDate, 'YYYY-MM-DD HH24:MI:SS');
465 END IF;
466 l_bom_comp_tbl(j).new_operation_sequence_number := bom_comp_rec.NewOperationSequenceNumber;
467 l_bom_comp_tbl(j).item_sequence_number := bom_comp_rec.ItemSequenceNumber;
468 l_bom_comp_tbl(j).quantity_per_assembly := TO_NUMBER(bom_comp_rec.QuantityPerAssembly);
469 l_bom_comp_tbl(j).planning_percent := bom_comp_rec.PlanningPercent;
470 l_bom_comp_tbl(j).projected_yield := TO_NUMBER(bom_comp_rec.ProjectedYield);
471 l_bom_comp_tbl(j).include_in_cost_rollup := bom_comp_rec.IncludeInCostRollup;
472 l_bom_comp_tbl(j).wip_supply_type := bom_comp_rec.WipSupplyType;
473 l_bom_comp_tbl(j).so_basis := bom_comp_rec.SoBasis;
474 l_bom_comp_tbl(j).optional := bom_comp_rec.Optional;
475 l_bom_comp_tbl(j).mutually_exclusive := bom_comp_rec.MutuallyExclusive;
476 l_bom_comp_tbl(j).check_atp := bom_comp_rec.CheckAtp;
477 l_bom_comp_tbl(j).shipping_allowed := bom_comp_rec.ShippingAllowed;
478 l_bom_comp_tbl(j).required_to_ship := bom_comp_rec.RequiredToShip;
479 l_bom_comp_tbl(j).required_for_revenue := bom_comp_rec.RequiredForRevenue;
480 l_bom_comp_tbl(j).include_on_ship_docs := bom_comp_rec.IncludeOnShipDocs;
481 l_bom_comp_tbl(j).quantity_related := bom_comp_rec.QuantityRelated;
482 l_bom_comp_tbl(j).supply_subinventory := bom_comp_rec.SupplySubinventory;
483 l_bom_comp_tbl(j).location_name := bom_comp_rec.LocationName;
484 l_bom_comp_tbl(j).minimum_allowed_quantity := TO_NUMBER(bom_comp_rec.MinimumAllowedQuantity);
485 l_bom_comp_tbl(j).maximum_allowed_quantity := TO_NUMBER(bom_comp_rec.MaximumAllowedQuantity);
486 l_bom_comp_tbl(j).comments := bom_comp_rec.Comments;
487 l_bom_comp_tbl(j).attribute_category := bom_comp_rec.AttributeCategory;
488 l_bom_comp_tbl(j).attribute1 := bom_comp_rec.Attribute1;
489 l_bom_comp_tbl(j).attribute2 := bom_comp_rec.Attribute2;
490 l_bom_comp_tbl(j).attribute3 := bom_comp_rec.Attribute3;
491 l_bom_comp_tbl(j).attribute4 := bom_comp_rec.Attribute4;
492 l_bom_comp_tbl(j).attribute5 := bom_comp_rec.Attribute5;
493 l_bom_comp_tbl(j).attribute6 := bom_comp_rec.Attribute6;
494 l_bom_comp_tbl(j).attribute7 := bom_comp_rec.Attribute7;
495 l_bom_comp_tbl(j).attribute8 := bom_comp_rec.Attribute8;
496 l_bom_comp_tbl(j).attribute9 := bom_comp_rec.Attribute9;
497 l_bom_comp_tbl(j).attribute10 := bom_comp_rec.Attribute10;
501 l_bom_comp_tbl(j).attribute14 := bom_comp_rec.Attribute14;
498 l_bom_comp_tbl(j).attribute11 := bom_comp_rec.Attribute11;
499 l_bom_comp_tbl(j).attribute12 := bom_comp_rec.Attribute12;
500 l_bom_comp_tbl(j).attribute13 := bom_comp_rec.Attribute13;
502 l_bom_comp_tbl(j).attribute15 := bom_comp_rec.Attribute15;
503 l_bom_comp_tbl(j).from_end_item_unit_number := bom_comp_rec.FromEndItemUnitNumber;
504 l_bom_comp_tbl(j).new_from_end_item_unit_number := bom_comp_rec.NewFromEndItemUnitNumber;
505 l_bom_comp_tbl(j).to_end_item_unit_number := bom_comp_rec.ToEndItemUnitNumber;
506 l_bom_comp_tbl(j).return_status := bom_comp_rec.ReturnStatus;
507 l_bom_comp_tbl(j).transaction_type := bom_comp_rec.TransactionType;
508 l_bom_comp_tbl(j).original_system_reference := bom_comp_rec.OriginalSystemReference;
509 l_bom_comp_tbl(j).delete_group_name := bom_comp_rec.DeleteGroupName;
510 l_bom_comp_tbl(j).dg_description := bom_comp_rec.DgDescription;
511 l_bom_comp_tbl(j).enforce_int_requirements := bom_comp_rec.EnforceIntRequirements;
512 l_bom_comp_tbl(j).auto_request_material := bom_comp_rec.AutoRequestMaterial;
513 l_bom_comp_tbl(j).row_identifier := bom_comp_rec.RowIdentifier;
514 l_bom_comp_tbl(j).suggested_vendor_name := bom_comp_rec.SuggestedVendorName;
515 l_bom_comp_tbl(j).unit_price := bom_comp_rec.UnitPrice;
516 l_bom_comp_tbl(j).basis_type := bom_comp_rec.BasisType; --Bug: 5174575: New attribute added for R12
517
518 /* myerrams, Bug: 5142847; Check out if the threshold has reached*/
519 l_threshold_count := l_threshold_count + 1;
520 IF l_threshold_count >= l_threshold_value then
521 l_submit_flag := TRUE;
522 END IF;
523
524 l_bom_comp_tbl_rec := l_bom_comp_tbl(j);
525 FOR bom_sub_rec IN bom_sub_comp_CUR (l_COMPONENT_SEQUENCE_ID) LOOP
526 ----------------------------------------------------------------------------------------------------------------------------------
527 /* If the number of records are more than equal to 10,000 */
528 IF l_submit_flag = TRUE THEN
529 CALL_PROCESS_BOM (p_debug => P_debug,
530 p_output_dir => P_output_dir,
531 p_debug_filename => P_debug_filename,
532 p_bom_header_tbl => l_bom_header_tbl,
533 p_bom_component_tbl => l_bom_comp_tbl,
534 p_bom_ref_designator_tbl => l_bom_ref_desig_tbl,
535 p_bom_sub_component_tbl => l_bom_sub_comp_tbl,
536 p_bom_comp_ops_tbl => l_bom_comp_oper_tbl,
537 X_bom_header_tbl => X_bom_header_tbl,
538 X_bom_revisions_tbl => X_bom_revisions_tbl,
539 X_bom_comp_tbl => X_bom_comp_tbl,
540 X_bom_ref_desig_tbl => X_bom_ref_desig_tbl,
541 X_bom_sub_comp_tbl => X_bom_sub_comp_tbl,
542 X_bom_comp_oper_tbl => X_bom_comp_oper_tbl,
543 x_return_status => X_return_status,
544 x_g_msg_data => X_G_msg_data,
545 x_msg_count => X_msg_count);
546
547 l_bom_header_tbl.DELETE;
548 l_bom_comp_tbl.DELETE;
549 l_bom_ref_desig_tbl.DELETE;
550 l_bom_sub_comp_tbl.DELETE;
551 l_bom_comp_oper_tbl.DELETE;
552 i := 1;
553 j := 1;
554 k := 1;
555 l := 1;
556 m := 1;
557 l_threshold_count := 1;
558 l_submit_flag := FALSE;
559
560 l_bom_comp_tbl(1) := l_bom_comp_tbl_rec;
561 l_bom_header_tbl(1) := l_bom_header_tbl_rec;
562 l_bom_header_tbl(1).transaction_type := 'UPDATE';
563 l_bom_comp_tbl(1) .transaction_type := 'UPDATE';
564
565 END IF; --END l_submit_flag condition
566 -----------------------------------------------------------------------------------------------------------
567 l_bom_sub_comp_tbl(k).organization_code := bom_sub_rec.OrganizationCode;
568 l_bom_sub_comp_tbl(k).assembly_item_name := bom_sub_rec.AssemblyItemName;
569 l_bom_sub_comp_tbl(k).start_effective_date := TO_DATE(bom_sub_rec.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS');
570 l_bom_sub_comp_tbl(k).operation_sequence_number := bom_sub_rec.OperationSequenceNumber;
571 l_bom_sub_comp_tbl(k).component_item_name := bom_sub_rec.ComponentItemName;
572 l_bom_sub_comp_tbl(k).alternate_bom_code := bom_sub_rec.AlternateBomCode;
573 l_bom_sub_comp_tbl(k).substitute_component_name := bom_sub_rec.SubstituteComponentName;
574 l_bom_sub_comp_tbl(k).new_substitute_component_name := bom_sub_rec.NewSubstituteComponentName;
575 l_bom_sub_comp_tbl(k).substitute_item_quantity := TO_NUMBER(bom_sub_rec.SubstituteItemQuantity);
576 l_bom_sub_comp_tbl(k).attribute_category := bom_sub_rec.AttributeCategory;
577 l_bom_sub_comp_tbl(k).attribute1 := bom_sub_rec.Attribute1;
578 l_bom_sub_comp_tbl(k).attribute2 := bom_sub_rec.Attribute2;
579 l_bom_sub_comp_tbl(k).attribute3 := bom_sub_rec.Attribute3;
580 l_bom_sub_comp_tbl(k).attribute4 := bom_sub_rec.Attribute4;
581 l_bom_sub_comp_tbl(k).attribute5 := bom_sub_rec.Attribute5;
585 l_bom_sub_comp_tbl(k).attribute9 := bom_sub_rec.Attribute9;
582 l_bom_sub_comp_tbl(k).attribute6 := bom_sub_rec.Attribute6;
583 l_bom_sub_comp_tbl(k).attribute7 := bom_sub_rec.Attribute7;
584 l_bom_sub_comp_tbl(k).attribute8 := bom_sub_rec.Attribute8;
586 l_bom_sub_comp_tbl(k).attribute10 := bom_sub_rec.Attribute10;
587 l_bom_sub_comp_tbl(k).attribute11 := bom_sub_rec.Attribute11;
588 l_bom_sub_comp_tbl(k).attribute12 := bom_sub_rec.Attribute12;
589 l_bom_sub_comp_tbl(k).attribute13 := bom_sub_rec.Attribute13;
590 l_bom_sub_comp_tbl(k).attribute14 := bom_sub_rec.Attribute14;
591 l_bom_sub_comp_tbl(k).attribute15 := bom_sub_rec.Attribute15;
592 l_bom_sub_comp_tbl(k).program_id := bom_sub_rec.ProgramId;
593 l_bom_sub_comp_tbl(k).from_end_item_unit_number := bom_sub_rec.FromEndItemUnitNumber;
594 l_bom_sub_comp_tbl(k).enforce_int_requirements := bom_sub_rec.EnforceIntRequirements;
595 l_bom_sub_comp_tbl(k).original_system_reference := bom_sub_rec.OriginalSystemReference;
596 l_bom_sub_comp_tbl(k).return_status := bom_sub_rec.ReturnStatus;
597 l_bom_sub_comp_tbl(k).transaction_type := bom_sub_rec.TransactionType;
598 l_bom_sub_comp_tbl(k).row_identifier := bom_sub_rec.RowIdentifier;
599
600 /* myerrams, Bug: 5142847; Check out if the threshold has reached*/
601 l_threshold_count := l_threshold_count + 1;
602 IF l_threshold_count >= l_threshold_value then
603 l_submit_flag := TRUE;
604 END IF;
605
606 k := k + 1;
607 END LOOP; --bom_sub_rec loop
608
609 FOR bom_ref_desig_rec IN bom_ref_desig_CUR(l_COMPONENT_SEQUENCE_ID) LOOP
610 ----------------------------------------------------------------------------------------------------------------------------------
611 /* If the number of records are more than equal to 10,000 */
612 IF l_submit_flag = TRUE THEN
613 CALL_PROCESS_BOM (p_debug => P_debug,
614 p_output_dir => P_output_dir,
615 p_debug_filename => P_debug_filename,
616 p_bom_header_tbl => l_bom_header_tbl,
617 p_bom_component_tbl => l_bom_comp_tbl,
618 p_bom_ref_designator_tbl => l_bom_ref_desig_tbl,
619 p_bom_sub_component_tbl => l_bom_sub_comp_tbl,
620 p_bom_comp_ops_tbl => l_bom_comp_oper_tbl,
621 X_bom_header_tbl => X_bom_header_tbl,
622 X_bom_revisions_tbl => X_bom_revisions_tbl,
623 X_bom_comp_tbl => X_bom_comp_tbl,
624 X_bom_ref_desig_tbl => X_bom_ref_desig_tbl,
625 X_bom_sub_comp_tbl => X_bom_sub_comp_tbl,
626 X_bom_comp_oper_tbl => X_bom_comp_oper_tbl,
627 x_return_status => X_return_status,
628 x_g_msg_data => X_G_msg_data,
629 x_msg_count => X_msg_count);
630
631 l_bom_header_tbl.DELETE;
632 l_bom_comp_tbl.DELETE;
633 l_bom_ref_desig_tbl.DELETE;
634 l_bom_sub_comp_tbl.DELETE;
635 l_bom_comp_oper_tbl.DELETE;
636 i := 1;
637 j := 1;
638 k := 1;
639 l := 1;
640 m := 1;
641 l_threshold_count := 1;
642 l_submit_flag := FALSE;
643
644 l_bom_comp_tbl(1) := l_bom_comp_tbl_rec;
645 l_bom_header_tbl(1) := l_bom_header_tbl_rec;
646 l_bom_header_tbl(1).transaction_type := 'UPDATE';
647 l_bom_comp_tbl(1) .transaction_type := 'UPDATE';
648
649 END IF; --END l_submit_flag condition
650 -----------------------------------------------------------------------------------------------------------
651 l_bom_ref_desig_tbl(l).organization_code := bom_ref_desig_rec.OrganizationCode;
652 l_bom_ref_desig_tbl(l).assembly_item_name := bom_ref_desig_rec.AssemblyItemName;
653 l_bom_ref_desig_tbl(l).start_effective_date := TO_DATE(bom_ref_desig_rec.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS');
654 l_bom_ref_desig_tbl(l).operation_sequence_number := bom_ref_desig_rec.OperationSequenceNumber;
655 l_bom_ref_desig_tbl(l).component_item_name := bom_ref_desig_rec.ComponentItemName;
656 l_bom_ref_desig_tbl(l).alternate_bom_code := bom_ref_desig_rec.AlternateBomCode;
657 l_bom_ref_desig_tbl(l).reference_designator_name := bom_ref_desig_rec.ReferenceDesignatorName;
658 l_bom_ref_desig_tbl(l).ref_designator_comment := bom_ref_desig_rec.RefDesignatorComment;
659 l_bom_ref_desig_tbl(l).attribute_category := bom_ref_desig_rec.AttributeCategory;
660 l_bom_ref_desig_tbl(l).attribute1 := bom_ref_desig_rec.Attribute1;
661 l_bom_ref_desig_tbl(l).attribute2 := bom_ref_desig_rec.Attribute2;
662 l_bom_ref_desig_tbl(l).attribute3 := bom_ref_desig_rec.Attribute3;
663 l_bom_ref_desig_tbl(l).attribute4 := bom_ref_desig_rec.Attribute4;
664 l_bom_ref_desig_tbl(l).attribute5 := bom_ref_desig_rec.Attribute5;
665 l_bom_ref_desig_tbl(l).attribute6 := bom_ref_desig_rec.Attribute6;
669 l_bom_ref_desig_tbl(l).attribute10 := bom_ref_desig_rec.Attribute10;
666 l_bom_ref_desig_tbl(l).attribute7 := bom_ref_desig_rec.Attribute7;
667 l_bom_ref_desig_tbl(l).attribute8 := bom_ref_desig_rec.Attribute8;
668 l_bom_ref_desig_tbl(l).attribute9 := bom_ref_desig_rec.Attribute9;
670 l_bom_ref_desig_tbl(l).attribute11 := bom_ref_desig_rec.Attribute11;
671 l_bom_ref_desig_tbl(l).attribute12 := bom_ref_desig_rec.Attribute12;
672 l_bom_ref_desig_tbl(l).attribute13 := bom_ref_desig_rec.Attribute13;
673 l_bom_ref_desig_tbl(l).attribute14 := bom_ref_desig_rec.Attribute14;
674 l_bom_ref_desig_tbl(l).attribute15 := bom_ref_desig_rec.Attribute15;
675 l_bom_ref_desig_tbl(l).from_end_item_unit_number := bom_ref_desig_rec.FromEndItemUnitNumber;
676 l_bom_ref_desig_tbl(l).original_system_reference := bom_ref_desig_rec.OriginalSystemReference;
677 l_bom_ref_desig_tbl(l).new_reference_designator := bom_ref_desig_rec.NewReferenceDesignator;
678 l_bom_ref_desig_tbl(l).return_status := bom_ref_desig_rec.ReturnStatus;
679 l_bom_ref_desig_tbl(l).transaction_type := bom_ref_desig_rec.TransactionType;
680 l_bom_ref_desig_tbl(l).row_identifier := bom_ref_desig_rec.RowIdentifier;
681
682
683 /* myerrams, Bug: 5142847; Check out if the threshold has reached*/
684 l_threshold_count := l_threshold_count + 1;
685 IF l_threshold_count >= l_threshold_value then
686 l_submit_flag := TRUE;
687 END IF;
688
689 l := l + 1;
690 END LOOP; --bom_ref_desig_rec loop
691
692 FOR bom_comp_oper_rec IN bom_comp_oper_CUR(l_BILL_SEQUENCE_ID, l_COMPONENT_SEQUENCE_ID) LOOP
693 ----------------------------------------------------------------------------------------------------------------------------------
694 /* If the number of records are more than equal to 10,000 */
695 IF l_submit_flag = TRUE THEN
696 CALL_PROCESS_BOM (p_debug => P_debug,
697 p_output_dir => P_output_dir,
698 p_debug_filename => P_debug_filename,
699 p_bom_header_tbl => l_bom_header_tbl,
700 p_bom_component_tbl => l_bom_comp_tbl,
701 p_bom_ref_designator_tbl => l_bom_ref_desig_tbl,
702 p_bom_sub_component_tbl => l_bom_sub_comp_tbl,
703 p_bom_comp_ops_tbl => l_bom_comp_oper_tbl,
704 X_bom_header_tbl => X_bom_header_tbl,
705 X_bom_revisions_tbl => X_bom_revisions_tbl,
706 X_bom_comp_tbl => X_bom_comp_tbl,
707 X_bom_ref_desig_tbl => X_bom_ref_desig_tbl,
708 X_bom_sub_comp_tbl => X_bom_sub_comp_tbl,
709 X_bom_comp_oper_tbl => X_bom_comp_oper_tbl,
710 x_return_status => X_return_status,
711 x_g_msg_data => X_G_msg_data,
712 x_msg_count => X_msg_count);
713
714 l_bom_header_tbl.DELETE;
715 l_bom_comp_tbl.DELETE;
716 l_bom_ref_desig_tbl.DELETE;
717 l_bom_sub_comp_tbl.DELETE;
718 l_bom_comp_oper_tbl.DELETE;
719 i := 1;
720 j := 1;
721 k := 1;
722 l := 1;
723 m := 1;
724 l_threshold_count := 1;
725 l_submit_flag := FALSE;
726
727 l_bom_comp_tbl(1) := l_bom_comp_tbl_rec;
728 l_bom_header_tbl(1) := l_bom_header_tbl_rec;
729 l_bom_header_tbl(1).transaction_type := 'UPDATE';
730 l_bom_comp_tbl(1) .transaction_type := 'UPDATE';
731
732 END IF; --END l_submit_flag condition
733 -----------------------------------------------------------------------------------------------------------
734 l_bom_comp_oper_tbl(m).organization_code := bom_comp_oper_rec.OrganizationCode;
735 l_bom_comp_oper_tbl(m).assembly_item_name := bom_comp_oper_rec.AssemblyItemName;
736 l_bom_comp_oper_tbl(m).start_effective_date := TO_DATE(bom_comp_oper_rec.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS');
737 l_bom_comp_oper_tbl(m).from_end_item_unit_number := bom_comp_oper_rec.FromEndItemUnitNumber;
738 l_bom_comp_oper_tbl(m).to_end_item_unit_number := bom_comp_oper_rec.ToEndItemUnitNumber;
739 l_bom_comp_oper_tbl(m).operation_sequence_number := bom_comp_oper_rec.OperationSequenceNumber;
740 l_bom_comp_oper_tbl(m).additional_operation_seq_num := bom_comp_oper_rec.AdditionalOperationSeqNum;
741 l_bom_comp_oper_tbl(m).new_additional_op_seq_num := bom_comp_oper_rec.NewAdditionalOpSeqNum;
742 l_bom_comp_oper_tbl(m).component_item_name := bom_comp_oper_rec.ComponentItemName;
743 l_bom_comp_oper_tbl(m).alternate_bom_code := bom_comp_oper_rec.AlternateBomCode;
744 l_bom_comp_oper_tbl(m).attribute_category := bom_comp_oper_rec.AttributeCategory;
745 l_bom_comp_oper_tbl(m).attribute1 := bom_comp_oper_rec.Attribute1;
746 l_bom_comp_oper_tbl(m).attribute2 := bom_comp_oper_rec.Attribute2;
747 l_bom_comp_oper_tbl(m).attribute3 := bom_comp_oper_rec.Attribute3;
748 l_bom_comp_oper_tbl(m).attribute4 := bom_comp_oper_rec.Attribute4;
752 l_bom_comp_oper_tbl(m).attribute8 := bom_comp_oper_rec.Attribute8;
749 l_bom_comp_oper_tbl(m).attribute5 := bom_comp_oper_rec.Attribute5;
750 l_bom_comp_oper_tbl(m).attribute6 := bom_comp_oper_rec.Attribute6;
751 l_bom_comp_oper_tbl(m).attribute7 := bom_comp_oper_rec.Attribute7;
753 l_bom_comp_oper_tbl(m).attribute9 := bom_comp_oper_rec.Attribute9;
754 l_bom_comp_oper_tbl(m).attribute10 := bom_comp_oper_rec.Attribute10;
755 l_bom_comp_oper_tbl(m).attribute11 := bom_comp_oper_rec.Attribute11;
756 l_bom_comp_oper_tbl(m).attribute12 := bom_comp_oper_rec.Attribute12;
757 l_bom_comp_oper_tbl(m).attribute13 := bom_comp_oper_rec.Attribute13;
758 l_bom_comp_oper_tbl(m).attribute14 := bom_comp_oper_rec.Attribute14;
759 l_bom_comp_oper_tbl(m).attribute15 := bom_comp_oper_rec.Attribute15;
760 l_bom_comp_oper_tbl(m).return_status := bom_comp_oper_rec.ReturnStatus;
761 l_bom_comp_oper_tbl(m).transaction_type := bom_comp_oper_rec.TransactionType;
762 l_bom_comp_oper_tbl(m).row_identifier := bom_comp_oper_rec.RowIdentifier;
763
764
765 /* myerrams, Bug: 5142847; Check out if the threshold has reached*/
766 l_threshold_count := l_threshold_count + 1;
767 IF l_threshold_count >= l_threshold_value then
768 l_submit_flag := TRUE;
769 END IF;
770
771 m := m + 1;
772 END LOOP; --bom_comp_oper_rec loop
773
774 j := j + 1; --Bom Components count.
775 END LOOP; --bom_comp_CUR loop
776
777 /* If the number of records are more than equal to 10,000 */
778 IF l_submit_flag = TRUE THEN
779
780 CALL_PROCESS_BOM (p_debug => P_debug,
781 p_output_dir => P_output_dir,
782 p_debug_filename => P_debug_filename,
783 p_bom_header_tbl => l_bom_header_tbl,
784 p_bom_component_tbl => l_bom_comp_tbl,
785 p_bom_ref_designator_tbl => l_bom_ref_desig_tbl,
786 p_bom_sub_component_tbl => l_bom_sub_comp_tbl,
787 p_bom_comp_ops_tbl => l_bom_comp_oper_tbl,
788 X_bom_header_tbl => X_bom_header_tbl,
789 X_bom_revisions_tbl => X_bom_revisions_tbl,
790 X_bom_comp_tbl => X_bom_comp_tbl,
791 X_bom_ref_desig_tbl => X_bom_ref_desig_tbl,
792 X_bom_sub_comp_tbl => X_bom_sub_comp_tbl,
793 X_bom_comp_oper_tbl => X_bom_comp_oper_tbl,
794 x_return_status => X_return_status,
795 x_g_msg_data => X_G_msg_data,
796 x_msg_count => X_msg_count);
797
798 l_bom_header_tbl.DELETE;
799 l_bom_comp_tbl.DELETE;
800 l_bom_ref_desig_tbl.DELETE;
801 l_bom_sub_comp_tbl.DELETE;
802 l_bom_comp_oper_tbl.DELETE;
803
804 i := 1;
805 j := 1;
806 k := 1;
807 l := 1;
808 m := 1;
809 l_threshold_count := 1;
810 l_submit_flag := FALSE;
811
812 ELSE
813 i := i + 1; -- If the threshold is not yet reached, continue adding more headers and dependent entities.
814 END IF; --END l_submit_flag condition
815
816 END LOOP; --bom_header_rec loop
817
818 /* If there are any Headers pending processing after the Header loop is closed, process those records here */
819 IF l_submit_flag = FALSE and l_threshold_count > 1 THEN
820
821 CALL_PROCESS_BOM (p_debug => P_debug,
822 p_output_dir => P_output_dir,
823 p_debug_filename => P_debug_filename,
824 p_bom_header_tbl => l_bom_header_tbl,
825 p_bom_component_tbl => l_bom_comp_tbl,
826 p_bom_ref_designator_tbl => l_bom_ref_desig_tbl,
827 p_bom_sub_component_tbl => l_bom_sub_comp_tbl,
828 p_bom_comp_ops_tbl => l_bom_comp_oper_tbl,
829 X_bom_header_tbl => X_bom_header_tbl,
830 X_bom_revisions_tbl => X_bom_revisions_tbl,
831 X_bom_comp_tbl => X_bom_comp_tbl,
832 X_bom_ref_desig_tbl => X_bom_ref_desig_tbl,
833 X_bom_sub_comp_tbl => X_bom_sub_comp_tbl,
834 X_bom_comp_oper_tbl => X_bom_comp_oper_tbl,
835 x_return_status => X_return_status,
836 x_g_msg_data => X_G_msg_data,
837 x_msg_count => X_msg_count);
838 l_bom_header_tbl.DELETE;
839 l_bom_comp_tbl.DELETE;
840 l_bom_ref_desig_tbl.DELETE;
841 l_bom_sub_comp_tbl.DELETE;
842 l_bom_comp_oper_tbl.DELETE;
843
844
845 END IF; --END l_submit_flag condition
846
847 IF g_fnd_debug = 'Y' AND
851 , G_MODULE_PREFIX || g_api_name
848 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
849 THEN
850 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
852 , 'End of method Import_BOM');
853 END IF;
854
855 IF g_fnd_debug = 'Y' AND
856 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
857 THEN
858 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
859 , G_MODULE_PREFIX || g_api_name || '.end'
860 , NULL);
861 END IF;
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
866 THEN
867 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
868 , G_MODULE_PREFIX || g_api_name
869 , SQLCODE||' : '||SQLERRM);
870 END IF;
871 X_G_msg_data := SQLCODE||' : '||SQLERRM;
872 X_return_status := 'E';
873 --debug_log('UEXP Error:'|| X_G_msg_data);
874
875
876 END Import_Bom;
877
878
879
880 PROCEDURE CALL_PROCESS_BOM(P_debug IN VARCHAR2,
881 P_output_dir IN VARCHAR2,
882 P_debug_filename IN VARCHAR2,
883 p_bom_header_tbl IN BOM_BO_PUB.BOM_HEADER_TBL_TYPE ,
884 p_bom_component_tbl IN BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
885 p_bom_ref_designator_tbl IN BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
886 p_bom_sub_component_tbl IN BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
887 p_bom_comp_ops_tbl IN BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
888 X_bom_header_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_HEADER_TBL_TYPE ,
889 X_bom_revisions_tbl IN OUT NOCOPY BOM_BO_PUB.Bom_Revision_Tbl_Type,
890 X_bom_comp_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
891 X_bom_ref_desig_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
892 X_bom_sub_comp_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
893 X_bom_comp_oper_tbl IN OUT NOCOPY BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
894 X_return_status IN OUT NOCOPY VARCHAR2, --myerrams, Bug: 5592181
895 X_G_msg_data OUT NOCOPY LONG,
896 X_msg_count OUT NOCOPY NUMBER
897 ) IS
898
899 /* Following variables are used for logging messages */
900 l_message VARCHAR2(2000) := NULL;
901 l_entity VARCHAR2(3) := NULL;
902 l_msg_index NUMBER;
903 l_message_type VARCHAR2(1);
904
905 BEGIN
906 IF g_fnd_debug = 'Y' AND
907 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
908 THEN
909 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
910 , G_MODULE_PREFIX || g_api_name
911 , 'Before Calling BOM_BO_PUB.Process_Bom()');
912 END IF;
913
914 BOM_GLOBALS.Set_Caller_Type('MIGRATION');
915 BOM_BO_PUB.Process_Bom(p_bom_header_tbl => p_bom_header_tbl,
916 p_bom_component_tbl => p_bom_component_tbl,
917 p_bom_ref_designator_tbl => p_bom_ref_designator_tbl ,
918 p_bom_sub_component_tbl => p_bom_sub_component_tbl ,
919 p_bom_comp_ops_tbl => p_bom_comp_ops_tbl,
920 x_bom_header_tbl => X_bom_header_tbl,
921 x_bom_revision_tbl => X_bom_revisions_tbl,
922 x_bom_component_tbl => X_bom_comp_tbl,
923 x_bom_ref_designator_tbl => X_bom_ref_desig_tbl,
924 x_bom_sub_component_tbl => X_bom_sub_comp_tbl,
925 x_bom_comp_ops_tbl => X_bom_comp_oper_tbl,
926 x_return_status => g_X_return_status, --myerrams, Bug: 5592181
927 x_msg_count => X_msg_count,
928 p_debug => P_debug,
929 p_output_dir => P_output_dir,
930 p_debug_filename => P_debug_filename);
931 --myerrams, Bug:5654718
932 X_bom_header_tbl.DELETE;
933 X_bom_revisions_tbl.DELETE;
934 X_bom_comp_tbl.DELETE;
935 X_bom_ref_desig_tbl.DELETE;
936 X_bom_sub_comp_tbl.DELETE;
937 X_bom_comp_oper_tbl.DELETE;
938
939 commit;
940
941 IF g_fnd_debug = 'Y' AND
942 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
943 THEN
944 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
945 , G_MODULE_PREFIX || g_api_name
946 , 'Current Call Return Status of BOM_BO_PUB.Process_Bom: g_X_return_status:'|| g_X_return_status);
947 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
948 , G_MODULE_PREFIX || g_api_name
949 , 'Previous Call Return Status of BOM_BO_PUB.Process_Bom: X_return_status:'|| X_return_status);
950 END IF;
951
952 IF (g_X_return_status <> 'S' and g_status_set = FALSE) THEN
953 X_return_status := g_X_return_status;
954 g_status_set := TRUE;
955 END IF;
956
957 IF (g_X_return_status = 'U' and X_return_status <> 'U') THEN
958 X_return_status := g_X_return_status;
959 END IF;
960
961 FOR i IN 1..X_msg_count LOOP
962 BEGIN
963
964 ERROR_HANDLER.Get_Message(x_entity_index => l_msg_index,
965 x_entity_id => l_entity,
966 x_message_text => l_message,
967 x_message_type => l_message_type);
968 X_G_msg_data := TO_CHAR(l_msg_index) || ': '||l_entity ||': '|| l_message_type ||': '||l_message;
969
970 IF g_fnd_debug = 'Y' AND
971 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
972 THEN
973 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
974 , G_MODULE_PREFIX || g_api_name
975 , 'Error messages from BOM_BO_PUB.Process_Bom:'|| X_G_msg_data);
976 END IF;
977
978 END;
979 END LOOP;
980
981 IF g_fnd_debug = 'Y' AND
982 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
983 THEN
984 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
985 , G_MODULE_PREFIX || g_api_name
986 , 'After Calling BOM_BO_PUB.Process_Bom()');
987 END IF;
988 /* Reset all the PL/SQL tables and counters*/
989
992 END BOM_BOM_COPYORG_IMP;
990 END CALL_PROCESS_BOM;
991