AP- Invoice History Report (APXINHIS.rdf)This report gives all the details suppliers wise. Please add the following columns: Name of the Bank, Bank Account Number, Payment Method – (EDI, Check etc)Steps:

1) Download the original report though ftp to the local folder from /apps/visappl/ap/11.5.0/reports/US. The name of the report is APXINHIS.rdf.

2) Save the original report as APXINHIS_ORG.rdf.3) Open APXINHIS.rdf in report builder.4) Modify the query as follows:

SELECTpv.vendor_name C_VENDOR,pvs.vendor_site_code C_VENDOR_SITE,i.invoice_num C_INVOICE_NUMBER,i.invoice_date C_TRANSACTION_DATE,i.invoice_type_lookup_code C_TRANSACTION_TYPE,alc.displayed_field C_TRANSACTION_TYPE_FIELD,i.payment_currency_code C_CURR,decode (i.doc_sequence_value,NULL,i.voucher_num,i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER, /*Bug fix 894310*/f2.name C_DOC_SEQUENCE_NAME,decode(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.original_prepayment_amount*i.payment_cross_rate, i.payment_currency_code),0), nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))

C_TOTAL_INVOICE_AMT,ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER,ck.check_number C_TRANSACTION_NUMBER,f.name C_DOC_SEQUENCE_NAME,aip.accounting_date C_TRANSACTION_DATE,apd.line_type_lookup_code C_TRANSACTION_TYPE,ck.currency_code C_CURR,-1*apd.amount C_TRANSACTION_AMT,ABB.BANK_NAME C_BANK_NAME,aip.Bank_Account_Num C_BANK_ACCOUNT_NUM,i.payment_method_lookup_code C_PAYMENT_METHODFROM po_vendors pv, po_vendor_sites pvs, ap_invoices i, ap_checks ck, fnd_document_sequences f, fnd_document_sequences f2, ap_invoice_payments aip, ap_payment_distributions apd, ap_lookup_codes alc, AP_BANK_BRANCHES ABBWHERE pv.vendor_id = pvs.vendor_idAND i.vendor_id = pv.vendor_idAND i.vendor_site_id = pvs.vendor_site_idAND i.invoice_id = aip.invoice_id (+)AND i.invoice_type_lookup_code = alc.lookup_codeAND alc.lookup_type = 'INVOICE TYPE'AND apd.invoice_payment_id (+)= aip.invoice_payment_idAND ck.check_id (+)= aip.check_idAND f.doc_sequence_id (+) = ck.doc_sequence_idAND f2.doc_sequence_id(+)= i.doc_sequence_idAND decode(i.invoice_type_lookup_code, 'PREPAYMENT',nvl(aip.invoice_payment_type, 'X'),1) <> decode(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)AND aip.bank_num=abb.bank_num&LP_VENDOR_ID&LP_VENDOR_SITE

&LP_INVOICES&LP_INVOICES_NUMBER_FROM&LP_INVOICES_NUMBER_TO&LP_DOC_SEQUENCE_NAME&LP_DOC_SEQUENCE_NUMBER_FROM&LP_DOC_SEQUENCE_NUMBER_TO&LP_INVOICE_DATE_FROM&LP_INVOICE_DATE_TOORDER BYpv.vendor_name,pvs.vendor_site_code,i.invoice_num,i.invoice_date,i.invoice_type_lookup_code,aip.accounting_date,apd.line_type_lookup_code

5) Modify the layout editor to add the new columns in it.6) Save the report.7) Transfer this report to /apps/visappl/ap/11.5.0/reports/US8) Run the report from Oracle Payables responsibility.

AP- Invoice Audit Listing (APXINLST)This report gives the details of all the booked invoices. Please add the following columns Accounted forApproval statusGL Date.Steps: Download the original report though ftp to the local folder from

/apps/visappl/ap/11.5.0/reports/US. The name of the report is APXINLST.rdf. Save the original report as APXINLST_ORG.rdf. Open APXINLST.rdf in report builder. Modify the query as follows

SELECT i.invoice_num C_INVOICE_NUMBER, i.invoice_date C_INVOICE_DATE, i.invoice_currency_code C_CURRENCY_CODE, i.invoice_amount C_INVOICE_AMOUNT,

lc.displayed_field C_INVOICE_TYPE, v.vendor_name C_VENDOR_NAME, v.segment1 C_VENDOR_NUMBER, i.GL_DATE C_GL_DATE, i.APPROVAL_STATUS C_APPROVAL_STATUS, i.ACCTS_PAY_CODE_COMBINATION_ID

C_CODE_COMBINATION, &P_FLEXDATA C_FLEXDATA, GCC.chart_of_accounts_id c_numFROM ap_invoices i, po_vendors v, ap_lookup_codes lc, GL_CODE_COMBINATIONS gcc WHERE i.invoice_type_lookup_code = nvl(:P_INVOICE_TYPE,

i.invoice_type_lookup_code) AND i.vendor_id = v.vendor_idAND lc.lookup_code (+) = i.invoice_type_lookup_code

AND lc.lookup_type (+) = 'INVOICE TYPE'AND i.invoice_amount >= :P_MIN_INV_AMOUNTAND i.invoice_date >= :P_DATE_SINCEAND gcc.CODE_COMBINATION_ID = i.ACCTS_PAY_CODE_COMBINATION_ID&C_ORDER_BY

Add a formula column in Q_inv data block.1. C_DESC_ALL with datatype char(400) and write following PL/SQL.

SRW.REFERENCE(:C_NUM); SRW.REFERENCE(:C_FLEXDATA); SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":C_NUM" APPL_SHORT_NAME="SQLGL" DATA=":C_FLEXDATA" DESCRIPTION=":C_DESC_ALL" DISPLAY="ALL"'); RETURN(:C_DESC_ALL); Add a field in layout editor and assign C_DESC_ALL to its sourse property. Add a field in layout editor and assign C_GL_DATE to its sourse property. Add a field in layout editor and assign C_APPROVAL_STATUS to its sourse property. Save the report. Transfer this report to /apps/visappl/ap/11.5.0/reports/US Run the report from Oracle Payables responsibility.

AP- Distribution Set Listing (APXGDGDL)Give the details of Account description with all the segments.Steps:

Download the original report though ftp to the local folder from /apps/visappl/ap/11.5.0/reports/US. The name of the report is APXGDGDL.rdf.

Save the original report as APXGDGDL_ORG.rdf. Open APXGDGDL.rdf in report builder. Add two formula column in Q_DIST_SET data block's G_DIST_SET_DTL group.

1. C_DESC_ALL with datatype char(400) and write following PL/SQL. SRW.REFERENCE(:C_CHART_OF_ACCOUNTS_ID); SRW.REFERENCE(:C_FLEXDATA); SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" DATA=":C_FLEXDATA" APPL_SHORT_NAME="SQLGL" DESCRIPTION=":C_DESC_ALL" DISPLAY="ALL" NUM=":C_CHART_OF_ACCOUNTS_ID"');RETURN(:C_DESC_ALL);

Add a field in layout editor and assign C_DESC_ALL to its sourse property. Save the report. Transfer this report to /apps/visappl/ap/11.5.0/reports/US Run the report from Oracle Payables responsibility.

AP- Payables Trial Balances:

This is the report for trail balance for suppliers.Give the details of the account that is shown in this.For e.g. 01-000-2210-0000-000 – Details of all the segments.

Steps:

Download the original report though ftp to the local folder from /apps/visappl/ap/11.5.0/reports/US. The name of the report is APXTRBAL.rdf.

Save the original report as APXTRBAL_ORG.rdf. Open APXTRBAL.rdf in report builder. Add a user parameters: P_FLEXDATA1 with data type char(400)

and initial value GCC.SEGMENT1 || '-' || GCC.SEGMENT2 Change the query in Q_Detail data block as:

