[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(distinct backlogs_pk)
INTO p_num_rows
FROM (SELECT trunc(sysdate,'DD')
||'-'|| inst.instance_code
||'-'|| l.order_quantity_uom
||'-'|| nvl( pl.inventory_item_id,l.inventory_item_id)
||'-'|| decode( pl.ship_from_org_id, NULL,
pl.inventory_item_id||'-'||pl.org_id,
pl.inventory_item_id||'-'||pl.ship_from_org_id)
||'-'|| decode( l.ship_from_org_id, NULL,
l.inventory_item_id||'-'||l.org_id,
l.inventory_item_id||'-'||l.ship_from_org_id)
||'-'|| nvl( l.ship_from_org_id,h.ship_from_org_id)
||'-'|| l.org_id
||'-'|| l.sold_to_org_id
||'-'|| h.sales_channel_code
||'-'|| l.salesrep_id
||'-'|| l.task_id
||'-'|| h.invoice_to_org_id
||'-'|| l.ship_to_org_id
||'-'|| nvl( l.demand_class_code,h.demand_class_code)
||'-'|| h.transactional_curr_code
||'-'|| l.line_category_code
||'-'|| ot.transaction_type_id
||'-'|| nvl( os_2.order_source_id, os_1.order_source_id)
||'-'|| fspa.set_of_books_id BACKLOGS_PK
FROM edw_local_instance inst,
oe_transaction_types_tl ot,
oe_order_lines_all pl,
oe_order_headers_all h,
oe_order_lines_all l,
oe_order_sources os_1,
oe_order_sources os_2,
mtl_system_items_b p_mtl,
mtl_system_items_b mtl,
financials_system_params_all fspa,
gl_sets_of_books gl
WHERE (h.last_update_date between p_from_date and p_to_date
OR l.last_update_date between p_from_date and p_to_date)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND nvl(l.ordered_quantity,0) > 0
AND nvl(l.source_document_type_id,0) <> 10
AND l.line_category_code = ('ORDER')
AND l.header_id = h.header_id
AND h.header_id = pl.header_id
AND nvl(l.top_model_line_id, l.line_id) = pl.line_id
AND h.order_type_id = ot.transaction_type_id
AND ot.language = userenv('LANG')
AND l.order_source_id = os_1.order_source_id (+)
AND l.source_document_type_id = os_2.order_source_id (+)
AND l.org_id = fspa.org_id
AND fspa.set_of_books_id = gl.set_of_books_id
AND l.inventory_item_id = mtl.inventory_item_id(+)
AND l.ship_from_org_id = mtl.organization_id(+)
AND pl.inventory_item_id = p_mtl.inventory_item_id(+)
AND pl.ship_from_org_id = p_mtl.organization_id(+));
SELECT nvl(avg(nvl(vsize(sold_to_org_id),0)),0),
nvl(avg(nvl(vsize(demand_class_code),0)),0),
nvl(avg(nvl(vsize(ship_from_org_id),0)),0),
nvl(avg(nvl(vsize(inventory_item_id),0)),0),
nvl(avg(nvl(vsize(org_id),0)),0),
nvl(avg(nvl(vsize(line_category_code),0)),0),
nvl(avg(nvl(vsize(salesrep_id),0)),0),
nvl(avg(nvl(vsize(ship_to_org_id),0)),0),
nvl(avg(nvl(vsize(task_id),0)),0)
FROM OE_ORDER_LINES_ALL
WHERE last_update_date BETWEEN p_from_date AND p_to_date;
SELECT nvl(avg(nvl(vsize(UOM_EDW_BASE_UOM),0)),0)
FROM EDW_MTL_LOCAL_UOM_M;
SELECT nvl(avg(nvl(vsize(invoice_to_org_id),0)),0),
nvl(avg(nvl(vsize(sales_channel_code),0)),0),
nvl(avg(nvl(vsize(transactional_curr_code),0)),0),
nvl(avg(nvl(vsize(salesrep_id),0)),0),
nvl(avg(nvl(vsize(order_category_code),0)),0)
FROM OE_ORDER_HEADERS_ALL
WHERE last_update_date BETWEEN p_from_date AND p_to_date;
SELECT nvl(avg(nvl(vsize(party_site_id),0)),0)
FROM HZ_CUST_ACCT_SITES_ALL;
SELECT nvl(avg(nvl(vsize(period_set_name),0)),0) + nvl(avg(nvl(vsize(accounted_period_type),0)),0)
FROM GL_SETS_OF_BOOKS;
SELECT nvl(avg(nvl(vsize(name),0)),0)
FROM OE_ORDER_SOURCES;
SELECT nvl(avg(nvl(vsize(name),0)),0)
FROM OE_TRANSACTION_TYPES_TL;
SELECT nvl(avg(nvl(vsize(to_char(set_of_books_id)),0)),0),
nvl(avg(nvl(vsize(set_of_books_id),0)),0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL;
SELECT nvl(avg(nvl(vsize(inventory_item_id),0)),0),
nvl(avg(nvl(vsize(organization_id),0)),0)
FROM mtl_system_items_b;
SELECT nvl(avg(nvl(vsize(nvl(l.unit_selling_price,0)
*(nvl(l.ordered_quantity,0)-nvl(l.invoiced_quantity,0))
*nvl(EDW_CONVERSION_RATE,0)),0)),0)
FROM oe_order_lines_all l,
OPI_EDW_LOCAL_UOM_CONV_F;