APPS.CSF_ACCESS_PKG SQL Statements

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

Line: 77

  v_statement := 'select counter'
               ||' from '||x_acc
               ||' where '||x_pk||' = '||x_pk_id
               ||' and server_id = '||x_server_id;

Line: 106

PROCEDURE InsertAcc
  ( x_acc       IN VARCHAR2
  , x_pk        IN VARCHAR2
  , x_pk_id     IN NUMBER
  , x_server_id IN NUMBER
  )
IS
/********************************************************
 Name:
   InsertAcc

 Purpose:
   Insert access record.

 Arguments:
   x_acc         Name of the access table.
   x_pk          Name of the PK column.
   x_pk_id       Value of the PK.
   x_server_id   Id of the MDG server.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  v_statement varchar2(1000);

Line: 136

  v_statement := 'insert into '||x_acc
               ||'('||x_pk||',server_id, last_update_date, last_updated_by, '
               ||'creation_date, created_by, counter) '
               ||'values('||x_pk_id||','||x_server_id
               ||',sysdate,1,sysdate,1,1)';

Line: 144

END InsertAcc;

Line: 146

FUNCTION UpdateAcc
  ( x_acc       IN VARCHAR2
  , x_pk        IN VARCHAR2
  , x_pk_id     IN NUMBER
  , x_server_id IN NUMBER
  , x_op        IN VARCHAR2
  ) RETURN NUMBER
IS
/********************************************************
 Name:
   UpdateAcc

 Purpose:
   Update the access record. If the access record does
   not exist, insert the access record, else increase the
   counter. If it is a deletion, decrease the counter. If
   last record, delete the access record.

   Return-value 0 means the record has been inserted.
   Return-value 1 means the record has been updated.

 Arguments:
   x_acc         Name of the access table.
   x_pk          Name of the PK column.
   x_pk_id       Value of the PK.
   x_server_id   Id of the MDG server.
   x_op          Operation to be performed: '+' for
                 increase, '-' for decrease.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  v_id        NUMBER;

Line: 201

      InsertAcc
        ( x_acc       => x_acc
        , x_pk        => x_pk
        , x_pk_id     => x_pk_id
        , x_server_id => x_server_id
        );

Line: 221

    v_statement := 'update '||x_acc
                 ||' set counter = '||v_counter
                 ||' where '||x_pk||'='||x_pk_id
                 || ' and server_id ='||x_server_id;

Line: 226

    v_statement := 'delete from '||x_acc
                 ||' where '||x_pk||'='||x_pk_id
                 ||' and server_id ='||x_server_id;

Line: 236

END UpdateAcc;

Line: 266

    SELECT 1
    FROM   asg_device_users
    WHERE  resource_id = x_resource_id;

Line: 321

    SELECT server_id
    FROM   asg_server_resources
    WHERE  resource_id = x_resource_id;

Line: 341

Procedure UpdateAccesses_Partyid
  ( x_party_id  IN NUMBER
  , x_server_id IN NUMBER
  , x_op        IN VARCHAR2
  )
IS
/********************************************************
 Name:
   UpdateAccesses_Partyid

 Purpose:
   Update Service Access records based on party_id.

 Arguments:
   x_party_id    The id of the party for which the access
                 record must be updated.
   x_server_id   Id of the MDG server.
   x_op          Operation to be performed: '+' for
                 increase, '-' for decrease.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  v_ret NUMBER;

Line: 372

  v_ret := UpdateAcc
             ( x_acc       => 'ASG_PARTY_ACC'
             , x_pk        => 'PARTY_ID'
             , x_pk_id     => x_party_id
             , x_server_id => x_server_id
             , x_op        => x_op
             );

Line: 379

END UpdateAccesses_Partyid;

Line: 381

Procedure UpdateAccesses_Incidentid
  ( x_incident_id IN NUMBER
  , x_server_id   IN NUMBER
  , x_op          IN VARCHAR2
  )
IS
/********************************************************
 Name:
   UpdateAccesses_Incidentid

 Purpose:
   Update Service Access records based on incident_id.

 Arguments:
   x_incident_id   The id of the incident for which the
                   access record must be updated.
   x_server_id     Id of the MDG server.
   x_op            Operation to be performed: '+' for
                   increase, '-' for decrease.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?        Created
   29-OCT-2001 MRAAP    Added cursor to retrieve
                        party_id of Installed At address
                        of Task. This is needed, because
                        this customer may differ from
                        the Service Request customer.

                        This is part of the fix for
                        bug 1931013.
********************************************************/
  CURSOR c_customer
    ( x_incident_id NUMBER
    )
  IS
    SELECT customer_id
    FROM   cs_incidents_all_b
    WHERE  incident_id = x_incident_id;

