Item Assign to an Organization

01. Create a temp Table:
xxpran_item_assig_stg(item_number,ORG_CODE,MSG,SUBINV_CODE)

02.Insert required data to this temp table.
declare
cursor c1 is select * from xxpran_item_assig_stg;
/******* Variable Declaration ************/

g_user_id fnd_user.user_id%TYPE :=NULL;
l_appl_id fnd_application.application_id%TYPE;
l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_false;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_org_id number(3);
l_item_id number(20);
l_flag varchar2(4) default'A';
l_msg varchar2(200);
BEGIN
SELECT fa.application_id INTO l_appl_id
FROM fnd_application fa
WHERE fa.application_short_name = 'INV';

SELECT fr.responsibility_id INTO l_resp_id
FROM fnd_application fa, fnd_responsibility_tl fr
WHERE fa.application_short_name = 'INV'
AND fa.application_id = fr.application_id
AND UPPER (fr.responsibility_name) = 'INVENTORY';

fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

FOR x1 IN c1 LOOP
/* Validation For Organization */
begin
select organization_id into l_org_id
from ORG_ORGANIZATION_DEFINITIONS
where organization_code = x1.org_code;
exception
when others then
l_flag :='E';
l_msg :='Organization Code'||x1.org_code||' not in system';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;

/* Validation For Inventory Item Id */
begin
select inventory_item_id into l_item_id
from mtl_system_items_b
where segment1 = x1.item_number
and organization_id = 102 --- IMO org id
;
exception
when others then
l_flag :='E';
l_msg :='Inventory Item Sengent1 '||x1.item_number||' not in system';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;

if l_flag!='E' then
EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_INVENTORY_ITEM_ID => l_item_id
-- , p_item_number => 000000000001035
, p_organization_id => l_org_id
-- , P_ORGANIZATION_CODE => 'DXN'
-- , P_PRIMARY_UOM_CODE => 'EA'
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);
fnd_file.PUT_LINE(fnd_file.log,'Status: '||x_return_status);

