1 TYPE BODY PO_AP_DIST_REC_TYPE AS
2
3 -- Name: create_object
4 -- Function: Constructor method
5 -- Creates a PO_AP_DIST_REC_TYPE object based on the given tables
6 -- of field values. The tables must all be the same size. To
7 -- create an empty object, pass in NULL for p_po_distribution_id.
8 STATIC FUNCTION create_object (
9 p_po_distribution_id_tbl po_tbl_number DEFAULT NULL,
10 p_uom_code_tbl po_tbl_varchar30 DEFAULT NULL,
11 p_quantity_billed_tbl po_tbl_number DEFAULT NULL,
12 p_amount_billed_tbl po_tbl_number DEFAULT NULL,
13 p_quantity_financed_tbl po_tbl_number DEFAULT NULL,
14 p_amount_financed_tbl po_tbl_number DEFAULT NULL,
15 p_quantity_recouped_tbl po_tbl_number DEFAULT NULL,
16 p_amount_recouped_tbl po_tbl_number DEFAULT NULL,
17 p_retainage_withheld_amt_tbl po_tbl_number DEFAULT NULL,
18 p_retainage_released_amt_tbl po_tbl_number DEFAULT NULL,
19 p_last_update_login_tbl po_tbl_number DEFAULT NULL,
20 p_request_id_tbl po_tbl_number DEFAULT NULL
21 ) RETURN PO_AP_DIST_REC_TYPE
22 IS
23 d_position NUMBER := 0;
24 d_mod CONSTANT VARCHAR2(100) := 'po.plsql.PO_AP_DIST_REC_TYPE';
25 l_api_name CONSTANT VARCHAR2(30) := 'create_object';
26 l_dist_changes PO_AP_DIST_REC_TYPE;
27 rowcount NUMBER;
28 BEGIN
29
30 IF PO_LOG.d_proc THEN
31 PO_LOG.proc_begin(d_mod,'p_po_distribution_id_tbl',p_po_distribution_id_tbl);
32 END IF;
33
34 -- Create an empty object first
35 l_dist_changes := PO_AP_DIST_REC_TYPE (
36 po_distribution_id_tbl => po_tbl_number(),
37 uom_code_ap_tbl => po_tbl_varchar30(),
38 uom_code_po_tbl => po_tbl_varchar30(),
39 quantity_billed_ap_tbl => po_tbl_number(),
40 quantity_billed_tbl => po_tbl_number(),
41 amount_billed_tbl => po_tbl_number(),
42 quantity_financed_ap_tbl => po_tbl_number(),
43 quantity_financed_tbl => po_tbl_number(),
44 amount_financed_tbl => po_tbl_number(),
45 quantity_recouped_ap_tbl => po_tbl_number(),
46 quantity_recouped_tbl => po_tbl_number(),
47 amount_recouped_tbl => po_tbl_number(),
48 retainage_withheld_amt_tbl => po_tbl_number(),
49 retainage_released_amt_tbl => po_tbl_number(),
50 last_update_login_tbl => po_tbl_number(),
51 request_id_tbl => po_tbl_number()
52 );
53
54 d_position := 10;
55 IF PO_LOG.d_stmt THEN
56 PO_LOG.stmt(d_mod,d_position,'Empty table created');
57 END IF;
58
59 -- Update the values of the object if params were passed in
60 -- The values are only populated if distribution ids are provided
61 IF (p_po_distribution_id_tbl IS NOT NULL) THEN
62
63 d_position := 20;
64
65 l_dist_changes.po_distribution_id_tbl := p_po_distribution_id_tbl;
66 rowcount := l_dist_changes.po_distribution_id_tbl.COUNT;
67
68 IF PO_LOG.d_stmt THEN
69 PO_LOG.stmt(d_mod,d_position,'Updating table values');
70 PO_LOG.stmt(d_mod,d_position,'Rowcount: ' || rowcount);
71 END IF;
72
73 IF (p_uom_code_tbl IS NOT NULL) THEN
74 l_dist_changes.uom_code_ap_tbl := p_uom_code_tbl;
75 ELSE
76 l_dist_changes.uom_code_ap_tbl.extend(rowcount);
77 END IF;
78
79 IF (p_quantity_billed_tbl IS NOT NULL) THEN
80 l_dist_changes.quantity_billed_ap_tbl := p_quantity_billed_tbl;
81 ELSE
82 l_dist_changes.quantity_billed_ap_tbl.extend(rowcount);
83 END IF;
84
85 IF (p_amount_billed_tbl IS NOT NULL) THEN
86 l_dist_changes.amount_billed_tbl := p_amount_billed_tbl;
87 ELSE
88 l_dist_changes.amount_billed_tbl.extend(rowcount);
89 END IF;
90
91 IF (p_quantity_financed_tbl IS NOT NULL) THEN
92 l_dist_changes.quantity_financed_ap_tbl := p_quantity_financed_tbl;
93 ELSE
94 l_dist_changes.quantity_financed_ap_tbl.extend(rowcount);
95 END IF;
96
97 IF (p_amount_financed_tbl IS NOT NULL) THEN
98 l_dist_changes.amount_financed_tbl := p_amount_financed_tbl;
99 ELSE
100 l_dist_changes.amount_financed_tbl.extend(rowcount);
101 END IF;
102
103 IF (p_quantity_recouped_tbl IS NOT NULL) THEN
104 l_dist_changes.quantity_recouped_ap_tbl := p_quantity_recouped_tbl;
105 ELSE
106 l_dist_changes.quantity_recouped_ap_tbl.extend(rowcount);
107 END IF;
108
109 IF (p_amount_recouped_tbl IS NOT NULL) THEN
110 l_dist_changes.amount_recouped_tbl := p_amount_recouped_tbl;
111 ELSE
112 l_dist_changes.amount_recouped_tbl.extend(rowcount);
113 END IF;
114
115 IF (p_retainage_withheld_amt_tbl IS NOT NULL) THEN
116 l_dist_changes.retainage_withheld_amt_tbl :=
117 p_retainage_withheld_amt_tbl;
118 ELSE
119 l_dist_changes.retainage_withheld_amt_tbl.extend(rowcount);
120 END IF;
121
122 IF (p_retainage_released_amt_tbl IS NOT NULL) THEN
123 l_dist_changes.retainage_released_amt_tbl :=
124 p_retainage_released_amt_tbl;
125 ELSE
126 l_dist_changes.retainage_released_amt_tbl.extend(rowcount);
127 END IF;
128
129 IF (p_last_update_login_tbl IS NOT NULL) THEN
130 l_dist_changes.last_update_login_tbl := p_last_update_login_tbl;
131 ELSE
132 l_dist_changes.last_update_login_tbl.extend(rowcount);
133 END IF;
134
135 IF (p_request_id_tbl IS NOT NULL) THEN
136 l_dist_changes.request_id_tbl := p_request_id_tbl;
137 ELSE
138 l_dist_changes.request_id_tbl.extend(rowcount);
139 END IF;
140
141 d_position := 30;
142 IF PO_LOG.d_stmt THEN
143 PO_LOG.stmt(d_mod,d_position,'Table updates completed');
144 END IF;
145
146 END IF; -- if p_po_distribution_id_tbl is not null
147
148 IF PO_LOG.d_proc THEN
149 PO_LOG.proc_end(d_mod);
150 END IF;
151
152 RETURN l_dist_changes;
153 END create_object;
154
155
156 -- Name: add_change
157 -- Function: Member method
158 -- Adds a row to the PO_AP_DIST_REC_TYPE for an update to
159 -- a particular PO distribution
160 MEMBER PROCEDURE add_change (
161 p_po_distribution_id NUMBER,
162 p_uom_code VARCHAR2 DEFAULT NULL,
163 p_quantity_billed NUMBER DEFAULT NULL,
164 p_amount_billed NUMBER DEFAULT NULL,
165 p_quantity_financed NUMBER DEFAULT NULL,
166 p_amount_financed NUMBER DEFAULT NULL,
167 p_quantity_recouped NUMBER DEFAULT NULL,
168 p_amount_recouped NUMBER DEFAULT NULL,
169 p_retainage_withheld_amt NUMBER DEFAULT NULL,
170 p_retainage_released_amt NUMBER DEFAULT NULL,
171 p_last_update_login NUMBER DEFAULT NULL,
172 p_request_id NUMBER DEFAULT NULL
173 )
174 IS
175 d_position NUMBER := 0;
176 d_mod CONSTANT VARCHAR2(100) := 'po.plsql.PO_AP_DIST_REC_TYPE';
177 l_api_name CONSTANT VARCHAR2(30) := 'add_change';
178 rowcount NUMBER;
179 BEGIN
180 IF PO_LOG.d_proc THEN
181 PO_LOG.proc_begin(d_mod);
182 END IF;
183
184 -- First, extend all the member tables
185 SELF.po_distribution_id_tbl.extend;
186 SELF.uom_code_ap_tbl.extend;
187 SELF.uom_code_po_tbl.extend; --bug 4749047
188 SELF.quantity_billed_ap_tbl.extend;
189 SELF.quantity_billed_tbl.extend; --bug 4749047
190 SELF.amount_billed_tbl.extend;
191 SELF.quantity_financed_ap_tbl.extend;
192 SELF.quantity_financed_tbl.extend; --bug 4749047
193 SELF.amount_financed_tbl.extend;
194 SELF.quantity_recouped_ap_tbl.extend;
195 SELF.quantity_recouped_tbl.extend; --bug 4749047
196 SELF.amount_recouped_tbl.extend;
197 SELF.retainage_withheld_amt_tbl.extend;
198 SELF.retainage_released_amt_tbl.extend;
199 SELF.last_update_login_tbl.extend;
200 SELF.request_id_tbl.extend;
201
202 -- Get the index number of the new last element
203 d_position := 20;
204 rowcount := SELF.po_distribution_id_tbl.LAST; --bug 4749047
205
206 IF PO_LOG.d_stmt THEN
207 PO_LOG.stmt(d_mod,d_position,'rowcount: ' || rowcount);
208 END IF;
209
210 -- Populate the last element with the passed in values
211 SELF.po_distribution_id_tbl(rowcount) := p_po_distribution_id;
212 SELF.uom_code_ap_tbl(rowcount) := p_uom_code;
213 SELF.quantity_billed_ap_tbl(rowcount) := p_quantity_billed;
214 SELF.amount_billed_tbl(rowcount) := p_amount_billed;
215 SELF.quantity_financed_ap_tbl(rowcount) := p_quantity_financed;
216 SELF.amount_financed_tbl(rowcount) := p_amount_financed;
217 SELF.quantity_recouped_ap_tbl(rowcount) := p_quantity_recouped;
218 SELF.amount_recouped_tbl(rowcount) := p_amount_recouped;
219 SELF.retainage_withheld_amt_tbl(rowcount) := p_retainage_withheld_amt;
220 SELF.retainage_released_amt_tbl(rowcount) := p_retainage_released_amt;
221 SELF.last_update_login_tbl(rowcount) := p_last_update_login;
222 SELF.request_id_tbl(rowcount) := p_request_id;
223
224 d_position := 30;
225 IF PO_LOG.d_proc THEN
226 PO_LOG.proc_end(d_mod);
227 END IF;
228
229 END add_change;
230
231
232 -- Name: populate_calculated_fields
233 -- Function: calculates additional information based on
234 -- the data passed in from AP:
235 -- 1. Converts quantities to PO UOM from AP UOM
236 -- 2. Defaults the WHO columns if passed in as NULL
237 MEMBER PROCEDURE populate_calculated_fields
238 IS
239 d_position NUMBER := 0;
240 d_mod CONSTANT VARCHAR2(100) := 'po.plsql.PO_AP_DIST_REC_TYPE';
241 l_api_name CONSTANT VARCHAR2(30) := 'populate_calculated_fields';
242 l_uom_code_po VARCHAR2(25);
243 l_uom_code_ap VARCHAR2(25);
244 l_item_id NUMBER;
245 l_uom_rate NUMBER;
246 BEGIN
247
248 IF PO_LOG.d_proc THEN
249 PO_LOG.proc_begin(d_mod);
250 END IF;
251
252 d_position := 10;
253 -- Get the UOM from AP (based on the first distribution, since
254 -- all distributions in a particular instance of this object belong
255 -- to the same line location
256 l_uom_code_ap := uom_code_ap_tbl(1);
257
258 IF PO_LOG.d_stmt THEN
259 PO_LOG.stmt(d_mod,d_position,'l_uom_code_ap: ' || l_uom_code_ap);
260 END IF;
261
262 d_position := 20;
263
264 -- Get the UOM from the PO (based on the first distribution, since
265 -- all distributions in a particular instance of this object belong
266 -- to the same line location)
267 BEGIN
268 SELECT nvl(PLL.unit_meas_lookup_code, POL.unit_meas_lookup_code),
269 POL.item_id
270 INTO l_uom_code_po, l_item_id
271 FROM PO_DISTRIBUTIONS_ALL POD,
272 PO_LINE_LOCATIONS_ALL PLL,
273 PO_LINES_ALL POL
274 WHERE POD.po_distribution_id = po_distribution_id_tbl(1)
275 AND PLL.line_location_id = POD.line_location_id
276 AND PLL.po_line_id = POL.po_line_id;
277 EXCEPTION
278 WHEN OTHERS THEN
279 IF PO_LOG.d_exc THEN
280 PO_LOG.exc(d_mod,d_position,SQLERRM);
281 END IF;
282 RAISE;
283 END;
284
285 d_position := 30;
286 IF PO_LOG.d_stmt THEN
287 PO_LOG.stmt(d_mod,d_position,'l_uom_code_po: ' || l_uom_code_po);
288 PO_LOG.stmt(d_mod,d_position,'l_item_id: ' || l_item_id);
289 END IF;
290
291 d_position := 40;
292 IF PO_LOG.d_stmt THEN
293 PO_LOG.stmt(d_mod,d_position,'Looping through distributions');
294 END IF;
295
296 FOR i IN 1..po_distribution_id_tbl.COUNT LOOP
297 SELF.uom_code_po_tbl.extend;
298 SELF.quantity_billed_tbl.extend;
299 SELF.quantity_financed_tbl.extend;
300 SELF.quantity_recouped_tbl.extend;
301
302 uom_code_po_tbl(i) := l_uom_code_po;
303
304 --UOM will be NULL for Fixed Price or Rate-based Line Locations,
305 --and for calls to update Retained/Released amount.
306 --So we can ignore the conversion logic in these cases
307 --<bug#5339961 START>
308 --We would want to convert UOMs only if the uom_code_ap is
309 --populated and if the uom_code_ap is different from uom_code_po.
310 --As on date both uom_code_ap and uom_code_po would be the same.
311 --The code below would handle any future requirements of AP to convert.
312
313 IF l_uom_code_po is not null and l_uom_code_ap is not null
314 and l_uom_code_po <> l_uom_code_ap THEN
315 -- Do the UOM conversion since AP UOM does not equal PO UOM
316 d_position := 40;
317 IF PO_LOG.d_stmt THEN
318 PO_LOG.stmt(d_mod,d_position,'Before call to UOM conversion');
319 END IF;
320
321 l_uom_rate := po_uom_s.po_uom_convert(
322 l_uom_code_ap, l_uom_code_po, l_item_id);
323
324 d_position := 45;
325 IF PO_LOG.d_stmt THEN
326 PO_LOG.stmt(d_mod,d_position,'l_uom_rate: ' || l_uom_rate);
327 END IF;
328
329 quantity_billed_tbl(i) :=
330 round(quantity_billed_ap_tbl(i) * l_uom_rate, 15);
331 quantity_financed_tbl(i) :=
332 round(quantity_financed_ap_tbl(i) * l_uom_rate, 15);
333 quantity_recouped_tbl(i) :=
334 round(quantity_recouped_ap_tbl(i) * l_uom_rate, 15);
335 ELSE
336 -- AP UOM equals PO UOM. No conversion necessary
337 d_position := 50;
338 IF PO_LOG.d_stmt THEN
342 quantity_billed_tbl(i) := quantity_billed_ap_tbl(i);
339 PO_LOG.stmt(d_mod,d_position,'AP UOM equals PO UOM');
340 END IF;
341
343 quantity_financed_tbl(i) := quantity_financed_ap_tbl(i);
344 quantity_recouped_tbl(i) := quantity_recouped_ap_tbl(i);
345
346 END IF; -- IF AP UOM <> PO UOM
347
348 END LOOP; -- Distribution Loop
349 --<bug#5339961 END>
350 d_position := 70;
351 IF PO_LOG.d_proc THEN
352 PO_LOG.proc_end(d_mod);
353 END IF;
354
355 END populate_calculated_fields;
356
357
358 -- Name: dump_to_log
359 -- Function: writes the contents of this object to the FND log.
360 -- Notes: This procedure is for debugging purposes only.
361 MEMBER PROCEDURE dump_to_log
362 IS
363 l_msg VARCHAR2(2000);
364 BEGIN
365 FOR i IN 1..po_distribution_id_tbl.COUNT LOOP
369 l_msg := substrb(l_msg || '; uom_code_po ' || uom_code_po_tbl(i) , 1, 2000);
366 l_msg := 'Distribution Row ' || i || ': ';
367 l_msg := substrb(l_msg || 'po_distribution_id ' || po_distribution_id_tbl(i) , 1, 2000);
368 l_msg := substrb(l_msg || '; uom_code_ap ' || uom_code_ap_tbl(i) , 1, 2000);
370 l_msg := substrb(l_msg || '; quantity_billed_ap ' || quantity_billed_ap_tbl(i) , 1, 2000);
371 l_msg := substrb(l_msg || '; quantity_billed ' || quantity_billed_tbl(i) , 1, 2000);
372 l_msg := substrb(l_msg || '; amount_billed ' || amount_billed_tbl(i) , 1, 2000);
373 l_msg := substrb(l_msg || '; quantity_financed_ap ' || quantity_financed_ap_tbl(i) , 1, 2000);
374 l_msg := substrb(l_msg || '; quantity_financed ' || quantity_financed_tbl(i) , 1, 2000);
375 l_msg := substrb(l_msg || '; amount_financed ' || amount_financed_tbl(i) , 1, 2000);
376 l_msg := substrb(l_msg || '; quantity_recouped_ap ' || quantity_recouped_ap_tbl(i) , 1, 2000);
377 l_msg := substrb(l_msg || '; quantity_recouped ' || quantity_recouped_tbl(i) , 1, 2000);
378 l_msg := substrb(l_msg || '; amount_recouped ' || amount_recouped_tbl(i) , 1, 2000);
379 l_msg := substrb(l_msg || '; retainage_withheld_amt ' || retainage_withheld_amt_tbl(i) , 1, 2000);
380 l_msg := substrb(l_msg || '; retainage_released_amt ' || retainage_released_amt_tbl(i) , 1, 2000);
381 l_msg := substrb(l_msg || '; last_update_login ' || last_update_login_tbl(i) , 1, 2000);
382 l_msg := substrb(l_msg || '; request_id ' || request_id_tbl(i) , 1, 2000);
383
384 FND_LOG.string( FND_LOG.LEVEL_STATEMENT,
385 'po.plsql.PO_AP_DIST_REC_TYPE.dump_to_log',
386 l_msg );
387 END LOOP;
388 END dump_to_log;
389
390 END;
[Home] [Help]