26: SELECT Adjustment_id,transaction_header_id,
27: transaction_type_code,transaction_sub_type,
28: transaction_date_entered,
29: period_counter,adjustment_status
30: FROM IGI_IAC_TRANSACTION_HEADERS
31: WHERE asset_id = p_asset_id
32: AND book_type_code = p_book_type_code
33: AND transaction_type_code IN ('ADDITION','REVALUATION','RECLASS','ADJUSTMENT')
34: AND transaction_sub_type IN ('REVALUATION','OCCASSIONAL','INDEXED','CATCHUP','PROFESSIONAL','IMPLEMENTATION','COST')
37:
38: -- Selecting the previous adjustment id to get previous cost before revaluaton
39: CURSOR c_get_iac_Prev_transactions (P_adj_id Number , p_trans_type varchar) IS
40: SELECT *
41: FROM IGI_IAC_TRANSACTION_HEADERS
42: WHERE asset_id = p_asset_id
43: AND book_type_code = p_book_type_code
44: AND adjustment_id = ( SELECT MAX(adjustment_id)
45: FROM IGI_IAC_TRANSACTION_HEADERS
41: FROM IGI_IAC_TRANSACTION_HEADERS
42: WHERE asset_id = p_asset_id
43: AND book_type_code = p_book_type_code
44: AND adjustment_id = ( SELECT MAX(adjustment_id)
45: FROM IGI_IAC_TRANSACTION_HEADERS
46: WHERE asset_id = p_asset_id
47: AND book_type_code = p_book_type_code
48: AND Adjustment_status IN ( 'COMPLETE','RUN')
49: AND adjustment_id < p_adj_id)
51:
52: -- to get the previous transaction for reclass
53: CURSOR c_get_iac_Prev_reclass (P_adj_id Number , p_trans_type varchar) IS
54: SELECT *
55: FROM IGI_IAC_TRANSACTION_HEADERS
56: WHERE asset_id = p_asset_id
57: AND book_type_code = p_book_type_code
58: AND adjustment_id = ( SELECT MAX(adjustment_id)
59: FROM IGI_IAC_TRANSACTION_HEADERS
55: FROM IGI_IAC_TRANSACTION_HEADERS
56: WHERE asset_id = p_asset_id
57: AND book_type_code = p_book_type_code
58: AND adjustment_id = ( SELECT MAX(adjustment_id)
59: FROM IGI_IAC_TRANSACTION_HEADERS
60: WHERE asset_id = p_asset_id
61: AND book_type_code = p_book_type_code
62: AND Adjustment_status IN ( 'COMPLETE','RUN')
63: AND transaction_type_code = 'RECLASS'
75: WHERE asset_id = p_asset_id
76: AND book_type_code = p_book_type_code;
77:
78: --- fecthing the Fa_cost if transaction is Addition or Revaluation;
79: CURSOR C_get_fa_cost ( P_adj_id igi_iac_transaction_headers.adjustment_id%type)
80: IS
81: SELECT cost,date_placed_in_service
82: FROM fa_books fb,igi_iac_transactioN_headers igth
83: WHERE fb.asset_id = p_asset_id
78: --- fecthing the Fa_cost if transaction is Addition or Revaluation;
79: CURSOR C_get_fa_cost ( P_adj_id igi_iac_transaction_headers.adjustment_id%type)
80: IS
81: SELECT cost,date_placed_in_service
82: FROM fa_books fb,igi_iac_transactioN_headers igth
83: WHERE fb.asset_id = p_asset_id
84: AND fb.book_type_code =p_book_type_code
85: AND fb.asset_id = igth.asset_id
86: AND fb.book_type_code = igth.book_type_code
88: and fb.date_effective < igth.transaction_date_entered
89: and nvl(fb.date_ineffective,igth.transaction_date_entered) >= igth.transaction_date_entered;
90:
91: -- bug 3394103 start 1, fetching previous fa cost for all trxs but addition and revaluation
92: CURSOR c_get_fa_cost_prev(cp_trx_hdr_id igi_iac_transaction_headers.transaction_header_id%TYPE,
93: cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
94: IS
95: SELECT cost,
96: date_placed_in_service
89: and nvl(fb.date_ineffective,igth.transaction_date_entered) >= igth.transaction_date_entered;
90:
91: -- bug 3394103 start 1, fetching previous fa cost for all trxs but addition and revaluation
92: CURSOR c_get_fa_cost_prev(cp_trx_hdr_id igi_iac_transaction_headers.transaction_header_id%TYPE,
93: cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
94: IS
95: SELECT cost,
96: date_placed_in_service
97: FROM fa_books fb,
94: IS
95: SELECT cost,
96: date_placed_in_service
97: FROM fa_books fb,
98: igi_iac_transaction_headers igth
99: WHERE fb.asset_id = p_asset_id
100: AND fb.book_type_code =p_book_type_code
101: AND fb.asset_id = igth.asset_id
102: AND fb.book_type_code = igth.book_type_code
104: AND igth.transaction_header_id = cp_trx_hdr_id
105: AND igth.transaction_header_id = fb.transaction_header_id_out;
106:
107:
108: CURSOR c_get_fa_cost_curr(cp_trx_hdr_id igi_iac_transaction_headers.transaction_header_id%TYPE,
109: cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
110: IS
111: SELECT cost,
112: date_placed_in_service
105: AND igth.transaction_header_id = fb.transaction_header_id_out;
106:
107:
108: CURSOR c_get_fa_cost_curr(cp_trx_hdr_id igi_iac_transaction_headers.transaction_header_id%TYPE,
109: cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
110: IS
111: SELECT cost,
112: date_placed_in_service
113: FROM fa_books fb,
110: IS
111: SELECT cost,
112: date_placed_in_service
113: FROM fa_books fb,
114: igi_iac_transaction_headers igth
115: WHERE fb.asset_id = p_asset_id
116: AND fb.book_type_code =p_book_type_code
117: AND fb.asset_id = igth.asset_id
118: AND fb.book_type_code = igth.book_type_code
122: -- bug 3394103 end 1
123:
124: -- bug 3587648, start 1
125: -- declare cursors to obtain fa_cost for Reclass transactions
126: CURSOR c_get_fa_recl_cost_curr(cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
127: IS
128: SELECT cost,
129: date_placed_in_service
130: FROM fa_books fb,
127: IS
128: SELECT cost,
129: date_placed_in_service
130: FROM fa_books fb,
131: igi_iac_transaction_headers igth
132: WHERE fb.asset_id = p_asset_id
133: AND fb.book_type_code = p_book_type_code
134: AND fb.asset_id = igth.asset_id
135: AND fb.book_type_code = igth.book_type_code
139: WHERE fah.transaction_header_id_in = igth.transaction_header_id
140: AND fah.asset_id = p_asset_id)
141: AND nvl(fb.date_ineffective,igth.transaction_date_entered) >= igth.transaction_date_entered;
142:
143: CURSOR c_get_fa_recl_cost_prev(cp_adj_id igi_iac_transaction_headers.adjustment_id%TYPE)
144: IS
145: SELECT cost,
146: date_placed_in_service
147: FROM fa_books fb,
144: IS
145: SELECT cost,
146: date_placed_in_service
147: FROM fa_books fb,
148: igi_iac_transactioN_headers igth
149: WHERE fb.asset_id = p_asset_id
150: AND fb.book_type_code = p_book_type_code
151: AND fb.asset_id = igth.asset_id
152: AND fb.book_type_code = igth.book_type_code
191:
192: l_get_iac_cost c_get_iac_cost%ROWTYPE;
193: l_get_adj_type c_get_adj_type%rowtype;
194:
195: TYPE t_Adjustment_id IS TABLE OF igi_iac_transaction_headers.adjustment_Id%TYPE;
196: TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
192: l_get_iac_cost c_get_iac_cost%ROWTYPE;
193: l_get_adj_type c_get_adj_type%rowtype;
194:
195: TYPE t_Adjustment_id IS TABLE OF igi_iac_transaction_headers.adjustment_Id%TYPE;
196: TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200: TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
193: l_get_adj_type c_get_adj_type%rowtype;
194:
195: TYPE t_Adjustment_id IS TABLE OF igi_iac_transaction_headers.adjustment_Id%TYPE;
196: TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200: TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
201: TYPE t_adjustment_status IS TABLE OF igi_iac_transaction_headers.adjustment_status%TYPE;
194:
195: TYPE t_Adjustment_id IS TABLE OF igi_iac_transaction_headers.adjustment_Id%TYPE;
196: TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200: TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
201: TYPE t_adjustment_status IS TABLE OF igi_iac_transaction_headers.adjustment_status%TYPE;
202:
195: TYPE t_Adjustment_id IS TABLE OF igi_iac_transaction_headers.adjustment_Id%TYPE;
196: TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200: TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
201: TYPE t_adjustment_status IS TABLE OF igi_iac_transaction_headers.adjustment_status%TYPE;
202:
203: l_adjustment_id t_adjustment_id;
196: TYPE t_transaction_header_id IS TABLE OF igi_iac_transaction_headers.transaction_header_id%TYPE;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200: TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
201: TYPE t_adjustment_status IS TABLE OF igi_iac_transaction_headers.adjustment_status%TYPE;
202:
203: l_adjustment_id t_adjustment_id;
204: l_transaction_header_id t_transaction_header_id;
197: TYPE t_transaction_type_code IS TABLE OF igi_iac_transaction_headers.transaction_type_code%TYPE;
198: TYPE t_transaction_sub_type IS TABLE OF igi_iac_transaction_headers.transaction_sub_type%TYPE;
199: TYPE t_transaction_date_entered IS TABLE OF igi_iac_transaction_headers.transaction_date_entered%TYPE;
200: TYPE t_period_counter IS TABLE OF igi_iac_transaction_headers.period_counter%TYPE;
201: TYPE t_adjustment_status IS TABLE OF igi_iac_transaction_headers.adjustment_status%TYPE;
202:
203: l_adjustment_id t_adjustment_id;
204: l_transaction_header_id t_transaction_header_id;
205: l_transaction_type_code t_transaction_type_code;
215: l_prd_rec_pre igi_iac_types.prd_rec;
216: l_idx1 BINARY_INTEGER;
217: l_idx2 BINARY_INTEGER;
218:
219: TYPE reval_history IS RECORD ( adjustment_id igi_iac_transaction_headers.adjustment_Id%TYPE,
220: transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
216: l_idx1 BINARY_INTEGER;
217: l_idx2 BINARY_INTEGER;
218:
219: TYPE reval_history IS RECORD ( adjustment_id igi_iac_transaction_headers.adjustment_Id%TYPE,
220: transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224: period_counter igi_iac_transaction_headers.period_counter%TYPE,
217: l_idx2 BINARY_INTEGER;
218:
219: TYPE reval_history IS RECORD ( adjustment_id igi_iac_transaction_headers.adjustment_Id%TYPE,
220: transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224: period_counter igi_iac_transaction_headers.period_counter%TYPE,
225: adjustment_status igi_iac_transaction_headers.adjustment_status%TYPE
218:
219: TYPE reval_history IS RECORD ( adjustment_id igi_iac_transaction_headers.adjustment_Id%TYPE,
220: transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224: period_counter igi_iac_transaction_headers.period_counter%TYPE,
225: adjustment_status igi_iac_transaction_headers.adjustment_status%TYPE
226: );
219: TYPE reval_history IS RECORD ( adjustment_id igi_iac_transaction_headers.adjustment_Id%TYPE,
220: transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224: period_counter igi_iac_transaction_headers.period_counter%TYPE,
225: adjustment_status igi_iac_transaction_headers.adjustment_status%TYPE
226: );
227:
220: transaction_header_id igi_iac_transaction_headers.transaction_header_id%TYPE,
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224: period_counter igi_iac_transaction_headers.period_counter%TYPE,
225: adjustment_status igi_iac_transaction_headers.adjustment_status%TYPE
226: );
227:
228: TYPE Iac_reval_history IS TABLE OF reval_history INDEX BY BINARY_INTEGER;
221: transaction_type_code igi_iac_transaction_headers.transaction_type_code%TYPE,
222: transaction_sub_type igi_iac_transaction_headers.transaction_sub_type%TYPE,
223: transaction_date_entered igi_iac_transaction_headers.transaction_date_entered%TYPE,
224: period_counter igi_iac_transaction_headers.period_counter%TYPE,
225: adjustment_status igi_iac_transaction_headers.adjustment_status%TYPE
226: );
227:
228: TYPE Iac_reval_history IS TABLE OF reval_history INDEX BY BINARY_INTEGER;
229: