DBA Data[Home] [Help]

APPS.IA_WF_REQUEST_PKG SQL Statements

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

Line: 5

PROCEDURE Insert_Approval
         (p_request_id            in  number
         ,p_approver_id           in  number
         ,p_approval_status       in  varchar2
         ,p_approval_chain_phase  in  varchar2
         ,p_approval_id           out nocopy number
         );
Line: 23

PROCEDURE Update_Approval_Status
         (p_approval_id      in number
         ,p_chain_phase      in varchar2
         ,p_approval_status  in varchar2
         );
Line: 31

PROCEDURE Update_Approval_Notify
         (p_approval_id      in number
         ,p_notification_id  in number
         ,p_user_comment     in varchar2
         );
Line: 38

PROCEDURE Update_Request_Header_Status
         (p_request_id       in number
         ,p_status           in varchar2
         );
Line: 43

PROCEDURE Update_Request_Line_Status
         (p_request_id       in number
         ,p_status           in varchar2
         );
Line: 119

    select book_type_code
          ,preparer_id
          ,requester_id
          ,responsibility_id
          ,request_type
          ,purpose
          ,status
          ,releasing_approver_id
          ,receiving_approver_id
    into l_book_type_code
        ,l_preparer_id
        ,l_requester_id
        ,l_responsibility_id
        ,l_request_type
        ,l_purpose
        ,l_status
        ,l_releasing_approver_id
        ,l_receiving_approver_id
    from ia_request_headers
    where request_id=p_request_id;
Line: 211

      select book_type_code
      into l_dummy_text
      from fa_book_controls
      where book_type_code=l_book_type_code
        and book_class='CORPORATE'
        and rownum < 2;
Line: 233

    select responsibility_id
    into l_dummy
    from fnd_responsibility
    where responsibility_id=l_responsibility_id
      and application_id=IA_WF_UTIL_PKG.GetApplicationID;
Line: 330

    update ia_request_headers
    set status = IA_WF_UTIL_PKG.HeaderStatusPendingApproval
       ,last_update_date = SYSDATE
       ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
       ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
    where request_id = p_request_id;
Line: 337

    update ia_request_details
    set status = IA_WF_UTIL_PKG.LineStatusPending
       ,last_update_date = SYSDATE
       ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
       ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
    where request_id = p_request_id;
Line: 346

      FND_MESSAGE.set_name('IA', 'IA_HEADER_STATUS_UPDATE_ERROR'); -- Error: Unable to update the status for request id, REQUEST_ID
Line: 1019

    select 1
    into l_dummy
    from ia_request_headers
    where request_id=p_request_id;
Line: 1024

    select max(approval_id)
    into l_approval_id
    from ia_request_approvals
    where request_id=p_request_id
      and status=IA_WF_UTIL_PKG.ApprovalStatusPendingApproval;
Line: 1043

    select *+  leading(grp_id_view)  *
           notification_id
          ,recipient_role
          ,original_recipient
    into l_notification_id
        ,l_recipient_role
        ,l_original_recipient
    from wf_notifications wfn ,
       ( select notification_id group_id
         from wf_item_activity_statuses
         where item_type = 'IAWF'
           and item_key = p_request_id
       )  grp_id_view
    where grp_id_view.group_id = wfn.group_id;
Line: 1099

  debugInfo := 'Update ia_approval_requests with the notification ID';
Line: 1102

  Update_Approval_Notify(p_approval_id     => l_approval_id
                        ,p_notification_id => l_notification_id
                        ,p_user_comment    => substr(p_comment,1,4000));
Line: 1136

PROCEDURE Insert_Next_Approver
         (itemtype in varchar2
         ,itemkey in varchar2
         ,actid in number
         ,funcmode in varchar2
         ,result out nocopy varchar2)
IS

  l_error_message	VARCHAR2(2000);
Line: 1150

  callingProgram        VARCHAR2(80)    := 'Insert_Next_Approver';
Line: 1193

    debugInfo := 'Insert into IA_REQUEST_APPROVALS table the next approver with status of Pending Approval';
Line: 1198

    Insert_Approval(p_request_id           => l_request_id
                   ,p_approver_id          => l_approver_id
                   ,p_approval_status      => IA_WF_UTIL_PKG.ApprovalStatusPendingApproval
                   ,p_approval_chain_phase => l_chain_phase
                   ,p_approval_id          => l_approval_id);
Line: 1217

    debugInfo := 'Insert into IA_APPROVERS_LIST_T';
Line: 1263

END Insert_Next_Approver;
Line: 1267

PROCEDURE Insert_Approval
         (p_request_id            in  number
         ,p_approver_id           in  number
         ,p_approval_status       in  varchar2
         ,p_approval_chain_phase  in  varchar2
         ,p_approval_id           out nocopy number
         )
IS

  debugInfo             VARCHAR2(255)   := NULL;
Line: 1280

  callingProgram        VARCHAR2(80)    := 'Insert_Approval';
