Doyen Domains

Blog Archives

Follow us on

Total Page Views

1,198 views

Period End Processing Oracle Inventory and Oracle Cost Management

Period End Processing Oracle Inventory and Oracle Cost Management

Steps Oracle Inventory and Oracle Cost Management provide the required features to effect the necessary period-end procedures to: §     Reconcile the inventory and work in process costs and values. §     Transfer inventory and manufacturing costs to the General Ledger. §     Transfer summary or detail accounting information to the general ledger. §     Independently open and close periods for each inventory organization....[ read more ]

  • March 5, 2019 | 88 views

SQL Query to find details from PO till Cheque payment

SQL Query to find details from PO till Cheque payment

SQL Query to find details from PO till Cheque payment The following five components of  P2P Cycle Query are 1.    Requisition Detail 2.    Purchase Order Details 3.    Receiving Details 4.    Invoicing Detail 5.    Payment Details SELECT A.ORG_ID "ORG ID", E.VENDOR_NAME "VENDOR NAME", UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE", F.VENDOR_SITE_CODE "VENDOR SITE", F.ADDRESS_LINE1 "ADDRESS", F.CITY "CITY", F.COUNTRY "COUNTRY", TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE", D.SEGMENT1 "PO NUMBER",...[ read more ]

  • March 5, 2019 | 43 views

Fusion HCM – Templates for Loading Work Structures using HDL

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

  • March 4, 2019 | 48 views

Fusion HCM – Template for Loading Workers using HDL

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

  • March 4, 2019 | 31 views

GL Account analysis for PA Expenditure & Event query

GL Account analysis for PA Expenditure & Event query

                 /*  1. GL Account analysis for PA Expenditure */                     SELECT   'Expenditure' revenue_type,                               hca.cust_account_id  customer_id,                          ...[ read more ]

  • March 4, 2019 | 39 views

Query To Find Contracts With Revenue on Hold in Oracle Fusion

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)                   FROM (                        SELECT ((PEI.DENOM_RAW_COST/100) * (100 - NVL(PEI.REVENUE_RECOG_PERCENTAGE, 0))) PNAR_AMOUNT                          FROM PJC_EXP_ITEMS_ALL PEI                        ...[ read more ]

  • March 4, 2019 | 30 views

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

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 pcb.contract_id, pcb.project_id, ppb.segment1 AS project_number            FROM pjb_cntrct_proj_links pcb, pjf_projects_all_b ppb           WHERE pcb.project_id = ppb.project_id) proj  WHERE bpb.bill_plan_id = bro.bill_plan_id    AND bro.person_id = ppn.person_id    AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date  ...[ read more ]

  • March 4, 2019 | 27 views

Query to Find AP Reimbursable Expenses in Oracle Fusion

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            GROUP BY gper.avg_rate,                     gsob.CURRENCY_CODE,                     gper.period_name,                     gper.to_currency_code)SELECT ppat.segment1 project_num,       ppat.name project_name,       hou.name project_org,       hou1.name exp_org,       TO_CHAR (gjl.effective_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS gl_date,       gjh.period_name,       aid.amount invoice_amount,       gjh.currency_code journal_currency,       TO_CHAR (aid.pjc_expenditure_item_date, 'DD-MON-YYYY',...[ read more ]

  • March 4, 2019 | 32 views

Query to Find NonBillable Expense in Oracle Fusion

Query to Find NonBillable Expense in Oracle Fusion

SELECT pcdl.prvdr_gl_period_name gl_period,       trx_org.NAME expenditure_organization,       prb.project_id,       NVL (prb.segment1, 'N/A') project_number,       NVL (prl.NAME, 'N/A') project_name,       NULL project_type_class_code,       ppn.full_name employee_vendor,       papf.person_number employee_number,       ptv.task_name,       pec.expenditure_category_name expenditure_category,       pet.expenditure_type_name expenditure_type,       pcdl.prvdr_gl_date week_ending,       pei.expenditure_item_date expenditure_date,       pcdl.denom_currency_code entered_currency_code,       NVL (pcdl.denom_raw_cost, 0) entered_amount,       NVL (pcdl.acct_raw_cost, 0) accounted_amount,       NVL (pcdl.acct_exchange_rate, 1) accounted_exchange_rate,       NVL (pcdl.acct_rate_type, 'FIXED') accounted_exchange_type,       pcdl.prvdr_gl_date conversion_date,       gl.NAME set_of_books_name,       gl.currency_code book_currency_code,       pec1.expenditure_comment comments,       pei.orig_transaction_reference ap_invoice_number,       'PA' subledger_name,  FROM...[ read more ]

  • March 4, 2019 | 36 views

Fusion Query to get Revenue and billable hours of a Employee

Fusion Query to get Revenue and billable hours of a Employee

SELECT pcdl.org_id employee_cost_org_id, pcdl.prvdr_gl_period_name gl_period,        pcdl.prvdr_pa_period_name,        NVL (pei.override_to_organization_id,             pei.incurred_by_organization_id            ) expenditure_org_id,        trx_org.NAME expenditure_org, fnbu.bu_name AS exp_bu_name,        prb.carrying_out_organization_id project_owning_org_id,        (SELECT NAME           FROM hr_organization_units          WHERE organization_id =...[ read more ]

  • March 4, 2019 | 32 views