Concurrent Requests Status and Phase Code Explained

Concurrent Request Status and Phase Code Combinations.

select  *  from apps.fnd_lookup_Values where lookup_type like 'CP_STATUS_CODE' AND VIEW_APPLICATION_ID =0

select  * from apps.fnd_lookup_Values where lookup_type like 'CP_PHASE_CODE' AND VIEW_APPLICATION_ID =0 

Oracle Form Personalization Modifying Record Group Query on LOV : frm-40502: oracle error: unable to read list of values

I had a requirement to modify LOV query for AR Receipt Form to restrict Customer for Specific Location.

Lov for Highlighted Field needs to be change

Step1:- Select Field that needs a Change in LOV, in this case it is Name, 

Go to                        Help --> Diagnostic --> Examine 

This will give you Exact Block_Name.Field_Name , In this case it is RGW_FOLDER.M_CUSTOMER_NAME

Step2: - We have to find out LOV Name mapped on the Name Field and corresponding Record group Query.

(Note - For following Step , make sure your Form Builder setup is completed. Copy resources, plsql and forms Folder from AU_TOP to your local and Add these folder in your Forms_Path.) 

Open FMB (ARXRWMAI.fmb) in your form builder, and search for M_CUSTOMER_NAME Field in RGW_FOLDER Block.

Check properties of this field and get LOV name. In our case it is "RGW_CUSTOMER_NAME_NOSKIP"

 

Now go to List of value section in the form and search for your LOV (RGW_CUSTOMER_NAME_NOSKIP) and get the Record Group name. In our case it is 'RGW_CUSTOMER_NAME'

Go to Record Group and Get the Query of this Record Group.

    select rtrim(substrb(party.party_name,1,50)) "party_name", 

               cust_acct.account_number "account_number", 

               party.jgzz_fiscal_code "jgzz_fiscal_code", 

               party.tax_reference "tax_reference", 

               cust_acct.cust_account_id "customer_id", 

               cust_acct.fob_point, 

               null "fob_point_meaning", 

               cust_acct.ship_via, 

               null "ship_via_meaning", 

               cust_acct.primary_salesrep_id, 

               null "primary_salesrep_name", 

               null "primary_salesrep_num" ,

               cust_acct.account_name account_description

        from hz_cust_accounts cust_acct,

                 hz_parties party, 

                 hz_customer_profiles cp 

        where cust_acct.cust_account_id = cp.cust_account_id (+)

                  and cust_acct.party_id = party.party_id 

                  and cp.site_use_id is null   

                  and (( (:ar_world.site_required_flag = 'Y' ) and

                    EXISTS (SELECT 1 FROM HZ_CUST_ACCT_SITES 

                           WHERE CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID 

                             )

                             )

              or :ar_world.site_required_flag = 'N')

Step3:- Now we have to use the Record Group query identified in last step and modify it, as per our business requirement using Form Personalization.

        

    Go to       Help-->Diagnostic--> Custom Code--> Personalize

A)- Add New Entry 

Seq: XX

Description: Modify Bill to Customer Name LOV 

Condition : WHEN-NEW-ITEM-INSTANCE

Trigger Object : RGW_FOLDER.M_CUSTOMER_NAME

Add New Entry

B) -Go to Actions

Seq:1 

Type: Builtin

Builtin Type :  Create Record Group From Query

Argument : Type your Modified Query

Group Name: XX_AR_GRP_NAME

Note : Sometime Query May give an Error like frm-40502: oracle error: unable to read list of values, 

Make Sure the Field that is mapped with your Field i.e. Party Name doesn't have any Alias in this Case

"select party.party_name,

       cust_acct.account_number "account_number", 

       party.jgzz_fiscal_code "jgzz_fiscal_code", 

       party.tax_reference "tax_reference", 

       cust_acct.cust_account_id "customer_id", 

       cust_acct.fob_point, 

       null "fob_point_meaning", 

       cust_acct.ship_via, 

       null "ship_via_meaning", 

       cust_acct.primary_salesrep_id, 

       null "primary_salesrep_name", 

       null "primary_salesrep_num" ,

       cust_acct.account_name account_description

from hz_cust_accounts cust_acct,

     hz_parties party, 

     hz_customer_profiles cp 

