Concurrent Program,Responsibility name and Requestor fetching details query

SELECT * FROM PER_ALL_PEOPLE_F

WHERE FULL_NAME LIKE '%SAN%'

SELECT * FROM FND_USER

WHERE EMPLOYEE_ID = 487

  SELECT REQUEST_ID,
         PHASE_CODE,
         STATUS_CODE,
         REQUEST_DATE,
         REQUESTED_BY,
         REQUESTOR,
         COMPLETION_TEXT,
         ARGUMENT_TEXT,
         PROGRAM,
         USER_CONCURRENT_PROGRAM_NAME,
         RESPONSIBILITY_NAME
    FROM FND_CONC_REQ_SUMMARY_V A, FND_RESPONSIBILITY_TL B
   WHERE     A.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
         AND A.USER_CONCURRENT_PROGRAM_NAME LIKE '%PDI%'
         AND REQUESTOR LIKE 'XX 1030%'
ORDER BY REQUEST_DATE DESC

Concurrent Program and attached Responsibility and Request Group Query

select * from fnd_concurrent_programs_tl
where user_concurrent_program_name like 'XXX%Sales%'

 'XXX GL Extract Report%'

SELECT frt.responsibility_name,
               frg.request_group_name,
               frg.description,
               fcpt.user_concurrent_program_name
  FROM fnd_request_groups frg
             ,fnd_request_group_units frgu
             ,fnd_concurrent_programs fcp
             ,fnd_concurrent_programs_tl fcpt
             ,fnd_responsibility_tl frt
             ,fnd_responsibility frs
 WHERE frgu.unit_application_id = fcp.application_id
 AND   frgu.request_unit_id = fcp.concurrent_program_id
 AND   frg.request_group_id = frgu.request_group_id
 AND   frg.application_id = frgu.application_id
 AND   fcpt.source_lang = USERENV('LANG')
 AND   fcp.application_id = fcpt.application_id
 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id
 AND   frs.application_id = frt.application_id
 AND   frs.responsibility_id = frt.responsibility_id
 AND   frt.source_lang = USERENV('LANG')
 AND   frs.request_group_id = frg.request_group_id
 AND   frs.application_id = frg.application_id
-- AND   fcp.concurrent_program_name = <shortname>
 AND   (fcpt.user_concurrent_program_name)  like 'U%Credi%'
 --and frt.responsibility_name like '%Paper%'


 select * from fnd_concurrent_programs
 where CONCURRENT_PROGRAM_ID = 133344

 where concurrent_program_name = 'XXX GL Extract Report'

 select fcp.user_concurrent_program_name
,fcr.request_date
,fu.user_name
,fcr.actual_start_date
,fcr.actual_completion_date
,fcr.phase_code
,fcr.status_code
,fcr.argument1
,fcr.argument2
,fcr.argument3
from fnd_concurrent_programs_vl fcp
,fnd_concurrent_requests fcr
,fnd_user fu
where fcp.user_concurrent_program_name like 'U%Sales%'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.requested_by = fu.user_id
order by fcr.actual_completion_date desc



SELECT * FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'XXXX_GL_EXT_MUL_PERIOD_PROC'



select variable_name, value
from fnd_env_context
where variable_name like '%\_TOP' escape '\'
and concurrent_process_id = (select max(concurrent_process_id) from fnd_env_context)
order by 1;

Indian Localization Tax Summary Query of oracle Apps R12.2.5

Query:-

