[Home] [Help]
PACKAGE BODY: APPS.CSE_ASSET_ADJUST_PKG
Source
1 PACKAGE BODY CSE_ASSET_ADJUST_PKG AS
2 -- $Header: CSEFADJB.pls 120.1.12020000.2 2012/07/04 15:24:03 dsingire ship $
3
4 PROCEDURE process_adjustment_trans(
5 x_return_code OUT NOCOPY VARCHAR2
6 ,x_err_buffer OUT NOCOPY VARCHAR2
7 ,p_inv_org_id IN NUMBER
8 ,p_inventory_item_id IN NUMBER
9 , p_conc_request_id IN NUMBER )
10 IS
11 BEGIN
12 NULL ;
13 END process_adjustment_trans ;
14
15
16 PROCEDURE retire_asset (
17 p_ret_asset_rec IN cse_datastructures_pub.asset_query_rec
18 ,p_ret_dist_tbl IN cse_datastructures_pub.distribution_tbl
19 ,p_transaction_id IN NUMBER
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_error_msg OUT NOCOPY VARCHAR2)
22 IS
23
24 BEGIN
25 NULL ;
26 END retire_asset ;
27
28
29
30 PROCEDURE insert_retirement (
31 p_ext_ret_rec IN OUT NOCOPY fa_mass_ext_retirements%ROWTYPE
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_error_msg OUT NOCOPY VARCHAR2)
34 IS
35 l_api_name VARCHAR2(100) := 'CSE_ASSET_ADJUST_PKG.insert_retirement' ;
36
37 BEGIN
38 cse_util_pkg.write_log('Begin --Insert Retirements ');
39 INSERT INTO fa_mass_ext_retirements (
40 BATCH_NAME ,
41 MASS_EXTERNAL_RETIRE_ID ,
42 RETIREMENT_ID ,
43 BOOK_TYPE_CODE ,
44 REVIEW_STATUS ,
45 ASSET_ID ,
46 DISTRIBUTION_ID ,
47 TRANSACTION_NAME ,
48 DATE_RETIRED ,
49 DATE_EFFECTIVE ,
50 COST_RETIRED ,
51 RETIREMENT_PRORATE_CONVENTION ,
52 UNITS ,
53 PERCENTAGE ,
54 COST_OF_REMOVAL ,
55 PROCEEDS_OF_SALE ,
56 RETIREMENT_TYPE_CODE ,
57 REFERENCE_NUM ,
58 SOLD_TO ,
59 TRADE_IN_ASSET_ID ,
60 CALC_GAIN_LOSS_FLAG,
61 STL_METHOD_CODE ,
62 STL_LIFE_IN_MONTHS ,
63 STL_DEPRN_AMOUNT ,
64 CREATED_BY ,
65 CREATION_DATE ,
66 LAST_UPDATED_BY ,
67 LAST_UPDATE_DATE ,
68 LAST_UPDATE_LOGIN )
69 VALUES
70 (p_ext_ret_rec.BATCH_NAME ,
71 p_ext_ret_rec.MASS_EXTERNAL_RETIRE_ID ,
72 p_ext_ret_rec.RETIREMENT_ID ,
73 p_ext_ret_rec.BOOK_TYPE_CODE ,
74 p_ext_ret_rec.REVIEW_STATUS ,
75 p_ext_ret_rec.ASSET_ID ,
76 p_ext_ret_rec.DISTRIBUTION_ID ,
77 p_ext_ret_rec.TRANSACTION_NAME ,
78 p_ext_ret_rec.DATE_RETIRED ,
79 p_ext_ret_rec.DATE_EFFECTIVE ,
80 p_ext_ret_rec.COST_RETIRED ,
81 p_ext_ret_rec.RETIREMENT_PRORATE_CONVENTION ,
82 p_ext_ret_rec.UNITS ,
83 p_ext_ret_rec.PERCENTAGE ,
84 p_ext_ret_rec.COST_OF_REMOVAL ,
85 --p_ext_ret_rec.PROCEEDS_OF_SALE ,
86 DECODE(p_ext_ret_rec.PROCEEDS_OF_SALE,fnd_api.g_miss_num,null,p_ext_ret_rec.PROCEEDS_OF_SALE), --bug 10095659
87 p_ext_ret_rec.RETIREMENT_TYPE_CODE ,
88 p_ext_ret_rec.REFERENCE_NUM ,
89 p_ext_ret_rec.SOLD_TO ,
90 p_ext_ret_rec.TRADE_IN_ASSET_ID ,
91 p_ext_ret_rec.CALC_GAIN_LOSS_FLAG,
92 p_ext_ret_rec.STL_METHOD_CODE ,
93 p_ext_ret_rec.STL_LIFE_IN_MONTHS ,
94 p_ext_ret_rec.STL_DEPRN_AMOUNT ,
95 p_ext_ret_rec.CREATED_BY ,
96 p_ext_ret_rec.CREATION_DATE ,
97 p_ext_ret_rec.LAST_UPDATED_BY ,
98 p_ext_ret_rec.LAST_UPDATE_DATE ,
99 p_ext_ret_rec.LAST_UPDATE_LOGIN ) ;
100
101 EXCEPTION
102 WHEN OTHERS
103 THEN
104 x_return_status := fnd_api.G_RET_STS_ERROR ;
105 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
106 fnd_message.set_token('API_NAME',l_api_name);
107 fnd_message.set_token('SQL_ERROR',SQLERRM);
108 x_error_msg := fnd_message.get;
109
110 END insert_retirement ;
111
112
113
114 PROCEDURE create_inv_rets(p_asset_id IN NUMBER,
115 p_mass_ext_retire_id IN NUMBER,
116 p_mtl_cost IN OUT NOCOPY NUMBER,
117 p_non_mtl_cost IN OUT NOCOPY NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_error_msg OUT NOCOPY VARCHAR2)
120 IS
121 l_api_name VARCHAR2(100) := 'CSE_ASSET_ADJUST_PKG.create_inv_rets';
122 l_cost NUMBER ;
123 CURSOR mtl_src_lines_cur IS
124 SELECT source_line_id
125 , fixed_assets_cost cost
126 FROM fa_asset_invoices
127 WHERE SIGN(fixed_assets_cost) = SIGN(p_mtl_cost)
128 AND NVL(attribute15,'N') = 'Y'
129 AND date_ineffective IS NULL
130 AND asset_id = p_asset_id ;
131
132
133 CURSOR non_mtl_src_lines_cur IS
134 SELECT source_line_id
135 , fixed_assets_cost cost
136 FROM fa_asset_invoices
137 WHERE SIGN(fixed_assets_cost) = SIGN(p_mtl_cost)
138 AND NVL(attribute15,'N') <> 'Y'
139 AND date_ineffective IS NULL
140 AND asset_id = p_asset_id ;
141
142 BEGIN
143 FOR mtl_src_lines_rec IN mtl_src_lines_cur
144 LOOP
145 IF ABS(mtl_src_lines_rec.cost) < ABS(p_mtl_cost)
146 THEN
147 l_cost := mtl_src_lines_rec.cost ;
148 ELSE
149 l_cost := p_mtl_cost ;
150 END IF ;
151 IF l_cost = 0
152 THEN
153 EXIT ;
154 END IF ;
155 INSERT INTO fa_ext_inv_retirements (
156 mass_external_retire_id
157 , source_line_id
158 , cost_retired )
159 VALUES ( p_mass_ext_retire_id
160 , mtl_src_lines_rec.source_line_id
161 , l_cost) ;
162 p_mtl_cost := p_mtl_cost - l_cost ;
163 END LOOP ;
164 FOR non_mtl_src_lines_rec IN non_mtl_src_lines_cur
165 LOOP
166 IF ABS(non_mtl_src_lines_rec.cost) < ABS(p_non_mtl_cost)
167 THEN
168 l_cost := non_mtl_src_lines_rec.cost ;
169 ELSE
170 l_cost := p_non_mtl_cost ;
171 END IF ;
172 IF l_cost = 0
173 THEN
174 EXIT ;
175 END IF ;
176 INSERT INTO fa_ext_inv_retirements (
177 mass_external_retire_id
178 , source_line_id
179 , cost_retired )
180 VALUES ( p_mass_ext_retire_id
181 , non_mtl_src_lines_rec.source_line_id
182 , l_cost) ;
183 END LOOP ;
184 EXCEPTION
185 WHEN OTHERS
186 THEN
187 x_return_status := fnd_api.G_RET_STS_ERROR ;
188 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
189 fnd_message.set_token('API_NAME',l_api_name);
190 fnd_message.set_token('SQL_ERROR',SQLERRM);
191 x_error_msg := fnd_message.get;
192
193 END create_inv_rets ;
194
195
196 END CSE_ASSET_ADJUST_PKG ;