Infolinks

Wednesday 20 June 2012

REPORT CUSTOMIZATION EXAMPLES

REPORT OF ACCOUNT PAYABLE Trail Balances

Report;
·      APXTRBAL
Tables Used :-

1) ap_trial_bal
2) po_vendors
3) ap_system_parameters_all
4) 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 ft
13)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

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

Report for Cancelled Orders Report

Report:-

·      OEXOEOCS

Tables:-


·oe_order_lines_history(header_id,hist_type_code='CANCELLATION',inventory_item_id,ordered_item)
·oe_order_headers_all(header_id,salesresp_id)
·mtl_system_items_vl (inventory_item_id)
·ra_salesreps (salesresp_id)
·oe_sold_to_orgs_v
·mtl_customer_items
·oe_items_v(ordered_item )

Parameter Used:-

1  Set of Books Id             Set of Books Id    P_SOB_ID                             OE: Number Zero
                                                                                                                    Precision
2  Organization Id             Organization Id     P_ORGANIZATION_ID            OE: Number Zero
                                                                                                                    Precision
3  Item Flex Code            Item Flex Code      P_ITEM_FLEX_CODE            OE: Text

4  Sort By                      Sort By                  P_ORDER_BY                      ONT_SRS_OEXOEOCS_SORTBY

5  Customer Nam(From) Customer Name (Low) P_CUSTOMER_NAME_LOW  ONT_SRS_CUSTOMER_NAME

6  Customer Name (To) Customer Name (High)P_CUSTOMER_NAME_HIGH ONT_SRS_CUSTOMER_NAME

7  Order Number (From)Order Number (Low)  P_ORDER_NUM_LOW      ONT_SRS_ORDER_NUMBER

8  Order Number(To)   Order Number (High) P_ORDER_NUM_HIGH     ONT_SRS_ORDER_NUMBER

9  Salesperson (From) Salesperson (Low)   P_SALESREP_LOW       ONT_SRS_SALESREPS

10 Salesperson Name   Salesperson (High)  P_SALESREP_HIGH      ONT_SRS_SALESREPS

11 Order Date (From)  Order Date (Low)    P_ORDER_DATE_LOW     FND_STANDARD_DATE

12 Order Date (To)    Order Date (High)   P_ORDER_DATE_HIGH    FND_STANDARD_DAT

13 Item               Item                P_ITEM               OE: Item

14 Item Display       Item Display        P_PRINT_DESCRIPTION                                                                                       ONT_SRS_ITEM_DISPLAY_CODE

15 Order Category     Order Category    P_ORDER_CATEGORY
                                                                                    ONT_SRS_ORDER_CATEGORY

16 Line Category      Line Category       P_LINE_CATEGORY      ONT_SRS_LINE_CATEGORY

17 Show in Functional Show in Functional  p_use_functional_cur  OM: Yes or
   Currency           Currency            rency                      No

18 Mixed Precision    Mixed Precision     P_Mixed_Precision     OE: Number Zero
                                                                    Precision


Custamisation:-
Add one field shipment Priority Code from oe_order_lines_history of the report and group by this code.
so that while shipping from this it may find that we know that which shippemnt gives the first priority.

**************************************************************************************************************************************
Report for Corporate cards issued,expired details report by expiry date and employee
Report:-
·      Expiry_date

Tables:-

·                     HR_EMPLOYEES(EMPLOYEE_ID,LAST_NAME)
·                     AP_CARDS_ALL (CARD_ID,CARD_EXPIRATION_DATE,EMPLOYEE_ID)
·                     AP_CARD_PROFILES_ALL
·                     AP_CARD_CODES_ALL

Develop the report in which the issued date is the date_from column of the HR_EMPLOYERS_V which is the join date of the employee and expired date is the inactive_date of the ap_cards_all.

**************************************************************************************************************************************
Credit  card payments amount payable,last due date etc.

Report:-
·      credit_card_payament

Tables:-
·                     AP_CREDIT_CARD_TRXNS_ALL(BILLED_DATE,BILLED_AMOUNT,CARD_PROGRAM_ID,                                              CARD_NUMBER)

·                     HR_EMPLOYEES(EMPLOYEE_ID,LAST_NAME)

·                     AP_CARDS_ALL (CARD_NUMBER,INACTIVE_DATE,EMPLOYEE_ID,CARD_PROGRAM_ID)

Using this tables we create the above report

**************************************************************************************************************************************
Customisation of Invoice Register Using LOV to select Supplier

Report :-

·      APXINRIR

Tables:-

·        ap_invoices (vendor_id,batch_id)
·        po_vendors (vendor_id)
·        ap_invoice_distributions (tax_code_id)
·        gl_code_combinations
·        &P_gl_code_combinations2
·        ap_batches (batch_id)
·        ap_lookup_codes (lookup_type = 'INVOICE TYPE' & = 'POSTING STATUS' & = 'INVOICE                                                                                                  DISTRIBUTION TYPE')
·        ap_tax_codes (tax_id)

Parameters:-
3  Batch                        Batch                       P_BATCH                       AP_SRS_BATCH

4  Entered By                Entered By               P_ENTRY_PERSON       AP_SRS_USER_NAME

5  From Entered Date     First Entered Date    P_START_DATE             FND_STANDARD_DATE

6  To Entered Date       Last Entered Date    P_END_DATE                 FND_STANDARD_DATE

7  Accounting Period                 Accounting Period    P_ACCOUNTING_PERIOD AP_SRS_ACCOUNTING_PERIOD_OPT

8  Cancelled Invoices     Cancelled Invoices Only P_CANCELLED_FLAG  AP_SRS_YES_OnlyNO_MAND

9  Unapproved Invoices   Unapproved Invoices Only P_UNAPPROVE_FLAG  AP_SRS_YES_OnlyNO_MAND

10 Invoice Type              Invoice Type      P_INVOICE_TYPE                 AP_SRS_INVOICE_TYPE

11 Trace Switch            Trace Switch        P_TRACE_SWITCH             AP_SRS_YES_NO_OPT


Custamisation:-
I one parameter p_vendor in the report and create a flexfield of values set of type table and attach a table po_vendors
to it the in the where clause of the query i compair the value to the ap_invoices table . The value set in which the values will be the vendor_id and the meaning is vendor_name.

**************************************************************************************************************************************
Report for shipped Order Details.

Report:-
·      sHIPPED_QUANTITY

Tables:-

·                     wsh_delivery_details

Columns Used:

·                      REQUESTED_QUANTITY
·                      CANCELLED_QUANTITY
·                      DATE_REQUESTED
·                      DATE_SCHEDULED,
·                      DELIVERED_QUANTITY
·                      SHIPPED_QUANTITY
·                      SOURCE_HEADER_NUMBER
·                      SOURCE_LINE_NUMBER

Conditions;-

RELEASED_STATUS='C'

With this condition we can show the order whose shippment is completed.
====


Report Customization


1) Report Name :- Customer Item Report         (Inventory)

Customization :-   Add one field Customer Status to report

Short Name :-  INVIRCIT

Parameter :- Organization

Table :-  MTL_CUSTOMER_ITEMS_ALL_V , MTL_SYSTEM_ITEMS_KFV, MTL_SYSTEM_ITEMS_KFV ,
ORG_ORGANIZATION_DEFINITIONS OOD

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/inv/11.5.0/reports/US to your directory.
Go to the data module and click on the Query In the select statement of MTL_CUTOMERS_TERMS_ALL 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 Customer Items Report Shows Added Customer Status .

------------------------------------------------------------------------------------------------------------------2) Report Name :- Inactive Item Report         (Inventory)

Customization :-   Add one field Transaction Qty to report

Short Name :-  INVIRSLO

Parameter :- Organization,Subinv Break Option,Category Set Name,Inactive From Date

Table :-  mtl_system_items ,mtl_material_transactions ,mtl_item_categories,
mtl_categories.


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/inv/11.5.0/reports/US to your directory.
Go to the data module and click on the Query In the select statement addtransaction Qty from MTL_MATERIAL_TRANSACTION 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 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 Inactive Items Report Shows Added Customer Status .

------------------------------------------------------------------------------------------------------------------3)Report Name :- Distribution Set Listing         (AP)

Customization :-   Display the Distribution List as 'like' the given Distribution set Name

Short Name :- APXGDGDL

Parameter :- Active / Inactive Set,Effective Date

Table :- Ap_distribution_sets,Ap_distribution_set_lines,gl_code_combination


Solution          :-    Add one Parameter (Distribution Set Name) in the parameter List
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_SET_NAME)
Go the report builder navigator window ->click on the icon data module
Click on the query
Change the query as per your bind variable (i.e. dist_name like 'M11_Dist')
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 Distibution Set Listing report shows the distirbution set listing like the parameter entered by the user.
------------------------------------------------------------------------------------------------------------------
4) Report Name :- Invoice Register     (AP)

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

Short Name :- APXINRIR

Parameter :- Cancelled Invoices Only,Unapproved Invoices Only
Table :-  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 Query
In 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.
------------------------------------------------------------------------------------------------------------------------------------------------

7)  Internal Requestion Status Report.

Customization :-   Add total for Quantity Ordered

Short Name :-POXRQSIN

Parameter :-

Table :-  po_requisition_lines ,  po_requisition_headers  ,  mtl_system_items ,  hr_employees

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/inv/11.5.0/reports/US to your directory.
Create one summary Column for the Quantity order for Sum
Go the report builder navigator window ->click on the icon layout
add the total field
give the source of the summary column to that field
save the report -ftp the rdf file
create concurrent program with required parameter as per original report
attach that program to the Oracle Inventory or your responsibility group

This customize Internal requestion status report shows total for quantity order.

No comments:

Post a Comment