Introduction
This Post illustrates the steps required to get the AP – Employee invoices query FUSION Application
Script to AP – Employee invoices query – FUSION
SELECT gp.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’
) planning_week
FROM gl_periods gp
WHERE 1 = 1
AND dist.accounting_date BETWEEN start_date AND end_date
AND adjustment_period_flag = ‘N’
AND gp.period_set_name = gl.period_set_name)
planning_week,
(select fv.description from
fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
where
fv.flex_value_set_id = fvs.flex_value_set_id
AND fvs.flex_value_set_name = ‘Department’
AND fv.flex_value = gCC.segment3
) expenditure_organization
, inv.project_id project_id,
NVL ((SELECT p.segment1
FROM pjf_projects_all_b p
WHERE p.project_id = inv.project_id),
‘N/A’
) project_number,
NVL ((SELECT p.NAME
FROM pjf_projects_all_tl p
WHERE project_id(+) = inv.project_id),
‘N/A’) project_name, NULL project_type_class_code,
hp.party_name employee_vendor, papf.person_number employee_number,
DECODE (TO_CHAR (dist.accounting_date, ‘DAY’),
5, dist.accounting_date,
NEXT_DAY (dist.accounting_date, 6)
) week_ending,
dist.accounting_date expenditure_date,
inv.invoice_currency_code entered_currency_code,
dist.amount entered_amount,
DECODE (inv.invoice_currency_code,
gl.currency_code, NVL (dist.amount, 0),
NVL (dist.amount, 0) * NVL (dist.exchange_rate, 0)
) accounted_amount, — accounted_amount
dist.exchange_rate accounted_exchange_rate,
dist.exchange_rate_type accounted_exchange_type,
( DECODE (inv.invoice_currency_code,
gl.currency_code, NVL (dist.amount, 0),
NVL (dist.amount, 0) * NVL (dist.exchange_rate, 1)
)
* NVL ((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type = ‘Corporate’
AND gdr.to_currency = ‘USD’
AND gdr.from_currency = gl.currency_code
AND gdr.conversion_date = dist.accounting_date),
1
)
) usd_amount,
NVL ((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type = ‘Corporate’
AND gdr.to_currency = ‘USD’
AND gdr.from_currency = gl.currency_code
AND gdr.conversion_date = dist.accounting_date),
1
) usd_conversion_rate,
dist.accounting_date conversion_date,
gl.NAME set_of_books_name,
gl.currency_code book_currency_code, gcc.segment1 company,
gcc.segment2 ACCOUNT, gcc.segment3 department,
gcc.segment4 region, gcc.segment5 FUNCTION,
gcc.segment6 intercompany, dist.description comments,
inv.invoice_num ap_invoice_number,
gcc.segment1,
(SELECT class_code
FROM pjf_project_classes prc,
pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = inv.project_id) project_region_code
FROM ap_invoice_distributions_all dist,
ap_invoice_lines_all line,
ap_invoices_all inv,
gl_code_combinations gcc,
hz_parties hp,
gl_ledgers gl,
gl_periods gp,
PER_ALL_PEOPLE_F_V papf
WHERE dist.invoice_id = line.invoice_id
AND dist.invoice_line_number = line.line_number
AND line.invoice_id = inv.invoice_id
AND dist.dist_code_combination_id = gcc.code_combination_id
AND inv.party_Id = hp.party_id
AND inv.set_of_books_id = gl.ledger_id
AND gp.period_set_name = gl.period_set_name
AND dist.accounting_date BETWEEN gp.start_date AND gp.end_date
AND gp.adjustment_period_flag = ‘N’
and hp.orig_system_reference=papf.person_id
AND dist.project_id IS NULL
and sysdate between nvl(papf.EFFECTIVE_START_DATE,sysdate-1) and nvl(papf.EFFECTIVE_END_DATE,sysdate+1)
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
