Current Employee Validation Query in R12

Current Employee validation Query in R12


select distinct papf.person_id
  --into l_person_id
   from per_all_people_f papf ,
  per_all_assignments_f paa   ,
  per_assignment_status_types pas
  where papf.person_id            = paa.person_id
and paa.primary_flag              = 'Y'
and paa.assignment_type           = 'E'
and pas.user_status              IN ('Livex','Active Assignment','Active Contingent Assignment')
and paa.assignment_status_type_id =pas.assignment_status_type_id
and papf.current_employee_flag    ='Y'
and (papf.employee_number         =rec_key_members.employee_number
or papf.npw_number                =rec_key_members.employee_number)
and paa.primary_flag              = 'Y'
and paa.assignment_type          IN('E','C')
  --and     trunc(sysdate) between  papf.effective_start_date and papf.effective_end_date
  --and     trunc(sysdate) between  paa.effective_start_date and paa.effective_end_date
and trunc(:customer_start_date) between trunc(paa.effective_start_date) and trunc(paa.effective_end_date)
and paa.position_id is not null
and exisTS
  (select 'Y'
     from per_person_types ppt
    where ppt.person_type_id      = papf.person_type_id
  and ppt.active_flag             ='Y'
  and ppt.system_person_type NOT IN ('EX_EMP','EX_CWK','OTHER')
  );

VENDOR SITE UPDATE SCRIPT OR SUPPLIER SITE UPDATE SCRIPT IN R12

vendor site update script or supplier site update script in R12

procedure update_api
is
l_msg_data                   varchar2 (20000);
l_return_status              varchar2 (100);
l_msg_count                  number;
l_party_id  number;
l_vendor_rec                 ap_vendor_pub_pkg.r_vendor_site_rec_type;
vendor_site_id number         :=null;
cursor c_ven_l
is
/*
select av.vendor_id,avs.vendor_site_id, avs.org_id,bb.prepay_code_combination_id,av.segment1,avs.vendor_site_code from xxmb_ven_site_stg_bk2 aa,xxmb_ven_site_stg bb, gl_code_combinations_kfv cc,ap_suppliers av, ap_supplier_sites_all avs,hr_operating_units hou
where aa.vendor_number=bb.vendor_number
and aa.employee_number=bb.employee_number
and aa.error_code='processed'
and aa.vendor_site_code=bb.vendor_site_code
and aa.operating_unit_name=bb.operating_unit_name
and bb.prepay_code_combination=cc.concatenated_segments
and bb.prepay_code_combination_id=cc.code_combination_id
and bb.vendor_number=av.segment1
and bb.operating_unit_name= hou.name
and hou.organization_id= avs.org_id
and av.vendor_id=avs.vendor_id;
*/
select av.vendor_id,avs.vendor_site_id, aa.group_id,av.segment1,avs.vendor_site_code, avs.org_id,xvs.awt_group_name,avs.awt_group_id
from ap_awt_groups aa, xxmb_ven_site_stg_bk1 xvs,ap_suppliers av,ap_supplier_sites_all avs,hr_operating_units hou
where aa.name=xvs.awt_group_name
and xvs.operating_unit_name= hou.name
and avs.org_id= hou.organization_id
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and av.vendor_id=avs.vendor_id
and avs.vendor_site_code=xvs.vendor_site_code
and error_code='processed'
--and a
and avs.awt_group_id is  null
--and avs.org_id=90
--and xvs.vendor_number=16810
order by 4;
/*
select av.vendor_id,avs.vendor_site_id, aa.group_id,av.segment1,avs.vendor_site_code, avs.org_id,xvs.awt_group_name,avs.awt_group_id
from ap_awt_groups aa,xxmb_ven_site_stg xvs,ap_suppliers av,ap_supplier_sites_all avs,hr_operating_units hou
where aa.name=xvs.awt_group_name
and xvs.operating_unit_name= hou.name
and avs.org_id= hou.organization_id
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and av.vendor_id=avs.vendor_id
and avs.vendor_site_code=xvs.vendor_site_code
and error_code='processed'
--and avs.org_id=90
--and xvs.vendor_number=16810
order by 4;
--and avs.vendor_site_id=11600;
*/
begin

--mo_global.set_policy_context('s',90);

for cv_ven_l in c_ven_l
loop
l_return_status := null;
l_msg_count := null;
l_msg_data := null;
--  l_vendor_id := null;

l_party_id := null;
l_vendor_rec := null;
--dbms_output.put_line ('updating vendor  ' || cv_ven_l.segment1);
--dbms_output.put_line('updating vendor  ' || cv_ven_l.segment1||'cv_ven_l.org_id'||cv_ven_l.org_id||'cv_ven_l.vendor_site_code'||cv_ven_l.vendor_site_code);
--l_vendor_rec.vendor_id=cv_ven_l.vendor_id;
--l_vendor_rec.vendor_site_id=cv_ven_l.vendor_site_id;
--l_vendor_rec.segment1 := 23055--cv_ven_l.segment1;
--l_vendor_rec.vendor_name := cv_ven_l.vendor_name;
--l_vendor_rec.start_date_active := sysdate;
l_vendor_rec.vendor_id:=cv_ven_l.vendor_id;--3174;
l_vendor_rec.vendor_site_id:=cv_ven_l.vendor_site_id;--2762;
--l_vendor_rec.prepay_code_combination_id:=cv_ven_l.prepay_code_combination_id;
l_vendor_rec.awt_group_id:=cv_ven_l.group_id;
l_vendor_rec.allow_awt_flag:='y';
--l_vendor_rec.org_id:=88;--cv_ven_l.org_id;
--l_vendor_rec.vendor_site_code:=cv_ven_l.vendor_site_code;
--l_vendor_rec.phone:='9822310522';
vendor_site_id:=cv_ven_l.vendor_site_id;

fnd_global.apps_initialize(user_id => fnd_global.user_id,resp_id => fnd_global.resp_id,resp_appl_id => fnd_global.resp_appl_id);
fnd_msg_pub.initialize;
mo_global.init ('sqlap');

ap_vendor_pub_pkg.update_vendor_site
(p_api_version      => 1.0,
p_init_msg_list     => 'f',
p_commit            => 'f',
p_validation_level  => fnd_api.g_valid_level_full,
x_return_status     => l_return_status,
x_msg_count         => l_msg_count,
x_msg_data          => l_msg_data,
p_vendor_site_rec   => l_vendor_rec,
p_vendor_site_id    => vendor_site_id
--p_calling_prog    => l_party_id

);
--dbms_output.put_line ('c_ven_l'||cv_ven_l.segment1);
--dbms_output.put_line ('c_ven_l'||cv_ven_l.vendor_site_code);
--dbms_output.put_line ('c_ven_l'||cv_ven_l.org_id);
dbms_output.put_line ('c_ven_l'||cv_ven_l.vendor_site_id);
dbms_output.put_line ('x_msg_count'||l_msg_count);
dbms_output.put_line ('x_msg_data'||l_msg_data);
dbms_output.put_line ('l_return_status'||l_return_status);
commit;
end loop;
end update_api;


procedure update_main (
errbuf         out      varchar2,
retcode        out      varchar2
)
is
begin
update_api;
end update_main;

VENDOR OR SUPPLIER UPDATE SCRIPT IN R12

ap_suppliers update script :

procedure update_api
is
l_msg_data                   varchar2 (20000);
l_return_status              varchar2 (100);
l_msg_count                  number;
l_party_id  number;
l_vendor_rec                 ap_vendor_pub_pkg.r_vendor_rec_type;
cursor c_ven_l
is
select vendor_id from ap_suppliers where last_updated_by='-1';
/*
select av.vendor_id, aa.group_id,av.segment1 from ap_awt_groups aa,xxmb_ven_stg_bk1 xvs,ap_suppliers av
where aa.name=xvs.awt_group_name
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and error_code='processed';
*/
/*select av.vendor_id, aa.group_id,av.segment1 from ap_awt_groups aa,xxmb_ven_stg xvs,ap_suppliers av
where aa.name=xvs.awt_group_name
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and error_code='processed';
*/

begin



for cv_ven_l in c_ven_l
loop
l_return_status := null;
l_msg_count := null;
l_msg_data := null;
--  l_vendor_id := null;