where cust_acct.cust_account_id = cp.cust_account_id (+)

  and cust_acct.party_id = party.party_id 

  and cp.site_use_id is null   

  and (

        EXISTS (SELECT 1 

                     FROM HZ_CUST_ACCT_SITES_all HCAS,

                       hz_cust_site_uses_all hcsua,

                         hz_party_sites hps

                         ,hz_locations hl

                   WHERE CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID 

                    AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id

                                and hcas.party_site_id=hps.party_site_id

                                    AND hps.location_id = hl.location_id

                                 and hl.country = 'XX' -- Select a Particular Country Customer

                         and hcsua.site_use_code= 'BILL_TO'

                             )

         )"

C)- 2nd Action

   

     Seq 2: 

    

     Type: Property

     

    Object Type : LOV

 

    Target Object : Name of LOV identified in 2nd Step (RGW_CUSTOMER_NAME_NOSKIP) 

     

    Property Name: GROUP_NAME

    Value : XX_AR_GRP_NAME

 

Save You work Close the Form and try LOV, it should have been modified.

Happy Learning!!!!

UMX/ Roles Security Queries

 -- Who all Users a particular Role/Responsibility Is Assigned

Select wlur.USER_NAME,

wfr.display_name,

wlur.ROLE_NAME,

wrh.super_name,

wfr.description,

wlur.ASSIGNMENT_REASON,

wrh.relationship_id,

wlur.START_DATE,

wlur.EXPIRATION_DATE,

wlur.EFFECTIVE_START_DATE,

wlur.EFFECTIVE_END_DATE

from APPS.WF_LOCAL_USER_ROLES wlur,

APPS.WF_ROLE_HIERARCHIES  wrh,

apps.UMX_ALL_ROLE_VL wfr

WHERE 1=1

AND wlur.role_name = wrh.sub_name (+)

and wfr.name =  wlur.role_name 

and wlur.USER_NAME = 'XXXXXX'  -- USER NAME

and sysdate between wlur.EFFECTIVE_START_DATE and wlur.EFFECTIVE_END_DATE

--and wfr.display_name like 'Receivables - Limited Access' ---- Responsibility Name

--and role_name ='FND_RESP|SQLGL|AMZ_GL_LOOK_CODE_MGR|STANDARD' -- Role Name

order by wlur.ROLE_NAME 

-- Responsibility and Role Name Join

SELECT --asn.LOOKUP_ASSIGNMENT_ID, 

uar.display_name "Responsbility Name",

uar.description "Responsiblity Description",

flv.MEANING "Role Category" ,

     fat.application_name,

     flv.description "Role Description",

asn.LOOKUP_TYPE CATEGORY_LOOKUP_TYPE, 

asn.LOOKUP_CODE CATEGORY_LOOKUP_CODE, 

asn.INSTANCE_PK1_VALUE WF_ROLE_NAME, 

uar.orig_system

FROM apps.fnd_lookup_assignments asn, 

apps.fnd_lookup_values_vl flv ,

apps.umx_all_role_vl uar,

apps.fnd_application fa,

apps.fnd_application_tl fat

WHERE flv.lookup_type = asn.LOOKUP_TYPE 

AND flv.lookup_code = asn.LOOKUP_CODE 

and uar.name = asn.INSTANCE_PK1_VALUE

and fa.application_short_name  = uar.owner_tag

and fa.application_id = fat.application_id

and fat.source_lang ='US'

AND flv.lookup_type = 'UMX_CATEGORY_LOOKUP' 

AND asn.obj_name = 'UMX_ACCESS_ROLE'

AND uar.status ='ACTIVE'

-- and asn.LOOKUP_CODE like '%AR%'

-- AND FA.APPLICATION_SHORT_NAME = 'SQLAP'

--and flv.MEANING = 'General Ledger Closer' ----- Role Description from Request Access Page

--and asn.INSTANCE_PK1_VALUE = 'FND_RESP|SQLAP|AMZ_AP_CN_ACCESS|STANDARD'

and uar.DISPLAY_NAME  = 'NZ - Receivables Inquiry' -- Responsibiity Name

--Assign a Security Admin Role to a User 

Begin

wf_local_synch.PropagateUserRole(

     p_user_name => 'XXXXXXX',

     p_role_name => 'UMX|SECURITY_ADMIN',

     p_start_date=> SYSDATE);

commit;

end;

User Preferences Values

Sometimes, we need to Update/retrieve User Preferences values Setup at User level in our code/Specifically in User specific application logic building.

So here are few pointers , one ought to know while working with User references.

 --> User Preferences are nothing but the System Profiles (Default from Site Level), starting with 'ICX%'%

 If some one Updates his/her user presences from EBS Home Page, updated values automatically gets reflects in System profiles as well  at the User Level (Not on Site level). 

 --> Function to get These values,

