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

Discoverer Parameters

Creating parameter in Discoverer report


After Creating the workbook,Click on tools->Parameters

Hit on new add the column name in which u need the parameters to pick.

To add between condition add new condition and select the parameter as the condition item.

Party Merge Alert CRM

                                                                     
                                                                     
                                                                     
                                             
ALERTS


1.Party Merge

Table from :HZ_MERGE_PARTIES 
Table to Update:XXPN_TRANSACTION_LINES
Alert_Name:XX_CUST_ALERT
Application:Receivables

Event type:

SELECT FROM_PARTY_ID,TO_PARTY_ID 
INTO &FROM_PARTY,&TO_PARTY
FROM  APPS.HZ_MERGE_PARTIES  
WHERE FROM_PARTY_ID!=TO_PARTY_ID
AND MERGE_TYPE='PARTY_MERGE'
AND MERGE_REASON_CODE='MERGER'
AND ROWID=:ROWID

Action Type:

begin
UPDATE XXPN_TRANSACTION_LINES
SET PARTY_ID=&TO_PARTY
WHERE PARTY_ID=&FROM_PARTY;
end;
/


************************************************

2.Account Merge

Table from RA_CUSTOMER_MERGE_HEADERS 
Table to XXPN_TRANSACTION_LINES
Alert name:XX_ACCOUNT_MERGE


Event type: select DUPLICATE_ID,CUSTOMER_ID 
      INTO &FROM_CUST,&TO_CUST
      from RA_CUSTOMER_MERGE_HEADERS 
      where MERGE_REASON_CODE='MERGER'
      and PROCESS_FLAG='Y'
      and DUPLICATE_ID!=CUSTOMER_ID
      AND ROWID=:ROWID
      
      
      Action_type:
      
      BEGIN
      UPDATE APPS.XXPN_TRANSACTION_LINES 
      SET CUSTOMER_ACCOUNT_ID=&TO_CUST WHERE CUSTOMER_ACCOUNT_ID=&FROM_CUST;
      END;
      /