1 PACKAGE BODY PA_CLIENT_EXTN_ASSET_ALLOC AS
2 -- $Header: PACCXAAB.pls 115.2 2003/08/18 14:30:24 ajdas noship $
3
4
5
6 PROCEDURE ASSET_ALLOC_BASIS(p_project_asset_line_id IN NUMBER,
7 p_project_id IN NUMBER,
8 p_asset_basis_table IN OUT NOCOPY PA_ASSET_ALLOCATION_PVT.ASSET_BASIS_TABLE_TYPE,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2) IS
12
13
14 v_total_basis_amount NUMBER := 0;
15 v_asset_basis_amount NUMBER := 0;
16 v_project_asset_id NUMBER;
17
18 zero_total_basis EXCEPTION;
19 negative_total_basis EXCEPTION;
20 null_asset_basis EXCEPTION;
21 negative_asset_basis EXCEPTION;
22
23 BEGIN
24 /**********************************************************************************
25
26 This is a client extension package called by the PA_ASSET_ALLOCATION_PVT.
27 ALLOCATE_UNASSIGNED procedure. It is called once for every UNASSIGNED asset
28 line where the project (or batch) has an Asset Allocation Method equal to 'CE'
29 (Client Extension Basis). It will allow the customer to determine the Total
30 Basis Amount and the Asset Basis Amount for each asset in the array, using
31 whatever logic they choose.
32
33 The p_asset_basis_table will be passed into the Client Extension procedure. It
34 is a table indexed by Binary Integer with three columns:
35
36 PROJECT_ASSET_ID NUMBER;
37 ASSET_BASIS_AMOUNT NUMBER;
38 TOTAL_BASIS_AMOUNT NUMBER;
39
40 The table will already be populated with values for Project Asset ID, which
41 correspond to the assets associated with the current UNASSIGNED asset line via
42 Grouping Levels and Asset Assignments. The basis amount columns will contain
43 zeros, and purpose of this extension to determine and populate those fields.
44 Note that the Total Basis Amount should be identical for each row in the table.
45 The customer may use whatever logic they choose as a method for determining
46 the basis amounts for each asset and in total.
47
48 One possible method is that the costs currently assigned to each asset could
49 be used as the basis. This would enable customers to use a "Direct Cost"
50 allocation method, where they would use the Asset Assignment Client Extension
51 to assign individual expenditure items (such as inventory issues and supplier
52 invoices) to specific assets, and then use these amounts as the allocation basis
53 for indirect costs on the project (such as labor and overheads).
54
55 Checks will be performed prior to the allocation logic which will verify that:
56
57 1) Each Project Asset ID is valid for the project,
58 2) the Project Asset Date Placed in Service is specified,
59 3) the Project Asset CAPITAL_HOLD_FLAG is set to 'N', meaning that the
60 asset is eligible for new asset line generation,
61 4) the Project Asset Type is 'AS-BUILT' for capital Asset Lines (Line Type = 'C'),
62 5) the Project Asset Type is 'RETIREMENT_ADJUSTMENT' for retirement cost Asset Lines (Line Type = 'R'),
63 6) the Total Basis Amount is not equal to zero (to avoid division by zero),
64 7) each Asset Basis Amount is NOT NULL and is not negative,
65 8) each project asset in the array refers to the same Total Basis Amount, and
66 9) the Asset Basis Amounts sum up to the Total Basis Amount.
67
68
69 For this reason, clients should ensure that the extension populates the Total
70 Basis Amount and Asset Basis Amount in the p_asset_basis_table in a fashion that
71 satisfies these validations. If the client chooses to modify or add to assets
72 in the p_asset_basis_table, they should ensure that the first five validations above
73 are true for each asset.
74
75 The Total Basis Amount is equal to the sum of all Asset Basis Amounts in the
76 table, and it is stored on each row. The asset allocation will then use the
77 Asset Basis Amount/Total Basis Amount for each project asset to prorate the
78 amount of each UNASSIGNED asset line.
79
80 Sample code is included to show a possible alternate method of basis derivation,
81 as well as a sample of error handling.
82
83 **********************************************************************************/
84
85 x_return_status := 'S';
86 x_msg_count := 0;
87 x_msg_data := NULL;
88
89 --Sample code starts here
90 /********************************************************************************/
91 /* This sample code shows how to populate the Total and Asset Basis amounts */
92 /* using and alternate method. For the example, we use Estimated Units as */
93 /* our alternate basis method, which is not available as a delivered method */
94 /* (Actual Units and Estimated Costs are delivered methods, but not Estimated */
95 /* Units). */
96 /********************************************************************************/
97
98 /* Remove this comment line to enable sample code
99
100 i := asset_basis_table.FIRST;
101
102 --Loop through each asset and assign Asset Basis Amount and calculate the Total Basis
103 WHILE i IS NOT NULL LOOP
104
105 --For Estimated Cost method, Asset Basis Amount = Estimated Cost
106 v_asset_basis_amount := NULL; --Initialize this to NULL in order to trap NULL units as an error
107 v_project_asset_id := p_asset_basis_table(i).PROJECT_ASSET_ID; --Used for error messages
108
109 SELECT estimated_asset_units
110 INTO v_asset_basis_amount
111 FROM pa_project_assets_all
112 WHERE project_asset_id = p_asset_basis_table(i).PROJECT_ASSET_ID;
113
114
115 --Validate that the basis amount is not NULL or negative
116 IF v_asset_basis_amount IS NULL THEN
117 RAISE null_asset_basis;
118 ELSIF v_asset_basis_amount < 0 THEN
119 RAISE negative_asset_basis;
120 END IF;
121
122
123 p_asset_basis_table(i).ASSET_BASIS_AMOUNT := v_asset_basis_amount;
124
125 v_total_basis_amount := v_total_basis_amount + NVL(v_asset_basis_amount,0);
126
127 i := asset_basis_table.NEXT(i);
128
129 END LOOP;
130
131
132 --Validate that the total basis amount is not ZERO or negative
133 IF v_total_basis_amount = 0 THEN
134 RAISE zero_total_basis;
135 ELSIF v_total_basis_amount < 0 THEN
136 RAISE negative_total_basis;
137 END IF;
138
139
140 i := asset_basis_table.FIRST;
141
142 --Loop through each asset and assign Total Basis Amount
143 WHILE i IS NOT NULL LOOP
144
145 p_asset_basis_table(i).TOTAL_BASIS_AMOUNT := v_total_basis_amount;
146
147 i := asset_basis_table.NEXT(i);
148
149 END LOOP;
150
151
152
153 Remove this comment line to enable sample code */
154 --Sample code ends here
155
156
157 RETURN;
158
159 EXCEPTION
160 WHEN zero_total_basis THEN
161 x_return_status := 'E';
162 x_msg_count := x_msg_count + 1;
163 x_msg_data := 'Total basis is ZERO for project id '||p_project_id;
164 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
165 p_procedure_name => 'ASSET_ALLOC_BASIS',
166 p_error_text => SUBSTRB(x_msg_data,1,240));
167 RETURN;
168
169 WHEN negative_total_basis THEN
170 x_return_status := 'E';
171 x_msg_count := x_msg_count + 1;
172 x_msg_data := 'Total basis is negative for project id '||p_project_id;
173 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
174 p_procedure_name => 'ASSET_ALLOC_BASIS',
175 p_error_text => SUBSTRB(x_msg_data,1,240));
176 RETURN;
177
178 WHEN null_asset_basis THEN
179 x_return_status := 'E';
180 x_msg_count := x_msg_count + 1;
181 x_msg_data := 'Asset basis is NULL for project asset id '||v_project_asset_id;
182 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
183 p_procedure_name => 'ASSET_ALLOC_BASIS',
184 p_error_text => SUBSTRB(x_msg_data,1,240));
185 RETURN;
186
187 WHEN negative_asset_basis THEN
188 x_return_status := 'E';
189 x_msg_count := x_msg_count + 1;
190 x_msg_data := 'Asset basis is negative for project asset id '||v_project_asset_id;
191 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
192 p_procedure_name => 'ASSET_ALLOC_BASIS',
193 p_error_text => SUBSTRB(x_msg_data,1,240));
194 RETURN;
195
196 WHEN OTHERS THEN
197 x_return_status := 'U';
198 x_msg_count := x_msg_count + 1;
199 x_msg_data := 'PA_CLIENT_EXTN_ASSET_ALLOC error '||SQLCODE||' '||SQLERRM;
200 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CLIENT_EXTN_ASSET_ALLOC',
201 p_procedure_name => 'ASSET_ALLOC_BASIS',
202 p_error_text => SUBSTRB(x_msg_data,1,240));
203 ROLLBACK;
204 RAISE;
205
206 END;
207
208 END;
[Home] [Help]