GL Journals - Tax

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Journals - Tax (XML)
Short Name: GLXJETAX_XML
DB package: GL_GLXJETAX_XMLP_PKG
select 
gjl1.je_line_num C_JE_LINE_NUM,
gjl1.effective_date C_EFFECTIVE_DATE,
gjl1.accounted_dr C_TAXABLE_ACC_DR,
gjl1.accounted_cr C_TAXABLE_ACC_CR,
gjl1.tax_document_date C_TAX_DOCUMENT_DATE,
gjl1.tax_document_identifier C_TAX_DOCUMENT_IDENTIFIER,
gjl1.tax_customer_name C_TAX_CUSTOMER_NAME,
gjl1.tax_registration_number C_TAX_REGISTRATION_NUMBER,
gjl1.tax_group_id C_TAX_GROUP_ID,
gjh.je_header_id C_JE_HEADER_ID,
gjh.name C_NAME,
gjh.default_effective_date C_DEFAULT_EFFECTIVE_DATE,
gjl2.accounted_dr C_ACCOUNTED_DR,
gjl2.accounted_cr C_ACCOUNTED_CR,
&p_bal_segment C_BAL_SEGMENT,
&p_taxable_acc_segment C_TAXABLE_ACC_SEGMENT,
&p_tax_acc_segment C_TAX_ACC_SEGMENT,
gjl1.tax_code_id C_TAX_CODE_ID,
gjl1.tax_type_code C_TAX_TYPE_CODE,
rates.calculation_level_code C_CALCULATION_LEVEL_CODE,
gl.meaning C_TAX_TYPE_MEANING, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bal_segment_pad', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'PADDED_VALUE') C_BAL_SEGMENT_PAD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bal_segment_f', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') C_BAL_SEGMENT_F, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_secure', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'SECURITY') BAL_SECURE, 
	GL_GLXJETAX_XMLP_PKG.c_tax_codeformula(gjl1.tax_type_code, gjl1.tax_code_id) C_TAX_CODE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_tax_acc_flexfield_pad', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_TAX_ACC_FLEXFIELD_PAD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_tax_acc_flexfield', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_TAX_ACC_FLEXFIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('tax_secure', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'ALL', 'Y', 'SECURITY') TAX_SECURE, 
	GL_GLXJETAX_XMLP_PKG.c_jou_dr_or_crformula(:C_JOU_TAXABLE_ACC_DR, :C_JOU_TAXABLE_ACC_CR) C_JOU_DR_OR_CR, 
	GL_GLXJETAX_XMLP_PKG.c_jou_taxable_acc_sumformula(:C_JOU_TAXABLE_ACC_DR, :C_JOU_TAXABLE_ACC_CR) C_JOU_TAXABLE_ACC_SUM, 
	GL_GLXJETAX_XMLP_PKG.c_jou_accounted_sumformula(:C_JOU_ACCOUNTED_DR, :C_JOU_ACCOUNTED_CR) C_JOU_ACCOUNTED_SUM, 
	GL_GLXJETAX_XMLP_PKG.c_jou_gross_drformula(:C_JOU_TAXABLE_ACC_DR, :C_JOU_ACCOUNTED_DR) C_JOU_GROSS_DR, 
	GL_GLXJETAX_XMLP_PKG.c_jou_gross_crformula(:C_JOU_TAXABLE_ACC_CR, :C_JOU_ACCOUNTED_CR) C_JOU_GROSS_CR, 
	GL_GLXJETAX_XMLP_PKG.c_jou_grossformula(:C_JOU_GROSS_DR, :C_JOU_GROSS_CR) C_JOU_GROSS, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_taxable_acc_flexfield', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_TAXABLE_ACC_FLEXFIELD, 
	GL_GLXJETAX_XMLP_PKG.c_dr_or_crformula(gjl1.accounted_dr, gjl1.accounted_cr) C_DR_OR_CR, 
	GL_GLXJETAX_XMLP_PKG.c_taxable_acc_amountformula(gjl1.accounted_dr, gjl1.accounted_cr) C_TAXABLE_ACC_AMOUNT, 
	GL_GLXJETAX_XMLP_PKG.c_accounted_amountformula(gjl2.accounted_dr, gjl2.accounted_cr) C_ACCOUNTED_AMOUNT, 
	GL_GLXJETAX_XMLP_PKG.c_gross_drformula(gjl1.accounted_dr, gjl2.accounted_dr) C_GROSS_DR, 
	GL_GLXJETAX_XMLP_PKG.c_gross_crformula(gjl1.accounted_cr, gjl2.accounted_cr) C_GROSS_CR, 
	GL_GLXJETAX_XMLP_PKG.c_grossformula(:C_GROSS_DR, :C_GROSS_CR) C_GROSS, 
	GL_GLXJETAX_XMLP_PKG.c_new_groupformula(gjh.je_header_id, gjl1.tax_group_id, gjl2.accounted_dr, gjl2.accounted_cr) C_NEW_GROUP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acc_secure', 'SQLGL', 'GL#', gcc2.CHART_OF_ACCOUNTS_ID, NULL, gcc2.CODE_COMBINATION_ID, 'ALL', 'Y', 'SECURITY') ACC_SECURE,
	GL_GLXJETAX_XMLP_PKG.C_OLD_GROUPS_p C_OLD_GROUPS,
	GL_GLXJETAX_XMLP_PKG.C_OLD_HEADER_ID_p C_OLD_HEADER_ID,
	GL_GLXJETAX_XMLP_PKG.C_RUN_ACCOUNTED_DR_p C_RUN_ACCOUNTED_DR,
	GL_GLXJETAX_XMLP_PKG.C_RUN_ACCOUNTED_CR_p C_RUN_ACCOUNTED_CR
