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;