R12 create new quotation

R12.2.3以上：

DECLARE

------------------------------------------------------------------------------------
--Define Mandatory column variables to insert into interface tables. Values to all
-- Variables need to be intialized with required data before running the script.
------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
--Header level information
---------------------------------------------------------------------------------
--This provides batch id which is used to run particular set of data
l_batch_id   po_headers_interface.batch_id%TYPE := 100;

--Contains Organization ID
l_org_id   po_headers_interface.org_id%TYPE := 204;

--Contains Agent ID for which PO is created
l_agent_id   po_headers_interface.agent_id%TYPE := 25;

--Contains Vendor ID
l_vendor_id   po_headers_interface.vendor_id%TYPE := 21;

--Contains Vendor Site ID
l_vendor_site_id  po_headers_interface.vendor_site_id%TYPE := 41;

--Contains Ship to Location ID
l_ship_to_location_id    po_headers_interface.ship_to_location_id%TYPE := 204;

--Contains Bill to Location ID
l_bill_to_location_id    po_headers_interface.bill_to_location_id%TYPE := 204;

-- Contains Attribute value which can be your username
-- which can be used to check records inserted by the user
l_attribute1             po_headers_interface.attribute1%TYPE := 'SBONTALA';

--Contains document subtype
l_document_subtype        po_headers_interface.document_subtype%TYPE := 'STANDARD';

---------------------------------------------------------------------------------
--Line level information
---------------------------------------------------------------------------------
--Contains Line type
l_line_type              po_lines_interface.line_type%TYPE := 'Goods';

--Contains Item information
l_item                   po_lines_interface.item%TYPE := 'AS10000';

--Specifies UOM code
l_uom_code               po_lines_interface.uom_code%TYPE := 'Ea';

--Contains Unit price of the Item
l_unit_price             po_lines_interface.unit_price%TYPE:= 100;

--Specifies the price override for the item
l_price_override         po_line_locations_interface.price_override%TYPE := 10;

--Specified the quantity for the price break
l_quantity               po_line_locations_interface.quantity%TYPE := 100;

--Contains ship to organization id
l_ship_to_organization_id  po_line_locations_interface.ship_to_organization_id%TYPE := 204;

l_unit_of_measure          po_line_locations_interface.unit_of_measure%TYPE := 'Each';

-- Specifies number of Purchase order to be created
l_header_count   NUMBER := 1;

--Specifies number of lines to be created per PO
l_line_count     NUMBER := 1;

--Specifies number of price breaks to be created per Line
L_prc_brk_count  NUMBER := 1;

--To track progress
l_progress       VARCHAR2(10) ;

BEGIN

--Header Loop
FOR hdr_cnt IN 1..L_header_count
LOOP
 ---- Inserting into header interface table
 l_progress := '001';
   Insert into PO.PO_HEADERS_INTERFACE
    (INTERFACE_HEADER_ID,
     BATCH_ID, 
     PROCESS_CODE,
     ACTION,  
     ORG_ID,
     DOCUMENT_TYPE_CODE,  
     DOCUMENT_SUBTYPE ,
     CURRENCY_CODE,  
     AGENT_ID,  
     VENDOR_ID, 
     VENDOR_SITE_ID,  
     SHIP_TO_LOCATION_ID, 
     BILL_TO_LOCATION_ID, 
     ATTRIBUTE1,   
     CREATION_DATE)
  VALUES
  ( po_headers_interface_s.NEXTVAL, --- INTERFACE_HEADER_ID,
     l_batch_id, ---     BATCH_ID,  
     'PENDING', ---     PROCESS_CODE,
     'ORIGINAL', ---     ACTION,  
     l_org_id, ---     ORG_ID,
     'QUOTATION', ---     DOCUMENT_TYPE_CODE, 
     l_document_subtype,
     'USD',  ---     CURRENCY_CODE,
     l_agent_id,         ---   AGENT_ID, 
     l_vendor_id, ---   VENDOR_ID,
     l_vendor_Site_id,  ---   VENDOR_SITE_ID,
     l_ship_to_location_id, --- SHIP_TO_LOCATION_ID,
     l_bill_to_location_id, --- BILL_TO_LOCATION_ID,
     l_attribute1, ---     ATTRIBUTE1,
     SYSDATE ); ---     CREATION_DATE,          

        ---Line Loop
          FOR line_cnt IN 1..l_line_count LOOP
         
       l_progress := '002';
     --- Inserting into Lines interface table
                l_progress := '002';
  Insert into PO.PO_LINES_INTERFACE
     (INTERFACE_LINE_ID,
      INTERFACE_HEADER_ID,
      ACTION,  
      LINE_NUM,     
      LINE_TYPE,   
      ITEM,   
      UOM_CODE, 
      UNIT_PRICE,    
      SHIP_TO_ORGANIZATION_ID,  
      SHIP_TO_LOCATION_ID,
      NEED_BY_DATE,
      PROMISED_DATE,     
      CREATION_DATE,   
      LINE_LOC_POPULATED_FLAG)
   Values
     (po_lines_interface_s.nextval,  --- INTERFACE_LINE_ID,
      po_headers_interface_s.currval,  --- INTERFACE_HEADER_ID,
     'ADD',  ---     ACTION,  
      line_cnt,   ---     LINE_NUM,     
      l_line_type, ---     LINE_TYPE,  
      l_item,  ---     ITEM,  
      l_uom_code,  ---     UOM_CODE, 
      l_unit_price,---     UNIT_PRICE,   
      l_ship_to_organization_id,  ---     SHIP_TO_ORGANIZATION_ID,   
      l_ship_to_location_id,   ---    SHIP_TO_LOCATION_ID,
      SYSDATE,  ---     NEED_BY_DATE,
      SYSDATE,   ---     PROMISED_DATE,
      SYSDATE,   ---     CREATION_DATE,    
      'Y');  ---     LINE_LOC_POPULATED_FLAG,
           
     --Price Break Loop
     FOR prc_brk_cnt IN 1..L_prc_brk_count LOOP
             
      --Inserting into line location interface table
      Insert into PO.PO_LINE_LOCATIONS_INTERFACE
               (INTERFACE_LINE_LOCATION_ID,
                INTERFACE_HEADER_ID,
                INTERFACE_LINE_ID,             
                SHIPMENT_TYPE,
                SHIPMENT_NUM,
                SHIP_TO_ORGANIZATION_ID,              
                SHIP_TO_LOCATION_ID,                
                QUANTITY,      
  UNIT_OF_MEASURE,
  PRICE_OVERRIDE,
                CREATION_DATE)
             Values
               (po_line_locations_interface_s.nextval,---    INTERFACE_LINE_LOCATION_ID,
                po_headers_interface_s.currval,    ---        INTERFACE_HEADER_ID,
                po_lines_interface_s.currval,    ---        INTERFACE_LINE_ID,            
                'QUOTATION',    ---        SHIPMENT_TYPE,
                prc_brk_cnt,    ---        SHIPMENT_NUM,
                l_ship_to_organization_id,    ---        SHIP_TO_ORGANIZATION_ID,              
                l_ship_to_location_id,    ---        SHIP_TO_LOCATION_ID,  
                l_quantity,    ---        QUANTITY,    
  l_unit_of_measure,
  l_price_override,
                SYSDATE);    ---        CREATION_DATE,

     END LOOP; --End of Price break loop
         END LOOP; --End of line loop
  END LOOP; --End of header loop

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error while inserting data at :'||l_progress||SQLCODE||SQLERRM);

