GL Drilldown to FA, FA Drilldown to AP, AP Drillup to GL for Asset Clearing Account

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

SELECT GJBF.NAME FA_GL_BATCH_NAME,
GJHF.NAME FA_GL_JOURNAL_NAME,
GJLF.JE_LINE_NUM FA_GL_JE_LINE_NUMBER,
XEF.EVENT_ID,
GLF.NAME LEDGER_NAME ,
GLF.CURRENCY_CODE LEDGER_CURRENCY ,
XAHF.ACCOUNTING_DATE GL_DATE,
XALF.ACCOUNTED_DR ,
XALF.ACCOUNTED_CR ,
FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#', GLF.CHART_OF_ACCOUNTS_ID, GJLF.CODE_COMBINATION_ID) ACCOUNT ,
XLA_OA_FUNCTIONS_PKG.GET_CCID_DESCRIPTION (GLF.CHART_OF_ACCOUNTS_ID, GJLF.CODE_COMBINATION_ID) ACCOUNT_DESCRIPTION ,
FTH.TRANSACTION_HEADER_ID,
FTH.TRANSACTION_TYPE_CODE,
ADB.ASSET_NUMBER,
ADB.ASSET_NUMBER
||' - '
||ADTL.DESCRIPTION ASSET_NUMBER_DESC ,
FTH.TRANSACTION_DATE_ENTERED TRANSACTION_DATE_ENTERED ,
CP.PERIOD_NAME PERIOD_EFFECTIVE ,
--FTH.DATE_EFFECTIVE DATE_EFFECTIVE ,
DP.PERIOD_COUNTER PERIOD_COUNTER ,
DP.PERIOD_NAME PERIOD_ENTERED,
INV.INVOICE_NUM,
FAI.INVOICE_LINE_NUMBER,
FAI.AP_DISTRIBUTION_LINE_NUMBER,
--AP SLA Columns
FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#', GLP.CHART_OF_ACCOUNTS_ID, GJLP.CODE_COMBINATION_ID) AP_ACCOUNT ,
XLA_OA_FUNCTIONS_PKG.GET_CCID_DESCRIPTION (GLP.CHART_OF_ACCOUNTS_ID, GJLP.CODE_COMBINATION_ID) AP_ACCOUNT_DESCRIPTION ,
XALP.ACCOUNTED_DR AP_ACCOUNTED_DR,
XALP.ACCOUNTED_CR AP_ACCOUNTED_CR,
GLP.CURRENCY_CODE AP_LEDGER_CURRENCY ,
XAHP.ACCOUNTING_DATE AP_GL_DATE,
GLP.NAME AP_LEDGER_NAME ,
GJBP.NAME AP_GL_BATCH_NAME,
GJHP.NAME AP_GL_JOURNAL_NAME,
GJLP.JE_LINE_NUM AP_GL_JE_LINE_NUMBER,
GJHF.JE_CATEGORY FA_JE_CATEGORY,
GJHF.JE_SOURCE FA_JE_SO,
GJHF.PERIOD_NAME FA_PERIOD_NAME,
GJHP.JE_CATEGORY AP_JE_CATEGORY,
GJHP.JE_SOURCE AP_JE_SO,
GJHP.PERIOD_NAME AP_PERIOD_NAME
FROM GL_JE_BATCHES GJBF,
GL_JE_HEADERS GJHF,
GL_JE_LINES GJLF,
GL_IMPORT_REFERENCES GIRF,
XLA_AE_LINES XALF,
XLA_AE_HEADERS XAHF,
XLA_EVENTS XEF,
XLA_GL_LEDGERS_V GLF,
gl_period_statuses gps,
gl_ledgers cash_led,
--FA_TRANSACTION_HISTORY_TRX_V tables
FA_LOOKUPS_B LOB,
FA_LOOKUPS_TL LOTL,
FA_CATEGORIES CT,
FA_CALENDAR_PERIODS CP,
FA_DEPRN_PERIODS DP,
FA_TRANSACTION_HEADERS FTH,
FA_ASSET_HISTORY AH,
FA_ADDITIONS_B ADB,
FA_ADDITIONS_TL ADTL,
FA_BOOK_CONTROLS BC,
FA_TRX_REFERENCES TRX,
-- AP Invoices
FA_ASSET_INVOICES FAI ,
AP_INVOICES_ALL INV,
AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST,
-- AP SLA GL Link
GL_JE_BATCHES GJBP,
GL_JE_HEADERS GJHP,
GL_JE_LINES GJLP,
GL_IMPORT_REFERENCES GIRP,
XLA_AE_LINES XALP,
XLA_AE_HEADERS XAHP,
XLA_EVENTS XEP,
XLA_GL_LEDGERS_V GLP
WHERE GJHF.JE_BATCH_ID = GJBF.JE_BATCH_ID
AND GJLF.JE_HEADER_ID = GJHF.JE_HEADER_ID
AND GIRF.JE_BATCH_ID = GJBF.JE_BATCH_ID
AND GIRF.JE_HEADER_ID = GJHF.JE_HEADER_ID
AND GIRF.JE_LINE_NUM = GJLF.JE_LINE_NUM
AND GIRF.GL_SL_LINK_ID = XALF.GL_SL_LINK_ID
AND XALF.AE_HEADER_ID = XAHF.AE_HEADER_ID
AND XAHF.EVENT_ID = XEF.EVENT_ID
AND XEF.EVENT_ID = FTH.EVENT_ID
AND GLF.LEDGER_ID = XAHF.LEDGER_ID
and GJHF.JE_SOURCE = 'Assets'
and gps.period_name = GJHF.period_name
and gps.application_id = 101
and gps.set_of_books_id = GJHF.ledger_id
and GJHF.ledger_id = cash_led.ledger_id
and cash_led.name not like '%CASH%'
--FA_TRANSACTION_HISTORY_TRX_V tables Joins
AND AH.ASSET_ID = ADB.ASSET_ID
AND ADB.ASSET_ID = ADTL.ASSET_ID
AND ADTL.LANGUAGE = USERENV('LANG')
AND DP.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND CP.CALENDAR_TYPE = BC.DEPRN_CALENDAR
AND CT.CATEGORY_ID = AH.CATEGORY_ID
AND LOB.LOOKUP_TYPE = 'FAXOLTRX'
AND LOB.LOOKUP_CODE = FTH.TRANSACTION_TYPE_CODE
AND LOB.LOOKUP_TYPE = LOTL.LOOKUP_TYPE
AND LOB.LOOKUP_CODE = LOTL.LOOKUP_CODE
AND LOTL.LANGUAGE = USERENV('LANG')
AND FTH.ASSET_ID = ADB.ASSET_ID
AND FTH.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
AND FTH.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN
AND FTH.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT, FTH.TRANSACTION_HEADER_ID + 1)
AND FTH.TRANSACTION_DATE_ENTERED BETWEEN CP.START_DATE AND CP.END_DATE
AND FTH.DATE_EFFECTIVE BETWEEN DP.PERIOD_OPEN_DATE AND NVL(DP.PERIOD_CLOSE_DATE,FTH.DATE_EFFECTIVE)
AND FTH.TRX_REFERENCE_ID = TRX.TRX_REFERENCE_ID(+)
-- AP Invoices
AND FAI.INVOICE_TRANSACTION_ID_IN = FTH.INVOICE_TRANSACTION_ID
AND FAI.INVOICE_ID = INV.INVOICE_ID
AND INV.INVOICE_ID = AP_DIST.INVOICE_ID
AND AP_DIST.INVOICE_LINE_NUMBER = FAI.INVOICE_LINE_NUMBER
AND AP_DIST.DISTRIBUTION_LINE_NUMBER = FAI.AP_DISTRIBUTION_LINE_NUMBER
AND AP_DIST.INVOICE_DISTRIBUTION_ID = FAI.INVOICE_DISTRIBUTION_ID
--AP SLA GL Link
AND GJHP.JE_BATCH_ID = GJBP.JE_BATCH_ID
AND GJLP.JE_HEADER_ID = GJHP.JE_HEADER_ID
AND GIRP.JE_BATCH_ID = GJBP.JE_BATCH_ID
AND GIRP.JE_HEADER_ID = GJHP.JE_HEADER_ID
AND GIRP.JE_LINE_NUM = GJLP.JE_LINE_NUM
AND GIRP.GL_SL_LINK_ID = XALP.GL_SL_LINK_ID
AND XALP.AE_HEADER_ID = XAHP.AE_HEADER_ID
AND XAHP.EVENT_ID = XEP.EVENT_ID
AND XEP.EVENT_ID = AP_DIST.ACCOUNTING_EVENT_ID
AND GLP.LEDGER_ID = XAHP.LEDGER_ID
AND GLF.LEDGER_ID = GLP.LEDGER_ID
AND GJLF.CODE_COMBINATION_ID =XALP.CODE_COMBINATION_ID
--AND GJBF.NAME = 'USD_CASH2022(USD): Assets A 443803 1853643 587894'
--AND GJHF.NAME = 'GBP_CASH: 01-2016 CIP Adjustment USD'
--AND GJLF.JE_LINE_NUM = 2
--and XEF.EVENT_ID = 1501523
and &WHERE_LEDGER_COND
and gps.EFFECTIVE_PERIOD_NUM BETWEEN :START_EFFECTIVE_PERIOD_NUM
and :END_EFFECTIVE_PERIOD_NUM;