[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.txn_billing_type_id,
b.repair_number,
b.repair_line_id,
b.quantity
INTO x_txn_billing_type_id,
x_repair_number ,
x_repair_line_id,
x_quantity
FROM cs_estimate_details a,
csd_repairs b
WHERE ((a.original_source_id = b.repair_line_id
AND a.original_source_code = 'DR') OR
(a.source_id = b.repair_line_id
AND a.source_code = 'DR'))
AND a.order_header_id = l_header_id
AND a.order_line_id = l_line_id;
SELECT
split_from_line_id
INTO l_line_id
FROM oe_order_lines_all
WHERE line_id = l_line_id
AND header_id = l_header_id;
select uom_code
into p_from_uom_code
from mtl_units_of_measure
where unit_of_measure = p_from_uom;
select primary_uom_code
into v_primary_uom_code
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = p_item_id;
/**** Added Organization name in Select columns to fix bug 2760279
**** Added hr_organization_units in from clause
**** Added one joing with hr_organization_units ******************/
Cursor RMA_RECEIPT_LINES( p_repair_line_id number ) is
SELECT /*+ CHOOSE */ oeh.order_number rma_number,
oeh.header_id rma_header_id,
oel.line_id ,
oel.split_from_line_id,
oel.line_number rma_line_number,
oel.inventory_item_id,
rcvt.organization_id,
rcvt.unit_of_measure,
oel.line_type_id,
rcvt.quantity received_quantity,
rcvt.subinventory received_subinventory,
rcvt.transaction_date received_date,
rcvt.transaction_id,
rcvt.last_updated_by who_col,
rcvt.subinventory,
hou.name organization_name
FROM rcv_transactions rcvt,
oe_order_headers_all oeh,
oe_order_lines_all oel,
hr_organization_units hou
WHERE oel.header_id = oeh.header_id
AND rcvt.oe_order_line_id = oel.line_id
AND rcvt.transaction_type = 'RECEIVE'
AND rcvt.source_document_code = 'RMA'
And rcvt.organization_id = hou.organization_id
AND rcvt.transaction_id NOT IN
(SELECT paramn1
FROM csd_Repair_history crh,
csd_repairs cra
WHERE crh.repair_line_id = cra.repair_line_id
AND event_code='RR'
AND cra.repair_line_id = nvl(p_repair_line_id,cra.repair_line_id)) -- travi 020903 change
AND EXISTS (SELECT ced.order_header_id
FROM csd_repairs cra,
cs_estimate_details ced
WHERE ((cra.repair_line_id = ced.original_source_id
AND ced.original_source_code = 'DR') OR
(cra.repair_line_id = ced.source_id
AND ced.source_code = 'DR'))
AND oeh.header_id = ced.order_header_id
AND cra.repair_line_id = nvl(p_repair_line_id,cra.repair_line_id)); -- travi 020903 change
select cpt.serial_number pt_sl_number
, dra.serial_number dr_sl_number
, cpt.inventory_item_id pt_item_id
, dra.inventory_item_id dr_item_id
from csd_product_txns_v cpt
, csd_repairs dra
where action_type = 'RMA'
and dra.repair_line_id = p_rep_line_id
and cpt.repair_line_id = dra.repair_line_id
and nvl(cpt.serial_number_control_code,1) > 1;
select transaction_id
from rcv_transactions
where parent_transaction_id = p_txn_id;
select serial_num
from rcv_serial_transactions
where transaction_id = p_txn_id;
Select product_transaction_id
from csd_product_txns_v
where repair_line_id = p_rep_line_id
and action_type in ( 'RMA','WALK_IN_RECEIPT')
and repair_quantity = quantity_rcvd;
csd_gen_utility_pvt.add('At the Begin of Depot RMA receipt update');
fnd_file.put_line(fnd_file.log, 'Selecting RMA Number : '||I.rma_number );
Select transaction_type_id
into v_transaction_type_id
from cs_txn_billing_types
where txn_billing_type_id = l_txn_billing_type_id;
Select depot_Repair_flag
into v_depot_repair_flag
from cs_transaction_types_b
where transaction_type_id = v_transaction_type_id;
p_PROGRAM_UPDATE_DATE => null,
p_CREATED_BY => -1,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => -1,
p_LAST_UPDATE_DATE => sysdate,
p_REPAIR_LINE_ID => l_repair_line_id,
p_EVENT_CODE => 'RSC', -- RMA serial number changed
p_EVENT_DATE => I.received_date,
p_QUANTITY => I.received_quantity,
p_PARAMN1 => i.transaction_id,
p_PARAMN2 => i.rma_line_number,
p_PARAMN3 => i.line_type_id,
p_PARAMN4 => l_txn_billing_type_id,
p_PARAMN5 => i.who_col,
p_PARAMN6 => i.rma_header_id,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => i.subinventory,
p_PARAMC2 => i.rma_number,
p_PARAMC3 => l_pt_serial_num, -- prd txn ser num
p_PARAMC4 => l_st_serial_num, -- rcv ser txn ser num
p_PARAMC5 => null,
p_PARAMC6 => null,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
p_PARAMC10 => null,
p_PARAMD1 => null,
p_PARAMD2 => null,
p_PARAMD3 => null,
p_PARAMD4 => null,
p_PARAMD5 => null,
p_PARAMD6 => null,
p_PARAMD7 => null,
p_PARAMD8 => null,
p_PARAMD9 => null,
p_PARAMD10 => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
p_LAST_UPDATE_LOGIN => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
csd_gen_utility_pvt.add('Successfully updated the history');
fnd_file.put_line(fnd_file.log, 'Successfully updated the history');
csd_gen_utility_pvt.add('Inserted into Repairs History Table for Serial number Change');
update csd_repairs
set quantity_rcvd = nvl(quantity_rcvd,0)+nvl(p_result_quantity,0)
where repair_line_id = l_repair_line_id;
Update csd_product_transactions
set prod_txn_status = l_prod_txn_stat
where product_transaction_id = P.product_transaction_id;
p_PROGRAM_UPDATE_DATE => null,
p_CREATED_BY => -1,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => -1,
p_LAST_UPDATE_DATE => sysdate,
p_REPAIR_LINE_ID => l_repair_line_id,
p_EVENT_CODE => 'RR',
p_EVENT_DATE => I.received_date,
p_QUANTITY => I.received_quantity,
p_PARAMN1 => i.transaction_id,
p_PARAMN2 => i.rma_line_number,
p_PARAMN3 => i.line_type_id,
p_PARAMN4 => l_txn_billing_type_id,
p_PARAMN5 => i.who_col,
p_PARAMN6 => i.rma_header_id,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => i.subinventory,
p_PARAMC2 => i.rma_number,
p_PARAMC3 => i.Organization_Name, -- Bug No 2760279
p_PARAMC4 => null,
p_PARAMC5 => null,
p_PARAMC6 => null,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
p_PARAMC10 => null,
p_PARAMD1 => null,
p_PARAMD2 => null,
p_PARAMD3 => null,
p_PARAMD4 => null,
p_PARAMD5 => null,
p_PARAMD6 => null,
p_PARAMD7 => null,
p_PARAMD8 => null,
p_PARAMD9 => null,
p_PARAMD10 => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
p_LAST_UPDATE_LOGIN => null,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
csd_gen_utility_pvt.add('Successfully completed Depot RMA receipt update ');
fnd_file.put_line(fnd_file.log, 'Successfully completed Depot RMA receipt update');
csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_records));
x_LAST_UPDATED_BY OUT NOCOPY number)
is
v_quantity_completed number;
Select WIP_ENTITY_ID,
QUANTITY_COMPLETED,
COMPLETION_SUBINVENTORY,
DATE_COMPLETED,
ORGANIZATION_ID,
routing_reference_id,
LAST_UPDATED_BY
into v_wip_entity_id,
x_wip_completed_qty,
x_COMPLETION_SUBINVENTORY,
x_DATE_COMPLETED,
x_ORGANIZATION_ID,
x_routing_reference_id,
x_LAST_UPDATED_BY
from WIP_DISCRETE_JOBS
where WIP_ENTITY_ID=p_WIP_ENTITY_ID;
procedure depot_wip_update
(errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_repair_line_id in number)
is
v_total_rec number;
x_LAST_UPDATED_BY number;
SELECT CRJ.REPAIR_JOB_XREF_ID,
CRJ.WIP_ENTITY_ID,
CRJ.GROUP_ID,
CRJ.REPAIR_LINE_ID,
csr.repair_number,
crj.quantity_completed,
crj.quantity,
csr.promise_date,
crj.organization_id
from CSD_REPAIR_JOB_XREF CRJ
,CSD_REPAIRS csr
where
csr.repair_line_id = crj.repair_line_id
and
nvl(crj.quantity_completed,0) < crj.quantity
and csr.repair_line_id = nvl(p_repair_line_id, csr.repair_line_id) -- travi 121801 change
order by crj.wip_entity_id, csr.promise_date;
csd_gen_utility_pvt.add('At the begin of Depot Repair WIP Job update');
Select wip_entity_id
into v_wei
from wip_entities
where wip_entity_name = v_wen
and wip_entities.organization_id = K.organization_id;---- 0430 bug number- sangita to fix duplicate wip name problem.
-- so should not update when v_wei is null bug# 2770713 saupadhy
Begin
update csd_repair_job_xref
set wip_entity_id = v_wei
where repair_line_id = K.repair_line_id
and repair_job_xref_id = K.repair_job_xref_id
and v_wei is not null;
x_date_completed,x_organization_id,x_routing_reference_id,x_last_updated_by);
Select nvl(sum(quantity_completed),0) into SumOfROCompQty from csd_repair_job_xref where wip_entity_id = i.wip_entity_id;
Select wip_entity_name
into v_wip_entity_name
from wip_entities
where wip_entity_id = i.wip_entity_id;
Select nvl(sum(quantity_completed),0) into SumOfROCompQty from csd_repair_job_xref where wip_entity_id = i.wip_entity_id;
update csd_repair_job_xref
set quantity_completed =v_completed_qty
where repair_line_id = i.repair_line_id
and repair_job_xref_id = i.repair_job_xref_id;
fnd_message.set_name('CSD','CSD_DRC_WIP_JOB_UPDATE');
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_wip_update before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for JC event');
p_PROGRAM_UPDATE_DATE => null,
p_CREATED_BY => -1,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => -1,
p_LAST_UPDATE_DATE => sysdate,
p_REPAIR_LINE_ID => I.repair_line_id,
p_EVENT_CODE => 'JC',
p_EVENT_DATE => nvl(x_date_completed,sysdate),
p_QUANTITY => v_transaction_quantity,
p_PARAMN1 => x_organization_id,
p_PARAMN2 => x_routing_reference_id,
p_PARAMN3 => null,
p_PARAMN4 => i.wip_entity_id,
p_PARAMN5 => null,
p_PARAMN6 => null,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => x_completion_subinventory,
p_PARAMC2 => v_wip_entity_name,
p_PARAMC3 => null,
p_PARAMC4 => null,
p_PARAMC5 => null,
p_PARAMC6 => null,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
p_PARAMC10 => null,
p_PARAMD1 => x_date_completed,
p_PARAMD2 => null,
p_PARAMD3 => null,
p_PARAMD4 => null,
p_PARAMD5 => null,
p_PARAMD6 => null,
p_PARAMD7 => null,
p_PARAMD8 => null,
p_PARAMD9 => null,
p_PARAMD10 => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
p_LAST_UPDATE_LOGIN => null,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_wip_update after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
csd_gen_utility_pvt.add('Successfully completed Depot Repair WIP Job Update');
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_WIP_UPDATE : Call from Depot Repair Form Tools Menu');
csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_rec));
procedure depot_shipment_update
(errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_repair_line_id in number)
is
v_total_records number;
select /*+ index(cra CSD_REPAIRS_U1) */
dd.serial_number sl_number, -- travi 012502
cra.quantity qty, -- travi 012502
dd.lot_number lot_number, --vijay 02/03/2003
dd.revision revision, --vijay 02/03/2003
dd.subinventory subinv, --vijay 02/03/2003
oeh.order_number order_number,
oeh.header_id sales_order_header,
oel.line_number order_line_number,
oel.line_type_id,
cra.repair_number,
cra.repair_line_id,
ced.txn_billing_type_id,
dd.requested_quantity,
dd.shipped_quantity,
dl.initial_pickup_date date_shipped,
dd.delivery_detail_id,
dd.requested_quantity_uom shipped_uom_code,
mtlu.unit_of_measure shipped_uom,
dd.inventory_item_id ,
dd.organization_id
from
wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd ,
oe_order_headers_all oeh,
oe_order_lines_all oel,
csd_Repairs cra,
cs_estimate_Details ced,
mtl_units_of_measure mtlu
Where ((cra.repair_line_id = ced.original_source_id
and ced.original_source_code = 'DR') OR
(cra.repair_line_id = ced.source_id
and ced.source_code = 'DR'))
and dd.delivery_detail_id = da.delivery_detail_id
and da.delivery_id = dl.delivery_id(+)
and ced.order_header_id = oeh.header_id
and ced.order_line_id = oel.line_id
and ced.order_header_id = oel.header_id
and dd.source_header_id = ced.order_header_id
and dd.source_line_id = ced.order_line_id
and dd.released_status = 'C' -- travi 022002
and dd.delivery_detail_id not in
(select paramn1
from csd_Repair_history
where repair_line_id = cra.repair_line_id
and event_code='PS')
and mtlu.uom_code = dd.requested_quantity_uom
and cra.repair_line_id = nvl(p_repair_line_id, cra.repair_line_id);
select nvl(cpt.serial_number, dra.serial_number) serial_number
from csd_product_txns_v cpt
, csd_repairs dra
where action_type = 'SHIP'
and dra.repair_line_id = p_rep_line_id
and cpt.repair_line_id = dra.repair_line_id
and nvl(cpt.serial_number_control_code,1) > 1;
csd_gen_utility_pvt.add('At the begin of Depot repair Shipping Update');
Select transaction_type_id
into v_transaction_type_id
from cs_txn_billing_types
where txn_billing_type_id = i.txn_billing_type_id;
Select depot_Repair_flag
into v_depot_repair_flag
from cs_transaction_types_b
where transaction_type_id = v_transaction_type_id;
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for SSC event');
p_PROGRAM_UPDATE_DATE => null,
p_CREATED_BY => -1,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => -1,
p_LAST_UPDATE_DATE => sysdate,
p_REPAIR_LINE_ID => I.repair_line_id,
p_EVENT_CODE => 'SSC', -- Ship serial number changed
p_EVENT_DATE => I.date_shipped,
p_QUANTITY => p_result_ship_quantity,
p_PARAMN1 => i.delivery_detail_id,
p_PARAMN2 => i.order_line_number,
p_PARAMN3 => i.line_type_id,
p_PARAMN4 => i.txn_billing_type_id,
p_PARAMN5 => null,
p_PARAMN6 => null,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => null,
p_PARAMC2 => i.order_number,
p_PARAMC3 => l_pt_serial_num, -- prd txn ser num
p_PARAMC4 => i.sl_number, -- WDD ship ser num
p_PARAMC5 => null,
p_PARAMC6 => null,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
p_PARAMC10 => null,
p_PARAMD1 => null,
p_PARAMD2 => null,
p_PARAMD3 => null,
p_PARAMD4 => null,
p_PARAMD5 => null,
p_PARAMD6 => null,
p_PARAMD7 => null,
p_PARAMD8 => null,
p_PARAMD9 => null,
p_PARAMD10 => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
p_LAST_UPDATE_LOGIN => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
csd_gen_utility_pvt.add('Successfully updated the history');
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_SHIPMENT_UPDATE - Repair Line ID : '||to_char(i.repair_line_id));
csd_gen_utility_pvt.add('Inserted into Repairs History Table for Serial number Change');
update csd_repairs
set quantity_shipped = nvl(quantity_shipped,0)+nvl(p_result_ship_quantity,0)
where repair_line_id = I.repair_line_id;
update csd_product_transactions
set sub_inventory = i.subinv,
lot_number = i.lot_number
where
repair_line_id = i.repair_line_id;
csd_gen_utility_pvt.add('Updated csd_repairs table');
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for PS event');
p_PROGRAM_UPDATE_DATE => null,
p_CREATED_BY => -1,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => -1,
p_LAST_UPDATE_DATE => sysdate,
p_REPAIR_LINE_ID => I.repair_line_id,
p_EVENT_CODE => 'PS',
p_EVENT_DATE => I.date_shipped,
p_QUANTITY => p_result_ship_quantity,
p_PARAMN1 => i.delivery_detail_id,
p_PARAMN2 => i.order_line_number,
p_PARAMN3 => i.line_type_id,
p_PARAMN4 => i.txn_billing_type_id,
p_PARAMN5 => null,
p_PARAMN6 => null,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => null,
p_PARAMC2 => i.order_number,
p_PARAMC3 => null,
p_PARAMC4 => null,
p_PARAMC5 => null,
p_PARAMC6 => null,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
p_PARAMC10 => null,
p_PARAMD1 => null,
p_PARAMD2 => null,
p_PARAMD3 => null,
p_PARAMD4 => null,
p_PARAMD5 => null,
p_PARAMD6 => null,
p_PARAMD7 => null,
p_PARAMD8 => null,
p_PARAMD9 => null,
p_PARAMD10 => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
p_LAST_UPDATE_LOGIN => null,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_shipment_update after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write l_return_status'||l_return_status);
csd_gen_utility_pvt.add('Successfully completed Depot repair Shipping Update');
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_SHIPMENT_UPDATE : Call from Depot Repair Form Tools Menu');
csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_records));
PROCEDURE depot_update_task_hist
(
errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY number,
p_repair_line_id in number
)
is
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_AND_WRITE';
CURSOR c_updated_tasks( p_repair_line_id number ) is
select tsk.task_id
,rep.repair_line_id
--,max(hist.repair_history_id) repair_history_id
from csd_repair_tasks_v tsk
,csd_repair_history hist
,csd_repairs rep
where rep.repair_line_id = tsk.source_object_id
and tsk.source_object_id = hist.repair_line_id
and tsk.task_id = hist.paramn1
and ( tsk.task_status_id <> hist.paramn5 or tsk.owner_id <> hist.paramn3)
and rep.repair_line_id = nvl(p_repair_line_id, rep.repair_line_id) -- travi 181201 change
group by tsk.task_id, rep.repair_line_id;
Select tsk.task_id, -- hist.paramn1
tsk.last_updated_by, -- hist.paramn2
tsk.owner_id, -- hist.paramn3
tsk.assigned_by_id, -- hist.paramn4
tsk.task_status_id, -- hist.paramn5
tsk.task_number, -- hist.paramc1
tsk.owner_type, -- hist.paramc2
tsk.owner, -- hist.paramc3
null assignee_type, -- hist.paramc4
null assignee_name, -- hist.paramc5
tsk.task_status, -- hist.paramc6
tsk.planned_start_date, -- hist.paramd1
tsk.actual_start_date, -- hist.paramd2
tsk.actual_end_date, -- hist.paramd3
tsk.last_update_date, -- hist.paramd4
hist.paramc3, -- tsk.owner
hist.paramc6 -- tsk.task_status
from CSD_REPAIR_TASKS_V tsk
,csd_repair_history hist
where tsk.source_object_type_code = 'DR'
and tsk.task_id = l_task_id
and tsk.source_object_id = l_repair_line_id
and hist.repair_history_id = l_rep_hist_id
and hist.paramn1 = tsk.task_id
and hist.repair_line_id = tsk.source_object_id
and ( tsk.task_status_id <> hist.paramn5 or tsk.owner_id <> hist.paramn3);
FOR R in c_updated_tasks( p_repair_line_id )
loop
l_event_code := '';
l_paramn2 := ''; -- last updated by
l_paramd4 := ''; -- last updated date
select max(hist2.repair_history_id)
into l_rep_hist_id
from CSD_REPAIR_HISTORY hist2
where hist2.repair_line_id = R.repair_line_id
and hist2.paramn1 = R.task_id;
l_paramn2, -- last updated by
l_paramn3, -- owner id
l_paramn4, -- assigned by id
l_paramn5, -- status id
l_paramc1, -- task number
l_paramc2, -- owner type
l_paramc3, -- owner name
l_paramc4, -- null assignee type
l_paramc5, -- null assignee name
l_paramc6, -- status
l_paramd1, -- planned start date
l_paramd2, -- actual start date
l_paramd3, -- actual end date
l_paramd4, -- last updated date
l_owner, -- tsk.owner
l_task_status; -- -- tsk.task_status
SAVEPOINT Update_rep_task_hist;
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_update_task_hist before CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write call for TOC or TSC event');
p_PROGRAM_UPDATE_DATE => null,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_repair_line_id => l_repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => sysdate,
p_QUANTITY => null,
p_PARAMN1 => l_paramn1,
p_PARAMN2 => l_paramn2,
p_PARAMN3 => l_paramn3,
p_PARAMN4 => l_paramn4,
p_PARAMN5 => l_paramn5,
p_PARAMN6 => null,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => FND_GLOBAL.USER_ID,
p_PARAMC1 => l_paramc1,
p_PARAMC2 => l_paramc2,
p_PARAMC3 => l_paramc3,
p_PARAMC4 => l_paramc4,
p_PARAMC5 => l_paramc5,
p_PARAMC6 => l_paramc6,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
p_PARAMC10 => null,
p_PARAMD1 => l_paramd1,
p_PARAMD2 => l_paramd1,
p_PARAMD3 => l_paramd1,
p_PARAMD4 => l_paramd1,
p_PARAMD5 => null,
p_PARAMD6 => null,
p_PARAMD7 => null,
p_PARAMD8 => null,
p_PARAMD9 => null,
p_PARAMD10 => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.depot_update_task_hist after CSD_TO_FORM_REPAIR_HISTORY.Validate_And_Write x_return_status'||x_return_status);
csd_gen_utility_pvt.add('Completed depot_update_task_hist with Success..');
csd_gen_utility_pvt.add('Inserted into CSD_REPAIR_HISTORY table '||to_char(v_total_records)||' Records');
csd_gen_utility_pvt.add('CSD_DEPOT_REPAIR_CNTR.DEPOT_UPDATE_TASK_HIST : Call from Depot Repair Form Tools Menu');
csd_gen_utility_pvt.add('Total Records inserted into Repairs History Table ='||to_char(v_total_records));
ROLLBACK TO Update_rep_task_hist;
ROLLBACK TO Update_rep_task_hist;
ROLLBACK TO Update_rep_task_hist;
END depot_update_task_hist;