CREATE OR REPLACE PACKAGE A07_REPLENISHMENT_INT AUTHID CURRENT_USER AS
/******************************************************************************
NAME: A07_REPLENISH_INTERFACE
PURPOSE: Item Replenishment Interface.
PARAMETERS:
INPUT:
OUTPUT:
RETURNED VALUE:
CALLED BY:
CALLS:
EXAMPLE USE: NUMBER := A07_REPLENISH_INTERFACE.MyFuncName(Number);
A07_REPLENISH_INTERFACE.MyProcName(Number, Varchar2);
ASSUMPTIONS:+
LIMITATIONS:
ALGORITHM:
NOTES:
******************************************************************************/
-- All the Global Variables to be used in this package
g_create_dt date := sysdate;
g_create_by number := 10005218;
g_update_dt date := sysdate;
g_update_by number := 10005218;
v_sqlcode number;
v_sqlerrm varchar2(255);
-- All Global Variables specific to the package
-- All the Functions used in this package
Function CHK_ORG_EXISTS (org_id mtl_parameters.organization_id%type) return boolean;
Function CHK_ITEM_EXISTS (item_id mtl_system_items_b.inventory_item_id%type,
v_org_id mtl_system_items_b.organization_id%type) return boolean;
Function CHK_ITEM_SUBINV (v_item_id number, v_subinv varchar2, v_org_id number) return boolean;
Function GET_ITEM_PLAN (v_org_id number, v_item_id number, v_subinv varchar2) return number;
Function GET_ITEM_UOM (v_org_id number, v_item_id number) return varchar2;
-- All the Procedures used in this package
Procedure Validate_Organization;
Procedure Validate_Item;
Procedure Validate_Item_SubInv;
Procedure Validate_Plan_Method;
Procedure Validate_Uom;
Procedure Validate ;
Procedure Populate_Interface;
-- Procedure Run (errcode out number, errbuf out varchar2);
END A07_REPLENISHMENT_INT;
/
CREATE OR REPLACE PACKAGE BODY A07_REPLENISHMENT_INT AS
Function CHK_ORG_EXISTS (org_id mtl_parameters.organization_id%type) return boolean
Is
cnt number;
Begin
Select count(*) into cnt
From hr_all_organization_units
where organization_id = org_id;
if cnt <> 0
then
return true;
Else
return false;
End if;
End;
Function CHK_ITEM_EXISTS (item_id mtl_system_items_b.inventory_item_id%type,
v_org_id mtl_system_items_b.organization_id%type) return boolean
Is
cnt number;
Begin
Select count(*) into cnt
From mtl_system_items_b
Where inventory_item_id = item_id
And organization_id = v_org_id;
if cnt <> 0
then
return true;
else
return false;
end if;
End;
Function CHK_ITEM_SUBINV (v_item_id number, v_subinv varchar2, v_org_id number) return boolean
Is
cnt number;
Begin
Select count(*) into cnt
From mtl_item_sub_inventories
Where inventory_item_id = v_item_id
and secondary_inventory = v_subinv
and organization_id = v_org_id;
if cnt <> 0
then
return true;
else
return false;
end if;
End;
Function GET_ITEM_PLAN (v_org_id number, v_item_id number, v_subinv varchar2) return number
Is
ipm number;
Begin
Select inventory_planning_code into ipm
From mtl_item_sub_inventories
Where inventory_item_id = v_item_id
And organization_id = v_org_id
And secondary_inventory = v_subinv;
Return ipm;
Exception
When NO_DATA_FOUND then
return -2;
When OTHERS then
return -1;
End;
Function GET_ITEM_UOM (v_org_id number, v_item_id number) return varchar2
is
uom varchar2(3);
Begin
Select primary_uom_code into uom
From mtl_system_items_b
Where organization_id = v_org_id
And inventory_item_id = v_item_id;
Return uom;
Exception
When NO_DATA_FOUND then
return NULL;
When OTHERS then
return NULL;
End;
Procedure Validate_Organization
As
Cursor c_org is Select distinct organization_id from A07_replenish_interface;
v_org c_org%rowtype;
Begin
Open c_org;
Loop
Fetch c_org into v_org;
Exit when c_org%NOTFOUND;
Begin
If chk_org_exists(v_org.organization_id)
then
null;
Else
Update A07_replenish_interface
Set error_flag = -1,
error_description = 'Organization does not exist'
Where organization_id = v_org.organization_id;
End If;
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
update A07_replenish_interface
Set error_flag = v_sqlcode,
error_description = v_sqlerrm
where organization_id = v_org.organization_id;
End;
End Loop;
Close c_org;
Commit;
fnd_file.put_line(FND_FILE.OUTPUT,'Validate Organization completed successfully');
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
fnd_file.put_line(FND_FILE.LOG,'In the process : Validate Organization');
fnd_file.put_line(FND_FILE.LOG,'Error Code is ' || v_sqlcode);
fnd_file.put_line(FND_FILE.LOG,'Error Mesg is ' || v_sqlerrm);
End Validate_Organization;
Procedure Validate_Item
As
Cursor c_item is Select inventory_item_id, organization_id from A07_replenish_interface
Where error_flag is null;
v_item c_item%rowtype;
Begin
Open c_item;
Loop
Fetch c_item into v_item;
Exit when c_item%NOTFOUND;
Begin
If chk_item_exists(v_item.inventory_item_id, v_item.organization_id)
then
null;
else
Update A07_replenish_interface
Set error_flag = -2,
error_description = 'Item does not exist'
Where organization_id = v_item.organization_id
And inventory_item_id = v_item.inventory_item_id;
End If;
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
update A07_replenish_interface
Set error_flag = v_sqlcode,
error_description = v_sqlerrm
Where organization_id = v_item.organization_id
And inventory_item_id = v_item.inventory_item_id;
End;
End Loop;
Close c_item;
Commit;
fnd_file.put_line(FND_FILE.OUTPUT,'Validate Item completed successfully');
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
fnd_file.put_line(FND_FILE.LOG,'In the process : Validate Item');
fnd_file.put_line(FND_FILE.LOG,'Error Code is ' || v_sqlcode);
fnd_file.put_line(FND_FILE.LOG,'Error Mesg is ' || v_sqlerrm);
End Validate_Item;
Procedure Validate_Item_SubInv
As
Cursor c_item is Select inventory_item_id, subinventory_code,organization_id
from A07_replenish_interface
Where error_flag is null;
v_item c_item%rowtype;
Begin
Open c_item;
Loop
Fetch c_item into v_item;
Exit when c_item%NOTFOUND;
Begin
If chk_item_subinv(v_item.inventory_item_id,v_item.subinventory_code,v_item.organization_id)
then
null;
else
Update A07_replenish_interface
Set error_flag = -2,
error_description = 'Item does not exist in suninventory'
Where organization_id = v_item.organization_id
And inventory_item_id = v_item.inventory_item_id
And subinventory_code = v_item.subinventory_code;
End If;
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
update A07_replenish_interface
Set error_flag = v_sqlcode,
error_description = v_sqlerrm
Where organization_id = v_item.organization_id
And inventory_item_id = v_item.inventory_item_id
And subinventory_code = v_item.subinventory_code;
End;
End Loop;
Close c_item;
Commit;
fnd_file.put_line(FND_FILE.OUTPUT,'Validate Item completed successfully');
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
fnd_file.put_line(FND_FILE.LOG,'In the process : Validate Item');
fnd_file.put_line(FND_FILE.LOG,'Error Code is ' || v_sqlcode);
fnd_file.put_line(FND_FILE.LOG,'Error Mesg is ' || v_sqlerrm);
End Validate_Item_SubInv;
Procedure Validate_Plan_Method
As
Cursor c_plan is Select organization_id, inventory_item_id, subinventory_code, count_method
from A07_replenish_interface
Where error_flag is null;
v_plan c_plan%rowtype;
ipm number;
Begin
Open c_plan;
Loop
Fetch c_plan into v_plan;
Exit when c_plan%NOTFOUND;
ipm := get_item_plan(v_plan.organization_id, v_plan.inventory_item_id,v_plan.subinventory_code);
Begin
If ipm = 1
then
if v_plan.count_method != 2
then
Update A07_replenish_interface
Set error_flag = -3,
error_description = 'Planning method and Counting Method mismatch'
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id
and subinventory_code = v_plan.subinventory_code;
end if;
Elsif ipm = 2 then
if v_plan.count_method != 3
then
Update A07_replenish_interface
Set error_flag = -3,
error_description = 'Planning method and Counting Method mismatch'
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id
and subinventory_code = v_plan.subinventory_code;
end if;
Elsif ipm = 6 then
if v_plan.count_method != 1
then
Update A07_replenish_interface
Set error_flag = -3,
error_description = 'Planning method and Counting Method mismatch'
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id
and subinventory_code = v_plan.subinventory_code;
end if;
Else
null;
End If;
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
update A07_replenish_interface
Set error_flag = v_sqlcode,
error_description = v_sqlerrm
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id
And subinventory_code = v_plan.subinventory_code;
End;
End Loop;
Close c_plan;
Commit;
fnd_file.put_line(FND_FILE.OUTPUT,'Validate Plan Method completed successfully');
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
fnd_file.put_line(FND_FILE.LOG,'In the process : Validate Plan Method');
fnd_file.put_line(FND_FILE.LOG,'Error Code is ' || v_sqlcode);
fnd_file.put_line(FND_FILE.LOG,'Error Mesg is ' || v_sqlerrm);
End Validate_Plan_Method;
Procedure Validate_UOM
As
Cursor c_uom is Select distinct inventory_item_id, organization_id
from A07_replenish_interface
Where error_flag is null;
v_plan c_uom%rowtype;
uom varchar2(3);
Begin
Open c_uom;
Loop
Fetch c_uom into v_plan;
Exit when c_uom%NOTFOUND;
uom := get_item_uom(v_plan.organization_id, v_plan.inventory_item_id);
Begin
If uom is NULL
then
Update A07_replenish_interface
Set error_flag = -4,
error_description = 'UOM not found'
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id;
Else
Update A07_replenish_interface
Set UOM_CODE = uom
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id;
End If;
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
update A07_replenish_interface
Set error_flag = v_sqlcode,
error_description = v_sqlerrm
Where organization_id = v_plan.organization_id
And inventory_item_id = v_plan.inventory_item_id;
End;
End Loop;
Close c_uom;
Commit;
fnd_file.put_line(FND_FILE.OUTPUT,'Validate Plan Method completed successfully');
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
fnd_file.put_line(FND_FILE.LOG,'In the process : Validate Plan Method');
fnd_file.put_line(FND_FILE.LOG,'Error Code is ' || v_sqlcode);
fnd_file.put_line(FND_FILE.LOG,'Error Mesg is ' || v_sqlerrm);
End Validate_UOM;
Procedure Validate
As
Begin
Update A07_replenish_interface
Set error_flag = NULL, error_description = NULL;
-- Update the organization_ids to the respective organization
Validate_organization;
-- Check if the items exist for the given organization in MTL_SYSTEM_ITEMS_B
Validate_item;
-- Check if the organization costing method and the item costing method are same.
Validate_Plan_Method;
-- Check if the item is existing in the subinventory
Validate_Item_SubInv;
-- To Check if the item cost already exists in the CST_ITEM_COSTS
Validate_UOM;
Update A07_replenish_interface
Set error_flag = 0, error_description = 'Clean Data'
Where error_flag is null;
Commit;
fnd_file.put_line(FND_FILE.OUTPUT,'Validate Interface completed successfully');
Exception
When OTHERS then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
Rollback;
fnd_file.put_line(FND_FILE.LOG,'In the process : Validate Interface');
fnd_file.put_line(FND_FILE.LOG,'Error Code is ' || v_sqlcode);
fnd_file.put_line(FND_FILE.LOG,'Error Mesg is ' || v_sqlerrm);
End Validate;
Procedure Populate_Interface
As
CURSOR C1 IS SELECT * FROM A07_REPLENISH_INTERFACE;
Cursor c_hdr is Select distinct organization_id, subinventory_code, count_date
from A07_replenish_interface
where error_flag = 0;
Cursor c_lines (org_id in number,subinv_code in varchar2) is
Select inventory_item_id, organization_id
from A07_replenish_interface
where error_flag = 0;
h_id number;
l_id number;
h_name varchar2(15);
V_HDR C_HDR%ROWTYPE;
Begin
Open c_hdr;
Loop <<OUTER>>
Fetch c_hdr into v_hdr;
Exit when c_hdr%NOTFOUND;
Select mtl_replenish_headers_s.nextval into h_id from dual;
h_name := 'SRT' || h_id ;
END LOOP;
FOR REC IN C1
LOOP
Insert into mtl_replenish_headers_int
( REPLENISHMENT_HEADER_ID,REPLENISHMENT_COUNT_NAME,COUNT_DATE, LAST_UPDATE_DATE,CREATION_DATE,
CREATED_BY ,LAST_UPDATED_BY,ORGANIZATION_ID,SUBINVENTORY_CODE, PROCESS_STATUS,PROCESS_MODE )
Values
(REC.REPLENISHMENT_HEADER_ID,REC.REPLENISHMENT_COUNT_NAME,REC.COUNT_DATE,REC.LAST_UPDATE_DATE,REC.CREATION_DATE,
REC.CREATED_BY,REC.LAST_UPDATED_BY,REC.ORGANIZATION_ID,REC.SUBINVENTORY_CODE,REC.PROCESS_STATUS,REC.PROCESS_MODE);
END LOOP;
END;
END A07_REPLENISHMENT_INT;
