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 at 28 but payroll is run on 27 so we need to get the value.
Thanks/Regards,
Afzal.
.