VIEW: APPS.HRFV_GB_DIRECT_DEPOSITS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,pp.full_name person_name ,opm.org_payment_method_name payment_method ,ppm.amount amount ,ppm.percentage percentage ,ppm.effective_start_date effective_start_date ,hr_discoverer.check_end_date(ppm.effective_end_date) effective_end_date ,ppm.priority priority ,decode(pt.territory_code, 'GB', pea.segment3, null) sort_code ,decode(pt.territory_code, 'GB', decode(pea.segment1, null, null, hr_bis.bis_decode_lookup( 'GB_BANKS', pea.segment1)), null) bank_name ,decode(pt.territory_code, 'GB', pea.segment2, null) bank_branch ,decode(pt.territory_code, 'GB', pea.segment4, null) account_number ,decode(pt.territory_code, 'GB', pea.segment5, null) account_name ,decode(pt.territory_code, 'GB', pea.segment6, null) account_type ,decode(pt.territory_code, 'GB', pea.segment7, null) bld_society_account_number ,decode(pt.territory_code, 'GB', pea.segment8, null) bank_branch_location ,ptT.payment_type_name payment_type ,opm.currency_code currency_code ,pa.assignment_number assignment_number ,orgT.name organization_name ,job.name job_name ,pft.name position_name ,gdt.name grade_name ,locT.location_code location_name ,pp.employee_number employee_number ,'_DF:PAY:PAY_PERSONAL_PAYMENT_METHODS:ppm' ,pa.assignment_id assignment_id ,pa.business_group_id business_group_id ,pea.external_account_id external_account_id ,gdt.grade_id grade_id ,job.job_id job_id ,opm.org_payment_method_id org_payment_method_id ,pa.person_id person_id ,ppm.personal_payment_method_id personal_payment_method_id ,pft.position_id position_id FROM hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,per_jobs job ,hr_all_positions_f_tl pft ,per_grades_tl gdt ,hr_locations_all_tl locT ,pay_payment_types pt ,pay_payment_types_tl ptT ,per_people_x pp ,pay_org_payment_methods_f opm ,per_assignments_x pa ,pay_external_accounts pea ,pay_personal_payment_methods_f ppm WHERE ppm.assignment_id = pa.assignment_id AND pa.person_id = pp.person_id AND ppm.org_payment_method_id = opm.org_payment_method_id AND opm.payment_type_id = pt.payment_type_id AND pt.payment_type_id = ptT.payment_type_id AND ptT.language = userenv('LANG') AND ppm.external_account_id = pea.external_account_id AND TRUNC(sysdate) BETWEEN ppm.effective_start_date AND ppm.effective_end_date AND TRUNC(sysdate) BETWEEN opm.effective_start_date AND opm.effective_end_date AND pa.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND pa.job_id = job.job_id(+) AND pa.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND pa.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND pa.location_id = locT.location_id(+) AND locT.language (+) = userenv('LANG') AND pt.territory_code = 'GB' AND pa.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND pa.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, pa.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PP.FULL_NAME PERSON_NAME
, OPM.ORG_PAYMENT_METHOD_NAME PAYMENT_METHOD
, PPM.AMOUNT AMOUNT
, PPM.PERCENTAGE PERCENTAGE
, PPM.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, HR_DISCOVERER.CHECK_END_DATE(PPM.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PPM.PRIORITY PRIORITY
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT3
, NULL) SORT_CODE
, DECODE(PT.TERRITORY_CODE
, 'GB'
, DECODE(PEA.SEGMENT1
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP( 'GB_BANKS'
, PEA.SEGMENT1))
, NULL) BANK_NAME
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT2
, NULL) BANK_BRANCH
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT4
, NULL) ACCOUNT_NUMBER
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT5
, NULL) ACCOUNT_NAME
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT6
, NULL) ACCOUNT_TYPE
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT7
, NULL) BLD_SOCIETY_ACCOUNT_NUMBER
, DECODE(PT.TERRITORY_CODE
, 'GB'
, PEA.SEGMENT8
, NULL) BANK_BRANCH_LOCATION
, PTT.PAYMENT_TYPE_NAME PAYMENT_TYPE
, OPM.CURRENCY_CODE CURRENCY_CODE
, PA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ORGT.NAME ORGANIZATION_NAME
, JOB.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, '_DF:PAY:PAY_PERSONAL_PAYMENT_METHODS:PPM'
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEA.EXTERNAL_ACCOUNT_ID EXTERNAL_ACCOUNT_ID
, GDT.GRADE_ID GRADE_ID
, JOB.JOB_ID JOB_ID
, OPM.ORG_PAYMENT_METHOD_ID ORG_PAYMENT_METHOD_ID
, PA.PERSON_ID PERSON_ID
, PPM.PERSONAL_PAYMENT_METHOD_ID PERSONAL_PAYMENT_METHOD_ID
, PFT.POSITION_ID POSITION_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_JOBS JOB
, HR_ALL_POSITIONS_F_TL PFT
, PER_GRADES_TL GDT
, HR_LOCATIONS_ALL_TL LOCT
, PAY_PAYMENT_TYPES PT
, PAY_PAYMENT_TYPES_TL PTT
, PER_PEOPLE_X PP
, PAY_ORG_PAYMENT_METHODS_F OPM
, PER_ASSIGNMENTS_X PA
, PAY_EXTERNAL_ACCOUNTS PEA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
WHERE PPM.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND PA.PERSON_ID = PP.PERSON_ID
AND PPM.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND OPM.PAYMENT_TYPE_ID = PT.PAYMENT_TYPE_ID
AND PT.PAYMENT_TYPE_ID = PTT.PAYMENT_TYPE_ID
AND PTT.LANGUAGE = USERENV('LANG')
AND PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
AND TRUNC(SYSDATE) BETWEEN PPM.EFFECTIVE_START_DATE
AND PPM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND PA.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND PA.JOB_ID = JOB.JOB_ID(+)
AND PA.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND PA.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND PA.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND PT.TERRITORY_CODE = 'GB'
AND PA.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PA.BUSINESS_GROUP_ID) WITH READ ONLY