, ,

Verifying Value Sets

— To Check Value Sets (Table Type)

SELECT *
  FROM apps.fnd_flex_validation_tables
 WHERE flex_value_set_id IN (
                             SELECT flex_value_set_id
                               FROM apps.fnd_flex_value_sets
                              WHERE flex_value_set_name =
                                                         ‘<VALUE SET NAME>’)
                                                       
— To Check Value Sets (Independent)

SELECT *
  FROM apps.fnd_flex_values
 WHERE flex_value_set_id IN (
                             SELECT flex_value_set_id
                               FROM apps.fnd_flex_value_sets
                              WHERE flex_value_set_name =
                                                         ‘<VALUE SET NAME>’)
ORDER BY flex_value         

/by
, ,

Query to fetch Responsibilities attached to a User

The below query will fetch the responsibilities assigned to a particular user.
SELECT
    fu.user_id,
    fu.user_name,
    fr.responsibility_name,
    fr.description,
    fa.application_name
FROM fnd_user fu,
     fnd_user_resp_groups g,
     fnd_application_tl fa,
     fnd_responsibility_tl fr
WHERE
     g.user_id(+) = fu.user_id
     AND g.responsibility_application_id = fa.application_id
     AND fa.application_id = fr.application_id
     AND g.responsibility_id = fr.responsibility_id
     AND fu.user_name  =UPPER(‘User_Name‘);
/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