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;
      /

Default date in report

When we transfer the custom report to the application,we get error on input parameter date while running because of mismatch of date format type:

the default type of the date in application is YYYY/MM/DD HH24:MI:SS

Change in the format of input parameter date and transfer to application.

Set who Columns

Set who columns in pre-insert trigger in block level trigger

FND_STANDARD.SET_WHO;


The WHO columns are

CREATED BY,
CREATION_DATE,
LAST_UPDATE_BY,
LAST_UPDATED_DATE