[Home] [Help]
PACKAGE BODY: APPS.OE_OPT_PROCESS
Source
1 PACKAGE BODY OE_OPT_PROCESS AS
2 /* $Header: OEXOPPRB.pls 115.3 99/07/16 08:13:58 porting shi $ */
3
4 OE_SUCCESS CONSTANT VARCHAR2(1) := 'Y';
5 OE_FAILURE CONSTANT VARCHAR2(1) := 'N';
6
7 PROCEDURE Get_Item_Information
8 (
9 Options_Inventory_Item_Id IN NUMBER,
10 Options_Item_Type_Code IN OUT VARCHAR2,
11 Options_Item_Type OUT VARCHAR2,
12 P_Organization_Id IN NUMBER,
13 Lines_Component_Sequence_Id IN NUMBER,
14 Options_Component_Code IN VARCHAR2,
15 Configuration_Parent_Line_Id IN NUMBER,
16 Options_ATO_Flag OUT VARCHAR2,
17 Options_ATO_Line_Id OUT NUMBER,
18 ATO_Parent_Component_Code OUT VARCHAR2,
19 Lines_ATO_Flag IN VARCHAR2 ,
20 Options_Line_Id IN OUT NUMBER,
21 Serviceable_Flag OUT VARCHAR2,
22 Item IN VARCHAR2,
23 Lines_Ship_Model_Comp_Flag IN VARCHAR2,
24 Options_Ship_Model_Comp_Flag OUT VARCHAR2,
25 Options_Plan_Level IN NUMBER,
26 Lines_Creation_Date_Time IN DATE,
27 Return_Status OUT VARCHAR2
28 )
29 is
30
31 L_Return_status VARCHAR2(1);
32
33
34
35 CURSOR c_ato_attributes(x_options_comp_code VARCHAR2,
36 x_lines_comp_seq_id NUMBER,
37 x_options_plan_level NUMBER,
38 x_lines_creation_date DATE) is
39 SELECT 'Y'
40 ,DECODE( BOMEXP.COMPONENT_CODE,
41 x_options_comp_code, NULL, OELIN.LINE_ID )
42 ,BOMEXP.COMPONENT_CODE
43 FROM BOM_EXPLOSIONS BOMEXP
44 ,MTL_SYSTEM_ITEMS MTLITM
45 ,SO_LINES OELIN
46 WHERE BOMEXP.TOP_BILL_SEQUENCE_ID = x_lines_comp_seq_id
47 AND BOMEXP.EXPLOSION_TYPE = 'OPTIONAL'
48 AND BOMEXP.PLAN_LEVEL <= x_options_plan_level
49 AND BOMEXP.EFFECTIVITY_DATE <=
50 NVL(x_lines_creation_date, SYSDATE)
51 AND BOMEXP.DISABLE_DATE >
52 NVL(x_lines_creation_date, SYSDATE)
53 AND BOMEXP.COMPONENT_CODE =
54 SUBSTR( x_options_comp_code, 1,
55 LENGTH( BOMEXP.COMPONENT_CODE ) )
56 AND LENGTH( BOMEXP.COMPONENT_CODE ) <=
57 LENGTH( x_options_comp_code )
58 AND MTLITM.ORGANIZATION_ID = BOMEXP.ORGANIZATION_ID
59 AND MTLITM.INVENTORY_ITEM_ID = BOMEXP.COMPONENT_ITEM_ID
60 AND DECODE( MTLITM.BOM_ITEM_TYPE,
61 1, NVL( MTLITM.REPLENISH_TO_ORDER_FLAG, 'N' ),
62 4, DECODE( MTLITM.REPLENISH_TO_ORDER_FLAG,
63 'Y', DECODE( MTLITM.BUILD_IN_WIP_FLAG,
64 'Y', 'Y', 'N' ),
65 'N' ),
66 'N' ) = 'Y'
67 AND OELIN.PARENT_LINE_ID (+) = CONFIGURATION_PARENT_LINE_ID
68 AND OELIN.SERVICE_PARENT_LINE_ID (+) is NULL
69 AND OELIN.COMPONENT_CODE (+) = BOMEXP.COMPONENT_CODE
70 ORDER BY BOMEXP.SORT_ORDER;
71
72 begin
73
74 Return_Status:=OE_SUCCESS;
75
76 Options_Ship_Model_Comp_Flag:=Lines_Ship_Model_Comp_Flag;
77
78
79 if (Lines_ATO_Flag = 'Y') then
80 Options_ATO_Flag:='Y';
81 Options_ATO_Line_Id:=Configuration_Parent_Line_Id;
82 else
83 Options_ATO_Flag:='N';
84 Options_ATO_Line_Id:=NULL;
85 end if;
86
87 if OPTIONS_INVENTORY_ITEM_ID is not null then
88 SELECT DECODE( BOM_ITEM_TYPE,
89 1, 'MODEL',
90 2, 'CLASS',
91 4, DECODE( PICK_COMPONENTS_FLAG, 'Y', 'KIT', 'STANDARD' ),
92 'UNKNOWN' ),
93 NVL(SERVICEABLE_PRODUCT_FLAG,'N')
94 INTO Options_ITEM_TYPE_CODE,
95 Serviceable_Flag
96 FROM MTL_SYSTEM_ITEMS
97 WHERE ORGANIZATION_ID = P_Organization_Id
98 AND INVENTORY_ITEM_ID = Options_INVENTORY_ITEM_ID;
99
100 select meaning into Options_Item_Type from so_lookups
101 where lookup_type = 'ITEM TYPE' and
102 lookup_code = Options_Item_Type_Code;
103
104
105 --Search for a parent ATO component in the explosion table. Note that we
106 --cannot search SO_LINES for a parent ATO component because it may not yet
107 --exist in the database. Also note that we're looking for the highest-
108 --positioned ATO component in the BOM (the ORDER BY clause ensures that the
109 --highest-positioned component is selected first). The cursor now
110 --explicitly retrieves only the first record.
111
112 if Lines_ATO_Flag <> 'Y' THEN
113
114 OPEN c_ato_attributes(options_component_code,
115 lines_component_sequence_id,
116 options_plan_level,
117 lines_creation_date_time);
118
119 FETCH c_ato_attributes INTO
120 options_ato_flag,
121 options_ato_line_id,
122 ato_parent_component_code;
123
124 CLOSE c_ato_attributes;
125
126 end if;
127
128 end if;
129
130 exception
131
132 when no_data_found then
133 null;
134 when too_many_rows then
135 null;
136 when others then
137 Return_Status:=OE_FAILURE;
138 OE_MSG.Internal_Exception(Routine=>
139 'OE_OPT_PROCESS.Get_Item_Information',
140 Operation=>'',
141 Object=>'OPTION',
142 Message=>' When Others'||sqlcode);
143
144
145 end Get_Item_Information;
146
147
148 PROCEDURE Get_Option_Detail_Controls
149 (
150 World_Organization_Id IN NUMBER,
151 Options_Inventory_Item_Id IN NUMBER,
152 Options_ATO_Flag IN VARCHAR2,
153 ATO_Parent_Component_Code IN VARCHAR2,
154 Options_Component_Code IN VARCHAR2,
155 Options_ATO_Line_Id IN NUMBER,
156 Options_Schedulable_Flag OUT VARCHAR2,
157 Order_Enforce_List_Prices_Flag IN VARCHAR2,
158 Options_Adjustable_Flag OUT VARCHAR2,
159 Apply_Order_Adjs_Flag OUT VARCHAR2,
160 Options_Serviceable_Flag OUT VARCHAR2,
161 P_Return_Status OUT VARCHAR2
162 )
163
164 is
165
166 begin
167
168
169 if (Options_ATO_Flag = 'Y') then
170 -- if (Options_ATO_Parent_Component_Code =
171 -- Options_Component_Code) then
172 if ( Options_ATO_Line_Id is null ) then
173 Options_Schedulable_Flag:='Y';
174 else
175 Options_Schedulable_Flag:='N';
176 end if;
177 else
181 if (Order_Enforce_List_Prices_Flag = 'Y') then
178 Options_Schedulable_Flag:='Y';
179 end if;
180
182 Options_Adjustable_Flag:='N';
183 else
184 Options_Adjustable_Flag:='Y';
185 end if;
186
187 Apply_Order_Adjs_Flag:='Y';
188
189 SELECT NVL(SERVICEABLE_PRODUCT_FLAG,'N')
190 INTO Options_Serviceable_Flag
191 FROM MTL_SYSTEM_ITEMS
192 WHERE ORGANIZATION_ID = World_Organization_Id
193 AND INVENTORY_ITEM_ID = Options_Inventory_Item_Id;
194
195 exception
196
197 when no_data_found then
198
199 null;
200
201 when others then
202 P_Return_Status:=OE_FAILURE;
203 OE_MSG.Internal_Exception(Routine=>
204 'OE_OPT_PROCESS.Get_Option_Detail_Controls',
205 Operation=>'',
206 Object=>'OPTION',
207 Message=>' When Others');
208
209
210 end Get_Option_Detail_Controls;
211
212 PROCEDURE Get_ATO_Parent_Information
213 (
217 )
214 Options_ATO_Line_Id IN NUMBER,
215 Options_ATO_Parent_Comp_Code OUT VARCHAR2,
216 P_Return_Status OUT VARCHAR2
218 is
219
220 begin
221
222 SELECT COMPONENT_CODE
223 INTO Options_ATO_Parent_Comp_Code
224 FROM SO_LINES
225 WHERE LINE_ID = Options_ATO_Line_Id;
226
227 exception
228
229 when no_data_found then
230 null;
231
232 when others then
233 P_Return_Status:=OE_FAILURE;
234 OE_MSG.Internal_Exception(Routine=>
235 'OE_OPT_PROCESS.Get_ATO_Parent_Information',
236 Operation=>'',
237 Object=>'OPTION',
238 Message=>' When Others');
239
240 end Get_ATO_Parent_Information;
241
242 PROCEDURE Insert_Installn_Details
243 (
244 P_Line_Id IN NUMBER,
245 P_User_Id IN NUMBER,
246 P_Login_Id IN NUMBER,
247 P_Configuration_Parent_Line_Id IN NUMBER,
248 P_Return_Status OUT VARCHAR2
249 )
250 is
251
252 begin
253
254 P_Return_Status:=OE_SUCCESS;
255
256 INSERT INTO SO_LINE_SERVICE_DETAILS
257 ( LINE_SERVICE_DETAIL_ID
258 , CREATION_DATE
259 , CREATED_BY
260 , LAST_UPDATE_DATE
261 , LAST_UPDATED_BY
262 , LAST_UPDATE_LOGIN
263 , LINE_ID
264 , SOURCE_LINE_SERVICE_DETAIL_ID
265 , TRANSACTION_TYPE_ID
266 , SYSTEM_ID
267 , SYSTEM_TYPE_CODE
268 , CUSTOMER_PRODUCT_ID
269 , CUSTOMER_PRODUCT_TYPE_CODE
270 , CUSTOMER_PRODUCT_QUANTITY
274 , CONTEXT
271 , INSTALLATION_SITE_USE_ID
272 , TECHNICAL_CONTACT_ID
273 , SERVICE_ADMIN_CONTACT_ID
275 , ATTRIBUTE1
276 , ATTRIBUTE2
277 , ATTRIBUTE3
278 , ATTRIBUTE4
279 , ATTRIBUTE5
280 , ATTRIBUTE6
281 , ATTRIBUTE7
282 , ATTRIBUTE8
283 , ATTRIBUTE9
284 , ATTRIBUTE10
285 , ATTRIBUTE11
286 , ATTRIBUTE12
287 , ATTRIBUTE13
288 , ATTRIBUTE14
289 , ATTRIBUTE15
290 )
291 SELECT SO_LINE_SERVICE_DETAILS_S.NEXTVAL
292 , SYSDATE
293 , P_User_Id
294 , SYSDATE
295 , P_User_Id
296 , P_Login_Id
297 , P_Line_Id
298 , LINE_SERVICE_DETAIL_ID
299 , TRANSACTION_TYPE_ID
300 , SYSTEM_ID
301 , SYSTEM_TYPE_CODE
302 , CUSTOMER_PRODUCT_ID
303 , CUSTOMER_PRODUCT_TYPE_CODE
304 , CUSTOMER_PRODUCT_QUANTITY
305 , INSTALLATION_SITE_USE_ID
306 , TECHNICAL_CONTACT_ID
307 , SERVICE_ADMIN_CONTACT_ID
308 , CONTEXT
309 , ATTRIBUTE1
310 , ATTRIBUTE2
311 , ATTRIBUTE3
312 , ATTRIBUTE4
313 , ATTRIBUTE5
314 , ATTRIBUTE6
315 , ATTRIBUTE7
316 , ATTRIBUTE8
317 , ATTRIBUTE9
318 , ATTRIBUTE10
319 , ATTRIBUTE11
320 , ATTRIBUTE12
321 , ATTRIBUTE13
322 , ATTRIBUTE14
323 , ATTRIBUTE15
324 FROM SO_LINE_SERVICE_DETAILS
325 WHERE LINE_ID = P_Configuration_Parent_Line_Id
326 AND TRANSACTION_TYPE_ID = 2;
327
328 exception
329
330 when others then
331 P_Return_Status:=OE_FAILURE;
332 OE_MSG.Internal_Exception(Routine=>
333 'OE_OPT_PROCESS.Insert_Service_Details',
334 Operation=>'',
335 Object=>'OPTION',
336 Message=>' When Others');
337
338 end Insert_Installn_Details;
339
340 /* Select statement modified to get low,high quantities from
341 the BOM - 895383 (894316 in Rel 11) */
342
343 PROCEDURE Query_BOM_Quantity
344 (
345 P_Creation_Date_Time IN DATE,
346 P_Component_Sequence_Id IN NUMBER,
347 P_Component_Code IN VARCHAR2,
348 P_Model_Open_Quantity IN NUMBER,
349 P_Component_Quantity IN OUT NUMBER,
350 P_Low_Quantity IN OUT NUMBER,
351 P_High_Quantity IN OUT NUMBER,
352 P_Return_Status OUT VARCHAR2
353 )
354 is
355
356 begin
357
358 P_Return_Status:=OE_SUCCESS;
359
360 SELECT EXTENDED_QUANTITY / COMPONENT_QUANTITY *
364 INTO P_Component_Quantity
361 P_Model_Open_Quantity
362 , NVL( LOW_QUANTITY, 1 )
363 , HIGH_QUANTITY
365 , P_Low_quantity
366 , P_High_Quantity
367 FROM BOM_EXPLOSIONS
368 WHERE TOP_BILL_SEQUENCE_ID = P_Component_Sequence_Id
369 AND COMPONENT_CODE = P_Component_Code
370 AND EXPLOSION_TYPE = 'OPTIONAL'
371 AND PLAN_LEVEL > 0
372 AND EFFECTIVITY_DATE <=
373 NVL(P_Creation_Date_Time,
374 SYSDATE)
375 AND DISABLE_DATE >
376 NVL(P_Creation_Date_Time,
377 SYSDATE);
378
379
380
381 exception
382
383 when others then
384 P_Return_Status:=OE_FAILURE;
385 OE_MSG.Internal_Exception(Routine=>
386 'OE_OPT_PROCESS.Query_BOM_Quantity',
387 Operation=>'',
388 Object=>'OPTION',
389 Message=>' When Others');
390
391 end Query_BOM_Quantity;
392
393 PROCEDURE Set_Update_Subconfig_Flag
394 (
395 P_Row_Id IN VARCHAR2,
396 P_Ordered_Quantity IN NUMBER,
397 P_Update_Subconfig_Flag OUT VARCHAR2,
398 P_Return_Status OUT VARCHAR2
399 )
400 is
401
402 L_Dummy NUMBER;
403
404 begin
405
406 SELECT NULL INTO L_Dummy
407 FROM SO_LINES
408 WHERE ROWID = P_Row_Id
409 AND NVL( ORDERED_QUANTITY, -1 )
410 <> NVL( P_Ordered_Quantity, -1 );
411
412 P_Update_Subconfig_Flag:='Y';
413
414 exception
415
416 when no_data_found then
417
418 P_Update_Subconfig_Flag:='N';
419
420 when others then
421
422 P_Return_Status:=OE_FAILURE;
423 OE_MSG.Internal_Exception(Routine=>
424 'OE_OPT_PROCESS.Set_Update_Subconfig_Flag',
425 Operation=>'',
426 Object=>'OPTION',
427 Message=>' When Others');
428
429 end Set_Update_Subconfig_Flag;
430
431
432 end OE_OPT_PROCESS;