ALL QUERIES

Query to find few business groups set up in the instance :

select

business_group_id,name

from per_business_groups

where lower(name) like '%vision corporation%'

order by name

/

Query to find legal entities associated with a SOB

select

organization_id legal_entity_id,

business_group_id,

name,

date_from,

date_to,

set_of_books_id,

vat_registration_number

from hr_legal_entities

where set_of_books_id=1

and business_group_id=202

Query to find inventory organizations for an operating unit :

select

organization_id,

organization_code,

organization_name,

(select location_id from hr_all_organization_units ou

where od.organization_id=ou.organization_id) location_id,

user_definition_enable_date,

disable_date,

chart_of_accounts_id,

inventory_enabled_flag,

operating_unit,

legal_entity,

set_of_books_id,

business_group_id

from org_organization_definitions od

where operating_unit=204

order by organization_code

/

The location_id found here is referenced in hr_locations table (described later), for detailed address information of this organization.

to find SOBs set up in the instance :

select

set_of_books_id,

name sob_name,

chart_of_accounts_id,

chart_of_accounts_name,

period_set_name calendar_period,

accounted_period_type,

user_period_type,

currency_code

from gl_sets_of_books_v

where set_of_books_id=1

/

find operating units for a legal entity :

select

organization_id operating_unit,

name,

business_group_id,

substr(set_of_books_id,1,10),

substr(legal_entity_id,1,10),

date_from,

date_to

from hr_operating_units

where legal_entity_id=204

/

select

ood.organization_code ORG_CODE,

a.master_organization_id MASTER_ORG_ID,

o1.organization_code MASTER_ORG_CD,

o1.organization_name MASTER_ORG_NM,

a.cost_organization_id COST_ORG_ID,

o2.organization_code COST_ORG_CD,

o2.organization_name COST_ORG_NM,

a.source_organization_id SOURCE_ORG_ID,

o3.organization_code SOURCE_ORG_CD,

o3.organization_name SOURCE_ORG_NM,

mfg1.meaning PRIMARY_COST_METHOD,

mfg2.meaning NEGATIVE_BALANCE,

mfg11.meaning GL_UPDATE_CODE,

a.calendar_code CALENDAR_CODE,

a.default_demand_class DEFAULT_DEMAND_CLASS,

mfg12.meaning ENCUMBRANCE_REVERSAL_FLAG,

mfg3.meaning LOCATOR_CONTROL,

mfg4.meaning INTERORG_TRANSFER_CODE,

DECODE(a.maintain_fifo_qty_stack_type, NULL,'',mfg6.meaning)

MAINTAIN_FIFO_COST,

mfg7.meaning SERIAL_NUMBER_TYPE,

mfg8.meaning LOT_NUMBER_UNIQUENESS,

mfg9.meaning LOT_NUMBER_GENERATION,

DECODE(a.LOT_NUMBER_ZERO_PADDING, NULL, '' ,

mfg10.meaning)

LOT_NUMBER_ZERO_PADDING,

b.rule_name ATP_RULE_NAME,

c.picking_rule_name PICKING_RULE_NAME,

a.default_locator_order_value,

a.default_subinv_order_value,

a.interorg_trnsfr_charge_percent intorg_charge_percent,

a.auto_serial_alpha_prefix,

a.start_auto_serial_number,

a.auto_lot_alpha_prefix,

a.lot_number_length,

mfg13.meaning SERIAL_GENERATION,

mfg14.meaning SOURCE_TYPE,

a.source_subinventory SOURCE_SUBINV

from

mtl_parameters a,

mtl_atp_rules b,

mtl_picking_rules c,

org_organization_definitions ood,

org_organization_definitions o1,

org_organization_definitions o2,

org_organization_definitions o3,

mfg_lookups mfg1,

mfg_lookups mfg2,

mfg_lookups mfg3,

mfg_lookups mfg4,

mfg_lookups mfg6,

mfg_lookups mfg7,

mfg_lookups mfg8,

mfg_lookups mfg9,

mfg_lookups mfg10,

mfg_lookups mfg11,

mfg_lookups mfg12,

mfg_lookups mfg13,

mfg_lookups mfg14

where a.organization_id = 207

and a.master_organization_id = o1.organization_id (+)

and a.cost_organization_id = o2.organization_id (+)

and a.source_organization_id = o3.organization_id (+)

and a.organization_id = ood.organization_id (+)

and a.default_atp_rule_id = b.rule_id(+)

and a.default_picking_rule_id = c.picking_rule_id(+)

and mfg1.lookup_type (+) = 'MTL_PRIMARY_COST'

and a.primary_cost_method = mfg1.lookup_code(+)

and mfg2.lookup_type (+) = 'SYS_YES_NO'

and a.negative_inv_receipt_code = mfg2.lookup_code(+)

and mfg3.lookup_type (+) = 'MTL_LOCATION_CONTROL'

and a.stock_locator_control_code = mfg3.lookup_code(+)

and mfg4.lookup_type (+) = 'MTL_INTER_INV_TRANSFER'

and a.matl_interorg_transfer_code = mfg4.lookup_code(+)

and mfg6.lookup_type (+) = 'SYS_YES_NO'

and a.maintain_fifo_qty_stack_type = mfg6.lookup_code(+)

and mfg7.lookup_type (+) = 'MTL_SERIAL_NUMBER_TYPE'

and a.serial_number_type = mfg7.lookup_code(+)

and mfg8.lookup_type (+) = 'MTL_LOT_UNIQUENESS'

and a.lot_number_uniqueness = mfg8.lookup_code(+)

and mfg9.lookup_type (+) = 'MTL_LOT_GENERATION'

and a.lot_number_generation = mfg9.lookup_code(+)

and mfg10.lookup_type (+) = 'SYS_YES_NO'

and a.lot_number_zero_padding = mfg10.lookup_code(+)

and mfg11.lookup_type (+) = 'SYS_YES_NO'

and a.general_ledger_update_code = mfg11.lookup_code(+)

and mfg12.lookup_type (+) = 'SYS_YES_NO'

and a.encumbrance_reversal_flag = mfg12.lookup_code(+)

and mfg13.lookup_type (+) = 'MTL_SERIAL_GENERATION'

and a.serial_number_generation = mfg13.lookup_code(+)

and mfg14.lookup_type (+) = 'MTL_SOURCE_TYPES'

and a.source_type = mfg14.lookup_code (+)

/

Query to find subinventories for an inv organization :

select

secondary_inventory_name subinventory,

description,

subinventory_type,

organization_id,

asset_inventory,

quantity_tracked,

inventory_atp_code,

availability_type,

reservable_type,

locator_type,

picking_order,

dropping_order,

location_id,

status_id

from mtl_secondary_inventories

where organization_id=207

order by subinventory

/

Find items restricted to a subinventory :

select

isb.inventory_item_id,

isb.organization_id,

si.segment1 item,

