Best Link I Found on Web for R12 release

best Link to Study the R12 instance related Materials

http://oracleebspro.blogspot.com/search/label/UPK

and
http://dillipkumarjena.blogspot.com/2012/11/oracle-sub-ledger-accounting-is-rules.html

XDOFX

<?for-each-group:LIST_G_GL_ACCOUNT; LIST_G_GL_ACCOUNT?>
 <?for-each:G_GL_ACCOUNT?>
<?xdoxslt:set_variable($_XDOCTX,’x’,COST_CENTER)?>
<?xdoxslt:set_variable($_XDOCTX,’y’,GL_ACCOUNT)?>
               Book:  Your's BOOK
               Company:  1234
               Asset Type:  Capitalized
        Asset Account:<? GL_ACCOUNT?>
        Cost Center:  <?COST_CENTER?>
<?for-each-group: G_RES_ACCOUNT; G_RES_ACCOUNT?>
    Reserve Account:<?RES_ACCOUNT?>
ASSENT NO.                                                
Asset Description.
Date IN PLACE
DEPRE METHOD
LIFE MO.
 COST
Year to Date
Initial depreciation
TRANSACTION NUM
<?for-each:G_ASSET?>
<?xdofx:substr(ASSET_NUMBER,1,6)?>
<?xdofx:substr(ASSET_NUMBER,8)?>
<?START_DATE?>            
<?METHOD?>
<?LIFE?>
<?COST?>
<?YTD_DEPRN?>
<?DEPRN_RESERVE?>
<?THID?>
<?end for-each?>
Reserve  Account <?RES_ACCOUNT?>  Total  <?D_RA_COST?> <?D_YTD_DEPRN?> <?D_DEPRN_RESERVE?>
CostCenter <? xdoxslt:get_variable($_XDOCTX,’x’)?>Total<?D_RA_COST?>  <?D_YTD_DEPRN?>   <?D_DEPRN_RESERVE?>
Asset Account <? xdoxslt:get_variable($_XDOCTX,’y’)?>Total<?D_RA_COST?><?D_YTD_DEPRN?> <?D_DEPRN_RESERVE?>
<?end for-each-group?>
  <?split-by-page-break:?>
<?end for-each?>
 <?end for-each-group?>

Example of Dynamic SQL Statment

xname varchar2(10);
begin
execute immediate 'select ename   FROM   employeetest  WHERE  eno=10 and edepartment=''ABC'' '   into xname;
dbms_output.put_line(xname);
end;

Example How to query Tax and Invoices in Oracle Apps

SELECT
v.vendor_id,
v.invoice_num,
v.invoice_id,
v.invoice_amount,
to_char(c.check_date,'YYYYMMDD')                DepositDate,
b.bank_name,
b.bank_branch_name,
p.amount                                        paymentAmount,
v.invoice_amount                                TAX_Invoice_Amount,
'Your Name' "Name",
'Your Address' "Address",
'Your Number' "UN",
'Your Date' "Date",
'Your Office' "OfficerName",
'Your Designation' "Designation"
FROM
Ap_Checks_All            c,
AP_Invoice_Payments_all  p,
Ap_Invoices_All          v,
Ap_Bank_Branches         b,
Ap_Bank_Accounts_All     ba
WHERE
v.invoice_date between '1-jul-2009' and '30-Jun-2010'
and v.vendor_id=1
and v.invoice_type_lookup_code='AWT'
and b.bank_branch_id=ba.bank_branch_id
and c.bank_account_id=ba.bank_account_id
and c.check_id=p.check_id
and v.invoice_id=p.invoice_id
and substr(v.invoice_num,19,6) in(SELECT v.invoice_id FROM
Ap_Invoices_All v
WHERE
v.invoice_date between '1-jul-2009' and '30-Jun-2010'
and v.vendor_id='0000'
and v.invoice_type_lookup_code='STANDARD'
)

Example Usage for Formula&Placeholder Column in a report

Procedure That will return 4 Variables Example

Procedure That will take vendor id from calling environment and return 4 other variables like V_invamt,V_taxamt,V_vname,V_vntn to the calling environment.

create or replace procedure PrintInvoiceInfo(V_venid in number, V_invamt out number,V_taxamt out number,V_vname out varchar2,V_vntn out varchar2)

is

begin

SELECT sum(v.invoice_amount),sum(idv.amount*awt.tax_rate/100),pov.vendor_name,pov.num_1099 into V_invamt,V_taxamt,V_vname,V_vntn

       FROM

       Ap_Invoices_All v,

       AP_INVOICE_DISTRIBUTIONS_V idv,

       AP_AWT_TAX_RATES_ALL        awt,

       Po_Vendors                  pov

       WHERE

       awt.tax_name(+)=idv.awt_group_name

       and v.invoice_id=idv.invoice_id

       and pov.vendor_id=v.vendor_id

       and v.invoice_date between '1-jul-2009' and '30-Jun-2010'

       and v.vendor_id=V_venid

       and v.invoice_type_lookup_code='STANDARD'

       group by v.vendor_id,pov.vendor_name,pov.num_1099;

end PrintInvoiceInfo;

Test window-  The Calling Environment

declare

invamt number;

taxamt number;

vname  varchar2(30);

vntn   varchar2(10);

begin

dbms_application_info.set_client_info(000);

PrintInvoiceInfo(0000,invamt,taxamt,vname,vntn);

dbms_output.put_line(invamt||taxamt||vname||vntn);

end;

How to define Cursor with report

DECLARE

    V_venid  ap_invoices_all.vendor_id%type;

    V_invid   ap_invoices_all.invoice_id%type;

    V_invamt  ap_invoices_all.invoice_amount%type;

    V_taxamt  number;

   

   

    CURSOR StdInv_cursor IS  

    SELECT v.vendor_id,sum(v.invoice_amount),sum(idv.amount*awt.tax_rate/100)

       FROM

       Ap_Invoices_All v,

       AP_INVOICE_DISTRIBUTIONS_V idv,

       AP_AWT_TAX_RATES_ALL        awt

       WHERE

       awt.tax_name(+)=idv.awt_group_name

       and v.invoice_id=idv.invoice_id

       and v.invoice_date between '1-jul-2009' and '30-Jun-2010'

       and v.vendor_id=0000

       and v.invoice_type_lookup_code='STANDARD'

       group by v.vendor_id;

   

 BEGIN

 OPEN StdInv_cursor;

          loop

          FETCH StdInv_cursor INTO V_venid,V_invamt,V_taxamt;

          exit when StdInv_cursor%NOTFound;

          dbms_output.put_line (V_venid||'    '||'      '||V_invamt||'     '||V_taxamt);

          END Loop;

   CLOSE StdInv_cursor;

 END;