SELECT &C_SELECT_LE legal_entity, &C_SELECT_OU operating_unit, pv.VENDOR_NAME supplier_name, ai.invoice_num invoice_number, ai.INVOICE_DATE invoice_date, ai.INVOICE_CURRENCY_CODE invoice_curr, DECODE(ai.invoice_currency_code, asp.base_currency_code, ai.INVOICE_AMOUNT, (ap_utilities_pkg.ap_round_currency((ai.invoice_amount*NVL(ai.exchange_rate,1) ),asp.base_currency_code))) invoice_amount, atb.remaining_amount REMAINING_AMOUNT , atb.code_combination_id CODE_COMBINATION_ID, atb.vendor_id THIRD_PARTY_ID, fnd_flex_ext.get_segs('SQLGL','GL#', :p_chart_of_accounts_id, atb.code_combination_id) CONCAT_SEGMENTS, ai.DESCRIPTION invoice_description, atb.invoice_id SOURCE_INVOICE_ID, atb.ORG_ID, atb.SET_OF_BOOKS_ID, gcc.chart_of_accounts_id C_NUM1, &P_FLEXDATA1 C_FLEXDATA1 FROM &C_ORG_FROM_TABLES, ap_trial_bal atb, po_vendors pv, ap_system_parameters_all asp, ap_invoices_all ai, GL_CODE_COMBINATIONS GCCWHERE nvl(atb.org_id,-99) = nvl(ai.org_id,-99) AND nvl(atb.org_id,-99) = nvl(asp.org_id,-99)

AND atb.vendor_id = pv.vendor_id AND atb.invoice_id = ai.invoice_id AND GCC.CODE_COMBINATION_ID=atb.code_combination_id AND atb.set_of_books_id = :P_set_of_books_id &p_supp_acc_where &C_MULTI_ORG_WHERE &P_ORG_WHERE

Add following SRW function in BEFORE REPORT Trigger:SRW.REFERENCE(:P_STRUCT_NUM);SRW.USER_EXIT('FND FLEXSQL

CODE="GL#" NUM=":P_STRUCT_NUM" APPL_SHORT_NAME="SQLGL" OUTPUT=":P_FLEXDATA1" TABLEALIAS="GCC" MODE="SELECT" DISPLAY="ALL"'); Add a formula column in Q_Detail data block's

G_Concat_segment group.C_DISC_ALL1 with datatype char(400) and write following PL/SQL. SRW.REFERENCE(:C_NUM1);

SRW.REFERENCE(:C_FLEXDATA1); SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":C_NUM1" APPL_SHORT_NAME="SQLGL" DATA=":C_FLEXDATA1" DESCRIPTION=":C_DISC_ALL1" DISPLAY="FA_COST_CTR"'); Return(:C_DISC_ALL1); Add a field in layout editor and assign C_DISC_ALL1 to its sourse

property. This field should be in the same frame of Concat_segment field.

Save the report. Transfer this report to /apps/visappl/ap/11.5.0/reports/US Run the report from Oracle Payables responsibility.

Report Name :- Invoice Register (AP)

Customization :- 1) Display the Invoice register as per given supplier Name2) Use the LOV for selection of Supplier

Short Name :- APXINRIR

Parameter :- Cancelled Invoices Only,Unapproved Invoices OnlyTable :- Ap_invoices,po_vendors,ap_invoice_distribution,Ap_batches,Ap_lookup_codes,Ap_tax_codes

Solution :- See the parameter listing which is required for the report with the help of report name and short name . Then FPT the report from apps/viappl/ap/11.5.0/reports/US to your directory. Create one parameter in the user parameter list (i.e. MYSUPPLIER) Go the report builder navigator window ->click on the icon data module Click on the query Change the query as per your bind veriable (i.e. dist_name like 'M11_PAY') save the report -ftp the rdf file

Create a Table Type Value Set for Supplier Name from the table Po_vendors_all OR you can select predefined value set which contain the supplier name

create concurrent program with required parameter as per original report also add your new parameter to that concurrent program add the value set for supplier name parameter. attach that program to the Oracle Payble or your responsibility group