l_party_id := null;
l_vendor_rec := null;
--dbms_output.put_line('updating vendor  ' || cv_ven_l.segment1);
--dbms_output.put_line('updating vendor  ' || cv_ven_l.segment1);
--l_vendor_rec.segment1 := cv_ven_l.segment1;
l_vendor_rec.vendor_id := cv_ven_l.vendor_id;
--l_vendor_rec.awt_group_id :=cv_ven_l.group_id;
--l_vendor_rec.vendor_name := cv_ven_l.vendor_name;
--l_vendor_rec.start_date_active := sysdate;
fnd_global.apps_initialize(user_id => fnd_global.user_id,resp_id => fnd_global.resp_id,resp_appl_id => fnd_global.resp_appl_id);
fnd_msg_pub.initialize;
ap_vendor_pub_pkg.update_vendor (p_api_version      => 1.0,
p_init_msg_list    => 'f',
p_commit           => 'f',
x_return_status    => l_return_status,
p_validation_level     =>fnd_api.g_valid_level_full,
x_msg_count        => l_msg_count,
x_msg_data         => l_msg_data,
p_vendor_rec       => l_vendor_rec,
p_vendor_id        => cv_ven_l.vendor_id

);
dbms_output.put_line('cv_ven_l.vendor_id'||cv_ven_l.vendor_id);
dbms_output.put_line('l_msg_count'||l_msg_count);
dbms_output.put_line('l_msg_data'||l_msg_data);
dbms_output.put_line('l_return_status'||l_return_status);
commit;
end loop;
end update_api;


procedure update_main (
errbuf         out      varchar2,
retcode        out      varchar2
)
is
begin
update_api;
end update_main;

pa pending records in r12

--begin
--mo_global.set_policy_context('S',87);
--end;

select * from (
select
(select name from apps.hr_operating_units  where organization_id=a.org_id) Organization,'Invoice' Type,
customer_number,customer_name,segment1 project_number,draft_Invoice_num
,decode(a.transfer_status_code,'P','Pending','T','Transfer to Receivable, Run Tie Back','R','Rejected','X','Rejected')transfer_status_code,a.transfer_rejection_reason
,gL_date
from apps.pa_draft_invoices_all  a,apps.pa_projects_all b,apps.pa_project_customers_v c
where a.project_id=b.project_id
and b.project_id= c.project_id
and a.transfer_status_code not in ('A','T')
and segment1 not like 'BRI%'
union all
select
(select name from apps.hr_operating_units  where organization_id=a.org_id) Organization,'Revenue' Type,
customer_number,customer_name,segment1 project_number,draft_revenue_num
,decode(a.transfer_status_code,'P','Pending','R','Rejected','X','Rejected')transfer_status_code,a.transfer_rejection_reason,gL_date
from apps.pa_draft_revenues_all  a,apps.pa_projects_all b,apps.pa_project_customers_v c
where a.project_id=b.project_id
and b.project_id= c.project_id
and a.transfer_status_code!='A'
and segment1 not like 'BRI%' ) order by Organization,Type

how to initialize sequrity profile from back end in R12

query to fine security profile information

select psp.security_profile_name, psp.security_profile_id, hou.name, hou.organization_id            
from per_security_profiles psp, per_security_organizations pso, hr_operating_units hou        
where pso.security_profile_id = psp.security_profile_id  and pso.organization_id = hou.organization_id
order by psp.security_profile_name,hou.name;
how to initialize sequrity profile from back end

begin
mo_global.set_org_access(null,3068 /*security_profile_id */,'ar' /*module shortname*/);
end;

r12 cash management joins with ap ar gl

select  to_number(null)                                         cash_receipt_id,
           aip.accounting_date                                   gl_date,
           to_char(apc.doc_sequence_value)             voucher_no,
           to_char(apc.check_number)                       check_number,
           apc.vendor_name                                       vendor_name,
           api.accts_pay_code_combination_id         account_code,
           0                                                                 receipts_ent,
           0                                                                 receipts_acc,
           sum(round(aip.amount,5))                         payments_ent,
           sum(round(aip.amount*nvl(aip.exchange_rate,1),5))     payments_acc,
           aba.bank_account_name                           bank_account_name,
           aba.bank_account_num                             bank_account_num,
           aip.exchange_rate                                      exchange_rate,
           api.payment_currency_code                     curr,
          'ap'                                                              flg,
           api.vendor_id                                            partyid,
           to_char(null)                                             status,
           aba.bank_account_id,
            'payables'                                                  transaction_source
