[Oracle EBS] 诡异的订单暂挂：明明额度够且无逾期，为何信用检查失败？

SELECT h.order_number AS 订单号,
       -- 下单方信息
       p_bill.party_name AS 下单客户名称,
       
       -- 实际欠款方信息 (导致暂挂的真凶)
       p_debt.party_name      AS 欠款账户名称,
       ca_debt.account_number AS 欠款账户账号,
       
       -- 违规详情
       aps.trx_number AS 逾期发票号,
       aps.due_date AS 到期日,
       trunc(SYSDATE) - trunc(aps.due_date) AS 实际逾期天数,
       to_char(aps.amount_due_remaining, 'FM999,999,999.00') AS 剩余欠款
  FROM oe_order_headers_all h
-- 1. 从订单找到下单方 Party
  JOIN hz_cust_site_uses_all su
    ON h.invoice_to_org_id = su.site_use_id
  JOIN hz_cust_acct_sites_all cas
    ON su.cust_acct_site_id = cas.cust_acct_site_id
  JOIN hz_cust_accounts ca_bill
    ON cas.cust_account_id = ca_bill.cust_account_id
  JOIN hz_parties p_bill
    ON ca_bill.party_id = p_bill.party_id

-- 2. 核心逻辑：找该 Party 下所有的账户 (兄弟公司/分公司)
  JOIN hz_cust_accounts ca_debt
    ON ca_debt.party_id = p_bill.party_id
  JOIN hz_parties p_debt
    ON ca_debt.party_id = p_debt.party_id

-- 3. 查这些账户的 AR 逾期
  JOIN ar_payment_schedules_all aps
    ON aps.customer_id = ca_debt.cust_account_id
 WHERE h.order_number = '&Enter_Order_Number'  -- 输入订单号
   AND aps.status = 'OP' -- 未结清
   AND aps.amount_due_remaining > 0 -- 有欠款
   AND aps.class IN ('INV', 'DM') -- 只看发票和借项通知单
   AND (trunc(SYSDATE) - trunc(aps.due_date)) > 0 -- 只要逾期就列出来
 ORDER BY aps.due_date ASC;

SELECT 
    h.order_number               AS 订单号,
    ott_tl.name                  AS 订单类型,
    ccr.name                     AS 规则名称,
    
    -- 核心配置：允许逾期天数
    -- 解释：系统只会看这个数字。如果为空或0，代表“零容忍”(只要有逾期就暂挂)。
    NVL(ccr.open_ar_days, 0)     AS 最大逾期天数
    
FROM 
    oe_order_headers_all h
    -- 1. 关联类型翻译表获取名称
    JOIN oe_transaction_types_tl ott_tl 
      ON h.order_type_id = ott_tl.transaction_type_id
      AND ott_tl.language = USERENV('LANG')
    
    -- 2. 关联类型定义表获取规则ID
    JOIN oe_transaction_types_all ott_all 
      ON h.order_type_id = ott_all.transaction_type_id
      
    -- 3. 关联信用规则
    -- 这里查看的是“预订(Booking)”时的规则
    -- 如果是发运暂挂，请改为关联 shipping_credit_check_rule_id
    LEFT JOIN oe_credit_check_rules ccr 
      ON ott_all.entry_credit_check_rule_id = ccr.credit_check_rule_id
WHERE 
    h.order_number = '&Enter_Order_Number';