Need help with Conflicting Column Count in TPT

Tools & Utilities

Need help with Conflicting Column Count in TPT

I have a TPT job that is getting the following errors:

TPT_INFRA: TPT02640: Error: Conflicting column count. Source column count (12) Target column count (27).

$EXPORT: TPT12108: Output Schema does not match data from SELECT statement

Job step STEP4 terminated (status 12)

Here is the script below  - it it terminating in STEP 4

The affected areas would be APAC_TRANS_COMM_SCHEMA and STEP4 - just wondered if another set of eyes could something I'm missing - the target and source both appear to have 27 columns.

Thanks, Phil

DEFINE JOB FDX_APAC_EXTRACT

DESCRIPTION 'FDX_APAC_EXTRACT'

(

  DEFINE SCHEMA MANIFEST_SCHEMA

  DESCRIPTION 'APAC_MANIFEST'

  (

       Manifested_Ship_Date                 VARCHAR(10)

      ,Manifested_Origin_Country          varchar(3)

      ,Manifested_Origin_Airport                      varchar(3)

      ,Manifested_Origin_Location_Code  varchar(5)

      ,Manifested_Packages                    varchar(25)

      ,Manifested_Weight                      varchar(20)

      ,Manifested_Destination_Country   varchar(3)

      ,Manifested_Destination_Airport   varchar(3)

      ,Manifested_Product                                 varchar(255)

      ,Manifested_Division_Code                        varchar(3)

            ,Manifested_Service_Code                 varchar(4)

            ,Heavy_Weight_Flag                                       varchar(1)

  );

  DEFINE SCHEMA APAC_TRANS_COMM_SCHEMA

  DESCRIPTION 'APAC_TRANS_COMM'

  (

            Ship_Trk_Nbr             varchar(15)

            ,Inv_Status      varchar(3)

            ,Master_Trk_Nbr_Flg varchar(2)

            ,Unique_Ship_Nbr varchar(10)

            ,BUL_ID_ORIG varchar(5)

            ,CON_CREATE_DT       varchar(10)

            ,CON_CREATE_TM      varchar(5)

            ,COE_ID           varchar(3)

            ,SHIP_DT        varchar(10)

            ,Orig_Cntry_Cd varchar(2)

            ,Orig_Depot_ID varchar(5)

            ,Dest_Cntry_Cd varchar(2)

            ,Dest_Depot_Id varchar(5)

            ,Payr_Cust_Nbr varchar(11)

            ,Payr_Cntry_Cd            varchar(3)

            ,Payr_Cust_Acct_Id varchar(9)

            ,Billed_Wgt varchar(12)

            ,Billed_Wgt_UOM varchar(1)

            ,Detail_Div_Cd varchar(3)

            ,Detail_Prod_Cd varchar(4)

            ,HW_Flg varchar(1)

            ,Simple_Prod_Cat0_Nm varchar(60)

            ,Rated_Curr_Cd varchar(3)

            ,Net_rev_amt_EUR varchar(20)

            ,Fuel_Surchg_EUR varchar(20)

            ,Packs varchar(6)

            ,Ship_Cnt varchar(3)

  );   

  

  DEFINE SCHEMA APAC_TRANS_OPS_SCHEMA

  DESCRIPTION 'APAC_TRAN_OPS'

  ( 

   Ship_Trk_Nbr varchar(15)

  ,Unique_Ops_Ship_Nbr varchar(10)

  ,Bul_Id_Orig varchar(5)

  ,CON_CREATE_DT varchar(10)

  ,CON_CREATE_TM varchar(5)

  ,Ship_DT varchar(10)

 ,Sender_Cust_Nbr varchar(11)

  ,Sender_Cntry_Cd varchar(3)

  ,Sender_Cust_Acct_ID varchar(9)

  ,Recp_Cust_Nbr varchar(11)

  ,Recp_Cntry_Cd varchar(3)

  ,Recp_Cust_Acct_Id varchar(9)

  ,Actl_Wgt_UOM varchar(1)

  ,Actl_Wgt varchar(10)

  );    

  STEP STEP1

    (

      APPLY

        ('DELETE FROM PRODFEDEX_W.FDX_APAC_MANIFEST;'),

        ('INSERT INTO PRODFEDEX_W.FDX_APAC_MANIFEST SELECT T1.CON_SRKY_ID ,T1.CON_COLL_DT ,T2.COU_ISO_ID AS COU_ID_ORIG ,T1.BUL_ID_CON_ORIG ,T1.CON_OA_TITEM_QT ,T1.CON_OPSA_TGRS_WT ,T3.COU_ISO_ID AS COU_ID_DEST ,(CASE WHEN T1.CON_OPSA_TGRS_WT < 68 THEN ''P'' ELSE ''F'' END) AS PFT_ID ,T1.DIV_ID ,T1.PRD_ID_ACTUAL ,T7.SEC_ACT_DEP_DT ,T7.SEC_ACT_DEP_TM ,T4.BUL_ID_SEC_ORIG ,T4.BUL_ID_SEC_DEST FROM PRODOPS_P.CON_V01 T1 INNER JOIN PRODNETWORKOPS_P.BusinessLocation_FV01 T2 On T1.BUL_ID_CON_ORIG = T2.BUL_ID And T2.COU_ISO_ID NOT IN (''AU'',''NZ'',''AS'',''CK'',''FJ'',''FM'',''KI'',''MH'',''NC'',''NF'',''NR'',''PF'',''PG'',''PW'',''SB'',''TL'',''TO'',''TV'',''VU'',''WF'',''WS'',''WS'',''CX'',''CC'',''AR'') INNER JOIN PRODNETWORKOPS_P.BusinessLocation_FV01 T3 On T1.BUL_ID_DEST = T3.BUL_ID LEFT OUTER JOIN PRODOPS_P.CONINSECTOR_V01 T4 On T1.CON_SRKY_ID = T4.CON_SRKY_ID And T4.SEM_ID = ''A'' LEFT OUTER JOIN PRODOPS_P.SECTOR_V01 T7 On T4.COM_ID = T7.COM_ID And T4.STY_TYPE_CD = T7.STY_TYPE_CD And T4.SEC_NR = T7.SEC_NR And T4.SEC_DEP_DT = T7.SEC_DEP_DT And T4.BUL_ID_SEC_ORIG = T7.BUL_ID_SEC_ORIG And T4.BUL_ID_SEC_DEST = T7.BUL_ID_SEC_DEST And T4.SEM_ID = T7.SEM_ID And T4.SEC_NET_CD = T7.SEC_NET_CD And T4.SEC_SEQ_NR = T7.SEC_SEQ_NR LEFT OUTER JOIN PRODFEDEX_W.FDX_COU_HIER T9 On T2.COU_ISO_ID = T9.COU_ISO_ID LEFT OUTER JOIN PRODFEDEX_W.FDX_COU_HIER T10 On T3.COU_ISO_ID = T10.COU_ISO_ID Inner Join PRODOPS_P.CONPARTYNAD_V01 T11 ON T11.CON_SRKY_ID = T1.CON_SRKY_ID AND T11.CPT_ID = COALESCE(NULLIF(T1.TOP_ID_OPS_ACT,''''),T1.TOP_ID_OPS_CON) AND T11.COU_ID_CPN NOT IN (''AU'',''NZ'',''AS'',''CK'',''FJ'',''FM'',''KI'',''MH'',''NC'',''NF'',''NR'',''PF'',''PG'',''PW'',''SB'',''TL'',''TO'',''TV'',''VU'',''WF'',''WS'',''WS'',''CX'',''CC'',''AR'') WHERE T1.CON_COLL_DT BETWEEN td_month_begin(add_months(current_date, -25)) and td_month_end(add_months(current_date, -2)) AND (T9.REG_CD IN (''APAC'') OR T10.REG_CD IN (''APAC'')) /* Country Subset Filter (on Payer Account Country)*/ and T11.COU_ID_CPN IN (''CN'', ''JP'', ''PH'') /* Country Subset Filter (on Origin Country) */ and T2.COU_ISO_ID IN (''CN'', ''JP'', ''PH'') and T1.DIV_ID <> ''Z'' ;')

      TO OPERATOR ($DDL);

  );

  STEP STEP2

  (

  APPLY TO OPERATOR

            ($FILE_WRITER [1]

               ATTR (

                                    DirectoryPath = '/stage/tdinfust/TNT_Data',

                                    FileName = 'apac_manifest.dat'

                     )

     )

  SELECT * FROM OPERATOR

            ($EXPORT(MANIFEST_SCHEMA) [4]

        ATTR (

                SelectStmt = 'SELECT T1.CON_COLL_DT (varchar(10)) as Manifested_Ship_Date ,  T1.COU_ID_ORIG (varchar(3)) AS Manifested_Origin_Country ,  SUBSTRING(T1.BUL_ID_ORIG_AIRPRT FROM 19 FOR 3) (varchar(3)) AS Manifested_Origin_Airport ,  T1.BUL_ID_CON_ORIG (varchar(5)) AS Manifested_Origin_Location_Code , SUM(T1.CON_OA_TITEM_QT) (varchar(25)) AS Manifested_Packages ,  SUM(T1.CON_OPSA_TGRS_WT) (varchar(20)) AS Manifested_Weight ,  T1.COU_ID_DEST (varchar(3)) AS Manifested_Destination_Country , SUBSTRING(T1.BUL_ID_DEST_AIRPRT FROM 24 FOR 3)(varchar(3)) AS Manifested_Destination_Airport ,  T1.PRD_CAT0_NM (varchar(255)) AS Manifested_Product ,  T1.DIV_ID (varchar(3)) AS Manifested_Division_Code ,  T1.PRD_ID_ACTUAL (varchar(4)) AS Manifested_Service_Code   ,T1.HW_FLAG (varchar(1)) as Heavy_Weight_Flag FROM ( SELECT  T1.CON_SRKY_ID , T1.CON_COLL_DT , T1.COU_ID_ORIG , MIN(CAST(T1.SEC_ACT_DEP_DT AS CHAR(10)) || CAST(T1.SEC_ACT_DEP_TM AS CHAR(8))  || CAST(T1.BUL_ID_SEC_ORIG AS CHAR(5)) || CAST(T1.BUL_ID_SEC_DEST AS CHAR(5))) AS BUL_ID_ORIG_AIRPRT , T1.BUL_ID_CON_ORIG , T1.CON_OA_TITEM_QT , T1.CON_OPSA_TGRS_WT , T1.COU_ID_DEST , MAX(CAST(T1.SEC_ACT_DEP_DT AS CHAR(10)) || CAST(T1.SEC_ACT_DEP_TM AS CHAR(8))  || CAST(T1.BUL_ID_SEC_ORIG AS CHAR(5)) || CAST(T1.BUL_ID_SEC_DEST AS CHAR(5))) AS BUL_ID_DEST_AIRPRT, T8.PRD_CAT0_NM , T1.DIV_ID , T1.PRD_ID_ACTUAL , CASE WHEN T1.PFT_ID = ''F'' THEN ''Y'' ELSE ''N'' END AS HW_FLAG  FROM PRODFEDEX_W.FDX_APAC_MANIFEST T1 /* TNT Product Group Translation */  LEFT OUTER JOIN  PRODFEDEX_W.FDX_PRDGRP_XREF2 T8  On T1.DIV_ID = T8.DIV_ID  And T1.PRD_ID_ACTUAL = T8.PRD_ID  And T1.PFT_ID = T8.PFT_ID   Group by 1,2,3,5,6,7,8,10,11,12,13   ) T1  Group by 1,2,3,4,7,8,9,10,11,12;'

             )

            );

  );

  STEP STEP3

      (

        APPLY

          ('DELETE FROM PRODFEDEX_W.FDX_RATELEM;'),

          ('DELETE FROM PRODFEDEX_W.FDX_RATELEM2;'),

          ('INSERT INTO PRODFEDEX_W.FDX_RATELEM Select T2.* , T1.COE_LOAD_DT ,(CASE WHEN T2.COE_RATE_ELEM_CD = ''2'' AND (T2.COE_COMPANY_AM <> 0 OR T2.COE_CUSTOMER_AM <> 0 OR T2.COE_HANDRATE_AM <> 0) THEN ROW_NUMBER() OVER(PARTITION BY (T1.COE_SRKY_ID  || T2.COE_RATE_ELEM_CD) ORDER BY T1.COE_SRKY_ID) END) AS RANK_VALUE_NR ,CASE WHEN  RANK_VALUE_NR=1 THEN COE_RATE_ELEM_ID ELSE  '' '' END AS OPTION_1 ,CASE WHEN  RANK_VALUE_NR=2 THEN COE_RATE_ELEM_ID ELSE  '' '' END AS OPTION_2 ,CASE WHEN  RANK_VALUE_NR=3 THEN COE_RATE_ELEM_ID ELSE  '' '' END AS OPTION_3 ,CASE WHEN  RANK_VALUE_NR=4 THEN COE_RATE_ELEM_ID ELSE  '' '' END AS OPTION_4 ,CASE WHEN  RANK_VALUE_NR=5 THEN COE_RATE_ELEM_ID ELSE  '' '' END AS OPTION_5 From PRODSCD_P.CONENTRY_v01 T1 INNER JOIN PRODSCD_P.CONENTRYRATELEM_V01 T2 On T1.COE_SRKY_ID = T2.COE_SRKY_ID And T2.COE_RATE_ELEM_CD IN (''2'',''5'') LEFT OUTER JOIN PRODFEDEX_W.FDX_SURCHGRP T3 On SUBSTR(T2.COE_RATE_ELEM_ID,1,3) = T3.CHD_ID And (T2.COU_ID_INV = T3.COU_ISO_ID_SRCHG Or T3.COU_ISO_ID_SRCHG = ''ALL'') WHERE T1.CON_COLL_DT BETWEEN td_month_begin(add_months(current_date, -25)) and td_month_end(add_months(current_date, -2));'),

          ('INSERT INTO PRODFEDEX_W.FDX_RATELEM2 Select COE_SRKY_ID ,SUM(CASE WHEN T1.COE_RATE_ELEM_CD = ''5'' AND COE_HANDRATE_CD IN (''H'',''M'') AND CHT_ID IN ('' '',''R'',''A'',''I'',''N'') THEN T1.COE_HANDRATE_AM WHEN T1.COE_RATE_ELEM_CD = ''5'' AND CHT_ID IN ('' '',''R'',''A'',''I'',''N'') AND COE_OVERRIDE_IN <> ''Y'' THEN COALESCE(T1.COE_COMPANY_AM,0) + COALESCE(T1.COE_CUSTOMER_AM,0) WHEN T1.COE_RATE_ELEM_CD = ''5'' AND CHT_ID IN ('' '',''R'',''A'',''I'',''N'') AND COE_OVERRIDE_IN = ''Y'' THEN T1.COE_CUSTOMER_AM ELSE 0.0 END) AS COE_NET_REV_FS_AM ,SUM(CASE WHEN T1.COE_RATE_ELEM_CD = ''2'' AND T1.COE_HANDRATE_CD IN (''H'',''M'') AND T1.CHT_ID IN ('' '',''R'',''A'',''I'',''N'') AND T1.COE_HANDRATE_AM <> 0 THEN T1.COE_HANDRATE_AM WHEN T1.COE_RATE_ELEM_CD = ''2'' AND T1.CHT_ID IN ('' '',''R'',''A'',''I'',''N'') AND T1.COE_OVERRIDE_IN <> ''Y'' AND (T1.COE_COMPANY_AM <> 0 OR T1.COE_CUSTOMER_AM <> 0) THEN COALESCE(T1.COE_COMPANY_AM,0) + COALESCE(T1.COE_CUSTOMER_AM,0) WHEN T1.COE_RATE_ELEM_CD = ''2'' AND T1.CHT_ID IN ('' '',''R'',''A'',''I'',''N'') AND T1.COE_OVERRIDE_IN = ''Y'' AND T1.COE_CUSTOMER_AM <> 0 THEN T1.COE_CUSTOMER_AM ELSE 0.0 END) AS COE_NET_REV_SH_AM ,MAX(T1.OPT_1_ID) AS OPT_ID_1 ,MAX(T1.OPT_2_ID) AS OPT_ID_2 ,MAX(T1.OPT_3_ID) AS OPT_ID_3 ,MAX(T1.OPT_4_ID) AS OPT_ID_4 ,MAX(T1.OPT_5_ID) AS OPT_ID_5 From PRODFEDEX_W.FDX_RATELEM T1 Group By 1 ;')

        TO OPERATOR ($DDL);

              );

    STEP STEP4

    (

    APPLY TO OPERATOR

            ($FILE_WRITER [1]

               ATTR (

                                    DirectoryPath = '/stage/tdinfust/TNT_Data',

                                    FileName = 'apac_trans_commercial.dat'

                     )

       )

    SELECT * FROM OPERATOR

            ($EXPORT(APAC_TRANS_COMM_SCHEMA) [4]

          ATTR (

                SelectStmt = 'SELECT T1.CON_ID (varchar(15)) AS Ship_Trk_Nbr  , T1.STT_ID_LATEST (varchar(3)) AS Inv_Status  , (CASE WHEN T1.DIV_ID = ''G'' AND T1.PRD_ID IN (''99'',''130'') THEN ''C'' ELSE NULL END) (varchar(2)) AS Master_Trk_Nbr_Flg  , T1.COE_SRKY_ID (varchar(10)) AS Unique_Ship_Nbr  , T1.BUL_ID_ORIG (varchar(5)) as BUL_ID_ORIG  , T1.CON_CREATE_DT (varchar(10)) as CON_CREATE_DT  , T1.CON_CREATE_TM (varchar(5)) as CON_CREATE_TM  , T1.COE_ID (varchar(3)) as COE_ID  , T1.CON_COLL_DT (varchar(10)) AS Ship_Dt  , T2.COU_ISO_ID (varchar(2)) AS Orig_Cntry_Cd  , T1.BUL_ID_CON_ORIG (varchar(5)) AS Orig_Depot_Id  , T3.COU_ISO_ID (varchar(2)) AS Dest_Cntry_Cd  , T1.BUL_ID_DEST (varchar(5)) AS Dest_Depot_Id  , T5.CAC_ID (varchar(11)) AS Payer_Cust_Nbr  , T1.COU_ID_ACC (varchar(3)) AS Payer_Cntry_Cd  , T1.ACC_ID (varchar(9)) AS Payer_Cust_Acct_Id , (T1.COE_PAY_WT * 2.20462) (varchar(12)) AS Billed_Wgt  , ''P'' (varchar(1)) AS Billed_UOM  , T1.DIV_ID (varchar(3)) AS Detail_Div_Cd  , T1.PRD_ID (varchar(4)) AS Detail_Prod_Cd  , CASE WHEN T1.COE_PAY_WT >= 68 THEN ''Y'' ELSE ''N'' END (varchar(1)) AS HW_Flg  , T6.PRD_CAT0_NM (varchar(60)) AS Simple_Prod_Cat_Nm  , T8.CUY_ID (varchar(3)) AS Rated_Curr_Cd  , T1.COE_NET_REV_AM (varchar(20)) AS Net_Rev_Amt_EUR  , T7.COE_NET_REV_FS_AM (varchar(20)) AS Fuel_Surchg_EUR  , T1.COE_TOTAL_ITEM_QT (varchar(6)) AS Packs  , T1.COE_CONSIGNMENT_QT (varchar(3)) AS Ship_Cnt   FROM PRODSCD_P.CONENTRY_V02 T1 INNER JOIN PRODSCD_P.BUSINESSLOCATION_V01 T2 On T1.BUL_ID_CON_ORIG = T2.BUL_ID And T2.COU_ISO_ID NOT IN (''AU'',''NZ'',''AS'',''CK'',''FJ'',''FM'',''KI'',''MH'',''NC'',''NF'',''NR'',''PF'',''PG'',''PW'',''SB'',''TL'',''TO'',''TV'',''VU'',''WF'',''WS'',''WS'',''CX'',''CC'',''AR'') INNER JOIN PRODSCD_P.BUSINESSLOCATION_V01 T3 On T1.BUL_ID_DEST = T3.BUL_ID LEFT OUTER JOIN PRODALLVIEWS_RV.GlExchangeRate_FV01 T4 On ''USD'' = T4.CUY_ID And T1.COE_LOAD_DT Between T4.EXP_START_DT And T4.EXP_END_DT And T4.EGP_ID = ''MO'' LEFT OUTER JOIN PRODSCD_P.ACCNTXREF_V01 T5 On T1.COU_ID_ACC = T5.LAC_LEGACY_COU_CD And T1.ACC_ID = T5.LAC_LEGACY_ACCT_NR And T1.ACG_ID = T5.LAC_LEGACY_ACG_CD And T5.LAC_DELETE_IN <> ''Y'' and T5.CUS_DATA_TYPE_CD = ''CUS'' LEFT OUTER JOIN PRODFEDEX_W.FDX_PRDGRP_XREF2 T6 On T1.DIV_ID = T6.DIV_ID And T1.PRD_ID = T6.PRD_ID And (CASE WHEN T1.COE_PAY_WT < 68 THEN ''P'' ELSE ''F'' END) = T6.PFT_ID LEFT OUTER JOIN PRODFEDEX_W.FDX_RATELEM2 T7 On T1.COE_SRKY_ID = T7.COE_SRKY_ID LEFT OUTER JOIN PRODSCD_P.COUNTRYEXCHANGERATE_V01 T8 ON T1.COU_ID_ACC = T8.COU_ID AND T1.COE_LOAD_DT Between T8.EXP_START_DT And T8.EXP_END_DT AND T8.EGP_ID = ''MO'' AND T8.CTY_ID = ''G'' LEFT OUTER JOIN PRODFEDEX_W.FDX_COU_HIER T13 On T1.COU_ID_ACC = T13.COU_ISO_ID WHERE T1.ETP_ID Like ''F%'' AND T1.CON_COLL_DT BETWEEN  td_month_begin(add_months(current_date, -25)) and td_month_end(add_months(current_date, -2)) AND T13.REG_CD IN (''APAC'') AND T1.COU_ID_ACC NOT IN (''CN'', ''JP'', ''PH'');'

               )

            );

  );

    STEP STEP5

    (

    APPLY TO OPERATOR

            ($FILE_WRITER [1]

               ATTR (

                                    DirectoryPath = '/stage/tdinfust/TNT_Data',

                                    FileName = 'apac_trans_ops.dat'

                     )

       )

    SELECT * FROM OPERATOR

            ($EXPORT(APAC_TRANS_OPS_SCHEMA) [4]

          ATTR (

                SelectStmt = 'SELECT T1.CON_ID (varchar(15)) AS Ship_Trk_Nbr  , T1.CON_SRKY_ID (varchar(10)) AS Unique_Ops_Ship_Nbr  , T1.BUL_ID_ORIG (varchar(5)) as BUL_ID_ORIG  , T1.CON_CREATE_DT (varchar(10)) as CON_CREATE_DT  , T1.CON_CREATE_TM (varchar(5)) as CON_CREATE_TM , T1.CON_COLL_DT (varchar(10)) AS Ship_Dt  , T2B.CAC_ID (varchar(11)) AS Sender_Cust_Nbr  , T2.COU_ID_CPN (varchar(3)) AS Sender_Cntry_Cd  , T2.ACC_ID_CPN (varchar(9)) AS Sender_Cust_Acct_ID  , T3B.CAC_ID (varchar(11)) AS Recp_Cust_Nbr  , T3.COU_ID_CPN (varchar(3)) AS Recp_Cntry_Cd  , T3.ACC_ID_CPN (varchar(9)) AS Recp_Cust_Acct_Id  , ''P'' (varchar(1)) AS Actl_Wgt_UOM , T1.CON_OPSA_TGRS_WT (varchar(10)) AS Actl_Wgt  FROM PRODOPS_P.CON_V01 T1  INNER JOIN PRODNETWORKOPS_P.BusinessLocation_FV01 T1A  On T1.BUL_ID_CON_ORIG = T1A.BUL_ID  AND T1A.COU_ISO_ID NOT IN (''AU'',''NZ'',''AS'',''CK'',''FJ'',''FM'',''KI'',''MH'',''NC'',''NF'',''NR'',''PF'',''PG'',''PW'',''SB'',''TL'',''TO'',''TV'',''VU'',''WF'',''WS'',''WS'',''CX'',''CC'',''AR'') INNER JOIN PRODNETWORKOPS_P.BusinessLocation_FV01 T1B  On T1.BUL_ID_DEST = T1B.BUL_ID  INNER JOIN  PRODOPS_P.CONSENADDRESS_V01 T2  On T1.CON_SRKY_ID = T2.CON_SRKY_ID  LEFT OUTER JOIN  PRODSCD_P.ACCNTXREF_V01 T2B  On T2.COU_ID_CPN = T2B.LAC_LEGACY_COU_CD  And T2.ACC_ID_CPN = T2B.LAC_LEGACY_ACCT_NR  And T2.ACG_ID_CPN = T2B.LAC_LEGACY_ACG_CD  And T2B.LAC_DELETE_IN <> ''Y''  And T2B.CUS_DATA_TYPE_CD = ''CUS''  And T2.ACC_ID_CPN <> ''''  INNER JOIN  PRODOPS_P.CONRECADDRESS_V01 T3  On T1.CON_SRKY_ID = T3.CON_SRKY_ID  LEFT OUTER JOIN  PRODSCD_P.ACCNTXREF_V01 T3B  On T3.COU_ID_CPN = T3B.LAC_LEGACY_COU_CD  And T3.ACC_ID_CPN = T3B.LAC_LEGACY_ACCT_NR  And T3.ACG_ID_CPN = T3B.LAC_LEGACY_ACG_CD  And T3B.LAC_DELETE_IN <> ''Y''  And T3B.CUS_DATA_TYPE_CD = ''CUS''  And T3.ACC_ID_CPN <> ''''  LEFT OUTER JOIN  PRODFEDEX_W.FDX_COU_HIER T9  On T1A.COU_ISO_ID = T9.COU_ISO_ID  LEFT OUTER JOIN  PRODFEDEX_W.FDX_COU_HIER T10  On T1B.COU_ISO_ID = T10.COU_ISO_ID  Inner Join  PRODOPS_P.CONPARTYNAD_V01 T11  ON T11.CON_SRKY_ID = T1.CON_SRKY_ID  AND T11.CPT_ID = COALESCE(NULLIF(T1.TOP_ID_OPS_ACT,''''),T1.TOP_ID_OPS_CON)  WHERE T1.CON_COLL_DT BETWEEN td_month_begin(add_months(current_date, -25)) and td_month_end(add_months(current_date, -2))  AND T11.COU_ID_CPN NOT IN (''AU'',''NZ'',''AS'',''CK'',''FJ'',''FM'',''KI'',''MH'',''NC'',''NF'',''NR'',''PF'',''PG'',''PW'',''SB'',''TL'',''TO'',''TV'',''VU'',''WF'',''WS'',''WS'',''CX'',''CC'',''AR'') AND (T9.REG_CD IN (''APAC'') OR T10.REG_CD IN (''APAC''))  AND T11.COU_ID_CPN in (''CN'', ''JP'', ''PH'')  AND T1.DIV_ID <> ''Z''  GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;'

               )

            );

  );

);