1 PACKAGE BODY CE_BNK_STMT_IMP_RPT_PKG
2 -- $Header: CEBNKSTMTIMPB.pls 120.0.12000000.2 2007/06/21 02:13:20 csutaria noship $
3 --*****************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 --*****************************************************************************
7 --
8 --
9 -- PROGRAM NAME
10 -- CEBNKSTMTIMPB.pls
11 --
12 -- DESCRIPTION
13 -- This script creates the package body of CE_BNK_STMT_IMP_RPT_PKG.
14 -- This package is used by the 'Bank Statement Import Validation - Israel' report.
15 --
16 -- USAGE
17 --
18 -- To execute This can be applied by running this script at SQL*Plus.
19 --
20 -- PROGRAM LIST DESCRIPTION
21 --
25 -- beforeReportTrigger It is a public function which is run just after the
22 -- get_account_num It is a private function which is used to fetch the
23 -- bank account number corresponding to a bank account ID
24 --
26 -- queries are parsed and before queries are executed.
27 --
28 -- DEPENDENCIES
29 -- None
30 --
31 -- CALLED BY
32 -- DataTemplate Extract in 'Bank Statement Import Validation - Israel' report.
33 --
34 -- LAST UPDATE DATE 20-DEC-2006
35 --
36 --
37 -- HISTORY
38 -- =======
39 --
40 -- VERSION DATE AUTHOR(S) DESCRIPTION
41 -- ------- ----------- ----------------- ------------------------------------
42 -- Draft1A 20-DEC-2006 Harsh Poddar Draft Version
43 --***************************************************************************
44
45 AS
46
47 FUNCTION get_account_num(P_BANK_ACCOUNT_ID IN VARCHAR2) RETURN VARCHAR2 IS
48 lc_bank_account_num VARCHAR2(50);
49
50 BEGIN
51
52 SELECT cbav.bank_account_num
53 INTO lc_bank_account_num
54 FROM ce_bank_accounts_v cbav
55 WHERE cbav.bank_account_id = P_BANK_ACCOUNT_ID;
56
57 RETURN lc_bank_account_num;
58
59 END get_account_num;
60
61 FUNCTION beforeReportTrigger RETURN BOOLEAN IS
62
63 BEGIN
64
65 SELECT cbbv.bank_branch_name
66 INTO gc_bank_branch_name
67 FROM ce_bank_branches_v cbbv
68 WHERE cbbv.branch_party_id = P_BANK_BRANCH_ID;
69
70 gc_status_current := fnd_message.get_string('CE','CE_BNK_STMT_IMP_CUR');
71 gc_status_latest := fnd_message.get_string('CE','CE_BNK_STMT_IMP_LAT');
72
73 IF (P_BANK_ACCOUNT_ID IS NOT NULL AND P_STATEMENT_NUM IS NOT NULL) THEN
74 /* Specific values have been chosen for all parameters */
75
76 gc_bank_account_num := get_account_num(P_BANK_ACCOUNT_ID);
77
78 lc_uploaded_select_columns := ' :gc_bank_account_num BANK_ACCOUNT_NUM,
79 :P_STATEMENT_NUM BANK_STMT_NUM';
80
81 lc_uploaded_where_conditions := 'uploaded.bank_branch_name = :gc_bank_branch_name
82 AND uploaded.bank_account_num = :gc_bank_account_num
83 AND uploaded.statement_number = :P_STATEMENT_NUM';
84
85 lc_uploaded_group_by := ' :gc_bank_account_num, uploaded.statement_date';
86
87 ELSIF (P_BANK_ACCOUNT_ID IS NOT NULL) THEN
88 /* Specific values have been chosen for bank branch name and bank account number parameters only */
89
90 gc_bank_account_num := get_account_num(P_BANK_ACCOUNT_ID);
91
92 lc_uploaded_select_columns := ' :gc_bank_account_num BANK_ACCOUNT_NUM, uploaded.statement_number BANK_STMT_NUM';
93
94 lc_uploaded_where_conditions := 'uploaded.bank_branch_name = :gc_bank_branch_name
95 AND uploaded.bank_account_num = :gc_bank_account_num';
96
97 lc_uploaded_group_by := ':gc_bank_account_num, uploaded.statement_number, uploaded.statement_date';
98
99 ELSIF (P_STATEMENT_NUM IS NOT NULL) THEN
100 /* Specific values have been chosen for bank branch name and bank statement number parameters only */
101
102 lc_uploaded_select_columns := ' uploaded.bank_account_num BANK_ACCOUNT_NUM
103 , :P_STATEMENT_NUM BANK_STMT_NUM';
104
105 lc_uploaded_where_conditions := 'uploaded.bank_branch_name = :gc_bank_branch_name
106 AND uploaded.statement_number = :P_STATEMENT_NUM';
107
108 lc_uploaded_group_by := 'uploaded.bank_account_num, uploaded.statement_date';
109
110 ELSE /* Specific value has been chosen for bank branch name parameter only */
111
112 lc_uploaded_select_columns := ' uploaded.bank_account_num BANK_ACCOUNT_NUM
113 , uploaded.statement_number BANK_STMT_NUM';
114
115 lc_uploaded_where_conditions := 'uploaded.bank_branch_name = :gc_bank_branch_name';
116
117 lc_uploaded_group_by := ' uploaded.bank_account_num, uploaded.statement_number, uploaded.statement_date ';
118
119 END IF;
120
121 IF P_BANK_ACCOUNT_ID IS NOT NULL THEN
122
123 lc_latest_bank_acc_num := ':gc_bank_account_num';
124
125 lc_latest_bank_acc_from := ' ';
126
127 lc_latest_bank_acc_where := ' AND latest.bank_account_id = :P_BANK_ACCOUNT_ID ';
128 ELSE
129 /*lc_latest_bank_acc_num := 'cba.bank_account_num';
130
131 lc_latest_bank_acc_from := ', ce_bank_accounts cba ';
132
133 lc_latest_bank_acc_where := ' AND cba.bank_account_id = latest.bank_account_id ';*/
134 lc_latest_bank_acc_num := 'cba.bank_account_num';
135
136 lc_latest_bank_acc_from := ', ce_bank_accounts cba ';
137
138 lc_latest_bank_acc_where := ' AND cba.bank_account_id = latest.bank_account_id
139 AND cba.bank_branch_id = :P_BANK_BRANCH_ID';
140
141 END IF;
142
143 RETURN (TRUE);
144
145 END beforeReportTrigger;
146
147 END CE_BNK_STMT_IMP_RPT_PKG;
[Home] [Help]