Order
header info
Select
* from oe_order_headers_all ooh
where
order_number= :p_order_number
Operating
unit info
select
* from hr_operating_units
where
organization_id=oe_order_headers_all.org_id
Order
type info
select
* from apps.oe_transaction_types_tl
where
transaction_type_id=oe_order_headers_all.order_type_id
Price
list info
select
* from apps.qp_list_headers_tl
where
list_header_id=oe_order_headers_all.price_list_id
select
* from apps.qp_list_lines
where
list_header_id=oe_order_headers_all.price_list_id
Find
customer info
select
* from hz_cust_accounts hca
where
cust_account_id=oe_order_headers_all.sold_to_org_id
select
* from apps.hz_parties
where
party_id=hz_cust_accounts.party_id
Find
Ship to location info
select
* from hz_cust_site_uses_all
where
site_use_id=oe_order_headers_all.ship_to_org_id
select
* from apps.hz_cust_acct_sites_all
where
cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id
select
* from hz_party_sites
where
party_site_id=hz_cust_acct_sites_all.party_site_id
Find Bill
to location
select
* from hz_cust_site_uses_all
where
site_use_id=oe_order_headers_all.invoice_to_org_id
select
* from hz_cust_acct_sites_all
where
cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id
select
* from hz_party_sites
where
party_site_id=hz_cust_acct_sites_all.party_site_id
actual
address
select
* from hz_locations
where
location_id=hz_party_sites.location_id
Sales
rep id
select
name from apps.ra_salesreps_all salerep where
salesrep_id
= oe_order_headers_all.salesrep_id and rownum =1
Payment
terms
select
name from apps.ra_terms_tl
where
term_id =oe_order_headers_all.payment_term_id
and
language = 'US'
Order
source
select
name from apps.oe_order_sources
where
order_source_id= oe_order_headers_all.order_source_id
and
enabled_flag= 'Y'
Order
Source Reference
select
orig_sys_document_ref from oe_order_headers_all ooh
where
order_number='&oracle order number'
FOB
Point Code
select
lookup_code from ar_lookups
where
lookup_type = 'FOB' and enabled_flag = 'Y'
and
upper(meaning) = upper(oe_order_headers_all.fob_point_code)
Freight
terms
select
lookup_code from apps.oe_lookups
where
upper (lookup_type) = 'FREIGHT_TERMS' and enabled_flag = 'Y'
and
upper (lookup_code) = upper (oe_order_headers_all.freight_terms_code)
For
sales channel code validation
select
lookup_code from apps.oe_lookups
where
lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y'
upper(lookup_code)
= upper(oe_order_headers_all.sales_channel_code)
Ship
method
select
ship_method_code from wsh.wsh_carrier_services
where
ship_method_code = oe_order_headers_all.shipping_method_code
Warehouse
Info
select
* from org_organization_definitions
where
organization_id = oe_order_headers_all.ship_from_org_id
Sales
order Lines Details
select
* from apps.oe_order_lines_all
where
header_id=oe_order_headers_all.header_id
Transactional
currency code
select
ota.price_list_id, qhb.currency_code
from
ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb
where
ota.transaction_type_id = oe_order_headers_all.order_type_id
and
ota.price_list_id = qhb.list_header_id(+)
and
NVL(qhb.list_type_code, 'PRL') = 'PRL'
and
qhb.currency_code =oe_order_headers_all.transactional_curr_code
Item
info
select
* from apps.mtl_system_items_b
where
segment1 like oe_order_lines_all.ordered_item
and
organization_id=oe_order_lines_all.ship_from_org_id
UOM
select
uom_code from inv.mtl_units_of_measure_tl
where
upper(uom_code)= upper(oe_order_lines_all.order_quantity_uom)
and
language= 'US' and nvl(disable_date, (sysdate + 1)) > sysdate
Item type
code validation
select
lookup_code from apps.oe_lookups
where
upper(lookup_type) = 'ITEM_TYPE'
and
enabled_flag = 'Y'
and
upper(lookup_code)= oe_order_lines_all.item_type_code
On hand
quantities
select
* from apps.mtl_onhand_quantities
where
inventory_item_id=oe_order_lines_all.inventory_item_id
and
organization_id=oe_order_lines_all.ship_from_org_id
Shipping
select
* from wsh_delivery_details
where
source_header_id=oe_order_headers_all.header_id
select
* from wsh_delivery_assignments
where
delivery_detail_id=wsh_delivery_details.delivery_detail_id
select
* from wsh_new_deliveries
where
delivery_id=wsh_delivery_assignments.delivery_id
select
* from wsh_delivery_legs
where
delivery_id=wsh_new_deliveries.delivery_id
select
* from wsh_trip_stops wts
where
stop_id=wsh_delivery_legs.pick_up_stop_id
select
* from wsh_trips wt
where
trip_id=wsh_trip_stops.trip_id
select
* from org_organization_definitions
where
organization_id = wsh_new_deliveries.organization_id
Material
transactions
select
* from mtl_material_transactions
where
inventory_item_id=oe_order_lines_all.inventory_item_id
and
organization_id=oe_order_lines_all.ship_from_org_id
select
* from mtl_transaction_types
where
transaction_type_id = mmt.transaction_type_id
select
* from apps.mtl_txn_source_types
where
transaction_source_type_id= mmt.transaction_source_type_id
mmt =
mtl_material_transactions
Join between OM, WSH, AR Tables
SELECT ooh.order_number
Join between OM, WSH, AR Tables
SELECT ooh.order_number
,ool.line_id
,ool.ordered_quantity
,ool.shipped_quantity
,ool.invoiced_quantity
,wdd.delivery_detail_id
,wnd.delivery_id
,rctl.interface_line_attribute1
,rctl.interface_line_attribute3
,rctl.interface_line_attribute6
,rct.org_id
,rct.creation_date
,trx_number
,rctl.quantity_ordered
,rct.interface_header_context
FROM oe_order_headers_all ooh
,oe_order_lines_all ool
,wsh_delivery_details wdd
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,ra_customer_trx_all rct
,ra_customer_trx_lines_all rctl
WHERE
ooh.header_Id=ool.header_id
AND wdd.source_header_id=ooh.header_id
AND wdd.delivery_detail_Id=wda.delivery_detail_id
AND wda.delivery_id=wnd.delivery_id
AND rctl.interface_line_attribute1=to_char(ooh.order_number)
AND rctl.interface_line_attribute6=to_char(ool.line_id)
AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
AND rctl.customer_trx_id=rct.customer_trx_id
AND
rct.interface_header_context='ORDER ENTRY'
Purchase
release concurrent program will transfer the details from OM to PO requisitions
interface. The following query will verify the same:
SELECT
interface_source_code,
interface_source_line_id,
quantity,
destination_type_code,
transaction_id,
process_flag,
request_id,
TRUNC (creation_date)
FROM
po_requisitions_interface_all
WHERE
interface_source_code = 'ORDER ENTRY'
AND
interface_source_line_id IN (SELECT drop_ship_source_id
FROM oe_drop_ship_sources
WHERE header_id = &order_hdr_id
AND line_id = &order_line_id);
The following sql is used to review the requisition, sales order,
and receipt number. It shows the joins between various tables in Internal
Sales order (ISO)
SELECT porh.segment1,
porl.line_num,
pord.distribution_num,
ooh.order_number
sales_order,
ool.line_number so_line_num,
rsh.receipt_num,
rcv.transaction_type
FROM oe_order_headers_all
ooh,
po_requisition_headers_all porh,
po_requisition_lines_all porl,
po_req_distributions_all pord,
oe_order_lines_all ool,
po_system_parameters_all posp,
rcv_shipment_headers rsh,
rcv_transactions rcv
WHERE ooh.order_source_id
= posp.order_source_id
AND porh.org_id =
posp.org_id
AND
porh.requisition_header_id = ool.source_document_id
AND
porl.requisition_line_id = ool.source_document_line_id
AND
porh.requisition_header_id = porl.requisition_header_id
AND
porl.requisition_line_id = pord.requisition_line_id
AND
porl.requisition_line_id = rcv.requisition_line_id
AND
pord.distribution_id = rcv.req_distribution_id
AND rcv.shipment_header_id = rsh.shipment_header_id
No comments :
Post a Comment