eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIX_PMV_DBI_UTL_PKG dependencies on POA_DBI_UTIL_PKG

Line 20: , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map

16: ) RETURN VARCHAR2;
17:
18: PROCEDURE get_join_info (
19: p_view_by IN VARCHAR2
20: , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
21: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
22: , p_func_area IN VARCHAR2
23: , p_version IN VARCHAR2);
24:

Line 21: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl

17:
18: PROCEDURE get_join_info (
19: p_view_by IN VARCHAR2
20: , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
21: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
22: , p_func_area IN VARCHAR2
23: , p_version IN VARCHAR2);
24:
25: PROCEDURE init_dim_map (

Line 26: p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map

22: , p_func_area IN VARCHAR2
23: , p_version IN VARCHAR2);
24:
25: PROCEDURE init_dim_map (
26: p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
27: , p_mv_set IN VARCHAR2
28: , p_version IN VARCHAR2/*keep this for extensibility */);
29:
30:

Line 62: , p_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl

58: , p_version IN VARCHAR2
59: , p_mv_set IN VARCHAR2 --ITM/OTM/ECM
60: , p_where_clause OUT NOCOPY VARCHAR2
61: , p_mv OUT NOCOPY VARCHAR2
62: , p_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
63: , p_comp_type OUT NOCOPY VARCHAR2 --001
64: , p_xtd OUT NOCOPY VARCHAR2 --001
65: , p_view_by_select OUT NOCOPY VARCHAR2
66: , p_view_by OUT NOCOPY VARCHAR2

Line 71: l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;

67: )
68: IS
69:
70:
71: l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
72:
73: -- As of now, these are not passed as out params
74: l_dim_bmap NUMBER;
75: p_comparison_type VARCHAR2(1);

Line 101: poa_dbi_util_pkg.get_parameter_values (

97: init_dim_map (l_dim_map, p_mv_set,p_version);
98: p_mv := get_mv (p_mv_set,p_version);
99:
100:
101: poa_dbi_util_pkg.get_parameter_values (
102: p_param
103: , l_dim_map
104: , p_view_by
105: , p_comparison_type

Line 130: p_where_clause :=poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)

126: /* Get the filter where clause [value of the dimensions selected ] and
127: concatenate it with the where clause with row_type*/
128:
129: IF p_mv_set='SES' THEN
130: p_where_clause :=poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
131: ||get_agent_group_where_clause(p_param,p_mv_set)||' and application_id=696';
132: ELSIF p_func_area = 'IORRR' THEN
133: p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
134: ||get_dnis_where_clause(p_param)

Line 133: p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)

129: IF p_mv_set='SES' THEN
130: p_where_clause :=poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
131: ||get_agent_group_where_clause(p_param,p_mv_set)||' and application_id=696';
132: ELSIF p_func_area = 'IORRR' THEN
133: p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
134: ||get_dnis_where_clause(p_param)
135: ||get_agent_group_where_clause(p_param,p_mv_set)
136: ||get_row_type_where_clauses( p_func_area, p_version, p_mv_set)
137: ||get_outcome_filter_clause();

Line 139: p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)

135: ||get_agent_group_where_clause(p_param,p_mv_set)
136: ||get_row_type_where_clauses( p_func_area, p_version, p_mv_set)
137: ||get_outcome_filter_clause();
138: ELSE
139: p_where_clause := poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
140: ||get_dnis_where_clause(p_param)
141: ||get_agent_group_where_clause(p_param,p_mv_set)
142: ||get_row_type_where_clauses( p_func_area, p_version, p_mv_set);
143: END IF;

Line 343: p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map

339: init_dim_map: Initialize the dimension mapping for BIX.
340: The dimensions are populated depending on the mv set (ie) ECM,ITM,OTM
341: ----------------------------------------------------------------------------- */
342: PROCEDURE init_dim_map (
343: p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
344: , p_mv_set IN VARCHAR2
345: , p_version IN VARCHAR2/*keep this for extensibility */)
346: IS
347: l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;

Line 347: l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;

343: p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
344: , p_mv_set IN VARCHAR2
345: , p_version IN VARCHAR2/*keep this for extensibility */)
346: IS
347: l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
348: BEGIN
349: IF P_MV_SET='ECM' THEN
350: -- Email Account Dimension
351:

Line 581: , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map

