Employee Bill Payments (SQL Script)

Human Capital Management (HCM) Human Resources (HRMS)
1

This employee bill payments query displays employee payment details for active personnel as of today, supporting payroll and financial tracking.

#sqlquery

SELECT 
       --SQL4Fusion (An Orbit Analytics Project)
	   --This query displays employee payment details with organization info as of today.
       HAOUT.NAME                  ORGANIZATION_NAME,
	   PAPF.PERSON_NUMBER          PERSON_NUMBER,
       PPNFV.FULL_NAME             PERSON_NAME,      
       BBP.PAYMENT_DATE            PAYMENT_DATE,
       BBP.PAYMENT_MODE            PAYMENT_MODE,
       BBP.PAYMENT_DOC_NUM         PAYMENT_DOCUMENT_NUMBER,
       BBP.AMT_PAID                AMOUNT_PAID,
       BBP.PER_ACCT_NUM            ACCOUNT_NUMBER,
       BBP.ADJ_AMT                 ADJUSTED_AMOUNT,
       BBP.ADJ_DATE                ADJUSTED_DATE
FROM   
       HR_ALL_ORGANIZATION_UNITS_TL HAOUT,
       BEN_BILL_PAYMENTS            BBP,
       PER_PERSON_NAMES_F_V         PPNFV,
       PER_ALL_PEOPLE_F             PAPF,
       PER_ALL_ASSIGNMENTS_M        PAAM
WHERE  1=1
   AND PAPF.PERSON_ID 			= PAAM.PERSON_ID
   AND PAPF.PERSON_ID 			= PPNFV.PERSON_ID
   AND PAPF.PERSON_ID 			= BBP.PERSON_ID
   AND PAAM.ORGANIZATION_ID 	= HAOUT.ORGANIZATION_ID
   AND HAOUT.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
   ORDER BY HAOUT.NAME, 
			PAPF.PERSON_NUMBER