Basic SQLs – Currently Open EBS Forms

 Business Need:

Find who is logged into EBS Forms and have open (non-navigator) forms. Must be run and inform users before bouncing middle tier. Or else they will be kicked out with unsaved data lost.

 

Query:

SELECT USR.USER_NAME, S.SID, S.SERIAL# "SER#", L.PROCESS_SPID "OS PID", S.PROCESS,
   S.OSUSER, RSP.RESPONSIBILITY_NAME RESP_NAME, FRM.USER_FORM_NAME, TO_CHAR(NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)), 'MonDD hh24:mi') AS START_TIME,
   VA.NAME "COMMAND", S.STATUS, W.EVENT "WAITING FOR", S.MACHINE
FROM APPS.FND_RESPONSIBILITY_VL RSP, APPS.FND_FORM_VL FRM, APPS.FND_USER USR, APPS.FND_LOGINS L,
   APPS.FND_LOGIN_RESPONSIBILITIES R, APPS.FND_LOGIN_RESP_FORMS F, SYS.V_$SESSION S , SYS.V_$SESSION_WAIT W, SYS.AUDIT_ACTIONS VA
WHERE R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
-- AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND F.AUDSID = S.AUDSID
AND S.SID = W.SID
AND VA.ACTION = S.COMMAND
--and S.SID = '1234'
ORDER BY USR.USER_NAME, START_TIME

 
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Forms, Oracle Applications, Query, SQL, SQLPLUS, Logged Users

Basic SQLs – AR Invoice, Receipt, Receipt Application

 

Business Need:

Oracle EBS Financials

AR – Account Receivables

Analysis, Troubleshooting, Reporting, Bug Fixing, User Tracking , <you name it!>

 

Tables:

RA_CUSTOMER_TRX_ALL TRX – AR Transactions (=Customer Invoices)

AR_CASH_RECEIPTS_ALL RCT – AR Receipts

AR_RECEIVABLE_APPLICATIONS_ALL APP – AR Receipt Applications

 

Receipts have Many-to-Many relationship with Transactions. I.e. a single invoice can be applied with 10 receipts. Also a single receipt can be used against 10 invoices.

 

SQLs:

 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, APP.RECEIVABLE_APPLICATION_ID, APP.AMOUNT_APPLIED, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE
    --,  TRX.*, RCT.*, APP.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.CT_REFERENCE IN ( '4955')
--AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE
    -- ,  TRX.*, RCT.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND TRX.CT_REFERENCE = '4958'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
SELECT ARC.RECEIPT_NUMBER, TRX_NUMBER INVOICE_NUM, CT_REFERENCE SALES_ORDER, RAC.*, ARC.*
  FROM AR_RECEIVABLE_APPLICATIONS_ALL APP,       RA_CUSTOMER_TRX_ALL            RAC,        AR_CASH_RECEIPTS_ALL           ARC
 WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
   AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID
   AND ARC.RECEIPT_NUMBER = '1000026';
 
 
-- get order number from TX127 ... Merchant Ref Number
 
SELECT NVL(MAX(RAC.CT_REFERENCE),0) SALES_ORDER
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL RAC, AR_CASH_RECEIPTS_ALL ARC, IBY_FNDCPT_TX_EXTENSIONS IFTE
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
AND IFTE.ORDER_ID = ARC.RECEIPT_NUMBER
AND IFTE.ORIGIN_APPLICATION_ID = 222
AND IFTE.TRXN_EXTENSION_ID = REGEXP_REPLACE('AR127248', '[^0-9]+', '') 
AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID;
 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL