· 4 years ago · Jul 13, 2021, 11:12 AM
1set serveroutput on
2DECLARE
3
4 l_retcode varchar2(2000);
5 l_errbuf varchar2(2000);
6 l_organization_id number;
7 l_request_id number;
8 l_retflag boolean;
9 l_phase varchar2(80) := '';
10 l_status varchar2(80) := '';
11 l_dev_phase varchar2(80) := '';
12 l_dev_status varchar2(80) := '';
13 l_message varchar2(2000):= '';
14 l_module varchar2(4000);
15 l_batch_id number := 10632;
16
17
18
19
20 l_user_name varchar2(30) := 'MLXBATCH';
21 l_resp_name varchar2(30) := 'MLX_SFM_SUPERUSER';
22
23 l_user_id fnd_user.user_id%type;
24 l_resp_id fnd_responsibility.responsibility_id%type;
25 l_appl_id fnd_application.application_id%type;
26 l_appl_short_name fnd_application_vl.application_short_name%type;
27
28
29
30 l_table_exists NUMBER :=0;
31 l_bom_count NUMBER :=0;
32 l_transaction_type VARCHAR2(32);
33 l_supply_locator NUMBER;
34 l_effectivity_date DATE;
35 l_current_item VARCHAR2(32);
36 l_current_org NUMBER;
37
38 l_operation_seq_num NUMBER;
39 l_item_num NUMBER;
40
41
42 type item_bulk_rec is record (assembly_item_id mtl_system_items.inventory_item_id%type,
43 assembly_item_number mtl_system_items.segment1%type,
44 organization_id org_organization_definitions.organization_id%type,
45 organization_code org_organization_definitions.organization_code%type,
46 component_item_id mtl_system_items.inventory_item_id%type,
47 component_item_number mtl_system_items.segment1%type,
48 supply_type VARCHAR(32),
49 subinventory VARCHAR(32),
50 usage NUMBER);
51
52
53 type item_bulk_tab is table of item_bulk_rec;
54 item_bulk item_bulk_tab;
55
56 BEGIN
57 ----------------------------------------------------------------------------------------------------------------------
58
59 xxmlx_fnd_tools_pkg.init_apps_session('MLXBATCH', 'MLX_SFM_SUPERUSER');
60------------------------------------------Create temp table-----------------------------------------------------------
61 select count(*) into l_table_exists from dba_tables where table_name = 'xxmlx_bom_upld_temp';
62 if l_table_exists = 1
63 then
64 execute immediate 'DROP TABLE xxmlx_bom_upld_temp';
65 end if;
66 execute immediate 'CREATE TABLE xxmlx_bom_upld_temp
67 (
68 assembly_item VARCHAR2(16),
69 organization_code VARCHAR2(8),
70 component_item VARCHAR(16),
71 usage NUMBER,
72 supply_type VARCHAR(64),
73 subinventory VARCHAR(32),
74 locator VARCHAR(32)
75 )
76 organization external
77 (
78 type oracle_loader DEFAULT directory UPLOADS access parameters(records delimited BY newline skip 1 fields terminated BY '','' optionally enclosed by ''"'' missing field VALUES are NULL) location(''Phantom_BOMs.csv'')
79 )';
80-----------------------------------------------------------------------------------------------------------------------
81
82--------------------------------------------BULK INTO THE TABLE--------------------------------------------------------
83
84 execute immediate 'SELECT msiprime.inventory_item_id as assembly_item_id,
85 msiprime.segment1 as assembly_item_number,
86 oodprime.organization_id as organization_id,
87 oodprime.organization_code as organization_code,
88 msisec.inventory_item_id as component_item_id,
89 msisec.segment1 as component_item_number,
90 mls.lookup_code as supply_type,
91 x.subinventory as subinventory,
92 x.usage as usage
93 FROM
94 mtl_system_items msiprime,
95 mtl_system_items msisec,
96 org_organization_definitions oodprime,
97 org_organization_definitions oodsec,
98 xxmlx_bom_upld_temp x,
99 mfg_lookups mls
100 WHERE msiprime.segment1 = x.assembly_item
101 AND msiprime.organization_id = oodprime.organization_id
102 AND x.organization_code = oodprime.organization_code
103 AND msisec.segment1 = x.component_item
104 AND msisec.organization_id = oodsec.organization_id
105 AND x.organization_code = oodsec.organization_code
106 AND UPPER(mls.meaning) = UPPER(x.supply_type)
107 AND mls.lookup_type = ''WIP_SUPPLY''
108 ORDER BY msiprime.segment1,oodprime.organization_id' BULK COLLECT INTO item_bulk;
109-------------------------------------------------------------------------------------------------------------------------
110
111
112 delete from bom_bill_of_mtls_interface;
113 delete from bom_inventory_comps_interface;
114
115
116
117 FOR i IN 1..item_bulk.COUNT
118 LOOP
119
120---------------------------BOM EXISTS OR NO ----------------------
121 select count(*)
122 into l_bom_count
123 from BOM_BILL_OF_MATERIALS
124 where assembly_item_id = item_bulk(i).assembly_item_id;
125
126
127
128------------------------------------------------------------------
129
130
131 IF (l_bom_count > 0) THEN
132 l_transaction_type := 'UPDATE';
133 ELSE
134 l_transaction_type := 'CREATE';
135 END IF;
136---------------------------------- No locators in X-orgs, so we take -1 in that case---------------------------------
137 BEGIN
138 IF item_bulk(i).organization_code NOT LIKE 'X%' THEN
139 select distinct(supply_locator_id)
140 into l_supply_locator
141 from BOMFG_BOM_COMPONENTS
142 where supply_subinventory = item_bulk(i).subinventory
143 and supply_locator_id is not null
144 AND organization_code = item_bulk(i).organization_code;
145 ELSE
146
147 l_supply_locator := -1;
148
149 END IF;
150
151 EXCEPTION
152 WHEN no_data_found THEN
153 l_supply_locator := -1;
154 END;
155--------------------------------------take from external table and parse-----------The IF below checks for next item in the CSV, so it can reset the resource sequence in the BOMS
156begin
157
158 IF l_current_item IS NULL OR l_current_item != item_bulk(i).assembly_item_number
159 OR (l_current_item = item_bulk(i).assembly_item_number AND l_current_org != item_bulk(i).organization_id) THEN
160
161 l_current_item := item_bulk(i).assembly_item_number;
162 l_current_org := item_bulk(i).organization_id;
163
164
165 SELECT count(*)
166 INTO l_item_num
167 FROM apps.mtl_system_items msi1
168 , apps.mtl_system_items msi2
169 , apps.bom_bill_of_materials bom
170 , apps.bom_inventory_components_v bic
171 , apps.org_organization_definitions org
172 WHERE msi1.inventory_item_id = bom.assembly_item_id
173 AND msi1.organization_id = bom.organization_id
174 AND msi2.inventory_item_id = bic.component_item_id
175 AND msi2.organization_id = bom.organization_id
176 AND bom.bill_sequence_id = bic.bill_sequence_id
177 AND org.organization_id = msi1.organization_id
178 --AND msi1.inventory_item_status_code = 'Active'
179 AND bic.disable_date is null
180 AND msi1.segment1 = item_bulk(i).assembly_item_number
181 AND org.organization_id = item_bulk(i).organization_id
182 ORDER BY org.organization_name, msi1.segment1, bic.bill_sequence_id, bic.item_num;
183
184 end if;
185
186 EXCEPTION
187 WHEN no_data_found THEN
188 DBMS_OUTPUT.PUT_LINE(item_bulk(i).assembly_item_number);
189 DBMS_OUTPUT.PUT_LINE(item_bulk(i).organization_id);
190
191END;
192
193-------------------------------Take how many resources already exist in the BOM and start appending from the correct sequence----------------------------------------------------
194
195 BEGIN
196
197 SELECT boc.operation_seq_num
198 INTO l_operation_seq_num
199 FROM bom_inventory_components_v boc,
200 bom_bill_of_materials bom
201 WHERE bom.assembly_item_id = item_bulk(i).assembly_item_id
202 AND bom.bill_sequence_id = boc.bill_sequence_id
203 AND boc.disable_date IS NULL
204 AND rownum = 1
205 ORDER BY boc.creation_date;
206
207 EXCEPTION
208 WHEN no_data_found then
209 l_operation_seq_num := 10;
210 END;
211-----------------------------Create the BOM if it doesn't exist or Update it otherwise------------------------------------------------------
212
213
214
215 INSERT INTO bom_bill_of_mtls_interface
216 (
217 assembly_item_id,
218 process_flag,
219 item_number,
220 organization_id,
221 organization_code,
222 transaction_type,
223 batch_id
224 )
225 VALUES
226 (
227 item_bulk(i).assembly_item_id, --Assembly Item Inventory Item ID from MTL_SYSTEM_ITEMS
228 1,
229 item_bulk(i).assembly_item_number, --This is the assembly item number
230 item_bulk(i).organization_id, --Organization ID in which the BOM is to be created
231 item_bulk(i).organization_code,
232 l_transaction_type,
233 l_batch_id
234 );
235
236
237
238
239 --------------------------------------------------------Addding the components in the BOM--------------------------------------------------------------------------------
240
241 INSERT INTO bom_inventory_comps_interface
242 (
243 operation_seq_num,
244 item_num,
245 assembly_item_id,
246 assembly_item_number,
247 component_item_id,
248 component_item_number,
249 organization_code,
250 organization_id,
251 component_quantity,
252 process_flag,
253 transaction_type,
254 batch_id,
255 wip_supply_type,
256 supply_subinventory,
257 SUPPLY_LOCATOR_ID,
258 component_yield_factor
259 )
260 VALUES
261 (
262 l_operation_seq_num,
263 (l_item_num+1)*10,
264 item_bulk(i).assembly_item_id, --Again the assembly item ID value same as inserted in earlier statement
265 item_bulk(i).assembly_item_number,
266 item_bulk(i).component_item_id, --Inventory Item ID of the Component Item which comprises the BOM for the assembly
267 item_bulk(i).component_item_number,
268 item_bulk(i).organization_code, --Organization code in which BOM is to be created
269 item_bulk(i).organization_id, --Organization ID in which BOM is to be created
270 -- 4, --You can take this value from BOM_ITEM_TYPE in MTL_SYSTEM_ITEMS for the Component Item
271 item_bulk(i).usage,
272 1,
273 'CREATE',
274 l_batch_id,
275 item_bulk(i).supply_type,
276 item_bulk(i).subinventory,
277 l_supply_locator,
278 0.85
279 );
280
281 l_item_num := l_item_num+1;
282
283 END LOOP;
284
285
286
287 -- get user_id:
288 select usr.user_id
289 into l_user_id
290 from fnd_user usr
291 where usr.user_name = l_user_name;
292
293 -- get respo and application id:
294 select res.application_id
295 , res.responsibility_id
296 into l_appl_id
297 , l_resp_id
298 from fnd_responsibility_vl res
299 where res.responsibility_name = l_resp_name;
300
301 -- get application short name:
302 select fa.application_short_name
303 into l_appl_short_name
304 from fnd_application_vl fa
305 where fa.application_id = l_appl_id;
306
307 xxmlx_fnd_tools_pkg.init_apps_session('ATZ', 'MLX_SFM_SUPERUSER');
308
309 l_request_id := fnd_request.submit_request('BOM' -- APPLICATION SHORT NAME
310 , 'BMCOIN' -- CONCURRENT PROGRAM NAME
311 , '', '', FALSE -- NORMALLY THESE WILL NOT BE CHANGED
312 ,115
313 ,1
314 ,2
315 ,1
316 ,2
317 , l_batch_id
318 );
319 commit;
320
321 if L_REQUEST_ID = 0 then
322 raise_application_error(-20001,'ERROR:: Request not started');
323 end if;
324
325 l_retflag := Fnd_Concurrent.wait_for_request(request_id => l_request_id
326 , INTERVAL => 5
327 , max_wait => 300
328 , phase => l_phase
329 , status => l_status
330 , dev_phase => l_dev_phase
331 , dev_status => l_dev_status
332 , message => l_message
333 );
334
335 if l_retflag = true and l_status = 'Normal' then
336 dbms_output.put_line('Success!');
337 dbms_output.put_line(l_request_id);
338 else
339 raise_application_error(-20001,'ERROR:: Request_id: '|| l_request_id);
340 end if;
341
342
343 END;
344