HCM SQL: Mapping Between Absence Plan and Type

SELECT 
        aapf.absence_plan_id,
        aapft.name absence_plan_name,
        aatft.name absence_type_name,
        aatft.absence_type_id,
        aatp.ABSENCE_TYPE_PLAN_ID,
        aatf.status
    FROM
        anc_absence_plans_f_tl aapft,
        anc_absence_plans_f aapf,
        ANC_ABSENCE_TYPES_F_TL aatft,
        ANC_ABSENCE_TYPES_F aatf,
        ANC_ABSENCE_TYPE_PLANS_F aatp
    WHERE 1=1
        --------
        and aapft.absence_plan_id = aapf.absence_plan_id
        AND aapf.plan_status = 'A'   -- added to pick only Active Absence Plans
        AND aapft.language = 'US'
        and aapf.LEGISLATIVE_DATA_GROUP_ID = :P_LDG
        --------
        and aatft.absence_type_id = aatf.absence_type_id
        AND aatf.status = 'A'   -- added to pick only Active Absence Plans
        AND aatft.language = 'US'       
        and aatf.LEGISLATIVE_DATA_GROUP_ID = :P_LDG
        --------
        and aatp.ABSENCE_PLAN_ID = aapf.absence_plan_id
        and aatp.ABSENCE_TYPE_ID = aatf.ABSENCE_TYPE_ID
        --------   
        AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
        AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
        AND trunc(SYSDATE) BETWEEN aatp.effective_start_date AND aatp.effective_end_date
        AND trunc(SYSDATE) BETWEEN aatft.effective_start_date AND aatft.effective_end_date
        AND trunc(SYSDATE) BETWEEN aatf.effective_start_date AND aatf.effective_end_date

Fusion FA Asset Register Report : SQL

SELECT 
 FA.ASSET_NUMBER
,FAT.DESCRIPTION
,FB.ORIGINAL_COST
,FB.DATE_PLACED_IN_SERVICE
,FA.ATTRIBUTE_CATEGORY_CODE
,FCB.SEGMENT1 MAJOR_CATEGORY
,FCB.SEGMENT2 MINOR_CATEGORY
,NVL(FDS.DEPRN_RESERVE,0) DEPRN_RESERVE
,NVL(FDS.YTD_DEPRN,0) YTD_DEPRN
,(select FL.SEGMENT1 ||' ,'|| FL.SEGMENT2 ||' ,'|| FL.SEGMENT3 from fa_locations FL where FL.location_id = FDH.LOCATION_ID) location
,(SELECT FULL_NAME FROM PER_PERSON_NAMES_F WHERE PERSON_ID= FDH.ASSIGNED_TO AND NAME_TYPE ='GLOBAL') EMP_NAME
, (SELECT TO_CHAR(TRUNC(METH.LIFE_IN_MONTHS/12) +
             (MOD(METH.LIFE_IN_MONTHS,12)/100), '9000D00')
                                LIFE_IN_YR_MONTHS
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) LIFE_IN_YR_MONTHS
,1/(SELECT (METH.LIFE_IN_MONTHS/12) LIFE_IN_YR
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) DEPRN_RATIO
--,FCATB.ASSET_COST_ACCOUNT_CCID COST_ACCT_CCID
--,FCATB.RESERVE_ACCOUNT_CCID ACCUMULATED_DEPRN_CCID
--,FDH.CODE_COMBINATION_ID EXPENSE_CCID
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.ASSET_COST_ACCOUNT_CCID ) COST_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.RESERVE_ACCOUNT_CCID    ) ACCDEP_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID       ) EXP_ACCT
--,FDS_P.DEPRN_RESERVE DEPRN_RESERVE_PREVIOUS
--,FDS_P.DEPRN_AMOUNT
--,FDH.ASSIGNED_TO
--,FDH.LOCATION_ID
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_C.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1') ) CURRENT_PERIOD
--,FDP_C.PERIOD_COUNTER CURRENT_PERIOD_COUNTER
--,FDP_P.PERIOD_COUNTER PREVIOUS_PERIOD_COUNTER
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_P.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')) PREVIOUS_PERIOD
--ROUND(SYSDATE - FB.DATE_PLACED_IN_SERVICE,0) DAYS_IN_SERVICE
FROM
  FA_ADDITIONS_B FA
