JG Global Account Analysis

Description
Categories: BI Publisher
Imported from BI Publisher
Application: Regional Localizations
Source: Global Account Analysis Report
Short Name: JGSLAAANA
DB package: XLA_ACCT_ANALYSIS_RPT_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

 SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
     	 zxr.tax_regime_name                                TAX_REGIME_NAME
		,zxl.tax_regime_code                                TAX_REGIME_CODE
        ,zxl.tax                                            TAX
        ,ztt.tax_full_name                                  TAX_NAME
        ,zst.tax_status_name                                TAX_STATUS_NAME
		,zxl.tax_status_code                                TAX_STATUSC_CODE
        ,zrt.tax_rate_name                                  TAX_RATE_NAME
        ,zxl.tax_rate                                       TAX_RATE
        ,zxl.tax_type_code                                  TAX_TYPE_CODE
        ,zxl.tax_rate_code                                  TAX_RATE_CODE
FROM     xla_distribution_links         xdl
        ,zx_lines                       zxl
        ,zx_regimes_tl                  zxr
        ,zx_taxes_tl                    ztt
        ,zx_status_tl                   zst
        ,zx_rates_tl                    zrt
WHERE    :p_include_tax_details_flag = 'Y'
  AND    zxr.tax_regime_id(+)                = zxl.tax_regime_id
  AND    zxr.language(+)                     = USERENV('LANG')
  AND    ztt.tax_id(+)                       = zxl.tax_id
  AND    ztt.language(+)                     = USERENV('LANG')
  AND    zst.tax_status_id(+)                = zxl.tax_status_id
  AND    zst.language(+)                     = USERENV('LANG')
  AND    zrt.tax_rate_id(+)                  = zxl.tax_rate_id
  AND    zrt.language(+)                     = USERENV('LANG')
  AND    xdl.application_id                  = :APPLICATION_ID
  AND    xdl.ae_header_id                    = :HEADER_ID
  AND    xdl.ae_line_num                     = :ORIG_LINE_NUMBER
  AND    xdl.tax_line_ref_id    = zxl.tax_line_id
  AND    :ACCOUNTING_CLASS_CODE in ('TAX','RTAX','NRTAX')
UNION ALL
SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
     	 zxr.tax_regime_name                                TAX_REGIME_NAME
		,zxl.tax_regime_code                                TAX_REGIME_CODE
        ,zxl.tax                                            TAX
        ,ztt.tax_full_name                                  TAX_NAME
        ,zst.tax_status_name                                TAX_STATUS_NAME
		,zxl.tax_status_code                                TAX_STATUSC_CODE
        ,zrt.tax_rate_name                                  TAX_RATE_NAME
        ,zxl.tax_rate                                       TAX_RATE
        ,zxl.tax_type_code                                  TAX_TYPE_CODE
        ,zxl.tax_rate_code                                  TAX_RATE_CODE
FROM     xla_distribution_links         xdl
        ,zx_lines                       zxl
        ,zx_regimes_tl                  zxr
        ,zx_taxes_tl                    ztt
        ,zx_status_tl                   zst
        ,zx_rates_tl                    zrt
        ,ra_cust_trx_line_gl_dist_all   rd
WHERE    :p_include_tax_details_flag = 'Y'
  AND    zxr.tax_regime_id(+)                = zxl.tax_regime_id
  AND    zxr.language(+)                     = USERENV('LANG')
  AND    ztt.tax_id(+)                       = zxl.tax_id
  AND    ztt.language(+)                     = USERENV('LANG')
  AND    zst.tax_status_id(+)                = zxl.tax_status_id
  AND    zst.language(+)                     = USERENV('LANG')
  AND    zrt.tax_rate_id(+)                  = zxl.tax_rate_id
  AND    zrt.language(+)                     = USERENV('LANG')
  AND    xdl.application_id                  = 222 --:APPLICATION_ID
  AND    xdl.ae_header_id                    = :HEADER_ID
  AND    xdl.ae_line_num                     = :ORIG_LINE_NUMBER
  AND    xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
  AND    rd.cust_trx_line_gl_dist_id=xdl.source_distribution_id_num_1
  AND    rd.customer_trx_line_id = zxl.trx_line_id
  AND    rd.customer_trx_id = zxl.trx_id  
  AND    zxl.application_id =222 
  AND    :ACCOUNTING_CLASS_CODE in ('REVENUE','SUSPENSE','UNBILL','UNEARNED_REVENUE')
