[Home] [Help]
PACKAGE: APPS.POA_DBI_TEMPLATE_PKG
Source
1 PACKAGE poa_dbi_template_pkg
2 /* $Header: poadbitmpls.pls 120.2 2005/09/08 15:46:54 nnewadka noship $ */
3
4 AS
5
6 g_c_period_start_date CONSTANT VARCHAR2 (60) := '&BIS_CURRENT_EFFFECTIVE_START_DATE';
7 g_c_period_end_date CONSTANT VARCHAR2 (60) := '&BIS_CURRENT_EFFECTIVE_END_DATE';
8 g_p_period_start_date CONSTANT VARCHAR2 (60) := '&BIS_PREVIOUS_EFFECTIVE_START_DATE';
9 g_p_period_end_date CONSTANT VARCHAR2 (60) := '&BIS_PREVIOUS_EFFECTIVE_END_DATE';
10 g_c_as_of_date CONSTANT VARCHAR2 (60) := '&BIS_CURRENT_ASOF_DATE';
11 g_p_as_of_date CONSTANT VARCHAR2 (60) := '&BIS_PREVIOUS_ASOF_DATE';
12 g_pp_date CONSTANT VARCHAR2 (60) := '&PREV_PREV_DATE';
13 -- Two bitmap variables used for the inlist generation
14 g_inlist_xed CONSTANT NUMBER := 1; -- Bit 0
15 g_inlist_xtd CONSTANT NUMBER := 2; -- Bit 1
16 g_inlist_ytd CONSTANT NUMBER := 4; -- Bit 2
17
18 -- for balance
19 g_c_as_of_date_balance constant varchar2(60) := 'least(&BIS_CURRENT_EFFECTIVE_END_DATE,&LAST_COLLECTION)';
20 g_p_as_of_date_balance constant varchar2(60) := 'least(&BIS_PREVIOUS_EFFECTIVE_END_DATE,&LAST_COLLECTION)';
21 g_c_as_of_date_o_balance constant varchar2(70) := 'least((&BIS_CURRENT_EFFECTIVE_START_DATE -1),&LAST_COLLECTION)';
22 -- for rolling and balance
23 g_inlist_rlx constant number := 8; -- Bit 3
24 g_inlist_bal constant number := 16; -- Bit 4
25
26 FUNCTION status_sql (
27 p_fact_name IN VARCHAR2
28 , p_where_clause IN VARCHAR2
29 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
30 , p_use_windowing IN VARCHAR2
31 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
32 , p_use_grpid VARCHAR2 := 'Y' -- when using rollup, pass: 'R', grouping sets, pass: 'Y'
33 , p_paren_count IN NUMBER := 3
34 , p_filter_where IN VARCHAR2 := NULL
35 , p_generate_viewby IN VARCHAR2 := 'Y'
36 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL)
37 RETURN VARCHAR2;
38
39 FUNCTION union_all_status_sql(
40 p_mv IN POA_DBI_UTIL_PKG.poa_dbi_mv_tbl
41 , p_join_tables IN POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl
42 , p_use_windowing IN VARCHAR2
43 , p_paren_count IN NUMBER := 3
44 , p_filter_where IN VARCHAR2 := NULL
45 , p_generate_viewby IN VARCHAR2 := 'Y'
46 , p_diff_measures in varchar2 := 'Y') RETURN VARCHAR2;
47
48
49 PROCEDURE get_status_col_calc(
50 p_col_names IN poa_dbi_util_pkg.poa_dbi_col_tbl
51 , x_col_calc_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_calc_tbl
52 , x_inlist_bmap OUT NOCOPY NUMBER
53 , x_compute_prior OUT NOCOPY VARCHAR2
54 , x_compute_prev_prev OUT NOCOPY VARCHAR2
55 , x_compute_opening_bal OUT NOCOPY VARCHAR2);
56
57 PROCEDURE get_trend_col_clauses(
58 p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
59 , p_xtd IN VARCHAR2
60 , x_inlist_bmap OUT NOCOPY NUMBER
61 , x_col_names OUT NOCOPY VARCHAR2
62 , x_inner_col_names OUT NOCOPY VARCHAR2
63 , x_compute_opening_bal OUT NOCOPY VARCHAR2
64 , x_col_list OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_list);
65
66
67 FUNCTION trend_sql (
68 p_xtd IN VARCHAR2
69 , p_comparison_type IN VARCHAR2
70 , p_fact_name IN VARCHAR2
71 , p_where_clause IN VARCHAR2
72 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
73 , p_use_grpid VARCHAR2 := 'Y'
74 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL
75 , p_fact_hint IN VARCHAR2 :=null
76 , p_called_by_union IN VARCHAR2 := 'N' )
77 RETURN VARCHAR2;
78
79 FUNCTION union_all_trend_sql(
80 p_mv IN poa_dbi_util_pkg.poa_dbi_mv_tbl
81 , p_comparison_type IN VARCHAR2
82 , p_filter_where IN VARCHAR2 := NULL
83 , p_diff_measures in varchar2 := 'Y')
84 RETURN VARCHAR2;
85
86 FUNCTION dtl_status_sql (
87 p_fact_name IN VARCHAR2
88 , p_where_clause IN VARCHAR2
89 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
90 , p_use_windowing IN VARCHAR2
91 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
92 , p_use_grpid IN VARCHAR2 := 'Y'
93 , p_paren_count IN NUMBER := 3
94 , p_group_by IN VARCHAR2
95 , p_from_clause IN VARCHAR2)
96 RETURN VARCHAR2;
97
98 FUNCTION dtl_status_sql2 (
99 p_fact_name IN VARCHAR2
100 , p_where_clause IN VARCHAR2
101 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
102 , p_use_windowing IN VARCHAR2
103 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
104 , p_use_grpid IN VARCHAR2 := 'Y'
105 , p_filter_where IN VARCHAR2 := NULL
106 , p_paren_count IN NUMBER := 3
107 , p_group_by IN VARCHAR2
108 , p_from_clause IN VARCHAR2)
109 RETURN VARCHAR2;
110
111
112 FUNCTION get_viewby_rank_clause (
113 p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
114 , p_use_windowing IN VARCHAR2)
115 RETURN VARCHAR2;
116
117 END poa_dbi_template_pkg;