Thursday 10 October 2024

Query to find customer profile information in oracle apps

 SELECT
    hp.party_id,
    hp.party_number,
    hp.party_name,
    hp.party_type,
    hca.account_number,
    hcpc.name           profile_class,
    hcpc.description    profile_class_description,
    ac.name             collector_name,
    ac.description      collector_description,
    hcp.send_statements,
    ascs.name           statement_cycle_name,
    hcp.statement_cycle_id,
    hcp.dunning_letters,
    hcp.dunning_letter_set_id,
    adls.name           dunning_letter_set,
    hcp.object_version_number,
    hca.cust_account_id,
    hcp.cust_account_profile_id
FROM
    hz_parties               hp,
    hz_cust_accounts         hca,
    ar_statement_cycles      ascs,
    ar_dunning_letter_sets   adls,
    ar_collectors            ac,
    hz_cust_profile_classes  hcpc,
    hz_customer_profiles     hcp
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.statement_cycle_id = ascs.statement_cycle_id (+)
    AND hcp.dunning_letter_set_id = adls.dunning_letter_set_id (+)
    AND hcp.collector_id = ac.collector_id (+)
    AND hcp.profile_class_id = hcpc.profile_class_id (+)
    AND hcp.site_use_id IS NULL
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
;

Friday 6 September 2024

Script to assign the concurrent program to a request group

DECLARE
   l_ret_code                    NUMBER;
   l_ret_message                 VARCHAR2 (1000);  
                                           
BEGIN
  
xxconv_util.add_program_to_group ('Custom Application'              --program application name
                                      , 'DELIVERABLE_PRG'                                --program short name 
                                      , 'PA Manager'       --request group name 
                                      , 'Projects'      --request group application
                                      , l_ret_code
                                      , l_ret_message
                                       );
       
COMMIT;
EXCEPTION  WHEN OTHERS THEN 
DBMS_OUTPUT.PUT_LINE('Error Attaching Program To Request Group  ' || SQLERRM);
ROLLBACK;
END;
/

Tuesday 27 August 2024

How to update and return the value in plsql (RETURNING Clause to Avoid Unnecessary SQL Statements)

PROCEDURE validate_prc (
    p_guid            VARCHAR2,
    p_invoice_number  OUT  VARCHAR2,
    p_cust_num        OUT  VARCHAR2,
    p_site_use_id     OUT  NUMBER,
    p_click_count     OUT  NUMBER
) IS
    PRAGMA autonomous_transaction;
BEGIN
UPDATE adv_coll_tbl
        SET click_count = click_count+1
WHERE  guid = p_guid
RETURNING
    invoice_number,
            customer_number,
            bill_to_site_use_id,
            click_count
INTO
    p_invoice_number,
            p_cust_num,
            p_site_use_id,
            p_click_count
;
IF SQL%FOUND THEN
dbms_output.put_line(' Success ');
ELSE
dbms_output.put_line(' Error ');
END IF;
END;

Monday 12 August 2024

Query to find location of rtf template in bursting control file

 select 'xdo://'|| 
       xtb.application_short_name||'.'||
         xtb.template_code ||'.'||
         xtb.default_language ||'.'||
         xtb.default_territory
  from   apps.xdo_templates_b xtb
 where   xtb.template_code ='ARXSGP';

Sunday 10 March 2024

Query to find the last access date for a Responsibility in R12

SELECT
    frt.responsibility_name,
    fu.user_name,
    MAX(flr.start_time) "Last Connect"
FROM
    applsys.fnd_login_responsibilities    flr,
    applsys.fnd_user                      fu,
    applsys.fnd_logins                    fl,
    applsys.fnd_responsibility_tl         frt
WHERE
        fl.login_id = flr.login_id
    AND fl.user_id = fu.user_id
    AND fu.user_name = 'SANDEEP_SHARMA' -- Username
    AND frt.responsibility_id = flr.responsibility_id
       --and frt.responsibility_name = '&ResponsibilityName'
GROUP BY
    frt.responsibility_name,
    fu.user_name;

Tuesday 12 September 2023

Query to find oracle patch is applied or not

 SELECT DISTINCT
    e.patch_name,
    a.bug_number,
    ad_patch.is_patch_applied('R12', - 1, a.bug_number)            patch_applied,
    to_char(c.end_date, 'DD-MON-RRRR HH24:MI:SS')           end_date,
    e.patch_type,
    d.patch_abstract
FROM
    ad_bugs             a,
    ad_patch_run_bugs   b,
    ad_patch_runs       c,
    ad_patch_drivers    d,
    ad_applied_patches  e
WHERE
        a.bug_id = b.bug_id
    AND b.patch_run_id = c.patch_run_id
    AND c.patch_driver_id = d.patch_driver_id
    AND d.applied_patch_id = e.applied_patch_id
   AND e.patch_name in ('33515489')--patch_number
ORDER BY
    1,
    3 ASC;

Sunday 18 June 2023

Query to get Account description of GL Code Combination in Oracle APPS

SELECT
    gcc.concatenated_segments                                                                              account,
    gl_flexfields_pkg.get_concat_description(gcc.chart_of_accounts_id, gcc.code_combination_id) account_description
FROM
    gl_code_combinations_kfv gcc
WHERE
    gcc.code_combination_id = 111111