, , , , , ,

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
, , , , , ,

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_NUM
from apps.ap_bank_accounts_all a,
apps.ap_bank_branches b,
apps.ap_bank_account_uses_all c,
apps. PO_VENDORS d
where a.org_id=xxxx
AND a.bank_branch_id = b.bank_branch_id
AND a.bank_account_id = c.external_bank_account_id
AND c.vendor_id=d.vendor_id
 AND c.end_date IS NULL
/by
, , , , ,

I/C AP & AR Invoice from SO

Select a.order_number Sales_Order, d.trx_number IC_AR_INVOICE, e.INVOICE_NUM IC_AP_INVOICE, g.segment1 PO_NUMBER,
b.ordered_item, b.invoice_interface_status_code,
b.invoiced_quantity, b.flow_status_code
From apps. oe_order_headers_all a,
     apps. oe_order_lines_all b,
     apps. HR_ALL_ORGANIZATION_UNITS c,
     apps. RA_CUSTOMER_TRX_ALL d,
     apps. AP_INVOICES_ALL e,
     apps. OE_DROP_SHIP_SOURCES f,
     apps. PO_HEADERS_ALL g
Where
b.header_id = a.header_id
AND f.LINE_ID = b.line_id
AND d.org_id=c.organization_id
AND a.order_number=’10090178′ //SO number
AND c.name LIKE ‘XXX%UK%’ //Operating unit
AND d.ct_reference= to_char(a.order_number)
AND e.INVOICE_NUM = d.trx_number
AND f.po_header_id=g.po_header_id
/by
, , , , ,

Reason for invoices rejected by Payables Open Interface Import

Below simple code can be used to check reason for invoices rejected by  Payables Open Interface Import.
Select aii.Invoice_num , air.PARENT_TABLE, air.REJECT_LOOKUP_CODE, air.parent_id
From apps. AP_INTERFACE_REJECTIONS air,
apps. AP_INVOICE_LINES_INTERFACE aili,
apps. AP_INVOICES_INTERFACE aii
WHere aii.invoice_id=aili.invoice_id
AND  aili.invoice_id=air.parent_id
AND aii.Invoice_num = ‘Invoice Number’
/by