[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;