Monday 25 April 2022

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;

No comments:

Post a Comment