Price adjustment query

Query1:
------

select ooh.order_number,


(SELECT SUM(NVL(ordered_quantity,0)*NVL(unit_selling_price,0))

FROM oe_order_lines_all

where header_id=ool.header_id

AND line_id=ool.line_id

and org_id=ool.org_id

)line_total,

SUM(NVL (ool.tax_value, 0)) tax_amount

,(SELECT NVL(SUM(NVL(opa.operand,0)),0)

FROM oe_price_adjustments opa

WHERE

opa.header_id = ooh.header_id

AND opa.line_id=ool.line_id

AND opa.applied_flag = 'Y'

AND opa.list_line_type_code = 'FREIGHT_CHARGE') Freight_charges,

(SELECT NVL(SUM(NVL (ool.pricing_quantity, 0) * NVL (opa.adjusted_amount, 0)),0)

FROM oe_price_adjustments opa

WHERE

opa.header_id = ool.header_id

AND opa.line_id=ool.line_id

AND opa.applied_flag = 'Y'

AND opa.list_line_type_code = 'DIS') Discount_amount

from oe_order_headers_all ooh,

oe_order_lines_all ool,

hz_cust_site_uses_all hcsua,

hz_cust_acct_sites_all hcasa,

hz_cust_accounts hca,

hz_parties hp,

ra_terms rt

,oe_transaction_types_tl ott

,ra_salesreps_all rs

-- ,oe_price_adjustments_v opa

where ooh.header_id=ool.header_id

AND ooh.org_id=ool.org_id

AND ooh.ship_to_org_id = hcsua.site_use_id

AND hcsua.cust_acct_site_id=hcasa.cust_acct_site_id

and hcasa.cust_account_id=hca.cust_account_id

and hca.party_id=hp.party_id

and hcsua.org_id=ooh.org_id

and ooh.payment_term_id=rt.term_id(+)

AND ooh.order_type_id = ott.transaction_type_id

AND ooh.salesrep_id=rs.salesrep_id(+)

AND ooh.org_id=rs.org_id

--AND opa.header_id(+) = ooh.header_id

--AND opa.applied_flag(+) = 'Y'

--AND opa.list_line_type_code(+) = 'FREIGHT_CHARGE'

AND ott.language='US'

AND ool.item_type_code <> 'CONFIG'

AND ool.top_model_line_id is null

--and ooh.order_number='3303592'

AND ooh.org_id=160

--and ooh.creation_date>sysdate-5

AND EXISTS(SELECT 1

FROM MTL_DESCR_ELEMENT_VALUES_V

WHERE inventory_item_id=ool.inventory_item_id

AND element_name='BM.Product'

AND element_value is not null)

group by

hcsua.attribute15,

ooh.transactional_Curr_code,

ooh.cust_po_number,

ooh.fob_point_code,

ool.tax_code,

rt.name,

ool.line_id,

hp.party_name,

hca.account_number,

ooh.order_number

,ott.name

,ooh.flow_status_code

,ool.flow_status_code

,rs.name

,ooh.orig_sys_document_ref

,ool.ordered_quantity

,unit_selling_price

,ool.header_id

,ool.org_id

,ool.pricing_quantity

, ooh.header_id

,ool.ordered_item

,ooh.request_date

,ool.request_date

,ool.schedule_ship_date

,ool.promise_date


Query2:
--------
select ooh.order_number "Order Number"


,hcsua.attribute15 "Ship To Reference Number",

ooh.transactional_Curr_code,

ooh.cust_po_number,

ooh.fob_point_code,

ooh.request_date,

ool.request_date,

ool.schedule_ship_date,

ool.promise_date,

ool.tax_code,

ool.ordered_item,

rt.name,

hp.party_name "Customer Name",

hca.account_number "Customer Number"

,ott.name "Transaction Type Name"

,ooh.flow_status_code "Order Status"

,ool.flow_status_code "Line Status"

,rs.name "Sales Person Name"

,ooh.orig_sys_document_ref

,ool.ordered_quantity

,(select SUM(NVL(ordered_quantity,0)*NVL(unit_selling_price,0)) from oe_order_lines_all where top_model_line_id=ool.top_model_line_id) Line_total

,(select SUM(NVL (tax_value, 0)) from oe_order_lines_all where top_model_line_id=ool.top_model_line_id) Line_tax_amount

,(select NVL(sum(operand),0) from oe_price_adjustments a,oe_order_lines_all b where a.line_id=b.line_id

and a.list_line_type_code='FREIGHT_CHARGE' and a.applied_flag(+) = 'Y'

and b.top_model_line_id=ool.top_model_line_id) freight_charges

,(select NVL(SUM(NVL (b.pricing_quantity, 0) * NVL (a.adjusted_amount, 0)),0) from oe_price_adjustments a,oe_order_lines_all b where a.line_id=b.line_id

and a.list_line_type_code='DIS' and a.applied_flag(+) = 'Y'

and b.top_model_line_id=ool.top_model_line_id) Discount_amount

,ool.line_id

from oe_order_headers_all ooh,

oe_order_lines_all ool,

hz_cust_site_uses_all hcsua,

hz_cust_acct_sites_all hcasa,

hz_cust_accounts hca,

hz_parties hp,

ra_terms rt

,oe_transaction_types_tl ott

,ra_salesreps_all rs

-- ,oe_price_adjustments_v opa

where ooh.header_id=ool.header_id

AND ooh.org_id=ool.org_id

AND ooh.ship_to_org_id = hcsua.site_use_id

AND hcsua.cust_acct_site_id=hcasa.cust_acct_site_id

and hcasa.cust_account_id=hca.cust_account_id

and hca.party_id=hp.party_id

and hcsua.org_id=ooh.org_id

and ooh.payment_term_id=rt.term_id(+)

AND ooh.order_type_id = ott.transaction_type_id

AND ooh.salesrep_id=rs.salesrep_id(+)

AND ooh.org_id=rs.org_id

AND ott.language='US'

AND ool.item_type_code = 'CONFIG'

--and ooh.order_number='3303308'

AND ooh.org_id=160

group by

hcsua.attribute15,

ooh.transactional_Curr_code,

ooh.cust_po_number,

ooh.fob_point_code,

ool.tax_code,

rt.name,

ool.line_id,

hp.party_name,

hca.account_number,

ooh.order_number

,ott.name

,ooh.flow_status_code

,ool.flow_status_code

,rs.name

,ooh.orig_sys_document_ref

,ool.ordered_quantity

,unit_selling_price

,ool.header_id

,ool.org_id

,ool.pricing_quantity

, ooh.header_id

,ool.ordered_item

,ool.top_model_line_id

,ool.link_to_line_id

,ooh.request_date

,ool.request_date

,ool.schedule_ship_date

,ool.promise_date