Line: 428

    SELECT party_id
    FROM   cs_hz_sr_contact_points contact
    WHERE  contact.incident_id = x_incident_id
    AND    contact.primary_flag = 'Y';

Line: 437

    SELECT hps.party_id
    FROM   hz_party_sites hps
    ,      jtf_tasks_b jt_b
    WHERE  jt_b.source_object_id = x_incident_id
    AND    jt_b.source_object_type_code = 'SR'
    AND    jt_b.address_id = hps.party_site_id;

Line: 448

  v_ret := UpdateAcc
             ( x_acc       => 'ASG_INCIDENT_ACC'
             , x_pk        => 'INCIDENT_ID'
             , x_pk_id     => x_incident_id
             , x_server_id => x_server_id
             , x_op        => x_op
             );

Line: 468

    UpdateAccesses_Partyid
      ( x_party_id  => v_party_id
      , x_server_id => x_server_id
      , x_op        => x_op
      );

Line: 488

    UpdateAccesses_Partyid
      ( x_party_id  => v_party_id
      , x_server_id => x_server_id
      , x_op        => x_op
      );

Line: 508

    UpdateAccesses_Partyid
      ( x_party_id  => v_party_id
      , x_server_id => x_server_id
      , x_op        => x_op
      );

Line: 516

END UpdateAccesses_Incidentid;

Line: 518

Procedure UpdateAccesses_Taskid
  ( x_task_id   IN NUMBER
  , x_server_id IN NUMBER
  , x_op        IN VARCHAR2
  )
IS
/********************************************************
 Name:
   UpdateAccesses_Taskid

 Purpose:
   Update Service Access records based on task_id.

   Determine if the task needs to be replicated to the
   mobile client, according to the following conditions:

   - the task is not deleted (deleted_flag is not 'Y')
   - the type of the task is 'Dispatch' or the task is
     private or the task is an departure or arrival
     task.

   If the conditions apply, proceed with inserting/updating
   the access record in ASG_TASK_ACC and make a call to
   see if the Service Request related to the task needs
   to be replicated as well.

 Arguments:
   x_task_id     The id of the task for which the access
                 record must be updated.
   x_server_id   Id of the MDG server.
   x_op          Operation to be performed: '+' for
                 increase, '-' for decrease.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?        Created
   16-OCT-2001 MRAAP    Modified WHERE-clause of cursor
                        c_task to include task with
		        type_id = 21 (arrival task).
		        This is a fix for bug 2055402.

********************************************************/
  CURSOR c_service_req
    ( x_task_id NUMBER
    )
  IS
    SELECT source_object_id
    FROM   jtf_tasks_b
    WHERE  source_object_type_code = 'SR'
    AND    task_id = x_task_id;

Line: 576

    SELECT task_id
    FROM   jtf_tasks_b      jt_b
    ,      jtf_task_types_b jtt_b
    WHERE  jt_b.task_id = x_task_id
    AND    jt_b.task_type_id = jtt_b.task_type_id
    AND    NVL(jt_b.deleted_flag, 'N') <> 'Y'
    AND    (  jtt_b.rule = 'DISPATCH'
           OR jt_b.private_flag = 'Y'
           OR jt_b.task_type_id IN (20, 21)
           );

Line: 600

    v_ret := UpdateAcc
               ( x_acc       => 'ASG_TASK_ACC'
               , x_pk        => 'TASK_ID'
               , x_pk_id     => x_task_id
               , x_server_id => x_server_id
               , x_op        => x_op
               );

