Apps To Fusion

This article is mostly a technical explanation of how encumbrances can be migrated from open Purchase Orders into Oracle Grants / Oracle Projects.

In oracle applications, my exposure to encumbrances is related to those in Oracle Purchasing / iProcurement, Oracle Payables, Labor Distributions. Firstly, let me tell you that Oracle Applications Encumbrance functionality is very rich, it works well and is also very stable. But it did take Oracle some time to stabilise & deliver feature rich encumbrance Functionality.

My client was already live with Oracle Purchasing & iProcurement when this migration requirement came through. Prior to migration, the encumbrances were pushed into GL via the Oracle Purchasing module.

When implementing Oracle Grants/ Oracle Projects, some of my clients GL Codes were mapped to Grants Awards, Projects, Tasks & Expenditure type.
Before migration, the Encumbrances for such GL codes wouldn't be visible from Grants in either the Award status  (ASI) or the Project Status (PSI) screen. The challenge was not only to make those encumbrances visible from GMS [Grants Management System], but also to have those liquidated/reversed when purchase orders were matched to Oracle Payables Invoices. Not only that, upon matching invoices in Payables, award status screen must then show those earlier encumbrances as actuals.

There are now three subsections below, and you may wish to read those that interest you.
            Section 1. Challenges
            Section 2. Steps undertaken
            Section 3. Notes

Overall this task was of medium complexity, but challenges were :-
1. Ensuring that all Open Purchase Orders regardless of their Approval/Reservation status were successfully migrated to POETA.
2. To ensure that after switching the relevant Purchase Orders to POETA, those purchase Orders must be Re-Encumbered Successfully. See the note at very end to find how this was ensured.
3. To ensure that all the PO's that were approved prior to migration were Re-Approved successfully without invoking the Approval Workflow and also without having to update any Oracle table.
4. Use Oracle API's at as many possible places. No direct updates to any of the Oracle tables were to be done. Considering there aren't many public API's to help achieve these tasks, I had to do plenty digging to find the APIs that forms and workflows etc called. Those internal API's were re-used, hence avoiding any direct table updates.
5. Some of the PO_DISTRBUTION_LINES records were partially matched. Hence such Distributions had to be split into two.

