Unsuccessful/Successful Logins with Status (Active/In Active/Locked) History from FND_USER

https://dbissues.blogspot.com/2017/12/unsuccessfulsuccessful-logins-with.html


Create Table

create  table XXX_USER_STATUS_HIST
(

user_id   number,
start_date  date,
end_Date   date,
Creation_date      date,
encrypted_user_password    varchar2(100)

)

---------------------------------------------------------------------------------------

Trigger to Maintain History

CREATE OR REPLACE TRIGGER XXX_USER_STATUS_HIST_TRG

BEFORE INSERT OR UPDATE ON "APPLSYS"."FND_USER#"

FOR EACH ROW

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

insert into XXX_USER_STATUS_HIST

values

(:new.user_id , :new.start_date ,:new.end_date , sysdate , :new.encrypted_user_password );

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR (

num=> -20107,

msg=> 'Some Error Occurred.......');

END XXX_USER_STATUS_HIST_TRG;

------------------------------------------------------------------------------------------------

Query

select user_id, USER_NAME, DESCRIPTION,FAX,START_TIME, LOGIN   ,

(
select max(  case
                when  xx.ENCRYPTED_USER_PASSWORD = 'INVALID'  then  'LOCKED'
                when  xx.end_date is null then 'ACTIVE'
                when  xx.end_date is not null and ABC.start_time < xx.end_date then 'ACTIVE'
                else 'INACTIVE'
           end  )
from   XXX_USER_STATUS_HIST  XX
where  XX.user_id = ABC.USER_ID
AND    XX.creation_date = (  select  max(creation_date)  from  XXX_USER_STATUS_HIST  XX2 where XX2.user_id = ABC.USER_ID  and  xx2.creation_date <=  ABC.start_time  )
)
STATUS
/*,
(
select max(    xx.ENCRYPTED_USER_PASSWORD  )
from   XXX_USER_STATUS_HIST  XX
where  XX.user_id = ABC.USER_ID
AND    XX.creation_date = (  select  max(creation_date)  from  XXX_USER_STATUS_HIST  XX2 where XX2.user_id = ABC.USER_ID  and  xx2.creation_date <=  ABC.start_time  )
)
ENCRYPTED_USER_PASSWORD*/
from
(SELECT  b.USER_ID ,  b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' LOGIN
,

/*( \*select *\
case when  NVL(b.end_date, SYSDATE) >= SYSDATE
and b.encrypted_user_password <> 'INVALID'  THEN  'Active'
when NVL (b.end_date, SYSDATE) < SYSDATE
and b.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = U.user_id
)*/
--'N/A'   STATUS,
b.ENCRYPTED_USER_PASSWORD

FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
          BETWEEN CASE
                    WHEN LENGTH(&START_TIME) in (14,15)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&START_TIME) in (8,9)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR')
                    ELSE U.ATTEMPT_TIME
                  END
          AND     CASE
                    WHEN LENGTH(&END_TIME) in (14,15)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&END_TIME) in (8,9)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
                    ELSE U.ATTEMPT_TIME
                  END


union

select  c.USER_ID ,  c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' LOGIN
,

/*( \*select*\
case when  NVL(c.end_date, SYSDATE) >= SYSDATE
and c.encrypted_user_password <> 'INVALID'  THEN  'Active'
when NVL (c.end_date, SYSDATE) < SYSDATE
and c.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = a.user_id
)*/
--'N/A'  STATUS

 c.ENCRYPTED_USER_PASSWORD

from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
          BETWEEN CASE
                    WHEN LENGTH(&START_TIME) in (14,15)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&START_TIME) in (8,9)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR')
                    ELSE a.START_TIME
                  END
          AND     CASE
                    WHEN LENGTH(&END_TIME) in (14,15)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&END_TIME) in (8,9)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
                    ELSE a.START_TIME
                  END

)  ABC

order by (START_TIME) desc

Current Status Active/Inactive and Locked users from FND_USER

https://dbissues.blogspot.com/2017/12/activeinactive-and-locked-users-from.html

SELECT 'Active' STATUS, valid.*
  FROM fnd_user valid
 WHERE valid.user_id IN (SELECT user_id
                           FROM fnd_user
                          WHERE NVL (end_date, SYSDATE) >= SYSDATE)
                          and valid.encrypted_user_password <> 'INVALID'
UNION ALL
SELECT 'In Active' STATUS, invalid.*
  FROM fnd_user invalid
 WHERE invalid.user_id IN (SELECT user_id
                             FROM fnd_user
                            WHERE NVL (end_date, SYSDATE) < SYSDATE)
                            and invalid.encrypted_user_password <> 'INVALID'
