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
No comments:
Post a Comment