Search for:
Mar 31 2021
HDL – Loading Multiple attachments for same document type

There are various cases where more than one attachment should be maintained against employee document record. First of all, we will create a new document type where multiple attachments will be allowed.

  1. Navigate to Setup and Maintenance -> Document Types -> Click on Create

2. Choose multiple occurrences as Yes:

3. Enter the relevant information and submit the record.

4. Prepare the HDL in below format and load the data:

METADATA|DocumentsOfRecord|PersonNumber|DateFrom|DateTo|DocumentType|DocumentName|DocumentNumber|DocumentCode|Country|AttachmentEntityName|Comments|IssuedDate|IssuingAuthority|IssuingCountry|IssuingLocation|Publish|PublishDate|SourceSystemId|SourceSystemOwner
MERGE|DocumentsOfRecord|101|2021/01/01||Test Multi Attachments Doc|Multiple Attachments||TMAD_001||||||||||HRC_SQLLOADER_101_TMAD_001|HRC_SQLLOADER

METADATA|DocumentAttachment|SourceSystemOwner|SourceSystemId|PersonNumber|DocumentType|DocumentCode|Title|Description|DataTypeCode|Country|EntityName|File|FileName|MimeType|URL|URLorTextorFileName|DmVersonNumber
MERGE|DocumentAttachment|HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001_1|101|Test Multi Attachments Doc|TMAD_001|Test Multi Attachments Doc|Test Multi Attachments Doc 1|FILE|||Historical_rating.docx|Historical_rating.docx|||Historical_rating.docx|
MERGE|DocumentAttachment|HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001_2|101|Test Multi Attachments Doc|TMAD_001|Test Multi Attachments Doc|Test Multi Attachments Doc 2|FILE|||Historical_rating1.docx|Historical_rating1.docx|||Historical_rating1.docx|

5. Place the docx files in BlobFiles folder and zip with the .dat file:

6. Import and Load the file.

7. Once the data is loaded, verify from the UI:

Mandeep Gupta 0 Comments
Mar 30 2021
Reports (BIP) – Query to get Department Manager Name
SELECT distinct houft.name, 
       ppnfv.full_name
FROM HR_ORGANIZATION_INFORMATION_F hoif,
     per_person_names_f_v ppnfv,
     hr_organization_units_f_tl houft
WHERE trunc(sysdate) BETWEEN hoif.EFFECTIVE_START_DATE AND hoif.EFFECTIVE_END_DATE
AND trunc(sysdate) BETWEEN ppnfv.effective_Start_date AND ppnfv.effective_end_date
AND ppnfv.person_id=hoif.ORG_INFORMATION2
AND houft.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_ORG_MANAGER_INFO'
AND houft.language = 'US'
AND TRUNC(sysdate) BETWEEN houft.EFFECTIVE_START_DATE AND houft.EFFECTIVE_END_DATE
Mandeep Gupta 0 Comments
Mar 29 2021
HDL – Loading Contract History Using HDL

Sample HDL file to contract history:

METADATA|Worker|PersonId|EffectiveStartDate|EffectiveEndDate|PersonNumber|BloodType|CorrespondenceLanguage|StartDate|DateOfBirth|DateOfDeath|CountryOfBirth|RegionOfBirth|TownOfBirth|PersonDuplicateCheck|ActionCode|ReasonCode|GUID|SourceSystemOwner|SourceSystemId
MERGE|Worker||2016/08/27|4712/12/31|435282|||2016/08/27|1967/07/23||PH|Philippines|||HIRE|||LEGACY|PER_909090

