[Home] [Help]
PACKAGE BODY: APPS.ASL_INV_ITEM_SUMM_PUB
Source
1 PACKAGE BODY ASL_INV_ITEM_SUMM_PUB AS
2 /* $Header: aslconb.pls 120.2 2005/10/25 03:46:43 appldev ship $ */
3
4 -- Global variables for WHO variables and Concurrent program
5 G_load_table VARCHAR2(30) := 'ASL_INV_SUMM_PKG' ;
6 G_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
7 G_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID();
8 G_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
9 G_user_id NUMBER := FND_GLOBAL.USER_ID();
10 G_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID();
11 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('APPS_DEBUG'), 'N');
12 -- 07-APR-2004 vqnguyen disabled pricing summarization.
13 -- replace the following G_PRICING declaration with the
14 -- commented one below it to re-enable
15 G_PRICING VARCHAR2(1) := 'Y';
16 -- G_PRICING VARCHAR2(1) := NVL(fnd_profile.value('ASL_ALL_ITEM_PRICE_LINE'),'N');
17
18
19 PROCEDURE Table_Load_Main(
20 ERRBUF OUT NOCOPY VARCHAR2
21 , RETCODE OUT NOCOPY VARCHAR2
22 , p_run_mode IN VARCHAR2 DEFAULT 'I'
23 , p_category_set_id IN NUMBER
24 , p_organization_id IN NUMBER
25 , p_category_id IN NUMBER
26 ) IS
27
28 l_run_mode VARCHAR2(1) := p_run_mode;
29 l_err_code VARCHAR2(1);
30 l_err_msg VARCHAR2(200); -- Error message for parameter input errors
31 l_category_set_id NUMBER := p_category_set_id;
32 l_organization_id NUMBER := p_organization_id;
33 l_category_id NUMBER := p_category_id;
34
35
36 -- Package Exceptions
37
38 FAILED_EDIT EXCEPTION;
39
40 FAILED_LOAD EXCEPTION;
41
42 BEGIN
43 -- Begin Actual Load Process
44
45
46
47 IF l_run_mode = 'C' THEN
48
49 asl_summ_load_glbl_pkg.Delete_Rows (
50 p_table_name => 'ASL_INVENTORY_ITEM_DENORM'
51 , p_category_set_id => l_category_set_id
52 , p_organization_id => l_organization_id
53 , p_category_id => l_category_id
54 , x_err_msg => l_err_msg
55 , x_err_code => l_err_code
56 );
57
58
59 Complete_Inv_Item_Refresh(
60 x_err_msg => l_err_msg
61 , x_err_code => l_err_code
62 , p_category_set_id => l_category_set_id
63 , p_organization_id => l_organization_id
64 , p_category_id => l_category_id
65 );
66 -- Based on flag setting inventory pricing data is collected
67 IF G_PRICING = 'N' THEN
68
69 asl_summ_load_glbl_pkg.Delete_Rows (
70 p_table_name => 'ASL_INVENTORY_PRICING'
71 , p_category_set_id => l_category_set_id
72 , p_organization_id => l_organization_id
73 , p_category_id => l_category_id
74 , x_err_msg => l_err_msg
75 , x_err_code => l_err_code
76 );
77
78
79 Complete_Inv_Pricing_Refresh(
80 x_err_msg => l_err_msg
81 , x_err_code => l_err_code
82 , p_category_set_id => l_category_set_id
83 , p_organization_id => l_organization_id
84 , p_category_id => l_category_id
85 );
86 END IF;
87
88
89 ELSE
90 IF l_category_id IS NULL THEN
91 Increm_Cat_Inv_Item_Refresh(
92 x_err_msg => l_err_msg
93 , x_err_code => l_err_code
94 , p_category_set_id => l_category_set_id
95 , p_organization_id => l_organization_id
96 );
97 ELSE
98 Increm_Inv_Item_Refresh(
99 x_err_msg => l_err_msg
100 , x_err_code => l_err_code
101 , p_category_set_id => l_category_set_id
102 , p_organization_id => l_organization_id
103 , p_category_id => l_category_id
104 );
105 END IF;
106 IF G_PRICING = 'N' THEN
107 IF l_category_id IS NULL THEN
108 Increm_Cat_Inv_Price_Refresh(
109 x_err_msg => l_err_msg
110 , x_err_code => l_err_code
111 , p_category_set_id => l_category_set_id
112 , p_organization_id => l_organization_id
113 ) ;
114 ELSE
115 Increm_Inv_Pricing_Refresh(
116 x_err_msg => l_err_msg
117 , x_err_code => l_err_code
118 , p_category_set_id => l_category_set_id
119 , p_organization_id => l_organization_id
120 , p_category_id => l_category_id
121 );
122
123 END IF;
124 END IF;
125
126 END IF;
127
128 IF l_err_code = '2' THEN -- Load Failure - Halt processing
129 RAISE FAILED_LOAD;
130 ELSE
131 Category_summary_Info_Refresh(
132 x_err_msg => l_err_msg
133 , x_err_code => l_err_code);
134 END IF;
135
136
137
138 EXCEPTION
139 WHEN FAILED_LOAD THEN
140 ERRBUF := l_err_msg;
141 RETCODE := l_err_code;
142
143
144 WHEN OTHERS THEN
145 l_err_msg := SUBSTR(SQLERRM,1,150);
146 l_err_code := '2' ;
147 ERRBUF := l_err_msg;
148 RETCODE := l_err_code;
149
150 END Table_Load_Main;
151
152 /*************** Begin Sub Procedures ***************/
153
154 PROCEDURE Category_summary_Info_Refresh (
155 x_err_msg OUT NOCOPY VARCHAR2
156 , x_err_code OUT NOCOPY VARCHAR2
157 ) IS
158 l_rows_inserted NUMBER := 0;
159 BEGIN
160 DELETE asl_category_summary_info;
161 INSERT INTO asl_category_summary_info (CATEGORY_ID, CATEGORY_SET_ID , ORGANIZATION_ID,
162 CREATION_DATE, LANGUAGE_CODE )
163 SELECT DISTINCT category_id, category_set_id , organization_id, SYSDATE, language_code
164 FROM asl_inventory_item_denorm ;
165
166 IF SQL%NOTFOUND THEN
167 x_err_msg := 'Category_summary_Info_Refresh' || SUBSTR(SQLERRM,1,150);
168 x_err_code := '0';
169 ELSE
170 COMMIT;
171 l_rows_inserted := SQL%ROWCOUNT;
172 x_err_code := '0';
173 END IF;
174 IF G_DEBUG = 'Y' THEN
175 asl_summ_load_glbl_pkg.Write_Log(
176 p_table => 'ASL_CATEGORY_SUMMARY_INFO'
177 , p_action => 'I'
178 , p_procedure => 'Category_summary_Info_Refresh'
179 , p_num_rows => l_rows_inserted
180 );
181 END IF;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 x_err_msg := 'Category_summary_Info_Refresh: ' || SUBSTR(SQLERRM,1,150);
186 x_err_code := '2';
187
188 IF G_DEBUG = 'Y' THEN
189 asl_summ_load_glbl_pkg.Write_Log(
190 p_table => 'ASL_CATEGORY_SUMMARY_INFO'
191 , p_action => 'E'
192 , p_procedure => 'Category_summary_Info_Refresh'
193 , p_load_mode => 'I'
194 , p_message => x_err_msg
195 );
196 END IF;
197
198 END Category_summary_Info_Refresh;
199
200 PROCEDURE Complete_Inv_Item_Refresh(
201 x_err_msg OUT NOCOPY VARCHAR2
202 , x_err_code OUT NOCOPY VARCHAR2
203 , p_category_set_id IN NUMBER
204 , p_organization_id IN NUMBER
205 , p_category_id IN NUMBER
206 )IS
207
208 l_rows_inserted NUMBER := 0;
209
210 l_category_id NUMBER := p_category_id;
211 l_category_set_id NUMBER := p_category_set_id;
212 l_inv_org_id NUMBER := p_organization_id;
213
214 BEGIN
215
216
217 IF l_category_id IS NULL THEN
218
219 INSERT INTO ASL_INVENTORY_ITEM_DENORM
220 (CATEGORY_SET_ID
221 ,CATEGORY_ID
222 ,INVENTORY_ITEM_ID
223 ,ORGANIZATION_ID
224 ,LAST_UPDATE_DATE
225 ,CREATION_DATE
226 ,INVENTORY_ITEM_NUMBER
227 ,ITEM_DESCRIPTION
228 ,LANGUAGE_CODE
229 ,UOM_CODE
230 ,UOM_DESCRIPTION
231 ,INTEREST_TYPE_ID
232 ,INTEREST_TYPE
233 ,PRIMARY_INTEREST_CODE_ID
234 ,PRIMARY_INTEREST_CODE
235 ,SECONDARY_INTEREST_CODE_ID
236 ,SECONDARY_INTEREST_CODE
237 ,SHIPPLE_FLAG
238 ,SERVICE_ITEM_FLAG
239 ,TAXABLE_FLAG
240 ,RETURNABLE_FLAG
241 ,SERVICEABLE_FLAG
242 ,ACTIVE_FLAG
243 ,BOM_ENABLED_FLAG
244 ,VENDOR_WARRANTY_FLAG
245 ,PRIMARY_UOM_CODE
246 ) SELECT /*+ FIRST_ROWS */
247 MIC.CATEGORY_SET_ID,
248 MIC.CATEGORY_ID,
249 ITEM.INVENTORY_ITEM_ID,
250 ITEM.ORGANIZATION_ID,
251 SYSDATE, -- For bootstrap, using sysdate temporary.
252 SYSDATE,
253 B.CONCATENATED_SEGMENTS,
254 ITEM.DESCRIPTION,
255 USERENV ( 'LANG' ),
256 UOM.UOM_CODE,
257 UOM.UNIT_OF_MEASURE,
258 NULL,
259 NULL,
260 NULL,
261 NULL,
262 NULL,
263 NULL,
264 B.SHIPPABLE_ITEM_FLAG ,
265 B.SERVICE_ITEM_FLAG ,
266 B.TAXABLE_FLAG ,
267 B.returnable_flag ,
268 B.SERVICEABLE_PRODUCT_FLAG ,
269 'Y', -- Active Flag to be 'Y'
270 DECODE(B.BOM_ITEM_TYPE,1,'MDL',4,DECODE(B.SERVICE_ITEM_FLAG ,'Y','SRV', DECODE(B.SERVICEABLE_PRODUCT_FLAG,'Y','SVA','STD')),'OPP') ,
271 B.VENDOR_WARRANTY_FLAG,
272 B.PRIMARY_UOM_CODE
273 FROM MTL_SYSTEM_ITEMS_B_KFV B,
274 MTL_SYSTEM_ITEMS_TL ITEM,
275 MTL_ITEM_CATEGORIES MIC,
276 MTL_UNITS_OF_MEASURE_TL UOM
277 WHERE MIC.ORGANIZATION_ID = l_inv_org_id
278 AND MIC.CATEGORY_SET_ID = l_category_set_id
279 AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
280 AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
281 AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
282 AND UOM.LANGUAGE = userenv('LANG')
283 AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
284 AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
285 AND ITEM.LANGUAGE = userenv('LANG');
286
287 ELSE
288 INSERT INTO ASL_INVENTORY_ITEM_DENORM
289 (CATEGORY_SET_ID
290 ,CATEGORY_ID
291 ,INVENTORY_ITEM_ID
292 ,ORGANIZATION_ID
293 ,LAST_UPDATE_DATE
294 ,CREATION_DATE
295 ,INVENTORY_ITEM_NUMBER
296 ,ITEM_DESCRIPTION
297 ,LANGUAGE_CODE
298 ,UOM_CODE
299 ,UOM_DESCRIPTION
300 ,INTEREST_TYPE_ID
301 ,INTEREST_TYPE
302 ,PRIMARY_INTEREST_CODE_ID
303 ,PRIMARY_INTEREST_CODE
304 ,SECONDARY_INTEREST_CODE_ID
305 ,SECONDARY_INTEREST_CODE
306 ,SHIPPLE_FLAG
307 ,SERVICE_ITEM_FLAG
308 ,TAXABLE_FLAG
309 ,RETURNABLE_FLAG
310 ,SERVICEABLE_FLAG
311 ,ACTIVE_FLAG
312 ,BOM_ENABLED_FLAG
313 ,VENDOR_WARRANTY_FLAG
314 ,PRIMARY_UOM_CODE
315 ) SELECT /*+ FIRST_ROWS */
316 MIC.CATEGORY_SET_ID,
317 MIC.CATEGORY_ID,
318 ITEM.INVENTORY_ITEM_ID,
319 ITEM.ORGANIZATION_ID,
320 SYSDATE, -- For bootstrap, using sysdate temporary.
321 SYSDATE,
322 B.CONCATENATED_SEGMENTS,
323 ITEM.DESCRIPTION,
324 USERENV ( 'LANG' ),
325 UOM.UOM_CODE,
326 UOM.UNIT_OF_MEASURE,
327 NULL,
328 NULL,
329 NULL,
330 NULL,
331 NULL,
332 NULL,
333 B.SHIPPABLE_ITEM_FLAG ,
334 B.SERVICE_ITEM_FLAG ,
335 B.TAXABLE_FLAG ,
336 B.returnable_flag ,
337 B.SERVICEABLE_PRODUCT_FLAG ,
338 'Y', -- Active Flag to be 'Y'
339 DECODE(B.BOM_ITEM_TYPE,1,'MDL',4,DECODE(B.SERVICE_ITEM_FLAG ,'Y','SRV', DECODE(B.SERVICEABLE_PRODUCT_FLAG,'Y','SVA','STD')),'OPP') ,
340 B.VENDOR_WARRANTY_FLAG,
341 B.PRIMARY_UOM_CODE
342 FROM MTL_SYSTEM_ITEMS_B_KFV B,
343 MTL_SYSTEM_ITEMS_TL ITEM,
344 MTL_ITEM_CATEGORIES MIC,
345 MTL_UNITS_OF_MEASURE_TL UOM
346 WHERE MIC.ORGANIZATION_ID = l_inv_org_id
347 AND MIC.CATEGORY_SET_ID = l_category_set_id
348 AND MIC.CATEGORY_ID = l_category_id
349 AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
350 AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
351 AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
352 AND UOM.LANGUAGE = userenv('LANG')
353 AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
354 AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
355 AND ITEM.LANGUAGE = userenv('LANG');
356
357 END IF;
358
359
360
361
362 IF SQL%NOTFOUND THEN
363 x_err_msg := 'Complete_Inv_Item_Refresh' || SUBSTR(SQLERRM,1,150);
364 x_err_code := '0';
365 ELSE
366 COMMIT;
367 l_rows_inserted := SQL%ROWCOUNT;
368 x_err_code := '0';
369 END IF;
370 IF G_DEBUG = 'Y' THEN
371 asl_summ_load_glbl_pkg.Write_Log(
372 p_table => 'ASL_INVENTORY_ITEM_DENORM'
373 , p_action => 'I'
374 , p_procedure => 'Insert_Inv_Item_Denorm'
375 , p_num_rows => l_rows_inserted
376 );
377 END IF;
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 x_err_msg := 'Complete_Inv_Item_Refresh: ' || SUBSTR(SQLERRM,1,150);
382 x_err_code := '2';
383
384 IF G_DEBUG = 'Y' THEN
385 asl_summ_load_glbl_pkg.Write_Log(
386 p_table => 'ASL_INVENTORY_ITEM_DENORM'
387 , p_action => 'E'
388 , p_procedure => 'Complete_Inv_Item_Refresh'
389 , p_load_mode => 'I'
390 , p_message => x_err_msg
391 );
392 END IF;
393
394 END Complete_Inv_Item_Refresh;
395
396 PROCEDURE Increm_Inv_Item_Refresh(
397 x_err_msg OUT NOCOPY VARCHAR2
398 , x_err_code OUT NOCOPY VARCHAR2
399 , p_category_set_id IN NUMBER
400 , p_organization_id IN NUMBER
401 , p_category_id IN NUMBER
402 ) IS
403
404 l_rows_updated NUMBER := 0;
405 l_upd_date DATE := TRUNC(sysdate);
406 l_category_id NUMBER := p_category_id;
407 l_category_set_id NUMBER := p_category_set_id;
408 l_inv_org_id NUMBER := p_organization_id;
409 l_inv_item_id NUMBER;
410 l_count NUMBER := 1;
411
412
413
414 CURSOR inv_item_id(pl_category_set_id NUMBER,
415 pl_inv_org_id NUMBER,
416 pl_category_id NUMBER) IS
417 SELECT INVENTORY_ITEM_ID
418 FROM MTL_ITEM_CATEGORIES
419 WHERE CATEGORY_SET_ID = pl_category_set_id
420 AND ORGANIZATION_ID = pl_inv_org_id
424 BEGIN
421 AND CATEGORY_ID = pl_category_id;
422
423
425
426
427
428 OPEN inv_item_id (l_category_set_id,
429 l_inv_org_id,
430 l_category_id);
431
432 WHILE l_count = 1 LOOP
433
434 FETCH inv_item_id INTO l_inv_item_id;
435
436 IF inv_item_id%FOUND THEN
437
438
439 -- update record if it already exists
440 UPDATE ASL_INVENTORY_ITEM_DENORM aiid
441 SET
442 ( LAST_UPDATE_DATE
443 ,INVENTORY_ITEM_NUMBER
444 ,ITEM_DESCRIPTION
445 ,LANGUAGE_CODE
446 ,UOM_CODE
447 ,UOM_DESCRIPTION
448 -- ,INTEREST_TYPE_ID -- is this required
449 -- ,INTEREST_TYPE
450 -- ,PRIMARY_INTEREST_CODE_ID
451 -- ,PRIMARY_INTEREST_CODE
452 -- ,SECONDARY_INTEREST_CODE_ID
453 -- ,SECONDARY_INTEREST_CODE -- is this required
454 ,SHIPPLE_FLAG
455 ,SERVICE_ITEM_FLAG
456 ,TAXABLE_FLAG
457 ,RETURNABLE_FLAG
458 ,SERVICEABLE_FLAG
459 ,ACTIVE_FLAG ) =
460 (SELECT
461 SYSDATE,
462 B.CONCATENATED_SEGMENTS,
463 ITEM.DESCRIPTION,
464 USERENV ( 'LANG' ),
465 UOM.UOM_CODE,
466 UOM.UNIT_OF_MEASURE,
467 -- NULL,
468 -- NULL,
469 -- NULL,
470 -- NULL,
471 -- NULL,
472 -- NULL,
473 B.SHIPPABLE_ITEM_FLAG ,
474 B.SERVICE_ITEM_FLAG ,
475 B.TAXABLE_FLAG ,
476 B.returnable_flag ,
477 B.SERVICEABLE_PRODUCT_FLAG ,
478 'Y' -- Active Flag to be 'Y'
479 FROM MTL_SYSTEM_ITEMS_B_KFV B,
480 MTL_SYSTEM_ITEMS_TL ITEM,
481 MTL_ITEM_CATEGORIES MIC,
482 MTL_UNITS_OF_MEASURE_TL UOM
483 WHERE MIC.ORGANIZATION_ID = l_inv_org_id
484 AND MIC.CATEGORY_SET_ID = l_category_set_id
485 AND MIC.CATEGORY_ID = l_category_id
486 AND B.INVENTORY_ITEM_ID = l_inv_item_id
487 AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
488 AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
489 AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
490 AND UOM.LANGUAGE = userenv('LANG')
491 AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
492 AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
493 AND ITEM.LANGUAGE = userenv('LANG') )
494
495 WHERE aiid.ORGANIZATION_ID = l_inv_org_id
496 AND aiid.CATEGORY_SET_ID = l_category_set_id
497 AND aiid.CATEGORY_ID = l_category_id
498 AND aiid.INVENTORY_ITEM_ID = l_inv_item_id
499 AND aiid.LANGUAGE_CODE = userenv('LANG')
500 -- if we do not put this part of code and if select does not get records update will raise ora error
501 AND EXISTS (SELECT 1 FROM
502 MTL_SYSTEM_ITEMS_B_KFV B,
503 MTL_SYSTEM_ITEMS_TL ITEM,
504 MTL_ITEM_CATEGORIES MIC,
505 MTL_UNITS_OF_MEASURE_TL UOM
506 WHERE MIC.ORGANIZATION_ID = l_inv_org_id
507 AND MIC.CATEGORY_SET_ID = l_category_set_id
508 AND MIC.CATEGORY_ID = l_category_id
509 AND B.INVENTORY_ITEM_ID = l_inv_item_id
510 AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
511 AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
512 AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
513 AND UOM.LANGUAGE = userenv('LANG')
514 AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
515 AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
516 AND ITEM.LANGUAGE = userenv('LANG') );
517
518 -- Insert if inv item id and which does not exist
519 INSERT INTO ASL_INVENTORY_ITEM_DENORM
520 (CATEGORY_SET_ID
521 ,CATEGORY_ID
522 ,INVENTORY_ITEM_ID
523 ,ORGANIZATION_ID
524 ,LAST_UPDATE_DATE
525 ,CREATION_DATE
526 ,INVENTORY_ITEM_NUMBER
527 ,ITEM_DESCRIPTION
528 ,LANGUAGE_CODE
529 ,UOM_CODE
530 ,UOM_DESCRIPTION
531 ,INTEREST_TYPE_ID
532 ,INTEREST_TYPE
533 ,PRIMARY_INTEREST_CODE_ID
534 ,PRIMARY_INTEREST_CODE
535 ,SECONDARY_INTEREST_CODE_ID
536 ,SECONDARY_INTEREST_CODE
537 ,SHIPPLE_FLAG
538 ,SERVICE_ITEM_FLAG
539 ,TAXABLE_FLAG
540 ,RETURNABLE_FLAG
541 ,SERVICEABLE_FLAG
542 ,ACTIVE_FLAG
543 ,BOM_ENABLED_FLAG
544 ,VENDOR_WARRANTY_FLAG
545 ,PRIMARY_UOM_CODE
546 ) SELECT /*+ FIRST_ROWS */
547 MIC.CATEGORY_SET_ID,
548 MIC.CATEGORY_ID,
549 ITEM.INVENTORY_ITEM_ID,
550 ITEM.ORGANIZATION_ID,
551 SYSDATE, -- For bootstrap, using sysdate temporary.
555 USERENV ( 'LANG' ),
552 SYSDATE,
553 B.CONCATENATED_SEGMENTS,
554 ITEM.DESCRIPTION,
556 UOM.UOM_CODE,
557 UOM.UNIT_OF_MEASURE,
558 NULL,
559 NULL,
560 NULL,
561 NULL,
562 NULL,
563 NULL,
564 B.SHIPPABLE_ITEM_FLAG ,
565 B.SERVICE_ITEM_FLAG ,
566 B.TAXABLE_FLAG ,
567 B.returnable_flag ,
568 B.SERVICEABLE_PRODUCT_FLAG ,
569 'Y', -- Active Flag to be 'Y'
570 DECODE(B.BOM_ITEM_TYPE,1,'MDL',4,DECODE(B.SERVICE_ITEM_FLAG ,'Y','SRV', DECODE(B.SERVICEABLE_PRODUCT_FLAG,'Y','SVA','STD')),'OPP') ,
571 B.VENDOR_WARRANTY_FLAG,
572 B.PRIMARY_UOM_CODE
573 FROM MTL_SYSTEM_ITEMS_B_KFV B,
574 MTL_SYSTEM_ITEMS_TL ITEM,
575 MTL_ITEM_CATEGORIES MIC,
576 MTL_UNITS_OF_MEASURE_TL UOM
577 WHERE MIC.ORGANIZATION_ID = l_inv_org_id
578 AND MIC.CATEGORY_SET_ID = l_category_set_id
579 AND MIC.CATEGORY_ID = l_category_id
580 AND ITEM.INVENTORY_ITEM_ID = l_inv_item_id
581 AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
582 AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
583 AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
584 AND UOM.LANGUAGE = userenv('LANG')
585 AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
586 AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
587 AND ITEM.LANGUAGE = userenv('LANG')
588 AND NOT EXISTS
589 ( SELECT 1
590 FROM ASL_INVENTORY_ITEM_DENORM aiid
591 WHERE aiid.CATEGORY_SET_ID = l_category_set_id
592 AND aiid.ORGANIZATION_ID = l_inv_org_id
593 AND aiid.CATEGORY_ID = l_category_id
594 AND aiid.INVENTORY_ITEM_ID = l_inv_item_id
595 AND aiid.LANGUAGE_CODE = userenv('LANG') );
596
597 ELSE
598 l_count := 0; -- loop stops at this stage.
599
600 END IF;
601 END LOOP;
602
603 CLOSE inv_item_id;
604
605 IF SQL%NOTFOUND THEN
606 l_rows_updated := 0 ;
607 x_err_code := '0';
608 ELSE
609 COMMIT;
610 l_rows_updated := SQL%ROWCOUNT;
611 x_err_code := '0';
612 END IF;
613
614
615 IF G_DEBUG = 'Y' THEN
616 asl_summ_load_glbl_pkg.Write_Log(
617 p_table => 'ASL_INVENTORY_ITEM_DENORM'
618 , p_action => 'U'
619 , p_procedure => 'Increm_Inv_Item_Refresh'
620 , p_num_rows => l_rows_updated
621 ) ;
622 END IF;
623
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 x_err_msg := 'Increm_Inv_Item_Refresh: ' || SUBSTR(SQLERRM,1,150);
628 x_err_code := '2';
629
630 IF G_DEBUG = 'Y' THEN
631 asl_summ_load_glbl_pkg.Write_Log(
632 p_table => 'ASL_INVENTORY_ITEM_DENORM'
633 , p_action => 'E'
634 , p_procedure => 'Increm_Inv_Item_Refresh'
635 , p_load_mode => 'U'
636 , p_message => x_err_msg
637 );
638 END IF;
639
640
641
642 END Increm_Inv_Item_Refresh;
643
644
645 PROCEDURE Increm_Cat_Inv_Item_Refresh(
646 x_err_msg OUT NOCOPY VARCHAR2
647 , x_err_code OUT NOCOPY VARCHAR2
648 , p_category_set_id IN NUMBER
649 , p_organization_id IN NUMBER
650 ) IS
651
652 l_rows_updated NUMBER := 0;
653 l_upd_date DATE := TRUNC(sysdate);
654 l_category_id NUMBER ;
655 l_category_set_id NUMBER := p_category_set_id;
656 l_inv_org_id NUMBER := p_organization_id;
657 l_inv_item_id NUMBER;
658 l_count1 NUMBER := 1;
659 l_err_code VARCHAR2(1);
660 l_err_msg VARCHAR2(200); -- Error message for parameter input errors
661
662
663 CURSOR cat_id(pl_category_set_id NUMBER,
664 pl_inv_org_id NUMBER) IS
665 SELECT CATEGORY_ID
666 FROM MTL_ITEM_CATEGORIES
667 WHERE CATEGORY_SET_ID = pl_category_set_id
668 AND ORGANIZATION_ID = pl_inv_org_id;
669
670 BEGIN
671
672 OPEN cat_id(l_category_set_id,
673 l_inv_org_id);
674
675 WHILE l_count1 = 1 LOOP -- loop for cat id
676 FETCH cat_id INTO l_category_id;
677 IF cat_id%FOUND THEN
678
679 Increm_Inv_Item_Refresh(
680 x_err_msg => l_err_msg
681 , x_err_code => l_err_code
685 );
682 , p_category_set_id => l_category_set_id
683 , p_organization_id => l_inv_org_id
684 , p_category_id => l_category_id
686
687
688 ELSE -- else for cat id if
689 l_count1 := 0; -- first loop stops at this stage.
690 END IF;
691 END LOOP; -- end loop for cat id
692 CLOSE cat_id;
693
694
695 IF SQL%NOTFOUND THEN
696 l_rows_updated := 0 ;
697 x_err_code := '0';
698 ELSE
699 COMMIT;
700 l_rows_updated := SQL%ROWCOUNT;
701 x_err_code := '0';
702 END IF;
703
704
705 IF G_DEBUG = 'Y' THEN
706 asl_summ_load_glbl_pkg.Write_Log(
707 p_table => 'ASL_INVENTORY_ITEM_DENORM'
708 , p_action => 'U'
709 , p_procedure => 'Increm_Cat_Inv_Item_Refresh'
710 , p_num_rows => l_rows_updated
711 ) ;
712 END IF;
713
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 x_err_msg := 'Increm_Cat_Inv_Item_Refresh: ' || SUBSTR(SQLERRM,1,150);
718 x_err_code := '2';
719
720 IF G_DEBUG = 'Y' THEN
721 asl_summ_load_glbl_pkg.Write_Log(
722 p_table => 'ASL_INVENTORY_ITEM_DENORM'
723 , p_action => 'E'
724 , p_procedure => 'Increm_Cat_Inv_Item_Refresh'
725 , p_load_mode => 'U'
726 , p_message => x_err_msg
727 );
728 END IF;
729
730
731
732 END Increm_Cat_Inv_Item_Refresh;
733
734 PROCEDURE Complete_Inv_Pricing_Refresh(
735 x_err_msg OUT NOCOPY VARCHAR2
736 , x_err_code OUT NOCOPY VARCHAR2
737 , p_category_set_id IN NUMBER
738 , p_organization_id IN NUMBER
739 , p_category_id IN NUMBER
740 ) IS
741
742
743
744 l_inv_org_id NUMBER := p_organization_id;
745 l_category_set_id NUMBER := p_category_set_id;
746 l_category_id NUMBER := p_category_id;
747 l_rows_inserted NUMBER := 0;
748
749 l_currency_code VARCHAR2(4);
750 l_list_header_id NUMBER;
751 l_inventory_item_id NUMBER;
752
753
754
755 CURSOR list_hdr_id(p_currency_code VARCHAR2) IS
756 SELECT QH.LIST_HEADER_ID
757 FROM QP_LIST_HEADERS_B QH
758 WHERE QH.LIST_TYPE_CODE = 'PRL'
759 AND nvl(QH.start_date_active, SYSDATE) <= SYSDATE
760 AND nvl(QH.end_date_active, SYSDATE) >= SYSDATE
761 AND QH.mobile_download = 'Y'
762 AND QH.ACTIVE_FLAG = 'Y'
763 AND QH.currency_code = p_currency_code;
764
765 CURSOR invt_item_id(p_inv_org_id NUMBER, p_category_set_id NUMBER, p_category_id NUMBER) IS
766 SELECT INVENTORY_ITEM_ID
767 FROM ASL_INVENTORY_ITEM_DENORM ITEM
768 WHERE ITEM.CATEGORY_SET_ID = p_category_set_id
769 AND ITEM.CATEGORY_ID = p_category_id
770 AND ITEM.ORGANIZATION_ID = p_inv_org_id
771 AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
772
773
774 BEGIN
775 /*
776 04-April-2005 SEBHAT. Refer Bug: 4266517
777 Disabling the population of ASL_INVENTORY_PRICING table as this is currently
778 not being used for downloading summarised information to Sales Offline(ASL)
779 */
780
781 NULL;
782
783 /*
784 SELECT FND_PROFILE.value('JTF_PROFILE_DEFAULT_CURRENCY')
785 INTO l_currency_code
786 FROM DUAL;
787
788 IF l_category_id IS NULL THEN
789
790 -- We should add the logic for only downloading the mobile flag. and list_type_code = PRL.
791 INSERT INTO ASL_INVENTORY_PRICING
792 (LIST_HEADER_ID
793 ,LIST_LINE_ID
794 ,LIST_LINE_TYPE_CODE
795 ,INVENTORY_ITEM_ID
796 ,ORGANIZATION_ID
797 ,AUTOMATIC_FLAG
798 ,LIST_PRICE
799 ,LIST_PRICE_UOM_CODE
800 ,PRIMARY_UOM_FLAG
801 ,LIST_LINE_NO
802 ,LAST_UPDATE_DATE
803 ,CREATION_DATE
804 ,LANGUAGE_CODE
805 ,CURRENCY_CODE
806 )
807 SELECT *//*+ ORDERED use_nl(QPA QL)
808 index(QPA QP_PRICING_ATTRIBUTES_N5)
809 index(QL QP_LIST_LINES_PK)
810 index(ITEM ASL_INVENTORY_ITEM_DENORM_N1)*/
811 /*distinct QH.LIST_HEADER_ID,
812 QL.list_line_id,
813 QL.list_line_type_code,
814 ITEM.inventory_item_id,
815 l_inv_org_id,
816 QL.AUTOMATIC_FLAG ,
817 DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
818 QL.LIST_PRICE_UOM_CODE ,
819 QL.PRIMARY_UOM_FLAG ,
820 QL.LIST_LINE_NO ,
821 SYSDATE,
822 SYSDATE,
823 USERENV ( 'LANG' ),
824 l_currency_code
828 QP_LIST_HEADERS_B QH
825 FROM QP_PRICING_ATTRIBUTES QPA,
826 QP_LIST_LINES QL,
827 ASL_INVENTORY_ITEM_DENORM ITEM ,
829 WHERE QPA.LIST_HEADER_ID = QH.LIST_HEADER_ID
830 AND QH.LIST_TYPE_CODE = 'PRL'
831 AND nvl(QH.start_date_active, SYSDATE) <= SYSDATE
832 AND nvl(QH.end_date_active, SYSDATE) >= SYSDATE
833 AND QH.mobile_download = 'Y'
834 AND QH.ACTIVE_FLAG = 'Y'
835 AND QH.currency_code = l_currency_code
836 -- AND QPA.PRICING_PHASE_ID = 1
837 AND QPA.product_attribute_context = 'ITEM'
838 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
839 AND to_char(ITEM.inventory_item_id) = QPA.PRODUCT_ATTR_VALUE
840 -- AND QPA.QUALIFICATION_IND IN (4, 6)
841 AND QPA.excluder_flag = 'N'
842 AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
843 AND QL.LIST_LINE_TYPE_CODE = 'PLL'
844 AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE'
845 AND ITEM.CATEGORY_SET_ID = l_category_set_id
846 AND ITEM.ORGANIZATION_ID = l_inv_org_id
847 AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
848
849 ELSE -- when l_category_id is passed by user
850
851 -- Get all the list_header_ids and loop
852 FOR r_list_hdr_id IN list_hdr_id(l_currency_code) LOOP
853
854 l_list_header_id := r_list_hdr_id.LIST_HEADER_ID;
855 -- DBMS_OUTPUT.PUT_LINE( ' HEADER ID ' || l_list_header_id );
856
857 FOR r_invt_item_id IN invt_item_id(l_inv_org_id, l_category_set_id, l_category_id) LOOP
858 l_inventory_item_id := r_invt_item_id.INVENTORY_ITEM_ID;
859
860
861 INSERT INTO ASL_INVENTORY_PRICING
862 (LIST_HEADER_ID
863 ,LIST_LINE_ID
864 ,LIST_LINE_TYPE_CODE
865 ,INVENTORY_ITEM_ID
866 ,ORGANIZATION_ID
867 ,AUTOMATIC_FLAG
868 ,LIST_PRICE
869 ,LIST_PRICE_UOM_CODE
870 ,PRIMARY_UOM_FLAG
871 ,LIST_LINE_NO
872 ,LAST_UPDATE_DATE
873 ,CREATION_DATE
874 ,LANGUAGE_CODE
875 ,CURRENCY_CODE
876 ) SELECT*/ /*+ ORDERED use_nl(QPA QL)
877 index(QPA QP_PRICING_ATTRIBUTES_N5)
878 index(QL QP_LIST_LINES_PK)*/
879 /*l_list_header_id,
880 QL.list_line_id,
881 QL.list_line_type_code,
882 l_inventory_item_id,
883 l_inv_org_id,
884 QL.AUTOMATIC_FLAG ,
885 DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
886 QL.LIST_PRICE_UOM_CODE ,
887 QL.PRIMARY_UOM_FLAG ,
888 QL.LIST_LINE_NO ,
889 SYSDATE,
890 SYSDATE,
891 USERENV ( 'LANG' ),
892 l_currency_code
893 FROM QP_PRICING_ATTRIBUTES QPA,
894 QP_LIST_LINES QL
895 WHERE QPA.LIST_HEADER_ID = l_list_header_id
896 -- AND QPA.PRICING_PHASE_ID = 1
897 AND QPA.product_attribute_context = 'ITEM'
898 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
899 AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
900 -- AND QPA.QUALIFICATION_IND IN (4, 6)
901 AND QPA.excluder_flag = 'N'
902 AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
903 AND QL.LIST_LINE_TYPE_CODE = 'PLL'
904 AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE' ;
905
906 END LOOP; -- inv item id loop ends
907 END LOOP; -- header id loop ends
908
909
910
911 END IF;
912
913
914 IF SQL%NOTFOUND THEN
915 x_err_msg := 'Complete_Inv_Pricing_Refresh' || SUBSTR(SQLERRM,1,150);
916 x_err_code := '0';
917 ELSE
918 COMMIT;
919 l_rows_inserted := SQL%ROWCOUNT;
920 x_err_code := '0';
921 END IF;
922 IF G_DEBUG = 'Y' THEN
923 asl_summ_load_glbl_pkg.Write_Log(
924 p_table => 'ASL_INVENTORY_PRICING'
925 , p_action => 'I'
926 , p_procedure => 'Complete_Inv_Pricing_Refresh'
927 , p_num_rows => l_rows_inserted
928 );
929 END IF;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 x_err_msg := 'Complete_Inv_Pricing_Refresh: ' || SUBSTR(SQLERRM,1,150);
934 x_err_code := '2';
935
936 IF G_DEBUG = 'Y' THEN
937 asl_summ_load_glbl_pkg.Write_Log(
938 p_table => 'ASL_INVENTORY_PRICING'
939 , p_action => 'E'
940 , p_procedure => 'Complete_Inv_Pricing_Refresh'
941 , p_load_mode => 'I'
942 , p_message => x_err_msg
943 );
944 END IF;
945 04-April-2005 SEBHAT. Refer Bug: 4266517
946 */
947 END Complete_Inv_Pricing_Refresh;
948
949 PROCEDURE Increm_Inv_Pricing_Refresh(
950 x_err_msg OUT NOCOPY VARCHAR2
951 , x_err_code OUT NOCOPY VARCHAR2
952 , p_category_set_id IN NUMBER
953 , p_organization_id IN NUMBER
954 , p_category_id IN NUMBER
955 ) IS
956
957 l_inv_org_id NUMBER := p_organization_id;
958 l_category_set_id NUMBER := p_category_set_id;
959 l_category_id NUMBER := p_category_id;
960 l_rows_updated NUMBER := 0;
961
962 l_currency_code VARCHAR2(4);
963 l_list_header_id NUMBER;
964 l_inventory_item_id NUMBER;
965
966
967
968 CURSOR list_hdr_id(p_currency_code VARCHAR2) IS
969 SELECT QH.LIST_HEADER_ID
970 FROM QP_LIST_HEADERS_B QH
971 WHERE QH.LIST_TYPE_CODE = 'PRL'
972 AND nvl(QH.start_date_active, SYSDATE) <= SYSDATE
973 AND nvl(QH.end_date_active, SYSDATE) >= SYSDATE
974 AND QH.mobile_download = 'Y'
975 AND QH.ACTIVE_FLAG = 'Y'
976 AND QH.currency_code = p_currency_code;
977
978 CURSOR invt_item_id(p_inv_org_id NUMBER, p_category_set_id NUMBER, p_category_id NUMBER) IS
979 SELECT INVENTORY_ITEM_ID
980 FROM ASL_INVENTORY_ITEM_DENORM ITEM
981 WHERE ITEM.CATEGORY_SET_ID = p_category_set_id
982 AND ITEM.CATEGORY_ID = p_category_id
983 AND ITEM.ORGANIZATION_ID = p_inv_org_id
984 AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
985
986 BEGIN
987 /* 11-AUG-2005 SEBHAT. Refer Bug: 4282256
988 Disabling the population of ASL_INVENTORY_PRICING table in the
989 Incremental Refresh as done for Complete Refresh for Bug: 4266517
990 */
991
992 NULL;
993
994 /*
995 SELECT FND_PROFILE.value('JTF_PROFILE_DEFAULT_CURRENCY')
996 INTO l_currency_code
997 FROM DUAL;
998
999
1000 -- Get all the list_header_ids and loop
1001 FOR r_list_hdr_id IN list_hdr_id(l_currency_code) LOOP
1002
1003 l_list_header_id := r_list_hdr_id.LIST_HEADER_ID;
1004
1005
1006 FOR r_invt_item_id IN invt_item_id(l_inv_org_id, l_category_set_id, l_category_id) LOOP
1010
1007 l_inventory_item_id := r_invt_item_id.INVENTORY_ITEM_ID;
1008
1009
1011
1012 -- update records if they exist already
1013
1014 UPDATE ASL_INVENTORY_PRICING aip
1015 SET (LIST_LINE_ID
1016 ,LIST_LINE_TYPE_CODE
1017 ,AUTOMATIC_FLAG
1018 ,LIST_PRICE
1019 ,LIST_PRICE_UOM_CODE
1020 ,PRIMARY_UOM_FLAG
1021 ,LIST_LINE_NO
1022 ,LAST_UPDATE_DATE
1023 ,LANGUAGE_CODE ) =
1024 ( SELECT + ORDERED use_nl(QPA QL)
1025 index(QPA QP_PRICING_ATTRIBUTES_N5)
1026 index(QL QP_LIST_LINES_PK)
1027 QL.list_line_id,
1028 QL.list_line_type_code,
1029 QL.AUTOMATIC_FLAG ,
1030 DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
1031 QL.LIST_PRICE_UOM_CODE ,
1032 QL.PRIMARY_UOM_FLAG ,
1033 QL.LIST_LINE_NO ,
1034 SYSDATE,
1035 USERENV ( 'LANG' )
1036 FROM QP_PRICING_ATTRIBUTES QPA,
1037 QP_LIST_LINES QL
1038 WHERE QPA.LIST_HEADER_ID = l_list_header_id
1039 -- AND QPA.PRICING_PHASE_ID = 1
1040 AND QPA.product_attribute_context = 'ITEM'
1041 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
1042 AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
1043 -- AND QPA.QUALIFICATION_IND IN (4, 6)
1044 AND QPA.excluder_flag = 'N'
1045 AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
1046 AND QL.LIST_LINE_TYPE_CODE = 'PLL'
1047 AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE' )
1048 WHERE aip.LIST_HEADER_ID = l_list_header_id
1049 AND aip.INVENTORY_ITEM_ID = l_inventory_item_id
1050 AND aip.ORGANIZATION_ID = l_inv_org_id
1051 AND aip.CURRENCY_CODE = l_currency_code
1052 AND EXISTS (SELECT 1
1053 FROM QP_PRICING_ATTRIBUTES QPA,
1054 QP_LIST_LINES QL
1055 WHERE QPA.LIST_HEADER_ID = l_list_header_id
1056 -- AND QPA.PRICING_PHASE_ID = 1
1057 AND QPA.product_attribute_context = 'ITEM'
1058 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
1059 AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
1060 -- AND QPA.QUALIFICATION_IND IN (4, 6)
1061 AND QPA.excluder_flag = 'N'
1062 AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
1063 AND QL.LIST_LINE_TYPE_CODE = 'PLL'
1064 AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE' );
1065
1066 -- If records do not exist Insert the same
1067 INSERT INTO ASL_INVENTORY_PRICING
1068 (LIST_HEADER_ID
1069 ,LIST_LINE_ID
1070 ,LIST_LINE_TYPE_CODE
1071 ,INVENTORY_ITEM_ID
1072 ,ORGANIZATION_ID
1073 ,AUTOMATIC_FLAG
1074 ,LIST_PRICE
1075 ,LIST_PRICE_UOM_CODE
1076 ,PRIMARY_UOM_FLAG
1077 ,LIST_LINE_NO
1078 ,LAST_UPDATE_DATE
1079 ,CREATION_DATE
1080 ,LANGUAGE_CODE
1081 ,CURRENCY_CODE
1082 ) SELECT + ORDERED use_nl(QPA QL)
1083 index(QPA QP_PRICING_ATTRIBUTES_N5)
1084 index(QL QP_LIST_LINES_PK)
1085 l_list_header_id,
1086 QL.list_line_id,
1087 QL.list_line_type_code,
1088 l_inventory_item_id,
1089 l_inv_org_id,
1090 QL.AUTOMATIC_FLAG ,
1091 DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
1092 QL.LIST_PRICE_UOM_CODE ,
1093 QL.PRIMARY_UOM_FLAG ,
1094 QL.LIST_LINE_NO ,
1095 SYSDATE,
1096 SYSDATE,
1097 USERENV ( 'LANG' ),
1098 l_currency_code
1099 FROM QP_PRICING_ATTRIBUTES QPA,
1100 QP_LIST_LINES QL
1101 WHERE QPA.LIST_HEADER_ID = l_list_header_id
1102 -- AND QPA.PRICING_PHASE_ID = 1
1106 -- AND QPA.QUALIFICATION_IND IN (4, 6)
1103 AND QPA.product_attribute_context = 'ITEM'
1104 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
1105 AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
1107 AND QPA.excluder_flag = 'N'
1108 AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
1109 AND QL.LIST_LINE_TYPE_CODE = 'PLL'
1110 AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE'
1111 AND NOT EXISTS
1112 ( SELECT 1
1113 FROM ASL_INVENTORY_PRICING aip
1114 WHERE aip.LIST_HEADER_ID = l_list_header_id
1115 AND aip.INVENTORY_ITEM_ID = l_inventory_item_id
1116 AND aip.ORGANIZATION_ID = l_inv_org_id
1117 AND aip.CURRENCY_CODE = l_currency_code);
1118
1119
1120 END LOOP; -- inv item id loop ends
1121 END LOOP; -- header id loop ends
1122
1123 IF SQL%NOTFOUND THEN
1124 l_rows_updated := 0 ;
1125 x_err_code := '0';
1126 ELSE
1127 COMMIT;
1128 l_rows_updated := SQL%ROWCOUNT;
1129 x_err_code := '0';
1130 END IF;
1131
1132
1133 IF G_DEBUG = 'Y' THEN
1134 asl_summ_load_glbl_pkg.Write_Log(
1135 p_table => 'ASL_INVENTORY_PRICING'
1136 , p_action => 'U'
1137 , p_procedure => 'Increm_Inv_Pricing_Refresh'
1138 , p_num_rows => l_rows_updated
1139 ) ;
1140 END IF;
1141
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 x_err_msg := 'Increm_Inv_Pricing_Refresh: ' || SUBSTR(SQLERRM,1,150);
1146 x_err_code := '2';
1147
1148 IF G_DEBUG = 'Y' THEN
1149 asl_summ_load_glbl_pkg.Write_Log(
1150 p_table => 'ASL_INVENTORY_PRICING'
1151 , p_action => 'E'
1152 , p_procedure => 'Increm_Inv_Pricing_Refresh'
1153 , p_load_mode => 'U'
1154 , p_message => x_err_msg
1155 );
1156 END IF;
1157 */
1158 END Increm_Inv_Pricing_Refresh;
1159
1160 PROCEDURE Increm_Cat_Inv_Price_Refresh(
1161 x_err_msg OUT NOCOPY VARCHAR2
1162 , x_err_code OUT NOCOPY VARCHAR2
1163 , p_category_set_id IN NUMBER
1164 , p_organization_id IN NUMBER
1165 ) IS
1166
1167 l_rows_updated NUMBER := 0;
1168 l_upd_date DATE := TRUNC(sysdate);
1169 l_category_id NUMBER ;
1170 l_category_set_id NUMBER := p_category_set_id;
1171 l_inv_org_id NUMBER := p_organization_id;
1172 l_inv_item_id NUMBER;
1173 l_err_code VARCHAR2(1);
1174 l_err_msg VARCHAR2(200); -- Error message for parameter input errors
1175
1176
1177 CURSOR cat_id(pl_category_set_id NUMBER,
1181 WHERE ITEM.CATEGORY_SET_ID = pl_category_set_id
1178 pl_inv_org_id NUMBER) IS
1179 SELECT distinct CATEGORY_ID
1180 FROM ASL_INVENTORY_ITEM_DENORM ITEM
1182 AND ITEM.ORGANIZATION_ID = pl_inv_org_id
1183 AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
1184
1185
1186
1187 BEGIN
1188
1189 FOR r_cat_id IN cat_id( l_category_set_id, l_inv_org_id) LOOP
1190 l_category_id := r_cat_id.CATEGORY_ID;
1191
1192
1193
1194 Increm_Inv_Pricing_Refresh(
1195 x_err_msg => l_err_msg
1196 , x_err_code => l_err_code
1197 , p_category_set_id => l_category_set_id
1198 , p_organization_id => l_inv_org_id
1199 , p_category_id => l_category_id
1200 );
1201
1202 END LOOP; -- end loop for cat id
1203
1204
1205
1206 IF SQL%NOTFOUND THEN
1207 l_rows_updated := 0 ;
1208 x_err_code := '0';
1209 ELSE
1210 COMMIT;
1211 l_rows_updated := SQL%ROWCOUNT;
1212 x_err_code := '0';
1213 END IF;
1214
1215
1216 IF G_DEBUG = 'Y' THEN
1217 asl_summ_load_glbl_pkg.Write_Log(
1218 p_table => 'ASL_INVENTORY_PRICING'
1219 , p_action => 'U'
1220 , p_procedure => 'Increm_Cat_Inv_Price_Refresh'
1221 , p_num_rows => l_rows_updated
1222 ) ;
1223 END IF;
1224
1225
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 x_err_msg := 'Increm_Cat_Inv_Price_Refresh: ' || SUBSTR(SQLERRM,1,150);
1229 x_err_code := '2';
1230
1231 IF G_DEBUG = 'Y' THEN
1232 asl_summ_load_glbl_pkg.Write_Log(
1233 p_table => 'ASL_INVENTORY_PRICING'
1234 , p_action => 'E'
1235 , p_procedure => 'Increm_Cat_Inv_Price_Refresh'
1236 , p_load_mode => 'U'
1237 , p_message => x_err_msg
1238 );
1239 END IF;
1240
1241
1242
1243 END Increm_Cat_Inv_Price_Refresh;
1244
1245
1246
1247 END ASL_INV_ITEM_SUMM_PUB;