[Oracle]供應商建立於多組織

l   建立供應商，Organization Name與Supplier Number

l   Supplier Sites建立不同OU

備註如遇到已經建立可以先更換名稱並失效，再進行新增 

[Oracle]How to disable Suppiler

 只能設定失效時間，資料表的Flag找不到位置

[Oracle]R12:如何刪除供應商或相關資料(需備份資料庫有風險)

 l   PO > Management > Purge > Initiate

ü   Last Activity Date:決定哪些已前的資料準備清除

ü   Operating Unit:測試發現就算指定OU，仍列出其他OU，可能升級造成

l   Request > Initiate Purge (AP/PO Purge Initiation (Selection) Routine)

l   Request > Print Purge (Purge Report Listings) 可以查看此次Purge預選清單

l   PO > Management > Purge > Confirm 開始刪除資料

*Warning: AP accounting entries will be/have been purged along with their transaction records.* 供應商所有發票與付款也會被清除

How to Use Payables Purging Process for Invoice, Payment, Purchase Order, Requisition and Supplier Scheduling? (Doc ID 139316.1)

[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