END;

 

R12.1.

 

for rec_qtn in  qtn_cursor loop
insert into po_headers_interface
     (interface_header_id,
      PO_HEADER_ID,
      BATCH_ID,
      action,
      org_id,
      document_type_code,
      vendor_id,
      vendor_site_code,
      vendor_site_id,
      effective_date,
      document_num,
      load_sourcing_rules_flag,
      AGENT_ID,  
      PROCESS_CODE
   )
SELECT po_headers_interface_s.nextval,A.*
  from
      (select
             DISTINCT
             B.PO_HEADER_ID,
             p_batch_id  BATCH_ID,
             'UPDATE' ACTION,---------------------- 'ORIGINAL' New quoation, 'UPDATE' Update
             b.org_id OU_ID,
             'QUOTATION'DOCUMENT_TYPE_CODE,
             B.VENDOR_ID,
             C.VENDOR_SITE_CODE,
             B.VENDOR_SITE_ID,
             b.start_date EFFECTIVE_DATE,
             A.Qtn_no,'N',
             b.agent_id,---------------------- buyer
            'PENDING' PROCESS_CODE
        from
             PO_VENDOR_SITES_ALL C,
             po_headers_all b,
             TEMP  a
       where
             B.VENDOR_SITE_ID=C.VENDOR_SITE_ID AND
             B.VENDOR_ID=C.VENDOR_ID AND
             b.type_lookup_code='QUOTATION' AND
             b.org_id= V_ou_id and          ------------ OU ID
             a.qtn_No=b.segment1 and
             a.qtn_No=rec_qtn.qtn_no---------------------Original quotatin No
       ) A;
      

 

 

-----------------------------------insert into po_lines_interface

insert into po_lines_interface
  (interface_line_id,
   interface_header_id,
   action,
   item,
   item_revision,
   --      item_description,
   unit_price,
   unit_of_measure,
   effective_date,
   template_name,
   quantity,
   VENDOR_PRODUCT_NUM,
   shipment_attribute8,   ---------------------------- Update standard cost
   line_attribute14       ---------------------------- Update sign
   ) 
  SELECT po_lines_interface_s.nextval,
         po_headers_interface_s.currval,
         'UPDATE' ACTION,  -------------------------------------- 'ORIGINAL' New quoation, 'UPDATE' Update
         A.ITEM_no,
         (select max(revision) revision
                from apps.mtl_item_revisions_b B
               where effectivity_date <= sysdate
                 AND B.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                 AND B.ORGANIZATION_ID = msi.organization_id) REVISION,
         A.Qtn_PRICE,
         MSI.PRIMARY_UNIT_OF_MEASURE,
         TRUNC(SYSDATE) EFFECTIVE_DATE,
         'Purchased Item',
         1,
         a.VENDOR_PRODUCT_NUM,
         'Standard Cost',  ---------------------------- Update standard cost
         'Convert from'||p_batch_id  ---------------------------- Update sign
       
    from apps.MTL_SYSTEM_ITEMS_B  MSI,TEMP a
   WHERE msi.organization_id =V_organization_id
     AND A.ITEM_no = msi.segment1
     and a.qtn_No = rec_qtn.qtn_No