2个INIT和UPDATE ORACLE库的Package

INIT：
CREATE OR REPLACE PACKAGE BODY CUX_INIT_BASE_INFO IS

procedure INIT_ALL is
begin
    init_item;
    init_item_category_bkind;
    init_item_category_mkind;
    init_item_category_skind;
    init_item_category_bsort;
    init_item_category_msort;
    init_item_category_ssort;
end INIT_ALL;

--³õÊ¼»¯ITEM,½«ËùÓÐERP ITEMÐÅÏ¢µ¼Èë--
PROCEDURE INIT_ITEM IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM
      SELECT DISTINCT MSIB.INVENTORY_ITEM_ID,
                      MP.ORGANIZATION_ID,
                      MP.ORGANIZATION_CODE,
                      MSIB.SEGMENT1,
                      MSIB.SEGMENT2,
                      MSIB.SEGMENT3,
                      NVL(MSIB.PRIMARY_UOM_CODE, ' '), --µ¥Î»
                      NVL(MSIB.PRIMARY_UNIT_OF_MEASURE, ' '), --Ãû³Æ
                      NVL(MSIB.ATTRIBUTE13, ' ') BARCODE,
                      NVL(MSIB.LIST_PRICE_PER_UNIT, 0) PRICE,
                      SYSDATE

        FROM MTL_SYSTEM_ITEMS_B@ERP MSIB, MTL_PARAMETERS@ERP MP
       WHERE MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID;

    COMMIT;
    --Íê³É£¬ÉèÖÃ±êÖ¾Î»Îª'S'
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      --Ê§°Ü£¬±êÖ¾Î»Îª'F'
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL, MY_SQLERRM, 'CUX_ITEM', SYSDATE, '');
      COMMIT;

END INIT_ITEM;

--³õÊ¼»¯ITEM_CATEGORY--
PROCEDURE INIT_ITEM_CATEGORY_BKIND IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM_CATEGORY
      SELECT DISTINCT V.ITEM_ID, V.ORG_ID, '´óÀà', V.SEG1, SYSDATE
        FROM APPS_ITEM_CATEGORY_CLASS_V V;

    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         ''); --´óÀà
      COMMIT;

END INIT_ITEM_CATEGORY_BKIND;

PROCEDURE INIT_ITEM_CATEGORY_MKIND IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM_CATEGORY
      SELECT DISTINCT V.ITEM_ID, V.ORG_ID, 'ÖÐÀà', V.SEG2, SYSDATE
        FROM APPS_ITEM_CATEGORY_CLASS_V V;

    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         '');
      COMMIT;

END INIT_ITEM_CATEGORY_MKIND;

PROCEDURE INIT_ITEM_CATEGORY_SKIND IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM_CATEGORY
      SELECT DISTINCT V.ITEM_ID, V.ORG_ID, 'Ð¡Àà', V.SEG3, SYSDATE
        FROM APPS_ITEM_CATEGORY_CLASS_V V;

    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         '');
      COMMIT;

END INIT_ITEM_CATEGORY_SKIND;

PROCEDURE INIT_ITEM_CATEGORY_BSORT IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM_CATEGORY
      SELECT DISTINCT V.ITEM_ID, V.ORG_ID, '´óÏµÁÐ', V.SEG1, SYSDATE
        FROM APPS_ITEM_CATEGORY_SERIES_V V;

    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         '');
      COMMIT;

END INIT_ITEM_CATEGORY_BSORT;

PROCEDURE INIT_ITEM_CATEGORY_MSORT IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM_CATEGORY
      SELECT DISTINCT V.ITEM_ID, V.ORG_ID, 'ÖÐÏµÁÐ', V.SEG2, SYSDATE
        FROM APPS_ITEM_CATEGORY_SERIES_V V;

    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         '');
      COMMIT;

END INIT_ITEM_CATEGORY_MSORT;

PROCEDURE INIT_ITEM_CATEGORY_SSORT IS
    V_LOG_ID   NUMBER;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
BEGIN
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP ;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    INSERT INTO CUX_ITEM_CATEGORY
      SELECT DISTINCT V.ITEM_ID, V.ORG_ID, 'Ð¡ÏµÁÐ', V.SEG3, SYSDATE
        FROM APPS_ITEM_CATEGORY_SERIES_V V;

    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         '');
      COMMIT;

END INIT_ITEM_CATEGORY_SSORT;
END CUX_INIT_BASE_INFO;

UPDATE:
CREATE OR REPLACE PACKAGE BODY CUX_UPDATE_BASE_INFO IS
V_STARTDATE CONSTANT VARCHAR2(30) := '1900-01-01';
V_DATESTYLE CONSTANT VARCHAR2(30) := 'YYYY-MM-DD';
V_MARGINTIME CONSTANT NUMBER := 1 / 6;
/*GETLASTDATE ´Ó¸üÐÂÈÕÖ¾ÎÄ¼þÀï»ñÈ¡×î½üµÄ¸üÐÂÈÕÆÚ
p_update_moduleÊÇ¸üÐÂµÄupdate ModuleÀàÐÍ.
        */
FUNCTION GETLASTDATE(P_UPDATE_MODULE IN VARCHAR2) RETURN DATE IS
    LASTDATE DATE;
BEGIN
    SELECT NVL(MAX(T.LAST_UPDATED_DATE) - V_MARGINTIME,
               TO_DATE(V_STARTDATE, V_DATESTYLE))
      INTO LASTDATE
      FROM CUX_UPDATE_LOG T
     WHERE T.FLAG = 'S'
       AND T.UPDATED_MODULE = P_UPDATE_MODULE;
    RETURN LASTDATE;
