, , , , , ,

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

Finding all unapplied receipts in AR

Select a.cash_receipt_id, a.receipt_number, a.Amount, SUM(b.AMOUNT_APPLIED) AS “Unapplied”
from apps. AR_CASH_RECEIPTS_ALL a,
apps. AR_RECEIVABLE_APPLICATIONS_ALL b
WHERE a.ORG_ID=’118′
AND a.CURRENCY_CODE=’USD’
AND a.cash_receipt_id=b.cash_receipt_id
AND b.STATUS=’UNAPP’
GROUP BY a.cash_receipt_id, a.receipt_number, a.Amount
/by