UNION ALL 
  SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
     	 zxr.tax_regime_name                                TAX_REGIME_NAME
		,zxl.tax_regime_code                                TAX_REGIME_CODE
        ,zxl.tax                                            TAX
        ,ztt.tax_full_name                                  TAX_NAME
        ,zst.tax_status_name                                TAX_STATUS_NAME
		,zxl.tax_status_code                                TAX_STATUSC_CODE
        ,zrt.tax_rate_name                                  TAX_RATE_NAME
        ,zxl.tax_rate                                       TAX_RATE
        ,zxl.tax_type_code                                  TAX_TYPE_CODE
        ,zxl.tax_rate_code                                  TAX_RATE_CODE
FROM     xla_distribution_links         xdl
        ,zx_lines                       zxl
        ,zx_regimes_tl                  zxr
        ,zx_taxes_tl                    ztt
        ,zx_status_tl                   zst
        ,zx_rates_tl                    zrt
        ,ra_cust_trx_line_gl_dist_all   rd
        ,ra_customer_trx_lines_all      rl
WHERE    :p_include_tax_details_flag = 'Y'
  AND    zxr.tax_regime_id(+)                = zxl.tax_regime_id
  AND    zxr.language(+)                     = USERENV('LANG')
  AND    ztt.tax_id(+)                       = zxl.tax_id
  AND    ztt.language(+)                     = USERENV('LANG')
  AND    zst.tax_status_id(+)                = zxl.tax_status_id
  AND    zst.language(+)                     = USERENV('LANG')
  AND    zrt.tax_rate_id(+)                  = zxl.tax_rate_id
  AND    zrt.language(+)                     = USERENV('LANG')
  AND    xdl.application_id                  = 222 --:APPLICATION_ID
  AND    xdl.ae_header_id                    = :HEADER_ID
  AND    xdl.ae_line_num                     = :ORIG_LINE_NUMBER
  AND    rd.customer_trx_id = rl.customer_trx_id
  AND    rd.customer_trx_line_id= rl.customer_trx_line_id
  AND    xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
  AND    xdl.APPLIED_TO_DISTRIBUTION_TYPE='RA_CUST_TRX_LINE_GL_DIST_ALL'
  AND    rd.cust_trx_line_gl_dist_id=xdl.APPLIED_TO_DIST_ID_NUM_1
  AND    rd.customer_trx_id = zxl.trx_id
  AND    (rl.customer_trx_line_id = zxl.trx_line_id
         or rl.tax_line_id= zxl.tax_line_id)  
  AND    zxl.application_id =222 
  AND    :ACCOUNTING_CLASS_CODE in ('REVENUE','SUSPENSE','UNBILL','UNEARNED_REVENUE','TAX')
UNION ALL
  SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
     	 zxr.tax_regime_name                                TAX_REGIME_NAME
		,zxl.tax_regime_code                                TAX_REGIME_CODE
        ,zxl.tax                                            TAX
        ,ztt.tax_full_name                                  TAX_NAME
        ,zst.tax_status_name                                TAX_STATUS_NAME
		,zxl.tax_status_code                                TAX_STATUSC_CODE
        ,zrt.tax_rate_name                                  TAX_RATE_NAME
        ,zxl.tax_rate                                       TAX_RATE
        ,zxl.tax_type_code                                  TAX_TYPE_CODE
        ,zxl.tax_rate_code                                  TAX_RATE_CODE
FROM     xla_distribution_links         xdl
        ,zx_lines                       zxl
        ,zx_regimes_tl                  zxr
        ,zx_taxes_tl                    ztt
        ,zx_status_tl                   zst
        ,zx_rates_tl                    zrt
        ,ap_invoice_distributions_all  ID
