zaterdag 21 november 2015

Open Interface Requisition to Purchase Order

In this article I like to share some code samples to create a purchase requisition related to a blanket, approve it, create a purchase order from it and approve that. Then perform a receipt on the purchase order just created.

Create Requisition

So the first step is to create the requisition. I have the number start with a special prefix (XLS- by default, could be anything), so in my AME I can approve these requisitions automatically. Here I'm assuming that requisitions created this way should be approved automatically.
The header does nothing really but draw a new id.

      ----------------------------------------------------------------------------------------------
      -- Create requisition header
      ----------------------------------------------------------------------------------------------
      PROCEDURE Create_Requistion (
        p_po_header_id      IN  NUMBER
      , x_org_id OUT NUMBER
      , x_requisition_header_id    OUT NUMBER   
      , x_vendor_id           OUT NUMBER
      , x_vendor_site_id      OUT NUMBER
      , x_requisition_number   OUT VARCHAR2 
      , x_error_code        OUT VARCHAR2
      , x_error_msg         OUT VARCHAR2
      )
      IS
        CURSOR C_Vendor
        (
          cp_header_id  PO_HEADERS.PO_Header_Id%TYPE
        )
        IS
        SELECT H.Vendor_Id
        ,      H.Vendor_Site_Id
        ,      H.Org_Id
        FROM   PO_HEADERS_ALL  H
        WHERE  H.PO_Header_Id    = cp_header_id
        ;
       
      BEGIN
        x_error_code := NULL;
        x_requisition_number := NVL (FND_PROFILE.Value ('XXX_MY_PREFIX'),'XLS-') || to_char (Sysdate,'DDMMYYYYHH24MISS');
       
        SELECT PO_REQUISITION_HEADERS_S.NEXTVAL    
        INTO   x_requisition_header_id
        FROM   DUAL;
       
        OPEN  C_Vendor (cp_header_id => p_po_header_id);
        FETCH C_Vendor INTO x_vendor_id, x_vendor_site_id,x_org_id;
        CLOSE C_Vendor;
               
      END Create_Requistion;



After that I create a loop to go through the items I like to add to my requisition. For each line we insert a record in PO_REQUISITIONS_INTERFACE_ALL. In our case the charge account is fetched based on some rules. Like the expense account segment comes from the item.

select  r.segment_value    
            from    mtl_item_categories_v           c
            ,       FND_ID_FLEX_STRUCTURES_VL       s
            ,       po_rule_expense_accounts        r           
            where   s.id_flex_num                   = c.structure_id
            and     s.id_flex_structure_code        = 'PO_ITEM_CATEGORY'
            and     r.rule_value_id                 = c.category_id
            and     c.organization_id               = (select master_organization_id from mtl_parameters where organization_id = m.organization_id)
            and     r.org_id                        = fnd_profile.value ('ORG_ID')
            and        c.inventory_item_id                = m.inventory_item_id     
     

