Oracle APPS ITEM Assignment API- Oracle Apps R12

 

Oracle APPS ITEM Assignment API- Oracle Apps R12

CREATE TABLE APPS.XXPRAN_SUBINV_LOCATOR_STG_TEST

(

  ITEM_NUMBER  VARCHAR2(50 BYTE),

  ORG_CODE     VARCHAR2(4 BYTE),

  SUBINV_CODE  VARCHAR2(20 BYTE),

  USER_NAME    VARCHAR2(50 BYTE),

  STATUS       VARCHAR2(250 BYTE)

)

 

CREATE OR REPLACE PROCEDURE APPS.XXPRAN_INV_ASSIGN(P_USER IN VARCHAR2)

AS

/******* Cursor Declaration *************/

cursor c1 is

select distinct x.item_number,x.org_code,og.organization_id,mi.inventory_item_id

from apps.xxpran_subinv_locator_stg x,

mtl_system_items_b mi,

org_organization_definitions og

where 1=1 and x.user_name=P_USER

and x.item_number=mi.segment1 and mi.organization_id=102

and x.org_code=og.organization_code

and not exists (select 1 from mtl_system_items_b b where b.organization_id = og.organization_id and b.segment1=x.item_number)

 

 

;--P_USER;

 

/******* 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(4);

            l_item_id             number(20);

            l_flag                varchar2(4) default'A';

            l_msg                 varchar2(200);

            v_user_id             number;

            v_status              varchar2(2000);

             

  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';

           

            select user_id into v_user_id from fnd_user where user_name=P_USER;

    

            fnd_global.apps_initialize (v_user_id, l_resp_id, l_appl_id); --g_user_id

           

            --delete APPS.XXPRAN_SUBINV_LOCATOR_STG where item_number

            --not in (select segment1 from mtl_system_items_b where organization_id = 102);

            --commit; no need

           

            --12.2.10 Bug..17/10/2022 -- Jahangir  --Master - Child Conflict

           

            update inv.mtl_system_items_b b set b.mrp_calculate_atp_flag='N'

            where b.organization_id=102 and b.mrp_calculate_atp_flag is null

            and b.segment1 in (select distinct x.item_number from apps.xxpran_subinv_locator_stg x);

            commit;

           

            FOR x1 IN c1 LOOP

             /*

              ---no need to checking, join query already exists

               --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 ;

                    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;

                */

               

                l_item_id:=x1.inventory_item_id;

                l_org_id :=x1.organization_id;

                l_flag:='A';

               

                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);

                    --modified by zahid 14-Aug-2014

                   -- delete from APPS.XXPRAN_SUBINV_LOCATOR_STG where item_number=x1.item_number and org_code= x1.org_code;

                    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);

                        v_status:=v_status||' '||x_message_list(j).message_text;

                        END LOOP;

                        update apps.xxpran_subinv_locator_stg set status=x_return_status ||' '||trim(substr(v_status,1,200))

                        where item_number=x1.item_number and org_code= x1.org_code;

                    else

                        update apps.xxpran_subinv_locator_stg set status=x_return_status

                        where item_number=x1.item_number and org_code= x1.org_code;

                    commit;

                    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);

              null;

    END;

/

 

 

CREATE OR REPLACE PROCEDURE APPS.XXPRAN_SUBINV_LOCATOR(P_USER IN VARCHAR2)

IS

    ----------------

   

    M_EXISTS    VARCHAR2 (1);

    L_ERR_FLAG  CHAR (1) := 'N';

    V_USER_ID   NUMBER :=-1;

   

    CURSOR C1 IS

    SELECT DISTINCT I.INVENTORY_ITEM_ID ITEMNO, IG.ITEM_NUMBER ITEM, O.ORGANIZATION_ID,O.ORGANIZATION_CODE, IG.SUBINV_CODE

    FROM XXPRAN_SUBINV_LOCATOR_STG IG, ORG_ORGANIZATION_DEFINITIONS O, MTL_SYSTEM_ITEMS_B I

    WHERE IG.USER_NAME=P_USER

    AND IG.ORG_CODE=O.ORGANIZATION_CODE

    AND IG.ITEM_NUMBER=I.SEGMENT1 --and IG.ITEM_NUMBER='72001'

    AND O.ORGANIZATION_ID=I.ORGANIZATION_ID

    AND NOT EXISTS (

    SELECT 1 FROM APPS.MTL_ITEM_SUB_INVENTORIES

    WHERE   INVENTORY_ITEM_ID   = I.INVENTORY_ITEM_ID

    AND     ORGANIZATION_ID     = O.ORGANIZATION_ID

    AND     UPPER(SECONDARY_INVENTORY) = UPPER(IG.SUBINV_CODE))

    ORDER BY O.ORGANIZATION_CODE,IG.ITEM_NUMBER,IG.SUBINV_CODE

    ;

    

    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)

    ;

                       

   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

 

    SELECT USER_ID INTO V_USER_ID FROM FND_USER WHERE USER_NAME=P_USER;

 

     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,

                       V_USER_ID,--R_SUB_INV.LAST_UPDATED_BY,

                       R_SUB_INV.CREATION_DATE,

                       V_USER_ID, --R_SUB_INV.CREATED_BY,

                       -1,--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,

                            V_USER_ID,--R_SEC_LOC.LAST_UPDATED_BY,

                            R_SEC_LOC.CREATION_DATE,

                            V_USER_ID,--R_SEC_LOC.CREATED_BY,

                            -1,--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;

/