JG General Ledger Journal Entry - draft

Description
Categories: BI Publisher
Application: Regional Localizations
Source: General Ledger Journal Entry Report (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZGLJRN_XML
DB package: JG_JGZZGLJRN_XMLP_PKG
	select
	inner.*,
	JG_JGZZGLJRN_XMLP_PKG.legal_entity_nameformula(inner.LEGAL_ENTITY_ID, :LEGAL_ENTITY_NAME) LEGAL_ENTITY_NAME,
	JG_JGZZGLJRN_XMLP_PKG.le_address_line_1formula(inner.LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_1) LE_ADDRESS_LINE_1,
	JG_JGZZGLJRN_XMLP_PKG.le_address_line_2formula(inner.LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_2) LE_ADDRESS_LINE_2,
	JG_JGZZGLJRN_XMLP_PKG.le_address_line_3formula(inner.LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_3) LE_ADDRESS_LINE_3,
	JG_JGZZGLJRN_XMLP_PKG.le_cityformula(inner.LEGAL_ENTITY_ID, :LE_CITY) LE_CITY,
	JG_JGZZGLJRN_XMLP_PKG.le_postal_codeformula(inner.LEGAL_ENTITY_ID, :LE_POSTAL_CODE) LE_POSTAL_CODE,
	JG_JGZZGLJRN_XMLP_PKG.le_registration_numberformula(inner.LEGAL_ENTITY_ID, :LE_REGISTRATION_NUMBER) LE_REGISTRATION_NUMBER,
	JG_JGZZGLJRN_XMLP_PKG.le_activity_codeformula(inner.LEGAL_ENTITY_ID, :LE_ACTIVITY_CODE) LE_ACTIVITY_CODE
	from 
	(SELECT
    &P_PERIOD_INX_HINT
    LGR.NAME LEDGER_NAME,
    LGR.LEDGER_ID LEDGER_ID,
    glc.acctg_environment_code ENVIRONMENT_CODE,
    lgr.configuration_id CONFIGURATION_ID,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('acct', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'N', 'VALUE') ACCT,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') ACCT_VALUE,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') BALANCING_SEGMENT_VALUE,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') COST_CENTER_VALUE,
    &INTERCOMPANY_SEGMENT_VALUE INTERCOMPANY_SEGMENT_VALUE,
    &MANAGEMENT_SEGMENT_VALUE MANAGEMENT_SEGMENT_VALUE,
    &SECONDARY_TRACK_SEGMENT_VALUE SECONDARY_TRACK_SEGMENT_VALUE,
    GLBATCH.NAME BATCH_NAME,
    GLBATCH.DESCRIPTION BATCH_DESC,
    GLBATCH.ACTUAL_FLAG ACTUAL_FLAG,
     DECODE(GLBATCH.POSTED_DATE, NULL, NULL, TO_CHAR(GLBATCH.POSTED_DATE,  'YYYY-MM-DD') || 'T' ||
     TO_CHAR(GLBATCH.POSTED_DATE,'HH24:MI:SS')) BATCH_POSTED_DATE,
     GLBATCH.STATUS BATCH_STATUS,
     LOOK.MEANING BATCH_POSTED_STATUS,
    GLH.NAME JRNL_NAME,
     GLH.DESCRIPTION JRNL_DESC,
    GLH.PERIOD_NAME PERIOD_NAME,
    GLH.CURRENCY_CODE JRNL_CURRENCY_CODE,
    TO_CHAR(GLH.DEFAULT_EFFECTIVE_DATE, 'YYYY-MM-DD') || 'T' ||
    TO_CHAR(GLH.DEFAULT_EFFECTIVE_DATE, 'HH24:MI:SS') JRNL_EFFECTIVE_DATE,
    NVL(SRC.USER_JE_SOURCE_NAME, '**********') JRNL_SOURCE,
    NVL(CAT.USER_JE_CATEGORY_NAME, '**********') JRNL_CATEGORY,
    FSV.HEADER_NAME JRNL_ACCT_SEQ_NAME,
    FSV.VERSION_NAME JRNL_ACCT_SEQ_VERSION,
    GLH.CLOSE_ACCT_SEQ_VALUE JRNL_ACCT_SEQ_NUM,
    DOCSEQ.NAME JRNL_DOC_SEQ_NAME,
    GLH.DOC_SEQUENCE_VALUE JRNL_DOC_SEQ_NUM,
    GLL.JE_LINE_NUM JRNL_LINE_NUM,
   GLL.ENTERED_DR		ENTERED_DR,
   GLL.ENTERED_CR		ENTERED_CR,
   GLL.ACCOUNTED_DR	ACCOUNTED_DR,
   GLL.ACCOUNTED_CR	ACCOUNTED_CR,
    GLL.DESCRIPTION JRNL_LINE_DESC,
    GLL.SUBLEDGER_DOC_SEQUENCE_ID  REFERENCE1,
	JG_JGZZGLJRN_XMLP_PKG.legal_entity_idformula(LGR.LEDGER_ID, glc.acctg_environment_code, lgr.configuration_id, cc.segment1) LEGAL_ENTITY_ID,
	&LEDGER_NAME_PARAM CF_LEDGER_NAME,
	/*JG_JGZZGLJRN_XMLP_PKG.legal_entity_nameformula(:LEGAL_ENTITY_ID, :LEGAL_ENTITY_NAME) LEGAL_ENTITY_NAME,
	JG_JGZZGLJRN_XMLP_PKG.le_address_line_1formula(:LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_1) LE_ADDRESS_LINE_1,
	JG_JGZZGLJRN_XMLP_PKG.le_address_line_2formula(:LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_2) LE_ADDRESS_LINE_2,
	JG_JGZZGLJRN_XMLP_PKG.le_address_line_3formula(:LEGAL_ENTITY_ID, :LE_ADDRESS_LINE_3) LE_ADDRESS_LINE_3,
	JG_JGZZGLJRN_XMLP_PKG.le_cityformula(:LEGAL_ENTITY_ID, :LE_CITY) LE_CITY,
	JG_JGZZGLJRN_XMLP_PKG.le_postal_codeformula(:LEGAL_ENTITY_ID, :LE_POSTAL_CODE) LE_POSTAL_CODE,
	JG_JGZZGLJRN_XMLP_PKG.le_registration_numberformula(:LEGAL_ENTITY_ID, :LE_REGISTRATION_NUMBER) LE_REGISTRATION_NUMBER,
	JG_JGZZGLJRN_XMLP_PKG.le_activity_codeformula(:LEGAL_ENTITY_ID, :LE_ACTIVITY_CODE) LE_ACTIVITY_CODE,*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'N', 'FULL_DESCRIPTION') ACCT_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_value_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION') ACCT_VALUE_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION') BALANCING_SEGMENT_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'FULL_DESCRIPTION') COST_CENTER_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('intercompany_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_INTERCOMPANY', 'N', 'DESCRIPTION') INTERCOMPANY_SEGMENT_DESC,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('management_segment_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_MANAGEMENT', 'N', 'DESCRIPTION') MANAGEMENT_SEGMENT_DESC,
	JG_JGZZGLJRN_XMLP_PKG.secondary_track_segment_descfo() SECONDARY_TRACK_SEGMENT_DESC
FROM   &P_FROM_CLAUSE
WHERE CAT.JE_CATEGORY_NAME = GLH.JE_CATEGORY
AND SRC.JE_SOURCE_NAME = GLH.JE_SOURCE
AND GLH.DOC_SEQUENCE_ID = DOCSEQ.DOC_SEQUENCE_ID (+)
AND GLH.CLOSE_ACCT_SEQ_VERSION_ID = FSV.SEQ_VERSION_ID (+)
AND GLBATCH.AVERAGE_JOURNAL_FLAG = 'N'
AND GLBATCH.JE_BATCH_ID = GLH.JE_BATCH_ID
&P_POSTING_STATUS
&P_HEADER_POSTING_STATUS
&CURR_WHERE_JRNL
&P_DATE_WHERE
&P_LEDGER_WHERE
AND GLH.LEDGER_ID = LGR.LEDGER_ID
AND lgr.configuration_id = glc.configuration_id
&P_BALANCE_TYPE_WHERE
AND GLL.PERIOD_NAME = GLP.PERIOD_NAME
AND GLP.APPLICATION_ID = 101
AND GLP.LEDGER_ID = LGR.LEDGER_ID
AND GLBATCH.STATUS= LOOK.LOOKUP_CODE
AND LOOK.LOOKUP_TYPE = 'BATCH_STATUS'
&PERIOD_WHERE
&DAS_WHERE
&SEG_SECURITY_WHERE
AND &TEMP_ACCT_WHERE
AND CC.CHART_OF_ACCOUNTS_ID = :P_COA_ID
&P_SOURCE_WHERE
&P_CATEGORY_WHERE
&P_DOC_SEQ_WHERE
order by JRNL_EFFECTIVE_DATE
) inner
Parameter Name SQL text Validation
Chart Of Accounts Id
 
LOV Oracle
Access Set Id
 
Number
User Parameter 5
 
User Parameter 4
 
User Parameter 3
 
User Parameter 2
 
User Parameter 1
 
First Page Number
 
Number
Page Number Format
 
LOV Oracle
Document Number To
 
Number
Document Number From
 
Number
Document Sequence Name
 
LOV Oracle
Balance Type
 
LOV Oracle
Batch Name
 
LOV Oracle
Journal Category
 
LOV Oracle
Journal Source
 
LOV Oracle
Posting Status
 
LOV Oracle
Currency
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Account To
 
Account From
 
End Date
 
Date
Start Date
 
Date
Ledger
 
LOV Oracle