PAY NACHA

select  distinct
       paa.tax_unit_id      tax_uid2,
       ppf.last_name || ', '||
       ppf.first_name                   emp_name,
       paf.assignment_number            asg_num,
       substr(ltrim(pea.segment4),1,8)  tr_code,
       substr(ltrim(pea.segment4),9,1)  chk_dig,
       pea.segment5                     bank_nam,
       pea.segment3                     acct_num,
       substr(hrl.meaning,1,8)||' '     acct_typ,
       decode(pps.value,0,'Yes','No')   pnot_dat,
       pps.value                        amount ,
       pps.pre_payment_id        pre_payment_id, 
	PAY_PAYUSNAC_XMLP_PKG.calc_pnot(pps.value) C_ADD_PRENOT
from   
       pay_pre_payments                 pps,
       pay_external_accounts            pea,
       pay_personal_payment_methods_f   ppp,
       per_people_f                             ppf,
       hr_lookups                                hrl,
       pay_action_interlocks                int2,
       pay_action_interlocks                int,
       pay_assignment_actions            paa3,
       pay_payroll_actions                   ppa,
       per_assignments_f                    paf,
       pay_assignment_actions           paa 
where  
       hrl.lookup_type                = 'US_ACCOUNT_TYPE'
and    hrl.lookup_code                = pea.segment2
and    ppp.external_account_id        = pea.external_account_id
and    ppp.personal_payment_method_id =
                            pps.personal_payment_method_id
and    :paid_date between ppp.effective_start_date and 
                       ppp.effective_end_date
and    pps.pre_payment_id             = paa.pre_payment_id
and    ppf.person_id                  = paf.person_id
and    :paid_date between ppf.effective_start_date and
                       ppf.effective_end_date
and    paf.payroll_id                 = :payrid
and    :period                        =    hr_payrolls.display_period_name(ppa.payroll_action_id)
and    ppa.action_type             in ('R', 'Q', 'B')
and    ppa.payroll_action_id                = paa3.payroll_action_id
and    ( 
            (pps.source_action_id is not null
             and paa3.assignment_action_id = pps.source_action_id)
          or (pps.source_action_id is null
                and paa3.source_action_id is null)
           )
and    paa3.assignment_action_id      = int2.locked_action_id
and    int2.locking_action_id         = int.locked_action_id
and    int.locking_action_id          = paa.assignment_action_id
and    paf.assignment_id              = paa.assignment_id
and    :paid_date between paf.effective_start_date and
                       paf.effective_end_date
and    paa.payroll_action_id          = :p_payroll_action_id 
 and paa.tax_unit_id=:tax_uid
ORDER BY 1,2,3