METADATA|PersonName|PersonNameId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|PersonNumber|LegislationCode|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|PreNameAdjunct|MilitaryRank|PreviousLastName|Suffix|Title|CharSetContext|NameInformation1|NameInformation2|NameInformation3|NameInformation4|NameInformation5|NameInformation6|NameInformation7|NameInformation8|NameInformation9|NameInformation10|NameInformation11|NameInformation12|NameInformation13|NameInformation14|NameInformation15|NameInformation16|NameInformation17|NameInformation18|NameInformation19|NameInformation20|NameInformation21|NameInformation22|NameInformation23|NameInformation24|NameInformation25|NameInformation26|NameInformation27|NameInformation28|NameInformation29|NameInformation30|GUID|SourceSystemOwner|SourceSystemId
MERGE|PersonName||2016/08/27|4712/12/31|PER_909090||US|GLOBAL|||Ashwin Ravi||Ashwin Ravi|||||MR.|||||||||||||||||||||||||||||||||LEGACY|909090_PER_NAME

METADATA|PersonLegislativeData|PersonLegislativeId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|PersonNumber|LegislationCode|HighestEducationLevel|MaritalStatus|MaritalStatusDate|Sex|GUID|SourceSystemOwner|SourceSystemId
MERGE|PersonLegislativeData||2016/08/27|4712/12/31|PER_909090||US||M||M||LEGACY|909090_PER_LEG

METADATA|WorkRelationship|PeriodOfServiceId|LegalEmployerName|PersonId(SourceSystemId)|PersonNumber|DateStart|WorkerType|LegalEmployerSeniorityDate|ActualTerminationDate|LegalEntityId|Comments|EnterpriseSeniorityDate|LastWorkingDate|NotifiedTerminationDate|OnMilitaryServiceFlag|PrimaryFlag|ProjectedTerminationDate|RehireAuthorizerPersonId|RehireAuthorizor|RehireReason|RevokeUserAccess|WorkerNumber|RehireRecommendationFlag|NewStartDate|ActionCode|ReasonCode|DateOfDeath|HideUntilDate|DateForPrimaryFlagChange|CancelWorkRelationshipFlag|TerminateWorkRelationshipFlag|ReverseTerminationFlag|CorrectTerminationFlag|GUID|SourceSystemOwner|SourceSystemId|GlobalTransferFlag|ReadyToConvert|WorkerComments
MERGE|WorkRelationship||US Company|PER_909090|435282|2016/08/27|E||||||||N|Y|||||||||HIRE||||||||||LEGACY|455985_WR||||

METADATA|WorkTerms|AssignmentId|AssignmentNumber|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|LegalEmployerName|DateStart|WorkerType|AssignmentName|PeopleGroup|AssignmentStatusTypeId|AssignmentStatusTypeCode|AssignmentType|PersonTypeId|PersonTypeCode|SystemPersonType|BargainingUnitCode|BillingTitle|BusinessUnitId|BusinessUnitShortCode|ContractId|DateProbationEnd|WorkerCategory|AssignmentCategory|EstablishmentId|ReportingEstablishment|ExpenseCheckSendToAddress|GradeId|GradeCode|GradeLadderPgmId|GradeLadderPgmName|HourlySalariedCode|InternalBuilding|InternalFloor|InternalLocation|InternalMailstop|InternalOfficeNumber|JobId|JobCode|LabourUnionMemberFlag|LocationId|LocationCode|ManagerFlag|NormalHours|Frequency|NoticePeriod|NoticePeriodUOM|OrganizationId|DepartmentName|PositionId|PositionCode|PositionOverrideFlag|PrimaryWorkTermsFlag|ProbationPeriod|ProbationUnit|ProjectedEndDate|ProjectedStartDate|ProposedWorkerType|ProposedUserPersonType|ReasonCode|RetirementAge|RetirementDate|SpecialCeilingStepId|SpecialCeilingStep|StepEntryDate|TaxAddressId|EndTime|StartTime|VendorSiteId|WorkAtHomeFlag|WorkTermsAssignmentId|VendorId|FreezeStartDate|FreezeUntilDate|ActionCode|CategoryCode|TaxReportingUnitId|TaxReportingUnit|GUID|CollectiveAgreementId|CollectiveAgreementIdCode|FullPartTime|PermanentTemporary|SourceSystemOwner|SourceSystemId
MERGE|WorkTerms||ET435282|2017/07/31|Y|1|2016/08/27|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||HIRE|||||||||LEGACY|52361_WT
MERGE|WorkTerms||ET435282|2018/08/26|Y|1|2017/08/01|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||ASG_CHANGE|||||||||LEGACY|52361_WT
MERGE|WorkTerms||ET435282|2019/08/26|Y|1|2018/08/27|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||CONTRACT_EXTENSION|||||||||LEGACY|52361_WT
MERGE|WorkTerms||ET435282|4712/12/31|Y|1|2019/08/27|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||CONTRACT_EXTENSION|||||||||LEGACY|52361_WT

