[Oracle]AR Interface Error(TYPE決定修正的位置)

l   無效的業務代表識別碼 (SALESREP_ID)

l   無效的銷售業績型態識別碼 (SALES_CREDIT_TYPE_ID)

l   'Oracle 應收帳款管理系統' 中需有帳單寄送地址識別碼, 且必須將其指定給帳單寄送地點客戶 (ORIG_SYSTEM_BILL_ADDRESS_ID)

l   無效的主要業務代表識別碼 (PRIMARY_SALESREP_ID)

l   無效的帳單寄送地點客戶識別碼 (ORIG_SYSTEM_BILL_CUSTOMER_ID)

l   Cannot get remit to address

檢查AR > Setup > Print > Remit to Addresses 

方案一、直接設定一組Default，方案二、設定指定國家(看那個客戶有問題)

參考文件

Troubleshooting Autoinvoice Import - Execution Report Errors (Request Status = Completed) (Doc ID 1089172.1)

Troubleshooting Remit To Address in Oracle Receivables: Cannot get remit to address (Doc ID 1101855.1)

How to Setup a Remit-To Address in Release 12 Oracle Receivables (Doc ID 1101666.1)

l   Please correct the receivable account assignment

檢查AR > Setup > Transaction > AutoAccounting

Receivable設定

l   Please correct the revenue account assignment

檢查AR > Setup > Transaction > AutoAccounting

Revenue設定

l   You must supply sales credit assignments for this transaction because the system option Require Salesreps is set to Yes.

RA_INTERFACE_SALESCREDITS_ALL這個資料表沒有建立

l   Conversion rate for the given date, currency code, set of books and conversion type has not been defined

l   Invalid Warehouse ID (WAREHOUSE_ID)

Tax code must be active, adhoc, and of type VAT or SALES TAX

l   Unable to derive a gl date for your transaction.  Please ensure that your transaction is in a gl period which you have defined

補充GL Date決定拋AR日期

發生錯誤原因：舉例AR要接8月，但是目前日期已經9/1，會發生問題，系統似乎會自動比對資料，需要於AR Interface填寫Conversion Date與GL Date

[Oracle]Qurey Account Details帳戶明細

SELECT APSA.ORG_ID, --ORG_ID
       HCA.CUST_ACCOUNT_ID,
       HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER, --客戶編號
       HP.PARTY_NAME CUSTOMER_NAME, --客戶名稱
       APSA.TRX_NUMBER,
       APSA.INVOICE_CURRENCY_CODE,
       APSA.TRX_DATE, --立帳日期
       APSA.DUE_DATE, --到期日
       APSA.CLASS,
       NVL(CTT.NAME, ARM.NAME) TRANSACTION_TYPE_NAME,
       APSA.STATUS,
       AL.MEANING,
       CRH_CURRENT.STATUS,
       SUM(APSA.AMOUNT_DUE_ORIGINAL) ORIGINAL, --原幣應收
       SUM(APSA.ACCTD_AMOUNT_DUE_REMAINING) AMOUNT --本幣餘額
  FROM APPS.HZ_PARTIES                  HP,
       APPS.HZ_CUST_ACCOUNTS            HCA,
       APPS.AR_PAYMENT_SCHEDULES_ALL    APSA,
       APPS.AR_CASH_RECEIPTS_ALL        CR,
       APPS.AR_RECEIPT_METHODS          ARM,
       APPS.RA_CUSTOMER_TRX_ALL         RCTA,
       APPS.RA_CUST_TRX_TYPES_ALL       CTT,
       APPS.AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT,
       APPS.AR_LOOKUPS                  AL
 WHERE APSA.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
   AND APSA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
   AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
   AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID(+)
   AND RCTA.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
   AND APSA.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID(+)
   AND CRH_CURRENT.CURRENT_RECORD_FLAG(+) = 'Y'
   AND HCA.PARTY_ID = HP.PARTY_ID
   AND APSA.STATUS = AL.LOOKUP_CODE
   AND AL.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
   AND APSA.STATUS = 'OP' --未結
      --AND HCA.ACCOUNT_NUMBER = '97290259'
      --AND APSA.ORG_ID = 136
   AND NVL(CTT.NAME, ARM.NAME) LIKE '%暂收订金%'
 GROUP BY APSA.ORG_ID,
          HCA.CUST_ACCOUNT_ID,
          HCA.ACCOUNT_NUMBER,
          HP.PARTY_NAME,
          APSA.TRX_NUMBER,
          APSA.INVOICE_CURRENCY_CODE,
          APSA.TRX_DATE,
          APSA.DUE_DATE,
          APSA.CLASS,
          CTT.NAME,
          ARM.NAME,
          APSA.STATUS,
          AL.MEANING,
          CRH_CURRENT.STATUS,
          ARM.NAME
 ORDER BY APSA.TRX_DATE

