SELECT b.NAME je_batch_name,
b.description je_batch_description,
b.running_total_accounted_dr je_batch_total_dr,
b.running_total_accounted_cr je_batch_total_cr,
b.status je_batch_status,
b.default_effective_date je_batch_effective_date,
b.default_period_name je_batch_period_name,
b.creation_date je_batch_creation_date,
u.user_name je_batch_created_by,
h.je_category je_header_category,
h.je_source je_header_source,
h.period_name je_header_period_name,
h.NAME je_header_journal_name,
h.status je_header_journal_status,
h.creation_date je_header_created_date,
u1.user_name je_header_created_by,
h.description je_header_description,
h.running_total_accounted_dr je_header_total_acctd_dr,
h.running_total_accounted_cr je_header_total_acctd_cr,
l.je_line_num je_lines_line_number,
l.ledger_id je_lines_ledger_id,
glcc.concatenated_segments je_lines_ACCOUNT,
l.entered_dr je_lines_entered_dr,
l.entered_cr je_lines_entered_cr,
l.accounted_dr je_lines_accounted_dr,
l.accounted_cr je_lines_accounted_cr,
l.description je_lines_description,
glcc1.concatenated_segments xla_lines_account,
xlal.accounting_class_code xla_lines_acct_class_code,
xlal.accounted_dr xla_lines_accounted_dr,
xlal.accounted_cr xla_lines_accounted_cr,
xlal.description xla_lines_description,
xlal.accounting_date xla_lines_accounting_date,
xlate.entity_code xla_trx_entity_code,
xlate.source_id_int_1 xla_trx_source_id_int_1,
xlate.source_id_int_2 xla_trx_source_id_int_2,
xlate.source_id_int_3 xla_trx_source_id_int_3,
xlate.security_id_int_1 xla_trx_security_id_int_1,
xlate.security_id_int_2 xla_trx_security_id_int_2,
xlate.transaction_number xla_trx_transaction_number,
rcvt.transaction_type rcv_trx_transaction_type,
rcvt.transaction_date rcv_trx_transaction_date,
rcvt.quantity rcv_trx_quantity,
rcvt.shipment_header_id rcv_trx_shipment_header_id,
rcvt.shipment_line_id rcv_trx_shipment_line_id,
rcvt.destination_type_code rcv_trx_destination_type_code,
rcvt.po_header_id rcv_trx_po_header_id,
rcvt.po_line_id rcv_trx_po_line_id,
rcvt.po_line_location_id rcv_trx_po_line_location_id,
rcvt.po_distribution_id rcv_trx_po_distribution_id,
rcvt.vendor_id rcv_trx_vendor_id,
rcvt.vendor_site_id rcv_trx_vendor_site_id
FROM
gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
fnd_user u,
fnd_user u1,
gl_code_combinations_kfv glcc,
gl_code_combinations_kfv glcc1,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
rcv_transactions rcvt
WHERE
b.created_by = u.user_id
AND h.created_by = u1.user_id
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.application_id = xlah.application_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlal.code_combination_id = glcc1.code_combination_id
AND xlah.application_id = xlae.application_id
AND xlah.event_id = xlae.event_id
AND xlae.application_id = xlate.application_id
AND xlae.entity_id = xlate.entity_id
AND xlate.source_id_int_1 = rcvt.transaction_id
AND h.je_category = 'Receiving'
AND b.default_period_name = '01_APR-2009'
ORDER BY h.je_category;
======joins==
GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)
================
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)
SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number
SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;
=================
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)
SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number
SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;
====================
Query : Get Inventory Accounting Entries linked to GL
Query : Get Inventory Accounting Entries linked to GL
select mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1
=====================
Query : INVENTORY & ORDER MANAGEMENT
Query : INVENTORY & ORDER MANAGEMENT:
Just replace xxx in the query with your organization_id
SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1
=======================
Query : Purchase Order and Requisition
Query : Purchase Order and Requisition
SELECT prh.segment1 req_number
,prh.authorization_status
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,pd.req_header_reference_num
,pd.req_line_reference_num
,pl.line_num
,pl.item_description
,pl.quantity
,pl.amount
,ph.segment1 po_number
,prd.distribution_id
,pd.req_distribution_id
FROM po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_distributions_all pd
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.org_id = prd.org_id
and prd.distribution_id = pd.req_distribution_id(+)
and prd.org_id = pd.org_id(+)
and pd.line_location_id = pll.line_location_id(+)
and pd.org_id = pll.org_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pll.org_id = pl.org_id(+)
and pl.po_header_id = ph.po_header_id(+)
and pl.org_id = ph.org_id(+);
================
Query : Query to find receipts against a PO shipment line
Query : Query to find receipts against a PO shipment line
execute fnd_client_info.set_org_context('org_id');
SELECT
pol.po_header_id,
pol.po_line_id,
pll.line_location_id,
pll.quantity,
rsh. shipment_header_id,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0),
nvl(rct.source_doc_quantity,0) ) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_line_locations pll
where rct.po_line_location_id = 28302 --- check it out
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
order by rct.transaction_id
====================
Query : Sales Order Details After Pick Release
Query : Sales Order Details After Pick Release:
SELECT ooh.order_number,
ool.line_number,
wnd.delivery_id,
mtrh.request_number
FROM oe_order_headers ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
mtl_txn_request_lines mtrl,
mtl_txn_reuest_headers mtrh
WHERE ooh.order_number = --Order Number Input Parameter
AND ool.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
=================================
Query : Sales Order Hold & Release
Query : Sales Order Hold & Release:
SELECT OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
FLV1.MEANING RELEASE_REASON,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
ONT.OE_HOLD_RELEASES HR,
APPS.FND_LOOKUP_VALUES FLV1
WHERE HS.ORG_ID = '&ORG_ID' --- xxx
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID
AND FLV1.LOOKUP_TYPE = 'RELEASE_REASON'
AND FLV1.VIEW_APPLICATION_ID = 660
AND FLV1.LOOKUP_CODE = HR.RELEASE_REASON_CODE
========================
Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments
Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments
Below queries are for Oracle Applications Order Management and Advance Pricing Modules.
SELECT l.list_line_id,q.qualifier_grouping_no,
q.qualifier_id, q.qualifier_context, q.qualifier_attr_value,
q.comparison_operator_code,q.qualifier_precedence,q.qual_attr_value_from_number,
q.qualifier_attribute,q.end_date_active,l.end_date_active,h.end_date_active
FROM
qp_list_headers_all h,
qp_list_lines l,
qp_qualifiers q
where h.list_header_id = l.list_header_id
and h.list_header_id = q.list_header_id
and h.list_header_id = &list_id -- Price List Header ID or Modifier header ID
and NVL(h.end_date_active,sysdate) >= sysdate
and NVL(l.end_date_active,sysdate) >= sysdate
and NVL(q.end_date_active,sysdate) >= sysdate;
select q.qualifier_id,q.qualifier_context,q.qualifier_attribute,qualifier_attr_value ,
ct.prc_context_id, qs.segment_code
from qp_qualifiers q , qp_prc_contexts_b ct , qp_segments_b qs
where q.list_header_id = &ListHeaderID --PriceList Header ID
and ct.prc_context_type ='QUALIFIER'
and q.qualifier_context = ct.prc_context_code
and qs.prc_context_id = ct.prc_context_id
and qs.segment_mapping_column = q.qualifier_attribute
==========================
Query to Join Delivery Details and MTL Material Table
Query to Join Delivery Details and MTL Material Table
select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,
wdd.released_status, oe_interfaced_flag, inv_interfaced_flag,
wdd.creation_date, mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag
from wsh_delivery_details wdd,
mtl_material_transactions mtl,
oe_order_lines_all l
where
l.line_id = wdd.source_line_id
AND wdd.delivery_Detail_id = mtl.picking_line_id
AND wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.released_status = 'Y'
Also below is query to Join Order line with MTL_MATERIAL_TRANSACTIONS
select mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag , l.shipped_quantity
from
mtl_material_transactions mtl,
oe_order_lines_all l
where
mtl.trx_source_line_id = l.line_id
====================
Query to List all the responsibilities attached to a User
Query to List all the responsibilities attached to a User
Based on a request from one of the reader here is the query which he was looking for.
He needed query that can list all the responsibilities attached to a user.
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
=============
Query to check all the Picked Delivery Deatils where Reservation not exists
Query to check all the Picked Delivery Deatils where Reservation not exists
Select ool.line_id,
wdd.delivery_detail_id,wdd.source_line_id,wdd.ship_from_location_id,wdd.organi
zation_id,wdd.src_requested_quantity,
wdd.shipped_quantity,wdd.move_order_line_id,wdd.customer_item_id,wdd.split_fro
m_delivery_detail_id,
wdd.source_header_number,wdd.source_line_number,wdd.picked_quantity
from wsh_delivery_details wdd , oe_order_lines_all ool
where wdd.source_line_id = ool.line_id
and ool.open_flag ='Y'
and wdd.source_code ='OE'
and Picked_quantity > 0
and released_status= 'Y'
and ool.header_id = &enter_Sales_Order_Header_id
and not exists
(
select 1 from mtl_reservations mr
where mr.demand_source_line_id = wdd.source_line_id
and mr.inventory_item_id = wdd.inventory_item_id)
Above sample script is with Order_Header_id as a Parameter. If you want to check for all the Orders , then remove the parameter Condition.
==============================
b.description je_batch_description,
b.running_total_accounted_dr je_batch_total_dr,
b.running_total_accounted_cr je_batch_total_cr,
b.status je_batch_status,
b.default_effective_date je_batch_effective_date,
b.default_period_name je_batch_period_name,
b.creation_date je_batch_creation_date,
u.user_name je_batch_created_by,
h.je_category je_header_category,
h.je_source je_header_source,
h.period_name je_header_period_name,
h.NAME je_header_journal_name,
h.status je_header_journal_status,
h.creation_date je_header_created_date,
u1.user_name je_header_created_by,
h.description je_header_description,
h.running_total_accounted_dr je_header_total_acctd_dr,
h.running_total_accounted_cr je_header_total_acctd_cr,
l.je_line_num je_lines_line_number,
l.ledger_id je_lines_ledger_id,
glcc.concatenated_segments je_lines_ACCOUNT,
l.entered_dr je_lines_entered_dr,
l.entered_cr je_lines_entered_cr,
l.accounted_dr je_lines_accounted_dr,
l.accounted_cr je_lines_accounted_cr,
l.description je_lines_description,
glcc1.concatenated_segments xla_lines_account,
xlal.accounting_class_code xla_lines_acct_class_code,
xlal.accounted_dr xla_lines_accounted_dr,
xlal.accounted_cr xla_lines_accounted_cr,
xlal.description xla_lines_description,
xlal.accounting_date xla_lines_accounting_date,
xlate.entity_code xla_trx_entity_code,
xlate.source_id_int_1 xla_trx_source_id_int_1,
xlate.source_id_int_2 xla_trx_source_id_int_2,
xlate.source_id_int_3 xla_trx_source_id_int_3,
xlate.security_id_int_1 xla_trx_security_id_int_1,
xlate.security_id_int_2 xla_trx_security_id_int_2,
xlate.transaction_number xla_trx_transaction_number,
rcvt.transaction_type rcv_trx_transaction_type,
rcvt.transaction_date rcv_trx_transaction_date,
rcvt.quantity rcv_trx_quantity,
rcvt.shipment_header_id rcv_trx_shipment_header_id,
rcvt.shipment_line_id rcv_trx_shipment_line_id,
rcvt.destination_type_code rcv_trx_destination_type_code,
rcvt.po_header_id rcv_trx_po_header_id,
rcvt.po_line_id rcv_trx_po_line_id,
rcvt.po_line_location_id rcv_trx_po_line_location_id,
rcvt.po_distribution_id rcv_trx_po_distribution_id,
rcvt.vendor_id rcv_trx_vendor_id,
rcvt.vendor_site_id rcv_trx_vendor_site_id
FROM
gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
fnd_user u,
fnd_user u1,
gl_code_combinations_kfv glcc,
gl_code_combinations_kfv glcc1,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
rcv_transactions rcvt
WHERE
b.created_by = u.user_id
AND h.created_by = u1.user_id
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.application_id = xlah.application_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlal.code_combination_id = glcc1.code_combination_id
AND xlah.application_id = xlae.application_id
AND xlah.event_id = xlae.event_id
AND xlae.application_id = xlate.application_id
AND xlae.entity_id = xlate.entity_id
AND xlate.source_id_int_1 = rcvt.transaction_id
AND h.je_category = 'Receiving'
AND b.default_period_name = '01_APR-2009'
ORDER BY h.je_category;
======joins==
GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)
================
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)
SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number
SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;
=================
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL
SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)
SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number
SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;
====================
Query : Get Inventory Accounting Entries linked to GL
Query : Get Inventory Accounting Entries linked to GL
select mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1
=====================
Query : INVENTORY & ORDER MANAGEMENT
Query : INVENTORY & ORDER MANAGEMENT:
Just replace xxx in the query with your organization_id
SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1
=======================
Query : Purchase Order and Requisition
Query : Purchase Order and Requisition
SELECT prh.segment1 req_number
,prh.authorization_status
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,pd.req_header_reference_num
,pd.req_line_reference_num
,pl.line_num
,pl.item_description
,pl.quantity
,pl.amount
,ph.segment1 po_number
,prd.distribution_id
,pd.req_distribution_id
FROM po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_distributions_all pd
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.org_id = prd.org_id
and prd.distribution_id = pd.req_distribution_id(+)
and prd.org_id = pd.org_id(+)
and pd.line_location_id = pll.line_location_id(+)
and pd.org_id = pll.org_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pll.org_id = pl.org_id(+)
and pl.po_header_id = ph.po_header_id(+)
and pl.org_id = ph.org_id(+);
================
Query : Query to find receipts against a PO shipment line
Query : Query to find receipts against a PO shipment line
execute fnd_client_info.set_org_context('org_id');
SELECT
pol.po_header_id,
pol.po_line_id,
pll.line_location_id,
pll.quantity,
rsh. shipment_header_id,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0),
nvl(rct.source_doc_quantity,0) ) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_line_locations pll
where rct.po_line_location_id = 28302 --- check it out
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
order by rct.transaction_id
====================
Query : Sales Order Details After Pick Release
Query : Sales Order Details After Pick Release:
SELECT ooh.order_number,
ool.line_number,
wnd.delivery_id,
mtrh.request_number
FROM oe_order_headers ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
mtl_txn_request_lines mtrl,
mtl_txn_reuest_headers mtrh
WHERE ooh.order_number = --Order Number Input Parameter
AND ool.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
=================================
Query : Sales Order Hold & Release
Query : Sales Order Hold & Release:
SELECT OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
FLV1.MEANING RELEASE_REASON,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
ONT.OE_HOLD_RELEASES HR,
APPS.FND_LOOKUP_VALUES FLV1
WHERE HS.ORG_ID = '&ORG_ID' --- xxx
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID
AND FLV1.LOOKUP_TYPE = 'RELEASE_REASON'
AND FLV1.VIEW_APPLICATION_ID = 660
AND FLV1.LOOKUP_CODE = HR.RELEASE_REASON_CODE
========================
Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments
Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments
Below queries are for Oracle Applications Order Management and Advance Pricing Modules.
SELECT l.list_line_id,q.qualifier_grouping_no,
q.qualifier_id, q.qualifier_context, q.qualifier_attr_value,
q.comparison_operator_code,q.qualifier_precedence,q.qual_attr_value_from_number,
q.qualifier_attribute,q.end_date_active,l.end_date_active,h.end_date_active
FROM
qp_list_headers_all h,
qp_list_lines l,
qp_qualifiers q
where h.list_header_id = l.list_header_id
and h.list_header_id = q.list_header_id
and h.list_header_id = &list_id -- Price List Header ID or Modifier header ID
and NVL(h.end_date_active,sysdate) >= sysdate
and NVL(l.end_date_active,sysdate) >= sysdate
and NVL(q.end_date_active,sysdate) >= sysdate;
select q.qualifier_id,q.qualifier_context,q.qualifier_attribute,qualifier_attr_value ,
ct.prc_context_id, qs.segment_code
from qp_qualifiers q , qp_prc_contexts_b ct , qp_segments_b qs
where q.list_header_id = &ListHeaderID --PriceList Header ID
and ct.prc_context_type ='QUALIFIER'
and q.qualifier_context = ct.prc_context_code
and qs.prc_context_id = ct.prc_context_id
and qs.segment_mapping_column = q.qualifier_attribute
==========================
Query to Join Delivery Details and MTL Material Table
Query to Join Delivery Details and MTL Material Table
select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,
wdd.released_status, oe_interfaced_flag, inv_interfaced_flag,
wdd.creation_date, mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag
from wsh_delivery_details wdd,
mtl_material_transactions mtl,
oe_order_lines_all l
where
l.line_id = wdd.source_line_id
AND wdd.delivery_Detail_id = mtl.picking_line_id
AND wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.released_status = 'Y'
Also below is query to Join Order line with MTL_MATERIAL_TRANSACTIONS
select mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag , l.shipped_quantity
from
mtl_material_transactions mtl,
oe_order_lines_all l
where
mtl.trx_source_line_id = l.line_id
====================
Query to List all the responsibilities attached to a User
Query to List all the responsibilities attached to a User
Based on a request from one of the reader here is the query which he was looking for.
He needed query that can list all the responsibilities attached to a user.
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
=============
Query to check all the Picked Delivery Deatils where Reservation not exists
Query to check all the Picked Delivery Deatils where Reservation not exists
Select ool.line_id,
wdd.delivery_detail_id,wdd.source_line_id,wdd.ship_from_location_id,wdd.organi
zation_id,wdd.src_requested_quantity,
wdd.shipped_quantity,wdd.move_order_line_id,wdd.customer_item_id,wdd.split_fro
m_delivery_detail_id,
wdd.source_header_number,wdd.source_line_number,wdd.picked_quantity
from wsh_delivery_details wdd , oe_order_lines_all ool
where wdd.source_line_id = ool.line_id
and ool.open_flag ='Y'
and wdd.source_code ='OE'
and Picked_quantity > 0
and released_status= 'Y'
and ool.header_id = &enter_Sales_Order_Header_id
and not exists
(
select 1 from mtl_reservations mr
where mr.demand_source_line_id = wdd.source_line_id
and mr.inventory_item_id = wdd.inventory_item_id)
Above sample script is with Order_Header_id as a Parameter. If you want to check for all the Orders , then remove the parameter Condition.
==============================
No comments:
Post a Comment