SELECT FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',<USER_ID>) FROM DUAL;

This function retrieves the value from profiles for a specific User , if their is no value found at user value it further goes down to site value level and fetch the value.

Below query also can be used to get the values , those are updated at User level.

SELECT

    VAL.PROFILE_OPTION_VALUE, OPT.PROFILE_OPTION_NAME,val.PROFILE_OPTION_ID

FROM

    APPLSYS.FND_PROFILE_OPTION_VALUES VAL,

    APPLSYS.FND_PROFILE_OPTIONS OPT,

    APPLSYS.FND_USER FUSER

WHERE

    VAL.PROFILE_OPTION_ID = OPT.PROFILE_OPTION_ID

    AND VAL.LEVEL_VALUE = FUSER.USER_ID

  --  AND OPT.PROFILE_OPTION_NAME LIKE 'ICX%'

    AND VAL.LEVEL_ID = 10004 -- USER LEVEL

    AND FUSER.USER_NAME = 'DEMOUSER';

Extend the existing translation (xlf) files for multiple languages

 

Sometime we need to extend/Enhance the existing Translation files in EBS Templates, here is the solution

 

Root cause : The issue with the translation of the report is because the trans unit block going in different language xlf files for new fields  of the report was different.

 

Solution : It’s a mandatory for translation to work that the trans unit id defined in the different language xlf files for the same fields or words  of the report needs to be same.

 

Below is the approach which needs to be followed for adding the new field in the existing xlfs  files :

1).Add the new element to  the rtf (taking example of the XX proforma report  XXCUST_AR_PROF_REPORT-01_en_US.rtf ).After saving the changes upload the rtf to its template XXCUST_AR_PROF_REPORT-01 .Thereafter, download the xlf file of the rtf you just uploaded by clicking export translations. (highlighted in yellow)

After clicking the export translation , it will ask to save/open the file .Save it.

 

2).Opening the above downloaded file , look for the new field you added. Copy the trans unit block for it and paste it in other xlfs present for that report.

Source is the field for which you need the translation to happen.

Target is the corresponding translated value for it.

Below is the screenshot from the  XXDON_AR_PROF_REPORT-01_en_US.xlf file( Saved in step 1).

Hope that helps!!!

DBC file path for J Developer

In the previous version of EBS (11.1....) , DBC file used to store in $FND_TOP/secure path under

"apps/apps_st/appl/fnd/12.0.0/secure."

Whereas in R12 version , location of DBC file changed to $FND_SECURE, this can be found under

"/appsDEV/inst/apps/DEV_rdcdxerpap01/appl/fnd/12.0.0/secure" Location

Convert Client Time zone date to Server Time Zone Date

Convert Client Time zone date to Server Time Zone Date



Select TO_CHAR (FND_TIMEZONES_PVT.ADJUST_DATETIME
                                (LAST_ACCPET_DATE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE,
                                                                   FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE),
                                                                   'DD-MON-YYYY HH24:MI:SS')
 from PO_LINE_LOCATIONS_ALL where line_location_id =xxxxxxxx;

Show Serial Number in RTF Template

XML Tag to show Serial Number in RTF template


<?position()/>

Delete Attachment in EBS

Hi,

If you are looking for a code that can favor you to Delete multiple/Single attachments, here is an oracle provided API that do fortunes with Attachments.

API Name:
fnd_attached_documents2_pkg 

1. If you want to delete all the attachments from specific transaction for example, Attachments from Sales order Headers or say GL Journal Header  , do Use

Procedure "DELETE_ATTACHMENTS" 

           This procedure attracts 3 mandatory parameters and those are

• ENTITY_NAME-->TABLE_NAME
• PK1_VALUE --> Transaction_Id (can be OrdeR_Header_id, Journal_Header_id)
• Delete_document_Flag --> 'Y'

This will delete all the available attachments from the Transaction.

2. If you want to delete Single attachments from specific transaction for example, Sequence No 10 from Attachments from Sales order Headers or say GL Journal Header  , do Use

Procedure "DELETE_ATTACHMENT" 

           This procedure attracts 2 mandatory parameters and those are

• attachment_document_id--> fnd_attached_docs_form_vl%attachment_document_id
• Delete_document_Flag --> 'Y'

This will delete Specific attachment (corresponding to the specific attachment_document_id) from fnd_attached_docs_form_vl