This employee bills credit and debit info query displays credit and debit amounts for employees as of today, supporting payroll and financial tracking.
#sqlquery
SELECT --SQL4Fusion (An Orbit Analytics Project)
--This Report retrieves total information of credit and debit bills of an employee.
HAOUTL.NAME ORGANIZATION_NAME,
PAPF.PERSON_NUMBER PERSON_NUMBER,
PPNFV.FULL_NAME PERSON_NAME,
BBPC.RECORDED_DATE BILL_DATE,
SUM(BBPC.CREDIT_AMT) CREDIT_AMOUNT,
SUM(BBPC.DEBIT_AMT) DEBIT_AMOUNT
FROM
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL,
BEN_BILL_PER_CREDIT BBPC,
PER_PERSON_NAMES_F_V PPNFV,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM
WHERE
PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PPNFV.PERSON_ID
AND PAPF.PERSON_ID = BBPC.PERSON_ID
AND PAAM.ORGANIZATION_ID = HAOUTL.ORGANIZATION_ID
AND HAOUTL.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
GROUP BY HAOUTL.NAME ,
PAPF.PERSON_NUMBER ,
PPNFV.FULL_NAME ,
BBPC.RECORDED_DATE
ORDER BY HAOUTL.NAME,
PAPF.PERSON_NUMBER ;