How to convert comma separated values into rows in pl sql

 WITH data AS (
    SELECT
        :values1 str
    FROM
        dual
)
SELECT
    TRIM(regexp_substr(str, '[^,]+', 1, level)) str
FROM
    data
CONNECT BY
    instr(str, ',', 1, level - 1) > 0;

WITH data AS (

    SELECT

        subject str

    FROM

        dly_rpt_tbl

    WHERE

        id ='1251912748426776107'

)

SELECT

    TRIM(regexp_replace(regexp_substr(str, '[^,]+', 1, level), '[^[:digit:]]', ''))                column1

FROM

    data

CONNECT BY

    instr(str, ',', 1, level - 1) > 0;

Query to ger AR Invoice details

 SELECT
    hou.name,
    rcta.org_id,
    hca.account_number,
    hp.party_name,
    rcta.customer_trx_id,
    rcta.trx_number,
    rcta.trx_date,
    apsa.due_date,
    apsa.invoice_currency_code,
    apsa.amount_due_original,
    apsa.amount_line_items_original,
    apsa.tax_original,
    apsa.class,
    rctt.name,
    rbs.name,
    rcta.interface_header_context,
    rcta.interface_header_attribute1,
    rcta.bill_to_site_use_id,
    hcsua.site_use_id,
    hl.address1,
    hl.address2,
    hl.city,
    hl.state,
    hl.postal_code,
    hl.country,
    ft.territory_short_name
FROM
    ra_customer_trx_all       rcta,
    ar_payment_schedules_all  apsa,
    ra_cust_trx_types_all     rctt,
    ra_batch_sources_all      rbs,
    hr_operating_units        hou,
    hz_cust_accounts          hca,
    hz_parties                hp,
    hz_cust_acct_sites_all    hcasa,
    hz_cust_site_uses_all     hcsua,
    hz_party_sites            hps,
    hz_locations              hl,
    fnd_territories_vl        ft
WHERE
        1 = 1
    AND rcta.org_id = hou.organization_id
    AND apsa.customer_trx_id = rcta.customer_trx_id
    AND apsa.org_id = rcta.org_id
    AND rctt.cust_trx_type_id = rcta.cust_trx_type_id
    AND rctt.org_id = rcta.org_id
    AND rcta.batch_source_id = rbs.batch_source_id
    AND rcta.org_id = rbs.org_id
    AND rcta.bill_to_customer_id = hca.cust_account_id
    AND hca.party_id = hp.party_id
    AND hca.cust_account_id = hcasa.cust_account_id
    AND rcta.bill_to_customer_id = hcasa.cust_account_id
    AND rcta.bill_to_site_use_id = hcsua.site_use_id
    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
    AND hp.party_id = hps.party_id
    AND hcasa.party_site_id = hps.party_site_id
    AND hps.location_id = hl.location_id
    AND hl.country = ft.territory_code
    AND hcsua.site_use_code = 'BILL_TO'
    AND hcasa.status = 'A'
    AND hcsua.status = 'A'
    AND hca.status = 'A'

    AND rcta.trx_number = '1111111111'
    AND rcta.org_id = 111

query to remove junk characters from column

  Select TRANSLATE (trim(column_name), 'x'||CHR(10)||CHR(13)||CHR(9), 'x') from dual;

Query to find customer dunning contact email id

 SELECT
    account_number    account_number,
    obj.party_name    customer_name,
    sub.person_first_name,
    sub.person_last_name,
    sub.party_name    contact_name,
    LISTAGG(hcp.email_address, ',') WITHIN GROUP(
        ORDER BY
            hca.cust_account_id
    )                 emild_list,
    hrr.responsibility_type
FROM
    apps.hz_cust_accounts     hca,
    apps.hz_parties           obj,
    apps.hz_relationships     rel,
    apps.hz_contact_points    hcp,
    apps.hz_parties           sub,
    hz_cust_account_roles     hcar,
    hz_role_responsibility    hrr
WHERE
        hca.party_id = rel.object_id
    AND hca.party_id = obj.party_id
    AND rel.subject_id = sub.party_id
    AND rel.relationship_type = 'CONTACT'
    AND rel.directional_flag = 'F'
    AND rel.party_id = hcp.owner_table_id
   -- and hca.account_number=  '752588'
    AND hcp.owner_table_name = 'HZ_PARTIES'
    AND hcar.cust_account_id = hca.cust_account_id
    AND hcar.party_id = rel.party_id
    AND hrr.cust_account_role_id (+) = hcar.cust_account_role_id
    AND hrr.responsibility_type = 'DUN'
    AND hcp.contact_point_type = 'EMAIL'
