Friday, 1 June 2012

Self-Service Reports



Overview

1.    Usually for seeing output of any Report we need to run Concurrent Program, but we can view the output of Report without running concurrent program manually. As we register our OAF web page or Form under function è sub menu è menu è Responsibility
Similarly we can register our normal report in above flow and can view the output in front end without running any concurrent program.   
Steps
1.     Understanding requirement
For Example: xxx General Payslip Self Service Report
This report was need under Employee Self-Service Responsibility in Front End

2.     Prepare the Report specific to current 'USER_ID'
----------our report query taking employee_number based on current USER_ID
employee_number IN
                (SELECT employee_number
                   FROM per_all_people_f
                  WHERE person_id IN
                           (SELECT employee_id
                              FROM fnd_user
                             WHERE user_id IN
                                      (SELECT FND_PROFILE.VALUE ('USER_ID')
                                         FROM DUAL)))

-------------------------------------------
Report Trigger Before Form--------------
-------------------------------------------

function BeforePForm return boolean is
l_application_id NUMBER;
l_resp_id NUMBER;
l_user_id NUMBER;
begin
l_user_id := FND_PROFILE.VALUE('USER_ID');
l_resp_id := FND_PROFILE.VALUE('RESP_ID');
l_application_id := FND_PROFILE.VALUE('RESP_APPL_ID');
fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
SRW.USER_EXIT('FND SRWINIT');
return (TRUE);
end;

-------------------------------------------
Report Trigger After Form---------------
-------------------------------------------

function AfterPForm return boolean is
begin
 srw.user_exit('FND SRWEXIT');
 return (TRUE);
end;


Normal Reports created short name is XXPAYSLIP_NEW_SS    
Registered under HRMS request group.

3.     Create a function.
PropertiesèType : SSWA jsp function

Web HTMLèHTML Call :
OA.jsp?akRegionApplicationId=0&akRegionCode=FNDCPPROGRAMPAGE&programApplName=PER&programName=XXPAYSLIP_NEW_SS&programRegion=Hide&scheduleRegion=Hide&notifyRegion=Hide&printRegion=Hide


We need to pass
programApplName (Responsibility short name under which report is registerd)
and programName (Our Report short name)

4.     Assigning this function to Menu : Employee Self-Service.


5.     Done.



Automatic Train is created in OAF web page:

Once Employee Submits, report output will be displayed

Thursday, 29 March 2012

IMP ABOUT THE WHOLE CYCLE OF O TO CASH

---------------------------IMP ABOUT THE RELATION AND FLOW -------------------------------
 INV---OM---- AR--- > GL<-----AP---- PO----INV     O TO C CYCLE CALLED ORDER TO CASH CYCLE.....

AR --INVOICE AND RECEIPT
AP --PAYMENT  AND INVOICE

 -------------------IMP ABOUT THE RELATION -----------------------------------------------
 (CUSTOMER) AR------RECEIVING MONEY FROM CUSTOMER  ------>ORGANIZATION  -----PAYING MONEY TO SUPPLIER ------>AP (SUPPLIER)

Saturday, 24 March 2012

referance query

SELECT ORGANIZATION_CODE AS "Warehouse name", SEGMENT1 AS "Item Number",
       Description AS "Item Descriptions",PRIMARY_UOM_CODE AS "UOM",
       sum(MOQ.TRANSACTION_QUANTITY) AS "Quantity",ACTUAL_COST AS "Cost"
      
     
FROM   MTL_PARAMETERS MP,MTL_SYSTEM_ITEMS_B MB,
       MTL_ONHAND_QUANTITIES MOQ,MTL_MATERIAL_TRANSACTIONS MMT
      
WHERE MP.ORGANIZATION_ID = MB.ORGANIZATION_ID
AND   MB.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND   MB.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND   MOQ.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND   MMT.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND   MOQ.CREATE_TRANSACTION_ID = MMT.TRANSACTION_ID
AND   MP.ORGANIZATION_ID = NVL(:ORG,MP.ORGANIZATION_ID)
AND   TRUNC(MOQ.CREATION_DATE) BETWEEN NVL (:FROM_DATE, trunc(moq.creation_date))
                                      AND NVL (:TO_DATE, trunc(moq.creation_date))
