[Home] [Help]
PACKAGE BODY: APPS.FII_AR_PDUE_REC_TREND
Source
1 PACKAGE BODY FII_AR_PDUE_REC_TREND AS
2 /* $Header: FIIARDBIPTB.pls 120.6.12000000.2 2007/04/09 20:16:08 vkazhipu ship $ */
3
4
5
6 PROCEDURE get_pdue_rec_trend (
7 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 open_rec_sql OUT NOCOPY VARCHAR2,
9 open_rec_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
10 IS
11
12 l_viewby_id VARCHAR2(50);
13 l_viewby VARCHAR2(250);
14 l_party_where VARCHAR2(250);
15 l_parent_party_where VARCHAR2(250);
16 l_collector_where VARCHAR2(250);
17 l_cust_acct_where VARCHAR2(250);
18 l_cust_self_drill VARCHAR2(500);
19 l_past_due_rec_drill VARCHAR2(500);
20 l_open_rec_drill VARCHAR2(500);
21 l_select VARCHAR2(15000);
22 l_group_by VARCHAR2(100) := NULL;
23 l_order_by VARCHAR2(250);
24 l_order_column VARCHAR2(250);
25 l_select_curr_end_prd VARCHAR2(5000);
26 l_end_date VARCHAR2(50);
27
28 BEGIN
29
30 fii_ar_util_pkg.reset_globals;
31 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
32
33 -- Viewby month
34 SELECT fii_time_api.ent_cper_end(fii_ar_util_pkg.g_as_of_date) INTO fii_ar_util_pkg.g_curr_per_end FROM DUAL;
35 SELECT fii_time_api.ent_pper_end(fii_ar_util_pkg.g_as_of_date) INTO fii_ar_util_pkg.g_prior_per_end FROM DUAL;
36
37
38 fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
39 fii_ar_util_pkg.populate_summary_gt_tables;
40
41 /* Dynamically generating the where clause for PARTY_ID*/
42 IF fii_ar_util_pkg.g_party_id <> '-111' THEN
43 l_party_where := ' AND f.party_id = t.party_id ';
44 END IF;
45
46
47 /* Dynamically generating the where clause for COLLECTOR_ID*/
48 IF fii_ar_util_pkg.g_collector_id <> '-111' THEN
49 l_collector_where := 'AND f.collector_id = t.collector_id ';
50 END IF;
51
52 IF fii_ar_util_pkg.g_as_of_date = fii_ar_util_pkg.g_curr_per_end THEN
53 l_select_curr_end_prd :=' ';
54 l_end_date := ' :ASOF_DATE ';
55
56 ELSE
57
58 l_select_curr_end_prd := ' UNION ALL
59 /* The select statment will return data for current month asofdate, if asofdate<> last day of month*/
60 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
61 per.sequence sequence,
62 sum(f.total_open_amount) FII_AR_OPEN_REC,
63 sum(f.past_due_open_amount) FII_AR_PDUE_REC,
64 NULL FII_AR_PRIOR_PDUE_REC
65 FROM fii_time_ent_period per,
66 FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
67 ( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ * FROM fii_time_structures cal, fii_ar_summary_gt gt
68 WHERE report_date = :ASOF_DATE
69 AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE ) t
70 WHERE f.time_id = t.time_id
71 AND f.period_type_id = t.period_type_id
72 AND f.org_id = t.org_id
73 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||l_collector_where||'
74 AND per.end_date = :CURR_PERIOD_END
75 GROUP BY report_date, per.sequence
76 ';
77
78 l_end_date := ' :PRIOR_PERIOD_END ';
79
80 END IF;
81
82 l_select :=
83 '
84 SELECT cy_per.name VIEWBY,
85 to_char(cy_per.end_date,''DD/MM/YYYY'') FII_AR_MONTH_END_DATE,
86 SUM(FII_AR_OPEN_REC) FII_AR_OPEN_REC,
87 SUM(FII_AR_PDUE_REC) FII_AR_PDUE_REC,
88 SUM(FII_AR_PRIOR_PDUE_REC) FII_AR_PRIOR_PDUE_REC,
89 SUM(FII_AR_PDUE_REC) FII_AR_PDUE_REC_G,
90 DECODE(SUM(FII_AR_PDUE_REC),NULL,NULL,0,NULL,DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
91 ''&pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
92 ''AS_OF_DATE=FII_AR_MONTH_END_DATE&pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'')) FII_AR_PDUE_REC_DRILL,
93 DECODE(SUM(FII_AR_OPEN_REC),NULL,NULL,0,NULL,DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
94 ''&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
95 ''AS_OF_DATE=FII_AR_MONTH_END_DATE&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'')) FII_AR_OPEN_REC_DRILL
96 FROM
97 fii_time_ent_period cy_per,
98 (SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
99 t.sequence sequence,
100 CASE WHEN t.report_date >= :SD_SDATE THEN
101 sum(f.total_open_amount) ELSE NULL END FII_AR_OPEN_REC,
102 CASE WHEN t.report_date >= :SD_SDATE THEN
103 sum(f.past_due_open_amount) ELSE NULL END FII_AR_PDUE_REC,
104 CASE WHEN t.report_date < :SD_SDATE THEN
105 sum(f.past_due_open_amount) ELSE NULL END FII_AR_PRIOR_PDUE_REC
106 FROM FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
107 ( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ * FROM fii_time_structures cal, fii_ar_summary_gt gt,fii_time_ent_period per
108 WHERE
109 cal.report_date = per.end_date
110 and per.start_date <= '||l_end_date||'
111 AND per.start_date >= :SD_PRIOR_PRIOR
112 AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE ) t
113 WHERE f.time_id = t.time_id
114 AND f.period_type_id = t.period_type_id
115 AND f.org_id = t.org_id
116 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||l_collector_where||'
117 GROUP BY report_date, t.sequence
118 '||l_select_curr_end_prd||'
119 ) inline_view
120 WHERE cy_per.start_date <= :ASOF_DATE
121 AND cy_per.start_date > :SD_PRIOR
122 AND cy_per.sequence = inline_view.sequence (+)
123 GROUP BY cy_per.name,cy_per.end_date,cy_per.start_date
124 ORDER BY cy_per.start_date
125 ';
126
127
128
129 fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, open_rec_sql, open_rec_output);
130
131
132 END get_pdue_rec_trend;
133
134 END FII_AR_PDUE_REC_TREND;
135