[Home] [Help]
PACKAGE BODY: APPS.FEM_ASSEMBLER_PREDICATE_API
Source
1 PACKAGE BODY fem_assembler_predicate_api AS
2 --$Header: FEMASPRDB.pls 120.1.12000000.2 2007/10/26 17:41:12 gcheng ship $
3
4 PROCEDURE GENERATE_ASSEMBLER_PREDICATE(
5 x_predicate_string OUT NOCOPY LONG,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 p_condition_obj_id IN NUMBER,
10 p_rule_effective_date IN VARCHAR2,
11 p_DS_IO_Def_ID IN NUMBER,
12 p_Output_Period_ID IN NUMBER,
13 p_Request_ID IN NUMBER,
14 p_Object_ID IN VARCHAR2,
15 p_Ledger_ID IN NUMBER,
16 p_by_dimension_column IN VARCHAR2,
17 p_by_dimension_id IN NUMBER,
18 p_by_dimension_value IN VARCHAR2,
19 p_fact_table_name IN VARCHAR2,
20 p_table_alias IN VARCHAR2,
21 p_Ledger_Flag IN VARCHAR2 := 'N',
22 p_api_version IN NUMBER := 1.0,
23 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
24 p_commit IN VARCHAR2 := FND_API.G_FALSE,
25 p_encoded IN VARCHAR2 := FND_API.G_TRUE) IS
26
27 l_predicate_string LONG;
28 l_sqlStmt LONG;
29 l_cond_predicate LONG := NULL;
30 l_ds_predicate LONG := NULL;
31 l_return_status1 VARCHAR2(100);
32 l_msg_count1 NUMBER;
33 l_msg_data1 VARCHAR2(500);
34 l_return_status2 VARCHAR2(100);
35 l_msg_count2 NUMBER;
36 l_msg_data2 VARCHAR2(500);
37 BEGIN
38 IF (p_condition_obj_id IS NOT NULL AND p_condition_obj_id <> 0 ) THEN
39 Fem_Conditions_Api.Generate_condition_predicate(
40 p_api_version => p_api_version,
41 p_init_msg_list => p_init_msg_list,
42 p_commit => p_commit,
43 p_encoded => p_encoded,
44 p_condition_obj_id => p_condition_obj_id,
45 p_rule_effective_date => p_rule_effective_date,
46 p_input_fact_table_name => p_fact_table_name,
47 p_table_alias => p_table_alias,
48 p_display_predicate => 'N',
49 p_return_predicate_type => 'BOTH',
50 p_logging_turned_on => 'Y',
51 p_by_dimension_column => p_by_dimension_column,
52 p_by_dimension_id => p_by_dimension_id,
53 p_by_dimension_value => p_by_dimension_value,
54 x_return_status => l_return_status1,
55 x_msg_count => l_msg_count1,
56 x_msg_data => l_msg_data1,
57 x_predicate_string => l_predicate_string);
58
59 l_cond_predicate := l_predicate_string;
60
61 IF ((l_return_status1 = FND_API.G_RET_STS_ERROR) OR (l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
62 l_predicate_string := '';
63 END IF;
64
65 IF ((l_return_status1 = FND_API.G_RET_STS_SUCCESS) AND (substr(rtrim(l_ds_predicate,' '),1,LENGTH(l_ds_predicate)) = '()')) THEN
66 l_predicate_string := '';
67 END IF;
68
69 ELSE
70 l_predicate_string := '';
71 l_return_status1 := FND_API.G_RET_STS_SUCCESS;
72 END IF;
73
74 Fem_ds_where_clause_generator.FEM_Gen_DS_WClause_PVT(
75 p_api_version => p_api_version,
76 p_init_msg_list => p_init_msg_list,
77 p_encoded => p_encoded,
78 x_return_status => l_return_status2,
79 x_msg_count => l_msg_count2,
80 x_msg_data => l_msg_data2,
81 p_DS_IO_Def_ID => p_DS_IO_Def_ID,
82 p_Output_Period_ID => p_Output_Period_ID,
83 p_table_alias => p_table_alias,
84 p_table_name => p_fact_table_name,
85 p_Ledger_ID => p_Ledger_ID,
86 p_where_clause => l_sqlStmt);
87
88 l_ds_predicate := l_sqlStmt;
89
90 IF ((l_return_status2 = FND_API.G_RET_STS_ERROR) OR (l_return_status2 = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
91 l_sqlStmt := '';
92 END IF;
93
94 IF ((l_return_status2 = FND_API.G_RET_STS_SUCCESS) AND (substr(rtrim(l_ds_predicate,' '),1,LENGTH(l_ds_predicate)) = '()')) THEN
95 l_sqlStmt := '';
96 END IF;
97
98 IF (((l_return_status1 = FND_API.G_RET_STS_ERROR) OR (l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR)) AND
99 (l_return_status2 = FND_API.G_RET_STS_SUCCESS)) THEN
100 x_return_status := l_return_status1;
101 x_msg_count := l_msg_count1;
102 x_msg_data := l_msg_data1;
103 ELSIF (((l_return_status2 = FND_API.G_RET_STS_ERROR) OR (l_return_status2 = FND_API.G_RET_STS_UNEXP_ERROR)) AND
104 (l_return_status1 = FND_API.G_RET_STS_SUCCESS)) THEN
105 x_return_status := l_return_status2;
106 x_msg_count := l_msg_count2;
107 x_msg_data := l_msg_data2;
108 ELSIF (((l_return_status1 = FND_API.G_RET_STS_ERROR) OR (l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR)) AND
109 ((l_return_status2 = FND_API.G_RET_STS_ERROR) OR (l_return_status2 = FND_API.G_RET_STS_UNEXP_ERROR))) THEN
110 x_return_status := l_return_status1 || ' and ' || l_return_status2;
111 x_msg_count := 1;
112 x_msg_data := l_msg_data1 || ' and ' || l_msg_data2;
113 ELSE
114 x_return_status := l_return_status1;
115 x_msg_count := l_msg_count1 + l_msg_count2;
116 x_msg_data := l_msg_data1 || l_msg_data2;
117 END IF;
118
119 IF ((l_predicate_string is NOT NULL) AND (l_sqlStmt is NOT NULL) AND (p_Ledger_Flag = 'Y')) THEN
120 x_predicate_string := l_predicate_string || ' AND ' || l_sqlStmt || ' AND '
121 --|| '(' || p_table_alias || '.' || 'CREATED_BY_REQUEST_ID' ||
122 --' <> ' || p_Request_ID || ')' || ' AND '
123 || '(' ||
124 p_table_alias || '.' || 'CREATED_BY_OBJECT_ID' || ' NOT IN(' ||
125 p_Object_ID || ')' || ')' || ' AND ' || '(' || p_table_alias ||
126 '.' || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
127 END IF;
128
129 IF ((l_predicate_string is NOT NULL) AND (l_sqlStmt is NOT NULL) AND (p_Ledger_Flag <> 'Y')) THEN
130 x_predicate_string := l_predicate_string || ' AND ' || l_sqlStmt || ' AND ' || '(' ||
131 p_table_alias || '.' || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
132 END IF;
133
134 IF ((l_predicate_string is NOT NULL) AND (l_sqlStmt is NULL) AND (p_Ledger_Flag = 'Y')) THEN
135 x_predicate_string := l_predicate_string || ' AND '
136 -- || '(' || p_table_alias || '.' || 'CREATED_BY_REQUEST_ID' || ' <> ' ||
137 --p_Request_ID || ')' || ' AND '
138 || '(' || p_table_alias || '.' || 'CREATED_BY_OBJECT_ID' || ' NOT IN(' ||
139 p_Object_ID || ')' || ')' || ' AND ' || '(' || p_table_alias ||
140 '.' || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
141 END IF;
142
143 IF ((l_predicate_string is NOT NULL) AND (l_sqlStmt is NULL) AND (p_Ledger_Flag <> 'Y')) THEN
144 x_predicate_string := l_predicate_string || ' AND ' || '(' || p_table_alias || '.'
145 || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
146 END IF;
147
148 IF ((l_predicate_string is NULL) AND (l_sqlStmt is NOT NULL) AND (p_Ledger_Flag = 'Y')) THEN
149 x_predicate_string := l_sqlStmt || ' AND '
150 --|| '(' || p_table_alias || '.' || 'CREATED_BY_REQUEST_ID' || ' <> ' ||
151 --p_Request_ID || ')' || ' AND '
152 || '(' ||
153 p_table_alias || '.' || 'CREATED_BY_OBJECT_ID' || ' NOT IN(' ||
154 p_Object_ID || ')' || ')' || ' AND ' || '(' || p_table_alias ||
155 '.' || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
156 END IF;
157
158 IF ((l_predicate_string is NULL) AND (l_sqlStmt is NOT NULL) AND (p_Ledger_Flag <> 'Y')) THEN
159 x_predicate_string := l_sqlStmt || ' AND ' || '(' || p_table_alias || '.' ||
160 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
161 END IF;
162
163 IF ((l_predicate_string is NULL) AND (l_sqlStmt is NULL) AND (p_Ledger_Flag = 'Y')) THEN
164 x_predicate_string := --'(' || p_table_alias || '.' || 'CREATED_BY_REQUEST_ID'
165 --|| ' <> ' || p_Request_ID || ')' || ' AND ' ||
166 '(' || p_table_alias || '.' || 'CREATED_BY_OBJECT_ID' || ' NOT IN(' ||
167 p_Object_ID || ')' || ')' || ' AND ' || '(' || p_table_alias ||
168 '.' || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
169 END IF;
170
171 IF ((l_predicate_string is NULL) AND (l_sqlStmt is NULL) AND (p_Ledger_Flag <> 'Y')) THEN
172 x_predicate_string := '(' || p_table_alias || '.' || 'LEDGER_ID' || ' = ' || p_Ledger_ID || ')';
173 END IF;
174
175 END GENERATE_ASSEMBLER_PREDICATE;
176
177 END FEM_ASSEMBLER_PREDICATE_API;