from
gl_lookups gl,
zx_account_rates rates,
zx_party_tax_profile ptp,
gl_code_combinations gcc2,
gl_je_lines gjl2,
gl_code_combinations gcc1,
gl_je_lines gjl1,
gl_je_batches gjb,
gl_je_headers gjh,
gl_period_statuses gps
where
gps.ledger_id= :P_LEDGER_ID
and gps.application_id =101
and trunc(gps.end_date) >= trunc(:P_START_DATE)
and trunc(gps.start_date) <= trunc(:P_END_DATE)
and gps.closing_status <> 'N'
and gjh.period_name = gps.period_name
and gjh.tax_status_code = 'T'
and gjh.ledger_id = :P_LEDGER_ID
and gjh.actual_flag = 'A'
and (gjh.display_alc_journal_flag is null or gjh.display_alc_journal_flag = 'Y')
and trunc(gjh.default_effective_date) between trunc(:P_START_DATE) and trunc(:P_END_DATE)
and gjb.je_batch_id = gjh.je_batch_id+0
and gjb.default_period_name = gjh.period_name
and gjb.actual_flag = 'A'
and &P_WHERE_POSTING_STATUS
and gjl1.je_header_id = gjh.je_header_id
and &P_WHERE_TAX
and gcc1.code_combination_id = gjl1.code_combination_id
and &P_WHERE_BAL_SEGMENT
and gjl2.je_header_id = gjl1.je_header_id
and gjl2.tax_group_id = gjl1.tax_group_id
and gjl2.tax_code_id is null
and gcc2.code_combination_id = gjl2.code_combination_id
and   rates.Ledger_id = :P_LEDGER_ID
AND   rates.content_owner_id = ptp.party_tax_profile_id
AND   ptp.party_id = gjb.org_id
AND   ptp.party_type_code = 'OU'
AND  rates.account_segment_value is NULL 
and   nvl(rates.tax_class, 'INPUT/OUTPUT')  = decode(gjl1.tax_type_code,  'I',
                   decode(rates.tax_status_code, 'STD_AR_INPUT', 'OUPUT', 'INPUT'),
                   'O', 'OUTPUT',  'INPUT/OUTPUT')
&P_WHERE_DAS
and gl.lookup_type = decode(gjl1.tax_type_code, NULL, 'TAX_TYPE', 'TAX_TYPE')
and gl.lookup_code = gjl1.tax_type_code
--order by gjl1.je_line_num
order by C_BAL_SEGMENT_PAD,C_TAX_TYPE_CODE,C_TAX_CODE,C_TAX_ACC_FLEXFIELD_PAD,C_DEFAULT_EFFECTIVE_DATE,
C_NAME,C_JE_HEADER_ID,C_TAX_REGISTRATION_NUMBER, gjl1.je_line_num
Parameter Name SQL text Validation
Chart of Accounts Id
 
Number
Data Access Set Id
 
Number
Posting Status
 
LOV Oracle
To Effective Date
 
Date
From Effective Date
 
Date
Tax Code
 
LOV Oracle
Tax Type
 
LOV Oracle
To Balancing Segment
 
From Balancing Segment
 
Ledger
 
LOV Oracle