Query : AP to GL data transfer

SELECT glcc.segment1 "Company"
, gjjlv.period_name "PERIOD"
, gjb.name "JOURNAL BATCH NAME"
, gjjlv.header_name "JOURNAL SOURCE"
, gjjlv.line_reference_1 "SUPPLIER NAME"
, gjjlv.currency_code "CURRENCY"
, invoice_type_lookup_code "TRANSACTION TYPE"
, gjjlv.line_reference_5 "TRANSACTION NUMBER"
, aia.invoice_date "TRANSACTION DATE"
-- , gjjlv.je_source "SOURCE"
, gjjlv.line_entered_dr "ENTERED DEBIT"
, gjjlv.line_entered_cr "ENTERED CREDIT"
, gjjlv.line_accounted_dr "ACCOUNTED_DEBIT"
, gjjlv.line_accounted_cr "ACCOUNTED_CREDIT"
,glcc.concatenated_segments "CHARGE ACCOUNT"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gjl
, ap_ae_lines_all aala
, gl_je_headers gjh
, gl_je_batches gjb
, ap_invoices_all aia
, apps.gl_code_combinations_KFV glcc
, po_vendors pv
WHERE gjl.gl_sl_link_table = 'APECL'
AND gjl.period_name ='NOV-2008'--period
--and gjjlv.currency_code = --currency code
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.period_name = gjl.period_name
AND gjh.set_of_books_id = gjl.set_of_books_id
AND glcc.code_combination_id = gjl.code_combination_id
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gjl.je_line_num
AND gjh.period_name = gjjlv.period_name
AND gjh.set_of_books_id = gjjlv.set_of_books_id
AND glcc.code_combination_id = gjjlv.line_code_combination_id
AND aala.code_combination_id = gjl.code_combination_id
AND aala.gl_sl_link_id = gjl.gl_sl_link_id
AND aala.reference5 = aia.invoice_num
AND gjh.set_of_books_id = aia.set_of_books_id
AND pv.vendor_id = aia.vendor_id
AND gjjlv.line_reference_1 = pv.vendor_name
ORDER BY aia.invoice

0 comments:

Post a Comment