1 PACKAGE BODY CST_PAC_ITEM_COSTS_PKG AS
2 /* $Header: CSTGLICB.pls 115.7 2002/11/08 20:33:53 awwang ship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 | FILENAME : CSTGLICS.pls |
8 | |
9 | DESCRIPTION: Use this package to insert,lock,update and delete |
10 | records from the table CST_PAC_ITEM_COSTS |
11 | |
12 | PROCEDURE LIST: |
13 | PROCEDURE Insert_row, |
14 | PROCEDURE Lock_row, |
15 | PROCEDURE Update_row, |
16 | PROCEDURE Delete_row |
17 | |
18 | HISTORY: |
19 | 02/15/99 Tatiana Simmonds Created |
20 +=======================================================================*/
21
22 --======================================================================
23 --CONSTANTS
24 --======================================================================
25 G_PKG_NAME CONSTANT VARCHAR2(30) :='CST_PAC_ITEM_COSTS_PKG';
26
27
28 --========================================================================
29 -- PROCEDURE : Insert_row PUBLIC
30 -- PARAMETERS: p_row_id ROWID of the current record
31 -- p_pac_period_id period id
32 -- p_cost_group_id cost group id
33 -- p_inventory_item_id inventory item id
34 -- p_item_cost item cost
35 -- p_market_value market value
36 -- p_justification justification
37 -- p_creation_date date, when a record was inserted
38 -- p_created_by userid of the person,who inserted a record
39 -- COMMENT : Procedure inserts record into the table CST_PAC_ITEM_COSTS
40 --========================================================================
41 PROCEDURE Insert_row (
42 p_row_id IN OUT NOCOPY VARCHAR2
43 , p_pac_period_id NUMBER
44 , p_cost_group_id NUMBER
45 , p_inventory_item_id NUMBER
46 , p_item_cost NUMBER
47 , p_market_value NUMBER
48 , p_justification VARCHAR2
49 , p_creation_date DATE
50 , p_created_by NUMBER
51 )
52 IS
53 CURSOR C IS
54 SELECT
55 rowid
56 FROM
57 CST_PAC_ITEM_COSTS
58 WHERE pac_period_id=p_pac_period_id
59 AND cost_group_id=p_cost_group_id
60 AND inventory_item_id=p_inventory_item_id;
61
62 BEGIN
63 INSERT
64 INTO cst_pac_item_costs
65 ( pac_period_id
66 , cost_group_id
67 , inventory_item_id
68 , item_cost
69 , market_value
70 , justification
71 , creation_date
72 , created_by
73 )
74 VALUES
75 ( p_pac_period_id
76 , p_cost_group_id
77 , p_inventory_item_id
78 , p_item_cost
79 , p_market_value
80 , p_justification
81 , p_creation_date
82 , p_created_by
83 );
84
85 OPEN c;
86 FETCH c INTO p_row_id;
87 IF (c%NOTFOUND)
88 THEN
89 CLOSE c;
90 RAISE NO_DATA_FOUND;
91 END IF;
92 CLOSE c;
93 EXCEPTION
94 WHEN OTHERS THEN
95 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
96 THEN
97 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Insert_row');
98 END IF;
99 RAISE;
100
101 END Insert_row;
102
103
104 --========================================================================
105 -- PROCEDURE : Lock_row PUBLIC
106 -- PARAMETERS: p_row_id ROWID of the current record
107 -- p_market_value market value
108 -- p_justification justification
109 -- COMMENT : Procedure locks current record in the table CST_PAC_ITEM_COSTS.
110 --========================================================================
111 PROCEDURE Lock_row (
112 p_row_id VARCHAR2
113 ,p_market_value NUMBER
114 ,p_justification VARCHAR2
115 )
116 IS
117 CURSOR c
118 IS
119 SELECT *
120 FROM CST_PAC_ITEM_COSTS
121 WHERE ROWID=CHARTOROWID(p_row_id)
122 FOR UPDATE OF market_value NOWAIT;
123
124 recinfo c%ROWTYPE;
125
126 BEGIN
127
128 OPEN c;
129 FETCH c INTO recinfo;
130 IF (c%NOTFOUND)
131 THEN
132 CLOSE c;
133 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_DELETED');
134 RAISE NO_DATA_FOUND;
135 END IF;
136 CLOSE c;
137
138 IF
139 ((recinfo.market_value=p_market_value)
140 OR (recinfo.market_value is NULL AND p_market_value is NULL))
141 AND
142 ((recinfo.justification=p_justification)
143 OR (recinfo.justification is NULL AND p_justification is NULL))
144 THEN
145 NULL;
146 ELSE
147 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
148 APP_EXCEPTION.raise_exception;
149 END IF;
150
151 END Lock_Row;
152
153
154 --========================================================================
155 -- PROCEDURE : Update_row PUBLIC
156 -- PARAMETERS: p_row_id ROWID of the current record
157 -- p_item_cost item cost
158 -- p_market_value market value
159 -- p_justification justification
160 -- p_last_update_date date,when the record was updated
161 -- p_last_updated_by userid of the person,who updated the record
162 -- COMMENT : Procedure updates columns market_value and justification
163 -- in the table CST_PAC_ITEM_COSTS for the record
164 -- with ROWID,passed as a parameter p_row_id.
165 --========================================================================
166 PROCEDURE Update_row (
167 p_row_id VARCHAR2
168 , p_item_cost NUMBER
169 , p_market_value NUMBER
170 , p_justification VARCHAR2
171 , p_last_update_date DATE
172 , p_last_updated_by NUMBER
173 )
174 IS
175
176 BEGIN
177 UPDATE CST_PAC_ITEM_COSTS
178 SET
179 item_cost=p_item_cost
180 ,market_value=p_market_value
181 ,justification=p_justification
182 ,last_update_date=p_last_update_date
183 ,last_updated_by=p_last_updated_by
184 WHERE ROWID=CHARTOROWID(p_row_id);
185
186
187 IF (SQL%NOTFOUND)
188 THEN
189 RAISE NO_DATA_FOUND;
190 END IF;
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Update_row');
196 END IF;
197 RAISE;
198
199 END Update_Row;
200
201
202 --========================================================================
203 -- PROCEDURE : Delete_row PUBLIC
204 -- PARAMETERS: p_row_id ROWID of the current record
205 -- COMMENT : Procedure deletes record with ROWID=p_row_id from the
206 -- table CST_PAC_ITEM_COSTS.
207 --========================================================================
208 PROCEDURE Delete_row (
209 p_row_id VARCHAR2)
210 IS
211 BEGIN
212 DELETE
213 FROM CST_PAC_ITEM_COSTS
214 WHERE ROWID=p_row_id;
215
216 IF (SQL%NOTFOUND)
217 THEN
218 RAISE NO_DATA_FOUND;
219 END IF;
220 EXCEPTION
221 WHEN OTHERS THEN
222 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
223 THEN
224 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
225 END IF;
226 RAISE;
227
228 END Delete_row;
229
230 END CST_PAC_ITEM_COSTS_PKG;
[Home] [Help]