1 PACKAGE BODY JTF_TERR_RSC_ACCESS_PKG AS
2 /* $Header: jtfvrsab.pls 120.2 2005/09/08 17:55:09 applrt ship $ */
3
4 -- 01/20/00 VNEDUNGA Changing Update/Lock_Row procedure to use
5 -- TERR_RSC_ACCESS_ID instead of rowid
6 -- 01/20/00 VNEDUNGA Changing = NULL -> IS NULL
7 -- 02/22/00 JDOCHERT Passing in ORG_ID to Insert/Update/Lock
8
9
10 PROCEDURE Insert_Row(
11 x_Rowid IN OUT NOCOPY VARCHAR2,
12 x_TERR_RSC_ACCESS_ID IN OUT NOCOPY NUMBER,
13 x_LAST_UPDATE_DATE IN DATE,
14 x_LAST_UPDATED_BY IN NUMBER,
15 x_CREATION_DATE IN DATE,
16 x_CREATED_BY IN NUMBER,
17 x_LAST_UPDATE_LOGIN IN NUMBER,
18 x_TERR_RSC_ID IN NUMBER,
19 x_ACCESS_TYPE IN VARCHAR2,
20 x_TRANS_ACCESS_CODE IN VARCHAR2,
21 x_ORG_ID IN NUMBER
22 ) IS
23 CURSOR C IS SELECT rowid FROM JTF_TERR_RSC_ACCESS_ALL
24 WHERE TERR_RSC_ACCESS_ID = x_TERR_RSC_ACCESS_ID;
25 CURSOR C2 IS SELECT JTF_TERR_RSC_ACCESS_s.nextval FROM sys.dual;
26 BEGIN
27 If (x_TERR_RSC_ACCESS_ID IS NULL) then
28 OPEN C2;
29 FETCH C2 INTO x_TERR_RSC_ACCESS_ID;
30 CLOSE C2;
31 End If;
32 INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
33 TERR_RSC_ACCESS_ID,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_LOGIN,
39 TERR_RSC_ID,
40 ACCESS_TYPE,
41 TRANS_ACCESS_CODE,
42 ORG_ID
43 ) VALUES (
44 x_TERR_RSC_ACCESS_ID,
45 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
46 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
47 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
48 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
49 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
50 decode( x_TERR_RSC_ID, FND_API.G_MISS_NUM, NULL,x_TERR_RSC_ID),
51 decode( x_ACCESS_TYPE, FND_API.G_MISS_CHAR, NULL,x_ACCESS_TYPE),
52 decode( x_TRANS_ACCESS_CODE, FND_API.G_MISS_CHAR, NULL,x_TRANS_ACCESS_CODE),
53 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID)
54 );
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_TERR_RSC_ACCESS_ID IN NUMBER
66 ) IS
67 BEGIN
68 DELETE FROM JTF_TERR_RSC_ACCESS_ALL
69 WHERE TERR_RSC_ACCESS_ID = x_TERR_RSC_ACCESS_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 VARCHAR2,
79 x_TERR_RSC_ACCESS_ID IN NUMBER,
80 x_LAST_UPDATE_DATE IN DATE,
81 x_LAST_UPDATED_BY IN NUMBER,
82 x_CREATION_DATE IN DATE,
83 x_CREATED_BY IN NUMBER,
84 x_LAST_UPDATE_LOGIN IN NUMBER,
85 x_TERR_RSC_ID IN NUMBER,
86 x_ACCESS_TYPE IN VARCHAR2,
87 x_TRANS_ACCESS_CODE IN VARCHAR2,
88 x_ORG_ID IN NUMBER
89 ) IS
90 BEGIN
91 Update JTF_TERR_RSC_ACCESS_ALL
92 SET
93 TERR_RSC_ACCESS_ID = decode( x_TERR_RSC_ACCESS_ID, FND_API.G_MISS_NUM,TERR_RSC_ACCESS_ID,x_TERR_RSC_ACCESS_ID),
94 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
95 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
96 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
97 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
98 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
99 TERR_RSC_ID = decode( x_TERR_RSC_ID, FND_API.G_MISS_NUM,TERR_RSC_ID,x_TERR_RSC_ID),
100 ACCESS_TYPE = decode( x_ORG_ID, FND_API.G_MISS_CHAR,ACCESS_TYPE,x_ACCESS_TYPE),
101 TRANS_ACCESS_CODE = decode( x_ORG_ID, FND_API.G_MISS_CHAR,TRANS_ACCESS_CODE,x_TRANS_ACCESS_CODE),
102 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM,ORG_ID,x_ORG_ID)
103 where TERR_RSC_ACCESS_ID = X_TERR_RSC_ACCESS_ID;
104
105 If (SQL%NOTFOUND) then
106 RAISE NO_DATA_FOUND;
107 End If;
108 END Update_Row;
109
110
111
112 PROCEDURE Lock_Row(
113 x_Rowid IN VARCHAR2,
114 x_TERR_RSC_ACCESS_ID IN NUMBER,
115 x_LAST_UPDATE_DATE IN DATE,
116 x_LAST_UPDATED_BY IN NUMBER,
117 x_CREATION_DATE IN DATE,
118 x_CREATED_BY IN NUMBER,
119 x_LAST_UPDATE_LOGIN IN NUMBER,
120 x_TERR_RSC_ID IN NUMBER,
121 x_ACCESS_TYPE IN VARCHAR2,
122 x_TRANS_ACCESS_CODE IN VARCHAR2,
123 x_ORG_ID IN NUMBER
124 ) IS
125 CURSOR C IS
126 SELECT *
127 FROM JTF_TERR_RSC_ACCESS_ALL
128 WHERE TERR_RSC_ACCESS_ID = x_TERR_RSC_ACCESS_ID
129 FOR UPDATE of TERR_RSC_ACCESS_ID NOWAIT;
130 Recinfo C%ROWTYPE;
131 BEGIN
132 OPEN C;
133 FETCH C INTO Recinfo;
134 If (C%NOTFOUND) then
135 CLOSE C;
136 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
137 APP_EXCEPTION.RAISE_EXCEPTION;
138 End If;
139 CLOSE C;
140 if (
141 ( ( Recinfo.TERR_RSC_ACCESS_ID = x_TERR_RSC_ACCESS_ID)
142 OR ( ( Recinfo.TERR_RSC_ACCESS_ID IS NULL )
143 AND ( x_TERR_RSC_ACCESS_ID IS NULL )))
144 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
145 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
146 AND ( x_LAST_UPDATE_DATE IS NULL )))
147 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
148 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
149 AND ( x_LAST_UPDATED_BY IS NULL )))
150 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
151 OR ( ( Recinfo.CREATION_DATE IS NULL )
152 AND ( x_CREATION_DATE IS NULL )))
153 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
154 OR ( ( Recinfo.CREATED_BY IS NULL )
155 AND ( x_CREATED_BY IS NULL )))
156 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
157 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
158 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
159 AND ( ( Recinfo.TERR_RSC_ID = x_TERR_RSC_ID)
160 OR ( ( Recinfo.TERR_RSC_ID IS NULL )
161 AND ( x_TERR_RSC_ID IS NULL )))
162 AND ( ( Recinfo.ACCESS_TYPE = x_ACCESS_TYPE)
163 OR ( ( Recinfo.ACCESS_TYPE IS NULL )
164 AND ( x_ACCESS_TYPE IS NULL )))
165 AND ( ( Recinfo.TRANS_ACCESS_CODE = x_TRANS_ACCESS_CODE)
166 OR ( ( Recinfo.TRANS_ACCESS_CODE IS NULL )
167 AND ( x_TRANS_ACCESS_CODE IS NULL )))
168 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
169 OR ( ( Recinfo.ORG_ID IS NULL )
170 AND ( x_ORG_ID IS NULL )))
171 ) then
172 return;
173 else
174 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
175 APP_EXCEPTION.RAISE_EXCEPTION;
176 End If;
177 END Lock_Row;
178
179 END JTF_TERR_RSC_ACCESS_PKG;
[Home] [Help]