1: PACKAGE BODY pos_product_service_utl_pkg AS
2: /* $Header: POSPSUTB.pls 120.24.12020000.2 2012/07/23 20:36:17 atjen ship $*/
3: --
4: -- type definition
5: TYPE cursor_ref_type IS REF CURSOR;
1043:
1044: /*Bug 9043064 (FP 9011350) Added the following debug statements.*/
1045:
1046: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_validation_type : '||p_validation_type);
1048: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1044: /*Bug 9043064 (FP 9011350) Added the following debug statements.*/
1045:
1046: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_validation_type : '||p_validation_type);
1048: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1045:
1046: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_validation_type : '||p_validation_type);
1048: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1046: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_validation_type : '||p_validation_type);
1048: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1047: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_validation_type : '||p_validation_type);
1048: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1048: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1056: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_column_name : '||p_parent_column_name);
1049: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1056: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_column_name : '||p_parent_column_name);
1057: END IF;
1050: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1056: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_column_name : '||p_parent_column_name);
1057: END IF;
1058:
1051: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1056: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_column_name : '||p_parent_column_name);
1057: END IF;
1058:
1059: /*Modified as as part of bug 8611906 considering where clause can be null also and syncind data in global temp table with other VO used fro browse specific*/
1052: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1056: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_column_name : '||p_parent_column_name);
1057: END IF;
1058:
1059: /*Modified as as part of bug 8611906 considering where clause can be null also and syncind data in global temp table with other VO used fro browse specific*/
1060: if (p_validation_type = 'I') then
1074: from pos_products_services_gt
1075: where SEGMENT_VALUE_ID = ffvl.flex_value_id )';
1076:
1077: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (I) : '||l_tbl_sql);
1079: END IF;
1080:
1081: OPEN l_tblcur for l_tbl_sql;
1082: loop
1130: AND mcsvc.category_set_id = '|| g_default_po_category_set_id ||
1131: ' AND ffvl.flex_value = mcb.' || p_parent_column_name ;
1132:
1133: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1134: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_sql : '||l_sql);
1135: END IF;
1136:
1137:
1138: OPEN l_cur FOR l_sql;
1159: where SEGMENT_VALUE_ID = ffvl2.flex_value_id)' ;
1160:
1161: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1162: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1163: 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (D) : '||l_tbl_sql);
1164: END IF;
1165:
1166: OPEN l_tblcur for l_tbl_sql;
1167: loop
1223: and mcb.category_id = mcsvc.category_id and mcsvc.category_set_id ='||g_default_po_category_set_id || ' and
1224: To_Char(x.value) = mcb.'||p_column_name|| ' AND NOT EXISTS (select SEGMENT_VALUE_ID from pos_products_services_gt where SEGMENT_VALUE_ID = x.value ) ';
1225:
1226: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1227: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (T1) : '||l_tbl_sql);
1228: END IF;
1229:
1230: ELSE
1231: l_tbl_sql := 'select distinct mcb.' ||p_column_name ||' value,
1240:
1241: ' and NOT EXISTS (select SEGMENT_VALUE_ID from pos_products_services_gt where SEGMENT_VALUE_ID = x.value ) ';
1242:
1243: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1244: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (T2) : '||l_tbl_sql);
1245: END IF;
1246:
1247: END IF;
1248:
1924: codes.
1925:
1926: Example usage:
1927: SELECT code, meaning
1928: FROM table(pos_product_service_utl_pkg.product_service_ocv)
1929: */
1930:
1931: PROCEDURE insert_into_ocv_table
1932: (p_ocv_table IN OUT NOCOPY product_service_ocv_table,
2076: END get_flexfield_columns;
2077:
2078: /* End Supplier Hub: Supplier Profile Workbench */
2079:
2080: END pos_product_service_utl_pkg;