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 datal_batch_id   po_headers_interface.batch_id%TYPE := 100;

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

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

--Contains Vendor IDl_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 IDl_ship_to_location_id    po_headers_interface.ship_to_location_id%TYPE := 204;

--Contains Bill to Location IDl_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 userl_attribute1             po_headers_interface.attribute1%TYPE := 'SBONTALA';

--Contains document subtypel_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 informationl_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 Iteml_unit_price             po_lines_interface.unit_price%TYPE:= 100;

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

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

--Contains ship to organization idl_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 createdl_header_count   NUMBER := 1;

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

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

--To track progressl_progress       VARCHAR2(10) ;

BEGIN

--Header LoopFOR hdr_cnt IN 1..L_header_countLOOP ---- 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 loopinsert 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