GROUP BY
    account_number,
    obj.party_name,
    sub.person_first_name,
    sub.person_last_name,
    sub.party_name,
    hrr.responsibility_type

Query to get the service contract party roles

 SELECT
    okh.id,
    okh.contract_number,
    okh.contract_number_modifier,
    okh.authoring_org_id,
    hp.party_name,
    hp.party_number,
    hca.account_number,
    flv.meaning,
    okr.rle_code
FROM
    okc_k_party_roles_b   okr,
    okc_k_headers_all_b   okh,
    hz_parties            hp,
    hz_cust_accounts_all  hca,
    fnd_lookup_values     flv
WHERE
        okr.chr_id = okh.id
    AND okr.jtot_object1_code = 'OKX_PARTY'
    AND okh.contract_number = '111111111'
    AND okr.object1_id1 = hca.party_id
    AND hp.party_id = hca.party_id
    AND okr.rle_code = flv.lookup_code
    AND flv.lookup_type = 'OKC_ROLE'
    --AND trunc(nvl(flv.end_date_active, sysdate)) >= trunc(sysdate)
    --AND flv.enabled_flag = 'Y'
    AND flv.language = userenv('LANG')
    ;

Query to find list of schedule concurrent program

 SELECT fcr.request_id ,
  fcpt.user_concurrent_program_name
  || NVL2(fcr.description, ' ('
  || fcr.description
  || ')', NULL) conc_prog ,
  fu.user_name requestor ,
  fu.description requested_by ,
  fu.email_address ,
  frt.responsibility_name requested_by_resp ,
  TRIM(fl.meaning) STATUS ,
  fcr.phase_code ,
  fcr.status_code ,
  fcr.argument_text "PARAMETERS" ,
  TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested ,
  TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start ,
  TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time ,
  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN SUBSTR( fu.description , 0 , 40 )
  END last_update_by ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN fcr.last_update_date
  END last_update_date ,
  fcr.increment_dates ,
  CASE
    WHEN fcrc.CLASS_INFO IS NULL
    THEN 'Yes: '
      || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
    ELSE 'n/a'
  END run_once ,
  CASE
    WHEN fcrc.class_type = 'P'
    THEN 'Repeat every '
      || SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ':')           - 1)
      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days')
      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run')
    ELSE 'n/a'
  END set_days_of_week ,
  CASE
    WHEN fcrc.class_type                         = 'S'
    AND instr(SUBSTR(fcrc.class_info, 33),'1',1) > 0
    THEN 'Days of week: '
      || DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun, ')
      || DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ')
      || DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ')
      || DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ')
      || DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ')
      || DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ')
      || DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat ')
    ELSE 'n/a'
  END days_of_week
FROM apps.fnd_concurrent_requests fcr ,
  apps.fnd_user fu ,
  apps.fnd_concurrent_programs fcp ,
  apps.fnd_concurrent_programs_tl fcpt ,
  apps.fnd_printer_styles_tl fpst ,
  apps.fnd_conc_release_classes fcrc ,
  apps.fnd_responsibility_tl frt ,
  apps.fnd_lookups fl
WHERE fcp.application_id       = fcpt.application_id
AND fcr.requested_by           = fu.user_id
AND fcr.concurrent_program_id  = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id  = fcpt.concurrent_program_id
AND fcr.responsibility_id      = frt.responsibility_id
AND fcr.print_style            = fpst.printer_style_name(+)
AND fcr.release_class_id       = fcrc.release_class_id(+)
AND fcr.status_code            = fl.lookup_code
AND fl.lookup_type             = 'CP_STATUS_CODE'
AND fcr.phase_code             = 'P'
AND frt.language               = 'US'
AND fpst.language              = 'US'
AND fcpt.language              = 'US'
and fcpt.user_concurrent_program_name ='Concurrent program name'
ORDER BY Fu.Description,
  Fcr.Requested_Start_Date ASC  
  ;

Query to find receipt number from invoice

 SELECT
    acr.receipt_number,
    acr.receipt_date,
    acr.amount,
    rct.customer_trx_id,
    rct.trx_number,
    rct.trx_date,
    acr.last_update_date,
    rct.last_update_date,
    acr.receipt_method_id
FROM
    ar_receivable_applications_all  ara,
    ar_cash_receipts_all            acr,
    ra_customer_trx_all             rct
WHERE
        1 = 1
    --    ara.status = 'APP'
    AND ara.cash_receipt_id = acr.cash_receipt_id
    AND ara.applied_customer_trx_id = rct.customer_trx_id
    AND rct.org_id = 1111
    AND rct.trx_number IN ( '111111' )
ORDER BY
    rct.last_update_date DESC;