END GETLASTDATE;

PROCEDURE UPDATE_ALL IS
BEGIN
    UPDATE_CUSTOMER;
    UPDATE_CUSTOMER_DTL_CONTACT;
    UPDATE_CUSTOMER_DTL_ADDRESS;
    UPDATE_CUSTOMER_DTL_TEL;
    UPDATE_CUSTOMER_DTL_FAX;
    UPDATE_VENDOR;
    UPDATE_VENDOR_DTL_ADD_TEL_FAX;
    UPDATE_VENDOR_DTL_MANAGER;
    UPDATE_ITEM;
    UPDATE_ITEM_CATEGORY;
    UPDATE_PACKAGE;

END UPDATE_ALL;
/*---------------------------------customer-------------------------------------*/
/*
¸üÐÂÊý¾Ý½»»»ÇøÄÚcux_customerµÄÊý¾Ý
*/
PROCEDURE UPDATE_CUSTOMER IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_CUSTOMER IS
      SELECT T.CUSTOMER_ID,
             T.CUSTOMER_NUMBER,
             T.CUSTOMER_NAME,
             T.CUSTOMER_CATEGORY_CODE,
             T.ORGANIZATION_CODE,
             T.STATUS,
             T.LAST_UPDATE_DATE
        FROM APPS_CUSTOMER_V T
       WHERE T.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_CUSTOMER');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    --¶Ô¸üÐÂÈÕÖ¾ÏÈ²åÈëµ±Ç°ERP¸üÐÂÊ±¼ä
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_CUSTOMER', -1, NULL, V_ERP_DATE);
    --²éÕÒÓÐÎÞ¼ÇÂ¼
    FOR V_CUSTOMER IN C_CUSTOMER LOOP
      V_PKID := V_CUSTOMER.CUSTOMER_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_CUSTOMER T
       WHERE T.CUSTOMER_ID = V_CUSTOMER.CUSTOMER_ID;
      --²»´æÔÚÔò²åÈë
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_CUSTOMER
        VALUES
          (V_CUSTOMER.CUSTOMER_ID,
           V_CUSTOMER.CUSTOMER_NUMBER,
           V_CUSTOMER.CUSTOMER_NAME,
           V_CUSTOMER.CUSTOMER_CATEGORY_CODE,
           V_CUSTOMER.ORGANIZATION_CODE,
           SYSDATE,
           V_CUSTOMER.STATUS);
        --´æÔÚÔò¸üÐÂ£¬Ê¹Óëµ±Ç°ÏµÍ³Ê±¼äÍ¬²½
      ELSIF (V_FLAG = 1) THEN
        UPDATE CUX_CUSTOMER T
           SET T.CUSTOMER_NUMBER        = V_CUSTOMER.CUSTOMER_NUMBER,
               T.CUSTOMER_NAME          = V_CUSTOMER.CUSTOMER_NAME,
               T.CUSTOMER_CATEGORY_CODE = V_CUSTOMER.CUSTOMER_CATEGORY_CODE,
               T.ORGANIZATION_CODE      = V_CUSTOMER.ORGANIZATION_CODE,
               T.STATUS                 = V_CUSTOMER.STATUS,
               T.LAST_UPDATED_DATE      = SYSDATE
         WHERE T.CUSTOMER_ID = V_CUSTOMER.CUSTOMER_ID;
      END IF;
    END LOOP;
    --Íê³É£¬ÉèÖÃ±êÖ¾Î»Îª'S'
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      --Ê§°Ü£¬±êÖ¾Î»Îª'F'
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);

      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_CUSTOMER',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_CUSTOMER;

--update customer_dtl_contact--
PROCEDURE UPDATE_CUSTOMER_DTL_CONTACT IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_CUSTOMER_DTL_CONTACT IS
      SELECT RC.CUSTOMER_ID, ACV.LAST_NAME
        FROM AR_CONTACTS_V@ERP ACV, RA_CUSTOMERS@ERP RC
       WHERE ACV.CUSTOMER_ID = RC.CUSTOMER_ID
         AND ACV.LAST_UPDATE_DATE >= V_LAST_DATE
       ORDER BY ACV.LAST_UPDATE_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_CUSTOMER_DTL_CONTACT');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_CUSTOMER_DTL_CONTACT', -1, NULL, V_ERP_DATE);
    FOR V_CUSTOMER_DTL_CONTACT IN C_CUSTOMER_DTL_CONTACT LOOP
      V_PKID := V_CUSTOMER_DTL_CONTACT.CUSTOMER_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_CUSTOMER_DTL T
       WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_CONTACT.CUSTOMER_ID;
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_CUSTOMER_DTL
        VALUES
          (V_CUSTOMER_DTL_CONTACT.CUSTOMER_ID,
           '',
           '',
           '',
           V_CUSTOMER_DTL_CONTACT.LAST_NAME,
           SYSDATE);
      ELSE
        UPDATE CUX_CUSTOMER_DTL T
           SET T.MANAGER          = V_CUSTOMER_DTL_CONTACT.LAST_NAME,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_CONTACT.CUSTOMER_ID;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_CUSTOMER_DTL_CONTACT',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_CUSTOMER_DTL_CONTACT;

-- update customer_dtl_address--
PROCEDURE UPDATE_CUSTOMER_DTL_ADDRESS IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_CUSTOMER_DTL_ADDRESS IS
      SELECT DISTINCT RC.CUSTOMER_ID CUSTOMER_ID,
                      NVL(HRL.ADDRESS1, ' ') ADDRESS,
                      HRL.LAST_UPDATE_DATE LAST_UPDATE_DATE
        FROM RA_CUSTOMERS@ERP   RC,
             HZ_PARTY_SITES@ERP HPS,
             HZ_LOCATIONS@ERP   HRL
       WHERE RC.PARTY_ID = HPS.PARTY_ID
         AND HRL.LOCATION_ID(+) = HPS.LOCATION_ID
         AND HPS.IDENTIFYING_ADDRESS_FLAG = 'Y'
         AND HRL.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_CUSTOMER_DTL_ADDRESS');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_CUSTOMER_DTL_ADDRESS', -1, NULL, V_ERP_DATE);
    FOR V_CUSTOMER_DTL_ADDRESS IN C_CUSTOMER_DTL_ADDRESS LOOP
      V_PKID := V_CUSTOMER_DTL_ADDRESS.CUSTOMER_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_CUSTOMER_DTL T
       WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_ADDRESS.CUSTOMER_ID;
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_CUSTOMER_DTL
        VALUES
          (V_CUSTOMER_DTL_ADDRESS.CUSTOMER_ID,
           V_CUSTOMER_DTL_ADDRESS.ADDRESS,
           '',
           '',
           '',
           SYSDATE);
      ELSE
        UPDATE CUX_CUSTOMER_DTL T
           SET T.ADDRESS          = V_CUSTOMER_DTL_ADDRESS.ADDRESS,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_ADDRESS.CUSTOMER_ID;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_CUSTOMER_DTL_ADDRESS',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_CUSTOMER_DTL_ADDRESS;

--update customer_dtl_tel--
PROCEDURE UPDATE_CUSTOMER_DTL_TEL IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_CUSTOMER_DTL_TEL IS
      SELECT DISTINCT RC.CUSTOMER_ID CUSTOMER_ID,
                      NVL(PHON.PHONE_NUMBER, ' ') TEL --µç»°1
        FROM RA_CUSTOMERS@ERP      RC,
             AR_CONTACTS_V@ERP     ACV,
             HZ_CONTACT_POINTS@ERP PHON
       WHERE ACV.CUSTOMER_ID(+) = RC.CUSTOMER_ID
         AND PHON.PRIMARY_FLAG = 'Y'
         AND RC.PARTY_ID = PHON.OWNER_TABLE_ID
         AND PHON.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_CUSTOMER_DTL_TEL');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_CUSTOMER_DTL_TEL', -1, NULL, V_ERP_DATE);
    FOR V_CUSTOMER_DTL_TEL IN C_CUSTOMER_DTL_TEL LOOP
      V_PKID := V_CUSTOMER_DTL_TEL.CUSTOMER_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_CUSTOMER_DTL T
       WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_TEL.CUSTOMER_ID;
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_CUSTOMER_DTL
        VALUES
          (V_CUSTOMER_DTL_TEL.CUSTOMER_ID,
           '',
           V_CUSTOMER_DTL_TEL.TEL,
           '',
           '',
           SYSDATE);
      ELSE
        UPDATE CUX_CUSTOMER_DTL T
           SET T.TEL = V_CUSTOMER_DTL_TEL.TEL, T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_TEL.CUSTOMER_ID;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_CUSTOMER_DTL_TEL',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_CUSTOMER_DTL_TEL;

--update customer_dtl_fax--
PROCEDURE UPDATE_CUSTOMER_DTL_FAX IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_CUSTOMER_DTL_FAX IS
      SELECT DISTINCT RC.CUSTOMER_ID, PHON.PHONE_NUMBER FAX
        FROM RA_CUSTOMERS@ERP      RC,
             AR_CONTACTS_V@ERP     ACV,
             HZ_CONTACT_POINTS@ERP PHON
       WHERE ACV.CUSTOMER_ID(+) = RC.CUSTOMER_ID
         AND PHON.PHONE_LINE_TYPE = 'FAX'
         AND RC.PARTY_ID = PHON.OWNER_TABLE_ID(+)
         AND PHON.LAST_UPDATE_DATE >= V_LAST_DATE;
BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_CUSTOMER_DTL_FAX');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_CUSTOMER_DTL_FAX', -1, NULL, V_ERP_DATE);
    FOR V_CUSTOMER_DTL_FAX IN C_CUSTOMER_DTL_FAX LOOP
      V_PKID := V_CUSTOMER_DTL_FAX.CUSTOMER_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_CUSTOMER_DTL T
       WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_FAX.CUSTOMER_ID;
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_CUSTOMER_DTL
        VALUES
          (V_CUSTOMER_DTL_FAX.CUSTOMER_ID,
           '',
           '',
           V_CUSTOMER_DTL_FAX.FAX,
           '',
           SYSDATE);
      ELSE
        UPDATE CUX_CUSTOMER_DTL T
           SET T.FAX = V_CUSTOMER_DTL_FAX.FAX, T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.CUSTOMER_ID = V_CUSTOMER_DTL_FAX.CUSTOMER_ID;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_CUSTOMER_DTL_FAX',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_CUSTOMER_DTL_FAX;

/*--------------------------vendor---------------------------------------------*/
/*¸üÐÂ»º³åÇøÄÚµÄ¹©Ó¦ÉÌÏà¹ØÊý¾Ý*/
PROCEDURE UPDATE_VENDOR IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_VENDOR IS
      SELECT DISTINCT PV.VENDOR_ID VENDOR_ID,
                      V.VENDOR_NUMBER VENDOR_NUMBER, --¹©Ó¦ÉÌ±àºÅ
                      NVL(PV.VENDOR_NAME, ' ') VENDOR_NAME --Ãû³Æ
        FROM PO_VENDORS@ERP PV, AP_VENDORS_V@ERP V
       WHERE PV.VENDOR_TYPE_LOOKUP_CODE = 'VENDOR'
         AND V.VENDOR_ID = PV.VENDOR_ID
         AND PV.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_VENDOR');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    --¶Ô¸üÐÂÈÕÖ¾ÏÈ²åÈëµ±Ç°ERP¸üÐÂÊ±¼ä
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_VENDOR', -1, NULL, V_ERP_DATE);
    --²éÕÒÓÐÎÞ¼ÇÂ¼
    FOR V_VENDOR IN C_VENDOR LOOP
      V_PKID := V_VENDOR.VENDOR_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_VENDOR T
       WHERE T.VENDOR_ID = V_VENDOR.VENDOR_ID;
      --²»´æÔÚÔò²åÈë
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_VENDOR
        VALUES
          (V_VENDOR.VENDOR_ID,
           V_VENDOR.VENDOR_NUMBER,
           V_VENDOR.VENDOR_NAME,
           SYSDATE);
        --´æÔÚÔò¸üÐÂ£¬Ê¹Óëµ±Ç°ÏµÍ³Ê±¼äÍ¬²½
      ELSIF (V_FLAG = 1) THEN
        UPDATE CUX_VENDOR T
           SET T.VENDOR_NUMBER    = V_VENDOR.VENDOR_NUMBER,
               T.VENDOR_NAME      = V_VENDOR.VENDOR_NAME,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.VENDOR_ID = V_VENDOR.VENDOR_ID;
      END IF;
    END LOOP;
    --Íê³É£¬ÉèÖÃ±êÖ¾Î»Îª'S'
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      --Ê§°Ü£¬±êÖ¾Î»Îª'F'

      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_VENDOR',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_VENDOR;

--update vendor address/tel/fax--
PROCEDURE UPDATE_VENDOR_DTL_ADD_TEL_FAX IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_VENDOR_ADDRESS_TEL_FAX IS
      SELECT DISTINCT PV.VENDOR_ID VENDOR_ID,
                      NVL(PVSA.ADDRESS_LINE1, ' ') VENDOR_ADDRESS, --µØÖ·
                      NVL(PVSA.AREA_CODE || PVSA.PHONE, ' ') TEL, --µç»°1
                      NVL(PVSA.FAX_AREA_CODE || PVSA.FAX, ' ') FAX, --´«Õæ
                      PVSA.LAST_UPDATE_DATE
        FROM PO_VENDORS@ERP PV, PO_VENDOR_SITES_ALL@ERP PVSA
       WHERE PV.VENDOR_ID = PVSA.VENDOR_ID
         AND PV.VENDOR_TYPE_LOOKUP_CODE = 'VENDOR'
         AND PV.LAST_UPDATE_DATE >= V_LAST_DATE
       ORDER BY PVSA.LAST_UPDATE_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_VENDOR_DTL_ADDRESS_TEL_FAX');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_VENDOR_DTL_ADDRESS_TEL_FAX', -1, NULL, V_ERP_DATE);
    FOR V_VENDOR_DTL_ADDRESS_TEL_FAX IN C_VENDOR_ADDRESS_TEL_FAX LOOP
      V_PKID := V_VENDOR_DTL_ADDRESS_TEL_FAX.VENDOR_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_VENDOR_DTL T
       WHERE T.VENDOR_ID = V_VENDOR_DTL_ADDRESS_TEL_FAX.VENDOR_ID;
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_VENDOR_DTL
        VALUES
          (V_VENDOR_DTL_ADDRESS_TEL_FAX.VENDOR_ID,
           V_VENDOR_DTL_ADDRESS_TEL_FAX.VENDOR_ADDRESS,
           V_VENDOR_DTL_ADDRESS_TEL_FAX.TEL,
           V_VENDOR_DTL_ADDRESS_TEL_FAX.FAX,
           '',
           SYSDATE);
      ELSE
        UPDATE CUX_VENDOR_DTL T
           SET T.ADDRESS          = V_VENDOR_DTL_ADDRESS_TEL_FAX.VENDOR_ADDRESS,
               T.TEL              = V_VENDOR_DTL_ADDRESS_TEL_FAX.TEL,
               T.FAX              = V_VENDOR_DTL_ADDRESS_TEL_FAX.FAX,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.VENDOR_ID = V_VENDOR_DTL_ADDRESS_TEL_FAX.VENDOR_ID;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_VENDOR_DTL_ADDRESS_TEL_FAX',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_VENDOR_DTL_ADD_TEL_FAX;

--update vendor manager--
PROCEDURE UPDATE_VENDOR_DTL_MANAGER IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      NUMBER;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_VENDOR_MANAGER IS
      SELECT DISTINCT PV.VENDOR_ID, NVL(PVC.LAST_NAME, ' ') MANAGER --¸ºÔðÈË
        FROM PO_VENDORS@ERP          PV,
             PO_VENDOR_SITES_ALL@ERP PVSA,
             PO_VENDOR_CONTACTS@ERP PVC
       WHERE PV.VENDOR_ID = PVSA.VENDOR_ID
         AND PVSA.VENDOR_SITE_ID = PVC.VENDOR_SITE_ID(+)
         AND PV.VENDOR_TYPE_LOOKUP_CODE = 'VENDOR'
         AND PVC.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_VENDOR_DTL_MANAGER');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_VENDOR_DTL_MANAGER', -1, NULL, V_ERP_DATE);
    FOR V_VENDOR_MANAGER IN C_VENDOR_MANAGER LOOP
      V_PKID := V_VENDOR_MANAGER.VENDOR_ID;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_VENDOR_DTL T
       WHERE T.VENDOR_ID = V_VENDOR_MANAGER.VENDOR_ID;
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_VENDOR_DTL
        VALUES
          (V_VENDOR_MANAGER.VENDOR_ID,
           '',
           '',
           '',
           V_VENDOR_MANAGER.MANAGER,
           SYSDATE);
      ELSE
        UPDATE CUX_VENDOR_DTL T
           SET T.MANAGER          = V_VENDOR_MANAGER.MANAGER,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.VENDOR_ID = V_VENDOR_MANAGER.VENDOR_ID;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_VENDOR_DTL_MANAGER',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_VENDOR_DTL_MANAGER;

/*--------------------------Inventory Item---------------------------------------------*/

/*¸üÐÂ»º³åÇøÄÚµÄÎïÁÏÊý¾Ý*/
PROCEDURE UPDATE_ITEM IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_ITEM IS
      SELECT DISTINCT MSIB.INVENTORY_ITEM_ID ITEM_ID,
                      MSIB.ORGANIZATION_ID ORG_ID,
                      MP.ORGANIZATION_CODE ORG_CODE,
                      MSIB.SEGMENT1,
                      MSIB.SEGMENT2,
                      MSIB.SEGMENT3,
                      NVL(MSIB.PRIMARY_UOM_CODE, ' ') UNIT, --µ¥Î»
                      NVL(MSIB.PRIMARY_UNIT_OF_MEASURE, ' ') UNIT_NAME,
                      NVL(MSIB.ATTRIBUTE13, ' ') BARCODE, --ÌõÂë
                      NVL(MSIB.LIST_PRICE_PER_UNIT, 0) PRICE --¼Û¸ñ

        FROM MTL_SYSTEM_ITEMS_B@ERP MSIB, MTL_PARAMETERS@ERP MP
       WHERE MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
         AND MSIB.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_ITEM');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    --¶Ô¸üÐÂÈÕÖ¾ÏÈ²åÈëµ±Ç°ERP¸üÐÂÊ±¼ä
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM', -1, NULL, V_ERP_DATE);
    --²éÕÒÓÐÎÞ¼ÇÂ¼
    FOR V_ITEM IN C_ITEM LOOP
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_ITEM T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM.ITEM_ID
         AND T.ORG_ID = V_ITEM.ORG_ID;
      --²»´æÔÚÔò²åÈë
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_ITEM
        VALUES
          (V_ITEM.ITEM_ID,
           V_ITEM.ORG_ID,
           V_ITEM.ORG_CODE,
           V_ITEM.SEGMENT1,
           V_ITEM.SEGMENT2,
           V_ITEM.SEGMENT3,
           V_ITEM.UNIT,
           V_ITEM.UNIT_NAME,
           V_ITEM.BARCODE,
           V_ITEM.PRICE,
           SYSDATE);
        COMMIT;
        --´æÔÚÔò¸üÐÂ£¬Ê¹Óëµ±Ç°ÏµÍ³Ê±¼äÍ¬²½
      ELSIF (V_FLAG = 1) THEN
        UPDATE CUX_ITEM T
           SET T.ORG_CODE         = V_ITEM.ORG_CODE,
               T.SEGMENT1         = V_ITEM.SEGMENT1,
               T.SEGMENT2         = V_ITEM.SEGMENT2,
               T.SEGMENT3         = V_ITEM.SEGMENT3,
               T.UNIT             = V_ITEM.UNIT,
               T.UNIT_NAME        = V_ITEM.UNIT_NAME,
               T.BARCODE          = V_ITEM.BARCODE,
               T.PRICE            = V_ITEM.PRICE,
               T.LAST_UPDATE_DATE = SYSDATE

         WHERE T.INVENTORY_ITEM_ID = V_ITEM.ITEM_ID
           AND T.ORG_ID = V_ITEM.ORG_ID;
        COMMIT;
      END IF;
    END LOOP;
    --Íê³É£¬ÉèÖÃ±êÖ¾Î»Îª'S'
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      --Ê§°Ü£¬±êÖ¾Î»Îª'F'
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL, MY_SQLERRM, 'CUX_ITEM', SYSDATE, '');
      COMMIT;

END UPDATE_ITEM;

/* update_item_barcode          */
/*PROCEDURE UPDATE_ITEM_BARCODE IS
    V_FLAG   NUMBER;
    V_LOG_ID NUMBER;

    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_ITEM_BARCODE IS
      SELECT DISTINCT MSIB.INVENTORY_ITEM_ID ITEM_ID,
                      MSIB.ORGANIZATION_ID ORG_ID,
                      NVL(MSIB.ATTRIBUTE13, ' ') BARCODE --ÌõÂë
        FROM MTL_SYSTEM_ITEMS_B@ERP MSIB
       WHERE MSIB.LAST_UPDATE_DATE >= V_LAST_DATE;
BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_ITEM');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;

    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM', -1, NULL, V_ERP_DATE);
    FOR V_ITEM_BARCODE IN C_ITEM_BARCODE LOOP
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_ITEM T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_BARCODE.ITEM_ID
         AND T.ORG_ID = V_ITEM_BARCODE.ORG_ID;

      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_ITEM
        VALUES
          (V_ITEM_BARCODE.ITEM_ID,
           V_ITEM_BARCODE.ORG_ID,
           '',
           '',
           '',
           '',
           '',
           '',

           SYSDATE,

           '',
           V_ITEM_BARCODE.BARCODE);
        COMMIT;
      ELSE
        UPDATE CUX_ITEM T
           SET T.BARCODE          = V_ITEM_BARCODE.BARCODE,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_BARCODE.ITEM_ID
           AND T.ORG_ID = V_ITEM_BARCODE.ORG_ID;
        COMMIT;
      END IF;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_BARCODE',
         SYSDATE,
         '');
      COMMIT;
      ROLLBACK;
END UPDATE_ITEM_BARCODE;*/

/*PROCEDURE UPDATE_ITEM_PRICE IS
      V_FLAG   NUMBER;
      V_LOG_ID NUMBER;

      V_LAST_DATE DATE;
      V_ERP_DATE DATE;
      MY_SQLERRM VARCHAR2(80);
      CURSOR C_ITEM_PRICE IS
        SELECT DISTINCT MSIB.INVENTORY_ITEM_ID ITEM_ID,
                        MSIB.ORGANIZATION_ID ORG_ID,
                        NVL(MSIB.LIST_PRICE_PER_UNIT, 0) PRICE --¼Û¸ñ
          FROM MTL_SYSTEM_ITEMS_B@ERP MSIB
         WHERE MSIB.LAST_UPDATE_DATE >= V_LAST_DATE;
    BEGIN
      V_LAST_DATE := GETLASTDATE('CUX_ITEM');
      SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;

      SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
      INSERT INTO CUX_UPDATE_LOG
      VALUES
        (V_LOG_ID, 'CUX_ITEM', -1, NULL, V_ERP_DATE);
      FOR V_ITEM_PRICE IN C_ITEM_PRICE LOOP
        SELECT COUNT(*)
          INTO V_FLAG
          FROM CUX_ITEM T
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_PRICE.ITEM_ID
           AND T.ORG_ID = V_ITEM_PRICE.ORG_ID;

        IF (V_FLAG = 0) THEN
          INSERT INTO CUX_ITEM
          VALUES
            (V_ITEM_PRICE.ITEM_ID,
             V_ITEM_PRICE.ORG_ID,
             '',
             '',
             '',
             '',
             '',
             '',
             SYSDATE,
             V_ITEM_PRICE.PRICE,
             '');
          COMMIT;
        ELSE
          UPDATE CUX_ITEM T
             SET T.PRICE = V_ITEM_PRICE.PRICE, T.LAST_UPDATE_DATE = SYSDATE
           WHERE T.INVENTORY_ITEM_ID = V_ITEM_PRICE.ITEM_ID
             AND T.ORG_ID = V_ITEM_PRICE.ORG_ID;
          COMMIT;
        END IF;
      END LOOP;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
        MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
        INSERT INTO CUX_ERROR_MESSAGE
        VALUES
          (CUX_ERROR_MESSAGE_S.NEXTVAL,
           MY_SQLERRM,
           'CUX_ITEM_PRICE',
           SYSDATE,
           '');
        COMMIT;
        ROLLBACK;
    END UPDATE_ITEM_PRICE;
*/
/*update_item_category   */
PROCEDURE UPDATE_ITEM_CATEGORY IS
    V_BKIND_FLAG NUMBER;
    V_MKIND_FLAG NUMBER;
    V_SKIND_FLAG NUMBER;
    V_BSORT_FLAG NUMBER;
    V_MSORT_FLAG NUMBER;
    V_SSORT_FLAG NUMBER;
    V_LOG_ID     NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE   DATE;
    MY_SQLERRM   VARCHAR2(80);
    CURSOR C_ITEM_CATEGORY IS
      SELECT DISTINCT MSIB.INVENTORY_ITEM_ID ITEM_ID,
                      MSIB.ORGANIZATION_ID ORG_ID,
                      (SELECT NVL(MICV.SEGMENT1, ' ')
                         FROM MTL_ITEM_CATEGORIES_V@ERP MICV
                        WHERE MICV.INVENTORY_ITEM_ID =
                              MSIB.INVENTORY_ITEM_ID
                          AND MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
                          AND MICV.CATEGORY_SET_NAME = 'PS_²úÆ·Àà±ð') BKIND, --´óÀà
                      (SELECT NVL(MICV.SEGMENT2, ' ')
                         FROM MTL_ITEM_CATEGORIES_V@ERP MICV
                        WHERE MICV.INVENTORY_ITEM_ID =
                              MSIB.INVENTORY_ITEM_ID
                          AND MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
                          AND MICV.CATEGORY_SET_NAME = 'PS_²úÆ·Àà±ð') MKIND, --ÖÐÀà
                      (SELECT NVL(MICV.SEGMENT3, ' ')
                         FROM MTL_ITEM_CATEGORIES_V@ERP MICV
                        WHERE MICV.INVENTORY_ITEM_ID =
                              MSIB.INVENTORY_ITEM_ID
                          AND MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
                          AND MICV.CATEGORY_SET_NAME = 'PS_²úÆ·Àà±ð') SKIND, --Ð¡Àà
                      (SELECT NVL(MICV.SEGMENT1, ' ')
                         FROM MTL_ITEM_CATEGORIES_V@ERP MICV
                        WHERE MICV.INVENTORY_ITEM_ID =
                              MSIB.INVENTORY_ITEM_ID
                          AND MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
                          AND MICV.CATEGORY_SET_NAME = 'PS_²úÆ·ÏµÁÐ') BSORT, --´óÏµÁÐ
                      (SELECT NVL(MICV.SEGMENT2, ' ')
                         FROM MTL_ITEM_CATEGORIES_V@ERP MICV
                        WHERE MICV.INVENTORY_ITEM_ID =
                              MSIB.INVENTORY_ITEM_ID
                          AND MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
                          AND MICV.CATEGORY_SET_NAME = 'PS_²úÆ·ÏµÁÐ') MSORT, --ÖÐÏµÁÐ
                      ' ' SSORT --Ð¡ÏµÁÐ
        FROM MTL_SYSTEM_ITEMS_B@ERP MSIB,

             MTL_ITEM_CATEGORIES_V@ERP MICV
       WHERE MSIB.INVENTORY_ITEM_ID = MICV.INVENTORY_ITEM_ID
         AND MSIB.ORGANIZATION_ID = MICV.ORGANIZATION_ID
         AND MICV.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_ITEM_CATEGORY');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_ITEM_CATEGORY', -1, NULL, V_ERP_DATE);
    FOR V_ITEM_CATEGORY IN C_ITEM_CATEGORY LOOP
      --´óÀà--
      SELECT COUNT(*)
        INTO V_BKIND_FLAG
        FROM CUX_ITEM_CATEGORY T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
         AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
         AND T.CATEGORY = '´óÀà';
      IF (V_BKIND_FLAG = 0) THEN
        INSERT INTO CUX_ITEM_CATEGORY
        VALUES
          (V_ITEM_CATEGORY.ITEM_ID,
           V_ITEM_CATEGORY.ORG_ID,
           '´óÀà',
           V_ITEM_CATEGORY.BKIND,
           SYSDATE);
      ELSE
        UPDATE CUX_ITEM_CATEGORY T
           SET T.CATEGORY_INFO    = V_ITEM_CATEGORY.BKIND,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
           AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
           AND T.CATEGORY = '´óÀà';
      END IF;

      --ÖÐÀà--
      SELECT COUNT(*)
        INTO V_MKIND_FLAG
        FROM CUX_ITEM_CATEGORY T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
         AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
         AND T.CATEGORY = 'ÖÐÀà';

      IF (V_MKIND_FLAG = 0) THEN
        INSERT INTO CUX_ITEM_CATEGORY
        VALUES
          (V_ITEM_CATEGORY.ITEM_ID,
           V_ITEM_CATEGORY.ORG_ID,
           'ÖÐÀà',
           V_ITEM_CATEGORY.MKIND,
           SYSDATE);
      ELSE
        UPDATE CUX_ITEM_CATEGORY T
           SET T.CATEGORY_INFO    = V_ITEM_CATEGORY.MKIND,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
           AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
           AND T.CATEGORY = 'ÖÐÀà';
      END IF;

      --Ð¡Àà--
      SELECT COUNT(*)
        INTO V_SKIND_FLAG
        FROM CUX_ITEM_CATEGORY T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
         AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
         AND T.CATEGORY = 'Ð¡Àà';

      IF (V_SKIND_FLAG = 0) THEN
        INSERT INTO CUX_ITEM_CATEGORY
        VALUES
          (V_ITEM_CATEGORY.ITEM_ID,
           V_ITEM_CATEGORY.ORG_ID,
           'Ð¡Àà',
           V_ITEM_CATEGORY.SKIND,
           SYSDATE);
      ELSE
        UPDATE CUX_ITEM_CATEGORY T
           SET T.CATEGORY_INFO    = V_ITEM_CATEGORY.SKIND,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
           AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
           AND T.CATEGORY = 'Ð¡Àà';
      END IF;

      --´óÏµÁÐ--
      SELECT COUNT(*)
        INTO V_BSORT_FLAG
        FROM CUX_ITEM_CATEGORY T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
         AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
         AND T.CATEGORY = '´óÏµÁÐ';

      IF (V_BSORT_FLAG = 0) THEN
        INSERT INTO CUX_ITEM_CATEGORY
        VALUES
          (V_ITEM_CATEGORY.ITEM_ID,
           V_ITEM_CATEGORY.ORG_ID,
           '´óÏµÁÐ',
           V_ITEM_CATEGORY.BSORT,
           SYSDATE);
      ELSE
        UPDATE CUX_ITEM_CATEGORY T
           SET T.CATEGORY_INFO    = V_ITEM_CATEGORY.BSORT,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
           AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
           AND T.CATEGORY = '´óÏµÁÐ ';
      END IF;

      --ÖÐÏµÁÐ--
      SELECT COUNT(*)
        INTO V_MSORT_FLAG
        FROM CUX_ITEM_CATEGORY T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
         AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
         AND T.CATEGORY = 'ÖÐÏµÁÐ';

      IF (V_MSORT_FLAG = 0) THEN
        INSERT INTO CUX_ITEM_CATEGORY
        VALUES
          (V_ITEM_CATEGORY.ITEM_ID,
           V_ITEM_CATEGORY.ORG_ID,
           'ÖÐÏµÁÐ',
           V_ITEM_CATEGORY.MSORT,
           SYSDATE);
      ELSE
        UPDATE CUX_ITEM_CATEGORY T
           SET T.CATEGORY_INFO    = V_ITEM_CATEGORY.MSORT,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
           AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
           AND T.CATEGORY = 'ÖÐÏµÁÐ';
      END IF;

      --Ð¡ÏµÁÐ--
      SELECT COUNT(*)
        INTO V_SSORT_FLAG
        FROM CUX_ITEM_CATEGORY T
       WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
         AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
         AND T.CATEGORY = 'Ð¡ÏµÁÐ';

      IF (V_SSORT_FLAG = 0) THEN
        INSERT INTO CUX_ITEM_CATEGORY
        VALUES
          (V_ITEM_CATEGORY.ITEM_ID,
           V_ITEM_CATEGORY.ORG_ID,
           'Ð¡ÏµÁÐ',
           V_ITEM_CATEGORY.SSORT,
           SYSDATE);
      ELSE
        UPDATE CUX_ITEM_CATEGORY T
           SET T.CATEGORY_INFO    = V_ITEM_CATEGORY.SSORT,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.INVENTORY_ITEM_ID = V_ITEM_CATEGORY.ITEM_ID
           AND T.ORG_ID = V_ITEM_CATEGORY.ORG_ID
           AND T.CATEGORY = 'Ð¡ÏµÁÐ';
      END IF;
      COMMIT;
    END LOOP;
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_ITEM_CATEGORY',
         SYSDATE,
         '');
      COMMIT;

