Query to Get Direct Deport Cash Management Reconciliation Details

SELECT paa.assignment_action_id,

       ppa.effective_date payment_date,

       popmf.org_payment_method_name,

       per.employee_number,

       per.attribute29 employee_name,

       ppp.VALUE amount,

       NVL ( (SELECT 'Yes'

                FROM pay_ce_reconciled_payments pcrp

               WHERE pcrp.assignment_action_id = paa.assignment_action_id),

            'No')

          reconciled,

       (SELECT cleared_date

          FROM pay_ce_reconciled_payments pcrp

         WHERE pcrp.assignment_action_id = paa.assignment_action_id)

          cleared_date

  FROM pay_payroll_actions ppa,

       pay_assignment_actions paa,

       per_all_people_f per,

       per_all_assignments_f paaf,

       pay_pre_payments ppp,

       pay_org_payment_methods_f popmf

 WHERE     ppa.action_type = 'M'

       AND paa.payroll_action_id = ppa.payroll_action_id

       AND paa.assignment_id = paaf.assignment_id

       AND per.person_id = paaf.person_id

       AND paaf.primary_flag = 'Y'

       AND ppa.effective_date BETWEEN per.effective_start_date

                                  AND per.effective_end_date

       AND ppa.effective_date BETWEEN paaf.effective_start_date

                                  AND paaf.effective_end_date

       AND ppp.pre_payment_id = paa.pre_payment_id

       AND ppp.org_payment_method_id = popmf.org_payment_method_id

       AND ppa.org_payment_method_id NOT IN (86, 87)

       AND ppa.effective_date BETWEEN :l_start_date AND :l_end_date