> 持续流程改进 > 招聘与人才招募 > Oracle HCM Recruiting > 数据模板

招聘与人才招募数据模板

Oracle HCM Recruiting

← 返回改进指南

招聘与人才招募属性(18) 招聘与人才招募活动(14) 数据提取指南(2)

招聘与人才招募数据模板

此模板为收集优化招聘与人才招募流程所需的数据提供完整指南，涵盖关键数据属性、需要跟踪的重要活动，以及从源系统提取这些信息的实用说明。您可以使用此资源，顺利完成数据准备工作。

全面分析建议收集的属性
需要跟踪的关键流程活动和里程碑
从系统提取数据的指南

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

招聘与人才招募属性

以下是建议纳入事件日志的数据字段，可用于全面分析招聘与人才招募流程。

5 必填 5 建议填写 8 选填

	名称	说明
	开始时间 EventTime	表示招聘活动开始时间的时间戳。
说明此属性记录具体活动发生的准确日期和时间，为每个职位申请案例中的事件排序提供时间依据。开始时间是所有基于时间的流程挖掘分析的关键数据，可用于计算活动间的周期时间、发现延误、衡量整体招聘周期，并检查是否符合服务级别协议或绩效目标。重要性此属性提供事件的时间顺序，是计算持续时间、发现瓶颈和分析流程绩效的必要依据。获取途径在Oracle HCM Recruiting中，此时间戳通常与活动名称一起记录在事件日志或状态历史表中。示例 2023-10-01T10:00:00Z2023-10-05T14:30:00Z2023-11-12T09:15:00Z
	活动 ActivityName	已发生的具体招聘活动或事件的名称。
说明此属性描述招聘流程中的一个步骤或里程碑，例如“申请已筛选”“面试已进行”或“已发出录用通知”。每项活动都代表申请生命周期中的一个独立事件。活动是流程挖掘的基础组成部分，用于定义流程图中的节点。分析活动的顺序和频次，有助于发现常见流程路径、瓶颈、偏离标准流程的情况以及返工循环。重要性此属性定义招聘流程中的各个步骤，是构建流程图的基础，也支持分析流程走向及其差异。获取途径通常从Oracle HCM Recruiting中与职位申请对象相关的事件表或状态历史表中提取。示例申请已筛选面试已完成已发出录用通知候选人已录用
	职位申请 JobApplicationId	每份候选人职位申请的唯一标识符。
说明职位申请ID是主键，用于跟踪候选人针对特定职位需求的完整申请历程。它关联所有相关活动，从最初提交申请到最终录用决定，构成一个完整的流程实例。在流程挖掘中，此属性对于定义案例至关重要，可用于还原每位候选人的独特路径。分析人员可据此查看端到端的申请流程、衡量整体周期时间，并分析招聘漏斗各阶段的转化率。重要性该标识符可唯一识别每个招聘案例，从而追踪候选人申请从开始到结束的完整生命周期。获取途径这是Oracle HCM Recruiting模块中的主要标识符，通常与候选人提交申请的记录相关联。示例 APP-2024-00123APP-2024-00124APP-2024-00125
	数据上次更新 LastDataUpdate	数据上次从源系统刷新或提取的时间戳。
说明此属性表示最近一次数据提取的日期和时间，可帮助了解所分析数据的新旧程度。用户需要据此了解流程分析反映的最新情况，并判断近期活动是否已纳入分析。这也是仪表板或报告的重要元数据。重要性告知用户数据的时效性，帮助其了解分析所涵盖的时间范围。获取途径此时间戳在数据提取、转换和加载（ETL）过程中生成并添加。示例 2024-05-20T08:00:00Z2024-05-21T08:00:00Z
	源系统 SourceSystem	数据的来源系统。
说明此属性用于标识记录招聘数据的源信息系统。对于此流程，通常为“Oracle HCM Recruiting”。在使用多个系统的环境中，此字段对于数据沿袭和问题排查至关重要。它有助于确认数据来自预期来源，也可用于筛选特定平台的数据进行分析。重要性标识数据来源，对于数据治理、验证以及确保分析基于正确的信息来源至关重要。获取途径此值通常在数据提取、转换和加载（ETL）过程中作为静态值添加，用于标记数据集。示例 Oracle HCM RecruitingOracle Fusion Cloud HCM
	招聘人员 Recruiter	负责处理职位申请的招聘人员姓名或ID。
