Haz
2
2015

İade siparişlerinin arayüze atılması işlemi

Merhaba,

Siparişin satır statüsünün kapalı olduğu ve arayüze kayıt gitmediği durumlarda aşağıdaki script ile kayıtlar arayüze gönderebilinir.Kod sipariş numarasını parametre olarak almaktadır.

 
 
/* Formatted on 2015/06/02 09:24 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR oe
   IS
      SELECT   oeh.order_number, oeh.ordered_date,
               oeh.transactional_curr_code,
 
               --oel.line_type_id,oeh.ORDER_TYPE_ID,oel.line_type_id,
               (SELECT NAME
                  FROM oe_transaction_types_tl
                 WHERE LANGUAGE = 'TR'
                   AND transaction_type_id = oeh.order_type_id)
                                                             order_type_name,
 
               /* (SELECT cust_trx_type_id
                 FROM oe_transaction_types_all
                 WHERE 1 = 1 AND transaction_type_id = oel.line_type_id)
                   cust_trx_type_id_s,*/
               (SELECT cust_trx_type_id
                  FROM oe_transaction_types_all
                 WHERE 1 = 1
                   AND transaction_type_id = oeh.order_type_id)
                                                            cust_trx_type_id,
               oeh.attribute1 header_attribute1,
               oeh.attribute15 header_attribute15,
               oeh.sold_to_org_id customer_id,
               (SELECT v.address_id
                  FROM apps.hz_site_uses_v v
                 WHERE v.site_use_code = 'BILL_TO'
                   AND v.site_use_id = oeh.invoice_to_org_id)
                                                             fatura_adres_id,
               (SELECT v.address_id
                  FROM apps.hz_site_uses_v v
                 WHERE v.site_use_code = 'SHIP_TO'
                   AND v.site_use_id = oeh.ship_to_org_id) fatura_adres_id,
               oel.line_id,
               (SELECT wda.delivery_id
                  FROM wsh.wsh_delivery_details wdd,
                       wsh.wsh_delivery_assignments wda
                 WHERE 1 = 1
                   AND wdd.container_flag = 'N'
                   AND wdd.source_code = 'OE'
                   AND wdd.source_line_id = oel.line_id
                   AND wda.delivery_detail_id = wdd.delivery_detail_id)
                                                                  deliveryid,
               oel.line_number, oel.unit_selling_price,
               (oel.ordered_quantity * -1) ordered_quantity,
               (SELECT b.description
                  FROM mtl_system_items_b b
                 WHERE b.organization_id = 363
                   AND b.inventory_item_id = oel.inventory_item_id)
                                                                 description,
               oel.inventory_item_id, oel.header_id,
               oel.orig_sys_document_ref, oel.attribute1, oel.org_id,
               oel.ship_from_org_id, oel.tax_code tax_code,
               oel.order_quantity_uom, oel.pricing_date,
               oel.actual_shipment_date,
               DECODE (oeh.transactional_curr_code,
                       'TRY', 'User',
                       oeh.conversion_type_code
                      ) conversion_type_code,
               DECODE (oeh.transactional_curr_code,
                       'TRY', 1,
                       oeh.conversion_rate
                      ) conversion_rate,
               DECODE (oeh.transactional_curr_code,
                       'TRY', NULL,
                       oeh.conversion_rate_date
                      ) conversion_rate_date,
               (SELECT MIN (vtx.vat_tax_id)
                  FROM ar_vat_tax_all_tl vtx
                 WHERE 1 = 1
                   AND vtx.org_id = oel.org_id
                   AND vtx.printed_tax_name = 'KDV - IADE -18' -- oel.tax_code
                   AND vtx.LANGUAGE = 'TR'
                   AND vtx.source_lang = 'TR') tax,
               oeh.salesrep_id, oel.unit_list_price, oeh.order_type_id,
 
               -- oeh.attribute1,
               oeh.attribute2, oeh.attribute3, oeh.attribute4,
               oeh.attribute5, oeh.attribute6, oeh.attribute7,
               oeh.attribute8, oeh.attribute9, oeh.attribute10,
               oeh.attribute11, oeh.attribute12, oeh.attribute13,
               oeh.attribute14, oeh.attribute15
          FROM ont.oe_order_lines_all oel, ont.oe_order_headers_all oeh
         WHERE oeh.order_number = :siparisno
           AND oel.org_id = fnd_profile.VALUE ('ORG_ID')
           AND oel.header_id = oeh.header_id
           AND oel.shipped_quantity <> 0
           --AND invoice_interface_status_code = 'YES'
           AND NOT EXISTS (
                  SELECT 1
                    FROM ar.ra_interface_lines_all ra
                   WHERE ra.interface_line_attribute1 = TO_CHAR (:siparisno)
                     AND ra.interface_line_attribute6 = TO_CHAR (oel.line_id))
           AND EXISTS (
                  SELECT rcv.transaction_id
                    FROM po.rcv_transactions rcv
                   WHERE rcv.shipment_line_id =
                                   (SELECT rsl.shipment_line_id
                                      FROM po.rcv_shipment_lines rsl
                                     WHERE rsl.oe_order_line_id = oel.line_id))
      ORDER BY oel.line_id;
 
   l_kontrol             NUMBER := 0;
   l_created_by          NUMBER := 0;
   l_last_updated_by     NUMBER := 0;
   l_last_update_login   NUMBER := -1;
