For Lot Control Apply

CREATE OR REPLACE PROCEDURE APPS.pran_item_stock_stg_proc_RLOT as
cursor c1 is

SELECT TRANSACTION_INTERFACE_ID,    SOURCE_CODE,    SOURCE_LINE_ID,    SOURCE_HEADER_ID,    INVENTORY_ITEM_ID,    INVENTORY_ITEM_CODE,    PRIMARY_UOM_CODE,    PROCESS_FLAG,    VALIDATION_REQUIRED,    TRANSACTION_MODE,    -1*SUM(TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,    ORGANIZATION_ID,    ORGANIZATION_CODE,    PRIMARY_QUANTITY,    TRANSACTION_UOM,    TRANSACTION_DATE,    SUBINVENTORY_CODE,    LOCATOR_ID,    TRANSACTION_TYPE_ID,    TRANSACTION_COST,    DISTRIBUTION_ACCOUNT_ID,    LAST_UPDATE_DATE,    LAST_UPDATED_BY,    CREATION_DATE,    CREATED_BY FROM (
select distinct transaction_interface_id,source_code,source_line_id,
source_header_id,i.inventory_item_id, inventory_item_code , primary_uom_code ,process_flag,validation_required,
transaction_mode,transaction_quantity,o.organization_id, o.organization_code, primary_quantity,
primary_uom_code transaction_uom ,transaction_date ,subinventory_code,locator_id,
transaction_type_id,transaction_cost,op.MATERIAL_ACCOUNT distribution_account_id,
si.last_update_date,si.last_updated_by,si.creation_date,si.created_by from pran_item_stock_stg_M si, mtl_system_items_b i, org_organization_definitions o, mtl_parameters op
where o.ORGANIZATION_ID=op.ORGANIZATION_ID and op.ORGANIZATION_CODE=si.ORGANIZATION_CODE and  si.INVENTORY_ITEM_CODE=i.SEGMENT1
and si.ORGANIZATION_CODE=o.ORGANIZATION_CODE and o.ORGANIZATION_ID=i.ORGANIZATION_ID --and FLAG ='Y'--is null
 --and INVENTORY_ITEM_CODE='47428'  and o.ORGANIZATION_CODE='D27'
 )
 GROUP BY TRANSACTION_INTERFACE_ID,    SOURCE_CODE,    SOURCE_LINE_ID,    SOURCE_HEADER_ID,    INVENTORY_ITEM_ID,    INVENTORY_ITEM_CODE,    PRIMARY_UOM_CODE,    PROCESS_FLAG,    VALIDATION_REQUIRED,    TRANSACTION_MODE,     ORGANIZATION_ID,    ORGANIZATION_CODE,    PRIMARY_QUANTITY,    TRANSACTION_UOM,    TRANSACTION_DATE,    SUBINVENTORY_CODE,    LOCATOR_ID,    TRANSACTION_TYPE_ID,    TRANSACTION_COST,    DISTRIBUTION_ACCOUNT_ID,    LAST_UPDATE_DATE,    LAST_UPDATED_BY,    CREATION_DATE,    CREATED_BY;


c1_rec c1%rowtype;
v_organization_id number(5);
v_inventory_item_id number(10);
v_primary_uom_code varchar2(6);
v_inventory_location_id number(10);
v_subinventory_code varchar2(10);
v_code_combination_id number(10);
v_transaction_type_id number(8);
   interface_id number := 0;
Begin




for c1_rec in c1 loop
exit when c1%notfound;

begin

-- location
select
inventory_location_id,
subinventory_code
into v_inventory_location_id, v_subinventory_code
from mtl_item_locations
where subinventory_code =c1_rec.subinventory_code;


--Declare

--begin
  select mtl_material_transactions_s.nextval
  into interface_id
  from dual;



exception
when no_data_found then

v_organization_id :=null;
v_inventory_item_id :=null;
v_primary_uom_code :=null;
v_inventory_location_id :=null;
v_subinventory_code :=null;


end;


 

if

v_inventory_location_id is not null
and v_subinventory_code is not null then

update
pran_item_stock_stg_m set flag='Y' where
INVENTORY_ITEM_CODE=c1_rec.INVENTORY_ITEM_CODE
and organization_code =c1_rec.organization_code
and SUBINVENTORY_CODE =c1_rec.SUBINVENTORY_CODE
;



insert into mtl_transactions_interface
(
transaction_interface_id,
source_code,
source_header_id,
source_line_id,
inventory_item_id,
process_flag,
validation_required,
transaction_mode,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
transaction_date,
transaction_type_id,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id,
TRANSACTION_COST,
SOURCE_LOT_NUMBER
)
values
(
--mtl_material_transactions_s.NEXTVAL,
interface_id,
--c1_rec.source_code,
'Stock Upload for Lot Control',
1,
1,
c1_rec.inventory_item_id,
1,
2,
3,
c1_rec.transaction_quantity,
sysdate,
1187,
sysdate,
1187,
c1_rec.organization_id,
--c1_rec.transaction_date,
to_date('09/01/2021','dd-mm-yy'),
--
42,
c1_rec.distribution_account_id,
--128305,
c1_rec.transaction_uom,
c1_rec.subinventory_code,
--c1_rec.locator_id,
v_inventory_location_id,
null,
'09012021'
);

else

update pran_item_stock_stg_m set flag='N' where
inventory_item_code=c1_rec.inventory_item_code
and organization_code =c1_rec.organization_code
and transaction_uom =c1_rec.transaction_uom
and locator_id =c1_rec.locator_id
and subinventory_code =c1_rec.subinventory_code
and distribution_account_id =c1_rec.distribution_account_id
and transaction_type_id =c1_rec.transaction_type_id;

end if;



Insert into apps.mtl_transaction_lots_interface
( transaction_interface_id
, source_code
, source_line_id
, lot_number
, lot_expiration_date
, transaction_quantity
  , PRIMARY_QUANTITY
, last_update_date
, last_updated_by
, creation_date
, created_by
)
Values
(interface_id
, 'Stock Upload for Lot Control'
, 1
, '09012021'
, to_date('09/01/2021','dd-mm-yy')
, c1_rec.transaction_quantity
, null
, SYSDATE
, 1187
, SYSDATE
, 1187
);

commit;
end loop;


End pran_item_stock_stg_proc_RLOT;
/

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;