ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
8E2FF36EDE7F79D2E0530100007F1FF2
AR Account Number
Y
Y
select
hca.account_number value,
hp.party_name||' ('||initcap(hp.party_type)||')' description
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
(
:$flex$.operating_unit is null or
hca.cust_account_id in (
select
hcasa.cust_account_id
from
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa
where
xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and
haouv.organization_id=hcasa.org_id
)
)
order by
hp.party_name
US
8E2FF36EDE8679D2E0530100007F1FF2
AR Customer Name
Y
select
hp.party_name value,
hca.account_number||' ('||initcap(hp.party_type)||')' description
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
(
:$flex$.operating_unit is null or
hca.cust_account_id in (
select
hcasa.cust_account_id
from
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa
where
xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and
haouv.organization_id=hcasa.org_id
)
)
order by
hp.party_name
US
8E2FF36EDEB979D2E0530100007F1FF2
HR Operating Unit
Y
select
hou.name value,
null description
from
hr_operating_units hou
where
sysdate between hou.date_from and nvl(hou.date_to,sysdate) and
(:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.name
Fixed where clause duplication
AR
وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC)
D
Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC)
E
Unidad operativa restringida por el control de acceso multiorgánico (MOAC)
F
Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC)
I
Unità operativa limitata dal controllo di accesso multiorgano (MOAC)
JA
多機関アクセス制御(MOAC)で制限された操作ユニット
KO
다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위
PTB
Unidade operacional restrita por controle de acesso multi org (MOAC)
RU
Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC)
S
Styrenhet begränsad av multi-org access control (MOAC)
TR
Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi
US
Operating unit restricted by multi org access control (MOAC)
ZHS
受多机构访问控制（MOAC）限制的操作单元。
8E2FF36EDEF179D2E0530100007F1FF2
INV Item
Y
Y
select
msiv.concatenated_segments value,
msiv.description||' ('||mp.organization_code||')' description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
(
:$flex$.organization_code is null and (
fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or
msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')
) or
xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y'
) and
msiv.organization_id=mp.organization_id
order by
mp.organization_code,
msiv.concatenated_segments
US
Inventory items, restricted to the accessible inventory organizations
8E2FF36EDF0A79D2E0530100007F1FF2
ONT Line Status
Y
select
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='LINE_FLOW_STATUS' and
flvv.view_application_id=660 and
flvv.security_group_id=0
order by
flvv.meaning
US
8E2FF36EDEF479D2E0530100007F1FF2
ONT Line Type
Y
select
ottt.name value,
xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description
from
oe_transaction_types_all otta,
oe_transaction_types_tl ottt
where
(:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
otta.transaction_type_code='LINE' and
otta.transaction_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
decode(otta.order_category_code,'ORDER',1,'MIXED',2,3),
ottt.name
US
8E2FF36EDEE779D2E0530100007F1FF2
ONT Order Number
Y
Y
select
ooha.order_number value,
ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description
from
oe_order_headers_all ooha,
oe_transaction_types_tl ottt,
hz_cust_accounts hca
where
nvl(ooha.transaction_phase_code,'F')='F' and
(:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
ooha.sold_to_org_id=hca.cust_account_id and
ooha.order_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
ooha.order_number desc
Fixed bug in operating unit access restriction when the default operating unit is set to something else
US
8E2FF36EDF0979D2E0530100007F1FF2
ONT Order Status
Y
select
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='FLOW_STATUS' and
flvv.view_application_id=660 and
flvv.security_group_id=0
order by
flvv.meaning
US
8E2FF36EDEE879D2E0530100007F1FF2
ONT Order Type
Y
select
ottt.name value,
xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description
from
oe_transaction_types_all otta,
oe_transaction_types_tl ottt
where
(:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
otta.transaction_type_code='ORDER' and
otta.transaction_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
decode(otta.order_category_code,'ORDER',1,'MIXED',2,3),
ottt.name
US
8E2FF36EDF3979D2E0530100007F1FF2
ONT Quote Number
Y
Y
select
nvl(ooha.quote_number,regexp_substr(ooha.orig_sys_document_ref,'^(\d+).',1,1,null,1)) value,
ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description
from
oe_order_headers_all ooha,
oe_transaction_types_tl ottt,
hz_cust_accounts hca
where
(ooha.quote_number is not null or ooha.orig_sys_document_ref is not null and ooha.source_document_type_id=16) and
(:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
ooha.sold_to_org_id=hca.cust_account_id and
ooha.order_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
ooha.order_number desc
Fixed bug in operating unit access restriction when the default operating unit is set to something else
US
33464264788FEC38E06369FB0905D155
-- 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)
Y
#,##0.00;[Red]-#,##0.00
B8B2F4B3E08120E2E05347D8A10ADA58
US
Mgt: Senfor Intl - Sales Order Book - Demand Planning Analysis
This report extracts details of Sales Orders and Lines according to the parameters entered.
Kit items with an item type of "INCLUDED" are excluded.
Pivot tables and templates can be added to produce Analysis dashboards. This example contains a pivot table by Business Unit and Customer with a dashboard configued. Data fields, parameters and other templates may be added or removed according to requirements. Please contact the author, iyilmaz@senfor-intl.eu, if you require assistance.
Version Modified on Modified by Description
===== ======== == ====== ========
1.0 15 Dec 2020 Ion Yılmaz Initial Release
1.1 24 Jul 2021 Ion Yilmaz Added the SFG number for planning.
1.2 18 Aug 2021 Ion Yilmaz Changed logic for finding the SFG and the item categories to look for Kits and PTO items.
1.3 25 Aug.2021 Ion Yilmaz Added Demand Class, Product Family and Product Class plus Order date parameters
1.4 18 Nov 2021 Ion Yılmaz Added restriction "and rctla.warehouse_id is not null" to x in order that duplicate lines are not created by credits and rebills.
1.5 7 Jul 2024 Ion Yılmaz Released program for general use.
Copyright 2020 Senfor International Yönetim Danışmanlık Hizmetleri Ltd. Şti.
Original Author: İon Yılmaz (iyilmaz@senfor-intl.com.tr) Senfor International, www.senfor-intl.eu
All rights reserved. Permission to use this code is granted provided the original author is acknowledged.
This code is made available on an "As-Is" basis and may require modification depending upon the configuration of the system it is being used on.
The author does not assume or hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause. |
Parameters:
===========
Order Number: Select the specific sales order(s) to be included (Optional).
Quote Number: Not currently used.
Customer Name: Select the specific customer(s) to be included (Optional).
Account Number: Select the specific customer account(s) to be included (Optional).
Type: Not currently used.
Order Category: Select the order category code(s) to be included (Optional).
Line Category: Select the specific line category code(s) to be included (Optional).
Order Type: Select the order types(s) to be included (Optional).
LineType: Select the order line types(s) to be included (Optional).
Line Type not equal: Enter Line type exclusions if applicable (Optional).
Order Status: Select the order status(es) to be included (Optional).
Line Status: Select the order line status(es) to be included (Optional).
Item: Select the item(s) to be included (Optional).
Shippable Flag: Is the order line shippable, Yes/No.
Open only: Only include open orders (based on order header open flg and the last order line open flag, Yes/No.
Exclude Cancelled: Exclude cancelled order lines, Yes/No.
Scheduled ship date from: Enter the scheduled ship date from (Optional).
Scheduled ship date to: Enter the scheduled ship date to (Optional).
Request date from: Enter the requested ship date from (Optional).
Request date to: Enter the requested ship date to (Optional).
Creation date from: Enter the order creation date from (Optional).
Creation date to: Enter the order creation date to (Optional).
Operating Unit: Select the required Operating Unit (Business Group) (Optional)
Mgt Senfor Intl
Senfor International
&xrrpv_table
1=1
2=2
1
-10
&xrrpv_table
(
select distinct
dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1) organization_id,
dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id
from
xxen_report_parameters_vl xrpv,
xxen_report_run_param_values xrrpv
where
xrpv.parameter_name='organization_id|inventory_item_id' and
xrpv.parameter_id=xrrpv.parameter_id and
xrrpv.run_id=:run_id
) xrrpv,
Char
US
organization_id|inventory_item_id
2
2=2
xrrpv.organization_id=msiv.organization_id and
xrrpv.inventory_item_id=msiv.inventory_item_id
US
organization_id|inventory_item_id
3
1
2=2
haouv.name=:operating_unit
LOV
HR Operating Unit
8E2FF36EDEB979D2E0530100007F1FF2
Y
select
hou.name value,
null description
from
hr_operating_units hou
where
sysdate between hou.date_from and nvl(hou.date_to,sysdate) and
(:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.name
US
Operating Unit
4
5
2=2
ooha.order_number=:order_number
LOV
ONT Order Number
8E2FF36EDEE779D2E0530100007F1FF2
Y
Y
select
ooha.order_number value,
ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description
from
oe_order_headers_all ooha,
oe_transaction_types_tl ottt,
hz_cust_accounts hca
where
nvl(ooha.transaction_phase_code,'F')='F' and
(:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
ooha.sold_to_org_id=hca.cust_account_id and
ooha.order_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
ooha.order_number desc
US
Order Number
5
7
2=2
(
ooha.quote_number=:quote_number or
ooha.orig_sys_document_ref like :quote_number||'%' and ooha.source_document_type_id=16
)
LOV
ONT Quote Number
8E2FF36EDF3979D2E0530100007F1FF2
Y
Y
select
nvl(ooha.quote_number,regexp_substr(ooha.orig_sys_document_ref,'^(\d+).',1,1,null,1)) value,
ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description
from
oe_order_headers_all ooha,
oe_transaction_types_tl ottt,
hz_cust_accounts hca
where
(ooha.quote_number is not null or ooha.orig_sys_document_ref is not null and ooha.source_document_type_id=16) and
(:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
ooha.sold_to_org_id=hca.cust_account_id and
ooha.order_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
ooha.order_number desc
US
Quote Number
6
10
2=2
upper(hp.party_name) like upper(:customer_name)
LOV
AR Customer Name
8E2FF36EDE8679D2E0530100007F1FF2
Y
select
hp.party_name value,
hca.account_number||' ('||initcap(hp.party_type)||')' description
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
(
:$flex$.operating_unit is null or
hca.cust_account_id in (
select
hcasa.cust_account_id
from
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa
where
xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and
haouv.organization_id=hcasa.org_id
)
)
order by
hp.party_name
US
Customer Name
7
20
2=2
hca.account_number=:account_number
LOV
AR Account Number
8E2FF36EDE7F79D2E0530100007F1FF2
Y
Y
select
hca.account_number value,
hp.party_name||' ('||initcap(hp.party_type)||')' description
from
hz_parties hp,
hz_cust_accounts hca
where
hp.party_id=hca.party_id and
(
:$flex$.operating_unit is null or
hca.cust_account_id in (
select
hcasa.cust_account_id
from
hr_all_organization_units_vl haouv,
hz_cust_acct_sites_all hcasa
where
xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and
haouv.organization_id=hcasa.org_id
)
)
order by
hp.party_name
US
Account Number
8
25
2=2
nvl(ooha.transaction_phase_code,'F')='F'
LOV custom
Y
select 'Order' value, '' description from dual union all
select 'Quote' value, '' description from dual
Order
US
Type
9
2=2
ooha.transaction_phase_code='N'
Quote
US
Type
10
30
2=2
ooha.order_category_code=xxen_util.lookup_code(:order_category,'ORDER_CATEGORY',660)
LOV custom
Y
select
flv.meaning value,
flv.lookup_code description
from
fnd_lookup_values flv
where
flv.lookup_type='ORDER_CATEGORY' and
flv.view_application_id=660 and
flv.language(+)=userenv('lang') and
flv.security_group_id=0
order by
flv.meaning
US
Order Category
11
35
2=2
oola.line_category_code=xxen_util.lookup_code(:line_category,'ORDER_CATEGORY',660)
LOV custom
Y
select
flv.meaning value,
flv.lookup_code description
from
fnd_lookup_values flv
where
flv.lookup_code in ('ORDER','RETURN') and
flv.lookup_type='ORDER_CATEGORY' and
flv.view_application_id=660 and
flv.language(+)=userenv('lang') and
flv.security_group_id=0
order by
flv.meaning
US
Line Category
12
40
2=2
ottt.name=:order_type
LOV
ONT Order Type
8E2FF36EDEE879D2E0530100007F1FF2
Y
select
ottt.name value,
xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description
from
oe_transaction_types_all otta,
oe_transaction_types_tl ottt
where
(:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
otta.transaction_type_code='ORDER' and
otta.transaction_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
decode(otta.order_category_code,'ORDER',1,'MIXED',2,3),
ottt.name
US
Order Type
13
50
2=2
ottt2.name=:line_type
LOV
ONT Line Type
8E2FF36EDEF479D2E0530100007F1FF2
Y
select
ottt.name value,
xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description
from
oe_transaction_types_all otta,
oe_transaction_types_tl ottt
where
(:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
otta.transaction_type_code='LINE' and
otta.transaction_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
decode(otta.order_category_code,'ORDER',1,'MIXED',2,3),
ottt.name
US
Line Type
14
52
2=2
ottt2.name<>:line_type_not
LOV
ONT Line Type
8E2FF36EDEF479D2E0530100007F1FF2
Y
select
ottt.name value,
xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description
from
oe_transaction_types_all otta,
oe_transaction_types_tl ottt
where
(:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
otta.transaction_type_code='LINE' and
otta.transaction_type_id=ottt.transaction_type_id and
ottt.language=userenv('lang')
order by
decode(otta.order_category_code,'ORDER',1,'MIXED',2,3),
ottt.name
US
Line Type not equal
15
55
2=2
ooha.flow_status_code=xxen_util.lookup_code(:header_status,'FLOW_STATUS',660)
LOV
ONT Order Status
8E2FF36EDF0979D2E0530100007F1FF2
Y
select
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='FLOW_STATUS' and
flvv.view_application_id=660 and
flvv.security_group_id=0
order by
flvv.meaning
US
Order Status
16
57
2=2
oola.flow_status_code=xxen_util.lookup_code(:line_status,'LINE_FLOW_STATUS',660)
LOV
ONT Line Status
8E2FF36EDF0A79D2E0530100007F1FF2
Y
select
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='LINE_FLOW_STATUS' and
flvv.view_application_id=660 and
flvv.security_group_id=0
order by
flvv.meaning
US
Line Status
17
60
2=2
msiv.concatenated_segments like :item
LOV
INV Item
8E2FF36EDEF179D2E0530100007F1FF2
Y
Y
select
msiv.concatenated_segments value,
msiv.description||' ('||mp.organization_code||')' description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
(
:$flex$.organization_code is null and (
fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or
msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')
) or
xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y'
) and
msiv.organization_id=mp.organization_id
order by
mp.organization_code,
msiv.concatenated_segments
US
Item
18
70
2=2
oola.shippable_flag=:shippable_flag
LOV Oracle
Yes_No
Y
select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,description
US
Shippable Flag
19
140
2=2
ooha.open_flag='Y' and
oola.max_open_flag='Y'
LOV Oracle
Yes_No
Y
select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,description
US
Open only
20
150
2=2
ooha.cancelled_flag='N' and
oola.cancelled_flag='N'
LOV Oracle
Yes_No
Y
select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,description
Yes
US
Exclude Cancelled
21
160
2=2
oola.schedule_ship_date>=:schedule_ship_date_from
Date
US
Schedule Ship Date From
22
170
2=2
oola.schedule_ship_date<:schedule_ship_date_to+1
Date
US
Schedule Ship Date To
23
180
2=2
oola.request_date>=:request_date_from
Date
US
Request Date From
24
187
2=2
oola.request_date<:request_date_to+1
Date
US
Request Date To
25
195
2=2
oola.creation_date>=:creation_date_from
Date
US
Creation Date From
26
197
2=2
oola.creation_date<:creation_date_to+1
Date
US
Creation Date To
:$flex$.operating_unit
Operating Unit
Account Number
:$flex$.operating_unit
Operating Unit
Customer Name
:$flex$.operating_unit
Operating Unit
Line Type
:$flex$.operating_unit
Operating Unit
Line Type not equal
:$flex$.operating_unit
Operating Unit
Order Number
:$flex$.operating_unit
Operating Unit
Order Type
:$flex$.operating_unit
Operating Unit
Quote Number