Function to get the Receivables application apply date

CREATE OR REPLACE FUNCTION xxxxxx_get_apply_date (
   p_payment_schedule_id   IN   NUMBER,
   p_org_id            IN   NUMBER,
   p_from_date         IN   DATE,
   p_to_date           IN   DATE
)
   RETURN VARCHAR2
IS
   CURSOR c1
   IS 
   SELECT app.apply_date apply_date                                -- Q1
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all ctl
            ,ra_cust_trx_types_all ctt
            ,ar_receipt_methods rm
            ,ar_cash_receipts_all cr
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_cash_receipt_history_all crh
      WHERE  app.status = 'APP'
      AND    app.cash_receipt_id = cr.cash_receipt_id(+)
      AND    cr.receipt_method_id = rm.receipt_method_id(+)
      --AND    app.payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    app.applied_customer_trx_line_id = ctl.customer_trx_line_id(+)
      AND    cr.cash_receipt_id = crh.cash_receipt_id(+)
      AND    crh.current_record_flag(+) = 'Y'
      --AND    ps.payment_schedule_id = :p_payment_schedule_id
      --AND    ct.customer_trx_id = :p_customer_trx_id
      --AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS',crh.status) IS NULL  -- RECEIPT_STATE ***
      AND    ct.org_id = p_org_id
      AND    ps.payment_schedule_id = app.payment_schedule_id
      AND    app.payment_schedule_id IN (
                SELECT app1.payment_schedule_id
                FROM   ar_receivable_applications_all app1
                WHERE  app1.receivable_application_id =
                                                 app.receivable_application_id
                AND    app1.applied_payment_schedule_id =
                                                         p_payment_schedule_id)
      AND    app.gl_date <= p_to_date                             
      UNION ALL
      SELECT app.apply_date apply_date                              -- Q1_1
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all ctl
            ,ra_cust_trx_types_all ctt
            ,ar_receipt_methods rm
            ,ar_cash_receipts_all cr
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_cash_receipt_history_all crh
      WHERE  app.status = 'APP'
      AND    app.cash_receipt_id = cr.cash_receipt_id(+)
      AND    cr.receipt_method_id = rm.receipt_method_id(+)
      --AND    app.payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    app.applied_customer_trx_line_id = ctl.customer_trx_line_id(+)
      AND    cr.cash_receipt_id = crh.cash_receipt_id(+)
      AND    crh.current_record_flag(+) = 'Y'
      --AND    ps.payment_schedule_id = :p_payment_schedule_id
      --AND    ct.customer_trx_id = :p_customer_trx_id
      AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS'
                                                   ,crh.status) IS NULL
      -- RECEIPT_STATE ***
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    ct.org_id = p_org_id
      AND    app.applied_payment_schedule_id IN (
                SELECT app1.applied_payment_schedule_id
                FROM   ar_receivable_applications_all app1
                WHERE  app1.receivable_application_id =
                                                 app.receivable_application_id
                AND    app1.payment_schedule_id = p_payment_schedule_id)
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT app.apply_date apply_date                                     -- Q3
      FROM   ar_payment_schedules_all ps
            ,ar_payment_schedules_all psa
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all art
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.applied_payment_schedule_id = psa.payment_schedule_id
      AND    app.status IN ('ACC', 'OTHER ACC')
      AND    art.receivables_trx_id(+) = app.receivables_trx_id
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ps.org_id = p_org_id
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    app.gl_date <= p_to_date    
      UNION ALL
      SELECT adj.apply_date apply_date                                    -- Q4
      FROM   ar_adjustments_all adj
            ,ar_receivables_trx_all rt
            ,ra_customer_trx_all ct
      WHERE  adj.status NOT IN ('R', 'U')
      AND    adj.receivables_trx_id = rt.receivables_trx_id(+)
      AND    adj.customer_trx_id = ct.customer_trx_id(+)
      AND    ct.org_id = p_org_id
      AND    adj.payment_schedule_id = p_payment_schedule_id
      /*AND    arpt_sql_func_util.get_lookup_meaning ('APPROVAL_TYPE'
                                                   ,adj.status) IS NULL*/
      AND    adj.gl_date <= p_to_date
      UNION ALL
      SELECT app.apply_date apply_date                                         -- Q5
      FROM   ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all rt
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.receivables_trx_id = rt.receivables_trx_id
      AND    app.status = 'ACTIVITY'
      AND    SIGN (app.applied_payment_schedule_id) < 0
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ps.org_id = p_org_id
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT app.apply_date apply_date                         -- Q6
      FROM   ar_payment_schedules_all ps
            ,ar_payment_schedules_all ps_dummy
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all rt
            ,ar_cash_receipt_history_all crh
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.applied_payment_schedule_id = ps_dummy.payment_schedule_id
      AND    ps_dummy.cash_receipt_id = crh.cash_receipt_id
      AND    crh.current_record_flag = 'Y'
      AND    app.receivables_trx_id = rt.receivables_trx_id
      AND    app.status = 'ACTIVITY'
      AND    SIGN (app.applied_payment_schedule_id) > 0
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS'
                                                   ,crh.status) IS NULL
      AND    ps.org_id = p_org_id
      AND    app.gl_date <= p_to_date;
    
  v_apply_date   VARCHAR2(2000) := NULL;
BEGIN
   FOR i IN c1
   LOOP
      v_apply_date := v_apply_date || ',' || to_char(i.apply_date,'DD-MON-YY');
   END LOOP;

   RETURN ltrim(v_apply_date,',');
END;
/