NF_Entrada_Itens: LOAD "COD_EMPRESA" & '-' & "NUM_AVISO_REC" as PK_AvisoRec, "COD_EMPRESA" & '-' & "NUM_AVISO_REC" & '-' & "NUM_SEQ" as PK_AvisoRec_Seq, //"NUM_AVISO_REC" & ' /' & "NUM_SEQ" as PK_AvisoRec_Gao, //"NUM_AVISO_REC" as PK_AvisoRec_Gao2, "NUM_SEQ" as NFE_Seq, COD_ITEM as NFE_Item, DEN_ITEM as NFE_Den_Item, "COD_EMPRESA" & '-' & "NUM_OC"& '-' & "COD_ITEM" as PK_OrdemCompra, num("QTD_DECLARAD_NF") as NFE_Qtde_Declarada, num("QTD_RECEBIDA") as NFE_Qtde_Recebida, num("QTD_DEVOLVID") as NFE_Devolvida, num("QTD_REJEIT") as NFE_Rejeitada, num("QTD_LIBER" ) as NFE_Liberada, num("QTD_LIBER_EXCEP") as NFE_Liberada_Expec, num("PRE_UNIT_NF") as NFE_Preco_Unit, num("PRE_UNIT_NF" * "QTD_DECLARAD_NF") as NFE_Preco_Total, // num("VAL_CONTABIL_ITEM") as NFE_Item_Vl_Bruto, // num("PCT_IPI_DECLARAD") as NFE_Perc_IPI, num("VAL_IPI_CALC_ITEM") as NFE_Vl_IPI, num("VAL_DESC_ITEM") as NFE_Desc_Item, //num("PCT_ICMS_ITEM_C") as NFE_Perc_Icms, num("PCT_ICMS_ITEM_D") as NFE_Perc_Icms, //num("VAL_ICMS_ITEM_C") as NFE_Vl_Icms, num("VAL_ICMS_ITEM_D") as NFE_Vl_Icms, num("VAL_FRETE") as NFE_Vl_Frete, //num("VAL_ICMS_FRETE_C") as NFE_Vl_Icms_Frete, "VAL_ICMS_FRETE_C" as NFE_Vl_Icms_Frete_Calc, num("VAL_ICMS_FRETE_D") as NFE_Vl_Icms_Frete, COD_FISCAL_ITEM as PK_GeraPagto, IES_LIBERACAO_AR as NFE_Ies_Liberacao_AR, IES_DIVERG_LISTADA as NFE_Ies_Diverg_Listada, // //if("COD_FISCAL_ITEM"='6202' or "COD_FISCAL_ITEM"='5202','S','N') as Nat_Oper_Compra_Devolvido, //if("COD_FISCAL_ITEM"='6910' or "COD_FISCAL_ITEM"='5910','S','N') as Nat_Oper_Compra_Bonificado, // COD_LOCAL_ESTOQ as NFE_Cod_Local_Estoque, COD_UNID_MED_NF as NFE_Cod_Unid_Med_NF, COD_TIP_DESPESA as NFE_Cod_Tip_Despesa, GRU_CTR_DESP_ITEM as NFE_Cod_Grupo_Despesa, COD_CLA_FISC as NFE_Classificacao_Fiscal Where Exists(PK_AvisoRec,"COD_EMPRESA" & '-' & "NUM_AVISO_REC"); SQL SELECT * FROM LOGIX."AVISO_REC"; left join LOAD "COD_ITEM" as NFE_Item, TRIM("DEN_ITEM") & ' - ' & "COD_ITEM" as NFE_Item_Nome; SQL SELECT * FROM LOGIX.ITEM; left join (NF_Entrada_Itens) LOAD "COD_EMPRESA" & '-' & "NUM_AVISO_REC" & '-' & "NUM_SEQ" as PK_AvisoRec_Seq, "COD_EMPRESA" & '-' & "NUM_AVISO_REC" as PK_AvisoRec, "VAL_PIS_D" as NFE_Vl_Pis, "VAL_COFINS_D" as NFE_Vl_Cofins, "VAL_BASE_PIS_D" as NFE_Vl_Base_Pis, "VAL_BASE_COFINS_D" as NFE_Vl_Base_Cofins, "PCT_PIS_ITEM_D" as NFE_Pct_Pis_Item, "PCT_COFINS_ITEM_D" as NFE_Pct_Cofins_Item; SQL SELECT * FROM LOGIX."AR_PIS_COFINS"; left join (NF_Entrada_Itens) LOAD "EMPRESA" & '-' & "AVISO_RECEBTO" & '-' & "SEQ_AVISO_RECEBTO" as PK_AvisoRec_Seq, "EMPRESA" & '-' & "AVISO_RECEBTO" as PK_AvisoRec, "VAL_PIS_DECLARADO" as NFE_Vl_Frete_Pis, "VAL_COFINS_DECL" as NFE_Vl_Frete_Cofins Where Exists(PK_AvisoRec_Seq,"EMPRESA" & '-' & "AVISO_RECEBTO" & '-' & "SEQ_AVISO_RECEBTO"); SQL SELECT * FROM LOGIX."SUP_FR_PIS_COFINS"; left join (NF_Entrada_Itens) LOAD COD_FISCAL as PK_GeraPagto, 'S' as Desc_GeraPagto; SQL SELECT * FROM LOGIX.COD_FISCAL_SUP WHERE DEN_COD_FISCAL LIKE 'COMPRA P%' OR DEN_COD_FISCAL LIKE 'REMESSA INDUSTRIALIZACAO%' OR DEN_COD_FISCAL LIKE 'INDL EFETUADA POR OUTRA EMPRESA%'; left join (NF_Entrada_Itens) LOAD PK_AvisoRec, PK_Fornec as NFE_Fornec, NFE_DataEntrada as NFE_Item_Dt, year(NFE_DataEntrada) as NFE_Item_Ano, month(NFE_DataEntrada) as NFE_Item_Mes Resident NF_Entrada; //left join (NF_Entrada_Itens) //LOAD // "COD_ITEM" as NFE_Item, // "COD_TIP_DESPESA" as NFE_Tipo_Despesa // Where Exists(NFE_Item,"COD_ITEM"); //SQL SELECT COD_EMPRESA,COD_ITEM,COD_TIP_DESPESA FROM item_sup; //left join (NF_Entrada_Itens) //LOAD // "COD_TIP_DESPESA" as NFE_Tipo_Despesa, // "COD_TIP_DESPESA" & '-' & "NOM_TIP_DESPESA" as NFE_Tipo_Despesa_Desc // Where Exists(NFE_Tipo_Despesa,"COD_TIP_DESPESA"); //SQL SELECT * FROM LOGIX."TIPO_DESPESA" Where "COD_EMPRESA" = '01'; //// left join (NF_Entrada_Itens) LOAD "COD_TIP_DESPESA" as NFE_Cod_Tip_Despesa, "COD_TIP_DESPESA" & '-' & "NOM_TIP_DESPESA" as NFE_Tipo_Despesa_Desc Where Exists(NFE_Cod_Tip_Despesa,"COD_TIP_DESPESA"); SQL SELECT * FROM LOGIX."TIPO_DESPESA" Where "COD_EMPRESA" = '01'; left join (NF_Entrada_Itens) LOAD "GRU_CTR_DESP" as NFE_Cod_Grupo_Despesa, "GRU_CTR_DESP" & '-' & "DEN_GRU_CTR_DESP" as NFE_Nom_Grupo_Despesa; SQL SELECT * FROM LOGIX."GRUPO_CTR_DESP" WHERE "COD_EMPRESA" = '01'; left join (NF_Entrada_Itens) LOAD "COD_EMPRESA" & '-' & "NUM_AVISO_REC" & '-' & "NUM_SEQ" as PK_AvisoRec_Seq, "COD_EMPRESA" & '-' & "NUM_AVISO_REC" as PK_AvisoRec, "VAL_ICMS_DEV_RET" as NFE_Item_Icms_ST; SQL SELECT * FROM LOGIX."AR_SUBST_TRIBUT"; // Vinicius: Criando a chave para substituir a sintética NF_Entrada_Itens2: NoConcatenate Load *, NFE_Item & '-' & NFE_Fornec as Chave_Item_Fornec Resident NF_Entrada_Itens; Drop table NF_Entrada_Itens; Rename Table NF_Entrada_Itens2 to NF_Entrada_Itens; Item_Fornec: LOAD COD_ITEM & '-' & COD_FORNECEDOR as Chave_Item_Fornec, // Vinicius: É necessário remover esses campos para não criar mais a chave sintética //"COD_ITEM" as NFE_Item, //COD_FORNECEDOR as NFE_Fornec, COD_UNID_MED_FORN as Item_Fornec_Cod_Unid_Med_Forn, FAT_CONVER_UNID as Item_Fornec_Fat_Conver_Unid; SELECT * FROM LOGIX."ITEM_FORNEC";