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
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
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
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM