For partners that build or integrate commercially available applications and service solutions with the Oracle Cloud Platform
For partners that provide implementation or managed services around Oracle Cloud Applications
Hi
Does anyone know the links/joins between GL and Projects, we are trying to build a report however not all transactions are coming through?
Your assistance is appreciated
Thanks
Comment
Refer below doc id
How To Link Projects Transactions To Subledger Accounting (SLA) (Doc ID 1274575.1)
Average Rating:



1 rating
|
Sign in to rate this
See if below Query Helps:
SELECT gld.name "LEDGER NAME",
gjh.period_name "PERIOD NAME",
gjb.name "JOURNAL BATCH",
gjh.name "JOURNAL NAME",
TO_CHAR (gjh.default_effective_date, 'MM/DD/YYYY') "NATURALING DATE",
gjs.user_je_source_name "JOURNAL SOURCE",
gjc.user_je_category_name "JOURNAL CATEGORY",
gjh.status "JOURNAL HDR STATUS",
gjh.accrual_rev_flag "ACCRUAL REV FLAG",
TO_CHAR (gjh.posted_date, 'MM/DD/YYYY') "POSTED DATE",
gjl.je_line_num "JOURNAL LINE NUM",
SUBSTR (NVL (REPLACE (xal.description, ' ', ''), gjl.description),
1,
160)
"JOURNAL LINE DESC" ,
CC.segment1 "COM CODE",
(SELECT VVS.description
FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
WHERE 1 = 1
AND CC.segment1 = VVS.VALUE
AND VVS.value_set_id = VSET.value_set_id
AND VSET.value_set_code = 'CHART_COM')
"COM NAME",
CC.segment2 "REGION CODE",
(SELECT VVS.description
FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
WHERE 1 = 1
AND CC.segment2 = VVS.VALUE
AND VVS.value_set_id = VSET.value_set_id
AND VSET.value_set_code = 'CHART_AREA')
"AREA NAME",
CC.segment3 "DEPT CODE",
(SELECT VVS.description
FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
WHERE 1 = 1
AND CC.segment3 = VVS.VALUE
AND VVS.value_set_id = VSET.value_set_id
AND VSET.value_set_code = 'CHART_COST_CENT')
"DEPT NAME",
CC.segment4 "NATURAL CODE",
(SELECT VVS.description
FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
WHERE 1 = 1
AND CC.segment4 = VVS.VALUE
AND VVS.value_set_id = VSET.value_set_id
AND VSET.value_set_code = 'CHART_NATURAL')
"NATURAL NAME",
CC.segment5 "REGION CODE",
(SELECT VVS.description
FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
WHERE 1 = 1
AND CC.segment5 = VVS.VALUE
AND VVS.value_set_id = VSET.value_set_id
AND VSET.value_set_code = 'CHART_REGION')
"REGION NAME",
CC.segment6 "IC CODE",
(SELECT VVS.description
FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
WHERE 1 = 1
AND CC.segment6 = VVS.VALUE
AND VVS.value_set_id = VSET.value_set_id
AND VSET.value_set_code = 'CHART_INTERCOM')
"INT COM NAME",
CC.segment7 "ENTITY CODE",
CC.segment9 "F1",
ROUND (XDL.UNROUNDED_ENTERED_DR, 2) "ENTERED Dr",
ROUND (XDL.UNROUNDED_ENTERED_CR, 2) "ENTERED Cr",
ROUND (
NVL (XDL.UNROUNDED_ENTERED_DR, 0)
- NVL (XDL.UNROUNDED_ENTERED_CR, 0),
2)
"NET ENTERED",
ROUND (XDL.UNROUNDED_NATURALED_DR, 2) "NATURALED Dr",
ROUND (XDL.UNROUNDED_NATURALED_CR, 2) "NATURALED Cr",
ROUND (
NVL (XDL.UNROUNDED_NATURALED_DR, 0)
- NVL (XDL.UNROUNDED_NATURALED_CR, 0),
2)
"NET NATURALED",
gir.gl_sl_link_id "GL SL LINK ID",
(SELECT transaction_number
FROM xla_transaction_entities xte
WHERE 1 = 1 AND xah.entity_id = xte.entity_id)
"TRANSACTION NUMBER",
--xe.reference_char_1 "REFERENCE_VALUE",
xe.event_type_code "EVENT_TYPE_CODE",
-- pei.project_id,
ppb.segment1 "proj Num",
ppa.name "proj Name",
pet.expenditure_type_name "Exp Type Name",
TO_CHAR (pei.expenditure_item_date, 'MM/DD/YYYY') "Exp Item Date",
pec.expenditure_comment "Exp Comment",
hro.name "Exp Organization",
pt.element_number "Task Lowest",
(SELECT element_number
FROM pjf_proj_elements_b pt1
WHERE object_type = 'PJF_TASKS'
AND pt.denorm_parent_element_id = pt1.proj_element_id)
"Task Level2",
(SELECT element_number
FROM pjf_proj_elements_b pt2
WHERE object_type = 'PJF_TASKS'
AND pt.denorm_top_element_id = pt2.proj_element_id)
"Task Level3",
NULL "Party Name",
NULL "Party Number",
NVL (gjl.currency_code, gjh.currency_code) "Currency Code",
NULL "Transaction Type",
gjh.CREATED_BY "Created By",
NULL "Sales Order Number"
FROM gl_ledgers gld,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
xla_transaction_entities xte,
xla_events xe,
pjc_exp_items_all pei,
pjf_projects_all_b ppb,
pjf_projects_all_tl ppa,
pjf_exp_types_tl pet,
pjc_exp_comments pec,
hr_all_organization_units hro,
pjf_proj_elements_b pt,
gl_je_sources gjs,
gl_je_categories gjc
WHERE gld.ledger_id = gjh.ledger_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.code_combination_id = gcc.code_combination_id
AND gir.reference_7 = xal.ae_header_id
AND xal.ae_header_id = xah.ae_header_id
AND gir.reference_5 = xah.entity_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xah.entity_id = xte.entity_id
AND gir.reference_6 = xah.event_id
AND xah.event_id = xe.event_id
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'R'
AND xdl.source_distribution_id_num_1 = pei.expenditure_item_id
AND pei.project_id = ppb.project_id
AND ppb.project_id = ppa.project_id
AND pei.project_id = ppa.project_id
AND ppa.LANGUAGE = 'US'
AND pet.LANGUAGE = 'US'
AND pei.expenditure_type_id = pet.expenditure_type_id
AND pei.expenditure_item_id = pec.expenditure_item_id(+)
AND pei.expenditure_organization_id = hro.organization_id
AND pei.task_id = pt.proj_element_id
AND pt.object_type = 'PJF_TASKS'
-- AND pt.chargeable_flag = 'Y'
AND gjh.je_source = gjs.JE_SOURCE_NAME
AND gjh.je_category = gjc.JE_CATEGORY_NAME
AND gjh.je_from_sla_flag IS NOT NULL
AND gld.name IN (:LEDGER_NAME)
AND gjh.period_name IN (:PERIOD_NAME)
AND gcc.segment1 IN (:COM)
AND gcc.segment2 IN (:AREA)
AND gcc.segment3 IN (:COST CENTER)
AND gcc.segment4 IN (:NATURAL)
AND gcc.segment5 IN (:REGION)
AND gcc.segment6 IN (:INTERCOM)
AND gcc.segment7 IN (:ENTITY)
AND gcc.segment8 IN (:LEGAL)
AND gcc.segment9 IN (:F1)
AND gjs.user_je_source_name IN (:JE_SOURCE)
AND gjc.user_je_category_name IN (:JE_CATEGORY)
AND gjh.status IN (:POSTING_STATUS)
AND ( gjh.accrual_rev_status IN (:REVERSAL_STATUS)
OR gjh.accrual_rev_status IS NULL)
Average Rating:



1 rating
|
Sign in to rate this
I copied the SQL to create a new data model and I received a "missing right parenthesis" error. I haven't been able to identify a missing parenthesis.
Be the first to rate this
|
Sign in to rate this
Here's one for project adjustments, you can remove that condition to pull all project costs.
SELECT
pxc.project_number as project_num
, peia.expenditure_item_id as trx_num
, pxc.task_number as task_num
, pe.name as task_name
, pxc.expenditure_type --, pxc.expenditure_type_class --mhaynes updated 3.12.19 pull expenditure_type instead of expenditure_type_class per Kevin G
, pxc.exp_organization_name as expenditure_org
, CASE
WHEN pcdl.capitalizable_flag = 'Y'
THEN 'CAPEX'
ELSE 'OPEX'
END capex_opex
, glp.fiscal_year_number as fiscal_year
, glp.fiscal_period_name as acct_period
, to_char(aca.check_date,'MM/DD/YYYY') as paid_date
, to_char(peia.creation_date,'MM/DD/YYYY') as exp_item_create_date
, to_char(peia.expenditure_item_date,'MM/DD/YYYY') as exp_item_date
--, NVL(gcc.segment1, gcc_adj_dr.segment1) as gl_company --mhaynes added 3.12.19
, (
SELECT
ffvt.description
FROM
fnd_flex_value_sets fvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE
fvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND fvs.flex_value_set_name = 'Company BCBS COA'
AND ffv.flex_value = NVL(gcc.segment1, gcc_adj_dr.segment1)
)
as gl_company
, NVL(gcc.segment2, gcc_adj_dr.segment2) as gl_acct --if adjustment then ap inv distribution will be null go to pjc distribution
, NVL(gcc.segment3, gcc_adj_dr.segment3) as gl_cost_center --if adjustment then ap inv distribution will be null go to pjc distribution
, aia.invoice_num
, NVL(supp.vendor_name, ppn.full_name) as vendor_name
, (
SELECT
ppf.full_name
FROM
po_distributions_all pod
, per_employees_x ppf
WHERE
pod.po_distribution_id = dist.po_distribution_id --link to main query
AND pod.deliver_to_person_id = ppf.person_id
--AND dist.invoice_distribution_id = 276942 --cannot use there was no requester info on this line
AND rownum = 1
)
as employee_name
, pcdl.project_raw_cost as amount_paid --mhaynes renamed 3.12.19 per Kevin G
--, aia.amount_paid --mhaynes removed 3.12.19 per Kevin G
, CASE
WHEN pxc.expenditure_type_class = 'Miscellaneous Transaction'
THEN 'Y'
ELSE 'N'
END adjustment
, pts.user_transaction_source
, pxc.expenditure_type_class
FROM
pjc_exp_items_all peia
, pjc_cost_dist_lines_all pcdl
, pjc_xla_cost_v pxc
, pjf_proj_elements_vl pe
, gl_fiscal_period_v glp
, ap_invoices_all aia
, ap_invoice_lines_all ala
, ap_invoice_distributions_all dist
, poz_suppliers_v supp
, ap_invoice_payments_all aipa
, ap_checks_all aca
, gl_code_combinations gcc
, gl_code_combinations gcc_adj_cr
, gl_code_combinations gcc_adj_dr
, per_person_names_f ppn
, pjf_txn_sources_vl pts
WHERE
peia.expenditure_item_id = pcdl.expenditure_item_id
AND peia.expenditure_item_id = pxc.expenditure_item_id
AND pcdl.task_id = pe.proj_element_id
AND trunc(pcdl.prvdr_gl_date) between fiscal_period_start_date and fiscal_period_end_date
AND peia.original_header_id = aia.invoice_id (+)
AND aia.invoice_id = ala.invoice_id (+)
AND peia.original_line_number = ala.line_number (+)
AND peia.original_dist_id = dist.invoice_distribution_id (+)
AND dist.dist_code_combination_id = gcc.code_combination_id (+)
AND pcdl.raw_cost_cr_ccid = gcc_adj_cr.code_combination_id (+) --adjustments
AND pcdl.raw_cost_dr_ccid = gcc_adj_dr.code_combination_id (+) --adjustments
AND gcc_adj_cr.account_type (+) IN ('E'
,'R'
,'A')
AND gcc_adj_dr.account_type (+) IN ('E'
,'R'
,'A')
AND aia.vendor_id = supp.vendor_id (+)
AND aia.invoice_id = aipa.invoice_id (+)
AND aipa.check_id = aca.check_id (+)
AND NVL(aipa.reversal_flag,'N') != 'Y'
AND NVL(aca.status_lookup_code,'N') != 'VOIDED'
AND peia.incurred_by_person_id = ppn.person_id (+)
AND pxc.transaction_source = pts.transaction_source --mhaynes added 3.27.19 requested by Brent S
AND ppn.name_type (+) = 'US'
AND pxc.document_entry_name != 'Nonrecoverable Tax'
AND pxc.expenditure_type_class = 'Miscellaneous Transaction' --mhaynes added 3.13.19 need to get proj exp creation date
AND glp.adjustment_period_flag != 'Y'
AND peia.creation_date between NVL(ppn.effective_start_date,'1900-01-01') and NVL(ppn.effective_end_date, '4712-01-01')
--AND aia.invoice_id = (NVL(:p_inv_num,aia.invoice_id) OR aia.invoice_id is null)
AND pxc.project_number = NVL(:p_pj_num, pxc.project_number )
AND glp.fiscal_period_name = NVL(:p_period, glp.fiscal_period_name)
AND peia.expenditure_item_id = NVL(:p_trx_num,peia.expenditure_item_id)
AND pxc.exp_organization_name = NVL(:p_exp_org, pxc.exp_organization_name) --mhaynes added 3.12.19 requested by Kevin G
AND(NVL(TO_CHAR(peia.creation_date,'YYYY-MM-DD'),'1900-01-01') >= NVL(NVL(:p_pay_date_from, TO_CHAR(peia.creation_date,'YYYY-MM-DD')),'1900-01-01'))
AND(NVL(TO_CHAR(peia.creation_date,'YYYY-MM-DD'),'4712-01-01') <= NVL(NVL(:p_pay_date_to, TO_CHAR(peia.creation_date,'YYYY-MM-DD')),'4712-01-01'))
AND 'Y' = NVL(:p_inv_pay_status, 'Y')
Average Rating:



1 rating
|
Sign in to rate this