METADATA|Assignment|AssignmentId|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsAssignmentId(SourceSystemId)|WorkTermsNumber|AssignmentType|AssignmentName|AssignmentNumber|PeopleGroup|AssignmentStatusTypeId|AssignmentStatusTypeCode|BargainingUnitCode|BillingTitle|BusinessUnitId|BusinessUnitShortCode|DateProbationEnd|WorkerCategory|AssignmentCategory|EstablishmentId|ReportingEstablishment|ExpenseCheckSendToAddress|GradeId(SourceSystemId)|GradeCode|GradeLadderPgmId|GradeLadderPgmName|HourlySalariedCode|InternalBuilding|InternalFloor|InternalLocation|InternalMailstop|InternalOfficeNumber|JobId(SourceSystemId)|JobCode|LabourUnionMemberFlag|LocationId|LocationCode|ManagerFlag|NormalHours|Frequency|NoticePeriod|NoticePeriodUOM|OrganizationId(SourceSystemId)|DepartmentName|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|DateStart|WorkerType|LegalEmployerName|PersonTypeId|PersonTypeCode|SystemPersonType|PositionId(SourceSystemId)|PositionCode|PositionOverrideFlag|PrimaryAssignmentFlag|PrimaryFlag|ProbationPeriod|ProbationUnit|ProjectTitle|ProjectedEndDate|ProjectedStartDate|ProposedWorkerType|ProposedUserPersonType|ReasonCode|RetirementAge|RetirementDate|SpecialCeilingStepId|SpecialCeilingStep|TaxAddressId|EndTime|StartTime|VendorSiteId|WorkAtHomeFlag|VendorId|FreezeStartDate|FreezeUntilDate|CategoryCode|CollectiveAgreementId|CollectiveAgreementIdCode|FullPartTime|PermanentTemporary|DefaultExpenseAccount|GUID|SourceSystemOwner|SourceSystemId|GspEligibilityFlag|OvertimePeriodName|SeniorityBasis|UnionId|UnionName
MERGE|Assignment||HIRE|2016/08/27|2017/07/31|1|Y|52361_WT|ET435282|E||E435282|121||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||
MERGE|Assignment||ASG_CHANGE|2017/08/01|2018/08/26|1|Y|52361_WT|ET435282|E||E435282|122||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||
MERGE|Assignment||CONTRACT_EXTENSION|2018/08/27|2019/08/26|1|Y|52361_WT|ET435282|E||E435282|122||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||
MERGE|Assignment||CONTRACT_EXTENSION|2019/08/27|4712/12/31|1|Y|52361_WT|ET435282|E||E435282|122||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||

METADATA|Contract|ContractId|EffectiveStartDate|EffectiveEndDate|AssignmentId|AssignmentNumber|PersonNumber|ContractType|Description|Duration|DurationUnits|ExtensionPeriod|ExtensionPeriodUnits|ExtensionReason|PersonId(SourceSystemId)|GUID|SourceSystemOwner|SourceSystemId
MERGE|Contract||2016/08/27|2018/08/26||ET435282|435282|Flexiable 2 Years|Rolling Contract|2|Y||||PER_909090||LEGACY|52361_CONTRACT
MERGE|Contract||2018/08/27|2019/08/26||ET435282|435282|Flexiable 2 Years|Rolling Contract|||1|Y||PER_909090||LEGACY|52361_CONTRACT
MERGE|Contract||2019/08/27|4712/12/31||ET435282|435282|Flexiable 2 Years|Rolling Contract|||1|Y||PER_909090||LEGACY|52361_CONTRACT

