, ,

To Retriew the records from Org dependent Views

To Retriew the records from Org dependent Views on Sql Developer/Toad/Sql * Plus

begin
fnd_client_info.set_org_context(Org_id);
end;

/by
, , , , , ,

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_NUM
from 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 plla
where pha.org_id=’xxxx’
AND pha.org_id=pvsa.org_id
AND pv.vendor_id=pha.vendor_id
AND pvsa.vendor_id=pv.vendor_id
AND pha.po_header_id=pla.po_header_id(+)
–AND pha.po_header_id=plla.po_header_id
AND 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_NAME
From  apps. ORG_ORGANIZATION_DEFINITIONS a,
apps. HR_OPERATING_UNITS b,
apps. xle_entity_profiles c,
apps. gl_ledgers d
Where a.operating_unit=b.organization_id
AND c.legal_entity_id=a.legal_entity
AND 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_name
from 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 apt
where aps.invoice_id = ai.invoice_id
AND apt.term_id = ai.terms_id
AND po.vendor_id = ai.vendor_id
AND pos.vendor_site_id = ai.vendor_site_id
AND ai.org_id = ‘xxx’ — org id
AND 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 fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND ppf.effective_end_date = TO_DATE (’31/12/4712′, ‘DD/MM/RRRR’)
AND fu.user_id = fur.user_id
AND NVL (fur.end_date, SYSDATE + 1) > SYSDATE
AND fur.responsibility_id = fr.responsibility_id
AND fr.responsibility_name LIKE ‘%XXXX%’ – Replace with appropriate OU Qualifier
AND fr.LANGUAGE = ‘US’
ORDER BY fu.user_name, fr.responsibility_name
/by