Sunday, December 25, 2022

API to Retire an Asset in Oracle Apps R12 (FA_RETIREMENT_PUB.DO_RETIREMENT)

 







In this post, we have given the sample script to retire an API in R12.


API: FA_RETIREMENT_PUB.DO_RETIREMENT 


Test Instance: R12.1.3 


Script: 


SET SERVEROUTPUT ON; DECLARE le_api_error EXCEPTION; -- Asset to be retired lv_asset_number VARCHAR2(100) DEFAULT '43296'; lv_book_type_code VARCHAR2(15) DEFAULT 'CORP'; ln_asset_id NUMBER; ln_user_id NUMBER := 12345; ln_cost_retired NUMBER; ln_proceeds_of_sale NUMBER := 0; ln_cost_of_removal NUMBER := 0; ln_request_id NUMBER; lr_trans_rec fa_api_types.trans_rec_type; lr_dist_trans_rec fa_api_types.trans_rec_type; lr_asset_hdr_rec fa_api_types.asset_hdr_rec_type; lr_asset_retire_rec fa_api_types.asset_retire_rec_type; lt_asset_dist_tbl fa_api_types.asset_dist_tbl_type; lt_subcomp_tbl fa_api_types.subcomp_tbl_type; lt_inv_tbl fa_api_types.inv_tbl_type; ln_api_version NUMBER := 1; lv_init_msg_list VARCHAR2(1) := fnd_api.g_false; lv_commit VARCHAR2(1) := fnd_api.g_true; lv_validation_level NUMBER := fnd_api.g_valid_level_full; lv_calling_func VARCHAR2(80) := 'Shareoracleapps Wrapper'; lv_return_sts VARCHAR2(1) := fnd_api.g_false; ln_msg_cnt NUMBER := 0; lv_message VARCHAR2(512); ln_count NUMBER; i NUMBER := 0; lv_dummy VARCHAR2(512); ln_message_count NUMBER; CURSOR cur_fa_addition IS SELECT a.asset_number, a.asset_id, b.cost FROM fa_additions_b a, fa_books_v b WHERE a.asset_id = b.asset_id and a.asset_number = lv_asset_number AND a.creation_date > sysdate - 300 AND b.book_type_code = lv_book_type_code AND ROWNUM < 5 AND NOT EXISTS ( SELECT 1 FROM fa_retirements fr WHERE fr.asset_id = a.asset_id ); BEGIN fnd_global.apps_initialize(user_id => 12345, resp_id => 50561, resp_appl_id => 140 ); FOR i IN cur_fa_addition LOOP BEGIN fa_srvr_msg.init_server_message; fa_debug_pkg.set_debug_flag(debug_flag => 'YES'); dbms_output.put_line('Asset_id :' || i.asset_id); ln_asset_id := i.asset_id; ln_cost_retired := i.cost; -- Get Standard Who Info ln_request_id := fnd_global.conc_request_id; fnd_profile.get('LOGIN_ID', lr_trans_rec.who_info.last_update_login); fnd_profile.get('USER_ID', lr_trans_rec.who_info.last_updated_by); IF ( lr_trans_rec.who_info.last_updated_by IS NULL ) THEN lr_trans_rec.who_info.last_updated_by := -1; END IF; IF ( lr_trans_rec.who_info.last_update_login IS NULL ) THEN lr_trans_rec.who_info.last_update_login := -1; END IF; lr_trans_rec.who_info.last_update_date := SYSDATE; lr_trans_rec.who_info.creation_date := SYSDATE; lr_trans_rec.who_info.created_by := lr_trans_rec.who_info.last_updated_by; lr_asset_hdr_rec.asset_id := ln_asset_id; lr_asset_hdr_rec.book_type_code := lv_book_type_code; lr_trans_rec.transaction_type_code := NULL; -- This Will Be Determined Inside Api lr_trans_rec.transaction_date_entered := NULL; lr_asset_hdr_rec.period_of_addition := NULL; lr_asset_retire_rec.retirement_prorate_convention := NULL; lr_asset_retire_rec.date_retired := NULL; -- Will Be Current Period By Default lr_asset_retire_rec.units_retired := NULL; lr_asset_retire_rec.cost_retired := ln_cost_retired; lr_asset_retire_rec.proceeds_of_sale := ln_proceeds_of_sale; lr_asset_retire_rec.cost_of_removal := ln_cost_of_removal; lr_asset_retire_rec.retirement_type_code := 'SALE'; lr_asset_retire_rec.trade_in_asset_id := NULL; lr_asset_retire_rec.calculate_gain_loss := fnd_api.g_false; fnd_profile.put('USER_ID', ln_user_id); lt_asset_dist_tbl.DELETE; dbms_output.put_line('Call API'); fa_retirement_pub.do_retirement ( p_api_version => ln_api_version, p_init_msg_list => lv_init_msg_list, p_commit => lv_commit, p_validation_level => lv_validation_level, p_calling_fn => lv_calling_func, x_return_status => lv_return_sts, x_msg_count => ln_msg_cnt, x_msg_data => lv_message, px_trans_rec => lr_trans_rec, px_dist_trans_rec => lr_dist_trans_rec, px_asset_hdr_rec => lr_asset_hdr_rec, px_asset_retire_rec => lr_asset_retire_rec, p_asset_dist_tbl => lt_asset_dist_tbl, p_subcomp_tbl => lt_subcomp_tbl, p_inv_tbl => lt_inv_tbl ); IF lv_return_sts = fnd_api.g_false THEN RAISE le_api_error; ELSE dbms_output.put_line('lv_return_sts :' || lv_return_sts); END IF; dbms_output.put_line('Asset Retirement Done: id: ' || lr_asset_retire_rec.retirement_id ); IF ( fa_debug_pkg.print_debug ) THEN fa_debug_pkg.write_debug_log; END IF; fa_srvr_msg.add_message(calling_fn => lv_calling_func, name => 'FA_SHARED_END_SUCCESS', token1 => 'PROGRAM', value1 => 'RETIREMENT_API' ); ln_message_count := fnd_msg_pub.count_msg; IF ( ln_message_count > 0 ) THEN lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false); dbms_output.put_line('dump: ' || lv_dummy); FOR i IN 1..( ln_message_count - 1 ) LOOP lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false); dbms_output.put_line('dump: ' || lv_dummy); END LOOP; ELSE dbms_output.put_line('dump: NO MESSAGE !'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error :' || sqlerrm); END; END LOOP; EXCEPTION WHEN le_api_error THEN ROLLBACK WORK; fa_srvr_msg.add_message(calling_fn => lv_calling_func, name => 'FA_SHARED_PROGRAM_FAILED', token1 => 'PROGRAM', value1 => lv_calling_func); ln_message_count := fnd_msg_pub.count_msg; IF ( ln_message_count > 0 ) THEN lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false); dbms_output.put_line('dump: ' || lv_dummy); FOR i IN 1..( ln_message_count - 1 ) LOOP lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false); dbms_output.put_line('dump: ' || lv_dummy); END LOOP; ELSE dbms_output.put_line('dump: NO MESSAGE !'); END IF; COMMIT; END; /

 

Script to submit “Calculate Gain or Loss”: 

DECLARE lv_book_type_code VARCHAR2 (15) := 'CORP'; ln_request_id NUMBER; BEGIN fnd_global.apps_initialize (user_id => 12345, resp_id => 50561, resp_appl_id => 140 ); ln_request_id := fnd_request.submit_request ('OFA', 'FARET', null, null, false, lv_book_type_code ); COMMIT; DBMS_OUTPUT.PUT_LINE (ln_request_id); End;


Sample Execution :





Sunday, December 25, 2022 by Team search · 1

Thursday, November 17, 2022

Oracle PLQSL Script to convert Binary data (BLOB) to BASE64 encoded data (UTL_ENCODE.BASE64)

 







In this post, we have given a small script to convert BINARY data to BASE64 encoded data.  


Before seeing the script, lets have a quick background,  


Background: We have some binary data that we want to send across a network. Let's assume, we send them as bits and bytes over the network in a raw format.  


Possibility 1: Few combinations of Binary data may be considered as control characters by the intermediate hardware components. (modem). This will result Unexpected behaviour.  


Possibility 2: Binary data could be corrupted because the underlying protocol might think that you've entered a special character combination (like how FTP translates line endings). 


So, to avoid all these, people encode the binary data into characters. Base64 is one of these types of encodings. 

 

Script: 

CREATE OR REPLACE FUNCTION convert_blob_to_base64(pic_blob IN BLOB)
RETURN CLOB
IS
  lc_clob CLOB;
  ln_chunk_size PLS_INTEGER := 24000;
BEGIN
  FOR i IN 0..TRUNC((DBMS_LOB.GETLENGTH(pic_blob) - 1 ) / ln_chunk_size)
  LOOP
    lc_clob := lc_clob 
              || UTL_RAW.CAST_TO_VARCHAR2
	 	       ( UTL_ENCODE.BASE64_ENCODE
			        ( DBMS_LOB.SUBSTR( pic_blob,
                                       ln_chunk_size,
                                       i * ln_chunk_size + 1
                                     )
                    )
               );
  END LOOP;
  RETURN lc_clob;
END convert_blob_to_base64; 


Thursday, November 17, 2022 by Team search · 0

Saturday, November 12, 2022

API to terminate Employee in Oracle Apps R12 (HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP, HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP, HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP )

 









In this post we have given a script to end date an employee. Below three API's are used. 

API:

HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP
HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP
HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP 

TEST Instance: R12.1.3

Script:

SET SERVEROUTPUT ON; DECLARE l_obj NUMBER; l_period_of_service_id NUMBER; l_dod_warning BOOLEAN; l_supervisor_warning BOOLEAN; l_event_warning BOOLEAN; l_interview_warning BOOLEAN; l_review_warning BOOLEAN; l_recruiter_warning BOOLEAN; l_pay_proposal_warning BOOLEAN; l_entries_changed_warning VARCHAR2(30); l_asg_future_changes_warning BOOLEAN; l_org_now_no_manager_warning BOOLEAN; l_termination_date DATE := SYSDATE-5; l_last_std_process_date DATE := SYSDATE-5; l_final_process_date DATE := SYSDATE-5; ln_person_id NUMBER DEFAULT 39010; -- To be end dated BEGIN SELECT period_of_service_id ,object_version_number INTO l_period_of_service_id ,l_obj FROM per_periods_of_service WHERE person_id = ln_person_id AND actual_termination_date IS NULL; /*   -- HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP populates the Leaving_Reason,   -- Actual_Termination_Date and Last_Standard_Process_Date   */ hr_ex_employee_api.actual_termination_emp (p_validate => FALSE ,p_effective_date => SYSDATE ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_obj ,p_actual_termination_date => l_termination_date ,p_last_standard_process_date => l_last_std_process_date -- OUT Variables ,p_supervisor_warning => l_supervisor_warning ,p_event_warning => l_event_warning ,p_interview_warning => l_interview_warning ,p_review_warning => l_review_warning ,p_recruiter_warning => l_recruiter_warning ,p_asg_future_changes_warning => l_asg_future_changes_warning ,p_entries_changed_warning => l_entries_changed_warning ,p_pay_proposal_warning => l_pay_proposal_warning ,p_dod_warning => l_dod_warning ); /*   -- HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP populates Accepted_Termination_Date,   -- Notified_Termination_Date and Projected_Termination_Date   */ hr_ex_employee_api.update_term_details_emp (p_validate => FALSE ,p_effective_date => SYSDATE ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_obj ,p_accepted_termination_date => SYSDATE-5 ,p_notified_termination_date => SYSDATE-5 ,p_projected_termination_date => SYSDATE-5 ); COMMIT; DBMS_OUTPUT.PUT_LINE('Employee is terminated…'); /*   -- Update Final Process Date for Terminated Employee --   -- To Be executed only after the employee is terminated   -- and has an become EX-EMP   */ SELECT period_of_service_id, object_version_number, last_standard_process_date INTO l_period_of_service_id, l_obj, l_final_process_date FROM per_periods_of_service ppos WHERE period_of_service_id = ( SELECT MAX(period_of_service_id) FROM per_periods_of_service WHERE person_id = ppos.person_id AND actual_termination_date IS NOT NULL) AND person_id = ln_person_id AND final_process_date IS NULL; hr_ex_employee_api.final_process_emp (p_validate => FALSE ,p_period_of_service_id => l_period_of_service_id ,p_object_version_number => l_obj ,p_final_process_date => l_final_process_date -- OUT Variables ,p_org_now_no_manager_warning => l_org_now_no_manager_warning ,p_asg_future_changes_warning => l_asg_future_changes_warning ,p_entries_changed_warning => l_entries_changed_warning ); COMMIT; DBMS_OUTPUT.PUT_LINE('Final Process Date is updated for Terminated Employee…'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error while terminating Employee : '||SQLERRM); END; /

Testing:





Saturday, November 12, 2022 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.