END UPDATE_ITEM_CATEGORY;

----------------------------package-------------------------------------
/* ¸üÐÂÊý¾Ý»º³åÇøµÄ°ü×°ÂÊ */
PROCEDURE UPDATE_PACKAGE IS
    V_FLAG      NUMBER;
    V_LOG_ID    NUMBER;
    V_LAST_DATE DATE;
    V_ERP_DATE DATE;
    V_PKID      VARCHAR2(10);
    MY_SQLERRM VARCHAR2(80);
    CURSOR C_PACKAGE IS
      SELECT FFV.FLEX_VALUE PNO, --°ü×°ºÅ
             'Ïä' UNIT1, --Ïäµ¥Î»
             CISM.FLEX_VALUE_NAME SIZERUN, --³ßÂë
             MUOM.UNIT_OF_MEASURE UNIT2, --Ð¡µ¥Î»
             CISM.QUANTITY QUANTITY --ÊýÁ¿
        FROM CINV_ITEM_SIZE_MAP@ERP      CISM,
             FND_FLEX_VALUES@ERP         FFV,
             MTL_UNITS_OF_MEASURE_VL@ERP MUOM
       WHERE CISM.MASTER_FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
         AND MUOM.UOM_CODE = CISM.UNIT_CODE
         AND FFV.LAST_UPDATE_DATE >= V_LAST_DATE;

