1 / 34

Oracle Financials – APEX

Oracle Financials – APEX. Presented by : Ian Drever Chitra Kanakaraj The University of Waikato. Presentation Contents. Business Requirements Design Considerations Configuring Oracle Financials to link to Application Express

rudolf
Download Presentation

Oracle Financials – APEX

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Financials – APEX Presented by : Ian Drever Chitra Kanakaraj The University of Waikato

  2. Presentation Contents • Business Requirements • Design Considerations • Configuring Oracle Financials to link to Application Express • Configuring Oracle Financials to link to a page within an Application Express application • How this works in practice • Questions

  3. Business Requirements • The University decided to implement Oracle Projects to manage costing and billing for research and other projects. • The Research Office had developed a comprehensive stand alone Microsoft Access database to control Research at the university. • Oracle Financials was to be the prime source for research project information • APEX would provide additional information associated with managing research projects • Ownership of the APEX application would remain with the Research Office • A large number of staff may require access to research project information

  4. Design Considerations • The user must be able to open the APEX project details from within an open project in Oracle Projects • Single sign on is required • Access would be required to Project Information without requiring access to Oracle Projects • Oracle Projects would provide the base source data • APEX would store additional data • Oracle Projects would capture transactional and revenue costing • APEX would not update Oracle Projects data

  5. Configuring Oracle Financials to link to Application Express

  6. Step 1: Create a profile for launching Application Express • Create the Profile under the Application Developer Responsibility • Name – “Oracle Application Express Launcher”

  7. Step 2: Set the site for the for the profile created in Step 1. • Responsibility – System Administrator • Menu Option: Profile -> System and search for the ‘Oracle Application Express Launcher’. • Set the value at the Site Level as: • “http://<hostname>:<port>/pls/apex”

  8. Step 3: Create a function • Responsibility: System Administrator • Menu Option: Application ->Function • Create Function: APEX_LAUNCH • User Function Name: Projects: Options: APEX_LAUNCH

  9. Step 4: Set the function properties • Set the Property type to “SSWA plsql function”

  10. Step 5: Create a line for the APEX_LAUNCHER on the Web Enabled PL.Sql page Web Enabled PL/SQL page • Menu Option: Security -> Web PL/SQL • Search for the APEX_LAUNCHER function • Set the Type column to“Package”

  11. Step 6: Create a menu entry for APEX. • Menu Option: Application->Menu • Add a line for the LAUNCH APEX prompt

  12. Step 7: Create a menu option line for APEX on the PA_IMP_SUPERUSER_GUI menu

  13. Compile the package APEX_LAUNCHER in the EBS database (APPS) • This package has the procedure APEX_LAUNCH. Process flow in the procedure is as follows • This package: • Sets the url using FND_PROFILE.value('LAUNCH_APEX'); • Passes the application id and the page id 111 in the url • Sends a COOKIE using OWA_COOKIE.send procedure with a name and a value parameter. Use this value parameter to pass the username and password for the APEX login authentication. • Opens a new window using the UnilinkURL. • In the APEX application on page 101 (i.e. logon page), create a process (on load before header) to read the OWA_COOKIE sent from the above procedure and set the USERNAME and PASSWORD. Use the APEX login API wwv_flow_custom_auth_std.login to login the APEX application. • The code is based on this white paper:http://www.oracle.com/technology/products/database/application_express/pdf/Extend_Oracle_Applications_11i.pdf

  14. PROCEDURE apex_launch( application IN NUMBER, • page IN NUMBER DEFAULT 1, • request IN VARCHAR2 DEFAULT NULL, • item_names IN VARCHAR2 DEFAULT NULL, • item_values IN VARCHAR2 DEFAULT NULL) is • user_key VARCHAR(100); • apex_launcher_profile varchar2(2000); • unilink_user varchar2(30); • unilink_url varchar2(150); • BEGIN • -- replace the FND_GLOBAL.user_name with unilink_user • unilink_user := 'UNILINK'; • unilink_url :='http://<server>/pls/htmldb/f?p=114:111:::NO::P101_USERNAME,P101_PASSWORD,P101_FILE_NUMBER,P101_USER_ID:unilink,unilink,,'||FND_GLOBAL.user_id; • BEGIN • SELECT encrypted_user_password • INTO user_key • from fnd_user • where user_name = unilink_user; • EXCEPTION WHEN OTHERS THEN • user_key := ''; • END; • apex_launcher_profile := FND_PROFILE.value('LAUNCH_APEX'); • if apex_launcher_profile IS NULL Then • htp.p ('Please contact System Administrator. '); • htp.p ('Profile - LAUNCH_APEX is null') ; • return; • end if;

  15. unilink_url := apex_launcher_profile||'/pls/htmldb/f?p=114:111:::NO::P101_USERNAME,P101_PASSWORD,P101_FILE_NUMBER,P101_USER_ID:unilink,unilink,,'||FND_GLOBAL.user_id; • -- FND_GLOBAL.user_name gives the login user name for the ESB system • -- in order to pass the valid Apex Unilink user_name a constant value is used. • OWA_UTIL.mime_header('text/html', false); • OWA_COOKIE.send • (name=>'APEX_APPS_'||application, • value=> unilink_user||':'||'unilink', • path=>'/', • domain=>'.waikato.ac.nz' ); • OWA_UTIL.http_header_close; • htp.p(' • <script LANGUAGE="JavaScript"><!-- • history.go(-1); • var v_win; • v_win = window.open("'||unilink_url|| '",' || • '"",' || • '",width=800,height=600,scrollbars,resizable"); • v_win.moveTo ((screen.width/2) - ' || (800 / 2) || ', (screen.height/2) - ' || (600 / 2) || '); • v_win.focus(); • //--></script> • '); • exception when others then • htp.p(SQLERRM); • END;

  16. Set up Page 111 in Apex, this is used by the launcher on Oracle Financials: • Create a blank page in APEX application that needs to be integrated with Oracle EBS, e.g. P111. • Edit page attribute and set the Security - Authentication to ‘Page is Public’. • Create a branch in this page, as follows: Branch Type Branch to Page Accept Processing (not common) Branch Point On Load: Before Header Action – Page 101 (This is my login page id) • Unconditional Branch

  17. Add a process in the 101 Page. • APEX 101 page - before header process • DECLARE • c OWA_COOKIE.cookie; • a wwv_flow_global.vc_arr2; • BEGIN • c := OWA_COOKIE.get('APEX_APPS_'||:APP_ID); • a := htmldb_util.string_to_table(c.vals(1)); • :P101_USERNAME := a(1); • :P101_PASSWORD := a(2); • IF :P101_PASSWORD IS NOT NULL THEN • wwv_flow_custom_auth_std.login( • P_UNAME => :P101_USERNAME, • P_PASSWORD => :P101_PASSWORD, • P_SESSION_ID => v('APP_SESSION'), • P_FLOW_PAGE => :APP_ID||':1' • ); • END IF; • EXCEPTION WHEN OTHERS THEN • :P101_USERNAME := 'Unilink'; • END;

  18. Demonstration • Or how it works in practice • Clicking on the APEX Unilink menu item will open the required application in APEX

  19. APEX Research Application

  20. Questions?

  21. The Earth has at least two moons, the latest is a 3-miles-wide (5-km) satellite. • It takes 770 years to complete a horseshoe-shaped orbit around the Earth. • The moon is called Cruithne and will remain in a suspended state around Earth for at least 5,000 years. How many moons does the Earth have?

  22. Configuring Oracle Financials to link to a page within an Application Express application

  23. Prerequisites • Set the responsibility to “Project Implementation Superuser”

  24. Step 1: Open a project from the Projects menu option

  25. Step 2: Access the Help>Diagnostics>Custom Code>Personalize menu option.

  26. Step 3: Update the PA_PAXPREPR_PROJECT function • Add a line for the Trigger event – Initialize Unilink Menu • and Global Variables • Set the Trigger Event to WHEN_NEW_FORM_INSTANCE • Add the responsibilities that you want to access the link

  27. Step 4: Set the function actions • Add line for the Type ‘Menu’ • Enter the Menu Label as ‘APEX UNILINK’

  28. Step 5: Create a line for Call APEX UNILINK in the PA_PAXPREPR_PROJECT • Set the conditions for the “Call APEX Unilink” function • Set the Trigger Event to SPECIAL9 • Add the responsibilities

  29. Step 6: Set the Action argument • Set the Type to “BUILTIN” • Set Builtin Type to Launch a URL

  30. Argument ='http://<apexservername>/pls/htmldb/f?p=114:111:::NO::P101_USERNAME,P101_PASSWORD,P101_FILE_NUMBER,P101_USER_ID:unilink,unilink,'||:PROJECT_FOLDER.SEGMENT1_MIR||','||FND_GLOBAL.user_id • Note: Include the = sign in the argument since we are passing the segment1 value through the url. • The above oracle EBS form personalization is done by using the example given in the following link http://www.scribd.com/doc/46650/Examples-of-Oracle-EBS-Form-Personalization

  31. Practical Application • Or how it works in practice • Clicking on the APEX UNILINK menu option will open the required application page in APEX for the project linked to Oracle Projects.

  32. APEX Project Page

  33. References & Credits “Oracle Applications Express The Fast Way to Extend Oracle Applications 11i By Rod West, Cabot Consulting” “ORACLE EBS 11I, VERSION 11.5.10 « FORM PERSONALIZATION » Auteur : Joël Asselin Date de création : Octobre 20, 2006 “

  34. Questions?

More Related