[Home] [Help]
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
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';
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
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
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: );
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
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
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
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
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: ) ;
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
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: ) ;
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
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' );
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,
823: USERENV ( 'LANG' ),
824: l_currency_code
825: FROM QP_PRICING_ATTRIBUTES QPA,
826: QP_LIST_LINES QL,
827: ASL_INVENTORY_ITEM_DENORM ITEM ,
828: QP_LIST_HEADERS_B QH
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
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' );
1176:
1177: CURSOR cat_id(pl_category_set_id NUMBER,
1178: pl_inv_org_id NUMBER) IS
1179: SELECT distinct CATEGORY_ID
1180: FROM ASL_INVENTORY_ITEM_DENORM ITEM
1181: WHERE ITEM.CATEGORY_SET_ID = pl_category_set_id
1182: AND ITEM.ORGANIZATION_ID = pl_inv_org_id
1183: AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
1184: