1 PACKAGE BODY CSD_REPAIR_ACTUAL_PROCESS_PVT as
2 /* $Header: csdactpb.pls 120.14.12020000.3 2013/03/15 00:54:55 takwong ship $ csdactpb.pls */
3
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdactpb.pls';
5
6 -- The following constants represent inventory transaction types.
7 -- Of the many INV txn types only the following ones are used in the package.
8 G_MTL_TXN_TYPE_COMP_ISSUE CONSTANT NUMBER := 35;
9 G_MTL_TXN_TYPE_COMP_RETURN CONSTANT NUMBER := 43;
10
11 -- The following constant represent inventory transaction source type.
12 -- Of the many types only the following one is used in the package.
13 G_MTL_TXN_SOURCE_TYPE_WIP CONSTANT NUMBER := 5;
14
15 /*--------------------------------------------------------------------*/
16 /* procedure name: Log_WIP_Resource_Txn_warnings */
17 /* description : Procedure is used to log resource transaction */
18 /* discrepencies. */
19 /* The procedures log warnings for the following - */
20 /* 1. Billing item not defined for Resource. */
21 /* 2. Item not defined in the Service Validation Org. */
22 /* 3. Billing Type not defined for the item. */
23 /* 4. Txn Billing Type could not be derived based on */
24 /* the Repair Type setup. */
25 /* */
26 /* Called from : Import_Actuals_From_Wip */
27 /* */
28 /* x_warning_flag : This flag communicates to the calling procedure */
29 /* whether there are any messages logged that can be */
30 /* displayed to the user. If the value is G_TRUE */
31 /* then it indicates that one or more message have */
32 /* been logged. */
33 /* */
34 /* Notes : This procedure is not defined in the SPEC of the package */
35 /* and is a private procedure. */
36 /*--------------------------------------------------------------------*/
37
38 PROCEDURE Log_WIP_Resource_Txn_warnings( p_wip_entity_id IN NUMBER,
39 p_depot_organization IN NUMBER,
40 p_wip_organization IN NUMBER,
41 p_repair_type_id IN NUMBER,
42 x_warning_flag OUT NOCOPY VARCHAR2) IS
43
44 -- CONSTANTS --
45 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
46 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
47 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
48 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
49 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
50 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
51 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
52 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repair_actual_process_pvt.log_wip_resource_txn_warnings';
53 lc_api_name CONSTANT VARCHAR2(30) := 'LOG_WIP_RESOURCE_TXN_WARNINGS';
54
55 -- Fetches records that have the following warning -
56 -- 1. Billing item not defined for Resource.
57 CURSOR C_Resource_Item_Not_Defined IS
58 SELECT DISTINCT RES.resource_code
59 FROM WIP_TRANSACTIONS WTXN, BOM_RESOURCES RES
60 WHERE WTXN.wip_entity_id = p_wip_entity_id
61 AND RES.resource_id = WTXN.resource_id
62 AND RES.billable_item_id IS NULL;
63
64 -- Fetches records that have the following warnings -
65 -- 2. Item not defined in the Service Validation Org.
66 -- 3. Billing Type not defined for the item.
67 CURSOR C_Resource_Item_In_Depot_Org IS
68 SELECT RES.billable_item_id INVENTORY_ITEM_ID,
69 SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
70 MSIW.concatenated_segments WIP_ITEM_NAME,
71 MSID.inventory_item_id DEPOT_ITEM_ID,
72 MSID.material_billable_flag BILLING_TYPE
73 FROM WIP_TRANSACTIONS WTXN,
74 BOM_RESOURCES RES,
75 MTL_SYSTEM_ITEMS_KFV MSIW,-- For WIP organization
76 -- MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
77 MTL_SYSTEM_ITEMS_B MSID -- For Depot/Service organization
78 WHERE WTXN.wip_entity_id = p_wip_entity_id
79 AND RES.resource_id = WTXN.resource_id
80 AND MSIW.inventory_item_id = RES.billable_item_id
81 AND MSIW.organization_id = p_wip_organization
82 AND MSID.inventory_item_id(+) = RES.billable_item_id
83 AND MSID.organization_id(+) = p_depot_organization
84 AND MSID.material_billable_flag IS NULL -- Billing type not defined
85 GROUP BY RES.billable_item_id, MSIW.concatenated_segments,
86 MSID.inventory_item_id, MSID.material_billable_flag;
87
88 -- Fetches records that have the following warnings -
89 -- 4. Txn Billing Type could not be derived based on the Repair Type setup.
90 CURSOR C_Resource_Txn_Billing_Type IS
91 SELECT RES.billable_item_id INVENTORY_ITEM_ID,
92 SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
93 MSID.concatenated_segments DEPOT_ITEM_NAME
94 FROM WIP_TRANSACTIONS WTXN,
95 BOM_RESOURCES RES,
96 MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
97 WHERE WTXN.wip_entity_id = p_wip_entity_id
98 AND RES.resource_id = WTXN.resource_id
99 AND MSID.inventory_item_id = RES.billable_item_id
100 AND MSID.organization_id = p_depot_organization
101 AND MSID.material_billable_flag IS NOT NULL
102 AND NOT EXISTS
103 (SELECT 'x'
104 FROM CS_TXN_BILLING_TYPES TBT,
105 CSD_REPAIR_TYPES_SAR SAR
106 WHERE TBT.billing_type = MSID.material_billable_flag
107 AND SAR.txn_billing_type_id = TBT.txn_billing_type_id
108 AND SAR.repair_type_id = p_repair_type_id
109 AND TRUNC(NVL(TBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
110 AND TRUNC(NVL(TBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
111 )
112 GROUP BY RES.billable_item_id, MSID.concatenated_segments;
113
114 BEGIN
115
116 -- logging
117 if (lc_proc_level >= lc_debug_level) then
118 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
119 'Entering CSD_REPAIR_ACTUAL_PROCESS_PVT.Log_WIP_Resource_Txn_warnings');
120 end if;
121
122 -- log parameters
123 if (lc_stat_level >= lc_debug_level) then
124 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
125 'p_wip_entity_id: ' || p_wip_entity_id);
126 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
127 'p_depot_organization: ' || p_depot_organization);
128 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
129 'p_wip_organization: ' || p_wip_organization);
130 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
131 'p_repair_type_id: ' || p_repair_type_id);
132 end if;
133
134 -- Set the warning flag
135 x_warning_flag := FND_API.G_FALSE;
136
137 if (lc_stat_level >= lc_debug_level) then
138 FND_LOG.STRING(lc_stat_level, lc_mod_name,
139 'Before the FOR LOOP for C_Resource_Item_Not_Defined.');
140 end if;
141
142 -- Simply gets all records and log warnings for each of them.
143 FOR i_rec IN C_Resource_Item_Not_Defined LOOP
144 x_warning_flag := FND_API.G_TRUE;
145 if (lc_stat_level >= lc_debug_level) then
146 FND_LOG.STRING(lc_stat_level, lc_mod_name,
147 'The billing item is not defined for the Resource = ' || i_rec.RESOURCE_CODE);
148 end if;
149 FND_MESSAGE.set_name( 'CSD', 'CSD_ACT_RESOURCE_NO_ITEM');
150 -- 'The billing item is not defined for the Resource $RESOURCE_CODE'.
151 FND_MESSAGE.set_token( 'RESOURCE_CODE', i_rec.RESOURCE_CODE );
152 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
153 END LOOP;
154
155 if (lc_stat_level >= lc_debug_level) then
156 FND_LOG.STRING(lc_stat_level, lc_mod_name,
157 'After the FOR LOOP for C_Resource_Item_Not_Defined.');
158 end if;
159
160 if (lc_stat_level >= lc_debug_level) then
161 FND_LOG.STRING(lc_stat_level, lc_mod_name,
162 'Before the FOR LOOP for C_Resource_Item_In_Depot_Org.');
163 end if;
164
165 -- Simply gets all records and log warnings for each of them if -
166 -- EITHER 'Depot Item Name' is missing (meaning - not defined in
167 -- Service Validation/Depot Org)
168 -- OR 'billing type' not defined for the item.
169 -- Either of the conditions are considered only if
170 -- the txn qty is a positive value.
171 FOR i_rec IN C_Resource_Item_In_Depot_Org LOOP
172
173 IF i_rec.Quantity > 0 THEN
174 IF ( i_rec.DEPOT_ITEM_ID IS NULL ) THEN
175 x_warning_flag := FND_API.G_TRUE;
176 if (lc_stat_level >= lc_debug_level) then
177 FND_LOG.STRING(lc_stat_level, lc_mod_name,
178 'The item ' || i_rec.WIP_ITEM_NAME || ' is not defined in the Service '
179 || 'Validation Organization. It is defined only in the WIP organization.');
180 end if;
181 FND_MESSAGE.set_name( 'CSD', 'CSD_ACT_ITEM_NOT_SRV_ORG');
182 -- 'The item $ITEM_NAME is not defined in the Service Validation Organization. It is defined only in the WIP organization.
183 FND_MESSAGE.set_token( 'ITEM_NAME', i_rec.WIP_ITEM_NAME );
184 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
185 ELSIF ( i_rec.BILLING_TYPE IS NULL ) THEN
186 x_warning_flag := FND_API.G_TRUE;
187 if (lc_stat_level >= lc_debug_level) then
188 FND_LOG.STRING(lc_stat_level, lc_mod_name,
189 'Billing type is not defined for the item ' || i_rec.WIP_ITEM_NAME);
190 end if;
191 FND_MESSAGE.set_name( 'CSD', 'CSD_ACT_ITEM_NO_BILLING_TYPE');
192 -- '''Billing type'' is not defined for the item $ITEM_NAME.
193 FND_MESSAGE.set_token( 'ITEM_NAME', i_rec.WIP_ITEM_NAME );
194 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
195 END IF;
196 END IF;
197
198 END LOOP;
199
200 if (lc_stat_level >= lc_debug_level) then
201 FND_LOG.STRING(lc_stat_level, lc_mod_name,
202 'After the FOR LOOP for C_Resource_Item_In_Depot_Org.');
203 end if;
204
205 if (lc_stat_level >= lc_debug_level) then
206 FND_LOG.STRING(lc_stat_level, lc_mod_name,
207 'Before the FOR LOOP for C_Resource_Txn_Billing_Type.');
208 end if;
209
210 -- Simply gets all records and log warnings for each of them.
211 FOR i_rec IN C_Resource_Txn_Billing_Type LOOP
212 IF i_rec.Quantity > 0 THEN
213 x_warning_flag := FND_API.G_TRUE;
214 if (lc_stat_level >= lc_debug_level) then
215 FND_LOG.STRING(lc_stat_level, lc_mod_name,
216 'Unable to determine service activity billing type for the item ' || i_rec.DEPOT_ITEM_NAME);
217 end if;
218 FND_MESSAGE.set_name( 'CSD', 'CSD_CHRG_NO_ITEM_SAR');
219 -- Unable to determine service activity billing type for the item $ITEM.
220 FND_MESSAGE.set_token( 'ITEM', i_rec.DEPOT_ITEM_NAME);
221 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
222 END IF;
223 END LOOP;
224
225 if (lc_stat_level >= lc_debug_level) then
226 FND_LOG.STRING(lc_stat_level, lc_mod_name,
227 'After the FOR LOOP for C_Resource_Txn_Billing_Type.');
228 end if;
229
230 -- Note : This procedure only adds to the FND msg stack. The msgs will
231 -- be logged to the generic message utility by the calling program.
232
233 -- logging
234 if (lc_proc_level >= lc_debug_level) then
235 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
236 'Leaving CSD_REPAIR_ACTUAL_PROCESS_PVT.Log_WIP_Resource_Txn_warnings');
237 end if;
238
239 END Log_WIP_Resource_Txn_warnings;
240
241 /*--------------------------------------------------------------------*/
242 /* procedure name: Log_WIP_MTL_Txn_warnings */
243 /* description : Procedure is used to log material transaction */
244 /* discrepencies. */
245 /* The procedures log warnings for the following - */
246 /* 1. Item not defined in the Service Validation Org. */
247 /* 2. Billing Type not defined for the item. */
248 /* 3. Txn Billing Type could not be derived based on */
249 /* the Repair Type setup. */
250 /* */
251 /* Called from : Import_Actuals_From_Wip */
252 /* */
253 /* x_warning_flag : This flag communicates to the calling procedure */
254 /* whether there are any messages logged that can be */
255 /* displayed to the user. If the value is G_TRUE */
256 /* then it indicates that one or more message have */
257 /* been logged. */
258 /* */
259 /* Notes : This procedure is not defined in the SPEC of the package */
260 /* and is a private procedure. */
261 /*--------------------------------------------------------------------*/
262
263 PROCEDURE Log_WIP_MTL_Txn_warnings( p_wip_entity_id IN NUMBER,
264 p_depot_organization IN NUMBER,
265 p_wip_organization IN NUMBER,
266 p_inventory_item_id IN NUMBER,
267 p_repair_type_id IN NUMBER,
268 x_warning_flag OUT NOCOPY VARCHAR2
269 ) IS
270
271 -- CONSTANTS --
272 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
273 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
274 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
275 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
276 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
277 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
278 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
279 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repair_actual_process_pvt.log_wip_mtl_txn_warnings';
280 lc_api_name CONSTANT VARCHAR2(30) := 'LOG_WIP_MTL_TXN_WARNINGS';
281
282 -- Fetches records that have the following warnings -
283 -- 1. Item not defined in the Service Validation Org.
284 -- 2. Billing Type not defined for the item.
285 CURSOR C_MTL_Item_In_Depot_Org IS
286 SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
287 MSIW.concatenated_segments WIP_ITEM_NAME,
288 SUM( DECODE( MMT.transaction_type_id
289 , G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
290 , G_MTL_TXN_TYPE_COMP_RETURN,( -1 * ABS( mmt.primary_quantity )))) Quantity,
291 MSID.inventory_item_id DEPOT_ITEM_ID,
292 MSID.material_billable_flag BILLING_TYPE
293 FROM MTL_MATERIAL_TRANSACTIONS MMT,
294 MTL_SYSTEM_ITEMS_KFV MSIW, -- For WIP organization
295 MTL_SYSTEM_ITEMS_B MSID -- For Depot/Service organization
296 -- MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
297 WHERE MMT.transaction_source_id = p_wip_entity_id
298 AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
299 AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
300 G_MTL_TXN_TYPE_COMP_RETURN )
301 AND MMT.inventory_item_id <> p_inventory_item_id
302 AND MSIW.inventory_item_id = MMT.inventory_item_id
303 AND MSIW.organization_id = p_wip_organization
304 AND MSID.inventory_item_id(+) = MMT.inventory_item_id
305 AND MSID.organization_id(+) = p_depot_organization
306 AND MSID.material_billable_flag IS NULL -- Billing type not defined
307 GROUP BY mmt.inventory_item_id,
308 MSIW.concatenated_segments,
309 MSID.inventory_item_id,
310 MSID.material_billable_flag;
311
312 -- Fetches records that have the following warnings -
313 -- 3. Txn Billing Type could not be derived based on the Repair Type setup.
314 CURSOR C_MTL_Txn_Billing_Type IS
315 SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
316 MSID.concatenated_segments DEPOT_ITEM_NAME,
317 SUM( DECODE( MMT.transaction_type_id
318 , G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
319 , G_MTL_TXN_TYPE_COMP_RETURN,( -1 * ABS( mmt.primary_quantity )))) Quantity
320 FROM MTL_MATERIAL_TRANSACTIONS MMT,
321 MTL_SYSTEM_ITEMS_KFV MSID -- For Depot/Service organization
322 WHERE MMT.transaction_source_id = p_wip_entity_id
323 AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
324 AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
325 G_MTL_TXN_TYPE_COMP_RETURN )
326 AND MMT.inventory_item_id <> p_inventory_item_id
327 AND MSID.inventory_item_id = MMT.inventory_item_id
328 AND MSID.organization_id = p_depot_organization
329 AND MSID.material_billable_flag IS NOT NULL
330 AND NOT EXISTS
331 (SELECT 'x'
332 FROM CS_TXN_BILLING_TYPES TBT,
333 CSD_REPAIR_TYPES_SAR SAR
334 WHERE TBT.billing_type = MSID.material_billable_flag
335 AND SAR.txn_billing_type_id = TBT.txn_billing_type_id
336 AND SAR.repair_type_id = p_repair_type_id
337 AND TRUNC(NVL(TBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
338 AND TRUNC(NVL(TBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
339 )
340 GROUP BY mmt.inventory_item_id,
341 MSID.concatenated_segments;
342
343 BEGIN
344
345 -- logging
346 if (lc_proc_level >= lc_debug_level) then
347 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
348 'Entering CSD_REPAIR_ACTUAL_PROCESS_PVT.Log_WIP_MTL_Txn_warnings');
349 end if;
350
351 -- log parameters
352 if (lc_stat_level >= lc_debug_level) then
353 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
354 'p_wip_entity_id: ' || p_wip_entity_id);
355 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
356 'p_depot_organization: ' || p_depot_organization);
357 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
358 'p_wip_organization: ' || p_wip_organization);
359 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
360 'p_inventory_item_id: ' || p_inventory_item_id);
361 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
362 'p_repair_type_id: ' || p_repair_type_id);
363 end if;
364
365 -- Set the warning flag
366 x_warning_flag := FND_API.G_FALSE;
367
368 if (lc_stat_level >= lc_debug_level) then
369 FND_LOG.STRING(lc_stat_level, lc_mod_name,
370 'Before the FOR LOOP for C_MTL_Item_In_Depot_Org.');
371 end if;
372
373 -- Simply gets all records and log warnings for each of them if -
374 -- EITHER 'Depot Item Name' is missing (meaning - not defined in
375 -- Service Validation/Depot Org)
376 -- OR 'billing type' not defined for the item.
377 -- Either of the conditions are considered only if
378 -- the txn qty is a positive value.
379 FOR i_rec IN C_MTL_Item_In_Depot_Org LOOP
380 IF i_rec.Quantity > 0 THEN
381 IF (i_rec.DEPOT_ITEM_ID IS NULL) THEN
382 if (lc_stat_level >= lc_debug_level) then
383 FND_LOG.STRING(lc_stat_level, lc_mod_name,
384 'The item ' || i_rec.WIP_ITEM_NAME || ' is not defined in the Service '
385 || 'Validation Organization. It is defined only in the WIP organization.');
386 end if;
387 x_warning_flag := FND_API.G_TRUE;
388 FND_MESSAGE.set_name('CSD','CSD_ACT_ITEM_NOT_SRV_ORG');
389 -- The item $ITEM_NAME is not defined in the Service Validation Organization. It is defined only in the WIP organization.
390 FND_MESSAGE.set_token('ITEM_NAME', i_rec.WIP_ITEM_NAME);
391 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
392 ELSIF (i_rec.BILLING_TYPE IS NULL) THEN
393 if (lc_stat_level >= lc_debug_level) then
394 FND_LOG.STRING(lc_stat_level, lc_mod_name,
395 'Billing type is not defined for the item ' || i_rec.WIP_ITEM_NAME);
396 end if;
397 x_warning_flag := FND_API.G_TRUE;
398 FND_MESSAGE.set_name('CSD','CSD_ACT_ITEM_NO_BILLING_TYPE');
399 -- '''Billing type'' is not defined for the item $ITEM_NAME.
400 FND_MESSAGE.set_token('ITEM_NAME', i_rec.WIP_ITEM_NAME);
401 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
402 END IF;
403 END IF;
404 END LOOP;
405
406 if (lc_stat_level >= lc_debug_level) then
407 FND_LOG.STRING(lc_stat_level, lc_mod_name,
408 'After the FOR LOOP for C_MTL_Item_In_Depot_Org.');
409 end if;
410
411 if (lc_stat_level >= lc_debug_level) then
412 FND_LOG.STRING(lc_stat_level, lc_mod_name,
413 'Before the FOR LOOP for C_MTL_Txn_Billing_Type.');
414 end if;
415
416 -- Simply gets all records and log warnings for each of them.
417 FOR i_rec IN C_MTL_Txn_Billing_Type LOOP
418 IF i_rec.Quantity > 0 THEN
419 x_warning_flag := FND_API.G_TRUE;
420 if (lc_stat_level >= lc_debug_level) then
421 FND_LOG.STRING(lc_stat_level, lc_mod_name,
422 'Unable to determine service activity billing type for the item ' || i_rec.DEPOT_ITEM_NAME);
423 end if;
424 FND_MESSAGE.set_name( 'CSD', 'CSD_CHRG_NO_ITEM_SAR');
425 -- Unable to determine service activity billing type for the item $ITEM.
426 FND_MESSAGE.set_token( 'ITEM', i_rec.DEPOT_ITEM_NAME);
427 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
428 END IF;
429 END LOOP;
430
431 if (lc_stat_level >= lc_debug_level) then
432 FND_LOG.STRING(lc_stat_level, lc_mod_name,
433 'After the FOR LOOP for C_MTL_Txn_Billing_Type.');
434 end if;
435
436 -- Note : This procedure only adds to the FND msg stack. The msgs will
437 -- be logged to the generic message utility by the calling program.
438
439 -- logging
440 if (lc_proc_level >= lc_debug_level) then
441 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
442 'Leaving CSD_REPAIR_ACTUAL_PROCESS_PVT.Log_WIP_MTL_Txn_warnings');
443 end if;
444
445 END Log_WIP_MTL_Txn_warnings;
446
447 /*--------------------------------------------------------------------*/
448 /* procedure name: Import_Actuals_From_Task */
449 /* description : Procedure is used to import Task debrief lines into */
450 /* repair actual lines. We only create links to */
451 /* existing charge lines for the debrief lines. The */
452 /* links are represented in repair actual lines table. */
453 /* No new charge lines are created. */
454 /* */
455 /* Called from : Depot Repair Actuals UI */
456 /* */
457 /* x_warning_flag : This flag communicates to the calling procedure */
458 /* whether there are any messages logged that can be */
459 /* displayed to the user. If the value is G_TRUE */
460 /* then it indicates that one or more message have */
461 /* been logged. */
462 /* */
463 /* Note : This procedure assumes that the Actual header is created */
464 /* prior to calling this procedure. */
465 /*--------------------------------------------------------------------*/
466
467 PROCEDURE Import_Actuals_From_Task
468 (
469 p_api_version IN NUMBER,
470 p_commit IN VARCHAR2,
471 p_init_msg_list IN VARCHAR2,
472 p_validation_level IN NUMBER,
473 x_return_status OUT NOCOPY VARCHAR2,
474 x_msg_count OUT NOCOPY NUMBER,
475 x_msg_data OUT NOCOPY VARCHAR2,
476 p_repair_line_id IN NUMBER,
477 p_repair_actual_id IN NUMBER,
478 x_warning_flag OUT NOCOPY VARCHAR2
479 )
480 IS
481
482 -- CONSTANTS --
483 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
484 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
485 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
486 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
487 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
488 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
489 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
490 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repair_actual_process_pvt.import_actuals_from_task';
491 lc_api_name CONSTANT VARCHAR2(30) := 'IMPORT_ACTUALS_FROM_TASK';
492 lc_api_version CONSTANT NUMBER := 1.0;
493
494 -- VARIABLES
495 l_msg_count NUMBER;
496 l_msg_data VARCHAR2(100);
497 l_msg_index NUMBER;
498
499 -- Although both the constants have the same value, they have
500 -- been defined deliberatly as different. We use diff constants as
501 -- they really represent different entities and hence any future
502 -- maintenenace will be easier.
503 G_ACTUAL_MSG_ENTITY_TASK CONSTANT VARCHAR2(15) := 'TASK';
504 G_ACTUAL_SOURCE_CODE_TASK CONSTANT VARCHAR2(15) := 'TASK';
505
506 -- We do not populate the following record.
507 -- It is really a dummy for this procedure as we do create
508 -- new Charge line(s) when importing lines. We just create 'links'.
509 l_charge_line_rec CS_CHARGE_DETAILS_PUB.CHARGES_REC_TYPE;
510
511 -- The following variable will be used to store
512 -- actual line info for each record in the loop.
513 l_curr_actual_line_rec CSD_REPAIR_ACTUAL_LINES_PVT.CSD_ACTUAL_LINES_REC_TYPE;
514
515 -- The folowing variable will be used to skip processing for
516 -- a current row in the loop, if we encounter an error.
517 l_skip_curr_row BOOLEAN := FALSE;
518
519 -- Stores the count currencies that are different
520 -- than the RO currency.
521 l_multi_currency_count NUMBER := 0;
522
523 -- The following variables will keep count
524 -- of the tota/failedl estimate lines.
525 l_import_line_total_count NUMBER := 0;
526 l_import_line_failed_count NUMBER := 0;
527
528 -- swai: bug 7122368
529 l_bill_to_account_id NUMBER;
530 l_bill_to_party_id NUMBER;
531 l_bill_to_party_site_id NUMBER;
532 l_ship_to_account_id NUMBER;
533 l_ship_to_party_id NUMBER;
534 l_ship_to_party_site_id NUMBER;
535
536 --gilam: put in nvl for actual header id
537 -- Fetches only the charge lines that have not
538 -- been imported earlier for all eligible tasks.
539 /* swai: bug 6042488 / FP bug 5949309 - replace CSF_DEBRIEF_HEADERS_V
540 with CSF_DEBRIEF_HEADERS, JTF_TASKS_B, and JTF_TASK_ASSIGNMENTS */
541 CURSOR c_valid_task_charge_lines IS
542 SELECT CEST.estimate_detail_id,
543 JTB.task_id actual_source_id -- swai: bug 6042488
544 FROM CS_ESTIMATE_DETAILS CEST,
545 CSF_DEBRIEF_LINES CDBL,
546 CSF_DEBRIEF_HEADERS CDBH, -- swai: bug 6042488
547 JTF_TASKS_B JTB, -- swai: bug 6042488
548 JTF_TASK_ASSIGNMENTS JTA -- swai: bug 6042488
549 WHERE CEST.original_source_code = 'DR'
550 AND CEST.original_source_id = p_repair_line_id
551 AND CEST.source_code = 'SD'
552 AND CDBL.debrief_line_id = CEST.source_id
553 AND CDBH.debrief_header_id = CDBL.debrief_header_id
554 /* swai: added for bug fix 5949309 */
555 AND JTB.source_object_id = CEST.original_source_id
556 AND JTB.source_object_type_code = 'DR'
557 AND nvl (JTB.deleted_flag, 'N') <> 'Y'
558 AND CDBH.task_assignment_id = jta.task_assignment_id
559 AND JTA.task_id = jtb.task_id
560 AND JTA.assignee_role = 'ASSIGNEE'
561 /* end swai fix 5949309 */
562 AND NOT EXISTS
563 (
564 SELECT 'EXISTS'
565 FROM CSD_REPAIR_ACTUAL_LINES ACTL
566 WHERE ACTL.repair_actual_id = nvl(p_repair_actual_id,ACTL.repair_actual_id)
567 AND ACTL.estimate_detail_id = CEST.estimate_detail_id
568 AND ACTL.actual_source_code = G_ACTUAL_SOURCE_CODE_TASK
569 AND ACTL.actual_source_id = JTB.task_id -- swai: bug 6042488
570 );
571
572 -- The following cursor will check if there are any charge
573 -- lines created via task debrief that have currency different
574 -- from the RO currency.
575 CURSOR c_multi_currency_check IS
576 SELECT count(distinct CEST.currency_code)
577 FROM CS_ESTIMATE_DETAILS CEST,
578 CSD_REPAIRS RO
579 WHERE RO.repair_line_id = p_repair_line_id
580 AND CEST.original_source_code = 'DR'
581 AND CEST.original_source_id = RO.repair_line_id
582 AND CEST.source_code = 'SD'
583 AND RO.currency_code <> CEST.currency_code ;
584
585 --yvchen: bug#13439235
586 l_order_line_id NUMBER;
587
588 CURSOR cur_order_line_id(p_repair_actual_line_id IN NUMBER) IS
589 select order_line_id from
590 csd_repair_actual_lines_v
591 where repair_actual_line_id = p_repair_actual_line_id;
592 --yvchen: bug#13439235
593
594 BEGIN
595
596 -- Standard start of API Savepoint
597 Savepoint Import_Actuals_Task_sp;
598
599 -- Standard call to check for call compatibility.
600 IF NOT FND_API.Compatible_API_Call (lc_api_version,
601 p_api_version,
602 lc_api_name ,
603 G_PKG_NAME )
604 THEN
605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
606 END IF;
607
608 -- Initialize message list if p_init_msg_list is set to TRUE.
609 IF FND_API.to_Boolean( p_init_msg_list ) THEN
610 FND_MSG_PUB.initialize;
611 END IF;
612
613 -- logging
614 if (lc_proc_level >= lc_debug_level) then
615 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
616 'Entering CSD_REPAIR_ACTUAL_PROCESS_PVT.import_actuals_from_task');
617 end if;
618
619 -- log parameters
620 if (lc_stat_level >= lc_debug_level) then
621 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
622 'p_api_version: ' || p_api_version);
623 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
624 'p_commit: ' || p_commit);
625 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
626 'p_init_msg_list: ' || p_init_msg_list);
627 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
628 'p_validation_level: ' || p_validation_level);
629 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
630 'p_repair_line_id: ' || p_repair_line_id);
631 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
632 'p_repair_actual_id: ' || p_repair_actual_id);
633 end if;
634
635 -- Initialize API return status to success
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638 --
639 -- Begin API Body
640 --
641
642 -- Initialzing the warning flag.
643 x_warning_flag := FND_API.G_FALSE;
644
645 -- Validate mandatory input parameters.
646 if (lc_proc_level >= lc_debug_level) then
647 FND_LOG.STRING(lc_proc_level, lc_mod_name,
648 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_repair_line_id');
649 end if;
650
651 CSD_PROCESS_UTIL.Check_Reqd_Param
652 ( p_param_value => p_repair_line_id,
653 p_param_name => 'REPAIR_LINE_ID',
654 p_api_name => lc_api_name);
655
656 if (lc_stat_level >= lc_debug_level) then
657 FND_LOG.STRING(lc_stat_level, lc_mod_name,
658 'Done checking required params');
659 end if;
660
661 if (lc_stat_level >= lc_debug_level) then
662 FND_LOG.STRING(lc_stat_level, lc_mod_name,
663 'Opening Cursor c_multi_currency_check');
664 end if;
665
666 -- We need to make sure that no charge lines exist
667 -- for in a different currency than the RO currency.
668 OPEN c_multi_currency_check;
669 FETCH c_multi_currency_check
670 INTO l_multi_currency_count;
671 CLOSE c_multi_currency_check;
672
673 if (lc_stat_level >= lc_debug_level) then
674 FND_LOG.STRING(lc_stat_level, lc_mod_name,
675 'Cursor c_multi_currency_check closed. Count is = ' || l_multi_currency_count);
676 end if;
677
678 -- Expect the value to be zero. If the value is more
679 -- than 0 then it means that a charge line with diff
680 -- currency exixts.
681 If (l_multi_currency_count > 0) THEN
682 if (lc_stat_level >= lc_debug_level) then
683 FND_LOG.STRING(lc_stat_level, lc_mod_name,
684 'Task debrief lines have more than one distinct currencies.');
685 end if;
686 FND_MESSAGE.SET_NAME('CSD','CSD_ACT_MULTI_CURR_TASK');
687 -- Task debrief lines cannot be imported into actuals for
688 -- the repair order. All charge lines, that were created
689 -- via task debrief, must have the same currency as the
690 -- repair order currency. It was found that, one or more
691 -- charge line(s) are in a currency different than the
692 -- repair order currency.
693 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
694 RAISE FND_API.G_EXC_ERROR;
695 END IF;
696
697 -- Before we start the process of copying the
698 -- lines, we purge any existing error messages for the
699 -- Module ACT (source entity ESTIMATE).
700 if (lc_proc_level >= lc_debug_level) then
701 FND_LOG.STRING(lc_proc_level, lc_mod_name,
702 'Calling CSD_GEN_ERRMSGS_PVT.purge_entity_msgs');
703 end if;
704 CSD_GEN_ERRMSGS_PVT.purge_entity_msgs(
705 p_api_version => 1.0,
706 -- p_commit => FND_API.G_TRUE,
707 -- p_init_msg_list => FND_API.G_FALSE,
708 -- p_validation_level => FND_API.G_VALID_LEVEL_FULL,
709 p_module_code => G_MSG_MODULE_CODE_ACT,
710 p_source_entity_id1 => p_repair_line_id,
711 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_TASK,
712 p_source_entity_id2 => NULL, -- Since we want to delete all messages.
713 x_return_status => x_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data
716 );
717
718 if (lc_proc_level >= lc_debug_level) then
719 FND_LOG.STRING(lc_proc_level, lc_mod_name,
720 'Returned from CSD_GEN_ERRMSGS_PVT.purge_entity_msgs');
721 end if;
722
723 -- Stall the process if we were unable to purge
724 -- the older messages.
725 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728
729 -- swai: bug 7122368
730 Get_Default_Third_Party_Info (p_repair_line_id => p_repair_line_id,
731 x_bill_to_account_id => l_bill_to_account_id,
732 x_bill_to_party_id => l_bill_to_party_id,
733 x_bill_to_party_site_id => l_bill_to_party_site_id,
734 x_ship_to_account_id => l_ship_to_account_id,
735 x_ship_to_party_id => l_ship_to_party_id,
736 x_ship_to_party_site_id => l_ship_to_party_site_id);
737
738
739 -- For all the charge lines in cs_estimate_details table
740 -- for the given Repair Order
741 -- LOOP
742 if (lc_stat_level >= lc_debug_level) then
743 FND_LOG.STRING(lc_stat_level, lc_mod_name,
744 'Begin loop through c_valid_task_charge_lines');
745 end if;
746 FOR task_charge_line_rec IN c_valid_task_charge_lines
747 LOOP
748
749 -- savepoint for the current record.
750 Savepoint current_actual_line_sp;
751
752 -- Make the estimate detail id NULL, for each iteration.
753 -- l_estimate_detail_id := NULL;
754 l_skip_curr_row := FALSE;
755
756 -- Increment the total count.
757 l_import_line_total_count := l_import_line_total_count + 1;
758
759 if (lc_stat_level >= lc_debug_level) then
760 FND_LOG.STRING(lc_stat_level, lc_mod_name,
761 'l_skip_curr_row = false');
762 FND_LOG.STRING(lc_stat_level, lc_mod_name,
763 'l_curr_actual_line_rec.ESTIMATE_DETAIL_ID = ' || task_charge_line_rec.estimate_detail_id);
764 FND_LOG.STRING(lc_stat_level, lc_mod_name,
765 'l_curr_actual_line_rec.REPAIR_ACTUAL_ID = ' || p_repair_actual_id);
766 FND_LOG.STRING(lc_stat_level, lc_mod_name,
767 'l_curr_actual_line_rec.REPAIR_LINE_ID = ' || p_repair_line_id);
768 FND_LOG.STRING(lc_stat_level, lc_mod_name,
769 'l_curr_actual_line_rec.ACTUAL_SOURCE_CODE = ' || G_ACTUAL_SOURCE_CODE_TASK);
770 FND_LOG.STRING(lc_stat_level, lc_mod_name,
771 'l_curr_actual_line_rec.ACTUAL_SOURCE_ID = ' || task_charge_line_rec.actual_source_id);
772 end if;
773
774 -- If the copying of charge line was successful then
775 -- we initialize the actual line record with relevant values.
776 l_curr_actual_line_rec.ESTIMATE_DETAIL_ID := task_charge_line_rec.estimate_detail_id;
777 l_curr_actual_line_rec.REPAIR_ACTUAL_ID := p_repair_actual_id;
778 l_curr_actual_line_rec.REPAIR_LINE_ID := p_repair_line_id;
779 l_curr_actual_line_rec.ACTUAL_SOURCE_CODE := G_ACTUAL_SOURCE_CODE_TASK;
780 l_curr_actual_line_rec.ACTUAL_SOURCE_ID := task_charge_line_rec.actual_source_id;
781 l_curr_actual_line_rec.REPAIR_ACTUAL_LINE_ID := NULL;
782
783 /*
784 l_curr_actual_line_rec.OBJECT_VERSION_NUMBER
785 l_curr_actual_line_rec.CREATED_BY
786 l_curr_actual_line_rec.CREATION_DATE
787 l_curr_actual_line_rec.LAST_UPDATED_BY
788 l_curr_actual_line_rec.LAST_UPDATE_DATE
789 l_curr_actual_line_rec.LAST_UPDATE_LOGIN
790 */
791
792
793
794 /*
795 -- In 11.5.10 we don't do Actual costing
796 l_curr_actual_line_rec.ITEM_COST := task_charge_line_rec.item_cost;
797 -- We do not have any notes at the time of
798 -- creating repair actual lines.
799 l_curr_actual_line_rec.JUSTIFICATION_NOTES := task_charge_line_rec.justification_notes;
800 l_curr_actual_line_rec.RESOURCE_ID
801 l_curr_actual_line_rec.OVERRIDE_CHARGE_FLAG:= task_charge_line_rec.override_charge_flag;
802 l_curr_actual_line_rec.ATTRIBUTE_CATEGORY := task_charge_line_rec.context;
803 l_curr_actual_line_rec.ATTRIBUTE1 := task_charge_line_rec.attribute1;
804 l_curr_actual_line_rec.ATTRIBUTE2 := task_charge_line_rec.attribute2;
805 l_curr_actual_line_rec.ATTRIBUTE3 := task_charge_line_rec.attribute3;
806 l_curr_actual_line_rec.ATTRIBUTE4 := task_charge_line_rec.attribute4;
807 l_curr_actual_line_rec.ATTRIBUTE5 := task_charge_line_rec.attribute5;
808 l_curr_actual_line_rec.ATTRIBUTE6 := task_charge_line_rec.attribute6;
809 l_curr_actual_line_rec.ATTRIBUTE7 := task_charge_line_rec.attribute7;
810 l_curr_actual_line_rec.ATTRIBUTE8 := task_charge_line_rec.attribute8;
811 l_curr_actual_line_rec.ATTRIBUTE9 := task_charge_line_rec.attribute9;
812 l_curr_actual_line_rec.ATTRIBUTE10 := task_charge_line_rec.attribute10;
813 l_curr_actual_line_rec.ATTRIBUTE11 := task_charge_line_rec.attribute11;
814 l_curr_actual_line_rec.ATTRIBUTE12 := task_charge_line_rec.attribute12;
815 l_curr_actual_line_rec.ATTRIBUTE13 := task_charge_line_rec.attribute13;
816 l_curr_actual_line_rec.ATTRIBUTE14 := task_charge_line_rec.attribute14;
817 l_curr_actual_line_rec.ATTRIBUTE15 := task_charge_line_rec.attribute15;
818 l_curr_actual_line_rec.LOCATOR_ID
819 l_curr_actual_line_rec.LOC_SEGMENT1
820 l_curr_actual_line_rec.LOC_SEGMENT2
821 l_curr_actual_line_rec.LOC_SEGMENT3
822 l_curr_actual_line_rec.LOC_SEGMENT4
823 l_curr_actual_line_rec.LOC_SEGMENT5
824 l_curr_actual_line_rec.LOC_SEGMENT6
825 l_curr_actual_line_rec.LOC_SEGMENT7
826 l_curr_actual_line_rec.LOC_SEGMENT8
827 l_curr_actual_line_rec.LOC_SEGMENT9
828 l_curr_actual_line_rec.LOC_SEGMENT10
829 l_curr_actual_line_rec.LOC_SEGMENT11
830 l_curr_actual_line_rec.LOC_SEGMENT12
831 l_curr_actual_line_rec.LOC_SEGMENT13
832 l_curr_actual_line_rec.LOC_SEGMENT14
833 l_curr_actual_line_rec.LOC_SEGMENT15
834 l_curr_actual_line_rec.LOC_SEGMENT16
835 l_curr_actual_line_rec.LOC_SEGMENT17
836 l_curr_actual_line_rec.LOC_SEGMENT18
837 l_curr_actual_line_rec.LOC_SEGMENT19
838 l_curr_actual_line_rec.LOC_SEGMENT20
839 */
840
841 -- We now create a corresponding Repair Actual line.
842 BEGIN
843
844 if (lc_proc_level >= lc_debug_level) then
845 FND_LOG.STRING(lc_proc_level, lc_mod_name,
846 'Calling CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines');
847 end if;
848
849 CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines(
850 p_api_version => 1.0,
851 p_commit => p_commit,
852 p_init_msg_list => p_init_msg_list,
853 p_validation_level => p_validation_level,
854 px_csd_actual_lines_rec => l_curr_actual_line_rec,
855 px_charges_rec => l_charge_line_rec,
856 x_return_status => x_return_status,
857 x_msg_count => x_msg_count,
858 x_msg_data => x_msg_data
859 );
860
861 if (lc_proc_level >= lc_debug_level) then
862 FND_LOG.STRING(lc_proc_level, lc_mod_name,
863 'Returned from CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines');
864 end if;
865
866 -- Throw an error if the API returned an error.
867 -- We do not stall the process if we find an error in
868 -- copying the charge line. We continue processing of
869 -- other lines. We just skip the current row.
870 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
871 RAISE FND_API.G_EXC_ERROR;
872 END IF;
873
874 IF (l_curr_actual_line_rec.repair_actual_line_id IS NULL) THEN
875 if (lc_proc_level >= lc_debug_level) then
876 FND_LOG.STRING(lc_proc_level, lc_mod_name,
877 'Unable to create a repair actual line. Create API returned NULL for the repair actual line identifier.');
878 end if;
879 FND_MESSAGE.SET_NAME('CSD','CSD_ACT_NULL_ACTUAL_ID');
880 -- 'Unable to create a repair actual line. Create API returned NULL for the repair actual line identifier.
881 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884
885 EXCEPTION
886 WHEN FND_API.G_EXC_ERROR THEN
887 l_skip_curr_row := TRUE;
888 if (lc_excep_level >= lc_debug_level) then
889 FND_LOG.STRING(lc_excep_level, lc_mod_name,
890 'Encountered an EXC error while creating a repair actual line.');
891 end if;
892
893 WHEN OTHERS THEN
894 if (lc_excep_level >= lc_debug_level) then
895 FND_LOG.STRING(lc_excep_level, lc_mod_name,
896 'Encountered an OTHERS error while creating a repair actual line.');
897 end if;
898 l_skip_curr_row := TRUE;
899 FND_MESSAGE.SET_NAME('CSD','CSD_ACT_ERROR_ACTUAL_LINE');
900 -- Encountered an unknown error while creating a repair actual line. SQLCODE = $SQLCODE , SQLERRM = $SQLERRM
901 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
902 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
903 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
904
905 END;
906
907 -- swai: bug 7122368 - 3rd party billing, need to set account info
908 -- update actual line to have default bill-to and ship-to.
909 BEGIN
910 l_charge_line_rec.estimate_detail_id := task_charge_line_rec.estimate_detail_id;
911 l_charge_line_rec.bill_to_party_id := l_bill_to_party_id;
912 l_charge_line_rec.bill_to_account_id := l_bill_to_account_id;
913 l_charge_line_rec.invoice_to_org_id := l_bill_to_party_site_id;
914 l_charge_line_rec.ship_to_party_id := l_ship_to_party_id;
915 l_charge_line_rec.ship_to_account_id := l_ship_to_account_id;
916 l_charge_line_rec.ship_to_org_id := l_ship_to_party_site_id;
917
918 if (lc_proc_level >= lc_debug_level) then
919 FND_LOG.STRING(lc_proc_level, lc_mod_name,
920 'Calling CSD_REPAIR_ACTUAL_LINES_PVT.update_repair_actual_lines');
921 end if;
922
923 --yvchen: bug#13439235
924 IF (l_curr_actual_line_rec.repair_actual_line_id IS NOT NULL) THEN
925 OPEN cur_order_line_id(l_curr_actual_line_rec.repair_actual_line_id);
926 FETCH cur_order_line_id into l_order_line_id;
927 CLOSE cur_order_line_id;
928 END IF;
929
930 --yvchen: bug#13439235
931 --If the actual line already booked, which has the order line id,
932 --we can not update this line.
933 If (l_order_line_id is null) Then
934 --yvchen: bug#13439235
935 CSD_REPAIR_ACTUAL_LINES_PVT.update_repair_actual_lines (
936 p_api_version => 1.0,
937 p_commit => p_commit,
938 p_init_msg_list => p_init_msg_list,
939 p_validation_level => p_validation_level,
940 px_csd_actual_lines_rec => l_curr_actual_line_rec,
941 px_charges_rec => l_charge_line_rec,
942 x_return_status => x_return_status,
943 x_msg_count => x_msg_count,
944 x_msg_data => x_msg_data
945 );
946 if (lc_proc_level >= lc_debug_level) then
947 FND_LOG.STRING(lc_proc_level, lc_mod_name,
948 'Returned from CSD_REPAIR_ACTUAL_LINES_PVT.update_repair_actual_lines');
949 end if;
950 end if;
951
952 -- Throw an error if the API returned an error.
953 -- We do not stall the process if we find an error in
954 -- copying the charge line. We continue processing of
955 -- other lines. We just skip the current row.
956 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959
960 EXCEPTION
961 WHEN FND_API.G_EXC_ERROR THEN
962 l_skip_curr_row := TRUE;
963 if (lc_proc_level >= lc_debug_level) then
964 FND_LOG.STRING(lc_proc_level, lc_mod_name,
965 'Encountered an EXEC error while updating a repair actual line with billing information.');
966 end if;
967
968 WHEN OTHERS THEN
969 l_skip_curr_row := TRUE;
970 if (lc_proc_level >= lc_debug_level) then
971 FND_LOG.STRING(lc_proc_level, lc_mod_name,
972 'Encountered OTHERS error while updating a repair actual line with billing information.');
973 end if;
974 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_ERROR_ACTUAL_LINE');
975 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
976 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
977 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
978
979 END;
980
981 IF l_skip_curr_row THEN
982 if (lc_stat_level >= lc_debug_level) then
983 FND_LOG.STRING(lc_stat_level, lc_mod_name,
984 'l_skip_curr_row = true');
985 end if;
986
987 -- we rollback any updates/inserts for the current
988 -- record and set the warning flag to TRUE.
989 ROLLBACK TO current_actual_line_sp;
990 x_warning_flag := FND_API.G_TRUE;
991
992 -- Increment the total count.
993 l_import_line_failed_count := l_import_line_failed_count + 1;
994
995 -- Log all the warnigs/error in the stack.
996 if (lc_proc_level >= lc_debug_level) then
997 FND_LOG.STRING(lc_proc_level, lc_mod_name,
998 'Calling CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
999 end if;
1000 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs(
1001 p_api_version => 1.0,
1002 p_module_code => G_MSG_MODULE_CODE_ACT,
1003 p_source_entity_id1 => p_repair_line_id,
1004 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_TASK,
1005 p_source_entity_id2 => task_charge_line_rec.actual_source_id,
1006 x_return_status => x_return_status,
1007 x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data
1009 );
1010 if (lc_proc_level >= lc_debug_level) then
1011 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1012 'Returned from CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
1013 end if;
1014
1015 -- If we are unable to log messages then we stop
1016 -- further processing.
1017 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1018 if (lc_proc_level >= lc_debug_level) then
1019 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1020 'Unable to save messages using the generic logging utility.');
1021 end if;
1022 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_GENERIC_SAVE_FAILED');
1023 -- Unable to save messages using the generic logging utility.
1024 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027
1028 END IF;
1029
1030 END LOOP;
1031
1032 x_warning_flag := FND_API.G_TRUE;
1033
1034 -- If no eligible task debrief lines found for import.
1035 IF( l_import_line_total_count <= 0 ) THEN
1036 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_TASK_INELIGIBLE');
1037 -- No eligible Task debrief lines found for import into Actuals.
1038 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_INFORMATION_MSG);
1039 ELSE -- Attempt to import task debrief lines was made.
1040
1041 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_TASK_SUMMARY');
1042 -- Import of Task debrief lines into Actuals has completed. Failed to import
1043 -- FAILED_COUNT lines. PASS_COUNT lines were imported successfully.
1044 FND_MESSAGE.set_token('FAILED_COUNT', l_import_line_failed_count);
1045 FND_MESSAGE.set_token('PASS_COUNT',(l_import_line_total_count - l_import_line_failed_count));
1046 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_INFORMATION_MSG);
1047 END IF;
1048
1049 if (lc_proc_level >= lc_debug_level) then
1050 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1051 'Calling procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
1052 end if;
1053
1054 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs(
1055 p_api_version => 1.0,
1056 -- p_commit => FND_API.G_TRUE,
1057 -- p_init_msg_list => FND_API.G_FALSE,
1058 -- p_validation_level => p_validation_level,
1059 p_module_code => G_MSG_MODULE_CODE_ACT,
1060 p_source_entity_id1 => p_repair_line_id,
1061 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_TASK,
1062 p_source_entity_id2 => 0, -- We not have any Task id in this case.
1063 x_return_status => x_return_status,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data );
1066
1067 if (lc_proc_level >= lc_debug_level) then
1068 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1069 'Returned from procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
1070 end if;
1071
1072 -- If we are unable to log messages then we
1073 -- throw an error.
1074 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1075 if (lc_proc_level >= lc_debug_level) then
1076 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1077 'Unable to save messages using the generic logging utility.');
1078 end if;
1079 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_GENERIC_SAVE_FAILED');
1080 -- Unable to save messages using the generic logging utility.
1081 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084
1085 -- Standard check of p_commit.
1086 IF FND_API.To_Boolean( p_commit ) THEN
1087 COMMIT WORK;
1088 END IF;
1089
1090 -- logging
1091 if (lc_proc_level >= lc_debug_level) then
1092 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
1093 'Leaving CSD_REPAIR_ACTUAL_PROCESS_PVT.import_actuals_from_task');
1094 end if;
1095
1096 EXCEPTION
1097 WHEN FND_API.G_EXC_ERROR THEN
1098 x_return_status := FND_API.G_RET_STS_ERROR ;
1099 ROLLBACK TO Import_Actuals_Task_sp;
1100 FND_MSG_PUB.Count_And_Get
1101 (p_count => x_msg_count,
1102 p_data => x_msg_data );
1103
1104 -- save message in debug log
1105 IF (lc_excep_level >= lc_debug_level) THEN
1106 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1107 'EXC_ERROR['||x_msg_data||']');
1108 END IF;
1109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1111 ROLLBACK TO Import_Actuals_Task_sp;
1112 FND_MSG_PUB.Count_And_Get
1113 ( p_count => x_msg_count,
1114 p_data => x_msg_data );
1115
1116 -- save message in debug log
1117 IF (lc_excep_level >= lc_debug_level) THEN
1118 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1119 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
1120 END IF;
1121 WHEN OTHERS THEN
1122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1123 ROLLBACK TO Import_Actuals_Task_sp;
1124 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1125 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,lc_api_name );
1126 END IF;
1127 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_ERROR_TASK_IMPORT');
1128 -- Unknown generic error encountered while importing Task debrief lines to Actuals. SQLCODE = $SQLCODE, SQLERRM = $SQLERRM.
1129 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
1130 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
1131 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
1132 FND_MSG_PUB.Count_And_Get
1133 (p_count => x_msg_count,
1134 p_data => x_msg_data );
1135 -- save message in debug log
1136 IF (lc_excep_level >= lc_debug_level) THEN
1137 -- create a seeded message
1138 FND_LOG.STRING(lc_excep_level, lc_mod_name,
1139 'WHEN OTHERS THEN. SQL Message['||sqlerrm||']' );
1140 END IF;
1141
1142 END Import_Actuals_From_Task;
1143
1144
1145 /*--------------------------------------------------------------------*/
1146 /* procedure name: Import_Actuals_From_Wip */
1147 /* description : Procedure is used to import WIP debrief lines into */
1148 /* repair actual lines. We consider material and */
1149 /* resource transactions to create charge/repair actual */
1150 /* lines. */
1151 /* */
1152 /* Called from : Depot Repair Actuals UI */
1153 /* */
1154 /* x_warning_flag : This flag communicates to the calling procedure */
1155 /* whether there are any messages logged that can be */
1156 /* displayed to the user. If the value is G_TRUE */
1157 /* then it indicates that one or more message have */
1158 /* been logged. */
1159 /* */
1160 /* Note : This procedure assumes that the Actual header is created */
1161 /* prior to calling this procedure. */
1162 /*--------------------------------------------------------------------*/
1163
1164 PROCEDURE Import_Actuals_From_Wip( p_api_version IN NUMBER,
1165 p_commit IN VARCHAR2,
1166 p_init_msg_list IN VARCHAR2,
1167 p_validation_level IN NUMBER,
1168 x_return_status OUT NOCOPY VARCHAR2,
1169 x_msg_count OUT NOCOPY NUMBER,
1170 x_msg_data OUT NOCOPY VARCHAR2,
1171 p_repair_line_id IN NUMBER,
1172 p_repair_actual_id IN NUMBER,
1173 p_repair_type_id IN NUMBER,
1174 p_business_process_id IN NUMBER,
1175 p_currency_code IN VARCHAR2,
1176 p_incident_id IN NUMBER,
1177 p_organization_id IN NUMBER,
1178 x_warning_flag OUT NOCOPY VARCHAR2 ) IS
1179
1180 -- Constants --
1181 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1182 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1183 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1184 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1185 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1186 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1187 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1188 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repair_actual_process_pvt.import_actuals_from_wip';
1189 lc_api_name CONSTANT VARCHAR2(30) := 'IMPORT_ACTUALS_FROM_WIP';
1190 lc_api_version CONSTANT NUMBER := 1.0;
1191
1192 -- Although both the constants have the same value, they have
1193 -- been deliberatly defined as different. We use diff constants as
1194 -- they really represent different entities and hence any future
1195 -- maintenenace will be easier.
1196 G_ACTUAL_MSG_ENTITY_WIP CONSTANT VARCHAR2(30) := 'WIP';
1197 G_ACTUAL_SOURCE_CODE_WIP CONSTANT VARCHAR2(30) := 'WIP';
1198
1199 -- For charge line type 'ACTUAL'.
1200 G_CHARGE_LINE_TYPE_ACTUAL CONSTANT VARCHAR2(30) := 'ACTUAL';
1201
1202 -- Variables --
1203
1204 -- Stores default contract and pricelist info.
1205 l_default_contract_line_id NUMBER := NULL;
1206 l_default_price_list_hdr_id NUMBER := NULL;
1207
1208 /*Fixed for bug#5846031
1209 po number from RO should be defaulted on actual lines
1210 created from WIP.
1211 */
1212 l_default_po_number csd_repairs.default_po_num%type;
1213
1214 -- swai: bug 7119691
1215 l_bill_to_account_id NUMBER;
1216 l_bill_to_party_id NUMBER;
1217 l_bill_to_party_site_id NUMBER;
1218 l_ship_to_account_id NUMBER;
1219 l_ship_to_party_id NUMBER;
1220 l_ship_to_party_site_id NUMBER;
1221
1222 GENERIC_MSG_SAVE_FAILED EXCEPTION;
1223
1224 -- Keeps the count of WIP jobs
1225 l_wip_count NUMBER := 0;
1226
1227 -- Cursors --
1228
1229 -- It forms the outer most loop for the processing.
1230 -- Ensures that we process one WIP job at a time.
1231 CURSOR c_eligible_WIP_Jobs IS
1232 SELECT XREF.wip_entity_id, XREF.organization_id WIP_Organization_Id,
1233 XREF.inventory_item_id, XREF.JOB_NAME
1234 FROM CSD_ACTUALS_FROM_WIP_V XREF
1235 WHERE XREF.repair_line_id = p_repair_line_id;
1236
1237 -- A note on the view CSD_ACTUALS_FROM_WIP_V.
1238 -- 1. It gets all the WIP jobs for an RO that have the
1239 -- statuses - 4(Complete), 5(Complete - No Charge) and 12(Closed).
1240 -- 2. We opted to also include 'Complete' (NOT in the design) as user
1241 -- cannot update a WIP job status to 'Complete - NC' within Depot
1242 -- workbench. For a WIP job with status 4, there can more transactions
1243 -- whereas there can be NO transactions for status 5. So there is a
1244 -- potential risk that the user may not be able to import the newly
1245 -- created transaction lines if he/she has already imported that
1246 -- WIP job.
1247 -- This has been logged in the issue list (#126).
1248 -- 3. The view makes sure that any WIP jobs that is/are shared
1249 -- with other repair orders are not fetched.
1250
1251 -- Gets all the WIP jobs for the repair order that are
1252 -- shared with other repair orders.
1253 -- WIP jobs that are 'shared' across repair orders are
1254 -- ineligble for importing of WIP debrief lines.
1255 CURSOR c_ineligible_WIP_Jobs IS
1256 SELECT XREF.wip_entity_id,
1257 WENT.WIP_ENTITY_NAME JOB_NAME
1258 FROM CSD_REPAIR_JOB_XREF XREF,
1259 WIP_ENTITIES WENT
1260 WHERE XREF.repair_line_id = p_repair_line_id
1261 AND WENT.wip_entity_id = XREF.wip_entity_id
1262 AND EXISTS
1263 (SELECT 'x'
1264 FROM CSD_REPAIR_JOB_XREF RJOB
1265 WHERE RJOB.wip_entity_id = XREF.wip_entity_id
1266 HAVING COUNT(*) > 1
1267 );
1268
1269 -- Gets the actual lines from material transactions.
1270 -- Points to note:
1271 -- 1. We consider only 'Component Issue' and 'Component Return'.
1272 -- 2. 'Component Issue' is -ve quantity and 'Component Return' is +ve
1273 -- from inventory point of view. But it's other way around from
1274 -- Depot Repair POV.
1275 -- 3. The SUM function is intended to cancel out any +ve and -ve qty.
1276 -- 4. If an item, that is same as the Assembly item, is issued/returned
1277 -- to the job then we do not consider it to be a material line.
1278 -- 5. We use primary quantity/UOM and NOT transactional quantity/UOM.
1279 -- 6. The view CSD_ACTUALS_FROM_WIP_V considers only the WIP jobs that have
1280 -- not been imported.
1281
1282 CURSOR c_actual_lines_from_materials( l_wip_entity_id NUMBER,
1283 l_inventory_item_id NUMBER) IS
1284 SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
1285 MSI.primary_uom_code UOM,
1286 -- swai: bug fix 4458737 (FP of 4425939) remove CEIL
1287 -- CEIL(SUM( DECODE( MMT.transaction_type_id
1288 SUM( DECODE( MMT.transaction_type_id
1289 , G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
1290 , G_MTL_TXN_TYPE_COMP_RETURN,
1291 ( -1 * ABS( mmt.primary_quantity )))) QUANTITY,
1292 MSI.concatenated_segments ITEM_NAME,
1293 MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
1294 TXBT.txn_billing_type_id, TXBT.transaction_type_id,
1295 G_ACTUAL_SOURCE_CODE_WIP ACTUAL_SOURCE_CODE,
1296 l_wip_entity_id ACTUAL_SOURCE_ID
1297 FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_SYSTEM_ITEMS_KFV MSI,
1298 CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
1299 WHERE MMT.transaction_source_id = l_wip_entity_id
1300 AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
1301 AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
1302 G_MTL_TXN_TYPE_COMP_RETURN )
1303 AND MMT.inventory_item_id <> l_inventory_item_id
1304 AND MSI.inventory_item_id = MMT.inventory_item_id
1305 -- AND MSI.organization_id = cs_std.get_item_valdn_orgzn_id
1306 AND MSI.organization_id = p_organization_id
1307 AND MSI.material_billable_flag IS NOT NULL
1308 AND TXBT.billing_type = MSI.material_billable_flag
1309 AND RTYP.repair_type_id = p_repair_type_id
1310 AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
1311 AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1312 AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
1313 GROUP BY MMT.inventory_item_id, MSI.primary_uom_code,
1314 MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
1315 TXBT.txn_billing_type_id, TXBT.transaction_type_id,
1316 l_wip_entity_id
1317 ORDER BY MMT.inventory_item_id;
1318
1319 --bug#9557061
1320 CURSOR c_actual_lines_from_materials1( l_wip_entity_id NUMBER,
1321 l_inventory_item_id NUMBER) IS
1322 SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
1323 MSI.primary_uom_code UOM,
1324 SUM( ABS( mmt.primary_quantity )) QUANTITY,
1325 MSI.concatenated_segments ITEM_NAME,
1326 MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
1327 TXBT.txn_billing_type_id, TXBT.transaction_type_id,
1328 G_ACTUAL_SOURCE_CODE_WIP ACTUAL_SOURCE_CODE,
1329 l_wip_entity_id ACTUAL_SOURCE_ID
1330 FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_SYSTEM_ITEMS_KFV MSI,
1331 CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
1332 WHERE MMT.transaction_source_id = l_wip_entity_id
1333 AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
1334 AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
1335 G_MTL_TXN_TYPE_COMP_RETURN )
1336 AND MMT.inventory_item_id <> l_inventory_item_id
1337 AND MSI.inventory_item_id = MMT.inventory_item_id
1338 -- AND MSI.organization_id = cs_std.get_item_valdn_orgzn_id
1339 AND MSI.organization_id = p_organization_id
1340 AND MSI.material_billable_flag IS NOT NULL
1341 AND TXBT.billing_type = MSI.material_billable_flag
1342 AND RTYP.repair_type_id = p_repair_type_id
1343 AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
1344 AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1345 AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
1346 AND MMT.transaction_type_id = G_MTL_TXN_TYPE_COMP_ISSUE
1347 GROUP BY MMT.inventory_item_id, MSI.primary_uom_code,
1348 MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
1349 TXBT.txn_billing_type_id, TXBT.transaction_type_id,
1350 l_wip_entity_id
1351 ORDER BY MMT.inventory_item_id;
1352 --bug#9557061
1353
1354 -- Gets the actual lines from resource transactions.
1355 -- Points to note are -
1356 -- 1. Resource transactions are represented in WIP transactions table.
1357 -- 2. We assume the 'billable_item_id' to the inventory item for
1358 -- which we create actual lines.
1359 -- 3. The view CSD_ACTUALS_FROM_WIP_V considers only the WIP jobs that have
1360 -- not been imported.
1361 -- 4. We use primary quantity/UOM and NOT transactional quantity/UOM.
1362 -- 5. For resource transactions, we assume, that the txn primary UOM
1363 -- and the billable item is a valid combination for getting the
1364 -- selling price for a pricelist.
1365
1366 CURSOR c_actual_lines_from_resources( l_wip_entity_id NUMBER) IS
1367 SELECT RES.billable_item_id INVENTORY_ITEM_ID,
1368 WTXN.primary_uom UOM,
1369 -- swai: bug fix 4458737 (FP of 4425939) remove CEIL
1370 -- CEIL(SUM( NVL( WTXN.primary_quantity, 0 ))) QUANTITY,
1371 SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
1372 MSI.concatenated_segments ITEM_NAME,
1373 MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
1374 TXBT.txn_billing_type_id, TXBT.transaction_type_id,
1375 G_ACTUAL_SOURCE_CODE_WIP ACTUAL_SOURCE_CODE,
1376 l_wip_entity_id ACTUAL_SOURCE_ID,
1377 RES.resource_id RESOURCE_ID -- Added for ER 3607765, vkjain.
1378 FROM WIP_TRANSACTIONS WTXN, BOM_RESOURCES RES,
1379 MTL_SYSTEM_ITEMS_KFV MSI, CSD_REPAIR_TYPES_SAR RTYP,
1380 CS_TXN_BILLING_TYPES TXBT
1381 WHERE WTXN.wip_entity_id = l_wip_entity_id
1382 AND WTXN.transaction_type IN( 1, 2, 3 )
1383 AND RES.resource_id = WTXN.resource_id
1384 AND MSI.inventory_item_id = RES.billable_item_id
1385 -- and MSI.organization_id = cs_std.get_item_valdn_orgzn_id
1386 AND MSI.organization_id = p_organization_id
1387 AND MSI.material_billable_flag IS NOT NULL
1388 AND TXBT.billing_type = MSI.material_billable_flag
1389 AND RTYP.repair_type_id = p_repair_type_id
1390 AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
1391 AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1392 AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
1393 GROUP BY RES.billable_item_id, WTXN.primary_uom,
1394 MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
1395 TXBT.txn_billing_type_id, TXBT.transaction_type_id,
1396 l_wip_entity_id, RES.resource_id
1397 ORDER BY RES.billable_item_id;
1398
1399 BEGIN
1400
1401 -- Standard Start of API savepoint
1402 SAVEPOINT Import_actuals_wip_sp;
1403
1404 -- Standard call to check for call compatibility.
1405 IF NOT FND_API.Compatible_API_Call( lc_api_version,
1406 p_api_version,
1407 lc_api_name,
1408 G_PKG_NAME ) THEN
1409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1410 END IF;
1411
1412 -- Initialize message list if p_init_msg_list is set to TRUE.
1413 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1414 FND_MSG_PUB.initialize;
1415 END IF;
1416
1417 -- logging
1418 if (lc_proc_level >= lc_debug_level) then
1419 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
1420 'Entering CSD_REPAIR_ACTUAL_PROCESS_PVT.import_actuals_from_wip');
1421 end if;
1422
1423 -- log parameters
1424 if (lc_stat_level >= lc_debug_level) then
1425 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1426 'p_api_version: ' || p_api_version);
1427 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1428 'p_commit: ' || p_commit);
1429 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1430 'p_init_msg_list: ' || p_init_msg_list);
1431 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1432 'p_validation_level: ' || p_validation_level);
1433 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1434 'p_repair_line_id: ' || p_repair_line_id);
1435 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1436 'p_repair_actual_id: ' || p_repair_actual_id);
1437 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1438 'p_repair_type_id: ' || p_repair_type_id);
1439 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1440 'p_business_process_id: ' || p_business_process_id);
1441 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1442 'p_currency_code: ' || p_currency_code);
1443 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1444 'p_incident_id: ' || p_incident_id);
1445 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1446 'p_organization_id: ' || p_organization_id);
1447 end if;
1448
1449 -- Initialize API return status to success
1450 x_return_status := FND_API.G_RET_STS_SUCCESS;
1451
1452 --
1453 -- Begin API Body
1454 --
1455
1456 x_warning_flag := FND_API.G_FALSE;
1457
1458 --DBMS_OUTPUT.put_line( 'before api begin' );
1459
1460 -- Validate mandatory input parameters.
1461 if (lc_proc_level >= lc_debug_level) then
1462 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1463 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_repair_line_id');
1464 end if;
1465 CSD_PROCESS_UTIL.Check_Reqd_Param
1466 ( p_param_value => p_repair_line_id,
1467 p_param_name => 'REPAIR_LINE_ID',
1468 p_api_name => lc_api_name);
1469
1470 if (lc_proc_level >= lc_debug_level) then
1471 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1472 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_repair_type_id');
1473 end if;
1474
1475 CSD_PROCESS_UTIL.Check_Reqd_Param
1476 ( p_param_value => p_repair_type_id,
1477 p_param_name => 'REPAIR_TYPE_ID',
1478 p_api_name => lc_api_name);
1479
1480 if (lc_proc_level >= lc_debug_level) then
1481 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1482 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_business_process_id');
1483 end if;
1484
1485 CSD_PROCESS_UTIL.Check_Reqd_Param
1486 ( p_param_value => p_business_process_id,
1487 p_param_name => 'BUSINESS_PROCESS_ID',
1488 p_api_name => lc_api_name);
1489
1490 if (lc_proc_level >= lc_debug_level) then
1491 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1492 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_currency_code');
1493 end if;
1494
1495 CSD_PROCESS_UTIL.Check_Reqd_Param
1496 ( p_param_value => p_currency_code,
1497 p_param_name => 'CURRENCY_CODE',
1498 p_api_name => lc_api_name);
1499
1500 if (lc_proc_level >= lc_debug_level) then
1501 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1502 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_incident_id');
1503 end if;
1504
1505 CSD_PROCESS_UTIL.Check_Reqd_Param
1506 ( p_param_value => p_incident_id,
1507 p_param_name => 'INCIDENT_ID',
1508 p_api_name => lc_api_name);
1509
1510 if (lc_proc_level >= lc_debug_level) then
1511 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1512 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_organization_id');
1513 end if;
1514
1515 CSD_PROCESS_UTIL.Check_Reqd_Param
1516 ( p_param_value => p_organization_id,
1517 p_param_name => 'ORGANIZATION_ID',
1518 p_api_name => lc_api_name);
1519
1520 if (lc_stat_level >= lc_debug_level) then
1521 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1522 'Done checking required params');
1523 end if;
1524
1525 -- We make API calls to get default contract and price list
1526 -- for the repair order. An assumption is that the contract
1527 -- and the pricelist will together make sense. So we do not
1528 -- unnecessary validate them.
1529
1530 if (lc_proc_level >= lc_debug_level) then
1531 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1532 'Calling CSD_CHARGE_LINE_UTIL.Get_DefaultContract');
1533 end if;
1534
1535 -- Get default Contract.
1536 l_default_contract_line_id := CSD_CHARGE_LINE_UTIL.Get_DefaultContract( p_repair_line_id );
1537
1538 if (lc_proc_level >= lc_debug_level) then
1539 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1540 'Returned from CSD_CHARGE_LINE_UTIL.Get_DefaultContract. '
1541 || 'l_default_contract_line_id = ' || l_default_contract_line_id);
1542 end if;
1543
1544 --DBMS_OUTPUT.put_line( 'l_default_contract_line_id = '
1545 -- || TO_CHAR( l_default_contract_line_id ));
1546
1547 if (lc_proc_level >= lc_debug_level) then
1548 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1549 'Calling CSD_CHARGE_LINE_UTIL.Get_RO_PriceList');
1550 end if;
1551
1552 -- Get default pricelist for the repair order.
1553 l_default_price_list_hdr_id := CSD_CHARGE_LINE_UTIL.Get_RO_PriceList(p_repair_line_id);
1554
1555 if (lc_proc_level >= lc_debug_level) then
1556 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1557 'Returned from CSD_CHARGE_LINE_UTIL.Get_RO_PriceList. '
1558 || 'l_default_price_list_hdr_id = ' ||l_default_price_list_hdr_id);
1559 end if;
1560
1561 --DBMS_OUTPUT.put_line( 'The price list id is = '
1562 -- || l_default_price_list_hdr_id );
1563
1564 IF ( l_default_price_list_hdr_id IS NULL ) THEN
1565 if (lc_proc_level >= lc_debug_level) then
1566 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1567 'Unable to determine default price list for the repair order.');
1568 end if;
1569 -- Unable to determine default pricelist
1570 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_DEFAULT_PL_IMPORT');
1571 -- Unable to determine default price list for the repair order.
1572 -- A default price list must be selected for the repair order to import actual lines.
1573 FND_MSG_PUB.ADD;
1574 RAISE FND_API.G_EXC_ERROR;
1575 END IF;
1576
1577 --DBMS_OUTPUT.put_line( 'The price list is available ' );
1578
1579 -- We should purge the earlier messages before we insert
1580 -- any new ones.
1581 if (lc_proc_level >= lc_debug_level) then
1582 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1583 'Calling CSD_GEN_ERRMSGS_PVT.purge_entity_msgs');
1584 end if;
1585 CSD_GEN_ERRMSGS_PVT.purge_entity_msgs( p_api_version => 1.0,
1586 -- p_commit => FND_API.G_TRUE,
1587 -- p_init_msg_list => FND_API.G_FALSE,
1588 -- p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1589 p_module_code => G_MSG_MODULE_CODE_ACT,
1590 p_source_entity_id1 => p_repair_line_id,
1591 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
1592 p_source_entity_id2 => NULL,-- Purge all records for the entity
1593 x_return_status => x_return_status,
1594 x_msg_count => x_msg_count,
1595 x_msg_data => x_msg_data );
1596
1597 if (lc_proc_level >= lc_debug_level) then
1598 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1599 'Returned from CSD_GEN_ERRMSGS_PVT.purge_entity_msgs');
1600 end if;
1601
1602 -- Do not proceed if unable to purge.
1603 -- Throw an error if the API returned 'no success'.
1604 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1605 RAISE FND_API.G_EXC_ERROR;
1606 END IF;
1607
1608 -- Log info messages for all the wip jobs that are shared with other
1609 -- repair orders.
1610 if (lc_stat_level >= lc_debug_level) then
1611 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1612 'Begin LOOP through c_ineligible_WIP_Jobs');
1613 end if;
1614
1615 FOR inelgible_WIP_rec IN c_ineligible_WIP_Jobs LOOP
1616 IF (lc_stat_level >= lc_debug_level) then
1617 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1618 'The WIP job ' || inelgible_WIP_rec.JOB_NAME || 'is shared across Repair Orders'
1619 || '. It is not imported');
1620 END IF;
1621 -- Add an INFO message indicating whether the job will not be imported.
1622 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_INELIGIBLE_WIP');
1623 -- The WIP job $JOB_NAME is submitted for more than one repair order.
1624 -- The actual lines, for a WIP job that is shared across repair orders,
1625 -- can not be imported.
1626 FND_MESSAGE.set_token( 'JOB_NAME', inelgible_WIP_rec.JOB_NAME );
1627 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_INFORMATION_MSG );
1628
1629 if (lc_proc_level >= lc_debug_level) then
1630 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1631 'Calling CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
1632 end if;
1633 -- We have to log message individually as wip_entity_id is required for
1634 -- logging messages.
1635 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs( p_api_version => 1.0,
1636 -- p_commit => FND_API.G_TRUE,
1637 -- p_init_msg_list => FND_API.G_FALSE,
1638 -- p_validation_level => p_validation_level,
1639 p_module_code => G_MSG_MODULE_CODE_ACT,
1640 p_source_entity_id1 => p_repair_line_id,
1641 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
1642 p_source_entity_id2 => inelgible_WIP_rec.wip_entity_id,
1643 x_return_status => x_return_status,
1644 x_msg_count => x_msg_count,
1645 x_msg_data => x_msg_data );
1646
1647 if (lc_proc_level >= lc_debug_level) then
1648 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1649 'Returned from CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
1650 end if;
1651
1652 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1653 --DBMS_OUTPUT.put_line( 'Unable to save FND msgs' );
1654 RAISE GENERIC_MSG_SAVE_FAILED;
1655 END IF;
1656
1657 END LOOP; -- c_ineligible_WIP_Jobs cursor
1658
1659 if (lc_stat_level >= lc_debug_level) then
1660 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1661 'End LOOP c_ineligible_WIP_Jobs');
1662 end if;
1663
1664 -- The following is the outermost loop to ensure that we
1665 -- process one WIP Job at a time.
1666 -- The idea is that if we get into an error while processing
1667 -- a WIP Job, we skip that one, log a ERROR message and
1668 -- continue with next WIP job.
1669 if (lc_stat_level >= lc_debug_level) then
1670 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1671 'Begin LOOP through c_eligible_WIP_Jobs');
1672 end if;
1673 FOR curr_WIP_job_rec IN c_eligible_WIP_Jobs LOOP
1674 l_wip_count := l_wip_count + 1;
1675
1676 if (lc_stat_level >= lc_debug_level) then
1677 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1678 'WIP count (l_wip_count) is = ' || l_wip_count);
1679 end if;
1680
1681 DECLARE
1682
1683 -- Stores all the material txn lines
1684 l_MLE_MTL_lines_tbl CSD_CHARGE_LINE_UTIL.MLE_LINES_TBL_TYPE;
1685
1686 -- Stores all the WIP/resource txn lines
1687 l_MLE_RES_lines_tbl CSD_CHARGE_LINE_UTIL.MLE_LINES_TBL_TYPE;
1688
1689 -- Stores eligible material/resource txn lines only
1690 x_valid_MLE_lines_tbl CSD_CHARGE_LINE_UTIL.MLE_LINES_TBL_TYPE;
1691
1692 -- Stores eligible charge lines corresponding to x_valid_MLE_lines_tbl
1693 x_charge_lines_tbl CSD_CHARGE_LINE_UTIL.CHARGE_LINES_TBL_TYPE;
1694
1695 -- It's really same as x_valid_MLE_lines_tbl but in repair actual line format.
1696 -- The reason we have two TYPES for the same set of data is that -
1697 -- CSD_CHARGE_LINE_UTIL.MLE_LINES_TBL_TYPE is generic format and can be
1698 -- utilized by both ESTIMATE and ACTUALS, whereas
1699 -- CSD_REPAIR_ACTUAL_LINES_PVT.CSD_ACTUAL_LINES_TBL_TYPE is specific to
1700 -- Actuals.
1701 l_actual_lines_tbl CSD_REPAIR_ACTUAL_LINES_PVT.CSD_ACTUAL_LINES_TBL_TYPE;
1702
1703 l_curr_warning_flag VARCHAR2(5) := FND_API.G_FALSE;
1704 x_curr_warning_flag VARCHAR2(5) := FND_API.G_FALSE;
1705 l_actuals_count NUMBER := 0;
1706
1707 l_message VARCHAR2(30) := NULL;
1708
1709 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1710 l_msg_count NUMBER;
1711 l_msg_data VARCHAR2(200);
1712
1713 BEGIN
1714
1715 SAVEPOINT curr_wip_job_sp;
1716
1717 --dbms_output.put_line('The job name being processed is ' || curr_WIP_job_rec.JOB_NAME);
1718
1719 if (lc_stat_level >= lc_debug_level) then
1720 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1721 'Inside the BLOCK for processing one WIP job at a time.');
1722 end if;
1723
1724 /****** Processing the Material transactions specific data - START *********/
1725
1726 --DBMS_OUTPUT.put_line( 'processing mtl..' );
1727
1728 if (lc_stat_level >= lc_debug_level) then
1729 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1730 'Start: Processing material transactions for the WIP job - ' || curr_WIP_job_rec.JOB_NAME);
1731 end if;
1732
1733 -- Log bulk messages for all the generic warnings for the
1734 -- material transaction lines.
1735 if (lc_proc_level >= lc_debug_level) then
1736 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1737 'Calling Log_WIP_MTL_Txn_warnings');
1738 end if;
1739
1740 Log_WIP_MTL_Txn_warnings(
1741 p_wip_entity_id => curr_WIP_job_rec.wip_entity_id,
1742 p_depot_organization => p_organization_id,
1743 p_wip_organization => curr_WIP_job_rec.WIP_Organization_id,
1744 p_inventory_item_id => curr_WIP_job_rec.inventory_item_id,
1745 p_repair_type_id => p_repair_type_id,
1746 x_warning_flag => x_curr_warning_flag
1747 );
1748
1749 if (lc_proc_level >= lc_debug_level) then
1750 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1751 'Returned from Log_WIP_MTL_Txn_warnings. '
1752 || 'x_curr_warning_flag = ' || x_curr_warning_flag);
1753 end if;
1754
1755 --DBMS_OUTPUT.put_line( 'After log MTL Txn Warnings ...' );
1756
1757 IF ( x_curr_warning_flag <> FND_API.G_FALSE ) THEN
1758 l_curr_warning_flag := FND_API.G_TRUE;
1759 END IF;
1760
1761 -- First, we process the material transaction lines for all the WIP
1762 -- jobs. Getting the table for all the lines.
1763 DECLARE
1764 l_count NUMBER := 0;
1765 BEGIN
1766 if (lc_stat_level >= lc_debug_level) then
1767 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1768 'Begin: loop through the cursor c_actual_lines_from_materials.');
1769 end if;
1770
1771 --bug#9557061
1772 IF (nvl(fnd_profile.value('CSD_IMPORT_WIP_TO_ACTUALS_NET_QTY'),'Y') = 'N') THEN
1773 FOR actuals_rec IN c_actual_lines_from_materials1( curr_WIP_job_rec.wip_entity_id,
1774 curr_WIP_job_rec.inventory_item_id) LOOP
1775 l_count := l_count + 1;
1776 l_MLE_MTL_lines_tbl( l_count ).inventory_item_id := actuals_rec.inventory_item_id;
1777 l_MLE_MTL_lines_tbl( l_count ).uom := actuals_rec.uom;
1778 l_MLE_MTL_lines_tbl( l_count ).quantity := actuals_rec.quantity;
1779 -- l_MLE_MTL_lines_tbl(l_count).selling_price := r1.selling_price;
1780 l_MLE_MTL_lines_tbl( l_count ).item_name := actuals_rec.item_name;
1781 l_MLE_MTL_lines_tbl( l_count ).comms_nl_trackable_flag := actuals_rec.ib_trackable_flag;
1782 l_MLE_MTL_lines_tbl( l_count ).txn_billing_type_id := actuals_rec.txn_billing_type_id;
1783 l_MLE_MTL_lines_tbl( l_count ).transaction_type_id := actuals_rec.transaction_type_id;
1784 l_MLE_MTL_lines_tbl( l_count ).source_code := actuals_rec.actual_source_code;
1785 l_MLE_MTL_lines_tbl( l_count ).source_id1 := actuals_rec.actual_source_id;
1786 END LOOP;
1787
1788 else
1789 FOR actuals_rec IN c_actual_lines_from_materials( curr_WIP_job_rec.wip_entity_id,
1790 curr_WIP_job_rec.inventory_item_id) LOOP
1791 l_count := l_count + 1;
1792 l_MLE_MTL_lines_tbl( l_count ).inventory_item_id := actuals_rec.inventory_item_id;
1793 l_MLE_MTL_lines_tbl( l_count ).uom := actuals_rec.uom;
1794 l_MLE_MTL_lines_tbl( l_count ).quantity := actuals_rec.quantity;
1795 -- l_MLE_MTL_lines_tbl(l_count).selling_price := r1.selling_price;
1796 l_MLE_MTL_lines_tbl( l_count ).item_name := actuals_rec.item_name;
1797 l_MLE_MTL_lines_tbl( l_count ).comms_nl_trackable_flag := actuals_rec.ib_trackable_flag;
1798 l_MLE_MTL_lines_tbl( l_count ).txn_billing_type_id := actuals_rec.txn_billing_type_id;
1799 l_MLE_MTL_lines_tbl( l_count ).transaction_type_id := actuals_rec.transaction_type_id;
1800 l_MLE_MTL_lines_tbl( l_count ).source_code := actuals_rec.actual_source_code;
1801 l_MLE_MTL_lines_tbl( l_count ).source_id1 := actuals_rec.actual_source_id;
1802 END LOOP;
1803 end if;
1804 --bug#9557061
1805
1806 if (lc_stat_level >= lc_debug_level) then
1807 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1808 'End: loop through the cursor c_actual_lines_from_materials.');
1809 end if;
1810 END;
1811
1812 --DBMS_OUTPUT.put_line( 'after MTL actuals loop '
1813 -- || l_MLE_MTL_lines_tbl.COUNT );
1814
1815 if (lc_proc_level >= lc_debug_level) then
1816 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1817 'Calling procedure CSD_CHARGE_LINE_UTIL.Convert_To_Charge_Lines.');
1818 end if;
1819
1820 -- Filter out all the bad data and populate 'Charges' table
1821 -- and MLE table with valid set of data.
1822 CSD_CHARGE_LINE_UTIL.Convert_To_Charge_Lines( p_api_version => 1.0,
1823 p_commit => FND_API.G_FALSE,
1824 p_init_msg_list => FND_API.G_FALSE,
1825 p_validation_level => p_validation_level,
1826 x_return_status => l_return_status,
1827 x_msg_count => l_msg_count,
1828 x_msg_data => l_msg_data,
1829 p_est_act_module_code => G_MSG_MODULE_CODE_ACT,
1830 p_est_act_msg_entity => G_ACTUAL_MSG_ENTITY_WIP,
1831 p_charge_line_type => G_CHARGE_LINE_TYPE_ACTUAL,
1832 p_repair_line_id => p_repair_line_id,
1833 p_repair_actual_id => p_repair_actual_id,
1834 p_repair_type_id => p_repair_type_id,
1835 p_business_process_id => p_business_process_id,
1836 p_currency_code => p_currency_code,
1837 p_incident_id => p_incident_id,
1838 p_organization_id => p_organization_id,
1839 p_price_list_id => l_default_price_list_hdr_id,
1840 p_contract_line_id => l_default_contract_line_id,
1841 p_MLE_lines_tbl => l_MLE_MTL_lines_tbl,
1842 px_valid_MLE_lines_tbl => x_valid_MLE_lines_tbl,
1843 px_charge_lines_tbl => x_charge_lines_tbl,
1844 x_warning_flag => x_curr_warning_flag );
1845
1846 if (lc_proc_level >= lc_debug_level) then
1847 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1848 'Returned from procedure CSD_CHARGE_LINE_UTIL.Convert_To_Charge_Lines. '
1849 || 'x_curr_warning_flag = ' || x_curr_warning_flag);
1850 end if;
1851
1852 --DBMS_OUTPUT.put_line( 'after getting the valid MTL data '
1853 -- || x_valid_MLE_lines_tbl.COUNT );
1854
1855 IF ( x_curr_warning_flag <> FND_API.G_FALSE ) THEN
1856 l_curr_warning_flag := FND_API.G_TRUE;
1857 END IF;
1858
1859 -- Throw an error if the API returned an error.
1860 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1861 RAISE FND_API.G_EXC_ERROR;
1862 END IF;
1863
1864 if (lc_stat_level >= lc_debug_level) then
1865 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1866 'End: Processing material transactions for the WIP job - ' || curr_WIP_job_rec.JOB_NAME);
1867 end if;
1868
1869 /****** Processing the Material transactions specific data - END *********/
1870
1871
1872
1873 /****** Processing the Resource/WIP transactions specific data - START *********/
1874
1875 -- DBMS_OUTPUT.put_line( 'processing RES ..' );
1876
1877 if (lc_stat_level >= lc_debug_level) then
1878 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1879 'Start: Processing Resource/WIP transactions for the WIP job - ' || curr_WIP_job_rec.JOB_NAME);
1880 end if;
1881
1882 -- Log bulk messages for all the generic warnings for the
1883 -- WIP transaction lines.
1884 if (lc_proc_level >= lc_debug_level) then
1885 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1886 'Calling Log_WIP_Resource_Txn_warnings');
1887 end if;
1888 Log_WIP_Resource_Txn_warnings(
1889 p_wip_entity_id => curr_WIP_job_rec.wip_entity_id,
1890 p_depot_organization => p_organization_id,
1891 p_wip_organization => curr_WIP_job_rec.WIP_Organization_Id,
1892 p_repair_type_id => p_repair_type_id,
1893 x_warning_flag => x_curr_warning_flag
1894 );
1895
1896 if (lc_proc_level >= lc_debug_level) then
1897 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1898 'Returned from Log_WIP_Resource_Txn_warnings. '
1899 || 'x_curr_warning_flag = ' || x_curr_warning_flag);
1900 end if;
1901
1902 --DBMS_OUTPUT.put_line( 'After Log_WIP_Resource_Txn_warnings ...' );
1903 IF ( x_curr_warning_flag <> FND_API.G_FALSE ) THEN
1904 l_curr_warning_flag := FND_API.G_TRUE;
1905 END IF;
1906
1907 -- Now, we process the WIP transaction lines (for resources) for all
1908 -- the WIP jobs. Getting the table for all the lines.
1909 DECLARE
1910 l_count NUMBER := 0;
1911 BEGIN
1912 if (lc_stat_level >= lc_debug_level) then
1913 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1914 'Begin: loop through the cursor c_actual_lines_from_resources.');
1915 end if;
1916 FOR actuals_rec IN c_actual_lines_from_resources( curr_WIP_job_rec.wip_entity_id) LOOP
1917 l_count := l_count + 1;
1918 l_MLE_RES_lines_tbl( l_count ).inventory_item_id := actuals_rec.inventory_item_id;
1919 l_MLE_RES_lines_tbl( l_count ).uom := actuals_rec.uom;
1920 l_MLE_RES_lines_tbl( l_count ).quantity := actuals_rec.quantity;
1921 -- l_MLE_RES_lines_tbl(l_count).selling_price := r1.selling_price;
1922 l_MLE_RES_lines_tbl( l_count ).item_name := actuals_rec.item_name;
1923 l_MLE_RES_lines_tbl( l_count ).comms_nl_trackable_flag := actuals_rec.ib_trackable_flag;
1924 l_MLE_RES_lines_tbl( l_count ).txn_billing_type_id := actuals_rec.txn_billing_type_id;
1925 l_MLE_RES_lines_tbl( l_count ).transaction_type_id := actuals_rec.transaction_type_id;
1926 l_MLE_RES_lines_tbl( l_count ).source_code := actuals_rec.actual_source_code;
1927 l_MLE_RES_lines_tbl( l_count ).source_id1 := actuals_rec.actual_source_id;
1928
1929 -- Added for ER 3607765, vkjain.
1930 l_MLE_RES_lines_tbl( l_count ).resource_id := actuals_rec.resource_id;
1931 END LOOP;
1932 if (lc_stat_level >= lc_debug_level) then
1933 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1934 'End: loop through the cursor c_actual_lines_from_resources.');
1935 end if;
1936 END;
1937
1938 --DBMS_OUTPUT.put_line( 'after the RES actuals loop '
1939 -- || l_MLE_MTL_lines_tbl.COUNT );
1940
1941 if (lc_proc_level >= lc_debug_level) then
1942 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1943 'Calling procedure CSD_CHARGE_LINE_UTIL.Convert_To_Charge_Lines.');
1944 end if;
1945
1946 -- Filter out all the bad data and populate 'Charges' table
1947 -- and MLE table with valid set of data.
1948 CSD_CHARGE_LINE_UTIL.Convert_To_Charge_Lines( p_api_version => 1.0,
1949 p_commit => FND_API.G_FALSE,
1950 p_init_msg_list => FND_API.G_FALSE,
1951 p_validation_level => p_validation_level,
1952 x_return_status => l_return_status,
1953 x_msg_count => l_msg_count,
1954 x_msg_data => l_msg_data,
1955 p_est_act_module_code => G_MSG_MODULE_CODE_ACT,
1956 p_est_act_msg_entity => G_ACTUAL_MSG_ENTITY_WIP,
1957 p_charge_line_type => G_CHARGE_LINE_TYPE_ACTUAL,
1958 p_repair_line_id => p_repair_line_id,
1959 p_repair_actual_id => p_repair_actual_id,
1960 p_repair_type_id => p_repair_type_id,
1961 p_business_process_id => p_business_process_id,
1962 p_currency_code => p_currency_code,
1963 p_incident_id => p_incident_id,
1964 p_organization_id => p_organization_id,
1965 p_price_list_id => l_default_price_list_hdr_id,
1966 p_contract_line_id => l_default_contract_line_id,
1967 p_MLE_lines_tbl => l_MLE_RES_lines_tbl,
1968 px_valid_MLE_lines_tbl => x_valid_MLE_lines_tbl,
1969 px_charge_lines_tbl => x_charge_lines_tbl,
1970 x_warning_flag => x_curr_warning_flag );
1971
1972 if (lc_proc_level >= lc_debug_level) then
1973 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1974 'Returned from procedure CSD_CHARGE_LINE_UTIL.Convert_To_Charge_Lines. '
1975 || 'x_curr_warning_flag = ' || x_curr_warning_flag);
1976 end if;
1977
1978 --DBMS_OUTPUT.put_line( 'after getting the valid RES lines '
1979 -- || x_valid_MLE_lines_tbl.COUNT );
1980
1981 IF ( x_curr_warning_flag <> FND_API.G_FALSE ) THEN
1982 l_curr_warning_flag := FND_API.G_TRUE;
1983 END IF;
1984
1985 -- Throw an error if the API returned an error.
1986 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1987 RAISE FND_API.G_EXC_ERROR;
1988 END IF;
1989
1990 if (lc_stat_level >= lc_debug_level) then
1991 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1992 'END: Processing Resource/WIP transactions for the WIP job - ' || curr_WIP_job_rec.JOB_NAME);
1993 end if;
1994
1995 /****** Processing the Resource/WIP transactions specific data - END *********/
1996
1997 --DBMS_OUTPUT.put_line( 'before call to fnd save msgs' );
1998
1999 -- Log all the warnings that may have been added to
2000 -- the message stack earlier.
2001 IF ( l_curr_warning_flag <> FND_API.G_FALSE ) THEN
2002 x_warning_flag := l_curr_warning_flag;
2003
2004 if (lc_stat_level >= lc_debug_level) then
2005 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2006 'x_warning_flag is set to = ' || x_warning_flag);
2007 end if;
2008
2009 if (lc_proc_level >= lc_debug_level) then
2010 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2011 'Calling procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs.');
2012 end if;
2013
2014 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs( p_api_version => 1.0,
2015 -- p_commit => FND_API.G_TRUE,
2016 -- p_init_msg_list => FND_API.G_FALSE,
2017 -- p_validation_level => p_validation_level,
2018 p_module_code => G_MSG_MODULE_CODE_ACT,
2019 p_source_entity_id1 => p_repair_line_id,
2020 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
2021 p_source_entity_id2 => curr_WIP_job_rec.wip_entity_id,
2022 x_return_status => l_return_status,
2023 x_msg_count => l_msg_count,
2024 x_msg_data => l_msg_data );
2025
2026 if (lc_proc_level >= lc_debug_level) then
2027 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2028 'Returned from procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs.');
2029 end if;
2030
2031 --DBMS_OUTPUT.put_line( 'before call to generic save msgs' );
2032
2033 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2034 --DBMS_OUTPUT.put_line( 'Unable to save FND msgs' );
2035 RAISE GENERIC_MSG_SAVE_FAILED;
2036 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 END IF;
2038
2039 -- swai: bug 10383350
2040 -- if profile set so that entire WIP job is not imported if warnings/errors encountered,
2041 -- then raise exception to rollback.
2042 if (nvl(fnd_profile.value('CSD_IMPORT_WIP_TO_ACT_ON_SUCCESS'),'N') = 'Y') then
2043 if (lc_proc_level >= lc_debug_level) then
2044 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2045 'Throw EXC error since not all lines were processed sucessfully');
2046 end if;
2047 RAISE FND_API.G_EXC_ERROR;
2048 end if;
2049
2050 END IF;
2051
2052 --DBMS_OUTPUT.put_line( 'after generic msgs. Before mle to actuals' );
2053 --DBMS_OUTPUT.put_line( 'The table count is '
2054 -- || x_valid_MLE_lines_tbl.COUNT );
2055
2056 -- Convert the generic MLE structure to specific ACTUAL LINES structure.
2057 Convert_MLE_To_Actuals( p_MLE_lines_tbl => x_valid_MLE_lines_tbl,
2058 p_repair_line_id => p_repair_line_id,
2059 p_repair_actual_id => p_repair_actual_id,
2060 x_actual_lines_tbl => l_actual_lines_tbl );
2061
2062 /*Fixed for bug#5846031
2063 PO number from RO should be defaulted on actual lines
2064 created from WIP.
2065 */
2066 begin
2067 select default_po_num
2068 into l_default_po_number
2069 from csd_repairs
2070 where repair_line_id = p_repair_line_id;
2071 exception
2072 when no_data_found then
2073 l_default_po_number := Null;
2074 when others then
2075 l_default_po_number := Null;
2076 end ;
2077
2078 -- swai: bug 7119691
2079 Get_Default_Third_Party_Info (p_repair_line_id => p_repair_line_id,
2080 x_bill_to_account_id => l_bill_to_account_id,
2081 x_bill_to_party_id => l_bill_to_party_id,
2082 x_bill_to_party_site_id => l_bill_to_party_site_id,
2083 x_ship_to_account_id => l_ship_to_account_id,
2084 x_ship_to_party_id => l_ship_to_party_id,
2085 x_ship_to_party_site_id => l_ship_to_party_site_id);
2086
2087 --DBMS_OUTPUT.put_line( 'after Conv MLE to actuals' );
2088 l_actuals_count := l_actual_lines_tbl.COUNT;
2089
2090 -- Insert repair actual line for each record in the tbl.
2091 -- If any row encounters any error, stop processing
2092 -- any further.
2093 if (lc_proc_level >= lc_debug_level) then
2094 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2095 'Calling procedure CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines'
2096 || ' for all actual lines. l_actuals_count = ' || l_actuals_count);
2097 end if;
2098
2099 FOR i IN 1..l_actuals_count LOOP
2100 --DBMS_OUTPUT.put_line( 'count = '
2101 -- || TO_CHAR( i ));
2102 /*Fixed for bug#5846031
2103 PO number from RO should be defaulted on actual lines
2104 created from WIP.
2105 Default if PO is not null.
2106 */
2107 If l_default_po_number is not null then
2108 x_charge_lines_tbl( i ).purchase_order_num := l_default_po_number ;
2109 end if;
2110
2111 -- swai: bug 7119691 - 3rd party billing, need to set account info
2112 x_charge_lines_tbl( i ).bill_to_party_id := l_bill_to_party_id;
2113 x_charge_lines_tbl( i ).bill_to_account_id := l_bill_to_account_id;
2114 x_charge_lines_tbl( i ).invoice_to_org_id := l_bill_to_party_site_id;
2115 x_charge_lines_tbl( i ).ship_to_party_id := l_ship_to_party_id;
2116 x_charge_lines_tbl( i ).ship_to_account_id := l_ship_to_account_id;
2117 x_charge_lines_tbl( i ).ship_to_org_id := l_ship_to_party_site_id;
2118
2119 CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines( p_api_version => 1.0,
2120 p_commit => FND_API.G_FALSE,
2121 p_init_msg_list => FND_API.G_FALSE,
2122 p_validation_level => 0,
2123 px_CSD_ACTUAL_LINES_REC => l_actual_lines_tbl( i ),
2124 px_CHARGES_REC => x_charge_lines_tbl( i ),
2125 x_return_status => l_return_status,
2126 x_msg_count => l_msg_count,
2127 x_msg_data => l_msg_data );
2128
2129 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2130 if (lc_proc_level >= lc_debug_level) then
2131 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2132 'Unable to create repair actual lines for count = ' || i);
2133 end if;
2134 --DBMS_OUTPUT.put_line( 'Unable to create repair actual lines' );
2135 RAISE FND_API.G_EXC_ERROR;
2136 END IF;
2137 END LOOP;
2138
2139 if (lc_proc_level >= lc_debug_level) then
2140 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2141 'Returned from procedure CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines'
2142 || ' for all actual lines. l_actuals_count = ' || l_actuals_count);
2143 end if;
2144
2145 IF ( l_curr_warning_flag <> FND_API.G_FALSE ) THEN
2146 l_message := 'CSD_ACT_WIP_IMPORT_W_WARN';
2147 -- Debrief lines import for the WIP Job $JOB_NAME completed with warnings. $ACTUAL_LINE_COUNT new repair actual line(s) were created for the job.
2148 if (lc_stat_level >= lc_debug_level) then
2149 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2150 'Debrief lines import for the WIP Job ' || curr_WIP_job_rec.JOB_NAME
2151 || ' completed with warnings. l_actuals_count = ' || l_actuals_count);
2152 end if;
2153 ELSE
2154 l_message := 'CSD_ACT_WIP_IMPORT_NO_WARN';
2155 -- Debrief lines import for the WIP Job $JOB_NAME completed with NO warnings. $ACTUAL_LINE_COUNT new repair actual line(s) were created for the job.
2156 if (lc_stat_level >= lc_debug_level) then
2157 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2158 'Debrief lines import for the WIP Job ' || curr_WIP_job_rec.JOB_NAME
2159 || ' completed with NO warnings. l_actuals_count = ' || l_actuals_count);
2160 end if;
2161 END IF;
2162
2163 -- Add an INFO message indicating whether the jobs has any warnings.
2164 -- Also mention the number of actual lines created.
2165 FND_MESSAGE.SET_NAME( 'CSD', l_message );
2166 FND_MESSAGE.set_token( 'JOB_NAME', curr_WIP_job_rec.JOB_NAME );
2167 FND_MESSAGE.set_token( 'ACTUAL_LINE_COUNT', l_actuals_count );
2168 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_INFORMATION_MSG );
2169
2170 -- The reasons the following code appears twice in the procedure are -
2171 -- 1. We want to log warnings even if there is error in creating
2172 -- repair actual line. That's why the previous call is placed
2173 -- before the 'create actual line' calls.
2174 -- 2. We want to log the INFO whether a specific WIP Job processing
2175 -- has completed with or without warnings. This call should
2176 -- (obviously) be placed after 'create actual line' calls.
2177
2178 if (lc_proc_level >= lc_debug_level) then
2179 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2180 'Calling procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs.');
2181 end if;
2182
2183 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs( p_api_version => 1.0,
2184 -- p_commit => FND_API.G_TRUE,
2185 -- p_init_msg_list => FND_API.G_FALSE,
2186 -- p_validation_level => p_validation_level,
2187 p_module_code => G_MSG_MODULE_CODE_ACT,
2188 p_source_entity_id1 => p_repair_line_id,
2189 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
2190 p_source_entity_id2 => curr_WIP_job_rec.wip_entity_id,
2191 x_return_status => l_return_status,
2192 x_msg_count => l_msg_count,
2193 x_msg_data => l_msg_data );
2194
2195 if (lc_proc_level >= lc_debug_level) then
2196 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2197 'Returning from procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs.');
2198 end if;
2199
2200 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2201 --DBMS_OUTPUT.put_line( 'Unable to save FND msgs' );
2202 RAISE GENERIC_MSG_SAVE_FAILED;
2203 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204 END IF;
2205
2206
2207 EXCEPTION
2208 -- If there is an error for a specific WIP Job then we
2209 -- do not consider it be a fatal error. We do not stop
2210 -- the process but continue with the next WIP job. We
2211 -- just log messages in the generic utlity as ERROR.
2212
2213 WHEN FND_API.G_EXC_ERROR THEN
2214 -- x_return_status := FND_API.G_RET_STS_ERROR;
2215 x_warning_flag := FND_API.G_TRUE;
2216 ROLLBACK TO curr_wip_job_sp;
2217
2218 -- Add an ERROR message.
2219 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_WIP_IMPORT_ERROR');
2220 -- An error encountered while importing WIP debrief lines into Actuals for the WIP entity - $JOB_NAME. The lines for the WIP Job will not be imported into actuals.
2221 FND_MESSAGE.set_token( 'JOB_NAME', curr_WIP_job_rec.JOB_NAME );
2222 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2223
2224 -- Save the messages using generic utility.
2225 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs( p_api_version => 1.0,
2226 -- p_commit => FND_API.G_TRUE,
2227 -- p_init_msg_list => FND_API.G_FALSE,
2228 -- p_validation_level => p_validation_level,
2229 p_module_code => G_MSG_MODULE_CODE_ACT,
2230 p_source_entity_id1 => p_repair_line_id,
2231 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
2232 p_source_entity_id2 => curr_WIP_job_rec.wip_entity_id,
2233 x_return_status => l_return_status,
2234 x_msg_count => l_msg_count,
2235 x_msg_data => l_msg_data );
2236
2237 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2238 --DBMS_OUTPUT.put_line( 'Unable to save FND msgs' );
2239 RAISE GENERIC_MSG_SAVE_FAILED;
2240 END IF;
2241
2242 /*
2243 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2244 p_data => x_msg_data);
2245 */
2246
2247 WHEN GENERIC_MSG_SAVE_FAILED THEN
2248 ROLLBACK TO curr_wip_job_sp;
2249 if (lc_proc_level >= lc_debug_level) then
2250 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2251 'Encountered an EXEC error while creating a repair actual lines ' ||
2252 'for the WIP Job ' || curr_WIP_job_rec.JOB_NAME);
2253 end if;
2254 -- We do not want to continue processing.
2255 -- Catch the exception outside, in the outermost loop.
2256 RAISE GENERIC_MSG_SAVE_FAILED;
2257
2258 WHEN OTHERS THEN
2259 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2260
2261 ROLLBACK TO curr_wip_job_sp;
2262 x_warning_flag := FND_API.G_TRUE;
2263
2264 if (lc_proc_level >= lc_debug_level) then
2265 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2266 'Encountered an OTHERS error while creating a repair actual lines ' ||
2267 'for the WIP Job ' || curr_WIP_job_rec.JOB_NAME ||
2268 '. SQLCODE = ' || SQLCODE || '. SQLERRM = ' || SQLERRM);
2269 end if;
2270
2271 --DBMS_OUTPUT.put_line( ' SQLCODE = '
2272 -- || SQLCODE );
2273 --DBMS_OUTPUT.put_line( ' SQLERRM = '
2274 -- || SQLERRM );
2275
2276 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2277 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, lc_api_name );
2278 END IF;
2279
2280 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_WIP_IMP_JOB_ERR');
2281 -- Unknown error encountered while importing WIP debrief lines to Actuals for the WIP entity $JOB_NAME. SQLCODE = $SQLCODE, SQLERRM = $SQLERRM.
2282 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
2283 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
2284 FND_MESSAGE.set_token( 'JOB_NAME', curr_WIP_job_rec.JOB_NAME );
2285 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2286
2287 if (lc_proc_level >= lc_debug_level) then
2288 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2289 'Calling CSD_GEN_ERRMSGS_PVT.save_fnd_msgs in WHEN OTHERS THEN');
2290 end if;
2291
2292 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs( p_api_version => 1.0,
2293 -- p_commit => FND_API.G_TRUE,
2294 -- p_init_msg_list => FND_API.G_FALSE,
2295 -- p_validation_level => p_validation_level,
2296 p_module_code => G_MSG_MODULE_CODE_ACT,
2297 p_source_entity_id1 => p_repair_line_id,
2298 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
2299 p_source_entity_id2 => curr_WIP_job_rec.wip_entity_id,
2300 x_return_status => l_return_status,
2301 x_msg_count => l_msg_count,
2302 x_msg_data => l_msg_data );
2303
2304 if (lc_proc_level >= lc_debug_level) then
2305 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2306 'Returned from CSD_GEN_ERRMSGS_PVT.save_fnd_msgs in WHEN OTHERS THEN');
2307 end if;
2308
2309 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2310 --DBMS_OUTPUT.put_line( 'Unable to save FND msgs' );
2311 RAISE GENERIC_MSG_SAVE_FAILED;
2312 END IF;
2313 /*
2314 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2315 p_data => x_msg_data );
2316 */
2317 END;
2318 END LOOP; -- for cursor c_eligible_WIP_Jobs
2319
2320 if (lc_stat_level >= lc_debug_level) then
2321 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2322 'End LOOP c_eligible_WIP_Jobs');
2323 end if;
2324
2325 IF( l_wip_count <= 0 ) THEN
2326 x_warning_flag := FND_API.G_TRUE;
2327 -- FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_WIP_INELIGIBLE');
2328 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_NO_INELIGIBLE_WIP');
2329 -- No eligible WIP jobs found for import
2330 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_INFORMATION_MSG);
2331 -- FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_WARNING_MSG);
2332 -- FND_MSG_PUB.add;
2333
2334 if (lc_proc_level >= lc_debug_level) then
2335 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2336 'Calling procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
2337 end if;
2338
2339 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs(
2340 p_api_version => 1.0,
2341 -- p_commit => FND_API.G_TRUE,
2342 -- p_init_msg_list => FND_API.G_FALSE,
2343 -- p_validation_level => p_validation_level,
2344 p_module_code => G_MSG_MODULE_CODE_ACT,
2345 p_source_entity_id1 => p_repair_line_id,
2346 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_WIP,
2347 p_source_entity_id2 => -999, -- We not have any WIP id in this case.
2348 x_return_status => x_return_status,
2349 x_msg_count => x_msg_count,
2350 x_msg_data => x_msg_data );
2351
2352 if (lc_proc_level >= lc_debug_level) then
2353 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2354 'Returned from procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
2355 end if;
2356
2357 -- If we are unable to log messages then we
2358 -- throw an error.
2359 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2360 if (lc_proc_level >= lc_debug_level) then
2361 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2362 'Unable to save messages using the generic logging utility.');
2363 end if;
2364 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_GENERIC_SAVE_FAILED');
2365 -- Unable to save messages using the generic logging utility.
2366 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2367 RAISE FND_API.G_EXC_ERROR;
2368 END IF;
2369
2370 END IF;
2371
2372 --dbms_output.put_line('The total wip count is ' || l_wip_count);
2373 --
2374 -- End API Body
2375 --
2376
2377 -- Standard check of p_commit.
2378 IF FND_API.To_Boolean( p_commit ) THEN
2379 COMMIT WORK;
2380 END IF;
2381
2382 -- logging
2383 if (lc_proc_level >= lc_debug_level) then
2384 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
2385 'Leaving CSD_REPAIR_ACTUAL_PROCESS_PVT.import_actuals_from_wip');
2386 end if;
2387
2388 -- Standard call to get message count and IF count is get message info.
2389
2390 /*
2391 FND_MSG_PUB.Count_And_Get
2392 (p_count => x_msg_count,
2393 p_data => x_msg_data );
2394 */
2395
2396 EXCEPTION
2397 WHEN FND_API.G_EXC_ERROR THEN
2398 x_return_status := FND_API.G_RET_STS_ERROR;
2399 ROLLBACK TO Import_actuals_wip_sp;
2400 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2401 p_data => x_msg_data );
2402
2403 -- save message in debug log
2404 IF (lc_excep_level >= lc_debug_level) THEN
2405 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2406 'EXC_ERROR['||x_msg_data||']');
2407 END IF;
2408
2409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2411 ROLLBACK TO Import_actuals_wip_sp;
2412 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2413 p_data => x_msg_data );
2414
2415 -- save message in debug log
2416 IF (lc_excep_level >= lc_debug_level) THEN
2417 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2418 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
2419 END IF;
2420
2421 WHEN GENERIC_MSG_SAVE_FAILED THEN
2422 ROLLBACK TO Import_actuals_wip_sp;
2423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2424
2425 -- save message in debug log
2426 IF (lc_excep_level >= lc_debug_level) THEN
2427 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2428 'GENERIC_MSG_SAVE_FAILED. SQL Message['||x_msg_data||']');
2429 END IF;
2430
2431 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_GENERIC_SAVE_FAILED');
2432 -- Unable to save messages using the generic logging utility.
2433 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2434
2435 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2436 p_data => x_msg_data );
2437
2438 WHEN OTHERS THEN
2439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2440 ROLLBACK TO Import_actuals_wip_sp;
2441 --DBMS_OUTPUT.put_line( ' SQLCODE = '
2442 -- || SQLCODE );
2443 --DBMS_OUTPUT.put_line( ' SQLERRM = '
2444 -- || SQLERRM );
2445
2446 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2447 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, lc_api_name );
2448 END IF;
2449
2450 -- save message in debug log
2451 IF (lc_excep_level >= lc_debug_level) THEN
2452 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2453 'WHEN OTHERS THEN. SQL Message['||SQLERRM||']');
2454 END IF;
2455
2456 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_WIP_IMPORT_OTHERS');
2457 -- Unknown error encountered while importing WIP debrief lines to Actuals. SQLCODE = $SQLCODE, SQLERRM = $SQLERRM
2458 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
2459 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
2460 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2461 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2462 p_data => x_msg_data );
2463 END Import_Actuals_From_Wip;
2464
2465 /*--------------------------------------------------------------------*/
2466 /* procedure name: Import_Actuals_From_Estimate */
2467 /* description : Procedure is used to import Estimates lines into */
2468 /* repair actual lines. Creates new charge lines and */
2469 /* corresponding repair actual lines. */
2470 /* */
2471 /* Called from : Depot Repair Actuals UI */
2472 /* */
2473 /* x_warning_flag : This flag communicates to the calling procedure */
2474 /* whether there are any messages logged that can be */
2475 /* displayed to the user. If the value is G_TRUE */
2476 /* then it indicates that one or more message have */
2477 /* been logged. */
2478 /* */
2479 /*--------------------------------------------------------------------*/
2480
2481 PROCEDURE Import_Actuals_From_Estimate
2482 (
2483 p_api_version IN NUMBER,
2484 p_commit IN VARCHAR2,
2485 p_init_msg_list IN VARCHAR2,
2486 p_validation_level IN NUMBER,
2487 x_return_status OUT NOCOPY VARCHAR2,
2488 x_msg_count OUT NOCOPY NUMBER,
2489 x_msg_data OUT NOCOPY VARCHAR2,
2490 p_repair_line_id IN NUMBER,
2491 p_repair_actual_id IN NUMBER,
2492 x_warning_flag OUT NOCOPY VARCHAR2
2493 )
2494 IS
2495 -- CONSTANTS --
2496 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2497 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
2498 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
2499 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
2500 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
2501 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
2502 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
2503 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repair_actual_process_pvt.import_actuals_from_estimate';
2504 lc_api_name CONSTANT VARCHAR2(30) := 'IMPORT_ACTUALS_FROM_ESTIMATE';
2505 lc_api_version CONSTANT NUMBER := 1.0;
2506
2507 -- VARIABLES --
2508 l_msg_count NUMBER;
2509 l_msg_data VARCHAR2(2000);
2510 l_msg_index NUMBER;
2511
2512 -- Although both the constants have the same value, they have
2513 -- been defined deliberatly as different. We use diff constants as
2514 -- they really represent different entities and hence any future
2515 -- maintenenace will be easier.
2516 G_ACTUAL_MSG_ENTITY_ESTIMATE CONSTANT VARCHAR2(15) := 'ESTIMATE';
2517 G_ACTUAL_SOURCE_CODE_ESTIMATE CONSTANT VARCHAR2(15) := 'ESTIMATE';
2518
2519 -- The following variable will be used for to store ID
2520 -- for the newly created 'actual' charge line.
2521 l_estimate_detail_id NUMBER;
2522
2523 -- We do not populate the following record.
2524 -- It is really a dummy for this procedure as we do create
2525 -- new Charge line(s) when importing lines. We just create 'links'.
2526 l_charge_line_rec CS_CHARGE_DETAILS_PUB.CHARGES_REC_TYPE;
2527
2528 -- The following variable will be used to store
2529 -- actual line info for each record in the loop.
2530 l_curr_actual_line_rec CSD_REPAIR_ACTUAL_LINES_PVT.CSD_ACTUAL_LINES_REC_TYPE;
2531
2532 -- The folowing variable will be used to skip processing for
2533 -- a current row in the loop, if we encounter an error.
2534 l_skip_curr_row BOOLEAN := FALSE;
2535
2536 -- The following variables will keep count
2537 -- of the tota/failedl estimate lines.
2538 l_est_line_total_count NUMBER := 0;
2539 l_est_line_failed_count NUMBER := 0;
2540
2541 -- swai: bug 7119695
2542 l_bill_to_account_id NUMBER;
2543 l_bill_to_party_id NUMBER;
2544 l_bill_to_party_site_id NUMBER;
2545 l_ship_to_account_id NUMBER;
2546 l_ship_to_party_id NUMBER;
2547 l_ship_to_party_site_id NUMBER;
2548
2549 -- Cursor deifnition to fetch all the estimate lines
2550 -- that have NOT been imported into Actuals.
2551 CURSOR c_valid_estimate_lines IS
2552 SELECT ESTL.repair_estimate_line_id,
2553 ESTL.estimate_detail_id,
2554 -- ESTL.item_cost, In 11.5.10 we don't do Actual costing.
2555 ESTL.justification_notes,
2556 ESTL.resource_id,
2557 ESTL.context,
2558 ESTL.attribute1,
2559 ESTL.attribute2,
2560 ESTL.attribute3,
2561 ESTL.attribute4,
2562 ESTL.attribute5,
2563 ESTL.attribute6,
2564 ESTL.attribute7,
2565 ESTL.attribute8,
2566 ESTL.attribute9,
2567 ESTL.attribute10,
2568 ESTL.attribute11,
2569 ESTL.attribute12,
2570 ESTL.attribute13,
2571 ESTL.attribute14,
2572 ESTL.attribute15,
2573 ESTL.override_charge_flag
2574 FROM CSD_REPAIR_ESTIMATE ESTH,
2575 CSD_REPAIRS CR, -- swai: bug 4618500 (FP of 4580845)
2576 CSD_REPAIR_ESTIMATE_LINES ESTL
2577 -- swai: bug 4618500 (FP of 4580845)
2578 -- Join with table CSD_REPAIRS added
2579 -- We should not import the line from estimate to Actuals until the lines are accepted (i.e. approved)
2580 -- if the flag Estimate Approval Required flag is checked. (This would make the behavior consistent with 1159)
2581 -- Modified the query to achieve following:
2582 -- (1)If Estimate Approval Required flag is checked and status of the estimate is accepted then only
2583 -- import estimate lines to Actuals.
2584 -- (2)If Estimate Approval Required flag unchecked then do not restrict lines from import.
2585 WHERE CR.repair_line_id = p_repair_line_id
2586 AND ( ( nvl(CR.approval_required_flag,'N') ='Y' and nvl(CR.approval_status,'X')= 'A' )
2587 OR
2588 ( nvl(CR.approval_required_flag,'N') ='N' )
2589 )
2590 AND ESTH.repair_line_id = CR.repair_line_id
2591 -- end swai: bug 4618500 (FP of 4580845)
2592 AND ESTL.repair_estimate_id = ESTH.repair_estimate_id
2593 AND NOT EXISTS
2594 (
2595 SELECT 'EXISTS'
2596 FROM CSD_REPAIR_ACTUAL_LINES ACTL
2597 WHERE ACTL.actual_source_code = G_ACTUAL_SOURCE_CODE_ESTIMATE
2598 AND ACTL.actual_source_id = ESTL.repair_estimate_line_id
2599 )
2600 order by ESTL.estimate_detail_id ; /* nnadig,fix for bug#8219894 */
2601
2602 BEGIN
2603
2604 -- Standard start of API Savepoint
2605 Savepoint Import_Actuals_Estimate_sp;
2606
2607 -- Standard call to check for call compatibility.
2608 IF NOT FND_API.Compatible_API_Call (lc_api_version,
2609 p_api_version,
2610 lc_api_name ,
2611 G_PKG_NAME )
2612 THEN
2613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2614 END IF;
2615
2616 -- Initialize message list if p_init_msg_list is set to TRUE.
2617 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2618 FND_MSG_PUB.initialize;
2619 END IF;
2620
2621 -- logging
2622 if (lc_proc_level >= lc_debug_level) then
2623 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
2624 'Entering CSD_REPAIR_ACTUAL_PROCESS_PVT.import_actuals_from_estimate');
2625 end if;
2626 -- log parameters
2627 if (lc_stat_level >= lc_debug_level) then
2628 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
2629 'p_api_version: ' || p_api_version);
2630 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
2631 'p_commit: ' || p_commit);
2632 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
2633 'p_init_msg_list: ' || p_init_msg_list);
2634 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
2635 'p_validation_level: ' || p_validation_level);
2636 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
2637 'p_repair_line_id: ' || p_repair_line_id);
2638 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
2639 'p_repair_actual_id: ' || p_repair_actual_id);
2640 end if;
2641
2642 -- Initialize API return status to success
2643 x_return_status := FND_API.G_RET_STS_SUCCESS;
2644
2645 --
2646 -- Begin API Body
2647 --
2648
2649 -- Initialzing the warning flag.
2650 x_warning_flag := FND_API.G_FALSE;
2651
2652 -- Validate mandatory input parameters.
2653 if (lc_proc_level >= lc_debug_level) then
2654 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2655 'Calling CSD_PROCESS_UTIL.Check_Reqd_Param for p_repair_line_id');
2656 end if;
2657 CSD_PROCESS_UTIL.Check_Reqd_Param
2658 ( p_param_value => p_repair_line_id,
2659 p_param_name => 'REPAIR_LINE_ID',
2660 p_api_name => lc_api_name);
2661
2662 if (lc_stat_level >= lc_debug_level) then
2663 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2664 'Done checking required params');
2665 end if;
2666
2667 -- Before we start the process of copying the
2668 -- lines, we purge any existing error messages for the
2669 -- Module ACT (source entity ESTIMATE).
2670 if (lc_proc_level >= lc_debug_level) then
2671 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2672 'Calling CSD_GEN_ERRMSGS_PVT.purge_entity_msgs');
2673 end if;
2674 CSD_GEN_ERRMSGS_PVT.purge_entity_msgs(
2675 p_api_version => 1.0,
2676 -- p_commit => FND_API.G_TRUE,
2677 -- p_init_msg_list => FND_API.G_FALSE,
2678 -- p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2679 p_module_code => G_MSG_MODULE_CODE_ACT,
2680 p_source_entity_id1 => p_repair_line_id,
2681 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_ESTIMATE,
2682 p_source_entity_id2 => NULL, -- Since we want to delete all messages.
2683 x_return_status => x_return_status,
2684 x_msg_count => x_msg_count,
2685 x_msg_data => x_msg_data
2686 );
2687 if (lc_proc_level >= lc_debug_level) then
2688 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2689 'Returned from CSD_GEN_ERRMSGS_PVT.purge_entity_msgs');
2690 end if;
2691
2692 -- Stall the process if we were unable to purge
2693 -- the older messages.
2694 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2695 RAISE FND_API.G_EXC_ERROR;
2696 END IF;
2697
2698 --bug#13627518 --bug#14014723
2699 -- swai: bug 7119695
2700 /* Get_Default_Third_Party_Info (p_repair_line_id => p_repair_line_id,
2701 x_bill_to_account_id => l_bill_to_account_id,
2702 x_bill_to_party_id => l_bill_to_party_id,
2703 x_bill_to_party_site_id => l_bill_to_party_site_id,
2704 x_ship_to_account_id => l_ship_to_account_id,
2705 x_ship_to_party_id => l_ship_to_party_id,
2706 x_ship_to_party_site_id => l_ship_to_party_site_id);
2707 */
2708 --bug#13627518 --bug#14014723
2709
2710
2711 -- For all the estimate lines in csd_repair_estimate_lines
2712 -- table (for the given Repair Order) import into repair actual lines.
2713 -- LOOP
2714 if (lc_stat_level >= lc_debug_level) then
2715 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2716 'Begin loop through c_valid_estimate_lines');
2717 end if;
2718 FOR estimate_line_rec IN c_valid_estimate_lines
2719 LOOP
2720
2721 -- savepoint for the current record.
2722 Savepoint current_actual_line_sp;
2723
2724 -- Make the estimate detail id NULL, for each iteration.
2725 l_estimate_detail_id := NULL;
2726 l_skip_curr_row := FALSE;
2727
2728 -- Increment the total count.
2729 l_est_line_total_count := l_est_line_total_count + 1;
2730
2731 BEGIN
2732 -- Call the Charges API to make an 'Actual' charge line
2733 -- copy of the 'Estimate' charge line.
2734 if (lc_proc_level >= lc_debug_level) then
2735 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2736 'Calling CS_Charge_Details_PUB.copy_estimate for estimate_detail_id=' || estimate_line_rec.estimate_detail_id);
2737 end if;
2738 CS_Charge_Details_PUB.copy_estimate(
2739 p_api_version => 1.0,
2740 p_init_msg_list => p_init_msg_list,
2741 p_commit => FND_API.G_FALSE,
2742 p_transaction_control => FND_API.G_TRUE,
2743 p_estimate_detail_id => estimate_line_rec.estimate_detail_id,
2744 x_estimate_detail_id => l_estimate_detail_id,
2745 x_return_status => x_return_status,
2746 x_msg_count => x_msg_count,
2747 x_msg_data => x_msg_data
2748 );
2749
2750 if (lc_proc_level >= lc_debug_level) then
2751 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2752 'Returned from CS_Charge_Details_PUB.copy_estimate');
2753 end if;
2754 if (lc_stat_level >= lc_debug_level) then
2755 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2756 'x_return_status = ' || x_return_status);
2757 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2758 'l_estimate_detail_id = ' || l_estimate_detail_id);
2759 end if;
2760
2761 -- Throw an error if the API returned an error.
2762 -- We do not stall the process if we find an error in
2763 -- copying the charge line. We continue processing of
2764 -- other lines. We just skip the current row.
2765 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2766 RAISE FND_API.G_EXC_ERROR;
2767 END IF;
2768
2769 IF (l_estimate_detail_id IS NULL) THEN
2770 if (lc_proc_level >= lc_debug_level) then
2771 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2772 'Unable to copy the Estimate charge line into Actual charge line. Charges API returned NULL for the Estimate Detail identifier.');
2773 end if;
2774 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_COPY_CHRG_LINE_FAIL');
2775 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2776 RAISE FND_API.G_EXC_ERROR;
2777 END IF;
2778
2779 EXCEPTION
2780 WHEN FND_API.G_EXC_ERROR THEN
2781 l_skip_curr_row := TRUE;
2782
2783 WHEN OTHERS THEN
2784 l_skip_curr_row := TRUE;
2785 if (lc_proc_level >= lc_debug_level) then
2786 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2787 'Encountered an unknown error while copying an estimate charge line to an actual charge line. SQLCODE = ' || SQLCODE || ', SQLERRM = ' || SQLERRM );
2788 end if;
2789 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_EST_IMPORT_ERR');
2790 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
2791 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
2792 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2793 END;
2794
2795 IF NOT l_skip_curr_row THEN
2796
2797 if (lc_stat_level >= lc_debug_level) then
2798 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2799 'l_skip_curr_row = false');
2800 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2801 'l_curr_actual_line_rec.ESTIMATE_DETAIL_ID = ' || l_estimate_detail_id);
2802 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2803 'l_curr_actual_line_rec.REPAIR_ACTUAL_ID = ' || p_repair_actual_id);
2804 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2805 'l_curr_actual_line_rec.REPAIR_LINE_ID = ' || p_repair_line_id);
2806 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2807 'l_curr_actual_line_rec.OVERRIDE_CHARGE_FLAG = ' || estimate_line_rec.override_charge_flag);
2808 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2809 'l_curr_actual_line_rec.ACTUAL_SOURCE_CODE = ' || G_ACTUAL_SOURCE_CODE_ESTIMATE);
2810 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2811 'l_curr_actual_line_rec.ACTUAL_SOURCE_ID = ' || estimate_line_rec.repair_estimate_line_id);
2812 end if;
2813
2814 -- If the copying of charge line was successful then
2815 -- we initialize the actual line record with relevant values.
2816 l_curr_actual_line_rec.REPAIR_ACTUAL_LINE_ID := null; -- clear the primary key
2817 l_curr_actual_line_rec.ESTIMATE_DETAIL_ID := l_estimate_detail_id;
2818 l_curr_actual_line_rec.REPAIR_ACTUAL_ID := p_repair_actual_id;
2819 l_curr_actual_line_rec.REPAIR_LINE_ID := p_repair_line_id;
2820
2821 --l_curr_actual_line_rec.OBJECT_VERSION_NUMBER
2822 --l_curr_actual_line_rec.CREATED_BY
2823 --l_curr_actual_line_rec.CREATION_DATE
2824 --l_curr_actual_line_rec.LAST_UPDATED_BY
2825 --l_curr_actual_line_rec.LAST_UPDATE_DATE
2826 --l_curr_actual_line_rec.LAST_UPDATE_LOGIN
2827
2828 -- In 11.5.10 we don't do Actual costing
2829 -- l_curr_actual_line_rec.ITEM_COST := estimate_line_rec.item_cost;
2830 l_curr_actual_line_rec.JUSTIFICATION_NOTES := estimate_line_rec.justification_notes;
2831 l_curr_actual_line_rec.RESOURCE_ID := estimate_line_rec.resource_id;
2832 l_curr_actual_line_rec.OVERRIDE_CHARGE_FLAG:= estimate_line_rec.override_charge_flag;
2833 l_curr_actual_line_rec.ACTUAL_SOURCE_CODE := G_ACTUAL_SOURCE_CODE_ESTIMATE;
2834 l_curr_actual_line_rec.ACTUAL_SOURCE_ID := estimate_line_rec.repair_estimate_line_id;
2835 l_curr_actual_line_rec.ATTRIBUTE_CATEGORY := estimate_line_rec.context;
2836 l_curr_actual_line_rec.ATTRIBUTE1 := estimate_line_rec.attribute1;
2837 l_curr_actual_line_rec.ATTRIBUTE2 := estimate_line_rec.attribute2;
2838 l_curr_actual_line_rec.ATTRIBUTE3 := estimate_line_rec.attribute3;
2839 l_curr_actual_line_rec.ATTRIBUTE4 := estimate_line_rec.attribute4;
2840 l_curr_actual_line_rec.ATTRIBUTE5 := estimate_line_rec.attribute5;
2841 l_curr_actual_line_rec.ATTRIBUTE6 := estimate_line_rec.attribute6;
2842 l_curr_actual_line_rec.ATTRIBUTE7 := estimate_line_rec.attribute7;
2843 l_curr_actual_line_rec.ATTRIBUTE8 := estimate_line_rec.attribute8;
2844 l_curr_actual_line_rec.ATTRIBUTE9 := estimate_line_rec.attribute9;
2845 l_curr_actual_line_rec.ATTRIBUTE10 := estimate_line_rec.attribute10;
2846 l_curr_actual_line_rec.ATTRIBUTE11 := estimate_line_rec.attribute11;
2847 l_curr_actual_line_rec.ATTRIBUTE12 := estimate_line_rec.attribute12;
2848 l_curr_actual_line_rec.ATTRIBUTE13 := estimate_line_rec.attribute13;
2849 l_curr_actual_line_rec.ATTRIBUTE14 := estimate_line_rec.attribute14;
2850 l_curr_actual_line_rec.ATTRIBUTE15 := estimate_line_rec.attribute15;
2851
2852 -- The following information is not stored in Estimate
2853 -- lines tables. Hence, it is not applicable for the procedure.
2854 -- The lines below are presented only for completeness sake.
2855 --l_curr_actual_line_rec.LOCATOR_ID
2856 --l_curr_actual_line_rec.LOC_SEGMENT1
2857 --l_curr_actual_line_rec.LOC_SEGMENT2
2858 --l_curr_actual_line_rec.LOC_SEGMENT3
2859 --l_curr_actual_line_rec.LOC_SEGMENT4
2860 --l_curr_actual_line_rec.LOC_SEGMENT5
2861 --l_curr_actual_line_rec.LOC_SEGMENT6
2862 --l_curr_actual_line_rec.LOC_SEGMENT7
2863 --l_curr_actual_line_rec.LOC_SEGMENT8
2864 --l_curr_actual_line_rec.LOC_SEGMENT9
2865 --l_curr_actual_line_rec.LOC_SEGMENT10
2866 --l_curr_actual_line_rec.LOC_SEGMENT11
2867 --l_curr_actual_line_rec.LOC_SEGMENT12
2868 --l_curr_actual_line_rec.LOC_SEGMENT13
2869 --l_curr_actual_line_rec.LOC_SEGMENT14
2870 --l_curr_actual_line_rec.LOC_SEGMENT15
2871 --l_curr_actual_line_rec.LOC_SEGMENT16
2872 --l_curr_actual_line_rec.LOC_SEGMENT17
2873 --l_curr_actual_line_rec.LOC_SEGMENT18
2874 --l_curr_actual_line_rec.LOC_SEGMENT19
2875 --l_curr_actual_line_rec.LOC_SEGMENT20
2876
2877
2878 -- We now create a corresponding Repair Actual line.
2879 BEGIN
2880
2881 if (lc_proc_level >= lc_debug_level) then
2882 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2883 'Calling CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines');
2884 end if;
2885 CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines(
2886 p_api_version => 1.0,
2887 p_commit => p_commit,
2888 p_init_msg_list => p_init_msg_list,
2889 p_validation_level => p_validation_level,
2890 px_csd_actual_lines_rec => l_curr_actual_line_rec,
2891 px_charges_rec => l_charge_line_rec,
2892 x_return_status => x_return_status,
2893 x_msg_count => x_msg_count,
2894 x_msg_data => x_msg_data
2895 );
2896 if (lc_proc_level >= lc_debug_level) then
2897 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2898 'Returned from CSD_REPAIR_ACTUAL_LINES_PVT.create_repair_actual_lines');
2899 end if;
2900
2901 -- Throw an error if the API returned an error.
2902 -- We do not stall the process if we find an error in
2903 -- copying the charge line. We continue processing of
2904 -- other lines. We just skip the current row.
2905 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2906 RAISE FND_API.G_EXC_ERROR;
2907 END IF;
2908
2909 IF (l_curr_actual_line_rec.repair_actual_line_id IS NULL) THEN
2910 if (lc_proc_level >= lc_debug_level) then
2911 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2912 'Unable to create a repair actual line. Create API returned NULL for the repair actual line identifier.');
2913 end if;
2914 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_NULL_ACTUAL_ID');
2915 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2916 RAISE FND_API.G_EXC_ERROR;
2917 END IF;
2918
2919 EXCEPTION
2920 WHEN FND_API.G_EXC_ERROR THEN
2921 l_skip_curr_row := TRUE;
2922 if (lc_proc_level >= lc_debug_level) then
2923 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2924 'Encountered an EXEC error while creating a repair actual line.');
2925 end if;
2926
2927 WHEN OTHERS THEN
2928 l_skip_curr_row := TRUE;
2929 if (lc_proc_level >= lc_debug_level) then
2930 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2931 'Encountered OTHERS error while creating a repair actual line.');
2932 end if;
2933 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_ERROR_ACTUAL_LINE');
2934 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
2935 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
2936 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2937
2938 END;
2939
2940 --bug#13627518 --bug#14014723
2941 -- swai: bug 7119695 - 3rd party billing, need to set account info
2942 -- update actual line to have default bill-to and ship-to.
2943
2944 --deleted the code here in between
2945
2946 -- end swai: bug 7119695
2947 --bug#13627518 --bug#14014723
2948
2949 END IF; -- IF NOT l_skip_curr_row
2950
2951 IF l_skip_curr_row THEN
2952 if (lc_stat_level >= lc_debug_level) then
2953 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2954 'l_skip_curr_row = true');
2955 end if;
2956
2957 -- we rollback any updates/inserts for the current
2958 -- record and set the warning flag to TRUE.
2959 ROLLBACK TO current_actual_line_sp;
2960 x_warning_flag := FND_API.G_TRUE;
2961
2962 -- Increment the failed count.
2963 l_est_line_failed_count := l_est_line_failed_count + 1;
2964
2965 -- Save warnings/errors that may be the stack
2966 if (lc_proc_level >= lc_debug_level) then
2967 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2968 'Calling CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
2969 end if;
2970 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs(
2971 p_api_version => 1.0,
2972 -- p_commit => FND_API.G_TRUE,
2973 -- p_init_msg_list => FND_API.G_FALSE,
2974 -- p_validation_level => p_validation_level,
2975 p_module_code => G_MSG_MODULE_CODE_ACT,
2976 p_source_entity_id1 => p_repair_line_id,
2977 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_ESTIMATE,
2978 p_source_entity_id2 => estimate_line_rec.repair_estimate_line_id,
2979 x_return_status => x_return_status,
2980 x_msg_count => x_msg_count,
2981 x_msg_data => x_msg_data
2982 );
2983 if (lc_proc_level >= lc_debug_level) then
2984 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2985 'Returned from CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
2986 end if;
2987
2988 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2989 if (lc_proc_level >= lc_debug_level) then
2990 FND_LOG.STRING(lc_proc_level, lc_mod_name,
2991 'Unable to save messages using the generic logging utility.');
2992 end if;
2993 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_GENERIC_SAVE_FAILED');
2994 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
2995 RAISE FND_API.G_EXC_ERROR;
2996 END IF;
2997
2998 END IF;
2999
3000 END LOOP;
3001
3002 x_warning_flag := FND_API.G_TRUE;
3003
3004 -- If no eligible estimate lines found for import.
3005 IF( l_est_line_total_count <= 0 ) THEN
3006 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_EST_INELIGIBLE');
3007 -- No eligible Estimate lines found for import into Actuals.
3008 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_INFORMATION_MSG);
3009 ELSE -- Attempt to import estimate lines was made.
3010
3011 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_ACT_EST_SUMMARY');
3012 -- Import of Estimate lines into Actuals has completed. Failed to import
3013 -- FAILED_COUNT lines. PASS_COUNT lines were imported successfully.
3014 FND_MESSAGE.set_token('FAILED_COUNT', l_est_line_failed_count);
3015 FND_MESSAGE.set_token('PASS_COUNT',(l_est_line_total_count - l_est_line_failed_count));
3016 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_INFORMATION_MSG);
3017 END IF;
3018
3019 if (lc_proc_level >= lc_debug_level) then
3020 FND_LOG.STRING(lc_proc_level, lc_mod_name,
3021 'Calling procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
3022 end if;
3023
3024 CSD_GEN_ERRMSGS_PVT.save_fnd_msgs(
3025 p_api_version => 1.0,
3026 -- p_commit => FND_API.G_TRUE,
3027 -- p_init_msg_list => FND_API.G_FALSE,
3028 -- p_validation_level => p_validation_level,
3029 p_module_code => G_MSG_MODULE_CODE_ACT,
3030 p_source_entity_id1 => p_repair_line_id,
3031 p_source_entity_type_code => G_ACTUAL_MSG_ENTITY_ESTIMATE,
3032 p_source_entity_id2 => 0, -- We not have any Estimate id in this case.
3033 x_return_status => x_return_status,
3034 x_msg_count => x_msg_count,
3035 x_msg_data => x_msg_data );
3036
3037 if (lc_proc_level >= lc_debug_level) then
3038 FND_LOG.STRING(lc_proc_level, lc_mod_name,
3039 'Returned from procedure CSD_GEN_ERRMSGS_PVT.save_fnd_msgs');
3040 end if;
3041
3042 -- If we are unable to log messages then we
3043 -- throw an error.
3044 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3045 if (lc_proc_level >= lc_debug_level) then
3046 FND_LOG.STRING(lc_proc_level, lc_mod_name,
3047 'Unable to save messages using the generic logging utility.');
3048 end if;
3049 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_GENERIC_SAVE_FAILED');
3050 -- Unable to save messages using the generic logging utility.
3051 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
3052 RAISE FND_API.G_EXC_ERROR;
3053 END IF;
3054
3055 -- Standard check of p_commit.
3056 IF FND_API.To_Boolean( p_commit ) THEN
3057 COMMIT WORK;
3058 END IF;
3059
3060 -- logging
3061 if (lc_proc_level >= lc_debug_level) then
3062 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
3063 'Leaving CSD_REPAIR_ACTUAL_PROCESS_PVT.import_actuals_from_estimate');
3064 end if;
3065
3066 EXCEPTION
3067
3068 WHEN FND_API.G_EXC_ERROR THEN
3069 x_return_status := FND_API.G_RET_STS_ERROR ;
3070 ROLLBACK TO Import_Actuals_Estimate_sp;
3071 FND_MSG_PUB.Count_And_Get
3072 (p_count => x_msg_count,
3073 p_data => x_msg_data );
3074
3075 -- save message in debug log
3076 IF (lc_excep_level >= lc_debug_level) THEN
3077 FND_LOG.STRING(lc_excep_level, lc_mod_name,
3078 'EXC_ERROR['||x_msg_data||']');
3079 END IF;
3080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3082 ROLLBACK TO Import_Actuals_Estimate_sp;
3083 FND_MSG_PUB.Count_And_Get
3084 ( p_count => x_msg_count,
3085 p_data => x_msg_data );
3086 -- save message in debug log
3087 IF (lc_excep_level >= lc_debug_level) THEN
3088 FND_LOG.STRING(lc_excep_level, lc_mod_name,
3089 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
3090 END IF;
3091
3092 WHEN OTHERS THEN
3093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3094 ROLLBACK TO Import_Actuals_Estimate_sp;
3095 IF FND_MSG_PUB.Check_Msg_Level
3096 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3097 THEN
3098 FND_MSG_PUB.Add_Exc_Msg
3099 (G_PKG_NAME ,
3100 lc_api_name );
3101 END IF;
3102 FND_MESSAGE.SET_NAME('CSD', 'CSD_ACT_EST_IMPORT_ERR');
3103 -- Encountered an unknown error while copying an estimate charge line to an actual charge line. SQLCODE = $SQLCODE, SQLERRM = $SQLERRM.
3104 FND_MESSAGE.SET_TOKEN('SQLCODE', SQLCODE);
3105 FND_MESSAGE.SET_TOKEN('SQLERRM', SQLERRM);
3106 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_ERROR_MSG);
3107 FND_MSG_PUB.Count_And_Get
3108 (p_count => x_msg_count,
3109 p_data => x_msg_data );
3110 -- save message in debug log
3111 IF (lc_excep_level >= lc_debug_level) THEN
3112 -- create a seeded message
3113 FND_LOG.STRING(lc_excep_level, lc_mod_name,
3114 'WHEN OTEHRS THEN. SQL Message['||sqlerrm||']' );
3115 END IF;
3116
3117 END Import_Actuals_From_Estimate;
3118
3119 /*--------------------------------------------------------------------*/
3120 /* procedure name: Convert_MLE_To_Actuals */
3121 /* description : Procedure is used to convert table of records from */
3122 /* MLE table format to repair actual lines format. */
3123 /* */
3124 /* Called from : Import_Actuals_From_Wip */
3125 /* */
3126 /*--------------------------------------------------------------------*/
3127
3128 PROCEDURE Convert_MLE_To_Actuals( p_MLE_lines_tbl IN CSD_CHARGE_LINE_UTIL.MLE_LINES_TBL_TYPE,
3129 p_repair_line_id IN NUMBER,
3130 p_repair_actual_id IN NUMBER,
3131 x_actual_lines_tbl IN OUT NOCOPY CSD_REPAIR_ACTUAL_LINES_PVT.CSD_ACTUAL_LINES_TBL_TYPE ) IS
3132 l_count NUMBER := 0;
3133
3134 BEGIN
3135 l_count := p_MLE_lines_tbl.COUNT;
3136 --DBMS_OUTPUT.put_line( 'The count inside the convert proc is '
3137 -- || l_count );
3138
3139 FOR i IN 1..l_count LOOP
3140 x_actual_lines_tbl( i ).OBJECT_VERSION_NUMBER := 1;
3141 x_actual_lines_tbl( i ).REPAIR_ACTUAL_ID := p_repair_actual_id;
3142 x_actual_lines_tbl( i ).REPAIR_LINE_ID := p_repair_line_id;
3143 x_actual_lines_tbl( i ).ITEM_COST := p_MLE_lines_tbl( i ).item_cost;
3144 x_actual_lines_tbl( i ).JUSTIFICATION_NOTES := NULL;
3145 x_actual_lines_tbl( i ).OVERRIDE_CHARGE_FLAG := p_MLE_lines_tbl( i ).override_charge_flag;
3146 -- x_actual_lines_tbl(i).transaction_type_id := p_MLE_lines_tbl(i).transaction_type_id;
3147 x_actual_lines_tbl( i ).ACTUAL_SOURCE_CODE := p_MLE_lines_tbl( i ).source_code;
3148 x_actual_lines_tbl( i ).ACTUAL_SOURCE_ID := p_MLE_lines_tbl( i ).source_id1;
3149
3150 -- Added for ER 3607765, vkjain.
3151 x_actual_lines_tbl( i ).RESOURCE_ID := p_MLE_lines_tbl( i ).resource_id;
3152 END LOOP;
3153
3154 END Convert_MLE_To_Actuals;
3155
3156
3157 -- swai: bug 7119695 and 7119691
3158 /*--------------------------------------------------------------------*/
3159 /* procedure name: Get_Default_Third_Party_Info */
3160 /* description : Procedure is used to get the default bill and ship */
3161 /* information from the repair actual header. If no */
3162 /* header is found, defaults are gotten from the SR */
3163 /* */
3164 /* Called from : Get_Default_Third_Party_Info */
3165 /* */
3166 /*--------------------------------------------------------------------*/
3167
3168 PROCEDURE Get_Default_Third_Party_Info (p_repair_line_id IN NUMBER,
3169 x_bill_to_account_id OUT NOCOPY NUMBER,
3170 x_bill_to_party_id OUT NOCOPY NUMBER,
3171 x_bill_to_party_site_id OUT NOCOPY NUMBER,
3172 x_ship_to_account_id OUT NOCOPY NUMBER,
3173 x_ship_to_party_id OUT NOCOPY NUMBER,
3174 x_ship_to_party_site_id OUT NOCOPY NUMBER ) IS
3175
3176 -- variables --
3177 l_incident_id NUMBER;
3178 l_org_id NUMBER;
3179
3180 -- cursors --
3181 CURSOR c_primary_account_address(p_party_id NUMBER, p_account_id NUMBER, p_org_id NUMBER, p_site_use_type VARCHAR2)
3182 IS
3183 select distinct
3184 hp.party_site_id
3185 from hz_party_sites_v hp,
3186 hz_parties hz,
3187 hz_cust_acct_sites_all hca,
3188 hz_cust_site_uses_all hcsu
3189 where hcsu.site_use_code = p_site_use_type
3190 and hp.status = 'A'
3191 and hcsu.status = 'A'
3192 and hp.party_id = hz.party_id
3193 and hp.party_id = p_party_id
3194 and hca.party_site_id = hp.party_site_id
3195 and hca.cust_account_id = p_account_id
3196 and hcsu.cust_acct_site_id = hca.cust_acct_site_id
3197 and hca.org_id = p_org_id
3198 and hcsu.primary_flag = 'Y'
3199 and rownum = 1;
3200
3201 CURSOR c_default_bill_to(p_repair_line_id NUMBER)
3202 IS
3203 select act.bill_to_account_id,
3204 act.bill_to_party_id,
3205 act.bill_to_party_site_id,
3206 csd.incident_id
3207 from csd_repair_actuals act,
3208 csd_repairs csd
3209 where csd.repair_line_id = p_repair_line_id
3210 and act.repair_line_id = csd.repair_line_id;
3211
3212 CURSOR c_sr_bill_to_ship_to(p_repair_line_id NUMBER)
3213 IS
3214 select cs.account_id,
3215 cs.bill_to_party_id,
3216 cs.bill_to_site_id,
3217 cs.ship_to_site_id
3218 from csd_repairs csd,
3219 cs_incidents_all_b cs
3220 where csd.repair_line_id = p_repair_line_id
3221 and csd.incident_id = cs.incident_id;
3222
3223 BEGIN
3224
3225 -- get default bill-to
3226 OPEN c_default_bill_to (p_repair_line_id);
3227 FETCH c_default_bill_to
3228 INTO x_bill_to_account_id,
3229 x_bill_to_party_id,
3230 x_bill_to_party_site_id,
3231 l_incident_id;
3232 IF c_default_bill_to%ISOPEN THEN
3233 CLOSE c_default_bill_to;
3234 END IF;
3235
3236 IF ((x_bill_to_account_id is not null) AND (x_bill_to_party_site_id is not null)) THEN
3237 -- get the default account primary ship-to
3238 l_org_id := CSD_PROCESS_UTIL.get_org_id( l_incident_id );
3239 x_ship_to_account_id := x_bill_to_account_id;
3240 x_ship_to_party_id := x_bill_to_party_id;
3241 OPEN c_primary_account_address (x_ship_to_party_id,
3242 x_ship_to_account_id,
3243 l_org_id,
3244 'SHIP_TO');
3245 FETCH c_primary_account_address INTO x_ship_to_party_site_id;
3246 IF c_primary_account_address%ISOPEN THEN
3247 CLOSE c_primary_account_address;
3248 END IF;
3249 ELSE
3250 -- get the bill-to and ship to from the SR.
3251 OPEN c_sr_bill_to_ship_to (p_repair_line_id);
3252 FETCH c_sr_bill_to_ship_to
3253 INTO x_bill_to_account_id,
3254 x_bill_to_party_id,
3255 x_bill_to_party_site_id,
3256 x_ship_to_party_site_id;
3257 IF c_sr_bill_to_ship_to%ISOPEN THEN
3258 CLOSE c_sr_bill_to_ship_to;
3259 END IF;
3260 x_ship_to_account_id := x_bill_to_account_id;
3261 x_ship_to_party_id := x_bill_to_party_id;
3262 END IF;
3263
3264 END Get_Default_Third_Party_Info;
3265
3266 End CSD_REPAIR_ACTUAL_PROCESS_PVT;
[Home] [Help]