While reading data generated using a BIP report in OIC to filter data using certain business requirement, I was not able to read the special characters in OIC from the BIP generated file even though data was coming file in Notepad++ if I checked the BIP generated file.
The BIP template was eText and the issue was in that where I was using the character set as iso-8859-1 instead of utf-8 which was causing the output file to be generated in ANSI encoding format and in OIC I was trying to read the file using UTF-8.
So to resolve this issue, we need to read the file as ISO8859_1 in OIC and keep the etext character set as iso-8859-1 or else we need to change the character set to utf-8 in etext template and read it as UTF-8 in OIC. This is a simple thing which was overlooked and took a lot of my time.
METADATA|WorkRelationship|LegalEmployerName|PersonNumber|DateStart|WorkerType|ActualTerminationDate|EnterpriseSeniorityDate|LastWorkingDate|OnMilitaryServiceFlag|PrimaryFlag|GlobalTransferFlag|ActionCode|RehireRecommendationFlag
MERGE|WorkRelationship|AL Legal Employer Name|20000001|2021/12/01|E||2019/06/01||N|Y|Y|GLB_TRANSFER|Y
METADATA|WorkTerms|ActionCode|AssignmentStatusTypeCode|AssignmentNumber|AssignmentType|WorkerType|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|GradeCode|PeriodOfServiceId(SourceSystemId)|PersonNumber|DateStart|LegalEmployerName|BusinessUnitId|PersonTypeCode
MERGE|WorkTerms|GLB_TRANSFER|ACTIVE_PROCESS|ET20000001-1|ET|E|4712/12/31|Y|1|2021/12/01|Grad1||20000001|2021/12/01|AL Legal Employer Name|300000045830045|Regular
METADATA|Assignment|ActionCode|PersonNumber|EffectiveStartDate|EffectiveEndDate|AssignmentNumber|WorkTermsNumber|AssignmentType|EffectiveSequence|EffectiveLatestChange|BusinessUnitId|DateProbationEnd|WorkerCategory|AssignmentCategory|GradeCode|HourlySalariedCode|PositionCode|JobCode|LocationId|ManagerFlag|OrganizationId|DateStart|LegalEmployerName|PersonTypeCode|PrimaryAssignmentFlag|AssignmentStatusTypeCode|WorkerType
MERGE|Assignment|GLB_TRANSFER|20000001|2021/12/01|4712/12/31|E20000001-1|ET20000001-1|E|1|Y|300000045830045||Senior Management|Full-time Regular|Grad1|S|123134||300000002378027||300000001871927|2021/12/01|AL Legal Employer Name|Regular|Y|ACTIVE_PROCESS|E
METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|LegalEmployerName|CancelWorkRelationshipFlag
/***************************************************************************
FORMULA NAME: XYZ_ANNUAL_LEAVE_PRORATION_FF
FORMULA TYPE: Global Absence Partial Period Accrual Rate Formula
DESCRIPTION: This formula returns the accrual for mid-period enrollments and un-enrollments for Annual Leave absence plan
Change History:
Name Date Version Comments
-------------------------------------------------------------------------------
Aditya DRAFT 1A Initial Version
*******************************************************************************/
DEFAULT FOR IV_ACCRUAL IS 0
DEFAULT FOR ACP_HIRE_DATE IS '1901/01/01 00:00:00' (date)
DEFAULT FOR ACP_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_CALEDARSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_CALEDARENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'
INPUTS are IV_ACCRUAL,IV_ACCRUALPERIODSTARTDATE,IV_ACCRUALPERIODENDDATE,IV_PLANENROLLMENTSTARTDATE,IV_PLANENROLLMENTENDDATE,IV_CALEDARSTARTDATE,IV_CALEDARENDDATE
ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ld_effective_date= GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))
ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)
LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)
ld_hire_date=ACP_HIRE_DATE
ld_term_date=ACP_TERMINATION_DATE
/*to check if hire date is on or before 15th of the month*/
ld_st_date= GREATEST(GREATEST(ld_hire_date,IV_ACCRUALPERIODSTARTDATE),IV_PLANENROLLMENTSTARTDATE)
ln_st_day = to_number(to_char(ld_st_date,'DD'))
if ln_st_day > 15 THEN
(
ld_accrual_period_st_date = ADD_DAYS(LAST_DAY(ld_st_date),1)/*accrual start date is from next month*/
)
else
(
ld_accrual_period_st_date = ADD_MONTHS(ADD_DAYS(LAST_DAY(ld_st_date),1),-1)/*accrual start date should be from 1st of current month*/
)
/*to check if end date is on or after 15th of the month*/
ld_end_date= LEAST(LEAST(ld_term_date,IV_ACCRUALPERIODENDDATE),IV_PLANENROLLMENTENDDATE)
ln_end_day= to_number(to_char(ld_end_date,'DD'))
if ln_end_day <= 15 THEN
(
ld_accrual_period_end_date = ADD_MONTHS(LAST_DAY(ld_end_date),-1)
)
else
(
ld_accrual_period_end_date = LAST_DAY(ld_end_date)
)
ln_term_duration = 1 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'yy')) - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'yy'))) * 12 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'mm')) - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'mm')))
/*prorationFactor = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)/12*/
ln_participation_duration = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)
ln_prorated_accrual = IV_ACCRUAL * (ln_participation_duration / ln_term_duration)
accrual = round(ln_prorated_accrual)
RETURN accrual
