[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE NUMBER := 1;
'csf.plsql.csf_debrief_update_pkg.'||p_procedure,
p_message);
l_ib_update_status Varchar2(50);
l_spare_update_status Varchar2(50);
select charge_upload_status,ib_update_status,spare_update_status
into l_charge_upload_status,l_ib_update_status,l_spare_update_status
from csf_debrief_lines
where debrief_line_id = p_debrief_line_id
for update nowait;
l_ib_update_status Varchar2(50) ;
l_spare_update_status Varchar2(50) ;
select estimate_detail_id
from cs_estimate_details
where source_code = 'SD'
and source_id = p_debrief_line_id;
select fnd_profile.value('CSF_DEBRIEF_ERROR_STATUS'),
jta.assignment_status_id,
jta.object_version_number
from jtf_task_assignments jta
where jta.task_assignment_id = l_task_assignment_id;
select cdl.debrief_line_id,
cdl.service_date,
cdl.transaction_type_id,
cdl.inventory_item_id,
cdl.issuing_inventory_org_id,
cdl.receiving_inventory_org_id,
cdl.issuing_sub_inventory_code,
cdl.receiving_sub_inventory_code,
cdl.issuing_locator_id,
cdl.receiving_locator_id,
cdl.parent_product_id,
cdl.removed_product_id,
cdl.item_serial_number,
cdl.item_revision,
cdl.item_lotnumber,
cdl.uom_code,
cdl.quantity,
cdl.labor_start_date,
cdl.labor_end_date,
cdl.expense_amount,
cdl.currency_code,
cdl.charge_upload_status,
cdl.ib_update_status,
cdl.spare_update_status,
cdl.business_process_id,
cdl.return_reason_code,
cdl.instance_id,
cdl.status_of_received_part, --added for bug 3192060
cdl.item_operational_status_code,
cdl.usage_type,
cdl.return_organization_id,
cdl.return_subinventory_name,
cdl.carrier_code,
cdl.shipping_method,
cdl.shipping_number,
cdl.waybill,
cdl.removed_product_id,
cdl.expenditure_org_id,
cdl.project_id,
cdl.project_task_id
from csf_debrief_lines cdl
where cdl.debrief_header_id = p_debrief_header_id
and nvl(cdl.quantity,-1) <> 0
and nvl(cdl.inventory_item_id,-1) <> 9999999
and nvl(p_debrief_line_id,cdl.debrief_line_id)=cdl.debrief_line_id;
SELECT cdh.debrief_header_id,
jtb.source_object_type_code source_type_code,
ciab.incident_id ,
to_number(null) repair_line_id,
ciab.customer_id,
ciab.account_id customer_account_id,
cdh.debrief_number ,
jta.task_assignment_id,
jtsb.cancelled_flag,
jtsb.rejected_flag,
jtsb.completed_flag,
jtsb.closed_flag,
cdh.travel_start_time, --FOR TRAVEL LINES
cdh.travel_end_time, ciab.system_id system_id
from
JTF_TASK_STATUSES_B jtsb,
CSF_DEBRIEF_HEADERS cdh,
JTF_TASKS_B jtb,
JTF_TASK_ASSIGNMENTS jta,
cs_incidents_all_b ciab
WHERE cdh.task_assignment_id = jta.task_assignment_id
and jta.task_id = jtb.task_id
and nvl(jtb.deleted_flag,'N') <> 'Y'
and jta.assignment_status_id = jtsb.task_status_id
and jta.assignee_role = 'ASSIGNEE'
and jtb.source_object_type_code = 'SR'
and ciab.incident_id = jtb.source_object_id
and cdh.debrief_header_id = p_header_id
union all
SELECT cdh.debrief_header_id,
jtb.source_object_type_code,
cr.incident_id,
cr.repair_line_id,
jtb.customer_id ,
jtb.cust_account_id,
cdh.debrief_number ,
jta.task_assignment_id,
jtsb.cancelled_flag,
jtsb.rejected_flag,
jtsb.completed_flag,
jtsb.closed_flag,
cdh.travel_start_time, --FOR TRAVEL LINES
cdh.travel_end_time, to_number(null) system_id
from
JTF_TASK_STATUSES_B jtsb,
CSF_DEBRIEF_HEADERS cdh,
JTF_TASKS_B jtb,
JTF_TASK_ASSIGNMENTS jta,
csd_repairs cr
WHERE cdh.task_assignment_id = jta.task_assignment_id
and jta.task_id = jtb.task_id
and nvl(jtb.deleted_flag,'N') <> 'Y'
and jta.assignment_status_id = jtsb.task_status_id
and jta.assignee_role = 'ASSIGNEE'
and jtb.source_object_type_code = 'DR'
and jtb.source_object_id=cr.repair_line_id
and cdh.debrief_header_id = p_header_id ;
SELECT cdh.debrief_header_id,
jtb.source_object_type_code source_type_code,
ciab.incident_id ,
to_number(null) repair_line_id,
ciab.customer_id,
ciab.account_id customer_account_id,
cdh.debrief_number ,
jta.task_assignment_id,
jtsb.cancelled_flag,
jtsb.rejected_flag,
jtsb.completed_flag,
jtsb.closed_flag,
cdh.travel_start_time, --FOR TRAVEL LINES
cdh.travel_end_time, ciab.system_id system_id
from
JTF_TASK_STATUSES_B jtsb,
CSF_DEBRIEF_HEADERS cdh,
JTF_TASKS_B jtb,
JTF_TASK_ASSIGNMENTS jta,
cs_incidents_all_b ciab
WHERE cdh.task_assignment_id = jta.task_assignment_id
and jta.task_id = jtb.task_id
and nvl(jtb.deleted_flag,'N') <> 'Y'
and jta.assignment_status_id = jtsb.task_status_id
and jta.assignee_role = 'ASSIGNEE'
and jtb.source_object_type_code = 'SR'
and ciab.incident_id = jtb.source_object_id
and ciab.incident_id = p_incident_id
and cdh.debrief_header_id =
nvl(p_debrief_header_id,cdh.debrief_header_id)
union all
SELECT cdh.debrief_header_id,
jtb.source_object_type_code,
cr.incident_id,
cr.repair_line_id,
jtb.customer_id ,
jtb.cust_account_id,
cdh.debrief_number ,
jta.task_assignment_id,
jtsb.cancelled_flag,
jtsb.rejected_flag,
jtsb.completed_flag,
jtsb.closed_flag,
cdh.travel_start_time, --FOR TRAVEL LINES
cdh.travel_end_time, to_number(null) system_id
from
JTF_TASK_STATUSES_B jtsb,
CSF_DEBRIEF_HEADERS cdh,
JTF_TASKS_B jtb,
JTF_TASK_ASSIGNMENTS jta,
csd_repairs cr
WHERE cdh.task_assignment_id = jta.task_assignment_id
and jta.task_id = jtb.task_id
and nvl(jtb.deleted_flag,'N') <> 'Y'
and jta.assignment_status_id = jtsb.task_status_id
and jta.assignee_role = 'ASSIGNEE'
and jtb.source_object_type_code = 'DR'
and jtb.source_object_id=cr.repair_line_id
and cr.incident_id = p_incident_id
and cdh.debrief_header_id =
nvl(p_debrief_header_id,cdh.debrief_header_id);
Select debrief_header_id
From csf_debrief_headers
Where processed_flag is null or processed_flag <> 'COMPLETED';
select create_cost_flag,
create_charge_flag,
travel_flag --FOR TRAVEL LINES TO BE REPLACED WITH TRAVEL
FLAG
from cs_transaction_types
where transaction_type_id = p_transaction_type_id;
select cttv.line_order_category_code,
cbtc.billing_category billing_type,
ctst.sub_type_id ,
ctst.transaction_type_id transaction_type_id_csi
from cs_transaction_types_vl cttv,
csi_txn_sub_types ctst,
csi_txn_types ctt,
mtl_system_items_b_kfv msibk,
cs_billing_type_categories cbtc
where cttv.transaction_type_id = p_transaction_type_id
and msibk.material_billable_flag = cbtc.billing_type
and msibk.inventory_item_id = p_inventory_item_id
and msibk.organization_id = p_inventory_org_id
and ctst.cs_transaction_type_id(+) = cttv.transaction_type_id
and ctt.source_application_id(+) = 513
and ctt.transaction_type_id(+) = ctst.transaction_type_id;
select internal_party_id
from csi_install_parameters;
select install_site_use_id,
ship_to_site_use_id
from cs_incidents_all
where incident_id = p_incident_id;
select party_site_id
from hz_party_site_uses
where party_site_use_id = p_install_site_id;
select comms_nl_trackable_flag
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
select meaning
from fnd_lookups
where lookup_type = 'CSF_INTERFACE_STATUS'
and lookup_code = p_code;
select 1
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.application_id = 513
and fcp.concurrent_program_name = 'CSFUPDATE'
and fcr.request_id < fnd_global.conc_request_id
and fcr.argument2 = to_char(p_debrief_header_id)
and fcr.phase_code in ('P','R');
select intransit_type
from mtl_interorg_parameters
where from_organization_id = v_frm_org_id
and to_organization_id = v_to_org_id;
select cila.organization_id,cila.subinventory_code
from csp_inv_loc_assignments cila,
jtf_task_assignments jta
where cila.default_code = 'OUT'
and cila.resource_type = jta.resource_type_code
and cila.resource_id = jta.resource_id
and jta.task_assignment_id = l_task_assignment_id;
SELECT inventory_item_id,
uom_code,
SUM(quantity) sum_qty
FROM csf_debrief_lines
WHERE debrief_header_id = p_debrief_header_id
AND inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
AND NVL(spare_update_status, 'FAILED') <> 'SUCCEEDED'
GROUP BY inventory_item_id, uom_code;
SELECT mr.inventory_item_id,
mr.reservation_uom_code,
SUM(mr.reservation_quantity)
FROM csp_requirement_headers crh,
csp_requirement_lines crl,
csp_req_line_details crld,
mtl_reservations mr
WHERE crh.task_assignment_id = p_task_assignment_id
AND crl.requirement_header_id = crh.requirement_header_id
AND crld.requirement_line_id = crl.requirement_line_id
AND crld.source_type = 'RES'
AND mr.reservation_id = crld.source_id
AND mr.inventory_item_id = nvl(p_inventory_item_id, mr.inventory_item_id)
GROUP BY mr.inventory_item_id, mr.reservation_uom_code;
log('main','calling delete_in_progress_charges');
Cs_service_billing_engine_pvt.Delete_In_Progress_Charges(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_incident_id => l_incident_id,
p_debrief_header_id => l_debrief_header_id, -- added for bug 3549864
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_ib_update_status,
l_spare_update_status,
l_business_process_id,
l_return_reason_code,
l_instance_id,
l_instance_status, --added for bug 3192060
l_item_operational_status_code,
l_usage_type,
l_return_organization_id,
l_return_subinventory_name,
l_carrier_code,
l_shipping_method,
l_shipping_number,
l_waybill,
l_recovered_instance_id,
l_expenditure_org_id,
l_project_id,
l_project_task_id;
log('main','l_spare_update_status:'||l_spare_update_status);
and nvl(l_spare_update_status,'N') <> 'SUCCEEDED' then
l_loop := 1;
l_spare_update_status := 'SUCCEEDED';
l_spare_update_status := 'SUCCEEDED';
l_spare_update_status := 'FAILED';
log('main','l_spare_update_status:'||l_spare_update_status);
l_spare_update_status := 'SUCCEEDED';
l_spare_update_status := 'FAILED';
log('main','l_spare_update_status:'||l_spare_update_status);
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => null,
p_charge_upload_status => l_spare_update_status,
p_spare_update_status => l_spare_update_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
update csf_debrief_headers
set travel_distance_in_km = l_header_distance_in_km,
travel_start_time = l_header_start_time,
travel_end_time = l_header_end_time
where debrief_header_id = l_debrief_header_id;
log('main','calling update_row when failed');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => substr(l_msg_data,1,2000),
p_charge_upload_status => l_charges_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
log('main','calling update_row when succeeded');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => null,
p_charge_upload_status => l_charges_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
log('main','calling update_row when create_charge_flag = N');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => null,
p_charge_upload_status => l_charges_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
log('main','l_ib_update_status:'||l_ib_update_status||' l_trackable:'||l_trackable||' l_charges_interface_status:'||l_charges_interface_status||'l_charge_upload_status:'||l_charge_upload_status);
if nvl(l_ib_update_status,' ') <> 'SUCCEEDED'
and l_trackable ='Y'
and (l_charges_interface_status = 'SUCCEEDED'
or l_charge_upload_status ='SUCCEEDED' )
Then
---------------------------------------------------------------------------
-- UPDATE INSTALL BASE --
---------------------------------------------------------------------------
l_instance_status_id :=to_number(l_instance_status); --added for bug 3192060
log('main','calling csf_ib.update_install_base');
csf_ib.update_install_base(
p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_new_instance_id => l_new_instance_id, --
p_in_out_flag => l_in_out_flag, --
p_transaction_type_id => l_transaction_type_id_csi,
p_txn_sub_type_id => l_txn_sub_type_id,
p_instance_id => l_instance_id,
p_inventory_item_id => l_inventory_item_id,
p_inv_organization_id => l_organization_id,
p_inv_subinventory_name => l_subinventory_code,
p_inv_locator_id => l_locator_id,
p_quantity => l_quantity,
p_inv_master_organization_id => l_inv_master_organization_id,
p_mfg_serial_number_flag => 'N',
p_serial_number => l_item_serial_number,
p_lot_number => l_item_lotnumber,
p_revision => l_revision,
p_unit_of_measure => l_uom_code,
p_party_id => l_party_id,
p_party_account_id => l_customer_account_id,
p_party_site_id => l_party_site_id,
p_parent_instance_id => l_parent_product_id,
p_instance_status_id => l_instance_status_id, --added for bug 3192060
p_item_operational_status_code => l_item_operational_status_code,
p_system_id => l_system_id, --added for bug 11936065
p_recovered_instance_id => l_recovered_instance_id); --added for bug 12640969;
log('main','update_row a');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => substr(l_msg_data,1,2000),
p_charge_upload_status => l_charges_interface_status,
p_ib_update_status => l_ib_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
if nvl(l_ib_update_status,' ') <> 'SUCCEEDED'
and l_billing_type='M' and l_trackable='Y'
and (l_charges_interface_status= 'SUCCEEDED'
or l_charge_upload_status='SUCCEEDED')
Then
if l_line_category_code <> 'RETURN' then
l_instance_id := l_new_instance_id;
log('main','update_row b');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => null,
p_instance_id => l_instance_id,
p_ib_update_status => l_ib_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
log('main','l_spare_update_status:'||l_spare_update_status||' l_charges_interface_status:'||l_charges_interface_status||' l_charge_upload_status:'||l_charge_upload_status);
log('main','l_ib_interface_status:'||l_ib_interface_status||' l_ib_update_status:'||l_ib_update_status||' l_trackable:'||l_trackable);
if nvl(l_spare_update_status,' ') <> 'SUCCEEDED'
and (l_charges_interface_status = 'SUCCEEDED'
or l_charge_upload_status ='SUCCEEDED' )
and ( (l_ib_interface_status = 'SUCCEEDED' and l_trackable='Y')
or (l_ib_update_status = 'SUCCEEDED' and l_trackable ='Y')
or l_trackable ='N' or l_trackable is null ) Then
l_return_status := fnd_api.g_ret_sts_success;
IF jtf_usr_hks.Ok_To_Execute('CSF_DEBRIEF_UPDATE_PKG',
'TRANSACT_MATERIAL','B','C') THEN
csf_debrief_update_pkg.g_debrief_line_id := l_debrief_line_id;
csf_debrief_update_pkg.g_account_id := null;
csf_debrief_pub.call_internal_hook('CSF_DEBRIEF_UPDATE_PKG',
'TRANSACT_MATERIAL','B',
l_return_status);
p_account_id => csf_debrief_update_pkg.g_account_id,
px_transaction_header_id => l_inv_transaction_header_id,
px_transaction_id => l_inv_transaction_id,
p_transaction_source_id => l_debrief_header_id,
p_trx_source_line_id => l_debrief_line_id,
p_transaction_source_name => l_debrief_number,
p_transaction_date => l_service_date );
update csf_debrief_lines
set material_transaction_id = l_inv_transaction_id
where debrief_line_id = l_debrief_line_id;
log('main','calling update_row c');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => null,
p_spare_update_status => l_inv_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
l_spare_update_status := 'SUCCEEDED';
l_spare_update_status := 'FAILED';
l_spare_update_status := 'SUCCEEDED';
l_spare_update_status := 'FAILED';
end if; --spares update status
log('main','calling update_row d');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => substr(l_msg_data,1,2000),
p_charge_upload_status => l_charges_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
log('main','calling update_row e');
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => substr(l_msg_data,1,2000),
p_charge_upload_status => l_charges_interface_status,
p_ib_update_status => l_ib_interface_status,
p_spare_update_status => l_inv_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
if nvl(l_spare_update_status,' ') <> 'SUCCEEDED' and l_billing_type='M'
and (l_charges_interface_status= 'SUCCEEDED'
or l_charge_upload_status='SUCCEEDED')
and ( (l_ib_interface_status = 'SUCCEEDED' and l_trackable='Y')
or (l_ib_update_status = 'SUCCEEDED' and l_trackable ='Y')
or l_trackable ='N' or l_trackable is null ) then
--we tried to update inventory
log('main','calling update_row f l_inv_interface_status:'||l_inv_interface_status);
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => null,
p_spare_update_status => l_inv_interface_status,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
csf_debrief_lines_pkg.update_row(
p_debrief_line_id => l_debrief_line_id,
p_error_text => substr(l_msg_data,1,2000));
csf_debrief_update_pkg.relieve_reservations ( l_task_assignment_id ,
l_res_item_id,
l_qty_to_relieve,
l_res_item_uom,
l_return_status,
l_msg_data ,
l_msg_count );
fnd_file.put_line(fnd_file.log,'Calling update_assignment_status: ');
log('main','calling update_assignment_status');
csf_task_assignments_pub.update_assignment_status
( p_api_version => 1.0
, p_init_msg_list => null
, p_commit => null
, p_validation_level => fnd_api.g_valid_level_none
, p_task_assignment_id => l_task_assignment_id
, p_object_version_number => l_object_version_number
, p_assignment_status_id => l_debrief_error_status
, p_update_task => 'T'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_task_object_version_number => l_task_object_version_number
, x_task_status_id => l_task_status_id
) ;
update csf_debrief_headers
set processed_flag = nvl(l_processed_flag,'UNPROCESSED')
where debrief_header_id = l_debrief_header_id;
'CSFUPDATE',
'CSF:Update Debrief Lines',
null,
FALSE,
2.0,
p_debrief_header_id);
Select crld.source_id,crld.req_line_detail_id,crh.requirement_header_id,mr.reservation_quantity
from csp_requirement_headers crh,
csp_requirement_lines crl,
csp_req_line_details crld,
mtl_reservations mr
where crh.task_assignment_id = p_task_assignment_id
and crl.requirement_header_id = crh.requirement_header_id
and crld.requirement_line_id = crl.requirement_line_id
and crld.source_type = 'RES'
and mr.reservation_id = crld.source_id
and mr.inventory_item_id = nvl(p_inventory_item_id,mr.inventory_item_id);
CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_detail_id);
csp_requirement_headers_pkg.update_row(
p_requirement_header_id => l_requirement_header_id,
p_open_requirement => 'N');
select debrief_header_id
from csf_debrief_headers
where task_assignment_id = p_task_assignment_id;
update csf_debrief_headers
set processed_flag = 'PENDING'
where debrief_header_id = l_debrief_header_id;
'CSFUPDATE',
'CSF:Update Debrief Lines',
null,
FALSE,
1.0,
l_debrief_header_id);
select jtb.task_id,
jtb.task_number,
cdh.debrief_header_id,
cdh.debrief_number,
decode(processed_flag,'PENDING','P','E') debrief_status
from csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_tasks_b jtb
where processed_flag in ('PENDING','COMPLETED W/ERRORS')
and jta.task_assignment_id = cdh.task_assignment_id
and jtb.task_id = jta.task_id
and jtb.source_object_type_code = 'SR'
and jtb.source_object_id = p_incident_id
union all
select jtb.task_id,
jtb.task_number,
cdh.debrief_header_id,
cdh.debrief_number,
decode(processed_flag,'PENDING','P','E') debrief_status
from csd_repairs cr,
jtf_tasks_b jtb,
jtf_task_assignments jta,
csf_debrief_headers cdh
where jtb.source_object_id = cr.repair_line_id
and jtb.source_object_type_code = 'DR'
and jta.task_id = jtb.task_id
and cdh.task_assignment_id = jta.task_assignment_id
and cdh.processed_flag in ('PENDING','COMPLETED W/ERRORS')
and cr.incident_id = p_incident_id;
End Csf_Debrief_Update_pkg;