fantasy 的恬淡心情

--Update 客戶剩餘的信用額度

 
CREATE OR REPLACE PACKAGE XXX_CMS_OM_INT_PKG AS
  PROCEDURE EXPOSURE_TMEP(P_ORG_ID  IN NUMBER
                         ,P_USER_ID IN NUMBER
                         ,P_OK_FLG  OUT VARCHAR2);
  PROCEDURE NEW_EXPOSURE_IMPORT(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2);
 
  PROCEDURE UPD_EXPOSURE_TEMP(P_CUST_NUM  IN VARCHAR2
                             ,P_TYPE      IN VARCHAR2
                             ,P_ERROR_MSG IN VARCHAR2);
END XXX_CMS_OM_INT_PKG;
/
CREATE OR REPLACE PACKAGE BODY XXX_CMS_OM_INT_PKG AS
  PROCEDURE EXPOSURE_TMEP(P_ORG_ID  IN NUMBER
                         ,P_USER_ID IN NUMBER
                         ,P_OK_FLG  OUT VARCHAR2) IS
    CUSTUSESITEID NUMBER;
    L_CHK_DATA_ERROR EXCEPTION;
    --
    LS_CUST_NAME     VARCHAR2(240);
    LS_NO_DATA_FOUND VARCHAR2(100);
    L_EXPOSURE_IFC   ONT.OE_EXPOSURE_INTERFACE%ROWTYPE;
    L_EXPOSURE_BAK   OE_EXPOSURE_INTERFACE_BACKUP%ROWTYPE;
    LN_OK_CNT        NUMBER := 0;
    LS_ERROR_MSG     VARCHAR2(1000);
  BEGIN
    FND_FILE.PUT_LINE(FND_FILE.LOG
                     ,'Import to OE_EXPOSURE_INTERFACE Start Time:' ||
                      TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
    P_OK_FLG := 'Y';
 
    FOR L_TEMP_REC IN (SELECT *
                         FROM XXX.CMS_OE_EXPOSURE_INTERFACE
                        WHERE NVL(EXPOSURE_INTERFACE_FLAG, 'N') = 'N') LOOP
      FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
      BEGIN
        SELECT HP.PARTY_NAME
          INTO LS_CUST_NAME
          FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
         WHERE HCA.PARTY_ID = HP.PARTY_ID
           AND HCA.STATUS = 'A'
           AND HCA.ACCOUNT_NUMBER = L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          LS_NO_DATA_FOUND := 'Cust Number: ' ||
                              L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER ||
                              ' , not exists in ERP';
          RAISE L_CHK_DATA_ERROR;
      END;
    
      FND_FILE.PUT_LINE(FND_FILE.LOG
                       ,'Customer Number: ' ||
                        L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer Name: ' || LS_CUST_NAME);
    
      BEGIN
        BEGIN
          SELECT SITE_USE.LOCATION
            INTO CUSTUSESITEID -- find the customer use site id information
            FROM HZ_CUST_ACCOUNTS       CUST
                ,HZ_CUST_ACCT_SITES_ALL CUST_SITE
                ,HZ_CUST_SITE_USES_ALL  SITE_USE
           WHERE CUST.ACCOUNT_NUMBER = L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER
             AND CUST.CUST_ACCOUNT_ID = CUST_SITE.CUST_ACCOUNT_ID
             AND CUST_SITE.CUST_ACCT_SITE_ID = SITE_USE.CUST_ACCT_SITE_ID
             AND SITE_USE.PRIMARY_FLAG = 'Y'
             AND SITE_USE.STATUS = 'A'
             AND SITE_USE.SITE_USE_CODE = 'BILL_TO'
             AND SITE_USE.ORG_ID = P_ORG_ID;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            LS_NO_DATA_FOUND := 'Bill to Address not exists in ERP';
            RAISE L_CHK_DATA_ERROR;
        END;
      
        --Insert Values to OE_EXPOSURE_INTERFACE
        L_EXPOSURE_IFC.EXPOSURE_SOURCE_CODE := L_TEMP_REC.EXPOSURE_SOURCE_CODE;
        L_EXPOSURE_IFC.OPERATION_CODE       := L_TEMP_REC.OPERATION_CODE;
        L_EXPOSURE_IFC.EXPOSURE_AMOUNT      := L_TEMP_REC.EXPOSURE_AMOUNT;
        L_EXPOSURE_IFC.CURRENCY_CODE        := L_TEMP_REC.CURRENCY_CODE;
        L_EXPOSURE_IFC.BILL_TO_SITE_USE_ID  := CUSTUSESITEID;
        L_EXPOSURE_IFC.ORG_ID               := P_ORG_ID;
        L_EXPOSURE_IFC.CREATED_BY           := P_USER_ID;
        L_EXPOSURE_IFC.LAST_UPDATED_BY      := P_USER_ID;
        L_EXPOSURE_IFC.CREATION_DATE        := SYSDATE;
        L_EXPOSURE_IFC.LAST_UPDATE_DATE     := SYSDATE;
      
        INSERT INTO ONT.OE_EXPOSURE_INTERFACE VALUES L_EXPOSURE_IFC;
      
        COMMIT;
        
        LN_OK_CNT := LN_OK_CNT + 1;
        
        UPD_EXPOSURE_TEMP(P_CUST_NUM  => L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER
                         ,P_TYPE      => 'S'
                         ,P_ERROR_MSG => NULL);
      EXCEPTION
        WHEN L_CHK_DATA_ERROR THEN
          FND_FILE.PUT_LINE(FND_FILE.LOG
                           ,'Error Message: ' || LS_NO_DATA_FOUND);
        WHEN OTHERS THEN
          LS_ERROR_MSG := TO_CHAR(SQLCODE) || SQLERRM;
          ROLLBACK;
          UPD_EXPOSURE_TEMP(P_CUST_NUM  => L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER
                           ,P_TYPE      => 'F'
                           ,P_ERROR_MSG => LS_ERROR_MSG);
          
        
          FND_FILE.PUT_LINE(FND_FILE.LOG
                           ,'Insert Data has errors: ' || LS_ERROR_MSG);
      END;
    
      IF LN_OK_CNT = 0 THEN
        P_OK_FLG := 'N';
      END IF;
    END LOOP;
 
    FND_FILE.PUT_LINE(FND_FILE.LOG
                     ,'Import to OE_EXPOSURE_INTERFACE END Time:' ||
                      TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
  END EXPOSURE_TMEP;
  PROCEDURE NEW_EXPOSURE_IMPORT(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2) IS
    LN_ORG_ID     NUMBER := FND_PROFILE.VALUE('ORG_ID');
    LN_USER_ID    NUMBER := FND_PROFILE.VALUE('USER_ID');
    LS_OK_FLG     VARCHAR2(1);
    LN_REQUEST_ID NUMBER;
    LS_PHASE      VARCHAR2(50);
    LS_STATUS     VARCHAR2(50);
    LS_DEV_PHASE  VARCHAR2(50);
    LS_DEV_STATUS VARCHAR2(50);
    LS_MSG        VARCHAR2(100);
  BEGIN
    FND_FILE.PUT_LINE(FND_FILE.LOG
                     ,'Start Time:' ||
                      TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(LN_ORG_ID);
 
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org ID:' || TO_CHAR(LN_ORG_ID));
 
    FND_GLOBAL.APPS_INITIALIZE(USER_ID      => LN_USER_ID
                              ,RESP_ID      => FND_PROFILE.VALUE('RESP_ID')
                              ,RESP_APPL_ID => FND_PROFILE.VALUE('RESP_APPL_ID'));
 
    --Improt Temp Table
    --IMPORT_EXPOSURE_PKG(X_RETCODE => LS_RETCODE, X_ERRBUF => LS_ERRBUF);
    EXPOSURE_TMEP(P_ORG_ID  => LN_ORG_ID
                 ,P_USER_ID => LN_USER_ID
                 ,P_OK_FLG  => LS_OK_FLG);
 
    IF LS_OK_FLG = 'Y' THEN
      --Submit Request to Run OE Exposure Import
      LN_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'ONT'
                                                 ,PROGRAM     => 'OEXCEIMP'
                                                 ,DESCRIPTION => NULL
                                                 ,START_TIME  => NULL
                                                 ,SUB_REQUEST => FALSE
                                                 ,ARGUMENT1   => LN_ORG_ID
                                                 ,ARGUMENT2   => 'COMMISSION'
                                                 ,ARGUMENT3   => NULL
                                                 ,ARGUMENT4   => 'N'
                                                 ,ARGUMENT5   => CHR(0));
      COMMIT;
    
      FND_FILE.PUT_LINE(FND_FILE.LOG
                       ,'Request ID:' || TO_CHAR(LN_REQUEST_ID));
    
      IF LN_REQUEST_ID > 0 THEN
        IF FND_CONCURRENT.WAIT_FOR_REQUEST(LN_REQUEST_ID
                                          ,10
                                          ,0
                                          ,LS_PHASE
                                          ,LS_STATUS
                                          ,LS_DEV_PHASE
                                          ,LS_DEV_STATUS
                                          ,LS_MSG) THEN
          FND_FILE.PUT_LINE(FND_FILE.LOG
                           ,'OE Exposure Import IS successful');
        ELSE
          FND_FILE.PUT_LINE(FND_FILE.LOG, 'OE Exposure Import IS faild');
        END IF;
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'Run Request has errors');
      END IF;
    END IF;
    FND_FILE.PUT_LINE(FND_FILE.LOG
                     ,'End Time:' ||
                      TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
  END NEW_EXPOSURE_IMPORT;
 
  PROCEDURE UPD_EXPOSURE_TEMP(P_CUST_NUM  IN VARCHAR2
                             ,P_TYPE      IN VARCHAR2
                             ,P_ERROR_MSG IN VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;                         
  BEGIN
    IF P_TYPE = 'S' THEN
      UPDATE XXX.CMS_OE_EXPOSURE_INTERFACE XT
         SET XT.EXPOSURE_INTERFACE_FLAG = 'Y'
            ,XT.CHECK_FLAG              = 'Y'
            ,XT.IMPORT_FLAG             = 'Y'
            ,XT.SUCCESS_DATE            = SYSDATE
       WHERE XT.BILL_TO_CUSTOMER_NUMBER = P_CUST_NUM
         AND NVL(XT.EXPOSURE_INTERFACE_FLAG, 'N') = 'N';
    ELSE
      UPDATE XXX.CMS_OE_EXPOSURE_INTERFACE XT
         SET XT.EXPOSURE_INTERFACE_FLAG = 'N'
            ,XT.CHECK_FLAG              = 'N'
            ,XT.IMPORT_FLAG             = 'N'
            ,XT.ERR_MSG                 = P_ERROR_MSG
            ,XT.SUCCESS_DATE            = NULL
       WHERE XT.BILL_TO_CUSTOMER_NUMBER = P_CUST_NUM
         AND NVL(XT.EXPOSURE_INTERFACE_FLAG, 'N') = 'N';     
    END IF;
    
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
       FND_FILE.PUT_LINE(FND_FILE.LOG
                        ,'Update XXX.CMS_OE_EXPOSURE_INTERFACE has errors');
  END UPD_EXPOSURE_TEMP;                           
END XXX_CMS_OM_INT_PKG;
/