[Home] [Help]
262:
263: g_description_queries(g_product_segment_count) := NULL;
264: END LOOP;
265:
266: -- check segment def against pos_sup_products_services
267: -- to be implemented later
268: --
269:
270: -- all validation is done
500: RETURN get_segment_value_desc( p_product_segment_index, p_segment_value, p_parent_segment_value);
501: END get_product_description;
502:
503: --FUNCTION get_segment_value(p_product_segment_index IN NUMBER,
504: -- p_rec IN pos_sup_products_services%ROWTYPE ) RETURN VARCHAR2
505: FUNCTION get_segment_value(p_product_segment_index IN NUMBER,
506: p_rec IN category_segment_record ) RETURN VARCHAR2
507: IS
508: l_index NUMBER;
606: (p_classification_id IN NUMBER,
607: x_has_subcategory OUT NOCOPY VARCHAR2)
608: IS
609: CURSOR l_cur IS
610: SELECT * FROM pos_sup_products_services
611: WHERE classification_id = p_classification_id;
612: l_rec l_cur%ROWTYPE;
613: l_segment_values category_segment_record;
614: l_query VARCHAR2(3000);
611: WHERE classification_id = p_classification_id;
612: l_rec l_cur%ROWTYPE;
613: l_segment_values category_segment_record;
614: l_query VARCHAR2(3000);
615: l_segment_value pos_sup_products_services.segment1%TYPE;
616: l_last_not_null INTEGER;
617: l_cur2 cursor_ref_type;
618: l_count NUMBER;
619: BEGIN
665: RETURN;
666: END IF;
667:
668: l_query := 'select mcb.category_id from mtl_categories_b mcb, ' ||
669: ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
670: ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
671: ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
672: ' and mcb.category_id = mcsvc.category_id ' ||
673: ' and psps.classification_id = :1 and (';
799: END get_concatenated_description;
800:
801: --
802: -- get the description of product and service for a row
803: -- in pos_sup_products_services table.
804: --
805: PROCEDURE get_product_description
806: (p_classification_id IN NUMBER, x_description OUT NOCOPY VARCHAR2 ) IS
807: CURSOR l_cur IS
804: --
805: PROCEDURE get_product_description
806: (p_classification_id IN NUMBER, x_description OUT NOCOPY VARCHAR2 ) IS
807: CURSOR l_cur IS
808: SELECT * FROM pos_sup_products_services
809: WHERE classification_id = p_classification_id;
810: l_rec l_cur%ROWTYPE;
811: l_segment_values category_segment_record;
812: l_segment_value pos_sup_products_services.segment1%TYPE;
808: SELECT * FROM pos_sup_products_services
809: WHERE classification_id = p_classification_id;
810: l_rec l_cur%ROWTYPE;
811: l_segment_values category_segment_record;
812: l_segment_value pos_sup_products_services.segment1%TYPE;
813: BEGIN
814: assert_init();
815: OPEN l_cur;
816: FETCH l_cur INTO l_rec;
850: PROCEDURE get_product_description
851: (p_category IN VARCHAR2, x_description OUT NOCOPY VARCHAR2 ) IS
852:
853: l_segment_values category_segment_record;
854: l_segment_value pos_sup_products_services.segment1%TYPE;
855:
856: l_length NUMBER;
857: l_num_of_delim NUMBER;
858: l_index NUMBER;
896: --dbms_output.put_line(x_description);
897: END get_product_description;
898:
899: -- get the description of product and service for a row
900: -- in pos_sup_products_services table, and whether there
901: -- is a subcategories for the product and service
902: PROCEDURE get_desc_check_subcategory
903: (p_classification_id IN NUMBER,
904: x_description OUT NOCOPY VARCHAR2,
943: l_query VARCHAR2(4000);
944: BEGIN
945: assert_init();
946: l_query := 'select psps.vendor_id, mcb.category_id from mtl_categories_b mcb, ' ||
947: ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
948: ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
949: ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
950: ' and mcb.category_id = mcsvc.category_id and (';
951: FOR l_index IN 1..g_product_segment_count LOOP
1221:
1222:
1223:
1224: l_sql := 'SELECT classification_id
1225: FROM pos_sup_products_services
1226: WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1227: 'AND status <> '||''''||'X'||''''||
1228: 'AND vendor_id = '||x_vendor_id||
1229: 'union all
1254: end if;
1255:
1256:
1257: l_parent_sql := 'SELECT classification_id
1258: FROM pos_sup_products_services
1259: WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1260: 'AND '||l_next_seg||' is null '||
1261: 'AND status <> '||''''||'X'||''''||
1262: 'AND vendor_id = '||x_vendor_id||
1375:
1376: Cursor ps_code_cur is
1377: (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
1378: segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
1379: from pos_sup_products_services
1380: where classification_id = x_classification_id)
1381: UNION ALL
1382: (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
1383: segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
1419: p_ps_request_id IN NUMBER,
1420: x_has_subcategory OUT NOCOPY VARCHAR2)
1421: IS
1422: l_query VARCHAR2(3000);
1423: l_segment_value pos_sup_products_services.segment1%TYPE;
1424: l_last_not_null INTEGER;
1425: l_cur2 cursor_ref_type;
1426: l_count NUMBER;
1427: BEGIN
1671: BEGIN
1672:
1673: for i in 1..p_req_id_tbl.COUNT LOOP
1674:
1675: UPDATE POS_SUP_PRODUCTS_SERVICES
1676: SET status = p_status,
1677: last_updated_by = fnd_global.user_id,
1678: last_update_date = Sysdate,
1679: last_update_login = fnd_global.login_id