eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BOM_RTG_EAM_UTIL SQL Statements

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

Line: 37


        SELECT 'Valid Nwk Exists'
        FROM BOM_OPERATION_NETWORKS bon
        WHERE exists  ( SELECT operation_sequence_id
                        FROM bom_operation_sequences bos
                        WHERE bos.operation_sequence_id = bon.from_op_seq_id
                        AND   bos.routing_sequence_id = p_routing_sequence_id
                        AND   bos.effectivity_date <= SYSDATE
                        AND   nvl(bos.disable_date,SYSDATE+2) > SYSDATE
                       )
        AND   exists  ( SELECT operation_sequence_id
                        FROM bom_operation_sequences bos2
                        WHERE bos2.operation_sequence_id = bon.to_op_seq_id
                        AND   bos2.routing_sequence_id = p_routing_sequence_id
                        AND   bos2.effectivity_date <= SYSDATE
                        AND   nvl(bos2.disable_date,SYSDATE+2) > SYSDATE
                        )
        AND  nvl(bon.disable_date,SYSDATE+2) > SYSDATE ;

Line: 84


        SELECT   bon.from_op_seq_id     from_op_seq_id
               , bos1.operation_seq_num from_op_seq_num
               , bon.to_op_seq_id       to_op_seq_id
               , bos2.operation_seq_num to_op_seq_num
               , bon.transition_type    transition_type
               , bon.planning_pct       planning_pct
        FROM     BOM_OPERATION_NETWORKS bon
               , BOM_OPERATION_SEQUENCES bos1
               , BOM_OPERATION_SEQUENCES bos2
        WHERE nvl(bon.disable_date,SYSDATE+2) > SYSDATE
        AND   bon.transition_type <> 3
        AND   bos2.effectivity_date <= SYSDATE
        AND   nvl(bos2.disable_date,SYSDATE+2) > SYSDATE
        AND   bos2.routing_sequence_id = p_routing_sequence_id
        AND   bos2.operation_sequence_id = bon.to_op_seq_id
        AND   bos1.effectivity_date <= SYSDATE
        AND   nvl(bos1.disable_date,SYSDATE+2) > SYSDATE
        AND   bos1.routing_sequence_id = p_routing_sequence_id
        AND   bos1.operation_sequence_id = bon.from_op_seq_id
        ORDER BY from_op_seq_num ;

Line: 169


        SELECT   bon.from_op_seq_id     from_op_seq_id
               , bos1.operation_seq_num from_op_seq_num
               , bon.to_op_seq_id       to_op_seq_id
               , bos2.operation_seq_num to_op_seq_num
               , bon.transition_type    transition_type
               , bon.planning_pct       planning_pct
        FROM     BOM_OPERATION_NETWORKS bon
               , BOM_OPERATION_SEQUENCES bos1
               , BOM_OPERATION_SEQUENCES bos2
        WHERE NOT EXISTS ( SELECT NULL
                           FROM   BOM_OPERATION_NETWORKS bon2
                           WHERE  bon2.to_op_seq_id = bon.from_op_seq_id
                         )
        AND   nvl(bon.disable_date,SYSDATE+2) > SYSDATE
        AND   bon.transition_type <> 3
        AND   bos2.effectivity_date <= SYSDATE
        AND   nvl(bos2.disable_date,SYSDATE+2) > SYSDATE
        AND   bos2.routing_sequence_id = p_routing_sequence_id
        AND   bos2.operation_sequence_id = bon.to_op_seq_id
        AND   bos1.effectivity_date <= SYSDATE
        AND   nvl(bos1.disable_date,SYSDATE+2) > SYSDATE
        AND   bos1.routing_sequence_id = p_routing_sequence_id
        AND   bos1.operation_sequence_id = bon.from_op_seq_id
        ORDER BY from_op_seq_num ;

