Description:
In database management systems, query by example (QBE) refers to a method of forming queries in which the database program displays a new record with a space for each field. You can then enter conditions for each field that you want to be included in the query.
SELECT glimp.je_header_id je_header_id, glimp.je_line_num je_line_num,
xal.accounted_cr accounted_cr, xal.accounted_dr accounted_dr,
xal.entered_cr entered_cr, xal.entered_dr entered_dr,
xah.accounting_date accounting_date,
xal.accounting_class_code acct_line_type,
xal.code_combination_id code_combination_id,
gcc.segment1
|| ‘.’
|| gcc.segment3
|| ‘.’
|| gcc.segment2
|| ‘.’
|| gcc.segment4
|| ‘.’
|| gcc.segment5 account_combination,
gcc.segment1 company_code, gcc.segment3 center_code,
gcc.segment2 account_code, gcc.segment4 subaccount,
gcc.segment5 cost_center_type,
xal.description comments,
xah.gl_transfer_status_code gl_transfer_status,
xal.party_id vendor_id,
v.segment1 vendor_number,
v.vendor_name vendor_name, xal.party_site_id vendor_site_id,
vs.vendor_site_code vendor_site_name,
c.check_id check_id,
c.check_number check_number,
ae.event_number accounting_event_number,
ae.event_type_code accounting_event_type,
xal.ae_line_num accounting_line_number,
xah.je_category_name je_category,
jc.user_je_category_name user_je_category_name,
c.currency_code trx_hdr_currency,
xal.last_update_date last_update_date,
xal.last_updated_by last_updated_by,
xal.creation_date creation_date, xal.created_by created_by,
xal.last_update_login last_update_login, xal.request_id request_id,
c.bank_account_name bank_account_name
i.invoice_num invoice_number,
i.invoice_currency_code invoice_currency,
d.distribution_line_number invoice_line_number,
d.line_type_lookup_code invoice_line_type,
ph.segment1 po_order_number, pr.release_num po_order_release_num,
rsh.receipt_num rcv_receipt_num
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references glimp,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
gl_daily_conversion_types glct,
gl_je_categories jc,
ap_checks_all c,
ap_invoices_all i,
ap_invoice_payments_all p,
ap_invoice_distributions_all d,
ap_invoice_lines_all l,
ap_accounting_events_all ae,
po_vendor_sites_all vs,
po_vendors v,
po_distributions_all pd,
po_headers_all ph,
po_releases_all pr,
ce_statement_lines sl,
ce_statement_headers sh,
rcv_transactions rct,
rcv_shipment_headers rsh,
ce_statement_reconciliations csr,
zx_lines_summary_v zls,
zx_rates_vl zr,
fnd_document_sequences fd,
fnd_document_sequences fd2,
fnd_flex_values_vl ffvv1,
ap_lookup_codes l1
WHERE xal.application_id = xah.application_id
AND xah.ae_header_id = xal.ae_header_id
AND glimp.je_header_id = glh.je_header_id
AND glimp.je_line_num = gll.je_line_num
AND glh.je_header_id = gll.je_header_id
AND glimp.gl_sl_link_id = xal.gl_sl_link_id
AND gcc.code_combination_id = xal.code_combination_id
AND xal.currency_conversion_type = glct.conversion_type(+)
AND jc.je_category_name = xah.je_category_name
AND d.invoice_id = i.invoice_id
AND i.invoice_id = p.invoice_id
AND c.check_id = ae.source_id
AND c.check_id = p.check_id
AND ae.accounting_event_id = xah.event_id
AND xal.party_id = v.vendor_id
AND xal.party_site_id = vs.vendor_site_id
AND ph.po_header_id(+) = pd.po_header_id
AND pd.po_distribution_id(+) = d.po_distribution_id
AND pr.po_release_id(+) = pd.po_release_id
AND sl.statement_header_id = sh.statement_header_id(+)
AND csr.statement_line_id = sl.statement_line_id(+)
AND rsh.shipment_header_id(+) = rct.shipment_header_id
AND d.rcv_transaction_id = rct.transaction_id(+)
and l.invoice_id=i.invoice_id
and l.line_number=1
AND csr.reference_type(+) = ‘PAYMENT’
AND csr.current_record_flag(+) = ‘Y’
AND gll.je_header_id(+) = csr.je_header_id
AND c.check_id = csr.reference_id(+)
AND i.invoice_id = zls.trx_id(+)
AND zls.tax_rate_id = zr.tax_rate_id(+)
AND c.doc_sequence_id = fd.doc_sequence_id(+)
AND sh.doc_sequence_id = fd2.doc_sequence_id(+)
AND ae.source_table = ‘AP_CHECKS’
AND l1.lookup_code = c.payment_method_lookup_code
AND l1.lookup_type = ‘PAYMENT METHOD’
AND DECODE (xal.source_table,
‘AP_INVOICE_PAYMENTS’, xal.source_id,
NULL
) = p.invoice_payment_id
and l.line_number=d.invoice_line_number
–Dhiraj Jha
Summary:
The queries to create the various link as PO, AP, GL, ZX, CE, RCV, & XLA.
Queries?
Do drop a note by writing us at contact@staging.doyensys.com or use the comment section below to ask your questions.
