[Home] [Help]
PACKAGE BODY: APPS.FII_EA_AP_INV_DTL_PKG
Source
1 PACKAGE BODY FII_EA_AP_INV_DTL_PKG AS
2 /* $Header: FIIEAINVDETB.pls 120.1 2005/07/15 08:09:15 dhmehra noship $ */
3
4 PROCEDURE get_inv_detail
5 (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 ,p_invoice_detail_sql OUT NOCOPY VARCHAR2
8 ,p_invoice_detail_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 )IS
10
11
12 -- Local Variables Declaration
13
14 l_sqlstmt VARCHAR2(14000);
15 l_as_of_date DATE;
16 l_operating_unit VARCHAR2(240);
17 l_supplier VARCHAR2(240);
18 l_invoice_number NUMBER;
19 l_period_type VARCHAR2(240);
20 l_record_type_id NUMBER;
21 l_view_by VARCHAR2(240);
22 l_currency VARCHAR2(240);
23 l_column_name VARCHAR2(240);
24 l_table_name VARCHAR2(240);
25 l_gid NUMBER;
26 l_org_where VARCHAR2(240);
27 l_supplier_where VARCHAR2(240);
28 l_ea_supplier_where VARCHAR2(240);
29 l_invoice_id NUMBER;
30 l_yes VARCHAR2(240);
31 l_no VARCHAR2(240);
32 l_due_date_url VARCHAR2(1000);
33 l_tran_amt_url VARCHAR2(1000);
34 l_ever_on_hold_url VARCHAR2(1000);
35
36
37 BEGIN
38
39 FII_PMV_UTIL.get_parameters(
40 p_page_parameter_tbl
41 ,l_as_of_date
42 ,l_operating_unit
43 ,l_supplier
44 ,l_invoice_number
45 ,l_period_type
46 ,l_record_type_id
47 ,l_view_by
48 ,l_currency
49 ,l_column_name
50 ,l_table_name
51 ,l_gid
52 ,l_org_where
53 ,l_supplier_where
54 );
55
56 -- Get Invoice ID from UTIL package
57 FII_PMV_UTIL.get_invoice_id(p_page_parameter_tbl, l_invoice_id);
58
59
60 -- Get Translated Messages for -- YES , NO
61 FII_PMV_UTIL.get_yes_no_msg(l_yes, l_no);
62
63 -- UTIL package returns, l_supplier_where = f.supplier_id
64 -- However, Oracle Payables table,ap_invoices_all has column name, vendor_id instead.
65
66 l_ea_supplier_where := REPLACE(l_supplier_where,'supplier_id','vendor_id');
67
68 -- Deciding URL's based on the drill column:
69
70 l_due_date_url := 'pFunctionName=FII_AP_SCHED_PAY_DISCOUNT&FII_AP_INVOICE_ID=FII_INVOICE_ID&FII_CURRENCIES=FII_EA_TRAN_CURRENCY&pParamIds=Y';
71 l_tran_amt_url := 'pFunctionName=FII_AP_INV_DIST_DETAIL&FII_AP_INVOICE_ID=FII_INVOICE_ID&FII_CURRENCIES=FII_EA_TRAN_CURRENCY&pParamIds=Y';
72 l_ever_on_hold_url := 'pFunctionName=FII_AP_HOLD_HISTORY&FII_AP_INVOICE_ID=FII_INVOICE_ID&pParamIds=Y';
73
74
75 -- PMV SQL formation
76
77 l_sqlstmt := '
78
79 SELECT TRUNC(f.invoice_date) FII_EA_INV_DATE
80 ,TRUNC(f.creation_date) FII_EA_ENTERED_DATE
81 ,TRUNC(MIN(pay.due_date)) FII_EA_DUE_DATE
82 ,f.invoice_currency_code FII_EA_TRAN_CURRENCY
83 ,f.invoice_amount FII_EA_TRAN_AMT
84 ,CASE WHEN COUNT(hold.hold_date) > 0
85 THEN '''||l_yes||'''
86 ELSE '''||l_no||'''
87 END FII_EA_EVER_ON_HOLD
88 ,COUNT(DISTINCT aid.invoice_distribution_id) FII_EA_DIST_LINES
89 ,tl.name FII_EA_TERMS
90 ,f.source FII_EA_SOURCE
91 ,'''||l_due_date_url||''' FII_EA_DUE_DATE_DRILL
92 ,'''||l_tran_amt_url||''' FII_EA_TRAN_AMT_DRILL
93 ,'''||l_ever_on_hold_url||''' FII_EA_EVER_ON_HOLD_DRILL
94 FROM ap_invoices_all f
95 ,ap_payment_schedules_all pay
96 ,ap_invoice_distributions_all aid
97 ,ap_terms_tl tl
98 ,ap_holds_all hold
99 WHERE f.invoice_id = :INVOICE_ID
100 '||l_org_where||' '||l_ea_supplier_where||'
101 AND f.invoice_id = pay.invoice_id (+)
102 AND f.invoice_id = hold.invoice_id (+)
103 AND f.cancelled_date IS NULL
104 AND f.invoice_id = aid.invoice_id (+)
105 -- Bug # 4491000 , Commented out following filter
106 -- AND f.invoice_type_lookup_code NOT IN (''EXPENSE REPORT'')
107 AND f.invoice_amount <> 0
108 AND f.terms_id = tl.term_id
109 AND tl.language = USERENV(''LANG'')
110 GROUP BY f.invoice_date
111 ,f.creation_date
112 ,f.invoice_currency_code
113 ,f.invoice_amount
114 ,tl.name
115 ,f.source
116 ';
117
118
119 -- Binding Section
120
121 FII_PMV_UTIL.bind_variable(
122 p_sqlstmt=> l_sqlstmt
123 ,p_page_parameter_tbl=> p_page_parameter_tbl
124 ,p_sql_output=> p_invoice_detail_sql
125 ,p_bind_output_table=> p_invoice_detail_output
126 ,p_invoice_number=> l_invoice_id
127 );
128 END;
129
130 END FII_EA_AP_INV_DTL_PKG;