Yanmar - Discount on order lines check
Description
Detail Sales Order or Quote header report with line item details including status, cost, project and shipping information.
Run
Yanmar - Discount on order lines check and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.customer, x.account_number, x.order_number, decode((select ol.lookup_code from oe_lookups ol where ol.lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y' and upper(ol.lookup_code) = upper(x.sales_channel_code)),'DIRECT','Target Price','INDIRECT','LumpSum Discount') channel, x.ATTRIBUTE2 "Special Price", x.ATTRIBUTE3 "Lumpsum Amount", x.ATTRIBUTE4 "Additional Discount Pct", x.order_type, x.customer_po, hp1.party_name ship_to_customer, hca1.account_number ship_to_account, hcsua1.location ship_to_location, (select hz_format_pub.format_address(hps1.location_id,null,null,' , ') from dual) ship_to_address, ftv1.territory_short_name ship_to_country, hp2.party_name bill_to_customer, hca2.account_number bill_to_account, hcsua2.location bill_to_location, (select hz_format_pub.format_address(hps2.location_id,null,null,' , ') from dual) bill_to_address, ftv2.territory_short_name bill_to_country, x.ordered_date, x.price_list, x.header_status, x.currency, x.subtotal hdr_subtotal, nvl(x.line_charges_total,0)+nvl(x.header_charges,0) hdr_transport_charge, nvl(x.subtotal,0)+nvl(x.tax,0)+nvl(x.line_charges_total,0)+nvl(x.header_charges,0) total_inc_tax, x.payment_terms, x.invoice_number, x.invoiced_amount, x.warehouse, x.freight_terms, x.fob, x.shipment_priority, x.line, x.line_type, x.line_status, x.item, x.description, x.item_type, x.quantity, x.list_price, --1- (decode(x.extended_price,0,1) / decode(x.list_price,0,1)) discount_pct, (1 - (CASE WHEN (x.extended_price) = 0 THEN 100 ELSE (x.extended_price) END) / (CASE WHEN (x.list_price) = 0 THEN 100 ELSE (x.list_price) END)) * 100 discount_pct, x.discounted_price, x.unit_selling_price, x.extended_price, x.line_charges, x.tax_code, x.tax_amount, x.request_date, x.promise_date, x.schedule_ship_date, x.actual_shipment_date, x.shipped_quantity, x.delivery, nvl2(x.next_step,x.next_step,decode(x.next_step_code, 'SC','Ship Confirm/Close Trip Stop' )) next_step, x.project, x.task, x.header_created_by, x.header_creation_date, x.header_last_updated_by, x.header_last_update_date, x.line_created_by, x.line_creation_date, x.line_last_updated_by, x.line_last_update_date, x.order_category, x.line_category, x.header_id, x.line_id, x.line_number, x.line_days_late, x.line_quantity_short, xxen_util.meaning(x.line_shipped_flag,'YES_NO',0) line_shipped, x.order_date_type_code, x.orig_line_promise_date, x.orig_line_quantity, x.orig_line_delivery_lead_time, x.orig_line_actual_shipment_date, x.orig_line_tot_shipped_quantity, nvl(xxen_util.meaning(x.deliv_in_full,'YES_NO',0),'N/A') line_dif, nvl(xxen_util.meaning(x.deliv_on_time,'YES_NO',0),'N/A') line_dot, nvl(xxen_util.meaning(decode(x.deliv_in_full||x.deliv_on_time,null,null,'YY','Y','N'),'YES_NO',0),'N/A') line_difot, nvl(xxen_util.meaning(min(x.deliv_in_full) over (partition by x.header_id),'YES_NO',0),'N/A') orders_dif, nvl(xxen_util.meaning(min(x.deliv_on_time) over (partition by x.header_id),'YES_NO',0),'N/A') orders_dot, nvl(xxen_util.meaning(decode(min(x.deliv_in_full) over (partition by x.header_id)||min(x.deliv_on_time) over (partition by x.header_id),null,null,'YY','Y','N'),'YES_NO',0),'N/A') orders_difot from ( select /*+ push_pred(oolh) push_pred(oolh2) push_pred(wda) */ distinct haouv.name operating_unit, hp.party_name customer, hca.account_number, ooha.order_number, ooha.sales_channel_code, ooha.ATTRIBUTE2, ooha.ATTRIBUTE3, ooha.ATTRIBUTE4, nvl(ooha.quote_number,regexp_substr(ooha.orig_sys_document_ref,'^(\d+).',1,1,null,1)) quote_number, decode(ooha.source_document_type_id,10,'Requisitions',2,'Orders',16,'Quotes',7,'Incidents',(select oos0.name from oe_order_sources oos0 where ooha.source_document_type_id=oos0.order_source_id)) source_type, case ooha.source_document_type_id when 10 then (select prha.segment1 from po_requisition_headers_all prha where ooha.source_document_id=prha.requisition_header_id) when 2 then (select to_char(ooha0.order_number) from oe_order_headers_all ooha0 where ooha.source_document_id=ooha0.header_id) when 16 then (select aqha.quote_number||':'||aqha.quote_version from aso_quote_headers_all aqha where ooha.source_document_id=aqha.quote_header_id) when 7 then (select ciab.incident_number from cs_incidents_all_b ciab where ooha.source_document_id=ciab.incident_id) end source_document, decode(ooha.transaction_phase_code,'N','Quote','Order') type, ottt.name order_type, nvl(oola.cust_po_number,ooha.cust_po_number) customer_po, xxen_util.client_time(ooha.ordered_date) ordered_date, (select qlhv.name from qp_list_headers_vl qlhv where ooha.price_list_id=qlhv.list_header_id) price_list, jrrev.resource_name salesperson, max(jrrev2.resource_name) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_salesperson, oos.name order_source, ooha.orig_sys_document_ref order_source_reference, xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) header_status, ooha.transactional_curr_code currency, sum(decode(oola.cancelled_flag,'N',oola.extended_price)) over (partition by oola.header_id) subtotal, sum(decode(oola.cancelled_flag,'N',oola.tax_amount)) over (partition by oola.header_id) tax, sum(decode(oola.cancelled_flag,'N',oola.line_charges)) over (partition by oola.header_id) line_charges_total, (select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where ooha.header_id=opa.header_id and opa.line_id is null and opa.list_line_type_code='FREIGHT_CHARGE' and opa.applied_flag='Y') header_charges, (select rtv.name from ra_terms_vl rtv where nvl(oola.payment_term_id,ooha.payment_term_id)=rtv.term_id) payment_terms, max(rcta.trx_number) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_number, max(rcta.trx_date) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_date, xxen_util.meaning(max(rcta.status_trx) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6),'PAYMENT_SCHEDULE_STATUS',222) invoice_status, listagg(decode(rctla.line_type,'FREIGHT',null,rctla.line_number),', ') within group (order by decode(rctla.line_type,'FREIGHT',null,rctla.line_number)) over (partition by rctla.interface_line_attribute6) invoice_line, sum(rctla.extended_amount) over (partition by rctla.interface_line_attribute6) invoiced_amount, (select mp.organization_code from mtl_parameters mp where nvl(oola.ship_from_org_id,ooha.ship_from_org_id)=mp.organization_id) warehouse, xxen_util.meaning(nvl(oola.shipping_method_code,ooha.shipping_method_code),'SHIP_METHOD',3) ship_method, xxen_util.meaning(ooha.customer_preference_set_code,'REQUEST_DATE_TYPE',660) line_set, xxen_util.meaning(nvl(oola.freight_terms_code,ooha.freight_terms_code),'FREIGHT_TERMS',660) freight_terms, xxen_util.meaning(nvl(oola.fob_point_code,ooha.fob_point_code),'FOB',222) fob, xxen_util.meaning(nvl(oola.shipment_priority_code,ooha.shipment_priority_code),'SHIPMENT_PRIORITY',660) shipment_priority, nvl(oola.shipping_instructions,ooha.shipping_instructions) shipping_instructions, nvl(oola.packing_instructions,ooha.packing_instructions) packing_instructions, xxen_util.meaning(nvl(oola.payment_type_code,ooha.payment_type_code),'PAYMENT TYPE',660) payment_type, rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') line, ottt2.name line_type, xxen_util.meaning(oola.flow_status_code,'LINE_FLOW_STATUS',660) line_status, msiv.concatenated_segments item, msiv.description, xxen_util.meaning(oola.item_type_code,'ITEM_TYPE',660) item_type, oola.ordered_quantity quantity, oola.order_quantity_uom uom, oola.unit_list_price list_price, oola.discounted_price, oola.unit_selling_price, oola.extended_price, oola.line_charges, oola.tax_code, oola.tax_amount, xxen_util.meaning(oola.calculate_price_flag,'CALCULATE_PRICE_FLAG',660) calculate_price_flag, oola.pricing_quantity, oola.pricing_quantity_uom pricing_uom, oola.pricing_date, xxen_util.client_time(oola.request_date) request_date, xxen_util.client_time(oola.promise_date) promise_date, xxen_util.client_time(oola.schedule_ship_date) schedule_ship_date, xxen_util.client_time(oola.actual_shipment_date) actual_shipment_date, oola.shipped_quantity, xxen_util.meaning(decode(oola.shippable_flag,'Y','Y'),'YES_NO',0) shippable_flag, (select distinct listagg(os.set_name,', ') within group (order by os.set_name) over (partition by oola.line_id) set_name from oe_sets os where oola.ship_set_id=os.set_id) ship_set, wnd.name delivery, wda.next_step_code, xxen_util.meaning(wda.next_step_code,'NEXT_STEP',665) next_step, xxen_util.meaning(case when ooha.cancelled_flag='Y' or oola.cancelled_flag='Y' then 'Y' end,'YES_NO',0) cancelled_flag, xxen_util.client_time(oolh.hist_creation_date) cancel_date, xxen_util.user_name(oolh.hist_created_by) cancelled_by, xxen_util.meaning(oer.reason_code,'CANCEL_CODE',660) cancel_reason, decode(oola.cancelled_flag,'Y',oola.cancelled_quantity) cancelled_quantity, decode(oola.cancelled_flag,'Y',oola.cancelled_quantity)*oola.unit_selling_price cancelled_amount, ppa.project_number project, pt.task_number task, xxen_util.user_name(ooha.created_by) header_created_by, xxen_util.client_time(ooha.creation_date) header_creation_date, xxen_util.user_name(ooha.last_updated_by) header_last_updated_by, xxen_util.client_time(ooha.last_update_date) header_last_update_date, xxen_util.user_name(oola.created_by) line_created_by, xxen_util.client_time(oola.creation_date) line_creation_date, xxen_util.user_name(oola.last_updated_by) line_last_updated_by, xxen_util.client_time(oola.last_update_date) line_last_update_date, xxen_util.meaning(ooha.order_category_code,'ORDER_CATEGORY',660) order_category, xxen_util.meaning(oola.line_category_code,'ORDER_CATEGORY',660) line_category, ooha.header_id, oola.line_number, oola.shipment_number, oola.option_number, oola.component_number, oola.service_number, oola.line_id, nvl(oola.ship_to_org_id,ooha.ship_to_org_id) ship_to_org_id, nvl(oola.invoice_to_org_id,ooha.invoice_to_org_id) invoice_to_org_id, ooha.order_date_type_code, oola.split_line, oola.orig_line_id_ orig_line_id, case when oola.ordered_quantity<=0 or oola.is_shippable='N' then null when oola.shipped_quantity<0 then 'Y' else 'N' end line_shipped_flag, case when oola.ordered_quantity>0 and oola.is_shippable='Y' then greatest(trunc(decode(nvl(ooha.order_date_type_code,'SHIP'),'SHIP',nvl(oola.actual_shipment_date,sysdate),nvl(oola.actual_shipment_date,sysdate) + nvl(oola.delivery_lead_time,0)))-trunc(oola.promise_date),0) end line_days_late, case when oola.ordered_quantity>0 and oola.is_shippable='Y' then greatest(oola.ordered_quantity-nvl(oola.shipped_quantity,0),0) end line_quantity_short, coalesce(oolh2.delivery_lead_time,oola2.delivery_lead_time,oola.delivery_lead_time,0) orig_line_delivery_lead_time, xxen_util.client_time(trunc(coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date))) orig_line_promise_date, sum(oola.ordered_quantity) over (partition by oola.orig_line_id_) orig_line_quantity, xxen_util.client_time(trunc(coalesce(oolh2.actual_shipment_date,oola2.actual_shipment_date,oola.actual_shipment_date))) orig_line_actual_shipment_date, sum(oola.shipped_quantity) over (partition by oola.orig_line_id_) orig_line_tot_shipped_quantity, case when sum(oola.ordered_quantity) over (partition by oola.orig_line_id_)<=0 or oola.is_shippable='N' then null when sum(oola.ordered_quantity) over (partition by oola.orig_line_id_)<=nvl(sum(oola.shipped_quantity) over (partition by oola.orig_line_id_),0) then 'Y' else 'N' end deliv_in_full, case when sum(oola.ordered_quantity) over (partition by oola.orig_line_id_)<=0 or oola.is_shippable='N' then null when trunc(coalesce(oolh2.actual_shipment_date,oola2.actual_shipment_date,oola.actual_shipment_date,coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date)+1)) + decode(nvl(ooha.order_date_type_code,'SHIP'),'SHIP',0,nvl(coalesce(oolh2.delivery_lead_time,oola2.delivery_lead_time,oola.delivery_lead_time),0)) <= trunc(coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date)) then 'Y' else 'N' end deliv_on_time from hr_all_organization_units_vl haouv, oe_order_headers_all ooha, ( select nvl(oola.orig_line_id,oola.line_id) orig_line_id_, oola.* from ( select nvl( (select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.arithmetic_operator='NEWPRICE' and opa.list_line_type_code='DIS' and opa.applied_flag='Y'), --new price oola.unit_list_price- nvl(oola.unit_list_price*(select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand)/100 from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.arithmetic_operator='%' and opa.list_line_type_code='DIS' and opa.applied_flag='Y'),0)- --percentage discount nvl((select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.arithmetic_operator='AMT' and opa.list_line_type_code='DIS' and opa.applied_flag='Y'),0) --absolute amount discount ) discounted_price, decode(oola.line_category_code,'RETURN',-1,1)*oola.unit_selling_price*oola.ordered_quantity extended_price, decode(oola.line_category_code,'RETURN',-1,1)*oola.tax_value tax_amount, ( select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*decode(opa.arithmetic_operator,'LUMPSUM',case when oola.ordered_quantity>0 then opa.operand end,oola.ordered_quantity*opa.adjusted_amount)) line_charges from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.list_line_type_code='FREIGHT_CHARGE' and opa.applied_flag='Y' ) line_charges, max(oola.open_flag) over (partition by oola.header_id) max_open_flag |