Encumbrances can
be migrated from open Purchase Orders into Oracle Grants / Oracle
Projects.
This article is mostly a technical explanation of how encumbrances can be migrated from open Purchase Orders into Oracle Grants / Oracle Projects.
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.
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.
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 undertakenAll 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';
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 undertakenAll 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.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
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.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.
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.
No comments:
Post a Comment