[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE NUMBER := 1;
select nvl(incident_occurred_date,incident_date)
from jtf_task_assignments jta , cs_incidents_all cia, jtf_tasks_b jtb,
csf_debrief_headers cdh
where jta.task_assignment_id = cdh.task_assignment_id
and jtb.task_id = jta.task_id
and cia.incident_id = jtb.source_object_id
and jtb.source_object_type_code = 'SR'
and cdh.debrief_header_id = p_debrief_header_id;
select jtv.source_object_type_code
from jtf_tasks_vl jtv,
jtf_task_assignments_v jta
where jta.task_assignment_id = p_task_assignment_id
and jta.task_id = jtv.task_id;
select jta.object_version_number, jta.resource_type_code, jta.resource_id
from jtf_task_assignments jta
where jta.task_assignment_id = p_debrief_rec.TASK_ASSIGNMENT_ID;
select 1 into x
from CSF_DEBRIEF_HEADERS
where DEBRIEF_HEADER_ID = l_DEBRIEF_HEADER_ID ;
SELECT CSF_DEBRIEF_HEADERS_S2.nextval
INTO l_debrief_number
FROM dual;
SELECT CSF_DEBRIEF_HEADERS_S1.nextval
INTO l_debrief_header_id
FROM dual;
SELECT CSF_DEBRIEF_HEADERS_S2.nextval
INTO l_debrief_number
FROM dual;
csf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_Data,
p_task_assignment_id => p_debrief_rec.task_assignment_id,
p_object_version_number => l_oject_version_number,
p_actual_travel_distance => p_debrief_rec.TRAVEL_DISTANCE_IN_KM ,
p_actual_travel_duration => l_actual_travel_duration,
p_actual_travel_duration_uom => l_unit_of_measure_for_hr,
p_resource_type_code => l_resource_type_code,
p_resource_id => l_resource_id,
x_task_object_version_number => l_task_object_version,
x_task_status_id => l_task_status_id);
csf_debrief_headers_pkg.Insert_Row(
PX_DEBRIEF_HEADER_ID => l_debrief_header_id,
P_DEBRIEF_NUMBER => l_debrief_number,
P_DEBRIEF_DATE => p_debrief_rec.debrief_date,
P_DEBRIEF_STATUS_ID => p_debrief_rec.DEBRIEF_STATUS_ID,
P_TASK_ASSIGNMENT_ID => p_debrief_rec.TASK_ASSIGNMENT_ID,
P_CREATED_BY => nvl(p_debrief_rec.created_by,G_USER_ID),
P_CREATION_DATE => nvl(p_debrief_rec.creation_date,SYSDATE),
P_LAST_UPDATED_BY => nvl(p_debrief_rec.last_updated_by,G_USER_ID),
P_LAST_UPDATE_DATE => nvl(p_debrief_rec.last_update_date,SYSDATE),
P_LAST_UPDATE_LOGIN => nvl(p_debrief_rec.last_update_login,G_LOGIN_ID),
P_ATTRIBUTE1 => p_debrief_rec.ATTRIBUTE1 ,
P_ATTRIBUTE2 => p_debrief_rec.ATTRIBUTE2 ,
P_ATTRIBUTE3 => p_debrief_rec.ATTRIBUTE3 ,
P_ATTRIBUTE4 => p_debrief_rec.ATTRIBUTE4 ,
P_ATTRIBUTE5 => p_debrief_rec.ATTRIBUTE5 ,
P_ATTRIBUTE6 => p_debrief_rec.ATTRIBUTE6 ,
P_ATTRIBUTE7 => p_debrief_rec.ATTRIBUTE7 ,
P_ATTRIBUTE8 => p_debrief_rec.ATTRIBUTE8 ,
P_ATTRIBUTE9 => p_debrief_rec.ATTRIBUTE9 ,
P_ATTRIBUTE10 => p_debrief_rec.ATTRIBUTE10 ,
P_ATTRIBUTE11 => p_debrief_rec.ATTRIBUTE11 ,
P_ATTRIBUTE12 => p_debrief_rec.ATTRIBUTE12 ,
P_ATTRIBUTE13 => p_debrief_rec.ATTRIBUTE13 ,
P_ATTRIBUTE14 => p_debrief_rec.ATTRIBUTE14,
P_ATTRIBUTE15 => p_debrief_rec.ATTRIBUTE15,
P_ATTRIBUTE_CATEGORY => p_debrief_rec.ATTRIBUTE_CATEGORY,
p_object_version_number => p_debrief_rec.object_version_number,
p_TRAVEL_START_TIME =>p_debrief_rec.TRAVEL_START_TIME,
p_TRAVEL_END_TIME =>p_debrief_rec.TRAVEL_END_TIME,
p_TRAVEL_DISTANCE_IN_KM =>p_debrief_rec.TRAVEL_DISTANCE_IN_KM
);
PROCEDURE Update_debrief(
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_DEBRIEF_Rec IN DEBRIEF_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Update_debrief';
select jta.object_version_number,
jta.resource_type_code,
jta.resource_id
from jtf_task_assignments jta
where jta.task_assignment_id = p_debrief_rec.TASK_ASSIGNMENT_ID;
SAVEPOINT UPDATE_DEBRIEF_PVT;
csf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_Data,
p_task_assignment_id => p_debrief_rec.task_assignment_id,
p_object_version_number => l_oject_version_number,
p_actual_travel_distance => p_debrief_rec.TRAVEL_DISTANCE_IN_KM ,
p_actual_travel_duration => l_actual_travel_duration,
p_actual_travel_duration_uom => l_unit_of_measure_for_hr,
p_resource_type_code => l_resource_type_code,
p_resource_id => l_resource_id,
x_task_object_version_number => l_task_object_version,
x_task_status_id => l_task_status_id);
CSF_DEBRIEF_HEADERS_PKG.Update_Row(
P_DEBRIEF_HEADER_ID => p_debrief_rec.DEBRIEF_HEADER_ID,
P_DEBRIEF_NUMBER => p_debrief_rec.DEBRIEF_NUMBER ,
P_DEBRIEF_DATE => p_debrief_rec.DEBRIEF_DATE,
P_DEBRIEF_STATUS_ID => p_debrief_rec.DEBRIEF_STATUS_ID ,
P_TASK_ASSIGNMENT_ID => p_debrief_rec.TASK_ASSIGNMENT_ID,
P_CREATED_BY => p_debrief_rec.CREATED_BY,
P_CREATION_DATE => p_debrief_rec.CREATION_DATE,
P_LAST_UPDATED_BY => nvl(p_debrief_rec.last_updated_by,g_user_id),
P_LAST_UPDATE_DATE => nvl(p_debrief_Rec.last_update_date,sysdate),
P_LAST_UPDATE_LOGIN => nvl(p_debrief_rec.last_update_login,g_login_id),
P_ATTRIBUTE1 => p_debrief_rec.ATTRIBUTE1 ,
P_ATTRIBUTE2 => p_debrief_rec.ATTRIBUTE2 ,
P_ATTRIBUTE3 => p_debrief_rec.ATTRIBUTE3 ,
P_ATTRIBUTE4 => p_debrief_rec.ATTRIBUTE4 ,
P_ATTRIBUTE5 => p_debrief_rec.ATTRIBUTE5 ,
P_ATTRIBUTE6 => p_debrief_rec.ATTRIBUTE6 ,
P_ATTRIBUTE7 => p_debrief_rec.ATTRIBUTE7 ,
P_ATTRIBUTE8 => p_debrief_rec.ATTRIBUTE8 ,
P_ATTRIBUTE9 => p_debrief_rec.ATTRIBUTE9 ,
P_ATTRIBUTE10 => p_debrief_rec.ATTRIBUTE10 ,
P_ATTRIBUTE11 => p_debrief_rec.ATTRIBUTE11 ,
P_ATTRIBUTE12 => p_debrief_rec.ATTRIBUTE12 ,
P_ATTRIBUTE13 => p_debrief_rec.ATTRIBUTE13 ,
P_ATTRIBUTE14 => p_debrief_rec.ATTRIBUTE14,
P_ATTRIBUTE15 => p_debrief_rec.ATTRIBUTE15,
P_ATTRIBUTE_CATEGORY => p_debrief_rec.ATTRIBUTE_CATEGORY,
p_object_version_number => p_debrief_rec.object_version_number,
p_TRAVEL_START_TIME =>p_debrief_rec.TRAVEL_START_TIME,
p_TRAVEL_END_TIME =>p_debrief_rec.TRAVEL_END_TIME,
p_TRAVEL_DISTANCE_IN_KM =>p_debrief_rec.TRAVEL_DISTANCE_IN_KM);
ROLLBACK TO UPDATE_DEBRIEF_PVT;
ROLLBACK TO UPDATE_DEBRIEF_PVT;
ROLLBACK TO UPDATE_DEBRIEF_PVT;
End Update_debrief;
select 1
from JTF_TASK_ASSIGNMENTS
where TASK_ASSIGNMENT_ID=P_TASK_ASSIGNMENT_ID;
select billing_category
from cs_billing_type_categories cbtc,
mtl_system_items_b msib
where inventory_item_id = p_debrief_line_rec.inventory_item_id
and organization_id = l_valid_org -- changed for bug 4259770
-- nvl(p_debrief_line_rec.issuing_inventory_org_id, p_debrief_line_rec.receiving_inventory_org_id)
and material_billable_flag = billing_type;
elsif P_DEBRIEF_REC.LAST_UPDATED_BY <>FND_API.G_MISS_NUM then
Return FALSE;
elsif P_DEBRIEF_REC.LAST_UPDATE_DATE <>FND_API.G_MISS_DATE then
Return FALSE;
elsif P_DEBRIEF_REC.LAST_UPDATE_LOGIN <>FND_API.G_MISS_NUM then
Return FALSE;
select greatest(nvl(rejected_flag,'N'),
nvl(completed_flag,'N'),
nvl(closed_flag,'N'),
nvl(cancelled_flag,'N'))
from jtf_task_statuses_b jtsb,
jtf_task_assignments jta,
csf_debrief_headers cdh
where cdh.debrief_header_id = p_debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
AND jtsb.task_status_id = jta.assignment_status_id;
select ctst.src_status_id
from csi_txn_sub_types ctst ,
cs_transaction_types_vl cttv
where ctst.cs_transaction_type_id = cttv.transaction_type_id
AND cttv.transaction_type_id = p_transaction_type_id;
select instance_status_id
from csi_instance_statuses
where name = p_cp_status;
select primary_uom_code
from mtl_system_items_b
where inventory_item_id = p_inventory_item_id;
select ctbt.billing_type,
cttv.line_order_category_code,
cttv.transaction_type_id
from cs_transaction_types_vl cttv,
cs_txn_billing_types ctbt
where ctbt.txn_billing_type_id = p_txn_billing_type_id
and ctbt.transaction_type_id = cttv.transaction_type_id;
select ctbt.billing_type,
cttv.line_order_category_code
from cs_transaction_types_vl cttv,
cs_txn_billing_types ctbt,
mtl_system_items_b_kfv msibk
where cttv.transaction_type_id = p_transaction_type_id
and cttv.transaction_type_id = ctbt.transaction_type_id
and ctbt.billing_type = msibk.material_billable_flag
and msibk.inventory_item_id = p_inventory_item_id;
p_debrief_line_rec.IB_UPDATE_STATUS := p_debrief_line_tbl(l_curr_row).IB_UPDATE_STATUS ;
p_debrief_line_rec.IB_UPDATE_MSG_CODE := p_debrief_line_tbl(l_curr_row).IB_UPDATE_MSG_CODE ;
p_debrief_line_rec.IB_UPDATE_MESSAGE := p_debrief_line_tbl(l_curr_row).IB_UPDATE_MESSAGE ;
p_debrief_line_rec.SPARE_UPDATE_STATUS := p_debrief_line_tbl(l_curr_row).SPARE_UPDATE_STATUS ;
p_debrief_line_rec.SPARE_UPDATE_MSG_CODE := p_debrief_line_tbl(l_curr_row).SPARE_UPDATE_MSG_CODE ;
p_debrief_line_rec.SPARE_UPDATE_MESSAGE := p_debrief_line_tbl(l_curr_row).SPARE_UPDATE_MESSAGE ;
p_debrief_line_rec.LAST_UPDATE_DATE := nvl(p_debrief_line_tbl(l_curr_row).last_update_date,SYSDATE);
p_debrief_line_rec.LAST_UPDATED_BY := nvl(p_debrief_line_tbl(l_curr_row).last_updated_by,G_USER_ID);
p_debrief_line_rec.LAST_UPDATE_LOGIN := nvl(p_debrief_line_tbl(l_curr_row).last_update_login,G_LOGIN_ID);
select 1 into x
from CSF_DEBRIEF_LINES
where DEBRIEF_LINE_ID = l_DEBRIEF_LINE_ID ;
SELECT CSF_DEBRIEF_LINES_S.nextval
INTO l_debrief_line_id
FROM dual;
csf_debrief_lines_pkg.INSERT_ROW(
px_DEBRIEF_LINE_ID => l_debrief_line_id,
p_DEBRIEF_HEADER_ID => p_DEBRIEF_LINE_rec.DEBRIEF_HEADER_ID,
p_DEBRIEF_LINE_NUMBER => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_NUMBER,
p_SERVICE_DATE => p_DEBRIEF_LINE_rec.SERVICE_DATE ,
p_BUSINESS_PROCESS_ID => p_DEBRIEF_LINE_rec.BUSINESS_PROCESS_ID ,
p_TXN_BILLING_TYPE_ID => p_DEBRIEF_LINE_rec.TXN_BILLING_TYPE_ID ,
p_INVENTORY_ITEM_ID => p_DEBRIEF_LINE_rec.INVENTORY_ITEM_ID ,
p_INSTANCE_ID => p_DEBRIEF_LINE_rec.INSTANCE_ID ,
p_ISSUING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.ISSUING_INVENTORY_ORG_ID,
p_RECEIVING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.RECEIVING_INVENTORY_ORG_ID,
p_ISSUING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.ISSUING_SUB_INVENTORY_CODE,
p_RECEIVING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.RECEIVING_SUB_INVENTORY_CODE,
p_ISSUING_LOCATOR_ID => p_DEBRIEF_LINE_rec.ISSUING_LOCATOR_ID ,
p_RECEIVING_LOCATOR_ID => p_DEBRIEF_LINE_rec.RECEIVING_LOCATOR_ID ,
p_PARENT_PRODUCT_ID => p_DEBRIEF_LINE_rec.PARENT_PRODUCT_ID ,
p_REMOVED_PRODUCT_ID => p_DEBRIEF_LINE_rec.REMOVED_PRODUCT_ID ,
p_STATUS_OF_RECEIVED_PART => l_cp_status_id ,
p_ITEM_SERIAL_NUMBER => p_DEBRIEF_LINE_rec.ITEM_SERIAL_NUMBER ,
p_ITEM_REVISION => p_DEBRIEF_LINE_rec.ITEM_REVISION ,
p_ITEM_LOTNUMBER => p_DEBRIEF_LINE_rec.ITEM_LOTNUMBER ,
p_UOM_CODE => p_DEBRIEF_LINE_rec.UOM_CODE ,
p_QUANTITY => p_DEBRIEF_LINE_rec.QUANTITY ,
p_RMA_HEADER_ID => p_DEBRIEF_LINE_rec.RMA_HEADER_ID ,
p_DISPOSITION_CODE => p_DEBRIEF_LINE_rec.DISPOSITION_CODE ,
p_MATERIAL_REASON_CODE => p_DEBRIEF_LINE_rec.MATERIAL_REASON_CODE ,
p_LABOR_REASON_CODE => p_DEBRIEF_LINE_rec.LABOR_REASON_CODE ,
p_EXPENSE_REASON_CODE => p_DEBRIEF_LINE_rec.EXPENSE_REASON_CODE ,
p_LABOR_START_DATE => trunc(xx_labor_start_date,'MI'),
p_LABOR_END_DATE => trunc(xx_labor_end_date,'MI'),
p_STARTING_MILEAGE => p_DEBRIEF_LINE_rec.STARTING_MILEAGE ,
p_ENDING_MILEAGE => p_DEBRIEF_LINE_rec.ENDING_MILEAGE ,
p_EXPENSE_AMOUNT => p_DEBRIEF_LINE_rec.EXPENSE_AMOUNT ,
p_CURRENCY_CODE => p_DEBRIEF_LINE_rec.CURRENCY_CODE ,
p_DEBRIEF_LINE_STATUS_ID => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_STATUS_ID ,
p_RETURN_REASON_CODE => P_debrief_line_rec.RETURN_REASON_CODE ,
p_CHANNEL_CODE => p_DEBRIEF_LINE_rec.CHANNEL_CODE,
p_CHARGE_UPLOAD_STATUS => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_STATUS ,
p_CHARGE_UPLOAD_MSG_CODE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MSG_CODE ,
p_CHARGE_UPLOAD_MESSAGE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MESSAGE ,
p_IB_UPDATE_STATUS => p_DEBRIEF_LINE_rec.IB_UPDATE_STATUS ,
p_IB_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.IB_UPDATE_MSG_CODE ,
p_IB_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.IB_UPDATE_MESSAGE ,
p_SPARE_UPDATE_STATUS => p_DEBRIEF_LINE_rec.SPARE_UPDATE_STATUS ,
p_SPARE_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MSG_CODE ,
p_SPARE_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MESSAGE ,
p_CREATED_BY => nvl(p_debrief_line_rec.created_by,G_USER_ID),
p_CREATION_DATE => nvl(p_debrief_line_rec.creation_date,SYSDATE),
p_LAST_UPDATED_BY => nvl(p_debrief_line_rec.last_updated_by,G_USER_ID),
p_LAST_UPDATE_DATE => nvl(p_debrief_line_rec.last_update_date,SYSDATE),
p_LAST_UPDATE_LOGIN => nvl(p_debrief_line_rec.last_update_login,G_LOGIN_ID),
p_ATTRIBUTE1 => p_DEBRIEF_LINE_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_DEBRIEF_LINE_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_DEBRIEF_LINE_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_DEBRIEF_LINE_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_DEBRIEF_LINE_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_DEBRIEF_LINE_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_DEBRIEF_LINE_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_DEBRIEF_LINE_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_DEBRIEF_LINE_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_DEBRIEF_LINE_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_DEBRIEF_LINE_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_DEBRIEF_LINE_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_DEBRIEF_LINE_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_DEBRIEF_LINE_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_DEBRIEF_LINE_rec.ATTRIBUTE15,
p_ATTRIBUTE_CATEGORY => p_DEBRIEF_LINE_rec.ATTRIBUTE_CATEGORY,
p_TRANSACTION_TYPE_ID => P_DEBRIEF_LINE_REC.TRANSACTION_TYPE_ID,
p_RETURN_DATE => P_DEBRIEF_LINE_REC.RETURN_DATE,
P_USAGE_TYPE => P_DEBRIEF_LINE_REC.USAGE_TYPE,
P_DEST_ORGANIZATION_ID => P_DEBRIEF_LINE_REC.DEST_ORGANIZATION_ID,
P_DEST_SUBINVENTORY_NAME => P_DEBRIEF_LINE_REC.DEST_SUBINVENTORY_NAME,
P_CARRIER_CODE => P_DEBRIEF_LINE_REC.CARRIER_CODE,
P_SHIPPING_METHOD => P_DEBRIEF_LINE_REC.SHIPPING_METHOD,
P_SHIPMENT_NUMBER => P_DEBRIEF_LINE_REC.SHIPMENT_NUMBER,
P_WAYBILL => P_DEBRIEF_LINE_REC.WAYBILL,
p_expenditure_org_id => p_debrief_line_rec.expenditure_org_id,
p_project_id => p_debrief_line_rec.project_id,
p_project_task_id => p_debrief_line_rec.project_task_id
);
-- Calling the Update Resource Location API to Update Resource's Locatio
--dbms_output.put_line('l_Debrief_type : '||l_Debrief_type);
SELECT jta.resource_type_code,
jta.resource_id,
jta.task_id
INTO l_resource_type,
l_resource_id,
l_task_id
FROM jtf_task_Assignments jta, csf_Debrief_headers cdh
WHERE jta.task_Assignment_id = cdh.task_assignment_id
AND cdh.debrief_header_id = p_debrief_line_rec.DEBRIEF_HEADER_ID;
update_task_actuals(p_debrief_line_rec.debrief_header_id, -- modified for bug 3748610
x_return_status,
x_msg_count,
x_msg_data);
select jta.resource_id,
loc.geometry
into z_resource_id,
z_location
from csf_debrief_lines cdl,
csf_debrief_headers csf,
jtf_task_assignments jta,
jtf_tasks_b jtb,
hz_party_sites p,
hz_locations loc
where csf.debrief_header_id = p_debrief_line_rec.debrief_header_id
and debrief_line_id = l_debrief_line_id
and csf.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = csf.task_assignment_id
and jta.task_id = jtb.task_id
and jtb.address_id = p.party_site_id
and p.location_id = loc.location_id;
select max(actual_start_date)
into z_start_date
from jtf_task_assignments
where resource_id = z_resource_id;
select object_version_number
into z_object_version_number
from jtf_rs_resource_extns
where resource_id = z_resource_id;
/* jtf_rs_resource_pub.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => z_resource_id,
P_RESOURCE_NUMBER => null,
P_LOCATION => z_location,
P_object_version_num => z_object_version_number,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
-- Calling the Update Task Assignment API Location API to Update Resource's Location
if P_Upd_tskassgnstatus = 'Y' then
BEGIN
Validate_Task_Assignment_Satus(
P_Api_Version_Number =>1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Task_Assignment_status =>P_Task_Assignment_status,
X_TA_STATUS_ID =>l_assignment_status_id,
X_Return_Status =>X_Return_Status,
X_Msg_Count =>X_MSG_COUNT,
X_Msg_Data =>X_MSG_DATA);
select jta.task_assignment_id,
jta.object_version_number,
jta.task_id
into l_task_assignment_id,
l_object_version_number,
l_task_id
from jtf_task_assignments jta,
csf_debrief_headers csf
where csf.task_assignment_id = jta.task_assignment_id
and csf.debrief_header_id = p_debrief_line_rec.debrief_header_id;
CSF_TASK_ASSIGNMENTS_PUB.UPDATE_ASSIGNMENT_STATUS(
P_API_VERSION =>1.0 ,
P_INIT_MSG_LIST =>FND_API.G_FALSE,
P_COMMIT =>FND_API.G_FALSE,
X_RETURN_STATUS =>x_return_status,
X_MSG_COUNT =>x_msg_count,
X_MSG_DATA =>x_msg_data,
P_TASK_ASSIGNMENT_ID =>l_task_assignment_id,
P_ASSIGNMENT_STATUS_ID =>l_assignment_status_id,
P_OBJECT_VERSION_NUMBER =>l_object_version_number,
P_UPDATE_TASK =>'T',
X_TASK_OBJECT_VERSION_NUMBER =>l_task_object_version_number,
X_TASK_STATUS_ID =>l_task_status_id);
FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_UPDATE_TASK_ASSIGNMENT');
'CSFUPDATE',
'CSF:Update Debrief Lines',
null,
FALSE,
1.0,
p_debrief_line_rec.debrief_header_id);
select task_status_id
from jtf_task_statuses_vl
where name=P_Task_Assignment_status;
PROCEDURE Update_debrief_line(
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_Upd_tskassgnstatus IN VARCHAR2 ,
P_Task_Assignment_status IN VARCHAR2 ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_DEBRIEF_LINE_Rec IN OUT NOCOPY DEBRIEF_LINE_Rec_type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
select greatest(nvl(rejected_flag,'N'),
nvl(completed_flag,'N'),
nvl(closed_flag,'N'),
nvl(cancelled_flag,'N'))
from jtf_task_statuses_b jtsb,
jtf_task_assignments jta,
csf_debrief_headers cdh
where cdh.debrief_header_id = p_debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and jtsb.task_status_id = jta.assignment_status_id;
select CHARGE_UPLOAD_STATUS,
IB_UPDATE_STATUS,
SPARE_UPDATE_STATUS,
labor_start_date
from CSF_DEBRIEF_LINES
where DEBRIEF_LINE_ID=P_DEBRIEF_LINE_ID;
select ctst.src_status_id
from csi_txn_sub_types ctst ,
cs_transaction_types_vl cttv
where ctst.cs_transaction_type_id = cttv.transaction_type_id
and cttv.transaction_type_id = p_transaction_type_id;
select instance_status_id
from csi_instance_statuses
where name = p_cp_status;
l_api_name CONSTANT VARCHAR2(30) := 'Update_debrief_line';
select primary_uom_code
from mtl_system_items_b
where inventory_item_id = p_inventory_item_id;
select uom_code
from csf_debrief_lines cdl
where debrief_line_id = P_DEBRIEF_LINE_Rec.debrief_line_id;
SAVEPOINT UPDATE_DEBRIEF_LINE_PVT;
csf_debrief_lines_pkg.update_ROW(
p_DEBRIEF_LINE_ID => p_DEBRIEF_LINE_rec.debrief_line_id,
p_DEBRIEF_HEADER_ID => p_DEBRIEF_LINE_rec.DEBRIEF_HEADER_ID,
p_DEBRIEF_LINE_NUMBER => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_NUMBER,
p_SERVICE_DATE => p_DEBRIEF_LINE_rec.SERVICE_DATE ,
p_BUSINESS_PROCESS_ID => p_DEBRIEF_LINE_rec.BUSINESS_PROCESS_ID ,
p_TXN_BILLING_TYPE_ID => p_DEBRIEF_LINE_rec.TXN_BILLING_TYPE_ID ,
p_INVENTORY_ITEM_ID => p_DEBRIEF_LINE_rec.INVENTORY_ITEM_ID ,
p_INSTANCE_ID => p_DEBRIEF_LINE_rec.INSTANCE_ID ,
p_ISSUING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.ISSUING_INVENTORY_ORG_ID,
p_RECEIVING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.RECEIVING_INVENTORY_ORG_ID,
p_ISSUING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.ISSUING_SUB_INVENTORY_CODE,
p_RECEIVING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.RECEIVING_SUB_INVENTORY_CODE,
p_ISSUING_LOCATOR_ID => p_DEBRIEF_LINE_rec.ISSUING_LOCATOR_ID ,
p_RECEIVING_LOCATOR_ID => p_DEBRIEF_LINE_rec.RECEIVING_LOCATOR_ID ,
p_PARENT_PRODUCT_ID => p_DEBRIEF_LINE_rec.PARENT_PRODUCT_ID ,
p_REMOVED_PRODUCT_ID => p_DEBRIEF_LINE_rec.REMOVED_PRODUCT_ID ,
p_STATUS_OF_RECEIVED_PART => l_cp_status_id,
p_ITEM_SERIAL_NUMBER => p_DEBRIEF_LINE_rec.ITEM_SERIAL_NUMBER ,
p_ITEM_REVISION => p_DEBRIEF_LINE_rec.ITEM_REVISION ,
p_ITEM_LOTNUMBER => p_DEBRIEF_LINE_rec.ITEM_LOTNUMBER ,
p_UOM_CODE => p_debrief_line_rec.uom_code ,
p_QUANTITY => P_DEBRIEF_LINE_Rec.quantity ,
p_RMA_HEADER_ID => p_DEBRIEF_LINE_rec.RMA_HEADER_ID ,
p_DISPOSITION_CODE => p_DEBRIEF_LINE_rec.DISPOSITION_CODE ,
p_MATERIAL_REASON_CODE => p_DEBRIEF_LINE_rec.MATERIAL_REASON_CODE ,
p_LABOR_REASON_CODE => p_DEBRIEF_LINE_rec.LABOR_REASON_CODE ,
p_EXPENSE_REASON_CODE => p_DEBRIEF_LINE_rec.EXPENSE_REASON_CODE ,
p_LABOR_START_DATE => trunc(p_DEBRIEF_LINE_rec.labor_start_date,'MI'),
p_LABOR_END_DATE => trunc(p_DEBRIEF_LINE_rec.labor_end_date,'MI'),
p_STARTING_MILEAGE => p_DEBRIEF_LINE_rec.STARTING_MILEAGE ,
p_ENDING_MILEAGE => p_DEBRIEF_LINE_rec.ENDING_MILEAGE ,
p_EXPENSE_AMOUNT => p_DEBRIEF_LINE_rec.EXPENSE_AMOUNT ,
p_CURRENCY_CODE => p_DEBRIEF_LINE_rec.CURRENCY_CODE ,
p_DEBRIEF_LINE_STATUS_ID => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_STATUS_ID ,
p_return_reason_code => p_debrief_line_rec.return_reason_code,
p_CHANNEL_CODE =>p_DEBRIEF_LINE_rec.CHANNEL_CODE,
p_CHARGE_UPLOAD_STATUS => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_STATUS ,
p_CHARGE_UPLOAD_MSG_CODE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MSG_CODE ,
p_CHARGE_UPLOAD_MESSAGE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MESSAGE ,
p_IB_UPDATE_STATUS => p_DEBRIEF_LINE_rec.IB_UPDATE_STATUS ,
p_IB_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.IB_UPDATE_MSG_CODE ,
p_IB_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.IB_UPDATE_MESSAGE ,
p_SPARE_UPDATE_STATUS => p_DEBRIEF_LINE_rec.SPARE_UPDATE_STATUS ,
p_SPARE_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MSG_CODE ,
p_SPARE_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MESSAGE ,
p_CREATED_BY => p_debrief_line_rec.created_by,
p_CREATION_DATE => p_debrief_line_rec.creation_date,
p_LAST_UPDATED_BY => nvl(p_debrief_line_rec.last_updated_by,g_user_id),
p_LAST_UPDATE_DATE => nvl(p_debrief_line_rec.last_update_date,sysdate),
p_LAST_UPDATE_LOGIN => nvl(p_debrief_line_rec.last_update_login,g_login_id),
p_ATTRIBUTE1 => p_DEBRIEF_LINE_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_DEBRIEF_LINE_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_DEBRIEF_LINE_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_DEBRIEF_LINE_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_DEBRIEF_LINE_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_DEBRIEF_LINE_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_DEBRIEF_LINE_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_DEBRIEF_LINE_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_DEBRIEF_LINE_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_DEBRIEF_LINE_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_DEBRIEF_LINE_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_DEBRIEF_LINE_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_DEBRIEF_LINE_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_DEBRIEF_LINE_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_DEBRIEF_LINE_rec.ATTRIBUTE15,
p_ATTRIBUTE_CATEGORY => p_DEBRIEF_LINE_rec.ATTRIBUTE_CATEGORY,
P_TRANSACTION_TYPE_ID => P_DEBRIEF_LINE_REC.TRANSACTION_TYPE_ID,
p_RETURN_DATE => p_DEBRIEF_LINE_rec.RETURN_DATE,
p_usage_type => p_DEBRIEF_LINE_rec.usage_type,
p_dest_organization_id => p_DEBRIEF_LINE_rec.dest_organization_id,
p_dest_subinventory_name => p_DEBRIEF_LINE_rec.dest_subinventory_name,
p_carrier_code => p_DEBRIEF_LINE_rec.carrier_code,
p_shipping_method => p_DEBRIEF_LINE_rec.shipping_method,
p_shipment_number => p_DEBRIEF_LINE_rec.shipment_number,
p_waybill => p_DEBRIEF_LINE_rec.waybill,
p_expenditure_org_id => p_debrief_line_rec.expenditure_org_id,
p_project_id => p_debrief_line_rec.project_id,
p_project_task_id => p_debrief_line_rec.project_task_id
);
update_task_actuals(p_debrief_line_rec.debrief_header_id, -- modified for bug 3748610
x_return_status,
x_msg_count,
x_msg_data);
-- Calling the Update Resource Location API to Update Resource's Locatio
if l_debrief_type='L' then
Begin
select jta.resource_id,
loc.geometry
into z_resource_id,
z_location
from csf_debrief_lines cdl,
csf_debrief_headers csf,
jtf_task_assignments jta,
jtf_tasks_b jtb,
hz_party_sites p,
hz_locations loc
where csf.debrief_header_id = l_debrief_line_rec.debrief_header_id
and debrief_line_id = l_debrief_line_id
and csf.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = csf.task_assignment_id
and jta.task_id = jtb.task_id
and jtb.address_id = p.party_site_id
and p.location_id = loc.location_id;
select max(actual_start_date)
into z_start_date
from jtf_task_assignments
where resource_id = z_resource_id;
select object_version_number
into z_object_version_number
from jtf_rs_resource_extns
where resource_id = z_resource_id;
/* jtf_rs_resource_pub.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => z_resource_id,
P_RESOURCE_NUMBER => null,
P_LOCATION => z_location,
P_object_version_num => z_object_version_number,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
-- Calling the Update Task Assignment API Location API to Update Resource's Location
if P_Upd_tskassgnstatus = 'Y' then
BEGIN
Validate_Task_Assignment_Satus(
P_Api_Version_Number =>1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Task_Assignment_status =>P_Task_Assignment_status,
X_TA_STATUS_ID =>l_assignment_status_id,
X_Return_Status =>X_Return_Status,
X_Msg_Count =>X_MSG_COUNT,
X_Msg_Data =>X_MSG_DATA);
select jta.task_assignment_id,
jta.object_version_number,
jta.task_id
into l_task_assignment_id,
l_object_version_number,
l_task_id
from jtf_task_assignments jta,
csf_debrief_headers csf
where csf.task_assignment_id = jta.task_assignment_id
and csf.debrief_header_id = p_debrief_line_rec.debrief_header_id;
CSF_TASK_ASSIGNMENTS_PUB.UPDATE_ASSIGNMENT_STATUS(
P_API_VERSION =>1.0 ,
P_INIT_MSG_LIST =>FND_API.G_FALSE,
P_COMMIT =>FND_API.G_FALSE,
X_RETURN_STATUS =>x_return_status,
X_MSG_COUNT =>x_msg_count,
X_MSG_DATA =>x_msg_data,
P_TASK_ASSIGNMENT_ID =>l_task_assignment_id,
P_ASSIGNMENT_STATUS_ID =>l_assignment_status_id,
P_OBJECT_VERSION_NUMBER =>l_object_version_number,
P_UPDATE_TASK =>'T',
X_TASK_OBJECT_VERSION_NUMBER =>l_task_object_version_number,
X_TASK_STATUS_ID =>l_task_status_id);
FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_UPDATE_TASK_ASSIGNMENT');
'CSFUPDATE',
'CSF:Update Debrief Lines',
null,
FALSE,
1.0,
p_debrief_line_rec.debrief_header_id);
ROLLBACK TO UPDATE_DEBRIEF_LINE_PVT;
ROLLBACK TO UPDATE_DEBRIEF_LINE_PVT;
ROLLBACK TO UPDATE_DEBRIEF_LINE_PVT;
End Update_debrief_line;
select mp.organization_code,
msi.secondary_inventory_name
from mtl_secondary_inventories msi,
mtl_parameters mp
where mp.organization_id = p_organization_id
and msi.organization_id = mp.organization_id
and secondary_inventory_name = p_subinventory_code;
select msib.inventory_item_id
from mtl_system_items_b msib,cs_billing_type_categories cbtc
where msib.organization_id = nvl(p_organization_id,fnd_profile.value('CS_INV_VALIDATION_ORG'))
and msib.inventory_item_id = p_inventory_item_id
and msib.material_billable_flag = cbtc.billing_type;
select 1
from csi_item_instances
where INSTANCE_ID=P_INSTANCE_ID;
select 1
from csf_debrief_headers
where debrief_header_id=p_debrief_header_id;
select 1
from cs_business_processes
where BUSINESS_PROCESS_ID=P_BUSINESS_PROCESS_ID;
select 1
from cs_transaction_types vl
where transaction_type_id=P_Transaction_type_id;
select jtb.task_id,
jtb.customer_id,
jtb.cust_account_id,
jta.resource_id
into l_task_id,
l_party_id,
l_cust_account_id,
l_resource_id
from jtf_task_assignments jta,
jtf_tasks_b jtb
where jta.task_id = jtb.task_id
and jta.task_assignment_id = P_task_assignment_id;
PROCEDURE UPDATE_TASK_ACTUAL_DATES (
p_task_id IN NUMBER,
p_actual_start_date IN DATE,
p_actual_end_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_version number;
SELECT OBJECT_VERSION_NUMBER
FROM JTF_TASKS_VL
WHERE TASK_ID = p_task_id;
csf_tasks_pub.update_task (
p_api_version => 1.0,
p_object_version_number => l_version,
p_task_id => p_task_id,
p_actual_start_date => p_actual_start_date,
p_actual_end_date => p_actual_end_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
) ;
procedure update_actual_times (
p_task_assignment_id in number,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) IS
cursor c_actual_times is
select max(cdh.debrief_header_id),
min(cdl.labor_start_date),
max(cdl.labor_end_date)
from csf_debrief_headers cdh,
csf_debrief_lines cdl
where cdh.task_assignment_id = p_task_assignment_id
and cdl.debrief_header_id = cdh.debrief_header_id
and cdl.labor_start_date is not null
and cdl.labor_end_date is not null;
select cdh.debrief_header_id
from csf_debrief_headers cdh,jtf_task_assignments jta
where cdh.task_assignment_id = jta.task_assignment_id
and jta.task_assignment_id =p_task_assignment_id
and (jta.actual_start_date is not null
or jta.actual_end_date is not null
or jta.actual_effort is not null);
update_task_actuals(l_debrief_header_id,
x_return_status,
x_msg_count,
x_msg_data);
procedure update_actual_times (
p_debrief_header_id in number,
p_start_date in date,
p_end_date in date,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) IS
l_task_id number := null;
select task_id,
jta.task_assignment_id,
least(nvl(actual_start_date,p_start_date),
nvl(p_start_date,fnd_api.g_miss_date)),
greatest(nvl(actual_end_date,p_end_date),
nvl(p_end_date,fnd_api.g_miss_date)),
jta.object_version_number,
jta.resource_type_code,
jta.resource_id
from jtf_task_assignments jta,
csf_debrief_headers cdh
where debrief_header_id = p_debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and (nvl(actual_start_date,p_start_date) >=
nvl(p_start_date,actual_start_date)
or nvl(actual_end_date,p_end_date) <=
nvl(p_end_date,actual_end_date));
select least(nvl(actual_start_date,p_start_date),
nvl(p_start_date,fnd_api.g_miss_date)),
greatest(nvl(actual_end_date,p_end_date),
nvl(p_end_date,fnd_api.g_miss_date)),
object_version_number
from jtf_tasks_b
where task_id = l_task_id
and (nvl(actual_start_date,p_start_date) >=
nvl(p_start_date,actual_start_date)
or nvl(actual_end_date,p_end_date) <=
nvl(p_end_date,actual_end_date));
csf_task_assignments_pub.update_task_assignment (
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_task_assignment_id => l_task_assignment_id,
p_object_version_number => l_object_version,
p_actual_start_date => l_start_date,
p_actual_end_date => l_end_date,
p_resource_type_code => l_resource_type_code,
p_resource_id => l_resource_id,
x_task_object_version_number => l_task_object_version,
x_task_status_id => l_task_status_id);
csf_tasks_pub.update_task (
p_api_version => 1.0,
p_object_version_number => l_object_version,
p_task_id => l_task_id,
p_actual_start_date => l_start_date,
p_actual_end_date => l_end_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data) ;
select customer_product_id
from cs_incidents_all_b
where incident_id = p_incident_id;
select max(ccv.value_timestamp),
max(jtb.creation_date)
from csi_counter_associations cca,
cs_counter_values ccv,
jtf_tasks_b jtb
where cca.source_object_id = l_customer_product_id
and cca.source_object_code = 'CP'
and ccv.counter_id(+)= cca.counter_id
and jtb.task_id = p_task_id;
Select cdh.debrief_number,
cdl.labor_start_date,
cdl.labor_end_date,
jtb.task_number
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta ,
jtf_tasks_b jtb
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdl.labor_start_date is not null
and ((p_debrief_line_id is not null
and cdl.debrief_line_id <> p_debrief_line_id)
or (p_debrief_line_id is null))
and jta.resource_type_code = p_resource_type_code
and jta.resource_id = p_resource_id
and jtb.task_id = jta.task_id
and jtb.task_id <> p_task_id
and (p_labor_start_date >= cdl.labor_start_date and p_labor_start_date < cdl.labor_end_date
or p_labor_end_date > cdl.labor_start_date and p_labor_end_date < cdl.labor_end_date
or cdl.labor_start_date > p_labor_start_date and cdl.labor_start_date < p_labor_end_date
or cdl.labor_end_date > p_labor_start_date and cdl.labor_end_date < p_labor_end_date);
PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE(x_return_status out nocopy varchar2) is
l_task_assignment_id number := null;
select cdh.debrief_header_id,resource_type_code,resource_id
into l_debrief_header_id,l_old_resource_type,l_old_resource_id
from csf_debrief_headers cdh,jtf_task_assignments jta,csf_debrief_lines cdl
where jta.task_assignment_id = l_task_assignment_id
and cdl.debrief_header_id = cdh.debrief_header_id
and cdh.task_assignment_id = jta.task_assignment_id
and rownum = 1;
PROCEDURE TASK_ASSIGNMENT_PRE_DELETE(x_return_status out nocopy varchar2) is
l_task_assignment_id number := null;
select debrief_header_id
into l_debrief_header_id
from csf_debrief_headers
where task_assignment_id = l_task_assignment_id;
PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2) is
l_msg_data varchar(2000);
select debrief_header_id
from csf_debrief_headers
where task_assignment_id = p_task_assignment_id;
select 'Y'
from csf_debrief_headers cdh,
csf_debrief_lines cdl,
cs_transaction_types ctt,
cs_txn_billing_types ctbt
where cdh.task_assignment_id = p_task_assignment_id
and cdl.debrief_header_id = cdh.debrief_header_id
and ctt.transaction_type_id = cdl.transaction_type_id
and ctbt.transaction_type_id = ctt.transaction_type_id
and ctbt.billing_type = 'L'
and ctt.travel_flag = 'Y';
select 'Y'
from csf_debrief_headers cdh,
csf_debrief_lines cdl,
cs_transaction_types ctt,
cs_txn_billing_types ctbt
where cdh.task_assignment_id = p_task_assignment_id
and cdl.debrief_header_id = cdh.debrief_header_id
and ctt.transaction_type_id = cdl.transaction_type_id
and ctbt.transaction_type_id = ctt.transaction_type_id
and ctbt.billing_type = 'L'
and nvl(ctt.travel_flag,'N') = 'N';
select jtsbold.travel_flag old_travel_flag,
jtsbnew.travel_flag new_travel_flag,
trunc(jtaab.creation_date,'MI') creation_date
from jtf_task_assignments_audit_b jtaab,
jtf_task_statuses_b jtsbnew,
jtf_task_statuses_b jtsbold
where jtsbold.task_status_id = jtaab.old_assignment_status_id
and jtsbnew.task_status_id = jtaab.new_assignment_status_id
and jtaab.old_assignment_status_id <> jtaab.new_assignment_status_id
and jtaab.assignment_id = p_task_assignment_id
order by jtaab.creation_date desc;
select jtsbold.working_flag old_working_flag,
jtsbnew.working_flag new_working_flag,
trunc(jtaab.creation_date,'MI') creation_date
from jtf_task_assignments_audit_b jtaab,
jtf_task_statuses_b jtsbnew,
jtf_task_statuses_b jtsbold
where jtsbold.task_status_id = jtaab.old_assignment_status_id
and jtsbnew.task_status_id = jtaab.new_assignment_status_id
and jtaab.old_assignment_status_id <> jtaab.new_assignment_status_id
and jtaab.assignment_id = p_task_assignment_id
order by jtaab.creation_date desc;
select working_flag,travel_flag
from jtf_task_statuses_b
where task_status_id = l_assignment_status_id;
select jttb.rule
from jtf_task_types_b jttb,
jtf_tasks_b jtb
where jtb.task_id = l_task_id
and jttb.task_type_id = jtb.task_type_id;
select 'Y'
from jtf_task_assignments jta,
jtf_task_statuses_b jtsb
where assignee_role = 'ASSIGNEE'
and resource_type_code = l_resource_type_code
and resource_id = l_resource_id
and jtsb.task_status_id = jta.assignment_status_id
and (working_flag = 'Y' or travel_flag = 'Y');
select assignee_role
from jtf_task_assignments
where task_assignment_id = p_task_assignment_id;
log('task_assignment_post_update','task_assignment_id: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id);
log('task_assignment_post_update','hook assignee role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role);
log('task_assignment_post_update','l_assignee_role: '||l_assignee_role);
log('task_assignment_post_update','l_task_id: '||l_task_id);
log('task_assignment_post_update','l_task_type_rule: '||l_task_type_rule);
log('task_assignment_post_update','l_task_assignment_id: '||l_task_assignment_id);
log('task_assignment_post_update','l_resource_type_code: '||l_resource_type_code);
log('task_assignment_post_update','l_resource_id: '||l_resource_id);
log('task_assignment_post_update','l_assignment_status_id: '||l_assignment_status_id);
log('task_assignment_post_update','l_travel_flag: '||l_travel_flag);
log('task_assignment_post_update','l_working_flag: '||l_working_flag);
log('task_assignment_post_update','update csp_planning_parameters');
update csp_planning_parameters cpp
set hz_location_id =
(select hps.location_id
from hz_party_sites hps,
jtf_tasks_b jtb,
jtf_task_assignments jta
where hps.party_site_id = jtb.address_id
and jta.task_id = jtb.task_id
and jta.task_assignment_id = l_task_assignment_id)
where cpp.stocking_site_type = 'TECHNICIAN'
and (cpp.organization_id,cpp.secondary_inventory) in
(select csi.organization_id,
csi.secondary_inventory_name
from csp_sec_inventories csi
where csi.owner_resource_type = l_resource_type_code
and csi.owner_resource_id = l_resource_id);
log('task_assignment_post_update','l_any_working_task: '||l_any_working_task);
log('task_assignment_post_update','update csp_planning_parameters in else');
update csp_planning_parameters cpp
set hz_location_id =
(select min(hps.location_id)
from hz_party_sites hps, csp_rs_cust_relations crcr,
hz_cust_acct_sites_all hcasa,hz_cust_site_uses_all hcsua
where crcr.customer_id = hcasa.cust_account_id
and hcasa.party_site_id = hps.party_site_id
and crcr.resource_type = l_resource_type_code
and crcr.resource_id = l_resource_id
and hcsua.site_use_code = 'SHIP_TO'
and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
and hcsua.primary_flag = 'Y'
and hcsua.status = 'A')
where cpp.stocking_site_type = 'TECHNICIAN'
and (cpp.organization_id,cpp.secondary_inventory) in
(select csi.organization_id,
csi.secondary_inventory_name
from csp_sec_inventories csi
where csi.owner_resource_type = l_resource_type_code
and csi.owner_resource_id = l_resource_id);
log('task_assignment_post_update','profile CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'||
fnd_profile.value('CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'));
log('task_assignment_post_update','l_travel_exists: '||l_travel_exists);
log('task_assignment_post_update','l_travel_exists: '||l_travel_exists);
log('task_assignment_post_update','l_task_assignment_id: '||l_task_assignment_id);
log('task_assignment_post_update','In c_audit_travel loop');
log('task_assignment_post_update','cr.old_travel_flag: '||cr.old_travel_flag);
log('task_assignment_post_update','l_travel_end_date: '||
to_char(l_travel_end_date,'dd-mon-yyyy hh24:mi:ss'));
log('task_assignment_post_update','l_travel_start_date: '||
to_char(l_travel_start_date,'dd-mon-yyyy hh24:mi:ss'));
log('task_assignment_post_update','end c_audit_travel loop');
log('task_assignment_post_update','l_working_exists: '||l_working_exists);
log('task_assignment_post_update','l_working_exists: '||l_working_exists);
log('task_assignment_post_update','In c_audit_working loop');
log('task_assignment_post_update','cr.old_working_flag: '||cr.old_working_flag);
log('task_assignment_post_update','l_working_end_date: '||
to_char(l_working_end_date,'dd-mon-yyyy hh24:mi:ss'));
log('task_assignment_post_update','l_working_start_date: '||
to_char(l_working_start_date,'dd-mon-yyyy hh24:mi:ss'));
log('task_assignment_post_update','end c_audit_working loop');
log('task_assignment_post_update','l_debrief_header_id: '||l_debrief_header_id);
log('task_assignment_post_update','l_record_counter: '||l_record_counter);
log('task_assignment_post_update','l_business_process_id: '||l_business_process_id);
log('task_assignment_post_update','l_transaction_type_id: '||l_transaction_type_id);
l_debrief_line(l_record_counter).last_updated_by := fnd_global.user_id;
l_debrief_line(l_record_counter).last_update_date := sysdate;
l_debrief_line(l_record_counter).last_update_login := fnd_global.user_id;
log('task_assignment_post_update','l_business_process_id: '||l_business_process_id);
log('task_assignment_post_update','l_transaction_type_id: '||l_transaction_type_id);
l_debrief_line(l_record_counter).last_updated_by := fnd_global.user_id;
l_debrief_line(l_record_counter).last_update_date := sysdate;
l_debrief_line(l_record_counter).last_update_login := fnd_global.user_id;
log('task_assignment_post_update','l_record_counter: '||l_record_counter);
log('task_assignment_post_update','l_debrief_header_id: '||l_debrief_header_id);
log('task_assignment_post_update','calling csf_debrief_pub.create_debrief');
log('task_assignment_post_update','l_return_status: '||l_return_status);
log('task_assignment_post_update','Calling csf_debrief_pub.create_debrief_lines');
log('task_assignment_post_update','l_return_status: '||l_return_status);
log('task_assignment_post_update','x_return_status: '||x_return_status);
log('task_assignment_post_update','End of task_assignment_post_update');
select decode(fnd_profile.value('CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'),
'STATUS',jtsb.working_flag,'N')
from jtf_task_assignments_audit_b jtaab,
jtf_task_statuses_b jtsb
where jtsb.task_status_id = jtaab.new_assignment_status_id
and jtaab.assignment_id = p_task_assignment_id
and jtsb.working_flag = 'Y';
select nvl(cdh.processed_flag,'PENDING')
from csf_debrief_headers cdh,
csf_debrief_lines cdl
where cdh.task_assignment_id = p_task_assignment_id
and cdh.debrief_header_id = cdl.debrief_header_id;
procedure update_task_actuals (
p_debrief_header_id in number,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) IS
l_task_id number := null;
select task_id, jta.task_assignment_id, jta.object_version_number,
jta.resource_type_code, jta.resource_id
from jtf_task_assignments jta,
csf_debrief_headers cdh
where jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = p_debrief_header_id;
select parent_task_id,object_version_number
from jtf_tasks_b
where task_id = l_task_id ;
select object_version_number
from jtf_tasks_b
where task_id = l_parent_task_id ;
select min(labor_start_date),
max(labor_end_date),
min(debrief_line_id)
from csf_debrief_lines cdl,
cs_transaction_types ctt
where cdl.debrief_header_id = p_debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'ORDER';
select min(labor_start_date),
max(labor_end_date),
min(debrief_line_id)
from csf_debrief_lines cdl,
cs_transaction_types ctt
where cdl.debrief_header_id = p_debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'RETURN'
and cdl.labor_start_date between p_start_date_order and p_end_date_order
and cdl.labor_end_date between p_start_date_order and p_end_date_order;
select min(labor_start_date),
max(labor_end_date),
min(debrief_line_id)
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
cs_transaction_types ctt
where jta.task_id = l_task_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = cdl.debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'ORDER';
select min(labor_start_date),
max(labor_end_date),
min(debrief_line_id)
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
cs_transaction_types ctt
where jta.task_id = l_task_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = cdl.debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'RETURN'
and cdl.labor_start_date between p_start_date_order and p_end_date_order
and cdl.labor_end_date between p_start_date_order and p_end_date_order;
select min(labor_start_date),
max(labor_end_date)
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb,
cs_transaction_types ctt
where jtb.parent_task_id = l_parent_task_id
and jta.task_id = jtb.task_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = cdl.debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'ORDER';
select min(labor_start_date),
max(labor_end_date)
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb,
cs_transaction_types ctt
where jtb.parent_task_id = l_parent_task_id
and jta.task_id = jtb.task_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = cdl.debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'RETURN'
and cdl.labor_start_date between p_start_date_order and p_end_date_order
and cdl.labor_end_date between p_start_date_order and p_end_date_order;
select uom_code, quantity
from jtf_task_assignments jta,
csf_debrief_headers cdh,
csf_debrief_lines cdl,
cs_transaction_types ctt
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = p_debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'ORDER'
and (cdl.inventory_item_id in
(select msib.inventory_item_id
from mtl_system_items_b msib
where msib.organization_id = l_organization_id
and msib.inventory_item_id = cdl.inventory_item_id
and msib.material_billable_flag = 'L')
or
cdl.inventory_item_id is null)
union all
select uom_code, (quantity * -1)
from jtf_task_assignments jta,
csf_debrief_headers cdh,
csf_debrief_lines cdl,
cs_transaction_types ctt
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdh.debrief_header_id = p_debrief_header_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'RETURN'
and (cdl.inventory_item_id in
(select msib.inventory_item_id
from mtl_system_items_b msib
where msib.organization_id = l_organization_id
and msib.inventory_item_id = cdl.inventory_item_id
and msib.material_billable_flag = 'L')
or
cdl.inventory_item_id is null);
select uom_code, quantity
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb,
cs_transaction_types ctt
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'ORDER'
and jtb.task_id = jta.task_id
and jtb.task_id = l_task_id
and (cdl.inventory_item_id in
(select msib.inventory_item_id
from mtl_system_items_b msib
where msib.organization_id = l_organization_id
and msib.inventory_item_id = cdl.inventory_item_id
and msib.material_billable_flag = 'L')
or
cdl.inventory_item_id is null)
union all
select uom_code, (quantity * -1)
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb,
cs_transaction_types ctt
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'RETURN'
and jtb.task_id = jta.task_id
and jtb.task_id = l_task_id
and (cdl.inventory_item_id in
(select msib.inventory_item_id
from mtl_system_items_b msib
where msib.organization_id = l_organization_id
and msib.inventory_item_id = cdl.inventory_item_id
and msib.material_billable_flag = 'L')
or
cdl.inventory_item_id is null);
select uom_code, quantity
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb,
cs_transaction_types ctt
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'ORDER'
and jtb.task_id = jta.task_id
and jtb.parent_task_id = l_parent_task_id
and (cdl.inventory_item_id in
(select msib.inventory_item_id
from mtl_system_items_b msib
where msib.organization_id = l_organization_id
and msib.inventory_item_id = cdl.inventory_item_id
and msib.material_billable_flag = 'L')
or
cdl.inventory_item_id is null)
union all
select uom_code, (quantity * -1)
from csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb,
cs_transaction_types ctt
where cdh.debrief_header_id = cdl.debrief_header_id
and jta.task_assignment_id = cdh.task_assignment_id
and cdl.transaction_type_id = ctt.transaction_type_id
and ctt.line_order_category_code = 'RETURN'
and jtb.task_id = jta.task_id
and jtb.parent_task_id = l_parent_task_id
and (cdl.inventory_item_id in
(select msib.inventory_item_id
from mtl_system_items_b msib
where msib.organization_id = l_organization_id
and msib.inventory_item_id = cdl.inventory_item_id
and msib.material_billable_flag = 'L')
or
cdl.inventory_item_id is null);
select fnd_profile.value('CSF_UOM_HOURS')
from dual;
csf_task_assignments_pub.update_task_assignment (
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_task_assignment_id => l_task_assignment_id,
p_object_version_number => l_object_version,
p_actual_start_date => l_start_date,
p_actual_end_date => l_end_date,
p_actual_effort => l_duration_sum, -- added for bug 3629886
p_actual_effort_uom => l_t_uom, -- added for bug 3629886
p_resource_type_code => l_resource_type_code,
p_resource_id => l_resource_id,
x_task_object_version_number => l_task_object_version,
x_task_status_id => l_task_status_id);
csf_tasks_pub.update_task (
p_api_version => 1.0,
p_object_version_number => l_object_version,
p_task_id => l_task_id,
p_actual_start_date => l_start_date,
p_actual_end_date => l_end_date,
p_actual_effort => l_duration_sum, -- added for bug 3629886
p_actual_effort_uom => l_t_uom, -- added for bug 3629886
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data) ;
csf_tasks_pub.update_task (
p_api_version => 1.0,
p_object_version_number => l_object_version,
p_task_id => l_parent_task_id,
p_actual_start_date => l_start_date,
p_actual_end_date => l_end_date,
p_actual_effort => l_duration_sum, -- added for bug 3629886
p_actual_effort_uom => l_t_uom, -- added for bug 3629886
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data) ;
select nvl(incident_occurred_date,incident_date)
from jtf_task_assignments jta , cs_incidents_all cia, jtf_tasks_b jtb
where jta.task_assignment_id = p_task_assignment_id
and jtb.task_id = jta.task_id
and cia.incident_id = jtb.source_object_id
and jtb.source_object_type_code = 'SR';
select 1
from all_procedures
where object_name = 'INV_SERIAL_NUMBER_PUB'
and procedure_name = 'IS_SERIAL_CONTROLLED'
and owner = l_schema_name;
select oracle_username
from fnd_oracle_userid
where read_only_flag = 'U';
select serial_number_control_code
from mtl_system_items_b
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;