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