The following lines contain the word 'select', 'insert', 'update' or 'delete':
cfmRec.row.last_updated_by := param.environment.userID;
cfmRec.row.last_update_date := sysdate;
/* select transaction_type_name
into cfmRec.row.transaction_source_name
from mtl_transaction_types
where transaction_type_id = param.transactionType;
select project_id,
task_id
into cfmRec.row.source_project_id,
cfmRec.row.source_task_id
from wip_flow_schedules
where wip_entity_id = param.wipEntityID
and organization_id = param.environment.orgID;
select count(*) into dummy
from mtl_system_items
where inventory_item_id = param.assemblyID
and organization_id = param.environment.orgID
and build_in_wip_flag = 'Y'
and base_item_id is not null
and bom_item_type = 4
and replenish_to_order_flag = 'Y';
select demand_source_header_id,
demand_source_line,
demand_source_delivery
into cfmRec.row.demand_source_header_id,
cfmRec.row.demand_source_line,
cfmRec.row.demand_source_delivery
from wip_flow_schedules
where organization_id = param.environment.orgID
and wip_entity_id = param.wipEntityID;
lpnCfmRec.row.last_updated_by := lpnParam.environment.userID;
lpnCfmRec.row.last_update_date := sysdate;
* This function is used to insert the record encapsulated in cfmRec to
* table mtl_transactions_interface and some furthur validation and processing.
*/
Function put(cfmRec CfmRecord, errMsg OUT NOCOPY VARCHAR2) return boolean IS
Begin
INSERT INTO mtl_transactions_interface
(transaction_interface_id,
transaction_header_id,
lock_flag, transaction_mode,
process_flag, validation_required,
source_code,
source_line_id,
source_header_id,
last_updated_by, last_update_date,
creation_date, created_by,
inventory_item_id,
organization_id,
acct_period_id,
transaction_date,
bom_revision, revision,
bom_revision_date,
routing_revision, routing_revision_date,
transaction_type_id,
negative_req_flag,
transaction_action_id,
transaction_quantity,
primary_quantity,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id, reason_id,
qa_collection_id,
transaction_source_type_id,
wip_entity_type,
transaction_source_name,
schedule_number,
repetitive_line_id,
operation_seq_num,
scheduled_flag, flow_schedule,
transaction_source_id,
accounting_class,
source_project_id,
source_task_id,
kanban_card_id,
demand_source_header_id,
demand_source_line,
demand_source_delivery
)
VALUES (cfmRec.row.transaction_interface_id,
cfmRec.row.transaction_header_id,
cfmRec.row.lock_flag, cfmRec.row.transaction_mode,
cfmRec.row.process_flag, cfmRec.row.validation_required,
cfmRec.row.source_code, cfmRec.row.source_line_id,
cfmRec.row.source_header_id,
cfmRec.row.last_updated_by, cfmRec.row.last_update_date,
cfmRec.row.creation_date, cfmRec.row.created_by,
cfmRec.row.inventory_item_id,
cfmRec.row.organization_id,
cfmRec.row.acct_period_id,
cfmRec.row.transaction_date,
cfmRec.row.bom_revision, cfmRec.row.revision,
cfmRec.row.bom_revision_date,
cfmRec.row.routing_revision, cfmRec.row.routing_revision_date,
cfmRec.row.transaction_type_id,
cfmRec.row.negative_req_flag,
cfmRec.row.transaction_action_id,
cfmRec.row.transaction_quantity,
cfmRec.row.primary_quantity,
cfmRec.row.distribution_account_id,
cfmRec.row.transaction_uom,
cfmRec.row.subinventory_code,
cfmRec.row.locator_id, cfmRec.row.reason_id,
cfmRec.row.qa_collection_id,
cfmRec.row.transaction_source_type_id,
cfmRec.row.wip_entity_type,
cfmRec.row.transaction_source_name,
cfmRec.row.schedule_number,
cfmRec.row.repetitive_line_id,
cfmRec.row.operation_seq_num,
cfmRec.row.scheduled_flag, cfmRec.row.flow_schedule,
cfmRec.row.transaction_source_id,
cfmRec.row.accounting_class,
cfmRec.row.source_project_id,
cfmRec.row.source_task_id,
cfmRec.row.kanban_card_id,
cfmRec.row.demand_source_header_id,
cfmRec.row.demand_source_line,
cfmRec.row.demand_source_delivery
);
INSERT INTO wip_lpn_completions
(header_id, source_id, source_code,
lock_flag, transaction_mode,
last_updated_by, last_update_date,
creation_date, created_by,
inventory_item_id,
organization_id,
acct_period_id,
transaction_date,
bom_revision, --check later revision,
bom_revision_date,
routing_revision, routing_revision_date,
transaction_type_id,
transaction_action_id,
transaction_quantity,
primary_quantity,
-- distribution_account_id,
transaction_uom,
subinventory_code,
locator_id, reason_id,
qa_collection_id,
transaction_source_type_id,
wip_entity_id,
wip_entity_type,
--repetitive_line_id,
operation_seq_num,
transaction_source_id,
accounting_class,
item_project_id,
item_task_id,
kanban_card_id,
lpn_id,
completion_transaction_id,
demand_source_header_id,
demand_source_line,
demand_source_delivery
)
VALUES (lpnCfmRec.row.header_id, lpnCfmRec.row.header_id, WMA_COMMON.SOURCE_CODE,
lpnCfmRec.row.lock_flag, lpnCfmRec.row.transaction_mode,
lpnCfmRec.row.last_updated_by, lpnCfmRec.row.last_update_date,
lpnCfmRec.row.creation_date, lpnCfmRec.row.created_by,
lpnCfmRec.row.inventory_item_id,
lpnCfmRec.row.organization_id,
lpnCfmRec.row.acct_period_id,
lpnCfmRec.row.transaction_date,
lpnCfmRec.row.bom_revision,-- lpnCfmRec.row.revision,
lpnCfmRec.row.bom_revision_date,
lpnCfmRec.row.routing_revision, lpnCfmRec.row.routing_revision_date,
lpnCfmRec.row.transaction_type_id,
lpnCfmRec.row.transaction_action_id,
lpnCfmRec.row.transaction_quantity,
lpnCfmRec.row.primary_quantity,
-- lpnCfmRec.row.distribution_account_id,
lpnCfmRec.row.transaction_uom,
lpnCfmRec.row.subinventory_code,
lpnCfmRec.row.locator_id, lpnCfmRec.row.reason_id,
lpnCfmRec.row.qa_collection_id,
lpnCfmRec.row.transaction_source_type_id,
lpnCfmRec.row.wip_entity_id,
lpnCfmRec.row.wip_entity_type,
--lpnCfmRec.row.repetitive_line_id,
lpnCfmRec.row.operation_seq_num,
lpnCfmRec.row.transaction_source_id,
lpnCfmRec.row.accounting_class,
lpnCfmRec.row.item_project_id, lpnCfmRec.row.item_task_id,
lpnCfmRec.row.kanban_card_id,
lpnCfmRec.row.lpn_id,
lpnCfmRec.row.completion_transaction_id,
lpnCfmRec.row.demand_source_header_id,
lpnCfmRec.row.demand_source_line,
lpnCfmRec.row.demand_source_delivery
);