This customize Invoice Register shows the parameter list with supplier name LOV.------------------------------------------------------------------------------------------------------------------------------------------------

5) Report Name :- Final Payment Register (AP) Customization :- Add one Field STSTUS in the Header Section

Short Name :- APXPBFPR

Parameter :- Payment,Trace Switch

Table :- Ap_checks,Po_Vendor_Sites,Ap_Bank_Account,Ap_Invoice_Selection_criteria,

Ap_lookup_codes, Fnd_Teritories

Solution :- See the parameter listing which is required for the report with the help of report name and short name . Then FPT the report from apps/viappl/ap/11.5.0/reports/US to your directory.

Go to the data module and click on the QueryIn the select statement of ap_invoice _selection_criteria add ststus with

alias Go the report builder navigator window ->click on the icon Layout editor

go to the header section seperate all the frames of header section. palce the boilerplate text for the status.Add one field and give the source to the field as status Error :- Frequency Below Group Check the frame source or check the frame gone in the another frame save the report -ftp the rdf file create concurrent program with required parameter as per original report attach that program to the Oracle Payble or your responsibility group

This customize Final Payment RegisterReport shows the header section with the addition Field STATUS-----------------------------------------------------------------------------------------------------------------------------------------------

6)Report Name :- Payment Term Listing (AP)

Customization :- Display the Payment Term as 'like' the given Payment Term Name

Short Name :- APXPTPTR

Parameter :- Active / Inactive Set,Effective Date

Table :- AP_terms_t

Solution :- See the parameter listing which is required for the report with the help of report name and short name .

Then FPT the report from apps/viappl/ap/11.5.0/reports/US to your directory. Create one parameter in the user parameter list (i.e. MYDIST_PAY_NAME) Go the report builder navigator window ->click on the icon data module Click on the query Change the query as per your bind veriable (i.e. dist_name like 'M11_PAY') save the report -ftp the rdf file create concurrent program with required parameter as per original report also add your new parameter to that concurrent program attach that program to the Oracle Payble or your responsibility group

This customize Payment Term Listing report shows the Payment Terms like the parameter entered by the user.

REPORT OF ACCOUNT PAYABLE Trail Balances

Report; APXTRBAL

Tables Used :-

1) ap_trial_bal2) po_vendors 3) ap_system_parameters_all4) ap_invoices_all

Parameters:-

Prompt Name Token Value Set

Accounting Currency Set of Books Id p_set_of_books_id AP_SRS_ACCTG_CURRENCY As of Date Accounting Date p_accounting_date FND_STANDARD_DATE

Supplier Name Supplier Name p_vendor_id AP_SRS_VENDOR_NAME

Liability Account Accounting Flexfield p_account_id AP_SRS_FLEXFIELD

Summarize Report Summarize Report p_summarize AP_SRS_YES_NO_MAND

Exclude Invoices From Date p_from_date FND_STANDARD_DATE Prior To

Customisation:-

I make the ageing of the remaining amount column.I categories the amount in three different category from 0-30 days,31-60 days,61-above days pending days amount.I make a formula column in the group.From this i get the how many days the amount is pending.

**********************************************************************************************************

REPORT OF ACCOUNT PAYABLE (Final Payment Register)

Tables Used :-

Table 1) ap_bank_account table(bank_account_name) 2) ap_invoice_selection_criteria 3) ap_check_stocks 4) ap_bank_accounts 5) ap_lookup_codes 6) ap_lookup_codes 7) fnd_lookups 8) fnd_lookups 9) ap_system_parameters 10) gl_sets_of_books 11) gl_daily_conversion_types 12) fnd_territories_vl ft13)po_vendor_sites vs,14)

Parameters:-

Prompt Name Token Value Set

Payment Batch Payment Batch P_CHECKRUN_NAME AP_SRS_FINAL_PAYBATCH

Trace Switch Trace Switch P_TRACE_SWITCH AP_SRS_YES_NO_OPT

Customisation:-I add a extra column in the report of final payment register USER_CONVERSION_TYPE of gl_daily_conversion_types.In the query i add this column and display on the report