APPS.FII_AR_REC_ACTIVITY_PKG dependencies on FII_AR_UTIL_PKG

Line 45: fii_ar_util_pkg.reset_globals;

41: l_curr_month_end_drill varchar2(50);
42: l_gt_hint varchar2(500);
43: BEGIN
44: --Call to reset the parameter variables
45: fii_ar_util_pkg.reset_globals;
46:
47: --Call to get all the parameters in the report
48: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
49: l_gt_hint := ' leading(gt) cardinality(gt 1) ';

Line 48: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

44: --Call to reset the parameter variables
45: fii_ar_util_pkg.reset_globals;
46:
47: --Call to get all the parameters in the report
48: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
49: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
50:
51: --Frame the order by clause for the report sql
52: IF(instr(fii_ar_util_pkg.g_order_by,',') <> 0) THEN

Line 52: IF(instr(fii_ar_util_pkg.g_order_by,',') <> 0) THEN

48: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
49: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
50:
51: --Frame the order by clause for the report sql
52: IF(instr(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
53:
54: /*This means no particular sort column is selected in the report,
55: So sort on the default column FII_AR_TOTAL_REC_XTD in descending order
56: NVL is added to make sure the null values appear last*/

Line 60: ELSIF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN

56: NVL is added to make sure the null values appear last*/
57:
58: l_order_by := 'ORDER BY NVL(FII_AR_TOTAL_REC_XTD, -999999999) DESC';
59:
60: ELSIF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
61:
62: /*This means a particular sort column is clicked to have descending order
63: in which case we would want all the null values to appear last in the
64: report so add an NVL to that column*/

Line 66: l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));

62: /*This means a particular sort column is clicked to have descending order
63: in which case we would want all the null values to appear last in the
64: report so add an NVL to that column*/
65:
66: l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
67: l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
68:
69: ELSE
70:

Line 81: WHERE fii_ar_util_pkg.g_as_of_date between start_date and END_date;

77:
78: -- To get the month end date to be passed to Receipt Activity Detail report
79: SELECT nvl(end_date, sysdate) INTO l_curr_month_end
80: FROM fii_time_ent_period
81: WHERE fii_ar_util_pkg.g_as_of_date between start_date and END_date;
82:
83: l_curr_month_end_drill := to_char(l_curr_month_end,'DD/MM/YYYY');
84:
85: --This call will populate fii_ar_summary_gt table

Line 86: fii_ar_util_pkg.populate_summary_gt_tables;

82:
83: l_curr_month_end_drill := to_char(l_curr_month_end,'DD/MM/YYYY');
84:
85: --This call will populate fii_ar_summary_gt table
86: fii_ar_util_pkg.populate_summary_gt_tables;
87:
88: -- Assigning self drill for view-by Customer scenario to NULL
89:
90: l_customer_drill := '''''';

Line 100: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN

96:
97: l_app_rec_xtd_summary_drill := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
98:
99:
100: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
101: l_total_rec_xtd_summary_dr_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
102: l_app_rec_xtd_summary_dr_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
103: ELSE
104: l_total_rec_xtd_summary_dr_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';

Line 109: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

105: l_app_rec_xtd_summary_dr_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
106:
107: END IF;
108:
109: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
110:
111: l_total_rec_xtd_detail_drill := 'pFunctionName=FII_AR_RCT_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||
112: inline_view.party_id||''&BIS_PMV_DRILL_CODE_AS_OF_DATE='||l_curr_month_end_drill||'&pParamIds=Y';
113:

Line 126: IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN

122: END IF;
123:
124: --Setting up the where clauses based on the Parameter and viewby
125: --for Collector Dimension
126: IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
127: l_collector_where := 'AND f.collector_id = t.collector_id';
128: END IF;
129:
130: --Customer Dimension where clause

Line 131: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

127: l_collector_where := 'AND f.collector_id = t.collector_id';
128: END IF;
129:
130: --Customer Dimension where clause
131: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
132: l_party_where := ' AND f.party_id = t.party_id ';
133: END IF;
134:
135: --Industry Dimension where clause

Line 136: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR

132: l_party_where := ' AND f.party_id = t.party_id ';
133: END IF;
134:
135: --Industry Dimension where clause
136: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
137: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
138:
139: l_industry_where := ' AND f.class_code = t.class_code AND f.class_category = t.class_category';
140: END IF;

Line 137: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN

133: END IF;
134:
135: --Industry Dimension where clause
136: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
137: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
138:
139: l_industry_where := ' AND f.class_code = t.class_code AND f.class_category = t.class_category';
140: END IF;
141:

Line 143: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS') OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN

139: l_industry_where := ' AND f.class_code = t.class_code AND f.class_category = t.class_category';
140: END IF;
141:
142: -- Select, where, group by clauses based on viewby
143: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS') OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
144: l_total_rec_xtd_drill := ''''||l_total_rec_xtd_summary_dr_1||'''';
145:
146: l_total_applied_xtd_drill := ''''||l_app_rec_xtd_summary_dr_1||'''';
147:

Line 148: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN

144: l_total_rec_xtd_drill := ''''||l_total_rec_xtd_summary_dr_1||'''';
145:
146: l_total_applied_xtd_drill := ''''||l_app_rec_xtd_summary_dr_1||'''';
147:
148: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
149: l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
150: l_total_rec_xtd_drill := ''''||l_total_rec_xtd_detail_drill||'''';
151:
152: l_total_applied_xtd_drill := ''''||l_app_rec_xtd_detail_drill||'''';

Line 158: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

154: l_cust_acct_where := 'AND f.cust_account_id = t.cust_account_id';
155:
156: l_inner_cst_columns := l_inner_cst_columns || ' ,t.party_id';
157:
158: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
159: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
160: l_total_rec_xtd_drill := 'DECODE(is_self_flag, ''Y'',
161: '''||l_total_rec_xtd_detail_drill||''', DECODE(is_leaf_flag,''Y'',
162: '''||l_total_rec_xtd_detail_drill||''', '''||l_total_rec_xtd_summary_drill||'''))';

Line 159: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN

155:
156: l_inner_cst_columns := l_inner_cst_columns || ' ,t.party_id';
157:
158: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
159: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
160: l_total_rec_xtd_drill := 'DECODE(is_self_flag, ''Y'',
161: '''||l_total_rec_xtd_detail_drill||''', DECODE(is_leaf_flag,''Y'',
162: '''||l_total_rec_xtd_detail_drill||''', '''||l_total_rec_xtd_summary_drill||'''))';
163:

Line 226: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/

222: DECODE(FII_AR_APPLIED_REC_XTD,0,'''',DECODE(NVL(FII_AR_APPLIED_REC_XTD,-999999),-999999,'''',
223: '||l_total_applied_xtd_drill||')) FII_AR_APPLIED_REC_XTD_DRILL,
224: '||l_customer_drill||' FII_AR_CUST_SELF_DRILL
225: FROM (
226: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
227: t.viewby VIEWBY,
228: t.viewby_code viewby_id
229: '||l_inner_cst_columns||',
230: SUM(CASE WHEN (t.report_date = :ASOF_DATE) and

Line 245: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,

241: SUM(CASE WHEN (t.report_date = :PREVIOUS_ASOF_DATE) and
242: (f.header_filter_date <= :PREVIOUS_ASOF_DATE) and
243: (f.header_filter_date >= :PRIOR_PERIOD_START)
244: THEN total_receipt_amount ELSE NULL END) FII_AR_TOTAL_REC_PRIOR_XTD
245: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
246: ( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
247: FROM fii_time_structures cal,'||fii_ar_util_pkg.get_from_statement||'
248: gt WHERE report_date IN ( :ASOF_DATE, :PREVIOUS_ASOF_DATE)
249: AND (bitand(cal.record_type_id, :BITAND) = :BITAND)

Line 247: FROM fii_time_structures cal,'||fii_ar_util_pkg.get_from_statement||'

243: (f.header_filter_date >= :PRIOR_PERIOD_START)
244: THEN total_receipt_amount ELSE NULL END) FII_AR_TOTAL_REC_PRIOR_XTD
245: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
246: ( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
247: FROM fii_time_structures cal,'||fii_ar_util_pkg.get_from_statement||'
248: gt WHERE report_date IN ( :ASOF_DATE, :PREVIOUS_ASOF_DATE)
249: AND (bitand(cal.record_type_id, :BITAND) = :BITAND)
250: AND '||fii_ar_util_pkg.get_where_statement||'
251: ) t

Line 250: AND '||fii_ar_util_pkg.get_where_statement||'

246: ( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
247: FROM fii_time_structures cal,'||fii_ar_util_pkg.get_from_statement||'
248: gt WHERE report_date IN ( :ASOF_DATE, :PREVIOUS_ASOF_DATE)
249: AND (bitand(cal.record_type_id, :BITAND) = :BITAND)
250: AND '||fii_ar_util_pkg.get_where_statement||'
251: ) t
252: WHERE f.time_id = t.time_id
253: AND f.period_type_id = t.period_type_id
254: AND f.org_id = t.org_id

Line 255: AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||' '|| l_parent_party_where ||' ' || l_collector_where ||'

251: ) t
252: WHERE f.time_id = t.time_id
253: AND f.period_type_id = t.period_type_id
254: AND f.org_id = t.org_id
255: AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||' '|| l_parent_party_where ||' ' || l_collector_where ||'
256: '|| l_industry_where||' '|| l_cust_acct_where ||'
257: GROUP BY VIEWBY, t.viewby_code '||l_inner_cst_columns||') inline_view
258: '||l_order_by;
259:

Line 260: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rec_activity_sql, rec_activity_output);

256: '|| l_industry_where||' '|| l_cust_acct_where ||'
257: GROUP BY VIEWBY, t.viewby_code '||l_inner_cst_columns||') inline_view
258: '||l_order_by;
259:
260: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rec_activity_sql, rec_activity_output);
261:
262: END get_rec_activity;
263:
264: END FII_AR_REC_ACTIVITY_PKG;