Oracle EBS - Create Miscellaneous Issue Transaction PLSQL Script

 --SET SERVEROUTPUT ON

DECLARE

    x_return_status  VARCHAR2(1000);

    x_msg_count      NUMBER;

    x_msg_data       VARCHAR2(1000);

    l_segment1       VARCHAR2(150);

    l_segment2       VARCHAR2(150);

    l_segment3       VARCHAR2(150);

    l_segment4       VARCHAR2(150);

    l_segment5       VARCHAR2(150);

    l_segment6       VARCHAR2(150);

    l_segment7       VARCHAR2(150);

    l_segment8       VARCHAR2(150);

    CURSOR main_cur IS

    SELECT

        *

    FROM

        xxtlm.xxtlm_item_dtls_mi

    WHERE

            1 = 1

--        AND item_code IN  ('CBB050E')

        AND organization_id IN ( 85 --INA 

        , 97  --CA1

         )

        AND nvl(status_flag, 'N') <> 'S';

BEGIN

    dbms_output.enable(100000000);

    FOR rec IN main_cur LOOP

        BEGIN

            

            l_segment1 := '10';

            l_segment2 := '00';

            l_segment3 := CASE WHEN rec.is_consigned = 1 THEN '3205' ELSE '6060' END;

            l_segment4 := '10';

            l_segment5 := '01';

            l_segment6 := CASE WHEN rec.planning_organization_id = 85 THEN '11' WHEN rec.planning_organization_id = 97 THEN '21' ELSE '00' END;

            l_segment7 := '00';

            l_segment8 := '0000';

            

            xxtlm_pkmtl_transaction(p_item_id => rec.inventory_item_id, p_organization_id => rec.planning_organization_id,

                                   p_owning_organization_id => NULL,

                                   p_owning_tp_type => NULL,

                                   p_planning_tp_type => NULL,

                                   p_planning_organization_id => NULL,

                                   p_subinv => rec.subinventory_code,

                                   p_loc => rec.locators,

                                   p_lot_no => NULL,

                                   p_origination_date => NULL,

                                   p_uom_code => rec.primary_uom_code,

                                   p_quantity => rec.onhand_quantity * - 1,

                                   p_transaction_type => 'Miscellaneous issue',

                                   p_trans_type_ref => rec.organization_code

                                                       || ' MISC ISSUE '

                                                       || trunc(sysdate),

                                   p_primary_qty => NULL,

                                   p_clei_code => NULL, ---need to change logic

                                   p_segment1 => l_segment1,

                                   p_segment2 => l_segment2,

                                   p_segment3 => l_segment3,

                                   p_segment4 => l_segment4,

                                   p_segment5 => l_segment5,

                                   p_segment6 => l_segment6,

                                   p_segment7 => l_segment7,

                                   p_segment8 => l_segment8,

                                   x_return_status => x_return_status,

                                   x_msg_count => x_msg_count,

                                   x_msg_data => x_msg_data);

            dbms_output.put_line('X_RETURN_STATUS->'

                                 || x_return_status

                                 || '    X_MSG_COUNT->'

                                 || x_msg_count

                                 || '    X_MSG_DATA->'

                                 || x_msg_data);

        END;

        BEGIN

            UPDATE xxtlm.xxtlm_item_dtls_mi

            SET

                status_flag = x_return_status,

                ERROR_MSG=x_msg_data

            WHERE

                    organization_id = rec.organization_id

                AND inventory_item_id = rec.inventory_item_id

                AND subinventory_code = rec.subinventory_code

                AND locators = rec.locators

                AND onhand_quantity = rec.onhand_quantity;

        EXCEPTION

            WHEN OTHERS THEN

                dbms_output.put_line('Error while updating status for item: '

                                     || rec.item_code

                                     || '. locator: '

                                     || rec.locators);

        END;

    END LOOP;

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Unexpected Error in Main block: ' || sqlerrm);

END;

/

/***--Dependent Procedure---***/

create or replace PROCEDURE xxtlm_pkmtl_transaction (

    p_item_id                   IN   VARCHAR2,

    p_organization_id           IN   NUMBER,

    p_owning_organization_id    IN   mtl_onhand_quantities_detail.owning_organization_id%TYPE,

    p_owning_tp_type            IN   mtl_onhand_quantities_detail.owning_tp_type%TYPE,

    p_planning_tp_type          IN   mtl_onhand_quantities_detail.planning_tp_type%TYPE,

    p_planning_organization_id  IN   mtl_onhand_quantities_detail.planning_organization_id%TYPE,

    p_subinv                    IN   VARCHAR2,

    p_loc                       IN   VARCHAR2,

    p_lot_no                    IN   VARCHAR2,

    p_origination_date          IN   DATE,

    p_uom_code                  IN   VARCHAR2,

    p_quantity                  IN   NUMBER,

    p_transaction_type          IN   VARCHAR2,

    p_trans_type_ref            IN   VARCHAR2,

    p_primary_qty               IN   NUMBER,

    p_clei_code                 IN   VARCHAR2,

    p_segment1                  IN   VARCHAR2,

    p_segment2                  IN   VARCHAR2,

    p_segment3                  IN   VARCHAR2,

    p_segment4                  IN   VARCHAR2,

    p_segment5                  IN   VARCHAR2,

    p_segment6                  IN   VARCHAR2,

    p_segment7                  IN   VARCHAR2,

    p_segment8                  IN   VARCHAR2,

    x_return_status             OUT  VARCHAR2,

    x_msg_count                 OUT  NUMBER,

    x_msg_data                  OUT  VARCHAR2

) IS

    l_transaction_id    NUMBER;

    l_loc_id            NUMBER;

    l_trn_type_id       NUMBER;

    l_segment1          VARCHAR2(150);

    l_segment2          VARCHAR2(150);

    l_segment3          VARCHAR2(150);

    l_segment4          VARCHAR2(150);

    l_segment5          VARCHAR2(150);

    l_segment6          VARCHAR2(150);

    l_segment7          VARCHAR2(150);

    l_segment8          VARCHAR2(150);

    l_segment9          VARCHAR2(150);

    l_segment10         VARCHAR2(150);

    l_segment11         VARCHAR2(150);

    l_expiration_date   DATE;

    l_trans_count       NUMBER;

    lc_result           NUMBER;

    l_subinv_code       mtl_item_locations_kfv.subinventory_code%TYPE;

    r_mtl_trns_rec      mtl_transactions_interface%rowtype;

    r_mtl_lot_rec       mtl_transaction_lots_interface%rowtype;

    g_l_transaction_id  NUMBER;

    g_l_lpn_id          NUMBER := 0;

    CURSOR cur_tns_rec IS

    SELECT

        mtt.*

    FROM

        mtl_transaction_types mtt

    WHERE

        mtt.transaction_type_name = p_transaction_type;

    r_tns_rec           cur_tns_rec%rowtype;

    PRAGMA autonomous_transaction;

BEGIN

    x_return_status := NULL;

    x_msg_data := NULL;

    dbms_output.put_line('================================================================================');

    BEGIN

        SELECT

            mtl_material_transactions_s.NEXTVAL

        INTO l_transaction_id

        FROM

            dual;

    EXCEPTION

        WHEN OTHERS THEN

            l_transaction_id := NULL;

    END;

    OPEN cur_tns_rec;

    FETCH cur_tns_rec INTO r_tns_rec;

    CLOSE cur_tns_rec;

    BEGIN

--        l_segment1 := '10';

--        l_segment2 := '00';

--        l_segment3 := '3205';

--        l_segment4 := '10';

--        l_segment5 := '01';

--        l_segment6 := '00';

--        l_segment7 := '00';