577: ----------------------------------------------------------------------------- */
578:
579: PROCEDURE get_join_info (
580: p_view_by IN VARCHAR2
581: , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
582: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
583: , p_func_area IN VARCHAR2
584: , p_version IN VARCHAR2)
585: IS

Line 582: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl

578:
579: PROCEDURE get_join_info (
580: p_view_by IN VARCHAR2
581: , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
582: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
583: , p_func_area IN VARCHAR2
584: , p_version IN VARCHAR2)
585: IS
586: l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;

Line 586: l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;

582: , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
583: , p_func_area IN VARCHAR2
584: , p_version IN VARCHAR2)
585: IS
586: l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
587: l_view_by VARCHAR2(120);
588: BEGIN
589:
590:

Line 591: x_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();

587: l_view_by VARCHAR2(120);
588: BEGIN
589:
590:
591: x_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
592: /*
593: For some of ICI reports, there is no view by clause, but we do join to some other tables like hz_parties
594: to get customer names.
595: */

Line 686: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

682: ----------------------------------------------------------------------------- */
683: FUNCTION status_sql_daylevel (
684: p_fact_name IN VARCHAR2
685: , p_row_type_where_clause IN VARCHAR2
686: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
687: , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
688: , p_time_type IN VARCHAR2 default 'ESD'
689: , p_union IN VARCHAR2 default 'ALL'
690: )

Line 687: , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

683: FUNCTION status_sql_daylevel (
684: p_fact_name IN VARCHAR2
685: , p_row_type_where_clause IN VARCHAR2
686: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
687: , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
688: , p_time_type IN VARCHAR2 default 'ESD'
689: , p_union IN VARCHAR2 default 'ALL'
690: )
691: RETURN VARCHAR2

Line 759: IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)

755: -- Regular current column
756: -- Prev column (based on prior_code)
757:
758:
759: IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
760: THEN
761:
762: IF (p_col_name(i).column_name='NULL') THEN
763: l_col_names:=

Line 799: IF ( p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors

795: || '_total'
796: || fnd_global.newline;
797:
798: -- Sum of prev column (based on prior_code flagging)
799: IF ( p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors
800: OR p_col_name(i).prior_code = poa_dbi_util_pkg.prev_prev
801: OR p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )
802: THEN
803: l_col_names :=

Line 800: OR p_col_name(i).prior_code = poa_dbi_util_pkg.prev_prev

796: || fnd_global.newline;
797:
798: -- Sum of prev column (based on prior_code flagging)
799: IF ( p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors
800: OR p_col_name(i).prior_code = poa_dbi_util_pkg.prev_prev
801: OR p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )
802: THEN
803: l_col_names :=
804: l_col_names

Line 801: OR p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )

797:
798: -- Sum of prev column (based on prior_code flagging)
799: IF ( p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors
800: OR p_col_name(i).prior_code = poa_dbi_util_pkg.prev_prev
801: OR p_col_name(i).prior_code = poa_dbi_util_pkg.OPENING_PRIOR_CURR )
802: THEN
803: l_col_names :=
804: l_col_names
805: || ', sum(sum('

Line 1234: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl

1230: p_xtd IN VARCHAR2
1231: , p_comparison_type IN VARCHAR2
1232: , p_fact_name IN VARCHAR2
1233: , p_where_clause IN VARCHAR2
1234: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
1235: , p_use_grpid IN VARCHAR2 := 'Y'
1236: , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
1237: , p_fact_hint IN VARCHAR2 := null
1238: , p_union_clause IN VARCHAR2 DEFAULT NULL) --This parameter is specific to ICI

Line 1236: , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl

1232: , p_fact_name IN VARCHAR2
1233: , p_where_clause IN VARCHAR2
1234: , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
1235: , p_use_grpid IN VARCHAR2 := 'Y'
1236: , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
1237: , p_fact_hint IN VARCHAR2 := null
1238: , p_union_clause IN VARCHAR2 DEFAULT NULL) --This parameter is specific to ICI
1239: RETURN VARCHAR2
1240: IS

Line 1279: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap

1275: l_c_calc_end_date := g_c_period_end_date;
1276: l_p_calc_end_date := g_p_period_end_date;
1277: l_c_report_date_str := ' n.end_date ';
1278: l_p_report_date_str := ' n.end_date ';
1279: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1280: , g_inlist_xed);
1281: elsif p_col_name(i).to_date_type = 'RLX' then
1282: l_c_calc_end_date := g_c_as_of_date;
1283: l_p_calc_end_date := g_p_as_of_date;

