Help in Branch information SQL

Kauan_Bohn

I’m having trouble finding fields related to "branch" (in my query, this field is represented by different paths:
rcta.attribute15 AS BRANCH,inv.organization_code AS BRANCH_CODE,inv.organization_name AS BRANCH_NAME).

However, I need to find the municipality of the branch and also its code (CNPJ). I’m able to find the codes, but not the descriptions.

I'd like to share this query with my fellow Brazilian colleagues, as it might be useful to some.

I would appreciate any guidance on where to find this information.

WITH TotalInvoice AS (
SELECT
customer_trx_id,
org_id,
warehouse_id,
FINAL_DISCHARGE_LOCATION_ID,
SUM(NVL(gross_extended_amount, extended_amount)) AS total_funct_curr_amt
FROM ra_customer_trx_lines_all
WHERE line_type = 'LINE'
GROUP BY customer_trx_id, org_id, warehouse_id, FINAL_DISCHARGE_LOCATION_ID
)
SELECT
rcta.trx_number AS TRANSACAO,
jfdr.doc_num AS NF,
TO_CHAR(jfdr.FISCAL_DOC_DATE, 'DD/MM/YYYY') AS DATA_NF,
jfdr.series AS SERIE,
jfdr.status,
jfdla.line_amt AS VALOR_NF,
jfdr.rps_number AS RPS,
jfdr.cancel_reason AS MOTIVO_CANCEL,
fabu.bu_name AS EMPRESA,
hp.party_name AS CLIENTE,
hp.state AS ESTADO_CLIENTE,
hp.city AS CIDADE_CLIENTE,
jfdla.product_code AS ITEM,
jfdla.product_description AS DESCRICAO_ITEM,
jfdla.bill_to_location_id,
jfdla.final_discharge_location_id AS final_discharge_location_id2 ,
rctt.name AS TRANSACTION_TYPE_NAME,
ti.warehouse_id,
ti.FINAL_DISCHARGE_LOCATION_ID,
rcta.attribute15 AS FILIAL,

inv.organization_code AS CODIGO_FILIAL,
inv.organization_name AS NOME_FILIAL,
CASE 
    WHEN SUM(CASE WHEN zl.tax_regime_code = 'CSRF' THEN 1 ELSE 0 END) > 0 
    THEN 'Retida' 
    ELSE 'Transacional' 
END AS TIPO_NF,

-- Valores de impostos (CONFINS, CSRF, ICMS, IPI, ISS, PIS)
SUM(CASE WHEN zl.tax_regime_code = 'CONFINS' THEN ABS(zl.taxable_amt) ELSE 0 END) AS CONFINS_VALOR_BASE_IMPOSTO,
MAX(CASE WHEN zl.tax_regime_code = 'CONFINS' THEN ABS(zl.tax_rate) ELSE NULL END) AS CONFINS_TAXA_IMPOSTO,
ROUND(SUM(CASE WHEN zl.tax_regime_code = 'CONFINS' THEN (zl.tax_rate * ti.total_funct_curr_amt) / 100 ELSE 0 END), 2) AS CONFINS_VALOR_IMPOSTO

FROM
JG_FSCL_DOC_RELATIONS_ALL jfdr
JOIN JG_FSCL_DOC_LINES_ALL jfdla ON jfdr.doc_hdr_id = jfdla.doc_hdr_id
JOIN RA_CUSTOMER_TRX_ALL rcta ON jfdr.trx_id = rcta.customer_trx_id
JOIN RA_CUST_TRX_TYPES_ALL rctt ON rcta.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
JOIN FUN_ALL_BUSINESS_UNITS_V fabu ON fabu.bu_id = jfdla.org_id
JOIN HZ_PARTIES hp ON rcta.sold_to_party_id = hp.party_id
JOIN ZX_LINES zl ON rcta.customer_trx_id = zl.trx_id
JOIN TotalInvoice ti ON rcta.customer_trx_id = ti.customer_trx_id
AND rcta.org_id = ti.org_id
JOIN RA_CUSTOMER_TRX_LINES_ALL rctla ON rcta.customer_trx_id = rctla.customer_trx_id
JOIN egp_system_items_b esib ON rctla.warehouse_id = esib.organization_id
JOIN INV_ORGANIZATION_DEFINITIONS_V inv ON esib.organization_id = inv.organization_id

GROUP BY
rcta.trx_number,
jfdr.doc_num,
jfdr.rps_number,
jfdla.product_code,
jfdla.line_amt,
jfdla.product_fisc_classification,
jfdr.series,
jfdr.status,
jfdr.cancel_reason,
fabu.bu_name,
hp.party_name,
hp.state,
hp.city,
ti.total_funct_curr_amt,
jfdla.product_description,
rcta.attribute15,
rctt.name,
jfdr.FISCAL_DOC_DATE,
ti.warehouse_id,
ti.FINAL_DISCHARGE_LOCATION_ID,
inv.organization_code,
inv.organization_name,
jfdla.bill_to_location_id,
jfdla.FINAL_DISCHARGE_LOCATION_ID
ORDER BY
rcta.trx_number;

MandeepGupta

Hi @Kauan_Bohn ,

You need to explore below path:

INV_ORGANIZATION_DEFINITIONS_V -> LEGAL_ENTITY join to XLE_ENTITY_PROFILES.NAME
and then XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID should map to XLE_REGISTRATIONS.SOURCE_ID

Can you try this?

Thanks.

Kauan_Bohn

@MandeepGupta,

This worked perfectly, thank you very much for your help, it was crucial to my analysis.

MandeepGupta

Hi,

Are you looking for description of rcta.attribute15?

Thanks.

Kauan_Bohn

No, rcta.attribute15 and inv.organization_name are my descriptions.

I need to find the municipality where the branch is established, the Brazilian company registration number (similar to a tax ID for businesses), and the final unloading location (where the service was provided).

Kauan_Bohn

I need the description of:

jfdla.bill_to_location_id
jfdla.final_discharge_location_id

MandeepGupta

Can you check in hr_locations_all and join with location_id?

Thanks.

Kauan_Bohn

Yeah, it works for the "FINAL_DISCHARGE_LOCATION_ID" in my first query, like this:

JOIN hr_locations_all hla ON hla.location_id = ti.FINAL_DISCHARGE_LOCATION_ID -- Adicionando o join com hr_locations_all

But i still miss the Brazilian company registration number. That is composed with 14 characteres, like this:

XX.XXX.XXX/0001-XX

I have this information twice in my query (it is the same):

rcta.attribute15 AS FILIAL,

inv.organization_name AS NOME_FILIAL

MandeepGupta

Hi @Kauan_Bohn ,

Please check XLE_REGISTRATIONS table.

Thanks.

Kauan_Bohn

Hi @MandeepGupta,

Indeed, the table you mentioned has the necessary columns, but I couldn't link them to any, and when trying with this join, it doesn't return any data:

vJOIN XLE_REGISTRATIONS xl ON xl.location_id = hla.location_id  

OR

JOIN XLE_REGISTRATIONS xl ON ti.FINAL_DISCHARGE_LOCATION_ID