GL Journals - Check

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Journals - Check (XML)
Short Name: GLRXCKJ_XML
DB package: GL_GLWACCTR_XMLP_PKG
SELECT    &P_HINT_CLAUSE
    	LGR.NAME		LEDGER_NAME,
	LGR.CURRENCY_CODE	LEDGER_CURRENCY,
                LGR.LEDGER_ID                       LEDGER_ID,
	GLB.JE_BATCH_ID		BATCH_ID,
    	GLB.NAME		BATCH_NAME,
	GLB.DESCRIPTION		BATCH_DESC,
	GLB.STATUS		BATCH_STATUS_CODE,
     	GLB.POSTED_DATE		BATCH_POSTED_DATE,
     	GLB.DEFAULT_PERIOD_NAME	BATCH_PERIOD_NAME,
     	GLB.DEFAULT_EFFECTIVE_DATE	BATCH_EFFECTIVE_DATE,
        	GLB.ACTUAL_FLAG			ACTUAL_FLAG,
	GLB.AVERAGE_JOURNAL_FLAG	AVERAGE_JOURNAL_FLAG,
	GLB.APPROVAL_STATUS_CODE	APPROVAL_STATUS_CODE,
	GLB.BUDGETARY_CONTROL_STATUS	BUD_CONTROL_STATUS,
	GLB.RUNNING_TOTAL_DR	BATCH_TOT_DR,
	GLB.RUNNING_TOTAL_CR	BATCH_TOT_CR,
	GLB.RUNNING_TOTAL_ACCOUNTED_DR	BATCH_TOT_ACC_DR,
	GLB.RUNNING_TOTAL_ACCOUNTED_CR	BATCH_TOT_ACC_CR,
	GLB.DATE_CREATED	BATCH_DATE_CREATED,
	GLH.JE_HEADER_ID		HDR_ID,
	GLH.NAME		HDR_NAME,
	GLH.DESCRIPTION		HDR_DESC,
	GLH.TAX_STATUS_CODE	TAX_STATUS_CODE,
	GLH.RUNNING_TOTAL_DR	HDR_TOT_DR,
	GLH.RUNNING_TOTAL_CR	HDR_TOT_CR,
	GLH.RUNNING_TOTAL_ACCOUNTED_DR	HDR_TOT_ACC_DR,
	GLH.RUNNING_TOTAL_ACCOUNTED_CR	HDR_TOT_ACC_CR,
	GLH.CURRENCY_CODE	HDR_CURR_CODE,
	GLH.CURRENCY_CONVERSION_DATE	CONVERSION_DATE,
	GLH.CURRENCY_CONVERSION_RATE	CONVERSION_RATE,
	DCT.USER_CONVERSION_TYPE	CONVERSION_TYPE,
	GLH.BUDGET_VERSION_ID	BUDGET_VERSION_ID,
	GLH.ENCUMBRANCE_TYPE_ID	ENCUMBRANCE_TYPE_ID,
	GLH.EXTERNAL_REFERENCE	HDR_EXTREFERENCE,
	--GLH.LAST_UPDATE_DATE	HDR_LAST_UPDATE_DATE,
	to_char(GLH.LAST_UPDATE_DATE,'DD-MON-YY')	HDR_LAST_UPDATE_DATE,
	GLH.DEFAULT_EFFECTIVE_DATE	HDR_EFF_DATE,
                GLH.DOC_SEQUENCE_ID          DOC_SEQ_ID,
	DOCSEQ.NAME		DOC_SEQ_NAME,
	GLH.DOC_SEQUENCE_VALUE	DOC_SEQ_VALUE,
	FU.USER_NAME		HDR_LAST_UPDATED_BY,
	GJS.USER_JE_SOURCE_NAME	HDR_SOURCE,
	GJC.USER_JE_CATEGORY_NAME	HDR_CATEGORY,
	GLL.JE_LINE_NUM		JE_LINE_NUM,
	GLL.TAX_CODE_ID		TAX_CODE_ID,
	GLL.TAX_TYPE_CODE	TAX_TYPE_CODE,
	GLL.DESCRIPTION		LINE_DESC,
	GLL.ENTERED_DR		ENTERED_DR,
	GLL.ENTERED_CR		ENTERED_CR,
	GLL.ACCOUNTED_DR	ACCOUNTED_DR,
	GLL.ACCOUNTED_CR	ACCOUNTED_CR,
	GLL.STAT_AMOUNT		STAT_AMOUNT,
	GLL.EFFECTIVE_DATE	EFFECTIVE_DATE,
	GLL.REFERENCE_1		REFERENCE1,
	GLL.REFERENCE_2		REFERENCE2,
	GLL.REFERENCE_3		REFERENCE3,
	GLL.REFERENCE_4		REFERENCE4,
	GLL.REFERENCE_5		REFERENCE5,
	GLL.REFERENCE_6		REFERENCE6,
	GLL.REFERENCE_7		REFERENCE7,
	GLL.REFERENCE_8		REFERENCE8,
	GLL.REFERENCE_9		REFERENCE9,
	GLL.REFERENCE_10		REFERENCE10,
	SUBDOCSEQ.NAME		SUB_DOC_SEQ_NAME,
                GLL.SUBLEDGER_DOC_SEQUENCE_ID SUB_DOC_SEQ_ID,
	GLL.SUBLEDGER_DOC_SEQUENCE_VALUE	SUB_DOC_SEQ_VAL,
	&SELECT_ACCOUNT	ACCOUNT,
	&SELECT_BAL_SEGMENT	 BAL_SEG_VAL,
	&SELECT_ACCT_SEGMENT	ACCT_SEG_VAL,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_seg_val', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')	COST_CTR_SEG_VAL,
	&SELECT_SECONDARY_SEGMENT	ADDITIONAL_SEGMENT_VALUE,
	GL_GLWACCTR_XMLP_PKG.batch_typeformula(GLB.ACTUAL_FLAG) BATCH_TYPE,
	GL_GLWACCTR_XMLP_PKG.batch_statusformula(GLB.STATUS) BATCH_STATUS,
	GL_GLWACCTR_XMLP_PKG.approval_statusformula(GLB.APPROVAL_STATUS_CODE) APPROVAL_STATUS,
	GL_GLWACCTR_XMLP_PKG.budget_ctrl_statusformula(GLB.BUDGETARY_CONTROL_STATUS) BUDGET_CTRL_STATUS,
	GL_GLWACCTR_XMLP_PKG.jrnl_typeformula(GLB.AVERAGE_JOURNAL_FLAG) JRNL_TYPE,
	GL_GLWACCTR_XMLP_PKG.budget_nameformula(GLH.BUDGET_VERSION_ID) BUDGET_NAME,
	GL_GLWACCTR_XMLP_PKG.encumbrance_typeformula(GLH.ENCUMBRANCE_TYPE_ID) ENCUMBRANCE_TYPE,
	GL_GLWACCTR_XMLP_PKG.tax_statusformula(GLH.TAX_STATUS_CODE) TAX_STATUS,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('account_dsp', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ACCOUNT_DSP,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') ACCOUNT_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_seg_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION') BAL_SEG_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_seg_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') ACCT_SEG_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_seg_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'DESCRIPTION') COST_CTR_SEG_DESC,
	&ADDITIONAL_SEGMENT_DESC ADDITIONAL_SEGMENT_DESC,
	GL_GLWACCTR_XMLP_PKG.tax_typeformula(GLL.TAX_TYPE_CODE) TAX_TYPE,
	GL_GLWACCTR_XMLP_PKG.tax_codeformula(GLL.TAX_CODE_ID, GLL.TAX_TYPE_CODE) TAX_CODE,
	GL_GLWACCTR_XMLP_PKG.contra_acct_valueformula(GLH.JE_HEADER_ID, GLL.SUBLEDGER_DOC_SEQUENCE_ID, GLL.SUBLEDGER_DOC_SEQUENCE_VALUE, GLL.ACCOUNTED_DR, GLL.ACCOUNTED_CR) CONTRA_ACCT_VALUE,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('contra_acct_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') CONTRA_ACCT_DESC,
	GL_GLWACCTR_XMLP_PKG.cf_line_balanceformula(cc.segment1, cc.segment3, cc.segment4, LGR.LEDGER_ID, GLB.STATUS) CF_LINE_BALANCE,
	GL_GLWACCTR_XMLP_PKG.cf_gl_balanceformula(cc.segment1, cc.segment3, cc.segment4, LGR.LEDGER_ID, GLB.STATUS) CF_GL_BALANCE
--	GL_GLWACCTR_XMLP_PKG.begin_balanceformula(GL_GLWACCTR_XMLP_PKG.cf_line_balanceformula(cc.segment1, cc.segment3, cc.segment4, LGR.LEDGER_ID, GLB.STATUS), GL_GLWACCTR_XMLP_PKG.cf_gl_balanceformula(cc.segment1, cc.segment3, cc.segment4, LGR.LEDGER_ID, GLB.STATUS)) BEGIN_BALANCE
FROM     &P_PERF_FROM
         &P_JOURNAL_FROM_CLAUSE
         &P_LEDGER_FROM
WHERE GLH.LEDGER_ID = LGR.LEDGER_ID
 &P_LEDGER_WHERE
 AND  GLH.JE_SOURCE = GJS.JE_SOURCE_NAME
 AND  GLH.JE_CATEGORY  =  GJC.JE_CATEGORY_NAME
 AND  GLH.LAST_UPDATED_BY = FU.USER_ID
 AND  GLB.JE_BATCH_ID = GLH.JE_BATCH_ID
 AND  GLH.DOC_SEQUENCE_ID = DOCSEQ.DOC_SEQUENCE_ID(+)
 AND  GLL.SUBLEDGER_DOC_SEQUENCE_ID = SUBDOCSEQ.DOC_SEQUENCE_ID(+)
 AND DCT.CONVERSION_TYPE (+) = GLH.CURRENCY_CONVERSION_TYPE
&P_POSTING_STATUS
&P_HEADER_POSTING_STATUS
&P_DAS_WHERE
&P_CURRENCY_WHERE
&P_ACTUAL_TYPE_WHERE
&P_BATCH_WHERE
&P_SOURCE_WHERE
&P_CATEGORY_WHERE
 and &P_BAL_WHERE
&P_DOC_ID_WHERE
&P_DOC_VAL_WHERE
&P_SUB_DOC_ID_WHERE
&P_SUB_DOC_VAL_WHERE
&P_NOT_ZERO_LINE_WHERE
&P_PERIOD_WHERE
&P_CONTRA_ACCOUNT_WHERE
&P_JOURNAL_DATE_WHERE
&P_JOURNAL_UPD_DATE_WHERE
&P_JOURNAL_LAST_UPD_WHERE
 and &P_AFF_WHERE
&P_AMOUNT_WHERE
&P_JOURNAL_WHERE_CLAUSE
Parameter Name SQL text Validation
Report Name
 
Pagebreak Segment
 
LOV Oracle
GL_SRS_COA_UNVALIDATED
 
Number
Actual Flag
 
LOV Oracle
Access Set Id
 
Number
Last Updated By
 
LOV Oracle
End Update Date
 
Date
Start Update Date
 
Date
Category
 
LOV Oracle
Batch Name
 
LOV Oracle
Journal Source
 
LOV Oracle
Currency
 
LOV Oracle
Posting Status
 
LOV Oracle
Ledger/Ledger Set
 
LOV Oracle