1 PACKAGE BODY igs_ps_unit_lgcy_pvt AS
2 /* $Header: IGSPS93B.pls 120.2 2005/09/27 01:35:52 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'igs_ps_unit_lgcy_pvt';
6
7 PROCEDURE create_unit(
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2,
10 p_commit IN VARCHAR2,
11 p_validation_level IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 p_unit_ver_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ver_rec_type,
16 p_unit_tr_tbl IN OUT NOCOPY igs_ps_generic_pub.unit_tr_tbl_type,
17 p_unit_dscp_tbl IN OUT NOCOPY igs_ps_generic_pub.unit_dscp_tbl_type,
18 p_unit_gs_tbl IN OUT NOCOPY igs_ps_generic_pub.unit_gs_tbl_type,
19 p_usec_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_tbl_type,
20 p_usec_gs_tbl IN OUT NOCOPY igs_ps_generic_pub.usec_gs_tbl_type,
21 p_uso_tbl IN OUT NOCOPY igs_ps_generic_pub.uso_tbl_type,
22 p_unit_ref_tbl IN OUT NOCOPY igs_ps_generic_pub.unit_ref_tbl_type,
23 p_uso_ins_tbl IN OUT NOCOPY igs_ps_generic_pub.uso_ins_tbl_type ) AS
24
25 /***********************************************************************************************
26 Created By: Sanjeeb Rakshit
27 Date Created By: 20-Nov-2002
28 Purpose: This is a public API to import data from external system to OSS.
29 Known limitations,enhancements,remarks:
30
31 Change History
32
33 Who When What
34 sommukhe 27-SEP-2005 BUG #4632652.FND logging included.
35 smvk 07-Nov-2003 Bug # 3138353. Added the call to validate_unit_dtls, which does unit level cross
36 subprocesses validation.
37 jbegum 02-june-2003 Bug # 2972950. Added the call to unit section occcurrence instructor sub process
38 As Mentioned in TD.
39 ***********************************************************************************************/
40 l_api_name CONSTANT VARCHAR2(30) := 'create_unit';
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_rec_status VARCHAR2(1) :='S';
43 l_record_exists BOOLEAN := FALSE;
44 BEGIN
45
46 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
47 fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pvt.create_unit.start_logging_for',
48 'Data import from external Sysytem to OSS -LEGACY ');
49 END IF;
50
51 --Standard start of API savepoint
52 SAVEPOINT Create_Unit_PVT;
53
54 --Standard call to check for call compatibility
55 IF NOT FND_API.Compatible_API_Call(l_api_version ,
56 p_api_version ,
57 l_api_name ,
58 G_PKG_NAME ) THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 --Initialize message list if p_init_msg_list is set to TRUE
63 IF FND_API.to_Boolean(p_init_msg_list) THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66
67
68 --Initialize API return status to success
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 --API body
72 --Call Unit Version sub process
73 IF p_unit_ver_rec.unit_cd IS NOT NULL AND p_unit_ver_rec.version_number IS NOT NULL THEN
74 igs_ps_unit_lgcy_pkg.create_unit_version(p_unit_ver_rec,l_rec_status);
75 l_record_exists := TRUE;
76 IF l_rec_status = 'E' THEN
77 RAISE FND_API.G_EXC_ERROR;
78 END IF;
79 END IF;
80
81 --Call the Unit teaching responsibility sub process
82 IF p_unit_tr_tbl.COUNT > 0 THEN
83 igs_ps_unit_lgcy_pkg.create_teach_resp( p_unit_tr_tbl,l_rec_status);
84 l_record_exists := TRUE;
85
86 IF l_rec_status = 'E' THEN
87 x_return_status := 'E';
88 END IF;
89 END IF;
90
91
92 --Call the Unit discipline sub process
93 IF p_unit_dscp_tbl.COUNT > 0 THEN
94 igs_ps_unit_lgcy_pkg.create_unit_discip( p_unit_dscp_tbl,l_rec_status);
95 l_record_exists := TRUE;
96
97 IF l_rec_status = 'E' THEN
98 x_return_status := 'E';
99 END IF;
100 END IF;
101
102 --Call the Unit grading schema sub process
103 IF p_unit_gs_tbl.COUNT > 0 THEN
104 igs_ps_unit_lgcy_pkg.create_unit_grd_sch( p_unit_gs_tbl,l_rec_status);
105 l_record_exists := TRUE;
106
107 IF l_rec_status = 'E' THEN
108 x_return_status := 'E';
109 END IF;
110 END IF;
111
112 IF NVL(p_unit_ver_rec.status,'E') = 'S' THEN
113 igs_ps_unit_lgcy_pkg.validate_unit_dtls( p_unit_ver_rec, l_rec_status);
114 IF l_rec_status = 'E' THEN
115 x_return_status := 'E';
116 END IF;
117 END IF;
118
119
120 --Call the Unit Section sub process
121 IF p_usec_tbl.COUNT > 0 THEN
122 igs_ps_unit_lgcy_pkg.create_unit_section( p_usec_tbl,l_rec_status,'L');
123 l_record_exists := TRUE;
124
125 IF l_rec_status = 'E' THEN
126 x_return_status := 'E';
127 END IF;
128 END IF;
129
130 --Call the Unit Section Grading schema sub process
131 IF p_usec_gs_tbl.COUNT > 0 THEN
132 igs_ps_unit_lgcy_pkg.create_usec_grd_sch( p_usec_gs_tbl,l_rec_status,'L');
133 l_record_exists := TRUE;
134
135 IF l_rec_status = 'E' THEN
136 x_return_status := 'E';
137 END IF;
138 END IF;
139
140 --Call the Unit Section Occurrence sub process
141 IF p_uso_tbl.COUNT > 0 THEN
142
143 igs_ps_unit_lgcy_pkg.create_usec_occur( p_uso_tbl,l_rec_status,'L');
144 l_record_exists := TRUE;
145
146 IF l_rec_status = 'E' THEN
147 x_return_status := 'E';
148 END IF;
149 END IF;
150
151 --Call the Unit Section Reference Code sub process
152 IF p_unit_ref_tbl.COUNT > 0 THEN
153 igs_ps_unit_lgcy_pkg.create_unit_ref_code( p_unit_ref_tbl,l_rec_status,'L');
154 l_record_exists := TRUE;
155
156 IF l_rec_status = 'E' THEN
157 x_return_status := 'E';
158 END IF;
159 END IF;
160
161 -- Call the Unit Section Occurence Instructors sub process
162 IF p_uso_ins_tbl.COUNT > 0 THEN
163 igs_ps_unit_lgcy_pkg.create_uso_ins(p_uso_ins_tbl,l_rec_status);
164 l_record_exists := TRUE;
165
166 IF l_rec_status = 'E' THEN
167 x_return_status := 'E';
168 END IF;
169 END IF;
170
171 --If none of the PL/SQL data has been passed then raise error
172 IF NOT l_record_exists THEN
173 FND_MESSAGE.SET_NAME('IGS','IGS_PS_LGCY_DATA_NOT_PASSED');
174 FND_MSG_PUB.ADD;
175 RAISE FND_API.G_EXC_ERROR;
176 END IF;
177
178 --If return status is error then raise expected exception to rollback the changes
179 IF x_return_status = 'E' THEN
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182
183 --End of API body
184
185 --Standard check of p_commit
186 IF FND_API.TO_Boolean( p_commit) THEN
187 COMMIT WORK;
188 END IF;
189
190 --Standard call to get message count and if count is 1, get message info.
191 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
192 p_data => x_msg_data);
193
194 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
195 fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pvt.create_unit.end_of_logging_for',
196 'Data import from external Sysytem to OSS -LEGACY ');
197 END IF;
198
199
200 EXCEPTION
201 WHEN FND_API.G_EXC_ERROR THEN
202 ROLLBACK TO Create_Unit_PVT;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
205 p_data => x_msg_data );
206
207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
208 ROLLBACK TO Create_Unit_PVT;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
211 p_data => x_msg_data );
212
213 WHEN OTHERS THEN
214 ROLLBACK TO Create_Unit_PVT;
215
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
218 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
219 l_api_name);
220 END IF;
221 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
222 p_data => x_msg_data );
223 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
224 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_unit_lgcy_pvt.create_unit.in_exception_section_OTHERS.err_msg',
225 SUBSTRB(SQLERRM,1,4000));
226 END IF;
227
228 END create_unit;
229
230 END igs_ps_unit_lgcy_pvt;