DBA Data[Home] [Help]

APPS.BIS_FUNCTIONAL_AREA_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 49

    IF(l_Func_Area_Rec.Last_Updated_By IS NULL) THEN
        l_Func_Area_Rec.Last_Updated_By := l_Func_Area_Rec.Created_By;
Line: 53

    IF(l_Func_Area_Rec.Last_Update_Login IS NULL) THEN
        l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
Line: 57

    IF(l_Func_Area_Rec.Last_Update_Date IS NULL) THEN
       l_Func_Area_Rec.Creation_Date    := SYSDATE;
Line: 59

       l_Func_Area_Rec.Last_Update_Date := SYSDATE;
Line: 61

       l_Func_Area_Rec.Creation_Date    := l_Func_Area_Rec.Last_Update_Date;
Line: 65

    INSERT INTO BIS_FUNCTIONAL_AREAS
    (
        FUNCTIONAL_AREA_ID
      , SHORT_NAME
      , CREATED_BY
      , CREATION_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , LAST_UPDATE_LOGIN
    )
    VALUES
    (
        l_Func_Area_Rec.Functional_Area_Id
      , l_Func_Area_Rec.Short_Name
      , l_Func_Area_Rec.Created_By
      , l_Func_Area_Rec.Creation_Date
      , l_Func_Area_Rec.Last_Updated_By
      , l_Func_Area_Rec.Last_Update_Date
      , l_Func_Area_Rec.Last_Update_Login
    );
Line: 87

    INSERT INTO BIS_FUNCTIONAL_AREAS_TL
    (
        FUNCTIONAL_AREA_ID
      , NAME
      , DESCRIPTION
      , LANGUAGE
      , SOURCE_LANG
      , CREATED_BY
      , CREATION_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , LAST_UPDATE_LOGIN
    )
    SELECT
        l_Func_Area_Rec.Functional_Area_Id
      , l_Func_Area_Rec.Name
      , l_Func_Area_Rec.Description
      , L.LANGUAGE_CODE
      , USERENV('LANG')
      , l_Func_Area_Rec.Created_By
      , l_Func_Area_Rec.Creation_Date
      , l_Func_Area_Rec.Last_Updated_By
      , l_Func_Area_Rec.Last_Update_Date
      , l_Func_Area_Rec.Last_Update_Login
   FROM  FND_LANGUAGES L
   WHERE L.INSTALLED_FLAG IN ('I', 'B')
   AND   NOT EXISTS
        (
          SELECT NULL
          FROM   BIS_FUNCTIONAL_AREAS_TL T
          WHERE  T.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
          AND    T.LANGUAGE           = L.LANGUAGE_CODE
        );
Line: 168

PROCEDURE Update_Functional_Area(
  p_Api_Version         IN          NUMBER
 ,p_Commit              IN          VARCHAR2 := FND_API.G_FALSE
 ,p_Func_Area_Rec       IN          BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
 ,x_Return_Status       OUT NOCOPY  VARCHAR2
 ,x_Msg_Count           OUT NOCOPY  NUMBER
 ,x_Msg_Data            OUT NOCOPY  VARCHAR2
) IS
  l_Count                  NUMBER;
Line: 179

    SAVEPOINT UpdateFuncAreaSP_Pvt;
Line: 206

    IF(p_Func_Area_Rec.Last_Update_Date IS NULL) THEN
       l_Func_Area_Rec.Last_Update_Date := SYSDATE;
Line: 209

       l_Func_Area_Rec.Last_Update_Date := p_Func_Area_Rec.Last_Update_Date ;
Line: 212

    IF (p_Func_Area_Rec.Last_Updated_By IS NULL) THEN
      l_Func_Area_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
Line: 215

      l_Func_Area_Rec.Last_Updated_By := p_Func_Area_Rec.Last_Updated_By;
Line: 218

    IF (p_Func_Area_Rec.Last_Update_Login IS NULL) THEN
      l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
Line: 221

      l_Func_Area_Rec.Last_Update_Login := p_Func_Area_Rec.Last_Update_Login;
Line: 225

    UPDATE BIS_FUNCTIONAL_AREAS
    SET
        LAST_UPDATED_BY    = l_Func_Area_Rec.Last_Updated_By
      , LAST_UPDATE_DATE   = l_Func_Area_Rec.Last_Update_Date
      , LAST_UPDATE_LOGIN  = l_Func_Area_Rec.Last_Update_Login
    WHERE
        SHORT_NAME         = l_Func_Area_Rec.Short_Name;
Line: 253

        ROLLBACK TO UpdateFuncAreaSP_Pvt;
Line: 263

        ROLLBACK TO UpdateFuncAreaSP_Pvt;
Line: 273

        ROLLBACK TO UpdateFuncAreaSP_Pvt;
Line: 276

            x_msg_data      :=  x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
Line: 278

            x_msg_data      :=  SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
Line: 281

        ROLLBACK TO UpdateFuncAreaSP_Pvt;
Line: 284

            x_msg_data      :=  x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
Line: 286

            x_msg_data      :=  SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
Line: 289

END Update_Functional_Area;
Line: 327

    IF (p_Func_Area_Rec.Last_Update_Date IS NULL) THEN
       l_Func_Area_Rec.Last_Update_Date := SYSDATE;
Line: 330

       l_Func_Area_Rec.Last_Update_Date := p_Func_Area_Rec.Last_Update_Date;
Line: 333

    IF (p_Func_Area_Rec.Last_Updated_By IS NULL) THEN
      l_Func_Area_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
Line: 336

      l_Func_Area_Rec.Last_Updated_By := p_Func_Area_Rec.Last_Updated_By;
Line: 339

    IF (p_Func_Area_Rec.Last_Update_Login IS NULL) THEN
      l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
Line: 342

      l_Func_Area_Rec.Last_Update_Login := p_Func_Area_Rec.Last_Update_Login;
Line: 354

    UPDATE BIS_FUNCTIONAL_AREAS_TL
    SET
        NAME               = l_Func_Area_Rec.Name
      , DESCRIPTION        = l_Func_Area_Rec.Description
      , LAST_UPDATED_BY    = l_Func_Area_Rec.Last_Updated_By
      , LAST_UPDATE_DATE   = l_Func_Area_Rec.Last_Update_Date
      , LAST_UPDATE_LOGIN  = l_Func_Area_Rec.Last_Update_Login
      , SOURCE_LANG        = USERENV('LANG')
    WHERE
        FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
    AND USERENV('LANG')    IN (LANGUAGE, SOURCE_LANG);
Line: 423

     SELECT  F.FUNCTIONAL_AREA_ID
           , F.SHORT_NAME
           , F.NAME
           , F.DESCRIPTION
           , F.CREATED_BY
           , F.CREATION_DATE
           , F.LAST_UPDATED_BY
           , F.LAST_UPDATE_DATE
           , F.LAST_UPDATE_LOGIN
     FROM    BIS_FUNCTIONAL_AREAS_VL F
     WHERE   F.SHORT_NAME = p_Func_Area_Rec.Short_Name;
Line: 447

       x_Func_Area_Rec.Last_Updated_By    := cRFA.LAST_UPDATED_BY;
Line: 448

       x_Func_Area_Rec.Last_Update_Date   := cRFA.LAST_UPDATE_DATE;
Line: 449

       x_Func_Area_Rec.Last_Update_Login  := cRFA.LAST_UPDATE_LOGIN;
Line: 515

    INSERT INTO BIS_FUNC_AREA_APP_DEPENDENCY
    (
       FUNCTIONAL_AREA_ID
      ,APPLICATION_ID
    )
    VALUES
    (
       p_Functional_Area_Id
      ,p_Application_Id
    );
Line: 571

PROCEDURE Update_Func_Area_Apps_Dep (
  p_Api_Version         IN           NUMBER
 ,p_Commit              IN           VARCHAR2 := FND_API.G_FALSE
 ,p_Functional_Area_Id  IN           NUMBER
 ,p_Application_Id      IN           NUMBER
 ,x_Return_Status       OUT  NOCOPY  VARCHAR2
 ,x_Msg_Count           OUT  NOCOPY  NUMBER
 ,x_Msg_Data            OUT  NOCOPY  VARCHAR2
) IS
    l_Count             NUMBER;
Line: 585

    SAVEPOINT UpdateFuncAreaAppDepSP_Pvt;
Line: 600

        ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
Line: 610

        ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
Line: 620

        ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
Line: 623

            x_msg_data      :=  x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
Line: 625

            x_msg_data      :=  SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
Line: 628

        ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
Line: 631

            x_msg_data      :=  x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
Line: 633

            x_msg_data      :=  SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
Line: 636

END Update_Func_Area_Apps_Dep;
Line: 640

