Search This Blog

Saturday, November 25, 2017

Bank Details Query

SELECT   papf.person_number
,       ppnf.full_name
,           pp.attribute1 initials
,           bank_dtls.bank_account_name
,           bank_dtls.bank_name
,           bank_dtls.bank_branch_name
,          bank_dtls.bank_account_num
,           bank_dtls.iban_number
,           bank_dtls.currency_code
,           bank_dtls.base_org_pay_method_name
FROM    per_all_people_f papf
,    per_person_names_f ppnf
,    per_all_assignments_f paaf
,    pay_rel_groups_dn payrel
,           per_persons pp
,    (SELECT pba.bank_account_name
       ,       pba.bank_name
       ,       pba.bank_branch_name
       ,      pba.bank_account_num
       ,       pba.iban_number
       ,       popf.currency_code
       ,       popf.base_org_pay_method_name
     ,       pppmf.payroll_relationship_id
     FROM  pay_bank_accounts pba
     ,   pay_person_pay_methods_f pppmf
     ,   pay_org_pay_methods_f popf
     WHERE pppmf.bank_account_id = pba.bank_account_id
     AND     pppmf.org_payment_method_id = popf.org_payment_method_id
     AND TRUNC(SYSDATE) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN popf.effective_start_date AND popf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN pba.start_date AND pba.end_date) bank_dtls
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type ='GLOBAL'
AND payrel.payroll_relationship_id = bank_dtls.payroll_relationship_id(+)
AND payrel.assignment_id = paaf.assignment_id
AND papf.person_id = paaf.person_id
AND   paaf.assignment_status_type = 'ACTIVE'
--AND papf.person_number = '44'
-- AND paaf.primary_flag = 'Y'
AND   papf.person_id = pp.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
--AN D TRUNC(SYSDATE) BETWEen epbad.account_start_date and epbad.account_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN payrel.start_date AND payrel.end_date

No comments:

Post a Comment