--        l_segment8 := '0000';

        l_segment1 := p_segment1;

        l_segment2 := p_segment2;

        l_segment3 := p_segment3;

        l_segment4 := p_segment4;

        l_segment5 := p_segment5;

        l_segment6 := p_segment6;

        l_segment7 := p_segment7;

        l_segment8 := p_segment8;

    EXCEPTION

        WHEN OTHERS THEN

            l_segment1 := NULL;

            l_segment2 := NULL;

            l_segment6 := NULL;

            l_segment3 := NULL;

            l_segment4 := NULL;

            l_segment5 := NULL;

            l_segment7 := NULL;

            l_segment8 := NULL;

            l_segment9 := NULL;

            l_segment10 := NULL;

    END;

    BEGIN

        SELECT

            inventory_location_id,

            subinventory_code

        INTO

            l_loc_id,

            l_subinv_code

        FROM

            mtl_item_locations_kfv

        WHERE

                concatenated_segments = p_loc

            AND organization_id = p_organization_id;

    EXCEPTION

        WHEN OTHERS THEN

            l_loc_id := NULL;

    END;

    dbms_output.put_line('p_loc: ' || p_loc);

    dbms_output.put_line('l_loc_id: ' || l_loc_id);

    dbms_output.put_line('p_lot_no: ' || p_lot_no);

    dbms_output.put_line('Transaction_type: ' || r_tns_rec.transaction_type_name);

    dbms_output.put_line('p_quantity: ' || p_quantity);

    BEGIN

        r_mtl_trns_rec := NULL;

        g_l_transaction_id := NULL;

        r_mtl_trns_rec.transaction_uom := p_uom_code;

        r_mtl_trns_rec.transaction_date := sysdate;

        r_mtl_trns_rec.source_code := p_transaction_type;

        r_mtl_trns_rec.source_line_id := l_transaction_id;           -- 422170

        r_mtl_trns_rec.source_header_id := l_transaction_id;         -- 167091

        r_mtl_trns_rec.process_flag := 1;

        r_mtl_trns_rec.transaction_mode := 3;

        r_mtl_trns_rec.lock_flag := 2;

        r_mtl_trns_rec.locator_id := l_loc_id;

        r_mtl_trns_rec.last_update_date := sysdate;

        r_mtl_trns_rec.last_updated_by := fnd_global.user_id;

        r_mtl_trns_rec.creation_date := sysdate;

        r_mtl_trns_rec.created_by := fnd_global.user_id;

        r_mtl_trns_rec.inventory_item_id := p_item_id;

        r_mtl_trns_rec.subinventory_code := nvl(p_subinv, l_subinv_code);

        r_mtl_trns_rec.organization_id := p_organization_id;

        r_mtl_trns_rec.transaction_quantity := p_quantity;

        r_mtl_trns_rec.primary_quantity := p_primary_qty;

        r_mtl_trns_rec.transaction_type_id := r_tns_rec.transaction_type_id;

        r_mtl_trns_rec.transaction_source_type_id := r_tns_rec.transaction_source_type_id;

        r_mtl_trns_rec.transaction_action_id := r_tns_rec.transaction_action_id;

        --Added by Venky

        r_mtl_trns_rec.owning_organization_id := p_owning_organization_id; --3091

        r_mtl_trns_rec.owning_tp_type := p_owning_tp_type;                 --1

        r_mtl_trns_rec.planning_organization_id := p_planning_organization_id;

        r_mtl_trns_rec.planning_tp_type := p_planning_tp_type;             --2

        -- End Added by Venky

        r_mtl_trns_rec.dst_segment1 := l_segment1;

        r_mtl_trns_rec.dst_segment2 := l_segment2;

        r_mtl_trns_rec.dst_segment3 := l_segment3;

        r_mtl_trns_rec.dst_segment4 := l_segment4;

        r_mtl_trns_rec.dst_segment5 := l_segment5;

        r_mtl_trns_rec.dst_segment6 := l_segment6;

        r_mtl_trns_rec.dst_segment7 := l_segment7;

        r_mtl_trns_rec.dst_segment8 := l_segment8;

        r_mtl_trns_rec.dst_segment9 := l_segment9;

        r_mtl_trns_rec.dst_segment10 := l_segment10;

        r_mtl_trns_rec.transaction_interface_id := l_transaction_id;

        r_mtl_trns_rec.transaction_header_id := l_transaction_id;

        r_mtl_trns_rec.transaction_reference := p_trans_type_ref;

        IF g_l_lpn_id > 0 THEN

            r_mtl_trns_rec.transfer_lpn_id := g_l_lpn_id;

            dbms_output.put_line('g_l_lpn_id: ' || g_l_lpn_id);

        END IF;

        -- Insert MTI for inv open transactions.

        INSERT INTO mtl_transactions_interface VALUES r_mtl_trns_rec;

        dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: ' || l_transaction_id);

        g_l_transaction_id := l_transaction_id;

        --dbms_output.put_line('Inserted a record'||l_transaction_id);

        x_return_status := fnd_api.g_ret_sts_success;

    EXCEPTION

        WHEN OTHERS THEN

            fnd_file.put_line(fnd_file.output,

                             'in exception ---- inserting data into  MTL_TRANSACTIONS_INTERFACE : ' || sqlerrm);

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

            x_return_status := fnd_api.g_ret_sts_error;

            x_msg_count := x_msg_count + 1;

            x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :' || sqlerrm;

    END;

    dbms_output.put_line('STEP1');

    IF x_return_status != fnd_api.g_ret_sts_error THEN                                   --Successful return of first insert

        IF p_lot_no IS NOT NULL THEN

            --Derive lot expiration date for the lot number

            BEGIN

                SELECT

                    expiration_date

                INTO l_expiration_date

                FROM

                    mtl_lot_numbers

                WHERE

                        lot_number = p_lot_no

                    AND inventory_item_id = p_item_id

                    AND organization_id = p_organization_id;

            EXCEPTION

                WHEN OTHERS THEN

                    dbms_output.put_line('Exception in fetching lot expiration_date: ' || sqlerrm);

                    l_expiration_date := NULL;

                    dbms_output.put_line('STEP2');

            END;

            dbms_output.put_line('STEP3');

            dbms_output.put_line('Expiration_date: ' || l_expiration_date);

            dbms_output.put_line('STEP3.1');

            BEGIN

                dbms_output.put_line('STEP4');

                --Insert lot number interface if the item is lot controlled.

                r_mtl_lot_rec := NULL;

                g_l_transaction_id := l_transaction_id;

                r_mtl_lot_rec.transaction_interface_id := l_transaction_id;

                r_mtl_lot_rec.source_code := p_transaction_type;

                r_mtl_lot_rec.source_line_id := l_transaction_id;

                r_mtl_lot_rec.last_update_date := sysdate;

                r_mtl_lot_rec.last_updated_by := fnd_global.user_id;

                r_mtl_lot_rec.creation_date := sysdate;

                r_mtl_lot_rec.created_by := fnd_global.user_id;

                r_mtl_lot_rec.last_update_login := fnd_global.login_id;

                r_mtl_lot_rec.lot_number := p_lot_no;

                r_mtl_lot_rec.origination_date := p_origination_date;

                r_mtl_lot_rec.lot_expiration_date := l_expiration_date;

                r_mtl_lot_rec.transaction_quantity := p_quantity;

                r_mtl_lot_rec.primary_quantity := p_quantity;

                r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;

                --New CLEI Code--

                r_mtl_lot_rec.lot_attribute_category := '102';

                r_mtl_lot_rec.c_attribute1 := p_clei_code;

                --New CLEI Code--

                dbms_output.put_line('STEP5');

                INSERT INTO mtl_transaction_lots_interface VALUES r_mtl_lot_rec;

                dbms_output.put_line('STEP6');

            EXCEPTION

                WHEN OTHERS THEN

                    x_return_status := fnd_api.g_ret_sts_error;

                    x_msg_count := x_msg_count + 1;

                    x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :' || sqlerrm;

                    dbms_output.put_line('STEP7');

            END;

        END IF;

        dbms_output.put_line('STEP8');

        COMMIT;

        --call Inventory open transaction api to perform transfer of material from source locator to destination locator.

        dbms_output.put_line('STEP9');

        x_return_status := NULL;

        x_msg_count := 0;

        x_msg_data := NULL;

        dbms_output.put_line('Before Calling process_transactions' || l_transaction_id);

        lc_result := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,

                                                             p_init_msg_list => fnd_api.g_false,

                                                             p_commit => fnd_api.g_true,

                                                             p_validation_level => fnd_api.g_valid_level_full,

                                                             x_return_status => x_return_status,

                                                             x_msg_count => x_msg_count,

                                                             x_msg_data => x_msg_data,

                                                             x_trans_count => l_trans_count,

                                                             p_table => 1,

                                                             p_header_id => l_transaction_id);

        dbms_output.put_line('STEP10');

        dbms_output.put_line('Return Status' || x_return_status);

        IF nvl(x_return_status, 'N') <> fnd_api.g_ret_sts_success THEN

            x_return_status := fnd_api.g_ret_sts_error;

            dbms_output.put_line('Return Status: ' || x_return_status);

            dbms_output.put_line('Message: ' || x_msg_data);

            dbms_output.put_line('STEP11');

        END IF;

    END IF;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('In Exception' || sqlerrm);

        fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);

        dbms_output.put_line('STEP12');

        ROLLBACK;

