1 package body bompnord as
2 /* $Header: BOMEORDB.pls 120.2 2005/11/21 05:43:23 arudresh ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMEORDB.pls |
10 | DESCRIPTION : This file contains package body for the procedure used
11 | by the Enter Orders form
12 | to call the exploder. The procedure first checks if
13 | a preexpldoed model already exists. If it does, then
14 | it returns true or, if the copy_flag = 1, makes a copy of
15 | the bill based on the rev_date and passes back the groupid
16 | of the copy. If not or the preexploded model is marked for
17 | reexplosion, then it first calls the custom oe exploder to
18 | create a new master bill.
19 |
20 | Parameters: org_id organization_id
21 | copy_flag 1 - create copy (used for the Configurator)
22 | 2 - don't create copy
23 | expl_type 'OPTIONAL' or 'INCLUDED'
24 | order_by 1 - Op seq, item seq
25 | 2 - Item seq, op seq
26 | grp_id unique value to identify current
27 | copy of the bill (only used by the
28 | configurator)
29 | session_id unique value to identify current session
30 | (only used by the configurator)
31 | levels_to_explode
32 | item_id item id of asembly to explode
33 | starting_rev_date
34 | comp_code concatenated component code (not used)
35 | user_id
36 | err_msg error message out buffer
37 | error_code error code out. returns sql error code
38 | if sql error, 9999 if loop detected.
39 | Revision
40 | 02/20/94 Shreyas Shah creation
41 | 12/27/94 Robert Yee Modified to avoid duplicate models
42 | 12/28/94 Robert Yee Remove updating of other session's flags
43 | 09/21/95 Raj Jain modified for new BOM_OE_EXPLODER
44 | 09/26/95 Raj Jain split BOMPNORD.sql into BOMEORDB.pls and
45 | BOMEORDS.pls
46 | 04/28/97 Robert Yee Redesign for partial explosions
47 | 01/05/03 Rahul Chitko Added default for date so that explosion will
48 | happen successfully for sysdate even if it is
49 | called with a null rev_date.
50 | |
51 +==========================================================================*/
52
53 Procedure Delete_Bom_Expl(top_bill_id Number,
54 arg_expl_type Varchar2)
55 IS
56 pragma AUTONOMOUS_TRANSACTION;
57
58 BEGIN
59 DELETE from bom_explosions
60 WHERE top_bill_sequence_id = top_bill_id
61 AND explosion_type = arg_expl_type;
62 COMMIT;
63 END Delete_Bom_Expl;
64
65 PROCEDURE bmxporder_explode_for_order (
66 org_id IN NUMBER,
67 copy_flag IN NUMBER DEFAULT 2,
68 expl_type IN VARCHAR2 DEFAULT 'OPTIONAL',
69 order_by IN NUMBER DEFAULT 1,
70 grp_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
71 session_id IN NUMBER DEFAULT 0,
72 levels_to_explode IN NUMBER DEFAULT 60,
73 item_id IN NUMBER,
74 comp_code IN VARCHAR2 DEFAULT '',
75 starting_rev_date IN DATE DEFAULT SYSDATE - 1000,
76 rev_date IN VARCHAR2 DEFAULT NULL,
77 user_id IN NUMBER DEFAULT 0,
78 commit_flag IN VARCHAR2 DEFAULT 'N',
79 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
80 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
81 alt_bom_designator IN VARCHAR2 DEFAULT NULL
82 ) IS
83
84 stmt_num NUMBER;
85 X_ReExplode boolean := false; -- Re-Explode
86 X_PreExploded boolean := false; -- Exploded before.
87 X_EarlyRevs boolean := false; -- Explode earlier revisions
88 X_Duplicate boolean := false; -- Duplicate explosions
89 X_top_bill_id NUMBER := -1;
90 X_sessn_id NUMBER;
91
92 X_revision_date DATE;
93
94 X_no_top_assy EXCEPTION;
95 X_INT_ERROR EXCEPTION;
96
97 X_exp_past_days number := 1000;
98
99 -- X_SortWidth constant number := 7; -- maximum of 9999999 components per bill
100 X_SortWidth constant number := Bom_Common_Definitions.G_Bom_SortCode_Width;
101
102 CURSOR get_bill_id IS
103 SELECT bill_sequence_id
104 FROM bom_bill_of_materials
105 WHERE assembly_item_id = ITEM_ID
106 AND organization_id = ORG_ID
107 AND alternate_bom_designator is null;
108
109 Cursor Convert_Date is
110 SELECT to_date(nvl(REV_DATE, SYSDATE),'YYYY/MM/DD HH24:MI') rev_datetime
111 FROM sys.dual;
112
113 Cursor Check_Flag is
114 Select 1
115 FROM bom_explosions be
116 WHERE be.top_bill_sequence_id = X_TOP_BILL_ID
117 AND be.explosion_type = EXPL_TYPE
118 and be.rexplode_flag = 1
119 and rownum = 1;
120
121 Cursor Get_Eff_Date is
122 select to_date(to_char(effectivity_date,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI') effectivity_date
123 from bom_explosions
124 where top_bill_sequence_id = X_TOP_BILL_ID
125 AND explosion_type = EXPL_TYPE
126 and sort_order = lpad('1', X_SortWidth, '0');
127
128 CURSOR lock_bom IS
129 SELECT 1
130 FROM bom_bill_of_materials bbom
131 WHERE organization_id = ORG_ID
132 AND assembly_item_id = ITEM_ID
133 AND alternate_bom_designator is NULL
134 FOR UPDATE OF assembly_item_id NOWAIT;
135
136 X_Row_Locked EXCEPTION;
137 Pragma exception_init(X_Row_locked, -54);
138
139 Cursor Get_New_Group is
140 SELECT bom_config_explosions_s.nextval group_id
141 FROM sys.dual;
142
143 Begin
144
145 error_code := 0;
146 x_sessn_id := session_id;
147
148
149 /* Get the date version of the rev_date parameter. */
150
151 -- Bug 4252245
152 -- Removed the convert_date cursor call and wrote seperate sql statements
153 -- based on rev_date NULL or NOT NULL values
154
155 /*For X_Date in Convert_Date loop
156 x_revision_date := X_Date.rev_datetime;
157 End loop;*/
158
159 If rev_date is NOT NULL then
160 stmt_num := 10;
161 SELECT to_date(REV_DATE,'YYYY/MM/DD HH24:MI')
162 into x_revision_date
163 FROM sys.dual;
164 else
165 stmt_num := 20;
166 SELECT to_date(to_char(sysdate,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI')
167 into x_revision_date
168 FROM sys.dual;
169 end if;
170
171 -- Bug 4252245 ends
172
173 -- Get the bill sequence id for the item/org passed in. If a primary
174 -- bill does not exist, raise an exception.
175
176 stmt_num := 25;
177
178 For X_Bill in get_bill_id loop
179 x_top_bill_id := X_Bill.bill_sequence_id;
180 End loop;
181 IF x_top_bill_id < 0 THEN
182 raise x_no_top_assy;
183 END IF;
184
185 -- Check if any rexplode flag is set for the bill in bom_explosions.
186
187 stmt_num := 30;
188 For X_Explosion in Check_Flag loop
189 X_ReExplode := true;
190 End loop;
191
192
193 -- Check if any rows exist in bom_explosions where component is effective
194 -- before requested date. If none then set re-explosion flag to ensure it
195 -- explodes again to cover earlier revisions
196 --
197 -- Also check for duplicate explosions. If duplicate found, then re-explode.
198 --
199
200 stmt_num := 40;
201 For X_Root in Get_Eff_Date loop
202 X_PreExploded := true; -- Exploded before.
203 If Get_Eff_Date%rowcount > 1 then -- duplicate rows
204 X_Duplicate := true; -- Duplicate explosions
205 X_ReExplode := true;
206 Exit;
207 End if;
208 If X_Root.effectivity_date > X_revision_date then
209 X_EarlyRevs := true; -- Explode earlier revisions
210 X_ReExplode := true;
211 End if;
212 End loop;
213
214 If not X_PreExploded then -- new explosion
215 X_ReExplode := true;
216 End if;
217
218 If X_ReExplode then
219 Begin
220
221 -- Lock the bill header so that two users don't try to create or
222 -- re-explode the same bill at the same time. If we can't get a
223 -- lock and the bill already exists then just pass all the re-explode
224 -- logic (we'll use the existing bom). If the bom doesn't exist yet,
225 -- then raise the x_row_locked exception.
226
227 stmt_num := 50;
228 -- For X_Locked_Bill in lock_bom loop
229
230 stmt_num := 60;
231 If X_EarlyRevs or X_Duplicate then
232 --Added call to Delete_Bom_Expl procedure to avoid deadlock issue and removed earlier delete st
233 Delete_Bom_Expl(X_TOP_BILL_ID,EXPL_TYPE);
234 End if; -- re-explode entire tree
235
236 stmt_num := 70;
237
238 FND_PROFILE.GET('BOM:OE_EXP_PAST_DAYS',x_exp_past_days);
239
240 /* Call the oe bom exploder to create the non-date specific bom */
241
242
243 stmt_num := 80;
244 BOM_OE_EXPLODER_PKG.be_exploder
245 (arg_org_id => org_id,
246 arg_starting_rev_date => least(X_revision_date, sysdate -
247 nvl(x_exp_past_days,1000)),
248 arg_expl_type => expl_type,
249 arg_order_by => order_by,
250 arg_levels_to_explode => levels_to_explode,
251 arg_item_id => item_id,
252 arg_comp_code => comp_code,
253 arg_user_id => user_id,
254 arg_err_msg => err_msg,
255 arg_error_code => error_code,
256 arg_alt_bom_desig => alt_bom_designator);
257
258 -- End loop;
259
260 If (error_code <> 0) then
261 raise X_INT_ERROR;
262 end if;
263
264 EXCEPTION
265 When X_Row_Locked then
266 If not X_PreExploded then -- new explosion
267 Raise;
268 End if;
269 End; -- exploder nested block
270
271 END IF; -- Re-Explode
272
273
274 IF (COPY_FLAG = 1) THEN
275
276 -- The procedure is being called from the configurator form, which
277 -- needs it's own copy of the bill. Using the master bill, copy
278 -- the bill as of the given rev_date into BOM_CONFIG_EXPLOSIONS.
279 -- Pass back the group_id of this copy.
280
281 stmt_num := 90;
282
283 For X_Group in Get_New_Group loop
284
285
286 stmt_num := 100;
287
288 INSERT INTO bom_config_explosions (
289 TOP_BILL_SEQUENCE_ID,
290 BILL_SEQUENCE_ID,
291 ORGANIZATION_ID,
292 EXPLOSION_TYPE,
293 COMPONENT_SEQUENCE_ID,
294 COMPONENT_ITEM_ID,
295 PLAN_LEVEL,
296 EXTENDED_QUANTITY,
297 SORT_ORDER,
298 GROUP_ID,
299 SESSION_ID,
300 TOP_ITEM_ID,
301 COMPONENT_QUANTITY,
302 SO_BASIS,
303 OPTIONAL,
304 MUTUALLY_EXCLUSIVE_OPTIONS,
305 CHECK_ATP,
306 SHIPPING_ALLOWED,
307 REQUIRED_TO_SHIP,
308 REQUIRED_FOR_REVENUE,
309 INCLUDE_ON_SHIP_DOCS,
310 INCLUDE_ON_BILL_DOCS,
311 LOW_QUANTITY,
312 HIGH_QUANTITY,
313 PICK_COMPONENTS,
314 PRIMARY_UOM_CODE,
315 PRIMARY_UNIT_OF_MEASURE,
316 BASE_ITEM_ID,
317 ATP_COMPONENTS_FLAG,
318 ATP_FLAG,
319 BOM_ITEM_TYPE,
320 PICK_COMPONENTS_FLAG,
321 REPLENISH_TO_ORDER_FLAG,
322 SHIPPABLE_ITEM_FLAG,
323 CUSTOMER_ORDER_FLAG,
324 INTERNAL_ORDER_FLAG,
325 CUSTOMER_ORDER_ENABLED_FLAG,
326 INTERNAL_ORDER_ENABLED_FLAG,
327 SO_TRANSACTIONS_FLAG,
328 DESCRIPTION,
329 ASSEMBLY_ITEM_ID,
330 CONFIGURATOR_FLAG,
331 COMPONENT_CODE,
332 LOOP_FLAG,
333 PARENT_BOM_ITEM_TYPE,
334 OPERATION_SEQ_NUM,
335 ITEM_NUM,
336 EFFECTIVITY_DATE,
337 DISABLE_DATE,
338 IMPLEMENTATION_DATE,
339 REXPLODE_FLAG,
340 COMMON_BILL_SEQUENCE_ID)
341 SELECT
342 TOP_BILL_SEQUENCE_ID,
343 BILL_SEQUENCE_ID,
344 ORGANIZATION_ID,
345 expl_type,
346 COMPONENT_SEQUENCE_ID,
347 COMPONENT_ITEM_ID,
348 PLAN_LEVEL,
349 EXTENDED_QUANTITY,
350 SORT_ORDER,
351 X_Group.group_id,
352 x_sessn_id,
353 TOP_ITEM_ID,
354 COMPONENT_QUANTITY,
355 SO_BASIS,
356 OPTIONAL,
357 MUTUALLY_EXCLUSIVE_OPTIONS,
358 CHECK_ATP,
359 SHIPPING_ALLOWED,
360 REQUIRED_TO_SHIP,
361 REQUIRED_FOR_REVENUE,
362 INCLUDE_ON_SHIP_DOCS,
363 INCLUDE_ON_BILL_DOCS,
364 LOW_QUANTITY,
365 HIGH_QUANTITY,
366 PICK_COMPONENTS,
367 PRIMARY_UOM_CODE,
368 PRIMARY_UNIT_OF_MEASURE,
369 BASE_ITEM_ID,
370 ATP_COMPONENTS_FLAG,
371 ATP_FLAG,
372 BOM_ITEM_TYPE,
373 PICK_COMPONENTS_FLAG,
374 REPLENISH_TO_ORDER_FLAG,
375 SHIPPABLE_ITEM_FLAG,
376 CUSTOMER_ORDER_FLAG,
377 INTERNAL_ORDER_FLAG,
378 CUSTOMER_ORDER_ENABLED_FLAG,
379 INTERNAL_ORDER_ENABLED_FLAG,
380 SO_TRANSACTIONS_FLAG,
381 DESCRIPTION,
382 ASSEMBLY_ITEM_ID,
383 'Y',
384 COMPONENT_CODE,
385 LOOP_FLAG,
386 PARENT_BOM_ITEM_TYPE,
387 OPERATION_SEQ_NUM,
388 ITEM_NUM,
389 EFFECTIVITY_DATE,
390 DISABLE_DATE,
391 IMPLEMENTATION_DATE,
392 REXPLODE_FLAG,
393 COMMON_BILL_SEQUENCE_ID
394 FROM bom_explosions
395 WHERE top_bill_sequence_id = X_TOP_BILL_ID
396 AND explosion_type = EXPL_TYPE
397 AND plan_level > 0
398 AND effectivity_date <= X_REVISION_DATE
399 AND disable_date > X_REVISION_DATE;
400
401 grp_id := X_Group.group_id;
402
403 End loop;
404
405 ELSE
406
407 grp_id := 0;
408
409 END IF; -- (COPY_FLAG = 1)
410
411 IF commit_flag = 'Y' then
412 commit;
413 END IF;
414
415 error_code := 0;
416 err_msg := '';
417
418 EXCEPTION
419 When X_Row_Locked then
420 FND_MESSAGE.set_name('BOM','BOM_CHANGES_IN_PROCESS');
421 err_msg := fnd_message.get;
422 error_code := -54;
423 when X_INT_ERROR then
424 NULL;
425 WHEN x_no_top_assy THEN
426 error_code := 9998;
427 FND_MESSAGE.Set_Name('BOM', 'BOM_BILL_DOES_NOT_EXIST');
428 err_msg := FND_MESSAGE.GET;
429 when others then
430 err_msg := 'BOMPNORD(' || stmt_num || ')' || substrb(SQLERRM, 1, 60);
431 error_code := SQLCODE;
432 END bmxporder_explode_for_order;
433
434 END bompnord;
[Home] [Help]