1 PACKAGE BODY JTF_TYPE_QTYPE_USGS_PKG as
2 /* $Header: jtfvtqub.pls 115.3 2000/02/29 18:25:50 pkm ship $ */
3
4 -- 01/20/00 VNEDUNGA Changing update/lock-row procedurs to
5 -- use TYPE_QTYPE_USG_ID instead of row_id
6 -- 01/20/00 VNEDUNGA Changing = NULL to IS NULL
7 -- 02/17/00 VNEDUNGA Adding ORG_ID to the table handler procedures
8
9 PROCEDURE Insert_Row(
10 x_Rowid IN OUT VARCHAR2,
11 x_TYPE_QTYPE_USG_ID IN OUT NUMBER,
12 x_LAST_UPDATED_BY IN NUMBER,
13 x_LAST_UPDATE_DATE IN DATE,
14 x_CREATED_BY IN NUMBER,
15 x_CREATION_DATE IN DATE,
16 x_LAST_UPDATE_LOGIN IN NUMBER,
17 x_TERR_TYPE_ID IN NUMBER,
18 x_QUAL_TYPE_USG_ID IN NUMBER,
19 x_ORG_ID IN NUMBER
20 ) IS
21 CURSOR C IS SELECT rowid FROM JTF_TYPE_QTYPE_USGS
22 WHERE TYPE_QTYPE_USG_ID = x_TYPE_QTYPE_USG_ID;
23 CURSOR C2 IS SELECT JTF_TYPE_QTYPE_USGS_s.nextval FROM sys.dual;
24 BEGIN
25 If (x_TYPE_QTYPE_USG_ID IS NULL) then
26 OPEN C2;
27 FETCH C2 INTO x_TYPE_QTYPE_USG_ID;
28 CLOSE C2;
29 End If;
30 INSERT INTO JTF_TYPE_QTYPE_USGS(
31 TYPE_QTYPE_USG_ID,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_DATE,
34 CREATED_BY,
35 CREATION_DATE,
36 LAST_UPDATE_LOGIN,
37 TERR_TYPE_ID,
38 QUAL_TYPE_USG_ID,
39 ORG_ID
40 ) VALUES (
41 x_TYPE_QTYPE_USG_ID,
42 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
43 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,x_LAST_UPDATE_DATE),
44 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
45 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, NULL,x_CREATION_DATE),
46 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
47 decode( x_TERR_TYPE_ID, FND_API.G_MISS_NUM, NULL,x_TERR_TYPE_ID),
48 decode( x_QUAL_TYPE_USG_ID, FND_API.G_MISS_NUM, NULL,x_QUAL_TYPE_USG_ID),
49 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL, x_ORG_ID) );
50 OPEN C;
51 FETCH C INTO x_Rowid;
52 If (C%NOTFOUND) then
53 CLOSE C;
54 RAISE NO_DATA_FOUND;
55 End If;
56 End Insert_Row;
57
58
59
60 PROCEDURE Delete_Row( x_TYPE_QTYPE_USG_ID IN NUMBER
61 ) IS
62 BEGIN
63 DELETE FROM JTF_TYPE_QTYPE_USGS
64 WHERE TYPE_QTYPE_USG_ID = x_TYPE_QTYPE_USG_ID;
65 If (SQL%NOTFOUND) then
66 RAISE NO_DATA_FOUND;
67 End If;
68 END Delete_Row;
69
70
71
72 PROCEDURE Update_Row(
73 x_Rowid IN VARCHAR2,
74 x_TYPE_QTYPE_USG_ID IN NUMBER,
75 x_LAST_UPDATED_BY IN NUMBER,
76 x_LAST_UPDATE_DATE IN DATE,
77 x_CREATED_BY IN NUMBER,
78 x_CREATION_DATE IN DATE,
79 x_LAST_UPDATE_LOGIN IN NUMBER,
80 x_TERR_TYPE_ID IN NUMBER,
81 x_QUAL_TYPE_USG_ID IN NUMBER,
82 x_ORG_ID IN NUMBER
83 ) IS
84 BEGIN
85 Update JTF_TYPE_QTYPE_USGS
86 SET
87 TYPE_QTYPE_USG_ID = decode( x_TYPE_QTYPE_USG_ID, FND_API.G_MISS_NUM,TYPE_QTYPE_USG_ID,x_TYPE_QTYPE_USG_ID),
88 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
89 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
90 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
91 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
92 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
93 TERR_TYPE_ID = decode( x_TERR_TYPE_ID, FND_API.G_MISS_NUM,TERR_TYPE_ID,x_TERR_TYPE_ID),
94 QUAL_TYPE_USG_ID = decode( x_QUAL_TYPE_USG_ID, FND_API.G_MISS_NUM,QUAL_TYPE_USG_ID,x_QUAL_TYPE_USG_ID),
95 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM, ORG_ID, x_ORG_ID)
96 where TYPE_QTYPE_USG_ID = X_TYPE_QTYPE_USG_ID;
97
98 If (SQL%NOTFOUND) then
99 RAISE NO_DATA_FOUND;
100 End If;
101 END Update_Row;
102
103
104
105 PROCEDURE Lock_Row(
106 x_Rowid IN VARCHAR2,
107 x_TYPE_QTYPE_USG_ID IN NUMBER,
108 x_LAST_UPDATED_BY IN NUMBER,
109 x_LAST_UPDATE_DATE IN DATE,
110 x_CREATED_BY IN NUMBER,
111 x_CREATION_DATE IN DATE,
112 x_LAST_UPDATE_LOGIN IN NUMBER,
113 x_TERR_TYPE_ID IN NUMBER,
114 x_QUAL_TYPE_USG_ID IN NUMBER,
115 x_ORG_ID IN NUMBER
116 ) IS
117 CURSOR C IS
118 SELECT *
119 FROM JTF_TYPE_QTYPE_USGS
120 WHERE TYPE_QTYPE_USG_ID = x_TYPE_QTYPE_USG_ID
121 FOR UPDATE of TYPE_QTYPE_USG_ID NOWAIT;
122 Recinfo C%ROWTYPE;
123 BEGIN
124 OPEN C;
125 FETCH C INTO Recinfo;
126 If (C%NOTFOUND) then
127 CLOSE C;
128 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
129 APP_EXCEPTION.RAISE_EXCEPTION;
130 End If;
131 CLOSE C;
132 if (
133 ( ( Recinfo.TYPE_QTYPE_USG_ID = x_TYPE_QTYPE_USG_ID)
134 OR ( ( Recinfo.TYPE_QTYPE_USG_ID IS NULL )
135 AND ( x_TYPE_QTYPE_USG_ID IS NULL )))
136 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
137 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
138 AND ( x_LAST_UPDATED_BY IS NULL )))
139 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
140 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
141 AND ( x_LAST_UPDATE_DATE IS NULL )))
142 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
143 OR ( ( Recinfo.CREATED_BY IS NULL )
144 AND ( x_CREATED_BY IS NULL )))
145 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
146 OR ( ( Recinfo.CREATION_DATE IS NULL )
147 AND ( x_CREATION_DATE IS NULL )))
148 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
149 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
150 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
151 AND ( ( Recinfo.TERR_TYPE_ID = x_TERR_TYPE_ID)
152 OR ( ( Recinfo.TERR_TYPE_ID IS NULL )
153 AND ( x_TERR_TYPE_ID IS NULL )))
154 AND ( ( Recinfo.QUAL_TYPE_USG_ID = x_QUAL_TYPE_USG_ID)
155 OR ( ( Recinfo.QUAL_TYPE_USG_ID IS NULL )
156 AND ( x_QUAL_TYPE_USG_ID IS NULL )))
157 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
158 OR ( ( Recinfo.ORG_ID IS NULL )
159 AND ( x_ORG_ID IS NULL )))
160 ) then
161 return;
162 else
163 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 End If;
166 END Lock_Row;
167
168 END JTF_TYPE_QTYPE_USGS_PKG;