We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc
/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */ CREATE PROCEDURE p_get_name ( p_empno IN OUT NUMBER, p_name OUT VARCHAR2, p_err OUT NUMBER [code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
I have to implement a functionality in my application. I have an employee table and each employee does a transaction which is stored in a transaction table.
The functionality that I have to implement is that if an employee does not perform any transaction for a period of 2 years then updated the employee and set him inactive.
Employee Table ============== create table empoyee ( empno number, sal number) insert into employee(empno,sal) values (1, 200);
[code]...
If we make any update in Employee table for his/her salary, before update, that record should be inserted into EmployeeHist table and history will continue to build. Employee Table should have only current salary.If we change sal for emplyee # 1 from 200 to 800 then original current record in employee table will be inserted into employeehist table like
I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.
1-5 records 6-10 records 11-15 records 16-20 records 46-50 records
I have employee interface table something like this.
emp_idemp_name Job_title supervisor_name 1AJ Engineer BJ 2CK Analyst ND 3BJ Manager TR 5TR VP IT JD 6ND S Manager MD 7MD VP Telecom SK 8SK VP Eng JR
I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.
I am a Java person but since my app uses the Oracle DB I am to do this task.
We were trying to refresh the QA database from the production export dump.After import we checked the dba_rules in QA database doesnot show any rule . This seems a reasons for many of the packages get invalidated after import.I checked the production database that have 16 rules defined under dba_rules tables.I am not sure abt the syntax for the creation of rules . Is there any way i can take the backup of rules from PROD or some creation of rule script so that i can create similar rules in QA.
I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.
I have got single sign on working via the built in LDAP Directory authentication in APEX. But at the moment this is letting everyone who is within AD log inHow can I assign role permissions to each logged in user so some users have an admin role and see certain parts of the application / pages / navigation items while editors and readers have different permissions
And also to restrict access to certain pages within the application
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
I need to calculate first business day of a given month . Below is complete explanation
Business day=sould not include weekends and holidays.
In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.
So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.
I'll be passing the month begin date as parameter..so the function should return the first business day for that month.
format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate vcActivityName = Process Request usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity) Working Days: Monday through Saturday Hours of Operation: 9AM 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes. format of date time: 2010-09-17 14:50:51.150 vcActivityName = Process Request Don't worry about process request..
I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00 (and this is my problem, how to add this condition in this function)
I have been asked to see why a sister company cannot access multiple business areas through a single responsibility. At present, if they log in through a GL responsibility they can see all GL folders, items and the returned data. If they then go to the AP business area, they can see the folders and items, but when they run a report they get an error message 'Query returned no data' - but if they run the same query using an AP responsibility they get the correct results. I have looked at their user set up, and it seems fine i.e. has access to all business areas and required priveledges.
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :
1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x), 2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x), 3- delete employee (id_emp=x).
The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.
Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.
When step 2 ends, we release all the tables from the markers.
I'm trying to get employee hire anniversary between 2 days ago and within 7 days from today..It's working fine until I get to January system month (then mm=01, which is less then mm=12)
select employee_id, first_name, last_name, job_id, hire_date, case when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-2), 'mmdd'), '9999')) then 'Day before yesterday' when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-1), 'mmdd'), '9999')) then 'Yesterday' when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate), 'mmdd'), '9999')) then 'Today' when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate+1), 'mmdd'), '9999')) then 'Tomorrow' [code]....
i have an employee table where there is a column called join date. Now i have to select the employee according to the days. Means how many employee joined on Monday/Tuesday etc.
