CREATE OR REPLACE package body APPS.xxiron_pn_properties_pkg
 is
 procedure xxiron_pn_properties_proc(errbuf out varchar2,retcode OUT NUMBER)
 is 
    cursor c_pn_properties is
    select rowid,e.* from XXIRON_PN_PROPERTIES_ALL e
    where 1=1
    and process_flag is null;
    v_error_msg varchar2(4000);
    v_error_flag varchar2(1);
    v_country fnd_territories_tl.territory_code%type;
    v_status  fnd_lookups.lookup_code%type;
    v_tenure  fnd_lookups.lookup_code%type;
    v_zone    fnd_lookups.lookup_code%type;
    v_portfolio fnd_lookups.lookup_code%type;
    v_class     fnd_lookups.lookup_code%type;
    v_condition fnd_lookups.lookup_code%type;
    v_organization_id number;
    v_row_id varchar2(4000);
    v_property_id number;
    v_user_id                        NUMBER;
    v_resp_id                        NUMBER;
    v_resp_appl_id                   NUMBER;
  begin
      v_user_id := fnd_profile.VALUE ('USER_ID');
      v_resp_id := fnd_profile.VALUE ('RESP_ID');
      v_resp_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');
      fnd_global.apps_initialize (user_id           => v_user_id,
                                  resp_id           => v_resp_id,
                                  resp_appl_id      => v_resp_appl_id    
                                 );
      for r_pn_properties in c_pn_properties loop
        v_error_msg := null;
        v_error_flag := null;
        v_country   := null;
        v_status    := null;
        v_tenure    := null;
        v_zone      := null;
        v_portfolio := null;
        v_class     := null;
        v_condition := null;
        v_organization_id := null;
        v_row_id := null;
        v_property_id := null;
           /* country code validation*/
            begin
              select territory_code
              into v_country
              from  fnd_territories_tl 
              where 1=1
              and upper(territory_short_name) = upper(r_pn_properties.COUNTRY)
              And Language = Userenv ('LANG');
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := 'Invalid Country Name ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := ' Country Name having '||SQLERRM;
              end;
            /* End of country code validation*/
            /* Property Status validation*/
            Begin 
               select lookup_code
                into v_status
                from fnd_lookups 
                where 1=1
                and lookup_type = 'PN_STATUS_TYPE'
                and upper(meaning) = upper(r_pn_properties.PROPERTY_STATUS)
                and enabled_flag = 'Y';
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Property Status ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Property Status having '||SQLERRM;
             end;
            /* End of Property Status validation*/
            /* Tenure validation*/ 
            Begin 
               select lookup_code
                into v_tenure
                from fnd_lookups 
                where 1=1
                and lookup_type = 'PN_LEASED_OR_OWNED'
                and upper(meaning) = upper(r_pn_properties.TENURE)
                and enabled_flag = 'Y';
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Tenure ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Tenure having '||SQLERRM;
             end;
            /* End of Tenure validation*/
          /* Zone validation*/  --need to ask whether meaning or description will insert into table
            Begin 
               select lookup_code
                into v_zone
                from fnd_lookups 
                where 1=1
                and lookup_type = 'PN_ZONE_TYPE'
                and upper(meaning) = upper(r_pn_properties.zone)
                and enabled_flag = 'Y';
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Zone ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Zone having '||SQLERRM;
             end;
            /* End of zone validation*/
             /* Portfolio validation*/ 
               Begin 
               select lookup_code
                into v_portfolio
                from fnd_lookups 
                where 1=1
                and lookup_type = 'PN_PORTFOLIO_TYPE'
                and upper(meaning) = upper(r_pn_properties.PORTFOLIO)
                and enabled_flag = 'Y';
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Portfolio ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Portfolio having '||SQLERRM;
             end;
            /* End of portfolio validation*/
             /* Class validation*/ 
               Begin 
               select lookup_code
                into v_class
                from fnd_lookups 
                where 1=1
                and lookup_type = 'PN_CLASS_TYPE'
                and upper(meaning) = upper(r_pn_properties.CLASS)
                and enabled_flag = 'Y';
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Class ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Class having '||SQLERRM;
             end;
            /* End of Class validation*/
            /* Condition validation*/ 
               Begin 
               select lookup_code
                into v_condition
                from fnd_lookups 
                where 1=1
                and lookup_type = 'PN_CONDITION_TYPE'
                and upper(meaning) = upper(r_pn_properties.CONDITION)
                and enabled_flag = 'Y';
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Condition ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Condition having '||SQLERRM;
             end;
            /* End of Condition validation*/
           /* Validation on operating unit*/
              Begin 
               select organization_id
                into v_organization_id
                from hr_operating_units  
                where 1=1
                and upper(name) = upper(r_pn_properties.org_code);
               exception 
               when no_data_found then 
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||'and Invalid Operating Unit ';
               when others then
               v_error_flag := 'Y';
               v_error_msg := v_error_msg||' and Operating Unit having '||SQLERRM;
             end;
              /* End validation on operating unit*/
           if  v_error_flag is null then
           PNT_PROPERTIES_PKG.INSERT_ROW(
                       v_organization_id,
                       v_row_id,
                       v_property_id,
                       sysdate,
                       FND_GLOBAL.user_id,
                       sysdate,
                       FND_GLOBAL.user_id,
                       FND_GLOBAL.user_id,
                       r_pn_properties.PROPERTY_NAME,
                       r_pn_properties.PROPERTY_CODE,
                       null,
                       v_zone,
                       r_pn_properties.DISTRICT,
                       v_country,
                       r_pn_properties.DESCRIPTION,
                       v_portfolio,
                       v_tenure,
                       v_class,
                       v_status,
                       v_condition,
                       'A',
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        null
                       );
           fnd_file.put_line(fnd_file.log,'Property Id'||v_property_id||' For Property Name'||r_pn_properties.PROPERTY_NAME); 
             if  v_property_id is not null then
                  update XXIRON_PN_PROPERTIES_ALL
                  set PROCESS_FLAG = 'P',last_updated_by = fnd_global.user_id,last_update_date = sysdate
                  where 1=1
                  and rowid = r_pn_properties.rowid;
                  else
                  update XXIRON_PN_PROPERTIES_ALL
                  set error_message = 'Error while processing through API',PROCESS_FLAG = 'E',last_updated_by = fnd_global.user_id,last_update_date = sysdate
                  where 1=1
                  and rowid = r_pn_properties.rowid;
              end if;
             else
             fnd_file.put_line(fnd_file.log,'Error message for Property code '||r_pn_properties.PROPERTY_CODE||' is '||v_error_msg);
                  update XXIRON_PN_PROPERTIES_ALL
                  set error_message = v_error_msg,PROCESS_FLAG = 'E',last_updated_by = fnd_global.user_id,last_update_date = sysdate
                  where 1=1
                  and rowid = r_pn_properties.rowid;
             end if;
      end loop;
      commit;
  end xxiron_pn_properties_proc;
 end xxiron_pn_properties_pkg; 
/