1 PACKAGE BODY IEX_TRX_VIEW_PKG AS
2 /* $Header: iexttvwb.pls 120.9 2006/02/27 21:46:13 jypark ship $ */
3
4 PG_DEBUG NUMBER(2);
5
6
7 FUNCTION is_paid(p_payment_schedule_id NUMBER) RETURN NUMBER
8 IS
9 --Begin - schekuri - Date 13-Apr-2005 - Bug#4113120
10 --modified the query to reduce the Disk reads and Query reads
11 cursor c_pay IS
12 SELECT '1'
13 FROM ar_cash_receipts acr,
14 ar_receivable_applications ara
15 WHERE ara.applied_payment_schedule_id = p_payment_schedule_id
16 AND ara.cash_receipt_id = acr.cash_receipt_id
17 AND nvl(acr.confirmed_flag, 'Y') = 'Y'
18 AND acr.reversal_date is null
19 AND EXISTS
20 (SELECT '1'
21 FROM ar_cash_receipt_history acrh
22 WHERE acr.cash_receipt_id = acrh.cash_receipt_id
23 AND ACRH.STATUS NOT IN (DECODE (ACRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED', 'N', ' '), 'REVERSED')
24 AND ACRH.CURRENT_RECORD_FLAG = 'Y');
25 /*cursor c_pay IS
26 SELECT '1'
27 FROM ar_cash_receipts acr,
28 ar_receivable_applications ara,
29 ar_cash_receipt_history acrh
30 WHERE ara.applied_payment_schedule_id = p_payment_schedule_id
31 AND ara.cash_receipt_id = acr.cash_receipt_id
32 AND acr.cash_receipt_id = acrh.cash_receipt_id
33 AND nvl(acr.confirmed_flag, 'Y') = 'Y'
34 AND acr.reversal_date is null
35 AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED',
36 'N', ' '), 'REVERSED')
37 AND acrh.current_record_flag = 'Y';*/
38
39 --End - schekuri - Date 13-Apr-2005 - Bug#4113120
40
41 l_dummy VARCHAR(1);
42 l_status NUMBER;
43 BEGIN
44 open c_pay;
45 fetch c_pay into l_dummy;
46 if c_pay%found then
47 l_status := 1;
48 else
49 l_status := 0;
50 END if;
51 close c_pay;
52
53 RETURN l_status;
54 END;
55 FUNCTION is_promised(p_delinquency_id NUMBER) RETURN NUMBER
56 IS
57 cursor c_pro IS
58 select '1'
59 from iex_promISe_details
60 where delinquency_id = p_delinquency_id
61 and status = 'COLLECTABLE';
62 l_dummy VARCHAR(1);
63 l_status NUMBER;
64 BEGIN
65 open c_pro;
66 fetch c_pro into l_dummy;
67 if c_pro%found then
68 l_status := 1;
69 else
70 l_status := 0;
71 END if;
72 close c_pro;
73
74 RETURN l_status;
75 END;
76 FUNCTION get_sales_order(p_customer_trx_id NUMBER) RETURN VARCHAR2
77 IS
78 cursor c_so IS
79 select sales_order
80 from ra_customer_trx_lines
81 where customer_trx_id = p_customer_trx_id
82 -- Begin fix bug #5012865-JYPARK-02/01/2006-getting line type to get sales_order
83 and line_type = 'LINE'
84 -- End fix bug #5012865-JYPARK-02/01/2006-getting line type to get sales_order
85 and line_NUMBER = 1;
86 l_sales_order VARCHAR2(50);
87 BEGIN
88 open c_so;
89 fetch c_so into l_sales_order;
90 close c_so;
91 RETURN l_sales_order;
92 END;
93
94 FUNCTION get_score(p_payment_schedule_id NUMBER) RETURN NUMBER
95 IS
96 CURSOR c_score IS
97 SELECT a.score_value
98 FROM iex_score_histories a
99 WHERE a.creation_date =
100 (SELECT MAX(creation_date)
101 FROM iex_Score_histories
102 WHERE score_object_code = 'IEX_INVOICES'
103 AND score_object_id = p_payment_schedule_id)
104 AND a.score_object_code = 'IEX_INVOICES'
105 AND a.score_object_id = p_payment_schedule_id;
106 l_score NUMBER;
107 BEGIN
108 OPEN c_score;
109 FETCH c_score INTO l_score;
110 CLOSE c_score;
111 RETURN l_score;
112 END get_score;
113 FUNCTION get_strategy_name(p_delinquency_id NUMBER) RETURN VARCHAR2
114 IS
115 CURSOR c_str IS
116 SELECT str.strategy_id, str.strategy_template_id, str_temp.strategy_name
117 FROM iex_strategies str, iex_strategy_templates_vl str_temp
118 WHERE str.delinquency_id = p_delinquency_id
119 AND str.strategy_template_id = str_temp.strategy_temp_id
120 AND str_temp.category_type = 'DELINQUENT';
121 l_strategy_row c_str%rowtype;
122 BEGIN
123 OPEN c_str;
124 FETCH c_str INTO l_strategy_row;
125 CLOSE c_str;
126 return l_strategy_row.strategy_name;
127 END get_strategy_name;
128
129
130 -- clchang added 11/11/2002 for IEX_DUNNINGS_ACCT_BALI_V
131 FUNCTION get_party_id(p_account_id NUMBER) RETURN NUMBER
132 IS
133 cursor c_party (IN_ID number) is
134 select del.party_cust_id
135 --from iex_delinquencies_all del
136 from iex_delinquencies del
137 where del.cust_account_id = in_id;
138 l_party number;
139 BEGIN
140 IF PG_DEBUG < 10 THEN
141 iex_debug_pub.LogMessage ('IEXTTVWB:get_party_id');
142 END IF;
143 IF PG_DEBUG < 10 THEN
144 iex_debug_pub.LogMessage ('get_party_id: ' || 'IEXTTVWB:account_id='||p_account_id);
145 END IF;
146 open c_party(p_account_id);
147 fetch c_party into l_party;
148 if c_party%found then
149 IF PG_DEBUG < 10 THEN
150 iex_debug_pub.LogMessage ('get_party_id: ' || 'IEXTTVWB:party='||l_party);
151 END IF;
152 else
153 IF PG_DEBUG < 10 THEN
154 iex_debug_pub.LogMessage ('get_party_id: ' || 'IEXTTVWB:notfound');
155 END IF;
156 l_party := 0;
157 END if;
158 close c_party;
159
160 RETURN l_party;
161
162 END get_party_id;
163
164 PROCEDURE post_query_trx(p_trx_tab IN OUT NOCOPY postQueryTabType, p_type IN VARCHAR2)
165 IS
166 --Begin - schekuri - Date 13-Apr-2005 - Bug#4113120
167 --modified the query to reduce the Disk reads and Query reads
168 cursor c_pay(p_payment_schedule_id NUMBER) IS
169 SELECT '1'
170 FROM ar_cash_receipts acr,
171 ar_receivable_applications ara
172 WHERE ara.applied_payment_schedule_id = p_payment_schedule_id
173 AND ara.cash_receipt_id = acr.cash_receipt_id
174 AND nvl(acr.confirmed_flag, 'Y') = 'Y'
175 AND acr.reversal_date is null
176 -- Begin-fix bug #4572737-JYPARK-08/30/2005-exclude unapplied receipt
177 AND ara.display = 'Y'
178 -- End-fix bug #4572737-JYPARK-08/30/2005-exclude unapplied receipt
179 AND EXISTS
180 (SELECT '1'
181 FROM ar_cash_receipt_history acrh
182 WHERE acr.cash_receipt_id = acrh.cash_receipt_id
183 AND ACRH.STATUS NOT IN (DECODE (ACRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED', 'N', ' '), 'REVERSED')
184 AND ACRH.CURRENT_RECORD_FLAG = 'Y');
185
186 /*cursor c_pay(p_payment_schedule_id NUMBER) IS
187 SELECT '1'
188 FROM ar_cash_receipts acr,
189 ar_receivable_applications ara,
190 ar_cash_receipt_history acrh
191 WHERE ara.applied_payment_schedule_id = p_payment_schedule_id
192 AND ara.cash_receipt_id = acr.cash_receipt_id
193 AND acr.cash_receipt_id = acrh.cash_receipt_id
194 AND nvl(acr.confirmed_flag, 'Y') = 'Y'
195 AND acr.reversal_date is null
196 AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED',
197 'N', ' '), 'REVERSED')
198 AND acrh.current_record_flag = 'Y';*/
199
200 --End - schekuri - Date 13-Apr-2005 - Bug#4113120
201
202 cursor c_pro(p_delinquency_id NUMBER) IS
203 select '1'
204 from iex_promise_details
205 where delinquency_id = p_delinquency_id
206 and status = 'COLLECTABLE';
207
208 cursor c_so(p_customer_trx_id NUMBER) IS
209 select sales_order
210 from ra_customer_trx_lines
211 where customer_trx_id = p_customer_trx_id
212 -- Begin fix bug #5012865-JYPARK-02/01/2006-getting line type to get sales_order
213 and line_type = 'LINE'
214 -- End fix bug #5012865-JYPARK-02/01/2006-getting line type to get sales_order
215 and line_NUMBER = 1;
216
217 CURSOR c_str(p_delinquency_id NUMBER) IS
218 SELECT str_temp.strategy_name
219 FROM iex_strategies str, iex_strategy_templates_vl str_temp
220 WHERE str.delinquency_id = p_delinquency_id
221 AND str.strategy_template_id = str_temp.strategy_temp_id
222 AND str_temp.category_type = 'DELINQUENT'
223 AND str.status_code IN ('OPEN', 'ONHOLD');
224
225 CURSOR c_score(p_payment_schedule_id NUMBER) IS
226 SELECT a.score_value
227 FROM iex_score_histories a
228 WHERE a.creation_date =
229 (SELECT MAX(creation_date)
230 FROM iex_Score_histories
231 WHERE score_object_code = 'IEX_INVOICES'
232 AND score_object_id = p_payment_schedule_id)
233 AND a.score_object_code = 'IEX_INVOICES'
234 AND a.score_object_id = p_payment_schedule_id;
235
236 l_dummy VARCHAR(1);
237 l_status NUMBER;
238 BEGIN
239 FOR i IN p_trx_tab.first..p_trx_tab.last LOOP
240 open c_pay(p_trx_tab(i).payment_schedule_id);
241 fetch c_pay into l_dummy;
242 if c_pay%found then
243 -- p_trx_tab(i).paid_flag := 'jtfgtrue.gif';
244 p_trx_tab(i).paid_flag := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO', 'Y');
245 else
246 -- p_trx_tab(i).paid_flag := 'jtfgnull.gif';
247 p_trx_tab(i).paid_flag := '';
248 END if;
249 close c_pay;
250
251 open c_pro(p_trx_tab(i).delinquency_id);
252 fetch c_pro into l_dummy;
253 if c_pro%found then
254 -- p_trx_tab(i).promised_flag := 'jtfgtrue.gif';
255 p_trx_tab(i).promised_flag := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO', 'Y');
256 else
257 -- p_trx_tab(i).promised_flag := 'jtfgnull.gif';
258 p_trx_tab(i).promised_flag := '';
259 END if;
260 close c_pro;
261
262 open c_so(p_trx_tab(i).customer_trx_id);
263 fetch c_so into p_trx_tab(i).sales_order;
264 close c_so;
265
266 OPEN c_score(p_trx_tab(i).payment_schedule_id);
267 FETCH c_score INTO p_trx_tab(i).trx_score;
268 CLOSE c_score;
269 END LOOP;
270
271 END post_query_trx;
272 BEGIN
273 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
274 END;
[Home] [Help]