WHERE    :p_include_tax_details_flag = 'Y'
  AND    zxr.tax_regime_id(+)                = zxl.tax_regime_id
  AND    zxr.language(+)                     = USERENV('LANG')
  AND    ztt.tax_id(+)                       = zxl.tax_id
  AND    ztt.language(+)                     = USERENV('LANG')
  AND    zst.tax_status_id(+)                = zxl.tax_status_id
  AND    zst.language(+)                     = USERENV('LANG')
  AND    zrt.tax_rate_id(+)                  = zxl.tax_rate_id
  AND    zrt.language(+)                     = USERENV('LANG')
  AND    xdl.application_id                  = 200 --:APPLICATION_ID
  AND    xdl.ae_header_id                    = :HEADER_ID
  AND    xdl.ae_line_num                     = :ORIG_LINE_NUMBER
  AND    ID.invoice_distribution_id=xdl.source_distribution_id_num_1
  AND    xdl.source_distribution_type = 'AP_INV_DIST'
  AND    xdl.application_id = 200 
  AND    ID.invoice_line_number = zxl.trx_line_id
  AND    ID.invoice_id = zxl.trx_id
  AND    zxl.application_id =200
  AND    :ACCOUNTING_CLASS_CODE in ('ITEM EXPENSE','ACCRUAL')
 UNION ALL
 SELECT 
         zxrgtl.tax_regime_name                             TAX_REGIME_NAME
 		,zxrgb.tax_regime_code                              TAX_REGIME_CODE
        ,zxtb.tax                                           TAX
        ,zxttl.tax_full_name                                TAX_NAME
        ,zxtstl.tax_status_name                             TAX_STATUS_NAME
		,zxtsb.tax_status_code                               TAX_STATUSC_CODE
        ,zxrtl.tax_rate_name                                TAX_RATE_NAME
        ,zxrb.percentage_rate                               TAX_RATE
        ,zxtb.tax_type_code                                 TAX_TYPE_CODE
        ,zxrb.tax_rate_code                                 TAX_RATE_CODE
FROM     gl_je_lines   jel
        ,zx_rates_b    zxrb
        ,zx_rates_tl   zxrtl
        ,zx_taxes_b    zxtb
        ,zx_taxes_tl   zxttl
        ,zx_regimes_b  zxrgb
        ,zx_regimes_tl zxrgtl
        ,zx_status_b   zxtsb      
        ,zx_status_tl  zxtstl 
WHERE    :p_include_tax_details_flag = 'Y'
  AND    jel.je_header_id                    = :HEADER_ID
  AND    jel.je_line_num                     = :ORIG_LINE_NUMBER
  AND    jel.ledger_id                       = :LEDGER_ID
  AND    jel.tax_code_id  = zxrb.tax_rate_id
  AND    zxrb.tax_rate_id = zxrtl.tax_rate_id
  AND    zxtb.tax_id = zxttl.tax_id
  AND    zxrgb.tax_regime_id = zxrgtl.tax_regime_id
  AND    zxtsb.tax_status_id = zxtstl.tax_status_id
  AND    zxrtl.language = userenv('LANG') 
  AND    zxttl.language = userenv('LANG')
  AND    zxrgtl.language = userenv('LANG')
  AND    zxtstl.language = userenv('LANG')
  AND    zxrb.tax_regime_code  = zxtb.tax_regime_code
  AND    zxrb.tax              = zxtb.tax
  AND    (zxrb.content_owner_id = zxtb.content_owner_id OR zxtb.content_owner_id = -99)
  AND  zxrb.tax_regime_code = zxrgb.tax_regime_code
  AND  zxrb.tax_regime_code = zxtsb.tax_regime_code
  AND  zxrb.tax = zxtsb.tax
  AND  zxrb.tax_status_code = zxtsb.tax_status_code
  AND  (zxrb.content_owner_id = zxtsb.content_owner_id OR zxtsb.content_owner_id = -99)
Parameter Name SQL text Validation
Ledger
 
LOV Oracle
Legal Entity
 
LOV Oracle
Period From
 
LOV Oracle
Period To
 
LOV Oracle
GL Date From
 
Date
GL Date To
 
Date
Accounting Flexfield From
 
Char
Accounting Flexfield To
 
Char
Display VAT Code
 
LOV Oracle
Include Legal Entity Information
 
LOV Oracle
Include Zero Amount Lines
 
LOV Oracle
First Flexfield Segment Used
 
LOV Oracle
Second Flexfield Segment Used
 
LOV Oracle
Third Flexfield Segment Used
 
LOV Oracle
First Report Sort
 
LOV Oracle
Second Report Sort
 
LOV Oracle
Third Report Sort
 
LOV Oracle
First Page Number
 
Number
Assign Zero Beginning Balances
 
LOV Oracle
Include Accounts With No Activity
 
LOV Oracle