Entries by Oracle ERP Apps Guide

, , , , , ,

Query : PO, Supplier and Inventory Item

Select pha.segment1 “PO_NUMBER”,pla.line_num,plla.shipment_num,pv.segment1 “Oracle_Supplier_number”, msib.segment1 “item_number”,pla.item_revision, pla.item_description,NULL as “Delivery_date”, NULL as “Blank Column”, pla.unit_price, pha.rate,plla.Quantity_received,plla.Quantity_rejected,(pla.unit_price*pla.Quantity) as “Line_Amount”, NULL as “ITEM_COST”, NULL as “Amount”, pla.VENDOR_PRODUCT_NUMfrom apps. PO_HEADERS_ALL pha,apps. PO_VENDORS pv,apps. PO_VENDOR_SITES_ALL pvsa,apps. PO_LINES_ALL pla,apps. MTL_SYSTEM_ITEMS_B msib,apps. PO_LINE_LOCATIONS_ALL pllawhere pha.org_id=’xxxx’AND pha.org_id=pvsa.org_idAND pv.vendor_id=pha.vendor_idAND pvsa.vendor_id=pv.vendor_idAND pha.po_header_id=pla.po_header_id(+)–AND pha.po_header_id=plla.po_header_idAND plla.po_line_id(+)=pla.po_line_id–AND msib.organization_id=’xxxx’AND pla.item_id=msib.inventory_item_id(+)Order by pha.segment1

/by
, , , , , ,

Inventory Organization and corresponding Operating Unit, Ledger and Legal Entity

Select a.organization_id, a.organization_code, a.organization_name, a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,a.legal_entity,c.name LE_NAMEFrom  apps. ORG_ORGANIZATION_DEFINITIONS a,apps. HR_OPERATING_UNITS b,apps. xle_entity_profiles c,apps. gl_ledgers dWhere a.operating_unit=b.organization_idAND c.legal_entity_id=a.legal_entityAND d.ledger_id=a.set_of_books_id

/by
, , , , , ,

AP invoice details with supplier name, payment terms

Select DISTINCT(aps.invoice_id), ai.SOURCE, ai.invoice_num, aps.due_date, aps.gross_amount, aps.hold_flag, aps.payment_method_lookup_code, aps.payment_status_flag, aps.org_id, aps.inv_curr_gross_amount,ai.invoice_type_lookup_code invoice_type, ai.invoice_date, po.vendor_name, pos.vendor_site_code_alt legacy_system_site_number, ai.invoice_amount,ai.invoice_currency_code, ai.exchange_rate, ai.payment_method_lookup_code, ai.pay_group_lookup_code, ai.terms_id, ai.doc_sequence_value, ai.doc_category_code,ai.validated_tax_amount,ai.payment_currency_code,ai.pay_curr_invoice_amount,ai.invoice_currency_code,ai.invoice_amount, apt.NAME payment_terms_namefrom apps. AP_PAYMENT_SCHEDULES_ALL aps,apps.ap_invoices_all ai,apps.po_vendors po,apps.po_vendor_sites_all pos,apps. AP_TERMS_TL aptwhere aps.invoice_id = ai.invoice_idAND apt.term_id = ai.terms_idAND po.vendor_id = ai.vendor_idAND pos.vendor_site_id = ai.vendor_site_idAND ai.org_id = ‘xxx’ — org idAND apt.enabled_flag=’Y’AND apt.language=’US’

/by
, , , , , ,

User name, Employee & Associated responsibilities

SELECT fu.user_name “User Login”, fu.description “Role Description”,fu.start_date “Login Start Date”, fu.end_date “Login End Date”,fu.email_address “E-Mail Associated”, fu.employee_id “Employee Id”,ppf.employee_number “Employee Number”, ppf.full_name “Full Name”,hou.NAME “Business Group”,fr.responsibility_name “Responsibility Associated”,fur.start_date “Association Start Date”,fur.end_date “Association End Date”FROM apps.fnd_user fu,apps.per_all_people_f ppf,apps.hr_all_organization_units hou,apps.fnd_user_resp_groups_all fur,apps.fnd_responsibility_tl frWHERE ppf.person_id = fu.employee_idAND hou.organization_id = ppf.business_group_idAND ppf.effective_end_date = TO_DATE (’31/12/4712′, ‘DD/MM/RRRR’)AND fu.user_id = fur.user_idAND NVL […]

/by
, , , , , ,

Supplier , Supplier Bank and Supplier bank branch – Oracle Apps 11i

Select a.bank_account_name, a.bank_account_num, a.iban_number, a.bank_account_type, a.currency_code, a.bank_account_name_alt, a.org_id, a.creation_date,b.bank_name, b.bank_branch_name, b.bank_num , b.eft_swift_code, b.address_line1, b.address_line2, b.city, b.county, b.state, b.zip, b.country, c.vendor_id,d.vendor_name,d.segment1 AS SUPPLIER_NUMfrom apps.ap_bank_accounts_all a, apps.ap_bank_branches b,apps.ap_bank_account_uses_all c,apps. PO_VENDORS dwhere a.org_id=xxxxAND a.bank_branch_id = b.bank_branch_id AND a.bank_account_id = c.external_bank_account_idAND c.vendor_id=d.vendor_id AND c.end_date IS NULL

/by