Duplicate Vendor List

--Duplicate Vendor List
select pv1.vendor_name,
pv2.vendor_name DUP_VENDOR_NAME,
pv1.segment1 VENDOR_ID,
pv2.segment1 DUP_VENDOR_ID,
pvsa1.vendor_site_code SITE_CODE,
pvsa2.vendor_site_code SITE_CODE,
pvsa1.address_line1,
pvsa2.address_line1 DUP_ADDRESS_LINE1,
pvsa1.zip
from po_vendors pv1,
po_vendors pv2,
po_vendor_sites_all pvsa1,
po_vendor_sites_all pvsa2
where pvsa1.vendor_site_id <> pvsa2.vendor_site_id
and substr(replace(pvsa1.address_line1, ' '),1,20) =
substr(replace(pvsa2.address_line1, ' '),1,20)
and pvsa1.zip = pvsa2.zip
and pv1.vendor_id = pvsa1.vendor_id
and pv2.vendor_id = pvsa2.vendor_id
and pv1.vendor_id <> pv2.vendor_id
and pvsa1.address_line1 <> 'YOUR ADDEDSS’
order by 1;

Read More

INFO: API's Present in Oracle Applications 11i/R12

Some of Commonly used API in EBS are:

• FND_PROGRAM.DELETE_EXECUTABLE( )
• FND_PROGRAM.REGISTER( )
• FND_PROGRAM.DELETE_PROGRAM( )
• FND_PROGRAM.PARAMETER( )
• FND_PROGRAM.DELETE_PARAMETER( )
• FND_PROGRAM.INCOMPATIBILITY( )
• FND_PROGRAM.DELETE_INCOMPATIBILITY( )
• FND_PROGRAM.REQUEST_GROUP( )
• FND_PROGRAM.DELETE_GROUP( )
• FND_PROGRAM.ADD_TO_GROUP( )
• FND_PROGRAM.REMOVE_FROM_GROUP( )
• FND_REQUEST.SUBMIT_REQUEST( )
• FND_CONCURRENT.WAIT_FOR_REQUEST( )
• FND_REQUEST.SET_PRINT_OPTIONS ( )
• FND_GLOBAL.USER_IDFND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
• FND_GLOBAL.LOGIN_ID
• FND_GLOBAL.CONC_LOGIN_ID
• FND_GLOBAL.PROG_APPL_ID
• FND_GLOBAL.CONC_PROGRAM_ID
• FND_GLOBAL.CONC_REQUEST_ID
• FND_PROFILE.PUT(name,value)
• FND_PROFILE.GET(name IN varchar2,value out varchar2)
• API’S IN APPS
PO
• PO_CUSTOM_PRICE_PUB
• PO_DOCUMENT_CONTROL_PUB
• PO_DOC_MANAGER_PUB
• PO_WFDS_PUB

AP
• AP_NOTES_PUB
• AP_WEB_AUDIT_LIST_PUB


INV
• INV_COST_GROUP_PUB
• INV_ITEM_CATALOG_ELEM_PUB
• INV_ITEM_CATEGORY_PUB
• INV_ITEM_PUB
• INV_ITEM_REVISION_PUB
• INV_ITEM_STATUS_PUB
• INV_LOT_API_PUB
• INV_MATERIAL_STATUS_PUB
• INV_MOVEMENT_STATISTICS_PUB
• INV_MOVE_ORDER_PUB
• INV_PICK_RELEASE_PUB
• INV_PICK_WAVE_PICK_CONFIRM_PUB
• INV_RESERVATION_PUB
• INV_SERIAL_NUMBER_PUB
• INV_SHIPPING_TRANSACTION_PUB

Read More

More4Apps

More4Apps have a range of wizards for loading transactions into Oracle E-Business Suite. You can download information into Excel from Oracle, make changes, perform validation within the spreadsheet and upload back into Oracle. Alternatively, you can import files into Excel or key directly into the spreadsheet, before loading into Oracle.

The wizards use the Oracle API's or Open Interfaces, ensuring your support agreement is unaffected. These wizards do not require development to get up-and-running.

Check out the website www.more4apps.com

Read More

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