SELECT SUM(DECODE(jtc.TAX_TYPE,'Excise',jrpt.TAX_AMOUNT,0)) EXCISE_AMT
             ,SUM(DECODE(JTC.TAX_TYPE,'Other Excise',jrpt.TAX_AMOUNT,0)) EDU_CESS
             ,SUM(DECODE(JTC.TAX_TYPE,'Addl. Excise',jrpt.TAX_AMOUNT,0)) ADDL_EXCISE
             ,SUM(DECODE(JTC.TAX_TYPE,'SERVICE-EC',jrpt.TAX_AMOUNT,0)) EDU_CESS_SER_EC
             ,SUM(DECODE(JTC.TAX_TYPE,'CST',jrpt.TAX_AMOUNT,0)) CST
             ,SUM(DECODE(JTC.TAX_TYPE,'Freight',jrpt.TAX_AMOUNT,0)) FREIGHT
             ,(SUM(DECODE(JTC.TAX_TYPE,'Freight',jrpt.TAX_AMOUNT,0)))+ (SUM(DECODE(JTC.TAX_TYPE,'Insurance',jrpt.TAX_AMOUNT,0))) FREIGHT
             ,SUM(DECODE(JTC.TAX_NAME,'VAT 4 % On Purchase',jrpt.TAX_AMOUNT,0)) + SUM(DECODE(JTC.TAX_NAME,'VAT Addi. 1 % On Purchase',jrpt.TAX_AMOUNT,0)) LST
             ,SUM(DECODE(JTC.TAX_TYPE,'Service',jrpt.TAX_AMOUNT,0)) SERVICE
             ,SUM(DECODE(JTC.TAX_NAME,'ADHOC TOLL TAX',jrpt.TAX_AMOUNT,0)) AD_TOLL    
         ,SUUM(DECODE(JTC.TAX_NAME,'Less TOLL TAX',jrpt.TAX_AMOUNT,0)) LESS_TOLL  
--        into :cp_excise1,
--             :cp_edu_cess1,
--             :cp_addl_excise1,
--             :cp_edu_cess_Ser1,
--             :cp_cst1,
--             :cp_frt1,
--             :cp_lst1,
--             :cp_Service1,
--             :cp_ad_toll1,
--         :cp_less_toll1
        FROM  --ja_in_ap_tax_distributions jrpt,
                  JAI_AP_MATCH_INV_TAXES jrpt,
             --ja_in_tax_codes         jtc
             JAI_CMN_TAXES_ALL jtc
        where jrpt.tax_id  = jtc.tax_id
        and   jrpt.invoice_id = 413189 --:invoice_id2; --413261  --
       -- and   jrpt.po_distribution_id =787878 --:po_distribution_id1; -- 787879; --

       SELECT * FROM JAI_CMN_TAXES_ALL
       WHERE TAX_TYPE LIKE  '%VAT%'

Junk Characters remove in oracle pdf out put in oracle apps r12.2.5

Issue: After Migrating from 11i to R12.2.5 Oracle Apps Custom Reports are getting Junk Character when ever we select PDF output format.
Resolution:
------------
Execute Below Commands in Linux Enviorment
1. Set up Env
  . ./APPSTEST.env
2. cd $XXTEST_TOP/reports/US

3. rwconverter userid=apps/testapps04 source=XXUCHMBHIDEPOT.rdf dest=XXUCHMBHIDEPOT.rex stype=rdffile dtype=rexfile overwrite=yes batch=yes

4. cat XXUCHMBHIDEPOT.rex | tr -d "\015" >XXUCHMBHIDEPOT.rextmp

5. sed -e 's/CHARSET = 0/CHARSET = 38/' -e 's/CHARSET = 31/CHARSET = 38/' -e 's/CHARSET = 178/CHARSET = 38/' -e 's/CHARSET = 871/CHARSET = 38/' <XXUCHMBHIDEPOT.rextmp> XXUCHMBHIDEPOT.rex

6. mv  XXUCHMBHIDEPOT.rdf XXUCHMBHIDEPOT.rdf-bb11

7. rwconverter userid=apps/testapps04 source=XXUCHMBHIDEPOT.rex dest=XXUCHMBHIDEPOT.rdf stype=rexfile dtype=rdffile  overwrite=yes batch=yes



Any Doubts let me know..

Junk Characters remove from PDF output in Oracle reports(after migration reports from 11i to R12.2.5)

When ever will get Junk characters in the report, Run the below commands in LINUX Enviornment
Set the APPS Env
and go to the report dire


1). rwconverter userid=apps/testapps04 source=rdf_name.rdf dest=rdf_name.rex stype=rdffile dtype=rexfile overwrite=yes batch=yes

2). cat rdf_name.rex | tr -d "\015" >rdf_name.rextmp
sed -e 's/CHARSET = 0/CHARSET = 38/' -e 's/CHARSET = 31/CHARSET = 38/' -e 's/CHARSET = 178/CHARSET = 38/' -e 's/CHARSET = 871/CHARSET = 38/' <rdf_name.rextmp> rdf_name.rex
3) . mv  rdf_name.rdf  rdf_name.rdf-bb11
4) . rwconverter userid=apps/testapps04 source=rdf_name.rex dest=rdf_name.rdf stype=rexfile dtype=rdffile  overwrite=yes batch=yes

