销售订单-修改量-高级定价关联sql

修改量消耗明细

--修改量消耗明细
SELECT t.name,
       t.comments,
       t.version_no,
       cux_rebate_pub.get_hou_name(p_organization_id => t.orig_org_id) org_name,
       ac.customer_name,
       --ql.amount "限额",
       -- qlb.limit_balance_id,
       qlb.consumed_amount  "总消耗",
       qlb.available_amount "总可用",
       --qlt.creation_date,
       qlt.price_request_code,
       substr(qlt.price_request_code, 5, 6) header_id,
       substr(qlt.price_request_code, 12, 6) line_id,
       qlt.amount "本次消耗",
       hou.name org_name,
       ooh.order_number,
       
       type.name order_type,
       type.description type_desc,
       b.segment1 || '.' || b.segment2 item_code,
       b.description item_desc,
       decode(ool.line_category_code,
              'ORDER',
              ool.ordered_quantity,
              'RETURN',
              -1 * ool.ordered_quantity,
              ool.ordered_quantity) ordered_quantity
  FROM qp_list_headers_all          t,
       qp_limits                    ql,
       qp_limit_balances            qlb,
       qp_limit_transactions        qlt,
       ar_customers                 ac,
       oe_order_headers_all         ooh,
       oe_order_lines_all           ool,
       oe_transaction_types_tl      TYPE,
       mtl_system_items_b           b,
       org_organization_definitions ood,
       hr_operating_units           hou
 WHERE 1 = 1
   AND t.list_header_id = ql.list_header_id
   AND ql.list_line_id = -1
   AND ql.limit_id = qlb.limit_id
   AND qlb.limit_balance_id = qlt.limit_balance_id
   AND to_char(ac.customer_id) = t.attribute1
   AND t.version_no IN ('APPLY_REBATE_ACCRUE', 'APPLY_REBATE_ADJUST')
      --AND t.orig_org_id = 86
   --AND ac.customer_name = '陈娟'
   AND t.NAME = 'CUST2530.PZ'
   AND ooh.header_id = substr(qlt.price_request_code, 5, 6)
   AND ool.line_id = substr(qlt.price_request_code, 12, 6)
   AND ooh.header_id = ool.header_id
   AND type.transaction_type_id = ooh.order_type_id
   AND type.language = 'ZHS'
   AND ool.inventory_item_id = b.inventory_item_id
   AND b.organization_id = ood.organization_id
   AND ood.organization_code = 'ZZZ'
   AND hou.organization_id = ooh.org_id
 ORDER BY qlt.price_request_code

订单关联定价

SELECT ooh.order_number,
       ooh.header_id,
       ool.line_number,
       ool.line_id,
       ool.flow_status_code,
       ool.ordered_quantity,
       qh.name,
       qh.description,
       qh.version_no,
       opa.adjusted_amount "调整单价",
       ool.ordered_quantity "数量",
       opa.adjusted_amount * ool.ordered_quantity "消耗",
       ql.pricing_phase_id "定价阶段",
       ql.pricing_group_sequence "时段",
       ql.product_precedence "优先级"
  FROM oe_order_lines_all   ool,
       oe_order_headers_all ooh,
       oe_price_adjustments opa,
       qp_list_headers_all  qh,
       qp_list_lines        ql
 WHERE 1 = 1
   AND ooh.header_id = ool.header_id
   AND opa.header_id = ooh.header_id
   AND opa.line_id = ool.line_id
   AND opa.list_header_id = qh.list_header_id
   AND opa.list_line_id = ql.list_line_id
   AND qh.list_header_id = ql.list_header_id
   AND qh.version_no IN ('APPLY_REBATE_ADJUST', 'APPLY_REBATE_ACCRUE')
      -- AND ool.flow_status_code = 'CLOSED'
   AND ooh.order_number IN ('1100000557',
                            '1300000054',
                            '1200000104',
                            '1100000558',
                            '1100000559',
                            '1100000560',
                            '1100000561',
                            '1100000562',
                            '1100000563')
 ORDER BY ooh.header_id, ool.line_number, qh.version_no

销售订单关联修改量

SELECT ooh.org_id,
       cux_rebate_pub.get_hou_name(p_organization_id => ooh.org_id) org_name,
       ooh.header_id,
       ooh.order_number,
       ooh.creation_date,
       cux_om_pub.get_order_type_name(p_order_type_id => ooh.order_type_id) order_type,
       hca.account_number customer_number,
       hp.party_name customer_name,
       ooh.attribute1 cux_order_number,
       ool.line_number || '.' || ool.shipment_number line_num,
       msi.concatenated_segments item_name,
       msi.description item_dsp,
       ool.ordered_quantity,
       ool.unit_selling_price,
       cux_om_pub.get_adjusted_amt(p_oe_line_id => ool.line_id,
                                   p_version_no => 'APPLY_REBATE_ACCRUE') *
       ool.ordered_quantity line_accrue_mt,
       cux_om_pub.get_adjusted_amt(p_oe_line_id => ool.line_id,
                                   p_version_no => 'APPLY_REBATE_ADJUST') *
       ool.ordered_quantity line_adjust_amt,
       mp.organization_code,
       ool.flow_status_code
  FROM oe_order_headers_all ooh,
       oe_order_lines_all   ool,
       hz_cust_accounts     hca,
       hz_parties           hp,
       mtl_system_items_vl  msi,
       mtl_parameters       mp
 WHERE 1 = 1
   AND ooh.header_id = ool.header_id
   AND ooh.sold_to_org_id = hca.cust_account_id
   AND hca.party_id = hp.party_id
   AND ool.ship_from_org_id = msi.organization_id
   AND ool.inventory_item_id = msi.inventory_item_id
   AND ool.ship_from_org_id = mp.organization_id
      --AND ooh.order_type_id = 1181 --订单类型为PZ_SBLP_ORDER_TYPE
  /* AND cux_om_pub.get_order_type_name(p_order_type_id => ooh.order_type_id) LIKE
       '%SBLP%'*/
--AND ooh.header_id = 811502
 ORDER BY ooh.org_id,
          ooh.order_number,
          ool.line_number,
          ool.shipment_number