InPage ki Urdu File ko PDF File mein Tabdeel Karein
Windows 7 Performance Steps
FND Queries
AR/AP/INV/PO Queries
APPS TABLES WITH LINKSOracle Apps Receivables (AR) Tables
Oracle Receivables Table and Column Descriptions
Oracle Apps technical Queries [AP, PO, INV]
Change Organization Lov Query
Oracle Apps Queries
Query to Select Prepayment Invoice Status in Oracle Payables
Script to get AP Invoice Aging for Prepayment(ADVANCES)
Query to check whether periods
Purchase Order Useful Queries
Query to display PO Details which don't have Invoices in Oracle apps R12
PO: Tips and useful Query
Query To Get PO Number, Invoice Number and Receipt Number With one of the Input
SQL Query to Calculate Total PO Amount
SQL Query to pull invoice matched against a PO and Non PO Invoice?
Receipts with Matched or Unmatched Invoices
PO - Oracle Standard Procedure - Amount
Receiving Transactions
Oracle Apps - Supply Chain Management (SCM)11i
R12: PO to Payment Query
Base tables for Purchase Order (PO) in Oracle Apps
Link between PO_HEADERS_ALL AND RCV_SHIPMENT_HEADERS (PO and Receipts)
R12 Vendor/Supplier Bank Details Query
R12 Vendor/Supplier Bank Details Query
AP Supplier Bank Info Query
R12 Supplier Bank Accounts
How to find Supplier and Bank Details in Oracle Apps R12
R12 Bank Accounts- Supplier and Customer
AP to Bank
Subledger Accounting Setup in EBS R12 (Very Important)
Oracle Apps R12 Subledger Accounting Tables and joins
Path in Oracle EBS R12
Subledger Accounting Setup -> Accounting Method Builder -> Events -> Event Models
Path in Oracle EBS R12
Subledger Accounting Setup -> Accounting Method Builder -> Events -> Event Models
XLA TABLES
Drilldown from GL to Receiving TransactionsR12 Mapping Between Subledger Tables, SLA and GL Tables
PO TO GL LINK IN R12 QUERY FOR PO TO GL
R12 – SQL: PO-XLA-GL
SQL Query for Link Between PO-RCV-XLA-AP
Category: XLA Dist Link to Transaction Table
XLA tables and AP Invoices tables
ORACLE APPS Queries [PAYABLES Queries]
R12 - GL / XLA / FAH - How to link GL data to the subledger data or vice versa
Subledger Accounting (SLA) in R12
Xla distribution links Source Distribution Types
How and When Sub-Ledger Data is populated into XLA_DISTRIBUTION_LINKS Table
Link ap_invoice_distributions_all to a GL Header
How to join (ap invoice distributions all) AP table info with PO Table
AP --> Subledger Accounting (XLA) --> GL --> PA --> FA Query Links
Link between AP and GL in R/12
R12 Mapping Between Subledger Tables, SLA and GL Tables
SQL: AP Distribution -> GL
AP Invoice Technical Details with Functional Inputs
XLA tables and Payments tables
Payment data in Sub-Ledger Accounting (XLA) - R12
Query to Link AP Invoices, Payments, GL and SLA Tables: R12 AP GL XLA
SELECT *
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all ac,
xla.xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjhWHERE 1 = 1
--
AND aipa.invoice_id = aia.invoice_id
AND ac.check_id = aipa.check_id
--
AND ac.check_id = 1508865
AND ac.check_id = xte.source_id_int_1
AND xte.application_id = xah.application_id
AND xte.application_id = 200
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.entity_id = xah.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.balance_type_code = 'A'
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
XLA tables and purchasing TABLES
Financial Services Accounting Hub Repository Data ModelR12 Mapping Between Subledger Tables, SLA and GL Tables
PO TO GL LINK IN R12 QUERY FOR PO TO GL
R12 – SQL: PO-XLA-GL
SQL Query for Link Between PO-RCV-XLA-AP
XLA tables Drilldown from General Ledger
SELECT je_header_id, ae_header_id, journal_name, default_effective_date,
je_source, je_category, ledger_id, ledger_name, doc_sequence_id,
doc_sequence_value, subledger_doc_sequence_id,
subledger_doc_sequence_value, journal_currency_code, ledger_currency,
application_id, application_name, period_set_name, period_num,
period_name, posted_date, je_header_name, je_batch_name, line_num,
accounted_dr, accounted_cr, code_combination_id, expense_account,
ACCOUNT, account_description, accounting_class_code, entity_code,
transaction_number, source_id_int_1, report_seqval_transaction,
vendor_id_with, vendor_number_with, vendor_name_with, devision,
directorate, department, section, user_name
FROM (WITH vendor_info_cash AS
(SELECT /*+ index(rt RCV_TRANSACTIONS_N2)*/
rt.transaction_id, rsh.receipt_num, rsh.vendor_id,
segment1 vendor_number, vendor_name
FROM po.rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pv
WHERE rsh.shipment_header_id = rt.shipment_header_id
AND rt.transaction_type = 'DELIVER'
AND pv.vendor_id(+) = rt.vendor_id),
vendor_info_invoice AS
(SELECT /*+ index(ap AP_INVOICES_U1)*/
ap.invoice_id, asp.vendor_id, asp.segment1 vendor_number,
asp.vendor_name
FROM ap.ap_invoices_all ap, ap_suppliers asp
WHERE ap.vendor_id = asp.vendor_id)
SELECT je_header_id, ae_header_id, journal_name,
default_effective_date, je_source, je_category, ledger_id,
ledger_name, doc_sequence_id, doc_sequence_value,
subledger_doc_sequence_id, subledger_doc_sequence_value,
journal_currency_code, ledger_currency, application_id,
application_name, period_set_name, period_num, period_name,
posted_date, je_header_name, je_batch_name, line_num,
accounted_dr, accounted_cr, code_combination_id,
expense_account, ACCOUNT, account_description,
--------------------------(Disable due to low Performance)
-- fnd_flex_ext.get_segs ('SQLGL',
-- 'GL#',
-- chart_of_accounts_id,
-- code_combination_id
-- ) ACCOUNT,
-- xla_oa_functions_pkg.get_ccid_description
-- (chart_of_accounts_id,
-- code_combination_id
-- ) account_description,
--------------------------(End Disable due to low Performance)
accounting_class_code, entity_code, transaction_number,
source_id_int_1,
---(If je_source = 'Cost Management' then Display Recipt Num with Status = DELIVER')
CASE
WHEN je_source =
'Cost Management'
THEN NVL
((SELECT receipt_num
FROM vendor_info_cash
WHERE transaction_id = source_id_int_1
AND ROWNUM = 1),
report_seqval_transaction
)
ELSE report_seqval_transaction
END report_seqval_transaction,
-----(Display the Vendor id )------------------
CASE
WHEN je_source = 'Cost Management'
THEN NVL ((SELECT vendor_id
FROM vendor_info_cash
WHERE transaction_id = source_id_int_1
AND ROWNUM = 1),
NULL
)
WHEN je_source = 'Payables'
THEN NVL ((SELECT vendor_id
FROM vendor_info_invoice
WHERE invoice_id = source_id_int_1), NULL)
END vendor_id_with,
-----(Display the Vendor number)------------------
CASE
WHEN je_source = 'Cost Management'
THEN NVL
((SELECT vendor_number
FROM vendor_info_cash
WHERE transaction_id = source_id_int_1
AND ROWNUM = 1),
NULL
)
WHEN je_source = 'Payables'
THEN NVL ((SELECT vendor_number
FROM vendor_info_invoice
WHERE invoice_id = source_id_int_1), NULL)
END vendor_number_with,
-----(Display the Vendor name)------------------
CASE
WHEN je_source = 'Cost Management'
THEN NVL ((SELECT vendor_name
FROM vendor_info_cash
WHERE transaction_id = source_id_int_1
AND ROWNUM = 1),
NULL
)
WHEN je_source = 'Payables'
THEN NVL ((SELECT vendor_name
FROM vendor_info_invoice
WHERE invoice_id = source_id_int_1), NULL)
END vendor_name_with,
------------------------------
user_name
FROM (
-------------(Start of Part - 1) Query to Fetch records Query to Fetch records for those are not link with Sub Ledger)--------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gjh.je_header_id, NULL ae_header_id,
gjh.NAME journal_name, gjh.default_effective_date,
gjh.je_source, gjh.je_category, gjh.ledger_id,
gled.NAME ledger_name, gjh.doc_sequence_id,
gjh.doc_sequence_value, NULL subledger_doc_sequence_id,
NULL subledger_doc_sequence_value,
gjh.currency_code journal_currency_code,
gled.currency_code ledger_currency,
NULL application_id, NULL application_name,
gp.period_set_name, gp.period_num, gjh.period_name,
gjh.posted_date, gjh.NAME je_header_name,
gjb.NAME je_batch_name, gjl.je_line_num line_num,
gjl.accounted_dr, gjl.accounted_cr,
gjl.code_combination_id, gcc.segment4 expense_account,
gcc.concatenated_segments ACCOUNT,
NULL account_description, NULL accounting_class_code,
NULL entity_code, NULL transaction_number,
NULL source_id_int_1,
TO_CHAR
(gjh.doc_sequence_value)
report_seqval_transaction,
ffv2.hierarchy_level, ffv2.description, gcc.segment2,
:p_username user_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc,
fnd_flex_values_vl ffv2
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc.code_combination_id
-------------(Link with fnd_flex_values_vl ffv2 and gl_code_combinations_kfv gcc)(for Division, Directate, Department and Section)
AND gcc.segment2 = ffv2.flex_value
AND ffv2.flex_value_set_id = 1016203
AND ffv2.flex_value != '0000'
---------------------------------------------------------------------------
-- (Parameters on Reports)-----
---------------------------------------------------------------------------
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = :p_ledger_id
-------------(Condition of Sub Ledger Accounting)
-------------(Condition based on DIVISION, DEPARTMENT, DIRECTORATE, SECTION) (Division (Mandotory), Department, Directorate, Section (Optional)
AND ( gcc.segment2 IN (SELECT section
FROM bein_cost_center_hierarchy
WHERE division = :p_devision)
OR :p_devision IS NULL
)
AND ( gcc.segment2 IN (SELECT section
FROM bein_cost_center_hierarchy
WHERE department = :p_department)
OR :p_department IS NULL
)
AND ( gcc.segment2 IN (SELECT section
FROM bein_cost_center_hierarchy
WHERE directorate =
:p_directorate)
OR :p_directorate IS NULL
)
AND (gcc.segment2 = :p_section OR :p_section IS NULL)
-------------(segment4 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND ( TO_NUMBER (gcc.segment4) BETWEEN :p_from_account
AND :p_to_account
OR :p_from_account IS NULL
OR :p_to_account IS NULL
)
-------------(Condition of Period - Mandatory)-----
AND ( ( (gp.period_num BETWEEN 1
AND (SELECT gp.period_num
FROM gl_periods gp,
gl_ledgers gl
WHERE gp.period_set_name =
gl.period_set_name
AND gp.period_name =
:p_period_name
AND gl.ledger_id =
:p_ledger_id
AND ROWNUM = 1)
)
AND :p_period_type = 'YTD'
)
OR ( (gjh.period_name = :p_period_name)
AND :p_period_type = 'PTD'
)
)
----------------------(End of Part - 1)-------------------------------------------------------------------------------------
UNION
-------------(Start of Part - 2) Query to Fetch records for those are link with Sub Ledger)---------------------------------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) INDEX (xah XLA_AE_HEADERS_U1) INDEX(xal XLA_AE_LINES_U1) */
gjh.je_header_id, xah.ae_header_id,
gjh.NAME journal_name, gjh.default_effective_date,
gjh.je_source, gjh.je_category, gjh.ledger_id,
gled.NAME ledger_name, gjh.doc_sequence_id,
gjh.doc_sequence_value, ir.subledger_doc_sequence_id,
ir.subledger_doc_sequence_value,
gjh.currency_code journal_currency_code,
gled.currency_code ledger_currency, fav.application_id,
fav.application_name, gp.period_set_name,
gp.period_num, gjh.period_name, gjh.posted_date,
gjh.NAME je_header_name, gjb.NAME je_batch_name,
xal.ae_line_num line_num, xal.accounted_dr,
xal.accounted_cr, xal.code_combination_id,
gcc.segment4 expense_account,
gcc.concatenated_segments ACCOUNT,
NULL account_description, xal.accounting_class_code,
xte.entity_code, xte.transaction_number,
xte.source_id_int_1,
NVL (xte.transaction_number,
xte.source_id_int_1
) report_seqval_transaction,
ffv2.hierarchy_level, ffv2.description, gcc.segment2,
:p_username user_name
FROM gl_je_headers gjh,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_je_lines gjl,
gl_code_combinations_kfv gcc,
fnd_flex_values_vl ffv2,
gl_import_references ir,
fnd_application_vl fav,
xla_subledgers_fvl sub_led,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
xla.xla_events xe,
xla.xla_transaction_entities xte
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc.code_combination_id
-------------(Link with fnd_flex_values_vl ffv2 and gl_code_combinations_kfv gcc)(for Division, Directate, Department and Section)
AND gcc.segment2 = ffv2.flex_value
AND ffv2.flex_value_set_id = 1016203
AND ffv2.flex_value != '0000'
-------------------------------(SUB LEDGER LINKS WITH XLA TABLES)--------------------
----------------------(link between gl_import_references, gl_je_lines)
AND ir.je_header_id = gjl.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between fnd_application and xal_ae_headers)
AND fav.application_id = xah.application_id
-------------(link between xla_subledgers_fvl and fnd_application)
AND fav.application_id = sub_led.application_id
-------------(link between xla_subledgers_fvl and xla_ae_headers)
AND sub_led.application_id = xah.application_id
-------------(link between xla_subledgers_fvl and Jl_je_headers)
AND sub_led.je_source_name = gjh.je_source
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xte.entity_id = xe.entity_id
AND xte.application_id = xe.application_id
---------------------------------------------------------------------------
-- (Parameters on Reports)-----
---------------------------------------------------------------------------
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = :p_ledger_id
-------------(Condition of sub_led.application_id - Optional)-------------
AND sub_led.application_id =
NVL (:p_subledger_id, sub_led.application_id)
-------------(Condition of Sub Ledger Accounting)
-------------(Condition based on DIVISION, DEPARTMENT, DIRECTORATE, SECTION) (Division (Mandotory), Department, Directorate, Section (Optional)
AND ( gcc.segment2 IN (SELECT section
FROM bein_cost_center_hierarchy
WHERE division = :p_devision)
OR :p_devision IS NULL
)
AND ( gcc.segment2 IN (SELECT section
FROM bein_cost_center_hierarchy
WHERE department = :p_department)
OR :p_department IS NULL
)
AND ( gcc.segment2 IN (SELECT section
FROM bein_cost_center_hierarchy
WHERE directorate =
:p_directorate)
OR :p_directorate IS NULL
)
AND (gcc.segment2 = :p_section OR :p_section IS NULL)
-------------(segment4 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND ( TO_NUMBER (gcc.segment4) BETWEEN :p_from_account
AND :p_to_account
OR :p_from_account IS NULL
OR :p_to_account IS NULL
)
-------------(Condition of Period - Mandatory)-----
AND ( ( (gp.period_num BETWEEN 1
AND (SELECT gp.period_num
FROM gl_periods gp,
gl_ledgers gl
WHERE gp.period_set_name =
gl.period_set_name
AND gp.period_name =
:p_period_name
AND gl.ledger_id =
:p_ledger_id
AND ROWNUM = 1)
)
AND :p_period_type = 'YTD'
)
OR ( (gjh.period_name = :p_period_name)
AND :p_period_type = 'PTD'
)
)
-------------(End of Part - 2)-------------------------------------------------------------------------------------
) q_bein_gl_drilldown /*union end*/) /* view end*/
--WHERE je_header_id = :1
-- AND je_line_num = :2
--------------------------------------(End of Query - Q_BEIN_GL_DRILLDOWN)--------------------------
XLA Query with Payables
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gcc.concatenated_segments gl_account, gjh.default_effective_date,
je_source, gjh.je_category,
---
(SELECT xte.source_id_int_1
FROM gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
WHERE 1 = 1
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
------------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND ROWNUM = 1) source_id_int_1,
---
(SELECT xte.transaction_number
FROM gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
WHERE 1 = 1
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
------------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND ROWNUM = 1) transaction_number,
--
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gled.ledger_id, gled.NAME ledger_name, gjh.je_header_id,
gjl.je_line_num, gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND ( ( (gp.period_num BETWEEN 1
AND (SELECT gp.period_num
FROM gl_periods gp, gl_ledgers gl
WHERE gp.period_set_name =
gl.period_set_name
AND gp.period_name = :p_period_name
AND gl.ledger_id = gjh.ledger_id
AND ROWNUM = 1)
)
AND :p_period_type = 'YTD'
)
OR ((gjh.period_name = :p_period_name) AND :p_period_type = 'PTD')
)
gcc.concatenated_segments gl_account, gjh.default_effective_date,
je_source, gjh.je_category,
---
(SELECT xte.source_id_int_1
FROM gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
WHERE 1 = 1
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
------------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND ROWNUM = 1) source_id_int_1,
---
(SELECT xte.transaction_number
FROM gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
WHERE 1 = 1
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
------------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND ROWNUM = 1) transaction_number,
--
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gled.ledger_id, gled.NAME ledger_name, gjh.je_header_id,
gjl.je_line_num, gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND ( ( (gp.period_num BETWEEN 1
AND (SELECT gp.period_num
FROM gl_periods gp, gl_ledgers gl
WHERE gp.period_set_name =
gl.period_set_name
AND gp.period_name = :p_period_name
AND gl.ledger_id = gjh.ledger_id
AND ROWNUM = 1)
)
AND :p_period_type = 'YTD'
)
OR ((gjh.period_name = :p_period_name) AND :p_period_type = 'PTD')
)
TAX Queries
select
s.segment1 "Supplier Number",
hps.party_site_name "Address Name",
ass.vendor_site_code "Site Name",
zr.tax_regime_code "Tax Regime Code",
zr.tax "Tax",
zr.tax_jurisdiction_code "Tax Jurisdiction Code",
zr.rep_party_tax_name "Company Reporting Name",
zr.registration_type_code "Tax Registration Type",
zr.registration_number "Tax Registration Number",
zr.registration_status_code "Tax Registration Status",
zr.default_registration_flag "Default Registration",
zr.legal_location_id "Legal Registration Address",
zr.registration_reason_code "Tax Registration Reason",
zr.tax_classification_code "Tax Classification",
zr.registration_source_code "Source",
hp_i.party_name "Issuing Tax Authority",
zr.effective_from "Effective From",
zr.effective_to "Effective To",
--
s.vendor_id,
ass.vendor_site_id,
s.party_id,
hps.party_site_id,
zptp.party_tax_profile_id,
zr.registration_id
from
ap_suppliers s,
ap_supplier_sites ass,
hz_party_sites hps,
zx_party_tax_profile zptp,
zx_registrations zr,
hz_parties hp_i,
zx_party_tax_profile zptp_i
where
1=1
and ass.vendor_id = s.vendor_id
and hps.party_id = s.party_id
and hps.party_site_id = ass.party_site_id
and zptp.party_id = hps.party_site_id
and zptp.party_type_code = 'THIRD_PARTY_SITE'
and zr.party_tax_profile_id = zptp.party_tax_profile_id
and zptp_i.party_tax_profile_id (+) = zr.tax_authority_id
and hp_i.party_id (+) = zptp_i.party_id
--
and s.segment1 = '1008'
order by
1,2,3,4
