WITH GET_PERIOD_AVG_RATE
AS ( SELECT gper.avg_rate,
gsob.CURRENCY_CODE AS functional_currency,
gper.period_name,
gper.to_currency_code
FROM GL_LOOKUPS lk,
GL_TRANSLATION_RATES gper,
gl_sets_of_books gsob
WHERE lk.lookup_type = 'TRANSLATION_BAL_TYPE'
AND gper.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID
AND lk.lookup_code = gper.actual_flag
GROUP BY gper.avg_rate,
gsob.CURRENCY_CODE,
gper.period_name,
gper.to_currency_code)
SELECT ppat.segment1 project_num,
ppat.name project_name,
hou.name project_org,
hou1.name exp_org,
TO_CHAR (gjl.effective_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS gl_date,
gjh.period_name,
aid.amount invoice_amount,
gjh.currency_code journal_currency,
TO_CHAR (aid.pjc_expenditure_item_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS expenditure_item_date,
gcc.segment2 ACCT,
TO_CHAR (gjh.CURRENCY_CONVERSION_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS CURRENCY_CONVERSION_DATE,
gcc.segment1 Company,
gcc.segment3 Department,
gcc.segment4 Region,
aid.RECEIPT_CURRENCY_CODE,
gjh.je_category je_category,
HP.party_name Supplier_name,
(SELECT PPNF.full_name
FROM per_person_names_f PPNF
WHERE PPNF.person_id = EI.incurred_by_person_id
AND PPNF.name_type = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND NVL ( PPNF.EFFECTIVE_END_DATE, TRUNC (SYSDATE) + 1))
emp_name,
gjl.description journal_description,
gjl.attribute1 Journal_line_DFF,
DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate) usd_conversion_rate,
ROUND ( xal.accounted_dr * DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate), 2) us_accounted_dr,
ROUND ( xal.accounted_cr * DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate), 2) us_accounted_cr,
ei.project_currency_code,
ei.project_burdened_cost,
FROM ap_invoice_distributions_all AID,
ap_invoices_all aia,
ap_invoice_lines_all ail,
hz_parties HP,
PJF_PROJECTS_ALL_vl ppat,
hr_all_organization_units hou,
hr_all_organization_units hou1,
xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_sets_of_books sob,
PJC_EXP_ITEMS_ALL ei,
GET_PERIOD_AVG_RATE gpavg
WHERE aia.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND ail.invoice_id = aid.invoice_id
AND aid.PJC_PROJECT_ID = ppat.project_id
AND hou.organization_id = ppat.carrying_out_organization_id
AND hou1.organization_id = aid.PJC_ORGANIZATION_ID
AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99)) --19004(invoice_id)
AND xe.entity_id = xah.entity_id
AND aid.amount = NVL (xal.accounted_dr, xal.accounted_cr)
AND xte.entity_code = 'AP_INVOICES'
AND xte.application_id = 200
AND xe.event_id = aid.ACCOUNTING_EVENT_ID
AND xte.entity_id = xe.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND UPPER (xal.ACCOUNTING_CLASS_CODE) IN ('ITEM EXPENSE')
AND xte.application_id = xah.application_id
AND gcc.code_combination_id = xal.code_combination_id
AND aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
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 gjl.je_header_id = gjh.je_header_id
AND sob.set_of_books_id = gjh.ledger_id
AND gjh.currency_code = gpavg.functional_currency(+)
AND gjh.period_name = gpavg.period_name(+)
AND gpavg.to_currency_code (+) = 'USD'
AND aia.party_id = HP.party_id
AND ei.vendor_id = aia.vendor_id
AND ei.project_id = aid.pjc_project_id
AND ei.task_id = aid.pjc_task_id
AND aid.org_id = ei.org_id
AND aid.amount = ei.quantity
AND aid.invoice_id = aia.invoice_id
AND aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND ei.ORIG_TRANSACTION_REFERENCE = aid.INVOICE_DISTRIBUTION_ID
AS ( SELECT gper.avg_rate,
gsob.CURRENCY_CODE AS functional_currency,
gper.period_name,
gper.to_currency_code
FROM GL_LOOKUPS lk,
GL_TRANSLATION_RATES gper,
gl_sets_of_books gsob
WHERE lk.lookup_type = 'TRANSLATION_BAL_TYPE'
AND gper.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID
AND lk.lookup_code = gper.actual_flag
GROUP BY gper.avg_rate,
gsob.CURRENCY_CODE,
gper.period_name,
gper.to_currency_code)
SELECT ppat.segment1 project_num,
ppat.name project_name,
hou.name project_org,
hou1.name exp_org,
TO_CHAR (gjl.effective_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS gl_date,
gjh.period_name,
aid.amount invoice_amount,
gjh.currency_code journal_currency,
TO_CHAR (aid.pjc_expenditure_item_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS expenditure_item_date,
gcc.segment2 ACCT,
TO_CHAR (gjh.CURRENCY_CONVERSION_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS CURRENCY_CONVERSION_DATE,
gcc.segment1 Company,
gcc.segment3 Department,
gcc.segment4 Region,
aid.RECEIPT_CURRENCY_CODE,
gjh.je_category je_category,
HP.party_name Supplier_name,
(SELECT PPNF.full_name
FROM per_person_names_f PPNF
WHERE PPNF.person_id = EI.incurred_by_person_id
AND PPNF.name_type = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND NVL ( PPNF.EFFECTIVE_END_DATE, TRUNC (SYSDATE) + 1))
emp_name,
gjl.description journal_description,
gjl.attribute1 Journal_line_DFF,
DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate) usd_conversion_rate,
ROUND ( xal.accounted_dr * DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate), 2) us_accounted_dr,
ROUND ( xal.accounted_cr * DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate), 2) us_accounted_cr,
ei.project_currency_code,
ei.project_burdened_cost,
FROM ap_invoice_distributions_all AID,
ap_invoices_all aia,
ap_invoice_lines_all ail,
hz_parties HP,
PJF_PROJECTS_ALL_vl ppat,
hr_all_organization_units hou,
hr_all_organization_units hou1,
xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_sets_of_books sob,
PJC_EXP_ITEMS_ALL ei,
GET_PERIOD_AVG_RATE gpavg
WHERE aia.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND ail.invoice_id = aid.invoice_id
AND aid.PJC_PROJECT_ID = ppat.project_id
AND hou.organization_id = ppat.carrying_out_organization_id
AND hou1.organization_id = aid.PJC_ORGANIZATION_ID
AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99)) --19004(invoice_id)
AND xe.entity_id = xah.entity_id
AND aid.amount = NVL (xal.accounted_dr, xal.accounted_cr)
AND xte.entity_code = 'AP_INVOICES'
AND xte.application_id = 200
AND xe.event_id = aid.ACCOUNTING_EVENT_ID
AND xte.entity_id = xe.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND UPPER (xal.ACCOUNTING_CLASS_CODE) IN ('ITEM EXPENSE')
AND xte.application_id = xah.application_id
AND gcc.code_combination_id = xal.code_combination_id
AND aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
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 gjl.je_header_id = gjh.je_header_id
AND sob.set_of_books_id = gjh.ledger_id
AND gjh.currency_code = gpavg.functional_currency(+)
AND gjh.period_name = gpavg.period_name(+)
AND gpavg.to_currency_code (+) = 'USD'
AND aia.party_id = HP.party_id
AND ei.vendor_id = aia.vendor_id
AND ei.project_id = aid.pjc_project_id
AND ei.task_id = aid.pjc_task_id
AND aid.org_id = ei.org_id
AND aid.amount = ei.quantity
AND aid.invoice_id = aia.invoice_id
AND aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND ei.ORIG_TRANSACTION_REFERENCE = aid.INVOICE_DISTRIBUTION_ID
Good Blog, Thanks For Sharing This Informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad