[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(cnt)
from (SELECT count(*) cnt
FROM
oe_order_headers_all h,
oe_order_lines_all pl,
oe_order_lines_all l,
wsh_delivery_details wdd,
mtl_transaction_accounts mta,
mtl_material_transactions mmt
where mmt.transaction_source_type_id = 2
and mta.transaction_source_type_id = 2
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type in (2, 35)
and pl.org_id = l.org_id
and h.org_id = l.org_id
and l.line_id = mmt.trx_source_line_id
and l.line_category_code = 'ORDER'
and pl.line_category_code = 'ORDER'
and pl.line_id = nvl(l.top_model_line_id, l.line_id)
and h.header_id = l.header_id
and h.header_id = pl.header_id
and wdd.delivery_detail_id = mmt.picking_line_id
AND greatest(nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mta.last_update_date,to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mmt.last_update_date,to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN p_from_date and p_to_date
UNION ALL
SELECT count(*) cnt
from
oe_order_headers_all h,
oe_order_lines_all pl,
oe_order_lines_all cl,
oe_order_lines_all l,
mtl_transaction_accounts mta,
mtl_material_transactions mmt
where mmt.transaction_source_type_id = 12
and mta.transaction_source_type_id = 12
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type in (2, 35)
and h.org_id = l.org_id
and l.line_id = mmt.trx_source_line_id
and l.line_category_code = 'RETURN'
and cl.line_id (+) = l.link_to_line_id
and pl.line_id (+) = nvl(cl.top_model_line_id, cl.line_id)
and h.header_id = l.header_id
AND greatest(nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mta.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mmt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN p_from_date and p_to_date
UNION ALL
SELECT count(*) cnt
FROM
oe_order_headers_all h,
oe_order_lines_all pl, /* parent line */
oe_order_lines_all l, /* child line */
ra_customer_trx_lines_all rcl,
ap_invoice_distributions_all aid,
ap_invoices_all ai,
mtl_material_transactions mmt,
mtl_parameters mp
WHERE ai.source = 'Intercompany'
AND aid.invoice_id = ai.invoice_id
and aid.org_id = ai.org_id
and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1)
and aid.line_type_lookup_code = 'ITEM'
and rcl.interface_line_attribute6 = l.line_id
and pl.line_id = nvl(l.top_model_line_id, l.line_id)
and pl.org_id = l.org_id
and h.org_id = l.org_id
and h.header_id = l.header_id
and h.header_id = pl.header_id
and l.line_category_code = 'ORDER'
and pl.line_category_code = 'ORDER'
and rcl.interface_line_attribute7 = mmt.transaction_id
and nvl(mmt.logical_transaction,0) <> 1
and mmt.organization_id = mp.organization_id
and mp.process_enabled_flag <> 'Y'
AND greatest(
nvl(aid.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(ai.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN p_from_date and p_to_date
);
SELECT avg(nvl(vsize(transaction_id), 0)) txn_id,
avg(nvl(vsize(transaction_uom), 0)) uom,
avg(nvl(vsize(currency_code), 0)) currency,
avg(nvl(vsize(subinventory_code), 0)) sub_code,
avg(nvl(vsize(locator_id), 0)) locator,
avg(nvl(vsize(primary_quantity), 0)) qty,
avg(nvl(vsize(inventory_item_id), 0)) item_id
FROM mtl_material_transactions
WHERE last_update_date between p_from_date and p_to_date;
SELECT avg(nvl(vsize(cost_element_id), 0)) cost_element_id,
avg(nvl(vsize(transaction_value), 0)) transaction_value,
avg(nvl(vsize(reference_account), 0)) account
FROM mtl_transaction_accounts
WHERE last_update_date between p_from_date and p_to_date;
SELECT avg(nvl(vsize(line_id), 0)) line_id,
avg(nvl(vsize(project_id), 0)) project_id,
avg(nvl(vsize(task_id), 0)) task_id,
avg(nvl(vsize(source_type_code), 0)) source_type_code,
avg(nvl(vsize(marketing_source_code_id), 0)) marketing_source_code_id
FROM oe_order_lines_all
WHERE last_update_date between p_from_date and p_to_date;
SELECT avg(nvl(vsize(sales_channel_code), 0)) channel_code,
avg(nvl(vsize(salesrep_id), 0)) salesrep_id,
avg(nvl(vsize(order_category_code), 0)) order_category_code,
avg(nvl(vsize(header_id), 0)) header_id,
avg(nvl(vsize(order_number), 0)) order_number
FROM oe_order_headers_all
WHERE last_update_date between p_from_date and p_to_date;
select avg(nvl(vsize(lookup_code_pk), 0))
from edw_lookup_code_fkv;
SELECT avg(nvl(vsize(lot_number), 0)) LOT,
avg(nvl(vsize(revision), 0)) REVISION,
avg(nvl(vsize(serial_number), 0)) SERIAL_NUMBER
FROM wsh_delivery_details
WHERE last_update_date between p_from_date and p_to_date;
SELECT avg(nvl(vsize(waybill), 0)) waybill
FROM wsh_new_deliveries
WHERE last_update_date between p_from_date and p_to_date;
SELECT
avg(nvl(vsize(instance_code), 0))
FROM EDW_LOCAL_INSTANCE ;
SELECT avg(nvl(Vsize(organization_id), 0)) org_id,
avg(nvl(Vsize(organization_code), 0)) org_code
FROM mtl_parameters;
SELECT AVG(Nvl(Vsize(primary_quantity), 0))
FROM wip_transactions
WHERE last_update_date between p_from_date and p_to_date;
SELECT AVG(Nvl(Vsize(24*capacity_units), 0))
FROM bom_department_resources
WHERE last_update_date between p_from_date and p_to_date;
SELECT AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_FK(Sysdate, set_of_books_id) ),0))
FROM gl_sets_of_books;
SELECT AVG(Nvl(Vsize(activity_offer_id), 0))
FROM ams_act_offers
WHERE last_update_date between p_from_date and p_to_date;
SELECT AVG(Nvl(Vsize(cell_code), 0))
FROM ams_list_entries
WHERE last_update_date between p_from_date and p_to_date;
SELECT AVG(Nvl(Vsize(user_status_id), 0))
FROM ams_campaigns_all_b
WHERE last_update_date between p_from_date and p_to_date;