APPS.ENG_CHANGE_LINE_UTIL SQL Statements

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

Line: 44

   SELECT  cl.change_line_id
        ,  cl.change_type_id
        ,  cl.status_code
        ,  cl.assignee_id
        ,  cl.need_by_date
        ,  cl.object_id
        ,  cl.pk1_value
        ,  cl.pk2_value
        ,  cl.pk3_value
        ,  cl.pk4_value
        ,  cl.pk5_value
        ,  cl.scheduled_date
        ,  cl.implementation_date
        ,  cl.cancelation_date
        ,  cltl.name
        ,  cltl.description
        ,  cl.original_system_reference
   FROM   ENG_CHANGE_LINES cl
        , ENG_CHANGE_LINES_TL cltl
   WHERE  cl.change_id = p_change_id
   AND    cl.sequence_number = p_line_sequence_number
   AND    cl.change_line_id = cltl.change_line_id
   AND    cltl.language = userenv('LANG') ;

Line: 72

   SELECT change_id FROM eng_engineering_changes
   WHERE change_notice = p_change_notice and organization_id = p_organization_id;

Line: 204

* Purpose   : Perform any insert/update/deletes to the
*             Change Line table.
*********************************************************************/
PROCEDURE Perform_Writes
(  p_change_line_rec       IN  Eng_Eco_Pub.Change_Line_Rec_Type
 , p_change_line_unexp_rec IN  Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
 , x_Mesg_Token_Tbl        OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
 , x_return_status         OUT NOCOPY VARCHAR2
 )
IS

    l_change_line_rec             Eng_Eco_Pub.Change_Line_Rec_Type ;

Line: 245

      ('Change Line: Executing Insert Row. . . ') ;

Line: 249

      Insert_Row
        (  p_change_line_rec        => l_change_line_rec
         , p_change_line_unexp_rec  => l_change_line_unexp_rec
         , x_return_status          => l_temp_return_status
         , x_mesg_token_tbl         => l_temp_mesg_token_tbl
        ) ;

Line: 262

   ELSIF l_change_line_rec.transaction_type = BOM_Globals.G_OPR_UPDATE
   THEN


IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
      ('Change Line: Executing Update Row. . . ') ;

Line: 270

      Update_Row
        (  p_change_line_rec        => l_change_line_rec
         , p_change_line_unexp_rec  => l_change_line_unexp_rec
         , x_return_status          => l_temp_return_status
         , x_mesg_token_tbl         => l_temp_mesg_token_tbl
        ) ;

Line: 285

   ELSIF l_change_line_rec.transaction_type = BOM_Globals.G_OPR_DELETE
   THEN

IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
      ('Change Line: Executing Delete Row. . . ') ;

Line: 292

      Delete_Row
        (  p_change_line_rec        => l_change_line_rec
         , p_change_line_unexp_rec  => l_change_line_unexp_rec
         , x_return_status          => l_temp_return_status
         , x_mesg_token_tbl         => l_temp_mesg_token_tbl
        ) ;

Line: 345

* Purpose   : This procedure will insert a record in the Change Line
*             table:  ENG_CHANGE_LINES/_TL.
*
*****************************************************************************/
PROCEDURE Insert_Row
(  p_change_line_rec       IN  Eng_Eco_Pub.Change_Line_Rec_Type
 , p_change_line_unexp_rec IN  Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
 , x_Mesg_Token_Tbl        OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
 , x_return_status         OUT NOCOPY VARCHAR2
)
IS

    -- Error Handlig Variables
    l_return_status       VARCHAR2(1);

Line: 366

   SELECT change_id FROM eng_engineering_changes
   WHERE change_notice = p_change_notice and organization_id = p_organization_id;

