Description:
The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes.
******************************************************************************************************
This section provides details from AP for NON-Employee related invoices.
*******************************************************************************************************/
SELECT (SELECT period_name
FROM gl_periods
WHERE apd.accounting_date BETWEEN start_date AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’) gl_period,
(SELECT ‘Q’
|| quarter_num
|| ‘-WK’
|| LPAD (TO_CHAR (DECODE (period_num,
53, 14,
DECODE (MOD (period_num, 13),
0, 13,
MOD (period_num, 13)
)
)
),
2,
‘0’
) planning_week
FROM gl_periods
WHERE period_type = ‘4_4_57130283831′
–’22’ — PA Periods
AND TRUNC (apd.accounting_date) BETWEEN start_date AND end_date
AND adjustment_period_flag = ‘N’) planning_week,
fv.description expenditure_organization, ai.project_id project_id,
NVL ((SELECT p.segment1
FROM pjf_projects_all_vl p
WHERE p.project_id(+) = ai.project_id), ‘N/A’) project_number,
NVL ((SELECT p.NAME
FROM pjf_projects_all_vl p
WHERE project_id(+) = ai.project_id), ‘N/A’) project_name,
NVL ((SELECT pt.project_type
FROM pjf_project_types_tl pt, pjf_projects_all_vl p
WHERE p.project_id(+) = ai.project_id
AND p.project_type_id = pt.project_type_id),
‘N/A’
) project_type_class_code,
NVL (pozs.vendor_name, ‘N/A’) employee_vendor,
NVL
((SELECT per.person_number
FROM per_all_people_f per
WHERE per.person_id = pozs.party_id
AND apd.accounting_date BETWEEN per.effective_start_date
AND per.effective_end_date),
‘N/A’
) employee_number,
‘AP Invoice’ task_name, ‘AP Invoice’ expenditure_category,
‘AP Invoice’ expenditure_type,
/*DECODE (TO_CHAR (apd.accounting_date, ‘DAY’),
‘FRIDAY ‘, apd.accounting_date,
NEXT_DAY (apd.accounting_date, ‘Friday’)
) week_ending,*/
NULL week_ending,
— Spaces added to account for padding done by DAY formatting option
apd.accounting_date expenditure_date,
ai.invoice_currency_code denom_currency_code, apd.amount,
DECODE (ai.invoice_currency_code,
sob.currency_code, NVL (apd.amount, 0),
NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
) accounted_amount, — accounted_amount
apd.exchange_rate accounted_exchange_rate,
apd.exchange_rate_type accounted_exchange_type,
DECODE (ai.invoice_currency_code,
sob.currency_code, NVL (apd.amount, 0),
NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
)
* DECODE
(sob.currency_code,
‘USD’, 1,
(SELECT NVL (avg_rate, 0)
FROM gl_translation_rates
WHERE set_of_books_id = sob.set_of_books_id
AND period_name =
(SELECT period_name
FROM gl_periods
WHERE apd.accounting_date BETWEEN start_date
AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’)
AND to_currency_code = ‘USD’)
) usd_amount,
(SELECT NVL (avg_rate, 0)
FROM gl_translation_rates
WHERE set_of_books_id = sob.set_of_books_id
AND period_name =
(SELECT period_name
FROM gl_periods
WHERE apd.accounting_date BETWEEN start_date AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’)
AND to_currency_code = ‘USD’) usd_conversion_rate,
apd.accounting_date conversion_date, sob.NAME set_of_books_name,
sob.currency_code book_currency_code, gcc.segment1 company,
gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
gcc.segment5 FUNCTION, gcc.segment6 intercompany,
apd.description comments, ai.invoice_num ap_invoice_number,
‘AP’ subledger_name
FROM ap_invoices_all ai,
ap_invoice_distributions_all apd,
poz_suppliers_v pozs,
gl_code_combinations gcc,
gl_sets_of_books sob,
fnd_flex_values_vl fv,
–FND_VS_VALUES_TL fv,
fnd_vs_value_sets fvs
WHERE 1 = 1
AND ai.invoice_id = apd.invoice_id
AND ai.vendor_id = pozs.vendor_id
AND apd.dist_code_combination_id = gcc.code_combination_id
AND fv.flex_value_set_id = fvs.value_set_id
AND fvs.description = ‘Department THG’ –‘ANSR_GL_DEPARTMENT’
AND fv.flex_value = gcc.segment3
AND pozs.vendor_type_lookup_code != ‘EMPLOYEE’
AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id
AND ai.set_of_books_id = sob.set_of_books_id
UNION ALL
/******************************************************************************************************
This section provides details from AP where the invoice is for an Employee but have not been coded to
a project. This is usually an entry error.
*******************************************************************************************************/
SELECT (SELECT period_name
FROM gl_periods
WHERE apd.accounting_date BETWEEN start_date AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’) gl_period,
(SELECT ‘Q’
|| quarter_num
|| ‘-WK’
|| LPAD (TO_CHAR (DECODE (period_num,
53, 14,
DECODE (MOD (period_num, 13),
0, 13,
MOD (period_num, 13)
)
)
),
2,
‘0’
) planning_week
FROM gl_periods
WHERE period_type = ‘4_4_57130283831′
–’22’ — PA Periods
AND TRUNC (apd.accounting_date) BETWEEN start_date AND end_date
AND adjustment_period_flag = ‘N’) planning_week,
fv.description expenditure_organization, ai.project_id project_id,
NVL ((SELECT p.segment1
FROM pjf_projects_all_vl p
WHERE p.project_id(+) = ai.project_id), ‘N/A’) project_number,
NVL ((SELECT p.NAME
FROM pjf_projects_all_vl p
WHERE project_id(+) = ai.project_id), ‘N/A’) project_name,
NULL project_type_class_code,
pozs.vendor_name employee_vendor,
NVL
((SELECT per.person_number
FROM per_all_people_f per
WHERE per.person_id = pozs.party_id
AND apd.accounting_date BETWEEN per.effective_start_date
AND per.effective_end_date),
‘N/A’
) employee_number,
‘AP Invoice’ task_name, ‘AP Invoice’ expenditure_category,
‘AP Invoice’ expenditure_type,
/*DECODE (TO_CHAR (apd.accounting_date, ‘DAY’),
‘FRIDAY ‘, apd.accounting_date,
NEXT_DAY (apd.accounting_date, ‘Friday’)
) week_ending,*/
NULL week_ending,
— Spaces added to account for padding done by DAY formatting option
apd.accounting_date expenditure_date,
ai.invoice_currency_code denom_currency_code, apd.amount,
DECODE (ai.invoice_currency_code,
sob.currency_code, NVL (apd.amount, 0),
NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
) accounted_amount, — accounted_amount
apd.exchange_rate accounted_exchange_rate,
apd.exchange_rate_type accounted_exchange_type,
DECODE (ai.invoice_currency_code,
sob.currency_code, NVL (apd.amount, 0),
NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
)
* DECODE
(sob.currency_code,
‘USD’, 1,
(SELECT NVL (avg_rate, 0)
FROM gl_translation_rates
WHERE set_of_books_id = sob.set_of_books_id
AND period_name =
(SELECT period_name
FROM gl_periods
WHERE apd.accounting_date BETWEEN start_date
AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’)
AND to_currency_code = ‘USD’)
) usd_amount,
(SELECT NVL (avg_rate, 0)
FROM gl_translation_rates
WHERE set_of_books_id = sob.set_of_books_id
AND period_name =
(SELECT period_name
FROM gl_periods
WHERE apd.accounting_date BETWEEN start_date AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’)
AND to_currency_code = ‘USD’) usd_conversion_rate,
apd.accounting_date conversion_date, sob.NAME set_of_books_name,
sob.currency_code book_currency_code, gcc.segment1 company,
gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
gcc.segment5 FUNCTION, gcc.segment6 intercompany,
apd.description comments, ai.invoice_num ap_invoice_number,
‘AP’ subledger_name
FROM ap_invoices_all ai,
ap_invoice_distributions_all apd,
poz_suppliers_v pozs,
gl_code_combinations gcc,
gl_sets_of_books sob,
fnd_flex_values_vl fv,
–FND_VS_VALUES_TL fv,
fnd_vs_value_sets fvs
WHERE 1 = 1
AND ai.invoice_id = apd.invoice_id
AND ai.vendor_id = pozs.vendor_id
AND apd.dist_code_combination_id = gcc.code_combination_id
AND fv.flex_value_set_id = fvs.value_set_id
AND fvs.description = ‘Department THG’ –‘ANSR_GL_DEPARTMENT’
AND fv.flex_value = gcc.segment3
AND pozs.vendor_type_lookup_code = ‘EMPLOYEE’
AND apd.project_id IS NULL
AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id
AND ai.set_of_books_id = sob.set_of_books_id
UNION ALL
/******************************************************************************************************
This section provides details from GL for journals entered mainly manually. Explicitly excludes
Payables, Projects, and Consolidation sources to avoid double counting.
*******************************************************************************************************/
(SELECT jl.period_name,
(SELECT ‘Q’
|| quarter_num
|| ‘-WK’
|| LPAD (TO_CHAR (DECODE (period_num,
53, 14,
DECODE (MOD (period_num, 13),
0, 13,
MOD (period_num, 13)
)
)
),
2,
‘0’
)
FROM gl_periods
WHERE period_type = ‘4_4_57130283831′
–’22’ — PA Periods
AND TRUNC (jl.effective_date) BETWEEN start_date AND end_date
AND adjustment_period_flag = ‘N’) planning_week,
fv.description expenditure_organization, 0 project_id,
‘N/A’ project_number, ‘N/A’ project_name,
‘N/A’ project_type_class_code, ‘N/A’ employee_vendor,
‘N/A’ employee_number, ‘N/A’ task_name, jh.NAME,
jl.description,
DECODE (TO_CHAR (jl.effective_date, ‘DAY’),
‘FRIDAY ‘, jl.effective_date,
NEXT_DAY (jl.effective_date, ‘Friday’)
) week_ending,
— Spaces added to account for padding done by DAY formatting option
jl.effective_date, jh.currency_code,
NVL (jl.entered_dr, 0) – NVL (jl.entered_cr, 0) amount,
NVL (jl.accounted_dr, 0)
– NVL (jl.accounted_cr, 0) accounted_amount,
jh.currency_conversion_rate accounted_exchange_rate,
jh.currency_conversion_type accounted_exchange_type,
DECODE
(sob.currency_code, –jh.currency_code,
‘USD’, ( NVL (jl.accounted_dr, 0)
– NVL (jl.accounted_cr, 0)),
NVL
(( ( NVL (jl.accounted_dr, 0)
– NVL (jl.accounted_cr, 0)
)
* (SELECT NVL (avg_rate, 0)
FROM gl_translation_rates
WHERE set_of_books_id = sob.set_of_books_id
AND period_name =
(SELECT period_name
FROM gl_periods
WHERE jl.effective_date BETWEEN start_date
AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’)
AND to_currency_code = ‘USD’
)
),
0
)
) usd_amount,
(SELECT NVL (avg_rate, 0)
FROM gl_translation_rates
WHERE set_of_books_id = sob.set_of_books_id
AND period_name =
(SELECT period_name
FROM gl_periods
WHERE jl.effective_date BETWEEN start_date AND end_date
AND period_set_name = ‘THG 4/4/5’
AND period_type = ‘4_4_57130283831’
AND adjustment_period_flag = ‘N’)
AND to_currency_code = ‘USD’) usd_conversion_rate,
jl.effective_date conversion_date,
sob.NAME set_of_books_name,
sob.currency_code book_currency_code, gl.segment1 company,
gl.segment2 ACCOUNT, gl.segment3 department,
gl.segment4 region, gl.segment5 FUNCTION,
gl.segment6 intercompany, jl.description,
NULL ap_invoice_number, ‘GL’ subledger_name
FROM gl_je_headers jh,
gl_je_lines jl,
gl_code_combinations gl,
gl_sets_of_books sob,
fnd_flex_values_vl fv,
–FND_VS_VALUES_TL fv,
fnd_vs_value_sets fvs
WHERE jl.je_header_id = jh.je_header_id
AND fv.flex_value_set_id = fvs.value_set_id
AND fvs.description = ‘Department THG’ –‘ANSR_GL_DEPARTMENT’
AND fv.flex_value = gl.segment3
AND jh.je_source NOT IN
(‘Consolidation’, ‘Payables’, ‘Projects’)
AND jl.code_combination_id = gl.code_combination_id
AND sob.chart_of_accounts_id = gl.chart_of_accounts_id
AND jh.ledger_id = sob.set_of_books_id
AND jh.period_name = jl.period_name)
Summary
This Post described the script for SGA Details – JROD COPY in oracle EBS.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
