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;
? --3?ê??ˉITEM,???ùóDERP ITEMD??￠μ?è?--
? 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, ' '), --??3?
????????????????????? 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;
??? --íê3é￡?éè??±ê?????a'S'
??? UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
??? COMMIT;
? EXCEPTION
??? WHEN OTHERS THEN
????? ROLLBACK;
????? --꧰ü￡?±ê?????a'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;
? --3?ê??ˉ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, '?Dàà', 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, 'D?àà', 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, '′ó?μáD', 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, '?D?μáD', 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, 'D??μáD', 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 ′ó?üD?è??????tà???è?×??üμ??üD?è??ú
? p_update_moduleê??üD?μ?update ModuleààDí.
??????? */
? 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-------------------------------------*/
? /*
? ?üD?êy?Y???????úcux_customerμ?êy?Y
? */
? 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;
??? --???üD?è????è2?è?μ±?°ERP?üD?ê±??
??? INSERT INTO CUX_UPDATE_LOG
??? VALUES
????? (V_LOG_ID, 'CUX_CUSTOMER', -1, NULL, V_ERP_DATE);
??? --2é?òóD?T????
??? 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;
????? --2?′??ú?ò2?è?
????? 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);
??????? --′??ú?ò?üD?￡?ê1ó?μ±?°?μí3ê±??í?2?
????? 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;
??? --íê3é￡?éè??±ê?????a'S'
??? UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
??? COMMIT;
?
? EXCEPTION
??? WHEN OTHERS THEN
????? ROLLBACK;
????? --꧰ü￡?±ê?????a'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---------------------------------------------*/
? /*?üD??o3????úμ?1?ó|éì?à1?êy?Y*/
? 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, --1?ó|éì±ào?
????????????????????? NVL(PV.VENDOR_NAME, ' ') VENDOR_NAME --??3?
??????? 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;
??? --???üD?è????è2?è?μ±?°ERP?üD?ê±??
??? INSERT INTO CUX_UPDATE_LOG
??? VALUES
????? (V_LOG_ID, 'CUX_VENDOR', -1, NULL, V_ERP_DATE);
??? --2é?òóD?T????
??? 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;
????? --2?′??ú?ò2?è?
????? IF (V_FLAG = 0) THEN
??????? INSERT INTO CUX_VENDOR
??????? VALUES
????????? (V_VENDOR.VENDOR_ID,
?????????? V_VENDOR.VENDOR_NUMBER,
?????????? V_VENDOR.VENDOR_NAME,
?????????? SYSDATE);
??????? --′??ú?ò?üD?￡?ê1ó?μ±?°?μí3ê±??í?2?
????? 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;
??? --íê3é￡?éè??±ê?????a'S'
??? UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
??? COMMIT;
?
? EXCEPTION
??? WHEN OTHERS THEN
????? ROLLBACK;
????? --꧰ü￡?±ê?????a'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 --?o?eè?
??????? 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---------------------------------------------*/
? /*?üD??o3????úμ???á?êy?Y*/
? 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;
??? --???üD?è????è2?è?μ±?°ERP?üD?ê±??
??? INSERT INTO CUX_UPDATE_LOG
??? VALUES
????? (V_LOG_ID, 'CUX_ITEM', -1, NULL, V_ERP_DATE);
??? --2é?òóD?T????
??? 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;
????? --2?′??ú?ò2?è?
????? 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;
??????? --′??ú?ò?üD?￡?ê1ó?μ±?°?μí3ê±??í?2?
????? 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;
??? --íê3é￡?éè??±ê?????a'S'
??? UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
??? COMMIT;
?
? EXCEPTION
??? WHEN OTHERS THEN
????? ROLLBACK;
????? --꧰ü￡?±ê?????a'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_2ú?·àà±e') 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_2ú?·àà±e') MKIND, --?Dàà
????????????????????? (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_2ú?·àà±e') SKIND, --D?àà
????????????????????? (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_2ú?·?μáD') BSORT, --′ó?μáD
????????????????????? (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_2ú?·?μáD') MSORT, --?D?μáD
????????????????????? ' ' SSORT --D??μáD
??????? 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;
???
????? --?Dàà--
????? 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 = '?Dàà';
???
????? IF (V_MKIND_FLAG = 0) THEN
??????? INSERT INTO CUX_ITEM_CATEGORY
??????? VALUES
????????? (V_ITEM_CATEGORY.ITEM_ID,
?????????? V_ITEM_CATEGORY.ORG_ID,
?????????? '?Dàà',
?????????? 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 = '?Dàà';
????? END IF;
???
????? --D?àà--
????? 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 = 'D?àà';
???
????? IF (V_SKIND_FLAG = 0) THEN
??????? INSERT INTO CUX_ITEM_CATEGORY
??????? VALUES
????????? (V_ITEM_CATEGORY.ITEM_ID,
?????????? V_ITEM_CATEGORY.ORG_ID,
?????????? 'D?àà',
?????????? 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 = 'D?àà';
????? END IF;
???
????? --′ó?μáD--
????? 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 = '′ó?μáD';
???
????? IF (V_BSORT_FLAG = 0) THEN
??????? INSERT INTO CUX_ITEM_CATEGORY
??????? VALUES
????????? (V_ITEM_CATEGORY.ITEM_ID,
?????????? V_ITEM_CATEGORY.ORG_ID,
?????????? '′ó?μáD',
?????????? 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 = '′ó?μáD ';
????? END IF;
???
????? --?D?μáD--
????? 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 = '?D?μáD';
???
????? IF (V_MSORT_FLAG = 0) THEN
??????? INSERT INTO CUX_ITEM_CATEGORY
??????? VALUES
????????? (V_ITEM_CATEGORY.ITEM_ID,
?????????? V_ITEM_CATEGORY.ORG_ID,
?????????? '?D?μáD',
?????????? 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 = '?D?μáD';
????? END IF;
???
????? --D??μáD--
????? 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 = 'D??μáD';
???
????? IF (V_SSORT_FLAG = 0) THEN
??????? INSERT INTO CUX_ITEM_CATEGORY
??????? VALUES
????????? (V_ITEM_CATEGORY.ITEM_ID,
?????????? V_ITEM_CATEGORY.ORG_ID,
?????????? 'D??μáD',
?????????? 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 = 'D??μáD';
????? 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-------------------------------------
? /* ?üD?êy?Y?o3???μ?°ü×°?ê */
? 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, --°ü×°o?
???????????? '??' UNIT1, --??μ￥??
???????????? CISM.FLEX_VALUE_NAME SIZERUN, --3???
???????????? MUOM.UNIT_OF_MEASURE UNIT2, --D?μ￥??
???????????? CISM.QUANTITY QUANTITY --êyá?
??????? 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;
??? --???üD?è????è2?è?μ±?°ERP?üD?ê±??
??? INSERT INTO CUX_UPDATE_LOG
??? VALUES
????? (V_LOG_ID, 'CUX_PACKAGE', -1, NULL, V_ERP_DATE);
??? --2é?òóD?T????
??? 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;
???
????? --2?′??ú?ò2?è?
????? 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;
??????? --′??ú?ò?üD?￡?ê1ó?μ±?°?μí3ê±??í?2?
????? 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;
??? --íê3é￡?éè??±ê?????a'S'
??? UPDATE CUX_UPDATE_LOG SET FLAG = 'S' WHERE LOG_ID = V_LOG_ID;
??? COMMIT;
?
? EXCEPTION
??? WHEN OTHERS THEN
????? ROLLBACK;
????? --꧰ü￡?±ê?????a'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;