Line: 386

   INSERT INTO ENG_CHANGE_LINES(
      change_line_id
    , change_id
    , change_type_id
    , status_code
    , sequence_number
    , need_by_date
    , scheduled_date
    , implementation_date
    , cancelation_date
    , assignee_id
    , object_id
    , pk1_value
    , pk2_value
    , pk3_value
    , pk4_value
    , pk5_value
    , last_update_date
    , last_updated_by
    , last_update_login
    , creation_date
    , created_by
    , request_id
    , program_application_id
    , program_id
    , program_update_date
    , original_system_reference
    , Approval_Status_Type
    ,   Required_Flag
    ,   Complete_Before_Status_Code
    ,   Start_After_Status_Code
   )
   VALUES (
      p_change_line_unexp_rec.change_line_id
    , l_change_id
    , p_change_line_unexp_rec.change_type_id
    , p_change_line_unexp_rec.status_code
    , p_change_line_rec.sequence_number
    , p_change_line_rec.need_by_date
    , p_change_line_rec.scheduled_date
    , p_change_line_rec.implementation_date
    , p_change_line_rec.cancelation_date
    , p_change_line_unexp_rec.assignee_id
    , p_change_line_unexp_rec.object_id
    , p_change_line_unexp_rec.pk1_value
    , p_change_line_unexp_rec.pk2_value
    , p_change_line_unexp_rec.pk3_value
    , p_change_line_unexp_rec.pk4_value
    , p_change_line_unexp_rec.pk5_value
    , SYSDATE                    -- Last Update Date
    , BOM_Globals.Get_User_Id    -- Last Updated By
    , BOM_Globals.Get_Login_Id   -- Last Update Login
    , SYSDATE                    -- Creation Date
    , BOM_Globals.Get_User_Id    -- Created By
    , NULL                       -- Request Id
    , BOM_Globals.Get_Prog_AppId -- Application Id
    , BOM_Globals.Get_Prog_Id    -- Program Id
    , SYSDATE                    -- program_update_date
    , p_change_line_rec.original_system_reference
    , p_change_line_unexp_rec.Approval_Status_Type --Added as it is mandatory 18-6-2003
    ,p_change_line_rec. Required_Flag
    , p_change_line_rec.Complete_Before_Status_Code
    ,p_change_line_rec. Start_After_Status_Code
    );

Line: 452

   INSERT INTO ENG_CHANGE_LINES_TL (
      change_line_id
    , language
    , source_lang
    , created_by
    , creation_date
    , last_update_date
    , last_updated_by
    , last_update_login
    , name
    , description
    )
    SELECT  p_change_line_unexp_rec.change_line_id
          , lang.language_code
          , USERENV('LANG')
          , BOM_Globals.Get_User_Id
          , SYSDATE
          , SYSDATE
          , BOM_Globals.Get_User_Id
          , BOM_Globals.Get_Login_Id
          , p_change_line_rec.name
          , p_change_line_rec.description
    FROM FND_LANGUAGES lang
    WHERE lang.installed_flag in ('I', 'B')
    AND NOT EXISTS ( SELECT NULL
                     FROM   ENG_CHANGE_LINES_TL tl
                     WHERE  tl.change_line_id = p_change_line_unexp_rec.change_line_id
                     AND    tl.language = lang.language_code
                     );

Line: 493

    ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ( p_change_id => l_change_id );

Line: 499

        l_err_text := 'Error in ' || G_PKG_NAME || ' at ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ';

Line: 508

   ('Unexpected Error occured in Insert . . .' || SQLERRM);

Line: 513

          l_err_text := G_PKG_NAME || ' : Utility (Change Line Insert) ' ||
                                        SUBSTR(SQLERRM, 1, 200);

Line: 528

END Insert_Row ;

Line: 532

* Procedure : Update_Row
* Parameters IN : Change Line exposed column record
*                 Change Line unexposed column record
* Parameters OUT: Return Status
*                 Message Token Table
* Purpose   : Update_Row procedure will update the production record with
*             the user given values. Any errors will be returned by filling
*             the Mesg_Token_Tbl and setting the return_status.
****************************************************************************/
PROCEDURE Update_Row
(  p_change_line_rec       IN  Eng_Eco_Pub.Change_Line_Rec_Type
 , p_change_line_unexp_rec IN  Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
 , x_Mesg_Token_Tbl        OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
 , x_return_status         OUT NOCOPY VARCHAR2
)
IS

    -- Error Handlig Variables
    l_return_status   VARCHAR2(1);

Line: 560

IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update change line . . .') ;

Line: 563

   UPDATE ENG_CHANGE_LINES
   SET last_update_date           = SYSDATE
     , last_updated_by            = BOM_Globals.Get_User_Id
     , last_update_login          = BOM_Globals.Get_Login_Id
     , change_type_id             = p_change_line_unexp_rec.change_type_id
     , status_code                = p_change_line_unexp_rec.status_code
     , sequence_number            = p_change_line_rec.sequence_number
     , need_by_date               = p_change_line_rec.need_by_date
     , scheduled_date             = p_change_line_rec.scheduled_date
     , implementation_date        = p_change_line_rec.implementation_date
     , cancelation_date           = p_change_line_rec.cancelation_date
     , assignee_id                = p_change_line_unexp_rec.assignee_id
     , object_id                  = p_change_line_unexp_rec.object_id
     , pk1_value                  = p_change_line_unexp_rec.pk1_value
     , pk2_value                  = p_change_line_unexp_rec.pk2_value
     , pk3_value                  = p_change_line_unexp_rec.pk3_value
     , pk4_value                  = p_change_line_unexp_rec.pk4_value
     , pk5_value                  = p_change_line_unexp_rec.pk5_value
     , original_system_reference  = p_change_line_rec.original_system_reference
       WHERE change_line_id = p_change_line_unexp_rec.change_line_id ;

Line: 584

   UPDATE ENG_CHANGE_LINES_TL
   SET last_update_date           = SYSDATE
     , last_updated_by            = BOM_Globals.Get_User_Id
     , last_update_login          = BOM_Globals.Get_Login_Id
     , name                       = p_change_line_rec.name
     , description                = p_change_line_rec.description
   WHERE  change_line_id = p_change_line_unexp_rec.change_line_id
   AND    USERENV('LANG') = language;

Line: 599

       ('Unexpected Error occured in Update . . .' || SQLERRM);

Line: 604

          l_err_text := G_PKG_NAME || ' : Utility (Chage Line Update) ' ||
                                        SUBSTR(SQLERRM, 1, 200);

Line: 619

END Update_Row ;

Line: 623

* Procedure     : Delete_Row
* Parameters IN : Change Line exposed column record
*                 Change Line unexposed column record
* Parameters OUT: Return Status
*                 Message Token Table
* Purpose       : procedure will delete a change line record.
*********************************************************************/
PROCEDURE Delete_Row
(  p_change_line_rec            IN  Eng_Eco_Pub.Change_Line_Rec_Type
 , p_change_line_unexp_rec      IN  Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
 , x_Mesg_Token_Tbl             OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
 , x_return_status              OUT NOCOPY VARCHAR2
 )
IS

    l_change_line_rec        Eng_Eco_Pub.Change_Line_Rec_Type ;

Line: 664

   DELETE  FROM ENG_CHANGE_LINES
   WHERE   change_line_id = l_change_line_unexp_rec.change_line_id ;

Line: 668

   DELETE  FROM ENG_CHANGE_LINES_TL
   WHERE   change_line_id = l_change_line_unexp_rec.change_line_id ;

Line: 681

       ('Unexpected Error occured in Delete . . .' || SQLERRM);

Line: 686

          l_err_text := G_PKG_NAME || ' : Utility (Change Line Delete) ' ||
                                        SUBSTR(SQLERRM, 1, 200);

Line: 702

END Delete_Row ;

Line: 715

select ect.type_name ,ect.subject_id ,ese.entity_name,ese.parent_entity_name from
eng_change_order_types_vl ect ,eng_subject_entities ese
where ect.subject_id =ese.subject_id
and change_order_type_id =p_change_type_id
and subject_level=1 ;

Line: 722

SELECT  LP.PROJ_ELEMENT_ID -- into l_current_lifecycle_id
FROM PA_EGO_LIFECYCLES_PHASES_V LP, MTL_ITEM_REVISIONS MIR
WHERE  LP.PROJ_ELEMENT_ID = MIR.CURRENT_PHASE_ID
AND MIR.INVENTORY_ITEM_ID = item_id
AND MIR.ORGANIZATION_ID = l_org_id
AND MIR.REVISION = revision;

Line: 731

SELECT ITEM_CATALOG_GROUP_ID
from mtl_system_items msi
where msi.INVENTORY_ITEM_ID = item_id
AND   msi.ORGANIZATION_ID = l_org_id;

Line: 887

                SELECT CURRENT_PHASE_ID
                INTO l_change_subject_unexp_rec.lifecycle_state_id
                FROM MTL_System_items_vl
                WHERE INVENTORY_ITEM_ID = l_change_subject_unexp_rec.pk1_value
                AND ORGANIZATION_ID = l_change_subject_unexp_rec.pk2_value;