Line: 267


    SELECT   from_op_seq_id
           , to_op_seq_id
           , transition_type
           , planning_pct
    FROM   BOM_OPERATION_NETWORKS
    START WITH from_op_seq_id = p_from_op_seq_id
    AND    transition_type <> 3
    CONNECT BY PRIOR to_op_seq_id = from_op_seq_id
    AND    transition_type <> 3;

Line: 297


            SELECT operation_seq_num
            INTO   l_from_op_seq_num
            FROM   bom_operation_sequences
            WHERE  operation_sequence_id =
                       all_connected_op_rec.from_op_seq_id ;

Line: 303


            SELECT operation_seq_num
            INTO   l_to_op_seq_num
            FROM   bom_operation_sequences
            WHERE  operation_sequence_id =
                       all_connected_op_rec.to_op_seq_id ;

Line: 846


            l_connected_op_tbl.DELETE;

Line: 954


                l_each_nwk_link_tbl.DELETE ;

Line: 1006


         l_each_nwk_link_tbl.DELETE ;

Line: 1086


        SELECT 'x' dummy
        FROM DUAL
        WHERE EXISTS
        ( SELECT NULL
          FROM   bom_operation_networks a
          WHERE  a.from_op_seq_id = P_From_Op_Seq_Id
          AND    a.to_op_seq_id   <>   P_To_Op_Seq_Id
          AND    a.transition_type = 1
        );

Line: 1146


     SELECT NVL(eam_enabled_flag, 'N') eam_enabled_flag
     FROM   mtl_parameters
     WHERE  organization_id = p_org_id ;

Line: 1176


     SELECT 'Valid'
     FROM   MFG_LOOKUPS
     WHERE  lookup_code = TO_NUMBER(p_shutdown_type)
     AND    lookup_type =  'BOM_EAM_SHUTDOWN_TYPE' ;

Line: 1195


* Function      : Check_UpdateDept
* Returns       : BOOLEAN
* Parameters IN : p_op_seq_id, p_org_id, p_dept_id
* Parameters OUT: None
* Purpose       : Function will return the value of True or False
*                 to check if user can update the department for this operation.
*****************************************************************************/
FUNCTION Check_UpdateDept
( p_op_seq_id     IN   NUMBER
, p_org_id        IN   NUMBER
, p_dept_id       IN   NUMBER
)
RETURN BOOLEAN
IS

    CURSOR  Check_DeptResource IS
       SELECT 'This dept is updatable'
       FROM  BOM_DEPARTMENTS bd
       WHERE trunc(nvl(bd.disable_date, sysdate + 1)) > trunc(sysdate)
       AND   bd.department_id   = p_dept_id
       AND   bd.organization_id = p_org_id
       AND   (
               -- (NOT EXISTS (SELECT NULL
               --             FROM BOM_OPERATION_RESOURCES bor
               --             WHERE bor.operation_sequence_id = NVL(p_op_seq_id, -1)
               --         )
              --  ) OR
              ( NOT EXISTS ( SELECT  'Dept Invalid'
                             FROM   BOM_OPERATION_RESOURCES bor2
                             WHERE  bor2.operation_sequence_id = NVL(p_op_seq_id,-1)
                             AND    NOT EXISTS (SELECT 'x'
                                                FROM    BOM_DEPARTMENT_RESOURCES bdr
                                                WHERE   bdr.department_id = bd.department_id
                                                AND     bdr.resource_id   =  bor2.resource_id)
                           )
               )
             ) ;

Line: 1241


END Check_UpdateDept ;

Line: 1253


*                 error if the operation is UPDATE and record DOES NOT
*                 EXIST.
*                 In case of UPDATE if record exists, then the procedure
*                 will return old record in the old entity parameters
*                 with a success status.
*********************************************************************/
PROCEDURE Op_Node_Check_Existence
(  p_op_node_rec           IN  Bom_Rtg_Eam_Util.Op_Node_Rec_Type
,  x_old_op_node_rec       IN OUT NOCOPY Bom_Rtg_Eam_Util.Op_Node_Rec_Type
,  x_return_mesg           IN OUT NOCOPY VARCHAR2
,  x_return_status         IN OUT NOCOPY VARCHAR2
)
IS
   l_return_mesg    VARCHAR2(2000) ;

