Locator creation API in Oracle Property manager

V_ROWID                ROWID;
v_location_id          NUMBER;
v_addr_id              NUMBER;
v_return_status        VARCHAR2(10);
v_return_MSG           VARCHAR2(1000);
v_code                 VARCHAR2(1000);
v_uom_code             VARCHAR2(1000);
v_uom                  VARCHAR2(1000);
v_city_code            VARCHAR2(1000);
v_city                 VARCHAR2(1000);
v_state_code           VARCHAR2(1000);
v_state                VARCHAR2(1000);
v_operator_name        VARCHAR2(1000);
v_operator_description VARCHAR2(1000);
v_site_type1           VARCHAR2(1000);
v_site_type1_desc      VARCHAR2(1000);
v_site_type2           VARCHAR2(1000);
v_site_type2_desc      VARCHAR2(1000);
v_in_outdoor           VARCHAR2(1000);
v_in_outdoor_desc      VARCHAR2(1000);
v_error_flag           VARCHAR2(10):='N';
v_error_msg            VARCHAR2(10000);
v_circle_id            NUMBER;
-----------------------------------
/*Begin executing MAIN procedure*/
-----------------------------------
BEGIN
--fnd_global.apps_initialize (48634, 62408, 240);


    FND_FILE.PUT_LINE(FND_FILE.LOG,'*******************************************************************');
    FND_FILE.PUT_LINE(FND_FILE.LOG,'                   Airtel Symphony Locator Creation');
    FND_FILE.PUT_LINE(FND_FILE.LOG,'*******************************************************************');
FOR r_get_locator_rec
 IN c_get_locator_rec
LOOP

V_ROWID                :=NULL;
v_location_id          :=NULL;
v_addr_id              :=NULL;
v_return_status        :=NULL;
v_return_MSG           :=NULL;
v_code                 :=NULL;
v_uom_code             :=NULL;
v_uom                  :=NULL;
v_city_code            :=NULL;
v_city                 :=NULL;
v_state_code           :=NULL;
v_state                :=NULL;
v_operator_name        :=NULL;
v_operator_description :=NULL;
v_site_type1           :=NULL;
v_site_type1_desc      :=NULL;
v_site_type2           :=NULL;
v_site_type2_desc      :=NULL;
v_in_outdoor           :=NULL;
v_in_outdoor_desc      :=NULL;
v_error_msg            :=NULL;
v_circle_id            :=NULL;


--Validate Circle
        BEGIN
            SELECT organization_id
            INTO   v_circle_id
            FROM   hr_operating_units
            WHERE  name  = r_get_locator_rec.operating_unit
            ;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'Circle'||r_get_locator_rec.operating_unit||'  is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating Circle '||r_get_locator_rec.operating_unit||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;
 g_org_id:=v_circle_id;

-- Fetch responsibility Details for apps initialization
        BEGIN
                SELECT fr.responsibility_id,fr.application_id
                INTO   g_resp_id,g_appl_id
                FROM   fnd_profile_options_vl fpo, fnd_profile_option_values fpov, fnd_responsibility_vl fr
                WHERE  fpo.profile_option_name = 'ORG_ID'
                AND    fpo.profile_option_id = fpov.profile_option_id
                AND    fpov.profile_option_value = to_char(v_circle_id)
                AND    fpov.level_value = fr.responsibility_id
                AND    upper(fr.responsibility_name) like upper('%Property Lease Initiator%');


        EXCEPTION
        WHEN OTHERS THEN
            v_error_msg:=v_error_msg||'Error in fetching Responsibility, termintaing the process';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
                  UPDATE btvl_sym_locator_stg
                  SET    status = 'ERROR'
                        ,message = v_error_msg
                  WHERE  rowid = r_get_locator_rec.rowid;
                  COMMIT;
           -- RAISE_APPLICATION_ERROR(-2001,v_error_msg);

        END;
        dbms_output.put_line(g_user_id||','|| g_resp_id||','||g_appl_id);
        fnd_global.apps_initialize (g_user_id, g_resp_id,g_appl_id);
        g_invorg_id :=FND_PROFILE.VALUE('BTVL_PN_INV_ORG_ID');
        g_bg_id     :=fnd_profile.value('PER_BUSINESS_GROUP_ID');
        g_org_id    :=fnd_profile.value('ORG_ID');

