Oracle Reports6i

1.PO VENDORS REPORT

1. Open report builder and in data model type the following query
   Select * from po_vendors;

2. Now design the following report

Now save the above report in this path D:\oracle\visappl\po\11.5.0\reports\US\vendor.pdf

2.AP Invoice Report

Now open Data model and write following query in query builder

SELECT aia.invoice_id,

aia.vendor_id,

aia.invoice_num,

aia.invoice_amount,

aia.amount_paid,

aia.invoice_date,

aia.INVOICE_TYPE_LOOKUP_CODE,

aid.dist_code_combination_id,

aid.LINE_TYPE_LOOKUP_CODE,

aid.amount,

aid.description,

aid.accounting_date,

aps.payment_num,

aps.gross_amount,

aps.PAYMENT_METHOD_LOOKUP_CODE,

gcc.segment1

|| '-'

|| gcc.segment2

|| '-'

|| gcc.segment3

|| '-'

|| gcc.segment4

|| '-'

|| gcc.segment5

FROM ap_invoices_all aia,

ap_invoice_distributions_all aid,

ap_payment_schedules_all aps,

gl_code_combinations gcc

WHERE aia.invoice_id = aid.invoice_id

AND aid.invoice_id = aps.invoice_id

AND aid.dist_code_combination_id = gcc.code_combination_id

AND aia.invoice_id = :p_invoice_id;

Now click on ok button .and arrange columns as follows

following user parameter will be created :

Now design the report as follows:

3. AR DETAILS REPORT

open data block and enter the following query

SELECT party.party_name CUSTOMER_NAME,

cust_acct.account_number CUSTOMER_NUMBER,

TRX.customer_trx_id customer_trx_id,

trx.term_id payment_term_id,

ps.class TRANS_TYPE,

ps.trx_number TRX_NUMBER,

types.name invoice_type,

ps.trx_date TRANSACTION_DATE,

NVL (ps.exchange_rate, 1) exchange_rate,

ps.invoice_currency_code trx_currency_code,

NVL (SUM (ps.amount_due_original), 0) TRANS_AMOUNT,

NVL (SUM (ps.amount_due_remaining), 0) TRANS_AMOUNT_REMAINING,

NVL (SUM (ps.amount_applied), 0) RECEIPT_AMOUNT,

TRX.primary_salesrep_id primary_salesrep_id

FROM ra_cust_trx_types_all types,

hz_cust_accounts cust_acct,

hz_parties party,

ar_payment_schedules_all ps,

ra_customer_trx_all trx

WHERE ps.customer_trx_id = trx.customer_trx_id

AND ps.customer_id = cust_acct.cust_account_id

AND cust_acct.party_id = party.party_id

AND ps.cust_trx_type_id = types.cust_trx_type_id

AND NVL (SUBSTR (party.party_name, 1, 50), 'N') <=

NVL (:P_CUSTOMER_NAME_TO,

NVL (SUBSTR (party.party_name, 1, 50), 'N'))

AND ps.org_id = types.org_id

AND trx.interface_header_attribute4 IS NOT NULL

AND TRUNC (ps.gl_date) <= :P_AS_OF_DATE

AND ps.gl_date_closed > :P_AS_OF_DATE

GROUP BY ps.class,

ps.trx_number,

ps.trx_date,

types.name,

ps.invoice_currency_code,

party.party_name,

cust_acct.account_number,

TRX.customer_trx_id,

trx.term_id,

TRX.primary_salesrep_id,

ps.status,

ps.payment_schedule_id,

NVL (ps.exchange_rate, 1)

Now arrange the fields as follows:

Following user parameters are created:

Now design the Layout as follows

Now run the report

To know the details open toad and enter as follows and copy the customer name

Now enter the details in parameter window

Now run the report following will appear

• Now save the above report and compile and
• Save it in AP_TOP folder
• And create concurrent executable and program and attach to request group
• And Run the Request

4. PO DETAILS REPORT

open data block and enter following query

SELECT pha.po_header_id po_hdr_id,

pha.segment1 po_num,

pha.type_lookup_code po_type,

pha.creation_date po_create_date,

pv.vendor_name supplier,

pvs.vendor_site_code supplier_site,

pvc.first_name || ',' || pvc.last_name supplier_contact_name,

pla.po_header_id,

pla.line_num linenum,

msi.segment1 item,

