[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_SI_BAI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_PS_SI_BAI_EXPORT_PKG AS
2 --$Header: JACNBAIB.pls 120.3 2010/11/08 06:34:43 riqi noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNBAIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Bank Account Information |
13 --| for Public Sector |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Add_Bank_Account_Information |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 06/AUG/2010 Wuhua Liu Created |
21 --| 08/NOV/2010 Richard Qi Fixed Bug# 10268933 |
22 --+======================================================================*/
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 --
27 -- Add_Bank_Account_Information Public
28 --
29 -- DESCRIPTION:
30 --
31 -- This procedure is to export Bank Account Information for
32 -- public sector
33 --
34 -- PARAMETERS:
35 -- In: pn_legal_entity_id legal_entity ID
36 -- pn_accounting_year accounting year
37 --
38 -- DESIGN REFERENCES:
39 -- TDD_1213_FIN_GL_P_CNAOV2_SI.doc
40 --
41 -- CHANGE HISTORY:
42 --
43 -- 06-AUG-2010 Wuhua Liu created
44 -- 14-SEP-2010 Wuhua Liu bug# 10110988 handle the
45 -- duplicate bank information, only
46 -- export latest bank profiles
47 -- 08-NOV-2010 Richard Qi Fixed Bug# 10268933
48
49 PROCEDURE Add_Bank_Account_Information
50 ( pn_legal_entity_id IN NUMBER
51 , pn_accounting_year IN NUMBER
52 )
53 IS
54 lv_procedure_name VARCHAR2(40) := 'Add_Bank_Account_Information';
55 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
56 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
57 ln_row_count NUMBER := 0;
58
59 --To get bank account information
60 CURSOR bank_account_cur
61 ( pn_legal_entity NUMBER
62 , pn_account_year NUMBER
63 )
64 IS
65 SELECT
66 CBA.BANK_ACCOUNT_ID BANK_ACCOUNT_ID
67 , HOP.BANK_OR_BRANCH_NUMBER BANK_NUMBER
68 , HP.PARTY_NAME BANK_NAME
69 , CBA.BANK_ACCOUNT_NAME ACCOUNT_NAME
70 , CBA.BANK_ACCOUNT_TYPE ACCOUNT_TYPE
71 , CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
72 FROM
73 HZ_PARTIES HP
74 , HZ_ORGANIZATION_PROFILES HOP
75 , HZ_CODE_ASSIGNMENTS HCA -- added for fixing bug# 10268933
76 , CE_BANK_ACCOUNTS CBA
77 WHERE CBA.BANK_ID = HP.PARTY_ID(+)
78 AND CBA.BANK_ID = HOP.PARTY_ID(+)
79 AND HP.PARTY_TYPE = 'ORGANIZATION'
80 AND CBA.BANK_ACCOUNT_NUM IS NOT NULL
81 AND (CBA.START_DATE IS NULL
82 OR
83 EXTRACT(YEAR FROM CBA.START_DATE) <= pn_account_year
84 ) -- THE ACCOUNTING YEAR PARAMETER
85 AND (CBA.END_DATE IS NULL
86 OR
87 EXTRACT(YEAR FROM CBA.END_DATE) >= pn_account_year
88 ) -- THE ACCOUNTING YEAR PARAMETER
89 --ONLY THE LATEST BANK PROFILE SHOULD BE EXPORTED
90 -- BUG 10110988, ONLY EXPORT THE LATEST BANK INFO
91 AND HOP.ORGANIZATION_PROFILE_ID = (
92 SELECT MAX(HOP2.ORGANIZATION_PROFILE_ID)
93 FROM HZ_ORGANIZATION_PROFILES HOP2
94 WHERE CBA.BANK_ID = HOP2.PARTY_ID
95 -- the following is commented for fixing bug# 10268933
96 /*AND EXTRACT(YEAR FROM HOP2.EFFECTIVE_START_DATE) <= PN_ACCOUNT_YEAR*/
97 AND ( HOP2.EFFECTIVE_END_DATE IS NULL
98 OR
99 EXTRACT(YEAR FROM HOP2.EFFECTIVE_END_DATE) >= pn_account_year
100 )
101 )
102 -- added for fixing bug# 10268933 BEGIN --
103 AND HCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
104 AND HCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')
105 AND HCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
106 AND HCA.OWNER_TABLE_ID = HP.PARTY_ID
107 AND ( HCA.END_DATE_ACTIVE IS NULL
108 OR
109 EXTRACT(YEAR FROM HCA.END_DATE_ACTIVE) >= pn_account_year
110
111 )
112 -- added for fixing bug# 10268933 END --
113 --THE LEGAL ENTITY ID PARAMETER
114 AND CBA.ACCOUNT_OWNER_ORG_ID = pn_legal_entity;
115
116 BEGIN
117 --logging for debug
118 IF (ln_proc_level >= ln_dbg_level)
119 THEN
120 FND_LOG.STRING( ln_proc_level
121 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.begin'
122 , 'Enter procedure');
123 --logging the parameters
124 FND_LOG.STRING( ln_proc_level
125 , GV_MODULE_PREFIX|| '.' || lv_procedure_name
126 || '.parameters'
127 , 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
128 || 'pv_accounting_year=' || pn_accounting_year);
129 END IF; --ln_proc_level>=ln_dbg_level
130 FND_FILE.PUT_LINE( FND_FILE.log
131 , lv_procedure_name || '.parameters'
132 || 'pn_legal_entity_id=' || pn_legal_entity_id || ','
133 || 'pv_accounting_year=' || pn_accounting_year);
134 FOR v_row IN bank_account_cur( pn_legal_entity_id
135 , pn_accounting_year
136 )
137 LOOP
138 --logging the parameters
139 ln_row_count := ln_row_count + 1;
140 JA_CN_UTILITY.Add_Sub_Root_Node( 'BANK_ACCOUNT_INFORMATION'
141 , JA_CN_UTILITY.GV_TAG_TYPE_START
142 );
143 JA_CN_UTILITY.Add_Child_Node( 'BANK_ID'
144 , v_row.bank_number
145 );
146 JA_CN_UTILITY.Add_Child_Node( 'BANK_NAME'
147 , v_row.bank_name
148 );
149 JA_CN_UTILITY.Add_Child_Node( 'BANK_ACCOUNT'
150 , v_row.account_name
151 );
152 JA_CN_UTILITY.Add_Child_Node( 'BANK_ACCOUNT_TYPE'
153 , v_row.account_type
154 );
155 JA_CN_UTILITY.Add_Sub_Root_Node( 'BANK_ACCOUNT_INFORMATION'
156 , JA_CN_UTILITY.GV_TAG_TYPE_END
157 );
158 END LOOP; --v_row IN bank_account_cur(pn_legal_entity_id,pn_accounting_year)
159
160 IF (ln_row_count = 0)
161 THEN
162 JA_CN_UTILITY.Print_No_Data_Found_For_Log( 'BANK_ACCOUNT_INFORMATION'
163 , JA_CN_UTILITY.GV_MODULE_GLSI
164 );
165 JA_CN_UTILITY.Add_Sub_Root_Node( 'BANK_ACCOUNT_INFORMATION'
166 , JA_CN_UTILITY.GV_TAG_TYPE_START
167 );
168 JA_CN_UTILITY.Add_Child_Node( 'BANK_ID'
169 , NULL
170 );
171 JA_CN_UTILITY.Add_Child_Node( 'BANK_NAME'
172 , NULL
173 );
174 JA_CN_UTILITY.Add_Child_Node( 'BANK_ACCOUNT'
175 , NULL
176 );
177 JA_CN_UTILITY.Add_Child_Node( 'BANK_ACCOUNT_TYPE'
178 , NULL
179 );
180 JA_CN_UTILITY.Add_Sub_Root_Node( 'BANK_ACCOUNT_INFORMATION'
181 , JA_CN_UTILITY.GV_TAG_TYPE_END
182 );
183 END IF; -- (ln_row_count = 0)
184 --logging for debug
185 IF (ln_proc_level >= ln_dbg_level)
186 THEN
187 FND_LOG.STRING( ln_proc_level
188 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end'
189 , 'Exit procedure');
190 END IF; -- (ln_proc_level>=ln_dbg_level)
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
194 THEN
195 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
196 , GV_MODULE_PREFIX || '.' || lv_procedure_name ||
197 ' .Other_Exception '
198 , SQLCODE || SQLERRM);
199 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
200 FND_FILE.PUT_LINE( FND_FILE.log
201 , GV_MODULE_PREFIX || '.' || lv_procedure_name
202 || SQLCODE || SQLERRM );
203 RAISE;
204 END Add_Bank_Account_Information;
205
206 END JA_CN_PS_SI_BAI_EXPORT_PKG;