Oracle Flexcube中ACC_STATUS（OVER/NORM）自动变更排查问询

一、排查触发时机 #

1. 验证日终EOD流程是否触发 #

• 查询系统EOD任务表，定位可能涉及账户状态变更的任务：

  SELECT JOB_NAME, JOB_DESCRIPTION, FREQUENCY 
  FROM STTB_JOB_DETAILS 
  WHERE JOB_DESCRIPTION LIKE '%ACCOUNT STATUS%' OR JOB_NAME LIKE '%OVERDUE%';
  

• 查看EOD执行日志，筛选状态变更相关记录：

  SELECT LOG_DATE, TASK_NAME, LOG_DETAILS 
  FROM STTB_EOD_LOG 
  WHERE LOG_DETAILS LIKE '%ACC_STATUS%' OR LOG_DETAILS LIKE '%OVER%' OR LOG_DETAILS LIKE '%NORM%';
  

2. 排查定时JOB #

• 检查Oracle调度器任务（DBMS_SCHEDULER）：

  SELECT JOB_NAME, PROGRAM_NAME, SCHEDULE_NAME, STATE 
  FROM DBA_SCHEDULER_JOBS 
  WHERE PROGRAM_NAME IN (
      SELECT PROGRAM_NAME 
      FROM DBA_SCHEDULER_PROGRAMS 
      WHERE PROGRAM_ACTION LIKE '%sttm_cust_account%'
  );
  

• 检查旧版DBMS_JOB任务：

  SELECT JOB, WHAT, NEXT_DATE 
  FROM DBA_JOBS 
  WHERE WHAT LIKE '%sttm_cust_account%';
  

二、定位状态变更的逻辑包 #

1. 检查表上的触发器 #

• 查询sttm_cust_account表关联的触发器：

  SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, STATUS 
  FROM USER_TRIGGERS 
  WHERE TABLE_NAME = 'STTM_CUST_ACCOUNT';
  

• 查看触发器代码，找到调用的存储过程/包：

  SELECT TEXT 
  FROM USER_TRIGGERS 
  WHERE TRIGGER_NAME = '<触发器名称>';
  

2. 查找Flexcube标准业务包 #

• 账户维护类包（如PKG_CUST_ACCOUNT_MAINT）：

  SELECT TEXT 
  FROM USER_SOURCE 
  WHERE NAME = 'PKG_CUST_ACCOUNT_MAINT' 
  AND TYPE = 'PACKAGE BODY'
  AND TEXT LIKE '%ACC_STATUS%' AND TEXT LIKE '%OVER%' AND TEXT LIKE '%NORM%';
  

• 日终账户处理类包（如PKG_EOD_ACCOUNT_PROC）：

  SELECT TEXT 
  FROM USER_SOURCE 
  WHERE NAME = 'PKG_EOD_ACCOUNT_PROC' 
  AND TYPE = 'PACKAGE BODY'
  AND TEXT LIKE '%ACC_STATUS%';
  

• 逾期处理类包（如PKG_OVERDUE_PROCESSING）：

  SELECT TEXT 
  FROM USER_SOURCE 
  WHERE NAME = 'PKG_OVERDUE_PROCESSING' 
  AND TYPE = 'PACKAGE BODY'
  AND TEXT LIKE '%sttm_cust_account%' AND TEXT LIKE '%ACC_STATUS%';
  

内容的提问来源于stack exchange，提问作者AliMusa