isb.secondary_inventory,

isb.primary_subinventory_flag,

isb.picking_order,

isb.min_minmax_quantity,

isb.max_minmax_quantity,

isb.inventory_planning_code,

isb.fixed_lot_multiple,

isb.minimum_order_quantity,

isb.maximum_order_quantity,

isb.source_type,

isb.source_organization_id,

isb.source_subinventory

from mtl_item_sub_inventories isb,

mtl_system_items si

where isb.secondary_inventory='Stores'

and isb.inventory_item_id=si.inventory_item_id

and isb.organization_id=si.organization_id

order by 1

/

Query to find locators in a subinventory:

select

il.inventory_location_id,

il.organization_id,

il.subinventory_code,

il.description,

il.physical_location_id,

il.pick_uom_code,

il.dimension_uom_code,

il.length,

il.width,

il.height,

il.locator_status,

il.status_id,

l.meaning

from mfg_lookups l,

mtl_item_locations il

where organization_id=207

and subinventory_code='Stores'

and il.inventory_location_type=l.lookup_code(+)

and l.lookup_type (+) = 'MTL_LOCATOR_TYPES'

/

Query to find Customer contacts and their roles :

select

oc.JOB_TITLE ,

oc.PARTY_SITE_ID,

RELATIONSHIP_ID ,

RELATIONSHIP_TYPE ,

hp.PARTY_NAME OBJECT_NAME, -- Contact

OBJECT_ID ,

OBJECT_TYPE ,

OBJECT_TABLE_NAME ,

hr.PARTY_ID ,

RELATIONSHIP_CODE ,

SUBJECT_ID ,

SUBJECT_TYPE ,

SUBJECT_TABLE_NAME ,

oc.TITLE ,

oc.MAIL_STOP ,

oc.CONTACT_KEY ,

ocr.ROLE_TYPE ,

DIRECTIONAL_FLAG

from HZ_ORG_CONTACT_ROLES ocr,

HZ_ORG_CONTACTS oc,

HZ_PARTIES hp,

HZ_RELATIONSHIPS hr

where hr.subject_id=1004 --Party id of the customer

and hr.object_id=hp.party_id

and hr.RELATIONSHIP_ID=oc. PARTY_RELATIONSHIP_ID

and oc.ORG_CONTACT_ID = ocr.ORG_CONTACT_ID(+)

order by OBJECT_NAME

/

Query to find communication channels for a customer :

Select

CONTACT_POINT_ID ,

CONTACT_POINT_TYPE ,

EMAIL_ADDRESS,

PHONE_NUMBER,

URL,

CONTACTS,

STATUS ,

OWNER_TABLE_NAME ,

OWNER_TABLE_ID ,

PRIMARY_FLAG ,

ORIG_SYSTEM_REFERENCE

From

HZ_CONTACT_POINTS

Where OWNER_TABLE_NAME ='HZ_PARTIES'

And OWNER_TABLE_ID = 1004 --Party_id for 'Hilman and Associates'

/

Query to find Organization type Party info :

select

hp.PARTY_ID,

hp.PARTY_NUMBER,

hp.PARTY_NAME,

hca.ACCOUNT_NUMBER,

hca.CUST_ACCOUNT_ID,

hop.ORGANIZATION_PROFILE_ID ,

hop.EFFECTIVE_START_DATE ,

hop.EFFECTIVE_END_DATE ,

hop.ORGANIZATION_NAME ,

hop.DUNS_NUMBER ,

hop.ENQUIRY_DUNS ,

hop.CEO_NAME ,

hop.CEO_TITLE ,

hop.PRINCIPAL_NAME ,

hop.PRINCIPAL_TITLE ,

hop.LEGAL_STATUS ,

hop.CONTROL_YR ,

hop.EMPLOYEES_TOTAL ,

hop.HQ_BRANCH_IND ,

hop.BRANCH_FLAG ,

hop.OOB_IND ,

hop.LINE_OF_BUSINESS

From

HZ_ORGANIZATION_PROFILES hop,

HZ_CUST_ACCOUNTS hca,

HZ_PARTIES hp

Where hp.party_name='Hilman and Associates'

and hp.PARTY_ID = hca.PARTY_ID

and hp.PARTY_ID = hop.PARTY_ID

and sysdate BETWEEN hop.EFFECTIVE_START_DATE AND nvl(hop.EFFECTIVE_END_DATE , sysdate+1)

Order by hca.ACCOUNT_NUMBER

/

Query to find supplier info :

select

pov.vendor_id,

pov.vendor_name supplier,

pov.vendor_type_lookup_code,

sl.location_code shipto_location,

bl.location_code billto_location,

pov.customer_num,

pov.ship_via_lookup_code,

pov.fob_lookup_code,

rt.name terms,

pov.set_of_books_id,

pov.credit_status_lookup_code,

pov.credit_limit

from ra_terms rt,

hr_locations bl,

hr_locations sl,

po_vendors pov

where pov.vendor_name like 'Abb%'

and pov.ship_to_location_id=sl.location_id(+)

and pov.bill_to_location_id=bl.location_id(+)

and pov.terms_id=rt.term_id(+)

order by 1

/

Query to find Supplier sites :

select

pov.vendor_name Supplier,

povs.vendor_site_id,

povs.vendor_site_code Site,

povs.address_line1 A1ddress,

povs.address_line2 A2ddress,

povs.address_line3 A3ddress,

povs.city||', '||

povs.state||' '||

povs.zip A4ddress,

povs.ship_to_location_id,

povs.bill_to_location_id,

povs.ship_via_lookup_code,

povs.freight_terms_lookup_code,

povs.fob_lookup_code

from po_vendors pov,

po_vendor_sites povs

where pov.vendor_id=601

and pov.vendor_id=povs.vendor_id

order by 1

/

Query to find Supplier contacts :

select

vc.vendor_contact_id,

vc.vendor_site_id,

vc.first_name,

vc.middle_name,

vc.last_name,

vc.prefix,

vc.title,

vc.mail_stop,

vc.area_code,

vc.phone,

vc.department,

vc.email_address,

vc.url,

vc.alt_area_code,

vc.alt_phone,

vc.fax_area_code,

vc.inactive_date,

vc.fax

from po_vendor_contacts vc

where vc.vendor_site_id=4556

order by 1

/

Query to get item attributes NOT under status control :

select meaning1 attrib_group,user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,

-- attribute_group_id,data_type,

-- user_attribute_name,level_updateable_flag,

-- validation_code ,lookup_type1,

-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,

meaning2 control_level,

-- ,enabled_flag2,

-- lookup_type3,lookup_code3,

meaning3 status_control,

-- enabled_flag3,lookup_type4,lookup_code4,

meaning4 validation

-- ,enabled_flag4

from

mtl_item_attributes_v

where control_level in (1,2)

and status_control_code is null

and user_attribute_name_gui is not null

