Analytics Software

Hi
I am trying to call an Oracle API from an SQR report running in PeopleSoft 8.1.

The code I am using is shown below:

begin-sql
declare

      LN_PERSON_ID NUMBER;;
      LN_ASSIGNMENT_ID  NUMBER;;
      LN_PER_OBJECT_VERSION_NUMBER NUMBER(9);;
      LN_ASG_OBJECT_VERSION_NUMBER NUMBER(9);;
      LD_PER_EFFECTIVE_START_DATE  DATE;;
      LD_PER_EFFECTIVE_END_DATE DATE;;
      LV_FULL_NAME VARCHAR(80);;
      LN_PER_COMMENT_ID NUMBER;;
      LN_ASSIGNMENT_SEQUENCE NUMBER;;
      LV_ASSIGNMENT_NUMBER VARCHAR(30);;
      LB_NAME_COMBINATION_WARNING BOOLEAN;;
      LB_ASSIGN_PAYROLL_WARNING BOOLEAN;;
      LB_ORIG_HIRE_WARNING BOOLEAN;;
      LN_EMPLID VARCHAR(6);;
         lv_err_code VARCHAR(1000);;
          lv_err_msg VARCHAR(1000);;

begin
             LN_EMPLID := $Emplid;;
      
            HR_EMPLOYEE_API.CREATE_EMPLOYEE@OPAYR(
            p_validate => FALSE            
            ,p_hire_date                    => to_date($Effdt, 'dd-mon-yyyy')
            ,p_business_group_id            => 83
            ,p_last_name                    => $Last-Name 
            ,p_sex                          => $Sex 
            ,P_FIRST_NAME                   => $First_Name 
            ,P_MARITAL_STATUS               => $Mar-Status
            ,P_MIDDLE_NAMES                 => $Middle-Name
            ,P_NATIONAL_IDENTIFIER          => $National-Id  
            ,P_TITLE                        => $Prefix   
            ,P_PER_INFORMATION1             => $Tax_Reference-No  
            ,P_PER_INFORMATION2             => $Passport-Number
            ,P_PER_INFORMATION4             => $Ethnic-Group 
            ,P_ORIGINAL_DATE_OF_HIRE        => to_date($Effdt, 'dd-mon-yyyy')
            ,p_date_of_birth                => to_date($Birthdate, 'dd-mon-yyyy')
            ,p_attribute1                   => $Nc-Legacy-Company 
            ,p_person_type_id               => 1145
          
            ,p_employee_number              =>   LN_EMPLID
            ,p_person_id                    => LN_PERSON_ID 
            ,p_assignment_id                => LN_ASSIGNMENT_ID 
            ,p_per_object_version_number    => LN_PER_OBJECT_VERSION_NUMBER
            ,p_asg_object_version_number    => LN_ASG_OBJECT_VERSION_NUMBER
            ,p_per_effective_start_date     => LD_PER_EFFECTIVE_START_DATE
            ,p_per_effective_end_date       => LD_PER_EFFECTIVE_END_DATE
            ,p_full_name                    => LV_FULL_NAME  
            ,p_per_comment_id               => LN_PER_COMMENT_ID
            ,p_assignment_sequence          => LN_ASSIGNMENT_SEQUENCE 
            ,p_assignment_number            => LV_ASSIGNMENT_NUMBER 
            ,p_name_combination_warning     => LB_NAME_COMBINATION_WARNING 
            ,p_assign_payroll_warning       => LB_ASSIGN_PAYROLL_WARNING
            );;



 exception
    when others then
 
   lv_err_code := sqlcode;;
   lv_err_msg := sqlerrm;;    
   
  raise_application_error(-20000,'API ERROR '||lv_err_code||' on '||lv_err_msg);;




end;;

end-sql
                     

End-Procedure 

I cannot ge this to work correctly. It seems as if the PL/SQL embedded in the Begin-sql is not parsing the variables. The error recevied is:

Start of Program Oracle Payslip Interface
(SQR 5528) ORACLE OCIStmtExecute error 20000 in cursor 23:
ORA-20000: API ERROR -20001 on ORA-20001: HR_ZA_MAND_SEG_RACE:
ORA-06512: at "APPS.HR_EMPLOYEE_API", line 1019
ORA-06512: at "APPS.HR_EMPLOYEE_API", line 1186
ORA-06512: at line 1

This error refers to the value assigned to P_PER_INFORMATION4. I have confrimed that there is a valid value in the $Ethnic-Group variable but this value does not seem to be parsing to the PL/SQL. Any assistance would be greatly appreciated.

Regards
Muhammad