Version Tested – 20B

Mandeep Gupta 0 Comments
Mar 29 2021
BIP (Reports) – Query to extract Accrual details
select papf.person_number,
aapft.name aapftan_name ,
apae.begin_bal,
apae.accrued,
apae.used,
apae.accrual_period,
apae.end_bal,
apae.first_last_prd_in_aapft_term
from per_all_peoaapfte_f papf,
anc_per_accrual_entries apae,
anc_absence_aapftans_f_tl aapft
where 1=1
and papf.person_id = apae.person_id
and apae.aapftan_id= aapft.absence_aapftan_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and aapft.language ='US'
and trunc(sysdate) between aapft.effective_start_date and aapft.effective_end_date
and apae.accrual_period <= TO_DATE('2021/12/31','YYYY/MM/DD')
Mandeep Gupta 0 Comments
Mar 28 2021
Reports (BIP) – Basic Query for Checklist E-sign Report

Oracle provided a new functionality to include e signatures in checklist tasks. A custom report can be configured and upon e-signature and completion, it will be saved to worker’s document of records.

You can follow the below document on my oracle support for detailed steps – 2611795.1

Below is the basic SQL query to start the data model development. You can add additional tables/columns as per your need.

SELECT DISTINCT Person.PERSON_ID
,PAT.ALLOCATED_CHECKLIST_ID AS P_ALLOCATED_CHECKLIST_ID
--,PAT.ALLOCATED_TASK_ID AS TASK_ID
,PersonName.FULL_NAME FULL_NAME
,PersonName.DISPLAY_NAME DISPLAY_NAME
,PersonName.first_name FIRST_NAME
,PersonName.last_name LAST_NAME
,PersonName.middle_names MIDDLE_NAMES
,to_char(Person.date_of_birth,'DD-MM-RRRR', 'nls_date_language=American') DOB
,(SELECT flv.meaning
FROM FND_LOOKUP_VALUES flv
WHERE flv.lookup_type = 'TITLE'
AND flv.language = 'US'
AND flv.lookup_code = PersonName.title) TITLE
--,PAT.SIGNER_NAME AS P_SIGNER_NAME
--,TO_CHAR(PAT.SIGN_DATE,'MM/DD/YY') AS P_SIGN_DATE
,:P_SIGNER_NAME P_SIGNER_NAME
,:P_SIGN_DATE P_SIGN_DATE
FROM PER_PERSONS Person,
PER_PERSON_NAMES_F PersonName,
PER_ALLOCATED_CHECKLISTS PAC,
PER_ALLOCATED_TASKS_VL PAT
WHERE PAC.ALLOCATED_CHECKLIST_ID = NVL(:P_ALLOCATED_CHECKLIST_ID,PAC.ALLOCATED_CHECKLIST_ID)
AND PAC.ALLOCATED_CHECKLIST_ID=PAT.ALLOCATED_CHECKLIST_ID
AND PAT.PERFORMER_ORIG_SYS_ID = Person.PERSON_ID
AND PersonName.PERSON_ID = Person.PERSON_ID
AND PersonName.NAME_TYPE = 'GLOBAL'
AND GREATEST(TRUNC(PAC.ACTION_DATE),TRUNC(SYSDATE)) BETWEEN LEAST(TRUNC(SYSDATE),PersonName.EFFECTIVE_START_DATE) AND PersonName.EFFECTIVE_END_DATE
AND PAT.STATUS='COM'
AND PAT.allocated_task_id=3000012222113131
Mandeep Gupta 0 Comments
Mar 28 2021
BIP Reports – Query to get Position Profile Attachment details

