Oracle AR 貸項通知單 API 建立完整 PL/SQL 範例

業務情境

在 Oracle E-Business Suite (EBS) Accounts Receivable (AR) 模組中，透過公用 API AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST 程式化建立貸項通知單，適用於退貨、折讓等業務自動化處理。

核心參數說明

完整 PL/SQL 範例

DECLARE
  l_dummy              VARCHAR2(240);
  l_customer_trx_id    ra_customer_trx.customer_trx_id%TYPE := 165023;
  l_line_credits_flag  ra_cm_requests.line_credits_flag%TYPE;
  l_line_amount        NUMBER;
  l_freight_amount     NUMBER := 0;
  l_cm_lines_tbl       arw_cmreq_cover.cm_line_tbl_type_cover;
  l_cm_reason_code     VARCHAR2(150) := 'RETURN';
  l_comments           VARCHAR2(150) := 'Demo for Create_Request API';
  l_msg_count          NUMBER := 0;
  l_msg_data           VARCHAR2(20000) := NULL;
  l_return_status      VARCHAR2(1);
  l_request_id         NUMBER;
  l_batch_source_name  VARCHAR2(150) := 'OM前受';
  cm_trx_id            NUMBER;
BEGIN
  -- 1. 設定 Apps 與 Org Context
  fnd_global.apps_initialize(1110, 50683, 222);  -- user_id, resp_id, resp_appl_id
  mo_global.init('AR');
  mo_global.set_policy_context('S', 121);        -- 單一組織 121

  -- 2. 填入明細行貸項資料 (針對發票行 customer_trx_line_id = 403228)
  l_cm_lines_tbl(1).customer_trx_line_id := 403228;
  l_cm_lines_tbl(1).quantity_credited    := NULL;   -- 全部退回不需指定數量
  l_cm_lines_tbl(1).price                := NULL;
  l_cm_lines_tbl(1).extended_amount      := -151000; -- 貸項金額 (負數)

  -- 3. 呼叫建立貸項 API
  AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST(
    P_API_VERSION            => 1.0,
    P_INIT_MSG_LIST          => FND_API.G_TRUE,
    P_COMMIT                 => FND_API.G_FALSE,       -- 手動控制 COMMIT
    P_VALIDATION_LEVEL       => FND_API.G_VALID_LEVEL_FULL,
    P_CUSTOMER_TRX_ID        => l_customer_trx_id,
    P_LINE_CREDIT_FLAG       => 'Y',
    P_CM_REASON_CODE         => l_cm_reason_code,
    P_CM_LINE_TBL            => l_cm_lines_tbl,
    P_SKIP_WORKFLOW_FLAG     => 'Y',
    P_CREDIT_METHOD_INSTALLMENTS => NULL,
    P_CREDIT_METHOD_RULES    => NULL,
    P_BATCH_SOURCE_NAME      => l_batch_source_name,
    P_ORG_ID                 => 121,
    X_REQUEST_ID             => l_request_id,
    X_RETURN_STATUS          => l_return_status,
    X_MSG_COUNT              => l_msg_count,
    X_MSG_DATA               => l_msg_data
  );

  -- 4. 取得訊息堆疊輸出
  FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data);
  DBMS_OUTPUT.PUT_LINE('Return Status ==> ' || l_return_status);
  DBMS_OUTPUT.PUT_LINE('Credit Memo request_id ==> ' || l_request_id);
  DBMS_OUTPUT.PUT_LINE('Msg Count ==> ' || l_msg_count);
  FOR i IN 1..l_msg_count LOOP
    DBMS_OUTPUT.PUT_LINE(SUBSTR(FND_MSG_PUB.GET(p_msg_index => i, p_encoded => 'F'), 1, 254));
  END LOOP;

  -- 5. 成功時查詢產生的貸項交易 ID
  IF l_return_status <> 'S' THEN
    NULL;
  ELSE
    SELECT cm_customer_trx_id
      INTO cm_trx_id
      FROM ra_cm_requests_all
     WHERE request_id = l_request_id;
    DBMS_OUTPUT.PUT_LINE('CM trx_id = ' || cm_trx_id);
    -- COMMIT;  -- 確認無誤後再提交
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception Error!');
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 80));
    FND_MESSAGE.RETRIEVE(l_dummy);
    DBMS_OUTPUT.PUT_LINE(l_dummy);
END;
/

關鍵執行流程圖解

Apps Initialize → Set Org Context → Populate Line Table
      ↓
AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST
      ↓
Check Return Status (S/U/E)
      ↓
Query RA_CM_REQUESTS_ALL → Get CM_CUSTOMER_TRX_ID
      ↓
Commit / Rollback

常見錯誤與排查

1. Invalid Org ID：確認 mo_global.set_policy_context 參數與 P_ORG_ID 一致。
2. Batch Source Not Found：P_BATCH_SOURCE_NAME 需在 AR 系統中預先定義且對應 Org。
3. Line Amount Mismatch：EXTENDED_AMOUNT 正負號與金額需對應原發票行金額。
4. Workflow Stuck：設定 P_SKIP_WORKFLOW_FLAG = 'Y' 可繞過審核流程 (需業務確認)。
5. Message Stack Overflow：大量訊息時增加 l_msg_data 緩衝區大小。

擴充應用建議

• 將固定值參數化，封裝為 Stored Procedure 供表單/介面呼叫。
• 搭配 AR_ADJUST_API_PUB 處理小額沖銷/調整。
• 整合到 Concurrent Program 實現批次自動產生貸項。
• 加入日誌寫入 Custom Table 供稽核追蹤。