说明此属性用于标识负责该申请的人才招聘专业人员。此人通常负责引导候选人完成招聘流程。按招聘人员分析流程，有助于平衡工作量、对标绩效并发现最佳实践。例如，可了解哪些招聘人员的周期时间最短或录用接受率最高，为“招聘人员工作量与效率”仪表板提供支持。重要性将活动关联到具体人员，以便分析招聘人员的工作量、效率和绩效。获取途径与Oracle HCM Recruiting中的职位申请或职位需求对象相关联。示例 John SmithJane DoeEmily Jones
	招聘经理 HiringManager	相关职位需求的招聘经理姓名或ID。
说明此属性用于标识有空缺职位的团队或部门经理。招聘经理是关键相关方，通常会参与筛选、面试和最终录用决策。按招聘经理分析流程绩效，有助于发现与其面试安排或反馈延迟相关的瓶颈，并找出培训或流程改进机会，帮助经理更高效地履行招聘职责。重要性标识流程中的关键相关方，以便分析招聘经理参与所造成的延误或效率差异。获取途径此属性存储在Oracle HCM Recruiting的职位需求对象中，该对象与职位申请相关联。示例 Robert BrownSusan WhiteMichael Green
	申请来源 ApplicationSource	收到申请的渠道或来源。
说明此属性用于跟踪候选人通过何种渠道发现职位空缺，例如“LinkedIn”“公司网站”“员工推荐”或“招聘网站”。此属性对于“申请来源成效”仪表板至关重要，可帮助组织衡量不同招聘渠道的投资回报。通过分析哪些来源带来的录用人数最多或招聘周期最短，人才招聘团队可以优化招聘渠道策略。重要性帮助确定吸引并录用合格候选人的有效渠道，从而优化招聘营销支出。获取途径候选人在Oracle HCM Recruiting中提交申请时，通常会记录此信息。示例 LinkedIn员工推荐公司招聘页面Indeed
	申请状态 ApplicationStatus	职位申请当前或最终的状态。
说明此属性表示申请的处理状态，例如“活跃”“已录用”“已拒绝”或“已撤回”，反映申请在流程中的当前进展或最终结果。分析最终状态是计算录用率和流失率等关键指标的基础。通过了解成功进入最终阶段的候选人数量，可以评估招聘漏斗的整体成效。重要性提供招聘流程的结果，是计算转化率、录用率和流失率的必要依据。获取途径 Oracle HCM Recruiting职位申请主记录中的标准字段。示例已录用已拒绝候选人已撤回已接受录用
	部门 Department	招聘候选人所对应的部门或业务单元。
说明此属性用于指定有空缺职位的组织单元，例如“工程”“销售”或“市场营销”，为每份职位申请提供组织背景。按部门细分招聘流程对于绩效对标至关重要。管理层可据此比较组织不同部门的招聘周期、流失率和其他关键指标，满足“部门招聘绩效对标”仪表板的分析需求。重要性支持比较不同业务单元或部门的招聘绩效和流程差异。获取途径通常属于Oracle HCM Recruiting中与申请相关联的职位需求数据。示例工程销售财务人力资源
	工作地点 JobLocation	职位所在的地理位置或办公地点。
说明此属性用于指定职位需求对应的城市、州或国家/地区，也可标明职位是否为远程办公。此维度支持按地区分析招聘流程，有助于了解不同地点的招聘周期是否存在显著差异，或某些地区的候选人流失率是否较高。这些信息可用于针对不同劳动力市场制定招聘策略。重要性支持按地理位置分析招聘指标，帮助发现各地区在招聘绩效和挑战方面的差异。获取途径存储在Oracle HCM Recruiting的职位需求对象中。示例纽约，纽约州伦敦，英国远程办公旧金山，加利福尼亚州
	录用金额 OfferAmount	向候选人发出的录用通知所列薪酬金额。
说明此属性记录该职位拟提供的薪酬，是招聘流程最后阶段的重要信息。尽管薪酬信息较为敏感，分析录用金额仍有助于了解薪酬竞争力。将其与录用接受率关联分析，可判断薪酬是否是候选人拒绝录用的原因。此属性也可用于确保不同部门和职位之间的薪酬公平。重要性支持分析薪酬趋势及其对录用接受率的影响，帮助制定更具竞争力的招聘策略。获取途径可在Oracle HCM Recruiting申请的录用通知详情部分找到。示例 8500012000065000
	拒绝原因 RejectionReason	候选人申请被拒绝的原因。
说明当申请状态变更为“已拒绝”时，此属性用于记录相关背景信息。原因可能包括“不符合企业文化”“缺少所需技能”或“已录用其他候选人”。分析流程各阶段的拒绝原因可提供重要反馈，帮助发现职位描述问题、预期不一致或筛选流程中的问题，从而优化招聘策略并提升候选人储备质量。重要性帮助了解候选人未能继续推进的原因，对于改进职位描述、筛选标准和整体流程成效至关重要。获取途径招聘人员或招聘经理在Oracle HCM Recruiting中拒绝候选人时，通常会在专用字段或备注栏中记录此信息。示例未达到最低任职资格已选择资历更合适的候选人薪酬预期不符
	招聘周期目标 TimeToHireTarget	填补职位需求的目标时长，以天为单位。
说明此属性定义预期的招聘完成时限，从职位需求开放到候选人接受录用通知为止。目标通常根据职位级别或部门标准设定。此属性对于“招聘周期目标达成率”KPI至关重要。将实际招聘周期与目标进行比较，组织可以衡量战略目标的达成情况，并主动发现可能无法按时填补的职位需求。重要性提供实际招聘周期的对照基准，直接支持目标达成率KPI的计算。获取途径此属性可能是Oracle HCM Recruiting职位需求对象中的字段，也可能由其他系统维护。示例 30456090
	是否为内部候选人 IsInternalCandidate	用于标识候选人是否为现职员工的标志。
说明此布尔属性用于区分现有员工（内部候选人）和外部候选人。内部招聘和外部招聘的流程步骤、服务级别协议和成功率通常各不相同。分析此属性有助于比较内部人才流动与外部招聘流程，了解公司是否有效地提拔内部人才，以及内部招聘流程是否比外部招聘更高效。重要性区分内部和外部候选人。两者的招聘路径和绩效指标往往差异显著，分开分析可提高分析准确性。获取途径此标志通常位于Oracle HCM Recruiting的候选人或申请档案中。示例 truefalse
	是否返工 IsRework	用于标识某项活动是否为第二次或后续重复执行的标志。
说明此计算属性用于识别同一案例中重复发生的活动。例如，申请从“面试”退回“筛选”，之后又回到“面试”，那么第二次发生的“筛选”和“面试”都会标记为返工。分析返工是发现流程低效和隐性成本的关键。它有助于定位流程未能顺利推进、需要重复执行步骤的环节，并通过量化此类循环的频次和影响，为“申请返工与瓶颈分析”仪表板提供支持。重要性识别流程低效和重复作业的环节，帮助发现隐性成本和简化流程的机会。获取途径在数据准备期间，通过检查同一CaseId是否已发生相同活动来计算。示例 truefalse
	结束时间 EventEndTime	表示招聘活动完成时间的时间戳。
说明结束时间表示活动完成的时刻。结束时间与开始时间之差即为单项活动的处理时间。在流程挖掘中，此属性用于计算具体任务的持续时间，例如筛选申请或进行面试所需的时间。分析活动持续时间有助于找出整体流程中耗时最长的具体步骤。重要性支持计算各项活动的持续时间，有助于定位低效步骤并衡量任务层面的绩效。获取途径此数据可能记录在事件日志表中，也可根据后续活动的开始时间推算。示例 2023-10-01T10:30:00Z2023-10-05T15:00:00Z2023-11-12T11:15:00Z
	职位需求ID JobRequisitionId	候选人所申请职位空缺的唯一标识符。
说明职位需求ID将多份职位申请关联到同一个已批准的职位空缺，代表招聘流程中的人才需求。此属性对于汇总申请数据、分析特定招聘需求的整体情况至关重要。借助它，您可以分析某个职位的候选人数量、该职位的招聘渠道成效，以及填补职位所需的总时间。重要性汇总同一职位空缺的所有申请，以便分析特定职位的完整人才储备情况。获取途径这是Oracle HCM Recruiting职位需求对象的主键，并与每份申请相关联。示例 REQ-2024-501REQ-2024-502REQ-2024-503

必填建议填写选填

招聘与人才招募活动

以下是建议记录在事件日志中的关键流程步骤和里程碑，有助于准确发现流程并识别瓶颈。

6 建议填写 8 选填

	活动	说明
	候选人已录用	表示招聘流程最终成功完成。候选人状态正式变为“已录用”时触发此事件，并开始向核心人力资源部门交接。
重要性这是流程的主要成功结果，也是计算招聘周期和其他关键效率指标的终点。获取途径这是IRC_SUBMISSIONS中职位申请记录的关键状态变更，由Oracle Recruiting中的“录用”或“转至人力资源”操作触发。采集招聘人员执行最终的“录用”操作时记录。事件类型 explicit
	已发出录用通知	表示已正式向候选人发出职位录用通知。招聘人员通过Oracle Recruiting系统正式发送录用通知时会记录此事件。