While creating a position profile, there is an option to add the attachments. In order to get the various details of the attachment like name, UCM content ID etc, please use the below query:

select hpb.PROFILE_TYPE_ID,
       PROFILE_CODE,
       PROFILE_STATUS_CODE,
       PROFILE_USAGE_CODE,
       fad.CATEGORY_NAME ,
       fdt.file_name,
       fdt.dm_version_number document_id,
       fdt.dm_document_id UCM_file
  from fnd_attached_documents fad, HRT_PROFILES_B hpb, fnd_documents_tl fdt
 where ENTITY_NAME = 'HRT_PROFILES_B'
   and hpb.profile_id = fad.PK1_VALUE
   AND fad.document_id = fdt.document_id
   AND fdt.language = 'US'

In order to download the attchments from UCM, the user should have AttachmentsRead role attached. Please check the below post on how to create AttachmentsRead role:

https://fusionhcmconsulting.com/2021/03/security-role-to-view-document-of-record-attachments-on-ucm/

Mandeep Gupta 0 Comments
Mar 24 2021
Configuration – Extract DFF Setup

Use below query to extract the DFF setups defined in Fusion:

SELECT
 fdsv.descriptive_flexfield_code
      ,fdsv.context_code
      ,fdsv.segment_code
      ,fdsv.name
      ,fdsv.column_name
      ,fvvs.value_set_code
      ,fdsv.display_type
      ,fdsv.prompt
      ,fdsv.short_prompt
      ,fdsv.enabled_flag
      ,fdsv.required_flag
      ,fdsv.read_only_flag
      ,fdsv.description
      ,fdsv.sequence_number
      ,fdsv.derivation_value
      ,fdsv.bi_enabled_flag
  FROM
 fnd_df_segments_vl fdsv,
       fnd_vs_value_sets fvvs
 WHERE fdsv.value_set_id = fvvs.value_set_id(+)
   AND fdsv.descriptive_flexfield_code = 'PER_ASG_DF'
Mandeep Gupta 0 Comments
Mar 7 2021
Reports (BIP) – Query to get child request details

When a scheduled request is submitted, the details are stored in ess_request_history table. If the submitted request has no child requests, it will have only one row in the table.

The ParentId attribute in this case will be 0. However, if the parent request invokes multiple child jobs, the parentId attribute will have the requestid of previous request. In order to segregate the all child jobs submitted by a parent request, one need to use absParentId attribute in the table.

Below is the sample query:

SELECT REQUESTID,APPLICATION,NAME,ABSPARENTID,PARENTREQUESTID,INSTANCEPARENTID
  FROM ess_request_history
 WHERE ABSPARENTID = 2394562 --parent request id
 ORDER BY requestid DESC
Mandeep Gupta 0 Comments
Mar 7 2021
Reports (BIP) – Query to get Assignee details for a course
SELECT warf.ASSIGNMENT_RECORD_ID,
warf.EVENT_TYPE,
we.EVENT_TYPE we_event_type,
warf.EVENT_SUB_TYPE,
we.EVENT_SUB_TYPE we_EVENT_SUB_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.EVENT_CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) created_By,
we.CREATED_BY_ID "Assigned_By_Person_Id",
(SELECT person_number
FROM per_all_people_f
WHERE person_id = we.CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) "Assigned_By",
warf.ATTRIBUTION_TYPE,
we.ATTRIBUTION_TYPE we_ATTRIBUTION_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.ATTRIBUTION_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) attribution_id,
warf.ATTRIBUTION_ID warf_ATTRIBUTION_ID,
we.ATTRIBUTION_ID we_ATTRIBUTION_ID,
warf.learner_id,
warf.ATTRIBUTION_LOOKUP_CODE,
warf.EVENT_ASSIGNMENT_ID
from WLF_ASSIGNMENT_RECORDS_F warf,
WLF_EVENT_ASSIGNMENTS_F weaf,
WLF_EVENTS we
where warf.EVENT_ASSIGNMENT_ID = weaf.EVENT_ASSIGNMENT_ID
and trunc(sysdate) between warf.effective_start_date and warf.effective_end_date
and trunc(sysdate) between weaf.effective_start_date and weaf.effective_end_date
and we.event_id = weaf.event_id
Mandeep Gupta 0 Comments
Mar 7 2021
Reports (BIP) – Query to extract default Access Group data for a Course/Offering