and attribute_name in (

select attribute_name from mtl_item_attr_appl_inst_v )

order by

attribute_group_id_gui, sequence_gui

/

Query to get item status attribute controls :

select

ia.attribute_group_id group_id,

ia.user_attribute_name_gui,

lk.meaning controlled_at,

ia.attribute_name,

-- ia.user_attribute_name,

ia.status_control_code,

ia.validation_code

from fnd_lookup_values lk,

mtl_item_attributes ia

where ia.control_level=lk.lookup_code

and lk.lookup_type='ITEM_CONTROL_LEVEL_GUI'

order by ia.attribute_group_id,1

/

Query to find item status attributes :

select

mis.inventory_item_status_code item_status,

mis.description,

mis.disable_date,

av.attribute_name,

av.attribute_value value

from mtl_item_status mis,

mtl_status_attribute_values av

where mis.inventory_item_status_code=

av.inventory_item_status_code

order by 1

/

Query to get item attributes under status control :

select meaning1 attrib_group,user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,

-- attribute_group_id,data_type,

-- user_attribute_name,level_updateable_flag,

-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,

meaning2 control_level,

-- ,enabled_flag2,

-- lookup_type3,lookup_code3,

meaning3 status_control,

-- enabled_flag3,lookup_type4,lookup_code4,

meaning4 validation

-- ,enabled_flag4

from

mtl_item_attributes_v where control_level in (1,2) and status_control_code

is not null and user_attribute_name_gui is not null and attribute_name in (

select attribute_name

from mtl_item_attr_appl_inst_v ) order by

attribute_group_id_gui, sequence_gui

/

Query to find an Item attribute info :

select segment1 item,msi.description,inventory_item_id,ml.meaning item_type,