By passing segments and allowing for dynamic insert you can create the account dynamically. Otherwise you could fetch the code_combination_id ofcourse.

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
             (interface_source_code
             ,source_type_code
             ,requisition_type
             ,destination_type_code
             ,item_id
             ,item_description
             ,quantity
             ,authorization_status
             ,preparer_id           
             , requisition_header_id
             ,req_number_segment1        
             ,uom_code
             ,destination_organization_id
             ,destination_subinventory
             ,deliver_to_location_id
             ,deliver_to_requestor_id
             ,need_by_date
             ,gl_date
             --,charge_account_id
             , charge_account_segment1
             , charge_account_segment2
             , charge_account_segment3
             , charge_account_segment4
             , charge_account_segment5
             , charge_account_segment6
             , charge_account_segment7
             , charge_account_segment8
             , charge_account_segment9
             ,accrual_account_id
             --,variance_account_id
             ,org_id
             ,suggested_vendor_id
             ,suggested_vendor_site_id
             ,unit_price
             ,creation_date
             ,created_by
             ,last_update_date
             ,last_updated_by
             , header_description
             , category_id
             --, category_segment1
            , autosource_doc_header_id                    -- 24-Sep-2015
             , autosource_doc_line_num                    -- 24-Sep-2015
             , autosource_flag                            -- 24-Sep-2015
             )
            VALUES ('SCAN'
             ,'VENDOR'
             ,'PURCHASE'
             ,I.destination_type_code -- 'EXPENSE' -- depends on whether it's an article or not ..
             ,I.inventory_item_id
             ,l_description
             ,I.Quantity
             ,'INCOMPLETE'
             ,l_emp_id           
             , p_requisition_header_id
             , p_requisition_number       
             , I.Primary_Unit_Of_Measure
             , p_item_org_id
             ,null -- rec_get_lines_info.subinventory
             , I.Location_Id -- rec_get_lines_info.location_id
             ,l_emp_id
             ,sysdate
             ,SYSDATE
             , l_segment1
             , l_segment2
             , l_segment3
             , l_segment4
             , l_segment5
             , l_segment6
             , l_segment7
             , l_segment8
             , l_segment9
             , null -- rec_get_lines_info.ap_accrual_account
             , p_org_id
             , p_vendor_id
             , p_vendor_site_id
             , COALESCE (I.Price_From_Blanket,l_cost_price,I.Unit_Price)
             ,SYSDATE
             ,fnd_global.user_id
             ,SYSDATE
             ,fnd_global.user_id
             , 'Description ...'
             , I.Category_Id
             --, I.category_segment
             , p_blanket_id
             , p_blanket_line_num
             , p_autosource_flag
             );  



Now after we have added the lines, we need to import it. So we submit the requisition import in PL/SQL using

l_request_id :=
                fnd_request.submit_request (application => 'PO' --Application,
                , program => 'REQIMPORT' --Program,
                 ,argument1 => 'SCAN' --Interface Source code,             
                 ,argument2 => '' --Batch ID,
                 ,argument3 => 'ALL'--Group By,
                 ,argument4 => ''--Last Req Number,
                 ,argument5 => 'N'--Multi Distributions,
                 ,argument6 => 'Y' --Initiate Approval after ReqImport
                 );
                 COMMIT;


And since we want to continue with creating the PO after that, we wait until the request is finished using


l_conc_status := APPS.FND_CONCURRENT.WAIT_FOR_REQUEST
                                (request_id => l_request_id
                                ,interval   => 5            -- Sleep 5 seconds between checks.
                                ,max_wait   => 600           
                                ,phase      => l_phase
                                ,status     => l_status
                                ,dev_phase  => l_dev_phase
                                ,dev_status => l_dev_status
                                ,message    => l_message
                                );


If the DEV_STATUS is not in ERROR, CANCELLED or TERMINATED, we continue. 


Verify Import

