[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_TMPL_APPROVAL_GRP
Source
1 PACKAGE BODY OKC_TERMS_TMPL_APPROVAL_GRP AS
2 /* $Header: OKCGTMPLAPPB.pls 120.1 2005/10/07 07:04:45 ndoddi noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_TMPL_APPROVAL_GRP';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21
22 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
23 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
24 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
25
26 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
27 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
28 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
29 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
30 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
31 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
32 G_TEMPLATE_MISS_REC OKC_TERMS_TEMPLATES_PVT.template_rec_type;
33
34 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
35 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
36 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
37
38
39 ---------------------------------------------------------------------------
40 -- Procedure start_approval
41 ---------------------------------------------------------------------------
42 /* added 2 new IN params and 1 out param
43 p_validation_level : 'A' or 'E' do all checks or checks with severity = E
44 p_check_for_drafts : 'Y' or 'N' if Y checks for drafts and inserts them
45 in the OKC_TMPL_DRAFT_CLAUSES table
46 x_sequence_id : contains the sequence id for table OKC_QA_ERRORS_T
47 that contains the validation results
48
49 Existing out param x_qa_return_status will change to
50 have the following statues
51 x_qa_return_status : S if the template was succesfully submitted
52 W if qa check resulted in warnings. Use x_sequence_id
53 to display the qa results.
54 E if qa check resulted in errors. Use x_sequence_id
55 to display the qa results
56 D if there are draft articles and the user should be
57 redirected to the new submit page. Use x_sequence_id
58 if not null, to display a warnings link on the
59 new submit page.
60
61 p_validation_level p_check_for_drafts
62 Search/View/Update : A Y
63 New Submit Page : A N
64 Validation Page : E N
65
66 */
67 PROCEDURE start_approval (
68 p_api_version IN Number,
69 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
70 p_commit IN Varchar2 default FND_API.G_FALSE,
71 p_template_id IN Number,
72 p_object_version_number IN Number default NULL,
73 x_return_status OUT NOCOPY Varchar2,
74 x_msg_data OUT NOCOPY Varchar2,
75 x_msg_count OUT NOCOPY Number,
76 x_qa_return_status OUT NOCOPY Varchar2,
77
78 p_validation_level IN VARCHAR2 DEFAULT 'A',
79 p_check_for_drafts IN VARCHAR2 DEFAULT 'N',
80 x_sequence_id OUT NOCOPY NUMBER)
81 IS
82
83 l_api_version CONSTANT NUMBER := 2;
84 --l_api_version CONSTANT NUMBER := 1;
85 l_api_name CONSTANT VARCHAR2(30) := 'g_start_approval';
86 l_seq_id NUMBER;
87 l_qa_return_status VARCHAR2(30);
88 l_qa_result_tbl OKC_TERMS_QA_GRP.QA_RESULT_TBL_TYPE;
89 l_deliverables_exist VARCHAR2(100);
90 l_dummy_var VARCHAR2(1);
91
92 CURSOR tmpl_csr(pc_template_id NUMBER) IS
93 SELECT object_version_number
94 FROM okc_terms_templates
95 WHERE template_id = pc_template_id;
96
97 CURSOR article_exists_cur IS
98 SELECT 1
99 FROM OKC_K_ARTICLES_B
100 WHERE document_id = p_template_id
101 AND document_type = 'TEMPLATE';
102
103 l_tmpl_rec tmpl_csr%ROWTYPE;
104
105 BEGIN
106
107 /*IF (l_debug = 'Y') THEN
108 okc_debug.log('100: Entered OKC_TERMS_TMPL_APPROVAL_GRP.start_approval', 2);
109 END IF;*/
110
111 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
112 FND_LOG.STRING(G_PROC_LEVEL,
113 G_PKG_NAME, '100: Entered OKC_TERMS_TMPL_APPROVAL_GRP.start_approval' );
114 END IF;
115
116 -- Standard Start of API savepoint
117 SAVEPOINT g_start_approval_GRP;
118
119 -- Standard call to check for call compatibility.
120 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
122 END IF;
123
124 -- Initialize message list if p_init_msg_list is set to TRUE.
125 IF FND_API.to_Boolean( p_init_msg_list ) THEN
126 FND_MSG_PUB.initialize;
127 END IF;
128
129 -- Initialize API return status to success
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131
132
133 /*IF (l_debug = 'Y') THEN
134 okc_debug.log('200: opening tmpl_csr', 2);
135 END IF;*/
136
137 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
138 FND_LOG.STRING(G_PROC_LEVEL,
139 G_PKG_NAME, '200: opening tmpl_csr' );
140 END IF;
141
142 -- Calling Template Apporval PVT API
143
144 OPEN tmpl_csr(p_template_id);
145 FETCH tmpl_csr INTo l_tmpl_rec;
146 IF tmpl_csr%NOTFOUND THEN
147 /*IF (l_debug = 'Y') THEN
148 Okc_Debug.Log('300: - attribute TEMPLATE_ID is invalid', 2);
149 END IF;*/
150
151 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
152 FND_LOG.STRING(G_PROC_LEVEL,
153 G_PKG_NAME, '300: - attribute TEMPLATE_ID is invalid' );
154 END IF;
155 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_ID');
156 x_return_status := G_RET_STS_ERROR;
157 END IF;
158 CLOSE tmpl_csr;
159
160 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 OKC_TERMS_TMPL_APPROVAL_PVT.start_approval(
167 p_api_version => p_api_version,
168 p_init_msg_list => FND_API.G_FALSE,
169 p_commit => FND_API.G_FALSE,
170 p_template_id => p_template_id,
171 p_object_version_number => l_tmpl_rec.object_version_number,
172 x_return_status => x_return_status,
173 x_msg_data => x_msg_data,
174 x_msg_count => x_msg_count,
175 x_qa_return_status => x_qa_return_status,
176 p_validation_level => p_validation_level,
177 p_check_for_drafts => p_check_for_drafts,
178 x_sequence_id => x_sequence_id);
179 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184
185 IF FND_API.To_Boolean( p_commit ) THEN
186 COMMIT WORK;
187 END IF;
188
189 -- Standard call to get message count and if count is 1, get message info.
190 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
191
192 /*IF (l_debug = 'Y') THEN
193 okc_debug.log('1000: Leaving start_approval', 2);
194 END IF;*/
195
196 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
197 FND_LOG.STRING(G_PROC_LEVEL,
198 G_PKG_NAME, '1000: Leaving start_approval' );
199 END IF;
200
201 EXCEPTION
202
203 WHEN FND_API.G_EXC_ERROR THEN
204 /*IF (l_debug = 'Y') THEN
205 okc_debug.log('800: Leaving start_approval: OKC_API.G_EXCEPTION_ERROR Exception', 2);
206 END IF;*/
207
208 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
209 FND_LOG.STRING(G_EXCP_LEVEL,
210 G_PKG_NAME, '800: Leaving start_approval: OKC_API.G_EXCEPTION_ERROR Exception' );
211 END IF;
212
213 ROLLBACK TO g_start_approval_grp;
214 x_return_status := G_RET_STS_ERROR ;
215 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
216
217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
218 /*IF (l_debug = 'Y') THEN
219 okc_debug.log('900: Leaving start_approval: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
220 END IF;*/
221
222 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
223 FND_LOG.STRING(G_EXCP_LEVEL,
224 G_PKG_NAME, '900: Leaving start_approval: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
225 END IF;
226
227 ROLLBACK TO g_start_approval_grp;
228 x_return_status := G_RET_STS_UNEXP_ERROR ;
229 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
230
231 WHEN OTHERS THEN
232 /*IF (l_debug = 'Y') THEN
233 okc_debug.log('1000: Leaving start_approval because of EXCEPTION: '||sqlerrm, 2);
234 END IF;*/
235
236 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
237 FND_LOG.STRING(G_EXCP_LEVEL,
238 G_PKG_NAME, '1000: Leaving start_approval because of EXCEPTION: '||sqlerrm );
239 END IF;
240
241 ROLLBACK TO g_start_approval_grp;
242 x_return_status := G_RET_STS_UNEXP_ERROR ;
243 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
244 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
245 END IF;
246 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
247
248 END start_approval;
249
250 END OKC_TERMS_TMPL_APPROVAL_GRP;