Daniel Borgstrom talking about Oracle

This post describes how to send a request output using e-mail. First, create two directories. The first one should point to the output directory. CREATE DIRECTORY xx_output AS '/u02/oracle/VISION/inst/apps/VISION/logs/appl/conc/out'; GRANT ALL ON DIRECTORY xx_output TO PUBLIC; The second one should be any directory that the database user can access. CREATE DIRECTORY xx_maildir AS '/home/oracle/tmp'; GRANT ALL ON DIRECTORY xx_maildir TO PUBLIC; Then create a generic procedure that can send mail with attachments. create or replace PROCEDURE xx_send_mail_file(p_subject IN VARCHAR2, p_message IN VARCHAR2, p_recipient IN VARCHAR2, p_max_size IN NUMBER DEFAULT 9999999999, p_filename1 IN VARCHAR2 DEFAULT NULL, p_filename2 IN VARCHAR2 DEFAULT NULL, p_filename3 IN VARCHAR2 DEFAULT NULL, ...

This post describes how to down- and upload different components using FNDLOAD. Value set Download FNDLOAD apps/ appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct download_file .ldt VALUE_SET FLEX_VALUE_SET_NAME=" VALUE_SET_NAME " Upload FNDLOAD apps/ appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct download_file .ldt CUSTOM_MODE=FORCE Note: Use the optional FNDLOAD parameter P_VSET_DOWNLOAD_CHILDREN set to N if you do not want the value set values to be downloaded. Concurrent Request Download FNDLOAD apps/ appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct download_file .ldt PROGRAM APPLICATION_SHORT_NAME= " APPL_SHORT_NAME " CONCURRENT_PROGRAM_NAME= " REQUEST_NAME " Upload FNDLOAD apps/ appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct download_file .ldt CUSTOM_MODE=FORCE Note: If value sets are used by the request parameters the set up for them will also be included in the ldt-file. If the va...

To be able to use the menu Help -> Diagnostics -> Examine you have to set the profile value Utilities:Diagnostics to Yes.

First, list your customization to get the exact path: begin jdr_utils.listdocuments('/oracle/apps/ar/hz/components/search/server',TRUE); end; Then, remove it: begin jdr_utils.deletedocument(p_document => '/oracle/apps/ar/hz/components/search/server/customizations/site/0/HzPuiDQMSrchResultsVO'); COMMIT; end;

1. FTP your form to $AU_TOP/forms/US 2. Compile using: frmcmp_batch userid=USERNAME/PASSWORD module=XXXXX.fmb output_file=XXXXX.fmx module_type=form 3. Move the fmx to the correct folder, ex mv XXXXX.fmx $AP_TOP/forms/US/XXXXX.fmx 4. Done

When you back in the day wanted to schedule or run something in the backgound you used DBMS_JOB. With  the beginning of release 10g of the database new functionality was introduced to accomplish this, the DBMS_SCHEDULER. DBMS_SCHEDULER is so much more flexible the DBMS_JOB and makes everything easier, some examples: To set up your intervals you just specify the parameter repeat_interval. MON-FRI 22.00: repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22;' Every hour, every day: repeat_interval => 'FREQ=HOURLY;INTERVAL=1' Every monday, 05:00: repeat_interval=> 'FREQ=DAILY; BYDAY=MON; BYHOUR=5;' You will not get the problem with shifting start times that you have with DBMS_JOB. Possible to create chains of jobs that are dependent of each other. Possible to schedule:   - Execution of host scripts.  - E-mail notifications.  - Execute host scripts on remote servers (requires an agent on the remote server).  - Execute jo...

Useful query which will fetch your inventory items from contract lines. Service Products: SELECT kl.line_number, ks.name, itm.segment1, itm.segment2 FROM okc_k_headers_v kh, okc_k_lines_v kl, okc_k_items ki, okc_line_styles_v ks, mtl_system_items_b itm WHERE kh.contract_number = ' contract number ' AND kh.contract_number_modifier IS NULL AND kh.id = kl.dnz_chr_id AND kh.id = ki.dnz_chr_id AND kl.id = ki.cle_id AND kl.lse_id = ks.id AND ki.jtot_object1_code IN ('OKX_SERVICE') AND kh.inv_organization_id = itm.organization_id AND ki.object1_id2 = itm.organization_id AND ki.object1_id1 = itm.inventory_item_id ORDER BY kl.line_number; Covered Products: SELECT kl.line_number, ks.NAME, i.segment1, i.segment2 FROM okc_k_headers_b kh, okc_k_lines_b kl, okc_k_items ki, okc_line_styles_v ks, csi_item_instances c, mtl_system_items_b i WHERE kh.contract_number = ' contr...

In a R12 upgrade project it can be a good idea to read some information from the database regarding the customizations. List Forms Personalization SELECT ft.user_function_name, fo.form_name, c.* FROM fnd_form_custom_rules c, fnd_form_functions f, fnd_form_functions_tl ft, fnd_form fo WHERE c.function_name = f.function_name AND f.function_id = ft.function_id AND f.form_id = fo.form_id ORDER BY 2 List Custom Forms SELECT ft.user_form_name, f.* FROM fnd_form f, fnd_form_tl ft WHERE f.form_id = ft.form_id AND form_name LIKE 'XX%' ORDER BY 4

If you have a concurrent request executable of type host (shell script on unix) and you want to set the status of the request to Warning or Error upon completion you need to use a small SQL-script to accomplish this. Warning DECLARE l_result BOOLEAN; l_session_id NUMBER; l_reqid NUMBER; BEGIN l_reqid := '&&1'; dbms_output.put_line('l_reqid '||l_reqid); fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null,  null, l_reqid, null,null,null,null,null,null,-1); l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.'); COMMIT; END; / exit 0; Error DECLARE l_result BOOLEAN; l_session_id NUMBER; l_reqid NUMBER; BEGIN l_reqid := '&&1'; dbms_output.put_line('l_reqid '||l_reqid); fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null,  null, l_reqid, n...

