, , , , , ,

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

Important Account Receivables Tables

RA_CUSTOMER_TRX_ALL 
Table Or View: Table
Important columns: 

CUSTOMER_TRX_ID
BATCH_SOURCE_ID
TRX_NUMBER

Description: 

Header-level information about invoices, debit memos, chargebacks, commitments and credit memos.

RA_CUSTOMER_TRX_LINES_ALL  
 Table Or View: View

Important columns: 

CUSTOMER_TRX_ID
CUSTOMER_TRX_LINE_ID
LINE_TYPE : Line, TAX or Freight
LINE_NUMBER
INVENTORY_ITEM_ID
QUANTITY_ORDERED
QUANTITY_CREDITED
QUANTITY_INVOICED
UNIT_STANDARD_PRICE
UNIT_SELLING_PRICE
SALES_ORDER
SALES_ORDER_REVISION
SALES_ORDER_LINE
TAX_EXEMPT_FLAG
SALES_TAX_ID
WAREHOUSE_ID
ORG_ID : Organization identifier

Description: 

RA_CUSTOMER_TRX_LINES_ALL contians the line level information of invoices (header informations are stored in RA_CUSTOMER_TRX_ALL).
This view contains information for all the type of lines : Line, TAX and Freight.

RA_CUST_TRX_TYPES_ALL

Table Or View: Table
Important columns: 

CUST_TRX_TYPE_ID NUMBER : Transaction type identifier
NAME : Transaction type Name
TYPE
POST_TO_GL : Indicates if transactions should be posted to the General Ledger. Y for yes, N otherwise.
ACCOUNTING_AFFECT_FLAG : Indicates if transactions affect open receivable balances. Y for yes, N otherwise.
CREDIT_MEMO_TYPE_ID : Transaction type identifier of subsequent credit memos
ORG_ID NUMBER : Organization identifier

Description: 


The RA_CUST_TRX_TYPES_ALL table stores information about each transaction type that is used for invoices, commitments, bills receivable, and credit memos. Each row includes AutoAccounting information as well as standard defaults for the invoices that result.
The POST_TO_GL column stores Y or N to indicate if this transaction can post to your General Ledger.
The ACCOUNTING_AFFECT_FLAG column stores Y or N to indicate if this transaction can update your open receivables balances. If the ACCOUNTING_AFFECT_FLAG column is Y, you can see this transaction in your agings.
The TYPE column contains:
INV – Invoice
CM – Credit Memo
DM – Debit Memo
DEP – Deposit
GUAR – Guarantee
If AutoAccounting is based on transaction type, the GL_ID_REV, GL_ID_FREIGHT, and GL_ID_REC columns store the default revenue, freight, and receivables accounts. The STATUS and CREDIT_MEMO_TYPE_ID columns are required even though they are null allowed.
The primary key for this table is CUST_TRX_TYPE_ID.

/by