Bill wise Outstanding Query in Oracle apps R12.2.5

SELECT
 DISTINCT cust_acct.ACCOUNT_NUMBER customer_number,
        SUBSTRB (HP.party_name, 1, 50) customer_name,
       ps.trx_number Invoice_No,
       ps.trx_date,
       (SELECT DESCRIPTION
          FROM RA_CUSTOMER_TRX_LINES_ALL
         WHERE     CUSTOMER_TRX_ID = ps.CUSTOMER_TRX_ID
               AND DESCRIPTION IS NOT NULL
               AND ROWNUM = 1)
          PRODUCT_CATEGORY,
       ps.due_date,
       NVL (ps.amount_due_original, 0) Invoice_amt,
      NVL (ps.AMOUNT_DUE_REMAINING, 0) DUEAMOUNT,
     -- NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
       NVL ( (ps.amount_due_REMAINING * NVL (ps.EXCHANGE_RATE, 1)), 0)
          NET_OUTSTANDING,
       PS.CLASS TYPE,
       (SELECT DISTINCT name
          FROM JTF_RS_SALESREPS
         WHERE salesrep_id(+) = rta.primary_salesrep_id)
          SALESREP_NAME,
       HP.STATE REGION,
       HP.COUNTY Continent,
       TERMS.NAME CREDIT_PERIOD,
       TRUNC (SYSDATE) RUN_DATE,
       :P_AS_ON_DATE AS_ON_DATE
  FROM ra_cust_trx_types_all rtt,
       ra_customer_trx_all rta,
       ra_cust_trx_line_gl_dist_all rgld,
       gl_code_combinations cc,
       hz_cust_accounts_all cust_acct,
       --ra_customer_trx_lines_all rctla,
       hz_parties hp,
       --JTF_RS_SALESREPS REP,
       ra_terms terms,
       ar_payment_schedules_all ps,
       hz_cust_acct_sites_all acct_site,
       hz_party_sites party_site,
       hz_locations loc,
       hz_cust_site_uses_all site_uses
 WHERE  --cust_acct.account_number = :p_account_number
       ps.customer_id = cust_acct.cust_account_id
       AND ps.cust_trx_type_id = rtt.cust_trx_type_id
       AND cust_acct.party_id = hp.party_id
       --and rta.customer_trx_id = rctla.customer_trx_id
       AND rta.term_id = terms.term_id
     --  AND ps.trx_date <= :p_as_of_date
       AND ps.CLASS NOT IN ('CM', 'PMT')
       AND site_uses.site_use_code = 'BILL_TO'
       AND acct_site.party_site_id = party_site.party_site_id
       AND loc.location_id = party_site.location_id
       AND NVL (site_uses.status, 'A') = 'A'
       AND cust_acct.cust_account_id = acct_site.cust_account_id
       AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
       AND ps.customer_id = acct_site.cust_account_id
       AND ps.customer_site_use_id = site_uses.site_use_id
       AND rta.customer_trx_id = ps.customer_trx_id
       AND rta.customer_trx_id = rgld.customer_trx_id
       AND rgld.code_combination_id = cc.code_combination_id
       AND rgld.account_class = 'REV'
      -- AND RTA.ORG_ID = 102
       AND RTA.SET_OF_BOOKS_ID = '2021'
       AND rta.previous_customer_trx_id IS NULL
       AND cust_acct.account_number =
              NVL (:P_CUST_NUMBER, cust_acct.account_number)
       AND ps.ORG_ID = :P_ORG_ID
       AND HP.party_name = NVL (:P_CUST_NAME, HP.party_name)
       AND ps.TRX_NUMBER <= NVL (:P_FROM_INVOICE_NO, ps.trx_number)
       AND ps.TRX_NUMBER >= NVL (:P_TO_INVOICE_NO, ps.trx_number)
       and  TRUNC (ps.gl_date) <= :P_AS_ON_DATE
       AND PS.AMOUNT_DUE_REMAINING <> 0
       ORDER BY TRX_DATE