Line: 619

      UpdateAccesses_Incidentid
        ( x_incident_id => v_incident_id
        , x_server_id   => x_server_id
        , x_op          => x_op
        );

Line: 631

END UpdateAccesses_Taskid;

Line: 633

PROCEDURE UpdateMobileUserAcc
  ( x_resource_id IN NUMBER
  , x_server_id   IN NUMBER
  , x_op          IN VARCHAR2
  )
IS
/********************************************************
 Name:
   UpdateMobileUserAcc

 Purpose:
   Add/Delete all accesses related to a mobile User
   This procedure is called from Create/Delete Mobile
   User.

 Arguments:
   x_resource_id   Resource_id of the mobile user.
   x_server_id     Id of the MDG server.
   x_op            Operation to be performed: '+' for
                   increase, '-' for decrease.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  CURSOR c_tasks
    ( x_resource_id number
    )
  IS
    SELECT task_id
    FROM   jtf_task_assignments
    WHERE  resource_id = x_resource_id;

Line: 680

    UpdateAccesses_Taskid
      ( x_task_id   => v_task_id
      , x_server_id => x_Server_id
      , x_op        => x_op
      );

Line: 692

END UpdateMobileUserAcc;

Line: 694

PROCEDURE INCIDENT_POST_INSERT
  ( x_return_status OUT VARCHAR2
  )
IS
BEGIN
  x_return_status := 'S';

Line: 700

END INCIDENT_POST_INSERT;

Line: 702

PROCEDURE INCIDENT_PRE_UPDATE
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   INCIDENT_PRE_UPDATE

 Purpose:
   Retrieve more info about the incident (old and new values)
   and call the sr-contact-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  CURSOR c_customer_id
    ( b_incident_id NUMBER
    )
  IS
    SELECT customer_id
    FROM   cs_incidents_all
    WHERE  incident_id = b_incident_id;

Line: 753

    , trigger_mode  => 'ON-UPDATE'
    );

Line: 763

END INCIDENT_PRE_UPDATE;

Line: 765

PROCEDURE INCIDENT_POST_UPDATE
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   INCIDENT_POST_UPDATE

 Purpose:
   Retrieve the incident_id
   and call the sr-contact-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  incident_id NUMBER;

Line: 800

END INCIDENT_POST_UPDATE;

Line: 802

PROCEDURE TASKS_POST_INSERT
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   TASKS_POST_INSERT

 Purpose:
   Retrieve more info about the task (new values)
   and call the task-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   29-NOV-2001 MRAAP     Created

********************************************************/
  n_task_id                 NUMBER;

Line: 845

    , trigger_mode              => 'ON-INSERT'
    );

Line: 849

END TASKS_POST_INSERT;

Line: 851

PROCEDURE TASKS_PRE_UPDATE
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   TASKS_PRE_UPDATE

 Purpose:
   Retrieve more info about the task (old and new values)
   and call the task-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  CURSOR c_task
    ( b_task_id NUMBER
    )
  IS
    SELECT source_object_id
    ,      source_object_name
    ,      source_object_type_code
    FROM   jtf_tasks_b
    WHERE  task_id =  b_task_id;

Line: 917

    , trigger_mode              => 'ON-UPDATE'
    );

Line: 922

END TASKS_PRE_UPDATE;

Line: 924

PROCEDURE TASKS_POST_UPDATE
  ( x_return_status OUT VARCHAR2
  )
IS
BEGIN
  x_return_status := 'S';

Line: 930

END TASKS_POST_UPDATE;

Line: 932

PROCEDURE TASKS_PRE_DELETE
  ( x_return_status OUT VARCHAR2
  )
IS
BEGIN
  x_return_status := 'S';

Line: 938

END TASKS_PRE_DELETE;

Line: 940

PROCEDURE TASK_ASSIGN_POST_INSERT
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   TASK_ASSIGN_POST_INSERT

 Purpose:
   Retrieve more info about the task_assignment (new values)
   and call the task-assignment-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  n_task_assignment_id NUMBER;

Line: 979

    , Trigger_Mode         => 'ON-INSERT'
    );

Line: 983

END TASK_ASSIGN_POST_INSERT;

Line: 985

