Query to find hold and release information for sales orders

SELECT
    hou.name,
    hca.account_number     customer_number,
    hp.party_name          customer_name,
    ooha.header_id,
    ooha.order_number,
    ott.name               order_type,
    ooha.ordered_date,
    lkup3.meaning          order_status,
    hd.name                hold_name,
    lkup1.meaning          hold_type,
    holds.creation_date    hold_applied_date,
    fu.user_name           hold_applied_by,
    hr.creation_date       released_date,
    fur.user_name          released_by,
    lkup2.meaning          release_reason,
    holds.hold_release_id,
    holds.hold_source_id,
    hd.hold_id
FROM
    oe_order_headers_all     ooha,
    hr_organization_units    hou,
    oe_order_holds_all       holds,
    oe_hold_sources_all      hs,
    oe_hold_definitions      hd,
    oe_hold_releases         hr,
    oe_transaction_types_tl  ott,
    oe_lookups               lkup1,
    oe_lookups               lkup2,
    oe_lookups               lkup3,
    hz_cust_site_uses_all    hcsua,
    hz_cust_acct_sites_all   hcasa,
    hz_cust_accounts         hca,
    hz_parties               hp,
    fnd_user                 fu,
    fnd_user                 fur,
    fnd_lookup_values        flv
WHERE
        1 = 1
    AND ooha.header_id = holds.header_id
    AND ooha.org_id = hou.organization_id
    AND holds.hold_source_id = hs.hold_source_id
    AND hd.hold_id = hs.hold_id
    AND holds.hold_release_id = hr.hold_release_id
    AND ooha.order_type_id = ott.transaction_type_id
    AND ooha.order_type_id = flv.lookup_code
    AND flv.lookup_type = 'NUAN_CREDIT_HOLD_RELEASE_OT'
    AND flv.enabled_flag = 'Y'
    AND trunc(nvl(flv.end_date_active, sysdate)) >= trunc(sysdate)
    AND lkup1.lookup_code = hd.type_code
    AND lkup1.lookup_type = 'HOLD_TYPE'
    AND trunc(sysdate) BETWEEN nvl(lkup1.start_date_active, trunc(sysdate)) AND nvl(lkup1.end_date_active, trunc(sysdate))
    AND lkup3.lookup_type = 'FLOW_STATUS'
    AND lkup3.lookup_code = ooha.flow_status_code
    AND trunc(sysdate) BETWEEN nvl(lkup3.start_date_active, trunc(sysdate)) AND nvl(lkup3.end_date_active, trunc(sysdate))
    AND lkup2.lookup_type = 'RELEASE_REASON'
    AND lkup2.lookup_code = hr.release_reason_code
    AND trunc(sysdate) BETWEEN nvl(lkup2.start_date_active, trunc(sysdate)) AND nvl(lkup2.end_date_active, trunc(sysdate))
    AND ooha.invoice_to_org_id = hcsua.site_use_id
    AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.cust_account_id = hca.cust_account_id
    AND hca.party_id = hp.party_id
    AND fu.user_id = holds.created_by
    AND fur.user_id = hr.created_by
    AND hd.name = 'Credit Check Failure'
    AND trunc(ooha.ordered_date) >= '01-JAN-2025'
    AND ooha.order_number IN ( 'order_number')
ORDER BY
    fur.user_name;

Query to find hold and release information of sales order

SELECT

    hou.name,

    ooha.org_id,

    ooha.order_number,

    ott.name                                                                        order_type,

    lkup3.meaning                                                                   order_status,

    hd.name                                                                         hold_name,

    lkup.meaning                                                                    hold_type,

    (

        SELECT

            flv.meaning

        FROM

            fnd_lookup_values flv

        WHERE

                1 = 1

            AND flv.lookup_code = hs.hold_entity_code

            AND flv.lookup_type = 'HOLD_ENTITY_DESC'

            AND flv.language = userenv('LANG')

            AND flv.view_application_id = 660

            AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)

    )                                                                               criteria,

    oe_holds_pvt.entity_id_value(hs.hold_entity_code, hs.hold_entity_id)            value,

    holds.creation_date                                                             hold_applied_date,

    hr.creation_date                                                                released_date,

    lkup2.meaning                                                                   release_reason,

    hr.release_comment

FROM

    oe_order_headers_all     ooha,

    hr_organization_units    hou,

    oe_order_holds_all       holds,

    oe_hold_sources_all      hs,

    oe_hold_definitions      hd,

    oe_hold_releases         hr,

    oe_transaction_types_tl  ott,

    oe_lookups               lkup,

    oe_lookups               lkup2,

    oe_lookups               lkup3

