[Home] [Help]
1: Package BODY Csd_Repairs_Pvt AS
2: /* $Header: csdvdrab.pls 120.38.12020000.4 2013/03/30 00:14:11 yvchen ship $ */
3: --
4: -- Package name : CSD_REPAIRS_PVT
5: -- Purpose : This package contains the private APIs for creating,
1: Package BODY Csd_Repairs_Pvt AS
2: /* $Header: csdvdrab.pls 120.38.12020000.4 2013/03/30 00:14:11 yvchen ship $ */
3: --
4: -- Package name : CSD_REPAIRS_PVT
5: -- Purpose : This package contains the private APIs for creating,
6: -- updating, deleting repair orders. Access is
7: -- restricted to Oracle Depot Rapair Internal Development.
8: -- History :
39: -- validate ib_ref_number when repair type is refurbished.
40: -- 115.45 05/19/05 vparvath Adding update_ro_Status API for R12 development.
41: -- 08/15/06 rfieldma Adding new error messages for update repair type and update repair status.
42: -- procedures update_repair_type, update_flow_status
43: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIRS_PVT';
44: G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvdrab.pls';
45: g_debug NUMBER := Csd_Gen_Utility_Pvt.g_debug_level;
46: --
47:
65: P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
66: P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
67: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
68: p_REPAIR_LINE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
69: P_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type,
70: X_REPAIR_LINE_ID OUT NOCOPY NUMBER,
71: X_REPAIR_NUMBER OUT NOCOPY VARCHAR2,
72: X_Return_Status OUT NOCOPY VARCHAR2,
73: X_Msg_Count OUT NOCOPY NUMBER,
92: l_date_closed DATE;
93: l_resolve_by_date DATE; --rfieldma: 5355051
94: l_business_process_id NUMBER; -- rfieldma: 5355051
95: l_severity_id NUMBER; -- rfieldma: 5355051
96: l_REPLN_Rec Csd_Repairs_Pub.REPLN_Rec_Type; -- swai bug 7657379
97:
98: -- bug#7043215, subhat
99: -- new DFF value rec.
100: x_dff_value_rec CSD_REPAIRS_UTIL.DEF_Rec_Type;
96: l_REPLN_Rec Csd_Repairs_Pub.REPLN_Rec_Type; -- swai bug 7657379
97:
98: -- bug#7043215, subhat
99: -- new DFF value rec.
100: x_dff_value_rec CSD_REPAIRS_UTIL.DEF_Rec_Type;
101:
102: -- subhat, resolve by date calc changes.
103: l_resolve_by_dt_calc_at VARCHAR2(30);
104:
108: l_inventory_item_name VARCHAR2(40);
109: l_repair_type_name VARCHAR2(80);
110: --
111: CURSOR C1 IS
112: SELECT CSD_REPAIRS_S1.NEXTVAL
113: FROM sys.dual;
114: --
115: CURSOR C2 IS
116: SELECT CSD_REPAIRS_S2.NEXTVAL
112: SELECT CSD_REPAIRS_S1.NEXTVAL
113: FROM sys.dual;
114: --
115: CURSOR C2 IS
116: SELECT CSD_REPAIRS_S2.NEXTVAL
117: FROM sys.dual;
118: --
119: CURSOR get_draft_status_details IS
120: SELECT FS_B.flow_status_id,
145:
146: -- bug#13698799 auto create ro, parent ro
147: CURSOR c_get_repair_number (p_repair_line_id NUMBER) IS
148: Select repair_number
149: from csd_repairs
150: where repair_line_id = p_repair_line_id;
151:
152: Cursor c_get_item_name (p_inventory_item_id number,
153: p_organization_id number) is
190: -- swai: bug 7657379
191: -- Default fields in repln_rec from defauling rules before
192: -- checking required params.
193: l_REPLN_Rec := p_REPLN_Rec;
194: Csd_Repairs_Util.DEFAULT_RO_ATTRS_FROM_RULE
195: ( p_api_version => 1.0,
196: p_commit => fnd_api.g_false,
197: p_init_msg_list => fnd_api.g_false,
198: px_repln_rec => l_REPLN_Rec,
203: -- Note: From this point on, P_REPLN_REC has been replaced with l_REPLN_Rec
204:
205: --
206: -- Check for required parameters
207: Csd_Repairs_Util.check_reqd_param
208: (p_param_value => l_REPLN_Rec.incident_id,
209: p_param_name => 'P_REPLN_REC.INCIDENT_ID',
210: p_api_name => l_api_name
211: );
208: (p_param_value => l_REPLN_Rec.incident_id,
209: p_param_name => 'P_REPLN_REC.INCIDENT_ID',
210: p_api_name => l_api_name
211: );
212: Csd_Repairs_Util.check_reqd_param
213: (p_param_value => l_REPLN_Rec.inventory_item_id,
214: p_param_name => 'P_REPLN_REC.INVENTORY_ITEM_ID',
215: p_api_name => l_api_name
216: );
214: p_param_name => 'P_REPLN_REC.INVENTORY_ITEM_ID',
215: p_api_name => l_api_name
216: );
217:
218: Csd_Repairs_Util.check_reqd_param
219: (p_param_value => l_REPLN_Rec.unit_of_measure,
220: p_param_name => 'P_REPLN_REC.UNIT_OF_MEASURE',
221: p_api_name => l_api_name
222: );
219: (p_param_value => l_REPLN_Rec.unit_of_measure,
220: p_param_name => 'P_REPLN_REC.UNIT_OF_MEASURE',
221: p_api_name => l_api_name
222: );
223: Csd_Repairs_Util.check_reqd_param
224: (p_param_value => l_REPLN_Rec.quantity,
225: p_param_name => 'P_REPLN_REC.QUANTITY',
226: p_api_name => l_api_name
227: );
229: /* R12 Flex Flow change, vkjain.
230: -- Status is no longer mandatory.
231: -- One can either pass flow_status_id
232: -- or just status.
233: CSD_REPAIRS_UTIL.check_reqd_param
234: (p_param_value => l_REPLN_Rec.status,
235: p_param_name => 'P_REPLN_REC.STATUS',
236: p_api_name => l_api_name
237: );
236: p_api_name => l_api_name
237: );
238: */
239:
240: Csd_Repairs_Util.check_reqd_param
241: (p_param_value => l_REPLN_Rec.currency_code,
242: p_param_name => 'P_REPLN_REC.CURRENCY_CODE',
243: p_api_name => l_api_name
244: );
244: );
245:
246: -- Need to check if the status_reason_code is to be validated.
247:
248: Csd_Repairs_Util.check_reqd_param
249: (p_param_value => l_REPLN_Rec.approval_required_flag,
250: p_param_name => 'P_REPLN_REC.APPROVAL_REQUIRED_FLAG',
251: p_api_name => l_api_name
252: );
249: (p_param_value => l_REPLN_Rec.approval_required_flag,
250: p_param_name => 'P_REPLN_REC.APPROVAL_REQUIRED_FLAG',
251: p_api_name => l_api_name
252: );
253: Csd_Repairs_Util.check_reqd_param
254: (p_param_value => l_REPLN_Rec.repair_type_id,
255: p_param_name => 'P_REPLN_REC.REPAIR_TYPE_ID',
256: p_api_name => l_api_name
257: );
289:
290:
291: --
292: -- Invoke validation procedures
293: -- added a new out parameter which is of type CSD_REPAIRS_UTIL.DEF_Rec_Type
294: -- this collection holds the validated/derived DFF values (only in create mode)
295: -- subhat, FP bug#7242791
296: --
297: Validate_Repairs
423: INTO l_severity_id;
424: CLOSE c_get_severity_id;
425:
426: -- get resolve by date
427: csd_repairs_util.get_contract_resolve_by_date(
428: p_contract_line_id => l_REPLN_Rec.contract_line_id,
429: p_bus_proc_id => l_business_process_id,
430: p_severity_id => l_severity_id,
431: p_request_date => sysdate,
445: END IF; -- end contract line id is not null and not g_miss_num
446: END IF; -- end resolve by date = g_miss_date
447:
448: --
449: -- Invoke table handler(CSD_REPAIRS_PKG.Insert_Row)
450: --
451: Csd_Repairs_Pkg.Insert_Row(
452: px_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
453: p_REQUEST_ID => G_REQUEST_ID,
447:
448: --
449: -- Invoke table handler(CSD_REPAIRS_PKG.Insert_Row)
450: --
451: Csd_Repairs_Pkg.Insert_Row(
452: px_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
453: p_REQUEST_ID => G_REQUEST_ID,
454: p_PROGRAM_ID => G_PROGRAM_ID,
455: p_PROGRAM_APPLICATION_ID => G_PROG_APPL_ID,
582: x_REPAIR_NUMBER := l_REPAIR_NUMBER;
583:
584: -- START bug#13698799 auto create ro, parent ro
585: IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
586: Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, 'CSD.PLSQL.csd_repairs_pvt.create_repair_order',
587: 'Calling Repair History to log activity');
588: END IF;
589:
590: IF l_REPLN_Rec.parent_repair_line_id is not null and l_REPLN_Rec.parent_repair_line_id <> FND_API.G_MISS_NUM THEN
644: END IF;
645:
646: IF l_REPLN_Rec.parent_repair_line_id is not NULL and l_REPLN_Rec.parent_repair_line_id <> FND_API.G_MISS_NUM THEN
647: IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
648: Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, 'CSD.PLSQL.csd_repairs_pvt.create_repair_order',
649: 'Calling Repair History to log activity for parent RO');
650: END IF;
651:
652: -- log an activity on the newly created repair order
744: P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
745: P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
746: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
747: p_REPAIR_LINE_ID IN NUMBER,
748: P_REPLN_Rec IN OUT NOCOPY Csd_Repairs_Pub.REPLN_Rec_Type,
749: X_Return_Status OUT NOCOPY VARCHAR2,
750: X_Msg_Count OUT NOCOPY NUMBER,
751: X_Msg_Data OUT NOCOPY VARCHAR2
752: )
848: RETURN_ATTRIBUTE30,
849: waybill_no,
850: return_type_id,
851: PARENT_REPAIR_LINE_ID -- bug#13698799 auto create ro, parent ro
852: FROM CSD_REPAIRS
853: WHERE REPAIR_LINE_ID = p_REPAIR_LINE_ID
854: FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
855:
856: l_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Order';
854: FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
855:
856: l_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Order';
857: l_api_version_number CONSTANT NUMBER := 1.0;
858: l_OLD_REPLN_rec Csd_Repairs_Pub.REPLN_Rec_Type;
859: l_NEW_REPLN_rec Csd_Repairs_Pub.REPLN_Rec_Type := P_REPLN_Rec;
860: l_rowid ROWID;
861: l_repair_line_id NUMBER;
862: l_OBJECT_VERSION_NUMBER NUMBER;
855:
856: l_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Order';
857: l_api_version_number CONSTANT NUMBER := 1.0;
858: l_OLD_REPLN_rec Csd_Repairs_Pub.REPLN_Rec_Type;
859: l_NEW_REPLN_rec Csd_Repairs_Pub.REPLN_Rec_Type := P_REPLN_Rec;
860: l_rowid ROWID;
861: l_repair_line_id NUMBER;
862: l_OBJECT_VERSION_NUMBER NUMBER;
863:
862: l_OBJECT_VERSION_NUMBER NUMBER;
863:
864: -- bug#7242791, 12.1 FP, subhat
865: -- new out parameter for validate_repairs API.
866: x_dff_value_rec csd_repairs_util.def_rec_type;
867:
868: BEGIN
869:
870: -- Standard Start of API savepoint
878: THEN
879: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
880: END IF;
881: -- Check for required parameters
882: Csd_Repairs_Util.check_reqd_param
883: (p_param_value => p_repair_line_id,
884: p_param_name => 'P_REPAIR_LINE_ID',
885: p_api_name => l_api_name
886: );
1019: END IF;
1020: CLOSE C_Get_repairs;
1021:
1022: -- bug#12884352
1023: -- should use csd_repairs_pub.update_repair_type to update repair type, not this function
1024: IF P_REPLN_rec.repair_type_id is not NULL and P_REPLN_rec.repair_type_id <> Fnd_Api.G_MISS_NUM and l_OLD_REPLN_rec.repair_type_id <> P_REPLN_Rec.repair_type_id THEN
1025: IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1026: Fnd_Message.Set_Name('CSD', 'CSD_UPDATE_REPAIR_TYPE');
1027: Fnd_Msg_Pub.ADD;
1066:
1067: l_OBJECT_VERSION_NUMBER := p_REPLN_rec.OBJECT_VERSION_NUMBER + 1;
1068:
1069: --
1070: -- Invoke table handler(CSD_REPAIRS_PKG.Update_Row)
1071: --
1072: --
1073: Csd_Repairs_Pkg.Update_Row(
1074: p_REPAIR_LINE_ID => p_REPAIR_LINE_ID,
1069: --
1070: -- Invoke table handler(CSD_REPAIRS_PKG.Update_Row)
1071: --
1072: --
1073: Csd_Repairs_Pkg.Update_Row(
1074: p_REPAIR_LINE_ID => p_REPAIR_LINE_ID,
1075: p_REQUEST_ID => G_REQUEST_ID,
1076: p_PROGRAM_ID => G_PROGRAM_ID,
1077: p_PROGRAM_APPLICATION_ID => G_PROG_APPL_ID,
1278: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1279: --
1280: -- Api body
1281: --
1282: -- Invoke table handler(CSD_REPAIRS_PKG.Delete_Row)
1283: Csd_Repairs_Pkg.Delete_Row(
1284: p_REPAIR_LINE_ID => p_REPAIR_LINE_ID);
1285:
1286: -- End of API body
1279: --
1280: -- Api body
1281: --
1282: -- Invoke table handler(CSD_REPAIRS_PKG.Delete_Row)
1283: Csd_Repairs_Pkg.Delete_Row(
1284: p_REPAIR_LINE_ID => p_REPAIR_LINE_ID);
1285:
1286: -- End of API body
1287:
1340: IS
1341:
1342: CURSOR c1 IS
1343: SELECT 'X'
1344: FROM CSD_REPAIRS dra
1345: WHERE dra.repair_line_id = p_repair_line_id;
1346:
1347: l_dummy VARCHAR2(1);
1348: l_valid VARCHAR2(1) := 'Y';
1388: IS
1389:
1390: CURSOR c1 IS
1391: SELECT 'X'
1392: FROM CSD_REPAIRS dra
1393: WHERE dra.repair_number = p_repair_number;
1394:
1395: l_dummy VARCHAR2(1);
1396: l_valid VARCHAR2(1) := 'Y';
2549:
2550: --
2551: -- bug#7242791,12. FP, subhat
2552: -- added a new out parameter.ds
2553: -- @param: x_dff_rec OUT NOCOPY CSD_REPAIRS_UTIL.DEF_Rec_Type
2554: --
2555: PROCEDURE Validate_Repairs
2556: (
2557: P_Api_Version_Number IN NUMBER,
2558: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2559: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2560: P_Validation_mode IN VARCHAR2,
2561: p_repair_line_id IN NUMBER := Fnd_Api.G_MISS_NUM,
2562: P_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type,
2563: P_OLD_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type := Csd_Repairs_Pub.G_MISS_REPLN_Rec,
2564: x_return_status OUT NOCOPY VARCHAR2,
2565: x_msg_count OUT NOCOPY NUMBER,
2566: x_msg_data OUT NOCOPY VARCHAR2,
2559: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2560: P_Validation_mode IN VARCHAR2,
2561: p_repair_line_id IN NUMBER := Fnd_Api.G_MISS_NUM,
2562: P_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type,
2563: P_OLD_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type := Csd_Repairs_Pub.G_MISS_REPLN_Rec,
2564: x_return_status OUT NOCOPY VARCHAR2,
2565: x_msg_count OUT NOCOPY NUMBER,
2566: x_msg_data OUT NOCOPY VARCHAR2,
2567: -- bug#7242791, subhat
2564: x_return_status OUT NOCOPY VARCHAR2,
2565: x_msg_count OUT NOCOPY NUMBER,
2566: x_msg_data OUT NOCOPY VARCHAR2,
2567: -- bug#7242791, subhat
2568: x_dff_rec OUT NOCOPY CSD_REPAIRS_UTIL.DEF_Rec_Type
2569:
2570: )
2571: IS
2572:
2571: IS
2572:
2573: l_api_name CONSTANT VARCHAR2(30) := 'Validate_Repairs';
2574: l_api_version_number CONSTANT NUMBER := 1.0;
2575: --l_DEF_Rec Csd_Repairs_Util.DEF_Rec_Type;
2576: l_valid_def_rec BOOLEAN;
2577: l_attribute_category VARCHAR2(30);
2578: l_attribute1 VARCHAR2(150);
2579: l_attribute2 VARCHAR2(150);
3149: END IF;
3150: ELSE
3151: l_attribute30 := p_REPLN_Rec.attribute30;
3152: END IF;
3153: Csd_Repairs_Util.Convert_to_DEF_Rec_Type
3154: (p_attribute_category => l_attribute_category,
3155: p_attribute1 => l_attribute1,
3156: p_attribute2 => l_attribute2,
3157: p_attribute3 => l_attribute3,
3190: -- create and profile CSD: Enable Flexfield Defaulting for Repair Orders Flexfield
3191: -- is set to Yes.
3192: IF p_validation_mode = Jtf_Plsql_Api.G_CREATE THEN
3193: if nvl(fnd_profile.value('CSD_RO_DFF_DEF'),'N') = 'Y' then
3194: l_valid_def_rec := Csd_Repairs_Util.Is_DescFlex_Valid
3195: (p_api_name => 'Create_Repair_Order',
3196: p_desc_flex_name => 'CSD_REPAIRS',
3197: p_attr_values => x_dff_rec,
3198: p_validate_only => FND_API.G_FALSE);
3192: IF p_validation_mode = Jtf_Plsql_Api.G_CREATE THEN
3193: if nvl(fnd_profile.value('CSD_RO_DFF_DEF'),'N') = 'Y' then
3194: l_valid_def_rec := Csd_Repairs_Util.Is_DescFlex_Valid
3195: (p_api_name => 'Create_Repair_Order',
3196: p_desc_flex_name => 'CSD_REPAIRS',
3197: p_attr_values => x_dff_rec,
3198: p_validate_only => FND_API.G_FALSE);
3199: else
3200: l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3196: p_desc_flex_name => 'CSD_REPAIRS',
3197: p_attr_values => x_dff_rec,
3198: p_validate_only => FND_API.G_FALSE);
3199: else
3200: l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3201: (p_api_name => 'Create_Repair_Order',
3202: p_desc_flex_name => 'CSD_REPAIRS',
3203: p_attr_values => x_dff_rec,
3204: p_validate_only => FND_API.G_TRUE
3198: p_validate_only => FND_API.G_FALSE);
3199: else
3200: l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3201: (p_api_name => 'Create_Repair_Order',
3202: p_desc_flex_name => 'CSD_REPAIRS',
3203: p_attr_values => x_dff_rec,
3204: p_validate_only => FND_API.G_TRUE
3205: );
3206: end if;
3204: p_validate_only => FND_API.G_TRUE
3205: );
3206: end if;
3207: ELSE
3208: l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3209: (p_api_name => 'Update_Repair_Order',
3210: p_desc_flex_name => 'CSD_REPAIRS',
3211: p_attr_values => x_dff_rec,
3212: p_validate_only => FND_API.G_TRUE
3206: end if;
3207: ELSE
3208: l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3209: (p_api_name => 'Update_Repair_Order',
3210: p_desc_flex_name => 'CSD_REPAIRS',
3211: p_attr_values => x_dff_rec,
3212: p_validate_only => FND_API.G_TRUE
3213: );
3214: END IF;
3388: SAVEPOINT copy_Attachments;
3389:
3390: IF(Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level) THEN
3391: Fnd_Log.STRING(Fnd_Log.Level_Procedure,
3392: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3393: 'Entered Copy_Attachments API');
3394: END IF;
3395:
3396: -- Standard call to check for call compatibility.
3411: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3412:
3413: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3414: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3415: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3416: 'Validate Original Repair line id = '||p_original_ro_id);
3417: END IF;
3418:
3419: IF NOT (Csd_Process_Util.Validate_rep_line_id
3422: END IF;
3423:
3424: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3425: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3426: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3427: 'Validation of Original Repair line id completed successfully');
3428: END IF;
3429:
3430: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3428: END IF;
3429:
3430: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3431: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3432: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3433: 'Validate New Repair line id = '||p_new_ro_id);
3434: END IF;
3435:
3436: IF NOT (Csd_Process_Util.Validate_rep_line_id
3439: END IF;
3440:
3441: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3442: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3443: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3444: 'Validation of New Repair line id completed successfully');
3445: END IF;
3446:
3447: -- Begin API Body
3449: -- the from_entity (Original Repair Order)
3450:
3451: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3452: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3453: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3454: 'Open main cursor');
3455: END IF;
3456:
3457: IF(Fnd_Log.Level_Event >= Fnd_Log.G_Current_Runtime_Level) THEN
3455: END IF;
3456:
3457: IF(Fnd_Log.Level_Event >= Fnd_Log.G_Current_Runtime_Level) THEN
3458: Fnd_Log.STRING(Fnd_Log.Level_Event,
3459: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3460: 'Started Copying of Attachments ');
3461: END IF;
3462:
3463:
3464: FOR docrec IN doclist LOOP
3465:
3466: IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3467: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3468: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3469: 'Main cursor - Datatype id = '||docrec.datatype_id);
3470:
3471: END IF;
3472:
3480: -- Create Documents records
3481:
3482: IF ( Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level ) THEN
3483: Fnd_Log.STRING(Fnd_Log.Level_Procedure,
3484: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3485: 'Calling the FND_DOCUMENTS_PKG.Insert_Row');
3486: END IF;
3487:
3488: Fnd_Documents_Pkg.Insert_Row(l_row_id_tmp,
3540: CLOSE shorttext;
3541:
3542: IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3543: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3544: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3545: 'Inserting into fnd_documents_short_text for
3546: document id = '||docrec.document_id);
3547: END IF;
3548:
3563: CLOSE longtext;
3564:
3565: IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3566: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3567: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3568: 'Inserting into fnd_documents_long_text for
3569: document id = '||docrec.document_id);
3570: END IF;
3571:
3596: CLOSE fnd_lobs_cur;
3597:
3598: IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3599: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3600: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3601: 'Inserting into fnd_lobs for
3602: document id = '||docrec.document_id);
3603: END IF;
3604:
3635:
3636:
3637: IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3638: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3639: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3640: 'Inserting into fnd_attached_documents for
3641: document id = '||docrec.document_id);
3642: END IF;
3643:
3732: p_data => x_msg_data);
3733:
3734: IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3735: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3736: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3737: 'Copy Attachments completed successfully ');
3738: END IF;
3739:
3740: EXCEPTION
3746: p_data => x_msg_data
3747: );
3748: IF ( Fnd_Log.LEVEL_ERROR >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
3749: Fnd_Log.STRING(Fnd_Log.LEVEL_ERROR,
3750: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3751: 'EXC_ERROR ['||x_msg_data||']');
3752: END IF;
3753:
3754: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3758: p_data => x_msg_data );
3759:
3760: IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
3761: Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
3762: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3763: 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
3764: END IF;
3765:
3766: WHEN OTHERS THEN
3775: p_data => x_msg_data );
3776:
3777: IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
3778: Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
3779: 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3780: 'SQL Message ['||SQLERRM||']');
3781: END IF;
3782:
3783: END Copy_Attachments;
3822: SAVEPOINT Delete_Attachments;
3823:
3824: IF(Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level) THEN
3825: Fnd_Log.STRING(Fnd_Log.Level_Procedure,
3826: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3827: 'Entered Delete_Attachments API');
3828: END IF;
3829:
3830: -- Standard call to check for call compatibility.
3845: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3846:
3847: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3848: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3849: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3850: 'Validate Repair line id = '||p_repair_line_id);
3851: END IF;
3852:
3853: IF NOT (Csd_Process_Util.Validate_rep_line_id
3856: END IF;
3857:
3858: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3859: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3860: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3861: 'Validation of Repair line id completed successfully');
3862: END IF;
3863:
3864: --
3867:
3868: -- Delete from FND_DOCUMENTS_SHORT_TEXT table
3869: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3870: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3871: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3872: 'Deleting from fnd_documents_short_text');
3873: END IF;
3874:
3875: IF(Fnd_Log.Level_Event >= Fnd_Log.G_Current_Runtime_Level) THEN
3873: END IF;
3874:
3875: IF(Fnd_Log.Level_Event >= Fnd_Log.G_Current_Runtime_Level) THEN
3876: Fnd_Log.STRING(Fnd_Log.Level_Event,
3877: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3878: 'Started Deleting of Attachments');
3879: END IF;
3880:
3881:
3893:
3894: -- Delete from FND_DOCUMENTS_LONG_TEXT table
3895: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3896: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3897: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3898: 'Deleting from fnd_documents_long_text');
3899: END IF;
3900:
3901: DELETE FROM fnd_documents_long_text
3912:
3913: -- Delete from FND_DOCUMENTS_LONG_RAW table
3914: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3915: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3916: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3917: 'Deleting from fnd_documents_long_raw');
3918: END IF;
3919:
3920: DELETE FROM fnd_documents_long_raw
3932:
3933: -- Delete from FND_LOBS table
3934: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3935: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3936: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3937: 'Deleting from fnd_lobs');
3938: END IF;
3939:
3940: DELETE FROM fnd_lobs
3951:
3952: -- Delete from FND_DOCUMENTS_TL table
3953: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3954: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3955: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3956: 'Deleting from fnd_documents_tl');
3957: END IF;
3958:
3959: DELETE FROM fnd_documents_tl
3966:
3967: -- Delete from FND_DOCUMENTS table
3968: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3969: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3970: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3971: 'Deleting from fnd_documents');
3972: END IF;
3973:
3974: DELETE FROM fnd_documents
3980:
3981: -- delete from FND_ATTACHED_DOCUMENTS table
3982: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3983: Fnd_Log.STRING(Fnd_Log.Level_Statement,
3984: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3985: 'Deleting from fnd_attached_documents');
3986: END IF;
3987:
3988: DELETE FROM fnd_attached_documents fad
4001: p_data => x_msg_data);
4002:
4003: IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
4004: Fnd_Log.STRING(Fnd_Log.Level_Statement,
4005: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4006: 'Delete_Attachments completed successfully');
4007: END IF;
4008:
4009: EXCEPTION
4015: p_data => x_msg_data
4016: );
4017: IF ( Fnd_Log.LEVEL_ERROR >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
4018: Fnd_Log.STRING(Fnd_Log.LEVEL_ERROR,
4019: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4020: 'EXC_ERROR ['||x_msg_data||']');
4021: END IF;
4022:
4023: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4027: p_data => x_msg_data );
4028:
4029: IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
4030: Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
4031: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4032: 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
4033: END IF;
4034:
4035: WHEN OTHERS THEN
4044: p_data => x_msg_data );
4045:
4046: IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
4047: Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
4048: 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4049: 'SQL Message ['||SQLERRM||']');
4050: END IF;
4051:
4052:
4063: -- p_api_version IN NUMBER,
4064: -- p_commit IN VARCHAR2,
4065: -- p_init_msg_list IN VARCHAR2,
4066: -- p_validation_level IN NUMBER,
4067: -- p_repair_status_rec IN CSD_REPAIRS_PUB.REPAIR_STATUS_REC,
4068: -- p_status_control_rec IN CSD_REPAIRS_PUB.STATUS_UPD_CONTROL_REC,
4069: -- OUT
4070: -- x_return_status
4071: -- x_msg_count
4064: -- p_commit IN VARCHAR2,
4065: -- p_init_msg_list IN VARCHAR2,
4066: -- p_validation_level IN NUMBER,
4067: -- p_repair_status_rec IN CSD_REPAIRS_PUB.REPAIR_STATUS_REC,
4068: -- p_status_control_rec IN CSD_REPAIRS_PUB.STATUS_UPD_CONTROL_REC,
4069: -- OUT
4070: -- x_return_status
4071: -- x_msg_count
4072: -- x_msg_data
4089: p_validation_level IN NUMBER,
4090: x_return_status OUT NOCOPY VARCHAR2,
4091: x_msg_count OUT NOCOPY NUMBER,
4092: x_msg_data OUT NOCOPY VARCHAR2,
4093: p_repair_status_Rec IN Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
4094: p_status_control_rec IN Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE,
4095: x_object_version_number OUT NOCOPY NUMBER
4096: ) IS
4097: l_api_version_number CONSTANT NUMBER := 1.0;
4090: x_return_status OUT NOCOPY VARCHAR2,
4091: x_msg_count OUT NOCOPY NUMBER,
4092: x_msg_data OUT NOCOPY VARCHAR2,
4093: p_repair_status_Rec IN Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
4094: p_status_control_rec IN Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE,
4095: x_object_version_number OUT NOCOPY NUMBER
4096: ) IS
4097: l_api_version_number CONSTANT NUMBER := 1.0;
4098: l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_RO_STATUS';
4102: l_msg_data VARCHAR2 (2000);
4103: l_incident_id NUMBER;
4104: l_repair_type_id NUMBER;
4105: l_object_version_number NUMBER;
4106: l_repair_status_Rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
4107:
4108: --Define cursors
4109:
4110: --Get the flow status id from csd_repairs
4106: l_repair_status_Rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
4107:
4108: --Define cursors
4109:
4110: --Get the flow status id from csd_repairs
4111: CURSOR ro_details_cur(p_repair_line_id NUMBER) IS
4112: SELECT FLOW_STATUS_ID, REPAIR_TYPE_ID
4113: FROM CSD_REPAIRS
4114: WHERE REPAIR_LINE_ID = p_repair_line_id;
4109:
4110: --Get the flow status id from csd_repairs
4111: CURSOR ro_details_cur(p_repair_line_id NUMBER) IS
4112: SELECT FLOW_STATUS_ID, REPAIR_TYPE_ID
4113: FROM CSD_REPAIRS
4114: WHERE REPAIR_LINE_ID = p_repair_line_id;
4115:
4116: -- Get state, state is the status_code in the scehma. status is flow_status
4117: CURSOR flow_stat_cur(p_repair_status_id VARCHAR2) IS
4123: BEGIN
4124: --------------------Standard stuff -------------------------
4125: IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level) THEN
4126: Fnd_Log.STRING (Fnd_Log.level_procedure,
4127: 'csd.plsql.csd_repairs_pvt.update_ro_status.begin',
4128: 'Entering update_ro_status private api');
4129: END IF;
4130: IF Fnd_Api.to_boolean (p_init_msg_list) THEN
4131: Fnd_Msg_Pub.initialize;
4141:
4142: ------------Convert Values to Id----------------------------------------------
4143: IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4144: Fnd_Log.STRING (Fnd_Log.level_event,
4145: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4146: '-----step 1: Value to Id conversion');
4147: END IF;
4148: Csd_Repairs_Util.Convert_Status_Val_To_Id(p_repair_status_rec,
4149: l_repair_status_rec,
4144: Fnd_Log.STRING (Fnd_Log.level_event,
4145: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4146: '-----step 1: Value to Id conversion');
4147: END IF;
4148: Csd_Repairs_Util.Convert_Status_Val_To_Id(p_repair_status_rec,
4149: l_repair_status_rec,
4150: l_return_status);
4151:
4152: ------------Default/Validate Input parameters----------------------------------------------
4151:
4152: ------------Default/Validate Input parameters----------------------------------------------
4153: IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4154: Fnd_Log.STRING (Fnd_Log.level_event,
4155: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4156: '-----step 2: Validate input ');
4157: END IF;
4158:
4159: IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4157: END IF;
4158:
4159: IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4160: Fnd_Log.STRING (Fnd_Log.level_event,
4161: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4162: '-----step 3: get required repair order values for update private api,ro['
4163: ||l_repair_status_rec.repair_line_id||'],status['
4164: ||l_repair_status_rec.repair_status||']');
4165:
4186: CLOSE flow_stat_cur;
4187:
4188: IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4189: Fnd_Log.STRING (Fnd_Log.level_event,
4190: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4191: '-----step 4: Checking for open jobs/tasks,ro['
4192: ||l_repair_status_rec.repair_line_id||'],state['
4193: ||l_repair_status_rec.repair_state||']');
4194: END IF;
4194: END IF;
4195: ------------Validate Input parameters: record level validation------------------------------
4196: IF(p_status_control_Rec.check_task_wip = 'Y') THEN
4197:
4198: Csd_Repairs_Util.CHECK_TASK_N_WIPJOB (
4199: p_repair_line_id => l_repair_status_rec.repair_line_id,
4200: p_repair_status => l_repair_status_rec.repair_state,
4201: x_return_status => l_return_status,
4202: x_msg_count => l_msg_count,
4215: ------------Call other private api to perform the status update------------------------------
4216: /*----------------------Vinay says the api update_flow_Status has logic to close ro status.*/
4217: IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4218: Fnd_Log.STRING (Fnd_Log.level_event,
4219: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4220: '-----step 5a: Calling another private api to validate and update status');
4221: END IF;
4222: Csd_Repairs_Pvt.UPDATE_FLOW_STATUS (
4223: p_api_version => 1.0,
4218: Fnd_Log.STRING (Fnd_Log.level_event,
4219: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4220: '-----step 5a: Calling another private api to validate and update status');
4221: END IF;
4222: Csd_Repairs_Pvt.UPDATE_FLOW_STATUS (
4223: p_api_version => 1.0,
4224: p_commit => Fnd_api.g_false,
4225: p_init_msg_list => Fnd_Api.g_false,
4226: p_validation_level => Fnd_Api.g_valid_level_full,
4255: Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4256:
4257: IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level) THEN
4258: Fnd_Log.STRING (Fnd_Log.level_procedure,
4259: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4260: 'Leaving update_ro_Status private api');
4261: END IF;
4262:
4263: EXCEPTION
4267: ROLLBACK TO update_ro_status_pvt;
4268: Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4269: IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level) THEN
4270: Fnd_Log.STRING (Fnd_Log.level_error,
4271: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4272: 'EXC_ERROR[' || x_msg_data || ']');
4273: END IF;
4274: WHEN Fnd_Api.g_exc_unexpected_error
4275: THEN
4278: Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4279:
4280: IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level) THEN
4281: Fnd_Log.STRING (Fnd_Log.level_exception,
4282: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4283: 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
4284: END IF;
4285: WHEN OTHERS
4286: THEN
4294: Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4295:
4296: IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level) THEN
4297: Fnd_Log.STRING (Fnd_Log.level_exception,
4298: 'csd.plsql.csd_repairs_pvt.update_ro_status',
4299: 'SQL MEssage[' || SQLERRM || ']');
4300: END IF;
4301: END UPDATE_RO_STATUS;
4302:
4319: x_object_version_number OUT NOCOPY NUMBER
4320: ) IS
4321:
4322: -- CONSTANTS --
4323: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.update_flow_status';
4324: lc_api_name CONSTANT VARCHAR2(30) := 'Update_Flow_Status';
4325: lc_api_version CONSTANT NUMBER := 1.0;
4326: lc_update_fs_event_code CONSTANT VARCHAR2(5) := 'SC';
4327: lc_status_code_closed CONSTANT VARCHAR2(1) := 'C';
4388: -- swai: bug 6937272( FP of 6882484)
4389: -- Query to get current object version number of repair order
4390: CURSOR c_get_object_version_number (p_repair_line_id NUMBER) IS
4391: SELECT object_version_number
4392: FROM CSD_REPAIRS
4393: WHERE repair_line_id = p_repair_line_id;
4394:
4395: BEGIN
4396:
4606: END IF;
4607:
4608: END IF;
4609:
4610: -- STEP 6: Update CSD_REPAIRS with status details.
4611:
4612: IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4613: Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4614: 'Calling update Repairs');
4615: END IF;
4616:
4617: --BEGIN
4618:
4619: UPDATE CSD_REPAIRS
4620: SET flow_status_id = p_to_flow_status_id,
4621: status = l_to_status_code,
4622: status_reason_code = p_reason_code,
4623: date_closed = DECODE(l_to_status_code, 'C', SYSDATE, NULL),
4906: p_responsibility_id IN NUMBER
4907: ) RETURN BOOLEAN IS
4908:
4909: -- CONSTANTS --
4910: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.Is_Rt_Update_Allowed';
4911: lc_api_name CONSTANT VARCHAR2(30) := 'Is_Rt_Update_Allowed';
4912:
4913: -- VARIABLES --
4914: l_dummy VARCHAR2(1) := NULL;
4993: x_object_version_number OUT NOCOPY NUMBER
4994: ) IS
4995:
4996: -- CONSTANTS --
4997: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.update_repair_type';
4998: lc_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Type';
4999: lc_api_version CONSTANT NUMBER := 1.0;
5000: lc_update_rt_event_code CONSTANT VARCHAR2(5) := 'RTU';
5001:
5124:
5125:
5126: -- Validate whether the set up exists for transition of
5127: -- FROM repair type to TO repair type for the current flow_status_id
5128: -- in CSD_REPAIRS. In the process, also check if the return
5129: -- reason is required.
5130: IF l_reason_required_flag IS NULL THEN
5131: -- "Unable to update repair type. The repair type transition
5132: -- is not valid for the current status."
5190: END IF;
5191:
5192: BEGIN
5193:
5194: UPDATE CSD_REPAIRS
5195: SET repair_type_id = p_to_repair_type_id,
5196: last_updated_by = Fnd_Global.USER_ID,
5197: last_update_date = SYSDATE,
5198: last_update_login = Fnd_Global.LOGIN_ID,
5429: p_responsibility_id IN NUMBER
5430: ) RETURN BOOLEAN IS
5431:
5432: -- CONSTANTS --
5433: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.Is_Flwsts_Update_Allowed';
5434: lc_api_name CONSTANT VARCHAR2(30) := 'Is_Flwsts_Update_Allowed';
5435:
5436: -- VARIABLES --
5437: l_dummy VARCHAR2(1) := NULL;
5511: p_flwsts_wf_rec IN Flwsts_Wf_Rec_Type
5512: ) IS
5513:
5514: -- CONSTANTS --
5515: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.launch_flwsts_wf';
5516: lc_api_name CONSTANT VARCHAR2(30) := 'Launch_Flwsts_Wf';
5517: lc_api_version CONSTANT NUMBER := 1.0;
5518:
5519: -- For setting WF engine threshold
5834: p_check_task_wip IN VARCHAR2,
5835: p_object_version_number IN NUMBER
5836:
5837: ) IS
5838: l_repair_status_rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
5839: l_status_upd_control_rec Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE;
5840: l_object_Version_number NUMBER;
5841: l_return_status VARCHAR2(1);
5842:
5835: p_object_version_number IN NUMBER
5836:
5837: ) IS
5838: l_repair_status_rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
5839: l_status_upd_control_rec Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE;
5840: l_object_Version_number NUMBER;
5841: l_return_status VARCHAR2(1);
5842:
5843: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repairs_pvt.update_ro_status_websrvc';
5839: l_status_upd_control_rec Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE;
5840: l_object_Version_number NUMBER;
5841: l_return_status VARCHAR2(1);
5842:
5843: lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repairs_pvt.update_ro_status_websrvc';
5844: lc_api_name CONSTANT VARCHAR2(30) := 'update_ro_status_websrvc';
5845: lc_api_version CONSTANT NUMBER := 1.0;
5846:
5847: BEGIN
5873:
5874: l_return_Status := Fnd_Api.G_RET_STS_SUCCESS;
5875:
5876: --debug('In plsql api to update ro status for web service['||to_char(p_repair_line_id)||']');
5877: csd_repairs_util.Check_WebSrvc_Security(
5878: p_repair_line_id => p_repair_line_id,
5879: x_return_status => l_return_status);
5880:
5881: IF(l_return_Status <> Fnd_Api.G_RET_STS_SUCCESS) THEN
5892:
5893: l_status_upd_control_rec.check_task_wip := p_check_task_wip;
5894:
5895: --debug('calling update_ro_status private api');
5896: Csd_Repairs_Pvt.UPDATE_RO_STATUS(P_Api_Version => p_api_version,
5897: P_Commit => p_commit,
5898: P_Init_Msg_List => p_init_msg_list,
5899: P_Validation_Level => Fnd_Api.G_VALID_LEVEL_FULL,
5900: X_Return_Status => x_return_status,
6122: select
6123: csd.repair_line_id
6124: from
6125: cs_estimate_details est,
6126: csd_repairs csd
6127: where
6128: est.order_line_id = p_line_id
6129: and est.original_source_id = csd.repair_line_id;
6130:
6158: cr.object_version_number
6159: into l_from_flow_status_id,
6160: l_repair_type_id,
6161: l_object_version_number
6162: from csd_repairs cr
6163: where cr.repair_line_id = l_repair_line_id;
6164: exception
6165: when no_data_found then
6166: -- should never get in here.
6167: null;
6168: end;
6169:
6170: if l_to_flow_status_id <> l_from_flow_status_id then
6171: csd_repairs_pvt.update_flow_status(p_api_version => 1.0,
6172: p_commit => fnd_api.g_false,
6173: p_init_msg_list => fnd_api.g_false,
6174: p_validation_level => fnd_api.g_valid_level_full,
6175: x_return_status => l_return_status,
6205: resultout := 'COMPLETE:ERROR';
6206:
6207: l_module_name := 'UPDATE_RO_STATUS';
6208:
6209: CSD_REPAIRS_PVT.LAUNCH_WFEXCEPTION_BEVENT(
6210: p_return_status => l_return_status,
6211: p_msg_count => l_msg_count,
6212: p_msg_data => l_msg_data,
6213: p_repair_line_id => l_repair_line_id,
6222: END IF;
6223:
6224: EXCEPTION
6225: WHEN OTHERS THEN
6226: WF_CORE.CONTEXT('CSD_REPAIRS_PVT','UPDATE_RO_STATUS_WF',itemtype,
6227: itemkey,to_char(actid),funcmode);
6228: raise;
6229: END;
6230:
6331: commit;
6332:
6333: END;
6334:
6335: END Csd_Repairs_Pvt;