APPS.IEX_STRY_API_PUB SQL Statements

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

Line: 73

  **Appends to a message  the name of the object anf the operation (insert, update ,delete)
*/
PROCEDURE AddfailMsg
  ( p_object	    IN	VARCHAR2,
    p_operation 	IN	VARCHAR2 ) IS

BEGIN
      fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');

Line: 114

       select status_code
       from iex_strategies
       where strategy_id = p_strategy_id ;

Line: 119

  select delinquency_id,object_id,object_type
  from iex_strategies
  where strategy_id = p_strategy_id ;

Line: 193

                  iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'Calling set_strategy to update the status_code');

Line: 325

          vPLSQL := 'SELECT COUNT(1) ' ||
                  	 ' FROM ar_payment_schedules_all ps, ' ||
                          ' iex_delinquencies_all del WHERE ' ||
                            l_where_clause ||
                      ' AND ps.payment_schedule_id = del.payment_schedule_id ' ||
                      ' AND ps.status = ''OP'' ' ||
                      ' AND del.status IN (''DELINQUENT'', ''PREDELINQUENT'') ' ||
                  		' AND NOT EXISTS(SELECT 1 ' ||
                                    	 ' FROM iex_promise_details pd ' ||
                                      ' WHERE pd.delinquency_id = del.delinquency_id ' ||
                  		                  ' AND pd.status = ''COLLECTABLE'' ' ||
                  		                  ' AND pd.state = ''PROMISE''  ' ||
                  	               ' GROUP BY pd.delinquency_id ' ||
                  		               ' HAVING sum(nvl(pd.promise_amount,0)) >= ps.amount_due_remaining)';

Line: 392

 /** update work item and call send signal
  * if send signal fails, roolback the work item
  **/

PROCEDURE UPDATE_AND_SENDSIGNAL( P_strategy_work_item_Rec  IN
                                          iex_strategy_work_items_pvt.strategy_work_item_Rec_Type,
                                 p_commit                  IN VARCHAR2,
                                 x_return_status           OUT NOCOPY VARCHAR2,
                                 x_msg_count               OUT NOCOPY NUMBER,
                                 x_msg_data                OUT NOCOPY VARCHAR2)IS

l_return_status VARCHAR2(1);

Line: 412

      SAVEPOINT UPDATE_AND_SENDSIGNAL;

Line: 417

        select  status_code INTO l_status_code from iex_strategy_work_items
          where strategy_id = p_strategy_work_item_rec.strategy_id
          and work_item_id = p_strategy_work_item_rec.work_item_id;

Line: 425

            IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'work item not in status OPEN or ONHOLD');

Line: 434

          IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'work item not found');

Line: 439

      iex_strategy_work_items_pvt.update_strategy_work_items(
                 p_api_version_number     => 2.0,
                 p_init_msg_list          =>  FND_API.G_TRUE,
                 p_validation_level       =>  FND_API.G_VALID_LEVEL_FULL,
                 p_commit                 =>  p_commit,
                 x_return_status          => l_return_status,
                 x_msg_count              => l_msg_count,
                 x_msg_data               => l_msg_data,
                 p_strategy_work_item_rec => p_strategy_work_item_rec,
                 xo_object_version_number => l_object_version_number);

Line: 454

           ROLLBACK TO UPDATE_AND_SENDSIGNAL;

Line: 465

                IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');

Line: 473

               select  count(*) INTO v_result from iex_strategies
               where strategy_id =p_strategy_work_item_rec.strategy_id
               and next_work_item_id =p_strategy_work_item_rec.work_item_id
               and status_code ='OPEN';

Line: 481

                     IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');

Line: 483

                  rollback to  UPDATE_AND_SENDSIGNAL;

Line: 489

                  IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');

Line: 502

     ROLLBACK TO UPDATE_AND_SENDSIGNAL;

Line: 505

        IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);

Line: 508

END UPDATE_AND_SENDSIGNAL;

Line: 510

/** update work item and call send signal
  * if send signal fails, roolback the work item
  * this is called from the JSP page , so passing
  * columns instead of record type
  * temporary fix till rosetta is fixed
  * if the status is not changed to 'CLOSED'
  * 'CANCELLED' THEN just update the work item
  * do not call send signal
  *06/21/02 --jsanju
  **/

PROCEDURE UPDATE_AND_SENDSIGNAL(p_status         IN  VARCHAR2
                                ,p_work_item_id  IN  NUMBER
                                ,p_resource_id   IN  NUMBER
                                ,p_execute_start IN  DATE
                                ,p_execute_end   IN  DATE
                                ,p_commit        IN VARCHAR2
                                ,x_return_status OUT NOCOPY VARCHAR2
                                ,x_msg_count     OUT NOCOPY NUMBER
                                ,x_msg_data      OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1);

Line: 541

    select object_version_number,strategy_id
    from iex_strategy_work_items
    where work_item_id = p_work_item_id ;

Line: 548

         IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'START UPDATE AND  SEND SIGNAL');

Line: 550

      SAVEPOINT UPDATE_AND_SENDSIGNAL;

Line: 567

        IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'BEFORE CALLING UPDATE WORK ITEM PVT');

Line: 569

          iex_strategy_work_items_pvt.update_strategy_work_items(
                 p_api_version_number     => 2.0,
                 p_init_msg_list          =>  FND_API.G_TRUE,
                 p_validation_level       =>  FND_API.G_VALID_LEVEL_FULL,
                 p_commit                 =>  FND_API.G_FALSE,
                 x_return_status          => l_return_status,
                 x_msg_count              => l_msg_count,
                 x_msg_data               => l_msg_data,
                 p_strategy_work_item_rec => l_strategy_work_item_rec,
                 xo_object_version_number => l_version_number);

Line: 581

        IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'Status of work item update ' ||l_return_status);

Line: 588

           ROLLBACK TO UPDATE_AND_SENDSIGNAL;

Line: 601

                   IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');

Line: 609

               select  count(*) INTO v_result from iex_strategies
               where strategy_id =l_strategy_work_item_rec.strategy_id
               and next_work_item_id =l_strategy_work_item_rec.work_item_id
               and status_code ='OPEN';

Line: 617

                       IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');

Line: 619

                    rollback to  UPDATE_AND_SENDSIGNAL;

Line: 625

                     IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');

Line: 639

     ROLLBACK TO UPDATE_AND_SENDSIGNAL;

Line: 642

        IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);

Line: 646

END    UPDATE_AND_SENDSIGNAL;

Line: 675

select
       --ias.activity_result_code Result,
       -- ias.error_name ERROR_NAME,
        ias.error_message ERROR_MESSAGE
       -- ,ias.error_stack ERROR_STACK
from wf_item_activity_statuses ias
where
 ias.item_type ='IEXSTRY'
 and ias.item_key =p_strategy -- changed for bug 13111098 pnaveenk
 and  ias.activity_status     = 'ERROR';*/

Line: 787

       IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);

Line: 794

PROCEDURE UPDATE_WORKITEM       (p_status         IN  VARCHAR2
                                ,p_work_item_id  IN  NUMBER
                                ,p_resource_id   IN  NUMBER
                                ,p_execute_start IN  DATE
                                ,p_execute_end   IN  DATE
                                ,p_commit        IN VARCHAR2
                                ,x_return_status OUT NOCOPY VARCHAR2
                                ,x_msg_count     OUT NOCOPY NUMBER
                                ,x_msg_data      OUT NOCOPY VARCHAR2) IS


l_return_status VARCHAR2(1);

Line: 816

    select object_version_number,strategy_id
    from iex_strategy_work_items
    where work_item_id = p_work_item_id ;

Line: 823

         IEX_DEBUG_PUB.logmessage('START UPDATE_WORKITEM');

Line: 825

      SAVEPOINT UPDATE_WORKITEM;

Line: 842

        IEX_DEBUG_PUB.logmessage('UPDATE_WORKITEM: ' || 'BEFORE CALLING UPDATE WORK ITEM PVT');

