Overview:
While moving from 11i to
R12, All OPEN WIP Jobs (Released & Un-released) are to be converted from
Legacy to R12.
In Discrete Manufacturing
WIP Jobs are the Manufacturing Supply Orders for MAKE Items. As WIP Jobs are
completed, a Finished Goods Assemblies are generated in Sub-inventory, i.e.
On-Hand for Finish Good assemblies is created. These Finished Goods are later
shipped to Customers against a Sales Order.
Purpose/ Need of Legacy
WIP Jobs Conversion:
1) Planning/
MRP- 1st Run will consider Open WIP Jobs and will not duplicate/
recreate again for existing Sales Orders
2) For
Completed and Closed WIP Jobs, WIP Accounts are relieved (There is NO costs
held in WIP accounts) and Legacy System Accounting Periods can be closed in
Clean manner
3) WIP Jobs
in intermediate stages of Routing/ Operation- These WIP Jobs are completed or
Short Closed, so that Inventory is NOT held up in WIP accounts, facilitating
Clean closure of Accounting Periods
FLOW:
This process will explain in detail the flow
of Conversion. Here we used DB link to process data into R12 staging tables.
Mapping points with data selection criteria:
1) In order
to maintain the Manufacturing Lead times, Legacy Completion Dates (Either Start Date OR Completion Date is Mandatory. We Choose to
Populate Completion date, so as to NOT shift the schedule Ship-Dates. Start
Dates were automatically calculated for Converted WIP Jobs by system performing
Backward Calculation) were
converted (Start Date got automatically populated based on Lead Times).
2) Link
Legacy WIP Job Numbers (11i) to Newly Converted WIP Jobs in R12 – To retain WIP
Job sequence, we put legacy WIP job number in More Tabà Description.
1)
Insert data into Staging tables.
Interface Table:
WIP_JOB_SCHEDULE_INTERFACE
Oracle standard Import Program: WIP Mass Load
Oracle Base Tables:
WIP_DISCRETE_JOBS
WIP_ENTITIES
WIP_DISCRETE_JOBS.STATUS_TYPE values in
ORACLE
Value
|
Meaning
|
1
|
Unreleased
|
3
|
Released
|
4
|
Complete
|
5
|
Complete - No Charges
|
6
|
On Hold
|
7
|
Cancelled
|
8
|
Pending Bill Load
|
9
|
Failed Bill Load
|
10
|
Pending Routing Load
|
11
|
Failed Routing Load
|
12
|
Closed
|
13
|
Pending - Mass Loaded
|
14
|
Pending Close
|
15
|
Failed Close
|
Validation for few required fields:
1) Organization
code:
SELECT organization_id
INTO ln_organization_id
FROM mtl_parameters
WHERE organization_code =
p_organization_code;
2) Completion
sub inventory:
SELECT COUNT (1)
INTO ln_temp_count
FROM mtl_secondary_inventories
WHERE secondary_inventory_name
= p_completion_subinv
AND organization_id =
ln_organization_id;
3) Completion
locator:
SELECT inventory_location_id
INTO ln_completion_locator_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments =
p_completion_locator
AND subinventory_code =
p_completion_subinv
AND organization_id =
ln_organization_id;
4) Supply
sub inventory:
SELECT COUNT (1)
INTO ln_temp_count
FROM mtl_secondary_inventories
WHERE secondary_inventory_name =
p_supply_subinventory
AND organization_id =
ln_organization_id;
5) Supply
locator:
SELECT inventory_location_id
INTO ln_supply_locator_id
FROM mtl_item_locations_kfv
WHERE
concatenated_segments = p_supply_locator
AND subinventory_code =
p_supply_subinventory
AND organization_id = ln_organization_id;
6) Primary
Item ID:
SELECT inventory_item_id
INTO ln_primary_item_id
FROM mtl_system_items_b
WHERE segment1 = p_item_name
AND organization_id =
ln_organization_id;
7) Routing:
SELECT
routing_sequence_id
INTO ln_routing_sequence_id
FROM bom_operational_routings
WHERE assembly_item_id = ln_primary_item_id
AND organization_id =
ln_organization_id;
8) BOM:
SELECT
bill_sequence_id
INTO ln_bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = ln_primary_item_id
AND organization_id =
ln_organization_id;
INSERT INTO INTERFACE TABLE:
1.
New Job with existing Bills and Routings (To
create only job with the assembly item already defined in BOM and routing.)
INSERT INTO
wip_job_schedule_interface
(
organization_code
, primary_item_id
, job_name
, start_quantity
, net_quantity
, first_unit_start_date
, class_code
, status_type
, COMPLETION_SUBINVENTORY
, group_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
'NA' -- organization_id
,12345 -- primary_item_id
,WIP_JOB_NUMBER_S.nextval --
job_name
,5 -- start_quantity
,5 -- Net Quantity
,to_date('09-MAY-2008','DD-MON-RRRR')
--first_unit_start_date
,'Discrete' --class_code
,3 --status_type
1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled
,'ASSY'
--COMPLETION_SUBINVENTORY
,101 -- group_id
,1 -- load_type /*1 Create
Standard Discrete Job,2 Create Pending Repetitive Schedule
,3 Update Standard or Non-Standard
Discrete Job ,4 Create Non-Standard Discrete Job */
,2 -- process_phase 2
Validation 3 Explosion 4 Complete 5 Creation
,1 -- process_status 1
Pending 2 Running 3 Error 4 Complete 5 Warning
,43556 -- created_by
, SYSDATE -- creation_date
,43556 -- last_updated_by
, SYSDATE --
last_update_date
);
Run standard program to
insert data into Oracle Base tables.
Responsibility:
Manufacturing SU
Standard Program Name:
MASS LOAD
Navigation:
Manufacturing SU à
WIP àDiscrete
à
Import Jobs and Schedule.
Parameters for Concurrent Program:
GROUP ID: 101 (Same group id as in Interface Table)
The system polls the WIP_JOB_SCHEDULE_INTERFACE table for
rows with this group identification number. When rows with this number are
found, the system validates the data and either creates a job or repetitive
schedule, reschedules an existing job, or enters an error message. Rows with
errors in them remain in the table and can be updated and resubmitted.
Check values in Interface table:
select * from WIP_JOB_SCHEDULE_INTERFACE
where DESCRIPTION = '6453772'
Error Records:
select * from WIP_INTERFACE_ERRORS
where INTERFACE_ID = '1212'
-- need to change
after mass load program complete then WIP_JOB_SCHEDULE_INTERFACE
table populate value in
INTERFACE_ID column.That will be used to check error records in WIP_INTERFACE_ERRORS table.
No comments:
Post a Comment