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 |