--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...
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...
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....
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...
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 = <'&...
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...
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....
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,...
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...
Subscribe to:
Posts (Atom)
