tag:blogger.com,1999:blog-59542683118555276782026-02-13T11:50:46.672-08:00 Oracle Apps Knowledge SharingMeant for sharing our knowledge related to Oracle E-Business SuiteUnknownnoreply@blogger.comBlogger521110tag:blogger.com,1999:blog-5954268311855527678.post-59702043861702012072022-12-25T06:35:00.002-08:002022-12-25T06:35:45.881-08:00API 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;
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5954268311855527678.post-75682797260816110052022-11-17T02:54:00.004-08:002022-11-17T02:54:41.121-08:00Oracle 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 consideredUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-46751408235420194382022-11-12T00:56:00.001-08:002022-11-12T00:56:09.851-08:00API 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_EMPHR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMPHR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP TEST Instance: R12.1.3Script:SET SERVEROUTPUT ON;
DECLARE
l_obj NUMBER;
l_period_of_service_id NUMBER;
l_dod_warning Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-45281902235968388952022-10-20T10:15:00.000-07:002022-10-20T10:15:20.521-07:00Query to get AP Payments , Bank Statements, Internal Bank account and Vendor Details in Oracle apps R12 In this post, we have given the query which gives you the joins between AP payment tables, Bank statement tables, Internal bank Account details and vendor details. Tested instance: R12.2.4 Tables Involved: CE_BANK_ACCOUNTS CE_BANK_ACCT_USES_ALL CE_BANK_BRANCHES_V Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-26614380759308495322022-10-20T09:40:00.000-07:002022-10-20T09:40:50.727-07:00Query to get basic AP Invoice and Payment information from Oracle Apps R12 In this post, we have given the query which gives you the joins between AP invoice tables and relevant Payment tables. Tested instance: R12.2.4 Tables Involved: AP_INVOICE_PAYMENTS_ALL AP_CHECKS_ALL AP_LOOKUP_CODES IBY_PAYMENT_METHODS_VL AP_INVOICES_ALL AP_SUPPLIERS Query:
WITH ap_invoice_paymentsUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-43853597692268105132022-10-15T10:09:00.001-07:002022-10-15T10:09:42.009-07:00Query to check whether file exists on server using Oracle PLSQL (DBMS_LOB.FILEEXISTS, UTL_FILE.FGETATTR)In this post, we have given few approaches to check whether file exists in the directory or not using PLSQL. Background: 1. Create a Directory in the server and place the test file 2. Create a DBA_DIRECTORY for the same We have explained two options here. Option 1: Using SQL query and DBMS_LOB.FILEEXISTS and BFILENAMESELECT (CASE DBMS_LOB.FILEEXISTS(Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5954268311855527678.post-42206762865475299832022-09-23T00:56:00.001-07:002022-09-23T00:56:24.444-07:00Query to get XML Template, Data source and template file details in Oracle Apps R12 Test Instance: R12.2.5Query:SELECT xtv.application_short_name,
xtv.template_code,
xtv.ds_app_short_name,
xtv.data_source_code,
xtv.template_type_code,
xtv.template_name,
xtv.description,
fu.user_name created_by,
(SELECT application_name
FROM fnd_application_vl
WHERE Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-27559498072929595822022-09-23T00:48:00.002-07:002022-09-23T00:59:57.228-07:00Query to get list of users having sensitive responsibilities in Oracle Apps R12 Test Instance: R12.2.5Query: SELECT fu.user_id,
fu.user_name,
frt.responsibility_name,
TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,
furg.end_date
FROM fnd_user fu ,
fnd_user_resp_groups_direct furg ,
fnd_responsibility_vl frt
WHERE fu.user_id = furg.user_id
AND frt.responsibility_idUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-7074671767247175702022-09-22T23:39:00.003-07:002022-09-23T01:00:44.793-07:00Query to get failed workflows with detail error message in Oracle Apps R12Test Instance: R12.2.5Query:SELECT
workflowitemeo.item_type,
workflowitemeo.item_key,
workflowitemeo.begin_date,
workflowitemeo.end_date,
workflowitemtypeeo.display_name,
wf_fwkmon.getitemstatus(workflowitemeo.item_type,
workflowitemeo.item_key,
workflowitemeo.end_date,
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5954268311855527678.post-50605635391305415992021-09-12T02:53:00.001-07:002021-09-12T02:53:14.794-07:00Performance Tuning - ANY_VALUE aggregate function (example) In this post, we have tried to explain the
performance impact of the miscellaneous function named ANY_VALUE with simple example.
How many times we have come across below
scenario (display department_name) where we are forced to use aggregate function
like MIN or MAX to retrieve a static value within the group. We Unknownnoreply@blogger.com1