--更新 信用評比，彈性欄位
--ATTR1 ACCOUNT_NUMBER 必要
--ATTR2 客戶名稱 非必要
--ATTR3 客戶分級 必要 對應 標準欄位 CREDIT_RATING
--ATTR4 資本額 必要 對應 彈性欄位 ATTRIBUTE8
--ATTR5 設立時間 必要 對應 彈性欄位 ATTRIBUTE9 (備註使用字串就好，好像會自己轉換時間，使用時間反而出錯)
--ATTR6 空白回寫狀態用
DECLARE
P_ORG_ID NUMBER := 0;
P_CUST_ACCOUNT_ID NUMBER := 0;
P_CUST_ACCOUNT_PROFILE_ID NUMBER := 0;
CURSOR CUSTOMER IS
SELECT ATTR1, ATTR2, ATTR3, TO_NUMBER(ATTR4,999999999999999) ATTR4, ATTR5
FROM YOLIN_TEMP
WHERE 1=1
--AND ATTR1 = '12304443';
AND ATTR6 IS NULL;
BEGIN
FOR C1 IN CUSTOMER LOOP
BEGIN
SELECT DISTINCT HCSA.ORG_ID,
HCA.CUST_ACCOUNT_ID,
HCP.CUST_ACCOUNT_PROFILE_ID
INTO P_ORG_ID,
P_CUST_ACCOUNT_ID,
P_CUST_ACCOUNT_PROFILE_ID
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,
HZ_CUSTOMER_PROFILES HCP
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 HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'
AND HCSA.ORG_ID = 136
AND HCA.ACCOUNT_NUMBER = C1.ATTR1
--AND HP.PARTY_NAME LIKE '%台達%' --43938484
AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID;
--ORDER BY HP.PARTY_NUMBER, HCSU.SITE_USE_CODE, HCSU.LOCATION;
UPDATE HZ_CUST_ACCOUNTS_ALL
SET ATTRIBUTE8 = C1.ATTR4,
ATTRIBUTE9 = C1.ATTR5,
LAST_UPDATE_DATE = SYSDATE
WHERE CUST_ACCOUNT_ID = P_CUST_ACCOUNT_ID;
UPDATE HZ_CUSTOMER_PROFILES
SET CREDIT_RATING = C1.ATTR3,
LAST_UPDATE_DATE = SYSDATE
WHERE CUST_ACCOUNT_PROFILE_ID = P_CUST_ACCOUNT_PROFILE_ID;
UPDATE YOLIN_TEMP SET ATTR6 = 'OK' WHERE ATTR1 = C1.ATTR1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到客戶:' || C1.ATTR1);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('異常:' || C1.ATTR1);
END;
END LOOP;
COMMIT;
END;
--SELECT * FROM HZ_CUST_ACCOUNTS_ALL WHERE ACCOUNT_NUMBER = '05425224'
--SELECT * FROM HZ_CUSTOMER_PROFILES WHERE CUST_ACCOUNT_PROFILE_ID = 8623002
--SELECT * FROM HZ_CUST_ACCOUNTS_ALL WHERE LAST_UPDATE_DATE > TO_DATE('20220916 17:00:00','YYYYMMDD HH24:MI:SS')
--SELECT * FROM HZ_CUSTOMER_PROFILES WHERE LAST_UPDATE_DATE > TO_DATE('20220916 17:00:00','YYYYMMDD HH24:MI:SS')
--SELECT * FROM YOLIN_TEMP WHERE V2 = '台灣積體電路製造股份有限公司'
--SELECT * FROM YOLIN_TEMP WHERE V1 IS NULL
--DELETE YOLIN_TEMP WHERE V1 IS NULL
/*
SELECT HCSA.ORG_ID,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
HCA.ATTRIBUTE8,
HCA.ATTRIBUTE9,
HCP.CUST_ACCOUNT_PROFILE_ID,
HCP.CREDIT_RATING
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,
HZ_CUSTOMER_PROFILES HCP
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 HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'
AND HCSA.ORG_ID = 136
AND HCA.ACCOUNT_NUMBER = 'N123848233'
--AND HP.PARTY_NAME LIKE '%台達%' --43938484
--AND HCA.CUST_ACCOUNT_ID = '500775'
AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID
ORDER BY HP.PARTY_NUMBER, HCSU.SITE_USE_CODE, HCSU.LOCATION;
*/
create table YOLIN_TEMP
(
rpt_id VARCHAR2(32),
line_no NUMBER,
attr1 VARCHAR2(2000),
attr2 VARCHAR2(2000),
attr3 VARCHAR2(2000),
attr4 VARCHAR2(2000),
attr5 VARCHAR2(2000),
attr6 VARCHAR2(2000),
attr7 VARCHAR2(2000),
attr8 VARCHAR2(2000),
attr9 VARCHAR2(2000),
attr10 VARCHAR2(200),
attr11 VARCHAR2(200),
attr12 VARCHAR2(200),
attr13 VARCHAR2(200),
attr14 VARCHAR2(200),
attr15 VARCHAR2(200),
attr16 VARCHAR2(200),
attr17 VARCHAR2(200),
attr18 VARCHAR2(200),
attr19 VARCHAR2(200),
attr20 VARCHAR2(200),
attr21 VARCHAR2(200),
attr22 VARCHAR2(200),
attr23 VARCHAR2(200),
attr24 VARCHAR2(200),
attr25 VARCHAR2(200),
attr26 VARCHAR2(200),
attr27 VARCHAR2(200),
attr28 VARCHAR2(200),
attr29 VARCHAR2(200),
attr30 VARCHAR2(200),
attr31 VARCHAR2(200),
attr32 VARCHAR2(200),
attr33 VARCHAR2(200),
attr34 VARCHAR2(200),
attr35 VARCHAR2(200),
attr36 VARCHAR2(200),
attr37 VARCHAR2(200),
attr38 VARCHAR2(200),
attr39 VARCHAR2(200),
attr40 VARCHAR2(200),
attr41 VARCHAR2(200),
attr42 VARCHAR2(200),
attr43 VARCHAR2(200),
attr44 VARCHAR2(200),
attr45 VARCHAR2(200),
attr46 VARCHAR2(200),
attr47 VARCHAR2(200),
attr48 VARCHAR2(200),
attr49 VARCHAR2(200),
attr50 VARCHAR2(200),
attr51 VARCHAR2(200),
attr52 VARCHAR2(200),
attr53 VARCHAR2(200),
attr54 VARCHAR2(200),
attr55 VARCHAR2(200),
attr56 VARCHAR2(200),
attr57 VARCHAR2(200),
attr58 VARCHAR2(200),
attr59 VARCHAR2(200),
attr60 VARCHAR2(200),
attr61 VARCHAR2(200),
attr62 VARCHAR2(200),
attr63 VARCHAR2(200),
attr64 VARCHAR2(200),
attr65 VARCHAR2(200),
attr66 VARCHAR2(200),
attr67 VARCHAR2(200),
attr68 VARCHAR2(200),
attr69 VARCHAR2(200),
attr70 VARCHAR2(200),
attr71 VARCHAR2(200),
attr72 VARCHAR2(200),
attr73 VARCHAR2(200),
attr74 VARCHAR2(200),
attr75 VARCHAR2(200),
attr76 VARCHAR2(200),
attr77 VARCHAR2(200),
attr78 VARCHAR2(200),
attr79 VARCHAR2(200),
attr80 VARCHAR2(200),
attr81 VARCHAR2(200),
attr82 VARCHAR2(200),
attr83 VARCHAR2(200),
attr84 VARCHAR2(200),
attr85 VARCHAR2(200),
attr86 VARCHAR2(200),
attr87 VARCHAR2(200),
attr88 VARCHAR2(200),
attr89 VARCHAR2(200),
attr90 VARCHAR2(200),
attr91 VARCHAR2(200),
attr92 VARCHAR2(200),
attr93 VARCHAR2(200),
attr94 VARCHAR2(200),
attr95 VARCHAR2(200),
attr96 VARCHAR2(200),
attr97 VARCHAR2(200),
attr98 VARCHAR2(200),
attr99 VARCHAR2(200),
bblob BLOB,
cclob CLOB,
bbfile BFILE
)
2023-03-08
[Oracle]更新標準欄位信用評比(CREDIT_RATING)與彈性欄位
建議使用API更新，此範例不好
訂閱：
張貼留言 (Atom)
沒有留言:
張貼留言