OU、库存组织、子库存的关系
SELECT hou.organization_id ou_org_id, --org_id
hou.name ou_name, --ou名称
ood.organization_id org_org_id, --库存组织id
ood.organization_code org_org_code, --库存组织代码
msi.secondary_inventory_name, --子库存名称
msi.description --子库存描述
FROM hr_organization_information hoi, --组织分类表
hr_operating_units hou, --ou视图
org_organization_definitions ood, --库存组织定义视图
mtl_secondary_inventories msi --子库存信息表
WHERE hoi.org_information1 = 'OPERATING_UNIT'
AND hoi.organization_id = hou.organization_id
AND ood.operating_unit = hoi.organization_id
AND ood.organization_id = msi.organization_id
循环游标内部同时更新属性
DECLARE
CURSOR sal_cursor IS
SELECT e.department_id, employee_id, last_name, salary
FROM employees e, departments d
WHERE d.department_id = e.department_id
AND d.department_id = 60
FOR UPDATE OF salary NOWAIT;
BEGIN
FOR emp_record IN sal_cursor LOOP
IF emp_record.salary < 5000 THEN
UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
动态DDL
--
--Dynamic execute structure query language(sql)
--
PROCEDURE do_ddl(ddl_text in VARCHAR2) IS
c INTEGER;
rows_processed INTEGER;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(c, ddl_text, dbms_sql.native);
rows_processed := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(c);
RAISE;
END do_ddl;
--------------------------------------------------------------------------------------------------
DECLARE
v_cursor NUMBER;
v_stat NUMBER;
v_row NUMBER;
v_id NUMBER;
v_no VARCHAR(100);
v_date DATE;
v_sql VARCHAR(200);
s_id NUMBER;
s_date DATE;
BEGIN
s_id := 3000;
s_date := SYSDATE;
v_sql := 'SELECT id,qan_no,sample_date FROM "tblno" WHERE id > :sid and sample_date < :sdate';
v_cursor := dbms_sql.open_cursor; --打开游标;
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句;
dbms_sql.bind_variable(v_cursor, ':sid', s_id); --绑定输入参数;
dbms_sql.bind_variable(v_cursor, ':sdate', s_date);
dbms_sql.define_column(v_cursor, 1, v_id); --定义列
dbms_sql.define_column(v_cursor, 2, v_no, 100);
dbms_sql.define_column(v_cursor, 3, v_date);
v_stat := dbms_sql.execute(v_cursor); --执行动态SQL语句。
LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rows在结果集中移动游标,如果未抵达末尾,返回1。
dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。
dbms_sql.column_value(v_cursor, 2, v_no);
dbms_sql.column_value(v_cursor, 3, v_date);
dbms_output.put_line(v_id || ';' || v_no || ';' || v_date);
END LOOP;
dbms_sql.close_cursor(v_cursor); --关闭游标。
END;
类别集、类别、物料类别关系
SELECT mcs.category_set_id
,mcs.category_set_name
,mc.category_id
,mc.concatenated_segments category_name
,mic.inventory_item_id
FROM mtl_item_categories mic
,mtl_category_sets_vl mcs --vl单语言
,mtl_categories_b_kfv mc
WHERE mic.category_set_id = mcs.category_set_id
AND mic.category_id = mc.category_id
AND mcs.category_set_id = nvl(&p_category_set_id, mcs.category_set_id)
AND mic.organization_id = nvl(&p_l_org_id, mic.organization_id)
ORDER BY mcs.category_set_id, mc.category_id, mc.concatenated_segments, mic.inventory_item_id
控制并发请求的运行结果为“警告”
可以在程序中用以下函数实现:
l_conc_result := fnd_concurrent.set_completion_status('WARNING', 'WARNING');