-- Mgt: Senfor Intl - Sales Order Book - Demand Planning Analysis
select
x.customer "Buying Group",
x.account_number "Cust. Account",
x.operating_unit "Business Unit",
case x.channel
when '-1' then 'Unassigned'
else x.channel
end "Market Channel",
x.order_number "Order Number",
--x.quote_number, --Ion removed 15dec2020
--x.source_type, --Ion removed 15dec2020
--x.source_document, --Ion removed 15dec2020
x.type "Transaction",
x.order_type "Order Type",
x.customer_po "Customer PO",
hp1.party_name "Ship To" ,
hca1.account_number "Ship To Account" ,
hcsua1.location "Ship To Location",
hps1.party_site_number "Ship To Site",
--(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" ,
hca2.account_number "Bill To Account",
hcsua2.location "Cust. Location" ,
--(select hz_format_pub.format_address(hps2.location_id,null,null,' , ') from dual) bill_to_address,
ftv2.territory_short_name "Bill To Country",
hcsua2.attribute20 "Sales Region",
x.invoice_salesperson "Sales Manager",
x.ordered_date "Order Date",
x.Year "Order Year",
x.Month "Order Month",
/* (select
(case x.ordered_date
when sysdate then (SELECT 'Current' FROM dual)
else (SELECT 'Future' FROM dual)
end)from dual) "Timeline", */
x.price_list "Price List",
--x.salesperson "Salesperson",
-- x.invoice_salesperson,
-- hcsua2.attribute20,
x.order_source "Order Source",
x.order_source_reference "Reference",
x.header_status "Order Status",
x.currency "Currency",
-- x.subtotal "Net Order",
-- x.tax "Tax",
--nvl(x.line_charges_total,0)+nvl(x.header_charges,0) charges,
-- nvl(x.subtotal,0)+nvl(x.tax,0)+nvl(x.line_charges_total,0)+nvl(x.header_charges,0) "Order Total",
x.payment_terms "Payment Terms",
x.warehouse "Warehouse",
--x.ship_method,
--x.line_set,
--x.freight_terms,
--x.fob,
--x.shipment_priority,
--x.shipping_instructions,
--x.packing_instructions,
--x.payment_type,
x.line "Line",
x.line_type "Line Type",
x.line_status "Line Status", -- Ion added 14dec2020
x.item "Product",
x.description "Description",
nvl(x.class, ' ') "Sales Class",
nvl(x.inventory_category, ' ') "Product Group",
x.item_type,
x.cust_item,
x.uom "UOM",
x.list_price "List Price",
x.discount "Discount",
x.discounted_price "Discounted List Price",
(nvl(Case x.currency
When 'EUR' then x.discounted_price
Else
Round(x.discounted_price * (select gdr.conversion_rate
from apps.gl_daily_rates gdr
where 1=1
and gdr.conversion_type = 'Corporate'
and gdr.from_currency = x.currency -- Replace with Order Currency
and gdr.to_currency = 'EUR' -- Always EUR
and gdr.conversion_date = trunc(x.ordered_date) -- Replace with Order Date
),2)
end,0) ) "EUR Disc. List Price",
x.surcharge "Core Price",
x.unit_selling_price "Unit Price",
x.quantity "Qty Ordered",
--nvl(x.quantity,0)*nvl(x.list_price - (x.list_price*x.discount/100),0) "Total Rebuild Value",
nvl(x.quantity*x.discounted_price,0) "Total Rebuild Value",
nvl(Case x.currency
When 'EUR' then nvl(x.quantity,0)*nvl(x.list_price - (x.list_price*x.discount/100),0)
Else
Round((nvl(x.quantity,0)*nvl(x.list_price - (x.list_price*x.discount/100),0) * (select gdr.conversion_rate
from apps.gl_daily_rates gdr
where 1=1
and gdr.conversion_type = 'Corporate'
and gdr.from_currency = x.currency -- Replace with Order Currency
and gdr.to_currency = 'EUR' -- Always EUR
and gdr.conversion_date = trunc(x.ordered_date) -- Replace with Order Date
)),2)
end,0) "EUR Book Revenue",
nvl((x.surcharge*x.quantity),0) "Total Core Value",
nvl(x.qty_shipped,0) "Qty Shipped", --ION 22dec To add total qty shipped
/*
(nvl(Case x.currency
When 'EUR' then x.discounted_price*x.qty_shipped
Else
Round(x.discounted_price * (select gdr.conversion_rate
from apps.gl_daily_rates gdr
where 1=1
and gdr.conversion_type = 'Corporate'
and gdr.from_currency = x.currency -- Replace with Order Currency
and gdr.to_currency = 'EUR' -- Always EUR
and gdr.conversion_date = trunc(x.ordered_date) -- Replace with Order Date
),2)
end,0) * x.qty_shipped) "EUR Shipped Revenue",*/
(nvl(Case x.currency
When 'EUR' then x.discounted_price
Else
Round(x.discounted_price * (select gdr.conversion_rate
from apps.gl_daily_rates gdr
where 1=1
and gdr.conversion_type = 'Corporate'
and gdr.from_currency = x.currency -- Replace with Order Currency
and gdr.to_currency = 'EUR' -- Always EUR
and gdr.conversion_date = trunc(x.ordered_date) -- Replace with Order Date
),2)
end,0))*x.qty_shipped "EUR Shipped Revenue",
nvl(x.quantity,0)-nvl(qty_shipped,0) "Open Qty",
nvl(x.qty_invoiced,0) "Invoiced Qty",
nvl(x.extended_amount,0) "Invoice Amount",
nvl(Case x.currency
When 'EUR' then x.revenue_amount
Else
Round(x.revenue_amount * (select gdr.conversion_rate
from apps.gl_daily_rates gdr
where 1=1
and gdr.conversion_type = 'Corporate'
and gdr.from_currency = x.currency -- Replace with Order Currency
and gdr.to_currency = 'EUR' -- Always EUR
and gdr.conversion_date = trunc(x.ordered_date) -- Replace with Order Date
),2)
end,0) "EUR Invoiced Revenue",
-- nvl(x.reserved,0) "Qty Reserved",
--x.line_charges, ION 15dec
nvl(x.tax_code, ' ') "Tax Code",
x.tax_amount "Tax",
--x.calculate_price_flag,
x.pricing_quantity "Pricing Qty",
x.pricing_uom "Pricing UOM",
x.pricing_date "Pricing Date",
x.request_date "Requested Date",
x.request_year "Requested Year",
x.request_month "Requested Month",
x.promise_date "Date Promised",
x.schedule_ship_date "Scheduled Date"
,
/* (select
(case x.actual_shipment_date
when (to_char(x.actual_shipment_date, 'DD/MM/YYYY' > sysdate )
then (SELECT 'Current' FROM dual)
else (SELECT 'Future' FROM dual)
end)from dual) "Timeline",*/
nvl(to_char(x.actual_shipment_date), ' ') "Date Shipped", --ION15dec
nvl(to_char(x.ship_year), ' ') "Shipped Year",
nvl(to_char(x.ship_month), ' ') "Shipped Month",
-- x.qty_shipped,
--x.shipment_priority,
--x.shippable_flag,
--x.ship_set, --ION15dec
nvl(to_char(x.delivery), ' ') "Delivery",
nvl(to_char(x.invoice_number), ' ') "Invoice no.",
nvl(to_char(x.invoice_date), ' ') "Invoice Date",
nvl(to_char(x.inv_year), ' ') "Invoice Year",
nvl(to_char(x.inv_month), ' ') "Invoice Month",
--x.invoice_status,
nvl(to_char(x.invoice_line), ' ') "Invoice_Line"
--x.project,
--x.task,
--x.created_by,
--x.creation_date,
--x.last_updated_by,
--x.last_update_date,
--x.order_category,
--x.line_category
--,
--, x.header_id, --36224
-- x.line_id --345244
-- x.line_number
from
(
select
hp.party_name customer,
hca.account_number,
ooha.order_number,
haouv.name operating_unit,
--ION Delete
--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 apps.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 apps.po_requisition_headers_all prha where ooha.source_document_id=prha.requisition_header_id)
--when 2 then (select to_char(ooha0.order_number) from apps.oe_order_headers_all ooha0 where ooha.source_document_id=ooha0.header_id)
--when 16 then (select aqha.quote_number||':'||aqha.quote_version from apps.aso_quote_headers_all aqha where ooha.source_document_id=aqha.quote_header_id)
--when 7 then (select ciab.incident_number from apps.cs_incidents_all_b ciab where ooha.source_document_id=ciab.incident_id)
--end source_document, ----Ion removed 14dec2020
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,
ooha.sales_channel_code channel,
ooha.ordered_date ordered_date,
Extract(month from ooha.ordered_date) Month,
Extract(year from ooha.ordered_date) Year,
(select qlhv.name
from apps.qp_list_headers_vl qlhv
where ooha.price_list_id=qlhv.list_header_id) price_list,
--jrrev.resource_name salesperson,
jrrev2.resource_name invoice_salesperson,--Ion removed 14dec2020
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.flow_status_code as header_status, -- Ion added 14dec2020
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 nvl(sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand),0)
from apps.oe_price_adjustments opa
where ooha.header_id=opa.header_id
and oola.line_id = opa.line_id
and opa.list_line_type_code='SUR'
and opa.applied_flag='Y') Surcharge,
(select nvl(sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand),0)
from apps.oe_price_adjustments opa
where ooha.header_id=opa.header_id
and oola.line_id = opa.line_id
and opa.list_line_type_code='DIS'
and opa.applied_flag='Y'
and opa.arithmetic_operator = '%') Discount,
(select decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand
from apps.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,
-- To calculate discounted price
nvl(
(select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand)
from apps.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 apps.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 apps.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,
/*from
apps.oe_order_lines_all oola
where
oola.line_id in (select opa.line_id
from apps.oe_price_adjustments opa
where opa.arithmetic_operator='NEWPRICE'
and opa.list_line_type_code='DIS'
and opa.applied_flag='Y')) discounted_price,*/
(select rtv.name
from apps.ra_terms_vl rtv
where nvl(oola.payment_term_id,ooha.payment_term_id)=rtv.term_id) payment_terms,
/* Invoice Details */
rcta.trx_number invoice_number,
rcta.trx_date invoice_date,
Extract(month from rcta.trx_date) inv_month,
Extract(year from rcta.trx_date) inv_year,
rctla.reason_code,
--xxen_util.meaning(rcta.status_trx,'PAYMENT_SCHEDULE_STATUS',222) invoice_status,
decode(rctla.line_type,'FREIGHT',null,rctla.line_number) invoice_line,
rctla.extended_amount, --Ion--
rctla.revenue_amount,
(select mp.organization_code
from apps.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,
oola.flow_status_code as line_status, -- Ion added 14dec2020
(SELECT distinct
mc.CONCATENATED_SEGMENTS
FROM
apps.mtl_item_categories mic,
apps.mtl_category_sets_tl mcst,
apps.mtl_category_sets_b mcs,
apps.mtl_categories_b_kfv mc,
apps.mtl_system_items_b msiv
WHERE mic.category_set_id = mcs.category_set_id
AND mcs.category_set_id = mcst.category_set_id
AND mcst.LANGUAGE = USERENV ('LANG')
AND mic.category_id = mc.category_id
AND msiv.organization_id = oola.ship_from_org_id
AND msiv.organization_id = mic.organization_id
AND msiv.inventory_item_id = mic.inventory_item_id
and msiv.segment1 = oola.ordered_item
and mcst.category_set_name='Inv.Items') Class,
--'Planning-Product Group') planning_category,
(SELECT distinct
mc1.CONCATENATED_SEGMENTS
FROM
apps.mtl_item_categories mic1,
apps.mtl_category_sets_tl mcst1,
apps.mtl_category_sets_b mcs1,
apps.mtl_categories_b_kfv mc1,
apps.mtl_system_items_b msiv1
WHERE mic1.category_set_id = mcs1.category_set_id
AND mcs1.category_set_id = mcst1.category_set_id
AND mcst1.LANGUAGE = USERENV ('LANG')
AND mic1.category_id = mc1.category_id
AND msiv1.organization_id = oola.ship_from_org_id
AND msiv1.organization_id = mic1.organization_id
AND msiv1.inventory_item_id = mic1.inventory_item_id
and msiv1.segment1 = oola.ordered_item
and mcst1.category_set_name='Purchasing') inventory_category,
msiv.concatenated_segments item,
msiv.description,
oola.item_type_code item_type,
--xxen_util.meaning(oola.item_type_code,'ITEM_TYPE',660) item_type,
oola.ordered_quantity quantity,
oola.order_quantity_uom uom,
oola.unit_selling_price,
oola.extended_price,
oola.unit_list_price list_price,
--oola.line_charges, --ION 15dec
-- (oola.ordered_quantity-sum(oola.shipped_quantity) over (partition by ooha.order_number, oola.line_number, oola.shipment_number)) open_qty, --ION 22dec To add total qty shipped
oola.tax_code,
oola.tax_amount,
--xxen_util.meaning(oola.calculate_price_flag,'CALCULATE_PRICE_FLAG',660) calculate_price_flag,
oola.pricing_quantity,
/* --ION Need to add reservations */
/*
(select distinct
sum(mr.primary_reservation_quantity) over (partition by mr.inventory_item_id, mr.organization_id, mr.demand_source_line_id)
--mr.inventory_item_id,
--mr.organization_id,
--mr.subinventory_code
from
apps.mtl_reservations mr
where
mr.organization_id = nvl(oola.ship_from_org_id,ooha.ship_from_org_id)
and mr.inventory_item_id = oola.inventory_item_id
and mr.demand_source_line_id = oola.line_id
and mr.subinventory_code = (select distinct mp.organization_code
from apps.mtl_parameters mp
where nvl(oola.ship_from_org_id,ooha.ship_from_org_id)=mp.organization_id)
) reserved,
*/
oola.pricing_quantity_uom pricing_uom,
oola.pricing_date,
--xxen_util.client_time(
oola.request_date request_date,
Extract(month from oola.request_date) request_month,
Extract(year from oola.request_date) request_year,
--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, --ION15dec
Extract(month from oola.actual_shipment_date) ship_month,
Extract(year from oola.actual_shipment_date) ship_year,
sum(oola.shipped_quantity) over (partition by ooha.order_number, oola.line_number, oola.shipment_number) qty_shipped, --ION 22dec To add total qty shipped
sum(oola.invoiced_quantity) over (partition by ooha.order_number, oola.line_number, oola.shipment_number) qty_invoiced, --ION 4feb To add total qty invoiced
--xxen_util.meaning(decode(oola.shippable_flag,'Y','Y'),'YES_NO',0) shippable_flag,
/* Get delivery Details*/
--(select distinct listagg(os.set_name,', ') --ION15dec
-- within group (order by os.set_name) over (partition by oola.line_id) set_name ION 15dec
-- from apps.oe_sets os where oola.ship_set_id=os.set_id) ship_set, wnd.name delivery, ION15dec
wda.delivery_id delivery,
/* Get project details */
--ppa.project_number project,
--pt.task_number task,
--xxen_util.user_name(oola.created_by) created_by,
--xxen_util.client_time(oola.creation_date) creation_date,
--xxen_util.user_name(oola.last_updated_by) last_updated_by,
--xxen_util.client_time(oola.last_update_date) 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.attribute1,
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,
(select
--oola1.ordered_item as Remy_item,
oola2.ordered_item --as Customer_item
from --apps.oe_order_lines_all oola1,
apps.oe_order_lines_all oola2,
apps.mtl_system_items_vl msiv
where 1=1
--and ooha.org_id = oola1.org_id
--and ooha.header_id = oola1.header_id
--and ooha.org_id = oola1.org_id
--and ooha.header_id = oola1.header_id
AND oola.top_model_line_id = oola2.line_id(+)
AND oola.inventory_item_id = msiv.inventory_item_id
AND oola.ship_from_org_id = msiv.organization_id
) cust_item
from
apps.hr_all_organization_units_vl haouv,
apps.oe_order_headers_all ooha,
(
select
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
apps.oe_price_adjustments opa
where
oola.item_type_code != 'INCLUDED'
and --Ion Exclude Included items
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,
oola.*
from
apps.oe_order_lines_all oola
) oola,
apps.oe_transaction_types_tl ottt,
apps.oe_transaction_types_tl ottt2,
apps.mtl_system_items_vl msiv,
apps.hz_cust_accounts hca,
apps.hz_parties hp,
apps.oe_order_sources oos,
--apps.jtf_rs_salesreps jrs,
apps.jtf_rs_salesreps jrs2,
--apps.jtf_rs_resource_extns_vl jrrev,
apps.jtf_rs_resource_extns_vl jrrev2,
(
select distinct
wdd.source_line_id,
min(wda.delivery_id) over (partition by wdd.source_line_id, wda.delivery_id) delivery_id
from
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda
where
wdd.source_code='OE' and
wdd.delivery_detail_id=wda.delivery_detail_id
) wda,
apps.wsh_new_deliveries wnd,
-- apps.wsh_delivery_details wdd,
-- apps.wsh_delivery_assignments wda,
--(
--select ppa.project_id, ppa.segment1 project_number from apps.pa_projects_all ppa union
--select psm.project_id, psm.project_number from apps.pjm_seiban_numbers psm
--) ppa,
--&xrrpv_table
--apps.pa_tasks pt,
apps.ra_customer_trx_lines_all rctla,
apps.ra_customer_trx_all rcta
where
2=2
and oola.item_type_code != 'INCLUDED' --Ion to exclude INCLUDED items
and haouv.organization_id=ooha.org_id
and ooha.sold_to_org_id=hca.cust_account_id(+)
and hca.party_id=hp.party_id(+)
and ooha.order_type_id=ottt.transaction_type_id(+)
and ottt.language(+)=userenv('lang')
and ooha.order_source_id=oos.order_source_id(+)
and ooha.header_id=oola.header_id(+)
and oola.line_type_id=ottt2.transaction_type_id(+)
and ottt2.language(+)=userenv('lang')
and oola.inventory_item_id=msiv.inventory_item_id(+)
and oola.ship_from_org_id=msiv.organization_id(+)
and ooha.salesrep_id=jrs2.salesrep_id
and ooha.org_id=jrs2.org_id
and jrs2.resource_id=jrrev2.resource_id(+)
and oola.line_id=wda.source_line_id(+)
and wda.delivery_id=wnd.delivery_id(+)
-- and wda.delivery_detail_id = wdd.delivery_detail_id(+)
-- and wdd.organization_id = oola.ship_from_org_id
and ooha.transaction_phase_code = 'F' --and orders only, no quotes
/* and ottt.name in ('CBL INVOICING - RMY BE',
'EXPRESS - RMY BE',
'EXPRESS - RMY UK',
'REBILL-CREDIT - RMY BE',
'REBILL-CREDIT - RMY UK',
'STANDARD - RMY BE',
'STANDARD - RMY HU',
'STANDARD - RMY UK',
'WARRANTY- no rcpt RMY BE',
'WARRANTY- no rcpt RMY UK',
'WARRANTY- with rcpt RMY BE',
'WARRANTY- with rcpt RMY UK') --Order Type
*/
and ooha.flow_status_code not in ('CANCELLED', 'ENTERED') --Order Status
/* and ottt2.name not in ('BILL & AUTO INVENTORY-RMY BE', --Line Type
'CORE CREDIT ONLY - RMY BE',
'CORE CREDIT ONLY - RMY UK',
'CORE CREDIT ONLY - RMY HU',
'SHIP ONLY-RMY UK')
*/
and oola.flow_status_code not in ('CANCELLED', 'ENTERED') --Line Status
--and ottt2.name not in ('CORE CREDIT ONLY - RMY BE', 'CREDIT ONLY - RMY BE', 'CREDIT ONLY - RMY UK', 'RETURN - RMY BE', 'RETURN - RMY UK')
--oola.project_id=ppa.project_id(+) and
--oola.task_id=pt.task_id(+)
and to_char(oola.line_id)=rctla.interface_line_attribute6(+)
and rctla.interface_line_context(+) in ('ORDER ENTRY')
and rctla.customer_trx_id=rcta.customer_trx_id(+)
and rcta.primary_salesrep_id=jrs2.salesrep_id(+)
and rcta.org_id=jrs2.org_id(+)
and rctla.attribute3 is null
and (interface_line_attribute5 =0 or interface_line_attribute5 is null)
--and rctla.reason_code <> 'BONUS'
--and jrs2.resource_id=jrrev2.resource_id(+)
--and rcta.trx_number = '8482000984' --Ion--
-- and ooha.order_number in(41005014, 41005262, 41009387)
--41002399
--41002148
--, 41003357
--and trunc(ooha.ordered_date) = trunc(sysdate,'month')
) x,
apps.hz_cust_site_uses_all hcsua1,
apps.hz_cust_site_uses_all hcsua2,
apps.hz_cust_acct_sites_all hcasa1,
apps.hz_cust_acct_sites_all hcasa2,
apps.hz_cust_accounts hca1,
apps.hz_cust_accounts hca2,
apps.hz_parties hp1,
apps.hz_parties hp2,
apps.hz_party_sites hps1,
apps.hz_party_sites hps2,
apps.hz_locations hl1,
apps.hz_locations hl2,
apps.fnd_territories_vl ftv1,
apps.fnd_territories_vl ftv2
where
1=1
and x.ship_to_org_id=hcsua1.site_use_id(+)
and x.invoice_to_org_id=hcsua2.site_use_id(+)
and hcsua1.cust_acct_site_id=hcasa1.cust_acct_site_id(+)
and hcsua2.cust_acct_site_id=hcasa2.cust_acct_site_id(+)
and hcasa1.cust_account_id=hca1.cust_account_id(+)
and hcasa2.cust_account_id=hca2.cust_account_id(+)
and hca1.party_id=hp1.party_id(+)
and hca2.party_id=hp2.party_id(+)
and hcasa1.party_site_id=hps1.party_site_id(+)
and hcasa2.party_site_id=hps2.party_site_id(+)
and hps1.location_id=hl1.location_id(+)
and hps2.location_id=hl2.location_id(+)
and hl1.country=ftv1.territory_code(+)
and hl2.country=ftv2.territory_code(+)
order by
x.operating_unit,
x.ordered_date,
x.account_number,
x.order_number,
x.line_number,
x.shipment_number,
nvl(x.option_number,-1),
nvl(x.component_number,-1),
nvl(x.service_number,-1) |
