Oracle Fusion Application Composer: How to find base tables for Custom Objects

Subject: How to get a fusion base table for custom objects.

(To Learn More About Application Composer Visit Oracle Fusion: Application Composer)

There is 2 way to find custom object base tables.

1) Using Backend SQL Query Approach:

/*--Need to pass custom object api name--*/ 

/*--Get Table ID--*/

select TABLE_ID

from fusion.adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%customobject_api_name%';

/*--Need to pass custom Table Id from the above query--*/ 

/*--Get Table Name--*/

select TABLE_NAME 

from fusion.adf_extensible_table where TABLE_ID = P_TABLE_ID;

/*--Combining above 2 query--*/ 

/*--Need to pass custom object api name only--*/ 

/*--Get Table Name--*/

select TABLE_NAME 

from fusion.adf_extensible_table 

where TABLE_ID IN (select TABLE_ID

from fusion.adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%PO_REF%');

2) Using Metadata Manager Approach:

Navigation: 

Application Composer > Metadata Manager > Click on Generate > After Successfully Run > Click on Export.

Open the excel sheet. It is having all the custom object summaries.

Click on any custom object it will open the detailed mapping for that custom object. Let's click on the Bank_c custom object.

It's having all the custom object fields with base table mapping.

Hereby, we covered both approach to find custom objects and their fields mapping.

Oracle EBS Function: Get Inventory Item On-Hand Quantity

CREATE OR REPLACE FUNCTION xx_onhand_qty (

    p_inv_item_id  IN  VARCHAR2,

    p_org_id       IN  NUMBER,

    p_sub_inv      IN  VARCHAR2,

    p_loc_id       IN  NUMBER

) RETURN NUMBER IS

    x_return_status        VARCHAR2(50);

    x_msg_count            VARCHAR2(50);

    x_msg_data             VARCHAR2(50);

    v_item_id              NUMBER;

    v_organization_id      NUMBER;

    v_qoh                  NUMBER;

    v_rqoh                 NUMBER;

    v_atr                  NUMBER;

    v_att                  NUMBER;

    v_qr                   NUMBER;

    v_qs                   NUMBER;

    v_lot_control_code     BOOLEAN;

    v_serial_control_code  BOOLEAN;

    l_qty                  NUMBER;

    l_qty_avai_reserve     NUMBER := 0;

    l_qty_avai_transact    NUMBER := 0;

    l_reserved_qty         NUMBER := 0;

BEGIN

    SELECT

        inventory_item_id,

        mp.organization_id

    INTO

        v_item_id,

        v_organization_id

    FROM

        mtl_system_items_b  msib,

        mtl_parameters      mp

    WHERE

            msib.inventory_item_id = p_inv_item_id

        AND msib.organization_id = mp.organization_id

        AND msib.organization_id = p_org_id; -- :organization_code;

    BEGIN

        SELECT

            nvl(SUM(reservation_quantity), 0)

        INTO l_reserved_qty

        FROM

            mtl_reservations

        WHERE

                inventory_item_id = p_inv_item_id

            AND organization_id = p_org_id

            AND subinventory_code = p_sub_inv

            AND locator_id = p_loc_id;

    EXCEPTION

        WHEN OTHERS THEN

            l_reserved_qty := 0;

            dbms_output.put_line('ERROR: ' || sqlerrm);

    END;

    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := false;

    v_serial_control_code := false;

    fnd_client_info.set_org_context(1);

    inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, p_init_msg_lst => 'F',

                                          x_return_status => x_return_status,

                                          x_msg_count => x_msg_count,

                                          x_msg_data => x_msg_data,

                                          p_organization_id => v_organization_id,

                                          p_inventory_item_id => v_item_id,

                                          p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,

                                          p_is_revision_control => false,

                                          p_is_lot_control => v_lot_control_code,

                                          p_is_serial_control => v_serial_control_code,

                                          p_revision => NULL,                          -- p_revision,

                                          p_lot_number => NULL,                        -- p_lot_number,

                                          p_lot_expiration_date => sysdate,

                                          p_subinventory_code => nvl(p_sub_inv, NULL),                 -- p_subinventory_code,

                                          p_locator_id => nvl(p_loc_id, NULL),                        -- p_locator_id,

                                          p_onhand_source => 3,

                                          x_qoh => v_qoh,                    -- Quantity on-hand

                                          x_rqoh => v_rqoh,         --reservable quantity on-hand

                                          x_qr => v_qr,

                                          x_qs => v_qs,

                                          x_att => v_att,               -- available to transact

                                          x_atr => v_atr                 -- available to reserve

                                          );

    l_qty := nvl(v_qoh, 0); --On Hand qty

    l_qty_avai_reserve := nvl(v_atr, 0); --Available to Reserve

    l_qty_avai_transact := nvl(v_att, 0); --Available to Transact

    RETURN nvl((l_qty_avai_transact - l_reserved_qty), 0);

    dbms_output.put_line('On-Hand Quantity: ' || v_qoh);

    dbms_output.put_line('Available to reserve: ' || v_atr);

    dbms_output.put_line('Quantity Reserved: ' || v_qr);

    dbms_output.put_line('Quantity Suggested: ' || v_qs);

    dbms_output.put_line('Available to Transact: ' || v_att);

EXCEPTION

    WHEN OTHERS THEN

        RETURN NULL;

        dbms_output.put_line('ERROR: ' || sqlerrm);

END xx_onhand_qty; 

/

--Testing Script: get onhand qty--

select XX_ONHAND_QTY(8334013,85,'ZONEA',147167) from dual;  --Item_id,Organization_id,Sub_inv,Locator_id

Oracle EBS - Procedure to create duplicate sales order based on existing sales order using Oracle API.

Requirement: We need to create duplicate Sales Order Based on Existing Sales Order using Oracle API.

CREATE OR REPLACE PROCEDURE xx_create_so_prc (

    p_order_num IN NUMBER

) AS

    l_return_status               VARCHAR2(1000);

    l_msg_count                   NUMBER;

    l_msg_data                    VARCHAR2(1000);

    p_api_version_number          NUMBER := 1.0;

    p_init_msg_list               VARCHAR2(10) := fnd_api.g_false;

    p_return_values               VARCHAR2(10) := fnd_api.g_false;

    p_action_commit               VARCHAR2(10) := fnd_api.g_false;

    x_return_status               VARCHAR2(1);

    x_msg_count                   NUMBER;

    x_msg_data                    VARCHAR2(100);

    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_header_adj_tbl              oe_order_pub.header_adj_tbl_type;

    l_line_adj_tbl                oe_order_pub.line_adj_tbl_type;

    l_header_scr_tbl              oe_order_pub.header_scredit_tbl_type;

    l_line_scredit_tbl            oe_order_pub.line_scredit_tbl_type;

    l_request_rec                 oe_order_pub.request_rec_type;

    x_header_rec                  oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

    p_old_header_rec              oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

    p_header_val_rec              oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

    p_old_header_val_rec          oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

    p_header_adj_tbl              oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

    p_old_header_adj_tbl          oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

    p_header_adj_val_tbl          oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

    p_old_header_adj_val_tbl      oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

    p_header_price_att_tbl        oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

    p_old_header_price_att_tbl    oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

    p_header_adj_att_tbl          oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

    p_old_header_adj_att_tbl      oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

    p_header_adj_assoc_tbl        oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

    p_old_header_adj_assoc_tbl    oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

    p_header_scredit_tbl          oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

    p_old_header_scredit_tbl      oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

    p_header_scredit_val_tbl      oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

    p_old_header_scredit_val_tbl  oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

    x_line_val_tbl                oe_order_pub.line_val_tbl_type;

    x_line_adj_tbl                oe_order_pub.line_adj_tbl_type;

    x_line_adj_val_tbl            oe_order_pub.line_adj_val_tbl_type;

    x_line_price_att_tbl          oe_order_pub.line_price_att_tbl_type;

    x_line_adj_att_tbl            oe_order_pub.line_adj_att_tbl_type;

    x_line_adj_assoc_tbl          oe_order_pub.line_adj_assoc_tbl_type;

    x_line_scredit_tbl            oe_order_pub.line_scredit_tbl_type;

    x_line_scredit_val_tbl        oe_order_pub.line_scredit_val_tbl_type;

    x_lot_serial_tbl              oe_order_pub.lot_serial_tbl_type;

    x_lot_serial_val_tbl          oe_order_pub.lot_serial_val_tbl_type;

    x_action_request_tbl          oe_order_pub.request_tbl_type;

    p_line_tbl                    oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

    p_old_line_tbl                oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

    p_line_val_tbl                oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

    p_old_line_val_tbl            oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

    p_line_adj_tbl                oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

    p_old_line_adj_tbl            oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

    p_line_adj_val_tbl            oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

    p_old_line_adj_val_tbl        oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

    p_line_price_att_tbl          oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

    p_old_line_price_att_tbl      oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

    p_line_adj_att_tbl            oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

    p_old_line_adj_att_tbl        oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

    p_line_adj_assoc_tbl          oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

    p_old_line_adj_assoc_tbl      oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

    p_line_scredit_tbl            oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

    p_old_line_scredit_tbl        oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

    p_line_scredit_val_tbl        oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

    p_old_line_scredit_val_tbl    oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

    p_lot_serial_tbl              oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

    p_old_lot_serial_tbl          oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

    p_lot_serial_val_tbl          oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

    p_old_lot_serial_val_tbl      oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

    p_action_request_tbl          oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;

    x_header_val_rec              oe_order_pub.header_val_rec_type;

    x_header_adj_tbl              oe_order_pub.header_adj_tbl_type;

    x_header_adj_val_tbl          oe_order_pub.header_adj_val_tbl_type;

    x_header_price_att_tbl        oe_order_pub.header_price_att_tbl_type;

    x_header_adj_att_tbl          oe_order_pub.header_adj_att_tbl_type;

    x_header_adj_assoc_tbl        oe_order_pub.header_adj_assoc_tbl_type;

    x_header_scredit_tbl          oe_order_pub.header_scredit_tbl_type;

    x_header_scredit_val_tbl      oe_order_pub.header_scredit_val_tbl_type;

    x_debug_file                  VARCHAR2(100);

    l_line_tbl_index              NUMBER;

    l_msg_index_out               NUMBER(10);

    CURSOR c_hdr IS

    SELECT

        transactional_curr_code,

        sold_to_org_id,

        price_list_id,

        sold_from_org_id,

        salesrep_id,

        order_type_id

    FROM

        oe_order_headers_all

    WHERE

        order_number = p_order_num--1219754

        ;

    CURSOR c_line IS

    SELECT

        line_number,

        ordered_quantity,

        ship_from_org_id,

        inventory_item_id,

        schedule_ship_date

    FROM

        oe_order_lines_all

    WHERE

        header_id = (

            SELECT

                header_id

            FROM

                oe_order_headers_all

            WHERE

                order_number = p_order_num

        )--28755419

    ORDER BY

        line_number;

