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;

/

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

API to get order line number, line amount and order amount

---------------------------------------------------------------------------------

oe_order_misc_pub.get_concat_line_number(oola.line_id) 

oe_totals_grp.get_order_total(ooha.header_id, oola.line_id, 'ALL')

oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL')

---------------------------------------------------------------------------------

SELECT
    ooha.org_id,
    ooha.header_id,
    ooha.order_number,
    oola.line_id,
    oe_order_misc_pub.get_concat_line_number(oola.line_id) line_number,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, oola.line_id, 'ALL'), 0)                 line_amount,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL'), 0)                         order_amount  
FROM
    oe_order_headers_all  ooha,
    oe_order_lines_all    oola
WHERE
        ooha.header_id = oola.header_id
    AND ooha.org_id = oola.org_id
    AND ooha.order_number = '71000004'
    order by oola.line_id;

query to find processing constraints in Order Management in oracle APPS

SELECT
c.constraint_id,
e.entity_display_name entity,
c.column_display_name attribute,
l1.meaning opeartion,
l2.meaning user_action,
l4.meaning seeded,
cc.group_number,
l3.meaning scope,
cc.validation_entity_display_name val_entity,
cc.record_set_display_name record_set,
decode(
cc.modifier_flag, 'Y', NULL, ' '
) modifier,
cc.validation_tmplt_display_name val_template,
l5.meaning seeded_flag
FROM
oe_pc_constraints_v c,
oe_pc_entities_v e,
oe_pc_constraint_cnds_v cc,
oe_lookups l1,
oe_lookups l2,
oe_lookups l3,
oe_lookups l4,
oe_lookups l5
WHERE
c.entity_id = e.entity_id (+)
AND l1.lookup_code (+) = c.constrained_operation
AND l1.lookup_type (+) = 'PC_OPERATION'
AND l2.lookup_code (+) = c.on_operation_action
AND l2.lookup_type (+) = 'PC_ON_OPERATION_ACTION'
AND l4.lookup_code (+) = c.system_flag
AND l4.lookup_type (+) = 'YES_NO'
AND c.constraint_id = cc.constraint_id (+)
AND l3.lookup_code (+) = cc.scope_op
AND l3.lookup_type (+) = 'PC_SCOPE_OP'
AND l5.lookup_code (+) = cc.system_flag
AND l5.lookup_type (+) = 'YES_NO'
and c.constraint_id =1002
ORDER BY
e.entity_display_name,
nvl(
l1.meaning, 'A'
),
nvl(
c.column_display_name, 'A'
),
cc.group_number

Differentiate ship set and arrival set?

In the ship set process, the user would like to ship group of order lines from same warehouse to same location. If Order Lines are grouped under ship set, then until all lines in the ship set satisfy the demand, you cannot ship the goods to customer.

In the arrival set process, the customer requests specific order lines to arrive together.

Which event will be fire while Booking Sales Orders

When user press the Book button, two predefined event will be triggered.

These event can be captured by using Forms Personalization and CUSTOM.pll.

OM_PRE_BOOK_EVENT: Validation before booking the order.

OM_POST_BOOK_EVENT: some process once order is booked.

What is the importance of Auto Allocate flag

Enable: After picking automatically pick wave move order generated and material will be automatically moved from source sub inventory to the staging area.

Disable: After picking pick wave move order generated but manually move stock from source sub inventory to the staging area.

What is the importance of Auto create delivery flag

Enable: After picking automatically delivery numbers generated.

Disable: After picking manually delivery numbers has to be generated.

query to find between Internal Requisition and internal sales orders

  SELECT   OOH.ORDER_NUMBER SO,

           OOH.ORDERED_DATE,

           OOH.ORG_ID,

           PRH.SEGMENT1 REQ

    FROM   OE_ORDER_HEADERS_ALL OOH,

           PO_REQUISITION_HEADERS_ALL PRH,

           PO_REQUISITION_LINES_ALL PRL

   WHERE   1 = 1

           AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

           --AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF

           AND PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID

           --AND ORDER_NUMBER = '66534' --SALES ORDER NUMBER

           AND PRH.SEGMENT1 = '300059130'-- REQUISITION NUMBER

           AND PRH.TYPE_LOOKUP_CODE = 'INTERNAL'

   ORDER BY   OOH.ORDERED_DATE DESC

Mandatory setups for IR ISO Flow

1.      In item master form internal ordered and internal orders Enabled  flag should be enabled and  assigned to both the orgs

2.      List price required

3.      Shipping networks (specify source and destination organization).

4.      Customer ship to location should be destination org location

5.      In the purchasing options attach Internal Order Transaction type