[Home] [Help]
PACKAGE BODY: APPS.FLM_FILTER_CRITERIA_PROCESS
Source
1 PACKAGE BODY flm_filter_criteria_process AS
2 /* $Header: FLMFLCRB.pls 115.3 2003/05/02 00:31:51 hwenas noship $ */
3
4 /* Function to return the column name for the criteria. */
5 FUNCTION get_filter_column (p_criteria_group_type NUMBER, p_criteria_type NUMBER) RETURN VARCHAR2 IS
6 BEGIN
7 IF (p_criteria_group_type = FLM_CONSTANTS.CRITERIA_GROUP_SEQ_DEMAND) THEN
8 IF (p_criteria_type = FLM_CONSTANTS.CRITERIA_ASSEMBLY) THEN
9 return ('ITEM_NUMBER');
10 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_CUST) THEN
11 return ('CUSTOMER_NAME');
12 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_CUST_SITE) THEN
13 return ('SHIP_TO_ADDRESS');
14 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_DEMAND_CLASS) THEN
15 return ('DEMAND_CLASS');
16 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_ORDER_DATE) THEN
17 return ('ORDER_DATE');
18 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_ORDER_LINE) THEN
19 return ('SO_LINE_NUMBER');
20 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_ORDER_NUM) THEN
21 return ('ORDER_NUMBER');
22 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_ORDER_PTY) THEN
23 return ('ORDER_PRIORITY');
24 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_ORDER_QTY) THEN
25 return ('ORIGINAL_ORDER_QUANTITY');
26 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_PLAN_NAME) THEN
27 return ('PLAN_NAME');
28 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_PROJECT) THEN
29 return ('PROJECT_NAME');
30 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_TASK) THEN
31 return ('TASK_NAME');
32 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_SHIP_NUM) THEN
33 return ('SHIPMENT_NUMBER');
34 ELSIF (p_criteria_type = FLM_CONSTANTS.CRITERIA_UNSCH_QTY) THEN
35 return ('ORDER_QUANTITY');
36 END IF;
37 END IF;
38 END get_filter_column;
39
40 /* Procedure to construct the where clause of the FLM filter criteria.
41 The data is obtained from FLM_FILTER_CRITERIA table. */
42 PROCEDURE get_filter_clause (p_criteria_group_id IN NUMBER,
43 p_table_alias IN VARCHAR2,
44 p_init_msg_list IN VARCHAR2,
45 x_filter OUT NOCOPY VARCHAR2,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2)
49 IS
50 l_debug_line NUMBER;
51 l_filter VARCHAR2(5000);
52 l_prev_criteria_operation NUMBER := -1;
53 l_prev_criteria_num NUMBER := -1;
54 l_counter NUMBER := 1;
55
56 CURSOR criteria_list (p_criteria_group_id NUMBER) IS
57 SELECT CRITERIA_GROUP_TYPE,CRITERIA_NUM,CRITERIA_TYPE,CRITERIA_OPERATION,CRITERIA_VALUE_TYPE,
58 CRITERIA_VALUE_NAME,CRITERIA_VALUE_NUM,CRITERIA_VALUE_DATE
59 FROM flm_filter_criteria
60 WHERE CRITERIA_GROUP_ID = p_criteria_group_id
61 ORDER BY CRITERIA_NUM, CRITERIA_OPERATION;
62
63 BEGIN
64 SAVEPOINT flm_get_filter_clause;
65 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
66 THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 l_debug_line := 10;
73 FOR criteria_list_rec IN criteria_list(p_criteria_group_id) LOOP
74
75 l_debug_line := 20;
76 /* Check if this the new operation to be constructed */
77 IF l_prev_criteria_num <> criteria_list_rec.criteria_num THEN
78 IF l_prev_criteria_num <> -1 THEN
79 /*Connecting with the previous operation*/
80 IF l_prev_criteria_operation = FLM_CONSTANTS.OP_LESS_THAN_EQ THEN
81 l_filter := l_filter || ' AND ';
82 ELSIF l_prev_criteria_operation = FLM_CONSTANTS.OP_EQUALS THEN
83 l_filter := l_filter || ' AND ';
84 END IF;
85 END IF;
86
87 /*Opening of the current operation */
88 IF criteria_list_rec.criteria_operation = FLM_CONSTANTS.OP_GREATER_THAN_EQ THEN
89 l_filter := l_filter || p_table_alias || '.' ||
90 get_filter_column(criteria_list_rec.criteria_group_type,criteria_list_rec.criteria_type) || ' BETWEEN ';
91 ELSIF criteria_list_rec.criteria_operation = FLM_CONSTANTS.OP_EQUALS THEN
92 l_filter := l_filter || p_table_alias || '.' ||
93 get_filter_column(criteria_list_rec.criteria_group_type,criteria_list_rec.criteria_type);
94 IF criteria_list_rec.criteria_value_type = FLM_CONSTANTS.VALUE_STRING THEN
95 l_filter := l_filter || ' LIKE ';
96 ELSE
97 l_filter := l_filter || ' = ';
98 END IF;
99 ELSE
100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
101 return;
102 END IF;
103 ELSE
104 /*Connecting within an operation*/
105 IF criteria_list_rec.criteria_operation = FLM_CONSTANTS.OP_LESS_THAN_EQ THEN
106 l_filter := l_filter || ' AND ';
107 ELSE
108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
109 return;
110 END IF;
111 END IF;
112
113 l_debug_line := 30;
114 /*Getting the values for the current operation*/
115 IF criteria_list_rec.criteria_value_type = FLM_CONSTANTS.VALUE_STRING THEN
116 l_filter := l_filter || ':filter_'|| l_counter;
117 FLM_Util.add_bind(':filter_'|| l_counter,
118 criteria_list_rec.criteria_value_name);
119
120 ELSIF criteria_list_rec.criteria_value_type = FLM_CONSTANTS.VALUE_NUM THEN
121 l_filter := l_filter || ':filter_'|| l_counter;
122 FLM_Util.add_bind(':filter_'|| l_counter,
123 criteria_list_rec.criteria_value_num);
124
125 ELSIF criteria_list_rec.criteria_value_type = FLM_CONSTANTS.VALUE_DATE THEN
126 l_filter := l_filter || 'TO_DATE(:filter_'|| l_counter ||
127 ',''DD-MON-RR HH24:MI:SS'')';
128 FLM_Util.add_bind(':filter_'|| l_counter,
129 to_char(criteria_list_rec.criteria_value_date, 'DD-MON-RR HH24:MI:SS'));
130
131 ELSE
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 return;
134 END IF;
135
136 l_debug_line := 40;
137 l_prev_criteria_operation := criteria_list_rec.criteria_operation;
138 l_prev_criteria_num := criteria_list_rec.criteria_num;
139 l_counter := l_counter + 1;
140 END LOOP;
141
142 x_filter := l_filter;
143 l_debug_line := 50;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
148 ROLLBACK TO flm_get_filter_clause;
149
150 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
151 FND_MSG_PUB.Add_Exc_Msg ('flm_filter_criteria_process' ,'get_filter_clause('||l_debug_line||')');
152 END IF;
153
154 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
155
156 END get_filter_clause;
157
158
159 END flm_filter_criteria_process;