[Home] [Help]
PACKAGE BODY: APPS.CE_XML_BS_LOADER
Source
1 PACKAGE BODY CE_XML_BS_LOADER AS
2 /* $Header: cexmldrb.pls 120.3 2005/06/24 15:28:13 lkwan noship $ */
3
4 CURSOR get_statements_cursor IS
5 select item_key,
6 statement_number,
7 trading_partner,
8 bank_account_num
9 from ce_xml_statement_list;
10
11 PROCEDURE SETUP_IMPORT(
12 X_STATEMENT_NUMBER IN VARCHAR2,
13 X_BANK_ACCOUNT_NUM IN VARCHAR2,
14 X_TRADING_PARTNER IN VARCHAR2,
15 X_ITEM_KEY IN VARCHAR2) IS
16
17 BEGIN
18 insert into CE_XML_STATEMENT_LIST
19 (ITEM_KEY, STATEMENT_NUMBER, TRADING_PARTNER, BANK_ACCOUNT_NUM)
20 values (
21 X_ITEM_KEY,
22 X_STATEMENT_NUMBER,
23 X_TRADING_PARTNER,
24 X_BANK_ACCOUNT_NUM);
25 END SETUP_IMPORT;
26
27
28 PROCEDURE RUN_IMPORT IS
29
30 l_statement_number VARCHAR2(50);
31 l_trading_partner VARCHAR2(50);
32 l_bank_account_num VARCHAR2(50);
33 l_item_key VARCHAR2(20);
34
35 l_cnt NUMBER;
36 l_org_id NUMBER;
37 l_bank_branch_id NUMBER;
38 l_bank_account_id NUMBER;
39 errbuf VARCHAR2(256);
40 retcode NUMBER;
41
42 l_bs_count NUMBER;
43 l_bs_count2 NUMBER;
44
45 BEGIN
46
47 OPEN get_statements_cursor;
48 LOOP
49 FETCH get_statements_cursor INTO l_item_key,
50 l_statement_number,
51 l_trading_partner,
52 l_bank_account_num;
53 EXIT WHEN get_statements_cursor%NOTFOUND OR
54 get_statements_cursor%NOTFOUND is null;
55
56 DELETE ce_xml_statement_list
57 WHERE item_key = l_item_key;
58
59 WF_ENGINE.createProcess('CEXMLBSL', l_item_key, 'CE_XML_BSL');
60
61 if (l_trading_partner is null) then
62 SELECT count(1)
63 INTO l_cnt
64 FROM CE_BANK_ACCOUNTS
65 WHERE BANK_ACCOUNT_NUM = l_bank_account_num;
66 else
67 SELECT count(1)
68 INTO l_cnt
69 FROM CE_BANK_ACCOUNTS BA, CE_BANK_BRANCHES_v BB
70 WHERE BA.bank_account_num = l_bank_account_num
71 AND BB.bank_name = l_trading_partner
72 AND BB.branch_party_id = BA.bank_branch_id;
73 end if;
74
75 if (l_cnt = 0) then
76
77 /* no bank account id found */
78 WF_ENGINE.SetItemAttrNumber(itemtype => 'CEXMLBSL',
79 itemkey => l_item_key,
80 aname => 'CE_IMPORT',
81 avalue => -1);
82
83 elsif (l_cnt > 1) then
84
85 /* multiple bank account id found */
86 WF_ENGINE.SetItemAttrNumber(itemtype => 'CEXMLBSL',
87 itemkey => l_item_key,
88 aname => 'CE_IMPORT',
89 avalue => -2);
90
91 else
92
93 /* can run the import program */
94 if (l_trading_partner is null) then
95 SELECT BB.branch_party_id,
96 BA.bank_account_id,
97 BA.ACCOUNT_OWNER_org_id,
98 BB.bank_name
99 INTO l_bank_branch_id, l_bank_account_id, l_org_id, l_trading_partner
100 FROM CE_BANK_ACCOUNTS BA, CE_BANK_BRANCHES_V BB
101 WHERE BA.bank_account_num = l_bank_account_num
102 AND BB.branch_party_id = BA.bank_branch_id;
103 else
104 SELECT BB.branch_party_id,
105 BA.bank_account_id,
106 BA.ACCOUNT_OWNER_org_id
107 INTO l_bank_branch_id, l_bank_account_id, l_org_id
108 FROM CE_BANK_ACCOUNTS BA, CE_BANK_BRANCHES_V BB
109 WHERE BA.bank_account_num = l_bank_account_num
110 AND BB.bank_name = l_trading_partner
111 AND BB.branch_party_id = BA.bank_branch_id;
112 end if;
113
114 fnd_client_info.set_org_context(l_org_id);
115
116 SELECT count(1)
117 INTO l_bs_count
118 FROM CE_STATEMENT_HEADERS_V
119 WHERE bank_account_id = l_bank_account_id
120 AND statement_number = l_statement_number;
121
122 CE_AUTO_BANK_REC.statement(
123 errbuf => errbuf,
124 retcode => retcode,
125 p_option => 'IMPORT',
126 p_bank_branch_id => to_char(l_bank_branch_id),
127 p_bank_account_id => to_char(l_bank_account_id),
128 p_statement_number_from => l_statement_number,
129 p_statement_number_to => l_statement_number,
130 p_statement_date_from => '',
131 p_statement_date_to => '',
132 p_gl_date => to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
133 p_receivables_trx_id => '',
134 p_payment_method_id => '',
135 p_nsf_handling => 'NO_ACTION',
136 p_display_debug => 'N',
137 p_debug_path => '',
138 p_debug_file => '');
139
140 /* check if there is error during import */
141 SELECT count(1)
142 INTO l_bs_count2
143 FROM CE_STATEMENT_HEADERS_V
144 WHERE bank_account_id = l_bank_account_id
145 AND statement_number = l_statement_number;
146
147 if (l_bs_count = 0 AND l_bs_count2 = 1) then
148
149 /* no error (or just warning) during import */
150 WF_ENGINE.SetItemAttrNumber(itemtype => 'CEXMLBSL',
151 itemkey => l_item_key,
152 aname => 'CE_IMPORT',
153 avalue => 0);
154
155 else
156
157 /* has error during import */
158 WF_ENGINE.SetItemAttrNumber(itemtype => 'CEXMLBSL',
159 itemkey => l_item_key,
160 aname => 'CE_IMPORT',
161 avalue => -3);
162
163 end if;
164
165 end if;
166
167 /* set up other WF notification info */
168 if (l_trading_partner is null) then
169 l_trading_partner := 'unidentified FSP';
170 end if;
171
172 WF_ENGINE.SetItemAttrText(itemtype => 'CEXMLBSL',
173 itemkey => l_item_key,
174 aname => 'CE_TRADING_PARTNER',
175 avalue => l_trading_partner);
176
177 WF_ENGINE.SetItemAttrText(itemtype => 'CEXMLBSL',
178 itemkey => l_item_key,
179 aname => 'STATEMENT_NUMBER',
180 avalue => l_statement_number);
181
182 WF_ENGINE.SetItemAttrText(itemtype => 'CEXMLBSL',
183 itemkey => l_item_key,
184 aname => 'CE_NOTIFICATION_ROLE',
185 avalue => 'Cash Management Notifications');
186
187 WF_ENGINE.startProcess('CEXMLBSL', l_item_key);
188
189 END LOOP;
190 CLOSE get_statements_cursor;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF get_statements_cursor%ISOPEN THEN
195 CLOSE get_statements_cursor;
196 END IF;
197 RAISE;
198 END RUN_IMPORT;
199
200 END CE_XML_BS_LOADER;