Search This Blog

Thursday, June 1, 2017

Fusion Queries for Bank Name and Nationality


Bank Query:

Select distinct bank_name from pay_bank_accounts

 

Nationality:

select lookup_code,meaning from hr_lookups where lookup_type like 'NATIONALITY' and enabled_flag = 'Y'

Wednesday, May 24, 2017

Fusion Payroll Query to get Payroll Relationship and BU Name

select p.person_number,
py.payroll_name,haou.name BUName
from PAY_ASSIGNED_PAYROLLS_DN ap
,    pay_PAYROLL_TERMS pt
,    PAY_PAY_RELATIONSHIPS_DN pr
,    PAY_ALL_PAYROLLS_F py
,    PER_ALL_PEOPLE_F p
,    per_all_assignments_m paam
,    hr_all_organization_units haou
,    (select distinct person_id from per_person_secured_list_v) ppsl
where ap.payroll_term_id = pt.payroll_term_id
and   pt.payroll_relationship_id = pr.payroll_relationship_id
and   ap.payroll_id = py.payroll_id
and   pr.person_id = p.person_id
--and p.person_number = '2824'
and ap.lsed is null
and paam.person_id=p.person_id
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between p.effective_start_date and p.effective_end_date
and paam.assignment_type = 'E'
and paam.primary_flag = 'Y'
and haou.organization_id = paam.business_unit_id
and ppsl.person_id = p.person_id

Sunday, May 7, 2017

Encryption Concept

1. Customer will encrypt the date using Fusion provided public key.
2. Security Console setup will be done in Fusion which will decrypt the data before loading to system
3. Data to be sent out will be encrypted using Customer provided public key. So customer needs to provide public key.
4. Always public keys are shared.

Thursday, March 16, 2017

Taleo Client Connect (TCC) erroring out with Workflow Execution Failed error

Error while running TCC from Unix server

While running TCC through UNIX below error is coming.

2017-03-16 18:27:25,543 [ERROR] Client - The workflow execution failed!
Error: The HTTP request was unable to resolve the specified host.Use the monitoring feature to get more information.
2017-03-16 18:27:25,543 [ERROR] Client - The workflow execution failed!
Error: The HTTP request was unable to resolve the specified host.Use the monitoring feature to get more information.
2017-03-16 18:27:25,543 [ERROR] Client - The workflow execution failed!
Error: The HTTP request was unable to resolve the specified host.Use the monitoring feature to get more information.

Resolution:

In the cfg file for Send, Poll and Retrieve tabs, End Point source should be "Provided in the Current Step"

Monday, December 13, 2010

Matrix Report in XML Publisher

This report is number of JVs created per month per source wise.

Query:
select je_source,to_char(date_created,'Mon-YY') Source_Month,count(je_header_id) je_count from gl_je_headerswhere to_char(date_created,'Q') = :p_quarter and to_char(date_created,'YYYY') = :p_year group
 by je_source,to_char(date_created,'Mon-YY')
RDF report:
    

Total Number of JVs Quarterly

Source Name
GSource_MonthE
Total
GJE_SOURCE
G If exists: JE_COUNT end; E
999E
Total
G If exists: 999 end; E


O              
G Close to JE_SOURCE - <?for-each-   group@section:G_SERVICE_NAME;./JE_SOURCE?><?sort:JE_SOURCE;'ascending';data-type='text'?>

 JE_SOURCE - <?JE_SOURCE?><?variable@incontext:JS;JE_SOURCE?>
       
 G close to Source_Month :
<?for-each-group@column:G_SERVICE_NAME;./SOURCE_MONTH?>
Source_Month - <?SOURCE_MONTH?>

E  - <?end for-each-group?>
G Near If Exists -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?
IF Exists –
<?if:count(current-group()[JE_SOURCE=$JS])?>

JE_COUNT - <?current-group()[JE_SOURCE=$JS]/JE_COUNT?> 
End; - <?end if?>

E  - <?end for-each-group?>
G in Total -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?

IF Exists in Total –
<?if:count(current-group())?>
999 in Total
<?sum(current-group()/JE_COUNT)?>
End if in Total:
<?end if?>
E in Total –
<?end for-each-group?>

