[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CSD_REPAIRS_S1.NEXTVAL
FROM sys.dual;
SELECT CSD_REPAIRS_S2.NEXTVAL
FROM sys.dual;
SELECT FS_B.flow_status_id,
FS_B.flow_status_code,
FS_LKUP.meaning flow_status,
FS_B.status_code
FROM CSD_FLOW_STATUSES_B FS_B,
FND_LOOKUPS FS_LKUP
WHERE FS_B.flow_status_code = 'D' AND
FS_LKUP.lookup_type = 'CSD_REPAIR_FLOW_STATUS' AND
FS_LKUP.lookup_code = FS_B.flow_status_code AND
FS_LKUP.enabled_flag = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(FS_LKUP.start_date_active, SYSDATE)) AND
TRUNC(NVL(FS_LKUP.end_date_active, SYSDATE));
SELECT business_process_id
FROM csd_repair_types_b b
WHERE repair_type_id = p_repair_type_id;
SELECT incident_severity_id
FROM csd_incidents_v a
WHERE incident_id = p_incident_id;
SELECT 'Y'
INTO l_Refurbish_Non_IO_Order
FROM Csd_Repair_types_b
WHERE Repair_type_id = l_REPLN_Rec.Repair_type_Id
AND repair_type_Ref = 'RF'
AND NVL(internal_order_flag,'N') = 'N' ;
Csd_Repairs_Pkg.Insert_Row(
px_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
p_REQUEST_ID => G_REQUEST_ID,
p_PROGRAM_ID => G_PROGRAM_ID,
p_PROGRAM_APPLICATION_ID => G_PROG_APPL_ID,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REPAIR_NUMBER => l_REPAIR_NUMBER,
p_INCIDENT_ID => l_REPLN_Rec.INCIDENT_ID,
p_INVENTORY_ITEM_ID => l_REPLN_Rec.INVENTORY_ITEM_ID,
p_CUSTOMER_PRODUCT_ID => l_REPLN_Rec.CUSTOMER_PRODUCT_ID,
p_UNIT_OF_MEASURE => l_REPLN_Rec.UNIT_OF_MEASURE,
p_REPAIR_TYPE_ID => l_REPLN_Rec.REPAIR_TYPE_ID,
p_RESOURCE_GROUP => l_REPLN_Rec.RESOURCE_GROUP,
p_RESOURCE_ID => l_REPLN_Rec.RESOURCE_ID,
p_INSTANCE_ID => l_REPLN_Rec.INSTANCE_ID,
p_PROJECT_ID => l_REPLN_Rec.PROJECT_ID,
p_TASK_ID => l_REPLN_Rec.TASK_ID,
p_UNIT_NUMBER => l_REPLN_Rec.UNIT_NUMBER, -- rfieldma, project integration
p_CONTRACT_LINE_ID => l_REPLN_Rec.CONTRACT_LINE_ID,
p_QUANTITY => l_REPLN_Rec.QUANTITY,
-- p_STATUS => l_REPLN_Rec.STATUS,
p_STATUS => l_status, -- Modifed for R12 Flex Flow
p_APPROVAL_REQUIRED_FLAG => l_Approval_Required_Flag,
p_DATE_CLOSED => l_date_closed,
p_QUANTITY_IN_WIP => l_REPLN_Rec.QUANTITY_IN_WIP,
p_APPROVAL_STATUS => l_REPLN_Rec.APPROVAL_STATUS,
p_QUANTITY_RCVD => l_REPLN_Rec.QUANTITY_RCVD,
p_QUANTITY_SHIPPED => l_REPLN_Rec.QUANTITY_SHIPPED,
p_CURRENCY_CODE => l_REPLN_Rec.CURRENCY_CODE,
p_DEFAULT_PO_NUM => l_REPLN_Rec.DEFAULT_PO_NUM,
p_SERIAL_NUMBER => l_REPLN_Rec.SERIAL_NUMBER,
p_PROMISE_DATE => l_REPLN_Rec.PROMISE_DATE,
-- subhat, bug#7242791
p_ATTRIBUTE_CATEGORY => x_dff_value_rec.ATTRIBUTE_CATEGORY, --p_REPLN_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => x_dff_value_rec.ATTRIBUTE1, --p_REPLN_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_dff_value_rec.ATTRIBUTE2,--p_REPLN_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_dff_value_rec.ATTRIBUTE3,--p_REPLN_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_dff_value_rec.ATTRIBUTE4,--p_REPLN_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_dff_value_rec.ATTRIBUTE5,--p_REPLN_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_dff_value_rec.ATTRIBUTE6,--p_REPLN_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_dff_value_rec.ATTRIBUTE7,--p_REPLN_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_dff_value_rec.ATTRIBUTE8,--p_REPLN_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_dff_value_rec.ATTRIBUTE9,--p_REPLN_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_dff_value_rec.ATTRIBUTE10,--p_REPLN_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_dff_value_rec.ATTRIBUTE11,--p_REPLN_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_dff_value_rec.ATTRIBUTE12,--p_REPLN_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_dff_value_rec.ATTRIBUTE13,--p_REPLN_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_dff_value_rec.ATTRIBUTE14,--p_REPLN_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_dff_value_rec.ATTRIBUTE15,--p_REPLN_rec.ATTRIBUTE15,
-- end bug#7242791, subhat.
--bug#7497907, 12.1 FP, subhat
p_ATTRIBUTE16 => x_dff_value_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => x_dff_value_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => x_dff_value_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => x_dff_value_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => x_dff_value_rec.ATTRIBUTE20,
p_ATTRIBUTE21 => x_dff_value_rec.ATTRIBUTE21,
p_ATTRIBUTE22 => x_dff_value_rec.ATTRIBUTE22,
p_ATTRIBUTE23 => x_dff_value_rec.ATTRIBUTE23,
p_ATTRIBUTE24 => x_dff_value_rec.ATTRIBUTE24,
p_ATTRIBUTE25 => x_dff_value_rec.ATTRIBUTE25,
p_ATTRIBUTE26 => x_dff_value_rec.ATTRIBUTE26,
p_ATTRIBUTE27 => x_dff_value_rec.ATTRIBUTE27,
p_ATTRIBUTE28 => x_dff_value_rec.ATTRIBUTE28,
p_ATTRIBUTE29 => x_dff_value_rec.ATTRIBUTE29,
p_ATTRIBUTE30 => x_dff_value_rec.ATTRIBUTE30,
p_ORDER_LINE_ID => l_REPLN_Rec.ORDER_LINE_ID,
p_ORIGINAL_SOURCE_REFERENCE => l_REPLN_Rec.ORIGINAL_SOURCE_REFERENCE,
p_STATUS_REASON_CODE => l_REPLN_Rec.STATUS_REASON_CODE,
p_OBJECT_VERSION_NUMBER => 1, -- travi l_REPLN_Rec.OBJECT_VERSION_NUMBER,
p_AUTO_PROCESS_RMA => l_Auto_Process_RMA,
p_REPAIR_MODE => l_REPLN_Rec.REPAIR_MODE,
p_ITEM_REVISION => l_REPLN_Rec.ITEM_REVISION,
p_REPAIR_GROUP_ID => l_REPLN_Rec.REPAIR_GROUP_ID,
p_RO_TXN_STATUS => l_REPLN_Rec.RO_TXN_STATUS,
p_ORIGINAL_SOURCE_HEADER_ID => l_REPLN_Rec.ORIGINAL_SOURCE_HEADER_ID,
p_ORIGINAL_SOURCE_LINE_ID => l_REPLN_Rec.ORIGINAL_SOURCE_LINE_ID,
p_PRICE_LIST_HEADER_ID => l_REPLN_Rec.PRICE_LIST_HEADER_ID,
p_Supercession_Inv_Item_Id => l_REPLN_Rec.Supercession_Inv_Item_Id,
p_flow_status_Id => l_flow_status_Id,
p_Inventory_Org_Id => l_REPLN_Rec.Inventory_Org_Id,
p_PROBLEM_DESCRIPTION => l_REPLN_Rec.PROBLEM_DESCRIPTION, -- swai: bug 4666344
p_RO_PRIORITY_CODE => l_REPLN_Rec.RO_PRIORITY_CODE, -- swai: R12
p_RESOLVE_BY_DATE => l_resolve_by_date, -- rfieldma: 5355051
p_BULLETIN_CHECK_DATE => l_REPLN_Rec.BULLETIN_CHECK_DATE,
p_ESCALATION_CODE => l_REPLN_Rec.ESCALATION_CODE,
p_REPAIR_YIELD_QUANTITY => l_REPLN_Rec.REPAIR_YIELD_QUANTITY --bug#6692459
);
PROCEDURE Update_Repair_Order(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
p_REPAIR_LINE_ID IN NUMBER,
P_REPLN_Rec IN OUT NOCOPY Csd_Repairs_Pub.REPLN_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Get_repairs(p_REPAIR_LINE_ID NUMBER) IS
SELECT ROWID,
REPAIR_LINE_ID,
REPAIR_NUMBER,
INCIDENT_ID,
INVENTORY_ITEM_ID,
CUSTOMER_PRODUCT_ID,
UNIT_OF_MEASURE,
REPAIR_TYPE_ID,
OWNING_ORGANIZATION_ID,
RESOURCE_ID,
PROJECT_ID,
TASK_ID,
UNIT_NUMBER, -- rfieldma, prj integration
CONTRACT_LINE_ID,
AUTO_PROCESS_RMA,
REPAIR_MODE,
OBJECT_VERSION_NUMBER,
ITEM_REVISION,
INSTANCE_ID,
STATUS,
STATUS_REASON_CODE,
DATE_CLOSED,
APPROVAL_REQUIRED_FLAG,
APPROVAL_STATUS,
SERIAL_NUMBER,
PROMISE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
QUANTITY,
QUANTITY_IN_WIP,
QUANTITY_RCVD,
QUANTITY_SHIPPED
-- bug#7497907, 12.1 FP, subhat
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
FROM CSD_REPAIRS
WHERE REPAIR_LINE_ID = p_REPAIR_LINE_ID
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Order';
SAVEPOINT UPDATE_REPAIR_ORDER_PVT;
Fnd_Message.Set_Name('CSD', 'CSD_API_MISSING_UPDATE_TARGET');
p_validation_mode => Jtf_Plsql_Api.G_UPDATE,
p_repair_line_id => p_repair_line_id,
P_REPLN_Rec => P_REPLN_Rec,
P_OLD_REPLN_Rec => l_OLD_REPLN_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_dff_rec => x_dff_value_rec
);
ROLLBACK TO UPDATE_REPAIR_ORDER_PVT;
Csd_Repairs_Pkg.Update_Row(
p_REPAIR_LINE_ID => p_REPAIR_LINE_ID,
p_REQUEST_ID => G_REQUEST_ID,
p_PROGRAM_ID => G_PROGRAM_ID,
p_PROGRAM_APPLICATION_ID => G_PROG_APPL_ID,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_CREATION_DATE => Fnd_Api.G_MISS_DATE, -- swai ADS bug 3063922, changed from sysdate
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REPAIR_NUMBER => p_REPLN_rec.REPAIR_NUMBER,
p_INCIDENT_ID => p_REPLN_rec.INCIDENT_ID,
p_INVENTORY_ITEM_ID => p_REPLN_rec.INVENTORY_ITEM_ID,
p_CUSTOMER_PRODUCT_ID => p_REPLN_rec.CUSTOMER_PRODUCT_ID,
p_UNIT_OF_MEASURE => p_REPLN_rec.UNIT_OF_MEASURE,
p_REPAIR_TYPE_ID => p_REPLN_rec.REPAIR_TYPE_ID,
p_RESOURCE_GROUP => p_REPLN_rec.RESOURCE_GROUP,
p_RESOURCE_ID => p_REPLN_rec.RESOURCE_ID,
p_INSTANCE_ID => p_REPLN_rec.INSTANCE_ID,
p_PROJECT_ID => p_REPLN_rec.PROJECT_ID,
p_TASK_ID => p_REPLN_rec.TASK_ID,
p_UNIT_NUMBER => p_REPLN_rec.UNIT_NUMBER, -- rfieldma, project integration
p_CONTRACT_LINE_ID => p_REPLN_rec.CONTRACT_LINE_ID,
p_QUANTITY => p_REPLN_rec.QUANTITY,
p_STATUS => p_REPLN_rec.STATUS,
p_APPROVAL_REQUIRED_FLAG => p_REPLN_rec.APPROVAL_REQUIRED_FLAG,
p_DATE_CLOSED => p_REPLN_rec.DATE_CLOSED,
p_QUANTITY_IN_WIP => p_REPLN_rec.QUANTITY_IN_WIP,
p_APPROVAL_STATUS => p_REPLN_rec.APPROVAL_STATUS,
p_QUANTITY_RCVD => p_REPLN_rec.QUANTITY_RCVD,
p_QUANTITY_SHIPPED => p_REPLN_rec.QUANTITY_SHIPPED,
p_CURRENCY_CODE => p_REPLN_rec.CURRENCY_CODE,
p_DEFAULT_PO_NUM => p_REPLN_rec.DEFAULT_PO_NUM,
p_SERIAL_NUMBER => p_REPLN_rec.SERIAL_NUMBER,
p_PROMISE_DATE => p_REPLN_rec.PROMISE_DATE,
p_ATTRIBUTE_CATEGORY => p_REPLN_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => p_REPLN_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_REPLN_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_REPLN_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_REPLN_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_REPLN_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_REPLN_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_REPLN_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_REPLN_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_REPLN_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_REPLN_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_REPLN_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_REPLN_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_REPLN_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_REPLN_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_REPLN_rec.ATTRIBUTE15,
-- bug#7497907, 12.1 FP, subhat
p_ATTRIBUTE16 => p_REPLN_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => p_REPLN_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => p_REPLN_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => p_REPLN_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => p_REPLN_rec.ATTRIBUTE20,
p_ATTRIBUTE21 => p_REPLN_rec.ATTRIBUTE21,
p_ATTRIBUTE22 => p_REPLN_rec.ATTRIBUTE22,
p_ATTRIBUTE23 => p_REPLN_rec.ATTRIBUTE23,
p_ATTRIBUTE24 => p_REPLN_rec.ATTRIBUTE24,
p_ATTRIBUTE25 => p_REPLN_rec.ATTRIBUTE25,
p_ATTRIBUTE26 => p_REPLN_rec.ATTRIBUTE26,
p_ATTRIBUTE27 => p_REPLN_rec.ATTRIBUTE27,
p_ATTRIBUTE28 => p_REPLN_rec.ATTRIBUTE28,
p_ATTRIBUTE29 => p_REPLN_rec.ATTRIBUTE29,
p_ATTRIBUTE30 => p_REPLN_rec.ATTRIBUTE30,
p_ORDER_LINE_ID => p_REPLN_rec.ORDER_LINE_ID,
p_ORIGINAL_SOURCE_REFERENCE => p_REPLN_rec.ORIGINAL_SOURCE_REFERENCE,
p_STATUS_REASON_CODE => p_REPLN_rec.STATUS_REASON_CODE,
p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
p_AUTO_PROCESS_RMA => p_REPLN_rec.AUTO_PROCESS_RMA,
p_REPAIR_MODE => p_REPLN_rec.REPAIR_MODE,
p_ITEM_REVISION => p_REPLN_rec.ITEM_REVISION,
p_REPAIR_GROUP_ID => p_REPLN_rec.REPAIR_GROUP_ID,
p_RO_TXN_STATUS => p_REPLN_rec.RO_TXN_STATUS,
p_ORIGINAL_SOURCE_HEADER_ID => p_REPLN_rec.ORIGINAL_SOURCE_HEADER_ID,
p_ORIGINAL_SOURCE_LINE_ID => p_REPLN_rec.ORIGINAL_SOURCE_LINE_ID,
p_PRICE_LIST_HEADER_ID => p_REPLN_rec.PRICE_LIST_HEADER_ID,
p_PROBLEM_DESCRIPTION => p_REPLN_rec.PROBLEM_DESCRIPTION, -- swai: bug 4666344
p_RO_PRIORITY_CODE => p_Repln_Rec.RO_PRIORITY_CODE, -- swai: R12
p_RESOLVE_BY_DATE => p_Repln_rec.RESOLVE_BY_DATE, -- rfieldma: 5355051
p_BULLETIN_CHECK_DATE => p_Repln_rec.BULLETIN_CHECK_DATE,
p_ESCALATION_CODE => p_Repln_rec.ESCALATION_CODE
);
END Update_Repair_Order;
PROCEDURE Delete_Repair_Order(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
p_REPAIR_LINE_ID IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Repair_Order';
SAVEPOINT DELETE_REPAIR_ORDER_PVT;
Csd_Repairs_Pkg.Delete_Row(
p_REPAIR_LINE_ID => p_REPAIR_LINE_ID);
END Delete_Repair_Order;
SELECT 'X'
FROM CSD_REPAIRS dra
WHERE dra.repair_line_id = p_repair_line_id;
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
RETURN;
SELECT 'X'
FROM CSD_REPAIRS dra
WHERE dra.repair_number = p_repair_number;
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_repair_number IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM cs_incidents_all_b sr
WHERE sr.incident_id = p_incident_id; -- need to add more condition
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_incident_id IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM mtl_system_items_b mtl
WHERE mtl.inventory_item_id = p_inventory_item_id
AND mtl.organization_id = Cs_Std.get_item_valdn_orgzn_id;
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_inventory_item_id IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM csi_item_instances cii,
cs_incidents_all_b sr
WHERE sr.incident_id = p_incident_id
AND cii.instance_id = p_customer_product_id
AND cii.inventory_item_id = p_inventory_item_id
AND sr.customer_id = cii.owner_party_id
AND (p_serial_number IS NULL OR (p_serial_number IS NOT NULL AND
cii.serial_number = p_serial_number));
IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
AND (p_customer_product_id = p_old_customer_product_id
OR p_customer_product_id = Fnd_Api.G_MISS_NUM
OR p_customer_product_id IS NULL) THEN
RETURN;
SELECT 'X'
FROM csi_item_instances cii,
cs_incidents_all_b sr
WHERE sr.incident_id = p_incident_id
AND cii.instance_id = p_customer_product_id
AND cii.inventory_item_id = p_inventory_item_id
AND cii.owner_party_id IN (sr.customer_id , l_Internal_Party_Id )
AND (p_serial_number IS NULL OR (p_serial_number IS NOT NULL AND
cii.serial_number = p_serial_number));
IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
AND (p_customer_product_id = p_old_customer_product_id
OR p_customer_product_id = Fnd_Api.G_MISS_NUM
OR p_customer_product_id IS NULL) THEN
RETURN;
SELECT Internal_Party_id
INTO l_Internal_party_id
FROM csi_install_parameters
WHERE ROWNUM = 1;
SELECT 'X'
FROM aso_i_item_uoms_v uom
WHERE uom.uom_code = p_unit_of_measure
AND uom.inventory_item_id = p_inventory_item_id
AND uom.organization_id = Cs_Std.get_item_valdn_orgzn_id;
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_unit_of_measure IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM CSD_REPAIR_TYPES_B TYPE
WHERE TYPE.repair_type_id = p_repair_type_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(TYPE.start_date_active, SYSDATE))
AND TRUNC(NVL(TYPE.end_date_active, SYSDATE));
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_repair_type_id IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM jtf_rs_resource_extns rs
WHERE l_resource_group IS NULL
AND rs.resource_id = l_resource_id
AND rs.category = 'EMPLOYEE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rs.start_date_active, SYSDATE))
AND TRUNC(NVL(rs.end_date_active, SYSDATE))
UNION
SELECT 'X'
FROM jtf_rs_group_members rm, jtf_rs_resource_extns rs,jtf_rs_groups_b rg
WHERE l_resource_group IS NOT NULL
AND rm.resource_id = l_resource_id
AND rm.group_id = l_resource_group
AND rm.delete_flag <> 'Y'
AND rs.resource_id = rm.resource_id
AND rs.category = 'EMPLOYEE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rs.start_date_active, SYSDATE))
AND TRUNC(NVL(rs.end_date_active, SYSDATE))
AND rg.group_id = rm.group_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rg.start_date_active, SYSDATE))
AND TRUNC(NVL(rg.end_date_active, SYSDATE));
IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
AND (p_resource_id = p_old_resource_id
OR p_resource_id = Fnd_Api.G_MISS_NUM
OR p_resource_id IS NULL) THEN
RETURN;
SELECT 'X'
FROM jtf_rs_group_usages rs, jtf_Rs_groups_b rg
WHERE rs.group_id = p_resource_group
AND rs.usage = 'REPAIR_ORGANIZATION'
AND rs.group_id = rg.group_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rg.start_date_active, SYSDATE))
AND TRUNC(NVL(rg.end_date_active, SYSDATE));
IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
AND (p_resource_group = p_old_resource_group
OR p_resource_group = Fnd_Api.G_MISS_NUM
OR p_resource_group IS NULL) THEN
RETURN;
SELECT 'X'
FROM fnd_lookups fnd
WHERE fnd.lookup_code = p_status
AND fnd.lookup_type = 'CSD_REPAIR_STATUS'
AND fnd.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(fnd.start_date_active, SYSDATE))
AND TRUNC(NVL(fnd.end_date_active, SYSDATE));
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_status IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM fnd_lookups fnd
WHERE fnd.lookup_code = p_approval_required_flag
AND fnd.lookup_type = 'YES_NO'
AND fnd.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(fnd.start_date_active, SYSDATE))
AND TRUNC(NVL(fnd.end_date_active, SYSDATE));
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_approval_required_flag IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
SELECT 'X'
FROM fnd_lookups fnd
WHERE fnd.lookup_code = p_approval_status
AND fnd.lookup_type = lc_appr_sts_lkp_typ
AND fnd.enabled_flag = lc_enabled
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(fnd.start_date_active, SYSDATE))
AND TRUNC(NVL(fnd.end_date_active, SYSDATE));
IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
AND (p_approval_status = p_old_approval_status
OR p_approval_status = Fnd_Api.G_MISS_CHAR
OR p_approval_status IS NULL) THEN
RETURN;
IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
AND (p_promise_date = p_old_promise_date
OR p_promise_date = Fnd_Api.G_MISS_DATE
OR p_promise_date IS NULL) THEN
RETURN;
IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_quantity IS NULL THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
elsif p_validation_mode = JTF_PLSQL_API.G_UPDATE then
if p_quantity = FND_API.G_MISS_NUM
or p_quantity is null then
if p_old_quantity < p_quantity_in_wip then
l_valid := 'N';
SELECT 'X'
FROM csd_repair_order_groups crog
WHERE crog.repair_group_id = p_ro_group_id;
ELSIF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
IF p_ro_group_id IS NULL THEN
IF (g_debug > 0 ) THEN
Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id is null : Validate_RO_GROUP_ID');
Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id p_validation_mode : '||p_validation_mode);
Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id is not null or g_miss_num : Validate_RO_GROUP_ID');
Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id p_validation_mode : '||p_validation_mode);
if p_validation_mode = JTF_PLSQL_API.G_UPDATE then
if p_ro_group_id is null then
x_return_status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.SET_TOKEN('API_NAME', 'Update_Repair_Order');
Csd_Gen_Utility_Pvt.ADD('Create / update Repair_Order group_id is not null or g_miss_num : Validate_RO_GROUP_ID');
Csd_Gen_Utility_Pvt.ADD('Create / update group_id p_validation_mode : '||p_validation_mode);
SELECT 'X'
FROM oe_order_lines_all
WHERE line_id = p_ORIGINAL_SOURCE_LINE_ID
AND header_id = p_ORIGINAL_SOURCE_HEADER_ID;
SELECT 'Y' INTO l_Refurbished_repair_Type_Flag
FROM Csd_REpair_types_b
WHERE Repair_type_Id = p_Repln_Rec.Repair_Type_Id
AND NVL(internal_order_Flag,'N') = 'N'
AND repair_type_ref = 'RF' ;
(p_api_name => 'Update_Repair_Order',
p_desc_flex_name => 'CSD_REPAIRS',
p_attr_values => x_dff_rec,
p_validate_only => FND_API.G_TRUE
);
SELECT fad.seq_num,
fad.document_id,
fad.attached_document_id,
fad.attribute_category,
fad.attribute1,
fad.attribute2,
fad.attribute3,
fad.attribute4,
fad.attribute5,
fad.attribute6,
fad.attribute7,
fad.attribute8,
fad.attribute9,
fad.attribute10,
fad.attribute11,
fad.attribute12,
fad.attribute13,
fad.attribute14,
fad.attribute15,
fad.column1,
fad.automatically_added_flag,
fd.datatype_id,
fd.category_id,
fd.security_type,
fd.security_id,
fd.publish_flag,
fd.image_type,
fd.storage_type,
fd.usage_type,
fd.start_date_active,
fd.end_date_active,
fd.request_id,
fd.program_application_id,
fd.program_id,
fdtl.LANGUAGE,
fdtl.description,
fdtl.file_name,
fdtl.media_id,
fdtl.doc_attribute_category dattr_cat,
fdtl.doc_attribute1 dattr1,
fdtl.doc_attribute2 dattr2,
fdtl.doc_attribute3 dattr3,
fdtl.doc_attribute4 dattr4,
fdtl.doc_attribute5 dattr5,
fdtl.doc_attribute6 dattr6,
fdtl.doc_attribute7 dattr7,
fdtl.doc_attribute8 dattr8,
fdtl.doc_attribute9 dattr9,
fdtl.doc_attribute10 dattr10,
fdtl.doc_attribute11 dattr11,
fdtl.doc_attribute12 dattr12,
fdtl.doc_attribute13 dattr13,
fdtl.doc_attribute14 dattr14,
fdtl.doc_attribute15 dattr15
FROM fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdtl
WHERE fad.document_id = fd.document_id
AND fd.document_id = fdtl.document_id
AND fdtl.LANGUAGE = USERENV('LANG')
AND fad.pk1_value = TO_CHAR(p_original_ro_id);
SELECT short_text
FROM fnd_documents_short_text
WHERE media_id = mid;
SELECT long_text
FROM fnd_documents_long_text
WHERE media_id = mid;
SELECT file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
LANGUAGE,
oracle_charset,
file_format
FROM fnd_lobs
WHERE file_id = mid;
'Calling the FND_DOCUMENTS_PKG.Insert_Row');
Fnd_Documents_Pkg.Insert_Row(l_row_id_tmp,
l_document_id_tmp,
SYSDATE,
Fnd_Global.user_id,
SYSDATE,
Fnd_Global.user_id,
Fnd_Global.user_id,
docrec.datatype_id,
docrec.category_id,
docrec.security_type,
docrec.security_id,
docrec.publish_flag,
docrec.image_type,
docrec.storage_type,
docrec.usage_type,
docrec.start_date_active,
docrec.end_date_active,
docrec.request_id,
docrec.program_application_id,
docrec.program_id,
SYSDATE,
docrec.LANGUAGE,
docrec.description,
docrec.file_name,
l_media_id_tmp,
docrec.dattr_cat, docrec.dattr1,
docrec.dattr2, docrec.dattr3,
docrec.dattr4, docrec.dattr5,
docrec.dattr6, docrec.dattr7,
docrec.dattr8, docrec.dattr9,
docrec.dattr10, docrec.dattr11,
docrec.dattr12, docrec.dattr13,
docrec.dattr14, docrec.dattr15);
'Inserting into fnd_documents_short_text for
document id = '||docrec.document_id);
INSERT INTO fnd_documents_short_text (
media_id,
short_text)
VALUES (
l_media_id_tmp,
l_short_text_tmp);
'Inserting into fnd_documents_long_text for
document id = '||docrec.document_id);
INSERT INTO fnd_documents_long_text (
media_id,
long_text)
VALUES (
l_media_id_tmp,
l_long_text_tmp);
'Inserting into fnd_lobs for
document id = '||docrec.document_id);
INSERT INTO fnd_lobs (
file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
LANGUAGE,
oracle_charset,
file_format)
VALUES (
l_media_id_tmp,
l_fnd_lobs_rec.file_name,
l_fnd_lobs_rec.file_content_type,
l_fnd_lobs_rec.upload_date,
l_fnd_lobs_rec.expiration_date,
l_fnd_lobs_rec.program_name,
l_fnd_lobs_rec.program_tag,
l_fnd_lobs_rec.file_data,
l_fnd_lobs_rec.LANGUAGE,
l_fnd_lobs_rec.oracle_charset,
l_fnd_lobs_rec.file_format);
'Inserting into fnd_attached_documents for
document id = '||docrec.document_id);
INSERT INTO fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
automatically_added_flag,
program_application_id,
program_id,
program_update_date,
request_id,
attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15,
column1)
(SELECT
fnd_attached_documents_s.NEXTVAL,
docrec.document_id,
SYSDATE,
Fnd_Global.user_id,
SYSDATE,
Fnd_Global.user_id,
Fnd_Global.user_id,
docrec.seq_num,
entity_name,
TO_CHAR(p_new_ro_id),
pk2_value,
pk3_value,
pk4_value,
pk5_value,
'Y',
program_application_id,
program_id,
SYSDATE,
request_id,
docrec.attribute_category,
docrec.attribute1, docrec.attribute2,
docrec.attribute3, docrec.attribute4,
docrec.attribute5, docrec.attribute6,
docrec.attribute7, docrec.attribute8,
docrec.attribute9, docrec.attribute10,
docrec.attribute11, docrec.attribute12,
docrec.attribute13, docrec.attribute14,
docrec.attribute15,
docrec.column1
FROM fnd_attached_documents
WHERE attached_document_id = docrec.attached_document_id);
/* procedure name: Delete_Attachments */
/* description : This procedure deletes all the attachements */
/* linked with the repair line id. */
/* */
/* p_api_version Standard IN param */
/* p_commit Standard IN param */
/* p_init_msg_list Standard IN param */
/* p_validation_level Standard IN param */
/* p_repair_line_id Repair Line Id */
/* x_return_status Standard OUT param */
/* x_msg_count Standard OUT param */
/* x_msg_data Standard OUT param */
/* */
/*------------------------------------------------------------------*/
PROCEDURE Delete_Attachments
(p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments';
SAVEPOINT Delete_Attachments;
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Entered Delete_Attachments API');
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Validate Repair line id = '||p_repair_line_id);
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Validation of Repair line id completed successfully');
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_documents_short_text');
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Started Deleting of Attachments');
DELETE FROM fnd_documents_short_text
WHERE media_id IN
(SELECT fdtl.media_id
FROM fnd_documents_tl fdtl,
fnd_documents fd,
fnd_attached_documents fad
WHERE fdtl.document_id = fd.document_id
AND fd.document_id = fad.document_id
AND fd.usage_type = 'O'
AND fd.datatype_id = 1
AND fad.pk1_value = TO_CHAR(p_repair_line_id));
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_documents_long_text');
DELETE FROM fnd_documents_long_text
WHERE media_id IN
(SELECT fdtl.media_id
FROM fnd_documents_tl fdtl,
fnd_documents fd,
fnd_attached_documents fad
WHERE fdtl.document_id = fd.document_id
AND fd.document_id = fad.document_id
AND fd.usage_type = 'O'
AND fd.datatype_id = 2
AND fad.pk1_value = TO_CHAR(p_repair_line_id));
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_documents_long_raw');
DELETE FROM fnd_documents_long_raw
WHERE media_id IN
(SELECT fdtl.media_id
FROM fnd_documents_tl fdtl,
fnd_documents fd,
fnd_attached_documents fad
WHERE fdtl.document_id = fd.document_id
AND fd.document_id = fad.document_id
AND fd.usage_type = 'O'
AND fd.datatype_id IN (3,4)
AND fad.pk1_value = TO_CHAR(p_repair_line_id));
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_lobs');
DELETE FROM fnd_lobs
WHERE file_id IN
(SELECT fdtl.media_id
FROM fnd_documents_tl fdtl,
fnd_documents fd,
fnd_attached_documents fad
WHERE fdtl.document_id = fd.document_id
AND fd.document_id = fad.document_id
AND fd.usage_type = 'O'
AND fd.datatype_id = 6
AND fad.pk1_value = TO_CHAR(p_repair_line_id));
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_documents_tl');
DELETE FROM fnd_documents_tl
WHERE document_id IN
(SELECT fad.document_id
FROM fnd_attached_documents fad, fnd_documents fd
WHERE fad.document_id = fd.document_id
AND fd.usage_type = 'O'
AND fad.pk1_value = TO_CHAR(p_repair_line_id));
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_documents');
DELETE FROM fnd_documents
WHERE usage_type = 'O'
AND document_id IN
(SELECT document_id
FROM fnd_attached_documents fad
WHERE fad.pk1_value = TO_CHAR(p_repair_line_id));
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Deleting from fnd_attached_documents');
DELETE FROM fnd_attached_documents fad
WHERE fad.pk1_value = TO_CHAR(p_repair_line_id);
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'Delete_Attachments completed successfully');
ROLLBACK TO Delete_Attachments;
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'EXC_ERROR ['||x_msg_data||']');
ROLLBACK TO Delete_Attachments;
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
ROLLBACK TO Delete_Attachments;
'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
'SQL Message ['||SQLERRM||']');
END Delete_Attachments;
PROCEDURE UPDATE_RO_STATUS
(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_status_Rec IN Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
p_status_control_rec IN Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE,
x_object_version_number OUT NOCOPY NUMBER
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_RO_STATUS';
SELECT FLOW_STATUS_ID, REPAIR_TYPE_ID
FROM CSD_REPAIRS
WHERE REPAIR_LINE_ID = p_repair_line_id;
SELECT STATUS_CODE
FROM CSD_FLOW_STATUSES_B
WHERE FLOW_STATUS_ID = p_repair_status_id;
'csd.plsql.csd_repairs_pvt.update_ro_status.begin',
'Entering update_ro_status private api');
SAVEPOINT update_ro_status_pvt;
'csd.plsql.csd_repairs_pvt.update_ro_status',
'-----step 1: Value to Id conversion');
'csd.plsql.csd_repairs_pvt.update_ro_status',
'-----step 2: Validate input ');
'csd.plsql.csd_repairs_pvt.update_ro_status',
'-----step 3: get required repair order values for update private api,ro['
||l_repair_status_rec.repair_line_id||'],status['
||l_repair_status_rec.repair_status||']');
'csd.plsql.csd_repairs_pvt.update_ro_status',
'-----step 4: Checking for open jobs/tasks,ro['
||l_repair_status_rec.repair_line_id||'],state['
||l_repair_status_rec.repair_state||']');
/*----------------------Vinay says the api update_flow_Status has logic to close ro status.*/
IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
Fnd_Log.STRING (Fnd_Log.level_event,
'csd.plsql.csd_repairs_pvt.update_ro_status',
'-----step 5a: Calling another private api to validate and update status');
Csd_Repairs_Pvt.UPDATE_FLOW_STATUS (
p_api_version => 1.0,
p_commit => Fnd_api.g_false,
p_init_msg_list => Fnd_Api.g_false,
p_validation_level => Fnd_Api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repair_line_id => l_repair_status_rec.repair_line_id,
p_repair_type_id => l_repair_type_id,
p_from_flow_status_id => l_repair_status_rec.from_status_id,
p_to_flow_status_id => l_repair_status_rec.repair_status_id,
p_reason_code => l_repair_status_rec.reason_code,
p_comments => l_repair_status_rec.comments,
p_check_access_flag => 'Y',
p_object_version_number => l_repair_status_rec.object_version_number,
x_object_version_number => x_object_version_number) ;
'csd.plsql.csd_repairs_pvt.update_ro_status',
'Leaving update_ro_Status private api');
ROLLBACK TO update_ro_status_pvt;
'csd.plsql.csd_repairs_pvt.update_ro_status',
'EXC_ERROR[' || x_msg_data || ']');
ROLLBACK TO update_ro_status_pvt;
'csd.plsql.csd_repairs_pvt.update_ro_status',
'EXC_UNEXP_ERROR[' || x_msg_data || ']');
ROLLBACK TO update_ro_status_pvt;
'csd.plsql.csd_repairs_pvt.update_ro_status',
'SQL MEssage[' || SQLERRM || ']');
END UPDATE_RO_STATUS;
PROCEDURE Update_Flow_Status (
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER,
p_repair_type_id IN NUMBER,
p_from_flow_status_id IN NUMBER,
p_to_flow_status_id IN NUMBER,
p_reason_code IN VARCHAR2,
p_comments IN VARCHAR2,
p_check_access_flag IN VARCHAR2,
p_object_version_number IN NUMBER,
x_object_version_number OUT NOCOPY NUMBER
) IS
-- CONSTANTS --
lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.update_flow_status';
lc_api_name CONSTANT VARCHAR2(30) := 'Update_Flow_Status';
lc_update_fs_event_code CONSTANT VARCHAR2(5) := 'SC';
SELECT FS_TRANS.flwsts_tran_id,
FS_TRANS.wf_item_type,
FS_TRANS.wf_process_name,
FS_TRANS.reason_required_flag,
FS_TRANS.capture_activity_flag,
FS_TRANS.allow_all_resp_flag,
TO_FS_B.status_code,
TO_FS_B.flow_status_code to_flow_status_code,
FROM_FS_B.flow_status_code from_flow_status_code
FROM CSD_FLWSTS_TRANS_B FS_TRANS,
CSD_FLOW_STATUSES_B TO_FS_B,
CSD_FLOW_STATUSES_B FROM_FS_B
WHERE FS_TRANS.from_flow_status_id = p_from_flow_status_id AND
FS_TRANS.to_flow_status_id = p_to_flow_status_id AND
FS_TRANS.repair_type_id = p_repair_type_id AND
TO_FS_B.flow_status_id = FS_TRANS.to_flow_status_id AND
FROM_FS_B.flow_status_id = FS_TRANS.from_flow_status_id;
SELECT RSN_LKUP.meaning reason
FROM FND_LOOKUPS RSN_LKUP
WHERE
RSN_LKUP.lookup_type = 'CSD_REASON' AND
RSN_LKUP.lookup_code = p_reason_code AND
RSN_LKUP.enabled_flag = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(RSN_LKUP.start_date_active, SYSDATE)) AND
TRUNC(NVL(RSN_LKUP.end_date_active, SYSDATE));
SELECT milestone_code
FROM CSD_FLWSTS_TRAN_MILES
WHERE flwsts_tran_id = p_flwsts_tran_id;
SELECT object_version_number
FROM CSD_REPAIRS
WHERE repair_line_id = p_repair_line_id;
SAVEPOINT Update_Flow_Status;
'Entered Update_Flow_Status');
-- "Unable to update repair status. A new status is required for the transition."
Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_TO_STATUS_REQD');
'Calling Is_Flwsts_Update_Allowed');
IF NOT Is_Flwsts_Update_Allowed(p_repair_type_id => p_repair_type_id,
p_from_flow_status_id => p_from_flow_status_id,
p_to_flow_status_id => p_to_flow_status_id,
p_responsibility_id => Fnd_Global.resp_id
) THEN
-- Unable to update repair status. The user does not
-- have access to update the repair status.
Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_NO_ACCESS');
'After calling Is_Flwsts_Update_Allowed');
SELECT TO_CHAR(CSD_WF_ITEM_KEY_S.NEXTVAL)
INTO l_wf_item_key
FROM DUAL;
'Calling update Repairs');
UPDATE CSD_REPAIRS
SET flow_status_id = p_to_flow_status_id,
status = l_to_status_code,
status_reason_code = p_reason_code,
date_closed = DECODE(l_to_status_code, 'C', SYSDATE, NULL),
wf_item_key = NVL(l_wf_item_key, wf_item_key),
wf_item_type = NVL(l_wf_item_type, wf_item_type),
last_updated_by = Fnd_Global.USER_ID,
last_update_date = SYSDATE,
last_update_login = Fnd_Global.LOGIN_ID,
object_version_number = object_version_number + 1
WHERE repair_line_id = p_repair_line_id AND
flow_status_id = p_from_flow_status_id; -- swai: bug 6937272 (FP of 6882484)
'After calling update Repairs');
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_repair_line_id,
p_EVENT_CODE => lc_update_fs_event_code,
p_EVENT_DATE => SYSDATE,
-- p_QUANTITY => p_quantity,
p_PARAMN1 => NULL,
p_PARAMN2 => NULL,
p_PARAMN3 => NULL,
p_PARAMN10 => Fnd_Global.USER_ID,
p_PARAMC1 => l_to_flow_status_code,
p_PARAMC2 => l_from_flow_status_code,
p_PARAMC3 => l_reason_meaning,
p_PARAMC4 => NULL,
p_PARAMC5 => NULL,
p_PARAMC6 => p_comments,
p_PARAMC7 => l_wf_item_type,
p_PARAMC8 => l_wf_item_key,
p_PARAMC9 => l_wf_process_name,
p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
'Leaving Update_Flow_Status');
ROLLBACK TO Update_Flow_Status;
ROLLBACK TO Update_Flow_Status;
ROLLBACK TO Update_Flow_Status;
END Update_Flow_Status;
FUNCTION Is_Rt_Update_Allowed (
p_from_repair_type_id IN NUMBER,
p_to_repair_type_id IN NUMBER,
p_common_flow_status_id IN NUMBER,
p_responsibility_id IN NUMBER
) RETURN BOOLEAN IS
-- CONSTANTS --
lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.Is_Rt_Update_Allowed';
lc_api_name CONSTANT VARCHAR2(30) := 'Is_Rt_Update_Allowed';
CURSOR c_is_rt_update_allowed IS
SELECT 'x'
FROM CSD_RT_TRANS_B RT_B
WHERE RT_B.FROM_REPAIR_TYPE_ID = p_from_repair_type_id AND
RT_B.TO_REPAIR_TYPE_ID = p_to_repair_type_id AND
RT_B.COMMON_FLOW_STATUS_ID = p_common_flow_status_id AND
((RT_B.ALLOW_ALL_RESP_FLAG = 'Y') OR
EXISTS
(SELECT 'y'
FROM CSD_RT_TRAN_RESPS RESP
WHERE RESP.RT_TRAN_ID = RT_B.RT_TRAN_ID AND
RESP.RESPONSIBILITY_ID = p_responsibility_id)
);
'Entered Is_Rt_Update_Allowed');
OPEN c_is_rt_update_allowed;
FETCH c_is_rt_update_allowed INTO l_dummy;
CLOSE c_is_rt_update_allowed;
'Leaving Is_Rt_Update_Allowed');
END Is_Rt_Update_Allowed;
PROCEDURE Update_Repair_Type (
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER,
p_from_repair_type_id IN NUMBER,
p_to_repair_type_id IN NUMBER,
p_common_flow_status_id IN NUMBER,
p_reason_code IN VARCHAR2,
p_object_version_number IN NUMBER,
x_object_version_number OUT NOCOPY NUMBER
) IS
-- CONSTANTS --
lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.update_repair_type';
lc_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Type';
lc_update_rt_event_code CONSTANT VARCHAR2(5) := 'RTU';
SELECT RT_TRANS.reason_required_flag,
RT_TRANS.capture_activity_flag
FROM CSD_RT_TRANS_B RT_TRANS
WHERE RT_TRANS.from_repair_type_id = p_from_repair_type_id AND
RT_TRANS.to_repair_type_id = p_to_repair_type_id AND
RT_TRANS.common_flow_status_id = p_common_flow_status_id;
SELECT meaning
FROM FND_LOOKUPS RSN_LKUP
WHERE
RSN_LKUP.lookup_type = 'CSD_RT_TRANSITION_REASONS' AND
RSN_LKUP.lookup_code = p_reason_code AND
RSN_LKUP.enabled_flag = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(RSN_LKUP.start_date_active, SYSDATE)) AND
TRUNC(NVL(RSN_LKUP.end_date_active, SYSDATE));
SELECT CRTV.name name1, CRTV1.name name2
FROM CSD_REPAIR_TYPES_Vl CRTV, CSD_REPAIR_TYPES_Vl CRTV1
WHERE
CRTV.REPAIR_TYPE_Id = p_from_repair_type_id AND
CRTV1.REPAIR_TYPE_Id = p_to_repair_type_id;
SAVEPOINT Update_Repair_Type;
'Entered Update_Repair_Type');
-- "Unable to update repair type. A new repair type is required for the transition."
Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TO_RT_REQD');
'Calling Is_Rt_Update_Allowed');
IF NOT Is_Rt_Update_Allowed(p_from_repair_type_id => p_from_repair_type_id,
p_to_repair_type_id => p_to_repair_type_id,
p_common_flow_status_id => p_common_flow_status_id,
p_responsibility_id => Fnd_Global.resp_id
) THEN
-- Unable to update repair type. The user does not
-- have access to update the repair type.
Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TRANS_NO_ACCESS');
'After calling Is_Rt_Update_Allowed');
'calling update for Repair Type');
UPDATE CSD_REPAIRS
SET repair_type_id = p_to_repair_type_id,
last_updated_by = Fnd_Global.USER_ID,
last_update_date = SYSDATE,
last_update_login = Fnd_Global.LOGIN_ID,
object_version_number = object_version_number + 1
WHERE repair_line_id = p_repair_line_id AND
object_version_number = p_object_version_number;
'After calling update for Repair Type');
p_EVENT_CODE => lc_update_rt_event_code,
p_EVENT_DATE => SYSDATE,
p_PARAMN1 => p_from_repair_type_id,
p_PARAMN2 => p_to_repair_type_id,
p_PARAMN3 => Fnd_Global.USER_ID,
p_PARAMC1 => p_reason_code,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);*/
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => 1,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_repair_line_id,
p_EVENT_CODE => 'RTU',
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => p_from_repair_type_id,
p_PARAMN2 => p_to_repair_type_id,
p_PARAMN3 => fnd_global.user_id,--NULL,
p_PARAMN4 => NULL,
p_PARAMN5 => NULL,
p_PARAMN6 => NULL,
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => NULL,
p_PARAMC1 => p_reason_code,
p_PARAMC2 => l_dummy, -- reason
p_PARAMC3 => l_from_repair_type,
p_PARAMC4 => l_to_repair_type,
p_PARAMC5 => NULL,
p_PARAMC6 => NULL,
p_PARAMC7 => NULL,
p_PARAMC8 => NULL,
p_PARAMC9 => NULL,
p_PARAMC10 => NULL,
p_PARAMD1 => NULL,
p_PARAMD2 => NULL,
p_PARAMD3 => NULL,
p_PARAMD4 => NULL,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
'Leaving Update_Repair_Type');
ROLLBACK TO Update_Repair_Type;
ROLLBACK TO Update_Repair_Type;
ROLLBACK TO Update_Repair_Type;
END Update_Repair_Type;
FUNCTION Is_Flwsts_Update_Allowed(
p_repair_type_id IN NUMBER,
p_from_flow_status_id IN NUMBER,
p_to_flow_status_id IN NUMBER,
p_responsibility_id IN NUMBER
) RETURN BOOLEAN IS
-- CONSTANTS --
lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.Is_Flwsts_Update_Allowed';
lc_api_name CONSTANT VARCHAR2(30) := 'Is_Flwsts_Update_Allowed';
CURSOR c_is_flwsts_update_allowed IS
SELECT 'x'
FROM CSD_FLWSTS_TRANS_B RT_B
WHERE RT_B.FROM_FLOW_STATUS_ID = p_from_flow_status_id AND
RT_B.TO_FLOW_STATUS_ID = p_to_flow_status_id AND
RT_B.REPAIR_TYPE_ID = p_repair_type_id AND
(
(RT_B.ALLOW_ALL_RESP_FLAG = 'Y') OR
EXISTS
(SELECT 'y'
FROM CSD_FLWSTS_TRAN_RESPS RESP
WHERE RESP. FLWSTS_TRAN_ID = RT_B.FLWSTS_TRAN_ID AND
RESP.RESPONSIBILITY_ID = p_responsibility_id)
);
'Entered Is_Flwsts_Update_Allowed');
OPEN c_is_flwsts_update_allowed;
FETCH c_is_flwsts_update_allowed INTO l_dummy;
CLOSE c_is_flwsts_update_allowed;
'Leaving Is_Flwsts_Update_Allowed');
END Is_Flwsts_Update_Allowed;
PROCEDURE UPDATE_RO_STATUS_WEBSRVC
(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER,
p_repair_status IN VARCHAR2,
p_reason_code IN VARCHAR2,
p_comments IN VARCHAR2,
p_check_task_wip IN VARCHAR2,
p_object_version_number IN NUMBER
) IS
l_repair_status_rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repairs_pvt.update_ro_status_websrvc';
lc_api_name CONSTANT VARCHAR2(30) := 'update_ro_status_websrvc';
SAVEPOINT UPDATE_RO_STATUS_WEBSRVC_PVT;
'Entered UPDATE_RO_STATUS_WEBSRVC');
--debug('In plsql api to update ro status for web service['||to_char(p_repair_line_id)||']');
--debug('calling update_ro_status private api');
Csd_Repairs_Pvt.UPDATE_RO_STATUS(P_Api_Version => p_api_version,
P_Commit => p_commit,
P_Init_Msg_List => p_init_msg_list,
P_Validation_Level => Fnd_Api.G_VALID_LEVEL_FULL,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data,
P_REPAIR_STATUS_REC => l_repair_status_rec,
P_STATUS_CONTROL_REC => l_status_upd_control_rec,
X_OBJECT_VERSION_NUMBER => l_object_Version_number);
'Leaving UPDATE_RO_STATUS_WEBSRVC');
ROLLBACK TO UPDATE_RO_STATUS_WEBSRVC_PVT;
ROLLBACK TO UPDATE_RO_STATUS_WEBSRVC_PVT;
ROLLBACK TO UPDATE_RO_STATUS_WEBSRVC_PVT;
END UPDATE_RO_STATUS_WebSrvc;
Select csd_ro_status_bevent_key_s1.nextval into l_event_key from dual ;
/* Procedure name: UPDATE_RO_STATUS_WF */
/* Description : Procedure called from workflow process to update repair order */
/* status */
/* */
/* Called from : Workflow */
/* PARAMETERS */
/* IN */
/* */
/* itemtype - type of the current item */
/* itemkey - key of the current item */
/* actid - process activity instance id */
/* funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...) */
/* OUT */
/* result */
/* - COMPLETE[:] */
/* activity has completed with the indicated result */
/* - WAITING */
/* activity is waiting for additional transitions */
/* - DEFERED */
/* execution should be defered to background */
/* - NOTIFIED[::] */
/* activity has notified an external entity that this */
/* step must be performed. A call to wf_engine.CompleteActivty */
/* will signal when this step is complete. Optional */
/* return of notification ID and assigned user. */
/* - ERROR[:] */
/* function encountered an error. */
/* Change Hist : */
/* 04/18/06 mshirkol Initial Creation. ( Fix for bug#5610891 ) */
/*-------------------------------------------------------------------------------------*/
Procedure UPDATE_RO_STATUS_WF
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out nocopy varchar2) IS
l_line_id number;
select
csd.repair_line_id
from
cs_estimate_details est,
csd_repairs csd
where
est.order_line_id = p_line_id
and est.original_source_id = csd.repair_line_id;
l_module_name := 'UPDATE_RO_STATUS';
WF_CORE.CONTEXT('CSD_REPAIRS_PVT','UPDATE_RO_STATUS_WF',itemtype,
itemkey,to_char(actid),funcmode);
/* Called from : CSD_UPDATE_PROGRAMS_PVT */
/* PARAMETERS */
/* IN */
/* p_return_status */
/* p_msg_count */
/* p_msg_data */
/* p_repair_line_id */
/* p_module_name */
/* */
/* Change Hist : */
/* 04/18/06 mshirkol Initial Creation. ( Fix for bug#5610891 ) */
/*-------------------------------------------------------------------------------------*/
Procedure LAUNCH_WFEXCEPTION_BEVENT(
p_return_status in varchar2,
p_msg_count in number,
p_msg_data in varchar2,
p_repair_line_id in number,
p_module_name in varchar2) IS
l_msg varchar2(2000);
select CSD_WF_EXCEPTIONS_BEVENT_S1.nextval from dual;