[Home] [Help]
PACKAGE BODY: APPS.FII_AR_REC_ACT_HISTORY_PKG
Source
1 PACKAGE BODY FII_AR_REC_ACT_HISTORY_PKG AS
2 /* $Header: FIIARDBIRAHB.pls 120.8.12000000.2 2007/04/09 20:23:39 vkazhipu ship $ */
3
4 -----------------------------------------------------------------
5 -- This procedure is called by the Receipts Activity History report
6 -----------------------------------------------------------------
7 PROCEDURE get_rec_act_history
8 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
9 p_rec_act_detail_sql OUT NOCOPY VARCHAR2,
10 p_rec_act_detail_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11 IS
12 l_sqlstmt VARCHAR2(25000);
13 l_rct_amt_url VARCHAR2(500) := NULL;
14 l_return_status VARCHAR2(10);
15 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
16 l_cust_acct_id VARCHAR2(30);
17 l_cust_id VARCHAR2(500);
18 l_where_clause VARCHAR2(2000);
19 l_from_table VARCHAR2(1000);
20 l_gt_table_name VARCHAR2(300) := 'FII_AR_SUMMARY_GT v';
21 l_other VARCHAR2(30) := FND_MESSAGE.get_string('FII', 'FII_AR_OTHER');
22
23
24 BEGIN
25 -- Reset all the global variables to NULL or to the default value
26 fii_ar_util_pkg.reset_globals;
27
28 -- Get the parameters and set the global variables
29 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
30
31 l_cust_id := fii_ar_util_pkg.g_party_id;
32 l_cust_acct_id := fii_ar_util_pkg.g_cust_account_id;
33
34 -- Populate global temp table based on the parameters chosen
35 fii_ar_util_pkg.populate_summary_gt_tables;
36
37 -- Only add the join on cust_acct_id if we have a specific customer acct
38 -- selected
39
40 IF (l_cust_acct_id <> '-111') THEN
41 l_where_clause := l_where_clause ||
42 ' AND f.collector_bill_to_customer_id = :CUST_ACCOUNT_ID
43 AND v.org_id = f.org_id
44 AND v.party_id = :PARTY_ID';
45 l_gt_table_name := 'FII_AR_SUMMARY_GT v';
46
47 -- Only add the join on party_id when we have a specific customer selected
48 -- and if customer account id is not present
49 --if customer account id is present just bind customer account id and don't user customer id
50 --since one customer account anyway belongs to one customer
51
52 ELSIF (l_cust_id <> '-111') THEN
53
54 l_from_table := l_from_table || 'fii_cust_accounts acct, ';
55 l_where_clause := l_where_clause ||
56 ' AND f.collector_bill_to_customer_id = acct.cust_account_id
57 AND acct.account_owner_party_id in ( :PARTY_ID )
58 AND v.org_id = f.org_id
59 AND acct.account_owner_party_id = acct.parent_party_id
60 AND v.party_id = :PARTY_ID';
61 l_gt_table_name := 'FII_AR_SUMMARY_GT v';
62 END IF;
63
64
65 -------------------------------
66 -- Construct the drilldown URLs
67 -------------------------------
68
69 -- Receipt Amount Drilldown URL
70 --
71 l_rct_amt_url := 'pFunctionName=FII_AR_PAID_REC_DTL&BIS_PMV_DRILL_CODE_FII_AR_CUST_ACCOUNT=FII_AR_CUST_ACCOUNT'||
72 '&BIS_PMV_DRILL_CODE_FII_AR_CASH_RECEIPT_ID=FII_AR_CASH_RECEIPT_ID'||
73 '&pParamIds=Y';
74
75
76
77 -------------------------------
78 -- Construct the sql statements
79 -------------------------------
80 l_sqlstmt :=
81 'SELECT /*+ leading(v) cardinality(v 1) */ decode(f.application_status,''ACC'','''||l_other||''',''OTHER ACC'','''||l_other||''',l.meaning) FII_AR_RCT_ACTION,
82 f.amount_applied_rct FII_AR_RCT_AMT,
83 f.filter_date FII_AR_RCT_DATE,
84 fnd.user_name FII_AR_RCT_USER,
85 decode(f.application_status,''APP'',''' ||l_rct_amt_url||''',NULL) FII_AR_RCT_AMT_DRILL,
86 NVL(f.applied_customer_trx_id,-999999) FII_AR_APP_CUST_TRX_ID
87 FROM
88 fii_ar_receipts_f f,
89 '||l_from_table ||'
90 '||l_gt_table_name||'
91 ,ar_lookups l
92 ,fnd_user fnd
93 WHERE f.user_id = fnd.user_id
94 AND l.lookup_type = ''PAYMENT_TYPE''
95 AND l.lookup_code = f.application_status
96 AND f.cash_receipt_id = :CASH_RECEIPT_ID
97 '|| l_where_clause ||'
98 &ORDER_BY_CLAUSE';
99
100
101
102
103 fii_ar_util_pkg.bind_variable
104 (p_sqlstmt => l_sqlstmt,
105 p_page_parameter_tbl => p_page_parameter_tbl,
106 p_sql_output => p_rec_act_detail_sql,
107 p_bind_output_table => p_rec_act_detail_output);
108
109 END get_rec_act_history;
110
111
112
113 END FII_AR_REC_ACT_HISTORY_PKG;
114