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.
