[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRCTCR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRCTCR_XMLP_PKG AS
2 /* $Header: CSTRCTCRB.pls 120.0 2007/12/24 09:53:15 dwkrishn noship $ */
3 FUNCTION TOT_PER_CHANGEFORMULA(TOT_ITEM_COST1 IN NUMBER
4 ,TOT_ITEM_COST2 IN NUMBER
5 ,TOT_DIFFERENCE IN NUMBER) RETURN NUMBER IS
6 BEGIN
7 IF TOT_ITEM_COST1 = 0 THEN
8 IF TOT_ITEM_COST2 = 0 THEN
9 RETURN 0;
10 ELSE
11 RETURN 100;
12 END IF;
13 ELSE
14 RETURN (ROUND(TOT_DIFFERENCE / TOT_ITEM_COST1 * 100
15 ,2));
16 END IF;
17 RETURN NULL;
18 END TOT_PER_CHANGEFORMULA;
19
20 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
21 CURSOR c1 IS
22 SELECT fifst.id_flex_num
23 FROM fnd_id_flex_structures fifst
24 WHERE fifst.application_id = 401
25 AND fifst.id_flex_code = 'MSTK'
26 AND fifst.enabled_flag = 'Y'
27 AND fifst.freeze_flex_definition_flag = 'Y'
28 ORDER BY fifst.id_flex_num;
29 BEGIN
30 /*SRW.MESSAGE(100
31 ,TO_CHAR(SYSDATE
32 ,'"Before report trigger started at "Dy Mon DD HH:MI:SS YYYY'))*/NULL;
33 DECLARE
34 SQL_STMT_NUM VARCHAR2(5);
35 CREATE_WHERE VARCHAR2(875);
36 BEGIN
37 QTY_PRECISION:= bom_common_xmlp_pkg.get_precision(P_EXT_PRECISION);
38 SQL_STMT_NUM := '0: ';
39 P_TABLE_ORDER := ' mtl_system_items_vl msi, mtl_item_categories mic, mtl_categories_kfv mc, cst_item_costs cic, cst_detail_cost_view cdcv';
40 SQL_STMT_NUM := '1: ';
41 IF P_VIEW_COST <> 1 THEN
42 FND_MESSAGE.SET_NAME('null'
43 ,'null');
44 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
45 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
46 END IF;
47 SQL_STMT_NUM := '2: ';
48 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
49 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
50 SQL_STMT_NUM := '3: ';
51 SQL_STMT_NUM := '4: ';
52 SQL_STMT_NUM := '5: ';
53 IF P_FROM_ITEM IS NULL AND P_TO_ITEM IS NULL THEN
54 P_WHERE_ITEM := '1 = 1';
55 P_TABLE_ORDER := 'mtl_categories_kfv mc, mtl_item_categories mic, cst_item_costs cic, mtl_system_items_vl msi, cst_detail_cost_view cdcv';
56 ELSE
57 P_TABLE_ORDER := ' cst_detail_cost_view cdcv, cst_item_costs cic, mtl_categories_kfv mc, mtl_item_categories mic, mtl_system_items_vl msi';
58 --CREATE_WHERE := ' msi.inventory_item_id in (select inventory_item_id from mtl_system_items MSLT where ' || P_WHERE_ITEM || ' and MSLT.organization_id = ' || TO_CHAR(ORG_ID) || ' )';
59 CREATE_WHERE_ITEM1 := ' msi.inventory_item_id in (select inventory_item_id from mtl_system_items MSLT where ';
60 CREATE_WHERE_ITEM2 := ' and MSLT.organization_id = ' || TO_CHAR(ORG_ID) || ' )';
61 --P_WHERE_ITEM := CREATE_WHERE;
62 END IF;
63 CREATE_WHERE := NULL;
64 SQL_STMT_NUM := '6: ';
65 IF P_FROM_CAT IS NULL AND P_TO_CAT IS NULL THEN
66 P_WHERE_CAT := '1 = 1';
67 ELSE
68 --CREATE_WHERE := 'mc.category_id in (select category_id from mtl_categories MCT where ' || P_WHERE_CAT || 'and MCT.structure_id = ' || TO_CHAR(P_STRUCT_NUM) || ' )';
69 --P_WHERE_CAT := CREATE_WHERE;
70 P_TABLE_ORDER := 'cst_detail_cost_view cdcv, cst_item_costs cic, mtl_system_items_vl msi, mtl_item_categories mic, mtl_categories_kfv mc';
71 CREATE_WHERE_CAT1 := 'mc.category_id in (select category_id from mtl_categories MCT where ';
72 CREATE_WHERE_CAT2 := 'and MCT.structure_id = ' || TO_CHAR(P_STRUCT_NUM) || ' )';
73 END IF;
74
75 SQL_STMT_NUM := '7: ';
76 IF COMPARISON_OPT = 2 THEN
77 COLUMN1 := 'cdcv.resource_code';
78 ELSIF COMPARISON_OPT = 3 THEN
79 COLUMN1 := 'cdcv.activity';
80 ELSIF COMPARISON_OPT = 4 THEN
81 COLUMN1 := 'cdcv.operation_seq_num';
82 ELSIF COMPARISON_OPT = 5 THEN
83 COLUMN1 := 'cdcv.department';
84 ELSIF COMPARISON_OPT = 6 THEN
85 COLUMN1 := 'cdcv.cost_level';
86 ELSE
87 COLUMN1 := 'cdcv.cost_element';
88 END IF;
89 SQL_STMT_NUM := '8: ';
90 SELECT
91 NVL(FC.EXTENDED_PRECISION
92 ,FC.PRECISION),
93 NVL(FC.PRECISION
94 ,0)
95 INTO P_EXT_PRECISION,P_PRECISION
96 FROM
97 ORG_ORGANIZATION_DEFINITIONS O,
98 GL_SETS_OF_BOOKS GL,
99 MTL_PARAMETERS P,
100 FND_CURRENCIES FC
101 WHERE O.ORGANIZATION_ID = ORG_ID
102 AND P.ORGANIZATION_ID = ORG_ID
103 AND O.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
104 AND FC.CURRENCY_CODE = GL.CURRENCY_CODE;
105 --Added
106 OPEN C1;
107 FETCH C1 INTO PID_FLEX_NUM;
108 CLOSE C1;
109 --Added till this
110
111 RETURN (TRUE);
112 EXCEPTION
113 WHEN OTHERS THEN
114 /*SRW.MESSAGE(999
115 ,SQL_STMT_NUM || SQLERRM)*/NULL;
116 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
117 END;
118 /*SRW.MESSAGE(100
119 ,TO_CHAR(SYSDATE
120 ,'"Before report trigger completed at "Dy Mon DD HH:MI:SS YYYY'))*/NULL;
121 RETURN (TRUE);
122 END BEFOREREPORT;
123
124 FUNCTION AFTERREPORT RETURN BOOLEAN IS
125 BEGIN
126 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
127 /*SRW.MESSAGE(100
128 ,TO_CHAR(SYSDATE
129 ,'"After report trigger completed at "Dy Mon DD HH:MI:SS YYYY'))*/NULL;
130 RETURN (TRUE);
131 END AFTERREPORT;
132
133 FUNCTION DISP_CURRENCYFORMULA(CURR_CODE_SAVED IN VARCHAR2) RETURN VARCHAR2 IS
134 BEGIN
135 RETURN ('(' || CURR_CODE_SAVED || ')');
136 END DISP_CURRENCYFORMULA;
137
138 FUNCTION MIN_PERC_DIFF_SAVEDFORMULA(EXT_PRECISION_SAVED IN VARCHAR2) RETURN NUMBER IS
139 BEGIN
140 IF (MIN_PERC_DIFF IS NULL) THEN
141 RETURN (0);
142 ELSE
143 RETURN (ROUND(MIN_PERC_DIFF
144 ,EXT_PRECISION_SAVED));
145 END IF;
146 RETURN NULL;
147 END MIN_PERC_DIFF_SAVEDFORMULA;
148
149 FUNCTION MIN_AMT_DIFF_SAVEDFORMULA(EXT_PRECISION_SAVED IN VARCHAR2) RETURN NUMBER IS
150 BEGIN
151 IF (MIN_AMT_DIFF IS NULL) THEN
152 RETURN (0);
153 ELSE
154 RETURN (ROUND(MIN_AMT_DIFF
155 ,EXT_PRECISION_SAVED));
156 END IF;
157 RETURN NULL;
158 END MIN_AMT_DIFF_SAVEDFORMULA;
159
160 FUNCTION MIN_UNIT_COST_SAVEDFORMULA(EXT_PRECISION_SAVED IN VARCHAR2) RETURN NUMBER IS
161 BEGIN
162 IF (MIN_UNIT_COST IS NULL) THEN
163 RETURN (0);
164 ELSE
165 RETURN (ROUND(MIN_UNIT_COST
166 ,EXT_PRECISION_SAVED));
167 END IF;
168 RETURN NULL;
169 END MIN_UNIT_COST_SAVEDFORMULA;
170
171 FUNCTION COLUMN1_TITLE_HDRFORMULA(COLUMN1_TITLE_SAVED IN VARCHAR2) RETURN VARCHAR2 IS
172 BEGIN
173 RETURN (COLUMN1_TITLE_SAVED);
174 END COLUMN1_TITLE_HDRFORMULA;
175
176 FUNCTION AFTERPFORM RETURN BOOLEAN IS
177 BEGIN
178 RETURN (TRUE);
179 END AFTERPFORM;
180
181 END BOM_CSTRCTCR_XMLP_PKG;
182