[Home] [Help]
PACKAGE BODY: APPS.FV_ASSIGN_REASON_CODES_PKG
Source
1 PACKAGE BODY fv_assign_reason_codes_pkg AS
2 -- $Header: FVXPPRCB.pls 120.8.12000000.3 2007/08/16 15:33:50 sasukuma ship $
3
4 /*******************************************************************/
5 /***** Variable Declaration For All Processes ******/
6 /*******************************************************************/
7 x_set_of_books_id number :=NULL;
8 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
9 -- x_set_of_books_id number := to_number(fnd_profile.value('GL_SET_OF_BKS_ID'));
10 g_module_name varchar2(100) := 'fv.plsql.fv_assign_reason_codes_pkg.';
11 l_set_of_books_name gl_ledgers.name%TYPE :=NULL;
12
13 x_org_id number :=mo_global.get_current_org_id;
14 -- mo_utils.get_ledger_info(x_org_id ,x_set_of_books_id ,l_set_of_books_name);
15
16
17
18
19 err_message varchar2(100);
20 v_count number (15) := 0;
21
22
23
24 procedure set_org(x_org_id IN NUMBER) IS
25 BEGIN
26 --procedure added for as per design in ver 1.0 of design document
27
28 IF (x_org_id IS NOT NULL) THEN
29
30
31 mo_utils.Get_Ledger_Info
32 ( p_operating_unit => x_org_id
33 , p_ledger_id => x_set_of_books_id
34 , p_ledger_name => l_set_of_books_name);
35
36
37 End if;
38
39
40 END;
41
42
43
44 PROCEDURE interest_reason_codes IS
45
46
47 CURSOR reason_codes_cur IS
48 SELECT air.original_invoice_id, air.checkrun_name
49 FROM ap_invoice_relationships air,
50 ap_invoices api,
51 fv_terms_types fvt
52 WHERE( air.original_invoice_id NOT IN
53 (SELECT farc.invoice_id
54 FROM fv_assign_reason_codes farc
55 WHERE farc.set_of_books_id = x_set_of_books_id
56 AND org_id = x_org_id
57 AND farc.checkrun_name is not null -- Bug 5037297
58 AND entry_source = 'INTEREST')
59 OR
60 (air.original_invoice_id IN
61 (SELECT farc.invoice_id
62 FROM fv_assign_reason_codes farc
63 WHERE farc.set_of_books_id = x_set_of_books_id
64 AND org_id = x_org_id
65 AND entry_source = 'INTEREST'
66 AND air.checkrun_name <> farc.checkrun_name)))
67 AND api.org_id = x_org_id
68 AND air.original_invoice_id = api.invoice_id
69 AND fvt.term_id = api.terms_id
70 AND fvt.terms_type = 'PROMPT PAY';
71 l_module_name varchar2(200) := g_module_name || 'interest_reason_codes';
72 l_errbuf varchar2(300);
73 org_id_tab mo_global.OrgIdTab;
74 begin
75
76 org_id_tab := mo_global.get_ou_tab;
77
78 FOR i IN 1 .. org_id_tab.count LOOP
79 set_org(org_id_tab(i));
80 x_org_id := org_id_tab(i);
81
82 FOR reason_code_rec IN reason_codes_cur LOOP
83
84 UPDATE fv_assign_reason_codes
85 SET checkrun_name = reason_code_rec.checkrun_name,
86 entry_mode = 'SYSTEM',
87 last_update_date = SYSDATE,
88 last_updated_by = FND_GLOBAL.USER_ID,
89 last_update_login = FND_GLOBAL.LOGIN_ID
90 WHERE invoice_id = reason_code_rec.original_invoice_id
91 AND org_id = x_org_id
92 AND set_of_books_id = x_set_of_books_id
93 AND entry_source = 'INTEREST';
94
95 IF (SQL%ROWCOUNT = 0) THEN
96
97
98 INSERT INTO fv_assign_reason_codes
99 (invoice_id,
100 org_id,
101 set_of_books_id,
102 entry_mode,
103 entry_source,
104 last_update_date,
105 last_updated_by,
106 created_by,
107 creation_date,
108 checkrun_name,
109 last_update_login)
110 VALUES
111 (reason_code_rec.original_invoice_id,
112 x_org_id,
113 x_set_of_books_id,
114 'SYSTEM',
115 'INTEREST',
116 SYSDATE,
117 FND_GLOBAL.USER_ID,
118 FND_GLOBAL.USER_ID,
119 SYSDATE,
120 reason_code_rec.checkrun_name,
121 FND_GLOBAL.LOGIN_ID);
122
123 END IF;
124 END LOOP;
125 END LOOP;
126 COMMIT;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 null;
130 WHEN OTHERS THEN
131 l_errbuf := sqlerrm;
132 IF reason_codes_cur%ISOPEN THEN
133 close reason_codes_cur;
134 END IF;
135 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.final_exception','ERROR = '||l_errbuf);
137 END IF;
138 err_message := 'FV_ASSIGN_REASON_CODES_PKG.Interest_Reason_Codes '||
139 sqlerrm;
140 fnd_message.set_name('FV','FV_RC_QUICK_PAY');
141 fnd_message.set_token('MSG',err_message);
142 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception');
144 END IF;
145 app_exception.raise_exception;
146
147 END interest_reason_codes;
148 /**********************************************************************/
149 PROCEDURE get_quick_payments IS
150
151 -- Select all quick payments which are not on
152 -- fv_assign_reason_codes with an entry_source of 'EBD'
153 -- NOTE: there may be ones of type 'INTEREST' automatically
154 -- loaded, this picks up the 'EBD' side.
155
156 CURSOR c_quick_payments IS
157
158 SELECT /*+ USE_MERGE(api) */ api.invoice_id, apc.checkrun_name
159 FROM fv_terms_types ftt,
160 ap_terms apt,
161 ap_invoices api,
162 ap_checks apc,
163 ap_invoice_payments app
164 WHERE app.set_of_books_id = x_set_of_books_id
165 and api.org_id = x_org_id
166 and app.discount_lost > 0
167 and api.invoice_id = app.invoice_id
168 and apc.check_id = app.check_id
169 and apc.checkrun_name like '%Quick Payment%'
170 AND apt.term_id = api.terms_id
171 AND ftt.term_id = apt.term_id
172 AND ftt.terms_type = 'PROMPT PAY'
173 AND apc.void_date is null;
174 l_module_name varchar2(200) := g_module_name || 'get_quick_payment';
175 l_errbuf varchar2(300);
176 org_id_tab MO_GLOBAL.orgidtab;
177 BEGIN
178 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
179 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN');
180 END IF;
181
182 org_id_tab := mo_global.get_ou_tab;
183
184
185 FOR i IN 1 .. org_id_tab.count LOOP
186 set_org(org_id_tab(i));
187 x_org_id := org_id_tab(i);
188 -- Check to see if the row exists already. If so, update, otherwise insert.
189
190 FOR v_quick_payments IN c_quick_payments
191 LOOP
192
193 select count (*)
194 into v_count
195 from fv_assign_reason_codes fvr
196 where fvr.invoice_id = v_quick_payments.invoice_id
197 and org_id = x_org_id
198 and fvr.entry_source = 'EBD'
199 and fvr.set_of_books_id = x_set_of_books_id;
200
201 If v_count > 0 then
202
203 UPDATE fv_assign_reason_codes
204 SET Checkrun_name = v_quick_payments.checkrun_name,
205 Entry_mode = 'SYSTEM',
206 Last_Update_Date = SYSDATE,
207 Last_Updated_By = FND_GLOBAL.USER_ID,
208 Last_Update_Login = FND_GLOBAL.LOGIN_ID
209 where invoice_id = v_quick_payments.invoice_id
210 and set_of_books_id = x_set_of_books_id
211 and org_id = x_org_id
212 and entry_source = 'EBD';
213
214 elsif v_count = 0 then
215
216 INSERT into fv_assign_reason_codes
217 (invoice_id, entry_source, set_of_books_id,
218 /*-- Version 1.1 RCW.--------*/
219 org_id,
220 /*-- end 1.1 RCW -----------*/
221 entry_mode, last_update_date,
222 last_updated_by, created_by, creation_date, checkrun_name, last_update_login)
223 VALUES
224 (v_quick_payments.invoice_id, 'EBD', x_set_of_books_id,
225 /*-- Version 1.1 RCW.-------*/
226 x_org_id,
227 /*-- end 1.1 RCW ----------*/
228 'SYSTEM', sysdate,
229 fnd_global.user_id, fnd_global.user_id, sysdate,
230 v_quick_payments.checkrun_name, fnd_global.login_id);
231
232 end if;
233
234 END LOOP;
235 END LOOP;
236 commit;
237
238 --cursor exception
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 null;
242 WHEN OTHERS THEN
243 l_errbuf := sqlerrm;
244 IF c_quick_payments%ISOPEN THEN
245 close c_quick_payments;
246 END IF;
247 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.final_exception','ERROR = '||l_errbuf);
249 END IF;
250 err_message := 'FV_ASSIGN_REASON_CODES_PKG.GET_QUICK_PAYMENTS '||sqlerrm;
251 fnd_message.set_name('FV','FV_RC_QUICK_PAY');
252 fnd_message.set_token('MSG',err_message);
253 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
254 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception');
255 END IF;
256 app_exception.raise_exception;
257
258 END get_quick_payments;
259
260
261 END fv_assign_reason_codes_pkg;