price list information

化了半天时间才找到了PRICE_LIST INFORMATION ，后来才发现标准报表里有部分现成代码，贴上，以此纪念。             
                        
                         SELECT    (select  qlht.name from
                                  -- QP_LIST_HEADERS_B QLHB ,
                                      QP_LIST_HEADERS_TL QLHT
                                  where
                                  --qlhb.list_header_id=qlht.list_header_id
                                   --and
                                    qlht.list_header_id=qllv.list_header_id 
                                    and qlht.language='US'
                                  -- and rownum=1
                                   ) price_list_name,
                                   --QLHT.NAME   PRICE_LIST_NAME,
                                   qllv.PRODUCT_ATTRIBUTE_CONTEXT,
                                    (SELECT nvl(a.user_segment_name, a.seeded_segment_name)
                                    
                                      FROM   qp_segments_tl a, qp_segments_b b,
                                            qp_prc_contexts_b c, qp_pte_segments d
                                     WHERE
                                        -- c.prc_context_type = a_context_type
                                        -- AND    c.prc_context_code = a_context_code
                                             c.prc_context_id = b.prc_context_id
                                      AND    b.segment_mapping_column = QLLV.product_attribute
                                      AND    b.segment_id = a.segment_id
                                      AND    a.language = userenv('LANG')
                                      AND    b.segment_id = d.segment_id
                                       AND ROWNUM=1)
                                     ,
                                  qllv.product_attr_val_disp, 
                                 -- qllv.CUSTOMER_ITEM_ID,
                                  qllv.PRODUCT_UOM_CODE,
                                  qpfv.name,
                                 qllv.PRICING_ATTRIBUTE_CONTEXT,
                                  qllv.START_DATE_ACTIVE,
                                 qllv.END_DATE_ACTIVE, 
                                 qllv.OPERAND value,
                                  decode(qllv.LIST_LINE_TYPE_CODE,'PLL','Price List Line','PBH','Price Break Header') line_type,
                                 qllv.ARITHMETIC_OPERATOR,
                                 qllv.PRODUCT_PRECEDENCE,                               
                                 qllv.MODIFIER_LEVEL_CODE,                            
                                 qllv.PRICING_ATTRIBUTE,
                                 qllv.PRICING_ATTR_VALUE_FROM,
                                 qllv.PRICING_ATTR_VALUE_TO,
                                 qllv.LIST_PRICE,
                                 qllv.PERCENT_PRICE
                                 ,QLLV.organization_id
                                -- ,qllv.*
                                -- , QLHB.ORIG_ORG_ID
                               --  qllv.*                              
                              
                           FROM qp_list_lines_v  qllv ,
                                 qp_price_formulas_vl  qpfv
                               -- ,QP_LIST_HEADERS_B QLHB
                               --  ,QP_LIST_HEADERS_TL QLHT
                                
                          WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
                                 and qllv.price_by_formula_id=qpfv.price_formula_ID(+)
                               -- AND QLLV.list_header_id=QLHB.LIST_HEADER_ID
                               
                               --  AND QLHB.LIST_HEADER_ID=QLHT.LIST_HEADER_ID
                                -- AND QLHB.ORIG_ORG_ID=QLLV.organization_id
                                 and (
                                    qllv.end_date_active is NULL
                                   --   or
                                    or  qllv.end_date_active >SYSDATE
                                  )
                                  order by qllv.LIST_HEADER_ID,qllv.product_attr_val_disp;   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/356396/viewspace-683044/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/356396/viewspace-683044/