group by moq.organization_id,ORGANIZATION_CODE,SEGMENT1,Description,PRIMARY_UOM_CODE,ACTUAL_COST,MOQ.CREATION_DATE,
      MOQ.INVENTORY_ITEM_ID;



function AfterPForm return boolean is
begin
:F_D:=to_date(:FROM_DATE,'YYYY/MM/DD HH24:MI:SS');
:T_D:=to_date(:TO_DATE,'YYYY/MM/DD HH24:MI:SS');
  return (TRUE);
end;

function BeforeReport return boolean is
begin
  SRW.USER_EXIT('FND SRWINIT');
  return (TRUE);
end;

function AfterReport return boolean is
begin
      SRW.MESSAGE(000,'********** HMC Warehouse Obsolesce Report **********');
  if :CS_COUNT > 0 then 
  SRW.MESSAGE(001,'Number Of Records Printed '||:CS_COUNT);
  else
  SRW.MESSAGE(001,'No data for the provided report parameters');
  end if;
 
  SRW.MESSAGE(000,'********** End of HMC Warehouse Obsolesce Report **********');
  SRW.USER_EXIT('FND SRWEXIT');
  return (TRUE);
end;

po print correct

select poh.segment1 PO_Number,poh.REVISION_NUM, papf.full_name,aps.vendor_name Vendor_Name,apsa.address_line1||','||apsa.address_line2||','||apsa.address_line3
||','||apsa.address_line4||','||apsa.city||','||apsa.zip Vendor_Address,TO_CHAR(TO_DATE(poh.CREATION_DATE,'DD-MON-RRRR'))CREATION_DATE,
TO_CHAR(TO_DATE(SYSDATE,'DD-MON-RRRR')) PRINT_DATE,TO_CHAR(TO_DATE(poh.REVISED_DATE,'DD-MON-RRRR'))REVISED_DATE,
decode(poh.authorization_status ,'APPROVED',poh.authorization_status,'DRAFT') Status, poh.type_lookup_code PO_Type,poh.currency_code,
poh.user_hold_flag on_hold, poh.freight_terms_lookup_code delivery_mode,poh.quote_vendor_quote_number supplier_quote_number,
poh.attribute1 Insurance,apt.name payment_term,pol.line_num||'.'||rsl.line_num line_number,pltv.line_type line_type
,(select segment1
  from mtl_system_items_b
  where inventory_item_id=pol.item_id
  and organization_id =prl.destination_organization_id) Item_code
, pol.item_description,
pol.vendor_product_num vendor_item_code,mcb.concatenated_segments item_category,pol.quantity,pol.UNIT_MEAS_LOOKUP_CODE Purchasing_uom,pol.unit_price,
pol.quantity*pol.unit_price line_total,poll.PROMISED_DATE,prh.segment1||'.'||prl.LINE_NUM PR_Ref,
hrl.ADDRESS_LINE_1||','||hrl.address_line_2||','||hrl.address_line_3||','||hrl.TOWN_OR_CITY||','||hrl.country Ship_to_location,pol.NOTE_TO_VENDOR Line_comments,
decode(poh.authorization_status ,'APPROVED','','This PO is in draft status and should not be considered as a confirmed Purchase Order from HMC') COMMENTS
from po_headers_all poh,
po_lines_all pol,
ap_terms_tl apt,
ap_suppliers aps,
ap_supplier_sites_all apsa,
per_all_people_f papf,
--mtl_system_items_b msib,
mtl_categories_b_kfv mcb,
po_line_locations_all poll,
hr_locations hrl,
po_distributions_all pod,
po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,fnd_user fnd,po_line_types_vl pltv
where poh.PO_HEADER_ID = pol.po_header_id and
      pol.line_type_id= pltv.LINE_TYPE_ID and
      poh.terms_id = apt.term_id and
      poh.VENDOR_ID  = aps.vendor_id and
      poh.VENDOR_SITE_ID =apsa.vendor_site_id and
      aps.vendor_id =apsa.vendor_id and
      poh.AGENT_ID  = papf.person_id and
      fnd.employee_id(+)=PAPF.PERSON_ID and
      --pol.ITEM_ID  = msib.INVENTORY_ITEM_ID and
      pol.category_id= mcb.category_id and
      pol.po_line_id=poll.po_line_id and
      poll.LINE_LOCATION_ID = pod.LINE_LOCATION_ID
      and prh.requisition_header_id(+)=prl.requisition_header_id
      and prl.requisition_line_id(+)=prd.requisition_line_id
      and prd.distribution_id(+)=pod.req_distribution_id
      --and prl.DESTINATION_ORGANIZATION_ID = msib.ORGANIZATION_ID
      and poll.ship_to_location_id = hrl.location_id
      AND pod.po_distribution_id=rsl.po_distribution_id(+)
      AND rsl.shipment_header_id = rsh.shipment_header_id(+) and
      apt.language='US'
     and poh.PO_HEADER_ID = nvl(:SEG,poh.PO_HEADER_ID)
      and aps.vendor_id = nvl(:VEND,aps.vendor_id)