END xxtlm_pkmtl_transaction;

Oracle EBS - How to assign Item with Item Category using Oracle API

 DECLARE

    x_return_status      VARCHAR2(1000);

    x_errorcode          NUMBER;

    x_msg_count          NUMBER;

    x_msg_data           VARCHAR2(1000);

    x_msg_index_out      NUMBER;

    l_error_message      VARCHAR2(4000);

    l_category_id        NUMBER := 7041;  --DEFAULT.DEFAULT.DEFAULT

    l_category_set_id    NUMBER := 1100000061; --TLM_IS_Production_Scheduling

    l_inventory_item_id  NUMBER := 1008;

    l_organization_id    NUMBER := 116; --TIS

BEGIN

    --Create Table Script--

    /*

    CREATE TABLE xxtlm_item_category_assign_stag (

    item_id    NUMBER,

    item_code  VARCHAR2(500),

    organization_id NUMBER,

    status     VARCHAR2(500),

    error_msg  VARCHAR2(500)

);

    */

    DELETE FROM xxtlm_item_category_assign_stag;

    COMMIT;

   

   --START Insert Item in the Stag table--

    INSERT INTO xxtlm_item_category_assign_stag (

        item_code,

        item_id,

        organization_id

    )

        SELECT

            a.assembly_item       item_code,

            a.assembly_item_id    item_id,

            a.organization_id

        FROM

            xxtlm_trace_bom_v a

        WHERE

                a.organization_id = 116

            AND EXISTS (

                SELECT

                    item_type

                FROM

                    mtl_system_items_b

                WHERE

                        organization_id = 116

                    AND item_type <> 'ASM1'

                    AND inventory_item_id = a.assembly_item_id

            )

        UNION

        SELECT

            a.component_item       item_code,

            a.component_item_id    item_id,

            a.organization_id

        FROM

            xxtlm_trace_bom_v a

        WHERE

                organization_id = 116

            AND EXISTS (

                SELECT

                    item_type

                FROM

                    mtl_system_items_b

                WHERE

                        organization_id = 116

                    AND item_type <> 'ASM1'

                    AND inventory_item_id = a.component_item_id

            );

    COMMIT;

   --END Insert Item in the Stag table--

    

    --Get Category Set ID--

    BEGIN

        SELECT

            category_set_id

        INTO l_category_set_id

        FROM

            mtl_category_sets_tl

        WHERE

            category_set_name = 'TLM_IS_Production_Scheduling'; --CATEGORY_SET_ID-1100000061

    EXCEPTION

        WHEN OTHERS THEN

            dbms_output.put_line('Error while getting category set details '

                                 || substr(sqlerrm, 1, 200));

    END;

--Get Category ID--

    BEGIN

        SELECT

            category_id

        INTO l_category_id

        FROM

            apps.mtl_categories_kfv

        WHERE

            concatenated_segments = 'DEFAULT.DEFAULT.DEFAULT'; --CATEGORY_ID=7041

    EXCEPTION

        WHEN OTHERS THEN

            dbms_output.put_line('Error while getting category details '

                                 || substr(sqlerrm, 1, 200));

    END;

    

     --Apps Initialization is available in another section. Use the below link to know in detail

    fnd_global.apps_initialize(user_id => 3985, resp_id => 20634, resp_appl_id => 401);

    FOR i IN (

        SELECT

            item_id,

            organization_id

        FROM

            xxtlm_item_category_assign_stag

        WHERE

            item_id = 1008

    ) LOOP

        l_error_message := NULL;

        inv_item_category_pub.create_category_assignment(p_api_version => 1.0, p_init_msg_list => fnd_api.g_false,

                                                        p_commit => fnd_api.g_false,

                                                        x_return_status => x_return_status,

                                                        x_errorcode => x_errorcode,

                                                        x_msg_count => x_msg_count,

                                                        x_msg_data => x_msg_data,

                                                        p_category_id => l_category_id,

                                                        p_category_set_id => l_category_set_id,

                                                        p_inventory_item_id => i.item_id,-- l_inventory_item_id,

                                                        p_organization_id => i.organization_id --l_organization_id

                                                        );

        IF x_return_status = fnd_api.g_ret_sts_success THEN

            UPDATE xxtlm_item_category_assign_stag

            SET

                status = 'S'

            WHERE

                    item_id = i.item_id

                AND organization_id = i.organization_id;

            COMMIT;

            dbms_output.put_line('Item Category Assignment using API is Successful');

        ELSE

            BEGIN

                IF ( fnd_msg_pub.count_msg > 1 ) THEN

                    FOR k IN 1..fnd_msg_pub.count_msg LOOP

                        fnd_msg_pub.get(p_msg_index => k, p_encoded => 'F', p_data => x_msg_data,

                                       p_msg_index_out => x_msg_index_out);

                        dbms_output.put_line('x_msg_data:= ' || x_msg_data);

                        IF x_msg_data IS NOT NULL THEN

                            l_error_message := l_error_message

                                               || '-'

                                               || x_msg_data;

                        END IF;

                    END LOOP;

                ELSE

            --Only one error

                    fnd_msg_pub.get(p_msg_index => 1, p_encoded => 'F', p_data => x_msg_data,

                                   p_msg_index_out => x_msg_index_out);

                    l_error_message := x_msg_data;

                END IF;

                dbms_output.put_line('Error encountered by the API is ' || l_error_message);

                ROLLBACK;

            EXCEPTION

                WHEN OTHERS THEN

                    l_error_message := sqlerrm;

                    dbms_output.put_line('Error encountered by the API is ' || l_error_message);

            END;

            UPDATE xxtlm_item_category_assign_stag

            SET

                status = 'E',

                error_msg = l_error_message

            WHERE

                    item_id = i.item_id

                AND organization_id = i.organization_id;

        END IF;

    END LOOP;

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Error in Assigning Category to an Item and error is '

                             || substr(sqlerrm, 1, 200));