from      ap_invoice_payments_all aip,
                ap_invoices_all api,
                ap_checks_all apc,
                ce_bank_accounts aba ,
                ce_bank_acct_uses_all cbu
where  api.invoice_id = aip.invoice_id
and        aip.check_id = apc.check_id
and        apc.ce_bank_acct_use_id = cbu.bank_acct_use_id
and        aba.bank_account_id=cbu.bank_account_id
and
(
 (
                 apc.status_lookup_code in ('negotiable', 'cleared','voided',
'reconciled unaccounted', 'reconciled', 'cleared but unaccounted' )
  and        aip.accounting_date between nvl(:p_start_date,aip.accounting_date) and nvl(:p_end_date,aip.accounting_date)
  and        apc.future_pay_due_date is null
 )
 or
 (
                apc.status_lookup_code in ('negotiable')
  and       apc.future_pay_due_date between nvl(:p_start_date,apc.future_pay_due_date) and nvl(:p_end_date,apc.future_pay_due_date)
 )
)
and        aba.bank_account_id = :p_bank_account_id
and        aip.invoice_payment_type is null
and              (api.org_id is null or api.org_id = :p_org_id)
group by   aip.accounting_date,
           apc.doc_sequence_value,
           apc.check_number,
           apc.vendor_name,
           api.accts_pay_code_combination_id,
           aba.bank_account_name,
           aba.bank_account_num,
           aip.exchange_rate,
           api.payment_currency_code,
           api.vendor_id,aba.bank_account_id
union all
select  to_number(null)                                        cash_receipt_id,
           aip.accounting_date                                  gl_date,
           to_char(apc.doc_sequence_value)            voucher_no,
           to_char(apc.check_number)                      check_number,
           apc.vendor_name                                      vendor_name,
           api.accts_pay_code_combination_id        account_code,
           0                                                                 receipts_ent,
           0                                                                 receipts_acc,
           sum(round(aip.amount,5))                         payments_ent,
           sum(round(aip.amount*nvl(aip.exchange_rate,1),5))     payments_acc,
           aba.bank_account_name                            bank_account_name,
           aba.bank_account_num                              bank_account_num,
           aip.exchange_rate                                       exchange_rate,
           api.payment_currency_code                       curr,
          'ap'                                                                flg,
           api.vendor_id                                              partyid,
           to_char(null)                                               status,
           aba.bank_account_id,
            'payables'                                                    transaction_source
from  
               ap_invoice_distributions_all apid,
                ap_invoice_payments_all aip,
                ap_checks_all apc,
ap_invoice_lines_all apla,
                ap_invoices_all api,
                po_vendors pov,
                ce_bank_accounts aba
where      api.invoice_id=apid.invoice_id
and        apla.invoice_id = apid.invoice_id
and        apla.line_number = apid.invoice_line_number
and        api.invoice_id=aip.invoice_id
and        apc.check_id=aip.check_id
and        api.vendor_id = pov.vendor_id
and        aba.bank_account_id = :p_bank_account_id
and        aba.asset_code_combination_id=apid.dist_code_combination_id
and        apid.match_status_flag = 'a'
and        nvl(api.payment_status_flag,'n')  <>'n'
and        apid.accounting_date  between nvl(:p_start_date,apid.accounting_date) and nvl(:p_end_date,apid.accounting_date)
and        (api.org_id is null or api.org_id = :p_org_id)
group by   aip.accounting_date,
           apc.doc_sequence_value,
           apc.check_number,
           apc.vendor_name,
           api.accts_pay_code_combination_id,
           aba.bank_account_name,
           aba.bank_account_num,
           aip.exchange_rate,
           api.payment_currency_code,
           api.vendor_id,aba.bank_account_id