It's best to verify whether the import was OK using a procedure like this.

 -------------------------------------------------------------------------------
      -- Verify requisition import
      -------------------------------------------------------------------------------
      PROCEDURE Verify_Import (
          p_requisition_number         IN VARCHAR 
        , x_req_header_id            OUT NUMBER
        , x_error_msg                 OUT VARCHAR2
        )
        IS
          CURSOR C_Interface
          IS
          SELECT M.Segment1
          ,         I.Item_Description
          ,      I.Transaction_Id
          FROM      PO_REQUISITIONS_INTERFACE_ALL I
          ,         MTL_SYSTEM_ITEMS_B M
          WHERE  I.Req_Number_Segment1 = p_requisition_number
          AND    I.Process_Flag = 'ERROR'
          AND    I.Item_Id = M.Inventory_Item_Id (+)
          AND    I.Destination_Organization_Id = NVL (M.Organization_Id,I.Destination_Organization_Id)
          ;
         
          CURSOR C_Actual_ID
          IS
          SELECT Requisition_Header_Id
          FROM   PO_REQUISITION_HEADERS_ALL
          WHERE  Segment1 = p_requisition_number
          ;
         
         
          CURSOR C_Errors
          (
            p_transaction_id NUMBER
            )
          IS
          SELECT E.Error_Message
          FROM   PO_INTERFACE_ERRORS E
          WHERE  E.Interface_Transaction_Id = p_transaction_id
          ;
         
          l_concat_msg VARCHAR2(240);
          l_id        NUMBER;
         
        BEGIN
       
          OPEN  C_Actual_ID;
          FETCH C_Actual_Id INTO l_id;
          CLOSE C_Actual_Id;
         
          IF l_id IS NOT NULL
          THEN

            -- Update some error record
            null;
         END IF;
         
          <<interface>>
          FOR I IN C_Interface
          LOOP
             x_error_msg := 'Error during import Req ' || p_requisition_number;
             l_concat_msg := null;
             <<Errors>>
             FOR E IN C_Errors (p_transaction_id => I.Transaction_id)
             LOOP

               -- Print error message based on E.Error_message and item number
                -- for example
                NULL;
             END LOOP Errors;
                         
          END LOOP Interface;
          COMMIT;
        END Verify_Import;





Approve Requisition

