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