PO Re-approval API Script: Oracle EBS R12

Oracle has provided a standard API to re approve Purchase Order (PO). Below mentioned is the standard script to re approve PO.

SET serveroutput ON
DECLARE
  l_conterms_exist_flag           po.po_headers_all.conterms_exist_flag%TYPE;
  l_revision_num                      NUMBER;
  l_request_id                           NUMBER := 0;
  l_doc_type                             VARCHAR2(30);
  l_doc_subtype                      VARCHAR2(30);
  l_comm_doc_type                VARCHAR2(30);
  l_document_id                      NUMBER;
  l_agent_id                              NUMBER;
  l_printflag                              VARCHAR2(1) := 'N';
  l_faxflag                                 VARCHAR2(1) := 'N';
  l_faxnum                                VARCHAR2(30);
  l_emailflag                             VARCHAR2(1) := 'N';
  l_emailaddress                     apps.po_vendor_sites.email_address%TYPE;
  l_default_method                apps.po_vendor_sites.supplier_notif_method%TYPE;
  l_user_id                               apps.po_lines.last_updated_by%TYPE    := -1;
  l_login_id                             apps.po_lines.last_update_login%TYPE := -1;
  x_return_status                   VARCHAR2(1);
  x_msg_data                          VARCHAR2(2000);
  l_doc_num                           VARCHAR2(30);
  l_approval_path_id            NUMBER;
  l_progress                           NUMBER;
  v_po_header_id                 NUMBER;
  v_error_flag                        NUMBER:=0;
  v_main_error                      VARCHAR2(1000);

BEGIN
  BEGIN
    SELECT po_header_id INTO v_po_header_id
    FROM apps.po_headers_all
    WHERE segment1='1234' AND org_id=204;
   exception WHEN no_data_found THEN
    v_error_flag:=1;
    v_main_error:=v_main_error||'PO does not exist in EBS';
  END;

IF v_error_flag <> 1 THEN
      l_document_id :=v_po_header_id;
     --------INITIALIZING APPS ENVIRONMENT-------------------------------                
           
      apps.mo_global.init ('PO');
      apps.mo_global.set_policy_context ('S',204);
      apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
      apps.po_moac_utils_pvt.set_org_context(204);

-- Set the FND profile option values.
    apps.fnd_profile.put('AFLOG_ENABLED','Y');
    apps.fnd_profile.put('AFLOG_MODULE','%');
    apps.fnd_profile.put('AFLOG_LEVEL',to_char(1));
    apps.fnd_profile.put('AFLOG_FILENAME','');

    -- Refresh the FND cache.
    apps.fnd_log_repository.init();

--Get User ID and Login ID
    l_user_id := apps.fnd_global.user_id;
    l_login_id := apps.fnd_global.login_id;

  BEGIN

    SELECT  nvl(conterms_exist_flag,'N'),
            revision_num,
            decode(type_lookup_code,'BLANKET','PA','CONTRACT','PA','PO'),
            type_lookup_code,
            agent_id
    INTO    l_conterms_exist_flag,
            l_revision_num,
            l_doc_type,
            l_doc_subtype,
            l_agent_id
    FROM    apps.po_headers_all
    WHERE   po_header_id = l_document_id;

      l_comm_doc_type :=  l_doc_subtype;

    exception
      WHEN no_data_found THEN 
        SELECT  nvl(poh.conterms_exist_flag,'N'),            
                por.revision_num,
                'RELEASE',
                poh.type_lookup_code,
                por.agent_id
        INTO    l_conterms_exist_flag,            
                l_revision_num,
                l_doc_type,
                l_doc_subtype,
                l_agent_id
        FROM    apps.po_releases_all por,
                apps.po_headers_all poh
        WHERE   po_release_id = l_document_id
                AND poh.po_header_id=por.po_header_id;

       l_comm_doc_type :=l_doc_type;
      WHEN others THEN
       dbms_output.put_line('IN EXCEPTION sqlcode: '||sqlcode||'sqlerrm: '||sqlerrm);
    END;
    
    SELECT podt.default_approval_path_id
    INTO   l_approval_path_id
    FROM   apps.po_document_types podt
    WHERE  podt.document_type_code   = l_doc_type
           AND podt.document_subtype = l_doc_subtype;

    SELECT MAX(log_sequence) INTO l_progress FROM apps.fnd_log_messages;

    apps.po_document_action_pvt.do_approve(
        p_document_id => l_document_id,
        p_document_type => l_doc_type,
        p_document_subtype => l_doc_subtype,
        p_note => NULL,
        p_approval_path_id => l_approval_path_id,
        x_return_status => x_return_status,
        x_exception_msg => x_msg_data );

     SELECT MAX(log_sequence) INTO l_progress FROM apps.fnd_log_messages;
IF x_return_status ='S' THEN
 
        -- Communicate to the Supplier
      apps.po_vendor_sites_sv.get_transmission_defaults (  
        p_document_id      => l_document_id,  
        p_document_type    => l_doc_type,  
        p_document_subtype => l_doc_subtype,  
        p_preparer_id      => l_agent_id,  
        x_default_method   => l_default_method,  
        x_email_address    => l_emailaddress,  
        x_fax_number       => l_faxnum,  
        x_document_num     => l_doc_num,       
        p_retrieve_only_flag => 'Y'  
        );
 
      IF (l_default_method  = 'EMAIL') AND (l_emailaddress IS NOT NULL) THEN  
     l_faxnum := NULL;  
   elsif (l_default_method  = 'FAX') AND (l_faxnum IS NOT NULL) THEN  
        l_emailaddress := NULL;  
      elsif (l_default_method  = 'PRINT') THEN  
        l_emailaddress := NULL;  
        l_faxnum := NULL;  
      ELSE
        l_default_method := 'PRINT'; 
        l_emailaddress := NULL;  
        l_faxnum := NULL;  
      END IF; 
    
      apps.po_communication_pvt.communicate(
        p_authorization_status=>apps.po_document_action_pvt.g_doc_status_approved,
        p_with_terms=>l_conterms_exist_flag,
        p_language_code=>apps.fnd_global.current_language,
        p_mode =>l_default_method,
        p_document_id =>l_document_id,
        p_revision_number =>l_revision_num,
        p_document_type =>l_comm_doc_type,
        p_fax_number =>l_faxnum,
        p_email_address =>l_emailaddress,
        p_request_id =>l_request_id);

SELECT MAX(log_sequence) INTO l_progress FROM apps.fnd_log_messages;
    apps.fnd_file.put_line(apps.fnd_file.LOG,' LOG SEQUENCE '||l_progress);
    END IF;
 COMMIT;
ELSE
  dbms_output.put_line('Error'||v_main_error);
END IF;
exception
WHEN others THEN
SELECT MAX(log_sequence) INTO l_progress FROM apps.fnd_log_messages;
   apps.fnd_file.put_line(apps.fnd_file.LOG,' LOG SEQUENCE '||l_progress);
END;