重要性这是衡量录用接受率以及从最终面试到发出录用通知所需时间的关键里程碑，也是候选人体验的重要环节。获取途径职位录用状态变为“已发出”或“已发送”时会明确记录，并将操作时间戳保存到录用对象中。采集招聘人员执行“发出录用通知”操作时记录。事件类型 explicit
	已接受录用	表示候选人已正式接受职位录用。候选人通过职业门户接受录用，或招聘人员手动更新状态时会记录此事件。
重要性这是流程中的主要成功里程碑，是计算录用接受率的依据，也标志着录用后的活动开始。获取途径录用记录的状态更新为“已接受”时记录。此操作可以由候选人在线完成，也可以由招聘人员手动录入。采集取自录用状态变为“已接受”时的时间戳。事件类型 explicit
	收到申请	候选人申请职位时，系统会创建新的职位申请记录。通常在系统首次创建申请记录时明确记录此事件。
重要性这是每个案例的起点，对于计算招聘周期等端到端流程时长，以及分析不同时期的申请量都至关重要。获取途径此事件取自Oracle Recruiting中候选人提交记录的创建时间戳，数据可能来自IRC_SUBMISSIONS等表。采集取自职位申请记录的创建时间戳。事件类型 explicit
	申请被拒绝	表示公司已决定在流程的任一阶段不再考虑该候选人。系统通过申请状态变更记录此情况。
重要性这是申请流程常见的结束节点。分析申请在哪个阶段被拒绝，有助于了解流失率和流程效率。获取途径根据职位申请的任一终止状态变更推断，例如“已拒绝”“未入选”或其他类似的已配置状态值。采集根据职位申请状态变更为终止状态“已拒绝”推断。事件类型 inferred
	面试已完成	表示候选人面试已结束。通常根据面试官或招聘人员将已安排面试的状态更新为“已完成”或“已进行”推断此事件。
重要性这是招聘流程中的关键里程碑。从此活动到“已发出录用通知”的周期时间是重要的绩效指标。获取途径根据Oracle Recruiting中已安排面试记录的状态变更推断。状态变更的时间戳即为事件时间。采集根据面试状态变为“已完成”或“已结束”推断。事件类型 inferred
	已启动背景调查	表示候选人接受录用后，背景调查已启动。此事件通常由状态变更或与第三方服务集成触发。
重要性此活动对于跟踪录用后、入职前的时间安排和确保合规至关重要。此处的延误可能影响候选人的入职日期。获取途径根据职位申请状态变为“背景调查”推断，或通过集成的背景调查服务商API调用记录。采集根据申请状态变为“背景调查进行中”推断。事件类型 inferred
	已拒绝录用	表示候选人决定拒绝职位录用。录用记录的状态会明确变更，通常还会附上拒绝原因。
重要性这是流程中的关键失败节点。分析候选人何时以及为何拒绝录用，有助于了解薪酬竞争力和候选人体验。获取途径录用记录的状态更新为“已拒绝”或“候选人已拒绝”时记录。采集取自录用状态变为“已拒绝”时的时间戳。事件类型 explicit
	录用详情已批准	表示职位录用通知发给候选人之前，内部审批工作流已完成。根据录用记录的最终审批状态推断此事件。
重要性此活动有助于分析内部录用审批流程的耗时和效率，该流程可能是造成延误的重要原因。获取途径根据Oracle Recruiting中职位录用记录的状态变更历史推断，跟踪状态变为“已批准”的时间。采集根据录用对象的状态变为“已批准”推断。事件类型 inferred
	申请已撤回	表示候选人决定退出该职位的选拔流程。当申请状态更新为“候选人已撤回”时，系统会记录此情况。
重要性跟踪撤回情况有助于发现候选人体验不佳、导致流失率较高的阶段。这对于分析候选人流失率KPI至关重要。获取途径根据职位申请状态变更为“已撤回”推断。候选人可在门户中发起撤回，招聘人员也可代为操作。采集根据职位申请状态变更为“候选人已撤回”推断。事件类型 inferred
	申请已筛选	表示招聘人员对候选人申请进行初步审核，以确认其是否具备基本资格。通常根据申请状态从“新建”变为“审核中”推断此事件。
重要性分析此阶段的耗时有助于发现初步审核流程中的瓶颈，也是衡量申请筛选返工率KPI的关键。获取途径根据Oracle Recruiting中职位申请状态字段的历史记录推断。查看IRC_SUBMISSIONS表历史记录中的状态变更。采集根据职位申请状态变为“筛选中”或“审核中”推断。事件类型 inferred
	评估已完成	表示候选人已完成所需的初步筛选评估，例如技能测试或性格评估。通常在评估平台向Oracle HCM返回完成状态时记录此事件。
