Sales order-Amount Modified-Advanced pricing associated SQL

Modify amount Consumption Details

--Revised amount consumption details 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 "Limit",--qlb.limit_balance_id, qlb.consumed_a Mount "Total consumption", Qlb.available_amount "Total available",--qlt.creation_date, Qlt.price_request_code, substr (QLT. Price_request_code, 5, 6) header_id, substr (Qlt.price_request_code, 6) line_id, qlt.amount "This time consumption", H Ou.name org_name, Ooh.order_number, Type.Name Order_type, type.description Type_desc, b.seg Ment1 | | '. ' | |              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_balancesQLB, 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 ')--AN D t.orig_org_id =--and ac.customer_name = ' Chen Juan ' 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 oo l.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

Order Association Pricing

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 "price adjustment", oo L.ordered_quantity "Quantity", Opa.adjusted_amount * ool.ordered_quantity "consumption", ql.pricing_phase_id "pricing stage", QL  . pricing_group_sequence "Period", ql.product_precedence "priority" 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.h eader_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 I N (' Apply_rebate_adjust ', ' apply_rebate_accrue ')--and Ool.flow_status_code = ' CLOSED ' and Ooh.order_number in (' 1             100000557 ',               ' 1300000054 ', ' 1200000104 ', ' 1100000558 ',                            ' 1100000559 ', ' 1100000560 ', ' 1100000561 ', ' 1100000562 ', ' 1100000563 ') ORDER by ooh.header_id, Ool.line_number, Qh.versio N_no

Sales Order Association Modification amount

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_i d) 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_adjus T ') * 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 O   oh.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_t ype_id = 1181--Order type 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

Sales order-Amount Modified-Advanced pricing associated SQL