[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_UPGRADE_SV2
Source
1 PACKAGE BODY PO_ASL_UPGRADE_SV2 AS
2 /* $Header: POXA2LUB.pls 120.2 2006/01/30 18:16:00 pthapliy noship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: upgrade_autosource_vendors
7
8 ===========================================================================*/
9
10 PROCEDURE upgrade_autosource_vendors(
11 x_sr_receipt_id NUMBER,
12 x_autosource_rule_id NUMBER,
13 x_item_id NUMBER,
14 x_asl_status_id NUMBER,
15 x_upgrade_docs VARCHAR2,
16 x_usr_upgrade_docs VARCHAR2
17 ) IS
18 x_progress VARCHAR2(3) := '';
19 x_vendor_id NUMBER;
20 x_vendor_rank NUMBER;
21 x_split NUMBER;
22 x_last_update_date DATE;
23 x_last_update_login NUMBER;
24 x_last_updated_by NUMBER;
25 x_created_by NUMBER;
26 x_creation_date DATE;
27 x_sr_source_id NUMBER;
28 x_asl_id NUMBER;
29 x_split_multiplier NUMBER;
30 x_add_percent VARCHAR2(1) := '';
31 x_ATTRIBUTE_CATEGORY po_autosource_vendors.attribute_category%type;
32 x_attribute1 po_autosource_vendors.attribute1%type;
33 x_attribute2 po_autosource_vendors.attribute2%type;
34 x_attribute3 po_autosource_vendors.attribute3%type;
35 x_attribute4 po_autosource_vendors.attribute4%type;
36 x_attribute5 po_autosource_vendors.attribute5%type;
37 x_attribute6 po_autosource_vendors.attribute6%type;
38 x_attribute7 po_autosource_vendors.attribute7%type;
39 x_attribute8 po_autosource_vendors.attribute8%type;
40 x_attribute9 po_autosource_vendors.attribute9%type;
41 x_attribute10 po_autosource_vendors.attribute10%type;
42 x_attribute11 po_autosource_vendors.attribute11%type;
43 x_attribute12 po_autosource_vendors.attribute12%type;
44 x_attribute13 po_autosource_vendors.attribute13%type;
45 x_attribute14 po_autosource_vendors.attribute14%type;
46 x_attribute15 po_autosource_vendors.attribute15%type;
47
48 CURSOR C is
49 SELECT VENDOR_ID,
50 VENDOR_RANK,
51 nvl(SPLIT,0),
52 LAST_UPDATE_DATE,
53 LAST_UPDATE_LOGIN,
54 LAST_UPDATED_BY,
55 CREATION_DATE,
56 CREATED_BY,
57 attribute_category,
58 attribute1,
59 attribute2,
60 attribute3,
61 attribute4,
62 attribute5,
63 attribute6,
64 attribute7,
65 attribute8,
66 attribute9,
67 attribute10,
68 attribute11,
69 attribute12,
70 attribute13,
71 attribute14,
72 attribute15
73 FROM PO_AUTOSOURCE_VENDORS
74 WHERE AUTOSOURCE_RULE_ID = x_autosource_rule_id;
75
76 CURSOR I is
77 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
78 FROM SYS.DUAL;
79
80 BEGIN
81
82 -- Determine whether we need to scale up the splits to sum to
83 -- 100%
84
85 po_asl_upgrade_sv3.get_split_multiplier(
86 x_autosource_rule_id,
87 x_split_multiplier,
88 x_add_percent);
89
90 -- Select all the vendors for this autosource rule.
91
92 OPEN C;
93 LOOP
94
95 x_progress := '020';
96 FETCH C into x_vendor_id,
97 x_vendor_rank,
98 x_split,
99 x_last_update_date,
100 x_last_update_login,
101 x_last_updated_by,
102 x_creation_date,
103 x_created_by,
104 x_attribute_category,
105 x_attribute1,
106 x_attribute2,
107 x_attribute3,
108 x_attribute4,
109 x_attribute5,
110 x_attribute6,
111 x_attribute7,
112 x_attribute8,
113 x_attribute9,
114 x_attribute10,
115 x_attribute11,
116 x_attribute12,
117 x_attribute13,
118 x_attribute14,
119 x_attribute15
120 ;
121
122 EXIT WHEN C%NOTFOUND;
123
124 --testing
125 --x_created_by := 99999;
126
127 x_progress := '020';
128 OPEN I;
129 FETCH I into x_sr_source_id;
130 IF (I%NOTFOUND) THEN
131 close I;
132 fnd_file.put_line(fnd_file.log, '** Cannot get sr_source_id');
133 raise NO_DATA_FOUND;
134 END IF;
135 CLOSE I;
136
137 -- Insert record into mpr_sr_source_org
138
139 x_progress := '030';
140 fnd_file.put_line(fnd_file.log,'Adding vendor to sourcing rule. VENDOR_ID = '||x_vendor_id);
141
142 INSERT INTO MRP_SR_SOURCE_ORG(
143 sr_source_id,
144 sr_receipt_id,
145 vendor_id,
146 source_type,
147 allocation_percent,
148 rank,
149 last_update_date,
150 last_updated_by,
151 creation_date,
152 created_by,
153 last_update_login,
154 attribute_category,
155 attribute1,
156 attribute2,
157 attribute3,
158 attribute4,
159 attribute5,
160 attribute6,
161 attribute7,
162 attribute8,
163 attribute9,
164 attribute10,
165 attribute11,
166 attribute12,
167 attribute13,
168 attribute14,
169 attribute15
170 ) VALUES (
171 x_sr_source_id,
172 x_sr_receipt_id,
173 x_vendor_id,
174 3, -- source_type
175 decode(x_split_multiplier, 1, x_split,
176 decode(x_add_percent, 'N', round(x_split*x_split_multiplier),
177 decode(x_vendor_rank, 1, round(x_split*x_split_multiplier)+1,
178 round(x_split*x_split_multiplier)))),
179 x_vendor_rank,
180 x_last_update_date,
181 x_last_updated_by,
182 x_creation_date,
183 x_created_by,
184 x_last_update_login,
185 x_attribute_category,
186 x_attribute1,
187 x_attribute2,
188 x_attribute3,
189 x_attribute4,
190 x_attribute5,
191 x_attribute6,
192 x_attribute7,
193 x_attribute8,
194 x_attribute9,
195 x_attribute10,
196 x_attribute11,
197 x_attribute12,
198 x_attribute13,
199 x_attribute14,
200 x_attribute15
201 );
202
203 -- Create new ASL entry for this supplier-item relationship.
204
205 x_progress := '040';
206 create_asl_entry(x_vendor_id,
207 x_item_id,
208 x_asl_status_id,
209 x_last_update_date,
210 x_last_update_login,
211 x_last_updated_by,
212 x_created_by,
213 x_creation_date,
214 x_usr_upgrade_docs,
215 x_asl_id);
216
217 IF (x_upgrade_docs = 'Y') THEN
218
219 x_progress := '050';
220 upgrade_asl_documents(x_autosource_rule_id,
221 x_vendor_id,
222 x_asl_id);
223 END IF;
224
225 END LOOP;
226 CLOSE C;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 fnd_file.put_line(fnd_file.log, '** Exception in upgrade_autosource_vendors');
231 fnd_file.put_line(fnd_file.log, 'x_progress = '||x_progress);
232 PO_MESSAGE_S.SQL_ERROR('UPGRADE_AUTOSOURCE_VENDORS', x_progress, sqlcode);
233 END;
234
235 /*===========================================================================
236
237 PROCEDURE NAME: create_asl_entry
238
239 ===========================================================================*/
240
241 PROCEDURE create_asl_entry(x_vendor_id NUMBER,
242 x_item_id NUMBER,
243 x_asl_status_id NUMBER,
244 x_last_update_date DATE,
245 x_last_update_login NUMBER,
246 x_last_updated_by NUMBER,
247 x_created_by NUMBER,
248 x_creation_date DATE,
249 x_usr_upgrade_docs VARCHAR2,
250 x_asl_id IN OUT NOCOPY NUMBER
251 ) IS
252 x_progress VARCHAR2(30) := '';
253 x_record_unique BOOLEAN;
254 x_owning_organization_id NUMBER;
255 x_purch_uom po_lines.unit_meas_lookup_code%type;
256 x_release_generation_method po_asl_attributes.release_generation_method%type := '';
257
258
259
260 CURSOR I2 is
261 SELECT PO_APPROVED_SUPPLIER_LIST_S.NEXTVAL
262 FROM SYS.DUAL;
263
264 BEGIN
265
266 -- Check whether the global supplier-item relationship has already been
267 -- defined.
268
269 x_progress := '010';
270 x_record_unique := po_asl_sv.check_record_unique(
271 NULL,
272 x_vendor_id,
273 NULL,
274 x_item_id,
275 NULL,
276 -1);
277
278 IF x_record_unique THEN
279
280 -- Get owning_organization_id from financials_system_parameters.
281
282 x_progress := '1451';
283
284 begin
285 select mtl.organization_id
286 into x_owning_organization_id
287 from mtl_system_items msi, mtl_parameters mtl,
288 financials_system_parameters fsp
289 where msi.inventory_item_id = x_item_id
290 and msi.organization_id = fsp.inventory_organization_id
291 and msi.organization_id = mtl.master_organization_id
292 and msi.organization_id = mtl.organization_id;
293 exception
294 when no_data_found then
295 select mtl.organization_id
296 into x_owning_organization_id
297 from mtl_system_items msi, mtl_parameters mtl
298 where msi.inventory_item_id = x_item_id
299 and msi.organization_id = mtl.master_organization_id
300 and msi.organization_id = mtl.organization_id;
301 end;
302
303
304 /* select mtl.organization_id
305 into x_owning_organization_id
306 from mtl_system_items msi, mtl_parameters mtl
307 where msi.inventory_item_id = x_item_id
308 and msi.organization_id = mtl.master_organization_id
309 and msi.organization_id = mtl.organization_id;
310 */
311 -- SELECT inventory_organization_id
312 -- INTO x_owning_organization_id
313 -- FROM financials_system_params_all
314 -- WHERE rownum < 2;
315
316 -- The reason we are doing the above is because we do not do
317 -- anything with the owning org hence any owning org is fine.(R11)
318
319 -- If supplier-item relationship has not yet been created,
320 -- insert new record in po_approved_supplier_list.
321 -- Temporarily set request_id to -99 so that we can identify
322 -- the ASL records created in this upgrade.
323
324 x_progress := '020';
325 OPEN I2;
326 FETCH I2 into x_asl_id;
327 IF (I2%NOTFOUND) THEN
328 close I2;
329 fnd_file.put_line(fnd_file.log, '** Cannot get asl_id');
330 raise NO_DATA_FOUND;
331 END IF;
332 CLOSE I2;
333
334 x_progress := '030';
335 fnd_file.put_line(fnd_file.log, 'x_item_id '||to_char(x_item_id));
336 fnd_file.put_line(fnd_file.log, 'x_asl_id '||to_char(x_asl_id));
337 fnd_file.put_line(fnd_file.log, 'x_vendor_id '||to_char(x_vendor_id));
338 fnd_file.put_line(fnd_file.log, 'x_asl_status_id '||to_char(x_asl_status_id));
339 fnd_file.put_line(fnd_file.log, 'x_creation_date '||to_char(x_creation_date));
340 fnd_file.put_line(fnd_file.log, 'x_last_update_date '||to_char(x_last_update_date));
341 fnd_file.put_line(fnd_file.log, 'owning_org '||to_char(x_owning_organization_id));
342 fnd_file.put_line(fnd_file.log, 'update by '|| to_char(x_last_updated_by) );
343 fnd_file.put_line(fnd_file.log, 'created by '||to_char(x_created_by) );
344
345 INSERT INTO PO_APPROVED_SUPPLIER_LIST(
346 asl_id ,
347 using_organization_id ,
348 owning_organization_id ,
349 vendor_business_type ,
350 asl_status_id ,
351 last_update_date ,
352 last_updated_by ,
353 creation_date ,
354 created_by ,
355 vendor_id ,
356 item_id ,
357 last_update_login ,
358 request_id
359 ) VALUES (
360 x_asl_id ,
361 -1,
362 x_owning_organization_id, -- ??x_owning_organization_id
363 'DIRECT',
364 x_asl_status_id ,
365 x_last_update_date ,
366 x_last_updated_by ,
367 x_creation_date ,
368 x_created_by ,
369 x_vendor_id ,
370 x_item_id ,
371 x_last_update_login ,
372 -99
373 );
377 --using_organization_id: use 2 for ap349db1, 1 for ap309db1
374
375
376 --testing
378 -- use 23 for systest
379
380 -- IF upgrading documents, get the release generation
381 -- method for this asl entry.
382
383 IF (x_usr_upgrade_docs IN ('CURRENT', 'FUTURE')) THEN
384
385 BEGIN
386
387 -- Get release generation method from top ranked blanket
388 -- in currently effective autosource rule. Check that
389 -- the document is effective.
390
391 SELECT doc_generation_method
392 INTO x_release_generation_method
393 FROM po_autosource_documents_all pad,
394 po_autosource_rules par
395 WHERE pad.autosource_rule_id = par.autosource_rule_id
396 AND par.item_id = x_item_id
397 AND pad.vendor_id = x_vendor_id
398 AND par.start_date <= sysdate
399 AND par.end_date > sysdate
400 AND rownum < 2
401 AND pad.sequence_num =
402 (SELECT min(sequence_num)
403 FROM po_autosource_documents_all pad2,
404 po_headers_all poh
405 WHERE pad2.autosource_rule_id = pad.autosource_rule_id
406 AND pad2.vendor_id = x_vendor_id
407 AND pad2.document_type_code = 'BLANKET'
408 AND pad2.document_header_id = poh.po_header_id
409 AND nvl(poh.start_date, sysdate-1) <= sysdate
410 AND nvl(poh.end_date, sysdate+1) > sysdate);
411
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414
415 IF (x_usr_upgrade_docs = 'FUTURE') THEN
416
417 BEGIN
418
419 -- Get release generation method from top ranked blanket
420 -- in the first future autosource rule that contains a
421 -- blanket.
422
423 SELECT pad.doc_generation_method
424 INTO x_release_generation_method
425 FROM po_autosource_rules par,
426 po_autosource_documents_all pad
427 WHERE par.item_id = x_item_id
428 AND pad.vendor_id = x_vendor_id
429 AND rownum < 2
430 AND par.autosource_rule_id = pad.autosource_rule_id
431 AND par.start_date =
432 (SELECT min(par3.start_date)
433 FROM po_autosource_documents_all pad3,
434 po_autosource_rules par3,
435 po_headers_all poh3
436 WHERE par3.autosource_rule_id = pad3.autosource_rule_id
437 AND pad3.vendor_id = x_vendor_id
438 AND par3.item_id = x_item_id
439 AND pad3.document_header_id = poh3.po_header_id
440 AND nvl(poh3.start_date, sysdate-1) <= sysdate
441 AND nvl(poh3.end_date, sysdate+1) > sysdate
442 AND par3.start_date > sysdate
443 AND pad3.document_type_code = 'BLANKET')
444 AND pad.sequence_num =
445 (SELECT min(sequence_num)
446 FROM po_autosource_documents_all pad2,
447 po_headers_all poh2
448 WHERE pad2.autosource_rule_id = pad.autosource_rule_id
449 AND pad2.vendor_id = x_vendor_id
450 AND pad2.document_type_code = 'BLANKET'
451 AND pad2.document_header_id = poh2.po_header_id
452 AND nvl(poh2.start_date, sysdate-1) <= sysdate
453 AND nvl(poh2.end_date, sysdate+1) > sysdate);
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 NULL;
458 END;
459
460 END IF;
461 END;
462 END IF;
463
464 -- Create an attributes record for this ASL entry.
465
466 x_progress := '040';
467
468 INSERT INTO po_asl_attributes(
469 asl_id,
470 using_organization_id,
471 last_update_date,
472 last_updated_by,
473 creation_date,
474 created_by,
475 last_update_login,
476 document_sourcing_method,
477 release_generation_method,
478 enable_plan_schedule_flag,
479 enable_ship_schedule_flag,
480 enable_autoschedule_flag,
481 enable_authorizations_flag,
482 vendor_id,
483 item_id
484 ) VALUES (
485 x_asl_id,
486 -1,
487 x_last_update_date,
488 x_last_updated_by,
489 x_creation_date,
493 x_release_generation_method,
490 x_created_by,
491 x_last_update_login,
492 'ASL',
494 'N',
495 'N',
496 'N',
497 'N',
498 x_vendor_id,
499 x_item_id
500 );
501
502 ELSE
503
504 -- If supplier-item relationship already exists, return
505 -- asl_id for this asl entry.
506
507 x_progress := '050';
508 SELECT asl_id
509 INTO x_asl_id
510 FROM po_approved_supplier_list pasl
511 WHERE pasl.vendor_id = x_vendor_id
512 AND pasl.item_id = x_item_id
513 AND using_organization_id = -1;
514
515 fnd_file.put_line(fnd_file.log, 'ASL entry already exists.');
516
517 END IF;
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 fnd_file.put_line(fnd_file.log, '** Exception in create_asl_entry');
522 fnd_file.put_line(fnd_file.log, 'x_progress = '||x_progress);
523 fnd_file.put_line(fnd_file.log,'CREATE_ASL_ENTRY'|| sqlcode);
524 PO_MESSAGE_S.SQL_ERROR('CREATE_ASL_ENTRY', x_progress, sqlcode);
525 END;
526 /*===========================================================================
527
528 PROCEDURE NAME: upgrade_asl_documents
529
530 ===========================================================================*/
531
532 PROCEDURE upgrade_asl_documents(
533 x_autosource_rule_id NUMBER,
534 x_vendor_id NUMBER,
535 x_asl_id NUMBER
536 ) IS
537 x_progress VARCHAR2(3) := '';
538 x_dummy_count NUMBER;
539 x_sequence_num NUMBER;
540 x_document_header_id NUMBER;
541 x_document_line_id NUMBER;
542 x_org_id NUMBER;
543 x_last_update_date DATE;
544 x_last_update_login NUMBER;
545 x_last_updated_by NUMBER;
546 x_created_by NUMBER;
547 x_creation_date DATE;
548 x_request_id NUMBER;
549 x_document_type_code po_autosource_documents.document_type_code%type;
550 x_doc_generation_method po_autosource_documents.doc_generation_method%type;
551
552 CURSOR C IS
553 SELECT pad.document_type_code,
554 pad.document_header_id,
555 pad.document_line_id,
556 pad.last_update_date,
557 pad.last_updated_by,
558 pad.last_update_login,
559 pad.creation_date,
560 pad.created_by,
561 pad.doc_generation_method,
562 pad.org_id
563 FROM PO_AUTOSOURCE_DOCUMENTS_all pad,
564 PO_HEADERS_all poh
565 WHERE pad.autosource_rule_id = x_autosource_rule_id
566 AND pad.vendor_id = x_vendor_id
567 AND poh.po_header_id = pad.document_header_id
568 AND sysdate >= nvl(poh.start_date, sysdate-1)
569 AND sysdate < nvl(poh.end_date, sysdate+1)
570 ORDER BY pad.sequence_num ;
571
572 BEGIN
573
574 -- If ASL entry was not created in this upgrade, do not add source
575 -- documents to it.
576
577 SELECT request_id
578 INTO x_request_id
579 FROM po_approved_supplier_list
580 WHERE asl_id = x_asl_id;
581
582 IF (x_request_id IS NULL OR x_request_id <> -99) THEN
583 null;
584 fnd_file.put_line(fnd_file.log, 'Not adding source documents to existing ASL entry.');
585 return;
586 END IF;
587
588 OPEN C;
589 LOOP
590 x_progress := '010';
591 FETCH C into x_document_type_code,
592 x_document_header_id,
593 x_document_line_id,
594 x_last_update_date,
595 x_last_updated_by,
596 x_last_update_login,
597 x_creation_date,
598 x_created_by,
599 x_doc_generation_method,
600 x_org_id;
601
602 EXIT WHEN C%NOTFOUND;
603
604 -- Make sure that this source document does not already exist for
605 -- this ASL entry.
606
607 x_progress := '020';
608 SELECT count(*)
609 INTO x_dummy_count
610 FROM po_asl_documents
611 WHERE asl_id = x_asl_id
612 AND using_organization_id = -1
613 AND document_header_id = x_document_header_id
614 AND document_type_code = x_document_type_code;
615
616 IF x_dummy_count > 0 THEN
617
618 null;
619 fnd_file.put_line(fnd_file.log, 'Source document already exists for this this ASL entry.');
620 ELSE
621
622 -- Sequence number for this document is one above the highest
623 -- sequence number.
624
625 x_progress := '030';
626 SELECT nvl(max(sequence_num)+1, 1)
627 INTO x_sequence_num
628 FROM po_asl_documents
629 WHERE asl_id = x_asl_id
630 AND using_organization_id = -1;
631
632
633 x_progress := '040';
634 fnd_file.put_line(fnd_file.log, 'Upgrading source document.');
635 fnd_file.put_line(fnd_file.log, 'DOCUMENT_TYPE_CODE = '||x_document_type_code);
636 fnd_file.put_line(fnd_file.log, 'DOCUMENT_ID = '||x_document_header_id);
637
638 INSERT INTO PO_ASL_DOCUMENTS(
639 asl_id,
640 using_organization_id,
641 sequence_num,
642 document_type_code,
643 document_header_id,
644 document_line_id,
645 last_update_date,
646 last_updated_by,
647 last_update_login,
648 creation_date,
649 created_by,
650 org_id
651 ) VALUES (
652 x_asl_id,
653 -1,
654 x_sequence_num,
655 x_document_type_code,
656 x_document_header_id,
657 x_document_line_id,
658 x_last_update_date,
659 x_last_updated_by,
660 x_last_update_login,
661 x_creation_date,
662 x_created_by,
663 x_org_id
664 );
665
666 /*
667 -- If the ASL entry was created in the upgrade process, then get
668 -- the release generation method from the top-ranked source document.
669
670 IF (x_new_asl AND x_sequence_num = 1
671 AND x_doc_generation_method IS NOT NULL) THEN
672
673 x_progress := '050';
674 UPDATE po_asl_attributes
675 SET release_generation_method = x_doc_generation_method,
676 last_updated_by = x_last_updated_by,
677 last_update_date = x_last_update_date,
678 last_update_login = x_last_update_login
679 WHERE asl_id = x_asl_id
680 AND using_organization_id = -1;
681
682 END IF;
683 */
684 END IF;
685
686 END LOOP;
687 CLOSE C;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 fnd_file.put_line(fnd_file.log, '** Exception in upgrade_asl_documents');
692 fnd_file.put_line(fnd_file.log, 'x_progress = '||x_progress);
693 PO_MESSAGE_S.SQL_ERROR('UPGRADE_ASL_DOCUMENTS', x_progress, sqlcode);
694 END;
695
696
697
698 END PO_ASL_UPGRADE_SV2;