Every client wants to have a Cost Report which will actually show the transaction details of each entry in GL. For example users wants to know what is the source of the Actual entry in GL. Source means I meant Sub-ledgers ( Finance , HRMS , Procurement , EAM , Project , Etc.. )
Lets have a look into the SQL query which can be used for report either in EBS or BI.
/* Formatted on 5/12/2016 8:52:31 AM (QP5 v5.126.903.23003) */
SELECT LEDGER_ID,
CODE_COMBINATION_ID,
JE_SOURCE,
PO_NUMBER,
JE_NAME,
BATCH_NAME,
(REGEXP_REPLACE (
REGEXP_REPLACE (PO_DESCRIPTION,
'[^' || CHR (32) || '-' || CHR (126) || ']'),
' '
))
PO_DESCRIPTION,
functional_amount,
transaction_currency,
functional_currency,
transaction_amount,
transaction_gl_date,
user_je_category_name,
supplier_id,
NVL (
(REGEXP_REPLACE (
REGEXP_REPLACE (REF_DESCRIPTION,
'[^' || CHR (32) || '-' || CHR (126) || ']'),
' '
)),
description
)
REF_DESCRIPTION,
NVL (NVL (transaction_reference, DOC_SEQUENCE_VALUE), description)
transaction_reference,
PROJECT_ID
FROM ( SELECT (REGEXP_REPLACE (
REGEXP_REPLACE (
gjl.description,
'[^' || CHR (32) || '-' || CHR (126) || ']'
),
' '
))
description,
USER_JE_SOURCE_NAME "JE_SOURCE",
GL1.LEDGER_ID LEDGER_ID,
GCC.CODE_COMBINATION_ID CODE_COMBINATION_ID,
gcc.segment3 ac_code,
gcc.chart_of_accounts_id,
gjh.DOC_SEQUENCE_VALUE,
(SELECT --FFH.PARENT_FLEX_VALUE
DECODE (COUNT (1),
1,
MAX (FFH.PARENT_FLEX_VALUE))
FROM FND_FLEX_VALUES_VL FFV,
FND_FLEX_VALUE_SETS FFS,
FND_FLEX_VALUE_HIERARCHIES FFH
WHERE FLEX_VALUE = GCC.SEGMENT3
AND FFS.FLEX_VALUE_SET_NAME =
'XXOLNG_GL_Accounts'
AND FFS.FLEX_VALUE_SET_ID =
FFV.FLEX_VALUE_SET_ID
AND FFV.SUMMARY_FLAG = 'N'
AND FFS.FLEX_VALUE_SET_ID =
FFH.FLEX_VALUE_SET_ID
AND GCC.SEGMENT3 BETWEEN FFH.CHILD_FLEX_VALUE_LOW
AND FFH.CHILD_FLEX_VALUE_HIGH)
Parent_Account,
apps.gl_flexfields_pkg.get_description_sql (
gcc.chart_of_accounts_id,
3,
gcc.segment3
)
ac_name,
TRIM(TO_CHAR (
(SUM(NVL (XAL.ACCOUNTED_DR, 0)
- NVL (XAL.ACCOUNTED_CR, 0))),
'9999999999999999999999.00'
))
functional_amount,
GJH.CURRENCY_CODE transaction_currency,
'USD' functional_currency,
TRIM(TO_CHAR (
SUM(NVL (XAL.ENTERED_DR, 0)
- NVL (XAL.ENTERED_CR, 0)),
'9999999999999999999999.00'
))
transaction_amount,
TO_CHAR (GJH.DEFAULT_EFFECTIVE_DATE, 'DD-MON-YYYY')
transaction_gl_date,
GJC.user_je_category_name,
GJH.period_name,
GCC.SEGMENT2 department_code,
APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2
)
department_name,
GCC.SEGMENT4 loc_code,
APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GCC.CHART_OF_ACCOUNTS_ID,
4,
GCC.SEGMENT4
)
loc_description,
DECODE (
GJH.JE_CATEGORY,
'Purchase Invoices',
(SELECT DISTINCT AI.INVOICE_NUM
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICES_ALL AI
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AID.INVOICE_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_INV_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND ROWNUM = 1),
'Payments',
(SELECT DISTINCT ACA.CHECK_NUMBER
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE AIPA.INVOICE_ID =
XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND ACA.CHECK_ID = AIPA.CHECK_ID
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_PMT_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND ROWNUM = 1),
'Receipts',
(SELECT DISTINCT ACR.RECEIPT_NUMBER
FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_CASH_RECEIPTS_ALL ACR
WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID
AND ACR.CASH_RECEIPT_ID =
ACRH.CASH_RECEIPT_ID),
'Sales Invoices',
(SELECT DISTINCT RCTA.TRX_NUMBER
FROM XLA_DISTRIBUTION_LINKS XDL,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUSTOMER_TRX_ALL RCTA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND RCTLG.CUST_TRX_LINE_GL_DIST_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND RCTLG.CUSTOMER_TRX_ID =
RCTA.CUSTOMER_TRX_ID),
'Debit Memos',
(SELECT DISTINCT RCTA.TRX_NUMBER
FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUSTOMER_TRX_ALL RCTA
WHERE RCTLG.EVENT_ID = XAH.EVENT_ID
AND RCTLG.CODE_COMBINATION_ID =
XAL.CODE_COMBINATION_ID
AND RCTLG.CUSTOMER_TRX_ID =
RCTA.CUSTOMER_TRX_ID),
'Credit Memos',
(SELECT DISTINCT RCTA.TRX_NUMBER
FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUSTOMER_TRX_ALL RCTA
WHERE RCTLG.EVENT_ID = XAH.EVENT_ID
AND RCTLG.CODE_COMBINATION_ID =
XAL.CODE_COMBINATION_ID
AND RCTLG.CUSTOMER_TRX_ID =
RCTA.CUSTOMER_TRX_ID),
'Addition',
(SELECT DISTINCT FAB.ASSET_NUMBER
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1),
'Adjustment',
(SELECT DISTINCT FAB.ASSET_NUMBER
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1),
'Retirement',
(SELECT DISTINCT FAB.ASSET_NUMBER
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1),
'Depreciation',
(SELECT DISTINCT FAB.ASSET_NUMBER
FROM FA_ADDITIONS_B FAB, FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FAB.ASSET_ID = FTL.ASSET_ID
AND FAB.ASSET_ID = XTE.SOURCE_ID_INT_1),
'Bank Transfers',
(SELECT DISTINCT TRXN_REFERENCE_NUMBER
FROM XLA_DISTRIBUTION_LINKS XDL, CE_CASHFLOWS CC
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'CE_TRANS'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
CC.CASHFLOW_ID),
'Receiving',
(SELECT DISTINCT RSH.RECEIPT_NUM
FROM XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'RCV_RECEIVING_SUB_LEDGER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID =
RT.SHIPMENT_HEADER_ID),
'Inventory',
(SELECT DISTINCT MTA.TRANSACTION_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
MTL_TRANSACTION_ACCOUNTS MTA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'MTL_TRANSACTION_ACCOUNTS'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
MTA.INV_SUB_LEDGER_ID),
'Purchases',
(SELECT DISTINCT POH.SEGMENT1
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL,
PO_DISTRIBUTIONS_ALL POD,
XLA_DISTRIBUTION_LINKS XDL
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_DISTRIBUTIONS_ALL'
AND POD.PO_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND POD.LINE_LOCATION_ID =
POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND ROWNUM = 1),
'Reconciled Payments',
(SELECT DISTINCT ACA.CHECK_NUMBER
FROM XLA_DISTRIBUTION_LINKS XDL, AP_CHECKS_ALL ACA
WHERE ACA.CHECK_ID = XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_PMT_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID),
'Misc Receipts',
(SELECT DISTINCT ACR.RECEIPT_NUMBER
FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_CASH_RECEIPTS_ALL ACR
WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID
AND ACR.CASH_RECEIPT_ID =
ACRH.CASH_RECEIPT_ID),
'Requisitions',
(SELECT DISTINCT PRHA.SEGMENT1
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQUISITION_HEADERS_ALL PRHA
WHERE XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_REQ_DISTRIBUTIONS_ALL'
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID =
PRHA.REQUISITION_HEADER_ID),
'Release',
(SELECT DISTINCT PRHA.SEGMENT1
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PRHA
WHERE XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_REQ_DISTRIBUTIONS_ALL'
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
PRDA.REQ_DISTRIBUTION_ID
AND PRDA.PO_HEADER_ID = PRHA.PO_HEADER_ID),
'WIP',
(SELECT DISTINCT WE.WIP_ENTITY_NAME
FROM WIP_ENTITIES WE,
WIP_TRANSACTIONS WI,
WIP_TRANSACTION_ACCOUNTS WTA,
XLA_DISTRIBUTION_LINKS XDL
WHERE 1 = 1
AND WI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
WTA.WIP_SUB_LEDGER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID)
)
transaction_reference,
DECODE (
GJH.JE_CATEGORY,
'Receiving',
(SELECT DISTINCT PRHA.SEGMENT1
FROM apps.XLA_DISTRIBUTION_LINKS XDL,
apps.RCV_RECEIVING_SUB_LEDGER RRSL,
apps.RCV_TRANSACTIONS RT,
--PO_HEADERS_ALL POH,
apps.PO_DISTRIBUTIONS_ALL PDA,
apps.PO_REQ_DISTRIBUTIONS_ALL PRDA,
apps.PO_REQUISITION_LINES_ALL PRLA,
apps.PO_REQUISITION_HEADERS_ALL PRHA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = xal.aE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RT.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PDA.REQ_DISTRIBUTION_ID =
PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRHA.REQUISITION_HEADER_ID =
PRLA.REQUISITION_HEADER_ID
AND ROWNUM = 1),
'WIP',
(SELECT DISTINCT PRHA.SEGMENT1
FROM WIP_TRANSACTIONS WI,
WIP_TRANSACTION_ACCOUNTS WTA,
XLA_DISTRIBUTION_LINKS XDL,
RCV_TRANSACTIONS RT,
po_distributions_all PDA,
po_req_distributions_all PRDA,
po_REQUISITION_lines_all PRLA,
PO_REQUISITION_HEADERS_ALL PRHA
WHERE 1 = 1
AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
WTA.WIP_SUB_LEDGER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND WI.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PRDA.DISTRIBUTION_ID =
PDA.REQ_DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID =
PRHA.REQUISITION_HEADER_ID
AND ROWNUM = 1),
'Inventory',
(SELECT DISTINCT PHA.SEGMENT1
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_HEADERS_ALL PHA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'MTL_TRANSACTION_ACCOUNTS'
AND PHA.po_header_id =
XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND ROWNUM = 1),
'Purchase Invoices',
(SELECT DISTINCT PHA.SEGMENT1
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICES_ALL AI,
PO_HEADERS_ALL PHA,
PO_DISTRIBUTIONS_ALL PDA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AID.INVOICE_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_INV_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND AID.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND ROWNUM = 1)
)
PO_NUMBER,
DECODE (
GJH.JE_CATEGORY,
'Receiving',
(SELECT DISTINCT PRLA.ITEM_DESCRIPTION
FROM apps.XLA_DISTRIBUTION_LINKS XDL,
apps.RCV_RECEIVING_SUB_LEDGER RRSL,
apps.RCV_TRANSACTIONS RT,
apps.PO_DISTRIBUTIONS_ALL PDA,
apps.PO_REQ_DISTRIBUTIONS_ALL PRDA,
apps.PO_REQUISITION_LINES_ALL PRLA,
apps.PO_REQUISITION_HEADERS_ALL PRHA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = xal.aE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RT.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PDA.REQ_DISTRIBUTION_ID =
PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRHA.REQUISITION_HEADER_ID =
PRLA.REQUISITION_HEADER_ID
AND ROWNUM = 1),
'WIP',
(SELECT DISTINCT PRLA.ITEM_DESCRIPTION
FROM WIP_TRANSACTIONS WI,
WIP_TRANSACTION_ACCOUNTS WTA,
XLA_DISTRIBUTION_LINKS XDL,
RCV_TRANSACTIONS RT,
po_distributions_all PDA,
po_req_distributions_all PRDA,
po_REQUISITION_lines_all PRLA,
PO_REQUISITION_HEADERS_ALL PRHA
WHERE 1 = 1
AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
WTA.WIP_SUB_LEDGER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND WI.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PRDA.DISTRIBUTION_ID =
PDA.REQ_DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID =
PRHA.REQUISITION_HEADER_ID
AND ROWNUM = 1),
'Inventory',
(SELECT DISTINCT PHA.COMMENTS
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_HEADERS_ALL PHA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'MTL_TRANSACTION_ACCOUNTS'
AND PHA.po_header_id =
XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND ROWNUM = 1),
'Purchase Invoices',
(SELECT DISTINCT PHA.COMMENTS
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICES_ALL AI,
PO_HEADERS_ALL PHA,
PO_DISTRIBUTIONS_ALL PDA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AID.INVOICE_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_INV_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND AID.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND ROWNUM = 1)
)
PO_DESCRIPTION,
GJH.NAME JE_NAME,
GJB.NAME BATCH_NAME,
DECODE (
GJH.JE_CATEGORY,
'Purchase Invoices',
(SELECT DISTINCT APS.VENDOR_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICES_ALL AI,
ap_suppliers APS
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AID.INVOICE_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_INV_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND APS.VENDOR_ID = AI.VENDOR_ID
AND ROWNUM = 1),
'Reconciled Payments',
(SELECT DISTINCT APS.VENDOR_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_CHECKS_ALL ACA,
ap_suppliers APS
WHERE ACA.CHECK_ID = XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_PMT_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND APS.VENDOR_ID = ACA.VENDOR_ID),
'Payments',
(SELECT DISTINCT APS.VENDOR_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
ap_suppliers APS
WHERE AIPA.INVOICE_ID =
XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND ACA.CHECK_ID = AIPA.CHECK_ID
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_PMT_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND APS.VENDOR_ID = ACA.VENDOR_ID
AND ROWNUM = 1),
'Receiving',
(SELECT DISTINCT APS.VENDOR_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
ap_suppliers APS
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'RCV_RECEIVING_SUB_LEDGER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID =
RT.SHIPMENT_HEADER_ID
AND APS.VENDOR_ID = RSH.VENDOR_ID),
'WIP',
(SELECT DISTINCT APS.VENDOR_ID
FROM PO_LINES_ALL PLA,
WIP_TRANSACTIONS WI,
WIP_TRANSACTION_ACCOUNTS WTA,
XLA_DISTRIBUTION_LINKS XDL,
PO_HEADERS_ALL PHA,
ap_suppliers APS
WHERE 1 = 1
AND PLA.PO_LINE_ID = WI.PO_LINE_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_HEADER_ID = WI.PO_HEADER_ID
AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
WTA.WIP_SUB_LEDGER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND APS.VENDOR_ID = PHA.VENDOR_ID)
)
supplier_ID,
DECODE (
GJH.JE_CATEGORY,
'Purchase Invoices',
(SELECT DISTINCT AID.DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICES_ALL AI
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AID.INVOICE_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_INV_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND ROWNUM = 1),
'Payments',
(SELECT DISTINCT AIA.DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
AP_INVOICES_ALL AIA
WHERE AIPA.INVOICE_ID =
XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND ACA.CHECK_ID = AIPA.CHECK_ID
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_PMT_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND ROWNUM = 1),
'Receipts',
(SELECT DISTINCT ACR.COMMENTS
FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_CASH_RECEIPTS_ALL ACR
WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID
AND ACR.CASH_RECEIPT_ID =
ACRH.CASH_RECEIPT_ID),
'Sales Invoices',
(SELECT DISTINCT RCL.DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCL
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND RCTLG.CUST_TRX_LINE_GL_DIST_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND RCTLG.CUSTOMER_TRX_ID =
RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID =
RCL.CUSTOMER_TRX_ID
AND ROWNUM = 1),
'Debit Memos',
(SELECT DISTINCT RCL.DESCRIPTION
FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCL
WHERE RCTLG.EVENT_ID = XAH.EVENT_ID
AND RCTLG.CODE_COMBINATION_ID =
XAL.CODE_COMBINATION_ID
AND RCTLG.CUSTOMER_TRX_ID =
RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID =
RCL.CUSTOMER_TRX_ID
AND ROWNUM = 1),
'Credit Memos',
(SELECT DISTINCT RCL.DESCRIPTION
FROM RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCL
WHERE RCTLG.EVENT_ID = XAH.EVENT_ID
AND RCTLG.CODE_COMBINATION_ID =
XAL.CODE_COMBINATION_ID
AND RCTLG.CUSTOMER_TRX_ID =
RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID =
RCL.CUSTOMER_TRX_ID
AND ROWNUM = 1),
'Addition',
(SELECT DISTINCT FTL.DESCRIPTION
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1),
'Adjustment',
(SELECT DISTINCT FTL.DESCRIPTION
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1),
'Retirement',
(SELECT DISTINCT FTL.DESCRIPTION
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1),
'Depreciation',
(SELECT DISTINCT FTL.DESCRIPTION
FROM FA_ADDITIONS_B FAB, FA_ADDITIONS_TL FTL
WHERE 1 = 1
AND FAB.ASSET_ID = FTL.ASSET_ID
AND FAB.ASSET_ID = XTE.SOURCE_ID_INT_1),
'Bank Transfers',
(SELECT DISTINCT CC.DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL, CE_CASHFLOWS CC
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'CE_TRANS'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
CC.CASHFLOW_ID),
'Receiving',
(SELECT DISTINCT RSL.ITEM_DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
RCV_RECEIVING_SUB_LEDGER RRSL,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
rcv_shipment_lines RSL
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'RCV_RECEIVING_SUB_LEDGER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID =
RT.SHIPMENT_HEADER_ID
AND RSL.shipment_line_id =
RT.SHIPMENT_LINE_ID),
'Payroll',
GJH.JE_CATEGORY || '-' || GJH.PERIOD_NAME,
'Inventory',
(SELECT DISTINCT MSI.DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
MTL_TRANSACTION_ACCOUNTS MTA,
MTL_SYSTEM_ITEMS_B MSI,
mtl_material_transactions mmt
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = GIR.JE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'MTL_TRANSACTION_ACCOUNTS'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
MTA.INV_SUB_LEDGER_ID
AND mmt.transaction_id = mta.transaction_id
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID =
MMT.INVENTORY_ITEM_ID),
'Purchases',
(SELECT DISTINCT POL.ITEM_DESCRIPTION
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL,
PO_DISTRIBUTIONS_ALL POD,
XLA_DISTRIBUTION_LINKS XDL
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_DISTRIBUTIONS_ALL'
AND POD.PO_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND POD.LINE_LOCATION_ID =
POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND ROWNUM = 1),
'Reconciled Payments',
(SELECT DISTINCT AIA.DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_CHECKS_ALL ACA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_INVOICES_ALL AIA
WHERE ACA.CHECK_ID = XDL.APPLIED_TO_SOURCE_ID_NUM_1
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_PMT_DIST'
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND XDL.EVENT_ID = XAH.EVENT_ID
AND ROWNUM = 1),
'Misc Receipts',
(SELECT DISTINCT ACR.COMMENTS
FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_CASH_RECEIPTS_ALL ACR
WHERE 1 = 1 AND ACRH.EVENT_ID = XAH.EVENT_ID
AND ACR.CASH_RECEIPT_ID =
ACRH.CASH_RECEIPT_ID),
'Requisitions',
(SELECT DISTINCT PRLA.ITEM_DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQUISITION_HEADERS_ALL PRHA
WHERE XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_REQ_DISTRIBUTIONS_ALL'
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID =
PRHA.REQUISITION_HEADER_ID),
'Release',
(SELECT DISTINCT PLA.ITEM_DESCRIPTION
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PRHA,
PO_LINES_ALL PLA
WHERE XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_REQ_DISTRIBUTIONS_ALL'
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
PRDA.REQ_DISTRIBUTION_ID
AND PRDA.PO_HEADER_ID = PRHA.PO_HEADER_ID
AND PRHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND ROWNUM = 1),
'WIP',
(SELECT DISTINCT WE.DESCRIPTION
FROM WIP_ENTITIES WE,
WIP_TRANSACTIONS WI,
WIP_TRANSACTION_ACCOUNTS WTA,
XLA_DISTRIBUTION_LINKS XDL
WHERE 1 = 1
AND WI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
WTA.WIP_SUB_LEDGER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID)
)
REF_DESCRIPTION,
DECODE (
GJH.JE_CATEGORY,
'Receiving',
(SELECT DISTINCT PPA.PROJECT_ID
FROM apps.XLA_DISTRIBUTION_LINKS XDL,
apps.RCV_RECEIVING_SUB_LEDGER RRSL,
apps.RCV_TRANSACTIONS RT,
apps.PO_DISTRIBUTIONS_ALL PDA,
PA_PROJECTS_ALL PPA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = xal.aE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RT.PO_DISTRIBUTION_ID =
PDA.PO_DISTRIBUTION_ID
AND PDA.PROJECT_ID = PPA.PROJECT_ID
AND ROWNUM = 1),
'Purchase Invoices',
(SELECT DISTINCT PPA.PROJECT_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
AP_INVOICES_ALL AI,
PA_PROJECTS_ALL PPA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AID.INVOICE_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'AP_INV_DIST'
AND XDL.EVENT_ID = XAH.EVENT_ID
AND AID.PROJECT_ID = PPA.PROJECT_ID
AND ROWNUM = 1),
'Purchases',
(SELECT DISTINCT PPA.PROJECT_ID
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL,
PO_DISTRIBUTIONS_ALL POD,
XLA_DISTRIBUTION_LINKS XDL,
PA_PROJECTS_ALL PPA
WHERE XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_DISTRIBUTIONS_ALL'
AND POD.PO_DISTRIBUTION_ID =
XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND POD.LINE_LOCATION_ID =
POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PPA.PROJECT_ID = POD.PROJECT_ID
AND ROWNUM = 1),
'Requisitions',
(SELECT DISTINCT PPA.PROJECT_ID
FROM XLA_DISTRIBUTION_LINKS XDL,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQUISITION_HEADERS_ALL PRHA,
PA_PROJECTS_ALL PPA
WHERE XDL.SOURCE_DISTRIBUTION_TYPE =
'PO_REQ_DISTRIBUTIONS_ALL'
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID =
PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID =
PRHA.REQUISITION_HEADER_ID
AND PRDA.PROJECT_ID = PPA.PROJECT_ID),
'WIP',
(SELECT DISTINCT PPA.PROJECT_ID
FROM WIP_ENTITIES WE,
WIP_TRANSACTIONS WI,
WIP_TRANSACTION_ACCOUNTS WTA,
XLA_DISTRIBUTION_LINKS XDL,
PA_PROJECTS_ALL PPA,
WIP_DISCRETE_JOBS WDJ
WHERE 1 = 1
AND WI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WI.TRANSACTION_ID = WTA.TRANSACTION_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 =
WTA.WIP_SUB_LEDGER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND WDJ.PROJECT_ID = PPA.PROJECT_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID),
'Addition',
(SELECT DISTINCT PPA.PROJECT_ID
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL,
FA_ASSET_INVOICES FAI,
PA_PROJECTS_ALL PPA
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1
AND FAI.ASSET_ID = FAB.ASSET_ID
AND FAI.PROJECT_ID IS NOT NULL
AND FAI.PROJECT_ID = PPA.PROJECT_ID
AND ROWNUM = 1),
'Adjustment',
(SELECT DISTINCT PPA.PROJECT_ID
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL,
FA_ASSET_INVOICES FAI,
PA_PROJECTS_ALL PPA
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1
AND FAI.ASSET_ID = FAB.ASSET_ID
AND FAI.PROJECT_ID IS NOT NULL
AND FAI.PROJECT_ID = PPA.PROJECT_ID
AND ROWNUM = 1),
'Retirement',
(SELECT DISTINCT PPA.PROJECT_ID
FROM FA_TRANSACTION_HEADERS FTH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL,
FA_ASSET_INVOICES FAI,
PA_PROJECTS_ALL PPA
WHERE 1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.ASSET_ID = FTL.ASSET_ID
AND FTH.TRANSACTION_HEADER_ID =
XTE.SOURCE_ID_INT_1
AND FAI.ASSET_ID = FAB.ASSET_ID
AND FAI.PROJECT_ID IS NOT NULL
AND FAI.PROJECT_ID = PPA.PROJECT_ID
AND ROWNUM = 1),
'Depreciation',
(SELECT DISTINCT PPA.PROJECT_ID
FROM FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FTL,
FA_ASSET_INVOICES FAI,
PA_PROJECTS_ALL PPA
WHERE 1 = 1
AND FAB.ASSET_ID = FTL.ASSET_ID
AND FAB.ASSET_ID = XTE.SOURCE_ID_INT_1
AND FAI.ASSET_ID = FAB.ASSET_ID
AND FAI.PROJECT_ID IS NOT NULL
AND FAI.PROJECT_ID = PPA.PROJECT_ID
AND ROWNUM = 1)
)
PROJECT_ID,
XTE.ENTITY_ID,
XTE.SOURCE_ID_INT_1,
XAL.AE_HEADER_ID,
GJH.JE_CATEGORY,
XAL.AE_LINE_NUM,
GJL.JE_HEADER_ID
FROM GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
XLA_EVENTS XE,
XLA_TRANSACTION_ENTITIES XTE,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_LEDGERS GL1,
GL_JE_SOURCES GJS,
GL_CODE_COMBINATIONS_KFV GCC,
GL_JE_CATEGORIES GJC,
gl_period_statuses per,
gl_je_batches gjb
WHERE 1 = 1
AND gjh.je_batch_id = gjb.je_batch_id
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XE.EVENT_ID(+) = XAH.EVENT_ID
AND XE.APPLICATION_ID(+) = XAH.APPLICATION_ID
AND XTE.APPLICATION_ID(+) = XAH.APPLICATION_ID
AND XTE.ENTITY_ID(+) = XAH.ENTITY_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GL1.LEDGER_ID = GJH.LEDGER_ID
AND GJS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJC.LANGUAGE = 'US'
AND GJH.STATUS = 'P'
AND GJH.ACTUAL_FLAG = 'A'
AND (NVL (NVL (XAL.ACCOUNTED_CR, XAL.ACCOUNTED_DR), 0)) <>
0
AND gjh.period_name = per.period_name
AND per.ledger_id = gjh.ledger_id
AND per.application_id = 101
AND (TRUNC (TO_DATE (per.start_date)) >=
DECODE (
SUBSTR (:p_period_name_from, 1, 3),
'Adj',
(SELECT start_date
FROM gl_period_statuses
WHERE APPLICATION_ID = 101
AND ledger_id = per.ledger_id
AND PERIOD_NAME = :p_period_name_from),
TRUNC (TO_DATE (:p_period_name_from, 'Mon-yy'))
))
AND (TRUNC (per.end_date) <=
DECODE (
SUBSTR (:p_period_name_to, 1, 3),
'Adj',
(SELECT end_date
FROM gl_period_statuses
WHERE APPLICATION_ID = 101
AND ledger_id = per.ledger_id
AND PERIOD_NAME = :p_period_name_to),
TRUNC(LAST_DAY (
TO_DATE (:p_period_name_to, 'Mon-yy')
))
))
AND gcc.segment3 = '5521005'
GROUP BY GCC.SEGMENT3,
GCC.CHART_OF_ACCOUNTS_ID,
GJH.CURRENCY_CODE,
GJH.DEFAULT_EFFECTIVE_DATE,
GJC.USER_JE_CATEGORY_NAME,
GJH.JE_CATEGORY,
XTE.ENTITY_ID,
XTE.SOURCE_ID_INT_1,
GIR.JE_LINE_NUM,
GJH.PERIOD_NAME,
XAH.DOC_SEQUENCE_ID,
XAH.DOC_SEQUENCE_VALUE,
GJH.NAME,
gjb.NAME,
XAL.CODE_COMBINATION_ID,
GCC.SEGMENT2,
XAL.AE_LINE_NUM,
XAH.EVENT_ID,
XAL.DISPLAYED_LINE_NUMBER,
GCC.SEGMENT4,
USER_JE_SOURCE_NAME,
GL1.NAME,
GJH.DOC_SEQUENCE_VALUE,
gjh.je_header_id,
XAL.AE_HEADER_ID,
GJL.JE_HEADER_ID,
gjh.je_source,
gjl.description,
GL1.LEDGER_ID,
GCC.CODE_COMBINATION_ID)
UNION ALL
SELECT LEDGER_ID,
CODE_COMBINATION_ID,
JE_SOURCE,
PO_NUMBER,
JE_NAME,
BATCH_NAME,
PO_DESCRIPTION,
functional_amount,
transaction_currency,
functional_currency,
transaction_amount,
transaction_gl_date,
user_je_category_name,
supplier,
NVL (REF_DESCRIPTION, description) REF_DESCRIPTION,
DECODE (DOC_SEQUENCE_VALUE, NULL, description, DOC_SEQUENCE_VALUE)
transaction_reference,
NULL PROJECT_NUM
FROM ( SELECT (REGEXP_REPLACE (
REGEXP_REPLACE (
gjl.description,
'[^' || CHR (32) || '-' || CHR (126) || ']'
),
' '
))
description,
USER_JE_SOURCE_NAME "JE_SOURCE",
GL.LEDGER_ID,
GCC.CODE_COMBINATION_ID,
gcc.segment3 ac_code,
gcc.chart_of_accounts_id,
gjh.DOC_SEQUENCE_VALUE,
(SELECT DECODE (COUNT (1),
1,
MAX (FFH.PARENT_FLEX_VALUE))
FROM FND_FLEX_VALUES_VL FFV,
FND_FLEX_VALUE_SETS FFS,
FND_FLEX_VALUE_HIERARCHIES FFH
WHERE FLEX_VALUE = GCC.SEGMENT3
AND FFS.FLEX_VALUE_SET_NAME =
'XXOLNG_GL_Accounts'
AND FFS.FLEX_VALUE_SET_ID =
FFV.FLEX_VALUE_SET_ID
AND FFV.SUMMARY_FLAG = 'N'
AND FFS.FLEX_VALUE_SET_ID =
FFH.FLEX_VALUE_SET_ID
AND GCC.SEGMENT3 BETWEEN FFH.CHILD_FLEX_VALUE_LOW
AND FFH.CHILD_FLEX_VALUE_HIGH)
Parent_Account,
apps.gl_flexfields_pkg.get_description_sql (
gcc.chart_of_accounts_id,
3,
gcc.segment3
)
ac_name,
TRIM(TO_CHAR (
(SUM(NVL (gjl.accounted_dr, 0)
- NVL (gjl.accounted_cr, 0))),
'9999999999999999999999.00'
))
functional_amount,
gjh.currency_code transaction_currency,
'USD' functional_currency,
TRIM(TO_CHAR (
SUM(NVL (gjl.entered_dr, 0)
- NVL (gjl.entered_cr, 0)),
'9999999999999999999999.00'
))
transaction_amount,
TO_CHAR (gjh.default_effective_date, 'DD-MON-YYYY')
transaction_gl_date,
gjc.user_je_category_name,
gjh.period_name,
gcc.segment2 department_code,
apps.gl_flexfields_pkg.get_description_sql (
gcc.chart_of_accounts_id,
2,
gcc.segment2
)
department_name,
gcc.segment4 loc_code,
apps.gl_flexfields_pkg.get_description_sql (
gcc.chart_of_accounts_id,
4,
gcc.segment4
)
loc_description,
NULL transaction_reference,
NULL PO_NUMBER,
GJH.NAME JE_NAME,
gjb.NAME BATCH_NAME,
NULL PO_DESCRIPTION,
NULL supplier,
DECODE (gjh.je_category,
'Payroll',
GJH.JE_CATEGORY || '-' || GJH.PERIOD_NAME)
REF_DESCRIPTION
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations_kfv gcc,
gl_ledgers gl,
gl_period_statuses per,
gl_je_categories gjc,
gl_je_sources gjs,
gl_je_batches gjb
WHERE 1 = 1
AND gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gl.ledger_id = gjh.ledger_id
AND gjh.status = 'P'
AND gjh.actual_flag = 'A'
AND gjh.je_category = gjc.je_category_name
AND gjs.JE_SOURCE_NAME = gjh.JE_SOURCE
AND gjc.LANGUAGE = 'US'
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.period_name = per.period_name
AND per.ledger_id = gjh.ledger_id
AND per.application_id = 101
AND (TRUNC (TO_DATE (per.start_date)) >=
DECODE (
SUBSTR (:p_period_name_from, 1, 3),
'Adj',
(SELECT start_date
FROM gl_period_statuses
WHERE APPLICATION_ID = 101
AND ledger_id = per.ledger_id
AND PERIOD_NAME = :p_period_name_from),
TRUNC (TO_DATE (:p_period_name_from, 'Mon-yy'))
))
AND (TRUNC (per.end_date) <=
DECODE (
SUBSTR (:p_period_name_to, 1, 3),
'Adj',
(SELECT end_date
FROM gl_period_statuses
WHERE APPLICATION_ID = 101
AND ledger_id = per.ledger_id
AND PERIOD_NAME = :p_period_name_to),
TRUNC(LAST_DAY (
TO_DATE (:p_period_name_to, 'Mon-yy')
))
))
AND gcc.segment3 = '5521005'
AND application_id = 101
AND NOT EXISTS
(SELECT 1
FROM GL_IMPORT_REFERENCES GIR1,
XLA_AE_LINES XAL
WHERE GIR1.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GIR1.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID)
GROUP BY gcc.segment3,
gcc.chart_of_accounts_id,
gjh.currency_code,
gjh.default_effective_date,
gjc.user_je_category_name,
gjh.je_category,
gjh.period_name,
gcc.segment2,
gcc.segment4,
gjh.je_header_id,
gjh.je_source,
gl.NAME,
USER_JE_SOURCE_NAME,
GCC.CODE_COMBINATION_ID,
gjl.description,
gcc.chart_of_accounts_id,
gjh.DOC_SEQUENCE_VALUE,
GJH.NAME,
gjb.NAME,
GCC.CODE_COMBINATION_ID,
GL.LEDGER_ID)
