[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.
440:
441: /* Bug No:1515981 Forcing the use of following hint to improve the performance*/
442:
443: ----
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
443: ----
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)
573: -- Assign at Item level
574: ----
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
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(
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,
842:
843: --bug10330313
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:
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 ;
1459:
1460: ----
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:
1507: END;
1508: ----
1509: END IF;
1510:
1511: INSERT INTO MRP_SR_ASSIGNMENTS(
1512: assignment_id,
1513: assignment_type,
1514: sourcing_rule_id,
1515: sourcing_rule_type,
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), ----
1527: x_sourcing_rule_id,
1528: l_sourcing_rule_type, ----
1529: x_assignment_set_id,
1537: l_organization_id, ----
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: