1 PACKAGE BODY PA_PAGE_CONTENTS_PUB AS
2 --$Header: PAPGCTPB.pls 120.1 2006/02/24 02:33:46 appldev noship $
3
4 --Bug 5020365.When this API is called from paxstcvb.Generate_Error_Page, some messages
5 --added in publish flow might already be there in fnd_msg_pub. In this case, this API
6 --should not raise error and let the calling API take care of it. Made changes to not
7 --raise error if the error message is not added to the msg pub by this API
8
9 procedure CREATE_PAGE_CONTENTS (
10
11 p_api_version IN NUMBER := 1.0
12 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
13 ,p_commit IN VARCHAR2 := FND_API.g_false
14 ,p_validate_only IN VARCHAR2 := FND_API.g_true
15 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
16
17 ,P_OBJECT_TYPE IN VARCHAR2
18 ,P_PK1_VALUE IN VARCHAR2
19 ,P_PK2_VALUE IN VARCHAR2 :=NULL
20 ,P_PK3_VALUE IN VARCHAR2 :=NULL
21 ,P_PK4_VALUE IN VARCHAR2 :=NULL
22 ,P_PK5_VALUE IN VARCHAR2 :=NULL
23
24 ,x_page_content_id OUT NOCOPY NUMBER
25 ,x_return_status OUT NOCOPY VARCHAR2
26 ,x_msg_count OUT NOCOPY NUMBER
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ) is
29
30 l_rowid ROWID;
31 l_content_id NUMBER;
32 l_msg_index_out NUMBER;
33 l_init_msg_count NUMBER;--Bug 5020365
34
35
36 CURSOR existing_record IS
37 SELECT PAGE_CONTENT_ID
38 FROM PA_PAGE_CONTENTS
39 WHERE OBJECT_TYPE = P_OBJECT_TYPE
40 AND NVL(PK1_VALUE,0) = NVL(P_PK1_VALUE,0)
41 AND NVL(PK2_VALUE,0) = NVL(P_PK2_VALUE,0)
42 AND NVL(PK3_VALUE,0) = NVL(P_PK3_VALUE,0)
43 AND NVL(PK4_VALUE,0) = NVL(P_PK4_VALUE,0)
44 AND NVL(PK5_VALUE,0) = NVL(P_PK5_VALUE,0);
45
46
47
48 BEGIN
49 -- Initialize the Error Stack
50 PA_DEBUG.init_err_stack('PA_PAGE_CONTENTS_PUB.Save_Page_Contents');
51
52 -- Initialize the return status to success
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 --Clear the global PL/SQL message table
56 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
57 FND_MSG_PUB.initialize;
58 END IF;
59
60 --Bug 5020365. Keep track of the messages that are there in the stack at the begining.
61 l_init_msg_count:=0;
62 l_init_msg_count := fnd_msg_pub.count_msg;
63
64 OPEN existing_record;
65 FETCH existing_record INTO l_content_id;
66
67 IF existing_record%NOTFOUND THEN
68 PA_PAGE_CONTENTS_PVT.ADD_PAGE_CONTENTS (
69 p_api_version
70 ,p_init_msg_list
71 ,p_commit
72 ,p_validate_only
73 ,p_max_msg_count
74
75 ,P_OBJECT_TYPE
76 ,P_PK1_VALUE
77 ,P_PK2_VALUE
78 ,P_PK3_VALUE
79 ,P_PK4_VALUE
80 ,P_PK5_VALUE
81 ,x_page_content_id
82 ,x_return_status
83 ,x_msg_count
84 ,x_msg_data
85 );
86 ELSE
87 x_page_content_id := l_content_id;
88 CLOSE existing_record;
89
90 PA_PAGE_CONTENTS_PVT.CLEAR_PAGE_CONTENTS (
91 p_api_version
92 ,p_init_msg_list
93 ,p_commit
94 ,p_validate_only
95 ,p_max_msg_count
96
97 ,x_page_content_id
98 ,x_return_status
99 ,x_msg_count
100 ,x_msg_data
101 );
102
103 END IF;
104
105
106 -- IF the number of messages is 1 then fetch the message code from the stack
107 -- and return its text
108 -- Bug 5020365. Get the message only if the message is added to stack in this procedure.
109 x_msg_count := FND_MSG_PUB.Count_Msg;
110 IF (l_init_msg_count <>1 AND x_msg_count = 1) THEN
111 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
112 ,p_msg_index => 1
113 ,p_data => x_msg_data
114 ,p_msg_index_out => l_msg_index_out
115 );
116 END IF;
117
118 -- Reset the error stack when returning to the calling program
119 PA_DEBUG.Reset_Err_Stack;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 -- Set the excetption Message and the stack
124 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_CONTENTS_PUB.SAVE_PAGE_CONTENTS'
125 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
126
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
128 RAISE;
129
130 end CREATE_PAGE_CONTENTS;
131
132 procedure DELETE_PAGE_CONTENTS (
133 p_api_version IN NUMBER := 1.0
134 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
135 ,p_commit IN VARCHAR2 := FND_API.g_false
136 ,p_validate_only IN VARCHAR2 := FND_API.g_true
137 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
138
139 ,P_PAGE_CONTENT_ID IN NUMBER :=NULL
140 ,P_OBJECT_TYPE IN VARCHAR2 :=NULL
141 ,P_PK1_VALUE IN VARCHAR2 :=NULL
142 ,P_PK2_VALUE IN VARCHAR2 :=NULL
143 ,P_PK3_VALUE IN VARCHAR2 :=NULL
144 ,P_PK4_VALUE IN VARCHAR2 :=NULL
145 ,P_PK5_VALUE IN VARCHAR2 :=NULL
146
147 -- ,x_page_content_id OUT NUMBER
148 ,x_return_status OUT NOCOPY VARCHAR2
149 ,x_msg_count OUT NOCOPY NUMBER
150 ,x_msg_data OUT NOCOPY VARCHAR2
151 ) is
152 l_rowid ROWID;
153 l_content_id NUMBER;
154 l_msg_index_out NUMBER;
155
156
157 CURSOR existing_record IS
158 SELECT PAGE_CONTENT_ID
159 FROM PA_PAGE_CONTENTS
160 WHERE OBJECT_TYPE = P_OBJECT_TYPE
161 AND NVL(PK1_VALUE,0) = NVL(P_PK1_VALUE,0)
162 AND NVL(PK2_VALUE,0) = NVL(P_PK2_VALUE,0)
163 AND NVL(PK3_VALUE,0) = NVL(P_PK3_VALUE,0)
164 AND NVL(PK4_VALUE,0) = NVL(P_PK4_VALUE,0)
165 AND NVL(PK5_VALUE,0) = NVL(P_PK5_VALUE,0);
166
167 begin
168 -- Initialize the Error Stack
169 PA_DEBUG.init_err_stack('PA_PAGE_CONTENTS_PUB.Delete_Page_Contents');
170
171 -- Initialize the return status to success
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 --Clear the global PL/SQL message table
175 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
176 FND_MSG_PUB.initialize;
177 END IF;
178
179 l_content_id := P_PAGE_CONTENT_ID;
180
181 --Retrieve Page_Content_id from key values
182 IF P_PAGE_CONTENT_ID is NULL THEN
183 IF P_OBJECT_TYPE is NOT NULL THEN
184 OPEN existing_record;
185 FETCH existing_record INTO l_content_id;
186 IF existing_record%NOTFOUND THEN
187 CLOSE existing_record;
188 return;
189 END IF;
190 CLOSE existing_record;
191 ELSE
192 return;
193 END IF;
194 END IF;
195
196 PA_PAGE_CONTENTS_PVT.DELETE_PAGE_CONTENTS (
197 p_api_version
198 ,p_init_msg_list
199 ,p_commit
200 ,p_validate_only
201 ,p_max_msg_count
202
203 ,l_content_id
204 ,x_return_status
205 ,x_msg_count
206 ,x_msg_data);
207
208 -- IF the number of messages is 1 then fetch the message code from the stack
209 -- and return its text
210 x_msg_count := FND_MSG_PUB.Count_Msg;
211 IF x_msg_count = 1 THEN
212 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
213 ,p_msg_index => 1
214 ,p_data => x_msg_data
215 ,p_msg_index_out => l_msg_index_out
216 );
217 END IF;
218
219 -- Reset the error stack when returning to the calling program
220 PA_DEBUG.Reset_Err_Stack;
221
222
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 rollback;
227 -- Set the excetption Message and the stack
228 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_CONTENTS_PUB.DELETE_PAGE_CONTENTS'
229 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
230
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
232 RAISE;
233 end DELETE_PAGE_CONTENTS;
234
235 END PA_PAGE_CONTENTS_PUB;