[Home] [Help]
PACKAGE BODY: APPS.ZPB_DC_UTIL
Source
1 PACKAGE BODY ZPB_DC_UTIL AS
2 /* $Header: ZPBDCUTB.pls 120.1 2007/12/04 14:33:44 mbhat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ZPB_DC_UTIL';
5
6 /*=========================================================================+
7 | PROCEDURE freeze_worksheet
8 |
9 | DESCRIPTION
10 | Procedure freezes the worksheet with the specified object_id in
11 | ZPB_DC_OBJECTS table.
12 |
13 | NOTE: FND context has to be initialized before calling this API.
14 |
15 +=========================================================================*/
16 PROCEDURE freeze_worksheet
17 (
18 p_object_id IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg OUT NOCOPY VARCHAR2
21 )
22 IS
23 l_api_name CONSTANT VARCHAR2(30) := 'freeze_worksheet';
24 l_return_status VARCHAR2(1) ;
25 l_prior_lock_status ZPB_DC_OBJECTS.STATUS%type ;
26
27 BEGIN
28
29 -- get the status of the worksheet
30 SELECT status
31 INTO l_prior_lock_status
32 FROM ZPB_DC_OBJECTS
33 WHERE object_id = p_object_id;
34
35 UPDATE ZPB_DC_OBJECTS
36 SET status = 'LOCKED',
37 prior_lock_status = l_prior_lock_status,
38 freeze_flag = 'Y',
39 --who columns
40 last_updated_by = fnd_global.USER_ID,
41 last_update_date = SYSDATE,
42 last_update_login = fnd_global.LOGIN_ID
43 WHERE object_id = p_object_id;
44
45 IF(SQL%ROWCOUNT > 0)
46 THEN
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48 x_msg :='Row updated successfully';
49 ELSE
50 x_return_status := FND_API.G_RET_STS_ERROR;
51 x_msg :='Object_id ' ||p_object_id ||' not found ';
52 END IF;
53
54 COMMIT;
55
56 EXCEPTION
57
58 WHEN NO_DATA_FOUND THEN
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 x_msg :='Object_id ' ||p_object_id ||' not found ';
61
62
63 WHEN OTHERS THEN
64 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
65 x_msg:=substr(sqlerrm, 1, 255);
66
67 END freeze_worksheet;
68
69 /*=========================================================================+
70 | PROCEDURE unfreeze_worksheet
71 |
72 | DESCRIPTION
73 | Procedure unfreezes the worksheet with the specified object_id in
74 | ZPB_DC_OBJECTS table.
75 |
76 | NOTE: FND context has to be initialized before calling this API
77 |
78 +=========================================================================*/
79
80 PROCEDURE unfreeze_worksheet
81 (
82 p_object_id IN NUMBER,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg OUT NOCOPY VARCHAR2
85 )
86 IS
87 l_api_name CONSTANT VARCHAR2(30) := 'unfreeze_worksheet';
88 l_return_status VARCHAR2(1) ;
89 l_prior_lock_status ZPB_DC_OBJECTS.PRIOR_LOCK_STATUS%type ;
90
91 BEGIN
92
93 -- get the previous status of the the worksheet
94 SELECT prior_lock_status
95 INTO l_prior_lock_status
96 FROM ZPB_DC_OBJECTS
97 WHERE object_id = p_object_id;
98
99 UPDATE ZPB_DC_OBJECTS
100 SET status = l_prior_lock_status,
101 freeze_flag = 'N',
102 --who columns
103 last_updated_by = fnd_global.USER_ID,
104 last_update_date = SYSDATE,
105 last_update_login = fnd_global.LOGIN_ID
106 WHERE object_id = p_object_id
107 AND status = 'LOCKED';
108
109 IF(SQL%ROWCOUNT > 0)
110 THEN
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112 x_msg :='Row updated successfully';
113 ELSE
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 x_msg :='No locked worksheet to unfreeze for Object_id ' || p_object_id;
116 END IF;
117
118 COMMIT;
119
120 EXCEPTION
121
122 WHEN NO_DATA_FOUND THEN
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 x_msg :='Object_id ' ||p_object_id ||' not found ';
125
126 WHEN OTHERS THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 x_msg:=substr(sqlerrm, 1, 255);
129
130 END unfreeze_worksheet;
131
132 /*=========================================================================+
133 | PROCEDURE refresh_worksheet
134 |
135 | DESCRIPTION
136 | Procedure set the worksheet with the specified object_id
137 | for refresh from shared in ZPB_DC_OBJECTS table.
138 |
139 | NOTE: FND context has to be initialized before calling this API
140 |
141 +=========================================================================*/
142
143 PROCEDURE refresh_worksheet
144 (
145 p_object_id IN NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg OUT NOCOPY VARCHAR2
148 )
149 IS
150 l_api_name CONSTANT VARCHAR2(30) := 'refresh_worksheet';
151 l_return_status VARCHAR2(1) ;
152
153
154
155 BEGIN
156
157 UPDATE ZPB_DC_OBJECTS
158 SET status = 'DISTRIBUTION_PENDING',
159 freeze_flag = 'N',
160 distributor_user_id = -100,
161 --who columns
162 last_updated_by = fnd_global.USER_ID,
163 last_update_date = SYSDATE,
164 last_update_login = fnd_global.LOGIN_ID
165 WHERE object_id = p_object_id;
166
167 IF(SQL%ROWCOUNT > 0)
168 THEN
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170 x_msg :='Row updated successfully';
171 ELSE
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 x_msg :='Object_id ' ||p_object_id ||' not found ';
174 END IF;
175
176 COMMIT;
177
178 EXCEPTION
179
180 WHEN OTHERS THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 x_msg:=substr(sqlerrm, 1, 255);
183
184 END refresh_worksheet;
185
186 END ZPB_DC_UTIL;