You are currently viewing Personal Payment Method, its importance and query for Active Employees
Personal Payment Method, its importance and query for Active Employees
- Personal Payment Method, its importance and query for Active Employees
Total Visits: 48

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
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.