R12 Oracle Apps: 11i GL Query

11i AP-GL Links

SELECT distinct b.period_name, b.line_reference_2, a.po_order_number, a.rcv_receipt_num "Receipt Number",
a.trx_date "Receipt Date", a.trx_number_displayed "Invoice Number",
a.accounting_date "Invoice Date", a.third_party_name "Vendor Name",
a.third_party_number "Vendor Number", b.je_source "Source", b.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
a.currency_code, a.entered_dr, a.entered_cr, a.accounted_dr,
a.accounted_cr, a.je_header_id, a.je_line_num, a.code_combination_id,
a.doc_sequence_value "Vocher Number", a.gl_date "GL_Date",
a.attribute1 "Staff Number", a.attribute2 "Project",
a.attribute3 "Customer Num/Name", a.description "Description"
FROM
(SELECT ph.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
i.invoice_date trx_date, i.invoice_num trx_number_displayed,
aeh.accounting_date accounting_date,
v.vendor_name third_party_name, v.segment1 third_party_number,
ael.currency_code currency_code, ael.entered_dr entered_dr,
ael.entered_cr entered_cr, ael.accounted_dr accounted_dr,
ael.accounted_cr accounted_cr, r.je_header_id je_header_id,
r.je_line_num je_line_num,
ael.code_combination_id code_combination_id,
200 application_id, i.gl_date, i.doc_sequence_value,
d.description, d.attribute1, d.attribute2, d.attribute3
FROM gl_import_references r,
gl_je_categories jc,
ap_ae_lines_all ael,
ap_ae_headers_all aeh,
ap_invoice_distributions_all d,
ap_invoices_all i,
ap_accounting_events_all ae,
po_releases_all pr,
po_headers_all ph,
po_distributions_all pd,
rcv_transactions rct,
rcv_shipment_headers rsh,
po_vendor_sites_all vs,
po_vendors v,
ap_tax_codes_all AT,
gl_daily_conversion_types glct,
fnd_document_sequences fd,
ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7,
ap_lookup_codes l8,
ap_lookup_codes l9,
ap_lookup_codes l10,
ap_lookup_codes l11,
ap_lookup_codes l12
WHERE l2.lookup_code(+) = d.line_type_lookup_code
AND l2.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
AND l3.lookup_code = ael.ae_line_type_code
AND l3.lookup_type = 'AE LINE TYPE'
AND l4.lookup_code = aeh.gl_transfer_flag || ''
AND l4.lookup_code = aeh.gl_transfer_flag || ''
AND l4.lookup_type = 'POSTING STATUS'
AND l5.lookup_code = ae.event_type_code
AND l5.lookup_type = 'EVENT TYPE'
AND l6.lookup_code(+) = aeh.accounting_error_code
AND l6.lookup_type(+) = 'ACCOUNTING ERROR TYPE'
AND l7.lookup_code(+) = ael.accounting_error_code
AND l7.lookup_type(+) = 'ACCOUNTING ERROR TYPE'
AND l8.lookup_code = 'Invoices'
AND l8.lookup_type = 'POSTING CATEGORY'
AND l9.lookup_code(+) = ael.gl_transfer_error_code
AND l9.lookup_type(+) = 'POSTING EXCEPTIONS'
AND l10.lookup_code = 'EVENT'
AND l10.lookup_type = 'VIEW_ACCOUNTING'
AND l11.lookup_code = 'LINE'
AND l11.lookup_type = 'VIEW_ACCOUNTING'
AND l12.lookup_code = 'DIST_LINE_NUM'
AND l12.lookup_type = 'VIEW_ACCOUNTING'
AND jc.je_category_name = aeh.ae_category
AND rsh.shipment_header_id(+) = rct.shipment_header_id
AND d.rcv_transaction_id = rct.transaction_id(+)
AND pr.po_release_id(+) = pd.po_release_id
AND ph.po_header_id(+) = pd.po_header_id
AND pd.po_distribution_id(+) = d.po_distribution_id
AND ael.currency_conversion_type = glct.conversion_type(+)
AND ael.subledger_doc_sequence_id = fd.doc_sequence_id(+)
AND ael.third_party_sub_id = vs.vendor_site_id
AND ael.third_party_id = v.vendor_id
-- AND l1.lookup_code = i.invoice_type_lookup_code
-- AND l1.lookup_type = 'INVOICE TYPE'
AND AT.tax_id(+) = ael.tax_code_id
AND ae.accounting_event_id = aeh.accounting_event_id
AND i.invoice_id(+) = ae.source_id
--- AND ae.source_table = 'AP_INVOICES'
AND (ae.source_table = 'AP_INVOICES'
OR ae.source_table = 'AP_CHECKS'
OR ae.source_table = 'AP_INVOICE_PAYMENTS')

AND aeh.ae_header_id = ael.ae_header_id
AND DECODE (ael.source_table,
'AP_INVOICE_DISTRIBUTIONS', ael.source_id,
NULL
) = d.invoice_distribution_id(+)
AND ael.gl_sl_link_id = r.gl_sl_link_id
UNION ALL
SELECT poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
rct.transaction_date trx_date, NULL trx_number_displayed,
rrs.accounting_date accounting_date,
pov.vendor_name third_party_name,
pov.segment1 third_party_number, rrs.currency_code,
rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
rrs.accounted_cr, r.je_header_id, r.je_line_num,
rrs.code_combination_id, 201 application_id,null, --ai.gl_date,
null,poh.COMMENTS DESCRIPTION,null,-- ai.doc_sequence_value, aid.description, aid.attribute1,
null,null-- aid.attribute2, aid.attribute3
FROM gl_import_references r,
rcv_receiving_sub_ledger rrs,
--ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
po_distributions_all pod,
po_headers_all poh,
rcv_transactions rct,
rcv_shipment_headers rsh,
po_vendors pov
WHERE rrs.gl_sl_link_id = r.gl_sl_link_id
AND rct.transaction_id = rrs.rcv_transaction_id
AND rsh.shipment_header_id = rct.shipment_header_id
AND pod.po_distribution_id = rrs.reference3
AND poh.po_header_id = pod.po_header_id
-- AND aid.po_distribution_id(+) = pod.po_distribution_id
-- AND ai.invoice_id = aid.invoice_id
-- AND ai.vendor_id = pov.vendor_id
and exists (select 1
from ap_invoice_distributions_all aid,ap_invoices_all ai
where aid.po_distribution_id=pod.po_distribution_id
AND ai.invoice_id = aid.invoice_id
AND ai.vendor_id = pov.vendor_id ) ) a,
gl_je_journal_lines_v b,
gl_summary_combinations_v c
WHERE b.je_header_id = a.je_header_id
AND b.line_je_line_num = a.je_line_num
AND b.line_code_combination_id = c.code_combination_id
AND a.code_combination_id = b.line_code_combination_id
AND b.actual_flag = 'A'
AND b.batch_status || '' = 'P'
AND b.currency_code != 'STAT'
AND ( NVL (b.effective_period_num, 0) >= :cf_period_from
AND (NVL (b.effective_period_num, 0) <= :cf_period_to)
)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and b.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND a.currency_code=decode(:p_currency_code,'INR','INR',
'ALL',a.currency_code,
'NON INR',decode(a.currency_code,'INR','###',a.currency_code))
UNION ALL
SELECT period_name, gl_je_journal_lines_v.line_reference_2, TO_CHAR (NULL) po_order_number,
TO_CHAR (NULL) rcv_receipt_num, TO_DATE (NULL) trx_date,
TO_CHAR (NULL) trx_number_displayed, TO_DATE (NULL) accounting_date,
TO_CHAR (NULL) third_party_name, TO_CHAR (NULL) third_party_number,
je_source "Source", gl_je_journal_lines_v.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
gl_je_journal_lines_v.currency_code, line_entered_dr, line_entered_cr,
line_accounted_dr, line_accounted_cr,
JE_HEADER_ID,--TO_NUMBER (NULL) je_header_id,
LINE_JE_LINE_NUM,--TO_NUMBER (NULL) je_line_num,
line_code_combination_id,
DOC_SEQUENCE_VALUE,--TO_NUMBER (NULL) doc_sequence_value,
HEADER_EFFECTIVE_DATE,--TO_DATE (NULL) gl_date,
LINE_ATTRIBUTE1,--TO_CHAR (NULL) attribute1,
LINE_ATTRIBUTE2,--TO_CHAR (NULL) attribute2,
LINE_ATTRIBUTE3,--TO_CHAR (NULL) attribute3,
LINE_DESCRIPTION description--TO_CHAR (NULL) description
FROM gl_je_journal_lines_v , gl_summary_combinations_v, gl_sets_of_books
WHERE gl_summary_combinations_v.code_combination_id =
gl_je_journal_lines_v.line_code_combination_id
AND actual_flag = 'A'
AND batch_status || '' = 'P'
AND gl_je_journal_lines_v.currency_code != 'STAT'
AND je_source NOT IN ('Payables', 'Purchasing')
AND gl_je_journal_lines_v.set_of_books_id =
gl_sets_of_books.set_of_books_id
AND gl_sets_of_books.mrc_sob_type_code = 'P'
AND ( NVL (effective_period_num, 0) >= :cf_period_from
AND (NVL (effective_period_num, 0) <= :cf_period_to)
)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and gl_je_journal_lines_v.SET_OF_BOOKS_ID not in ( 1014,1015,1016 ) -- Added by RAVINDER on 4 Apr 07
AND gl_je_journal_lines_v.currency_code =decode(:p_currency_code,'INR','INR',
'ALL',gl_je_journal_lines_v.currency_code,
'NON INR',decode(gl_je_journal_lines_v.currency_code,'INR','###',gl_je_journal_lines_v.currency_code)) --Added by Jayshree.D on 10-Dec-10
UNION ALL
SELECT b.period_name, b.line_reference_2, a.po_order_number, a.rcv_receipt_num "Receipt Number",
a.trx_date "Receipt Date", a.trx_number_displayed "Invoice Number",
a.accounting_date "Invoice Date", a.third_party_name "Vendor Name",
a.third_party_number "Vendor Number", b.je_source "Source", b.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
a.currency_code, a.entered_dr, a.entered_cr, a.accounted_dr,
a.accounted_cr, a.je_header_id, a.je_line_num, a.code_combination_id,
a.doc_sequence_value "Vocher Number", a.gl_date "GL_Date",
a.attribute1 "Staff Number", a.attribute2 "Project",
a.attribute3 "Customer Num/Name", a.description "Description"
FROM (SELECT poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
rct.transaction_date trx_date, NULL trx_number_displayed,
rrs.accounting_date accounting_date,
pov.vendor_name third_party_name,
pov.segment1 third_party_number, rrs.currency_code,
rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
rrs.accounted_cr, r.je_header_id, r.je_line_num,
rrs.code_combination_id, 201 application_id,
TO_DATE (NULL) gl_date, TO_NUMBER (NULL) doc_sequence_value,
TO_CHAR (NULL) description, TO_CHAR (NULL) attribute1,
TO_CHAR (NULL) attribute2, TO_CHAR (NULL) attribute3
FROM gl_import_references r,
rcv_receiving_sub_ledger rrs,
po_distributions_all pod,
po_headers_all poh,
rcv_transactions rct,
rcv_shipment_headers rsh,
po_vendors pov
WHERE rrs.gl_sl_link_id = r.gl_sl_link_id
AND rct.transaction_id = rrs.rcv_transaction_id
AND rsh.shipment_header_id = rct.shipment_header_id
AND pod.po_distribution_id = rrs.reference3
AND poh.po_header_id = pod.po_header_id
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all
WHERE po_distribution_id = pod.po_distribution_id)
AND poh.vendor_id = pov.vendor_id) a,
gl_je_journal_lines_v b,
gl_summary_combinations_v c
WHERE b.je_header_id = a.je_header_id
AND b.line_je_line_num = a.je_line_num
AND b.line_code_combination_id = c.code_combination_id
AND a.code_combination_id = b.line_code_combination_id
AND b.actual_flag = 'A'
AND b.batch_status || '' = 'P'
AND b.currency_code != 'STAT'
AND ( NVL (b.effective_period_num, 0) >= :cf_period_from
AND (NVL (b.effective_period_num, 0) <= :cf_period_to)
)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and b.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND a.currency_code =decode(:p_currency_code,'INR','INR',
'ALL',a.currency_code,
'NON INR',decode(a.currency_code,'INR','###',a.currency_code))
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNION --Added by Saurabh on 06-Mar-08
SELECT period_name, gl_je_journal_lines_v.line_reference_2, TO_CHAR (NULL) po_order_number,
TO_CHAR (NULL) rcv_receipt_num, TO_DATE (NULL) trx_date,
TO_CHAR (NULL) trx_number_displayed, TO_DATE (NULL) accounting_date,
TO_CHAR (NULL) third_party_name, TO_CHAR (NULL) third_party_number,
je_source "Source",gl_je_journal_lines_v.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
gl_je_journal_lines_v.currency_code, line_entered_dr, line_entered_cr,
line_accounted_dr, line_accounted_cr,
JE_HEADER_ID,--TO_NUMBER (NULL) je_header_id,
LINE_JE_LINE_NUM,--TO_NUMBER (NULL) je_line_num,
line_code_combination_id,
DOC_SEQUENCE_VALUE,--TO_NUMBER (NULL) doc_sequence_value,
HEADER_EFFECTIVE_DATE,--TO_DATE (NULL) gl_date,
LINE_ATTRIBUTE1,--TO_CHAR (NULL) attribute1,
LINE_ATTRIBUTE2,--TO_CHAR (NULL) attribute2,
LINE_ATTRIBUTE3,--TO_CHAR (NULL) attribute3,
LINE_DESCRIPTION description--TO_CHAR (NULL) description
FROM gl_je_journal_lines_v , gl_summary_combinations_v, gl_sets_of_books
WHERE gl_summary_combinations_v.code_combination_id = gl_je_journal_lines_v.line_code_combination_id
AND actual_flag = 'A'
AND batch_status || '' = 'P'
AND gl_je_journal_lines_v.currency_code != 'STAT'
AND je_source = 'Purchasing'
AND gl_je_journal_lines_v.set_of_books_id =
gl_sets_of_books.set_of_books_id
AND gl_sets_of_books.mrc_sob_type_code = 'P'
AND ( NVL (effective_period_num, 0) >= :cf_period_from
AND (NVL (effective_period_num, 0) <= :cf_period_to)
)
--AND AID.ACCOUNTING_DATE between NVL (:P_Start_Date,sysdate) and NVL (:P_End_Date,sysdate)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and gl_je_journal_lines_v.je_header_id in (select distinct je_header_id from gl_je_batches_headers_v where batch_name like 'Reverses%')
and gl_je_journal_lines_v.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND gl_je_journal_lines_v.currency_code =decode(:p_currency_code,'INR','INR',
'ALL',gl_je_journal_lines_v.currency_code,
'NON INR',decode(gl_je_journal_lines_v.currency_code,'INR','###',gl_je_journal_lines_v.currency_code))

R12 Oracle Apps

Monday, July 2, 2012

11i GL Query

No comments:

Post a Comment