END;

/

Oracle Fusion: How to Create Report Structure in Oracle Fusion for accessing BIP Report from the Oracle UI.

Subject: How to Create Report Structure in Oracle Fusion.

Prerequisite: For creating structure first we need to enable sandbox with having tools "structure"

Step1: Create Sandbox with Tool Structure > Create and Enter Sandbox

Step2: Navigate to Configuration > Structure

Step3: Create Group > Save and Close.

Name: SCM Maintenance Reports (It’s UI Tab Name)

Select Icon.

Show on Navigator: EL Expression

 

Step4: Select Group and then Click on Create Page.

Name: GDI WO Item Ordering Report

Group: SCM Maintenance Report

Link Type: Static URL

Destination: 

(Note:

1. Copy the URL From the Report Level, Open the Report from Catalog > Settings > Share Report Link > Current Page

2. Yellow Highlighted part is for the dynamically pick the Application instance URL, )

Paste the below URL in the Destination field.

https://#{header["host"]}/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FCustom%2FGDI%20Custom%20Reports%2FSCM%2FMaintenance%2FWork%20Order%2FReport%2FGDI%20WO%20Item%20Ordering%20Report.xdo&path=%2Fshared%2FCustom%2FGDI%20Custom%20Reports%2FSCM%2FMaintenance%2FWork%20Order%2FReport%2FGDI%20WO%20Item%20Ordering%20Report.xdo

 

Enter all the below information for the BIP Report. Save and Close

 

Step5: Navigate to Home Page > SCM Maintenance Report Tab

 

We can able to access the newly registered reports from the UI.