1 PACKAGE csi_mass_edit_pub AUTHID CURRENT_USER as
2 /* $Header: csipmees.pls 120.11 2011/03/30 11:15:13 sjawaji ship $ */
3 -- Start of Comments
4 -- Package name : CSI_MASS_EDIT_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 -- Default number of records fetch per call
11 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
12 g_user_id NUMBER := FND_GLOBAL.user_id;
13 g_login_id NUMBER := FND_GLOBAL.login_id;
14
15
16 TYPE mass_edit_rec IS RECORD
17 (
18 entry_id NUMBER := fnd_api.g_miss_num
19 ,name VARCHAR2(50) := fnd_api.g_miss_char
20 ,txn_line_id NUMBER := fnd_api.g_miss_num
21 ,txn_line_detail_id NUMBER := fnd_api.g_miss_num
22 ,status_code VARCHAR2(30) := fnd_api.g_miss_char
23 ,batch_type VARCHAR2(30) := fnd_api.g_miss_char
24 ,description VARCHAR2(2000):= fnd_api.g_miss_char
25 ,schedule_date DATE := fnd_api.g_miss_date
26 ,start_date DATE := fnd_api.g_miss_date
27 ,end_date DATE := fnd_api.g_miss_date
28 ,object_version_number NUMBER := fnd_api.g_miss_num
29 ,system_cascade VARCHAR2(1) := fnd_api.g_miss_char
30 );
31
32 TYPE mass_edit_tbl IS TABLE OF mass_edit_rec INDEX BY BINARY_INTEGER;
33
34 TYPE mass_edit_inst_rec is RECORD
35 (
36 TXN_LINE_DETAIL_ID NUMBER := fnd_api.g_miss_num
37 ,INSTANCE_ID NUMBER := fnd_api.g_miss_num
38 ,ACTIVE_END_DATE DATE := fnd_api.g_miss_date
39 ,OBJECT_VERSION_NUMBER NUMBER := fnd_api.g_miss_num
40 );
41
42 TYPE mass_edit_inst_tbl IS TABLE OF mass_edit_inst_rec INDEX BY BINARY_INTEGER;
43
44 TYPE mass_edit_error_rec IS RECORD
45 (
46 ENTRY_ID NUMBER := FND_API.G_MISS_NUM ,
47 TXN_LINE_DETAIL_ID NUMBER := FND_API.G_MISS_NUM ,
48 INSTANCE_ID NUMBER := FND_API.G_MISS_NUM ,
49 ERROR_TEXT VARCHAR2(2000) := FND_API.G_MISS_CHAR,
50 ERROR_CODE VARCHAR2(1) := FND_API.G_MISS_CHAR,
51 NAME VARCHAR2(50) := FND_API.G_MISS_CHAR
52 );
53
54 TYPE mass_edit_error_tbl IS TABLE OF mass_edit_error_rec INDEX BY BINARY_INTEGER;
55
56 TYPE mass_edit_sys_error_rec IS RECORD
57 (
58 ENTRY_ID NUMBER := FND_API.G_MISS_NUM ,
59 BATCH_NAME VARCHAR2(30) := FND_API.G_MISS_CHAR,
60 TXN_LINE_DETAIL_ID NUMBER := FND_API.G_MISS_NUM ,
61 SYSTEM_ID NUMBER := FND_API.G_MISS_NUM ,
62 ERROR_TEXT VARCHAR2(2000) := FND_API.G_MISS_CHAR,
63 ERROR_CODE VARCHAR2(1) := FND_API.G_MISS_CHAR,
64 NAME VARCHAR2(50) := FND_API.G_MISS_CHAR
65 );
66
67 TYPE mass_edit_sys_error_tbl IS TABLE OF mass_edit_sys_error_rec INDEX BY BINARY_INTEGER;
68
69 TYPE Mass_Upd_Rep_Error_Rec IS RECORD
70 (
71 Instance_id NUMBER := FND_API.G_MISS_NUM,
72 Entity_Name VARCHAR2(30) := FND_API.G_MISS_CHAR,
73 Error_Message VARCHAR2(2000) := FND_API.G_MISS_CHAR,
74 ENTRY_ID NUMBER := FND_API.G_MISS_NUM ,
75 TXN_LINE_DETAIL_ID NUMBER := FND_API.G_MISS_NUM ,
76 ERROR_CODE VARCHAR2(1) := FND_API.G_MISS_CHAR,
77 NAME VARCHAR2(50) := FND_API.G_MISS_CHAR
78 );
79
80 TYPE Mass_Upd_Rep_Error_Tbl IS TABLE OF Mass_Upd_Rep_Error_Rec INDEX BY BINARY_INTEGER;
81
82 --Added for Bug 10414679
83 TYPE log_rec IS RECORD
84 (
85 ITEM_NUMBER VARCHAR2(40) := FND_API.G_MISS_CHAR,
86 EXTERNAL_REFERENCE VARCHAR2(30) := FND_API.G_MISS_CHAR,
87 SERIAL_NUMBER VARCHAR2(30) := FND_API.G_MISS_CHAR,
88 LOT_NUMBER VARCHAR2(80) := FND_API.G_MISS_CHAR,
89 QUANTITY NUMBER := FND_API.G_MISS_NUM,
90 INTERNALLY_OWNED VARCHAR2(1) := FND_API.G_MISS_CHAR,
91 OWNER_PARTY_NUM VARCHAR2(30) := FND_API.G_MISS_CHAR,
92 OWNER_NAME VARCHAR2(360) := FND_API.G_MISS_CHAR,
93 SYSTEM_NAME VARCHAR2(50) := FND_API.G_MISS_CHAR
94 );
95
96 TYPE log_tbl IS TABLE OF log_rec INDEX BY BINARY_INTEGER;
97
98 PROCEDURE Initiate_Mass_Edit
99 (
100 errbuf OUT NOCOPY VARCHAR2
101 ,retcode OUT NOCOPY NUMBER
102 ,p_entry_id IN NUMBER
103 );
104
105 /* This is the Procedure for the Concurrent Program that processes the mass edit batch */
106 PROCEDURE Process_mass_edit_batch
107 ( errbuf OUT NOCOPY VARCHAR2
108 ,retcode OUT NOCOPY NUMBER
109 ,p_Entry_id IN NUMBER
110 );
111
112 /*
113 This API is used to Create a new Mass edit Batch
114 */
115
116 PROCEDURE CREATE_MASS_EDIT_BATCH
117 (
118 p_api_version IN NUMBER,
119 p_commit IN VARCHAR2 := fnd_api.g_false,
120 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
121 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
122 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
123 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec ,
124 px_mass_edit_inst_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_inst_tbl,
125 px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
126 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
127 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
128 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
129 x_mass_edit_error_tbl OUT NOCOPY mass_edit_error_tbl,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2
133
134 );
135
136 /*
137 This API is the Update Batch API to update an already existing Mass edit batch
138 It can add, remove, update item instances as well it's child entities in the batch
139 */
140
141 PROCEDURE UPDATE_MASS_EDIT_BATCH (
142 p_api_version IN NUMBER,
143 p_commit IN VARCHAR2 := fnd_api.g_false,
144 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
145 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
146 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
147 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec ,
148 px_mass_edit_inst_tbl IN OUT NOCOPY mass_edit_inst_tbl,
149 px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
150 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
151 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
152 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
153 x_mass_edit_error_tbl OUT NOCOPY mass_edit_error_tbl,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2);
157
158 /*
159 This API can be used to delete the mass udpate batch and all of its transaction details
160 created for that batch
161 */
162
163 PROCEDURE DELETE_MASS_EDIT_BATCH
164 (
165 p_api_version IN NUMBER,
166 p_commit IN VARCHAR2 := fnd_api.g_false,
167 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
168 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
169 p_mass_edit_rec IN mass_edit_rec,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2
173
174 );
175
176 /*
177 This is a wrapper API can be used to delete multiple mass udpate batches and in turn calls the
178 delete_mass_edit_batch API
179 */
180
181 PROCEDURE DELETE_MASS_EDIT_BATCHES
182 (
183 p_api_version IN NUMBER,
184 p_commit IN VARCHAR2 := fnd_api.g_false,
185 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
186 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
187 p_mass_edit_tbl IN mass_edit_tbl,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2
191 );
192
193 /*
194 This API gets all the transaction line details and also the child records for each of
195 these line details, for a given mass edit batch.
196 */
197
198 PROCEDURE GET_MASS_EDIT_DETAILS (
199 p_api_version IN NUMBER,
200 p_commit IN VARCHAR2 := fnd_api.g_false,
201 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
202 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
203 px_mass_edit_rec IN OUT NOCOPY mass_edit_rec,
204 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl ,
205 x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
206 x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
207 x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER ,
210 x_msg_data OUT NOCOPY VARCHAR2);
211
212 /*----------------------------------------------------*/
213 /* Procedure name: PROCESS_SYSTEM_MASS_UPDATE */
214 /* Description : procedure used to update System in */
215 /* mass update batch */
216 /*----------------------------------------------------*/
217
218 PROCEDURE PROCESS_SYSTEM_MASS_UPDATE
219 (
220 p_api_version IN NUMBER
221 ,p_commit IN VARCHAR2 := fnd_api.g_false
222 ,p_Entry_id IN NUMBER
223 ,p_instance_tbl IN OUT NOCOPY csi_datastructures_pub.instance_tbl
224 ,p_ext_attrib_values_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
225 ,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
226 ,p_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
227 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
228 ,x_return_status OUT NOCOPY VARCHAR2
229 ,x_msg_count OUT NOCOPY NUMBER
230 ,x_msg_data OUT NOCOPY VARCHAR2
231 );
232
233 /*----------------------------------------------------*/
234 /* Procedure name: IDENTIFY_SYSTEM_FOR_UPDATE */
235 /* Description : procedure used to identifies System for */
236 /* mass update batch */
237 /*----------------------------------------------------*/
238 PROCEDURE IDENTIFY_SYSTEM_FOR_UPDATE (
239 p_txn_line_id IN NUMBER
240 ,p_upd_system_tbl OUT NOCOPY csi_datastructures_pub.mu_systems_tbl
241 ,x_return_status OUT NOCOPY VARCHAR2);
242
243 /*----------------------------------------------------*/
244 /* Procedure name: VALIDATE_SYSTEM_BATCH */
245 /* Description : procedure to validate systems before*/
246 /* before mass update */
247 /*----------------------------------------------------*/
248 PROCEDURE VALIDATE_SYSTEM_BATCH (
249 p_entry_id IN NUMBER
250 ,p_txn_line_id IN NUMBER
251 ,p_upd_system_tbl IN csi_datastructures_pub.mu_systems_tbl
252 ,x_return_status OUT NOCOPY VARCHAR2);
253
254
255
256 PROCEDURE Process_webadi_batch
257 ( errbuf OUT NOCOPY VARCHAR2
258 ,retcode OUT NOCOPY NUMBER
259 ,p_Entry_id IN NUMBER
260 ,p_batch_type IN VARCHAR2
261 ,p_schedule_date DATE
262 );
263
264 PROCEDURE Process_webadi_create
265 ( errbuf OUT NOCOPY VARCHAR2
266 ,retcode OUT NOCOPY NUMBER
267 ,p_Entry_id IN NUMBER
268 ,p_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
269 );
270
271 PROCEDURE Process_webadi_update
272 ( errbuf OUT NOCOPY VARCHAR2
273 ,retcode OUT NOCOPY NUMBER
274 ,p_Entry_id IN NUMBER
275 ,p_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
276 );
277
278 PROCEDURE Create_Reupload_Batch
279 ( errbuf OUT NOCOPY VARCHAR2
280 ,retcode OUT NOCOPY NUMBER
281 ,p_Entry_id IN NUMBER
282 ,p_reupld_batch_id OUT NOCOPY NUMBER
283 );
284
285 PROCEDURE Populate_WebAdi_Ids
286 ( errbuf OUT NOCOPY VARCHAR2
287 ,retcode OUT NOCOPY NUMBER
288 ,p_Entry_id IN NUMBER
289 );
290
291 End CSI_MASS_EDIT_PUB;
292
293