[Oracle]CUSTOMER_CLASS_CODE資料來源

 AR > Setup > System > QuickCodes > CUSTOMER CLASS

[Oracle]R12:客戶資料

SELECT HCSA.ORG_ID,
       HCA.CUST_ACCOUNT_ID,
       HCA.ACCOUNT_NUMBER,
   HP.PARTY_NAME
  FROM HZ_PARTIES             HP,
       HZ_PARTY_SITES         HPS,
       HZ_LOCATIONS           HL,
       HZ_CUST_ACCOUNTS_ALL   HCA,
       HZ_CUST_ACCT_SITES_ALL HCSA,
       HZ_CUST_SITE_USES_ALL  HCSU
 WHERE HP.PARTY_ID = HPS.PARTY_ID
   AND HPS.LOCATION_ID = HL.LOCATION_ID
   AND HP.PARTY_ID = HCA.PARTY_ID
   AND HCSA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
   AND HCSU.CUST_ACCT_SITE_ID = HCSA.CUST_ACCT_SITE_ID
   AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
      --AND HP.PARTY_ID = 20054
   AND HP.PARTY_NAME = 'XXXX'
   AND HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'
   AND HCSA.ORG_ID = 136
 ORDER BY HP.PARTY_NUMBER, HCSU.SITE_USE_CODE, HCSU.LOCATION

[Oracle]R12:客戶聯絡人

SELECT HP_PER.PARTY_NAME CONTACT_NAME
  FROM HZ_CUST_ACCOUNT_ROLES  ROL,
       HZ_PARTIES             HP_REL,
       HZ_RELATIONSHIPS       REL,
       HZ_PARTIES             HP_PER,
       HZ_CONTACT_POINTS      HCP_PH,
       HZ_CUST_ACCOUNT_ROLES  HCAR,
       HZ_ROLE_RESPONSIBILITY HRR
 WHERE ROL.PARTY_ID = HP_REL.PARTY_ID
   AND HP_REL.PARTY_ID = REL.PARTY_ID
   AND REL.OBJECT_TYPE = 'PERSON'
   AND REL.RELATIONSHIP_CODE = 'CONTACT'
   AND REL.OBJECT_ID = HP_PER.PARTY_ID
   AND HCAR.CUST_ACCOUNT_ROLE_ID = HRR.CUST_ACCOUNT_ROLE_ID
   AND HP_REL.PARTY_ID = HCAR.PARTY_ID
   AND ROL.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
   AND ROL.CUST_ACCOUNT_ID = 5090837 --P_CUST_ACCOUNT_ID
   AND ROL.CUST_ACCT_SITE_ID IS NULL
   AND HCAR.CUST_ACCT_SITE_ID IS NULL
   AND REL.STATUS = 'A'
   AND ROL.STATUS = 'A'
   AND HCP_PH.STATUS = 'A'
      --AND HCP_PH.PRIMARY_FLAG = 'Y'
   AND HCP_PH.CONTACT_POINT_TYPE = 'EMAIL'
   AND HP_REL.PARTY_ID = HCP_PH.OWNER_TABLE_ID
   AND HCP_PH.OWNER_TABLE_NAME = 'HZ_PARTIES'
   AND HRR.RESPONSIBILITY_TYPE = 'STMTS'
 ORDER BY HCP_PH.PRIMARY_FLAG;

[Oracle]R12:付款條件

SELECT RT.TERM_ID,
       RT.NAME,
       RT.CYCLE_NAME,
       RTL.SEQUENCE_NUM,
       RTL.RELATIVE_AMOUNT    相關金額,
       RTL.DUE_DAYS           到期天數,
       RTL.DUE_DATE           到期日期,
       RTL.DUE_DAY_OF_MONTH   每月固定日期,
       RTL.DUE_MONTHS_FORWARD 後推月數,
       RTL.CREATION_DATE,
       RTL.CREATED_BY,
       FU1.USER_NAME,
       RTL.LAST_UPDATE_DATE,
       RTL.LAST_UPDATED_BY,
       FU2.USER_NAME
  FROM RA_TERMS_VL RT, RA_TERMS_LINES RTL, FND_USER FU1, FND_USER FU2
 WHERE 1 = 1
   AND RT.TERM_ID = RTL.TERM_ID
   AND RTL.CREATED_BY = FU1.USER_ID
   AND RTL.LAST_UPDATED_BY = FU2.USER_ID
   AND TRUNC(NVL(RT.END_DATE_ACTIVE, SYSDATE + 1)) > TRUNC(SYSDATE)
 ORDER BY RT.TERM_ID ASC

[Oracle]R12:修改付款條件與到期日

 到期日:可以透過上方作業 > 調整來進行修改

付款條件:需透過Data Fix

   
UPDATE RA_CUSTOMER_TRX_ALL A
   SET A.TERM_ID = 1033
 WHERE A.CUSTOMER_TRX_ID = 2490433