May
2
2014

Sipariş Satırını İptal Etmek İçin Kullanılan Örnek Script

Merhaba,

 

OE_ORDER_LINES_ALL tablosunda satır verilerinin iptal durumuna getirilmesini sağlayan örnek bir scripti aşağıda bulabilirsiniz.

 

DECLARE
   l_user_id                      NUMBER;
   l_resp_id                      NUMBER;
   l_appl_id                      NUMBER;
   l_header_rec_in                oe_order_pub.header_rec_type;
-- pl/sql table and record definition to be used as IN parameters
   l_line_tbl_in                  oe_order_pub.line_tbl_type;
-- pl/sql table and record definition to be used as IN parameters
   l_action_request_tbl_in        oe_order_pub.request_tbl_type;
-- Used to assigining Book Order related input parameters
   l_header_rec_out               oe_order_pub.header_rec_type;
-- pl/sql table and record definition to be used as OUT parameters
   l_line_tbl_out                 oe_order_pub.line_tbl_type;
   l_header_val_rec_out           oe_order_pub.header_val_rec_type;
   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   l_action_request_tbl_out       oe_order_pub.request_tbl_type;
   l_chr_program_unit_name        VARCHAR2 (100);
-- To store the package and procedure name for logging
   l_chr_ret_status               VARCHAR2 (1000)                     := NULL;
-- To store the error message code returned by API
   l_msg_count                    NUMBER                                 := 0;
-- To store the number of error messages API has encountered
   l_msg_data                     VARCHAR2 (2000);
-- To store the error message text returned by API
   l_num_api_version              NUMBER                               := 1.0;
 
-- API version
   CURSOR c_so_details
   IS
      SELECT oh.order_number, ol.*
        FROM oe_order_lines_all ol, oe_order_headers_all oh
       WHERE oh.header_id = ol.header_id
         AND oh.org_id = ol.org_id
         AND NVL (ol.cancelled_flag, 'N') = 'N'
         AND oh.order_number = '10001690'            -- Enter the Order Number
         AND ol.line_number = 1                       -- Enter the Line Number
         AND ol.shipment_number = 2               -- Enter the Shipment Number
         AND ol.flow_status_code = 'AWAITING_SHIPPING';
BEGIN
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = 'XXUSER';
 
   SELECT responsibility_id, application_id
     INTO l_resp_id, l_appl_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = 'Order Management Super User';
 
   fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
 
   FOR iso_rec IN c_so_details
   LOOP
      l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
      l_line_tbl_in (1).line_id := iso_rec.line_id;
      l_line_tbl_in (1).ordered_quantity := 0;
      l_line_tbl_in (1).change_reason := 'Admin Error';
      l_line_tbl_in (1).change_comments := 'CANCEL ORDER';
      l_line_tbl_in (1).operation := oe_globals.g_opr_update;
      oe_msg_pub.delete_msg;
      mo_global.init ('ONT');
      mo_global.set_policy_context ('S', iso_rec.org_id);
      oe_order_pub.process_order
                   (p_api_version_number          => l_num_api_version,
                    p_org_id                      => mo_global.get_current_org_id,
                    p_init_msg_list               => fnd_api.g_false,
                    p_return_values               => fnd_api.g_false,
                    p_action_commit               => fnd_api.g_false,
                    p_line_tbl                    => l_line_tbl_in,
                    x_header_rec                  => l_header_rec_out,
                    x_header_val_rec              => l_header_val_rec_out,
                    x_header_adj_tbl              => l_header_adj_tbl_out,
                    x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                    x_header_price_att_tbl        => l_header_price_att_tbl_out,
                    x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                    x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                    x_header_scredit_tbl          => l_header_scredit_tbl_out,
                    x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                    x_line_tbl                    => l_line_tbl_out,
                    x_line_val_tbl                => l_line_val_tbl_out,
                    x_line_adj_tbl                => l_line_adj_tbl_out,
                    x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                    x_line_price_att_tbl          => l_line_price_att_tbl_out,
                    x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                    x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                    x_line_scredit_tbl            => l_line_scredit_tbl_out,
                    x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                    x_lot_serial_tbl              => l_lot_serial_tbl_out,
                    x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                    x_action_request_tbl          => l_action_request_tbl_out,
                    x_return_status               => l_chr_ret_status,
                    x_msg_count                   => l_msg_count,
                    x_msg_data                    => l_msg_data
                   );
      l_msg_data := NULL;
 
      IF l_chr_ret_status <> 'S'
      THEN
         FOR iindx IN 1 .. l_msg_count
         LOOP
            l_msg_data := l_msg_data || ' .' || oe_msg_pub.get (iindx);
         END LOOP;
      END IF;
 
      DBMS_OUTPUT.ENABLE (10000);
      DBMS_OUTPUT.put_line (   'Sales Order => '
                            || iso_rec.order_number
                            || ' - Line Number => '
                            || iso_rec.line_number
                            || ' - Shipment Number => '
                            || iso_rec.shipment_number
                            || ' Having Line ID=> '
                            || iso_rec.line_id
                            || ' Cancelled Successfully'
                           );
      DBMS_OUTPUT.put_line ('Return Status: ' || l_chr_ret_status);
      DBMS_OUTPUT.put_line ('Error Message: ' || l_msg_data);
   END LOOP;
END;
Mustafa Korkmaz

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 E-Business Suite Uygulama Yazılımı
üzerinde Teknik Danışman 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
Mustafa Korkmaz

İlgili Yazılar



Yorum yapın

*