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