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;