[Home] [Help]
PACKAGE BODY: APPS.ARP_PAY_REL_CUST_PKG
Source
1 PACKAGE BODY ARP_PAY_REL_CUST_PKG AS
2 /* $Header: ARPRCPKB.pls 120.2 2011/03/17 18:03:48 mraymond noship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7
8 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
10 G_BUILD_ID number;
11
12 /*========================================================================
13 | Prototype Declarations Procedures
14 *=======================================================================*/
15
16 /*========================================================================
17 | Prototype Declarations Functions
18 *=======================================================================*/
19
20 /*-------------------------------------------------------------------------+
21 | PUBLIC PROCEDURE |
22 | build_for_automatch() |
23 | |
24 | DESCRIPTION |
25 | This procedure populates the ar_potential_customers_gt |
26 | table for each worker of the receipt automatch feature.
27 | It uses the list of receipts in ar_cash_remit_refs_interim and
28 | collects all related customers for
29 | the intended receipts.
30 | |
31 | Note that this routine is currently called by each worker.
32 |
33 | REQUIRES |
34 | P_WORKER_NUMBER
35 | |
36 | RETURNS |
37 | |
38 | NOTES |
39 | |
40 | EXAMPLE |
41 | |
42 | MODIFICATION HISTORY |
43 | 18-JAN-2011 M Raymond Bug 9919720 - Revised to completion.
44 |
45 +-------------------------------------------------------------------------*/
46 PROCEDURE build_for_automatch(p_worker_number IN NUMBER DEFAULT NULL) IS
47
48 /* This cursor detects presence of the two known relationship groups
49 PAY_ANY and PAY_TOP_DOWN. We use the presence of each to include
50 a UNION clause for that type in the dynamic sql. */
51 cursor c_rel_grp IS
52 SELECT distinct ca.class_code class
53 FROM hz_code_assignments ca
54 ,hz_relationship_types rt
55 WHERE ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
56 AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
57 AND ca.status = 'A'
58 AND ca.owner_table_id = rt.relationship_type_id
59 AND rt.object_type = 'ORGANIZATION'
60 AND rt.subject_type = 'ORGANIZATION'
61 AND ca.class_code like '%AR_PAY%';
62
63 cur_ins INTEGER;
64 l_pay_any VARCHAR2(1) := 'N';
65 l_pay_top_down VARCHAR2(1) := 'N';
66 l_ins_sql CLOB;
67 l_rows_inserted NUMBER;
68 BEGIN
69 IF PG_DEBUG in ('Y', 'C') THEN
70 arp_debug.debug('arp_pay_rel_cust_pkg.build_for_automatch()+');
71 arp_debug.debug(' p_worker_number:' || p_worker_number);
72 END IF;
73
77 G_PAY_UNRELATED := arp_standard.sysparm.pay_unrelated_invoices_flag;
74 /* Determine if this list has already been built, and if not
75 build it now */
76 G_BUILD_ID := p_worker_number;
78
79 IF G_PAY_UNRELATED = 'Y'
80 THEN
81 /* relationships don't matter */
82 NULL;
83 ELSE
84 /* relationships matter, build list */
85
86 /* Detect relationship groups, omit or include sql based
87 on presence */
88 FOR l_rel IN c_rel_grp LOOP
89
90 IF l_rel.class = 'PARTY_REL_GRP_AR_PAY_ANY'
91 THEN
92 l_pay_any := 'Y';
93 ELSIF l_rel.class = 'PARTY_REL_GRP_AR_PAY_TOP_DOWN'
94 THEN
95 l_pay_top_down := 'Y';
96 END IF;
97 END LOOP;
98
99 IF PG_DEBUG in ('Y','C')
100 THEN
101 arp_debug.debug('l_pay_any = ' || l_pay_any);
102 arp_debug.debug('l_pay_top_down = ' || l_pay_top_down);
103 END IF;
104
105 /* relationships do matter - build list */
106
107 /* l_ins_sql is the full insert with all of the UNION clauses
108 appended */
109 l_ins_sql :=
110 'INSERT INTO ar_potential_customers_gt' || CRLF ||
111 ' (cust_account_id, related_cust_account_id)' || CRLF ||
112 'SELECT distinct customer_id, customer_id' || CRLF ||
113 'FROM ar_cash_remit_refs_interim' || CRLF ||
114 'WHERE customer_id IS NOT NULL' || CRLF ||
115 'AND worker_number = :worker_number1' || CRLF ||
116 'UNION ' || CRLF ||
117 'SELECT DISTINCT rel.cust_account_id, rel.related_cust_account_id ' || CRLF ||
118 'FROM ar_cash_remit_refs_interim int, ' || CRLF ||
119 ' hz_cust_acct_relate_all rel ' || CRLF ||
120 'WHERE int.customer_id = rel.cust_account_id ' || CRLF ||
121 'AND rel.bill_to_flag = ''Y'' ' || CRLF ||
122 'AND rel.status = ''A'' ' || CRLF ||
123 'AND int.customer_id IS NOT NULL ' || CRLF ||
124 'AND int.worker_number = :worker_number2' || CRLF;
125
126 IF l_pay_any = 'Y'
127 THEN
128 l_ins_sql := l_ins_sql ||
129 'UNION ' || CRLF ||
130 'SELECT /*+ ORDERED */ ' || CRLF ||
131 ' DISTINCT acc.cust_account_id, relacc.cust_account_id' || CRLF ||
132 'FROM ar_cash_remit_refs_interim int,' || CRLF ||
133 ' ar_cash_receipts cr, ' || CRLF ||
134 ' hz_cust_accounts acc,' || CRLF ||
135 ' hz_hierarchy_nodes gettop,' || CRLF ||
136 ' hz_hierarchy_nodes top,' || CRLF ||
137 ' hz_hierarchy_nodes hn, ' || CRLF ||
138 ' hz_cust_accounts relacc ' || CRLF ||
139 'WHERE int.cash_receipt_id = cr.cash_receipt_id ' || CRLF ||
140 'AND cr.pay_from_customer = acc.cust_account_id ' || CRLF ||
141 'AND acc.party_id = gettop.child_id ' || CRLF ||
142 'AND cr.receipt_date >= hd.effective_start_date' || CRLF ||
143 'AND cr.receipt_date >= top.effective_start_date' || CRLF ||
144 'AND cr.receipt_date >= gettop.effective_start_date' || CRLF ||
145 'AND cr.receipt_date <= hn.effective_end_date' || CRLF ||
146 'AND cr.receipt_date <= top.effective_end_date' || CRLF ||
147 'AND cr.receipt_date <= gettop.effective_end_date' || CRLF ||
148 'AND int.customer_id IS NOT NULL' || CRLF ||
149 'AND int.worker_number = :worker_number3' || CRLF ||
150 'AND top.hierarchy_type = gettop.hierarchy_type' || CRLF ||
151 'AND top.parent_id = gettop.parent_id' || CRLF ||
152 'AND gettop.parent_table_name = ''HZ_PARTIES''' || CRLF ||
153 'AND gettop.parent_object_type = ''ORGANIZATION''' || CRLF ||
154 'AND gettop.child_table_name = ''HZ_PARTIES''' || CRLF ||
155 'AND gettop.child_object_type = ''ORGANIZATION''' || CRLF ||
156 'AND top.parent_table_name = ''HZ_PARTIES''' || CRLF ||
157 'AND top.parent_object_type = ''ORGANIZATION''' || CRLF ||
158 'AND top.top_parent_flag = ''Y''' || CRLF ||
159 'AND hn.hierarchy_type = top.hierarchy_type' || CRLF ||
160 'AND hn.parent_id = top.parent_id' || CRLF ||
161 'AND hn.parent_table_name = ''HZ_PARTIES''' || CRLF ||
162 'AND hn.parent_object_type = ''ORGANIZATION''' || CRLF ||
163 'AND hn.child_id = relacc.party_id' || CRLF ||
164 'AND hn.child_table_name = ''HZ_PARTIES''' || CRLF ||
165 'AND hn.child_object_type = ''ORGANIZATION''' || CRLF ||
166 'AND EXISTS ' || CRLF ||
167 ' (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
168 ' FROM hz_code_assignments ca' || CRLF ||
169 ' ,hz_relationship_types rt ' || CRLF ||
170 ' WHERE ca.class_category = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
171 ' AND ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
172 ' AND ca.class_code = ''PARTY_REL_GRP_AR_PAY_ANY'''|| CRLF ||
173 ' AND ca.status = ''A'''|| CRLF ||
174 ' AND ca.owner_table_id = rt.relationship_type_id' || CRLF ||
175 ' AND hn.hierarchy_type = rt.relationship_type' || CRLF ||
176 ' AND rt.object_type = ''ORGANIZATION''' || CRLF ||
177 ' AND rt.subject_type = ''ORGANIZATION'')' || CRLF;
178
179 END IF;
180
181 IF l_pay_top_down = 'Y'
182 THEN
183 l_ins_sql := l_ins_sql ||
184 'UNION' || CRLF ||
188 ' ar_cash_receipts cr,' || CRLF ||
185 'SELECT /*+ ORDERED */ ' || CRLF ||
186 ' DISTINCT acc.cust_account_id, relacc.cust_account_id ' || CRLF ||
187 'FROM ar_cash_remit_refs_interim int,' || CRLF ||
189 ' hz_cust_accounts acc,' || CRLF ||
190 ' hz_hierarchy_nodes hn, ' || CRLF ||
191 ' hz_cust_accounts relacc ' || CRLF ||
192 'WHERE int.cash_receipt_id = cr.cash_receipt_id' || CRLF ||
193 'AND cr.pay_from_customer = acc.cust_account_id ' || CRLF ||
194 'AND acc.party_id = hn.parent_id ' || CRLF ||
195 'AND cr.receipt_date BETWEEN hn.effective_start_date AND ' || CRLF ||
196 ' hn.effective_end_date' || CRLF ||
197 'AND int.customer_id IS NOT NULL' || CRLF ||
198 'AND int.worker_number = :worker_number4' || CRLF ||
199 'AND hn.parent_table_name = ''HZ_PARTIES''' || CRLF ||
200 'AND hn.parent_object_type = ''ORGANIZATION''' || CRLF ||
201 'AND hn.child_id = relacc.party_id' || CRLF ||
202 'AND hn.child_table_name = ''HZ_PARTIES''' || CRLF ||
203 'AND hn.child_object_type = ''ORGANIZATION''' || CRLF ||
204 'AND EXISTS ' || CRLF ||
205 ' (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
206 ' FROM hz_code_assignments ca' || CRLF ||
207 ' ,hz_relationship_types rt ' || CRLF ||
208 ' WHERE ca.class_category = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
209 ' AND ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
210 ' AND ca.class_code = ''PARTY_REL_GRP_AR_PAY_TOP_DOWN'''|| CRLF ||
211 ' AND ca.status = ''A'''|| CRLF ||
212 ' AND ca.owner_table_id = rt.relationship_type_id' || CRLF ||
213 ' AND hn.hierarchy_type = rt.relationship_type' || CRLF ||
214 ' AND rt.object_type = ''ORGANIZATION''' || CRLF ||
215 ' AND rt.subject_type = ''ORGANIZATION'')' || CRLF;
216
217 END IF;
218
219 /* Now open, parse, execute the sql */
220 cur_ins := DBMS_SQL.OPEN_CURSOR;
221 DBMS_SQL.PARSE(cur_ins, l_ins_sql, DBMS_SQL.NATIVE);
222
223 /* binds */
224 DBMS_SQL.BIND_VARIABLE(cur_ins, ':worker_number1', p_worker_number);
225 DBMS_SQL.BIND_VARIABLE(cur_ins, ':worker_number2', p_worker_number);
226 -- third and fourth (dynamic) parts of statement
227 IF l_pay_any = 'Y'
228 THEN
229 DBMS_SQL.BIND_VARIABLE(cur_ins, ':worker_number3', p_worker_number);
230 END IF;
231
232 IF l_pay_top_down = 'Y'
233 THEN
234 DBMS_SQL.BIND_VARIABLE(cur_ins, ':worker_number4', p_worker_number);
235 END IF;
236 l_rows_inserted := DBMS_SQL.EXECUTE(cur_ins);
237 DBMS_SQL.CLOSE_CURSOR(cur_ins);
238
239 END IF; /* end pay_unrelated */
240
241 IF PG_DEBUG in ('Y', 'C') THEN
242 arp_debug.debug(' rows inserted: ' || l_rows_inserted);
243 arp_debug.debug('arp_pay_rel_cust_pkg.build_for_automatch()-');
244 END IF;
245 EXCEPTION
246 WHEN OTHERS THEN
247 IF PG_DEBUG in ('Y', 'C') THEN
248 arp_debug.debug('EXCEPTION: arp_pay_rel_cust_pkg.build_for_automatch');
249 END IF;
250 RAISE;
251 END build_for_automatch;
252
253 /*-------------------------------------------------------------------------+
254 | PUBLIC PROCEDURE |
255 | build_for_lockbox() |
256 | |
257 | DESCRIPTION |
258 | This procedure populates the ar_potential_customers_gt |
259 | table for specific lockbox runs. It uses the list of customers
260 | in ar_payments_interface and collects all related customers for
261 | the intended receipts.
262 | |
263 | REQUIRES |
264 | P_TRANSMISSION_ID, P_PAYMENT_REC_TYPE, P_PAY_UNRELATED_INVOICES |
265 | |
266 | RETURNS |
267 | |
268 | NOTES |
269 | |
270 | EXAMPLE |
271 | |
272 | MODIFICATION HISTORY |
273 | 01-DEC-2010 M Raymond Bug 10146717 - Created
274 |
275 +-------------------------------------------------------------------------*/
276 PROCEDURE build_for_lockbox(
277 p_transmission_id IN VARCHAR2,
278 p_payment_rec_type IN VARCHAR2,
279 p_pay_unrelated_invoices IN VARCHAR2)
280 IS
281
282 l_pay_any VARCHAR2(1) := 'N';
283 l_pay_top_down VARCHAR2(1) := 'N';
284 l_ins_sql CLOB;
285 l_rows_inserted NUMBER;
286
287 /* This cursor detects presence of the two known relationship groups
288 PAY_ANY and PAY_TOP_DOWN. We use the presence of each to include
289 a UNION clause for that type in the dynamic sql. */
290 cursor c_rel_grp IS
291 SELECT distinct ca.class_code class
292 FROM hz_code_assignments ca
293 ,hz_relationship_types rt
294 WHERE ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
295 AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
299 AND rt.subject_type = 'ORGANIZATION'
296 AND ca.status = 'A'
297 AND ca.owner_table_id = rt.relationship_type_id
298 AND rt.object_type = 'ORGANIZATION'
300 AND ca.class_code like '%AR_PAY%';
301
302 cur_ins INTEGER;
303
304 BEGIN
305 IF PG_DEBUG in ('Y','C') THEN
306 arp_debug.debug('arp_pay_rel_cust_pkg.build_for_lockbox()+');
307 arp_debug.debug(' pay unrelated invoices = ' ||
308 p_pay_unrelated_invoices);
309 arp_debug.debug(' payment_rec_type = ' || p_payment_rec_type);
310 arp_debug.debug(' transmission_id = ' || p_transmission_id);
311 END IF;
312
313 /* Only build the first time we are called for a given
314 transmission_id. This is called 2-3 times within lockbox
315 depending on setups, autoassociate, etc. */
316 IF NVL(G_BUILD_ID, -999) <> p_transmission_id
317 THEN
318 G_BUILD_ID := p_transmission_id;
319 G_PAY_UNRELATED := p_pay_unrelated_invoices;
320
321 IF p_pay_unrelated_invoices = 'Y'
322 THEN
323 /* relationships don't matter, do nothing */
324 NULL;
325 ELSE
326 /* Detect relationship groups, omit or include sql based
327 on presence */
328 FOR l_rel IN c_rel_grp LOOP
329
330 IF l_rel.class = 'PARTY_REL_GRP_AR_PAY_ANY'
331 THEN
332 l_pay_any := 'Y';
333 ELSIF l_rel.class = 'PARTY_REL_GRP_AR_PAY_TOP_DOWN'
334 THEN
335 l_pay_top_down := 'Y';
336 END IF;
337 END LOOP;
338
339 IF PG_DEBUG in ('Y','C')
340 THEN
341 arp_debug.debug('l_pay_any = ' || l_pay_any);
342 arp_debug.debug('l_pay_top_down = ' || l_pay_top_down);
343 END IF;
344
345 /* relationships do matter - build list */
346
347 /* l_ins_sql is the full insert with all of the UNION clauses
348 appended */
349 l_ins_sql :=
350 'INSERT INTO ar_potential_customers_gt' || CRLF ||
351 ' (cust_account_id, related_cust_account_id)' || CRLF ||
352 'SELECT distinct customer_id, customer_id' || CRLF ||
353 'FROM ar_payments_interface' || CRLF ||
354 'WHERE customer_id IS NOT NULL' || CRLF ||
355 'AND transmission_id = :transmission_id1' || CRLF ||
356 'AND record_type = :pay_rec_type1' || CRLF ||
357 'UNION ' || CRLF ||
358 'SELECT DISTINCT rel.cust_account_id, rel.related_cust_account_id ' || CRLF ||
359 'FROM ar_payments_interface int, ' || CRLF ||
360 ' hz_cust_acct_relate_all rel ' || CRLF ||
361 'WHERE int.customer_id = rel.cust_account_id ' || CRLF ||
362 'AND rel.bill_to_flag = ''Y'' ' || CRLF ||
363 'AND rel.status = ''A'' ' || CRLF ||
364 'AND int.customer_id IS NOT NULL ' || CRLF ||
365 'AND transmission_id = :transmission_id2' || CRLF ||
366 'AND record_type = :pay_rec_type2' || CRLF;
367
368 IF l_pay_any = 'Y'
369 THEN
370 l_ins_sql := l_ins_sql ||
371 'UNION ' || CRLF ||
372 'SELECT /*+ ORDERED */ ' || CRLF ||
373 ' DISTINCT acc.cust_account_id, relacc.cust_account_id' || CRLF ||
374 'FROM ar_payments_interface int,' || CRLF ||
375 ' hz_cust_accounts acc,' || CRLF ||
376 ' hz_hierarchy_nodes gettop,' || CRLF ||
377 ' hz_hierarchy_nodes top,' || CRLF ||
378 ' hz_hierarchy_nodes hn, ' || CRLF ||
379 ' hz_cust_accounts relacc ' || CRLF ||
380 'WHERE int.customer_id = acc.cust_account_id ' || CRLF ||
381 'AND acc.party_id = gettop.child_id ' || CRLF ||
382 'AND int.receipt_date >= ' || CRLF ||
383 ' GREATEST(hn.effective_start_date, ' || CRLF ||
384 ' top.effective_start_date, ' || CRLF ||
385 ' gettop.effective_start_date)' || CRLF ||
386 'AND int.receipt_date <= ' || CRLF ||
387 ' LEAST(hn.effective_end_date, ' || CRLF ||
388 ' top.effective_end_date,' || CRLF ||
389 ' gettop.effective_end_date, ' || CRLF ||
390 ' hn.effective_end_date)' || CRLF ||
391 'AND int.customer_id IS NOT NULL' || CRLF ||
392 'AND int.transmission_id = :transmission_id3' || CRLF ||
393 'AND int.record_type = :pay_rec_type3' || CRLF ||
394 'AND top.hierarchy_type = gettop.hierarchy_type' || CRLF ||
395 'AND top.parent_id = gettop.parent_id' || CRLF ||
396 'AND gettop.parent_table_name = ''HZ_PARTIES''' || CRLF ||
397 'AND gettop.parent_object_type = ''ORGANIZATION''' || CRLF ||
398 'AND gettop.child_table_name = ''HZ_PARTIES''' || CRLF ||
399 'AND gettop.child_object_type = ''ORGANIZATION''' || CRLF ||
400 'AND top.parent_table_name = ''HZ_PARTIES''' || CRLF ||
401 'AND top.parent_object_type = ''ORGANIZATION''' || CRLF ||
402 'AND top.top_parent_flag = ''Y''' || CRLF ||
403 'AND hn.hierarchy_type = top.hierarchy_type' || CRLF ||
404 'AND hn.parent_id = top.parent_id' || CRLF ||
405 'AND hn.parent_table_name = ''HZ_PARTIES''' || CRLF ||
406 'AND hn.parent_object_type = ''ORGANIZATION''' || CRLF ||
407 'AND hn.child_id = relacc.party_id' || CRLF ||
411 ' (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
408 'AND hn.child_table_name = ''HZ_PARTIES''' || CRLF ||
409 'AND hn.child_object_type = ''ORGANIZATION''' || CRLF ||
410 'AND EXISTS ' || CRLF ||
412 ' FROM hz_code_assignments ca' || CRLF ||
413 ' ,hz_relationship_types rt ' || CRLF ||
414 ' WHERE ca.class_category = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
415 ' AND ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
416 ' AND ca.class_code = ''PARTY_REL_GRP_AR_PAY_ANY'''|| CRLF ||
417 ' AND ca.status = ''A'''|| CRLF ||
418 ' AND ca.owner_table_id = rt.relationship_type_id' || CRLF ||
419 ' AND hn.hierarchy_type = rt.relationship_type' || CRLF ||
420 ' AND rt.object_type = ''ORGANIZATION''' || CRLF ||
421 ' AND rt.subject_type = ''ORGANIZATION'')' || CRLF;
422
423 END IF;
424
425 IF l_pay_top_down = 'Y'
426 THEN
427 l_ins_sql := l_ins_sql ||
428 'UNION' || CRLF ||
429 'SELECT /*+ ORDERED */ ' || CRLF ||
430 ' DISTINCT acc.cust_account_id, relacc.cust_account_id ' || CRLF ||
431 'FROM ar_payments_interface int,' || CRLF ||
432 ' hz_cust_accounts acc,' || CRLF ||
433 ' hz_hierarchy_nodes hn, ' || CRLF ||
434 ' hz_cust_accounts relacc ' || CRLF ||
435 'WHERE int.customer_id = acc.cust_account_id ' || CRLF ||
436 'AND acc.party_id = hn.parent_id ' || CRLF ||
437 'AND int.receipt_date BETWEEN hn.effective_start_date AND ' || CRLF ||
438 ' hn.effective_end_date' || CRLF ||
439 'AND int.customer_id IS NOT NULL' || CRLF ||
440 'AND int.transmission_id = :transmission_id4' || CRLF ||
441 'AND int.record_type = :pay_rec_type4' || CRLF ||
442 'AND hn.parent_table_name = ''HZ_PARTIES''' || CRLF ||
443 'AND hn.parent_object_type = ''ORGANIZATION''' || CRLF ||
444 'AND hn.child_id = relacc.party_id' || CRLF ||
445 'AND hn.child_table_name = ''HZ_PARTIES''' || CRLF ||
446 'AND hn.child_object_type = ''ORGANIZATION''' || CRLF ||
447 'AND EXISTS ' || CRLF ||
448 ' (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
449 ' FROM hz_code_assignments ca' || CRLF ||
450 ' ,hz_relationship_types rt ' || CRLF ||
451 ' WHERE ca.class_category = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
452 ' AND ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
453 ' AND ca.class_code = ''PARTY_REL_GRP_AR_PAY_TOP_DOWN'''|| CRLF ||
454 ' AND ca.status = ''A'''|| CRLF ||
455 ' AND ca.owner_table_id = rt.relationship_type_id' || CRLF ||
456 ' AND hn.hierarchy_type = rt.relationship_type' || CRLF ||
457 ' AND rt.object_type = ''ORGANIZATION''' || CRLF ||
458 ' AND rt.subject_type = ''ORGANIZATION'')' || CRLF;
459
460 END IF;
461
462 /* Now open, parse, execute the sql */
463 cur_ins := DBMS_SQL.OPEN_CURSOR;
464 DBMS_SQL.PARSE(cur_ins, l_ins_sql, DBMS_SQL.NATIVE);
465
466 /* binds */
467 DBMS_SQL.BIND_VARIABLE(cur_ins, ':transmission_id1', p_transmission_id);
468 DBMS_SQL.BIND_VARIABLE(cur_ins, ':pay_rec_type1',p_payment_rec_type);
469 DBMS_SQL.BIND_VARIABLE(cur_ins, ':transmission_id2', p_transmission_id);
470 DBMS_SQL.BIND_VARIABLE(cur_ins, ':pay_rec_type2',p_payment_rec_type);
471 -- third and fourth (dynamic) parts of statement
472 IF l_pay_any = 'Y'
473 THEN
474 DBMS_SQL.BIND_VARIABLE(cur_ins, ':transmission_id3', p_transmission_id);
475 DBMS_SQL.BIND_VARIABLE(cur_ins, ':pay_rec_type3',p_payment_rec_type);
476 END IF;
477
478 IF l_pay_top_down = 'Y'
479 THEN
480 DBMS_SQL.BIND_VARIABLE(cur_ins, ':transmission_id4', p_transmission_id);
481 DBMS_SQL.BIND_VARIABLE(cur_ins, ':pay_rec_type4',p_payment_rec_type);
482 END IF;
483 l_rows_inserted := DBMS_SQL.EXECUTE(cur_ins);
484 DBMS_SQL.CLOSE_CURSOR(cur_ins);
485 END IF;
486
487 ELSE
488 /* Relationships already built, no action required */
489 NULL;
490 END IF;
491
492 IF PG_DEBUG in ('Y','C') THEN
493 arp_debug.debug(' inserted ' || l_rows_inserted || ' customer/relationships. ');
494 arp_debug.debug('arp_pay_rel_cust_pkg.build_for_lockbox()-');
495 END IF;
496
497 END build_for_lockbox;
498
499 /*-------------------------------------------------------------------------+
500 | PUBLIC PROCEDURE |
501 | build_for_app_lov() |
502 | |
503 | DESCRIPTION |
504 | This procedure populates the ar_potential_customers_gt |
505 | table for specific customers. It takes the current receipt
506 | customer and apply_date and collects all related customers for
507 | the intended receipt.
508 |
509 | This routine is used by both receipt apply_to LOV and mass apply
510 | logic.
511 | |
512 | REQUIRES |
516 | |
513 | P_CUSTOMER_ID, P_PAY_UNRELATED_INVOICES, P_APPLY_DATE |
514 | |
515 | RETURNS |
517 | NOTES |
518 | |
519 | EXAMPLE |
520 | |
521 | MODIFICATION HISTORY |
522 | 01-FEB-2010 M Raymond Bug 11676945 - Created
523 |
524 +-------------------------------------------------------------------------*/
525 PROCEDURE build_for_app_lov(p_customer_id IN NUMBER,
526 p_pay_unrelated_invoices IN VARCHAR2,
527 p_apply_date IN DATE) IS
528
529 l_pay_any VARCHAR2(1) := 'N';
530 l_pay_top_down VARCHAR2(1) := 'N';
531 l_ins_sql CLOB;
532 l_rows_inserted NUMBER;
533
534 cursor c_rel_grp IS
535 SELECT distinct ca.class_code class
536 FROM hz_code_assignments ca
537 ,hz_relationship_types rt
538 WHERE ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
539 AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
540 AND ca.status = 'A'
541 AND ca.owner_table_id = rt.relationship_type_id
542 AND rt.object_type = 'ORGANIZATION'
543 AND rt.subject_type = 'ORGANIZATION'
544 AND ca.class_code like '%AR_PAY%';
545
546 cur_ins INTEGER;
547 l_is_cust_there VARCHAR2(20);
548 BEGIN
549 IF PG_DEBUG in ('Y','C') THEN
550 arp_debug.debug('arp_pay_rel_cust_pkg.build_for_app_lov()+');
551 arp_debug.debug(' pay unrelated invoices = ' ||
552 p_pay_unrelated_invoices);
553 arp_debug.debug(' customer_id = ' || p_customer_id);
554 arp_debug.debug(' apply_date = ' || p_apply_date);
555 END IF;
556
557 IF NVL(G_BUILD_ID, -999) <> p_customer_id
558 OR NVL(G_PAY_UNRELATED, 'X') <> p_pay_unrelated_invoices
559 THEN
560 G_BUILD_ID := p_customer_id;
561 G_PAY_UNRELATED := p_pay_unrelated_invoices;
562
563 IF G_PAY_UNRELATED = 'Y'
564 THEN
565 /* relationships don't matter */
566 NULL;
567 ELSE
568
569 /* Check to see if this customer is already in
570 the table */
571 BEGIN
572 select 'found'
573 into l_is_cust_there
574 from ar_potential_customers_gt
575 where cust_account_id = p_customer_id;
576
577 IF PG_DEBUG in ('Y','C') THEN
578 arp_debug.debug(' Customer already loaded');
579 END IF;
580 RETURN;
581 EXCEPTION
582 WHEN NO_DATA_FOUND THEN
583 /* Carry on with the load */
584 NULL;
585 END;
586
587 FOR l_rel IN c_rel_grp LOOP
588
589 IF l_rel.class = 'PARTY_REL_GRP_AR_PAY_ANY'
590 THEN
591 l_pay_any := 'Y';
592 ELSIF l_rel.class = 'PARTY_REL_GRP_AR_PAY_TOP_DOWN'
593 THEN
594 l_pay_top_down := 'Y';
595 END IF;
596 END LOOP;
597
598 IF PG_DEBUG in ('Y','C')
599 THEN
600 arp_debug.debug('l_pay_any = ' || l_pay_any);
601 arp_debug.debug('l_pay_top_down = ' || l_pay_top_down);
602 END IF;
603
604 /* relationships do matter - build list */
605
606 /* l_ins_sql is the full insert with all of the UNION
607 clauses appended */
608 l_ins_sql :=
609 'INSERT INTO ar_potential_customers_gt' || CRLF ||
610 ' (cust_account_id, related_cust_account_id)' || CRLF ||
611 'SELECT :customer_id1, :customer_id2' || CRLF ||
612 'FROM dual' || CRLF ||
613 'UNION ' || CRLF ||
614 'SELECT DISTINCT rel.cust_account_id, rel.related_cust_account_id ' || CRLF ||
615 'FROM hz_cust_acct_relate_all rel ' || CRLF ||
616 'WHERE rel.cust_account_id = :customer_id3' || CRLF ||
617 'AND rel.bill_to_flag = ''Y'' ' || CRLF ||
618 'AND rel.status = ''A'' ' || CRLF;
619
620 IF l_pay_any = 'Y'
621 THEN
622 l_ins_sql := l_ins_sql ||
623 'UNION ' || CRLF ||
624 'SELECT /*+ ORDERED */ ' || CRLF ||
625 ' DISTINCT acc.cust_account_id, relacc.cust_account_id' || CRLF ||
626 'FROM '|| CRLF ||
627 ' hz_cust_accounts acc,' || CRLF ||
628 ' hz_hierarchy_nodes gettop,' || CRLF ||
629 ' hz_hierarchy_nodes top,' || CRLF ||
630 ' hz_hierarchy_nodes hn, ' || CRLF ||
631 ' hz_cust_accounts relacc ' || CRLF ||
632 'WHERE acc.cust_account_id = :customer_id4' || CRLF ||
633 'AND acc.party_id = gettop.child_id ' || CRLF ||
634 'AND :apply_date1 >= hn.effective_start_date ' || CRLF ||
635 'AND :apply_date2 >= top.effective_start_date ' || CRLF ||
636 'AND :apply_date3 >= gettop.effective_start_date ' || CRLF ||
637 'AND :apply_date4 <= top.effective_end_date ' || CRLF ||
638 'AND :apply_date5 <= gettop.effective_end_date ' || CRLF ||
639 'AND :apply_date6 <= hn.effective_end_date ' || CRLF ||
643 'AND gettop.parent_object_type = ''ORGANIZATION''' || CRLF ||
640 'AND top.hierarchy_type = gettop.hierarchy_type' || CRLF ||
641 'AND top.parent_id = gettop.parent_id' || CRLF ||
642 'AND gettop.parent_table_name = ''HZ_PARTIES''' || CRLF ||
644 'AND gettop.child_table_name = ''HZ_PARTIES''' || CRLF ||
645 'AND gettop.child_object_type = ''ORGANIZATION''' || CRLF ||
646 'AND top.parent_table_name = ''HZ_PARTIES''' || CRLF ||
647 'AND top.parent_object_type = ''ORGANIZATION''' || CRLF ||
648 'AND top.top_parent_flag = ''Y''' || CRLF ||
649 'AND hn.hierarchy_type = top.hierarchy_type' || CRLF ||
650 'AND hn.parent_id = top.parent_id' || CRLF ||
651 'AND hn.parent_table_name = ''HZ_PARTIES''' || CRLF ||
652 'AND hn.parent_object_type = ''ORGANIZATION''' || CRLF ||
653 'AND hn.child_id = relacc.party_id' || CRLF ||
654 'AND hn.child_table_name = ''HZ_PARTIES''' || CRLF ||
655 'AND hn.child_object_type = ''ORGANIZATION''' || CRLF ||
656 'AND EXISTS ' || CRLF ||
657 ' (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
658 ' FROM hz_code_assignments ca' || CRLF ||
659 ' ,hz_relationship_types rt ' || CRLF ||
660 ' WHERE ca.class_category = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
661 ' AND ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
662 ' AND ca.class_code = ''PARTY_REL_GRP_AR_PAY_ANY'''|| CRLF ||
663 ' AND ca.status = ''A'''|| CRLF ||
664 ' AND ca.owner_table_id = rt.relationship_type_id' || CRLF ||
665 ' AND hn.hierarchy_type = rt.relationship_type' || CRLF ||
666 ' AND rt.object_type = ''ORGANIZATION''' || CRLF ||
667 ' AND rt.subject_type = ''ORGANIZATION'')' || CRLF;
668
669 END IF;
670
671 IF l_pay_top_down = 'Y'
672 THEN
673 l_ins_sql := l_ins_sql ||
674 'UNION' || CRLF ||
675 'SELECT /*+ ORDERED */ ' || CRLF ||
676 ' DISTINCT acc.cust_account_id, relacc.cust_account_id ' || CRLF ||
677 'FROM ' || CRLF ||
678 ' hz_cust_accounts acc,' || CRLF ||
679 ' hz_hierarchy_nodes hn, ' || CRLF ||
680 ' hz_cust_accounts relacc ' || CRLF ||
681 'WHERE acc.cust_account_id = :customer_id5' || CRLF ||
682 'AND acc.party_id = hn.parent_id ' || CRLF ||
683 'AND :apply_date7 BETWEEN hn.effective_start_date AND ' || CRLF ||
684 ' hn.effective_end_date' || CRLF ||
685 'AND hn.parent_table_name = ''HZ_PARTIES''' || CRLF ||
686 'AND hn.parent_object_type = ''ORGANIZATION''' || CRLF ||
687 'AND hn.child_id = relacc.party_id' || CRLF ||
688 'AND hn.child_table_name = ''HZ_PARTIES''' || CRLF ||
689 'AND hn.child_object_type = ''ORGANIZATION''' || CRLF ||
690 'AND EXISTS ' || CRLF ||
691 ' (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
692 ' FROM hz_code_assignments ca' || CRLF ||
693 ' ,hz_relationship_types rt ' || CRLF ||
694 ' WHERE ca.class_category = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
695 ' AND ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
696 ' AND ca.class_code = ''PARTY_REL_GRP_AR_PAY_TOP_DOWN'''|| CRLF ||
697 ' AND ca.status = ''A'''|| CRLF ||
698 ' AND ca.owner_table_id = rt.relationship_type_id' || CRLF ||
699 ' AND hn.hierarchy_type = rt.relationship_type' || CRLF ||
700 ' AND rt.object_type = ''ORGANIZATION''' || CRLF ||
701 ' AND rt.subject_type = ''ORGANIZATION'')' || CRLF;
702
703 END IF;
704
705 /* Now execute the sql */
706 cur_ins := DBMS_SQL.OPEN_CURSOR;
707 DBMS_SQL.PARSE(cur_ins, l_ins_sql, DBMS_SQL.NATIVE);
708
709 /* binds */
710 DBMS_SQL.BIND_VARIABLE(cur_ins, ':customer_id1', p_customer_id);
711 DBMS_SQL.BIND_VARIABLE(cur_ins, ':customer_id2', p_customer_id);
712 DBMS_SQL.BIND_VARIABLE(cur_ins, ':customer_id3', p_customer_id);
713 -- third and fourth (dynamic) parts of statement
714 IF l_pay_any = 'Y'
715 THEN
716 DBMS_SQL.BIND_VARIABLE(cur_ins, ':customer_id4', p_customer_id);
717 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date1', p_apply_date);
718 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date2', p_apply_date);
719 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date3', p_apply_date);
720 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date4', p_apply_date);
721 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date5', p_apply_date);
722 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date6', p_apply_date);
723 END IF;
724
725 IF l_pay_top_down = 'Y'
726 THEN
727 DBMS_SQL.BIND_VARIABLE(cur_ins, ':customer_id5', p_customer_id);
728 DBMS_SQL.BIND_VARIABLE(cur_ins, ':apply_date7', p_apply_date);
729 END IF;
730 l_rows_inserted := DBMS_SQL.EXECUTE(cur_ins);
731 DBMS_SQL.CLOSE_CURSOR(cur_ins);
732 /* end */
733 END IF;
734 ELSE
735 /* Relationships already built, no action required */
736 NULL;
737 END IF;
738
739 IF PG_DEBUG in ('Y','C') THEN
740 arp_debug.debug(' inserted ' || l_rows_inserted || ' customer/relationships. ');
741 arp_debug.debug('arp_pay_rel_cust_pkg.build_for_app_lov()-');
742 END IF;
743
744 END build_for_app_lov;
745
746 PROCEDURE clear_table IS
747 BEGIN
748 IF PG_DEBUG in ('Y','C') THEN
749 arp_debug.debug('arp_pay_rel_cust_pkg.clear_table()+');
750 END IF;
751
752 delete from ar_potential_customers_gt;
753
754 IF PG_DEBUG in ('Y','C') THEN
755 arp_debug.debug('arp_pay_rel_cust_pkg.clear_table()-');
756 END IF;
757 END clear_table;
758
759 END ARP_PAY_REL_CUST_PKG;