JL Colombian Fixed Assets Technical Appraisal Additions - draft

SELECT    ap1.appraiser_name   appraiser_name,
                 substr(ap1.appraiser_address1,1,40) ||', '|| 
                 substr(ap1.appraiser_address2,1,40) ||', '||   
                 substr(ap1.appraiser_city,1,20)     address,
                 ap1.appraisal_date   appraisal_date,
                 ap1.fiscal_year      fiscal_year,
                 ap1.currency_code    currency_code,
                 flh.meaning          hdr_status,
                 asp1.asset_number     asset_number,
                adtl.description      description,
                ad.tag_number       tag_number,
              asp1.appraisal_value appraisal_value,
              fld.meaning         status, 
	JL_JLCOFAAR_XMLP_PKG.cf_1formula() CF_1
FROM      jl_co_fa_appraisals  ap1,
          jl_co_fa_asset_apprs asp1,
          fa_additions_b         ad,
          fa_additions_tl       adtl,
          fnd_lookups          flh,
          fnd_lookups          fld
WHERE
   ad.asset_id = adtl.asset_id
  AND      adtl.language = userenv('LANG')  
  AND     ap1.appraisal_id   = :P_APPRAISAL_ID
  AND     asp1.appraisal_id   = ap1.appraisal_id
  AND     asp1.asset_number = ad.asset_number(+)
&C_STATUS
  AND     flh.lookup_type   = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
  AND     flh.lookup_code   = ap1.appraisal_status
  AND     fld.lookup_type   = flh.lookup_type
  AND     fld.lookup_code   = asp1.status
  AND     UPPER(fld.enabled_flag) = 'Y'
  AND     SYSDATE BETWEEN NVL(fld.start_date_active,SYSDATE-1) 
              AND NVL(fld.end_date_active,SYSDATE+1)
UNION
SELECT    ap1.appraiser_name   appraiser_name,
                 substr(ap1.appraiser_address1,1,40) ||', '|| 
                 substr(ap1.appraiser_address2,1,40) ||', '||   
                 substr(ap1.appraiser_city,1,20)     address,
                 ap1.appraisal_date   appraisal_date,
                 ap1.fiscal_year      fiscal_year,
                 ap1.currency_code    currency_code,
                 flh.meaning          hdr_status,
                 asp1.asset_number     asset_number,
                 null      description,
                 null       tag_number,
                 asp1.appraisal_value  appraisal_value,
                 fld.meaning         status, 
	JL_JLCOFAAR_XMLP_PKG.cf_1formula() CF_1
FROM      jl_co_fa_appraisals  ap1,
          jl_co_fa_asset_apprs asp1,
          fnd_lookups          flh,
          fnd_lookups          fld
WHERE   ap1.appraisal_id   = :P_APPRAISAL_ID
  AND     asp1.appraisal_id   = ap1.appraisal_id
  AND     NOT EXISTS  (select ad.asset_number from fa_additions_b ad
                                        where ad.asset_number = asp1.asset_number)
&C_STATUS
  AND     flh.lookup_type   = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
  AND     flh.lookup_code   = ap1.appraisal_status
  AND     fld.lookup_type   = flh.lookup_type
  AND     fld.lookup_code   = asp1.status
  AND     UPPER(fld.enabled_flag) = 'Y'
  AND     SYSDATE BETWEEN NVL(fld.start_date_active,SYSDATE-1) 
              AND NVL(fld.end_date_active,SYSDATE+1)
UNION
SELECT    ap1.appraiser_name   appraiser_name,
                 substr(ap1.appraiser_address1,1,40) ||', '|| 
                 substr(ap1.appraiser_address2,1,40) ||', '||   
                 substr(ap1.appraiser_city,1,20)     address,
                 ap1.appraisal_date   appraisal_date,
                 ap1.fiscal_year      fiscal_year,
                 ap1.currency_code    currency_code,
                 flh.meaning          hdr_status,
                 null     asset_number,
                 null      description,
                 null       tag_number,
                 0       appraisal_value,
                 null         status, 
	JL_JLCOFAAR_XMLP_PKG.cf_1formula() CF_1
FROM      jl_co_fa_appraisals  ap1,
                fnd_lookups          flh
WHERE   ap1.appraisal_id   = :P_APPRAISAL_ID
  AND      ap1.appraisal_status = 'V'
  AND      (:P_ALL_ROWS <> 'Y' AND :P_ALL_ROWS is not null)
  AND      flh.lookup_type   = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
  AND      flh.lookup_code   = ap1.appraisal_status
  AND      UPPER(flh.enabled_flag) = 'Y'
  AND      SYSDATE BETWEEN NVL(flh.start_date_active,SYSDATE-1) 
               AND NVL(flh.end_date_active,SYSDATE+1)
ORDER BY asset_number