[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE NUMBER := 1;
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 cdl.debrief_line_id,
cdl.service_date,
--cdl.txn_billing_type_id,
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
from csf_debrief_lines cdl
where cdl.debrief_header_id = p_debrief_header_id
and nvl(cdl.quantity,-1) <> 0 ;
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
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 -- changed for the bug 3648213
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, -- replaced -1 with jtb.cust_account_id for bug 3343984
cdh.debrief_number ,
jta.task_assignment_id,
jtsb.cancelled_flag,
jtsb.rejected_flag,
jtsb.completed_flag,
jtsb.closed_flag
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 ; -- changed for the bug 3648213
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
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, -- replaced -1 with jtb.cust_account_id for bug 3343984
cdh.debrief_number ,
jta.task_assignment_id,
jtsb.cancelled_flag,
jtsb.rejected_flag,
jtsb.completed_flag,
jtsb.closed_flag
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 cdtv.debrief_header_id,
cdtv.source_type_code,
cdtv.incident_id,
cdtv.repair_line_id,
cdtv.customer_id,
cdtv.customer_account_id,
cdtv.debrief_number,
cdtv.task_assignment_id,
jtsv.cancelled_flag,
jtsv.rejected_flag,
jtsv.completed_flag,
jtsv.closed_flag
from csf_debrief_tasks_v cdtv,
jtf_task_assignments jta,
jtf_task_statuses_vl jtsv
where cdtv.debrief_header_id = nvl(p_debrief_header_id, cdtv.debrief_header_id)
and cdtv.task_assignment_id = jta.task_assignment_id
and jta.assignment_status_id = jtsv.task_status_id
and cdtv.incident_id = nvl(p_incident_id, cdtv.incident_id); */
select cttv.line_order_category_code,
cttv.transaction_type_id ,
ctbt.billing_type,
ctst.sub_type_id ,
ctst.transaction_type_id transaction_type_id_csi
from cs_transaction_types_vl cttv,
cs_txn_billing_types ctbt,
csi_txn_sub_types ctst,
csi_txn_types ctt
where cttv.transaction_type_id = ctbt.transaction_type_id
and ctbt.txn_billing_type_id = p_txn_billing_type_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 create_cost_flag,
create_charge_flag
from cs_transaction_types
where transaction_type_id = p_transaction_type_id;
select cttv.line_order_category_code,
ctbt.txn_billing_type_id ,
cbtc.billing_category billing_type,
ctst.sub_type_id ,
ctst.transaction_type_id transaction_type_id_csi
from cs_transaction_types_vl cttv,
cs_txn_billing_types ctbt,
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 cttv.transaction_type_id = ctbt.transaction_type_id
and ctbt.billing_type = msibk.material_billable_flag
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;
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;
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);
if l_charges_interface_status = 'SUCCEEDED' Then --it means we tried to update Charges and it was succ
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);
if nvl(l_ib_update_status,' ') <> 'SUCCEEDED' and l_trackable ='Y'
and (l_charges_interface_status = 'SUCCEEDED' or l_charge_upload_status ='SUCCEEDED' ) --continue only if charges was suc.
Then
------------------------------------------------------------------------------------------------------
-- UPDATE INSTALL BASE --
-------------------------------------------------------------------------------------------------------
--dbms_output.put_line('in IB');
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);
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') --we tried to update IB for this line
Then
if l_line_category_code <> 'RETURN' then
l_instance_id := l_new_instance_id;
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);
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 )
--ib_interface status must be = with Suceesed only when I tried to update Ib for this record
Then
--dbms_output.put_line('in update inv');
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 );
end if; --spares update status
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);
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
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));
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
from csp_requirement_headers crh,
csp_requirement_lines crl,
csp_req_line_details crld
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';
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;