BIP – Query to check if employee bank account is active or not
select ext_bank_account_id
,case when nvl(end_date,trunc(sysdate)) >= trunc(sysdate)
then 'Active'
else 'Inactive'
end active_inactive
from IBY_EXT_BANK_ACCOUNTSHDL – Sample HDL to Update User Table value
SQL Query to extract data for a particular value of a user table:
SELECT DATA
FROM (
SELECT 'METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT
'MERGE'
||'|'||
'UserDefinedTableColumnInstance'
||'|'||
to_char(fuci.EFFECTIVE_END_DATE,'YYYY/MM/DD')
||'|'||
to_char(fuci.EFFECTIVE_START_DATE,'YYYY/MM/DD')
||'|'||
fuci.LEGISLATIVE_DATA_GROUP_ID
||'|'||
fuci.USER_COLUMN_ID
||'|'||
fuci.USER_COLUMN_INSTANCE_ID
||'|'||
fuci.USER_ROW_ID
||'|'||
fuci.Value
||'|'||
hikm.source_system_owner
||'|'||
hikm.source_system_id
||'|'||
fuc.USER_TABLE_ID
DATA, 2 DATA_ROW
FROM
FF_USER_COLUMN_INSTANCES_F fuci,
FF_USER_COLUMNS fuc,
FF_USER_TABLES ft,
HRC_INTEGRATION_KEY_MAP hikm
WHERE fuci.USER_COLUMN_ID=fuc.USER_COLUMN_ID
AND fuc.USER_TABLE_ID=ft.USER_TABLE_ID
AND hikm.surrogate_id = fuci.USER_COLUMN_INSTANCE_ID
AND ft.BASE_USER_TABLE_NAME = 'TEST_WAGE'
) ORDER BY DATA_ROWSample HDL:
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|300000046974912|300000175181219|300000175188123|300000175187989|5.20|FUSION|300000175144123|300000175181333BIP – HDL to update Grade Rate values
SQL to extract data in HDL Format:
SELECT DATA
FROM(
SELECT 'METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 'MERGE|GradeRateValue|'
||
hikm.source_system_id
||'|'||
hikm.source_system_owner
||'|'||
TO_CHAR(prvf.effective_start_date,'YYYY/MM/DD')
||'|'||
TO_CHAR(prvf.effective_end_date,'YYYY/MM/DD')
||'|'||
prvf.rate_id
||'|'||
pgf.grade_code
||'|'||
fssv.set_code
||'|'||
ldg.name
||'|'||
prvf.minimum
||'|'||
prvf.maximum
||'|'||
prvf.mid_value
||'|'||
prvf.value DATA, 2 DATA_ROW
FROM per_rate_values_f prvf,
per_legislative_data_groups_tl ldg,
per_grades_f pgf,
fnd_setid_sets_vl fssv,
per_rates_f pr,
hrc_integration_key_map hikm
where 1=1
and pr.legislative_data_group_id = ldg.legislative_data_group_id
and ldg.language = USERENV('LANG')
and trunc(sysdate) between prvf.effective_start_date and prvf.effective_end_date
and upper(prvf.rate_object_type) = 'GRADE'
and prvf.rate_object_id = pgf.grade_id
and trunc(sysdate) between pgf.effective_start_date and pgf.effective_end_date
AND pgf.set_id=fssv.set_id
and prvf.rate_id = pr.rate_id
and trunc(sysdate) between pr.effective_start_date and pr.effective_end_date
and hikm.surrogate_id = prvf.rate_value_id
) ORDER BY DATA_ROWSample HDL:
METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount
MERGE|GradeRateValue|300000107518119|FUSION|1951/01/01|4712/12/31|300000106295381|ADMIN05|COMMON|KZ Legislative Data Group|870|962|913.5|BIP – Query to extract Person Address Details
select
distinct
PAPF.PERSON_NUMBER,
PAPF.PERSON_ID,
PPAUF.PERSON_ADDR_USAGE_ID "ADDRESS_ID",
TO_CHAR(PA.EFFECTIVE_START_DATE,'YYYY/MM/DD')"EFFECTIVE_START_DATE",
TO_CHAR(PA.EFFECTIVE_END_DATE,'YYYY/MM/DD')"EFFECTIVE_END_DATE",
PPAUF.ADDRESS_TYPE,
PA.ADDRESS_LINE_1,
PA.ADDRESS_LINE_2,
PA.ADDRESS_LINE_3
from
PER_ADDRESSES_F PA,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_ADDR_USAGES_F PPAUF
where
PAPF.PERSON_ID = PPAUF.PERSON_ID(+)
AND PPAUF.ADDRESS_ID = PA.ADDRESS_ID(+) HDL – Sample file to update Default Expense Account
METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2002/06/16|4712/12/31|300000066966135|FUSION|HIRE
METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|DefaultExpenseAccount
MERGE|Assignment|300000066966140|300000066966135|Y|1|2002/06/16|4712/12/31|300000066966140|FUSION|HIRE|143-000-215123-0000-000-0000-0000