1 PACKAGE BODY gl_coa_segment_val_pvt AS
2 /* $Header: GLSVIPVB.pls 120.2 2011/01/28 14:29:32 sommukhe noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'gl_coa_segment_val_pvt';
5
6 PROCEDURE coa_segment_val_imp (
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
9 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
10 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_gl_flex_values_tbl IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_tbl_type,
15 p_gl_flex_values_nh_tbl IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_tbl_type,
16 p_gl_flex_values_status OUT NOCOPY VARCHAR2,
17 p_gl_flex_values_nh_status OUT NOCOPY VARCHAR2
18
19 ) AS
20 /***********************************************************************************************
21 Created By: Somnath Mukherjee
22 Date Created By: 01-AUG-2008
23 Purpose: This is a public API to import data from external system to GL.
24 Known limitations,enhancements,remarks:
25
26 Change History
27
28 Who When What
29 ***********************************************************************************************/
30
31
32 l_api_name CONSTANT VARCHAR2(30) := 'Flex_Values_import';
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_record_exists BOOLEAN := FALSE;
35 BEGIN
36
37 --Standard start of API savepoint
38 SAVEPOINT gl_coa_segment_val_pvt;
39
40 --Standard call to check for call compatibility
41 IF NOT FND_API.Compatible_API_Call(l_api_version ,
42 p_api_version ,
43 l_api_name ,
44 G_PKG_NAME) THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47
48 --Initialize message list if p_init_msg_list is set to TRUE
49 IF FND_API.to_Boolean(p_init_msg_list) THEN
50 FND_MSG_PUB.initialize;
51 END IF;
52
53
54 --API body
55
56
57 --Initialize API return status to success
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60
61 --API body
62 p_gl_flex_values_status := 'S';
63 p_gl_flex_values_nh_status := 'S';
64
65
66 --Similarly for all the other PL/SQL table status variable
67
68 --Call the fnd_flex_val sub process
69 IF p_gl_flex_values_tbl.COUNT > 0 THEN
70
71
72 --gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values( p_gl_flex_values_tbl,p_gl_flex_values_status);
73 GL_COA_SEG_VAL_IMP_PKG.create_gl_coa_flex_values(
74 p_gl_flex_values_tbl => p_gl_flex_values_tbl,
75 p_c_rec_status => p_gl_flex_values_status,
76 p_validation_level => p_validation_level); /* DRM: Added */
77
78 l_record_exists := TRUE;
79 IF p_gl_flex_values_status = 'E' THEN
80 --Set the API status to 'E'
81 x_return_status := 'E';
82 END IF;
83
84 FOR I in 1..p_gl_flex_values_tbl.LAST LOOP
85 IF p_gl_flex_values_tbl.EXISTS(I) THEN
86 IF p_gl_flex_values_tbl(I).status = 'S' THEN
87 p_gl_flex_values_tbl(I).status := 'P';
88 END IF;
89 END IF;
90 END LOOP;
91
92 END IF;
93
94
95 --Call the fnd_flex_val_norm_hierarchy sub process
96 IF p_gl_flex_values_nh_tbl.COUNT > 0 THEN
97 --gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values_nh( p_gl_flex_values_nh_tbl,p_gl_flex_values_nh_status);
98 GL_COA_SEG_VAL_IMP_PKG.create_gl_coa_flex_values_nh(
99 p_gl_flex_values_nh_tbl => p_gl_flex_values_nh_tbl,
100 p_c_rec_status => p_gl_flex_values_nh_status,
101 p_validation_level => p_validation_level); /* DRM: Added */
102 l_record_exists := TRUE;
103
104 IF p_gl_flex_values_nh_status = 'E' THEN
105 --Set the API status to 'E'
106 x_return_status := 'E';
107 END IF;
108
109 FOR I in 1..p_gl_flex_values_nh_tbl.LAST LOOP
110 IF p_gl_flex_values_nh_tbl.EXISTS(I) THEN
111 IF p_gl_flex_values_nh_tbl(I).status = 'S' THEN
112 p_gl_flex_values_nh_tbl(I).status := 'P';
113 END IF;
114 END IF;
115 END LOOP;
116 END IF;
117
118
119 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
120 fnd_log.string( fnd_log.level_procedure, 'gl.plsql.gl_coa_segment_val_pvt.Flex_Values_import.end_of_logging_for',
121 'Data import from external Sysytem to GL ');
122 END IF;
123
124 --If none of the PL/SQL data has been passed then raise error
125 IF NOT l_record_exists THEN
126 FND_MESSAGE.SET_NAME ('GL','GL_COA_SVI_DATA_NOT_PASSED');
127 FND_MSG_PUB.ADD;
128 RAISE FND_API.G_EXC_ERROR;
129 END IF;
130
131 --End of API body
132
133 --Standard check of p_commit
134 IF FND_API.TO_Boolean( p_commit) THEN
135 COMMIT WORK;
136 END IF;
137
138 --Standard call to get message count and if count is 1, get message info.
139 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
140 p_data => x_msg_data);
141
142
143 EXCEPTION
144 WHEN FND_API.G_EXC_ERROR THEN
145 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception1:');
146 ROLLBACK;
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
149 p_data => x_msg_data );
150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
151 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception2:');
152 ROLLBACK;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
155 p_data => x_msg_data );
156
157 WHEN OTHERS THEN
158 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception3:'||SQLERRM);
159 ROLLBACK ;
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
162 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
163 l_api_name);
164 END IF;
165 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
166 p_data => x_msg_data );
167 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
168 fnd_log.string( fnd_log.level_exception, 'gl.plsql.gl_coa_segment_val_pvt.Flex_Values_import.in_exception_section_OTHERS.err_msg',
169 SUBSTRB(SQLERRM,1,4000));
170 END IF;
171
172 END coa_segment_val_imp;
173
174 END gl_coa_segment_val_pvt;