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;
Code for Item Replenishment Interface
Share this:
Oracle WorkFlow Interview Questions
1. How to find the latest version of a current workflow process, and how to revert to the old version of workflow
2. Name five very important base tables of oracle workflow and their significance.
3. Name five oracle workflow apis and their significance and use
4. How would you clear stuck or deferred workflows and what is meant by a deferred workflow
5. What is the difference between workflow wait activity and block activity, what is the api which is used in this regard to put a wait and to put a block.
6. How would you continue the workflow which is in wait and which is in block mode, name the api which is responsible for that
7. What is access level in oracle workflow and how it is significant.
8. Apart from loading the workflow from workflow builder is there any other method of loading the workflow from database to local machine.
9. How many types of attributes are present in workflow and what is a document type attribute
10. How would you force a user to not re-assign the workflow to other user.
Share this:
Oracle 11i Apps General Questions
Share this:
List of Key Flexfields in Oracle 11i Applications
Share this:
Dual UoM in Oracle R12
Share this: