Tuesday, July 8, 2014

Oracle Purchase Order Single Insertion Script

CREATE OR REPLACE PROCEDURE XXAA_PO_SINGLE_INSERT
AS
BEGIN
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO HEADERS INTERFACE TABLE<---------
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_headers_interface (interface_header_id,
                                     batch_id,
                                     action,
                                     document_type_code,
                                     vendor_id,
                                     vendor_site_id,
                                     org_id,
                                     currency_code,
                                     agent_id,
                                     ship_to_location_id,
                                     bill_to_location_id,
                                     approved_date,
                                     creation_date,
                                     created_by,
                                     effective_date)
        VALUES (po_headers_interface_s.NEXTVAL,         --interface_header_id,
                4321,                                              -- batch_id
                'ORIGINAL',                                          -- action
                'STANDARD',                              -- document_type_code
                3921,                                             -- vendor_id
                4715,                                        -- vendor_site_id
                204,                                                 -- org_id
                'USD',                                        -- currency_code
                29,                                                -- agent_id
                207,                                    -- ship_to_location_id
                204,                                    -- bill_to_location_id
                SYSDATE,                                      -- approved_date
                SYSDATE,                                      -- creation_date
                1007939,                                         -- created_by
                SYSDATE                                       --effective_date
                       );
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
             --------> INSERT INTO LINES INTERFACE <---------
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_lines_interface (interface_line_id,
                                   interface_header_id,
                                   line_type,
                                   line_num,
                                   unit_of_measure,
                                   quantity,
                                   unit_price,
                                   item_id,
                                   item_description,
                                   ship_to_location_id,
                                   ship_to_organization_id,
                                   creation_date,
                                   created_by,
                                   shipment_num,
                                   category_id,
                                   promised_date)
        VALUES (po_lines_interface_s.NEXTVAL,             -- interface_line_id
                po_headers_interface_s.CURRVAL,         -- interface_header_id
                'Goods',                                          -- line_type
                1,                                                 -- line_num
                'Each',                                     -- unit_of_measure
                10,                                                -- quantity
                100,                                             -- unit_price
                2155,                                               -- item_id
                'Sentinel Standard Desktop - Rugged',      -- item_description
                209,                                    -- ship_to_location_id
                209,                                -- ship_to_organization_id
                SYSDATE,                                      -- creation_date
                1007939,                                         -- created_by
                1,                                             -- shipment_num
                1,                                              -- category_id
                SYSDATE + 10                                   --promised_date
                );

--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO DISTRIBUTIONS INTERFACE TABLE<---------
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_distributions_interface (interface_distribution_id,
                                           interface_line_id,
                                           interface_header_id,
                                           distribution_num,
                                           quantity_ordered,
                                           destination_type_code,
                                           accrue_on_receipt_flag,
                                           creation_date,
                                           created_by,
                                           org_id)
        VALUES (po_distributions_interface_s.NEXTVAL, --interface_distribut_id
                po_lines_interface_s.CURRVAL,             -- interface_line_id
                po_headers_interface_s.CURRVAL,         -- interface_header_id
                1,                                         -- distribution_num
                10,                                        -- quantity_ordered
                'INVENTORY',                          -- destination_type_code
                'N',                                 -- accrue_on_receipt_flag
                SYSDATE,                                      -- creation_date
                1007939,                                          --created_by
                204                                                   --org_id
                );

   COMMIT;
------------------------------------------------------------------------------
END XXAA_PO_SINGLE_INSERT;
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

BEGIN
   XXAA_PO_SINGLE_INSERT;
END;
/
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

RUN PROGRAME -->:::  Import Standard Purchase Orders  :::<--

            Create or update Items         :  No
            Approval Status            :  Approved
            Batch Id            :  ********

-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect