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