SELECT employee_number,
employee_name,
functional_currency,
SUM(NVL(ledger_bal_as_at_from_date,0)) ledger_bal_as_at_from_date,
SUM(concur_postings_in_period) concur_postings_in_period,
SUM(gl_postings_in_period) gl_postings_in_period,
(xxb_ap_exp_sum_led_bal_pk.get_le_bal_by_curr_func(:p_legal_entity,:P_NATURAL_ACCOUNT,:P_GL_DATE_FROM,functional_currency,entered_currency)) ledger_bal_as_to_date,
entered_currency,
SUM(NVL(entered_ledger_bal_from_date,0))entered_ledger_bal_from_date,
SUM(entered_concur_post_in_period) entered_concur_post_in_period,
SUM(entered_gl_postings_in_period) entered_gl_postings_in_period,
(xxb_ap_exp_sum_led_bal_pk.get_le_bal_by_curr_trans(:p_legal_entity,:P_NATURAL_ACCOUNT,:P_GL_DATE_FROM,entered_currency)) entered_ledger_bal_as_to_date,
(xxb_ap_exp_sum_led_bal_pk.get_le_total_bal_func(:p_legal_entity,:P_NATURAL_ACCOUNT,:P_GL_DATE_FROM)) total_le_bal_func,
(xxb_ap_exp_sum_led_bal_pk.get_le_total_bal_trans(:p_legal_entity,:P_NATURAL_ACCOUNT,:P_GL_DATE_FROM)) total_le_bal_trans
FROM (SELECT papf.employee_number,
papf.full_name employee_name,
gl.currency_code functional_currency,
NULL ledger_bal_as_at_from_date,
SUM (DECODE (gl.currency_code,
aia.invoice_currency_code, aida.amount,
aida.base_amount
)
) concur_postings_in_period,
NULL gl_postings_in_period,
NULL ledger_bal_as_to_date,
aia.invoice_currency_code entered_currency,
NULL entered_ledger_bal_from_date,
SUM (aida.amount) entered_concur_post_in_period,
NULL entered_gl_postings_in_period,
NULL enterd_ledger_bal_as_to_date
FROM ap_invoices_all aia,
per_all_people_f papf,
gl_periods gp,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
gl_code_combinations gcc,
gl_ledgers gl,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
fnd_lookup_values_vl flvv
WHERE 1 = 1
AND gjh.default_effective_date >= gp.start_date
AND gjh.default_effective_date <= gp.end_date
AND aia.invoice_id = aila.invoice_id
AND aia.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aila.attribute4 = papf.employee_number
AND aida.dist_code_combination_id = gcc.code_combination_id
AND aida.charge_applicable_to_dist_id IS NULL
AND aia.set_of_books_id = gl.ledger_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.ledger_id = gl.ledger_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.ae_header_id = xal.ae_header_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.alloc_to_source_id_num_1 = aia.invoice_id
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xal.application_id = xah.application_id
AND gjl.je_header_id = gir.je_header_id
AND gl.ledger_category_code = 'PRIMARY'
AND NVL (gl.attribute1, 'N') = 'N'
AND ap_invoices_pkg.get_posting_status (aia.invoice_id) = 'Y'
AND NVL (flvv.lookup_type, 'XXB_AP_CONCUR_PAYTYPE_LKP') = 'XXB_AP_CONCUR_PAYTYPE_LKP'
AND NVL (flvv.enabled_flag, 'Y') = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (flvv.start_date_active, TRUNC (SYSDATE) )
AND NVL (flvv.end_date_active, TRUNC (SYSDATE) + 1)
AND flvv.attribute1(+) = aia.pay_group_lookup_code
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND gp.period_set_name = 'BXGLOBALFISCAL'
AND gjh.default_effective_date >=
TRUNC (TO_DATE (:p_gl_date_from, 'RRRR/MM/DD HH24:MI:SS') )
AND gjh.default_effective_date <=
TRUNC (TO_DATE (:p_gl_date_to, 'RRRR/MM/DD HH24:MI:SS') )
AND gcc.segment1 = NVL (:p_legal_entity, gcc.segment1)
AND gcc.segment4 = NVL (:p_natural_account, gcc.segment4)
-- AND papf.employee_number = '100954'--'109979'
GROUP BY papf.employee_number, papf.full_name, gl.currency_code,
aia.invoice_currency_code
UNION ALL
SELECT papf.employee_number,
papf.full_name employee_name,
gl.currency_code functional_currency,
NULL ledger_bal_as_at_from_date,
NULL concur_postings_in_period,
SUM (NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) )
gl_postings_in_period,
NULL ledger_bal_as_to_date,
gjh.currency_code entered_currency,
NULL entered_ledger_bal_from_date,
NULL entered_concur_post_in_period,
SUM (NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0) )
entered_gl_postings_in_period,
NULL enterd_ledger_bal_as_to_date
FROM gl_je_lines gjl,
per_all_people_f papf,
gl_je_headers gjh,
gl_je_sources gjs,
gl_je_batches gjb,
gl_je_categories gjc,
gl_code_combinations gcc,
gl_ledgers gl
WHERE gjl.attribute2 = papf.employee_number
AND gjh.je_header_id = gjl.je_header_id
AND gjh.je_source = gjs.je_source_name
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_category = gjc.je_category_name
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.ledger_id = gl.ledger_id
AND gl.ledger_category_code = 'PRIMARY'
AND NVL (gl.attribute1, 'N') = 'N'
AND gjh.status = 'P'
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND gjh.default_effective_date >=
TRUNC (TO_DATE (:p_gl_date_from, 'RRRR/MM/DD HH24:MI:SS') )
AND gjh.default_effective_date <=
TRUNC (TO_DATE (:p_gl_date_to, 'RRRR/MM/DD HH24:MI:SS') )
AND gcc.segment1 = NVL (:p_legal_entity, gcc.segment1)
AND gcc.segment4 = NVL (:p_natural_account, gcc.segment4)
-- AND papf.employee_number = '100954'
GROUP BY papf.employee_number, papf.full_name, gl.currency_code, gjh.currency_code
HAVING (
(SUM (NVL (gjl.entered_dr, 0)) - SUM (NVL (gjl.entered_cr, 0)))<>0
OR (SUM (NVL (gjl.accounted_dr, 0)) - SUM (NVL (gjl.accounted_cr, 0)))<>0
) -- added in ver 1.1
UNION ALL
SELECT papf.employee_number employee_number,
papf.full_name employee_name ,
gll.currency_code functional_currency,
NVL (SUM (DECODE (gll.currency_code,
aia.invoice_currency_code, aida.amount,
aida.base_amount )
),
0
) ledger_bal_as_at_from_date,
NULL concur_postings_in_period,
NULL gl_postings_in_period,
NULL ledger_bal_as_to_date,
aia.invoice_currency_code entered_currency,
NVL (SUM (aida.amount), 0) entered_ledger_bal_from_date ,
NULL entered_concur_post_in_period,
NULL entered_gl_postings_in_period,
NULL enterd_ledger_bal_as_to_date
FROM ap_invoices_all aia,
per_all_people_f papf,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
gl_code_combinations gcc,
gl_ledgers gll,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
fnd_lookup_values_vl flvv
WHERE 1 = 1
AND aia.invoice_id = aila.invoice_id
AND aia.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aila.attribute4 = papf.employee_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND aida.dist_code_combination_id = gcc.code_combination_id
AND aia.set_of_books_id = gll.ledger_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.ledger_id = gll.ledger_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.ae_header_id = xal.ae_header_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.alloc_to_source_id_num_1 = aia.invoice_id
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xal.application_id = xah.application_id
AND gjl.je_header_id = gir.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gll.ledger_category_code = 'PRIMARY'
AND NVL (gll.attribute1, 'N') = 'N'
AND ap_invoices_pkg.get_posting_status (aia.invoice_id) = 'Y'
AND NVL (flvv.lookup_type, 'XXB_AP_CONCUR_PAYTYPE_LKP') = 'XXB_AP_CONCUR_PAYTYPE_LKP'
AND NVL (flvv.enabled_flag, 'Y') = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (flvv.start_date_active, TRUNC (SYSDATE) )
AND NVL (flvv.end_date_active, TRUNC (SYSDATE) + 1)
AND flvv.attribute1(+) = aia.pay_group_lookup_code
AND gjh.default_effective_date >=
TRUNC (TO_DATE ( (SELECT fpov.profile_option_value
FROM fnd_profile_options fpo, fnd_profile_option_values fpov
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name =
'BX EMPLOYEE RECONCILIATION CUT OFF DATE'),
'DD-MON-RRRR HH24:MI:SS'
)
)
AND TRUNC(gjh.default_effective_date )<
TRUNC (TO_DATE (:p_gl_date_from, 'RRRR/MM/DD HH24:MI:SS') )
AND gcc.segment1 = NVL (:p_legal_entity, gcc.segment1)
AND gcc.segment4 = NVL (:p_natural_account, gcc.segment4)
GROUP BY papf.employee_number, papf.full_name, gll.currency_code, aia.invoice_currency_code
-- AND aia.invoice_currency_code = p_currency_code
UNION ALL
SELECT papf.employee_number,
papf.full_name employee_name ,
led.currency_code functional_currency,
SUM (NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) )
ledger_bal_as_at_from_date,
NULL concur_postings_in_period,
NULL gl_postings_in_period,
NULL ledger_bal_as_to_date,
gjh.currency_code entered_currency,
SUM (NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0) ) entered_ledger_bal_from_date,
NULL entered_concur_post_in_period,
NULL entered_gl_postings_in_period,
NULL enterd_ledger_bal_as_to_date
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations gcc,
per_all_people_f papf,
gl_ledgers led
WHERE 1 = 1
AND gjh.je_source NOT LIKE 'Pay%'
AND gjh.je_header_id = gjl.je_header_id
AND gjl.attribute2 =papf.employee_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment1 = :p_legal_entity
AND gcc.segment4 = :p_natural_account
AND led.ledger_id = gjh.ledger_id
AND led.ledger_category_code = 'PRIMARY'
AND TO_CHAR (gjl.effective_date, 'DD-MON-YYYY') >=
TO_DATE ( (SELECT fpov.profile_option_value
FROM fnd_profile_options fpo, fnd_profile_option_values fpov
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name =
'BX EMPLOYEE RECONCILIATION CUT OFF DATE'),
'DD-MON-YYYY'
)
AND TRUNC(gjl.effective_date) < TRUNC(TO_DATE (:p_gl_date_from, 'RRRR/MM/DD HH24:MI:SS'))
GROUP BY papf.employee_number, papf.full_name, gjh.currency_code, led.currency_code )
GROUP BY employee_number,employee_name ,functional_currency, entered_currency
Monday, 30 December 2013
Employee Expense Report
Subscribe to:
Posts (Atom)