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;

Read More

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;

Read More

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';

Read More

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;

Read More

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;
/

Read More

Query to Find Database Version in Oracle

select * from v$version;

Output:

Read More

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;

Read More

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;

Read More

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_CODE	MEANING
R	Normal
I	Normal
Z	Waiting
D	Cancelled
U	Disabled
E	Error
M	No Manager
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
G	Warning

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_CODE	MEANING	DESCRIPTION
C	Completed	Request has finished
I	Inactive	Request is unable to run
P	Pending	Request is waiting to be run
R	Running	Request is running

Read More

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;

Read More

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;

Read More

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

Read More

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;

Read More

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(+);

Read More

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')));

Read More

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')));

Read More

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%';

Read More

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

Read More

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');

Read More

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'

Read More