Oracle Apps Knowledge Sharing

In this post, we have given a query which helps to derive the approval action history of a Purchase Requisition.

Key Table: PO_ACTION_HISTORY

Instance: R12.2.4

Query: 

  SELECT hou.name ou_name,
         hou.short_code ou_short_code,
         prha.segment1 pr_number,
         (SELECT pah.action_date  
            FROM po_action_history pah
           WHERE prha.requisition_header_id = pah.object_id
             AND pah.object_type_code = 'REQUISITION'
             AND pah.sequence_num = (SELECT MIN(sequence_num) 
                                       FROM po_action_history pah1
                                      WHERE pah1.action_code = 'SUBMIT'
                                         AND prha.requisition_header_id = pah1.object_id
                                         AND pah1.object_type_code= 'REQUISITION'
                                     )
          ) pr_last_submission_date,
          prha.creation_date,
         (SELECT prla.catalog_type
            FROM po_requisition_lines_all prla
           WHERE prha.requisition_header_id = prla.requisition_header_id
             AND ROWNUM < 2
         ) pr_type,
         NVL2((SELECT prla.urgent_flag 
                 FROM po_requisition_lines_all prla 
                WHERE prha.requisition_header_id = prla.requisition_header_id
                  AND urgent_flag = 'Y'
                  AND ROWNUM < 2
               ),'Y','N'
             ) urgent_flag,
        auth_status.meaning,
        authorization_status pr_status,
        pah.sequence_num action_seq_num,
        papf.full_name action_person,
        pah.action_code,
        pah.action_date,
        pah.note,
        prha.change_pending_flag	   
   FROM po_requisition_headers_all prha,
        fnd_lookup_values_vl auth_status,
        hr_operating_units hou,
        po_action_history pah,
        per_all_people_f papf	   
  WHERE prha.org_id = hou.organization_id
    AND prha.requisition_header_id = pah.object_id
    AND papf.person_id = pah.employee_id
    AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND pah.object_type_code    = 'REQUISITION'
    AND auth_status.lookup_type = 'AUTHORIZATION STATUS'
    AND auth_status.lookup_code = prha.authorization_status;

            In this post, we have given a query to fetch Requestor Initiated Purchase Order Change requests.

Test Instance: R12.2.4

Query:

SELECT hou.short_code,
       pcr_r.change_request_id,
       pcr_r.initiator,
       pcr_r.action_type,
       pcr_r.document_type,
       pcr_r.document_header_id po_header_id,
       pcr_r.document_num po_number,
       pcr_r.document_revision_num po_revision_num,
       pcr_r.document_line_id po_line_id,
       pcr_r.document_line_number po_line_number,
       pcr_r.old_quantity,
       pcr_r.new_quantity,
       pcr_r.old_promised_date,
       pcr_r.new_promised_date,
       pcr_r.old_supplier_part_number,
       pcr_r.new_supplier_part_number,
       pcr_r.old_price,
       pcr_r.new_price,
       pcr_r.old_currency_unit_price,
       pcr_r.new_currency_unit_price,
       pcr_r.old_need_by_date,
       pcr_r.new_need_by_date,
       pcr_r.requester_id,
       fu.user_name requester,
       furesp.user_name responser,
       pcr_r.response_date,
       pcr_r.response_reason,
       pcr_r.creation_date,
       pcr_r.request_status,
       pcr_r.request_reason,
       pcr_r.request_level,
       pcr_r.change_active_flag
  FROM po_change_requests pcr_r,
       po_headers_all pha,
       hr_operating_units hou,
       fnd_user fu,
       fnd_user furesp
 WHERE pcr_r.document_header_id = pha.po_header_id
   AND pha.org_id = hou.organization_id
   AND pcr_r.document_type = 'PO'
   AND pcr_r.requester_id = fu.user_id
   AND pcr_r.responded_by = furesp.user_id
   AND pcr_r.initiator = 'REQUESTER'
 
 

            In this post, we have given a script which can update requester/preparer or approver of standard purchase requisition.

Business Case: This is pretty much useful when a preparer of current purchase requisition must be changed due to various business reasons like preparer left organization or moving to another department or moving higher the ladder etc.

Tested instance: R12.2.4

API: PO_MASS_UPDATE_REQ_GRP.UPDATE_PERSONS

Parameters:

Script:

SET SERVEROUTPUT ON;
DECLARE
  lv_msg_data             VARCHAR2(100);
  ln_msg_count            NUMBER;
  lv_return_status        VARCHAR2(1); 
  ln_org_id               NUMBER;
  ln_changed_preparer_id  NUMBER;
  ln_current_preparer_id  NUMBER;
  ln_new_preparer_id      NUMBER DEFAULT '88';
  ln_valid_employee       NUMBER;
  lv_document_no          VARCHAR2(200) DEFAULT '100008225'; 
  le_custom_exception     EXCEPTION;
BEGIN 

  -- check whether the given person is a valid preparer
  SELECT COUNT(1)
    INTO ln_valid_employee
    FROM per_all_people_f 
   WHERE person_id = ln_new_preparer_id
     AND current_employee_flag = 'Y'
	 AND SYSDATE BETWEEN effective_from_date AND effective_to_date;
   
  IF ln_valid_employee = 0
  THEN
    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is not a valid employee');
    RAISE le_custom_exception;
  END IF;

  SELECT org_id,
         preparer_id        
    INTO ln_org_id,
         ln_current_preparer_id
    FROM po_requisition_headers_all pha 
   WHERE pha.segment1 = lv_document_no;
   
  IF ln_current_preparer_id = ln_new_preparer_id
  THEN
    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is already a preparer');
    RAISE le_custom_exception;
  END IF; 
  
  fnd_global.apps_initialize(user_id      => 178,
                             resp_id      => 5739, 
                             resp_appl_id => 201
                            );
                            
  mo_global.init('PO'); 
  fnd_client_info.set_org_context(ln_org_id);
  mo_global.set_policy_context ('S', ln_org_id);
  
  PO_MASS_UPDATE_REQ_GRP.UPDATE_PERSONS
           (
            p_update_person       => 'PREPARER',
            p_old_personid        => ln_current_preparer_id,
            p_new_personid        => ln_new_preparer_id,
            p_document_type       => 'PURCHASE',
            p_document_no_from    => lv_document_no,
            p_document_no_to      => lv_document_no,
            p_date_from           => NULL,
            p_date_to             => NULL,
            p_commit_interval     => 1,
            p_msg_data            => lv_msg_data,
            p_msg_count           => ln_msg_count,
            p_return_status       => lv_return_status
          );
          
   SELECT preparer_id 
     INTO ln_current_preparer_id
     FROM po_requisition_headers_all pha 
    WHERE pha.segment1 = lv_document_no;
  
  IF ln_new_preparer_id <> ln_current_preparer_id
  THEN 
    DBMS_OUTPUT.PUT_LINE('msg_data'|| lv_msg_data);
    DBMS_OUTPUT.PUT_LINE('msg_count'|| ln_msg_count);
    DBMS_OUTPUT.PUT_LINE('return_status'|| lv_return_status);                            
  ELSE 
    DBMS_OUTPUT.PUT_LINE('Successfully Updated');
  END IF;
EXCEPTION 
  WHEN le_custom_exception THEN 
    DBMS_OUTPUT.PUT_LINE('Custom Error. Hence, action Aborted');
  WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);
END;