SQL Queries

Fusion – Query to extract Customer Details

select hp.party_name customer_name, a1.account_number customer_number, a3.STATUS site_status,  SITE_USE_CODE,LOCATION,PRIMARY_FLAG ,PARTY_SITE_NUMBER,A5.ADDRESS1,A5.ADDRESS2,A5.ADDRESS3,A5.ADDRESS4,A5.CITY,A5.POSTAL_CODE,A5.STATE,A5.COUNTRY  from hz_cust_accounts a1,hz_cust_acct_sites_all a2,  hz_cust_site_uses_all a3,HZ_PARTY_SITES A4,HZ_LOCATIONS A5,  hz_parties hp  where a1.CUST_ACCOUNT_ID=a2.CUST_ACCOUNT_ID  and a1.party_id=hp.party_id  and a2.CUST_ACCT_SITE_ID=a3.CUST_ACCT_SITE_ID  and nvl(A1.STATUS,’A’)=’A’  and nvl(A3.STATUS,’A’)=’A’  AND A2.PARTY_SITE_ID=A4.PARTY_SITE_ID  AND A4.LOCATION_ID=A5.LOCATION_ID

Read More

Fusion – Global Human Resources Tables

Global Human Resources Tables EEC_CONTEST_HDR EEC_CONTEST_METRIC_DETAILS EEC_CONTEST_PARTICIPANTS EEC_CONTEST_REWARDS EEC_CONTEST_STAGES EEC_CONTEST_TEMPLATE_DTL EEC_CONTEST_TEMPLATE_HDR_B EEC_CONTEST_TEMPLATE_HDR_TL EEC_CONTEST_UPDATES EEC_CONTEST_VOTERS EEC_CONTEST_VOTE_CAST EEC_CONTEST_WINNERS EEC_LB_CONTEST_DETAILS EEC_METRICS_B EEC_METRICS_TL EEC_REWARDS_B EEC_REWARDS_TL EEC_TEAM HCM_EXTENDED_LOOKUP_CODES_B HCM_EXTENDED_LOOKUP_CODES_TL HR_ALL_ORGANIZATION_UNITS_F HR_ALL_ORGANIZATION_UNITS_F_ HR_ALL_POSITIONS_F HR_ALL_POSITIONS_F_ HR_ALL_POSITIONS_F_TL HR_ALL_POSITIONS_F_TL_…

Read More

Open Account Balances Data Manager (XLATBDMG) / Accounts Payable Trial Balance (APTBRPT) Performance Issue in R12

The “Accounts Payable Trial Balance” / “Open Account Balances Data Manager” report having severe performance issues. The rebuild it self taken more than 4 hours. Also there was too much…

Read More

Create Requisition interface against the blanket purchase agreement for different Locations

AIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations. SCRIPT: CREATE OR REPLACE PACKAGE BODY APPS.XXTTK_BPO_PKG IS PROCEDURE XXTTK_REQ ( ERRORBUF     OUT       VARCHAR2…

Read More

Customer Site Usage Import Error Query – FBDI Oracle Fusion

The below query extracts the error details along with interface attributes of Customer Site Usages import using FBDI in Fusion Applications. SELECT distinct err.message_name ERROR_MESSAGE ,msg.message_text MESSAGE_TEXT ,site_use.* FROM hz_imp_errors…

Read More

Customer Site Import Error Query – FBDI Oracle Fusion

The below query extracts the error details along with interface attributes of Customer Sites import using FBDI in Fusion Applications. SELECT distinct err.message_name ERROR_MESSAGE ,msg.message_text MESSAGE_TEXT ,site.* FROM hz_imp_errors err…

Read More

Customer Contact Points Import Error Query – FBDI Oracle Fusion

The below query extracts the error details along with interface attributes of Customer Contact Points import using FBDI in Fusion Applications. SELECT distinct err.message_name ERROR_MESSAGE ,msg.message_text MESSAGE_TEXT ,cpt.* FROM hz_imp_errors…

Read More

Customer Account Import Error Query – FBDI

The below query extracts the error details along with interface attributes of Customer Account import using FBDI in Fusion Applications. SELECT distinct err.message_name ERROR_MESSAGE ,msg.message_text MESSAGE_TEXT ,acc.* FROM hz_imp_errors err…

Read More

Global Temporary Table ( GTT) – key facts

The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior…

Read More

Oracle 12c SQL Plan Directives – Disable | Enable | Use As Hint

What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a…

Read More