Line: 906

            Insert into eng_change_subjects
                 (CHANGE_SUBJECT_ID,
                  CHANGE_ID,
                  CHANGE_LINE_ID,
                  ENTITY_NAME,
                  PK1_VALUE,
                  PK2_VALUE,
                  PK3_VALUE,
                  PK4_VALUE,
                  PK5_VALUE,
                  SUBJECT_LEVEL,
                  LIFECYCLE_STATE_ID,
                  LAST_UPDATE_DATE,
                  LAST_UPDATED_BY,
                  CREATION_DATE,
                  CREATED_BY,
                  LAST_UPDATE_LOGIN,
                  REQUEST_ID,
                  PROGRAM_ID,
                  PROGRAM_APPLICATION_ID,
                  PROGRAM_UPDATE_DATE)
                 values
                 (eng_change_subjects_s.nextval,
                  l_change_subject_unexp_rec.change_id,
                  l_change_subject_unexp_rec.change_line_id,
                  l_change_subject_unexp_rec.entity_name,
                  l_change_subject_unexp_rec.pk1_value,
                  l_change_subject_unexp_rec.pk2_value,
                  l_change_subject_unexp_rec.pk3_value,
                  l_change_subject_unexp_rec.pk4_value,
                  l_change_subject_unexp_rec.pk5_value,
                  l_change_subject_unexp_rec.subject_level,
                  l_change_subject_unexp_rec.lifecycle_state_id,
                  SYSDATE,
                  l_User_Id,
                  SYSDATE,
                  l_User_Id,
                  l_Login_Id,
                  l_request_id,
                  l_prog_appid,
                  l_prog_id,sysdate);

Line: 950

                Insert into eng_change_subjects
                    (CHANGE_SUBJECT_ID,
                     CHANGE_ID,
                     CHANGE_LINE_ID,
                     ENTITY_NAME,
                     PK1_VALUE,
                     PK2_VALUE,
                     PK3_VALUE,
                     PK4_VALUE,
                     PK5_VALUE,
                     SUBJECT_LEVEL,
                     LIFECYCLE_STATE_ID,
                     LAST_UPDATE_DATE,
                     LAST_UPDATED_BY,
                     CREATION_DATE,
                     CREATED_BY,
                     LAST_UPDATE_LOGIN,
                     REQUEST_ID,
                     PROGRAM_ID,
                     PROGRAM_APPLICATION_ID,
                     PROGRAM_UPDATE_DATE)
                    values
                    (eng_change_subjects_s.nextval,
                     l_change_subject_unexp_rec.change_id,
                     l_change_subject_unexp_rec.change_line_id,
                     l_parent_entity_name, --l_change_subject_unexp_rec.entity_name,
                     l_change_subject_unexp_rec.pk1_value,
                     l_change_subject_unexp_rec.pk2_value,
                     null,
                     null,
                     null,
                     2,
                     null,
                     SYSDATE,
                     l_User_Id,
                     SYSDATE,
                     l_User_Id,
                     l_Login_Id,
                     l_request_id,
                     l_prog_appid,
                     l_prog_id,
                     sysdate);

Line: 997

                Insert into eng_change_subjects
                   (CHANGE_SUBJECT_ID,
                    CHANGE_ID,
                    CHANGE_LINE_ID,
                    ENTITY_NAME,
                    PK1_VALUE,
                    PK2_VALUE,
                    PK3_VALUE,
                    PK4_VALUE,
                    PK5_VALUE,
                    SUBJECT_LEVEL,
                    LIFECYCLE_STATE_ID,
                    LAST_UPDATE_DATE,
                    LAST_UPDATED_BY,
                    CREATION_DATE,
                    CREATED_BY,
                    LAST_UPDATE_LOGIN,
                    REQUEST_ID,
                    PROGRAM_ID,
                    PROGRAM_APPLICATION_ID,
                    PROGRAM_UPDATE_DATE)
                   values
                   (eng_change_subjects_s.nextval,
                    l_change_subject_unexp_rec.change_id,
                     l_change_subject_unexp_rec.change_line_id,
                    l_parent_entity_name, --l_change_subject_unexp_rec.entity_name,
                    l_item_catalog_group_id,
                    null,
                    null,
                    null,
                    null,
                    2,
                    null,
                    SYSDATE,
                    l_User_Id,
                    SYSDATE,
                    l_User_Id,
                    l_Login_Id,
                    l_request_id,
                    l_prog_appid,
                    l_prog_id,
                    sysdate);

