Wednesday, November 6, 2019

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>

Thursday, October 31, 2019

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

Friday, October 4, 2019

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;

Thursday, September 5, 2019

Wednesday, July 31, 2019

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>'

Thursday, July 25, 2019

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.




Monday, July 22, 2019

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.

Monday, February 25, 2019

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)

Sunday, January 13, 2019

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

Wednesday, January 2, 2019

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