In Oracle Learning cloud, Learning Admin can define the Default access at Course or offering level. Course.dat can be used to load this information.

In order to extract this information from DB in HDL format, please use below query:

select 'MERGE' "METADATA"       
      ,'CourseDefaultAccess' "CourseDefaultAccess"                  ,to_char(wapf.effective_start_date,'RRRR/MM/DD') "EffectiveStartDate"           ,to_char(wapf.effective_end_date,'RRRR/MM/DD') "EffectiveEndDate"       ,wapf.ACCESS_PERMISSION_NUMBER "DefaultAccessNumber"       
,wlif.learning_item_number "CourseNumber"       
,wapf.FOLLOW_SPEC "FollowSpecialization"       
,wapf.ASSIGN_AS_MANAGER                              "MgrInitialStatus"       ,wapf.ASSIGN_FOR_SELF                                "SelfInitialStatus"        ,wlif.SS_VIEW_MODE                                 "SelfViewMode"    
 from WLF_ACCESS_PERMISSIONS_F wapf      
     ,WLF_LEARNING_ITEMS_F wlif 
where 1=1   
  and wapf.access_permission_id= wlif.access_permission_id   
--and trunc(sysdate) between wapf.effective_start_date and wapf.effective_end_date   
  and trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date   
  and wlif.learning_item_number = 'OLC251051'
Mandeep Gupta 0 Comments
Mar 4 2021
Security – Role to view document of record attachments on UCM

Document of record attachments uploaded by workers in the Fusion HCM resides on UCM server. If there is a requirement to download or view the uploaded attachment on UCM, none of the standard roles like HR Analyst, HR Specialist have the privilege to view the DOR attachments on UCM. To achieve this, a custom role should be created. Please follow the below mentioned steps:

  1. Create a new abstract role ‘XXX Read Attachments’ and add ‘AttachmentsRead’ standard role to it.

2. Once the role is ready, assign it to the required User and run ‘Import User and Role Application Security Data’ process.

3. On successful completion of the process, the user will be able to search any of the document of record attachment using content ID in UCM server and will be able to download the same.

Mandeep Gupta 0 Comments
Mar 4 2021
Reports (BIP) – Query to get correct time zone

When an activity in learning is created with a start time and end time, the time is stored in UTC format in the data base. When any reporting is done on this data, the output is incorrect as time is shown in UTC format which is different from user time format.

This is applicable for all transactions. Please use the below workaorund to get the correct date time in the SQL:

select wlif.start_date
      ,wlaf.time_zone
      ,TZ_OFFSET(wlaf.time_zone) time_zone_offset
      ,(FROM_TZ(CAST(wlif.start_date AS TIMESTAMP),(case when TZ_OFFSET(wlaf.time_zone)     like '+%'
then '-'||substr(TZ_OFFSET(wlaf.time_zone),2)
else '+'||substr(TZ_OFFSET(wlaf.time_zone),2)
end
))) actual_start_date
      ,TO_CHAR(CAST(wlif.start_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') actual_start_time
 from wlf_learning_items_f wlif
    , wlf_li_activities_f wlaf
where learning_item_number= 'OLC245017'
  and wlif.learning_item_id = wlaf.learning_item_id
  and trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date
  and trunc(sysdate) between wlaf.effective_start_date and wlaf.effective_end_date
Mandeep Gupta 0 Comments