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.

AR 7 Bucket Ageing Query

Select MAIN_TAB.*
from
(select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
(nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) outstanding_amount_inr,
(nvl(AR_TAB.Fcy_Amt,0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then (AR_TAB.INR_AMT - AR_TAB.ar_acctd_amt_due)
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED* nvl(ps.EXCHANGE_RATE,1) ,0)),2)
from ar_receivable_applications_all a
where a.APPLIED_PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(a.AMOUNT_APPLIED,0)),2) from ar_receivable_applications_all a where a.APPLIED_PAYMENT_SCHEDULE_ID=ps.PAYMENT_SCHEDULE_ID
and a.DISPLAY='Y' and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('INV','DM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) <> 0
union all /*For credit Memo*/
select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
-(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) outstanding_amount_inr,
-(nvl(abs(AR_TAB.Fcy_Amt),0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then -(abs(AR_TAB.INR_AMT) - abs(AR_TAB.ar_acctd_amt_due))
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED*nvl(ps.EXCHANGE_RATE,1),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(abs(a.AMOUNT_APPLIED),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('CM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) <> 0) MAIN_TAB
order by MAIN_TAB.PARTY_NAME asc;

PO Tax Sub Query

Select Poh.SEGMENT1 Purchase_Order_Number,Pol.QUANTITY, Pol.UNIT_PRICE,

jpt.TAX_RATE, Jpt.TAX_TYPE,Jpt.TAX_AMOUNT, Jct.TAX_DESCRFROM
JAI_PO_TAXES jpt
, JAI_CMN_TAXES_ALL jct
, Po_Headers_All Poh
, Po_lines_All Pol
WHERE
Jpt.po_header_id = Poh.PO_HEADER_ID
And Jpt.PO_LINE_ID = POl.PO_LINE_ID
And Poh.PO_HEADER_ID = Pol.PO_HEADER_ID
And Jpt.TAX_ID = Jct.TAX_ID

How to change the prompt of a particular field

How To Change The Prompt Of A Particular Field.

Step 1. Change This """Customer """" Prompt.




Then See Customer Field And Block Name.










Then Open : Help>Diagnostics>Custom Code >Personalize

















Then Press ""Get Value""









Then Change Customer Name As "BRIJESH."






Then Apply Now

Then """"""""""""Validate """"" And Save .

Then Open New Sales Order From And Promt Name Will be Change.

How to change APPS login page logo(Oracle Logo) in R12.1.3

Navigate to  $OA_MEDIA top (Ex:- /u01/appl/apps_st/comn/java/classes/oracle/apps/media)

Replace the gif file(FNDSSCORP.gif) with your company logo in $OA_MEDIA folder.

Bounce the Apache
Clear all cookies in client machin

How to change form colour in Oracle APPS

Navigation:- Syatem Administrator > Profile > System

Type --> Java Color Scheme in Profile field

Click on Find

Choose value from LOV(blue,khaki,olive,purple,red,swan,teal,titanium) at site level.
Save the record.


Clear The Cache

Re login to get colour effect.

Invoice to Payment link

SELECT   a.org_id "ORG ID",
         E.SEGMENT1 "VENDOR NUM",
         e.vendor_name "SUPPLIER NAME",
         UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
         f.vendor_site_code "VENDOR SITE CODE",
         f.ADDRESS_LINE1 "ADDRESS",
         f.city "CITY",
         f.country "COUNTRY",
         TO_CHAR (TRUNC (d.CREATION_DATE)) "PO Date",
         d.segment1 "PO NUM",
         d.type_lookup_code "PO Type",
         c.quantity_ordered "QTY ORDERED",
         c.quantity_cancelled "QTY CANCELLED",
         g.item_id "ITEM ID",
         g.item_description "ITEM DESCRIPTION",
         g.unit_price "UNIT PRICE",
         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
         * NVL (g.unit_price, 0)
            "PO Line Amount",
         (SELECT   DECODE (ph.approved_FLAG, 'Y', 'Approved')
            FROM   po.po_headers_all ph
           WHERE   ph.po_header_ID = d.po_header_id)
            "PO Approved?",
         a.invoice_type_lookup_code "INVOICE TYPE",
         a.invoice_amount "INVOICE AMOUNT",
         TO_CHAR (TRUNC (a.INVOICE_DATE)) "INVOICE DATE",
         a.invoice_num "INVOICE NUMBER",
         (SELECT   DECODE (x.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   ap.ap_invoice_distributions_all x
           WHERE   x.invoice_distribution_id = b.invoice_distribution_id)
            "Invoice Approved?",
         a.amount_paid,
         h.amount,
         h.check_id,
         h.invoice_payment_id "Payment Id",
         i.check_number "Cheque Number",
         TO_CHAR (TRUNC (i.check_DATE)) "Payment Date"
  FROM   AP.AP_INVOICES_ALL A,
         AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
         PO.PO_DISTRIBUTIONS_ALL C,
         PO.PO_HEADERS_ALL D,
         PO.PO_VENDORS E,
         PO.PO_VENDOR_SITES_ALL F,
         PO.PO_LINES_ALL G,
         AP.AP_INVOICE_PAYMENTS_ALL H,
         AP.AP_CHECKS_ALL I
 WHERE       a.invoice_id = b.invoice_id
         AND b.po_distribution_id = c.po_distribution_id(+)
         AND c.po_header_id = d.po_header_id(+)
         AND e.vendor_id(+) = d.VENDOR_ID
         AND f.vendor_site_id(+) = d.vendor_site_id
         AND d.po_header_id = g.po_header_id
         AND c.po_line_id = g.po_line_id
         AND a.invoice_id = h.invoice_id
         AND h.check_id = i.check_id
         AND f.vendor_site_id = i.vendor_site_id
         AND c.PO_HEADER_ID IS NOT NULL
         AND a.payment_status_flag = 'Y'
         AND d.type_lookup_code != 'BLANKET'

Remove Junk Characters Function

CREATE OR REPLACE FUNCTION xxx_ascii_only_vipul (p_txt IN VARCHAR2)
       RETURN VARCHAR2
    IS
       v_tmp     VARCHAR2 (32767);
       v_clean   VARCHAR2 (32767);
       v_char    VARCHAR2 (3 BYTE);
    BEGIN
       FOR i IN 1 .. LENGTH (p_txt) LOOP
          v_char := SUBSTR (p_txt, i, 1);

         IF    (ASCII (v_char) BETWEEN 32 AND 127)
            OR (ASCII (v_char) IN (9, 10, 13)) THEN
            v_clean := v_clean || v_char;
         END IF;
      END LOOP;

      IF LENGTH (v_clean) != LENGTH (p_txt) THEN
         DBMS_OUTPUT.put_line ('removed '||TO_CHAR(LENGTH(p_txt) - LENGTH(v_clean))||' characters');
      END IF;

      RETURN v_clean;
   END;

DFF Deletion API

DFF Deletion API:

DECLARE
CURSOR C1 IS
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE, DESCRIPTIVE_FLEXFIELD_NAME, APPLICATION_ID
FROM FND_DESCR_FLEX_CONTEXTS
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCR_FLEX_CONTEXTS_PKG.DELETE_ROW (
  V1.APPLICATION_ID,
  V1.DESCRIPTIVE_FLEXFIELD_NAME,
  V1.DESCRIPTIVE_FLEX_CONTEXT_CODE);
END LOOP;
END;

-----------

DECLARE
CURSOR C1 IS
SELECT APPLICATION_COLUMN_NAME,DESCRIPTIVE_FLEX_CONTEXT_CODE,DESCRIPTIVE_FLEXFIELD_NAME,APPLICATION_ID
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCR_FLEX_COL_USAGE_PKG.DELETE_ROW (
  V1.APPLICATION_ID,
  V1.DESCRIPTIVE_FLEXFIELD_NAME,
  V1.DESCRIPTIVE_FLEX_CONTEXT_CODE,
  V1.APPLICATION_COLUMN_NAME);
END LOOP;
END;

-----------

DECLARE
CURSOR C1 IS
SELECT DESCRIPTIVE_FLEXFIELD_NAME,APPLICATION_ID
FROM FND_DESCRIPTIVE_FLEXS
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCRIPTIVE_FLEXS_PKG.DELETE_ROW (
  V1.APPLICATION_ID,
  V1.DESCRIPTIVE_FLEXFIELD_NAME);
END LOOP;
END;

Session Activity Queries

--> Checking session

select sesion.sid, sesion.username, optimizer_mode,

hash_value, address, cpu_time, elapsed_time, sql_text

from v$sqlarea sqlarea, v$session sesion

where sesion.sql_hash_value = sqlarea.hash_value

and sesion.sql_address = sqlarea.address

and sesion.username is not null

--> Get the rows fetched, it there is difference it means processing is happening

select b.name, a.value vlu

from v$sesstat a, v$statname b

where a.statistic# = b.statistic#

and sid =&sid

and a.value != 0

and b.name like '%row%'

--> Get the sql_hash_value

select sql_hash_value from v$session where sid='&sid';

Get the sql_Text

select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;

Get the explain_plan

set lines 190

col XMS_PLAN_STEP format a40

set pages 100

select

case when access_predicates is not null then 'A' else ' ' end ||

case when filter_predicates is not null then 'F' else ' ' end xms_pred,

id xms_id,

lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,

object_name xms_object_name,

cost xms_opt_cost,

cardinality xms_opt_card,

bytes xms_opt_bytes,

optimizer xms_optimizer

from v$sql_plan

where hash_value in (&SQL_HASH_VALUE)

and to_char(child_number) like '%';

Based the cost u can decide what to be done.

One of the solutions is to analyse the statistics

exec fnd_stats.gather_schema_statistics('ALL');

--> Time Remaining to complete the current task

set lines 150

col username format a20

col opname format a30

col target format a40

select sid,opname,target,

to_char(start_time,'DD-MON-YY HH24:MI') START_TIME,

time_remaining/60 "Time Remaining in Mins",username

from v$session_longops where time_remaining>1 order by time_remaining

/

--> Active session

select sid, to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,

username, type, status, process, sql_address, sql_hash_value

from v$session

where username is not null

Work remaining

select V1.sid, V1.serial#, V2.USERNAME, V2.OSUSER, substr(V1.opname,1,10), to_char(V1.start_time, 'HH24:MI:SS') AS Started, (V1.SOFAR/V1.TOTALWORK)*100 AS Pct_completed

FROM V$SESSION_LONGOPS V1, V$SESSION V2

WHERE V1.SID= V2.SID AND V1.SERIAL#=V2.SERIAL#

AND (SOFAR/TOTALWORK)*100 < 100

AND TOTALWORK > 0

/

--> Memory usage

SELECT username, value/(1024*1024) "Current session memory MB", sess.sid,sess.status

FROM v$session sess, v$sesstat stat, v$statname name

WHERE sess.sid = stat.sid

AND stat.statistic# = name.statistic#

AND name.name like '%memory%'

AND username = ‘APPS’

Order by 2,4 asc