GL Journals - Day Book

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,
	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(:CF_LINE_BALANCE, :CF_GL_BALANCE) 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