Query : Invoices on hold

select distinct
ai.invoice_num,ai.invoice_amount,ai.invoice_date
,ai.invoice_received_date,ah.hold_lookup_code,ah.hold_reason,pv.vendor_name,pv.segment1 vendor_number
,ph.segment1 po_number
,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6
--,aid.AMOUNT
from ap_invoices_all ai
,ap_holds_all ah
,po_vendors pv
,po_headers_all ph
,ap_invoice_distributions_all aid
,po_distributions_all pda
,gl_code_combinations gcc
where 1=1
and ai.invoice_id = ah.invoice_id
and ai.vendor_id = pv.vendor_id
--and ai.po_header_id = ph.po_header_id(+)
and ph.PO_HEADER_ID = pda.PO_HEADER_ID
and ah.invoice_id = aid.invoice_id
and aid.po_distribution_id = pda.po_distribution_id
and aid.dist_code_combination_id = gcc.code_combination_id

Read More

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