Nis
6
2016

Sipariş Kopyalama İşlemi

Yazar Mustafa Korkmaz Yorumlar

Merhaba,

Bu yazımda standart fonksiyonalitede olan siparişin bazı niteliklerinin değiştirilerek veya değiştirilmeden doğrudan kopyalanması işleminin script olarak örneğini bulabilirsiniz.

Aşağıdaki script ; belirlenmiş siparişlerin ilgili işlem kodu ile RMA (return material authorization) siparişine kopyalanması örneğidir .

 
DECLARE
--***********************************************************************************************
--> constant variable ...
   l_hdr_type_id          NUMBER                                      := 8507;
                                                      --> sipariş tipi başlık
   l_line_type_id         NUMBER                                      := 8506;
                                                       --> sipariş satır tipi
   l_orj_header_id        NUMBER;
   l_return_reason_code   ont.oe_order_lines_all.return_reason_code%TYPE
                                                            := 'CANCELLATION'; --> iade oluşturma nedeni
--***********************************************************************************************
   l_dummy                NUMBER;
   l_line_count           NUMBER;
   l_line_list            VARCHAR2 (2400);
   l_hdr_list             VARCHAR2 (2400);
   l_message              VARCHAR2 (2400);
   l_new_order            ont.oe_order_headers_all%ROWTYPE;
   l_query_header_id      NUMBER;
   l_return_status        VARCHAR2 (1);
   l_msg_count            NUMBER;
   p_msg_count            NUMBER;
   l_msg_data             VARCHAR2 (2000);
   l_copy_rec             oe_order_copy_util.copy_rec_type
                                           := oe_order_copy_util.get_copy_rec;
   l_copy_f_rec           oe_order_copy_util.copy_rec_type
                                           := oe_order_copy_util.get_copy_rec;
   l_msg                  VARCHAR2 (2000);
 
   CURSOR crs_order
   IS
      --> kopyalanmak istenen siparişlerin listesi
      SELECT oeh.header_id
        FROM oe_order_headers_all oeh
       WHERE 1 = 1
         AND oeh.org_id = 1634
         AND oeh.order_number IN
                (101988, 102641, 102750, 102233, 102770, 102827, 102852
                ,102853, 102855, 102856, 102882, 102879, 102878, 102886
                ,102883, 102885, 102979, 102983, 102997, 102996, 102984
                ,102981, 102982, 103250, 103324, 103358, 103356, 103357
                ,103351, 103364, 103348, 103354, 103367, 103349, 103353
                ,103352, 103347, 103359, 103360, 103355, 103365, 103448
                ,104310, 104306, 104304, 104301, 104305, 104300, 104299
                ,104308, 104347, 104345, 104350, 104343, 104338, 104340
                ,104344, 104346, 104399, 104438, 104437, 104432, 104400
                ,104439, 104441, 104455, 104440, 104442, 104456, 104454
                ,104540, 104529, 104542, 104534, 104546, 104531, 104532
                ,104536, 104533, 104541, 104538, 104547, 104537, 104545
                ,104522, 104528, 104525, 104524, 104574, 104526, 104521
                ,104527, 104571, 104523, 104573, 104520, 104580, 104594
                ,104595, 104590, 104605, 104592, 104593, 104601, 104603
                ,104604);
BEGIN
--***********************************************************************************************
   fnd_global.apps_initialize (0, 67425, 660);
 
--***********************************************************************************************
   FOR rec IN crs_order
   LOOP
--***********************************************************************************************
      l_copy_rec                            := l_copy_f_rec;
--***********************************************************************************************
      l_line_count                          := 0;
      l_message                             := NULL;
      l_return_status                       := NULL;
      l_query_header_id                     := NULL;
      l_msg_data                            := NULL;
      l_line_list                           := NULL;
      l_hdr_list                            := NULL;
      l_msg                                 := NULL;
      l_hdr_type_id                         := 0;
      l_line_type_id                        := 0;
--***********************************************************************************************
      l_orj_header_id                       := rec.header_id;
 
--**********************************************************************************************
      --> cancelled olmayan sipariş satırları kopyalanmak üzere toplanır !
      FOR rec IN (SELECT *
                    FROM ont.oe_order_lines_all
                   WHERE 1 = 1
                     AND cancelled_flag = 'N'
                     AND header_id = l_orj_header_id)
      LOOP
         l_line_count    := l_line_count + 1;
         l_line_list     := l_line_list || ',' || rec.line_id;
      END LOOP;
 
--***********************************************************************************************
      l_line_list                           := LTRIM (l_line_list, ',');
      l_hdr_list                            := TO_CHAR (l_orj_header_id);
--***********************************************************************************************
      DBMS_OUTPUT.put_line ('l_line_list : ' || l_line_list);
      DBMS_OUTPUT.put_line ('l_hdr_list  : ' || l_hdr_list);
      DBMS_OUTPUT.put_line ('l_line_count  : ' || l_line_count);
--***********************************************************************************************
/* T(true) veya F(false) */
      l_copy_rec.api_version_number         := 1;
      l_copy_rec.init_msg_list              := 'T';
      l_copy_rec.COMMIT                     := 'F';
      l_copy_rec.copy_order                 := 'T';
      l_copy_rec.hdr_count                  := 1;
                          --> her api call edildiğinde bir header kopyalanacak
      l_copy_rec.hdr_list                   := l_hdr_list;
      l_copy_rec.expiration_date            := NULL;
      l_copy_rec.transaction_name           := NULL;
      l_copy_rec.copy_transaction_name      := 'T';
      l_copy_rec.copy_expiration_date       := 'F';
      l_copy_rec.version_number             := 0;
      l_copy_rec.line_version_number        := 0;
      l_copy_rec.append_to_header_id        := NULL;
      l_copy_rec.hdr_info                   := 'F';
      l_copy_rec.hdr_type                   := l_hdr_type_id;
      l_copy_rec.hdr_descflex               := 'T';
      l_copy_rec.hdr_credit_card_details    := 'F';
      l_copy_rec.hdr_scredits               := 'T';
      l_copy_rec.hdr_attchmnts              := 'T';
      l_copy_rec.hdr_holds                  := 'T';
      l_copy_rec.manual_order_number        := NULL;
      l_copy_rec.manual_quote_number        := NULL;
      l_copy_rec.hdr_payments               := 'T';
      l_copy_rec.all_lines                  := 'F';
      l_copy_rec.line_count                 := l_line_count;
      l_copy_rec.line_list                  := l_line_list;
      l_copy_rec.line_type                  := l_line_type_id;
      l_copy_rec.incl_cancelled             := 'F';
      l_copy_rec.line_price_mode            := 1;
      l_copy_rec.line_price_date            := NULL;
      l_copy_rec.line_discount_id           := NULL;
      l_copy_rec.line_descflex              := 'F';
      l_copy_rec.line_holds                 := 'F';
      l_copy_rec.line_scredits              := 'F';
      l_copy_rec.line_attchmnts             := 'T';
      l_copy_rec.line_payments              := 'F';
      l_copy_rec.return_reason_code         := l_return_reason_code;
--> kopya ile yeni oluşturalacak sipariş return tipli ise return_reason_code zorunludur !
      l_copy_rec.default_null_values        := 'F';
      l_copy_rec.new_phase                  := 'F';
      l_copy_rec.version_reason_code        := NULL;
      l_copy_rec.comments                   := NULL;
      l_copy_rec.phase_change_flag          := 'T';
      l_copy_rec.line_phase_change_flag     := 'T';
      l_copy_rec.phase_change_flag          := NULL;
      l_copy_rec.version_number             := NULL;
      l_copy_rec.copy_complete_config       := 'T';
      l_copy_rec.source_block_type          := 'LINE';
--**********************************************************************************************
      oe_order_copy_util.copy_order (p_copy_rec           => l_copy_rec
                                    ,x_header_id          => l_query_header_id
                                    ,x_return_status      => l_return_status
                                    ,x_msg_count          => l_msg_count
                                    ,x_msg_data           => l_msg_data
                                    );
--**********************************************************************************************
      DBMS_OUTPUT.put_line ('Return Status : ' || l_return_status);
 
      IF l_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            l_msg        := oe_msg_pub.get (i, 'F');
            l_message    := l_message || CHR (10) || l_msg;
         END LOOP;
 
         DBMS_OUTPUT.put_line ('Process Order Failed Message : ' || l_message);
      ELSE
         /* kopyalama işlemi sonrası success olarak oluşturulan yeni sipariş aşağıdaki api ile kaydedilir !*/
         oe_order_book_util.complete_book_eligible
                                         (p_api_version_number      => 1
                                         ,p_init_msg_list           => fnd_api.g_true
                                         ,p_header_id               => l_query_header_id
                                         ,x_return_status           => l_return_status
                                         ,x_msg_count               => l_msg_count
                                         ,x_msg_data                => l_msg_data
                                         );
 
         IF l_return_status <> fnd_api.g_ret_sts_success
         THEN
            FOR i IN 1 .. l_msg_count
            LOOP
               l_msg        := oe_msg_pub.get (i, 'F');
               l_message    := l_message || CHR (10) || l_msg;
            END LOOP;
 
            DBMS_OUTPUT.put_line (   'Failed to Booked  : New_Header_Id : '
                                  || l_query_header_id
                                  || ' Order_Number : '
                                  || l_new_order.order_number
                                  || ' Error Message : '
                                  || l_message
                                 );
         ELSE
            DBMS_OUTPUT.put_line
                                (   'Successfully Booked  : New_Header_Id : '
                                 || l_query_header_id
                                 || ' Order_Number : '
                                 || l_new_order.order_number
                                );
         END IF;
 
         SELECT *
           INTO l_new_order
           FROM oe_order_headers_all
          WHERE 1 = 1 AND header_id = l_query_header_id;
 
         DBMS_OUTPUT.put_line (   'Successfully Completed  : New_Header_Id : '
                               || l_query_header_id
                               || ' Order_Number : '
                               || l_new_order.order_number
                              );
      END IF;
 
      COMMIT;
   END LOOP;
END;

declare --*********************************************************************************************** --> constant variable ... l_hdr_type_id number := 8507; --> sipariş tipi başlık l_line_type_id number := 8506; --> sipariş satır tipi l_orj_header_id number; l_return_reason_code ont.oe_order_lines_all.return_reason_code%type := 'CANCELLATION'; --> iade oluşturma nedeni --*********************************************************************************************** l_dummy number; l_line_count number; l_line_list varchar2 (2400); l_hdr_list varchar2 (2400); l_message varchar2 (2400); l_new_order ont.oe_order_headers_all%rowtype; l_query_header_id number; l_return_status varchar2 (1); l_msg_count number; p_msg_count number; l_msg_data varchar2 (2000); l_copy_rec oe_order_copy_util.copy_rec_type := oe_order_copy_util.get_copy_rec; l_copy_f_rec oe_order_copy_util.copy_rec_type := oe_order_copy_util.get_copy_rec; l_msg varchar2 (2000); cursor crs_order is --> kopyalanmak istenen siparişlerin listesi select oeh.header_id from oe_order_headers_all oeh where 1 = 1 and oeh.org_id = 1634 and oeh.order_number in (101988, 102641, 102750, 102233, 102770, 102827, 102852 ,102853, 102855, 102856, 102882, 102879, 102878, 102886 ,102883, 102885, 102979, 102983, 102997, 102996, 102984 ,102981, 102982, 103250, 103324, 103358, 103356, 103357 ,103351, 103364, 103348, 103354, 103367, 103349, 103353 ,103352, 103347, 103359, 103360, 103355, 103365, 103448 ,104310, 104306, 104304, 104301, 104305, 104300, 104299 ,104308, 104347, 104345, 104350, 104343, 104338, 104340 ,104344, 104346, 104399, 104438, 104437, 104432, 104400 ,104439, 104441, 104455, 104440, 104442, 104456, 104454 ,104540, 104529, 104542, 104534, 104546, 104531, 104532 ,104536, 104533, 104541, 104538, 104547, 104537, 104545 ,104522, 104528, 104525, 104524, 104574, 104526, 104521 ,104527, 104571, 104523, 104573, 104520, 104580, 104594 ,104595, 104590, 104605, 104592, 104593, 104601, 104603 ,104604); begin --*********************************************************************************************** fnd_global.apps_initialize (0, 67425, 660); --*********************************************************************************************** for rec in crs_order loop --*********************************************************************************************** l_copy_rec := l_copy_f_rec; --*********************************************************************************************** l_line_count := 0; l_message := null; l_return_status := null; l_query_header_id := null; l_msg_data := null; l_line_list := null; l_hdr_list := null; l_msg := null; l_hdr_type_id := 0; l_line_type_id := 0; --*********************************************************************************************** l_orj_header_id := rec.header_id; --********************************************************************************************** --> cancelled olmayan sipariş satırları kopyalanmak üzere toplanır ! for rec in (select * from ont.oe_order_lines_all where 1 = 1 and cancelled_flag = 'N' and header_id = l_orj_header_id) loop l_line_count := l_line_count + 1; l_line_list := l_line_list || ',' || rec.line_id; end loop; --*********************************************************************************************** l_line_list := ltrim (l_line_list, ','); l_hdr_list := to_char (l_orj_header_id); --*********************************************************************************************** dbms_output.put_line ('l_line_list : ' || l_line_list); dbms_output.put_line ('l_hdr_list : ' || l_hdr_list); dbms_output.put_line ('l_line_count : ' || l_line_count); --*********************************************************************************************** /* T(true) veya F(false) */ l_copy_rec.api_version_number := 1; l_copy_rec.init_msg_list := 'T'; l_copy_rec.commit := 'F'; l_copy_rec.copy_order := 'T'; l_copy_rec.hdr_count := 1; --> her api call edildiğinde bir header kopyalanacak l_copy_rec.hdr_list := l_hdr_list; l_copy_rec.expiration_date := null; l_copy_rec.transaction_name := null; l_copy_rec.copy_transaction_name := 'T'; l_copy_rec.copy_expiration_date := 'F'; l_copy_rec.version_number := 0; l_copy_rec.line_version_number := 0; l_copy_rec.append_to_header_id := null; l_copy_rec.hdr_info := 'F'; l_copy_rec.hdr_type := l_hdr_type_id; l_copy_rec.hdr_descflex := 'T'; l_copy_rec.hdr_credit_card_details := 'F'; l_copy_rec.hdr_scredits := 'T'; l_copy_rec.hdr_attchmnts := 'T'; l_copy_rec.hdr_holds := 'T'; l_copy_rec.manual_order_number := null; l_copy_rec.manual_quote_number := null; l_copy_rec.hdr_payments := 'T'; l_copy_rec.all_lines := 'F'; l_copy_rec.line_count := l_line_count; l_copy_rec.line_list := l_line_list; l_copy_rec.line_type := l_line_type_id; l_copy_rec.incl_cancelled := 'F'; l_copy_rec.line_price_mode := 1; l_copy_rec.line_price_date := null; l_copy_rec.line_discount_id := null; l_copy_rec.line_descflex := 'F'; l_copy_rec.line_holds := 'F'; l_copy_rec.line_scredits := 'F'; l_copy_rec.line_attchmnts := 'T'; l_copy_rec.line_payments := 'F'; l_copy_rec.return_reason_code := l_return_reason_code; --> kopya ile yeni oluşturalacak sipariş return tipli ise return_reason_code zorunludur ! l_copy_rec.default_null_values := 'F'; l_copy_rec.new_phase := 'F'; l_copy_rec.version_reason_code := null; l_copy_rec.comments := null; l_copy_rec.phase_change_flag := 'T'; l_copy_rec.line_phase_change_flag := 'T'; l_copy_rec.phase_change_flag := null; l_copy_rec.version_number := null; l_copy_rec.copy_complete_config := 'T'; l_copy_rec.source_block_type := 'LINE'; --********************************************************************************************** oe_order_copy_util.copy_order (p_copy_rec => l_copy_rec ,x_header_id => l_query_header_id ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data ); --********************************************************************************************** dbms_output.put_line ('Return Status : ' || l_return_status); if l_return_status <> fnd_api.g_ret_sts_success then for i in 1 .. l_msg_count loop l_msg := oe_msg_pub.get (i, 'F'); l_message := l_message || chr (10) || l_msg; end loop; dbms_output.put_line ('Process Order Failed Message : ' || l_message); else /* kopyalama işlemi sonrası success olarak oluşturulan yeni sipariş aşağıdaki api ile kaydedilir !*/ oe_order_book_util.complete_book_eligible (p_api_version_number => 1 ,p_init_msg_list => fnd_api.g_true ,p_header_id => l_query_header_id ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data ); if l_return_status <> fnd_api.g_ret_sts_success then for i in 1 .. l_msg_count loop l_msg := oe_msg_pub.get (i, 'F'); l_message := l_message || chr (10) || l_msg; end loop; dbms_output.put_line ( 'Failed to Booked : New_Header_Id : ' || l_query_header_id || ' Order_Number : ' || l_new_order.order_number || ' Error Message : ' || l_message ); else dbms_output.put_line ( 'Successfully Booked : New_Header_Id : ' || l_query_header_id || ' Order_Number : ' || l_new_order.order_number ); end if; select * into l_new_order from oe_order_headers_all where 1 = 1 and header_id = l_query_header_id; dbms_output.put_line ( 'Successfully Completed : New_Header_Id : ' || l_query_header_id || ' Order_Number : ' || l_new_order.order_number ); end if; commit; end loop; end;

About
Latest Posts

Mustafa Korkmaz

Oracle Applications Developer at Partnera

2005 yılında Dokuz Eylül Üniversitesi Endüstri Mühendisliğinden Mezun
Oldu, 2011 Yılında Ahmet Yesevi Üniversitesi Bilgisayar Mühendisliğinde
Yüksek Lisansını Tamamlandı,2012 Yılında Doğuş Üniversitesinde Bilgisayar
Mühendisliği Doktora Eğitimine Başladı.10 yılı aşkın bir zamandır Oracle
Ürünleri Uygulama Yazılım Uzmanı Olarak profesyonel kariyerine devam
etmektedir. İlgi alanları Middleware, CI/CD,
Oracle Database , PL/SQL , Java , Makine Öğrenmesi ve Veri Madenciliği alanlarındadır.
2 versiyon Oracle Database ' (10g, 11g) inde OCA(Oracle Certificate Associate) ve
PL/SQL Certificated Professional ünvanları bulunmaktadır.
Mail: mustafakorkmz@gmail.com