msi.description itemdesc,

pla.quantity qty,

pla.unit_price price,

(pla.quantity * pla.unit_price) lineprice

FROM po_headers_all pha,

po_vendors pv,

po_vendor_sites_all pvs,

po_vendor_contacts pvc,

po_lines_all pla,

mtl_system_items_b msi

WHERE pha.vendor_id = pv.vendor_id

AND pha.po_header_id = pla.po_header_id

AND pvs.vendor_id = pv.vendor_id

AND pvc.vendor_site_id = pvs.vendor_site_id

AND pla.item_id = msi.inventory_item_id

AND pla.org_id = msi.organization_id

AND pv.vendor_id = :p_vendor_id

Following user parameter will be created

Now design the lay out as follows:

Now run the report

Now run query to find vendor number in toad

Now pass the above vendor number

Now run the report

Invoice Voucher Report

Now open the data model and enter query

SELECT pv.vendor_name,

aia.invoice_type_lookup_code,

aia.invoice_num,

aia.invoice_id,

aia.voucher_num,

aia.invoice_amount,

aia.amount_paid,

aia.invoice_amount - aia.amount_paid balance_amount,

aia.TERMS_ID,

apt.name term_name,

aia.VENDOR_ID,

aia.EXCHANGE_DATE,

aiv.APPROVAL_STATUS_lookup_code,

aia.PAYMENT_AMOUNT_TOTAL

FROM ap_invoices_v aiv,

ap_invoices_all aia,

po_vendors pv,

ap_terms apt

WHERE aiv.invoice_id = aia.invoice_id

AND pv.vendor_id = aia.vendor_id

AND aia.terms_id = apt.term_id

AND aia.invoice_type_lookup_code IN ('STANDARD', 'AWT')

AND aiv.APPROVAL_STATUS_lookup_code = 'NEVER APPROVED'

AND aia.voucher_num = NVL (:voucher_number, aia.voucher_num)

Now following user parameter will be created

Now click on main section

set the properties as follows

Now design the layout as follows

Now run the report

now to find the voucher number type as follows

Note :

By default invoice voucher number does not exist .to enable the voucher number follow the steps :

Now click on

Now select the voucher number

Now enter the following details

now save the details

Now in toad type the query and find the voucher number

Now pass this voucher number in parameter window

######################################################

CREATE CONCURRENT PROGRAM EXECUTABLE

FND_PROGRAM.executable (executable => v_program_name,

application => Application_name_IN,

short_name => v_program_name,

description => 'Clopay HRMS File Permissions',

execution_method => 'Host',

execution_file_name => 'XHR_CHNG_OWNER',

subroutine_name => NULL,

icon_name => NULL,

language_code => 'US',

execution_file_path => NULL);

CREATE CONCURRENT PROGRAM

FND_PROGRAM.REGISTER(program => 'Clopay HRMS File Permissions',

application => Application_name_IN,

enabled => 'Y',

short_name => v_program_name,

description => 'Clopay HRMS File Permissions',

executable_short_name => v_program_name,

executable_application => Application_name_IN,

execution_options => NULL,

priority => NULL,

request_type => NULL,

request_type_application => NULL,

use_in_srs => 'Y',

allow_disabled_values => 'N',

run_alone => 'N',

output_type => 'TEXT',

enable_trace => 'N',

restart => 'Y',

nls_compliant => 'Y',

icon_name => NULL,

language_code => 'US',

mls_function_short_name => NULL,

mls_function_application => NULL,

incrementor => NULL);

CREATE CONCURRENT PROGRAM PARAMETERS

FND_PROGRAM.PARAMETER (

program_short_name => v_program_name,

application => Application_name_IN,

SEQUENCE => 1,

parameter => 'P_PATH',

description => 'Path',

enabled => 'Y',

value_set => '120 Characters',

default_type => NULL,

DEFAULT_VALUE => NULL,

REQUIRED => 'Y',

enable_security => 'N',

RANGE => NULL,

display => 'Y',

display_size => 30,

description_size => 100,

concatenated_description_size => 100,

PROMPT => 'File Path'

);

CONCURRENT PROGRAM ADDING TO REQUEST GROUP

FND_PROGRAM.add_to_group (program_short_name => v_program_name,

program_application => Application_name_IN,

request_group => request_group_name_IN,

group_application => Group_application_name_IN);

###############################################################