[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_REV_CL_PVT
Source
1 PACKAGE BODY CN_IMP_REV_CL_PVT AS
2 -- $Header: cnvimrcb.pls 120.1 2005/08/07 23:04:02 vensrini noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMP_REV_CL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimrcb.pls';
6
7 -- Start of comments
8 -- API name : RevCl_Import
9 -- Type : Private.
10 -- Function : programtransfer data from staging table into
11 -- cn_revenue_classes_all
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- IN p_imp_header_id IN NUMBER,
15 -- OUT : errbuf OUT VARCHAR2 Required
16 -- retcode OUTVARCHAR2 Optional
17 -- Version : Current version 1.0
18 --
19 --
20 --
21 -- Notes : The import process will terminated when error occurs.
22 -- Cannot partially import data because if error happens and re-run
23 -- SQL*Loader to stage fixed data,the old one will be deleted first.
24 -- Because SQL*Loader run in APPEND mode.
25 -- End of comments
26
27 PROCEDURE RevCl_Import
28 (errbuf OUT NOCOPY VARCHAR2,
29 retcode OUT NOCOPY VARCHAR2,
30 p_imp_header_id IN NUMBER,
31 p_org_id IN NUMBER
32 ) IS
33
34 l_status_code cn_imp_lines.status_code%TYPE := 'STAGE';
35
36 CURSOR c_rev_cl_imp_csr IS
37 SELECT
38 imp_line_id,
39 imp_header_id,
40 status_code,
41 error_code,
42 name,
43 description
44
45 FROM CN_REVENUE_CLASSES_IMP_V
46 WHERE imp_header_id = p_imp_header_id
47 AND status_code = l_status_code
48 ;
49
50 Cursor get_rev_cls( p_revenue_class_name cn_revenue_classes.name%TYPE ) IS
51 select count(1)
52 from cn_revenue_classes
53 where name = p_revenue_class_name and org_id=p_org_id;
54
55 l_api_name CONSTANT VARCHAR2(30) := 'RevCl_Import';
56 l_api_version CONSTANT NUMBER := 1.0;
57
58 l_rev_cl_imp c_rev_cl_imp_csr%ROWTYPE;
59 l_rev_class_name cn_revenue_classes.name%TYPE;
60
61 l_return_status VARCHAR2(1);
62 -- l_msg_count NUMBER;
63 l_msg_data VARCHAR2(2000);
64 l_loading_status VARCHAR2(30);
65 l_revenue_class_id NUMBER;
66
67 l_processed_row NUMBER := 0;
68 l_failed_row NUMBER := 0;
69 l_message VARCHAR2(2000);
70 l_error_code VARCHAR2(30);
71 l_revenue_classes_id NUMBER(15);
72 l_header_list VARCHAR2(2000);
73 l_sql_stmt VARCHAR2(2000);
74 l_count NUMBER;
75 err_num NUMBER;
76 l_msg_count NUMBER := 0;
77 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
78 l_rev_class_rec cn_revenue_class_pvt.revenue_class_rec_type;
79 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
80
81 BEGIN
82 retcode := 0 ;
83
84 -- Get imp_header info
85 SELECT name, status_code,server_flag,imp_map_id, source_column_num,
86 import_type_code
87 INTO l_imp_header.name ,l_imp_header.status_code ,
88 l_imp_header.server_flag, l_imp_header.imp_map_id,
89 l_imp_header.source_column_num,l_imp_header.import_type_code
90 FROM cn_imp_headers
91 WHERE imp_header_id = p_imp_header_id;
92
93 -- open process audit batch
94 cn_message_pkg.begin_batch
95 ( x_process_type => l_imp_header.import_type_code,
96 x_parent_proc_audit_id => p_imp_header_id ,
97 x_process_audit_id => l_process_audit_id,
98 x_request_id => null,
99 p_org_id => p_org_id);
100
101 cn_message_pkg.write
102 (p_message_text => 'REVCL: Start Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
103 p_message_type => 'MILESTONE');
104
105 -- Get source column name list and target column dynamic sql statement
106 CN_IMPORT_PVT.build_error_rec
107 (p_imp_header_id => p_imp_header_id,
108 x_header_list => l_header_list,
109 x_sql_stmt => l_sql_stmt);
110
111 OPEN c_rev_cl_imp_csr;
112 LOOP
113 FETCH c_rev_cl_imp_csr INTO l_rev_cl_imp;
114 EXIT WHEN c_rev_cl_imp_csr%notfound;
115
116 l_processed_row := l_processed_row + 1;
117
118 cn_message_pkg.debug('REVCL:Record ' || To_char(l_processed_row) || ' imp_line_id = ' || To_char(l_rev_cl_imp.imp_line_id));
119 -- -------- Checking for all required fields ----------------- --
120 -- Check required field
121
122 -- insert into cn_comm_lines_api
123 l_rev_class_rec.revenue_class_id := l_revenue_class_id;
124 l_rev_class_rec.name := l_rev_cl_imp.name;
125 l_rev_class_rec.description := l_rev_cl_imp.description;
126 l_rev_class_rec.liability_account_id := NULL;
127 l_rev_class_rec.expense_account_id := NULL;
128 l_rev_class_rec.object_version_number := NULL;
129
130 CN_REVENUE_CLASS_PVT.Create_Revenue_Class
131 ( p_api_version => 1.0,
132 x_return_status => l_return_status,
133 p_init_msg_list => FND_API.G_TRUE,
134 x_msg_count => l_msg_count,
135 x_msg_data => l_msg_data,
136 x_loading_status => l_loading_status,
137 x_revenue_class_id => l_revenue_class_id,
138 p_revenue_class_rec => l_rev_class_rec,
139 p_org_id => p_org_id);
140
141 if (l_return_status = FND_API.G_RET_STS_ERROR) then
142 begin
143
144 l_failed_row := l_failed_row + 1;
145 CN_IMPORT_PVT.update_imp_lines
146 (p_imp_line_id => l_rev_cl_imp.imp_line_id,
147 p_status_code => 'FAIL',
148 p_error_code => l_loading_status);
149 CN_IMPORT_PVT.update_imp_headers
150 (p_imp_header_id => p_imp_header_id,
151 p_status_code => 'IMPORT_FAIL',
152 p_processed_row => l_processed_row,
153 p_failed_row => l_failed_row);
154 cn_message_pkg.write
155 (p_message_text => l_msg_data,
156 p_message_type => 'ERROR');
157 CN_IMPORT_PVT.write_error_rec
158 (p_imp_header_id => p_imp_header_id,
159 p_imp_line_id => l_rev_cl_imp.imp_line_id,
160 p_header_list => l_header_list,
161 p_sql_stmt => l_sql_stmt);
162
163 retcode := 2;
164 errbuf := l_msg_data;
165 GOTO end_loop;
166 end;
167
168 else
169
170 begin
171 l_error_code := '';
172 CN_IMPORT_PVT.update_imp_lines
173 (p_imp_line_id => l_rev_cl_imp.imp_line_id,
174 p_status_code => 'COMPLETE',
175 p_error_code => l_error_code);
176 cn_message_pkg.debug('REVCL:Import completed. revenue_classes_id = ' || To_char(l_revenue_class_id));
177 end;
178 end if;
179 << end_loop>>
180 NULL;
181 END LOOP; -- c_rev_cl_imp_csr
182 IF c_rev_cl_imp_csr%ROWCOUNT = 0 THEN
183 l_processed_row := 0;
184 END IF;
185 CLOSE c_rev_cl_imp_csr;
186 IF l_failed_row = 0 AND retcode = 0 THEN
187 -- update update_imp_headers
188 CN_IMPORT_PVT.update_imp_headers
189 (p_imp_header_id => p_imp_header_id,
190 p_status_code => 'COMPLETE',
191 p_processed_row => l_processed_row,
192 p_failed_row => l_failed_row);
193 END IF;
194
195 cn_message_pkg.write
196 (p_message_text => 'REVCL: End Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
197 p_message_type => 'MILESTONE');
198
199 -- close process batch
200 cn_message_pkg.end_batch(l_process_audit_id);
201
202 -- Commit all imports
203 COMMIT;
204
205
206 END RevCL_Import;
207 END CN_IMP_REV_CL_PVT;