In this article, we will look into Personal payment method details and how to extract Personal Payment Method details for active employees.
Personal Payment Method can be created only when the Payroll is assigned to a person and valid payment methods are assigned to the Payroll. It is possible to assign multiple Personal Payment Methods for a single person. For example, an employee can be assigned two personal payment methods as follows: 50% of his/her payment through EFT (a bank account is required for this) and the other 50% through Check.
In case a personal payment method is not assigned to an employee, the Default Payment Method that is provided at the time of Payroll Definition will be utilized for that employee.
An important aspect of the personal payment method is that it has to be associated with an organization’s payment method in order to specify the payment source. As with the organization payment method, a bank account is required for EFT payment.
Other aspects are:
- Processing Order – in the case of multiple payment methods, the order in which those methods have to be processed is to be specified
- Amount Type – the type of payment to be done
- Amount – the actual amount of payment
The task associated with it is Manage Personal Payment Methods.
SQL Query to pull Personal Payment Method Info for Active Employees
SELECT DISTINCT P.PERSON_NUMBER,
(
SELECT DISTINCT NAME.LIST_NAME
FROM PER_PERSON_NAMES_F NAME
WHERE NAME.PERSON_ID = P.PERSON_ID
AND NAME.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN NAME.EFFECTIVE_START_DATE
AND NAME.EFFECTIVE_END_DATE
) AS EMPLOYEE_NAME,
TO_CHAR(A.EFFECTIVE_START_DATE, 'MM/DD/YYYY') AS ASSIGNMENT_START_DATE,
TO_CHAR(A.EFFECTIVE_END_DATE, 'MM/DD/YYYY') AS ASSIGNMENT_END_DATE,
A.ASSIGNMENT_NUMBER,
P2.PAYROLL_RELATIONSHIP_NUMBER,
LEG.NAME AS LDG,
(
SELECT DISTINCT LE.NAME
FROM PER_LEGAL_EMPLOYERS LE
WHERE LE.ORGANIZATION_ID = A.LEGAL_ENTITY_ID
AND SYSDATE BETWEEN LE.EFFECTIVE_START_DATE
AND LE.EFFECTIVE_END_DATE
) AS LEGAL_EMPLOYER,
(
SELECT DISTINCT DE.NAME
FROM HR_ALL_ORGANIZATION_UNITS DE
WHERE DE.ORGANIZATION_ID = A.ORGANIZATION_ID
AND SYSDATE BETWEEN DE.EFFECTIVE_START_DATE
AND DE.EFFECTIVE_END_DATE
) AS DEPARTMENT,
(
SELECT DISTINCT PALP.REPORTING_NAME
FROM PAY_ASSIGNED_PAYROLLS_DN PASP,
PAY_ALL_PAYROLLS_F PALP
WHERE P3.PAYROLL_TERM_ID = PASP.PAYROLL_TERM_ID
AND PALP.PAYROLL_ID = PASP.PAYROLL_ID
AND SYSDATE BETWEEN PASP.START_DATE
AND PASP.END_DATE
AND SYSDATE BETWEEN PALP.EFFECTIVE_START_DATE
AND PALP.EFFECTIVE_END_DATE
AND PASP.START_DATE = (
SELECT MAX(PASP1.START_DATE)
FROM PAY_ASSIGNED_PAYROLLS_DN PASP1
WHERE PASP1.PAYROLL_TERM_ID = PASP.PAYROLL_TERM_ID
AND PASP1.START_DATE <= SYSDATE
)
) AS PAYROLL_NAME,
PAY.NAME AS PPM_NAME,
TO_CHAR(PAY.EFFECTIVE_START_DATE, 'MM/DD/YYYY') AS PPM_START,
TO_CHAR(PAY.EFFECTIVE_END_DATE, 'MM/DD/YYYY') AS PPM_END,
PAY.PAYMENT_AMOUNT_TYPE AS PPM_AMOUNT_TYPE,
PAY.AMOUNT AS PPM_AMOUNT,
PAY.PERCENTAGE AS PPM_PERCENTAGE,
PAY.PRIORITY AS PPM_PRIORITY,
BA.BANK_ACCOUNT_NAME,
BA.BANK_ACCOUNT_NUM,
BA.BANK_BRANCH_NAME,
BA.BRANCH_NUMBER
FROM PER_ALL_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PAY_PAY_RELATIONSHIPS_DN P2,
PAY_PAYROLL_ASSIGNMENTS P3,
PER_LEGAL_EMPLOYERS LEG,
PAY_PERSON_PAY_METHODS_F PAY,
PAY_BANK_ACCOUNTS BA
WHERE P.PERSON_ID = A.PERSON_ID
AND A.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED')
AND A.ASSIGNMENT_TYPE = 'E'
AND P2.PAYROLL_RELATIONSHIP_ID = P3.PAYROLL_RELATIONSHIP_ID
AND P3.HR_ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND LEG.ORGANIZATION_ID = A.LEGAL_ENTITY_ID
AND PAY.PAYROLL_RELATIONSHIP_ID = P2.PAYROLL_RELATIONSHIP_ID
AND PAY.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN LEG.EFFECTIVE_START_DATE
AND LEG.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN P2.START_DATE
AND P2.END_DATE
AND SYSDATE BETWEEN P3.START_DATE
AND P3.END_DATE
AND SYSDATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
ORDER BY P.PERSON_NUMBER
