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;

/

 

 

 

OPM FORMULA UPLOAD PROCESS API-ORACLE APPS R12-US

 

 

CREATE TABLE XXCUST.XXJLN_FORMULA_HEADERS_RRRRRR

(

  FORMULA_NO               VARCHAR2(32 BYTE)    NOT NULL,

  FORMULA_VERS             NUMBER               DEFAULT 1                     NOT NULL,

  FORMULA_TYPE             NUMBER(5)            DEFAULT 1                     NOT NULL,

  FORMULA_DESC1            VARCHAR2(70 BYTE),

  FORMULA_CLASS            VARCHAR2(8 BYTE),

  INACTIVE_IND             NUMBER(5)            DEFAULT 0                     NOT NULL,

  FORMULA_UOM              VARCHAR2(4 BYTE),

  FORMULA_STATUS           VARCHAR2(30 BYTE)    DEFAULT 100                   NOT NULL,

  FLAG                     VARCHAR2(2 BYTE),

  RECORD_TYPE              VARCHAR2(12 BYTE),

  OWNER_ORGANIZATION_CODE  VARCHAR2(10 BYTE)    DEFAULT 'FMO'                 NOT NULL,

  OWNER_NAME               VARCHAR2(20 BYTE)    DEFAULT '34220'               NOT NULL,

  LINE_TYPE                NUMBER(10),

  LINE_NO                  INTEGER              DEFAULT 1                     NOT NULL,

  QTY                      NUMBER(20,10),

  RELEASE_TYPE             VARCHAR2(10 BYTE),

  COST_ALLOC               VARCHAR2(10 BYTE),

  SCALE_TYPE_HDR           NUMBER(10)           DEFAULT 1                     NOT NULL,

  DETAIL_UOM               VARCHAR2(10 BYTE),

  SCALE_TYPE_DTL           INTEGER              DEFAULT 1                     NOT NULL,

  PHANTOM_TYPE             INTEGER              DEFAULT 0                     NOT NULL,

  DELETE_MARK              VARCHAR2(10 BYTE)    DEFAULT 0                     NOT NULL,

  CONTRIBUTE_YIELD_IND     VARCHAR2(10 BYTE)    DEFAULT 'Y'                   NOT NULL,

  INVENTORY_ITEM_CODE      VARCHAR2(15 BYTE),

  TYPE_OF_ITEM             VARCHAR2(250 BYTE),

  FILE_NAME                VARCHAR2(250 BYTE),

  ORG_CODE                 VARCHAR2(200 BYTE),

  ORG_NAME                 VARCHAR2(300 BYTE),

  NOTE                     VARCHAR2(250 BYTE)

)

 

--------------------------------PROCESS-------------------------------------

 

DECLARE

begin

 

/*

----------First time for Item Assignmet-------------

 

 

delete from APPS.xxpran_subinv_locator_stg_rrr

where USER_NAME='333';

commit;

 

 

INSERT INTO  APPS.xxpran_subinv_locator_stg(ITEM_NUMBER,ORG_CODE,SUBINV_CODE,USER_NAME)

SELECT distinct INVENTORY_ITEM_CODE,'FMO','FMO','34220'

FROM XXCUST.XXJLN_FORMULA_HEADERS_REZA

where FLAG is null;

commit;

 

 

 

delete

FROM APPS.xxpran_subinv_locator_stg WHERE ITEM_NUMBER NOT IN (

SELECT BB.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B BB WHERE BB.ORGANIZATION_ID=102 AND BB.SEGMENT1 IN (

SELECT ITEM_NUMBER FROM APPS.xxpran_subinv_locator_stg

));

commit;

 

 

apps.xxpran_inv_assign('xxxxx');

 

*/

 

----------After run one time then off this------------------

 

 

DECLARE

   CURSOR c_header IS

  

      SELECT DISTINCT formula_no

      FROM     xxcust.XXJLN_FORMULA_HEADERS_REZA a

       WHERE flag IS  NULL

       and  not exists (select 1 from  fm_form_mst b where a.FORMULA_NO = b.formula_no);

     

   CURSOR c_master  (

      p_formula     varchar2   --TEST_M020

   ) IS

      SELECT *

        FROM     xxcust.XXJLN_FORMULA_HEADERS_REZA --mii_gmd_formula

       WHERE flag IS NULL

         AND formula_no = p_formula;

     

   l_formula_header_tbl   gmd_formula_pub.formula_insert_hdr_tbl_type;

   l_formula             xxcust.XXJLN_FORMULA_HEADERS_REZA%ROWTYPE; -- mii_gmd_formula%ROWTYPE;

   l_count                NUMBER                                      := 0;

   l_loop_cnt             NUMBER                                      := 0;

   l_record_count         NUMBER                                      := 0;

   l_data                 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_item_id              number;

   v_organization_id      number;

   v_user_id                            number;

   v_total_form                      number;

BEGIN

--FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);

   fnd_global.apps_initialize (user_id           => 1110,

                               resp_id           => 50774,

                               resp_appl_id      => 555

                              );

                             

                            --  FND_RESPONSIBILITY_VL ,  FND_user

                           

                             

/*FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, resp_appl_id);*/

   l_init_msg_list := TRUE;

   l_commit := TRUE;

 

FOR l_header IN c_header 

LOOP

   OPEN c_master(l_header.formula_no);

   LOOP

      FETCH c_master INTO l_formula;

      exit WHEN c_master%NOTFOUND;

    

      dbms_output.put_line (c_master%ROWCOUNT);

      BEGIN

         SELECT DISTINCT inventory_item_id

           INTO v_item_id

           FROM mtl_system_items_b

          WHERE segment1 = UPPER(l_formula.inventory_item_code);

     

         SELECT organization_id

          INTO v_organization_id

           FROM mtl_parameters

          WHERE organization_code = l_formula.owner_organization_code;

     

         SELECT user_id

           INTO v_user_id

           FROM fnd_user

          WHERE user_name = l_formula.owner_name;

         

      EXCEPTION

         WHEN others THEN

            UPDATE xxcust.XXJLN_FORMULA_HEADERS_REZA --mii_gmd_formula

             set flag = 'E'

             --, note = 'Ada exception'

            WHERE formula_no = l_formula.formula_no;

      END;

 

 

      l_formula_header_tbl (c_master%ROWCOUNT).record_type := 'I';

      l_formula_header_tbl (c_master%ROWCOUNT).formula_no := UPPER(l_formula.formula_no);

      l_formula_header_tbl (c_master%ROWCOUNT).formula_vers := l_formula.formula_vers;

      l_formula_header_tbl (c_master%ROWCOUNT).formula_type := l_formula.formula_type;

      l_formula_header_tbl (c_master%ROWCOUNT).formula_desc1 := l_formula.formula_desc1;

      l_formula_header_tbl (c_master%ROWCOUNT).formula_class := l_formula.formula_class;

      l_formula_header_tbl (c_master%ROWCOUNT).inactive_ind := l_formula.inactive_ind;

      l_formula_header_tbl (c_master%ROWCOUNT).owner_organization_id := v_organization_id;

      l_formula_header_tbl (c_master%ROWCOUNT).formula_status := l_formula.formula_status;

      l_formula_header_tbl (c_master%ROWCOUNT).owner_id := v_user_id;

      l_formula_header_tbl (c_master%ROWCOUNT).line_type := l_formula.line_type;

      l_formula_header_tbl (c_master%ROWCOUNT).line_no := l_formula.line_no;

      l_formula_header_tbl (c_master%ROWCOUNT).inventory_item_id := v_item_id;

      l_formula_header_tbl (c_master%ROWCOUNT).qty := l_formula.qty;

      l_formula_header_tbl (c_master%ROWCOUNT).detail_uom := l_formula.detail_uom;

      l_formula_header_tbl (c_master%ROWCOUNT).release_type := l_formula.release_type;

      l_formula_header_tbl (c_master%ROWCOUNT).scale_type_hdr := l_formula.scale_type_hdr;

      l_formula_header_tbl (c_master%ROWCOUNT).scale_type_dtl := l_formula.scale_type_dtl;

      l_formula_header_tbl (c_master%ROWCOUNT).cost_alloc := l_formula.cost_alloc;

      l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := 'Y'; -- l_formula.CONTRIBUTE_YIELD_IND;

      l_formula_header_tbl (c_master%ROWCOUNT).PHANTOM_TYPE := l_formula.PHANTOM_TYPE;

      l_formula_header_tbl (c_master%ROWCOUNT).delete_mark := l_formula.delete_mark;

      l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := 'Y';

    

    

      DBMS_OUTPUT.put_line ('Value Test ' || l_formula_header_tbl (1).formula_no);

     

   

 

    

--      UPDATE mii_gmd_formula

--         set flag = 'Y'

--       WHERE formula_no = l_formula.formula_no

--         AND line_no = l_formula.line_no

--         AND inventory_item_code = l_formula.inventory_item_code;

        

   END LOOP;

 

   CLOSE c_master;

 

   gmd_formula_pub.insert_formula

                                (p_api_version             => 1.0,

                                 p_formula_header_tbl      => l_formula_header_tbl,

                                 x_return_status           => l_return_status,

                                 x_msg_count               => l_count,

                                 x_msg_data                => l_data

                                );

                                DBMS_OUTPUT.put_line ('l_return_status:'||l_return_status);

                                DBMS_OUTPUT.put_line ('l_data:'||l_data);

                              

   IF l_return_status = 'E' OR l_return_status = 'U'

         THEN

            UPDATE xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula

             set flag = l_return_status

             --, note = l_data

       WHERE formula_no = l_formula.formula_no;

  ELSE

     UPDATE xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula

             set flag = 'Y'

--             , note = l_data

       WHERE formula_no = l_formula.formula_no;

   END IF;                           

                                  

END LOOP;

/*

   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;

 

 

update XXCUST.XXJLN_FORMULA_HEADERS_RRRR

set FLAG =null

where FLAG = 'E';

commit;

 

---select count(1) into v_total_form  from XXCUST.XXJLN_FORMULA_HEADERS_REZA a where (a.flag is null or  a.flag !='Y');

 

END;

END;

/

/

/

/

/

/

/

/

/

/