GL Dept Spend Details Report
SELECT DISTINCT
/*
k.Manager1,
k.Manager2,
k.Manager3,
k.Manager4,
k.Analyst_Name,
*/ k.sec_category,
/*
k.Subject_To_Budget,
*/
--k.Pnl_Line,
--k.Codept_Desc,
k.country_cd,
/*
k.Allocation_Base,
*/
k.co,
k.co_desc,
k.location,
k.location_desc,
--k.Codept,
k.gac,
k.gac_desc,
k.lac,
k.lac_desc,
k.dept,
k.dept_desc,
k.pal,
k.pal_desc,
k.ico,
k.ico_desc,
k.gac
|| '-'
|| k.gac_desc Acct_Name,
/*
k.Cost_Element_1,
k.Cost_Element_2,
*/
k.amt_book_cy,
--k.Budget_Amt,
k.period,
k.po_num,
k.po_line_desc,
k.proj,
k.proj_desc,
k.vendor_name,
k.inv_desc,
k.inv_cy_cd,
k.inv_num,
k.ap_line_desc,
k.je_line_desc,
k.proj_num,
k.task_num,
k.expenditure_type,
k.expenditure_org,
k.source,
--To_Number(To_Char(To_Date(k.Period, 'MON-YY'), 'YYYYMM')) Period_YYYYMM
To_number(To_char(To_date(k.period, 'MON-YY',
'NLS_DATE_LANGUAGE=AMERICAN'),
'YYYYMM')) Period_YYYYMM
FROM (SELECT
/*
Glcc.Manager1,
Glcc.Manager2,
Glcc.Manager3,
Glcc.Manager4,
Glcc.Finance_Analyst_User_Name Analyst_Name,
Coalesce((SELECT Hou.Attribute11
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT' --na--
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5),
(SELECT Sec_Cat
FROM Xxon_Codept_Seccat_Pnl_v
WHERE Glcc.Segment5 BETWEEN From_Dept AND To_Dept
AND Rownum = 1)) Sec_Category,
*/ (SELECT Hou.attribute11
FROM hr_organization_units Hou
WHERE Hou.TYPE = 'HR_DEPT' --na--
AND Substr(Hou.name, 1, 3) = Glcc.segment1
AND Substr(Hou.name, 5, 4) = Glcc.segment5) Sec_Category
,
/*
Glcc.Subject_To_Budget_Flag Subject_To_Budget,
(SELECT Hou.Attribute12
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT' --na--
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5) Pnl_Line,
(SELECT Substr(Hou.Name, 13)
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT' --na--
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5) Codept_Desc,
*/
(SELECT Hl.country
FROM hr_organization_units Hou,
hr_locations Hl
WHERE Hou.TYPE = 'HR_DEPT' --na--
AND Hl.location_id = Hou.location_id
AND Substr(Hou.name, 1, 3) = Glcc.segment1
AND Substr(Hou.name, 5, 4) = Glcc.segment5) Country_Cd,
/*
Glcc.Current_Allocation_Base Allocation_Base,
*/
Glcc.segment1 Co,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS1_ENT'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment1) Co_Desc,
Glcc.segment2 Location,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS2_LOC'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value =
Glcc.segment2) Location_Desc,
--Glcc.Segment1 || '-' || Glcc.Segment5 Codept,
Glcc.segment3 Gac,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS3_GAC'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment3) Gac_Desc,
Glcc.segment4 Lac,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS4_LAC'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment4) Lac_Desc,
Glcc.segment5 Dept,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS5_DEPT'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment5) Dept_Desc,
Glcc.segment6 PAL,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS6_PAL'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment6) PAL_Desc,
Glcc.segment7 ICO,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS7_ICO'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment7) ICO_Desc,
--Glcc.Cost_Element_1,
--Glcc.Cost_Element_2,
CASE
WHEN xal.currency_code = 'USD' THEN ( (
Nvl(Xdl.unrounded_entered_dr, 0) ) - (
Nvl(Xdl.unrounded_entered_cr, 0) ) )
ELSE ( ( Nvl(Xdl.unrounded_accounted_dr, 0) ) -
( Nvl(Xdl.unrounded_accounted_cr, 0) ) )
END Amt_Book_Cy,
--round(((Nvl(Xdl.Unrounded_Accounted_Dr, 0)) -
--(Nvl(Xdl.Unrounded_Accounted_Cr, 0))),2) Amt_Book_Cy,
0 Budget_Amt,
Gp.period_name Period,
Pha.segment1 Po_Num,
Pla.item_description Po_Line_Desc
,
Glcc.segment8
Proj,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS8_PROG'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment8) Proj_Desc,
--Aps.Vendor_Name,
psv.vendor_name,
Aia.description Inv_Desc,
Aia.invoice_currency_code Inv_Cy_Cd,
Aia.invoice_num Inv_Num,
Aid.description Ap_Line_Desc
,
NULL
Je_Line_Desc,
ppab.segment1 proj_num,
ptv.task_number task_num,
pet.expenditure_type_name
expenditure_type,
houp.name
expenditure_org,
'Payables' SOURCE,
xal.gl_sl_link_id,
xal.ledger_id,
xal.ae_header_id,
gled.name ledger_name
FROM gl_code_combinations Glcc,
--Xxon_Gl_Fs_Glcc_Attr_Plopr Glcc,
--Ap_Suppliers Aps,
poz_suppliers_v psv,
ap_invoice_distributions_all Aid,
ap_invoices_all Aia,
xla_ae_lines Xal,
xla_ae_headers Xah,
xla_distribution_links Xdl,
xla_events Xe,
xla_transaction_entities Xte,
gl_periods Gp,
--Fnd_Application Fa,
po_distributions_all Pod,
po_headers_all Pha,
po_lines_all Pla,
pjf_projects_all_b ppab,
pjf_tasks_v ptv,
pjf_exp_types_vl pet,
hr_organization_units houp,
gl_daily_rates Gdr,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
GL_LEDGERS gled/*,
gl_ledger_relationships glr,
gl_ledger_config_details glcd,
gl_ledger_CONFIGURATIONS glr_le,
gl_ledger_config_details glcd_le*/
WHERE 1 = 1
/*
AND EXISTS
(SELECT 1
FROM Gl_Consolidation Sgc
WHERE EXISTS
(SELECT 1
FROM Gl_Cons_Map_Set_v s,
Gl_Cons_Set_Assign_v a
WHERE s.Consolidation_Set_Id = a.Consolidation_Set_Id
AND a.Consolidation_Id = Sgc.Consolidation_Id)
AND Sgc.From_Ledger_Id = Xah.Ledger_Id
AND Sgc.From_Currency_Code = 'USD'
AND Sgc.Name NOT LIKE 'ZZZ%'
AND Nvl('Y', 'N') = 'Y')
*/
--AND Aia.Vendor_Id = Aps.Vendor_Id
--AND aia.invoice_num in ('0003TT81','0003V888','K52025')
AND Aia.vendor_id = psv.vendor_id
AND Aid.invoice_id(+) = Aia.invoice_id
AND Xte.ledger_id = Aia.set_of_books_id
AND Xte.entity_code IN ( 'AP_INVOICES', 'AP_PAYMENTS' )
AND Aia.invoice_id = Nvl(Xte.source_id_int_1, ( -99 ))
AND Xte.application_id = 200
AND Xal.ae_header_id = Xah.ae_header_id
AND Xal.application_id = Xah.application_id
--AND xal.currency_conversion_type (+)= 'Corporate'
--AND xal.accounting_class_code <> 'LIABILITY'
AND Xah.event_id = Xe.event_id
AND Xe.entity_id = Xte.entity_id
AND Xah.application_id = Xe.application_id
--AND Fa.Application_Id = Xah.Application_Id
--AND Fa.Application_Id = Xte.Application_Id
AND Xte.application_id = Xdl.application_id
AND Xah.ae_header_id = Xdl.ae_header_id
AND Xal.ae_line_num = Xdl.ae_line_num
AND Xdl.source_distribution_type = 'AP_INV_DIST'
AND Xdl.source_distribution_id_num_1 =
Aid.invoice_distribution_id(+)
AND Xah.event_id = Xdl.event_id
--AND Fa.Application_Short_Name = 'SQLAP' --na--
AND Glcc.code_combination_id = Xal.code_combination_id
AND Gp.period_set_name = '4-4-5'
AND gp.start_date <= Last_day(SYSDATE)
AND Pod.po_distribution_id(+) = Aid.po_distribution_id
AND Pha.po_header_id(+) = Pod.po_header_id
AND Pla.po_line_id(+) = Pod.po_line_id
AND aid.pjc_project_id = ppab.project_id(+)
AND aid.pjc_task_id = ptv.task_id(+)
AND aid.pjc_organization_id = houp.organization_id(+)
AND aid.pjc_expenditure_type_id = pet.expenditure_type_id(+)
AND Gdr.from_currency(+) = xal.currency_code
AND Gdr.to_currency(+) = 'USD'
AND Gdr.conversion_type(+) = 'Corporate'
AND Gdr.conversion_date(+) = xal.accounting_date
/***************************************************/
AND Gjh.actual_flag = 'A'
AND Gjh.je_source = 'Payables'
AND Gjh.je_header_id = Gjl.je_header_id
AND Gjh.je_header_id = Gir.je_header_id
AND Gjl.je_line_num = Gir.je_line_num
AND Glcc.code_combination_id = Gjl.code_combination_id
AND Gir.gl_sl_link_id = Xal.gl_sl_link_id
AND Gir.gl_sl_link_table = Xal.gl_sl_link_table
AND Xah.ledger_id = Gjh.ledger_id
AND XAL.LEDGER_ID = GJL.LEDGER_ID
AND Gjh.period_name = Gp.period_name
AND gled.ledger_id = xah.ledger_id
AND gled.LEDGER_CATEGORY_CODE = 'PRIMARY'
--AND GLED.LEDGER_ID = GLR.PRIMARY_LEDGER_ID
--AND GLED.LEDGER_ID = GLR.TARGET_LEDGER_ID
--AND GLED.LEDGER_CATEGORY_CODE = GLR.TARGET_LEDGER_CATEGORY_CODE
--AND GLCD.OBJECT_TYPE_CODE = 'PRIMARY'
--AND GLCD.SETUP_STEP_CODE = 'NONE'
--AND GLR_LE.CONFIGURATION_ID = GLCD.CONFIGURATION_ID
--AND GLR_LE.CONFIGURATION_ID =GLCD_LE.CONFIGURATION_ID
--AND GLCD_LE.OBJECT_TYPE_CODE (+) = 'LEGAL_ENTITY'
--AND GLCD_LE.SETUP_STEP_CODE (+) = 'NONE'
--AND XLE.LEGAL_ENTITY_ID = GLCD.LE.OBJECT_ID
/***************************************************/
/* AND EXISTS (SELECT 1
FROM gl_je_headers Gjh,
gl_je_lines Gjl,
gl_import_references Gir
WHERE Gjh.actual_flag = 'A'
AND Gjh.je_source = 'Payables'
AND Gjh.je_header_id = Gjl.je_header_id
AND Gjh.je_header_id = Gir.je_header_id
AND Gjl.je_line_num = Gir.je_line_num
AND Glcc.code_combination_id =
Gjl.code_combination_id
AND Gir.gl_sl_link_id = Xal.gl_sl_link_id
AND Gir.gl_sl_link_table =
Xal.gl_sl_link_table
AND Xah.ledger_id = Gjh.ledger_id
AND Gjh.period_name = Gp.period_name)*/
AND ( ( Nvl(Xdl.unrounded_accounted_dr, 0) ) -
( Nvl(Xdl.unrounded_accounted_cr, 0) ) ) <> 0
/*Commenting out below 62 or 62 condition as per confirmation from Tyler Lacey*/
---AND (Glcc.Segment3 LIKE '62%' OR Glcc.Segment3 LIKE '61%')
AND ( CASE
WHEN Gp.period_name IN ( :p_period ) THEN 1
WHEN ( Coalesce(NULL, :p_period) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment1 IN ( :p_company ) THEN 1
WHEN ( Coalesce(NULL, :p_company) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment2 IN ( :p_location ) THEN 1
WHEN ( Coalesce(NULL, :p_location) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment3 IN ( :p_gac ) THEN 1
WHEN ( Coalesce(NULL, :p_gac) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment5 IN ( :p_dept ) THEN 1
WHEN ( Coalesce(NULL, :p_dept) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment8 IN ( :p_proj ) THEN 1
WHEN ( Coalesce(NULL, :p_proj) IS NULL ) THEN 1
END = 1 )) k
UNION ALL
SELECT DISTINCT
/*
k.Manager1,
k.Manager2,
k.Manager3,
k.Manager4,
k.Analyst_Name,
*/ k.sec_category,
/*
k.Subject_To_Budget,
*/
--k.Pnl_Line,
--k.Codept_Desc,
k.country_cd,
/*
k.Allocation_Base,
*/
k.co,
k.co_desc,
k.location,
k.location_desc,
--k.Codept,
k.gac,
k.gac_desc,
k.lac,
k.lac_desc,
k.dept,
k.dept_desc,
k.pal,
k.pal_desc,
k.ico,
k.ico_desc,
k.gac
|| '-'
|| k.gac_desc,
/*
k.Cost_Element_1,
k.Cost_Element_2,
*/
SUM(k.book_amt) Book_Amt,
--sum(k.budget_Amt) budget_Amt,
k.period,
k.po_num,
k.po_line_desc,
k.proj,
k.proj_desc,
k.vendor_name,
k.inv_desc,
k.inv_cy_cd,
k.inv_num,
k.ap_line_desc,
k.je_line_desc,
k.proj_num,
k.task_num,
k.expenditure_type,
k.expenditure_org,
k.source,
--To_Number(To_Char(To_Date(k.Period, 'MON-YY'), 'YYYYMM')) Period_YYYYMM
To_number(To_char(To_date(k.period, 'MON-YY', 'NLS_DATE_LANGUAGE=AMERICAN'
),
'YYYYMM')) Period_YYYYMM
FROM (SELECT
/*
Glcc.Manager1,
Glcc.Manager2,
Glcc.Manager3,
Glcc.Manager4,
Glcc.Finance_Analyst_User_Name Analyst_Name,
Coalesce((SELECT Hou.Attribute11
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT'
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5),
(SELECT Sec_Cat
FROM Xxon_Codept_Seccat_Pnl_v
WHERE Glcc.Segment5 BETWEEN From_Dept AND To_Dept
AND Rownum = 1)) Sec_Category,
*/ (SELECT Hou.attribute11
FROM hr_organization_units Hou
WHERE Hou.TYPE = 'HR_DEPT'
AND Substr(Hou.name, 1, 3) = Glcc.segment1
AND Substr(Hou.name, 5, 4) = Glcc.segment5) Sec_Category,
/*
Glcc.Subject_To_Budget_Flag Subject_To_Budget,
Coalesce((SELECT Hou.Attribute12
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT'
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5),
(SELECT Pnl_Line
FROM Xxon_Codept_Seccat_Pnl_v
WHERE Glcc.Segment5 BETWEEN From_Dept AND To_Dept
AND Rownum = 1)) Pnl_Line,
(SELECT Hou.Attribute12
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT'
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5) Pnl_Line,
*/
(SELECT Substr(Hou.name, 13)
FROM hr_organization_units Hou
WHERE Hou.TYPE = 'HR_DEPT' --na--
AND Substr(Hou.name, 1, 3) = Glcc.segment1
AND Substr(Hou.name, 5, 4) = Glcc.segment5) Codept_Desc,
(SELECT Hl.country
FROM hr_organization_units Hou,
hr_locations Hl
WHERE Hou.TYPE = 'HR_DEPT' --na--
AND Hl.location_id = Hou.location_id
AND Substr(Hou.name, 1, 3) = Glcc.segment1
AND Substr(Hou.name, 5, 4) = Glcc.segment5) Country_Cd,
/*
Glcc.Current_Allocation_Base Allocation_Base,
*/
Glcc.segment1 Co,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS1_ENT'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment1) Co_Desc,
Glcc.segment2 Location,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS2_LOC'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment2) Location_Desc,
--Glcc.Segment1 || '-' || Glcc.Segment5 Codept,
Glcc.segment3 Gac,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS3_GAC'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment3) Gac_Desc,
Glcc.segment4 Lac,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS4_LAC'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment4) Lac_Desc,
Glcc.segment5 Dept,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS5_DEPT'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment5) Dept_Desc,
Glcc.segment6 PAL,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS6_PAL'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment6) PAL_Desc,
Glcc.segment7 ICO,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS7_ICO'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment7) ICO_Desc,
/*
Glcc.Cost_Element_1,
Glcc.Cost_Element_2,
((Nvl(Gjl.Accounted_Dr, 0)) - (Nvl(Gjl.Accounted_Cr, 0))) Book_Amt,
*/
--CASE
--WHEN Gjl.Currency_Code = 'USD' THEN
Round(( ( Nvl(Gjl.accounted_dr, 0) ) - ( Nvl(Gjl.accounted_cr, 0) )
), 2)
Book_Amt,
--ELSE
--Round((((Nvl(Gjl.Accounted_Dr, 0)) - (Nvl(Gjl.Accounted_Cr, 0))) *
-- Gdr.Conversion_Rate),2)
--END Book_Amt,
0 budget_Amt,
Gp.period_name Period,
NULL Po_Num,
NULL Po_Line_Desc,
Glcc.segment8 Proj,
(SELECT Ffv.description
FROM fnd_flex_value_sets Ffvs,
fnd_flex_values_vl Ffv
WHERE Ffvs.flex_value_set_name = 'XXON_GLS8_PROG'
AND Ffv.flex_value_set_id = Ffvs.flex_value_set_id
AND Ffv.flex_value = Glcc.segment8) Proj_Desc,
NULL Vendor_Name,
NULL Inv_Desc,
NULL Inv_Cy_Cd,
NULL Inv_Num,
NULL Ap_Line_Desc,
Gjl.description Je_Line_Desc,
NULL proj_num,
NULL task_num,
NULL
expenditure_type,
NULL expenditure_org
,
'Non-Payables' SOURCE
FROM gl_je_headers Gp,
gl_je_lines Gjl,
GL_LEDGERS GLED,
--Xxon_Gl_Fs_Glcc_Attr_Plopr Glcc
gl_code_combinations glcc,
gl_daily_rates Gdr
WHERE Gp.je_header_id = Gjl.je_header_id
AND Glcc.code_combination_id = Gjl.code_combination_id
AND Gdr.from_currency(+) = Gjl.currency_code
AND Gdr.to_currency(+) = 'USD'
AND Gdr.conversion_type(+) = 'Corporate'
AND Gdr.conversion_date(+) LIKE Gp.date_created
AND gp.ledger_id = gled.ledger_id
AND gled.LEDGER_CATEGORY_CODE = 'PRIMARY'
/*
AND EXISTS
(SELECT 1
FROM Gl_Consolidation Sgc
WHERE EXISTS
(SELECT 1
FROM Gl_Cons_Map_Set_v s,
Gl_Cons_Set_Assign_v a
WHERE s.Consolidation_Set_Id = a.Consolidation_Set_Id
AND a.Consolidation_Id = Sgc.Consolidation_Id)
AND Sgc.From_Ledger_Id = Gp.Ledger_Id
AND Sgc.From_Currency_Code = 'USD'
AND Sgc.Name NOT LIKE 'ZZZ%'
AND Nvl('Y', 'N') = 'Y'
UNION ALL
SELECT 1
FROM Dual
WHERE Nvl('Y', 'N') = 'N')
*/
AND ( ( Nvl(Gjl.accounted_dr, 0) ) - ( Nvl(Gjl.accounted_cr, 0) )
) <> 0
AND Gp.je_source <> 'Payables'
--AND Gp.Currency_Code <> 'STAT'
/*Commenting out below 62 or 62 condition as per confirmation from Tyler Lacey*/
---AND (Glcc.Segment3 LIKE '61%' OR Glcc.Segment3 LIKE '62%')
AND ( CASE
WHEN Gp.period_name IN ( :p_period ) THEN 1
WHEN ( Coalesce(NULL, :p_period) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment1 IN ( :p_company ) THEN 1
WHEN ( Coalesce(NULL, :p_company) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment2 IN ( :p_location ) THEN 1
WHEN ( Coalesce(NULL, :p_location) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment3 IN ( :p_gac ) THEN 1
WHEN ( Coalesce(NULL, :p_gac) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment5 IN ( :p_dept ) THEN 1
WHEN ( Coalesce(NULL, :p_dept) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Glcc.segment8 IN ( :p_proj ) THEN 1
WHEN ( Coalesce(NULL, :p_proj) IS NULL ) THEN 1
END = 1 )) k
GROUP BY
/*
k.Manager1,
k.Manager2,
k.Manager3,
k.Manager4,
k.Analyst_Name,
*/
k.sec_category,
/*
k.Subject_To_Budget,
*/
--k.Pnl_Line,
--k.Codept_Desc,
k.country_cd,
/*
k.Allocation_Base,
*/
k.co,
k.co_desc,
k.location,
k.location_desc,
--k.Codept,
k.gac,
k.gac_desc,
k.lac,
k.lac_desc,
k.dept,
k.dept_desc,
k.pal,
k.pal_desc,
k.ico,
k.ico_desc,
k.gac
|| '-'
|| k.gac_desc,
/*
k.Cost_Element_1,
k.Cost_Element_2,
*/
k.period,
k.po_num,
k.po_line_desc,
k.proj,
k.proj_desc,
k.vendor_name,
k.inv_desc,
k.inv_cy_cd,
k.inv_num,
k.ap_line_desc,
k.je_line_desc,
k.proj_num,
k.task_num,
k.expenditure_type,
k.expenditure_org,
k.source
/*---Commenting out Budget as per onsemi confirmation----
----Include_Budget Param----
UNION ALL
SELECT
---k.Manager1,
---k.Manager2,
---k.Manager3,
---k.Manager4,
---k.Analyst_Name,
k.Sec_Category,
---k.Subject_To_Budget,
--k.Pnl_Line,
--k.Codept_Desc,
k.Country_Cd,
---k.Allocation_Base,
k.Co,
k.Co_Desc,
k.Location,
k.Location_Desc,
--k.Codept,
k.Gac,
k.Gac_Desc,
k.Lac,
k.Lac_Desc,
k.Dept,
k.Dept_Desc,
k.PAL,
k.PAL_Desc,
k.ICO,
k.ICO_Desc,
k.Gac || '-' || k.Gac_Desc,
--k.Cost_Element_1,
--k.Cost_Element_2,
SUM(0) Book_Amt,
--SUM(k.Bdgt_Amt) Bdgt_Amt,
k.Period,
NULL Po_Num,
NULL Po_Line_Desc,
k.Proj,
k.Proj_Desc,
NULL Vendor_Name,
NULL Inv_Desc,
NULL Inv_Cy_Cd,
NULL Inv_Num,
NULL Ap_Line_Desc,
NULL Je_Line_Desc,
'Budget' SOURCE,
--To_Number(To_Char(To_Date(k.Period, 'MON-YY'), 'YYYYMM')) Period_YYYYMM
To_Number(To_Char(To_Date(k.Period, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'), 'YYYYMM')) Period_YYYYMM
FROM (SELECT
--Glcc.Manager1,
--Glcc.Manager2,
--Glcc.Manager3,
--Glcc.Manager4,
--Glcc.Finance_Analyst_User_Name Analyst_Name,
(SELECT Hou.Attribute11
FROM Hr_Organization_Units Hou
WHERE Hou.Type = 'HR_DEPT'
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5) Sec_Category,
(SELECT Hl.Country
FROM Hr_Organization_Units Hou, Hr_Locations Hl
WHERE Hou.Type = 'HR_DEPT' --na--
AND Hl.Location_Id = Hou.Location_Id
AND Substr(Hou.Name, 1, 3) = Glcc.Segment1
AND Substr(Hou.Name, 5, 4) = Glcc.Segment5) Country_Cd,
--Glcc.Current_Allocation_Base Allocation_Base,
Glcc.Segment1 Co,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS1_ENT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment1)Co_Desc,
Glcc.Segment2 Location,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS2_LOC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment2)Location_Desc,
--Glcc.Segment1 || '-' || Glcc.Segment5 Codept,
Glcc.Segment3 Gac,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment3) Gac_Desc,
Glcc.Segment4 Lac,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS4_LAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment4) Lac_Desc,
Glcc.Segment5 Dept,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS5_DEPT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment5) Dept_Desc,
Glcc.Segment6 PAL,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS6_PAL'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment6) PAL_Desc,
Glcc.Segment7 ICO,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS7_ICO'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment7) ICO_Desc,
--Glcc.Cost_Element_1,
--Glcc.Cost_Element_2,
--(Nvl(Gp.Period_Net_Dr, 0) - Nvl(Gp.Period_Net_Cr, 0)) Bdgt_Amt,
Gp.Period_Name Period,
Glcc.Segment8 Proj,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS8_PROG'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = Glcc.Segment8) Proj_Desc
FROM --Xxon_Gl_Balances Gp,
gl_balances gp,
--Xxon_Gl_Fs_Glcc_Attr_Plopr Glcc
gl_code_combinations glcc
WHERE Gp.Actual_Flag = 'B'
AND Glcc.Code_Combination_Id = Gp.Code_Combination_Id
AND Gp.Currency_Code <> 'STAT'
AND (Glcc.Segment3 LIKE '61%' OR Glcc.Segment3 LIKE '62%')
AND (Nvl(Gp.Period_Net_Dr, 0) - Nvl(Gp.Period_Net_Cr, 0)) <> 0
) k
GROUP BY
---k.Manager1,
---k.Manager2,
---k.Manager3,
---k.Manager4,
---k.Analyst_Name,
k.Sec_Category,
---k.Subject_To_Budget,
--k.Pnl_Line,
--k.Codept_Desc,
k.Country_Cd,
---k.Allocation_Base,
k.Co,
k.Co_Desc,
k.Location,
k.Location_Desc,
--k.Codept,
k.Gac,
k.Gac_Desc,
k.Lac,
k.Lac_Desc,
k.Dept,
k.Dept_Desc,
k.PAL,
k.PAL_Desc,
k.ICO,
k.ICO_Desc,
k.Gac || '-' || k.Gac_Desc,
--k.Cost_Element_1,
--k.Cost_Element_2,
k.Period,
k.Proj,
k.Proj_Desc
*/
No comments:
Post a Comment