BEGIN

    fnd_global.apps_initialize(user_id => 26406, resp_id => 21623, resp_appl_id => 660);

    mo_global.init('ONT');

    mo_global.set_policy_context('S', 102);

    oe_msg_pub.initialize;

    oe_debug_pub.initialize;

    x_debug_file := oe_debug_pub.set_debug_mode('FILE');

    oe_debug_pub.setdebuglevel(5);

    FOR i IN c_hdr LOOP

        l_header_rec := oe_order_pub.g_miss_header_rec;

        l_header_rec.operation := oe_globals.g_opr_create;

        l_header_rec.transactional_curr_code := i.transactional_curr_code;

        l_header_rec.pricing_date := sysdate;

        l_header_rec.sold_to_org_id := i.sold_to_org_id;

        l_header_rec.price_list_id := i.price_list_id;

        l_header_rec.ordered_date := sysdate;

        l_header_rec.sold_from_org_id := i.sold_from_org_id;

        l_header_rec.salesrep_id := i.salesrep_id;

        l_header_rec.order_type_id := i.order_type_id;

        FOR j IN c_line LOOP

            l_line_tbl_index := 1;

            l_line_tbl(j.line_number) := oe_order_pub.g_miss_line_rec;

            l_line_tbl(j.line_number).operation := oe_globals.g_opr_create;

            l_line_tbl(j.line_number).ordered_quantity := j.ordered_quantity;

            l_line_tbl(j.line_number).ship_from_org_id := j.ship_from_org_id;

            l_line_tbl(j.line_number).inventory_item_id := j.inventory_item_id;

            l_line_tbl(j.line_number).schedule_ship_date := j.schedule_ship_date;

        END LOOP;

    END LOOP;

    oe_order_pub.process_order(p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_false,

                              p_return_values => fnd_api.g_false,

                              p_action_commit => fnd_api.g_false,

                              x_return_status => l_return_status,

                              x_msg_count => l_msg_count,

                              x_msg_data => l_msg_data,

                              p_header_rec => l_header_rec,

                              p_line_tbl => l_line_tbl,

                              p_action_request_tbl => l_action_request_tbl

                                -- OUT PARAMETERS

                              ,

                              x_header_rec => x_header_rec,

                              x_header_val_rec => x_header_val_rec,

                              x_header_adj_tbl => x_header_adj_tbl,

                              x_header_adj_val_tbl => x_header_adj_val_tbl,

                              x_header_price_att_tbl => x_header_price_att_tbl,

                              x_header_adj_att_tbl => x_header_adj_att_tbl,

                              x_header_adj_assoc_tbl => x_header_adj_assoc_tbl,

                              x_header_scredit_tbl => x_header_scredit_tbl,

                              x_header_scredit_val_tbl => x_header_scredit_val_tbl,

                              x_line_tbl => p_line_tbl,

                              x_line_val_tbl => x_line_val_tbl,

                              x_line_adj_tbl => x_line_adj_tbl,

                              x_line_adj_val_tbl => x_line_adj_val_tbl,

                              x_line_price_att_tbl => x_line_price_att_tbl,

                              x_line_adj_att_tbl => x_line_adj_att_tbl,

                              x_line_adj_assoc_tbl => x_line_adj_assoc_tbl,

                              x_line_scredit_tbl => x_line_scredit_tbl,

                              x_line_scredit_val_tbl => x_line_scredit_val_tbl,

                              x_lot_serial_tbl => x_lot_serial_tbl,

                              x_lot_serial_val_tbl => x_lot_serial_val_tbl,

                              x_action_request_tbl => l_action_request_tbl);

    dbms_output.put_line('Order Header_ID : ' || x_header_rec.header_id);

    FOR i IN 1..l_msg_count LOOP

        oe_msg_pub.get(p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_msg_data, p_msg_index_out => l_msg_index_out);

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

        dbms_output.put_line('message index : ' || l_msg_index_out);

    END LOOP;

   -- Check the return status

    IF l_return_status = fnd_api.g_ret_sts_success THEN

        dbms_output.put_line('Order Created Successfull');

    ELSE

        dbms_output.put_line('Orcer Creation Failed');

    END IF;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Error: ' || sqlerrm);

END xx_create_so_prc;

/

--Testing Scripts:--

--Need to pass existing order number based on what we need to create a new order--

exec XX_CREATE_SO_PRC(1219754);  

--Get the latest created Sales Order Number--

select *

from(

select HEADER_ID,ORDER_NUMBER from 

oe_order_headers_all

where trunc(creation_date) =trunc(sysdate)

order by creation_date desc)

where rownum=1

;