Stock Locator Interface API

CREATE OR REPLACE PACKAGE XXTUI_STOCK_LOC_PKGR IS
PROCEDURE MSG(P_MESSAGE IN VARCHAR2);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER);
END;
/              
CREATE OR REPLACE PACKAGE BODY XXTUI_STOCK_LOC_PKGR IS

PROCEDURE MSG(P_MESSAGE IN VARCHAR2) IS
BEGIN
    FND_FILE.PUT_LINE(FND_FILE.LOG,P_MESSAGE);
END;

PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER) IS
              
    lv_return_status            VARCHAR2(1);
    ln_msg_count                NUMBER;
    lv_msg_data                 VARCHAR2(2000);   
    ln_inventory_location_id    NUMBER;   
    lv_locator_exists           VARCHAR2(1);  
   
   
    ln_organization_id          NUMBER;
    lb_error_flag               BOOLEAN;
    lv_error_message            VARCHAR2(4000);
    lv_subinventory_exists      NUMBER(1);
    lv_loc_exists               NUMBER(1);
   
    ln_t_cnt                    NUMBER := 0;
    ln_s_cnt                    NUMBER := 0;
    ln_e_cnt                    NUMBER := 0;
   
BEGIN

        MSG('********** TUI Stock Locator Program '||
            TO_CHAR(SYSDATE,'DD MON YYYY HH24:MI:SS')||' **********');

        FOR rec_loc IN (SELECT a.rowid rowid1,
                               a.*
                          FROM XXTUI_ITEM_LOC_STG_PRAVEEN a
                         WHERE process_flag = 'N')
        LOOP
       

       
        ln_t_cnt                := ln_t_cnt + 1;
       
        lv_error_message        := '';
        lb_error_flag           := FALSE;
       
       
--Get Organization id based on Organization code       
        BEGIN
            SELECT organization_id
              INTO ln_organization_id
              FROM MTL_PARAMETERS
             WHERE organization_code = rec_loc.organization_code;       
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
                 lv_error_message := 'Organization code provided is invalid';
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
                 lv_error_message := 'Error while validating Organization code:- '||SQLERRM;               
        END;
       
       
-- Validate subinventory code       
        BEGIN
            SELECT 1
              INTO lv_subinventory_exists
              FROM MTL_SECONDARY_INVENTORIES
             WHERE secondary_inventory_name = rec_loc.subinventory_code
               AND organization_id = ln_organization_id;       
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '||'subinventory code provided is invalid';
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '||'Error while validating subinventory code:- '||SQLERRM;               
        END;       
       
       
--Check Locator exists       
        BEGIN
            SELECT 1
              INTO lv_loc_exists
              FROM MTL_ITEM_LOCATIONS_KFV
             WHERE subinventory_code = rec_loc.subinventory_code
               AND organization_id = ln_organization_id
               AND concatenated_segments = rec_loc.concatenated_segments;
                
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '||'Locator combinations already exists';                   
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 NULL;
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '||'Error while validating locator:- '||SQLERRM;               
        END;        
       
       
       
        IF lb_error_flag = FALSE THEN
       
        ln_s_cnt                := ln_s_cnt + 1;
       
            INV_LOC_WMS_PUB.CREATE_LOCATOR (
                lv_return_status,
                ln_msg_count,
                lv_msg_data,
                ln_inventory_location_id,
                lv_locator_exists,
                NULL,
                rec_loc.organization_code,
                rec_loc.concatenated_segments ,
                rec_loc.description,
                NULL,
                NULL,
                NULL,
                rec_loc.subinventory_code,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL) ;
               
                IF lv_return_status = 'S' THEN
                   COMMIT;
                END IF;
               
               
                MSG('                       ');
                MSG('return_status        :-'||lv_return_status);
                MSG('msg_count            :-'||ln_msg_count);
                MSG('msg_data             :-'||lv_msg_data);
                MSG('inventory_location_id:-'||ln_inventory_location_id);
                MSG('locator_exists       :-'||lv_locator_exists);
               
               
               
            UPDATE XXTUI_ITEM_LOC_STG_PRAVEEN
               SET process_flag = 'S'
             WHERE rowid = rec_loc.rowid1;
            
        ELSE
       
        ln_e_cnt                := ln_e_cnt + 1;
       
             UPDATE XXTUI_ITEM_LOC_STG_PRAVEEN
               SET process_flag = 'E',
                   error_message = lv_error_message
             WHERE rowid = rec_loc.rowid1;
       
        END IF;
       
        END LOOP;
       
        MSG('                         ');
        MSG('Total Records Processed:-'||ln_t_cnt);
        MSG('Total Records success  :-'||ln_s_cnt);
        MSG('Total Records rejected :-'||ln_e_cnt);
        MSG('********** End of Locator Program '||
            TO_CHAR(SYSDATE,'DD MON YYYY HH24:MI:SS')||' **********');

EXCEPTION
        WHEN OTHERS THEN
             MSG('Err MSG:'||SQLERRM||' - '||'Err Num:-'||SQLCODE);              
END;
END;
/