VIEW: APPS.FV_1099T_V

Source

View Text - Preformatted

SELECT '          ' ||' ' ||rpad(nvl(substr(replace(translate(tax_id, '!@#$%^&*()_+-={}|[]\:;<>?,./ ',' '),' '),1,9),' '),9) ||rpad(substr(account_number,1,20),20) ||'       ' ||'0000000000' ||to_char(amount,'S0000000V99') ||to_char(finance_charge_amount,'S0000000V99') ||'0000000000' ||'0000000000' ||'0000000000' ||'0000000000' ||'0000000000' ||'0000000000' ||'                    ' ||decode(country,'US',' ', 'AS',' ', 'FM',' ', 'GU',' ', 'MH',' ', 'MP',' ', 'PR',' ', 'VI',' ', '1') ||rpad(substr(customer_name,1,40),40) ||rpad(nvl(substr(customer_name,41,50),' '),40) ||rpad(substr(address1||' '||address2||' '||address3||' '||address4,1,40),40) ||decode(country,'US',rpad(substr(city,1,29),29), 'AS',rpad(substr(city,1,29),29), 'FM',rpad(substr(city,1,29),29), 'GU',rpad(substr(city,1,29),29), 'MH',rpad(substr(city,1,29),29), 'MP',rpad(substr(city,1,29),29), 'PR',rpad(substr(city,1,29),29), 'VI',rpad(substr(city,1,29),29), rpad(substr(substr(city,1,29)||province||substr(state,1,2)||substr(replace(postal_code,'-',''),1,9)||country,1,40),40) ) ||decode(country,'US',rpad(substr(state,1,2),2), 'AS',rpad(substr(state,1,2),2), 'FM',rpad(substr(state,1,2),2), 'GU',rpad(substr(state,1,2),2), 'MH',rpad(substr(state,1,2),2), 'MP',rpad(substr(state,1,2),2), 'PR',rpad(substr(state,1,2),2), 'VI',rpad(substr(state,1,2),2)) ||decode(country,'US',lpad(substr(replace(postal_code,'-',''),1,9),9), 'AS',lpad(substr(replace(postal_code,'-',''),1,9),9), 'FM',lpad(substr(replace(postal_code,'-',''),1,9),9), 'GU',lpad(substr(replace(postal_code,'-',''),1,9),9), 'MH',lpad(substr(replace(postal_code,'-',''),1,9),9), 'MP',lpad(substr(replace(postal_code,'-',''),1,9),9), 'PR',lpad(substr(replace(postal_code,'-',''),1,9),9), 'VI',lpad(substr(replace(postal_code,'-',''),1,9),9)) ||'                            ' ||'                     ' ||to_char(date_canceled,'mmddyy') ||decode(bankruptcy_code,'Y','1',' ') ||rpad(substr(debt_description,1,39),39) ||'    ' fvr_1099t, amount, set_of_books_id, org_id FROM fv_1099c WHERE reportable_flag = 'Y'

View Text - HTML Formatted

SELECT ' ' ||' ' ||RPAD(NVL(SUBSTR(REPLACE(TRANSLATE(TAX_ID
, '!@#$%^&*()_+-={}|[]\:;<>?
, ./ '
, ' ')
, ' ')
, 1
, 9)
, ' ')
, 9) ||RPAD(SUBSTR(ACCOUNT_NUMBER
, 1
, 20)
, 20) ||' ' ||'0000000000' ||TO_CHAR(AMOUNT
, 'S0000000V99') ||TO_CHAR(FINANCE_CHARGE_AMOUNT
, 'S0000000V99') ||'0000000000' ||'0000000000' ||'0000000000' ||'0000000000' ||'0000000000' ||'0000000000' ||' ' ||DECODE(COUNTRY
, 'US'
, ' '
, 'AS'
, ' '
, 'FM'
, ' '
, 'GU'
, ' '
, 'MH'
, ' '
, 'MP'
, ' '
, 'PR'
, ' '
, 'VI'
, ' '
, '1') ||RPAD(SUBSTR(CUSTOMER_NAME
, 1
, 40)
, 40) ||RPAD(NVL(SUBSTR(CUSTOMER_NAME
, 41
, 50)
, ' ')
, 40) ||RPAD(SUBSTR(ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4
, 1
, 40)
, 40) ||DECODE(COUNTRY
, 'US'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'AS'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'FM'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'GU'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'MH'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'MP'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'PR'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, 'VI'
, RPAD(SUBSTR(CITY
, 1
, 29)
, 29)
, RPAD(SUBSTR(SUBSTR(CITY
, 1
, 29)||PROVINCE||SUBSTR(STATE
, 1
, 2)||SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)||COUNTRY
, 1
, 40)
, 40) ) ||DECODE(COUNTRY
, 'US'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'AS'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'FM'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'GU'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'MH'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'MP'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'PR'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)
, 'VI'
, RPAD(SUBSTR(STATE
, 1
, 2)
, 2)) ||DECODE(COUNTRY
, 'US'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'AS'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'FM'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'GU'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'MH'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'MP'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'PR'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)
, 'VI'
, LPAD(SUBSTR(REPLACE(POSTAL_CODE
, '-'
, '')
, 1
, 9)
, 9)) ||' ' ||' ' ||TO_CHAR(DATE_CANCELED
, 'MMDDYY') ||DECODE(BANKRUPTCY_CODE
, 'Y'
, '1'
, ' ') ||RPAD(SUBSTR(DEBT_DESCRIPTION
, 1
, 39)
, 39) ||' ' FVR_1099T
, AMOUNT
, SET_OF_BOOKS_ID
, ORG_ID
FROM FV_1099C
WHERE REPORTABLE_FLAG = 'Y'