[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* description : procedure used to create/update */
/* delete charge lines. This */
/* procedure allows the overriding */
/* of the create/update/delete cost */
/* flag introduced in the Charges */
/* API for 12.1 release */
/*--------------------------------------------------*/
PROCEDURE PROCESS_ESTIMATE_LINES(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_action IN VARCHAR2,
p_cs_cost_flag IN VARCHAR2 := 'Y',
x_Charges_Rec IN OUT NOCOPY Cs_Charge_Details_Pub.Charges_Rec_Type,
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) := 'PROCESS_ESTIMATE_LINES';
ELSIF p_action = 'UPDATE'
THEN
IF (g_debug > 0)
THEN
Csd_Gen_Utility_Pvt.ADD('l_Charges_Rec.estimate_detail_id =' ||
x_Charges_Rec.estimate_detail_id);
SELECT business_process_id
INTO x_Charges_Rec.business_process_id
FROM cs_estimate_details
WHERE estimate_detail_id =
x_Charges_Rec.estimate_detail_id;
Cs_Charge_Details_Pub.Update_Charge_Details(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_transaction_control => Fnd_Api.G_TRUE,
p_Charges_Rec => x_Charges_Rec,
p_update_cost_detail => p_cs_cost_flag, -- swai: 12.1 service costing uptake bug 6960295
x_object_version_number => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Csd_Gen_Utility_Pvt.ADD('update_charge_details failed');
ELSIF p_action = 'DELETE'
THEN
IF (g_debug > 0)
THEN
Csd_Gen_Utility_Pvt.ADD('l_estimate_detail_id =' ||
l_estimate_detail_id);
Cs_Charge_Details_Pub.Delete_Charge_Details(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_transaction_control => Fnd_Api.G_TRUE,
p_estimate_detail_id => l_estimate_detail_id,
p_delete_cost_detail => p_cs_cost_flag, -- swai: 12.1 service costing uptake bug 6960295
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Csd_Gen_Utility_Pvt.ADD('Delete_Charge_Details failed ');
/* procedure name: update_ro_group_estimate */
/* description : procedure used to update */
/* repair group for estimate changes*/
/*--------------------------------------------------*/
PROCEDURE UPDATE_RO_GROUP_ESTIMATE(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_repair_line_id IN NUMBER,
x_object_version_number OUT NOCOPY 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) := 'UPDATE_RO_GROUP_ESTIMATE';
Csd_Gen_Utility_Pvt.ADD('Inside UPDATE_RO_GROUP_ESTIMATE Procedure p_repair_line_id =' ||
p_repair_line_id);
SAVEPOINT UPDATE_RO_GROUP_ESTIMATE;
SELECT grp.repair_group_id,
grp.group_quantity,
grp.object_version_number
INTO l_repair_group_id, l_group_quantity, l_group_ovn
FROM CSD_REPAIR_ORDER_GROUPS grp
WHERE EXISTS
(SELECT 'x'
FROM CSD_REPAIRS rep
WHERE rep.repair_group_id = grp.repair_group_id
AND rep.repair_line_id = p_repair_line_id);
SELECT COUNT(*)
INTO l_tot_approved
FROM CSD_REPAIRS
WHERE repair_group_id = l_repair_group_id
AND approval_status = 'A'
AND approval_required_flag = 'Y';
SELECT COUNT(*)
INTO l_tot_rejected
FROM CSD_REPAIRS
WHERE repair_group_id = l_repair_group_id
AND approval_status = 'R'
AND approval_required_flag = 'Y';
SELECT COUNT(*)
INTO l_tot_no_approval
FROM CSD_REPAIRS
WHERE repair_group_id = l_repair_group_id
AND approval_required_flag = 'N';
Csd_Gen_Utility_Pvt.ADD('CSD_REPAIR_ESTIMATE_PVT.UPDATE_RO_GROUP_ESTIMATE Update Group RO call');
Csd_Repair_Groups_Pvt.UPDATE_REPAIR_GROUPS(p_api_version => 1.0,
p_commit => 'F',
p_init_msg_list => 'T',
p_validation_level => Fnd_Api.g_valid_level_full,
x_repair_order_group_rec => l_rep_group_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
Csd_Gen_Utility_Pvt.ADD('CSD_REPAIR_ESTIMATE_PVT.UPDATE_RO_GROUP_ESTIMATE UPDATE_REPAIR_GROUPS :' ||
x_return_status);
ROLLBACK TO UPDATE_RO_GROUP_ESTIMATE;
ROLLBACK TO UPDATE_RO_GROUP_ESTIMATE;
ROLLBACK TO UPDATE_RO_GROUP_ESTIMATE;
END UPDATE_RO_GROUP_ESTIMATE;
SELECT COUNT(*)
INTO l_est_count
FROM CSD_REPAIR_ESTIMATE
WHERE repair_line_id = x_estimate_rec.repair_line_id;
SELECT lookup_code
INTO l_est_status_code
FROM fnd_lookups
WHERE lookup_type = 'CSD_ESTIMATE_STATUS'
AND lookup_code = x_estimate_rec.estimate_status;
Csd_Repair_Estimate_Pkg.Insert_Row(px_REPAIR_ESTIMATE_ID => x_estimate_rec.repair_estimate_id,
p_REPAIR_LINE_ID => x_estimate_rec.repair_line_id,
p_ESTIMATE_STATUS => x_estimate_rec.estimate_status,
p_ESTIMATE_DATE => x_estimate_rec.estimate_date,
p_WORK_SUMMARY => x_estimate_rec.work_summary,
p_PO_NUMBER => x_estimate_rec.po_number,
p_LEAD_TIME => x_estimate_rec.lead_time,
p_LEAD_TIME_UOM => x_estimate_rec.lead_time_uom,
p_ESTIMATE_FREEZE_FLAG => x_estimate_rec.estimate_freeze_flag,
p_ESTIMATE_REASON_CODE => x_estimate_rec.estimate_reason_code,
p_NOT_TO_EXCEED => x_estimate_rec.not_to_exceed,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_CREATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
p_ATTRIBUTE1 => x_estimate_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_estimate_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_estimate_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_estimate_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_estimate_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_estimate_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_estimate_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_estimate_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_estimate_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_estimate_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_estimate_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_estimate_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_estimate_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_estimate_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_estimate_rec.ATTRIBUTE15,
p_CONTEXT => x_estimate_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => 1);
UPDATE_RO_GROUP_ESTIMATE(p_api_version => 1.0,
p_commit => Fnd_Api.g_false,
p_init_msg_list => Fnd_Api.g_true,
p_validation_level => Fnd_Api.g_valid_level_full,
p_repair_line_id => x_estimate_rec.repair_line_id,
x_object_version_number => l_group_obj_ver_num,
x_return_status => l_api_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* procedure name: update_repair_estimate */
/* description : procedure used to update */
/* repair estimate lines */
/* */
/*--------------------------------------------------*/
PROCEDURE UPDATE_REPAIR_ESTIMATE(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
x_estimate_rec IN OUT NOCOPY REPAIR_ESTIMATE_REC,
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) := 'UPDATE_REPAIR_ESTIMATE';
SELECT a.repair_estimate_id,
a.object_version_number,
b.approval_status
FROM CSD_REPAIR_ESTIMATE a, CSD_REPAIRS b
WHERE a.repair_line_id = b.repair_line_id
AND a.repair_estimate_id = p_est_id;
SAVEPOINT update_repair_estimate;
SELECT lookup_code
INTO l_est_status_code
FROM fnd_lookups
WHERE lookup_type = 'CSD_ESTIMATE_STATUS'
AND lookup_code = x_estimate_rec.estimate_status;
Csd_Gen_Utility_Pvt.ADD('Calling CSD_REPAIR_ESTIMATE_PKG.Update_Row');
Csd_Repair_Estimate_Pkg.Update_Row(p_REPAIR_ESTIMATE_ID => x_estimate_rec.repair_estimate_id,
p_REPAIR_LINE_ID => x_estimate_rec.repair_line_id,
p_ESTIMATE_STATUS => x_estimate_rec.estimate_status,
p_ESTIMATE_DATE => x_estimate_rec.estimate_date,
p_WORK_SUMMARY => x_estimate_rec.work_summary,
p_PO_NUMBER => x_estimate_rec.po_number,
p_LEAD_TIME => x_estimate_rec.lead_time,
p_LEAD_TIME_UOM => x_estimate_rec.lead_time_uom,
p_ESTIMATE_FREEZE_FLAG => x_estimate_rec.estimate_freeze_flag,
p_ESTIMATE_REASON_CODE => x_estimate_rec.estimate_reason_code,
p_NOT_TO_EXCEED => x_estimate_rec.not_to_exceed,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_CREATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
p_ATTRIBUTE1 => x_estimate_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_estimate_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_estimate_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_estimate_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_estimate_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_estimate_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_estimate_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_estimate_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_estimate_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_estimate_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_estimate_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_estimate_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_estimate_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_estimate_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_estimate_rec.ATTRIBUTE15,
p_CONTEXT => x_estimate_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => l_obj_ver_num + 1);
UPDATE_RO_GROUP_ESTIMATE(p_api_version => 1.0,
p_commit => Fnd_Api.g_false,
p_init_msg_list => Fnd_Api.g_true,
p_validation_level => Fnd_Api.g_valid_level_full,
p_repair_line_id => x_estimate_rec.repair_line_id,
x_object_version_number => l_group_obj_ver_num,
x_return_status => l_api_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO update_repair_estimate;
ROLLBACK TO update_repair_estimate;
ROLLBACK TO update_repair_estimate;
END update_repair_estimate;
/* procedure name: delete_repair_estimate */
/* description : procedure used to delete */
/* repair estimate header */
/* */
/*--------------------------------------------------*/
PROCEDURE DELETE_REPAIR_ESTIMATE(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_estimate_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_ESTIMATE';
l_delete_allow VARCHAR2(1);
SAVEPOINT delete_repair_estimate;
SELECT b.approval_status
INTO l_approval_status
FROM CSD_REPAIR_ESTIMATE a, CSD_REPAIRS b
WHERE a.repair_line_id = b.repair_line_id
AND a.repair_estimate_id = p_estimate_id;
SELECT COUNT(*)
INTO l_est_line_count
FROM CSD_REPAIR_ESTIMATE_LINES
WHERE repair_estimate_id = p_estimate_id;
Csd_Gen_Utility_Pvt.ADD('Call CSD_REPAIR_ESTIMATE_PKG.Delete_Row');
Csd_Repair_Estimate_Pkg.Delete_Row(p_REPAIR_ESTIMATE_ID => p_estimate_id);
Fnd_Message.SET_NAME('CSD', 'CSD_EST_DELETE_NOT_ALLOWED');
ROLLBACK TO delete_repair_estimate;
ROLLBACK TO delete_repair_estimate;
ROLLBACK TO delete_repair_estimate;
END delete_repair_estimate;
SELECT customer_id, account_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT incident_id
INTO l_incident_id
FROM CSD_REPAIRS
WHERE repair_line_id = x_estimate_line_rec.repair_line_id;
SELECT cov.actual_coverage_id ,
cov.coverage_name,
ent.txn_group_id
INTO l_coverage_id,
l_coverage_name,
l_txn_group_id
FROM oks_ent_coverages_v cov,
oks_ent_txn_groups_v ent
WHERE cov.contract_number = x_estimate_line_rec.contract_number -- travi change
AND cov.actual_coverage_id = ent.coverage_id
AND ent.business_process_id = l_bus_process_id;
SELECT currency_code
INTO l_curr_code
FROM oe_price_lists
WHERE price_list_id = x_estimate_line_rec.price_list_id;
Csd_Gen_Utility_Pvt.ADD('Call csd_repair_estimate_lines_pkg.insert_row to create repair estimate lines ');
Csd_Repair_Estimate_Lines_Pkg.Insert_Row(px_REPAIR_ESTIMATE_LINE_ID => x_estimate_line_rec.repair_estimate_line_id,
p_REPAIR_ESTIMATE_ID => x_estimate_line_rec.repair_estimate_id,
p_ESTIMATE_DETAIL_ID => l_Charges_Rec.estimate_detail_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_CREATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
p_ITEM_COST => x_estimate_line_rec.item_cost,
p_RESOURCE_ID => x_estimate_line_rec.resource_id,
p_OVERRIDE_CHARGE_FLAG => x_estimate_line_rec.override_charge_flag,
p_JUSTIFICATION_NOTES => x_estimate_line_rec.justification_notes,
p_ATTRIBUTE1 => x_estimate_line_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_estimate_line_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_estimate_line_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_estimate_line_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_estimate_line_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_estimate_line_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_estimate_line_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_estimate_line_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_estimate_line_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_estimate_line_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_estimate_line_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_estimate_line_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_estimate_line_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_estimate_line_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_estimate_line_rec.ATTRIBUTE15,
p_CONTEXT => x_estimate_line_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => 1,
p_EST_LINE_SOURCE_TYPE_CODE => x_estimate_line_rec.EST_LINE_SOURCE_TYPE_CODE,
p_EST_LINE_SOURCE_ID1 => x_estimate_line_rec.EST_LINE_SOURCE_ID1,
p_EST_LINE_SOURCE_ID2 => x_estimate_line_rec.EST_LINE_SOURCE_ID2,
p_RO_SERVICE_CODE_ID => x_estimate_line_rec.RO_SERVICE_CODE_ID);
/* procedure name: update_repair_estimate_lines */
/* description : procedure used to update */
/* repair estimate lines */
/* */
/*--------------------------------------------------*/
PROCEDURE UPDATE_REPAIR_ESTIMATE_LINES(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
x_estimate_line_rec IN OUT NOCOPY CSD_REPAIR_ESTIMATE_PVT.REPAIR_ESTIMATE_LINE_REC,
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) := 'UPDATE_REPAIR_ESTIMATE_LINES';
SELECT estimate_detail_id, object_version_number
FROM CSD_REPAIR_ESTIMATE_LINES
WHERE repair_estimate_line_id = p_est_line_id;
SAVEPOINT update_repair_estimate_lines;
SELECT business_process_id
INTO l_bus_process_id
FROM cs_estimate_details
WHERE estimate_detail_id = l_est_detail_id
AND order_header_id IS NULL;
SELECT cov.actual_coverage_id ,
-- cov.coverage_name, -- Commented for bugfix 3617932
ent.txn_group_id
into l_coverage_id,
-- l_coverage_name, -- Commented for bugfix 3617932
l_txn_group_id
FROM oks_ent_coverages_v cov,
oks_ent_txn_groups_v ent
WHERE cov.contract_number = x_estimate_line_rec.contract_number -- takwong, fixed bug#2510068
-- cov.contract_line_id = x_estimate_line_rec.contract_id
AND cov.actual_coverage_id = ent.coverage_id
AND ent.business_process_id = l_bus_process_id;
Csd_Gen_Utility_Pvt.ADD('Call process_estimate_lines to update charge lines ');
p_action => 'UPDATE',
x_Charges_Rec => l_Charges_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END IF; -- end of update charge line
Csd_Gen_Utility_Pvt.ADD('Call csd_repair_estimate_line_pkg.update_row to update the repair estimate');
Csd_Repair_Estimate_Lines_Pkg.Update_Row(p_REPAIR_ESTIMATE_LINE_ID => x_estimate_line_rec.repair_estimate_line_id,
p_REPAIR_ESTIMATE_ID => x_estimate_line_rec.repair_estimate_id,
p_ESTIMATE_DETAIL_ID => x_estimate_line_rec.estimate_detail_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_CREATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_LOGIN => Fnd_Global.USER_ID,
p_ITEM_COST => x_estimate_line_rec.item_cost,
p_RESOURCE_ID => x_estimate_line_rec.resource_id,
p_OVERRIDE_CHARGE_FLAG => x_estimate_line_rec.override_charge_flag,
p_JUSTIFICATION_NOTES => x_estimate_line_rec.justification_notes,
p_ATTRIBUTE1 => x_estimate_line_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_estimate_line_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_estimate_line_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_estimate_line_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_estimate_line_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_estimate_line_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_estimate_line_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_estimate_line_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_estimate_line_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_estimate_line_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_estimate_line_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_estimate_line_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_estimate_line_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_estimate_line_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_estimate_line_rec.ATTRIBUTE15,
p_CONTEXT => x_estimate_line_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => l_obj_ver_num + 1,
p_EST_LINE_SOURCE_TYPE_CODE => x_estimate_line_rec.EST_LINE_SOURCE_TYPE_CODE,
p_EST_LINE_SOURCE_ID1 => x_estimate_line_rec.EST_LINE_SOURCE_ID1,
p_EST_LINE_SOURCE_ID2 => x_estimate_line_rec.EST_LINE_SOURCE_ID2,
p_RO_SERVICE_CODE_ID => x_estimate_line_rec.RO_SERVICE_CODE_ID);
ROLLBACK TO update_repair_estimate_lines;
ROLLBACK TO update_repair_estimate_lines;
ROLLBACK TO update_repair_estimate_lines;
END update_repair_estimate_lines;
/* procedure name: delete_repair_estimate_lines */
/* description : procedure used to delete */
/* repair estimate lines */
/* */
/*--------------------------------------------------*/
PROCEDURE DELETE_REPAIR_ESTIMATE_LINES(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_estimate_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_ESTIMATE_LINES';
l_delete_allow VARCHAR2(1);
SAVEPOINT delete_repair_estimate_lines;
Csd_Gen_Utility_Pvt.ADD('check if rocord is allowed to delete');
SELECT a.estimate_detail_id
INTO l_est_detail_id
FROM CSD_REPAIR_ESTIMATE_LINES a, cs_estimate_details b
WHERE a.estimate_detail_id = b.estimate_detail_id
AND a.repair_estimate_line_id = p_estimate_line_id
AND b.order_header_id IS NULL;
l_delete_allow := 'Y';
l_delete_allow := 'N';
Csd_Gen_Utility_Pvt.ADD('Estimate Line is interfaced,so it cannot be deleted');
Fnd_Message.SET_NAME('CSD', 'CSD_API_DELETE_NOT_ALLOWED');
Csd_Gen_Utility_Pvt.ADD('l_delete_allow =' ||
l_delete_allow);
IF l_delete_allow = 'Y'
THEN
l_Charges_Rec.estimate_detail_id := l_est_detail_id;
Csd_Gen_Utility_Pvt.ADD('Call process_estimate_lines to delete');
p_action => 'DELETE',
x_Charges_Rec => l_Charges_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Csd_Gen_Utility_Pvt.ADD('Call csd_product_transactions_pkg.Delete_Row');
Csd_Repair_Estimate_Lines_Pkg.Delete_Row(p_REPAIR_ESTIMATE_LINE_ID => p_estimate_line_id);
END IF; --end of delete
ROLLBACK TO delete_repair_estimate_lines;
ROLLBACK TO delete_repair_estimate_lines;
ROLLBACK TO delete_repair_estimate_lines;
END delete_repair_estimate_lines;
SELECT template_code
FROM fnd_concurrent_programs
WHERE concurrent_program_name = p_xdo_conc_name; -- case sensitive
SELECT lower(iso_language), iso_territory
FROM fnd_languages
WHERE language_code = userenv('LANG');
SELECT ced.estimate_detail_id, ced.purchase_order_num
FROM CSD_REPAIR_ESTIMATE cre,
CSD_REPAIR_ESTIMATE_LINES crel,
cs_estimate_details ced
WHERE cre.repair_line_id = p_rep_line_id
AND cre.repair_estimate_id = crel.repair_estimate_id
AND crel.estimate_detail_id = ced.estimate_detail_id
AND ced.order_header_id IS NULL
AND ced.interface_to_oe_flag = 'N';
SELECT original_source_header_id, original_source_reference
INTO l_org_src_header_id, l_org_src_ref
FROM CSD_REPAIRS
WHERE repair_line_id = p_repair_line_id;
SELECT header_id
INTO l_order_header_id
FROM oe_order_headers_all ooh, oe_order_types_v oot
WHERE ooh.order_type_id = oot.order_type_id
AND ooh.header_id = l_org_src_header_id
AND oot.order_category_code IN ('MIXED', 'ORDER');
SELECT MAX(ced.order_header_id)
INTO l_order_header_id
FROM CSD_PRODUCT_TRANSACTIONS cpt,
cs_estimate_details ced,
oe_order_headers_all ooh,
oe_order_types_v oot
WHERE ooh.order_type_id = oot.order_type_id
AND ooh.header_id = ced.order_header_id
AND oot.order_category_code IN ('MIXED', 'ORDER')
AND cpt.estimate_detail_id = ced.estimate_detail_id
AND cpt.repair_line_id = p_repair_line_id
AND ced.order_header_id IS NOT NULL
AND ced.interface_to_oe_flag = 'Y';
SELECT ced.purchase_order_num
INTO l_orig_po_num
FROM CSD_PRODUCT_TRANSACTIONS cpt,
cs_estimate_details ced
WHERE cpt.estimate_detail_id =
ced.estimate_detail_id
AND cpt.repair_line_id = p_repair_line_id
AND ced.order_header_id =
l_est_line_rec.order_header_id
AND ced.purchase_order_num =
est.purchase_order_num;
p_action => 'UPDATE',
p_Charges_Rec => l_est_line_rec,
x_estimate_detail_id => l_estimate_detail_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT cia.incident_id, cia.customer_id, cia.account_id
INTO l_incident_id, l_party_id, l_account_id
FROM cs_incidents_all_b cia, CSD_REPAIRS cr
WHERE cia.incident_id = cr.incident_id
AND cr.repair_line_id = p_repair_line_id;
SELECT oe.order_number
INTO l_order_number
FROM oe_order_headers_all oe, cs_estimate_details ced
WHERE oe.header_id = ced.order_header_id
AND ced.estimate_detail_id = l_last_est_detail_id;
SELECT SUM(NVL(charge, 0))
INTO x_estimated_charge
FROM csd_repair_estimate_lines_v
WHERE repair_line_id = p_repair_line_id
AND billing_type IN ('M', 'L', 'E');
SELECT object_version_number
FROM CSD_RO_SERVICE_CODES
WHERE ro_service_code_id = p_ro_service_code_id;
SELECT default_po_num
FROM CSD_REPAIRS
WHERE repair_line_id = p_repair_line_id;
SELECT COUNT(*)
INTO l_num_service_codes
FROM CSD_RO_SERVICE_CODES
WHERE repair_line_id = p_repair_line_id
AND NVL(applicable_flag, 'N') = 'Y'
AND NVL(applied_to_est_flag, 'N') = 'N';
'Calling CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code');
Csd_Ro_Service_Codes_Pvt.Update_RO_Service_Code(p_api_version => 1.0,
p_commit => Fnd_Api.G_FALSE,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => 0,
p_ro_service_code_rec => l_ro_sc_rec_type,
x_obj_ver_number => l_obj_ver_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_est_lines_tbl(i)
.inventory_item_id
AND organization_id = l_est_lines_tbl(i)
.organization_id;
SELECT meaning
INTO l_repair_mode_name
FROM fnd_lookups
WHERE lookup_type = 'CSD_REPAIR_MODE'
AND lookup_code = p_repair_mode;
selecting from the query.
The cursors are replaced by new ones.
Following changes are made to the existing cursors -
-- Cursors do not accept p_price_list_id as IN parameter anymore.
-- They do not select the list_price from the QP view.
-- The reference to QP view is removed from the FROM part of statements.
-- All joins for the QP view commented in the WHERE clause.
-- The selling price is derived in the Convert_to_est_lines procedure
by calling CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE API.
-- Cursor gets:
-- Information to create material lines from task groups attached to
-- srvice codes linked to the repair line id.
cursor c_material_lines_from_sc (p_repair_line_id NUMBER,
p_price_list_id NUMBER) is
SELECT b.inventory_item_id,
b.primary_uom_code uom,
b.manual_quantity manual_qty,
ceil(b.rollup_quantity_used/b.rollup_times_used) rollup_qty,
a.actual_times_used times_used,
c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SERVICE_CODE' est_line_source_type_code,
ROSC.Service_code_id est_line_source_id1,
null est_line_source_id2,
ROSC.RO_Service_code_id
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SERVICE_CODES_VL SC,
CSD_SC_WORK_ENTITIES WRK,
CSP_PRODUCT_TASKS a,
CSP_TASK_PARTS b,
QP_PRICE_LIST_LINES_V c,
MTL_SYSTEM_ITEMS_KFV d,
CSD_REPAIR_TYPES_SAR e,
CS_TXN_BILLING_TYPES f,
JTF_TASK_TEMPLATES_VL g
WHERE ROSC.repair_line_id = p_repair_line_id
and ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
and ROSC.applied_to_est_flag = 'N'
and WRK.Service_code_id = ROSC.Service_code_id
and WRK.work_entity_type_code = 'TASK'
and SC.Service_code_Id = ROSC.Service_code_id
and nvl(SC.active_from, SYSDATE) <= SYSDATE
and nvl(SC.active_to, SYSDATE) >= SYSDATE
and g.task_group_id = WRK.work_entity_id1
and a.task_template_id = g.task_template_id
and a.product_id = p_inventory_item_id
and b.product_task_id = a.product_task_id
and (b.manual_quantity IS NOT NULL OR
b.rollup_quantity_used IS NOT NULL)
and b.substitute_item IS NULL
and c.price_list_id(+) = p_price_list_id
and c.inventory_item_id(+) = b.inventory_item_id
and c.unit_code(+) = b.primary_uom_code
and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
and d.inventory_item_id = b.inventory_item_id
and d.organization_id = p_organization_id
and e.repair_type_id = p_repair_type_id
and f.txn_billing_type_id = e.txn_billing_type_id(+)
and f.billing_type = d.material_billable_flag(+)
and d.material_billable_flag IS NOT NULL;
SELECT p_labor_inventory_item_id inventory_item_id,
b.planned_effort_uom uom,
b.planned_effort quantity,
c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SERVICE_CODE' est_line_source_type_code,
ROSC.Service_code_id est_line_source_id1,
null est_line_source_id2,
ROSC.RO_Service_code_id
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SERVICE_CODES_VL SC,
CSD_SC_WORK_ENTITIES WRK,
JTF_TASK_TEMPLATES_B b,
QP_PRICE_LIST_LINES_V c,
MTL_SYSTEM_ITEMS_KFV d,
CSD_REPAIR_TYPES_SAR e,
CS_TXN_BILLING_TYPES f,
JTF_TASK_TEMPLATES_VL g
WHERE ROSC.repair_line_id = p_repair_line_id
and ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
and ROSC.applied_to_est_flag = 'N'
and WRK.Service_code_id = ROSC.Service_code_id
and WRK.work_entity_type_code = 'TASK'
and SC.Service_code_Id = ROSC.Service_code_id
and nvl(SC.active_from, SYSDATE) <= SYSDATE
and nvl(SC.active_to, SYSDATE) >= SYSDATE
and g.task_group_id = WRK.work_entity_id1
and b.task_template_id = g.task_template_id
and b.planned_effort is NOT NULL
and b.planned_effort_uom is NOT NULL
and c.price_list_id(+) = p_price_list_id
and c.inventory_item_id(+) = p_labor_inventory_item_id
and c.unit_code(+) = b.planned_effort_uom
and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
and d.inventory_item_id = p_labor_inventory_item_id
and d.organization_id = p_organization_id
and e.repair_type_id = p_repair_type_id
and f.txn_billing_type_id = e.txn_billing_type_id(+)
and f.billing_type = d.material_billable_flag(+)
and d.material_billable_flag IS NOT NULL;
SELECT b.inventory_item_id,
b.primary_uom_code uom,
b.manual_quantity manual_qty,
ceil(b.rollup_quantity_used/b.rollup_times_used) rollup_qty,
a.actual_times_used times_used,
c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SOLUTION' est_line_source_type_code,
SetLinkRO.set_id est_line_source_id1,
null est_line_source_id2,
null ro_service_code_id
FROM
cs_kb_set_links SetLinkTG, --link to task group
cs_kb_set_links SetLinkRO, -- link to repair order
cs_kb_sets_vl KBSets,
jtf_task_temp_groups_vl JtfTaskTempGroupEO,
csp_product_tasks a,
csp_task_parts b,
qp_price_list_lines_v c,
mtl_system_items_kfv d,
csd_repair_types_sar e,
cs_txn_billing_types f,
jtf_task_templates_vl g
WHERE
g.task_group_id = JtfTaskTempGroupEO.task_template_group_id
and a.task_template_id = g.task_template_id
and a.product_id = p_inventory_item_id
and b.product_task_id = a.product_task_id
and (b.manual_quantity IS NOT NULL OR
b.rollup_quantity_used IS NOT NULL)
and b.substitute_item IS NULL
and c.price_list_id(+) = p_price_list_id
and c.inventory_item_id(+) = b.inventory_item_id
and c.unit_code(+) = b.primary_uom_code
and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
and d.inventory_item_id = b.inventory_item_id
and d.material_billable_flag IS NOT NULL
and d.organization_id = p_organization_id
and e.repair_type_id = p_repair_type_id
and f.txn_billing_type_id = e.txn_billing_type_id(+)
and f.billing_type = d.material_billable_flag(+)
and SetLinkRO.other_id = p_repair_line_id
and SetLinkRO.object_code = 'DR'
and SetLinkRO.link_type = 'S'
and SetLinkTG.set_id = SetLinkRO.set_id
and SetLinkTG.other_id = JtfTaskTempGroupEO.task_template_group_id
and SetLinkTG.object_code = 'CS_KB_TASK_TEMPLATE_GRP'
and JtfTaskTempGroupEO.application_id = 512 -- depot repair task groups only
and KBSets.set_id = SetLinkTG.set_id
and KBSets.status = 'PUB' -- only published solutions
and not exists -- not already brought in
( select 'x'
from csd_repair_estimate_lines_v csd
where csd.repair_line_id = SetLinkRO.other_id
and csd.est_line_source_type_code = 'SOLUTION'
and csd.est_line_source_id1 = KBSets.set_id);
SELECT p_labor_inventory_item_id inventory_item_id,
b.planned_effort_uom uom,
b.planned_effort quantity,
c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SOLUTION' est_line_source_type_code,
SetLinkRO.set_id est_line_source_id1,
null est_line_source_id2,
null ro_service_code_id
FROM
cs_kb_set_links SetLinkTG, --link to task group
cs_kb_set_links SetLinkRO, -- link to repair order
cs_kb_sets_vl KBSets,
jtf_task_temp_groups_vl JtfTaskTempGroupEO,
jtf_task_templates_b b,
qp_price_list_lines_v c,
mtl_system_items_kfv d,
csd_repair_types_sar e,
cs_txn_billing_types f,
jtf_task_templates_vl g
WHERE
SetLinkRO.other_id = p_repair_line_id
and SetLinkRO.object_code = 'DR'
and SetLinkRO.link_type = 'S'
and SetLinkTG.set_id = SetLinkRO.set_id
and SetLinkTG.other_id = JtfTaskTempGroupEO.task_template_group_id
and SetLinkTG.object_code = 'CS_KB_TASK_TEMPLATE_GRP'
and g.task_group_id = JtfTaskTempGroupEO.task_template_group_id
and b.task_template_id = g.task_template_id
and b.planned_effort is NOT NULL
and b.planned_effort_uom is NOT NULL
and c.price_list_id(+) = p_price_list_id
and c.inventory_item_id(+) = p_labor_inventory_item_id
and c.unit_code(+) = b.planned_effort_uom
and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
and d.inventory_item_id = p_labor_inventory_item_id
and d.organization_id = p_organization_id
and e.repair_type_id = p_repair_type_id
and f.txn_billing_type_id = e.txn_billing_type_id(+)
and f.billing_type = d.material_billable_flag(+)
and d.material_billable_flag IS NOT NULL
and JtfTaskTempGroupEO.application_id = 512 -- depot repair task groups only
and KBSets.set_id = SetLinkTG.set_id
and KBSets.status = 'PUB' -- only published solutions
and not exists -- not already brought in
( select 'x'
from csd_repair_estimate_lines_v csd
where csd.repair_line_id = SetLinkRO.other_id
and csd.est_line_source_type_code = 'SOLUTION'
and csd.est_line_source_id1 = KBSets.set_id);
SELECT b.inventory_item_id,
b.primary_uom_code uom,
b.manual_quantity manual_qty,
CEIL(b.rollup_quantity_used / b.rollup_times_used) rollup_qty,
a.actual_times_used times_used,
-- c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SERVICE_CODE' est_line_source_type_code,
ROSC.Service_code_id est_line_source_id1,
NULL est_line_source_id2,
ROSC.RO_Service_code_id
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SERVICE_CODES_VL SC,
CSD_SC_WORK_ENTITIES WRK,
CSP_PRODUCT_TASKS a,
CSP_TASK_PARTS b,
-- QP_PRICE_LIST_LINES_V c,
MTL_SYSTEM_ITEMS_KFV d,
CSD_REPAIR_TYPES_SAR e,
CS_TXN_BILLING_TYPES f,
JTF_TASK_TEMPLATES_VL g
WHERE ROSC.repair_line_id = p_repair_line_id
AND ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
AND ROSC.applied_to_est_flag = 'N'
AND WRK.Service_code_id = ROSC.Service_code_id
AND WRK.work_entity_type_code = 'TASK'
AND SC.Service_code_Id = ROSC.Service_code_id
AND NVL(SC.active_from, SYSDATE) <= SYSDATE
AND NVL(SC.active_to, SYSDATE) >= SYSDATE
AND g.task_group_id = WRK.work_entity_id1
AND a.task_template_id = g.task_template_id
AND a.product_id = p_inventory_item_id
AND b.product_task_id = a.product_task_id
AND (b.manual_quantity IS NOT NULL OR
b.rollup_quantity_used IS NOT NULL)
AND b.substitute_item IS NULL
-- Following lines commented by vkjain, bugfix 3468680
-- and c.price_list_id(+) = p_price_list_id
-- and c.inventory_item_id(+) = b.inventory_item_id
-- and c.unit_code(+) = b.primary_uom_code
-- and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
-- and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
AND d.inventory_item_id = b.inventory_item_id
AND d.organization_id = p_organization_id
AND e.repair_type_id = p_repair_type_id
AND f.txn_billing_type_id = e.txn_billing_type_id(+)
AND f.billing_type = d.material_billable_flag(+)
AND d.material_billable_flag IS NOT NULL;
SELECT p_labor_inventory_item_id inventory_item_id,
b.planned_effort_uom uom,
b.planned_effort quantity,
-- c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SERVICE_CODE' est_line_source_type_code,
ROSC.Service_code_id est_line_source_id1,
NULL est_line_source_id2,
ROSC.RO_Service_code_id
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SERVICE_CODES_VL SC,
CSD_SC_WORK_ENTITIES WRK,
JTF_TASK_TEMPLATES_B b,
-- QP_PRICE_LIST_LINES_V c,
MTL_SYSTEM_ITEMS_KFV d,
CSD_REPAIR_TYPES_SAR e,
CS_TXN_BILLING_TYPES f,
JTF_TASK_TEMPLATES_VL g
WHERE ROSC.repair_line_id = p_repair_line_id
AND ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
AND ROSC.applied_to_est_flag = 'N'
AND WRK.Service_code_id = ROSC.Service_code_id
AND WRK.work_entity_type_code = 'TASK'
AND SC.Service_code_Id = ROSC.Service_code_id
AND NVL(SC.active_from, SYSDATE) <= SYSDATE
AND NVL(SC.active_to, SYSDATE) >= SYSDATE
AND g.task_group_id = WRK.work_entity_id1
AND b.task_template_id = g.task_template_id
AND b.planned_effort IS NOT NULL
AND b.planned_effort_uom IS NOT NULL
-- Following lines commented by vkjain, bugfix 3468680
-- and c.price_list_id(+) = p_price_list_id
-- and c.inventory_item_id(+) = p_labor_inventory_item_id
-- and c.unit_code(+) = b.planned_effort_uom
-- and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
-- and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
AND d.inventory_item_id = p_labor_inventory_item_id
AND d.organization_id = p_organization_id
AND e.repair_type_id = p_repair_type_id
AND f.txn_billing_type_id = e.txn_billing_type_id(+)
AND f.billing_type = d.material_billable_flag(+)
AND d.material_billable_flag IS NOT NULL;
SELECT b.inventory_item_id,
b.primary_uom_code uom,
b.manual_quantity manual_qty,
CEIL(b.rollup_quantity_used / b.rollup_times_used) rollup_qty,
a.actual_times_used times_used,
-- c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SOLUTION' est_line_source_type_code,
SetLinkRO.set_id est_line_source_id1,
NULL est_line_source_id2,
NULL ro_service_code_id
FROM cs_kb_set_links SetLinkTG, --link to task group
cs_kb_set_links SetLinkRO, -- link to repair order
cs_kb_sets_vl KBSets,
jtf_task_temp_groups_vl JtfTaskTempGroupEO,
csp_product_tasks a,
csp_task_parts b,
-- qp_price_list_lines_v c,
mtl_system_items_kfv d,
CSD_REPAIR_TYPES_SAR e,
cs_txn_billing_types f,
jtf_task_templates_vl g
WHERE g.task_group_id =
JtfTaskTempGroupEO.task_template_group_id
AND a.task_template_id = g.task_template_id
AND a.product_id = p_inventory_item_id
AND b.product_task_id = a.product_task_id
AND (b.manual_quantity IS NOT NULL OR
b.rollup_quantity_used IS NOT NULL)
AND b.substitute_item IS NULL
-- Following lines commented by vkjain, bugfix 3468680
-- and c.price_list_id(+) = p_price_list_id
-- and c.inventory_item_id(+) = b.inventory_item_id
-- and c.unit_code(+) = b.primary_uom_code
-- and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
-- and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
AND d.inventory_item_id = b.inventory_item_id
AND d.material_billable_flag IS NOT NULL
AND d.organization_id = p_organization_id
AND e.repair_type_id = p_repair_type_id
AND f.txn_billing_type_id = e.txn_billing_type_id(+)
AND f.billing_type = d.material_billable_flag(+)
AND SetLinkRO.other_id = p_repair_line_id
AND SetLinkRO.object_code = 'DR'
AND SetLinkRO.link_type = 'S'
AND SetLinkTG.set_id = SetLinkRO.set_id
AND SetLinkTG.other_id =
JtfTaskTempGroupEO.task_template_group_id
AND SetLinkTG.object_code = 'CS_KB_TASK_TEMPLATE_GRP'
AND JtfTaskTempGroupEO.application_id = 512 -- depot repair task groups only
AND KBSets.set_id = SetLinkTG.set_id
AND KBSets.status = 'PUB' -- only published solutions
AND NOT EXISTS -- not already brought in
(SELECT 'x'
FROM csd_repair_estimate_lines_v csd
WHERE csd.repair_line_id = SetLinkRO.other_id
AND csd.est_line_source_type_code = 'SOLUTION'
AND csd.est_line_source_id1 = KBSets.set_id);
SELECT p_labor_inventory_item_id inventory_item_id,
b.planned_effort_uom uom,
b.planned_effort quantity,
-- c.list_price selling_price,
d.concatenated_segments item_name,
d.comms_nl_trackable_flag,
f.txn_billing_type_id,
'SOLUTION' est_line_source_type_code,
SetLinkRO.set_id est_line_source_id1,
NULL est_line_source_id2,
NULL ro_service_code_id
FROM cs_kb_set_links SetLinkTG, --link to task group
cs_kb_set_links SetLinkRO, -- link to repair order
cs_kb_sets_vl KBSets,
jtf_task_temp_groups_vl JtfTaskTempGroupEO,
jtf_task_templates_b b,
-- qp_price_list_lines_v c,
mtl_system_items_kfv d,
CSD_REPAIR_TYPES_SAR e,
cs_txn_billing_types f,
jtf_task_templates_vl g
WHERE SetLinkRO.other_id = p_repair_line_id
AND SetLinkRO.object_code = 'DR'
AND SetLinkRO.link_type = 'S'
AND SetLinkTG.set_id = SetLinkRO.set_id
AND SetLinkTG.other_id =
JtfTaskTempGroupEO.task_template_group_id
AND SetLinkTG.object_code = 'CS_KB_TASK_TEMPLATE_GRP'
AND g.task_group_id =
JtfTaskTempGroupEO.task_template_group_id
AND b.task_template_id = g.task_template_id
AND b.planned_effort IS NOT NULL
AND b.planned_effort_uom IS NOT NULL
-- Following lines commented by vkjain, bugfix 3468680
-- and c.price_list_id(+) = p_price_list_id
-- and c.inventory_item_id(+) = p_labor_inventory_item_id
-- and c.unit_code(+) = b.planned_effort_uom
-- and nvl(c.start_date_active, SYSDATE) <= SYSDATE -- swai added
-- and nvl(c.end_date_active, SYSDATE) >= SYSDATE -- swai added
AND d.inventory_item_id = p_labor_inventory_item_id
AND d.organization_id = p_organization_id
AND e.repair_type_id = p_repair_type_id
AND f.txn_billing_type_id = e.txn_billing_type_id(+)
AND f.billing_type = d.material_billable_flag(+)
AND d.material_billable_flag IS NOT NULL
AND JtfTaskTempGroupEO.application_id = 512 -- depot repair task groups only
AND KBSets.set_id = SetLinkTG.set_id
AND KBSets.status = 'PUB' -- only published solutions
AND NOT EXISTS -- not already brought in
(SELECT 'x'
FROM csd_repair_estimate_lines_v csd
WHERE csd.repair_line_id = SetLinkRO.other_id
AND csd.est_line_source_type_code = 'SOLUTION'
AND csd.est_line_source_id1 = KBSets.set_id);
SELECT KBSets.set_id solution_id, -- solution id
JtfTaskTempGroupEO.task_template_group_id task_group_id, -- task template group id
JtfTaskTempGroupEO.template_group_name task_group_name -- task template group name
FROM cs_kb_set_links SetLinkTG, --link to task group
cs_kb_set_links SetLinkRO, -- link to repair order
cs_kb_sets_vl KBSets,
jtf_task_temp_groups_vl JtfTaskTempGroupEO,
CSD_REPAIRS repairs
WHERE SetLinkRO.other_id = p_repair_line_id
AND SetLinkRO.object_code = 'DR'
AND SetLinkRO.link_type = 'S'
AND SetLinkTG.set_id = SetLinkRO.set_id
AND SetLinkTG.other_id =
JtfTaskTempGroupEO.task_template_group_id
AND SetLinkTG.object_code = 'CS_KB_TASK_TEMPLATE_GRP'
AND JtfTaskTempGroupEO.application_id = 512 -- depot repair task groups only
AND KBSets.set_id = SetLinkTG.set_id
AND KBSets.status = 'PUB' -- only published solutions
AND repairs.repair_line_id = SetLinkRO.other_id
AND NOT EXISTS -- not already brought in
(SELECT 'x'
FROM csd_repair_estimate_lines_v csd
WHERE csd.repair_line_id = SetLinkRO.other_id
AND csd.est_line_source_type_code = 'SOLUTION'
AND csd.est_line_source_id1 = KBSets.set_id)
AND NOT EXISTS -- no template for this product
(SELECT 'x'
FROM csp_product_tasks x,
jtf_task_templates_vl y,
jtf_task_temp_groups_vl z
WHERE x.product_id = repairs.inventory_item_id
AND x.task_template_id = y.task_template_id
AND y.task_group_id = SetLinkTG.other_id);
selecting from the query.
The cursors are replaced by new ones.
Following changes are made to the existing cursors -
-- Cursors do not accept p_price_list_id as IN parameter anymore.
-- They do not select the list_price from the QP view.
-- The reference to QP view is removed from the FROM part of statements.
-- All joins for the QP view commented in the WHERE clause.
-- The selling price is derived in the Convert_to_est_lines procedure
by calling CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE API.
cursor c_material_lines_from_bom (p_repair_line_id NUMBER,
p_repair_type_id NUMBER,
p_price_list_id NUMBER) is
SELECT BOM.component_item_id INVENTORY_ITEM_ID,
BOM.primary_uom_code UOM,
BOM.component_quantity QUANTITY,
PRICE.list_price SELLING_PRICE,
MTL.concatenated_segments ITEM_NAME,
MTL.comms_nl_trackable_flag COMMS_NL_TRACKABLE_FLAG,
SAR.txn_billing_type_id TXN_BILLING_TYPE_ID,
'SERVICE_CODE' EST_LINE_SOURCE_TYPE_CODE,
ROSC.Service_Code_id EST_LINE_SOURCE_ID1,
null EST_LINE_SOURCE_ID2,
ROSC.RO_Service_Code_id RO_SERVICE_CODE_ID
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SERVICE_CODES_VL SC,
CSD_SC_WORK_ENTITIES WRK,
BOM_INVENTORY_COMPONENTS_V BOM,
QP_PRICE_LIST_LINES_V PRICE,
MTL_SYSTEM_ITEMS_KFV MTL,
CSD_REPAIR_TYPES_SAR RTSAR,
CS_TXN_BILLING_TYPES SAR
WHERE ROSC.repair_line_id = p_repair_line_id
and ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
and ROSC.applied_to_est_flag = 'N'
and WRK.Service_code_id = ROSC.Service_code_id
and WRK.work_entity_type_code = 'BOM'
and SC.Service_code_Id = ROSC.Service_code_id
and nvl(SC.active_from, SYSDATE) <= SYSDATE
and nvl(SC.active_to, SYSDATE) >= SYSDATE
and WRK.work_entity_id3 = cs_std.get_item_valdn_orgzn_id
-- and WRK.work_entity_id1 IS NOT NULL
and BOM.bill_sequence_id = WRK.work_entity_id1
and nvl(BOM.effectivity_date , SYSDATE) <= SYSDATE -- swai bug 3323274
and nvl(BOM.disable_date , SYSDATE) >= SYSDATE -- swai bug 3323274
and PRICE.price_list_id(+) = p_price_list_id
and PRICE.inventory_item_id(+) = BOM.component_item_id
and PRICE.unit_code(+) = BOM.primary_uom_code
and nvl(PRICE.start_date_active, SYSDATE) <= SYSDATE -- swai added
and nvl(PRICE.end_date_active, SYSDATE) >= SYSDATE -- swai added
and MTL.inventory_item_id = BOM.component_item_id
and MTL.organization_id = WRK.work_entity_id3
and RTSAR.repair_type_id = p_repair_type_id
and SAR.txn_billing_type_id = RTSAR.txn_billing_type_id(+)
and SAR.billing_type = MTL.material_billable_flag(+)
and MTL.material_billable_flag IS NOT NULL;
SELECT
RES.billable_item_id INVENTORY_ITEM_ID,
RES.unit_of_measure UOM,
-- OPRES.assigned_units QUANTITY, -- Replaced by following line to fix 3365436. vkjain.
OPRES.usage_rate_or_amount QUANTITY,
PRICE.list_price SELLING_PRICE,
MTL.concatenated_segments ITEM_NAME,
MTL.comms_nl_trackable_flag COMMS_NL_TRACKABLE_FLAG,
SAR.txn_billing_type_id TXN_BILLING_TYPE_ID,
'SERVICE_CODE' EST_LINE_SOURCE_TYPE_CODE,
ROSC.Service_Code_id EST_LINE_SOURCE_ID1,
null EST_LINE_SOURCE_ID2,
ROSC.RO_Service_Code_id RO_SERVICE_CODE_ID,
RES.resource_id RESOURCE_ID -- vkjain. 3449978
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SC_WORK_ENTITIES WRK,
CSD_SERVICE_CODES_VL SC,
BOM_OPERATION_SEQUENCES OPSEQ,
BOM_OPERATION_RESOURCES OPRES,
QP_PRICE_LIST_LINES_V PRICE,
MTL_SYSTEM_ITEMS_KFV MTL,
CSD_REPAIR_TYPES_SAR RTSAR,
CS_TXN_BILLING_TYPES SAR,
BOM_RESOURCES RES
WHERE ROSC.repair_line_id = p_repair_line_id
and ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
and ROSC.applied_to_est_flag = 'N'
and WRK.Service_code_id = ROSC.Service_code_id
and WRK.work_entity_type_code = 'BOM'
and SC.Service_code_Id = ROSC.Service_code_id
and nvl(SC.active_from, SYSDATE) <= SYSDATE
and nvl(SC.active_to, SYSDATE) >= SYSDATE
and WRK.work_entity_id3 = cs_std.get_item_valdn_orgzn_id
-- and WRK.work_entity_id2 IS NOT NULL
and OPSEQ.ROUTING_SEQUENCE_ID = WRK.work_entity_id2
and nvl(OPSEQ.effectivity_date , SYSDATE) <= SYSDATE -- swai bug 3323274
and nvl(OPSEQ.disable_date , SYSDATE) >= SYSDATE -- swai bug 3323274
and OPRES.OPERATION_SEQUENCE_ID = OPSEQ.OPERATION_SEQUENCE_ID
and RES.RESOURCE_ID = OPRES.RESOURCE_ID
and PRICE.price_list_id(+) = p_price_list_id
and nvl(PRICE.start_date_active, SYSDATE) <= SYSDATE -- swai added
and nvl(PRICE.end_date_active, SYSDATE) >= SYSDATE -- swai added
and PRICE.inventory_item_id(+) = RES.billable_item_id
and PRICE.unit_code(+) = RES.unit_of_measure
and MTL.inventory_item_id = RES.billable_item_id
and MTL.organization_id = WRK.work_entity_id3
and MTL.material_billable_flag IS NOT NULL
and RTSAR.repair_type_id = p_repair_type_id
and SAR.txn_billing_type_id = RTSAR.txn_billing_type_id(+)
and SAR.billing_type = MTL.material_billable_flag(+);
SELECT BOM.component_item_id INVENTORY_ITEM_ID,
BOM.primary_uom_code UOM,
BOM.component_quantity QUANTITY,
-- PRICE.list_price SELLING_PRICE,
MTL.concatenated_segments ITEM_NAME,
MTL.comms_nl_trackable_flag COMMS_NL_TRACKABLE_FLAG,
SAR.txn_billing_type_id TXN_BILLING_TYPE_ID,
'SERVICE_CODE' EST_LINE_SOURCE_TYPE_CODE,
ROSC.Service_Code_id EST_LINE_SOURCE_ID1,
NULL EST_LINE_SOURCE_ID2,
ROSC.RO_Service_Code_id RO_SERVICE_CODE_ID
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SERVICE_CODES_VL SC,
CSD_SC_WORK_ENTITIES WRK,
BOM_INVENTORY_COMPONENTS_V BOM,
-- QP_PRICE_LIST_LINES_V PRICE,
MTL_SYSTEM_ITEMS_KFV MTL,
CSD_REPAIR_TYPES_SAR RTSAR,
CS_TXN_BILLING_TYPES SAR
WHERE ROSC.repair_line_id = p_repair_line_id
AND ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
AND ROSC.applied_to_est_flag = 'N'
AND WRK.Service_code_id = ROSC.Service_code_id
AND WRK.work_entity_type_code = 'BOM'
AND SC.Service_code_Id = ROSC.Service_code_id
AND NVL(SC.active_from, SYSDATE) <= SYSDATE
AND NVL(SC.active_to, SYSDATE) >= SYSDATE
AND WRK.work_entity_id3 = Cs_Std.get_item_valdn_orgzn_id
-- and WRK.work_entity_id1 IS NOT NULL
AND BOM.bill_sequence_id = WRK.work_entity_id1
AND NVL(BOM.effectivity_date, SYSDATE) <= SYSDATE -- swai bug 3323274
AND NVL(BOM.disable_date, SYSDATE) >= SYSDATE -- swai bug 3323274
-- Following lines commented by vkjain, bugfix 3468680
-- and PRICE.price_list_id(+) = p_price_list_id
-- and PRICE.inventory_item_id(+) = BOM.component_item_id
-- and PRICE.unit_code(+) = BOM.primary_uom_code
-- and nvl(PRICE.start_date_active, SYSDATE) <= SYSDATE -- swai added
-- and nvl(PRICE.end_date_active, SYSDATE) >= SYSDATE -- swai added
AND MTL.inventory_item_id = BOM.component_item_id
AND MTL.organization_id = WRK.work_entity_id3
AND RTSAR.repair_type_id = p_repair_type_id
AND SAR.txn_billing_type_id = RTSAR.txn_billing_type_id(+)
AND SAR.billing_type = MTL.material_billable_flag(+)
AND MTL.material_billable_flag IS NOT NULL;
SELECT RES.billable_item_id INVENTORY_ITEM_ID,
RES.unit_of_measure UOM,
-- OPRES.assigned_units QUANTITY, -- Replaced by following line to fix 3365436. vkjain.
OPRES.usage_rate_or_amount QUANTITY,
-- PRICE.list_price SELLING_PRICE,
MTL.concatenated_segments ITEM_NAME,
MTL.comms_nl_trackable_flag COMMS_NL_TRACKABLE_FLAG,
SAR.txn_billing_type_id TXN_BILLING_TYPE_ID,
'SERVICE_CODE' EST_LINE_SOURCE_TYPE_CODE,
ROSC.Service_Code_id EST_LINE_SOURCE_ID1,
NULL EST_LINE_SOURCE_ID2,
ROSC.RO_Service_Code_id RO_SERVICE_CODE_ID,
RES.resource_id RESOURCE_ID -- vkjain. 3449978
FROM CSD_RO_SERVICE_CODES ROSC,
CSD_SC_WORK_ENTITIES WRK,
CSD_SERVICE_CODES_VL SC,
BOM_OPERATION_SEQUENCES OPSEQ,
BOM_OPERATION_RESOURCES OPRES,
-- QP_PRICE_LIST_LINES_V PRICE,
MTL_SYSTEM_ITEMS_KFV MTL,
CSD_REPAIR_TYPES_SAR RTSAR,
CS_TXN_BILLING_TYPES SAR,
BOM_RESOURCES RES
WHERE ROSC.repair_line_id = p_repair_line_id
AND ROSC.applicable_flag = 'Y'
-- and ROSC.applied_to_work_flag = 'N' -- bugfix 3473869.vkjain.
AND ROSC.applied_to_est_flag = 'N'
AND WRK.Service_code_id = ROSC.Service_code_id
AND WRK.work_entity_type_code = 'BOM'
AND SC.Service_code_Id = ROSC.Service_code_id
AND NVL(SC.active_from, SYSDATE) <= SYSDATE
AND NVL(SC.active_to, SYSDATE) >= SYSDATE
AND WRK.work_entity_id3 = Cs_Std.get_item_valdn_orgzn_id
-- and WRK.work_entity_id2 IS NOT NULL
AND OPSEQ.ROUTING_SEQUENCE_ID = WRK.work_entity_id2
AND NVL(OPSEQ.effectivity_date, SYSDATE) <= SYSDATE -- swai bug 3323274
AND NVL(OPSEQ.disable_date, SYSDATE) >= SYSDATE -- swai bug 3323274
AND OPRES.OPERATION_SEQUENCE_ID =
OPSEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_ID = OPRES.RESOURCE_ID
-- Following lines commented by vkjain, bugfix 3468680
-- and PRICE.price_list_id(+) = p_price_list_id
-- and nvl(PRICE.start_date_active, SYSDATE) <= SYSDATE -- swai added
-- and nvl(PRICE.end_date_active, SYSDATE) >= SYSDATE -- swai added
-- and PRICE.inventory_item_id(+) = RES.billable_item_id
-- and PRICE.unit_code(+) = RES.unit_of_measure
AND MTL.inventory_item_id = RES.billable_item_id
AND MTL.organization_id = WRK.work_entity_id3
AND MTL.material_billable_flag IS NOT NULL
AND RTSAR.repair_type_id = p_repair_type_id
AND SAR.txn_billing_type_id = RTSAR.txn_billing_type_id(+)
AND SAR.billing_type = MTL.material_billable_flag(+);
SELECT count(*)
FROM csi_txn_sub_types ib,
cs_txn_billing_types cs
WHERE ib.cs_transaction_type_id = cs.transaction_type_id
and cs.txn_billing_type_id = p_txn_billing_type_id;
SELECT cs.transaction_type_id
FROM cs_txn_billing_types cs
WHERE cs.txn_billing_type_id = p_txn_billing_type_id;
SELECT name
INTO l_price_list_name
FROM qp_list_headers_tl
WHERE list_header_id = p_price_list_id;
x_est_lines_tbl(l_curRow).last_update_date := NULL;
x_est_lines_tbl(l_curRow).last_updated_by := NULL;
x_est_lines_tbl(l_curRow).last_update_login := NULL;
/* Updates repair order estimate approved flag and creates Depot estimate */
/* header record with information like, summary, lead time etc */
/*----------------------------------------------------------------------------*/
PROCEDURE CREATE_ESTIMATE_HEADER(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit 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_estimate_hdr_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_HDR_REC,
x_estimate_hdr_id OUT NOCOPY NUMBER ) IS
l_incident_id NUMBER;
Csd_Repairs_Grp.UPDATE_APPROVAL_STATUS(p_repair_line_id => l_estimate_hdr_rec.repair_line_id,
p_new_approval_status => l_approval_Status,
p_old_approval_status => NULL,
p_quantity => l_estimate_hdr_rec.repair_line_quantity,
p_org_contact_id => NULL,
p_reason => '',
p_object_version_number => l_estimate_hdr_rec.ro_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Updated ro status');
debug('Updated ro status failed, x_msg_data[' ||
l_msg_data || ']');
/* procedure name: UPDATE_ESTIMATE_HEADER */
/* description : procedure used to update estimate header */
/* Updates repair order estimate approved flag and creates Depot estimate */
/* header record with information like, summary, lead time etc */
/* Change History : Created 24-June-2005 by Vijay */
/*----------------------------------------------------------------------------*/
PROCEDURE UPDATE_ESTIMATE_HEADER(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit 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_estimate_hdr_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_HDR_REC,
x_object_version_number OUT NOCOPY NUMBER) IS
l_return_status VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ESTIMATE_HEADER';
SELECT REPAIR_ESTIMATE_ID,
REPAIR_LINE_ID,
ESTIMATE_STATUS,
ESTIMATE_DATE,
WORK_SUMMARY,
PO_NUMBER,
LEAD_TIME,
LEAD_TIME_UOM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
ESTIMATE_REASON_CODE,
NOT_TO_EXCEED -- swai: bug 9462789 allow update of NTE value
FROM CSD_REPAIR_ESTIMATE
WHERE REPAIR_ESTIMATE_ID = p_estimate_header_id;
SAVEPOINT UPDATE_ESTIMATE_HEADER_PVT;
debug('Start of update estimate header for estimate id[=' ||
p_estimate_hdr_rec.repair_estimate_id || ']');
'csd.plsql.csd_repair_estimate_pvt.update_estimate_header.begin',
'Entering update_estimate_header');
l_est_pvt_hdr_Rec.last_updated_by,
l_est_pvt_hdr_Rec.last_update_date,
l_est_pvt_hdr_Rec.last_update_login,
l_est_pvt_hdr_Rec.context,
l_est_pvt_hdr_Rec.attribute1,
l_est_pvt_hdr_Rec.attribute2,
l_est_pvt_hdr_Rec.attribute3,
l_est_pvt_hdr_Rec.attribute4,
l_est_pvt_hdr_Rec.attribute5,
l_est_pvt_hdr_Rec.attribute6,
l_est_pvt_hdr_Rec.attribute7,
l_est_pvt_hdr_Rec.attribute8,
l_est_pvt_hdr_Rec.attribute9,
l_est_pvt_hdr_Rec.attribute10,
l_est_pvt_hdr_Rec.attribute11,
l_est_pvt_hdr_Rec.attribute12,
l_est_pvt_hdr_Rec.attribute13,
l_est_pvt_hdr_Rec.attribute14,
l_est_pvt_hdr_Rec.attribute15,
l_est_pvt_hdr_Rec.object_version_number,
l_est_pvt_hdr_Rec.estimate_reason_code,
l_est_pvt_hdr_Rec.not_to_exceed; -- swai: bug 9462789
Csd_Repairs_Grp.UPDATE_APPROVAL_STATUS(p_repair_line_id => l_estimate_hdr_rec.repair_line_id,
p_new_approval_status => l_approval_Status,
p_old_approval_status => NULL,
p_quantity => l_estimate_hdr_rec.repair_line_quantity,
p_org_contact_id => NULL,
p_reason => '',
p_object_version_number => l_estimate_hdr_rec.ro_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Updated ro status');
debug('Updated ro status failed, x_msg_data[' ||
l_msg_data || ']');
Csd_Repair_Estimate_Pvt.update_repair_estimate(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_estimate_rec => l_est_pvt_hdr_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('update estiamte header failed, x_msg_data[' ||
l_msg_data || ']');
'csd.plsql.csd_repair_estimate_pvt.update_estimate_header.end',
'leaving update_estimate_header');
ROLLBACK TO UPDATE_ESTIMATE_HEADER_PVT;
'csd.plsql.csd_repair_estimate_pvt.update_estimate_header',
'EXC_ERROR[' || x_msg_data || ']');
ROLLBACK TO UPDATE_ESTIMATE_HEADER_PVT;
'csd.plsql.csd_repair_estimate_pvt.update_estimate_header',
'EXC_UNEXP_ERROR[' || x_msg_data || ']');
Rollback TO UPDATE_ESTIMATE_HEADER_PVT;
'csd.plsql.csd_repair_estimate_pvt.update_estimate_header',
'SQL MEssage[' || SQLERRM || ']');
END UPDATE_ESTIMATE_HEADER;
/* procedure name: UPDATE_ESTIMATE_LINE */
/* description : procedure used to update estimate line */
/* Updates Depot estimate line record and submits */
/* based on some validations. */
/* Change History : Created 24-June-2005 by Vijay */
/*----------------------------------------------------------------------------*/
PROCEDURE UPDATE_ESTIMATE_LINE(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit 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_estimate_line_rec IN Csd_Repair_Estimate_Pub.ESTIMATE_LINE_REC,
x_object_version_number OUT NOCOPY NUMBER) IS
BEGIN
NULL;
END UPDATE_ESTIMATE_LINE;
SELECT est.repair_estimate_id,
est.estimate_status,
lkp.meaning
FROM CSD_REPAIR_ESTIMATE est, fnd_lookups lkp
WHERE est.repair_line_id = p_repair_line_id
and lkp.lookup_code = est.estimate_status
and lkp.lookup_type = 'CSD_ESTIMATE_STATUS'
and rownum = 1;
SELECT lin.repair_estimate_line_id
FROM CSD_REPAIR_ESTIMATE_LINES lin
WHERE lin.repair_estimate_id = p_estimate_header_id;
SELECT XREF.wip_entity_id, XREF.organization_id WIP_Organization_Id,
XREF.inventory_item_id, XREF.JOB_NAME
FROM CSD_ESTIMATES_FROM_WIP_V XREF
WHERE XREF.repair_line_id = p_repair_line_id;
SELECT XREF.wip_entity_id,
WENT.WIP_ENTITY_NAME JOB_NAME
FROM CSD_REPAIR_JOB_XREF XREF,
WIP_ENTITIES WENT
WHERE XREF.repair_line_id = p_repair_line_id
AND WENT.wip_entity_id = XREF.wip_entity_id
AND EXISTS
(SELECT 'x'
FROM CSD_REPAIR_JOB_XREF RJOB
WHERE RJOB.wip_entity_id = XREF.wip_entity_id
HAVING COUNT(*) > 1
);
SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
MSI.primary_uom_code UOM,
SUM( DECODE( MMT.transaction_type_id
, G_MTL_TXN_TYPE_COMP_ISSUE, ABS( mmt.primary_quantity )
, G_MTL_TXN_TYPE_COMP_RETURN,
( -1 * ABS( mmt.primary_quantity )))) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ESTIMATE_SOURCE_CODE_WIP ESTIMATE_SOURCE_CODE,
l_wip_entity_id ESTIMATE_SOURCE_ID,
mmt.operation_seq_num OPERATION_SEQ_NUM
FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_SYSTEM_ITEMS_KFV MSI,
CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
WHERE MMT.transaction_source_id = l_wip_entity_id
AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
AND MMT.transaction_type_id IN( G_MTL_TXN_TYPE_COMP_ISSUE,
G_MTL_TXN_TYPE_COMP_RETURN )
AND MMT.inventory_item_id <> l_inventory_item_id
AND MSI.inventory_item_id = MMT.inventory_item_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY MMT.inventory_item_id, MSI.primary_uom_code,
MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
l_wip_entity_id, mmt.operation_seq_num
ORDER BY MMT.inventory_item_id;
SELECT mmt.inventory_item_id INVENTORY_ITEM_ID,
MSI.primary_uom_code UOM,
SUM(decode(sign(mmt.primary_quantity), -1, abs(mmt.primary_quantity), 0 )) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ESTIMATE_SOURCE_CODE_WIP ESTIMATE_SOURCE_CODE,
l_wip_entity_id ESTIMATE_SOURCE_ID,
mmt.operation_seq_num OPERATION_SEQ_NUM
FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_SYSTEM_ITEMS_KFV MSI,
CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
WHERE MMT.transaction_source_id = l_wip_entity_id
AND MMT.transaction_source_type_id = G_MTL_TXN_SOURCE_TYPE_WIP
AND MMT.inventory_item_id <> l_inventory_item_id
AND MSI.inventory_item_id = MMT.inventory_item_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
AND MMT.transaction_type_id = G_MTL_TXN_TYPE_COMP_ISSUE
GROUP BY MMT.inventory_item_id, MSI.primary_uom_code,
MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
l_wip_entity_id, mmt.operation_seq_num
ORDER BY MMT.inventory_item_id;
SELECT wro.inventory_item_id INVENTORY_ITEM_ID,
MSI.primary_uom_code UOM,
(wro.REQUIRED_QUANTITY - wro.QUANTITY_ISSUED) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ESTIMATE_SOURCE_CODE_WIP ESTIMATE_SOURCE_CODE,
l_wip_entity_id ESTIMATE_SOURCE_ID,
wro.operation_seq_num OPERATION_SEQ_NUM
FROM WIP_REQUIREMENT_OPERATIONS wro, MTL_SYSTEM_ITEMS_KFV MSI,
CSD_REPAIR_TYPES_SAR RTYP, CS_TXN_BILLING_TYPES TXBT
WHERE wro.wip_entity_id = l_wip_entity_id
AND wro.inventory_item_id <> l_inventory_item_id
AND MSI.inventory_item_id = wro.inventory_item_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
ORDER BY wro.inventory_item_id;
SELECT RES.billable_item_id INVENTORY_ITEM_ID,
WTXN.primary_uom UOM,
SUM( NVL( WTXN.primary_quantity, 0 )) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ESTIMATE_SOURCE_CODE_WIP ESTIMATE_SOURCE_CODE,
l_wip_entity_id ESTIMATE_SOURCE_ID,
wtxn.operation_seq_num OPERATION_SEQ_NUM,
RES.resource_id RESOURCE_ID -- Added for ER 3607765, vkjain.
FROM WIP_TRANSACTIONS WTXN, BOM_RESOURCES RES,
MTL_SYSTEM_ITEMS_KFV MSI, CSD_REPAIR_TYPES_SAR RTYP,
CS_TXN_BILLING_TYPES TXBT
WHERE WTXN.wip_entity_id = l_wip_entity_id
AND WTXN.transaction_type IN( 1, 2, 3 )
AND RES.resource_id = WTXN.resource_id
AND MSI.inventory_item_id = RES.billable_item_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
GROUP BY RES.billable_item_id, WTXN.primary_uom,
MSI.concatenated_segments, MSI.comms_nl_trackable_flag,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
l_wip_entity_id, wtxn.operation_seq_num , RES.resource_id
ORDER BY RES.billable_item_id;
SELECT RES.billable_item_id INVENTORY_ITEM_ID,
WOR.UOM_CODE UOM,
nvl( WTD.TRANSACTION_QUANTITY,
decode ( sign (
( WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1) )
- WOR.APPLIED_RESOURCE_UNITS -
- csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id,
wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
) , 1 ,
round (
(( WOR.USAGE_RATE_OR_AMOUNT * DECODE(WOR.BASIS_TYPE,1, WO.SCHEDULED_QUANTITY,1) )
- WOR.APPLIED_RESOURCE_UNITS
- csd_hv_wip_job_pvt.get_pending_quantity ( wor.wip_entity_id,
wor.operation_seq_num, wor.resource_seq_num, WOR.UOM_CODE )
), 6 ),
0 )
) QUANTITY,
MSI.concatenated_segments ITEM_NAME,
MSI.comms_nl_trackable_flag IB_TRACKABLE_FLAG,
TXBT.txn_billing_type_id, TXBT.transaction_type_id,
G_ESTIMATE_SOURCE_CODE_WIP ESTIMATE_SOURCE_CODE,
l_wip_entity_id ESTIMATE_SOURCE_ID,
WOR.operation_seq_num OPERATION_SEQ_NUM,
RES.resource_id RESOURCE_ID
FROM WIP_OPERATION_RESOURCES WOR, wip_operations wo,
CSD_WIP_TRANSACTION_DETAILS WTD, BOM_RESOURCES RES,
MTL_SYSTEM_ITEMS_KFV MSI, CSD_REPAIR_TYPES_SAR RTYP,
CS_TXN_BILLING_TYPES TXBT
WHERE WOR.wip_entity_id = l_wip_entity_id
AND RES.resource_id = WOR.resource_id
AND MSI.inventory_item_id = RES.billable_item_id
AND MSI.organization_id = p_organization_id
AND MSI.material_billable_flag IS NOT NULL
AND TXBT.billing_type = MSI.material_billable_flag
AND RTYP.repair_type_id = p_repair_type_id
AND TXBT.txn_billing_type_id = RTYP.txn_billing_type_id
AND TRUNC(NVL(TXBT.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(TXBT.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID,-1)=NVL(WO.REPETITIVE_SCHEDULE_ID,-1)
AND WOR. WIP_ENTITY_ID = WTD. WIP_ENTITY_ID(+)
AND WOR. OPERATION_SEQ_NUM = WTD. OPERATION_SEQ_NUM(+)
AND WOR.RESOURCE_SEQ_NUM = WTD. RESOURCE_SEQ_NUM(+)
ORDER BY RES.billable_item_id;
select object_version_number
into l_repair_estimate_rec.object_version_number
from csd_repair_estimate
where repair_estimate_id = l_repair_estimate_id;
FND_LOG.STRING(lc_stat_level, lc_mod_name,'Calling UPDATE_REPAIR_ESTIMATE');
UPDATE_REPAIR_ESTIMATE(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_estimate_rec => l_repair_estimate_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Unable to update repair estimate header for repair_line_id ' || p_repair_line_id);
'UPDATE_REPAIR_ESTIMATE returned l_repair_estimate_id='||l_repair_estimate_id);
FND_LOG.STRING(lc_stat_level, lc_mod_name,'Calling DELETE_REPAIR_ESTIMATE_LINES');
DELETE_REPAIR_ESTIMATE_LINES(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,
p_estimate_line_id => curr_est_line_rec.repair_estimate_line_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Unable to delete repair estimate line ' || curr_est_line_rec.repair_estimate_line_id);
'Returned from DELETE_REPAIR_ESTIMATE_LINES');
select default_po_num
into l_default_po_number
from csd_repairs
where repair_line_id = p_repair_line_id;