and TRUNC(poh.CREATION_DATE) >= to_date(substr(:DAT,1,instr(:DAT,' ')),'yyyy/mm/dd')
and trunc(poh.CREATION_DATE) <= to_date(substr(:DAT1,1,instr(:DAT1,' ')),'yyyy/mm/dd')
     --AND poh.CREATION_DATE between to_date(:dat,'dd/mon/yyyy hh24:mi:ss')
      --and  to_date(:dat1,'dd/mon/yyyy hh24:mi:ss')
      and trunc(sysdate) between trunc(papf.EFFECTIVE_START_DATE) and trunc(papf.EFFECTIVE_END_DATE)    
      and fnd.user_id=FND_GLOBAL.USER_ID
 order by poh.segment1,pol.line_num



function BeforeReport return boolean is
begin
  SRW.USER_EXIT('FND SRWINIT');
  /*:P_USERID:=FND_GLOBAL.USER_ID;
  :P1:='AND FND.USER_ID = '''||:P_USERID||'''';
  return (TRUE);
exception
    when others then
    :P1:='';*/
     return (TRUE);   
end;



function AfterReport return boolean is
begin
     SRW.MESSAGE(000,'*****************PO Print Report***************');
  if :CS_COUNTRCP>0 then
  SRW.MESSAGE(000,'Number of PO Printed:'||:CS_COUNTRCP);
  else
  SRW.MESSAGE(000,'No Data Found:');   
  end if;
  SRW.MESSAGE(000,'*****************End Of Report**********************');
  SRW.USER_EXIT('FND SRWEXIT');
  return (TRUE);
end;

query for the po print report

