TCA APIs Usage and Customer Account Creation Sample Code, Queries and Screens (Doc ID 837568.1)
IT Life 資訊人生
常常忘東忘西~~ 紀錄做過的事情，建立信心里程碑~~ 老ㄅ寫給小孩的哩哩叩叩~~
2026-05-18
[Oracle]R12:客戶信用額度修改API
--開發API修改信用額度
DECLARE
P_CUSTOMER_PROFILE_REC_TYPE HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE; --這個是修改 Account Profile
P_CUSTOMER_PROFILE_AMT HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE; --這個是修改 Profile Amounts
P_CUST_ACCOUNT_PROFILE_ID NUMBER;
P_OBJECT_VERSION_NUMBER NUMBER;
P_OVERALL_CREDIT_LIMIT NUMBER;
X_RETURN_STATUS VARCHAR2(2000);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(2000);
V_CUST_ACT_PROF_AMT_ID NUMBER;
V_CUST_ACCOUNT_PROFILE_ID NUMBER;
V_RETURN_STATUS VARCHAR2(2000);
V_MSG_COUNT NUMBER;
V_MSG_DATA VARCHAR2(2000);
V_MSG_DUMMY VARCHAR2(5000);
T_OUTPUT VARCHAR2(5000);
CURSOR CUSTOMER_DATE IS
SELECT DISTINCT HCSA.ORG_ID,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
HCP.CUST_ACCOUNT_PROFILE_ID PROFILE_ID,
HCP_NAME.NAME PROFILE_NAME,
HCPA.CURRENCY_CODE,
HCPA.CUST_ACCT_PROFILE_AMT_ID,
HCPA.OVERALL_CREDIT_LIMIT,
HCPA.OBJECT_VERSION_NUMBER
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUST_ACCT_SITES_ALL HCSA,
--HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_PROFILE_CLASSES HCP_NAME,
HZ_CUSTOMER_PROFILES HCP,
HZ_CUST_PROFILE_AMTS HCPA
WHERE 1 = 1
AND HP.PARTY_ID = HPS.PARTY_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
--AND HCSU.CUST_ACCT_SITE_ID = HCSA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCPA.CUST_ACCOUNT_ID(+)
and HCPA.CUST_ACCOUNT_PROFILE_ID = HCP.CUST_ACCOUNT_PROFILE_ID(+)
AND HCP.PROFILE_CLASS_ID = HCP_NAME.PROFILE_CLASS_ID(+)
AND HCSA.ORG_ID = 216
AND HCA.CUST_ACCOUNT_ID = 8687576
--AND HP.PARTY_NAME = 'XX資訊股份有限公司'
AND HCSA.STATUS <> 'I';
BEGIN
MO_GLOBAL.INIT('AR');
MO_GLOBAL.SET_POLICY_CONTEXT('S', P_OU_ID);
IF P_OU_ID = 103 THEN
FND_GLOBAL.APPS_INITIALIZE(0, 50243, 222); --SYSADMIN、ACETJ_AR_KeyUser、應收帳款管理系統
ELSIF P_OU_ID = 136 THEN
FND_GLOBAL.APPS_INITIALIZE(0, 50539, 222); --SYSADMIN、ACETW_AR_SuperUser、應收帳款管理系統
ELSIF P_OU_ID = 197 THEN
FND_GLOBAL.APPS_INITIALIZE(0, 51076, 222); --SYSADMIN、ACELY_AR_SuperUser、應收帳款管理系統
ELSIF P_OU_ID = 216 THEN
FND_GLOBAL.APPS_INITIALIZE(0, 51593, 222); --SYSADMIN、AEG_AR_SuperUser、應收帳款管理系統
ELSE
NULL;
END IF;
FOR C1 IN CUSTOMER_DATE LOOP
--這邊需要一個客製資料表來抓取新的信用額度
P_OVERALL_CREDIT_LIMIT := 991;
DBMS_OUTPUT.PUT_LINE('CUST_ACCT_PROFILE_AMT_ID = ' || C1.CUST_ACCT_PROFILE_AMT_ID);
DBMS_OUTPUT.PUT_LINE('New Credit Limit = ' || P_OVERALL_CREDIT_LIMIT);
P_CUSTOMER_PROFILE_AMT.CUST_ACCOUNT_PROFILE_ID := C1.PROFILE_ID;
P_CUSTOMER_PROFILE_AMT.CUST_ACCT_PROFILE_AMT_ID := C1.CUST_ACCT_PROFILE_AMT_ID;
P_CUSTOMER_PROFILE_AMT.CUST_ACCOUNT_ID := C1.CUST_ACCOUNT_ID;
--P_CUSTOMER_PROFILE_AMT.SITE_USE_ID := C1.SITE_USE_ID; --這邊先註解，因為信用額度可以建立於Site層
P_CUSTOMER_PROFILE_AMT.CURRENCY_CODE := C1.CURRENCY_CODE;
P_CUSTOMER_PROFILE_AMT.OVERALL_CREDIT_LIMIT := P_OVERALL_CREDIT_LIMIT;
DBMS_OUTPUT.PUT_LINE('Profile amt version = ' || C1.OBJECT_VERSION_NUMBER);
P_OBJECT_VERSION_NUMBER := C1.OBJECT_VERSION_NUMBER;
HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUST_PROFILE_AMT(P_INIT_MSG_LIST => 'T',
P_CUST_PROFILE_AMT_REC => P_CUSTOMER_PROFILE_AMT,
P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
DBMS_OUTPUT.PUT_LINE('x_return_status = ' || SUBSTR(X_RETURN_STATUS, 1, 255));
DBMS_OUTPUT.PUT_LINE('NEW OBJECT_VERSION_NUMBER = ' || TO_CHAR(P_OBJECT_VERSION_NUMBER));
DBMS_OUTPUT.put_line('x_msg_count = ' || TO_CHAR(X_MSG_COUNT));
DBMS_OUTPUT.put_line('x_msg_data = ' || SUBSTR(X_MSG_DATA, 1, 255));
IF X_MSG_COUNT > 1 THEN
FOR I IN 1 .. X_MSG_COUNT LOOP
DBMS_OUTPUT.PUT_LINE(I || '.' || SUBSTR(FND_MSG_PUB.GET(P_ENCODED => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
[Oracle]Query Receipt Methods收款方式三層
SELECT ARC.NAME 收款類別,
ARM.NAME 收款方式,
ARM.START_DATE,
ARM.END_DATE,
ARMA.ORG_ID 營運單位ID,
CBBV.BANK_NAME 銀行名稱,
CBBV.BANK_BRANCH_NAME 分行名稱,
CBA.BANK_ACCOUNT_NAME 銀行帳戶名稱,
CBA.BANK_ACCOUNT_NUM 銀行帳號,
CBA.CURRENCY_CODE 帳戶幣別,
ARMA.START_DATE,
ARMA.END_DATE,
ARMA.CASH_CCID,
GCC1.SEGMENT1 ||'.'||GCC1.SEGMENT2||'.'||GCC1.SEGMENT3||'.'||GCC1.SEGMENT4||'.'||GCC1.SEGMENT5||'.'||GCC1.SEGMENT6 現金,
ARMA.RECEIPT_CLEARING_CCID,
GCC2.SEGMENT1 ||'.'||GCC2.SEGMENT2||'.'||GCC2.SEGMENT3||'.'||GCC2.SEGMENT4||'.'||GCC2.SEGMENT5||'.'||GCC2.SEGMENT6 收款確認,
ARMA.REMITTANCE_CCID,
ARMA.BANK_CHARGES_CCID,
ARMA.UNAPPLIED_CCID,
ARMA.UNIDENTIFIED_CCID,
ARMA.ON_ACCOUNT_CCID
FROM APPS.AR_RECEIPT_CLASSES ARC,
APPS.AR_RECEIPT_METHODS ARM,
APPS.AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMA, -- 關聯中間表
APPS.CE_BANK_ACCT_USES_ALL CBAU, -- 帳戶使用表
APPS.CE_BANK_ACCOUNTS CBA, -- 銀行帳戶表
APPS.CE_BANK_BRANCHES_V CBBV, -- 銀行分行檢視表 (包含銀行名稱)
APPS.GL_CODE_COMBINATIONS GCC1,
APPS.GL_CODE_COMBINATIONS GCC2
WHERE ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID
AND ARM.RECEIPT_METHOD_ID = ARMA.RECEIPT_METHOD_ID
AND ARMA.REMIT_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID
AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND ARMA.CASH_CCID = GCC1.CODE_COMBINATION_ID
AND ARMA.RECEIPT_CLEARING_CCID = GCC2.CODE_COMBINATION_ID
AND ARC.NAME = 'ACETW_收票'
AND ARM.NAME = 'TW_票據-中信#6146'
AND TRUNC(NVL(ARMA.END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
ORDER BY ARM.NAME, ARMA.ORG_ID;
[Oracle]Customer Sales Territory設定來源
AR > Flexfiekds > Key > Segments
AR > Flexfiekds > Key > Values
AR > Setup > Transactions > Territories
2026-05-15
[Oracle]R12 客戶信用額度
Credit Limit(信用額度)
針對信用風險狀況，當保留時顯示Credit check hold applied. Overall limit exceeded. Using customer credit profile.
Order Credit Limit(訂單信用額度)
單筆最大訂單金額，當保留時顯示Credit check hold applied. Order limit exceeded. Using customer credit profile.
[Oracle]客戶修改名稱出現錯誤(This tax registration number is already in use for party type: Third Party and party name: XXX for the same period.)
參閱文件編號(KB780042)R12: AP/AR/ZX: How to Create More Than One Party With the Same VAT Registration Number to Avoid the Error 'This tax registration number is already in use for party type: Third Party and party name: XXXXXX for the same period.'
SELECT * FROM APPS.HZ_PARTIES HP WHERE HP.PARTY_NUMBER IN (472473,471303,472474)，發現這個名稱關係如下
28112955(這邊移除)
依照文件研究了一下後，決定把Tax Managers > Parties > Party Tax Profiles，查詢出要異動的資料把Default Reporting Registration Number清空，大概問題就是同一個Tax Number，不能建立於多個地方，
20260429 有測試打勾重覆Tax Number沒有作用的原因，大概是因為客戶主檔的Tax Profile有兩個地方Party與地址，我一直測試地址
訂閱：
文章 (Atom)