Receivables

--1. Transaction Throughput - AR Query

--Number of Stock Lines Invoiced

SELECT COUNT(*) Number_of_Stock_Lines_Invoiced

FROM ra_customer_trx_lines_all rctl

WHERE rctl.attribute3 = 'STK'

AND TO_CHAR(rctl.creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--Amount of Stock Lines Invoiced

SELECT SUM(EXTENDED_AMOUNT) Amount_of_Stock_Lines_Invoiced

FROM ra_customer_trx_lines_all rctl

WHERE rctl.attribute3 = 'STK'

AND TO_CHAR(rctl.creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--Number of IO Lines Invoiced

SELECT COUNT(*) Number_of_IO_Lines_Invoiced

FROM ra_customer_trx_lines_all rctl

WHERE rctl.attribute3 = 'IO'

AND TO_CHAR(rctl.creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--Amount of IO Lines Invoiced

SELECT SUM(EXTENDED_AMOUNT) Amount_of_IO_Lines_Invoiced

FROM ra_customer_trx_lines_all rctl

WHERE rctl.attribute3 = 'IO'

AND TO_CHAR(rctl.creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--Number of DS Lines Invoiced

SELECT COUNT(*) Number_of_DS_Lines_Invoiced

FROM ra_customer_trx_lines_all rctl

WHERE rctl.attribute3 = 'DS'

AND TO_CHAR(rctl.creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--Amount of DS Lines Invoiced

SELECT SUM(EXTENDED_AMOUNT) Amount_of_DS_Lines_Invoiced

FROM ra_customer_trx_lines_all rctl

WHERE rctl.attribute3 = 'DS'

AND TO_CHAR(rctl.creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--Number of Cash Applications - Receipts 

SELECT COUNT(RECEIPT_NUMBER) Number_of_Cash_Applications_Receipts 

FROM AR_CASH_RECEIPTS_ALL

WHERE  TO_CHAR(creation_date,'YYYY-MM-DD') BETWEEN TO_CHAR(:p_from_creation_date,'YYYY-MM-DD') AND TO_CHAR(:p_to_creation_date,'YYYY-MM-DD')

--2. Transaction Monitor - AR Query

--Number of AR Invoice Lines stuck in AutoInvoice interface

SELECT COUNT(*) Number_of_AR_Invoice_Lines_stuck_in_AutoInvoice_interface

FROM ra_interface_lines_all ril, ra_interface_errors_all rie

WHERE ril.interface_line_id = rie.interface_line_id

--AR Invoice Lines stuck in AutoInvoice interface

SELECT

hou.name                         business_unit,

rila.creation_date               creation_date,

rila.gl_date,

rila.last_update_date        last_update_date,

rila.interface_line_id           interface_line_id,

rila.reference_line_id           reference_line_id,

rila.batch_source_name           batch_source_name,

rila.interface_line_attribute1   order_number,

rila.sales_order_line            line_number,

rila.quantity                    quantity,

rila.unit_selling_price          unit_selling_price,

nvl(rila.amount, 0) amount,

rtrim(XMLAGG(xmlelement(e, riea.message_text, '; ').extract('//text()')

ORDER BY

rila.interface_line_id

).getclobval(), ', ') error_code

FROM

ra_interface_errors_all   riea,

ra_interface_lines_all    rila,

hr_operating_units        hou

WHERE

rila.interface_line_id = riea.interface_line_id

AND hou.organization_id = rila.org_id

GROUP BY

hou.name,

rila.creation_date,

rila.gl_date,

rila.last_update_date,

rila.interface_line_id,

rila.reference_line_id,

rila.batch_source_name,

rila.interface_line_attribute1,

rila.sales_order_line,

rila.quantity,

rila.unit_selling_price,

amount

--Number of SLA Events in Error

SELECT  COUNT(*) Number_of_SLA_Events_in_Error

FROM    xla_events                 xe

,   xla_ae_headers             xah

WHERE   1=1

AND xah.event_id                 = xe.event_id

AND xah.entity_id                = xe.entity_id

AND xe.process_status_code IN ('E','I','R')  --'Error','Invalid','Related Event In Error'

--Transaction details query

SELECT DISTINCT rcta.trx_number Transaction_Number,

  rcta.trx_date Transaction_date,

  rcta.creation_date Transaction__Creation_date,

  hp.party_name   Customer_Name,

  rcta.interface_header_attribute1 Sales_order_number,

  rctl.line_number Transaction_Line_Number,

  esib.item_number Item,

  rctl.quantity_ordered Quantity,

  rctl.unit_selling_price Unit_Price,

  rctl.extended_amount Extended_Price,

  rctl.attribute3 Order_Line_Type

FROM ra_customer_trx_all       rcta, 

hz_cust_accounts          hca, 

hz_parties   hp, 

ra_customer_trx_lines_all rctl,

egp_system_items          esib

WHERE hca.cust_account_id = rcta.bill_to_customer_id

AND   hca.party_id        = hp.party_id

AND   rcta.customer_trx_id = rctl.customer_trx_id

AND   rctl.inventory_item_id= esib.inventory_item_id

ORDER BY rcta.trx_number,

rctl.line_number