Oracle Fusion: Universal Content Management (UCM)

Oracle Fusion Universal Content Management (UCM) Overview:

Oracle Fusion: Universal Content Management (UCM)

Realtime Demo:

Oracle Fusion: Universal Content Management (UCM): Realtime Demo

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