BEGIN
   l_kontrol := 0;
 
   FOR sip IN oe
   LOOP
      INSERT INTO ra_interface_lines_all
                  (interface_line_context, interface_line_attribute1,
                   interface_line_attribute2, interface_line_attribute3,
                   interface_line_attribute4, interface_line_attribute5,
                   interface_line_attribute6, interface_line_attribute7,
                   interface_line_attribute8, batch_source_name,
                   set_of_books_id, line_type,
                   description,
                   currency_code,
                   amount,
                   cust_trx_type_id, term_id, orig_system_bill_customer_id,
                   orig_system_bill_address_id,
                   orig_system_ship_customer_id,
                   orig_system_ship_address_id,
                   orig_system_sold_customer_id, conversion_type,
                   conversion_date, conversion_rate,
                   trx_date, gl_date,
                   line_number, quantity,
                   quantity_ordered,
                   unit_selling_price,
                   unit_standard_price, tax_code,
                   ship_date_actual, primary_salesrep_id,
                   sales_order, sales_order_line, sales_order_date,
                   sales_order_source, inventory_item_id,
                   header_attribute1, header_attribute15,
                   uom_code, interface_line_attribute10,
                   interface_line_attribute11, interface_line_attribute12,
                   interface_line_attribute13, interface_line_attribute14,
                   interface_line_attribute15, interface_line_attribute9,
                   vat_tax_id, tax_exempt_flag, created_by, creation_date,
                   last_updated_by, last_update_date, last_update_login,
                   org_id, amount_includes_tax_flag, reset_trx_date_flag,
                   warehouse_id, header_attribute_category,
                   attribute_category,
                                      --  HEADER_ATTRIBUTE1,
                                      header_attribute2, header_attribute3,
                   header_attribute4, header_attribute5, header_attribute6,
                   header_attribute7, header_attribute8, header_attribute9,
                   header_attribute10, header_attribute11,
                   header_attribute12, header_attribute13, header_attribute14
                  --  HEADER_ATTRIBUTE15,
                  )
           VALUES ('ORDER ENTRY', sip.order_number,
                   sip.order_type_name, '0',
                   '0', '0',
                   sip.line_id, '0',
                   '0', 'SATIS IADE FATURALARI',
                   fnd_profile.VALUE ('GL_SET_OF_BKS_ID'),             --5001,
                                                          'LINE',
                   (SELECT b.description
                      FROM mtl_system_items_b b
                     WHERE b.organization_id = 363
                       AND b.inventory_item_id = sip.inventory_item_id),
                   NVL (sip.transactional_curr_code, 'TRY'),
                   ROUND ((sip.unit_selling_price * sip.ordered_quantity), 2),
                   3325,    -- sip.cust_trx_type_id, --istenen iade işlem tipi
                        NULL, sip.customer_id,
                   sip.fatura_adres_id,
                   sip.customer_id,
                   sip.fatura_adres_id,
                   sip.customer_id, NVL (sip.conversion_type_code, 'TRY'),
                   sip.conversion_rate_date, sip.conversion_rate,
                   sip.actual_shipment_date, sip.actual_shipment_date,
                   sip.line_number, ROUND (sip.ordered_quantity, 2),
                   ROUND (sip.ordered_quantity, 2),
                   ROUND (sip.unit_selling_price, 7),
                   ROUND (sip.unit_list_price, 7), sip.tax_code,
                   sip.actual_shipment_date, sip.salesrep_id,
                   sip.order_number, sip.line_number, sip.ordered_date,
                   'ORDER ENTRY', sip.inventory_item_id,
                   sip.header_attribute1, sip.header_attribute15,
                   sip.order_quantity_uom, sip.ship_from_org_id,
                   '0', '1',
                   '0', '0',
                   '0', '0',
                   sip.tax, 'S', l_created_by, SYSDATE,
                   l_last_updated_by, SYSDATE, l_last_update_login,
                   fnd_profile.VALUE ('ORG_ID'),                        --363,
                                                NULL, NULL,
                   sip.ship_from_org_id, sip.order_type_id,
                   sip.order_type_id,                     --attribute_category
                                     -- sip.attribute1,
                                     sip.attribute2, sip.attribute3,
                   sip.attribute4, sip.attribute5, sip.attribute6,
                   sip.attribute7, sip.attribute8, sip.attribute9,
                   sip.attribute10, sip.attribute11,
                   sip.attribute12, sip.attribute13, sip.attribute14
                  -- sip.attribute15
                  );
 
      COMMIT;
   END LOOP;
END;

Ozan Arıkan

1988 İstanbul doğumlu. İstanbul Üniversitesi Yönetim Bilişim
Sistemlerinden mezun oldu. 2012 yılından Beri Oracle e-Business
Suite Uygulama Geliştirme Fonksiyonel ve Teknik Danışman olarak
DbOptimize bünyesinde kariyerine devam etmektedir.
Mail: oznarkn@gmail.com
Ozan Arıkan


Yorum yapın

*