add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, October 26

Oracle Reports6i

1.PO VENDORS REPORT
  1. Open report builder and in data model type the following query
    Select * from po_vendors;
  1. 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);

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

No comments: