[Home] [Help]
PACKAGE BODY: APPS.ASO_QUOTE_RELATED_OBJECTS_PKG
Source
1 PACKAGE BODY ASO_QUOTE_RELATED_OBJECTS_PKG as
2 /* $Header: asotobjb.pls 120.1 2005/06/29 12:39:22 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_QUOTE_RELATED_OBJECTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_QUOTE_RELATED_OBJECTS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asotobjb.pls';
13
14 PROCEDURE Insert_Row(
15 px_RELATED_OBJECT_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
16 p_CREATION_DATE DATE,
17 p_CREATED_BY NUMBER,
18 p_LAST_UPDATE_DATE DATE,
19 p_LAST_UPDATED_BY NUMBER,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_REQUEST_ID NUMBER,
22 p_PROGRAM_APPLICATION_ID NUMBER,
23 p_PROGRAM_ID NUMBER,
24 p_PROGRAM_UPDATE_DATE DATE,
25 p_QUOTE_OBJECT_TYPE_CODE VARCHAR2,
26 p_QUOTE_OBJECT_ID NUMBER,
27 p_OBJECT_TYPE_CODE VARCHAR2,
28 p_OBJECT_ID NUMBER,
29 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
30 p_RECIPROCAL_FLAG VARCHAR2,
31 p_OBJECT_VERSION_NUMBER NUMBER
32 )
33 -- p_QUOTE_OBJECT_CODE NUMBER
34
35 IS
36 CURSOR C2 IS SELECT ASO_QUOTE_RELATED_OBJECTS_S.nextval FROM sys.dual;
37 BEGIN
38 If (px_RELATED_OBJECT_ID IS NULL) OR (px_RELATED_OBJECT_ID = FND_API.G_MISS_NUM) then
39 OPEN C2;
40 FETCH C2 INTO px_RELATED_OBJECT_ID;
41 CLOSE C2;
42 End If;
43 INSERT INTO ASO_QUOTE_RELATED_OBJECTS(
44 RELATED_OBJECT_ID,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_LOGIN,
50 REQUEST_ID,
51 PROGRAM_APPLICATION_ID,
52 PROGRAM_ID,
53 PROGRAM_UPDATE_DATE,
54 QUOTE_OBJECT_TYPE_CODE,
55 QUOTE_OBJECT_ID,
56 OBJECT_TYPE_CODE,
57 OBJECT_ID,
58 RELATIONSHIP_TYPE_CODE,
59 RECIPROCAL_FLAG,
60 OBJECT_VERSION_NUMBER
61 ) VALUES (
62 px_RELATED_OBJECT_ID,
63 ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
64 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
65 ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
66 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
67 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
68 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
69 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
70 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
71 ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_PROGRAM_UPDATE_DATE),
72 decode( p_QUOTE_OBJECT_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_QUOTE_OBJECT_TYPE_CODE),
73 decode( p_QUOTE_OBJECT_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_OBJECT_ID),
74 decode( p_OBJECT_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_OBJECT_TYPE_CODE),
75 decode( p_OBJECT_ID, FND_API.G_MISS_NUM, NULL, p_OBJECT_ID),
76 decode( p_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_RELATIONSHIP_TYPE_CODE),
77 decode( p_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, NULL, p_RECIPROCAL_FLAG),
78 decode ( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,1,NULL,1, p_OBJECT_VERSION_NUMBER));
79 -- decode( p_QUOTE_OBJECT_CODE, FND_API.G_MISS_NUM, NULL, p_QUOTE_OBJECT_CODE));
80 End Insert_Row;
81
82 PROCEDURE Update_Row(
83 p_RELATED_OBJECT_ID NUMBER,
84 p_CREATION_DATE DATE,
85 p_CREATED_BY NUMBER,
86 p_LAST_UPDATE_DATE DATE,
87 p_LAST_UPDATED_BY NUMBER,
88 p_LAST_UPDATE_LOGIN NUMBER,
89 p_REQUEST_ID NUMBER,
90 p_PROGRAM_APPLICATION_ID NUMBER,
91 p_PROGRAM_ID NUMBER,
92 p_PROGRAM_UPDATE_DATE DATE,
93 p_QUOTE_OBJECT_TYPE_CODE VARCHAR2,
94 p_QUOTE_OBJECT_ID NUMBER,
95 p_OBJECT_TYPE_CODE VARCHAR2,
96 p_OBJECT_ID NUMBER,
97 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
98 p_RECIPROCAL_FLAG VARCHAR2,
99 p_OBJECT_VERSION_NUMBER NUMBER
100 )
101 -- p_QUOTE_OBJECT_CODE NUMBER)
102
103 IS
104 BEGIN
105 Update ASO_QUOTE_RELATED_OBJECTS
106 SET
107 CREATION_DATE = ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
108 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
109 LAST_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
110 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
111 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
112 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
113 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
114 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
115 PROGRAM_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
116 QUOTE_OBJECT_TYPE_CODE = decode( p_QUOTE_OBJECT_TYPE_CODE, FND_API.G_MISS_CHAR, QUOTE_OBJECT_TYPE_CODE, p_QUOTE_OBJECT_TYPE_CODE),
117 QUOTE_OBJECT_ID = decode( p_QUOTE_OBJECT_ID, FND_API.G_MISS_NUM, QUOTE_OBJECT_ID, p_QUOTE_OBJECT_ID),
118 OBJECT_TYPE_CODE = decode( p_OBJECT_TYPE_CODE, FND_API.G_MISS_CHAR, OBJECT_TYPE_CODE, p_OBJECT_TYPE_CODE),
119 OBJECT_ID = decode( p_OBJECT_ID, FND_API.G_MISS_NUM, OBJECT_ID, p_OBJECT_ID),
120 RELATIONSHIP_TYPE_CODE = decode( p_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, RELATIONSHIP_TYPE_CODE, p_RELATIONSHIP_TYPE_CODE),
121 RECIPROCAL_FLAG = decode( p_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, RECIPROCAL_FLAG, p_RECIPROCAL_FLAG),
122 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, nvl(OBJECT_VERSION_NUMBER,0)+1, nvl(p_OBJECT_VERSION_NUMBER, nvl(OBJECT_VERSION_NUMBER,0))+1)
123 where RELATED_OBJECT_ID = p_RELATED_OBJECT_ID;
124
125 If (SQL%NOTFOUND) then
126 RAISE NO_DATA_FOUND;
127 End If;
128 END Update_Row;
129
130 PROCEDURE Delete_Row(
131 p_RELATED_OBJECT_ID NUMBER)
132 IS
133 BEGIN
134 DELETE FROM ASO_QUOTE_RELATED_OBJECTS
135 WHERE RELATED_OBJECT_ID = p_RELATED_OBJECT_ID;
136 If (SQL%NOTFOUND) then
137 RAISE NO_DATA_FOUND;
138 End If;
139 END Delete_Row;
140
141 PROCEDURE Lock_Row(
142 --p_OBJECT_VERSION_NUMBER NUMBER,
143 p_RELATED_OBJECT_ID NUMBER,
144 p_CREATION_DATE DATE,
145 p_CREATED_BY NUMBER,
146 p_LAST_UPDATE_DATE DATE,
147 p_LAST_UPDATED_BY NUMBER,
148 p_LAST_UPDATE_LOGIN NUMBER,
149 p_REQUEST_ID NUMBER,
150 p_PROGRAM_APPLICATION_ID NUMBER,
151 p_PROGRAM_ID NUMBER,
152 p_PROGRAM_UPDATE_DATE DATE,
153 p_QUOTE_OBJECT_TYPE_CODE VARCHAR2,
154 p_QUOTE_OBJECT_ID NUMBER,
155 p_OBJECT_TYPE_CODE VARCHAR2,
156 p_OBJECT_ID NUMBER,
157 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
158 p_RECIPROCAL_FLAG VARCHAR2)
159 -- p_QUOTE_OBJECT_CODE NUMBER)
160
161 IS
162 CURSOR C IS
163 SELECT RELATED_OBJECT_ID,
164 --OBJECT_VERSION_NUMBER,
165 CREATION_DATE,
166 CREATED_BY,
167 LAST_UPDATE_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_LOGIN,
170 REQUEST_ID,
171 PROGRAM_APPLICATION_ID,
172 PROGRAM_ID,
173 PROGRAM_UPDATE_DATE,
174 QUOTE_OBJECT_TYPE_CODE,
175 QUOTE_OBJECT_ID,
176 OBJECT_TYPE_CODE,
177 OBJECT_ID,
178 RELATIONSHIP_TYPE_CODE,
179 RECIPROCAL_FLAG
180 FROM ASO_QUOTE_RELATED_OBJECTS
181 WHERE RELATED_OBJECT_ID = p_RELATED_OBJECT_ID
182 FOR UPDATE of RELATED_OBJECT_ID NOWAIT;
183 Recinfo C%ROWTYPE;
184 BEGIN
185 OPEN C;
186 FETCH C INTO Recinfo;
187 If (C%NOTFOUND) then
188 CLOSE C;
189 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
190 APP_EXCEPTION.RAISE_EXCEPTION;
191 End If;
192 CLOSE C;
193 if (
194 /*
195 ( Recinfo.RELATED_OBJECT_ID = p_RELATED_OBJECT_ID)
196 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
197 OR ( ( Recinfo.CREATION_DATE IS NULL )
198 AND ( p_CREATION_DATE IS NULL )))
199 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
200 OR ( ( Recinfo.CREATED_BY IS NULL )
201 AND ( p_CREATED_BY IS NULL )))
202 AND
203 */
204 ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
205 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
206 AND ( p_LAST_UPDATE_DATE IS NULL )))
207 /*
208 AND
209 ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
210 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
211 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
212 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
213 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
214 AND ( p_LAST_UPDATED_BY IS NULL )))
215 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
216 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
217 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
218 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
219 OR ( ( Recinfo.REQUEST_ID IS NULL )
220 AND ( p_REQUEST_ID IS NULL )))
221 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
222 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
223 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
224 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
225 OR ( ( Recinfo.PROGRAM_ID IS NULL )
226 AND ( p_PROGRAM_ID IS NULL )))
227 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
228 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
229 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
230 AND ( ( Recinfo.QUOTE_OBJECT_TYPE_CODE = p_QUOTE_OBJECT_TYPE_CODE)
231 OR ( ( Recinfo.QUOTE_OBJECT_TYPE_CODE IS NULL )
232 AND ( p_QUOTE_OBJECT_TYPE_CODE IS NULL )))
233 AND ( ( Recinfo.QUOTE_OBJECT_ID = p_QUOTE_OBJECT_ID)
234 OR ( ( Recinfo.QUOTE_OBJECT_ID IS NULL )
235 AND ( p_QUOTE_OBJECT_ID IS NULL )))
236 AND ( ( Recinfo.OBJECT_TYPE_CODE = p_OBJECT_TYPE_CODE)
237 OR ( ( Recinfo.OBJECT_TYPE_CODE IS NULL )
238 AND ( p_OBJECT_TYPE_CODE IS NULL )))
239 AND ( ( Recinfo.OBJECT_ID = p_OBJECT_ID)
240 OR ( ( Recinfo.OBJECT_ID IS NULL )
241 AND ( p_OBJECT_ID IS NULL )))
242 AND ( ( Recinfo.RELATIONSHIP_TYPE_CODE = p_RELATIONSHIP_TYPE_CODE)
243 OR ( ( Recinfo.RELATIONSHIP_TYPE_CODE IS NULL )
244 AND ( p_RELATIONSHIP_TYPE_CODE IS NULL )))
245 AND ( ( Recinfo.RECIPROCAL_FLAG = p_RECIPROCAL_FLAG)
246 OR ( ( Recinfo.RECIPROCAL_FLAG IS NULL )
247 AND ( p_RECIPROCAL_FLAG IS NULL )))
248 */
249 /* AND ( ( Recinfo.QUOTE_OBJECT_CODE = p_QUOTE_OBJECT_CODE)
250 OR ( ( Recinfo.QUOTE_OBJECT_CODE IS NULL )
251 AND ( p_QUOTE_OBJECT_CODE IS NULL )))*/
252 ) then
253 return;
254 else
255 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
256 APP_EXCEPTION.RAISE_EXCEPTION;
257 End If;
258 END Lock_Row;
259
260 End ASO_QUOTE_RELATED_OBJECTS_PKG;