[Home] [Help]
PACKAGE BODY: APPS.HZ_SECONDARY_TRANSFORM_PKG
Source
1 PACKAGE BODY HZ_SECONDARY_TRANSFORM_PKG AS
2 /*$Header: ARHDQSTB.pls 120.4 2005/10/30 04:19:22 appldev noship $ */
3
4
5 PROCEDURE Insert_Row(
6 px_SECONDARY_TRANSFORM_ID IN OUT NOCOPY NUMBER,
7 p_SECONDARY_ATTRIBUTE_ID NUMBER,
8 p_FUNCTION_ID NUMBER,
9 p_TRANSFORMATION_WEIGHT NUMBER,
10 p_SIMILARITY_CUTOFF NUMBER,
11 p_ACTIVE_FLAG VARCHAR2,
12 p_USE_EXACT VARCHAR2,
13 p_CREATED_BY NUMBER,
14 p_CREATION_DATE DATE,
15 p_LAST_UPDATE_LOGIN NUMBER,
16 p_LAST_UPDATE_DATE DATE,
17 p_LAST_UPDATED_BY NUMBER,
18 p_OBJECT_VERSION_NUMBER NUMBER)
19 IS
20 CURSOR C2 IS SELECT HZ_SECONDARY_TRANS_S.nextval FROM sys.dual;
21 l_success VARCHAR2(1) := 'N';
22 BEGIN
23 WHILE l_success = 'N' LOOP
24 BEGIN
25 IF ( px_SECONDARY_TRANSFORM_ID IS NULL) OR (px_SECONDARY_TRANSFORM_ID = FND_API.G_MISS_NUM) THEN
26 OPEN C2;
27 FETCH C2 INTO px_SECONDARY_TRANSFORM_ID;
28 CLOSE C2;
29 END IF;
30
31 INSERT INTO HZ_SECONDARY_TRANS(
32 SECONDARY_TRANSFORM_ID,
33 SECONDARY_ATTRIBUTE_ID,
34 FUNCTION_ID,
35 TRANSFORMATION_WEIGHT ,
36 SIMILARITY_CUTOFF ,
37 ACTIVE_FLAG,
38 USE_EXACT,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATE_LOGIN,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 OBJECT_VERSION_NUMBER
45 )
46 VALUES (
47 px_SECONDARY_TRANSFORM_ID,
48 decode(p_SECONDARY_ATTRIBUTE_ID , FND_API.G_MISS_NUM , NULL, p_SECONDARY_ATTRIBUTE_ID),
49 decode(p_FUNCTION_ID, FND_API.G_MISS_NUM , NULL, p_FUNCTION_ID),
50 decode(p_TRANSFORMATION_WEIGHT, FND_API.G_MISS_NUM , NULL, p_TRANSFORMATION_WEIGHT),
51 decode(p_SIMILARITY_CUTOFF, FND_API.G_MISS_NUM , NULL, p_SIMILARITY_CUTOFF),
52 decode(p_ACTIVE_FLAG, FND_API.G_MISS_CHAR ,NULL, p_ACTIVE_FLAG),
53 decode(p_USE_EXACT, FND_API.G_MISS_CHAR ,NULL, p_USE_EXACT),
54 decode(p_created_by, FND_API.G_MISS_NUM , NULL, p_created_by),
55 decode(p_creation_date, FND_API.G_MISS_DATE ,to_date(NULL), p_creation_date),
56 decode(p_last_update_login, FND_API.G_MISS_NUM , NULL, p_last_update_login),
57 decode(p_last_update_date, FND_API.G_MISS_DATE ,to_date(NULL), p_last_update_date),
58 decode(p_last_updated_by, FND_API.G_MISS_NUM , NULL, p_last_updated_by),
59 1
60 );
61
62 l_success := 'Y';
63 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
64 IF INSTRB( SQLERRM, 'HZ_PRIMARY_TRANS_U1' ) <> 0 THEN
65 DECLARE
66 l_count NUMBER;
67 l_dummy VARCHAR2(1);
68 BEGIN
69 l_count := 1;
70 WHILE l_count > 0 LOOP
71 SELECT HZ_MATCH_RULE_SECONDARY_s.nextval
72 into px_SECONDARY_TRANSFORM_ID FROM sys.dual;
73 BEGIN
74 SELECT 'Y' INTO l_dummy
75 FROM HZ_MATCH_RULE_SECONDARY
76 WHERE SECONDARY_ATTRIBUTE_ID = px_SECONDARY_TRANSFORM_ID ;
77 l_count := 1;
78 EXCEPTION WHEN NO_DATA_FOUND THEN
79 l_count := 0;
80 END;
81 END LOOP;
82 END;
83 END IF;
84 END;
85 END LOOP;
86 End Insert_Row;
87
88 PROCEDURE Update_Row(
89 p_SECONDARY_TRANSFORM_ID NUMBER,
90 p_SECONDARY_ATTRIBUTE_ID NUMBER,
91 p_FUNCTION_ID NUMBER,
92 p_TRANSFORMATION_WEIGHT NUMBER,
93 p_SIMILARITY_CUTOFF NUMBER,
94 p_ACTIVE_FLAG VARCHAR2,
95 p_USE_EXACT VARCHAR2,
96 p_CREATED_BY NUMBER,
97 p_CREATION_DATE DATE,
98 p_LAST_UPDATE_LOGIN NUMBER,
99 p_LAST_UPDATE_DATE DATE,
100 p_LAST_UPDATED_BY NUMBER,
101 p_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER
102 ) IS
103 l_object_version_number number;
104
105 BEGIN
106
107 l_object_version_number := NVL(p_object_version_number, 1) + 1;
108
109 UPDATE hz_SECONDARY_trans
110 SET
111 SECONDARY_ATTRIBUTE_ID = decode(p_SECONDARY_ATTRIBUTE_ID ,FND_API.G_MISS_NUM,SECONDARY_ATTRIBUTE_ID,
112 p_SECONDARY_ATTRIBUTE_ID),
113 FUNCTION_ID = decode(p_FUNCTION_ID ,FND_API.G_MISS_NUM,FUNCTION_ID,
114 p_FUNCTION_ID),
115 TRANSFORMATION_WEIGHT = decode(p_TRANSFORMATION_WEIGHT ,FND_API.G_MISS_NUM,TRANSFORMATION_WEIGHT,
116 p_TRANSFORMATION_WEIGHT),
117 SIMILARITY_CUTOFF = decode(p_SIMILARITY_CUTOFF ,FND_API.G_MISS_NUM,SIMILARITY_CUTOFF,
118 p_SIMILARITY_CUTOFF),
119 ACTIVE_FLAG = decode(p_ACTIVE_FLAG ,FND_API.G_MISS_CHAR,ACTIVE_FLAG,
120 p_ACTIVE_FLAG),
121 USE_EXACT = decode(p_USE_EXACT ,FND_API.G_MISS_CHAR,USE_EXACT,
122 p_USE_EXACT),
123 -- Bug 3032780
124 /*
125 CREATED_BY = decode(p_created_by ,FND_API.G_MISS_NUM,CREATED_BY,
126 p_created_by),
127 CREATION_DATE = decode(p_CREATION_DATE ,FND_API.G_MISS_DATE,CREATION_DATE,
128 p_CREATION_DATE),
129 */
130 LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN ,FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,
131 p_LAST_UPDATE_LOGIN),
132 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE ,FND_API.G_MISS_DATE,LAST_UPDATE_DATE,
133 p_LAST_UPDATE_DATE),
134 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY ,FND_API.G_MISS_NUM,LAST_UPDATED_BY,
135 p_LAST_UPDATED_BY),
136 OBJECT_VERSION_NUMBER = decode(l_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER,l_object_version_number)
137 WHERE SECONDARY_TRANSFORM_ID = p_SECONDARY_TRANSFORM_ID;
138
139 p_OBJECT_VERSION_NUMBER := l_OBJECT_VERSION_NUMBER;
140
141 If (SQL%NOTFOUND) then
142 RAISE NO_DATA_FOUND;
143 End If;
144 END Update_Row;
145
146 PROCEDURE Delete_Row(p_SECONDARY_TRANSFORM_ID NUMBER) IS
147 BEGIN
148 DELETE FROM HZ_SECONDARY_TRANS
149 WHERE SECONDARY_transform_id = p_SECONDARY_transform_id;
150
151 If (SQL%NOTFOUND) then
152 RAISE NO_DATA_FOUND;
153 End If;
154 END Delete_Row;
155
156 PROCEDURE Lock_Row(
157 p_SECONDARY_TRANSFORM_ID IN OUT NOCOPY NUMBER,
158 p_OBJECT_VERSION_NUMBER IN NUMBER
159 ) IS
160 CURSOR C IS
161
162 SELECT OBJECT_VERSION_NUMBER
163 FROM hz_SECONDARY_trans
164 WHERE SECONDARY_TRANSFORM_ID = p_SECONDARY_TRANSFORM_ID
165 FOR UPDATE OF SECONDARY_TRANSFORM_ID NOWAIT;
166 Recinfo C%ROWTYPE;
167 BEGIN
168 OPEN C;
169 FETCH C INTO Recinfo;
170 If (C%NOTFOUND) then
171 CLOSE C;
172 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
173 APP_EXCEPTION.RAISE_EXCEPTION;
174 End If;
175 CLOSE C;
176
177 if (
178 ( Recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND p_OBJECT_VERSION_NUMBER IS NOT NULL
179 AND Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER )
180 OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL)AND (p_OBJECT_VERSION_NUMBER IS NULL ))
181 ) then
182 return;
183 else
184 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 End If;
187 END Lock_Row;
188
189 END HZ_SECONDARY_TRANSFORM_PKG;