到期日:可以透過上方作業 > 調整來進行修改
付款條件:需透過Data Fix
UPDATE RA_CUSTOMER_TRX_ALL A
SET A.TERM_ID = 1033
WHERE A.CUSTOMER_TRX_ID = 2490433
2026-05-20
[Oracle]R12:修改付款條件與到期日
[Oracel]R12標準報表AR: Journal Entries Report拆解
20220301 Patch 32920876: JOURNAL ENTRIES REPORT PERFORMANCE AR-ARRGTA: -1555 ORA-01555: SNAPSHOT TOO OLD，已正常
SELECT ADJ.SET_OF_BOOKS_ID,
ADJ.ORG_ID,
ADJ.GL_DATE,
CT.INVOICE_CURRENCY_CODE,
ARD.CODE_COMBINATION_ID,
NVL(ARD.AMOUNT_DR, 0) ENTERED_DR,
NVL(ARD.AMOUNT_CR, 0) ENTERED_CR,
NVL(ARD.ACCTD_AMOUNT_DR, 0) ACCTD_DR,
NVL(ARD.ACCTD_AMOUNT_CR, 0) ACCTD_CR,
ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_FUNCTION_MAPPING',
'ADJ_' || ARD.SOURCE_TYPE) SOURCE_TYPE,
GCC.SEGMENT1 ||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 ACCOUNT_ID
FROM RA_CUSTOMER_TRX_ALL CT,
RA_CUST_TRX_TYPES_ALL CTT,
AR_DISTRIBUTIONS_ALL ARD,
HZ_CUST_ACCOUNTS_ALL CUST,
AR_ADJUSTMENTS_ALL ADJ,
GL_CODE_COMBINATIONS GCC
WHERE 1 = 1
AND ARD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND NVL(ADJ.POSTABLE, 'Y') = 'Y'
AND ADJ.ADJUSTMENT_ID = ARD.SOURCE_ID
AND ARD.SOURCE_TABLE = 'ADJ'
AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID
AND CTT.ORG_ID = CT.ORG_ID
AND ARD.ORG_ID = CT.ORG_ID
AND ADJ.ORG_ID = CT.ORG_ID
AND CUST.CUST_ACCOUNT_ID =
DECODE(CTT.TYPE, 'BR', CT.DRAWEE_ID, CT.BILL_TO_CUSTOMER_ID)
AND CT.INVOICE_CURRENCY_CODE = CT.INVOICE_CURRENCY_CODE
AND ADJ.GL_DATE BETWEEN TO_DATE('2021/12/01', 'YYYY/MM/DD') AND
TO_DATE('2021/12/31', 'YYYY/MM/DD')
AND CT.ORG_ID = 136
UNION ALL
SELECT CT.SET_OF_BOOKS_ID SOB_ID,
CTLGD.ORG_ID,
CTLGD.GL_DATE GL_DATE,
CT.INVOICE_CURRENCY_CODE CURRENCY,
CTLGD.CODE_COMBINATION_ID CCID,
TO_NUMBER(
DECODE(CTLGD.ACCOUNT_CLASS,'REC',
DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,NULL,NVL(CTLGD.AMOUNT, 0)) ,
DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,-NVL(CTLGD.AMOUNT, 0),NULL)),
99999999) ENTERED_DR,
TO_NUMBER(
DECODE(CTLGD.ACCOUNT_CLASS,'REC',
DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,-NVL(CTLGD.AMOUNT, 0),NULL),
DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,NULL,NVL(CTLGD.AMOUNT, 0))),
99999999) ENTERED_CR,
TO_NUMBER(
DECODE(CTLGD.ACCOUNT_CLASS,
'REC',
DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,NULL,NVL(CTLGD.ACCTD_AMOUNT, 0)),
DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,-NVL(CTLGD.ACCTD_AMOUNT, 0),NULL)),
99999999) ACCTD_DR,
TO_NUMBER(
DECODE(CTLGD.ACCOUNT_CLASS,
'REC',
DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,-NVL(CTLGD.ACCTD_AMOUNT, 0),NULL),
DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,NULL,NVL(CTLGD.ACCTD_AMOUNT, 0))),
99999999) ACCTD_CR,
ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_FUNCTION_MAPPING',
DECODE(CTT.TYPE,
'CM',
'CM_',
'DM',
'DM_',
'CB',
'CB_',
'INV_') ||
NVL(CTLGD.ACCOUNT_CLASS, 'REV')) SOURCE_TYPE,
GCC.SEGMENT1 ||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 ACCOUNT_ID
FROM RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD,
RA_CUSTOMER_TRX_ALL CT,
RA_CUST_TRX_TYPES_ALL CTT,
HZ_CUST_ACCOUNTS_ALL CUST,
GL_CODE_COMBINATIONS GCC
WHERE 1=1
AND CTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND CTLGD.EVENT_ID IS NOT NULL
AND CTLGD.ACCOUNT_SET_FLAG = 'N'
AND CTLGD.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.ORG_ID = CTT.ORG_ID
AND CT.ORG_ID = CTLGD.ORG_ID
AND CUST.CUST_ACCOUNT_ID = CT.BILL_TO_CUSTOMER_ID
AND DECODE(CTLGD.ACCOUNT_CLASS,
'ROUND',
(NVL(CTLGD.AMOUNT, 0) + NVL(CTLGD.ACCTD_AMOUNT, 0)),
1) != DECODE(CTLGD.ACCOUNT_CLASS, 'ROUND', 0, 2)
AND CTT.TYPE IN ('INV', 'GUAR', 'DEP', 'CM', 'DM', 'CB')
AND CTLGD.GL_DATE BETWEEN TO_DATE('2021/12/01', 'YYYY/MM/DD') AND
TO_DATE('2021/12/31', 'YYYY/MM/DD')
AND CTLGD.ORG_ID = 136
[Oracle]R12如何調整AR Distribution科目
AR Transaction Receivable，掛到1101現金，目前這個設定是吃客戶主檔
文件Create Accounting with error caused by the incorrect CCID for Adjustment distribution (Doc ID 2307048.1)
AR > Setup > Receipt > Receivable Activity
將GL Account Source 修改為ACTIVITY_GL_ACCOUNT
再進入AR Transaction後，上方作業的調整就可以修改科目，而非只有金額
2026-05-18
[Oracle]R12:客戶新增API
TCA APIs Usage and Customer Account Creation Sample Code, Queries and Screens (Doc ID 837568.1)
[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;
訂閱：
文章 (Atom)