Function to get the Transaction Number

CREATE OR REPLACE FUNCTION xxxxxx_get_trx_number (
   p_payment_schedule_id   IN   NUMBER
  ,
   --p_inv_amt           IN   NUMBER,
   p_org_id                IN   NUMBER
  ,p_from_date             IN   DATE
  ,p_to_date               IN   DATE
)
   RETURN VARCHAR2
IS
   CURSOR c1
   IS
      SELECT ps.trx_number trx_rec_number                                -- 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 ps.trx_number trx_rec_number                              -- 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 DECODE (SIGN (ps.payment_schedule_id)
                    ,-1, arpt_sql_func_util.get_lookup_meaning
                                                              ('PAYMENT_TYPE'
                                                              ,app.status)
                    ,ps.trx_number
                    ) trx_rec_number                                     -- Q2
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all rtl
            ,ra_cust_trx_types_all ctt
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
      WHERE  app.applied_payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_line_id = rtl.customer_trx_line_id(+)
      AND    app.status NOT IN ('ACC', 'ACTIVITY', 'OTHER ACC')
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ct.org_id = p_org_id
      AND    app.gl_date <= p_to_date
      UNION ALL*/
      SELECT DECODE (SIGN (ps.payment_schedule_id)
                    ,-1, arpt_sql_func_util.get_lookup_meaning
                                                              ('PAYMENT_TYPE'
                                                              ,app.status)
                    ,NULL
                    ) trx_rec_number                                     -- 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 DECODE (adj.receivables_trx_id
                    ,-15, (SELECT trx.trx_number
                           FROM   ra_customer_trx trx
                                 ,ra_customer_trx_lines lines
                           WHERE  trx.customer_trx_id = lines.customer_trx_id
                           AND    lines.br_ref_customer_trx_id =
                                                            ct.customer_trx_id
                           AND    lines.br_adjustment_id = adj.adjustment_id)
                    ,adj.adjustment_number
                    ) trx_rec_number                                     -- 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 NULL trx_rec_number                                         -- 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 ps_dummy.trx_number trx_rec_number                          -- 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_trx_num   VARCHAR2 (2000) := NULL;
BEGIN
   FOR i IN c1
   LOOP
      v_trx_num := v_trx_num || ', ' || i.trx_rec_number;
   END LOOP;

   RETURN LTRIM (v_trx_num, ', ');
END;
/