[Home] [Help]
PACKAGE BODY: APPS.FII_EA_AP_TRAN_PKG
Source
1 PACKAGE BODY FII_EA_AP_TRAN_PKG AS
2 /* $Header: FIIEAAPTB.pls 120.6 2006/06/16 19:38:13 shanley noship $ */
3
4
5 PROCEDURE GET_AP_TRAN
6 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
7 ap_tran_sql out NOCOPY VARCHAR2,
8 ap_tran_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
9
10 -- declaration section
11 sqlstmt VARCHAR2(15000);
12 l_as_of_date DATE;
13 l_page_period_type VARCHAR2(100);
14 l_ledger_id VARCHAR2(30);
15 l_fud1_where VARCHAR2(200);
16 l_fud2_where VARCHAR2(200);
17
18 l_msg1 VARCHAR2(100);
19 -- Commented following variable as part of enhancement 4456983
20 -- l_pay_op_imp_flag VARCHAR2(1);
21 l_sysdate VARCHAR2(30);
22 BEGIN
23
24
25 fii_ea_util_pkg.reset_globals;
26 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
27 l_as_of_date := fii_ea_util_pkg.g_as_of_date;
28 l_page_period_type := fii_ea_util_pkg.g_page_period_type;
29 l_ledger_id := fii_ea_util_pkg.g_ledger_id;
30 l_fud1_where := replace(fii_ea_util_pkg.get_fud1_for_detail, 'fud1_id', 'user_dim1_id');
31 l_fud2_where := replace(fii_ea_util_pkg.get_fud2_for_detail, 'fud2_id', 'user_dim2_id');
32
33 -- Enhancement 4456983.
34 -- Commented following code line to make FII_EA_INV_DTL_DRILL, independent of DBI-AP implementation.
35 -- l_pay_op_imp_flag := NVL(FND_PROFILE.value('FII_AP_DBI_IMP'), 'N');
36
37 /*Get the Chart of Accounts ID to join to FII_GL_CCID_DIMENSIONS in PMV sql.
38 Get Period Set Name & Accounted Period Type to use index on Period Set Name in GL_JE_LINES*/
39 SELECT Chart_Of_Accounts_ID, Period_Set_Name, Accounted_Period_Type
40 INTO fii_ea_util_pkg.g_coaid, fii_ea_util_pkg.g_period_set_name, fii_ea_util_pkg.g_accounted_period_type
41 FROM GL_Sets_Of_Books
42 WHERE Set_Of_Books_ID = l_ledger_id;
43
44 l_msg1 := fnd_message.get_string('FII', 'FII_EA_MULTIPLE');
45
46 /*This report drills from gl so only pick up payables lines picked up in gl base mvs.
47 To link ap to gl:
48 (1) find subset of CCID dimensions in FII_GL_CCID_DIMENSIONS,
49 (2) pick up gl lines with one of the CCID dimensions in GL_JE_LINES,
50 (3) pick up only processed headers in FII_GL_PROCESSED_HEADER_IDS,
51 (4) pick up only payables headers in GL_JE_HEADERS (filter on GL effective date, not AP account date),
52 (5) link GL lines to AP lines in AP_AE_LINES_ALL,
53 (6) link AP lines to FII_AP_INV_B.
54 */
55
56
57 -- To pass SYSDATE in Drill URL - FII_EA_INV_DTL_DRILL,
58 -- we need to get SYSDATE into local variable as a string
59
60 SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY')
61 INTO l_sysdate
62 FROM dual;
63
64 sqlstmt := '
65 SELECT FII_EA_INV_NUM FII_EA_INV_NUM,
66 FII_EA_TRAN_CURRENCY FII_EA_TRAN_CURRENCY,
67 FII_EA_TRAN_AMT FII_EA_TRAN_AMT,
68 FII_EA_FUNCTIONAL_AMOUNT FII_EA_FUNCTIONAL_AMOUNT,
69 FII_EA_INV_DATE FII_EA_INV_DATE,
70 FII_EA_INV_TYPE FII_EA_INV_TYPE,
71 FII_EA_EXP_REPORT_NUM FII_EA_EXP_REPORT_NUM,
72 FII_EA_PO_NUM FII_EA_PO_NUM,
73 FII_EA_INV_DTL_DRILL FII_EA_INV_DTL_DRILL,
74 FII_EA_EXP_REPORT_NUM_DRILL FII_EA_EXP_REPORT_NUM_DRILL,
75 FII_EA_PO_NUM_DRILL FII_EA_PO_NUM_DRILL,
76 FII_EA_GT_FUNC_AMT FII_EA_GT_FUNC_AMT
77 FROM(
78 SELECT F.Invoice_Num FII_EA_INV_NUM,
79 F.Trans_Currency_Code FII_EA_TRAN_CURRENCY,
80 SUM(F.Amount_T) FII_EA_TRAN_AMT,
81 SUM(F.Amount_B) FII_EA_FUNCTIONAL_AMOUNT,
82 F.Invoice_Date FII_EA_INV_DATE,
83 F.Invoice_Type FII_EA_INV_TYPE,
84 DECODE(F.Exp_Report_Header_ID, NULL, NULL, -1, ''' || l_msg1 || ''' ,F.Invoice_Num) FII_EA_EXP_REPORT_NUM,
85 DECODE(MIN(F.PO_Distribution_ID), NULL, NULL,
86 DECODE(COUNT(DISTINCT F.PO_Header_ID || ''.'' || F.PO_Release_ID),
87 0, NULL, 1, MIN(F.PO_Num), ''' || l_msg1 || ''')) FII_EA_PO_NUM,
88 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_EA_AP_INV_DTL&FII_INVOICE='' || F.Invoice_Num || ''&FII_INVOICE_ID='' || F.Invoice_ID || ''&FII_OPERATING_UNITS='' || F.Org_ID || ''&POA_SUPPLIERS='' || F.Supplier_ID || ''&pParamIds=Y''
89 FII_EA_INV_DTL_DRILL,
90 DECODE(F.Exp_Report_Header_ID, NULL, NULL, -1, NULL,
91 ''pFunctionName=FII_IEXPENSES_DRILL&dbiReportHeaderId='' || F.Exp_Report_Header_ID ||
92 ''&dbiInvoiceId='' || F.Invoice_ID) FII_EA_EXP_REPORT_NUM_DRILL,
93 DECODE(MIN(F.PO_Distribution_ID), NULL, NULL,
94 DECODE(COUNT(DISTINCT F.PO_Header_ID || ''.'' || F.PO_Release_ID), 0, NULL, 1,
95 ''pFunctionName=FII_EA_POA_DRILL&PoHeaderId='' || MIN(F.PO_Header_ID) ||
96 ''&PoReleaseId='' || MIN(F.PO_Release_ID) || ''&addBreadCrumb=Y&retainAM=Y'',
97 NULL)) FII_EA_PO_NUM_DRILL,
98 SUM(SUM(F.Amount_B)) OVER () FII_EA_GT_FUNC_AMT,
99 (rank() over (ORDER BY SUM(F.Amount_B) DESC, F.Invoice_Num ASC, F.Invoice_ID ASC)) - 1 rnk
100 FROM FII_AP_INV_B F,
101 GL_Import_References GIR,
102 FII_GL_Processed_Header_IDS PH,
103 GL_JE_Lines JL
104 WHERE F.GL_SL_Link_ID = GIR.GL_SL_Link_ID
105 AND F.GL_SL_Link_Table = GIR.GL_SL_LINK_TABLE
106 AND GIR.JE_Header_ID = PH.JE_Header_ID
107 AND PH.JE_Header_ID = JL.JE_Header_ID
108 AND GIR.JE_Line_Num = JL.JE_Line_Num
109 AND GIR.gl_sl_link_table IN (''XLAJEL'', ''APECL'')
110 AND F.Company_ID = &FII_COMPANIES+FII_COMPANIES
111 AND F.Cost_Center_ID = &ORGANIZATION+HRI_CL_ORGCC
112 AND F.Fin_Category_ID = &FINANCIAL ITEM+GL_FII_FIN_ITEM
113 AND F.Ledger_ID = &FII_LEDGER+FII_LEDGER
114 ' || l_fud1_where || '
115 ' || l_fud2_where || '
116 AND JL.Effective_Date BETWEEN :CURR_PERIOD_START AND :ASOF_DATE
117 GROUP BY F.Invoice_Num,
118 F.Invoice_ID,
119 F.Supplier_ID,
120 F.Org_ID,
121 F.Trans_Currency_Code,
122 F.Invoice_Date,
123 F.Invoice_Type,
124 F.Exp_Report_Header_ID
125 )
126 WHERE (RNK between &START_INDEX and &END_INDEX or &END_INDEX = -1)
127 ORDER BY RNK';
128
129
130 FII_EA_UTIL_PKG.bind_variable(
131 p_sqlstmt => sqlstmt,
132 p_page_parameter_tbl => p_page_parameter_tbl,
133 p_sql_output => ap_tran_sql,
134 p_bind_output_table => ap_tran_output);
135
136
137 END GET_AP_TRAN;
138
139
140 END FII_EA_AP_TRAN_PKG;