PROCEDURE Delete_Functional_Area(
  p_Api_Version         IN          NUMBER
 ,p_Commit              IN          VARCHAR2 := FND_API.G_FALSE
 ,p_Func_Area_Rec       IN          BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
 ,x_Return_Status       OUT NOCOPY  VARCHAR2
 ,x_Msg_Count           OUT NOCOPY  NUMBER
 ,x_Msg_Data            OUT NOCOPY  VARCHAR2
) IS
    l_Func_Area_Rec   BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
Line: 652

      SELECT  B.FUNCTIONAL_AREA_ID,
              B.APPLICATION_ID
      FROM    BIS_FUNC_AREA_APP_DEPENDENCY B
      WHERE   B.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
Line: 657

    SAVEPOINT DeleteFuncAreaSP_Pvt;
Line: 678

    DELETE BIS_FUNCTIONAL_AREAS
    WHERE  FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
    AND    SHORT_NAME         = l_Func_Area_Rec.Short_Name;
Line: 682

    DELETE BIS_FUNCTIONAL_AREAS_TL
    WHERE  FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
Line: 691

        ROLLBACK TO DeleteFuncAreaSP_Pvt;
Line: 701

        ROLLBACK TO DeleteFuncAreaSP_Pvt;
Line: 711

        ROLLBACK TO DeleteFuncAreaSP_Pvt;
Line: 714

            x_msg_data      :=  x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
Line: 716

            x_msg_data      :=  SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
Line: 719

        ROLLBACK TO DeleteFuncAreaSP_Pvt;
Line: 722

            x_msg_data      :=  x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
Line: 724

            x_msg_data      :=  SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
Line: 726

END Delete_Functional_Area;
Line: 745

    DELETE BIS_FUNC_AREA_APP_DEPENDENCY B
    WHERE  B.FUNCTIONAL_AREA_ID = p_Functional_Area_Id
    AND    B.APPLICATION_ID     = p_Application_Id;
Line: 798

    DELETE FROM BIS_FUNCTIONAL_AREAS_TL T
    WHERE NOT EXISTS
    (
      SELECT NULL
      FROM   BIS_FUNCTIONAL_AREAS B
      WHERE  B.FUNCTIONAL_AREA_ID = T.FUNCTIONAL_AREA_ID
    );
Line: 806

    UPDATE BIS_FUNCTIONAL_AREAS_TL T SET (
        NAME,
        DESCRIPTION
    ) = (SELECT
            B.NAME,
            B.DESCRIPTION
         FROM  BIS_FUNCTIONAL_AREAS_TL B
         WHERE B.FUNCTIONAL_AREA_ID = T.FUNCTIONAL_AREA_ID
         AND   B.LANGUAGE           = T.SOURCE_LANG)
         WHERE (
            T.FUNCTIONAL_AREA_ID,
            T.LANGUAGE
         ) IN (SELECT
                SUBT.FUNCTIONAL_AREA_ID,
                SUBT.LANGUAGE
                FROM  BIS_FUNCTIONAL_AREAS_TL SUBB, BIS_FUNCTIONAL_AREAS_TL SUBT
                WHERE SUBB.FUNCTIONAL_AREA_ID = SUBT.FUNCTIONAL_AREA_ID
                AND   SUBB.LANGUAGE           = SUBT.SOURCE_LANG
                AND (
                     SUBB.NAME              <> SUBT.NAME
                     OR SUBB.DESCRIPTION    <> SUBT.DESCRIPTION
                    )
                );
Line: 830

    INSERT INTO BIS_FUNCTIONAL_AREAS_TL
    (
        FUNCTIONAL_AREA_ID
      , NAME
      , DESCRIPTION
      , LANGUAGE
      , SOURCE_LANG
      , CREATED_BY
      , CREATION_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , LAST_UPDATE_LOGIN
    )
    SELECT
        B.FUNCTIONAL_AREA_ID
      , B.NAME
      , B.DESCRIPTION
      , L.LANGUAGE_CODE
      , B.SOURCE_LANG
      , B.CREATED_BY
      , B.CREATION_DATE
      , B.LAST_UPDATED_BY
      , B.LAST_UPDATE_DATE
      , B.LAST_UPDATE_LOGIN
   FROM  BIS_FUNCTIONAL_AREAS_TL B, FND_LANGUAGES L
   WHERE L.INSTALLED_FLAG IN ('I', 'B')
   AND   B.LANGUAGE = USERENV('LANG')
   AND   NOT EXISTS
        (
          SELECT NULL
          FROM   BIS_FUNCTIONAL_AREAS_TL T
          WHERE  T.FUNCTIONAL_AREA_ID = B.FUNCTIONAL_AREA_ID
          AND    T.LANGUAGE           = L.LANGUAGE_CODE
        );