(select ia.user_attribute_name_gui||'.'||msi.inventory_item_status_code

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.inventory_item_status_code') attribute,

(select ia.user_attribute_name_gui||'.'||msi.purchasing_item_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.purchasing_item_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.shippable_item_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.shippable_item_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.mtl_transactions_enabled_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.mtl_transactions_enabled_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.so_transactions_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.so_transactions_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.internal_order_enabled_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.internal_order_enabled_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.customer_order_enabled_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.customer_order_enabled_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.purchasing_enabled_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.purchasing_enabled_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.inventory_asset_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.inventory_asset_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.eng_item_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.eng_item_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.inventory_item_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.inventory_item_flag') attribute,

(select ia.user_attribute_name||'.'||msi.service_item_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.service_item_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.internal_order_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.internal_order_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.build_in_wip_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.build_in_wip_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.bom_enabled_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.bom_enabled_flag') attribute,

(select ia.user_attribute_name_gui||'.'||msi.stock_enabled_flag

from mtl_item_attributes_v ia where lower(ia.attribute_name) ='mtl_system_items.stock_enabled_flag') attribute

from

fnd_lookup_values ml,

mtl_system_items msi

where msi.segment1 like 'AS18947%'

and msi.organization_id=204

and msi.item_type=ml.lookup_code(+)

and ml.lookup_type(+)='ITEM_TYPE'

order by 1,2

/

Query to find Item template attribute values :

select

it.template_name,

ita.attribute_name,

ita.attribute_value

from mtl_item_templates it,

mtl_item_templ_attributes ita

where it.template_name like 'ATO Model%'

and it.template_id=ita.template_id

and ita.attribute_value is not null

order by 1,2

/

Query to find item cross-references :

select

msi.segment1 item,

mcr.cross_reference_type reference_type,

mcr.cross_reference,

mcr.description

from mtl_cross_references mcr,

mtl_system_items msi

where mcr.cross_reference_type='Vendor'

and mcr.inventory_item_id=msi.inventory_item_id

and mcr.organization_id=msi.organization_id

order by 1,2

/

Query to find Customer items :

select

hp.party_name customer,

ci.customer_item_number,

ci.customer_item_desc,

msi.segment1 item,

msi.description item_desc,

ci.customer_category_code,

ci.item_definition_level,

ci.commodity_code_id,

ci.address_id

from

hz_parties hp,

hz_cust_accounts hca,

mtl_system_items msi,

mtl_customer_items ci,

mtl_customer_item_xrefs ix

where ci.customer_item_id=ix.customer_item_id

and ix.inventory_item_id=msi.inventory_item_id

and ix.master_organization_id=msi.organization_id

and ci.customer_id=hca.cust_account_id

and hca.party_id=hp.party_id

order by 1,2

/

Query to find Manufacturer items :

select

mm.manufacturer_name,

mp.mfg_part_num,

mp.description,

msi.segment1 inv_item,

msi.description item_desc

from

mtl_system_items msi,

mtl_mfg_part_numbers mp,

mtl_manufacturers mm

where mm.manufacturer_id=mp.manufacturer_id

and mp.inventory_item_id=msi.inventory_item_id

and mp.organization_id=msi.organization_id

order by 1,2

/

Query to find related items :

select

ito.segment1 item,

ito.description,

itr.segment1 related_item,

itr.description,

ml.meaning relation,

ri.reciprocal_flag

from

mfg_lookups ml,

mtl_system_items itr,

mtl_system_items ito,

mtl_related_items ri

where ri.inventory_item_id=ito.inventory_item_id

and ri.organization_id=ito.organization_id

and ri.related_item_id=itr.inventory_item_id

and ri.organization_id=itr.organization_id

and ri.relationship_type_id=ml.lookup_code

and ml.lookup_type(+)='MTL_RELATIONSHIP_TYPES'

order by 1,2

/

Query to find default category for a category set :

select

mcats.category_set_name,

mcat.segment1 default_category,

mcat.description cat_desc,

mcat.category_id,

mcats.category_set_id

from

mtl_category_sets mcats,

mtl_categories mcat

where mcats.category_set_name like '%'

and mcat.category_id = mcats.default_category_id

order by 1,2

/

Query to find all items assigned to categories of a category set :

select

mcats.category_set_name,

mcat.segment1||'.'|| mcat.segment2 category,

msi.segment1 item,

msi.description item_desc

from

mtl_item_categories micat,

mtl_category_sets mcats,

mtl_categories mcat,

mtl_system_items_vl msi

where mcats.category_set_name like 'Inv%'

and micat.category_set_id = mcats.category_set_id

and micat.category_id = mcat.category_id

and mcat.segment1 like 'N%'

and msi.inventory_item_id = micat.inventory_item_id

and msi.organization_id = micat.organization_id

and msi.organization_id = 204

order by 1,2,3

/

Query to find out the customer, line item, ordered qty and price info of the order :

select

h.order_number,

org.name customer_name,

h.ordered_date order_date,

ot.name order_type,

s.name sales_rep,

l.line_id,

l.line_number,

l.inventory_item_id,

si.segment1,

l.ordered_quantity,

l.unit_selling_price,

nvl(l.ordered_quantity,0) * nvl(l.unit_selling_price,0) amount,

h.transactional_curr_code currency_code

from ra_salesreps s,

oe_transaction_types_tl ot,

oe_sold_to_orgs_v org,

mtl_system_items_vl si,

oe_order_lines_all l,

oe_order_headers_all h

where h.order_number= 14463

and h.org_id = 204

and l.header_id = h.header_id

and h.sold_to_org_id = org.organization_id

and (h.cancelled_flag is null or h.cancelled_flag = 'N')

and h.open_flag='Y'

and l.open_flag = 'Y'

and l.service_reference_line_id is null

and l.inventory_item_id = si.inventory_item_id

and nvl(si.organization_id,0) = 204 --Item master orgn

and h.order_type_id = ot.transaction_type_id

and h.salesrep_id=s.salesrep_id

and h.org_id=s.org_id

order by l.line_id

/

a: Oe_sold_to_orgs_v is a view based on hz_parties and hz_cust_accounts.

b: Ra_salesreps is a view based on JTF_RS_SALESREPS and JTF_RS_RESOURCE_EXTNS_VL.

You must set the org context for the views to function properly as..

begin

fnd_client_info.set_org_context('204');

end;

Query to find customer, ship to and bill to information of an order :

select

h.order_number,

c.name customer_name,

lk1.meaning Freight_Terms,

lk2.meaning FOB,

s.location_code ship_location_code,

s.address_line_1 ship_address1,

s.address_line_2 ship_address2,

s.state ship_state,

s.postal_code ship_zip,

s.country ship_country,

b.location_code bill_location_code,

b.address_line_1 bill_address1,

b.address_line_2 bill_address2,

b.country bill_country

from

ar_lookups lk2,

oe_lookups lk1,

oe_sold_to_orgs_v c,

oe_invoice_to_orgs_v b,

oe_ship_to_orgs_v s,

oe_order_headers_all h

where h.order_number= 14463

and h.org_id = 204

and h.ship_to_org_id = s.organization_id

and h.invoice_to_org_id = b.organization_id

and h.sold_to_org_id = c.organization_id

and h.freight_terms_code = lk1.lookup_code(+)

and lk1.lookup_type(+) = 'FREIGHT_TERMS'

and lk2.lookup_code(+) = h.fob_point_code

and lk2.lookup_type(+) = 'FOB'

/

a: The oe_ship_to_orgs_v and oe_invoice_to_orgs_v views are based on HZ_CUST_SITE_USES_ALL, HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES and HZ_LOCATIONS.

b: Oe_lookups and ar_lookups are views based on fnd_lookup_values.

Query to find out order and line hold information :

select ho.name hold_name,

hs.hold_until_date,

hs.hold_comment,

h.order_number,

oh.header_id,

oh.line_id,

oh.order_hold_id,

l.item_identifier_type,

l.inventory_item_id,

l.ordered_item

from oe_order_holds_all oh,

oe_order_lines_all l,

oe_order_headers_all h,

oe_hold_definitions ho,

oe_hold_sources_all hs

where h.order_number= 14463

and oh.header_id = h.header_id

and (h.cancelled_flag is null or h.cancelled_flag = 'N')

and h.open_flag='Y'

and oh.hold_source_id = hs.hold_source_id

and hs.hold_id = ho.hold_id

and h.header_id = l.header_id(+)

and l.open_flag = 'Y'

and l.line_id = nvl(oh.line_id,l.line_id)

and l.service_reference_line_id is null

and oh.hold_release_id is null

and nvl(h.org_id,0) = 204

and nvl(l.org_id,0) = nvl(h.org_id,0)

order by ho.name,h.order_number

/

Query to find freight related info of order viz: freight carrier, ship method and service level :

select

h.order_number,

h.shipping_method_code,

wc.carrier_name,

wcsm.SERVICE_LEVEL ,

wcsm.freight_code

from

wsh_carrier_ship_methods_v wcsm,

wsh_carriers_v wc,

oe_order_headers_all h

where h.order_number= 14463

and h.org_id = 204

and h.shipping_method_code = wcsm.ship_method_code(+)

and nvl(wcsm.organization_id(+),0) = 204 --Master Organization

and wcsm.freight_code = wc.freight_code(+)

order by h.order_number

/

Query to find price discounts and surcharges on order lines :

Select h.order_number,

l.line_number,

pa.list_line_type_code,

pa.arithmetic_operator,

pa.operand,

DECODE(PA.MODIFIER_LEVEL_CODE,'ORDER',

L.UNIT_LIST_PRICE*L.ORDERED_QUANTITY *PA.OPERAND * SIGN(PA.ADJUSTED_AMOUNT)/100,

(PA.ADJUSTED_AMOUNT* NVL(L.ORDERED_QUANTITY,0) )) DISCOUNT_AMT

From

qp_list_headers_vl lh,

oe_price_adjustments pa,

oe_order_lines_all l,

oe_order_headers_all h

Where h.order_number = 14463

and h.header_id = l.header_id

and h.org_id = l.org_id

and h.header_id = pa.header_id

and l.line_id = pa.line_id(+)

and pa.list_header_id = lh.list_header_id

AND ( PA.LIST_LINE_TYPE_CODE = 'DIS'

OR PA.LIST_LINE_TYPE_CODE = 'SUR'

OR PA.LIST_LINE_TYPE_CODE = 'PBH' )

AND PA.APPLIED_FLAG='Y'

AND NOT EXISTS

(SELECT 'X'

FROM OE_PRICE_ADJ_ASSOCS PAS,

OE_PRICE_ADJUSTMENTS PA1

WHERE PAS.RLTD_PRICE_ADJ_ID =

PA.PRICE_ADJUSTMENT_ID

AND PA1.PRICE_ADJUSTMENT_ID=

PAS.PRICE_ADJUSTMENT_ID

AND PA1.LIST_LINE_TYPE_CODE ='PBH')

Order by l.line_id

/

a: Qp_list_headers_vl is view based on qp_list_headers_b and qp_list_headers_tl tables.

Query to find freight charges on order lines :

select

HEADER_ID ,

LINE_ID ,

CHARGE_ID ,

CHARGE_NAME ,

CHARGE_AMOUNT ,

CURRENCY_CODE ,

INVOICED_FLAG ,

INTERCO_INVOICED_FLAG ,

ORG_ID ,

SOURCE_SYSTEM_CODE ,

ESTIMATED_FLAG ,

INVOICED_AMOUNT

from OE_CHARGE_LINES_V

where header_id=

(select header_id

from oe_order_headers_all

where order_number=14463)

order by line_id

/

a: The OE_CHARGE_LINES_V view is based on oe_price_adjustments, oe_order_headers_all and oe_order_lines_all for FREIGHT CHARGES.

select distinct lv.parent_segment_id,lc.location_id_segment_1,

location_segment_user_value ,lr.from_postal_code,lr.to_postal_code,

location_segment_value, lr.tax_rate

from ar_location_rates lr,

ar_location_combinations lc,

ar_location_values lv

where lv.location_segment_user_value='CA' --State name

and lv.location_segment_id=lc.location_id_segment_1

and lv.location_structure_id= lc.location_structure_id

and lc.location_structure_id=101

and lv.location_segment_id=lr.location_segment_id

order by 1

/

Table ar_sales_tax contains location wise total tax- rates with tax break up ..

select distinct

location_id,

rate_context,

tax_rate,

location1_rate,

location2_rate,

location3_rate,

from_postal_code,

to_postal_code

from ar_sales_tax

where location_id=1000

and enabled_flag='Y'

/

Query to find out the shipper info :

select

wnd.delivery_id delivery_id,

substrb(party.party_name,1,50) customer,

wpb.name batch_name,

wsh_util_core.get_location_description(

wnd.INITIAL_PICKUP_LOCATION_ID,

'NEW UI CODE') ship_from,

wsh_util_core.get_location_description(

wnd.ULTIMATE_DROPOFF_LOCATION_ID,

'NEW UI CODE') ship_to,

wnd.INITIAL_PICKUP_DATE pickup_date,

wnd.ULTIMATE_DROPOFF_DATE dropoff_date,

lv.meaning ship_method,

wnd.WAYBILL waybill,

wnd.GROSS_WEIGHT gross_weight,

wnd.WEIGHT_UOM_CODE uom,

wnd.status_code,

we.message

from wsh_new_deliveries wnd,

wsh_picking_batches wpb,

wsh_exceptions we,

fnd_lookup_values_vl lv,

hz_cust_accounts cust_acct,

hz_parties party

where wnd.delivery_id = 12814

and wpb.batch_id = wnd.batch_id

and we.delivery_id(+) = wnd.delivery_id

and we.exception_name(+) = 'WSH_BATCH_MESSAGE'

and lv.lookup_code(+) = wpb.ship_method_code

and lv.lookup_type(+) = 'SHIP_METHOD'

and lv.view_application_id(+) = 3

and cust_acct.cust_account_id (+)=wnd.customer_id

and party.party_id(+) = cust_acct.party_id

/

Query to find out shipper detail info :

SELECT

wnd.delivery_id,

wnd.name delivery_name,

wdd.source_header_number

so_order_number,

oola.line_number so_line_number,

wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id,

wdd.shipping_instructions,

wdd.inventory_item_id,

wdd.requested_quantity_uom,

msi.description item_description,

msi.revision_qty_control_code ,

wdd.ship_method_code carrier,

wdd.shipment_priority_code priority,

wdd.organization_id,

wnd.initial_pickup_location_id,

wdd.released_status,

wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id =18910

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

/

Query to find out Move order line details :

SELECT

wnd.delivery_id,

wnd.name delivery_name,

wnd.initial_pickup_location_id,

mtrh.request_number mo_number,

mtrl.line_number mo_line_number,

mtrl.line_id mo_line_id,

mtrl.from_subinventory_code,

mtrl.to_subinventory_code,

mtrl.lot_number,

mtrl.serial_number_start,

mtrl.serial_number_end,

mtrl.uom_code,

mtrl.quantity,

mtrl.quantity_delivered,

mtrl.quantity_detailed,

wdd.source_header_number so_order_number,

oola.line_number so_line_number,

wdd.source_header_id so_header_id,

wdd.source_line_id so_line_id,

wdd.shipping_instructions,

wdd.inventory_item_id,

wdd.requested_quantity_uom,

msi.description item_description,

msi.revision_qty_control_code ,

wdd.ship_method_code carrier,

wdd.shipment_priority_code priority,

wdd.organization_id,

wdd.released_status,

wdd.source_code

FROM mtl_system_items_vl msi,

oe_order_lines_all oola,

mtl_txn_request_lines mtrl,

mtl_txn_request_headers mtrh,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

WHERE wnd.delivery_id =18910

AND wda.delivery_id = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.move_order_line_id = mtrl.line_id

AND mtrl.header_id = mtrh.header_id

AND wdd.inventory_item_id = msi.inventory_item_id(+)

AND wdd.organization_id = msi.organization_id(+)

AND wdd.source_line_id = oola.line_id

AND wdd.source_header_id = oola.header_id

/

Query to find Bill of Lading info of the Delivery :

select

wnd.delivery_id delivery_id,

wdi.sequence_number bol_number,

wdi.bol_notify_party,

wdi.port_of_loading,

wdi.port_of_discharge,

wnd.WAYBILL waybill,

wnd.GROSS_WEIGHT gross_weight,

wnd.WEIGHT_UOM_CODE uom,

wnd.status_code

from wsh_new_deliveries wnd,

wsh_delivery_legs wdl,

wsh_document_instances wdi

where wnd.delivery_id =12784

and wnd.delivery_id = wdl.delivery_id (+)

and wdi.entity_id (+) = wdl.delivery_leg_id

AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS'

AND wdi.document_type (+) = 'BOL'

AND wdi.status (+) <> 'CANCELLED'

/

Query to find delivery leg and pick up stop info :

SELECT wt.trip_id,

wt.name,

wt.STATUS_CODE,

wt.VEHICLE_ITEM_ID,

wt.VEHICLE_NUMBER,

wt.CARRIER_ID,

wt.SHIP_METHOD_CODE,

wts.STOP_ID,

wts.STOP_LOCATION_ID,

wts.STATUS_CODE,

wts.STOP_SEQUENCE_NUMBER,

wts.PLANNED_ARRIVAL_DATE,

wts.PLANNED_DEPARTURE_DATE,

wts.ACTUAL_ARRIVAL_DATE,

wts.ACTUAL_DEPARTURE_DATE,

wts.DEPARTURE_NET_WEIGHT,

wts.WEIGHT_UOM_CODE,

wdl.DELIVERY_LEG_ID,

wdl.DELIVERY_ID,

wdl.PICK_UP_STOP_ID,

wdl.DROP_OFF_STOP_ID,

wdl.SEQUENCE_NUMBER,

wdl.LOADING_ORDER_FLAG,

wdl.SHIPPER_TITLE,

wdl.SHIPPER_PHONE

FROM wsh_trips wt

,wsh_trip_stops wts

,wsh_delivery_legs wdl

WHERE wdl.delivery_id =12814

AND wts.stop_id = wdl.pick_up_stop_id

AND wts.trip_id = wt.trip_id;

Query to find Requisition details :

execute fnd_client_info.set_org_context('204');

col Justification form a24

col Item_Description form a36

col Source form a56

col Source_Type form a12

col Requestor form a20

col Line_Type form a12

col Item form a16

SELECT prl.line_num Line

, plt.line_type Line_Type

, prl.item_id prl_item_id

, msi.segment1 Item

, prl.item_revision Rev

, prl.need_by_date Need_By_Date

, prl.unit_meas_lookup_code Unit

, round(prl.quantity,2) Quantity_Amount

, prl.unit_price Unit_Price

, DECODE (PRL.order_type_lookup_code, /* */

'FIXED PRICE', PRL.amount,

'RATE', PRL.amount,

NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT

, ppf.full_name Requestor

, plc.displayed_field Source_Type

, decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '||prl.source_subinventory,'VENDOR',prh.segment1||' - '||prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '||prl.suggested_vendor_contact||' - '||prl.suggested_buyer_id,null)Source

, prl.item_description Item_Description

, prd.req_line_quantity Distributions

, prl.justification Justification

, prl.requisition_header_id

, prl.requisition_line_id

FROM po_requisition_headers prh

, po_requisition_lines prl

, po_req_distributions prd

, po_line_types plt

, per_people_f ppf

, org_organization_definitions ood

, po_lookup_codes plc

, mtl_system_items msi

, mtl_categories mca

, gl_code_combinations gcc, financials_system_parameters fsp

, po_system_parameters psp

WHERE prh.segment1 = '1713'

AND prl.requisition_line_id = prd.requisition_line_id

AND prl.requisition_header_id = prh.requisition_header_id

AND prl.line_type_id = plt.line_type_id

AND prl.to_person_id = ppf.person_id (+)

AND prl.source_organization_id = ood.organization_id(+)

AND plc.lookup_type = 'REQUISITION SOURCE TYPE'

AND plc.lookup_code = prl.source_type_code

AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0)

from financials_system_parameters fsp)

AND trunc(sysdate)

BETWEEN nvl(ppf.effective_start_date, trunc(sysdate))

AND nvl(ppf.effective_end_date, trunc(sysdate))

AND prl.item_id = msi.inventory_item_id(+)

AND msi.organization_id = 204

AND prl.category_id = mca.category_id

AND prd.code_combination_id = gcc.code_combination_id

AND nvl(prl.modified_by_agent_flag,'N') = 'N'

AND nvl(prl.cancel_flag,'N') != 'Y'

AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'

ORDER BY prl.line_num

/

Query to find Requisition header info :

execute fnd_client_info.set_org_context('204');

col Description form a40

col Req_type form a26

col type_lookup_code form a16

col PREPARER form a30

col APPROVER form a30

col NOTE_TO_APPROVER form a40

SELECT prh.segment1 Requisition

, psp.manual_req_num_type req_num_type

, ppf.full_name Preparer

, prh.creation_date Creation_Date

, prh.type_lookup_code

, ppf1.full_name Approver

, t.type_name Req_type

, prh.description Description

, pah.note Note_To_Approver

, prh.requisition_header_id Req_header

FROM po_requisition_headers prh

, per_people_f ppf1

, per_people_f ppf

, po_action_history pah

, po_system_parameters psp

, PO_DOCUMENT_TYPES_ALL_TL T

, PO_DOCUMENT_TYPES_ALL_B B

WHERE prh.REQUISITION_HEADER_ID=11675

and NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'

AND prh.preparer_id = ppf.person_id

AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0)