union
select  acrh.cash_receipt_id                               cash_receipt_id,
           acrh.gl_date                                             gl_date,
           acr.receipt_number                                  voucher_no,
           null                                                           check_number,
           nvl(rc.customer_name, acr.comments)    vendor_name,
           acrh.account_code_combination_id        account_code,
           round  (acrh.amount, 5)                           receipts_ent,
           round  (acrh.amount*nvl(acrh.exchange_rate,1), 5)     receipts_acc,
           0                                                               payments_ent,
           0                                                               payments_acc,
           aba.bank_account_name                          bank_account_name,
           aba.bank_account_num                            bank_account_num,
           0                                                                exchange_rate,
           acr.currency_code                                     curr,
          'ar'                                                               flg,
           rc.customer_id                                           partyid,
           acrh.status                                                  status,
           aba.bank_account_id,
            'receivables'  transaction_source
from       ar_cash_receipt_history_all               acrh,
           ar_cash_receipts_all                      acr,
           ar_cash_receipts_v                             rc,
           ce_bank_accounts                      aba
          ,ce_bank_acct_uses_all                 cbaua
where      acrh.cash_receipt_id(+)        =   acr.cash_receipt_id
--and        nvl(reversal_created_from,'zzzz') <> 'rate adjustment trigger'
and       rc.cash_receipt_id       =   acr.cash_receipt_id
--and        rc.remittance_bank_branch_id=aba.bank_branch_id
and        rc.remit_bank_acct_use_id=cbaua.bank_acct_use_id
and        aba.bank_account_id=cbaua.bank_account_id
and        acrh.status in ('cleared', 'remitted', 'confirmed')
and        aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and         acr.remit_bank_acct_use_id =(select bank_acct_use_id from ce_bank_acct_uses_all
                                         where bank_account_id=:p_bank_account_id and org_id=:p_org_id)
and        trunc(acrh.gl_date ) between (:p_start_date) and (:p_end_date)
and       (acr.org_id = :p_org_id or acr.org_id is null)
union all
select acrh.cash_receipt_id                            cash_receipt_id,
           acrh.gl_date                                          gl_date,
           acr.receipt_number                                    voucher_no,
           null                                                  check_number,
           nvl(rc.customer_name, acr.comments)                   vendor_name,
           acrh.account_code_combination_id                      account_code,
           round(decode(acrh.status,
                      'reversed',
                      (acrh.amount)*-1,acrh.amount ),5)          receipts_ent,
           round(decode(acrh.status,
                      'reversed',
                      (acrh.amount*
                        nvl(acrh.exchange_rate,1))*-1,
                        acrh.amount*
                          nvl(acrh.exchange_rate,1)),5)          receipts_acc,
           0                                                     payments_ent,
           0                                                     payments_acc,
           aba.bank_account_name                                 bank_account_name,
           aba.bank_account_num                                  bank_account_num,
           0                                                     exchange_rate,
           acr.currency_code                                     curr,
          'arrev'                                                flg,
           rc.customer_id                                        partyid,
           acrh.status                                           status,
           aba.bank_account_id,
            'receivables'  transaction_source
from       ar_cash_receipt_history_all                 acrh,
           ar_cash_receipts_all                        acr,
           ar_cash_receipts_v                            rc,
           ce_bank_accounts                        aba,
           ce_bank_acct_uses_all   ca
where      acrh.cash_receipt_id               =   acr.cash_receipt_id
--and        nvl(reversal_created_from,'zzzz') <> 'rate adjustment trigger'
and        rc.remit_bank_acct_use_id=ca.bank_acct_use_id
and        aba.bank_account_id=ca.bank_account_id
and        acr.cash_receipt_id              =   rc.cash_receipt_id
and        acrh.status in ('reversed')
and        aba.bank_account_id                =   nvl(:p_bank_account_id, aba.bank_account_id)
and        trunc(acrh.gl_date ) between (:p_start_date) and (:p_end_date)
and       (acr.org_id = :p_org_id or acr.org_id is null)
union
select  gjh.je_header_id                                      cash_receipt_id,
           gjh.default_effective_date                            gl_date,
           to_char(gjh.doc_sequence_value)                       voucher_no,
           to_char(gjl.je_line_num)                              check_number,
           null                                                  vendor_name,
           gjl.code_combination_id                               account_code,
           nvl(gjl.entered_dr,0)                                 receipts_ent,
           nvl(gjl.accounted_dr,0)                               receipts_acc,
           nvl(gjl.entered_cr,0)                                 payments_ent,
           nvl(gjl.accounted_cr,0)                               payments_acc,
           aba.bank_account_name                                 bank_account_name,
           aba.bank_account_num                                  bank_account_num,
           nvl(gjh.currency_conversion_rate,1)                   exchange_rate,
           gjh.currency_code                                     curr,
          'bank_transfer'                                        flg,
           null                                                  partyid,
           null                                                  status,
           aba.bank_account_id,
           gjc.user_je_category_name  transaction_source
