[Home] [Help]
PACKAGE BODY: APPS.AMS_RELATED_ITEMS_PKG
Source
1 PACKAGE BODY AMS_RELATED_ITEMS_PKG as
2 /* $Header: amstritb.pls 115.4 2002/11/11 22:05:43 abhola ship $ */
3 -- Start of Comments
4 -- Package name : AMS_RELATED_ITEMS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_RELATED_ITEMS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstritb.pls';
13
14 PROCEDURE Insert_Row(
15 p_INVENTORY_ITEM_ID NUMBER,
16 p_ORGANIZATION_ID NUMBER,
17 p_RELATED_ITEM_ID NUMBER,
18 p_RELATIONSHIP_TYPE_ID NUMBER,
19 p_RECIPROCAL_FLAG VARCHAR2,
20 p_LAST_UPDATE_DATE DATE,
21 p_LAST_UPDATED_BY NUMBER,
22 p_CREATION_DATE DATE,
23 p_CREATED_BY NUMBER,
24 p_LAST_UPDATE_LOGIN NUMBER,
25 p_REQUEST_ID NUMBER,
26 p_PROGRAM_APPLICATION_ID NUMBER,
27 p_PROGRAM_ID NUMBER,
28 p_PROGRAM_UPDATE_DATE DATE)
29
30 IS
31 X_ROWID VARCHAR2(30);
32
33 CURSOR C IS SELECT rowid FROM MTL_RELATED_ITEMS
34 WHERE RELATED_ITEM_ID = p_RELATED_ITEM_ID
35 AND ORGANIZATION_ID = p_ORGANIZATION_ID
36 AND INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
37 AND RELATIONSHIP_TYPE_ID = p_RELATIONSHIP_TYPE_ID;
38
39 BEGIN
40
41
42 INSERT INTO MTL_RELATED_ITEMS(
43 INVENTORY_ITEM_ID,
44 ORGANIZATION_ID,
45 RELATED_ITEM_ID,
46 RELATIONSHIP_TYPE_ID,
47 RECIPROCAL_FLAG,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_LOGIN,
53 REQUEST_ID,
54 PROGRAM_APPLICATION_ID,
55 PROGRAM_ID,
56 PROGRAM_UPDATE_DATE,
57 object_version_number
58 ) VALUES (
59 decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID),
60 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
61 decode( p_RELATED_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_RELATED_ITEM_ID),
62 decode( p_RELATIONSHIP_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_RELATIONSHIP_TYPE_ID),
63 decode( p_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, NULL, p_RECIPROCAL_FLAG),
64 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
65 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
66 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
67 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
68 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
69 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
70 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
71 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
72 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_PROGRAM_UPDATE_DATE),
73 1 );
74 OPEN C;
75 FETCH C INTO x_rowid;
76 If (C%NOTFOUND) then
77 CLOSE C;
78 RAISE NO_DATA_FOUND;
79 End If;
80 End Insert_Row;
81
82 PROCEDURE Update_Row(
83 p_INVENTORY_ITEM_ID NUMBER,
84 p_ORGANIZATION_ID NUMBER,
85 p_RELATED_ITEM_ID NUMBER,
86 p_RELATIONSHIP_TYPE_ID NUMBER,
87 p_RECIPROCAL_FLAG VARCHAR2,
88 p_LAST_UPDATE_DATE DATE,
89 p_LAST_UPDATED_BY NUMBER,
90 p_CREATION_DATE DATE,
91 p_CREATED_BY NUMBER,
92 p_LAST_UPDATE_LOGIN NUMBER,
93 p_REQUEST_ID NUMBER,
94 p_PROGRAM_APPLICATION_ID NUMBER,
95 p_PROGRAM_ID NUMBER,
96 p_PROGRAM_UPDATE_DATE DATE)
97
98 IS
99 BEGIN
100 Update MTL_RELATED_ITEMS
101 SET
102 INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID),
103 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
104 RELATED_ITEM_ID = decode( p_RELATED_ITEM_ID, FND_API.G_MISS_NUM, RELATED_ITEM_ID, p_RELATED_ITEM_ID),
105 RELATIONSHIP_TYPE_ID = decode( p_RELATIONSHIP_TYPE_ID, FND_API.G_MISS_NUM, RELATIONSHIP_TYPE_ID, p_RELATIONSHIP_TYPE_ID),
106 RECIPROCAL_FLAG = decode( p_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, RECIPROCAL_FLAG, p_RECIPROCAL_FLAG),
107 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
108 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
109 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
110 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_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 = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE)
116 where RELATED_ITEM_ID = p_RELATED_ITEM_ID
117 AND ORGANIZATION_ID = p_ORGANIZATION_ID
118 AND INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
119 AND RELATIONSHIP_TYPE_ID = p_RELATIONSHIP_TYPE_ID;
120
121 If (SQL%NOTFOUND) then
122 RAISE NO_DATA_FOUND;
123 End If;
124 END Update_Row;
125
126 PROCEDURE Delete_Row(
127 p_INVENTORY_ITEM_ID NUMBER,
128 p_ORGANIZATION_ID NUMBER,
129 p_RELATED_ITEM_ID NUMBER,
130 p_RELATIONSHIP_TYPE_ID NUMBER,
131 p_RECIPROCAL_FLAG VARCHAR2,
132 p_LAST_UPDATE_DATE DATE,
133 p_LAST_UPDATED_BY NUMBER,
134 p_CREATION_DATE DATE,
135 p_CREATED_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 IS
142 BEGIN
143 DELETE FROM MTL_RELATED_ITEMS
144 WHERE RELATED_ITEM_ID = p_RELATED_ITEM_ID
145 AND INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
146 AND ORGANIZATION_ID = p_ORGANIZATION_ID
147 AND RELATIONSHIP_TYPE_ID = p_RELATIONSHIP_TYPE_ID;
148
149 If (SQL%NOTFOUND) then
150 RAISE NO_DATA_FOUND;
151 End If;
152 END Delete_Row;
153
154 PROCEDURE Lock_Row(
155 p_INVENTORY_ITEM_ID NUMBER,
156 p_ORGANIZATION_ID NUMBER,
157 p_RELATED_ITEM_ID NUMBER,
158 p_RELATIONSHIP_TYPE_ID NUMBER,
159 p_RECIPROCAL_FLAG VARCHAR2,
160 p_LAST_UPDATE_DATE DATE,
161 p_LAST_UPDATED_BY NUMBER,
162 p_CREATION_DATE DATE,
163 p_CREATED_BY NUMBER,
164 p_LAST_UPDATE_LOGIN NUMBER,
165 p_REQUEST_ID NUMBER,
166 p_PROGRAM_APPLICATION_ID NUMBER,
167 p_PROGRAM_ID NUMBER,
168 p_PROGRAM_UPDATE_DATE DATE)
169
170 IS
171 CURSOR C IS
172 SELECT *
173 FROM MTL_RELATED_ITEMS
174 WHERE RELATED_ITEM_ID = p_RELATED_ITEM_ID
175 FOR UPDATE of RELATED_ITEM_ID NOWAIT;
176 Recinfo C%ROWTYPE;
177 BEGIN
178 OPEN C;
179 FETCH C INTO Recinfo;
180 If (C%NOTFOUND) then
181 CLOSE C;
182 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
183 APP_EXCEPTION.RAISE_EXCEPTION;
184 End If;
185 CLOSE C;
186 if (
187 ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
188 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
189 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
190 AND ( p_ORGANIZATION_ID IS NULL )))
191 AND ( ( Recinfo.RELATED_ITEM_ID = p_RELATED_ITEM_ID)
192 OR ( ( Recinfo.RELATED_ITEM_ID IS NULL )
193 AND ( p_RELATED_ITEM_ID IS NULL )))
194 AND ( ( Recinfo.RELATIONSHIP_TYPE_ID = p_RELATIONSHIP_TYPE_ID)
195 OR ( ( Recinfo.RELATIONSHIP_TYPE_ID IS NULL )
196 AND ( p_RELATIONSHIP_TYPE_ID IS NULL )))
197 AND ( ( Recinfo.RECIPROCAL_FLAG = p_RECIPROCAL_FLAG)
198 OR ( ( Recinfo.RECIPROCAL_FLAG IS NULL )
199 AND ( p_RECIPROCAL_FLAG IS NULL )))
200 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
201 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
202 AND ( p_LAST_UPDATE_DATE IS NULL )))
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.CREATION_DATE = p_CREATION_DATE)
207 OR ( ( Recinfo.CREATION_DATE IS NULL )
208 AND ( p_CREATION_DATE IS NULL )))
209 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
210 OR ( ( Recinfo.CREATED_BY IS NULL )
211 AND ( p_CREATED_BY IS NULL )))
212 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
213 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
214 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
215 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
216 OR ( ( Recinfo.REQUEST_ID IS NULL )
217 AND ( p_REQUEST_ID IS NULL )))
218 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
219 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
220 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
221 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
222 OR ( ( Recinfo.PROGRAM_ID IS NULL )
223 AND ( p_PROGRAM_ID IS NULL )))
224 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
225 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
226 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
227 ) then
228 return;
229 else
230 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 End If;
233 END Lock_Row;
234
235 End AMS_RELATED_ITEMS_PKG;