Friday, July 3, 2020

API to update Blanket Purchase Agreement in Oracle Apps R12 (PO_PDOI_GRP.CATALOG_UPLOAD)













This is an API used by Oracle internal programs to update the catalog using “iProcurement Catalog Administration”.

  1. In this case, whenever a change was made using this API, it resides in draft tables and a change summary notification is sent to buyer of the BPA.
  2. The buyer can view the changes made from notification and approve or reject the notification.
  3. If approved, the changes will reflect in PO_HEADERS_ALL, PO_LINES_ALL etc tables and new revision is created.
  4. If rejected, the changes in draft tables will be stamped has "REJECTED"
  5. Additionally “PO_PDOI_GRP” is the same package the standard concurrent program “Import Price Catalogs” programs calls
  6. In order to this API to work, the BPA should be given access to CATALOG_ADMIN for update
  7. This API takes inputs from standard BPA Open Interface tables like PO_HEADERS_INTERFACE , PO_LINES_INTERFACE etc

API: PO_PDOI_GRP.CATALOG_UPLOAD

Tested Instance: R12

Script:

SET DEFINE OFF;
SET SERVEROUTPUT ON;
DECLARE 
  ln_batch_id   NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSDATE,'HHMISSSS'));
  
  PROCEDURE submit_catalog_change
             (piv_approval_status  IN  VARCHAR2,
              pin_batch_id         IN  NUMBER,
              pin_org_id           IN  NUMBER,
              pin_intf_file_id     IN  NUMBER
             ) 
  IS                             
   ln_processed_lines_count     NUMBER;
   ln_rejected_lines_count      NUMBER;
   lv_err_tolerance_exceeded    VARCHAR2(2000);
   lv_return_status             VARCHAR2(1);
   lv_error_message             VARCHAR2(2000);
   lv_msg                       VARCHAR2(2000);
   ln_total_processed_lines     NUMBER;
   ln_total_failed_lines        NUMBER;
   
   lv_cutom_status              VARCHAR2(200);
   lv_custom_error_message      VARCHAR2(2000);
  
  BEGIN  
    dbms_output.put_line('ln_batch_id: '||ln_batch_id);
    
    FOR intf_rec IN ( SELECT * 
                        FROM po_headers_interface 
                       WHERE batch_id = pin_batch_id
                         AND org_id   = pin_org_id
                     )
    LOOP
      dbms_output.put_line('Processing Interface_HEADER_ID :'
                           ||intf_rec.interface_header_id
                           ||' with Interface Code = '
                           ||intf_rec.interface_source_code
                           );  
                           
      ln_processed_lines_count     := NULL;
      ln_rejected_lines_count      := NULL;
      lv_err_tolerance_exceeded    := NULL;
    
      PO_PDOI_GRP.CATALOG_UPLOAD
           (p_api_version                => 1.0,
            p_init_msg_list              => fnd_api.g_true,
            p_validation_level           => fnd_api.g_valid_level_full,
            p_commit                     => fnd_api.g_true,
            x_return_status              => lv_return_status,
            x_error_message              => lv_error_message,
            p_gather_intf_tbl_stat       => fnd_api.g_false,
            p_selected_batch_id          => pin_batch_id,
            p_batch_size                 => po_pdoi_constants.g_def_batch_size,
            p_buyer_id                   => NULL,
            p_document_type              => 'BLANKET',
            p_document_subtype           => NULL,
            p_create_items               => 'N',
            p_create_sourcing_rules_flag => 'N',
            p_rel_gen_method             => NULL,
            p_sourcing_level             => NULL,
            p_sourcing_inv_org_id        => NULL,
            p_approved_status            => NVL(piv_approval_status,'INITIATE APPROVAL'),
            p_process_code               => po_pdoi_constants.g_process_code_pending,
            p_interface_header_id        => intf_rec.interface_header_id,
            p_org_id                     => pin_org_id,
            p_ga_flag                    => 'Y',
            p_submit_dft_flag            => 'Y',
            p_role                       => 'CAT_ADMIN',
            p_catalog_to_expire          => NULL,
            p_err_lines_tolerance        => 1000,
            x_processed_lines_count      => ln_processed_lines_count,
            x_rejected_lines_count       => ln_rejected_lines_count,
            x_err_tolerance_exceeded     => lv_err_tolerance_exceeded
            );
   
      IF (lv_return_status <> FND_API.G_RET_STS_SUCCESS) 
      THEN   
        FOR i IN 1..FND_MSG_PUB.COUNT_MSG 
        LOOP
          lv_msg := FND_MSG_PUB.get( p_msg_index => i,
                                     p_encoded   => FND_API.G_FALSE
                                   );    
          dbms_output.put_line( 'The API call failed with error '||lv_msg);
          lv_custom_error_message := lv_custom_error_message||lv_msg||';';
         END LOOP;       
         lv_cutom_status := 'ERROR';
         lv_custom_error_message := lv_custom_error_message
                                    ||'Processed: '||ln_processed_lines_count
                                    ||'. Error:'||ln_rejected_lines_count;       
      ELSE
        lv_cutom_status := 'PROCESSED';
        lv_custom_error_message := NULL;
        dbms_output.put_line( 'The API call ended with SUCESSS status');
      END IF;
   
      dbms_output.put_line('processed_lines_count := '||ln_processed_lines_count);
      dbms_output.put_line('rejected_lines_count := '||ln_rejected_lines_count);
      dbms_output.put_line('err_tolerance_exceeded := '||lv_err_tolerance_exceeded);      
    END LOOP;   
    COMMIT;
  
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Others exception. Error: ' ||
                         SQLERRM);
  END submit_catalog_change;  
  
