[Home] [Help]
PACKAGE BODY: APPS.AP_BANKACCT_INACTIVE_WF_PKG
Source
1 PACKAGE BODY AP_BANKACCT_INACTIVE_WF_PKG AS
2 /* $Header: apbainwb.pls 120.5 2006/02/07 13:26:01 mswamina noship $ */
3
4 -- Package global
5 -- FND_LOG related variables to enable logging for this package
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_BANKACCT_INACTIVE_WF_PKG';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
15
16 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
17 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
18 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
19 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
20 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
21 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
22 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
23 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'AP.PLSQL.AP_BANKACCT_INACTIVE_WF_PKG.';
24
25 ------ Procedure rule_function is called by the Subscription program. Rule Function determines
26 ------ whether WorkFlow Program should be called.
27 ------ event name oracle.apps.iby.bankaccount.assignment_inactivated
28 -------------------------------------------------------------------------------------------
29 FUNCTION Rule_Function (P_Subscription IN RAW,
30 P_Event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2 IS
31
32
33 l_rule VARCHAR2(20);
34 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
35 l_parameter_t wf_parameter_t:= wf_parameter_t(null, null);
36 i_parameter_name l_parameter_t.name%type;
37 i_parameter_value l_parameter_t.value%type;
38 i pls_integer;
39
40 l_bank_acct_id l_parameter_t.value%type;
41 l_party_id l_parameter_t.value%type;
42 l_instr_assgn_id l_parameter_t.value%type;
43
44 BEGIN
45
46 l_parameter_list := p_event.getParameterList();
47 IF l_parameter_list is not null THEN
48 i := l_parameter_list.FIRST;
49 WHILE ( i <= l_parameter_list.LAST )
50 LOOP
51 i_parameter_name := null;
52 i_parameter_value := null;
53
54 i_parameter_name := l_parameter_list(i).getName();
55 i_parameter_value := l_parameter_list(i).getValue();
56
57 IF i_parameter_name is not null THEN
58 IF i_parameter_name = 'ExternalBankAccountID' THEN
59 l_bank_acct_id := i_parameter_value;
60 ELSIF i_parameter_name = 'PartyID' THEN
61 l_party_id := i_parameter_value;
62 ELSIF i_parameter_name = 'InstrumentAssignmentID' THEN
63 l_instr_assgn_id := i_parameter_value;
64 END IF;
65 END IF;
66
67 i := l_parameter_list.NEXT(i);
68 END LOOP;
69 END IF;
70
71 -- If Update_Payment_Schedules True then only execute WF program
72
73 IF Update_Payment_Schedules (l_bank_acct_id,
74 l_party_id,
75 l_instr_assgn_id,
76 'AP_BANKACCT_INACTIVE_WF_PKG.Rule_Function')
77 THEN
78
79 l_rule := WF_RULE.Default_Rule(p_subscription,p_event);
80
81 END IF;
82
83 RETURN ('SUCCESS');
84
85 END Rule_Function;
86
87 -- This procedure will be called from Rule_Function and Update the Payment
88 -- Schedules for inactivated bank account
89
90 FUNCTION Update_Payment_Schedules (
91 P_bank_account_id NUMBER,
92 P_party_id NUMBER,
93 P_instr_assgn_id NUMBER,
94 P_calling_sequence VARCHAR2) RETURN BOOLEAN IS
95
96 l_party_site_id NUMBER;
97 l_supplier_site_id NUMBER; /* bug 5000194, 4965233 */
98 l_vendor_id AP_SUPPLIERS.Vendor_Id%TYPE;
99 l_vendor_type_lookup_code AP_SUPPLIERS.Vendor_Type_Lookup_Code%TYPE;
100 l_payment_function VARCHAR2(80);
101 l_org_id NUMBER;
102 l_currency_code IBY_EXT_BANK_ACCOUNTS.Currency_Code%TYPE;
103 l_extbank_acct_id NUMBER;
104
105 l_current_calling_sequence VARCHAR2(2000);
106 l_debug_info VARCHAR2(2000);
107 l_api_name CONSTANT VARCHAR2(100) := 'Update_Payment_Schedules';
108
109 BEGIN
110
111 l_current_calling_sequence := p_calling_sequence||'->'||
112 'AP_BANKACCT_INACTIVE_WF_PKG.Update_Payment_Shedules';
113 --
114 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
115 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
116 ||' P_bank_account_id: '|| p_bank_account_Id
117 ||', P_party_Id: '||p_party_id
118 ||', P_instr_assgn_id: '||p_instr_assgn_id);
119 END IF;
120
121 l_debug_info := 'Deriving Vendor_Site_Id, Org_Id from Iby_Payee_Assigned_Bankacct_v';
122 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
123 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
124 END IF;
125
126 BEGIN
127 SELECT payment_function,
128 party_site_id,
129 supplier_site_id, /* bug 5000194, 4965233 */
130 org_id,
131 currency_code
132 INTO l_payment_function,
133 l_party_site_id,
134 l_supplier_site_id,
135 l_org_id,
136 l_currency_code
137 FROM Iby_Payee_Assigned_Bankacct_V
138 WHERE instr_assignment_id = p_instr_assgn_id
139 AND ext_bank_account_id = p_bank_account_id
140 AND party_id = p_party_id;
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 l_debug_info := 'Iby_Payee_Assigned_Bankacct_V has no row for Assignment Id';
144 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
146 END IF;
147 RETURN FALSE;
148 END;
149
150 /* bug 5000194. 4965233 */
151 BEGIN
152 SELECT vendor_id,
153 vendor_type_lookup_code
154 INTO l_vendor_id,
155 l_vendor_type_lookup_code
156 FROM ap_suppliers
157 WHERE party_id = p_party_id;
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 l_debug_info := 'This Payee is not a Supplier';
161 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
162 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
163 END IF;
164 NULL;
165 END;
166
167 l_debug_info := 'Calling Payables wrapper for deriving next available bank '||
168 'account from IBY';
169 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
170 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
171 END IF;
172
173 IF l_vendor_type_lookup_code = 'EMPLOYEE' THEN
174 l_extbank_acct_id := AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id
175 (l_vendor_id,
176 l_supplier_site_id,
177 l_payment_function,
178 l_org_id,
179 l_currency_code,
180 l_current_calling_sequence);
181 ELSE
182 l_extbank_acct_id := AP_IBY_UTILITY_PKG.Get_Default_Iby_Bank_Acct_Id
183 (p_party_id,
184 l_payment_function,
185 l_party_site_id,
186 l_org_id,
187 l_currency_code,
188 l_current_calling_sequence);
189
190 END IF;
191
192 IF l_party_site_id IS NULL THEN
193
194 IF l_org_id IS NOT NULL THEN
195
196 l_debug_info := 'Update Payment Schedules when assignment is inactivated at '
197 ||'party level for specific org: '||l_org_id;
198 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
199 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
200 END IF;
201
202 UPDATE ap_payment_schedules_all aps
203 SET aps.external_bank_account_id = l_extbank_acct_id,
204 aps.last_update_date = SYSDATE,
205 aps.last_updated_by = FND_GLOBAL.user_id,
206 aps.last_update_login = FND_GLOBAL.login_id
207 WHERE aps.invoice_id IN
208 (SELECT DISTINCT ai.invoice_id
209 FROM ap_invoices_all ai, ap_payment_schedules_all aps1
210 WHERE aps1.external_bank_account_id = P_bank_account_id
211 AND ai.invoice_id = aps1.invoice_id
212 AND ai.org_id = l_org_id
213 AND ai.payment_status_flag IN ('N','P')
214 AND ai.cancelled_date IS NULL
215 AND ai.party_id = p_party_id
216 AND (l_supplier_site_id IS NULL
217 OR ai.vendor_site_id = l_supplier_site_id));
218
219 ELSE
220
221 l_debug_info := 'Update Payment Schedules when assignment is inactivated at '
222 ||'party level for all org';
223 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
224 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
225 END IF;
226
227 UPDATE ap_payment_schedules_all aps
228 SET aps.external_bank_account_id = l_extbank_acct_id,
229 aps.last_update_date = SYSDATE,
230 aps.last_updated_by = FND_GLOBAL.user_id,
231 aps.last_update_login = FND_GLOBAL.login_id
232 WHERE aps.invoice_id IN
233 (SELECT DISTINCT ai.invoice_id
234 FROM ap_invoices_all ai, ap_payment_schedules_all aps1
235 WHERE aps1.external_bank_account_id = P_bank_account_id
236 AND ai.invoice_id = aps1.invoice_id
237 AND ai.payment_status_flag IN ('N','P')
238 AND ai.cancelled_date IS NULL
239 AND ai.party_id = p_party_id
240 AND (l_supplier_site_id IS NULL
241 OR ai.vendor_site_id = l_supplier_site_id));
242
243 END IF;
244
245 ELSE
246
247 l_debug_info := 'Update Payment Schedules when assignment is inactivated at '
248 ||'party site level';
249 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
251 END IF;
252
253 UPDATE ap_payment_schedules_all aps
254 SET aps.external_bank_account_id = l_extbank_acct_id,
255 aps.last_update_date = SYSDATE,
256 aps.last_updated_by = FND_GLOBAL.user_id,
257 aps.last_update_login = FND_GLOBAL.login_id
258 WHERE aps.invoice_id IN
259 (SELECT DISTINCT ai.invoice_id
260 FROM ap_invoices_all ai, ap_payment_schedules_all aps1
261 WHERE aps1.external_bank_account_id = P_bank_account_id
262 AND ai.invoice_id = aps1.invoice_id
263 AND ai.payment_status_flag IN ('N','P')
264 AND ai.cancelled_date IS NULL
265 AND ai.party_site_id = l_party_site_id
266 AND ai.party_id = p_party_id);
267
268 END IF;
269
270 RETURN TRUE;
271
272 END Update_Payment_Schedules;
273
274 END AP_BANKACCT_INACTIVE_WF_PKG;