Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64

Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (Doc ID 1330701.1)

SQL Query to get payslip of an employee

below is a useful SQL statement to retrieve a payslip of a certain employee in a certain calendar month.
just replace the calendar name and the emp number with yours and run it.


SELECT   ppa.date_earned, per.full_name, per.employee_number,
         NVL (pet.reporting_name, pet.element_name), piv.NAME,
         prrv.result_value, ptp.period_name

Query : Get Bank Information from HZ Tables

select * from 
hz_parties bankparty, hz_code_assignments bankca
where 
bankparty.party_type = 'ORGANIZATION'

Query : R12 Link ap_invoice_distributions_all to a GL Header via SLA

SELECT c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num, te.source_id_int_1, te.application_id, te.entity_id,
h.je_source,
h.je_category,
i.gl_date,
s.vendor_name,

Query : Drilldown from GL to Receiving Transactions

SELECT b.NAME je_batch_name,
b.description je_batch_description,
b.running_total_accounted_dr je_batch_total_dr,
b.running_total_accounted_cr je_batch_total_cr,
b.status je_batch_status,

Query : To get Menus Associated with responsibility

SELECT DISTINCT a.responsibility_name, c.user_menu_name 
FROM apps.fnd_responsibility_tl a, 
apps.fnd_responsibility b, 
apps.fnd_menus_tl c, 
apps.fnd_menus d, 

Query : to find the process in Workflow using a known function name or Activity name

select 
wat1.display_name "process name",wat.display_name "activity name",wa.function "function" 
from wf_process_activities wpa
,wf_activities_tl wat
,wf_activities_tl wat1
,wf_activities wa

EXTRA PERSON INFORMATION (EIT) UPLOAD API — PROCEDURE

Following Procedure used for uploading data in Extra Person Information.  

1) Create a EIT using System Administrator Responsibility.

Application>>Flexfield>>Descriptive>>Segments

Oracle EBS/Apps R12. ( Oracle HR / Payroll ) Important Queries

select paf.ASSIGNMENT_NUMBER,

       ppf.FULL_NAME,

       pet.element_name,

       pca.CONCATENATED_SEGMENTS,

       decode(pc.DEBIT_OR_CREDIT,

Query to find out payroll Costing Detail / ”How to find out payroll costed accounts” / “Query to find out Payroll Charged Accounts”.

SELECT distinct d.EMPLOYEE_NUMBER,d.FULL_NAME,a.CONCATENATED_SEGMENTS, a.element_name,decode(a.debit_or_credit,’Debit’,COSTED_VALUE,”) Debit  ,
decode(debit_or_credit,’Credit’,COSTED_VALUE,”) Credit

Query to Find All Active Employees and Current Salary.

select EMPLOYEE_NUMBER,a.FULL_NAME,x.USER_PERSON_TYPE, c.PROPOSED_SALARY_n Basi_Salary–,c.CHANGE_DATE–,c.*
from per_people_f A,per_assignments_f b,pER_PAY_pROposals c
,per_person_types x

Query fo Find out all Earning and Deduction Elements and values after Payroll Run

SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE,TP.PERIOD_NAME,paf.ORGANIZATION_ID,
sum(decode(pec.CLASSIFICATION_NAME,’Earnings’,to_number(rrv.result_value),
0)

Enable Export Feature in Custom Forms

In Order to Enable Export option, available in Oracle Application forms to export the data into Excel file.
In the custom form at form level trigger write the below code in When-New-Form-Instance trigger.
APP_SPECIAL.ENABLE('FILE.EXPORT', PROPERTY_ON);

Query for Oracle Payroll Salary Slip / Salary Slip Detail Report Query / How to find out detail of salary slip/ Element wise Landscape Salary Slip at Payroll Run Levle or quick pay level

Note:  I have hardcoded element names. You can also hardcode your elements to get Landscaped salary slip.

SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE  ,TP.PERIOD_NAME,
sum(decode(ety.element_name,’Basic Salary’                         ,TO_NUMBER(rrv.result_value),
‘Basic Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),

Difference Between Bitmap and character mode report

Here is an example to help explain how Oracle Reports are designed and printed in both the bitmap and character-mode environments. Assume you wish to print "Cc" where "C" is a different font and a larger point size than "c" and is in boldface type (where "c" is not). In Oracle Reports Designer, bitmap mode, you can make "C" bold and in a different font and point size than "c". This is because you are generating postscript

Copy One Parameter Value Into Another Parameter

To Copy the Parameter value passed in the one of the parameter to the below parameters,the concept of :$flex$ need to be used.
For Eg:
In a report there are two parameters,the valueset of the first parameter is xx_file_name is of validation type none.

LDT File generation for Lookups

The syntax for generation of LDT file for fnd lookups:
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct /home/applmgr/TEST.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME =application short nameLOOKUP_TYPE=lookup name

Exception handling for Bulk Insert and Bulk update using FORALL

DECLARE
TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;

FNDLOAD for Profile

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct filename.ldt PROFILE PROFILE_NAME="profile name" APPLICATION_SHORT_NAME="XXDIS"

FNDLOAD script for value set with values

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt VALUE_SET FLEX_VALUE_SET_NAME="valueset name"