1 PACKAGE BODY IGI_STP_NET_DOC_PKG as
2 -- $Header: igistpbb.pls 120.4.12000000.3 2007/09/25 08:49:05 gkumares ship $
3
4
5
6 PROCEDURE Available_Docs(x_type VARCHAR2,
7 x_param VARCHAR2,
8 x_ap_trx_min VARCHAR2,
9 x_ap_trx_max VARCHAR2,
10 x_ar_trx_min VARCHAR2,
11 x_ar_trx_max VARCHAR2,
12 --x_ref_min VARCHAR2,
13 --x_ref_max VARCHAR2,
14 x_customer_id number,
15 x_vendor_id number,
16 x_currency_code VARCHAR2) IS
17
18 x_user_id NUMBER := fnd_global.user_id;
19 v_ap_trx_min VARCHAR2(30);
20 v_ap_trx_max VARCHAR2(30);
21 v_ar_trx_min VARCHAR2(30);
22 v_ar_trx_max VARCHAR2(30);
23 --v_ref_min VARCHAR2(30);
24 -- v_ref_max VARCHAR2(30);
25
26 /* If the user has left the reference fields blank we assume they are using min/max, */
27
28 CURSOR trx_min (p_stp_id in number, p_application in varchar2) IS
29 SELECT min(trx_number)
30 from igi_stp_candidates
31 where stp_id = p_stp_id
32 and application = p_application
33 and user_id = x_user_id
34 and currency_code = x_currency_code ;
35
36 CURSOR trx_max(p_stp_id in number, p_application in varchar2) IS
37 SELECT max(trx_number)
38 from igi_stp_candidates
39 where stp_id = p_stp_id
40 and application = p_application
41 and user_id = x_user_id
42 and currency_code = x_currency_code;
43
44 /* CURSOR ref_min IS
45 SELECT min(a.reference)
46 from igi_stp_candidates a
47 where a.stp_id = x_vendor_id
48 and a.application = 'AP'
49 and a.user_id = x_user_id
50 and a.currency_code = x_currency_code
51 and a.reference in ( select b.reference from igi_stp_candidates b
52 where b.stp_id = x_customer_id
53 and b.application = 'AR'
54 and b.user_id = x_user_id
55 and b.currency_code = x_currency_code);
56
57 CURSOR ref_max IS
58 SELECT max(a.reference)
59 from igi_stp_candidates a
60 where a.stp_id = x_vendor_id
61 and a.application = 'AP'
62 and a.user_id = x_user_id
63 and a.currency_code = x_currency_code
64 and a.reference in ( select b.reference from igi_stp_candidates b
65 where b.stp_id = x_customer_id
66 and b.application = 'AR'
67 and b.user_id = x_user_id
68 and b.currency_code = x_currency_code);
69
70 -- This does the match of Reference
71
72 CURSOR matched_ref( p_ref_min in varchar2
73 ,p_ref_max in varchar2
74 ,l_ref_min in varchar2
75 ,l_ref_max in varchar2) IS
76 SELECT distinct p.reference
77 FROM igi_stp_candidates p
78 WHERE p.application = 'AP'
79 AND p.user_id = x_user_id
80 AND p.stp_id = x_vendor_id
81 AND p.currency_code = x_currency_code
82 AND p.reference >= nvl(p_ref_min, l_ref_min)
83 AND p.reference <= nvl(p_ref_max, l_ref_max)
84 AND p.reference IN (SELECT distinct r.reference
85 FROM igi_stp_candidates r
86 WHERE application = 'AR'
87 AND r.user_id = x_user_id
88 AND r.stp_id = x_customer_id
89 AND r.currency_code = x_currency_code
90 AND r.reference >= nvl(p_ref_min, l_ref_min)
91 AND r.reference <= nvl(p_ref_max, l_ref_max)); */
92 x_counter NUMBER := 1;
93 x_commit_cycle VARCHAR2(10) := fnd_profile.value('IGI_STP_COMMIT_CYCLE');
94
95
96
97 BEGIN
98
99 /* Depending on the Netting Type only certain documents are selected either AP and AR, AP or AR */
100
101 UPDATE igi_stp_candidates
102 SET process_flag = 'R'
103 WHERE user_id = x_user_id
104 and stp_id in (x_customer_id, x_vendor_id)
105 and currency_code = x_currency_code;
106 --shsaxena for bug 2713715
107 --and process_flag <> 'S';
108 -- ssemwal for bug 2437020
109 --shsaxena for bug 2713715
110 /* IF x_type IN ('1','2') THEN
111
112 If we are matching we need to restrict the available document
113 IF x_param = 'T' THEN
114 OPEN trx_min(x_vendor_id, 'AP');
115 FETCH trx_min INTO v_ap_trx_min;
116 CLOSE trx_min;
117
118 OPEN trx_max(x_vendor_id, 'AP');
119 FETCH trx_max INTO v_ap_trx_max;
120 CLOSE trx_max;
121
122 UPDATE igi_stp_candidates
123 SET process_flag = 'A'
124 WHERE user_id = x_user_id
125 and stp_id = x_vendor_id
126 and currency_code = x_currency_code
127 and application = 'AP'
128 and trx_type = 'STANDARD'
129 and trx_number >= nvl(x_ap_trx_min, v_ap_trx_min)
130 and trx_number <= nvl(x_ap_trx_max, v_ap_trx_max)
131 and process_flag = 'R';
132
133 OPEN trx_min(x_customer_id, 'AR');
134 FETCH trx_min INTO v_ar_trx_min;
135 CLOSE trx_min;
136
137 OPEN trx_max(x_customer_id, 'AR');
138 FETCH trx_max INTO v_ar_trx_max;
139 CLOSE trx_max;
140
141 UPDATE igi_stp_candidates
142 SET process_flag = 'A'
143 WHERE user_id = x_user_id
144 and stp_id = x_customer_id
145 and currency_code = x_currency_code
146 and application = 'AR'
147 and trx_type = 'INV'
148 and trx_number >= nvl(x_ar_trx_min, v_ar_trx_min)
149 and trx_number <= nvl(x_ar_trx_max, v_ar_trx_max)
150 and process_flag = 'R';
151 ELSIF x_param = 'R' THEN
152 OPEN ref_min;
153 FETCH ref_min INTO v_ref_min;
154 CLOSE ref_min;
155
156 OPEN ref_max;
157 FETCH ref_max INTO v_ref_max;
158 CLOSE ref_max;
159
160 for rec_match in matched_ref(x_ref_min, x_ref_max,
161 v_ref_min, v_ref_max) loop
162 UPDATE igi_stp_candidates
163 SET process_flag = 'A'
164 -- Bug 1322996
165 -- SET process_flag = 'S'
166 -- , package_num = x_counter
167 WHERE user_id = x_user_id
168 and currency_code = x_currency_code
169 and stp_id in ( x_customer_id, x_vendor_id)
170 and trx_type in ('STANDARD', 'INV')
171 and reference = rec_match.reference
172 and process_flag = 'R';
173 -- x_counter := x_counter + 1;
174 end loop;
175 END IF;*/
176 IF x_type IN ('3','5','6') THEN
177 IF x_param = 'T' THEN
178 OPEN trx_min(x_vendor_id, 'AP');
179 FETCH trx_min INTO v_ap_trx_min;
180 CLOSE trx_min;
181
182 OPEN trx_max(x_vendor_id, 'AP');
183 FETCH trx_max INTO v_ap_trx_max;
184 CLOSE trx_max;
185
186 UPDATE igi_stp_candidates
187 SET process_flag = 'A'
188 WHERE user_id = x_user_id
189 and stp_id = x_vendor_id
190 and currency_code = x_currency_code
191 and application = 'AP'
192 and trx_type = decode(x_type, 6,'CREDIT', 'STANDARD')
193 and trx_number >= nvl(x_ap_trx_min, v_ap_trx_min)
194 and trx_number <= nvl(x_ap_trx_max, v_ap_trx_max)
195 and process_flag = 'R';
196 END IF;
197 ELSIF x_type = '4' THEN
198 IF x_param = 'T' THEN
199 OPEN trx_min(x_customer_id, 'AR');
200 FETCH trx_min INTO v_ar_trx_min;
201 CLOSE trx_min;
202
203 OPEN trx_max(x_customer_id, 'AR');
204 FETCH trx_max INTO v_ar_trx_max;
205 CLOSE trx_max;
206
207 UPDATE igi_stp_candidates
208 SET process_flag = 'A'
209 WHERE user_id = x_user_id
210 and stp_id = x_customer_id
211 and currency_code = x_currency_code
212 and application = 'AR'
213 and trx_type = 'CM'
214 and trx_number >= nvl(x_ar_trx_min, v_ar_trx_min)
215 and trx_number <= nvl(x_ar_trx_max, v_ar_trx_max)
216 and process_flag = 'R';
217 END IF;
218 END IF;
219 COMMIT;
220 END Available_Docs;
221
222 PROCEDURE Update_Candidates (x_type VARCHAR2,
223 x_batch_id NUMBER,
224 x_package_id NUMBER,
225 x_org_id number) is
226
227 x_user_id NUMBER := fnd_global.user_id;
228 BEGIN
229
230
231
232 UPDATE igi_stp_candidates_all
233 SET batch_id = x_batch_id
234 , netting_trx_type_id = x_type
235 , package_id = x_package_id
236 WHERE user_id = x_user_id
237 AND process_flag = 'S'
238 AND batch_id = -1
239 AND org_id = x_org_id;
240 --shsaxena for bug 2713715
241 -- AND rowid = x_row_id;
242 -- ssemwal for bug 243702
243 --shsaxena for bug 2713715
244
245 COMMIT;
246
247 END Update_Candidates;
248
249 END IGI_STP_NET_DOC_PKG;
[Home] [Help]