INVARAAS.rdf -- ABC分配报表
+---------------------------------------------------------------------------+
库存管理系统: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
INVARAAS module: ABC 分配报表
+---------------------------------------------------------------------------+
当前的系统时间为 19-09-2012 14:56:58
+---------------------------------------------------------------------------+
+-----------------------------
| 正在启动并发执行程序...
+-----------------------------
变元
------------
ORG_ID='207'
GROUP_ID='229'
P_SORT_ID='1'
------------
APPLLCSP Environment Variable set to :
Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
SIMPLIFIED CHINESE_AMERICA.AL32UTF8
'.,'
输出口令:
Report Builder: Release 10.1.2.3.0 - Production on 3 09 19 14:57:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
+---------------------------------------------------------------------------+
FND_FILE 中日志消息开始
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
FND_FILE 中日志消息结束
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
正在执行请求完成选项...
+------------- 1) PRINT -------------+
正在打印输出文件。
请求编号 : 5806896
份数 : 0
打印机 : noprint
+--------------------------------------+
已完成执行请求完成选项。
+---------------------------------------------------------------------------+
已成功完成并发请求
当前的系统时间为 19-09-2012 14:57:21
+---------------------------------------------------------------------------+
--Q_Org
--mtl_abc_assignment_groups
--计算项目ABC码用的。用来分类物料，用来库存盘点用
--这个表是ABC组，每个组中可以定义几个ABC分类，
--通过某种规则计算项目在这个组中属于那一个类，比如A、B、C、D、E等（自定义）。
SELECT org.organization_name,
gsob.currency_code,
cur.precision std_precision,
NVL (cur.extended_precision, cur.precision) ext_precision
FROM org_organization_definitions org, --组织单位表
gl_sets_of_books gsob, --帐簿表
mtl_abc_assignment_groups abc, --ABC分类
fnd_currencies cur -- 币种表
WHERE 1 = 1
AND org.organization_id = abc.organization_id
AND org.set_of_books_id = gsob.set_of_books_id
AND abc.assignment_group_id = 229 --:GROUP_ID
AND abc.organization_id = 207 --:ORG_ID
AND gsob.currency_code = cur.currency_code(+)
FUNCTION C_FormattedCurrencyCodeFormula
RETURN VARCHAR2
IS
BEGIN
DECLARE
temp_c VARCHAR2 (20);
BEGIN
temp_c := '(' || :Currency_Code || ')';
RETURN (temp_c);
END;
RETURN NULL;
END;
--==================================================================
--Q_Groupname
SELECT assignment_group_name
FROM mtl_abc_assignment_groups abc
WHERE abc.assignment_group_id = 229 --:GROUP_ID
AND abc.organization_id = 207 --:ORG_ID
--==================================================================
--Q_Abclevel
SELECT DECODE (abc.item_scope_type, 1, '', 2, abc.secondary_inventory) TheLevel
FROM mtl_abc_assignment_groups abc,
mfg_lookups mfg
WHERE 1 = 1
--AND abc.assignment_group_id = 229 --:GROUP_ID
--AND abc.organization_id = 207 --:ORG_ID
AND mfg.lookup_type = 'MTL_ABC_ITEM_SCOPE'
AND mfg.lookup_code = abc.item_scope_type
--==================================================================
--Q_CompileItem
SELECT sys.item_number,
sys.description,
clas.abc_class_name,
com.sequence_number,
ROUND (NVL (com.compile_quantity, 0), 2) compile_quantity,
ROUND (NVL (com.compile_value, 0), 2) compile_value1,
--ROUND (NVL (com.compile_quantity, 0), :p_qty_precision) compile_quantity,
--ROUND (NVL (com.compile_value, 0), :c_std_precision) compile_value1,
sys.primary_uom_code
FROM mtl_item_flexfields sys,
mtl_abc_classes clas,
mtl_abc_compiles com,
mtl_abc_assignment_groups ass,
mtl_abc_assignments abc
WHERE sys.organization_id = 207 --:org_id
AND clas.organization_id = 207 --:org_id
AND com.organization_id = 207 --:org_id
AND ass.organization_id = 207 --:org_id
AND ass.assignment_group_id = 229 --:group_id
AND ass.compile_id = com.compile_id
AND abc.inventory_item_id = sys.item_id
AND abc.assignment_group_id = 229 --:group_id
AND abc.abc_class_id = clas.abc_class_id
AND abc.inventory_item_id = com.inventory_item_id
UNION
SELECT sys.item_number,
sys.description,
clas.abc_class_name,
DECODE (1, 0, 0, NULL) sequence_number,
0 compile_quantity,
0 compile_value,
sys.primary_uom_code
FROM mtl_item_flexfields sys,
mtl_abc_classes clas,
mtl_abc_assignment_groups ass,
mtl_abc_assignments abc
WHERE sys.organization_id = 207 --:org_id
AND clas.organization_id = 207 --:org_id
AND ass.organization_id = 207 --:org_id
AND ass.assignment_group_id = 229 --:group_id
AND abc.inventory_item_id = sys.item_id
AND abc.assignment_group_id = 229 --:group_id
AND abc.abc_class_id = clas.abc_class_id
AND NOT EXISTS
(SELECT *
FROM mtl_abc_compiles com
WHERE abc.inventory_item_id = com.inventory_item_id
AND com.organization_id = 207 --:org_id
AND ass.compile_id = com.compile_id)
-- &C_ORDERBY
--==================================================================
FUNCTION c_fcompilevalueformula
RETURN VARCHAR2
IS
BEGIN
BEGIN
srw.reference (:compile_value1);
srw.reference (:s_currencycode);
srw.USER_EXIT ('FND FORMAT_CURRENCY CODE=":S_CurrencyCode" --USD
DISPLAY_WIDTH="16"
AMOUNT=":Compile_Value1"
DISPLAY=":C_FCompileValue"');
RETURN (:c_fcompilevalue);
EXCEPTION
WHEN srw.user_exit_failure
THEN
RETURN ('FC ERROR');
END;
RETURN NULL;
END;
--==================================================================
procedure get_precision (id in number) is
begin
srw.attr.mask := srw.formatmask_attr;
--Bug 1403394 Earlier Characters are hard coded now changed.
if id = 0 then srw.attr.formatmask := '-NNNGNNNGNNNGNN0';
else
if id = 1 then srw.attr.formatmask := '-NNNGNNNGNNNGNN0D0';
else
if id = 3 then srw.attr.formatmask := '-NNGNNNGNNNGNN0D000';
else
if id = 4 then srw.attr.formatmask := '-NGNNNGNNNGNN0D0000';
else
if id = 5 then srw.attr.formatmask := '-NNNGNNNGNN0D00000';
else
if id = 6 then srw.attr.formatmask := '-NNGNNNGNN0D000000';
else
if id = 7 then srw.attr.formatmask := '-NNNNNNNNNNN0';
else
if id = 8 then srw.attr.formatmask := '-NNNNNNNNNNN0D0';
else
if id = 9 then srw.attr.formatmask := '-NNNNNNNNNN0D00';
else
if id = 10 then srw.attr.formatmask := '-NNNNNNNNNN0D000';
else
if id = 11 then srw.attr.formatmask := '-NNNNNNNNN0D0000';
else
if id = 12 then srw.attr.formatmask := '-NNNNNNNN0D00000';
else
if id = 13 then srw.attr.formatmask := '-NNNNNNN0D000000';
else srw.attr.formatmask := '-NNNGNNNGNNNGNN0D00';
end if; end if; end if; end if; end if; end if;
end if; end if; end if; end if; end if; end if; end if;
srw.set_attr(0,srw.attr);
end;