Line: 1293

       select ia_request_approvals_s.nextval
       into l_approval_id
       from dual;
Line: 1298

       debugInfo := 'Insert into IA_REQUEST_APPROVALS';
Line: 1300

       insert into ia_request_approvals
       (approval_id
       ,request_id
       ,approver_id
       ,status
       ,transaction_date
       ,approval_chain_phase
       ,created_by
       ,creation_date
       ,last_update_date
       ,last_updated_by
       ,last_update_login
       )
       values(l_approval_id
             ,p_request_id
             ,p_approver_id
             ,p_approval_status
             ,NULL
             ,p_approval_chain_phase
             ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
             ,sysdate
             ,sysdate
             ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
             ,nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
             );
Line: 1338

END Insert_Approval;
Line: 1375

  debugInfo := 'Delete rows from IA_APPROVERS_LIST_T';
Line: 1382

    delete from ia_approvers_list_t
    where request_id = p_request_id;
Line: 1422

         select ia_approvers_list_t_s.nextval
         into l_list_id
         from dual;
Line: 1429

         debugInfo := 'Insert into IA_APPROVERS_LIST_T';
Line: 1434

         insert into ia_approvers_list_t
         (list_id
         ,request_id
         ,approver_id
         ,approval_order
         ,status
         ,approval_chain_phase
         ,created_by
         ,creation_date
         ,last_update_date
         ,last_updated_by
         ,last_update_login
         )
         values(l_list_id
               ,p_request_id
               ,l_approver_id
               ,l_approval_order
               ,IA_WF_UTIL_PKG.ApprovalStatusPendingApproval -- PENDING
               ,l_chain_phase
               ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
               ,sysdate
               ,sysdate
               ,nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
               ,nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
               );
Line: 1481

PROCEDURE Update_Approval_Notify
         (p_approval_id      in number
         ,p_notification_id  in number
         ,p_user_comment     in varchar2
         )
IS

  debugInfo             VARCHAR2(255)   := NULL;
Line: 1492

  callingProgram        VARCHAR2(80)    := 'Update_Approval_Notify';
Line: 1496

  update ia_request_approvals
  set notification_id = p_notification_id
     ,user_comment = p_user_comment
     ,last_update_date = SYSDATE
     ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
     ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
  where approval_id = p_approval_id;
Line: 1504

END Update_Approval_Notify;
Line: 1508

PROCEDURE Update_Approval_Status
         (p_approval_id      in number
         ,p_chain_phase      in varchar2
         ,p_approval_status  in varchar2
         )
IS

  debugInfo             VARCHAR2(255)   := NULL;
Line: 1519

  callingProgram        VARCHAR2(80)    := 'Update_Approval_Status';
Line: 1527

  update ia_request_approvals
  set status = p_approval_status
     ,approval_chain_phase = p_chain_phase
     ,transaction_date = SYSDATE
     ,last_update_date = SYSDATE
     ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
     ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
  where approval_id = p_approval_id;
Line: 1538

END Update_Approval_Status;
Line: 1541

PROCEDURE Update_Request_Header_Status
         (p_request_id       in number
         ,p_status           in varchar2
         )
IS

  debugInfo             VARCHAR2(255)   := NULL;
Line: 1551

  callingProgram        VARCHAR2(80)    := 'Update_Request_Header_Status';
Line: 1557

  update ia_request_headers
  set status = p_status
     ,last_update_date = SYSDATE
     ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
     ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
  where request_id = p_request_id;
Line: 1566

END Update_Request_Header_Status;
Line: 1569

PROCEDURE Update_Request_Line_Status
         (p_request_id       in number
         ,p_status           in varchar2
         )
IS

  debugInfo             VARCHAR2(255)   := NULL;
Line: 1579

  callingProgram        VARCHAR2(80)    := 'Update_Request_Line_Status';
Line: 1590

  update ia_request_details
  set status = p_status
     ,last_update_date = SYSDATE
     ,last_updated_by = nvl(to_number(FND_PROFILE.VALUE('USER_ID')),-1)
     ,last_update_login = nvl(to_number(FND_PROFILE.VALUE('LOGIN_ID')),-1)
  where request_id = p_request_id
    and status = IA_WF_UTIL_PKG.LineStatusPending;
Line: 1600

END Update_Request_Line_Status;
Line: 1642

    Update_Approval_Notify(p_approval_id     => l_approval_id
                          ,p_notification_id => l_notification_id
                          ,p_user_comment    => l_user_comment);
Line: 1725

    debugInfo := 'Update Approval Status';
Line: 1740

      if (not IA_AME_REQUEST_PKG.UpdateApprovalStatus(RequestId  => l_request_id
                                                     ,ChainPhase => l_chain_phase
                                                     ,Approver   => tempApprover)) then
         FND_MESSAGE.set_name('IA', 'IA_AME_UPDATE_STATUS_ERROR'); -- Error occurred when updating approval status in AME.
