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