ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN EAE0323A88740D49E0530100007F2980Y select -- ledger x.ledger_name, x.ledger_currency, x.ledger_id, -- accounts x.account, x.account_desc, x.bal_seg_val, x.bal_seg_desc, x.acct_seg_val, x.acct_seg_desc, x.cost_ctr_seg_val, x.cost_ctr_seg_desc, x.additional_segment_name, x.additional_segment_value, x.additional_segment_desc, -- start balances nvl( (select distinct glb.begin_balance_dr from gl_balances glb where glb.actual_flag = 'A' and glb.currency_code = x.ledger_currency and glb.code_combination_id = x.line_ccid and glb.ledger_id = x.ledger_id and glb.period_name = :p_start_period_name_h ),0) acc_dr_begin, nvl( (select distinct glb.begin_balance_cr from gl_balances glb where glb.actual_flag = 'A' and glb.currency_code = x.ledger_currency and glb.code_combination_id = x.line_ccid and glb.ledger_id = x.ledger_id and glb.period_name = :p_start_period_name_h ),0) acc_cr_begin, -- start period movement prior to p_start_date nvl( (select sum(gll.accounted_dr) from gl_je_lines gll where gll.effective_date >= :p_period_start_date_h and gll.effective_date < :p_start_date_h and gll.code_combination_id = x.line_ccid and gll.ledger_id = x.ledger_id and gll.status = 'P' ),0) acc_dr_period, nvl( (select sum(gll.accounted_cr) from gl_je_lines gll where gll.effective_date >= :p_period_start_date_h and gll.effective_date < :p_start_date_h and gll.code_combination_id = x.line_ccid and gll.ledger_id = x.ledger_id and gll.status = 'P' ),0) acc_cr_period, -- contra account x.contra_acct_value, case when :p_report_name = 'CONTRA' then case x.contra_acct_value when 'MULTIPLE' then 'MULTIPLE' when 'NO CONTRA ACCOUNT' then 'NO CONTRA ACCOUNT' else (select ffvv.description from fnd_segment_attribute_values fsav, fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_ACCOUNT' and fsav.id_flex_num = :p_coa_id and fsav.attribute_value = 'Y' and fifsgv.application_id=fsav.application_id and fifsgv.id_flex_code=fsav.id_flex_code and fifsgv.id_flex_num=fsav.id_flex_num and fifsgv.application_column_name = fsav.application_column_name and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.flex_value = x.contra_acct_value ) end else null end contra_acct_value_desc, case when :p_report_name = 'CONTRA' then gl_xml_journal_rpt_pkg.net_begin_balance (p_acct_seg_where => '&p_aff_from_where2 &p_aff_to_where2', p_status => x.batch_status_code, p_start_date => :p_start_date_h, p_currency => :p_currency, p_led_id => x.ledger_id, p_bal_seg_name => :p_gl_bal_segment, p_bal_seg_val => x.bal_seg_val, p_acct_seg_name => :p_gl_acc_segment, p_acct_seg_val => x.acct_seg_val, p_sec_seg_name => :p_gl_sec_segment, p_sec_seg_val => x.additional_segment_value) else to_number(null) end cf_contra_net_begin_balance, case when :p_report_name = 'CONTRA' then gl_xml_journal_rpt_pkg.net_line_balance (p_acct_seg_where => '&p_aff_from_where2 &p_aff_to_where2', p_status => x.batch_status_code, p_start_date => :p_start_date_h, p_currency => :p_currency, p_led_id => x.ledger_id, p_bal_seg_name => :p_gl_bal_segment, p_bal_seg_val => x.bal_seg_val, p_acct_seg_name => :p_gl_acc_segment, p_acct_seg_val => x.acct_seg_val, p_sec_seg_name => :p_gl_sec_segment, p_sec_seg_val => x.additional_segment_value) else to_number(null) end cf_contra_net_line_balance, -- journal batch x.batch_id, x.batch_name, x.batch_desc, x.batch_status_code, x.batch_status, x.batch_posted_date, x.batch_period_name, x.batch_effective_date, x.actual_flag, x.batch_type, x.average_journal_flag, x.jrnl_type, x.approval_status_code, x.approval_status, x.bud_control_status, x.budget_ctl_status, x.batch_tot_dr, x.batch_tot_cr, x.batch_tot_acc_dr, x.batch_tot_acc_cr, x.batch_date_created, -- journnal header x.hdr_id, x.hdr_name, x.hdr_desc, x.tax_status_code, x.tax_status, x.hdr_tot_dr, x.hdr_tot_cr, x.hdr_tot_acc_dr, x.hdr_tot_acc_cr, x.hdr_curr_code, x.conversion_date, x.conversion_rate, x.conversion_type, x.budget_version_id, (select gbv.budget_name from gl_budget_versions gbv where gbv.budget_version_id = x.budget_version_id) budget_name, x.encumbrance_type_id, (select get.encumbrance_type from gl_encumbrance_types get where get.encumbrance_type_id = x.encumbrance_type_id) encumbrance_type, x.hdr_extreference, x.hdr_last_update_date, x.hdr_eff_date, x.doc_seq_id, x.doc_seq_name, x.doc_seq_value, x.posting_seq_value, x.close_seq_value, nvl2(x.sl_event_id,x.xlq_reporting_seq,x.close_seq_value) cf_reporting_seq, x.hdr_last_updated_by, x.hdr_source, x.hdr_category2, x.hdr_category, nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) cf_category, -- journal line x.je_line_num, x.tax_code_id, x.tax_type_code, (select gtcv.tax_code from gl_tax_codes_v gtcv where to_char(gtcv.tax_code_id)=x.tax_code_id and gtcv.tax_type_code = x.tax_type_code) tax_code, x.tax_type, x.line_desc, x.entered_dr, x.entered_cr, x.accounted_dr, x.accounted_cr, x.stat_amount, x.effective_date, x.reference1, x.reference2, x.reference3, x.reference4, x.reference5, x.reference6, x.reference7, x.reference8, x.reference9, x.reference10, x.line_proj_code2, case when x.sl_event_id is not null and x.hdr_source = 'Receivables' then coalesce ( (select distinct ractla.attribute1 from ra_cust_trx_line_gl_dist_all ractl, ra_customer_trx_all ract, ra_customer_trx_lines_all ractla, ra_batch_sources_all rabs where rabs.batch_source_id = ract.batch_source_id and ract.customer_trx_id = ractla.customer_trx_id and ractla.customer_trx_id = ractl.customer_trx_id and ractla.customer_trx_line_id = ractl.customer_trx_line_id and ractl.event_id = x.sl_event_id and ractl.code_combination_id = x.line_ccid and rownum=1 ), (select distinct ractla.attribute1 from ar_receivable_applications_all araa, ra_customer_trx_all ract, ra_customer_trx_lines_all ractla, ra_batch_sources_all rabs where rabs.batch_source_id = ract.batch_source_id and ract.customer_trx_id = ractla.customer_trx_id and ractla.customer_trx_id = araa.customer_trx_id and araa.event_id = x.sl_event_id and rownum=1 ), x.line_proj_code2 ) when x.sl_event_id is not null and x.hdr_source = 'Payables' then case when x.hdr_category = 'Payments' then nvl( (select distinct apila.attribute1 from ap_invoices_all apia, ap_invoice_lines_all apila, ap_invoice_payments_all aip where aip.accounting_event_id = x.sl_event_id and aip.payment_num = x.event_number and apia.invoice_id = aip.invoice_id and apila.invoice_id = aip.invoice_id and rownum=1 ),x.line_proj_code2) else nvl( (select distinct apila.attribute1 from ap_invoices_all apia, ap_invoice_lines_all apila, ap_invoice_distributions_all apida, xla_distribution_links xdl where xdl.application_id = 200 and xdl.ae_header_id = x.ae_header_id and xdl.ae_line_num = x.sl_line_num and xdl.source_distribution_type = 'AP_INV_DIST' and xdl.source_distribution_id_num_1 = apida.invoice_distribution_id and apia.invoice_id = apida.invoice_id and apila.invoice_id = apida.invoice_id and apila.line_number = apida.invoice_line_number and rownum=1 ),x.line_proj_code2) end else x.line_proj_code2 end line_proj_code, x.line_fund_code, (select ctsc.transaction_sub_type_name from ce_trxns_subtype_codes ctsc where ctsc.trxn_subtype_code_id = x.line_fund_code) cf_fund_code, x.line_reference, x.line_ccid, x.jgzz_recon_ref, x.sub_doc_seq_name, x.sub_doc_seq_id, x.sub_doc_seq_val, -- subledger x.transaction_no, x.sl_doc_seq_no, x.xlq_reporting_seq, x.party_type_code, x.party_id, x.party_site_id, case when x.sl_event_id is not null then case when x.hdr_source = 'Assets' and x.party_type_code = 'C' then to_char(x.source_id_int_1) when x.hdr_source = 'Receivables' and x.party_type_code = 'C' then (select a.account_number||'-'||c.location from hz_cust_accounts a, hz_cust_acct_sites_all b, hz_cust_site_uses_all c where a.cust_account_id = b.cust_account_id and b.cust_acct_site_id = c.cust_acct_site_id and c.site_use_id = x.party_site_id ) when x.hdr_source = 'Payables' and x.party_type_code = 'S' then (select pv.segment1 from po_vendors pv where pv.vendor_id = x.party_id) when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS' then (select substrb(pv.segment1,1,100) from cst_write_offs cwos, po_distributions_all poda, po_vendors pv where pv.vendor_id=cwos.vendor_id and poda.po_distribution_id = cwos.po_distribution_id and cwos.write_off_id = x.transaction_no ) when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' then (select substrb(pv.segment1,1,100) from rcv_transactions rt, rcv_shipment_headers rsh, po_vendors pv where pv.vendor_id=rt.vendor_id and rsh.shipment_header_id = rt.shipment_header_id and rt.transaction_id = x.transaction_no ) else null end else null end party_no, -- case when x.sl_event_id is not null then case when x.hdr_source = 'Assets' then (select substrb(description,1,100) from fa_additions ad where ad.asset_id = x.source_id_int_1) when x.hdr_source = 'Receivables' then (select substrb(nvl(party.party_name,''),1,100) from hz_parties party, hz_cust_accounts acnt, hz_cust_acct_sites_all site, hz_cust_site_uses_all uses, hz_party_sites ps, hz_locations loc where party.party_id = acnt.party_id and acnt.cust_account_id = site.cust_account_id and site.cust_acct_site_id = uses.cust_acct_site_id and ps.party_site_id=site.party_site_id and loc.location_id=ps.location_id and uses.site_use_id = x.party_site_id ) when x.hdr_source = 'Payables' and x.party_type_code = 'S' then nvl ((select decode(ai.source,'INDIA TDS',pv.vendor_name,ai.attribute1) from ap_invoices_all ai, po_vendors pv where ai.invoice_id = x.source_id_int_1 and ai.vendor_id = pv.vendor_id ), (select substrb(pv.vendor_name,1,100) from po_vendors pv where pv.vendor_id = x.party_id) ) when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS' then (select pv.vendor_name from cst_write_offs cwos, po_distributions_all poda, po_vendors pv where pv.vendor_id=cwos.vendor_id and poda.po_distribution_id = cwos.po_distribution_id and cwos.write_off_id = x.transaction_no ) when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' then (select pv.vendor_name from rcv_transactions rt, rcv_shipment_headers rsh, po_vendors pv where pv.vendor_id=rt.vendor_id and rsh.shipment_header_id = rt.shipment_header_id and rt.transaction_id = x.transaction_no ) else null end else null end party_name, -- case when nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) in ('Payments','Receipts') then x.hdr_source when nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' then case when x.sl_event_id is not null and x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS' then (select pha.attribute6 from cst_write_offs cwos, po_distributions_all poda, po_headers_all pha where pha.po_header_id=poda.po_header_id and poda.po_distribution_id = cwos.po_distribution_id and cwos.write_off_id = x.transaction_no ) when x.sl_event_id is not null and x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' then (select pha.attribute6 from rcv_transactions rt, rcv_shipment_headers rsh, po_headers_all pha where pha.po_header_id=rt.po_header_id and rsh.shipment_header_id = rt.shipment_header_id and rt.transaction_id = x.transaction_no ) else null end when x.sl_event_id is not null and x.hdr_source = 'Receivables' then coalesce ( (select distinct rabs.name from ra_cust_trx_line_gl_dist_all ractl, ra_customer_trx_all ract, ra_customer_trx_lines_all ractla, ra_batch_sources_all rabs where rabs.batch_source_id = ract.batch_source_id and ract.customer_trx_id = ractla.customer_trx_id and ractla.customer_trx_id = ractl.customer_trx_id and ractla.customer_trx_line_id = ractl.customer_trx_line_id and ractl.event_id = x.sl_event_id and ractl.code_combination_id = x.line_ccid and rownum=1 ), (select distinct rabs.name from ar_receivable_applications_all araa, ra_customer_trx_all ract, ra_customer_trx_lines_all ractla, ra_batch_sources_all rabs where rabs.batch_source_id = ract.batch_source_id and ract.customer_trx_id = ractla.customer_trx_id and ractla.customer_trx_id = araa.customer_trx_id and araa.event_id = x.sl_event_id and rownum=1 ), x.hdr_source ) when x.sl_event_id is not null and x.hdr_source = 'Payables' then case when x.hdr_category = 'Payments' then nvl( (select distinct apia.source from ap_invoices_all apia, ap_invoice_lines_all apila, ap_invoice_payments_all aip where aip.accounting_event_id = x.sl_event_id and aip.payment_num = x.event_number and apia.invoice_id = aip.invoice_id and apila.invoice_id = aip.invoice_id and rownum=1 ),x.hdr_source) else nvl( (select distinct apia.source from ap_invoices_all apia, ap_invoice_lines_all apila, ap_invoice_distributions_all apida, xla_distribution_links xdl where xdl.application_id = 200 and xdl.ae_header_id = x.ae_header_id and xdl.ae_line_num = x.sl_line_num and xdl.source_distribution_type = 'AP_INV_DIST' and xdl.source_distribution_id_num_1 = apida.invoice_distribution_id and apia.invoice_id = apida.invoice_id and apila.invoice_id = apida.invoice_id and apila.line_number = apida.invoice_line_number and rownum=1 ),x.hdr_source) end else x.hdr_source end sl_source, -- case when x.hdr_source='Assets' and x.source_id_int_3 is not null then to_char(x.source_id_int_3) when x.hdr_source='Payables' and x.transaction_no is not null then case when x.hdr_category='Payments' then (select distinct ab.batch_name from ap_batches_all ab, ap_invoices_all ai, ap_invoice_payments_all aip where ai.batch_id = ab.batch_id and ai.invoice_id = aip.invoice_id and aip.check_id = x.source_id_int_1 and rownum=1 ) else (select ab.batch_name from ap_batches_all ab, ap_invoices_all ai where ai.batch_id = ab.batch_id and ai.invoice_id = x.source_id_int_1 and rownum=1 ) end when x.hdr_source='Receivables' and x.transaction_no is not null then case when x.hdr_category='Receipts' then (select distinct nvl(bat.name ,'') from ar_cash_receipts_all cr, ar_cash_receipt_history_all crh, ar_batches_all bat where bat.batch_id = crh.batch_id and crh.cash_receipt_id = x.source_id_int_1 and rownum=1 ) else (select nvl(rab.name ,'') from ra_batches_all rab, ra_customer_trx_all ct where rab.batch_id = ct.batch_id and ct.customer_trx_id = x.source_id_int_1 and rownum=1 ) end else null end sl_batch_no, -- nvl (case when x.sl_event_id is not null then case when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS' then (select to_char(cwos.write_off_id) from cst_write_offs cwos, po_distributions_all poda, po_vendors pv where pv.vendor_id=cwos.vendor_id and poda.po_distribution_id = cwos.po_distribution_id and cwos.write_off_id = x.transaction_no ) when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' then (select rsh.receipt_num from rcv_transactions rt, rcv_shipment_headers rsh, po_vendors pv where pv.vendor_id=rt.vendor_id and rsh.shipment_header_id = rt.shipment_header_id and rt.transaction_id = x.transaction_no ) else null end else null end, x.transaction_no ) cf_receipt_num, -- case when x.sl_event_id is not null then case when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS' then (select to_char(poda.po_header_id) from cst_write_offs cwos, po_distributions_all poda, po_vendors pv where pv.vendor_id=cwos.vendor_id and poda.po_distribution_id = cwos.po_distribution_id and cwos.write_off_id = x.transaction_no ) when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' then (select to_char(rt.po_header_id) from rcv_transactions rt, rcv_shipment_headers rsh, po_vendors pv where pv.vendor_id=rt.vendor_id and rsh.shipment_header_id = rt.shipment_header_id and rt.transaction_id = x.transaction_no ) else null end else null end cmgt_po_header_id, -- case when (x.hdr_source ='Receivables') and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) not in ('Misc Receipts','Receipts','Adjustment') and x.transaction_no is not null then (select distinct rcta.purchase_order from ra_customer_trx_all rcta, org_organization_definitions ood where ood.set_of_books_id = x.ledger_id and rcta.org_id= ood.organization_id and rcta.trx_number=x.transaction_no and rownum=1 ) end ar_po_num, -- x.ae_header_id, x.entity_code, x.source_id_int_1, x.source_id_int_3, x.sl_event_id , x.event_number, x.sl_line_num, x.xla_description, x.xla_gl_sl_link_id, -- '*' last_col from ( select &p_hint_clause lgr.name ledger_name, lgr.currency_code ledger_currency, lgr.ledger_id ledger_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') account, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', '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('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') bal_seg_val, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', '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_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') acct_seg_val, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', '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('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_ctr_seg_val, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION') cost_ctr_seg_desc, :p_sec_seg_name additional_segment_name, &p_select_secondary_segment &p_null_secondary_segment additional_segment_value, &p_select_secondary_seg_desc &p_null_secondary_segment additional_segment_desc, case when :p_report_name = 'CONTRA' and (nvl(nvl(l.accounted_dr,gll.accounted_dr),0) != 0 or nvl(nvl(l.accounted_cr,gll.accounted_cr),0) !=0) then gl_xml_journal_rpt_pkg.Get_Contra_Account(:p_gl_acc_segment,glh.je_header_id,gll.subledger_doc_sequence_id,gll.subledger_doc_sequence_value,nvl(l.accounted_dr,gll.accounted_dr),nvl(l.accounted_cr,gll.accounted_cr)) else null end contra_acct_value, -- glb.je_batch_id batch_id, glb.name batch_name, glb.description batch_desc, glb.status batch_status_code, xxen_util.meaning(glb.status,'MJE_BATCH_STATUS',101) batch_status, 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, xxen_util.description(glb.actual_flag,'BATCH_TYPE',101) batch_type, glb.average_journal_flag average_journal_flag, xxen_util.meaning(glb.average_journal_flag,'AB_JOURNAL_TYPE',101) jrnl_type, glb.approval_status_code approval_status_code, xxen_util.meaning(glb.approval_status_code,'JE_BATCH_APPROVAL_STATUS',101) approval_status, glb.budgetary_control_status bud_control_status, xxen_util.meaning(glb.budgetary_control_status,'JE_BATCH_BC_STATUS',101) budget_ctl_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, xxen_util.meaning(glh.tax_status_code,'TAX_STATUS',101) tax_status, 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, nvl(l.currency_conversion_date,glh.currency_conversion_date) conversion_date , to_char( nvl(l.currency_conversion_rate,decode((nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)),0,0,(nvl(gll.accounted_dr,0)+nvl(gll.accounted_cr,0))/(nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)) )) ,'999G999D9999') 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, glh.posting_acct_seq_value posting_seq_value, nvl(h.close_acct_seq_value,glh.close_acct_seq_value) close_seq_value, fu.user_name hdr_last_updated_by, gjs.user_je_source_name hdr_source, gjc.user_je_category_name hdr_category2, decode(gjs.user_je_source_name ,'Assets',h.je_category_name,'Payables',h.je_category_name,'Receivables',h.je_category_name,'Cost Management',h.je_category_name,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, xxen_util.meaning(gll.tax_type_code,'TAX_TYPE',101) tax_type, decode(e.event_id,null,gll.description,l.description) line_desc, nvl(l.entered_dr,gll.entered_dr) entered_dr, nvl(l.entered_cr,gll.entered_cr) entered_cr, nvl(l.accounted_dr,gll.accounted_dr) accounted_dr, nvl(l.accounted_cr,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, gll.attribute11 line_proj_code2, gll.attribute12 line_fund_code, gll.attribute13 line_reference, gll.code_combination_id line_ccid, decode(l.gl_sl_link_id,'',rec.jgzz_recon_ref,l.jgzz_recon_ref) jgzz_recon_ref, subdocseq.name sub_doc_seq_name, gll.subledger_doc_sequence_id sub_doc_seq_id, gll.subledger_doc_sequence_value sub_doc_seq_val, nvl(te.transaction_number,to_char(' ')) transaction_no, h.doc_sequence_value sl_doc_seq_no, h.close_acct_seq_value xlq_reporting_seq, l.ae_header_id, te.entity_code, te.source_id_int_1 source_id_int_1, te.source_id_int_3 source_id_int_3, e.event_id sl_event_id , e.event_number, l.ae_line_num sl_line_num, l.description xla_description, l.gl_sl_link_id xla_gl_sl_link_id, l.party_type_code, l.party_site_id party_site_id , l.party_id party_id from gl_je_lines_recon rec, gl_system_usages use1, xla.xla_ae_headers h , xla.xla_ae_lines l , xla.xla_events e , xla.xla_transaction_entities te , xla_gl_ledgers_v gl , xle_entity_profiles le , fnd_user ue , fnd_application_vl app , xla.xla_event_types_tl et , xla.xla_event_classes_tl ec , xla_lookups lk5 , xla_lookups lk7 , gl_import_references ir, gl_system_usages use, &p_perf_from &p_journal_from_clause &p_ledger_from where 1=1 and 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 and gll.status = 'P' and rec.je_header_id(+) = gll.je_header_id and rec.je_line_num(+) = gll.je_line_num and rec.ledger_id(+) = :p_ledger_id and gl.ledger_id (+)= h.ledger_id and le.legal_entity_id (+)= te.legal_entity_id and ue.user_id (+)= e.created_by and ec.application_id(+) = et.application_id and ec.event_class_code (+)= et.event_class_code and ec.language (+)= userenv('LANG') and et.event_type_code (+)= h.event_type_code and et.language (+)= userenv('LANG') and et.application_id (+)= h.application_id and app.application_id (+)= h.application_id and te.entity_id (+)= h.entity_id and e.event_id (+)= h.event_id and h.ae_header_id (+)= l.ae_header_id and lk5.lookup_code = nvl(h.funds_status_code, 'REQUIRED') and lk5.lookup_type = 'XLA_FUNDS_STATUS' and lk7.lookup_code(+) = l.accounting_class_code and lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS' and ir.gl_sl_link_id = l.gl_sl_link_id(+) and ir.gl_sl_link_table = l.gl_sl_link_table(+) and ir.je_header_id(+) = gll.je_header_id and ir.je_line_num (+)= gll.je_line_num and gjs.je_source_name in('Assets','Inventory','Payables','Purchasing','Receivables','Revenue','Cost Management') &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 &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 &p_aff_from_where &p_aff_to_where &p_amount_where &p_journal_where_clause union all select &p_hint_clause lgr.name ledger_name, lgr.currency_code ledger_currency, lgr.ledger_id ledger_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') account, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', '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('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') bal_seg_val, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', '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_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') acct_seg_val, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', '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('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_ctr_seg_val, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION') cost_ctr_seg_desc, :p_sec_seg_name additional_segment_name, &p_select_secondary_segment &p_null_secondary_segment additional_segment_value, &p_select_secondary_seg_desc &p_null_secondary_segment additional_segment_desc, case when :p_report_name = 'CONTRA' then gl_xml_journal_rpt_pkg.Get_Contra_Account(:p_gl_acc_segment,glh.je_header_id,gll.subledger_doc_sequence_id,gll.subledger_doc_sequence_value,gll.accounted_dr,gll.accounted_cr) else null end contra_acct_value, -- glb.je_batch_id batch_id, glb.name batch_name, glb.description batch_desc, glb.status batch_status_code, xxen_util.meaning(glb.status,'MJE_BATCH_STATUS',101) batch_status, 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, xxen_util.description(glb.actual_flag,'BATCH_TYPE',101) batch_type, glb.average_journal_flag average_journal_flag, xxen_util.meaning(glb.average_journal_flag,'AB_JOURNAL_TYPE',101) jrnl_type, glb.approval_status_code approval_status_code, xxen_util.meaning(glb.approval_status_code,'JE_BATCH_APPROVAL_STATUS',101) approval_status, glb.budgetary_control_status bud_control_status, xxen_util.meaning(glb.budgetary_control_status,'JE_BATCH_BC_STATUS',101) budget_ctl_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, xxen_util.meaning(glh.tax_status_code,'TAX_STATUS',101) tax_status, 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 , to_char(decode((nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)),0,0,(nvl(gll.accounted_dr,0)+nvl(gll.accounted_cr,0))/(nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)) ) ,'999G999D9999') 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, glh.posting_acct_seq_value posting_seq_value, glh.close_acct_seq_value close_seq_value, fu.user_name hdr_last_updated_by, gjs.user_je_source_name hdr_source, gjc.user_je_category_name hdr_category2, 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, xxen_util.meaning(gll.tax_type_code,'TAX_TYPE',101) tax_type, 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, gll.attribute11 line_proj_code2, gll.attribute12 line_fund_code, gll.attribute13 line_reference, gll.code_combination_id line_ccid, rec.jgzz_recon_ref, subdocseq.name sub_doc_seq_name, gll.subledger_doc_sequence_id sub_doc_seq_id, gll.subledger_doc_sequence_value sub_doc_seq_val, null transaction_no, null sl_doc_seq_no, null xlq_reporting_seq, null ae_header_id, null entity_code, null source_id_int_1, null source_id_int_3, null sl_event_id , null event_number, null sl_line_num, null xla_description, null xla_gl_sl_link_id, null party_type_code, null party_site_id , null party_id from gl_je_lines_recon rec, &p_perf_from &p_journal_from_clause &p_ledger_from where 1=1 and glh.ledger_id = lgr.ledger_id &p_ledger_where and rec.je_header_id(+) = gll.je_header_id and rec.je_line_num(+) = gll.je_line_num and rec.ledger_id(+) = :p_ledger_id 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 and gll.status = 'P' and gjs.je_source_name not in('Assets','Inventory','Payables','Purchasing','Receivables','Revenue','Cost Management') &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 &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 &p_aff_from_where &p_aff_to_where &p_amount_where &p_journal_where_clause ) x where :p_report_name = :p_report_name and :p_access_set_id = :p_access_set_id and :p_ledger = :p_ledger and :p_ledger_id = :p_ledger_id and :p_ledger_type = :p_ledger_type and :p_coa_id = :p_coa_id and nvl(:p_status,'?') = nvl(:p_status,'?') and nvl(:p_currency,'?') = nvl(:p_currency,'?') and nvl(:p_select_basis,'?') = nvl(:p_select_basis,'?') and nvl(:p_use_date_col,'?') = nvl(:p_use_date_col,'?') and nvl(:p_start_date,sysdate) = nvl(:p_start_date,sysdate) and nvl(:p_end_date,sysdate) = nvl(:p_end_date,sysdate) and nvl(:p_amount_from,0) = nvl(:p_amount_from,0) and nvl(:p_amount_to,0) = nvl(:p_amount_to,0) and nvl(:p_amt_flag,'?') = nvl(:p_amt_flag,'?') and nvl(:p_aff_from,'?') = nvl(:p_aff_from,'?') and nvl(:p_aff_to,'?') = nvl(:p_aff_to,'?') and nvl(:p_contra_acct,'?') = nvl(:p_contra_acct,'?') order by account, effective_date, doc_seq_value General (:Period_Name is not null and :Start_Date is null and :End_Date is null) or (:Period_Name is null and :Start_Date is not null and :End_Date is not null) AR COPY OF: GL Journals تقرير دفعات دفتر اليومية ودفاتر اليومية ، بما في ذلك المبالغ والمرفقات الرجاء إدخال المزيد من المعلمات D COPY OF: GL Journals GL-Batches und Journale Bericht, einschließlich Beträge und Anhänge Bitte weitere Parameter eingeben F COPY OF: GL Journals Rapport sur les lots de GL et les journaux, y compris les montants et les pièces jointes Veuillez saisir d'autres paramètres I COPY OF: GL Journals Report dei lotti e dei giornali GL, compresi gli importi e gli allegati Si prega di inserire altri parametri US Acer: XXGLR009 Acer: XXGLR009 Please specify either a period or Start and End Date ZHS COPY OF: GL Journals GL批次和日记账报告，包括金额和附件。 请输入更多参数 &p_actual_type_where &p_aff_from_where &p_aff_from_where2 &p_aff_to_where &p_aff_to_where2 &p_amount_where &p_bal_where &p_batch_where &p_category_where &p_contra_account_where &p_currency_where &p_das_where &p_doc_id_where &p_doc_val_where &p_header_posting_status &p_hint_clause &p_journal_date_where &p_journal_from_clause &p_journal_last_upd_where &p_journal_upd_date_where &p_journal_where_clause &p_ledger_from &p_ledger_where &p_not_zero_line_where &p_null_secondary_segment &p_perf_from &p_period_where &p_posting_status &p_select_secondary_seg_desc &p_select_secondary_segment &p_source_where &p_sub_doc_id_where &p_sub_doc_val_where 1=1 :p_access_set_id :p_aff_from :p_aff_to :p_amount_from :p_amount_to :p_amt_flag :p_coa_id :p_contra_acct :p_currency :p_end_date :p_gl_acc_segment :p_gl_bal_segment :p_gl_sec_segment :p_ledger :p_ledger_id :p_ledger_type :p_period_start_date_h :p_report_name :p_sec_seg_name :p_select_basis :p_start_date :p_start_date_h :p_start_period_name_h :p_status :p_use_date_col 1 -200 :p_access_set_id Number fnd_profile.value('GL_ACCESS_SET_ID') US Access Set ID 2 1=1 select nvl(gl_access_set_security_pkg.get_security_clause(fnd_profile.value('GL_ACCESS_SET_ID'),'R','LEDGER_COLUMN','LEDGER_ID','GLH','SEG_COLUMN',null,'CC',null),'1=1') from dual US Access Set ID 3 -190 :p_ledger_id Number select gl.ledger_id from gl_ledgers gl where gl.name = :$flex$.Ledger_Ledger_Set US Ledger ID 4 -180 :p_ledger_type Char select gl.object_type_code from gl_ledgers gl where gl.name = :$flex$.Ledger_Ledger_Set US Ledger Type 5 &p_ledger_where and lgr.ledger_id = :p_ledger_id L US Ledger Type 6 &p_ledger_where and ls.ledger_set_id = :p_ledger_id and lgr.ledger_id = ls.ledger_id S US Ledger Type 7 -170 :p_coa_id Number select gl.chart_of_accounts_id from gl_ledgers gl where gl.name = :$flex$.Ledger_Ledger_Set US Chart Of Accounts 8 -160 :p_start_period_name_h Char select nvl( :$flex$.period_name, ( select gp.period_name from gl_ledgers gl, gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gl.ledger_id = :$flex$.ledger_id and :$flex$.start_date between gp.start_date and gp.end_date ) ) from dual US p_start_period_name_h 9 -150 :p_period_start_date_h Date select gp.start_date from gl_ledgers gl, gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gl.ledger_id = :$flex$.ledger_id and ( gp.period_name = :$flex$.Period_Name or (:$flex$.Period_Name is null and :$flex$.Start_Date between gp.start_date and gp.end_date) ) US p_period_start_date_h 10 -140 :p_start_date_h Date select nvl( (select gp.start_date from gl_ledgers gl, gl_periods gp where gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gl.ledger_id = :$flex$.ledger_id and gp.period_name = :$flex$.Period_Name ),:$flex$.start_date) from dual US p_start_date_h 11 -130 :p_gl_bal_segment Char select 'cc.' || fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_BALANCING' and fsav.id_flex_num = :$flex$.chart_of_accounts and fsav.attribute_value = 'Y' US p_gl_bal_segment 12 -120 :p_gl_acc_segment Char select 'cc.' || fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_ACCOUNT' and fsav.id_flex_num = :$flex$.chart_of_accounts and fsav.attribute_value = 'Y' US p_gl_acc_segment 13 -110 :p_gl_sec_segment Char select 'cc.' || fifsv.application_column_name from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.form_left_prompt = :$flex$.Secondary_Segment and fifsv.id_flex_num = :$flex$.chart_of_accounts US p_gl_sec_segment 14 -100 :p_use_date_col Char select case when nvl(xxen_util.lookup_code(:$flex$.selection_basis,'SELECTION_BASE',101),'L') = 'J' then case when nvl(xxen_util.lookup_code(:$flex$.Status,'BATCH_STATUS',101),'U') = 'P' then 'glb2.posted_date' else 'glh2.default_effective_date' end else case when :$flex$.report_name in ('CONTRA','VOUCHER') or (xxen_util.lookup_code(:$flex$.selection_basis,'SELECTION_BASE',101) is not null and nvl(xxen_util.lookup_code(:$flex$.Status,'BATCH_STATUS',101),'U') != 'P') then 'gll.effective_date' when nvl(xxen_util.lookup_code(:$flex$.Status,'BATCH_STATUS',101),'U') = 'P' then 'glb.posted_date' else 'glh.default_effective_date' end end from dual US P_USE_DATE_COL 15 -90 &p_header_posting_status select case :p_status_code when 'E' then 'and glh.je_header_id = gll.je_header_id(+) and gll.code_combination_id = cc.code_combination_id(+)' else 'and glh.je_header_id = gll.je_header_id and gll.ledger_id = lgr.ledger_id and gll.code_combination_id = cc.code_combination_id' end from dual Char select nvl(xxen_util.lookup_code( :$flex$.Status,'BATCH_STATUS',101),'*null*') from dual US P_HEADER_POSTING_STATUS 16 -80 &p_hint_clause select case when :p_report_name = 'CHECK' then case when xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) = 'E' or xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) = 'U' or :p_batch_name is not null then null when xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) != 'E' or xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) != 'U' or :p_status is null then case when :p_ledger_type = 'S' then '/*+ ORDERED USE_NL(LS) INDEX(LS GL_LEDGER_SET_ASSIGNMENTS_N1) USE_NL(GLL) INDEX (GLL GL_JE_LINES_U1) USE_NL(GLL)INDEX(CC GL_CODE_COMBINATIONS_U1) USE_NL(GLB) INDEX(GLB GL_JE_BATCHES_U1) USE_NL(GJC) INDEX (GJC GL_JE_CATEGORIES_TL_U1) USE_NL(GJS) INDEX (GJS GL_JE_SOURCES_TL_U1) USE_NL(DCT) INDEX (DCT GL_DAILY_CONVERSION_TYPES_U1) USE_NL(DOCSEQ) INDEX(DOCSEQ FND_DOCUMENT_SEQUENCES_U1) USE_NL(SUBDOCSEQ) INDEX(SUBDOCSEQ FND_DOCUMENT_SEQUENCES_U1) USE_NL(FU) INDEX(FU FND_USER_U1) */' else '/*+ ORDERED USE_NL(GLL) INDEX (GLL GL_JE_LINES_U1) USE_NL(GLL)INDEX(CC GL_CODE_COMBINATIONS_U1) USE_NL(GLB) INDEX(GLB GL_JE_BATCHES_U1) USE_NL(GJC) INDEX (GJC GL_JE_CATEGORIES_TL_U1) USE_NL(GJS) INDEX (GJS GL_JE_SOURCES_TL_U1) USE_NL(DCT) INDEX (DCT GL_DAILY_CONVERSION_TYPES_U1) USE_NL(DOCSEQ) INDEX(DOCSEQ FND_DOCUMENT_SEQUENCES_U1) USE_NL(SUBDOCSEQ) INDEX(SUBDOCSEQ FND_DOCUMENT_SEQUENCES_U1) USE_NL(FU) INDEX(FU FND_USER_U1) */' end else '/*+ ORDERED */' end else null end from dual Char Y US P_HINT_CLAUSE 17 -70 &p_perf_from select case when :p_report_name = 'CHECK' then case when xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) = 'E' or xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) = 'U' or :p_batch_name is not null then ' gl_je_batches glb, gl_je_headers glh '|| decode(:p_ledger_type,'S',', gl_ledger_set_assignments ls',null) || ' , gl_je_lines gll, gl_code_combinations cc, gl_ledgers lgr, gl_je_sources gjs, gl_je_categories gjc, '|| ' gl_daily_conversion_types dct, fnd_document_sequences docseq, '|| ' fnd_document_sequences subdocseq, fnd_user fu ' when xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) != 'E' or xxen_util.lookup_code(:p_status,'BATCH_STATUS',101) != 'U' or :p_status is null then ' gl_je_headers glh '|| decode(:p_ledger_type,'S',', gl_ledger_set_assignments ls',null) || ' , gl_je_batches glb, gl_je_lines gll, gl_code_combinations_kfv cc, gl_je_sources gjs, gl_ledgers lgr,'|| ' gl_je_categories gjc, gl_daily_conversion_types dct, fnd_document_sequences docseq, '|| ' fnd_document_sequences subdocseq, fnd_user fu ' else ' gl_ledgers lgr, gl_je_batches glb, gl_je_headers glh, gl_je_sources gjs, gl_je_categories gjc,' || ' gl_je_lines gll, gl_daily_conversion_types dct,fnd_document_sequences docseq, fnd_document_sequences subdocseq, gl_code_combinations cc, fnd_user fu ' || decode(:p_ledger_type,'S',', gl_ledger_set_assignments ls ',null) end else ' gl_ledgers lgr, gl_je_batches glb, gl_je_headers glh, gl_je_sources gjs, gl_je_categories gjc,' || ' gl_je_lines gll, gl_daily_conversion_types dct,fnd_document_sequences docseq, fnd_document_sequences subdocseq, gl_code_combinations cc, fnd_user fu ' || decode(:p_ledger_type,'S',', gl_ledger_set_assignments ls',null) end from dual Char Y US P_PERF_FROM 18 -60 &p_amount_where select case when nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') != 'J' and (:p_amount_from is not null or :p_amount_to is not null) then case :p_amt_flag when 'DR' then ' and gll.accounted_dr <> 0 and gll.accounted_dr ' when 'CR' then ' and gll.accounted_cr <> 0 and gll.accounted_cr ' else ' and ((gll.accounted_dr <> 0 and gll.accounted_dr ' end || case when :p_amt_flag is null then case when :p_amount_from is not null and :p_amount_to is null then ') or (gll.accounted_cr <> 0 and gll.accounted_cr >= :p_amount_from))' when :p_amount_from is null and :p_amount_to is not null then ') or (gll.accounted_cr <> 0 and gll.accounted_cr <= :p_amount_to))' else ') or (gll.accounted_cr <> 0 and gll2.accounted_cr between :p_amount_from and :p_amount_to))' end else null end else null end amount_where from dual Char Y US P_AMOUNT_WHERE 19 -50 &p_journal_from_clause with q1 as ( select --journal_from case when nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') = 'J' then case when upper(:p_use_date_col) = 'GLB2.POSTED_DATE' or upper(:p_use_date_col) = 'GLH2.DEFAULT_EFFECTIVE_DATE' then ' gl_je_batches glb2, ' else null end else null end journal_from, --journal_where case when nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') = 'J' then case when upper(:p_use_date_col) = 'GLB2.POSTED_DATE' or upper(:p_use_date_col) = 'GLH2.DEFAULT_EFFECTIVE_DATE' then ' and glb2.je_batch_id = glh2.je_batch_id ' else null end else null end journal_where, -- p_journal_date_where_2 case when nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') = 'J' then case when upper(:p_use_date_col) = 'GLH2.DEFAULT_EFFECTIVE_DATE' and (:p_start_date is not null or :p_end_date is not null) then ' and glh2.period_name in (select period_name from gl_period_statuses ps where ps.application_id = 101 and ps.ledger_id = lgr1.ledger_id ' || case when :p_start_date is null then ' and ( ps.start_date <= :p_end_date)' when :p_send_date is null then ' and ( ps.end_date >= :p_start_date)' else ' and (ps.end_date >= :p_start_date and ps.start_date <= :p_end_date)' end || ')' when upper(:p_use_date_col) = 'GLL2.EFFECTIVE_DATE' and (:p_start_date is not null or :p_end_date is not null) then ' and gll2.period_name in (select period_name from gl_period_statuses ps where ps.application_id = 101 and ps.set_of_books_id = :p_ledger_id ' || case when :p_start_date is null then ' and ( ps.start_date <= :p_end_date)' when :p_send_date is null then ' and ( ps.end_date >= :p_start_date)' else ' and (ps.end_date >= :p_start_date and ps.start_date <= :p_end_date)' end || ')' else null end || case when (:p_start_date is not null or :p_end_date is not null) then case when :p_start_date is null then ' and trunc('|| :p_use_date_col ||') <= :p_end_date' when :p_send_date is null then ' and trunc('|| :p_use_date_col ||') >= :p_start_date' else ' and trunc('|| :p_use_date_col ||') between :p_start_date and :p_end_date' end else null end else null end p_journal_date_where_2, -- amount_where_2 case when nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') = 'J' and (:p_amount_from is not null or :p_amount_to is not null) then case :p_amt_flag when 'DR' then ' and gll2.accounted_dr <> 0 and gll2.accounted_dr ' when 'CR' then ' and gll2.accounted_cr <> 0 and gll2.accounted_cr ' else ' and ((gll2.accounted_dr <> 0 and gll2.accounted_dr ' end || case when :p_amt_flag is null then case when :p_amount_from is not null and :p_amount_to is null then ') or (gll2.accounted_cr <> 0 and gll2.accounted_cr >= :p_amount_from))' when :p_amount_from is null and :p_amount_to is not null then ') or (gll2.accounted_cr <> 0 and gll2.accounted_cr <= :p_amount_to))' else ') or (gll2.accounted_cr <> 0 and gll2.accounted_cr between :p_amount_from and :p_amount_to))' end else null end else null end amount_where_2 from dual ) select ', (select distinct gll2.je_header_id je_header_id, gll2.subledger_doc_sequence_id sub_doc_seq_id, gll2.subledger_doc_sequence_value sub_doc_seq_value from ' || q1.journal_from || ' gl_ledgers lgr1, gl_je_lines gll2, gl_je_headers glh2, gl_code_combinations_kfv gcc3 '||decode(:p_ledger_type,'S',', gl_ledger_set_assignments ls1',null)||' where lgr1.ledger_id = glh2.ledger_id '|| q1.journal_where ||' '||decode(:p_ledger_type,'S',' and ls1.ledger_set_id = :p_ledger_id and lgr1.ledger_id = ls1.ledger_id',' and lgr1.ledger_id = :p_ledger_id')||' '|| q1.p_journal_date_where_2 || ' '|| q1.amount_where_2 || case when :p_aff_from is not null then ' and gcc3.concatenated_segments >= :p_aff_from ' else null end || case when :p_aff_to is not null then ' and gcc3.concatenated_segments <= :p_aff_to ' else null end || ' and ' || nvl(gl_access_set_security_pkg.get_security_clause(fnd_profile.value('GL_ACCESS_SET_ID'),'R','LEDGER_COLUMN','LEDGER_ID','GLH2','SEG_COLUMN',null,'GCC2',null),'1=1') || ' and gll2.je_header_id = glh2.je_header_id and glh2.ledger_id = gll2.ledger_id and gll2.code_combination_id = gcc3.code_combination_id and (nvl(gll2.accounted_dr, 0) <> 0 or nvl(gll2.accounted_cr, 0) <> 0 )) sub_table ' from q1 Char select 'Y' from dual where nvl(xxen_util.lookup_code(:$flex$.selection_basis,'SELECTION_BASE',101),'L') = 'J' US P_JOURNAL_FROM_CLAUSE 20 -40 &p_journal_date_where select case when nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') != 'J' then case when (upper(:p_use_date_col) = 'GLL.EFFECTIVE_DATE') and (:p_start_date is not null or :p_end_date is not null) then ' and gll.period_name in (select /*+ cardinailty(ps,2 ) */ period_name from gl_period_statuses ps where ps.application_id = 101 and ps.ledger_id = lgr.ledger_id ' || case when :p_start_date is null then ' and ( ps.start_date <= :p_end_date)' when :p_end_date is null then ' and ( ps.end_date >= :p_start_date)' else ' and (ps.end_date >= :p_start_date and ps.start_date <= :p_end_date)' end || ')' else null end || case when (:p_start_date is not null or :p_end_date is not null) then case when :p_start_date is null then ' and trunc('|| :p_use_date_col ||') <= :p_end_date' when :p_end_date is null then ' and trunc('|| :p_use_date_col ||') >= :p_start_date' else ' and trunc('|| :p_use_date_col ||') between :p_start_date and :p_end_date' end else null end else null end p_journal_date_where from dual Char select 'Y' from dual where nvl(xxen_util.lookup_code(:$flex$.selection_basis,'SELECTION_BASE',101),'L') != 'J' US P_JOURNAL_DATE_WHERE 21 -30 &p_not_zero_line_where select ' and (((nvl(gll.accounted_dr, 0) <> 0) or (nvl(gll.accounted_cr, 0) <> 0)) or ((nvl(gll.entered_dr, 0) <> 0) or (nvl(gll.entered_cr, 0) <> 0))) ' from dual where nvl(xxen_util.lookup_code( :p_status,'BATCH_STATUS',101),'P') != 'E' Char Y US P_NOT_ZERO_LINE_WHERE 22 -20 &p_contra_account_where and exists (select gjl2.je_line_num from gl_je_lines gjl2, gl_code_combinations_kfv gcc2 where gjl2.je_header_id = glh.je_header_id and gjl2.code_combination_id = gcc2.code_combination_id and ((gjl2.subledger_doc_sequence_id = gll.subledger_doc_sequence_id and gjl2.subledger_doc_sequence_value = gll.subledger_doc_sequence_value ) or (gll.subledger_doc_sequence_id is null and gjl2.subledger_doc_sequence_id is null and gjl2.subledger_doc_sequence_value is null ) ) and ((gll.accounted_dr is not null and gll.accounted_dr<> 0 and gjl2.accounted_cr is not null and gjl2.accounted_cr <> 0 ) or (gll.accounted_cr is not null and gll.accounted_cr<> 0 and gjl2.accounted_dr is not null and gjl2.accounted_dr <> 0 ) ) and gcc2.concatenated_segments = :p_contra_acct ) Char select 'Y' from dual where :$flex$.Contra_Accounting_Flexfield is not null US P_CONTRA_ACCOUNT_WHERE 23 -10 &p_null_secondary_segment '' Char select 'Y' from dual where :$flex$.secondary_segment is null US P_NULL_SECONDARY_SEGMENT 24 10 :p_report_name LOV custom Y select x.value, x.description from ( select 'CHECK' value, null description from dual union select 'CONTRA' value, null description from dual union select 'VOUCHER' value, null description from dual ) x order by x.value CHECK Y US Report Name 25 20 :p_ledger LOV custom Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_access_set_ledgers gasl, gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gasl.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gl.ledger_id=gasl.ledger_id and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by value select name from gl_ledgers where ledger_id = gl_access_set_security_pkg.get_default_ledger_id(fnd_profile.value('GL_ACCESS_SET_ID'), 'R') Y F Grand livre US Ledger/Ledger Set Ledger Name 26 30 :p_select_basis LOV custom Y select meaning value, description from fnd_lookup_values_vl where lookup_type = 'SELECTION_BASE' and view_application_id = 101 order by value Lines US Selection Basis 27 &p_journal_where_clause and sub_table.je_header_id = gll.je_header_id and (sub_table.sub_doc_seq_id is null or sub_table.sub_doc_seq_id = gll.subledger_doc_sequence_id ) and (sub_table.sub_doc_seq_value is null or sub_table.sub_doc_seq_value = gll.subledger_doc_sequence_value ) Journals US Selection Basis 28 40 1=1 glb.default_period_name = :p_period_name LOV custom Y select gp.period_name value, ( select xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_period_statuses gps where gp.period_name=gps.period_name and gps.ledger_id=gl.ledger_id and gps.application_id=101 ) description from gl_ledgers gl, gl_periods gp where gp.end_date<=sysdate and gp.period_set_name = gl.period_set_name and gp.period_type = gl.accounted_period_type and gl.name = :$flex$.ledger_ledger_set order by gp.start_date desc select distinct max(gp.period_name) keep (dense_rank last order by gp.start_date,gp.period_year,gp.period_num) over () period_name from gl.gl_periods gp where :$flex$.batch is null and :$flex$.journal is null and gp.start_date<=sysdate and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.chart_of_accounts is null or gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) F Période du US Period Name 29 50 :p_start_date Date US Start Date 30 60 :p_end_date Date US End Date 31 70 1=1 fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') = :p_bal_seg_val LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_segment_attribute_values fsav, fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_BALANCING' and fsav.id_flex_num = :$flex$.chart_of_accounts and fsav.attribute_value = 'Y' and fifsgv.application_id=fsav.application_id and fifsgv.id_flex_code=fsav.id_flex_code and fifsgv.id_flex_num=fsav.id_flex_num and fifsgv.application_column_name = fsav.application_column_name and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) order by ffvv.flex_value US Balancing Segment 32 80 :p_sec_seg_name LOV custom Y select fifsv.form_left_prompt value, to_char(fifsv.segment_num,'90') || ' ' || lower(fifsv.application_column_name) description from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num = :$flex$.chart_of_accounts order by fifsv.segment_num US Secondary Segment 33 &p_select_secondary_seg_desc select 'fnd_flex_xml_publisher_apis.process_kff_combination_1(''acct_flex_seg'', ''SQLGL'', ''GL#'', cc.chart_of_accounts_id, NULL, cc.code_combination_id,' || fifsv.segment_num || ', ''Y'', ''DESCRIPTION'')' from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.form_left_prompt = :p_sec_seg_name and fifsv.id_flex_num = :p_coa_id US Secondary Segment 34 &p_select_secondary_segment select 'cc.' || lower(fifsv.application_column_name) from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.form_left_prompt = :p_sec_seg_name and fifsv.id_flex_num = :p_coa_id US Secondary Segment 35 90 :p_aff_from LOV custom Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.detail_posting_allowed = 'Y' and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.chart_of_accounts_id = :$flex$.chart_of_accounts order by gcck.concatenated_segments US Accounting Flexfield From 36 &p_aff_from_where select 'and cc.'||lower(x.application_column_name)||'>='''|| regexp_substr(x.concatenated_segments || x.delim, '([^' || x.delim || ']*)\' || x.delim, 1, x.seq, null, 1) || '''' text from ( select :p_aff_from concatenated_segments, fifst.concatenated_segment_delimiter delim, fifss.application_column_name, fifss.segment_num, row_number() over (order by fifss.segment_num) seq from fnd_id_flex_structures fifst, fnd_id_flex_segments fifss where fifst.application_id=101 and fifst.id_flex_code='GL#' and fifst.id_flex_num=:p_coa_id and fifss.application_id=fifst.application_id and fifss.id_flex_code=fifst.id_flex_code and fifss.id_flex_num=fifst.id_flex_num and fifss.enabled_flag='Y' order by fifss.segment_num ) x where nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') != 'J' US Accounting Flexfield From 37 &p_aff_from_where2 select 'and cc.'||lower(x.application_column_name)||'>='''''|| regexp_substr(x.concatenated_segments || x.delim, '([^' || x.delim || ']*)\' || x.delim, 1, x.seq, null, 1) || '''''' text from ( select :p_aff_from concatenated_segments, fifst.concatenated_segment_delimiter delim, fifss.application_column_name, fifss.segment_num, row_number() over (order by fifss.segment_num) seq from fnd_id_flex_structures fifst, fnd_id_flex_segments fifss where fifst.application_id=101 and fifst.id_flex_code='GL#' and fifst.id_flex_num=:p_coa_id and fifss.application_id=fifst.application_id and fifss.id_flex_code=fifst.id_flex_code and fifss.id_flex_num=fifst.id_flex_num and fifss.enabled_flag='Y' order by fifss.segment_num ) x US Accounting Flexfield From 38 100 :p_aff_to LOV custom Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.detail_posting_allowed = 'Y' and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.chart_of_accounts_id = :$flex$.chart_of_accounts order by gcck.concatenated_segments :$flex$.Accounting_Flexfield_From US Accounting Flexfield To 39 &p_aff_to_where select 'and cc.'||lower(x.application_column_name)||'<='''|| regexp_substr(x.concatenated_segments || x.delim, '([^' || x.delim || ']*)\' || x.delim, 1, x.seq, null, 1) || '''' text from ( select :p_aff_to concatenated_segments, fifst.concatenated_segment_delimiter delim, fifss.application_column_name, fifss.segment_num, row_number() over (order by fifss.segment_num) seq from fnd_id_flex_structures fifst, fnd_id_flex_segments fifss where fifst.application_id=101 and fifst.id_flex_code='GL#' and fifst.id_flex_num=:p_coa_id and fifss.application_id=fifst.application_id and fifss.id_flex_code=fifst.id_flex_code and fifss.id_flex_num=fifst.id_flex_num and fifss.enabled_flag='Y' order by fifss.segment_num ) x where nvl(xxen_util.lookup_code(:p_select_basis,'SELECTION_BASE',101),'L') != 'J' US Accounting Flexfield To 40 &p_aff_to_where2 select 'and cc.'||lower(x.application_column_name)||'<='''''|| regexp_substr(x.concatenated_segments || x.delim, '([^' || x.delim || ']*)\' || x.delim, 1, x.seq, null, 1) || '''''' text from ( select :p_aff_to concatenated_segments, fifst.concatenated_segment_delimiter delim, fifss.application_column_name, fifss.segment_num, row_number() over (order by fifss.segment_num) seq from fnd_id_flex_structures fifst, fnd_id_flex_segments fifss where fifst.application_id=101 and fifst.id_flex_code='GL#' and fifst.id_flex_num=:p_coa_id and fifss.application_id=fifst.application_id and fifss.id_flex_code=fifst.id_flex_code and fifss.id_flex_num=fifst.id_flex_num and fifss.enabled_flag='Y' order by fifss.segment_num ) x US Accounting Flexfield To 41 110 :p_contra_acct LOV custom Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from gl_code_combinations_kfv gcck where gcck.detail_posting_allowed = 'Y' and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.chart_of_accounts_id = :$flex$.chart_of_accounts and :$flex$.report_name = 'CONTRA' order by gcck.concatenated_segments US Contra Accounting Flexfield 42 120 :p_status LOV GL Journal Status B16B03AA6AEB5FDDE0530100007FE310 Y select xxen_util.meaning('P','BATCH_STATUS',101) value, null description from dual union all select xxen_util.meaning('U','BATCH_STATUS',101) value, null description from dual union all select xxen_util.meaning('E','VALIDATION_STATUS',101) value, null description from dual xxen_util.meaning('P','BATCH_STATUS',101) F Statut US Status 43 &p_posting_status select case xxen_util.lookup_code( :p_status,'BATCH_STATUS',101) when 'E' then 'and glb.status in (select lookup_code from gl_lookups where lookup_type = ''MJE_BATCH_STATUS'' and lookup_code not in (''S'',''I'',''U'',''P''))' else 'and glb.status = xxen_util.lookup_code( :p_status,''BATCH_STATUS'',101)' end from dual US Status 44 130 :p_currency LOV FND Currency 8E2FF36EDE9079D2E0530100007F1FF2 Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code select gl.currency_code from gl_ledgers gl where gl.name = :$flex$.Ledger_Ledger_Set US Currency 45 &p_currency_where select case when :p_currency = 'STAT' then 'and glh.currency_code = ''STAT''' else 'and glh.currency_code != ''STAT''' end from dual US Currency 46 140 1=1 glh.actual_flag in (select flvv.lookup_code from fnd_lookup_values_vl flvv where flvv.description=:p_actual_flag and flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0) LOV GL Balance Type 8E2FF36EDF3C79D2E0530100007F1FF2 Y select flvv.description value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0 order by flvv.lookup_code select flv.description from fnd_lookup_values flv where flv.lookup_type='BATCH_TYPE' and flv.view_application_id=101 and flv.language(+)=userenv('lang') and flv.security_group_id=0 and flv.lookup_code = 'A' F Type d'équilibre US Balance Type 47 150 1=1 glb.name=:p_batch_name LOV GL Batch B16C8DB2BC207B92E0530100007F1372 Y Y select distinct gjb.name value, gjb.description from gl_je_batches gjb where gjb.je_batch_id in (select gjh.je_batch_id from gl_je_headers gjh where (:$flex$.journal is null or gjh.name=:$flex$.journal) and gjh.ledger_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y' or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y'))) order by gjb.name F Lot US Batch 48 160 1=1 (select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjsv.je_source_name=glh.je_source) <= :user_je_source_name_fr LOV GL Journal Source 8E2FF36EDF2379D2E0530100007F1FF2 Y select gjsv.user_je_source_name value, gjsv.description from gl_je_sources_vl gjsv order by gjsv.user_je_source_name F Source du journal US Journal Source From 49 170 1=1 (select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjsv.je_source_name=glh.je_source) <= :user_je_source_name_to LOV GL Journal Source 8E2FF36EDF2379D2E0530100007F1FF2 Y select gjsv.user_je_source_name value, gjsv.description from gl_je_sources_vl gjsv order by gjsv.user_je_source_name US Journal Source To 50 180 1=1 (select gjcv.user_je_category_name from gl_je_categories_vl gjcv where gjcv.je_category_name=glh.je_category) >= :user_journal_category_fr LOV GL Journal Category 8E2FF36EDF2479D2E0530100007F1FF2 Y select gjcv.user_je_category_name value, gjcv.description from gl_je_categories_vl gjcv order by gjcv.user_je_category_name F Catégorie de journal US Journal Category From 51 190 1=1 (select gjcv.user_je_category_name from gl_je_categories_vl gjcv where gjcv.je_category_name=glh.je_category) >= :user_journal_category_fr LOV GL Journal Category 8E2FF36EDF2479D2E0530100007F1FF2 Y select gjcv.user_je_category_name value, gjcv.description from gl_je_categories_vl gjcv order by gjcv.user_je_category_name US Journal Category To 52 200 :p_amount_from Number US Amount From 53 210 :p_amount_to Number US Amount To 54 220 :p_amt_flag LOV custom Y select x.value, null description from (select 'DR' value from dual union select 'CR' value from dual ) x order by x.value US Amount Flag 55 230 1=1 glh.doc_sequence_id = (select fds.doc_sequence_id from fnd_document_sequences fds where fds.name = :p_doc_seq_name) LOV custom select fds.name value, null description from fnd_document_sequences fds, fnd_doc_sequence_assignments fdsa where fdsa.doc_sequence_id = fds.doc_sequence_id and fdsa.set_of_books_id in ( select gl.ledger_id from gl_ledgers gl where gl.ledger_id = :$flex$.ledger_id union select l.ledger_id from apps.gl_ledgers ls, apps.gl_ledgers l, apps.gl_ledger_set_assignments lsa where lsa.ledger_set_id = :$flex$.ledger_id and l.ledger_id = lsa.ledger_id ) order by fds.name US Document Sequence Name 56 240 1=1 glh.doc_sequence_value = :p_doc_value LOV custom Y Y select to_char(doc_sequence_value) value, null description from gl_doc_sequence_audit where doc_sequence_id= (select doc_sequence_id from fnd_document_sequences where name = :$flex$.document_sequence_name) order by value US Document Number 57 250 1=1 glh.doc_sequence_value >= :p_start_doc_value LOV custom Y Y select to_char(doc_sequence_value) value, null description from gl_doc_sequence_audit where doc_sequence_id= (select doc_sequence_id from fnd_document_sequences where name = :$flex$.document_sequence_name) order by value US Document Number Low 58 260 1=1 glh.doc_sequence_value <= :p_end_doc_value LOV custom Y Y select to_char(doc_sequence_value) value, null description from gl_doc_sequence_audit where doc_sequence_id= (select doc_sequence_id from fnd_document_sequences where name = :$flex$.document_sequence_name) order by value :$flex$.Document_Number_Low US Document Number High 59 270 1=1 gll.subledger_doc_sequence_id = (select fds.doc_sequence_id from fnd_document_sequences fds where fds.name = :p_doc_seq_name) LOV custom Y select fds.name value, null description from fnd_document_sequences fds, fnd_doc_sequence_assignments fdsa where fdsa.doc_sequence_id = fds.doc_sequence_id and fdsa.set_of_books_id in ( select gl.ledger_id from gl_ledgers gl where gl.ledger_id = :$flex$.ledger_id union select l.ledger_id from apps.gl_ledgers ls, apps.gl_ledgers l, apps.gl_ledger_set_assignments lsa where lsa.ledger_set_id = :$flex$.ledger_id and l.ledger_id = lsa.ledger_id ) order by fds.name US Subledger Document Seq 60 280 1=1 gll.subledger_doc_sequence_value = :p_sub_doc_value LOV custom Y Y select to_char(doc_sequence_value) value, null description from gl_doc_sequence_audit where doc_sequence_id= (select doc_sequence_id from fnd_document_sequences where name = :$flex$.subledger_doc_seq) order by value US Subledger Document Number 61 290 1=1 glh.last_updated_by = xxen_util.user_id(:p_last_updated_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US Last Updated By 62 300 1=1 trunc(glh.last_update_date) >= :p_start_update_date Date F Date d'entrée en vigueur US Update Date From 63 310 1=1 trunc(glh.last_update_date) < trunc(:p_end_update_date)+1 Date F Date de publication au US Update Date To