Line 1286: l_inlist_bmap := poa_dbi_util_pkg.bitor( l_inlist_bmap

1282: l_c_calc_end_date := g_c_as_of_date;
1283: l_p_calc_end_date := g_p_as_of_date;
1284: l_c_report_date_str := ' n.end_date ';
1285: l_p_report_date_str := ' n.end_date ';
1286: l_inlist_bmap := poa_dbi_util_pkg.bitor( l_inlist_bmap
1287: , g_inlist_rlx);
1288: elsif p_col_name(i).to_date_type = 'BAL' then
1289: l_c_calc_end_date := g_c_as_of_date_balance;
1290: l_p_calc_end_date := g_p_as_of_date_balance;

Line 1293: l_inlist_bmap := poa_dbi_util_pkg.bitor( l_inlist_bmap

1289: l_c_calc_end_date := g_c_as_of_date_balance;
1290: l_p_calc_end_date := g_p_as_of_date_balance;
1291: l_c_report_date_str := ' n.end_date ';
1292: l_p_report_date_str := ' n.end_date ';
1293: l_inlist_bmap := poa_dbi_util_pkg.bitor( l_inlist_bmap
1294: , g_inlist_bal);
1295: l_balance_report := 'Y';
1296: ELSE -- XTD or YTD
1297: l_c_calc_end_date := g_c_as_of_date;

Line 1304: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap

1300: l_p_report_date_str := ' LEAST (n.end_date, &BIS_PREVIOUS_ASOF_DATE) ';
1301:
1302: IF p_col_name (i).to_date_type = 'XTD'
1303: THEN
1304: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1305: , g_inlist_xtd);
1306: ELSE -- YTD
1307: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1308: , g_inlist_ytd);

Line 1307: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap

1303: THEN
1304: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1305: , g_inlist_xtd);
1306: ELSE -- YTD
1307: l_inlist_bmap := poa_dbi_util_pkg.bitor (l_inlist_bmap
1308: , g_inlist_ytd);
1309: END IF;
1310: END IF;
1311:

Line 1318: poa_dbi_util_pkg.get_report_start_date(p_xtd)

1314: || ', sum(case when (n.start_date between '
1315: || case
1316: when p_col_name(i).to_date_type = 'RLX' or
1317: p_col_name(i).to_date_type = 'BAL' then
1318: poa_dbi_util_pkg.get_report_start_date(p_xtd)
1319: || ' and &BIS_CURRENT_EFFECTIVE_END_DATE and n.ordinal in (-1,2)'
1320: else
1321: '&BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE'
1322: end

Line 1335: IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)

1331: l_inner_col_names :=
1332: l_inner_col_names
1333: || ', sum(' || p_col_name(i).column_name || ') ' || p_col_name(i).column_alias;
1334:
1335: IF (p_col_name (i).prior_code <> poa_dbi_util_pkg.no_priors)
1336: THEN
1337: l_col_names :=
1338: l_col_names
1339: || ', lag(sum(case when (n.start_date between '

Line 1343: poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y')

1339: || ', lag(sum(case when (n.start_date between '
1340: || case
1341: when p_col_name(i).to_date_type = 'RLX' or
1342: p_col_name(i).to_date_type = 'BAL' then
1343: poa_dbi_util_pkg.get_report_start_date(p_xtd,'Y')
1344: || ' and &BIS_PREVIOUS_EFFECTIVE_END_DATE and n.ordinal in (-1,1)'
1345: else
1346: '&BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE'
1347: end

Line 1388: IF (p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors)

1384: || p_col_name (i).column_alias
1385: || '_total ';
1386:
1387: -- Grand total for previous columns
1388: IF (p_col_name (i).prior_code = poa_dbi_util_pkg.both_priors)
1389: THEN
1390: l_col_names :=
1391: l_col_names
1392: || ',

Line 1531: || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd)

1527: when p_xtd like 'RL%' then
1528: case
1529: when l_balance_report = 'N' then
1530: '( select /*+ NO_MERGE */ cal.ordinal,n.time_id,n.record_type_id,n.period_type_id,n.report_date,cal.start_date,cal.end_date'
1531: || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd)
1532: || ' cal, fii_time_structures n
1533: where '
1534: || l_time_clause
1535: || l_cal_clause

Line 1546: || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)

