Encumbrance Migration to Oracle Grants for Open Purchase Orders 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.In oracle applications, my exposure to encumbrances is related to those in Oracle Purchasing / iProcurement, Oracle Payables, Labor Distributions. This article is mostly a technical explanation of how encumbrances can be migrated from open Purchase Orders into Oracle Grants / Oracle Projects. 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 -- ,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 ,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 learnt 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.