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
	      AND Trunc(SYSDATE) BETWEEN Effective_Start_Date AND Effective_End_Date;

Line: 419

              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: 438

            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: 453

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 465

            po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);

Line: 501

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 515

      g_next_approvers.delete;

Line: 529

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );

Line: 540

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );

Line: 584

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 601

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 621

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 634

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 641

         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: 655

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

Line: 701

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 704

    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: 718

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 760

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 765

    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: 777

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 820

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 825

    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: 842

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);

Line: 874

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

is
pragma AUTONOMOUS_TRANSACTION;

Line: 893

  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: 899

  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: 910

       /* 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: 933

end insertActionHistory;

Line: 957

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: 976

    l_progress := 'Insert_Action_History: 001';

Line: 978

         PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1006

        l_progress := 'Insert_Action_History: 004 - Forward_Action_History';

Line: 1007

        insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);

Line: 1009

        l_progress := 'Insert_Action_History: 005 - Forward_Action_History';

Line: 1011

             PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1031

    l_progress := 'Insert_Action_History: 999';

Line: 1033

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

Line: 1040

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

Line: 1041

    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: 1043

 END Insert_Action_History;

Line: 1067

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: 1090

    l_progress := 'Update_Action_History_Approve: 001';

Line: 1092

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

Line: 1126

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

Line: 1129

            PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1132

        UpdateActionHistory(l_document_id, l_action,
                            l_note, l_current_approver);

Line: 1148

    l_progress := 'Update_Action_History_Approve: 003';

Line: 1150

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

Line: 1157

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

Line: 1158

    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: 1161

END Update_Action_History_Approve;

Line: 1185

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: 1209

    l_progress := 'Update_Action_History_Reject: 001';

Line: 1211

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

Line: 1245

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

Line: 1248

             PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1251

        UpdateActionHistory(l_document_id, l_action,
                            l_note, l_current_approver);

Line: 1267

    l_progress := 'Update_Action_History_Reject: 003';

Line: 1269

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1276

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

Line: 1277

    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: 1280

END Update_Action_History_Reject;

Line: 1303

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: 1327

    l_progress := 'Update_Action_History_Timeout: 001';

Line: 1329

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

Line: 1363

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

Line: 1366

             PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1369

        UpdateActionHistory(l_document_id, l_action,
                            l_note, l_current_approver);

Line: 1378

    l_progress := 'Update_Action_History_Timeout: 003';

Line: 1380

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1387

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

Line: 1388

    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: 1391

END Update_Action_History_Timeout;

Line: 1450

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1455

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

Line: 1467

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1533

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1538

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

Line: 1550

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

Line: 1583

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

pragma AUTONOMOUS_TRANSACTION;

Line: 1594

       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: 1606

       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: 1622

END UpdateActionHistory;

Line: 1665

        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: 1680

                  SELECT full_name
                  INTO l_next_approver_name
                  FROM per_all_people_f person
                  WHERE person_id = l_first_approver_id
		  AND Trunc(SYSDATE) BETWEEN Effective_Start_Date AND Effective_End_Date ;

Line: 1689

                  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: 1716

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

Line: 1730

                     g_next_approvers.delete(l_approver_index);