[Home] [Help]
PACKAGE BODY: APPS.BOM_FILTER
Source
1 PACKAGE BODY BOM_FILTER AS
2 /* $Header: BOMXRECB.pls 120.1 2005/06/01 19:04:52 appldev $ */
3 /*==========================================================================+
4 | Copyright (c) 2003 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMXRECB.pls |
9 | DESCRIPTION : Procedure explores and marks the parent items in |
10 | BOM_EXPLOSION_TEMP table.
11 | Parameters : p_ParamSortOrder Sort Order Array |
12 | p_GroupId Explosion Group ID |
13 | x_ResultSortOrder Resultant Sort Order Array |
14 | |
15 | Revision |
16 | 2003/10/22 Ajay creation |
17 | 2004/1/16 Ajay Modified the method to only update |
18 | the row with flag. |
19 | |
20 +==========================================================================*/
21
22 G_PARAM_LIST PARAM_LIST;
23
24 PROCEDURE applyFilter (p_ParamSortOrder dbms_sql.VARCHAR2_TABLE, p_GroupId VARCHAR2) as
25
26 l_st DATE := sysdate;
27 BEGIN
28
29 -- Reset the flag
30 UPDATE BOM_EXPLOSIONS_ALL
31 SET HGRID_FLAG=NULL
32 WHERE HGRID_FLAG='Y'
33 AND group_id = p_GroupId
34 AND plan_level <> 0;
35
36 -- Update all the resultant rows to HGRID_FLAG='Y'
37 -- Code to handle if the SORT_ORDER array is empty.
38 IF p_ParamSortOrder IS NOT NULL AND p_ParamSortOrder.COUNT <> 0 THEN
39 FORALL j IN p_ParamSortOrder.FIRST .. p_ParamSortOrder.LAST
40 UPDATE BOM_EXPLOSIONS_ALL SET hgrid_flag='Y' WHERE SORT_ORDER=p_ParamSortOrder(j) AND GROUP_ID=p_GroupId;
41 END IF;
42 --dbms_output.put_line('applying filter: start: ' || to_char(l_st, 'mm-dd-yyyy hh24:mi:ss'));
43 --dbms_output.put_line('applying filter: end : ' || to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss'));
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 null;
47 --debug('The BOM_EXPLOSION_TEMP table doesnt have data...');
48 END applyFilter;
49
50
51 /*******************************************************************
52 * Procedure: addBindParameter
53 * Parameter: p_bind_parameter IN VARCHAR2
54 * Purpose : Helper method to be called for building the query string
55 * with the required bind values.
56 * The java layer would call this for every bind parameter to
57 * be added to the filter query.
58 *
59 *********************************************************************/
60 PROCEDURE addBindParameter(p_bind_parameter VARCHAR2)
61 AS
62 BEGIN
63 G_PARAM_LIST(G_PARAM_LIST.COUNT + 1) := p_bind_parameter;
64 END;
65
66 PROCEDURE clearBindParameter
67 AS
68 BEGIN
69 G_PARAM_LIST.delete;
70 END;
71
72 PROCEDURE enableParents
73 AS
74 l_comps_in_filter BINARY_INTEGER := bom_filter.sort_order_t.COUNT;
75 l_sort_order VARCHAR2(2000);
76 l_st date := sysdate;
77 l_cnt number;
78 BEGIN
79 FOR comps IN 1..l_comps_in_filter
80 LOOP
81 --
82 -- split the sort_order to get all the parents
83 --
84 l_sort_order := bom_filter.sort_order_t(comps);
85 l_cnt := floor(length(l_sort_order)/Bom_Common_Definitions.G_Bom_SortCode_Width) - 1;
86
87 FOR ind IN 1..l_cnt
88 LOOP
89 --bom_filter.sort_order_t.extend;
90 bom_filter.sort_order_t(bom_filter.sort_order_t.COUNT+1) :=
91 substr(l_sort_order,1,(ind*Bom_Common_Definitions.G_Bom_SortCode_Width));
92 END LOOP;
93 END LOOP;
94
95 --dbms_output.put_line('enabling parents done: ' || to_char(l_st, 'mm-dd-yyyy hh24:mi:ss'));
96 --dbms_output.put_line(to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss'));
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 --dbms_output.put_line('error: ' || substr(sqlerrm,1,200));
101 raise;
102 END;
103
104 /*******************************************************************
105 * Procedure: applyFilter
106 * Parameter: p_filterQuery IN VARCHAR2
107 * p_GroupId IN NUMBER
108 * Purpose : Dynamically execute the filter query and apply the filter
109 * to the explosion identified by the given group id
110 *********************************************************************/
111 PROCEDURE applyFilter( p_FilterQuery IN VARCHAR2
112 , p_GroupId IN NUMBER
113 , p_TemplateId IN NUMBER
114 )
115 AS
116 filter_cursor INTEGER := DBMS_SQL.OPEN_CURSOR;
117 rows_processed BINARY_INTEGER;
118 --sort_order_t sort_order := sort_order();
119 ind NUMBER := 0;
120 l_sort_order VARCHAR2(2000);
121 BEGIN
122 --bug: 4277972, delete sort order table before returning list that meets criteria.
123 if sort_order_t.count > 0 then
124 sort_order_t.delete;
125 end if;
126
127 --
128 -- parse the query and get only the sort_orders
129 --
130 --sort_order_t := sort_order();
131 DBMS_SQL.PARSE
132 ( filter_cursor
133 , 'SELECT st_order
134 FROM ( ' || p_filterQuery ||
135 ' ) order by st_order'
136 , DBMS_SQL.NATIVE
137 );
138
139 --DBMS_SQL.DEFINE_COLUMN(filter_cursor,1,l_sort_order,2000);
140 DBMS_SQL.DEFINE_ARRAY(filter_cursor,1,bom_filter.sort_order_t,1000,1);
141 /*
142 DBMS_SQL.PARSE
143 ( filter_cursor
144 , 'SELECT ST_ORDER sort_order ' ||
145 'BULK COLLECT INTO ' || ':'||G_PARAM_LIST.COUNT+1 ||
146 ' FROM ' ||
147 ' ( ' ||
148 p_FilterQuery ||
149 ' ) '
150 , DBMS_SQL.NATIVE
151 );
152 */
153 --
154 -- bind all the parameters and execute
155 --
156 FOR params IN 1..G_PARAM_LIST.COUNT
157 LOOP
158 DBMS_SQL.BIND_VARIABLE(filter_cursor, ':'||params,G_PARAM_LIST(params));
159 END LOOP;
160
161 --
162 -- Execute and fetch all components that match the criteria
163 --
164 rows_processed := DBMS_SQL.EXECUTE(filter_cursor);
165 LOOP
166 -- fetch a row
167 rows_processed := dbms_sql.fetch_rows(filter_cursor);
168 -- fetch columns from the row
169 dbms_sql.column_value(filter_cursor, 1, bom_filter.sort_order_t);
170 EXIT WHEN rows_processed <> 1000;
171
172 END LOOP;
173 DBMS_SQL.CLOSE_CURSOR(filter_cursor);
174 enableParents;
175 applyFilter(bom_filter.sort_order_t, p_GroupId);
176 EXCEPTION
177 WHEN OTHERS THEN
178 IF (DBMS_SQL.IS_OPEN(filter_cursor))
179 THEN
180 DBMS_SQL.CLOSE_CURSOR(filter_cursor);
181 END IF;
182 RAISE;
183 END applyFilter;
184 /* Procedure Apply filter Ends */
185
186 END bom_filter;