Oracle EBS - AP_SUPPLIER_CONTACTS was not used in R12

AP_SUPPLIER_CONTACTS will be instead of below tables.

HZ_PARTIES

HZ_RELATIONHIPS

HZ_ORG_CONTACTS

hz_contact_points 

The link column in AP_SUPPLIER_CONTACTS map tp the following tables/columns

Per_Party_ID = Party_ID of PERSON Party in HZ_PARTIES
Relationship_ID = Relationship_ID of Rows in HZ_RELATIONHIPS
Rel_Party_ID = Party_ID of PARTY_RELATIONSHIP Party in HZ_PARTIES
Party_Site_ID = Maps to the Party Site created for the PARTY_RELATIONSHIP Party
Org_Contact_ID = Org_Contact_ID from HZ_ORG_CONTACTS
Org_Party_Site_ID = Party_Site_ID of the Supplier Site row.

Assuming you know the party_site_id for an address (which is the value in AP_SUPPLIER_SITES_ALL.party_Site_ID for a site that is associated with that address) you can use this query to get the contact for that address.

SELECT PERSON.person_first_name, PERSON.person_last_name, PTY_REL.address1,
PTY_REL.City, PTY_REL.state, PTY_REL.country,
PTY_REL.Primary_phone_area_code, PTY_REL.primary_phone_number
FROM hz_parties PERSON, hz_parties PTY_REL, ap_supplier_contacts APSC
WHERE APSC.per_party_id = PERSON.party_id
AND APSC.rel_party_id = PTY_REL.party_id
AND APSC.org_party_site_id = <Party_Site_Address_ID>

Oracle Forms - How to create an Alert or Message

How to create an Alert or Message

Oracle Forms - How to avoid duplicated records in a block

The purpose is to reject two records that contain duplicated values.

The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.

It uses two calculated items, one in the data bock and another in a control block.

The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:

Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)

Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.

Function COMPARAISON (val1 varchar2, val2 varchar2)

Return number

Is

   answer number := 0;

Begin

   if val1 = val2 then

      answer := 1;

   end if;

   return(answer);

End;

COMPARAISON is a program unit stored in the Forms module.

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.

The first value (:ctrl.charsave) contains the bakup value of the current record.

The DEPT block must have the following properties setting:

Query all records

YES

The CTRL block must have the following properties setting:

Query all records	YES
Single record	YES
Database data block	NO

The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.

It summarize the values contained in all the rows of the DEPT block (dept.match_found).

If the total is greater than 1, we have two duplicated data.

The sample dialog

·         Download the DUPLICATES.fmb sample dialog for you to test

How to Get Previous Record Value in Oracle Forms

DECLARE
   l_prev_empno   emp.empno%TYPE;
   l_prev_ename   emp.ename%TYPE;
   l_prev_job     emp.job%TYPE;
BEGIN
   IF TO_NUMBER (:SYSTEM.cursor_record) > 1
   THEN
      PREVIOUS_RECORD; /* move to the previous record and get previous record values */
      l_prev_empno := :emp.empno;
      l_prev_ename := :emp.ename;
      l_prev_job := :emp.job;
      NEXT_RECORD;    /* come back to the current record */
   END IF;
END;

How to Check First Record and Last Record in Oracle Forms

BEGIN
   IF :SYSTEM.Cursor_Record = '1'
   THEN
      MESSAGE ('At first record.');
   END IF;
END;

BEGIN
   IF :SYSTEM.LAST_RECORD = 'TRUE'
   THEN
      MESSAGE ('At last record.');
   END IF;
END;

Oracle Forms - How to support shift + F6 to duplicate record

1. Add trigger KEY_DUPREC in block level

2. Put " duplicate_record; " inside.

Kill session for lock

1. find target session.

SELECT aob.object_name
,aob.object_id
,b.process
,b.session_id,c.SID, c.SERIAL#,c.LOGON_time,c.seconds_in_wait, c.state
FROM all_objects aob, v$locked_object b, v$session c
WHERE aob.object_id = b.object_id
and b.session_id = c.sid


2. delete session.

alter system kill session '<sid>,<serial>'

How to open multiple OAF to work

As an Oracle ERP engineer, we often need to access multi window in OAF to execute job, then below method will help you to get it!

1) make a IE shortcut and edit attribute.
2) put -nomerge in the target bottom.

How to resume PR lines in AutoCreate Documents

It's often to get buyer to request to resume PR lines in AutoCreate Document pool. It could be some reasons like they don't want to group lines or somehow to cancel PO etc..

2 actions to resume:

1:  Clean line_location_id value in po_requisition_lines_all.

2:  Put 'Y' in reqs_in_pool_flag in po_requisition_lines_all. 

Then you will see  they show up in the pool.

Oracle EBS R12 interface & APIs Information Document - Integration Repository

Add Integration Repository responsibility.

Forms Process (FRMWEB) Consumes 100% of CPU

1) First we had to update the context file on the application middle tier as follows:

s_forms_catchterm=0

2) Since the FORMS_RECORD_GROUP_MAX is not a default variable in the app tier context file, you need to create a custom template as follows.

cd $AD_TOP/admin/template
mkdir custom
cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/template/custom/APPLSYS_ux.env

Edit the custom template file:

vi $AD_TOP/admin/template/custom/APPLSYS_ux.env

Set the environment variable FORMS_RECORD_GROUP_MAX to a high value such as 10000 or greater.

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

Save the file.

3)Run autoconfig utility for apps middle tier server.

4)Start app tier services and test.

Ref (Doc ID 745711.1)

How to re-Compile Invalid Objects (Package, Function, Procedure, View, Trigger)

Step 1. Create sql file as compile.sql in shell and content as below:

connect &1/&2

set pages 1000
set head off
set feedback off

spool compile1.sql
prompt set feedback on
select 'alter '||decode(object_type,'PACKAGE BODY',
    'PACKAGE',object_type)||' '||object_name||' '||
     decode(object_type,'PACKAGE BODY','compile body','compile')||';'
from user_objects
where status='INVALID'
/
spool off
@compile1.sql


Step 2. Login Oracle by sqlplus:

#sqlplus / as sysdba

Step 3. execute sql file:

> @compile.sql

HZ & HR Employee tables

1.PER_ALL_ASSIGNMENTS_F

2.PER_ALL_PEOPLE_F

1. HZ_PARTIES

2. HZ_ORGANIZATION_PROFILES

3. HZ_PERSON_PROFILES

4. HZ_CUSTOMER_PROFILES

5. HZ_LOCATIONS

6. HZ_PARTY_SITES

7. HZ_PARTY_SITE_USES

8. HZ_CUST_ACCOUNTS

9. HZ_CUST_ACCT_SITES

10. HZ_CUST_SITE_USES

11. HZ_CONTACT_POINTS

12. HZ_ORG_CONTACTS

13. HZ_ORG_CONTACT_ROLES

14. HZ_RELATIONSHIPS

15. HZ_CLASS_CATEGORIES

16. HZ_CLASS_CODE_DENORM

17. HZ_CODE_ASSIGNMENTS

18. HZ_CLASS_CATEGORY_USES

19. HZ_RELATIONSHIP_TYPES

PO_EMPLOYEE_HIERARCHIES_ALL