from       gl_je_headers                                 gjh,
           gl_je_lines                                   gjl,
           ce_bank_accounts                          aba,
           gl_je_sources                                 gjs,
           gl_je_categories                              gjc,
           ce_bank_acct_uses_all ca
where      1=1
and        aba.bank_account_id                      = nvl(:p_bank_account_id, aba.bank_account_id)
and        aba.asset_code_combination_id            = gjl.code_combination_id
and        ca.bank_account_id                       =aba.bank_account_id
and        gjl.je_header_id                         = gjh.je_header_id
--and        gjs.user_je_source_name                  in ('cash management','manual','receivables')
and        gjs.je_source_name                       = gjh.je_source
and        gjc.user_je_category_name                in ('bank transfers(1)','bank transfers','bank transfer','xxxx accruals','cash management')
and        gjc.je_category_name                     = gjh.je_category
and        gjh.default_effective_date  between (:p_start_date) and (nvl(:p_end_date,sysdate))
and        ca.org_id=:p_org_id
order by 1

How to know user password from Back End in Oracle apps R12

1.

CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
2.

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/
3. run below select query you will get user_name and password.

select usr.user_name,
       get_pwd.decrypt
          ((select (select get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      from dual) as apps_password
              from fnd_user usertable
             where usertable.user_name =
                      (select substr
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     instr
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         from dual)),
           usr.encrypted_user_password
          ) password
  from fnd_user usr
 where upper(usr.user_name) = upper('&user_name');

How to add Responsibility From Back End

how to add responsibility from back end

begin
--fnd_user_pkg.addresp(
--        ‘&user_name’, /*application user name */
--        ‘&responsablity_application_short_name’, /*get from query below */
--        ‘&responsibility_key’,/*get from query below */
--        ‘&security_group’, /* most of cases it is ‘standard’ so you can hard code it */
--        ‘&description’, /* any comments you want */
--        ‘&start_date’, /* sysdate from today */
--        ‘&end_date’ ); /* sysdate + 365 rights for next one year*/
    
fnd_user_pkg.addresp ('user_name','sysadmin','system_administrator','standard','add responsibility to user using pl/sql',sysdate-1,sysdate + 100);    
commit;
dbms_output.put_line('responsibility added successfully to the user ');
exception
        when others then
                dbms_output.put_line(' responsibility adding failed due to' || "sqlcode" || substr(sqlerrm, 1, 100));
                rollback;
end;

create table in report

Create Table in Report.

Srw.do_sql (sqlstatement char);


    SRW.USER_EXIT('FND SRWINIT');

  srw.do_sql('create table xxg4_y(ename varchar2(10))');

  return (TRUE);

Setting Format Attributes:

Function age_mask return Boolean is

Begin

If :age < 25 then

Srw.set_text_color(‘Red’);

Srw.set_font_face(‘Impact’) ;

Srw.set_font_weight(‘’srw.bold_weight) ;

Srw.set_font_style(‘srw.italic_style’);

Srw.set_fill_pattern(‘solid diamond’);

End if;

End;

Restricting data:

the srw.set_maxrow procedure sets the max number of records that can be retrieved for a 

specific query.

In the BEFORE REPORT trigger, you could use the srw.set_maxrow procedure to ensure that only 

the required num of records are fetched.

begin

                                    if :p_dname = 'RESEARCH' then

                                                srw.set_maxrow ('Q_2', 2);

                                                end if;

                                    end;

Running nested reports:

For running one report from another report the user has to use SRW.RUN_REPORT

