last Item wise Purchase order details Query

SELECT pha.segment1 po, pvv.vendor_name vendor_name, msi.segment1 item_code,
       pla.unit_price
  FROM po_headers_all pha,
       po_lines_all pla,
       po_vendors pvv,
       mtl_system_items msi
 WHERE pha.po_header_id = pla.po_header_id
   AND pha.vendor_id = pvv.vendor_id
   AND pla.item_id = msi.inventory_item_id
   AND msi.organization_id = :p_organization_id
   AND (TRUNC (pha.creation_date), pha.vendor_id, pla.item_id, pha.org_id) IN (
          SELECT *
            FROM (SELECT   MAX (TRUNC (ph.creation_date)) ct_date,
                           ph.vendor_id, pl.item_id, ph.org_id
                      FROM po_headers_all ph, po_vendors pv, po_lines_all pl
                     WHERE ph.vendor_id = pv.vendor_id
                       AND ph.po_header_id = pl.po_header_id
                       AND pl.item_id = :p_item_id
                       AND ph.org_id = :p_org_id
                  GROUP BY pl.item_id, ph.org_id, ph.vendor_id))

Procure to Pay Cycle (P2P) Query

SELECT
  PRH.SEGMENT1 REQ_NO,PRL.QUANTITY REQ_QTY,PRL.UNIT_PRICE REQ_PRICE,PRH.AUTHORIZATION_STATUS REQ_STATUS
,PHA.ORG_ID,PHA.SEGMENT1 PO_NUMBER,PLA.QUANTITY,PLA.UNIT_PRICE,PHA.AUTHORIZATION_STATUS PO_STATUS
,(SELECT APT.NAME FROM AP_TERMS APT WHERE APT.TERM_ID =PHA.TERMS_ID) PAYMENT_TERMS
,(SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID=RT.ORGANIZATION_ID) ORGZ_NAME
,RSH.SHIPMENT_NUM,RT.SUBINVENTORY,RT.PRIMARY_QUANTITY RCV_QTY,RT.TRANSACTION_TYPE
,AIA.INVOICE_NUM, AIA.VENDOR_ID
,AIP.PAYMENT_NUM,ACA.CHECK_NUMBER
FROM
     PO_REQUISITION_HEADERS_ALL PRH
    ,PO_REQUISITION_LINES_ALL   PRL
    ,PO_REQ_DISTRIBUTIONS_ALL   PRD
    ,PO_DISTRIBUTIONS_ALL       PDA
    ,PO_HEADERS_ALL             PHA
    ,PO_LINES_ALL               PLA
    ,RCV_TRANSACTIONS           RT
    ,RCV_SHIPMENT_HEADERS       RSH
    ,RCV_SHIPMENT_LINES         RSL
    ,AP_INVOICES_ALL            AIA
    ,AP_INVOICE_LINES_ALL       AIL
    ,AP_INVOICE_DISTRIBUTIONS_ALL AID
    ,AP_INVOICE_PAYMENTS_ALL    AIP
    ,AP_PAYMENT_SCHEDULES_ALL   APS
    ,AP_CHECKS_ALL              ACA
WHERE
    PRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID(+) = PRD.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID  = PRD.DISTRIBUTION_ID(+)
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_DISTRIBUTION_ID= RT.PO_DISTRIBUTION_ID
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND AIA.PO_HEADER_ID(+)=PHA.PO_HEADER_ID --AIA.QUICK_PO_HEADER_ID
--AND AIL.PO_LINE_ID(+)=PLA.PO_LINE_ID

AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.RCV_TRANSACTION_ID(+)=RT.TRANSACTION_ID
AND AID.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND AID.INVOICE_ID=AIP.INVOICE_ID(+)
AND AIP.INVOICE_ID=APS.INVOICE_ID(+)
AND AIP.INVOICE_PAYMENT_ID =ACA.PAYMENT_ID(+)
Order By PO_NUMBER

How to set a User as Worker

Step 1: Go to system administrator responsibility

Step 2:  Go to Security 

Step 3: then go User

Step 4: then Define menu.

Step 5: Query to user name.

Step 6: Set to person name.

How to print PO file attachments on the PO report (R12)

function CF_ATTACHFormula return Char is
V_ATTACH Varchar2(32000);
V_LONG_TXT LONG(32000);
begin
  SELECT DLT.LONG_TEXT INTO V_LONG_TXT
  /*AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  DLT.LONG_TEXT */
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_LONG_TEXT DLT    ,
  PO_HEADERS_ALL    PHA
WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID        = D.DOCUMENT_ID
AND DCT.CATEGORY_ID       = D.CATEGORY_ID
AND D.DATATYPE_ID         = DAT.DATATYPE_ID
AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID          = D.MEDIA_ID
AND DAT.NAME              = 'LONG_TEXT'
AND ENTITY_NAME = 'PO_HEADERS'
AND AD.PK1_VALUE  = PHA.PO_HEADER_ID
AND PHA.SEGMENT1 = :PO_ORDER;

DECLARE
   f_line       VARCHAR2 (32000);
   f            UTL_FILE.file_type;
   f_dir        VARCHAR2 (250);
   fname        VARCHAR2 (50);
   Comma1       VARCHAR (10);
   Comma2       VARCHAR (10);
   Comma3       VARCHAR (10);
   Comma4       VARCHAR (10);
   Comma5       VARCHAR (10);
 
BEGIN
   f_dir := '/usr/tmp';
   fname := 'TEST.txt';
   f := UTL_FILE.fopen (f_dir, fname, 'r');

 
   LOOP
      BEGIN
        
          UTL_FILE.get_line (f, f_line);
        EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

    
   V_ATTACH := V_ATTACH || CHR(10) ||f_line;
   
   SRW.MESSAGE(10001, V_LONG_TXT);

   ---   DBMS_OUTPUT.put_line(  f_line );

     
   END LOOP;

   UTL_FILE.fclose (f);
   COMMIT;
  
   Return V_ATTACH;
  
END;
end;