1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
|
CREATE OR REPLACE TRIGGER RH.test_ITF_OPSF_STRUC
-- ------------------------------
-- XXtest_MOD_D Version 115.7.04
-- AFTER INSERT OR UPDATE OF COD_COLL, COD_STRUC, LIB_STRUC ON STRUCTURES
AFTER INSERT OR UPDATE OF COD_COLL, COD_STRUC, LIB_STRUC ON RH.test_STRUC_HIS -- XXtest_MOD_D Version 115.7.04
-- ------------------------------
FOR EACH ROW
WHEN (
NEW.IND_VALID = 'O'
)
DECLARE
FLEX_VALUE_SET_ID_A FND_FLEX_VALUES.FLEX_VALUE_SET_ID%TYPE;
USER_ID_A FND_USER.USER_ID%TYPE;
FLEX_VALUE_ID_A FND_FLEX_VALUES.FLEX_VALUE_ID%TYPE;
BIDON VARCHAR2(1);
BEGIN
--
-- Insertion de la structure dans le jeu de valeur
-- test_SERVICE_PROJET (segment3 de la clé)
--
SELECT FLEX_VALUE_SET_ID
INTO FLEX_VALUE_SET_ID_A
FROM FND_FLEX_VALUE_SETS
-- -------------------------------
-- XXtest_MOD_D Version 115.7.04
WHERE FLEX_VALUE_SET_NAME = 'test_NCBC_STRUCTURE';
-- XXtest_MOD_F Version 115.7.04
-- -------------------------------
SELECT USER_ID
INTO USER_ID_A
FROM FND_USER
WHERE USER_NAME = 'SYSADMIN';
BEGIN
SELECT FLEX_VALUE_ID
INTO FLEX_VALUE_ID_A
FROM FND_FLEX_VALUES
WHERE FLEX_VALUE_SET_ID = FLEX_VALUE_SET_ID_A
-- -------------------------------
-- XXtest_MOD_D Version 115.7.04
--AND FLEX_VALUE = substr(:NEW.COD_STRUC,1,5);
AND FLEX_VALUE = :NEW.COD_STRUC;
-- XXtest_MOD_F Version 115.7.04
-- -------------------------------
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
..........
IF SQL%NOTFOUND THEN
INSERT INTO FND_FLEX_VALUES_TL(FLEX_VALUE_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
DESCRIPTION, FLEX_VALUE_MEANING, LANGUAGE, SOURCE_LANG)
VALUES (FLEX_VALUE_ID_A, SYSDATE, USER_ID_A, SYSDATE, USER_ID_A, :NEW.LIB_STRUC, :NEW.COD_STRUC, 'F', 'F');
END IF;
UPDATE FND_FLEX_VALUES_TL
SET
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = USER_ID_A,
DESCRIPTION = :NEW.LIB_STRUC
WHERE FLEX_VALUE_ID = FLEX_VALUE_ID_A
-- -------------------------------
-- XXtest_MOD_D Version 115.7.04
-- AND FLEX_VALUE_MEANING = substr(:NEW.COD_STRUC,1,5)
AND FLEX_VALUE_MEANING = :NEW.COD_STRUC
-- XXtest_MOD_F Version 115.7.04
-- -------------------------------
AND LANGUAGE = 'US';
IF SQL%NOTFOUND THEN
INSERT INTO FND_FLEX_VALUES_TL
(FLEX_VALUE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
DESCRIPTION,
FLEX_VALUE_MEANING,
LANGUAGE,
SOURCE_LANG)
VALUES (FLEX_VALUE_ID_A,
SYSDATE,
USER_ID_A,
SYSDATE,
USER_ID_A,
:NEW.LIB_STRUC,
-- -------------------------------
-- XXtest_MOD_D Version 115.7.04
--substr(:NEW.COD_STRUC,1,5),
:NEW.COD_STRUC,
-- XXtest_MOD_F Version 115.7.04
-- -------------------------------
'US',
'F');
END IF;
END IF;
-- -------------------------------
-- XXtest_MEC_D Version 115.7.04
-- Le segment12 de la CCF n'est plus alimenté par le segment3
-- Le code a été enlevé dans cette version
-- XXtest_MEC_F Version 115.7.04
-- -------------------------------
END; |
Partager