重要性此活动有助于跟踪候选人的参与情况，并分析候选人完成评估所需的时间，因为评估环节可能导致候选人退出。获取途径数据来自集成的第三方评估工具推送的状态更新，或招聘人员在候选人申请档案中手动更改的状态。采集评估服务提供商的API更新候选人状态时记录。事件类型 explicit
	面试反馈已提交	表示面试官已在面试后提交候选人反馈和评估。保存反馈表单时会明确记录此事件。
重要性面试结束到提交反馈之间的延误会显著拖慢招聘流程。跟踪此活动有助于发现决策环节的瓶颈。获取途径面试官通过Oracle Recruiting门户提交评估时会明确记录。使用反馈记录的创建时间戳。采集取自面试反馈记录的创建时间戳。事件类型 explicit
	面试已安排	表示系统已正式安排候选人面试。通常在创建面试并将其关联到职位申请时记录此事件。
重要性跟踪此活动有助于衡量安排面试所需的时间。这是候选人体验和整体招聘周期的重要组成部分。获取途径此事件会明确记录在Oracle Recruiting的面试安排模块中。相关数据可在与职位申请关联的面试表中找到。采集取自与申请关联的面试事件创建记录。事件类型 explicit

建议填写选填

数据提取指南

如何从Oracle HCM Recruiting获取数据

步骤

进入OTBI：登录Oracle HCM Cloud。通过导航菜单进入工具，然后选择报表和分析。
新建分析：在报表和分析工作区中，点击新建，然后选择分析。请勿选择报表，因为分析具备所需的灵活性。
选择主题区域：系统提示时，选择“Recruiting - Recruiting Real Time”主题区域。该数据模型包含所需的职位申请、面试和录用详情。
进入高级配置：选择主题区域后，系统会打开分析编辑器。前往右上角的高级选项卡，可直接编辑逻辑SQL查询。
粘贴逻辑SQL：在“Analysis XML”部分找到saw:query标签。将标签中的现有SQL查询替换为本文档“查询”部分提供的完整逻辑SQL。请务必替换整个默认查询。
设置日期范围参数：运行查询前，将[Start Date]和[End Date]占位值替换为所需日期范围，格式为“YYYY-MM-DD”。这对控制提取的数据量至关重要。
验证查询：点击“Validate SQL”按钮，检查是否存在语法错误。验证成功后即可继续。请注意，此操作只检查语法，不会确认您的具体配置中是否存在相应列。
查看结果：返回结果选项卡并运行分析，系统将显示事件日志数据预览。请确认列和数据均符合预期。
配置导出设置：对于大型数据集，建议将分析设为计划任务，而非交互式运行。在分析编辑器中，您可以设置导出属性，确保CSV格式正确。
导出数据：确认结果无误后，点击导出链接并选择CSV格式，即可下载完整的事件日志文件。
准备上传：打开下载的CSV文件，确认列标题与所需属性一致（如JobApplicationId、ActivityName、EventTime等），并检查日期格式是否统一。随后即可将文件上传至流程挖掘工具。

配置

主题区域：整个查询基于Recruiting - Recruiting Real Time主题区域。运行报表的用户必须具备访问该主题区域所需的权限。
日期范围筛选：查询包含[Start Date]和[End Date]占位符。请务必设置明确的日期范围，以控制运行性能和数据量。建议先选择最近3至6个月的数据。
数据安全：OTBI会根据Oracle HCM数据安全角色限制访问。运行报表的用户只能查看其获准访问的业务单元、部门和法定雇主的数据。
性能注意事项：对于招聘量较大的组织，长时间范围内运行此查询可能较慢，也可能在交互式用户界面中超时。提取大量数据时，建议保存分析并安排为后台任务运行，将结果发送到内容服务器或电子邮件。
自定义：所提供的查询使用标准字段。如果您的组织使用描述性灵活字段（DFF）或自定义对象跟踪特定招聘事件，则需要修改逻辑SQL以纳入这些字段。

