Wednesday 14 October 2015

Assignment Set Conversion

Assignment Set:

Once sourcing rules has been defined, you must assign them to particular items and/or organizations. These assignments are grouped together in assignment sets. In an assignment set you can assign your sourcing rules at different levels as below,
                 Item,
                 Item Category,
                 Item-Organization,
                 Item Category-Organization,
                 Organization,
                 Global.

After defining the sourcing rule and assignment set one needs to enter the default assignment set to be used in the profile option  MRP:Default Sourcing Assignment Set.

Possible Validations:

1. Valid Sourcing Rule.
2. Valid Item.
3. Valid Organization.


Code for reference:

PROCEDURE create_src_rule_assignment
   IS
      lc_return_status           VARCHAR2 (1);
      ln_msg_count               NUMBER                                  := 0;
      lc_msg_data                VARCHAR2 (1000);
      lc_msg_data_temp           VARCHAR2 (1000);
      ln_msg_index_out           NUMBER;
      ln_count                   NUMBER;
      ln_org_cnt                 NUMBER;
      ln_vendor_cnt              NUMBER;
      lc_org_class               VARCHAR2 (3);
      ln_org_num                 NUMBER;
      ln_line_num                NUMBER                                  := 0;
      ln_err_count               NUMBER                                  := 0;
      l_assignment_set_rec       mrp_src_assignment_pub.assignment_set_rec_type;
      l_assignment_set_val_rec   mrp_src_assignment_pub.assignment_set_val_rec_type;
      l_assignment_tbl           mrp_src_assignment_pub.assignment_tbl_type;
      l_assignment_val_tbl       mrp_src_assignment_pub.assignment_val_tbl_type;
      x_assignment_set_rec       mrp_src_assignment_pub.assignment_set_rec_type;
      x_assignment_set_val_rec   mrp_src_assignment_pub.assignment_set_val_rec_type;
      x_assignment_tbl           mrp_src_assignment_pub.assignment_tbl_type;
      x_assignment_val_tbl       mrp_src_assignment_pub.assignment_val_tbl_type;
      ln_assigned_to             NUMBER;
      ln_org                     NUMBER;
      ln_rule_id                 NUMBER;
      ln_item                    NUMBER;
      ln_assign_id               NUMBER;
   BEGIN
      fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
                                  fnd_profile.VALUE ('RESP_ID'),
                                  fnd_profile.VALUE ('RESP_APPL_ID'),
                                  NULL,
                                  NULL
                                 );

      FOR i IN (SELECT *
                  FROM XX_STG_TBL2
                 WHERE process_flag = 'V')
      LOOP
         ln_org := NULL;
         ln_item := NULL;
         ln_assigned_to := NULL;
         ln_rule_id := NULL;
         ln_assign_id := NULL;

         BEGIN
            SELECT organization_id
              INTO ln_org
              FROM org_organization_definitions
             WHERE organization_code = i.organization_code;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                  'Error in Selecting Organization'
                                 );
         END;

         DBMS_OUTPUT.put_line ('After Organization Id');

         BEGIN
            SELECT inventory_item_id
              INTO ln_item
              FROM mtl_system_items_b
             WHERE segment1 = i.item AND organization_id = ln_org;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG, 'Error in Selecting item');
         END;

         DBMS_OUTPUT.put_line ('After Item Id');

         BEGIN
            SELECT sourcing_rule_id
              INTO ln_rule_id
              FROM mrp_sourcing_rules
             WHERE sourcing_rule_name = i.sourcing_rule_name;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                  'Error in Selecting sourcing rule'
                                 );
         END;

         DBMS_OUTPUT.put_line ('After Sourcing Rule');

         BEGIN
            SELECT lookup_code
              INTO ln_assigned_to
              FROM mfg_lookups
             WHERE lookup_type = 'MRP_ASSIGNMENT_TYPE'
               AND UPPER (meaning) = UPPER (i.assigned_to);
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                  'Error in Selecting Assignment type'
                                 );
         END;

         DBMS_OUTPUT.put_line ('After Assignment Set');

         BEGIN
            SELECT NVL (assignment_set_id, 0)
              INTO ln_assign_id
              FROM mrp_assignment_sets
             WHERE UPPER (assignment_set_name) = UPPER (i.assignment_set);
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_assign_id := 0;
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                  'Error in Selecting Assignment set id'
                                 );
         END;

         IF ln_assign_id = 0
         THEN
            l_assignment_set_rec.assignment_set_name := i.assignment_set;
            l_assignment_set_rec.description := i.description;
            l_assignment_set_rec.operation := 'CREATE';
         ELSE
            l_assignment_set_rec.assignment_set_id := ln_assign_id;
            l_assignment_set_rec.assignment_set_name := NULL;
            l_assignment_set_rec.operation := NULL;
         END IF;

         l_assignment_tbl (1).assignment_type := ln_assigned_to;
         l_assignment_tbl (1).operation := 'CREATE';
         l_assignment_tbl (1).organization_id := ln_org;
         l_assignment_tbl (1).inventory_item_id := ln_item;
         l_assignment_tbl (1).sourcing_rule_id := ln_rule_id;
         l_assignment_tbl (1).sourcing_rule_type := 1;
         DBMS_OUTPUT.put_line ('Before Assignment');
         mrp_src_assignment_pub.process_assignment
                        (p_api_version_number          => 1.0,
                         p_init_msg_list               => 'T',
                         p_return_values               => fnd_api.g_false,
                         p_commit                      => fnd_api.g_false,
                         x_return_status               => lc_return_status,
                         x_msg_count                   => ln_msg_count,
                         x_msg_data                    => lc_msg_data,
                         p_assignment_set_rec          => l_assignment_set_rec,
                         p_assignment_set_val_rec      => l_assignment_set_val_rec,
                         p_assignment_tbl              => l_assignment_tbl,
                         p_assignment_val_tbl          => l_assignment_val_tbl,
                         x_assignment_set_rec          => x_assignment_set_rec,
                         x_assignment_set_val_rec      => x_assignment_set_val_rec,
                         x_assignment_tbl              => x_assignment_tbl,
                         x_assignment_val_tbl          => x_assignment_val_tbl
                        );
         COMMIT;

         IF lc_return_status = fnd_api.g_ret_sts_success
         THEN
            DBMS_OUTPUT.put_line ('Success!');
            fnd_file.put_line (fnd_file.LOG, 'Success!');

            BEGIN
               UPDATE XX_STG_TBL2
                  SET process_flag = 'S',
                      assignment_set_id =
                                        x_assignment_set_rec.assignment_set_id
                WHERE record_id = i.record_id;
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.put_line (fnd_file.LOG,
                                     'Error in Updating Assignment set id'
                                    );
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG, lc_return_status);
            fnd_file.put_line (fnd_file.LOG, ln_msg_count);
            DBMS_OUTPUT.put_line ('lc_return_status' || lc_return_status);
            DBMS_OUTPUT.put_line ('count:' || ln_msg_count);

            IF (ln_msg_count > 0)
            THEN
               FOR l_index IN 1 .. ln_msg_count
               LOOP
                  lc_msg_data := lc_msg_data || CHR (13) || lc_msg_data_temp;
                  lc_msg_data_temp :=
                     fnd_msg_pub.get (p_msg_index      => l_index,
                                      p_encoded        => fnd_api.g_false
                                     );
                  fnd_file.put_line (fnd_file.LOG, lc_msg_data_temp);
               END LOOP;

               BEGIN
                  UPDATE XX_STG_TBL2
                     SET process_flag = 'E',
                         error_message = lc_msg_data_temp
                   WHERE record_id = i.record_id;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     fnd_file.put_line (fnd_file.LOG,
                                        'Error in Updating Error Flag'
                                       );
                     DBMS_OUTPUT.put_line ('EXCEPTION' || SQLERRM);
               END;
            END IF;

            COMMIT;
            DBMS_OUTPUT.put_line ('Failure!');
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Error in Calling API');
   END;
 Base Tables:
 MRP_ASSIGNMENT_SETS.

 MRP_SR_ASSIGNMENTS_V.

2 comments:

  1. Hi..
    Thank you for the article. I wanted to know if we can use the same API to update the sourcing rule for an existing customer? if yes can you please provide a sample script. Much appreciated.

    Thanks..

    ReplyDelete
  2. yes use L_ASSIGNMENT_TBL(1).ASSIGNMENT_ID:= XXXX and L_ASSIGNMENT_TBL(v_count).OPERATION := 'UPDATE'
    Assignment_id is required when you want to do update.
    replace XXXX with your assignment id for item.

    ReplyDelete