[Home] [Help]
PACKAGE BODY: APPS.MTL_CLIENT_PARAMETERS_PKG
Source
1 PACKAGE BODY MTL_CLIENT_PARAMETERS_PKG AS
2 /* $Header: INVCTDFB.pls 120.1 2011/01/14 13:33:41 gjyoti noship $ */
3 /*#
4 * This package provides routine for MTL_CLIENT_PARAMETERS table handler
5 * LSP installation utilities
6 * @rep:scope private
7 * @rep:product INV
8 * @rep:lifecycle active
9 * @rep:displayname MTL Client Parameters pkg
10 * @rep:category BUSINESS_ENTITY LSP
11 */
12
13
14 /* Procedure to Lock record for MTL_CLIENT_PARAMETERS table
15 */
16
17 PROCEDURE lock_row (X_Rowid VARCHAR2,
18 p_client_id NUMBER,
19 p_client_code VARCHAR2,
20 p_client_number VARCHAR2,
21 p_trading_partner_site_id NUMBER,
22 p_receipt_asn_exists_code VARCHAR2,
23 p_rma_receipt_routing_id NUMBER,
24 p_group_by_customer_flag VARCHAR2,
25 p_group_by_freight_terms_flag VARCHAR2,
26 p_group_by_fob_flag VARCHAR2,
27 p_group_by_ship_method_flag VARCHAR2,
28 p_otm_enabled VARCHAR2,
29 p_ship_confirm_rule_id NUMBER,
30 p_autocreate_del_orders_flag VARCHAR2,
31 p_delivery_report_set_id NUMBER,
32 p_lpn_prefix VARCHAR2,
33 p_lpn_suffix VARCHAR2,
34 p_ucc_128_suffix_flag VARCHAR2,
35 p_total_lpn_length NUMBER,
36 p_lpn_starting_number NUMBER,
37 p_last_update_login NUMBER
38 )
39 IS
40 CURSOR C IS
41 SELECT *
42 FROM mtl_client_parameters
43 WHERE rowid = X_Rowid
44 FOR UPDATE of client_id NOWAIT;
45 Recinfo C%ROWTYPE;
46 BEGIN
47 OPEN C;
48 FETCH C INTO Recinfo;
49 if (C%NOTFOUND) then
50 CLOSE C;
51 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
52 APP_EXCEPTION.Raise_Exception;
53 end if;
54 CLOSE C;
55 if (
56 (Recinfo.client_id = p_client_id)
57 AND (Recinfo.client_code = p_client_code)
58 AND (Recinfo.client_number = p_client_number)
59 AND ( (Recinfo.trading_partner_site_id = p_trading_partner_site_id)
60 OR ( (Recinfo.trading_partner_site_id IS NULL)
61 AND (p_trading_partner_site_id IS NULL)))
62 AND ( (Recinfo.receipt_asn_exists_code = p_receipt_asn_exists_code)
63 OR ( (Recinfo.receipt_asn_exists_code IS NULL)
64 AND (p_receipt_asn_exists_code IS NULL)))
65 AND ( (Recinfo.rma_receipt_routing_id = p_rma_receipt_routing_id)
66 OR ( (Recinfo.rma_receipt_routing_id IS NULL)
67 AND (p_rma_receipt_routing_id IS NULL)))
68 AND ( (Recinfo.group_by_customer_flag = p_group_by_customer_flag)
69 OR ( (Recinfo.group_by_customer_flag IS NULL)
70 AND (p_group_by_customer_flag IS NULL)))
71 AND ( (Recinfo.group_by_freight_terms_flag = p_group_by_freight_terms_flag)
72 OR ( (Recinfo.group_by_freight_terms_flag IS NULL)
73 AND (p_group_by_freight_terms_flag IS NULL)))
74 AND ( (Recinfo.group_by_fob_flag = p_group_by_fob_flag)
75 OR ( (Recinfo.group_by_fob_flag IS NULL)
76 AND (p_group_by_fob_flag IS NULL)))
77 AND ( (Recinfo.group_by_ship_method_flag = p_group_by_ship_method_flag)
78 OR ( (Recinfo.group_by_ship_method_flag IS NULL)
79 AND (p_group_by_ship_method_flag IS NULL)))
80 AND ( (Recinfo.otm_enabled = p_otm_enabled)
81 OR ( (Recinfo.otm_enabled IS NULL)
82 AND (p_otm_enabled IS NULL)))
83 AND ( (Recinfo.ship_confirm_rule_id = p_ship_confirm_rule_id)
84 OR ( (Recinfo.ship_confirm_rule_id IS NULL)
85 AND (p_ship_confirm_rule_id IS NULL)))
86 AND ( (Recinfo.autocreate_del_orders_flag = p_autocreate_del_orders_flag)
87 OR ( (Recinfo.autocreate_del_orders_flag IS NULL)
88 AND (p_autocreate_del_orders_flag IS NULL)))
89 AND ( (Recinfo.delivery_report_set_id = p_delivery_report_set_id)
90 OR ( (Recinfo.delivery_report_set_id IS NULL)
91 AND (p_delivery_report_set_id IS NULL)))
92 AND ( (Recinfo.lpn_prefix = p_lpn_prefix)
93 OR ( (Recinfo.lpn_prefix IS NULL)
94 AND (p_lpn_prefix IS NULL)))
95 AND ( (Recinfo.lpn_suffix = p_lpn_suffix)
96 OR ( (Recinfo.lpn_suffix IS NULL)
97 AND (p_lpn_suffix IS NULL)))
98 AND ( (Recinfo.ucc_128_suffix_flag = p_ucc_128_suffix_flag)
99 OR ( (Recinfo.ucc_128_suffix_flag IS NULL)
100 AND (p_ucc_128_suffix_flag IS NULL)))
101 AND ( (Recinfo.total_lpn_length = p_total_lpn_length)
102 OR ( (Recinfo.total_lpn_length IS NULL)
103 AND (p_total_lpn_length IS NULL)))
104 AND ( (Recinfo.lpn_starting_number = p_lpn_starting_number)
105 OR ( (Recinfo.lpn_starting_number IS NULL)
106 AND (p_lpn_starting_number IS NULL)))
107 AND ( (Recinfo.last_update_login = p_last_update_login)
108 OR ( (Recinfo.last_update_login IS NULL)
109 AND (p_last_update_login IS NULL)))
110 ) then
111 return;
112 else
113 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
114 APP_EXCEPTION.RAISE_EXCEPTION;
115 end if;
116 END Lock_Row;
117
118
119 /* Procedure to Insert record for MTL_CLIENT_PARAMETERS table
120 */
121
122 PROCEDURE insert_row (X_Rowid IN OUT NOCOPY VARCHAR2,
123 p_client_id NUMBER,
124 p_client_code VARCHAR2,
125 p_client_number VARCHAR2,
126 p_trading_partner_site_id NUMBER,
127 p_receipt_asn_exists_code VARCHAR2,
128 p_rma_receipt_routing_id NUMBER,
129 p_group_by_customer_flag VARCHAR2,
130 p_group_by_freight_terms_flag VARCHAR2,
131 p_group_by_fob_flag VARCHAR2,
132 p_group_by_ship_method_flag VARCHAR2,
133 p_otm_enabled VARCHAR2,
134 p_ship_confirm_rule_id NUMBER,
135 p_autocreate_del_orders_flag VARCHAR2,
136 p_delivery_report_set_id NUMBER,
137 p_lpn_prefix VARCHAR2,
138 p_lpn_suffix VARCHAR2,
139 p_ucc_128_suffix_flag VARCHAR2,
140 p_total_lpn_length NUMBER,
141 p_lpn_starting_number NUMBER,
142 p_attribute_category VARCHAR2,
143 p_attribute1 VARCHAR2,
144 p_attribute2 VARCHAR2,
145 p_attribute3 VARCHAR2,
146 p_attribute4 VARCHAR2,
147 p_attribute5 VARCHAR2,
148 p_attribute6 VARCHAR2,
149 p_attribute7 VARCHAR2,
150 p_attribute8 VARCHAR2,
151 p_attribute9 VARCHAR2,
152 p_attribute10 VARCHAR2,
153 p_attribute11 VARCHAR2,
154 p_attribute12 VARCHAR2,
155 p_attribute13 VARCHAR2,
156 p_attribute14 VARCHAR2,
157 p_attribute15 VARCHAR2,
158 p_last_update_date DATE,
159 p_last_updated_by NUMBER,
160 p_creation_date DATE,
161 p_created_by NUMBER,
162 p_last_update_login NUMBER
163 ) IS
164 CURSOR C IS SELECT rowid FROM mtl_client_parameters
165 WHERE client_id = p_client_id;
166
167 BEGIN
168
169 INSERT INTO mtl_client_parameters(
170 client_id,
171 client_code,
172 client_number,
173 trading_partner_site_id,
174 receipt_asn_exists_code,
175 rma_receipt_routing_id,
176 group_by_customer_flag,
177 group_by_freight_terms_flag,
178 group_by_fob_flag,
179 group_by_ship_method_flag,
180 otm_enabled,
181 ship_confirm_rule_id,
182 autocreate_del_orders_flag,
183 delivery_report_set_id,
184 lpn_prefix,
185 lpn_suffix,
186 ucc_128_suffix_flag,
187 total_lpn_length,
188 lpn_starting_number,
189 attribute_category,
190 attribute1,
191 attribute2,
192 attribute3,
193 attribute4,
194 attribute5,
195 attribute6,
196 attribute7,
197 attribute8,
198 attribute9,
199 attribute10,
200 attribute11,
201 attribute12,
202 attribute13,
203 attribute14,
204 attribute15,
205 last_update_date,
206 last_updated_by,
207 creation_date,
208 created_by,
209 last_update_login
210 )
211 VALUES (
212 p_client_id,
213 p_client_code,
214 p_client_number,
215 p_trading_partner_site_id,
216 p_receipt_asn_exists_code,
217 p_rma_receipt_routing_id,
218 p_group_by_customer_flag,
219 p_group_by_freight_terms_flag,
220 p_group_by_fob_flag,
221 p_group_by_ship_method_flag,
222 p_otm_enabled,
223 p_ship_confirm_rule_id,
224 p_autocreate_del_orders_flag,
225 p_delivery_report_set_id,
226 p_lpn_prefix,
227 p_lpn_suffix,
228 p_ucc_128_suffix_flag,
229 p_total_lpn_length,
230 p_lpn_starting_number,
231 p_attribute_category,
232 p_attribute1,
233 p_attribute2,
234 p_attribute3,
235 p_attribute4,
236 p_attribute5,
237 p_attribute6,
238 p_attribute7,
239 p_attribute8,
240 p_attribute9,
241 p_attribute10,
242 p_attribute11,
243 p_attribute12,
244 p_attribute13,
245 p_attribute14,
246 p_attribute15,
247 p_last_update_date,
248 p_last_updated_by,
249 p_creation_date,
250 p_created_by,
251 p_last_update_login);
252
253 OPEN C;
254 FETCH C INTO x_Rowid;
255 if (C%NOTFOUND) then
256 CLOSE C;
257 Raise NO_DATA_FOUND;
258 end if;
259 CLOSE C;
260 END Insert_Row;
261
262
263 /* Procedure to Update record for MTL_CLIENT_PARAMETERS table
264 */
265
266 PROCEDURE update_row (X_Rowid VARCHAR2,
267 p_client_id NUMBER,
268 p_client_code VARCHAR2,
269 p_client_number VARCHAR2,
270 p_trading_partner_site_id NUMBER,
271 p_receipt_asn_exists_code VARCHAR2,
272 p_rma_receipt_routing_id NUMBER,
273 p_group_by_customer_flag VARCHAR2,
274 p_group_by_freight_terms_flag VARCHAR2,
275 p_group_by_fob_flag VARCHAR2,
276 p_group_by_ship_method_flag VARCHAR2,
277 p_otm_enabled VARCHAR2,
278 p_ship_confirm_rule_id NUMBER,
279 p_autocreate_del_orders_flag VARCHAR2,
280 p_delivery_report_set_id NUMBER,
281 p_lpn_prefix VARCHAR2,
282 p_lpn_suffix VARCHAR2,
283 p_ucc_128_suffix_flag VARCHAR2,
284 p_total_lpn_length NUMBER,
285 p_lpn_starting_number NUMBER,
286 p_attribute_category VARCHAR2,
287 p_attribute1 VARCHAR2,
288 p_attribute2 VARCHAR2,
289 p_attribute3 VARCHAR2,
290 p_attribute4 VARCHAR2,
291 p_attribute5 VARCHAR2,
292 p_attribute6 VARCHAR2,
293 p_attribute7 VARCHAR2,
294 p_attribute8 VARCHAR2,
295 p_attribute9 VARCHAR2,
296 p_attribute10 VARCHAR2,
297 p_attribute11 VARCHAR2,
298 p_attribute12 VARCHAR2,
299 p_attribute13 VARCHAR2,
300 p_attribute14 VARCHAR2,
301 p_attribute15 VARCHAR2,
302 p_last_update_date DATE,
303 p_last_updated_by NUMBER,
304 p_creation_date DATE,
305 p_created_by NUMBER,
306 p_last_update_login NUMBER
307
308 ) IS
309
310 BEGIN
311
312 UPDATE mtl_client_parameters
313 SET
314 trading_partner_site_id = p_trading_partner_site_id,
315 receipt_asn_exists_code = p_receipt_asn_exists_code,
316 rma_receipt_routing_id = p_rma_receipt_routing_id,
317 group_by_customer_flag = p_group_by_customer_flag,
318 group_by_freight_terms_flag = p_group_by_freight_terms_flag,
319 group_by_fob_flag = p_group_by_fob_flag,
320 group_by_ship_method_flag = p_group_by_ship_method_flag,
321 otm_enabled = p_otm_enabled,
322 ship_confirm_rule_id = p_ship_confirm_rule_id,
323 autocreate_del_orders_flag = p_autocreate_del_orders_flag,
324 delivery_report_set_id = p_delivery_report_set_id,
325 lpn_prefix = p_lpn_prefix,
326 lpn_suffix = p_lpn_suffix,
327 ucc_128_suffix_flag = p_ucc_128_suffix_flag,
328 total_lpn_length = p_total_lpn_length,
329 lpn_starting_number = p_lpn_starting_number,
330 attribute_category = p_attribute_category,
331 attribute1 = p_attribute1,
332 attribute2 = p_attribute2,
333 attribute3 = p_attribute3,
334 attribute4 = p_attribute4,
338 attribute8 = p_attribute8,
335 attribute5 = p_attribute5,
336 attribute6 = p_attribute6,
337 attribute7 = p_attribute7,
339 attribute9 = p_attribute9,
340 attribute10 = p_attribute10,
341 attribute11 = p_attribute11,
342 attribute12 = p_attribute12,
343 attribute13 = p_attribute13,
344 attribute14 = p_attribute14,
345 attribute15 = p_attribute15,
346 last_update_date = p_last_update_date,
347 last_updated_by = p_last_updated_by,
348 last_update_login = p_last_update_login
349 WHERE Rowid = X_Rowid;
350
351 if (SQL%NOTFOUND) then
352 Raise NO_DATA_FOUND;
353 end if;
354
355 END update_Row;
356
357 END MTL_CLIENT_PARAMETERS_PKG;