Credit/Debit 

Card 

(ShipMoney)'
    When P01.Transferbalance = '6' Then  'Citibank'
    When P01.Transferbalance = '7' Then  'Chase Manhattan'
    When P01.Transferbalance = '8' Then  'Hellenic Bank'
    When P01.Transferbalance = '9' Then  'Isabel Domestic'
    When P01.Transferbalance = 'A' Then  'Isabel International'
    When P01.Transferbalance = 'B' Then  'Bank Transfer'
    When P01.Transferbalance = 'C' Then  'Swedish Domestic'
    When P01.Transferbalance = 'D' Then  'Swedish International'
    When P01.Transferbalance = 'E' Then  'Philippine Banking'
    When P01.Transferbalance = 'F' Then  'Manual Bank'
    When P01.Transferbalance = 'G' Then  'Zagrebancka'
    When P01.Transferbalance = 'H' Then  'ING Bank'
    When P01.Transferbalance = 'I' Then  'Direct Deposit E-Monee'
    When P01.Transferbalance = 'J' Then  'Deutsche Bank'
    When P01.Transferbalance = 'K' Then  'SACS'
    When P01.Transferbalance = 'L' Then  'Hellenic Cyprus'
    When P01.Transferbalance = 'M' Then  'MT 100'
    When P01.Transferbalance = 'N' Then  'Citibank (PL)'
    When P01.Transferbalance = '0' Then  'Agent .R'
    When P01.Transferbalance = 'P' Then  'German Bank'
    When P01.Transferbalance = 'Q' Then  'Trident Trust'
    When P01.Transferbalance = 'R' Then  'Citibank (Asia)'
    When P01.Transferbalance = 'S' Then  'PNC Bank'
    When P01.Transferbalance = 'T' Then  'Chase Insight'
    When P01.Transferbalance = 'U' Then  'Ocean Pay'
    When P01.Transferbalance = 'V' Then  'Banco de Oro'
    When P01.Transferbalance = 'W' Then  'Bank of Philippine Island'
    When P01.Transferbalance = 'X'  Then  'CitiDirect (Onboard)'
    When P01.Transferbalance = 'Y' Then  'Metrobank Direct (PH)'
    When P01.Transferbalance = 'Z' Then  'Elektron'
    When P01.Transferbalance = 'A1' Then  'E-Banking (Maramut)'
    When P01.Transferbalance = 'A2' Then  'RBS Direct Access'
    When P01.Transferbalance = 'A3' Then  'Brazilian Banks'
    When P01.Transferbalance = 'A4' Then  'NETS'
    When P01.Transferbalance = 'A5' Then  'NONE'
    When P01.Transferbalance = 'A6' Then  'JDP Morgan'
    When P01.Transferbalance = 'A7'  Then  'J. P01. Morgan Access'
    When P01.Transferbalance = 'A8' Then  'Spar Nord Domestic'
    When P01.Transferbalance = 'A9' Then  'Spar Nord International'
    When P01.Transferbalance = 'B1' Then  'Rabobank'
    When P01.Transferbalance = 'B2' Then  'Deutsche Bank'
	When P01.Transferbalance = 'B4' Then  'ISO20022'
	When P01.Transferbalance = 'B5' Then  'Brightwell'
 End	as 'BALANCE ACTION'

FROM   dbo.PW001P01 p01
       LEFT JOIN PWORG AS payorg
            ON payorg.NUMORGID = dbo.ad_ScanOrgTreePayroll(p01.CLIENT)
       LEFT JOIN PWORGCMP AS paycmp
            ON  paycmp.NUMORGID = dbo.ad_scanorgtree(payorg.NUMORGID, 2)
       LEFT JOIN PWCMPPAY AS per
            ON  per.COMPANYID = paycmp.NUMORGID
            AND per.[PERIOD] = paycmp.PERIODE
	   LEFT JOIN PW001P0Y P0Y ON P01.PIN=P0Y.PIN
       JOIN PW001P03 AS p03
            ON  p03.PIN = P01.PIN
            AND p03.DATEFROM <= per.COMPLETIONDATE
            AND (ISNULL(p03.DATETO, p03.TODATEESTIMATED) >= per.PREPARATIONDATE
                    OR ISNULL(p03.DATETO, p03.TODATEESTIMATED) IS NULL
                )
			AND  p03.CODE IN (SELECT t.CODE
                               FROM pw001c12 t
                               WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
                             )
				 AND NOT EXISTS (SELECT 1 FROM pw001p03 p03t
                          WHERE p03t.PIN = p03.PIN  AND p03t.DATEFROM <= per.COMPLETIONDATE
                 AND (ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) >= per.PREPARATIONDATE
                    OR ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) IS NULL
                      )
				AND dbo.ad_scanorgtree(p03t.NUMORGID, 3) = dbo.ad_scanorgtree(p03.NUMORGID, 3) 
				       AND p03t.code IN (SELECT t.CODE
                              FROM pw001c12 t
                               WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
                              )
                    AND p03t.DATEFROM > p03.DateFrom)

       JOIN PW001C12 AS c12
            ON  c12.CODE = p03.CODE
            and c12.CODE not like 'LDC'
       LEFT JOIN PWORG AS orgves
            ON  orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
       LEFT JOIN PWORG AS orgdep
            ON  orgdep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)
       LEFT JOIN PWORG AS orgpos
            ON  orgpos.NUMORGID = p03.NUMORGID
            AND orgpos.ORGTYPE = '5'

LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM  PW001P0T P0T2
WHERE  (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID

      
where  P03.DATEFROM = (select MAX(DATEFROM) from PW001P03 old
					where old.PIN = P01.PIN
            AND old.DATEFROM <= per.COMPLETIONDATE
            AND (ISNULL(old.DATETO, old.TODATEESTIMATED) >= per.PREPARATIONDATE
                    OR ISNULL(old.DATETO, old.TODATEESTIMATED) IS NULL
                )
			AND  old.CODE IN (SELECT t.CODE
                               FROM pw001c12 t
                               WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
                             )
				 AND NOT EXISTS (SELECT 1 FROM pw001p03 p03t
                          WHERE p03t.PIN = old.PIN  AND p03t.DATEFROM <= per.COMPLETIONDATE
                 AND (ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) >= per.PREPARATIONDATE
                    OR ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) IS NULL
                      )
				AND dbo.ad_scanorgtree(p03t.NUMORGID, 3) = dbo.ad_scanorgtree(old.NUMORGID, 3) 
				       AND p03t.code IN (SELECT t.CODE
                              FROM pw001c12 t
                               WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
                              )
                    AND p03t.DATEFROM > old.DateFrom))