1 PACKAGE BODY PO_SOURCING_RULES_SV AS
2 /*$Header: POXPISRB.pls 120.3 2006/03/13 04:27:33 asista noship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
6
7 --<Shared Proc FPJ START>
8 -------------------------------------------------------------------------------
9 --Start of Comments
10 --Name: create_update_sourcing_rules
11 --Pre-reqs:
12 -- None
13 --Modifies:
14 -- This API inserts row into mrp_sr_assignments, mrp_sourcing_rules,
15 -- mrp_sr_receipt_org, mrp_sr_source_org depending on the create or update
16 -- flag from the approval flag.
17 --Locks:
18 -- None.
19 --Function:
20 -- This API creates Sourcing Rules. The validations of start_date,end_date
21 -- not being null and the approved_status being approved are done here and
22 -- then create_sourcing_rule is called. If p_create_update_code is UPDATE
23 -- then update_sourcing_rule is called.
24 --Parameters:
25 --IN:
26 --p_interface_header_id
27 -- sequence generated unique identifier of interface headers table. Used for
28 -- insertion into po_interface_errors
29 --p_interface_line_id
30 -- sequence generated unique identifier of interface line table. Used for
31 -- insertion into po_interface_errors
32 --p_item_id
33 -- unique identifier of item of the document for which the sourcing
34 -- rule is created or updated.
35 --p_vendor_product_num
36 -- supplier provided product number
37 --p_vendor_id
38 -- unique identifier of vendor of the document for which the sourcing
39 -- rule is created or updated
40 --p_po_header_id
41 -- unique identifier of the document for which the sourcing
42 -- rule is created or updated
43 --p_po_line_id
44 -- unique identifier of the document for which the sourcing
45 -- rule is created or updated
46 --p_document_type
47 -- The type of document being created. Should be Blanket/GA
48 --p_approval_status
49 -- The approval status of the document for which the sourcing
50 -- rule is created or updated
51 --p_rule_name
52 -- The name of Sourcing Rule being created/updated. Can be NULL.
53 --p_rule_name_prefix
54 -- Prefix that will be used to create a name for new sourcing rule.
55 -- The name is p_rule_name_prefix_<SR Sequence number);
56 --p_start_date
57 -- The start date of Sourcing Rule
58 --p_end_date
59 -- The disable date of Sourcing Rule
60 --p_create_update_code
61 -- Valid values are CREATE/CREATE_UPDATE
62 --p_organization_id
63 -- organization_id to be inserted in mrp_sr_assignments
64 --p_assignment_type_id
65 -- Type of Assignment.
66 --p_po_interface_error_code
67 -- This is the code used to populate interface_type field in po_interface_errors table.
68 --IN OUT:
69 --x_header_processable_flag
70 -- Running parameter which decides whether to do further processing or error out
71 -- Value is set to N if there was any error encountered. This is set in the procedure
72 -- PO_INTERFACE_ERRORS_SV1.handle_interface_errors
73
74 --<LOCAL SR/ASL PROJECT 11i11 START>
75 --p_assigment_set_id
76 -- Specifies the assignment set to which the created sourcing rule
77 -- should be assigned.
78 --p_vendor_site_id
79 -- Specifies the Supplier Site Id Enabled corresponding to the owining
80 -- org/Purchasing Org
81 --<LOCAL SR/ASL PROJECT 11i11 END>
82 --OUT:
83 --x_return_status
84 -- Standard API parameter. Returns status of API call.
85 -- Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR,
86 -- FND_API.G_RET_STS_UNEXP_ERROR
87 --Notes:
88 -- This procedure is now called from both Approval Window and PDOI
89 --Testing:
90 -- None
91 --End of Comments
92 -------------------------------------------------------------------------------
93
94 /*
95 Assignment Type => Assignment Type ID Mapping
96
97 Assignment Type Assignment Type Id
98 --------------------------------------------
99 --------------------------------------------
100 Global => 1
101 Item => 3
102 Organization => 4
103 Category-Org => 5
104 Item-Organization => 6
105
106 */
107
108 PROCEDURE create_update_sourcing_rule (
109 p_interface_header_id IN NUMBER,
110 p_interface_line_id IN NUMBER,
111 p_item_id IN NUMBER,
112 p_vendor_id IN NUMBER,
113 p_po_header_id IN NUMBER,
114 p_po_line_id IN NUMBER,
115 p_document_type IN VARCHAR2,
116 p_approval_status IN VARCHAR2,
117 p_rule_name IN VARCHAR2,
118 p_rule_name_prefix IN VARCHAR2,
119 p_start_date IN DATE,
120 p_end_date IN DATE,
121 p_create_update_code IN VARCHAR2,
122 p_organization_id IN NUMBER,
123 p_assignment_type_id IN NUMBER,
124 p_po_interface_error_code IN VARCHAR2,
125 x_header_processable_flag IN OUT NOCOPY VARCHAR2,
126 x_return_status OUT NOCOPY VARCHAR2,
127 ----<LOCAL SR/ASL PROJECT 11i11 START>
128 p_assignment_set_id IN NUMBER DEFAULT NULL,
129 p_vendor_site_id IN NUMBER DEFAULT NULL
130 ----<LOCAL SR/ASL PROJECT 11i11 END>
131 ) IS
132
133 X_process_flag varchar2(1) := 'Y';
134 X_progress VARCHAR2(3) := NULL;
135 x_assignment_set_id number := null;
136 X_temp_sourcing_rule_id number := null;
137 x_sourcing_rule_id number := null;
138 --Variable to keep track of return statuses of private procedures called
139 l_running_status VARCHAR2(1); --<Shared Proc FPJ>
140
141 begin
142
143 --Setting of this flag is now done before the call to the procedure.
144 --X_header_processable_flag := 'Y'; --<Shared Proc FPJ>
145 IF (g_po_pdoi_write_to_file = 'Y') THEN
146 PO_DEBUG.put_line(' Creating sourcing rule for the item ...');
147 END IF;
148
149
150 ----<LOCAL SR/ASL PROJECT 11i11 START>
151 /*
152 If the calling program is POASLGEN the value of p_assignment_set_id would be
153 populated in this case we should initialize the value of x_assignment_set_id with
154 p_assignment_set_id.
155 */
156
157 IF p_assignment_set_id is not null THEN
158 x_assignment_set_id :=p_assignment_set_id;
159 END IF;
160 ----<LOCAL SR/ASL PROJECT 11i11 END>
161
162
163 X_progress := '010';
164 --Make sure parameters are valid for sourcing rule creation/updation
165 PO_SOURCING_RULES_SV.validate_sourcing_rule(
166 x_interface_header_id =>p_interface_header_id,
167 x_interface_line_id =>p_interface_line_id,
168 x_approval_status =>p_approval_status,
169 x_rule_name =>p_rule_name,
170 x_start_date =>p_start_date,
171 x_end_date =>p_end_date,
172 x_assignment_type_id =>p_assignment_type_id,
173 x_organization_id =>p_organization_id,
174 x_assignment_set_id =>x_assignment_set_id,
175 x_process_flag =>x_process_flag,
176 x_running_status =>l_running_status,
177 x_header_processable_flag =>x_header_processable_flag,
178 x_po_interface_error_code =>p_po_interface_error_code);
179
180 X_progress := '020';
181 IF (X_process_flag = 'Y') THEN
182
183 IF ((X_header_processable_flag = 'Y') and
184 ((p_create_update_code = 'CREATE')
185 OR (p_create_update_code ='CREATE_UPDATE'))) THEN
186 IF (g_po_pdoi_write_to_file = 'Y') THEN
187 PO_DEBUG.put_line(' Creating call rule for the item ...');
188 END IF;
189 PO_SOURCING_RULES_SV.create_sourcing_rule(
190 x_interface_header_id =>p_interface_header_id,
191 x_interface_line_id =>p_interface_line_id,
192 x_item_id =>p_item_id,
193 x_vendor_id =>p_vendor_id,
194 x_po_header_id =>p_po_header_id,
195 x_po_line_id =>p_po_line_id,
196 x_document_type =>p_document_type,
197 x_rule_name =>p_rule_name,
198 x_rule_name_prefix =>p_rule_name_prefix,
199 x_start_Date =>p_start_Date,
200 x_end_date =>p_end_date,
201 x_organization_id =>p_organization_id,
202 x_assignment_type_id =>p_assignment_type_id,
203 x_assignment_set_id =>x_assignment_set_id,
204 x_sourcing_rule_id =>x_sourcing_rule_id,
205 x_temp_sourcing_rule_id =>x_temp_sourcing_rule_id,
206 x_process_flag =>x_process_flag ,
207 x_running_status =>l_running_status,
208 x_header_processable_flag =>x_header_processable_flag,
209 ----<LOCAL SR/ASL PROJECT 11i11 START>
210 p_vendor_site_id =>p_vendor_site_id
211 ----<LOCAL SR/ASL PROJECT 11i11 END>
212
213 );
214
215 END IF; -- p_create_update_code is create
216
217 X_progress := '030';
218 IF ((X_process_flag = 'Y') and
219 (p_create_update_code ='CREATE_UPDATE')) THEN
220
221 --validate to throw errors on some sure overlap failure cases
222 PO_SOURCING_RULES_SV.validate_update_sourcing_rule(
223 x_interface_header_id =>p_interface_header_id,
224 x_interface_line_id =>p_interface_line_id,
225 x_sourcing_rule_id =>x_sourcing_rule_id,
226 x_start_date =>p_start_date,
227 x_end_date =>p_end_date,
228 x_assignment_type_id =>p_assignment_type_id,
229 x_organization_id =>p_organization_id,
230 x_assignment_set_id =>x_assignment_set_id,
231 x_process_flag =>x_process_flag,
232 x_running_status =>l_running_status,
233 x_header_processable_flag =>x_header_processable_flag,
234 x_po_interface_error_code =>p_po_interface_error_code);
235
236 if (x_process_flag = 'Y') then
237 PO_SOURCING_RULES_SV.update_sourcing_rule(
238 x_interface_header_id =>p_interface_header_id,
239 x_interface_line_id =>p_interface_line_id,
240 x_item_id =>p_item_id,
241 x_vendor_id =>p_vendor_id,
242 x_po_header_id =>p_po_header_id,
243 x_po_line_id =>p_po_line_id,
244 x_document_type =>p_document_type,
245 x_sourcing_rule_id =>x_sourcing_rule_id,
246 x_temp_sourcing_rule_id =>x_temp_sourcing_rule_id,
247 x_start_Date =>p_start_Date,
248 x_end_date =>p_end_date,
249 x_organization_id =>p_organization_id,
250 x_assignment_type_id =>p_assignment_type_id,
251 x_assignment_set_id =>x_assignment_set_id,
252 x_running_status =>l_running_status,
253 x_header_processable_flag =>x_header_processable_flag,
254 x_po_interface_error_code =>p_po_interface_error_code,
255 ----<LOCAL SR/ASL PROJECT 11i11 START>
256 p_vendor_site_id =>p_vendor_site_id
257 ----<LOCAL SR/ASL PROJECT 11i11 END>
258 );
259 end if;
260 END IF; -- X_create_update_flag is update
261
262 END IF; --x_process_flag = 'Y'
263 --<Shared Proc FPJ START>
264 IF (l_running_status = 'N' or x_header_processable_flag = 'N') THEN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 ELSE
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268 END IF;
269 --<Shared Proc FPJ END>
270 EXCEPTION
271 WHEN OTHERS THEN
272 x_header_processable_flag := 'N';
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --<Shared Proc FPJ>
274 --dbms_output.put_line('...1');
275 po_message_s.sql_error('create_update_sourcing_rule', x_progress, sqlcode);
276 END create_update_sourcing_rule;
277
278 /*
279 Assignment Type => Assignment Type ID Mapping
280
281 Assignment Type Assignment Type Id
282 --------------------------------------------
283 --------------------------------------------
284 Global => 1
285 Item => 3
286 Organization => 4
287 Category-Org => 5
288 Item-Organization => 6
289
290 */
291
292 PROCEDURE create_sourcing_rule(X_interface_header_id IN NUMBER,
293 X_interface_line_id IN NUMBER,
294 X_item_id IN NUMBER,
295 X_vendor_id IN NUMBER,
296 X_po_header_id IN NUMBER,
297 X_po_line_id IN NUMBER,
298 X_document_type IN VARCHAR2,
299 X_rule_name IN VARCHAR2,
300 X_rule_name_prefix IN VARCHAR2,
301 X_start_date IN DATE,
302 X_end_date IN DATE,
303 X_organization_id IN NUMBER,
304 X_assignment_type_id IN NUMBER,
305 x_assignment_set_id IN NUMBER,
306 x_sourcing_rule_id IN OUT NOCOPY NUMBER,
307 x_temp_sourcing_rule_id IN OUT NOCOPY NUMBER,
308 x_process_flag IN OUT NOCOPY VARCHAR2,
309 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
310 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
311 ----<LOCAL SR/ASL PROJECT 11i11 START>
312 p_vendor_site_id IN NUMBER DEFAULT NULL
313 ----<LOCAL SR/ASL PROJECT 11i11 END>
314 ) IS
315 X_progress VARCHAR2(3) := NULL;
316 x_vendor_site_id number := null;
317 x_last_update_date date := sysdate;
318 x_last_updated_by number := fnd_global.user_id ;
319 x_creation_date date := sysdate;
320 x_created_by number := fnd_global.user_id;
321 x_last_update_login number := fnd_global.user_id;
322 x_sr_receipt_id number := null;
323 x_sr_source_id number := null;
324 X_ASSIGNMENT_ID number := null;
325 --
326 -- SKG
327 --
328 v_error_code NUMBER := 0;
329 v_error_msg VARCHAR2(2000) := NULL;
330 --
331 -- SKG
332 --
333 ----<LOCAL SR/ASL PROJECT 11i11 START>
334 l_organization_id NUMBER;
335 l_sourcing_rule_type NUMBER;
336 l_inv_org_id NUMBER;
337 l_item_exists varchar2(20);
338
339 ----<LOCAL SR/ASL PROJECT 11i11 END>
340
341 begin
342
343 X_progress := '010';
344
345 --<Shared Proc FPJ START>
346 -- Get the vendor site information from the Source Doc.
347 --If the doc is GA then get it from Org Assignment otherwise from header
348
349 ----<LOCAL SR/ASL PROJECT 11i11 START>
350
351 /*
352 The possible values for sourcing_rule_type are :
353 Sourcing Rule => 1
354 Bill Of Distributions => 2
355
356 By Default we create only sourcing rules and hence the value of l_sourcing_rule_type
357 would have to be 1.
358
359 If the value of x_assignment_type_id is null (x_assignment_type_id is null
360 when called from PDOI/WORKFLOW) we would default the x_assignment_type_id to
361 3(This implies sourcing level 'ITEM').
362
363 If the value of x_assignment_type_id is 3 it implies 'ITEM' assignment. In this
364 case the organization_id would be null.
365
366 If the value of x_assignment_type_id is 6 it implies 'ITEM-ORGANIZATION' assignment. In this
367 case the organization_id/receipt_organization_id would be x_organization_id.
368 */
369 l_sourcing_rule_type:=1;
370 IF nvl(x_assignment_type_id,3)=6 THEN
371 l_organization_id:=x_organization_id;
372 ELSE
373 l_organization_id:=null;
374 END IF;
375
376 /*
377 If the calling program is PDOI/Workflow we do not have the vendor_site_id
378 and we need to derive it. POASLGEN would pass the vendor site id by default.
379 */
380
381 IF p_vendor_site_id is NULL THEN
382 get_vendor_site_id(
383 p_po_header_id =>x_po_header_id,
384 x_vendor_site_id =>x_vendor_site_id
385 );
386 ELSE
387 x_vendor_site_id:=p_vendor_site_id;
388 END IF;
389 ----<LOCAL SR/ASL PROJECT 11i11 END>
390
391 --<Shared Proc FPJ END>
392
393 IF (g_po_pdoi_write_to_file = 'Y') THEN
394 PO_DEBUG.put_line(' Vendor Site from doc: ' || to_char (x_vendor_site_id));
395 END IF;
396
397
398 -- Check to see if there is an item level assignment for
399 -- this item at the ITEM level
400 /* Bug 1969613: Before this fix, an incoming line carrying a sourcing rule name
401 which was already existing but a new item used to error out.
402 This happened since for a new item the code always fetched a
403 new sourcing rule id and tried to attach the new rule but with
404 the existing sourcing rule name.The following piece of code now
405 brings up the sourcing rule id for a new item also and in the
406 end the item will be assigned to the assignment set for this
407 sourcing rule id.Also no new sourcing rule will be created
408 in such a case. */
409 /* Bug#3184990 Added the condition 'organization_id is null' to the below
410 ** sql to avoid the ORA-1422 error as PDOI should always consider the
411 ** Global Sourcing Rules only and not the local Sourcing Rules which are
412 ** defined specific to the organization. */
413
414 X_progress := '020';
415 If X_rule_name is not null then
416 BEGIN
417
418 ----<LOCAL SR/ASL PROJECT 11i11 START>
419 select sourcing_rule_id into X_temp_sourcing_rule_id
420 from mrp_sourcing_rules where
421 sourcing_rule_name = X_rule_name and
422 sourcing_rule_type =l_sourcing_rule_type and ----<LOCAL SR/ASL PROJECT 11i11>
423 nvl(organization_id,-999) = nvl(l_organization_id,-999); ----<LOCAL SR/ASL PROJECT 11i11>
424 ----<LOCAL SR/ASL PROJECT 11i11 END>
425 -- Bug#3184990
426 EXCEPTION
427 WHEN no_data_found THEN
428 X_temp_sourcing_rule_id := null;
429 END;
430 END IF;
431
432 BEGIN
433
434 /* Bug No:1515981 Forcing the use of following hint to improve the performance*/
435
436 ----<LOCAL SR/ASL PROJECT 11i11 START>
437 SELECT /*+ INDEX(MRP_SR_ASSIGNMENTS MRP_SR_ASSIGNMENTS_N3) */
438 sourcing_rule_id
439 INTO x_sourcing_rule_id
440 FROM mrp_sr_assignments
441 WHERE inventory_item_id = X_item_id
442 AND assignment_set_id = x_assignment_set_id
443 AND sourcing_rule_type =l_sourcing_rule_type
444 AND assignment_type=nvl(x_assignment_type_id,3)
445 AND decode(x_assignment_type_id,6,organization_id,-1)=decode(x_assignment_type_id,6,l_organization_id,-1);
446 ----<LOCAL SR/ASL PROJECT 11i11>
447 EXCEPTION
448 WHEN no_data_found THEN
449 x_sourcing_rule_id := X_temp_sourcing_rule_id; -- bug 1969613
450 END;
451
452 X_progress := '030';
453 IF (x_sourcing_rule_id is NULL) THEN
454 IF (g_po_pdoi_write_to_file = 'Y') THEN
455 PO_DEBUG.put_line(' Inserting Record in Mrp Sourcing Rules');
456 END IF;
457
458 SELECT MRP_SOURCING_RULES_S.NEXTVAL
459 INTO x_sourcing_rule_id
460 FROM SYS.DUAL;
461
462 INSERT INTO MRP_SOURCING_RULES(
463 sourcing_rule_id,
464 sourcing_rule_name,
465 status,
466 sourcing_rule_type,
467 organization_id,
468 last_update_date,
469 last_updated_by,
470 creation_date,
471 created_by,
472 last_update_login,
473 planning_active
474 ) VALUES (
475 x_sourcing_rule_id,
476 nvl(X_rule_name_prefix,'PURCH')||'_'||to_char(x_sourcing_rule_id),----<LOCAL SR/ASL PROJECT 11i11>
477 1, -- status
478 l_sourcing_rule_type, --<LOCAL SR/ASL PROJECT 11i11>
479 l_organization_id, --<LOCAL SR/ASL PROJECT 11i11>
480 x_last_update_date,
481 x_last_updated_by,
482 x_creation_date,
483 x_created_by,
484 x_last_update_login,
485 1 -- planning_active (1=ACTIVE)
486 );
487
488 IF (g_po_pdoi_write_to_file = 'Y') THEN
489 PO_DEBUG.put_line(' Inserting Record in Mrp Sr Receipt Org');
490 END IF;
491
492 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
493 INTO x_sr_receipt_id
494 FROM SYS.DUAL;
495
496
497 X_progress := '040';
498 INSERT INTO MRP_SR_RECEIPT_ORG(
499 sr_receipt_id,
500 sourcing_rule_id,
501 effective_date,
502 disable_date,
503 last_update_date,
504 last_updated_by,
505 creation_date,
506 created_by,
507 last_update_login,
508 receipt_organization_id----<LOCAL SR/ASL PROJECT 11i11>
509 ) VALUES (
510 x_sr_receipt_id,
511 x_sourcing_rule_id,
512 x_start_date,
513 x_end_date,
514 x_last_update_date,
515 x_last_updated_by,
516 x_creation_date,
517 x_created_by,
518 x_last_update_login,
519 l_organization_id----<LOCAL SR/ASL PROJECT 11i11>
520 );
521
522 IF (g_po_pdoi_write_to_file = 'Y') THEN
523 PO_DEBUG.put_line(' Inserting Record in Mrp Sr Source Org');
524 END IF;
525
526 X_progress := '050';
527 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
528 INTO x_sr_source_id
529 FROM SYS.DUAL;
530
531
532 INSERT INTO MRP_SR_SOURCE_ORG(
533 sr_source_id,
534 sr_receipt_id,
535 vendor_id,
536 vendor_site_id,
537 source_type,
538 allocation_percent,
539 rank,
540 last_update_date,
541 last_updated_by,
542 creation_date,
543 created_by,
544 last_update_login
545 ) VALUES (
546 x_sr_source_id,
547 x_sr_receipt_id,
548 x_vendor_id,
549 x_vendor_site_id,
550 3, -- source_type
551 100, -- bug 605898, allocation_percent should be 100 instead of 0
552 1, -- rank should be 1
553 x_last_update_date,
554 x_last_updated_by,
555 x_creation_date,
556 x_created_by,
557 x_last_update_login
558 );
559
560 IF (g_po_pdoi_write_to_file = 'Y') THEN
561 PO_DEBUG.put_line(' Assigning Sourcing Rule at Item level');
562 END IF;
563
564 X_progress := '060';
565 -- Assign at Item level
566 ----<LOCAL SR/ASL PROJECT 11i11 START>
567 --Validate and ensure that the item is enabled for the given inventory
568 --org. This is to ensure that the correct assignment goes in the
569 --MRP_SR_ASSIGNMENTS
570
571 IF nvl(x_assignment_type_id,3)=6 THEN
572 l_inv_org_id :=x_organization_id;
573 BEGIN
574
575 SELECT 'Item Exists'
576 INTO l_item_exists
577 FROM mtl_system_items
578 WHERE inventory_item_id = x_item_id
579 AND organization_id = l_inv_org_id;
580
581 EXCEPTION
582 WHEN NO_DATA_FOUND THEN
583 IF (g_po_pdoi_write_to_file = 'Y') THEN
584 PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
585 END IF;
586 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
587 ( x_interface_type => 'PO_DOCS_OPEN_INTERFACE'
588 , x_error_type => 'WARNING'
589 , x_batch_id => NULL
590 , x_interface_header_id => x_interface_header_id
591 , x_interface_line_id => x_interface_line_id
592 , x_error_message_name => 'PO_PDOI_CREATE_SR_NO_ITEM'
593 , x_table_name => 'PO_LINES_INTERFACE'
594 , x_column_name => 'ITEM_ID'
595 , x_tokenname1 => 'ORG_NAME'
596 , x_tokenname2 => NULL
597 , x_tokenname3 => NULL
598 , x_tokenname4 => NULL
599 , x_tokenname5 => NULL
600 , x_tokenname6 => NULL
601 , x_tokenvalue1 => PO_GA_PVT.get_org_name(p_org_id => x_organization_id)
602 , x_tokenvalue2 => NULL
603 , x_tokenvalue3 => NULL
604 , x_tokenvalue4 => NULL
605 , x_tokenvalue5 => NULL
606 , x_tokenvalue6 => NULL
607 , x_header_processable_flag => x_header_processable_flag
608 , x_interface_dist_id => NULL
609 );
610 x_header_processable_flag := 'Y';
611 return;
612 END;
613 ----<LOCAL SR/ASL PROJECT 11i11 END>
614 END IF;
615
616
617
618 SELECT MRP_SR_ASSIGNMENTS_S.NEXTVAL
619 INTO x_assignment_id
620 FROM SYS.DUAL;
621
622 INSERT INTO MRP_SR_ASSIGNMENTS(
623 assignment_id,
624 assignment_type,
625 sourcing_rule_id,
626 sourcing_rule_type,
627 assignment_set_id,
628 last_update_date,
629 last_updated_by,
630 creation_date,
631 created_by,
632 last_update_login,
633 organization_id,
634 inventory_item_id
635 ) VALUES (
636 x_assignment_id,
637 NVL(x_assignment_type_id,3), ----<LOCAL SR/ASL PROJECT 11i11>
638 x_sourcing_rule_id,
639 l_sourcing_rule_type, -- sourcing_rule_type (1=SOURCING RULE)
640 x_assignment_set_id,
641 x_last_update_date,
642 x_last_updated_by,
643 x_creation_date,
644 x_created_by,
645 x_last_update_login,
646 -- Bug 3692799: organization_id should be null
647 -- when assignment_type is 3 (item assignment)
648 l_organization_id, ----<LOCAL SR/ASL PROJECT 11i11>
649 x_item_id
650 );
651 ----<LOCAL SR/ASL PROJECT 11i11 END>
652
653 /* FPH We have created the sourcing rule. So set the flag to N.
654 * This will prevent us to call the update_sourcing_rule
655 * procedure.
656 */
657 x_process_flag := 'N';
658 END IF;
659
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 --
664 v_error_code := SQLCODE;
665 v_error_msg := SUBSTR (SQLERRM, 1, 2000);
666 IF (g_po_pdoi_write_to_file = 'Y') THEN
667 PO_DEBUG.put_line(v_error_msg);
668 PO_DEBUG.put_line(v_error_code);
669 END IF;
670 --
671
672 --dbms_output.put_line('...2');
673 x_running_status := 'N';
674 X_header_processable_flag := 'N';
675 po_message_s.sql_error('create_sourcing_rule', x_progress, sqlcode);
676 END create_sourcing_rule;
677
678
679 /*
680 Assignment Type => Assignment Type ID Mapping
681
682 Assignment Type Assignment Type Id
683 --------------------------------------------
684 --------------------------------------------
685 Global => 1
686 Item => 3
687 Organization => 4
688 Category-Org => 5
689 Item-Organization => 6
690
691 */
692
693 PROCEDURE update_sourcing_rule (X_interface_header_id IN NUMBER,
694 X_interface_line_id IN NUMBER,
695 X_item_id IN NUMBER,
696 X_vendor_id IN NUMBER,
697 X_po_header_id IN NUMBER,
698 X_po_line_id IN NUMBER,
699 X_document_type IN VARCHAR2,
700 x_sourcing_rule_id IN NUMBER,
701 x_temp_sourcing_rule_id IN NUMBER,
702 X_start_date IN DATE,
703 X_end_date IN DATE,
704 X_organization_id IN NUMBER,
705 X_assignment_type_id IN NUMBER,
706 x_assignment_set_id IN NUMBER,
707 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
708 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
709 X_po_interface_error_code IN VARCHAR2,
710 ----<LOCAL SR/ASL PROJECT 11i11 START>
711 p_vendor_site_id IN NUMBER DEFAULT NULL
712 ----<LOCAL SR/ASL PROJECT 11i11 END>
713
714 ) IS
715
716 X_progress VARCHAR2(3) := NULL;
717 X_process_flag varchar2(1) := 'Y';
718 x_sr_source_id number := null;
719 x_sr_receipt_id number := null;
720 x_org varchar2(10);
721 x_item_assignment_count number := null;
722 x_assignment_count number := null;
723 x_within_vendor_cnt number := null;
724 X_VENDOR_SITE_ID number;
725 X_vendor_rank number := 1;
726 x_vendor_count number := null;
727 x_vendor_count_on_sr number := null;
728 x_sourcing_name varchar2(50);
729 x_sourcing_rule_within number := 0;
730 x_last_update_date date := sysdate;
731 x_last_updated_by number := fnd_global.user_id ;
732 x_creation_date date := sysdate;
733 x_created_by number := fnd_global.user_id;
734 x_last_update_login number := fnd_global.user_id;
735 x_effective_date date ;
736 x_disable_date date;
737 x_cnt_srdate number := null;
738 v_error_code NUMBER := 0;
739 v_error_msg VARCHAR2(2000) := NULL;
740
741
742 /* This select statement first finds out if the combination of X_item_id and
743 X_vendor_id already exists in PO_AUTOSOURCE_VENDORS */
744
745 /* cursor c1 is to make sure if we can find the exact
746 match in the sourcing rule effectivity dates */
747
748 cursor C1 is
749 SELECT sr_receipt_id
750 FROM mrp_sr_receipt_org msro,
751 mrp_sourcing_rules msr
752 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
753 AND msr.sourcing_rule_id = x_sourcing_rule_id
754 AND msro.effective_date = x_start_date
755 AND trunc(NVL(msro.disable_date,sysdate)) = trunc(NVL(x_end_date,sysdate));
756
757 /* cursor c2 is used to see if we can find any
758 overlaps
759 */
760
761 /* ER - 1743024 - Now Overlapping of dates for an existing sourcing rule is allowed
762 if
763 1. The start date is not before the start date of the sourcing rule.
764 2. The start date is >= start date of sourcing rule and the end date is
765 after the end date of the sourcing rule.
766 3. There is only 1 Vendor , Vendor Site for that effectivity date range
767 4. The end date does not overlap in another effectivity date range for the
768 same sourcing rule.
769
770 Look for code after Cursor C2 is opened and fetched for this logic
771 */
772
773 cursor C2 is
774 SELECT sr_receipt_id, msro.effective_date, msro.disable_date
775 FROM mrp_sr_receipt_org msro,
776 mrp_sourcing_rules msr
777 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
778 AND msr.sourcing_rule_id = x_sourcing_rule_id
779 AND (trunc(msro.effective_date) between trunc(x_start_date) and
780 trunc(x_end_date)
781 OR trunc(NVL(msro.disable_date,sysdate)) between
782 trunc(x_start_date) and
783 trunc(x_end_date));
784
785
786 ----<LOCAL SR/ASL PROJECT 11i11 START>
787 l_organization_id NUMBER;
788 l_sourcing_rule_type NUMBER;
789 l_inv_org_id NUMBER;
790 l_item_exists varchar2(20);
791
792 ----<LOCAL SR/ASL PROJECT 11i11 END>
793
794 begin
795 -- Check to see if that sourcing rule is assigned elsewhere
796 /* If the sourcing rule has more than one row in the
797 mrp_sr_assignments table then we should not be proceeding with the
798 creation/updation of the sourcing rule bcos multiple planners could
799 be operating on the sourcing rule and we should not be modifying
800 the sourcing rule via PDOI */
801
802 X_progress := '010';
803 select sourcing_rule_name, organization_id
804 into x_sourcing_name, x_org
805 from mrp_sourcing_rules
806 where sourcing_rule_id = x_sourcing_rule_id;
807
808 if (x_org is NULL) then
809 x_org := 'ALL';
810 end if;
811
812 X_progress := '030';
813
814
815 /* Bug: 2215958 I am reverting whatever the fix made in 2160710 and writing this new query
816 Added the following query to check if there is an effectivity period in this rule which
817 encompasses the Blanket effectivity period and include blanket supplier and supplier site
818 as a source in that period. Now this check in addition to the above mentioned completes
819 the logic for the creation/updation of sourcing rule.
820 */
821
822 ----<LOCAL SR/ASL PROJECT 11i11 START>
823 /*
824 The possible values for sourcing_rule_type are :
825 Sourcing Rule => 1
826 Bill Of Distributions => 2
827
828 By Default we create only sourcing rules and hence the value of l_sourcing_rule_type
829 would have to be 1.
830
831 If the value of x_assignment_type_id is null (x_assignment_type_id is null
832 when called from PDOI/WORKFLOW) we would default the x_assignment_type_id to
833 3(This implies sourcing level 'ITEM').
834
835 If the value of x_assignment_type_id is 3 it implies 'ITEM' assignment. In this
836 case the organization_id would be null.
837
838 If the value of x_assignment_type_id is 6 it implies 'ITEM-ORGANIZATION' assignment. In this
839 case the organization_id/receipt_organization_id would be x_organization_id.
840 */
841 l_sourcing_rule_type:=1;
842
843 IF nvl(x_assignment_type_id,3)=6 THEN
844 l_organization_id:=x_organization_id;
845 ELSE
846 l_organization_id:=null;
847 END IF;
848
849 /*
850 If the calling program is PDOI/Workflow we do not have the vendor_site_id
851 and we need to derive it.
852 */
853 --<Shared Proc FPJ START>
854 -- Get the vendor site information from the Source Doc.
855 --If the doc is GA then get it from Org Assignment otherwise from header
856
857 IF p_vendor_site_id is NULL THEN
858 get_vendor_site_id(
859 p_po_header_id =>x_po_header_id,
860 x_vendor_site_id =>x_vendor_site_id);
861 ELSE
862 x_vendor_site_id:=p_vendor_site_id;
863 END IF;
864 ----<LOCAL SR/ASL PROJECT 11i11 END>
865
866 --<Shared Proc FPJ END>
867
868 IF (g_po_pdoi_write_to_file = 'Y') THEN
869 PO_DEBUG.put_line(' Vendor Site: ' || to_char (x_vendor_site_id));
870 END IF;
871 SELECT count(*) into x_within_vendor_cnt
872 FROM
873 mrp_sr_receipt_org msro,
874 mrp_sourcing_rules msr,
875 mrp_sr_source_org msso
876 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
877 AND msro.sr_receipt_id = msso.sr_receipt_id
878 AND msr.sourcing_rule_id = x_sourcing_rule_id
879 AND x_start_date between msro.effective_date and msro.disable_date
880 AND x_end_date between msro.effective_date and msro.disable_date
881 AND msso.vendor_id = x_vendor_id
882 AND (msso.vendor_site_id = x_vendor_site_id or
883 (msso.vendor_site_id is NULL and x_vendor_site_id is null));
884
885 IF (g_po_pdoi_write_to_file = 'Y') THEN
886 PO_DEBUG.put_line(' x_within_vendor_cnt: ' || to_char (x_within_vendor_cnt));
887 END IF;
888
889 SELECT count(*)
890 INTO x_assignment_count
891 FROM mrp_sr_assignments
892 WHERE sourcing_rule_id = x_sourcing_rule_id;
893
894 IF (g_po_pdoi_write_to_file = 'Y') THEN
895 PO_DEBUG.put_line(' x_assignment_count: ' || to_char(x_assignment_count));
896 END IF;
897
898 SELECT count(*)
899 INTO x_item_assignment_count
900 FROM mrp_sr_assignments
901 WHERE sourcing_rule_id = x_sourcing_rule_id
902 /* Bug 2160710 solved. Added the below two 'AND' conditions */
903 AND inventory_item_id = X_item_id
904 AND assignment_set_id = X_assignment_set_id ;
905
906 IF (g_po_pdoi_write_to_file = 'Y') THEN
907 PO_DEBUG.put_line(' x_item_assignment_count: ' || to_char (x_item_assignment_count));
908 END IF;
909
910 /* If x_within_vendor_cnt is 0, then it means that there is no
911 * sourcing rule with the same encompassing effective dates and
912 * the vendor. This inturn means that that the sourcing rule that
913 * is coming in irrespective of the vendor needs to updated.
914 * Now we need to consider whether this sourcing rule is assigned
915 * to any other item. We can update only if there is either no
916 * assignment or if there is an assignment to an item, it should
917 * be this item on the blanket in this assignment set. If not,
918 * we should not be changing since this would mean that we are
919 * updating a sourcing rule used by other planner. This check is
920 * done by getting X_assignment_count which is the number of times
921 * this sourcing rule is assigned to any item in any assignment set.
922 * x_item_assignment_count gets the number of times it is assigned
923 * to the item in the default assignment set(which can be 0 or 1).
924 * If they are the same, then it means that this sourcing rule is
925 * assigned only to this item in this assignment set only and
926 * hence can be changed. FPH change
927 */
928 --IF (x_dummy_count > 0) and (x_within_vendor_cnt = 0 ) THEN
929 IF ((x_within_vendor_cnt = 0) AND
930 ((x_assignment_count > 1) OR
931 (x_assignment_count =1 and x_item_assignment_count <> 1 ))) THEN
932
933 -- insert into po interface errors
934
935 IF (g_po_pdoi_write_to_file = 'Y') THEN
936 PO_DEBUG.put_line(' The existing sourcing rule is assgned elsewhere and does not
937 match with the vendor provided in the blanket');
938 END IF;
939
940 X_process_flag := 'N';
941 po_interface_errors_sv1.handle_interface_errors(
942 X_po_interface_error_code,
943 'FATAL',
944 null,
945 X_interface_header_id,
946 X_interface_line_id,
947 'PO_PDOI_SR_ASSIGNED',
948 'PO_HEADERS_INTERFACE',
949 'APPROVAL_STATUS',
950 null, null,null,null,null,null,
951 null, null,null, null,null,null,
952 X_header_processable_flag);
953
954
955 ELSE -- x_within_vendor_cnt
956
957 X_progress := '040';
958 OPEN C1;
959 FETCH C1
960 INTO x_sr_receipt_id;
961
962 IF C1%NOTFOUND THEN
963 OPEN C2;
964 FETCH C2 INTO x_sr_receipt_id, x_effective_date, x_disable_date;
965
966 IF (g_po_pdoi_write_to_file = 'Y') THEN
967 PO_DEBUG.put_line(' Startdate' || to_char(x_start_date,'dd-mon-yy'));
968 PO_DEBUG.put_line(' End date' || to_char (x_end_date,'dd-mon-yy'));
969 END IF;
970
971
972 IF C2%FOUND THEN
973
974 if (trunc(x_effective_date) > trunc(x_start_date)) then
975
976
977 -- insert into po interface errors
978
979 IF (g_po_pdoi_write_to_file = 'Y') THEN
980 PO_DEBUG.put_line(' The effectivity dates do not match');
981 END IF;
982
983 X_process_flag := 'N';
984 po_interface_errors_sv1.handle_interface_errors(
985 X_po_interface_error_code,
986 'FATAL',
987 null,
988 X_interface_header_id,
989 X_interface_line_id,
990 'PO_PDOI_OVERLAP_START_DATE',
991 'PO_LINES_INTERFACE',
992 'START_DATE',
993 'SR_NAME','ORG',null,null,null,null,
994 x_sourcing_name,x_org, null, null,null,null,
995 X_header_processable_flag);
996 elsif
997
998 ((trunc(x_start_date) >= trunc(x_effective_date))
999 AND (trunc(x_start_date) <= trunc(x_disable_date)) --FPH
1000 AND (trunc(x_end_date) > trunc(x_disable_date))) then
1001
1002
1003 /* Check if there is only one Vendor */
1004 -- bug fix 2320143 Throw error if vendor is different or vendor and site is different
1005 X_progress := '050';
1006 select count(*)
1007 into x_vendor_count
1008 from mrp_sr_source_org
1009 where sr_receipt_id = x_sr_receipt_id
1010 and (
1011 (vendor_id <> X_vendor_id)
1012 or
1013 ( (vendor_id = X_vendor_id)
1014 and
1015 (nvl(vendor_site_id, x_vendor_site_id) <> x_vendor_site_id)
1016 )
1017 );
1018
1019
1020 if (x_vendor_count >= 1) then
1021
1022 /* Error - Multiple Vendors assigned */
1023
1024 X_process_flag := 'N';
1025 po_interface_errors_sv1.handle_interface_errors(
1026 X_po_interface_error_code,
1027 'FATAL',
1028 null,
1029 X_interface_header_id,
1030 X_interface_line_id,
1031 'PO_PDOI_OVERLAP_MORE_VENDORS',
1032 'PO_HEADERS_INTERFACE',
1033 'APPROVAL_STATUS',
1034 'SR_NAME','ORG',null,null,null,null,
1035 x_sourcing_name, x_org,null,null,null,null,
1036 X_header_processable_flag);
1037
1038 else
1039
1040 /* Check if the end_date is in another date
1041 range, if so, error */
1042
1043 X_progress := '060';
1044 select count(*)
1045 into x_cnt_srdate
1046 from mrp_sr_receipt_org
1047 where x_end_date between
1048 effective_date and disable_date
1049 and sourcing_rule_id = x_sourcing_rule_id;
1050
1051 if (x_cnt_srdate >= 1) then
1052
1053 X_process_flag := 'N';
1054 po_interface_errors_sv1.handle_interface_errors(
1055 X_po_interface_error_code,
1056 'FATAL',
1057 null,
1058 X_interface_header_id,
1059 X_interface_line_id,
1060 'PO_PDOI_OVERLAP_START_END_DATE',
1061 'PO_LINES_INTERFACE',
1062 'START_DATE',
1063 'SR_NAME','ORG', null,null,null,null,
1064 x_sourcing_name, x_org,null,null,null,null,
1065 X_header_processable_flag) ;
1066
1067
1068 else
1069
1070 /* Update the Effective End date */
1071
1072 if (x_start_date = x_effective_date) then
1073
1074 X_progress := '070';
1075 update mrp_sr_receipt_org
1076 set disable_date = x_end_date
1077 where sr_receipt_id = x_sr_receipt_id;
1078
1079 elsif (x_start_date > x_effective_date) then
1080
1081 /* Update the current record's disable date
1082 to start date - 1 */
1083
1084 update mrp_sr_receipt_org
1085 set disable_date = x_start_date - 1
1086 where sr_receipt_id = x_sr_receipt_id;
1087
1088 /*
1089
1090 If the value of x_assignment_type_id is null (x_assignment_type_id is null
1091 when called from PDOI/WORKFLOW) we would default the x_assignment_type_id to
1092 3(This implies sourcing level 'ITEM').
1093
1094 */
1095 /* Create a new record for the new start and
1096 end dates */
1097
1098 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
1099 INTO x_sr_receipt_id
1100 FROM SYS.DUAL;
1101
1102 INSERT INTO MRP_SR_RECEIPT_ORG(
1103 sr_receipt_id,
1104 sourcing_rule_id,
1105 effective_date,
1106 disable_date,
1107 last_update_date,
1108 last_updated_by,
1109 creation_date,
1110 created_by,
1111 last_update_login,
1112 receipt_organization_id----<LOCAL SR/ASL PROJECT 11i11>
1113 )
1114 VALUES (
1115 x_sr_receipt_id,
1116 x_sourcing_rule_id,
1117 x_start_date,
1118 x_end_date,
1119 x_last_update_date,
1120 x_last_updated_by,
1121 x_creation_date,
1122 x_created_by,
1123 x_last_update_login,
1124 l_organization_id----<LOCAL SR/ASL PROJECT 11i11>
1125 );
1126
1127 -- update existing links to sourcing rule
1128
1129 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
1130 INTO x_sr_source_id
1131 FROM SYS.DUAL;
1132
1133 SELECT nvl(max(rank),0) +1
1134 INTO x_vendor_rank
1135 FROM MRP_SR_SOURCE_ORG MSSO
1136 WHERE sr_receipt_id = x_sr_receipt_id;
1137
1138 INSERT INTO MRP_SR_SOURCE_ORG(
1139 sr_source_id,
1140 sr_receipt_id,
1141 vendor_id,
1142 vendor_site_id,
1143 source_type,
1144 allocation_percent,
1145 rank,
1146 last_update_date,
1147 last_updated_by,
1148 creation_date,
1149 created_by,
1150 last_update_login )
1151 VALUES (
1152 x_sr_source_id,
1153 x_sr_receipt_id,
1154 x_vendor_id,
1155 x_vendor_site_id,
1156 3, -- source_type
1157 100,
1158 x_vendor_rank,
1159 x_last_update_date,
1160 x_last_updated_by,
1161 x_creation_date,
1162 x_created_by,
1163 x_last_update_login );
1164
1165 end if;
1166
1167 end if; /* End of x_cnt_srdate */
1168
1169 end if; /* End of vendor_count */
1170
1171 end if; /* End of elsif */
1172
1173 ELSE
1174
1175 /*Bug 1608608
1176 Check to see if the effectivity dates of new sourcing rule falls
1177 within the exisiting sourcing rule's effectivity dates.
1178 If it does then dont do anything and proceed further else
1179 insert into mrp_sr_receipt_org */
1180
1181 IF (g_po_pdoi_write_to_file = 'Y') THEN
1182 PO_DEBUG.put_line(' Check to see if the effectivity dates of new sourcing rule falls within the existing sourcing rules effectivity dates');
1183 END IF;
1184
1185
1186 IF (g_po_pdoi_write_to_file = 'Y') THEN
1187 PO_DEBUG.put_line(' Inserting Record MSRO for existing rule');
1188 END IF;
1189
1190 X_progress := '080';
1191 SELECT count(*) into x_sourcing_rule_within
1192 FROM mrp_sr_receipt_org msro,
1193 mrp_sourcing_rules msr
1194 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
1195 AND msr.sourcing_rule_id = x_sourcing_rule_id
1196 AND x_start_date between msro.effective_date and msro.disable_date
1197 AND x_end_date between msro.effective_date and msro.disable_date;
1198
1199 if (x_sourcing_rule_within = 0) then
1200
1201 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
1202 INTO x_sr_receipt_id
1203 FROM SYS.DUAL;
1204
1205
1206 INSERT INTO MRP_SR_RECEIPT_ORG(
1207 sr_receipt_id,
1208 sourcing_rule_id,
1209 effective_date,
1210 disable_date,
1211 last_update_date,
1212 last_updated_by,
1213 creation_date,
1214 created_by,
1215 last_update_login,
1216 receipt_organization_id ----<LOCAL SR/ASL PROJECT 11i11>
1217 )
1218 VALUES (
1219 x_sr_receipt_id,
1220 x_sourcing_rule_id,
1221 x_start_date,
1222 x_end_date,
1223 x_last_update_date,
1224 x_last_updated_by,
1225 x_creation_date,
1226 x_created_by,
1227 x_last_update_login,
1228 l_organization_id----<LOCAL SR/ASL PROJECT 11i11>
1229 );
1230
1231 -- update existing links to sourcing rule
1232
1233 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
1234 INTO x_sr_source_id
1235 FROM SYS.DUAL;
1236
1237 SELECT nvl(max(rank),0) +1
1238 INTO x_vendor_rank
1239 FROM MRP_SR_SOURCE_ORG MSSO
1240 WHERE sr_receipt_id = x_sr_receipt_id;
1241
1242 IF (g_po_pdoi_write_to_file = 'Y') THEN
1243 PO_DEBUG.put_line(' Vendor Rank' || to_char(x_vendor_rank));
1244 PO_DEBUG.put_line(' Inserting Record MSSO for existing rule');
1245 END IF;
1246
1247 INSERT INTO MRP_SR_SOURCE_ORG(
1248 sr_source_id,
1249 sr_receipt_id,
1250 vendor_id,
1251 vendor_site_id,
1252 source_type,
1253 allocation_percent,
1254 rank,
1255 last_update_date,
1256 last_updated_by,
1257 creation_date,
1258 created_by,
1259 last_update_login
1260 )
1261 VALUES (
1262 x_sr_source_id,
1263 x_sr_receipt_id,
1264 x_vendor_id,
1265 x_vendor_site_id,
1266 3, -- source_type
1267 100,
1268 x_vendor_rank,
1269 x_last_update_date,
1270 x_last_updated_by,
1271 x_creation_date,
1272 x_created_by,
1273 x_last_update_login
1274 );
1275
1276 end if;-- end of x_sourcing_rule_within
1277
1278 END IF; -- C2%FOUND
1279
1280 ELSE -- if c1%notfound
1281 IF (g_po_pdoi_write_to_file = 'Y') THEN
1282 PO_DEBUG.put_line(' New Vendor Rank ' || to_char(x_vendor_rank));
1283 END IF;
1284 X_progress := '090';
1285
1286 -- Check to see if the item already has a sourcing rule with the same
1287 -- vendor and vendor site at a effectivity period
1288
1289 -- bug 607343, change the check for unique vendor here
1290 /*BUG No.1541387:For the case when the sourcing rule is purged and the
1291 corresponding ASL consists of no supplier_site_code - making the provision for
1292 vendor_site_id and x_vendor_site_id to be null without an error.*/
1293 SELECT count(*)
1294 INTO x_vendor_count_on_sr
1295 FROM mrp_sr_source_org msso
1296 WHERE
1297 sr_receipt_id = x_sr_receipt_id
1298 AND vendor_id = x_vendor_id
1299 AND (vendor_site_id = x_vendor_site_id
1300 OR (vendor_site_id is NULL
1301 AND x_vendor_site_id is NULL));
1302
1303 IF x_vendor_count_on_sr > 0 THEN
1304
1305 IF (g_po_pdoi_write_to_file = 'Y') THEN
1306 PO_DEBUG.put_line(' The sourcing rule for this effectivity date already has this vendor');
1307 END IF;
1308
1309 -- It is Not an error as we may still need to add the
1310 -- document to the ASL.
1311
1312 ELSE
1313
1314 X_progress := '100';
1315 -- update existing links to sourcing rule
1316
1317 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
1318 INTO x_sr_source_id
1319 FROM SYS.DUAL;
1320
1321 SELECT nvl(max(rank),0) +1
1322 INTO x_vendor_rank
1323 FROM MRP_SR_SOURCE_ORG MSSO
1324 WHERE sr_receipt_id = x_sr_receipt_id;
1325
1326 IF (g_po_pdoi_write_to_file = 'Y') THEN
1327 PO_DEBUG.put_line(' New Vendor Rank ' || to_char(x_vendor_rank));
1328 END IF;
1329
1330 INSERT INTO MRP_SR_SOURCE_ORG(
1331 sr_source_id,
1332 sr_receipt_id,
1333 vendor_id,
1334 vendor_site_id,
1335 source_type,
1336 allocation_percent,
1337 rank,
1338 last_update_date,
1339 last_updated_by,
1340 creation_date,
1341 created_by,
1342 last_update_login
1343 ) VALUES (
1344 x_sr_source_id,
1345 x_sr_receipt_id,
1346 x_vendor_id,
1347 x_vendor_site_id,
1348 3, -- source_type
1349 0,
1350 x_vendor_rank,
1351 x_last_update_date,
1352 x_last_updated_by,
1353 x_creation_date,
1354 x_created_by,
1355 x_last_update_login
1356 );
1357
1358 END IF; -- x_vendor_count_on_sr > 0
1359
1360 END IF; -- C1%NOTFOUND
1361
1362 END IF; -- x_within_vendor_cnt = 0 ....
1363
1364 /*Bug 1969613: For the case explained in the bug note above assigning the
1365 item to the assignment set below. */
1366 if ((X_temp_sourcing_rule_id is not null) and
1367 (X_header_processable_flag = 'Y')) then
1368 IF (g_po_pdoi_write_to_file = 'Y') THEN
1369 PO_DEBUG.put_line('Assigning Sourcing Rule at Item level');
1370 END IF;
1371
1372 X_progress := '110';
1373 /* FPH. In the where claue below we used to check
1374 * sourcing_rule_id=X_temp_sourcing_rule_id. Since we
1375 * override x_temp_sourcing_rule_id with
1376 * X_sourcing_rule_id, we should be checking
1377 * with this.
1378 */
1379
1380 ----<LOCAL SR/ASL PROJECT 11i11 START>
1381 --Validate and ensure that the item is enabled for the given inventory
1382 --org. This is to ensure that the correct assignment goes in the
1383 --MRP_SR_ASSIGNMENTS
1384
1385 IF nvl(x_assignment_type_id,3)=6 THEN
1386 l_inv_org_id :=x_organization_id;
1387
1388 BEGIN
1389
1390 SELECT 'Item Exists'
1391 INTO l_item_exists
1392 FROM mtl_system_items
1393 WHERE inventory_item_id = x_item_id
1394 AND organization_id = l_inv_org_id;
1395
1396 EXCEPTION
1397 WHEN NO_DATA_FOUND THEN
1398 IF (g_po_pdoi_write_to_file = 'Y') THEN
1399 PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
1400 END IF;
1401 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
1402 ( x_interface_type => 'PO_DOCS_OPEN_INTERFACE'
1403 , x_error_type => 'WARNING'
1404 , x_batch_id => NULL
1405 , x_interface_header_id => x_interface_header_id
1406 , x_interface_line_id => x_interface_line_id
1407 , x_error_message_name => 'PO_PDOI_CREATE_SR_NO_ITEM'
1408 , x_table_name => 'PO_LINES_INTERFACE'
1409 , x_column_name => 'ITEM_ID'
1410 , x_tokenname1 => 'ORG_NAME'
1411 , x_tokenname2 => NULL
1412 , x_tokenname3 => NULL
1413 , x_tokenname4 => NULL
1414 , x_tokenname5 => NULL
1415 , x_tokenname6 => NULL
1416 , x_tokenvalue1 => PO_GA_PVT.get_org_name(p_org_id => x_organization_id)
1417 , x_tokenvalue2 => NULL
1418 , x_tokenvalue3 => NULL
1419 , x_tokenvalue4 => NULL
1420 , x_tokenvalue5 => NULL
1421 , x_tokenvalue6 => NULL
1422 , x_header_processable_flag => x_header_processable_flag
1423 , x_interface_dist_id => NULL
1424 );
1425 x_header_processable_flag := 'Y';
1426 return;
1427 END;
1428 ----<LOCAL SR/ASL PROJECT 11i11 END>
1429 END IF;
1430
1431 INSERT INTO MRP_SR_ASSIGNMENTS(
1432 assignment_id,
1433 assignment_type,
1434 sourcing_rule_id,
1435 sourcing_rule_type,
1436 assignment_set_id,
1437 last_update_date,
1438 last_updated_by,
1439 creation_date,
1440 created_by,
1441 last_update_login,
1442 organization_id,
1443 inventory_item_id
1444 ) select
1445 MRP_SR_ASSIGNMENTS_S.NEXTVAL,
1446 nvl(x_assignment_type_id,3), ----<LOCAL SR/ASL PROJECT 11i11>
1447 x_sourcing_rule_id,
1448 l_sourcing_rule_type, ----<LOCAL SR/ASL PROJECT 11i11>
1449 x_assignment_set_id,
1450 x_last_update_date,
1451 x_last_updated_by,
1452 x_creation_date,
1453 x_created_by,
1454 x_last_update_login,
1455 -- Bug 3692799: organization_id should be null
1456 -- when assignment_type is 3 (item assignment)
1457 l_organization_id, ----<LOCAL SR/ASL PROJECT 11i11>
1458 x_item_id
1459 from dual where not exists
1460 (select 'The item has to be attached to the assignment set' from
1461 mrp_sr_assignments where
1462 sourcing_rule_id=X_sourcing_rule_id --FPH
1463 and inventory_item_id= X_item_id);
1464 end if;
1465
1466
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 v_error_code := SQLCODE;
1470 v_error_msg := SUBSTR (SQLERRM, 1, 2000);
1471 IF (g_po_pdoi_write_to_file = 'Y') THEN
1472 PO_DEBUG.put_line(v_error_msg);
1473 PO_DEBUG.put_line(v_error_code);
1474 END IF;
1475 x_running_status := 'N';
1476 x_header_processable_flag := 'N';
1477 po_message_s.sql_error('update_sourcing_rule', x_progress, sqlcode);
1478 END update_sourcing_rule;
1479
1480 PROCEDURE validate_sourcing_rule (
1481 X_interface_header_id IN NUMBER,
1482 X_interface_line_id IN NUMBER,
1483 X_approval_status IN VARCHAR2,
1484 X_rule_name IN VARCHAR2,
1485 X_start_date IN DATE,
1486 X_end_date IN DATE,
1487 X_assignment_type_id IN NUMBER,
1488 X_organization_id IN NUMBER,
1489 x_assignment_set_id IN OUT NOCOPY NUMBER,
1490 X_process_flag IN OUT NOCOPY VARCHAR2,
1491 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
1492 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
1493 X_po_interface_error_code IN VARCHAR2) IS
1494
1495 x_asl_status_id number := null;
1496 X_progress VARCHAR2(3) := NULL;
1497 begin
1498 IF (g_po_pdoi_write_to_file = 'Y') THEN
1499 PO_DEBUG.put_line(' Validating sourcing rule Inputs ...');
1500 END IF;
1501
1502 X_progress := '020';
1503 IF (X_start_date is not null) AND (X_end_date is not null) THEN
1504 IF (X_start_date > X_end_date) THEN
1505 X_process_flag := 'N';
1506 po_interface_errors_sv1.handle_interface_errors(
1507 X_po_interface_error_code,
1508 'FATAL',
1509 null,
1510 X_interface_header_id,
1511 X_interface_line_id,
1512 'PO_PDOI_INVALID_START_DATE',
1513 'PO_LINES_INTERFACE',
1514 'START_DATE',
1515 'VALUE',
1516 null,null,null,null,null,
1517 X_start_date,
1518 null,null, null,null,null,
1519 X_header_processable_flag);
1520 END IF;
1521 END IF;
1522
1523
1524 IF (X_start_date is null) THEN
1525 X_progress := '030';
1526 /* do not create autosource rule rec if one of the date
1527 is null */
1528 X_process_flag := 'N';
1529 po_interface_errors_sv1.handle_interface_errors(
1530 X_po_interface_error_code,
1531 'FATAL',
1532 null,
1533 X_interface_header_id,
1534 X_interface_line_id,
1535 'PO_PDOI_COLUMN_NOT_NULL',
1536 'PO_LINES_INTERFACE',
1537 'START_DATE',
1538 'COLUMN_NAME',
1539 null,null,null,null,null,
1540 'START_DATE',
1541 null,null, null,null,null,
1542 X_header_processable_flag);
1543 END IF;
1544
1545 IF (X_end_date is null) THEN
1546 X_progress := '040';
1547 /* do not create autosource rule rec if one of the date
1548 is null */
1549 X_process_flag := 'N';
1550 po_interface_errors_sv1.handle_interface_errors(
1551 X_po_interface_error_code,
1552 'FATAL',
1553 null,
1554 X_interface_header_id,
1555 X_interface_line_id,
1556 'PO_PDOI_COLUMN_NOT_NULL',
1557 'PO_LINES_INTERFACE',
1558 'END_DATE',
1559 'COLUMN_NAME',
1560 null,null,null,null,null,
1561 'END_DATE',
1562 null,null, null,null,null,
1563 X_header_processable_flag);
1564 END IF;
1565
1566
1567 IF (X_approval_status <> 'APPROVED') THEN
1568 X_progress := '045';
1569 /*** cannot create autosource rule unless the document loaded is in
1570 approved status ***/
1571
1572 X_process_flag := 'N';
1573 po_interface_errors_sv1.handle_interface_errors(
1574 X_po_interface_error_code,
1575 'FATAL',
1576 null,
1577 X_interface_header_id,
1578 X_interface_line_id,
1579 'PO_PDOI_INVALID_DOC_STATUS',
1580 'PO_HEADERS_INTERFACE',
1581 'APPROVAL_STATUS',
1582 null, null,null,null,null,null,
1583 null, null,null, null,null,null,
1584 X_header_processable_flag);
1585
1586 END IF;
1587
1588
1589 -- The profile option MRP_DEFAULT_ASSIGNMENT_SET specifies the default
1590 -- assignment set used for PO. If user has not set this profile option
1591 -- then terminate the transaction.
1592
1593 X_progress := '050';
1594 ----<LOCAL SR/ASL PROJECT 11i11 START>
1595 /*
1596 If the calling program is 'POASLGEN' then the value of assignment set id would
1597 not be null. In this case we should not override the value of assignment_set_id.
1598 */
1599
1600 IF x_assignment_set_id IS NULL THEN
1601 fnd_profile.get('MRP_DEFAULT_ASSIGNMENT_SET', x_assignment_set_id);
1602 END IF;
1603
1604 ----<LOCAL SR/ASL PROJECT 11i11 END>
1605 IF x_assignment_set_id IS NULL THEN
1606 IF (g_po_pdoi_write_to_file = 'Y') THEN
1607 PO_DEBUG.put_line(' ** ERROR: Please set the following site level profile option');
1608 PO_DEBUG.put_line(' ** before proceeding with this upgrade: ');
1609 PO_DEBUG.put_line(' ** MRP: Default Sourcing Assignment Set');
1610 END IF;
1611 X_process_flag := 'N';
1612 po_interface_errors_sv1.handle_interface_errors(
1613 X_po_interface_error_code,
1614 'FATAL',
1615 null,
1616 X_interface_header_id,
1617 X_interface_line_id,
1618 'PO_PDOI_NO_ASSGNMT_SET',
1619 'PO_HEADERS_INTERFACE',
1620 'APPROVAL_STATUS',
1621 null, null,null,null,null,null,
1622 null, null,null, null,null,null,
1623 X_header_processable_flag);
1624 END IF;
1625
1626 --
1627 EXCEPTION
1628 WHEN OTHERS THEN
1629 x_running_status := 'N';
1630 --dbms_output.put_line('...4');
1631 X_header_processable_flag := 'N';
1632 po_message_s.sql_error('validate_sourcing_rule', x_progress, sqlcode);
1633 END validate_sourcing_rule;
1634
1635
1636 -- procedure to put future validations related to update_sourcing_rule
1637 PROCEDURE validate_update_sourcing_rule (X_interface_header_id IN NUMBER,
1638 X_interface_line_id IN NUMBER,
1639 X_sourcing_rule_id IN NUMBER,
1640 X_start_date IN DATE,
1641 X_end_date IN DATE,
1642 X_assignment_type_id IN NUMBER,
1643 X_organization_id IN NUMBER,
1644 x_assignment_set_id IN OUT NOCOPY NUMBER,
1645 X_process_flag IN OUT NOCOPY VARCHAR2,
1646 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
1647 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
1648 X_po_interface_error_code IN VARCHAR2) IS
1649 x_overlap_count NUMBER:= 0;
1650 x_sourcing_name VARCHAR2(50);
1651 x_org VARCHAR2(10);
1652 begin
1653 -- bug 2310660. There are some sure fail conditions that would
1654 -- be missed because we are not looping through the cursor to fish out overlapped dates correctly
1655
1656 BEGIN
1657 SELECT count(*) into x_overlap_count
1658 FROM mrp_sr_receipt_org msro, mrp_sourcing_rules msr
1659 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
1660 AND msr.sourcing_rule_id = x_sourcing_rule_id
1661 AND trunc(msro.effective_date) > trunc(x_start_date)
1662 AND (
1663 (trunc(NVL(msro.disable_date,sysdate)) between
1664 trunc(x_start_date) and trunc(x_end_date)
1665 )
1666 or
1667 ( trunc(x_end_date) between
1668 trunc(msro.effective_Date) and trunc(NVL(msro.disable_date,sysdate))
1669 )
1670 );
1671 EXCEPTION
1672 WHEN no_data_found THEN
1673 x_overlap_count := 0;
1674 END;
1675
1676 SELECT sourcing_rule_name, organization_id
1677 INTO x_sourcing_name, x_org
1678 FROM mrp_sourcing_rules
1679 WHERE sourcing_rule_id = x_sourcing_rule_id;
1680
1681 IF (x_org is NULL) THEN
1682 x_org := 'ALL';
1683 END IF;
1684
1685
1686 IF (x_overlap_count > 0) THEN
1687 po_interface_errors_sv1.handle_interface_errors(
1688 X_po_interface_error_code,
1689 'FATAL',
1690 null,
1691 X_interface_header_id,
1692 X_interface_line_id,
1693 'PO_PDOI_OVERLAP_START_DATE',
1694 'PO_LINES_INTERFACE',
1695 'START_DATE',
1696 'SR_NAME','ORG',null,null,null,null,
1697 x_sourcing_name,x_org, null, null,null,null,
1698 X_header_processable_flag);
1699 X_process_flag := 'N';
1700 ELSE
1701 X_process_flag := 'Y';
1702 END IF;
1703
1704 EXCEPTION
1705 WHEN OTHERS THEN
1706 x_running_status := 'N';
1707 X_process_flag := 'Y';
1708 END validate_update_sourcing_rule;
1709
1710 --<Shared Proc FPJ START>
1711 -------------------------------------------------------------------------------
1712 --Start of Comments
1713 --Name: GET_VENDOR_SITE_ID
1714 --Pre-reqs:
1715 -- Assumes that parameter p_po_header_id is a valid document id
1716 --Modifies:
1717 -- None.
1718 --Locks:
1719 -- None.
1720 --Function:
1721 -- If the document is GA then this procedure gets the purchasing site
1722 -- from the enabled org form otherwise gets the site from doc header
1723 --Parameters:
1724 --IN:
1725 --p_po_header_id
1726 -- Document unique identifier whose vendor site is desired
1727 --OUT:
1728 --x_vendor_site_id
1729 -- the site on the document
1730 --Notes:
1731 -- None
1732 --Testing:
1733 -- None
1734 --End of Comments
1735 -------------------------------------------------------------------------------
1736 PROCEDURE get_vendor_site_id (
1737 p_po_header_id IN NUMBER,
1738 x_vendor_site_id OUT NOCOPY NUMBER
1739 ) IS
1740 l_ga_flag po_headers_all.global_agreement_flag%TYPE := 'N';
1741 BEGIN
1742 --If the Doc is GA then get_vendor_site returns vendor_site_id
1743 --Else returns NULL
1744 x_vendor_site_id := PO_GA_PVT.get_vendor_site_id(p_po_header_id);
1745
1746 --Either the doc is not GA or the current org is not enabled.
1747 --For both of these cases, select site from the header
1748 IF x_vendor_site_id is NULL then
1749 x_vendor_site_id := PO_VENDOR_SITES_SV.get_vendor_site_id(
1750 p_po_header_id);
1751 END IF;
1752 EXCEPTION
1753 WHEN OTHERS THEN
1754 x_vendor_site_id := NULL;
1755 END get_vendor_site_id;
1756 --<Shared Proc FPJ END>
1757
1758 END PO_SOURCING_RULES_SV;
[Home] [Help]