WHERE

        1 = 1

    AND ooha.header_id = holds.header_id

    AND ooha.org_id = hou.organization_id

    AND holds.hold_source_id = hs.hold_source_id

    AND hd.hold_id = hs.hold_id

    AND holds.hold_release_id = hr.hold_release_id(+)

    AND lkup.lookup_code = hd.type_code

    AND ooha.order_type_id = ott.transaction_type_id

    AND lkup.lookup_type = 'HOLD_TYPE'

    AND trunc(sysdate) BETWEEN nvl(lkup.start_date_active, trunc(sysdate)) AND nvl(lkup.end_date_active, trunc(sysdate))

    AND lkup2.lookup_type = 'RELEASE_REASON'

    AND hr.release_reason_code = lkup2.lookup_code (+)

    AND trunc(sysdate) BETWEEN nvl(lkup2.start_date_active, trunc(sysdate)) AND nvl(lkup2.end_date_active, trunc(sysdate))

    AND lkup3.lookup_type = 'FLOW_STATUS'

    AND lkup3.lookup_code = ooha.flow_status_code

    AND trunc(sysdate) BETWEEN nvl(lkup3.start_date_active, trunc(sysdate)) AND nvl(lkup3.end_date_active, trunc(sysdate))

    AND ooha.order_number IN ( '71669760', '71667130' );

Script to Release the Hold from Sales order

SET SERVEROUTPUT ON SIZE 100000;

SET DEFINE OFF;

DECLARE

    l_return_status  VARCHAR2(30);

    l_msg_data       VARCHAR2(4000);

    l_msg_count      NUMBER;

    l_order_tbl      oe_holds_pvt.order_tbl_type;

    l_hold_id        NUMBER := 1;

    l_header_id      NUMBER := 14621798;--14612797;

    l_context        VARCHAR2(2);

BEGIN

    dbms_output.put_line('START');

    mo_global.init('ONT');

    fnd_global.apps_initialize(user_id => 233158, resp_id => 57420, resp_appl_id => 660);

    mo_global.set_policy_context('S', 111);

    l_order_tbl(1).header_id := l_header_id;

    l_return_status := NULL;

    l_msg_data := NULL;

    l_msg_count := NULL;

    dbms_output.put_line('Calling the API to Release hold');

    oe_holds_pub.release_holds(p_api_version => 1.0, p_order_tbl => l_order_tbl,

                              p_hold_id => l_hold_id,

                              p_release_reason_code => 'CREDIT_APPROVED',

                              p_release_comment => 'TEST1',

                              x_return_status => l_return_status,

                              x_msg_count => l_msg_count,

                              x_msg_data => l_msg_data);

    IF l_return_status = fnd_api.g_ret_sts_success THEN

        dbms_output.put_line('success:');

        COMMIT;

    ELSIF l_return_status IS NULL THEN

        dbms_output.put_line('Status is null');

    ELSE

        dbms_output.put_line('Failed: ' || l_msg_data);

        FOR i IN 1..oe_msg_pub.count_msg LOOP

            l_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');

            dbms_output.put_line(i

                                 || ') '

                                 || l_msg_data);

        END LOOP;

        ROLLBACK;

    END IF;

    dbms_output.put_line('END');

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Error is '

                             || sqlcode

                             || '---'

                             || sqlerrm);

END;

/

Script to change the order status to Booked

SET SERVEROUTPUT ON SIZE 100000;

SET DEFINE OFF;

DECLARE

    l_api_version_number          NUMBER := 1;

    l_return_status               VARCHAR2(2000);

    l_msg_count                   NUMBER;

    l_msg_data                    VARCHAR2(2000);

-- IN Variables --

    l_header_rec                  oe_order_pub.header_rec_type;

    l_line_tbl                    oe_order_pub.line_tbl_type;

    l_action_request_tbl          oe_order_pub.request_tbl_type;

    l_line_adj_tbl                oe_order_pub.line_adj_tbl_type;

