[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Called from : Called from Update API */
/* */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_message Required Debug message that needs to be logged */
/* p_mod_name Required Module name */
/* p_severity_level Required Severity level */
/* Output Parameters: */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* Out parameters */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Procedure DEBUG
(p_message in varchar2,
p_mod_name in varchar2,
p_severity_level in number
) IS
-- Variables used in FND Log
l_stat_level number := FND_LOG.LEVEL_STATEMENT;
/* Called from : Called from Update API */
/* */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* Output Parameters: */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* Out parameters */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Function INIT_ACTIVITY_REC RETURN csd_update_programs_pvt.activity_rec_type IS
l_activity_rec activity_rec_type;
l_activity_rec.PROGRAM_UPDATE_DATE := NULL;
/* Called from : Called from Update API (SO_RCV_UPDATE,SO_SHIP_UPDATE) */
/* */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_to_uom VARCHAR2 Required RO Unit of measure */
/* p_item_id NUMBER Required Inventory Item Id */
/* p_from_uom VARCHAR2 Conditionaly Required Needed for receiving lines */
/* p_from_uom_code VARCHAR2 Conditionaly Required Needed for shipping lines */
/* p_from_quantity NUMBER Required Transaction quantity */
/* Out parameters */
/* x_result_quantity NUMBER converted qty in Repair Order UOM */
/* */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Procedure CONVERT_TO_RO_UOM
(x_return_status OUT NOCOPY VARCHAR2,
p_to_uom_code IN varchar2,
p_item_id IN NUMBER,
p_from_uom IN varchar2,
p_from_uom_code in varchar2,
p_from_quantity IN number,
x_result_quantity OUT NOCOPY number
) IS
-- Standard variables
l_api_name CONSTANT VARCHAR2(30) := 'CONVERT_TO_RO_UOM';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.convert_to_ro_uom';
select uom_code
into l_from_uom_code
from mtl_units_of_measure
where unit_of_measure = p_from_uom;
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.log_activity';
p_PROGRAM_UPDATE_DATE => p_activity_rec.program_update_date,
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 => p_activity_rec.repair_line_id,
p_EVENT_CODE => p_activity_rec.event_code,
p_EVENT_DATE => p_activity_rec.event_date,
p_QUANTITY => p_activity_rec.quantity,
p_PARAMN1 => p_activity_rec.paramn1,
p_PARAMN2 => p_activity_rec.paramn2,
p_PARAMN3 => p_activity_rec.paramn3,
p_PARAMN4 => p_activity_rec.paramn4,
p_PARAMN5 => p_activity_rec.paramn5,
p_PARAMN6 => p_activity_rec.paramn6,
p_PARAMN7 => p_activity_rec.paramn7,
p_PARAMN8 => p_activity_rec.paramn8,
p_PARAMN9 => p_activity_rec.paramn9,
p_PARAMN10 => p_activity_rec.paramn10,
p_PARAMC1 => p_activity_rec.paramc1,
p_PARAMC2 => p_activity_rec.paramc2,
p_PARAMC3 => p_activity_rec.paramc3,
p_PARAMC4 => p_activity_rec.paramc4,
p_PARAMC5 => p_activity_rec.paramc5,
p_PARAMC6 => p_activity_rec.paramc6,
p_PARAMC7 => p_activity_rec.paramc7,
p_PARAMC8 => p_activity_rec.paramc8,
p_PARAMC9 => p_activity_rec.paramc9,
p_PARAMC10 => p_activity_rec.paramc10,
p_PARAMD1 => p_activity_rec.paramd1,
p_PARAMD2 => p_activity_rec.paramd2,
p_PARAMD3 => p_activity_rec.paramd3,
p_PARAMD4 => p_activity_rec.paramd4,
p_PARAMD5 => p_activity_rec.paramd5,
p_PARAMD6 => p_activity_rec.paramd6,
p_PARAMD7 => p_activity_rec.paramd7,
p_PARAMD8 => p_activity_rec.paramd8,
p_PARAMD9 => p_activity_rec.paramd9,
p_PARAMD10 => p_activity_rec.paramd10,
p_ATTRIBUTE_CATEGORY => p_activity_rec.attribute_category,
p_ATTRIBUTE1 => p_activity_rec.attribute1,
p_ATTRIBUTE2 => p_activity_rec.attribute2,
p_ATTRIBUTE3 => p_activity_rec.attribute3,
p_ATTRIBUTE4 => p_activity_rec.attribute4,
p_ATTRIBUTE5 => p_activity_rec.attribute5,
p_ATTRIBUTE6 => p_activity_rec.attribute6,
p_ATTRIBUTE7 => p_activity_rec.attribute7,
p_ATTRIBUTE8 => p_activity_rec.attribute8,
p_ATTRIBUTE9 => p_activity_rec.attribute9,
p_ATTRIBUTE10 => p_activity_rec.attribute10,
p_ATTRIBUTE11 => p_activity_rec.attribute11,
p_ATTRIBUTE12 => p_activity_rec.attribute12,
p_ATTRIBUTE13 => p_activity_rec.attribute13,
p_ATTRIBUTE14 => p_activity_rec.attribute14,
p_ATTRIBUTE15 => p_activity_rec.attribute15,
p_LAST_UPDATE_LOGIN => null,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
/* Procedure name: JOB_COMPLETION_UPDATE */
/* Description : Procedure called from wip_update API to update the completed qty */
/* It also logs activity for the job completion */
/* Called from : Called from WIP_Update API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER Optional Repair Order Line Id */
/* Output Parm : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Procedure JOB_COMPLETION_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER
) IS
-- Standard Variables
l_api_name CONSTANT VARCHAR2(30) := 'WIP_UPDATE';
l_update_qty NUMBER;
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.job_completion_update';
SELECT
crj.repair_job_xref_id,
crj.wip_entity_id,
crj.repair_line_id,
crj.quantity_completed allocated_comp_qty,
crj.quantity allocated_job_qty,
crj.organization_id,
cra.repair_number,
cra.promise_date,
cra.serial_number ro_serial_num,
cra.inventory_item_id ro_item_id,
wdj.quantity_completed job_completed_qty,
wdj.start_quantity job_qty,
wdj.date_completed,
wdj.primary_item_id job_item_id,
we.wip_entity_name,
mtl.serial_number_control_code
from CSD_REPAIR_JOB_XREF crj,
CSD_REPAIRS cra,
WIP_DISCRETE_JOBS wdj,
WIP_ENTITIES we,
MTL_SYSTEM_ITEMS mtl
where wdj.wip_entity_id = crj.wip_entity_id
and we.wip_entity_id = wdj.wip_entity_id
and crj.repair_line_id = cra.repair_line_id
and crj.organization_id = mtl.organization_id
and crj.inventory_item_id = mtl.inventory_item_id
and (crj.quantity - nvl(crj.quantity_completed,0)) > 0
and cra.repair_line_id = p_rep_line_id
and nvl(wdj.quantity_completed,0) > 0
order by crj.wip_entity_id, cra.promise_date;
SELECT
crj.repair_job_xref_id,
crj.wip_entity_id,
crj.repair_line_id,
crj.quantity_completed allocated_comp_qty,
crj.quantity allocated_job_qty,
crj.organization_id,
cra.repair_number,
cra.promise_date,
cra.serial_number ro_serial_num,
cra.inventory_item_id ro_item_id,
wdj.quantity_completed job_completed_qty,
wdj.start_quantity job_qty,
wdj.date_completed,
wdj.primary_item_id job_item_id,
we.wip_entity_name,
mtl.serial_number_control_code
from CSD_REPAIR_JOB_XREF crj,
CSD_REPAIRS cra,
WIP_DISCRETE_JOBS wdj,
WIP_ENTITIES we,
MTL_SYSTEM_ITEMS mtl
where wdj.wip_entity_id = crj.wip_entity_id
and we.wip_entity_id = wdj.wip_entity_id
and crj.repair_line_id = cra.repair_line_id
and crj.organization_id = mtl.organization_id
and crj.inventory_item_id = mtl.inventory_item_id
and (crj.quantity - nvl(crj.quantity_completed,0)) > 0
and nvl(wdj.quantity_completed,0) > 0
order by crj.wip_entity_id, cra.promise_date;
select subinventory_code,
transaction_quantity,
transaction_id,
transaction_date
from mtl_material_transactions mtl
where mtl.transaction_source_id = p_entity_id
and mtl.transaction_source_type_id = 5 -- Job or Schedule
and mtl.transaction_action_id = 31;-- Wip Assembly Completion
SAVEPOINT JOB_COMPLETION_UPDATE;
Debug('At the Beginning of JOB_COMPLETION_UPDATE',l_mod_name,1);
select nvl(sum(quantity_completed),0), count(repair_line_id) ---Added by vijay
into l_completed_qty, l_ro_count ----------------------------to put ro count in JC event
from csd_repair_job_xref
where wip_entity_id = JOB.wip_entity_id;
Select mt.transaction_id,
mt.transaction_date,
mt.subinventory_code
into l_mtl_trx_id,
l_mtl_trx_date,
l_mtl_subinv
from mtl_material_transactions mt,
mtl_unit_transactions mut
where mt.transaction_id = mut.transaction_id
and mt.transaction_source_id = JOB.wip_entity_id
and mt.transaction_source_type_id = 5 -- Job or Schedule
and mt.transaction_action_id = 31 -- Wip Assembly Completion
and mut.serial_number = JOB.ro_serial_num
and mt.inventory_item_id = JOB.ro_item_id;
update csd_repair_job_xref
set quantity_completed = nvl(quantity_completed,0) + 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_job_xref_id = JOB.repair_job_xref_id;
Select mt.transaction_id,
mt.transaction_date,
mut.serial_number,
mt.subinventory_code
into l_mtl_trx_id,
l_mtl_trx_date,
l_mtl_serial_num,
l_mtl_subinv
from mtl_material_transactions mt,
mtl_unit_transactions mut
where mt.transaction_id = mut.transaction_id
and mt.transaction_source_id = JOB.wip_entity_id
and mt.transaction_source_type_id = 5 -- Job or Schedule
and mt.transaction_action_id = 31 -- Wip Assembly Completion
and mut.serial_number not in (select crh.paramc3
from csd_repair_history crh,
csd_repair_job_xref crj
where crh.repair_line_id = crj.repair_line_id
and crj.wip_entity_id = JOB.wip_entity_id
and crh.event_code = 'JC')
and mut.serial_number not in (Select cra.serial_number
from csd_repairs cra,
csd_repair_job_xref crj
where cra.repair_line_id = crj.repair_line_id
and crj.wip_entity_id = JOB.wip_entity_id
and cra.serial_number is not null)
and rownum = 1;
update csd_repair_job_xref
set quantity_completed = nvl(quantity_completed,0) + 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_job_xref_id = JOB.repair_job_xref_id;
Select mt.transaction_id,
mt.transaction_date,
mut.serial_number,
mt.subinventory_code
into l_mtl_trx_id,
l_mtl_trx_date,
l_mtl_serial_num,
l_mtl_subinv
from mtl_material_transactions mt,
mtl_unit_transactions mut
where mt.transaction_id = mut.transaction_id
and mt.transaction_source_id = JOB.wip_entity_id
and mt.transaction_source_type_id = 5 -- Job or Schedule
and mt.transaction_action_id = 31 -- Wip Assembly Completion
and mut.serial_number not in (select crh.paramc3
from csd_repair_history crh,
csd_repair_job_xref crj
where crh.repair_line_id = crj.repair_line_id
and crj.wip_entity_id = JOB.wip_entity_id
and crh.event_code = 'JC')
and rownum = 1;
update csd_repair_job_xref
set quantity_completed = nvl(quantity_completed,0) + 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_job_xref_id = JOB.repair_job_xref_id;
SELECT nvl(SUM(paramn5),0)
INTO l_total_qty
FROM CSD_REPAIR_HISTORY
WHERE paramn3 = MTL.transaction_id
AND paramn4 = JOB.wip_entity_id
AND event_Code= 'JC';
l_update_qty := (nvl(MTL.transaction_quantity,0) - l_total_qty);
l_update_Qty := l_remaining_qty;
Debug('l_update_Qty ='||TO_CHAR(l_update_Qty),l_mod_name,1);
update csd_repair_job_xref
set quantity_completed = nvl(quantity_completed,0) + NVL(l_update_qty,0),
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_job_xref_id = JOB.repair_job_xref_id;
l_activity_rec.PARAMN5 := l_update_qty;
ROLLBACK TO JOB_COMPLETION_UPDATE;
ROLLBACK TO JOB_COMPLETION_UPDATE;
ROLLBACK TO JOB_COMPLETION_UPDATE;
END JOB_COMPLETION_UPDATE;
/* Procedure name: JOB_CREATION_UPDATE */
/* Description : Procedure called from wip_update API to update the wip entity Id */
/* for the new jobs created by the WIP Mass Load concurrent program */
/* Called from : Called from WIP_Update API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER Optional Repair Order Line Id */
/* Output Parameter : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/* */
/*-------------------------------------------------------------------------------------*/
Procedure JOB_CREATION_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER
) IS
--Standard variables
l_api_name CONSTANT VARCHAR2(30) := 'JOB_CREATION_UPDATE';
l_update_qty NUMBER;
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.job_creation_update';
SELECT
crj.repair_job_xref_id,
crj.repair_line_id,
crj.organization_id,
crj.quantity allocated_job_qty,
we.wip_entity_id,
we.wip_entity_name,
wdj.start_quantity job_qty,
wdj.creation_date
from CSD_REPAIR_JOB_XREF crj,
WIP_ENTITIES we,
wip_discrete_jobs wdj
where wdj.wip_entity_id = we.wip_entity_id
and crj.job_name = we.wip_entity_name
and crj.organization_id = we.organization_id
and crj.repair_line_id = p_rep_line_id
and crj.wip_entity_id is null;
SELECT
crj.repair_job_xref_id,
crj.repair_line_id,
crj.organization_id,
crj.quantity allocated_job_qty,
we.wip_entity_id,
we.wip_entity_name,
wdj.start_quantity job_qty,
wdj.creation_date
from CSD_REPAIR_JOB_XREF crj,
WIP_ENTITIES we,
wip_discrete_jobs wdj
where wdj.wip_entity_id = we.wip_entity_id
and crj.job_name = we.wip_entity_name
and crj.organization_id = we.organization_id
and crj.wip_entity_id is null;
SAVEPOINT JOB_CREATION_UPDATE;
Debug('At the Beginning of JOB_CREATION_UPDATE',l_mod_name,1);
Update csd_repair_job_xref
set wip_entity_id = K.wip_entity_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_job_xref_id = K.repair_job_xref_id;
Update csd_repairs
set quantity_in_wip = NVL(quantity_in_wip,0) + K.allocated_job_qty,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number+1,
last_update_login = fnd_global.login_id
where repair_line_id = K.repair_line_id;
ROLLBACK TO JOB_CREATION_UPDATE;
ROLLBACK TO JOB_CREATION_UPDATE;
ROLLBACK TO JOB_CREATION_UPDATE;
END JOB_CREATION_UPDATE;
/* Procedure name: RECEIPTS_UPDATE */
/* Description : Procedure called from the UI to update the depot tables */
/* for the receipts against RMA/Internal Requisitions. It calls */
/* RMA_RCV_UPDATE and IO_RCV_UPDATE to process RMA and IO respectively */
/* Called from : Called from Depot Repair UI */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_header_id NUMBER Optional Interal sales order Id */
/* p_internal_order_flag VARCHAR2 Required Order Type; Possible values -'Y','N' */
Procedure RECEIPTS_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_internal_order_flag IN VARCHAR2,
p_order_header_id IN NUMBER,
p_repair_line_id IN NUMBER,
p_past_num_of_days IN NUMBER DEFAULT NULL ----bug#6753684, 6742512
) IS
--Standard variables
l_api_name CONSTANT VARCHAR2(30) := 'RECEIPTS_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.receipts_update';
SAVEPOINT RECEIPTS_UPDATE;
Debug('At the Beginning of Receipts_update',l_mod_name,1);
Debug('Calling IO_SHIP_UPDATE API',l_mod_name,2);
IO_SHIP_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_order_header_id => p_order_header_id );
Debug('Return Status from IO_SHIP_UPDATE API :'||x_return_status,l_mod_name,2);
Debug('IO_SHIP_UPDATE failed',l_mod_name,4);
Debug('Calling IO_RCV_UPDATE API',l_mod_name,2);
IO_RCV_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_order_header_id => p_order_header_id );
Debug('Return Status from IO_RCV_UPDATE API :'||x_return_status,l_mod_name,2);
Debug('IO_RCV_UPDATE failed',l_mod_name,4);
Debug('Calling RMA_RCV_UPDATE API',l_mod_name,2);
RMA_RCV_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_repair_line_id => p_repair_line_id,
p_past_num_of_days => p_past_num_of_days);
Debug('Return Status from RMA_RCV_UPDATE API :'||x_return_status,l_mod_name,2);
Debug('RMA_RCV_UPDATE failed',l_mod_name,4);
-- automatically update RO status when item is received.
-- dont show any error messages. Pass p_validation_level = fnd_api.g_valid_level_full to receive messages.
csd_repairs_util.auto_update_ro_status(
p_api_version => 1,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_repair_line_id => p_repair_line_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_event => 'RECEIVE',
p_validation_level => fnd_api.g_valid_level_none);
ROLLBACK TO RECEIPTS_UPDATE;
ROLLBACK TO RECEIPTS_UPDATE;
ROLLBACK TO RECEIPTS_UPDATE;
END RECEIPTS_UPDATE;
/* Procedure name: RMA_RCV_UPDATE */
/* Description : Procedure called from the update API to update the depot tables */
/* for the receipts against RMA. It also logs activities for accept */
/* reject txn lines */
/* Called from : Called from RECEIPTS_UPDATE API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER Optional Repair Order Line Id */
/* Output Parameter : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Procedure RMA_RCV_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER,
p_past_num_of_days IN NUMBER DEFAULT NULL) ----bug#6753684, 6742512
IS
-- Standard Variables
l_api_name CONSTANT VARCHAR2(30) := 'RMA_RCV_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.rma_rcv_update';
SELECT
oeh.order_number rma_number,
oeh.header_id rma_header_id,
oel.line_id ,
oel.line_number rma_line_number,
oel.inventory_item_id,
haou.name org_name,
rcvt.organization_id,
rcvt.unit_of_measure,
rcvt.quantity received_quantity,
rcvt.transaction_date received_date,
rcvt.transaction_id,
rcvt.subinventory,
rcvt.locator_id,
rcvt.transaction_type,
cra.serial_number ro_serial_number,
cra.repair_number,
cra.unit_of_measure ro_uom,
cra.inventory_item_id ro_item_id,
cpt.product_transaction_id,
cpt.repair_line_id,
cpt.action_code,
cpt.source_serial_number,
cpt.source_instance_id,
cpt.quantity_received prod_txn_recd_qty,
abs(ced.quantity_required) estimate_quantity,
ced.order_line_id est_order_line_id,
ced.inventory_item_id prod_txn_item_id
FROM csd_product_transactions cpt,
cs_estimate_details ced,
csd_repairs cra,
rcv_transactions rcvt,
oe_order_headers_all oeh,
oe_order_lines_all oel,
hr_all_organization_units haou
WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
AND ced.order_header_id is not null
AND rcvt.oe_order_line_id = ced.order_line_id ----bug#6753684, 6742512
AND rcvt.oe_order_header_id = ced.order_header_id ----bug#6753684, 6742512
AND ced.source_code = 'DR'
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND cra.repair_line_id = cpt.repair_line_id
AND oeh.header_id = ced.order_header_id
AND oel.header_id = oeh.header_id
AND rcvt.oe_order_line_id = oel.line_id
AND rcvt.transaction_type in ('DELIVER','ACCEPT','REJECT')
AND rcvt.source_document_code = 'RMA'
AND rcvt.organization_id = haou.organization_id
AND NOT EXISTS
(SELECT 'X'
FROM csd_repair_history crh
WHERE crh.repair_line_id = cpt.repair_line_id
AND event_code = decode(rcvt.transaction_type,
'DELIVER','RR',
'ACCEPT', 'IP',
'REJECT','IP','')
AND paramn1 = rcvt.transaction_id)
AND ((ced.QUANTITY_REQUIRED < -1
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oeh.header_id))
OR (ced.QUANTITY_REQUIRED = -1
AND ced.ORDER_LINE_ID = oel.LINE_ID));
for Depot Receipt Update to improve performance.
*/
CURSOR RECEIPT_LINES_BY_DATE( p_from_Date Date, p_to_Date Date ) IS
SELECT
oeh.order_number rma_number,
oeh.header_id rma_header_id,
oel.line_id ,
oel.line_number rma_line_number,
oel.inventory_item_id,
haou.name org_name,
rcvt.organization_id,
rcvt.unit_of_measure,
rcvt.quantity received_quantity,
rcvt.transaction_date received_date,
rcvt.transaction_id,
rcvt.subinventory,
rcvt.locator_id,
rcvt.transaction_type,
cra.serial_number ro_serial_number,
cra.repair_number,
cra.unit_of_measure ro_uom,
cra.inventory_item_id ro_item_id,
cpt.product_transaction_id,
cpt.repair_line_id,
cpt.action_code,
cpt.source_serial_number,
cpt.source_instance_id,
cpt.quantity_received prod_txn_recd_qty,
abs(ced.quantity_required) estimate_quantity,
ced.order_line_id est_order_line_id,
ced.inventory_item_id prod_txn_item_id
FROM csd_product_transactions cpt,
cs_estimate_details ced,
csd_repairs cra,
rcv_transactions rcvt,
oe_order_headers_all oeh,
oe_order_lines_all oel,
hr_all_organization_units haou
WHERE cra.creation_date between p_from_date and p_to_date
AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
AND ced.order_header_id is not null
AND rcvt.oe_order_line_id = ced.order_line_id ----bug#6753684, 6742512
AND rcvt.oe_order_header_id = ced.order_header_id ----bug#6753684, 6742512
AND ced.source_code = 'DR'
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND cra.repair_line_id = cpt.repair_line_id
AND oeh.header_id = ced.order_header_id
AND oel.header_id = oeh.header_id
AND rcvt.oe_order_line_id = oel.line_id
AND rcvt.transaction_type in ('DELIVER','ACCEPT','REJECT')
AND rcvt.source_document_code = 'RMA'
AND rcvt.organization_id = haou.organization_id
AND NOT EXISTS
(SELECT 'X'
FROM csd_repair_history crh
WHERE crh.repair_line_id = cpt.repair_line_id
AND event_code = decode(rcvt.transaction_type,
'DELIVER','RR',
'ACCEPT', 'IP',
'REJECT','IP','')
AND paramn1 = rcvt.transaction_id)
AND ((ced.QUANTITY_REQUIRED < -1
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oeh.header_id))
OR (ced.QUANTITY_REQUIRED = -1
AND ced.ORDER_LINE_ID = oel.LINE_ID));
SELECT
oeh.order_number rma_number,
oeh.header_id rma_header_id,
oel.line_id ,
oel.line_number rma_line_number,
oel.inventory_item_id,
haou.name org_name,
rcvt.organization_id,
rcvt.unit_of_measure,
rcvt.quantity received_quantity,
rcvt.transaction_date received_date,
rcvt.transaction_id,
rcvt.subinventory,
rcvt.locator_id,
rcvt.transaction_type,
cra.serial_number ro_serial_number,
cra.repair_number,
cra.unit_of_measure ro_uom,
cra.inventory_item_id ro_item_id,
cpt.product_transaction_id,
cpt.repair_line_id,
cpt.action_code,
cpt.source_serial_number,
cpt.source_instance_id,
cpt.quantity_received prod_txn_recd_qty,
abs(ced.quantity_required) estimate_quantity,
ced.order_line_id est_order_line_id,
ced.inventory_item_id prod_txn_item_id
FROM hr_all_organization_units haou,
csd_repairs cra,
oe_order_headers_all oeh,
oe_order_lines_all oel,
rcv_transactions rcvt,
cs_estimate_details ced,
csd_product_transactions cpt
WHERE cpt.repair_line_id = p_repair_line_id
AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
AND ced.order_header_id is not null
AND rcvt.oe_order_line_id = ced.order_line_id ----bug#6753684, 6742512
AND rcvt.oe_order_header_id = ced.order_header_id ----bug#6753684, 6742512
AND ced.source_code = 'DR'
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND cra.repair_line_id = cpt.repair_line_id
AND oeh.header_id = ced.order_header_id
AND oel.header_id = oeh.header_id
AND rcvt.oe_order_line_id = oel.line_id
AND rcvt.transaction_type in ('DELIVER','ACCEPT','REJECT')
AND rcvt.source_document_code = 'RMA'
AND rcvt.organization_id = haou.organization_id
AND NOT EXISTS
(SELECT 'X'
FROM csd_repair_history crh
WHERE crh.repair_line_id = cpt.repair_line_id
AND event_code = decode(rcvt.transaction_type,
'DELIVER','RR',
'ACCEPT', 'IP',
'REJECT','IP','')
AND paramn1 = rcvt.transaction_id)
AND ((ced.QUANTITY_REQUIRED < -1
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oeh.header_id))
OR (ced.QUANTITY_REQUIRED = -1
AND ced.ORDER_LINE_ID = oel.LINE_ID));
SELECT cra.REPAIR_LINE_ID
FROM csd_repairs cra,
cs_estimate_details ced,
csd_product_transactions cpt
WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY')
AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
AND ced.order_header_id is not null
AND ced.source_code = 'DR'
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND cra.repair_line_id = cpt.repair_line_id;
select rst.serial_num,
rst.lot_num
from rcv_serial_transactions rst,
rcv_transactions rt
where rt.transaction_id = p_txn_id
and rst.transaction_id = rt.transaction_id;
SAVEPOINT RMA_RCV_UPDATE;
Debug('At the Beginning of RMA_RCV_UPDATE',l_mod_name,1);
select serial_number_control_code,
lot_control_code,
comms_nl_trackable_flag
into l_srl_ctl_code,
l_lot_ctl_code,
l_ib_flag
from mtl_system_items
where inventory_item_id = i.inventory_item_id
and organization_id = i.organization_id;
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = I.inventory_item_id
and serial_number = l_st_serial_num;
Select lot_num
into l_lot_number
from rcv_lot_transactions
where source_transaction_id = I.transaction_id;
-- Update the serial num and instance id if the item on ro
-- is same as prod txn
update csd_repairs
set quantity_rcvd = nvl(quantity_rcvd,0)+ l_result_quantity,
object_version_number = object_version_number+1,
customer_product_id = l_instance_id,
serial_number = l_st_serial_num,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = I.repair_line_id;
-- Update the qty if the item on ro
-- is not same as prod txn
update csd_repairs
set quantity_rcvd = nvl(quantity_rcvd,0)+ l_result_quantity,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = I.repair_line_id;
Update csd_product_transactions
set sub_inventory = I.subinventory,
locator_id = I.locator_id,
lot_number_rcvd = l_lot_number,
source_instance_id = l_instance_id,
source_serial_number = l_st_serial_num,
quantity_received = nvl(quantity_received,0) + nvl(I.received_quantity,0),
prod_txn_status = l_prod_txn_status,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = i.product_transaction_id;
Debug('processing cancelled orders in RMA_RCV_UPDATE',l_mod_name,1);
Debug('At the end of processing cancelled orders in RMA_RCV_UPDATE',l_mod_name,1);
ROLLBACK TO RMA_RCV_UPDATE;
ROLLBACK TO RMA_RCV_UPDATE;
ROLLBACK TO RMA_RCV_UPDATE;
END RMA_RCV_UPDATE;
/* Procedure name: IO_RCV_UPDATE */
/* Description : Procedure called from the Update api to update the depot tables */
/* for the receipts against Internal Requisitions */
/* It also logs activities for accept reject txn lines */
/* Called from : Called from RECEIPTS_UPDATE API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_header_id NUMBER Optional Internal sales order Id */
/* Output Parm : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Procedure IO_RCV_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_order_header_id IN NUMBER
) IS
-- Standard variables
l_api_name CONSTANT VARCHAR2(30) := 'IO_RCV_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.io_rcv_update';
select cpt.product_transaction_id,
cpt.prod_txn_status,
cpt.repair_line_id,
cpt.order_header_id,
cpt.order_line_id,
cpt.req_header_id,
cpt.req_line_id,
nvl(cpt.quantity_received,0) prod_txn_rcvd_qty,
cra.quantity ro_qty,
cra.quantity_rcvd ro_rcvd_qty,
cra.inventory_item_id,
cra.unit_of_measure ro_uom,
prh.segment1 requisition_number,
oel.ordered_quantity,
oeh.order_number
from csd_product_transactions cpt,
csd_repairs cra,
po_requisition_headers_all prh,
oe_order_lines_all oel,
oe_order_headers_all oeh
where cpt.repair_line_id = cra.repair_line_id
AND cpt.req_header_id = prh.requisition_header_id
AND cpt.order_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND cpt.action_type = 'MOVE_IN'
AND cpt.action_code = 'DEFECTIVES'
AND cpt.prod_txn_status = 'SHIPPED'
AND cpt.order_line_id is not null
--Vijay 11/4/04
-- AND (p_ord_header_id IS null OR p_ord_header_id = cpt.order_header_id)
AND nvl(cra.quantity_rcvd,0) < cra.quantity;
select cpt.product_transaction_id,
cpt.prod_txn_status,
cpt.repair_line_id,
cpt.order_header_id,
cpt.order_line_id,
cpt.req_header_id,
cpt.req_line_id,
nvl(cpt.quantity_received,0) prod_txn_rcvd_qty,
cra.quantity ro_qty,
cra.quantity_rcvd ro_rcvd_qty,
cra.inventory_item_id,
cra.unit_of_measure ro_uom,
prh.segment1 requisition_number,
oel.ordered_quantity,
oeh.order_number
from csd_product_transactions cpt,
csd_repairs cra,
po_requisition_headers_all prh,
oe_order_lines_all oel,
oe_order_headers_all oeh
where cpt.repair_line_id = cra.repair_line_id
AND cpt.req_header_id = prh.requisition_header_id
AND cpt.order_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND cpt.action_type = 'MOVE_IN'
AND cpt.action_code = 'DEFECTIVES'
AND cpt.prod_txn_status = 'SHIPPED'
AND cpt.order_line_id is not null
AND cpt.order_header_id = p_ord_header_id
AND nvl(cra.quantity_rcvd,0) < cra.quantity;
select rcv.transaction_id,
rcv.quantity rcvd_qty,
rcv.unit_of_measure,
rcv.subinventory,
rcv.locator_id,
rcv.organization_id,
rcv.transaction_date received_date,
rcv.transaction_type,
rcv.shipment_header_id,
rcv.shipment_line_id,
prl.item_id,
prl.destination_organization_id inv_org_id,
prl.quantity requisition_qty,
prh.segment1 requirement_number,
mtl.serial_number_control_code,
mtl.comms_nl_trackable_flag ib_flag,
mtl.lot_control_code,
hao.name org_name
from rcv_transactions rcv,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
mtl_system_items mtl,
hr_all_organization_units hao
where rcv.requisition_line_id = prl.requisition_line_id
and prl.item_id = mtl.inventory_item_id
and prl.destination_organization_id = mtl.organization_id
and prl.requisition_header_id = prh.requisition_header_id
and rcv.requisition_line_id = p_req_line_id
and hao.organization_id = rcv.organization_id
and rcv.transaction_type in ('DELIVER','ACCEPT','REJECT');
Select rcvt.transaction_id,
rcvt.transaction_date received_date,
rcvt.subinventory,
rcvt.quantity rcvd_qty,
rcvt.organization_id,
rcvt.locator_id,
hao.name org_name
from rcv_transactions rcvt,
hr_all_organization_units hao
where rcvt.parent_transaction_id = p_txn_id
and rcvt.transaction_type = 'DELIVER';
Select distinct
order_header_id,
order_line_id
from csd_product_transactions
where order_header_id = p_ord_header_id
AND action_type = 'MOVE_IN'
AND action_code = 'DEFECTIVES'
AND prod_txn_status = 'SHIPPED';
SAVEPOINT IO_RCV_UPDATE;
Debug('Beginning of IO_RCV_UPDATE',l_mod_name,1);
select 'EXISTS'
into l_dummy
from oe_order_headers_all oeh,
po_requisition_headers_all prh
where oeh.source_document_id = prh.requisition_header_id
and oeh.header_id = p_order_header_id
and exists (select 'x'
from csd_product_transactions cpt
where cpt.action_type = 'MOVE_IN'
and cpt.action_code = 'DEFECTIVES'
and cpt.order_header_id = oeh.header_id);
select nvl(sum(paramn3),0)
into l_total_accept_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn1 = RCV.transaction_id;
select nvl(sum(paramn4),0), nvl(sum(paramn3),0)
into l_pt_reject_qty, l_pt_accept_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn2 = RO.product_transaction_id;
select nvl(sum(quantity),0)
into l_total_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn1 = DEL.transaction_id;
select nvl(sum(quantity),0)
into l_pt_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn2 = RO.product_transaction_id;
select rcvt.serial_num,
rcvt.lot_num
into l_serial_num,
l_lot_num
from rcv_serial_transactions rcvt
where rcvt.transaction_id = DEL.transaction_id
and rownum = 1
and not exists (Select 'NOT EXIST'
from csd_repairs cra,
csd_product_transactions cpt
where cra.repair_line_id = cpt.repair_line_id
and cpt.action_type = 'MOVE_IN'
and cpt.order_header_id = ro.order_header_id
and cra.serial_number = rcvt.serial_num);
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = RCV.item_id
and serial_number = l_serial_num;
Select lot_num
into l_lot_num
from rcv_lot_transactions
where source_transaction_id = DEL.transaction_id;
UPDATE CSD_REPAIRS
SET SERIAL_NUMBER = l_serial_num,
quantity_rcvd = nvl(quantity_rcvd,0) + l_rcvd_qty,
customer_product_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE repair_line_id = RO.repair_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET SOURCE_SERIAL_NUMBER = l_serial_num,
source_instance_id = l_instance_id,
LOT_NUMBER_RCVD = l_lot_num,
LOCATOR_ID = DEL.locator_id,
QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
SUB_INVENTORY_RCVD = DEL.subinventory,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quantity_received = RO.ordered_quantity
and product_transaction_id = RO.product_transaction_id;
select nvl(sum(paramn4),0)
into l_total_reject_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn1 = RCV.transaction_id;
select nvl(sum(paramn3),0),nvl(sum(paramn4),0)
into l_pt_accept_qty, l_pt_reject_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn2 = RO.product_transaction_id;
select nvl(sum(quantity),0)
into l_total_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn1 = DEL.transaction_id;
select nvl(sum(quantity),0)
into l_pt_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn2 = RO.product_transaction_id;
select rcvt.serial_num,
rcvt.lot_num
into l_serial_num,
l_lot_num
from rcv_serial_transactions rcvt
where rcvt.transaction_id = DEL.transaction_id
and rownum = 1
and not exists (Select 'NOT EXIST'
from csd_repairs cra,
csd_product_transactions cpt
where cra.repair_line_id = cpt.repair_line_id
and cpt.action_type = 'MOVE_IN'
and cpt.order_header_id = ro.order_header_id
and cra.serial_number = rcvt.serial_num);
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = RCV.item_id
and serial_number = l_serial_num;
Select lot_num
into l_lot_num
from rcv_lot_transactions
where source_transaction_id = DEL.transaction_id;
UPDATE CSD_REPAIRS
SET SERIAL_NUMBER = l_serial_num,
quantity_rcvd = nvl(quantity_rcvd,0) + l_rcvd_qty,
customer_product_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE repair_line_id = RO.repair_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET SOURCE_SERIAL_NUMBER = l_serial_num,
source_instance_id = l_instance_id,
LOT_NUMBER_RCVD = l_lot_num,
LOCATOR_ID = DEL.locator_id,
QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
SUB_INVENTORY_RCVD = DEL.subinventory,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quantity_received = RO.ordered_quantity
and product_transaction_id = RO.product_transaction_id;
select nvl(sum(quantity),0)
into l_total_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn1 = RCV.transaction_id;
select nvl(sum(quantity),0)
into l_pt_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and paramn2 = RO.product_transaction_id;
select rcvt.serial_num,
rcvt.lot_num
into l_serial_num,
l_lot_num
from rcv_serial_transactions rcvt
where rcvt.transaction_id = RCV.transaction_id
and rownum = 1
and not exists (Select 'NOT EXIST'
from csd_repairs cra,
csd_product_transactions cpt
where cra.repair_line_id = cpt.repair_line_id
and cpt.order_header_id = ro.order_header_id
and cra.serial_number = rcvt.serial_num);
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = RCV.item_id
and serial_number = l_serial_num;
Select lot_num
into l_lot_num
from rcv_lot_transactions
where source_transaction_id = RCV.transaction_id;
UPDATE CSD_REPAIRS
SET SERIAL_NUMBER = l_serial_num,
quantity_rcvd = nvl(quantity_rcvd,0) + l_rcvd_qty,
customer_product_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE repair_line_id = RO.repair_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET SOURCE_SERIAL_NUMBER = l_serial_num,
source_instance_id = l_instance_id,
LOT_NUMBER_RCVD = l_lot_num,
QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
SUB_INVENTORY_RCVD= RCV.subinventory,
LOCATOR_ID = RCV.locator_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quantity_received = RO.ordered_quantity
and product_transaction_id = RO.product_transaction_id;
ROLLBACK TO IO_RCV_UPDATE;
ROLLBACK TO IO_RCV_UPDATE;
ROLLBACK TO IO_RCV_UPDATE;
END IO_RCV_UPDATE;
/* Procedure name: IO_RCV_UPDATE_MOVE_OUT */
/* Description : Procedure called from the Update api to update the depot tables */
/* for the receipts against Internal Requisitions for move out line */
/* It also logs activities for accept reject txn lines */
/* Called from : Called from SHIP_UPDATE API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_header_id NUMBER Optional Internal sales order Id */
/* Output Parm : */
/* Change Hist : */
/* 24-Apr-2007 swai Initial Creation. Bug#5564180 / FP#5845995 */
/*-------------------------------------------------------------------------------------*/
Procedure IO_RCV_UPDATE_MOVE_OUT
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_order_header_id IN NUMBER
) IS
-- Standard variables
l_api_name CONSTANT VARCHAR2(30) := 'IO_RCV_UPDATE_MOVE_OUT';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.io_rcv_update_move_out';
select cpt.product_transaction_id,
cpt.prod_txn_status,
cpt.repair_line_id,
cpt.order_header_id,
cpt.order_line_id,
cpt.req_header_id,
cpt.req_line_id,
nvl(cpt.quantity_received,0) prod_txn_rcvd_qty,
cra.quantity ro_qty,
cra.quantity_rcvd ro_rcvd_qty,
cra.inventory_item_id,
cra.unit_of_measure ro_uom,
prh.segment1 requisition_number,
oel.ordered_quantity,
oeh.order_number
from csd_product_transactions cpt,
csd_repairs cra,
po_requisition_headers_all prh,
oe_order_lines_all oel,
oe_order_headers_all oeh
where cpt.repair_line_id = cra.repair_line_id
AND cpt.req_header_id = prh.requisition_header_id
AND cpt.order_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND cpt.action_type = 'MOVE_OUT'
AND cpt.action_code = 'USABLES'
AND cpt.prod_txn_status = 'SHIPPED'
AND cpt.order_line_id is not null;
will not be updated with status received */
-- AND nvl(cra.quantity_rcvd,0) < cra.quantity;
select cpt.product_transaction_id,
cpt.prod_txn_status,
cpt.repair_line_id,
cpt.order_header_id,
cpt.order_line_id,
cpt.req_header_id,
cpt.req_line_id,
nvl(cpt.quantity_received,0) prod_txn_rcvd_qty,
cra.quantity ro_qty,
cra.quantity_rcvd ro_rcvd_qty,
cra.inventory_item_id,
cra.unit_of_measure ro_uom,
prh.segment1 requisition_number,
oel.ordered_quantity,
oeh.order_number
from csd_product_transactions cpt,
csd_repairs cra,
po_requisition_headers_all prh,
oe_order_lines_all oel,
oe_order_headers_all oeh
where cpt.repair_line_id = cra.repair_line_id
AND cpt.req_header_id = prh.requisition_header_id
AND cpt.order_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND cpt.action_type = 'MOVE_OUT'
AND cpt.action_code = 'USABLES'
AND cpt.prod_txn_status = 'SHIPPED'
AND cpt.order_line_id is not null
AND cpt.order_header_id = p_ord_header_id;
not be updated with status received */
-- AND nvl(cra.quantity_rcvd,0) < cra.quantity;
select rcv.transaction_id,
rcv.quantity rcvd_qty,
rcv.unit_of_measure,
rcv.subinventory,
rcv.locator_id,
rcv.organization_id,
rcv.transaction_date received_date,
rcv.transaction_type,
rcv.shipment_header_id,
rcv.shipment_line_id,
prl.item_id,
prl.destination_organization_id inv_org_id,
prl.quantity requisition_qty,
prh.segment1 requirement_number,
mtl.serial_number_control_code,
mtl.comms_nl_trackable_flag ib_flag,
mtl.lot_control_code,
hao.name org_name
from rcv_transactions rcv,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
mtl_system_items mtl,
hr_all_organization_units hao
where rcv.requisition_line_id = prl.requisition_line_id
and prl.item_id = mtl.inventory_item_id
and prl.destination_organization_id = mtl.organization_id
and prl.requisition_header_id = prh.requisition_header_id
and rcv.requisition_line_id = p_req_line_id
and hao.organization_id = rcv.organization_id
and rcv.transaction_type in ('DELIVER','ACCEPT','REJECT');
Select rcvt.transaction_id,
rcvt.transaction_date received_date,
rcvt.subinventory,
rcvt.quantity rcvd_qty,
rcvt.organization_id,
rcvt.locator_id,
hao.name org_name
from rcv_transactions rcvt,
hr_all_organization_units hao
where rcvt.parent_transaction_id = p_txn_id
and rcvt.transaction_type = 'DELIVER';
Select distinct
order_header_id,
order_line_id
from csd_product_transactions
where order_header_id = p_ord_header_id
AND action_type = 'MOVE_OUT'
AND action_code = 'USABLES'
AND prod_txn_status = 'SHIPPED';
SAVEPOINT IO_RCV_UPDATE_MOVE_OUT;
Debug('Beginning of IO_RCV_UPDATE_MOVE_OUT',l_mod_name,1);
select 'EXISTS'
into l_dummy
from oe_order_headers_all oeh,
po_requisition_headers_all prh
where oeh.source_document_id = prh.requisition_header_id
and oeh.header_id = p_order_header_id
and exists (select 'x'
from csd_product_transactions cpt
where cpt.action_type = 'MOVE_OUT'
and cpt.action_code = 'USABLES'
and cpt.order_header_id = oeh.header_id);
select nvl(sum(paramn3),0)
into l_total_accept_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn1 = RCV.transaction_id;
select nvl(sum(paramn4),0), nvl(sum(paramn3),0)
into l_pt_reject_qty, l_pt_accept_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn2 = RO.product_transaction_id;
select nvl(sum(quantity),0)
into l_total_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn1 = DEL.transaction_id;
select nvl(sum(quantity),0)
into l_pt_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn2 = RO.product_transaction_id;
select rcvt.serial_num,
rcvt.lot_num
into l_serial_num,
l_lot_num
from rcv_serial_transactions rcvt
where rcvt.transaction_id = DEL.transaction_id
and rownum = 1
and not exists (Select 'NOT EXIST'
from csd_repairs cra,
csd_product_transactions cpt
where cra.repair_line_id = cpt.repair_line_id
and cpt.action_type = 'MOVE_OUT'
and cpt.order_header_id = ro.order_header_id
and cra.serial_number = rcvt.serial_num);
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = RCV.item_id
and serial_number = l_serial_num;
Select lot_num
into l_lot_num
from rcv_lot_transactions
where source_transaction_id = DEL.transaction_id;
/*Bug#5564180/FP#5845995 Move out line should not update the received quantity of
RO. Move out line should not change the serial and instance number of RO
so commenting the below query
*/
/*
UPDATE CSD_REPAIRS
SET SERIAL_NUMBER = l_serial_num,
quantity_rcvd = nvl(quantity_rcvd,0) + l_rcvd_qty,
customer_product_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE repair_line_id = RO.repair_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET SOURCE_SERIAL_NUMBER = l_serial_num,
source_instance_id = l_instance_id,
LOT_NUMBER_RCVD = l_lot_num,
LOCATOR_ID = DEL.locator_id,
QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
SUB_INVENTORY_RCVD = DEL.subinventory,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quantity_received = RO.ordered_quantity
and product_transaction_id = RO.product_transaction_id;
select nvl(sum(paramn4),0)
into l_total_reject_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn1 = RCV.transaction_id;
select nvl(sum(paramn3),0),nvl(sum(paramn4),0)
into l_pt_accept_qty, l_pt_reject_qty
from csd_repair_history crh
where crh.event_code = 'IP'
and crh.paramn2 = RO.product_transaction_id;
select nvl(sum(quantity),0)
into l_total_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn1 = DEL.transaction_id;
select nvl(sum(quantity),0)
into l_pt_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn2 = RO.product_transaction_id;
select rcvt.serial_num,
rcvt.lot_num
into l_serial_num,
l_lot_num
from rcv_serial_transactions rcvt
where rcvt.transaction_id = DEL.transaction_id
and rownum = 1
and not exists (Select 'NOT EXIST'
from csd_repairs cra,
csd_product_transactions cpt
where cra.repair_line_id = cpt.repair_line_id
and cpt.action_type = 'MOVE_OUT'
and cpt.order_header_id = ro.order_header_id
and cra.serial_number = rcvt.serial_num);
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = RCV.item_id
and serial_number = l_serial_num;
Select lot_num
into l_lot_num
from rcv_lot_transactions
where source_transaction_id = DEL.transaction_id;
/*Bug#5564180/FP#5845995 Move out line should not update the received quantity of
RO. Move out line should not change the serial and instance number of RO
so commenting the below query
*/
/*
UPDATE CSD_REPAIRS
SET SERIAL_NUMBER = l_serial_num,
quantity_rcvd = nvl(quantity_rcvd,0) + l_rcvd_qty,
customer_product_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE repair_line_id = RO.repair_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET SOURCE_SERIAL_NUMBER = l_serial_num,
source_instance_id = l_instance_id,
LOT_NUMBER_RCVD = l_lot_num,
LOCATOR_ID = DEL.locator_id,
QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
SUB_INVENTORY_RCVD = DEL.subinventory,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quantity_received = RO.ordered_quantity
and product_transaction_id = RO.product_transaction_id;
select nvl(sum(quantity),0)
into l_total_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and crh.paramn1 = RCV.transaction_id;
select nvl(sum(quantity),0)
into l_pt_del_qty
from csd_repair_history crh
where crh.event_code = 'RRI'
and paramn2 = RO.product_transaction_id;
select rcvt.serial_num,
rcvt.lot_num
into l_serial_num,
l_lot_num
from rcv_serial_transactions rcvt
where rcvt.transaction_id = RCV.transaction_id
and rownum = 1;
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = RCV.item_id
and serial_number = l_serial_num;
Select lot_num
into l_lot_num
from rcv_lot_transactions
where source_transaction_id = RCV.transaction_id;
/*Bug#5564180/FP#5845995 Move out line should not update the received quantity of
RO. Move out line should not change the serial and instance number of RO
so commenting the below query
*/
/*
UPDATE CSD_REPAIRS
SET SERIAL_NUMBER = l_serial_num,
quantity_rcvd = nvl(quantity_rcvd,0) + l_rcvd_qty,
customer_product_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE repair_line_id = RO.repair_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET SOURCE_SERIAL_NUMBER = l_serial_num,
source_instance_id = l_instance_id,
LOT_NUMBER_RCVD = l_lot_num,
QUANTITY_RECEIVED = NVL(QUANTITY_RECEIVED,0) + l_rcvd_qty,
SUB_INVENTORY_RCVD= RCV.subinventory,
LOCATOR_ID = RCV.locator_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = RO.product_transaction_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RECEIVED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE quantity_received = RO.ordered_quantity
and product_transaction_id = RO.product_transaction_id;
ROLLBACK TO IO_RCV_UPDATE_MOVE_OUT;
ROLLBACK TO IO_RCV_UPDATE_MOVE_OUT;
ROLLBACK TO IO_RCV_UPDATE_MOVE_OUT;
END IO_RCV_UPDATE_MOVE_OUT;
/* Procedure name: SHIP_UPDATE */
/* Description : Procedure called from the UI to update the depot tables */
/* for the shipment against regular sales order/Internal Sales Order */
/* It calls SO_SHIP_UPDATE and IO_SHIP_UPDATE to process sales order */
/* and internal sales order */
/* Called from : Called from Depot Repair UI */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_header_id NUMBER Optional Interal sales order Id */
/* p_internal_order_flag VARCHAR2 Required Order Type; Possible values -'Y','N' */
Procedure SHIP_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_internal_order_flag IN VARCHAR2,
p_order_header_id IN NUMBER,
p_repair_line_id IN NUMBER,
p_past_num_of_days IN NUMBER DEFAULT NULL) ----bug#6753684, 6742512
IS
-- Standard Variables
l_api_name CONSTANT VARCHAR2(30) := 'SHIPMENT_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.ship_update';
SAVEPOINT SHIP_UPDATE;
Debug('At the Beginning of shipment Update ',l_mod_name,1);
Debug('Calling IO_SHIP_UPDATE ',l_mod_name,2);
IO_SHIP_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_order_header_id => p_order_header_id);
Debug('Return status from IO_SHIP_UPDATE '||x_return_status,l_mod_name,2);
Debug('IO_SHIP_UPDATE failed ',l_mod_name,4);
Call to API IO_RCV_UPDATE_MOVE_OUT is added so that
move out line can be updated for receiving in destination
organization.
*/
Debug('Calling IO_RCV_UPDATE_MOVE_OUT API',l_mod_name,2);
IO_RCV_UPDATE_MOVE_OUT
( p_api_version => p_api_version,
p_commit => p_commit ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_order_header_id => p_order_header_id );
Debug('Return Status from IO_RCV_UPDATE_MOVE_OUT :'||x_return_status,l_mod_name,2);
Debug('IO_RCV_UPDATE_MOVE_OUT failed',l_mod_name,4);
Debug('Calling SO_SHIP_UPDATE ',l_mod_name,2);
SO_SHIP_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_repair_line_id => p_repair_line_id,
p_past_num_of_days => p_past_num_of_days);
Debug('Return status from SO_SHIP_UPDATE '||x_return_status,l_mod_name,2);
Debug('SO_SHIP_UPDATE failed ',l_mod_name,4);
ROLLBACK TO SHIP_UPDATE;
ROLLBACK TO SHIP_UPDATE;
ROLLBACK TO SHIP_UPDATE;
END SHIP_UPDATE;
/* Procedure name: SO_SHIP_UPDATE */
/* Description : Procedure called from the Update api to update the depot tables */
/* for the shipment against sales order */
/* It also logs activities for the deliver txn lines */
/* Called from : Called from SHIP_UPDATE API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER Optional Repair Order Line Id */
/* Output Parm : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
PROCEDURE SO_SHIP_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER,
p_past_num_of_days IN NUMBER DEFAULT NULL) ----bug#6753684, 6742512
IS
--Cursor split into to by Vijay 11/4/04 one without repair line and
-- one with repair line.
-- Cursor to get all the shipment lines
Cursor SHIPMENT_LINES_ALL is
select
---changed to fix 3801614 , added dsn.serial_number if dd.serial_number is null
nvl(dd.serial_number, dsn.fm_serial_number) shipped_serial_num,
dd.lot_number lot_number,
dd.revision revision,
dd.subinventory subinv,
dd.requested_quantity,
dd.shipped_quantity,
dd.delivery_detail_id,
dd.requested_quantity_uom shipped_uom,
dd.inventory_item_id ,
dd.organization_id,
dd.source_header_number order_number,
dd.source_header_id sales_order_header,
dd.locator_id,
oel.line_number order_line_number,
oel.actual_shipment_date date_shipped,
oel.line_id, --Bug#6779806
cra.repair_number,
cra.repair_line_id,
cra.unit_of_measure ro_uom,
cra.inventory_item_id ro_item_id,
ced.quantity_required estimate_quantity,
cpt.source_serial_number prod_txn_serial_num,
cpt.source_instance_id,
cpt.product_transaction_id,
cpt.action_code,
wnd.name delivery_name,
hao.name org_name
from
csd_product_transactions cpt,
cs_estimate_details ced,
csd_repairs cra,
wsh_delivery_details dd ,
wsh_serial_numbers dsn,--Added to fix 3801614
--Changed to view from table, bug: 4341784
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oel,
hr_all_organization_units hao
Where cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
AND cpt.estimate_detail_id = ced.estimate_detail_id
AND dd.delivery_detail_id = wda.delivery_detail_id
AND dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
AND dd.organization_id = hao.organization_id
AND wda.delivery_id = wnd.delivery_id
AND cpt.repair_line_id = cra.repair_line_id
AND ced.order_header_id = oel.header_id
AND ced.order_line_id = oel.line_id ----bug#6753684, 6742512
AND dd.source_header_id = ced.order_header_id ----bug#6753684, 6742512
AND dd.source_line_id = ced.order_line_id ----bug#6753684, 6742512
AND dd.source_header_id = oel.header_id ----bug#6753684, 6742512
AND dd.source_line_id = oel.line_id
AND dd.source_code = 'OE' -- 4423818
AND dd.released_status in ('C','I')
AND ced.source_code = 'DR'
AND not exists
(select 'NOT EXIST'
from csd_repair_history crh
where crh.repair_line_id = cpt.repair_line_id
and crh.paramn1 = dd.delivery_detail_id
and event_code = 'PS')
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oel.header_id);
for Depot Shipment Update to improve performance.
*/
Cursor SHIPMENT_LINES_BY_DATE( p_from_Date Date, p_to_Date Date ) is
select
---changed to fix 3801614 , added dsn.serial_number if dd.serial_number is null
nvl(dd.serial_number, dsn.fm_serial_number) shipped_serial_num,
dd.lot_number lot_number,
dd.revision revision,
dd.subinventory subinv,
dd.requested_quantity,
dd.shipped_quantity,
dd.delivery_detail_id,
dd.requested_quantity_uom shipped_uom,
dd.inventory_item_id ,
dd.organization_id,
dd.source_header_number order_number,
dd.source_header_id sales_order_header,
dd.locator_id,
oel.line_number order_line_number,
oel.actual_shipment_date date_shipped,
oel.line_id, --Bug#6779806
cra.repair_number,
cra.repair_line_id,
cra.unit_of_measure ro_uom,
cra.inventory_item_id ro_item_id,
ced.quantity_required estimate_quantity,
cpt.source_serial_number prod_txn_serial_num,
cpt.source_instance_id,
cpt.product_transaction_id,
cpt.action_code,
wnd.name delivery_name,
hao.name org_name
from
csd_product_transactions cpt,
cs_estimate_details ced,
csd_repairs cra,
wsh_delivery_details dd ,
wsh_serial_numbers dsn,--Added to fix 3801614
--Changed to view from table, bug: 4341784
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oel,
hr_all_organization_units hao
Where cra.creation_date between p_from_date and p_to_date
AND cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
AND cpt.estimate_detail_id = ced.estimate_detail_id
AND dd.delivery_detail_id = wda.delivery_detail_id
AND dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
AND dd.organization_id = hao.organization_id
AND wda.delivery_id = wnd.delivery_id
AND cpt.repair_line_id = cra.repair_line_id
AND ced.order_header_id = oel.header_id
AND ced.order_line_id = oel.line_id ----bug#6753684, 6742512
AND dd.source_header_id = ced.order_header_id ----bug#6753684, 6742512
AND dd.source_line_id = ced.order_line_id ----bug#6753684, 6742512
AND dd.source_header_id = oel.header_id ----bug#6753684, 6742512
AND dd.source_line_id = oel.line_id
AND dd.source_code = 'OE' -- 4423818
AND dd.released_status in ('C','I')
AND ced.source_code = 'DR'
AND not exists
(select 'NOT EXIST'
from csd_repair_history crh
where crh.repair_line_id = cpt.repair_line_id
and crh.paramn1 = dd.delivery_detail_id
and event_code = 'PS')
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oel.header_id);
select
---changed to fix 3801614 , added dsn.serial_number if dd.serial_number is null
nvl(dd.serial_number, dsn.fm_serial_number) shipped_serial_num,
dd.lot_number lot_number,
dd.revision revision,
dd.subinventory subinv,
dd.requested_quantity,
dd.shipped_quantity,
dd.delivery_detail_id,
dd.requested_quantity_uom shipped_uom,
dd.inventory_item_id ,
dd.organization_id,
dd.source_header_number order_number,
dd.source_header_id sales_order_header,
dd.locator_id,
oel.line_number order_line_number,
oel.actual_shipment_date date_shipped,
oel.line_id, --Bug#6779806
cra.repair_number,
cra.repair_line_id,
cra.unit_of_measure ro_uom,
cra.inventory_item_id ro_item_id,
ced.quantity_required estimate_quantity,
cpt.source_serial_number prod_txn_serial_num,
cpt.source_instance_id,
cpt.product_transaction_id,
cpt.action_code,
wnd.name delivery_name,
hao.name org_name
from
csd_product_transactions cpt,
cs_estimate_details ced,
csd_repairs cra,
wsh_delivery_details dd ,
wsh_serial_numbers dsn,--Added to fix 3801614
--Changed to view from table, bug: 4341784
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oel,
hr_all_organization_units hao
Where cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY') -- Walk-in-issue will be changed to ship
AND cpt.estimate_detail_id = ced.estimate_detail_id
AND dd.delivery_detail_id = wda.delivery_detail_id
AND dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
AND dd.organization_id = hao.organization_id
AND wda.delivery_id = wnd.delivery_id
AND cpt.repair_line_id = cra.repair_line_id
AND ced.order_header_id = oel.header_id
AND dd.source_line_id = oel.line_id
AND dd.released_status in ('C','I')
AND ced.source_code = 'DR'
AND dd.source_code = 'OE' -- 12.1 FP bug#7551078, subhat
AND not exists
(select 'NOT EXIST'
from csd_repair_history crh
where crh.repair_line_id = cpt.repair_line_id
and crh.paramn1 = dd.delivery_detail_id
and event_code = 'PS')
AND cpt.repair_line_id = p_repair_line_id
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oel.header_id);
SELECT cra.REPAIR_LINE_ID
FROM csd_repairs cra,
cs_estimate_details ced,
csd_product_transactions cpt
WHERE cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY')
AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
AND ced.order_header_id is not null
AND ced.source_code = 'DR'
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND cra.repair_line_id = cpt.repair_line_id;
SELECT instance_id
FROM csi_item_instances
WHERE last_oe_order_line_id = p_order_line_id
AND inventory_item_id = p_inventory_item_id;
l_enable_update_instance VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'SO_SHIP_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.so_ship_update';
SAVEPOINT SO_SHIP_UPDATE;
Debug('At the Beginning of ship Update ',l_mod_name,1);
l_enable_update_instance := nvl(FND_PROFILE.VALUE('CSD_UPDATE_INSTANCE_ID_FOR_NON_S_IB'), 'N');
select serial_number_control_code,
comms_nl_trackable_flag
into l_srl_ctl_code,
l_ib_flag
from mtl_system_items
where inventory_item_id = i.inventory_item_id
and organization_id = i.organization_id;
-- Update repair orders only for the following action codes
IF I.action_code in ( 'CUST_PROD','EXCHANGE','REPLACEMENT') then
-- Updating the repair order with qty
update csd_repairs
set quantity_shipped = nvl(quantity_shipped,0)+l_result_ship_qty,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = I.repair_line_id;
Select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = I.inventory_item_id
and serial_number = I.shipped_serial_num;
IF (l_enable_update_instance ='Y') THEN
Open cur_get_instance_id(I.line_id,I.inventory_item_id);
update csd_product_transactions
set sub_inventory = i.subinv,
lot_number = i.lot_number,
quantity_shipped = nvl(quantity_shipped,0)+I.shipped_quantity,
locator_id = i.locator_id,
source_serial_number = i.shipped_serial_num,
source_instance_id = l_instance_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where product_transaction_id = i.product_transaction_id ;
update csd_product_transactions
set prod_txn_status = 'SHIPPED',
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where nvl(quantity_shipped,0) = I.estimate_quantity
and product_transaction_id = i.product_transaction_id ;
and (l_enable_update_instance ='Y')) then
--do nothing
--due to csi_item_instances has not update the instance id yet
--It is update by the concurent program.
--if there is not instance id yet, we don't want to
--update the history table
null;
Debug('processing cancelled orders in SO_SHIP_UPDATE',l_mod_name,1);
Debug('At the end of processing cancelled orders in SO_SHIP_UPDATE',l_mod_name,1);
ROLLBACK TO SO_SHIP_UPDATE;
ROLLBACK TO SO_SHIP_UPDATE;
ROLLBACK TO SO_SHIP_UPDATE;
END SO_SHIP_UPDATE;
/* Procedure name: IO_SHIP_UPDATE */
/* Description : Procedure called from the Update api to update the depot tables */
/* for the shipment against Internal sales order */
/* It also logs activities for the deliver txn lines */
/* Called from : Called from SHIP_UPDATE API */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_header_id NUMBER Optional Interal sales order Id */
/* Output Parm : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
PROCEDURE IO_SHIP_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_order_header_id IN NUMBER
) IS
-- Cursor to get all the order lines for a
-- specific order header id
--Chnaged the cursor to remove the OR condition on the repair line id.
--Vijay 11/4/04
--saupadhy 04/15/05 : BUg# 4279958 : Problem: When partial Shipping is done for both
-- move_in and Move_Out lines,only first shiplines information is updated on logistics lines
-- subsequest so lines information is not updated.
-- Cause: Cursors Delivery_Lines and Delivery_lines_all have hard join with mtl_trx_lines table.
-- Records in this table are created only when so line is shipped. In partial shipping scenario, if
-- second line is not shipped at the time update logistics program is run then, it will never be
-- created after update logistics program is run for the first ship line.
-- Solution: Hard Join with Mtl_Trx_line is replaced with outer join.
CURSOR DELIVERY_LINES (p_ord_header_id in number) IS
Select oel.header_id,
oel.line_id,
oel.ordered_quantity,
oel.source_document_id req_header_id,
oel.source_document_line_id req_line_id,
oel.orig_sys_document_ref req_number,
oel.inventory_item_id,
oel.actual_shipment_date shipment_date,
dd.delivery_detail_id,
dd.shipped_quantity,
---changed to fix 3801614 , added dsn.serial_number if dd.serial_number is null
nvl(dd.serial_number,dsn.fm_serial_number) del_line_serial_num,
--dd.serial_number del_line_serial_num,
dd.lot_number,
dd.subinventory,
dd.locator_id,
dd.organization_id,
dd.released_status,
dd.requested_quantity,
dd.source_header_number order_number,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
mtl.serial_number_control_code,
mtl.lot_control_code,
prh.segment1 requisition_number,
hao.name source_org_name,
hao1.name destination_org_name,
trl.txn_source_id
from oe_order_lines_all oel,
wsh_delivery_details dd,
wsh_serial_numbers dsn,--Added to fix 3801614
po_requisition_lines_all prl,
po_requisition_headers_all prh,
mtl_system_items mtl,
hr_all_organization_units hao,
hr_all_organization_units hao1,
mtl_txn_request_lines trl
where oel.header_id = p_ord_header_id
and oel.line_id = dd.source_line_id
and oel.header_id = dd.source_header_id
and prl.requisition_header_id = prh.requisition_header_id
and hao.organization_id = dd.organization_id
and hao1.organization_id = prl.destination_organization_id
and oel.source_document_line_id = prl.requisition_line_id
and oel.ship_from_org_id = mtl.organization_id
and oel.inventory_item_id= mtl.inventory_item_id
and dd.move_order_line_id = trl.line_id(+) -- Added to fix 4279958
and dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
and exists (Select 'x'
from csd_product_transactions cpt
where cpt.order_header_id = oel.header_id
and cpt.prod_txn_status in ('BOOKED','RELEASED'));
Select oel.header_id,
oel.line_id,
oel.ordered_quantity,
oel.source_document_id req_header_id,
oel.source_document_line_id req_line_id,
oel.orig_sys_document_ref req_number,
oel.inventory_item_id,
oel.actual_shipment_date shipment_date,
dd.delivery_detail_id,
dd.shipped_quantity,
---changed to fix 3801614 , added dsn.serial_number if dd.serial_number is null
nvl(dd.serial_number,dsn.fm_serial_number) del_line_serial_num,
--dd.serial_number del_line_serial_num,
dd.lot_number,
dd.subinventory,
dd.locator_id,
dd.organization_id,
dd.released_status,
dd.requested_quantity,
dd.source_header_number order_number,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
mtl.serial_number_control_code,
mtl.lot_control_code,
prh.segment1 requisition_number,
hao.name source_org_name,
hao1.name destination_org_name,
trl.txn_source_id
from oe_order_lines_all oel,
wsh_delivery_details dd,
wsh_serial_numbers dsn,--Added to fix 3801614
po_requisition_lines_all prl,
po_requisition_headers_all prh,
mtl_system_items mtl,
hr_all_organization_units hao,
hr_all_organization_units hao1,
mtl_txn_request_lines trl
where oel.line_id = dd.source_line_id
and oel.header_id = dd.source_header_id
and prl.requisition_header_id = prh.requisition_header_id
and hao.organization_id = dd.organization_id
and hao1.organization_id = prl.destination_organization_id
and oel.source_document_line_id = prl.requisition_line_id
and oel.ship_from_org_id = mtl.organization_id
and oel.inventory_item_id= mtl.inventory_item_id
and dd.move_order_line_id = trl.line_id(+) -- Added to fix 4279958
and dd.delivery_detail_id = dsn.delivery_detail_id(+) --Added to fix 3801614
and exists (Select 'x'
from csd_product_transactions cpt
where cpt.order_header_id = oel.header_id
and cpt.prod_txn_status in ('BOOKED','RELEASED'));
select
mut.subinventory_code,
mut.locator_id,
mut.serial_number,
mtl.transaction_id
from mtl_material_transactions mtl,
mtl_unit_transactions mut
where mtl.transaction_id = mut.transaction_id
and mtl.transaction_source_type_id = 8 -- Internal Order
and mtl.transaction_type_id in (50, 62,54,34)
and mtl.picking_line_id = p_del_line_id
and mtl.transaction_source_id = p_txn_src_id ;
select
mut.subinventory_code,
mut.locator_id,
mut.serial_number,
mtl.transaction_id
from MTL_TRANSACTION_LOT_NUMBERS mln,
mtl_unit_transactions mut,
mtl_material_transactions mtl
WHERE MLN.SERIAL_TRANSACTION_ID = mut.transaction_id
and mln.transaction_id = mtl.transaction_id
and mtl.transaction_source_type_id = 8 -- Internal Order
and mtl.transaction_type_id in ( 50,62,54,34)
and mtl.picking_line_id = p_del_line_id
and mtl.transaction_source_id = p_txn_src_id ;
l_api_name CONSTANT VARCHAR2(30) := 'IO_SHIP_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.io_ship_update';
SAVEPOINT IO_SHIP_UPDATE;
Debug('At the Beginning of IO_SHIP_UPDATE',l_mod_name,1 );
select 'EXISTS'
into l_dummy
from oe_order_headers_all oeh,
po_requisition_headers_all prh
where oeh.source_document_id = prh.requisition_header_id
and oeh.header_id = p_order_header_id
and exists (select 'x'
from csd_product_transactions cpt
where cpt.order_header_id = oeh.header_id );
select action_type,
action_code
into l_action_type,
l_action_code
from csd_product_transactions
where order_header_id = DEL.header_id
and rownum = 1;
Select nvl(sum(quantity_shipped),0)
into l_total_shipped_qty
from csd_product_transactions
where action_type = 'MOVE_IN'
and action_code = 'DEFECTIVES'
and order_line_id = DEL.line_id
and order_header_id = DEL.header_id;
Select product_transaction_id,
repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions
where order_header_id = DEL.header_id
and order_line_id = DEL.line_id
and action_type = l_action_type
and action_code = l_action_code;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET quantity_shipped = nvl(quantity_shipped,0) + nvl(DEL.shipped_quantity,0),
sub_inventory = DEL.subinventory,
lot_number = DEL.lot_number,
locator_id = DEL.locator_id,
release_sales_order_flag = l_release_so_flag,
ship_sales_order_flag = l_ship_so_flag,
prod_txn_status = l_prod_txn_status,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = l_prod_txn_id;
-- If product txn does not exist then insert a product txn for the
-- split order line
-- Get the repair line id for the order header id
Begin
Select repair_line_id
into l_rep_line_id
from csd_product_transactions
where order_header_id = DEL.header_id
and action_type = l_action_type
and action_code = l_action_code
and rownum = 1;
Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
(px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
p_REPAIR_LINE_ID => l_rep_line_id,
p_ESTIMATE_DETAIL_ID => NULL,
p_ACTION_TYPE => l_action_type,
p_ACTION_CODE => l_action_code,
p_LOT_NUMBER => DEL.lot_number,
p_SUB_INVENTORY => DEL.subinventory,
p_INTERFACE_TO_OM_FLAG => 'Y',
p_BOOK_SALES_ORDER_FLAG => 'Y',
p_RELEASE_SALES_ORDER_FLAG => l_release_so_flag,
p_SHIP_SALES_ORDER_FLAG => l_ship_so_flag,
p_PROD_TXN_STATUS => l_prod_txn_status,
p_PROD_TXN_CODE => '',
p_LAST_UPDATE_DATE => sysdate,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_CREATED_BY => fnd_global.user_id,
p_LAST_UPDATE_LOGIN => null,
p_ATTRIBUTE1 => '',
p_ATTRIBUTE2 => '',
p_ATTRIBUTE3 => '',
p_ATTRIBUTE4 => '',
p_ATTRIBUTE5 => '',
p_ATTRIBUTE6 => '',
p_ATTRIBUTE7 => '',
p_ATTRIBUTE8 => '',
p_ATTRIBUTE9 => '',
p_ATTRIBUTE10 => '',
p_ATTRIBUTE11 => '',
p_ATTRIBUTE12 => '',
p_ATTRIBUTE13 => '',
p_ATTRIBUTE14 => '',
p_ATTRIBUTE15 => '',
p_CONTEXT => '',
p_OBJECT_VERSION_NUMBER => 1,
P_REQ_HEADER_ID => DEL.req_header_id,
P_REQ_LINE_ID => DEL.req_line_id,
P_ORDER_HEADER_ID => DEL.header_id,
P_ORDER_LINE_ID => DEL.line_id,
P_PRD_TXN_QTY_RECEIVED => 0,
P_PRD_TXN_QTY_SHIPPED => nvl(DEL.shipped_quantity,0),
P_SOURCE_SERIAL_NUMBER => '',
P_SOURCE_INSTANCE_ID => NULL,
P_NON_SOURCE_SERIAL_NUMBER => '',
P_NON_SOURCE_INSTANCE_ID => NULL,
P_LOCATOR_ID => DEL.locator_id,
P_SUB_INVENTORY_RCVD => '',
P_LOT_NUMBER_RCVD => '',
P_PICKING_RULE_ID => null,
P_PROJECT_ID => null,
P_TASK_ID => null,
P_UNIT_NUMBER => '');
fnd_message.set_name('CSD','CSD_PROD_TXN_INSERT_FAILED');
fnd_message.set_name('CSD','CSD_PROD_TXN_INSERT_FAILED');
Select product_transaction_id,
repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions
where order_header_id = DEL.header_id
and order_line_id = DEL.line_id
and action_type = l_action_type
and action_code = l_action_code
and rownum =1;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET quantity_shipped = l_qty_shipped,
release_sales_order_flag = l_release_so_flag,
ship_sales_order_flag = l_ship_so_flag,
prod_txn_status = l_prod_txn_status,
sub_inventory = DEL.subinventory,
lot_number = DEL.lot_number,
locator_id = DEL.locator_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE order_header_id = DEL.header_id
and order_line_id = DEL.line_id
and action_type = l_action_type
and action_code = l_action_code
and prod_txn_status in('BOOKED','RELEASED')
and rownum <= nvl(DEL.requested_quantity,0) ;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET quantity_shipped = l_qty_shipped,
order_line_id = DEL.line_id,
release_sales_order_flag = l_release_so_flag,
ship_sales_order_flag = l_ship_so_flag,
prod_txn_status = l_prod_txn_status,
sub_inventory = DEL.subinventory,
lot_number = DEL.lot_number,
locator_id = DEL.locator_id,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE order_header_id = DEL.header_id
and action_type = l_action_type
and action_code = l_action_code
and prod_txn_status in ('BOOKED','RELEASED')
and rownum <= nvl(DEL.requested_quantity,0);
Select nvl(sum(quantity),0)
into l_total_shipped_qty
from csd_repair_history
where event_code = 'PSI'
and paramn1 = DEL.delivery_detail_id;
Select product_transaction_id,
repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions
where order_header_id = DEL.header_id
and order_line_id = DEL.line_id
and action_type = l_action_type
and action_code = l_action_code;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET quantity_shipped = nvl(quantity_shipped,0) + nvl(DEL.shipped_quantity,0),
sub_inventory = DEL.subinventory,
lot_number = DEL.lot_number,
locator_id = DEL.locator_id,
release_sales_order_flag = l_release_so_flag,
ship_sales_order_flag = l_ship_so_flag,
prod_txn_status = l_prod_txn_status,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE product_transaction_id = l_prod_txn_id;
-- If product txn does not exist then insert a product txn for the
-- split order line
-- Get the repair line id for the order header id
Begin
Select repair_line_id
into l_rep_line_id
from csd_product_transactions
where order_header_id = DEL.header_id
and action_type = l_action_type
and action_code = l_action_code
and rownum = 1;
Debug('Calling CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW',l_mod_name,2);
CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
(px_PRODUCT_TRANSACTION_ID => l_prod_txn_id,
p_REPAIR_LINE_ID => l_rep_line_id,
p_ESTIMATE_DETAIL_ID => NULL,
p_ACTION_TYPE => l_action_type,
p_ACTION_CODE => l_action_code,
p_LOT_NUMBER => DEL.lot_number,
p_SUB_INVENTORY => DEL.subinventory,
p_INTERFACE_TO_OM_FLAG => 'Y',
p_BOOK_SALES_ORDER_FLAG => 'Y',
p_RELEASE_SALES_ORDER_FLAG => l_release_so_flag,
p_SHIP_SALES_ORDER_FLAG => l_ship_so_flag,
p_PROD_TXN_STATUS => l_prod_txn_status,
p_PROD_TXN_CODE => '',
p_LAST_UPDATE_DATE => sysdate,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_CREATED_BY => fnd_global.user_id,
p_LAST_UPDATE_LOGIN => null,
p_ATTRIBUTE1 => '',
p_ATTRIBUTE2 => '',
p_ATTRIBUTE3 => '',
p_ATTRIBUTE4 => '',
p_ATTRIBUTE5 => '',
p_ATTRIBUTE6 => '',
p_ATTRIBUTE7 => '',
p_ATTRIBUTE8 => '',
p_ATTRIBUTE9 => '',
p_ATTRIBUTE10 => '',
p_ATTRIBUTE11 => '',
p_ATTRIBUTE12 => '',
p_ATTRIBUTE13 => '',
p_ATTRIBUTE14 => '',
p_ATTRIBUTE15 => '',
p_CONTEXT => '',
p_OBJECT_VERSION_NUMBER => 1,
P_REQ_HEADER_ID => DEL.req_header_id,
P_REQ_LINE_ID => DEL.req_line_id,
P_ORDER_HEADER_ID => DEL.header_id,
P_ORDER_LINE_ID => DEL.line_id,
P_PRD_TXN_QTY_RECEIVED => 0,
P_PRD_TXN_QTY_SHIPPED => nvl(DEL.shipped_quantity,0),
P_SOURCE_SERIAL_NUMBER => '',
P_SOURCE_INSTANCE_ID => NULL,
P_NON_SOURCE_SERIAL_NUMBER => '',
P_NON_SOURCE_INSTANCE_ID => NULL,
P_LOCATOR_ID => DEL.locator_id,
P_SUB_INVENTORY_RCVD => '',
P_LOT_NUMBER_RCVD => '',
P_PICKING_RULE_ID => null,
P_PROJECT_ID => null,
P_TASK_ID => null,
P_UNIT_NUMBER => '');
fnd_message.set_name('CSD','CSD_PROD_TXN_INSERT_FAILED');
fnd_message.set_name('CSD','CSD_PROD_TXN_INSERT_FAILED');
Update csd_repairs
set quantity_shipped = nvl(quantity_shipped,0) + DEL.shipped_quantity,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = l_rep_line_id;
Select cpt.product_transaction_id,
cpt.repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions cpt,
csd_repairs cra
where cpt.order_header_id = DEL.Header_id
and cpt.action_type = 'MOVE_OUT'
and cpt.action_code = 'USABLES'
and cpt.prod_txn_status in ('BOOKED', 'RELEASED')
-- and cra.supercession_inv_item_id is null
and cpt.source_serial_number is null
and cpt.repair_line_id = cra.repair_line_id
and cra.serial_number = UT.serial_number ;
Update csd_product_transactions
set prod_txn_status = 'SHIPPED',
quantity_shipped = 1,
sub_inventory = DEL.subinventory,
locator_id = DEL.locator_id,
lot_number = DEL.lot_number,
release_sales_order_flag = 'Y',
ship_sales_order_flag = 'Y',
order_line_id = DEL.line_id,
source_serial_number = UT.serial_number,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where product_transaction_id = l_prod_txn_id;
Update csd_repairs
set quantity_shipped = 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = l_rep_line_id;
Select cpt.product_transaction_id,
cpt.repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions cpt,
csd_repairs cra
where cpt.repair_line_id = cra.repair_line_id
and cpt.order_header_id = DEL.header_id
and cpt.source_serial_number is null
and cpt.action_type = 'MOVE_OUT'
and cpt.action_code = 'USABLES'
and cpt.prod_txn_status in ('BOOKED','RELEASED')
and cra.serial_number not in
(select
mut.serial_number
from mtl_material_transactions mtl,
mtl_unit_transactions mut,
wsh_delivery_details wdd
where mtl.transaction_id = mut.transaction_id
and mtl.transaction_source_type_id = 8 -- Internal Order
and mtl.transaction_type_id in ( 50,62,54,34)
and mtl.transaction_source_id = DEL.txn_source_id
and mtl.picking_line_id = wdd.delivery_detail_id
and wdd.source_header_id = DEL.header_id)
and rownum = 1;
Update csd_product_transactions
set prod_txn_status = 'SHIPPED',
quantity_shipped = 1,
sub_inventory = DEL.subinventory,
locator_id = DEL.locator_id,
lot_number = DEL.lot_number,
release_sales_order_flag = 'Y',
ship_sales_order_flag = 'Y',
order_line_id = DEL.line_id,
source_serial_number = UT.serial_number,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where product_transaction_id = l_prod_txn_id;
Update csd_repairs
set quantity_shipped = 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = l_rep_line_id;
Select cpt.product_transaction_id,
cpt.repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions cpt,
csd_repairs cra
where cpt.repair_line_id = cra.repair_line_id
and cra.supercession_inv_item_id is null
and cpt.source_serial_number is null
and cra.serial_number = UT.serial_number
and cpt.action_type = 'MOVE_OUT'
and cpt.action_code = 'USABLES'
and cpt.prod_txn_status in ('BOOKED', 'RELEASED');
Update csd_product_transactions
set prod_txn_status = 'SHIPPED',
quantity_shipped = 1,
sub_inventory = DEL.subinventory,
locator_id = DEL.locator_id,
lot_number = DEL.lot_number,
release_sales_order_flag = 'Y',
ship_sales_order_flag = 'Y',
order_line_id = DEL.line_id,
source_serial_number = UT.serial_number,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where product_transaction_id = l_prod_txn_id;
Update csd_repairs
set quantity_shipped = 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = l_rep_line_id;
Select cpt.product_transaction_id,
cpt.repair_line_id
into l_prod_txn_id,
l_rep_line_id
from csd_product_transactions cpt,
csd_repairs cra
where cpt.repair_line_id = cra.repair_line_id
and cpt.order_header_id = DEL.header_id
and cpt.source_serial_number is null
and cpt.action_type = 'MOVE_OUT'
and cpt.action_code = 'USABLES'
and cpt.prod_txn_status in ('BOOKED','RELEASED')
and cra.serial_number not in
(select
mut.serial_number
from mtl_material_transactions mtl,
mtl_unit_transactions mut,
wsh_delivery_details wdd
where mtl.transaction_id = mut.transaction_id
and mtl.transaction_source_type_id = 8 -- Internal Order
and mtl.transaction_type_id in ( 50,62,54,34)
and mtl.transaction_source_id = DEL.txn_source_id
and mtl.picking_line_id = wdd.delivery_detail_id
and wdd.source_header_id = DEL.header_id)
and rownum = 1;
Update csd_product_transactions
set prod_txn_status = 'SHIPPED',
quantity_shipped = 1,
sub_inventory = DEL.subinventory,
locator_id = DEL.locator_id,
lot_number = DEL.lot_number,
release_sales_order_flag = 'Y',
ship_sales_order_flag = 'Y',
order_line_id = DEL.line_id,
source_serial_number = UT.serial_number,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where product_transaction_id = l_prod_txn_id;
Update csd_repairs
set quantity_shipped = 1,
object_version_number = object_version_number+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where repair_line_id = l_rep_line_id;
ROLLBACK TO IO_SHIP_UPDATE;
ROLLBACK TO IO_SHIP_UPDATE;
ROLLBACK TO IO_SHIP_UPDATE;
END IO_SHIP_UPDATE;
/* Procedure name: WIP_UPDATE */
/* Description : Procedure called from the UI to update the depot tables */
/* for the WIP Job creation/Completion */
/* */
/* Called from : Called from Depot Repair UI */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional To Initialize message stack */
/* p_commit VARCHAR2 Optional Commits in API */
/* p_validation_level NUMBER Optional validation level */
/* */
/* Output Parameters: */
/* x_return_status VARCHAR2 Return status of the API */
/* x_msg_count NUMBER Number of messages in stack */
/* x_msg_data VARCHAR2 Error Message from message stack */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_upd_job_completion Required Order Type; Possible values -'Y','N' */
Procedure WIP_UPDATE
( p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_upd_job_completion IN VARCHAR2,
p_repair_line_id IN NUMBER
) IS
-- Standard Variables
l_api_name CONSTANT VARCHAR2(30) := 'WIP_UPDATE';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.wip_update';
SAVEPOINT WIP_UPDATE;
Debug('At the Beginning of Wip_update ',l_mod_name,1);
Debug('Calling JOB_CREATION_UPDATE',l_mod_name,2);
JOB_CREATION_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_repair_line_id => p_repair_line_id);
Debug('Return Status from JOB_CREATION_UPDATE :'||x_return_status,l_mod_name,2);
Debug(' JOB_CREATION_UPDATE failed ',l_mod_name,4);
Debug('Calling JOB_COMPLETION_UPDATE',l_mod_name,2);
JOB_COMPLETION_UPDATE
( p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_repair_line_id => p_repair_line_id);
Debug('Return Status from JOB_COMPLETION_UPDATE :'||x_return_status,l_mod_name,2);
Debug(' JOB_COMPLETION_UPDATE failed ',l_mod_name,4);
ROLLBACK TO WIP_UPDATE;
ROLLBACK TO WIP_UPDATE;
ROLLBACK TO WIP_UPDATE;
End WIP_UPDATE;
/* Procedure name: RECEIPTS_UPDATE_CONC_PROG */
/* Description : Procedure called by concurrent program to update the depot tables */
/* for the receipts against RMA/Internal Requisitions */
/* */
/* Called from : Called from Receipt update concurrent program */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* errbuf VARCHAR2 Error message */
/* retcode VARCHAR2 Error Code */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_type VARCHAR2 Required Order Type; Possible values- 'I','E' */
Procedure RECEIPTS_UPDATE_CONC_PROG
(errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_order_type IN varchar2,
p_order_header_id IN number,
p_repair_line_id IN number,
p_past_num_of_days IN NUMBER DEFAULT NULL) ----bug#6753684, 6742512
IS
--Standard Variables
l_api_name CONSTANT VARCHAR2(30) := 'RECEIPTS_UPDATE_CONC_PROG';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.receipts_update_conc_prog';
Debug('At the Beginning of receipts_update_conc_prog',l_mod_name,1);
Debug('Calling RECEIPTS_UPDATE',l_mod_name,2);
RECEIPTS_UPDATE
( p_api_version => l_api_version,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => l_internal_order_flag,
p_order_header_id => p_order_header_id,
p_repair_line_id => p_repair_line_id,
p_past_num_of_days => p_past_num_of_days);
Debug('Return Status from RECEIPTS_UPDATE :'||l_return_status,l_mod_name,2);
END RECEIPTS_UPDATE_CONC_PROG;
/* Procedure name: WIP_UPDATE_CONC_PROG */
/* Description : Procedure called by concurrent program to update the depot tables */
/* for the WIP Job Creation/ Completion */
/* */
/* Called from : Called from Wip Update Concurrent Program */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* errbuf VARCHAR2 Error message */
/* retcode VARCHAR2 Error Code */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_upd_job_completion Required Order Type; Possible values -'Y','N' */
Procedure WIP_UPDATE_CONC_PROG
(errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_repair_line_id IN number,
p_upd_job_completion IN varchar2) IS
-- Standard variables
l_api_name CONSTANT VARCHAR2(30) := 'WIP_UPDATE_CONC_PROG';
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.wip_update_conc_prog';
Debug('At the Beginning of wip_update_conc_prog',l_mod_name,1);
Debug('Calling WIP_UPDATE',l_mod_name,2);
WIP_UPDATE
( p_api_version => l_api_version,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_upd_job_completion => p_upd_job_completion,
p_repair_line_id => p_repair_line_id);
Debug('Return Status from WIP_UPDATE :'||l_return_status,l_mod_name,2);
END WIP_UPDATE_CONC_PROG;
/* Procedure name: SHIP_UPDATE_CONC_PROG */
/* Description : Procedure called by concurrent program to update the depot tables */
/* for the shipment against Sales order/Internal Sales Order */
/* */
/* Called from : Called from Receipt update concurrent program */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* errbuf VARCHAR2 Error message */
/* retcode VARCHAR2 Eeror Code */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_order_type VARCHAR2 Required Order Type; Possible values- 'I','E' */
PROCEDURE SHIP_UPDATE_CONC_PROG
(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_order_type IN VARCHAR2,
p_order_header_id IN NUMBER,
p_repair_line_id IN NUMBER,
p_past_num_of_days IN NUMBER DEFAULT NULL) ----bug#6753684, 6742512
IS
-- Standard Variables
l_api_version CONSTANT NUMBER := 1.0;
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.ship_update_conc_prog';
Debug('Beginning of ship_update_conc_prog ',l_mod_name,1);
Debug('Calling SHIP_UPDATE ',l_mod_name,2);
SHIP_UPDATE
( p_api_version => l_api_version,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => l_internal_order_flag,
p_order_header_id => p_order_header_id,
p_repair_line_id => p_repair_line_id,
p_past_num_of_days => p_past_num_of_days);
Debug('Return Status from SHIP_UPDATE :'||l_return_status,l_mod_name,2);
End SHIP_UPDATE_CONC_PROG;
/* Procedure name: TASK_UPDATE_CONC_PROG */
/* Description : Procedure called by concurrent program to update the depot tables */
/* for the task creation and update */
/* */
/* */
/* Called from : Called from Task Update concurrent program */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* errbuf VARCHAR2 Error message */
/* retcode VARCHAR2 Error Code */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER Optional Repair Order Line Id */
/* Output Parm : */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
PROCEDURE TASK_UPDATE_CONC_PROG
( errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_repair_line_id IN number ) is
CURSOR c_updated_tasks( p_repair_line_id in 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);
l_mod_name varchar2(2000) := 'csd.plsql.csd_update_programs_pvt.task_update_conc_prog';
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;
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
);
Debug(' JOB_COMPLETION_UPDATE failed ',l_mod_name,4);
ROLLBACK TO Update_rep_task_hist;
ROLLBACK TO Update_rep_task_hist;
ROLLBACK TO Update_rep_task_hist;
END TASK_UPDATE_CONC_PROG;
/* Description : Procedure called to update all the logistics lines status to booked */
/* */
/* */
/* Called from : csd_process_pvt and CSDREPLN.pld */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER */
/* p_commit VARCHAR2 */
/* Output Parm : */
/* Change Hist : */
/* 12/20/04 mshirkol Initial Creation. Fix for bug#4020651 */
/*-------------------------------------------------------------------------------------*/
PROCEDURE PROD_TXN_STATUS_UPD(p_repair_line_id in number,
p_commit in varchar2)
is
CURSOR c_product_transaction_id(p_repair_line_id IN number) is
SELECT c.product_transaction_id, a.booked_flag
FROM oe_order_lines_all a,
cs_estimate_details b,
csd_product_transactions c
WHERE a.line_id = b.order_line_id
AND b.estimate_detail_id = c.estimate_detail_id
and c.prod_txn_status = 'SUBMITTED'
and a.booked_flag = 'Y'
and c.book_sales_order_flag = 'N'
and b.order_header_id in
(
select p.order_header_id
from cs_estimate_details p, csd_product_transactions q
where p.estimate_detail_id=q.estimate_detail_id
and q.repair_line_id=p_repair_line_id
);
UPDATE csd_product_transactions
SET prod_txn_status = 'BOOKED', book_sales_order_flag = 'Y'
WHERE product_transaction_id = C.product_transaction_id;
/* Description : Procedure called to update all the logistics lines status to */
/* cancelled if the corresponding order line is cancelled. */
/* */
/* */
/* Called from : RMA_RCV_UPDATE, IO_RCV_UPDATE , SHIP_UPDATE, IO_SHIP_UPDATE */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* */
/* Output Parameters: */
/* */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* p_repair_line_id NUMBER */
/* Output Parm : */
/* Change Hist : */
/* 30/June/2005 vparvath Initial Creation. */
/*-------------------------------------------------------------------------------------*/
PROCEDURE check_for_cancelled_order(p_repair_line_id NUMBER) IS
CURSOR CANCELLED_ORDER_LINES(p_repair_line_id NUMBER) IS
SELECT DISTINCT cpt.product_transaction_id PRODUCT_TXN_ID
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
cs_estimate_details ced,
csd_product_transactions cpt
WHERE cpt.repair_line_id = p_repair_line_id
AND cpt.action_type in ('RMA', 'SHIP', 'RMA_THIRD_PTY', 'SHIP_THIRD_PTY')
AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
AND ced.order_header_id is not null
AND ced.source_code = 'DR'
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND oeh.header_id = ced.order_header_id
AND oel.header_id = oeh.header_id
and ced.order_line_id = oel.line_id
/*fixed for bug#5846050 only cancelled line should be updated not all the lines */
--AND oel.cancelled_flag = 'Y'
AND oel.ordered_quantity = 0 -- indicates the order line is cancelled.
; -- skip partial ship/receive case, current behavour is
AND oel.line_id in ( Select line_id
from oe_order_lines_all oel1
start with oel1.line_id = ced.order_line_id
connect by prior oel1.line_id = oel1.split_from_line_id
and oel1.shipped_quantity is not null
and oel1.header_id = oeh.header_id))
OR (ced.QUANTITY_REQUIRED = -1
AND ced.ORDER_LINE_ID = oel.LINE_ID));
UPDATE CSD_PRODUCT_TRANSACTIONS
SET PROD_TXN_STATUS = C_PRODTXN_CANCELLED,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE PRODUCT_TRANSACTION_ID = l_product_txn_id;
/* Procedure name: UPDATE_LOGISTIC_STATUS_WF */
/* Description : Procedure called from workflow process to update logistics */
/* line status */
/* */
/* Called from : Workflow */
/* PARAMETERS */
/* IN */
/* */
/* itemtype - type of the current item */
/* itemkey - key of the current item */
/* actid - process activity instance id */
/* funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...) */
/* OUT */
/* result */
/* - COMPLETE[:] */
/* activity has completed with the indicated result */
/* - WAITING */
/* activity is waiting for additional transitions */
/* - DEFERED */
/* execution should be defered to background */
/* - NOTIFIED[::] */
/* activity has notified an external entity that this */
/* step must be performed. A call to wf_engine.CompleteActivty */
/* will signal when this step is complete. Optional */
/* return of notification ID and assigned user. */
/* - ERROR[:] */
/* function encountered an error. */
/* Change Hist : */
/* 04/18/06 mshirkol Initial Creation. ( Fix for bug#5610891 ) */
/*-------------------------------------------------------------------------------------*/
Procedure UPDATE_LOGISTIC_STATUS_WF
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out nocopy varchar2) IS
l_line_id number;
select
est.original_source_code,
est.original_source_id,
est.line_category_code,
est.order_header_id
from
cs_estimate_details est
where
est.order_line_id = p_line_id;
l_module_name := 'LOGISTICS_RECEIPTS_UPDATE';
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
( p_api_version => 1.0,
p_commit => 'T',
p_init_msg_list => 'T',
p_validation_level => CSD_PROCESS_UTIL.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => null,
p_repair_line_id => l_orig_source_id);
l_module_name := 'LOGISTICS_SHIP_UPDATE';
CSD_UPDATE_PROGRAMS_PVT.SHIP_UPDATE
( p_api_version => 1.0,
p_commit => 'T',
p_init_msg_list => 'T',
p_validation_level => CSD_PROCESS_UTIL.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N' ,
p_order_header_id => null,
p_repair_line_id => l_orig_source_id);
WF_CORE.CONTEXT('CSD_UPDATE_PROGRAMS_PVT','UPDATE_LOGISTICS_WF',itemtype,
itemkey,to_char(actid),funcmode);