Line: 1273


   SELECT  X_COORDINATE
         , Y_COORDINATE
   FROM BOM_OPERATION_SEQUENCES
   WHERE  operation_sequence_id = p_operation_sequence_id ;

Line: 1335


             (BOM_Rtg_Globals.G_OPR_UPDATE,
              BOM_Rtg_Globals.G_OPR_DELETE )
   THEN
          l_return_mesg :=  'BOM_OP_DOESNOT_EXIST' ;

Line: 1526


* Purpose   : Perform any insert/update/deletes to the
*             Operation Sequences table.
*********************************************************************/
PROCEDURE Op_Node_Perform_Writes
(  p_op_node_rec           IN  Bom_Rtg_Eam_Util.Op_Node_Rec_Type
,  x_return_mesg           IN OUT NOCOPY VARCHAR2
,  x_return_status         IN OUT NOCOPY VARCHAR2
)
IS

BEGIN

   --
   -- Initialize Status
   --
   x_return_status      := FND_API.G_RET_STS_SUCCESS ;

Line: 1550


      ('Operation Sequence: Executing Insert Row. . . ') ;

Line: 1555


   ELSIF p_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
   THEN
      IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
      ('Operatin Sequence: Executing Update Row. . . ') ;

Line: 1562


      UPDATE BOM_OPERATION_SEQUENCES
      SET  X_Coordinate = p_op_node_rec.x_coordinate
      ,    Y_Coordinate = p_op_node_rec.y_coordinate
      ,    last_update_date            = SYSDATE                      /* Last Update Date */
      ,    last_updated_by             = BOM_Rtg_Globals.Get_User_Id  /* Last Updated By */
      ,    last_update_login           = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
      ,    program_application_id      = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
      ,    program_id                  = BOM_Rtg_Globals.Get_Prog_Id    /* Program Id */
      ,    program_update_date         = SYSDATE                    /* program_update_date */
      WHERE operation_sequence_id = p_op_node_rec.operation_sequence_id ;

Line: 1574


   ELSIF p_op_node_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
   THEN

      IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
      ('Operatin Sequence: Executing Delete Row. . . ') ;

Line: 1684


            IF l_op_node_rec.transaction_type <>  BOM_Rtg_Globals.G_OPR_UPDATE
            THEN
               /* Error Handling */
               FND_MESSAGE.SET_NAME('BOM','BOM_OPNODE_TRANS_TYPE_INVALID');

Line: 1740


               (BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
            THEN

                --
                -- Process flow step : Populate NULL columns for Update and Delete
                --

IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
     Error_Handler.Write_Debug('Populate NULL columns') ;

Line: 1773


                                                 , BOM_Rtg_Globals.G_OPR_UPDATE )
            THEN

               Bom_Rtg_Eam_Util.Op_Node_Entity_Defaulting
              (   p_op_node_rec   => l_op_node_rec
              ,   x_op_node_rec   => l_op_node_rec
              ,   x_return_mesg   => l_return_mesg
              ,   x_return_status => l_return_status
              ) ;

Line: 1915


        SELECT   bos1.Operation_Type
               , bos1.operation_seq_num From_Op_Seq_Number
               , bos1.effectivity_date  From_Start_Effective_Date
               , bos2.operation_seq_num To_Op_Seq_Number
               , bos2.effectivity_date  To_Start_Effective_Date
        FROM  BOM_OPERATION_SEQUENCES  bos1
           ,  BOM_OPERATION_SEQUENCES  bos2
        WHERE bos1.operation_sequence_id = p_from_op_id
        AND   bos2.operation_sequence_id = p_to_op_id ;