-- update item status flag and those items need to assign to subinventory
update xxpran_item_assig_stg set msg='AGN_OK' where item_number=x1.item_number;
-- delete from xxpran_item_assig_stg where item_number=x1.item_number;
--
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
fnd_file.PUT_LINE(fnd_file.log,'Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR j IN 1..x_message_list.COUNT LOOP
fnd_file.PUT_LINE(fnd_file.log,x_message_list(j).message_text);
END LOOP;
END IF;
end if;

END LOOP;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(fnd_file.log,'Exception Occured :');
fnd_file.PUT_LINE(fnd_file.log,SQLCODE ||':'||SQLERRM);
END;
/



EXEC APPS.XXPFL_SUBINV_M('M');


CREATE OR REPLACE PROCEDURE APPS.XXPFL_SUBINV_M(MM IN VARCHAR2) IS

    M_EXISTS VARCHAR2 (1);
    L_ERR_FLAG CHAR (1) := 'N';

    CURSOR C1 IS
   
    SELECT DISTINCT i.INVENTORY_ITEM_ID ITEMNO, ig.ITEM_NUMBER ITEM, o.ORGANIZATION_ID,ORGANIZATION_CODE, SUBINV_CODE
    FROM xxpran_item_assig_stg IG, ORG_ORGANIZATION_DEFINITIONS O, mtl_system_items_b i
    WHERE MSG='AGN_OK'   
    AND IG.ORG_CODE=O.ORGANIZATION_CODE
    and ig.ITEM_NUMBER=i.SEGMENT1
    and i.ORGANIZATION_ID=102;
    
  --  CURSOR C1 IS    SELECT  ORGANIZATION_ID,ORGANIZATION_CODE    FROM    org_organization_definitions  WHERE   ORGANIZATION_ID not in(102);

    -----  CURSOR SUB_INV(P_ORG NUMBER,P1_ITEM_ID NUMBER )  IS
    CURSOR SUB_INV(P_ORG NUMBER,P_SEC_INV VARCHAR2,P1_ITEM_ID NUMBER) IS
   
   
                        SELECT  MSI.INVENTORY_ITEM_ID,MP.ORGANIZATION_ID,
                                MS.SECONDARY_INVENTORY_NAME     SECONDARY_INVENTORY,
                                SYSDATE                         LAST_UPDATE_DATE,
                                1110                            LAST_UPDATED_BY,
                                SYSDATE                         CREATION_DATE,
                                1110                            CREATED_BY,
                                75516                           LAST_UPDATE_LOGIN,
                                6                               INVENTORY_PLANNING_CODE
                        FROM    MTL_SECONDARY_INVENTORIES   MS,
                                MTL_PARAMETERS              MP,
                                MTL_SYSTEM_ITEMS_B          MSI
                        WHERE   MS.ORGANIZATION_ID = MP.ORGANIZATION_ID
                        AND     MS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                        AND     MS.ORGANIZATION_ID = P_ORG
                        AND     MSI.INVENTORY_ITEM_ID = P1_ITEM_ID
                        AND     UPPER(MS.SECONDARY_INVENTORY_NAME) LIKE UPPER(P_SEC_INV)
                       
                       
                     --  AND     UPPER(MS.SECONDARY_INVENTORY_NAME) LIKE '%RM'
                      
                       ;
                       --AND UPPER(MS.SECONDARY_INVENTORY_NAME) LIKE '%FG';

    CURSOR  SEC_LOC(P_ORG NUMBER,P_SEC_INV VARCHAR2,P_ITEM_ID NUMBER) IS
   
                       SELECT   MSI.INVENTORY_ITEM_ID,
                                MP.ORGANIZATION_ID,
                                MIL.INVENTORY_LOCATION_ID   SECONDARY_LOCATOR,
                                SYSDATE                     LAST_UPDATE_DATE,
                                1110                        LAST_UPDATED_BY,
                                SYSDATE                     CREATION_DATE,
                                1110                        CREATED_BY,
                                75516                       LAST_UPDATE_LOGIN
                        FROM    MTL_PARAMETERS              MP,
                                MTL_SYSTEM_ITEMS_B          MSI,
                                MTL_ITEM_LOCATIONS          MIL
                        WHERE   MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
                        AND     MSI.ORGANIZATION_ID = MIL.ORGANIZATION_ID
                        AND     MSI.ORGANIZATION_ID = P_ORG
                        AND     MSI.INVENTORY_ITEM_ID = P_ITEM_ID
                        AND     UPPER(MIL.SUBINVENTORY_CODE) LIKE UPPER(P_SEC_INV);

BEGIN
     FOR I IN C1 LOOP
      IF (L_ERR_FLAG <> 'Y') THEN
        FOR R_SUB_INV IN SUB_INV(I.ORGANIZATION_ID, I.SUBINV_CODE, I.ITEMNO) LOOP
          -- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING --
          begin
            SELECT 'M'
            INTO M_EXISTS
            FROM    APPS.MTL_ITEM_SUB_INVENTORIES
            WHERE   INVENTORY_ITEM_ID   = R_SUB_INV.INVENTORY_ITEM_ID
            AND     ORGANIZATION_ID     = R_SUB_INV.ORGANIZATION_ID
            AND     UPPER(SECONDARY_INVENTORY) LIKE  UPPER(R_SUB_INV.SECONDARY_INVENTORY);
            exception
            when others then
                DBMS_OUTPUT.PUT_LINE('error -'||SQLERRM||R_SUB_INV.INVENTORY_ITEM_ID);
                M_EXISTS := 'N';
            end;

            IF (M_EXISTS <> 'M')  THEN
                BEGIN
                    INSERT INTO MTL_ITEM_SUB_INVENTORIES
                    (
                       INVENTORY_ITEM_ID,
                       ORGANIZATION_ID,
                       SECONDARY_INVENTORY,
                       LAST_UPDATE_DATE,
                       LAST_UPDATED_BY,
                       CREATION_DATE,
                       CREATED_BY,
                       LAST_UPDATE_LOGIN,
                       INVENTORY_PLANNING_CODE
                    )
                    VALUES
                    (  R_SUB_INV.INVENTORY_ITEM_ID,
                       R_SUB_INV.ORGANIZATION_ID,
                       R_SUB_INV.SECONDARY_INVENTORY,
                       R_SUB_INV.LAST_UPDATE_DATE,
                       R_SUB_INV.LAST_UPDATED_BY,
                       R_SUB_INV.CREATION_DATE,
                       R_SUB_INV.CREATED_BY,
                       R_SUB_INV.LAST_UPDATE_LOGIN,
                       R_SUB_INV.INVENTORY_PLANNING_CODE
                        );

                 FOR R_SEC_LOC IN SEC_LOC(I.ORGANIZATION_ID,R_SUB_INV.SECONDARY_INVENTORY,R_SUB_INV.INVENTORY_ITEM_ID) LOOP
                    INSERT INTO MTL_SECONDARY_LOCATORS
                        (
                            INVENTORY_ITEM_ID,
                            ORGANIZATION_ID,
                            SECONDARY_LOCATOR,
                            LAST_UPDATE_DATE,
                            LAST_UPDATED_BY,
                            CREATION_DATE,
                            CREATED_BY,
                            LAST_UPDATE_LOGIN,
                            SUBINVENTORY_CODE
                        )
                    VALUES
                        (   R_SEC_LOC.INVENTORY_ITEM_ID,
                            R_SEC_LOC.ORGANIZATION_ID,
                            R_SEC_LOC.SECONDARY_LOCATOR,
                            R_SEC_LOC.LAST_UPDATE_DATE,
                            R_SEC_LOC.LAST_UPDATED_BY,
                            R_SEC_LOC.CREATION_DATE,
                            R_SEC_LOC.CREATED_BY,
                            R_SEC_LOC.LAST_UPDATE_LOGIN,
                            R_SUB_INV.SECONDARY_INVENTORY
                        );
                       
                 END LOOP; -- LOCATOR
                COMMIT;
               
                 END;
          ELSE
              DBMS_OUTPUT.PUT_LINE('Combination Exist For Item id -'||R_SUB_INV.INVENTORY_ITEM_ID||'- Org -'||R_SUB_INV.ORGANIZATION_ID||'- SubInv -'||R_SUB_INV.SECONDARY_INVENTORY);
          END IF;

        END LOOP; -- SUB INVENTORY
        END IF;

     END LOOP; -- ITEM----ORGANIZATION

EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
    FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error - '||SQLERRM);
END;
/