Untitled

select * from EDW_MOB.B2B_CONNEC_ALL_OPEN_INVOICE where FX_INTERNAL_ACCOUNT_NO=72980011;

select * from EDW_MOB.B2B_CONNEC_ALL_OPEN_INVOICE where AMOUNT_DUE/INVOICE_AMOUNT >=0.1 AND SEGMENT='ABS / Channels' AND PREP_DATE > '01-APR-2020' AND 
FX_INTERNAL_ACCOUNT_NO IN (73770791,73529359,80106383,80129120,72730672,80129169,72391713,80136930,73459980,73648335);

select * from EDW_MOB.B2B_CONNEC_ALL_OPEN_INVOICE where AMOUNT_DUE/INVOICE_AMOUNT >=0.1 AND SEGMENT='ABS / Channels' AND PREP_DATE > '01-APR-2020' AND 
FX_INTERNAL_ACCOUNT_NO IN (73466353,73682146,72683103); /* 31-19310652 */

select sum(amount_due) from EDW_MOB.B2B_CONNEC_ALL_OPEN_INVOICE where AMOUNT_DUE/INVOICE_AMOUNT >=0.1 AND SEGMENT='ABS / Channels' AND PREP_DATE > '01-APR-2020' AND 
FX_INTERNAL_ACCOUNT_NO IN (80102658,80127860,80058765); /* 31-19935302  */

select * from EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX WHERE FX_EXTERNAL_ACCOUNT_NO='31-19308073';
select CASEDISTINCT(FILE_SOURCE) from EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX ;
select * from EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX WHERE FX_EXTERNAL_ACCOUNT_NO='31-19310652';  /*CORRECT */

select * from EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX WHERE FX_EXTERNAL_ACCOUNT_NO='31-19935302';  /*CORRECT */

select COUNT(T1.INVOICE_NUMBER) AS COUNTI, T2.NBA_ID,
SUM(T1.DAYS_0_30) as DAYS_0_30, SUM(T1.DAYS_31_60) as DAYS_31_60, 
SUM(T1.DAYS_61_90) as DAYS_61_90, SUM((T1.DAYS_91_120 + T1.DAYS_121_150 + T1.DAYS_151_180)) as DAYS_91_180, 
SUM(T1.DAYS_181_365) as DAYS_181_365, SUM(T1.DAYS_365_ABOVE) as DAYS_365_ABOVE , SUM(T1.INVOICE_AMOUNT) as INVOICE_AMOUNT,
sum(T1.NOT_DUE) as UNDER_DUE_DATE_AMOUNT, sum(T1.AMOUNT_DUE) as OUT_STANDING_AMOUNT, 
sum(T1.AMOUNT_DUE-NOT_DUE) as OVER_DUE_DATE_AMOUNT
from EDW_MOB.B2B_CONNEC_ALL_OPEN_INVOICE T1 INNER JOIN (SELECT DISTINCT
        CASE WHEN CRM_ACCOUNT_NO IS NULL THEN SUBSTR(FX_EXTERNAL_ACCOUNT_NO,4)
        ELSE CRM_ACCOUNT_NO
        END AS NBA_ID,
        FX_INTERNAL_ACCOUNT_NO
FROM EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX) T2 
ON T1.FX_INTERNAL_ACCOUNT_NO=T2.FX_INTERNAL_ACCOUNT_NO
where T1.SEGMENT='ABS / Channels' AND T1.AMOUNT_DUE/T1.INVOICE_AMOUNT >=0.1 AND T1.PREP_DATE > '01-APR-2020'
GROUP BY T2.NBA_ID HAVING T2.NBA_ID='19308073';

SELECT DISTINCT
        CASE WHEN CRM_ACCOUNT_NO IS NULL THEN SUBSTR(FX_EXTERNAL_ACCOUNT_NO,4)
        ELSE CRM_ACCOUNT_NO
        END AS NBA_ID,
        FX_INTERNAL_ACCOUNT_NO
FROM EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX



21101038-1-1-1-51-1-1-8-0-L14237609-2131196-2131196-0-17:17:126-0-0-0

SELECT * FROM EDW_MOB.B2B_CONNEC_ALL_OPEN_INVOICE T1 JOIN EDW_MOB.B2B_CONNEC_ACTIVE_BASE_FX T2 
ON T1.FX_INTERNAL_ACCOUNT_NO=T2.FX_INTERNAL_ACCOUNT_NO where T1.SEGMENT='ABS / Channels';