Following steps were undertaken
All the below steps were accomplished by writing various procedures and functions within a PL/SQL Package
Step 1.
Update the logged in User Id that executes migration program to be that of the Employee that can approve any Purchase Order.
See Note 4 below.
Step 2.
Run Payables Accounting Process and Program Create Journal in GL
Doing so will ensure that PO Distribution table is in synch with the latest set of matched invoices.
Step 3.
Load the data into a table say xx_eligible_po_dist_4_gms_enc
Create a local procedure for doing so.
      INSERT INTO xx_eligible_po_dist_4_gms_enc
        (po_header_id
        ,po_line_id
        ,line_location_id
        ,po_distribution_id
        ,code_combination_id
        ,po_status
        ,old_quantity_ordered
        ,old_quantity_cancelled
        ,old_quantity_billed
        ,old_quantity_delivered
        ,fnd_request_id)
        SELECT pod.po_header_id
              ,pod.po_line_id
              ,pod.line_location_id
              ,pod.po_distribution_id
              ,pod.code_combination_id
              ,po_headers_sv3.get_po_status(phea.po_header_id)
              ,pod.quantity_ordered
              ,pod.quantity_cancelled
              ,pod.quantity_billed
              ,pod.quantity_delivered
              ,g_conc_request_id
        FROM po_line_locations    pll
            ,po_distributions     pod
            ,po_headers           phea
            ,po_lines             pol
            ,po_lookup_codes      plc
            ,po_line_types        plt
            ,xx_enc_eligible_projects   eep
            ,gl_code_combinations glcc
        WHERE pod.line_location_id = pll.line_location_id AND
              pol.po_line_id = pod.po_line_id AND
              pol.line_type_id = plt.line_type_id AND
              nvl(pll.closed_code
                 ,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND
              nvl(pol.closed_code
                 ,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED') AND
              pll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED') AND
              nvl(pll.closed_code
                 ,'OPEN') = plc.lookup_code AND
              plc.lookup_type = 'DOCUMENT STATE' AND
              nvl(pll.cancel_flag
                 ,'N') = 'N' AND nvl(pol.cancel_flag
                                    ,'N') = 'N' AND
              glcc.code_combination_id = pod.code_combination_id AND
              (glcc.segment3 LIKE 'P%' OR glcc.segment3 LIKE 'R%') AND
              phea.po_header_id = pol.po_header_id AND
              pod.quantity_ordered - pod.quantity_cancelled -
              pod.quantity_billed > 0 AND
              phea.authorization_status IN
              ('APPROVED', 'IN PROCESS', 'INCOMPLETE', 'REQUIRES REAPPROVAL') AND
              eep.project_code = glcc.segment3 AND status <> 'CLOSED';

Next Update the above entries in the table with POETA
    PROCEDURE load_poeta_info_for_tab IS
    l_poeta_rec r_poeta_rec_type;
  BEGIN
    FOR p_rec IN (SELECT tt.code_combination_id
                  FROM xx_eligible_po_dist_4_gms_enc tt
                  WHERE tt.fnd_request_id = g_conc_request_id
                  GROUP BY tt.code_combination_id)
    LOOP
      l_poeta_rec := get_poeta_rec_for_ccid
         (p_gl_code_combination_id => p_rec.code_combination_id);
      UPDATE xx_eligible_po_dist_4_gms_enc tab_poeta
      SET tab_poeta.project_id            = l_poeta_rec.project_id
         ,tab_poeta.expenditure_org_id    = l_poeta_rec.expenditure_org_id
         ,tab_poeta.expenditure_type      = l_poeta_rec.expenditure_type
         ,tab_poeta.task_id               = l_poeta_rec.task_id
         ,tab_poeta.award_id              = l_poeta_rec.award_id
         ,tab_poeta.expenditure_item_date = l_poeta_rec.expenditure_item_date
      WHERE tab_poeta.code_combination_id = p_rec.code_combination_id;
    END LOOP;
  END load_poeta_info_for_tab;

            
Step 4. Validate the POETA information loaded into above table by calling api's
        gms_transactions_pub.validate_transaction
                                (p_project_id            => p_rec.project_id
                                ,p_task_id               => p_rec.task_id
                                ,p_award_id              => p_rec.award_id
                                ,p_expenditure_type      => p_rec.expenditure_type
                                ,p_expenditure_item_date => p_rec.expenditure_item_date
                                ,p_calling_module        => 'POXPOEPO'
                                ,p_outcome               => v_gms_outcome);
        pa_transactions_pub.validate_transaction(
                                 p_rec.project_id
                                ,p_rec.task_id
                                ,p_rec.expenditure_item_date
                                      ,p_rec.expenditure_type
                                      ,NULL
                                      ,141 --<EmployeeWithMaxApprovalLimit>
                                      ,NULL
                                      ,'GBP' --x_denom_currency_code
                                      ,'GBP' --x_acct_currency_code
                                      ,p_rec.denom_raw_cost --denom_raw_cost
                                      ,p_rec.denom_raw_cost --x_acct_raw_cost
                                      ,NULL --x_acct_rate_type
                                      ,NULL --x_acct_rate_date
                                      ,NULL --x_acct_exchange_rate
                                      ,NULL --transfer_ei
                                      ,p_rec.expenditure_org_id --x_expenditure_org_id
                                      ,NULL --nl_resource_org_id
                                      ,NULL --transaction_source
                                      ,'POXPOEPO' --x_form_name
                                      ,p_rec.vendor_id
                                      ,1054 -- x_last_updated_by <GlobalApprover>
                                      ,NULL --x_attribute_category
                                      ,NULL --x_attribute1
                                      ,NULL --x_attribute2
                                      ,NULL --x_attribute3
                                      ,NULL --x_attribute4
                                      ,NULL --x_attribute5
                                      ,NULL --x_attribute6
                                      ,NULL --x_attribute7
                                      ,NULL --x_attribute8
                                      ,NULL --x_attribute9
                                      ,NULL --x_attribute10
                                      ,NULL --x_attribute11
                                      ,NULL --x_attribute12
                                      ,NULL --x_attribute13
                                      ,NULL --x_attribute14
                                      ,NULL --x_attribute15
                                      ,x_msg_application
                                      ,x_msg_type
                                      ,x_msg_token1
                                      ,x_msg_token2
                                      ,x_msg_token3
                                      ,x_msg_count
                                      ,x_msg_data
                                      ,x_billable_flag);

The POETA records that fail validation must be flagged and reported, so that they are'nt processed for migration.
Step 5.
Now we are ready to roll. Finally Loop through the eligible PO Lines and move them to POETA.
Please find the psuedo code below
    LOOP FOR ALL ELIGIBLE PO LINES
      debug_log('Processing p_get_pos.po_header_id=>' ||
                p_get_pos.po_header_id || ' status=>' || p_get_pos.status);
      BEGIN
        IF does_invvalid_account_exist(p_po_header_id => p_get_pos.po_header_id)
        THEN
          RAISE gl_code_inactive;
        END IF;
     
        IF does_invalid_budget_acct_exist(p_po_header_id => p_get_pos.po_header_id)
        THEN
          RAISE gl_code_inactive;
        END IF;
     
        IF check_invalid_locations_exists(p_po_header_id => p_get_pos.po_header_id)
        THEN
          RAISE invalid_location;
        END IF;
        --Now here we would only have the validated PO's.
        --The POETA that was'nt validated were rejected during the
        --validate_gms and validate_pa stages
        b_originally_reserved_flag := is_orig_po_reserved(p_get_pos.status);
        b_originally_approved_flag := is_orig_po_approved(p_get_pos.status);
     
        IF b_originally_reserved_flag AND
           non_reserved_line_exists(p_po_header_id => p_get_pos.po_header_id)
        THEN
          RAISE unreserved_line_in_reserved_po;
        END IF;
       
        IF b_originally_reserved_flag AND
        does_del_more_than_order_exist(p_po_header_id => p_get_pos.po_header_id)
        THEN
          RAISE delivered_more_than_ordered;       
        END IF ;
        debug_log('Remove the association to Requisition.');
        --the reason we do below, is to esure that REQ is not debited in gl_bc
        --when Unreserve is done.
        --just updating attribute columns, hence no violation of Oracle support here
        UPDATE po_distributions_all
        SET attribute13              = req_header_reference_num || ':' ||
                                       req_line_reference_num || ':' ||
                                       req_distribution_id
           ,req_header_reference_num = NULL
           ,req_line_reference_num   = NULL
           ,req_distribution_id      = NULL
           ,attribute12              = to_char(gl_encumbered_date
                                              ,'DD-MON-YYYY')
           ,gl_encumbered_date       = trunc(SYSDATE)
        WHERE po_header_id = p_get_pos.po_header_id;
     
        IF b_originally_reserved_flag
        THEN
          IF NOT unreserve_po(p_po_header_id => p_get_pos.po_header_id
                             ,p_agent_id     => p_get_pos.agent_id)
          THEN
            RAISE can_not_unreserve;
          END IF;
          debug_log('Successfully unreserved p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        END IF;
     
        /* Here comes the main Logic for splitting the distribution lines*/
        split_po_distribution_lines(p_po_header_id => p_get_pos.po_header_id);
     
        IF b_originally_reserved_flag
        THEN
          IF NOT reserve_po(p_po_header_id => p_get_pos.po_header_id
                           ,p_agent_id     => p_get_pos.agent_id)
          THEN
            RAISE can_not_reserve;
          END IF;
          debug_log('Successfully re-reserved p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        END IF;
        IF b_originally_approved_flag
        THEN
          IF NOT approve_po(p_po_header_id => p_get_pos.po_header_id
                           ,p_agent_id     => p_get_pos.agent_id)
          THEN
            RAISE can_not_approve;
          END IF;
          debug_log('Successfully re-approved p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        END IF;
      EXCEPTION
        WHEN can_not_unreserve THEN
          debug_log('Exception in unreserving p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        WHEN can_not_reserve THEN
          debug_log('Exception in reserving p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        WHEN can_not_approve THEN
          debug_log('Exception in approving p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        WHEN gl_code_inactive THEN
          debug_log('GL Code is Inactive p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
        WHEN unreserved_line_in_reserved_po THEN
          debug_log
         ('Unreserved Line Exists in Reserved PO. Skipping p_get_pos.po_header_id=>' ||
           p_get_pos.po_header_id);
        WHEN invalid_location THEN
          debug_log
          ('Invalid Location for Ship-to or Bill-to. Skipping p_get_pos.po_header_id=>'
           || p_get_pos.po_header_id);
        WHEN delivered_more_than_ordered THEN
          debug_log
          ('Delivered Qty more than ordered qty. Skipping  p_get_pos.po_header_id=>' ||
                    p_get_pos.po_header_id);
      END;
      UPDATE xx_eligible_po_dist_4_gms_enc
      SET processed_flag                 = 'Y'
         ,po_status_after_process_is_run =
                  po_headers_sv3.get_po_status(p_get_pos.po_header_id)
         ,status_updated_by_request_id   = fnd_global.conc_request_id
      WHERE po_header_id = p_get_pos.po_header_id;
    END LOOP;


    Usefull API code packages for various activities in this context are
    FUNCTION reserve_po(p_po_header_id IN INTEGER
                     ,p_agent_id     IN INTEGER) RETURN BOOLEAN IS
    l_dm_call_rec po_doc_manager_pub.dm_call_rec_type;
    x_progress    VARCHAR2(200);
    l_warning_msg VARCHAR2(200);
    x_mesg        VARCHAR2(200);
    l_attr_exist  NUMBER := 0;
    v_session_id  INTEGER := userenv('sessionid');
  BEGIN
    l_dm_call_rec.action              := 'RESERVE_DOCUMENT';
    l_dm_call_rec.document_type       := 'PO';
    l_dm_call_rec.document_subtype    := 'STANDARD';
    l_dm_call_rec.document_id         := p_po_header_id;
    l_dm_call_rec.line_id             := NULL;
    l_dm_call_rec.shipment_id         := NULL;
    l_dm_call_rec.distribution_id     := NULL;
    l_dm_call_rec.employee_id         := p_agent_id;
    l_dm_call_rec.new_document_status := NULL;
    l_dm_call_rec.offline_code        := NULL;
    l_dm_call_rec.note                := NULL;
    l_dm_call_rec.approval_path_id    := NULL;
    l_dm_call_rec.forward_to_id       := p_agent_id;
    l_dm_call_rec.action_date         := NULL;
    l_dm_call_rec.override_funds      := 'N';
    -- Below are the output parameters
    l_dm_call_rec.info_request     := NULL;
    l_dm_call_rec.document_status  := NULL;
    l_dm_call_rec.online_report_id := NULL;
    l_dm_call_rec.return_code      := NULL;
    l_dm_call_rec.error_msg        := NULL;
    /* This is the variable that contains the return value from the
    ** call to the DOC MANAGER:
    ** SUCCESS =0,  TIMEOUT=1,  NO MANAGER=2,  OTHER=3
    */
    l_dm_call_rec.return_value := NULL;
 
    /* Call the API that calls the Document manager */
    debug_log('Before Calling doc_manager to RESERVE');
    COMMIT;
    po_doc_manager_pub.call_doc_manager(l_dm_call_rec);
    COMMIT;
    dbms_lock.sleep(.2);
    COMMIT;
    debug_log('After Calling doc_manager to RESERVE');
    debug_log('RESERVE p_po_header_id=>' || p_po_header_id ||
              ' L_DM_CALL_REC.Return_Value=>' ||
              l_dm_call_rec.return_value);
    IF l_dm_call_rec.return_value = 0
    THEN
      RETURN TRUE;
    END IF;
    --capture the fact that Doc Mgr could re-reserve the PO
    log_error(p_po_header_id           => p_po_header_id
             ,p_po_distribution_id     => NULL
             ,p_gl_code_combination_id => NULL
             ,p_field_name             => 'CAN NOT RE-RESERVE'
             ,p_error_text             => l_dm_call_rec.error_msg);
    RETURN FALSE;
    --if doc mgr returns status 0, then return true
    --in all other cases, return FALSE
 
  END reserve_po;

    FUNCTION approve_po(p_po_header_id IN INTEGER
                     ,p_agent_id     IN INTEGER) RETURN BOOLEAN IS
    l_dm_call_rec po_doc_manager_pub.dm_call_rec_type;
    x_progress    VARCHAR2(200);
    l_warning_msg VARCHAR2(200);
    x_mesg        VARCHAR2(200);
    l_attr_exist  NUMBER := 0;
    v_session_id  INTEGER := userenv('sessionid');
  BEGIN
    l_dm_call_rec.action              := 'APPROVE_DOCUMENT';
    l_dm_call_rec.action_date         := SYSDATE;
    l_dm_call_rec.document_type       := 'PO';
    l_dm_call_rec.document_subtype    := 'STANDARD';
    l_dm_call_rec.document_id         := p_po_header_id;
    l_dm_call_rec.line_id             := NULL;
    l_dm_call_rec.shipment_id         := NULL;
    l_dm_call_rec.distribution_id     := NULL;
    l_dm_call_rec.employee_id         := g_buyer_employee_id;
    l_dm_call_rec.new_document_status := 'APPROVED';
    l_dm_call_rec.offline_code        := NULL;
    l_dm_call_rec.note                := NULL;
    l_dm_call_rec.approval_path_id    := NULL;
    l_dm_call_rec.forward_to_id       := g_buyer_employee_id;
    /*  L_DM_CALL_REC.Action_date    := NULL;*/
    l_dm_call_rec.override_funds := 'N';
    -- Below are the output parameters
    l_dm_call_rec.info_request     := NULL;
    l_dm_call_rec.document_status  := NULL;
    l_dm_call_rec.online_report_id := NULL;
    l_dm_call_rec.return_code      := NULL;
    l_dm_call_rec.error_msg        := NULL;
    /* This is the variable that contains the return value from the
    ** call to the DOC MANAGER:
    ** SUCCESS =0,  TIMEOUT=1,  NO MANAGER=2,  OTHER=3
    */
    l_dm_call_rec.return_value := NULL;
    /* Call the API that calls the Document manager */
    COMMIT;
    po_doc_manager_pub.call_doc_manager(l_dm_call_rec);
    COMMIT;
    dbms_lock.sleep(.2);
    COMMIT;
    debug_log('APPROVE p_po_header_id=>' || p_po_header_id ||
              ' L_DM_CALL_REC.Return_Value=>' ||
              l_dm_call_rec.return_value);
    --if doc mgr returns status 0, then return true
    --in all other cases, return FALSE
    IF l_dm_call_rec.return_value = 0
    THEN
      RETURN TRUE;
    END IF;
    log_error(p_po_header_id           => p_po_header_id
             ,p_po_distribution_id     => NULL
             ,p_gl_code_combination_id => NULL
             ,p_field_name             => 'CAN NOT APPROVE'
             ,p_error_text             => l_dm_call_rec.error_msg);
    RETURN FALSE;
  END approve_po;

    FUNCTION unreserve_po(p_po_header_id IN INTEGER
                       ,p_agent_id     IN INTEGER) RETURN BOOLEAN IS
    l_dm_call_rec po_doc_manager_pub.dm_call_rec_type;
    x_progress    VARCHAR2(200);
    l_warning_msg VARCHAR2(200);
    x_mesg        VARCHAR2(200);
    l_attr_exist  NUMBER := 0;
    v_session_id  INTEGER := userenv('sessionid');
  BEGIN
    l_dm_call_rec.action              := 'UNRESERVE_DOCUMENT';
    l_dm_call_rec.action_date         := SYSDATE;
    l_dm_call_rec.document_type       := 'PO';
    l_dm_call_rec.document_subtype    := 'STANDARD';
    l_dm_call_rec.document_id         := p_po_header_id;
    l_dm_call_rec.line_id             := NULL;
    l_dm_call_rec.shipment_id         := NULL;
    l_dm_call_rec.distribution_id     := NULL;
    l_dm_call_rec.employee_id         := p_agent_id;
    l_dm_call_rec.new_document_status := NULL;
    l_dm_call_rec.offline_code        := NULL;
    l_dm_call_rec.note                := NULL;
    l_dm_call_rec.approval_path_id    := NULL;
    l_dm_call_rec.forward_to_id       := p_agent_id;
    /*  L_DM_CALL_REC.Action_date    := NULL;*/
    l_dm_call_rec.override_funds := 'N';
    -- Below are the output parameters
    l_dm_call_rec.info_request     := NULL;
    l_dm_call_rec.document_status  := NULL;
    l_dm_call_rec.online_report_id := NULL;
    l_dm_call_rec.return_code      := NULL;
    l_dm_call_rec.error_msg        := NULL;
    /* This is the variable that contains the return value from the
    ** call to the DOC MANAGER:
    ** SUCCESS =0,  TIMEOUT=1,  NO MANAGER=2,  OTHER=3
    */
    l_dm_call_rec.return_value := NULL;
 
    /* Call the API that calls the Document manager */
    COMMIT;
    po_doc_manager_pub.call_doc_manager(l_dm_call_rec);
    COMMIT;
    dbms_lock.sleep(.2);
    COMMIT;
 
    debug_log('UNRESERVE p_po_header_id=>' || p_po_header_id ||
              ' L_DM_CALL_REC.Return_Value=>' ||
              l_dm_call_rec.return_value);
    --if doc mgr returns status 0, then return true
    --in all other cases, return FALSE
    IF l_dm_call_rec.return_value = 0
    THEN
      RETURN TRUE;
    END IF;
    --capture the fact that Doc Mgr could not un-reserve the PO
    log_error(p_po_header_id           => p_po_header_id
             ,p_po_distribution_id     => NULL
             ,p_gl_code_combination_id => NULL
             ,p_field_name             => 'CAN NOT UNRESERVE'
             ,p_error_text             => l_dm_call_rec.error_msg);
    RETURN FALSE;
  END unreserve_po;

Step 6.
Run exceptions. I had written half a dozen SQLs for reconcilliation etc.

Step 7.
After reconcilliation, delete the Journals from GL_INTERFACE.
But before deletion please complete your reconcilliation.
You may decide not to delete those journals, but Oracle support gave  a nod to this.

Important notes for migration encumbrances to poeta

Note 1.
Internal to Oracle Encumbrances, two key tables are GMS_BC_PACKETS and GL_BC_PACKETS. Do not modify the data within these tables, treat these two tables as sacred tables. These tables are where the encumbrances are queued, which eventually hits the GL in GL_BALANCES. However, feel free to use gl_bc_packets to debug the progression of encumbrances. I learn quite a lot about encumbrances by merely studying the type of record that gets inserted into packet tables at various stages of PO/Req Entry, Approval, Reservation, Cancellation & Matching stages. The reference1 column in gl_bc_packets will tell you whether the Transaction is PO or REQ. There reference2 columns stores the respective header id, and reference3 stored the po_dist_id. Rest of the reference columns may be used, but I never bothered much as Ref1,2,3 sufficed for my requirements. Also, I must mention that GL_BC_PACKETS is for queuing encumbrances into GL. Once those encumbrances have been transferred and Posted, the respective records in GL_BC_PACKETS get deleted by running Oracle's concurrent process.

Note 2.
For some open PO's, you will be required for UnReserve, UnApprove, Modify PO with POETA and then Re-Reserve and Re-Approve the PO. For Re-Approval, make sure that your data migration Concurrent Process runs via an FND_USER that is attached to an Employee that has the highest approval limit across all cost centres for your client.

Note 3.
Given that during migration to POETA, we re-reserve the Purchase Orders, it is necessary that all the Awards against which encumbrances are being migrated have Advisory Funds Check Level. You may as well set the Advisory level to None for those Awards. This must be taken care of when migrating the Awards into Oracle Grants Accounting.

Note 4.
My client did not use internal Purchase Orders or Requisitions, hence I did not deal with those transactions. However the underlying principles must remain the same.

Note 5.
This migration task worked upon the Open Purchase Orders that had following possible statuses. I defined a global variable for each such status
  g_status_approved             VARCHAR2(50) := 'Approved';
  g_status_approved_reserved    VARCHAR2(50) := 'Approved, Reserved';
  g_status_in_process           VARCHAR2(50) := 'In Process';
  g_status_in_process_reserved  VARCHAR2(50) := 'In Process, Reserved';
  g_status_incomplete           VARCHAR2(50) := 'Incomplete';
  g_status_incomplete_reserved  VARCHAR2(50) := 'Incomplete, Reserved';
  g_status_requires_reapproval  VARCHAR2(50) := 'Requires Reapproval';
  g_status_requires_re_reserved VARCHAR2(50) := 'Requires Reapproval, Reserved';

Note 6. The Award_id that you see in po_distributions_all is not the gms_awards.award_id. For the PO_DISTRIBUTION Award_id you need to create adsl. Used Oracle API for doing so.

Note 7.
We used document manager API for Re-Approval of Purchase Order, Reservation of Purchase Order and also for Unreserving the Purchase Order.
Given that the document manager api sends a pipe signal that is read by doc mgr it happens in a different session. Not only  that, being run by a conc mgr, the doc mgr has limited slots hence give a sleep command before and after each document manager call. Reason we need to do this is to ensure that your loop execution is not faster than the load which Document Manager can undertake.

Note 8.
My client was based in UK, and they had only one single SOB. Hence i felt no risk in hardcoding the Currency Code to GBP.