[Home] [Help]
PACKAGE BODY: APPS.EAM_EXPLODE_ACTIVITY_PVT
Source
1 PACKAGE BODY EAM_EXPLODE_ACTIVITY_PVT AS
2 /* $Header: EAMVEXAB.pls 120.4 2006/09/08 11:44:12 cboppana noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVEXAB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_EXPLODE_ACTIVITY_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Kenichi Nagumo Initial Creation
21 ***************************************************************************/
22
23
24 PROCEDURE EXPLODE_ACTIVITY
25 ( p_validation_level IN NUMBER
26 , p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type
27 , p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type
28 , p_eam_op_network_tbl IN EAM_PROCESS_WO_PUB.eam_op_network_tbl_type
29 , p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type
30 , p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
31 , p_eam_sub_res_tbl IN EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type
32 , p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
33 , p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type
34 , x_eam_wo_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type
35 , x_eam_op_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_tbl_type
36 , x_eam_op_network_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_network_tbl_type
37 , x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type
38 , x_eam_res_inst_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
39 , x_eam_sub_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type
40 , x_eam_res_usage_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
41 , x_eam_mat_req_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type
42 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
43 , x_return_status OUT NOCOPY VARCHAR2
44 )
45 IS
46 --Bug#3342391 : Added a local variable
47 l_common_routing_seq_id NUMBER ;
48 l_routing_available CHAR := 'N';
49 l_wip_entity_id NUMBER;
50 l_organization_id NUMBER;
51
52 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type := p_eam_wo_rec;
53 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type := p_eam_op_tbl;
54 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type := p_eam_op_network_tbl;
55 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type := p_eam_res_tbl;
56 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type := p_eam_res_inst_tbl;
57 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type := p_eam_sub_res_tbl;
58 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type := p_eam_res_usage_tbl;
59 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type := p_eam_mat_req_tbl;
60 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
61
62 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type := p_eam_wo_rec;
63 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type := p_eam_op_tbl;
64 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type := p_eam_op_network_tbl;
65 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type := p_eam_res_tbl;
66 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type := p_eam_res_inst_tbl;
67 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type := p_eam_sub_res_tbl;
68 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type := p_eam_res_usage_tbl;
69 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type := p_eam_mat_req_tbl;
70 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
71
72 /* Error Handling Variables */
73 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
74 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
75 l_out_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
76 l_other_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
77 l_other_message VARCHAR2(2000);
78 l_err_text VARCHAR2(2000);
79 l_error_code NUMBER;
80
81 /* Others */
82 l_return_status VARCHAR2(1) ;
83 l_bo_return_status VARCHAR2(1) ;
84 l_process_children BOOLEAN := TRUE ;
85 l_valid_transaction BOOLEAN := TRUE ;
86 l_bill_sequence_id NUMBER;
87
88 -- Fix for Bug 3686343
89 p_rout_rev_datetime DATE := NVL(p_eam_wo_rec.routing_revision_date,p_eam_wo_rec.scheduled_start_date);
90 p_bom_rev_datetime DATE := NVL(p_eam_wo_rec.bom_revision_date,p_eam_wo_rec.scheduled_start_date);
91 -- Fix for Bug 3686343
92
93 j NUMBER := l_eam_op_tbl.COUNT;
94 k NUMBER := l_eam_res_tbl.COUNT;
95 m NUMBER := l_eam_op_network_tbl.COUNT;
96 n NUMBER := l_eam_mat_req_tbl.COUNT;
97
98 l_count NUMBER := 0;
99 l_count1 NUMBER := 0;
100
101
102 l_group_id NUMBER;
103
104 l_def_return_status VARCHAR2(1);
105 l_def_msg_count NUMBER;
106 l_def_msg_data VARCHAR2(1000);
107
108
109 CURSOR ActivityOP IS
110 SELECT
111 p_eam_wo_rec.batch_id batch_id
112 , p_eam_wo_rec.header_id header_id
113 , p_eam_wo_rec.wip_entity_id WIP_ENTITY_ID
114 , p_eam_wo_rec.organization_id ORGANIZATION_ID
115 , BOS.OPERATION_SEQUENCE_ID OPERATION_SEQUENCE_ID
116 , BOS.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
117 , BOS.STANDARD_OPERATION_ID STANDARD_OPERATION_ID
118 , BOS.DEPARTMENT_ID DEPARTMENT_ID
119 , BOS.OPERATION_DESCRIPTION DESCRIPTION
120 , BOS.LONG_DESCRIPTION LONG_DESCRIPTION
121 , 0 MINIMUM_TRANSFER_QUANTITY
122 , 1 COUNT_POINT_TYPE
123 , 1 BACKFLUSH_FLAG
124 , BOS.SHUTDOWN_TYPE SHUTDOWN_TYPE
125 , nvl(p_eam_wo_rec.scheduled_start_date,SYSDATE) START_DATE
126 , nvl(p_eam_wo_rec.scheduled_completion_date,SYSDATE) COMPLETION_DATE
127 , BOS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
128 , BOS.ATTRIBUTE1 ATTRIBUTE1
129 , BOS.ATTRIBUTE2 ATTRIBUTE2
130 , BOS.ATTRIBUTE3 ATTRIBUTE3
131 , BOS.ATTRIBUTE4 ATTRIBUTE4
132 , BOS.ATTRIBUTE5 ATTRIBUTE5
133 , BOS.ATTRIBUTE6 ATTRIBUTE6
134 , BOS.ATTRIBUTE7 ATTRIBUTE7
135 , BOS.ATTRIBUTE8 ATTRIBUTE8
136 , BOS.ATTRIBUTE9 ATTRIBUTE9
137 , BOS.ATTRIBUTE10 ATTRIBUTE10
138 , BOS.ATTRIBUTE11 ATTRIBUTE11
139 , BOS.ATTRIBUTE12 ATTRIBUTE12
140 , BOS.ATTRIBUTE13 ATTRIBUTE13
141 , BOS.ATTRIBUTE14 ATTRIBUTE14
142 , BOS.ATTRIBUTE15 ATTRIBUTE15
143 , null RETURN_STATUS
144 , 1 TRANSACTION_TYPE
145 -- Bug 3262984 : For adding attachments from the activity to the workorder on explosion
146 -- one of the entities requires common_routing_sequence_id as a primary key.Hence being added to the query.
147 , BORT.COMMON_ROUTING_SEQUENCE_ID COMMON_ROUTING_SEQUENCE_ID
148 , BOS.X_COORDINATE
149 , BOS.Y_COORDINATE
150 FROM BOM_OPERATION_SEQUENCES BOS
151 , BOM_OPERATIONAL_ROUTINGS BORT
152 WHERE BORT.assembly_item_id = p_eam_wo_rec.asset_activity_id
153 AND BORT.organization_id = p_eam_wo_rec.organization_id
154 AND BOS.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID -- agaurav- Changed ROUTING_SEQUENCE_ID to COMMON_ROUTING_SEQUENCE_ID
155 AND NVL(BOS.OPERATION_TYPE, 1) = 1
156 AND BOS.EFFECTIVITY_DATE <= p_rout_rev_datetime
157 AND NVL(BOS.DISABLE_DATE, p_rout_rev_datetime + 2) >= p_rout_rev_datetime
158 AND BOS.IMPLEMENTATION_DATE IS NOT NULL
159 AND ( ( p_eam_wo_rec.alternate_routing_designator is not null
160 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') = nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
161 OR ( p_eam_wo_rec.alternate_routing_designator is null
162 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') = nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
163 ) ;
164 -- agaurav - Added the check for alternate_routing_designator so that the
165 -- - operations are copied either from the primary or alternate routing.
166
167
168
169
170 /* Resource information from BOM to WIP */
171
172
173 CURSOR ActivityRES IS
174 SELECT
175 p_eam_wo_rec.batch_id batch_id
176 , p_eam_wo_rec.header_id header_id
177 , p_eam_wo_rec.wip_entity_id WIP_ENTITY_ID
178 , p_eam_wo_rec.organization_id ORGANIZATION_ID
179 , BOS.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
180 , BOS.DEPARTMENT_ID DEPARTMENT_ID
181 , BOR.RESOURCE_SEQ_NUM RESOURCE_SEQ_NUM
182 , BOR.RESOURCE_ID RESOURCE_ID
183 , BR.UNIT_OF_MEASURE UOM_CODE
184 , BOR.BASIS_TYPE BASIS_TYPE
185 , BOR.USAGE_RATE_OR_AMOUNT USAGE_RATE_OR_AMOUNT
186 , BOR.ACTIVITY_ID ACTIVITY_ID
187 , BOR.SCHEDULE_FLAG SCHEDULED_FLAG
188 , BOR.ASSIGNED_UNITS ASSIGNED_UNITS
189 --added for bug 4363800 decode statement to select 2,3 as eAM supports only 2 or 3
190 , DECODE(BOR.AUTOCHARGE_TYPE,1,2,4,3,3,3,2) AUTOCHARGE_TYPE -- Fix for Bug 3823415
191 , BOR.STANDARD_RATE_FLAG STANDARD_RATE_FLAG
192 , 0 APPLIED_RESOURCE_UNITS
193 , 0 APPLIED_RESOURCE_VALUE
194 , nvl(p_eam_wo_rec.scheduled_start_date,SYSDATE) START_DATE
195 , nvl(p_eam_wo_rec.scheduled_completion_date,SYSDATE) COMPLETION_DATE
196 , 0 REPLACEMENT_GROUP_NUM
197 , BOR.SCHEDULE_SEQ_NUM SCHEDULE_SEQ_NUM
198 , BOR.SUBSTITUTE_GROUP_NUM SUBSTITUTE_GROUP_NUM
199 , BOR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
200 , BOR.ATTRIBUTE1 ATTRIBUTE1
201 , BOR.ATTRIBUTE2 ATTRIBUTE2
202 , BOR.ATTRIBUTE3 ATTRIBUTE3
203 , BOR.ATTRIBUTE4 ATTRIBUTE4
204 , BOR.ATTRIBUTE5 ATTRIBUTE5
205 , BOR.ATTRIBUTE6 ATTRIBUTE6
206 , BOR.ATTRIBUTE7 ATTRIBUTE7
207 , BOR.ATTRIBUTE8 ATTRIBUTE8
208 , BOR.ATTRIBUTE9 ATTRIBUTE9
209 , BOR.ATTRIBUTE10 ATTRIBUTE10
210 , BOR.ATTRIBUTE11 ATTRIBUTE11
211 , BOR.ATTRIBUTE12 ATTRIBUTE12
212 , BOR.ATTRIBUTE13 ATTRIBUTE13
213 , BOR.ATTRIBUTE14 ATTRIBUTE14
214 , BOR.ATTRIBUTE15 ATTRIBUTE15
215 , null RETURN_STATUS
216 , 1 TRANSACTION_TYPE
217 FROM BOM_RESOURCES BR
218 , BOM_OPERATION_RESOURCES BOR
219 , BOM_OPERATION_SEQUENCES BOS
220 , BOM_OPERATIONAL_ROUTINGS BORT
221 WHERE BORT.assembly_item_id = p_eam_wo_rec.asset_activity_id
222 AND BORT.organization_id = p_eam_wo_rec.organization_id
223 AND BOS.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID -- agaurav- Changed ROUTING_SEQUENCE_ID to COMMON_ROUTING_SEQUENCE_ID
224 AND BOS.OPERATION_SEQUENCE_ID = BOR.OPERATION_SEQUENCE_ID
225 AND BOS.EFFECTIVITY_DATE <= p_rout_rev_datetime
226 AND NVL(BOS.DISABLE_DATE, p_rout_rev_datetime + 2) >= p_rout_rev_datetime
227 AND BOR.RESOURCE_ID = BR.RESOURCE_ID
228 AND BR.ORGANIZATION_ID = p_eam_wo_rec.organization_id
229 AND (BOR.ACD_TYPE IS NULL OR BOR.ACD_TYPE <> 3)
230 AND ( ( p_eam_wo_rec.alternate_routing_designator is not null
231 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') = nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
232 OR ( p_eam_wo_rec.alternate_routing_designator is null
233 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') = nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
234 ) ;
235 -- agaurav - Added the check for alternate_routing_designator so that the
236 -- - operations are copied either from the primary or alternate routing.
237
238
239
240 /* NETWORKS from activity BOM to WIP */
241 CURSOR ActivityOPN IS
242 SELECT
243 p_eam_wo_rec.batch_id batch_id
244 , p_eam_wo_rec.header_id header_id
245 , p_eam_wo_rec.wip_entity_id WIP_ENTITY_ID
246 , p_eam_wo_rec.organization_id ORGANIZATION_ID
247 , BOS_FROM.OPERATION_SEQ_NUM PRIOR_OPERATION
248 , BOS_TO.OPERATION_SEQ_NUM NEXT_OPERATION
249 , BON.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
250 , BON.ATTRIBUTE1 ATTRIBUTE1
251 , BON.ATTRIBUTE2 ATTRIBUTE2
252 , BON.ATTRIBUTE3 ATTRIBUTE3
253 , BON.ATTRIBUTE4 ATTRIBUTE4
254 , BON.ATTRIBUTE5 ATTRIBUTE5
255 , BON.ATTRIBUTE6 ATTRIBUTE6
256 , BON.ATTRIBUTE7 ATTRIBUTE7
257 , BON.ATTRIBUTE8 ATTRIBUTE8
258 , BON.ATTRIBUTE9 ATTRIBUTE9
259 , BON.ATTRIBUTE10 ATTRIBUTE10
260 , BON.ATTRIBUTE11 ATTRIBUTE11
261 , BON.ATTRIBUTE12 ATTRIBUTE12
262 , BON.ATTRIBUTE13 ATTRIBUTE13
263 , BON.ATTRIBUTE14 ATTRIBUTE14
264 , BON.ATTRIBUTE15 ATTRIBUTE15
265 , null RETURN_STATUS
266 , 1 TRANSACTION_TYPE
267 FROM BOM_OPERATION_NETWORKS BON
268 , BOM_OPERATION_SEQUENCES BOS_FROM
269 , BOM_OPERATION_SEQUENCES BOS_TO
270 , BOM_OPERATIONAL_ROUTINGS BORT
271 WHERE BORT.assembly_item_id = p_eam_wo_rec.asset_activity_id
272 AND BORT.organization_id = p_eam_wo_rec.organization_id
273 AND BOS_FROM.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID -- agaurav- Changed ROUTING_SEQUENCE_ID to COMMON_ROUTING_SEQUENCE_ID
274 AND BOS_TO.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID -- agaurav- Changed ROUTING_SEQUENCE_ID to COMMON_ROUTING_SEQUENCE_ID
275 AND BOS_FROM.EFFECTIVITY_DATE <= p_rout_rev_datetime
276 AND NVL(BOS_FROM.DISABLE_DATE, p_rout_rev_datetime + 2) >= p_rout_rev_datetime
277 AND BOS_TO.EFFECTIVITY_DATE <= p_rout_rev_datetime
278 AND NVL(BOS_TO.DISABLE_DATE, p_rout_rev_datetime + 2) >= p_rout_rev_datetime
279 AND BON.FROM_OP_SEQ_ID = BOS_FROM.OPERATION_SEQUENCE_ID
280 AND BON.TO_OP_SEQ_ID = BOS_TO.OPERATION_SEQUENCE_ID
281 AND NVL(BON.EFFECTIVITY_DATE, SYSDATE-2) < SYSDATE
282 AND NVL(BON.DISABLE_DATE, SYSDATE+2) > SYSDATE
283 -- agaurav - Added the check for alternate_routing_designator so that the
284 -- - operations are copied either from the primary or alternate routing.
285 AND ( ( p_eam_wo_rec.alternate_routing_designator is not null
286 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') = nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
287 OR ( p_eam_wo_rec.alternate_routing_designator is null
288 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') = nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
289 )
290 -- Bugfix 3556118 : There should not be any operation as part of the
291 -- network that is outside it's effectivity dates. If there is even
292 -- one such operation, then don't create any networks.
293 AND NOT EXISTS (
294 select 1
295 FROM BOM_OPERATION_NETWORKS BON
296 , BOM_OPERATION_SEQUENCES BOS_FROM
297 , BOM_OPERATION_SEQUENCES BOS_TO
298 , BOM_OPERATIONAL_ROUTINGS BORT
299 WHERE BORT.assembly_item_id = p_eam_wo_rec.asset_activity_id
300 AND BORT.organization_id = p_eam_wo_rec.organization_id
301 AND BOS_FROM.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID
302 -- agaurav- Changed ROUTING_SEQUENCE_ID to COMMON_ROUTING_SEQUENCE_ID
303 AND BOS_TO.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID
304 -- agaurav- Changed ROUTING_SEQUENCE_ID to COMMON_ROUTING_SEQUENCE_ID
305 AND ( p_rout_rev_datetime NOT BETWEEN BOS_FROM.EFFECTIVITY_DATE AND
306 NVL(BOS_FROM.DISABLE_DATE, p_rout_rev_datetime)
307 OR p_rout_rev_datetime NOT BETWEEN BOS_TO.EFFECTIVITY_DATE AND
308 NVL(BOS_TO.DISABLE_DATE, p_rout_rev_datetime)
309 )
310 AND BON.FROM_OP_SEQ_ID = BOS_FROM.OPERATION_SEQUENCE_ID
311 AND BON.TO_OP_SEQ_ID = BOS_TO.OPERATION_SEQUENCE_ID
312 AND NVL(BON.EFFECTIVITY_DATE, SYSDATE-2) < SYSDATE
313 AND NVL(BON.DISABLE_DATE, SYSDATE+2) > SYSDATE
314 -- agaurav - Added the check for alternate_routing_designator so that the
315 -- - operations are copied either from the primary or alternate routing.
316 AND ( ( p_eam_wo_rec.alternate_routing_designator is not null
317 AND
318 nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') =
319 nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
320 OR ( p_eam_wo_rec.alternate_routing_designator is null
321 AND
322 nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') =
323 nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator') )
324 )
325 );
326
327
328
329 cursor ActivityREQ(v_grpID NUMBER) is
330 -- This is a union of 2 queries. One for which the material operation
331 -- seq num is 1 and the second for the case when op seq num <> 1
332 (SELECT
333 p_eam_wo_rec.batch_id batch_id
334 , p_eam_wo_rec.header_id header_id
335 , null row_id
336 , p_eam_wo_rec.wip_entity_id WIP_ENTITY_ID
337 , p_eam_wo_rec.organization_id ORGANIZATION_ID
338 , BE.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
339 , BE.COMPONENT_ITEM_ID INVENTORY_ITEM_ID
340 , BE.COMPONENT_QUANTITY QUANTITY_PER_ASSEMBLY
341 , to_number(null) DEPARTMENT_ID
342 , BIC.WIP_SUPPLY_TYPE WIP_SUPPLY_TYPE
343 , nvl(p_eam_wo_rec.scheduled_start_date,SYSDATE) DATE_REQUIRED
344 , extended_quantity REQUIRED_QUANTITY
345 --fix for 3550864.
346 , null REQUESTED_QUANTITY
347 --fix for 3571180
348 ,null RELEASED_QUANTITY
349 , 0 QUANTITY_ISSUED
350 , NVL(BIC.SUPPLY_SUBINVENTORY, MSI.WIP_SUPPLY_SUBINVENTORY) SUPPLY_SUBINVENTORY
351 , DECODE(BIC.SUPPLY_SUBINVENTORY, NULL, MSI.WIP_SUPPLY_LOCATOR_ID,BIC.SUPPLY_LOCATOR_ID) SUPPLY_LOCATOR_ID
352 , wip_constants.SUPPLY_NET MRP_NET_FLAG
353 , 0 MPS_REQUIRED_QUANTITY
354 , null /*bugfix#5059638 nvl(p_eam_wo_rec.scheduled_start_date,SYSDATE)*/ MPS_DATE_REQUIRED
355 , BIC.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID
356 , BIC.COMPONENT_REMARKS COMMENTS
357 , BIC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
358 , BIC.ATTRIBUTE1 ATTRIBUTE1
359 , BIC.ATTRIBUTE2 ATTRIBUTE2
360 , BIC.ATTRIBUTE3 ATTRIBUTE3
361 , BIC.ATTRIBUTE4 ATTRIBUTE4
362 , BIC.ATTRIBUTE5 ATTRIBUTE5
363 , BIC.ATTRIBUTE6 ATTRIBUTE6
364 , BIC.ATTRIBUTE7 ATTRIBUTE7
365 , BIC.ATTRIBUTE8 ATTRIBUTE8
366 , BIC.ATTRIBUTE9 ATTRIBUTE9
367 , BIC.ATTRIBUTE10 ATTRIBUTE10
368 , BIC.ATTRIBUTE11 ATTRIBUTE11
369 , BIC.ATTRIBUTE12 ATTRIBUTE12
370 , BIC.ATTRIBUTE13 ATTRIBUTE13
371 , BIC.ATTRIBUTE14 ATTRIBUTE14
372 , BIC.ATTRIBUTE15 ATTRIBUTE15
373 , BE.AUTO_REQUEST_MATERIAL AUTO_REQUEST_MATERIAL
374 , BIC.SUGGESTED_VENDOR_NAME SUGGESTED_VENDOR_NAME
375 , BIC.VENDOR_ID VENDOR_ID
376 , BIC.UNIT_PRICE UNIT_PRICE
377 , null REQUEST_ID
378 , null PROGRAM_APPLICATION_ID
379 , null PROGRAM_ID
380 , null PROGRAM_UPDATE_DATE
381 , null RETURN_STATUS
382 , 1 TRANSACTION_TYPE
383 FROM BOM_EXPLOSION_TEMP BE
384 , BOM_INVENTORY_COMPONENTS BIC
385 , MTL_SYSTEM_ITEMS MSI
386 WHERE BE.GROUP_ID = v_grpID
387 AND BE.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
388 AND BE.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
389 AND BE.COMPONENT_ITEM_ID <> p_eam_wo_rec.asset_activity_id --EXCLUDE ASSY IF IT IS IN THE TABLE
390 AND MSI.ORGANIZATION_ID = p_eam_wo_rec.organization_id
391 AND BE.OPERATION_SEQ_NUM = 1)
392
393 UNION
394
395 (SELECT
396 p_eam_wo_rec.batch_id batch_id
397 , p_eam_wo_rec.header_id header_id
398 , null row_id
399 , p_eam_wo_rec.wip_entity_id WIP_ENTITY_ID
400 , p_eam_wo_rec.organization_id ORGANIZATION_ID
401 , BE.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
402 , BE.COMPONENT_ITEM_ID INVENTORY_ITEM_ID
403 , BE.COMPONENT_QUANTITY QUANTITY_PER_ASSEMBLY
404 , BOS.DEPARTMENT_ID DEPARTMENT_ID
405 , BIC.WIP_SUPPLY_TYPE WIP_SUPPLY_TYPE
406 , nvl(p_eam_wo_rec.scheduled_start_date,SYSDATE) DATE_REQUIRED
407 , extended_quantity REQUIRED_QUANTITY
408 --fix for 3550864.
409 , null REQUESTED_QUANTITY
410 --fix for 3572280
411 , null RELEASED_QUANTITY
412 , 0 QUANTITY_ISSUED
413 , NVL(BIC.SUPPLY_SUBINVENTORY, MSI.WIP_SUPPLY_SUBINVENTORY) SUPPLY_SUBINVENTORY
414 , DECODE(BIC.SUPPLY_SUBINVENTORY, NULL, MSI.WIP_SUPPLY_LOCATOR_ID,BIC.SUPPLY_LOCATOR_ID) SUPPLY_LOCATOR_ID
415 , wip_constants.SUPPLY_NET MRP_NET_FLAG
416 , 0 MPS_REQUIRED_QUANTITY
417 , null /*bugfix#5059638 nvl(p_eam_wo_rec.scheduled_start_date,SYSDATE)*/ MPS_DATE_REQUIRED
418 , BIC.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID
419 , BIC.COMPONENT_REMARKS COMMENTS
420 , BIC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
421 , BIC.ATTRIBUTE1 ATTRIBUTE1
422 , BIC.ATTRIBUTE2 ATTRIBUTE2
423 , BIC.ATTRIBUTE3 ATTRIBUTE3
424 , BIC.ATTRIBUTE4 ATTRIBUTE4
425 , BIC.ATTRIBUTE5 ATTRIBUTE5
426 , BIC.ATTRIBUTE6 ATTRIBUTE6
427 , BIC.ATTRIBUTE7 ATTRIBUTE7
428 , BIC.ATTRIBUTE8 ATTRIBUTE8
429 , BIC.ATTRIBUTE9 ATTRIBUTE9
430 , BIC.ATTRIBUTE10 ATTRIBUTE10
431 , BIC.ATTRIBUTE11 ATTRIBUTE11
432 , BIC.ATTRIBUTE12 ATTRIBUTE12
433 , BIC.ATTRIBUTE13 ATTRIBUTE13
434 , BIC.ATTRIBUTE14 ATTRIBUTE14
435 , BIC.ATTRIBUTE15 ATTRIBUTE15
436 , BE.AUTO_REQUEST_MATERIAL AUTO_REQUEST_MATERIAL
437 , BIC.SUGGESTED_VENDOR_NAME SUGGESTED_VENDOR_NAME
438 , BIC.VENDOR_ID VENDOR_ID
439 , BIC.UNIT_PRICE UNIT_PRICE
440 , null REQUEST_ID
441 , null PROGRAM_APPLICATION_ID
442 , null PROGRAM_ID
443 , null PROGRAM_UPDATE_DATE
444 , null RETURN_STATUS
445 , 1 TRANSACTION_TYPE
446 FROM BOM_EXPLOSION_TEMP BE
447 , BOM_INVENTORY_COMPONENTS BIC
448 , BOM_OPERATIONAL_ROUTINGS BORT
449 , BOM_OPERATION_SEQUENCES BOS
450 , MTL_SYSTEM_ITEMS MSI
451 WHERE BE.GROUP_ID = v_grpID
452 AND BE.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
453 AND BE.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
454 AND BE.COMPONENT_ITEM_ID <> p_eam_wo_rec.asset_activity_id --EXCLUDE ASSY IF IT IS IN THE TABLE
455 AND MSI.ORGANIZATION_ID = p_eam_wo_rec.organization_id
456 AND BORT.assembly_item_id = p_eam_wo_rec.asset_activity_id
457 AND BORT.organization_id = p_eam_wo_rec.organization_id
458 AND BOS.ROUTING_SEQUENCE_ID = BORT.COMMON_ROUTING_SEQUENCE_ID
459 AND BOS.EFFECTIVITY_DATE <= p_bom_rev_datetime
460 AND NVL(BOS.DISABLE_DATE, p_bom_rev_datetime + 2) >= p_bom_rev_datetime
461 AND BE.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
462 AND BOS.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
463 AND NVL(BOS.OPERATION_TYPE, 1) = 1
464 AND BOS.EFFECTIVITY_DATE <= p_bom_rev_datetime
465 AND NVL(BOS.DISABLE_DATE, p_bom_rev_datetime + 2) >= p_bom_rev_datetime
466 AND BOS.IMPLEMENTATION_DATE IS NOT NULL
467 AND (( p_eam_wo_rec.alternate_routing_designator is not null
468 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') =
469 nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator')
470 )
471 OR
472 ( p_eam_wo_rec.alternate_routing_designator is null
473 AND nvl(BORT.ALTERNATE_ROUTING_DESIGNATOR,'null_routing_designator') =
474 nvl(p_eam_wo_rec.alternate_routing_designator,'null_routing_designator')
475 )
476 )
477 AND BE.OPERATION_SEQ_NUM <> 1);
478
479 matreqrec ActivityREQ%ROWTYPE;
480
481 BEGIN
482
483
484 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Starting Activity Explosion'); end if;
485
486
487 -- **************************** OPERATIONS ******************
488 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Exploding Operations'); end if;
489 FOR oprec IN ActivityOP LOOP
490
491 j:=j+1; --counter for operations
492 l_eam_op_tbl(j).BATCH_ID :=oprec.BATCH_ID;
493 l_eam_op_tbl(j).HEADER_ID :=oprec.HEADER_ID;
494 l_eam_op_tbl(j).WIP_ENTITY_ID :=oprec.WIP_ENTITY_ID;
495 l_eam_op_tbl(j).ORGANIZATION_ID :=oprec.ORGANIZATION_ID;
496 l_eam_op_tbl(j).OPERATION_SEQUENCE_ID :=oprec.OPERATION_SEQUENCE_ID;
497 l_eam_op_tbl(j).OPERATION_SEQ_NUM :=oprec.OPERATION_SEQ_NUM;
498 l_eam_op_tbl(j).STANDARD_OPERATION_ID :=oprec.STANDARD_OPERATION_ID;
499 l_eam_op_tbl(j).DEPARTMENT_ID :=oprec.DEPARTMENT_ID;
500 l_eam_op_tbl(j).DESCRIPTION :=oprec.DESCRIPTION;
501 l_eam_op_tbl(j).LONG_DESCRIPTION :=oprec.LONG_DESCRIPTION;
502 l_eam_op_tbl(j).MINIMUM_TRANSFER_QUANTITY :=oprec.MINIMUM_TRANSFER_QUANTITY;
503 l_eam_op_tbl(j).COUNT_POINT_TYPE :=oprec.COUNT_POINT_TYPE;
504 l_eam_op_tbl(j).BACKFLUSH_FLAG :=oprec.BACKFLUSH_FLAG;
505 l_eam_op_tbl(j).SHUTDOWN_TYPE :=oprec.SHUTDOWN_TYPE;
506 l_eam_op_tbl(j).START_DATE :=oprec.START_DATE;
507 l_eam_op_tbl(j).COMPLETION_DATE :=oprec.COMPLETION_DATE;
508 l_eam_op_tbl(j).Attribute_Category :=oprec.Attribute_Category;
509 l_eam_op_tbl(j).Attribute1 :=oprec.Attribute1;
510 l_eam_op_tbl(j).Attribute2 :=oprec.Attribute2;
511 l_eam_op_tbl(j).Attribute3 :=oprec.Attribute3;
512 l_eam_op_tbl(j).Attribute4 :=oprec.Attribute4;
513 l_eam_op_tbl(j).Attribute5 :=oprec.Attribute5;
514 l_eam_op_tbl(j).Attribute6 :=oprec.Attribute6;
515 l_eam_op_tbl(j).Attribute7 :=oprec.Attribute7;
516 l_eam_op_tbl(j).Attribute8 :=oprec.Attribute8;
517 l_eam_op_tbl(j).Attribute9 :=oprec.Attribute9;
518 l_eam_op_tbl(j).Attribute10 :=oprec.Attribute10;
519 l_eam_op_tbl(j).Attribute11 :=oprec.Attribute11;
520 l_eam_op_tbl(j).Attribute12 :=oprec.Attribute12;
521 l_eam_op_tbl(j).Attribute13 :=oprec.Attribute13;
522 l_eam_op_tbl(j).Attribute14 :=oprec.Attribute14;
523 l_eam_op_tbl(j).Attribute15 :=oprec.Attribute15;
524 l_eam_op_tbl(j).X_POS :=oprec.X_COORDINATE;
525 l_eam_op_tbl(j).Y_POS :=oprec.Y_COORDINATE;
526 l_eam_op_tbl(j).RETURN_STATUS :=oprec.RETURN_STATUS;
527 l_eam_op_tbl(j).TRANSACTION_TYPE :=oprec.TRANSACTION_TYPE;
528
529 END LOOP;
530
531
532
533 -- **************************** RESOURCES ******************
534 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Exploding Resource Requirements'); end if;
535 FOR resrec IN ActivityRES LOOP
536
537
538 k:=k+1; --counter for resources
539
540 l_eam_res_tbl(k).BATCH_ID := resrec.BATCH_ID;
541 l_eam_res_tbl(k).HEADER_ID := resrec.HEADER_ID;
542 l_eam_res_tbl(k).WIP_ENTITY_ID := resrec.WIP_ENTITY_ID;
543 l_eam_res_tbl(k).ORGANIZATION_ID := resrec.ORGANIZATION_ID;
544 l_eam_res_tbl(k).OPERATION_SEQ_NUM := resrec.OPERATION_SEQ_NUM;
545 l_eam_res_tbl(k).DEPARTMENT_ID := resrec.DEPARTMENT_ID;
546 l_eam_res_tbl(k).RESOURCE_SEQ_NUM := resrec.RESOURCE_SEQ_NUM;
547 l_eam_res_tbl(k).RESOURCE_ID := resrec.RESOURCE_ID;
548 l_eam_res_tbl(k).UOM_CODE := resrec.UOM_CODE;
549 l_eam_res_tbl(k).BASIS_TYPE := resrec.BASIS_TYPE;
550 l_eam_res_tbl(k).USAGE_RATE_OR_AMOUNT := resrec.USAGE_RATE_OR_AMOUNT;
551 l_eam_res_tbl(k).ACTIVITY_ID := resrec.ACTIVITY_ID;
552 l_eam_res_tbl(k).SCHEDULED_FLAG := resrec.SCHEDULED_FLAG;
553 l_eam_res_tbl(k).ASSIGNED_UNITS := resrec.ASSIGNED_UNITS;
554 l_eam_res_tbl(k).AUTOCHARGE_TYPE := resrec.AUTOCHARGE_TYPE;
555 l_eam_res_tbl(k).STANDARD_RATE_FLAG := resrec.STANDARD_RATE_FLAG;
556 l_eam_res_tbl(k).APPLIED_RESOURCE_UNITS := resrec.APPLIED_RESOURCE_UNITS;
557 l_eam_res_tbl(k).APPLIED_RESOURCE_VALUE := resrec.APPLIED_RESOURCE_VALUE;
558 l_eam_res_tbl(k).START_DATE := resrec.START_DATE;
559 l_eam_res_tbl(k).COMPLETION_DATE := resrec.COMPLETION_DATE;
560 l_eam_res_tbl(k).SCHEDULE_SEQ_NUM := resrec.SCHEDULE_SEQ_NUM;
561 l_eam_res_tbl(k).SUBSTITUTE_GROUP_NUM := resrec.SUBSTITUTE_GROUP_NUM;
562 l_eam_res_tbl(k).REPLACEMENT_GROUP_NUM := resrec.REPLACEMENT_GROUP_NUM;
563 l_eam_res_tbl(k).ATTRIBUTE_CATEGORY := resrec.ATTRIBUTE_CATEGORY;
564 l_eam_res_tbl(k).ATTRIBUTE1 := resrec.ATTRIBUTE1;
565 l_eam_res_tbl(k).ATTRIBUTE2 := resrec.ATTRIBUTE2;
566 l_eam_res_tbl(k).ATTRIBUTE3 := resrec.ATTRIBUTE3;
567 l_eam_res_tbl(k).ATTRIBUTE4 := resrec.ATTRIBUTE4;
568 l_eam_res_tbl(k).ATTRIBUTE5 := resrec.ATTRIBUTE5;
569 l_eam_res_tbl(k).ATTRIBUTE6 := resrec.ATTRIBUTE6;
570 l_eam_res_tbl(k).ATTRIBUTE7 := resrec.ATTRIBUTE7;
571 l_eam_res_tbl(k).ATTRIBUTE8 := resrec.ATTRIBUTE8;
572 l_eam_res_tbl(k).ATTRIBUTE9 := resrec.ATTRIBUTE9;
573 l_eam_res_tbl(k).ATTRIBUTE10 := resrec.ATTRIBUTE10;
574 l_eam_res_tbl(k).ATTRIBUTE11 := resrec.ATTRIBUTE11;
575 l_eam_res_tbl(k).ATTRIBUTE12 := resrec.ATTRIBUTE12;
576 l_eam_res_tbl(k).ATTRIBUTE13 := resrec.ATTRIBUTE13;
577 l_eam_res_tbl(k).ATTRIBUTE14 := resrec.ATTRIBUTE14;
578 l_eam_res_tbl(k).ATTRIBUTE15 := resrec.ATTRIBUTE15;
579 l_eam_res_tbl(k).RETURN_STATUS := resrec.RETURN_STATUS;
580 l_eam_res_tbl(k).TRANSACTION_TYPE := resrec.TRANSACTION_TYPE;
581
582 END LOOP;
583
584
585 -- **************************** OPERATION NETWORKS ******************
586 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Exploding Operation Networks'); end if;
587 FOR opnetworkrec IN ActivityOPN LOOP
588
589 m:=m+1; --counter for operation networks
590
591 l_eam_op_network_tbl(m).BATCH_ID := opnetworkrec.BATCH_ID;
592 l_eam_op_network_tbl(m).HEADER_ID := opnetworkrec.HEADER_ID;
593 l_eam_op_network_tbl(m).WIP_ENTITY_ID := opnetworkrec.WIP_ENTITY_ID;
594 l_eam_op_network_tbl(m).ORGANIZATION_ID := opnetworkrec.ORGANIZATION_ID;
595 l_eam_op_network_tbl(m).PRIOR_OPERATION := opnetworkrec.PRIOR_OPERATION;
596 l_eam_op_network_tbl(m).NEXT_OPERATION := opnetworkrec.NEXT_OPERATION;
597 l_eam_op_network_tbl(m).ATTRIBUTE_CATEGORY := opnetworkrec.ATTRIBUTE_CATEGORY;
598 l_eam_op_network_tbl(m).ATTRIBUTE1 := opnetworkrec.ATTRIBUTE1;
599 l_eam_op_network_tbl(m).ATTRIBUTE2 := opnetworkrec.ATTRIBUTE2;
600 l_eam_op_network_tbl(m).ATTRIBUTE3 := opnetworkrec.ATTRIBUTE3;
601 l_eam_op_network_tbl(m).ATTRIBUTE4 := opnetworkrec.ATTRIBUTE4;
602 l_eam_op_network_tbl(m).ATTRIBUTE5 := opnetworkrec.ATTRIBUTE5;
603 l_eam_op_network_tbl(m).ATTRIBUTE6 := opnetworkrec.ATTRIBUTE6;
604 l_eam_op_network_tbl(m).ATTRIBUTE7 := opnetworkrec.ATTRIBUTE7;
605 l_eam_op_network_tbl(m).ATTRIBUTE8 := opnetworkrec.ATTRIBUTE8;
606 l_eam_op_network_tbl(m).ATTRIBUTE9 := opnetworkrec.ATTRIBUTE9;
607 l_eam_op_network_tbl(m).ATTRIBUTE10 := opnetworkrec.ATTRIBUTE10;
608 l_eam_op_network_tbl(m).ATTRIBUTE11 := opnetworkrec.ATTRIBUTE11;
609 l_eam_op_network_tbl(m).ATTRIBUTE12 := opnetworkrec.ATTRIBUTE12;
610 l_eam_op_network_tbl(m).ATTRIBUTE13 := opnetworkrec.ATTRIBUTE13;
611 l_eam_op_network_tbl(m).ATTRIBUTE14 := opnetworkrec.ATTRIBUTE14;
612 l_eam_op_network_tbl(m).ATTRIBUTE15 := opnetworkrec.ATTRIBUTE15;
613 l_eam_op_network_tbl(m).RETURN_STATUS := opnetworkrec.RETURN_STATUS;
614 l_eam_op_network_tbl(m).TRANSACTION_TYPE := opnetworkrec.TRANSACTION_TYPE;
615 END LOOP;
616
617
618 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Exploding Material Requirements'); end if;
619
620 BEGIN
621
622 --delete any previous explosions as effectivity/disable dates could be changed
623 --make sure the records are not locked.
624
625
626 select
627 bom_explosion_temp_s.nextval
628 into l_group_id
629 from dual;
630
631
632
633 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Explosion Group Id = ' || l_group_id); end if;
634
635
636 bompexpl.exploder_userexit
637 ( item_id => l_eam_wo_rec.asset_activity_id
638 , org_id => l_eam_wo_rec.organization_id
639 , grp_id => l_group_id
640 , rev_date => TO_CHAR(nvl(p_eam_wo_rec.bom_revision_date,nvl(p_eam_wo_rec.scheduled_start_date,
641 p_eam_wo_rec.scheduled_completion_date)),'YYYY/MM/DD HH24:MI:SS')
642 , alt_desg => l_eam_wo_rec.alternate_bom_designator
643 , err_msg => l_err_text
644 , error_code => l_error_code
645 );
646
647
648 if(l_error_code <> 0) then
649 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
650 else
651 x_return_status := fnd_api.g_ret_sts_success;
652 end if;
653
654
655 open ActivityREQ(v_grpID => l_group_id);
656 loop
657
658 fetch ActivityREQ into matreqrec;
659 <<start_loop_processing>>
660 if(ActivityREQ%NOTFOUND) then
661 close ActivityREQ;
662 exit;
663 end if;
664
665 n := n + 1;
666
667 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Fetching ' || matreqrec.INVENTORY_ITEM_ID); end if;
668
669 l_eam_mat_req_tbl(n).BATCH_ID := matreqrec.BATCH_ID;
670 l_eam_mat_req_tbl(n).HEADER_ID := matreqrec.HEADER_ID;
671 l_eam_mat_req_tbl(n).WIP_ENTITY_ID := matreqrec.WIP_ENTITY_ID;
672 l_eam_mat_req_tbl(n).ORGANIZATION_ID := matreqrec.ORGANIZATION_ID;
673 l_eam_mat_req_tbl(n).OPERATION_SEQ_NUM := matreqrec.OPERATION_SEQ_NUM;
674 l_eam_mat_req_tbl(n).INVENTORY_ITEM_ID := matreqrec.INVENTORY_ITEM_ID;
675 l_eam_mat_req_tbl(n).QUANTITY_PER_ASSEMBLY := matreqrec.QUANTITY_PER_ASSEMBLY;
676 l_eam_mat_req_tbl(n).DEPARTMENT_ID := matreqrec.DEPARTMENT_ID;
677 l_eam_mat_req_tbl(n).WIP_SUPPLY_TYPE := matreqrec.WIP_SUPPLY_TYPE;
678 l_eam_mat_req_tbl(n).DATE_REQUIRED := matreqrec.DATE_REQUIRED;
679 l_eam_mat_req_tbl(n).REQUIRED_QUANTITY := matreqrec.REQUIRED_QUANTITY;
680 --fix for 3550864.
681 l_eam_mat_req_tbl(n).REQUESTED_QUANTITY := matreqrec.REQUESTED_QUANTITY;
682 --fix for 35722280
683 l_eam_mat_req_tbl(n).RELEASED_QUANTITY := matreqrec.RELEASED_QUANTITY;
684 l_eam_mat_req_tbl(n).QUANTITY_ISSUED := matreqrec.QUANTITY_ISSUED;
685 l_eam_mat_req_tbl(n).SUPPLY_SUBINVENTORY := matreqrec.SUPPLY_SUBINVENTORY;
686 l_eam_mat_req_tbl(n).SUPPLY_LOCATOR_ID := matreqrec.SUPPLY_LOCATOR_ID;
687 l_eam_mat_req_tbl(n).MRP_NET_FLAG := matreqrec.MRP_NET_FLAG;
688 l_eam_mat_req_tbl(n).MPS_REQUIRED_QUANTITY := matreqrec.MPS_REQUIRED_QUANTITY;
689 l_eam_mat_req_tbl(n).MPS_DATE_REQUIRED := matreqrec.MPS_DATE_REQUIRED;
690 l_eam_mat_req_tbl(n).SUGGESTED_VENDOR_NAME := matreqrec.SUGGESTED_VENDOR_NAME;
691 l_eam_mat_req_tbl(n).VENDOR_ID := matreqrec.VENDOR_ID;
692 l_eam_mat_req_tbl(n).UNIT_PRICE := matreqrec.UNIT_PRICE;
693 l_eam_mat_req_tbl(n).AUTO_REQUEST_MATERIAL := matreqrec.AUTO_REQUEST_MATERIAL;
694 l_eam_mat_req_tbl(n).COMPONENT_SEQUENCE_ID := matreqrec.COMPONENT_SEQUENCE_ID;
695 l_eam_mat_req_tbl(n).COMMENTS := matreqrec.COMMENTS;
696 l_eam_mat_req_tbl(n).ATTRIBUTE_CATEGORY := matreqrec.ATTRIBUTE_CATEGORY;
697 l_eam_mat_req_tbl(n).ATTRIBUTE1 := matreqrec.ATTRIBUTE1;
698 l_eam_mat_req_tbl(n).ATTRIBUTE2 := matreqrec.ATTRIBUTE2;
699 l_eam_mat_req_tbl(n).ATTRIBUTE3 := matreqrec.ATTRIBUTE3;
700 l_eam_mat_req_tbl(n).ATTRIBUTE4 := matreqrec.ATTRIBUTE4;
701 l_eam_mat_req_tbl(n).ATTRIBUTE5 := matreqrec.ATTRIBUTE5;
702 l_eam_mat_req_tbl(n).ATTRIBUTE6 := matreqrec.ATTRIBUTE6;
703 l_eam_mat_req_tbl(n).ATTRIBUTE7 := matreqrec.ATTRIBUTE7;
704 l_eam_mat_req_tbl(n).ATTRIBUTE8 := matreqrec.ATTRIBUTE8;
705 l_eam_mat_req_tbl(n).ATTRIBUTE9 := matreqrec.ATTRIBUTE9;
706 l_eam_mat_req_tbl(n).ATTRIBUTE10 := matreqrec.ATTRIBUTE10;
707 l_eam_mat_req_tbl(n).ATTRIBUTE11 := matreqrec.ATTRIBUTE11;
708 l_eam_mat_req_tbl(n).ATTRIBUTE12 := matreqrec.ATTRIBUTE12;
709 l_eam_mat_req_tbl(n).ATTRIBUTE13 := matreqrec.ATTRIBUTE13;
710 l_eam_mat_req_tbl(n).ATTRIBUTE14 := matreqrec.ATTRIBUTE14;
711 l_eam_mat_req_tbl(n).ATTRIBUTE15 := matreqrec.ATTRIBUTE15;
712 l_eam_mat_req_tbl(n).RETURN_STATUS := matreqrec.RETURN_STATUS;
713 l_eam_mat_req_tbl(n).TRANSACTION_TYPE := matreqrec.TRANSACTION_TYPE;
714
715 end loop;
716
717 delete bom_explosion_temp
718 where group_id = l_group_id;
719
720 declare
721 l_owning_department number;
722 l_min_op_seq_num number;
723 begin
724
725 if l_eam_op_tbl.count = 0 and l_eam_wo_rec.status_type = 3 then
726 -- create a default operation
727
728 l_owning_department := p_eam_wo_rec.owning_department;
729 if p_eam_wo_rec.owning_department is null then
730 WIP_EAMWORKORDER_PVT.Get_EAM_Owning_Dept_Default
731 ( p_api_version => 1.0,
732 p_init_msg_list => FND_API.G_FALSE,
733 p_commit => FND_API.G_FALSE,
734 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
735 x_return_status => l_def_return_status,
736 x_msg_count => l_def_msg_count,
737 x_msg_data => l_def_msg_data,
738 p_primary_item_id => p_eam_wo_rec.asset_activity_id,
739 p_organization_id => p_eam_wo_rec.organization_id,
740 p_maintenance_object_type => p_eam_wo_rec.maintenance_object_type,
741 p_maintenance_object_id => p_eam_wo_rec.maintenance_object_id,
742 p_rebuild_item_id => p_eam_wo_rec.rebuild_item_id,
743 x_owning_department_id => l_owning_department
744 );
745 l_eam_op_tbl(1).department_id := l_owning_department;
746 end if;
747
748 l_eam_op_tbl(1).WIP_ENTITY_ID := p_eam_wo_rec.wip_entity_id;
749 l_eam_op_tbl(1).ORGANIZATION_ID := p_eam_wo_rec.organization_id;
750 l_eam_op_tbl(1).OPERATION_SEQ_NUM := 10;
751 l_eam_op_tbl(1).STANDARD_OPERATION_ID := null;
752 l_eam_op_tbl(1).DEPARTMENT_ID := l_owning_department;
753 l_eam_op_tbl(1).OPERATION_SEQUENCE_ID := null;
754 fnd_message.set_name('EAM', 'EAM_WO_DEFAULT_OP');
755 l_eam_op_tbl(1).DESCRIPTION := SUBSTRB(fnd_message.get, 1, 240);
756 l_eam_op_tbl(1).MINIMUM_TRANSFER_QUANTITY := 1;
757 l_eam_op_tbl(1).COUNT_POINT_TYPE := 1;
758 l_eam_op_tbl(1).BACKFLUSH_FLAG := 1;
759 l_eam_op_tbl(1).SHUTDOWN_TYPE := null;
760 l_eam_op_tbl(1).START_DATE := p_eam_wo_rec.scheduled_start_date;
761 l_eam_op_tbl(1).COMPLETION_DATE := p_eam_wo_rec.scheduled_completion_date;
762 l_eam_op_tbl(1).TRANSACTION_TYPE := 1;
763 l_eam_op_tbl(1).return_status := null;
764
765 end if;
766
767 if l_eam_op_tbl.count <> 0 then
768
769 l_min_op_seq_num := l_eam_op_tbl(1).operation_seq_num;
770 l_owning_department := l_eam_op_tbl(1).department_id;
771 for i in l_eam_op_tbl.first .. l_eam_op_tbl.last loop
772 if l_eam_op_tbl(i).operation_seq_num < l_min_op_seq_num then
773 l_min_op_seq_num := l_eam_op_tbl(i).operation_seq_num;
774 l_owning_department := l_eam_op_tbl(i).department_id;
775 end if;
776 end loop;
777
778 end if;
779
780 if l_eam_mat_req_tbl.count <> 0 and l_eam_op_tbl.count <> 0 then
781
782 for i in l_eam_mat_req_tbl.first .. l_eam_mat_req_tbl.last loop
783 if l_eam_mat_req_tbl(i).operation_seq_num = 1 then
784 l_eam_mat_req_tbl(i).operation_seq_num := l_min_op_seq_num;
785 l_eam_mat_req_tbl(i).department_id := l_owning_department;
786 end if;
787 end loop;
788
789 end if;
790
791 end;
792
793
794 x_return_status := fnd_api.g_ret_sts_success;
795
796 exception
797 when others then
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 fnd_message.set_name('EAM', 'EAM_UNEXPECTED_ERROR');
800 fnd_message.set_token('ERROR_TEXT', SQLERRM);
801
802 end;
803
804
805 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Activity Explosion completed'); end if;
806
807 begin
808
809 select bill_sequence_id
810 into l_bill_sequence_id
811 from bom_bill_of_materials
812 where organization_id= l_eam_wo_rec.organization_id
813 and assembly_item_id = l_eam_wo_rec.asset_activity_id
814 and (l_eam_wo_rec.alternate_bom_designator is null
815 or (l_eam_wo_rec.alternate_bom_designator is not null
816 and alternate_bom_designator = l_eam_wo_rec.alternate_bom_designator));
817
818 -- Fix for Bug 2787347 Activities having routing and no BOM are failing
819 exception
820
821 when others then
822 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('BOM not present hence no attachments need to be copied from Activity BOM'); end if;
823
824 x_return_status := fnd_api.g_ret_sts_success;
825
826 -- Fix for Bug 2787347 Activities having routing and no BOM are failing
827
828 end;
829
830
831 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug('Coping attachment'); end if;
832
833 COPY_ATTACHMENT
834 ( p_organization_id => l_eam_wo_rec.organization_id
835 , p_asset_activity_id => l_eam_wo_rec.asset_activity_id
836 , p_wip_entity_id => l_eam_wo_rec.wip_entity_id
837 , p_bill_sequence_id => l_bill_sequence_id
838 , x_error_message => l_other_message
839 , x_return_status => l_return_status
840 );
841
842 --Bug#3342391 : Calling copy attachment for each of the operations exploded from the asset activity.
843 FOR activityOpRec IN ActivityOP LOOP
844 l_common_routing_seq_id := activityOpRec.COMMON_ROUTING_SEQUENCE_ID ;
845 l_organization_id := activityOpRec.ORGANIZATION_ID;
846 l_wip_entity_id := activityOpRec.WIP_ENTITY_ID;
847 l_routing_available := 'Y';
848
849 COPY_ATTACHMENT
850 ( p_organization_id => l_organization_id
851 , p_asset_activity_id => NULL
852 , p_wip_entity_id => l_wip_entity_id
853 , p_bill_sequence_id => NULL
854 , x_error_message => l_other_message
855 , x_return_status => l_return_status
856 , p_common_routing_sequence_id => NULL
857 , p_operation_sequence_id => activityOpRec.OPERATION_SEQUENCE_ID
858 , p_operation_sequence_num => activityOpRec.OPERATION_SEQ_NUM
859 );
860
861 END LOOP;
862
863 --Bug#3342391 : The copy attachment procedure is being called to copy the attachments attached to the routing
864 IF (l_routing_available = 'Y') THEN
865 COPY_ATTACHMENT
866 ( p_organization_id => l_organization_id
867 , p_asset_activity_id => NULL
868 , p_wip_entity_id => l_wip_entity_id
869 , p_bill_sequence_id => NULL
870 , x_error_message => l_other_message
871 , x_return_status => l_return_status
872 , p_common_routing_sequence_id => l_common_routing_seq_id
873 , p_operation_sequence_id => NULL
874 , p_operation_sequence_num => NULL
875 );
876 END IF;
877
878 x_eam_wo_rec := l_eam_wo_rec;
879 x_eam_op_tbl := l_eam_op_tbl;
880 x_eam_op_network_tbl := l_eam_op_network_tbl;
881 x_eam_res_tbl := l_eam_res_tbl;
882 x_eam_res_inst_tbl := l_eam_res_inst_tbl;
883 x_eam_sub_res_tbl := l_eam_sub_res_tbl;
884 x_eam_res_usage_tbl := l_eam_res_usage_tbl;
885 x_eam_mat_req_tbl := l_eam_mat_req_tbl;
886
887 END EXPLODE_ACTIVITY;
888
889
890 --Bug#3342391: Modified the function definition to pass 3 new parameters and default it to null
891 -- for backward compatibility.The parameters contain the operation_sequence_id and operations_sequence_number
892 -- and common_routing_sequence_id
893 PROCEDURE COPY_ATTACHMENT
894 ( p_organization_id IN NUMBER
895 , p_asset_activity_id IN NUMBER
896 , p_wip_entity_id IN NUMBER
897 , p_bill_sequence_id IN NUMBER
898 , x_error_message OUT NOCOPY VARCHAR2
899 , x_return_status OUT NOCOPY VARCHAR2
900 , p_common_routing_sequence_id IN NUMBER := NULL
901 , p_operation_sequence_id IN NUMBER := NULL
902 , p_operation_sequence_num IN NUMBER := NULL
903 )
904 IS
905
906 begin
907
908 if (p_asset_activity_id) is not null then
909
910 fnd_attached_documents2_pkg.copy_attachments(
911 X_from_entity_name => 'MTL_SYSTEM_ITEMS',
912 X_from_pk1_value => p_organization_id,
913 X_from_pk2_value => p_asset_activity_id,
914 X_from_pk3_value => '',
915 X_from_pk4_value => '',
916 X_from_pk5_value => '',
917 X_to_entity_name => 'EAM_WORK_ORDERS',
918 X_to_pk1_value => p_organization_id,
919 X_to_pk2_value => p_wip_entity_id,
920 X_to_pk3_value => '',
921 X_to_pk4_value => '',
922 X_to_pk5_value => '',
923 X_created_by => FND_GLOBAL.USER_ID,
924 X_last_update_login => '',
925 X_program_application_id=> '',
926 X_program_id => '',
927 X_request_id => ''
928 );
929
930 end if;
931
932 if (p_bill_sequence_id) is not null then
933
934 fnd_attached_documents2_pkg.copy_attachments(
935 X_from_entity_name => 'BOM_BILL_OF_MATERIALS',
936 X_from_pk1_value => p_bill_sequence_id,
937 X_from_pk2_value => '',
938 X_from_pk3_value => '',
939 X_from_pk4_value => '',
940 X_from_pk5_value => '',
941 X_to_entity_name => 'EAM_WORK_ORDERS',
942 X_to_pk1_value => p_organization_id,
943 X_to_pk2_value => p_wip_entity_id,
944 X_to_pk3_value => '',
945 X_to_pk4_value => '',
946 X_to_pk5_value => '',
947 X_created_by => FND_GLOBAL.USER_ID,
948 X_last_update_login => '',
949 X_program_application_id=> '',
950 X_program_id => '',
951 X_request_id => ''
952 );
953 end if;
954
955 --Bug#3342391: Adding the code to copy the attachment from the routing header and each of the operations under it.
956 IF (p_common_routing_sequence_id) IS NOT NULL THEN
957 -- Copy the attachment from the routing header.
958 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
959 X_from_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
960 X_from_pk1_value => p_common_routing_sequence_id,
961 X_from_pk2_value => '',
962 X_from_pk3_value => '',
963 X_from_pk4_value => '',
964 X_from_pk5_value => '',
965 X_to_entity_name => 'EAM_WORK_ORDERS',
966 X_to_pk1_value => p_organization_id,
967 X_to_pk2_value => p_wip_entity_id,
968 X_to_pk3_value => '',
969 X_to_pk4_value => '',
970 X_to_pk5_value => '',
971 X_created_by => FND_GLOBAL.USER_ID,
972 X_last_update_login => '',
973 X_program_application_id=> '',
974 X_program_id => '',
975 X_request_id => ''
976 );
977 END IF;
978
979 -- Copy the attachment for each of the operations
980 IF p_operation_sequence_id IS NOT NULL THEN
981 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
982 X_from_entity_name => 'BOM_OPERATION_SEQUENCES',
983 X_from_pk1_value => p_operation_sequence_id,
984 X_from_pk2_value => '',
985 X_from_pk3_value => '',
986 X_from_pk4_value => '',
987 X_from_pk5_value => '',
988 X_to_entity_name => 'EAM_DISCRETE_OPERATIONS',
989 X_to_pk1_value => p_wip_entity_id,
990 X_to_pk2_value => p_operation_sequence_num ,
991 X_to_pk3_value => p_organization_id,
992 X_to_pk4_value => '',
993 X_to_pk5_value => '',
994 X_created_by => FND_GLOBAL.USER_ID,
995 X_last_update_login => '',
996 X_program_application_id=> '',
997 X_program_id => '',
998 X_request_id => ''
999 );
1000
1001 END IF;
1002
1003
1004 x_return_status := fnd_api.g_ret_sts_success;
1005
1006 exception
1007 when others then
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009 fnd_message.set_name('EAM', 'EAM_UNEXPECTED_ERROR');
1010 fnd_message.set_token('ERROR_TEXT', SQLERRM);
1011
1012 END COPY_ATTACHMENT;
1013
1014
1015 END EAM_EXPLODE_ACTIVITY_PVT;