UNION ALL
SELECT 'Locked' STATUS, Locked.*
  FROM fnd_user Locked
 WHERE Locked.encrypted_user_password = 'INVALID'
ORDER BY 2;

Successful/Unsuccessful user logins in Oracle from FND_USER

https://dbissues.blogspot.com/2017/12/successfulunsuccessful-user-logins-in.html

select USER_NAME, DESCRIPTION,FAX,START_TIME, STATUS
from
(SELECT b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' STATUS
FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
          BETWEEN CASE
                    WHEN LENGTH(&START_TIME) in (14,15)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&START_TIME) in (8,9)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR')
                    ELSE U.ATTEMPT_TIME
                  END
          AND     CASE
                    WHEN LENGTH(&END_TIME) in (14,15)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&END_TIME) in (8,9)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
                    ELSE U.ATTEMPT_TIME
                  END



union

select c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' STATUS
from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
          BETWEEN CASE
                    WHEN LENGTH(&START_TIME) in (14,15)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&START_TIME) in (8,9)
                     THEN TO_DATE(&START_TIME, 'DD-MON-RR')
                    ELSE a.START_TIME
                  END
          AND     CASE
                    WHEN LENGTH(&END_TIME) in (14,15)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
                    WHEN LENGTH(&END_TIME) in (8,9)
                     THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
                    ELSE a.START_TIME
                  END

)

order by (START_TIME) desc

Code to get ASCII codes of any Database Character

https://dbissues.blogspot.com/2017/11/code-to-get-ascii-codes-of-any-database.html

select  

ascii(substr('PER MONTH',1,1)),

ascii(substr('PER MONTH',2,1)),

ascii(substr('PER MONTH',3,1)),

ascii(substr('PER MONTH',4,1)),

ascii(substr('PER MONTH',5,1)),

ascii(substr('PER MONTH',6,1)),

ascii(substr('PER MONTH',7,1)),

ascii(substr('PER MONTH',9,1))

from   dual

Code to get ASCII codes of character appearing in Vendor Name

select  ascii(substr(a.VENDOR_NAME,1,1)) from ap_suppliers a

where a.SEGMENT1 = 1

select  chr(83) from dual

Undo Related Queries

https://dbissues.blogspot.com/2017/11/undo-related-queries.html

List of All active Transactions

SELECT sid,
username,
t.used_ublk,
t.used_urec
FROM v$transaction t,
v$session s
WHERE t.addr = s.taddr;


To list all Datafile of UndoTablespace

SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576 Mbytes,
maxbytes / 1048576 maxMbytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’
OR tablespace_name LIKE ‘%RBS%’
ORDER BY tablespace_name,
file_name;


SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576 Mbytes,
maxbytes / 1048576 maxMbytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDOTBS2%’
ORDER BY tablespace_name,
file_name;


col file_name FOR a40
set pagesize 100
SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576 Mbytes,
maxbytes / 1048576 maxMbytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%APPS_UNDOTS1%’
ORDER BY tablespace_name,
file_name;


SELECT file_name,
tablespace_name,
bytes / 1024 / 1024,
maxbytes / 1024 / 1024,
autoextensible
FROM dba_data_files
WHERE file_name LIKE ‘%undo%’
ORDER BY file_name;


To check when a table is last analysed
SELECT owner,
table_name,
tablespace_name,
status,
last_analyzed,
partitioned,
dependencies,
dropped
FROM dba_tables
WHERE table_name LIKE ‘MLC_PICK_LOCKS_DETAIL’;


SELECT owner,
table_name,
tablespace_name,
last_analyzed,
partitioned,
dependencies,
dropped
FROM dba_tables
WHERE table_name LIKE ‘APPS.XLA_AEL_GL_V’;


To list all Undo datafiles with status and size
show parameter undo
show parameter db_block_size
col tablespace_name FORM a20
col file_name FORM a60
set lines 120
SELECT tablespace_name,
file_name,
status,
bytes / 1024 / 1024
FROM dba_data_files
WHERE tablespace_name = (SELECT tablespace_name
FROM dba_tablespaces
WHERE CONTENTS = ‘UNDO’);

Total undo space

SELECT SUM(bytes) / 1024 / 1024 / 1024 GB
FROM dba_data_files
WHERE tablespace_name = ‘&Undo_TB_Name’;

Undo Tablespace

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name LIKE ‘%UNDO%’;


To find MaxQueryLength from stats$undostat
SELECT Max(maxquerylen)
FROM stats$undostat;

SELECT Max(maxquerylen)
FROM v$undostat;

SELECT begin_date,
u.maxquerylen
FROM (SELECT To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date,
maxquerylen
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 11;

SELECT begin_date,
u.maxquerylen
FROM (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26
ORDER BY begin_date ASC,
maxquerylen DESC;

SELECT begin_date,
u.maxquerylen
FROM (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26
ORDER BY maxquerylen DESC;

SELECT SUM(u.maxquerylen) / 25 AvgUndoRetTime
FROM (SELECT maxquerylen
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26;

SELECT SUM(u.maxquerylen)
FROM (SELECT maxquerylen
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26;


DBA_UNDO_EXTENTS

set linesize 152
col tablespace_name FOR a20
col status FOR a10
SELECT tablespace_name,
status,
Count(extent_id) “Extent Count”,
SUM(blocks) “Total Blocks”,
SUM(bytes) / ( 1024 * 1024 * 1024 ) spaceInGB
FROM dba_undo_extents
GROUP BY tablespace_name,
status
ORDER BY tablespace_name;


Mapping Undo Segments to usernames

SELECT s.sid,
s.serial#,
username,
s.machine,
t.used_ublk,
t.used_urec,
( rs.rssize ) / 1024 / 1024 MB,
rn.name
FROM v$transaction t,
v$session s,
v$rollstat rs,
v$rollname rn
WHERE t.addr = s.taddr
AND rs.usn = rn.usn
AND rs.usn = t.xidusn
AND rs.xacts > 0;


Total Undo Statistics

ALTER SESSION SET nls_date_format=‘dd-mon-yy hh24:mi’;

set lines 120
set pages 2000
SELECT begin_time,
end_time,
undoblks,
txncount,
maxquerylen,
unxpstealcnt,
expstealcnt,
ssolderrcnt,
nospaceerrcnt
FROM v$undostat;


Total Undo Statistics since specified year

SELECT ‘TOTAL STATISTICS SINCE Jan 01, 2005 – STATSPACK’
FROM dual;

set head ON
set lines 152
column undotsn format 999 heading ‘Undo|TS#’;
column undob format 9, 999, 999, 999 heading ‘Undo|Blocks’;
column txcnt format 9, 999, 999, 999, 999 heading ‘Num|Trans’;
column maxq format 999, 999 heading ‘Max Qry|Len (s)’;
column maxc format 9, 999, 999 heading ‘Max Tx|Concurcy’;
column snol format 9, 999 heading ‘Snapshot|Too Old’;
column nosp format 9, 999 heading ‘Out of|Space’;
column blkst format a13 heading ‘uS/uR/uU/|eS/eR/eU’ wrap;
column unst format 9, 999 heading ‘Unexp|Stolen’ newline;
column unrl format 9, 999 heading ‘Unexp|Relesd’;
column unru format 9, 999 heading ‘Unexp|Reused’;
column exst format 9, 999 heading ‘Exp|Stolen’;
column exrl format 9, 999 heading ‘Exp|Releas’;
column exru format 9, 999 heading ‘Exp|Reused’;
SELECT undotsn,
SUM(undoblks) undob,
SUM(txncount) txcnt,
Max(maxquerylen) maxq,
Max(maxconcurrency) maxc,
SUM(ssolderrcnt) snol,
SUM(nospaceerrcnt) nosp,
SUM(unxpstealcnt)
||‘/’
|| SUM(unxpblkrelcnt)
||‘/’
|| SUM(unxpblkreucnt)
||‘/’
|| SUM(expstealcnt)
||‘/’
|| SUM(expblkrelcnt)
||‘/’
|| SUM(expblkreucnt) blkst
FROM stats$undostat
WHERE dbid IN (SELECT dbid
FROM v$database)
AND instance_number IN (SELECT instance_number
FROM v$instance)
AND end_time > To_date(‘01012005 00:00:00’, ‘DDMMYYYY HH24:MI:SS’)
AND begin_time < (SELECT SYSDATE
FROM dual)
GROUP BY undotsn;

SELECT ( SUM(undoblks) ) / SUM (( end_time – begin_time ) * 86400)
FROM v$undostat;

Active Users Session Query before taking Oracle Down

https://dbissues.blogspot.com/2017/11/active-user-sessions-query-before-going.html

SELECT DISTINCT icx.session_id,
                  icx.user_id,
                  fu.user_name,
                  fu.description
    FROM icx_sessions icx, fnd_user fu
   WHERE     disabled_flag != 'Y'
         AND icx.pseudo_flag = 'N'
         AND (last_connect +
              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
                      NULL, limit_time,
                      0   , limit_time,
                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
         AND icx.counter < limit_connects
         AND icx.user_id = fu.user_id;

ls_Commands

https://dbissues.blogspot.com/2017/10/1.html

1. List Files using ls with no option
# ls
----------------------------------------------------------------------------------
2 List Files With option –l
# ls -l
----------------------------------------------------------------------------------
3. View Hidden Files
# ls -a
----------------------------------------------------------------------------------
4. List Files with Human Readable Format with option -lh
# ls -lh
----------------------------------------------------------------------------------
5. List Files and Directories with ‘/’ Character at the end
# ls -F
----------------------------------------------------------------------------------
6. List Files in Reverse Order
# ls -r
----------------------------------------------------------------------------------
7. Recursively list Sub-Directories
# ls -R
----------------------------------------------------------------------------------
8. Reverse Output Order
# ls -ltr
----------------------------------------------------------------------------------
9. Sort Files by File Size
ls -lS
----------------------------------------------------------------------------------
10. Display Inode number of File or Directory
# ls -i
----------------------------------------------------------------------------------
11. Shows version of ls command
# ls --version
----------------------------------------------------------------------------------
12. Show Help Page
# ls --help
----------------------------------------------------------------------------------
13. List Directory Information
With ls -l command list files under directory /tmp. Wherein with -ld parameters displays information of /tmp directory
# ls -l /tmp
# ls -ld /tmp/
----------------------------------------------------------------------------------
14. Display UID and GID of Files
# ls -n

15. ls command and it’s Aliases
We have made alias for ls command, when we execute ls command it’ll take -l option by default and display long listing as mentioned earlier.
# alias ls="ls -l"

Note: We can see number of alias available in your system with below alias command and same can be unalias as shown below example.

# alias
To remove an alias previously defined, just use the unalias command
# unalias ls

Oracle / PLSQL: CASE Statement

https://dbissues.blogspot.com/2017/10/oracle-plsql-case-statement.html

1ST QUERY

SELECT aia.invoice_id,
       gcc.SEGMENT2 location
     
       ------------------------ Aggregating multiple invoice distribution lines using SUM/Decode Combination -----------------------------------------------------------------------------
     
      ,
       atg.description          Payment_Section,
       vnd.vat_registration_num TaxPayer_NTN,
       vnd.attribute13          TaxPayer_CNIC
     
       --,        SUM (aidl1.amount)  - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (aidl.amount))) dist_net_amount_ent
       --,        SUM (nvl (aidl1.base_amount, aidl1.amount)) - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_net_amount_fnc
     
       --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ,
       aidl.IRS_NO,
       NVL(TAX_TYPE, 'No Tax type') TAX_TYPE,
       Section,
       Tax_exemption,
       --,        aia.invoice_currency_code inv_currency
     
       vnd.vendor_name Taxpayer_name
       --,        vnd.segment1 vendor_code
      ,
       vnds.city TaxPayer_City,
       vnds.address_line1 || ' ' || vnds.address_line2 || ' ' ||
       vnds.address_line3 TaxPayer_Address,
       vnds.attribute14 TaxPayer_Status,
       vnds.attribute15 TaxPayer_Business_Name
       --,        SUM (aidl1.amount) dist_amount_ent
      ,
       SUM(nvl(aidl1.base_amount, aidl1.amount)) Taxable_Amount
       --,        SUM (decode (aidl.line_type_lookup_code, 'AWT', abs(aidl.amount))) dist_tax_amount_ent
      ,
       SUM(decode(aidl.line_type_lookup_code, 'AWT', abs(aidl.amount))) Tax_Amount,
       NVL(aia.EXCHANGE_RATE, 1) INVOICE_EXG_RATE,
       SUM(decode(aidl.line_type_lookup_code,
                  'AWT',
                  abs(nvl(aidl.base_amount, aidl.amount)))) INVOICE_TAX_AMOUNT_PKR,
       NVL(aca.EXCHANGE_RATE, 1) PAYMENT_EXG_RATE,
       (case
         when aia.EXCHANGE_RATE = aca.EXCHANGE_RATE then
          SUM(decode(aidl.line_type_lookup_code,
                     'AWT',
                     abs(nvl(aidl.base_amount, aidl.amount))))
         else
         round(NVL(aca.EXCHANGE_RATE, 1) *
                SUM(decode(aidl.line_type_lookup_code,
                           'AWT',
                           abs(aidl.amount))),2)
       end) PAYMENT_TAX_AMOUNT_PKR,
      round( SUM(decode(aidl.line_type_lookup_code,
                  'AWT',
                  abs(nvl(aidl.base_amount, aidl.amount)))) -
       (case
          when aia.EXCHANGE_RATE = aca.EXCHANGE_RATE then
           SUM(decode(aidl.line_type_lookup_code,
                      'AWT',
                      abs(nvl(aidl.base_amount, aidl.amount))))
          else
           NVL(aca.EXCHANGE_RATE, 1) *
                 SUM(decode(aidl.line_type_lookup_code,
                            'AWT',
                            abs(aidl.amount)))
        end)) BALANCE_PAYABLE
       /*  ,
                            SUM(decode(aidl.line_type_lookup_code,
                                       'AWT',
                                       abs(nvl(aidl.base_amount, aidl.amount)))) Tax_Amount*/,
       vnd.segment1 Supplier_No,
       aca.doc_sequence_value APV_NO,
       aca.cleared_date APV_POSTED_DATE,
       aia.doc_sequence_value APN_NO,
       atg.NAME Tax_Code,
       aca.CURRENCY_CODE
       --,        atg.description Tax_Group_Desc
      ,
       aca.bank_account_name,
       U.USER_NAME

  FROM -- ap_invoice_distributions_all aidl                                                               -- used for tax grouping     --- Marked on 15 September 2010 By Muhammad Raheem (Saad)
       -- Distribution line calculating tax on sables basis, that create multiple TAX lines so these lines where sum to make single TAX line
        (SELECT sum(amount) amount,
                sum(base_amount) base_amount,
                line_type_lookup_code,
                PAY_awt_group_id, -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
                reversal_flag,
                invoice_id,
                awt_related_id,
                (SELECT TCA.ATTRIBUTE6
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) Tax_exemption,
                (SELECT TCA.ATTRIBUTE5
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) Section,
                (SELECT TCA.ATTRIBUTE4
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) IRS_NO,
                (SELECT TCA.ATTRIBUTE3
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) TAX_TYPE
           FROM ap_invoice_distributions_all
          WHERE org_id = :P_ORG_ID
               ------------------------ CHANGE TAX TYPE PARAMETER ------------------------------------------------------
            AND NVL(WITHHOLDING_TAX_CODE_ID, -1) IN
                (SELECT TAX_ID
                   FROM Ap_Tax_Codes_All TCA
                  WHERE ORG_ID = :P_ORG_ID
                    AND nvl(TCA.ATTRIBUTE3, 'NA') =
                        nvl(:P_TAX_TYPE, nvl(TCA.ATTRIBUTE3, 'NA')))
               -------------------------------------------------------------------------------------------
            AND NVL(WITHHOLDING_TAX_CODE_ID, -1) NOT IN
                ( --- ADDED ON 28 FEB -- --  added org_id by IACS ( IMRAN 27-july-2015 )
                 SELECT TAX_ID
                   FROM Ap_Tax_Codes_All
                  WHERE org_id = :P_ORG_ID
                    AND DESCRIPTION LIKE 'GST-SRO98') --- ADDED ON 28 FEB  --  added org_id by IACS ( IMRAN 27-july-2015 )
          group by PAY_awt_group_id,
                   reversal_flag,
                   line_type_lookup_code,
                   invoice_id,
                   awt_related_id,
                   attribute3,
                   WITHHOLDING_TAX_CODE_ID) aidl -- used for tax grouping 
      ,
       ap_invoice_distributions_all aidl1 -- used for item grouping
      ,
       ap_awt_groups atg,
       ap_invoices_all aia,
       ap_suppliers vnd,
       ap_supplier_sites_all vnds,
       ap_invoice_payments_all aipa,
       ap_checks_all aca,
       fnd_user u,
       gl_code_combinations gcc
 WHERE
--
-- -------* key join codition *------------
 aidl1.org_id = :P_ORG_ID -- ADDED ON 31-JUL-15
 and aia.org_id = :P_ORG_ID -- ADDED ON 31-JUL-15
 and aipa.org_id = :P_ORG_ID -- ADDED ON 31-JUL-15
 and aca.org_id = :P_ORG_ID -- ADDED ON 31-JUL-15
 and aia.org_id = aidl1.org_id -- ADDED ON 31-JUL-15
 and aia.org_id = aipa.org_id -- ADDED ON 31-JUL-15
 and aia.org_id = aca.org_id -- ADDED ON 31-JUL-15
 AND aidl1.invoice_distribution_id = aidl.awt_related_id
-----------------------------------------------------
 AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
 AND aia.invoice_id = aidl.invoice_id
 AND vnd.vendor_id = aia.vendor_id
 AND vnd.vendor_id = vnds.vendor_id
 AND aia.vendor_site_id = vnds.vendor_site_id
 AND aipa.invoice_id = aia.invoice_id
 AND aca.check_id = aipa.check_id
 and gcc.SEGMENT2 between nvl(:P_FROM_LOCATION, gcc.SEGMENT2) and
 nvl(:P_TO_LOCATION, gcc.SEGMENT2)
 AND aca.cleared_date IS NOT NULL
 AND aidl.amount <> '0' -- do not print zero value tax lines
 AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
 AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
 AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
-- -----------Parameters-------------------
 AND aca.cleared_date BETWEEN :P_DATE1 and :P_DATE2 --nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)
 AND atg.NAME BETWEEN nvl(:p_tax_code_from, atg.NAME) AND
 nvl(:p_tax_code_to, atg.NAME)
--     AND aia.doc_sequence_value = nvl (:p_apn_num, aia.doc_sequence_value)
 AND vnd.vendor_id BETWEEN nvl(:p_vendor_from, vnd.vendor_id) AND
 nvl(:p_vendor_to, vnd.vendor_id)
 and aca.CURRENCY_CODE between nvl(:CCY_from, aca.CURRENCY_CODE) and
 nvl(:CCY_to, aca.CURRENCY_CODE)
 and u.USER_ID = aia.CREATED_BY
 and U.user_id = NVL(:P_USER_ID, U.USER_ID)
 and aidl1.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

-- ----------------------------------------------
 GROUP BY atg.NAME,
          atg.description,
          vnd.vendor_name,
          vnd.segment1,
          vnds.city,
          vnd.vat_registration_num,
          aia.doc_sequence_value,
          aca.doc_sequence_value,
          aia.invoice_currency_code,
          aca.cleared_date,
          aia.invoice_id,
          aca.currency_code,
          vnds.address_line1,
          vnds.address_line2,
          vnds.address_line3,
          vnds.attribute14,
          vnds.attribute15,
          vnd.attribute13,
          aca.bank_account_name,
          aidl.IRS_NO,
          TAX_TYPE,
          U.USER_NAME,
          Section,
          Tax_exemption,
          aia.EXCHANGE_RATE,
          gcc.SEGMENT2,
          aca.EXCHANGE_RATE

 ORDER BY Tax_Code, aca.cleared_date, aia.doc_sequence_value ASC;


--------------------------------------------------------------------------------------------------------------------------

2nd QUERY

select fab.BOOK_TYPE_CODE,
       fb.ASSET_ID,
       fb.ASSET_NUMBER,
       fab.DATE_PLACED_IN_SERVICE,
       to_char(fab.DATE_PLACED_IN_SERVICE, 'MON-YY') MONTHS_PLACED_IN_SERVICE,
       fdp.PERIOD_NAME LAST_DEPRN_RUN,
       to_char(ADD_MONTHS((LAST_DAY(fab.DATE_PLACED_IN_SERVICE) + 1), -1),'DD-MON-YY') START_DATE_PLACED_IN_SERVICE,
     
       TO_CHAR(last_day(fab.DATE_PLACED_IN_SERVICE), 'DD-MON-YY') END_DATE_PLACED_IN_SERVICE,
       to_char(ADD_MONTHS((LAST_DAY('01-'||fdp.PERIOD_NAME) + 1), -1),'DD-MON-YY') START_DATE_DEPRN_RUN,
       TO_CHAR(last_day('01-' || fdp.PERIOD_NAME), 'DD-MON-YY') END_DATE_DEPRN_RUN
       --,MONTHS_BETWEEN(last_day( '01-'||fdp.PERIOD_NAME),fab.DATE_PLACED_IN_SERVICE) LIFE_USED_IN_MONTHS
      ,
       fab.LIFE_IN_MONTHS,
       /*TRUNC(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),
       fab.DATE_PLACED_IN_SERVICE)) LIFE_USED_IN_MONTHS,*/
       ----------------------------------------------------------------
       NVL(MONTHS_BETWEEN((select trunc(v.DATE_RETIRED, 'MONTH')
                            from fa_retirements_v v
                           where v.ASSET_ID = fb.ASSET_ID
                             and v.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
                             and v.ASSET_ID = fb.ASSET_ID
                             and v.TRANSACTION_HEADER_ID_IN =
                                 fab.TRANSACTION_HEADER_ID_IN),
                          trunc(fab.DATE_PLACED_IN_SERVICE, 'MONTH'))
         
          ,
         
          TRUNC(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),
                                fab.DATE_PLACED_IN_SERVICE)) + 1
                               
/*           CEIL(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),
                                fab.DATE_PLACED_IN_SERVICE))*/
         
           ) TOTAL_LIFE_USED_IN_MONTHS
     
       ------------------------------------------------------                 
      ,
       NVL(fab.life_in_months -
           GREATEST(NVL(fab.life_in_months, 0) -
                    MONTHS_BETWEEN((select trunc(v.DATE_RETIRED, 'MONTH')
                                     from fa_retirements_v v
                                    where v.ASSET_ID = fb.ASSET_ID
                                      and v.BOOK_TYPE_CODE =
                                          fab.BOOK_TYPE_CODE
                                      and v.ASSET_ID = fb.ASSET_ID
                                      and v.TRANSACTION_HEADER_ID_IN =
                                          fab.TRANSACTION_HEADER_ID_IN),
                                   TRUNC(fab.DATE_PLACED_IN_SERVICE, 'MONTH')),
                    0
                   
                    ),
                   
                  TRUNC(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),
                                fab.DATE_PLACED_IN_SERVICE)) + 1
       /*   
           CEIL(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),
                                fab.DATE_PLACED_IN_SERVICE))*/
         
           ) ACTUAL_LIFE_COMPLETED
     
       -----------------------------------
     
      ,
       (
       
        SELECT decode(faab.conversion_date,
                       NULL,
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.prorate_date)),
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.deprn_start_date)))
       
          FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb
       
         where faab.ASSET_ID = fbb.ASSET_ID
           and faab.date_ineffective IS NULL
           AND fdpp.period_counter =
               (SELECT MAX(dpp.period_counter)
                  FROM fa_deprn_periods dpp
                 where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE
               
                )
           and faab.ASSET_ID = fab.ASSET_ID
           and fb.ASSET_ID = fbb.ASSET_ID
           and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
           and fbb.ASSET_ID = b.ASSET_ID
           and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE
       
        ) REM_LIFE_IN_MON
       --------------------------------------------------------
     
      ,
       (select a.TRANSACTION_TYPE_CODE
          from fa_transaction_history_trx_v a
         where a.ASSET_ID = fab.ASSET_ID
           and a.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
           and a.CATEGORY_ID = fb.ASSET_CATEGORY_ID
           and a.ASSET_ID = fb.ASSET_ID
           and a.ASSET_ID = b.ASSET_ID
           and a.BOOK_TYPE_CODE = b.BOOK_TYPE_CODE
           and a.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE
           and a.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT') FULL_RETIREMENT

----------------------------------------
,    (  SELECT 

case
         when decode(faab.conversion_date,
                       NULL,
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.prorate_date)),
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.deprn_start_date))) <= 0 then 0
         else
         decode(faab.conversion_date,
                       NULL,
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.prorate_date)),
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.deprn_start_date)))
       end
         FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb
       
         where faab.ASSET_ID = fbb.ASSET_ID
           and faab.date_ineffective IS NULL
           AND fdpp.period_counter =
               (SELECT MAX(dpp.period_counter)
                  FROM fa_deprn_periods dpp
                 where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE
               
                )
           and faab.ASSET_ID = fab.ASSET_ID
           and fb.ASSET_ID = fbb.ASSET_ID
           and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
           and fbb.ASSET_ID = b.ASSET_ID
           and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE
     
       ) REM_LIFE_IN_MON_2


--------------------------------------


,    (  SELECT 

case
         when decode(faab.conversion_date,
                       NULL,
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.prorate_date)),
                       faab.life_in_months -
                       floor(months_between(fdpp.calendar_period_close_date,
                                            faab.deprn_start_date))) <= 0 then decode((select  a.TRANSACTION_TYPE_CODE
          from fa_transaction_history_trx_v a
         where a.ASSET_ID = fab.ASSET_ID
           and a.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
           and a.CATEGORY_ID = fb.ASSET_CATEGORY_ID
           and a.ASSET_ID = fb.ASSET_ID
           and a.ASSET_ID = b.ASSET_ID
           and a.BOOK_TYPE_CODE = b.BOOK_TYPE_CODE
           and a.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE
           and a.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT'
          ) , 'FULL RETIREMENT', 'FULL RETIREMENT', 'FULLY DEPRECIATED')
     
                                            else null
       end
         FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb
       
         where faab.ASSET_ID = fbb.ASSET_ID
           and faab.date_ineffective IS NULL
           AND fdpp.period_counter =
               (SELECT MAX(dpp.period_counter)
                  FROM fa_deprn_periods dpp
                 where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE
               
                )
           and faab.ASSET_ID = fab.ASSET_ID
           and fb.ASSET_ID = fbb.ASSET_ID
           and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
           and fbb.ASSET_ID = b.ASSET_ID
           and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE
     
       ) STATUS




-------------------------------------

  FROM fa_books         fab,
       fa_deprn_periods fdp,
       fa_additions_b   fb,
       fa_deprn_summary b

 where fab.ASSET_ID = fb.ASSET_ID
   and b.ASSET_ID = fab.ASSET_ID
   and b.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
   and b.PERIOD_COUNTER = fdp.PERIOD_COUNTER
   and fab.date_ineffective IS NULL
     
   AND fdp.period_counter =
       (SELECT MAX(dp.period_counter)
          FROM fa_deprn_summary dp
         where dp.ASSET_ID = fab.ASSET_ID
           and dp.ASSET_ID = fb.ASSET_ID
           and dp.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE
           and fdp.BOOK_TYPE_CODE = dp.BOOK_TYPE_CODE)

 order by to_number(fb.ASSET_NUMBER), fab.BOOK_TYPE_CODE

APP-FND-01542 : This Application Server is not authorized to access this system. Please contact your system administrator

https://dbissues.blogspot.com/2017/10/app-fnd-01542-this-application-server.html

This error message pops-up when value for profile “s_appserverid_authentication” is set to “SECURE“ in Application Context file.

This profile value can be set to three different values, “SECURE, PARTIAL, OFF”.

To over come this issue set check profile value “s_appserverid_authentication” from “SECURE” to “OFF”.

Then.
Stop the middle tiers
Run autoconfig
Startup the middle tier services
Retest direct login to forms

How to compile invalid objects in an APPS Environment

https://dbissues.blogspot.com/2017/10/how-to-compile-invalid-objects-in-apps.html

Compiling Objects Manually Through SQL Commands

First find the Invalid Objects in the Database by below given query.

SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = ‘INVALID’;

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = ‘INVALID’
GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use oneof the following depending on the object type :

alter package <package_name> compile; (package specification)
alter package <package_name> compile body; (package body)
alter view <view_name> compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors
OR
select * from user_errors where name = ‘<OBJECT_NAME>’;
Compiling Objects Through ADADMIN Utility

1. Login OS with APPS owner.

2. Start the ADADMIN Utility from the Unix prompt with this command :

$adadmin

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

Tablespace Growth Query

https://dbissues.blogspot.com/2017/09/tablespace-growth-query.html

  SELECT  TO_CHAR(SYSDATE,'MON-YYYY') period , a.tablespace_name,
                  ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
                            c.BYTES / 1024 / 1024 space_allocated,
                            ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
                            ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
                            c.DATAFILES
                          FROM dba_tablespaces a,
                               (    SELECT   tablespace_name,
                                          SUM (BYTES) BYTES
                                   FROM   dba_free_space
                               GROUP BY   tablespace_name
                               ) b,
                              (    SELECT   COUNT (1) DATAFILES,
                                          SUM (BYTES) BYTES,
                                          tablespace_name
                                   FROM   dba_data_files
                               GROUP BY   tablespace_name
                            ) c
                          WHERE b.tablespace_name(+) = a.tablespace_name
                            AND c.tablespace_name(+) = a.tablespace_name