Now we know the requisition was imported and it's OK, we can approve it using this procedure.

 -------------------------------------------------------------------------------
      -- Approve the requisition
      -------------------------------------------------------------------------------
      PROCEDURE Approve_Requisition
      (
        p_requisition_number            VARCHAR2
      , x_msg                            OUT VARCHAR2
      )
      IS
      l_msg  WF_NOTIFICATIONS.Subject%TYPE;
     
      CURSOR C_Wfl_Msg
      (
        p_item_key        IN VARCHAR2
      )
      IS
      SELECT Subject
      FROM   WF_NOTIFICATIONS
      WHERE  Item_Key = p_item_key
      AND    Message_Type = 'REQAPPRV'
      ;
     
      CURSOR c_req_details
       IS
          SELECT prh.requisition_header_id,
                 prh.org_id,
                 prh.preparer_id,
                 prh.segment1,
                 pdt.document_subtype,
                 pdt.document_type_code,
                 prh.authorization_status
            FROM apps.po_requisition_headers_all prh,
                 apps.po_document_types_all pdt
           WHERE     prh.type_lookup_code = pdt.document_subtype
                 AND prh.org_id = pdt.org_id
                 AND pdt.document_type_code = 'REQUISITION'
                 AND NVL (authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
                 AND prh.segment1 = p_requisition_number;     -- Enter The Requisition Number

        v_item_key VARCHAR2(240);
       
      BEGIN 
     
       <<req>>
      FOR p_rec IN c_req_details
       LOOP
      
        mo_global.init ('PO');
        mo_global.set_policy_context ('S', p_rec.org_id);


     
      SELECT    p_rec.requisition_header_id
                 || '-'
                 || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
            INTO v_item_key
            FROM DUAL;
           
            p_l ('Start Requisition approval ' || v_item_key);

             po_reqapproval_init1.start_wf_process (
             itemtype                 => NULL,
             itemkey                  => v_item_key,
             workflowprocess          => 'POAPPRV_TOP',
             actionoriginatedfrom     => 'PO_FORM',
             documentid               => p_rec.requisition_header_id, -- requisition_header_id
             documentnumber           => p_rec.segment1,     -- Requisition Number
             preparerid               => p_rec.preparer_id,
             documenttypecode         => p_rec.document_type_code,  -- REQUISITION
             documentsubtype          => p_rec.document_subtype,       -- PURCHASE
             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);
           
             COMMIT;
       
         END LOOP Req;
       
              
         -- Check workflow
         OPEN  C_WFL_Msg (p_item_key => v_item_key);
         FETCH C_WFL_Msg INTO l_msg;
         IF C_Wfl_Msg%FOUND
         THEN
           p_l ('Requisition Approval: ' || l_msg);
         END IF;
         CLOSE C_WFL_Msg;
       
        END Approve_Requisition;


Create Purchase Order based on Requisition

The Requisition is created and approved, so we can create our PO on it using the following procedure. In this case we also relate to a blanket agreement. After creating the PO I updated the comments and notes to vendor, since the system did not what I've added in the interface.

 -------------------------------------------------------------------------------
      -- Auto create the PO
      -------------------------------------------------------------------------------
      PROCEDURE Auto_Create_PO
      (
        p_requisition_number            IN VARCHAR2
      , p_po_header_id                    IN NUMBER
      , x_po_number                        OUT VARCHAR2
      , x_po_header_id                    OUT NUMBER     
      , x_error_msg                        OUT VARCHAR2
      )
      IS

               
            cursor c_req_lines
            is
            select             prha.segment1 req_num
            ,                hla.ship_to_location_id
            ,                prla.*
            from              po_requisition_headers_all prha
            inner join         po_requisition_lines_all prla
            on                 prha.requisition_header_id = prla.requisition_header_id
            inner join         hr_locations_all hla
            on                 prla.deliver_to_location_id = hla.location_id
            where             1=1
            and             prha.authorization_status = 'APPROVED'
            and             nvl(prla.reqs_in_pool_flag,'N') = 'Y'     
            and             nvl(prla.cancel_flag,'N') = 'N'
            and             nvl(prla.closed_code,'OPEN') = 'OPEN'
            and             prha.segment1 = p_requisition_number
            order by         hla.ship_to_location_id
            ,                prla.creation_date desc
            ;       
           
           
           


            l_line_num number;
            l_nr_of_lines number := 0;
            l_shipment_num number;
            l_prev_deliver_to_location_id po_requisition_lines_all.deliver_to_location_id%type;
            l_prev_ship_to_location_id hr_locations_all.ship_to_location_id%type;
            l_prev_blanket_po_header_id po_headers_all.po_header_id%type;
            l_prev_blanket_po_line_num po_lines_all.line_num%type;
            l_interface_header_id po_headers_interface.interface_header_id%type;
            l_batch_id po_headers_interface.batch_id%type;
            l_vendor_id po_headers_all.vendor_id%type;
            l_vendor_site_id po_headers_all.vendor_site_id%type;
            l_agent_id po_headers_all.agent_id%type;
            l_org_id po_headers_all.org_id%type;
            l_currency_code po_headers_all.currency_code%type;
            l_bill_to_location_id po_headers_all.bill_to_location_id%type;
            l_document_num po_headers_all.segment1%type;
            l_last_updated_by po_headers_all.last_updated_by%type;
            l_created_by po_headers_all.created_by%type;
            l_interface_line_id po_lines_interface.interface_line_id%type;
            l_promised_date po_line_locations_all.promised_date%type;
            l_from_line_id po_lines_all.from_line_id%type;
            l_consolidate ap_supplier_sites_all.attribute12%type;
            x_return_status varchar2(1);
            x_msg_count number;
            x_msg_data fnd_new_messages.message_text%type;
            x_document_num po_headers_all.segment1%type;
            x_autocreated_doc_id po_headers_all.po_header_id%type;
            x_num_lines_processed number;
           
            l_header_created boolean := false;
       
      BEGIN 
     
        x_error_msg := null;
        l_line_num := 0;
        p_l ('For all requisition lines ...' || p_requisition_number);
      <<req>>
      FOR i IN c_req_lines
       LOOP
      
            l_nr_of_lines := l_nr_of_lines + 1;
            mo_global.init ('PO');
            mo_global.set_policy_context ('S', i.org_id);
           
            IF NOT l_header_created
            THEN
              l_header_created := true;
             
            OPEN  C_PO_Header (cp_header_id => p_po_header_id);
            FETCH C_PO_Header INTO l_po_header;
            CLOSE C_PO_Header;
           
            l_vendor_id                := l_po_header.vendor_id;
            l_vendor_site_id        := l_po_header.vendor_site_id;
            l_agent_id                := l_po_header.agent_id;
            l_org_id                := l_po_header.org_id;
            l_currency_code            := l_po_header.currency_code;
            l_bill_to_location_id    := l_po_header.bill_to_location_id;
            l_consolidate            := l_po_header.consolidate;
           

            select po_headers_interface_s.nextval
            ,po_core_sv1.default_po_unique_identifier ('PO_HEADERS',l_org_id)
            into l_interface_header_id
            ,l_document_num
            from dual;
           
            l_batch_id := l_interface_header_id;
           
            p_l ('Interface header id is ' || l_interface_header_id || ' and org id is ' || l_org_id || ' and batch id is ' || l_batch_id);
           
            insert into po_headers_interface
            (
            interface_header_id
            , interface_source_code
            , org_id
            , batch_id
            , process_code
            , action
            , document_type_code
            , document_subtype
            , document_num
            , group_code
            , vendor_id
            , vendor_site_id
            , agent_id
            , currency_code
            , creation_date
            , created_by
            , last_update_date
            , last_updated_by
            , style_id
            , Comments
            )
            values
            (
             l_interface_header_id
            , 'PO'
            , l_org_id
            , l_batch_id
            , 'NEW'
            , 'NEW'
            , 'PO'
            , 'STANDARD'
            , l_document_num
            , 'REQUISITION' -- 'DEFAULT'
            , l_vendor_id
            , l_vendor_site_id
            , l_agent_id
            , l_currency_code
            , sysdate
            , fnd_global.user_id
            , sysdate
            , fnd_global.user_id
            , 1
            , 'My description'
            );
           
            END IF; -- Only first time
           
            select po_lines_interface_s.nextval
            into   l_interface_line_id
            from   dual;

            l_shipment_num     := 1;
            l_line_num        := l_line_num + 1;
            --l_from_line_id    := i.blanket_po_line_num;
            l_promised_date    := null;
           
            IF i.blanket_po_line_num IS NOT NULL
            THEN
            BEGIN
              SELECT PO_Line_Id
              INTO   l_from_line_id
              FROM   PO_LINES_ALL L
              WHERE  L.Line_Num = i.blanket_po_line_num
              AND    L.PO_header_Id = i.blanket_po_header_id
              ;
             
              EXCEPTION
                WHEN Others THEN
                  l_from_line_id := null;
            END;
            END IF;
           
            p_l ('Link to blanket ' || i.blanket_po_header_id || ' line ' || i.blanket_po_line_num || ' with id ' || l_from_line_id);
           
            insert into po_lines_interface
            ( interface_header_id
            , interface_line_id
            , requisition_line_id
            , from_header_id
            , from_line_id
            , promised_date
            , creation_date
            , created_by
            , last_update_date
            , last_updated_by
            , line_num
            , shipment_num
            )
            values
            ( l_interface_header_id
            , l_interface_line_id
            , i.requisition_line_id
            , i.blanket_po_header_id
            , l_from_line_id
            , l_promised_date
            , sysdate
            , fnd_global.user_id
            , sysdate
            , fnd_global.User_id
            , l_line_num
            , l_shipment_num
            );
           
                       
            COMMIT;
        END LOOP Req;
       
        p_l ('Auto create PO nr of lines ' || l_nr_of_lines);
       
        IF l_nr_of_lines = 0
        THEN
           p_l ('ERROR: Requisition not approved.');
          x_error_msg := 'Cannot find lines on requisition ' || p_requisition_number || ' that are OPEN and APPROVED.';
          x_return_status := fnd_api.g_ret_Sts_error;
        ELSE
           
            po_interface_s.create_documents(p_api_version              => 1.0
                                   ,x_return_status            => x_return_status
                                   ,x_msg_count                => x_msg_count
                                   ,x_msg_data                 => x_msg_data
                                   ,p_batch_id                 => l_batch_id
                                   ,p_req_operating_unit_id    => l_org_id
                                   ,p_purch_operating_unit_id  => l_org_id
                                   ,x_document_id              => x_autocreated_doc_id
                                   ,x_number_lines             => x_num_lines_processed
                                   ,x_document_number          => x_document_num
                                   ,p_document_creation_method => 'CREATEDOC'
                                   ,p_orig_org_id              => l_org_id);
                                  
            x_po_number         := x_document_num;
            x_po_header_id    := x_autocreated_doc_id;
           
            p_l ('Auto create PObatch ' || l_batch_id || ' and org id ' || l_org_id);
            p_l ('Auto create PO' || x_document_num || ' status ' || x_return_status);
           
            IF x_return_status <> fnd_api.g_ret_sts_success
            THEN
              x_error_msg := x_msg_data;
              p_l ('Error creating PO: ' ||x_msg_data);
              DELETE FROM PO_HEADERS_INTERFACE WHERE INterface_Header_Id = l_batch_id;
              DELETE FROM PO_LINES_INTERFACE WHERE Interface_Header_Id = l_batch_id;
           
            END IF;
           
        END IF;
       
        p_l ('');
       
        END Auto_Create_PO;

  

Approve Purchase Order

So next step is to approve the purchase order.

 -------------------------------------------------------------------------------
      -- Approve the Purchase Order
      -------------------------------------------------------------------------------
      PROCEDURE Approve_PO
      (
        p_po_number  VARCHAR2
      )
      IS
        v_item_key   VARCHAR2 (100);

       CURSOR c_po_details
       IS
          SELECT pha.po_header_id,
                 pha.org_id,
                 pha.segment1,
                 pha.agent_id,
                 pdt.document_subtype,
                 pdt.document_type_code,
                 nvl (pha.authorization_status,'INCOMPLETE') authorization_status
            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 nvl (pha.authorization_status,'INCOMPLETE') IN
                        ('INCOMPLETE', 'REQUIRES REAPPROVAL')
                 AND segment1 = p_po_number;  -- Enter the Purchase Order Number

    BEGIN
     
       p_l ('Goedkeuring controleren voor order ' || p_po_number);
       <<PO>>
       FOR p_rec IN c_po_details
       LOOP

          mo_global.init (p_rec.document_type_code);
          mo_global.set_policy_context ('S', p_rec.org_id);

          SELECT p_rec.po_header_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
            INTO v_item_key
            FROM DUAL;
          
            p_l ('Goedkeuring workflow gestart ' || v_item_key);

        
           po_reqapproval_init1.start_wf_process (itemtype                 => 'POAPPRV',
                             itemkey                  => v_item_key,
                             workflowprocess          => 'POAPPRV_TOP',
                             actionoriginatedfrom     => 'PO_FORM',
                             documentid               => p_rec.po_header_id, -- po_header_id
                             documentnumber           => p_rec.segment1, -- Purchase Order Number
                             preparerid               => p_rec.agent_id, -- Buyer/Preparer_id
                             documenttypecode         => p_rec.document_type_code, --'PO'
                             documentsubtype          => p_rec.document_subtype, --'STANDARD'
                             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);
         COMMIT;
        
         END LOOP PO;
      END Approve_PO;



Receive on Purchase Order

And in our case we also wanted to perform a receipt on the PO just created.
First you insert a record in the receiving header interface.

insert into rcv_headers_interface
            (
            header_interface_id,
            group_id,
            vendor_id,
            vendor_site_id,
            receipt_num,
            receipt_header_id,
            asn_type,
            shipped_date,
            processing_status_code,
            receipt_source_code,
            transaction_type,           
            last_update_date,
            last_updated_by,
            last_update_login,
            creation_date,
            created_by,
            shipment_num,
            ship_to_organization_id,
            expected_receipt_date,
            num_of_containers,
            packing_slip,
            validation_flag,
            waybill_airbill_num
            )
            values
            (
            l_header_id,
            l_group_id,
            l_po_info.vendor_id,
            l_po_info.vendor_site_id,
            null,
            null,
            'ASN',
            sysdate,
            'PENDING',
            'VENDOR',
            'NEW',            
            sysdate,
            fnd_global.user_id,
            fnd_global.user_id,
            sysdate,
            fnd_global.user_id,
            l_shipment_num,
            l_po_info.ship_to_organization_id,
            sysdate,
            1,
            'SYSTEM',
            'Y',
            null
            );


Then for each line you like to receive

INSERT INTO rcv_transactions_interface
                (
                  Interface_transaction_id
                , Group_Id
                , Last_Update_Date
                , Last_Updated_By
                , Creation_Date
                , Created_By
                , Last_Update_Login
                , Transaction_Type
                , Transaction_Date
                , Processing_Status_Code
                , Processing_Mode_Code
                , Transaction_STatus_Code
                , Quantity
                , UOM_Code
                , Interface_Source_Code
                , Item_Id
                , Employee_Id
                --, Shipment_Header_Id
                --, Shipment_Line_Id
                , PO_Header_Id
                , PO_Line_Id
                , PO_Line_Location_Id
                , Receipt_Source_Code
                , To_Organization_Id
                , Source_Document_Code
                , Destination_Type_Code
                , Expected_Receipt_Date
                , Header_Interface_Id
                , Validation_Flag           
                , Attribute1
                )
                VALUES
                (
                  l_interface_trx_id
                , l_group_id
                , sysdate
                , fnd_global.user_id
                , sysdate
                , fnd_global.user_id
                , fnd_global.login_id
                , 'RECEIVE'
                , l_item_info.transaction_date
                , 'PENDING'
                , l_mode
                , 'PENDING'
                , 1
                , l_item_info.uom_code
                , 'RCV'
                , l_item_info.item_id
                , l_item_info.employee_id
                --, l_item_info.shipment_header_id
                --, l_item_info.shipment_line_id
                , l_item_info.po_header_id
                , l_item_info.po_line_id
                , l_item_info.po_line_location_id
                , l_item_info.Receipt_source_code
                , p_item_org_id
                , l_item_info.Source_Document_Code           
                , l_item_info.Destination_Type_Code
                , l_item_info.expected_Receipt_date
                , l_header_id
                , 'Y'
                , p_attribute1
                );



And finally run the transaction manager

        XXP4_VB_RECEIPTS_PKG.call_txn_manager ( p_group_id        => l_group_id
                                                   , p_error_code      => l_error_code
                                                   , p_error_message   => l_error_message
                                                   );

woensdag 18 november 2015

Script to compare processing time of specific concurrent programs

In some cases you need to check out performance of specific concurrent programs. You can use the following query to check for a specific program that runs daily.


SELECT c.USER_CONCURRENT_PROGRAM_NAME,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
            a.request_id
            ,a.parent_request_id
            ,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS') request_date
            ,To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS') actual_start_date
            ,  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS') actual_completion_date
            , round ((a.actual_completion_date-a.actual_start_date)*24*60*60) AS secs_to_run
            , round ((a.actual_start_date-a.request_date)*24*60*60) AS wait_in_secs
            ,d.user_name
            , a.phase_code
            ,a.status_code
            ,a.argument_text
            ,a.priority
FROM   fnd_concurrent_requests a,
            fnd_concurrent_programs b ,
            FND_CONCURRENT_PROGRAMS_TL c,
            fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id
            AND c.USER_CONCURRENT_PROGRAM_NAME='Process transaction interface'
            and c.language = 'US'
            ;



Or use the following to find programs that run for a long time (10 minutes) last week.


SELECT c.USER_CONCURRENT_PROGRAM_NAME,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
            a.request_id
            ,a.parent_request_id
            ,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS') request_date
            ,To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS') actual_start_date
            ,  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS') actual_completion_date
            , round ((a.actual_completion_date-a.actual_start_date)*24*60*60) AS secs_to_run
            , round ((a.actual_start_date-a.request_date)*24*60*60) AS wait_in_secs
            ,d.user_name
            , a.phase_code
            ,a.status_code
            ,a.argument_text
            ,a.priority
FROM   fnd_concurrent_requests a,
            fnd_concurrent_programs b ,
            FND_CONCURRENT_PROGRAMS_TL c,
            fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id 

            and c.language = 'US'
            and a.actual_start_date >= sysdate-7           
            and round ((a.actual_completion_date-a.actual_start_date)*24*60*60) > 60*10 -- more than 10 minutes
            ;

eBS R11/R12 Programmatically submit concurrent programs

Very often you need to submit a concurrent program in your code. Either in Forms or PL/SQL. The following code can be used as quick start to build this into your code.


Set Layout

If you want to run your program using BI Publisher and you want to add a specific layout, it's best to specify that in your coding using.
Language will be US or NL for example. Output format ofcourse depends on your program, but can be PDF.
The variable v_result_r is a boolean.

v_result_r := fnd_request.add_layout
         (
           template_appl_name => l_application_short_name
         , template_code      => l_my_template_code
         , template_language  => l_language
         , template_territory => null
         , output_format      => l_output_format
         );



Set Printer

If you want the output to be printed,you set the print options just before you submit your program.
The variable v_print_opt is a boolean.

 v_print_opt := fnd_request.set_print_options
                  ( PRINTER        => l_printer
                  , STYLE          => NULL
                  , COPIES         => l_nr_copies
                  , SAVE_OUTPUT    => TRUE
                  , PRINT_TOGETHER => 'N'
                  );  




Submit Program

After setting the print options and layout options you submit your program. You can use current time or have it wait a little if necessary.
The variable l_request_id is a number.

You can hard code the application short name or fetch it on forehand. Add this in a cursor or subprocedure for example

            select a.application_short_name
            into l_application_short_name
            from   fnd_application a
            , fnd_concurrent_Programs p
            where p.concurrent_program_name = l_conc_prog_name
            and p.application_id = a.application_id
            ;


Then submit your program

l_request_id := fnd_request.submit_request 
( application => l_application_short_name
, program     => l_conc_prog_name
, start_time  => sysdate
, sub_request => false
, argument1   => l_param1
, argument2   => l_param2
, ...
);   


-- Important to commit!! In forms you may want to set the 
-- message level very high to avoid getting a message about it.

v_message_level := :system.message_level;
:system.message_level := 20;


COMMIT;

:system.message_level := v_message_level;

-- Usually you also display a message if l_request_id = 0, 
-- because in that case it could not submit your program.
-- Either because it lost focus (lost login information)
-- or your program/application combination does not exist.


Wait for request to finish

In some cases you want to wait until your submitted program has finished. Either to display results or submit another program.
The dev_status returns ERROR, CANCELLED, TERMINATED in case of errors. You add a max wait to make sure your program does not keep waiting for ever. Don't wait for your current program (FND_GLOBAL.Conc_Request_Id) to finish ;-).

        l_phase                VARCHAR2(240);
        l_dev_phase            VARCHAR2(240);
        l_dev_status        VARCHAR2(240);
        l_message            VARCHAR2(240);
        l_conc_status        BOOLEAN;
        l_status            VARCHAR2(240);



 l_conc_status := APPS.FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id => l_request_id
,interval   => 5            -- Sleep 5 seconds between checks.
,max_wait   => 600           
,phase      => l_phase
,status     => l_status
,dev_phase  => l_dev_phase
,dev_status => l_dev_status
,message    => l_message
); 


Query to find programs run today

This query shows you all concurrent programs that have run today.

SELECT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM fnd_concurrent_programs_vl fcp,
       fnd_concurrent_requests    fcr,
       fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) = trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = USERENV ('LANG')
 ORDER BY fcr.request_date,
          fcr.request_id DESC;