BEGIN
    V_LAST_DATE := GETLASTDATE('CUX_PACKAGE');
    SELECT CUX_UPDATE_LOG_S.NEXTVAL INTO V_LOG_ID FROM DUAL;
    SELECT SYSDATE INTO V_ERP_DATE FROM DUAL@ERP;
    --¶Ô¸üÐÂÈÕÖ¾ÏÈ²åÈëµ±Ç°ERP¸üÐÂÊ±¼ä
    INSERT INTO CUX_UPDATE_LOG
    VALUES
      (V_LOG_ID, 'CUX_PACKAGE', -1, NULL, V_ERP_DATE);
    --²éÕÒÓÐÎÞ¼ÇÂ¼
    FOR V_PACKAGE IN C_PACKAGE LOOP
      V_PKID := V_PACKAGE.PNO;
      SELECT COUNT(*)
        INTO V_FLAG
        FROM CUX_PACKAGE T
       WHERE T.PNO = V_PACKAGE.PNO
         AND T.SIZERUN = V_PACKAGE.SIZERUN;

      --²»´æÔÚÔò²åÈë
      IF (V_FLAG = 0) THEN
        INSERT INTO CUX_PACKAGE
        VALUES
          (V_PACKAGE.PNO,
           V_PACKAGE.UNIT1,
           V_PACKAGE.SIZERUN,
           V_PACKAGE.UNIT2,
           V_PACKAGE.QUANTITY,
           SYSDATE);
        COMMIT;
        --´æÔÚÔò¸üÐÂ£¬Ê¹Óëµ±Ç°ÏµÍ³Ê±¼äÍ¬²½
      ELSE
        UPDATE CUX_PACKAGE T
           SET T.UNIT1            = V_PACKAGE.UNIT1,
               T.SIZERUN          = V_PACKAGE.SIZERUN,
               T.UNIT2            = V_PACKAGE.UNIT2,
               T.QUANTITY         = V_PACKAGE.QUANTITY,
               T.LAST_UPDATE_DATE = SYSDATE
         WHERE T.PNO = V_PACKAGE.PNO
           AND T.SIZERUN = V_PACKAGE.SIZERUN;
        COMMIT;
      END IF;
    END LOOP;
    --Íê³É£¬ÉèÖÃ±êÖ¾Î»Îª'S'
    UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      --Ê§°Ü£¬±êÖ¾Î»Îª'F'
      UPDATE CUX_UPDATE_LOG SET FLAG = 'F' WHERE LOG_ID = V_LOG_ID;
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 80);
      INSERT INTO CUX_ERROR_MESSAGE
      VALUES
        (CUX_ERROR_MESSAGE_S.NEXTVAL,
         MY_SQLERRM,
         'CUX_PACKAGE',
         SYSDATE,
         TO_CHAR(V_PKID));
      COMMIT;

END UPDATE_PACKAGE;

END CUX_UPDATE_BASE_INFO;

posted on 2006-07-14 14:03 Kimi 阅读(350) 评论(0) 编辑收藏所属分类: Database

新用户注册刷新评论列表


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻知识库 C++博客博问管理
相关文章: oracle 基本连接操作图示（转） ORACLE 10G ODBC连接步骤和含义（转） MySQL 基本操作－－－丢了很久，今天又捡起来了，呵呵。玩具性的数据库，不过做点小程序还是蛮好的！ DB40的管理工具object manager1.8的基本使用 (转自MatriX) 比较 SQL SERVER与ORACLE（转） CHEK_POS_SALE 2个INIT和UPDATE ORACLE库的Package 使用rownum控制查询到的结果具体的一个引入JAR入ORACLE的语句一个简单的oracle pl/sql 调用java的实现，目标：从SYBASE数据库里向ERP数据库（Oracle）实现数据流通

Kimi's NutShell

随笔分类(139)

相册

友情链接

积分与排名

最新随笔

最新评论

阅读排行榜

评论排行榜