[Home] [Help]
PACKAGE BODY: APPS.HZ_INDUSTRIAL_REFERENCE_PKG
Source
1 PACKAGE BODY HZ_INDUSTRIAL_REFERENCE_PKG as
2 /* $Header: ARHORITB.pls 120.3 2005/10/30 04:21:08 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_Rowid IN OUT NOCOPY VARCHAR2,
7 x_INDUSTRY_REFERENCE_ID NUMBER,
8 x_INDUSTRY_REFERENCE VARCHAR2,
9 x_ISSUED_BY_AUTHORITY VARCHAR2,
10 x_NAME_OF_REFERENCE VARCHAR2,
11 x_RECOGNIZED_AS_OF_DATE DATE,
12 x_CREATED_BY NUMBER,
13 x_CREATION_DATE DATE,
14 x_LAST_UPDATE_LOGIN NUMBER,
15 x_LAST_UPDATE_DATE DATE,
16 x_LAST_UPDATED_BY NUMBER,
17 x_REQUEST_ID NUMBER,
18 x_PROGRAM_APPLICATION_ID NUMBER,
19 x_PROGRAM_ID NUMBER,
20 x_PROGRAM_UPDATE_DATE DATE,
21 x_WH_UPDATE_DATE DATE,
22 x_PARTY_ID NUMBER,
23 x_STATUS VARCHAR2
24 ) IS
25 CURSOR C IS SELECT rowid FROM HZ_INDUSTRIAL_REFERENCE
26 WHERE INDUSTRY_REFERENCE_ID = x_INDUSTRY_REFERENCE_ID;
27 BEGIN
28 INSERT INTO HZ_INDUSTRIAL_REFERENCE(
29 INDUSTRY_REFERENCE_ID,
30 INDUSTRY_REFERENCE,
31 ISSUED_BY_AUTHORITY,
32 NAME_OF_REFERENCE,
33 RECOGNIZED_AS_OF_DATE,
34 CREATED_BY,
35 CREATION_DATE,
36 LAST_UPDATE_LOGIN,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 REQUEST_ID,
40 PROGRAM_APPLICATION_ID,
41 PROGRAM_ID,
42 PROGRAM_UPDATE_DATE,
43 WH_UPDATE_DATE,
44 PARTY_ID,
45 STATUS
46 ) VALUES (
47 x_INDUSTRY_REFERENCE_ID,
48 decode( x_INDUSTRY_REFERENCE, FND_API.G_MISS_CHAR, NULL,x_INDUSTRY_REFERENCE),
49 decode( x_ISSUED_BY_AUTHORITY, FND_API.G_MISS_CHAR, NULL,x_ISSUED_BY_AUTHORITY),
50 decode( x_NAME_OF_REFERENCE, FND_API.G_MISS_CHAR, NULL,x_NAME_OF_REFERENCE),
51 decode( x_RECOGNIZED_AS_OF_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_RECOGNIZED_AS_OF_DATE),
52 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
53 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
54 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
55 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
56 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
57 decode( x_REQUEST_ID, FND_API.G_MISS_NUM, NULL,x_REQUEST_ID),
58 decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_APPLICATION_ID),
59 decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_ID),
60 decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_PROGRAM_UPDATE_DATE),
61 decode( x_WH_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_WH_UPDATE_DATE),
62 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
63 decode(x_STATUS,FND_API.G_MISS_CHAR,'A',x_STATUS));
64 OPEN C;
65 FETCH C INTO x_Rowid;
66 If (C%NOTFOUND) then
67 CLOSE C;
68 RAISE NO_DATA_FOUND;
69 End If;
70 End Insert_Row;
71
72
73
74 PROCEDURE Delete_Row( x_INDUSTRY_REFERENCE_ID NUMBER
75 ) IS
76 BEGIN
77 DELETE FROM HZ_INDUSTRIAL_REFERENCE
78 WHERE INDUSTRY_REFERENCE_ID = x_INDUSTRY_REFERENCE_ID;
79 If (SQL%NOTFOUND) then
80 RAISE NO_DATA_FOUND;
81 End If;
82 END Delete_Row;
83
84
85
86 PROCEDURE Update_Row(
87 x_Rowid IN OUT NOCOPY VARCHAR2,
88 x_INDUSTRY_REFERENCE_ID NUMBER,
89 x_INDUSTRY_REFERENCE VARCHAR2,
90 x_ISSUED_BY_AUTHORITY VARCHAR2,
91 x_NAME_OF_REFERENCE VARCHAR2,
92 x_RECOGNIZED_AS_OF_DATE DATE,
93 x_CREATED_BY NUMBER,
94 x_CREATION_DATE DATE,
95 x_LAST_UPDATE_LOGIN NUMBER,
96 x_LAST_UPDATE_DATE DATE,
97 x_LAST_UPDATED_BY NUMBER,
98 x_REQUEST_ID NUMBER,
99 x_PROGRAM_APPLICATION_ID NUMBER,
100 x_PROGRAM_ID NUMBER,
101 x_PROGRAM_UPDATE_DATE DATE,
102 x_WH_UPDATE_DATE DATE,
103 x_PARTY_ID NUMBER,
104 x_STATUS VARCHAR2
105 ) IS
106 BEGIN
107 Update HZ_INDUSTRIAL_REFERENCE
108 SET
109 INDUSTRY_REFERENCE_ID = decode( x_INDUSTRY_REFERENCE_ID, FND_API.G_MISS_NUM,INDUSTRY_REFERENCE_ID,x_INDUSTRY_REFERENCE_ID),
110 INDUSTRY_REFERENCE = decode( x_INDUSTRY_REFERENCE, FND_API.G_MISS_CHAR,INDUSTRY_REFERENCE,x_INDUSTRY_REFERENCE),
111 ISSUED_BY_AUTHORITY = decode( x_ISSUED_BY_AUTHORITY, FND_API.G_MISS_CHAR,ISSUED_BY_AUTHORITY,x_ISSUED_BY_AUTHORITY),
112 NAME_OF_REFERENCE = decode( x_NAME_OF_REFERENCE, FND_API.G_MISS_CHAR,NAME_OF_REFERENCE,x_NAME_OF_REFERENCE),
113 RECOGNIZED_AS_OF_DATE = decode( x_RECOGNIZED_AS_OF_DATE, FND_API.G_MISS_DATE,RECOGNIZED_AS_OF_DATE,x_RECOGNIZED_AS_OF_DATE),
114 -- Bug 3032780
115 /*
116 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
117 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
118 */
119 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
120 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
121 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
122 REQUEST_ID = decode( x_REQUEST_ID, FND_API.G_MISS_NUM,REQUEST_ID,x_REQUEST_ID),
123 PROGRAM_APPLICATION_ID = decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM,PROGRAM_APPLICATION_ID,x_PROGRAM_APPLICATION_ID),
124 PROGRAM_ID = decode( x_PROGRAM_ID, FND_API.G_MISS_NUM,PROGRAM_ID,x_PROGRAM_ID),
125 PROGRAM_UPDATE_DATE = decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE,PROGRAM_UPDATE_DATE,x_PROGRAM_UPDATE_DATE),
126 WH_UPDATE_DATE = decode( x_WH_UPDATE_DATE, FND_API.G_MISS_DATE,WH_UPDATE_DATE,x_WH_UPDATE_DATE),
127 PARTY_ID = decode( x_PARTY_ID, FND_API.G_MISS_NUM,PARTY_ID,x_PARTY_ID),
128 STATUS =decode(x_STATUS,FND_API.G_MISS_CHAR,STATUS,x_STATUS)
129 where rowid = X_RowId;
130
131 If (SQL%NOTFOUND) then
132 RAISE NO_DATA_FOUND;
133 End If;
134 END Update_Row;
135
136
137
138 PROCEDURE Lock_Row(
139 x_Rowid VARCHAR2,
140 x_INDUSTRY_REFERENCE_ID NUMBER,
141 x_INDUSTRY_REFERENCE VARCHAR2,
142 x_ISSUED_BY_AUTHORITY VARCHAR2,
143 x_NAME_OF_REFERENCE VARCHAR2,
144 x_RECOGNIZED_AS_OF_DATE DATE,
145 x_CREATED_BY NUMBER,
146 x_CREATION_DATE DATE,
147 x_LAST_UPDATE_LOGIN NUMBER,
148 x_LAST_UPDATE_DATE DATE,
149 x_LAST_UPDATED_BY NUMBER,
150 x_REQUEST_ID NUMBER,
151 x_PROGRAM_APPLICATION_ID NUMBER,
152 x_PROGRAM_ID NUMBER,
153 x_PROGRAM_UPDATE_DATE DATE,
154 x_WH_UPDATE_DATE DATE,
155 x_PARTY_ID NUMBER,
156 x_STATUS VARCHAR2
157 ) IS
158 CURSOR C IS
159 SELECT *
160 FROM HZ_INDUSTRIAL_REFERENCE
161 WHERE rowid = x_Rowid
162 FOR UPDATE of INDUSTRY_REFERENCE_ID NOWAIT;
163 Recinfo C%ROWTYPE;
164 BEGIN
165 OPEN C;
166 FETCH C INTO Recinfo;
167 If (C%NOTFOUND) then
168 CLOSE C;
169 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
170 APP_EXCEPTION.RAISE_EXCEPTION;
171 End If;
172 CLOSE C;
173 if (
174 ( ( Recinfo.INDUSTRY_REFERENCE_ID = x_INDUSTRY_REFERENCE_ID)
175 OR ( ( Recinfo.INDUSTRY_REFERENCE_ID = NULL )
176 AND ( x_INDUSTRY_REFERENCE_ID = NULL )))
177 AND ( ( Recinfo.INDUSTRY_REFERENCE = x_INDUSTRY_REFERENCE)
178 OR ( ( Recinfo.INDUSTRY_REFERENCE = NULL )
179 AND ( x_INDUSTRY_REFERENCE = NULL )))
180 AND ( ( Recinfo.ISSUED_BY_AUTHORITY = x_ISSUED_BY_AUTHORITY)
181 OR ( ( Recinfo.ISSUED_BY_AUTHORITY = NULL )
182 AND ( x_ISSUED_BY_AUTHORITY = NULL )))
183 AND ( ( Recinfo.NAME_OF_REFERENCE = x_NAME_OF_REFERENCE)
184 OR ( ( Recinfo.NAME_OF_REFERENCE = NULL )
185 AND ( x_NAME_OF_REFERENCE = NULL )))
186 AND ( ( Recinfo.RECOGNIZED_AS_OF_DATE = x_RECOGNIZED_AS_OF_DATE)
187 OR ( ( Recinfo.RECOGNIZED_AS_OF_DATE = NULL )
188 AND ( x_RECOGNIZED_AS_OF_DATE = NULL )))
189 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
190 OR ( ( Recinfo.CREATED_BY = NULL )
191 AND ( x_CREATED_BY = NULL )))
192 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
193 OR ( ( Recinfo.CREATION_DATE = NULL )
194 AND ( x_CREATION_DATE = NULL )))
195 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
196 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
197 AND ( x_LAST_UPDATE_LOGIN = NULL )))
198 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
199 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
200 AND ( x_LAST_UPDATE_DATE = NULL )))
201 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
202 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
203 AND ( x_LAST_UPDATED_BY = NULL )))
204 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
205 OR ( ( Recinfo.REQUEST_ID = NULL )
206 AND ( x_REQUEST_ID = NULL )))
207 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
208 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
209 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
210 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
211 OR ( ( Recinfo.PROGRAM_ID = NULL )
212 AND ( x_PROGRAM_ID = NULL )))
213 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
214 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
215 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
216 AND ( ( Recinfo.WH_UPDATE_DATE = x_WH_UPDATE_DATE)
217 OR ( ( Recinfo.WH_UPDATE_DATE = NULL )
218 AND ( x_WH_UPDATE_DATE = NULL )))
219 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
220 OR ( ( Recinfo.PARTY_ID = NULL )
221 AND ( x_PARTY_ID = NULL )))
222
223 AND ( ( Recinfo.STATUS = x_PARTY_ID)
224 OR ( ( Recinfo.STATUS = NULL )
225 AND ( x_STATUS = NULL )))
226 ) then
227 return;
228 else
229 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
230 APP_EXCEPTION.RAISE_EXCEPTION;
231 End If;
232 END Lock_Row;
233
234 END HZ_INDUSTRIAL_REFERENCE_PKG;