欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

获取销售订单行对应的总成本

程序员文章站 2022-06-14 08:06:11
...

--获取销售订单行对应的总成本

FUNCTION get_line_item_cost(p_inventory_item_id IN NUMBER,

p_organization_id   IN NUMBER) RETURN VARCHAR2 IS

l_period_id    NUMBER;

l_cost_type_id NUMBER;

l_total_cost   NUMBER;

BEGIN

SELECT gps.period_id,

glv.cost_type_id

INTO l_period_id,

l_cost_type_id

FROM org_organization_definitions ood,

gmf_ledger_valuation_methods glv,

gmf_calendar_assignments     gca,

gmf_period_statuses          gps --取period_id 

WHERE 1 = 1

AND glv.legal_entity_id = ood.legal_entity

AND ood.organization_id = p_organization_id

AND gca.cost_type_id = glv.cost_type_id

AND gca.legal_entity_id = ood.legal_entity

AND gps.legal_entity_id = ood.legal_entity

AND SYSDATE BETWEEN gps.start_date AND gps.end_date;

       

--取总成本

SELECT SUM(v.cmpnt_cost)

INTO l_total_cost

FROM cm_cmpt_dtl_vw v

WHERE v.inventory_item_id = p_inventory_item_id

AND v.organization_id = p_organization_id

AND v.period_id = l_period_id

AND v.cost_type_id = l_cost_type_id

AND v.cost_level = 0;

RETURN l_total_cost;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END get_line_item_cost;

相关标签: EBS PLSQL