Showing posts with label Queries. Show all posts
Showing posts with label Queries. Show all posts

Friday, August 7, 2015

Query to Derive Customer Number, Site Use ID from Customer Bank Account Number

SELECT hca.account_number, hcsu.site_use_id, hca.cust_account_id INTO l_customer_number, l_cust_site_use_id, l_cust_account_id FROM hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, IBY_ACCOUNT_OWNERS IAO, IBY_EXT_BANK_ACCOUNTS IEBA WHERE hca.cust_account_id = hcas.cust_account_id AND hca.party_id = iao.account_owner_party_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.org_id = AND hca.status = 'A' AND hcas.status = 'A' AND hcsu.status = 'A' AND hcas.bill_to_flag = 'P' AND NVL (IAO.end_date, SYSDATE + 1) > SYSDATE AND ieba.bank_account_num = AND iao.ext_bank_account_id = ieba.ext_bank_account_id;

Tuesday, June 9, 2015

Oracle Apps : Apply AP Invoice Hold using API

The hold codes can be extracted from query below.

SELECT *
  FROM ap_hold_codes
 WHERE hold_type = 'INVOICE HOLD REASON';

Below is the API to apply hold using Standard Oracle API :


PROCEDURE inv_hold (p_invoice_id        IN     NUMBER,
                    p_reject_code       IN     VARCHAR2,
                    p_hold_val_status   IN     VARCHAR2,
                    x_status               OUT VARCHAR2)
IS
   l_hold_invoice_id   NUMBER := NULL;
   l_hold_err_msg      VARCHAR2 (2000) := NULL;
BEGIN
   l_hold_invoice_id := p_invoice_id;

   IF     l_hold_invoice_id IS NOT NULL
      AND p_reject_code IS NOT NULL
      AND p_hold_val_status = 'Y'
   THEN
      BEGIN
         ap_holds_pkg.insert_single_hold (
            X_invoice_id         => l_hold_invoice_id,
            X_hold_lookup_code   => p_reject_code);

         x_status := 'S';
      EXCEPTION
         WHEN OTHERS
         THEN
            l_hold_err_msg :=
                  'Unknow Exception Occured in Hold API call.. Exception Code : '
               || SQLCODE
               || ' Exception Details : '
               || SQLERRM;
            DBMS_OUTPUT.put_line (l_hold_err_msg);
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END inv_hold;



Thursday, June 4, 2015

Query to Find Attached Template Name from Template Code


SELECT template_code, file_name
  FROM xdo_templates_b a, xdo_lobs b
 WHERE     b.lob_code = a.template_code
       AND b.xdo_file_type = 'RTF'
       AND a.template_code = 'XXXBITEMPLATE';


Tuesday, May 26, 2015

Query to drill down from PO header to shipment



SELECT poll.*
  FROM po_headers_all poh,
       po_releases_all pora,
       po_lines_all pla,
       po_line_locations_all poll
 WHERE     poh.po_header_id = pora.po_header_id
       AND pla.po_line_id = poll.po_line_id
       AND poll.po_release_id = pora.po_release_id
       AND poh.segment1 = :po_number
       AND pora.release_num = :po_release_num
       AND pla.line_num = :po_line_num
       AND poh.org_id = :org_id;


Thursday, May 21, 2015

How To Disable/End Date/ Delete Responsibilities of a User from Backend/API


Below is the query.
Please not Responsibilities cannot be deleted in Oracle Apps from a User. It can only be Disabled or End Dated.


BEGIN
   FOR I
      IN (SELECT fu.user_id,
                 fu.user_name,
                 furg.RESPONSIBILITY_APPLICATION_ID,
                 frv.RESPONSIBILITY_NAME,
                 frv.RESPONSIBILITY_KEY,
                 fa.application_short_name,
                 frg.security_group_key
            FROM FND_USER fu,
                 FND_USER_RESP_GROUPS furg,
                 FND_RESPONSIBILITY_VL frv,
                 fnd_application fa,
                 fnd_security_groups frg
           WHERE     fu.user_id = furg.user_id
                 AND furg.responsibility_id = frv.responsibility_id
                 AND frv.application_id = fa.application_id
                 AND frv.data_group_id = frg.security_group_id
                 AND fu.user_name = 'ABC'
                 AND frv.RESPONSIBILITY_NAME IN ('Receivables Support'))
   LOOP
      BEGIN
         DBMS_OUTPUT.put_line (
               i.user_name
            || i.application_short_name
            || i.responsibility_key
            || i.security_group_key);
         fnd_user_pkg.delresp (username         => i.user_name,
                               resp_app         => i.application_short_name,
                               resp_key         => i.responsibility_key,
                               security_group   => i.security_group_key);
         DBMS_OUTPUT.put_line (
            i.RESPONSIBILITY_NAME || ' End Dated Successfully ');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while deleting responsibilty from the user and the error is '
         || SQLERRM);
END;
/


Tuesday, May 19, 2015

Query to Find Database Version in Oracle



select * from v$version;

Output:


Wednesday, January 28, 2015

Query to find the parent request id recursively

SELECT request_id,
  description
FROM fnd_concurrent_requests
WHERE NVL (request_type, 'X')        = 'M'
  CONNECT BY PRIOR parent_request_id = request_id
  START WITH request_id              = &RequestID;

Friday, October 17, 2014

Query to derive Qualifier Attribute and Context from Modifier Name

Query :

SELECT 
  c.list_line_no "Modifier Line No",
  b.name "Modifier Name",
  b.description  "Modifier Description",
  a.qualifier_context,
  a.qualifier_attribute,
  a.qualifier_attr_value
FROM qp_qualifiers_v a,
  qp_list_headers b,
  qp_list_lines c
WHERE a.list_header_id=b.list_header_id
and c.list_header_id=b.list_header_id
and c.list_line_id = a.list_line_id
and b.name = &Modifier_Name
ORDER BY a.creation_date DESC;



Friday, September 5, 2014

Status Codes and Phase Codes of a Concurrent Request : Oracle EBS R12

Concurrent Request Status Codes : 

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
     AND LANGUAGE = 'US'
     AND ENABLED_FLAG = 'Y';

LOOKUP_CODEMEANING
RNormal
INormal
ZWaiting
DCancelled
UDisabled
EError
MNo Manager
CNormal
HOn Hold
WPaused
BResuming
PScheduled
QStandby
SSuspended
XTerminated
TTerminating
AWaiting
GWarning

Concurrent Request Phase Codes: 

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
    AND LANGUAGE = 'US'
    AND ENABLED_FLAG = 'Y';


LOOKUP_CODEMEANINGDESCRIPTION
CCompletedRequest has finished
IInactiveRequest is unable to run
PPendingRequest is waiting to be run
RRunningRequest is running

Friday, July 4, 2014

Query to find rejection Messages during AP Interface : Oracle R12


SELECT air.*
FROM ap_interface_rejections air,
     ap_invoice_lines_interface aili,
     ap_invoices_interface aii
WHERE aii.invoice_id     = aili.invoice_id
AND aili.invoice_line_id = air.parent_id
AND aii.invoice_num      = &Invoice_Num;

Tuesday, July 1, 2014

Example : How to Generate Code Combination ID's (CCID) from Backend in Oracle R12

DECLARE
  l_code_combination_id       NUMBER;
  l_boolean                   BOOLEAN;
  l_segment_array             APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_structure_number          APPS.FND_ID_FLEX_STRUCTURES.id_flex_num%TYPE;
  l_id_flex_code              APPS.FND_ID_FLEX_STRUCTURES.id_flex_code%TYPE;
BEGIN
  APPS.FND_GLOBAL.APPS_INITIALIZE(USER_ID => FND_PROFILE.VALUE('USER_ID'),
                                  RESP_ID => FND_PROFILE.VALUE('RESP_ID'),
                                  RESP_APPL_ID => FND_PROFILE.VALUE('RESP_APPL_ID'));
                                 
SELECT fifs.id_flex_num ,
       fifs.id_flex_code
  INTO l_structure_number ,
       l_id_flex_code
  FROM APPS.FND_ID_FLEX_STRUCTURES fifs
 WHERE fifs.id_flex_code         = 'GL#'
   AND fifs.id_flex_structure_code = 'XX_ACCOUNTING_FLEXFIELD';                                
 
  l_segment_array(1) := '00000';
  l_segment_array(2) := '10000';
  l_segment_array(3) := '10324';
  l_segment_array(4) := '00000';
  l_segment_array(5) := '00000';
  l_segment_array(6) := '00000';
 
  l_boolean          := APPS.FND_FLEX_EXT.GET_COMBINATION_ID( APPLICATION_SHORT_NAME =>'SQLGL',
                                                              KEY_FLEX_CODE => l_id_flex_code,
                                                              STRUCTURE_NUMBER => l_structure_number,
                                                              VALIDATION_DATE => SYSDATE,
                                                              N_SEGMENTS => 6,
                                                              SEGMENTS => l_segment_array,
                                                              COMBINATION_ID => l_code_combination_id,
                                                              DATA_SET => -1);
 
  x_ccid             := l_code_combination_id;
 
  IF l_code_combination_id IS NULL THEN
    RAISE;
  ELSE
    dbms_output.put_line(l_code_combination_id);
  END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Sunday, June 15, 2014

Query to find the orig system reference for a Customer/Party and other TCA entities

If the data was imported into the EBS system from ABC system and ABC's party id was used as the cross system , the below query can be used to cross-link them

select ORIG_SYSTEM_REFERENCE from apps.HZ_ORIG_SYS_REFERENCES where owner_table_name = 'HZ_PARTIES' and ORIG_SYSTEM = ‘ABC’ and OWNER_TABLE_ID = <party_id in EBS>

ORIG_SYSTEM_REFERENCE = PARTY_ID in ABC

ORIG_SYSTEM = ABC

OWNER_TABLE_ID = Party ID in EBS

OWNER_TABLE_NAME = HZ_PARTIES

Important TCA lookups

Lookup for Customer Class code

select lookup_type,lookup_code,meaning from ar_lookups where lookup_type in (select class_category from HZ_CLASS_CATEGORIES);


Lookup for Customer Type

select lookup_type,lookup_code,meaning from ar_lookups where lookup_type='CUSTOMER_TYPE';


Lookup for Party Type
select lookup_type,lookup_code,meaning,description from ar_lookups where lookup_type='PARTY_TYPE';


Lookup for Sales Channel

select  lookup_type,lookup_code,meaning from oe_lookups where lookup_type='SALES_CHANNEL';


Lookup for SIC code

select  lookup_type,lookup_code,meaning from ar_lookups where lookup_type='SIC_CODE_TYPE';


Lookup for Status

SELECT lookup_type,lookup_code,
  meaning,
  start_date_active,
  end_date_active,
  description
FROM ar_lookups
WHERE lookup_type = 'REGISTRY_STATUS'
AND enabled_flag  = 'Y';


Lookup for Territory/Countries
SELECT TERRITORY_CODE,NLS_TERRITORY FROM FND_TERRITORIES;






Saturday, May 17, 2014

Accounting FlexFields of Bank Account in R12

alter session set current_schema=apps;

SELECT cba.Bank_Account_Name,
  cbbv.Bank_Name,
  cbbv.Bank_Branch_Name,
  cba.Bank_Account_Num,
  gcc_asset.segment1||'.'||gcc_asset.segment2||'.'||gcc_asset.segment3||'.'||gcc_asset.segment4||'.'||gcc_asset.segment5||'.'||gcc_asset.segment6||'.'||gcc_asset.segment7||'.'||gcc_asset.segment8||'.'||gcc_asset.segment9 CASH_ACCOUNT,
  gcc_gain.segment1||'.'||gcc_gain.segment2||'.'||gcc_gain.segment3||'.'||gcc_gain.segment4||'.'||gcc_gain.segment5||'.'||gcc_gain.segment6||'.'||gcc_gain.segment7||'.'||gcc_gain.segment8||'.'||gcc_gain.segment9 GAIN_ACCOUNT,
  gcc_loss.segment1||'.'||gcc_loss.segment2||'.'||gcc_loss.segment3||'.'||gcc_loss.segment4||'.'||gcc_loss.segment5||'.'||gcc_loss.segment6||'.'||gcc_loss.segment7||'.'||gcc_loss.segment8||'.'||gcc_loss.segment9 LOSS_ACCOUNT,
  gcc_cashclear.segment1||'.'||gcc_cashclear.segment2||'.'||gcc_cashclear.segment3||'.'||gcc_cashclear.segment4||'.'||gcc_cashclear.segment5||'.'||gcc_cashclear.segment6||'.'||gcc_cashclear.segment7||'.'||gcc_cashclear.segment8||'.'||gcc_cashclear.segment9 CASH_CLEARING_ACCOUNT,
  gcc_bank.segment1||'.'||gcc_bank.segment2||'.'||gcc_bank.segment3||'.'||gcc_bank.segment4||'.'||gcc_bank.segment5||'.'||gcc_bank.segment6||'.'||gcc_bank.segment7||'.'||gcc_bank.segment8||'.'||gcc_bank.segment9 BANK_CHARGES_ACCOUNT
FROM ce_bank_accounts cba,
ce_bank_branches_v cbbv,
gl_code_combinations gcc_gain,
gl_code_combinations gcc_loss,
gl_code_combinations gcc_cashclear,
gl_code_combinations gcc_bank,
gl_code_combinations gcc_asset
where cba.bank_branch_id=cbbv.branch_party_id
and cba.bank_id=cbbv.bank_party_id
and cba.Gain_code_combination_id=gcc_gain.code_combination_id(+)
and cba.loss_code_combination_id=gcc_loss.code_combination_id(+)
and cba.cash_clearing_ccid=gcc_cashclear.code_combination_id(+)
and cba.bank_charges_ccid=gcc_bank.code_combination_id(+)
and cba.asset_code_combination_id=gcc_asset.code_combination_id(+);

Request Set of a Concurrent Program

Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

Programs in a Request Set

Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

Query to find ORG_ID attached to a responsibility

SELECT * --fpov.profile_option_value
             FROM applsys.fnd_profile_option_values fpov
                , applsys.fnd_profile_options fpo
                , applsys.fnd_profile_options_tl fpot
                , applsys.fnd_responsibility_tl fr
            WHERE 1 = 1
              AND fpo.profile_option_name = fpot.profile_option_name
              AND fpo.profile_option_id = fpov.profile_option_id
              AND fr.responsibility_id(+) = fpov.level_value
             -- AND fr.responsibility_id = :$PROFILES$.RESP_ID
              AND fpot.profile_option_name = 'ORG_ID'
              and fpov.profile_option_value='11'
              and fr.responsibility_name like '%Payables%Super%User%';

TCA Parties Table Drill Down

SELECT * FROM hz_parties WHERE party_name = 'Saikam INTL' -- party_id = 39266,party_number(registry_id)= 1849,party_type = organization

SELECT * FROM hz_cust_accounts WHERE party_id = 563212 -- cust_account_id=10042,account_number=1180

SELECT * FROM hz_cust_acct_sites_all WHERE cust_account_id = 147077 -- cust_acct_site_id=1247,party_site_id=30119

SELECT * FROM hz_cust_site_uses_all WHERE cust_acct_site_id = 117353 --site_use_id=1266,location=1267,

SELECT * FROM hz_party_sites WHERE party_id = 563212  --party_site_id = 30119,location_id=1023,party_site_number=1598

SELECT * FROM hz_locations WHERE location_id =127754

SELECT * FROM hz_party_site_uses WHERE party_site_id = 30119 --party_site_use_id = 29029

SELECT * FROM hz_customer_profiles WHERE cust_account_id = 10042 --cust_account_profile_id=11043,party_id

SELECT * FROM hz_organization_profiles WHERE party_id=39266 --organization_profile_id=30101,

SELECT * FROM hz_person_profiles WHERE party_id=39266

Query to find strategies of a Customer Oracle R12 AR

Strategies of a Customer


SELECT DISTINCT(ist.cust_account_id),
  ist.status_code,
  ist.strategy_id,
  ist.strategy_template_id,
  ist.creation_date,
  hca.account_number,
  hca.account_name,
  istt.strategy_name
FROM iex_strategies ist,
  hz_cust_accounts_all hca,
  iex_strategy_templates_tl istt,
  (SELECT MAX(creation_date) max_date,
    cust_account_id
  FROM iex_strategies
  GROUP BY cust_account_id
  ) da
WHERE hca.cust_account_id    = ist.cust_account_id
AND ist.strategy_template_id = istt.strategy_temp_id
AND ist.object_type          = 'ACCOUNT'
AND ist.status_code         <> 'CANCELLED' --= 'OPEN'
AND TRUNC(ist.creation_date) =TRUNC(da.max_date)
AND ist.cust_account_id      =da.cust_account_id
AND hca.cust_account_id      =da.cust_account_id
AND hca.account_number      IN ('GL-21618645');

Query to find responsibilities attached to a user in Oracle R12

SELECT * FROM FND_USER WHERE user_name LIKE 'DAVIDEVANS';

SELECT *
  FROM FND_USER_RESP_GROUPS
 WHERE user_id=1180; --user_id is the link between the FND_USER and the FIND_USER_RESP_GROUPS

SELECT *
  FROM FND_RESPONSIBILITY_VL
 WHERE responsibility_id=50621; --responsibility_id is the link between the FND_RESPONSIBILITY_VL and FND_USER_RESP_GROUPS

-- The query which displays the user name and his responsibility_name will be

select fu.user_id,
       fu.user_name,
       furg.RESPONSIBILITY_APPLICATION_ID,
       frv.RESPONSIBILITY_NAME
  from FND_USER fu, FND_USER_RESP_GROUPS furg, FND_RESPONSIBILITY_VL frv
 where fu.user_id = furg.user_id
   and furg.responsibility_id = frv.responsibility_id
   and fu.user_name like 'DAVIDEVANS'