APPS.POR_AME_RCO_WF_PVT SQL Statements

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

Line: 12

PROCEDURE UpdateActionHistory(p_document_id      NUMBER,
                              p_action           VARCHAR2,
                              p_note             VARCHAR2,
                              p_current_approver NUMBER);

Line: 61

  l_insertion_type            VARCHAR2(30);

Line: 72

           PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 94

          PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 118

                      PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);

Line: 129

              PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 138

                PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 143

                PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 185

           PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);

Line: 261

           PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 283

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 286

        SELECT
            to_char(l_document_id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
        INTO l_item_key
        FROM sys.dual;

Line: 391

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 401

            select first_position_id, first_approver_id
            into l_first_position_id, l_first_approver_id
            from po_requisition_headers_all
            where l_document_id = requisition_header_id;

Line: 410

              SELECT full_name
              INTO l_next_approver_name
              FROM per_all_people_f person
              WHERE person_id = l_first_approver_id;

Line: 418

              SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
                       SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
                       WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
                       and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
                       and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
                       and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
                       and asg.assignment_status_type_id not in (
                          SELECT assignment_status_type_id FROM per_assignment_status_types
                          WHERE per_system_status = 'TERM_ASSIGN'
                       ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
            ) where rownum = 1;

Line: 437

            SELECT employee_id
               into l_next_approver_id
            FROM fnd_user
            WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
               and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);

Line: 452

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 464

            po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);

Line: 500

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 514

      g_next_approvers.delete;

Line: 528

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );

Line: 539

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );

Line: 583

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 600

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 620

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 633

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 640

         SELECT name into l_current_approver.name FROM
             ( SELECT name FROM wf_roles WHERE orig_system = l_current_approver.orig_system
                 and orig_system_id = l_current_approver.orig_system_id
                 order by start_date
              )
         WHERE rownum = 1;

Line: 654

    ame_api2.updateApprovalStatus( applicationIdIn=>applicationId,
                                   transactionIdIn=>l_document_id,
                                   transactionTypeIn=>l_transaction_type,
                                   approverIn => l_current_approver);

Line: 700

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 703

    SELECT parent_item_type, parent_item_key
        into l_parent_item_type, l_parent_item_key
    FROM wf_items
    WHERE item_type = itemtype and item_key = itemkey;

Line: 717

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 759

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 764

    SELECT parent_item_type, parent_item_key
        into l_parent_item_type, l_parent_item_key
    FROM wf_items
    WHERE item_type = itemtype and item_key = itemkey;

Line: 776

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 819

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 824

    SELECT parent_item_type, parent_item_key
        into l_parent_item_type, l_parent_item_key
    FROM wf_items
    WHERE item_type = itemtype and item_key = itemkey;

Line: 841

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 873

procedure insertActionHistory( p_req_header_id in number,
                               p_employee_id in number,
                               p_approval_group_id in number )

is
pragma AUTONOMOUS_TRANSACTION;

Line: 892

  SELECT max(sequence_num)
  INTO l_sequence_num
  FROM PO_ACTION_HISTORY
  WHERE object_type_code = 'REQUISITION'
      AND object_id = p_req_header_id;

Line: 898

  SELECT object_sub_type_code,
          object_revision_num, approval_path_id, request_id,
          program_application_id, program_date, program_id
  INTO l_object_sub_type_code,
          l_object_revision_num, l_approval_path_id, l_request_id,
          l_program_application_id, l_program_date, l_program_id
  FROM PO_ACTION_HISTORY
  WHERE object_type_code = 'REQUISITION'
     AND object_id = p_req_header_id
     AND sequence_num = l_sequence_num;

Line: 909

       /* update po action history */
           po_forward_sv1.insert_action_history (
      	   p_req_header_id,
      	   'REQUISITION',
     	   l_object_sub_type_code,
     	   l_sequence_num + 1,
     	   NULL,
     	   NULL,
     	   p_employee_id,
     	   NULL,
     	   NULL,
		l_object_revision_num,
		NULL,                  /* offline_code */
		l_request_id,
		l_program_application_id,
		l_program_id,
		l_program_date,
     	   fnd_global.user_id,
     	   fnd_global.login_id,
           p_approval_group_id);

Line: 932

end insertActionHistory;

Line: 956

procedure Insert_Action_History( itemtype        in varchar2,
                                 itemkey         in varchar2,
                                 actid           in number,
                                 funcmode        in varchar2,
                                 resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(500) := '000';

Line: 975

    l_progress := 'Insert_Action_History: 001';

Line: 977

         PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1005

        l_progress := 'Insert_Action_History: 004 - Forward_Action_History';

Line: 1006

        insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);

Line: 1008

        l_progress := 'Insert_Action_History: 005 - Forward_Action_History';

Line: 1010

             PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1030

    l_progress := 'Insert_Action_History: 999';

Line: 1032

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1039

    wf_core.context('POR_AME_RCO_WF_PVT','Insert_Action_History',l_progress,sqlerrm);

