Tuesday, October 7, 2014

AR SLA Queries for PSA tables

Below are the queries for AR. Pass on to the team members if anyone needs they can use for remediating AR objects.

-- AR Transactions


SELECT rct.trx_number,
       rct.customer_trx_id,
       rctd.cust_trx_line_gl_dist_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ra_customer_trx_all rct,
       ra_customer_trx_lines_all rctl,
       ra_cust_trx_line_gl_dist_all rctd,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.application_id = 222
       AND xdl.source_distribution_type IN ('RA_CUST_TRX_LINE_GL_DIST_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = rctd.CUST_TRX_LINE_GL_DIST_ID
       AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
       AND rct.customer_trx_id = rctl.customer_trx_id
       AND rct.trx_number = :p_trx_number;

       -- AR adjustments

SELECT rct.trx_number,
       adj.customer_trx_id,
       adj.adjustment_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ra_customer_trx_all rct,
       ar_adjustments_all adj,
       ar_distributions_all ard,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.application_id = 222
       AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = ard.line_id
       AND ard.source_table = 'ADJ'
       AND ard.source_id = adj.adjustment_id
       AND adj.adjustment_id = :p_adj_id
       AND rct.customer_trx_id = adj.customer_trx_id
       AND rct.trx_number = :p_trx_number;

-- Misc Receipts

SELECT cr.receipt_number,
       cr.cash_receipt_id,
       arm.misc_cash_distribution_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ar_cash_receipts_all cr,
       ar_misc_cash_distributions_all arm,
       ar_distributions_all ard,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = ard.line_id
       AND ard.source_id = arm.misc_cash_distribution_id
       AND ard.source_table = 'MCD'
       AND xdl.application_id = 222
       AND arm.cash_receipt_id = cr.cash_receipt_id
       AND cr.cash_receipt_id = :receipt_id;

       -- CASH Receipts

SELECT cr.receipt_number,
       cr.cash_receipt_id,
       arp.receivable_application_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ar_cash_receipts_all cr,
       ar_receivable_applications_all arp,
       ar_distributions_all ard,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl,
       gl_code_combinations gcc
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = ard.line_id
       AND ard.source_id = arp.receivable_application_id
       AND ard.source_table = 'RA'
       AND xdl.application_id = 222
       AND arp.cash_receipt_id = cr.cash_receipt_id
       AND cr.cash_receipt_id = :receipt_id
       AND gcc.code_combination_id = xal.code_combination_id
       AND gcc.segment1 != '00000000000000'