Slipt single line to Multiple lines

user615979

Hi All

  Working EBS Version 12.1.3

please see below query using as function

there are multiple receipts for one invoice

output displaying as 8924, 8923

i want data output should be as

'Paid Receipt '8924  no of days Over due days

'Paid Receipt '8923  no of days Over due days

Please i need help

SELECT listagg(c.receipt_number, ', ') within group (order by c.receipt_number)  into p_receipt_num

  FROM ar_payment_schedules_all pay,

       ar_receivable_applications_all b,

       ar_cash_receipts_all c,

       xxink_ra_customers d,

       hz_parties adr,

       ar_customer_profiles_v col

WHERE c.pay_from_customer           = d.customer_id

   AND c.cash_receipt_id             = b.cash_receipt_id

   AND d.party_id                    = adr.party_id

   AND d.customer_name             >= NVL (p_cus_name_from, d.customer_name)

   AND d.customer_name            <= NVL (p_cus_name_to, d.customer_name)

--   AND col.collector_name            = NVL (:p_collector, col.collector_name)

--   AND NVL(d.customer_class_code, 'A') = NVL (:p_class, NVL (d.customer_class_code, 'A'))

   AND d.customer_id                 = col.customer_id

   AND b.applied_payment_schedule_id = pay.payment_schedule_id

   AND pay.org_id                    = c.org_id

   AND pay.class                    <> 'PMT'

   AND c.status                     <> 'REV'

   AND b.status                      = 'APP'

   AND pay.customer_id               = d.customer_id

   AND TRUNC (c.receipt_date)       <= TRUNC (p_trx_date2)

    and nvl(DISPLAY,'N') = 'Y'

--   AND TRUNC (b.apply_date)          > TRUNC (:date1)

   AND pay.trx_number = TRX_NUM;

Regards,

cormaco

Do you mean something like this:

with ar_cash_receipts_all(receipt_number) as (    select 8924 from dual union all    select 8923 from dual)select listagg('Paid Receipt ' ||c.receipt_number || ' no of days Over due days', chr(10)) within group (order by c.receipt_number) as textfrom ar_cash_receipts_all cTEXT                                              --------------------------------------------------Paid Receipt 8923 no of days Over due daysPaid Receipt 8924 no of days Over due days

If your client system is Windows use chr(13)||chr(10) instead.

cormaco

Do you mean something like this:

with ar_cash_receipts_all(receipt_number) as (    select 8924 from dual union all    select 8923 from dual)select listagg('Paid Receipt ' ||c.receipt_number || ' no of days Over due days', chr(10)) within group (order by c.receipt_number) as textfrom ar_cash_receipts_all cTEXT                                              --------------------------------------------------Paid Receipt 8923 no of days Over due daysPaid Receipt 8924 no of days Over due days

If your client system is Windows use chr(13)||chr(10) instead.

user615979

Thank You Very Much