GL Journals - Tax

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