--Fetch Location Code
        SELECT btvl_utilities_pkg.get_location_code(g_invorg_id, r_get_locator_rec.area_code, g_bg_id)
        into   v_code
        FROM   DUAL;

        FND_FILE.PUT_LINE(FND_FILE.LOG,'Location Code is '||v_code);
        dbms_output.put_line('Location Code is '||v_code);
--Validate UOM Code
        BEGIN
            SELECT lookup_code,meaning
            INTO   v_uom_code,v_uom
            FROM   fnd_lookup_values
            WHERE  lookup_type  = 'PN_UNITS_OF_MEASURE'
            AND    enabled_flag = 'Y'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                                      AND TRUNC(NVL(end_date_active,SYSDATE))
            AND    lookup_code  = r_get_locator_rec.uom_code;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'UOM Code'||v_uom_code||' entered is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating UOM Code '||v_uom_code||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

 --Validate City
        BEGIN
            SELECT lookup_code, meaning
            INTO   v_city_code,v_city
            FROM   fnd_lookups
            WHERE  lookup_type  = 'PN_CITIES'
            AND    enabled_flag = 'Y'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                                      AND TRUNC(NVL(end_date_active,SYSDATE))
            AND    UPPER(lookup_code)  = UPPER(r_get_locator_rec.city)
            AND    ROWNUM=1;

        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'City Code'||v_city_code||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating City Code '||v_city_code||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

 --Validate State
        BEGIN
            SELECT lookup_code, meaning
            INTO   v_state_code,v_state
            FROM   fnd_lookups
            WHERE  lookup_type  = 'PN_STATE'
            AND    enabled_flag = 'Y'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
                                      AND TRUNC(NVL(end_date_active,SYSDATE))
            AND    UPPER(lookup_code)  = UPPER(r_get_locator_rec.state) ;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_error_msg:=v_error_msg||'State '||v_state_code||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
        v_error_msg:=v_error_msg||'Error in Validating State '||v_state_code||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

 --Validate Name of Operator
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_operator_name,v_operator_description
            FROM  fnd_flex_value_sets ffvs ,
                  fnd_flex_values ffv ,
                  fnd_flex_values_tl ffvt
            WHERE 1=1
            AND   ffvs.flex_value_set_id = ffv.flex_value_set_id
            AND   ffv.flex_value_id      = ffvt.flex_value_id
            AND   ffvt.language          = USERENV('LANG')
            AND   flex_value_set_name    ='BTVL_PN_NAME_OPERATOR' --attribute2
            AND   ffv.enabled_flag <> 'N'
            AND   ffv.flex_value         = r_get_locator_rec.name_operator_attr2
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
             v_error_msg:=v_error_msg||'Name of Operator'||v_operator_name||' is not found . Please verify!!';
             FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
             v_error_flag := 'Y';
        WHEN OTHERS THEN
             v_error_msg:=v_error_msg||'Error in Validating Name of Operator'||v_operator_name||', Error Message ->'||sqlerrm;
             FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
             v_error_flag := 'Y';
        END;

 --Validate Site Type 1
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_site_type1,v_site_type1_desc
            FROM   fnd_flex_value_sets ffvs ,
                   fnd_flex_values ffv ,
                   fnd_flex_values_tl ffvt
            WHERE  1=1
            AND    ffvs.flex_value_set_id   = ffv.flex_value_set_id
            AND    ffv.flex_value_id        = ffvt.flex_value_id
            AND    ffvt.language            = USERENV('LANG')
            AND    flex_value_set_name      ='BTVL_PN_SITE_TYPE1' --attribute3
            AND    ffv.enabled_flag <> 'N'
            AND    ffv.flex_value           = r_get_locator_rec.site_Type1_attr3
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_error_msg:=v_error_msg||'Site Type 1'||v_site_type1||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
            v_error_msg:=v_error_msg||'Error in Validating Site Type 1 '||v_site_type1||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

  --Validate Site Type 2
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_site_type2,v_site_type2_desc
            FROM   fnd_flex_value_sets ffvs ,
                   fnd_flex_values ffv ,
                   fnd_flex_values_tl ffvt
            WHERE  1=1
            AND    ffvs.flex_value_set_id = ffv.flex_value_set_id
            AND    ffv.flex_value_id      = ffvt.flex_value_id
            AND    ffvt.language          = USERENV('LANG')
            AND    flex_value_set_name    ='BTVL_PN_SITE_TYPE2' --attribute4
            AND    ffv.flex_value = r_get_locator_rec.site_Type2_attr4
            AND    ffv.enabled_flag <> 'N'
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_error_msg  :=v_error_msg||'Site Type 2'||v_site_type2||' is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
            v_error_msg  :=v_error_msg||'Error in Validating Site Type 1 '||v_site_type2||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

  --Validate Indoor/Outdoor New
        BEGIN
            SELECT ffvt.flex_value_meaning,ffvt.description
            INTO   v_in_outdoor,v_in_outdoor_desc
            FROM   fnd_flex_value_sets ffvs ,
                   fnd_flex_values ffv ,
                   fnd_flex_values_tl ffvt
            WHERE  1=1
            AND    ffvs.flex_value_set_id = ffv.flex_value_set_id
            and    ffv.flex_value_id      = ffvt.flex_value_id
            AND    ffvt.language          = USERENV('LANG')
            and    flex_value_set_name    ='BTVL_PN_INDOOR_OUTDOOR' --attribute6
            AND    ffv.flex_value         = r_get_locator_rec.inout_door_attr12
            and    ffv.enabled_flag <> 'N'
            ORDER BY flex_value asc;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_error_msg  :=v_error_msg||'Indoor/Outdoor New'||v_in_outdoor||'  is not found . Please verify!!';
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        WHEN OTHERS THEN
            v_error_msg  :=v_error_msg||'Error in Validating Indoor/Outdoor New '||v_in_outdoor||', Error Message ->'||sqlerrm;
            FND_FILE.PUT_LINE(FND_FILE.LOG,v_error_msg);
            v_error_flag := 'Y';
        END;

  --If validation is Success then execute API
        IF v_error_flag <> 'Y'
        THEN
        dbms_output.put_line('error flag >'||v_error_flag);
          PNT_LOCATIONS_PKG.insert_row (
                                  x_rowid                       =>    v_rowid         --out parameter
                                 ,x_org_id                      =>    v_circle_id
                                 ,x_LOCATION_ID                 =>    v_location_id   --out parameter
                                 ,x_LAST_UPDATE_DATE            =>    sysdate
                                 ,x_LAST_UPDATED_BY             =>    -1
                                 ,x_CREATION_DATE               =>    sysdate
                                 ,x_CREATED_BY                  =>    -1
                                 ,x_LAST_UPDATE_LOGIN           =>    -1
                                 ,x_LOCATION_PARK_ID            =>    NULL
                                 ,x_LOCATION_TYPE_LOOKUP_CODE   =>    'BUILDING'
                                 ,x_SPACE_TYPE_LOOKUP_CODE      =>    NULL
                                 ,x_FUNCTION_TYPE_LOOKUP_CODE   =>    NULL
                                 ,x_STANDARD_TYPE_LOOKUP_CODE   =>    NULL
                                 ,x_LOCATION_ALIAS              =>    v_code
                                 ,x_LOCATION_CODE               =>    v_code
                                 ,x_BUILDING                    =>    r_get_locator_rec.area_code
                                 ,x_LEASE_OR_OWNED              =>    'L'
                                 ,x_CLASS                       =>    NULL
                                 ,x_STATUS_TYPE                 =>    NULL
                                 ,x_FLOOR                       =>    NULL
                                 ,x_OFFICE                      =>    NULL
                                 ,x_ADDRESS_ID                  =>    v_addr_id       --out parameter
                                 ,x_MAX_CAPACITY                =>    0
                                 ,x_OPTIMUM_CAPACITY            =>    0
                                 ,x_GROSS_AREA                  =>    NULL
                                 ,x_RENTABLE_AREA               =>    0
                                 ,x_USABLE_AREA                 =>    0
                                 ,x_ASSIGNABLE_AREA             =>    NULL
                                 ,x_COMMON_AREA                 =>    NULL
                                 ,x_SUITE                       =>    NULL
                                 ,x_ALLOCATE_COST_CENTER_CODE   =>    NULL
                                 ,x_UOM_CODE                    =>    r_get_locator_rec.uom_code
                                 ,x_DESCRIPTION                 =>    NULL
                                 ,x_PARENT_LOCATION_ID          =>    NULL
                                 ,x_INTERFACE_FLAG              =>    NULL
                                 ,x_REQUEST_ID                  =>    NULL
                                 ,x_PROGRAM_APPLICATION_ID      =>    NULL
                                 ,x_PROGRAM_ID                  =>    NULL
                                 ,x_PROGRAM_UPDATE_DATE         =>    NULL
                                 ,x_STATUS                      =>    'A'
                                 ,x_PROPERTY_ID                 =>    NULL
                                 ,x_ATTRIBUTE_CATEGORY          =>    'BUILDING'
                                 ,x_ATTRIBUTE1                  =>    r_get_locator_rec.technicl_siteattr1
                                 ,x_ATTRIBUTE2                  =>    r_get_locator_rec.name_operator_attr2
                                 ,x_ATTRIBUTE3                  =>    r_get_locator_rec.site_Type1_attr3
                                 ,x_ATTRIBUTE4                  =>    r_get_locator_rec.site_Type2_attr4
                                 ,x_ATTRIBUTE5                  =>    r_get_locator_rec.indus_infra_attr5
                                 ,x_ATTRIBUTE6                  =>    r_get_locator_rec.circuit_name_Attr6
                                 ,x_ATTRIBUTE7                  =>    NULL
                                 ,x_ATTRIBUTE8                  =>    NULL
                                 ,x_ATTRIBUTE9                  =>    NULL
                                 ,x_ATTRIBUTE10                 =>    NULL
                                 ,x_ATTRIBUTE11                 =>    NULL
                                 ,x_ATTRIBUTE12                 =>    r_get_locator_rec.inout_door_attr12
                                 ,x_ATTRIBUTE13                 =>    NULL
                                 ,x_ATTRIBUTE14                 =>    NULL
                                 ,x_ATTRIBUTE15                 =>    NULL
                                 ,x_address_line1               =>    r_get_locator_rec.address_line1
                                 ,x_address_line2               =>    r_get_locator_rec.address_line2
                                 ,x_address_line3               =>    r_get_locator_rec.address_line3
                                 ,x_address_line4               =>    r_get_locator_rec.address_line4
                                 ,x_county                      =>    NULL
                                 ,x_city                        =>    r_get_locator_rec.city
                                 ,x_state                       =>    r_get_locator_rec.state
                                 ,x_province                    =>    r_get_locator_rec.province
                                 ,x_zip_code                    =>    r_get_locator_rec.zip_code
                                 ,x_country                     =>    r_get_locator_rec.country
                                 ,x_territory_id                =>    NULL
                                 ,x_addr_last_update_date       =>    NULL
                                 ,x_addr_last_updated_by        =>    NULL
                                 ,x_addr_creation_date          =>    NULL
                                 ,x_addr_created_by             =>    NULL
                                 ,x_addr_last_update_login      =>    NULL
                                 ,x_addr_attribute_category     =>    NULL
                                 ,x_addr_attribute1             =>    NULL
                                 ,x_addr_attribute2             =>    NULL
                                 ,x_addr_attribute3             =>    NULL
                                 ,x_addr_attribute4             =>    NULL
                                 ,x_addr_attribute5             =>    NULL
                                 ,x_addr_attribute6             =>    NULL
                                 ,x_addr_attribute7             =>    NULL
                                 ,x_addr_attribute8             =>    NULL
                                 ,x_addr_attribute9             =>    NULL
                                 ,x_addr_attribute10            =>    NULL
                                 ,x_addr_attribute11            =>    NULL
                                 ,x_addr_attribute12            =>    NULL
                                 ,x_addr_attribute13            =>    NULL
                                 ,x_addr_attribute14            =>    NULL
                                 ,x_addr_attribute15            =>    NULL
                                 ,x_common_area_flag            =>    NULL
                                 ,x_active_start_date           =>    SYSDATE
                                 ,x_active_end_date             =>    NULL
                                 ,x_return_status               =>    v_return_status   --out parameter
                                 ,x_return_message              =>    v_return_msg      --out parameter
                                 ,x_bookable_flag               =>    NULL
                                 ,x_change_mode                 =>    NULL
                                 ,x_occupancy_status_code       =>    'Y'
                                 ,x_assignable_emp              =>    'Y'
                                 ,x_assignable_cc               =>    'Y'
                                 ,x_assignable_cust             =>    'Y'
                                 ,x_disposition_code            =>    NULL
                                 ,x_acc_treatment_code          =>    NULL
                                 ,x_source                      =>    NULL
                           --     , x_address_style              =>    NULL
                                );