OPM : Create Recipe Validity Rules to access Recipe from respicted Organization

 Check and Recipe Validation Rule :

   SELECT b.RECIPE_VALIDITY_RULE_ID,a.RECIPE_ID,b.INVENTORY_ITEM_ID, b.ORGANIZATION_ID,org.ORGANIZATION_CODE, org.ORGANIZATION_ID,  b.*--, org.*
          FROM gmd_recipes a, GMD_RECIPE_VALIDITY_RULES  b,  xxfg_rcp_s ra, org_organization_definitions org
        where a.RECIPE_ID=b.RECIPE_ID(+)
        and b.ORGANIZATION_ID is null
        and a.RECIPE_NO=ra.ITEM_CODE and ra.ORG_CODE=org.ORGANIZATION_CODE  --and RECIPE_NO in ('886873','886389')
       

------------------
DECLARE
   CURSOR c_master IS
    
     
   SELECT a.ROW_ID,    a.RECIPE_ID,    a.RECIPE_DESCRIPTION,    a.RECIPE_NO,    a.RECIPE_VERSION,   org.ORGANIZATION_ID ORGANIZATION_ID,   a.OWNER_ORGANIZATION_ID,      a.FORMULA_ID,    a.ROUTING_ID,    a.PROJECT_ID,    a.RECIPE_STATUS,    a.PLANNED_PROCESS_LOSS,    a.CONTIGUOUS_IND,    a.TEXT_CODE,    a.DELETE_MARK,    a.ATTRIBUTE_CATEGORY,    a.ATTRIBUTE1,    a.CREATION_DATE,    a.CREATED_BY,    a.LAST_UPDATED_BY,    a.LAST_UPDATE_DATE,    a.LAST_UPDATE_LOGIN,    a.ATTRIBUTE2,    a.ATTRIBUTE3,    a.ATTRIBUTE4,    a.ATTRIBUTE5,    a.ATTRIBUTE6,    a.ATTRIBUTE7,    a.ATTRIBUTE8,    a.ATTRIBUTE9,    a.ATTRIBUTE10,    a.ATTRIBUTE11,    a.ATTRIBUTE12,    a.ATTRIBUTE13,    a.ATTRIBUTE14,    a.ATTRIBUTE15,    a.ATTRIBUTE16,    a.ATTRIBUTE17,    a.ATTRIBUTE18,    a.ATTRIBUTE19,    a.ATTRIBUTE20,    a.ATTRIBUTE21,    a.ATTRIBUTE22,    a.ATTRIBUTE23,    a.ATTRIBUTE24,    a.ATTRIBUTE25,    a.ATTRIBUTE26,    a.ATTRIBUTE27,    a.ATTRIBUTE28,    a.ATTRIBUTE29,    a.ATTRIBUTE30,    a.OWNER_ID,    a.OWNER_LAB_TYPE,    a.CALCULATE_STEP_QUANTITY,    a.RECIPE_TYPE,    a.ENHANCED_PI_IND,    a.MASTER_RECIPE_ID,    a.FIXED_PROCESS_LOSS,    a.FIXED_PROCESS_LOSS_UOM
        FROM gmd_recipes a, GMD_RECIPE_VALIDITY_RULES  b,  xxfg_rcp_s ra, org_organization_definitions org
        where a.RECIPE_ID=b.RECIPE_ID(+)
        and b.RECIPE_ID is null
        and a.RECIPE_NO=ra.ITEM_CODE and ra.ORG_CODE=org.ORGANIZATION_CODE;
    
   /*      
      SELECT a.*
        FROM gmd_recipes a, GMD_RECIPE_VALIDITY_RULES  b
        where a.RECIPE_ID=b.RECIPE_ID(+)
        and b.RECIPE_ID is null;
        */
        
      -- WHERE flag = 'Y;
 
     
   l_recipe_flex_tbl     gmd_recipe_detail.recipe_flex;
   l_recipe_vr_tbl       gmd_recipe_detail.recipe_vr_tbl;
   l_recipe              gmd_recipes%ROWTYPE;
   l_formula             FM_MATL_DTL%ROWTYPE;
   l_count               NUMBER                        := 0;
   l_count_mtl               NUMBER                        := 0;
   l_loop_cnt            NUMBER                        := 0;
   l_record_count        NUMBER                        := 0;
   l_data                VARCHAR2 (2000);
   l_data_mtl                VARCHAR2 (2000);
   l_return_status       VARCHAR2 (1);
   l_status              VARCHAR2 (1);
   i                     NUMBER                        := 1;
   l_dummy_cnt           NUMBER;
   l_api_version         NUMBER                        := 1;
   l_init_msg_list       BOOLEAN;
   l_commit              BOOLEAN;
   return_sts            BOOLEAN;
   v_routing_id          number;
   v_formula_id          number;
   v_routingstep_id      number;
   v_formulaline_id      number;
   organization_id  number;
 
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1187,22882,552,0);
   fnd_global.apps_initialize (user_id           => 1187,
                               resp_id           => 22883,
                               resp_appl_id      => 552
                              );
 
   l_init_msg_list := TRUE;
   l_commit := TRUE;
 
   i := 0;
 
   DBMS_OUTPUT.put_line ('Open cursor');
 
   OPEN c_master;
 
   LOOP
     FETCH c_master INTO l_recipe;
            exit WHEN c_master%NOTFOUND;
     
           
      l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no := l_recipe.recipe_no;
      l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_version := l_recipe.recipe_version;
      --l_recipe_vr_tbl (c_master%ROWCOUNT).organization_id := l_recipe.organization_id;
      --l_recipe_vr_tbl (c_master%ROWCOUNT).start_date := l_recipe.start_date;
     
     
 
      DBMS_OUTPUT.put_line ('Value Test ' || c_master%ROWCOUNT || ': '|| l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no);
     
     
   END LOOP;
 
    CLOSE c_master;
    DBMS_OUTPUT.put_line ('Close cursor');
   
      gmd_recipe_detail.create_recipe_vr
                                  (p_api_version             => 1.0,
                                   p_init_msg_list           => fnd_api.g_true,
                                   p_commit                  => fnd_api.g_true,    
                                   p_called_from_forms       => 'NO',                      
                                   x_return_status           => l_return_status,
                                   x_msg_count               => l_count,
                                   x_msg_data                => l_data,
                                   p_recipe_vr_tbl           => l_recipe_vr_tbl,
                                   p_recipe_vr_flex          => l_recipe_flex_tbl
                                  );                        
       
   UPDATE GMD_RECIPE_VALIDITY_RULES
      set VALIDITY_RULE_STATUS = 700
    WHERE VALIDITY_RULE_STATUS != 700;
 
   IF l_count >= 0
   THEN
      l_loop_cnt := 1;

      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line ('Record = ' || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data);

         IF l_status = 'E' OR l_status = 'U'
         THEN
            l_data := CONCAT ('ERROR ', l_data);
         END IF;

         DBMS_OUTPUT.put_line (l_data);

         IF (l_status = 'U')
         THEN
            l_return_status := l_status;
         ELSIF (l_status = 'E' AND l_return_status <> 'U')
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;

         l_loop_cnt := l_loop_cnt + 1;

         IF l_loop_cnt > l_count
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;
   COMMIT;

END;