AR Useful Query

Sharing few queries Which i have came across while browsing :-


TRACE WHICH AR INVOICE IS LINKED WITH WHICH GL ENTRY.

Linking Table between AR to GL :

In R12, try using the query below:

SELECT b.NAME batch_name,
b.description batch_description,
h.je_category,
h.je_source,
h.period_name je_period_name,
h.NAME journal_name,
h.status journal_status,
h.description je_description,
l.je_line_num line_number
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
ra_customer_trx_all rct,
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =''





11i

select gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, trunc(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
from apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
where /*gjh.period_name IN ('OCT-2008','NOV-2008')
and */glcc.code_combination_id = gje.code_combination_id
and gjh.je_batch_id = gjb.je_batch_id
and gjh.je_header_id = gje.je_header_id
and gjh.period_name = gjb.default_period_name
and gjh.period_name = gje.period_name
and gjjlv.period_name = gjh.period_name
and gjjlv.je_batch_id = gjh.je_batch_id
and gjjlv.je_header_id = gjh.je_header_id
and gjjlv.line_je_line_num = gje.je_line_num
and gjjlv.line_code_combination_id = glcc.code_combination_id
and gjjlv.line_reference_4 = rcta.trx_number
and rcta.cust_trx_type_id = rctype.cust_trx_type_id
and rcta.org_id = rctype.org_id
and ra.customer_id = rcta.bill_to_customer_id

Happy Reading.....................

AR Tables 11i

RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLES_TRX_ALL
AR_RECEIVABLE_APPLICATIONS_ALL


transaction types stored in RA_CUST_TRX_TYPES_ALL

RA_CUSTOMER_TRX_ALL
--------------------------


The primary key for this table is CUSTOMER_TRX_ID.

-If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID
 stores the customer transaction identifier of the invoice you credited,otherwise it is null.

-invoice against a commitment  INITIAL_CUSTOMER_TRX_ID,otherwise it is null.

-COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No




RA_CUSTOMER_TRX_LINES_ALL
---------------------------------


-The primary key for this table is CUSTOMER_TRX_LINE_ID
-QUANTITY_ORDERED stores the amount of product ordered
-QUANTITY_INVOICED stores the amount of product invoiced

**For invoices entered through the window, QUANTITY_ORDERED and QUANTITY_INVOICED must be the same.
  For invoices imported through AutoInvoice, QUANTITY_ORDERED and QUANTITY_INVOICED can be different**

-If you enter a credit memo, QUANTITY_CREDITED stores the amount of product credited
-UOM_CODE stores the unit of measure code as defined in MTL_UNITS_OF_MEASURE
-UNIT_STANDARD_PRICE stores the list price per unit for this transaction line.
 UNIT_SELLING_PRICE stores the selling price per unit for this transaction line.
 For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE can be different.
-**LINE_TYPE differentiates between the different types of lines that are stored in this table.
LINE points to regular invoice lines that normally refer to an item. **

--For every row in this table that belongs to a complete transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y),
  there must be at least one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting information),
  even for non–postable transactions.


RA_CUST_TRX_LINE_GL_DIST_ALL
----------------------------------------------

The primary key for this table is CUST_TRX_LINE_GL_DIST_ID.

ACCOUNT_SET_FLAG is Y if this row is part of an account set

AR_PAYMENT_SCHEDULES_ALL
-----------------------------------------------

Oracle Receivables groups different transactions bythe column CLASS.
These classes include invoice (INV),
debit memos(DM), guarantees (GUAR),
credit memos (CM), deposits (DEP),
chargebacks (CB), and receipts (PMT).

The primary key for this table is PAYMENT_SCHEDULE_ID

--AR_PAYMENT_SCHEDULES_ALL joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries
such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits.

--AR_PAYMENT_SCHEDULES_ALL uses the foreign key CUSTOMER_TRX_ID to join to the RA_CUSTOMER_TRX_ALL
table for these transactions.

--AR_PAYMENT_SCHEDULES_ALL joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions using the
foreign key CASH_RECEIPT_ID.

--When a receiptis applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS and AMOUNT_DUE_REMAINING.

--STATUS changes from ’OP’ to ’CL’for any transaction that has an AMOUNT_DUE_REMAINING value of 0(Zero)

--The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. If this amount is negative, then this column indicates the credit balance amount currently available for this customer.

--For invoices with split terms, one record is created in RA_CUSTOMER_TRX_ALL and one record is stored in AR_PAYMENT_SCHEDULES_ALL for each installment.

--In AR_PAYMENT_SCHEDULES_ALL, DUE_DATE and AMOUNT_DUE_REMAINING can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column.



NOTE:
If you create a debit memo reversal when you reverse a receipt, Oracle Receivables creates a new payment schedule record for the
debit memo and fills in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was reversed.
Oracle Receivables creates a new payment schedule record when you create a chargeback in the Receipts window.
ASSOCIATED_CASH_RECEIPT_ID is the cash receipt of the payment you entered when you created the chargeback in this window.
GL_DATE_CLOSED indicates the general ledger date on which your transaction was closed.

AR_RECEIVABLES_TRX_ALL
------------------------------

Adjustment, Miscellaneous Cash, and Finance Charge

The primary key for this table is RECEIVABLES_TRX_ID.

AR_RECEIVABLE_APPLICATIONS_ALL
-----------------------------------


Possible statuses of your applications include APP, UNAPP, ACC, and UNID

There are two kinds of applications: CASH and CM (for credit memo applications). This is stored in the column APPLICATION_TYPE.

Salary Sum Query

Sum of all earnings in payroll:

/* Formatted on 2011/10/27 13:36 (Formatter Plus v4.8.8) */

SELECT pee.assignment_id, pee.element_entry_id, pee.element_link_id,
pel.element_type_id, pet.element_name, pettl.reporting_name,
DECODE (pet.element_name,
'Basic_New', 'Basic Salary',
'HRA', 'House Rent Allowance',
pet.element_name
) display_name,
(SELECT MAX (screen_entry_value)
FROM pay_element_entry_values_f
WHERE element_entry_id = pee.element_entry_id) screen_entry_value
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_element_classifications pec
WHERE pee.assignment_id = :assignment_id
AND TRUNC (SYSDATE) BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND TRUNC (SYSDATE) BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pel.element_type_id = pet.element_type_id
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.processing_type = 'R'
AND pet.element_type_id = pettl.element_type_id
AND TRUNC (SYSDATE) BETWEEN peev.effective_start_date
AND peev.effective_end_date*/
AND pet.classification_id = pec.classification_id
AND pec.classification_name = 'Earnings'
ORDER BY DECODE (pet.element_name, 'Basic_New', 1, 'HRA', 2, 3)

FOrmsPersonalization:To restrict a receipt method LOV for a responsibility

In Order Management Responsibility go to quick sales order menu

• create a sales order
• click on actions
• Choose payments
• form main menu go to help-diagnostics-custom code-personalize

1.Add a Sequence with description,

 

 

Under Condition ;
trigger event ; when_new_item_instance
Trigger_object ; blockname.filed-name(In which u need the personalization)
Condition:optional,can give any default values e.g:
Block_name.field_name=’Value’

 

e.g: WHEN-NEW-ITEM-INSTANCE;
OE_PAYMENTS.RECEIPT_METHOD:OE_PAYMENTS.PAYMENT_TYPE_CODE='CASH'
Under Level Choose the responsibility that you want to assign.

 

 

 

Now go to actions tab

 

2.The first sequence is built-in and the second sequence is property for a LOV
e.g:Seq 10
type;Builtin
In Builtin Type Choose;Create record group for query
Argument;Enter your query
NOTE:
1. If u r changing the existing record group the you need to check the exiting record group query and develop your query by choosing same columns and with your own condition appropriately.
2. U need to refer the the LOV that u r Personalizing.
Group Name;Give a custom name
E.g. query
**-For type Cash**
SELECT rm.NAME,rm.receipt_method_id
/*Have choosen the same columns as per the default LOV Record group query and changed the where clause as per my requirement*/
FROM
ar_receipt_methods rm,
oe_payments b,
ar_receipt_method_accounts_all rma,
fnd_lookup_values flv,
ce_bank_acct_uses_all cba
where
rm.receipt_method_id=b.receipt_method_id
AND rm.receipt_method_id = rma.receipt_method_id
AND flv.lookup_type like '%PAYMENT_METHODS%' --Existing LOV Name--
AND rma.remit_bank_acct_use_id = cba.bank_acct_use_id and
rma.org_id = cba.org_id
AND flv.enabled_flag = 'Y'
AND Flv.LOOKUP_CODE not IN('CCR')--Condition for Where Clause--
AND rm.receipt_method_id IN('2012')
GROUP BY rm.NAME,rm.receipt_method_id

 

3.Create property for the built-in type

 

My desp; Restrict Receipt Method for cash
Object_type;Cash

 

Target_Object;Payment_Methods
Property_name;Group Name
Value;Give the custom name that u have given for the Builtin Group Name

 

And ITS DONE !!!!

 

 

 

 

 

FND_CANONICAL

In HRMS we do use date with timestamp for EIT ans SIT segments.

To change the format od the date we can use fnd_date.canonical_to_date default function:

e.g:

before using fnd_date function

SELECT pei_information2 Visit_date from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

After using fnd_date function:

SELECT fnd_date.canonical_to_date(pei_information2) from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 And you can use to_chat over uit to make it in words as

SELECT TO_CHAR(fnd_date.canonical_to_date(pei_information2),'Month ddTH, YYYY') from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 

Find log using Request id

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = 515172

Adding LOV list to forms

1.Create a record group with the query required for lov list.
2.Create a LOV with the created Record group and give the subclass information as LOV
3.Map the field from the form in LOV mapping property field.