Help with Receivables transaction query

Kauan_Bohn

I'm creating a transaction attributes report, and I need to bring the CNPJ (Brazilian company identification number). I have two queries, which I will leave below, but I can't relate them because the LEGAL_ENTITY_ID column doesn't return any data. Does anyone have knowledge of this table?

Q1

WITH ICMS_CALC AS (
SELECT
TRX_ID,
MAX(CASE
WHEN TAX_REGIME_CODE = 'ICMS' AND REGEXP_LIKE(TAX_RATE, '^[0-9.]+$')
THEN TO_NUMBER(TAX_RATE)
ELSE NULL
END) AS ICMS_TAX_RATE,
MAX(CASE
WHEN TAX_REGIME_CODE = 'ICMS' AND REGEXP_LIKE(TAX_RATE, '^[0-9.]+$')
THEN (TO_NUMBER(TAX_RATE) / 100) * LINE_AMT
ELSE 0
END) AS ICMS_TAX_VALUE
FROM ZX_LINES
WHERE TAX_REGIME_CODE = 'ICMS'
GROUP BY TRX_ID
)
SELECT
hou.name,
rbsa.name source_name,
rctta.name transaction_name,
rcta.trx_number num_transacao,
to_char(rcta.trx_date, 'DD/MM/YYYY') data_transacao,
r.doc_num num_nf,
R.SERIES AS SERIE,
R.STATUS AS STATUS,
to_char(r.fiscal_doc_date, 'DD/MM/YYYY') data_nf,
rcta.complete_flag transacao_completa,
nvl(rcta.attribute1, r.fiscal_doc_key) chave_acesso,
rcta.attribute4 nfse_sub,
rcta.attribute5 numero_rps,
rcta.attribute6 serie_rps,
rcta.attribute7 tipo_rps,
rcta.attribute15 filial,
rcta.attribute2 divisao,
rcta.attribute3 negocio,
rcta.attribute8 centro_resultado,
(
SELECT description
FROM fnd_flex_values_vl ffv
WHERE ffv.flex_value = rcta.attribute8
AND ffv.flex_value_set_id IN (
SELECT DISTINCT default_value_set_id
FROM fnd_kf_segments_b
WHERE segment_code = 'Centro Resultado'
AND column_name = 'SEGMENT5'
)
) AS centro_resultado_desc,
RCTA.ATTRIBUTE11 AS CCO,
(
SELECT DESCRIPTION
FROM FND_FLEX_VALUES_VL FFV
WHERE FFV.FLEX_VALUE = RCTA.ATTRIBUTE11
AND FFV.FLEX_VALUE_SET_ID IN (
SELECT DISTINCT DEFAULT_VALUE_SET_ID
FROM FND_KF_SEGMENTS_B
WHERE SEGMENT_CODE = 'Conta Contabil'
AND COLUMN_NAME = 'SEGMENT6'
)
) AS CCO_DESC,
rcta.attribute_number1 tp_registro,
rcta.attribute12 correlation_id,
rcta.attribute9 loc_ini_prestacao,
rcta.attribute13 loc_fim_prestacao,
rcta.attribute14 fat_cobranca,
to_char(rcta.attribute_date1, 'DD/MM/YYYY') dt_prev_entrega,
rcta.attribute10 tipo_cte,
jfdla.line_amt AS VALOR_NF,
JFDLA.PRODUCT_CODE AS ITEM,
JFDLA.PRODUCT_DESCRIPTION AS DESCRICAO_ITEM,
hp.party_name cliente,
hp.party_number numero_cliente,
zl.ICMS_TAX_RATE,
zl.ICMS_TAX_VALUE,
-- Adicionando o CNPJ do cliente
(
SELECT DISTINCT
zpti.tax_payer_number
FROM zx_party_taxpayer_idntfs zpti
JOIN zx_party_tax_profile zptp ON zpti.entity_id = zptp.party_tax_profile_id
JOIN hz_party_sites hpsx ON zptp.party_id = hpsx.party_site_id
WHERE zpti.reporting_type_code = 'ORA_BR_CNPJ'
AND zptp.party_type_code = 'THIRD_PARTY_SITE'
AND zptp.site_flag = 'Y'
AND trunc(sysdate) BETWEEN NVL(zpti.effective_from, trunc(sysdate) - 1)
AND NVL(zpti.effective_to, trunc(sysdate + 1))
AND hpsx.party_id = hp.party_id
AND ROWNUM = 1
) AS cnpj,
-- Concatenando empresa e filial para formar a FK
hou.name || rcta.attribute15 AS FK
FROM
ra_customer_trx_all rcta
JOIN
ra_cust_trx_types_all rctta ON rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
JOIN
ra_batch_sources_all rbsa ON rcta.batch_source_seq_id = rbsa.batch_source_seq_id
JOIN
hr_organization_units hou ON rcta.org_id = hou.organization_id
LEFT JOIN
jg_fscl_doc_relations_all r ON rcta.customer_trx_id = r.trx_id
LEFT JOIN
jg_fscl_doc_lines_all jfdla ON r.doc_hdr_id = jfdla.doc_hdr_id
JOIN
hz_cust_accounts hca ON rcta.bill_to_customer_id = hca.cust_account_id
JOIN
hz_parties hp ON hca.party_id = hp.party_id
LEFT JOIN
ICMS_CALC zl ON rcta.customer_trx_id = zl.trx_id
WHERE
rcta.trx_date BETWEEN :DataInicial AND :DataFinal
ORDER BY
rcta.trx_date DESC

Q2

SELECT
inv.organization_code AS CODIGO_FILIAL,
xl.registration_number AS CNPJ_FILIAL,
inv.organization_name AS NOME_FILIAL
FROM
INV_ORGANIZATION_DEFINITIONS_V inv
JOIN
XLE_ENTITY_PROFILES ep ON inv.legal_entity = ep.legal_entity_id
JOIN
XLE_REGISTRATIONS xl ON ep.legal_entity_id = xl.source_id
ORDER BY
inv.organization_name

MandeepGupta

You mean legal_entity_id is blank in ra_customer_trx_all ?

Thanks.

Kauan_Bohn

I have this information on ra_customer_trx_all, but when i link them, it's no data found. 

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/61776#Comment_61776

My apologies. I still didnt understand. You get the legal entity id from your Q1 but when you link it to legal_entity_id from Q2 (XLE_ENTITY_PROFILES), you don't get any data?

Thanks.  

Kauan_Bohn

Yes, that's right, when running a query in isolation it returns values.


I thought of an alternative path.

I tried to do this through the data model, linking the FK of hou.name || rcta.attribute15 with Query 2, but I am unable to establish this connection, even though it is the same.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/61780#Comment_61780

can you share sample data for both? hou.name || rcta.attribute15 and  

inv.organization_code AS CODIGO_FILIAL,

xl.registration_number AS CNPJ_FILIAL,

inv.organization_name AS NOME_FILIAL

Also, can you link from q1 to q2 instead from q2 to q1.

Thanks.

Kauan_Bohn

Sure, I will attach it.

But it doesn't work for me

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/61783#Comment_61783

Thanks for sharing the details but can you share the value of "FK" from Q1. Also, can't you select legal_entity_id as an attribute in Q1 and Q2 and do a direct join?

Thanks.

Kauan_Bohn

Check this:

What i have to do to link this 2 queries in data set?