Line: 1756

    Update_Approval_Status(p_approval_id     => l_approval_id
                          ,p_chain_phase     => l_chain_phase
                          ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusApproved);
Line: 1865

    tempDelegatee.api_insertion := AME_UTIL.apiInsertion;
Line: 1870

    debugInfo := 'Update AME Approval Status';
Line: 1884

      if (not IA_AME_REQUEST_PKG.UpdateApprovalStatus(RequestId  => l_request_id
                                                   ,ChainPhase => l_chain_phase
                                                   ,Approver   => tempApprover
                                                   ,Forwardee  => tempDelegatee)) then
         FND_MESSAGE.set_name('IA', 'IA_AME_UPDATE_STATUS_ERROR'); -- Error occurred when updating approval status in Oracle Approval Management.
Line: 1901

    Update_Approval_Status(p_approval_id     => l_approval_id
                          ,p_chain_phase     => l_chain_phase
                          ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusDelegated);
Line: 1992

    debugInfo := 'Update Approval Status';
Line: 2006

      if (not IA_AME_REQUEST_PKG.UpdateApprovalStatus(RequestId  => l_request_id
                                                   ,ChainPhase => l_chain_phase
                                                   ,Approver   => tempApprover)) then
         FND_MESSAGE.set_name('IA', 'IA_AME_UPDATE_STATUS_ERROR'); -- Error occurred when updating approval status in Oracle Approvals Management.
Line: 2022

    Update_Approval_Status(p_approval_id     => l_approval_id
                          ,p_chain_phase     => l_chain_phase
                          ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusRejected);
Line: 2033

    Update_Request_Header_Status(p_request_id  => l_request_id
                                ,p_status      => IA_WF_UTIL_PKG.HeaderStatusRejected);
Line: 2052

    Update_Request_Line_Status(p_request_id  => l_request_id
                              ,p_status      => IA_WF_UTIL_PKG.LineStatusRejected);
Line: 2142

    Update_Approval_Status(p_approval_id     => l_approval_id
                          ,p_chain_phase     => l_chain_phase
                          ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusRejected);
Line: 2149

    Update_Request_Header_Status(p_request_id  => l_request_id
                                ,p_status      => IA_WF_UTIL_PKG.HeaderStatusRejected);
Line: 2186

PROCEDURE Update_ApprovalStatus_To_Final
         (itemtype in varchar2
         ,itemkey in varchar2
         ,actid in number
         ,funcmode in varchar2
         ,result out nocopy varchar2)
IS

  debugInfo             VARCHAR2(255)   := NULL;
Line: 2198

  callingProgram        VARCHAR2(80)    := 'Update_ApprovalStatus_To_Final';
Line: 2248

      debugInfo := 'Update Approval Status to Finally Approved';
Line: 2254

      Update_Approval_Status(p_approval_id     => l_approval_id
                            ,p_chain_phase     => l_chain_phase
                            ,p_approval_status => IA_WF_UTIL_PKG.ApprovalStatusFinallyApproved);
Line: 2261

    debugInfo := 'Update Header Status to Approved';
Line: 2266

    Update_Request_Header_Status(p_request_id  => l_request_id
                                ,p_status      => IA_WF_UTIL_PKG.HeaderStatusApproved);
Line: 2301

END Update_ApprovalStatus_To_Final;
Line: 2390

PROCEDURE Update_LineStatus_To_OnReview
         (itemtype in varchar2
         ,itemkey in varchar2
         ,actid in number
         ,funcmode in varchar2
         ,result out nocopy varchar2)
IS

  l_request_id number(15);
Line: 2404

  callingProgram        VARCHAR2(80)    := 'Update_LineStatus_To_Post';
Line: 2420

    debugInfo := 'Update request line status to ON_REVIEW';
Line: 2425

    Update_Request_Line_Status(p_request_id  => l_request_id
                              ,p_status      => IA_WF_UTIL_PKG.LineStatusOnReview);
Line: 2447

END Update_LineStatus_To_OnReview;
Line: 2449

PROCEDURE Update_LineStatus_To_Post
         (itemtype in varchar2
         ,itemkey in varchar2
         ,actid in number
         ,funcmode in varchar2
         ,result out nocopy varchar2)
IS

  l_request_id number(15);
Line: 2463

  callingProgram        VARCHAR2(80)    := 'Update_LineStatus_To_Post';
Line: 2480

    debugInfo := 'Update request line status to POST';
Line: 2485

    Update_Request_Line_Status(p_request_id  => l_request_id
                              ,p_status      => IA_WF_UTIL_PKG.LineStatusPost);
Line: 2493

    debugInfo := 'Update Header Status to Post';
Line: 2498

    Update_Request_Header_Status(p_request_id  => l_request_id
                                ,p_status      => IA_WF_UTIL_PKG.HeaderStatusPost);
Line: 2533

END Update_LineStatus_To_Post;