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;
Sunday, 17 December 2017
Friday, 15 December 2017
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
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
Thursday, 23 November 2017
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
Wednesday, 22 November 2017
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;
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;
Wednesday, 1 November 2017
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;
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;
Friday, 27 October 2017
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
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
Sunday, 15 October 2017
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
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
Subscribe to:
Posts (Atom)