[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rowid,
transaction_interface_id,
primary_quantity,
operation_seq_num,
scheduled_flag,
organization_id,
inventory_item_id,
transaction_source_id,
transaction_date,
transaction_type_id,
transaction_action_id,
schedule_number,
source_project_id,
project_id,
source_task_id,
task_id,
bom_revision,
revision,
bom_revision_date,
alternate_bom_designator,
routing_revision,
routing_revision_date,
alternate_routing_designator,
subinventory_code,
locator_id,
accounting_class,
acct_period_id,
completion_transaction_id,
transaction_batch_id,
transaction_batch_seq
from mtl_transactions_interface
where transaction_header_id = p_txnHeaderID
and transaction_source_type_id = 5
and process_flag = 1
and upper(nvl(flow_schedule, 'N')) = 'Y'
and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
WIP_CONSTANTS.CPLASSY_ACTION,
WIP_CONSTANTS.RETASSY_ACTION);
select count(*)
into l_fromUI
from mtl_transactions_interface
where parent_id is not null
and parent_id = flow_rec.transaction_interface_id
and substitution_type_id is null;
update mtl_transactions_interface
set 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,
alternate_bom_designator = flow_rec.alternate_bom_designator,
routing_revision = flow_rec.routing_revision,
routing_revision_date = flow_rec.routing_revision_date,
alternate_routing_designator = flow_rec.alternate_routing_designator,
subinventory_code = flow_rec.subinventory_code,
locator_id = flow_rec.locator_id,
accounting_class = flow_rec.accounting_class
where rowid = flow_rec.rowid;
select source_code
into l_src_code
from mtl_transactions_interface
where rowid = flow_rec.rowid;
SELECT COUNT(*)
INTO l_bf_count
FROM mtl_transactions_interface
WHERE transaction_header_id = p_txnHeaderID
AND completion_transaction_id = flow_rec.completion_transaction_id
AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION);
SELECT COUNT(*)
INTO l_lot_ser_count
FROM mtl_transactions_interface mti,
mtl_system_items msi
WHERE mti.organization_id = msi.organization_id
AND mti.inventory_item_id = msi.inventory_item_id
AND (msi.lot_control_code = WIP_CONSTANTS.LOT
OR
msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
WIP_CONSTANTS.DYN_RCV_SN))
AND transaction_header_id = p_txnHeaderID
AND completion_transaction_id = flow_rec.completion_transaction_id
AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION);
SELECT backflush_lot_entry_type
INTO l_lot_entry_type
FROM wip_parameters
WHERE organization_id = flow_rec.organization_id ;
update mtl_transactions_interface
set schedule_number = flow_rec.schedule_number
where completion_transaction_id = flow_rec.completion_transaction_id
and organization_id = flow_rec.organization_id --fix for bug 4890147, add more criteria so no FTS is done
and parent_id = flow_rec.transaction_interface_id;
* It doesn''t take the rowid so that everything can be selected from interface table.
* Instead, it takes all the parameter. The reason is that this might be called from
* the forms. It''s up to the caller to check the return value and set the error
* message. 1 means success and 0 means error.
*/
function deriveCompletion(p_scheduledFlag in number,
p_orgID in number,
p_itemID in number,
p_txnSrcID in number,
p_txnDate in date,
p_txnActionID in number,
p_schedNum in out NOCOPY varchar2,
p_srcProjID in out NOCOPY number,
p_projID in out NOCOPY number,
p_srcTaskID in out NOCOPY number,
p_taskID in out NOCOPY number,
p_bomRev in out NOCOPY varchar2,
p_rev in out NOCOPY varchar2,
p_bomRevDate in out NOCOPY date,
p_altBomDes in out NOCOPY varchar2,
p_routRev in out NOCOPY varchar2,
p_routRevDate in out NOCOPY date,
p_altRtgDes in out NOCOPY varchar2,
p_cplSubinv in out NOCOPY varchar2,
p_cplLocID in out NOCOPY number,
p_classCode in out NOCOPY varchar2) return varchar2 is
l_errMsg varchar2(240);
select schedule_number,
transaction_interface_id
into l_scheduleNumber,
l_interfaceID
from mtl_transactions_interface
where rowid = p_rowid;
select inventory_item_id,
organization_id,
substitution_item_id,
substitution_type_id,
operation_seq_num
from mtl_transactions_interface
where parent_id = p_parentID
and process_flag = 1;
select count(*)
into l_result
from mtl_transactions_interface
where parent_id = p_parentID
and process_flag = 1
and nvl(substitution_type_id, -1) not in (1, 2, 3, 4);
select count(*)
into l_result
from mtl_transactions_interface
where parent_id = p_parentID
and process_flag = 1
and nvl(flow_schedule, 'Y') <> 'Y';
select 1
into l_result
from mtl_system_items msi
where msi.organization_id = sub_rec.organization_id
and msi.inventory_item_id = sub_rec.inventory_item_id
and msi.mtl_transactions_enabled_flag = 'Y'
and msi.inventory_item_flag = 'Y'
and msi.bom_enabled_flag = 'Y'
and msi.eng_item_flag = decode(l_seeEngItem,
1,
msi.eng_item_flag,
'N')
and msi.bom_item_type = 4; -- standard type
select 1
into l_result
from mtl_system_items msi
where msi.organization_id = sub_rec.organization_id
and msi.inventory_item_id = sub_rec.substitution_item_id
and msi.mtl_transactions_enabled_flag = 'Y'
and msi.inventory_item_flag = 'Y'
and msi.bom_enabled_flag = 'Y'
and msi.eng_item_flag = decode(l_seeEngItem,
1,
msi.eng_item_flag,
'N')
and msi.bom_item_type = 4; -- standard type
* This procedure explodes the BOM and insert the material requirement into
* mti table under the given header id and parent id.
* If the supply subinv and locator in the BOM is not provided, then it will try
* to default those the rule: BOM level --> item level --> wip parameter
*/
procedure explodeRequirementsToMTI(p_txnHeaderID in number,
p_parentID in number,
p_txnTypeID in number,
p_assyID in number,
p_orgID in number,
p_qty in number,
p_altBomDesig in varchar2,
p_altOption in number,
/* Fix for bug#3423629 */ p_bomRevDate in date default NULL,
p_txnDate in date,
p_projectID in number,
p_taskID in number,
p_toOpSeqNum in number,
p_altRoutDesig in varchar2,
p_txnMode in number,
p_lockFlag in number := null,
p_txnSourceID in number := null,
p_acctPeriodID in number := null,
p_cplTxnID in number := null,
p_txnBatchID in number := null,
p_txnBatchSeq in number := null,
/* Fix for bug#5262858 */ p_defaultPushSubinv in varchar2 default null,
x_returnStatus out NOCOPY varchar2,
/* Fix for bug 5630078 */ x_nontxn_excluded out NOCOPY varchar2) is
l_compTbl system.wip_component_tbl_t;
l_insertPhantom number := WIP_CONSTANTS.NO;
l_insert varchar2(1) ; /*BUG 6134576*/
select wip_entity_id,
planned_quantity,
nvl(quantity_completed,0) as quantity_completed,
nvl(quantity_scrapped,0) as quantity_scrapped,
(planned_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped,0)) as open_quantity
from wip_flow_schedules wfs
where wfs.wip_entity_id = wipEntityId
;
select transaction_source_id
from mtl_transactions_interface
where transaction_header_id = txn_header_id
and rownum < 2
;
l_insertPhantom := wip_globals.use_phantom_routings(p_orgID);
will take place for them. No records are inserted in MTI for service items */
l_insert := 'Y' ;
select service_item_flag
into l_service_item_flag
from mtl_system_items
where inventory_item_id = l_compTbl(l_count).inventory_item_id
and organization_id = p_orgid ;
l_insert := 'N' ; /* 6134576 */
l_insert in following if statement
*/
/* goto MtiInsertLoop; */
goto MtiInsertLoop;
if (( l_insertPhantom = WIP_CONSTANTS.YES or
nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6) and l_insert = 'Y' ) then /*Bug 6134576*/
-- derive the txn type and action id
l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
insert into mtl_transactions_interface(
transaction_header_id,
transaction_interface_id,
transaction_mode,
parent_id,
source_code,
source_line_id,
source_header_id,
inventory_item_id,
revision,
organization_id,
transaction_source_id,
operation_seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
process_flag,
lock_flag,
validation_required,
transaction_date,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_source_type_id,
flow_schedule,
transaction_action_id,
transaction_type_id,
wip_supply_type,
wip_entity_type,
subinventory_code,
locator_id,
acct_period_id,
completion_transaction_id,
transaction_batch_id,
transaction_batch_seq,
project_id,
task_id,
source_project_id,
source_task_id)
values
(p_txnHeaderID,
mtl_material_transactions_s.nextval,
p_txnMode,
p_parentID,
'Backflush',
1,
1,
l_compTbl(l_count).inventory_item_id,
l_compTbl(l_count).revision,
p_orgID,
p_txnSourceID,
decode(l_compTbl(l_count).wip_supply_type,
6, -1*l_compTbl(l_count).operation_seq_num,
l_compTbl(l_count).operation_seq_num),
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,
1, -- process flag
p_lockFlag,
1, -- validation required
p_txnDate,
ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
l_compTbl(l_count).primary_uom_code,
ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
5,
'Y',
l_childTxnActionID,
l_childTxnTypeID,
l_compTbl(l_count).wip_supply_type,
wip_constants.flow,
l_compTbl(l_count).supply_subinventory,
l_compTbl(l_count).supply_locator_id,
p_acctPeriodID,
p_cplTxnID,
p_txnBatchID,
p_txnBatchSeq,
l_compTbl(l_count).project_id,
l_compTbl(l_count).task_id,
p_projectID,
p_taskID);
wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
' under op ' || l_compTbl(l_count).operation_seq_num,
x_returnStatus => l_returnStatus);
<>
l_count := l_compTbl.next(l_count);
p_delete_stack => fnd_api.g_false);
select transaction_interface_id,
substitution_type_id,
operation_seq_num,
organization_id,
inventory_item_id,
substitution_item_id,
transaction_uom,
subinventory_code,
locator_id
from mtl_transactions_interface
where parent_id = p_parentID
and process_flag = 1
and substitution_type_id is not null
order by substitution_type_id;
select transaction_interface_id
from mtl_transactions_interface
where parent_id = p_parentID
and process_flag = 1
and substitution_type_id is null
and operation_seq_num = p_opSeq
and organization_id = p_orgID
and inventory_item_id = p_itemID;
delete from mtl_transactions_interface
where transaction_interface_id = l_bfInterfaceID;
update mtl_transactions_interface
set inventory_item_id = subs_rec.substitution_item_id,
substitution_item_id = null,
substitution_type_id = null
where transaction_interface_id = subs_rec.transaction_interface_id;
delete from mtl_transactions_interface
where transaction_interface_id = l_bfInterfaceID;
delete from mtl_transactions_interface
where transaction_interface_id = subs_rec.transaction_interface_id;
fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_DELETE');
update mtl_transactions_interface
set substitution_type_id = null,
inventory_item_id = subs_rec.substitution_item_id,
substitution_item_id = null
where transaction_interface_id = subs_rec.transaction_interface_id;
delete from mtl_transactions_interface
where transaction_interface_id = subs_rec.transaction_interface_id;
update mtl_transactions_interface
set transaction_interface_id = subs_rec.transaction_interface_id,
subinventory_code = subs_rec.subinventory_code,
locator_id = subs_rec.locator_id
where transaction_interface_id = l_bfInterfaceID;
select transaction_source_id,
transaction_date
into l_wipEntityID,
l_txnDate
from mtl_transactions_interface
where transaction_interface_id = p_parentID;
select project_id, task_id
into l_srcProjID, l_srcTaskID
from wip_flow_schedules
where wip_entity_id = l_wipEntityID;
update mtl_transactions_interface
set transaction_source_type_id = nvl(transaction_source_type_id, 5),
flow_schedule = nvl(flow_schedule, 'Y'),
source_project_id = l_srcProjID,
source_task_id = l_srcTaskID,
transaction_source_id = l_wipEntityID,
wip_entity_type = decode(l_wipEntityID, null, null, 4),
transaction_date = to_date(to_char(l_txnDate, WIP_CONSTANTS.DT_NOSEC_FMT), WIP_CONSTANTS.DT_NOSEC_FMT)
where parent_id = p_parentID
and process_flag = 1
and substitution_type_id is null;
last_update_date DATE,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_login NUMBER,
request_id NUMBER,
program_application_id NUMBER,
program_id NUMBER,
program_update_date DATE,
organization_id NUMBER,
inventory_item_id NUMBER,
accounting_class VARCHAR2(10),
transaction_date DATE,
transaction_quantity NUMBER, -- we have to get the primary qty
transaction_uom VARCHAR2(3),
primary_quantity NUMBER,
transaction_source_id NUMBER,
transaction_source_name VARCHAR2(240),
revision VARCHAR2(3),
bom_revision VARCHAR2(3),
routing_revision VARCHAR2(3),
bom_revision_date DATE,
routing_revision_date DATE,
alternate_bom_designator VARCHAR2(10),
alternate_routing_designator VARCHAR2(10),
subinventory_code VARCHAR2(10),
locator_id NUMBER,
demand_class VARCHAR2(30),
schedule_group NUMBER,
build_sequence NUMBER,
repetitive_line_id NUMBER,
source_project_id NUMBER,
project_id NUMBER,
source_task_id NUMBER,
task_id NUMBER,
schedule_number VARCHAR2(30),
scheduled_flag NUMBER,
wip_entity_type NUMBER,
end_item_unit_number VARCHAR2(60),
transaction_header_id NUMBER,
completion_transaction_id NUMBER,
row_id rowid);
select transaction_action_id, -- CFM Scrap
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
organization_id,
inventory_item_id,
accounting_class,
transaction_date,
transaction_quantity, -- we have to get the 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,
transaction_header_id,
completion_transaction_id,
rowid
from mtl_transactions_interface
where transaction_interface_id = p_txnInterfaceID
and transaction_source_type_id = 5
and transaction_source_id is null
and flow_schedule = 'Y'
and transaction_action_id in (31, 32, 30) -- CFM Scrap
and scheduled_flag = 2
and process_flag = wip_constants.mti_inventory;
select transaction_action_id, -- CFM Scrap
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
organization_id,
inventory_item_id,
class_code,
transaction_date,
transaction_quantity, -- we have to get the 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,
transaction_header_id,
completion_transaction_id,
rowid
from mtl_material_transactions_temp
where transaction_temp_id = p_txnTmpID
and transaction_source_type_id = 5
and transaction_source_id is null
and flow_schedule = 'Y'
and transaction_action_id in (31, 32, 30) -- CFM Scrap
and scheduled_flag = 2
and process_flag = 'Y';
select transaction_source_type_id,
transaction_source_id,
flow_schedule,
transaction_action_id,
scheduled_flag,
process_flag
from mtl_transactions_interface
where transaction_interface_id = p_txnInterfaceID;
select transaction_source_type_id,
transaction_source_id,
flow_schedule,
transaction_action_id,
scheduled_flag,
process_flag
from mtl_transactions_interface
where transaction_interface_id = p_txnInterfaceID;
select count(*)
into l_count
from mtl_transactions_interface
where transaction_interface_id = p_txnInterfaceID;
select count(*)
into l_count
from mtl_material_transactions_temp
where transaction_temp_id = p_txnTmpID;
select primary_uom_code
into l_primaryUOM
from mtl_system_items
where inventory_item_id = l_flowRec.inventory_item_id
and organization_id = l_flowRec.organization_id;
select wip_entities_s.nextval into l_wipEntityID from dual;
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 l_materialAccount,
l_materialOverheadAccount,
l_resourceAccount,
l_outsideProcessingAccount,
l_materialVarianceAccount,
l_resourceVarianceAccount,
l_outsideProcVarAccount,
l_stdCostAdjustmentAccount,
l_overheadAccount,
l_overheadVarianceAccount
from wip_accounting_classes
where class_code = l_flowRec.accounting_class
and organization_id = l_flowRec.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,
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(
l_wipEntityID,
l_flowRec.organization_id,
l_flowRec.last_update_date,
l_flowRec.last_updated_by,
l_flowRec.creation_date,
l_flowRec.created_by,
l_flowRec.last_update_login,
l_flowRec.request_id,
l_flowRec.program_application_id,
l_flowRec.program_id,
l_flowRec.program_update_date,
l_flowRec.inventory_item_id,
l_flowRec.accounting_class,
l_flowRec.transaction_date,
NULL,
0,
0,
0,
NULL,
NULL,
l_flowRec.bom_revision,
l_flowRec.routing_revision,
l_flowRec.bom_revision_date,
l_flowRec.routing_revision_date,
l_flowRec.alternate_bom_designator,
l_flowRec.alternate_routing_designator,
l_flowRec.subinventory_code,
l_flowRec.locator_id,
l_materialAccount,
l_materialOverheadAccount,
l_resourceAccount,
l_outsideProcessingAccount,
l_materialVarianceAccount,
l_resourceVarianceAccount,
l_outsideProcVarAccount,
l_stdCostAdjustmentAccount,
l_overheadAccount,
l_overheadVarianceAccount,
l_flowRec.demand_class,
l_flowRec.transaction_date,
l_flowRec.schedule_group,
l_flowRec.build_sequence,
l_flowRec.repetitive_line_id,
--technically, the user should populate the source prj/tsk columns, but also
--accept prj/tsk columns
nvl(l_flowRec.source_project_id, l_flowRec.project_id),
decode(l_flowRec.source_project_id, null, l_flowRec.task_id, l_flowRec.source_task_id),
1, -- 1. Open, 2. Close
l_flowRec.schedule_number,
2, -- Unscheduled
l_flowRec.end_item_unit_number, -- end item unit number
NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
) returning project_id, task_id into l_prjID, l_tskID;
update mtl_transactions_interface
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
transaction_source_id = l_wipEntityID,
wip_entity_type = l_flowRec.wip_entity_type
where transaction_header_id = l_flowRec.transaction_header_id
and completion_transaction_id = l_flowRec.completion_transaction_id;
wip_logger.log(p_msg => SQL%ROWCOUNT || 'MTI rows updated!',
x_returnStatus => l_returnStatus);
update mtl_material_transactions_temp
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
transaction_source_id = l_wipEntityID,
wip_entity_type = l_flowRec.wip_entity_type
where transaction_temp_id = p_txnTmpID;
wip_logger.log(p_msg => SQL%ROWCOUNT || 'MMTT rows updated!',
x_returnStatus => l_returnStatus);
* This procedure performs the update to wip flow schedule.
*/
procedure updateFlowSchedule(p_txnTempID in number,
x_returnStatus out nocopy varchar2) is
l_wipEntityID number;
wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
p_params => l_params,
x_returnStatus => x_returnStatus);
select distinct
transaction_source_id,
decode(transaction_action_id, 30, 0, primary_quantity),
decode(transaction_action_id, 30, primary_quantity, 0),-- CFM Scrap
transaction_date,
flow_schedule
into l_wipEntityID,
l_cplQty,
l_scrapQty,
l_transactionDate,
l_flowSchedule
from mtl_material_transactions
where transaction_set_id = p_txnHeaderID
and transaction_action_id in (30, 31, 32);
select transaction_source_id,
decode(transaction_action_id, 30, 0, primary_quantity),
decode(transaction_action_id, 30, primary_quantity, 0),-- CFM Scrap
transaction_date,
flow_schedule
into l_wipEntityID,
l_cplQty,
l_scrapQty,
l_transactionDate,
l_flowSchedule
from mtl_material_transactions_temp
where transaction_temp_id = p_txnTempID;
select planned_quantity,
quantity_completed
into l_plannedQty,
l_completedQty
from wip_flow_schedules
where wip_entity_id = l_wipEntityID;
update wip_flow_schedules
set quantity_completed = nvl(quantity_completed, 0) + l_cplQty,
quantity_scrapped = nvl(quantity_scrapped, 0) + l_scrapQty,
transacted_flag = 'Y',
date_closed = decode(upper(nvl(l_flowSchedule, 'N')),
'Y',
decode(l_statusChange, 0, date_closed,
1, null,
2, l_transactionDate),
date_closed),
status = decode(upper(nvl(l_flowSchedule, 'N')),
'Y',
decode(l_statusChange, 0, status,
1, 1,
2, 2),
status)
where wip_entity_id = l_wipEntityID;
wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
p_procReturnStatus => x_returnStatus,
p_msg => 'Flow schedules updated successfully!',
x_returnStatus => l_returnStatus); --discard logging return status
wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
p_procReturnStatus => x_returnStatus,
p_msg => 'unexpected error: ' || SQLERRM,
x_returnStatus => l_returnStatus); --discard logging return status
fnd_message.set_name('WIP', 'WIP_UPDATE_WFS_ERROR');
end updateFlowSchedule;
update mtl_material_transactions_temp
set last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
process_flag = 'E',
lock_flag = 2,
error_code = substrb(p_msgData, 1, 240),
error_explanation = substrb(p_msgData, 1, 240)
where transaction_temp_id = p_txnTempID
or completion_transaction_id =
(select completion_transaction_id
from mtl_material_transactions_temp
where transaction_temp_id = p_txnTempID);
select common_routing_sequence_id,
nvl(cfm_routing_flag, 2)
into l_commonRoutSeqID,
l_cfmRouting
from bom_operational_routings
where organization_id = p_orgID
and assembly_item_id = p_assyID
and nvl(alternate_routing_designator, 'NONE') =
nvl(p_altRoutDesig, 'NONE');
select nvl(include_component_yield,1)
into l_includeYield
from wip_parameters
where organization_id = p_orgID;
x_compTbl.delete(l_count);
x_compTbl.delete(l_count);
x_compTbl.delete(l_count);
x_compTbl.delete(l_count);
select msi.service_item_flag
into l_service_item_flag
from mtl_system_items msi
where msi.inventory_item_id = x_compTbl(l_count).inventory_item_id
and msi.organization_id = p_orgID ;
x_compTbl.delete;
select msi.wip_supply_subinventory,
msi.wip_supply_locator_id,
wp.default_pull_supply_subinv,
wp.default_pull_supply_locator_id
into l_msiSubinv,
l_msiLocatorID,
l_wpSubinv,
l_wpLocatorID
from mtl_system_items msi,
wip_parameters wp
where msi.organization_id = wp.organization_id
and msi.organization_id = p_orgID
and msi.inventory_item_id = x_compTbl(l_count).inventory_item_id;
select decode (mp.project_reference_enabled,
null,milk.concatenated_segments,
2,milk.concatenated_segments,
1, inv_project.get_pjm_locsegs(milk.concatenated_segments))
into x_compTbl(l_count).locator_name
from mtl_parameters mp, mtl_item_locations_kfv milk
where mp.organization_id = p_orgID
and mp.organization_id = milk.organization_id
and milk.inventory_location_id = x_compTbl(l_count).supply_locator_id;
* This procedure explodes the BOM and insert the material requirement into
* mmtt table under the given header id and completion txn id.
* If the supply subinv and locator in the BOM is not provided, then it will try
* to default those the rule: BOM level --> item level --> wip parameter
*/
procedure explodeRequirementsToMMTT(p_txnTempID in number,
p_assyID in number,
p_orgID in number,
p_qty in number,
p_altBomDesig in varchar2,
p_altOption in number,
p_txnDate in date,
p_projectID in number,
p_taskID in number,
p_toOpSeqNum in number,
p_altRoutDesig in varchar2,
x_returnStatus out nocopy varchar2) is
l_compTbl system.wip_component_tbl_t;
l_insertPhantom number;
l_insertPhantom := wip_globals.use_phantom_routings(p_orgID);
if ( l_insertPhantom = WIP_CONSTANTS.YES or
nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6 ) then
-- derive the txn action and type id
l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
insert into mtl_material_transactions_temp(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
transaction_header_id,
transaction_temp_id,
transaction_mode,
transaction_source_id,
transaction_source_type_id,
transaction_type_id,
transaction_action_id,
transaction_date,
transaction_quantity,
transaction_uom,
primary_quantity,
parent_transaction_temp_id,
wip_supply_type,
wip_entity_type,
inventory_item_id,
revision,
operation_seq_num,
organization_id,
source_code,
process_flag,
posting_flag,
lock_flag,
subinventory_code,
locator_id,
acct_period_id,
completion_transaction_id,
flow_schedule
)
select
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,
mmtt.transaction_header_id,
mtl_material_transactions_s.nextval,
mmtt.transaction_mode,
mmtt.transaction_source_id,
5,
l_childTxnTypeID,
l_childTxnActionID,
p_txnDate,
l_compTbl(l_count).primary_quantity * -1,
l_compTbl(l_count).primary_uom_code,
l_compTbl(l_count).primary_quantity * -1,
p_txnTempID, -- parent transaction temp id
l_compTbl(l_count).wip_supply_type,
mmtt.wip_entity_type,
l_compTbl(l_count).inventory_item_id,
l_compTbl(l_count).revision,
decode(l_compTbl(l_count).wip_supply_type,
6, -1*l_compTbl(l_count).operation_seq_num,
l_compTbl(l_count).operation_seq_num),
p_orgID,
'WIP Flow Transcaction',
'N', -- default to No. call processLotSerialTemp() to update process flag
-- and determine if unfulfilled l/s requirements exist
'Y',
2, -- lock flag
l_compTbl(l_count).supply_subinventory,
l_compTbl(l_count).supply_locator_id,
l_acctPeriodID,
mmtt.completion_transaction_id,
'Y'
from mtl_material_transactions_temp mmtt
where mmtt.transaction_temp_id = p_txnTempID;
wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
' under op ' || l_compTbl(l_count).operation_seq_num,
x_returnStatus => l_returnStatus);
l_compTbl.delete;
x_lineOpTbl.delete;
select distinct bos.operation_sequence_id
into l_opSeqID
from bom_operation_sequences bos,
bom_operational_routings bor
where bor.common_routing_sequence_id = bos.routing_sequence_id
and bor.assembly_item_id = p_assyItemID
and bor.organization_id = p_orgID
and nvl(alternate_routing_designator, 'NONE') =
nvl(p_altRoutDesig, 'NONE')
and bos.operation_seq_num = p_terminalOpSeqNum
and bos.operation_type = 3;
select distinct bos.operation_sequence_id
into l_opSeqID
from bom_operation_sequences bos,
bom_operational_routings bor
where bor.common_routing_sequence_id = bos.routing_sequence_id
and bor.common_routing_sequence_id = p_routingSeqID
and bos.operation_seq_num = p_terminalOpSeqNum
and bos.operation_type = 3;
select distinct bos2.operation_seq_num
into l_evtLineOp
from bom_operation_sequences bos1,
bom_operation_sequences bos2
where bos1.routing_sequence_id = bos2.routing_sequence_id
and bos1.routing_sequence_id = p_routingSeqID
and bos1.operation_seq_num = p_eventNum
and bos1.operation_type = 1 -- event
and bos1.line_op_seq_id = bos2.operation_sequence_id
and bos2.operation_type = 3; -- line op
select inventory_item_id,
subinventory_code,
locator_id,
rowid
from mtl_material_transactions_temp
where completion_transaction_id = cpl_id
and transaction_source_type_id = 5
and flow_schedule = 'Y'
and process_flag = 'Y'
and locator_id is not null
and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
WIP_CONSTANTS.RETCOMP_ACTION,
WIP_CONSTANTS.ISSNEGC_ACTION,
WIP_CONSTANTS.RETNEGC_ACTION)
order by operation_seq_num;
select organization_id,
completion_transaction_id,
transaction_source_id,
source_project_id,
source_task_id
into l_orgID,
l_cplID,
l_wipEntityID,
l_srcProjectID,
l_srcTaskID
from mtl_material_transactions_temp
where transaction_temp_id = p_parentID;
select nvl(project_reference_enabled, 2),
stock_locator_control_code
into l_projRefEnabled,
l_orgLocControl
from mtl_parameters
where organization_id = l_orgID;
update mtl_material_transactions_temp
set (locator_id, project_id, task_id) =
(select inventory_location_id,
project_id,
task_id
from mtl_item_locations
where inventory_location_id = comp_rec.locator_id
and organization_id = l_orgID)
where rowid = comp_rec.rowid;