WIP Routing Interface With Revision
Routing Custom Table
CREATE TABLE APPS.XXMBS_BOM_OP_ROUTING_STG
(
ITEM_NAME VARCHAR2(100 BYTE),
REVISION_NO NUMBER(2),
OPERATION_SEQUENCE NUMBER(3),
DEPARTMENT VARCHAR2(30 BYTE),
OPERATION_DESCRIPTION VARCHAR2(100 BYTE),
RESOURCE_SEQUENCE NUMBER(3),
RESOURCE_CODE VARCHAR2(20 BYTE),
USAGE_RATE NUMBER(10,2),
ASSIGNED_UNITS NUMBER(5),
SCHEDULE_FLAG VARCHAR2(5 BYTE),
SCHEDULE_SEQ_NUM NUMBER(2),
COMPLETION_SUBINVENTORY VARCHAR2(25 BYTE),
COMPLETION_LOCATOR_ID NUMBER,
ROUT_VERIFY_FLAG VARCHAR2(1 BYTE),
SEQ_VERIFY_FLAG VARCHAR2(1 BYTE),
RES_VERIFY_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(2500 BYTE),
ROUTING_SEQUENCE_ID NUMBER,
OPERATION_SEQUENCE_ID NUMBER,
ORGANIZATION_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
NEED_REVISION_FLAG VARCHAR2(5 BYTE)
)
Import Routing Details Through Procedure and Validate with Open Interface
CREATE OR REPLACE Procedure APPS.Xxmbs_Bom_Routing_Api
Is
L_Organization_Id Number (5);
L_Rout_Verify_Flag Char (1);
L_Seq_Verify_Flag Char (1);
L_Res_Verify_Flag Char (1);
L_Error_Message Varchar2 (2500);
L_Inventory_Item_Id Number (20);
L_Department_Id Number (10);
L_Resource_Id Number (10);
L_Department_Code Varchar2 (20);
L_Resource_Code Varchar2 (20);
L_Schedule_Flag Number (3);
L_Res_Uom Varchar (10);
L_Link_Resource Number (10);
R_Count Number (10);
---Routing Cursor
Cursor C_Rout
Is
Select Distinct Item_Name, Revision_No,Routing_Sequence_Id,Need_Revision_Flag,Completion_Subinventory
,Completion_Locator_Id
From XxMBS_Bom_Op_Routing_Stg Order By Item_Name;
---Operation Sequence Cursor
Cursor C_Seq (P_Item_Name Varchar2)
Is
Select Distinct Item_Name, Revision_No, Operation_Sequence, Department,Routing_Sequence_Id,Operation_Sequence_Id,Need_Revision_Flag,OPERATION_DESCRIPTION
From XxMBS_Bom_Op_Routing_Stg
Where Item_Name = P_Item_Name
Order By Item_Name ,Operation_Sequence;
---Resource Cursor
Cursor C_Res (P_Item_Name Varchar2, P_Operation_Sequence Number)
Is
Select *
From XxMBS_Bom_Op_Routing_Stg
Where Item_Name = P_Item_Name
And Operation_Sequence = P_Operation_Sequence
-- And Resource_Sequence=P_Resource_Sequence
Order By Item_Name ,Operation_Sequence ,Resource_Sequence;
Begin
For Rout1 In C_Rout
Loop
L_Rout_Verify_Flag := 'Y';
L_Seq_Verify_Flag := 'Y';
L_Res_Verify_Flag := 'Y';
L_Error_Message := Null;
Begin
Select Organization_Id
Into L_Organization_Id
From Org_Organization_Definitions
Where Organization_Name = 'MBS - Hamriyah
Free Zone';
Exception
When Others
Then
L_Rout_Verify_Flag := 'N';
L_Error_Message := 'Organization is
not Valid';
End;
Begin
Select Inventory_Item_Id
Into L_Inventory_Item_Id
From Mtl_System_Items_B
Where Upper (Segment1) = Trim (Rout1.Item_Name)
And Organization_Id = L_Organization_Id;
Exception
When Others
Then
L_Rout_Verify_Flag := 'N';
L_Error_Message := L_Error_Message || 'Item
Name is not Valid';
End;
If L_Rout_Verify_Flag <> 'N'
Then
Savepoint A1;
Begin
If Rout1.Need_Revision_Flag = 'N' Then
Insert Into
Bom_Op_Routings_Interface
(Assembly_Item_Id,
Process_Revision,
Process_Flag, Transaction_Type, Routing_Type,
Organization_Id, Completion_Subinventory,
Completion_Locator_Id
)
Values (L_Inventory_Item_Id,
Decode (Rout1.Revision_No,
0, Null,
Rout1.Revision_No
),
1,
--1 (Pending), 3
(Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'CREATE', 1, ----1) Manufacturing,
2) Engineering
L_Organization_Id, Rout1.Completion_Subinventory ,
Rout1.Completion_Locator_Id
);
Elsif Rout1.Need_Revision_Flag='Y' Then
Insert Into
Bom_Op_Routings_Interface
(Assembly_Item_Id,
Process_Revision,
Process_Flag, Transaction_Type, Routing_Type,
Organization_Id, Completion_Subinventory,
Completion_Locator_Id,
Routing_Sequence_Id
)
Values (L_Inventory_Item_Id,
Decode (Rout1.Revision_No,
0, Null,
Rout1.Revision_No
),
1,
--1 (Pending), 3
(Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'UPDATE', 1, ----1) Manufacturing,
2) Engineering
L_Organization_Id, Rout1.Completion_Subinventory ,
Rout1.Completion_Locator_Id, Rout1.Routing_Sequence_Id
);
--If You Have Revision
We Need To Insert
Insert Into
Mtl_Rtg_Item_Revs_Interface
(Inventory_Item_Id,
Organization_Id,
Process_Revision,
Process_Flag,
Transaction_Type)
Values
(L_Inventory_Item_Id,
L_Organization_Id,
Decode (Rout1.Revision_No,0, Null,Rout1.Revision_No),
1,'CREATE'
);
End If;
Update XxMBS_Bom_Op_Routing_Stg
Set Rout_Verify_Flag = 'Y'--,NEED_REVISION_FLAG='Y'
Where Item_Name = Rout1.Item_Name;
Exception
When Others
Then
L_Error_Message := Sqlerrm;
Update XxMBS_Bom_Op_Routing_Stg
L Set Rout_Verify_Flag = 'N',
Error_Message = L_Error_Message
Where Item_Name = Rout1.Item_Name;
End;
For Seq1 In C_Seq (Rout1.Item_Name)
Loop
Begin
Select Distinct Department_Id, Department_Code
Into L_Department_Id, L_Department_Code
From Bom_Departments
Where Upper (Department_Code) = Upper (Trim (Seq1.Department))
And
Organization_Id = L_Organization_Id;
Exception
When Others
Then
L_Error_Message :=
L_Error_Message || 'Department Code is not valid';
L_Seq_Verify_Flag := 'N';
End;
If Trim (Seq1.Operation_Sequence) Is Null
Then
L_Error_Message :=
L_Error_Message || 'Operation sequence should not be null';
L_Seq_Verify_Flag := 'N';
End If;
If L_Seq_Verify_Flag <> 'N'
Then
Begin
If Seq1.Need_Revision_Flag='N' Then
Insert Into
Bom_Op_Sequences_Interface
(Assembly_Item_Id,
Operation_Seq_Num,
Department_Id, Department_Code, Process_Flag,
Transaction_Type, Organization_Id,
Effectivity_Date,OPERATION_DESCRIPTION
)
Values (L_Inventory_Item_Id,
Trim (Seq1.Operation_Sequence),
L_Department_Id, L_Department_Code, 1,
'CREATE', L_Organization_Id,
Sysdate,Seq1.OPERATION_DESCRIPTION
);
Elsif Seq1.Need_Revision_Flag='Y' Then
Insert Into
Bom_Op_Sequences_Interface
(Assembly_Item_Id,
Operation_Seq_Num,
Department_Id, Department_Code, Process_Flag,
Transaction_Type, Organization_Id,
Effectivity_Date,Routing_Sequence_Id,Operation_Sequence_Id,OPERATION_DESCRIPTION
)
Values (L_Inventory_Item_Id,
Trim (Seq1.Operation_Sequence),
L_Department_Id, L_Department_Code, 1,
'UPDATE', L_Organization_Id,
Sysdate,Seq1.Routing_Sequence_Id,Seq1.Operation_Sequence_Id,Seq1.OPERATION_DESCRIPTION
);
End If;
Update XxMBS_Bom_Op_Routing_Stg
Set Seq_Verify_Flag = 'Y',Organization_Id=L_Organization_Id,Inventory_Item_Id=L_Inventory_Item_Id
Where Item_Name = Seq1.Item_Name
And Operation_Sequence = Seq1.Operation_Sequence;
Exception
When Others
Then
Rollback To A1;
L_Error_Message := 'Op Sequence
Error.' || Sqlerrm;
Update XxMBS_Bom_Op_Routing_Stg
Set Seq_Verify_Flag = 'N',
Error_Message = L_Error_Message
Where Item_Name = Seq1.Item_Name
And Operation_Sequence = Seq1.Operation_Sequence;
Update XxMBS_Bom_Op_Routing_Stg
Set Rout_Verify_Flag = 'N'
Where Item_Name = Seq1.Item_Name;
End;
For Res1 In C_Res (Seq1.Item_Name, Seq1.Operation_Sequence)
Loop
----Resource Duplicate
Checking Starrt
Begin
Select Count(*) Into R_Count From XxMBS_Bom_Op_Routing_Stg Where ITEM_NAME=RES1.ITEM_NAME
And OPERATION_SEQUENCE=RES1.OPERATION_SEQUENCE And RESOURCE_SEQUENCE=RES1.RESOURCE_SEQUENCE;
Dbms_Output.Put_Line('Resource Code Duplicate ='||R_Count);
End;
If R_Count>1
Then
Dbms_Output.Put_Line('Resource Code Duplicate ='||R_Count);
L_Res_Verify_Flag
:= 'N';
L_Error_Message
:=
L_Error_Message
|| 'Resource Sequence Duplicate Pls Verify';
End If;
----Resource Duplicate
Checking Starrt
If Res1.RESOURCE_SEQUENCE Is Not Null Then
Begin
Select Distinct Resource_Id, Resource_Code,
Unit_Of_Measure
Into L_Resource_Id, L_Resource_Code, L_Res_Uom
From Bom_Resources
Where Upper (Resource_Code) =Trim (Upper (Res1.Resource_Code))
And Organization_Id = L_Organization_Id;
Dbms_Output.Put_Line('Resource Code ='||Res1.Resource_Code ||' '||L_Department_Id||'
'||L_Resource_Id);
Exception
When Others
Then
L_Res_Verify_Flag
:= 'N';
L_Error_Message
:=
L_Error_Message
|| 'Resource code is not
valid';
End;
Begin
Select Resource_Id
Into L_Link_Resource
From Bom_Department_Resources_V
Where Organization_Id = L_Organization_Id
And Department_Id = L_Department_Id
And Resource_Id = L_Resource_Id;
Dbms_Output.Put_Line('Resource Link = '||L_Organization_Id ||' '||L_Department_Id||' '||L_Resource_Id);
Exception
When Others
Then
L_Res_Verify_Flag
:= 'N';
L_Error_Message
:=
L_Error_Message
|| 'Resource code is not Linked with
Department';
End;
If ( (Res1.Resource_Sequence Is Null)
Or (Res1.Usage_Rate Is Null)
Or (Res1.Assigned_Units Is Null)
)
Then
L_Res_Verify_Flag
:= 'N';
L_Error_Message :=
L_Error_Message
|| 'Resource Seq or usage rate or assigned
units is null';
End If;
If ( Upper (Trim (Res1.Schedule_Flag)) = 'YES'
And L_Res_Uom <> 'Hrs'
)
Then
L_Res_Verify_Flag
:= 'N';
L_Error_Message :=
L_Error_Message
|| 'This Resource cannot be
Schedule';
Elsif Upper (Trim (Res1.Schedule_Flag)) = 'YES'
Then
L_Schedule_Flag := 1;
Else
L_Schedule_Flag
:= Null;
End If;
If L_Res_Verify_Flag <> 'N'
Then
Begin
If Res1.Need_Revision_Flag='N' Then
Insert Into
Bom_Op_Resources_Interface
(Resource_Seq_Num,
Resource_Id, Resource_Code,
Usage_Rate_Or_Amount, --Basis_Type ,
Assigned_Units,
--Schedule_Flag,Autocharge_Type,
Assembly_Item_Id,
Operation_Seq_Num,
--Operation_Sequence_Id,
Process_Flag,
Transaction_Type,
Effectivity_Date,
Organization_Id, Schedule_Flag,
Schedule_Seq_Num
)
Values (Trim (Res1.Resource_Sequence),
L_Resource_Id, L_Resource_Code,
Trim (Res1.Usage_Rate), --1,
Trim (Res1.Assigned_Units), -- 1, 2,
L_Inventory_Item_Id,
Trim (Seq1.Operation_Sequence),
--2346216 ,
1,
'CREATE', Sysdate,
L_Organization_Id,
L_Schedule_Flag,
Trim (Res1.Schedule_Seq_Num)
);
Elsif Res1.Need_Revision_Flag='Y' Then
Insert Into
Bom_Op_Resources_Interface
(Resource_Seq_Num,
Resource_Id, Resource_Code,
Usage_Rate_Or_Amount, --Basis_Type ,
Assigned_Units,
--Schedule_Flag,Autocharge_Type,
Assembly_Item_Id,
Operation_Seq_Num,
--Operation_Sequence_Id,
Process_Flag,
Transaction_Type,
Effectivity_Date,
Organization_Id, Schedule_Flag,
Schedule_Seq_Num,Operation_Sequence_Id
)
Values (Trim (Res1.Resource_Sequence),
L_Resource_Id, L_Resource_Code,
Trim (Res1.Usage_Rate), --1,
Trim (Res1.Assigned_Units), -- 1, 2,
L_Inventory_Item_Id,
Trim (Seq1.Operation_Sequence),
--2346216 ,
1,
'UPDATE', Sysdate,
L_Organization_Id,
L_Schedule_Flag,
Trim (Res1.Schedule_Seq_Num),Res1.Operation_Sequence_Id
);
End If;
Update XxMBS_Bom_Op_Routing_Stg
Set
Res_Verify_Flag = 'Y'
Where Item_Name = Res1.Item_Name
And Department = Res1.Department
And Resource_Code = Res1.Resource_Code;
Exception
When Others
Then
Rollback To A1;
L_Error_Message
:=
L_Error_Message
|| 'Op Resource Sequence Error.'
|| Sqlerrm;
Update XxMBS_Bom_Op_Routing_Stg
Set Res_Verify_Flag = 'N',
Error_Message
= L_Error_Message
Where Item_Name = Res1.Item_Name
And Department = Res1.Department
And Resource_Code = Res1.Resource_Code;
Update XxMBS_Bom_Op_Routing_Stg
Set Seq_Verify_Flag = 'N'
Where Item_Name = Res1.Item_Name
And Department = Res1.Department;
Update XxMBS_Bom_Op_Routing_Stg
Set Rout_Verify_Flag = 'N'
Where Item_Name = Res1.Item_Name;
End;
Else
Rollback To A1;
Update XxMBS_Bom_Op_Routing_Stg
Set Res_Verify_Flag = 'N',
Error_Message
= L_Error_Message
Where Item_Name = Res1.Item_Name
And Department = Res1.Department
And Resource_Code = Res1.Resource_Code;
Update XxMBS_Bom_Op_Routing_Stg
Set Seq_Verify_Flag = 'N'
Where Item_Name = Res1.Item_Name
And Department = Res1.Department;
Update XxMBS_Bom_Op_Routing_Stg
Set Rout_Verify_Flag = 'N'
Where Item_Name = Res1.Item_Name;
End If; --If_Res_Verify_Flag
End if;
End Loop;
Else
Rollback To Savepoint A1;
Update XxMBS_Bom_Op_Routing_Stg
Set Seq_Verify_Flag = 'N',
Error_Message = L_Error_Message
Where Item_Name = Seq1.Item_Name
And Operation_Sequence = Seq1.Operation_Sequence;
Update XxMBS_Bom_Op_Routing_Stg
Set Rout_Verify_Flag = 'N'
Where Item_Name = Seq1.Item_Name;
--Goto Next_Rout;
End If; --If_Seq_Verify_Flag
End Loop;
Else
Update XxMBS_Bom_Op_Routing_Stg
Set Rout_Verify_Flag = 'N',
Error_Message = L_Error_Message
Where Item_Name = Rout1.Item_Name;
End If;
---If_Rout_Verify_Flag
--Next_Rout; --(This
Will Be In << >>)
Commit;
End Loop;
Update XxMBS_Bom_Op_Routing_Stg Set NEED_REVISION_FLAG='Y';
End Xxmbs_Bom_Routing_Api;
/
No comments:
Post a Comment