[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
PROCEDURE group_wip_update
( p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_incident_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'group_wip_update';
p_last_updated_by NUMBER;
SELECT repair_group_id
FROM csd_repair_order_groups
WHERE incident_id = p_inc_id;
SELECT x.repair_job_xref_id, x.group_id, x.object_version_number
FROM csd_repair_job_xref x
WHERE x.repair_line_id in ( select r.repair_line_id
from csd_repairs r
where r.repair_group_id = p_rep_grp_id)
AND x.wip_entity_id = x.group_id;
SELECT distinct crog.wip_entity_id
FROM csd_repair_order_groups crog,
wip_discrete_jobs wdj
WHERE crog.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type in ( 4,12,5)
AND crog.incident_id = p_inc_id;
SELECT crog.repair_group_id
FROM csd_repair_order_groups crog
WHERE crog.wip_entity_id = p_wip_ent_id;
SELECT
crj.repair_job_xref_id,
crj.wip_entity_id,
crj.repair_line_id,
csr.repair_number,
crj.quantity_completed,
crj.quantity,
csr.promise_date
FROM csd_repair_job_xref crj,
csd_repairs csr
WHERE repair_group_id = p_rep_group_id
AND csr.repair_line_id = crj.repair_line_id
AND nvl(crj.quantity_completed,0) < crj.quantity
AND crj.wip_entity_id = p_wip_ent_id;
SAVEPOINT group_wip_update;
SELECT wip_entity_id
INTO v_weid
FROM wip_entities
WHERE wip_entity_name = 'CSD'||C6.group_id;
UPDATE csd_repair_job_xref
SET wip_entity_id = v_weid
WHERE repair_job_xref_id = C6.repair_job_xref_id
AND group_id = C6.group_id
AND object_version_number = C6.object_version_number;
UPDATE csd_repair_order_groups
SET wip_entity_id = v_weid
WHERE repair_group_id = C5.repair_group_id;
SELECT wip_entity_id,
quantity_completed,
completion_subinventory,
date_completed,
organization_id,
routing_reference_id,
last_updated_by
INTO v_wip_entity_id,
v_quantity_completed,
p_completion_subinventory,
p_date_completed,
p_organization_id,
p_routing_reference_id,
p_last_updated_by
FROM wip_discrete_jobs
WHERE wip_entity_id = C1.wip_entity_id
AND status_type in ( 4,12,5);
SELECT nvl(sum(quantity_completed),0)
INTO p_old_complete
FROM csd_repair_job_xref
WHERE wip_entity_id = C1.wip_entity_id;
update csd_repair_job_xref
set quantity_completed = quantity
where repair_line_id = C3.repair_line_id;
csd_gen_utility_pvt.add('Updated qty completed in csd_repair_job_xref for :'||C1.wip_entity_id);
update csd_repairs
set ro_txn_status = 'WIP_COMPLETED'
where repair_line_id = C3.repair_line_id;
csd_gen_utility_pvt.add('Updated txn status in csd_repairs :'||C3.repair_line_id);
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 => C3.repair_line_id,
p_EVENT_CODE => 'JC',
p_EVENT_DATE => nvl(p_date_completed,sysdate),
p_QUANTITY => v_transaction_quantity,
p_PARAMN1 => p_organization_id,
p_PARAMN2 => p_routing_reference_id,
p_PARAMN3 => null,
p_PARAMN4 => C3.wip_entity_id,
p_PARAMN5 => null,
p_PARAMN6 => null,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => p_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 => p_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 => FND_GLOBAL.LOGIN_ID,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data );
UPDATE csd_repair_order_groups
SET group_txn_status = 'WIP_COMPLETED',
completed_quantity = submitted_quantity
WHERE repair_group_id = C2.repair_group_id;
csd_gen_utility_pvt.add('Successfully completed Depot Repair WIP Job Update');
ROLLBACK TO Group_Wip_update;
ROLLBACK TO Group_Wip_update;
ROLLBACK TO Group_Wip_update ;
END Group_Wip_update;
/* procedure name: Pre_process_update */
/* description : procedure that updates the depot table*/
/* once the pre-process is completed */
/*-------------------------------------------------------*/
procedure Pre_process_update
( p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_incident_id IN number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Pre_process_update';
x_update_count Number;
Select crog.repair_group_id,
crt.repair_type_ref,
crog.group_txn_status,
crog.repair_order_quantity,
crog.received_quantity,
crog.shipped_quantity
from csd_repair_order_groups crog,
csd_repair_types_vl crt
where crog.repair_type_id = crt.repair_type_id
and crog.incident_id = p_inc_id;
SAVEPOINT Pre_process_update;
Group_Rma_Update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
csd_gen_utility_pvt.ADD('Group_ship_update failed ');
IF (nvl(grp.received_quantity,0) + nvl(x_update_count,0) = nvl(grp.repair_order_quantity,0)) then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating group txn status and qty ');
Update csd_repair_order_groups
set received_quantity = nvl(repair_order_quantity,0) ,
group_txn_status = 'OM_RECEIVED'
where repair_group_id = grp.repair_group_id ;
ELSIF nvl(x_update_count,0) > 0 then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating recd qty');
Update csd_repair_order_groups
set received_quantity = nvl(received_quantity,0)+ x_update_count
where repair_group_id = grp.repair_group_id ;
Group_Rma_Update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
csd_gen_utility_pvt.ADD('Group_rma_update failed ');
IF (nvl(grp.received_quantity,0) + nvl(x_update_count,0) = nvl(grp.repair_order_quantity,0)) then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating group txn status and qty ');
Update csd_repair_order_groups
set received_quantity = nvl(repair_order_quantity,0) ,
group_txn_status = 'OM_RECEIVED'
where repair_group_id = grp.repair_group_id ;
ELSIF nvl(x_update_count,0) > 0 then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating recd qty');
Update csd_repair_order_groups
set received_quantity = nvl(received_quantity,0)+ x_update_count
where repair_group_id = grp.repair_group_id ;
Group_ship_update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.ADD('Group_ship_update failed ');
Group_ship_update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
csd_gen_utility_pvt.ADD('Group_ship_update failed ');
IF (nvl(grp.shipped_quantity,0) + nvl(x_update_count,0)) = nvl(grp.repair_order_quantity,0) then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating group status and qty ');
Update csd_repair_order_groups
set shipped_quantity = nvl(repair_order_quantity,0) ,
group_txn_status = 'OM_SHIPPED'
where repair_group_id = grp.repair_group_id ;
ELSIF nvl(x_update_count,0) > 0 then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('updating shipped qty ='||x_update_count );
Update csd_repair_order_groups
set shipped_quantity = nvl(shipped_quantity,0)+x_update_count
where repair_group_id = grp.repair_group_id ;
ROLLBACK TO Pre_process_update;
ROLLBACK TO Pre_process_update ;
ROLLBACK TO Pre_process_update ;
END Pre_process_update;
/* procedure name: Post_process_update */
/* description : procedure that updates depot */
/* after post-process is complete */
/*--------------------------------------------------*/
procedure Post_process_update
( p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_incident_id IN number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Post_process_update';
x_update_count number;
Select crog.repair_group_id,
crt.repair_type_ref,
crog.group_txn_status,
crog.repair_order_quantity,
crog.received_quantity,
crog.shipped_quantity
from csd_repair_order_groups crog,
csd_repair_types_vl crt
where crog.repair_type_id = crt.repair_type_id
and crog.incident_id = p_inc_id;
SAVEPOINT Post_process_update;
Group_Rma_Update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
csd_gen_utility_pvt.ADD('Group_ship_update failed ');
IF nvl(grp.received_quantity,0)+nvl(x_update_count,0) = nvl(grp.repair_order_quantity,0) then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating the group txn status ');
Update csd_repair_order_groups
set received_quantity = nvl(received_quantity ,0) + nvl(x_update_count,0),
group_txn_status = 'OM_RECEIVED'
where repair_group_id = grp.repair_group_id ;
ELSIF nvl(x_update_count,0) > 0 then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('Updating the recd qty ');
Update csd_repair_order_groups
set received_quantity = nvl(received_quantity ,0) + nvl(x_update_count,0)
where repair_group_id = grp.repair_group_id ;
Group_ship_update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
csd_gen_utility_pvt.ADD('Group_ship_update failed ');
IF nvl(grp.shipped_quantity,0) + nvl(x_update_count,0) = grp.repair_order_quantity then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('updating group txn status');
Update csd_repair_order_groups
set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0),
group_txn_status = 'OM_SHIPPED'
where repair_group_id = grp.repair_group_id ;
ELSIF nvl(x_update_count,0) > 0 THEN
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('updating only shipped qty');
Update csd_repair_order_groups
set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0)
where repair_group_id = grp.repair_group_id ;
Group_ship_update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.add('x_update_count ='||x_update_count );
csd_gen_utility_pvt.ADD('Group_ship_update failed ');
IF nvl(grp.shipped_quantity,0) + nvl(x_update_count,0) = grp.repair_order_quantity then
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('updating group txn status');
Update csd_repair_order_groups
set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0),
group_txn_status = 'OM_SHIPPED'
where repair_group_id = grp.repair_group_id ;
ELSIF nvl(x_update_count,0) > 0 THEN
IF (g_debug > 0 ) THEN
csd_gen_utility_pvt.add('updating only shipped qty');
Update csd_repair_order_groups
set shipped_quantity = nvl(shipped_quantity ,0) + nvl(x_update_count,0)
where repair_group_id = grp.repair_group_id ;
Group_Rma_Update
( p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_repair_group_id => grp.repair_group_id,
x_update_count => x_update_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
csd_gen_utility_pvt.ADD('Group_rma_update failed ');
ROLLBACK TO Post_process_update;
ROLLBACK TO Post_process_update ;
ROLLBACK TO Post_process_update ;
END Post_process_update;
/* procedure name: Group_Rma_Update */
/* description : procedure used to apply contract */
/* */
/*--------------------------------------------------*/
procedure Group_Rma_Update
( p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_repair_group_id IN NUMBER,
x_update_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Group_Rma_Update';
Select
cr.incident_id, -- travi
cr.repair_group_id,
ced.order_header_id,
ced.order_line_id,
ced.txn_billing_type_id,
cpt.product_transaction_id,
cpt.action_code,
ooh.order_number rma_number,
ool.line_number rma_line_number,
ool.line_type_id,
cr.repair_line_id,
cr.repair_number,
rcv.organization_id,
cr.inventory_item_id,
rcv.unit_of_measure,
rcv.transaction_date received_date,
rcv.transaction_id transaction_id,
rcv.quantity received_quantity,
rcv.subinventory,
rcv.last_updated_by who_col,
rcv.oe_order_header_id rma_header_id,
rst.serial_num serial_number
from csd_repairs cr,
csd_product_transactions cpt,
cs_estimate_details ced,
rcv_transactions rcv,
rcv_serial_transactions rst,
oe_order_headers_all ooh,
oe_order_lines_all ool,
cs_txn_billing_types ctbt,
cs_transaction_types_b ctt
where cr.repair_line_id = cpt.repair_line_id
and cpt.estimate_detail_id = ced.estimate_detail_id
and ced.txn_billing_type_id = ctbt.txn_billing_type_id
and ctbt.transaction_type_id = ctt.transaction_type_id
and ctt.depot_Repair_flag = 'Y'
and cpt.action_type in ('RMA','WALK_IN_RECEIPT')
and ced.original_source_code = 'DR'
and cr.repair_group_id = p_rep_group_id
and rcv.oe_order_line_id = ced.order_line_id
and rcv.transaction_id = rst.transaction_id(+)
and rcv.oe_order_line_id = ool.line_id
and ool.header_id = ooh.header_id
and rcv.transaction_type = 'DELIVER'
and rcv.source_document_code = 'RMA'
and rcv.transaction_id NOT IN
(SELECT paramn1
FROM csd_Repair_history crh,
csd_repairs cra
WHERE crh.repair_line_id = cra.repair_line_id
AND crh.event_code = 'RR'
AND cra.repair_group_id = p_rep_group_id );
SAVEPOINT Group_Rma_Update;
csd_gen_utility_pvt.add('Before update csd_repairs');
update csd_repairs
set quantity_rcvd = nvl(quantity_rcvd,0)+ nvl(p_result_quantity,0),
ro_txn_status = 'OM_RECEIVED'
where repair_line_id = C1.repair_line_id;
csd_gen_utility_pvt.add('After update csd_repairs');
csd_gen_utility_pvt.add('Before select ib_flag');
select
comms_nl_trackable_flag
into l_ib_flag
from mtl_system_items
where inventory_item_id = C1.inventory_item_id
and organization_id = C1.organization_id
and rownum < 2; -- travi
csd_gen_utility_pvt.add('After select ib_flag');
csd_gen_utility_pvt.add('Before select account_id, customer_id');
select account_id, customer_id
into l_account_id, l_customer_id
from csd_incidents_v
where incident_id = C1.incident_id;
csd_gen_utility_pvt.add('After select account_id, customer_id');
csd_gen_utility_pvt.add('Before select instance_id');
Select instance_id
into l_instance_id
from csi_item_instances
where serial_number = C1.serial_number
and inventory_item_id = C1.inventory_item_id
and trunc(sysdate) between trunc(nvl(active_start_date,sysdate))
and trunc(nvl(active_end_date,sysdate))
and owner_party_account_id = nvl(l_account_id, owner_party_account_id) -- sr.account_id
and owner_party_id = l_customer_id; -- sr.customer_id
csd_gen_utility_pvt.add('After select instance_id');
update csd_repairs
set serial_number = C1.serial_number ,
customer_product_id = l_instance_id
where repair_line_id = C1.repair_line_id;
update csd_repairs
set serial_number = C1.serial_number
where repair_line_id = C1.repair_line_id;
Update csd_product_transactions
set prod_txn_status = 'RECEIVED'
where product_transaction_id = C1.product_transaction_id;
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 => C1.repair_line_id,
p_EVENT_CODE => 'RR',
p_EVENT_DATE => C1.received_date,
p_QUANTITY => C1.received_quantity,
p_PARAMN1 => C1.transaction_id,
p_PARAMN2 => C1.rma_line_number,
p_PARAMN3 => C1.line_type_id,
p_PARAMN4 => C1.txn_billing_type_id,
p_PARAMN5 => C1.who_col,
p_PARAMN6 => C1.rma_header_id,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => C1.subinventory,
p_PARAMC2 => C1.rma_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 => FND_GLOBAL.LOGIN_ID,
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 ');
x_update_count := v_total_records ;
ROLLBACK TO Group_Rma_Update;
ROLLBACK TO Group_Rma_Update ;
ROLLBACK TO Group_Rma_Update ;
END Group_Rma_Update;
/* procedure name: Group_ship_update */
/* description : procedure used to apply contract */
/* */
/*--------------------------------------------------*/
procedure Group_ship_update
( p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_repair_group_id IN number,
x_update_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Group_ship_update';
Select
dd.serial_number sl_number,
cra.quantity qty,
cpt.product_transaction_id,
cpt.action_code,
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
csd_Repairs cra,
csd_product_transactions cpt,
cs_estimate_details ced,
wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd ,
oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_units_of_measure mtlu
Where cra.repair_group_id = p_rep_group_id
and cra.repair_line_id = cpt.repair_line_id
and cpt.estimate_detail_id = ced.estimate_detail_id
and ced.original_source_code = 'DR'
and dd.delivery_detail_id = da.delivery_detail_id
and da.delivery_id = dl.delivery_id(+)
and ced.order_line_id = oel.line_id
and oel.header_id = oeh.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'
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;
SAVEPOINT Group_ship_update;
csd_gen_utility_pvt.add('at the begin Group_ship_update');
update csd_repairs
set quantity_shipped = nvl(quantity_shipped,0)+nvl(p_result_ship_quantity,0),
ro_txn_status = 'RO_SHIPPED'
where repair_line_id = I.repair_line_id;
update csd_product_transactions
set prod_txn_status= 'SHIPPED'
where product_transaction_id = I.product_transaction_id;
csd_gen_utility_pvt.add('Updated csd_repairs table');
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 => 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 => FND_GLOBAL.LOGIN_ID,
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 repair Shipping Update');
x_update_count := v_total_records;
ROLLBACK TO Group_ship_update;
ROLLBACK TO Group_ship_update ;
ROLLBACK TO Group_ship_update ;
End Group_ship_update;