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