-- OUT Variables --

    l_header_rec_out              oe_order_pub.header_rec_type;

    l_header_val_rec_out          oe_order_pub.header_val_rec_type;

    l_header_adj_tbl_out          oe_order_pub.header_adj_tbl_type;

    l_header_adj_val_tbl_out      oe_order_pub.header_adj_val_tbl_type;

    l_header_price_att_tbl_out    oe_order_pub.header_price_att_tbl_type;

    l_header_adj_att_tbl_out      oe_order_pub.header_adj_att_tbl_type;

    l_header_adj_assoc_tbl_out    oe_order_pub.header_adj_assoc_tbl_type;

    l_header_scredit_tbl_out      oe_order_pub.header_scredit_tbl_type;

    l_header_scredit_val_tbl_out  oe_order_pub.header_scredit_val_tbl_type;

    l_line_tbl_out                oe_order_pub.line_tbl_type;

    l_line_val_tbl_out            oe_order_pub.line_val_tbl_type;

    l_line_adj_tbl_out            oe_order_pub.line_adj_tbl_type;

    l_line_adj_val_tbl_out        oe_order_pub.line_adj_val_tbl_type;

    l_line_price_att_tbl_out      oe_order_pub.line_price_att_tbl_type;

    l_line_adj_att_tbl_out        oe_order_pub.line_adj_att_tbl_type;

    l_line_adj_assoc_tbl_out      oe_order_pub.line_adj_assoc_tbl_type;

    l_line_scredit_tbl_out        oe_order_pub.line_scredit_tbl_type;

    l_line_scredit_val_tbl_out    oe_order_pub.line_scredit_val_tbl_type;

    l_lot_serial_tbl_out          oe_order_pub.lot_serial_tbl_type;

    l_lot_serial_val_tbl_out      oe_order_pub.lot_serial_val_tbl_type;

    l_action_request_tbl_out      oe_order_pub.request_tbl_type;

BEGIN

    dbms_output.put_line('Starting of script');

-- Setting the Enviroment --

    mo_global.init('ONT');

    fnd_global.apps_initialize(user_id => 233158, resp_id => 57420,

                              resp_appl_id => 660);

    mo_global.set_policy_context('S', 111);

    l_action_request_tbl(1) := oe_order_pub.g_miss_request_rec;

    l_action_request_tbl(1).request_type := oe_globals.g_book_order;

    l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;

    l_action_request_tbl(1).entity_id := 14612797;--header_id

    dbms_output.put_line('Starting of API');

-- Calling the API to to Book an Existing Order --Buy bestselling books online

    oe_order_pub.process_order(p_api_version_number => l_api_version_number, p_header_rec => l_header_rec,

                              p_line_tbl => l_line_tbl,

                              p_action_request_tbl => l_action_request_tbl,

                              p_line_adj_tbl => l_line_adj_tbl

-- OUT variables

                              ,

                              x_header_rec => l_header_rec_out,

                              x_header_val_rec => l_header_val_rec_out,

                              x_header_adj_tbl => l_header_adj_tbl_out,

                              x_header_adj_val_tbl => l_header_adj_val_tbl_out,

                              x_header_price_att_tbl => l_header_price_att_tbl_out,

                              x_header_adj_att_tbl => l_header_adj_att_tbl_out,

                              x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,

                              x_header_scredit_tbl => l_header_scredit_tbl_out,

                              x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,

                              x_line_tbl => l_line_tbl_out,

                              x_line_val_tbl => l_line_val_tbl_out,

                              x_line_adj_tbl => l_line_adj_tbl_out,

                              x_line_adj_val_tbl => l_line_adj_val_tbl_out,

                              x_line_price_att_tbl => l_line_price_att_tbl_out,

                              x_line_adj_att_tbl => l_line_adj_att_tbl_out,

                              x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,

                              x_line_scredit_tbl => l_line_scredit_tbl_out,

                              x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,

                              x_lot_serial_tbl => l_lot_serial_tbl_out,

                              x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,

                              x_action_request_tbl => l_action_request_tbl_out,

                              x_return_status => l_return_status,

                              x_msg_count => l_msg_count,

                              x_msg_data => l_msg_data);

    dbms_output.put_line('Completion of API');

    IF l_return_status = fnd_api.g_ret_sts_success THEN

        COMMIT;

        dbms_output.put_line('Booking of an Existing Order is Success ');

    ELSE

        dbms_output.put_line('Booking of an Existing Order failed:' || l_msg_data);

        ROLLBACK;

        FOR i IN 1..l_msg_count LOOP

            l_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');

            dbms_output.put_line(i

                                 || ') '

                                 || l_msg_data);

        END LOOP;

    END IF;

END;

/

How to add First 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: ^
5. Replace with: ,
6. Click "Replace All".

Script to update the customer profile (hz_customer_profile_v2pub.update_customer_profile)

SET SERVEROUTPUT ON;
DECLARE
    p_customer_profile_rec_type  hz_customer_profile_v2pub.customer_profile_rec_type;
    p_cust_account_profile_id    NUMBER;
    p_object_version_number      NUMBER;
    x_return_status              VARCHAR2(2000);
    x_msg_count                  NUMBER;
    x_msg_data                   VARCHAR2(2000);
BEGIN
    dbms_output.put_line('Start of API');
    mo_global.init('AR');
    fnd_global.apps_initialize(user_id => 233158,     --fnd_user.user_id
                               resp_id => 58900,         --US Receivables Manager
                               resp_appl_id => 222);
    mo_global.set_policy_context('S', 101);
    fnd_global.set_nls_context('AMERICAN');
    
-- Initializing the Mandatory API parameters
    p_customer_profile_rec_type.cust_account_profile_id := 2097166;             --HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_PROFILE_ID
    p_customer_profile_rec_type.statement_cycle_id := fnd_api.g_miss_num;       --AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
    p_customer_profile_rec_type.send_statements := 'N';
    p_customer_profile_rec_type.dunning_letter_set_id := fnd_api.g_miss_num;    --AR_DUNNING_LETTER_SETS.DUNNING_LETTER_SET_ID   
    p_customer_profile_rec_type.dunning_letters := 'N';
    p_customer_profile_rec_type.credit_balance_statements := 'N';
    p_customer_profile_rec_type.credit_checking := 'N';
    p_customer_profile_rec_type.credit_hold := 'N';
    SELECT
        object_version_number
    INTO p_object_version_number
    FROM
        hz_customer_profiles
    WHERE
        cust_account_profile_id = 2097166;
    dbms_output.put_line('Calling the API hz_customer_profile_v2pub.update_customer_profile');
    hz_customer_profile_v2pub.update_customer_profile(p_init_msg_list => fnd_api.g_true,
                                                     p_customer_profile_rec => p_customer_profile_rec_type,
                                                     p_object_version_number => p_object_version_number,
                                                     x_return_status => x_return_status,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data => x_msg_data);
    IF x_return_status = fnd_api.g_ret_sts_success THEN
        COMMIT;
        dbms_output.put_line('Updation of Customer Profile is Successful ');
    ELSE
        dbms_output.put_line('Updation of Customer Profile got failed:' || x_msg_data);
        ROLLBACK;
        FOR i IN 1..x_msg_count LOOP
            x_msg_data := fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F');
            dbms_output.put_line(i
                                 || ') '
                                 || x_msg_data);
        END LOOP;
    END IF;
    dbms_output.put_line('End of API');
END;
/

Query to find customer number and name from project

 SELECT
    ppa.project_id,
    ppa.segment1,
    ppa.name,
    hp.party_name,
    hca.account_number,
    hcp.credit_checking,
    hcp.credit_hold
FROM
    pa_projects_all         ppa,
    pa_project_customers    ppc,
    hz_cust_accounts        hca,
    hz_customer_profiles    hcp,
    hz_cust_acct_sites_all  hcasa,
    hz_parties              hp
WHERE
        1 = 1
    AND ppa.project_id = ppc.project_id
    AND ppc.customer_id = hca.cust_account_id
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND ppc.bill_to_address_id = hcasa.cust_acct_site_id
    AND hcp.site_use_id IS NULL
    AND ppa.segment1 = '1111111';

Query to find customer information from PA Project

  SELECT
    ppa.project_id,
    ppa.segment1,
    ppa.name,
    hp.party_name,
    hca.account_number,
    hcp.credit_checking,
    hcp.credit_hold,
    hl_ship.address1               ship_address1,
    hl_ship.address2               ship_address2,
    hl_ship.address3               ship_address3,
    hl_ship.address4               ship_address4,
    hl_ship.postal_code          ship_postal_code,
    hl_ship.city                       ship_city,
    hl_ship.state                      ship_state,
    hl_ship.province               ship_province,
    hl_ship.county                  ship_county,
    hl_bill.address1                bill_address1,
    hl_bill.address2                bill_address2,
    hl_bill.address3                bill_address3,
    hl_bill.address4                bill_address4,
    hl_bill.postal_code           bill_postal_code,
    hl_bill.city                        bill_city,
    hl_bill.state                       bill_state,
    hl_bill.province                bill_province,
    hl_bill.county                   bill_county
FROM
    pa_projects_all                ppa,
    pa_project_customers     ppc,
    hz_cust_accounts            hca,
    hz_customer_profiles      hcp,
    hz_cust_acct_sites_all     hcasa_bill,
    hz_party_sites                  hps_bill,
    hz_locations                     hl_bill,
    hz_cust_acct_sites_all     hcasa_ship,
    hz_party_sites                  hps_ship,
    hz_locations                     hl_ship,
    hz_parties                         hp
WHERE
        1 = 1
    AND ppa.project_id = ppc.project_id
    AND ppc.customer_id = hca.cust_account_id
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND ppc.bill_to_address_id = hcasa_bill.cust_acct_site_id
    AND hcasa_bill.party_site_id = hps_bill.party_site_id
    AND hps_bill.location_id = hl_bill.location_id
    AND ppc.ship_to_address_id = hcasa_ship.cust_acct_site_id
    AND hcasa_ship.party_site_id = hps_ship.party_site_id
    AND hps_ship.location_id = hl_ship.location_id
    AND hcp.site_use_id IS NULL
    AND ppa.segment1 = '1111111';

How to add 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: $ 
5. Replace with: ,
6. Click "Replace All".

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;

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')
    ;