查询示例 sql

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Application Received' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - General"."Submission Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Application Screened' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Screen'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Assessment Completed' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Assessment"."Assessment Completion Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Interview Scheduled' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Schedule"."Interview Scheduled Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Interview Conducted' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Event Overall Status" = 'Completed'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Interview Completion Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Interview Feedback Submitted' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Interview Feedback"."Feedback Submission Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Details Approved' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Approval Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Extended' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Extended Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Accepted' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Accepted Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    "Recruiting - Recruiting Real Time"."Job Application - General"."Job Application Identifier" as "JobApplicationId",
    'Offer Declined' as "ActivityName",
    CAST("Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    "Recruiting - Recruiting Real Time"."Primary Recruiter"."Recruiter Display Name" as "Recruiter",
    "Recruiting - Recruiting Real Time"."Hiring Manager"."Hiring Manager Display Name" as "HiringManager",
    "Recruiting - Recruiting Real Time"."Requisition - General"."Department Name" as "Department",
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State"."Current State" as "ApplicationStatus",
    "Recruiting - Recruiting Real Time"."Job Application - Source"."Source Name" as "ApplicationSource"
FROM "Recruiting - Recruiting Real Time"
WHERE "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" IS NOT NULL
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" >= TIMESTAMP '[Start Date]'
AND "Recruiting - Recruiting Real Time"."Job Application - Offer"."Offer Declined Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Background Check Initiated' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Background Check'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Candidate Hired' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Hired'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Application Rejected' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" LIKE '%Rejected%'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

UNION ALL

SELECT
    T1."Job Application Identifier" as "JobApplicationId",
    'Application Withdrawn' as "ActivityName",
    CAST(T2."Start Date" AS TIMESTAMP) as "EventTime",
    'Oracle HCM Recruiting' as "SourceSystem",
    CURRENT_TIMESTAMP as "LastDataUpdate",
    T4."Recruiter Display Name" as "Recruiter",
    T5."Hiring Manager Display Name" as "HiringManager",
    T6."Department Name" as "Department",
    T3."Current State" as "ApplicationStatus",
    T7."Source Name" as "ApplicationSource"
FROM
    "Recruiting - Recruiting Real Time"."Job Application - General" T1,
    "Recruiting - Recruiting Real Time"."Job Application - Phase and State History" T2,
    "Recruiting - Recruiting Real Time"."Job Application - Current Phase and State" T3,
    "Recruiting - Recruiting Real Time"."Primary Recruiter" T4,
    "Recruiting - Recruiting Real Time"."Hiring Manager" T5,
    "Recruiting - Recruiting Real Time"."Requisition - General" T6,
    "Recruiting - Recruiting Real Time"."Job Application - Source" T7
WHERE T1."Job Application Identifier" = T2."Job Application Identifier"
AND T1."Job Application Identifier" = T3."Job Application Identifier"
AND T1."Recruiter Login Name" = T4."Recruiter Login Name"
AND T1."Hiring Manager Login Name" = T5."Hiring Manager Login Name"
AND T1."Requisition Number" = T6."Requisition Number"
AND T1."Source ID" = T7."Source ID"
AND T2."State Name" = 'Withdrawn by Candidate'
AND T2."Start Date" IS NOT NULL
AND T2."Start Date" >= TIMESTAMP '[Start Date]'
AND T2."Start Date" < TIMESTAMP '[End Date]'

步骤

准备工作：确保系统已安装Python 3和requests库。您可以使用pip安装：pip install requests。此外，您还需要有效的用户凭据（用户名和密码）、Oracle HCM Cloud实例的主机名，以及访问Recruiting REST API的权限。
初始化脚本：新建Python脚本文件，例如extract_hcm_data.py。首先导入所需的库：requests、csv和datetime。
定义配置：在脚本顶部定义Oracle HCM主机名、用户名、密码和所需的数据提取日期范围。出于安全考虑，建议使用占位符保存凭据。
建立API会话：创建requests.Session对象，并设置会话的身份验证属性，填写用户名和密码。这样，后续通过此会话发送的每个API请求都会携带凭据。
实现分页循环：脚本必须分页获取数据，以处理大量数据并遵守API限制。将偏移量变量初始化为0，并设置一个限制值，例如500。创建while循环，只要API仍返回数据就继续执行。每次循环中，脚本都会使用当前偏移量调用API，再将偏移量增加限制值以获取下一页。
发送API请求：在循环中，为recruitingCEJobApplications端点构建完整的API请求URL。使用fields参数指定所需属性，并通过expand参数包含Phases和Offers等关联对象。使用q参数，根据配置的日期范围筛选CreationDate。
处理JSON响应：每次API调用后，检查HTTP状态码是否为200。解析JSON响应，申请记录位于响应正文的items数组中。
遍历并生成事件：遍历API返回的每个申请对象，并提取ApplicationId、Recruiter和HiringManager等常用属性。
将状态映射为活动：这是核心逻辑。每个申请都必须为14项必需活动分别生成一行数据。需要检查CreationDate、Phases集合和Offers集合。例如，使用CreationDate记录“收到申请”。遍历Phases数组，根据每个阶段的进入时间生成“申请已筛选”“已安排面试”“候选人已录用”“申请已拒绝”等行。同样，遍历Offers数组，生成“已发出录用通知”“已接受录用”和“已拒绝录用”等事件。
构建事件日志：生成每项活动事件时，将其保存为字典，键名对应事件日志列标题（如JobApplicationId、ActivityName、EventTime等）。将每个字典添加到主列表，最终形成完整的事件日志。
导出为CSV：分页循环完成后，使用Python内置的csv模块将事件字典主列表写入CSV文件。确保文件采用UTF-8编码，且CSV列标题与ProcessMind数据导入所需的属性名称完全一致。

配置

API凭据：需要使用专用服务账户，并为其分配可访问Recruiting REST API的角色，例如“Recruiting Manager”，或具有“Recruiting REST API Access”权限的自定义角色。
API端点：主要使用的端点为recruitingCEJobApplications。请务必使用expand查询参数（例如expand=Phases,Offers），通过一次调用获取包含状态历史和录用详情的嵌套对象，提升效率。
日期范围筛选：使用q查询参数按日期筛选申请记录。建议根据CreationDate筛选，以控制数据量。首次分析建议选择最近3至6个月的数据。例如：q=CreationDate>'2023-01-01T00:00:00Z'。
字段选择：为提升性能并减少网络传输的数据量，请使用fields参数，仅请求必要的属性。包括ApplicationId、CreationDate、LastUpdateDate、Recruiter、HiringManager、Requisition、Phases和Offers等字段。
分页限制：limit参数用于控制每次API调用返回的记录数。通常设置为200至500，可在API调用次数和单次响应大小之间取得平衡。
状态映射：脚本使用字典将Oracle HCM的阶段和状态代码映射为标准活动名称。您可能需要根据组织的具体招聘工作流配置调整此映射。

查询示例 python

import requests
import csv
import json
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
HCM_HOST = class="hl-string">"https://[Your HCM Host].fa.us2.oraclecloud.com"
USERNAME = class="hl-string">"[Your Username]"
PASSWORD = class="hl-string">"[Your Password]"

class=class="hl-string">"hl-comment"># Date range for the query (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00+00:00"
END_DATE = class="hl-string">"2023-12-31T23:59:59+00:00"

class=class="hl-string">"hl-comment"># API and File settings
API_ENDPOINT = class="hl-string">"/hcmRestApi/resources/11.13.18.05/recruitingCEJobApplications"
CSV_FILENAME = class="hl-string">"recruitment_event_log.csv"
API_LIMIT = 500

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    class="hl-string">"""Main function to orchestrate the extraction process."""
    event_log = []
    offset = 0
    has_more = True

    session = requests.Session()
    session.auth = (USERNAME, PASSWORD)

    print(class="hl-string">"Starting data extraction from Oracle HCM...")

    while has_more:
        print(fclass="hl-string">"Fetching applications, offset: {offset}")
        try:
            params = {
                class="hl-string">'q': fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'",
                class="hl-string">'fields': class="hl-string">'ApplicationId,CreationDate,LastUpdateDate,ApplicationStatus,ApplicationSource,Recruiter,HiringManager,Department,Phases,Offers',
                class="hl-string">'expand': class="hl-string">'Phases,Offers',
                class="hl-string">'limit': API_LIMIT,
                class="hl-string">'offset': offset
            }
            response = session.get(HCM_HOST + API_ENDPOINT, params=params)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raises an exception for bad status codes

            data = response.json()
            applications = data.get(class="hl-string">'items', [])

            if not applications:
                print(class="hl-string">"No more applications found.")
                break

            process_applications(applications, event_log)

            has_more = data.get(class="hl-string">'hasMore', False)
            offset += API_LIMIT

        except requests.exceptions.HTTPError as http_err:
            print(fclass="hl-string">"HTTP error occurred: {http_err} - {response.text}")
            break
        except Exception as err:
            print(fclass="hl-string">"An error occurred: {err}")
            break

    if event_log:
        write_to_csv(event_log)
        print(fclass="hl-string">"Extraction complete. {len(event_log)} events written to {CSV_FILENAME}")
    else:
        print(class="hl-string">"No events were generated.")

def process_applications(applications, event_log):
    class="hl-string">"""Processes a batch of applications and generates event log entries."""
    now_iso = datetime.utcnow().isoformat() + class="hl-string">'Z'

    for app in applications:
        base_event = {
            class="hl-string">'JobApplicationId': app.get(class="hl-string">'ApplicationId'),
            class="hl-string">'Recruiter': app.get(class="hl-string">'Recruiter'),
            class="hl-string">'HiringManager': app.get(class="hl-string">'HiringManager'),
            class="hl-string">'Department': app.get(class="hl-string">'Department'),
            class="hl-string">'ApplicationSource': app.get(class="hl-string">'ApplicationSource'),
            class="hl-string">'ApplicationStatus': app.get(class="hl-string">'ApplicationStatus'),
            class="hl-string">'SourceSystem': class="hl-string">'Oracle HCM Recruiting',
            class="hl-string">'LastDataUpdate': now_iso
        }

        class=class="hl-string">"hl-comment"># 1. Application Received
        event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Application Received', class="hl-string">'EventTime': app.get(class="hl-string">'CreationDate')})

        class=class="hl-string">"hl-comment"># Process phases for status-based events
        for phase in app.get(class="hl-string">'Phases', []):
            phase_name = phase.get(class="hl-string">'PhaseName', class="hl-string">'').lower()
            status_name = phase.get(class="hl-string">'StateName', class="hl-string">'').lower()
            entry_date = phase.get(class="hl-string">'EntryDate')

            if not entry_date: continue

            activity_map = {
                class="hl-string">'screen': class="hl-string">'Application Screened',
                class="hl-string">'assessment': class="hl-string">'Assessment Completed',
                class="hl-string">'interview': class="hl-string">'Interview Scheduled',
                class="hl-string">'offer': class="hl-string">'Offer Details Approved', class=class="hl-string">"hl-comment"># Approximation, happens before offer is made
                class="hl-string">'background check': class="hl-string">'Background Check Initiated',
                class="hl-string">'hired': class="hl-string">'Candidate Hired',
                class="hl-string">'rejected': class="hl-string">'Application Rejected',
                class="hl-string">'withdrawn': class="hl-string">'Application Withdrawn'
            }

            class=class="hl-string">"hl-comment"># Simple mapping by phase name
            for key, activity in activity_map.items():
                if key in phase_name:
                    event_log.append({**base_event, class="hl-string">'ActivityName': activity, class="hl-string">'EventTime': entry_date})

            class=class="hl-string">"hl-comment"># Specific logic for Interview Conducted
            if class="hl-string">'interview' in phase_name and status_name == class="hl-string">'completed':
                 event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Interview Conducted', class="hl-string">'EventTime': entry_date})

            class=class="hl-string">"hl-comment"># Specific logic for Feedback Submitted (approximation)
            if class="hl-string">'interview' in phase_name and status_name == class="hl-string">'completed':
                 class=class="hl-string">"hl-comment"># This is an approximation, assuming feedback is submitted upon completion
                 event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Interview Feedback Submitted', class="hl-string">'EventTime': entry_date})


        class=class="hl-string">"hl-comment"># Process offers for offer-related events
        for offer in app.get(class="hl-string">'Offers', []):
            offer_status = offer.get(class="hl-string">'OfferStatus', class="hl-string">'').lower()
            offer_date = offer.get(class="hl-string">'OfferDate') or offer.get(class="hl-string">'LastUpdateDate')

            if not offer_date: continue

            if offer_status == class="hl-string">'extended':
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Offer Extended', class="hl-string">'EventTime': offer_date})
            elif offer_status == class="hl-string">'accepted':
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Offer Accepted', class="hl-string">'EventTime': offer_date})
            elif offer_status == class="hl-string">'declined':
                event_log.append({**base_event, class="hl-string">'ActivityName': class="hl-string">'Offer Declined', class="hl-string">'EventTime': offer_date})


def write_to_csv(event_log):
    class="hl-string">"""Writes the event log data to a CSV file."""
    if not event_log:
        return

    headers = [
        class="hl-string">'JobApplicationId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'Recruiter', class="hl-string">'HiringManager', class="hl-string">'Department', class="hl-string">'ApplicationStatus', class="hl-string">'ApplicationSource'
    ]

    class=class="hl-string">"hl-comment"># Deduplicate and sort events to ensure chronological order per application
    unique_events = {tuple(event.items()) for event in event_log}
    sorted_events = sorted([dict(event) for event in unique_events], key=lambda x: (x[class="hl-string">'JobApplicationId'], x[class="hl-string">'EventTime']))

    with open(CSV_FILENAME, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=headers)
        writer.writeheader()
        writer.writerows(sorted_events)

if __name__ == class="hl-string">"__main__":
    main()

准备好开始了吗？

使用此模板有把握地准备数据，开始发现人才招募流程中不易察觉的问题。立即着手优化招聘策略，提升效率和成效。

返回改进指南

下载模板

立即优化招聘与人才招募

将周期时间缩短30%，提升候选人体验。

开始免费试用

无需信用卡，几分钟即可完成设置。

招聘与人才招募数据模板

招聘与人才招募数据模板

招聘与人才招募属性

招聘与人才招募活动

数据提取指南

用于导出招聘数据的Oracle HCM OTBI报表

步骤

配置

查询示例 sql

使用Oracle HCM REST API通过Python脚本提取数据

步骤

配置

查询示例 python

准备好开始了吗？

立即优化招聘与人才招募