Line: 844

          iex_strategy_work_items_pvt.update_strategy_work_items(
                 p_api_version_number     => 2.0,
                 p_init_msg_list          =>  FND_API.G_TRUE,
                 p_validation_level       =>  FND_API.G_VALID_LEVEL_FULL,
                 p_commit                 =>  FND_API.G_FALSE,
                 x_return_status          => l_return_status,
                 x_msg_count              => l_msg_count,
                 x_msg_data               => l_msg_data,
                 p_strategy_work_item_rec => l_strategy_work_item_rec,
                 xo_object_version_number => l_version_number);

Line: 856

        IEX_DEBUG_PUB.logmessage('UPDATE_WORKITEM: ' || 'Status of work item update ' ||l_return_status);

Line: 866

     ROLLBACK TO UPDATE_WORKITEM;

Line: 869

        IEX_DEBUG_PUB.logmessage('inexception of UPDATE_WORKITEM '||sqlerrm);

Line: 872

END UPDATE_WORKITEM;

Line: 1018

                UPDATE iex_strategy_work_items
                SET uwq_status = p_uwq_status,
                    uwq_active_date = l_uwq_active_date,
                    uwq_complete_date = l_uwq_complete_date,
                    last_update_date = sysdate,
                    last_updated_by = g_user_id
                where
                    work_item_id = p_work_item_id_tbl(i);

Line: 1028

		iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');

Line: 1084

       select b.name into Newworkitemname from iex_stry_temp_work_items_tl B
          where B.LANGUAGE = userenv('LANG')
            and b.work_item_temp_id = p_work_item_temp_id;

Line: 1096

          select count(name) into Newcnt
             from iex_stry_temp_work_items_tl B, FND_LANGUAGES L
             where L.INSTALLED_FLAG in ('I', 'B') and B.LANGUAGE = userenv('LANG')
               and b.name = Newworkitemname;

Line: 1115

  insert into iex_stry_temp_work_items_b
    (work_item_temp_id, competence_id, work_type, category_type, priority_type, optional_yn,
     option_wait_time, option_wait_time_uom, pre_execution_wait, post_execution_wait, execution_time_uom,
     closure_time_limit, closure_time_uom, workflow_item_type, same_resource_yn,
     last_update_date, last_updated_by, last_update_login, creation_date, created_by, object_version_number,
     fulfil_temp_id, escalate_yn, notify_yn, schedule_wait, schedule_uom, enabled_flag, xdo_template_id
    )
    select p_new_work_item_temp_id, competence_id, work_type, category_type, priority_type, optional_yn,
      option_wait_time, option_wait_time_uom, pre_execution_wait, post_execution_wait, execution_time_uom,
      closure_time_limit, closure_time_uom, workflow_item_type, same_resource_yn,
      sysdate, fnd_global.user_id, fnd_global.user_id, sysdate, fnd_global.user_id, 1.0,
      fulfil_temp_id, escalate_yn, notify_yn, schedule_wait, schedule_uom, enabled_flag, xdo_template_id
    from iex_stry_temp_work_items_b
    where work_item_temp_id = p_work_item_temp_id;

Line: 1130

   INSERT INTO IEX_STRY_TEMP_WORK_ITEMS_TL
     (WORK_ITEM_TEMP_ID,NAME,DESCRIPTION,LANGUAGE,SOURCE_LANG,
      CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,LAST_UPDATED_BY
     )
     -- Bug 8233425 select p_new_work_item_temp_id, 'Copy of ' || b.name,b.description,l.language_code ,b.SOURCE_LANG,
     select p_new_work_item_temp_id, Newworkitemname,b.description,l.language_code ,b.SOURCE_LANG,
       sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.user_id
     from iex_stry_temp_work_items_tl B, FND_LANGUAGES L
     where L.INSTALLED_FLAG in ('I', 'B') and B.LANGUAGE = userenv('LANG')
           and b.work_item_temp_id = p_work_item_temp_id;

Line: 1141

   INSERT INTO iex_strategy_work_skills
     (work_skill_id, WORK_ITEM_TEMP_ID,competence_id,
      CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,LAST_UPDATED_BY, object_version_number
     )
     select iex_strategy_work_skills_s.NEXTVAL, p_new_work_item_temp_id, a.competence_id,
       sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.user_id, a.object_version_number
     from iex_strategy_work_skills a
     where a.work_item_temp_id = p_work_item_temp_id;