Entries by Oracle ERP Apps Guide

, , , ,

Query to extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,papf.effective_start_date employee_start_date,papf.effective_end_date employee_end_date,papf_cont.full_name contact_name, hl.meaning contact_type,pcr.date_start contact_start_date, pcr.date_end contact_end_dateFROM per_contact_relationships pcr,per_all_people_f papf,hr_lookups hl,per_all_people_f papf_contWHERE 1 = 1AND papf.person_id = pcr.person_idAND pcr.contact_person_id = papf_cont.person_idAND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)AND hl.lookup_type(+) = ‘CONTACT’AND hl.lookup_code(+) = pcr.contact_type

/by
, , , ,

Workflow Tables Information in R12

WF_ITEM_TYPES – It defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE WF_ITEM_ATTRIBUTES – stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL WF_ACTIVITIES […]

/by
, , , ,

A comparative analysis between SQL*LOADER and UTL_FILE utility

In implementing new systems we come across problems of importing “alien” data. This may be coming from a legacy system or an on-going system. This data is transported via extract files from the legacy system to the Oracle system. The gateway to Oracle for this data is SQL*Loader and data is loaded into tables via a control […]

/by
, , , , ,

Script to display status of all the Concurrent Managers

SELECT DISTINCT concurrent_process_id “Concurrent Process ID”,       pid “System Process ID”, os_process_id “Oracle Process ID”,       q.concurrent_queue_name “Concurrent Manager Name”,       p.process_status_code “Status of Concurrent Manager”,       TO_CHAR(p.process_start_date,’MM-DD-YYYY HH:MI:SSAM’) “Concurrent Manager Started at”  FROM fnd_concurrent_processes p,       fnd_concurrent_queues q,       fnd_v$process WHERE q.application_id = queue_application_id   AND q.concurrent_queue_id = p.concurrent_queue_id   AND spid = os_process_id   AND process_status_code NOT IN (‘K’, ‘S’)ORDER BY concurrent_process_id, os_process_id, […]

/by