SELECT glcc.segment1 COMPANY,
glcc.segment2 LOCATION,
glcc.segment3 COST_CENTER,
glcc.segment4 ACCOUNT,
glcc.segment5 PRODUCT_LINE,
glcc.segment6 CHANNEL,
glcc.segment7 PROJECT,
(SELECT flex_value
|| ' '
|| fvt.description
FROM apps.ap_invoices_all ap1,
apps.gl_code_combinations glc,
apps.fnd_flex_values fv,
apps.fnd_flex_values_tl fvt
WHERE aia.accts_pay_code_combination_id = glc.code_combination_id
AND glc.segment4 = fv.flex_value
AND fv.flex_value_set_id = 1002653
AND ap1.invoice_id = aia.invoice_id
AND fv.flex_value_id = fvt.flex_value_id
) CODE_COMBO_DESC,
GJH.POSTED_DATE POSTED_ON_DT,
20141130 MONTH_DT_WID,
gjh.JE_CATEGORY,
gjh.je_header_id gl_journal_id,
gjh.name DOCUMENT_NAME,
gjh.external_reference acct_je_line_desc,
XAL.DESCRIPTION JE_LINE_DESC,
pv.vendor_name vendor_customer,
PV.SEGMENT1 VENDOR_NUMBER,
(SELECT PAP.EMPLOYEE_NUMBER
FROM APPS.PER_ALL_PEOPLE_F PAP
WHERE PAP.person_id = PV.EMPLOYEE_ID
AND rownum =1
) Employee_number,
xal.accounting_class_code transaction_type,
aia.invoice_num transaction_number,
'' MOD_TRANSACTION_NUMBER,
CASE
WHEN XAL.ENTERED_DR IS NULL
THEN GJL.ENTERED_DR
ELSE XAL.ENTERED_DR
END GLOBAL_DR,
CASE
WHEN XAL.ENTERED_cR IS NULL
THEN gjl.ENTERED_cR
ELSE XAL.ENTERED_cR
END GLOBAL_CR,
XAL.CURRENCY_CODE GLOBAL_CUR,
CASE
WHEN XAL.ACCOUNTED_CR IS NULL
THEN GJL.ACCOUNTED_CR
ELSE XAL.ACCOUNTED_CR
END LOCAL_CR,
CASE
WHEN XAL.ACCOUNTED_DR IS NULL
THEN GJL.ACCOUNTED_DR
ELSE XAL.ACCOUNTED_DR
END LOCAL_DR,
APAD.amount TRANSACTION_AMOUNT,
aia.invoice_currency_code TRANSACTION_CURR_CODE,
GL.CURRENCY_CODE LOCAL_CUR,
gjh.period_name fiscal_period,
(GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR) begin_balance,
( GB.PERIOD_NET_DR - GB.PERIOD_NET_CR + GB.PROJECT_TO_DATE_DR - GB.PROJECT_TO_DATE_CR ) END_BALANCE,
GL.name Ledger_name
FROM APPS.AP_INVOICES_ALL AIA,
APPS.AP_INVOICE_LINES_ALL AILA,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
APPS.AP_PREPAY_APP_DISTS APAD,
apps.xla_events xe,
APPS.XLA_DISTRIBUTION_LINKS XDL,
XLA.XLA_TRANSACTION_ENTITIES XTE ,
XLA.XLA_AE_HEADERS XAH ,
XLA.XLA_AE_LINES XAL ,
APPS.GL_CODE_COMBINATIONS GLCC ,
APPS.GL_IMPORT_REFERENCES GIR ,
APPS.GL_JE_LINES GJL ,
apps.gl_je_headers gjh ,
APPS.AP_SUPPLIERS PV ,
APPS.GL_LEDGERS GL,
apps.gl_balances gb
WHERE 1 =1
AND aia.vendor_id = pv.vendor_id
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND XTE.LEDGER_ID = GL.LEDGER_ID
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND aida.line_type_lookup_code ='PREPAY'
AND aida.invoice_distribution_id = apad.prepay_app_distribution_id
AND NVL(XTE.SOURCE_ID_INT_1,-99) = AIA.INVOICE_ID
AND XTE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAL.LEDGER_ID = GJH.LEDGER_ID
AND XAL.APPLICATION_ID = xte.APPLICATION_ID
AND GLCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND gir.JE_HEADER_ID = gjl.JE_HEADER_ID
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_header_id = gjl.je_header_id
AND GJH.LEDGER_ID = XAL.LEDGER_ID
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GB.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
AND GB.PERIOD_NAME = GJH.PERIOD_NAME
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE
AND XAL.AE_HEADER_ID = xdl.AE_HEADER_ID
AND XAL.AE_LINE_NUM = xdl.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_PREPAY'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = APAD.PREPAY_APP_DIST_ID
AND APAD.AMOUNT <>0
AND XDL.EVENT_ID = XE.EVENT_ID
AND xe.application_id = xte.application_id
AND GJH.PERIOD_NAME = 'NOV-14'
AND GLCC.SEGMENT4 = '20600'
glcc.segment2 LOCATION,
glcc.segment3 COST_CENTER,
glcc.segment4 ACCOUNT,
glcc.segment5 PRODUCT_LINE,
glcc.segment6 CHANNEL,
glcc.segment7 PROJECT,
(SELECT flex_value
|| ' '
|| fvt.description
FROM apps.ap_invoices_all ap1,
apps.gl_code_combinations glc,
apps.fnd_flex_values fv,
apps.fnd_flex_values_tl fvt
WHERE aia.accts_pay_code_combination_id = glc.code_combination_id
AND glc.segment4 = fv.flex_value
AND fv.flex_value_set_id = 1002653
AND ap1.invoice_id = aia.invoice_id
AND fv.flex_value_id = fvt.flex_value_id
) CODE_COMBO_DESC,
GJH.POSTED_DATE POSTED_ON_DT,
20141130 MONTH_DT_WID,
gjh.JE_CATEGORY,
gjh.je_header_id gl_journal_id,
gjh.name DOCUMENT_NAME,
gjh.external_reference acct_je_line_desc,
XAL.DESCRIPTION JE_LINE_DESC,
pv.vendor_name vendor_customer,
PV.SEGMENT1 VENDOR_NUMBER,
(SELECT PAP.EMPLOYEE_NUMBER
FROM APPS.PER_ALL_PEOPLE_F PAP
WHERE PAP.person_id = PV.EMPLOYEE_ID
AND rownum =1
) Employee_number,
xal.accounting_class_code transaction_type,
aia.invoice_num transaction_number,
'' MOD_TRANSACTION_NUMBER,
CASE
WHEN XAL.ENTERED_DR IS NULL
THEN GJL.ENTERED_DR
ELSE XAL.ENTERED_DR
END GLOBAL_DR,
CASE
WHEN XAL.ENTERED_cR IS NULL
THEN gjl.ENTERED_cR
ELSE XAL.ENTERED_cR
END GLOBAL_CR,
XAL.CURRENCY_CODE GLOBAL_CUR,
CASE
WHEN XAL.ACCOUNTED_CR IS NULL
THEN GJL.ACCOUNTED_CR
ELSE XAL.ACCOUNTED_CR
END LOCAL_CR,
CASE
WHEN XAL.ACCOUNTED_DR IS NULL
THEN GJL.ACCOUNTED_DR
ELSE XAL.ACCOUNTED_DR
END LOCAL_DR,
APAD.amount TRANSACTION_AMOUNT,
aia.invoice_currency_code TRANSACTION_CURR_CODE,
GL.CURRENCY_CODE LOCAL_CUR,
gjh.period_name fiscal_period,
(GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR) begin_balance,
( GB.PERIOD_NET_DR - GB.PERIOD_NET_CR + GB.PROJECT_TO_DATE_DR - GB.PROJECT_TO_DATE_CR ) END_BALANCE,
GL.name Ledger_name
FROM APPS.AP_INVOICES_ALL AIA,
APPS.AP_INVOICE_LINES_ALL AILA,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
APPS.AP_PREPAY_APP_DISTS APAD,
apps.xla_events xe,
APPS.XLA_DISTRIBUTION_LINKS XDL,
XLA.XLA_TRANSACTION_ENTITIES XTE ,
XLA.XLA_AE_HEADERS XAH ,
XLA.XLA_AE_LINES XAL ,
APPS.GL_CODE_COMBINATIONS GLCC ,
APPS.GL_IMPORT_REFERENCES GIR ,
APPS.GL_JE_LINES GJL ,
apps.gl_je_headers gjh ,
APPS.AP_SUPPLIERS PV ,
APPS.GL_LEDGERS GL,
apps.gl_balances gb
WHERE 1 =1
AND aia.vendor_id = pv.vendor_id
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND XTE.LEDGER_ID = GL.LEDGER_ID
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND aida.line_type_lookup_code ='PREPAY'
AND aida.invoice_distribution_id = apad.prepay_app_distribution_id
AND NVL(XTE.SOURCE_ID_INT_1,-99) = AIA.INVOICE_ID
AND XTE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAL.LEDGER_ID = GJH.LEDGER_ID
AND XAL.APPLICATION_ID = xte.APPLICATION_ID
AND GLCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND gir.JE_HEADER_ID = gjl.JE_HEADER_ID
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_header_id = gjl.je_header_id
AND GJH.LEDGER_ID = XAL.LEDGER_ID
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GB.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
AND GB.PERIOD_NAME = GJH.PERIOD_NAME
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE
AND XAL.AE_HEADER_ID = xdl.AE_HEADER_ID
AND XAL.AE_LINE_NUM = xdl.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_PREPAY'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = APAD.PREPAY_APP_DIST_ID
AND APAD.AMOUNT <>0
AND XDL.EVENT_ID = XE.EVENT_ID
AND xe.application_id = xte.application_id
AND GJH.PERIOD_NAME = 'NOV-14'
AND GLCC.SEGMENT4 = '20600'
No comments:
Post a Comment