[Home] [Help]
PACKAGE BODY: APPS.CST_PL_IMPORT
Source
1 PACKAGE BODY CST_PL_IMPORT as
2 /* $Header: CSTPLIMB.pls 120.3 2006/03/21 11:50:38 vtkamath noship $ */
3
4 PROCEDURE START_PROCESS(ERRBUF OUT NOCOPY VARCHAR2,
5 RETCODE OUT NOCOPY NUMBER ,
6 p_pl_hdr_id IN NUMBER ,
7 p_range IN NUMBER ,
8 p_item_dummy IN NUMBER ,
9 p_category_dummy IN NUMBER ,
10 p_specific_item_id IN NUMBER ,
11 p_category_set IN NUMBER ,
12 p_category_validate_flag IN VARCHAR2,
13 p_category_structure IN NUMBER ,
14 p_specific_category_id IN NUMBER ,
15 p_organization_id IN NUMBER ,
16 p_item_price_eff_date IN VARCHAR2,
17 p_based_on_rollup IN NUMBER,
18 p_ad_qp_mult IN VARCHAR2,
19 p_conv_type IN VARCHAR2,
20 p_conv_date IN VARCHAR2,
21 p_def_mtl_subelement IN NUMBER ,
22 p_group_id_dummy IN NUMBER ,
23 p_group_id IN NUMBER
24 ) as
25
26 -- p_range values interpreted as below
27 -- 1 All items
28 -- 2 Specific item
29 -- 5 category
30 --
31
32 p_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
33 p_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
34 p_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
35 p_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
36 p_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
37 p_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
38 p_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
39 p_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
40 x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
41 x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
42 x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
43 x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
44 x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
45 x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
46 x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
47 x_return_status VARCHAR2(240);
48 x_return_status_text VARCHAR2(240);
49 qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
50 line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
51 line_rec QP_PREQ_GRP.LINE_REC_TYPE;
52 rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
53
54 I BINARY_INTEGER;
55 J BINARY_INTEGER;
56 l_version VARCHAR2(240);
57 l_num NUMBER ;
58
59
60 l_stmt_num NUMBER ;
61 l_cost_organization_id NUMBER ;
62 l_org_currency_code VARCHAR2(15) ;
63 l_price_list_name VARCHAR2(240) ; --changed to 240 for a bugfix
64 l_product_attr_value VARCHAR2(240);
65 l_primary_uom_code VARCHAR2(3);
66 l_item_count NUMBER:= 0;
67 l_num_rows NUMBER;
68 l_precision NUMBER;
69 l_extended_precision NUMBER ;
70 l_item NUMBER ;
71 l_item_cost NUMBER ;
72 l_req_groupid VARCHAR2(20);
73 l_based_on_rollup NUMBER;
74 l_conversion_rate NUMBER := 1;
75 l_min_reqid NUMBER;
76 l_base_count NUMBER;
77 l_list_currency_code VARCHAR2(30);
78 l_ad_qp_mult VARCHAR2(30);
79 Conc_request BOOLEAN;
80
81 p_err_num NUMBER; -- pass back to calling program
82 p_err_msg NUMBER;
83
84 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
85 l_process_enabled_flag mtl_parameters.process_enabled_flag%TYPE;
86 l_organization_code mtl_parameters.organization_code%TYPE;
87 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
88
89 -- Asset Item list (Non-Serviceable) for which prices need to be imported
90 -- Active Check not required for header as already done at SRS and for
91 -- list line would be done by the engine
92
93 Cursor GET_PL_ITEMS(l_lst_hdr_id number,
94 l_org_id number)
95 IS
96 select distinct msi.inventory_item_id , msi.primary_uom_code
97 from qp_list_headers_vl qph ,
98 qp_list_lines qpl ,
99 qp_pricing_attributes qpa,
100 mtl_system_items_b msi
101 where qph.list_header_id = l_lst_hdr_id
102 and qph.list_type_code = 'PRL'
103 and qph.list_header_id = qpl.list_header_id
104 and qpl.list_line_type_code = 'PLL'
105 and qpa.list_line_id = qpl.list_line_id
106 and qpa.product_attribute_context = 'ITEM'
107 and qpa.product_attribute = 'PRICING_ATTRIBUTE1'
108 and msi.organization_id = l_org_id
109 and msi.inventory_item_id = qpa.product_attr_value
110 and msi.inventory_asset_flag = 'Y'
111 /* Bug 4037114 .Servie items should be excluded and not servicable items
112 and msi.serviceable_product_flag = 'N' */
113 and msi.service_item_flag = 'N'
114 and ( p_range = 1
115 OR
116 ( p_range = 2
117 AND msi.inventory_item_id = p_specific_item_id
118 )
119 OR
120 EXISTS
121 (SELECT NULL
122 FROM mtl_item_categories MIC
123 WHERE MIC.organization_id = p_organization_id
124 AND MIC.category_id = nvl(p_specific_category_id ,MIC.category_id)
125 AND MIC.category_set_id = nvl(p_category_set , -99999)
126 AND MIC.inventory_item_id = msi.inventory_item_id
127 AND p_range = 5)
128 );
129
130 Cursor GET_ITEM_COST IS
131 select /*+ ORDERED USE_NL(b) */
132 adjusted_unit_price * nvl(priced_quantity,line_quantity),b.value_from
133 from qp_preq_lines_tmp a , qp_preq_line_attrs_tmp b
134 where a.line_index = b.line_index
135 and a.pricing_status_code = 'UPDATED'
136 and b.pricing_status_code = 'X'
137 and b.context = 'ITEM'
138 and b.attribute_type = 'PRODUCT'
139 and b.attribute = 'PRICING_ATTRIBUTE1' ;
140
141
142 BEGIN
143
144 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
145 BEGIN
146 SELECT nvl(process_enabled_flag,'N')
147 , organization_code
148 INTO l_process_enabled_flag
149 , l_organization_code
150 FROM mtl_parameters
151 WHERE organization_id = p_organization_id;
152
153 IF nvl(l_process_enabled_flag,'N') = 'Y' THEN
154 FND_MESSAGE.set_name('GMF','GMF_PROCESS_ORG_ERROR');
155 FND_MESSAGE.set_token('ORGCODE', l_organization_code);
156 FND_FILE.put_line(fnd_file.log,fnd_message.get);
157 Conc_request := FND_CONCURRENT.set_completion_status('ERROR',fnd_message.get);
158 RETURN;
159 END IF;
160
161 EXCEPTION
162 WHEN no_data_found THEN
163 l_process_enabled_flag := 'N';
164 l_organization_code := NULL;
165 END;
166 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
167
168 l_stmt_num := 10 ;
169 Select name
170 into l_price_list_name
171 from qp_list_headers_vl
172 where list_header_id = p_pl_hdr_id ;
173
174 /* set the values of QP install and Multi Currency Install */
175
176 If p_ad_qp_mult IS NULL then
177 l_ad_qp_mult := 'Yes';
178 else
179 l_ad_qp_mult := 'No';
180 End If;
181
182 FND_FILE.put_line(fnd_file.log, 'Organization id : ' || to_char(p_organization_id)) ;
183 FND_FILE.put_line(fnd_file.log, 'Price list name : ' || l_price_list_name ) ;
184 FND_FILE.put_line(fnd_file.log, 'Price list id : ' || to_char(p_pl_hdr_id) ) ;
185 FND_FILE.put_line(fnd_file.log, 'Range : ' || to_char(p_range) ) ;
186 FND_FILE.put_line(fnd_file.log, 'Specific item : ' || to_char(p_specific_item_id) ) ;
187 FND_FILE.put_line(fnd_file.log, 'Category set : ' || to_char(p_category_set) ) ;
188 FND_FILE.put_line(fnd_file.log, 'Category id : ' || to_char(p_specific_category_id) ) ;
189 FND_FILE.put_line(fnd_file.log, 'Item price eff date : ' ||
190 to_char(to_date(p_item_price_eff_date , 'RR/MM/DD HH24:MI:SS'),'DD-MON-RR') );
191 FND_FILE.put_line(fnd_file.log, 'Based on rollup : ' || to_char(p_based_on_rollup));
192
193 FND_FILE.put_line(fnd_file.log, 'QP:Multi Currency Installed : ' || l_ad_qp_mult);
194
195 FND_FILE.put_line(fnd_file.log, 'Conversion Type : ' || p_conv_type);
196
197 FND_FILE.put_line(fnd_file.log, 'Conversion Date : ' || to_char(to_date(p_conv_date,'RR/MM/DD HH24:MI:SS'),'DD-MON-RR'));
198 FND_FILE.put_line(fnd_file.log, 'Deflt matl subelement: ' || to_char(p_def_mtl_subelement) ) ;
199 FND_FILE.put_line(fnd_file.log, 'Group id : ' || to_char(p_group_id) ) ;
200 FND_FILE.put_line(fnd_file.log, '') ;
201
202 /* First check for multiple simultaneously running requests with the same group ID parameter*/
203
204 l_stmt_num := 15;
205
206 Select FCR.argument18 into l_req_groupid
207 from FND_CONCURRENT_REQUESTS FCR
208 where FCR.concurrent_program_id = FND_GLOBAL.CONC_PROGRAM_ID
209 AND FCR.program_application_id = FND_GLOBAL.PROG_APPL_ID
210 AND FCR.request_id = FND_GLOBAL.CONC_REQUEST_ID;
211
212 Select min(FCR.request_id) into l_min_reqid
213 from FND_CONCURRENT_REQUESTS FCR
214 where FCR.concurrent_program_id = FND_GLOBAL.CONC_PROGRAM_ID
215 AND FCR.program_application_id = FND_GLOBAL.prog_appl_id
216 AND FCR.phase_code <> 'C'
217 AND FCR.argument14 = l_req_groupid;
218
219 l_base_count := 0;
220 select count(*) into l_base_count
221 from CST_ITEM_CST_DTLS_INTERFACE CICDI
222 where CICDI.group_id = l_req_groupid;
223
224 If ((NVL(l_min_reqid,FND_GLOBAL.CONC_REQUEST_ID) <> FND_GLOBAL.CONC_REQUEST_ID)OR (l_base_count <> 0)) then
225 fnd_file.put_line(fnd_file.log,fnd_message.get_string('BOM','CST_REQ_ERROR'));
226 Conc_request := fnd_concurrent.set_completion_status('ERROR',fnd_message.get_string('BOM','CST_REQ_ERROR'));
227 return;
228 end If;
229
230 l_stmt_num := 17;
231 /* check for NULL conversion date passed in */
232
233 If (p_ad_qp_mult IS NOT NULL) AND (p_conv_date is NULL) then
234
235 fnd_file.put_line(fnd_file.log,substrb(fnd_message.get_string('BOM','CST_NULL_CONV_DATE'),1,240));
236 Conc_request := fnd_concurrent.set_completion_status('ERROR',fnd_message.get_string('BOM','CST_NULL_CONV_DATE'));
237 return;
238
239 End If;
240
241
242 l_stmt_num := 20 ;
243 Select cost_organization_id
244 into l_cost_organization_id
245 from mtl_parameters MP
246 where MP.organization_id = p_organization_id;
247
248 If p_organization_id <> l_cost_organization_id
249 Then
250 FND_FILE.put_line(fnd_file.log,(fnd_message.get_string('BOM','CST_NOT_COSTINGORG')));
251 RETURN ;
252 End if ;
253
254 l_stmt_num := 30 ;
255 -- Bug 5023568 : Changing the query to improve performance
256 Select currency_code
257 into l_org_currency_code
258 from cst_organization_definitions
259 where organization_id = p_organization_id;
260
261 /* Set the use multi Currency feature to yes */
262
263 /* check for advanced pricing to be installed.If it is then set this
264 multi currency use feature to yes.Otherwise just dont set it and
265 get the price list currency. We then pass this price list currency
266 and get the item cost from the pricing engine and then multiply this
267 item cost with the conversion factor*/
268
269 If p_ad_qp_mult IS NULL then
270 p_control_rec.use_multi_currency := 'Y';
271 else
272
273 Select qph.currency_code into l_list_currency_code
274 from qp_list_headers_vl qph
275 where qph.list_header_id = p_pl_hdr_id
276 and qph.list_type_code = 'PRL';
277
278 End If;
279
280 /* Check for the Organization's currency. If it is diferent than the
281 price list's currency and the Advanced pricing is not installed , then
282 we will have to do the currency conversion ourselves */
283
284 l_stmt_num := 33;
285
286 If ((UPPER(l_org_currency_code) <> UPPER(l_list_currency_code)) AND
287 (p_ad_qp_mult IS NOT NULL )) then
288
289 -- fnd_file.put_line(fnd_file.log,'list code : ' || l_list_currency_code);
290 -- fnd_file.put_line(fnd_file.log,'org code :' || l_org_currency_code);
291
292 /* Bail out the Exception */
293
294 Begin
295
296 l_conversion_rate := gl_currency_api.get_rate
297 (
298 l_list_currency_code,
299 l_org_currency_code,
300 to_date(p_conv_date ,'RR/MM/DD HH24:MI:SS'),
301 p_conv_type);
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 fnd_file.put_line(fnd_file.log,substrb(fnd_message.get_string('BOM','CST_NO_VALID_CURRATE'),1,240));
306
307 Conc_request := fnd_concurrent.set_completion_status('ERROR',substrb(fnd_message.get_string('BOM','CST_NO_VALID_CURRATE'),1,240));
308 RETURN;
309
310 End;
311
312 else /* if the 2 currencies match or multi currency is enabled */
313 IF l_ad_qp_mult = 'Yes' then
314 l_conversion_rate := '' ;
315 else
316 l_conversion_rate := 1;
317 END IF ;
318 end if;
319
320 FND_FILE.PUT_LINE(fnd_file.log,'Conversion rate is : ' || to_char(l_conversion_rate) );
321 FND_FILE.put_line(fnd_file.log, '') ;
322
323 p_control_rec.pricing_event := 'PRICE'; -- discounts considered only with 'LINE'
324 p_control_rec.calculate_flag := 'Y';
325 p_control_rec.simulation_flag := 'N';
326 p_control_rec.rounding_flag := 'N'; -- rounding not needed
327
328
329 l_stmt_num := 35 ;
330 /* MOAC changes - Send the org_id for the pricing list from qp_list_headers_vl
331 If Security is OFF, the orig_org_id will be null, pricing engine will ignore the OU checks. OK.
332 If security is ON and orig_org_id is not null, it is valid for the OU passed.
333 If the PL is global, orig_org_id will be null, it should still be OK. */
334
335 Select orig_org_id
336 into p_control_rec.org_id
337 from qp_list_headers_vl
338 where list_header_id = p_pl_hdr_id ;
339
340 l_stmt_num := 40 ;
341 Open GET_PL_ITEMS( p_pl_hdr_id , p_organization_id );
342 Loop
343 Fetch GET_PL_ITEMS into l_product_attr_value ,
344 l_primary_uom_code ;
345 Exit when GET_PL_ITEMS%NOTFOUND;
346
347 l_item_count := l_item_count + 1 ;
348 --
349 l_stmt_num := 50 ;
350 line_rec.request_type_code := 'ONT'; -- May change to 'INV'in future
351 line_rec.line_id := l_item_count;
352 line_rec.line_Index := l_item_count;
353 line_rec.line_type_code := 'LINE';
354 -- ?
355 line_rec.pricing_effective_date := to_date(p_item_price_eff_date ,'RR/MM/DD HH24:MI:SS');
356 line_rec.line_quantity := 1;
357 line_rec.line_uom_code := l_primary_uom_code;
358 -- line_rec.rounding_factor := -2; -- No rounding needed by us
359
360
361 /* Check for the advanced pricing to be installed.If it is then pass the
362 organization's currency code and get the item's cost.If it is not
363 installed, then, pass the price list's currency and get the item cost */
364
365 If p_ad_qp_mult IS NULL then
366 line_rec.currency_code := l_org_currency_code ;
367 else
368 line_rec.currency_code := l_list_currency_code ;
369 end If;
370
371 line_rec.price_flag := 'Y';
372 p_line_tbl(l_item_count) := line_rec;
373
374 line_attr_rec.LINE_INDEX := l_item_count;
375 line_attr_rec.PRICING_CONTEXT := 'ITEM';
376 line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
377 line_attr_rec.PRICING_ATTR_VALUE_FROM := l_product_attr_value ; --item_id
378 p_line_attr_tbl(l_item_count) := line_attr_rec;
379
380 qual_rec.LINE_INDEX := l_item_count;
381 qual_rec.QUALIFIER_CONTEXT :='MODLIST';
382 qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
383 qual_rec.QUALIFIER_ATTR_VALUE_FROM := p_pl_hdr_id; -- PL header id
384 -- Below 'Y' per suggestion from QP team , so that qualifiers are not checked
385 qual_rec.VALIDATED_FLAG :='Y';
386 p_qual_tbl(l_item_count) := qual_rec;
387
388 END LOOP ; -- get_pl_items
389 Close get_pl_items ;
390
391 IF l_item_count = 0 Then
392 FND_FILE.put_line(fnd_file.log,'No matching items found for specified parameters') ;
393 RETURN;
394 End if ;
395
396 l_version := QP_PREQ_GRP.GET_VERSION;
397 FND_FILE.put_line( fnd_file.log , 'Testing version '|| l_version);
398
399 l_stmt_num := 60 ;
400 QP_PREQ_GRP.PRICE_REQUEST
401 (p_line_tbl,
402 p_qual_tbl,
403 p_line_attr_tbl,
404 p_line_detail_tbl,
405 p_line_detail_qual_tbl,
406 p_line_detail_attr_tbl,
407 p_related_lines_tbl,
408 p_control_rec,
409 x_line_tbl,
410 x_line_qual,
411 x_line_attr_tbl,
412 x_line_detail_tbl,
413 x_line_detail_qual_tbl,
414 x_line_detail_attr_tbl,
415 x_related_lines_tbl,
416 x_return_status,
417 x_return_status_text);
418
419 FND_FILE.put_line(fnd_file.log, 'Return status from pricing API ' || x_return_status) ;
420 FND_FILE.put_line(fnd_file.log, 'Return text from pricing API ' || x_return_status_text) ;
421
422 IF x_return_status <> 'S' Then
423 RETURN ;
424 End if ;
425
426 -- should we check line detl attr tbl or line attr table
427
428 l_stmt_num := 70 ;
429
430 /* Get the value of based on rollup flag depending on what the user has specifiedin the SRS parameter */
431
432 If p_based_on_rollup = 1 OR p_based_on_rollup = 2 then
433 l_based_on_rollup := p_based_on_rollup;
434 Else
435 l_based_on_rollup := to_number(NULL);
436 End if;
437
438
439 Open GET_ITEM_COST;
440 Loop
441 Fetch GET_ITEM_COST into l_item_cost,
442 l_item ;
443
444 Exit when GET_ITEM_COST%NOTFOUND;
445 Insert into CST_ITEM_CST_DTLS_INTERFACE (
446 Inventory_item_ID ,
447 Organization_id ,
448 Last_update_date ,
449 Last_updated_by ,
450 Creation_date ,
451 Created_by ,
452 Last_update_login ,
453 Program_id ,
454 Level_type ,
455 Cost_element_id ,
456 Resource_ID ,
457 Rollup_source_type ,
458 Request_ID ,
459 Basis_type ,
460 Usage_rate_or_amount,
461 Basis_factor ,
462 Based_on_rollup_flag,
463 Group_id ,
464 Group_description ,
465 Process_flag )
466 Values
467 (
468 l_item ,
469 p_organization_id,
470 sysdate ,
471 FND_GLOBAL.user_id,
472 sysdate ,
473 FND_GLOBAL.user_id,
474 FND_GLOBAL.user_id,
475 FND_GLOBAL.conc_program_id,
476 '1', -- This Level
477 '1', -- Material
478 p_def_mtl_subelement ,
479 '1', -- user defined
480 p_group_id ,
481 '1' , -- Item based
482 l_item_cost * nvl(l_conversion_rate, 1),
483 '1' ,
484 l_based_on_rollup,
485 p_group_id ,
486 l_price_list_name || ':'|| FND_GLOBAL.user_name
487 || ':' || to_char(sysdate , 'DD-MON-RR' ) ,
488 1
489 ) ;
490
491 If SQL%NOTFOUND then
492 FND_FILE.put_line(fnd_file.log,'Insert into interface failed for Item '
493 || l_item ) ;
494 End if ;
495
496 END LOOP;
497
498 -- Commit the changes
499 --
500 COMMIT ;
501
502 l_stmt_num := 80 ;
503 select count(*) into l_num_rows
504 from CST_ITEM_CST_DTLS_INTERFACE
505 where group_id = p_group_id ;
506
507 FND_FILE.put_line(fnd_file.log,
508 'Sucessfully inserted ' || to_char(l_num_rows)|| ' rows into CST_ITEM_CST_DTLS_INTERFACE table');
509
510 EXCEPTION
511 When Others then
512 rollback ;
513 fnd_file.put_line(fnd_file.log,'CSTPLIMB:'|| to_char(l_stmt_num) || ' '||
514 substr(SQLERRM,1,180));
515 -- CONC_REQUEST := fnd_concurrent.set_completion_status
516 -- ('ERROR',(fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')) );
517
518 END START_PROCESS ;
519
520
521 -- The below function is to be used ONLY for generating
522 -- unique group id's for import cost from price list
523 -- SRS launch form.
524
525 FUNCTION GET_GROUP_ID Return integer IS
526 --
527 l_group_id integer ;
528 BEGIN
529 --
530 select CST_LISTS_S.currval
531 into l_group_id
532 from dual ;
533 return (l_group_id) ;
534
535 EXCEPTION
536 when others then
537 return 0 ;
538 null ;
539 -- p_err_num := SQLCODE;
540 -- p_err_msg := 'CSTPLIMB:' || substrb(SQLERRM,1,150);
541 -- return -9999;
542 END GET_GROUP_ID;
543
544
545 END CST_PL_IMPORT;