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.
  • October 14, 2015 | 15 views