JA India - Depreciation detail report - draft

SELECT   
        a.cost    cost ,
        c.asset_id,
        c.asset_number,
        decode(:p_type, 'Summary', null, 'Detail', c.asset_number) asset_number1,
        b.block_id block_id1,
        d.start_date start_date1,
        d.end_date end_date1,
        d.slno,
        e.description,
        a.transaction_header_id_in ,
        a.date_effective, 
	JA_JAINASST_XMLP_PKG.cf_5formula(c.asset_id, b.block_id) CF_5, 
	JA_JAINASST_XMLP_PKG.cf_additions_costformula(a.cost, a.transaction_header_id_in, a.date_effective, c.asset_id) cf_additions_cost
FROM   
        fa_books                   a,
        JAI_FA_AST_BLOCKS      b,
        jai_fa_ast_block_dtls    c,
        JAI_FA_AST_PERIOD_RATES         d,
        fa_additions               e
WHERE   
        a.asset_id                              = c.asset_id                AND
        e.asset_id                              = a.asset_id                AND
        (
           (
             c.asset_type                       = 'CAPITALIZED'             AND
             a.capitalize_flag                  = 'YES'                
           )                                                                 OR
           c.asset_type                         = 'EXPENSED'                   
        )                       AND
        a.book_type_code                = nvl(:book_name,a.book_type_code)  AND
        a.book_type_code                = b.book_type_code                  AND
        to_char(b.block_id)               = c.block_id                      AND
        a.cost                                   > nvl(d.exempt_upto,0)     AND   
        a.date_ineffective                 is null                          AND
        a.transaction_header_id_out             is null                     AND
        c.date_of_acquisition     BETWEEN d.start_date                      AND
                                          d.end_date                        AND
        b.start_date              BETWEEN nvl(:start_date,b.start_date)     AND
                                                        nvl(:end_date,b.year_ended)       AND
        d.year_start                            = nvl(:start_date,d.year_start)           AND
        d.year_end                             = nvl(:end_date,d.year_end) 
UNION   
SELECT  
         a.cost cost ,
         c.asset_id, 
         c.asset_number, 
         decode(:p_type, 'Summary', null, 'Detail', c.asset_number) asset_number1,
         b.block_id, 
         d.start_date, 
         d.end_date, 
         d.slno, 
         e.description,
         a.transaction_header_id_in ,
         a.date_effective, 
	JA_JAINASST_XMLP_PKG.cf_5formula(c.asset_id, b.block_id) CF_5, 
	JA_JAINASST_XMLP_PKG.cf_additions_costformula(a.cost, a.transaction_header_id_in, a.date_effective, c.asset_id) cf_additions_cost
FROM    
        fa_books a,
        JAI_FA_AST_BLOCKS b,
        jai_fa_ast_block_dtls c,
        JAI_FA_AST_PERIOD_RATES d,
        fa_additions       e
 WHERE  a.asset_id = c.asset_id AND
        e.asset_id = a.asset_id AND     
        (
           (
             c.asset_type                        = 'CAPITALIZED'             AND
             a.capitalize_flag                   = 'YES'                
           )                                                                   OR
           c.asset_type                        = 'EXPENSED'                   
        )       
        and a.book_type_code = nvl(:book_name,a.book_type_code)
        and to_char(b.block_id) = c.block_id
        and a.cost<= nvl(d.exempt_upto,0)  
        and a.date_ineffective is null
        and a.transaction_header_id_out is null
        and a.date_placed_in_service between d.start_date and d.end_date
        and a.book_type_code = b.book_type_code
        and b.start_date  between nvl(:start_date,b.start_date) and  nvl(:end_date,b.year_ended)
        and d.year_start = nvl(:start_date,d.year_start) and d.year_end =nvl(:end_date,d.year_end) 
UNION
SELECT  
        a.cost cost ,
        a.asset_id,
        c.asset_number, 
        decode(:p_type, 'Summary', null, 'Detail', c.asset_number) asset_number1,
        b.block_id block_id1,
        d.start_date,
        d.end_date,
        d.slno ,
        e.description,
        a.transaction_header_id_in ,
        a.date_effective, 
	JA_JAINASST_XMLP_PKG.cf_5formula(c.asset_id, b.block_id) CF_5, 
	JA_JAINASST_XMLP_PKG.cf_additions_costformula(a.cost, a.transaction_header_id_in, a.date_effective, c.asset_id) cf_additions_cost
FROM    
        fa_books a,JAI_FA_AST_BLOCKS b, jai_fa_ast_block_dtls c,JAI_FA_AST_PERIOD_RATES d,fa_additions e
WHERE   
        a.asset_id = c.asset_id
        and e.asset_id = a.asset_id
        and a.book_type_code = nvl(:book_name,a.book_type_code)
        and a.book_type_code = b.book_type_code
        and to_char(b.block_id) = c.block_id
        and a.cost > nvl(d.exempt_upto,0)                       
        and a.date_ineffective is null
        and a.transaction_header_id_out is null
        and c.date_of_acquisition between d.start_date and d.end_date
        and b.start_date between nvl(:start_date,b.start_date) and  nvl(:end_date,b.year_ended)
        and d.year_start = nvl(:start_date,d.year_start) and d.year_end =nvl(:end_date,d.year_end)
UNION   
SELECT  
        a.cost cost ,
        a.asset_id,c.asset_number,decode(:p_type, 'Summary', null, 'Detail', c.asset_number) asset_number1,b.block_id,d.start_date,d.end_date,d.slno , e.description,
        a.transaction_header_id_in ,
        a.date_effective, 
	JA_JAINASST_XMLP_PKG.cf_5formula(c.asset_id, b.block_id) CF_5, 
	JA_JAINASST_XMLP_PKG.cf_additions_costformula(a.cost, a.transaction_header_id_in, a.date_effective, c.asset_id) cf_additions_cost
FROM    
        fa_books a,JAI_FA_AST_BLOCKS b,jai_fa_ast_block_dtls c,JAI_FA_AST_PERIOD_RATES d,fa_additions e 
WHERE   
        a.asset_id = c.asset_id
        and e.asset_id = a.asset_id
        and a.book_type_code = nvl(:book_name,a.book_type_code)
        and to_char(b.block_id) = c.block_id
        and a.cost<= nvl(d.exempt_upto,0)           
        and a.date_ineffective is null
        and a.transaction_header_id_out is null
        and a.date_placed_in_service between d.start_date and d.end_date
        and a.book_type_code = b.book_type_code
        and b.start_date  between nvl(:start_date,b.start_date) and  nvl(:end_date,b.year_ended)
        and d.year_start = nvl(:start_date,d.year_start) and d.year_end =nvl(:end_date,d.year_end) 
 and b.block_id=:block_id