[Home] [Help]
PACKAGE BODY: APPS.FTE_RATE_CHART_PKG
Source
1 PACKAGE BODY FTE_RATE_CHART_PKG AS
2 /* $Header: FTERCPKB.pls 120.9 2005/08/19 00:14:48 pkaliyam noship $ */
3 ------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_RATE_CHART_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- FUNCTIONS: CHECK_FACILITIES --
8 -- GET_PRICELIST_ID --
9 -- GET_RATE_CHART_INFO --
10 -- GET_ASSOC_MODIFIERS --
11 -- GET_ASSOC_PRICELISTS --
12 -- PROCEDURES: Reset_Region_Info --
13 -- RESET_PRICE_VALUES --
14 -- RESET_ALL --
15 -- DELETE_FROM_QP --
16 -- REPLACE_RATE_CHART --
17 -- INSERT_QP_INTERFACE_TABLES --
18 -- QP_API_CALL --
19 -- --
20 ------------------------------------------------------------------------- --
21
22 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_RATE_CHART_PKG';
23
24 TYPE LH_INT_ACTION_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.INTERFACE_ACTION_CODE%TYPE INDEX BY BINARY_INTEGER;
25 TYPE LH_LIST_TYPE_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.LIST_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
26 TYPE LH_START_DATE_ACTIVE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.START_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
27 TYPE LH_END_DATE_ACTIVE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.END_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
28 TYPE LH_DESCRIPTION_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
29 TYPE LH_CREATION_DATE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.CREATION_DATE%TYPE INDEX BY BINARY_INTEGER;
30 TYPE LH_LAST_UPDATE_DATE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
31 TYPE LH_ATTRIBUTE1_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
32
33 -- GLOBAL VARIABLES FOR QUALIFIERS
34 TYPE QL_INT_ACTION_CODE_TAB IS TABLE OF QP_INTERFACE_QUALIFIERS.INTERFACE_ACTION_CODE%TYPE INDEX BY BINARY_INTEGER;
35 TYPE QL_QUALIFIER_ATTR_VALUE_TAB IS TABLE OF QP_INTERFACE_QUALIFIERS.QUALIFIER_ATTR_VALUE%TYPE INDEX BY BINARY_INTEGER;
36 TYPE QL_QUALIFIER_ATTR_TAB IS TABLE OF QP_INTERFACE_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
37 TYPE QL_QUALIFIER_CONTEXT_TAB IS TABLE OF QP_INTERFACE_QUALIFIERS.QUALIFIER_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
38
39 TYPE LL_OPERAND_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.OPERAND%TYPE INDEX BY BINARY_INTEGER;
40 TYPE LL_COMMENTS_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.COMMENTS%TYPE INDEX BY BINARY_INTEGER;
41 TYPE LL_PRIMARY_UOM_FLAG_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.PRIMARY_UOM_FLAG%TYPE INDEX BY BINARY_INTEGER;
42 TYPE LL_PROCESS_TYPE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.PROCESS_TYPE%TYPE INDEX BY BINARY_INTEGER;
43 TYPE LL_INT_ACTION_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.INTERFACE_ACTION_CODE%TYPE INDEX BY BINARY_INTEGER;
44 TYPE LL_LIST_LINE_TYPE_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.LIST_LINE_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
45 TYPE LL_AUTOMATIC_FLAG_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.AUTOMATIC_FLAG%TYPE INDEX BY BINARY_INTEGER;
46 TYPE LL_OVERRIDE_FLAG_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.OVERRIDE_FLAG%TYPE INDEX BY BINARY_INTEGER;
47 TYPE LL_MOD_LEVEL_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.MODIFIER_LEVEL_CODE%TYPE INDEX BY BINARY_INTEGER;
48 TYPE LL_ARITHMETIC_OPERATOR_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.ARITHMETIC_OPERATOR%TYPE INDEX BY BINARY_INTEGER;
49 TYPE LL_ACCRUAL_FLAG_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.ACCRUAL_FLAG%TYPE INDEX BY BINARY_INTEGER;
50 TYPE LL_PRC_BRK_TYPE_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.PRICE_BREAK_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
51 TYPE LL_PRODUCT_PRECEDENCE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.PRODUCT_PRECEDENCE%TYPE INDEX BY BINARY_INTEGER;
52 TYPE LL_ATTRIBUTE1_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
53 TYPE LL_ATTRIBUTE2_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
54 TYPE LL_RLTD_MOD_GRP_TYPE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.RLTD_MODIFIER_GRP_TYPE%TYPE INDEX BY BINARY_INTEGER;
55 TYPE LL_CHARGE_TYPE_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.CHARGE_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
56 TYPE LL_CHARGE_SUBTYPE_CODE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.CHARGE_SUBTYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
57 TYPE LL_START_DATE_ACTIVE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.START_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
58 TYPE LL_END_DATE_ACTIVE_TAB IS TABLE OF QP_INTERFACE_LIST_LINES.END_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
59
60 -- GLOBAL VARIABLES FROM ATTRIBUTE
61 TYPE AT_PROCESS_TYPE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PROCESS_TYPE%TYPE INDEX BY BINARY_INTEGER;
62 TYPE AT_INT_ACTION_CODE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.INTERFACE_ACTION_CODE%TYPE INDEX BY BINARY_INTEGER;
63 TYPE AT_EXCLUDER_FLAG_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.EXCLUDER_FLAG%TYPE INDEX BY BINARY_INTEGER;
64 TYPE AT_PRODUCT_ATTR_CONTEXT_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRODUCT_ATTRIBUTE_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
65 TYPE AT_PRODUCT_ATTR_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRODUCT_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
66 TYPE AT_PRODUCT_ATTR_VALUE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRODUCT_ATTR_VALUE%TYPE INDEX BY BINARY_INTEGER;
67 TYPE AT_PRODUCT_UOM_CODE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRODUCT_UOM_CODE%TYPE INDEX BY BINARY_INTEGER;
68 TYPE AT_PRODUCT_ATTR_DATATYPE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRODUCT_ATTRIBUTE_DATATYPE%TYPE INDEX BY BINARY_INTEGER;
69 TYPE AT_PRICING_ATTR_DATATYPE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRICING_ATTRIBUTE_DATATYPE%TYPE INDEX BY BINARY_INTEGER;
70 TYPE AT_PRICING_ATTR_CONTEXT_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRICING_ATTRIBUTE_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
71 TYPE AT_PRICING_ATTR_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRICING_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
72 TYPE AT_COMP_OPERATOR_CODE_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.COMPARISON_OPERATOR_CODE%TYPE INDEX BY BINARY_INTEGER;
73 TYPE AT_PRICING_ATTR_VALUE_FROM_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRICING_ATTR_VALUE_FROM%TYPE INDEX BY BINARY_INTEGER;
74 TYPE AT_PRICING_ATTR_VALUE_TO_TAB IS TABLE OF QP_INTERFACE_PRICING_ATTRIBS.PRICING_ATTR_VALUE_TO%TYPE INDEX BY BINARY_INTEGER;
75
76 -------------------------------------------------------------------------------
77 --
78 -- Reset_Region_Info. Resets GLOBAL record for Region. It is used by
79 -- Origin and Destination attributes
80 -------------------------------------------------------------------------------
81 PROCEDURE Reset_Region_Info IS
82 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.RESET_REGION_INFO';
83 BEGIN
84 G_region_info.region_id := -1;
85 G_region_info.region_type := '';
86 G_region_info.country := '';
87 G_region_info.country_region := '';
88 G_region_info.state := '';
89 G_region_info.city := '';
90 G_region_info.postal_code_from := '';
91 G_region_info.postal_code_to := '';
92 G_region_info.zone := '';
93 G_region_info.zone_level := -1;
94 G_region_info.country_code := '';
95 G_region_info.country_region_code := '';
96 G_region_info.state_code := '';
97 G_region_info.city_code := '';
98
99 g_region_flag := NULL;
100 g_region_linenum := NULL;
101
102 EXCEPTION WHEN OTHERS THEN
103 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
104 p_msg => sqlerrm,
105 p_category => 'O');
106
107 RAISE;
108 END Reset_Region_Info;
109
110
111 -------------------------------------------------------------------------------
112 --
113 -- Reset_Price_Values. Resets GLOBAL Variables
114 --
115 -------------------------------------------------------------------------------
116 PROCEDURE Reset_Price_Values IS
117
118 BEGIN
119 G_Product_UOM := null;
120 G_item := null;
121 G_Process_Id := 0;
122 G_listHeaderId := 0;
123 G_listLineId := 0;
124 G_Prc_Brk_Linenum := 0;
125 G_Prc_Brk_Hdr_Index := 0;
126 G_Cur_Line_Index := 0;
127 G_Mod_Grp := 1;
128 G_previous_upper := 0;
129 LH_REPLACE_RC.DELETE;
130 LH_NEW_RC.DELETE;
131 END Reset_Price_Values;
132
133 -------------------------------------------------------------------------------
134 --
135 -- Reset_All. Resets All GLOBAL Variables
136 --
137 -------------------------------------------------------------------------------
138 PROCEDURE Reset_All IS
139 BEGIN
140 G_line_number := NULL;
141 G_region_linenum := NULL;
142 G_region_flag := NULL;
143 Reset_Region_info;
144 Reset_Price_Values;
145 END RESET_ALL;
146
147 -------------------------------------------------------------------------------
148 --
149 -- Check_Facilities: Returns -1 if there are no Facilities attached to the
150 -- rate chart, 2 otherwise.
151 -------------------------------------------------------------------------------
152 FUNCTION Check_Facilities(p_pricelist_id IN NUMBER,
153 x_status OUT NOCOPY NUMBER,
154 x_error_msg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
155
156 CURSOR facility_codes IS
157 SELECT facility_code FROM fte_location_parameters
158 WHERE modifier_list = p_pricelist_id;
159
160 l_faccodes STRINGARRAY;
161 i NUMBER;
162
163 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.CHECK_FACILITIES';
164
165 BEGIN
166 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
167
168 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
169 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List Header ID', p_pricelist_id);
170 END IF;
171
172 x_status := -1;
173
174 OPEN facility_codes;
175 FETCH facility_codes BULK COLLECT INTO l_faccodes;
176 i := facility_codes%ROWCOUNT;
177 CLOSE facility_codes;
178
179 IF ( i > 0 ) THEN
180 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_RC_ASSIGNED_TO_FC');
181 FOR j IN 1..l_faccodes.COUNT LOOP
182 x_error_msg := x_error_msg || ' '||
183 FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_FACILITY_CODE',
184 p_tokens => STRINGARRAY('CODE'),
185 p_values => STRINGARRAY(l_faccodes(j)));
186 END LOOP;
187 x_status := 2;
188
189 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
190 p_msg => x_error_msg,
191 p_category => 'F');
192
193 END IF;
194
195 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
196 RETURN x_status;
197
198 EXCEPTION WHEN OTHERS THEN
199 IF (facility_codes%ISOPEN) THEN
200 CLOSE facility_codes;
201 END IF;
202 x_error_msg := sqlerrm;
203 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
204 p_msg => x_error_msg,
205 p_category => 'O');
206 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
207 x_status := 1;
208 RETURN x_status;
209 END Check_Facilities;
210
211 -----------------------------------------------------------------------------
212 -- FUNCTION Get_Pricelist_Id
213 --
214 -- Purpose
215 -- Get the pricelist_id of pricelist, or -1 if the pricelist doesn't exist
216 --
217 -- IN Parameters
218 -- 1. p_name: The name of the pricelist.
219 -- 2. p_carrier_id: The carrier Id of the pricelist
220 -- RETURNS:
221 -- The pricelist Id, or -1 if the pricelist doesn't exist
222 -----------------------------------------------------------------------------
223 FUNCTION Get_Pricelist_Id (p_name IN VARCHAR2,
224 p_carrier_id IN NUMBER,
225 p_attribute1 OUT NOCOPY VARCHAR2) RETURN NUMBER IS
226
227 l_list_header_id NUMBER := -1;
228 l_attribute1 VARCHAR2(50);
229 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_PRICELIST_ID';
230
231 BEGIN
232 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
233
234 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
235 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Rate Chart Name', p_name);
236 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Carrier ID', p_carrier_id);
237 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Attribute1', p_attribute1);
238 END IF;
239
240 IF (p_carrier_id IS NULL) THEN
241 SELECT l.list_header_id, nvl(b.attribute1, 'FTE_RATE_CHART')
242 INTO l_list_header_id, l_attribute1
243 FROM qp_list_headers_tl l, qp_list_headers_b b
244 WHERE l.list_header_id = b.list_header_id
245 AND l.name = p_name
246 AND l.language = userenv('LANG');
247 ELSE
248 SELECT l.list_header_id, nvl(b.attribute1, 'FTE_RATE_CHART')
249 INTO l_list_header_id, l_attribute1
250 FROM qp_list_headers_tl l, qp_list_headers_b b, qp_qualifiers q
251 WHERE l.list_header_id = b.list_header_id
252 AND l.name = p_name
253 AND l.list_header_id = q.list_header_id
254 AND q.qualifier_context = 'PARTY'
255 AND q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1'
256 AND q.qualifier_attr_value = Fnd_Number.Number_To_Canonical(p_Carrier_Id)
257 AND l.language = userenv('LANG');
258 END IF;
259
260 FTE_UTIL_PKG.Exit_Debug(l_module_name);
261
262 p_attribute1 := l_attribute1;
263
264 RETURN l_list_header_id;
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 --no rate chart with this 'rate chart name' and carrier found.
268 FTE_UTIL_PKG.Exit_Debug(l_module_name);
269 RETURN -1;
270 WHEN OTHERS THEN
271 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
272 p_msg => sqlerrm,
273 p_category => 'O');
274
275 FTE_UTIL_PKG.Exit_Debug(l_module_name);
276 RAISE;
277 END Get_Pricelist_Id;
278
279 -----------------------------------------------------------------------------
280 -- FUNCTION GET_RATE_CHART_INFO
281 --
282 -- Purpose: get the rate chart list header id, start date, and end date using the name and carrier id
283 --
284 -- IN Parameters
285 -- 1. p_name: The name of the pricelist.
286 -- 2. p_carrier_id: The carrier Id of the pricelist
287 --
288 -- OUT Parameters:
289 -- 1. x_status: status, -1 if no error
290 -- 2. x_error_msg: error message if any
291 -- RETURNS:
292 -- The pricelist Id, or -1 if the pricelist doesn't exist, -2 if error
293 -----------------------------------------------------------------------------
294
295 FUNCTION GET_RATE_CHART_INFO(p_name IN VARCHAR2,
296 p_carrier_id IN NUMBER,
297 x_status OUT NOCOPY NUMBER,
298 x_error_msg OUT NOCOPY VARCHAR2) RETURN STRINGARRAY IS
299
300 l_result STRINGARRAY;
301 l_list_header_id qp_list_headers_b.list_header_id%TYPE;
302 l_name qp_list_headers_tl.name%TYPE;
303 l_start_date VARCHAR2(50);
304 l_end_date VARCHAR2(50);
305 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_RATE_CHART_INFO';
306
307 CURSOR GET_CHART_INFO(p_name IN VARCHAR2, p_carrier_id IN NUMBER) IS
308 SELECT l.list_header_id, ltl.name, to_char(l.start_date_active,'YYYY-MM-DD'), to_char(l.end_date_active,'YYYY-MM-DD')
309 FROM qp_list_headers_b l, qp_qualifiers q, qp_list_headers_tl ltl
310 WHERE ltl.name = p_name
311 AND l.list_header_id=q.list_header_id
312 AND ltl.list_header_id = l.list_header_id
313 AND q.qualifier_context = 'PARTY'
314 AND q.qualifier_attr_value = TO_CHAR(p_carrier_id)
315 AND ltl.language = userenv('LANG');
316
317 BEGIN
318 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
319
320 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
321 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Rate Chart Name', p_name);
322 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Carrier ID', p_carrier_id);
323 END IF;
324
325 x_status := -1;
326
327 OPEN GET_CHART_INFO(p_name, p_carrier_id);
328 FETCH GET_CHART_INFO INTO l_list_header_id, l_name, l_start_date, l_end_date;
329
330 IF (GET_CHART_INFO%ROWCOUNT > 0) THEN
331 l_result := STRINGARRAY(l_list_header_id, l_start_date, l_end_date);
332 END IF;
333
334 CLOSE GET_CHART_INFO;
335
336 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
337 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List header ID', l_list_header_id);
338 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Start date ', l_start_date);
339 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'End date ', l_end_date);
340 END IF;
341
342 FTE_UTIL_PKG.Exit_Debug(l_module_name);
343 RETURN l_result;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 IF (GET_CHART_INFO%ISOPEN) THEN
348 CLOSE GET_CHART_INFO;
349 END IF;
350 x_error_msg := sqlerrm;
351 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
352 p_msg => x_error_msg,
353 p_category => 'O');
354
355 FTE_UTIL_PKG.Exit_Debug(l_module_name);
356 x_status := 1;
357 RETURN NULL;
358 END GET_RATE_CHART_INFO;
359
360 -------------------------------------------------------------------------------
361 -- PROCEDURE DELETE_FROM_QP
362 --
363 -- Purpose: delete from the qp table
364 --
365 -- IN parameters:
366 -- 1. p_list_header_id: list header id
367 -- 2. p_name: name associated with list header id
368 -- 3. p_action: delete or update
369 -- 4. p_line_number: line number
370 -- 5. p_delete_qualifier: boolean for deleting qualifier when updating, default true
371 --
372 -- OUT parameters:
373 -- 1. x_status: status, -1 if no error
374 -- 2. x_error_msg: error message if any
375 -------------------------------------------------------------------------------
376 PROCEDURE DELETE_FROM_QP(p_list_header_id IN NUMBER,
377 p_name IN VARCHAR2,
378 p_action IN VARCHAR2,
379 p_line_number IN NUMBER,
380 p_delete_qualifier IN BOOLEAN DEFAULT TRUE,
381 x_status OUT NOCOPY NUMBER,
382 x_error_msg OUT NOCOPY VARCHAR2) IS
383
384 l_list_header_id NUMBER;
385 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.DELETE_FROM_QP';
386 l_attribute1 VARCHAR2(50);
387 l_tokens STRINGARRAY := STRINGARRAY();
388
389 BEGIN
390 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
391
392 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
393 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List header ID', p_list_header_id);
394 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Rate Chart Name', p_name);
395 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Action', p_action);
396 END IF;
397
398 x_status := -1;
399
400 IF (p_list_header_id IS NULL OR p_list_header_id = -1) THEN
401 l_list_header_id := Get_Pricelist_Id(p_name => p_name,
402 p_carrier_id => NULL,
403 p_attribute1 => l_attribute1);
404 IF (l_list_header_id = -1) THEN
405 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_PRICELIST_INVALID',
406 p_tokens => STRINGARRAY('NAME'),
407 p_values => STRINGARRAY(p_name));
408
409 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
410 p_msg => x_error_msg,
411 p_category => 'C',
412 p_line_number => p_line_number);
413
414 x_status := 2;
415 FTE_UTIL_PKG.Exit_Debug(l_module_name);
416 RETURN;
417 END IF;
418
419 IF (l_attribute1 <> g_chart_type OR l_attribute1 IS NULL) THEN
420 l_tokens.EXTEND;
421 l_tokens(l_tokens.COUNT) := p_name;
422 l_tokens.EXTEND;
423 IF (g_chart_type = 'FTE_RATE_CHART') THEN
424 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_RATE_CHART');
425 ELSIF (g_chart_type = 'TL_RATE_CHART') THEN
426 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_TL_RATE_CHART');
427 ELSIF (g_chart_type = 'LTL_RC') THEN
428 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_LTL_RATE_CHART');
429 ELSIF (g_chart_type = 'FAC_RATE_CHART') THEN
430 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_FAC_RATE_CHART');
431 ELSIF (g_chart_type = 'FTE_MODIFIER') THEN
432 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_CHARGES_DISCOUNTS');
433 ELSIF (g_chart_type = 'FAC_MODIFIER') THEN
434 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_FAC_CHARGES');
435 ELSIF (g_chart_type = 'TL_MODIFIER') THEN
436 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_TL_ACCESSORIALS');
437 ELSIF (g_chart_type = 'MIN_MODIFIER') THEN
438 l_tokens(l_tokens.COUNT) := 'LTL and/or Parcel Modifier';
439 ELSE
440 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_UNKNOWN_CHART');
441 END IF;
442
443 l_tokens.EXTEND;
444 IF (l_attribute1 = 'FTE_RATE_CHART') THEN
445 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_RATE_CHART');
446 ELSIF (l_attribute1 = 'TL_RATE_CHART') THEN
447 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_TL_RATE_CHART');
448 ELSIF (l_attribute1 = 'LTL_RC') THEN
449 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_LTL_RATE_CHART');
450 ELSIF (l_attribute1 = 'FAC_RATE_CHART') THEN
451 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_FAC_RATE_CHART');
452 ELSIF (l_attribute1 = 'FTE_MODIFIER') THEN
453 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_CHARGES_DISCOUNTS');
454 ELSIF (l_attribute1 = 'FAC_MODIFIER') THEN
455 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_FAC_CHARGES');
456 ELSIF (l_attribute1 = 'TL_MODIFIER') THEN
457 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_TL_ACCESSORIALS');
458 ELSIF (l_attribute1 = 'MIN_MODIFIER') THEN
459 l_tokens(l_tokens.COUNT) := 'LTL and/or Parcel Modifier';
460 ELSE
461 l_tokens(l_tokens.COUNT) := FTE_UTIL_PKG.GET_MSG('FTE_UNKNOWN_CHART');
462 END IF;
463
464 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_DELETE_TYPE_WRONG',
465 p_tokens => STRINGARRAY('NAME', 'TYPE', 'ACTUAL'),
466 p_values => l_tokens);
467
468 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
469 p_msg => x_error_msg,
470 p_category => 'D',
471 p_line_number => p_line_number);
472
473 x_status := 2;
474 FTE_UTIL_PKG.Exit_Debug(l_module_name);
475 RETURN;
476 END IF;
477
478 ELSE
479 l_list_header_id := p_list_header_id;
480 END IF;
481
482 IF (l_list_header_id = -1) THEN
483 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_PRICELIST_INVALID',
484 p_tokens => STRINGARRAY('NAME'),
485 p_values => STRINGARRAY(p_name));
486
487 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
488 p_msg => x_error_msg,
489 p_category => 'C',
490 p_line_number => p_line_number);
491
492 x_status := 2;
493 FTE_UTIL_PKG.Exit_Debug(l_module_name);
494 RETURN;
495 ELSIF (p_action = 'DELETE') THEN
496 IF (g_chart_type = 'FAC_MODIFIER') THEN
497 x_status := Check_Facilities(l_list_header_id, x_status, x_error_msg);
498 ELSIF (NOT g_is_ltl) THEN
499 --For LTL Lanes, we want to delete the rate chart even if it is assigned
500 --to Lanes. We later obsolete these lanes.
501 FTE_LANE_PKG.Check_Lanes(p_pricelist_id => l_list_header_id,
502 x_status => x_status,
503 x_error_msg => x_error_msg);
504
505 IF (x_status <> -1) THEN
506 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
507 RETURN;
508 END IF;
509
510 END IF;
511 END IF;
512
513 IF (x_status = -1) THEN
514 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
515 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleting data for Pricelist ID ' || l_list_header_id);
516 END IF;
517
518 DELETE FROM qp_pricing_attributes
519 WHERE list_header_id = l_list_header_id;
520
521 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
522 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' attributes.');
523 END IF;
524
525 delete from qp_rltd_modifiers
526 where from_rltd_modifier_id in (select list_line_id
527 from qp_list_lines
528 where list_header_id = l_list_header_id);
529
530 delete from qp_list_lines
531 where list_header_id = l_list_header_id;
532
533 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
534 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' List Lines.');
535 END IF;
536
537 --Note: For TL Rate Charts and Modifiers, we keep all the qualifiers.
538 IF (p_action = 'UPDATE' AND g_chart_type IN ('FTE_MODIFIER') AND p_delete_qualifier) THEN
539 delete from qp_qualifiers
540 where list_header_id = l_list_header_id
541 and qualifier_context <> 'PARTY';
542
543 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
544 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' qualifiers.');
545 END IF;
546 END IF;
547
548 -- UPDATE doesn't delete from Headers and Qualifiers
549 IF (p_action = 'DELETE') THEN
550 delete from qp_qualifiers
551 where list_header_id = l_list_header_id;
552
553 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
554 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' qualifiers.');
555 END IF;
556
557 delete from qp_list_headers_b
558 where list_header_id =l_list_header_id;
559
560 delete from qp_list_headers_tl
561 where list_header_id = l_list_header_id;
562
563 --For Facility Modifiers, delete stuff from fte_prc_parameters
564 IF (g_chart_type = 'FAC_MODIFIER') THEN
565 DELETE FROM fte_prc_parameters
566 WHERE list_header_id = l_list_header_id
567 AND parameter_id in (57, 58, 59, 60);
568
569 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
570 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' prc_parameters.');
571 END IF;
572
573 END IF;
574 END IF;
575 END IF;
576
577 FTE_UTIL_PKG.Exit_Debug(l_module_name);
578 EXCEPTION WHEN OTHERS THEN
579 x_error_msg := sqlerrm;
580 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
581 p_msg => x_error_msg,
582 p_category => 'O',
583 p_line_number => p_line_number);
584 x_status := 1;
585 FTE_UTIL_PKG.Exit_Debug(l_module_name);
586 RETURN;
587 END DELETE_FROM_QP;
588
589 -----------------------------------------------------------------------------
590 -- PROCEDURE Replace_Rate_Chart
591 --
592 -- Purpose: This procedure osolete the old rate chart setting the expiry_date
593 -- of the Old Rate Chart as the (effective_date -1) of the new Rate Chart.
594 -- Also, it creates a new entry into FTE_LANE_RATE_CHARTS with the info
595 -- related to the new Rate Chart
596 --
597 --
598 -- IN Parameters
599 -- 1. p_old_rate_chart_id : Rate Chart that has to be replaced.
600 -- 2. p_new_rate_chart_name : Name of the New Rate Chart
601 --
602 -- Out Parameters
603 -- 1. x_status:
604 -----------------------------------------------------------------------------
605 PROCEDURE Replace_Rate_Chart (p_old_id IN NUMBER,
606 p_new_name IN VARCHAR2,
607 x_status OUT NOCOPY VARCHAR2,
608 x_error_msg OUT NOCOPY VARCHAR2) IS
609
610 l_new_start_date DATE;
611 l_new_end_date DATE;
612 l_new_id NUMBER;
613 l_old_start_date DATE;
614 l_old_end_date DATE;
615 l_lane_rate_chart_tbl FTE_LANE_PKG.lane_rate_chart_tbl;
616 l_lane_tbl FTE_LANE_PKG.lane_tbl;
617 l_lane_commodity_tbl FTE_LANE_PKG.lane_commodity_tbl;
618 l_lane_service_tbl FTE_LANE_PKG.lane_service_tbl;
619
620 CURSOR updated_lanes IS
621 select lane_id
622 from fte_lane_rate_charts
623 where list_header_id = p_old_id;
624
625 l_lane_id NUMBER;
626 l_debug_count number;
627
628 l_overlap boolean;
629
630 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.REPLACE_RATE_CHART';
631
632 BEGIN
633
634 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
635
636 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
637 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Old Rate Chart Id', p_old_id);
638 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'New Name', p_new_name);
639 END IF;
640 x_status := -1;
641
642 BEGIN
643 SELECT hb.list_header_id, hb.start_date_active, hb.end_date_active
644 INTO l_new_id, l_new_start_date, l_new_end_date
645 FROM qp_list_headers_b hb, qp_list_headers_tl tl
646 WHERE hb.list_header_id = tl.list_header_id
647 AND tl.name = p_new_name
648 AND tl.language = userenv('LANG');
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 x_error_msg := sqlerrm;
653 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
654 p_msg => x_error_msg,
655 p_category => 'O');
656
657 x_status := 2;
658 FTE_UTIL_PKG.Exit_Debug(l_module_name);
659 RETURN;
660 END;
661
662 OPEN updated_lanes;
663 LOOP
664 FETCH updated_lanes INTO l_lane_id;
665 EXIT WHEN updated_lanes%NOTFOUND;
666
667 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
668 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Update Lane ' || l_lane_id || ' with new rate chart id ' || l_new_id);
669 END IF;
670
671 -- Update FTE_LANE_RATE_CHARTS
672 UPDATE fte_lane_rate_charts
673 SET end_date_active = LEAST(end_date_active, l_new_start_date-0.00001),
674 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
675 LAST_UPDATE_DATE = sysdate,
676 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
677 WHERE list_header_id = p_old_id
678 AND lane_id = l_lane_id;
679
680 -- Update QP_LIST_HEADERS_B
681 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
682 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Update expiry date of old rate chart');
683 END IF;
684
685 UPDATE qp_list_headers_b
686 SET end_date_active = LEAST(end_date_active, l_new_start_date-0.00001),
687 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
688 LAST_UPDATE_DATE = sysdate,
689 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
690 WHERE list_header_id = p_old_id;
691
692
693 -- I have to check if the new Rate Chart has date overlapping
694 -- with the existing rate charts attached to the same lane.
695 l_overlap := FTE_LANE_PKG.VERIFY_OVERLAPPING_DATE(p_name => p_new_name,
696 p_lane_id => l_lane_id,
697 x_status => x_status,
698 x_error_msg => x_error_msg);
699 IF (l_overlap) THEN
700 --x_error_msg := 'Rate Chart cannot be replace since the new one overlaps the existing ones';
701 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_RC_REPLACE_OVERLAP');
702 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
703 p_msg => x_error_msg,
704 p_category => 'D');
705
706 x_status := 2;
707 FTE_UTIL_PKG.Exit_Debug(l_module_name);
708 return;
709 END IF;
710
711 l_lane_rate_chart_tbl(1).lane_id := l_lane_id;
712 l_lane_rate_chart_tbl(1).list_header_id := l_new_id;
713 l_lane_rate_chart_tbl(1).start_date_active := l_new_start_date;
714 l_lane_rate_chart_tbl(1).end_date_active := l_new_end_date;
715
716 FTE_LANE_PKG.INSERT_LANE_TABLES(p_lane_tbl => l_lane_tbl,
717 p_lane_rate_chart_tbl => l_lane_rate_chart_tbl,
718 p_lane_commodity_tbl => l_lane_commodity_tbl,
719 p_lane_service_tbl => l_lane_service_tbl,
720 x_status => x_status,
721 x_error_msg => x_error_msg);
722
723 END LOOP;
724 CLOSE updated_lanes;
725 FTE_UTIL_PKG.Exit_Debug(l_module_name);
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 IF (updated_lanes%ISOPEN) THEN
730 CLOSE updated_lanes;
731 END IF;
732 x_error_msg := sqlerrm;
733 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
734 p_msg => x_error_msg,
735 p_category => 'O');
736
737 x_status := 2;
738 FTE_UTIL_PKG.Exit_Debug(l_module_name);
739 RETURN;
740 END Replace_Rate_Chart;
741
742 -----------------------------------------------------------------------------
743 -- PROCEDURE: INSERT_QP_INTERFACE_TABLES
744 --
745 -- Purpose: Transfer pricelist data from the temporary tables in memory into
746 -- QP_INTERFACE_LIST_HEADERS, QP_INTERFACE_LIST_LINES,
747 -- QP_INTERFACE_QUALIFIERS and QP_INTERFACE_PRICING_ATTRIBS.
748 -- If input parameter 'p_job_id' IS NOT NULL, then the QP api
749 -- QP_PRL_LOADER_PUB(...) is also used to load the rate chart for
750 -- that particular job id. Otherwise, the data might correspond to
751 -- several rate charts, and the QP loading is done elsewhere for all
752 -- the corresponding job ids.
753 --
754 -- IN Parameters
755 -- 1. p_qp_list_header_tbl: list header pl/sql table.
756 -- 2. p_qp_list_line_tbl: list line pl/sql table.
757 -- 3. p_qp_qualifier_tbl: qualifier pl/sql table.
758 -- 4. p_qp_pricing_attrib_tbl: pricing attributes pl/sql table.
759 --
760 -- Out Parameters
761 -- 1. x_status: -1 if successful, 2 otherwise.
762 -- 2. x_error_msg: error msg if any
763 -----------------------------------------------------------------------------
764
765 PROCEDURE INSERT_QP_INTERFACE_TABLES(p_qp_list_header_tbl IN OUT NOCOPY qp_list_header_tbl,
766 p_qp_list_line_tbl IN OUT NOCOPY qp_list_line_tbl,
767 p_qp_qualifier_tbl IN OUT NOCOPY qp_qualifier_tbl,
768 p_qp_pricing_attrib_tbl IN OUT NOCOPY qp_pricing_attrib_tbl,
769 p_qp_call IN BOOLEAN DEFAULT TRUE,
770 x_status OUT NOCOPY NUMBER,
771 x_error_msg OUT NOCOPY VARCHAR2) IS
772
773 l_status VARCHAR2(10);
774 l_sql_errors VARCHAR2(8000);
775 l_region_id NUMBER;
776 l_region_result wsh_regions_search_pkg.region_rec;
777 cnt NUMBER;
778
779 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_QP_INTERFACE_TABLES';
780
781 LH_PROCESS_ID NUMBER_TAB;
782 LH_INT_ACTION_CODE LH_INT_ACTION_CODE_TAB;
783 LH_LIST_TYPE_CODE LH_LIST_TYPE_CODE_TAB;
784 LH_START_DATE_ACTIVE LH_START_DATE_ACTIVE_TAB;
785 LH_END_DATE_ACTIVE LH_END_DATE_ACTIVE_TAB;
786 LH_CURRENCY_CODE LH_CURRENCY_CODE_TAB;
787 LH_NAME LH_NAME_TAB;
788 LH_DESCRIPTION LH_DESCRIPTION_TAB;
789 LH_LIST_HEADER_ID NUMBER_TAB;
790 LH_CREATION_DATE LH_CREATION_DATE_TAB;
791 LH_LAST_UPDATE_DATE LH_LAST_UPDATE_DATE_TAB;
792 LH_ATTRIBUTE1 LH_ATTRIBUTE1_TAB;
793 LH_CURRENCY_HEADER_ID NUMBER_TAB;
794
795 QL_PROCESS_ID NUMBER_TAB;
796 QL_INT_ACTION_CODE QL_INT_ACTION_CODE_TAB;
797 QL_QUALIFIER_ATTR_VALUE QL_QUALIFIER_ATTR_VALUE_TAB;
798 QL_QUALIFIER_GROUPING_NO NUMBER_TAB;
799 QL_QUALIFIER_CONTEXT QL_QUALIFIER_CONTEXT_TAB;
800 QL_QUALIFIER_ATTR QL_QUALIFIER_ATTR_TAB;
801
802 LL_PROCESS_ID NUMBER_TAB;
803 LL_OPERAND LL_OPERAND_TAB;
804 LL_COMMENTS LL_COMMENTS_TAB;
805 LL_LIST_LINE_NO NUMBER_TAB;
806 LL_PRIMARY_UOM_FLAG LL_PRIMARY_UOM_FLAG_TAB;
807 LL_PROCESS_TYPE LL_PROCESS_TYPE_TAB;
808 LL_INT_ACTION_CODE LL_INT_ACTION_CODE_TAB;
809 LL_LIST_LINE_TYPE_CODE LL_LIST_LINE_TYPE_CODE_TAB;
810 LL_AUTOMATIC_FLAG LL_AUTOMATIC_FLAG_TAB;
811 LL_OVERRIDE_FLAG LL_OVERRIDE_FLAG_TAB;
812 LL_MOD_LEVEL_CODE LL_MOD_LEVEL_CODE_TAB;
813 LL_ARITHMETIC_OPERATOR LL_ARITHMETIC_OPERATOR_TAB;
814 LL_ACCRUAL_FLAG LL_ACCRUAL_FLAG_TAB;
815 LL_PRC_BRK_TYPE_CODE LL_PRC_BRK_TYPE_CODE_TAB;
816 LL_PRODUCT_PRECEDENCE LL_PRODUCT_PRECEDENCE_TAB;
817 LL_PRC_BRK_HDR_IDX NUMBER_TAB;
818 LL_RLTD_MOD_GRP_NO NUMBER_TAB;
819 LL_ATTRIBUTE1 LL_ATTRIBUTE1_TAB;
820 LL_ATTRIBUTE2 LL_ATTRIBUTE2_TAB;
821 LL_RLTD_MOD_GRP_TYPE LL_RLTD_MOD_GRP_TYPE_TAB;
822 LL_PRICING_GRP_SEQUENCE NUMBER_TAB;
823 LL_PRICING_PHASE_ID NUMBER_TAB;
824 LL_QUALIFICATION_IND NUMBER_TAB;
825 LL_CHARGE_TYPE_CODE LL_CHARGE_TYPE_CODE_TAB;
826 LL_CHARGE_SUBTYPE_CODE LL_CHARGE_SUBTYPE_CODE_TAB;
827 LL_FORMULA_ID NUMBER_TAB;
828 LL_START_DATE_ACTIVE LL_START_DATE_ACTIVE_TAB;
829 LL_END_DATE_ACTIVE LL_END_DATE_ACTIVE_TAB;
830
831 AT_PROCESS_ID NUMBER_TAB;
832 AT_PROCESS_TYPE AT_PROCESS_TYPE_TAB;
833 AT_INT_ACTION_CODE AT_INT_ACTION_CODE_TAB;
834 AT_EXCLUDER_FLAG AT_EXCLUDER_FLAG_TAB;
835 AT_PRODUCT_ATTR_CONTEXT AT_PRODUCT_ATTR_CONTEXT_TAB;
836 AT_PRODUCT_ATTRIBUTE AT_PRODUCT_ATTR_TAB;
837 AT_PRODUCT_ATTR_VALUE AT_PRODUCT_ATTR_VALUE_TAB;
838 AT_PRODUCT_UOM_CODE AT_PRODUCT_UOM_CODE_TAB;
839 AT_PRODUCT_ATTR_DATATYPE AT_PRODUCT_ATTR_DATATYPE_TAB;
840 AT_PRICING_ATTR_DATATYPE AT_PRICING_ATTR_DATATYPE_TAB;
841 AT_PRICING_ATTR_CONTEXT AT_PRICING_ATTR_CONTEXT_TAB;
842 AT_PRICING_ATTRIBUTE AT_PRICING_ATTR_TAB;
843 AT_PRICING_ATTR_VALUE_FROM AT_PRICING_ATTR_VALUE_FROM_TAB;
844 AT_PRICING_ATTR_VALUE_TO AT_PRICING_ATTR_VALUE_TO_TAB;
845 AT_ATTR_GROUPING_NO NUMBER_TAB;
846 AT_COMP_OPERATOR_CODE AT_COMP_OPERATOR_CODE_TAB;
847 AT_LIST_LINE_NO NUMBER_TAB;
848 l_count NUMBER;
849
850 BEGIN
851 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
852
853 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
854 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of rate chart header', p_qp_list_header_tbl.COUNT);
855 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of rate chart line', p_qp_list_line_tbl.COUNT);
856 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of qualifier', p_qp_qualifier_tbl.COUNT);
857 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of pricing attribute', p_qp_pricing_attrib_tbl.COUNT);
858 END IF;
859 x_status := -1;
860
861 IF (p_qp_list_header_tbl.COUNT > 0) THEN
862 G_PROCESS_ID := p_qp_list_header_tbl(p_qp_list_header_tbl.FIRST).process_id;
863
864 l_count := 1;
865 FOR i IN p_qp_list_header_tbl.FIRST..p_qp_list_header_tbl.LAST LOOP
866 IF (p_qp_list_header_tbl(i).interface_action_code <> 'D') THEN
867 -- for ADD and UPDATE
868 --INSERT LIST HEADERS
869 LH_PROCESS_ID(l_count) := p_qp_list_header_tbl(i).process_id;
870 LH_INT_ACTION_CODE(l_count) := p_qp_list_header_tbl(i).interface_action_code;
871 LH_LIST_TYPE_CODE(l_count) := p_qp_list_header_tbl(i).list_type_code;
872 LH_START_DATE_ACTIVE(l_count) := p_qp_list_header_tbl(i).start_date_active;
873 LH_END_DATE_ACTIVE(l_count) := p_qp_list_header_tbl(i).end_date_active;
874 LH_CURRENCY_CODE(l_count) := p_qp_list_header_tbl(i).currency_code;
875 LH_NAME(l_count) := p_qp_list_header_tbl(i).name;
876 LH_DESCRIPTION(l_count) := p_qp_list_header_tbl(i).description;
877 LH_LIST_HEADER_ID(l_count) := p_qp_list_header_tbl(i).list_header_id;
878 LH_ATTRIBUTE1(l_count) := p_qp_list_header_tbl(i).attribute1;
879 l_count := l_count + 1;
880 END IF;
881 END LOOP;
882
883 BEGIN
884 FORALL cnt IN 1..l_count-1
885 INSERT INTO QP_INTERFACE_LIST_HEADERS ( PROCESS_ID,
886 INTERFACE_ACTION_CODE,
887 LIST_TYPE_CODE,
888 START_DATE_ACTIVE,
889 END_DATE_ACTIVE,
890 CURRENCY_CODE,
891 NAME,
892 DESCRIPTION,
893 LIST_HEADER_ID,
894 ATTRIBUTE1,
895 PROCESS_TYPE,
896 AUTOMATIC_FLAG,
897 SOURCE_SYSTEM_CODE,
898 ACTIVE_FLAG,
899 LANGUAGE,
900 SOURCE_LANG,
901 CREATION_DATE,
902 LAST_UPDATE_DATE,
903 CREATED_BY,
904 LAST_UPDATED_BY,
905 LAST_UPDATE_LOGIN)
906 VALUES(
907 LH_PROCESS_ID(cnt),
908 LH_INT_ACTION_CODE(cnt),
909 LH_LIST_TYPE_CODE(cnt),
910 LH_START_DATE_ACTIVE(cnt),
911 LH_END_DATE_ACTIVE(cnt),
912 LH_CURRENCY_CODE(cnt),
913 LH_NAME(cnt),
914 LH_DESCRIPTION(cnt),
915 LH_LIST_HEADER_ID(cnt),
916 LH_ATTRIBUTE1(cnt),
917 'SSH',
918 'Y',
919 'FTE',
920 'Y',
921 'US',
922 'US',
923 sysdate,
924 sysdate,
925 FND_GLOBAL.USER_ID,
926 FND_GLOBAL.USER_ID,
927 FND_GLOBAL.USER_ID);
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 x_error_msg := sqlerrm;
932 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting rate chart header]');
933 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
934 p_msg => x_error_msg,
935 p_category => 'O');
936
937 x_status := 1;
938 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
939 p_qp_list_header_tbl.DELETE;
940 p_qp_list_line_tbl.DELETE;
941 p_qp_qualifier_tbl.DELETE;
942 p_qp_pricing_attrib_tbl.DELETE;
943 RETURN;
944 END; --FINISH INSERTING LIST HEADERS
945 END IF;
946
947 IF (p_qp_qualifier_tbl.COUNT > 0) THEN
948 FOR i IN p_qp_qualifier_tbl.FIRST..p_qp_qualifier_tbl.LAST LOOP
949 QL_PROCESS_ID(i) := p_qp_qualifier_tbl(i).process_id;
950 QL_INT_ACTION_CODE(i) := p_qp_qualifier_tbl(i).interface_action_code;
951 QL_QUALIFIER_ATTR_VALUE(i) := p_qp_qualifier_tbl(i).qualifier_attr_value;
952 QL_QUALIFIER_GROUPING_NO(i) := p_qp_qualifier_tbl(i).qualifier_grouping_no;
953 QL_QUALIFIER_CONTEXT(i) := p_qp_qualifier_tbl(i).qualifier_context;
954 QL_QUALIFIER_ATTR(i) := p_qp_qualifier_tbl(i).qualifier_attribute;
955 END LOOP;
956
957 --INSERT QUALIFIERS
958 BEGIN
959 FORALL cnt IN p_qp_qualifier_tbl.FIRST..p_qp_qualifier_tbl.LAST
960 INSERT INTO QP_INTERFACE_QUALIFIERS( PROCESS_ID,
961 INTERFACE_ACTION_CODE,
962 QUALIFIER_ATTR_VALUE,
963 QUALIFIER_GROUPING_NO,
964 PROCESS_TYPE,
965 EXCLUDER_FLAG,
966 COMPARISON_OPERATOR_CODE,
967 QUALIFIER_CONTEXT,
968 QUALIFIER_ATTRIBUTE,
969 CREATION_DATE,
970 LAST_UPDATE_DATE,
971 CREATED_BY,
972 LAST_UPDATED_BY,
973 LAST_UPDATE_LOGIN)
974 VALUES(
975 QL_PROCESS_ID(cnt),
976 QL_INT_ACTION_CODE(cnt),
977 QL_QUALIFIER_ATTR_VALUE(cnt),
978 QL_QUALIFIER_GROUPING_NO(cnt),
979 'SSH',
980 'N',
981 '=',
982 QL_QUALIFIER_CONTEXT(cnt),
983 QL_QUALIFIER_ATTR(cnt),
984 sysdate,
985 sysdate,
986 FND_GLOBAL.USER_ID,
987 FND_GLOBAL.USER_ID,
988 FND_GLOBAL.USER_ID);
989 EXCEPTION
990 WHEN OTHERS THEN
991 x_error_msg := sqlerrm;
992 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting qualifier]');
993 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
994 p_msg => x_error_msg,
995 p_category => 'O');
996
997 x_status := 1;
998 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
999 p_qp_list_header_tbl.DELETE;
1000 p_qp_list_line_tbl.DELETE;
1001 p_qp_qualifier_tbl.DELETE;
1002 p_qp_pricing_attrib_tbl.DELETE;
1003 RETURN;
1004 END; --FINISH INSERTING QUALIFIERS
1005 END IF;
1006
1007 IF (p_qp_list_line_tbl.COUNT > 0) THEN
1008 FOR i IN p_qp_list_line_tbl.FIRST..p_qp_list_line_tbl.LAST LOOP
1009 LL_PROCESS_ID(i) := p_qp_list_line_tbl(i).process_id;
1010 LL_OPERAND(i) := p_qp_list_line_tbl(i).operand;
1011 LL_COMMENTS(i) := p_qp_list_line_tbl(i).comments;
1012 LL_LIST_LINE_NO(i) := p_qp_list_line_tbl(i).list_line_no;
1013 LL_PRIMARY_UOM_FLAG(i) := p_qp_list_line_tbl(i).primary_uom_flag;
1014 LL_PROCESS_TYPE(i) := p_qp_list_line_tbl(i).process_type;
1015 LL_INT_ACTION_CODE(i) := p_qp_list_line_tbl(i).interface_action_code;
1016 LL_LIST_LINE_TYPE_CODE(i) := p_qp_list_line_tbl(i).list_line_type_code;
1017 LL_AUTOMATIC_FLAG(i) := p_qp_list_line_tbl(i).automatic_flag;
1018 LL_OVERRIDE_FLAG(i) := p_qp_list_line_tbl(i).override_flag;
1019 LL_MOD_LEVEL_CODE(i) := p_qp_list_line_tbl(i).modifier_level_code;
1020 LL_ARITHMETIC_OPERATOR(i) := p_qp_list_line_tbl(i).arithmetic_operator;
1021 LL_ACCRUAL_FLAG(i) := p_qp_list_line_tbl(i).accrual_flag;
1022 LL_PRC_BRK_TYPE_CODE(i) := p_qp_list_line_tbl(i).price_break_type_code;
1023 LL_PRODUCT_PRECEDENCE(i) := p_qp_list_line_tbl(i).product_precedence;
1024 LL_PRC_BRK_HDR_IDX(i) := p_qp_list_line_tbl(i).price_break_header_index;
1025 LL_RLTD_MOD_GRP_NO(i) := p_qp_list_line_tbl(i).rltd_modifier_grp_no;
1026 LL_ATTRIBUTE1(i) := p_qp_list_line_tbl(i).attribute1;
1027 LL_ATTRIBUTE2(i) := p_qp_list_line_tbl(i).attribute2;
1028 LL_RLTD_MOD_GRP_TYPE(i) := p_qp_list_line_tbl(i).rltd_modifier_grp_type;
1029 LL_PRICING_GRP_SEQUENCE(i) := p_qp_list_line_tbl(i).pricing_group_sequence;
1030 LL_PRICING_PHASE_ID(i) := p_qp_list_line_tbl(i).pricing_phase_id;
1031 LL_QUALIFICATION_IND(i) := p_qp_list_line_tbl(i).qualification_ind;
1032 LL_CHARGE_TYPE_CODE(i) := p_qp_list_line_tbl(i).charge_type_code;
1033 LL_CHARGE_SUBTYPE_CODE(i) := p_qp_list_line_tbl(i).charge_subtype_code;
1034 LL_FORMULA_ID(i) := p_qp_list_line_tbl(i).price_by_formula_id;
1035 LL_START_DATE_ACTIVE(i) := p_qp_list_line_tbl(i).start_date_active;
1036 LL_END_DATE_ACTIVE(i) := p_qp_list_line_tbl(i).end_date_active;
1037 END LOOP;
1038
1039 --INSERT LINES AND BREAKS
1040 BEGIN
1041 FORALL cnt IN p_qp_list_line_tbl.FIRST..p_qp_list_line_tbl.LAST
1042 INSERT INTO QP_INTERFACE_LIST_LINES( PROCESS_ID,
1043 OPERAND,
1044 COMMENTS,
1045 LIST_LINE_NO,
1046 PRIMARY_UOM_FLAG,
1047 PROCESS_TYPE,
1048 INTERFACE_ACTION_CODE,
1049 LIST_LINE_TYPE_CODE,
1050 AUTOMATIC_FLAG,
1051 OVERRIDE_FLAG,
1052 MODIFIER_LEVEL_CODE,
1053 ARITHMETIC_OPERATOR,
1054 ACCRUAL_FLAG,
1055 PRICE_BREAK_TYPE_CODE,
1056 PRODUCT_PRECEDENCE,
1057 PRICE_BREAK_HEADER_INDEX,
1058 RLTD_MODIFIER_GRP_NO,
1059 PRICE_BY_FORMULA_ID,
1060 ATTRIBUTE1,
1061 ATTRIBUTE2,
1062 RLTD_MODIFIER_GRP_TYPE,
1063 PRICING_GROUP_SEQUENCE,
1064 PRICING_PHASE_ID,
1065 QUALIFICATION_IND,
1066 CHARGE_TYPE_CODE,
1067 CHARGE_SUBTYPE_CODE,
1068 START_DATE_ACTIVE,
1069 END_DATE_ACTIVE,
1070 CREATION_DATE,
1071 LAST_UPDATE_DATE,
1072 CREATED_BY,
1073 LAST_UPDATED_BY,
1074 LAST_UPDATE_LOGIN)
1075 VALUES(
1076 LL_PROCESS_ID(cnt),
1077 LL_OPERAND(cnt),
1078 LL_COMMENTS(cnt),
1079 LL_LIST_LINE_NO(cnt),
1080 LL_PRIMARY_UOM_FLAG(cnt),
1081 LL_PROCESS_TYPE(cnt),
1082 LL_INT_ACTION_CODE(cnt),
1083 LL_LIST_LINE_TYPE_CODE(cnt),
1084 LL_AUTOMATIC_FLAG(cnt),
1085 LL_OVERRIDE_FLAG(cnt),
1086 LL_MOD_LEVEL_CODE(cnt),
1087 LL_ARITHMETIC_OPERATOR(cnt),
1088 LL_ACCRUAL_FLAG(cnt),
1089 LL_PRC_BRK_TYPE_CODE(cnt),
1090 LL_PRODUCT_PRECEDENCE(cnt),
1091 LL_PRC_BRK_HDR_IDX(cnt),
1092 LL_RLTD_MOD_GRP_NO(cnt),
1093 LL_FORMULA_ID(cnt),
1094 LL_ATTRIBUTE1(cnt),
1095 LL_ATTRIBUTE2(cnt),
1096 LL_RLTD_MOD_GRP_TYPE(cnt),
1097 LL_PRICING_GRP_SEQUENCE(cnt),
1098 LL_PRICING_PHASE_ID(cnt),
1099 LL_QUALIFICATION_IND(cnt),
1100 LL_CHARGE_TYPE_CODE(cnt),
1101 LL_CHARGE_SUBTYPE_CODE(cnt),
1102 LL_START_DATE_ACTIVE(cnt),
1103 LL_END_DATE_ACTIVE(cnt),
1104 sysdate,
1105 sysdate,
1106 FND_GLOBAL.USER_ID,
1107 FND_GLOBAL.USER_ID,
1108 FND_GLOBAL.USER_ID);
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 x_error_msg := sqlerrm;
1112 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting rate chart lines]');
1113 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1114 p_msg => x_error_msg,
1115 p_category => 'O');
1116
1117 x_status := 1;
1118 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1119 p_qp_list_header_tbl.DELETE;
1120 p_qp_list_line_tbl.DELETE;
1121 p_qp_qualifier_tbl.DELETE;
1122 p_qp_pricing_attrib_tbl.DELETE;
1123 RETURN;
1124 END; --FINISH INSERTING LIST_LINES
1125 END IF;
1126
1127 --INSERT PRICING ATTRIBUTES
1128 --Insert any trailing region at the end of the pricelist.
1129 IF (g_region_flag IS NOT NULL AND g_process_id IS NOT NULL) THEN
1130 l_region_id := FTE_REGION_ZONE_LOADER.Get_Region_ID(p_region_info => G_region_info);
1131
1132 IF (x_status <> -1) THEN
1133 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1134 p_qp_list_header_tbl.DELETE;
1135 p_qp_list_line_tbl.DELETE;
1136 p_qp_qualifier_tbl.DELETE;
1137 p_qp_pricing_attrib_tbl.DELETE;
1138 RETURN;
1139 END IF;
1140
1141 IF (l_region_id <> -1) THEN
1142 FTE_VALIDATION_PKG.ADD_Attribute(p_pricing_attribute => (g_region_flag || '_ZONE'),
1143 p_attr_value_from => l_region_id,
1144 p_attr_value_to => NULL,
1145 p_line_number => G_region_linenum,
1146 p_context => g_region_context,
1147 p_comp_operator => NULL,
1148 p_qp_pricing_attrib_tbl => p_qp_pricing_attrib_tbl,
1149 x_status => x_status,
1150 x_error_msg => x_error_msg);
1151 ELSE
1152 x_status := 2;
1153 x_error_msg := Fte_Util_PKG.Get_Msg(p_name => 'FTE_CAT_REGION_UNKNOWN',
1154 p_tokens => STRINGARRAY('NAME'),
1155 p_values => STRINGARRAY(g_region_flag));
1156
1157 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name, --check
1158 p_msg => x_error_msg,
1159 p_category => 'D');
1160
1161 reset_region_info;
1162 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1163 p_qp_list_header_tbl.DELETE;
1164 p_qp_list_line_tbl.DELETE;
1165 p_qp_qualifier_tbl.DELETE;
1166 p_qp_pricing_attrib_tbl.DELETE;
1167 return;
1168 END IF;
1169 END IF;
1170
1171 IF (p_qp_pricing_attrib_tbl.COUNT > 0) THEN
1172 FOR i IN p_qp_pricing_attrib_tbl.FIRST..p_qp_pricing_attrib_tbl.LAST LOOP
1173 AT_PROCESS_ID(i) := p_qp_pricing_attrib_tbl(i).process_id;
1174 AT_PROCESS_TYPE(i) := p_qp_pricing_attrib_tbl(i).process_type;
1175 AT_INT_ACTION_CODE(i) := p_qp_pricing_attrib_tbl(i).interface_action_code;
1176 AT_EXCLUDER_FLAG(i) := p_qp_pricing_attrib_tbl(i).excluder_flag;
1177 AT_PRODUCT_ATTR_CONTEXT(i) := p_qp_pricing_attrib_tbl(i).product_attribute_context;
1178 AT_PRODUCT_ATTRIBUTE(i) := p_qp_pricing_attrib_tbl(i).product_attribute;
1179 AT_PRODUCT_ATTR_VALUE(i) := p_qp_pricing_attrib_tbl(i).product_attr_value;
1180 AT_PRODUCT_UOM_CODE(i) := p_qp_pricing_attrib_tbl(i).product_uom_code;
1181 AT_PRODUCT_ATTR_DATATYPE(i) := p_qp_pricing_attrib_tbl(i).product_attribute_datatype;
1182 AT_PRICING_ATTR_DATATYPE(i) := p_qp_pricing_attrib_tbl(i).pricing_attribute_datatype;
1183 AT_PRICING_ATTR_CONTEXT(i) := p_qp_pricing_attrib_tbl(i).pricing_attribute_context;
1184 AT_PRICING_ATTRIBUTE(i) := p_qp_pricing_attrib_tbl(i).pricing_attribute;
1185 AT_PRICING_ATTR_VALUE_FROM(i) := p_qp_pricing_attrib_tbl(i).pricing_attr_value_from;
1186 AT_PRICING_ATTR_VALUE_TO(i) := p_qp_pricing_attrib_tbl(i).pricing_attr_value_to;
1187 AT_ATTR_GROUPING_NO(i) := p_qp_pricing_attrib_tbl(i).attribute_grouping_no;
1188 AT_COMP_OPERATOR_CODE(i) := p_qp_pricing_attrib_tbl(i).comparison_operator_code;
1189 AT_LIST_LINE_NO(i) := p_qp_pricing_attrib_tbl(i).list_line_no;
1190 END LOOP;
1191
1192 BEGIN
1193 FORALL cnt IN p_qp_pricing_attrib_tbl.FIRST..p_qp_pricing_attrib_tbl.LAST
1194 INSERT INTO QP_INTERFACE_PRICING_ATTRIBS(PROCESS_ID,
1195 PROCESS_TYPE,
1196 INTERFACE_ACTION_CODE,
1197 EXCLUDER_FLAG,
1198 PRODUCT_ATTRIBUTE_CONTEXT,
1199 PRODUCT_ATTRIBUTE,
1200 PRODUCT_ATTR_VALUE,
1201 PRODUCT_UOM_CODE,
1202 PRODUCT_ATTRIBUTE_DATATYPE,
1203 PRICING_ATTRIBUTE_DATATYPE,
1204 PRICING_ATTRIBUTE_CONTEXT,
1205 PRICING_ATTRIBUTE,
1206 PRICING_ATTR_VALUE_FROM,
1207 PRICING_ATTR_VALUE_TO,
1208 ATTRIBUTE_GROUPING_NO,
1209 COMPARISON_OPERATOR_CODE,
1210 LIST_LINE_NO,
1211 CREATION_DATE,
1212 LAST_UPDATE_DATE,
1213 CREATED_BY,
1214 LAST_UPDATED_BY,
1215 LAST_UPDATE_LOGIN)
1216 VALUES(
1217 AT_PROCESS_ID(cnt),
1218 AT_PROCESS_TYPE(cnt),
1219 AT_INT_ACTION_CODE(cnt),
1220 AT_EXCLUDER_FLAG(cnt),
1221 AT_PRODUCT_ATTR_CONTEXT(cnt),
1222 AT_PRODUCT_ATTRIBUTE(cnt),
1223 AT_PRODUCT_ATTR_VALUE(cnt),
1224 AT_PRODUCT_UOM_CODE(cnt),
1225 AT_PRODUCT_ATTR_DATATYPE(cnt),
1226 AT_PRICING_ATTR_DATATYPE(cnt),
1227 AT_PRICING_ATTR_CONTEXT(cnt),
1228 AT_PRICING_ATTRIBUTE(cnt),
1229 AT_PRICING_ATTR_VALUE_FROM(cnt),
1230 AT_PRICING_ATTR_VALUE_TO(cnt),
1231 AT_ATTR_GROUPING_NO(cnt),
1232 AT_COMP_OPERATOR_CODE(cnt),
1233 AT_LIST_LINE_NO(cnt),
1234 sysdate,
1235 sysdate,
1236 FND_GLOBAL.USER_ID,
1237 FND_GLOBAL.USER_ID,
1238 FND_GLOBAL.USER_ID);
1239
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 x_error_msg := sqlerrm;
1243 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting attributes]');
1244 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1245 p_msg => x_error_msg,
1246 p_category => 'O');
1247 x_status := 1;
1248 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1249 p_qp_list_header_tbl.DELETE;
1250 p_qp_list_line_tbl.DELETE;
1251 p_qp_qualifier_tbl.DELETE;
1252 p_qp_pricing_attrib_tbl.DELETE;
1253 RETURN;
1254 END; --FINISH INSERTING PRICING_ATTRIBS
1255 END IF;
1256
1257 -- temp fix for facility call with only qualifier
1258 IF (p_qp_list_header_tbl.COUNT = 0) THEN
1259 g_process_id := p_qp_qualifier_tbl(p_qp_qualifier_tbl.FIRST).process_id;
1260 END IF;
1261
1262 IF (G_PROCESS_ID IS NOT NULL AND p_qp_call) THEN
1263 QP_API_CALL(p_chart_type => g_chart_type,
1264 p_process_id => g_process_id,
1265 p_name => LH_NAME,
1266 p_currency => LH_CURRENCY_CODE,
1267 x_status => x_status,
1268 x_error_msg => x_error_msg);
1269
1270 IF (x_status <> -1) THEN
1271 p_qp_list_header_tbl.DELETE;
1272 p_qp_list_line_tbl.DELETE;
1273 p_qp_qualifier_tbl.DELETE;
1274 p_qp_pricing_attrib_tbl.DELETE;
1275 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1276 return;
1277 END IF;
1278 --+
1279 -- For Generating Output file
1280 --+
1281 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1282 p_msg_name => 'FTE_RATECHARTS_LOADED',
1283 p_category => NULL);
1284
1285 FOR i in LH_NAME.FIRST..LH_NAME.LAST LOOP
1286
1287 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1288 p_msg => LH_NAME(i),
1289 p_category => NULL);
1290 END LOOP;
1291
1292 END IF; --if job_id is not null
1293
1294 Reset_All; --Reset all global variables
1295
1296 p_qp_list_header_tbl.DELETE;
1297 p_qp_list_line_tbl.DELETE;
1298 p_qp_qualifier_tbl.DELETE;
1299 p_qp_pricing_attrib_tbl.DELETE;
1300 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1301 EXCEPTION WHEN OTHERS THEN
1302 -- IF (GET_CURRENCY_HEADER_ID%ISOPEN) THEN
1303 -- CLOSE GET_CURRENCY_HEADER_ID;
1304 -- END IF;
1305 x_error_msg := sqlerrm;
1306 x_status := 2;
1307 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1308 p_msg => x_error_msg,
1309 p_category => 'O');
1310
1311 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1312 p_qp_list_header_tbl.DELETE;
1313 p_qp_list_line_tbl.DELETE;
1314 p_qp_qualifier_tbl.DELETE;
1315 p_qp_pricing_attrib_tbl.DELETE;
1316 RETURN;
1317 END INSERT_QP_INTERFACE_TABLES;
1318
1319 -----------------------------------------------------------------------------
1320 -- FUNCTION Get_Assoc_Modifiers
1321 --
1322 -- Purpose Get the list of modifiers associated with the rate chart
1323 -- using either the rate chart's name or List Header ID.
1324 --
1325 -- IN Parameters
1326 -- 1. p_list_header_id:
1327 -- 2. p_pricelist_name:
1328 --
1329 -- RETURN
1330 -- 1. STRINGARRAY: A list of associated modifier IDs.
1331 -----------------------------------------------------------------------------
1332 FUNCTION Get_Assoc_Modifiers(p_list_header_id IN NUMBER,
1333 p_pricelist_name IN VARCHAR2)
1334 RETURN STRINGARRAY IS
1335
1336 l_mod_ids STRINGARRAY;
1337 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_ASSOC_MODIFIERS';
1338
1339 CURSOR Get_Modifiers_With_Name IS
1340 SELECT modc.list_header_id
1341 FROM qp_list_headers_tl rc, qp_list_headers_b b,
1342 qp_list_headers_tl modc, qp_list_headers_b b2,
1343 qp_qualifiers mod_qual
1344 WHERE rc.list_header_id = b.list_header_id
1345 AND modc.list_header_id = b2.list_header_id
1346 AND mod_qual.qualifier_context = 'MODLIST'
1347 AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
1348 AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
1349 AND mod_qual.list_header_id = modc.list_header_id
1350 AND rc.name = p_pricelist_name
1351 AND rc.language = userenv('LANG')
1352 AND modc.language = userenv('LANG')
1353 ORDER BY rc.creation_date DESC;
1354
1355 CURSOR Get_Modifiers_With_ID IS
1356 SELECT modc.list_header_id
1357 FROM qp_list_headers_tl rc, qp_list_headers_b b,
1358 qp_list_headers_tl modc, qp_list_headers_b b2,
1359 qp_qualifiers mod_qual
1360 WHERE rc.list_header_id = b.list_header_id
1361 AND modc.list_header_id = b2.list_header_id
1362 AND mod_qual.qualifier_context = 'MODLIST'
1363 AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
1364 AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
1365 AND mod_qual.list_header_id = modc.list_header_id
1366 AND rc.list_header_id = p_list_header_id
1367 AND rc.language = userenv('LANG')
1368 AND modc.language = userenv('LANG')
1369 ORDER BY rc.creation_date DESC;
1370
1371 BEGIN
1372 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1373
1374 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1375 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List Header ID', p_list_header_id);
1376 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Rate Chart Name', p_pricelist_name);
1377 END IF;
1378
1379 IF (p_list_header_id IS NOT NULL) THEN
1380 OPEN Get_Modifiers_With_ID;
1381 FETCH Get_Modifiers_With_ID BULK COLLECT INTO l_mod_ids;
1382 CLOSE Get_Modifiers_With_ID;
1383 ELSIF (p_pricelist_name IS NOT NULL) THEN
1384 OPEN Get_Modifiers_With_Name;
1385 FETCH Get_Modifiers_With_Name BULK COLLECT INTO l_mod_ids;
1386 CLOSE Get_Modifiers_With_Name;
1387 END IF;
1388 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1389 return l_mod_ids;
1390 EXCEPTION WHEN OTHERS THEN
1391 IF (Get_Modifiers_With_Name%ISOPEN) THEN
1392 CLOSE Get_Modifiers_With_Name;
1393 END IF;
1394 IF (Get_Modifiers_With_ID%ISOPEN) THEN
1395 CLOSE Get_Modifiers_With_ID;
1396 END IF;
1397 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1398 p_msg => sqlerrm,
1399 p_category => 'O');
1400
1401 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1402 RAISE;
1403 END Get_Assoc_Modifiers;
1404
1405 -----------------------------------------------------------------------------
1406 -- FUNCTION GET_ASSOC_PRICELISTS
1407 --
1408 -- Purpose Get the list of pricelists associated with the modifier using
1409 -- either the rate chart's name or List Header ID.
1410 --
1411 -- IN Parameters
1412 -- 1. p_list_header_id:
1413 -- 2. p_modifier_name:
1414 --
1415 -- RETURN
1416 -- 1. STRINGARRAY: A list of associated pricelist IDs.
1417 -----------------------------------------------------------------------------
1418 FUNCTION GET_ASSOC_PRICELISTS (p_list_header_id IN NUMBER,
1419 p_modifier_name IN VARCHAR2)
1420 RETURN STRINGARRAY IS
1421
1422 l_mod_ids STRINGARRAY;
1423 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_ASSOC_PRICELISTS';
1424
1425 CURSOR Get_Pricelists_With_Name IS
1426 SELECT rc.list_header_id
1427 FROM qp_list_headers_tl rc, qp_list_headers_b b,
1428 qp_list_headers_tl modc, qp_list_headers_b b2,
1429 qp_qualifiers mod_qual
1430 WHERE rc.list_header_id = b.list_header_id
1431 AND modc.list_header_id = b2.list_header_id
1432 AND mod_qual.qualifier_context = 'MODLIST'
1433 AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
1434 AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
1435 AND mod_qual.list_header_id = modc.list_header_id
1436 AND modc.name = p_modifier_name
1437 AND rc.language = userenv('LANG')
1438 AND modc.language = userenv('LANG')
1439 ORDER BY rc.creation_date DESC;
1440
1441 CURSOR Get_Pricelists_With_ID IS
1442 SELECT rc.list_header_id
1443 FROM qp_list_headers_tl rc, qp_list_headers_b b,
1444 qp_list_headers_tl modc, qp_list_headers_b b2,
1445 qp_qualifiers mod_qual
1446 WHERE rc.list_header_id = b.list_header_id
1447 AND modc.list_header_id = b2.list_header_id
1448 AND mod_qual.qualifier_context = 'MODLIST'
1449 AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
1450 AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
1451 AND mod_qual.list_header_id = modc.list_header_id
1452 AND modc.name = p_modifier_name
1453 AND rc.language = userenv('LANG')
1454 AND modc.language = userenv('LANG')
1455 ORDER BY rc.creation_date DESC;
1456
1457
1458 BEGIN
1459 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1460
1461 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1462 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List Header ID', p_list_header_id);
1463 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Modifier Name', p_modifier_name);
1464 END IF;
1465
1466 IF (p_list_header_id IS NOT NULL) THEN
1467 OPEN Get_Pricelists_With_ID;
1468 FETCH Get_Pricelists_With_ID BULK COLLECT INTO l_mod_ids;
1469 CLOSE Get_Pricelists_With_ID;
1470 ELSIF (p_modifier_name IS NOT NULL) THEN
1471 OPEN Get_Pricelists_With_Name;
1472 FETCH Get_Pricelists_With_Name BULK COLLECT INTO l_mod_ids;
1473 CLOSE Get_Pricelists_With_Name;
1474 END IF;
1475
1476 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1477 return L_MOD_IDS;
1478 EXCEPTION WHEN OTHERS THEN
1479 IF (Get_Pricelists_With_Name%ISOPEN) THEN
1480 CLOSE Get_Pricelists_With_Name;
1481 END IF;
1482 IF (Get_Pricelists_With_ID%ISOPEN) THEN
1483 CLOSE Get_Pricelists_With_ID;
1484 END IF;
1485 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1486 p_msg => sqlerrm,
1487 p_category => 'O');
1488
1489 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1490 RAISE;
1491 END GET_ASSOC_PRICELISTS;
1492
1493
1494 -----------------------------------------------------------------------------
1495 -- PROCEDURE QP_API_CALL
1496 --
1497 -- Purpose Call the qp api for loading pricelist and modlist
1498 --
1499 -- IN Parameters
1500 -- 1. p_chart_type: type of the load, pricelist or modlist
1501 -- 2. p_process_id: process id to load
1502 -- 3. p_name: name of the chart
1503 -- 4. p_currency: currency of the chart
1504 --
1505 -- OUT paramters
1506 -- 1. x_status: status, -1 for no error
1507 -- 2. x_error_msg: error message if any.
1508 -----------------------------------------------------------------------------
1509
1510 PROCEDURE QP_API_CALL(p_chart_type IN VARCHAR2,
1511 p_process_id IN NUMBER,
1512 p_name IN LH_NAME_TAB,
1513 p_currency IN LH_CURRENCY_CODE_TAB,
1514 x_status OUT NOCOPY NUMBER,
1515 x_error_msg OUT NOCOPY VARCHAR2) IS
1516 l_status VARCHAR2(10);
1517 l_sql_errors VARCHAR2(8000);
1518 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.QP_API_CALL';
1519
1520 l_list_header_ids STRINGARRAY := STRINGARRAY();
1521 l_return_status VARCHAR2(1);
1522 x_msg_count number;
1523 x_msg_data Varchar2(2000);
1524 x_msg_index number;
1525
1526 l_CURR_LISTS_rec QP_Currency_PUB.Curr_Lists_Rec_Type;
1527 l_CURR_LISTS_val_rec QP_Currency_PUB.Curr_Lists_Val_Rec_Type;
1528 l_CURR_DETAILS_tbl QP_Currency_PUB.Curr_Details_Tbl_Type;
1529 l_CURR_DETAILS_val_tbl QP_Currency_PUB.Curr_Details_Val_Tbl_Type;
1530
1531 l_currency_header_id NUMBER;
1532 l_result VARCHAR2(10);
1533
1534 CURSOR GET_CURRENCY_HEADER_ID(p_list_header_id IN NUMBER) IS
1535 SELECT currency_header_id
1536 FROM qp_list_headers
1537 WHERE list_header_id = p_list_header_id;
1538
1539 CURSOR CONVERSION_EXIST(p_currency_header_id IN NUMBER, p_currency IN VARCHAR2) IS
1540 SELECT 'TRUE'
1541 FROM qp_currency_details
1542 WHERE currency_header_id = p_currency_header_id
1543 AND to_currency_code = p_currency;
1544
1545 CURSOR GET_MOD_PRICELIST(p_name IN VARCHAR2) IS
1546 SELECT to_char(b.list_header_id)
1547 FROM qp_list_headers_tl lh,
1548 qp_list_headers_b b,
1549 qp_qualifiers qc,
1550 qp_qualifiers qs,
1551 qp_qualifiers qm,
1552 qp_list_headers_tl modlh,
1553 qp_qualifiers modqs,
1554 qp_qualifiers modqc
1555 WHERE modlh.name = p_name AND
1556 modlh.list_header_id = modqs.list_header_id AND
1557 modqc.list_header_id = modlh.list_header_id AND
1558 modqs.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' AND
1559 modqs.qualifier_context = 'LOGISTICS' AND
1560 modqc.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
1561 modqc.qualifier_context = 'PARTY' AND
1562 lh.list_header_id = b.list_header_id AND
1563 qc.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
1564 qc.qualifier_context = 'PARTY' AND
1565 qc.qualifier_attr_value = modqc.qualifier_attr_value AND
1566 qc.list_header_id = lh.list_header_id AND
1567 qs.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' AND
1568 qs.qualifier_context = 'LOGISTICS' AND
1569 qs.qualifier_attr_value = modqs.qualifier_attr_value AND
1570 qs.list_header_id = qc.list_header_id AND
1571 qm.qualifier_attribute = 'QUALIFIER_ATTRIBUTE7' AND
1572 qm.qualifier_context = 'LOGISTICS' AND
1573 qm.qualifier_attr_value = 'TRUCK' AND
1574 qm.list_header_id = qc.list_header_id AND
1575 b.attribute1 = 'TL_RATE_CHART' AND
1576 lh.language = userenv('LANG');
1577
1578 BEGIN
1579 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1580
1581 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1582 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Type of Chart', p_chart_type);
1583 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Process ID', p_process_id);
1584 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Chart Name', p_name.COUNT);
1585 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Currency', p_currency.COUNT);
1586 END IF;
1587 x_status := -1;
1588
1589 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1590 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Calling QP_PRL_LOADER_PUB.Load_Price_List');
1591 END IF;
1592
1593 FND_PROFILE.PUT('QP_PRICING_TRANSACTION_ENTITY', 'LOGSTX');
1594 FND_PROFILE.PUT('QP_SOURCE_SYSTEM_CODE', 'FTE');
1595
1596 IF (p_chart_type LIKE '%RATE_CHART%') THEN
1597 QP_PRL_LOADER_PUB.Load_Price_List(p_process_id => p_process_id,
1598 p_req_type_code => 'FTE',
1599 x_status => l_status,
1600 x_errors => l_sql_errors);
1601
1602 ELSIF (p_chart_type LIKE '%MODIFIER%') THEN
1603 QP_MOD_LOADER_PUB.Load_Mod_List(p_process_id => p_process_id,
1604 p_req_type_code => 'FTE',
1605 x_status => l_status,
1606 x_errors => l_sql_errors);
1607
1608 ELSE
1609 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_CHART_TYPE_NULL');
1610 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1611 p_msg => x_error_msg,
1612 p_category => 'O');
1613
1614 x_status := 2;
1615 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1616 RETURN;
1617
1618 END IF;
1619
1620 --check for errors
1621 IF (l_status <> 'COMPLETED') THEN
1622 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_QP_ERROR',
1623 p_tokens => STRINGARRAY('STATUS', 'ERROR'),
1624 p_values => STRINGARRAY(l_status, substr(l_sql_errors, 0, 700)));
1625
1626 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1627 p_msg => x_error_msg,
1628 p_category => 'O');
1629
1630 x_status := 2;
1631 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1632 ELSE
1633
1634 --For Parcel Rate Charts. Assumes that there is only one rate
1635 --chart in the spread sheet. **
1636 IF (LH_REPLACE_RC.COUNT > 0) THEN
1637 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1638 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'REPLACING RATE CHART ');
1639 END IF;
1640
1641 Replace_Rate_Chart(p_old_id => LH_REPLACE_RC(1),
1642 p_new_name => LH_NEW_RC(1),
1643 x_status => x_status,
1644 x_error_msg => x_error_msg);
1645
1646 IF (x_status <> -1) THEN
1647 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1648 RETURN;
1649 END IF;
1650
1651 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1652 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, LH_REPLACE_RC(1) || ' => ' || LH_NEW_RC(1));
1653 END IF;
1654 END IF;
1655 END IF;
1656
1657 IF (p_chart_type LIKE '%MODIFIER%') THEN
1658 -- find out the type of modifier and the currency list header
1659 FOR j IN p_name.FIRST..p_name.LAST LOOP
1660 IF (p_chart_type = 'TL_MODIFIER') THEN
1661 OPEN GET_MOD_PRICELIST(p_name => p_name(j));
1662 FETCH GET_MOD_PRICELIST BULK COLLECT INTO l_list_header_ids;
1663 CLOSE GET_MOD_PRICELIST;
1664 ELSE
1665 l_list_header_ids := GET_ASSOC_PRICELISTS (p_list_header_id => NULL,
1666 p_modifier_name => p_name(j));
1667 END IF;
1668
1669 IF (l_list_header_ids IS NOT NULL AND l_list_header_ids.COUNT > 0) THEN
1670 FOR i IN l_list_header_ids.FIRST..l_list_header_ids.LAST LOOP
1671 OPEN GET_CURRENCY_HEADER_ID(l_list_header_ids(i));
1672 FETCH GET_CURRENCY_HEADER_ID INTO l_currency_header_id;
1673 CLOSE GET_CURRENCY_HEADER_ID;
1674
1675 IF (l_currency_header_id IS NULL) THEN
1676 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_MULTI_CURR_DISABLED');
1677 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1678 p_msg => x_error_msg,
1679 p_category => 'B');
1680 x_status := 2;
1681 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1682 RETURN;
1683 END IF;
1684
1685 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1686 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Currency Header Id', l_currency_header_id);
1687 END IF;
1688
1689 OPEN CONVERSION_EXIST(l_currency_header_id, p_currency(j));
1690 FETCH CONVERSION_EXIST INTO l_result;
1691 CLOSE CONVERSION_EXIST;
1692
1693 IF (l_result IS NULL) THEN --no conversion
1694 l_CURR_LISTS_rec.currency_header_id := l_currency_header_id;
1695 l_CURR_LISTS_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
1696
1697 -- Create Multi-Currency Details for Currency
1698
1699 l_CURR_DETAILS_tbl(1).to_currency_code := p_currency(j);
1700 l_CURR_DETAILS_tbl(1).conversion_type := 'Corporate';
1701 l_CURR_DETAILS_tbl(1).conversion_date_type := 'PRICING_EFFECTIVITY_DATE';
1702 l_CURR_DETAILS_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
1703 l_CURR_DETAILS_tbl(1).selling_rounding_factor := -2;
1704
1705 -- Call the Multi-Currency Conversion Public API to create the header and lines
1706 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1707 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Calling Process Currency');
1708 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Currency', p_currency(j));
1709 END IF;
1710
1711 QP_Currency_PUB.Process_Currency( p_api_version_number => 1.0
1712 , x_return_status => l_return_status
1713 , x_msg_count =>x_msg_count
1714 , x_msg_data =>x_msg_data
1715 , p_CURR_LISTS_rec => l_CURR_LISTS_rec
1716 , p_CURR_LISTS_val_rec => l_CURR_LISTS_val_rec
1717 , p_CURR_DETAILS_tbl => l_CURR_DETAILS_tbl
1718 , p_CURR_DETAILS_val_tbl => l_CURR_DETAILS_val_tbl
1719 , x_CURR_LISTS_rec => l_CURR_LISTS_rec
1720 , x_CURR_LISTS_val_rec => l_CURR_LISTS_val_rec
1721 , x_CURR_DETAILS_tbl => l_CURR_DETAILS_tbl
1722 , x_CURR_DETAILS_val_tbl => l_CURR_DETAILS_val_tbl
1723 );
1724
1725
1726 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1727 x_status := 2;
1728 x_error_msg := x_msg_data;
1729 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1730 p_msg => x_error_msg,
1731 p_category => 'O');
1732 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1733 RETURN;
1734 END IF;
1735
1736 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1737 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Process Currency successfully');
1738 END IF;
1739
1740 END IF;
1741 END LOOP;
1742 END IF;
1743 END LOOP;
1744 END IF;
1745
1746
1747 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 IF (GET_CURRENCY_HEADER_ID%ISOPEN) THEN
1751 CLOSE GET_CURRENCY_HEADER_ID;
1752 END IF;
1753
1754 IF (CONVERSION_EXIST%ISOPEN) THEN
1755 CLOSE CONVERSION_EXIST;
1756 END IF;
1757
1758 IF (GET_MOD_PRICELIST%ISOPEN) THEN
1759 CLOSE GET_MOD_PRICELIST;
1760 END IF;
1761
1762 x_error_msg := sqlerrm;
1763 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1764 p_msg => x_error_msg,
1765 p_category => 'O');
1766
1767 x_status := 2;
1768 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1769 END QP_API_CALL;
1770
1771 END FTE_RATE_CHART_PKG;