Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 29 August 2019

Query to get Corporate Card Inter-Company Accounting Details in Oracle apps R12


SELECT DISTINCT aeh.invoice_num exp_rept_num
              , aeh.report_header_id
              , ael.report_line_id
              , ael.merchant_name
              , ( SELECT segment1
                 FROM   po_vendors pv
                      , ap_invoices_all aia
                 WHERE  pv.vendor_id = aia.vendor_id
                 AND    aia.invoice_id = aila.invoice_id ) vendor_num
              ,
                glcc.segment1 company_code
              , glcc.segment2 dept_code
              , paf.full_name employee_name
              , xah.accounting_date
              , ael.creation_date expense_date
              , CASE
                   WHEN NVL( UPPER( :p_expense_type ), 'A' ) IN
                                                             ( 'Y', 'N', 'A' )
                      THEN CASE
                             WHEN ael.credit_card_trx_id IS NOT NULL
                                THEN 'Credit Card'
                             WHEN ael.credit_card_trx_id IS NULL
                                THEN 'Out Of Pocket and Per-diem'
                          END
                END expense_type
              , aerp.prompt expense_item
              , xdl.unrounded_entered_dr debit
              , xdl.unrounded_entered_cr credit
              , (   NVL( xdl.unrounded_entered_dr, 0 )
                  - NVL( xdl.unrounded_entered_cr, 0 )
                ) dr_cr
              , glcc1.segment6 accounting_company_code
              , xal.code_combination_id
              , xal.ae_line_num
              ,    glcc1.segment1
                || '.'
                || glcc1.segment2
                || '.'
                || glcc1.segment3
                || '.'
                || glcc1.segment4
                || '.'
                || glcc1.segment5
                || '.'
                || glcc1.segment6
                || '.'
                || glcc1.segment7 charge_account
              -- Added by GSINGH FOR Version 1.5
,               aeh.week_end_date
              , ap_web_policy_utils.get_lookup_meaning
                                        ( 'EXPENSE REPORT STATUS'
                                        , DECODE
                                               ( ( SELECT ai.cancelled_date
                                                  FROM   ap_invoices_all ai
                                                  WHERE  ai.invoice_id =
                                                                   aeh.vouchno )
                                               , NULL, aeh.expense_status_code
                                               , 'CANCELLED'
                                               )
                                        ) report_status
              , ap_web_policy_utils.get_lookup_meaning( 'OIE_AUDIT_TYPES'
                                                      , NVL( aeh.audit_code
                                                           , 'AUDIT'
                                                           )
                                                      ) audit_type
              , ap_web_policy_utils.get_lookup_meaning( 'RECEIPT_STATUS'
                                                      , aeh.receipts_status
                                                      )
                                                       receipt_package_status
              , ( SELECT DISTINCT LISTAGG(ppa.segment1,'~')  WITHIN GROUP (ORDER BY  ppa.segment1) a
                 FROM   pa_projects_all ppa
                      , ap_exp_report_dists_all aerd
                 WHERE  ppa.project_id = aerd.project_id
                 AND    aeh.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id = ael.report_line_id )
                                                               project_number
        ,(SELECT DISTINCT LISTAGG(pt.task_number,'~')  WITHIN GROUP (ORDER BY  task_number) a FROM  pa_tasks pt
                      , ap_exp_report_dists_all aerd
                 WHERE  pt.task_id = aerd.task_id
                   AND    aerd.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id =ael.report_line_id ) task_number  
              , ( SELECT LISTAGG(ho.name,'~')  WITHIN GROUP (ORDER BY  ho.name) a
                 FROM   hr_all_organization_units ho
                      , ap_exp_report_dists_all aerd
                 WHERE  ho.organization_id = aerd.expenditure_organization_id
                 AND    aeh.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id = ael.report_line_id ) exp_org
              , ael.justification
-- END
FROM            ap_expense_report_headers_all aeh
              , ap_expense_report_lines_all ael
              , ap_expense_report_params_all aerp
              , ap_invoice_distributions_all aid
              , xla_ae_headers xah
              , xla_ae_lines xal
              , xla_distribution_links xdl
              , ap_invoice_lines_all aila
              , per_all_people_f paf
              , per_all_assignments_f pasf
              , gl_code_combinations glcc
              , gl_code_combinations glcc1
WHERE           aeh.report_header_id = ael.report_header_id
AND             aeh.vouchno != 0
AND             EXISTS(
                   SELECT 'X'
                   FROM   ap_expense_report_lines_all
                   WHERE  report_line_id = ael.report_line_id
                   AND    UPPER( :p_expense_type ) = 'Y'
                   AND    credit_card_trx_id IS NOT NULL )
AND             xal.code_combination_id = glcc1.code_combination_id
AND             aeh.employee_id = paf.person_id
AND             TRUNC( SYSDATE ) BETWEEN TRUNC( paf.effective_start_date )
                                     AND TRUNC( paf.effective_end_date )
AND             paf.person_id = pasf.person_id
AND             TRUNC( ael.creation_date )
                   BETWEEN TRUNC( pasf.effective_start_date )
                       AND TRUNC( pasf.effective_end_date )
AND             pasf.default_code_comb_id = glcc.code_combination_id
AND             ael.web_parameter_id = aerp.parameter_id(+)
AND             aeh.vouchno = aid.invoice_id
AND             aid.line_type_lookup_code = 'ITEM'
AND             aid.accounting_event_id = xah.event_id
AND             aid.invoice_distribution_id = xdl.source_distribution_id_num_1
AND             xah.ae_header_id = xal.ae_header_id
AND             xal.ae_line_num = xdl.ae_line_num
AND             xal.ae_header_id = xdl.ae_header_id
AND             xah.ae_header_id = xdl.ae_header_id
AND             xah.event_id = xdl.event_id
AND             xdl.rounding_class_code = 'ITEM EXPENSE'
AND             aeh.vouchno = aila.invoice_id
AND             ael.report_line_id = aila.reference_key2
AND             aila.line_type_lookup_code = 'ITEM'
AND             aila.line_number = aid.invoice_line_number                                    

Share this article :

0 comments:

Post a Comment