[Home] [Help]
63: AND ct.customer_trx_id = ps.customer_trx_id
64: AND ps.cons_inv_id > 0
65: AND ps.terms_sequence_number = 1
66: AND ct.customer_trx_id in ( SELECT customer_trx_id
67: FROM ra_interface_lines il
68: WHERE il.request_id = C_request_id
69: AND customer_trx_id IS NOT NULL
70: AND cons_billing_number IS NOT NULL
71: AND NVL(il.interface_status, '~') <> 'P'
235: SELECT l.interface_line_id,
236: l_message_text,
237: l.cons_billing_number,
238: l.org_id
239: FROM ra_interface_lines l
240: WHERE l.request_id = p_request_id
241: AND l.cons_billing_number IS NOT NULL
242: AND l.link_to_line_id IS NULL
243: AND l.orig_system_bill_customer_id IS NOT NULL
241: AND l.cons_billing_number IS NOT NULL
242: AND l.link_to_line_id IS NULL
243: AND l.orig_system_bill_customer_id IS NOT NULL
244: AND EXISTS (SELECT 'x'
245: FROM ra_interface_lines l2
246: WHERE l2.request_id = l.request_id
247: AND l2.cons_billing_number = l.cons_billing_number
248: AND l2.orig_system_bill_customer_id <> l.orig_system_bill_customer_id);
249:
263: SELECT l.interface_line_id,
264: l_message_text,
265: l.cons_billing_number,
266: l.org_id
267: FROM ra_interface_lines l
268: WHERE l.request_id = p_request_id
269: AND l.cons_billing_number IS NOT NULL
270: AND (EXISTS (SELECT 'X'
271: FROM ar_cons_inv
272: WHERE cons_billing_number = l.cons_billing_number
273: AND cons_inv_type = 'MINV' )
274: OR
275: EXISTS (SELECT 'X'
276: FROM ra_interface_lines l2
277: WHERE l2.request_id > 0
278: AND l2.request_id <> l.request_iD
279: AND l2.cons_billing_number = l.cons_billing_number));
280:
295: SELECT l.interface_line_id,
296: l_message_text,
297: l.cons_billing_number,
298: l.org_id
299: FROM ra_interface_lines l
300: WHERE l.request_id = p_request_id
301: AND l.cons_billing_number IS NOT NULL
302: AND l.link_to_line_id IS NULL
303: AND l.orig_system_bill_address_id IS NOT NULL
301: AND l.cons_billing_number IS NOT NULL
302: AND l.link_to_line_id IS NULL
303: AND l.orig_system_bill_address_id IS NOT NULL
304: AND EXISTS (SELECT 'X'
305: FROM ra_interface_lines l2
306: WHERE l2.request_id = l.request_id
307: AND l2.cons_billing_number = l.cons_billing_number
308: AND l2.orig_system_bill_address_id <> l.orig_system_bill_address_id);
309:
324: SELECT l.interface_line_id,
325: l_message_text,
326: l.cons_billing_number,
327: l.org_id
328: FROM ra_interface_lines l
329: WHERE l.request_id = p_request_id
330: AND l.cons_billing_number IS NOT NULL
331: AND l.link_to_line_id IS NULL
332: AND l.orig_system_bill_customer_id IS NOT NULL
365: SELECT l.interface_line_id,
366: l_message_text,
367: l.cons_billing_number,
368: l.org_id
369: FROM ra_interface_lines l
370: WHERE l.request_id = p_request_id
371: AND l.cons_billing_number IS NOT NULL
372: AND l.link_to_line_id IS NULL
373: AND l.orig_system_bill_customer_id IS NOT NULL
426: PROCEDURE update_ps (p_request_id IN NUMBER) IS
427:
428: CURSOR c01 IS
429: SELECT DISTINCT cons_billing_number, customer_trx_id
430: FROM ra_interface_lines li
431: WHERE request_id = p_request_id
432: and li.customer_trx_id IS NOT NULL
433: AND cons_billing_number IS NOT NULL
434: AND NVL(interface_status, '~') <> 'P' -- Only consider unprocessed Transactions
546: SELECT l.interface_line_id,
547: l_message_text,
548: l.cons_billing_number,
549: l.org_id
550: FROM ra_interface_lines_gt l
551: WHERE l.request_id = p_request_id
552: AND l.cons_billing_number IS NOT NULL
553: AND nvl(l.interface_status,'~') <> 'P'
554: AND l.link_to_line_id is null
554: AND l.link_to_line_id is null
555: AND l.customer_trx_id is not null
556: AND EXISTS
557: ( SELECT /*+ leading(L2) use_nl_with_index(E, RA_INTERFACE_ERRORS_N1) */ 'x'
558: FROM ra_interface_errors e, ra_interface_lines_gt l2
559: WHERE e.INTERFACE_LINE_ID = l2.INTERFACE_LINE_ID
560: AND l2.cons_billing_number = l.cons_billing_number
561: AND l2.request_id = l.request_id );
562: