API to Approve PO (purchase order)

 select * from po_headers_all

/

set serverout on;

declare

  /**********************************************************************

  I_ACTION ==>

  *********************************************************************/

  V_STATUS VARCHAR2(100); --For check this HEADER_ID Status. (Is 'INCOMPLETE' ?)

  V_RETURN_CODE VARCHAR2(1) := 'S'; --Process status code ==> S : Success / E : Error

  V_RETURN_MSG  VARCHAR2(2000); --Error message

  V_ITEM_KEY  VARCHAR2(200); --For API

  V_ITEM_TYPE VARCHAR2(20);

  V_SEGMENT1           PO_HEADERS_ALL.SEGMENT1%TYPE; --For API

  V_PREPARER_ID        PO_HEADERS_ALL.AGENT_ID%TYPE; --For API

  V_DOCUMENT_TYPE_CODE PO_DOCUMENT_TYPES_ALL.DOCUMENT_TYPE_CODE%TYPE; --For API

  V_DOCUMENT_SUBTYPE   PO_DOCUMENT_TYPES_ALL.DOCUMENT_SUBTYPE%type; --For API

  I_HEADER_ID number; -- PO_HEADER_ID

  V_ORG_ID number;

  X_RET_MSG varchar2 (2000);

  X_RET_CODE varchar2(2000);

  cursor c1 is 

  SELECT

    distinct pha.po_header_id

FROM

    po_headers_all    pha,

    po_lines_all      pla

--    xxtgw_ff_po_upd   xfp

WHERE

    1 = 1

    AND pha.po_header_id = pla.po_header_id

    AND pha.po_header_id = 1269068;

--    AND pha.comments = xfp.po_header_desc

--    AND pla.line_num = xfp.line_number

--    AND pla.unit_price = xfp.price

--    AND xfp.po_header_desc NOT IN (

--        '17595',

--        '16346'

--    )

--    AND xfp.currency_id != 'USD'

--    and xfp.seq_num between 1 and 10;

BEGIN

for i in c1 loop

    I_HEADER_ID := i.PO_HEADER_ID;

  --Check PO HEADER_ID is exists ?

  BEGIN

    SELECT AUTHORIZATION_STATUS

      INTO V_STATUS

      FROM PO_HEADERS_ALL A

     WHERE A.PO_HEADER_ID = I_HEADER_ID;

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      V_STATUS      := NULL;

      V_RETURN_MSG  := 'No data found : PO_HEADER_ID ==> '||I_HEADER_ID;

      V_RETURN_CODE := 'E';

  END;


  IF V_RETURN_CODE = 'S' THEN

    BEGIN

      --Find API parameters

      SELECT pha.org_id,

             pha.agent_id,

             pha.segment1,

             pha.wf_item_type,

             pha.wf_item_key,

             pdt.document_subtype,

             pdt.document_type_code

        INTO V_ORG_ID,

             V_PREPARER_ID,

             V_SEGMENT1,

             v_ITEM_TYPE,

             V_ITEM_KEY,

             V_DOCUMENT_SUBTYPE,

             V_DOCUMENT_TYPE_CODE

        FROM apps.po_headers_all pha, apps.po_document_types_all pdt

       WHERE pha.type_lookup_code = pdt.document_subtype

         AND pha.org_id = pdt.org_id

         AND pdt.document_type_code = 'PO'

         AND pha.PO_HEADER_ID = I_HEADER_ID;

    EXCEPTION

      WHEN OTHERS THEN

        V_RETURN_MSG  := 'No data found : po_document_types_all - ' || sqlerrm;

        V_RETURN_CODE := 'E';

    END;

  END IF;


  IF V_RETURN_CODE = 'S' THEN

    mo_global.init('PO');

    mo_global.set_policy_context('S', V_ORG_ID);


    SELECT I_HEADER_ID /*P_EP_PO_HEADER_ID*/

           || '-' || TO_CHAR(PO_WF_ITEMKEY_S.NEXTVAL)

      INTO V_ITEM_KEY

      FROM DUAL;


    --ORACLE API

    BEGIN

      --Restart WorkFlow

      PO_REQAPPROVAL_INIT1.START_WF_PROCESS(ItemType               => V_ITEM_TYPE, --'POAPPRV',

                                            ItemKey                => V_ITEM_KEY,

                                            WorkflowProcess        => 'POAPPRV_TOP',

                                            ActionOriginatedFrom   => 'PO_FORM',

                                            DocumentID             => I_HEADER_ID,

                                            DocumentNumber         => V_SEGMENT1, -- Purchase Order Number

                                            PreparerID             => V_PREPARER_ID, -- Buyer/Preparer_id

                                            DocumentTypeCode       => V_DOCUMENT_TYPE_CODE,

                                            DocumentSubtype        => V_DOCUMENT_SUBTYPE,

                                            SubmitterAction        => 'APPROVE',

                                            forwardToID            => NULL,

                                            forwardFromID          => NULL,

                                            DefaultApprovalPathID  => NULL,

                                            Note                   => NULL,

                                            PrintFlag              => 'N',

                                            FaxFlag                => 'N',

                                            FaxNumber              => NULL,

                                            EmailFlag              => 'N',

                                            EmailAddress           => NULL,

                                            CreateSourcingRule     => 'N',

                                            ReleaseGenMethod       => 'N',

                                            UpdateSourcingRule     => 'N',

                                            MassUpdateReleases     => 'N',

                                            RetroactivePriceChange => 'N',

                                            OrgAssignChange        => 'N',

                                            CommunicatePriceChange => 'N',

                                            p_Background_Flag      => 'N', 

                                            p_Initiator            => NULL,

                                            p_xml_flag             => NULL,

                                            FpdsngFlag             => 'N',

                                            p_source_type_code     => NULL);

      --API

      PO_REQAPPROVAL_ACTION.APPROVE_DOC(V_ITEM_TYPE,

                                        V_ITEM_KEY,

                                        NULL,

                                        'RUN',

                                        X_RET_MSG); --20120303

    END;


    IF NVL(V_RETURN_CODE, 'S') <> 'E' THEN

      V_RETURN_CODE := 'S';

    END IF;

  END IF;


   FND_FILE.PUT_LINE(FND_FILE.LOG, 'V_RETURN_CODE=' || V_RETURN_CODE);

  FND_FILE.PUT_LINE(FND_FILE.LOG, 'V_RETURN_MSG=' || V_RETURN_MSG);


  X_RET_CODE := V_RETURN_CODE;

  X_RET_MSG  := V_RETURN_MSG;

  end loop;

END ;

Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more

QR code Generate From Oracle PLSQL

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...
Read more