Query to find invoice number from credit memo

 SELECT
    rcta.customer_trx_id,
    rcta.trx_number,
    rcta.trx_date,
    rctta.name          transaction_type_name,
    rctta.type          transaction_type,
    rcta.previous_customer_trx_id,
    apsa.payment_schedule_id,
    apsa.class,
    araa.applied_customer_trx_id,
    araa.applied_payment_schedule_id,
    rctai.trx_number    invoice_number
FROM
    ra_customer_trx_all             rcta,
    ra_cust_trx_types_all           rctta,
    ar_payment_schedules_all        apsa,
    ar_receivable_applications_all  araa,
    ar_payment_schedules_all        apsai,
    ra_customer_trx_all             rctai
WHERE
        1 = 1
    AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
    AND rcta.org_id = rctta.org_id
    AND rcta.customer_trx_id = apsa.customer_trx_id
    AND rcta.org_id = apsa.org_id
    AND rcta.customer_trx_id = araa.customer_trx_id
    AND rcta.org_id = araa.org_id
    AND apsa.payment_schedule_id = araa.payment_schedule_id
    AND apsa.org_id = araa.org_id
    AND araa.applied_customer_trx_id = apsai.customer_trx_id
    AND araa.applied_payment_schedule_id = apsai.payment_schedule_id
    AND araa.org_id = apsai.org_id
    AND araa.applied_customer_trx_id = rctai.customer_trx_id
    AND araa.org_id = rctai.org_id
    AND rcta.trx_number = '111111111'
    AND apsa.class = 'CM';

Query to find order line number from sales order

 SELECT
    ooha.org_id,
    ooha.order_number,
    rtrim(oola.line_number|| '.'|| oola.shipment_number|| '.'|| oola.option_number|| '.'|| oola.component_number|| '.'|| oola.service_number,'.') line_number
FROM
    oe_order_headers_all  ooha,
    oe_order_lines_all    oola
WHERE
        1 = 1
    AND ooha.header_id = oola.header_id
    AND ooha.org_id = oola.org_id
    AND ooha.order_number = '11111111';

Query to find receipt number from Invoice number (link between ra_customer_trx_all and r_cash_receipts_all)

 SELECT
    acra.receipt_number,
    rcta.trx_number
FROM
    ar_receivable_applications_all  araa,
    ar_cash_receipts_all            acra,
    ra_customer_trx_all             rcta
WHERE
        araa.status = 'APP'
    AND araa.cash_receipt_id = acra.cash_receipt_id
    AND araa.applied_customer_trx_id = rcta.customer_trx_id
    --AND rct.customer_trx_id IN (1111111 )
    AND rcta.trx_number = '1111111'

;

Query to find customer number, site number, transaction type and other details from AR Invoice

 SELECT

    hou.name              ou_name,

    rcta.org_id,

    hp.party_id,

    --hp.party_number,

    hp.party_name,

    hp.party_type,

    hca.account_number    customer_number,

    hps.party_site_number,

    rcta.customer_trx_id,

    rcta.trx_number,

    rcta.trx_date,

    apsa.class,

    rctta.name            transaction_type_name,

    rctta.type            transaction_type,

    apsa.status,

    rcta.printing_original_date,

    rcta.printing_last_printed,

    rcta.printing_count,

    rcta.last_printed_sequence_num,

    rcta.creation_date,

    rcta.last_update_date,

    rcta.printing_pending,

    rcta.complete_flag,

    apsa.amount_due_original,

    apsa.amount_due_remaining,

    apsa.invoice_currency_code,

    hca.cust_account_id,

    rcta.bill_to_site_use_id

   -- apsa.*

FROM

    ra_customer_trx_all       rcta,

    ar_payment_schedules_all  apsa,

    ra_cust_trx_types_all     rctta,

    hz_parties                hp,

    hz_cust_accounts          hca,

    hz_cust_acct_sites_all    hcasa,

    hz_cust_site_uses_all     hcsua,

    hz_party_sites            hps,

    hr_operating_units        hou

WHERE

        1 = 1

--AND trx_number IN ( '50833855' )

    AND rcta.org_id = hou.organization_id

    AND apsa.customer_trx_id = rcta.customer_trx_id

    AND rcta.cust_trx_type_id = rctta.cust_trx_type_id

    AND rcta.org_id = rctta.org_id

    AND rcta.bill_to_customer_id = hca.cust_account_id

    AND rcta.bill_to_customer_id = hcasa.cust_account_id

    AND hca.cust_account_id = hcasa.cust_account_id

    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

    AND rcta.bill_to_site_use_id = hcsua.site_use_id

    AND hcasa.party_site_id = hps.party_site_id

    AND hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hcsua.site_use_code = 'BILL_TO'

ORDER BY

rcta.org_id,

    rcta.creation_date DESC

How to remove last character in notepad ++

1. Open file in Notepad++

2. Ctrl + H to open the Find and Replace dialog.

3. select Regular expression in Search Mode at the bottom.

4. Find what: .$ (if any specific character like , then use ,$)

5. Replace with: (leave this blank).

6. Click "Replace All".

Query to find invoice transaction type, class and status

 SELECT
    rcta.customer_trx_id,
    rcta.trx_number,
    rcta.trx_date,
    apsa.class,
    apsa.status,
    rctta.name    transaction_type_name,
    rctta.type    transaction_type
FROM
    ra_customer_trx_all       rcta,
    ar_payment_schedules_all  apsa,
    ra_cust_trx_types_all     rctta
WHERE
        1 = 1
    AND apsa.customer_trx_id = rcta.customer_trx_id
    AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
    AND rcta.org_id = rctta.org_id
    AND rcta.trx_number IN ( '33333333')
    AND rcta.org_id = 111
ORDER BY
    rcta.creation_date DESC;

Query to find out schedule request set

 SELECT
    fcr.request_id,
    fcr.description,
    fcpt.user_concurrent_program_name
    || nvl2(fcr.description, ' ('
                             || fcr.description
                             || ')', NULL)                                                      conc_prog,
    fcr.requested_start_date,
    to_char(actual_start_date, 'DD-MON-RRRR HH24:MI:SS'),
    to_char(actual_completion_date, 'DD-MON-RRRR HH24:MI:SS'),
    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,
    logfile_name,
    outfile_name
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 upper(fcr.description) LIKE '%REQUESTSET%NAME%'
ORDER BY
    fu.description,
    fcr.requested_start_date DESC;