BEGIN  

  -- Initialize enviroment
  mo_global.init('PO');
  mo_global.set_policy_context('S',14);
  fnd_global.apps_initialize(1112,51234,201);

  -- Create Header Data 
   Insert into PO.PO_HEADERS_INTERFACE
       (interface_header_id, 
        batch_id,    
        interface_source_code,
        po_header_id,
        document_num,
        vendor_id,
        vendor_site_id,
        org_id,
        agent_id,
        process_code,
        action,  
        creation_date)
     VALUES 
     ( po_headers_interface_s.NEXTVAL,  --- interface_header_id, 
       ln_batch_id,  --- batch_id,     
       po_pdoi_constants.g_call_mod_catalog_upload,
       93063, -- po header id
       '40010129', -- bpa number
       22, -- vendor id 
       30, -- vendor site id 
       84,  -- org_id 
       108,-- agent id 
       'PENDING',  --- process_code,
       'UPDATE', ---action, 
       SYSDATE 
     );  
        
   -- Create Line 1 
   INSERT INTO PO.PO_LINES_INTERFACE
           (interface_line_id,
            interface_header_id,
            action, 
            po_line_id,     
            po_header_id,
            document_num,           
            line_num,          
            line_type,        
            item,   
            item_description,
            vendor_product_num,
            category_id,            
            uom_code,  
            unit_price,        
            ship_to_organization_id,         
            ship_to_location_id,                    
            creation_date,      
            line_loc_populated_flag)
         VALUES
           (po_lines_interface_s.NEXTVAL,    
            po_headers_interface_s.CURRVAL, 
           'UPDATE',    ---  action, 
            282349,
            93063,
            '40010129',           
            9,   ---line_num,          
            'Goods', --- line_type,      
            NULL,    --- item,        
            'test item 9', -- item_description
            'vendor_item',-- Vendor product Num 
            3129,-- catagory_id
            'EA', --uom_code,   
            30, -- unit_price,       
            NULL,  --- ship_to_organization_id,          
            NULL,   --- ship_to_location_id,
            SYSDATE,  --- creation_date,        
            'N'
            );  
            
     COMMIT;       
            
    submit_catalog_change(piv_approval_status  => 'INITIATE APPROVAL' ,
                          pin_batch_id         => ln_batch_id,
                          pin_org_id           => 84,
                          pin_intf_file_id     => NULL
                         );         

END;

0 Responses to “API to update Blanket Purchase Agreement in Oracle Apps R12 (PO_PDOI_GRP.CATALOG_UPLOAD)”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search