Forum Stats

  • 3,815,605 Users
  • 2,259,059 Discussions
  • 7,893,185 Comments

Discussions

Slipt single line to Multiple lines

user615979
user615979 Member Posts: 343 Blue Ribbon
edited Dec 4, 2019 7:57AM in Reports

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,

Best Answer

  • cormaco
    cormaco Member Posts: 1,904 Silver Crown
    edited Dec 4, 2019 3:57AM Answer ✓

    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.

Answers

  • cormaco
    cormaco Member Posts: 1,904 Silver Crown
    edited Dec 4, 2019 3:57AM Answer ✓

    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
    user615979 Member Posts: 343 Blue Ribbon
    edited Dec 4, 2019 7:57AM

    Thank You Very Much