[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Completion_UOM(p_item_id in number, p_org_id in number,
p_txn_qty in number, p_txn_uom in varchar2,
p_pri_qty in out nocopy number) is
x_primary_uom VARCHAR2(3);
Select PRIMARY_UOM_CODE INTO x_primary_uom
FROM mtl_system_items
WHERE inventory_item_Id = p_item_id
AND organization_Id = p_org_id;
end Update_Completion_UOM ;
Select
INVENTORY_ITEM_ID,
SUBSTITUTION_ITEM_ID,
SUBSTITUTION_TYPE_ID,
OPERATION_SEQ_NUM,
FLOW_SCHEDULE
from Mtl_transactions_interface
where parent_id = p_parent_id
and substitution_type_id is not null
and process_flag = 2
and organization_id = p_organization_Id ;
select operation_seq_num
into x_parent_op_seq
from mtl_transactions_interface
where transaction_interface_id = p_parent_id;
SELECT mti.routing_revision_date, bor.routing_sequence_id -- CFM Scrap
INTO x_rout_rev_date, x_rout_seq_id
FROM mtl_transactions_interface mti, bom_operational_routings bor
WHERE mti.transaction_interface_id = p_parent_id
AND mti.inventory_item_id = bor.assembly_item_id(+)
AND mti.organization_id = bor.organization_id(+)
AND Nvl(mti.alternate_routing_designator,'@@@@') = Nvl(bor.alternate_routing_designator,'@@@@');
select 1 into x_success
from sys.dual
where Sub_Rec.substitution_type_id in (
select lookup_code from mfg_lookups
where lookup_type = 'WIP_SUBSTITUTION_TYPE') ;
select 1 into x_success
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = Sub_Rec.inventory_item_id
and mtl_transactions_enabled_flag = 'Y'
and inventory_item_flag = 'Y'
and bom_enabled_flag = 'Y'
and eng_item_flag = decode(x_see_eng_item,
1, eng_item_flag,
'N')
and bom_item_type = 4 ; -- Standard Type
select 1 into x_success
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = Sub_Rec.substitution_item_id
and mtl_transactions_enabled_flag = 'Y'
and inventory_item_flag = 'Y'
and bom_enabled_flag = 'Y'
and eng_item_flag = decode(x_see_eng_item,
1, eng_item_flag,
'N')
and sub_rec.substitution_type_id in (1,3)
and bom_item_type = 4 ; -- Standard Type
select 1 into x_success
from wip_flow_schedules wfs,
mtl_transactions_interface mti
where wfs.wip_entity_id = p_txn_src_id
and wfs.organization_id = p_organization_id
and trunc(wfs.scheduled_completion_date,WIP_CONSTANTS.DATE_FMT) =
trunc(mti.transaction_date,WIP_CONSTANTS.DATE_FMT)
and mti.rowid = p_rowid;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = NULL,
ERROR_EXPLANATION = x_message
WHERE rowid = p_rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = NULL,
ERROR_EXPLANATION = x_message
WHERE rowid = p_rowid ;
UPDATE mtl_transactions_interface MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
REVISION =
(SELECT NVL(mti.revision, MAX(mir.revision))
FROM mtl_item_revisions mir
WHERE mir.organization_id = mti.organization_id
AND mir.inventory_item_id = mti.inventory_item_id
AND mir.effectivity_date <=
mti.transaction_date
AND mir.effectivity_date =
(SELECT MAX(mir2.effectivity_date)
FROM mtl_item_revisions mir2
WHERE mir2.organization_id = mti.organization_id
AND mir2.inventory_item_id = mti.inventory_item_id
AND mir2.effectivity_date <=
mti.transaction_date
))
WHERE
PARENT_ID = p_interface_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND FLOW_SCHEDULE = 'Y'
AND TRANSACTION_ACTION_ID IN (1,27,33,34)
AND PROCESS_FLAG = 2
AND REVISION IS NULL
AND EXISTS (
SELECT 'X'
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.ORGANIZATION_ID = mti.ORGANIZATION_ID
AND msi.inventory_item_id = mti.inventory_item_id
AND msi.revision_qty_control_code = 2);
SELECT
transaction_interface_id,
transaction_action_id, -- CFM Scrap
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
accounting_class,
transaction_date,
transaction_quantity, -- we have to convert it to primary qty
transaction_uom,
primary_quantity,
transaction_source_id,
transaction_source_name,
revision,
bom_revision,
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
subinventory_code,
locator_id,
demand_class,
schedule_group,
build_sequence,
repetitive_line_id,
source_project_id,
project_id,
source_task_id,
task_id,
schedule_number,
scheduled_flag,
wip_entity_type,
end_item_unit_number,
rowid
FROM mtl_transactions_interface
WHERE transaction_header_id = header_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND FLOW_SCHEDULE = 'Y'
AND transaction_action_id in (31, 32, 30) -- CFM Scrap
AND scheduled_flag in (1, 2, 3) -- Unscheduled from form and interface
AND process_flag = 1;
Update Mtl_Transactions_Interface
Set
transaction_source_id = flow_rec.transaction_source_id,
schedule_number = flow_rec.schedule_number,
source_project_id = flow_rec.source_project_id,
project_id = flow_rec.project_id,
source_task_id = flow_rec.source_task_id,
task_id = flow_rec.task_id,
bom_revision = flow_rec.bom_revision,
revision = flow_rec.revision,
bom_revision_date = flow_rec.bom_revision_date,
routing_revision = flow_rec.routing_revision,
routing_revision_date = flow_rec.routing_revision_date,
subinventory_code = flow_rec.subinventory_code,
accounting_class = flow_rec.accounting_class,
wip_entity_type = flow_rec.wip_entity_type,
locator_id = flow_rec.locator_id
Where
rowid = flow_rec.rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = NULL,
ERROR_EXPLANATION = x_message
WHERE rowid = flow_rec.rowid ;
Update_Completion_UOM(
p_item_id => flow_rec.inventory_item_id,
p_org_id => flow_rec.organization_id,
p_txn_qty => flow_rec.transaction_quantity,
p_txn_uom => flow_rec.transaction_uom,
p_pri_qty => flow_rec.primary_quantity);
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = NULL,
ERROR_EXPLANATION = x_message
WHERE rowid = flow_rec.rowid ;
Select wip_entities_s.nextval into x_wip_entity_id
from dual ;
flow_rec.last_update_date,
flow_rec.last_updated_by,
flow_rec.creation_date,
flow_rec.created_by,
flow_rec.last_update_login,
flow_rec.request_id,
flow_rec.program_application_id,
flow_rec.program_id,
flow_rec.program_update_date,
flow_rec.inventory_item_id,
flow_rec.accounting_class,
flow_rec.transaction_date,
NULL,
0,
0, -- We have to insert it as the primary_quantity
0, -- CFM Scrap
NULL,
NULL,
flow_rec.bom_revision,
flow_rec.routing_revision,
flow_rec.bom_revision_date,
flow_rec.routing_revision_date,
flow_rec.alternate_bom_designator,
flow_rec.alternate_routing_designator,
flow_rec.subinventory_code,
flow_rec.locator_id, -- actually this will be validated by INV proc
flow_rec.demand_class,
flow_rec.transaction_date,
flow_rec.schedule_group,
flow_rec.build_sequence,
flow_rec.repetitive_line_id,
flow_rec.project_id,
flow_rec.task_id,
1, -- 1. Open, 2. Close
flow_rec.schedule_number,
2, -- Unscheduled
flow_rec.end_item_unit_number, -- end item unit number
NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL );
Update Mtl_transactions_interface
set transaction_source_id = wip_entities_s.currval,
scheduled_flag = 2, -- No
schedule_number = flow_rec.schedule_number,
primary_quantity = flow_rec.primary_quantity
where rowid = flow_rec.rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = x_message,
ERROR_EXPLANATION = decode(x_sql_err_num,0,NULL,x_sql_message)
WHERE rowid = flow_rec.rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = x_message,
ERROR_EXPLANATION = decode(x_sql_err_num,0,NULL,x_sql_message)
WHERE transaction_header_id = p_header_id
and transaction_source_type_id = 5
and transaction_action_id in (31, 32, 30); -- CFM Scrap
p_last_update_date in date,
p_last_updated_by in number,
p_creation_date in date,
p_created_by in number,
p_last_update_login in number,
p_request_id in number,
p_program_application_id in number,
p_program_id in number,
p_program_update_date in date,
p_primary_item_id in number,
p_class_code in varchar2,
p_scheduled_start_date in date,
p_date_closed in date,
p_planned_quantity in number,
p_quantity_completed in number,
p_quantity_scrapped in number, -- CFM Scrap
p_mps_sched_comp_date in date,
p_mps_net_quantity in number,
p_bom_revision in varchar2,
p_routing_revision in varchar2,
p_bom_revision_date in date,
p_routing_revision_date in date,
p_alternate_bom_designator in varchar2,
p_alternate_routing_designator in varchar2,
p_completion_subinventory in varchar2,
p_completion_locator_id in number,
p_demand_class in varchar2,
p_scheduled_completion_date in date,
p_schedule_group_id in number,
p_build_sequence in number,
p_line_id in number,
p_project_id in number,
p_task_id in number,
p_status in number,
p_schedule_number in varchar2,
p_scheduled_flag in number,
p_unit_number IN VARCHAR2,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2 ) return number is
x_material_account number;
select material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account
into
x_material_account,
x_material_overhead_account,
x_resource_account,
x_outside_processing_account,
x_material_variance_account,
x_resource_variance_account,
x_outside_proc_var_account,
x_std_cost_adjustment_account,
x_overhead_account,
x_overhead_variance_account
from
wip_accounting_classes
where
class_code = p_class_code
and organization_id = p_organization_id;
Insert into wip_flow_schedules(
WIP_ENTITY_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PRIMARY_ITEM_ID,
CLASS_CODE,
SCHEDULED_START_DATE,
DATE_CLOSED,
PLANNED_QUANTITY,
QUANTITY_COMPLETED,
QUANTITY_SCRAPPED, -- CFM Scrap
MPS_SCHEDULED_COMPLETION_DATE,
MPS_NET_QUANTITY,
BOM_REVISION,
ROUTING_REVISION,
BOM_REVISION_DATE,
ROUTING_REVISION_DATE,
ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR,
COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
MATERIAL_VARIANCE_ACCOUNT,
RESOURCE_VARIANCE_ACCOUNT,
OUTSIDE_PROC_VARIANCE_ACCOUNT,
STD_COST_ADJUSTMENT_ACCOUNT,
OVERHEAD_ACCOUNT,
OVERHEAD_VARIANCE_ACCOUNT,
DEMAND_CLASS,
SCHEDULED_COMPLETION_DATE,
SCHEDULE_GROUP_ID,
BUILD_SEQUENCE,
LINE_ID,
PROJECT_ID,
TASK_ID,
STATUS,
SCHEDULE_NUMBER,
SCHEDULED_FLAG,
END_ITEM_UNIT_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES (
p_wip_entity_id,
p_organization_id,
p_last_update_date,
p_last_updated_by,
p_creation_date,
p_created_by,
p_last_update_login,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_primary_item_id,
p_class_code,
p_scheduled_start_date,
p_date_closed,
p_planned_quantity,
p_quantity_completed,
p_quantity_scrapped, -- CFM Scrap
p_mps_sched_comp_date,
p_mps_net_quantity,
p_bom_revision,
p_routing_revision,
p_bom_revision_date,
p_routing_revision_date,
p_alternate_bom_designator,
p_alternate_routing_designator,
p_completion_subinventory,
p_completion_locator_id,
x_material_account,
x_material_overhead_account,
x_resource_account,
x_outside_processing_account,
x_material_variance_account,
x_resource_variance_account,
x_outside_proc_var_account,
x_std_cost_adjustment_account,
x_overhead_account,
x_overhead_variance_account,
p_demand_class,
p_scheduled_completion_date,
p_schedule_group_id,
p_build_sequence,
p_line_id,
p_project_id,
p_task_id,
p_status,
p_schedule_number,
p_scheduled_flag,
p_unit_number,
p_attribute_category,
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) ;
procedure Delete_Flow_Schedules( p_header_id in number ) is
-- **********************************************
-- Cursor to get all the Failed Flow Completions
-- **********************************************
CURSOR Del_Flow(header_id number) is
SELECT transaction_interface_id, rowid,
transaction_source_id, organization_Id
FROM mtl_transactions_interface
WHERE transaction_header_id = header_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND FLOW_SCHEDULE = 'Y'
AND scheduled_flag <> 1
AND transaction_action_id in (31, 32, 30) -- CFM Scrap
AND process_flag = 3;
delete wip_flow_schedules
where wip_entity_id = del_rec.transaction_source_id
and organization_id = del_rec.organization_id ;
update mtl_transactions_interface
set transaction_source_id = NULL
where rowid = del_rec.rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = NULL,
ERROR_EXPLANATION = x_message
WHERE rowid = del_rec.rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = NULL,
ERROR_EXPLANATION = x_message
WHERE transaction_header_id = p_header_id
and transaction_source_type_id = 5
and transaction_action_id in (31, 32);
end Delete_Flow_Schedules ;
procedure Delete_Flow_Schedule( p_wip_entity_id in number ) is
begin
delete wip_flow_schedules
where wip_entity_id = p_wip_entity_id ;
* The Error Message in this case is handled by the calling Delete_Flow_Schedules *
**********************************************************************************/
null ;
end Delete_Flow_Schedule ;
It's a function called by Update_Flow_Schedule.
This function determines if the schedule need to open / close :
- 0 means : no change
- 1 means : reopen the schedule
- 2 means : close the schedule
*/
function Status_Change(p_planned_qty number,
p_cur_completed_qty number,
p_qty_completed number) return number is
l_new_completed_qty number;
function Update_Flow_Schedule( p_wip_entity_id in number,
p_quantity_completed in number, -- CFM Scrap (primary qty)
p_quantity_scrapped IN NUMBER, -- CFM Scrap (primary qty)
p_transaction_date in date,
p_schedule_flag in varchar2,
p_last_updated_by number ) return number is
begin
-- This has to atleast perform the following functions
-- 1. Update the Completed Quantity (or the scrapped quantity)
-- (the sign is changed to handle it from WIP perspective.)
-- 2. Completion_Date
-- 3. set the status flag
-- ----------------------------------------------------
-- Set the DATE_CLOSED and STATUS based on :
-- 1. NVL(p_schedule_flag,'N') = 'Y'
-- 2. Status_Change() returns value
Update wip_flow_schedules
set QUANTITY_COMPLETED = Nvl(QUANTITY_COMPLETED,0)+(p_quantity_completed * -1), -- CFM Scrap
QUANTITY_SCRAPPED = Nvl(QUANTITY_SCRAPPED,0)+(p_quantity_scrapped * -1), -- CFM Scrap
TRANSACTED_FLAG = 'Y',
DATE_CLOSED =
decode(UPPER(NVL(p_schedule_flag,'N')), 'Y',
decode(Wip_Flow_Utilities.Status_Change(PLANNED_QUANTITY,QUANTITY_COMPLETED,p_quantity_completed*-1),
0,DATE_CLOSED,1,null,2,p_transaction_date),
DATE_CLOSED),
STATUS =
decode(UPPER(NVL(p_schedule_flag,'N')), 'Y',
decode(Wip_Flow_Utilities.Status_Change(PLANNED_QUANTITY,QUANTITY_COMPLETED,p_quantity_completed*-1),
0,STATUS,1,1,2,2),
STATUS),
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_DATE = sysdate
where wip_entity_id = p_wip_entity_id ;
* The Error Message in this case is handled by the calling Delete_Flow_Schedules *
**********************************************************************************/
return 0;
end Update_Flow_Schedule ;
select MTL_MATERIAL_TRANSACTIONS_S.nextval into x_new_txn_hdr
from sys.dual ;
Update Mtl_transactions_interface
set TRANSACTION_HEADER_ID = x_new_txn_hdr,
PROCESS_FLAG = 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
PROGRAM_APPLICATION_ID = p_appl_id,
PROGRAM_ID = p_prog_id,
REQUEST_ID = p_reqstid,
PROGRAM_UPDATE_DATE = SYSDATE,
LOCK_FLAG = 1,
ERROR_CODE = NULL,
ERROR_EXPLANATION = NULL,
TRANSACTION_MODE = 3
where
parent_id = p_interface_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND FLOW_SCHEDULE = 'Y'
AND TRANSACTION_ACTION_ID IN (1,27,33,34)
AND PROCESS_FLAG = 2
AND NVL(LOCK_FLAG,2) = 2;
Update Mtl_transactions_interface
set TRANSACTION_HEADER_ID = p_hdr_id
where transaction_interface_id = p_interface_id ;
Update Mtl_transactions_interface
set PROCESS_FLAG = 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
PROGRAM_APPLICATION_ID = p_appl_id,
PROGRAM_ID = p_prog_id,
REQUEST_ID = p_reqstid,
PROGRAM_UPDATE_DATE = SYSDATE,
LOCK_FLAG = 1,
ERROR_CODE = NULL,
ERROR_EXPLANATION = NULL
where
parent_id = p_interface_id
and transaction_header_id = p_hdr_id;
x_last_updated_by number ; /* Fix for Bug#2517396 */
SELECT DISTINCT
transaction_source_id,
Decode( transaction_action_id, 30, 0, (primary_quantity)*-1),-- CFM Scrap
Decode( transaction_action_id, 30, (primary_quantity)*-1, 0),-- CFM Scrap
transaction_date,
flow_schedule,
last_updated_by
into x_wip_entity_id,
x_cpl_qty, -- CFM Scrap
x_scr_qty, -- CFM Scrap
x_txn_date,
x_flow_schedule,
x_last_updated_by
from mtl_material_transactions
where transaction_set_id = p_header_id
and transaction_action_id in (31, 32, 30); -- CFM Scrap
x_success := Update_Flow_Schedule( x_wip_entity_id, x_cpl_qty, x_scr_qty, -- CFM Scrap
x_txn_date, x_flow_schedule, x_last_updated_by) ;
DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID IN (
SELECT MTI.TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRANSACTION_HEADER_ID = p_header_id
AND MTI.PROCESS_FLAG = 1);
DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE MSNI
WHERE TRANSACTION_INTERFACE_ID IN (
SELECT MTLI.SERIAL_TRANSACTION_TEMP_ID
FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRANSACTION_INTERFACE_ID =
MTLI.TRANSACTION_INTERFACE_ID
AND MTI.TRANSACTION_HEADER_ID = p_header_id
AND MTI.PROCESS_FLAG = 1);
DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID IN (
SELECT MTI.TRANSACTION_INTERFACE_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRANSACTION_HEADER_ID = p_header_id
AND MTI.PROCESS_FLAG = 1);
DELETE FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = p_header_id
AND PROCESS_FLAG = 1;
select ERROR_EXPLANATION
into l_err_mesg
from MTL_TRANSACTIONS_INTERFACE
where PROCESS_FLAG = 3
AND LOCK_FLAG = 2
AND TRANSACTION_HEADER_ID = p_header_id
AND ROWNUM <2;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(p_err_mesg,1,240),
ERROR_EXPLANATION = substrb(p_err_mesg,1,240)
WHERE (TRANSACTION_INTERFACE_ID = p_txn_int_id
or parent_id = p_txn_int_id)
AND process_flag <> 3 ;
select 1 into l_unsched
from wip_flow_schedules
where wip_entity_id = p_wip_entity_id
and scheduled_flag <> 1;
Delete_Flow_Schedule(p_wip_entity_id);
SELECT operation_seq_num
INTO l_terminal_op_seq_num
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_sequence_id = p_terminal_op_seq_id;
wip_line_ops.delete;
deleted. So set it to Y only if you are calling this function once. However if you
are calling it over and over again with the same p_line_op_seq2_id, it makes more
sense to set the destroy_cache to N because creating this table of records can be
expensive. */
FUNCTION Line_Op_same_or_prior(p_routing_sequence_id IN NUMBER,
p_eff_date IN DATE,
p_line_op_seq1_id IN NUMBER,
p_line_op_seq1_num IN NUMBER,
p_line_op_seq2_id IN NUMBER,
p_line_op_seq2_num IN NUMBER,
p_destroy_cache IN VARCHAR2) RETURN NUMBER
IS
l_success NUMBER := 0;
SELECT operation_sequence_id
INTO l_line_op_seq2_id
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_seq_num = p_line_op_seq2_num
AND operation_type = 3;
SELECT operation_sequence_id
INTO l_line_op_seq1_id
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_seq_num = p_line_op_seq1_num
AND operation_type = 3;
SELECT operation_sequence_id
INTO l_line_op_seq2_id
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_seq_num = p_line_op_seq2_num
AND operation_type = 3;
SELECT operation_sequence_id
INTO l_line_op_seq1_id
FROM bom_operation_sequences
WHERE routing_sequence_id = p_routing_sequence_id
AND operation_seq_num = p_line_op_seq1_num
AND operation_type = 3;
SELECT bos2.operation_seq_num, bos2.operation_sequence_id
INTO l_line_op_seq_num, l_line_op_seq_id
FROM bom_operation_sequences bos1, bom_operation_sequences bos2
WHERE bos2.operation_sequence_id = bos1.line_op_seq_id
AND bos1.routing_sequence_id = p_routing_sequence_id
AND bos1.operation_seq_num = p_event_op_seq_num
AND bos1.effectivity_date <= p_eff_date
AND Nvl(bos1.disable_date,p_eff_date+1) > p_eff_date
AND bos1.operation_type = 1;
select
lot_number,
expiration_date as lot_expiration_date,
description,
vendor_name,
supplier_lot_number,
grade_code,
origination_date,
date_code,
status_id,
change_date,
age,
retest_date,
maturity_date,
lot_attribute_category,
item_size,
color,
volume,
volume_uom,
place_of_origin,
best_by_date,
length,
length_uom,
recycled_content,
thickness,
thickness_uom,
width,
width_uom,
curl_wrinkle_fold,
c_attribute1,
c_attribute2,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute20,
d_attribute1,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
d_attribute10,
n_attribute1,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
n_attribute10,
vendor_id,
territory_code
from mtl_lot_numbers
where organization_id = p_org_id
and inventory_item_id = p_inventory_item_id
and lot_number = p_lot_number;
select mti.operation_seq_num,
mti.inventory_item_id,
msi.concatenated_segments,
mti.primary_quantity * -1 primary_quantity,
mti.transaction_quantity * -1 transaction_quantity,
msi.primary_uom_code,
mti.subinventory_code,
mti.locator_id,
msi.mtl_transactions_enabled_flag,
msi.serial_number_control_code,
msi.lot_control_code,
mti.revision,
mti.organization_id,
mti.transaction_source_id,
mti.transaction_action_id,
mti.transaction_interface_id,
mti.transaction_source_name,
mti.transfer_cost_group_id cost_group_id,
least( 1, NVL(mti.transfer_lpn_id, 0) + NVL(mti.content_lpn_id, 0) ) containerized,
mti.transaction_header_id -- bugfix 4455722
from mtl_transactions_interface mti,
mtl_system_items_kfv msi
where mti.parent_id = txn_interface_id
and nvl(mti.operation_seq_num, 1) > 0 /* not for phatom assembly */
and mti.transaction_action_id in (1,27,33,34) /*wip_constants.isscomp_action, wip_constants.retcomp_action, wip_constants.issnegc_action, wip_constants.retnegc_action)*/
and mti.inventory_item_id = msi.inventory_item_id
and mti.organization_id = msi.organization_id
and msi.lot_control_code = 2
and msi.serial_number_control_code = 1;
select t.transaction_action_id
into l_txn_act_id
from mtl_transaction_types t
where t.transaction_type_id = l_comp_txn_type_id;
select decode(t.revision_qty_control_code, 2, 'T', 'F') into l_rev_code
From mtl_system_items_b t
Where t.inventory_item_id = l_lotItemsRecords.inventory_item_id
and t.organization_id = l_lotItemsRecords.organization_id;
insert into mtl_transaction_lots_temp
(
transaction_temp_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
transaction_quantity,
primary_quantity,
lot_number,
group_header_id, -- bugfix 4455722
-- added
lot_expiration_date,
description,
vendor_name,
supplier_lot_number,
grade_code,
origination_date,
date_code,
status_id,
change_date,
age,
retest_date,
maturity_date,
lot_attribute_category,
item_size,
color,
volume,
volume_uom,
place_of_origin,
best_by_date,
length,
length_uom,
recycled_content,
thickness,
thickness_uom,
width,
width_uom,
curl_wrinkle_fold,
c_attribute1,
c_attribute2,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute20,
d_attribute1,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
d_attribute10,
n_attribute1,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
n_attribute10,
vendor_id,
territory_code
)
values
(
l_lotItemsRecords.transaction_interface_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
l_qty,
abs(l_cur_lot.primary_quantity),
l_cur_lot.lot_number,
l_lotItemsRecords.transaction_header_id, --bugfix 4455722
-- added
l_lotNumber.lot_expiration_date,
l_lotNumber.description,
l_lotNumber.vendor_name,
l_lotNumber.supplier_lot_number,
l_lotNumber.grade_code,
l_lotNumber.origination_date,
l_lotNumber.date_code,
l_lotNumber.status_id,
l_lotNumber.change_date,
l_lotNumber.age,
l_lotNumber.retest_date,
l_lotNumber.maturity_date,
l_lotNumber.lot_attribute_category,
l_lotNumber.item_size,
l_lotNumber.color,
l_lotNumber.volume,
l_lotNumber.volume_uom,
l_lotNumber.place_of_origin,
l_lotNumber.best_by_date,
l_lotNumber.length,
l_lotNumber.length_uom,
l_lotNumber.recycled_content,
l_lotNumber.thickness,
l_lotNumber.thickness_uom,
l_lotNumber.width,
l_lotNumber.width_uom,
l_lotNumber.curl_wrinkle_fold,
l_lotNumber.c_attribute1,
l_lotNumber.c_attribute2,
l_lotNumber.c_attribute3,
l_lotNumber.c_attribute4,
l_lotNumber.c_attribute5,
l_lotNumber.c_attribute6,
l_lotNumber.c_attribute7,
l_lotNumber.c_attribute8,
l_lotNumber.c_attribute9,
l_lotNumber.c_attribute10,
l_lotNumber.c_attribute11,
l_lotNumber.c_attribute12,
l_lotNumber.c_attribute13,
l_lotNumber.c_attribute14,
l_lotNumber.c_attribute15,
l_lotNumber.c_attribute16,
l_lotNumber.c_attribute17,
l_lotNumber.c_attribute18,
l_lotNumber.c_attribute19,
l_lotNumber.c_attribute20,
l_lotNumber.d_attribute1,
l_lotNumber.d_attribute2,
l_lotNumber.d_attribute3,
l_lotNumber.d_attribute4,
l_lotNumber.d_attribute5,
l_lotNumber.d_attribute6,
l_lotNumber.d_attribute7,
l_lotNumber.d_attribute8,
l_lotNumber.d_attribute9,
l_lotNumber.d_attribute10,
l_lotNumber.n_attribute1,
l_lotNumber.n_attribute2,
l_lotNumber.n_attribute3,
l_lotNumber.n_attribute4,
l_lotNumber.n_attribute5,
l_lotNumber.n_attribute6,
l_lotNumber.n_attribute7,
l_lotNumber.n_attribute8,
l_lotNumber.n_attribute9,
l_lotNumber.n_attribute10,
l_lotNumber.vendor_id,
l_lotNumber.territory_code
);