[Home] [Help]
PACKAGE BODY: APPS.FII_AP_DETAIL
Source
1 PACKAGE BODY FII_AP_DETAIL AS
2 /* $Header: FIIAPDEB.pls 120.7 2006/10/13 21:24:51 vkazhipu ship $ */
3 /* This is a public function to get account description */
4 FUNCTION Get_Account_Desc(p_chart_of_accounts_id IN NUMBER, p_dist_code_combination_id IN NUMBER)
5 return Varchar2 IS
6 l_account_description Varchar2(1000);
7 BEGIN
8 IF (FND_FLEX_KEYVAL.validate_ccid
9 ('SQLGL', 'GL#', p_CHART_OF_ACCOUNTS_ID, p_DIST_CODE_COMBINATION_ID,
10 'ALL', NULL, NULL, 'IGNORE', NULL, NULL, NULL, NULL)) THEN
11 l_account_description := FND_FLEX_KEYVAL.concatenated_descriptions;
12 END IF;
13 return l_account_description;
14 END;
15
16 PROCEDURE Get_Inv_Distribution_Detail (
17 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
18 inv_dist_sql OUT NOCOPY VARCHAR2,
19 inv_dist_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
20 ) IS
21 l_as_of_date DATE;
22 l_operating_unit VARCHAR2(240);
23 l_supplier VARCHAR2(240);
24 l_invoice_number NUMBER;
25 l_period_type VARCHAR2(240);
26 l_record_type_id NUMBER;
27 l_view_by VARCHAR2(240);
28 l_currency VARCHAR2(240);
29 l_column_name VARCHAR2(240);
30 l_table_name VARCHAR2(240);
31 l_gid NUMBER;
32 l_org_where VARCHAR2(240);
33 l_supplier_where VARCHAR2(240);
34 l_invoice_id NUMBER;
35 l_line_number NUMBER := -9999;
36
37 sqlstmt VARCHAR2(14000);
38 l_line_stmt VARCHAR2(1000);
39
40 BEGIN
41 fii_pmv_util.get_parameters(
42 p_page_parameter_tbl,
43 l_as_of_date,
44 l_operating_unit,
45 l_supplier,
46 l_invoice_number,
47 l_period_type,
48 l_record_type_id,
49 l_view_by,
50 l_currency,
51 l_column_name,
52 l_table_name,
53 l_gid,
54 l_org_where,
55 l_supplier_where
56 );
57
58 --Added code for R12 enhancement for getting value for line number
59 IF (p_page_parameter_tbl.count > 0) THEN
60 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
61 IF p_page_parameter_tbl(i).parameter_name = 'FII_LINE_NUM' THEN
62 l_line_number := p_page_parameter_tbl(i).parameter_value;
63
64 END IF;
65 END LOOP;
66 END IF;
67 --added by vkazhipu for bug 5581666 to handle the flow from EA reports
68 if (l_line_number = -9999) THEN
69 l_line_stmt := '';
70 else
71 l_line_stmt := ' AND aid.invoice_line_number = :LINE_NUMBER';
72 end if;
73
74 fii_pmv_util.get_invoice_id(p_page_parameter_tbl, l_invoice_id);
75
76 sqlstmt := '
77 SELECT aid.distribution_line_number FII_AP_DIST_NUM,
78 alc.displayed_field FII_AP_DIST_TYPE,
79 aid.amount FII_AP_DIST_AMOUNT,
80 apps.fnd_flex_ext.get_segs(''SQLGL'',''GL#'',glcc.chart_of_accounts_id,aid.dist_code_combination_id) FII_AP_ACCOUNT,
81 fii_ap_detail.get_account_desc(glcc.chart_of_accounts_id,aid.dist_code_combination_id) FII_AP_ACCOUNT_DESC,
82 sum (aid.amount) over() FII_AP_DIST_AMOUNT_GT
83 FROM ap_invoice_distributions_all aid,
84 ap_lookup_codes alc,
85 gl_code_combinations glcc
86 WHERE aid.line_type_lookup_code = alc.lookup_code
87 AND alc.lookup_type = ''INVOICE DISTRIBUTION TYPE''
88 AND aid.dist_code_combination_id = glcc.code_combination_id
89 AND aid.invoice_id = :INVOICE_ID'||l_line_stmt||'
90 &ORDER_BY_CLAUSE
91 ';
92
93 -- R12 Added bind varaiable parameter p_line_number
94 fii_pmv_util.bind_variable(
95 p_sqlstmt => sqlstmt,
96 p_page_parameter_tbl => p_page_parameter_tbl,
97 p_sql_output => inv_dist_sql,
98 p_bind_output_table => inv_dist_output,
99 p_invoice_number => l_invoice_id,
100 p_line_number => l_line_number
101 );
102 END get_inv_distribution_detail;
103
104 PROCEDURE Get_Hold_History
105 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
106 hold_history_sql out NOCOPY VARCHAR2,
107 hold_history_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
108 /* declaration section */
109 sqlstmt VARCHAR2(14000);
110
111 l_as_of_date DATE;
112 l_operating_unit VARCHAR2(240);
113 l_supplier VARCHAR2(240);
114 l_invoice_number NUMBER;
115 l_period_type VARCHAR2(240);
116 l_record_type_id NUMBER;
117 l_view_by VARCHAR2(240);
118 l_currency VARCHAR2(240);
119 l_column_name VARCHAR2(240);
120 l_table_name VARCHAR2(240);
121 l_gid NUMBER;
122 l_org_where VARCHAR2(240);
123 l_supplier_where VARCHAR2(240);
124
125 l_invoice_id NUMBER;
126
127 BEGIN
128
129 FII_PMV_Util.Get_Parameters(
130 p_page_parameter_tbl,
131 l_as_of_date,
132 l_operating_unit,
133 l_supplier,
134 l_invoice_number,
135 l_period_type,
136 l_record_type_id,
137 l_view_by,
138 l_currency,
139 l_column_name,
140 l_table_name,
141 l_gid,
142 l_org_where,
143 l_supplier_where
144 );
145
146 FII_PMV_Util.get_invoice_id(p_page_parameter_tbl, l_invoice_id);
147
148 /**************Description of Measures, Attributes returned to PMV *****
149 FII_MEASURE1 - Hold Name
150 FII_MEASURE2 - Hold Date
151 FII_MEASURE3 - Held By
152 FII_MEASURE4 - Hold Release Date
153 ***********************************************************************/
154
155 /* Main SQL section */
156
157 -- PMV SQL modified as part of Enhancement 4234120 to pick data from Oracle Payables Tables.
158 -- Prior to this, data was picked from MV, FII_AP_INV_HOLDS_B
159
160 sqlstmt := '
161 SELECT hold.hold_lookup_code FII_MEASURE1
162 ,TRUNC(hold.hold_date) FII_MEASURE2
163 ,DECODE(hold.release_lookup_code, NULL, NULL,hold.last_update_date) FII_MEASURE4
164 ,fnd_usr.user_name FII_MEASURE3
165 FROM ap_invoices_all inv
166 ,ap_holds_all hold
167 ,fnd_user_view fnd_usr
168 WHERE inv.invoice_id = hold.invoice_id
169 AND hold.invoice_id = :INVOICE_ID
170 AND hold.held_by = fnd_usr.user_id
171 AND inv.cancelled_date IS NULL
172 AND inv.invoice_type_lookup_code NOT IN (''PREPAYMENT'')
173 &ORDER_BY_CLAUSE
174 ';
175
176
177 /* Binding Section */
178 FII_PMV_Util.bind_variable(
179 p_sqlstmt=>sqlstmt,
180 p_page_parameter_tbl=>p_page_parameter_tbl,
181 p_sql_output=>hold_history_sql,
182 p_bind_output_table=>hold_history_output,
183 p_invoice_number=>l_invoice_id
184 );
185 END;
186
187 PROCEDURE Get_Inv_Activity_History
188 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
189 inv_act_sql out NOCOPY VARCHAR2,
190 inv_act_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
191 /* declaration section */
192 sqlstmt VARCHAR2(14000);
193
194 l_as_of_date DATE;
195 l_operating_unit VARCHAR2(240);
196 l_supplier VARCHAR2(240);
197 l_invoice_number NUMBER;
198 l_period_type VARCHAR2(240);
199 l_record_type_id NUMBER;
200 l_view_by VARCHAR2(240);
201 l_currency VARCHAR2(240);
202 l_column_name VARCHAR2(240);
203 l_table_name VARCHAR2(240);
204 l_gid NUMBER;
205 l_org_where VARCHAR2(240);
206 l_supplier_where VARCHAR2(240);
207 l_invoice_id NUMBER;
208
209 stmt1 VARCHAR2(240);
210 stmt2 VARCHAR2(240);
211 stmt3 VARCHAR2(240);
212 stmt4 VARCHAR2(240);
213 stmt5 VARCHAR2(240);
214 stmt6 VARCHAR2(240);
215 stmt7 VARCHAR2(240);
216 stmt8 VARCHAR2(240);
217 stmt9 VARCHAR2(240);
218
219 l_date_mask VARCHAR2(240);
220 BEGIN
221
222 FII_PMV_Util.Get_Parameters(
223 p_page_parameter_tbl,
224 l_as_of_date,
225 l_operating_unit,
226 l_supplier,
227 l_invoice_number,
228 l_period_type,
229 l_record_type_id,
230 l_view_by,
231 l_currency,
232 l_column_name,
233 l_table_name,
234 l_gid,
235 l_org_where,
236 l_supplier_where
237 );
238
239 FII_PMV_Util.get_invoice_id(p_page_parameter_tbl, l_invoice_id);
240
241 /**************Description of Measures, Attributes returned to PMV *****
242 FII_MEASURE1 - Action
243 FII_MEASURE2 - Date,
244 FII_MEASURE3 - User
245 ***********************************************************************/
246
247
248
249 /**********Message to be displayed ************************************/
250 stmt1 := FND_MESSAGE.get_string('FII', 'FII_AP_ENTRY');
251 stmt2 := FND_MESSAGE.get_string('FII', 'FII_AP_HOLD_PLACED');
252 stmt3 := FND_MESSAGE.get_string('FII', 'FII_AP_HOLD_RELEASED');
253 stmt4 := FND_MESSAGE.get_string('FII', 'FII_AP_PREPAY_APPLIED');
254 stmt5 := FND_MESSAGE.get_string('FII', 'FII_AP_PREPAY_UNAPPLIED');
255 stmt6 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMENT');
256 stmt7 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMT_VOID');
257 stmt8 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMT_STOP');
258 stmt9 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMT_RELEASE');
259
260 /*get date mask */
261 FII_PMV_Util.get_format_mask(l_date_mask);
262
263 /* Main SQL section */
264 sqlstmt := 'select action FII_MEASURE1,
265 action_date FII_MEASURE2,
266 usr.user_name FII_MEASURE3
267 from
268 (select :ENTRY action,
269 entered_date action_date,
270 created_by by_whom
271 from fii_ap_invoice_b
272 where invoice_id=:INVOICE_ID
273 union all
274 select :HOLD_PLACED action,
275 hold_date action_date,
276 held_by by_whom
277 from fii_ap_inv_holds_b
278 where invoice_id=:INVOICE_ID
279 and period_type_id = 1
280 union all
281 select :HOLD_RELEASED action,
282 release_date action_date,
283 released_by by_whom
284 from fii_ap_inv_holds_b
285 where invoice_id=:INVOICE_ID
286 and period_type_id = 1
287 union all
288 SELECT CASE WHEN b.amount < 0 THEN
289 :PREPAY_APPLIED
290 ELSE
291 :PREPAY_UNAPPLIED
292 END action,
293 trunc(b.creation_date) action_date,
294 a.last_updated_by by_whom
295 from ap_invoice_distributions_all a, ap_invoice_distributions_all b
296 where a.invoice_id=:INVOICE_ID
297 and a.invoice_distribution_id = b.prepay_distribution_id
298 and b.line_type_lookup_code = ''PREPAY''
299 and b.amount <> 0
300 and a.invoice_id <> b.invoice_id
301 union all
302 select :PAYMT action,
303 action_date action_date,
304 created_by by_whom
305 from fii_ap_pay_sched_b
306 where invoice_id=:INVOICE_ID
307 and action in (''PAYMENT'', ''PREPAYMENT'')
308 and period_type_id = 1
309 union all
310 select CASE WHEN c.stopped_date is not null
311 THEN :PAYMT_STOP
312 ELSE :PAYMT_RELEASE END action,
313 CASE WHEN c.stopped_date is not null
314 THEN c.stopped_date
315 ELSE c.released_date END action_date,
316 CASE WHEN c.stopped_date is not null
317 THEN c.stopped_by
318 ELSE c.released_by END by_whom
319 from ap_checks_all c, ap_invoice_payments_all p
320 where c.check_id = p.check_id
321 and p.invoice_id = :INVOICE_ID
322 and (c.stopped_date is not null
323 OR c.released_date is not null)
324 ) a,
325 fnd_user usr
326 Where a.by_whom = usr.user_id
327 &ORDER_BY_CLAUSE ';
328
329 stmt1 := FND_MESSAGE.get_string('FII', 'FII_AP_ENTRY');
330 stmt2 := FND_MESSAGE.get_string('FII', 'FII_AP_HOLD_PLACED');
331 stmt3 := FND_MESSAGE.get_string('FII', 'FII_AP_HOLD_RELEASED');
332 stmt4 := FND_MESSAGE.get_string('FII', 'FII_AP_PREPAY_APPLIED');
333 stmt5 := FND_MESSAGE.get_string('FII', 'FII_AP_PREPAY_UNAPPLIED');
334 stmt6 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMENT');
335 stmt7 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMT_VOID');
336 stmt8 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMT_STOP');
337 stmt9 := FND_MESSAGE.get_string('FII', 'FII_AP_PAYMT_RELEASE');
338
339 /* Binding Section */
340 FII_PMV_Util.bind_variable(
341 p_sqlstmt=>sqlstmt,
342 p_page_parameter_tbl=>p_page_parameter_tbl,
343 p_sql_output=>inv_act_sql,
344 p_bind_output_table=>inv_act_output,
345 p_invoice_number=>l_invoice_id,
346
347 p_entry=>stmt1,
348 p_hold_placed=>stmt2,
349 p_hold_released=>stmt3,
350 p_prepay_applied=>stmt4,
351 p_prepay_unapplied=>stmt5,
352 p_payment=>stmt6,
353 p_paymt_void=>stmt7,
354 p_paymt_stop=>stmt8,
355 p_paymt_release=>stmt9
356
357 );
358 END;
359
360 PROCEDURE Get_Sched_Pay_Discount
361 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
362 sched_pay_sql out NOCOPY VARCHAR2,
363 sched_pay_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
364 /* declaration section */
365 sqlstmt VARCHAR2(14000);
366
367 l_as_of_date DATE;
368 l_operating_unit VARCHAR2(240);
369 l_supplier VARCHAR2(240);
370 l_invoice_number NUMBER;
371 l_period_type VARCHAR2(240);
372 l_record_type_id NUMBER;
373 l_view_by VARCHAR2(240);
374 l_currency VARCHAR2(240);
375 l_column_name VARCHAR2(240);
376 l_table_name VARCHAR2(240);
377 l_gid NUMBER;
378 l_org_where VARCHAR2(240);
379 l_supplier_where VARCHAR2(240);
380
381 l_invoice_id NUMBER;
382
383 l_date_mask VARCHAR2(240);
384 l_yes VARCHAR2(240);
385 l_no VARCHAR2(240);
386 BEGIN
387
388 FII_PMV_Util.Get_Parameters(
389 p_page_parameter_tbl,
390 l_as_of_date,
391 l_operating_unit,
392 l_supplier,
393 l_invoice_number,
394 l_period_type,
395 l_record_type_id,
396 l_view_by,
397 l_currency,
398 l_column_name,
399 l_table_name,
400 l_gid,
401 l_org_where,
402 l_supplier_where
403 );
404
405 FII_PMV_Util.get_invoice_id(p_page_parameter_tbl, l_invoice_id);
406
407 /**************Description of Measures, Attributes returned to PMV *****
408 FII_MEASURE1 - Payment Number
409 FII_MEASURE2 - Due Date
410 FII_MEASURE3 - Amount
411 FII_MEASURE4 - Discount Date
412 FII_MEASURE5 - Discount Amount
413 FII_MEASURE6 - Second Discount Date
414 FII_MEASURE7 - Second Discount Amount
415 FII_MEASURE8 - Third Discount Date
416 FII_MEASURE9 - Third Discount Amount
417 FII_MEASURE10- Hold Flag
418 FII_MEASURE11 - Grand Total for Gross Amount
419 ***********************************************************************/
420
421 /*get date mask */
422 FII_PMV_Util.get_format_mask(l_date_mask);
423
424 /* get mls message for yes no */
425 FII_PMV_Util.get_yes_no_msg(l_yes, l_no);
426
427 /* Main SQL section */
428 sqlstmt := 'select a.Payment_Num FII_MEASURE1,
429 a.due_date FII_MEASURE2,
430 a.Gross_Amount FII_MEASURE3,
431 to_char(discount_date,'''||l_date_mask||''') FII_MEASURE4,
432 a.Discount_Amount_Available FII_MEASURE5,
433 to_char(second_discount_date,'''||l_date_mask||''') FII_MEASURE6,
434 a.Second_Disc_Amt_Available FII_MEASURE7,
435 to_char(third_discount_date, '''||l_date_mask||''') FII_MEASURE8,
436 a.Third_Disc_Amt_Available FII_MEASURE9,
437 decode(nvl(a.hold_flag, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE10,
438 sum(a.gross_amount) over() FII_MEASURE11
439 from AP_Payment_Schedules_All a
440 where a.invoice_id = :INVOICE_ID
441 &ORDER_BY_CLAUSE '
442 ;
443
444
445 /* Binding Section */
446 FII_PMV_Util.bind_variable(
447 p_sqlstmt=>sqlstmt,
448 p_page_parameter_tbl=>p_page_parameter_tbl,
449 p_sql_output=>sched_pay_sql,
450 p_bind_output_table=>sched_pay_output,
451 p_invoice_number=>l_invoice_id
452 );
453 END;
454
455 PROCEDURE get_inv_lines_detail (
456 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
457 p_inv_lines_detail_sql OUT NOCOPY VARCHAR2,
458 p_inv_lines_detail_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
459 ) IS
460 l_invoice_id NUMBER; -- Variable to retrieve Invoice ID from parameter passed from parent report
461 l_as_of_date DATE; -- Variables for get_parameters
462 l_operating_unit VARCHAR2(240);
463 l_supplier VARCHAR2(240);
464 l_invoice_number NUMBER;
465 l_period_type VARCHAR2(240);
466 l_record_type_id NUMBER;
467 l_view_by VARCHAR2(240);
468 l_currency VARCHAR2(240);
469 l_column_name VARCHAR2(240);
470 l_table_name VARCHAR2(240);
471 l_gid NUMBER;
472 l_org_where VARCHAR2(240);
473 l_supplier_where VARCHAR2(240);
474
475 sqlstmt VARCHAR2(14000);
476
477 l_url_line_amount VARCHAR2(1000); -- URL string to drill from line amount column
478 l_url_po_number VARCHAR2(1000); -- URL string to drill from po number column
479
480 BEGIN
481
482 -- To read parameters passed from the parent report
483 fii_pmv_util.get_parameters(
484 p_page_parameter_tbl,
485 l_as_of_date,
486 l_operating_unit,
487 l_supplier,
488 l_invoice_number,
489 l_period_type,
490 l_record_type_id,
491 l_view_by,
492 l_currency,
493 l_column_name,
494 l_table_name,
495 l_gid,
496 l_org_where,
497 l_supplier_where
498 );
499
500 -- To get the invoice id
501 fii_pmv_util.get_invoice_id(p_page_parameter_tbl, l_invoice_id);
502
503 l_url_line_amount := 'pFunctionName=FII_AP_INV_DIST_DETAIL&pParamIds=Y&FII_INVOICE_ID='|| l_invoice_id || '&FII_LINE_NUM=FII_AP_LINE_NUM';
504 l_url_po_number := 'pFunctionName=FII_EA_POA_DRILL&PoHeaderId='' || ail.po_header_id || ''&PoReleaseId='' || ail.po_release_id || ''&addBreadCrumb=Y&retainAM=Y';
505
506 -- PMV Query to retrieve the Invoice Lines Detail report
507 sqlstmt := '
508 SELECT ail.line_number FII_AP_LINE_NUM,
509 alc.displayed_field FII_AP_LINE_TYPE,
510 ail.amount FII_AP_LINE_AMOUNT,
511 ail.description FII_AP_LINE_DESC,
512 ail.quantity_invoiced FII_AP_QUANTITY,
513 muom.unit_of_measure_tl FII_AP_UOM, -- muom.uom_code
514 poh.segment1 FII_AP_PO_NUM, -- poh.segment1
515 poll.shipment_num FII_AP_PO_SHIPMENT_NUM,
516 por.release_num FII_AP_RELEASE_NUM,
517 rcvsh.receipt_num FII_AP_RECEIPT_NUM, -- receipt_num
518 sum (ail.amount) over() FII_AP_GT_LINE_AMOUNT,
519 -- Drill from Line Amount column to Invoice Distributions Detail report
520 ''' || l_url_line_amount || ''' FII_AP_LINE_AMOUNT_DRILL,
521 -- Drill from PO Number column to PO Overview report
522 ''' || l_url_po_number || ''' FII_AP_PO_NUM_DRILL
523 FROM ap_invoice_lines_all ail,
524 ap_lookup_codes alc,
525 mtl_units_of_measure muom,
526 po_headers_all poh,
527 po_line_locations_all poll,
528 po_releases_all por,
529 rcv_transactions rcvt,
530 rcv_shipment_headers rcvsh
531 WHERE ail.line_type_lookup_code = alc.lookup_code
532 AND alc.lookup_type = ''INVOICE LINE TYPE''
533 AND ail.unit_meas_lookup_code = muom.unit_of_measure(+)
534 AND ail.po_header_id = poh.po_header_id(+)
535 AND ail.po_line_location_id = poll.line_location_id(+)
536 AND ail.po_release_id = por.po_release_id(+)
537 AND ail.rcv_transaction_id = rcvt.transaction_id(+)
538 AND rcvt.shipment_header_id = rcvsh.shipment_header_id(+)
539 AND ail.invoice_id = :INVOICE_ID
540 ORDER BY ail.line_number';
541
542 fii_pmv_util.bind_variable(
543 p_sqlstmt => sqlstmt,
544 p_page_parameter_tbl => p_page_parameter_tbl,
545 p_sql_output => p_inv_lines_detail_sql,
546 p_bind_output_table => p_inv_lines_detail_output,
547 p_invoice_number => l_invoice_id
548 );
549 END get_inv_lines_detail;
550
551 END FII_AP_DETAIL;