Line: 1040

        ELSIF p_change_line_rec.transaction_type =  Eng_Globals.G_OPR_UPDATE
        THEN
            UPDATE eng_change_subjects SET
            pk1_value = l_change_subject_unexp_rec.pk1_value,
            pk2_value = l_change_subject_unexp_rec.pk2_value,
            pk3_value = l_change_subject_unexp_rec.pk3_value
            WHERE change_id = l_change_subject_unexp_rec.change_id
            AND change_line_id = l_change_subject_unexp_rec.change_line_id
            AND subject_level = 1;

Line: 1052

                UPDATE eng_change_subjects SET
                pk1_value = l_change_subject_unexp_rec.pk1_value,
                pk2_value = l_change_subject_unexp_rec.pk2_value
                WHERE change_id = l_change_subject_unexp_rec.change_id
                AND subject_level = 2
                AND change_line_id = l_change_subject_unexp_rec.change_line_id;

Line: 1063

                UPDATE eng_change_subjects SET
                pk1_value = l_item_catalog_group_id
                WHERE change_id = l_change_subject_unexp_rec.change_id
                AND subject_level = 2
                AND change_line_id = l_change_subject_unexp_rec.change_line_id;

Line: 1070

            DELETE FROM eng_change_subjects
            WHERE change_line_id = l_change_subject_unexp_rec.change_line_id
            AND change_id = l_change_subject_unexp_rec.change_id;

Line: 1106

  SELECT esev.query_object_name, esev.pk1_column_name, esev.pk2_column_name
       , esev.pk3_column_name, esev.pk4_column_name, esev.pk5_column_name
       , esev.query_column1_name, esev.query_column2_name, esev.query_column3_name
       , esev.query_column4_name, esev.query_column5_name
       , subs.entity_name, subs.subject_level, subs.pk1_value
       , subs.pk2_value, subs.pk3_value, subs.pk4_value, subs.pk5_value
    FROM eng_subject_entities_v esev, eng_change_subjects subs
   WHERE esev.subject_id     = p_subject_id
     AND esev.subject_level  = subs.subject_level
     AND esev.entity_name    = subs.entity_name
     AND subs.change_id      = p_change_id
     AND subs.change_line_id = p_change_line_id
   ORDER BY subs.subject_level DESC;

Line: 1178

      l_sql_stmt := 'SELECT '||l_query_column_cl|| ' FROM ' || csd1.query_object_name || ' WHERE ' || l_where_clause ;

Line: 1220

 SELECT * FROM(
  SELECT subs.entity_name, subs.pk1_value
       , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(eco.entity_url,'pk1Value',subs.pk1_value)
       ,'pk2Value', subs.pk2_value)
       ,'pk3Value', subs.pk3_value)
       ,'pk4Value', subs.pk4_value)
       ,'pk1Value', subs.pk5_value) subject_url
    FROM ENG_CHANGE_OBJECTS eco, eng_change_subjects subs
   WHERE eco.entity_name    = subs.entity_name
     AND subs.pk1_value IS NOT NULL
     AND subs.entity_name <> 'EGO_COMPONENT'
     AND subs.change_id      = p_change_id
     AND subs.change_line_id = p_change_line_id
     ORDER BY subs.subject_level)
     WHERE ROWNUM=1;

Line: 1237

       SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('&inventoryItemId=pk1Value&organizationId=pk2Value&revisionId=pk3Value'
       ,'pk1Value',subs.pk1_value)
       ,'pk2Value', subs.pk2_value)
       ,'pk3Value', subs.pk3_value)
       ,'pk4Value', subs.pk4_value)
       ,'pk1Value', subs.pk5_value) subject_url
       FROM eng_change_subjects subs
       WHERE subs.subject_level = 3 and subs.entity_name = 'EGO_ITEM_REVISION'
       AND subs.change_id      = p_change_id
       AND subs.change_line_id = p_change_line_id;

Line: 1260

	   	SELECT Nvl(ALTERNATE_BOM_DESIGNATOR,bom_globals.retrieve_message('BOM','BOM_PRIMARY'))
		into l_structure_name
		FROM BOM_BILL_OF_MATERIALS
		WHERE bill_sequence_id = l_csd.pk1_value;