1.PO VENDORS REPORT
- Open report builder and in data model type the following querySelect * from po_vendors;
- 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:
Post a Comment