Query : R12 Link ap_invoice_distributions_all to a GL Header via SLA

SELECT c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num, te.source_id_int_1, te.application_id, te.entity_id,
h.je_source,
h.je_category,
i.gl_date,
s.vendor_name,
s.segment1 as supplier_no,
l.event_class_code as event_class, i.invoice_id, ad.invoice_distribution_id,
i.invoice_num AS transaction_number,
i.invoice_date,
initcap(jl.description) description,
jl.accounted_dr as debit,
jl.accounted_cr as credit,
Nvl(Jl.Accounted_Dr, 0) - Nvl(Jl.Accounted_Cr, 0) Net_Amount
From Apps.Gl_Je_Headers H,
apps.gl_je_lines jl,
Apps.Gl_Code_Combinations C,
apps.gl_import_references r,
apps.xla_ae_lines al,
Apps.Xla_Ae_headers Ah,
apps.xla_distribution_links l,
apps.ap_invoices_all i,
apps.ap_invoice_distributions_all ad,
apps.ap_suppliers s,
apps.xla_events e,
apps.xla_transaction_entities te
where ad.accounting_date between :startdate and :enddate
and c.code_combination_id = 6429
and jl.description != 'GB VAT - STANDARD TAX'
and ad.line_type_lookup_code = 'ITEM'
AND jl.je_header_id = h.je_header_id
AND jl.code_combination_id = c.code_combination_id
and al.gl_sl_link_id = r.gl_sl_link_id
and al.ae_header_id = ah.ae_header_id
and al.application_id = ah.application_id
and ah.application_id = e.application_id
and ah.event_id = e.event_id
and e.application_id = te.application_id(+)
and e.entity_id = te.entity_id(+)
AND r.je_header_id = jl.je_header_id
AND r.je_line_num = jl.je_line_num
AND l.ae_header_id = al.ae_header_id
and l.ae_line_num = al.ae_line_num
and l.applied_to_source_id_num_1 = i.invoice_id
and l.source_distribution_id_num_1 = ad.invoice_distribution_id
and ad.invoice_id = i.invoice_id
and i.vendor_id = s.vendor_id
order by i.gl_date desc

Read More

Query : Transactions Posted from AR TO GL

SELECT l.subledger_doc_sequence_value "Doc Number",
l.effective_date "GL Date",
l.accounted_dr "Debit",
l.accounted_cr "Credit",
l.description "Description",
l.reference_4 "AR Number",
l.reference_9 "AR Type"
FROM gl_je_lines l, gl_je_headers h
WHERE je_source = 'Receivables'
AND h.je_header_id = l.je_header_id
AND h.set_of_books_id =
AND h.period_name = <'>

Read More

Query : To Extract revenue distribution lines in AR

SELECT distinct c.customer_name,
c.customer_number,
c.customer_id,
t.customer_trx_id,
t.trx_number,
ct.NAME invoice_type,
l.line_number,
t.org_id,
cc.segment1,
cc.segment2,
cc.segment3,
cc.segment4,
cc.segment5,
cc.segment6,
d.gl_date,
d.cust_trx_line_gl_dist_id,
d.code_combination_id,
d.account_class
FROM ra_cust_trx_types_all ct,
ra_customers c,
ra_customer_trx_all t,
ra_customer_trx_lines_all l,
gl_code_combinations cc,
ra_cust_trx_line_gl_dist_all d
WHERE 1 = 1
AND t.cust_trx_type_id = ct.cust_trx_type_id
AND t.bill_to_customer_id = c.customer_id
AND d.customer_trx_id = t.customer_trx_id
AND d.customer_trx_line_id = l.customer_trx_line_id(+)
AND d.code_combination_id = cc.code_combination_id
AND TRUNC (d.gl_date) >= TO_DATE ('01-01-2009', 'DD-MM-YYYY')
AND d.posting_control_id = -3
AND d.account_set_flag = 'N'
AND d.account_class = 'REV'

Read More

Know Oracle Payroll Tables

All the HRMS/Payroll tables that are date-tracked will must have two columns:-

Effective_start_date

Effective_end_date

All the objects in Oracle HRMS or Payroll that end with _x have a where clause where sysdate between Effective_start_date AND Effective_end_date, therefore you can use the views that return records as of SYSDATE.

The primary keys of the date tracked columns includes Effective_start_date & Effective_end_date.

PAY_ELEMENT_TYPES_F - Payroll Elements

Firstly, we create some elements, which get created in table PAY_ELEMENT_TYPES_F. The primary key is a combination of element_type_Id along with Date Track columns.

When will you join to pay_element_types_f
To display the name of Element in Reports or when you need develop adhoc query for payroll runs. Normally when Payroll is run , the results are stored in PAY_RUN_RESULTS, which stores a reference to element_type_Id.

2.PAY_ELEMENT_LINKS_F - Payroll Element Links

This table make payroll elements eligible to a group of people, you create Element Links.The Primary key is ELEMENT_LINK_ID with date-track columns.

When will you commonly use element_link_Id ?

1. When querying on Element Entry[PAY_ELEMENT_ENTRIES_F], a join can be made using ELEMENT_LINK_ID

2. The reason Oracle uses ELEMENT_LINK_ID in Element Entry to work out Costing Segments based on Payroll Costing Hierarchy.

3.PER_ALL_PEOPLE_F - Employee record

It is well known that Employee records are stored in PER_ALL_PEOPLE_F. Its a date track table with primary key being person_Id. This table also has party_Id, because Oracle creates a party in TCA as soon as a record in per_all_people_f gets created.

Main usage of per_all_people_f:-

1. To get the name of the person
2. To get the date of birth or tax Id of the person
Note:- The application uses PER_PEOPLE_F, as that is a secured view layer on top of PER_ALL_PEOPLE_F

4.PER_ALL_ASSIGNMENTS_F - Assignment table

This is the most central table in Oracle Payroll. Payroll engine uses this table as the main driver.

Why so: Because Element Entries are stored against Assignment record.

This table is date-tracked, with primary key being assignment_Id

Usage of per_all_assignments_f?

1. Find position_Id, hence position, or grade, the organization for the persons assignment.

2. It has foreign key to person_id. Each person Id can have no more than one primary assignment at any given point in time.

3. Pay run results and also the pay_assignment actions refers to this table.

5.PER_PERSON_TYPES - Person type

This is the master table for Person Types. Some examples of Person Types are Employees, Casuals, Applicants etc.

The primary key is person_type_id.

But please do not try joining this with person_type_id in per_all_people_f.

Instead join that to per_person_type_usages_f

_x will give you person_type usage as of SYSDATE.

For any other date, use the classic p_date between effective_start_date and effective_end_date.
6.PAY_ELEMENT_ENTRIES_F & PAY_ELEMENT_ENTRY_VALUES_F

This Tables effected when element entry is done,two tables get inserted into when fresh Element Entries are created.

PAY_ELEMENT_ENTRIES_F

Each Element that gets attached to an Assignment will have an entry in PAY_ELEMENT_ENTRIES_F.

For each assignment you will have one or more records in PAY_ELEMENT_ENTRIES_F table.

It is logical that PAY_ELEMENT_ENTRIES_F has following columns

Assignment_id

Element_link_id

ELEMENT_TYPE_ID

This table is date-tracked too. Please do not ask my where there was a need to store both ELEMENT_TYPE_ID and also ELEMENT_LINK_ID in this table.

Just storing the ELEMENT_LINK_ID could suffice. However, i guess Oracle did so for Performance reasons.

7.PAY_ELEMENT_ENTRY_VALUES_F

This table stores a reference to PAY_ELEMENT_ENTRIES_F.

This table is date-tracked, and its primary key is INPUT_VALUE_ID.

Hope this helps

Read More

Query for Customer Receipt Details

SELECT acra.cash_receipt_id,
DECODE (acra.TYPE,
'cash', 'cash receipt receipt',
'misc', 'miscellaneous',
acra.TYPE
) receipt_type,
acra.currency_code, acra.doc_sequence_value receipt_number,
acra.receipt_number reference_number,
TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
acra.misc_payment_source, hca.account_number customer_no,
NVL (acra.amount, 0) entered_amount,
NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
abb.bank_name, abb.bank_branch_name, acra.comments description
FROM ar_cash_receipts_all acra,
ar_receipt_methods arm,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_cust_accounts hca,
hz_parties hp
WHERE acra.pay_from_customer = hca.cust_account_id(+)
AND acra.org_id = abaa.org_id(+)
AND hca.party_id = hp.party_id(+)
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.remittance_bank_account_id = abaa.bank_account_id
AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;

Read More

Query : Supplier Invoice Details

selectap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount Invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name--,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account
from ap_invoices_all ap,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
ap_terms_tl at,
gl_code_combinations gcc
where alc.lookup_type='INVOICE TYPE'
and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE
and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'
and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE
and alc2.lookup_type='PAYMENT METHOD'
and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE
and ap.invoice_id=aid.invoice_id
and ap.vendor_id = pv.vendor_id
and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=pvs.vendor_id
and ap.vendor_site_id=pvs.vendor_site_id
--and ap.terms_id=at.term_id(+)
and ap.invoice_num=

Read More

Query : To find DataBase Locking

SELECT o.object_name,
l.oracle_username,
l.os_user_name,
s.machine,
s.program,
DECODE(l.locked_mode,0,'None',1,'NULL',2,'Row-S(SS)',3,'Row-X(SX)',
4,'Share',5,'S/Row-X(SSX)',6,'Exclusive(X)') locked_mode,
s.sid,
s.serial#
FROM all_objects o,v$locked_object l,v$session s
WHERE l.session_id = s.sid
AND l.object_id=o.object_id

Read More