[Home] [Help]
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.
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.
433:
434: /* Bug No:1515981 Forcing the use of following hint to improve the performance*/
435:
436: ----
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
436: ----
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)
565: -- Assign at Item level
566: ----
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
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(
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,
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:
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 ;
1379:
1380: ----
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:
1427: END;
1428: ----
1429: END IF;
1430:
1431: INSERT INTO MRP_SR_ASSIGNMENTS(
1432: assignment_id,
1433: assignment_type,
1434: sourcing_rule_id,
1435: sourcing_rule_type,
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), ----
1447: x_sourcing_rule_id,
1448: l_sourcing_rule_type, ----
1449: x_assignment_set_id,
1457: l_organization_id, ----
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: