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;

/

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;

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

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;

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;

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

Query to find AP Invoice status in Oracle apps

 SELECT
    aia.org_id,
    aia.invoice_num,
    aia.invoice_date,
    decode(ap_invoices_utility_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount,
                                                       aia.payment_status_flag,
                                                       aia.invoice_type_lookup_code),
           'FULL', 'Fully Applied',
           'NEVER APPROVED', 'Never Validated',
           'NEEDS REAPPROVAL','Needs Revalidation',
           'CANCELLED', 'Cancelled',
           'UNPAID', 'Unpaid',
           'AVAILABLE', 'Available',
           'UNAPPROVED', 'Unvalidated',
           'APPROVED', 'Validated',
           'PERMANENT', 'Permanent Prepayment',
           NULL) invoice_status
FROM
    ap_invoices_all aia
 WHERE aia.invoice_num  = '111111111';