SQLize Online / PHPize Online  /  SQLtest Online

SELECT NUMERO_COMPANIA, MAX(CENTRO_BENEFICIO) AS CENTRO_BENEFICIO, SAP_DOC_CLASS, NUMERO_FACTURA, NUMERO_NOTA_CREDITO, DOC_CONTABLE, FECHA_CONTABLE, FECHA_EMISION, NIT, ID_VENDOR, DENOMINACION_SOCIAL, COD_PRODUCTO, DESCIPCION_PROD, SUM(PRECIO_UNITARIO) AS PRECIO_UNITARIO, MAX(CANTIDAD_ARTICULO) AS CANTIDAD_ARTICULO, MAX(UNIDAD_MEDIDA) AS UNIDAD_MEDIDA, SUM(IMP_CONSUMO) AS IMP_CONSUMO, SUM(IMP_AUT_SINISC) AS IMP_AUT_SINISC, SUM(IMP_ESPECIFICO) AS IMP_ESPECIFICO, SUM(IMP_GRAVADO_IVA) AS IMP_GRAVADO_IVA, SUM(IMP_EXENTO_IVA) AS IMP_EXENTO_IVA, SUM(IMP_AUT_SIN_IVA) AS IMP_AUT_SIN_IVA, SUM(SUBTOTAL) AS SUBTOTAL, SUM(DESCUENTO) AS DESCUENTO, SUM(IMP_IVA) AS IMP_IVA, SUM(TOTAL_FACTURA) AS TOTAL_FACTURA, MAX(NO_NOTA_EXENCION) AS NO_NOTA_EXENCION, MAX(FECA_VIGH_EXENCION) AS FECA_VIGH_EXENCION FROM( SELECT M.GROUPID ,M.CMPNY_CD AS NUMERO_COMPANIA ,M.FISCAL_YR_NBR AS ANO ,M.FISCAL_PERIOD_NBR AS MES ,M.PROFIT_CNTR_ID AS CENTRO_BENEFICIO ,SUBSTR(M.PROFIT_CNTR_ID,1,4) AS STORE_NBR ,'' AS STORE_NM ,M.ACCTG_DOC_NBR AS DOC_CONTABLE ,M.DOC_TYPE_CD AS SAP_DOC_CLASS ,M.LINE_ITEM_NBR AS NUMERO_LINEA ,M.POSTG_CD ,CASE WHEN M.DOC_TYPE = 1 THEN M.REF_DOC_NBR ELSE '' END AS NUMERO_FACTURA ,CASE WHEN M.DOC_TYPE = 1 THEN '' ELSE M.REF_DOC_NBR END AS NUMERO_NOTA_CREDITO ,COALESCE(M.ASSGNMNT_NBR,'-') AS ASSGNMNT_NBR ,COALESCE(M.REF_DOC_NBR,'-') AS REF_DOC_NBR ,CASE WHEN POSTG_CD IN ('01','31') THEN 'INVOICE' WHEN POSTG_CD IN ('02','32') THEN 'REVERSE CREDIT MEMO' WHEN POSTG_CD IN ('11','21') THEN 'CREDIT MEMO' WHEN POSTG_CD IN ('12','22') THEN 'REVERSE INVOICE' WHEN POSTG_CD IN ('04') THEN 'OTHER RECEIVABLES' WHEN POSTG_CD IN ('05') THEN 'OUTGOING PAYMENT' WHEN POSTG_CD IN ('07','17','37') THEN 'OTHER CLEARINGS' WHEN POSTG_CD IN ('08','18','28','38') THEN 'PAYMENT CLEARING' WHEN POSTG_CD IN ('14','34') THEN 'OTHER PAYABLES' WHEN POSTG_CD IN ('15','35') THEN 'INCOMING PAYMENT' WHEN POSTG_CD IN ('29') THEN 'SPECIAL G/L DEBIT' WHEN POSTG_CD IN ('25') THEN 'OUTHOING PAYMENT' WHEN POSTG_CD IN ('27') THEN 'CLEARING' WHEN POSTG_CD IN ('39') THEN 'SPECIAL G/L CREDIT' ELSE '' END AS DOC_DESC ,M.DOC_POSTG_DT AS FECHA_CONTABLE ,M.DOC_ENT_DT AS FECHA_CREACION ,M.ISSU_DOC_DT AS FECHA_EMISION ,M.CLR_DT AS FECHA_LIQUIDACION ,COALESCE(M.TAX_1_NBR,'') AS NIT ,M.VENDOR_ACCT_NBR AS ID_VENDOR ,CONCAT ( COALESCE (M.VENDOR_1_NM, ''), ' ', COALESCE (M.VENDOR_2_NM, '') ) AS DENOMINACION_SOCIAL ,CASE WHEN M.CHK_QSSKZ = 1 THEN '-' ELSE BUS_PRTNR_REF_1_ID END AS CLAVE_REF_1 ,CASE WHEN M.CHK_QSSKZ = 1 THEN '-' ELSE BUS_PRTNR_REF_2_ID END AS CLAVE_REF_2 ,COALESCE(M.MATL_NBR, '') AS COD_PRODUCTO ,CASE WHEN M.CHK_QSSKZ = 1 THEN 'WITHHOLDING TAX' ELSE COALESCE(DS9.ITEM_DESC,COALESCE(M.ITEM_TXT,'')) END AS DESCIPCION_PROD ,CAST(CASE WHEN (M.ALLOC_QTY > 0) AND M.CHK_QSSKZ = 0 THEN ((M.LOCAL_CRNCY_ITEM_AMT/(1 + COALESCE(DS5.KBETR_PRO,0))) / M.ALLOC_QTY ) * M.DEBIT_CR_IND WHEN NOT (M.ALLOC_QTY > 0) AND M.CHK_QSSKZ = 0 THEN ((M.LOCAL_CRNCY_ITEM_AMT/(1 + COALESCE(DS5.KBETR_PRO,0))) ) * M.DEBIT_CR_IND ELSE 0.00 END AS DECIMAL(18,2)) AS PRECIO_UNITARIO ,CASE WHEN (M.ALLOC_QTY > 0) AND M.CHK_QSSKZ = 0 THEN M.ALLOC_QTY WHEN NOT(M.ALLOC_QTY > 0) AND M.CHK_QSSKZ = 0 THEN 1 ELSE 0.00 END AS CANTIDAD_ARTICULO ,COALESCE(M.MEAS_BASE_UNIT_CD,'') AS UNIDAD_MEDIDA ,COALESCE(NULL, 0.00) AS IMP_CONSUMO ,COALESCE(NULL, 0.00) AS IMP_AUT_SINISC ,COALESCE(NULL, 0.00) AS IMP_ESPECIFICO ,CAST(CASE WHEN DS5.TAX_IND IS NOT NULL AND DS5.TAX_IND = 1 AND M.CHK_MWSKZ = 0 THEN (M.LOCAL_CRNCY_ITEM_AMT/(1 + COALESCE(DS5.KBETR_PRO,0)) ) * M.DEBIT_CR_IND ELSE COALESCE(NULL, 0.00) END AS DECIMAL(18,2)) AS IMP_GRAVADO_IVA ,CAST(CASE WHEN (DS5.TAX_IND IS NULL OR M.CHK_MWSKZ = 1) AND M.CHK_QSSKZ = 0 THEN M.LOCAL_CRNCY_ITEM_AMT ELSE COALESCE(NULL, 0.00) END AS DECIMAL(18,2)) AS IMP_EXENTO_IVA ,COALESCE(NULL, 0.0) AS IMP_AUT_SIN_IVA ,CAST(CASE WHEN M.CHK_QSSKZ = 0 THEN (M.LOCAL_CRNCY_ITEM_AMT / (1 + COALESCE(DS5.KBETR_PRO,0))) * M.DEBIT_CR_IND ELSE 0.00 END AS DECIMAL(18,2)) AS SUBTOTAL ,COALESCE(COND_VAL_AMT,0.00) AS DESCUENTO ,COALESCE(DS5.TAX_CD,'') AS TAX_CODE ,COALESCE((DS5.TAX_RATE_AMT * 100),0) AS TAX_RATE_PCT ,CAST(CASE WHEN DS5.TAX_IND IS NOT NULL AND DS5.TAX_IND = 1 AND M.CHK_MWSKZ = 0 THEN (((M.LOCAL_CRNCY_ITEM_AMT / (1 + COALESCE(DS5.KBETR_PRO,0))) + COALESCE(COND_VAL_AMT,0.00)) * (DS5.TAX_RATE_AMT) ) * M.DEBIT_CR_IND ELSE COALESCE(NULL, 0.00) END AS DECIMAL(18,4)) AS IMP_IVA ,COALESCE(DS5.KBETR_PRO,0) AS TAX_RATE_PCT_PRO ,CAST(COALESCE( ((M.LOCAL_CRNCY_ITEM_AMT / (1 + COALESCE(DS5.KBETR_PRO,0))) * (COALESCE(DS5.KBETR_PRO,0)) ) * M.DEBIT_CR_IND ,0) AS DECIMAL(18,2)) AS IMP_IVA_PRO ,CASE WHEN M.CHK_QSSKZ = 1 THEN M.WH_TAX_CD ELSE '-' END AS WH_TAX_CD ,CASE WHEN M.CHK_QSSKZ = 1 THEN M.LOCAL_CRNCY_ITEM_AMT * M.DEBIT_CR_IND ELSE 0 END AS IMP_WH_TAX ,CAST( (M.LOCAL_CRNCY_ITEM_AMT - (CASE WHEN M.CHK_QSSKZ= 1 THEN M.LOCAL_CRNCY_ITEM_AMT ELSE 0 END)) + COALESCE(COND_VAL_AMT,0.00) + CAST(CASE WHEN DS5.TAX_IND IS NOT NULL AND DS5.TAX_IND = 1 AND M.CHK_MWSKZ = 0 THEN ((M.LOCAL_CRNCY_ITEM_AMT / (1 + COALESCE(DS5.KBETR_PRO,0))) + COALESCE(COND_VAL_AMT,0.00)) * (DS5.TAX_RATE_AMT) ELSE COALESCE(NULL, 0.00) END AS DECIMAL(18,2)) AS DECIMAL(18,2)) * M.DEBIT_CR_IND AS TOTAL_FACTURA ,COALESCE(NULL, '') AS NO_NOTA_EXENCION ,COALESCE(NULL, '') AS FECA_VIGH_EXENCION FROM( SELECT CORRELATIVO AS GROUPID ,DS6.LINE_ITEM_NBR ,DS6.LINE_ITEM_ID ,DS3.CUST_ID ,DS3.CMPNY_CD ,DS4.TAX_1_NBR ,DS3.VENDOR_ACCT_NBR ,DS4.VENDOR_1_NM ,DS4.VENDOR_2_NM ,DS3.ACCTG_DOC_NBR ,COALESCE(DS6.BILL_DOC_NBR,DS7.OBJ_CD) AS BILL_DOC_NBR ,DS3.ASSGNMNT_NBR ,DS3.REF_DOC_NBR ,DS3.FISCAL_YR_NBR ,DS3.DOC_TYPE ,DS3.ISSU_DOC_DT ,DS3.DOC_POSTG_DT ,DS3.CLR_DT ,DS3.DOC_ENT_DT ,DS3.FISCAL_PERIOD_NBR ,DS3.POSTG_CD ,DS3.DOC_TYPE_CD ,DS3.LOCAL_CRNCY_ITEM_AMT AS TOT_DMBTR ,COALESCE(DS6.TAX_CD,'') AS TAX_CD ,CASE WHEN COALESCE(DS6.TAX_CD,'') IN ('G0','R0','RE','RS','R7','RX','FI','F0') THEN 1 ELSE 0 END AS CHK_MWSKZ ,COALESCE(CAST(DS6.CLR_ENT_DT AS VARCHAR(10)),'') AS CLR_ENT_DT ,DS6.MATL_NBR AS MATL_NBR ,COALESCE(DS6.ITEM_TXT,DS3.ITEM_TXT) AS ITEM_TXT ,DS6.ALLOC_QTY AS ALLOC_QTY ,COALESCE(DS6.MEAS_BASE_UNIT_CD,'') AS MEAS_BASE_UNIT_CD ,COALESCE(DS8.PROFIT_CNTR_ID,'') AS PROFIT_CNTR_ID ,COALESCE(DS6.BUS_PRTNR_REF_1_ID,DS3.BUS_PRTNR_REF_1_ID) AS BUS_PRTNR_REF_1_ID ,COALESCE(DS6.BUS_PRTNR_REF_2_ID,DS3.BUS_PRTNR_REF_2_ID) AS BUS_PRTNR_REF_2_ID ,DS6.LOCAL_CRNCY_ITEM_AMT ,DS6.WH_TAX_CD ,DS6.DEBIT_CR_IND ,CASE WHEN DS6.WH_TAX_CD IS NOT NULL THEN 1 ELSE 0 END AS CHK_QSSKZ FROM( SELECT CUST_ID ,CMPNY_CD ,VENDOR_ACCT_NBR ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,CASE WHEN MAX(POSTG_CD) IN ('31', '22') THEN 1 WHEN MAX(POSTG_CD) IN ('32', '21') THEN -1 ELSE 0 END AS DOC_TYPE ,MAX(FISCAL_PERIOD_NBR) AS FISCAL_PERIOD_NBR ,MAX(POSTG_CD) AS POSTG_CD ,MAX(ASSGNMNT_NBR) AS ASSGNMNT_NBR ,MAX(REF_DOC_NBR) AS REF_DOC_NBR ,MAX(DOC_TYPE_CD) AS DOC_TYPE_CD ,MAX(DOC_POSTG_DT) AS DOC_POSTG_DT ,MAX(ISSU_DOC_DT) AS ISSU_DOC_DT ,MAX(CLR_DT) AS CLR_DT ,MAX(DOC_ENT_DT) AS DOC_ENT_DT ,MAX(ITEM_TXT) AS ITEM_TXT ,COALESCE(MAX(BUS_PRTNR_REF_1_ID),'') AS BUS_PRTNR_REF_1_ID ,COALESCE(MAX(BUS_PRTNR_REF_2_ID),'') AS BUS_PRTNR_REF_2_ID ,SUM(LOCAL_CRNCY_ITEM_AMT) AS LOCAL_CRNCY_ITEM_AMT FROM( SELECT CUST_ID ,CMPNY_CD ,VENDOR_ACCT_NBR ,CLR_DOC_NBR ,ASSGNMNT_NBR ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,FISCAL_PERIOD_NBR ,POSTG_CD ,LINE_ITEM_NBR ,REF_DOC_NBR ,PROFIT_CNTR_ID ,DOC_TYPE_CD ,DOC_POSTG_DT ,ISSU_DOC_DT ,CLR_DT ,DOC_ENT_DT ,DEBIT_CR_IND ,LOCAL_CRNCY_ITEM_AMT ,TAX_CD ,ITEM_TXT ,BUS_PRTNR_REF_1_ID ,BUS_PRTNR_REF_2_ID FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_VEND_SCNDRY_NDX WHERE DATE_FORMAT(DOC_POSTG_DT, 'YYYYMM') = DATE_FORMAT(ADD_MONTHS(CURRENT_DATE(),-1),'YYYYMM') AND ST_PROV_CD = 'NI' UNION SELECT CUST_ID ,CMPNY_CD ,VENDOR_ACCT_NBR ,CLR_DOC_NBR ,ASSGNMNT_NBR ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,FISCAL_PERIOD_NBR ,POSTG_CD ,LINE_ITEM_NBR ,REF_DOC_NBR ,PROFIT_CNTR_ID ,DOC_TYPE_CD ,DOC_POSTG_DT ,ISSU_DOC_DT ,CLR_DT ,DOC_ENT_DT ,DEBIT_CR_IND ,LOCAL_CRNCY_ITEM_AMT ,TAX_CD ,ITEM_TXT ,BUS_PRTNR_REF_1_ID ,BUS_PRTNR_REF_2_ID FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_VEND_SCNDRY_NDX_CLR_ITEM WHERE DATE_FORMAT(DOC_POSTG_DT, 'YYYYMM') = DATE_FORMAT(ADD_MONTHS(CURRENT_DATE(),-1),'YYYYMM') AND ST_PROV_CD = 'NI' ) AS DS1 GROUP BY CUST_ID, CMPNY_CD, VENDOR_ACCT_NBR, ACCTG_DOC_NBR, FISCAL_YR_NBR ) AS DS3 LEFT JOIN( SELECT ACCTG_DOC_NBR ,CMPNY_CD ,FISCAL_YR_NBR ,FISCAL_PERIOD_NBR ,OBJ_CD FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_DOC_HDR AS A WHERE DATE_FORMAT(A.DOC_POSTG_DT, 'YYYYMM') = DATE_FORMAT(ADD_MONTHS(CURRENT_DATE(),-1),'YYYYMM') AND A.ST_PROV_CD = 'NI' GROUP BY ACCTG_DOC_NBR ,CMPNY_CD ,FISCAL_YR_NBR ,FISCAL_PERIOD_NBR ,OBJ_CD ) AS DS7 ON DS7.ACCTG_DOC_NBR = DS3.ACCTG_DOC_NBR AND DS7.CMPNY_CD = DS3.CMPNY_CD AND DS7.FISCAL_YR_NBR = DS3.FISCAL_YR_NBR AND DS7.FISCAL_PERIOD_NBR = DS3.FISCAL_PERIOD_NBR LEFT JOIN( SELECT A.CUST_ID ,A.VENDOR_ACCT_NBR ,MAX(A.VENDOR_1_NM) AS VENDOR_1_NM ,MAX(A.VENDOR_2_NM) AS VENDOR_2_NM ,MAX(A.TAX_1_NBR) AS TAX_1_NBR FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_VENDOR_MASTER AS A GROUP BY A.CUST_ID ,A.VENDOR_ACCT_NBR ) AS DS4 ON DS3.CUST_ID = DS4.CUST_ID AND DS3.VENDOR_ACCT_NBR = DS4.VENDOR_ACCT_NBR LEFT JOIN( SELECT CMPNY_CD ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,LINE_ITEM_NBR ,BILL_DOC_NBR ,LINE_ITEM_ID ,POSTG_CD ,DEBIT_CR_IND ,ACCT_TYPE_CD ,COST_CNTR_ID ,PROFIT_CNTR_ID ,CLR_DT ,CLR_ENT_DT ,MATL_NBR ,ALLOC_QTY ,MEAS_BASE_UNIT_CD ,ASSGNMNT_NBR ,ITEM_TXT ,LOCAL_CRNCY_ITEM_AMT ,TAX_CD ,BUS_PRTNR_REF_1_ID ,BUS_PRTNR_REF_2_ID ,WH_TAX_CD FROM( SELECT UNIQ_ROW_ID, CMPNY_CD ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,LINE_ITEM_NBR ,BILL_DOC_NBR ,LINE_ITEM_ID ,POSTG_CD ,CASE WHEN DEBIT_CR_IND = 'S' THEN -1 ELSE 1 END AS DEBIT_CR_IND ,ACCT_TYPE_CD ,COST_CNTR_ID ,PROFIT_CNTR_ID ,CLR_DT ,CLR_ENT_DT ,MATL_NBR ,ALLOC_QTY ,MEAS_BASE_UNIT_CD ,ASSGNMNT_NBR ,ITEM_TXT ,LOCAL_CRNCY_ITEM_AMT ,TAX_CD ,BUS_PRTNR_REF_1_ID ,BUS_PRTNR_REF_2_ID ,WH_TAX_CD, SRC_FILE_RCV_TS FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_DOC_SEG WHERE FISCAL_YR_NBR = YEAR(ADD_MONTHS(CURRENT_DATE(),-1)) AND ST_PROV_CD = 'NI')AS A INNER JOIN( SELECT UNIQ_ROW_ID ,MAX(SRC_FILE_RCV_TS) AS SRC_FILE_RCV_TS FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_DOC_SEG WHERE FISCAL_YR_NBR = YEAR(ADD_MONTHS(CURRENT_DATE(),-1)) AND ST_PROV_CD = 'NI' GROUP BY UNIQ_ROW_ID) AS B ON A.UNIQ_ROW_ID = B.UNIQ_ROW_ID AND A.SRC_FILE_RCV_TS = B.SRC_FILE_RCV_TS ) AS DS6 ON DS3.ACCTG_DOC_NBR = DS6.ACCTG_DOC_NBR AND DS3.CMPNY_CD = DS6.CMPNY_CD AND DS3.FISCAL_YR_NBR = DS6.FISCAL_YR_NBR AND DS6.LINE_ITEM_NBR NOT IN (1) AND COALESCE(DS6.LINE_ITEM_ID,'') != 'T' INNER JOIN( SELECT ROW_NUMBER() OVER(PARTITION BY A.CMPNY_CD,A.ACCTG_DOC_NBR,A.FISCAL_YR_NBR ORDER BY A.LINE_ITEM_NBR) AS CORRELATIVO ,A.CMPNY_CD ,A.ACCTG_DOC_NBR ,A.FISCAL_YR_NBR ,C.PROFIT_CNTR_ID ,A.LINE_ITEM_NBR AS MINIMO ,COALESCE((LEAD(A.LINE_ITEM_NBR,1) OVER (PARTITION BY A.CMPNY_CD,A.ACCTG_DOC_NBR,A.FISCAL_YR_NBR ORDER BY A.LINE_ITEM_NBR)),(A.LINE_ITEM_NBR+99)) AS MAXIMO FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_DOC_SEG AS A INNER JOIN( SELECT CMPNY_CD ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,POSTG_CD FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_DOC_SEG WHERE LINE_ITEM_NBR = 2 GROUP BY CMPNY_CD,ACCTG_DOC_NBR,FISCAL_YR_NBR,POSTG_CD ) AS B ON A.CMPNY_CD = B.CMPNY_CD AND A.ACCTG_DOC_NBR = B.ACCTG_DOC_NBR AND A.FISCAL_YR_NBR = B.FISCAL_YR_NBR AND (A.POSTG_CD = B.POSTG_CD OR A.WH_TAX_CD IS NOT NULL) INNER JOIN( SELECT CMPNY_CD ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,MAX(PROFIT_CNTR_ID) as PROFIT_CNTR_ID FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_ACCTG_DOC_SEG WHERE FISCAL_YR_NBR = YEAR(ADD_MONTHS(CURRENT_DATE(),-1)) AND ST_PROV_CD = 'NI' GROUP BY CMPNY_CD,ACCTG_DOC_NBR,FISCAL_YR_NBR ) AS C ON A.CMPNY_CD = C.CMPNY_CD AND A.ACCTG_DOC_NBR = C.ACCTG_DOC_NBR AND A.FISCAL_YR_NBR = C.FISCAL_YR_NBR WHERE A.ST_PROV_CD = 'NI' AND A.FISCAL_YR_NBR = YEAR(ADD_MONTHS(CURRENT_DATE(),-1)) AND LINE_ITEM_NBR NOT IN (1) AND COALESCE(LINE_ITEM_ID,'') NOT IN ('T') GROUP BY A.CMPNY_CD,A.ACCTG_DOC_NBR,A.FISCAL_YR_NBR,C.PROFIT_CNTR_ID,A.LINE_ITEM_NBR ) AS DS8 ON DS6.CMPNY_CD = DS8.CMPNY_CD AND DS6.ACCTG_DOC_NBR = DS8.ACCTG_DOC_NBR AND DS6.FISCAL_YR_NBR = DS8.FISCAL_YR_NBR AND DS6.LINE_ITEM_NBR >= DS8.MINIMO AND DS6.LINE_ITEM_NBR < DS8.MAXIMO ) AS M LEFT JOIN ( SELECT CMPNY_CD ,ACCTG_DOC_NBR ,FISCAL_YR_NBR ,TAX_CD ,MAX(LINE_ITEM_NBR) AS LINE_ITEM_NBR ,MAX(GEN_LDGR_ACCT_NBR) AS GEN_LDGR_ACCT_NBR ,MAX(TRANS_CD) AS TRANS_CD ,MAX(LOCAL_CRNCY_TAX_BASE_AMT) AS LOCAL_CRNCY_TAX_BASE_AMT ,MAX(LOCAL_CRNCY_TAX_AMT) AS LOCAL_CRNCY_TAX_AMT ,MIN(LOCAL_CRNCY_TAX_AMT) AS HWSTE_PRO ,MAX(TAX_JURIS_CD) AS TAX_JURIS_CD ,CASE WHEN MAX(TAX_RATE_AMT) > 0 THEN 1 ELSE 0 END AS TAX_IND ,(MAX(TAX_RATE_AMT) / 1000) AS TAX_RATE_AMT ,COALESCE(CASE WHEN MAX(TAX_RATE_AMT) = MIN(TAX_RATE_AMT) THEN 0.00 ELSE MIN(TAX_RATE_AMT) / 1000 END,0) AS KBETR_PRO FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_TAX_DOC_SEG AS A INNER JOIN( SELECT UNIQ_ROW_ID ,MAX(SRC_FILE_RCV_TS) AS SRC_FILE_RCV_TS FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_TAX_DOC_SEG WHERE FISCAL_YR_NBR = YEAR(ADD_MONTHS(CURRENT_DATE(),-1)) AND ST_PROV_CD = 'NI' GROUP BY UNIQ_ROW_ID ) AS B ON A.UNIQ_ROW_ID = B.UNIQ_ROW_ID AND A.SRC_FILE_RCV_TS = B.SRC_FILE_RCV_TS GROUP BY CMPNY_CD,ACCTG_DOC_NBR,FISCAL_YR_NBR,TAX_CD ) AS DS5 ON M.CMPNY_CD = DS5.CMPNY_CD AND M.ACCTG_DOC_NBR = DS5.ACCTG_DOC_NBR AND M.FISCAL_YR_NBR = DS5.FISCAL_YR_NBR AND M.TAX_CD = DS5.TAX_CD AND COALESCE(DS5.GEN_LDGR_ACCT_NBR,'') <> '' AND DS5.TRANS_CD NOT IN ('NVV') LEFT JOIN( SELECT A.BILL_DOC_NBR ,A.DOC_COND_NBR FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_BILL_DOC_HDR_DATA AS A GROUP BY A.BILL_DOC_NBR ,A.DOC_COND_NBR ) AS DS8 ON DS8.BILL_DOC_NBR = M.BILL_DOC_NBR LEFT JOIN( SELECT A.BILL_DOC_NBR ,A.MATL_NBR ,A.BILL_ITEM_NBR ,A.ITEM_DESC FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_BILL_DOC_ITEM_DATA AS A GROUP BY A.BILL_DOC_NBR ,A.MATL_NBR ,A.BILL_ITEM_NBR ,A.ITEM_DESC ) AS DS9 ON DS8.BILL_DOC_NBR = DS9.BILL_DOC_NBR AND M.MATL_NBR = DS9.MATL_NBR LEFT JOIN( SELECT A.DOC_COND_NBR ,A.COND_ITEM_NBR ,A.COND_TYPE ,A.COND_VAL_AMT FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_CONDITIONS_TRANS_DATA AS A INNER JOIN( SELECT UNIQ_ROW_ID ,MAX(SRC_FILE_RCV_TS) AS SRC_FILE_RCV_TS FROM K1_FIN_DL_HIGHSECURE.K1_SAP_PRM_CONDITIONS_TRANS_DATA WHERE DATE_FORMAT(COND_PRICE_DT,'YYYYMM') = DATE_FORMAT(ADD_MONTHS(CURRENT_DATE(),-1),'YYYYMM') AND ST_PROV_CD = 'NI' GROUP BY UNIQ_ROW_ID ) AS B ON A.UNIQ_ROW_ID = B.UNIQ_ROW_ID AND A.SRC_FILE_RCV_TS = B.SRC_FILE_RCV_TS )AS DS10 ON DS8.DOC_COND_NBR = DS10.DOC_COND_NBR AND DS9.BILL_ITEM_NBR = DS10.COND_ITEM_NBR AND DS10.COND_TYPE = 'ZK00' ) AS A GROUP BY GROUPID ,NUMERO_COMPANIA,STORE_NBR, STORE_NM,SAP_DOC_CLASS ,ANO ,MES ,NUMERO_FACTURA ,NUMERO_NOTA_CREDITO ,DOC_CONTABLE ,POSTG_CD ,DOC_DESC ,ASSGNMNT_NBR ,REF_DOC_NBR ,FECHA_CONTABLE ,FECHA_EMISION ,NIT ,ID_VENDOR ,DENOMINACION_SOCIAL ,COD_PRODUCTO ,DESCIPCION_PROD