Line: 1040

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.INSERT_ACTION_HISTORY');

Line: 1042

 END Insert_Action_History;

Line: 1066

procedure Update_Action_History_Approve( itemtype        in varchar2,
                                         itemkey         in varchar2,
                                         actid           in number,
                                         funcmode        in varchar2,
                                         resultout       out NOCOPY varchar2) IS
  l_progress                  VARCHAR2(500) := '000';

Line: 1089

    l_progress := 'Update_Action_History_Approve: 001';

Line: 1091

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1125

        l_progress := 'Update_Action_History_Approve: 002-'|| to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;

Line: 1128

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1131

        UpdateActionHistory(l_document_id, l_action,
                            l_note, l_current_approver);

Line: 1147

    l_progress := 'Update_Action_History_Approve: 003';

Line: 1149

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1156

    wf_core.context('POR_AME_RCO_WF_PVT','Update_Action_History_Approve',l_progress,sqlerrm);

Line: 1157

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.UPDATE_ACTION_HISTORY_APPROVE');

Line: 1160

END Update_Action_History_Approve;

Line: 1184

procedure Update_Action_History_Reject(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';

Line: 1208

    l_progress := 'Update_Action_History_Reject: 001';

Line: 1210

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1244

        l_progress := 'Update_Action_History_Reject: 002-'|| to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;

Line: 1247

             PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1250

        UpdateActionHistory(l_document_id, l_action,
                            l_note, l_current_approver);

Line: 1266

    l_progress := 'Update_Action_History_Reject: 003';

Line: 1268

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1275

    wf_core.context('POR_AME_RCO_WF_PVT','Update_Action_History_Reject',l_progress,sqlerrm);

Line: 1276

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_RCO_WF_PVT.Update_Action_History_Reject');

Line: 1279

END Update_Action_History_Reject;

Line: 1302

procedure Update_Action_History_Timeout(itemtype        in varchar2,
                                itemkey         in varchar2,
                                actid           in number,
                                funcmode        in varchar2,
                                resultout       out NOCOPY varchar2) IS

  l_progress                  VARCHAR2(100) := '000';

Line: 1326

    l_progress := 'Update_Action_History_Timeout: 001';

Line: 1328

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1362

        l_progress := 'Update_Action_History_Timeout: 002-'|| to_char(l_document_id)||'-'||
                           l_document_type||'-'||l_document_subtype;

Line: 1365

             PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1368

        UpdateActionHistory(l_document_id, l_action,
                            l_note, l_current_approver);

Line: 1377

    l_progress := 'Update_Action_History_Timeout: 003';

Line: 1379

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1386

    wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Timeout',l_progress,sqlerrm);

Line: 1387

    PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_Timeout');

Line: 1390

END Update_Action_History_Timeout;

Line: 1449

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1454

    UpdateActionHistory(l_doc_header_id, 'NO ACTION',
          fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED'), NULL);

Line: 1466

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1532

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1537

    UpdateActionHistory(l_doc_header_id, 'NO ACTION',
          fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED'), NULL);

Line: 1549

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1582

PROCEDURE UpdateActionHistory(p_document_id      NUMBER,
                              p_action           VARCHAR2,
                              p_note             VARCHAR2,
                              p_current_approver NUMBER) IS

pragma AUTONOMOUS_TRANSACTION;

Line: 1593

       UPDATE po_action_history
          SET action_code = p_action,
              note = p_note,
              action_date = sysdate
        WHERE object_id = p_document_id and
              employee_id = p_current_approver and
              action_code is null and
              object_type_code = 'REQUISITION'
              and rownum=1;

Line: 1605

       UPDATE po_action_history
          SET action_code = p_action,
              note = p_note,
              action_date = sysdate
        WHERE object_id = p_document_id and
              action_code is null and
              object_type_code = 'REQUISITION';

Line: 1621

END UpdateActionHistory;

Line: 1664

        select first_position_id, first_approver_id
        into l_first_position_id, l_first_approver_id
        from po_requisition_headers_all
        where documentId = requisition_header_id;

Line: 1679

                  SELECT full_name
                  INTO l_next_approver_name
                  FROM per_all_people_f person
                  WHERE person_id = l_first_approver_id;

Line: 1687

                  SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
                         SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
                          WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
                          and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
                          and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
                          and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
                          and asg.assignment_status_type_id not in (
                             SELECT assignment_status_type_id FROM per_assignment_status_types
                              WHERE per_system_status = 'TERM_ASSIGN'
                          ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
                  ) where rownum = 1;

Line: 1714

                     ame_api2.updateApprovalStatus( applicationIdIn    =>applicationId,
                                                    transactionIdIn    =>documentId,
                                                    transactionTypeIn  =>documentType,
                                                    approverIn         => g_next_approvers(l_approver_index)
                                                  );

Line: 1728

                     g_next_approvers.delete(l_approver_index);