, , , , , ,

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

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

Important Account Payables Tables -Part 2

AP_INTERFACE_REJECTIONS
Table Or View:  Table
Important columns: 

PARENT_TABLE : Reference to table in which the rejection occurred (AP_INVOICES_INTERFACE or AP_INVOICE_LINES_INTERFACE)
PARENT_ID NUMBER : Reference to invoice or invoice line identifier which was rejected (INVOICE_ID or INVOICE_LINE_ID)
REJECT_LOOKUP_CODE : Invoice rejection reason

Description: 

AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and
AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.
If you use Oracle e-Commerce Gateway, you can pass information from this table to your suppliers by submitting the Payables Open Interface Outbound Advice for rejected data.
You can purge data in this table by using the Payables Open Interface Purge.

AP_CHECKS_ALL

Table Or View: Table
Important columns: 

AMOUNT : Payment amount
BANK_ACCOUNT_ID : longer used
BANK_ACCOUNT_NAME : Bank account name
CHECK_ID NUMBER : Payment identifier
CHECK_NUMBER : Payment number
VENDOR_NAME :  Supplier name
VENDOR_SITE_CODE :  Supplier site code
PAYMENT_TYPE_FLAG : Type of payment
A – Payment Process Request
M – Manual
Q – Quick payment
R – Reunfund

Description: 

AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers.  You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment.
Your Oracle Payables application stores address information for all payments. If you allow changes to the supplier payment address on manual payments or Quick payments, your Oracle Payables application maintains the new address information in this table. Your Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and BANK_ACCOUNT_TYPE for the supplier’s bank information when you use the Electronic payment method. Your Oracle Payables application stores a dummy value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be treated as a foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.
Payments are linked with invoices through AP_INVOICE_PAYMENTS_ALL

AP_SUPPLIERS

Table Or View: Table
Important columns: 

VENDOR_ID :  Yes Supplier unique identifier
SEGMENT1 : Supplier number

Description: 

AP_SUPPLIERS stores information about your supplier level attributes. Each row includes the purchasing, receiving, invoice, tax, classification, and general information. Oracle Purchasing uses this information to determine active suppliers.
This table replaces the old PO_VENDORS table. The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

 
PO_VENDORS
Table Or View: View
Important columns: 

VENDOR_ID
SEGMENT1

Description: 

PO_VENDORS is a view which contains selected columns of AP_SUPPLIERS

 

/by