| Query to get payroll values between dates [message #618811] |
Tue, 15 July 2014 15:01  |
 |
afzaa2yahoocom
Messages: 24
Registered: April 2011
Location: Dubai
|
Junior Member |
|
|
Hi All,
We are using oracle 11g database and EBS 12.1.3.
CREATE TABLE PER_PEOPLE_EXTRA_INFO
(PERSON_ID NUMBER,
START_DATE VARCHAR2(20),
END_DATE VARCHAR2(20))
INSERT INTO PER_PEOPLE_EXTRA_INFO
VALUES (
61, '2014/03/27 00:00:00','2014/10/03 00:00:00');
/
INSERT INTO PER_PEOPLE_EXTRA_INFO
VALUES (
62, '2014/03/27 00:00:00','2014/09/28 00:00:00');
SELECT PERSON_ID, SUBSTR(START_DATE,1,10) , SUBSTR(END_DATE,1,10)
FROM PER_PEOPLE_EXTRA_INFO
PERSON_ID START_DATE END_DATE
61 2014/03/27 2014/10/03
62 2014/03/27 2014/09/28
We will have the payroll results run for these employees for the month of 8th,9th months.
CREATE TABLE XXPAY_RUN_RESULTS
(PERSON_ID NUMBER,
PAYROLL_DATE DATE,
OT_HRS NUMBER)
INSERT INTO XXPAY_RUN_RESULTS
VALUES(61, TO_DATE('08/27/2014','MM/DD/YYYY'),40);
/
INSERT INTO XXPAY_RUN_RESULTS
VALUES(61, TO_DATE('09/27/2014','MM/DD/YYYY'),50);
/
INSERT INTO XXPAY_RUN_RESULTS
VALUES(62, TO_DATE('08/27/2014','MM/DD/YYYY'),10);
/
INSERT INTO XXPAY_RUN_RESULTS
VALUES(62, TO_DATE('09/27/2014','MM/DD/YYYY'),0);
/
SELECT * FROM XXPAY_RUN_RESULTS
PERSON_ID PAYROLL_DATE OT_HRS
61 8/27/2014 40
61 9/27/2014 50
62 8/27/2014 10
62 9/27/2014 0
DESIRED OUTPUT:
USER will enter from_Month, to_month as parameter let say 8th, 9th months
The query has to check in the first table if the start_date, end_date values falls under parameters 8th, 9th months then it has to bring the person_ids.
If exists then it has to check the payroll_run for 8th, 9th months for the person_ids and get the ot_hrs.
PERSON_ID START_DATE END_DATE OT_HRS
61 01-08-2014 31-08-2014 40
61 01-09-2014 30-09-2014 50
62 01-08-2014 31-08-2014 10
62 01-09-2014 28-09-2014 0 --As end date for 62 ends ast 28 but payroll is run on 27 so we need to get the value.
Thanks/Regards,
Afzal.
|
|
|
|
| Re: Query to get payroll values between dates [message #618816 is a reply to message #618811] |
Tue, 15 July 2014 15:16  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I would say that this looks like the wrong approach. You can do it all with flexfields.
If your implementation consultants are saying that you need custom tables, perhaps what you actually need is better implementation consultants.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
