Haz
2
2015
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
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
Latest posts by Ozan Arıkan (see all)
- Xslt üzerinde değişken tanımlanması - 07 Mart 2021
- Weblogic varsayılan java dizinin değiştirilmesi - 08 Temmuz 2020
- Jdeveloper üzerinde kaynak kodlara erişim eklentisi - 30 Mayıs 2020
İlgili Yazılar