Posts by Doyensys

Fusion HCM – Templates for Loading Work Structures using HDL

Grade : METADATA|Grade|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|SetCode|GradeCode|GradeName|ActiveStatusMERGE|Grade|SourceSystemOwnerName|SourceSystemIdName|1951/01/01|4712/12/31|COMMON|GradeCodeName|GradeName|A Location : METADATA|Location|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|LocationCode|LocationName|Description|SetCode|Country|ActiveStatus|ShipToSiteFlag|ReceivingSiteFlag|BillToSiteFlag|OfficeSiteFlag|Building|PostalCode|TownOrCity|Region1|Region2|AddressLine1MERGE|Location|SourceSystemOwnerName|SourceSystemIdName|1951/01/01|4712/12/31|LocationCode|LocationName|Description|COMMON|US|A|Y|Y|Y|Y|Building|PostalCode|New York|New York|NY|AddressLine1 Job : METADATA|Job|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|SetCode|JobCode|Name|ActiveStatus|MedicalCheckupRequired|BenchmarkJobFlagMERGE|Job|SourceSystemOwnerName|SourceSystemIdName|1950/01/01|4712/12/31|COMMON|ORA_CON|Oracle Consultant|A|N|N Position : METADATA|Position|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|BusinessUnitName|PositionCode|Name|ActiveStatus|DepartmentName|JobSetCode|JobCode|LocationCode|FullPartTime|RegularTemporary|HiringStatus|FTE|HeadCount|PositionTypeMERGE|Position|SourceSystemOwnerName|SourceSystemIdName|1951/01/01|4712/12/31|BusinessUnitName|PositionCode|Cloud Applications Marketing|A|Cloud Applications – US|COMMON|JobCode|LocationCode|FULL_TIME|R|APPROVED|10|10|SHARED Organization : METADATA|Organization|SourceSystemOwner|SourceSystemId|Name|ClassificationCode|EffectiveStartDate|EffectiveEndDate|LocationId(SourceSystemId)|LocationSetCodeMERGE|Organization|SourceSystemOwnerName|SourceSystemIdName1|Cloud Applications – Consultant|DEPARTMENT|1951/01/01|4712/12/31|LocationSourceSystemIdName|COMMONMETADATA|OrgUnitClassification|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|OrganizationId(SourceSystemId)|ClassificationCode|SetCode|StatusMERGE|OrgUnitClassification|SourceSystemOwnerName|SourceSystemIdName2|1951/01/01|4712/12/31|SourceSystemIdName1|DEPARTMENT|COMMON|A

Read More

Error in password verification for APPS

FNDCPASS  error in R12 as below. [app***@yrdy-testapp ~]$ cat L6630162.log +—————————————————————————+ Application Object Library: Version : 12.1 Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved. FNDCPASS: +—————————————————————————+…

Read More

Attach block volume using ICSCI commands and create mount points in OCI

Attach created storage in new server using ISCSI commands: Verify storage attachment using fdisk fdisk –l Create physical volume pvcreate /dev/sdb Create volume group Vgcreate bvdemo /dev/sdb Create logical volumes…

Read More

Fusion HCM – Template for Loading Workers using HDL

METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|ActionCode|BloodType|CountryOfBirth MERGE|Worker|SourceSystemOwnerName|PER-XXXX|2017/01/01|4712/12/31|XXXX|2017/01/01|1984/08/08|HIRE|A+|US METADATA|PersonName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|PersonNumber|LegislationCode|NameType|LastName|FirstName|Title|EffectiveStartDate|EffectiveEndDate MERGE|PersonName|SourceSystemOwnerName|PN-XXXX|PER-XXXX|XXXX|US|GLOBAL|Chan|Jackie|Mr.|2017/01/01|4712/12/31 METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|PersonNumber|DateStart|LegalEmployerName|WorkerType|PrimaryFlag|ActionCode MERGE|WorkRelationship|SourceSystemOwnerName|WR-XXXX|PER-XXXX|XXXX|2017/01/01|LegalEmployerName|E|Y|HIRE METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|AssignmentNumber|PeriodOfServiceId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|ActionCode|PersonId(SourceSystemId)|PersonNumber|LegalEmployerName|DateStart|WorkerType|BusinessUnitShortCode MERGE|WorkTerms|SourceSystemOwnerName|WT-XXXX|ETXXXX|WR-XXXX|2017/01/01|4712/12/31|1|Y|HIRE|PER-XXXX|XXXX|LegalEmployerName|2017/01/01|E|BusinessUnitShortCode METADATA|Assignment|SourceSystemOwner|SourceSystemId|AssignmentNumber|WorkTermsAssignmentId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|DateStart|ActionCode|PersonTypeCode|LegalEmployerName|BusinessUnitShortCode|JobCode|DepartmentName|LocationCode|PrimaryAssignmentFlag|PrimaryFlag|ManagerFlag MERGE|Assignment|SourceSystemOwnerName|ASG-XXXX|EXXXX|WT-XXXX|PER-XXXX|XXXX|2017/01/01|4712/12/31|1|Y|2017/01/01|HIRE|Employee|LegalEmployerName|BusinessUnitShortCode|JobCode|DepartmentName|LocationCode|Y|Y|N METADATA|PersonLegislativeData|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|MaritalStatus|Sex MERGE|PersonLegislativeData|SourceSystemOwnerName|PL-XXXX|2017/01/01|4712/12/31|PER-XXXX|US|M|M

Read More

GL Account analysis for PA Expenditure & Event query

                 /*  1. GL Account analysis for PA Expenditure */                     SELECT   ‘Expenditure’ revenue_type,…

Read More

Query To Find Contracts With Revenue on Hold in Oracle Fusion

SELECT PPA.segment1 project_number    ,OCTV.name contracty_type    ,PPA.name project_name    ,HOU.name organization_name    ,PPA.project_currency_code    ,TO_CHAR (PPA.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) completion_date    ,ROUND((NVL((INV.cont_curr_billed_amt), 0)), 2) project_to_date_Invoiced_sum    ,ROUND((NVL((INV_UNRECOG.cont_curr_billed_amt), 0)), 2) Draft_invoice_amount    ,ROUND(NVL((SELECT SUM(PNAR_AMOUNT)                  …

Read More

Quick installation of OCI client

Below command will quickly install all perl/python libraries along wit CLI sudo yum install gcc libffi-devel python-devel openssl-devel sudo easy_install pip curl -L “https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh” | bash  [root@instance-20180730-1239 opc]# bash -c…

Read More

Function To Get Bill Rate Of Employee in Specific Project in Oracle Fusion

FUNCTION get_bill_rate (      p_project_id IN NUMBER,           p_person_id  IN NUMBER   )RETURN NUMBERAS  ln_bill_rate NUMBER;BEGIN    SELECT DISTINCT bro.rate     INTO ln_bill_rate    FROM pjb_bill_plans_b bpb,         pjb_bill_rate_ovrrds bro,         per_all_people_f_v ppn,         (SELECT DISTINCT…

Read More

TUNING SQL STATEMENTS USING SQL TUNING ADVISOR (WITHOUT ENTERPRISE MANAGER)

Below are the steps to use SQL Tuning Advisor for particular SQL.1) Create tuning task using SQL_ID2) Execute tuning task3) Generate report for the tuning taskSTEP 1:CREATE tuning tasks for…

Read More

Query to Find AP Reimbursable Expenses in Oracle Fusion

WITH GET_PERIOD_AVG_RATE        AS (  SELECT gper.avg_rate,                     gsob.CURRENCY_CODE AS functional_currency,                     gper.period_name,                     gper.to_currency_code                FROM GL_LOOKUPS lk,                     GL_TRANSLATION_RATES gper,                     gl_sets_of_books gsob               WHERE     lk.lookup_type = ‘TRANSLATION_BAL_TYPE’                     AND gper.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID                     AND lk.lookup_code = gper.actual_flag           …

Read More