, FA_ADDITIONS_TL FAT
, FA_BOOKS FB
, FA_CATEGORIES_B FCB
, FA_CATEGORY_BOOKS FCATB
, FA_DEPRN_SUMMARY FDS
--, FA_DEPRN_SUMMARY FDS_P
, FA_DISTRIBUTION_HISTORY FDH
, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_C
--, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NOT NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_P
, FA_BOOK_CONTROLS FBC
WHERE 1=1
AND FAT.ASSET_ID = FA.ASSET_ID
AND ( userenv('LANG') ) = FAT.language
------
AND FA.ASSET_ID = FB.ASSET_ID 
AND FCB.CATEGORY_ID = FA.ASSET_CATEGORY_ID
AND FCATB.CATEGORY_ID = FCB.CATEGORY_ID
AND FB.BOOK_TYPE_CODE = FCATB.BOOK_TYPE_CODE
AND FB.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID(+) = FA.ASSET_ID
AND FDS.BOOK_TYPE_CODE(+) = FB.BOOK_TYPE_CODE
AND FDS.PERIOD_COUNTER(+) = FDP_C.PERIOD_COUNTER
--AND FDS_P.ASSET_ID = FA.ASSET_ID
--AND FDS_P.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
--AND FDS_P.PERIOD_COUNTER = FDP_P.PERIOD_COUNTER
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FDH.ASSET_ID = FA.ASSET_ID
AND FDH.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.SET_OF_BOOKS_ID = :P_LEDGER_ID
--AND NVL(FDH.ASSIGNED_TO,-1) = NVL(:P_EMPLOYEE_ID,NVL(FDH.ASSIGNED_TO,-1))
and (NVL(FDH.ASSIGNED_TO,-1) IN (:P_EMPLOYEE_ID) OR LEAST(:P_EMPLOYEE_ID) IS NULL)
and (FCB.SEGMENT1 IN (:P_MAJOR_CATEGORY_NAME) OR LEAST(:P_MAJOR_CATEGORY_NAME) IS NULL)
and (FCB.SEGMENT2 IN (:P_MINOR_CATEGORY_NAME) OR LEAST(:P_MINOR_CATEGORY_NAME) IS NULL)
AND NVL(FDH.LOCATION_ID,-1) BETWEEN NVL(:P_FROM_LOCATION_ID, NVL(FDH.LOCATION_ID,-1)) AND NVL(:P_TO_LOCATION_ID, NVL(FDH.LOCATION_ID,-1))
AND FA.ASSET_NUMBER BETWEEN NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER) AND NVL(:P_TO_ASSET_NO, NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER))
AND FB.BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')
AND EXISTS (select 'x' from FA_CATEGORY_BOOKS where book_type_code = :p_book_type_code and category_id = fcb.category_id)

Receipt Accounting SQL in Fusion Receivables and Transaction Accounting SQL in Fusion Receivables

SELECT
 xte.entity_code, 
xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 222
    AND xah.application_id = 222
    AND xte.application_id = 222
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='RECEIPTS'
and gled.ledger_id= :LEDGER_ID
and  xte.source_id_int_1 = :m_cash_receipt_id
order by  xal.accounted_dr,     xal.displayed_line_number

###################################################
Receivables Invoice or Transaction Accounting SQL
###################################################

SELECT
 xte.entity_code, 
xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 222
    AND xah.application_id = 222
    AND xte.application_id = 222
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='TRANSACTIONS'
and gled.ledger_id= :LEDGER_ID
and  xte.source_id_int_1 = :RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
order by  xal.accounted_dr,     xal.displayed_line_number

##########################
select primary_ledger_id from fun_fin_business_units_v where bu_name like 'XX%'

Payment Accounting SQL: Oracle Fusion R13

Payment Accounting SQL

SELECT
    xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 200
    AND xah.application_id = 200
    AND xte.application_id = 200
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='AP_PAYMENTS'
order by  xal.accounted_dr,     xal.displayed_line_number

Tax Registration Number

-- Getting from Party Site

    (
        SELECT
            zr.registration_number trn_customer_site
        FROM
            zx_party_tax_profile zptf,
            zx_registrations zr
        WHERE
            zptf.party_id = hps.party_site_id
            AND zptf.party_tax_profile_id = zr.party_tax_profile_id
            AND zptf.party_type_code = 'THIRD_PARTY_SITE'
    ) trn_customer_site,

AR Receipts Report

SELECT
 ACRA.RECEIPT_DATE
,ACRA.PAY_FROM_CUSTOMER
,ACRA.MISC_PAYMENT_SOURCE
,ACRA.TYPE
,ACRA.RECEIPT_NUMBER
,case when ACRA.TYPE != 'MISC' then (select hp.party_name from hz_parties hp, HZ_CUST_ACCOUNTS hca where hca.cust_account_id = ACRA.PAY_FROM_CUSTOMER and hca.party_id = hp.party_id)
 else ACRA.MISC_PAYMENT_SOURCE
 end CUSTOMER_NAME
,substr(arm.name,instr(ARM.name,'-')+1,length(arm.name)) receipt_method
,ACRA.AMOUNT
,(select IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(ACRA.AMOUNT,'AED',2) from dual) AMT_Words
,ACRA.COMMENTS Remark
,ARM.printed_name
,ACRA.created_by
,sysdate PRINTING_DATE
FROM
AR_RECEIPT_METHODS                 ARM
,AR_CASH_RECEIPTS_ALL               ACRA
WHERE 1=1
AND ARM.RECEIPT_METHOD_ID               =               ACRA.RECEIPT_METHOD_ID
and acra.org_id =:p_bu_id