[Home] [Help]
PACKAGE BODY: APPS.ZPB_DATA_COLLECTION_UTIL_PVT
Source
1 PACKAGE BODY ZPB_DATA_COLLECTION_UTIL_PVT AS
2 /* $Header: ZPBVDCUB.pls 120.0.12010.2 2005/12/23 06:05:12 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZPB_DATA_COLLECTION_UTIL_PVT';
6
7 -------------------------------------------------------------------------------
8
9 -- values for IN_MOVE_DATA_FLAG and IN_MOVE_TARGET_FLAG are 'Y' or 'N'
10
11 PROCEDURE DISTRIBUTE_TEMPLATE(IN_TEMPLATE_ID IN NUMBER,
12 IN_FROM_USER_ID IN NUMBER,
13 IN_TO_USER_ID IN NUMBER,
14 IN_MOVE_DATA_FLAG IN VARCHAR2,
15 IN_MOVE_TARGET_FLAG IN VARCHAR2,
16 IN_STRUCT_OR_DATA IN VARCHAR2)
17 IS
18 BEGIN
19
20 ZPB_AW.EXECUTE('call DC.DISTRIBUTE(' || TO_CHAR(IN_TEMPLATE_ID) || ', ' || TO_CHAR(IN_FROM_USER_ID) || ', ' || TO_CHAR(IN_TO_USER_ID) || ', ''' || IN_MOVE_DATA_FLAG || ''', ''' || IN_MOVE_TARGET_FLAG || ''', ''' || IN_STRUCT_OR_DATA || ''')' );
21
22 END;
23
24 PROCEDURE UPDATE_AW_DATA(IN_QDRS IN VARCHAR2)
25 IS
26 BEGIN
27 ZPB_AW.EXECUTE('');
28 END;
29
30 PROCEDURE COMMIT_AW_DATA
31 IS
32 BEGIN
33 ZPB_AW.EXECUTE('');
34 END;
35
36
37 PROCEDURE get_dc_owners(p_object_id IN NUMBER,
38 p_user_id IN NUMBER,
39 p_query_type IN VARCHAR2,
40 p_api_version IN NUMBER,
41 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
42 p_commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44 x_owner_list OUT NOCOPY CLOB,
45 x_return_status OUT NOCOPY varchar2,
46 x_msg_count OUT NOCOPY number,
47 x_msg_data OUT NOCOPY varchar2)
48
49
50 IS
51
52 l_api_name CONSTANT VARCHAR2(32) := 'get_dc_owners';
53 l_api_version CONSTANT NUMBER := 1.0;
54
55 l_owners VARCHAR2(2000);
56 l_owner_val VARCHAR2(128);
57 l_stat_len VARCHAR2(128);
58 l_query_path ZPB_DC_OBJECTS.DATAENTRY_OBJ_PATH%type;
59 l_query_name ZPB_DC_OBJECTS.DATAENTRY_OBJ_NAME%type;
60 l_query_key VARCHAR2(512);
61 i NUMBER;
62 j NUMBER;
63 l_length NUMBER;
64 l_lob CLOB;
65
66 BEGIN
67
68 -- Standard Start of API savepoint
69 SAVEPOINT zpb_excp_pvt_populate_results;
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call( l_api_version,
72 p_api_version,
73 l_api_name,
74 G_PKG_NAME)
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78 -- Initialize message list if p_init_msg_list is set to TRUE.
79 IF FND_API.to_Boolean(p_init_msg_list) THEN
80 FND_MSG_PUB.initialize;
81 END IF;
82 -- Initialize API return status to success
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 -- API body
86
87 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Building Data Collection ownership view...');
88
89 select dataentry_obj_path, dataentry_obj_name
90 into l_query_path, l_query_name
91 from zpb_dc_objects
92 where object_id = p_object_id;
93
94 l_query_key := l_query_path || '/' || l_query_name;
95
96 --set zpb_status_sql_members
97 --ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'running zpb_aw_status.run_olapi_queries(' ||
98 -- l_query_key || ').');
99
100 --not needed for new cm.setsqlstatus procecure
101 --zpb_aw_status.run_olapi_queries(l_query_key);
102
103 --run the ownership query
104 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'running sc.get.dstr.own');
105
106 l_stat_len := zpb_aw.interp('shw sc.get.dstr.own(''' || to_char(p_object_id) || ''', ''' ||
107 to_char(p_user_id) || ''', ''' || p_query_type || ''')');
108
109 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'sc.get.dstr.own found ' || l_stat_len || ' owners.');
110
111 --dbms_output.put_line('Stat Length = ' || l_stat_len);
112
113 l_length := to_number(l_stat_len);
114 if (l_length > 0) then
115 l_owners := zpb_aw.interp('shw joinchars(joincols(values(secuser), '' ''))');
116 --dbms_output.put_line(l_owners);
117 l_owners := substr(l_owners, 1, length(l_owners) - 1);
118 i := 1;
119 loop
120 j := instr(l_owners, ' ', i);
121 if (j = 0) then
122 l_owner_val := substr(l_owners, i);
123 else
124 l_owner_val := substr(l_owners, i, j -i);
125 i := j +1;
126 end if;
127
128 l_lob := l_lob || l_owner_val || ',';
129
130 exit when j=0;
131 end loop;
132
133 --remove the comma at end of lob
134 l_length := dbms_lob.getlength(l_lob);
135 dbms_lob.trim(l_lob, l_length -1);
136
137 x_owner_list := l_lob;
138 end if;
139
140 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Data Collection ownership view complete.');
141
142 -- End of API body.
143
144 -- Standard check of p_commit.
145 IF FND_API.To_Boolean( p_commit ) THEN
146 COMMIT WORK;
147 END IF;
148 -- Standard call to get message count and if count is 1, get message info.
149 FND_MSG_PUB.Count_And_Get(
150 p_count => x_msg_count, p_data => x_msg_data );
151
152 EXCEPTION
153 WHEN FND_API.G_EXC_ERROR THEN
154 ROLLBACK TO zpb_excp_pvt_populate_results;
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 FND_MSG_PUB.Count_And_Get(
157 p_count => x_msg_count,
158 p_data => x_msg_data
159 );
160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
161 ROLLBACK TO zpb_excp_pvt_populate_results;
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 FND_MSG_PUB.Count_And_Get(
164 p_count => x_msg_count,
165 p_data => x_msg_data
166 );
167 WHEN OTHERS THEN
168 ROLLBACK TO zpb_excp_pvt_populate_results;
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
171 FND_MSG_PUB.Add_Exc_Msg(
172 G_PKG_NAME,
173 l_api_name
174 );
175 END IF;
176 FND_MSG_PUB.Count_And_Get(
177 p_count => x_msg_count,
178 p_data => x_msg_data
179 );
180
181
182 END get_dc_owners;
183
184 END ZPB_DATA_COLLECTION_UTIL_PVT;
185