修改量消耗明细
--修改量消耗明细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