1 package body PA_CI_SUPPLIER_PKG as
2 -- $Header: PACISIIB.pls 115.5 2002/12/02 23:35:18 riyengar noship $
3 PROCEDURE print_msg(p_msg varchar2) IS
4 BEGIN
5 --dbms_output.put_line('Log:'||p_msg);
6 --r_debug.r_msg('Log:'||p_msg);
7 PA_DEBUG.g_err_stage := p_msg;
8 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
9 null;
10 END print_msg;
11
12 PROCEDURE insert_row (
13 x_rowid IN OUT NOCOPY VARCHAR2
14 ,x_CI_TRANSACTION_ID IN OUT NOCOPY NUMBER
15 ,p_CI_TYPE_ID IN NUMBER
16 ,p_CI_ID IN NUMBER
17 ,p_CI_IMPACT_ID IN NUMBER
18 ,p_VENDOR_ID IN NUMBER
19 ,p_PO_HEADER_ID IN NUMBER
20 ,p_PO_LINE_ID IN NUMBER
21 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
22 ,p_CURRENCY_CODE IN VARCHAR2
23 ,p_CHANGE_AMOUNT IN NUMBER
24 ,p_CHANGE_TYPE IN VARCHAR2
25 ,p_CHANGE_DESCRIPTION IN VARCHAR2
26 ,p_CREATED_BY IN NUMBER
27 ,p_CREATION_DATE IN DATE
28 ,p_LAST_UPDATED_BY IN NUMBER
29 ,p_LAST_UPDATE_DATE IN DATE
30 ,p_LAST_UPDATE_LOGIN IN NUMBER
31 ,p_ci_status IN VARCHAR2
32 ,x_return_status OUT NOCOPY VARCHAR2
33 ,x_error_msg_code OUT NOCOPY VARCHAR2
34 )IS
35 cursor return_rowid is
36 select rowid
37 from pa_ci_supplier_details
38 where ci_transaction_id = x_ci_transaction_id;
39
40 cursor get_itemid is
41 select pa_ci_supplier_details_s.nextval
42 from sys.dual;
43
44 l_return_status varchar2(100) := 'S';
45 l_error_msg_code varchar2(100) := NULL;
46 l_debug_mode varchar2(1) := 'N';
47 BEGIN
48
49 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
50 l_debug_mode := NVL(l_debug_mode, 'N');
51
52 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
53
54 IF l_debug_mode = 'Y' THEN
55 print_msg('Inside pa_ci_supplier_pkg table handler..');
56 End IF;
57
58 if (x_ci_transaction_id is null) then
59 open get_itemid;
60 fetch get_itemid into x_ci_transaction_id;
61 close get_itemid;
62 end if;
63
64 IF l_debug_mode = 'Y' THEN
65 print_msg('Info transacton id ..'||x_ci_transaction_id);
66 End IF;
67
68 INSERT into pa_ci_supplier_details
69 ( CI_TRANSACTION_ID
70 ,CI_TYPE_ID
71 ,CI_ID
72 ,CI_IMPACT_ID
73 ,VENDOR_ID
74 ,PO_HEADER_ID
75 ,PO_LINE_ID
76 ,ADJUSTED_CI_TRANSACTION_ID
77 ,CURRENCY_CODE
78 ,CHANGE_AMOUNT
79 ,CHANGE_TYPE
80 ,CHANGE_DESCRIPTION
81 ,CREATED_BY
82 ,CREATION_DATE
83 ,LAST_UPDATED_BY
84 ,LAST_UPDATE_DATE
85 ,LAST_UPDATE_LOGIN
86 ) VALUES
87 ( x_CI_TRANSACTION_ID
88 ,p_CI_TYPE_ID
89 ,p_CI_ID
90 ,p_CI_IMPACT_ID
91 ,p_VENDOR_ID
92 ,p_PO_HEADER_ID
93 ,p_PO_LINE_ID
94 ,p_ADJUSTED_TRANSACTION_ID
95 ,p_CURRENCY_CODE
96 ,pa_currency.round_trans_currency_amt
97 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
98 ,p_CHANGE_TYPE
99 ,p_CHANGE_DESCRIPTION
100 ,p_CREATED_BY
101 ,p_CREATION_DATE
102 ,p_LAST_UPDATED_BY
103 ,p_LAST_UPDATE_DATE
104 ,p_LAST_UPDATE_LOGIN
105 );
106 OPEN return_rowid;
107 FETCH return_rowid into x_rowid;
108 IF (return_rowid%notfound) then
109 l_return_status := 'E';
110 l_error_msg_code := 'NO_DATA_FOUND';
111 IF l_debug_mode = 'Y' THEN
112 print_msg('rowid not found raise insert failed');
113 End If;
114 raise NO_DATA_FOUND; -- should we return something else?
115 Else
116 IF l_debug_mode = 'Y' THEN
117 print_msg('Insert success');
118 End If;
119 l_return_status := 'S';
120 l_error_msg_code := NULL;
121 End if;
122 CLOSE return_rowid;
123
124 x_return_status := l_return_status;
125 x_error_msg_code := l_error_msg_code;
126 EXCEPTION
127 when others then
128 x_error_msg_code := sqlcode||sqlerrm;
129 IF l_debug_mode = 'Y' THEN
130 print_msg('x_err_msg_code exception:'||x_error_msg_code);
131 End If;
132 Raise;
133
134 END insert_row;
135
136 PROCEDURE update_row
137 (p_rowid IN VARCHAR2
138 ,p_ci_transaction_id IN NUMBER
139 ,p_CI_TYPE_ID IN NUMBER
140 ,p_CI_ID IN NUMBER
141 ,p_CI_IMPACT_ID IN NUMBER
142 ,p_VENDOR_ID IN NUMBER
143 ,p_PO_HEADER_ID IN NUMBER
144 ,p_PO_LINE_ID IN NUMBER
145 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
146 ,p_CURRENCY_CODE IN VARCHAR2
147 ,p_CHANGE_AMOUNT IN NUMBER
148 ,p_CHANGE_TYPE IN VARCHAR2
149 ,p_CHANGE_DESCRIPTION IN VARCHAR2
150 ,p_LAST_UPDATED_BY IN NUMBER
151 ,p_LAST_UPDATE_DATE IN DATE
152 ,p_LAST_UPDATE_LOGIN IN NUMBER
153 ,p_ci_status IN VARCHAR2
154 ,x_return_status OUT NOCOPY VARCHAR2
155 ,x_error_msg_code OUT NOCOPY VARCHAR2
156 )IS
157 CURSOR cur_row is
158 SELECT CI_TYPE_ID
159 ,CI_ID
160 ,CI_IMPACT_ID
161 ,VENDOR_ID
162 ,PO_HEADER_ID
163 ,PO_LINE_ID
164 ,ADJUSTED_CI_TRANSACTION_ID
165 ,CURRENCY_CODE
166 ,CHANGE_AMOUNT
167 ,CHANGE_TYPE
168 ,CHANGE_DESCRIPTION
169 FROM pa_ci_supplier_details
170 WHERE ci_transaction_id = p_ci_transaction_id
171 FOR UPDATE OF ci_transaction_id NOWAIT;
172
173 recinfo cur_row%rowtype;
174 l_debug_mode varchar2(1) := 'N';
175 BEGIN
176
177 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
178 l_debug_mode := NVL(l_debug_mode, 'N');
179
180 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
181
182 /** set the return status to success **/
183 x_return_status := 'S';
184 x_error_msg_code := NULL;
185
186 If l_debug_mode = 'Y' Then
187 print_msg('Inside update row.');
188 End If;
189
190 OPEN cur_row;
191 FETCH cur_row INTO recinfo;
192 If cur_row%NOTFOUND then
193 If l_debug_mode = 'Y' Then
194 print_msg('row not found return');
195 End If;
196 return;
197 End If;
198 CLOSE cur_row;
199
200 /** check if any of the attributes changed then update else donot **/
201 IF Nvl(recinfo.vendor_id,0) <> nvl(p_vendor_id,0) OR
202 Nvl(recinfo.po_header_id,0) <> nvl(p_po_header_id,0) OR
203 Nvl(recinfo.po_line_id,0) <> nvl(p_po_line_id,0) OR
204 Nvl(recinfo.currency_code,'X') <> nvl(p_currency_code,'X') OR
205 Nvl(recinfo.change_amount,0) <> nvl(p_change_amount,0) OR
206 nvl(recinfo.change_type,'X') <> nvl(p_change_type,'X') OR
207 Nvl(recinfo.change_description,'X') <> nvl(p_change_description,'X') THEN
208 If l_debug_mode = 'Y' Then
209 print_msg('firing update query');
210 End If;
211 UPDATE pa_ci_supplier_details SET
212 VENDOR_ID = p_vendor_id
213 ,PO_HEADER_ID = p_po_header_id
214 ,PO_LINE_ID = p_po_line_id
215 ,ADJUSTED_CI_TRANSACTION_ID = p_adjusted_transaction_id
216 ,CURRENCY_CODE = p_currency_code
217 ,CHANGE_AMOUNT = pa_currency.round_trans_currency_amt
218 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
219 ,CHANGE_TYPE = p_change_type
220 ,CHANGE_DESCRIPTION = p_change_description
221 ,LAST_UPDATED_BY = p_last_updated_by
222 ,LAST_UPDATE_DATE = p_last_update_date
223 ,LAST_UPDATE_LOGIN = p_last_update_login
224 WHERE ci_transaction_id = p_ci_transaction_id;
225 If sql%found then
226 x_return_status := 'S';
227 Else
228 x_return_status := 'E';
229 x_error_msg_code := 'NO_DATA_FOUND';
230 If l_debug_mode = 'Y' Then
231 print_msg('Update failure:'||x_error_msg_code);
232 End If;
233 raise NO_DATA_FOUND;
234 End If;
235
236 End IF;
237
238 EXCEPTION
239 when others then
240 x_error_msg_code := sqlcode||sqlerrm;
241 If l_debug_mode = 'Y' Then
242 print_msg('Exception:'||x_error_msg_code);
243 End if;
244 Raise;
245
246 END update_row;
247
248
249 PROCEDURE delete_row (p_ci_transaction_id in NUMBER)IS
250
251 l_debug_mode varchar2(1) := 'N';
252 BEGIN
253
254 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
255 l_debug_mode := NVL(l_debug_mode, 'N');
256
257 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
258
259 DELETE FROM PA_CI_SUPPLIER_DETAILS
260 WHERE CI_TRANSACTION_ID = P_CI_TRANSACTION_ID;
261 if sql%found then
262 If l_debug_mode = 'Y' Then
263 print_msg('Delete Success');
264 End iF;
265 Else
266 If l_debug_mode = 'Y' Then
267 print_msg('Delete Failure');
268 End If;
269 End if;
270
271 END delete_row;
272
273 PROCEDURE delete_row (x_rowid in VARCHAR2)IS
274
275 cursor get_itemid is
276 select ci_transaction_id
277 from pa_ci_supplier_details
278 where rowid = x_rowid;
279
280 l_ci_transaction_id Number;
281
282 BEGIN
283 open get_itemid;
284 fetch get_itemid into l_ci_transaction_id;
285 close get_itemid;
286
287 delete_row (l_ci_transaction_id);
288
289 END delete_row;
290
291 PROCEDURE lock_row (x_rowid in VARCHAR2)IS
292 BEGIN
293 null;
294 END lock_row;
295
296 END PA_CI_SUPPLIER_PKG;
[Home] [Help]