PROCEDURE TASK_ASSIGN_PRE_UPDATE
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   TASK_ASSIGN_PRE_UPDATE

 Purpose:
   Retrieve more info about the task_assignment (old and new values)
   and call the task-assignment-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  CURSOR c_task_assign
    ( b_task_assignment_id NUMBER
    )
  IS
    SELECT task_id
    ,      resource_id
    FROM   jtf_task_assignments
    WHERE  task_assignment_id = b_task_assignment_id;

Line: 1044

    , trigger_mode         => 'ON-UPDATE'
    );

Line: 1048

END TASK_ASSIGN_PRE_UPDATE;

Line: 1050

PROCEDURE TASK_ASSIGN_POST_UPDATE
  ( x_return_status OUT VARCHAR2
  )
IS
BEGIN
  x_return_status := 'S';

Line: 1056

END TASK_ASSIGN_POST_UPDATE;

Line: 1058

PROCEDURE TASK_ASSIGN_PRE_DELETE
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   TASK_ASSIGN_PRE_DELETE

 Purpose:
   Retrieve more info about the task_assignment (old values)
   and call the task-assignment-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   ??-???-???? ?     Created

********************************************************/
  CURSOR c_task_assign
    ( b_task_assignment_id NUMBER
    )
  IS
    SELECT task_id
    ,      resource_id
    FROM   jtf_task_assignments jta
    WHERE  task_assignment_id = b_task_assignment_id;

Line: 1116

    , trigger_mode         => 'ON-DELETE'
    );

Line: 1120

END TASK_ASSIGN_PRE_DELETE;

Line: 1122

PROCEDURE CUST_RELATIONS_POST_INSERT
  ( x_return_status OUT VARCHAR2
  )
IS
/********************************************************
 Name:
   CUST_RELATIONS_POST_INSERT

 Purpose:
   Retrieve more info about the ship_to_address(new values)
   and call the ship-to-address-trigger-handler.

 Arguments:
   x_return_status   'S' indicates successfull completion.
                     Any other value indicates an error.

 Known Limitations:

 Notes:

 History:
   21-JAN-2002 ASOYKAN  Created

********************************************************/
  CURSOR c_cust_relations
    ( b_rs_cust_relation_id NUMBER
    )
  IS
    SELECT crcr.resource_id
    ,      hps.party_id
    FROM   csp_rs_cust_relations  crcr
    ,      hz_cust_acct_sites_all hcas_all
    ,      hz_party_sites         hps
    WHERE  crcr.customer_id       = hcas_all.cust_account_id
    AND    hcas_all.party_site_id = hps.party_site_id
    AND    rs_cust_relation_id    = b_rs_cust_relation_id
    AND    crcr.resource_type     = 'RS_EMPLOYEE';

Line: 1178

    , trigger_mode        => 'ON-INSERT'
    );

Line: 1182

END CUST_RELATIONS_POST_INSERT;

Line: 1197

   and is fired in case of insert, update or delete.

 Arguments:


 Known Limitations:

 Notes:

 History:
   ??-???-???? ?       Created
********************************************************/
  CURSOR c_incident
    ( v_incident_id NUMBER
    )
  IS
    SELECT server_id
    FROM   asg_incident_acc
    WHERE  incident_id = v_incident_id;

Line: 1220

  IF trigger_mode = 'ON-UPDATE'
  AND o_customer_id <> n_customer_id
  THEN
    OPEN c_incident
      ( v_incident_id => incident_id
      );

Line: 1239

      UpdateAccesses_Partyid
        ( x_party_id  => o_customer_id
	, x_server_id => v_server_id
	, x_op        => '-'
	);

Line: 1244

      UpdateAccesses_Partyid
        ( x_party_id  => n_customer_id
	, x_server_id => v_server_id
	, x_op        => '+'
	);

Line: 1273

   and is fired in case of insert, update or delete.

 Arguments:


 Known Limitations:

 Notes:

 History:
   ??-???-???? ?       Created
********************************************************/
  CURSOR c_task
    ( v_task_id NUMBER
    )
  IS
    SELECT server_id
    FROM   asg_task_acc
    WHERE  task_id = v_task_id;

