Functional Review

A ERP should support different categories of a organisation,for example

-It needs to support the Manufacturing Unit Operation,
-It needs to support the Finanacial Operations,
-It needs to support the Service Operations,
-It needs to support the Supply chain Operations like stock holding and clearance details on live and
-It needs to support the live operating and customer's dealings too.

The flow of functional Modules in Oracle Apps is like,

ERP is in Need for high level of transaction Management,Which can be customized according to the industry.Will See About Each modules in Next Upcoming Postings.Happy reading !!!!!

                                       

Logic to find out items soldy qty

In inventory if u need to find the aging details of sales report u need to refer to cutomer tables

i.e:
 ra_customer_trx_lines_all and ra_customer_trx_all

conditions to filter will be inventory_item_id,organization_id

e.g query:

select
        sum(nvl(quantity_invoiced,0))  + sum(nvl(quantity_Credited,0))  Qty,to_number(ltrim(rtrim(cta.interface_header_attribute10)))
from
        ra_customer_trx_lines_all ctla ,ra_customer_trx_all cta
where
        cta.customer_trx_id =  ctla.customer_trx_id
        and inventory_item_id = 86343
       -- and to_number(ltrim(rtrim(cta.interface_header_attribute10))) = o_id
    --    and cta.trx_date >= sysdate - TDays
group by
        ltrim(rtrim(cta.interface_header_attribute10)),
        inventory_item_id;

Using placeholder Column

Placeholder column can be used to reference more then one outputs:

Eg:-create a placeholder column,Define a function in it.Can have multiple outputs and referenced to different place holders in the report;

E.g Query in placeholder column

function CF_CalcFormula return Number is

--*************************** Getting Sales Info from Oracle **********
cursor Ora_Sales(it_id number, o_id number,TDays number) is
select
        sum(nvl(quantity_invoiced,0))  + sum(nvl(quantity_Credited,0))  Qty
from
        ra_customer_trx_lines_all ctla ,ra_customer_trx_all cta
where
        cta.customer_trx_id =  ctla.customer_trx_id
        and inventory_item_id = it_id --86343
        and to_number(ltrim(rtrim(cta.interface_header_attribute10))) = o_id
        and cta.trx_date >= sysdate - TDays
group by
        ltrim(rtrim(cta.interface_header_attribute10)),
        inventory_item_id;  

    AMD12 number; AMD1 number; Order_Qty number;
    Safety_qty number; min_qty number; max_qty number; 

L1Y := 0; L6M := 0; L3M := 0; L1M := 0;

begin

 :CP_IY := L1Y;        --placeholder in report
     open Ora_Sales(:inventory_item_id,:organization_id,183);
                  fetch Ora_Sales into L6M;
   close ora_sales;
   :CP_6M := L6M;        --placeholder in report
   open Ora_Sales(:inventory_item_id,:organization_id,91);
                  fetch Ora_Sales into L3M;
   close ora_sales;
   :CP_3M := L3M;        --placeholder in report
     open Ora_Sales(:inventory_item_id,:organization_id,30);
                  fetch Ora_Sales into L1M;

amd1 := ((LI6M + L6M)/6  + (LI3M + L3M)/3 + (LI1M + L1M) )/3;
    amd12 := (LI1Y + L1Y)/12;
    :CP_AMD1 := round(NVL(amd1,0),2);    --placeholder in report

:cp_LeadTime := :p_lead_time ; --placeholder in report
:cp_buf_stk := :p_buf_stk; --placeholder in report

:cp_agency := :p_segment1; --placeholder in report

return 0;
end;

Unix Cmds

If u have developed and saved a report in custom folder and when u r running it in application u need to make it avilable to applmgr too,so that it will reference and pick the rdf.

Cmds:

Login in putty to appluser or root:

Go the path e.g: $ cd /t01/applmgr/apps/apps_st/appl/xcust/12.0.0/reports/US

Give ownership: E,g
$chown -h applmgr  Report_name.rdf

Get user and runtime

TO get user submitted the report and run time with date:

In before parameter trigger

Define two variables:
xuser varchar2(30);
xrundate varchar2(30);

begin
   

    select REQUESTOR,to_char(request_date,'DD-Mon-YY HH24:MI')|| 'Hrs' into xuser,xrundate from   FND_CONC_REQ_SUMMARY_V where REQUEST_ID =  :P_CONC_REQUEST_ID;
:cp_user := nvL(xuser,'');
:cp_rundate := xrundate; 

end;

Pick frieght charges OM

select
HEADER_ID ,
LINE_ID ,
CHARGE_ID ,
CHARGE_NAME ,
CHARGE_AMOUNT
from OE_CHARGE_LINES_V
where header_id=(select header_id from oe_order_headers_all)

On order Quantity

SELECT sum(pl.quantity)
            FROM po_line_locations_all pll,
                 po_lines_all pl,
                 po_headers_all ph
           WHERE pl.po_header_id = ph.po_header_id
             AND pll.po_line_id = pl.po_line_id
             AND msi.organization_id = pll.ship_to_organization_id
             AND msi.inventory_item_id = pl.item_id