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