0 Replies Latest reply on Dec 3, 2008 2:13 PM by 636485

    po_create_requisition_sv API

    636485
      Hi All,

      I am using the standard API po_create_requisition_sv.process_requisitions
      to create the purchase requisition. This API takes input parameter as a PL/SQL Table. I have made one anonymous block to use this API.

      When I run this block, I am getting invalid ROWID error. The code is pasted below..

      DECLARE
           v_header_rec po_create_requisition_sv.header_rec_type;

           v_line_table po_create_requisition_sv.line_tbl_type ;
           
           v_lt po_create_requisition_sv.line_tbl_type ;
      i BINARY_INTEGER :=0;
                

           v_requisition_header_id NUMBER;
           v_requisition_line_id NUMBER;
           
           l_api_version_number CONSTANT NUMBER := 1.0;
           l_Init_Msg_List VARCHAR2(100) := FND_API.G_TRUE ;
           l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
      l_msg_count NUMBER;
      l_msg_data VARCHAR2(2000);
      l_commit VARCHAR2(1) := FND_API.G_FALSE;
           
           v_cnt NUMBER;
           v_exists BINARY_INTEGER;
           
           CURSOR c1 IS
           SELECT po_requisition_headers_s.NEXTVAL req_header_id,po_requisition_lines_s.NEXTVAL req_line_id,188 category_id
                     -- ,'Heat exchanger main parts' item_Desc,'Each' uom,105 unit_price,3 qty,26086 del_to
                     -- ,27594 to_person_id
                     --,SYSDATE,13266,'VENDOR',-1,SYSDATE,13266,1062091,SYSDATE+10,1030,'EXPENSE',570
                FROM dual;
      BEGIN

      -- SELECT po_requisition_headers_s.NEXTVAL INTO v_Requisition_Header_Id
      -- FROM sys.dual;
      i := 0;
      FOR this IN c1
      LOOP
           i := i +1;
           dbms_output.put_line(i);
      SELECT
                          --v_Requisition_Header_Id
                          this.req_header_id           
                          ,27594--preparer_id               po_requisition_headers_all.preparer_id%TYPE
                          ,SYSDATE--last_update_date          po_requisition_headers_all. last_update_date%TYPE
                          ,13266--last_updated_by          po_requisition_headers_all.last_updated_by%TYPE
                          ,NULL--segment1               po_requisition_headers_all.segment1%TYPE
                          ,'N'--summary_flag               po_requisition_headers_all.summary_flag%TYPE
                          ,'Y'--enabled_flag               po_requisition_headers_all.enabled_flag%TYPE
                          ,NULL--segment2               po_requisition_headers_all.segment2%TYPE
                          ,NULL--segment3               po_requisition_headers_all.segment3%TYPE
                          ,NULL--segment4               po_requisition_headers_all.segment4%TYPE
                          ,NULL--segment5               po_requisition_headers_all.segment5%TYPE
                          ,NULL--start_date_active          po_requisition_headers_all.start_date_active%TYPE
                          ,NULL--end_date_active          po_requisition_headers_all.end_date_active%TYPE
                          ,-1--last_update_login          po_requisition_headers_all.last_update_login%TYPE
                          ,SYSDATE--creation_date               po_requisition_headers_all.creation_date%TYPE
                          ,13266--created_by               po_requisition_headers_all.created_by%TYPE
                          ,NULL--description               po_requisition_headers_all.description%TYPE
                          ,'INCOMPLETE'--authorization_status          po_requisition_headers_all.authorization_status%TYPE
                          ,NULL--note_to_authorizer          po_requisition_headers_all.note_to_authorizer%TYPE
                          ,'PURCHASE'--type_lookup_code          po_requisition_headers_all.type_lookup_code%TYPE
                          ,NULL--transferred_to_oe_flag          po_requisition_headers_all.transferred_to_oe_flag%TYPE
                          ,NULL--attribute_category          po_requisition_headers_all.attribute_category%TYPE
                          ,NULL--attribute1               po_requisition_headers_all.attribute1%TYPE
                          ,NULL--attribute2               po_requisition_headers_all.attribute2%TYPE
                          ,NULL--attribute3               po_requisition_headers_all.attribute3%TYPE
                          ,NULL--attribute4               po_requisition_headers_all.attribute4%TYPE
                          ,NULL--attribute5               po_requisition_headers_all.attribute5%TYPE
                          ,NULL--on_line_flag               po_requisition_headers_all.on_line_flag%TYPE
                          ,NULL--attribute6               po_requisition_headers_all.attribute6%TYPE
                          ,NULL--attribute7               po_requisition_headers_all.attribute7%TYPE
                          ,NULL--attribute8               po_requisition_headers_all.attribute8%TYPE
                          ,NULL--attribute9               po_requisition_headers_all.attribute9%TYPE
                          ,NULL--attribute10               po_requisition_headers_all.attribute10%TYPE
                          ,NULL--attribute11               po_requisition_headers_all.attribute11%TYPE
                          ,NULL--attribute12               po_requisition_headers_all.attribute12%TYPE
                          ,NULL--attribute13               po_requisition_headers_all.attribute13%TYPE
                          ,NULL--attribute14               po_requisition_headers_all.attribute14%TYPE
                          ,NULL--attribute15               po_requisition_headers_all.attribute15%TYPE
                          ,NULL--ussgl_transaction_code          po_requisition_headers_all.ussgl_transaction_code%TYPE
                          ,NULL--government_context          po_requisition_headers_all.government_context%TYPE
                          ,NULL--closed_code               po_requisition_headers_all.closed_code%TYPE
                          ,570--org_id                    org_organization_definitions.organization_id%TYPE
                          ,NULL--emergency_po_num          po_requisition_headers_all.emergency_po_num%TYPE
                          INTO v_header_rec FROM DUAL;
                          
                --SELECT po_requisition_lines_s.NEXTVAL
                -- INTO v_requisition_line_id
                -- FROM sys.dual;
                
                --v_line_table(i).requisition_header_id := this.req_header_id;
                
                SELECT
                          v_requisition_line_id           --po_requisition_lines_all.requisition_line_id%TYPE
                          ,this.req_header_id     --     po_requisition_headers_all.requisition_header_id%TYPE
                          ,1--line_num               po_requisition_lines_all.line_num%TYPE
                          ,1--line_type_id               po_requisition_lines_all.line_type_id%TYPE
                          ,188--category_id               mtl_categories.category_id%TYPE
                          ,'Heat exchanger main parts'--item_description          mtl_system_items.description%TYPE
                          ,'Each'--unit_meas_lookup_code          po_requisition_lines_all.unit_meas_lookup_code%TYPE
                          ,101--unit_price               po_requisition_lines_all.unit_price%TYPE
                          ,15--quantity               po_requisition_lines_all.quantity%TYPE
                          ,26086--deliver_to_location_id          po_requisition_lines_all.deliver_to_location_id%TYPE
                          ,27594--to_person_id               po_requisition_lines_all.to_person_id%TYPE
                          ,SYSDATE--last_update_date          po_requisition_lines_all.last_update_date%TYPE
                          ,13266--last_updated_by          po_requisition_lines_all.last_updated_by%TYPE
                          ,'VENDOR'--source_type_code          po_requisition_lines_all.source_type_code%TYPE
                          ,-1--last_update_login          po_requisition_lines_all.last_update_login%TYPE
                          ,SYSDATE--creation_date               po_requisition_lines_all.creation_date%TYPE
                          ,13266--created_by               po_requisition_lines_all.created_by%TYPE
                          ,1062091--item_id               po_requisition_lines_all.item_id%TYPE
                          ,NULL--item_revision               po_requisition_lines_all.item_revision%TYPE
                          ,NULL--quantity_delivered          po_requisition_lines_all.quantity_delivered%TYPE
                          ,NULL--suggested_buyer_id          po_requisition_lines_all.suggested_buyer_id%TYPE
                          ,NULL--encumbered_flag          po_requisition_lines_all.encumbered_flag%TYPE
                          ,NULL--rfq_required_flag          po_requisition_lines_all.rfq_required_flag%TYPE
                          ,SYSDATE +7 --need_by_date                po_requisition_lines_all.need_by_date%TYPE
                          ,NULL--line_location_id          po_requisition_lines_all.line_location_id%TYPE
                          ,NULL--modified_by_agent_flag          po_requisition_lines_all.modified_by_agent_flag%TYPE
                          ,NULL--parent_req_line_id          po_requisition_lines_all.parent_req_line_id%TYPE
                          ,NULL--justification               po_requisition_lines_all.justification%TYPE
                          ,NULL--note_to_agent               po_requisition_lines_all.note_to_agent%TYPE
                          ,NULL--note_to_receiver          po_requisition_lines_all.note_to_receiver%TYPE
                          ,NULL--purchasing_agent_id          po_requisition_lines_all.purchasing_agent_id%TYPE
                          ,NULL--document_type_code          po_requisition_lines_all.document_type_code%TYPE
                          ,NULL--blanket_po_header_id          po_requisition_lines_all.blanket_po_header_id%TYPE
                          ,NULL--blanket_po_line_num          po_requisition_lines_all.blanket_po_line_num%TYPE
                          ,NULL--currency_code               po_requisition_lines_all.currency_code%TYPE
                          ,NULL--rate_type               po_requisition_lines_all.rate_type%TYPE
                          ,NULL--rate_date               po_requisition_lines_all.rate_date%TYPE
                          ,NULL--rate                    po_requisition_lines_all.rate%TYPE
                          ,NULL--currency_unit_price          po_requisition_lines_all.currency_unit_price%TYPE
                          ,NULL--suggested_vendor_name          po_requisition_lines_all.suggested_vendor_name%TYPE
                          ,NULL--suggested_vendor_location     po_requisition_lines_all.suggested_vendor_location%TYPE
                          ,NULL--suggested_vendor_contact     po_requisition_lines_all.suggested_vendor_contact%TYPE
                          ,NULL--suggested_vendor_phone          po_requisition_lines_all.suggested_vendor_phone%TYPE
                          ,NULL--suggested_vendor_product_code     po_requisition_lines_all.suggested_vendor_product_code%TYPE
                          ,NULL--un_number_id               po_requisition_lines_all.un_number_id%TYPE
                          ,NULL--hazard_class_id          po_requisition_lines_all.hazard_class_id%TYPE
                          ,NULL--must_use_sugg_vendor_flag     po_requisition_lines_all.must_use_sugg_vendor_flag%TYPE
                          ,NULL--reference_num               po_requisition_lines_all.reference_num%TYPE
                          ,NULL--on_rfq_flag               po_requisition_lines_all.on_rfq_flag%TYPE
                          ,NULL--urgent_flag               po_requisition_lines_all.urgent_flag%TYPE
                          ,NULL--cancel_flag               po_requisition_lines_all.cancel_flag%TYPE
                          ,NULL--source_organization_id          org_organization_definitions.organization_id%TYPE
                          ,NULL--source_subinventory          po_requisition_lines_all.source_subinventory%TYPE
                          ,'PURCHASE'--destination_type_code          po_requisition_lines_all.destination_type_code%TYPE
                          ,1030--destination_organization_id     org_organization_definitions.organization_id%TYPE
                          ,NULL--destination_subinventory     po_requisition_lines_all.destination_subinventory%TYPE
                          ,NULL--quantity_cancelled          po_requisition_lines_all.quantity_cancelled%TYPE
                          ,NULL--cancel_date               po_requisition_lines_all.cancel_date%TYPE
                          ,NULL--cancel_reason               po_requisition_lines_all.cancel_reason%TYPE
                          ,NULL--closed_code               po_requisition_lines_all.closed_code%TYPE
                          ,NULL--agent_return_note          po_requisition_lines_all.agent_return_note%TYPE
                          ,NULL--changed_after_research_flag     po_requisition_lines_all.changed_after_research_flag%TYPE
                          ,NULL--vendor_id               po_vendors.vendor_id%TYPE
                          ,NULL--vendor_site_id               po_requisition_lines_all.vendor_site_id%TYPE
                          ,NULL--vendor_contact_id          po_requisition_lines_all.vendor_contact_id%TYPE
                          ,NULL--research_agent_id          po_requisition_lines_all.research_agent_id%TYPE
                          ,NULL--wip_entity_id               po_requisition_lines_all.wip_entity_id%TYPE
                          ,NULL--wip_line_id               po_requisition_lines_all.wip_line_id%TYPE
                          ,NULL--wip_repetitive_schedule_id     po_requisition_lines_all.wip_repetitive_schedule_id%TYPE
                          ,NULL--wip_operation_seq_num          po_requisition_lines_all.wip_operation_seq_num%TYPE
                          ,NULL--wip_resource_seq_num          po_requisition_lines_all.wip_resource_seq_num%TYPE
                          ,NULL--attribute_category          po_requisition_lines_all.attribute_category%TYPE
                          ,NULL--destination_context          po_requisition_lines_all.destination_context%TYPE
                          ,NULL--inventory_source_context     po_requisition_lines_all.inventory_source_context%TYPE
                          ,NULL--vendor_source_context          po_requisition_lines_all.vendor_source_context%TYPE
                          ,NULL--attribute1               po_requisition_lines_all.attribute1%TYPE
                          ,NULL--attribute2               po_requisition_lines_all.attribute2%TYPE
                          ,NULL--attribute3               po_requisition_lines_all.attribute3%TYPE
                          ,NULL--attribute4               po_requisition_lines_all.attribute4%TYPE
                          ,NULL--attribute5               po_requisition_lines_all.attribute5%TYPE
                          ,NULL--attribute6               po_requisition_lines_all.attribute6%TYPE
                          ,NULL--attribute7               po_requisition_lines_all.attribute7%TYPE
                          ,NULL--attribute8               po_requisition_lines_all.attribute8%TYPE
                          ,NULL--attribute9               po_requisition_lines_all.attribute9%TYPE
                          ,NULL--attribute10               po_requisition_lines_all.attribute10%TYPE
                          ,NULL--attribute11               po_requisition_lines_all.attribute11%TYPE
                          ,NULL--attribute12               po_requisition_lines_all.attribute12%TYPE
                          ,NULL--attribute13               po_requisition_lines_all.attribute13%TYPE
                          ,NULL--attribute14               po_requisition_lines_all.attribute14%TYPE
                          ,NULL--attribute15               po_requisition_lines_all.attribute15%TYPE
                          ,NULL--bom_resource_id          po_requisition_lines_all.bom_resource_id%TYPE
                          ,NULL--ussgl_transaction_code          po_requisition_lines_all.ussgl_transaction_code%TYPE
                          ,NULL--government_context          po_requisition_lines_all.government_context%TYPE
                          ,NULL--closed_reason               po_requisition_lines_all.closed_reason%TYPE
                          ,NULL--closed_date               po_requisition_lines_all.closed_date%TYPE
                          ,NULL--transaction_reason_code      po_requisition_lines_all.transaction_reason_code%TYPE
                          ,NULL--quantity_received          po_requisition_lines_all.quantity_received%TYPE
                          ,NULL--source_req_line_id          po_requisition_lines_all.source_req_line_id%TYPE
                          ,570--org_id                    po_requisition_lines_all.org_id%TYPE
                          ,NULL--kanban_card_id               po_requisition_lines_all.kanban_card_id%TYPE
                          ,NULL--catalog_type               po_requisition_lines_all.catalog_type%TYPE
                          ,NULL--catalog_source               po_requisition_lines_all.catalog_source%TYPE
                          ,NULL--manufacturer_id          po_requisition_lines_all.manufacturer_id%TYPE
                          ,NULL--manufacturer_name          po_requisition_lines_all.manufacturer_name%TYPE
                          ,NULL--manufacturer_part_number     po_requisition_lines_all.manufacturer_part_number%TYPE
                          ,NULL--requester_email          po_requisition_lines_all.requester_email%TYPE
                          ,NULL--requester_fax               po_requisition_lines_all.requester_fax%TYPE
                          ,NULL--requester_phone          po_requisition_lines_all.requester_phone%TYPE
                          ,NULL--unspsc_code               po_requisition_lines_all.unspsc_code%TYPE
                          ,NULL--other_category_code          po_requisition_lines_all.other_category_code%TYPE
                          ,NULL--supplier_duns               po_requisition_lines_all.supplier_duns%TYPE
                          ,NULL--tax_status_indicator          po_requisition_lines_all.tax_status_indicator%TYPE
                          ,NULL--pcard_flag               po_requisition_lines_all.pcard_flag%TYPE
                          ,NULL--new_supplier_flag          po_requisition_lines_all.new_supplier_flag%TYPE
                          ,NULL--auto_receive_flag          po_requisition_lines_all.auto_receive_flag%TYPE
                          ,NULL--tax_user_override_flag          po_requisition_lines_all.tax_user_override_flag%TYPE
                          ,NULL--tax_code_id               po_requisition_lines_all.tax_code_id%TYPE
                          ,NULL--note_to_vendor               po_requisition_lines_all.note_to_vendor%TYPE
                          ,NULL--oke_contract_version_id      po_requisition_lines_all.oke_contract_version_id%TYPE
                          ,NULL--oke_contract_header_id          po_requisition_lines_all.oke_contract_header_id%TYPE
                          ,NULL--item_source_id               po_requisition_lines_all.item_source_id%TYPE
                          ,NULL--supplier_ref_number          po_requisition_lines_all.supplier_ref_number%TYPE
                          ,NULL--source_doc_line_reference     NUMBER
                          ,NULL--uom_code               VARCHAR2(3)
                          ,NULL--order_type_lookup_code po_requisition_lines_all.order_type_lookup_code%TYPE
                          ,'GOODS'--purchase_basis po_requisition_lines_all.purchase_basis%TYPE
                          ,'QUANTITY'--matching_basis po_requisition_lines_all.matching_basis%TYPE                    
                INTO v_line_table(i)
                FROM dual;
                     
                
                /*
                v_line_table(1).line_type_id := 1;
                v_line_table(1).category_id :=188;
                v_line_table(1).item_description := 'Heat exchanger main parts';
                v_line_table(1).unit_meas_lookup_code := 'Each';
                v_line_table(1).unit_price := 105;
                v_line_table(1).quantity := 3;
                v_line_table(1).deliver_to_location_id :=26086;
                v_line_table(1).to_person_id := 27594;
                v_line_table(1).last_update_date := SYSDATE;
                v_line_table(1).last_updated_by := 13266;
                v_line_table(1).source_type_code := 'VENDOR';
                v_line_table(1).last_update_login := -1;
                v_line_table(1).creation_date := SYSDATE;
                v_line_table(1).created_by := 13266;
                v_line_table(1).item_id := 1062091;
                v_line_table(1).need_by_date := SYSDATE + 10;
                v_line_table(1).destination_organization_id := 1030;
                v_line_table(1).destination_type_code := 'PURCHASE';
                v_line_table(1).org_id := 570;     */

                
      --          dbms_output.put_line(v_line_table(i).requisition_header_id);

                v_exists := v_line_table.LAST;
                
                dbms_output.put_line(v_line_table(i).created_by);
                
                IF v_line_table.EXISTS(i) THEN
                dbms_output.put_line('v_exists');
                END IF;
                
                --v_lt := v_line_table(i);

                     
                     po_create_requisition_sv.process_requisition (px_header_rec => v_header_rec
           ,px_line_table => v_line_table
           ,x_return_status => l_return_status
           ,x_msg_count => l_msg_count
           ,x_msg_data => l_msg_data
      );
                                                                       
      dbms_output.put_line(l_return_status || l_msg_data);
                
                END LOOP;
                
      EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      END;