SELECT
DISTINCT(PHA.SEGMENT1) "PO Number",
PHA.REVISION_NUM "Revision",
PHA.AGENT_ID "Buyer ID",
PAPF.FULL_NAME "Buyer",
PV.SEGMENT1 "Vendor",
PVSA.ADDRESS_LINE1 || ',' ||PVSA.ADDRESS_LINE2 || ',' ||PVSA.ADDRESS_LINE3 || ',' ||PVSA.CITY || ',' ||PVSA.ZIP || ',' ||PVSA.COUNTRY "Vendor Address",
to_char(PHA.CREATION_DATE,'DD-MON-YYYY')"Creation Date",
to_char(SYSDATE,'DD-MON-YYYY')"Printed Date",
to_char(PHA.REVISED_DATE,'DD-MON-YYYY') "Revised Date",
DECODE(PHA.AUTHORIZATION_STATUS,'REQUIRES REAPPROVAL','DRAFT',
 'INCOMPLETE',    'DRAFT',
 'IN PROCESS',    'DRAFT',
 'PRE-APPROVED' ,'DREFT',
 '','DRAFT',
'APPROVED') "Status",
PHA.TYPE_LOOKUP_CODE "PO type",
PHA.CURRENCY_CODE "Currency",
PHA.USER_HOLD_FLAG "On Hold",
PHA.FREIGHT_TERMS_LOOKUP_CODE "Delivery Mode",
PHA.QUOTE_VENDOR_QUOTE_NUMBER "Supplier Quote Number",
PHA.ATTRIBUTE1 "Insurance",
APT.NAME "Payment Term" ,
PLA.LINE_NUM "Line Number",
PLA.ORDER_TYPE_LOOKUP_CODE "Line Type",
msib.SEGMENT1 "Item code",
PLA.ITEM_DESCRIPTION "Item Description",
PLA.VENDOR_PRODUCT_NUM "Vendor Item Code",
MCBK.CONCATENATED_SEGMENTS "Item Category",
PLA.QUANTITY "Quantity",
PLA.UNIT_MEAS_LOOKUP_CODE "Purchasing UOM",
PLA.UNIT_PRICE "Unit Price",
DECODE(PLA.QUANTITY,NULL,1*PLA.UNIT_PRICE,
 PLA.QUANTITY*PLA.UNIT_PRICE
 ) "Line Total",
--(PLA.QUANTITY*PLA.UNIT_PRICE) "Line Total",
PLLA.PROMISED_DATE "Promised date",
PRHA.SEGMENT1||'.'||PRLA.LINE_NUM "PR Ref",
HL.ADDRESS_LINE_1 || ',' || HL.ADDRESS_LINE_2 || ',' ||HL.ADDRESS_LINE_3 || ',' ||HL.TOWN_OR_CITY || ',' ||HL.POSTAL_CODE || ',' ||HL.COUNTRY "Ship to Location",
PLA.NOTE_TO_VENDOR "Line Comments",
xx_po_print_receipt_test(pha.po_header_id,pha.currency_code)"PO Total(QAR)",
xx_po_print_receipt_test_rates(PHA.CURRENCY_CODE,'USD',PHA.CREATION_DATE,PHA.PO_HEADER_ID) "PO Total (USD)",
PHA.COMMENTS "Comments"

FROM
PER_ALL_PEOPLE_F PAPF ,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_TERMS APT,
HR_LOCATIONS HL,
MTL_CATEGORIES_B_KFV MCBK,
MTL_SYSTEM_ITEMS_B MSIB,
ORG_ORGANIZATION_DEFINITIONS OOD,

PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQUISITION_HEADERS_ALL PRHA

WHERE PAPF.PERSON_ID=PHA.AGENT_ID
AND PV.VENDOR_ID =PHA.VENDOR_ID
AND PV.VENDOR_ID=PVSA.VENDOR_ID
AND PHA.VENDOR_SITE_ID=PVSA.VENDOR_SITE_ID
AND PHA.TERMS_ID=APT.TERM_ID
AND PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_LOCATION_ID=HL.LOCATION_ID
AND MCBK.CATEGORY_ID=PLA.CATEGORY_ID
and MSIB.INVENTORY_ITEM_ID=PLA.ITEM_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID=PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID=PRHA.REQUISITION_HEADER_ID
AND PV.SEGMENT1=NVL(:PV_SEG,PV.SEGMENT1)
AND  PHA.SEGMENT1=NVL(:PO_SEG,PHA.SEGMENT1)
AND PHA.CREATION_DATE BETWEEN :S AND :E
ORDER BY PHA.SEGMENT1;



function BeforeReport return boolean is
begin
 SRW.USER_EXIT('FND SRWINIT');
  return (TRUE);
end;



function AfterReport return boolean is
begin
  SRW.USER_EXIT('FND SRWEXIT');
  return (TRUE);
end;


date
RRRR/MM/DD HH24:MI:SS

P_CONC_REQUEST_ID
number
20

Thursday, 22 March 2012

How to set ORG ID in PL/SQL

11i
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('ORG_ID');
END;

R12
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S',ORG_ID);
END;