Go to layout editor, create a button and label it as ‘Click this button’

Open the property palette of the button and set the Button Behaviour: Type=pl/sql: Pl/sql 

Trigger= provide the code

procedure U_ButtonButtonAction is

begin

  srw.run_report('module=C:\Documents and Settings\Administrator\Desktop\POXDETIT.rdf 

destype=Screen');

end;

fnd load

FND LOAD...


Create  LDT File from  where we already created Concarent Program

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXOPENPOHEADERCONVLOADER.ldt PROGRAM APPLICATION_SHORT_NAME="CUST_TOP" CONCURRENT_PROGRAM_NAME="XXOPENPOHEADERCONVLOADER"


Adding concarent program throgh shall script based on downloded LTD File

chmod 755 XXAPSUPPLOADER.ldt >> $LOGFILE 2>> $LOGERR

dos2unix XXAPSUPPLOADER.ldt >> $LOGFILE 2>> $LOGERR

FNDLOAD apps/$P_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/ldt/XXAPSUPPSITELOADER.ldt - CUSTOM_MODE=FORCE >> $LOGFILE 2>> $LOGERR

Adding concarent program to request Group

BEGIN

IF NOT fnd_program.program_in_group(program_short_name => 'XXAPSUPPSITELOADER',
                          program_application  => 'CUST_TOP',
                          request_group => 'JAI_FixedAssets_RG',
                          group_application => 'JAIFARG') THEN

      fnd_program.add_to_group(program_short_name => 'XXAPSUPPSITELOADER',
                    program_application => 'CUST_TOP',
                    request_group => 'JAI_FixedAssets_RG',
                    group_application => 'JAIFARG');
  dbms_output.put_line('program attached to the  request group');
else
  dbms_output.put_line('program is in request group');
end if;                        

commit;
exception
   when others then
      raise;
end;

Query to list concurrent program details with its parameter, values set and default value/type AND Attached Responsibilities

1. query to list all the responsibilities attached to a user

select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')


2.to find from which responsibility a concurrent program can be run

select distinct
  a.user_concurrent_program_name,
  a.description,
  request_group_name,
  e.responsibility_name
from
  fnd_concurrent_programs_tl a,
  fnd_request_groups b,
  fnd_request_group_units c,
  fnd_responsibility d,
  fnd_responsibility_tl e
where
  a.concurrent_program_id = c.request_unit_id   
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id

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

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

5. query to list concurrent program details with its parameter, values set and default value/type:

  select fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.application_short_name
      , fav.application_name
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.default_value  
 from   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 where  fcp.concurrent_program_id = fcpl.concurrent_program_id
 and    fcpl.user_concurrent_program_name = :conc_prg_name
 and    fcpl.language = 'us'
 and    fav.application_id=fcp.application_id
 and    fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name
 and    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 and    flv.lookup_type(+) = 'flex_default_type'
 and    flv.lookup_code(+) = fdfcuv.default_type
 and    flv.language(+) = userenv ('lang');


 6. query to find out concurrent program details and its parameters

select fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.application_short_name
     , fav.application_name
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
from   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
where  fcp.concurrent_program_id = fcpl.concurrent_program_id
and    fcpl.user_concurrent_program_name = :conc_prg_name
and    fav.application_id=fcp.application_id
and    fcpl.language = 'US'
and    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


7 :for checking the locks in concurrent jobs

select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;

8 :for checking the concurrent programs running currently with details of processed time-- and start date


 select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where  a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
  
9 :for checking last run of a concurrent program along with processed time

-- useful to find the details of concurrent programs which run daily and comparison purpose

select distinct c.user_concurrent_program_name,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
            a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
  to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
            (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.fnd_concurrent_programs_tl c,
            apps.fnd_user d
where       a.concurrent_program_id= b.concurrent_program_id and
            b.concurrent_program_id=c.concurrent_program_id and
            a.requested_by =d.user_id and
--          trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' --  and argument_text like  '%, , , , ,%';
--          and status_code!='c'

10 :for checking the last run of concurrent program.

- use below query to check all the concurrent request running which may refer given package

-- this is very useful check before compiling any package on given instance.

-- the query can be modified as per requirement.

-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

11 :the following query will display the time taken to execute the concurrent programs

--for a particular user with the latest concurrent programs sorted in least time taken

-- to complete the request.


 select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      and pt.language = userenv('lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;


12 : by using the below query we can get sid,serial#,spid of the concurrent request..

    select a.request_id, d.sid, d.serial# , c.spid
    from apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    where a.controlling_manager = b.concurrent_process_id
    and c.pid = b.oracle_process_id
    and b.session_id=d.audsid
    and a.request_id = &request_id
    and a.phase_code = 'r';


13 : by using below concurrent manager and program rules...

--gives detail of the concurrent_queue_name and user_concurrent_program_name

select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
  

14 : gives details of running concurrent jobs


select distinct c.user_concurrent_program_name,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.fnd_concurrent_programs_tl c,
    apps.fnd_user d
where   a.concurrent_program_id=b.concurrent_program_id and
    b.concurrent_program_id=c.concurrent_program_id and
    a.requested_by=d.user_id and
    status_code='r' order by process_time desc;
  

15 : gives detail of concurrent job completed and pending


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

16 :gives detail of running and completed concurrent jobs with start date and end date

-- latest one at the top

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      and pt.language = userenv('lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;

17 : wait events details related with concurrent programs


select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";

18 : to find the pid of the concurrent job and kill it.


select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;

19 :to find the database sid of the concurrent job

-- we need our concurrent request id as an input.

-- c.spid= is the operating system process id

-- d.sid= is the oracle process id


sql> column process heading "fndlibr pid"

select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';

How do I calculate the table space size in oracle

select   /* + rule */
        df.tablespace_name "tablespace",
         df.bytes / (1024 * 1024 * 1024) "size(gb)",
         sum (fs.bytes) / (1024 * 1024 * 1024) "free(gb)",
         nvl (
            round (
               sum (fs.bytes) * 100 / df.bytes
            ),
            1
         ) "%free",
         round (
            (  df.bytes
             - sum (fs.bytes)
            ) * 100 / df.bytes
         ) "%used"
    from dba_free_space fs,
         (select   tablespace_name, sum (bytes) bytes
              from dba_data_files
             where tablespace_name like 'ontd%'
          group by tablespace_name) df
   where fs.tablespace_name(+) = df.tablespace_name
group by df.tablespace_name, df.bytes
order by 3 desc

to find the which sql is executing by the concurrent program in a schema
run the below query first to get the sid and the program details.

select   q.concurrent_queue_name qname, f.user_name, a.request_id "req id",
         decode (a.parent_request_id, -1, null, a.parent_request_id) "parent",
         a.concurrent_program_id "prg id", a.phase_code, a.status_code,
         vs.inst_id, vs.sid, vs.serial# "serial#", vp.spid,
         b.os_process_id apprsid,
           (  nvl (a.actual_completion_date, sysdate)
            - a.actual_start_date
           )
         * 1440
               "time",
            c.concurrent_program_name
         || ' - '
         || c2.user_concurrent_program_name "program"
    from applsys.fnd_concurrent_requests a,
         applsys.fnd_concurrent_processes b,
         applsys.fnd_concurrent_queues q,
         applsys.fnd_concurrent_programs_tl c2,
         applsys.fnd_concurrent_programs c,
         applsys.fnd_user f,
         gv$session vs,
         gv$process vp
   where a.controlling_manager = b.concurrent_process_id
     and a.concurrent_program_id = c.concurrent_program_id
     and a.program_application_id = c.application_id
     and c2.concurrent_program_id = c.concurrent_program_id
     and f.user_name = :user_name
     and c2.application_id = c.application_id
     and a.phase_code in ('i', 'p', 'r', 't')
     and a.status_code in ('r')
     and a.requested_by = f.user_id
     and b.queue_application_id = q.application_id
     and b.concurrent_queue_id = q.concurrent_queue_id
     and vp.spid = a.oracle_process_id
     and vs.paddr(+) = vp.addr
     and vs.inst_id(+) = vp.inst_id
order by 12;

query to find which sql is executing in a session

select a.sid, a.serial#, b.sql_text
   from v$session a, v$sqlarea b
    where a.sql_address=b.address
     and a.sid = :sid