from financials_system_parameters fsp)

AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE',

'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN')

--AND prh.segment1 = P_req_num_from

AND EXISTS (SELECT null

FROM po_requisition_lines prl

WHERE prl.requisition_header_id = prh.requisition_header_id

AND nvl(prl.modified_by_agent_flag,'N') = 'N'

AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED')

AND pah.object_id = prh.requisition_header_id

AND pah.employee_id = ppf1.person_id

AND pah.object_type_code = 'REQUISITION'

AND pah.object_sub_type_code = prh.type_lookup_code

AND pah.sequence_num =

(SELECT max(sequence_num)

FROM po_action_history pah

WHERE pah.object_id = prh.requisition_header_id

AND pah.object_type_code = 'REQUISITION'

AND pah.object_sub_type_code = prh.type_lookup_code)

and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE

AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE

AND b.document_type_code = 'REQUISITION'

AND b.document_subtype = prh.type_lookup_code

AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)

AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',

NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))

= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,

SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

AND T.LANGUAGE = USERENV('LANG')

/

Query to find an PO details :

execute fnd_client_info.set_org_context('204');

SELECT

decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number_Release

, pol.line_num Line

, pov.vendor_name Vendor

, pol.item_revision Rev

, pol.item_description Description

, pll.shipment_num

