帮我看一下，下边调用展ＢＯＭ的代码有何不正确，为什么Ｒeport最后出来的只是一层？
function BeforeReport return boolean is
begin
DECLARE
L_ORGANIZATION_NAME VARCHAR2(50);
L_EXPLODE_OPTION VARCHAR2(40);
L_RANGE_OPTION VARCHAR2(15);
L_SPECIFIC_ITEM VARCHAR2(245);
L_CATEGORY_SET VARCHAR2(10);
L_YES VARCHAR2(4);
L_NO VARCHAR2(4);
L_ALT_OPTION VARCHAR2(40);
L_ORDER_BY VARCHAR2(40);
l_seq_id NUMBER;
l_str varchar2(2000);
l_bom_or_eng NUMBER;
l_err_msg VARCHAR2(80);
l_err_code NUMBER;
exploder_error EXCEPTION;
loop_error EXCEPTION;
item_id_null EXCEPTION;
table_name VARCHAR2(20);
BEGIN
TABLE_NAME := 'Begin_trigger';
/* Calling AOL user exit */
SRW.USER_EXIT('FND SRWINIT');
TABLE_NAME := 'Trace';
IF :P_DEBUG = 'Y' THEN
SRW.DO_SQL('ALTER SESSION SET SQL_TRACE TRUE');
END IF;
TABLE_NAME := 'Check_specific';
IF (:P_RANGE_OPTION_TYPE = 1) AND
(:P_ITEM_ID IS NULL) THEN
SRW.USER_EXIT('FND MESSAGE_NAME NAME="MFG_REQUIRED_VALUE"');
SRW.USER_EXIT('FND MESSAGE_TOKEN TOKEN="ENTITY" VALUE="specific item"');
SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":P_MSG_BUF"');
SRW.MESSAGE('999', :P_MSG_BUF);
RAISE ITEM_ID_NULL;
END IF;
TABLE_NAME := 'Supress_detail';
IF :P_PRINT_OPTION1_FLAG = 2 THEN
SRW.SET_MAXROW('Q_ELEMENT', 0);
END IF;
TABLE_NAME := 'Ref_desg';
IF :P_PRINT_OPTION4_FLAG = 2 THEN
SRW.SET_MAXROW('Q_REF_DESIG', 0);
END IF;
TABLE_NAME := 'Sub_comps';
IF :P_PRINT_OPTION5_FLAG = 2 THEN
SRW.SET_MAXROW('Q_SUBS_COMPO', 0);
END IF;
TABLE_NAME := 'Org_define';
SELECT O.ORGANIZATION_NAME
INTO L_ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS O
WHERE O.ORGANIZATION_ID = :P_ORGANIZATION_ID;
TABLE_NAME := 'Category_sets';
IF :P_CATEGORY_SET_ID > 0 THEN
SELECT CATEGORY_SET_NAME
INTO L_CATEGORY_SET
FROM MTL_CATEGORY_SETS
WHERE CATEGORY_SET_ID = :P_CATEGORY_SET_ID;
:P_CATEGORY_SET := L_CATEGORY_SET;
END IF;
TABLE_NAME := 'Item_flexfields';
IF :P_ITEM_ID > 0 THEN
SELECT ITEM_NUMBER
INTO L_SPECIFIC_ITEM
FROM MTL_ITEM_FLEXFIELDS
WHERE ITEM_ID = :P_ITEM_ID
AND ORGANIZATION_ID = :P_ORGANIZATION_ID;
:P_SPECIFIC_ITEM := L_SPECIFIC_ITEM;
END IF;
TABLE_NAME := 'Lookups1';
SELECT SUBSTR(L1.MEANING,1,40),
SUBSTR(L2.MEANING,1,40),
SUBSTR(L3.MEANING,1,40),
SUBSTR(L4.MEANING,1,40)
INTO L_EXPLODE_OPTION, L_ALT_OPTION,
L_RANGE_OPTION, L_ORDER_BY
FROM MFG_LOOKUPS L1, MFG_LOOKUPS L2, MFG_LOOKUPS L3, MFG_LOOKUPS L4
WHERE L1.LOOKUP_TYPE = 'BOM_INQUIRY_DISPLAY_TYPE'
AND L1.LOOKUP_CODE = :P_EXPLODE_OPTION_TYPE
AND L2.LOOKUP_TYPE = 'MCG_AUTOLOAD_OPTION'
AND L2.LOOKUP_CODE = :P_ALT_OPTION_TYPE
AND L3.LOOKUP_TYPE = 'BOM_SELECTION_TYPE'
AND L3.LOOKUP_CODE = :P_RANGE_OPTION_TYPE
AND L4.LOOKUP_TYPE = 'BOM_BILL_SORT_ORDER_TYPE'
AND L4.LOOKUP_CODE = :P_ORDER_BY_TYPE;
TABLE_NAME := 'Lookups2';
SELECT SUBSTR(L1.MEANING, 1, 4), SUBSTR(L2.MEANING, 1, 4)
INTO L_YES, L_NO
FROM MFG_LOOKUPS L1, MFG_LOOKUPS L2
WHERE L1.LOOKUP_TYPE = 'SYS_YES_NO'
AND L1.LOOKUP_CODE = 1
AND L2.LOOKUP_TYPE = 'SYS_YES_NO'
AND L2.LOOKUP_CODE = 2;
:P_YES := L_YES;
:P_NO := L_NO;
TABLE_NAME := 'Print_option';
IF (:P_PRINT_OPTION1_FLAG = 1) THEN
:P_PRINT_OPTION1 := L_YES;
ELSE
:P_PRINT_OPTION1 := L_NO;
END IF;
IF (:P_PRINT_OPTION2_FLAG = 1) THEN
:P_PRINT_OPTION2 := L_YES;
ELSE
:P_PRINT_OPTION2 := L_NO;
END IF;
IF :P_PRINT_OPTION3_FLAG = 1 THEN
:P_PRINT_OPTION3 := L_YES;
ELSE
:P_PRINT_OPTION3 := L_NO;
END IF;
IF :P_PRINT_OPTION4_FLAG = 1 THEN
:P_PRINT_OPTION4 := L_YES;
ELSE
:P_PRINT_OPTION4 := L_NO;
END IF;
IF :P_PRINT_OPTION5_FLAG = 1 THEN
:P_PRINT_OPTION5 := L_YES;
ELSE
:P_PRINT_OPTION5 := L_NO;
END IF;
IF :P_IMPL_FLAG = 1 THEN
:P_IMPL := L_YES;
ELSE
:P_IMPL := L_NO;
END IF;
IF :P_PLAN_FACTOR_FLAG = 1 THEN
:P_PLAN_FACTOR := L_YES;
ELSE
:P_PLAN_FACTOR := L_NO;
END IF;
TABLE_NAME := 'Assign_values';
:P_ORGANIZATION_NAME := L_ORGANIZATION_NAME;
:P_EXPLODE_OPTION := L_EXPLODE_OPTION;
:P_RANGE_OPTION := L_RANGE_OPTION;
:P_ALT_OPTION := L_ALT_OPTION;
:P_ORDER_BY := L_ORDER_BY;
IF :P_BOM_OR_ENG = 'BOM' THEN
l_bom_or_eng := 1;
ELSE
l_bom_or_eng := 2;
END IF;
/* select a sequence id */
TABLE_NAME := 'Select_sequence';
SELECT BOM_LISTS_S.NEXTVAL
INTO l_seq_id
FROM DUAL;
/* Copy to P_sequence_id for After report trigger to use */
:P_sequence_id := l_seq_id;
TABLE_NAME := 'Locator_flex';
SRW.USER_EXIT('FND FLEXSQL CODE="MTLL" NUM=":P_LOCATOR_STRUCTURE_ID"
APPL_SHORT_NAME="INV" OUTPUT=":P_LOCATOR_FLEXDATA"
MODE="SELECT" DISPLAY="ALL"
TABLEALIAS="MIL"');
TABLE_NAME := 'Item_flex';
IF :P_RANGE_OPTION_TYPE = 2 THEN
IF (:P_ITEM_FROM IS NOT NULL) THEN
IF (:P_ITEM_TO IS NOT NULL) THEN
SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" APPL_SHORT_NAME="INV"
OUTPUT=":P_ASS_BETWEEN" OPERATOR="BETWEEN" MODE="WHERE"
OPERAND1=":P_ITEM_FROM" OPERAND2=":P_ITEM_TO"
NUM=":P_ITEM_STRUCTURE_ID" TABLEALIAS="MSI"');
ELSE
SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" APPL_SHORT_NAME="INV"
OUTPUT=":P_ASS_BETWEEN" OPERATOR=">=" MODE="WHERE"
OPERAND1=":P_ITEM_FROM"
NUM=":P_ITEM_STRUCTURE_ID" TABLEALIAS="msi"');
END IF;
ELSE /* :P_ITEM_FROM IS NULL */
IF (:P_ITEM_TO IS NOT NULL) THEN
SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" APPL_SHORT_NAME="INV"
OUTPUT=":P_ASS_BETWEEN" OPERATOR="<=" MODE="WHERE"
OPERAND1=":P_ITEM_TO"
NUM=":P_ITEM_STRUCTURE_ID" TABLEALIAS="msi"');
END IF;
END IF; /* item */
TABLE_NAME := 'Category_flex';
IF (:P_CATEGORY_FROM IS NOT NULL) THEN
IF (:P_CATEGORY_TO IS NOT NULL) THEN
SRW.USER_EXIT('FND FLEXSQL CODE="MCAT" APPL_SHORT_NAME="INV"
OUTPUT=":P_CAT_BETWEEN" OPERATOR="BETWEEN" MODE="WHERE"
OPERAND1=":P_CATEGORY_FROM" OPERAND2=":P_CATEGORY_TO"
NUM=":P_CATEGORY_STRUCTURE_ID" TABLEALIAS="mc"');
ELSE
SRW.USER_EXIT('FND FLEXSQL CODE="MCAT" APPL_SHORT_NAME="INV"
OUTPUT=":P_CAT_BETWEEN" OPERATOR=">=" MODE="WHERE"
OPERAND1=":P_CATEGORY_FROM"
NUM=":P_CATEGORY_STRUCTURE_ID" TABLEALIAS="mc"');
END IF;
ELSE
IF (:P_CATEGORY_TO IS NOT NULL) THEN
SRW.USER_EXIT('FND FLEXSQL CODE="MCAT" APPL_SHORT_NAME="INV"
OUTPUT=":P_CAT_BETWEEN" OPERATOR="<=" MODE="WHERE"
OPERAND1=":P_CATEGORY_TO"
NUM=":P_CATEGORY_STRUCTURE_ID" TABLEALIAS="mc"');
END IF;
END IF; /* category */
end if; /* :P_RANGE_TYPE = 2 */
TABLE_NAME := 'bom_lists';
l_str :='INSERT INTO BOM_LISTS (SEQUENCE_ID, ASSEMBLY_ITEM_ID,
ALTERNATE_DESIGNATOR) ';
TABLE_NAME := 'l_string';
IF :P_RANGE_OPTION_TYPE = 1 THEN
l_str := l_str ||
' SELECT DISTINCT '|| TO_CHAR(l_seq_id) ||',
'|| TO_CHAR(:P_ITEM_ID) ||',
bbom.alternate_bom_designator
FROM bom_bill_of_materials bbom
WHERE bbom.organization_id = '||TO_CHAR(:P_ORGANIZATION_ID)||'
AND bbom.assembly_item_id = '|| TO_CHAR(:P_ITEM_ID) ;
ELSE
l_str := l_str ||
' SELECT
DISTINCT '|| TO_CHAR(l_seq_id) ||',
msi.inventory_item_id,
bbom.alternate_bom_designator
FROM mtl_item_categories mic,
mtl_system_items msi,
mtl_categories mc,
bom_bill_of_materials bbom
WHERE '||:P_ASS_BETWEEN||'
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id =
'||TO_CHAR(:P_ORGANIZATION_ID)||'
AND mic.organization_id =
'||TO_CHAR(:P_ORGANIZATION_ID)||'
AND mic.category_id = mc.category_id
AND mic.category_set_id =
'||TO_CHAR(:P_CATEGORY_SET_ID)||'
AND mc.structure_id =
'||TO_CHAR(:P_CATEGORY_STRUCTURE_ID)||'
AND '||:P_CAT_BETWEEN||'
AND msi.inventory_item_id = bbom.assembly_item_id
AND msi.organization_id = bbom.organization_id
AND msi.bom_enabled_flag = ''Y''';
END IF;
l_str := l_str ||
' AND ( ('||TO_CHAR(:P_ALT_OPTION_TYPE)||' = 1)
OR
('||TO_CHAR(:P_ALT_OPTION_TYPE)||' = 2
AND bbom.alternate_bom_designator IS NULL)
OR
('||TO_CHAR(:P_ALT_OPTION_TYPE)||' = 3
AND NVL(bbom.alternate_bom_designator,''XXX'')=
NVL('''||:P_ALTERNATE_DESG||''', ''XXX''))
)
AND ( ('''||:P_BOM_OR_ENG||''' = ''BOM''
AND bbom.assembly_type = 1)
OR
('''||:P_BOM_OR_ENG||''' = ''ENG'')
)' ;
TABLE_NAME := 'do_sql';
SRW.DO_SQL(l_str);
TABLE_NAME := 'exploder';
/* Call BOM exploder */
bompexpl.explosion_report
(org_id => :P_ORGANIZATION_ID,
order_by => :P_ORDER_BY_TYPE,
list_id => l_seq_id,
grp_id => :P_GROUP_ID,
session_id => -1,
levels_to_explode => :P_EXPLOSION_LEVEL,
bom_or_eng => l_bom_or_eng,
impl_flag => :P_IMPL_FLAG,
explode_option => :P_EXPLODE_OPTION_TYPE,
module => 2,
cst_type_id => -1,
std_comp_flag => -1,
expl_qty => :P_EXPLOSION_QUANTITY,
report_option => -1,
req_id => :P_CONC_REQUEST_ID,
lock_flag => -1,
rollup_option => -1,
alt_rtg_desg => '',
alt_desg => :P_ALTERNATE_DESG,
rev_date => :P_REVISION_DATE,
err_msg => l_err_msg,
error_code => l_err_code,
cst_rlp_id => 0,
verify_flag => :P_VERIFY_FLAG,
plan_factor_flag => :P_PLAN_FACTOR_FLAG,
incl_lt_flag => :P_PRINT_OPTION3_FLAG);
if (:P_VERIFY_FLAG = 1 and l_err_code = 9999) then
RETURN(TRUE);
end if;
if (:P_VERIFY_FLAG = 2 and l_err_code = 9999) then
raise loop_error;
end if;
if l_err_code != 0 then
if l_err_code = 9998 then -- maximum levels exceeded
raise loop_error;
else
raise exploder_error;
end if;
end if;
RETURN (TRUE);
EXCEPTION
WHEN SRW.DO_SQL_FAILURE THEN
SRW.MESSAGE('1000', TABLE_NAME || SQLERRM);
RETURN (FALSE);
WHEN exploder_error THEN
SRW.MESSAGE('1000', l_err_msg);
RETURN (FALSE);
WHEN loop_error THEN
:P_ERR_MSG := l_err_msg;
SRW.USER_EXIT('FND MESSAGE_NAME NAME=":P_ERR_MSG"');
SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":P_MSG_BUF"');
SRW.MESSAGE(l_err_code, :P_MSG_BUF);
RETURN (FALSE);
WHEN ITEM_ID_NULL THEN
raise SRW.PROGRAM_ABORT;
WHEN OTHERS THEN
SRW.MESSAGE('2000', TABLE_NAME || SQLERRM);
RETURN (FALSE);
END; return (TRUE);
end;
99
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