999 in Total Column - <?sum(//G_SERVICE_NAME[JE_SOURCE=$JS]/JE_COUNT)?>
E in Total Column:
<?end for-each-group?>


      

     


Sunday, December 12, 2010

How to Use FND_STANDARD_DATE value set

Let’s take example of report – AP Invoices Paid in Time
Here GL_Date in Date parameter.
SELECT api.invoice_id, api.invoice_num, invoice_date, api.invoice_amount,
       api.vendor_name, api.vendor_number, apc.check_number, aps.due_date,
       apc.check_date
  FROM ap_invoices_v api,
       ap_payment_schedules_all aps,
       ap_invoice_payments_all aip,
       ap_checks_all apc
 WHERE api.invoice_id = aps.invoice_id
   AND aip.invoice_id = api.invoice_id
   AND aip.check_id = apc.check_id
   AND apc.check_date <= aps.due_date
   --and api.gl_date >= nvl(:p_gl_date,api.gl_date)
   AND api.gl_date >=
          NVL (TO_DATE (SUBSTR (:p_gl_date, 1, 10), 'YYYY/MM/DD'),
               api.gl_date)

That's it. Test the report through SRS window.

Stock Market in India Development of Stock Market in India 
 

Tuesday, December 7, 2010

HRMS APIs

Below are use full API in HRMS most of time these API are used to migrate external data from legacy system to oracle apps
Updating the Per_periods_of_service table using
hr_ex_employee_api.update_term_details_emp 
Terminate employee
hr_ex_employee_api.actual_termination_emp 
Applying the final process in the shared instance using hr_ex_employee_api.final_process_emp 
Re – Hire Employee
hr_employee_api.re_hire_ex_employee 
Update  Existing Person in system
hr_person_api.update_us_person 
Hire New Emp
hr_employee_api.create_us_employee 
Costing
pay_cost_allocation_api.create_cost_allocation 
Load  Assignment
hr_assignment_api.update_us_emp_asg 
Load Update Assign Criteria
hr_assignment_api.update_emp_asg_criteria 
Note : Contact Person Id Is To Be Passed
hr_contact_rel_api.create_contact 
Note : Contact Person Id is passed as Null(default of API)
hr_contact_rel_api.create_contact 
Load Phones
hr_phone_api.create_phone
hr_person_address_api.update_person_address 
Load Addresses
hr_person_address_api.update_person_address
hr_person_address_api.create_person_address 
Load Payment Methods
hr_personal_pay_method_api.create_us_personal_pay_method 
Element Loading
py_element_entry_api.create_element_entry
py_element_entry_api.update_element_entry 
Load Salaries
hr_upload_proposal_api.upload_salary_proposal 
Salary Proposal
hr_maintain_proposal_api.approve_salary_proposal 
State tax rules
pay_state_tax_rule_api.create_state_tax_rule
pay_state_tax_rule_api.update_state_tax_rule 
County Tax Rules
pay_county_tax_rule_api.create_county_tax_rule
pay_county_tax_rule_api.update_county_tax_rule 
City Tax Rules
pay_city_tax_rule_api.create_city_tax_rule
pay_city_tax_rule_api.update_city_tax_rule 
Schools and Colleges
per_esa_upd.upd
per_esa_ins.ins 
Performance Reviews
hr_perf_review_api.create_perf_review
hr_perf_review_api.update_perf_review 
State Information Taxes
hr_sit_api.update_sit
hr_sit_api.create_sit 
Qualifications
per_qualifications_api.create_qualification
per_qualifications_api.update_qualification 
Locations
hr_location_api.update_location
hr_location_api.create_location 
Organization
hr_organization_api.update_organization
hr_organization_api.create_org_classification 
If any Change in Organization information.
if information2 = 'Y' then
hr_organization_api.enable_org_classification 
If any Change in Organization information.
if information2 = 'N' then
hr_organization_api.disable_org_classification 
If Organization does not exist in instance
hr_organization_api.create_organization api 
Jobs
hr_job_api.update_job
hr_job_api.create_job 
Positions
hr_position_api.update_position
hr_position_api.create_position