· 5 years ago · Jul 30, 2020, 08:04 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(''Export_50.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 IF item_bulk(i).organization_code NOT LIKE 'X%' THEN
138 select distinct(supply_locator_id)
139 into l_supply_locator
140 from BOMFG_BOM_COMPONENTS
141 where supply_subinventory = item_bulk(i).subinventory
142 and supply_locator_id is not null
143 AND organization_code = item_bulk(i).organization_code;
144 ELSE
145
146 l_supply_locator := -1;
147
148 END IF;
149--------------------------------------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
150begin
151
152 IF l_current_item IS NULL OR l_current_item != item_bulk(i).assembly_item_number
153 OR (l_current_item = item_bulk(i).assembly_item_number AND l_current_org != item_bulk(i).organization_id) THEN
154
155 l_current_item := item_bulk(i).assembly_item_number;
156 l_current_org := item_bulk(i).organization_id;
157
158
159 SELECT count(*)
160 INTO l_item_num
161 FROM apps.mtl_system_items msi1
162 , apps.mtl_system_items msi2
163 , apps.bom_bill_of_materials bom
164 , apps.bom_inventory_components_v bic
165 , apps.org_organization_definitions org
166 WHERE msi1.inventory_item_id = bom.assembly_item_id
167 AND msi1.organization_id = bom.organization_id
168 AND msi2.inventory_item_id = bic.component_item_id
169 AND msi2.organization_id = bom.organization_id
170 AND bom.bill_sequence_id = bic.bill_sequence_id
171 AND org.organization_id = msi1.organization_id
172 --AND msi1.inventory_item_status_code = 'Active'
173 AND bic.disable_date is null
174 AND msi1.segment1 = item_bulk(i).assembly_item_number
175 AND org.organization_id = item_bulk(i).organization_id
176 ORDER BY org.organization_name, msi1.segment1, bic.bill_sequence_id, bic.item_num;
177
178 end if;
179
180 EXCEPTION
181 WHEN no_data_found THEN
182 DBMS_OUTPUT.PUT_LINE(item_bulk(i).assembly_item_number);
183 DBMS_OUTPUT.PUT_LINE(item_bulk(i).organization_id);
184
185END;
186
187-------------------------------Take how many resources already exist in the BOM and start appending from the correct sequence----------------------------------------------------
188
189 BEGIN
190
191 SELECT boc.operation_seq_num
192 INTO l_operation_seq_num
193 FROM bom_inventory_components_v boc,
194 bom_bill_of_materials bom
195 WHERE bom.assembly_item_id = item_bulk(i).assembly_item_id
196 AND bom.bill_sequence_id = boc.bill_sequence_id
197 AND boc.disable_date IS NULL
198 AND rownum = 1
199 ORDER BY boc.creation_date;
200
201 EXCEPTION
202 WHEN no_data_found then
203 l_operation_seq_num := 10;
204 END;
205-----------------------------Create the BOM if it doesn't exist or Update it otherwise------------------------------------------------------
206
207
208
209 INSERT INTO bom_bill_of_mtls_interface
210 (
211 assembly_item_id,
212 process_flag,
213 item_number,
214 organization_id,
215 organization_code,
216 transaction_type,
217 batch_id
218 )
219 VALUES
220 (
221 item_bulk(i).assembly_item_id, --Assembly Item Inventory Item ID from MTL_SYSTEM_ITEMS
222 1,
223 item_bulk(i).assembly_item_number, --This is the assembly item number
224 item_bulk(i).organization_id, --Organization ID in which the BOM is to be created
225 item_bulk(i).organization_code,
226 l_transaction_type,
227 l_batch_id
228 );
229
230
231
232
233 --------------------------------------------------------Addding the components in the BOM--------------------------------------------------------------------------------
234
235 INSERT INTO bom_inventory_comps_interface
236 (
237 operation_seq_num,
238 item_num,
239 assembly_item_id,
240 assembly_item_number,
241 component_item_id,
242 component_item_number,
243 organization_code,
244 organization_id,
245 component_quantity,
246 process_flag,
247 transaction_type,
248 batch_id,
249 wip_supply_type,
250 supply_subinventory,
251 SUPPLY_LOCATOR_ID
252 )
253 VALUES
254 (
255 l_operation_seq_num,
256 (l_item_num+1)*10,
257 item_bulk(i).assembly_item_id, --Again the assembly item ID value same as inserted in earlier statement
258 item_bulk(i).assembly_item_number,
259 item_bulk(i).component_item_id, --Inventory Item ID of the Component Item which comprises the BOM for the assembly
260 item_bulk(i).component_item_number,
261 item_bulk(i).organization_code, --Organization code in which BOM is to be created
262 item_bulk(i).organization_id, --Organization ID in which BOM is to be created
263 -- 4, --You can take this value from BOM_ITEM_TYPE in MTL_SYSTEM_ITEMS for the Component Item
264 item_bulk(i).usage,
265 1,
266 'CREATE',
267 l_batch_id,
268 item_bulk(i).supply_type,
269 item_bulk(i).subinventory,
270 l_supply_locator
271 );
272
273 l_item_num := l_item_num+1;
274
275 END LOOP;
276
277
278
279 -- get user_id:
280 select usr.user_id
281 into l_user_id
282 from fnd_user usr
283 where usr.user_name = l_user_name;
284
285 -- get respo and application id:
286 select res.application_id
287 , res.responsibility_id
288 into l_appl_id
289 , l_resp_id
290 from fnd_responsibility_vl res
291 where res.responsibility_name = l_resp_name;
292
293 -- get application short name:
294 select fa.application_short_name
295 into l_appl_short_name
296 from fnd_application_vl fa
297 where fa.application_id = l_appl_id;
298
299 fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
300
301 l_request_id := fnd_request.submit_request('BOM' -- APPLICATION SHORT NAME
302 , 'BMCOIN' -- CONCURRENT PROGRAM NAME
303 , '', '', FALSE -- NORMALLY THESE WILL NOT BE CHANGED
304 ,115
305 ,1
306 ,2
307 ,1
308 ,2
309 , l_batch_id
310 );
311 commit;
312
313 if L_REQUEST_ID = 0 then
314 raise_application_error(-20001,'ERROR:: Request not started');
315 end if;
316
317 l_retflag := Fnd_Concurrent.wait_for_request(request_id => l_request_id
318 , INTERVAL => 5
319 , max_wait => 300
320 , phase => l_phase
321 , status => l_status
322 , dev_phase => l_dev_phase
323 , dev_status => l_dev_status
324 , message => l_message
325 );
326
327 if l_retflag = true and l_status = 'Normal' then
328 dbms_output.put_line('Success!');
329 dbms_output.put_line(l_request_id);
330 else
331 raise_application_error(-20001,'ERROR:: Request_id: '|| l_request_id);
332 end if;
333
334
335 END create_or_update_bom;
336
337 ----- Supporting queries to clear interface tables, check for processed rows, etc.
338
339 select * from xxmlx_bom_upld_temp
340
341 DROP TABLE xxmlx_bom_upld_temp;
342 commit;
343
344
345 select * from bom_inventory_comps_interface
346 select * from bom_bill_of_mtls_interface
347
348
349 DELETE FROM bom_inventory_comps_interface;
350 DELETE FROM bom_bill_of_mtls_interface;
351 DELETE FROM BOM_INTERFACE_DELETE_GROUPS;
352 DELETE FROM MTL_INTERFACE_ERRORS;
353
354