function get_price(p_in_transaction_id in number) return number is
l_price number;
l_exists varchar2(1);
l_msg varchar2(200);
begin
begin
select 'Y'
into l_exists
from xla.xla_transaction_entities xte
where nvl(xte.source_id_int_1, -99) = p_in_transaction_id
and rownum = 1;
exception
when others then
l_exists := 'N';
l_msg := '存在未创建会计分录的事务处理 ID:' || p_in_transaction_id;
fnd_file.put_line(fnd_file.log, l_msg);
end;
if l_exists = 'Y' then
select sum(nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0))
into l_price
from xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
where xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.application_id = 555
and xe.event_id = xah.event_id
and xe.application_id = xah.application_id
and xe.entity_id = xte.entity_id
and xe.application_id = xte.application_id
and xte.application_id = xah.application_id
and xte.entity_id = xah.entity_id
and xal.accounting_class_code = 'DEFERRED_COGS'--'COST_OF_GOODS_SOLD'
and nvl(xte.source_id_int_1, -99) = p_in_transaction_id;
end if;
return nvl(l_price, 0);
end get_price;
l_price number;
l_exists varchar2(1);
l_msg varchar2(200);
begin
begin
select 'Y'
into l_exists
from xla.xla_transaction_entities xte
where nvl(xte.source_id_int_1, -99) = p_in_transaction_id
and rownum = 1;
exception
when others then
l_exists := 'N';
l_msg := '存在未创建会计分录的事务处理 ID:' || p_in_transaction_id;
fnd_file.put_line(fnd_file.log, l_msg);
end;
if l_exists = 'Y' then
select sum(nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0))
into l_price
from xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
where xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.application_id = 555
and xe.event_id = xah.event_id
and xe.application_id = xah.application_id
and xe.entity_id = xte.entity_id
and xe.application_id = xte.application_id
and xte.application_id = xah.application_id
and xte.entity_id = xah.entity_id
and xal.accounting_class_code = 'DEFERRED_COGS'--'COST_OF_GOODS_SOLD'
and nvl(xte.source_id_int_1, -99) = p_in_transaction_id;
end if;
return nvl(l_price, 0);
end get_price;