[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure fetch_and_delete(
p_hdr_id in number,
p_act_id in number default NULL,
p_mtls in out nocopy mtl_transactions_temp_rec) is
i number := 0;
SELECT
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSACTION_MODE,
LOCK_FLAG,
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,
REVISION,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_COST,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
TRANSACTION_REFERENCE,
REASON_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
SERIAL_NUMBER,
RECEIVING_DOCUMENT,
RCV_TRANSACTION_ID,
MOVE_TRANSACTION_ID,
COMPLETION_TRANSACTION_ID,
WIP_ENTITY_TYPE,
SCHEDULE_ID,
REPETITIVE_LINE_ID,
EMPLOYEE_CODE,
SCHEDULE_UPDATE_CODE,
SETUP_TEARDOWN_CODE,
ITEM_ORDERING,
NEGATIVE_REQ_FLAG,
OPERATION_SEQ_NUM,
PICKING_LINE_ID,
TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID,
PHYSICAL_ADJUSTMENT_ID,
CYCLE_COUNT_ID,
RMA_LINE_ID,
CUSTOMER_SHIP_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
USSGL_TRANSACTION_CODE,
VENDOR_LOT_NUMBER,
ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT,
SHIP_TO_LOCATION,
SHIPMENT_NUMBER,
TRANSFER_COST,
TRANSPORTATION_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_TO_LOCATION,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
MATERIAL_ALLOCATION_TEMP_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
ITEM_SEGMENTS,
ITEM_DESCRIPTION,
ITEM_TRX_ENABLED_FLAG,
ITEM_LOCATION_CONTROL_CODE,
ITEM_RESTRICT_SUBINV_CODE,
ITEM_RESTRICT_LOCATORS_CODE,
ITEM_REVISION_QTY_CONTROL_CODE,
ITEM_PRIMARY_UOM_CODE,
ITEM_UOM_CLASS,
ITEM_SHELF_LIFE_CODE,
ITEM_SHELF_LIFE_DAYS,
ITEM_LOT_CONTROL_CODE,
ITEM_SERIAL_CONTROL_CODE,
ALLOWED_UNITS_LOOKUP_CODE,
DEPARTMENT_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
VALID_SUBINVENTORY_FLAG,
VALID_LOCATOR_FLAG,
LOCATOR_SEGMENTS,
CURRENT_LOCATOR_CONTROL_CODE,
NUMBER_OF_LOTS_ENTERED,
WIP_COMMIT_FLAG,
NEXT_LOT_NUMBER,
LOT_ALPHA_PREFIX,
NEXT_SERIAL_NUMBER,
SERIAL_ALPHA_PREFIX,
POSTING_FLAG,
REQUIRED_FLAG,
PROCESS_FLAG,
ERROR_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRIMARY_SWITCH,
DEPARTMENT_CODE,
ERROR_EXPLANATION,
DEMAND_ID,
ITEM_INVENTORY_ASSET_FLAG,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
MOVEMENT_ID,
RESERVATION_QUANTITY,
SHIPPED_QUANTITY,
TRANSACTION_LINE_NUMBER,
EXPENDITURE_TYPE,
FINAL_COMPLETION_FLAG,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
RESOURCE_ACCOUNT,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
TRANSACTION_SEQUENCE_ID,
transfer_percentage,
qa_collection_id,
overcompletion_transaction_id,
overcompletion_transaction_qty,
overcompletion_primary_qty,
kanban_card_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_HEADER_ID = c_hdr_id
AND TRANSACTION_ACTION_ID = NVL(c_act_id, TRANSACTION_ACTION_ID);
p_mtls.LAST_UPDATE_DATE(i) := mtl_rec.LAST_UPDATE_DATE;
p_mtls.LAST_UPDATED_BY(i) := mtl_rec.LAST_UPDATED_BY;
p_mtls.LAST_UPDATE_LOGIN(i) := mtl_rec.LAST_UPDATE_LOGIN;
p_mtls.PROGRAM_UPDATE_DATE(i) := mtl_rec.PROGRAM_UPDATE_DATE;
p_mtls.SCHEDULE_UPDATE_CODE(i) := mtl_rec.SCHEDULE_UPDATE_CODE;
DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_HEADER_ID = p_hdr_id
AND TRANSACTION_ACTION_ID = NVL(p_act_id, TRANSACTION_ACTION_ID);
end fetch_and_delete;
procedure fetch_and_delete(
p_hdr_id in number,
p_act_id in number,
p_lots in out nocopy wip_lot_temp_cleanup.mtl_transaction_lots_temp_rec) is
i number := 0;
select
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,
lot_expiration_date,
error_code,
serial_transaction_temp_id,
group_header_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id));
p_lots.last_update_date(i) := lot_rec.last_update_date;
p_lots.last_updated_by(i) := lot_rec.last_updated_by;
p_lots.last_update_login(i) := lot_rec.last_update_login;
p_lots.program_update_date(i) := lot_rec.program_update_date;
delete from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id));
end fetch_and_delete;
procedure fetch_and_delete(
p_hdr_id in number,
p_act_id in number,
p_serials in out nocopy wip_serial_temp_cleanup.mtl_serial_numbers_temp_rec) is
i number := 0;
select
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,
vendor_serial_number,
vendor_lot_number,
fm_serial_number,
to_serial_number,
serial_prefix,
error_code error_code,
group_header_id
from mtl_serial_numbers_temp
where
transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id))
or
transaction_temp_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id)));
p_serials.last_update_date(i) := serial_rec.last_update_date;
p_serials.last_updated_by(i) := serial_rec.last_updated_by;
p_serials.last_update_login(i) := serial_rec.last_update_login;
p_serials.program_update_date(i) := serial_rec.program_update_date;
delete from mtl_serial_numbers_temp
where
transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id))
or
transaction_temp_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id)));
end fetch_and_delete;
procedure fetch_and_delete(
p_hdr_id in number,
p_act_id in number,
p_dyn_sns in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_rec) is
i number := 0;
select
INVENTORY_ITEM_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INITIALIZATION_DATE,
COMPLETION_DATE,
SHIP_DATE,
CURRENT_STATUS,
REVISION,
LOT_NUMBER,
FIXED_ASSET_TAG,
RESERVED_ORDER_ID,
PARENT_ITEM_ID,
PARENT_SERIAL_NUMBER,
ORIGINAL_WIP_ENTITY_ID,
ORIGINAL_UNIT_VENDOR_ID,
VENDOR_SERIAL_NUMBER,
VENDOR_LOT_NUMBER,
LAST_TXN_SOURCE_TYPE_ID,
LAST_TRANSACTION_ID,
LAST_RECEIPT_ISSUE_TYPE,
LAST_TXN_SOURCE_NAME,
LAST_TXN_SOURCE_ID,
DESCRIPTIVE_TEXT,
CURRENT_SUBINVENTORY_CODE,
CURRENT_LOCATOR_ID,
CURRENT_ORGANIZATION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
GROUP_MARK_ID,
LINE_MARK_ID,
LOT_LINE_MARK_ID
from mtl_serial_numbers
where current_status = 6
and group_mark_id = c_hdr_id
and (line_mark_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id))
or
lot_line_mark_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id))));
p_dyn_sns.LAST_UPDATE_DATE(i) := serial_rec.LAST_UPDATE_DATE;
p_dyn_sns.LAST_UPDATED_BY(i) := serial_rec.LAST_UPDATED_BY;
p_dyn_sns.LAST_UPDATE_LOGIN(i) := serial_rec.LAST_UPDATE_LOGIN;
p_dyn_sns.PROGRAM_UPDATE_DATE(i) := serial_rec.PROGRAM_UPDATE_DATE;
delete mtl_serial_numbers
where current_status = 6
and group_mark_id = p_hdr_id
and (line_mark_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id))
or
lot_line_mark_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id))));
end fetch_and_delete;
select
SERIAL_NUMBER,
INVENTORY_ITEM_ID,
GROUP_MARK_ID,
LINE_MARK_ID,
LOT_LINE_MARK_ID
from mtl_serial_numbers
where group_mark_id = c_hdr_id
and (line_mark_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id))
or
lot_line_mark_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = c_hdr_id
and transaction_action_id = nvl(c_act_id, transaction_action_id))));
update mtl_serial_numbers
set group_mark_id = null,
line_mark_id = null,
lot_line_mark_id = null
where group_mark_id = p_hdr_id
and (line_mark_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id))
or
lot_line_mark_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id in
(select transaction_temp_id
from mtl_material_transactions_temp
where transaction_header_id = p_hdr_id
and transaction_action_id = nvl(p_act_id, transaction_action_id))));
procedure fetch_and_delete(
p_hdr_id in number,
p_act_id in number default NULL,
p_materials in out nocopy mtl_transactions_temp_rec,
p_lots in out nocopy wip_lot_temp_cleanup.mtl_transaction_lots_temp_rec,
p_serials in out nocopy wip_serial_temp_cleanup.mtl_serial_numbers_temp_rec,
p_dyn_serials in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_rec,
p_ser_marks in out nocopy wip_serial_number_cleanup.mtl_serial_numbers_mark_rec) is
begin
-- get marked serial numbers
fetch_and_unmark(
p_hdr_id => p_hdr_id,
p_act_id => p_act_id,
p_serials => p_ser_marks);
fetch_and_delete(
p_hdr_id => p_hdr_id,
p_act_id => p_act_id,
p_dyn_sns => p_dyn_serials);
fetch_and_delete(
p_hdr_id => p_hdr_id,
p_act_id => p_act_id,
p_serials => p_serials);
fetch_and_delete(
p_hdr_id => p_hdr_id,
p_act_id => p_act_id,
p_lots => p_lots);
fetch_and_delete(
p_hdr_id => p_hdr_id,
p_act_id => p_act_id,
p_mtls => p_materials);
end fetch_and_delete;
procedure insert_rows(
p_mtls in mtl_transactions_temp_rec) is
i number := 1;
insert into mtl_material_transactions_temp (
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSACTION_MODE,
LOCK_FLAG,
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,
REVISION,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_COST,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
TRANSACTION_REFERENCE,
REASON_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
SERIAL_NUMBER,
RECEIVING_DOCUMENT,
RCV_TRANSACTION_ID,
MOVE_TRANSACTION_ID,
COMPLETION_TRANSACTION_ID,
WIP_ENTITY_TYPE,
SCHEDULE_ID,
REPETITIVE_LINE_ID,
EMPLOYEE_CODE,
SCHEDULE_UPDATE_CODE,
SETUP_TEARDOWN_CODE,
ITEM_ORDERING,
NEGATIVE_REQ_FLAG,
OPERATION_SEQ_NUM,
PICKING_LINE_ID,
TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID,
PHYSICAL_ADJUSTMENT_ID,
CYCLE_COUNT_ID,
RMA_LINE_ID,
CUSTOMER_SHIP_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
USSGL_TRANSACTION_CODE,
VENDOR_LOT_NUMBER,
ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT,
SHIP_TO_LOCATION,
SHIPMENT_NUMBER,
TRANSFER_COST,
TRANSPORTATION_COST,
TRANSPORTATION_ACCOUNT,
FREIGHT_CODE,
CONTAINERS,
WAYBILL_AIRBILL,
EXPECTED_ARRIVAL_DATE,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_TO_LOCATION,
NEW_AVERAGE_COST,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
MATERIAL_ALLOCATION_TEMP_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
ITEM_SEGMENTS,
ITEM_DESCRIPTION,
ITEM_TRX_ENABLED_FLAG,
ITEM_LOCATION_CONTROL_CODE,
ITEM_RESTRICT_SUBINV_CODE,
ITEM_RESTRICT_LOCATORS_CODE,
ITEM_REVISION_QTY_CONTROL_CODE,
ITEM_PRIMARY_UOM_CODE,
ITEM_UOM_CLASS,
ITEM_SHELF_LIFE_CODE,
ITEM_SHELF_LIFE_DAYS,
ITEM_LOT_CONTROL_CODE,
ITEM_SERIAL_CONTROL_CODE,
ALLOWED_UNITS_LOOKUP_CODE,
DEPARTMENT_ID,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
VALID_SUBINVENTORY_FLAG,
VALID_LOCATOR_FLAG,
LOCATOR_SEGMENTS,
CURRENT_LOCATOR_CONTROL_CODE,
NUMBER_OF_LOTS_ENTERED,
WIP_COMMIT_FLAG,
NEXT_LOT_NUMBER,
LOT_ALPHA_PREFIX,
NEXT_SERIAL_NUMBER,
SERIAL_ALPHA_PREFIX,
POSTING_FLAG,
REQUIRED_FLAG,
PROCESS_FLAG,
ERROR_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRIMARY_SWITCH,
DEPARTMENT_CODE,
ERROR_EXPLANATION,
DEMAND_ID,
ITEM_INVENTORY_ASSET_FLAG,
SHIPPABLE_FLAG,
REQUISITION_LINE_ID,
REQUISITION_DISTRIBUTION_ID,
MOVEMENT_ID,
RESERVATION_QUANTITY,
SHIPPED_QUANTITY,
TRANSACTION_LINE_NUMBER,
EXPENDITURE_TYPE,
FINAL_COMPLETION_FLAG,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
PA_EXPENDITURE_ORG_ID,
PROJECT_ID,
RESOURCE_ACCOUNT,
SOURCE_PROJECT_ID,
SOURCE_TASK_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
TRANSACTION_SEQUENCE_ID,
transfer_percentage,
qa_collection_id,
overcompletion_transaction_id,
overcompletion_transaction_qty,
overcompletion_primary_qty,
kanban_card_id
) values (
p_mtls.TRANSACTION_HEADER_ID(i),
p_mtls.TRANSACTION_TEMP_ID(i),
p_mtls.SOURCE_CODE(i),
p_mtls.SOURCE_LINE_ID(i),
p_mtls.TRANSACTION_MODE(i),
p_mtls.LOCK_FLAG(i),
p_mtls.LAST_UPDATE_DATE(i),
p_mtls.LAST_UPDATED_BY(i),
p_mtls.CREATION_DATE(i),
p_mtls.CREATED_BY(i),
p_mtls.LAST_UPDATE_LOGIN(i),
p_mtls.REQUEST_ID(i),
p_mtls.PROGRAM_APPLICATION_ID(i),
p_mtls.PROGRAM_ID(i),
p_mtls.PROGRAM_UPDATE_DATE(i),
p_mtls.INVENTORY_ITEM_ID(i),
p_mtls.REVISION(i),
p_mtls.ORGANIZATION_ID(i),
p_mtls.SUBINVENTORY_CODE(i),
p_mtls.LOCATOR_ID(i),
p_mtls.TRANSACTION_QUANTITY(i),
p_mtls.PRIMARY_QUANTITY(i),
p_mtls.TRANSACTION_UOM(i),
p_mtls.TRANSACTION_COST(i),
p_mtls.TRANSACTION_TYPE_ID(i),
p_mtls.TRANSACTION_ACTION_ID(i),
p_mtls.TRANSACTION_SOURCE_TYPE_ID(i),
p_mtls.TRANSACTION_SOURCE_ID(i),
p_mtls.TRANSACTION_SOURCE_NAME(i),
p_mtls.TRANSACTION_DATE(i),
p_mtls.ACCT_PERIOD_ID(i),
p_mtls.DISTRIBUTION_ACCOUNT_ID(i),
p_mtls.TRANSACTION_REFERENCE(i),
p_mtls.REASON_ID(i),
p_mtls.LOT_NUMBER(i),
p_mtls.LOT_EXPIRATION_DATE(i),
p_mtls.SERIAL_NUMBER(i),
p_mtls.RECEIVING_DOCUMENT(i),
p_mtls.RCV_TRANSACTION_ID(i),
p_mtls.MOVE_TRANSACTION_ID(i),
p_mtls.COMPLETION_TRANSACTION_ID(i),
p_mtls.WIP_ENTITY_TYPE(i),
p_mtls.SCHEDULE_ID(i),
p_mtls.REPETITIVE_LINE_ID(i),
p_mtls.EMPLOYEE_CODE(i),
p_mtls.SCHEDULE_UPDATE_CODE(i),
p_mtls.SETUP_TEARDOWN_CODE(i),
p_mtls.ITEM_ORDERING(i),
p_mtls.NEGATIVE_REQ_FLAG(i),
p_mtls.OPERATION_SEQ_NUM(i),
p_mtls.PICKING_LINE_ID(i),
p_mtls.TRX_SOURCE_LINE_ID(i),
p_mtls.TRX_SOURCE_DELIVERY_ID(i),
p_mtls.PHYSICAL_ADJUSTMENT_ID(i),
p_mtls.CYCLE_COUNT_ID(i),
p_mtls.RMA_LINE_ID(i),
p_mtls.CUSTOMER_SHIP_ID(i),
p_mtls.CURRENCY_CODE(i),
p_mtls.CURRENCY_CONVERSION_RATE(i),
p_mtls.CURRENCY_CONVERSION_TYPE(i),
p_mtls.CURRENCY_CONVERSION_DATE(i),
p_mtls.USSGL_TRANSACTION_CODE(i),
p_mtls.VENDOR_LOT_NUMBER(i),
p_mtls.ENCUMBRANCE_ACCOUNT(i),
p_mtls.ENCUMBRANCE_AMOUNT(i),
p_mtls.SHIP_TO_LOCATION(i),
p_mtls.SHIPMENT_NUMBER(i),
p_mtls.TRANSFER_COST(i),
p_mtls.TRANSPORTATION_COST(i),
p_mtls.TRANSPORTATION_ACCOUNT(i),
p_mtls.FREIGHT_CODE(i),
p_mtls.CONTAINERS(i),
p_mtls.WAYBILL_AIRBILL(i),
p_mtls.EXPECTED_ARRIVAL_DATE(i),
p_mtls.TRANSFER_SUBINVENTORY(i),
p_mtls.TRANSFER_ORGANIZATION(i),
p_mtls.TRANSFER_TO_LOCATION(i),
p_mtls.NEW_AVERAGE_COST(i),
p_mtls.VALUE_CHANGE(i),
p_mtls.PERCENTAGE_CHANGE(i),
p_mtls.MATERIAL_ALLOCATION_TEMP_ID(i),
p_mtls.DEMAND_SOURCE_HEADER_ID(i),
p_mtls.DEMAND_SOURCE_LINE(i),
p_mtls.DEMAND_SOURCE_DELIVERY(i),
p_mtls.ITEM_SEGMENTS(i),
p_mtls.ITEM_DESCRIPTION(i),
p_mtls.ITEM_TRX_ENABLED_FLAG(i),
p_mtls.ITEM_LOCATION_CONTROL_CODE(i),
p_mtls.ITEM_RESTRICT_SUBINV_CODE(i),
p_mtls.ITEM_RESTRICT_LOCATORS_CODE(i),
p_mtls.ITEM_REVISION_QTY_CONTROL_CODE(i),
p_mtls.ITEM_PRIMARY_UOM_CODE(i),
p_mtls.ITEM_UOM_CLASS(i),
p_mtls.ITEM_SHELF_LIFE_CODE(i),
p_mtls.ITEM_SHELF_LIFE_DAYS(i),
p_mtls.ITEM_LOT_CONTROL_CODE(i),
p_mtls.ITEM_SERIAL_CONTROL_CODE(i),
p_mtls.ALLOWED_UNITS_LOOKUP_CODE(i),
p_mtls.DEPARTMENT_ID(i),
p_mtls.WIP_SUPPLY_TYPE(i),
p_mtls.SUPPLY_SUBINVENTORY(i),
p_mtls.SUPPLY_LOCATOR_ID(i),
p_mtls.VALID_SUBINVENTORY_FLAG(i),
p_mtls.VALID_LOCATOR_FLAG(i),
p_mtls.LOCATOR_SEGMENTS(i),
p_mtls.CURRENT_LOCATOR_CONTROL_CODE(i),
p_mtls.NUMBER_OF_LOTS_ENTERED(i),
p_mtls.WIP_COMMIT_FLAG(i),
p_mtls.NEXT_LOT_NUMBER(i),
p_mtls.LOT_ALPHA_PREFIX(i),
p_mtls.NEXT_SERIAL_NUMBER(i),
p_mtls.SERIAL_ALPHA_PREFIX(i),
p_mtls.POSTING_FLAG(i),
p_mtls.REQUIRED_FLAG(i),
p_mtls.PROCESS_FLAG(i),
p_mtls.ERROR_CODE(i),
p_mtls.ATTRIBUTE_CATEGORY(i),
p_mtls.ATTRIBUTE1(i),
p_mtls.ATTRIBUTE2(i),
p_mtls.ATTRIBUTE3(i),
p_mtls.ATTRIBUTE4(i),
p_mtls.ATTRIBUTE5(i),
p_mtls.ATTRIBUTE6(i),
p_mtls.ATTRIBUTE7(i),
p_mtls.ATTRIBUTE8(i),
p_mtls.ATTRIBUTE9(i),
p_mtls.ATTRIBUTE10(i),
p_mtls.ATTRIBUTE11(i),
p_mtls.ATTRIBUTE12(i),
p_mtls.ATTRIBUTE13(i),
p_mtls.ATTRIBUTE14(i),
p_mtls.ATTRIBUTE15(i),
p_mtls.PRIMARY_SWITCH(i),
p_mtls.DEPARTMENT_CODE(i),
p_mtls.ERROR_EXPLANATION(i),
p_mtls.DEMAND_ID(i),
p_mtls.ITEM_INVENTORY_ASSET_FLAG(i),
p_mtls.SHIPPABLE_FLAG(i),
p_mtls.REQUISITION_LINE_ID(i),
p_mtls.REQUISITION_DISTRIBUTION_ID(i),
p_mtls.MOVEMENT_ID(i),
p_mtls.RESERVATION_QUANTITY(i),
p_mtls.SHIPPED_QUANTITY(i),
p_mtls.TRANSACTION_LINE_NUMBER(i),
p_mtls.EXPENDITURE_TYPE(i),
p_mtls.FINAL_COMPLETION_FLAG(i),
p_mtls.MATERIAL_ACCOUNT(i),
p_mtls.MATERIAL_OVERHEAD_ACCOUNT(i),
p_mtls.OUTSIDE_PROCESSING_ACCOUNT(i),
p_mtls.OVERHEAD_ACCOUNT(i),
p_mtls.PA_EXPENDITURE_ORG_ID(i),
p_mtls.PROJECT_ID(i),
p_mtls.RESOURCE_ACCOUNT(i),
p_mtls.SOURCE_PROJECT_ID(i),
p_mtls.SOURCE_TASK_ID(i),
p_mtls.TASK_ID(i),
p_mtls.TO_PROJECT_ID(i),
p_mtls.TO_TASK_ID(i),
p_mtls.TRANSACTION_SEQUENCE_ID(i),
p_mtls.TRANSFER_PERCENTAGE(i),
p_mtls.qa_collection_id(i),
p_mtls.overcompletion_transaction_id(i),
p_mtls.overcompletion_transaction_qty(i),
p_mtls.overcompletion_primary_qty(i),
p_mtls.kanban_card_id(i)
);
end insert_rows;
procedure insert_rows(
p_materials in mtl_transactions_temp_rec,
p_lots in wip_lot_temp_cleanup.mtl_transaction_lots_temp_rec,
p_serials in wip_serial_temp_cleanup.mtl_serial_numbers_temp_rec,
p_dyn_serials in wip_serial_number_cleanup.mtl_serial_numbers_rec,
p_ser_marks in wip_serial_number_cleanup.mtl_serial_numbers_mark_rec,
p_retcode out nocopy number,
p_app out nocopy varchar2,
p_msg out nocopy varchar2) is
x_retcode number;
insert_rows(p_mtls => p_materials);
wip_lot_temp_cleanup.insert_rows(p_lots => p_lots);
wip_serial_temp_cleanup.insert_rows(p_serials => p_serials);
wip_serial_number_cleanup.insert_rows(p_serials => p_dyn_serials);
end insert_rows;