Function to calculate Item Cost
Introduction
This Post illustrates the steps required Function to calculate Item Cost in Oracle EBS R12.
Script to Function to calculate Item Cost.
CREATE OR REPLACE FUNCTION XXTTK_ITEM_COST (P_INV_ITEM_ID IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,P_INV_ORG_ID IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE )
RETURN NUMBER
AS
L_PERIOD gmf_period_statuses.PERIOD_CODE%TYPE;
L_COST NUMBER;
BEGIN
BEGIN
select max(c.period_code)INTO L_PERIOD FROM cm_cmpt_dtl_vw a,
mtl_system_items b,
gmf_period_statuses c,mtl_lot_numbers mln
WHERE 1 = 1 –a.organization_id = :org_id
— AND b.organization_id >= in ( 250,178)
AND a.inventory_item_id = b.inventory_item_id
AND a.period_id = c.period_id
AND a.inventory_item_id = mln.inventory_item_id
AND a.organization_id = mln.organization_id
AND a.inventory_item_id=P_INV_ITEM_ID;
dbms_output.put_line(‘L_PERIOD ‘||L_PERIOD);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’No Data Found While Capturing the item Cost Period’);
dbms_output.put_line(‘No Data Found While Capturing the item Cost Period’);
end;
IF L_PERIOD IS NOT NULL THEN
BEGIN
SELECT SUM (cmpnt_cost) INTO L_COST
FROM cm_cmpt_dtl_vw a,
mtl_system_items b,
gmf_period_statuses c
–mtl_lot_numbers mln
WHERE 1 = 1 –a.organization_id = :org_id
AND b.organization_id = P_INV_ORG_ID –1238
AND a.inventory_item_id = b.inventory_item_id
AND a.period_id = c.period_id
— AND a.inventory_item_id = mln.inventory_item_id
–AND a.organization_id = mln.organization_id
AND c.period_code =L_PERIOD
AND b.inventory_item_id=P_INV_ITEM_ID
GROUP BY a.inventory_item_id,
segment1,
a.period_id,
b.description,
primary_uom_code;
dbms_output.put_line(‘L_COST ‘||L_COST);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’No Data Found While Capturing the item Cost Period’);
dbms_output.put_line(‘No Data Found While Capturing the item Cost Period’);
end;
end if;
Return L_COST;
EXCEPTION WHEN OTHERS THEN
ND_FILE.PUT_LINE(FND_FILE.LOG,’Error IN Main XXTTK_ITEM_COST function’);
dbms_output.put_line(‘Error IN Main XXTTK_ITEM_COST function’);
Return NULL;
end;
Summary
This Post described the script Function to calculate Item Cost in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
