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
Friday 27 October 2017
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
Tuesday 3 October 2017
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
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
Monday 2 October 2017
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)
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)
Subscribe to:
Posts (Atom)