JSP Page Personalization in ORACLE EBS

REQUIREMENT: How to show Item Number at PO Requisition Approval Notification

Set the following profile options at user level

1. Disable Self-Service Personal = No

2. Personalize Self-Service Defn = Yes

3. FND: Personalization Region Link Enabled = Yes

Click on Personalize "Requisition Lines"

How to find the text attachemnts attached to a Requisition Header

     /* Step:-1 -> Find the requisition */
     
      select b.REQUISITION_HEADER_ID from po_requisition_headers_all b where   trunc(b.CREATION_DATE)='25-MAR-2013'
     
      /*Step:- 2 - >   Find The document id (Here PK1_VALUE is the requisition_header_id) */
     
      select a.PK1_VALUE ,a.DOCUMENT_ID ,a.* from apps.FND_ATTACHED_DOCUMENTS a where a.PK1_VALUE =95355(requisition_header_id)
     
      /* Step :- 3 ->  Find the media_id */
     
      select a.MEDIA_ID ,a.* from fnd_documents a where a.DOCUMENT_ID=160936
     
      /*Step:- 4 -> Find the  Input Text in the requisition */
     
      select a.SHORT_TEXT,a.MEDIA_ID from fnd_documents_short_text a where a.MEDIA_ID=2;

How to attach multiple templates (.rtf) to a single Report

1.      Create your multiple RTFs.

2.     Now go to XML Publisher Administrator and create a Data Definition.

3. We need to create multiple templates against the same Data Definition.

       Similarly, you need to create multiple template against the same Data Definition ( e.g.           KCT000RCONA  here).

4.     Now run the Report from SRS window.

Submit the request and you will get output according to your selected template.

      

       

      

SORTING AUTOCREATE DATA

At the time of Auto create if data are coming without maintaining any order you can sort them in order. The process is showing below.

HOW TO INCREASE NUMBER OF LINES IN APPROVAL NOTIFICATION

Put the number that you want to display in approval notification in the following profile option.
"PO: Notification Lines Display Limit"

Enable Trace for long running Concurrent Request in Apps 11i/R12

Enable Trace for long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in Oracle
Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 in my case)
when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;


REQUEST_ID   SID    SERIAL#             OSUSER                       PROCESS           SPID
—————----------------------------------------------------------------------------
1145              514     28                  applmgr                     17794           2987.
.
Step 3.1 : Check and confirm SPID on Database Node
$ ps-ef | grep 2987
proddb 2987 1 0 13:30:43 ?
0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name
/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.trc
Wait for 15-20 minutes


Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off


Step 5: Create tkprof file like
$ tkprof prod _ora_2987.trc prod _ora_2987.txt explain=apps/<****> sort=(exeela,fchela)
sys=no


Step 6 : Check TKPROF file to find root cause of slow concurrent request


We can follow the Metalink Note: 296559.1 to know more about tracing.

Inventory Organization Access Control

Process:

For each responsibility that needs to access a subset of the available organizations:

1. Navigate to INV / Setup / Organizations / Organization Access
2. Enter the organization code, application name and responsibility name and save
3. Repeat for each organization that will be available to the responsibility.

Points to be remembered:

Organization access works on two parameters, organization and Responsibility.

1) When you add a row in organization access form with respect to an organization and a responsibility, then that organization will ONLY be accessed by that responsibility.
2) You can add multiple responsibilities to access the same organization, and all responsibilities will have access to that organization.
3) Any organization that is NOT assigned to at least one responsibility is available to ALL responsibilities.
4) So users will indirectly have access to organizations based on the responsibilities attached.

How To Calculate The Intransit Inventory Value Under Average Costing Method In OPM

Select * from gmf_xla_extract_lines where header_id in (Select header_id from gmf_xla_extract_headers where Transaction_date >'31-Mar-11' and Transaction_date<'01-May-11')
and Journal_line_type ='ITR'
Change the date range accordingly.

Supplier wise 5 Buckets Aging Query In AP

select yy.VENDOR_NAME,yy.LESS30,yy.GREATER30,yy.GREATER60,yy.GREATER90,yy.GREATER120,
(select count(1)
from ap_invoices_all aia
where aia.CANCELLED_DATE is null
and aia.VENDOR_ID=yy.vendor_id
and trunc(aia.INVOICE_DATE) between :p_from_date and  :p_to_date
and aia.AMOUNT_PAID <> aia.INVOICE_AMOUNT ) OUT_STND_BILL,
(select sum(nvl(apd.PREPAY_AMOUNT_REMAINING,0))
from ap_invoices_all aib,ap_invoice_distributions_all apd
where aib.CANCELLED_DATE is null
and aib.VENDOR_ID=yy.vendor_id
and aib.INVOICE_ID=apd.INVOICE_ID
and apd.REVERSAL_FLAG='N'
and trunc(aib.INVOICE_DATE) between :p_from_date and  :p_to_date
and aib.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
) ON_AC_AMOUNT
from
(select
xx.VENDOR_NAME,xx.VENDOR_ID,sum(nvl(xx.less_30,0)) LESS30,sum(nvl(xx.greater_30,0)) GREATER30,sum(nvl(xx.greater_60,0)) GREATER60,
sum(nvl(xx.greater_90,0)) GREATER90,sum(nvl(xx.greater_120,0)) GREATER120
from
(select c.VENDOR_NAME,c.VENDOR_ID,
(case when((sysdate) - trunc(a.DUE_DATE) <= 30) then nvl(a.AMOUNT_REMAINING,0)
else 0 end ) less_30,
(case when((sysdate) - trunc(a.DUE_DATE) between 31 and 60) then nvl(a.AMOUNT_REMAINING,0)
else 0 end ) greater_30,
(case when((sysdate) - trunc(a.DUE_DATE) between 61 and 90) then nvl(a.AMOUNT_REMAINING,0)
else 0 end ) greater_60,
(case when((sysdate) - trunc(a.DUE_DATE) between 91 and 120) then nvl(a.AMOUNT_REMAINING,0)
else 0 end ) greater_90,
(case when((sysdate) - trunc(a.DUE_DATE) > 120) then nvl(a.AMOUNT_REMAINING,0)
else 0 end ) greater_120
from ap_payment_schedules_all a,
ap_invoices_all b,
po_vendors c
where a.INVOICE_ID=b.INVOICE_ID
and b.VENDOR_ID=c.VENDOR_ID
and trunc(b.INVOICE_DATE) between :p_from_date and :p_to_date
and b.CANCELLED_DATE is null
order by c.VENDOR_NAME ) xx
group by xx.VENDOR_NAME,xx.VENDOR_ID) yy
order by yy.vendor_name ;

While Performing Receipt Traveller Concurrent program Error :- at the time of Receipt

While Performing Receipt Traveller Concurrent program Error:-
 "REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: missing expression

Solution: If India Localization is used then it is mandatory set the profile option INV: Dynamic Precision Option for Quantity on Reports to provide any value from LOV at site level.