JG Global Account Analysis

Description
Categories: BI Publisher
Application: Regional Localizations
Source: Global Account Analysis Report
Short Name: JGSLAAANA
DB package: XLA_ACCT_ANALYSIS_RPT_PKG
 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
P_INC_ACCT_WITH_NO_ACT
 
P_CUSTOM_PARAMETER_18
 
P_CUSTOM_PARAMETER_17
 
P_CUSTOM_PARAMETER_16
 
P_CUSTOM_PARAMETER_14
 
P_CUSTOM_PARAMETER_13
 
P_CUSTOM_PARAMETER_12
 
P_INCLUDE_ZERO_AMT_LINES
 
P_INCLUDE_LEGAL_ENTITY_INFO
 
P_INCLUDE_TAX_DETAILS
 
P_BALANCE_TYPE_CODE
 
LOV Oracle
P_COA_ID
 
LOV Oracle
P_LEGAL_ENTITY
 
P_LEDGER
 
P_RESP_APPLICATION_ID
 
Include Accounts With No Activity
 
LOV Oracle
Assign Zero Beginning Balances
 
LOV Oracle
First Page Number
 
Number
Third Report Sort
 
LOV Oracle
Second Report Sort
 
LOV Oracle
First Report Sort
 
LOV Oracle
Third Flexfield Segment Used
 
LOV Oracle
Second Flexfield Segment Used
 
LOV Oracle
First Flexfield Segment Used
 
LOV Oracle
Include Zero Amount Lines
 
LOV Oracle
Include Legal Entity Information
 
LOV Oracle
Display VAT Code
 
LOV Oracle
Accounting Flexfield To
 
Accounting Flexfield From
 
GL Date To
 
Date
GL Date From
 
Date
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ledger
 
LOV Oracle