Line: 1297

  IF trigger_mode = 'ON-UPDATE'
  AND o_source_object_id <> n_source_object_id
  THEN
    OPEN c_task
      ( v_task_id => n_task_id
      );

Line: 1318

        UpdateAccesses_Incidentid
	  ( x_incident_id => o_source_object_id
	  , x_server_id   => v_server_id
	  , x_op          => '-'
	  );

Line: 1327

        UpdateAccesses_Incidentid
	  ( n_source_object_id
	  , v_server_id
	  , '+'
	  );

Line: 1355

   and is fired in case of insert, update or delete.

 Arguments:


 Known Limitations:

 Notes:

 History:
   ??-???-???? ?       Created
********************************************************/
  CURSOR c_device_users
    ( v_resource_id number
    )
  IS
    SELECT server_id
    FROM   asg_server_resources
    WHERE  resource_id = v_resource_id;

Line: 1380

  IF trigger_mode = 'ON-INSERT'
  THEN
    -- Add this task to all the middle tiers for the resource
    OPEN c_device_users
      ( v_resource_id => n_resource_id
      );

Line: 1393

      UpdateAccesses_Taskid
        ( x_task_id   => o_task_id
	, x_server_id => v_server_id
	, x_op        => '+'
	);

Line: 1401

  ELSIF trigger_mode = 'ON-UPDATE'
  THEN
    IF n_resource_id <> o_resource_id
    THEN
      -- Remove the task from all the middle tiers for this old resource
      OPEN c_device_users
        ( v_resource_id => o_resource_id
        );

Line: 1416

        UpdateAccesses_Taskid
	  ( x_task_id   => o_task_id
	  , x_server_id => v_old_server_id
	  , x_op        => '-'
	  );

Line: 1435

        UpdateAccesses_Taskid
	  ( x_task_id   => n_task_id
	  , x_server_id => v_server_id
	  , x_op        => '+'
	  );

Line: 1444

  ELSIF Trigger_Mode = 'ON-DELETE'
  THEN
    -- Delete this task from all the middle tiers for this resource
    OPEN c_device_users
      ( v_resource_id => o_resource_id
      );

Line: 1457

      UpdateAccesses_Taskid
        ( x_task_id   => o_task_id
	, x_server_id => v_old_server_id
	, x_op        => '-'
	);

Line: 1478

   and is fired in case of insert, update or delete.

 Arguments:


 Known Limitations:

 Notes:

 History:
   ??-???-???? ?       Created
********************************************************/
  CURSOR c_primary_contact
    ( x_incident_id NUMBER
    )
  IS
    SELECT party_id
    FROM   cs_hz_sr_contact_points contact
    WHERE  contact.incident_id = x_incident_id
    AND    contact.primary_flag = 'Y'
    AND    EXISTS (SELECT incident_id
                   FROM   asg_incident_acc acc
                   WHERE  acc.incident_id = x_incident_id
                  );

Line: 1507

    SELECT server_id
    FROM   asg_incident_acc
    WHERE  incident_id = x_incident_id;

Line: 1532

      UpdateAccesses_Partyid
        ( x_party_id  => l_party_id
	, x_server_id => l_server_id
	, x_op        => x_op
	);

Line: 1557

   and is fired in case of insert, update or delete.

 Arguments:


 Known Limitations:

 Notes:

 History:
   21-JAN-2002 ASOYKAN    Created
********************************************************/
  CURSOR c_device_users
    ( v_resource_id number
    )
  IS
    SELECT server_id
    FROM   asg_server_resources
    WHERE  resource_id = v_resource_id;

Line: 1581

    SELECT server_id
    FROM   asg_party_acc
    WHERE  party_id = v_party_id;

Line: 1587

  IF trigger_mode = 'ON-INSERT'
  THEN
    -- Add this party to all the middle tiers for the resource
    OPEN c_device_users
      ( v_resource_id => resource_id
      );

Line: 1600

      UpdateAccesses_Partyid
        ( x_party_id  => o_party_id
	, x_server_id => v_server_id
	, x_op        => '+'
	);

Line: 1609

  ELSIF trigger_mode = 'ON-UPDATE'
  THEN
    NULL;