1542: and fact.period_type_id = n.period_type_id
1543: '
1544: else
1545: '( select /*+ NO_MERGE */ cal.ordinal,cal.start_date, cal.report_date'
1546: || ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)
1547: || ' cal where '
1548: || l_time_clause
1549: || l_cal_clause
1550: || ' ) n

Line 1557: || poa_dbi_util_pkg.get_calendar_table (p_xtd)

1553: end
1554: else -- non RL%
1555: ' (select /*+ NO_MERGE */ n.time_id,n.record_type_id, n.period_type_id,n.report_date,cal.start_date,cal.end_date
1556: from '
1557: || poa_dbi_util_pkg.get_calendar_table (p_xtd)
1558: || ' cal, fii_time_rpt_struct_v n
1559: where '
1560: || l_time_clause
1561: || l_cal_clause

Line 1578: || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)

1574: ' || p_where_clause || '
1575: group by '
1576: || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1577: || ' n.start_date, n.report_date) i, '
1578: || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)
1579: || ' n where i.start_date (+) = n.start_date '
1580: || l_outer_time_clause
1581: || case when p_xtd like 'RL%' then 'and i.ordinal(+) = n.ordinal ' end
1582: || ' group by '

Line 1587: || poa_dbi_util_pkg.get_calendar_table (p_xtd,'N','N')

1583: || case when p_xtd like 'RL%' then 'n.ordinal, ' end
1584: || 'n.start_date '
1585: || p_union_clause
1586: || ') iset, '
1587: || poa_dbi_util_pkg.get_calendar_table (p_xtd,'N','N')
1588: || ' cal '
1589: || '
1590: where cal.start_date between '
1591: || case

Line 1593: poa_dbi_util_pkg.get_report_start_date(p_xtd)

1589: || '
1590: where cal.start_date between '
1591: || case
1592: when p_xtd like 'RL%' then
1593: poa_dbi_util_pkg.get_report_start_date(p_xtd)
1594: else
1595: '&BIS_CURRENT_REPORT_START_DATE'
1596: end
1597: || ' and &BIS_CURRENT_ASOF_DATE

Line 1607: p_bix_col_tab IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_tbl,

1603:
1604: END trend_sql;
1605:
1606: FUNCTION get_continued_measures(
1607: p_bix_col_tab IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_tbl,
1608: p_where_clause IN OUT NOCOPY VARCHAR2,
1609: p_xtd IN VARCHAR2,
1610: p_comparison_type IN VARCHAR2,
1611: p_mv_set IN VARCHAR2

Line 1657: l_from_list := fnd_global.newline||' FROM '||get_mv(p_mv_set,'6.0.4') ||' fact,'||' fii_time_rpt_struct ' ||' cal ,'|| poa_dbi_util_pkg.get_calendar_table(p_xtd) ||' fii1 ';

1653: p_bix_col_tab.DELETE;
1654:
1655: --Form the FROM list and the where clause for reports that display session level values.
1656: IF p_mv_set = 'SES' THEN
1657: l_from_list := fnd_global.newline||' FROM '||get_mv(p_mv_set,'6.0.4') ||' fact,'||' fii_time_rpt_struct ' ||' cal ,'|| poa_dbi_util_pkg.get_calendar_table(p_xtd) ||' fii1 ';
1658: l_where_clause := fnd_global.newline
1659: ||'WHERE fact.time_id = cal.time_id '
1660: ||fnd_global.newline||'AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
1661: ||fnd_global.newline||'AND cal.period_type_id = fact.period_type_id '

Line 1673: l_from_list := fnd_global.newline||' FROM '||get_mv(p_mv_set,'6.0.4') ||' fact,'|| poa_dbi_util_pkg.get_calendar_table(p_xtd) ||' fii1';

1669: l_where_clause := l_where_clause || fnd_global.newline||' AND cal.report_date = least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) ';
1670: END IF;
1671:
1672: ELSE
1673: l_from_list := fnd_global.newline||' FROM '||get_mv(p_mv_set,'6.0.4') ||' fact,'|| poa_dbi_util_pkg.get_calendar_table(p_xtd) ||' fii1';
1674: l_where_clause := fnd_global.newline
1675: ||'WHERE fact.period_type_id = 1 '
1676: ||fnd_global.newline||'AND fact.time_id = TO_CHAR(fii1.start_date,''J'') '
1677: ||fnd_global.newline||'AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE '