ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4709E51A9E05362FB09050512 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 Y JG JGZZGLJRN_XML US JG General Ledger Journal Entry Report- Not Supported: Reserved For Future Use Imported from BI Publisher Description: General Ledger Journal Entry Report 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 BI Publisher &curr_where_jrnl &das_where &intercompany_segment_value &ledger_name_param &management_segment_value &p_balance_type_where &p_category_where &p_date_where &p_doc_seq_where &p_from_clause &p_header_posting_status &p_ledger_where &p_period_inx_hint &p_posting_status &p_source_where &period_where &secondary_track_segment_value &seg_security_where &temp_acct_where :acct_from_param :acct_seg_value_set_id :acct_to_param :acct_where :balance_type_param :batch_name_param :chart_of_accounts_id :curr_where_jrnl :currency_param :das_where :data_access_set_name :doc_seq_name_param :end_date_param :end_doc_seq_num_param :f_period_num :fp_start_date :from_lnsv :jrnl_category_param :jrnl_source_param :le_activity_code :le_address_line_1 :le_address_line_2 :le_address_line_3 :le_city :le_postal_code :le_registration_number :le_where :ledger_name_param :legal_entity_name :p_access_set_id :p_acct_from :p_acct_to :p_balance_type :p_balance_type_where :p_batch_name :p_batch_where :p_bud_enc_type :p_category :p_category_where :p_coa_id :p_conc_request_id :p_currency_code :p_date_where :p_doc_seq_name :p_doc_seq_where :p_end_date :p_end_doc_value :p_first_page_num :p_from_clause :p_header_posting_status :p_ledger_from :p_ledger_id :p_ledger_where :p_page_num_format :p_period_from :p_period_inx_hint :p_period_to :p_posting_status :p_source :p_source_where :p_start_date :p_start_doc_value :p_status :p_user_param_1 :p_user_param_2 :p_user_param_3 :p_user_param_4 :p_user_param_5 :page_num_format_param :page_num_start_param :period_from_param :period_to_param :period_where :posting_status_param :rep_execution_date :seg_security_where :select_account :select_acct_seg :select_bal_seg :select_begin_bal :select_cc_seg :select_cr :select_dr :select_ic_seg :select_le :select_mgt_seg :select_st_seg :start_date_param :start_doc_seq_num_param :t_period_num :temp_acct_where :temp_addr1 :temp_addr2 :temp_addr3 :temp_environment_code :temp_le_id :temp_le_name :temp_ledger_id :temp_legal_entity_id :temp_postal_code :temp_service_type :temp_tax_payer_id :temp_town_city :tp_end_date :user_param_1 :user_param_2 :user_param_3 :user_param_4 :user_param_5 1 -30 :p_access_set_id Number fnd_profile.value('GL_ACCESS_SET_ID') Y US Access Set Id Access Set Id 2 -20 :p_coa_id LOV Oracle JGZZ_SRS_COA_ID_BYSOB Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_access_sets where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') order by value,description SELECT chart_of_accounts_id FROM gl_access_sets WHERE access_set_id = fnd_profile.value('GL_ACCESS_SET_ID') Y US Chart Of Accounts Id Chart Of Accounts Id 3 10 :p_ledger_id LOV Oracle JGZZ_SRS_LEDGER_NAME Y select l.ledger_id id, l.name value, l.description description from gl_access_set_ledgers ac,gl_ledgers_public_v l where ac.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and l.ledger_id=ac.ledger_id order by name SELECT NAME FROM GL_LEDGERS_PUBLIC_V WHERE LEDGER_ID = GL_ACCESS_SET_SECURITY_PKG.GET_DEFAULT_LEDGER_ID(fnd_profile.value('GL_ACCESS_SET_ID'), 'R') US Ledger Ledger/Ledger Set 4 20 :p_start_date Date US Start Date Start Date 5 30 :p_end_date Date US End Date End Date 6 40 :p_acct_from Char Y US Account From Account From 7 50 :p_acct_to Char Y US Account To Account To 8 60 :p_period_from LOV Oracle JGZZ_SRS_GL_PERIOD_START Y select per.period_name id, per.period_name value, null description from gl_periods per,gl_access_sets gas where per.period_set_name=gas.period_set_name and per.period_type=gas.accounted_period_type and gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and exists (select 'Has Data' from gl_access_set_ledgers led2,gl_ledger_set_assignments ls,gl_period_statuses ps where led2.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and ls.ledger_set_id (+)=led2.ledger_id and ps.application_id=101 and ps.ledger_id=nvl(:$flex$.jgzz_srs_ledger_name:null,nvl(ls.ledger_id,led2.ledger_id)) and ps.closing_status in ('O','C','P') and ps.period_name=per.period_name) order by value,description Y US Period From Period From 9 70 :p_period_to LOV Oracle JGZZ_SRS_GL_PERIOD_END Y select per.period_name id, per.period_name value, null description from gl_periods per,gl_access_sets gas where per.period_set_name=gas.period_set_name and per.period_type=gas.accounted_period_type and gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and exists (select 'has data' from gl_access_set_ledgers led2,gl_ledger_set_assignments ls,gl_period_statuses ps where led2.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and ls.ledger_set_id (+)=led2.ledger_id and ps.application_id=101 and ps.ledger_id=nvl(:$flex$.jgzz_srs_ledger_name:null,nvl(ls.ledger_id,led2.ledger_id)) and ps.closing_status in ('O','C','P') and ps.period_name=per.period_name) and (per.period_year*10000+per.period_num)>= (select (per1.period_year*10000+per1.period_num) from gl_periods per1 where per1.period_set_name=per.period_set_name and per1.period_type=per.period_type and per1.period_name=:$flex$.jgzz_srs_gl_period_start) order by value,description Y US Period To Period To 10 80 :p_currency_code LOV Oracle JGZZ_SRS_CURRENCY_CODE Y select currency_code id, currency_code value, name description from fnd_currencies_vl where currency_flag='Y' order by currency_code US Currency Currency 11 90 :p_status LOV Oracle JGZZ_SRS_GL_POSTING_STATUS Y select lookup_code id, meaning value, description description from fnd_lookup_values where lookup_type='JGZZ_JRNL_REPORT_TYPE' and language=userenv('LANG') order by (meaning) Y US Posting Status Posting Status 12 100 :p_source LOV Oracle JGZZ_SRS_GL_JOURNALS_SOURCE Y select je_source_name id, user_je_source_name value, description description from gl_je_sources order by user_je_source_name US Journal Source Journal Source 13 110 :p_category LOV Oracle JGZZ_SRS_GL_JOURNALS_CATEGORY Y select je_category_name id, user_je_category_name value, description description from gl_je_categories order by user_je_category_name US Journal Category Journal Category 14 120 :p_batch_name LOV Oracle JGZZ_SRS_GL_BASE_BATCH Y select b.name id, b.name value, b.description description from gl_je_batches b where b.je_batch_id in (select max(b1.je_batch_id) from gl_je_batches b1 where b1.je_batch_id in (select h.je_batch_id from gl_je_headers h where h.ledger_id in (select nvl(ls.ledger_id,acc.ledger_id) from gl_access_set_ledgers acc,gl_ledger_set_assignments ls where acc.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and acc.ledger_id= nvl(:$flex$.jgzz_srs_ledger_name:null,acc.ledger_id) and ls.ledger_set_id (+)=decode(acc.ledger_id, :$flex$.jgzz_srs_ledger_name:null,acc.ledger_id,-1)) and h.period_name in (select gp3.period_name from gl_periods gp3,gl_access_sets gas where gp3.period_set_name=gas.period_set_name and gp3.period_type=gas.accounted_period_type and gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gp3.period_year*10000+gp3.period_num between (select per.period_year*10000+per.period_num from gl_periods per where per.period_set_name=gp3.period_set_name and per.period_type=gp3.period_type and per.period_name=:$flex$.jgzz_srs_gl_period_start) and (select per1.period_year*10000+per1.period_num from gl_periods per1 where per1.period_set_name=gp3.period_set_name and per1.period_type=gp3.period_type and per1.period_name=:$flex$.jgzz_srs_gl_period_end) and exists (select 'Has Data' from gl_access_set_ledgers led2,gl_ledger_set_assignments ls,gl_period_statuses ps where led2.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and ls.ledger_set_id (+)=led2.ledger_id and ps.application_id=101 and ps.ledger_id=nvl(:$flex$.jgzz_srs_ledger_name:null,nvl(ls.ledger_id,led2.ledger_id)) and ps.closing_status in ('O','C','P') and ps.period_name=gp3.period_name)) and h.je_source=nvl(:$flex$.jgzz_srs_gl_journals_source:null,h.je_source) and decode(h.currency_code,'STAT',1,0)= decode(:$flex$.jgzz_srs_currency_code:null,'STAT',1,0)) and b1.status=decode(:$flex$.jgzz_srs_gl_posting_status, 'P','P', 'U','U', 'E',decode(b1.status, 'P',null,'U',null,'I',null,'S',null,b1.status),null) group by b1.name) order by value,description US Batch Name Batch Name 15 130 :p_balance_type LOV Oracle JGZZ_SRS_BALANCE_TYPE_GLJRN Y select lookup_code id, meaning value, description description from gl_lookups where lookup_type='BATCH_TYPE' order by upper(meaning) SELECT MEANING FROM GL_LOOKUPS WHERE LOOKUP_TYPE = 'BATCH_TYPE' AND LOOKUP_CODE = 'A' US Balance Type Balance Type 16 140 :p_doc_seq_name LOV Oracle JGZZ_SRS_GL_DOC_SEQ_NAME Y select doc_sequence_id id, name value, null description from fnd_document_sequences where doc_sequence_id in (select distinct doc_sequence_id from fnd_doc_sequence_assignments where application_id=101) and type in ('A','G') order by name US Document Sequence Name Document Sequence Name 17 150 :p_start_doc_value Number US Document Number From Document Number From 18 160 :p_end_doc_value Number US Document Number To Document Number To 19 170 :p_page_num_format LOV Oracle JGZZ_SRS_PAGE_NUM_FORMAT Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='JGZZ_PAGE_NUM_FORMAT' order by value,description SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'JGZZ_PAGE_NUM_FORMAT' AND LOOKUP_CODE = 'PAGE_N_OF_M' Y US Page Number Format Page Number Format 20 180 :p_first_page_num Number US First Page Number First Page Number 21 190 :p_user_param_1 Char US User Parameter 1 User Parameter 1 22 200 :p_user_param_2 Char US User Parameter 2 User Parameter 2 23 210 :p_user_param_3 Char US User Parameter 3 User Parameter 3 24 220 :p_user_param_4 Char US User Parameter 4 User Parameter 4 25 230 :p_user_param_5 Char US User Parameter 5 User Parameter 5