[Home] [Help]
263:
264: g_description_queries(g_product_segment_count) := NULL;
265: END LOOP;
266:
267: -- check segment def against pos_sup_products_services
268: -- to be implemented later
269: --
270:
271: -- all validation is done
527: RETURN get_segment_value_desc( p_product_segment_index, p_segment_value, p_parent_segment_value);
528: END get_product_description;
529:
530: --FUNCTION get_segment_value(p_product_segment_index IN NUMBER,
531: -- p_rec IN pos_sup_products_services%ROWTYPE ) RETURN VARCHAR2
532: FUNCTION get_segment_value(p_product_segment_index IN NUMBER,
533: p_rec IN category_segment_record ) RETURN VARCHAR2
534: IS
535: l_index NUMBER;
633: (p_classification_id IN NUMBER,
634: x_has_subcategory OUT NOCOPY VARCHAR2)
635: IS
636: CURSOR l_cur IS
637: SELECT * FROM pos_sup_products_services
638: WHERE classification_id = p_classification_id;
639: l_rec l_cur%ROWTYPE;
640: l_segment_values category_segment_record;
641: l_query VARCHAR2(3000);
638: WHERE classification_id = p_classification_id;
639: l_rec l_cur%ROWTYPE;
640: l_segment_values category_segment_record;
641: l_query VARCHAR2(3000);
642: l_segment_value pos_sup_products_services.segment1%TYPE;
643: l_last_not_null INTEGER;
644: l_cur2 cursor_ref_type;
645: l_count NUMBER;
646: BEGIN
692: RETURN;
693: END IF;
694:
695: l_query := 'select mcb.category_id from mtl_categories_b mcb, ' ||
696: ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
697: ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
698: ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
699: ' and mcb.category_id = mcsvc.category_id ' ||
700: ' and psps.classification_id = :1 and (';
833: END get_concatenated_description;
834:
835: --
836: -- get the description of product and service for a row
837: -- in pos_sup_products_services table.
838: --
839: PROCEDURE get_product_description
840: (p_classification_id IN NUMBER, x_description OUT NOCOPY VARCHAR2 ) IS
841: CURSOR l_cur IS
838: --
839: PROCEDURE get_product_description
840: (p_classification_id IN NUMBER, x_description OUT NOCOPY VARCHAR2 ) IS
841: CURSOR l_cur IS
842: SELECT * FROM pos_sup_products_services
843: WHERE classification_id = p_classification_id;
844: l_rec l_cur%ROWTYPE;
845: l_segment_values category_segment_record;
846: l_segment_value pos_sup_products_services.segment1%TYPE;
842: SELECT * FROM pos_sup_products_services
843: WHERE classification_id = p_classification_id;
844: l_rec l_cur%ROWTYPE;
845: l_segment_values category_segment_record;
846: l_segment_value pos_sup_products_services.segment1%TYPE;
847: BEGIN
848: assert_init();
849: OPEN l_cur;
850: FETCH l_cur INTO l_rec;
884: PROCEDURE get_product_description
885: (p_category IN VARCHAR2, x_description OUT NOCOPY VARCHAR2 ) IS
886:
887: l_segment_values category_segment_record;
888: l_segment_value pos_sup_products_services.segment1%TYPE;
889:
890: l_length NUMBER;
891: l_num_of_delim NUMBER;
892: l_index NUMBER;
930: --dbms_output.put_line(x_description);
931: END get_product_description;
932:
933: -- get the description of product and service for a row
934: -- in pos_sup_products_services table, and whether there
935: -- is a subcategories for the product and service
936: PROCEDURE get_desc_check_subcategory
937: (p_classification_id IN NUMBER,
938: x_description OUT NOCOPY VARCHAR2,
977: l_query VARCHAR2(4000);
978: BEGIN
979: assert_init();
980: l_query := 'select psps.vendor_id, mcb.category_id from mtl_categories_b mcb, ' ||
981: ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
982: ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
983: ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
984: ' and mcb.category_id = mcsvc.category_id and (';
985: FOR l_index IN 1..g_product_segment_count LOOP
1392:
1393:
1394:
1395: l_sql := 'SELECT classification_id
1396: FROM pos_sup_products_services
1397: WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1398: 'AND status <> '||''''||'X'||''''||
1399: 'AND vendor_id = '||x_vendor_id||
1400: 'union all
1425: end if;
1426:
1427:
1428: l_parent_sql := 'SELECT classification_id
1429: FROM pos_sup_products_services
1430: WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1431: 'AND '||l_next_seg||' is null '||
1432: 'AND status <> '||''''||'X'||''''||
1433: 'AND vendor_id = '||x_vendor_id||
1551:
1552: Cursor ps_code_cur is
1553: (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
1554: segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
1555: from pos_sup_products_services
1556: where classification_id = x_classification_id)
1557: UNION ALL
1558: (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
1559: segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
1595: p_ps_request_id IN NUMBER,
1596: x_has_subcategory OUT NOCOPY VARCHAR2)
1597: IS
1598: l_query VARCHAR2(3000);
1599: l_segment_value pos_sup_products_services.segment1%TYPE;
1600: l_last_not_null INTEGER;
1601: l_cur2 cursor_ref_type;
1602: l_count NUMBER;
1603: BEGIN
1857: BEGIN
1858:
1859: for i in 1..p_req_id_tbl.COUNT LOOP
1860:
1861: UPDATE POS_SUP_PRODUCTS_SERVICES
1862: SET status = p_status,
1863: last_updated_by = fnd_global.user_id,
1864: last_update_date = Sysdate,
1865: last_update_login = fnd_global.login_id