1 PACKAGE BODY PO_SOURCING_RULES_SV AS
2 /*$Header: POXPISRB.pls 120.12.12020000.3 2013/02/10 20:31:37 vegajula ship $*/
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 12755392 : The x_sourcing_rule was not populated with the sourcing_rule_id.
426 Ahead in the code checks were made with respect to x_sourcing_rule_id and since it was not populated
427 the code flow was failing.We have to update both x_temp_sourcing_rule_id and x_sourcing_rule id with the same value.*/
428 x_sourcing_rule_id := X_temp_sourcing_rule_id;--bug 12755392
429 -- Bug#3184990
430 EXCEPTION
431 WHEN no_data_found THEN
432 X_temp_sourcing_rule_id := null;
433 x_sourcing_rule_id := X_temp_sourcing_rule_id;--bug 12755392
434 END;
435 END IF;
436
437 /* 8867481 */
438 IF X_rule_name is NULL then
439 BEGIN
440
441 /* Bug No:1515981 Forcing the use of following hint to improve the performance*/
442
443 ----<LOCAL SR/ASL PROJECT 11i11 START>
444 SELECT /*+ INDEX(MRP_SR_ASSIGNMENTS MRP_SR_ASSIGNMENTS_N3) */
445 sourcing_rule_id
446 INTO x_sourcing_rule_id
447 FROM mrp_sr_assignments
448 WHERE inventory_item_id = X_item_id
449 AND assignment_set_id = x_assignment_set_id
450 AND sourcing_rule_type =l_sourcing_rule_type
451 AND assignment_type=nvl(x_assignment_type_id,3)
452 AND decode(x_assignment_type_id,6,organization_id,-1)=decode(x_assignment_type_id,6,l_organization_id,-1);
453 ----<LOCAL SR/ASL PROJECT 11i11>
454 EXCEPTION
455 WHEN no_data_found THEN
456 x_sourcing_rule_id := X_temp_sourcing_rule_id; -- bug 1969613
457 END;
458 END IF; /* 8867481 */
459
460 X_progress := '030';
461 IF (x_sourcing_rule_id is NULL) THEN
462 IF (g_po_pdoi_write_to_file = 'Y') THEN
463 PO_DEBUG.put_line(' Inserting Record in Mrp Sourcing Rules');
464 END IF;
465
466 SELECT MRP_SOURCING_RULES_S.NEXTVAL
467 INTO x_sourcing_rule_id
468 FROM SYS.DUAL;
469
470 INSERT INTO MRP_SOURCING_RULES(
471 sourcing_rule_id,
472 sourcing_rule_name,
473 status,
474 sourcing_rule_type,
475 organization_id,
476 last_update_date,
477 last_updated_by,
478 creation_date,
479 created_by,
480 last_update_login,
481 planning_active
482 ) VALUES (
483 x_sourcing_rule_id,
484 nvl(X_rule_name,nvl(X_rule_name_prefix,'PURCH')||'_'||to_char(x_sourcing_rule_id)),----<LOCAL SR/ASL PROJECT 11i11>/* 8867481 */
485 1, -- status
486 l_sourcing_rule_type, --<LOCAL SR/ASL PROJECT 11i11>
487 l_organization_id, --<LOCAL SR/ASL PROJECT 11i11>
488 x_last_update_date,
489 x_last_updated_by,
490 x_creation_date,
491 x_created_by,
492 x_last_update_login,
493 1 -- planning_active (1=ACTIVE)
494 );
495
496 IF (g_po_pdoi_write_to_file = 'Y') THEN
497 PO_DEBUG.put_line(' Inserting Record in Mrp Sr Receipt Org');
498 END IF;
499
500 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
501 INTO x_sr_receipt_id
502 FROM SYS.DUAL;
503
504
505 X_progress := '040';
506 INSERT INTO MRP_SR_RECEIPT_ORG(
507 sr_receipt_id,
508 sourcing_rule_id,
509 effective_date,
510 disable_date,
511 last_update_date,
512 last_updated_by,
513 creation_date,
514 created_by,
515 last_update_login,
516 receipt_organization_id----<LOCAL SR/ASL PROJECT 11i11>
517 ) VALUES (
518 x_sr_receipt_id,
519 x_sourcing_rule_id,
520 x_start_date,
521 x_end_date,
522 x_last_update_date,
523 x_last_updated_by,
524 x_creation_date,
525 x_created_by,
526 x_last_update_login,
527 l_organization_id----<LOCAL SR/ASL PROJECT 11i11>
528 );
529
530 IF (g_po_pdoi_write_to_file = 'Y') THEN
531 PO_DEBUG.put_line(' Inserting Record in Mrp Sr Source Org');
532 END IF;
533
534 X_progress := '050';
535 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
536 INTO x_sr_source_id
537 FROM SYS.DUAL;
538
539
540 INSERT INTO MRP_SR_SOURCE_ORG(
541 sr_source_id,
542 sr_receipt_id,
543 vendor_id,
544 vendor_site_id,
545 source_type,
546 allocation_percent,
547 rank,
548 last_update_date,
549 last_updated_by,
550 creation_date,
551 created_by,
552 last_update_login
553 ) VALUES (
554 x_sr_source_id,
555 x_sr_receipt_id,
556 x_vendor_id,
557 x_vendor_site_id,
558 3, -- source_type
559 100, -- bug 605898, allocation_percent should be 100 instead of 0
560 1, -- rank should be 1
561 x_last_update_date,
562 x_last_updated_by,
563 x_creation_date,
564 x_created_by,
565 x_last_update_login
566 );
567
568 IF (g_po_pdoi_write_to_file = 'Y') THEN
569 PO_DEBUG.put_line(' Assigning Sourcing Rule at Item level');
570 END IF;
571
572 X_progress := '060';
573 -- Assign at Item level
574 ----<LOCAL SR/ASL PROJECT 11i11 START>
575 --Validate and ensure that the item is enabled for the given inventory
576 --org. This is to ensure that the correct assignment goes in the
577 --MRP_SR_ASSIGNMENTS
578
579 IF nvl(x_assignment_type_id,3)=6 THEN
580 l_inv_org_id :=x_organization_id;
581 BEGIN
582
583 SELECT 'Item Exists'
584 INTO l_item_exists
585 FROM mtl_system_items
586 WHERE inventory_item_id = x_item_id
587 AND organization_id = l_inv_org_id;
588
589 EXCEPTION
590 WHEN NO_DATA_FOUND THEN
591 IF (g_po_pdoi_write_to_file = 'Y') THEN
592 PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
593 END IF;
594 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
595 ( x_interface_type => 'PO_DOCS_OPEN_INTERFACE'
596 , x_error_type => 'WARNING'
597 , x_batch_id => NULL
598 , x_interface_header_id => x_interface_header_id
599 , x_interface_line_id => x_interface_line_id
600 , x_error_message_name => 'PO_PDOI_CREATE_SR_NO_ITEM'
601 , x_table_name => 'PO_LINES_INTERFACE'
602 , x_column_name => 'ITEM_ID'
603 , x_tokenname1 => 'ORG_NAME'
604 , x_tokenname2 => NULL
605 , x_tokenname3 => NULL
606 , x_tokenname4 => NULL
607 , x_tokenname5 => NULL
608 , x_tokenname6 => NULL
609 , x_tokenvalue1 => PO_GA_PVT.get_org_name(p_org_id => x_organization_id)
610 , x_tokenvalue2 => NULL
611 , x_tokenvalue3 => NULL
612 , x_tokenvalue4 => NULL
613 , x_tokenvalue5 => NULL
614 , x_tokenvalue6 => NULL
615 , x_header_processable_flag => x_header_processable_flag
616 , x_interface_dist_id => NULL
617 );
618 x_header_processable_flag := 'Y';
619 return;
620 END;
621 ----<LOCAL SR/ASL PROJECT 11i11 END>
622 END IF;
623
624
625
626 SELECT MRP_SR_ASSIGNMENTS_S.NEXTVAL
627 INTO x_assignment_id
628 FROM SYS.DUAL;
629
630 INSERT INTO MRP_SR_ASSIGNMENTS(
631 assignment_id,
632 assignment_type,
633 sourcing_rule_id,
634 sourcing_rule_type,
635 assignment_set_id,
636 last_update_date,
637 last_updated_by,
638 creation_date,
639 created_by,
640 last_update_login,
641 organization_id,
642 inventory_item_id
643 ) VALUES (
644 x_assignment_id,
645 NVL(x_assignment_type_id,3), ----<LOCAL SR/ASL PROJECT 11i11>
646 x_sourcing_rule_id,
647 l_sourcing_rule_type, -- sourcing_rule_type (1=SOURCING RULE)
648 x_assignment_set_id,
649 x_last_update_date,
650 x_last_updated_by,
651 x_creation_date,
652 x_created_by,
653 x_last_update_login,
654 -- Bug 3692799: organization_id should be null
655 -- when assignment_type is 3 (item assignment)
656 l_organization_id, ----<LOCAL SR/ASL PROJECT 11i11>
657 x_item_id
658 );
659 ----<LOCAL SR/ASL PROJECT 11i11 END>
660
661 /* FPH We have created the sourcing rule. So set the flag to N.
662 * This will prevent us to call the update_sourcing_rule
663 * procedure.
664 */
665 x_process_flag := 'N';
666 END IF;
667
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 --
672 v_error_code := SQLCODE;
673 v_error_msg := SUBSTR (SQLERRM, 1, 2000);
674 IF (g_po_pdoi_write_to_file = 'Y') THEN
675 PO_DEBUG.put_line(v_error_msg);
676 PO_DEBUG.put_line(v_error_code);
677 END IF;
678 --
679
680 --dbms_output.put_line('...2');
681 x_running_status := 'N';
682 X_header_processable_flag := 'N';
683 po_message_s.sql_error('create_sourcing_rule', x_progress, sqlcode);
684 END create_sourcing_rule;
685
686
687 /*
688 Assignment Type => Assignment Type ID Mapping
689
690 Assignment Type Assignment Type Id
691 --------------------------------------------
692 --------------------------------------------
693 Global => 1
694 Item => 3
695 Organization => 4
696 Category-Org => 5
697 Item-Organization => 6
698
699 */
700
701 PROCEDURE update_sourcing_rule (X_interface_header_id IN NUMBER,
702 X_interface_line_id IN NUMBER,
703 X_item_id IN NUMBER,
704 X_vendor_id IN NUMBER,
705 X_po_header_id IN NUMBER,
706 X_po_line_id IN NUMBER,
707 X_document_type IN VARCHAR2,
708 x_sourcing_rule_id IN NUMBER,
709 x_temp_sourcing_rule_id IN NUMBER,
710 X_start_date IN DATE,
711 X_end_date IN DATE,
712 X_organization_id IN NUMBER,
713 X_assignment_type_id IN NUMBER,
714 x_assignment_set_id IN NUMBER,
715 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
716 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
717 X_po_interface_error_code IN VARCHAR2,
718 ----<LOCAL SR/ASL PROJECT 11i11 START>
719 p_vendor_site_id IN NUMBER DEFAULT NULL
720 ----<LOCAL SR/ASL PROJECT 11i11 END>
721
722 ) IS
723
724 X_progress VARCHAR2(3) := NULL;
725 X_process_flag varchar2(1) := 'Y';
726 x_sr_source_id number := null;
727 x_sr_receipt_id number := null;
728 x_org varchar2(10);
729 x_item_assignment_count number := null;
730 x_assignment_count number := null;
731 x_within_vendor_cnt number := null;
732 X_VENDOR_SITE_ID number;
733 X_vendor_rank number := 1;
734 x_vendor_count number := null;
735 x_vendor_count_on_sr number := null;
736 x_sourcing_name varchar2(50);
737 x_sourcing_rule_within number := 0;
738 x_last_update_date date := sysdate;
739 x_last_updated_by number := fnd_global.user_id ;
740 x_creation_date date := sysdate;
741 x_created_by number := fnd_global.user_id;
742 x_last_update_login number := fnd_global.user_id;
743 x_effective_date date ;
744 x_disable_date date;
745 x_cnt_srdate number := null;
746 v_error_code NUMBER := 0;
747 v_error_msg VARCHAR2(2000) := NULL;
748
749
750 /* This select statement first finds out if the combination of X_item_id and
751 X_vendor_id already exists in PO_AUTOSOURCE_VENDORS */
752
753 /* cursor c1 is to make sure if we can find the exact
754 match in the sourcing rule effectivity dates */
755
756 cursor C1 is
757 SELECT sr_receipt_id
758 FROM mrp_sr_receipt_org msro,
759 mrp_sourcing_rules msr
760 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
761 AND msr.sourcing_rule_id = x_sourcing_rule_id
762 AND trunc(nvl(msro.effective_date,x_start_date)) = trunc(x_start_date)--bug 12755392
763 --added trunc and NVL condition
764 AND trunc(NVL(msro.disable_date,x_end_date)) = trunc(x_end_date);--bug 12755392
765 --in NVL for disable_date changed the sysdate to x_end_date and removed the nvl on
766 --x_end_date
767
768 /* cursor c2 is used to see if we can find any
769 overlaps
770 */
771
772 /* ER - 1743024 - Now Overlapping of dates for an existing sourcing rule is allowed
773 if
774 1. The start date is not before the start date of the sourcing rule.
775 2. The start date is >= start date of sourcing rule and the end date is
776 after the end date of the sourcing rule.
777 3. There is only 1 Vendor , Vendor Site for that effectivity date range
778 4. The end date does not overlap in another effectivity date range for the
779 same sourcing rule.
780
781 Look for code after Cursor C2 is opened and fetched for this logic
782 */
783
784 cursor C2 is
785 SELECT sr_receipt_id, msro.effective_date, msro.disable_date
786 FROM mrp_sr_receipt_org msro,
787 mrp_sourcing_rules msr
788 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
789 AND msr.sourcing_rule_id = x_sourcing_rule_id
790 AND (trunc(nvl(msro.effective_date,x_start_date)) between trunc(x_start_date) and
791 trunc(x_end_date)--bug 12755392 added NVL on effective date
792 OR trunc(NVL(msro.disable_date,x_end_date)) between
793 trunc(x_start_date) and
794 trunc(x_end_date));--bug 12755392 in NVL for disable_date,
795 --sysdate was used, changed that to x_end_date
796
797
798 ----<LOCAL SR/ASL PROJECT 11i11 START>
799 l_organization_id NUMBER;
800 l_sourcing_rule_type NUMBER;
801 l_inv_org_id NUMBER;
802 l_item_exists varchar2(20);
803
804 ----<LOCAL SR/ASL PROJECT 11i11 END>
805
806 --bug10330313
807 resource_busy_exc EXCEPTION;
808 PRAGMA EXCEPTION_INIT(resource_busy_exc,-00054);
809 l_locked_doc boolean := FALSE;
810 --bug10330313
811
812
813 begin
814
815 --bug10330313<START>
816 --lock the sourcing rule in mrp_sourcing rules before proceeding
817 --we ll loop 1000 times to try and lock the mrp_sourcing_rules record
818 --If locked, we continue to process the record.
819 --If not we exit the procedure raising an error.
820 FOR i IN 1..1000
821 LOOP
822 BEGIN
823 X_progress := '000';
824 PO_DEBUG.put_line('Trying to lock the sourcing rule');
825 PO_LOCKS.lock_sourcing_rules(
826 p_sourcing_rule_id => x_sourcing_rule_id
827 );
828 l_locked_doc := TRUE;
829 EXIT;
830 EXCEPTION
831 WHEN resource_busy_exc THEN
832 NULL;
833 END;
834
835 END LOOP; -- for i in 1..1000
836
837 IF (NOT l_locked_doc)
838 THEN
839 PO_DEBUG.put_line('failed to lock the sourcing rule after 1000 tries');
840 RAISE PO_CORE_S.g_early_return_exc;
841 END IF;
842
843 --bug10330313<END>
844 -- Check to see if that sourcing rule is assigned elsewhere
845 /* If the sourcing rule has more than one row in the
846 mrp_sr_assignments table then we should not be proceeding with the
847 creation/updation of the sourcing rule bcos multiple planners could
848 be operating on the sourcing rule and we should not be modifying
849 the sourcing rule via PDOI */
850
851 X_progress := '010';
852 select sourcing_rule_name, organization_id
853 into x_sourcing_name, x_org
854 from mrp_sourcing_rules
855 where sourcing_rule_id = x_sourcing_rule_id;
856
857 if (x_org is NULL) then
858 x_org := 'ALL';
859 end if;
860
861 X_progress := '030';
862
863
864 /* Bug: 2215958 I am reverting whatever the fix made in 2160710 and writing this new query
865 Added the following query to check if there is an effectivity period in this rule which
866 encompasses the Blanket effectivity period and include blanket supplier and supplier site
867 as a source in that period. Now this check in addition to the above mentioned completes
868 the logic for the creation/updation of sourcing rule.
869 */
870
871 ----<LOCAL SR/ASL PROJECT 11i11 START>
872 /*
873 The possible values for sourcing_rule_type are :
874 Sourcing Rule => 1
875 Bill Of Distributions => 2
876
877 By Default we create only sourcing rules and hence the value of l_sourcing_rule_type
878 would have to be 1.
879
880 If the value of x_assignment_type_id is null (x_assignment_type_id is null
881 when called from PDOI/WORKFLOW) we would default the x_assignment_type_id to
882 3(This implies sourcing level 'ITEM').
883
884 If the value of x_assignment_type_id is 3 it implies 'ITEM' assignment. In this
885 case the organization_id would be null.
886
887 If the value of x_assignment_type_id is 6 it implies 'ITEM-ORGANIZATION' assignment. In this
888 case the organization_id/receipt_organization_id would be x_organization_id.
889 */
890 l_sourcing_rule_type:=1;
891
892 IF nvl(x_assignment_type_id,3)=6 THEN
893 l_organization_id:=x_organization_id;
894 ELSE
895 l_organization_id:=null;
896 END IF;
897
898 /*
899 If the calling program is PDOI/Workflow we do not have the vendor_site_id
900 and we need to derive it.
901 */
902 --<Shared Proc FPJ START>
903 -- Get the vendor site information from the Source Doc.
904 --If the doc is GA then get it from Org Assignment otherwise from header
905
906 IF p_vendor_site_id is NULL THEN
907 get_vendor_site_id(
908 p_po_header_id =>x_po_header_id,
909 x_vendor_site_id =>x_vendor_site_id);
910 ELSE
911 x_vendor_site_id:=p_vendor_site_id;
912 END IF;
913 ----<LOCAL SR/ASL PROJECT 11i11 END>
914
915 --<Shared Proc FPJ END>
916
917 IF (g_po_pdoi_write_to_file = 'Y') THEN
918 PO_DEBUG.put_line(' Vendor Site: ' || to_char (x_vendor_site_id));
919 END IF;
920
921 /* Bug 12344417 Added nvl clause in effective_date and disable_date */
922 SELECT count(*) into x_within_vendor_cnt
923 FROM
924 mrp_sr_receipt_org msro,
925 mrp_sourcing_rules msr,
926 mrp_sr_source_org msso
927 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
928 AND msro.sr_receipt_id = msso.sr_receipt_id
929 AND msr.sourcing_rule_id = x_sourcing_rule_id
930 AND trunc(x_start_date) between trunc(nvl(msro.effective_date,x_start_date)) and trunc(nvl(msro.disable_date,x_end_date))-- bug 12755392
931 AND trunc(x_end_date) between trunc(nvl(msro.effective_date,x_start_date)) and trunc(nvl(msro.disable_date,x_end_date))--bug 12755392
932 AND msso.vendor_id = x_vendor_id
933 AND (msso.vendor_site_id = x_vendor_site_id or
934 (msso.vendor_site_id is NULL and x_vendor_site_id is null));
935
936 IF (g_po_pdoi_write_to_file = 'Y') THEN
937 PO_DEBUG.put_line(' x_within_vendor_cnt: ' || to_char (x_within_vendor_cnt));
938 END IF;
939
940 SELECT count(*)
941 INTO x_assignment_count
942 FROM mrp_sr_assignments
943 WHERE sourcing_rule_id = x_sourcing_rule_id;
944
945 IF (g_po_pdoi_write_to_file = 'Y') THEN
946 PO_DEBUG.put_line(' x_assignment_count: ' || to_char(x_assignment_count));
947 END IF;
948
949 SELECT count(*)
950 INTO x_item_assignment_count
951 FROM mrp_sr_assignments
952 WHERE sourcing_rule_id = x_sourcing_rule_id
953 /* Bug 2160710 solved. Added the below two 'AND' conditions */
954 AND inventory_item_id = X_item_id
955 AND assignment_set_id = X_assignment_set_id ;
956
957 IF (g_po_pdoi_write_to_file = 'Y') THEN
958 PO_DEBUG.put_line(' x_item_assignment_count: ' || to_char (x_item_assignment_count));
959 END IF;
960
961 /* If x_within_vendor_cnt is 0, then it means that there is no
962 * sourcing rule with the same encompassing effective dates and
963 * the vendor. This inturn means that that the sourcing rule that
964 * is coming in irrespective of the vendor needs to updated.
965 * Now we need to consider whether this sourcing rule is assigned
966 * to any other item. We can update only if there is either no
967 * assignment or if there is an assignment to an item, it should
968 * be this item on the blanket in this assignment set. If not,
969 * we should not be changing since this would mean that we are
970 * updating a sourcing rule used by other planner. This check is
971 * done by getting X_assignment_count which is the number of times
972 * this sourcing rule is assigned to any item in any assignment set.
973 * x_item_assignment_count gets the number of times it is assigned
974 * to the item in the default assignment set(which can be 0 or 1).
975 * If they are the same, then it means that this sourcing rule is
976 * assigned only to this item in this assignment set only and
977 * hence can be changed. FPH change
978 */
979 --IF (x_dummy_count > 0) and (x_within_vendor_cnt = 0 ) THEN
980 IF ((x_within_vendor_cnt = 0) AND
981 ((x_assignment_count > 1) OR
982 (x_assignment_count =1 and x_item_assignment_count <> 1 ))) THEN
983
984 -- insert into po interface errors
985
986 IF (g_po_pdoi_write_to_file = 'Y') THEN
987 PO_DEBUG.put_line(' The existing sourcing rule is assgned elsewhere and does not
988 match with the vendor provided in the blanket');
989 END IF;
990
991 X_process_flag := 'N';
992 po_interface_errors_sv1.handle_interface_errors(
993 X_po_interface_error_code,
994 'FATAL',
995 null,
996 X_interface_header_id,
997 X_interface_line_id,
998 'PO_PDOI_SR_ASSIGNED',
999 'PO_HEADERS_INTERFACE',
1000 'APPROVAL_STATUS',
1001 null, null,null,null,null,null,
1002 null, null,null, null,null,null,
1003 X_header_processable_flag);
1004
1005
1006 ELSE -- x_within_vendor_cnt
1007
1008 X_progress := '040';
1009 OPEN C1;
1010 FETCH C1
1011 INTO x_sr_receipt_id;
1012
1013 IF C1%NOTFOUND THEN
1014 OPEN C2;
1015 FETCH C2 INTO x_sr_receipt_id, x_effective_date, x_disable_date;
1016
1017 IF (g_po_pdoi_write_to_file = 'Y') THEN
1018 PO_DEBUG.put_line(' Startdate' || to_char(x_start_date,'dd-mon-yy'));
1019 PO_DEBUG.put_line(' End date' || to_char (x_end_date,'dd-mon-yy'));
1020 END IF;
1021
1022
1023 IF C2%FOUND THEN
1024
1025 if (trunc(x_effective_date) > trunc(x_start_date)) then
1026
1027
1028 -- insert into po interface errors
1029
1030 IF (g_po_pdoi_write_to_file = 'Y') THEN
1031 PO_DEBUG.put_line(' The effectivity dates do not match');
1032 END IF;
1033
1034 X_process_flag := 'N';
1035 po_interface_errors_sv1.handle_interface_errors(
1036 X_po_interface_error_code,
1037 'FATAL',
1038 null,
1039 X_interface_header_id,
1040 X_interface_line_id,
1041 'PO_PDOI_OVERLAP_START_DATE',
1042 'PO_LINES_INTERFACE',
1043 'START_DATE',
1044 'SR_NAME','ORG',null,null,null,null,
1045 x_sourcing_name,x_org, null, null,null,null,
1046 X_header_processable_flag);
1047 elsif
1048 --bug12591815 disable date on Sourcing rule was not getting updated
1049 --when new end date was less than old end date.
1050 ((trunc(x_start_date) >= trunc(x_effective_date))
1051 AND (trunc(x_start_date) <= trunc(x_disable_date)) --FPH
1052 ) then
1053
1054
1055 /* Check if there is only one Vendor */
1056 -- bug fix 2320143 Throw error if vendor is different or vendor and site is different
1057 X_progress := '050';
1058
1059 --bug10637307 if the vendor site is that of an enabled OU on the same blanket then
1060 --we can exclude the error. i.e an error should be raised only when there exists a
1061 --vendor/site from a blanket other than current blanket in the same SR and we are trying
1062 --to update its date.
1063
1064 SELECT COUNT(*)
1065 INTO x_vendor_count
1066 FROM mrp_sr_source_org
1067 WHERE sr_receipt_id = x_sr_receipt_id
1068 AND ( ( vendor_id <> x_vendor_id )
1069 OR ( ( vendor_id = x_vendor_id )
1070 AND ( ( x_vendor_site_id IS NOT NULL
1071 AND x_vendor_site_id <> vendor_site_id -- Changed from = to <> for the bug 12591815
1072 AND x_vendor_site_id NOT IN (SELECT vendor_site_id
1073 FROM po_ga_org_assignments
1074 WHERE po_header_id = x_po_header_id)
1075 )
1076 OR ( x_vendor_site_id IS NULL
1077 AND vendor_site_id IS NULL ))));
1078
1079 if (x_vendor_count >= 1) then
1080
1081 /* Error - Multiple Vendors assigned */
1082
1083 X_process_flag := 'N';
1084 po_interface_errors_sv1.handle_interface_errors(
1085 X_po_interface_error_code,
1086 'FATAL',
1087 null,
1088 X_interface_header_id,
1089 X_interface_line_id,
1090 'PO_PDOI_OVERLAP_MORE_VENDORS',
1091 'PO_HEADERS_INTERFACE',
1092 'APPROVAL_STATUS',
1093 'SR_NAME','ORG',null,null,null,null,
1094 x_sourcing_name, x_org,null,null,null,null,
1095 X_header_processable_flag);
1096
1097 else
1098
1099 /* Check if the end_date is in another date
1100 range, if so, error */
1101
1102 X_progress := '060';
1103 --bug12591815 disable date on Sourcing rule was not getting updated
1104 --when new end date was less than old end date.
1105 select count(*)
1106 into x_cnt_srdate
1107 from mrp_sr_receipt_org
1108 where x_end_date between
1109 effective_date and disable_date
1110 and sourcing_rule_id = x_sourcing_rule_id
1111 and sr_receipt_id <> x_sr_receipt_id;
1112
1113 if (x_cnt_srdate >= 1) then
1114
1115 X_process_flag := 'N';
1116 po_interface_errors_sv1.handle_interface_errors(
1117 X_po_interface_error_code,
1118 'FATAL',
1119 null,
1120 X_interface_header_id,
1121 X_interface_line_id,
1122 'PO_PDOI_OVERLAP_START_END_DATE',
1123 'PO_LINES_INTERFACE',
1124 'START_DATE',
1125 'SR_NAME','ORG', null,null,null,null,
1126 x_sourcing_name, x_org,null,null,null,null,
1127 X_header_processable_flag) ;
1128
1129
1130 else
1131
1132 /* Update the Effective End date */
1133
1134 if (x_start_date = x_effective_date) then
1135
1136 X_progress := '070';
1137 update mrp_sr_receipt_org
1138 set disable_date = x_end_date
1139 where sr_receipt_id = x_sr_receipt_id;
1140
1141 elsif (x_start_date > x_effective_date) then
1142
1143 /* Update the current record's disable date
1144 to start date - 1 */
1145
1146 update mrp_sr_receipt_org
1147 set disable_date = x_start_date - 1
1148 where sr_receipt_id = x_sr_receipt_id;
1149
1150 /*
1151
1152 If the value of x_assignment_type_id is null (x_assignment_type_id is null
1153 when called from PDOI/WORKFLOW) we would default the x_assignment_type_id to
1154 3(This implies sourcing level 'ITEM').
1155
1156 */
1157 /* Create a new record for the new start and
1158 end dates */
1159
1160 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
1161 INTO x_sr_receipt_id
1162 FROM SYS.DUAL;
1163
1164 INSERT INTO MRP_SR_RECEIPT_ORG(
1165 sr_receipt_id,
1166 sourcing_rule_id,
1167 effective_date,
1168 disable_date,
1169 last_update_date,
1170 last_updated_by,
1171 creation_date,
1172 created_by,
1173 last_update_login,
1174 receipt_organization_id----<LOCAL SR/ASL PROJECT 11i11>
1175 )
1176 VALUES (
1177 x_sr_receipt_id,
1178 x_sourcing_rule_id,
1179 x_start_date,
1180 x_end_date,
1181 x_last_update_date,
1182 x_last_updated_by,
1183 x_creation_date,
1184 x_created_by,
1185 x_last_update_login,
1186 l_organization_id----<LOCAL SR/ASL PROJECT 11i11>
1187 );
1188
1189 -- update existing links to sourcing rule
1190
1191 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
1192 INTO x_sr_source_id
1193 FROM SYS.DUAL;
1194
1195 SELECT nvl(max(rank),0) +1
1196 INTO x_vendor_rank
1197 FROM MRP_SR_SOURCE_ORG MSSO
1198 WHERE sr_receipt_id = x_sr_receipt_id;
1199
1200 INSERT INTO MRP_SR_SOURCE_ORG(
1201 sr_source_id,
1202 sr_receipt_id,
1203 vendor_id,
1204 vendor_site_id,
1205 source_type,
1206 allocation_percent,
1207 rank,
1208 last_update_date,
1209 last_updated_by,
1210 creation_date,
1211 created_by,
1212 last_update_login )
1213 VALUES (
1214 x_sr_source_id,
1215 x_sr_receipt_id,
1216 x_vendor_id,
1217 x_vendor_site_id,
1218 3, -- source_type
1219 100,
1220 x_vendor_rank,
1221 x_last_update_date,
1222 x_last_updated_by,
1223 x_creation_date,
1224 x_created_by,
1225 x_last_update_login );
1226
1227 end if;
1228
1229 end if; /* End of x_cnt_srdate */
1230
1231 end if; /* End of vendor_count */
1232
1233 end if; /* End of elsif */
1234
1235 ELSE
1236
1237 /*Bug 1608608
1238 Check to see if the effectivity dates of new sourcing rule falls
1239 within the exisiting sourcing rule's effectivity dates.
1240 If it does then dont do anything and proceed further else
1241 insert into mrp_sr_receipt_org */
1242
1243 IF (g_po_pdoi_write_to_file = 'Y') THEN
1244 PO_DEBUG.put_line(' Check to see if the effectivity dates of new sourcing rule falls within the existing sourcing rules effectivity dates');
1245 END IF;
1246
1247
1248 IF (g_po_pdoi_write_to_file = 'Y') THEN
1249 PO_DEBUG.put_line(' Inserting Record MSRO for existing rule');
1250 END IF;
1251
1252 X_progress := '080';
1253 SELECT count(*) into x_sourcing_rule_within
1254 FROM mrp_sr_receipt_org msro,
1255 mrp_sourcing_rules msr
1256 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
1257 AND msr.sourcing_rule_id = x_sourcing_rule_id
1258 AND x_start_date between msro.effective_date and msro.disable_date
1259 AND x_end_date between msro.effective_date and msro.disable_date;
1260
1261 if (x_sourcing_rule_within = 0) then
1262
1263 SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
1264 INTO x_sr_receipt_id
1265 FROM SYS.DUAL;
1266
1267
1268 INSERT INTO MRP_SR_RECEIPT_ORG(
1269 sr_receipt_id,
1270 sourcing_rule_id,
1271 effective_date,
1272 disable_date,
1273 last_update_date,
1274 last_updated_by,
1275 creation_date,
1276 created_by,
1277 last_update_login,
1278 receipt_organization_id ----<LOCAL SR/ASL PROJECT 11i11>
1279 )
1280 VALUES (
1281 x_sr_receipt_id,
1282 x_sourcing_rule_id,
1283 x_start_date,
1284 x_end_date,
1285 x_last_update_date,
1286 x_last_updated_by,
1287 x_creation_date,
1288 x_created_by,
1289 x_last_update_login,
1290 l_organization_id----<LOCAL SR/ASL PROJECT 11i11>
1291 );
1292
1293 -- update existing links to sourcing rule
1294
1295 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
1296 INTO x_sr_source_id
1297 FROM SYS.DUAL;
1298
1299 SELECT nvl(max(rank),0) +1
1300 INTO x_vendor_rank
1301 FROM MRP_SR_SOURCE_ORG MSSO
1302 WHERE sr_receipt_id = x_sr_receipt_id;
1303
1304 IF (g_po_pdoi_write_to_file = 'Y') THEN
1305 PO_DEBUG.put_line(' Vendor Rank' || to_char(x_vendor_rank));
1306 PO_DEBUG.put_line(' Inserting Record MSSO for existing rule');
1307 END IF;
1308
1309 INSERT INTO MRP_SR_SOURCE_ORG(
1310 sr_source_id,
1311 sr_receipt_id,
1312 vendor_id,
1313 vendor_site_id,
1314 source_type,
1315 allocation_percent,
1316 rank,
1317 last_update_date,
1318 last_updated_by,
1319 creation_date,
1320 created_by,
1321 last_update_login
1322 )
1323 VALUES (
1324 x_sr_source_id,
1325 x_sr_receipt_id,
1326 x_vendor_id,
1327 x_vendor_site_id,
1328 3, -- source_type
1329 100,
1330 x_vendor_rank,
1331 x_last_update_date,
1332 x_last_updated_by,
1333 x_creation_date,
1334 x_created_by,
1335 x_last_update_login
1336 );
1337
1338 end if;-- end of x_sourcing_rule_within
1339
1340 END IF; -- C2%FOUND
1341
1342 ELSE -- if c1%notfound
1343 IF (g_po_pdoi_write_to_file = 'Y') THEN
1344 PO_DEBUG.put_line(' New Vendor Rank ' || to_char(x_vendor_rank));
1345 END IF;
1346 X_progress := '090';
1347
1348 -- Check to see if the item already has a sourcing rule with the same
1349 -- vendor and vendor site at a effectivity period
1350
1351 -- bug 607343, change the check for unique vendor here
1352 /*BUG No.1541387:For the case when the sourcing rule is purged and the
1353 corresponding ASL consists of no supplier_site_code - making the provision for
1354 vendor_site_id and x_vendor_site_id to be null without an error.*/
1355 SELECT count(*)
1356 INTO x_vendor_count_on_sr
1357 FROM mrp_sr_source_org msso
1358 WHERE
1359 sr_receipt_id = x_sr_receipt_id
1360 AND vendor_id = x_vendor_id
1361 AND (vendor_site_id = x_vendor_site_id
1362 OR (vendor_site_id is NULL
1363 AND x_vendor_site_id is NULL));
1364
1365 IF x_vendor_count_on_sr > 0 THEN
1366
1367 IF (g_po_pdoi_write_to_file = 'Y') THEN
1368 PO_DEBUG.put_line(' The sourcing rule for this effectivity date already has this vendor');
1369 END IF;
1370
1371 -- It is Not an error as we may still need to add the
1372 -- document to the ASL.
1373
1374 ELSE
1375
1376 X_progress := '100';
1377 -- update existing links to sourcing rule
1378
1379 SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
1380 INTO x_sr_source_id
1381 FROM SYS.DUAL;
1382
1383 -- #13961772 POASLGEN IS NOT PROPERLY UPDATING SOURCING RULES
1384 -- the new source org has the top rank, always set the
1385 -- rank to 1, and update the existings to self increasing 1
1386 -- respectively.
1387 /*SELECT nvl(max(rank),0) +1
1388 INTO x_vendor_rank
1389 FROM MRP_SR_SOURCE_ORG MSSO
1390 WHERE sr_receipt_id = x_sr_receipt_id;
1391
1392 IF (g_po_pdoi_write_to_file = 'Y') THEN
1393 PO_DEBUG.put_line(' New Vendor Rank ' || to_char(x_vendor_rank));
1394 END IF;*/
1395
1396 Update Mrp_Sr_Source_Org
1397 Set Rank = Rank + 1
1398 Where Sr_Receipt_Id = X_Sr_Receipt_Id;
1399 --End 13961772
1400
1401 --bug9854697 we need to make a 100% allocation for the rule with a new vendor with same effective dates also
1402 --Since the ranks are anyway different making it a 100% is correct. PO and Planning will pick up the correct
1403 --record.
1404 --If it is 0%, the assignment set becomes invalid and also planning active was checked for this record which is
1405 --an inconsistent behaviour. Making it 100 resolved both these issues.
1406 --Also if it is a new vendor/new OU, making 0% allocation is not correct, as the entire purpose of sourcing is
1407 --defeated.
1408 --The user can anytime manually change the allocation percentage as per their business requirements.
1409
1410 INSERT INTO MRP_SR_SOURCE_ORG(
1411 sr_source_id,
1412 sr_receipt_id,
1413 vendor_id,
1414 vendor_site_id,
1415 source_type,
1416 allocation_percent,
1417 rank,
1418 last_update_date,
1419 last_updated_by,
1420 creation_date,
1421 created_by,
1422 last_update_login
1423 ) VALUES (
1424 x_sr_source_id,
1425 x_sr_receipt_id,
1426 x_vendor_id,
1427 x_vendor_site_id,
1428 3, -- source_type
1429 100, --bug9854697
1430 1, --x_vendor_rank, --13961772
1431 x_last_update_date,
1432 x_last_updated_by,
1433 x_creation_date,
1434 x_created_by,
1435 x_last_update_login
1436 );
1437
1438 END IF; -- x_vendor_count_on_sr > 0
1439
1440 END IF; -- C1%NOTFOUND
1441
1442 END IF; -- x_within_vendor_cnt = 0 ....
1443
1444 /*Bug 1969613: For the case explained in the bug note above assigning the
1445 item to the assignment set below. */
1446 if ((X_temp_sourcing_rule_id is not null) and
1447 (X_header_processable_flag = 'Y')) then
1448 IF (g_po_pdoi_write_to_file = 'Y') THEN
1449 PO_DEBUG.put_line('Assigning Sourcing Rule at Item level');
1450 END IF;
1451
1452 X_progress := '110';
1453 /* FPH. In the where claue below we used to check
1454 * sourcing_rule_id=X_temp_sourcing_rule_id. Since we
1455 * override x_temp_sourcing_rule_id with
1456 * X_sourcing_rule_id, we should be checking
1457 * with this.
1458 */
1459
1460 ----<LOCAL SR/ASL PROJECT 11i11 START>
1461 --Validate and ensure that the item is enabled for the given inventory
1462 --org. This is to ensure that the correct assignment goes in the
1463 --MRP_SR_ASSIGNMENTS
1464
1465 IF nvl(x_assignment_type_id,3)=6 THEN
1466 l_inv_org_id :=x_organization_id;
1467
1468 BEGIN
1469
1470 SELECT 'Item Exists'
1471 INTO l_item_exists
1472 FROM mtl_system_items
1473 WHERE inventory_item_id = x_item_id
1474 AND organization_id = l_inv_org_id;
1475
1476 EXCEPTION
1477 WHEN NO_DATA_FOUND THEN
1478 IF (g_po_pdoi_write_to_file = 'Y') THEN
1479 PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
1480 END IF;
1481 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
1482 ( x_interface_type => 'PO_DOCS_OPEN_INTERFACE'
1483 , x_error_type => 'WARNING'
1484 , x_batch_id => NULL
1485 , x_interface_header_id => x_interface_header_id
1486 , x_interface_line_id => x_interface_line_id
1487 , x_error_message_name => 'PO_PDOI_CREATE_SR_NO_ITEM'
1488 , x_table_name => 'PO_LINES_INTERFACE'
1489 , x_column_name => 'ITEM_ID'
1490 , x_tokenname1 => 'ORG_NAME'
1491 , x_tokenname2 => NULL
1492 , x_tokenname3 => NULL
1493 , x_tokenname4 => NULL
1494 , x_tokenname5 => NULL
1495 , x_tokenname6 => NULL
1496 , x_tokenvalue1 => PO_GA_PVT.get_org_name(p_org_id => x_organization_id)
1497 , x_tokenvalue2 => NULL
1498 , x_tokenvalue3 => NULL
1499 , x_tokenvalue4 => NULL
1500 , x_tokenvalue5 => NULL
1501 , x_tokenvalue6 => NULL
1502 , x_header_processable_flag => x_header_processable_flag
1503 , x_interface_dist_id => NULL
1504 );
1505 x_header_processable_flag := 'Y';
1506 return;
1507 END;
1508 ----<LOCAL SR/ASL PROJECT 11i11 END>
1509 END IF;
1510
1511 INSERT INTO MRP_SR_ASSIGNMENTS(
1512 assignment_id,
1513 assignment_type,
1514 sourcing_rule_id,
1515 sourcing_rule_type,
1516 assignment_set_id,
1517 last_update_date,
1518 last_updated_by,
1519 creation_date,
1520 created_by,
1521 last_update_login,
1522 organization_id,
1523 inventory_item_id
1524 ) select
1525 MRP_SR_ASSIGNMENTS_S.NEXTVAL,
1526 nvl(x_assignment_type_id,3), ----<LOCAL SR/ASL PROJECT 11i11>
1527 x_sourcing_rule_id,
1528 l_sourcing_rule_type, ----<LOCAL SR/ASL PROJECT 11i11>
1529 x_assignment_set_id,
1530 x_last_update_date,
1531 x_last_updated_by,
1532 x_creation_date,
1533 x_created_by,
1534 x_last_update_login,
1535 -- Bug 3692799: organization_id should be null
1536 -- when assignment_type is 3 (item assignment)
1537 l_organization_id, ----<LOCAL SR/ASL PROJECT 11i11>
1538 x_item_id
1539 from dual where not exists
1540 (select 'The item has to be attached to the assignment set' from
1541 mrp_sr_assignments where
1542 sourcing_rule_id=X_sourcing_rule_id --FPH
1543 and inventory_item_id= X_item_id);
1544 end if;
1545
1546
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549 v_error_code := SQLCODE;
1550 v_error_msg := SUBSTR (SQLERRM, 1, 2000);
1551 IF (g_po_pdoi_write_to_file = 'Y') THEN
1552 PO_DEBUG.put_line(v_error_msg);
1553 PO_DEBUG.put_line(v_error_code);
1554 END IF;
1555 x_running_status := 'N';
1556 x_header_processable_flag := 'N';
1557 po_message_s.sql_error('update_sourcing_rule', x_progress, sqlcode);
1558 END update_sourcing_rule;
1559
1560 PROCEDURE validate_sourcing_rule (
1561 X_interface_header_id IN NUMBER,
1562 X_interface_line_id IN NUMBER,
1563 X_approval_status IN VARCHAR2,
1564 X_rule_name IN VARCHAR2,
1565 X_start_date IN DATE,
1566 X_end_date IN DATE,
1567 X_assignment_type_id IN NUMBER,
1568 X_organization_id IN NUMBER,
1569 x_assignment_set_id IN OUT NOCOPY NUMBER,
1570 X_process_flag IN OUT NOCOPY VARCHAR2,
1571 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
1572 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
1573 X_po_interface_error_code IN VARCHAR2) IS
1574
1575 x_asl_status_id number := null;
1576 X_progress VARCHAR2(3) := NULL;
1577 begin
1578 IF (g_po_pdoi_write_to_file = 'Y') THEN
1579 PO_DEBUG.put_line(' Validating sourcing rule Inputs ...');
1580 END IF;
1581
1582 X_progress := '020';
1583 IF (X_start_date is not null) AND (X_end_date is not null) THEN
1584 IF (X_start_date > X_end_date) THEN
1585 X_process_flag := 'N';
1586 po_interface_errors_sv1.handle_interface_errors(
1587 X_po_interface_error_code,
1588 'FATAL',
1589 null,
1590 X_interface_header_id,
1591 X_interface_line_id,
1592 'PO_PDOI_INVALID_START_DATE',
1593 'PO_LINES_INTERFACE',
1594 'START_DATE',
1595 'VALUE',
1596 null,null,null,null,null,
1597 X_start_date,
1598 null,null, null,null,null,
1599 X_header_processable_flag);
1600 END IF;
1601 END IF;
1602
1603
1604 IF (X_start_date is null) THEN
1605 X_progress := '030';
1606 /* do not create autosource rule rec if one of the date
1607 is null */
1608 X_process_flag := 'N';
1609 po_interface_errors_sv1.handle_interface_errors(
1610 X_po_interface_error_code,
1611 'FATAL',
1612 null,
1613 X_interface_header_id,
1614 X_interface_line_id,
1615 'PO_PDOI_COLUMN_NOT_NULL',
1616 'PO_LINES_INTERFACE',
1617 'START_DATE',
1618 'COLUMN_NAME',
1619 null,null,null,null,null,
1620 'START_DATE',
1621 null,null, null,null,null,
1622 X_header_processable_flag);
1623 END IF;
1624
1625 IF (X_end_date is null) THEN
1626 X_progress := '040';
1627 /* do not create autosource rule rec if one of the date
1628 is null */
1629 X_process_flag := 'N';
1630 po_interface_errors_sv1.handle_interface_errors(
1631 X_po_interface_error_code,
1632 'FATAL',
1633 null,
1634 X_interface_header_id,
1635 X_interface_line_id,
1636 'PO_PDOI_COLUMN_NOT_NULL',
1637 'PO_LINES_INTERFACE',
1638 'END_DATE',
1639 'COLUMN_NAME',
1640 null,null,null,null,null,
1641 'END_DATE',
1642 null,null, null,null,null,
1643 X_header_processable_flag);
1644 END IF;
1645
1646
1647 IF (X_approval_status <> 'APPROVED') THEN
1648 X_progress := '045';
1649 /*** cannot create autosource rule unless the document loaded is in
1650 approved status ***/
1651
1652 X_process_flag := 'N';
1653 po_interface_errors_sv1.handle_interface_errors(
1654 X_po_interface_error_code,
1655 'FATAL',
1656 null,
1657 X_interface_header_id,
1658 X_interface_line_id,
1659 'PO_PDOI_INVALID_DOC_STATUS',
1660 'PO_HEADERS_INTERFACE',
1661 'APPROVAL_STATUS',
1662 null, null,null,null,null,null,
1663 null, null,null, null,null,null,
1664 X_header_processable_flag);
1665
1666 END IF;
1667
1668
1669 -- The profile option MRP_DEFAULT_ASSIGNMENT_SET specifies the default
1670 -- assignment set used for PO. If user has not set this profile option
1671 -- then terminate the transaction.
1672
1673 X_progress := '050';
1674 ----<LOCAL SR/ASL PROJECT 11i11 START>
1675 /*
1676 If the calling program is 'POASLGEN' then the value of assignment set id would
1677 not be null. In this case we should not override the value of assignment_set_id.
1678 */
1679
1680 IF x_assignment_set_id IS NULL THEN
1681 fnd_profile.get('MRP_DEFAULT_ASSIGNMENT_SET', x_assignment_set_id);
1682 END IF;
1683
1684 ----<LOCAL SR/ASL PROJECT 11i11 END>
1685 IF x_assignment_set_id IS NULL THEN
1686 IF (g_po_pdoi_write_to_file = 'Y') THEN
1687 PO_DEBUG.put_line(' ** ERROR: Please set the following site level profile option');
1688 PO_DEBUG.put_line(' ** before proceeding with this upgrade: ');
1689 PO_DEBUG.put_line(' ** MRP: Default Sourcing Assignment Set');
1690 END IF;
1691 X_process_flag := 'N';
1692 po_interface_errors_sv1.handle_interface_errors(
1693 X_po_interface_error_code,
1694 'FATAL',
1695 null,
1696 X_interface_header_id,
1697 X_interface_line_id,
1698 'PO_PDOI_NO_ASSGNMT_SET',
1699 'PO_HEADERS_INTERFACE',
1700 'APPROVAL_STATUS',
1701 null, null,null,null,null,null,
1702 null, null,null, null,null,null,
1703 X_header_processable_flag);
1704 END IF;
1705
1706 --
1707 EXCEPTION
1708 WHEN OTHERS THEN
1709 x_running_status := 'N';
1710 --dbms_output.put_line('...4');
1711 X_header_processable_flag := 'N';
1712 po_message_s.sql_error('validate_sourcing_rule', x_progress, sqlcode);
1713 END validate_sourcing_rule;
1714
1715
1716 -- procedure to put future validations related to update_sourcing_rule
1717 PROCEDURE validate_update_sourcing_rule (X_interface_header_id IN NUMBER,
1718 X_interface_line_id IN NUMBER,
1719 X_sourcing_rule_id IN NUMBER,
1720 X_start_date IN DATE,
1721 X_end_date IN DATE,
1722 X_assignment_type_id IN NUMBER,
1723 X_organization_id IN NUMBER,
1724 x_assignment_set_id IN OUT NOCOPY NUMBER,
1725 X_process_flag IN OUT NOCOPY VARCHAR2,
1726 X_running_status IN OUT NOCOPY VARCHAR2, --<Shared Proc FPJ>
1727 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
1728 X_po_interface_error_code IN VARCHAR2) IS
1729 x_overlap_count NUMBER:= 0;
1730 x_sourcing_name VARCHAR2(50);
1731 x_org VARCHAR2(10);
1732 begin
1733 -- bug 2310660. There are some sure fail conditions that would
1734 -- be missed because we are not looping through the cursor to fish out overlapped dates correctly
1735
1736 BEGIN
1737 SELECT count(*) into x_overlap_count
1738 FROM mrp_sr_receipt_org msro, mrp_sourcing_rules msr
1739 WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
1740 AND msr.sourcing_rule_id = x_sourcing_rule_id
1741 AND trunc(msro.effective_date) > trunc(x_start_date)
1742 AND (
1743 (trunc(NVL(msro.disable_date,sysdate)) between
1744 trunc(x_start_date) and trunc(x_end_date)
1745 )
1746 or
1747 ( trunc(x_end_date) between
1748 trunc(msro.effective_Date) and trunc(NVL(msro.disable_date,sysdate))
1749 )
1750 );
1751 EXCEPTION
1752 WHEN no_data_found THEN
1753 x_overlap_count := 0;
1754 END;
1755
1756 SELECT sourcing_rule_name, organization_id
1757 INTO x_sourcing_name, x_org
1758 FROM mrp_sourcing_rules
1759 WHERE sourcing_rule_id = x_sourcing_rule_id;
1760
1761 IF (x_org is NULL) THEN
1762 x_org := 'ALL';
1763 END IF;
1764
1765
1766 IF (x_overlap_count > 0) THEN
1767 po_interface_errors_sv1.handle_interface_errors(
1768 X_po_interface_error_code,
1769 'FATAL',
1770 null,
1771 X_interface_header_id,
1772 X_interface_line_id,
1773 'PO_PDOI_OVERLAP_START_DATE',
1774 'PO_LINES_INTERFACE',
1775 'START_DATE',
1776 'SR_NAME','ORG',null,null,null,null,
1777 x_sourcing_name,x_org, null, null,null,null,
1778 X_header_processable_flag);
1779 X_process_flag := 'N';
1780 ELSE
1781 X_process_flag := 'Y';
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 x_running_status := 'N';
1787 X_process_flag := 'Y';
1788 END validate_update_sourcing_rule;
1789
1790 --<Shared Proc FPJ START>
1791 -------------------------------------------------------------------------------
1792 --Start of Comments
1793 --Name: GET_VENDOR_SITE_ID
1794 --Pre-reqs:
1795 -- Assumes that parameter p_po_header_id is a valid document id
1796 --Modifies:
1797 -- None.
1798 --Locks:
1799 -- None.
1800 --Function:
1801 -- If the document is GA then this procedure gets the purchasing site
1802 -- from the enabled org form otherwise gets the site from doc header
1803 --Parameters:
1804 --IN:
1805 --p_po_header_id
1806 -- Document unique identifier whose vendor site is desired
1807 --OUT:
1808 --x_vendor_site_id
1809 -- the site on the document
1810 --Notes:
1811 -- None
1812 --Testing:
1813 -- None
1814 --End of Comments
1815 -------------------------------------------------------------------------------
1816 PROCEDURE get_vendor_site_id (
1817 p_po_header_id IN NUMBER,
1818 x_vendor_site_id OUT NOCOPY NUMBER
1819 ) IS
1820 l_ga_flag po_headers_all.global_agreement_flag%TYPE := 'N';
1821 BEGIN
1822 --If the Doc is GA then get_vendor_site returns vendor_site_id
1823 --Else returns NULL
1824 x_vendor_site_id := PO_GA_PVT.get_vendor_site_id(p_po_header_id);
1825
1826 --Either the doc is not GA or the current org is not enabled.
1827 --For both of these cases, select site from the header
1828 IF x_vendor_site_id is NULL then
1829 x_vendor_site_id := PO_VENDOR_SITES_SV.get_vendor_site_id(
1830 p_po_header_id);
1831 END IF;
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834 x_vendor_site_id := NULL;
1835 END get_vendor_site_id;
1836 --<Shared Proc FPJ END>
1837
1838 END PO_SOURCING_RULES_SV;
[Home] [Help]