[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL ( ccicv.item_cost, 0 )
INTO x_item_cost
FROM cst_cg_item_costs_view ccicv,
mtl_parameters mp
WHERE l_locator_id IS NULL
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( p_cost_group_id , mp.default_cost_group_id)
)
UNION ALL
SELECT NVL ( ccicv.item_cost, 0 )
FROM mtl_item_locations mil,
cst_cg_item_costs_view ccicv,
mtl_parameters mp
WHERE l_locator_id IS NOT NULL
AND mil.organization_id = p_organization_id
AND mil.inventory_location_id = l_locator_id
AND mil.project_id IS NULL
AND ccicv.organization_id = mil.organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( p_cost_group_id , mp.default_cost_group_id)
)
UNION ALL
SELECT NVL ( ccicv.item_cost, 0 )
FROM mtl_item_locations mil,
mrp_project_parameters mrp,
cst_cg_item_costs_view ccicv,
mtl_parameters mp
WHERE l_locator_id IS NOT NULL
AND mil.organization_id = p_organization_id
AND mil.inventory_location_id = l_locator_id
AND mil.project_id IS NOT NULL
AND mrp.organization_id = mil.organization_id
AND mrp.project_id = mil.project_id
AND ccicv.organization_id = mil.organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( mrp.costing_group_id, 1 )
);
PROCEDURE Final_Preupdate_Logic(
p_reference IN VARCHAR2 DEFAULT NULL,
p_primary_uom_quantity IN NUMBER ,
p_count_uom IN VARCHAR2 ,
p_serial_number IN VARCHAR2 ,
p_subinventory IN VARCHAR2 ,
p_lot_number IN VARCHAR2 ,
p_lot_expiration IN date ,
p_revision IN VARCHAR2 ,
p_transaction_reason_id IN NUMBER ,
p_transaction_process_mode IN NUMBER DEFAULT 3,
p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE)
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
MDEBUG( 'Process- Inside Final-Pre-update ');
mdebug('Final_Preupdate_Logic: Number of counts => ' || L_number_of_Counts);
MDEBUG( 'Process-Inside F-P-Update Logic: '|| to_char(MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.entry_status_code));
SELECT mtl_material_transactions_s.nextval
INTO
L_txn_header_id
FROM
dual;
SELECT mtl_material_transactions_s.nextval
INTO
L_txn_temp_id
FROM
dual;
SELECT auto_serial_alpha_prefix
INTO
L_serial_prefix
FROM
mtl_system_items
WHERE
inventory_item_id = MTL_CCEOI_VAR_PVT.G_inventory_item_id
AND organization_id =
MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.organization_id;
MDEBUG( 'Process-Tolerence:Update Adj Amt ');
-- delete record
DELETE_RESERVATION (p_subinventory, p_lot_number, p_revision);
MDEBUG( 'Process-Tolerence:Update Adj Amt Flag '||L_success_flag);
Final_Preupdate_Logic(
p_reference => p_reference
, p_primary_uom_quantity => p_primary_uom_quantity
, p_count_uom => p_count_uom
, p_serial_number => p_serial_number
, p_subinventory => p_subinventory
, p_lot_number => p_lot_number
, p_lot_expiration => p_lot_expiration_date
, p_revision => p_revision
, p_transaction_reason_id => p_transaction_reason_id
, p_transaction_process_mode => p_transaction_process_mode
, p_simulate => p_simulate);
Final_Preupdate_Logic(
p_reference => p_reference
, p_primary_uom_quantity => p_primary_uom_quantity
, p_count_uom => p_count_uom
, p_serial_number => p_serial_number
, p_subinventory => p_subinventory
, p_lot_number => p_lot_number
, p_lot_expiration => p_lot_expiration_date
, p_revision => p_revision
, p_transaction_reason_id => p_transaction_reason_id
, p_transaction_process_mode => p_transaction_process_mode
, p_simulate => p_simulate);
MDEBUG( 'Tol : before final-pre-update-logic');
Final_Preupdate_Logic(
p_reference => p_interface_rec.reference
, p_primary_uom_quantity =>
p_interface_rec.primary_uom_quantity
, p_count_uom =>
p_interface_rec.count_uom
, p_serial_number => p_interface_rec.serial_number
, p_subinventory =>p_interface_rec.subinventory
, p_lot_number =>p_interface_rec.lot_number
, p_lot_expiration =>
MTL_CCEOI_VAR_PVT.G_SKU_REC.expiration_date
, p_revision =>p_interface_rec.revision
, p_transaction_reason_id =>
p_interface_rec.transaction_reason_id
, p_transaction_process_mode => p_interface_rec.process_mode
, p_simulate => p_simulate);
MDEBUG( 'Tole : After update logic');
MDEBUG( 'Before preupdate logic - 2');
Final_Preupdate_Logic(
p_reference => p_interface_rec.reference
, p_primary_uom_quantity =>
p_interface_rec.primary_uom_quantity
, p_count_uom => p_interface_rec.count_uom
, p_serial_number => p_interface_rec.serial_number
, p_subinventory =>p_interface_rec.subinventory
, p_lot_number =>p_interface_rec.lot_number
, p_lot_expiration =>
MTL_CCEOI_VAR_PVT.G_SKU_REC.expiration_date
, p_revision =>p_interface_rec.revision
, p_transaction_reason_id =>
p_interface_rec.transaction_reason_id
, p_transaction_process_mode => p_interface_rec.process_mode
, p_simulate => p_simulate );
MDEBUG( 'preupdate logic-2 -end ');
MDEBUG( 'Before preupdate - 4');
Final_Preupdate_Logic(
p_reference => p_interface_rec.reference
, p_primary_uom_quantity =>
p_interface_rec.primary_uom_quantity, p_count_uom =>
p_interface_rec.count_uom
, p_serial_number => p_interface_rec.serial_number
, p_subinventory =>p_interface_rec.subinventory
, p_lot_number =>p_interface_rec.lot_number
, p_lot_expiration =>
MTL_CCEOI_VAR_PVT.G_SKU_REC.expiration_date
, p_revision =>p_interface_rec.revision
, p_transaction_reason_id =>
p_interface_rec.transaction_reason_id
, p_transaction_process_mode => p_interface_rec.process_mode
, p_simulate => p_simulate );
MDEBUG( 'End of pre-update - 4');
MDEBUG( 'before pre-update logic - 5');
Final_Preupdate_Logic(
p_reference => p_interface_rec.reference
, p_primary_uom_quantity =>
p_interface_rec.primary_uom_quantity
, p_count_uom => p_interface_rec.count_uom
, p_serial_number => p_interface_rec.serial_number
, p_subinventory =>p_interface_rec.subinventory
, p_lot_number =>p_interface_rec.lot_number
, p_lot_expiration =>
MTL_CCEOI_VAR_PVT.G_SKU_REC.expiration_date
, p_revision =>p_interface_rec.revision
, p_transaction_reason_id =>
p_interface_rec.transaction_reason_id
, p_transaction_process_mode => p_interface_rec.process_mode
, p_simulate => p_simulate );
MDEBUG( 'before pre-update logic -end - 5');
MDEBUG( 'before final preupdate logic - err is not null');
Final_Preupdate_Logic(
p_reference => p_interface_rec.reference
, p_primary_uom_quantity =>
p_interface_rec.primary_uom_quantity, p_count_uom =>
p_interface_rec.count_uom
, p_serial_number => p_interface_rec.serial_number
, p_subinventory =>p_interface_rec.subinventory
, p_lot_number =>p_interface_rec.lot_number
, p_lot_expiration =>
MTL_CCEOI_VAR_PVT.G_SKU_REC.expiration_date
, p_revision =>p_interface_rec.revision
, p_transaction_reason_id =>
p_interface_rec.transaction_reason_id
, p_transaction_process_mode => p_interface_rec.process_mode
, p_simulate => p_simulate );
MDEBUG( 'end of preupdate logic - err is not null');
Final_Preupdate_Logic(
p_reference => p_interface_rec.reference
, p_primary_uom_quantity =>
p_interface_rec.primary_uom_quantity
, p_count_uom => p_interface_rec.count_uom
, p_serial_number => p_interface_rec.serial_number
, p_subinventory =>p_interface_rec.subinventory
, p_lot_number =>p_interface_rec.lot_number
, p_lot_expiration =>
MTL_CCEOI_VAR_PVT.G_SKU_REC.expiration_date
, p_revision =>p_interface_rec.revision
, p_transaction_reason_id =>
p_interface_rec.transaction_reason_id
, p_transaction_process_mode => p_interface_rec.process_mode
, p_simulate => p_simulate );
select
positive_measurement_error
, negative_measurement_error
from mtl_system_items
where inventory_item_id = itemid
and organization_id = org;
PROCEDURE Delete_CCIEntry(
p_cc_entry_interface_id IN NUMBER )
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
delete from mtl_cc_entries_interface
where cc_entry_interface_id = p_cc_entry_interface_id;
PROCEDURE Delete_CCEOIError(
p_cc_entry_interface_id IN NUMBER )
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
delete from mtl_cc_interface_errors
where cc_entry_interface_id = p_cc_entry_interface_id;
PROCEDURE Insert_CCEntry(
p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE)
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SELECT
MTL_CYCLE_COUNT_ENTRIES_S.nextval
INTO
L_CCEntryId
FROM
dual;
INSERT INTO MTL_CYCLE_COUNT_ENTRIES
( COST_GROUP_ID
, PARENT_LPN_ID
, OUTERMOST_LPN_ID
, CYCLE_COUNT_ENTRY_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, COUNT_LIST_SEQUENCE
, COUNT_DATE_FIRST
, COUNT_DATE_CURRENT
, COUNT_DATE_PRIOR
, COUNT_DATE_DUMMY
, COUNTED_BY_EMPLOYEE_ID_FIRST
, COUNTED_BY_EMPLOYEE_ID_CURRENT
, COUNTED_BY_EMPLOYEE_ID_PRIOR
, COUNTED_BY_EMPLOYEE_ID_DUMMY
, COUNT_UOM_FIRST
, COUNT_UOM_CURRENT
, COUNT_UOM_PRIOR
, COUNT_QUANTITY_FIRST
, COUNT_QUANTITY_CURRENT
, COUNT_QUANTITY_PRIOR
, INVENTORY_ITEM_ID
, SUBINVENTORY
, ENTRY_STATUS_CODE
, COUNT_DUE_DATE
, ORGANIZATION_ID
, CYCLE_COUNT_HEADER_ID
, NUMBER_OF_COUNTS
, LOCATOR_ID
, ADJUSTMENT_QUANTITY
, ADJUSTMENT_DATE
, ADJUSTMENT_AMOUNT
, ITEM_UNIT_COST
, INVENTORY_ADJUSTMENT_ACCOUNT
, APPROVAL_DATE
, APPROVER_EMPLOYEE_ID
, REVISION
, LOT_NUMBER
, LOT_CONTROL
, SYSTEM_QUANTITY_FIRST
, SYSTEM_QUANTITY_CURRENT
, SYSTEM_QUANTITY_PRIOR
, REFERENCE_FIRST
, REFERENCE_CURRENT
, REFERENCE_PRIOR
, PRIMARY_UOM_QUANTITY_FIRST
, PRIMARY_UOM_QUANTITY_CURRENT
, PRIMARY_UOM_QUANTITY_PRIOR
, COUNT_TYPE_CODE
, TRANSACTION_REASON_ID
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, APPROVAL_TYPE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, SERIAL_NUMBER
, SERIAL_DETAIL
, APPROVAL_CONDITION
, NEG_ADJUSTMENT_QUANTITY
, NEG_ADJUSTMENT_AMOUNT
, EXPORT_FLAG
-- BEGIN INVCONV
, SECONDARY_UOM_QUANTITY_FIRST
, SECONDARY_UOM_QUANTITY_CURRENT
, SECONDARY_UOM_QUANTITY_PRIOR
, COUNT_SECONDARY_UOM_FIRST
, COUNT_SECONDARY_UOM_CURRENT
, COUNT_SECONDARY_UOM_PRIOR
, SECONDARY_SYSTEM_QTY_FIRST
, SECONDARY_SYSTEM_QTY_CURRENT
, SECONDARY_SYSTEM_QTY_PRIOR
, SECONDARY_ADJUSTMENT_QUANTITY
-- END INVCONV
)
VALUES
( NULL
, NULL
, NULL
, L_CCEntryId
, sysdate
, MTL_CCEOI_VAR_PVT.G_UserID
, sysdate
, MTL_CCEOI_VAR_PVT.G_UserID
, MTL_CCEOI_VAR_PVT.G_UserID
, nvl(p_interface_rec.COUNT_LIST_SEQUENCE,MTL_CCEOI_VAR_PVT.G_Seq_No)
-- , p_interface_rec.count_list_sequence
, NULL -- MTL_CCEOI_VAR_PVT.G_COUNT_DATE
, NULL -- MTL_CCEOI_VAR_PVT.G_COUNT_DATE
, NULL
, NULL
, NULL -- MTL_CCEOI_VAR_PVT.G_EMPLOYEE_ID
, NULL -- MTL_CCEOI_VAR_PVT.G_EMPLOYEE_ID
, NULL
, NULL
, NULL -- MTL_CCEOI_VAR_PVT.G_UOM_CODE
, NULL -- MTL_CCEOI_VAR_PVT.G_UOM_CODE
, NULL
, NULL -- MTL_CCEOI_VAR_PVT.G_COUNT_QUANTITY
, NULL -- MTL_CCEOI_VAR_PVT.G_COUNT_QUANTITY
, NULL
, MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID
, MTL_CCEOI_VAR_PVT.G_SUBINVENTORY
, 1
, l_count_due_date
, p_interface_rec.ORGANIZATION_ID
, MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID
, NULL -- 0 XXX number of counts (null for new entries)
, MTL_CCEOI_VAR_PVT.G_LOCATOR_ID
, NULL
, NULL
, NULL
, NULL
, NULL --MTL_CCEOI_VAR_PVT.G_ADJUST_ACCOUNT_ID
, NULL
, NULL
, MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION -- NULL --Revision
, MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER -- NULL --Lot Number
, decode(MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE, 1, null,
MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE)
, NULL -- MTL_CCEOI_VAR_PVT.G_SYSTEM_QUANTITY
, NULL -- MTL_CCEOI_VAR_PVT.G_SYSTEM_QUANTITY
, NULL
, NULL -- p_interface_rec.REFERENCE
, NULL -- p_interface_rec.REFERENCE
, NULL
, NULL
, NULL
, NULL
, 2 -- UNSCHEDULED COUNT
, NULL
, MTL_CCEOI_VAR_PVT.G_RequestID
, MTL_CCEOI_VAR_PVT.G_ProgramAppID
, MTL_CCEOI_VAR_PVT.G_ProgramID
, sysdate
, NULL -- DECODE(
-- MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.APPROVAL_OPTION_CODE,
-- 1, 2, 2, 1, 3, 2, NULL)
, p_interface_rec.ATTRIBUTE_CATEGORY
, p_interface_rec.ATTRIBUTE1
, p_interface_rec.ATTRIBUTE2
, p_interface_rec.ATTRIBUTE3
, p_interface_rec.ATTRIBUTE4
, p_interface_rec.ATTRIBUTE5
, p_interface_rec.ATTRIBUTE6
, p_interface_rec.ATTRIBUTE7
, p_interface_rec.ATTRIBUTE8
, p_interface_rec.ATTRIBUTE9
, p_interface_rec.ATTRIBUTE10
, p_interface_rec.ATTRIBUTE11
, p_interface_rec.ATTRIBUTE12
, p_interface_rec.ATTRIBUTE13
, p_interface_rec.ATTRIBUTE14
, p_interface_rec.ATTRIBUTE15
, p_interface_rec.SERIAL_NUMBER
, NULL
, NULL
, NULL
, NULL
, 1 -- exported... do not forget to unexport
-- BEGIN INVCONV
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
-- END INVCONV
);
MDEBUG( 'Exception Error while Inserting..');
PROCEDURE Insert_CCIEntry(
p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE ,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
MDEBUG( 'Insert_CCIEntry: Before creation of new seq id');
SELECT
MTL_CC_ENTRIES_INTERFACE_S1.nextval
INTO
L_CCEOIId
FROM
dual;
mdebug('Insert_CCIEntry: ' || L_CCEOIId);
INSERT INTO MTL_CC_ENTRIES_INTERFACE
( cost_group_name
, cost_group_id
, parent_lpn_id
, outermost_lpn_id
, CC_ENTRY_INTERFACE_ID
, organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, CC_ENTRY_INTERFACE_GROUP_ID
, CYCLE_COUNT_ENTRY_ID
, action_code
, cycle_count_header_id
, cycle_count_header_name
, count_list_sequence
, inventory_item_id
, item_segment1
, item_segment2
, item_segment3
, item_segment4
, item_segment5
, item_segment6
, item_segment7
, item_segment8
, item_segment9
, item_segment10
, item_segment11
, item_segment12
, item_segment13
, item_segment14
, item_segment15
, item_segment16
, item_segment17
, item_segment18
, item_segment19
, item_segment20
, revision
, subinventory
, locator_id
, locator_segment1
, locator_segment2
, locator_segment3
, locator_segment4
, locator_segment5
, locator_segment6
, locator_segment7
, locator_segment8
, locator_segment9
, locator_segment10
, locator_segment11
, locator_segment12
, locator_segment13
, locator_segment14
, locator_segment15
, locator_segment16
, locator_segment17
, locator_segment18
, locator_segment19
, locator_segment20
, lot_number
, serial_number
, primary_uom_quantity
, count_uom
, count_unit_of_measure
, count_quantity
, system_quantity
, adjustment_account_id
, account_segment1
, account_segment2
, account_segment3
, account_segment4
, account_segment5
, account_segment6
, account_segment7
, account_segment8
, account_segment9
, account_segment10
, account_segment11
, account_segment12
, account_segment13
, account_segment14
, account_segment15
, account_segment16
, account_segment17
, account_segment18
, account_segment19
, account_segment20
, account_segment21
, account_segment22
, account_segment23
, account_segment24
, account_segment25
, account_segment26
, account_segment27
, account_segment28
, account_segment29
, account_segment30
, count_date
, employee_id
, employee_full_name
, reference
, transaction_reason_id
, transaction_reason
, request_id
, program_application_id
, program_id
, program_update_date
, lock_flag
, process_flag
, process_mode
, valid_flag
, delete_flag
, status_flag
, error_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, project_id
, task_id
-- BEGIN INVCONV
, secondary_uom
, secondary_unit_of_measure
, secondary_count_quantity
, secondary_system_quantity
-- END INVCONV
)
VALUES
( p_interface_rec.cost_group_name
, p_interface_rec.cost_group_id
, p_interface_rec.parent_lpn_id
, p_interface_rec.outermost_lpn_id
, L_CCEOIId
, p_interface_rec.organization_id
, sysdate
, MTL_CCEOI_VAR_PVT.G_UserID
, sysdate
, MTL_CCEOI_VAR_PVT.G_UserID
, MTL_CCEOI_VAR_PVT.G_LoginID
, P_interface_rec.CC_ENTRY_INTERFACE_GROUP_ID
, P_interface_rec.CYCLE_COUNT_ENTRY_ID
--- was always insert g_process. BUG??
, nvl(p_interface_rec.action_code, MTL_CCEOI_VAR_PVT.G_PROCESS)
, p_interface_rec.cycle_count_header_id
, p_interface_rec.cycle_count_header_name
, nvl(p_interface_rec.COUNT_LIST_SEQUENCE,MTL_CCEOI_VAR_PVT.G_Seq_No)
, p_interface_rec.inventory_item_id
, p_interface_rec.item_segment1
, p_interface_rec.item_segment2
, p_interface_rec.item_segment3
, p_interface_rec.item_segment4
, p_interface_rec.item_segment5
, p_interface_rec.item_segment6
, p_interface_rec.item_segment7
, p_interface_rec.item_segment8
, p_interface_rec.item_segment9
, p_interface_rec.item_segment10
, p_interface_rec.item_segment11
, p_interface_rec.item_segment12
, p_interface_rec.item_segment13
, p_interface_rec.item_segment14
, p_interface_rec.item_segment15
, p_interface_rec.item_segment16
, p_interface_rec.item_segment17
, p_interface_rec.item_segment18
, p_interface_rec.item_segment19
, p_interface_rec.item_segment20
, p_interface_rec.revision
, p_interface_rec.subinventory
, p_interface_rec.locator_id
, p_interface_rec.locator_segment1
, p_interface_rec.locator_segment2
, p_interface_rec.locator_segment3
, p_interface_rec.locator_segment4
, p_interface_rec.locator_segment5
, p_interface_rec.locator_segment6
, p_interface_rec.locator_segment7
, p_interface_rec.locator_segment8
, p_interface_rec.locator_segment9
, p_interface_rec.locator_segment10
, p_interface_rec.locator_segment11
, p_interface_rec.locator_segment12
, p_interface_rec.locator_segment13
, p_interface_rec.locator_segment14
, p_interface_rec.locator_segment15
, p_interface_rec.locator_segment16
, p_interface_rec.locator_segment17
, p_interface_rec.locator_segment18
, p_interface_rec.locator_segment19
, p_interface_rec.locator_segment20
, p_interface_rec.lot_number
, p_interface_rec.serial_number
, p_interface_rec.primary_uom_quantity
, p_interface_rec.count_uom
, p_interface_rec.count_unit_of_measure
, p_interface_rec.count_quantity
, p_interface_rec.system_quantity --MTL_CCEOI_VAR_PVT.G_SYSTEM_QUANTITY
, p_interface_rec.adjustment_account_id
, p_interface_rec.account_segment1
, p_interface_rec.account_segment2
, p_interface_rec.account_segment3
, p_interface_rec.account_segment4
, p_interface_rec.account_segment5
, p_interface_rec.account_segment6
, p_interface_rec.account_segment7
, p_interface_rec.account_segment8
, p_interface_rec.account_segment9
, p_interface_rec.account_segment10
, p_interface_rec.account_segment11
, p_interface_rec.account_segment12
, p_interface_rec.account_segment13
, p_interface_rec.account_segment14
, p_interface_rec.account_segment15
, p_interface_rec.account_segment16
, p_interface_rec.account_segment17
, p_interface_rec.account_segment18
, p_interface_rec.account_segment19
, p_interface_rec.account_segment20
, p_interface_rec.account_segment21
, p_interface_rec.account_segment22
, p_interface_rec.account_segment23
, p_interface_rec.account_segment24
, p_interface_rec.account_segment25
, p_interface_rec.account_segment26
, p_interface_rec.account_segment27
, p_interface_rec.account_segment28
, p_interface_rec.account_segment29
, p_interface_rec.account_segment30
, p_interface_rec.count_date
, p_interface_rec.employee_id
, p_interface_rec.employee_full_name
, p_interface_rec.reference
, p_interface_rec.transaction_reason_id
, p_interface_rec.transaction_reason
, MTL_CCEOI_VAR_PVT.G_RequestID
, MTL_CCEOI_VAR_PVT.G_ProgramAppID
, MTL_CCEOI_VAR_PVT.G_ProgramID
, sysdate
, p_interface_rec.lock_flag
, 1 --p_interface_rec.process_flag
, p_interface_rec.process_mode
, p_interface_rec.valid_flag
, p_interface_rec.delete_flag
, p_interface_rec.status_flag
, p_interface_rec.error_flag
, p_interface_rec.ATTRIBUTE_CATEGORY
, p_interface_rec.attribute1
, p_interface_rec.attribute2
, p_interface_rec.attribute3
, p_interface_rec.attribute4
, p_interface_rec.attribute5
, p_interface_rec.attribute6
, p_interface_rec.attribute7
, p_interface_rec.attribute8
, p_interface_rec.attribute9
, p_interface_rec.attribute10
, p_interface_rec.attribute11
, p_interface_rec.attribute12
, p_interface_rec.attribute13
, p_interface_rec.attribute14
, p_interface_rec.attribute15
, p_interface_rec.project_id
, p_interface_rec.task_id
-- BEGIN INVCONV
, p_interface_rec.secondary_uom
, p_interface_rec.secondary_unit_of_measure
, p_interface_rec.secondary_count_quantity
, p_interface_rec.secondary_system_quantity
-- END INVCONV
);
mdebug('Insert_CCIEntry: Entry Inserted' || L_CCEOIId);
mdebug('Insert_CCIEntry: ' || sqlerrm);
PROCEDURE Insert_CCEOIError(
p_cc_entry_interface_id IN NUMBER ,
p_error_column_name IN VARCHAR2 ,
p_error_table_name IN VARCHAR2 ,
p_message_name IN VARCHAR2 )
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
MDEBUG('Insert error: ' || FND_MESSAGE.GET_STRING('INV', p_message_name));
SELECT MTL_CC_INTERFACE_ERRORS_S.nextval
INTO
L_interface_error_id
FROM
dual;
INSERT INTO mtl_cc_interface_errors(
interface_error_id,
cc_entry_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
error_message,
error_column_name,
error_table_name,
message_name
)
VALUES
(L_interface_error_id,
p_cc_entry_interface_id,
sysdate,
MTL_CCEOI_VAR_PVT.G_UserID,
sysdate,
MTL_CCEOI_VAR_PVT.G_UserID,
MTL_CCEOI_VAR_PVT.G_LoginID,
MTL_CCEOI_VAR_PVT.G_RequestID,
MTL_CCEOI_VAR_PVT.G_ProgramAppID,
MTL_CCEOI_VAR_PVT.G_ProgramID,
sysdate,
FND_MESSAGE.GET_STRING('INV', p_message_name),
p_error_column_name,
p_error_table_name,
p_message_name);
MDEBUG('Insert_CCEOIError - Exception: ' || sqlerrm);
Final_Preupdate_Logic(
p_reference => p_reference
, p_primary_uom_quantity => p_primary_uom_quantity
, p_count_uom => p_count_uom
, p_serial_number => p_serial_number
, p_subinventory => p_subinventory
, p_lot_number => p_lot_number
, p_lot_expiration => p_lot_expiration
, p_revision => p_revision
, p_transaction_reason_id => p_transaction_reason_id
, p_transaction_process_mode => p_transaction_process_mode
, p_simulate => p_simulate);
MDEBUG( 'SetCCExport : Before Update CCEId '||to_char(p_cycle_count_entry_id) ||' to '|| to_char(p_export_flag));
UPDATE mtl_cycle_count_entries
SET
export_flag = p_export_flag
, last_update_date = sysdate
, last_updated_by = MTL_CCEOI_VAR_PVT.G_UserID
, last_update_login = MTL_CCEOI_VAR_PVT.G_LoginID
, request_id = MTL_CCEOI_VAR_PVT.G_RequestID
, program_application_id = MTL_CCEOI_VAR_PVT.G_ProgramAppID
, program_id = MTL_CCEOI_VAR_PVT.G_ProgramID
, program_update_date = sysdate
WHERE
cycle_Count_entry_id = p_cycle_count_entry_id;
MDEBUG( 'SetCCExport : After Update');
MDEBUG( 'SetCCExport : Error Update');
L_delete NUMBER;
MDEBUG( 'Process:Setsflag-Delete '||p_flags);
UPDATE mtl_cc_entries_interface
SET
last_update_date = sysdate
, last_updated_by = MTL_CCEOI_VAR_PVT.G_UserID
, last_update_login = MTL_CCEOI_VAR_PVT.G_LoginID
, request_id = MTL_CCEOI_VAR_PVT.G_RequestID
, program_application_id = MTL_CCEOI_VAR_PVT.G_ProgramAppID
, program_id = MTL_CCEOI_VAR_PVT.G_ProgramID
, program_update_date = sysdate
, error_flag = DECODE(SUBSTR(p_flags, 1, 1), '$', error_flag, SUBSTR(p_flags, 1, 1))
, delete_flag = DECODE(SUBSTR(p_flags, 2, 1), '$', delete_flag, SUBSTR(p_flags, 2, 1))
, status_flag = DECODE(SUBSTR(p_flags, 3, 1), '$', status_flag, SUBSTR(p_flags, 3, 1))
, valid_flag = decode ( SUBSTR(p_flags, 1, 1),'1','2',DECODE(SUBSTR(p_flags, 4, 1), '$', valid_flag, SUBSTR(p_flags, 4, 1)))
-- , cycle_count_entry_id = nvl(cycle_count_entry_id,MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID)
WHERE
cc_entry_interface_id = p_cc_entry_interface_id;
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, p_error_column_name =>
'CYCLE_COUNT_HEADER_ID'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_INVALID_HEADER'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'CYCLE_COUNT_HEADER_ID'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_NO_HEADER'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'CYCLE_COUNT_HEADER_ID'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_MULT_HEADER'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'CYCLE_COUNT_HEADER_ID'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_UNMATCH_LISTSEQ'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'CYCLE_COUNT_HEADER_ID'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_MULT_MATCH_REQ'
);
IS SELECT
*
FROM mtl_cycle_count_entries
WHERE
cycle_count_header_id = header
AND organization_id = org
AND inventory_item_id = itemid
AND NVL(parent_lpn_id, -1) = NVL(lpnid, -1)
AND NVL(cost_group_id, -1) = NVL(cost, -1)
AND subinventory = subinv
AND entry_status_code IN(1, 2, 3)
AND(loc IS NULL OR
locator_id = loc)
AND(rev IS NULL OR
revision = rev)
AND(lot IS NULL OR
lot_number = lot)
AND(ser IS NULL OR
serial_number =ser);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
SELECT COUNT_QUANTITY INTO l_serial_quantity
FROM MTL_CC_ENTRIES_INTERFACE
WHERE CC_ENTRY_INTERFACE_ID = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID;
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => L_column_name
, p_error_table_name => L_table_name
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => '*'
, p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES'
, p_message_name => 'INV_CCEOI_NO_CCENTRY'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => '*'
, p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES'
, p_message_name => 'INV_CCEOI_MULT_REQUESTS'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_QUANTITY'
, p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES'
, p_message_name => 'INV_CCEOI_NEG_QTY');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_QUANTITY'
, p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES'
, p_message_name => 'INV_SERIAL_QTY_MUST_BE_1');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_QUANTITY'
, p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES'
, p_message_name => 'INV_GREATER_EQUAL_ZERO');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name =>
'UOM_CODE/UNIT_OF_MEASURE'
, p_error_table_name => 'MTL_ITEM_UOMS_VIEW'
, p_message_name => 'INV_CCEOI_NO_UOM'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name =>
'UOM_CODE/UNIT_OF_MEASURE'
, p_error_table_name => 'MTL_ITEM_UOMS_VIEW'
, p_message_name => 'INV_CCEOI_INVALID_UOM'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_QUANTITY'
, p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES'
, p_message_name => 'INV_CCEOI_NEG_QTY');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_QUANTITY'
, p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE'
, p_message_name => 'INV_SERIAL_QTY_MUST_BE_1');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_QUANTITY'
, p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE'
, p_message_name => 'INV_GREATER_EQUAL_ZERO');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error');
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'TRANSACTION_QUANTITY'
, p_error_table_name => 'MTL_ONHAND_QUANTITIES'
, p_message_name => 'INV_CCEOI_ADJCALC_NO_SERIAL'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_DATE'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_COUNT_DATE_FUTURE'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_DATE'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_COUNT_DATE_FUTURE'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'COUNT_DATE'
, p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS'
, p_message_name => 'INV_CCEOI_NO_OPEN_ADJ_PRD'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'EMPLOYEE_ID/NAME'
, p_error_table_name => 'MTL_EMPLOYEES_CURRENT_VIEW'
, p_message_name => L_message_name
);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error'
);
PROCEDURE Update_CCEntry(
p_cycle_count_entry_id IN NUMBER )
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
UPDATE MTL_CYCLE_COUNT_ENTRIES
SET
last_update_date =
sysdate
, last_updated_by =
MTL_CCEOI_VAR_PVT.G_UserID
, last_update_login =
MTL_CCEOI_VAR_PVT.G_LoginID
, count_date_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_DATE_FIRST
,count_date_prior =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_DATE_PRIOR
, count_date_current =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_DATE_CURRENT
, counted_by_employee_id_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNTED_BY_EMPLOYEE_ID_FIRST
, counted_by_employee_id_current=
MTL_CCEOI_VAR_PVT.G_EMPLOYEE_ID
, counted_by_employee_id_prior =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNTED_BY_EMPLOYEE_ID_PRIOR
, counted_by_employee_id_dummy=
MTL_CCEOI_VAR_PVT.G_EMPLOYEE_ID
, count_uom_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_UOM_FIRST
, count_uom_current=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_UOM_CURRENT
, count_uom_prior=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_UOM_PRIOR
, count_quantity_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_QUANTITY_FIRST
, count_quantity_current=
MTL_CCEOI_VAR_PVT.G_COUNT_QUANTITY
, count_quantity_prior=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_QUANTITY_PRIOR
, entry_status_code =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.ENTRY_STATUS_CODE
, count_due_date =
l_count_due_date
, number_of_counts=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.NUMBER_OF_COUNTS
, locator_id =
MTL_CCEOI_VAR_PVT.G_LOCATOR_ID
, adjustment_quantity =
--MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.ADJUSTMENT_QUANTITY
MTL_CCEOI_VAR_PVT.G_ADJUSTMENT_QUANTITY
, adjustment_date =
MTL_CCEOI_VAR_PVT.G_COUNT_DATE
, adjustment_amount =
MTL_CCEOI_VAR_PVT.G_ADJUSTMENT_AMOUNT
, inventory_adjustment_account=
decode(MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.ENTRY_STATUS_CODE, 5, MTL_CCEOI_VAR_PVT.G_ADJUST_ACCOUNT_ID, NULL)
, approval_date =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.APPROVAL_DATE
, approver_employee_id = null
-- decode( MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.APPROVAL_DATE, null,
-- null,
-- MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNTED_BY_EMPLOYEE_ID_CURRENT)
, revision=
MTL_CCEOI_VAR_PVT.G_SKU_REC.revision
, lot_number=
MTL_CCEOI_VAR_PVT.G_SKU_REC.lot_number
, lot_control =
decode(MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE, 1, null,
MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_CONTROL_CODE)
, system_quantity_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.SYSTEM_QUANTITY_FIRST
, system_quantity_current=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.SYSTEM_QUANTITY_CURRENT,
system_quantity_prior=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.SYSTEM_QUANTITY_PRIOR
, reference_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.REFERENCE_FIRST
, reference_current=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.REFERENCE_CURRENT
, reference_prior=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.REFERENCE_PRIOR
, primary_uom_quantity_first =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.PRIMARY_UOM_QUANTITY_FIRST
, primary_uom_quantity_prior=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.PRIMARY_UOM_QUANTITY_PRIOR
, primary_uom_quantity_current=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.PRIMARY_UOM_QUANTITY_CURRENT
, count_type_code =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.COUNT_TYPE_CODE
, transaction_reason_id=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.TRANSACTION_REASON_ID
, request_id =
MTL_CCEOI_VAR_PVT.G_RequestID
, program_application_id=
MTL_CCEOI_VAR_PVT.G_ProgramAppID
, program_id =
MTL_CCEOI_VAR_PVT.G_ProgramID
, program_update_date =
sysdate
, approval_type =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.APPROVAL_TYPE
, serial_number =
MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER
, serial_detail =
decode(MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER, NULL,
0, -- put 0 instead of NULL to be comliant with existing form
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.SERIAL_DETAIL)
, approval_condition =
nvl(MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.APPROVAL_CONDITION,0)
-- added nvl(x,0) to have the same output as forms
, neg_adjustment_quantity=
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.NEG_ADJUSTMENT_QUANTITY
, neg_adjustment_amount =
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.NEG_ADJUSTMENT_AMOUNT
, ITEM_UNIT_COST =
MTL_CCEOI_VAR_PVT.G_ITEM_COST
-- BEGIN INVCONV
, Count_Secondary_Uom_First = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Count_Secondary_Uom_First
, Count_Secondary_Uom_Current = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Count_Secondary_Uom_Current
, Count_Secondary_Uom_Prior = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Count_Secondary_Uom_Prior
, Secondary_Uom_Quantity_First = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Secondary_Uom_Quantity_First
, Secondary_Uom_Quantity_Current = MTL_CCEOI_VAR_PVT.G_SECONDARY_COUNT_QUANTITY
, Secondary_Uom_Quantity_Prior = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Secondary_Uom_Quantity_Prior
, Secondary_System_Qty_First = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Secondary_System_Qty_First
, Secondary_System_Qty_Current = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Secondary_System_Qty_Current
, Secondary_System_Qty_Prior = MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.Secondary_System_Qty_Prior
, Secondary_Adjustment_Quantity = MTL_CCEOI_VAR_PVT.G_SEC_ADJUSTMENT_QUANTITY
-- END INVCONV
WHERE
cycle_count_entry_id = P_cycle_count_entry_id;
MDEBUG( 'End of Update CC entry');
select
i.APPROVAL_TOLERANCE_POSITIVE ITEM_TOL_POS
,i.APPROVAL_TOLERANCE_NEGATIVE ITEM_TOL_NEG
,c.APPROVAL_TOLERANCE_POSITIVE CLASS_TOL_POS
,c.APPROVAL_TOLERANCE_NEGATIVE CLASS_TOL_NEG
,c.COST_TOLERANCE_POSITIVE CLASS_COST_POS
,c.COST_TOLERANCE_NEGATIVE CLASS_COST_NEG
FROM MTL_CYCLE_COUNT_ITEMS i
, MTL_CYCLE_COUNT_CLASSES c
WHERE i.cycle_count_Header_id = cheader
AND i.inventory_item_id = item
AND c.abc_class_id = i.abc_class_id
AND c.cycle_count_header_id = i.cycle_count_Header_id;
PROCEDURE Update_CCIEntry(
p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE ,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
UPDATE MTL_CC_ENTRIES_INTERFACE
SET
last_update_date = sysdate
, last_updated_by = MTL_CCEOI_VAR_PVT.G_UserID
, last_update_login = MTL_CCEOI_VAR_PVT.G_LoginID
, request_id = MTL_CCEOI_VAR_PVT.G_RequestID
, program_application_id = MTL_CCEOI_VAR_PVT.G_ProgramAppID
, program_id = MTL_CCEOI_VAR_PVT.G_ProgramID
, program_update_date = sysdate
, primary_uom_quantity = p_interface_rec.primary_uom_quantity
, count_uom = p_interface_rec.count_uom
, count_unit_of_measure = p_interface_rec.count_unit_of_measure
, count_quantity = p_interface_rec.count_quantity
, count_date =p_interface_rec.count_date
, employee_id = p_interface_rec.employee_id
, employee_full_name = p_interface_rec.employee_full_name
, reference = p_interface_rec.reference
, transaction_reason_id = p_interface_rec.transaction_reason_id
, transaction_reason = p_interface_rec.transaction_reason
, project_id = p_interface_rec.project_id
, task_id = p_interface_rec.task_id
--This code modification done for the bug2311404 by aapaul
, system_quantity = decode(MTL_CCEOI_VAR_PVT.G_SYSTEM_QUANTITY,NULL,
system_quantity,MTL_CCEOI_VAR_PVT.G_SYSTEM_QUANTITY)
, lock_flag = p_interface_rec.lock_flag
-- BEGIN INVCONV
, secondary_uom = p_interface_rec.secondary_uom
, secondary_unit_of_measure = p_interface_rec.secondary_unit_of_measure
, secondary_count_quantity = p_interface_rec.secondary_count_quantity
, secondary_system_quantity = decode(MTL_CCEOI_VAR_PVT.G_SECONDARY_SYSTEM_QUANTITY,NULL,
secondary_system_quantity,MTL_CCEOI_VAR_PVT.G_SECONDARY_SYSTEM_QUANTITY)
-- END INVCONV
WHERE
cc_entry_interface_id = p_interface_rec.cc_entry_interface_id;
UPDATE MTL_CC_ENTRIES_INTERFACE
SET cost_group_name = p_interface_rec.cost_group_name
, cost_group_id = p_interface_rec.cost_group_id
, parent_lpn_id = p_interface_rec.parent_lpn_id
, outermost_lpn_id = p_interface_rec.outermost_lpn_id
, organization_id = p_interface_rec.organization_id
, last_update_date = sysdate
, last_updated_by = MTL_CCEOI_VAR_PVT.G_UserID
, last_update_login = MTL_CCEOI_VAR_PVT.G_LoginID
, action_code = p_interface_rec.action_code
, cycle_count_header_id = p_interface_rec.cycle_count_header_id
, cycle_count_header_name = p_interface_rec.cycle_count_header_name
, count_list_sequence = p_interface_rec.count_list_sequence
, inventory_item_id = p_interface_rec.inventory_item_id
-- , inventory_item = p_interface_rec.inventory_item
, item_segment1 = p_interface_rec.item_segment1
, item_segment2 = p_interface_rec.item_segment2
, item_segment3 = p_interface_rec.item_segment3
, item_segment4 = p_interface_rec.item_segment4
, item_segment5 = p_interface_rec.item_segment5
, item_segment6 = p_interface_rec.item_segment6
, item_segment7 = p_interface_rec.item_segment7
, item_segment8 = p_interface_rec.item_segment8
, item_segment9 = p_interface_rec.item_segment9
, item_segment10 = p_interface_rec.item_segment10
, item_segment11 = p_interface_rec.item_segment11
, item_segment12 = p_interface_rec.item_segment12
, item_segment13 = p_interface_rec.item_segment13
, item_segment14 = p_interface_rec.item_segment14
, item_segment15 = p_interface_rec.item_segment15
, item_segment16 = p_interface_rec.item_segment16
, item_segment17 = p_interface_rec.item_segment17
, item_segment18 =p_interface_rec.item_segment18
, item_segment19 = p_interface_rec.item_segment19
, item_segment20 = p_interface_rec.item_segment20
, revision = p_interface_rec.revision
, subinventory = p_interface_rec.subinventory
, locator_id = p_interface_rec.locator_id
-- , locator = p_interface_rec.locator
, locator_segment1 = p_interface_rec.locator_segment1
, locator_segment2 = p_interface_rec.locator_segment2
, locator_segment3 = p_interface_rec.locator_segment3
, locator_segment4 = p_interface_rec.locator_segment4
, locator_segment5 = p_interface_rec.locator_segment5
, locator_segment6 = p_interface_rec.locator_segment6
, locator_segment7 = p_interface_rec.locator_segment7
, locator_segment8 = p_interface_rec.locator_segment8
, locator_segment9 = p_interface_rec.locator_segment9
, locator_segment10 = p_interface_rec.locator_segment10
, locator_segment11 = p_interface_rec.locator_segment11
, locator_segment12 = p_interface_rec.locator_segment12
, locator_segment13 = p_interface_rec.locator_segment13
, locator_segment14 = p_interface_rec.locator_segment14
, locator_segment15 = p_interface_rec.locator_segment15
, locator_segment16 = p_interface_rec.locator_segment16
, locator_segment17 = p_interface_rec.locator_segment17
, locator_segment18 = p_interface_rec.locator_segment18
, locator_segment19 = p_interface_rec.locator_segment19
, locator_segment20 = p_interface_rec.locator_segment20
, lot_number = p_interface_rec.lot_number
, serial_number = p_interface_rec.serial_number
, primary_uom_quantity = p_interface_rec.primary_uom_quantity
, count_uom = p_interface_rec.count_uom
, count_unit_of_measure = p_interface_rec.count_unit_of_measure
, count_quantity = p_interface_rec.count_quantity
, adjustment_account_id = p_interface_rec.adjustment_account_id
-- , adjustment_account = p_interface_rec.adjustment_account
, account_segment1 = p_interface_rec.account_segment1
, account_segment2 = p_interface_rec.account_segment2
, account_segment3 = p_interface_rec.account_segment3
, account_segment4 = p_interface_rec.account_segment4
, account_segment5 = p_interface_rec.account_segment5
, account_segment6 = p_interface_rec.account_segment6
, account_segment7 = p_interface_rec.account_segment7
, account_segment8 = p_interface_rec.account_segment8
, account_segment9 = p_interface_rec.account_segment9
, account_segment10 = p_interface_rec.account_segment10
, account_segment11 = p_interface_rec.account_segment11
, account_segment12 = p_interface_rec.account_segment12
, account_segment13 = p_interface_rec.account_segment13
, account_segment14 = p_interface_rec.account_segment14
, account_segment15 = p_interface_rec.account_segment15
, account_segment16 = p_interface_rec.account_segment16
, account_segment17 = p_interface_rec.account_segment17
, account_segment18 = p_interface_rec.account_segment18
, account_segment19 = p_interface_rec.account_segment19
, account_segment20 = p_interface_rec.account_segment20
, account_segment21 = p_interface_rec.account_segment21
, account_segment22 = p_interface_rec.account_segment22
, account_segment23 = p_interface_rec.account_segment23
, account_segment24 = p_interface_rec.account_segment24
, account_segment25 = p_interface_rec.account_segment25
, account_segment26 = p_interface_rec.account_segment26
, account_segment27 = p_interface_rec.account_segment27
, account_segment28 = p_interface_rec.account_segment28
, account_segment29 = p_interface_rec.account_segment29
, account_segment30 = p_interface_rec.account_segment30
, count_date =p_interface_rec.count_date
, employee_id = p_interface_rec.employee_id
, employee_full_name = p_interface_rec.employee_full_name
, reference = p_interface_rec.reference
, transaction_reason_id = p_interface_rec.transaction_reason_id
, transaction_reason = p_interface_rec.transaction_reason
, request_id = MTL_CCEOI_VAR_PVT.G_RequestID
, program_application_id = MTL_CCEOI_VAR_PVT.G_ProgramAppID
, program_id = MTL_CCEOI_VAR_PVT.G_ProgramID
, program_update_date = sysdate
, attribute_category = p_interface_rec.attribute_category
, attribute1 = p_interface_rec.attribute1
, attribute2 = p_interface_rec.attribute2
, attribute3 = p_interface_rec.attribute3
, attribute4 = p_interface_rec.attribute4
, attribute5 = p_interface_rec.attribute5
, attribute6 = p_interface_rec.attribute6
, attribute7 = p_interface_rec.attribute7
, attribute8 = p_interface_rec.attribute8
, attribute9 = p_interface_rec.attribute9
, attribute10 = p_interface_rec.attribute10
, attribute11 = p_interface_rec.attribute11
, attribute12 = p_interface_rec.attribute12
, attribute13 = p_interface_rec.attribute13
, attribute14 = p_interface_rec.attribute14
, attribute15 = p_interface_rec.attribute15
, project_id = p_interface_rec.project_id
, task_id = p_interface_rec.task_id
, lock_flag = p_interface_rec.lock_flag
-- BEGIN INVCONV
, secondary_uom = p_interface_rec.secondary_uom
, secondary_unit_of_measure = p_interface_rec.secondary_unit_of_measure
, secondary_count_quantity = p_interface_rec.secondary_count_quantity
-- END INVCONV
WHERE
cc_entry_interface_id = p_interface_rec.cc_entry_interface_id;
SELECT SERIAL_NUMBER_TYPE
INTO l_serial_number_type
FROM MTL_PARAMETERS
WHERE organization_id = MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID;
SELECT SERIAL_NUMBER_TYPE
INTO v_serial_number_type
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = v_org_id;
SELECT COUNT(*)
INTO L_serial_count
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = v_serial_number AND
INVENTORY_ITEM_ID = v_item_id AND
CURRENT_ORGANIZATION_ID = v_org_id AND
CURRENT_STATUS = 3;
PROCEDURE DELETE_RESERVATION (
p_subinventory IN VARCHAR2 ,
p_lot_number IN VARCHAR2 ,
p_revision IN VARCHAR2
)
IS
l_mtl_reservation_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE
:=INV_CC_RESERVATIONS_PVT.Define_Reserv_Rec_Type;
/* delete only cycle count reservation */
l_mtl_reservation_rec.demand_source_type_id := 9;
INV_CC_RESERVATIONS_PVT.Delete_All_Reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => l_init_msg_lst
, p_mtl_reservation_rec => l_mtl_reservation_rec
, x_error_code => l_error_code
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
mdebug('error in delete all reservations');
END DELETE_RESERVATION;
mtl_cceoi_process_pvt.insert_cceoierror
(p_cc_entry_interface_id => mtl_cceoi_var_pvt.g_cc_entry_interface_id
, p_error_column_name => 'SECONDARY_UOM_CODE'
, p_error_table_name => 'MTL_SYSTEM_ITEMS'
, p_message_name => 'INV_INCORRECT_SECONDARY_UOM');
mtl_cceoi_process_pvt.insert_cceoierror
(p_cc_entry_interface_id => mtl_cceoi_var_pvt.g_cc_entry_interface_id
, p_error_column_name => 'SECONDARY_COUNT_QUANTITY'
, p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE'
, p_message_name => 'INV_NO_CONVERSION_ERR');
mtl_cceoi_process_pvt.insert_cceoierror
(p_cc_entry_interface_id => mtl_cceoi_var_pvt.g_cc_entry_interface_id
, p_error_column_name => 'SECONDARY_COUNT_QUANTITY'
, p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE'
, p_message_name => 'INV_DEVIATION_CHECK_ERR');
mtl_cceoi_process_pvt.insert_cceoierror
(p_cc_entry_interface_id => mtl_cceoi_var_pvt.g_cc_entry_interface_id
, p_error_column_name => 'SECONDARY_COUNT_QUANTITY'
, p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE'
, p_message_name => 'INV_CCEOI_NEG_QTY');
mtl_cceoi_process_pvt.insert_cceoierror
(p_cc_entry_interface_id => mtl_cceoi_var_pvt.g_cc_entry_interface_id
, p_error_column_name => 'unexpected error'
, p_error_table_name => 'unexpected error'
, p_message_name => 'unexpected error');