In most cases the output files will be written to $COMMON_TOP/admin/out/ <ENV.NAME> . If you don't find anything there investgate the environment variables $APPLCSF (path), $APPLLOG (folder name) and $APPLOUT (folder name). I read somewhere that if $APPLCSF is not set the outputs will be written to $ <APPLICATION_SHORT_NAME> / <VALUE OF $APPLOUT> e.g $AR_TOP/admin/out.

Internet Exploring is crashing when starting EBS (jInitiator). You have installed jInitiator ver 1.3.1.21 without any problem but when you are trying to start the Forms environment IE is crashing. A workaround can be found here . I have used this solution succsessfully both with XP and Win7.

This select will give you all the values for a specific profile option. SELECT potl.language, potl.user_profile_option_name, potl.description, po.profile_option_id, DECODE(pov.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', 10006, 'Org', 10007, DECODE(to_char(pov.level_value2), '-1', 'Responsibility', DECODE(to_char(pov.level_value), '-1', 'Server', 'Server+Resp')), 'UnDef') level_set, DECODE(to_char(pov.level_id), '10001', '', '10002', app.application_short_name, ...

One of my clients who run the Finance modules (AR, ARP, GL etc), Service Contracts and Field Service in E-Business Suite R12 had some issues with customers who were inactive. The issue arised when they were creating Service Requests (through API cs_servicerequest_pub.Create_ServiceRequest) and got an error reported by the API that the request could  not be created due to the customer was invalid. The reason that a customer is invalid can vary but one reason is that some level of the customer is inactive. In AR and the TCA model the customer (party) status can be set in the following tables: hz_parties (TCA) hz_party_sites (TCA) hz_party_site_uses (TCA) hz_cust_accounts (TCA-AR) hz_cust_acct_sites_all (TCA-AR) hz_cust_site_uses_all (TCA-AR) In my clients case status was Active in all tables except  hz_party_site_uses . Below I will explain how it's possible to change the statuses. Set status in Account Receivables If you inactivate an Account Site Address/Acc...

Submitted/scheduled job can be found in: dba_jobs Running jobs can be found in: dba_jobs_running Remove a job: dbms_job.remove( jobno ); DBMS_JOB have been deprecated and replaced by DBMS_SCHEDULER (available from 10.1). Read more about DBMS_SCHEDULER here .

Using Advanced Queing is a good idea when you have some integration or other stuff you want to execute asynchronous. With Advanced Queing it's also possible to automte the deque process using a subscriber. This means, as soon as you put anything on the que it will be dequed as soon as possible. The below example will illustrate that. The code will also show how to read a muli level XML using PL/SQL. Create que table (also stops the que and drops the que table if it's existing). BEGIN BEGIN DBMS_AQADM.STOP_QUEUE(queue_name => 'XXDEMO_INV_QUE'); EXCEPTION WHEN others THEN NULL; END; BEGIN DBMS_AQADM.DROP_QUEUE(queue_name => 'XXDEMO_INV_QUE'); EXCEPTION WHEN others THEN NULL; END; BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'XXDEMO_INV_AQTAB'); EXCEPTION WHEN others THEN NULL; END; DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'XXDEMO_INV_AQTAB', q...

Sometimes when you are developing a Forms Personalization it will error out before the form is opened. When this happens you cannot disable custom code to get the form to open and fix the problem. The easiest way to fix this is to disable it directly in the core table. UPDATE fnd_form_custom_actions SET enabled = 'N' WHERE action_id = your action id ; Now your form will open and you can fix the problem.

This post describes how to create a report using XML Publisher in Oracle E-Business Suite. Traditionally when a XML Publisher report has been created Oracle Reports have been the engine to produce the XML. A template has then been developed to use the created XML. This way of working is only recommended when the Oracle Report is already available and a new layout is created. If a new report is created from scratch you should use the XML Publisher engine to create both the XML and the layout. It is possible to use this method in both 11.5.10 and R12. How to create a native XML Publisher report The query and parameters is created in a XML-document called Data Template. Example of a Data Template: <?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name="CUST_LIST" description="Orderdetail per customer" version="1.0"> <parameters> <parameter name="p_fr_datum" datatype="character"/...

To list your organizations in EBS run select * from hr_all_organization_units; select * from hr_all_organization_units_tl;

To set the MOAC (Multiorg Access Control) context in your database session (SQL*PLUS, TOAD, SQL Developer etc) use: begin mo_global.set_policy_context('S', <orgID>); end; When policy context is set to M (Multi), data from all accessible Operating Units will be returned. When policy context is set to S (Single), then only data from the specified org_id will be returned. Set language: alter session set nls_language = 'SWEDISH';

When you are working with performance issues you need to have an empty database cache to get a "real" result from your query. If you run a query which runs for 2 minutes the first time it would run very much faster the second time. To prevent the database to fetch the result from the cache you need to clear the buffer cache. Run the command below to flush the cache (you need a DB-user which has sufficient privileges, for example system) alter system flush buffer_cache; Note: this should not be done in any production enviroment.

Out of the box can we not create a webservice that have a parameter (in or out) of type XMLType using SOA Gateway. When the service is called you will get an error. In-parameter: Error Description: Error occured while service was being generated. Error Details: oracle.apps.fnd.soa.util.SOAException: ServiceGenerationError: Exception returned from JCA Service Runtime. Missing class: oracle.xdb.XMLType Dependent class: oracle.tip.adapter.db.sp.oracle.TypeConverter Loader: AppsSOAAdapter:0.0.0 Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter/DBAdapter.jar Configuration: in /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter The missing class is available from the following locations: 1. Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/rdbms/jlib/xdb.jar (from in /u02/oracle/VISION2/apps/apps_st/comn/webapps/oafm/) This code-source is available in ...