Sunday, November 26, 2017

bash: dos2unix: command not found

Either install it or use "sed" command instead as below

sed -i 's/\r$//' fileName

Tuesday, June 20, 2017

Error: Unable to retrieve Project Accounting implementation information

Few concurrent request fails when they are submitted using the streamline process.  It is fine when the process is submitted stand alone.

Error would be something like:

"
Using default of 500 expenditure items per set because profile
PA_NUM_EXP_ITEMS_PER_SET is not defined
pasini:BC Profile Option is Not SET.
Defaulting key segment because profile PA_SELECTIVE_FLEX_SEG is not defined
This concurrent request is not being rescheduled
ORA-01403: no data found
                                            
Unable to retrieve Project Accounting implementation information.
pasini() returned error
"

The reason could be:

1> Program 'Operating Unit Mode' should be set to single.

Navigate to 'System Administration' responsibility > Concurrent > Programs
Search your 'Concurrent Program'
Click update pencil icon

Now, in request tab for 'Operating Unit Mode' pick 'Single' and 'Apply'.


Additional 'Operating Unit' parameter in submit request form


2> If you are submitting this MOAC enabled program from back end, operating unit must be set before submit request.

Call
FND_REQUEST.SET_ORG_ID(<org_id>);
before calling
FND_REQUEST.SUBMIT_REQUEST();

--
Ref: My Experience/Oracle Metalink(2034041.1)/User Guides/Different other blogs available/Colleagues

Tuesday, May 16, 2017

ORA-00911: invalid character 00911. 00000 - "invalid character"

Error: ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:

You might get this error while executing SQL queries

Use the CONVERT function to convert the query string and try again, I hope it should work

To get more details on CONVERT function use please see:  Convert Function

Friday, May 12, 2017

Configuration Values - APIs

-- Global and localized configuration settings provide the appropriate defaults for business groups
-- Business Rules are held in pqp_configuration_values table
-- HRMS > Other Definitions > Configuration Values
-- Here I am updating a configuration value
declare
--
ln_con_val_id  number; 
ln_bg          number;
ln_ovn         number;
lc_cat         varchar2(200);
lc_info        varchar2(200);
--
begin
--
   select pcv.PCV_INFORMATION_CATEGORY
         ,pcv.PCV_INFORMATION2
         ,pcv.business_group_id
         ,pcv.configuration_value_id
         ,pcv.object_version_number
     into lc_cat
         ,lc_info
         ,ln_bg
         ,ln_con_val_id
         ,ln_ovn     
     from pqp_configuration_types   pct
         ,pqp_configuration_modules pcm
         ,pqp_configuration_values  pcv
    where 1=1
      and pct.module_id = pcm.module_id
      and pcm.module_name = 'Assignment Budget Value Maintenance'
      and pct.description = 'Maintenance Configuration'
      and pcv.pcv_information_category = pct.configuration_type
      ;
   
    dbms_output.put_line('Before:   lc_cat: '||lc_cat||'  lc_info: '||lc_info||' in_ovn: '|| ln_ovn);
   --
   pqp_pcv_api.update_configuration_value(p_validate        => false
                                         ,p_effective_date                 => sysdate
                                         ,p_business_group_id              => ln_bg
                                         ,p_configuration_value_id         => ln_con_val_id
                                         ,p_pcv_information_category       => lc_cat
                                         ,p_pcv_information2               => 'Y'
                                         ,p_object_version_number          => ln_ovn);
                                        
   
    dbms_output.put_line('Configuration Value Updated');    
   
    commit;                             
--
exception
   when others then
      dbms_output.put_line('Error : '||sqlerrm);
      rollback;
end; 
--

Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues
  

HRMS RUP8 - APIs new validations

There are many new validations have been added to RUP8.

I came across few in hr_assignment_api, I shall keep adding if I find any new one.

1). Error :ORA-20001: This assignment does not have a contract type effective as of DD-MON-YYYY. Please use the Extra Details of Service form to assign a contract type.

> Check for Contract Type meaning/spelling/Lower/Upper case
> HRMS > People >  Enter and Maintain > Assignment > Others > Extra Details of Service
> Use pqp_aat_api.update_assignment_attribute to correct if any.


2). Error :ORA-20001: HR_500161_ASG_INV_SOB_DCC

> Check if you have default_code_combination_id populated without set_of_books_id
> HRMS > People > Enter and Maintain > Assignment > Purchase Order Information
> Use hr_assignment_api.update_emp_asg / update_cwk_asg API to correct if any




Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

Running SQLTXPLAIN for a sql script

This method is based on the EXPLAIN PLAN FOR command.
To use this XPLAIN method, be sure SQLT has been installed first, if not please ask your DBA team to install. (Installation Instructions: Metalink Doc ID 215187.1)

> Get the directory details of the SQLT installed and SQLTXPLAIN password from the DBA team.
> Now save your required SQL script in .txt/.sql format replacing all input parameters  as bind variables.
   >> File should have one and only one valid SQL Statement
   >> Should not have empty lines (blank lines)
   >> The SQL Statement should not have a semicolon ';' at the end
   >> For example please refer sqlt/input/sample/sql1.sql
   >> For multiple scripts please create separate files.
> Now go to SQLT directory and connect to SQL*Plus
> Run SQL> START [path]sqltxplain.sql [path]filename [sqltxplain_password] 
   Ex: START /oracle/orauat/sqlt/run/sqltxplain.sql /oracle/orauat/sqlt/run/SqlScript1.txt sqltxplain_password

You may get below error

ERROR at line 1:
ORA-20106: SQLT parameter connect_identifier must be set when running SQLT from a remote client
ORA-06512: at line 10


> Execute below to overcome this error
EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@SID');  

 >> SID - Service Identifier of your DB instance

> You can Monitor progress from other session:
select *
  from SQLTXADMIN.sqlt$_log_v


> Log Directory
select *
  from dba_directories
 where directory_name like 'SQLT%UDUMP%'


** If the only output file is a log file similar to : sqlt_s50605_log.zip
Then it may be that the SQLT did not complete successfully please examine the log for errors.



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues