PAY ROLL PERSON PAY METHOD (v1)

 

PAY ROLL PERSON PAY METHOD 

  SELECT PAPF.PERSON_NUMBER,

         PPNF.DISPLAY_NAME AS PERSON_NAME,

         PAAM.ASSIGNMENT_NAME,

         PAAM.ASSIGNMENT_NUMBER,

         TO_CHAR (PPOS.DATE_START, 'DD-MM-YYYY') AS HIRE_DATE,

         PPMF.NAME,

         PAY_TYPE.MEANING PAYMENT_AMOUNT_TYPE,

         PPMF.BANK_ACCOUNT_ID,

         PPMF.AMOUNT,

         PPMF.PERCENTAGE,

         POPMVL.ORG_PAYMENT_METHOD_NAME,

         POPMVL.PAYMENT_TYPE_ID,

         POPMVL.DEFINED_BALANCE_ID,

         PPTTL.PAYMENT_TYPE_NAME,

         PBA.BANK_ACCOUNT_NAME,

         PBA.BANK_ACCOUNT_NUM,

         PBA.MASKED_ACCOUNT_NUM,

         PBA.BANK_ACCOUNT_NUM_ELECTRONIC,

         PBA.CHECK_DIGITS,

         PBA.CURRENCY_CODE,

         PBA.DESCRIPTION,

         PBA.SECONDARY_ACCOUNT_REFERENCE,

         PBA.MASKED_IBAN,

         PBA.IBAN_NUMBER,

         PBA.EFT_USER_NUM,

         PBA.RECON_DIFFERENCE_CCID,

         PBA.RECON_RULESET_ID,

         PBA.GL_CUR_EXC_RATE_TYPE,

         PBA.BANK_ID,

         PBA.BANK_NAME,

         PBA.BANK_HOME_COUNTRY,

         PBA.BANK_NUMBER,

         PBA.BANK_CODE,

         PBA.BRANCH_ID,

         PBA.BANK_BRANCH_NAME,

         PBA.BRANCH_NUMBER,

         PBA.MISSION_STATEMENT,

         PBA.EFT_SWIFT_CODE,

         PBA.EFT_USER_NUMBER,

         PBA.EDI_ID_NUMBER,

         PBA.EDI_LOCATION,

         PBA.RFC,

         PBA.BANK_ACCOUNT_TYPE,

         --PBA.CLEAR_BANK_ACCOUNT_NUMBER,

         -- PBA.CLEAR_IBAN,

         PBA.BANK_ACCOUNT_TYPE_NAME

    FROM PAY_BANK_ACCOUNTS PBA,

         PAY_PAYMENT_TYPES_TL PPTTL,

         PAY_ORG_PAY_METHODS_VL POPMVL,

         FND_LOOKUP_VALUES_VL PAY_TYPE,

         PAY_PERSON_PAY_METHODS_F PPMF,

         PAY_PAYROLL_ASSIGNMENTS PPA,

         PER_PERIODS_OF_SERVICE PPOS,

         PER_ALL_ASSIGNMENTS_M PAAM,

         PER_PERSON_NAMES_F PPNF,

         PER_ALL_PEOPLE_F PAPF

   WHERE     1 = 1

         AND TRUNC (SYSDATE) BETWEEN PBA.START_DATE AND PBA.END_DATE

         AND PBA.BANK_ACCOUNT_ID = PPMF.BANK_ACCOUNT_ID

         AND PPTTL.LANGUAGE(+) = 'US'

         AND PPTTL.PAYMENT_TYPE_ID(+) = POPMVL.PAYMENT_TYPE_ID

         AND TRUNC (SYSDATE) BETWEEN POPMVL.EFFECTIVE_START_DATE(+)

                                 AND POPMVL.EFFECTIVE_END_DATE(+)

         AND POPMVL.ORG_PAYMENT_METHOD_ID(+) = PPMF.ORG_PAYMENT_METHOD_ID

         AND PAY_TYPE.LOOKUP_TYPE = 'PAY_AMOUNT_TYPES'

         AND PAY_TYPE.LOOKUP_CODE = PPMF.PAYMENT_AMOUNT_TYPE

         AND TRUNC (SYSDATE) BETWEEN PPMF.EFFECTIVE_START_DATE(+)

                                 AND PPMF.EFFECTIVE_END_DATE(+)

         AND PPMF.PAYROLL_RELATIONSHIP_ID(+) = PPA.PAYROLL_RELATIONSHIP_ID

         AND PPA.HR_ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID

         AND PPOS.PERIOD_OF_SERVICE_ID = PAAM.PERIOD_OF_SERVICE_ID

         AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE

                                 AND PAAM.EFFECTIVE_END_DATE

         AND EFFECTIVE_LATEST_CHANGE = 'Y'

         AND PAAM.ASSIGNMENT_TYPE = 'E'

         AND PAAM.PERSON_ID = PAPF.PERSON_ID

         AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE

                                 AND PPNF.EFFECTIVE_END_DATE

         AND NAME_TYPE = 'GLOBAL'

         AND PPNF.PERSON_ID = PAPF.PERSON_ID

         AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE

                                 AND PAPF.EFFECTIVE_END_DATE

ORDER BY LPAD (papf.person_number, 10, '0')