Introduction
This Post illustrates the steps required to get the AP Supplier Invoices in – FUSION Application
Script to AP Supplier 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,
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,
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,
poz_suppliers ps,
hz_parties hp,
gl_ledgers gl,
gl_periods gp
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.vendor_id = ps.vendor_id
AND hp.party_id = ps.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’
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