Delete Interface/Integrator/Component - Web ADI

--Delete an Interface/Integrator Details

SELECT biv.application_id

       ,biv.integrator_code

       ,biv.user_name

       ,bib.interface_code

   FROM bne_integrators_vl biv

       ,bne_interfaces_b   bib

  WHERE upper(user_name) like '%XXAK%'

    AND bib.integrator_code = biv.integrator_code ;

---------------------------------------------

--Delete an Interface

DECLARE

   vn_number   NUMBER;

BEGIN

   vn_number := bne_integrator_utils.delete_interface

                (p_application_id => 20003,

        p_interface_code  => 'XXAKTESTUPADI_XINTG_INTF1');

                

   DBMS_OUTPUT.put_line ('ADI Interface Deleted '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

----------------------------

--Delete an Intergrator

DECLARE

   vn_number number:=0;

BEGIN

   vn_number:= bne_integrator_utils.delete_integrator

               (p_application_id => 20003,

                p_integrator_code => 'XXAKTESTUPADI_XINTG');

               

   dbms_output.put_line(' ADI Deleted : '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

---------------------------

--Delete a Component

DECLARE

   vn_number number:=0;

BEGIN

   vn_number:=BNE_INTEGRATOR_UTILS.DELETE_COMPONENT(p_application_id => 200,

                p_COMPONENT_CODE => 'HUBINV_XINTG_INTF1_C9_COMP');

              

   dbms_output.put_line(' ADI Deleted : '||vn_number);

   COMMIT;

   --

EXCEPTION 

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;