[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_UPGRADE_SV
Source
1 PACKAGE BODY PO_ASL_UPGRADE_SV AS
2 /* $Header: POXA1LUB.pls 115.5 2003/03/27 21:18:11 draising ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: upgrade_autosource_rules
7
8 ===========================================================================*/
9
10 PROCEDURE upgrade_autosource_rules(
11 x_asl_status_id NUMBER,
12 x_usr_upgrade_docs VARCHAR2
13 ) IS
14 x_progress VARCHAR2(30) := '';
15 x_dummy_count NUMBER;
16 x_autosource_rule_id NUMBER;
17 x_autosource_rule_name po_autosource_rules.autosource_rule_name%type := '';
18 x_item_id NUMBER;
19 x_start_date DATE;
20 x_end_date DATE;
21 x_last_update_date DATE;
22 x_last_update_login NUMBER;
23 x_last_updated_by NUMBER;
24 x_created_by NUMBER;
25 x_creation_date DATE;
26 x_sourcing_rule_id NUMBER;
27 x_assignment_id NUMBER;
28 x_assignment_set_id NUMBER;
29 x_sr_receipt_id NUMBER;
30 x_organization_id NUMBER;
31 x_first_rule VARCHAR2(1) := '';
32 x_upgrade_docs VARCHAR2(1) := '';
33 X_ATTRIBUTE_CATEGORY po_autosource_vendors.attribute_category%type;
34 x_attribute1 po_autosource_vendors.attribute1%type;
35 x_attribute2 po_autosource_vendors.attribute2%type;
36 x_attribute3 po_autosource_vendors.attribute3%type;
37 x_attribute4 po_autosource_vendors.attribute4%type;
38 x_attribute5 po_autosource_vendors.attribute5%type;
39 x_attribute6 po_autosource_vendors.attribute6%type;
40 x_attribute7 po_autosource_vendors.attribute7%type;
41 x_attribute8 po_autosource_vendors.attribute8%type;
42 x_attribute9 po_autosource_vendors.attribute9%type;
43 x_attribute10 po_autosource_vendors.attribute10%type;
44 x_attribute11 po_autosource_vendors.attribute11%type;
45 x_attribute12 po_autosource_vendors.attribute12%type;
46 x_attribute13 po_autosource_vendors.attribute13%type;
47 x_attribute14 po_autosource_vendors.attribute14%type;
48 x_attribute15 po_autosource_vendors.attribute15%type;
49
50 CURSOR C2 is
51 SELECT DISTINCT ITEM_ID
52 FROM PO_AUTOSOURCE_RULES;
53
54 CURSOR C1 is
55 SELECT AUTOSOURCE_RULE_ID,
56 AUTOSOURCE_RULE_NAME,
57 START_DATE,
58 END_DATE,
59 LAST_UPDATE_DATE,
60 LAST_UPDATE_LOGIN,
61 LAST_UPDATED_BY,
62 CREATION_DATE,
63 CREATED_BY,
64 ATTRIBUTE_CATEGORY,
65 attribute1,
66 attribute2,
67 attribute3,
68 attribute4,
69 attribute5,
70 attribute6,
71 attribute7,
72 attribute8,
73 attribute9,
74 attribute10,
75 attribute11,
76 attribute12,
77 attribute13,
78 attribute14,
79 attribute15
80 FROM PO_AUTOSOURCE_RULES
81 WHERE ITEM_ID = x_item_id
82 ORDER BY start_date;
83
84 CURSOR I1 is
85 SELECT MRP_SOURCING_RULES_S.NEXTVAL
86 FROM SYS.DUAL;
87
88 CURSOR I2 is
89 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
90 FROM SYS.DUAL;
91
92 CURSOR I3 is
93 SELECT MRP_SR_ASSIGNMENTS_S.NEXTVAL
94 FROM SYS.DUAL;
95
96 BEGIN
97
98 -- Make sure that user provides a valid value for the input x_usr_upgrade_docs
99
100 IF x_usr_upgrade_docs NOT IN ('NONE', 'CURRENT', 'FUTURE') THEN
101
102 fnd_file.put_line(fnd_file.log,x_usr_upgrade_docs||' is not a valid parameter.');
103 return;
104
105 END IF;
106
107
108 -- The profile option MRP_DEFAULT_ASSIGNMENT_SET specifies the default
109 -- assignment set used for PO. If user has not set this profile option
110 -- then terminate the upgrade.
111
112 x_progress := 'SV0-040';
113 fnd_profile.get('MRP_DEFAULT_ASSIGNMENT_SET', x_assignment_set_id);
114
115 If x_assignment_set_id IS NULL THEN
116 fnd_file.put_line(fnd_file.log, '** ERROR: Please set the following site level profile option');
117 fnd_file.put_line(fnd_file.log, '** before proceeding with this upgrade: ');
118 fnd_file.put_line(fnd_file.log, '** MRP: Default Sourcing Assignment Set');
119 return;
120 END IF;
121
122
123 -- For each item in po_autosource_rules, determine whether a sourcing
124 -- rule has already been assigned to that item. If so, do not upgrade
125 -- the autosource rules for this item. Otherwise, define a new sourcing
126 -- rule for the item and assign it to the item.
127
128 OPEN C2;
129 LOOP
130
131 x_progress := '050';
132 FETCH C2 into x_item_id;
133 EXIT WHEN C2%NOTFOUND;
134
135 x_progress := '060';
136
137 /* Bug 1261392
138 To increase the perfomance, used the hint mrp_sr_assignments_n3
139 */
140
141 SELECT /*+ INDEX(MRP_SR_ASSIGNMENTS MRP_SR_ASSIGNMENTS_N3)*/
142 count(*)
143 INTO x_dummy_count
144 FROM mrp_sr_assignments
145 WHERE inventory_item_id = x_item_id
146 AND assignment_set_id = x_assignment_set_id
147 AND sourcing_rule_type = 1
148 AND assignment_type = 3;
149
150 IF x_dummy_count > 0 THEN
151
152 -- Another sourcing rule has already been assigned to this item
153 -- in the default PO assignment set. Do not upgrade autosource
154 -- rules for this item.
155 null;
156 fnd_file.put_line(fnd_file.log, 'A sourcing rule has already been assigned to ITEM_ID = '||x_item_id);
157
158 ELSE
159
160 -- Upgrade all autosource rules for the item, regardless of
161 -- effectivity. Use the name of the autosource rule with the earliest
162 -- effectivity dates as the name of our new sourcing rule.
163 -- X_first_rule is used to identify the earliest autosource rule.
164
165 x_first_rule := 'Y';
166
167 fnd_file.put_line(fnd_file.log, 'Upgrading autosource rules for ITEM_ID = '||x_item_id);
168
169 -- Select all autosource rules for this item.
170
171 OPEN C1;
172 LOOP
173
174 x_progress := '080';
175 FETCH C1 into x_autosource_rule_id,
176 x_autosource_rule_name,
177 x_start_date,
178 x_end_date,
179 x_last_update_date,
180 x_last_update_login,
181 x_last_updated_by,
182 x_creation_date,
183 x_created_by,
184 x_ATTRIBUTE_CATEGORY,
185 x_attribute1,
186 x_attribute2,
187 x_attribute3,
188 x_attribute4,
189 x_attribute5,
190 x_attribute6,
191 x_attribute7,
192 x_attribute8,
193 x_attribute9,
194 x_attribute10,
195 x_attribute11,
196 x_attribute12,
197 x_attribute13,
198 x_attribute14,
199 x_attribute15 ;
200
201 EXIT WHEN C1%NOTFOUND;
202
203 -- testing:
204 --x_created_by := 99999;
205
206 IF x_first_rule = 'Y' THEN
207
208 -- Get new sourcing_rule_id and create a new sourcing rule
209 -- for this item.
210
211 x_progress := '090';
212 OPEN I1;
213 FETCH I1 into x_sourcing_rule_id;
214 IF (I1%NOTFOUND) THEN
215 close I1;
216 fnd_file.put_line(fnd_file.log, '** Cannot get sourcing_rule_id');
217 raise NO_DATA_FOUND;
218 END IF;
219 CLOSE I1;
220
221 -- Insert record into mpr_sourcing_rules
222
223 x_progress := '100';
224 fnd_file.put_line(fnd_file.log, 'Creating sourcing rule. SOURCING_RULE_ID = '||x_sourcing_rule_id);
225 fnd_file.put_line(fnd_file.log, 'SOURCING_RULE_NAME = '||x_autosource_rule_name);
226
227 INSERT INTO MRP_SOURCING_RULES(
228 sourcing_rule_id,
229 sourcing_rule_name,
230 status,
231 sourcing_rule_type,
232 last_update_date,
233 last_updated_by,
234 creation_date,
235 created_by,
236 last_update_login,
237 planning_active,
238 ATTRIBUTE_CATEGORY,
239 attribute1,
240 attribute2,
241 attribute3,
242 attribute4,
243 attribute5,
244 attribute6,
245 attribute7,
246 attribute8,
247 attribute9,
248 attribute10,
249 attribute11,
250 attribute12,
251 attribute13,
252 attribute14,
253 attribute15
254 ) VALUES (
255 x_sourcing_rule_id,
256 x_autosource_rule_name,
257 1, -- status
258 1, -- sourcing_rule_type (1=SOURCING RULE)
259 x_last_update_date,
260 x_last_updated_by,
261 x_creation_date,
262 x_created_by,
263 x_last_update_login,
264 1, -- planning_active (1=ACTIVE)
265 x_ATTRIBUTE_CATEGORY,
266 x_attribute1,
267 x_attribute2,
268 x_attribute3,
269 x_attribute4,
270 x_attribute5,
271 x_attribute6,
272 x_attribute7,
273 x_attribute8,
274 x_attribute9,
275 x_attribute10,
276 x_attribute11,
277 x_attribute12,
278 x_attribute13,
279 x_attribute14,
280 x_attribute15
281 );
282
283 x_first_rule := 'N';
284
285 END IF;
286
287 -- Get new sr_receipt_id and insert into mrp_sr_receipt_org
288
289 OPEN I2;
290 FETCH I2 into x_sr_receipt_id;
291 IF (I2%NOTFOUND) THEN
292 close I2;
293 fnd_file.put_line(fnd_file.log, '** Cannot get sr_receipt_id');
294 raise NO_DATA_FOUND;
295 END IF;
296 CLOSE I2;
297
298 x_progress := '110';
299 fnd_file.put_line(fnd_file.log, 'Upgrading autosource rule. AUTOSOURCE_RULE_ID = '||x_autosource_rule_id);
300
301 INSERT INTO MRP_SR_RECEIPT_ORG(
302 sr_receipt_id,
303 sourcing_rule_id,
304 effective_date,
305 disable_date,
306 last_update_date,
307 last_updated_by,
308 creation_date,
309 created_by,
310 last_update_login
311 ) VALUES (
312 x_sr_receipt_id,
313 x_sourcing_rule_id,
314 x_start_date,
315 x_end_date,
316 x_last_update_date,
317 x_last_updated_by,
318 x_creation_date,
319 x_created_by,
320 x_last_update_login
321 );
322
323 -- x_usr_upgrade_docs is set by user to specify whether source
324 -- documents should be upgraded.
325 -- 'NONE' Do not upgrade source documents
326 -- 'CURRENT' Upgrade source documents only for currently
327 -- effective autosource rule
328 -- 'FUTURE' Upgrade source documents for all autosource
329 -- rules effective now and in the future
330
331 IF (x_usr_upgrade_docs = 'NONE') THEN
332 x_upgrade_docs := 'N';
333 ELSIF (x_usr_upgrade_docs = 'CURRENT') THEN
334 IF (x_start_date <= sysdate and x_end_date > sysdate) THEN
335 x_upgrade_docs := 'Y';
336 ELSE
337 x_upgrade_docs := 'N';
338 END IF;
339 ELSIF (x_usr_upgrade_docs = 'FUTURE') THEN
340 IF (x_end_date > sysdate) THEN
341 x_upgrade_docs := 'Y';
342 ELSE
343 x_upgrade_docs := 'N';
344 END IF;
345 END IF;
346
347 -- Get all vendors for this sourcing rule from po_autosource_vendors.
348 -- For each vendor, create a new record in mrp_sr_source_org.
349 -- Also create an ASL entry for the supplier-item relationship if
350 -- one does not already exist.
351
352 x_progress := '130';
353 po_asl_upgrade_sv2.upgrade_autosource_vendors(
354 x_sr_receipt_id,
355 x_autosource_rule_id,
356 x_item_id,
357 x_asl_status_id,
358 x_upgrade_docs,
359 x_usr_upgrade_docs);
360
361 END LOOP;
362 CLOSE C1;
363
364 -- Get assignment_id and assign the new sourcing rule to the
365 -- item. Assignment type is ITEM.
366
367 x_progress := '140';
368 OPEN I3;
369 FETCH I3 into x_assignment_id;
370 IF (I3%NOTFOUND) THEN
371 close I3;
372 fnd_file.put_line(fnd_file.log, '** Cannot get assignment id');
373 raise NO_DATA_FOUND;
374 END IF;
375 CLOSE I3;
376
377 -- Get organization_id
378
379 x_progress := 'SV1-145';
380
381 /*Bug 1776173
382 If one item is assigned to multiple inventory organizations and
383 each of those of have different master organizations then
384 to prevent the following sql from returning too many rows
385 added the join to financials system parameters which will ensure
386 that only row is returned.
387 */
388 begin
389 select mtl.organization_id
390 into x_organization_id
391 from mtl_system_items msi, mtl_parameters mtl,
392 financials_system_parameters fsp --1776173
393 where msi.inventory_item_id = x_item_id
394 and msi.organization_id = fsp.inventory_organization_id --1776173
395 and msi.organization_id = mtl.master_organization_id
396 and msi.organization_id = mtl.organization_id;
397 exception
398 when no_data_found then
399 select mtl.organization_id
400 into x_organization_id
401 from mtl_system_items msi, mtl_parameters mtl
402 where msi.inventory_item_id = x_item_id
403 and msi.organization_id = mtl.master_organization_id
404 and msi.organization_id = mtl.organization_id;
405 fnd_file.put_line(fnd_file.log, 'In the exception');
406 end;
407
408 x_progress := '150';
409 fnd_file.put_line(fnd_file.log, 'Assigning sourcing rule to item.');
410
411 INSERT INTO MRP_SR_ASSIGNMENTS(
412 assignment_id,
413 assignment_type,
414 sourcing_rule_id,
415 sourcing_rule_type,
416 assignment_set_id,
417 last_update_date,
418 last_updated_by,
419 creation_date,
420 created_by,
421 last_update_login,
422 organization_id,
423 inventory_item_id
424 ) VALUES (
425 x_assignment_id,
426 3, -- assignment_type (3=ITEM)
427 x_sourcing_rule_id,
428 1, -- sourcing_rule_type (1=SOURCING RULE)
429 x_assignment_set_id,
430 x_last_update_date,
431 x_last_updated_by,
432 x_creation_date,
433 x_created_by,
434 x_last_update_login,
435 x_organization_id,
436 x_item_id
437 );
438
439 END IF;
440
441 --dbms_output.put_line('=============================================================');
442
443 END LOOP;
444 CLOSE C2;
445
446 -- Set request_id in po_approved_supplier_list back to null
447
448 UPDATE po_approved_supplier_list
449 SET request_id = null
450 WHERE request_id = -99;
451
452 -- Commit the new records
453
454 COMMIT;
455
459 fnd_file.put_line(fnd_file.log, '** Exception in upgrade_autosource_rules');
456 EXCEPTION
457
458 WHEN OTHERS THEN
460 fnd_file.put_line(fnd_file.log, 'x_progress = '||x_progress);
461 PO_MESSAGE_S.SQL_ERROR('UPGRADE_SOURCING_RULES', x_progress, sqlcode);
462 END;
463
464 END PO_ASL_UPGRADE_SV;