AP和AR关联找到对应的子分类账信息

最新推荐文章于 2022-04-28 16:42:11 发布

kelly_yun

最新推荐文章于 2022-04-28 16:42:11 发布

阅读量506

点赞数

分类专栏： FIN 文章标签： ar

本文链接：https://blog.csdn.net/kelly_yun/article/details/121850093

版权

FIN 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SELECT
(
SELECT
gdct.user_conversion_type
FROM
gl_daily_conversion_types gdct
WHERE
xal.currency_conversion_type = gdct.conversion_type
) conversion_type,
xal.currency_conversion_rate conversion_rate,
abs(sum(xdl.unrounded_accounted_dr)) * sign(rec_cur_ar.inv_line_amt) accounted_dr
into v_conversion_type,v_rate_exchange,v_RON_INV_LINE_AMT
FROM
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
xla.xla_distribution_links xdl,
xla_gl_ledgers_v xgl,
xla_lookups xlk,
ap_invoices_all aia,
ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE
xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xgl.ledger_id = xah.ledger_id
AND xgl.name = 'CT GB MRC RON'
AND xlk.lookup_code = xal.accounting_class_code
AND xlk.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xte.entity_code = 'AP_INVOICES'
AND xte.application_id = xah.application_id
AND xte.ledger_id = aia.set_of_books_id
AND xah.application_id = xdl.application_id
AND xah.event_id = xdl.event_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.ae_header_id = xdl.ae_header_id
AND nvl(xte.source_id_int_1, -99) = aia.invoice_id
AND xah.entity_id = xte.entity_id
AND xDL.SOURCE_distribution_TYPE = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND aia.invoice_id = ail.invoice_id
and aid.invoice_id = ail.invoice_Id
AND aid.invoice_line_number = ail.line_number
and AIA.invoice_id = rec_cur_ar.invoice_id
and ail.line_number = rec_cur_ar.line_number
group by
xal.currency_conversion_rate
,xal.currency_conversion_type;
V_TOTAL_IN_RON := rec_cur_ar.total_amt * v_rate_exchange;

EXCEPTION
WHEN OTHERS THEN
V_TOTAL_IN_RON := NULL;
V_RON_INV_LINE_AMT := NULL;
v_rate_exchange := null;
v_conversion_type := null;
END;
END IF;

BEGIN
V_RON_TOTAL_AMT := 0;
v_rate_exchange := 0;
v_conversion_type := null;
SELECT
(
SELECT
gdct.user_conversion_type
FROM
gl_daily_conversion_types gdct
WHERE
xal.currency_conversion_type = gdct.conversion_type
) conversion_type,

xal.currency_conversion_rate conversion_rate,
abs(sum(xdl.unrounded_accounted_cr)) * sign(rec_cur_d.total_amt) accounted_cr
into v_conversion_type,v_rate_exchange,V_RON_TOTAL_AMT
FROM
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
xla.xla_distribution_links xdl,
xla_gl_ledgers_v xgl,
xla_lookups xlk,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL DIS,
ra_customer_trx_lines_all rctl
WHERE
xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xgl.ledger_id = xah.ledger_id
AND xgl.name = 'CT GB MRC RON'
AND xlk.lookup_code = xal.accounting_class_code
AND xlk.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xte.entity_code = 'TRANSACTIONS'
AND xte.application_id = xah.application_id
AND xte.ledger_id = RCTA.set_of_books_id
AND xah.application_id = xdl.application_id
AND xah.event_id = xdl.event_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.ae_header_id = xdl.ae_header_id
AND nvl(xte.source_id_int_1, -99) = RCTA.CUSTOMER_TRX_ID
AND xah.entity_id = xte.entity_id
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DIS.CUST_TRX_LINE_GL_DIST_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND rcta.customer_trx_id = rctl.customer_trx_id
and dis.customer_trx_line_Id = rctl.customer_trx_line_Id
and dis.customer_trx_id = rctl.customer_trx_id
and rctl.customer_trx_line_id = rec_cur_d.customer_trx_line_id
and rctl.customer_trx_id = rec_cur_d.customer_trx_id
group by rcta.customer_trx_id
,rctl.customer_trx_line_Id
,xal.currency_conversion_rate
,xal.accounting_class_code
,xal.currency_conversion_type;
EXCEPTION
WHEN OTHERS THEN
V_RON_TOTAL_AMT := null;
v_rate_exchange := null;
v_conversion_type := null;