, pod.distribution_num Distribution

, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price

, pll.promised_date Promised_Date

, pol.unit_meas_lookup_code Unit

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_ordered,

'FIXED PRICE', POD.amount_ordered,

POD.quantity_ordered) Quantity_Amount_Ordered

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_billed,

'FIXED PRICE', POD.amount_billed,

POD.quantity_billed) Quantity_Amount_Billed

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_delivered,

'FIXED PRICE', POD.amount_delivered,

POD.quantity_delivered) Qty_Amount_Delivered

, DECODE (POL.order_type_lookup_code,

'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /

DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),

'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /

DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),

(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /

DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled

, DECODE (POL.order_type_lookup_code,

'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),

'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),

(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV

, poh.po_header_id

, pol.po_line_id

, por.release_num

, poh.currency_code C_CURRENCY

, nvl(por.po_release_id,-1) release_id

FROM po_distributions pod

, mtl_system_items msi

, po_line_locations pll

, po_lines pol

, po_releases por

, po_headers poh

, po_vendors pov

, financials_system_parameters fsp

, po_line_types plt

WHERE poh.segment1='804'

AND poh.po_header_id = pol.po_header_id

AND pol.po_line_id = pll.po_line_id

AND pll.line_location_id = pod.line_location_id

AND pol.item_id = msi.inventory_item_id (+)

AND msi.organization_id = fsp.inventory_organization_id

AND poh.vendor_id = pov.vendor_id (+)

AND pll.po_release_id = por.po_release_id (+)

AND pol.line_type_id = plt.line_type_id

AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')

AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')

AND nvl(poh.cancel_flag,'N') = 'N'

AND nvl(por.cancel_flag,'N') = 'N'

AND nvl(pol.cancel_flag,'N') = 'N'

AND nvl(pll.cancel_flag,'N') = 'N'

ORDER BY pll.line_location_id

/

Query to find receipts against a PO shipment line :

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

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 to find PO returns :

SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id

, sum ( (nvl(rct.source_doc_quantity,0)) ) Qty_returned

from rcv_transactions rct

, po_lines pol

, po_line_locations pll

where rct.transaction_type = 'RETURN TO VENDOR'

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')

group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

union all

SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id

, sum ( (nvl(rct.amount,0)) ) Qty_returned

from rcv_transactions rct

, po_lines pol

, po_line_locations pll

where rct.transaction_type = 'RETURN TO VENDOR'

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') IN ('RATE','FIXED PRICE')

group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

/

Query to find PO corrections :

SELECT pol.po_header_id,pol.po_line_id, rct.po_line_location_id Line_location_id

, sum (nvl(rct1.source_doc_quantity,0) ) Qty_corrected

from rcv_transactions rct

, rcv_transactions rct1

, po_lines pol

, po_line_locations pll

where rct.transaction_type in ( 'RECEIVE' ,'MATCH')

and rct.po_line_location_id = pll.line_location_id

and rct1.transaction_type = 'CORRECT'

and rct1.parent_transaction_id = rct.transaction_id

and rct1.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')

group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

union all

SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id

, sum (nvl(rct1.amount,0) ) Qty_corrected

from rcv_transactions rct

, rcv_transactions rct1

, po_lines pol

, po_line_locations pll

where rct.transaction_type in ( 'RECEIVE' ,'MATCH')

and rct.po_line_location_id = pll.line_location_id

and rct1.transaction_type = 'CORRECT'

and rct1.parent_transaction_id = rct.transaction_id

and rct1.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') IN ('RATE','FIXED PRICE')

group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

/

To get valid Ship-to ids...................

SELECT site_use_id

FROM hz_cust_site_uses_all hcsu,

hz_cust_acct_sites_all hcas,

hz_cust_accounts_all hca,

ra_customers rc

WHERE hca.cust_account_id = hcas.cust_account_id

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

AND hcsu.site_use_code = 'SHIP_TO'

AND hcsu.status = 'A'

AND hcas.org_id = 204

AND rc.customer_number = hca.account_number

AND rc.customer_id =5453

to get valid bill-to ids

SELECT site_use_id

FROM hz_cust_site_uses_all hcsu,

hz_cust_acct_sites_all hcas,

hz_cust_accounts_all hca,

ra_customers rc

WHERE hca.cust_account_id = hcas.cust_account_id

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

AND hcsu.site_use_code = 'BILL_TO'

AND hcsu.status = 'A'

AND hcas.org_id = 204

AND rc.customer_number = hca.account_number

AND rc.customer_id =5453

/

AP PAYMENT EXCEPTIONS

SELECT DISTINCT

sob.name sob_name,

hou.NAME ou_name,

acl.check_number,

acl.CURRENCY_CODE,

acl.amount,

xxap_dist_amount (ai.invoice_id,

ail.invoice_distribution_id,

acl.check_id) line_amt,

xxap_usd_amount(acl.CURRENCY_CODE,

acl.check_date,

xxap_dist_amount(ai.invoice_id,

ail.invoice_distribution_id,

acl.check_id)) usd_amount,

xxap_usd_amount(acl.CURRENCY_CODE,

acl.check_date,

acl.amount) usd_sum,

acl.check_date,

pv.vendor_name,

ai. invoice_num,

ail.distribution_line_number,

ai.description,

ai.ATTRIBUTE10 Approver,

gcc.segment1,

gcc.segment2,

gcc.segment3,

gcc.segment4,

gcc.segment5,

gcc.segment6,

gcc.segment7,

gcc.segment8,

gcc.segment9,

gcc.segment10,

gcc.segment11,

xxap_account_desc('247_Entity',

gcc.segment1) seg1_desc,

xxap_account_desc('247_Natural',

gcc.segment2) seg2_desc,

xxap_account_desc('247_Budget_Code',

gcc.segment3) seg3_desc,

xxap_account_desc('247_Program',

gcc.segment4) seg4_desc,

xxap_account_desc('247_Location',

gcc.segment5) seg5_desc,

xxap_account_desc('247_Business_Unit',

gcc.segment6)seg6_desc,

xxap_account_desc('247_Line_Of_Business',

gcc.segment7) seg7_desc,

xxap_account_desc('247_Service_Offeings',

gcc.segment8) seg8_desc,

xxap_account_desc('247_Entity',

gcc.segment9) seg9_desc,

xxap_account_desc('247_Spare1',

gcc.segment10) seg10_desc,

xxap_account_desc('247_Spare2',

gcc.segment11) seg11_desc,

DECODE(SUBSTR(gcc.segment2,

1,

2),

'60',

'Opex',

'61',

'Opex',

'62',

'Opex',

'63',

'Opex',

'64',

'Opex',

'65',

'Opex',

'66',

'Opex',

'67',

'Opex',

'68',

'Opex',

'69',

'Opex',

'31',

'Capex',

'Advance') Capex_Opex,

acl.status_lookup_code,

ai.doc_sequence_value,

pvs.vendor_site_code

FROM

ap_checks_all acl,

po_vendors pv,

ap_invoices_all ai,

ap_invoice_distributions_all ail,

ap_invoice_payments_all aip,

gl_code_combinations gcc,

GL_SETS_OF_BOOKS sob,

HR_OPERATING_UNITS hou,

po_vendor_sites_all pvs

WHERE

pv.vendor_id = acl.vendor_id AND

ai.VENDOR_ID = pv.vendor_id AND

pv.vendor_id = pvs.vendor_id AND

ai.vendor_site_id = pvs.vendor_site_id AND

ai.invoice_id = aip.invoice_id AND

aip.check_id = acl.check_id AND

ai.invoice_id = ail.invoice_id AND

ail.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID AND

ai.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID AND

hou.organization_id = ai.org_id AND

acl.amount <> 0 AND

acl.payment_type_flag <> 'R' AND

ail.line_type_lookup_code = 'ITEM'

/

SUPPLIER MASTER

SELECT v.vendor_id, v.segment1 AS vendor_num, v.vendor_name,

NVL (v.vendor_type_lookup_code, 'NULL') "Vendor Type",

v.attribute6 "STAX REG",

v.invoice_currency_code "Invoice Currency",

v.payment_currency_code "Payment Currency", t.NAME "Payment Term",

s.address_line1, s.address_line2, s.address_line3, s.city, s.state,

s.country,s.ZIP, s.freight_terms_lookup_code "Freight Term",

d1.gl_name_seg2 "Liability Account",

d2.gl_name_seg2 "Prepayment Account",

d1.gl_seg2 "Liability Account ID",

d2.gl_seg2 "Prepayment Account ID", t.tds_section, t.pan_no,

t.tan_no, t.ward_no, t.st_reg_no "LST No", t.cst_reg_no "CST No",

s.vendor_site_code, s.vendor_site_id, v.end_date_active,

DECODE (v.enabled_flag, 'Y', 'Active', 'Inactive') status, s.org_id,

h.NAME AS organisation_name, v.num_1099, v.type_1099

FROM po_vendors v,

ap_terms_tl t,

po_vendor_sites_all s,

disc_ccid_dsc_mv d1,

disc_ccid_dsc_mv d2,

disc_supplier_tds t,

hr_all_organization_units h

WHERE v.terms_id = t.term_id

AND v.vendor_id = s.vendor_id(+)

-- AND v.vendor_id <> 1

AND d1.code_combination_id = s.accts_pay_code_combination_id

AND d2.code_combination_id = s.prepay_code_combination_id

AND v.vendor_id = t.vendor_id(+)

AND (SYSDATE + 1) BETWEEN v.start_date_active

AND NVL (v.end_date_active, '31-DEC-2999')

AND s.org_id NOT IN ('87', '86')

AND s.org_id = h.organization_id

FIND REQUISITION DETAILS

SELECT

PO_REQUISITION_HEADERS_ALL.SEGMENT1 AS REQUISTION_NUMBER,

PO_REQUISITION_HEADERS_ALL.CREATION_DATE AS REQUISITION_DATE,

PO_REQUISITION_HEADERS_ALL.DESCRIPTION,

PO_REQUISITION_HEADERS_ALL.NOTE_TO_AUTHORIZER,

PO_REQUISITION_HEADERS_ALL.CANCEL_FLAG,

PO_REQUISITION_HEADERS_ALL.CLOSED_CODE,

PO_REQUISITION_LINES_ALL.CATEGORY_ID,

MTL_CATEGORIES_V.SEGMENT1 AS CATEGORY,

MTL_CATEGORIES_V.SEGMENT2 AS SUB_CATEGORY,

PO_REQUISITION_LINES_ALL.ITEM_DESCRIPTION,

PO_REQUISITION_LINES_ALL.UNIT_MEAS_LOOKUP_CODE UNIT_OF_MEASURE,

PO_REQUISITION_LINES_ALL.UNIT_PRICE,

PO_REQUISITION_LINES_ALL.QUANTITY,

PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID,

--DISTRIBUTION_LOCATION.DESCRIPTION AS DELIVER_TO_LOCATION,

DISC_EMPLOYEE_V."Employee_Name" AS EMPLOYEE_NAME,

PO_REQUISITION_LINES_ALL.TO_PERSON_ID,

PO_REQUISITION_LINES_ALL.ITEM_ID,

PO_REQUISITION_LINES_ALL.ITEM_REVISION,

PO_REQUISITION_LINES_ALL.QUANTITY_DELIVERED,

PO_REQUISITION_LINES_ALL.NEED_BY_DATE,

PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID,

PO_REQUISITION_LINES_ALL.BLANKET_PO_HEADER_ID,

PO_REQUISITION_LINES_ALL.BLANKET_PO_LINE_NUM,

PO_REQUISITION_LINES_ALL.CURRENCY_UNIT_PRICE,

PO_REQUISITION_LINES_ALL.SUGGESTED_VENDOR_NAME,

PO_REQUISITION_LINES_ALL.SUGGESTED_VENDOR_LOCATION,

PO_REQUISITION_LINES_ALL.SUGGESTED_VENDOR_CONTACT,

PO_REQUISITION_LINES_ALL.SUGGESTED_VENDOR_PHONE,

PO_REQUISITION_LINES_ALL.SUGGESTED_VENDOR_PRODUCT_CODE,

PO_REQUISITION_LINES_ALL.REFERENCE_NUM,

PO_REQUISITION_LINES_ALL.DESTINATION_ORGANIZATION_ID,

PO_REQUISITION_LINES_ALL.QUANTITY_CANCELLED,

PO_REQUISITION_LINES_ALL.CANCEL_DATE,

PO_REQUISITION_LINES_ALL.CANCEL_REASON,

PO_REQUISITION_LINES_ALL.VENDOR_ID,

PO_REQUISITION_LINES_ALL.VENDOR_SITE_ID,

PO_REQUISITION_LINES_ALL.VENDOR_CONTACT_ID,

--PO_REQUISITION_LINES_ALL.ATTRIBUTE8 AS TRAVEL_LOCATION_ID,

--TRAVEL_LOCATION.DESCRIPTION AS TRAVEL_LOCATION,

--PO_REQUISITION_LINES_ALL.ATTRIBUTE9 AS RECOVERABLE_FROM_CUSTOMER,

PO_REQUISITION_LINES_ALL.QUANTITY_RECEIVED,

PO_REQ_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,

PO_REQ_DISTRIBUTIONS_ALL.PROJECT_ID,

PO_REQ_DISTRIBUTIONS_ALL.TASK_ID,

PO_REQ_DISTRIBUTIONS_ALL.EXPENDITURE_TYPE,

PO_REQ_DISTRIBUTIONS_ALL.PROJECT_RELATED_FLAG,

PO_REQ_DISTRIBUTIONS_ALL.EXPENDITURE_ITEM_DATE,

REQ_CCID_DSC.GL_SEG2 AS CHARGE_GL_SEG2,

REQ_CCID_DSC.GL_SEG3 AS CHARGE_GL_SEG3,

REQ_CCID_DSC.GL_SEG4 AS CHARGE_GL_SEG4,

REQ_CCID_DSC.GL_SEG5 AS CHARGE_GL_SEG5,

REQ_CCID_DSC.GL_SEG6 AS CHARGE_GL_SEG6,

REQ_CCID_DSC.GL_NAME_SEG1 AS CHARGE_GL_NAME_SEG1,

REQ_CCID_DSC.GL_NAME_SEG2 AS CHARGE_GL_NAME_SEG2,

REQ_CCID_DSC.GL_NAME_SEG3 AS CHARGE_GL_NAME_SEG3,

REQ_CCID_DSC.GL_NAME_SEG4 AS CHARGE_GL_NAME_SEG4,

REQ_CCID_DSC.GL_NAME_SEG5 AS CHARGE_GL_NAME_SEG5,

REQ_CCID_DSC.GL_NAME_SEG6 AS CHARGE_GL_NAME_SEG6

from

PO_REQUISITION_HEADERS_ALL PO_REQUISITION_HEADERS_ALL,

PO_REQUISITION_LINES_ALL PO_REQUISITION_LINES_ALL,

--DISC_SEGMENT4 DISTRIBUTION_LOCATION,

--DISC_SEGMENT4 TRAVEL_LOCATION,

DISC_EMPLOYEE_V DISC_EMPLOYEE_V,

PO_REQ_DISTRIBUTIONS_ALL PO_REQ_DISTRIBUTIONS_ALL ,

DISC_CCID_DSC_MV REQ_CCID_DSC,

MTL_CATEGORIES_V MTL_CATEGORIES_V

where

PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID = PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID

--AND PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID = DISTRIBUTION_LOCATION.SEGMENT(+)

AND to_char(PO_REQUISITION_LINES_ALL.TO_PERSON_ID) = DISC_EMPLOYEE_V."Employee_Num" (+)

--AND PO_REQUISITION_LINES_ALL.ATTRIBUTE8 = TRAVEL_LOCATION.SEGMENT(+)

AND PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID = PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID

AND REQ_CCID_DSC.CODE_COMBINATION_ID = PO_REQ_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID

AND MTL_CATEGORIES_V.CATEGORY_ID = PO_REQUISITION_LINES_ALL.CATEGORY_ID