| SQL Id | SQL Text |
|---|
| 0074pdmwb7fmv | BEGIN WSH_SHIP_CONFIRM_ACTIONS.interface_ALL_wrp(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8); END; |
| 00b9c91r6ysp9 | UPDATE WSH_TRIP_STOPS SET PENDING_INTERFACE_FLAG = 'P', REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID, LAST_UPDATED_BY = FND_GLOBAL.USER_ID, LAST_UPDATE_DATE = SYSDATE, BATCH_ID = :B2 WHERE STOP_ID = :B1 |
| 05b1a8ykag57t |
insert into ra_customer_trx ( customer_trx_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, trx_number, cust_trx_type_id, trx_date, set_of_books_id, ct_reference, interface_header_context, interface_header_attribute1, interface_header_attribute2, interface_header_attribute3, interface_header_attribute4, interface_header_attribute5, interface_header_attribute6, interface_header_attribute7, interface_header_attribute8, interface_header_attribute9, interface_header_attribute10, interface_header_attribute11, interface_header_attribute12, interface_header_attribute13, interface_header_attribute14, interface_header_attribute15, bill_to_contact_id, batch_id, batch_source_id, sold_to_customer_id, bill_to_customer_id, bill_to_address_id, ship_to_customer_id, ship_to_contact_id, ship_to_address_id, ship_to_site_use_id, term_id, previous_customer_trx_id, primary_salesrep_id, purchase_order, purchase_order_revision, purchase_order_date, comments, internal_notes, exchange_rate_type, exchange_date, exchange_rate, invoice_currency_code, initial_customer_trx_id, agreement_id, request_id, program_application_id, program_id, program_update_date, complete_flag, doc_sequence_value, credit_method_for_rules, credit_method_for_installments, receipt_method_id, doc_sequence_id, related_customer_trx_id, invoicing_rule_id, ship_via, ship_date_actual, waybill_number, fob_point, territory_id, attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15, global_attribute_category, global_attribute1, global_attribute2, global_attribute3, global_attribute4, global_attribute5, global_attribute6, global_attribute7, global_attribute8, global_attribute9, global_attribute10, global_attribute11, global_attribute12, global_attribute13, global_attribute14, global_attribute15, global_attribute16, global_attribute17, global_attribute18, global_attribute19, global_attribute20, global_attribute21, global_attribute22, global_attribute23, global_attribute24, global_attribute25, global_attribute26, global_attribute27, global_attribute28, global_attribute29, global_attribute30, status_trx, posting_control_id, created_from, paying_customer_id, paying_site_use_id, reason_code, printing_option, orig_system_batch_name, payment_server_order_num, approval_code, contract_id, org_id, legal_entity_id, application_id, payment_attributes, billing_date) select l.customer_trx_id, sysdate, :b_last_updated_by, sysdate, :b_created_by, :b_last_update_login, '~$~$$$', l.cust_trx_type_id, l.trx_date, l.set_of_books_id, DECODE(b.DEFAULT_REFERENCE, '1', l.interface_line_attribute1, '2', l.interface_line_attribute2, '3', l.interface_line_attribute3, '4', l.interface_line_attribute4, '5', l.interface_line_attribute5, '6', l.interface_line_attribute6, '7', l.interface_line_attribute7, '8', l.interface_line_attribute8, '9', l.interface_line_attribute9, '10', l.interface_line_attribute10, '11', l.interface_line_attribute11, '12', l.interface_line_attribute12, '13', l.interface_line_attribute13, '14', l.interface_line_attribute14, '15', l.interface_line_attribute15, NULL ), substr(decode(l.interface_line_context, 'Global Data Elements', decode(1, 0, null, 'ORDER ENTRY'), decode(1, 1, l.interface_line_context, 'ORDER ENTRY')), 1, 30), l.interface_line_attribute1, l.interface_line_attribute2, l.interface_line_attribute3, l.interface_line_attribute4, l.interface_line_attribute5, l.interface_line_attribute6, l.interface_line_attribute7, l.interface_line_attribute8, l.interface_line_attribute9, l.interface_line_attribute10, l.interface_line_attribute11, l.interface_line_attribute12, l.interface_line_attribute13, l.interface_line_attribu
te14, l.interface_line_attribute15, l.orig_system_bill_contact_id, :b_batch_id, :b_batch_source_id, l.orig_system_sold_customer_id, l.orig_system_bill_customer_id, l.orig_system_bill_address_id, l.orig_system_ship_customer_id, l.orig_system_ship_contact_id, l.orig_system_ship_address_id, null, l.term_id, l.previous_customer_trx_id, l.primary_salesrep_id, l.purchase_order, l.purchase_order_revision, l.purchase_order_date, l.comments, l.internal_notes, decode(l.currency_code, g.currency_code, null, l.conversion_type), decode(l.currency_code, g.currency_code, null, nvl(l.conversion_date, l.trx_date)), decode(l.currency_code, g.currency_code, null, l.conversion_rate), l.currency_code, l.initial_customer_trx_id, l.agreement_id, :b1, :b_program_application_id, :b_program_id, sysdate, 'N', l.document_number, l.credit_method_for_acct_rule, l.credit_method_for_installments, l.receipt_method_id, l.document_number_sequence_id, l.related_customer_trx_id, l.invoicing_rule_id, l.ship_via, l.ship_date_actual, l.waybill_number, l.fob_point, l.territory_id, l.header_attribute_category, rtrim(l.header_attribute1), rtrim(l.header_attribute2), rtrim(l.header_attribute3), rtrim(l.header_attribute4), rtrim(l.header_attribute5), rtrim(l.header_attribute6), rtrim(l.header_attribute7), rtrim(l.header_attribute8), rtrim(l.header_attribute9), rtrim(l.header_attribute10), rtrim(l.header_attribute11), rtrim(l.header_attribute12), rtrim(l.header_attribute13), rtrim(l.header_attribute14), rtrim(l.header_attribute15), l.header_gdf_attr_category, l.header_gdf_attribute1, l.header_gdf_attribute2, l.header_gdf_attribute3, l.header_gdf_attribute4, l.header_gdf_attribute5, l.header_gdf_attribute6, l.header_gdf_attribute7, l.header_gdf_attribute8, l.header_gdf_attribute9, l.header_gdf_attribute10, l.header_gdf_attribute11, l.header_gdf_attribute12, l.header_gdf_attribute13, l.header_gdf_attribute14, l.header_gdf_attribute15, l.header_gdf_attribute16, l.header_gdf_attribute17, l.header_gdf_attribute18, l.header_gdf_attribute19, l.header_gdf_attribute20, l.header_gdf_attribute21, l.header_gdf_attribute22, l.header_gdf_attribute23, l.header_gdf_attribute24, l.header_gdf_attribute25, l.header_gdf_attribute26, l.header_gdf_attribute27, l.header_gdf_attribute28, l.header_gdf_attribute29, l.header_gdf_attribute30, 'OP', -3, 'RAXTRX', decode(l.receipt_method_id, null, null, l.paying_customer_id), decode(l.receipt_method_id, null, null, l.paying_site_use_id), l.reason_code, l.printing_option, l.orig_system_batch_name, l.payment_server_order_num, l.approval_code, l.contract_id, l.org_id, l.legal_entity_id, l.application_id, l.payment_attributes, l.billing_date from ra_interface_lines_gt l, ra_batch_sources b, gl_sets_of_books g where l.set_of_books_id = g.set_of_books_id and b.name = l.batch_source_name and l.interface_line_id in ( select /*+ INDEX (l1, RA_INTERFACE_LINES_N2) */ min(l1.interface_line_id) from ra_interface_lines_gt l1 where l1.request_id = :b2 and l1.customer_trx_id is not null and nvl(l1.interface_status, '~') != 'P' and l1.link_to_line_id is null and not ((l1.reference_line_id is not null or l1.reference_line_attribute1 || l1.reference_line_attribute2 || l1.reference_line_attribute3 || l1.reference_line_attribute4 || l1.reference_line_attribute5 || l1.reference_line_attribute6 || l1.reference_line_attribute7 || l1.reference_line_attribute8 || l1.reference_line_attribute9 || l1.reference_line_attribute10 || l1.reference_line_attribute11 || l1.reference_line_attribute12 || l1.reference_line_attribute13 || l1.reference_line_attribute14 || l1.reference_line_attribute15 is not null) and exists (select 'x' from ra_cust_trx_types t where t.cust_trx_type_id = l1.cust_trx_type_id and t.type = 'CM'))and ( l1.customer_trx_id, NVL(l1.ship_date_actual, to_date('01/01/0001', 'dd-mm-yyyy')) ) IN ( select /*+ no_merge */ l2.customer_trx_id, NVL(MIN
(l2.ship_date_actual), to_date('01/01/0001', 'dd-mm-yyyy')) from ra_interface_lines_gt l2 where request_id = :b3 group by l2.customer_trx_id )group by l1.customer_trx_id ) |
| 0758ng3cbw07m | SELECT STOP_ID FROM WSH_TRIP_STOPS WHERE BATCH_ID = :B1 FOR UPDATE NOWAIT |
| 0aqjpk56w9cgw | SELECT LINE_ID FROM OE_ORDER_LINES WHERE LINE_ID = :B1 FOR UPDATE NOWAIT |
| 0tsz27uj32p77 | UPDATE GL_BC_PACKETS SET GROUP_ID = :B2 , JE_BATCH_NAME = :B1 WHERE AE_HEADER_ID IN (SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE GROUP_ID = :B2 AND APPLICATION_ID = :B3 ) |
| 0uhc08p99nzvb |
declare l_application_short_name varchar2(50); l_program_short_name varchar2(50); l_request_id number; child_request_id number; l_status_code varchar2(100); l_phase_code varchar2(100); l_error varchar2(255); l_userid number; l_resp_id number; l_resp_appl_id number; l_conc_program_code varchar2(30); concurrent_program_failure EXCEPTION; -- HVOP variables l_order_source varchar2(100) := null; l_orig_system_doc_ref varchar2(100) := null; l_order_number number; l_HVOP_threads number := TO_NUMBER(LTRIM(RTRIM('56'))); l_hvop_request_id number; -- Capture parent HVOP request id to check_its -- Pick Release variables l_release_rule_base varchar2(100) := NULL; l_batch_prefix_base varchar2(100) := NULL; l_release_rule_name varchar2(100) := NULL; l_batch_prefix varchar2(100) := NULL; l_release_rule_id number; l_PR_threads number := TO_NUMBER(LTRIM(RTRIM('56'))); l_pick_number number; -- Process Delivery variables l_processed_entities varchar2(100) := 'L'; -- 'Delivery Lines Only'; -- Ship Confirm variables l_rule varchar2(100) := 1; -- 'Auto Ship' l_prefix varchar2(100) := 'test'; l_organization varchar2(100) := 204; -- 'V1' l_batch_base varchar2(100) ; l_batch_comp varchar2(100) ; l_batch number; -- ITS variables -- l_ITS_child_processes number := 1; l_ITS_child_processes number := TO_NUMBER(LTRIM(RTRIM('56'))); -- Auto Invoice variables l_batch_source_id varchar2(100) := '1001'; l_batch_source_name varchar2(100) := 'ORDER ENTRY'; l_Autoinvoice_date varchar2(100) := to_char(trunc(sysdate), 'YYYY/MM/DD HH24:MI:SS'); -- l_AI_threads number := 1; l_AI_threads number := TO_NUMBER(LTRIM(RTRIM('56'))); l_ra_auto_inv number; -- Revenue Recognition Master Program variables l_revenue_recognition_workers number := TO_NUMBER(LTRIM(RTRIM('56'))); l_rr_start_time varchar2(20); l_rr_end_time varchar2(20); l_rr_request_id number; l_act_class varchar2(240); l_act_set_flag varchar2(10); l_sab_request_id number; -- General Ledger Transfer Program variables l_general_ledger_date varchar2(100) := to_char(trunc(sysdate), 'YYYY/MM/DD HH24:MI:SS'); l_conc_prg_name varchar2(30) := NULL; l_count NUMBER := 0; l_hvop_req_id number; l_autoinv_req_id number; l_min_order number; l_max_order number; l_released_status varchar2(1) := NULL; l_flow_status_code varchar2(30) := NULL; l_oe_interfaced_flag varchar2(1) := NULL; l_inv_interfaced_flag varchar2(1) := NULL; -- Process Transactions and Background Workflow processes parameters l_process_trx_workers number := TO_NUMBER(LTRIM(RTRIM('56'))); l_wf_workers number := TO_NUMBER(LTRIM(RTRIM('56'))); l_process_trx_cnt number; l_wf_cnt number; l_snap_id number; -- Cursor to select batches for Ship Confirm Process cursor batches is select a.batch_id from wsh_picking_batches a where NVL(a.non_picking_flag, 'N') <> 'Y' and a.name like l_batch_comp||'%' and exists ( select 1 from wsh_delivery_details b where a.batch_id = b.batch_id and b.container_flag = 'N' and b.released_status in ('X', 'Y')) order by a.name desc; --Check_pick_release.sql -- cursor pick_release is -- select released_status, count(*) -- from wsh_delivery_details wdd, oe_order_headers_all oh -- where oh.order_number between l_min_order and l_max_order -- and oh.header_id = wdd.source_header_id -- group by released_status; --get_flow_status_code -- cursor get_flow_status_code is -- select oel.flow_status_code, count(*) -- from oe_order_headers_all oeh, -- oe_order_lines_all oel -- where oeh.header_id=oel.header_id -- and oeh.order_number between l_min_order and l_max_order -- group by oel.flow_status_code; --Check_its.sql -- cursor check_its is -- select oe_interfaced_flag, inv_interfaced_flag , count(*) -- from wsh_delivery_details wdd, oe_order_headers_all oh -- where oh.order_number between l_min_order and l_max_order -- and oh.header_id = wdd.source_header_id -- group by oe_interfaced_flag , inv_interfaced_flag; --Check RevRec: -- cursor check_rev_rec is -- select account_class, account_set_flag, count(*) -- from ra_cust_trx_line_gl_dist_all -
- where to_char(creation_date, 'YYYYMMDDHH24MI') > l_rr_start_time -- and to_char(creation_date, 'YYYYMMDDHH24MI') < l_rr_end_time -- group by account_class, account_set_flag -- order by account_class; PROCEDURE get_status(p_request_id IN NUMBER, p_status_code OUT VARCHAR2, p_phase_code OUT VARCHAR2) IS CURSOR c_get_codes(p_request_id IN NUMBER) IS SELECT status_code, phase_code FROM fnd_concurrent_requests WHERE request_id = p_request_id; data_found exception; BEGIN --dbms_output.put_line('get_status for ' || p_request_id); OPEN c_get_codes(p_request_id); FETCH c_get_codes INTO p_status_code, p_phase_code; IF c_get_codes%NOTFOUND THEN p_status_code := NULL; p_phase_code := NULL; END IF; CLOSE c_get_codes; --dbms_output.put_line('status ' || p_status_code || ' phase ' || p_phase_code); IF p_phase_code <> 'C' OR p_phase_code IS NULL THEN --dbms_output.put_line('dbms_lock.sleeping .....'); dbms_lock.sleep(5); get_status(p_request_id, p_status_code, p_phase_code); END IF; /* by Iyas for now IF l_program_short_name = 'ARACCPB' then dbms_lock.sleep(3); ELSE dbms_lock.sleep(0.5); END IF; */ END get_status; PROCEDURE get_status_kids(p_request_id IN NUMBER) IS -- Recursive call to wait for the completion of process and all kids cursor requests is select request_id from fnd_concurrent_requests req where request_id <> p_request_id start with req.request_id = p_request_id connect by prior req.request_id = req.parent_request_id order by request_id; l_start_time varchar2(20) := NULL; l_end_time varchar2(20) := NULL; l_hr varchar2(6) := NULL; l_mi varchar2(6) := NULL; l_sec varchar2(6) := NULL; l_tsec varchar2(6) := NULL; l_rows_per_hr varchar2(12) := NULL; l_num_req varchar2(6) := NULL; data_found exception; BEGIN --Parent Details if p_request_id is not null then get_status(p_request_id, l_status_code, l_phase_code); l_start_time := NULL; l_end_time := NULL; l_hr := NULL; l_mi := NULL; l_sec := NULL; l_tsec := NULL; l_rows_per_hr := NULL; select (select substr(user_concurrent_program_name, 1, 30) from fnd_concurrent_programs_TL prg where req.concurrent_program_id = prg.concurrent_program_id and userenv('LANG') = prg.language), (select concurrent_program_name from fnd_concurrent_programs prg1 where req.concurrent_program_id = prg1.concurrent_program_id), to_char( actual_start_date, 'DD-MON hh24:mi:ss' ), to_char( actual_completion_date, 'DD-MON hh24:mi:ss' ), trunc( mod( (actual_completion_date-actual_start_date)*24, 24 ) ), trunc( mod( (actual_completion_date-actual_start_date)*24*60, 60 ) ), trunc( mod( (actual_completion_date-actual_start_date)*24*60*60, 60 ) ), trunc((actual_completion_date-actual_start_date)*24*60*60) , trunc((20000*3600)/ decode(actual_completion_date-actual_start_date, 0, 1, ((actual_completion_date-actual_start_date)*24*60*60))) into l_conc_prg_name, l_conc_program_code, l_start_time, l_end_time, l_hr, l_mi, l_sec, l_tsec, l_rows_per_hr from fnd_concurrent_requests req where req.request_id = p_request_id; dbms_output.put_line ('------------------------------------------------------------------------------------------------------------------------'); dbms_output.put_line (l_conc_prg_name||' Code: '||l_conc_program_code ||' Req Id: '||p_request_id); dbms_output.put_line (' Start: '||l_start_time||' End: ' ||l_end_time ||' TSecs: '||l_tsec||' '||l_mi||' '||l_sec ||' Rows/Hour: '||l_rows_per_hr||' Status: '||l_status_code); IF l_status_code = 'E' THEN RAISE concurrent_program_failure; END IF; end if; --Child Details open requests; loop fetch requests into l_request_id; exit when requests%NOTFOUND; if l_request_id is not null then get_status(l_request_id, l_status_code, l_phase_code); l_start_time := NULL; l_end_time := NULL; l_hr := NULL; l_mi := NULL; l_sec := NULL; l_tsec := NULL; l_rows_per_hr := NULL; select (select substr(user_concurrent_program_name, 1, 30) from fnd_concurrent_programs_TL prg where req.concurrent_program_id = prg.concurrent_program_id and userenv('LANG')
= prg.language), (select concurrent_program_name from fnd_concurrent_programs prg1 where req.concurrent_program_id = prg1.concurrent_program_id), to_char( actual_start_date, 'DD-MON hh24:mi:ss' ), to_char( actual_completion_date, 'DD-MON hh24:mi:ss' ), trunc( mod( (actual_completion_date-actual_start_date)*24, 24 ) ), trunc( mod( (actual_completion_date-actual_start_date)*24*60, 60 ) ), trunc( mod( (actual_completion_date-actual_start_date)*24*60*60, 60 ) ), trunc((actual_completion_date-actual_start_date)*24*60*60) , trunc((20000*3600)/ decode(actual_completion_date-actual_start_date, 0, 1, ((actual_completion_date-actual_start_date)*24*60*60))) into l_conc_prg_name, l_conc_program_code, l_start_time, l_end_time, l_hr, l_mi, l_sec, l_tsec, l_rows_per_hr from fnd_concurrent_requests req where req.request_id = l_request_id; IF l_program_short_name = 'ARACCPB' or l_program_short_name = 'INCTCM' then IF (l_conc_prg_name = 'Revenue Recognition Master Pro' or l_conc_prg_name = 'Revenue Recognition with paral' or l_conc_prg_name = 'Revenue Contingency Analyzer' or l_conc_prg_name = 'Inventory transaction worker') then NULL; ELSE dbms_output.put_line ('------------------------------------------------------------------------------------------------------------------------'); /* dbms_output.put_line (l_conc_prg_name||' Code: '||l_conc_program_code ` ||' Req Id: '||p_request_id||' Start: ' ||l_start_time||' End: ' ||l_end_time ||' TSecs: '||l_tsec||' '||l_mi||' '||l_sec ||' Rows/Hour: '||l_rows_per_hr||' Status: '||l_status_code); */ dbms_output.put_line (l_conc_prg_name||' Code: '||l_conc_program_code ||' Req Id: '||p_request_id); dbms_output.put_line (' Start: '||l_start_time||' End: ' ||l_end_time ||' TSecs: '||l_tsec||' '||l_mi||' '||l_sec ||' Rows/Hour: '||l_rows_per_hr||' Status: '||l_status_code); END IF; ELSE dbms_output.put_line ('------------------------------------------------------------------------------------------------------------------------'); /* dbms_output.put_line (l_conc_prg_name||' Code: '||l_conc_program_code ||' Req Id: '||p_request_id||' Start: ' ||l_start_time||' End: ' ||l_end_time ||' TSecs: '||l_tsec||' '||l_mi||' '||l_sec ||' Rows/Hour: '||l_rows_per_hr||' Status: '||l_status_code); */ dbms_output.put_line (l_conc_prg_name||' Code: '||l_conc_program_code ||' Req Id: '||p_request_id); dbms_output.put_line (' Start: '||l_start_time||' End: ' ||l_end_time ||' TSecs: '||l_tsec||' '||l_mi||' '||l_sec ||' Rows/Hour: '||l_rows_per_hr||' Status: '||l_status_code); END IF; IF l_program_short_name = 'OEHVIMP' then l_hvop_req_id := l_request_id; -- Capture child HVOP req. id END IF; IF l_program_short_name = 'RAXMTR' then IF l_autoinv_req_id = 0 then l_autoinv_req_id := l_request_id; END IF; END IF; IF l_conc_program_code = 'XLABABUP' THEN l_sab_request_id := l_request_id; END IF; IF l_status_code = 'E' THEN IF l_conc_program_code = 'WSHRDASC' or l_conc_program_code = 'ARBARL_NON_SRS2' or l_conc_program_code = 'WSHRDASC' THEN NULL; ELSE RAISE concurrent_program_failure; END IF; END IF; end if; end loop; close requests; END get_status_kids; -- Procedure check_its_flags checks that all the lines have -- oe_interfaced_flag and inv_interfaced_flag changed to 'Y' -- before submitting the Workflow Background processes. The -- oe_interfaced_flag and inv_interfaced_flag flags are changed -- to 'Y' by the Process Transactions process. PROCEDURE check_its_flags ( p_min_order IN NUMBER, p_max_order IN NUMBER, p_lines IN NUMBER) IS l_oe_interfaced_flag VARCHAR2(10); l_inv_interfaced_flag VARCHAR2(10); l_count NUMBER; cursor c_get_its_flags (p_min_order IN NUMBER, p_max_order IN NUMBER) IS select oe_interfaced_flag, inv_interfaced_flag, count(*) from wsh_delivery_details wdd, oe_order_headers_all oh where oh.order_number between p_min_order and p_max_order and oh.header_id = wdd.source_header_id group by oe_interfaced_flag , inv_interfaced_flag; data_found exception; BEGIN OPEN c_get_its_flags (p_min_ord
er, p_max_order); FETCH c_get_its_flags INTO l_oe_interfaced_flag, l_inv_interfaced_flag, l_count; IF c_get_its_flags%NOTFOUND THEN l_oe_interfaced_flag := NULL; l_inv_interfaced_flag := NULL; l_count := 0; END IF; CLOSE c_get_its_flags; IF l_count < p_lines THEN --dbms_output.put_line('dbms_lock.sleeping .....'); dbms_lock.sleep(10); check_its_flags(p_min_order, p_max_order, p_lines); END IF; END check_its_flags; -- Procedure check_its checks that all the lines have flow_status_code -- changed to 'CLOSED' submitting Auto Invoice. flow_status_code is changed -- to 'CLOSED' by the Workflow Background processes. PROCEDURE check_its ( p_min_order IN NUMBER, p_max_order IN NUMBER, p_lines IN NUMBER) IS l_flow_status_code VARCHAR2(10); l_count NUMBER; cursor c_get_its (p_min_order IN NUMBER, p_max_order IN NUMBER) IS select oel.flow_status_code, count(*) from oe_order_headers_all oeh, oe_order_lines_all oel where oeh.header_id=oel.header_id and oeh.order_number between p_min_order and p_max_order and oel.flow_status_code = 'CLOSED' group by oel.flow_status_code; data_found exception; BEGIN OPEN c_get_its (p_min_order, p_max_order); FETCH c_get_its INTO l_flow_status_code, l_count; IF c_get_its%NOTFOUND THEN l_flow_status_code := NULL; l_count := 0; END IF; CLOSE c_get_its; IF l_count < (p_lines-10) THEN --dbms_output.put_line('dbms_lock.sleeping .....'); dbms_lock.sleep(10); check_its(p_min_order, p_max_order, p_lines); END IF; END check_its; begin dbms_output.put_line('Starting OTC Batch'); -- select count(*) into l_count from oe_lines_iface_all -- where trunc(creation_date) = trunc(sysdate); -- dbms_output.put_line('Lines inserted in oe_lines_iface_all: '||l_count); l_userid := 1318; -- "Operations" l_resp_id := 21623; -- "Order Management Super User" l_resp_appl_id := 660; -- "ONT" fnd_global.apps_initialize( l_userid, -- "Operations" l_resp_id, -- "Order Management Super User" l_resp_appl_id -- "ONT" ); apps.mo_global.init('ONT'); mo_global.set_policy_context('S', 204); -- Submit the HVOP Process /* begin execute immediate 'alter session set tracefile_identifier=''HVOP'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('HVOP_bid '|| l_snap_id); commit; END; l_application_short_name := 'ONT'; l_program_short_name := 'OEHVIMP'; l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, NULL, -- Change by Iyas to Null, original value = 204 l_order_source, l_orig_system_doc_ref, 'N', 'N', 'N', NULL, l_HVOP_threads , --hvop thread number 1000, --accounting rule id for rev recognition 'N', 'Y', CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); -- dbms_output.put_line('HVOP Request Id : '||l_request_id); IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); ELSE l_hvop_request_id := l_request_id; COMMIT; --Commit, otherwise program is not submitted END IF; -- Recursive call to wait for the completion of process get_status_kids(l_request_id); -- select count(*) into l_count -- from oe_order_lines_all where trunc(creation_date) = trunc(sysdate); -- dbms_output.put_line('Lines inserted in oe_order_lines_all (today): '||l_count); -- Get_low_high_order_num.sql select min(order_number), max(order_number) into l_min_order, l_max_order from oe_order_headers_all h where h.request_id in (select request_id from fnd_concurrent_requests where parent_request_id = l_hvop_request_id); -- dbms_outp
ut.put_line('Min Order: '||l_min_order|| ' Max Order: '||l_max_order); --return; -- open pick_release; -- loop -- fetch pick_release into l_released_status, l_count; -- EXIT when pick_release%NOTFOUND; -- dbms_output.put_line('Before Pick Release: Release Status: '||l_released_status||' Count: '||l_count); -- end loop; -- close pick_release; -- Gather stats for WSH tables -- Iyas -- fnd_stats.gather_table_Stats(ownname => 'WSH', tabname => 'WSH_DELIVERY_DETAILS', percent => 100); -- fnd_stats.gather_table_Stats(ownname => 'WSH', tabname => 'WSH_TRIP_STOPS', percent => 100); l_batch_prefix_base := l_request_id; -- Unique Identifier -- Submit the Pick Release Process /* begin execute immediate 'alter session set tracefile_identifier=''PICK_REL'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('HVOP_eid '|| l_snap_id); dbms_output.put_line('PICK_REL_bid '|| l_snap_id); commit; END; l_release_rule_name := 'EBS_release_rule1'; l_batch_prefix := '1rule'; begin select picking_rule_id into l_release_rule_id from wsh_picking_rules where name = l_release_rule_name; exception when no_data_found then l_release_rule_id := null; end; l_application_short_name := 'WSH'; l_program_short_name := 'WSHPSRS'; l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, l_release_rule_id, l_batch_prefix, 0, -- 1 to turn logging on/0 to turn off '', to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'), l_PR_threads , -- Change by Iyas Original value was '' CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); ELSE COMMIT; --Commit, otherwise program is not submitted END IF; -- Recursive call to wait for the completion of process get_status_kids(l_request_id); -- open pick_release; -- loop -- fetch pick_release into l_released_status, l_count; -- EXIT when pick_release%NOTFOUND; -- dbms_output.put_line('After Pick Release: Release Status: '||l_released_status||' Count: '||l_count); -- end loop; -- close pick_release; -- open get_flow_status_code; -- loop -- fetch get_flow_status_code into l_flow_status_code, l_count; -- EXIT when get_flow_status_code%NOTFOUND; -- dbms_output.put_line('Flow Status Code: '||l_flow_status_code||' Count: '||l_count); -- end loop; -- close get_flow_status_code; -- Submit Interface Trip Stop /* begin execute immediate 'alter session set tracefile_identifier=''ITS'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('PICK_REL_eid '|| l_snap_id); dbms_output.put_line('ITS_bid '|| l_snap_id); commit; END; l_application_short_name := 'WSH'; l_program_short_name := 'WSHINTERFACE'; l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, 'ALL', NULL, NULL, 0, NULL, NULL, 204, -- V1 l_ITS_child_processes, -- No of Child Processes -- 100, -- Stops per batch 1, -- Stops per batch CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', ''); --dbms_output.put_line('ITS Request Id : '||l_request_id); IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); ELSE COMMIT; --Commit, otherwise program is not submitted END IF; -- Recursive call to wait for the completion of process get_status_kids(l_request_id); -- By Iyas to test MTL_TRANSACTIONS_INTERFACE -- fnd_stats.gather_table_stats('INV', 'MTL_TRANSACTIONS_INTERFACE', CASCADE=>FALSE); -- begin execute immediate 'alter index inv.MTL_TRANSACTIONS_INTERFACE_N3 rebuild '; end; -- begin execute immediate 'alter index inv.MTL_TRANSACTIONS_INTERFACE_N1 rebuild '; end; -- begin execute immediate 'alter index inv.MTL_TRANSACTIONS_INTERFACE_N2 rebuild '; end; -- begin execute immediate 'alter index inv.MTL_TRANSACTIONS_INTERFACE_N4 rebuild '; end; -- begin execute immediate 'alter index inv.MTL_TRANSACTIONS_INTERFACE_U1 rebuild '; end; -- Open Check_its; -- loop -- FETCH Check_its INTO l_oe_interfaced_flag, l_inv_interfaced_flag, l_count; -- EXIT WHEN Check_its%NOTFOUND; -- dbms_output.put_line('After ITS: oe_interfaced_flag: '||l_oe_interfaced_flag||' inv_interfaced_flag: ' -- ||l_inv_interfaced_flag||' Count: '||l_count); -- end loop; -- Close Check_its; -- Submit Process Transaction Interface /* begin execute immediate 'alter session set tracefile_identifier=''PROC_TRXN'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('ITS_eid '|| l_snap_id); dbms_output.put_line('Inventory_bid '|| l_snap_id); commit; END; l_application_short_name := 'INV'; l_program_short_name := 'INCTCM'; for l_process_trx_cnt in 1 .. l_process_trx_workers loop l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); --dbms_output.put_line('Process Interface Request Id : '||l_request_id); IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); END IF; COMMIT; --Commit, otherwise program is not submitted end loop; -- Recursive call to wait for the completion of process -- Taking get_status_kids for now -- Iyas -- get_status_kids(l_request_id); -- Open Check_its; -- loop -- FETCH Check_its INTO l_oe_interfaced_flag, l_inv_interfaced_flag, l_count; -- EXIT WHEN Check_its%NOTFOUND; -- dbms_output.put_line('After Process Interface: oe_interfaced_flag: '||l_oe_interfaced_flag||' inv_interfaced_flag: ' -- ||l_inv_interfaced_flag||' Count: '||l_count); -- end loop; -- Close Check_its; -- Submit the Workflow background Process /* begin execute immediate 'alter session set tracefile_identifier=''WF'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('Inventory_eid '|| l_snap_id); dbms_output.put_line('Workflow_bid '|| l_snap_id); commit; END; l_application_short_name := 'FND'; l_program_short_name := 'FNDWFBG'; for l_wf_cnt in 1 .. l_wf_workers loop l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, 'OEOL', '', '', 'Y', 'N', CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''
, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); if l_request_id = 0 then l_error := fnd_message.get; dbms_output.put_line(l_error); end if; commit; end loop ; --Auto Invoice -- select count(*) into l_count -- from ra_interface_lines_all -- where batch_source_name = 'ORDER ENTRY' -- and trunc(creation_date) = trunc(sysdate); -- dbms_output.put_line('Before AutoInvoice:Lines inserted in ra_interface_lines_all (Today): '||l_count); check_its (l_min_order, l_max_order, 10000*10); -- This check will only allow to submit Auto Invoice after -- all lines are changed to 'CLOSED' by Workflow Background processes. -- Submit the Auto Invoice Process /* begin execute immediate 'alter session set tracefile_identifier=''AUTO_INV'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('Workflow_eid '|| l_snap_id); dbms_output.put_line('Auto_Invoice_bid '|| l_snap_id); commit; END; -- dbms_lock.sleep(600); -- By iyas new 07/26/2010 update ra_interface_lines set purchase_order = interface_line_attribute1 ; -- fnd_stats.gather_table_stats(ownname => 'AR', tabname => 'RA_INTERFACE_LINES_ALL', PERCENT=>100) ; l_userid := 1318; -- "Operations" l_resp_id := 50559; -- "Receivables, Vision Operations (USA)" l_resp_appl_id := 222; -- "AR" fnd_global.apps_initialize( l_userid, -- "Operations" l_resp_id, -- "Receivables, Vision Operations (USA)" l_resp_appl_id -- "AR" ); apps.mo_global.init('AR'); mo_global.set_policy_context('S', 204); arp_global.init_global(204); arp_standard.init_standard(204); l_autoinv_req_id := 0; l_application_short_name := 'AR'; l_program_short_name := 'RAXMTR'; l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, l_AI_threads, -- 2 -- No of CPUs minus one 204, -- Change by Iyas original value = 204 l_batch_source_id, -- '1001' l_batch_source_name, -- 'ORDER ENTRY' l_autoinvoice_date, NULL, NULL, NULL, NULL, NULL, NULL, NULL, --to_char(sysdate+t_no, 'YYYY/MM/DD HH24:MI:SS') -- Low GL date NULL, --to_char(sysdate+t_no+1, 'YYYY/MM/DD HH24:MI:SS') -- High GL date NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Y', NULL, CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); --dbms_output.put_line('Auto Invoice Request Id : '||l_request_id); IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); ELSE UPDATE FND_CONCURRENT_REQUESTS SET org_id = 204 WHERE request_id = l_request_id; COMMIT; --Commit, otherwise program is not submitted END IF; -- Recursive call to wait for the completion of process get_status_kids(l_request_id); IF l_status_code = 'E' THEN RAISE concurrent_program_failure; END IF; dbms_output.put_line('AutoInvoice Child ReqId: '||l_autoinv_req_id); --AutoInvoice Check: -- select count(*) into l_count -- from ra_customer_trx_lines_All -- where line_type = 'LINE' -- and request_id = l_autoinv_req_id; -- dbms_output.put_line('Lines inserted in ra_customer_trx_lines_all: '||l_count); -- select count(*) into l_count -- from zx_lines where application_id = 222 and trx_id in -- ( select customer_Trx_id from ra_customer_Trx_lines_All where request_id = l_autoinv_req_id); -- dbms_output.put_line('Lines inserted in zx_lines: '||l_count); -- select count(*) into l_count from ar_rev_rec_qt; -- dbms_output.put_line('Before RevRec: Lines to be processed from ar_rev_rec_qt: '||l_count); --return; -- Submit Revenue Recognition Master Program /* begin execute immediate 'alter session set tracefile_identif
ier=''REV_REC'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('Auto_Invoice_eid '|| l_snap_id); dbms_output.put_line('Rev_Recog_bid '|| l_snap_id); commit; END; l_application_short_name := 'AR'; l_program_short_name := 'ARTERRPM'; l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, 'S', -- Summary l_revenue_recognition_workers, -- No of CPUs 10, 40, 204, -- 10, 40 originally 60, 180 -- 1, 1, 204, -- GTB 20120228 CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); --dbms_output.put_line('Revenue Recognition Request Id : '||l_request_id); IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); ELSE UPDATE FND_CONCURRENT_REQUESTS SET org_id = 204 WHERE request_id = l_request_id; COMMIT; --Commit, otherwise program is not submitted END IF; -- Recursive call to wait for the completion of process get_status_kids(l_request_id); l_rr_request_id := l_request_id; IF l_status_code = 'E' THEN RAISE concurrent_program_failure; END IF; -- select to_char(actual_start_date, 'YYYYMMDDHH24MI') -- into l_rr_start_time -- from fnd_concurrent_requests where request_id = l_autoinv_req_id; -- select to_char(actual_completion_date, 'YYYYMMDDHH24MI') -- into l_rr_end_time -- from fnd_concurrent_requests where request_id = l_rr_request_id; -- open check_rev_rec; -- loop -- fetch check_rev_rec into l_act_class, l_act_set_flag, l_count; -- EXIT when check_rev_rec%NOTFOUND; -- dbms_output.put_line('After RevRec: Account Class: '||l_act_class||' Account Set Flag: '||l_act_set_flag||' Count: '||l_count); -- end loop; -- close check_rev_rec; -- select count(*) into l_count from gl_je_lines; -- dbms_output.put_line('Before Act: Lines in gl_je_lines: '||l_count); -- select count(*) into l_count from xla_ae_lines; -- dbms_output.put_line('Before Submit Act: Lines in xla_ae_lines: '||l_count); -- Submit Accounting /* begin execute immediate 'alter session set tracefile_identifier=''ACC'' events = ''10928 trace name context level 1024'' '; end; */ BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); dbms_output.put_line('Rev_Recog_eid '|| l_snap_id); dbms_output.put_line('Accounting_bid '|| l_snap_id); commit; END; l_application_short_name := 'AR'; l_program_short_name := 'ARACCPB'; l_request_id := FND_REQUEST.SUBMIT_REQUEST ( l_application_short_name, l_program_short_name, '', '', FALSE, 'S', 8, 30, 140, '', '', 222, 222, 'Y', 1, '', --30, 140 originally 60, 180 -- 'S', 8, 1, 2, '', '', 222, 222, 'Y', 1, '', -- GTB 20120228 to_char(sysdate+365, 'YYYY/MM/DD HH24:MI:SS'), 'Y', 'Y', 'F', 'Y', 'N', 'S', 'Y', 'Y', 'Y', '', 2, '', '', '', '', '', '', '', 'N', 'No', CHR(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); --dbms_output.put_line('Submit Accounting Request Id : '||l_request_id); --end loop; IF l_request_id = 0 THEN l_error := fnd_message.get; dbms_output.put_line(l_error); ELSE UPDATE FND_CONCURRENT_REQUESTS SET org_id = 204 WHERE request_id = l_request_id; COMMIT; --Commit, otherwise program is not submitted END IF; -- Recursive call to wait for the completion of process get_status_kids(l_request_id); BEGIN l_snap_id := DBMS_WORKLOAD_REPOSITORY.CREA
TE_SNAPSHOT (); dbms_output.put_line('Accouting_eid '|| l_snap_id); END; -- select count(*) into l_count from ar_rev_rec_qt; -- dbms_output.put_line('Lines to be processed from ar_rev_rec_qt: '||l_count); -- select count(*) into l_count from gl_je_lines; -- dbms_output.put_line('After Submit Act: Lines in gl_je_lines: '||l_count); -- select count(*) into l_count from xla_ae_lines; -- dbms_output.put_line('After Submit Act: Lines in xla_ae_lines: '||l_count); -- BEGIN -- select to_char(actual_start_date, 'YYYYMMDDHH24MI') -- into l_rr_start_time -- from fnd_concurrent_requests where request_id = l_autoinv_req_id; -- -- select to_char(actual_completion_date, 'YYYYMMDDHH24MI') -- into l_rr_end_time -- from fnd_concurrent_requests where request_id = l_request_id; -- EXCEPTION -- WHEN OTHERS THEN -- NULL; -- END; -- open check_rev_rec; -- loop -- fetch check_rev_rec into l_act_class, l_act_set_flag, l_count; -- EXIT when check_rev_rec%NOTFOUND; -- dbms_output.put_line('After SubAct: Account Class: '||l_act_class||' Account Set Flag: '||l_act_set_flag||' Count: '||l_count); -- end loop; -- close check_rev_rec; dbms_output.put_line('OTC Batch Completed'); exception when concurrent_program_failure then dbms_output.put_line('Exiting as '||l_conc_prg_name||' has errors for ReqId: ' || l_request_id); raise; when others then dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255)); raise; end; |
| 0wabkkyua3y3v | SELECT ITEM_KEY FROM WF_ITEMS WHERE ITEM_TYPE = :B2 AND ITEM_KEY = :B1 FOR UPDATE |
| 0wfry4rz1ygfk | SELECT /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/ OH.ORDER_TYPE_ID FROM OE_ORDER_HEADERS_ALL OH, WSH_DELIVERY_DETAILS WDD WHERE OH.HEADER_ID = WDD.SOURCE_HEADER_ID AND WDD.RELEASED_STATUS = 'S' AND WDD.SOURCE_CODE = 'OE' AND WDD.MOVE_ORDER_LINE_ID = :B1 |
| 0wrz2kbatczjk | SELECT EXECUTE_FLAG FROM JTF_USER_HOOKS WHERE PKG_NAME = :B4 AND API_NAME = :B3 AND PROCESSING_TYPE = :B2 AND USER_HOOK_TYPE = :B1 |
| 0z318y6g3uagc | declare l_retstat varchar2 ( 3 ) ; l_msg_data varchar2 ( 244 ) ; l_msg_cnt number ; l_trx_cnt number ; BEGIN :retval := INV_TXN_MANAGER_PUB . process_Transactions ( p_api_version => 1.0 , p_commit => fnd_api . g_true , x_return_status => l_retstat , x_msg_count => l_msg_cnt , x_msg_data => l_msg_data , x_trans_count => l_trx_cnt , p_table => 1 , p_header_id => :header_id ) ; END ; |
| 1cd2krbdzrhvq | insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_change#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phyblkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_number, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ftio.kcftiopyr, ftio.kcftiopyw, ftio.kcftiosbr, floor(ftio.kcftioprt / 10000), floor(ftio.kcftiopwt / 10000), floor(ftio.kcftiosbt / 10000), ftio.kcftiopbr, ftio.kcftiopbw, fw.count, fw.time from x$kcftio ftio, x$kcctf tf, x$kcbfwait fw, x$kccfn fn, x$kccts ts where ts.tstsn = tf.tftsn and ftio.kcftiofno = fn.fnfno and tf.tfnum = fn.fnfno and tf.tffnh = fn.fnnum and tf.tfdup <> 0 and fn.fntyp = 7 and fn.fnnam is not null and bitand(tf.tfsta, 32) <> 32 and fw.indx+1 = (fn.fnfno + :db_files) |
| 1cq3qr774cu45 | insert into WRH$_IOSTAT_FILETYPE (snap_id, dbid, instance_number, filetype_id, small_read_megabytes, small_write_megabytes, large_read_megabytes, large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_reqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_write_servicetime, small_sync_read_latency, large_read_servicetime, large_write_servicetime, retries_on_error) (select :snap_id, :dbid, :instance_number, filetype_id, sum(small_read_megabytes) small_read_megabytes, sum(small_write_megabytes) small_write_megabytes, sum(large_read_megabytes) large_read_megabytes, sum(large_write_megabytes) large_write_megabytes, sum(small_read_reqs) small_read_reqs, sum(small_write_reqs) small_write_reqs, sum(small_sync_read_reqs) small_sync_read_reqs, sum(large_read_reqs) large_read_reqs, sum(large_write_reqs) large_write_reqs, sum(small_read_servicetime) small_read_servicetime, sum(small_write_servicetime) small_write_servicetime, sum(small_sync_read_latency) small_sync_read_latency, sum(large_read_servicetime) large_read_servicetime, sum(large_write_servicetime) large_write_servicetime, sum(retries_on_error) retries_on_error from v$iostat_file group by filetype_id) |
| 1g62m2w49xngu | BEGIN XNP_EVENT.PROCESS_IN_EVT(:1, :2, :3, :4) ; END; |
| 1wnq9qthbb54q | BEGIN WSH_PICK_LIST.RELEASE_BATCH_SRS(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5); END; |
| 1xdjz3hr2u9wz |
SELECT /*+ leading(ct) index(ct, RA_CUSTOMER_TRX_N15) index(ctl, RA_CUSTOMER_TRX_LINES_N4) use_hash(ctl) */ ct.customer_trx_id, /* customer_trx_id */ ctl.customer_trx_line_id, /* customer_trx_line_id */ to_number(''), /* cust_trx_line_salesrep_id */ decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, nvl(ctl.revenue_amount, ctl.extended_amount), to_number('')), /* line_amount */ decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, decode(NULL, NULL, round((nvl(ctl.revenue_amount, ctl.extended_amount) * nvl(ct.exchange_rate, 1)), 2), round((nvl(ctl.revenue_amount, ctl.extended_amount) * nvl(ct.exchange_rate, 1)) / NULL) * NULL ), to_number('')), /* accounted_line_amount */ round( 100, 4), /* percent */ decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, nvl(ctl.revenue_amount, ctl.extended_amount), to_number('') ), /* amount */ decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, decode( NULL, NULL, round ((nvl(ctl.revenue_amount, ctl.extended_amount) * nvl(ct.exchange_rate, 1)), 2), round((nvl(ctl.revenue_amount, ctl.extended_amount) * nvl(ct.exchange_rate, 1)) / NULL ) * NULL ), to_number('')), /* acctd_amt */ 'TAX', /* account class */ decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, 'N', 'Y' ), /* account_set_flag */ ct.cust_trx_type_id, /* cust_trx_type_id */ decode(ct.invoicing_rule_id, -3, 'Y', 'N'), /* allow_not_open_flag */ to_char(''), /* concatenated segments */ :code_combination_id , /* code_combination_id */ decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, to_char(rgd.gl_date, 'J'), '' ), /* gl_date */ decode( :original_gl_date, NULL, decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoicing_rule_id ) ) ), NULL, to_char(rgd.original_gl_date, 'J'), '' ), decode(decode( NVL(:force_account_set_no, 'N'), 'N', ct.invoicing_rule_id, decode('TAX', 'UNBILL', ct.invoicing_rule_id, 'UNEARN', ct.invoicing_rule_id, decode(nvl(ctl.accounting_rule_duration, 0), 1, decode(nvl(ctl.autorule_duration_processed, 0), 0, ct.invoicing_rule_id, null), ct.invoi
cing_rule_id ) ) ), NULL, to_char(nvl(rgd.original_gl_date, :original_gl_date), 'J'), '' ) ), /* orig_gl_date */ nvl(ctl.default_ussgl_transaction_code, ct.default_ussgl_transaction_code), /* ussgl_trx_code */ nvl(ctl.default_ussgl_trx_code_context, ct.default_ussgl_trx_code_context), /* ussgl_trx_code_cntxt*/ ct.primary_salesrep_id, /* salesrep_id */ ctl_line.inventory_item_id, /* inventory_item_id */ ctl_line.memo_line_id, /* memo_line_id */ arp_etax_util.get_tax_account(ctl.customer_trx_line_id, rgd.gl_date, 'TAX'), arp_etax_util.get_tax_account(ctl.customer_trx_line_id, rgd.gl_date, 'INTERIM'), ct.bill_to_site_use_id, /* Billing site id */ ctl_line.warehouse_id /* Warehouse id */ , ctl.link_to_cust_trx_line_id /* 1651593 - tax errors */ FROM ra_customer_trx_lines ctl_line, fnd_currencies fc_foreign, ra_cust_trx_line_gl_dist rgd, ra_customer_trx_lines ctl, ra_customer_trx ct WHERE ct.customer_trx_id = ctl.customer_trx_id(+) AND ct.invoice_currency_code = fc_foreign.currency_code /* prevent duplicate records from being created */ AND not exists (SELECT /*+ INDEX (lgd RA_CUST_TRX_LINE_GL_DIST_N10)*/ 'distribution exists' FROM ra_cust_trx_line_gl_dist lgd WHERE ctl.customer_trx_id = lgd.customer_trx_id AND ctl.customer_trx_line_id = lgd.customer_trx_line_id and lgd.request_id = :request_id1 and 'TAX' = lgd.account_class and decode(ct.invoicing_rule_id, NULL, 'N', 'Y' ) = lgd.account_set_flag ) AND ct.customer_trx_id = rgd.customer_trx_id(+) AND 'REC' = rgd.account_class(+) AND 'N' = rgd.account_set_flag(+) AND ctl.line_type = 'TAX' AND ctl.link_to_cust_trx_line_id = ctl_line.customer_trx_line_id(+) and nvl(ct.invoicing_rule_id, -10) = nvl(ct.invoicing_rule_id, -10) AND ct.request_id = :request_id AND ctl.request_id (+) = :request_id AND ct.request_id is not null ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 |
| 1zj0h70du2jyx |
INSERT INTO WSH_DELIVERY_DETAILS( SOURCE_CODE, SOURCE_HEADER_ID, SOURCE_LINE_ID, CUSTOMER_ID, SOLD_TO_CONTACT_ID, INVENTORY_ITEM_ID, ITEM_DESCRIPTION, HAZARD_CLASS_ID, COUNTRY_OF_ORIGIN, SHIP_FROM_LOCATION_ID, SHIP_TO_LOCATION_ID, SHIP_TO_CONTACT_ID, SHIP_TO_SITE_USE_ID, DELIVER_TO_LOCATION_ID, DELIVER_TO_CONTACT_ID, DELIVER_TO_SITE_USE_ID, INTMED_SHIP_TO_LOCATION_ID, INTMED_SHIP_TO_CONTACT_ID, SHIP_TOLERANCE_ABOVE, SHIP_TOLERANCE_BELOW, REQUESTED_QUANTITY, REQUESTED_QUANTITY_UOM, SUBINVENTORY, REVISION, DATE_REQUESTED, DATE_SCHEDULED, MASTER_CONTAINER_ITEM_ID, DETAIL_CONTAINER_ITEM_ID, SHIP_METHOD_CODE, CARRIER_ID, FREIGHT_TERMS_CODE, SHIPMENT_PRIORITY_CODE, FOB_CODE, CUSTOMER_ITEM_ID, DEP_PLAN_REQUIRED_FLAG, CUSTOMER_PROD_SEQ, CUSTOMER_DOCK_CODE, CUST_MODEL_SERIAL_NUMBER, CUSTOMER_JOB, CUSTOMER_PRODUCTION_LINE, NET_WEIGHT, WEIGHT_UOM_CODE, VOLUME, VOLUME_UOM_CODE, TP_ATTRIBUTE_CATEGORY, TP_ATTRIBUTE1, TP_ATTRIBUTE2, TP_ATTRIBUTE3, TP_ATTRIBUTE4, TP_ATTRIBUTE5, TP_ATTRIBUTE6, TP_ATTRIBUTE7, TP_ATTRIBUTE8, TP_ATTRIBUTE9, TP_ATTRIBUTE10, TP_ATTRIBUTE11, TP_ATTRIBUTE12, TP_ATTRIBUTE13, TP_ATTRIBUTE14, TP_ATTRIBUTE15, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LAST_UPDATED_BY, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, MVT_STAT_STATUS, ORGANIZATION_ID, SHIP_SET_ID, ARRIVAL_SET_ID, SHIP_MODEL_COMPLETE_FLAG, TOP_MODEL_LINE_ID, SOURCE_HEADER_NUMBER, SOURCE_HEADER_TYPE_ID, SOURCE_HEADER_TYPE_NAME, CUST_PO_NUMBER, ATO_LINE_ID, SRC_REQUESTED_QUANTITY, SRC_REQUESTED_QUANTITY_UOM, CANCELLED_QUANTITY, TRACKING_NUMBER, SHIPPING_INSTRUCTIONS, PACKING_INSTRUCTIONS, PROJECT_ID, TASK_ID, ORG_ID, OE_INTERFACED_FLAG, INV_INTERFACED_FLAG, SOURCE_LINE_NUMBER, INSPECTION_FLAG, RELEASED_STATUS, DELIVERY_DETAIL_ID, CONTAINER_FLAG, GROSS_WEIGHT, SEAL_CODE, UNIT_NUMBER, UNIT_PRICE, CURRENCY_CODE, PREFERRED_GRADE, SRC_REQUESTED_QUANTITY2, SRC_REQUESTED_QUANTITY_UOM2, REQUESTED_QUANTITY2, CANCELLED_QUANTITY2, REQUESTED_QUANTITY_UOM2, PICKABLE_FLAG, ORIGINAL_SUBINVENTORY, RECEIVED_QUANTITY, RECEIVED_QUANTITY2, SOURCE_LINE_SET_ID, LINE_DIRECTION, IGNORE_FOR_PLANNING, EARLIEST_PICKUP_DATE, LATEST_PICKUP_DATE, EARLIEST_DROPOFF_DATE, LATEST_DROPOFF_DATE, SOURCE_DOCUMENT_TYPE_ID, SERVICE_LEVEL, MODE_OF_TRANSPORT, SOURCE_BLANKET_REFERENCE_ID, SOURCE_BLANKET_REFERENCE_NUM, VENDOR_ID, PARTY_ID, SHIP_FROM_SITE_ID, HOLD_CODE, SUPPLIER_ITEM_NUMBER, PO_SHIPMENT_LINE_ID, PO_SHIPMENT_LINE_NUMBER, SHIPPING_CONTROL, SOURCE_LINE_TYPE_CODE, PO_REVISION_NUMBER, RELEASE_REVISION_NUMBER, WV_FROZEN_FLAG, UNIT_WEIGHT, UNIT_VOLUME ) VALUES ( :B140 , :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , NVL(:B9 , -1), :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , NVL(:B20 , -1), NVL ( :B21 , 'XX'), :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , :B39 , :B40 , :B41 , :B42 , :B43 , :B44 , :B45 , :B46 , :B47 , :B48 , :B49 , :B50 , :B51 , :B52 , :B53 , :B54 , :B55 , :B56 , :B57 , :B58 , :B59 , :B60 , :B61 , :B62 , :B63 , :B64 , :B65 , :B66 , :B67 , :B68 , :B69 , :B70 , :B71 , :B72 , :B73 , :B74 , :B75 , :B142 , :B143 , :B143 , :B142 , :B142 , NULL, NULL, NULL, :B76 , :B77 , :B78 , :B79 , :B80 , :B81 , :B82 , :B83 , :B84 , :B85 , :B86 , :B87 , :B88 , :B89 , :B90 , :B91 , :B92 , :B93 , :B94 , :B141 , DECODE(:B140 , 'OE', 'N', 'X'), NVL(:B95 , DECODE(:B96 , 'Y', 'N', 'X')), :B97 , DECODE (:B140 , 'OKE' , NVL (:B98 , 'N') , 'N' ), :B99 , WSH_DELIVERY_DETAILS_S.NEXTVAL, 'N', :B100 , :B101 , :B102 ,
:B103 , :B104 , :B105 , :B106 , :B107 , :B108 , :B109 , :B110 , :B111 , :B112 , :B113 , :B114 , :B115 , DECODE(:B140 , 'OE', 'O', 'OKE', 'O', 'PO', DECODE(:B116 , 'Y', 'D', 'I'), 'O'), NVL(:B117 , 'N'), :B118 , :B119 , :B120 , :B121 , :B122 , :B123 , :B124 , :B125 , :B126 , :B127 , :B128 , :B129 , :B130 , :B131 , :B132 , :B133 , :B134 , :B135 , :B136 , :B137 , 'N' , :B138 , :B139 ) RETURNING DELIVERY_DETAIL_ID INTO :O0 |
| 23ysj88h2xpmf | SELECT TT.NAME FROM OE_TRANSACTION_TYPES_TL TT, OE_ORDER_HEADERS OH WHERE TT.LANGUAGE = ( SELECT LANGUAGE_CODE FROM FND_LANGUAGES WHERE INSTALLED_FLAG = 'B') AND TT.TRANSACTION_TYPE_ID = OH.ORDER_TYPE_ID AND OH.HEADER_ID = :B1 |
| 247b6t0095ck4 |
INSERT INTO OE_ORDER_LINES (ACCOUNTING_RULE_ID , ACCOUNTING_RULE_DURATION , ACTUAL_ARRIVAL_DATE , AGREEMENT_ID , ARRIVAL_SET_ID , ATO_LINE_ID , ATTRIBUTE1 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 , ATTRIBUTE19 , ATTRIBUTE2 , ATTRIBUTE20 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , AUTHORIZED_TO_SHIP_FLAG , BOOKED_FLAG , CANCELLED_FLAG , CANCELLED_QUANTITY , COMPONENT_CODE , COMPONENT_NUMBER , COMPONENT_SEQUENCE_ID , CONFIG_HEADER_ID , CONFIG_REV_NBR , CONFIG_DISPLAY_SEQUENCE , CONFIGURATION_ID , CONTEXT , CREATED_BY , CREATION_DATE , CREDIT_INVOICE_LINE_ID , CUSTOMER_DOCK_CODE , CUSTOMER_JOB , CUSTOMER_PRODUCTION_LINE , CUST_PRODUCTION_SEQ_NUM , CUST_MODEL_SERIAL_NUMBER , CUST_PO_NUMBER , CUSTOMER_LINE_NUMBER , DELIVERY_LEAD_TIME , DELIVER_TO_CONTACT_ID , DELIVER_TO_ORG_ID , DEMAND_BUCKET_TYPE_CODE , DEMAND_CLASS_CODE , EARLIEST_ACCEPTABLE_DATE , END_ITEM_UNIT_NUMBER , EXPLOSION_DATE , FOB_POINT_CODE , FREIGHT_CARRIER_CODE , FREIGHT_TERMS_CODE , GLOBAL_ATTRIBUTE1 , GLOBAL_ATTRIBUTE10 , GLOBAL_ATTRIBUTE11 , GLOBAL_ATTRIBUTE12 , GLOBAL_ATTRIBUTE13 , GLOBAL_ATTRIBUTE14 , GLOBAL_ATTRIBUTE15 , GLOBAL_ATTRIBUTE16 , GLOBAL_ATTRIBUTE17 , GLOBAL_ATTRIBUTE18 , GLOBAL_ATTRIBUTE19 , GLOBAL_ATTRIBUTE2 , GLOBAL_ATTRIBUTE20 , GLOBAL_ATTRIBUTE3 , GLOBAL_ATTRIBUTE4 , GLOBAL_ATTRIBUTE5 , GLOBAL_ATTRIBUTE6 , GLOBAL_ATTRIBUTE7 , GLOBAL_ATTRIBUTE8 , GLOBAL_ATTRIBUTE9 , GLOBAL_ATTRIBUTE_CATEGORY , HEADER_ID , INDUSTRY_ATTRIBUTE1 , INDUSTRY_ATTRIBUTE10 , INDUSTRY_ATTRIBUTE11 , INDUSTRY_ATTRIBUTE12 , INDUSTRY_ATTRIBUTE13 , INDUSTRY_ATTRIBUTE14 , INDUSTRY_ATTRIBUTE15 , INDUSTRY_ATTRIBUTE16 , INDUSTRY_ATTRIBUTE17 , INDUSTRY_ATTRIBUTE18 , INDUSTRY_ATTRIBUTE19 , INDUSTRY_ATTRIBUTE20 , INDUSTRY_ATTRIBUTE21 , INDUSTRY_ATTRIBUTE22 , INDUSTRY_ATTRIBUTE23 , INDUSTRY_ATTRIBUTE24 , INDUSTRY_ATTRIBUTE25 , INDUSTRY_ATTRIBUTE26 , INDUSTRY_ATTRIBUTE27 , INDUSTRY_ATTRIBUTE28 , INDUSTRY_ATTRIBUTE29 , INDUSTRY_ATTRIBUTE30 , INDUSTRY_ATTRIBUTE2 , INDUSTRY_ATTRIBUTE3 , INDUSTRY_ATTRIBUTE4 , INDUSTRY_ATTRIBUTE5 , INDUSTRY_ATTRIBUTE6 , INDUSTRY_ATTRIBUTE7 , INDUSTRY_ATTRIBUTE8 , INDUSTRY_ATTRIBUTE9 , INDUSTRY_CONTEXT , INVENTORY_ITEM_ID , INVOICE_TO_CONTACT_ID , INVOICE_TO_ORG_ID , INVOICING_RULE_ID , ORDERED_ITEM_ID , ITEM_IDENTIFIER_TYPE , ORDERED_ITEM , CUSTOMER_ITEM_NET_PRICE , ITEM_REVISION , ITEM_TYPE_CODE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LATEST_ACCEPTABLE_DATE , LINE_CATEGORY_CODE , LINE_ID , LINE_NUMBER , LINE_TYPE_ID , LINK_TO_LINE_ID , MODEL_GROUP_NUMBER , MFG_LEAD_TIME , OPEN_FLAG , OPTION_FLAG , OPTION_NUMBER , ORDERED_QUANTITY , ORDERED_QUANTITY2 , ORDER_QUANTITY_UOM , ORDERED_QUANTITY_UOM2 , ORG_ID , ORIG_SYS_DOCUMENT_REF , ORIG_SYS_LINE_REF , ORIG_SYS_SHIPMENT_REF , CHANGE_SEQUENCE , OVER_SHIP_REASON_CODE , OVER_SHIP_RESOLVED_FLAG , PAYMENT_TERM_ID , PREFERRED_GRADE , PRICE_LIST_ID , PRICING_ATTRIBUTE1 , PRICING_ATTRIBUTE10 , PRICING_ATTRIBUTE2 , PRICING_ATTRIBUTE3 , PRICING_ATTRIBUTE4 , PRICING_ATTRIBUTE5 , PRICING_ATTRIBUTE6 , PRICING_ATTRIBUTE7 , PRICING_ATTRIBUTE8 , PRICING_ATTRIBUTE9 , PRICING_CONTEXT , PRICING_DATE , PRICING_QUANTITY , PRICING_QUANTITY_UOM , PROJECT_ID , PROMISE_DATE , RE_SOURCE_FLAG , REFERENCE_HEADER_ID , REFERENCE_LINE_ID , REFERENCE_TYPE , REQUEST_DATE , REQUEST_ID , RETURN_ATTRIBUTE1 , RETURN_ATTRIBUTE10 , RETURN_ATTRIBUTE11 , RETURN_ATTRIBUTE12 , RETURN_ATTRIBUTE13 , RETURN_ATTRIBUTE14 , RETURN_ATTRIBUTE15 , RETURN_ATTRIBUTE2 , RETURN_ATTRIBUTE3 , RETURN_ATTRIBUTE4 , RETURN_ATTRIBUTE5 , RETURN_ATTRIBUTE6 , RETURN_ATTRIBUTE7 , RETURN_ATTRIBUTE8 , RETURN_ATTRIBUTE9 , RETURN_CONTEXT , RETURN_REASON_CODE , SALESREP_ID , SCHEDULE_ARRIVAL_DATE , SCHEDULE_SHIP_DATE , SCHEDULE_STATUS_CODE , SHIPMENT_NUMBER , SHIPMENT_PRIORITY_CODE , SHIPPED_QUANTITY , SHIPPED_QUANTITY2 , SHIPPING_METHOD_CODE , SHIPPING_QUANTITY , SHIPPING_QUANTITY2 , SHIPPING_QUANTITY_UOM , SHIP_FROM_ORG_ID , SUBINVENTORY , SHIP_SET_
ID , SHIP_TOLERANCE_ABOVE , SHIP_TOLERANCE_BELOW , SHIPPABLE_FLAG , SHIPPING_INTERFACED_FLAG , SHIP_TO_CONTACT_ID , SHIP_TO_ORG_ID , SHIP_MODEL_COMPLETE_FLAG , SOLD_TO_ORG_ID , SOLD_FROM_ORG_ID , SORT_ORDER , SOURCE_DOCUMENT_ID , SOURCE_TYPE_CODE , SPLIT_FROM_LINE_ID , MODEL_REMNANT_FLAG , TASK_ID , TAX_CODE , TAX_DATE , TAX_EXEMPT_FLAG , TAX_EXEMPT_NUMBER , TAX_EXEMPT_REASON_CODE , TAX_POINT_CODE , TAX_VALUE , TOP_MODEL_LINE_ID , UNIT_LIST_PRICE , UNIT_LIST_PRICE_PER_PQTY , UNIT_SELLING_PRICE , UNIT_SELLING_PRICE_PER_PQTY , VISIBLE_DEMAND_FLAG , VEH_CUS_ITEM_CUM_KEY_ID , SHIPPING_INSTRUCTIONS , PACKING_INSTRUCTIONS , SERVICE_TXN_REASON_CODE , SERVICE_TXN_COMMENTS , SERVICE_DURATION , SERVICE_PERIOD , SERVICE_START_DATE , SERVICE_END_DATE , SERVICE_COTERMINATE_FLAG , UNIT_LIST_PERCENT , UNIT_SELLING_PERCENT , UNIT_PERCENT_BASE_PRICE , SERVICE_NUMBER , SERVICE_REFERENCE_TYPE_CODE , TP_CONTEXT , TP_ATTRIBUTE1 , TP_ATTRIBUTE2 , TP_ATTRIBUTE3 , TP_ATTRIBUTE4 , TP_ATTRIBUTE5 , TP_ATTRIBUTE6 , TP_ATTRIBUTE7 , TP_ATTRIBUTE8 , TP_ATTRIBUTE9 , TP_ATTRIBUTE10 , TP_ATTRIBUTE11 , TP_ATTRIBUTE12 , TP_ATTRIBUTE13 , TP_ATTRIBUTE14 , TP_ATTRIBUTE15 , FLOW_STATUS_CODE , CALCULATE_PRICE_FLAG , COMMITMENT_ID , ORDER_SOURCE_ID , USER_ITEM_DESCRIPTION , LOCK_CONTROL , FIRM_DEMAND_FLAG , TRANSACTION_PHASE_CODE , END_CUSTOMER_CONTACT_ID , END_CUSTOMER_ID , END_CUSTOMER_SITE_USE_ID , IB_OWNER , IB_CURRENT_LOCATION , IB_INSTALLED_AT_LOCATION ) VALUES (:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , FND_GLOBAL.USER_ID , SYSDATE , :B39 , :B40 , :B41 , :B42 , :B43 , :B44 , :B45 , :B46 , :B47 , :B48 , :B49 , :B50 , :B51 , :B52 , :B53 , :B54 , :B55 , :B56 , :B57 , :B58 , :B59 , :B60 , :B61 , :B62 , :B63 , :B64 , :B65 , :B66 , :B67 , :B68 , :B69 , :B70 , :B71 , :B72 , :B73 , :B74 , :B75 , :B76 , :B77 , :B78 , :B79 , :B80 , :B81 , :B82 , :B83 , :B84 , :B85 , :B86 , :B87 , :B88 , :B89 , :B90 , :B91 , :B92 , :B93 , :B94 , :B95 , :B96 , :B97 , :B98 , :B99 , :B100 , :B101 , :B102 , :B103 , :B104 , :B105 , :B106 , :B107 , :B108 , :B109 , :B110 , :B111 , :B112 , :B113 , :B114 , :B115 , :B116 , :B117 , :B118 , :B119 , :B120 , FND_GLOBAL.USER_ID , SYSDATE , :B121 , :B122 , :B123 , :B124 , :B125 , :B126 , :B127 , :B128 , 'Y' , :B129 , :B130 , :B131 , :B132 , :B133 , :B134 , :B135 , :B136 , :B137 , :B138 , :B139 , :B140 , :B141 , :B142 , :B143 , :B144 , :B145 , :B146 , :B147 , :B148 , :B149 , :B150 , :B151 , :B152 , :B153 , :B154 , :B155 , :B156 , :B157 , :B158 , :B159 , :B160 , :B161 , :B162 , :B163 , :B164 , :B165 , :B268 , :B166 , :B167 , :B168 , :B169 , :B170 , :B171 , :B172 , :B173 , :B174 , :B175 , :B176 , :B177 , :B178 , :B179 , :B180 , :B181 , :B182 , :B183 , :B184 , :B185 , :B186 , 1 , :B187 , :B188 , :B189 , :B190 , :B191 , :B192 , :B193 , :B194 , :B195 , :B196 , :B197 , :B198 , :B199 , 'N' , :B200 , :B201 , :B202 , :B203 , :B267 , :B204 , :B205 , :B206 , :B207 , :B208 , :B209 , :B210 , :B211 , :B212 , :B213 , :B214 , :B215 , :B216 , :B217 , :B218 , :B219 , :B220 , :B221 , :B222 , :B223 , :B224 , :B225 , :B226 , :B227 , :B228 , :B229 , :B230 , :B231 , :B232 , :B233 , :B234 , :B235 , :B236 , :B237 , :B238 , :B239 , :B240 , :B241 , :B242 , :B243 , :B244 , :B245 , :B246 , :B247 , :B248 , :B249 , :B250 , :B251 , :B252 , :B253 , DECODE(:B254 , 'Y', 'BOOKED', 'ENTERED') , :B255 , :B256 , :B257 , :B258 , :B259 , :B260 , 'F' , :B261 , :B262 , :B263 , :B264 , :B265 , :B266 ) |
| 2v1vanmps8nck | SELECT NVL(OT.CUST_TRX_TYPE_ID, 0) FROM OE_ORDER_TYPES_V OT, OE_ORDER_HEADERS_ALL OH WHERE OT.ORDER_TYPE_ID = OH.ORDER_TYPE_ID AND OH.HEADER_ID = :B1 |
| 33838u0xy9fk9 |
INSERT INTO MTL_MATERIAL_TRANSACTIONS ( TRANSACTION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID, REVISION, TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_NAME, TRANSACTION_QUANTITY, TRANSACTION_UOM, PRIMARY_QUANTITY, TRANSACTION_DATE, TRANSACTION_COST, ACTUAL_COST, PRIOR_COST, NEW_COST, QUANTITY_ADJUSTED, VARIANCE_AMOUNT, TRANSFER_COST, TRANSPORTATION_COST, TRANSFER_TRANSACTION_ID, TRANSFER_ORGANIZATION_ID, TRANSFER_SUBINVENTORY, TRANSFER_LOCATOR_ID , ACCT_PERIOD_ID, TRANSACTION_REFERENCE, REASON_ID, REPETITIVE_LINE_ID, DEPARTMENT_ID, OPERATION_SEQ_NUM, SHIPMENT_NUMBER, FREIGHT_CODE, WAYBILL_AIRBILL, TRX_SOURCE_LINE_ID, MOVE_ORDER_LINE_ID , INVOICED_FLAG , DISTRIBUTION_ACCOUNT_ID , ENCUMBRANCE_ACCOUNT , ENCUMBRANCE_AMOUNT , COST_UPDATE_ID , PROJECT_ID , TASK_ID , TO_PROJECT_ID , TO_TASK_ID , LPN_ID , TRANSFER_LPN_ID, CONTENT_LPN_ID , PUT_AWAY_STRATEGY_ID , PUT_AWAY_RULE_ID , PICK_STRATEGY_ID , PICK_RULE_ID , CREATED_BY , COST_GROUP_ID , TRANSFER_COST_GROUP_ID , TRANSACTION_SET_ID , PM_COST_COLLECTED , COST_CATEGORY_ID , ORGANIZATION_TYPE , TRANSFER_ORGANIZATION_TYPE , OWNING_ORGANIZATION_ID , OWNING_TP_TYPE , PLANNING_ORGANIZATION_ID , PLANNING_TP_TYPE , XFR_OWNING_ORGANIZATION_ID , TRANSFER_OWNING_TP_TYPE , XFR_PLANNING_ORGANIZATION_ID , TRANSFER_PLANNING_TP_TYPE , SECONDARY_TRANSACTION_QUANTITY , SECONDARY_UOM_CODE, PICK_SLIP_NUMBER, PICKING_LINE_ID, VENDOR_LOT_NUMBER, SOURCE_CODE, SOURCE_LINE_ID, MOVE_TRANSACTION_ID, RCV_TRANSACTION_ID, COMPLETION_TRANSACTION_ID, CURRENCY_CODE, CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE, MATERIAL_ACCOUNT, MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT, OVERHEAD_ACCOUNT, MOVEMENT_ID, SOURCE_PROJECT_ID, SOURCE_TASK_ID, PA_EXPENDITURE_ORG_ID, EXPENDITURE_TYPE, TRANSFER_PERCENTAGE, ORG_COST_GROUP_ID, COST_TYPE_ID, VALUE_CHANGE, PERCENTAGE_CHANGE, EMPLOYEE_CODE, MASTER_SCHEDULE_UPDATE_CODE, TRX_SOURCE_DELIVERY_ID, RECEIVING_DOCUMENT, TRANSPORTATION_DIST_ACCOUNT, NUMBER_OF_CONTAINERS, RMA_LINE_ID, CYCLE_COUNT_ID, PHYSICAL_ADJUSTMENT_ID, FLOW_SCHEDULE, FINAL_COMPLETION_FLAG, QA_COLLECTION_ID, OVERCOMPLETION_TRANSACTION_QTY, OVERCOMPLETION_PRIMARY_QTY, OVERCOMPLETION_TRANSACTION_ID, COMMON_BOM_SEQ_ID, COMMON_ROUTING_SEQ_ID, MVT_STAT_STATUS, PICK_SLIP_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, SHIP_TO_LOCATION_ID, TRANSACTION_MODE, TRANSACTION_BATCH_ID, TRANSACTION_BATCH_SEQ, FOB_POINT, INTRANSIT_ACCOUNT, TRX_FLOW_HEADER_ID, LOGICAL_TRX_TYPE_CODE, ORIGINAL_TRANSACTION_TEMP_ID, SHORTAGE_PROCESS_CODE, TRANSFER_PRICE, COSTED_FLAG, OPM_COSTED_FLAG ) VALUES(:1, SYSDATE, :2, SYSDATE, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, ROUND(:17, 5), :18, ROUND(:19, 5), nvl(:20, sysdate), :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, 'N', :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, ROUND(:75, 5), :76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96, :97, :98, :99, :100, :101, :102, :103, :104, :105, :106, :107, :108, :109, :110, :111, :112, :113, :114, :115, :116, :117, :118, :119, :120, 'NEW', :121, :122, :123, :124, :125, :126, :127, :128, :129, :130, :131, :132, :133, :134, :135, :136, :137, :138, :139, :140, :141, :142, :143, :144, :145, :146, :147, :148, Decode(:149,
'D', 'N', NULL), DECODE(:150, 'O', 'N', NULL)) |
| 37f1qguwxw0sk | begin IF ( :v_msg_level:i_msg_level > 0 ) THEN arp_util . enable_debug ( 1000000 ) ; arp_global . msg_level := :v_msg_level:i_msg_level ; END IF ; arp_auto_accounting . g_error_buffer := NULL ; :v_num_failed_dist_rows:i_num_failed_dist_rows := 0 ; :v_coa_id:i_coa_id := arp_standard . gl_chart_of_accounts_id ; :v_retval:i_retval := 1 ; arp_auto_accounting . do_autoaccounting ( :v_mode:i_vmode , :v_account_class , :v_trx_id:i_trx_id , :v_line_id:i_line_id , :v_line_salesrep_id:i_line_salesrep_id , :v_request_id:i_request_id , :v_gl_date , :v_orig_gl_date , :v_total_trx_amount:i_total_trx_amount , :v_passed_ccid:i_passed_ccid , :v_force_acct_set_no , :v_cust_trx_type_id:i_cust_trx_type_id , :v_primary_salesrep_id:i_primary_salesrep_id , :v_inv_item_id:i_inv_item_id , :v_memo_line_id:i_memo_line_id , :v_ccid:i_ccid , :v_concat_segments:i_concat_segments , :v_num_failed_dist_rows:i_num_failed_dist_rows ) ; IF ( :v_mode:i_vmode = 'G' AND :v_ccid:i_ccid = -1 ) THEN :v_retval:i_retval := 0 ; END IF ; EXCEPTION WHEN arp_auto_accounting . no_ccid THEN :v_retval:i_retval := 0 ; WHEN NO_DATA_FOUND THEN NULL ; WHEN OTHERS THEN arp_util . debug ( 'arcdaa()' ) ; arp_util . debug ( SQLERRM ) ; :v_errorbuf:i_errorbuf := arp_auto_accounting . g_error_buffer ; :v_sqlcode:i_sqlcode := SQLCODE ; END ; |
| 3b3psftuyks81 | BEGIN INV_TXNSTUB_PUB.postTransaction(:1, :2, :3); END; |
| 3dmv51agpx0sx | SELECT NVL(LT.INVOICE_SOURCE_ID, OT.INVOICE_SOURCE_ID) FROM OE_LINE_TYPES_V LT, OE_ORDER_TYPES_V OT, OE_ORDER_HEADERS_ALL OH WHERE LT.LINE_TYPE_ID = :B2 AND OT.ORDER_TYPE_ID = OH.ORDER_TYPE_ID AND OH.HEADER_ID = :B1 |
| 3gn7673kd3f2m | insert into RA_INTERFACE_ERRORS (INTERFACE_LINE_ID, MESSAGE_TEXT, INVALID_VALUE, ORG_ID)select IL.interface_line_id , :b0 , null , T.org_id from RA_CUSTOMER_TRX T , RA_TERMS TRM , AR_CONS_BILL_CYCLES_B CB , RA_INTERFACE_LINES_GT IL where (((((T.request_id=:b1 and T.term_id=TRM.term_id) and TRM.billing_cycle_id=CB.billing_cycle_id) and CB.bill_cycle_type='EVENT') and T.billing_date is null ) and IL.customer_trx_id=T.customer_trx_id) |
| 3ktacv9r56b51 | select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# |
| 3wtnuzdm579hg | UPDATE RA_INTERFACE_LINES_GT SET PAYMENT_TRXN_EXTENSION_ID = :B2 WHERE CUSTOMER_TRX_ID = :B1 AND REQUEST_ID = FND_GLOBAL . CONC_REQUEST_ID AND RECEIPT_METHOD_ID IS NOT NULL |
| 4y08a52989vfk | BEGIN WSH_PICK_LIST.RELEASE_BATCH_SUB(:errbuf, :rc, :A0, :A1, :A2, :A3); END; |
| 4yak7x2zqz0dh | BEGIN inv_transfer_order_pvt.finalize_pick_confirm(:1, :2, :3, :4, :5, :6, :7); END; |
| 50thu59cud6yu | SELECT NVL(M.COST_OF_SALES_ACCOUNT, 0) FROM OE_ORDER_LINES OL, MTL_SYSTEM_ITEMS M WHERE OL.LINE_ID = :B1 AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID |
| 5b7p43uucnqp7 | SELECT LINK_TO_LINE_ID, ITEM_TYPE_CODE FROM OE_ORDER_LINES WHERE LINE_ID = :B1 |
| 5fky033hvxn3s | SELECT 'X' FROM WSH_TRIP_STOPS WTS, WSH_DELIVERY_LEGS WDL, WSH_DELIVERY_ASSIGNMENTS WDA , WSH_DELIVERY_DETAILS WDD WHERE WTS.BATCH_ID = :B1 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID AND WDA.DELIVERY_ID = WDL.DELIVERY_ID AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND WDD.CONTAINER_FLAG = 'N' AND WDD.SOURCE_CODE = 'OE' AND (EXISTS (SELECT 'any non-cancelled line outside batch' FROM WSH_DELIVERY_DETAILS WDD2 WHERE WDD2.SOURCE_LINE_ID = WDD.SOURCE_LINE_ID AND WDD2.SOURCE_CODE = 'OE' AND WDD2.CONTAINER_FLAG = 'N' AND WDD2.RELEASED_STATUS <> 'D' AND WDD2.DELIVERY_DETAIL_ID NOT IN (SELECT WDA3.DELIVERY_DETAIL_ID FROM WSH_DELIVERY_ASSIGNMENTS WDA3 , WSH_DELIVERY_LEGS WDL3, WSH_TRIP_STOPS WTS3, WSH_DELIVERY_DETAILS WDD3 WHERE WTS3.BATCH_ID = :B1 AND WDL3.PICK_UP_STOP_ID = WTS3.STOP_ID AND WDA3.DELIVERY_ID = WDL3.DELIVERY_ID AND WDD3.DELIVERY_DETAIL_ID = WDA3.DELIVERY_DETAIL_ID AND WDD3.SOURCE_LINE_ID = WDD.SOURCE_LINE_ID AND WDD3.SOURCE_CODE = 'OE' AND WDD3.CONTAINER_FLAG = 'N')) OR WDD.TOP_MODEL_LINE_ID IS NOT NULL OR WDD.SHIP_SET_ID IS NOT NULL OR NVL(WDD.SHIP_MODEL_COMPLETE_FLAG, 'N') = 'Y' OR WDD.REQUESTED_QUANTITY <> NVL(WDD.SHIPPED_QUANTITY, -99) OR NVL(REQUESTED_QUANTITY2, -99) <> NVL(SHIPPED_QUANTITY2, -99) OR WDD.SOURCE_LINE_SET_ID IS NOT NULL ) AND ROWNUM = 1 |
| 5t39uchjqpyfm | BEGIN xla_accounting_pkg.unit_processor_batch(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8, :A9, :A10, :A11, :A12, :A13, :A14); END; |
| 5t6wystf9zvbb | BEGIN OE_BULK_ORDER_IMPORT_PVT.ORDER_IMPORT_CONC_PGM(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8, :A9, :A10, :A11, :A12, :A13, :A14, :A15); END; |
| 5tyf16m9cmxb2 | update RA_INTERFACE_LINES_GT L set RECEIPT_METHOD_ID=(select SUBSTRB(min(DECODE(RM.SITE_USE_ID, null , ('2'||TO_CHAR(RM.RECEIPT_METHOD_ID)), ('1'||TO_CHAR(RM.RECEIPT_METHOD_ID)))), 2) from HZ_CUST_SITE_USES SU , RA_CUST_RECEIPT_METHODS RM , AR_RECEIPT_METHOD_ACCOUNTS A , CE_BANK_ACCOUNTS CBA , CE_BANK_ACCT_USES B , AR_RECEIPT_METHODS arm , AR_RECEIPT_CLASSES rc where (((((((((((((L.ORIG_SYSTEM_BILL_CUSTOMER_ID=RM.CUSTOMER_ID and L.ORIG_SYSTEM_BILL_ADDRESS_ID=SU.CUST_ACCT_SITE_ID) and SU.SITE_USE_CODE='BILL_TO') and SU.STATUS='A') and SU.SITE_USE_ID=NVL(RM.SITE_USE_ID, SU.SITE_USE_ID)) and RM.PRIMARY_FLAG='Y') and L.TRX_DATE between NVL(RM.START_DATE, L.TRX_DATE) and NVL(RM.END_DATE, L.TRX_DATE)) and RM.RECEIPT_METHOD_ID=A.RECEIPT_METHOD_ID) and RM.RECEIPT_METHOD_ID=arm.receipt_method_id) and ARM.RECEIPT_CLASS_ID=RC.RECEIPT_CLASS_ID) and RC.CREATION_METHOD_CODE in ('MANUAL', 'AUTOMATIC')) and A.REMIT_BANK_ACCT_USE_ID=B.BANK_ACCT_USE_ID) and CBA.BANK_ACCOUNT_ID=B.BANK_ACCOUNT_ID) and DECODE(CBA.RECEIPT_MULTI_CURRENCY_FLAG, 'Y', L.CURRENCY_CODE, CBA.CURRENCY_CODE)=L.CURRENCY_CODE)) where ((((REQUEST_ID=:b0 and RECEIPT_METHOD_ID is null ) and CUSTOMER_TRX_ID is not null ) and NVL(INTERFACE_STATUS, '~')<>'P') and PAYMENT_TYPE_CODE is null ) |
| 65qczrm0turbh | UPDATE OE_ORDER_LINES SET OPEN_FLAG = :B7 , CALCULATE_PRICE_FLAG = :B6 , FLOW_STATUS_CODE = :B5 , LAST_UPDATED_BY = :B4 , LAST_UPDATE_LOGIN = :B3 , LAST_UPDATE_DATE = :B2 , LOCK_CONTROL = :B1 WHERE LINE_ID = :B8 |
| 66tmsr3446uqn | SELECT WIAS.ACTIVITY_STATUS, WIAS.ACTIVITY_RESULT_CODE, WIAS.ASSIGNED_USER, WIAS.NOTIFICATION_ID, WIAS.BEGIN_DATE, WIAS.END_DATE, WIAS.DUE_DATE, WIAS.ERROR_NAME, WIAS.ERROR_MESSAGE, WIAS.ERROR_STACK FROM WF_ITEM_ACTIVITY_STATUSES WIAS WHERE WIAS.ITEM_TYPE = :B3 AND WIAS.ITEM_KEY = :B2 AND WIAS.PROCESS_ACTIVITY = :B1 |
| 6abn0zw8kkvtk | begin arp_etax_autoinv_util . calculate_tax ( :request_id , :phase , :num_tax_failures , :return_status:i_return_status ) ; END ; |
| 6hnnphk96ubbn | SELECT CREATION_SIGN FROM RA_CUST_TRX_TYPES WHERE CUST_TRX_TYPE_ID = :B1 |
| 6mu4cfrayu648 | SELECT 'x' FROM WSH_PR_WORKERS WHERE ROWID = :B1 AND PROCESSED = 'N' FOR UPDATE NOWAIT |
| 6y2m7nrx0s6tn | BEGIN DECLARE l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE ; BEGIN l_msg_header.message_id := :message_id ; l_msg_header.message_code := :message_code ; l_msg_header.reference_id := :reference_id ; l_msg_header.opp_reference_id := :opp_ref_id ; l_msg_header.creation_date := :creation_date; l_msg_header.sender_name := :sender_name ; l_msg_header.recipient_name := :recipient_name ; l_msg_header.version := :version; l_msg_header.direction_indr := :direction_indr ; l_msg_header.order_id := :order_id ; l_msg_header.wi_instance_id := :wi_instance_id ; l_msg_header.fa_instance_id := :fa_instance_id ; XNP_CSISUBTR_U.DEFAULT_PROCESS( l_msg_header, :l_msg_body, :error_code, :error_message ) ; END ; END ; |
| 6yqjdbt3s35xr |
declare l_return_status VARCHAR2 ( 2000 ) ; l_msg_count NUMBER ; l_msg_data VARCHAR2 ( 2000 ) ; l_instrument_assignment_id NUMBER ; l_response_rec IBY_FNDCPT_COMMON_PUB . RESULT_REC_TYPE ; l_payment_trxn_extension_id NUMBER ; l_payer_rec IBY_FNDCPT_COMMON_PUB . PAYERCONTEXT_REC_TYPE ; l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB . TRXNEXTENSION_REC_TYPE ; l_cust_account_id NUMBER ; l_cust_site_use_id NUMBER ; l_party_id NUMBER ; l_org_id NUMBER ; l_org_type VARCHAR2 ( 30 ) ; l_payment_function VARCHAR2 ( 30 ) ; l_payment_channel VARCHAR2 ( 30 ) ; l_instrument_type VARCHAR2 ( 30 ) ; cursor lines_rec_cur is select distinct l . customer_trx_id , l . trx_number , l . paying_customer_id , l . paying_site_use_id , l . orig_system_bill_customer_id , l . orig_system_bill_address_id , l . org_id , l . receipt_method_id , l . currency_code , l . trx_date , l . mandate_last_trx_flag , rm . payment_channel_code , pc . instrument_type , su . site_use_id bill_to_site_use_id from ra_interface_lines_gt l , ar_receipt_methods rm , ar_receipt_classes rc , iby_fndcpt_pmt_chnnls_b pc , hz_cust_site_uses su where l . request_id = fnd_global . conc_request_id and l . receipt_method_id is not null and l . customer_trx_id is not null and l . payment_trxn_extension_id is null and nvl ( l . interface_status , '~' ) != 'P' and rm . receipt_method_id = l . receipt_method_id and rm . receipt_class_id = rc . receipt_class_id and rc . creation_method_code in ( 'AUTOMATIC' , 'BR' ) and rm . payment_channel_code = pc . payment_channel_code and su . site_use_code = 'BILL_TO' and su . cust_acct_site_id = l . orig_system_bill_address_id ; BEGIN arp_standard . debug ( 'Default Payment Transaction Extension ID()+' ) ; FOR line_rec IN lines_rec_cur LOOP l_payment_channel := line_rec . payment_channel_code ; l_instrument_type := line_rec . instrument_type ; l_instrument_assignment_id := NULL ; l_payment_trxn_extension_id := NULL ; IF l_instrument_type = 'CREDITCARD' THEN IF :h_pmt_det_def_hierarchy = 'PARENT' THEN Select substrb ( max ( decode ( acct_site_use_id , NULL , '1' || to_char ( instr_assignment_id ) , '2' || to_char ( instr_assignment_id ) ) ) , 2 ) into l_instrument_assignment_id from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'CREDITCARD' and order_of_preference = 1 and cust_account_id = line_rec . paying_customer_id and line_rec . paying_site_use_id = nvl ( acct_site_use_id , line_rec . paying_site_use_id ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ; END IF ; IF l_instrument_assignment_id IS NULL THEN Select substrb ( max ( decode ( acct_site_use_id , NULL , '1' || to_char ( instr_assignment_id ) , '2' || to_char ( instr_assignment_id ) ) ) , 2 ) into l_instrument_assignment_id from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'CREDITCARD' and order_of_preference = 1 and cust_account_id = line_rec . orig_system_bill_customer_id and line_rec . bill_to_site_use_id = nvl ( acct_site_use_id , line_rec . bill_to_site_use_id ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ; END IF ; IF :h_pmt_det_def_hierarchy = 'BILL_TO' AND l_instrument_assignment_id IS NULL THEN Select substrb ( max ( decode ( acct_site_use_id , NULL , '1' || to_char ( instr_assignment_id ) , '2' || to_char ( instr_assignment_id ) ) ) , 2 ) into l_instrument_assignment_id from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'CREDITCARD' and order_of_preference = 1 and cust_account_id = line_rec . paying_customer_id and line_rec . paying_site_use_id = nvl ( acct_site_use_id , line_rec . paying_site_use_id ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ; END IF ; ELSIF l_instrument_type = 'BANKACCOUNT' THEN IF :h_pmt_det_def_hierarchy = 'PARENT' THEN Sel
ect substrb ( min ( decode ( acct_site_use_id , NULL , decode ( currency_code , NULL , '4' || to_char ( instr_assignment_id ) , '3' || to_char ( instr_assignment_id ) ) , decode ( currency_code , NULL , '2' || to_char ( instr_assignment_id ) , '1' || to_char ( instr_assignment_id ) ) ) ) , 2 ) into l_instrument_assignment_id from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'BANKACCOUNT' and cust_account_id = line_rec . paying_customer_id and line_rec . paying_site_use_id = nvl ( acct_site_use_id , line_rec . paying_site_use_id ) and line_rec . currency_code = nvl ( currency_code , line_rec . currency_code ) and order_of_preference = ( select substrb ( min ( decode ( acct_site_use_id , NULL , decode ( currency_code , NULL , '4' || to_char ( order_of_preference ) , '3' || to_char ( order_of_preference ) ) , decode ( currency_code , NULL , '2' || to_char ( order_of_preference ) , '1' || to_char ( order_of_preference ) ) ) ) , 2 ) from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'BANKACCOUNT' and cust_account_id = line_rec . paying_customer_id and line_rec . paying_site_use_id = nvl ( acct_site_use_id , line_rec . paying_site_use_id ) and line_rec . currency_code = nvl ( currency_code , line_rec . currency_code ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ; END IF ; IF l_instrument_assignment_id IS NULL THEN Select substrb ( min ( decode ( acct_site_use_id , NULL , decode ( currency_code , NULL , '4' || to_char ( instr_assignment_id ) , '3' || to_char ( instr_assignment_id ) ) , decode ( currency_code , NULL , '2' || to_char ( instr_assignment_id ) , '1' || to_char ( instr_assignment_id ) ) ) ) , 2 ) into l_instrument_assignment_id from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'BANKACCOUNT' and cust_account_id = line_rec . orig_system_bill_customer_id and line_rec . bill_to_site_use_id = nvl ( acct_site_use_id , line_rec . bill_to_site_use_id ) and line_rec . currency_code = nvl ( currency_code , line_rec . currency_code ) and order_of_preference = ( select substrb ( min ( decode ( acct_site_use_id , NULL , decode ( currency_code , NULL , '4' || to_char ( order_of_preference ) , '3' || to_char ( order_of_preference ) ) , decode ( currency_code , NULL , '2' || to_char ( order_of_preference ) , '1' || to_char ( order_of_preference ) ) ) ) , 2 ) from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'BANKACCOUNT' and cust_account_id = line_rec . orig_system_bill_customer_id and line_rec . bill_to_site_use_id = nvl ( acct_site_use_id , line_rec . bill_to_site_use_id ) and line_rec . currency_code = nvl ( currency_code , line_rec . currency_code ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ; END IF ; IF :h_pmt_det_def_hierarchy = 'BILL_TO' AND l_instrument_assignment_id IS NULL THEN Select substrb ( min ( decode ( acct_site_use_id , NULL , decode ( currency_code , NULL , '4' || to_char ( instr_assignment_id ) , '3' || to_char ( instr_assignment_id ) ) , decode ( currency_code , NULL , '2' || to_char ( instr_assignment_id ) , '1' || to_char ( instr_assignment_id ) ) ) ) , 2 ) into l_instrument_assignment_id from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'BANKACCOUNT' and cust_account_id = line_rec . paying_customer_id and line_rec . paying_site_use_id = nvl ( acct_site_use_id , line_rec . paying_site_use_id ) and line_rec . currency_code = nvl ( currency_code , line_rec . currency_code ) and order_of_preference = ( select substrb ( min ( decode ( acct_site
_use_id , NULL , decode ( currency_code , NULL , '4' || to_char ( order_of_preference ) , '3' || to_char ( order_of_preference ) ) , decode ( currency_code , NULL , '2' || to_char ( order_of_preference ) , '1' || to_char ( order_of_preference ) ) ) ) , 2 ) from IBY_FNDCPT_payer_assgn_instr_v where instrument_type = 'BANKACCOUNT' and cust_account_id = line_rec . paying_customer_id and line_rec . paying_site_use_id = nvl ( acct_site_use_id , line_rec . paying_site_use_id ) and line_rec . currency_code = nvl ( currency_code , line_rec . currency_code ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ) and line_rec . trx_date between NVL ( assignment_start_date , line_rec . trx_date ) and NVL ( assignment_end_date , line_rec . trx_date ) ; END IF ; END IF ; arp_standard . debug ( 'l_instrument_assignment_id :- ' || l_instrument_assignment_id ) ; IF l_instrument_assignment_id IS NOT NULL THEN select party_id , cust_account_id , acct_site_use_id , org_id , org_type , payment_function into l_party_id , l_cust_account_id , l_cust_site_use_id , l_org_id , l_org_type , l_payment_function from IBY_FNDCPT_payer_assgn_instr_v where instr_assignment_id = l_instrument_assignment_id and rownum < 2 ; l_payer_rec . payment_function := l_payment_function ; l_payer_rec . party_id := l_party_id ; l_payer_rec . cust_account_id := l_cust_account_id ; l_payer_rec . account_site_id := l_cust_site_use_id ; l_payer_rec . org_id := l_org_id ; l_payer_rec . org_type := l_org_type ; l_trxn_attribs_rec . originating_application_id := arp_standard . application_id ; l_trxn_attribs_rec . order_id := line_rec . customer_trx_id ; l_trxn_attribs_rec . trxn_ref_number1 := 'TRANSACTION' ; l_trxn_attribs_rec . trxn_ref_number2 := line_rec . customer_trx_id ; l_trxn_attribs_rec . seq_type_last := line_rec . mandate_last_trx_flag ; IBY_FNDCPT_TRXN_PUB . CREATE_TRANSACTION_EXTENSION ( p_api_version => 1.0 , p_init_msg_list => FND_API . G_TRUE , p_commit => FND_API . G_FALSE , x_return_status => l_return_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data , p_payer => l_payer_rec , p_payer_equivalency => IBY_FNDCPT_COMMON_PUB . G_PAYER_EQUIV_UPWARD , p_pmt_channel => l_payment_channel , p_instr_assignment => l_instrument_assignment_id , p_trxn_attribs => l_trxn_attribs_rec , x_entity_id => l_payment_trxn_extension_id , x_response => l_response_rec ) ; IF l_return_status = FND_API . G_RET_STS_SUCCESS THEN arp_standard . debug ( 'l_payment_trxn_extension_id :- ' || l_payment_trxn_extension_id ) ; update ra_interface_lines_gt set payment_trxn_extension_id = l_payment_trxn_extension_id where customer_trx_id = line_rec . customer_trx_id and request_id = fnd_global . conc_request_id and receipt_method_id is not null ; ELSE arp_standard . debug ( 'Processing customer_trx_id :- ' || line_rec . customer_trx_id ) ; arp_standard . debug ( 'result_code :- ' || l_response_rec . result_code ) ; arp_standard . debug ( 'result_category :- ' || l_response_rec . result_category ) ; arp_standard . debug ( 'result_message :- ' || l_response_rec . result_message ) ; arp_standard . debug ( 'l_return_status :- ' || l_return_status ) ; arp_standard . debug ( 'l_msg_count :- ' || l_msg_count ) ; arp_standard . debug ( 'l_msg_data :- ' || l_msg_data ) ; END IF ; END IF ; END LOOP ; arp_standard . debug ( 'Default Payment Transaction Extension ID()-' ) ; EXCEPTION WHEN OTHERS THEN arp_standard . debug ( 'Error in Default Payment Transaction Extension ID' ) ; arp_standard . debug ( '[' || SQLERRM ( SQLCODE ) || ']' ) ; RAISE ; END ; |
| 7bdg2fscmus2h |
SELECT ACCOUNTING_RULE_ID , ACCOUNTING_RULE_DURATION , ACTUAL_ARRIVAL_DATE , ACTUAL_SHIPMENT_DATE , AGREEMENT_ID , ARRIVAL_SET_ID , ATO_LINE_ID , ATTRIBUTE1 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 , ATTRIBUTE19 , ATTRIBUTE2 , ATTRIBUTE20 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , AUTO_SELECTED_QUANTITY , AUTHORIZED_TO_SHIP_FLAG , BOOKED_FLAG , CANCELLED_FLAG , CANCELLED_QUANTITY , COMPONENT_CODE , COMPONENT_NUMBER , COMPONENT_SEQUENCE_ID , CONFIG_HEADER_ID , CONFIG_REV_NBR , CONFIG_DISPLAY_SEQUENCE , CONFIGURATION_ID , CONTEXT , CREATED_BY , CREATION_DATE , CREDIT_INVOICE_LINE_ID , CUSTOMER_DOCK_CODE , CUSTOMER_JOB , CUSTOMER_PRODUCTION_LINE , CUST_PRODUCTION_SEQ_NUM , CUSTOMER_TRX_LINE_ID , CUST_MODEL_SERIAL_NUMBER , CUST_PO_NUMBER , CUSTOMER_LINE_NUMBER , CUSTOMER_SHIPMENT_NUMBER , CUSTOMER_ITEM_NET_PRICE , DELIVERY_LEAD_TIME , DELIVER_TO_CONTACT_ID , DELIVER_TO_ORG_ID , DEMAND_BUCKET_TYPE_CODE , DEMAND_CLASS_CODE , DEP_PLAN_REQUIRED_FLAG , EARLIEST_ACCEPTABLE_DATE , END_ITEM_UNIT_NUMBER , EXPLOSION_DATE , FIRST_ACK_CODE , FIRST_ACK_DATE , FOB_POINT_CODE , FREIGHT_CARRIER_CODE , FREIGHT_TERMS_CODE , FULFILLED_QUANTITY , FULFILLED_FLAG , FULFILLMENT_METHOD_CODE , FULFILLMENT_DATE , GLOBAL_ATTRIBUTE1 , GLOBAL_ATTRIBUTE10 , GLOBAL_ATTRIBUTE11 , GLOBAL_ATTRIBUTE12 , GLOBAL_ATTRIBUTE13 , GLOBAL_ATTRIBUTE14 , GLOBAL_ATTRIBUTE15 , GLOBAL_ATTRIBUTE16 , GLOBAL_ATTRIBUTE17 , GLOBAL_ATTRIBUTE18 , GLOBAL_ATTRIBUTE19 , GLOBAL_ATTRIBUTE2 , GLOBAL_ATTRIBUTE20 , GLOBAL_ATTRIBUTE3 , GLOBAL_ATTRIBUTE4 , GLOBAL_ATTRIBUTE5 , GLOBAL_ATTRIBUTE6 , GLOBAL_ATTRIBUTE7 , GLOBAL_ATTRIBUTE8 , GLOBAL_ATTRIBUTE9 , GLOBAL_ATTRIBUTE_CATEGORY , HEADER_ID , INDUSTRY_ATTRIBUTE1 , INDUSTRY_ATTRIBUTE10 , INDUSTRY_ATTRIBUTE11 , INDUSTRY_ATTRIBUTE12 , INDUSTRY_ATTRIBUTE13 , INDUSTRY_ATTRIBUTE14 , INDUSTRY_ATTRIBUTE15 , INDUSTRY_ATTRIBUTE16 , INDUSTRY_ATTRIBUTE17 , INDUSTRY_ATTRIBUTE18 , INDUSTRY_ATTRIBUTE19 , INDUSTRY_ATTRIBUTE20 , INDUSTRY_ATTRIBUTE21 , INDUSTRY_ATTRIBUTE22 , INDUSTRY_ATTRIBUTE23 , INDUSTRY_ATTRIBUTE24 , INDUSTRY_ATTRIBUTE25 , INDUSTRY_ATTRIBUTE26 , INDUSTRY_ATTRIBUTE27 , INDUSTRY_ATTRIBUTE28 , INDUSTRY_ATTRIBUTE29 , INDUSTRY_ATTRIBUTE30 , INDUSTRY_ATTRIBUTE2 , INDUSTRY_ATTRIBUTE3 , INDUSTRY_ATTRIBUTE4 , INDUSTRY_ATTRIBUTE5 , INDUSTRY_ATTRIBUTE6 , INDUSTRY_ATTRIBUTE7 , INDUSTRY_ATTRIBUTE8 , INDUSTRY_ATTRIBUTE9 , INDUSTRY_CONTEXT , INTMED_SHIP_TO_CONTACT_ID , INTMED_SHIP_TO_ORG_ID , INVENTORY_ITEM_ID , INVOICE_INTERFACE_STATUS_CODE , INVOICE_TO_CONTACT_ID , INVOICE_TO_ORG_ID , INVOICED_QUANTITY , INVOICING_RULE_ID , ORDERED_ITEM_ID , ITEM_IDENTIFIER_TYPE , ORDERED_ITEM , ITEM_REVISION , ITEM_TYPE_CODE , LAST_ACK_CODE , LAST_ACK_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN , LATEST_ACCEPTABLE_DATE , LINE_CATEGORY_CODE , LINE_ID , LINE_NUMBER , LINE_TYPE_ID , LINK_TO_LINE_ID , MODEL_GROUP_NUMBER , MFG_LEAD_TIME , OPEN_FLAG , OPTION_FLAG , OPTION_NUMBER , ORDERED_QUANTITY , ORDERED_QUANTITY2 , ORDER_QUANTITY_UOM , ORDERED_QUANTITY_UOM2 , ORG_ID , ORIG_SYS_DOCUMENT_REF , ORIG_SYS_LINE_REF , ORIG_SYS_SHIPMENT_REF , OVER_SHIP_REASON_CODE , OVER_SHIP_RESOLVED_FLAG , PAYMENT_TERM_ID , PLANNING_PRIORITY , PREFERRED_GRADE , PRICE_LIST_ID , PRICE_REQUEST_CODE , PRICING_ATTRIBUTE1 , PRICING_ATTRIBUTE10 , PRICING_ATTRIBUTE2 , PRICING_ATTRIBUTE3 , PRICING_ATTRIBUTE4 , PRICING_ATTRIBUTE5 , PRICING_ATTRIBUTE6 , PRICING_ATTRIBUTE7 , PRICING_ATTRIBUTE8 , PRICING_ATTRIBUTE9 , PRICING_CONTEXT , PRICING_DATE , PRICING_QUANTITY , PRICING_QUANTITY_UOM , PROGRAM_APPLICATION_ID , PROGRAM_ID , PROGRAM_UPDATE_DATE , PROJECT_ID , PROMISE_DATE , RE_SOURCE_FLAG , REFERENCE_CUSTOMER_TRX_LINE_ID , REFERENCE_HEADER_ID , REFERENCE_LINE_ID , RE
FERENCE_TYPE , REQUEST_DATE , REQUEST_ID , RETURN_ATTRIBUTE1 , RETURN_ATTRIBUTE10 , RETURN_ATTRIBUTE11 , RETURN_ATTRIBUTE12 , RETURN_ATTRIBUTE13 , RETURN_ATTRIBUTE14 , RETURN_ATTRIBUTE15 , RETURN_ATTRIBUTE2 , RETURN_ATTRIBUTE3 , RETURN_ATTRIBUTE4 , RETURN_ATTRIBUTE5 , RETURN_ATTRIBUTE6 , RETURN_ATTRIBUTE7 , RETURN_ATTRIBUTE8 , RETURN_ATTRIBUTE9 , RETURN_CONTEXT , RETURN_REASON_CODE , RLA_SCHEDULE_TYPE_CODE , SALESREP_ID , SCHEDULE_ARRIVAL_DATE , SCHEDULE_SHIP_DATE , SCHEDULE_STATUS_CODE , SHIPMENT_NUMBER , SHIPMENT_PRIORITY_CODE , SHIPPED_QUANTITY , SHIPPED_QUANTITY2 , SHIPPING_METHOD_CODE , SHIPPING_QUANTITY , SHIPPING_QUANTITY2 , SHIPPING_QUANTITY_UOM , SHIPPING_QUANTITY_UOM2 , SHIP_FROM_ORG_ID , SUBINVENTORY , SHIP_SET_ID , SHIP_TOLERANCE_ABOVE , SHIP_TOLERANCE_BELOW , SHIPPABLE_FLAG , SHIPPING_INTERFACED_FLAG , SHIP_TO_CONTACT_ID , SHIP_TO_ORG_ID , SHIP_MODEL_COMPLETE_FLAG , SOLD_TO_ORG_ID , SOLD_FROM_ORG_ID , SORT_ORDER , SOURCE_DOCUMENT_ID , SOURCE_DOCUMENT_LINE_ID , SOURCE_DOCUMENT_TYPE_ID , SOURCE_TYPE_CODE , SPLIT_FROM_LINE_ID , LINE_SET_ID , SPLIT_BY , MODEL_REMNANT_FLAG , TASK_ID , TAX_CODE , TAX_DATE , TAX_EXEMPT_FLAG , TAX_EXEMPT_NUMBER , TAX_EXEMPT_REASON_CODE , TAX_POINT_CODE , TAX_RATE , TAX_VALUE , TOP_MODEL_LINE_ID , UNIT_LIST_PRICE , UNIT_LIST_PRICE_PER_PQTY , UNIT_SELLING_PRICE , UNIT_SELLING_PRICE_PER_PQTY , VISIBLE_DEMAND_FLAG , VEH_CUS_ITEM_CUM_KEY_ID , SHIPPING_INSTRUCTIONS , PACKING_INSTRUCTIONS , SERVICE_TXN_REASON_CODE , SERVICE_TXN_COMMENTS , SERVICE_DURATION , SERVICE_PERIOD , SERVICE_START_DATE , SERVICE_END_DATE , SERVICE_COTERMINATE_FLAG , UNIT_LIST_PERCENT , UNIT_SELLING_PERCENT , UNIT_PERCENT_BASE_PRICE , SERVICE_NUMBER , SERVICE_REFERENCE_TYPE_CODE , SERVICE_REFERENCE_LINE_ID , SERVICE_REFERENCE_SYSTEM_ID , TP_CONTEXT , TP_ATTRIBUTE1 , TP_ATTRIBUTE2 , TP_ATTRIBUTE3 , TP_ATTRIBUTE4 , TP_ATTRIBUTE5 , TP_ATTRIBUTE6 , TP_ATTRIBUTE7 , TP_ATTRIBUTE8 , TP_ATTRIBUTE9 , TP_ATTRIBUTE10 , TP_ATTRIBUTE11 , TP_ATTRIBUTE12 , TP_ATTRIBUTE13 , TP_ATTRIBUTE14 , TP_ATTRIBUTE15 , FLOW_STATUS_CODE , MARKETING_SOURCE_CODE_ID , CALCULATE_PRICE_FLAG , COMMITMENT_ID , ORDER_SOURCE_ID , UPGRADED_FLAG , ORIGINAL_INVENTORY_ITEM_ID , ORIGINAL_ITEM_IDENTIFIER_TYPE , ORIGINAL_ORDERED_ITEM_ID , ORIGINAL_ORDERED_ITEM , ITEM_RELATIONSHIP_TYPE , ITEM_SUBSTITUTION_TYPE_CODE , LATE_DEMAND_PENALTY_FACTOR , OVERRIDE_ATP_DATE_CODE , FIRM_DEMAND_FLAG , EARLIEST_SHIP_DATE , USER_ITEM_DESCRIPTION , BLANKET_NUMBER , BLANKET_LINE_NUMBER , BLANKET_VERSION_NUMBER , UNIT_COST , LOCK_CONTROL , CHANGE_SEQUENCE , TRANSACTION_PHASE_CODE , SOURCE_DOCUMENT_VERSION_NUMBER , MINISITE_ID , IB_OWNER , IB_INSTALLED_AT_LOCATION , IB_CURRENT_LOCATION , END_CUSTOMER_ID , END_CUSTOMER_CONTACT_ID , END_CUSTOMER_SITE_USE_ID , RETROBILL_REQUEST_ID , ORIGINAL_LIST_PRICE , ORDER_FIRMED_DATE , ACTUAL_FULFILLMENT_DATE , CHARGE_PERIODICITY_CODE , CANCELLED_QUANTITY2 , FULFILLED_QUANTITY2 , CONTINGENCY_ID , REVREC_EVENT_CODE , REVREC_EXPIRATION_DAYS , ACCEPTED_QUANTITY , REVREC_COMMENTS , REVREC_SIGNATURE , REVREC_SIGNATURE_DATE , ACCEPTED_BY , REVREC_REFERENCE_DOCUMENT , REVREC_IMPLICIT_FLAG FROM OE_ORDER_LINES_ALL WHERE LINE_ID = :B1 |
| 7gup25397xndk | SELECT SOURCE_TYPE_CODE, ITEM_TYPE_CODE, ATO_LINE_ID FROM OE_ORDER_LINES WHERE LINE_ID = :B1 |
| 7qh9zcbrq9ky7 | SELECT 'Y' FROM WSH_PR_WORKERS WHERE ROWID = :B1 AND PROCESSED = 'N' FOR UPDATE NOWAIT |
| 7qs7fx89194u1 | SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID = :B4 AND APPLICATION_ID = :B3 AND LEVEL_ID = :B2 AND LEVEL_VALUE = :B1 AND PROFILE_OPTION_VALUE IS NOT NULL |
| 7yxbh5wb5zv9t | SELECT LINE_COLLECTIBLE_FLAG FROM AR_DEFERRED_LINES WHERE CUSTOMER_TRX_ID = :B2 AND CUSTOMER_TRX_LINE_ID = :B1 |
| 84czzn8ws6y5t | SELECT LOCATION_ID FROM PO_LOCATION_ASSOCIATIONS WHERE SITE_USE_ID = :B1 |
| 8p204vk5avzuq | SELECT TAX_REGIME_CODE, TAX, STATUS_RESULT, RATE_RESULT, CONDITION_SET_ID, EXCEPTION_SET_ID, RESULT_ID, QUERY_NUM FROM (SELECT /*+ leading(PR) use_nl(RL) */ RL.TAX_REGIME_CODE, RL.TAX, PR.STATUS_RESULT, PR.RATE_RESULT, PR.CONDITION_SET_ID, PR.EXCEPTION_SET_ID, PR.RESULT_ID, 1 QUERY_NUM, TAX.COMPOUNDING_PRECEDENCE FROM ZX_PROCESS_RESULTS PR, ZX_SCO_RULES_B_V RL, ZX_SCO_TAXES_B_V TAX WHERE (PR.CONDITION_GROUP_CODE = :B2 OR PR.CONDITION_GROUP_CODE LIKE :B2 ||'~'||'%') AND RL.EFFECTIVE_FROM <= :B1 AND (RL.EFFECTIVE_TO >= :B1 OR RL.EFFECTIVE_TO IS NULL ) AND PR.ENABLED_FLAG = 'Y' AND RL.TAX_RULE_ID = PR.TAX_RULE_ID AND RL.SERVICE_TYPE_CODE = 'DET_DIRECT_RATE' AND TAX.TAX_REGIME_CODE=RL.TAX_REGIME_CODE AND TAX.TAX=RL.TAX AND EXISTS (SELECT /*+ no_unnest */ 1 FROM ZX_SCO_RATES_B_V RT WHERE RT.TAX_REGIME_CODE = RL.TAX_REGIME_CODE AND RT.TAX = RL.TAX AND RT.TAX_STATUS_CODE = PR.STATUS_RESULT AND RT.TAX_RATE_CODE = PR.RATE_RESULT AND RT.EFFECTIVE_FROM <= :B1 AND (RT.EFFECTIVE_TO >= :B1 OR RT.EFFECTIVE_TO IS NULL ) AND RT.ACTIVE_FLAG = 'Y' AND (RT.TAX_CLASS = :B3 OR RT.TAX_CLASS IS NULL)) UNION ALL SELECT /*+ leading(RT.a) use_nl(RT.sd) */ DISTINCT RT.TAX_REGIME_CODE, RT.TAX, RT.TAX_STATUS_CODE, RT.TAX_RATE_CODE, NULL CONDITION_SET_ID, NULL EXCEPTION_SET_ID, NULL RESULT_ID, 2 QUERY_NUM, -1 COMPOUNDING_PRECEDENCE FROM ZX_SCO_RATES_B_V RT WHERE RT.TAX_RATE_CODE = :B4 AND RT.RATE_TYPE_CODE <> 'RECOVERY' AND RT.EFFECTIVE_FROM <= :B1 AND (RT.EFFECTIVE_TO >= :B1 OR RT.EFFECTIVE_TO IS NULL ) AND RT.ACTIVE_FLAG = 'Y' AND (RT.TAX_CLASS = :B3 OR RT.TAX_CLASS IS NULL) AND EXISTS (SELECT 1 FROM ZX_SCO_TAXES_B_V TAX WHERE TAX.TAX_REGIME_CODE = RT.TAX_REGIME_CODE AND TAX.TAX = RT.TAX AND TAX.LIVE_FOR_PROCESSING_FLAG = 'Y' AND TAX.LIVE_FOR_APPLICABILITY_FLAG = 'Y') ) ORDER BY COMPOUNDING_PRECEDENCE NULLS FIRST |
| 8zfgv4ck11dw9 | BEGIN WF_ENGINE.BACKGROUNDCONCURRENT(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4); END; |
| 98dpj4cn73gd3 | SELECT REFERENCE_LINE_ID, LINE_CATEGORY_CODE FROM OE_ORDER_LINES WHERE LINE_ID = :B1 |
| 9d8w3myb2h8bs | SELECT TAX_REGIME_CODE, TAX, TAX_STATUS_CODE, TAX_RATE_CODE, TAX_RATE_ID, EFFECTIVE_FROM, EFFECTIVE_TO, RATE_TYPE_CODE, PERCENTAGE_RATE, QUANTITY_RATE, ALLOW_ADHOC_TAX_RATE_FLAG, UOM_CODE, TAX_JURISDICTION_CODE, OFFSET_TAX, OFFSET_STATUS_CODE, OFFSET_TAX_RATE_CODE, ALLOW_EXEMPTIONS_FLAG, ALLOW_EXCEPTIONS_FLAG, NULL TAX_JURISDICTION_ID, DEF_REC_SETTLEMENT_OPTION_CODE, TAXABLE_BASIS_FORMULA_CODE, ADJ_FOR_ADHOC_AMT_CODE, INCLUSIVE_TAX_FLAG, TAX_CLASS FROM ZX_SCO_RATES_B_V WHERE TAX_REGIME_CODE = :B7 AND TAX = :B6 AND TAX_STATUS_CODE = :B5 AND ACTIVE_FLAG = 'Y' AND (TAX_JURISDICTION_CODE = :B4 ) AND TAX_RATE_CODE = :B3 AND (TAX_CLASS = :B2 OR TAX_CLASS IS NULL) AND ( :B1 >= EFFECTIVE_FROM AND (:B1 <= EFFECTIVE_TO OR EFFECTIVE_TO IS NULL)) ORDER BY TAX_CLASS NULLS LAST, SUBSCRIPTION_LEVEL_CODE |
| 9m52p59waknpu | SELECT HEADER_ID, ORG_ID, SHIP_FROM_ORG_ID, SOLD_TO_ORG_ID, COMMITMENT_ID, SALESREP_ID, INVENTORY_ITEM_ID, ITEM_TYPE_CODE, ORDER_SOURCE_ID, ORIG_SYS_DOCUMENT_REF, ORIG_SYS_LINE_REF, ORIG_SYS_SHIPMENT_REF, CHANGE_SEQUENCE, SOURCE_DOCUMENT_TYPE_ID, SOURCE_DOCUMENT_ID, SOURCE_DOCUMENT_LINE_ID FROM OE_ORDER_LINES WHERE LINE_ID = :B1 |
| a3d4khyx5kbad | SELECT NVL(PICKING_CREDIT_CHECK_RULE_ID, -1) FROM OE_ORDER_TYPES_V OT, OE_CREDIT_CHECK_RULES CCR WHERE OT.ORDER_TYPE_ID = :B1 AND PICKING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID AND TRUNC(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, TRUNC(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, TRUNC(SYSDATE)) |
| a43whfj9kfzrc |
INSERT INTO ZX_TRANSACTION_LINES_GT ( APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, INTERFACE_ENTITY_CODE, INTERFACE_LINE_ID, TRX_ID, TRX_LEVEL_TYPE, TRX_LINE_ID, LINE_CLASS, LINE_LEVEL_ACTION, TRX_SHIPPING_DATE, TRX_LINE_TYPE, TRX_LINE_DATE, LINE_AMT_INCLUDES_TAX_FLAG, LINE_AMT, TRX_LINE_QUANTITY, UNIT_PRICE, EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE, EXEMPTION_CONTROL_FLAG, PRODUCT_ID, PRODUCT_ORG_ID, UOM_CODE, FOB_POINT, SHIP_FROM_PARTY_ID, SHIP_FROM_LOCATION_ID, SHIP_TO_PARTY_ID, SHIP_TO_PARTY_SITE_ID, BILL_TO_PARTY_ID, BILL_TO_PARTY_SITE_ID, ADJUSTED_DOC_APPLICATION_ID, ADJUSTED_DOC_ENTITY_CODE, ADJUSTED_DOC_EVENT_CLASS_CODE, ADJUSTED_DOC_TRX_ID, ADJUSTED_DOC_LINE_ID, ADJUSTED_DOC_TRX_LEVEL_TYPE, ADJUSTED_DOC_NUMBER, ADJUSTED_DOC_DATE, SOURCE_APPLICATION_ID, SOURCE_ENTITY_CODE, SOURCE_EVENT_CLASS_CODE, SOURCE_TRX_ID, SOURCE_LINE_ID, SOURCE_TRX_LEVEL_TYPE, SOURCE_TAX_LINE_ID, OUTPUT_TAX_CLASSIFICATION_CODE, TRX_LINE_NUMBER, HISTORICAL_FLAG, CTRL_HDR_TX_APPL_FLAG, TRX_LINE_GL_DATE, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID, TRX_LINE_CURRENCY_CODE, TRX_LINE_PRECISION, TRX_LINE_MAU, SHIP_THIRD_PTY_ACCT_ID, SHIP_THIRD_PTY_ACCT_SITE_ID, SHIP_TO_CUST_ACCT_SITE_USE_ID, POA_PARTY_ID, POA_LOCATION_ID, POO_PARTY_ID, POO_LOCATION_ID, CASH_DISCOUNT, BILL_FROM_LOCATION_ID, TRX_BUSINESS_CATEGORY, PRODUCT_FISC_CLASSIFICATION, PRODUCT_CATEGORY, PRODUCT_TYPE, LINE_INTENDED_USE, ASSESSABLE_VALUE, USER_DEFINED_FISC_CLASS, ACCOUNT_CCID, TRX_LINE_DESCRIPTION ) SELECT 222, ZTH.ENTITY_CODE, ZTH.EVENT_CLASS_CODE, 'RA_INTERFACE_LINES', TL.CUSTOMER_TRX_LINE_ID, TL.CUSTOMER_TRX_ID, 'LINE', TL.CUSTOMER_TRX_LINE_ID, ZTH.EVENT_CLASS_CODE, DECODE(TL.LINE_TYPE, 'CHARGES', 'RECORD_WITH_NO_TAX', DECODE(NVL(ITL.TAXABLE_FLAG, TL.TAXABLE_FLAG), 'N', 'RECORD_WITH_NO_TAX', DECODE(ML.LINE_TYPE, 'TAX', 'LINE_INFO_TAX_ONLY', 'CREATE'))), NVL(TL.SALES_ORDER_DATE, T.SHIP_DATE_ACTUAL), DECODE(TL.INVENTORY_ITEM_ID, NULL, 'MISC', 'ITEM'), NULL, DECODE(TL.AMOUNT_INCLUDES_TAX_FLAG, 'Y', 'A', 'N', 'N', 'S'), TL.EXTENDED_AMOUNT, DECODE(ZTH.EVENT_CLASS_CODE, 'CREDIT_MEMO', NVL(TL.QUANTITY_CREDITED, TL.QUANTITY_INVOICED), TL.QUANTITY_INVOICED), TL.UNIT_SELLING_PRICE, TL.TAX_EXEMPT_NUMBER, TL.TAX_EXEMPT_REASON_CODE, TL.TAX_EXEMPT_FLAG, NVL(TL.INVENTORY_ITEM_ID, TL.MEMO_LINE_ID), DECODE(TL.MEMO_LINE_ID, NULL, NVL(TL.WAREHOUSE_ID, TO_NUMBER(:B3 )), NULL), TL.UOM_CODE, T.FOB_POINT, TL.WAREHOUSE_ID, HR.LOCATION_ID, STCA.PARTY_ID, STPS.PARTY_SITE_ID, ZTH.ROUNDING_BILL_TO_PARTY_ID, ZTH.RNDG_BILL_TO_PARTY_SITE_ID, DECODE(TL.PREVIOUS_CUSTOMER_TRX_LINE_ID, NULL, NULL, 222), DECODE(TL.PREVIOUS_CUSTOMER_TRX_LINE_ID, NULL, NULL, 'TRANSACTIONS'), DECODE(TL.PREVIOUS_CUSTOMER_TRX_LINE_ID, NULL, NULL, DECODE(ITT.TYPE, 'DM', 'DEBIT_MEMO', 'INVOICE')), DECODE(TL.PREVIOUS_CUSTOMER_TRX_LINE_ID, NULL, NULL, T.PREVIOUS_CUSTOMER_TRX_ID), DECODE(TL.PREVIOUS_CUSTOMER_TRX_LINE_ID, NULL, NULL, TL.PREVIOUS_CUSTOMER_TRX_LINE_ID), DECODE(TL.PREVIOUS_CUSTOMER_TRX_LINE_ID, NULL, NULL, 'LINE'), DECODE(T.PREVIOUS_CUSTOMER_TRX_ID, NULL, NULL, IT.TRX_NUMBER), DECODE(T.PREVIOUS_CUSTOMER_TRX_ID, NULL, NULL, IT.TRX_DATE), RIL.SOURCE_APPLICATION_ID, RIL.SOURCE_ENTITY_CODE, RIL.SOURCE_EVENT_CLASS_CODE, RIL.SOURCE_TRX_ID, RIL.SOURCE_TRX_LINE_ID, RIL.SOURCE_TRX_LINE_TYPE, RIL.SOURCE_TRX_DETAIL_TAX_LINE_ID, TL.TAX_CLASSIFICATION_CODE, TL.LINE_NUMBER, TL.HISTORICAL_FLAG, 'N', NVL(REC.GL_DATE, TRUNC(SYSDATE)), STPSU.LOCATION_ID, BTPSU.LOCATION_ID, ZTH.TRX_CURRENCY_CODE, ZTH.PRECISION, ZTH.MINIMUM_ACCOUNTABLE_UNIT, TL.SHIP_TO_CUSTOMER_ID, STPS.CUST_ACCT_SITE_ID, STCSU.SITE_USE_ID, ZTH.INTERNAL_ORGANIZATION_ID, ZTH.INTERNAL_ORG_LOCATION_ID, ZTH.INTERNAL_ORGANIZATION_ID, ZTH.INTERNAL_ORG_LOCATION_ID, TL.EXTENDED_AMOUNT * ARP_ETAX_UTIL.GET_DISCOUNT_RATE(T.CUSTOMER_TRX_ID), ZTH.INTERNAL_ORG_LOCATION_ID, RIL.TRX_BUSINESS_CATEGORY, RIL.PRODUCT_FISC_
CLASSIFICATION, NVL(RIL.PRODUCT_CATEGORY, ML.TAX_PRODUCT_CATEGORY), RIL.PRODUCT_TYPE, RIL.LINE_INTENDED_USE, RIL.ASSESSABLE_VALUE, RIL.USER_DEFINED_FISC_CLASS, ( SELECT DECODE( :B1 , 'CM', NULL, CODE_COMBINATION_ID) FROM RA_CUST_TRX_LINE_GL_DIST GLD WHERE ROWNUM = 1 AND GLD.CUSTOMER_TRX_LINE_ID = TL.CUSTOMER_TRX_LINE_ID AND GLD.ACCOUNT_CLASS = 'REV' AND GLD.REQUEST_ID = TL.REQUEST_ID) ACCOUNT_CCID, TL.DESCRIPTION FROM RA_CUSTOMER_TRX_LINES TL, RA_CUSTOMER_TRX T, RA_INTERFACE_LINES RIL, ZX_TRX_HEADERS_GT ZTH, HZ_CUST_ACCOUNTS STCA, HZ_CUST_ACCT_SITES STPS, HZ_CUST_SITE_USES STCSU, RA_CUSTOMER_TRX IT, RA_CUST_TRX_TYPES ITT, RA_CUSTOMER_TRX_LINES ITL, RA_CUST_TRX_LINE_GL_DIST REC, HZ_PARTY_SITES STPSU, HZ_PARTY_SITES BTPSU, HR_ALL_ORGANIZATION_UNITS HR, AR_MEMO_LINES_B ML WHERE TL.REQUEST_ID = :B2 AND TL.LINE_TYPE IN ('LINE', 'CHARGES') AND TL.CUSTOMER_TRX_ID = T.CUSTOMER_TRX_ID AND TL.CUSTOMER_TRX_LINE_ID = RIL.INTERFACE_LINE_ID (+) AND TL.CUSTOMER_TRX_ID = ZTH.TRX_ID AND NVL(T.PREVIOUS_CUSTOMER_TRX_ID, -99) = DECODE(:B1 , 'INV', -99, T.PREVIOUS_CUSTOMER_TRX_ID) AND TL.SHIP_TO_CUSTOMER_ID = STCA.CUST_ACCOUNT_ID (+) AND TL.SHIP_TO_SITE_USE_ID = STCSU.SITE_USE_ID (+) AND STCSU.CUST_ACCT_SITE_ID = STPS.CUST_ACCT_SITE_ID (+) AND STPS.PARTY_SITE_ID = STPSU.PARTY_SITE_ID (+) AND ZTH.RNDG_BILL_TO_PARTY_SITE_ID = BTPSU.PARTY_SITE_ID AND T.PREVIOUS_CUSTOMER_TRX_ID = IT.CUSTOMER_TRX_ID (+) AND IT.CUST_TRX_TYPE_ID = ITT.CUST_TRX_TYPE_ID (+) AND TL.PREVIOUS_CUSTOMER_TRX_LINE_ID = ITL.CUSTOMER_TRX_LINE_ID (+) AND REC.CUSTOMER_TRX_ID (+) = T.CUSTOMER_TRX_ID AND REC.ACCOUNT_CLASS (+) = 'REC' AND REC.LATEST_REC_FLAG (+) = 'Y' AND TL.WAREHOUSE_ID = HR.ORGANIZATION_ID (+) AND TL.MEMO_LINE_ID = ML.MEMO_LINE_ID (+) AND TL.ORG_ID = ML.ORG_ID (+) |
| assndb4wqnd6r | BEGIN INV_TXN_MANAGER_PUB.rel_reservations_mrp_update(:1, :2, :3, :4, :5, :6, :7); END; |
| aw21gc59tbcnr |
SELECT X.ORGANIZATION_ID ORGANIZATION_ID , X.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , X.REVISION REVISION , NULL LOT_NUMBER , NULL LOT_EXPIRATION_DATE , X.SUBINVENTORY_CODE SUBINVENTORY_CODE , SUB.RESERVABLE_TYPE RESERVABLE_TYPE , X.LOCATOR_ID LOCATOR_ID , SUM(X.PRIMARY_QUANTITY) PRIMARY_QUANTITY , SUM(X.SECONDARY_QUANTITY) SECONDARY_QUANTITY , X.QUANTITY_TYPE QUANTITY_TYPE , X.COST_GROUP_ID COST_GROUP_ID , X.LPN_ID LPN_ID , X.TRANSACTION_ACTION_ID TRANSACTION_ACTION_ID , X.TRANSFER_SUBINVENTORY_CODE TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID TRANSFER_LOCATOR_ID , NULL IS_RESERVABLE_LOT FROM ( SELECT X.ORGANIZATION_ID ORGANIZATION_ID , X.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , DECODE(:B10 , 2, NULL, X.REVISION) REVISION , NULL LOT_NUMBER , X.SUBINVENTORY_CODE SUBINVENTORY_CODE , X.LOCATOR_ID LOCATOR_ID , SUM(X.PRIMARY_QUANTITY) PRIMARY_QUANTITY , SUM(X.SECONDARY_QUANTITY) SECONDARY_QUANTITY , X.QUANTITY_TYPE QUANTITY_TYPE , X.COST_GROUP_ID COST_GROUP_ID , X.LPN_ID LPN_ID , X.TRANSACTION_ACTION_ID TRANSACTION_ACTION_ID , X.TRANSFER_SUBINVENTORY_CODE TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID TRANSFER_LOCATOR_ID FROM ( SELECT MR.ORGANIZATION_ID ORGANIZATION_ID , MR.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MR.REVISION REVISION , MR.LOT_NUMBER LOT_NUMBER , MR.SUBINVENTORY_CODE SUBINVENTORY_CODE , MR.LOCATOR_ID LOCATOR_ID , MR.PRIMARY_RESERVATION_QUANTITY - NVL(MR.DETAILED_QUANTITY, 0) PRIMARY_QUANTITY , MR.SECONDARY_RESERVATION_QUANTITY - NVL(MR.SECONDARY_DETAILED_QUANTITY, 0) SECONDARY_QUANTITY , 3 QUANTITY_TYPE , TO_NUMBER(NULL) COST_GROUP_ID , LPN_ID LPN_ID , TO_NUMBER(NULL) TRANSACTION_ACTION_ID , TO_CHAR(NULL) TRANSFER_SUBINVENTORY_CODE , TO_NUMBER(NULL) TRANSFER_LOCATOR_ID FROM MTL_RESERVATIONS MR WHERE NVL(MR.SUPPLY_SOURCE_TYPE_ID, 13) = 13 AND MR.PRIMARY_RESERVATION_QUANTITY > NVL(MR.DETAILED_QUANTITY, 0) AND ((:B7 <>1) OR (:B7 = 1 AND MR.LPN_ID IS NULL)) AND (:B6 <> 3 OR (:B6 = 3 AND NOT ( :B5 = MR.DEMAND_SOURCE_TYPE_ID AND :B4 = MR.DEMAND_SOURCE_HEADER_ID AND NVL(:B3 , -9999) = NVL(MR.DEMAND_SOURCE_LINE_ID, -9999) AND NVL(:B2 , '@@@###@@#') = NVL(MR.DEMAND_SOURCE_NAME, '@@@###@@#') AND NVL(:B1 , -9999) = NVL(MR.DEMAND_SOURCE_DELIVERY, -9999) ) ) ) UNION ALL SELECT MOQ.ORGANIZATION_ID ORGANIZATION_ID , MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MOQ.REVISION REVISION , MOQ.LOT_NUMBER LOT_NUMBER , MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE , MOQ.LOCATOR_ID LOCATOR_ID , MOQ.PRIMARY_TRANSACTION_QUANTITY , MOQ.SECONDARY_TRANSACTION_QUANTITY , 1 QUANTITY_TYPE , MOQ.COST_GROUP_ID COST_GROUP_ID , MOQ.LPN_ID LPN_ID , TO_NUMBER(NULL) TRANSACTION_ACTION_ID , TO_CHAR(NULL) TRANSFER_SUBINVENTORY_CODE , TO_NUMBER(NULL) TRANSFER_LOCATOR_ID FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ UNION ALL SELECT MMTT.ORGANIZATION_ID ORGANIZATION_ID , MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MMTT.REVISION REVISION , NULL LOT_NUMBER , MMTT.SUBINVENTORY_CODE SUBINVENTORY_CODE , MMTT.LOCATOR_ID LOCATOR_ID , DECODE(DECODE(MMTT.TRANSACTION_STATUS, 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, -1, 2), MMTT.TRANSACTION_STATUS) , 2, 1, DECODE(MMTT.TRANSACTION_ACTION_ID, 1, -1, 2, -1, 28, -1, 3, -1, SIGN(MMTT.PRIMARY_QUANTITY))) * ROUND(ABS(MMTT.PRIMARY_QUANTITY), 5) , DECODE(DECODE(MMTT.TRANSACTION_STATUS, 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, -1, 2), MMTT.TRANSACTION_STATUS) , 2, 1, DECODE(MMTT.TRANSACTION_ACTION_ID, 1, -1, 2, -1, 28, -1, 3, -1, SIGN(MMTT.SECONDARY_TRANSACTION_QUANTITY))) * ROUND(ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY), 5) , DECODE(MMTT.TRANSACTION_STATUS, 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, 1, 5), 1) QUANTITY_TYPE , MMTT.COST_GROUP_ID COST_GROUP_ID , NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID)) LPN_ID , DECODE(MMTT.TRANSACTION_STATUS, 2 , MMTT.TRANSACTION_ACTION_ID, TO_NUMBER(NULL)) TRANSACTION_ACTION_ID , DECODE(MMTT.TRANSACTION_STATUS, 2 , MMTT.TRANSFER_SUBINVENTORY, TO_CHAR(NULL)) TRANSFER_SUBIN
VENTORY_CODE , DECODE(MMTT.TRANSACTION_STATUS, 2 , MMTT.TRANSFER_TO_LOCATION, TO_NUMBER(NULL)) TRANSFER_LOCATOR_ID FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT WHERE MMTT.POSTING_FLAG = 'Y' AND MMTT.SUBINVENTORY_CODE IS NOT NULL AND (NVL(MMTT.TRANSACTION_STATUS, 0) <> 2 OR NVL(MMTT.TRANSACTION_STATUS, 0) = 2 AND MMTT.TRANSACTION_ACTION_ID IN (1, 2, 28, 3, 21, 29, 32, 34)) AND MMTT.TRANSACTION_ACTION_ID NOT IN (5, 6, 24, 30) UNION ALL SELECT DECODE(MMTT.TRANSACTION_ACTION_ID, 3, MMTT.TRANSFER_ORGANIZATION, MMTT.ORGANIZATION_ID) ORGANIZATION_ID , MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MMTT.REVISION REVISION , NULL LOT_NUMBER , MMTT.TRANSFER_SUBINVENTORY SUBINVENTORY_CODE , MMTT.TRANSFER_TO_LOCATION LOCATOR_ID , ROUND(ABS(MMTT.PRIMARY_QUANTITY), 5) , ROUND(ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY), 5) , 1 QUANTITY_TYPE , MMTT.TRANSFER_COST_GROUP_ID COST_GROUP_ID , NVL(MMTT.CONTENT_LPN_ID, MMTT.TRANSFER_LPN_ID) LPN_ID , TO_NUMBER(NULL) TRANSACTION_ACTION_ID , TO_CHAR(NULL) TRANSFER_SUBINVENTORY_CODE , TO_NUMBER(NULL) TRANSFER_LOCATOR_ID FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT WHERE MMTT.POSTING_FLAG = 'Y' AND DECODE( NVL(MMTT.TRANSACTION_STATUS, 0), 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, 1, 2), 1 ) <> 2 AND MMTT.TRANSACTION_ACTION_ID IN (2, 28, 3) AND MMTT.WIP_SUPPLY_TYPE IS NULL ) X WHERE X.ORGANIZATION_ID = :B9 AND X.INVENTORY_ITEM_ID = :B8 GROUP BY X.ORGANIZATION_ID, X.INVENTORY_ITEM_ID, X.REVISION , X.LOT_NUMBER, X.SUBINVENTORY_CODE, X.LOCATOR_ID , X.QUANTITY_TYPE, X.COST_GROUP_ID, X.LPN_ID , X.TRANSACTION_ACTION_ID, X.TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID ) X , MTL_SECONDARY_INVENTORIES SUB WHERE X.ORGANIZATION_ID = SUB.ORGANIZATION_ID (+) AND X.SUBINVENTORY_CODE = SUB.SECONDARY_INVENTORY_NAME (+) AND (:B12 = 2 OR NVL(SUB.ASSET_INVENTORY, 1) = 1) AND ( (:B11 = 1 AND NVL(SUB.INVENTORY_ATP_CODE, 1) = 1) OR (:B11 = 2 AND NVL(SUB.AVAILABILITY_TYPE, 1) = 1 ) OR :B11 =3 OR (:B11 = 4 AND (NVL(SUB.INVENTORY_ATP_CODE, 1) = 1 AND NVL(SUB.AVAILABILITY_TYPE, 1)=1)) ) GROUP BY X.ORGANIZATION_ID , X.INVENTORY_ITEM_ID , X.REVISION , NULL , NULL , SUB.RESERVABLE_TYPE , X.SUBINVENTORY_CODE , NULL , X.LOCATOR_ID , X.QUANTITY_TYPE , X.COST_GROUP_ID , X.LPN_ID , X.TRANSACTION_ACTION_ID , X.TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID |
| bc7gjv3ppdtbz | BEGIN dbms_workload_repository.create_snapshot(); END; |
| c64uqd4zbqsqc | SELECT ORDER_CATEGORY_CODE, ORDER_TYPE_ID FROM OE_ORDER_HEADERS WHERE HEADER_ID = :B1 |
| cd9mbmqyf4qhp | UPDATE WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS = :B8 , ACTIVITY_RESULT_CODE = NVL(:B13 , ACTIVITY_RESULT_CODE), BEGIN_DATE = NVL(:B11 , BEGIN_DATE), END_DATE = NVL(:B12 , END_DATE), DUE_DATE = DECODE(:B11 , TO_DATE(NULL), DUE_DATE, :B10 ), OUTBOUND_QUEUE_ID = :B9 , EXECUTION_TIME = DECODE(:B8 , :B7 , :B4 , :B6 , NVL(EXECUTION_TIME, :B4 ), :B5 , NVL(EXECUTION_TIME, :B4 ), EXECUTION_TIME) WHERE ITEM_TYPE = :B3 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B1 |
| cp2sa6sr2xtbm | SELECT 'Y' FROM CSI_ITEM_INSTANCES_H WHERE INSTANCE_ID = :B1 AND NEW_ACCOUNTING_CLASS_CODE = 'CUST_PROD' AND ROWNUM = 1 |
| drt9k4tpvph64 | SELECT AR_TAX.LOCATION_SEGMENT_ID, AR_TAX.ORG_ID, AR_TAX.TAX_LINE_ID, AR_TAX.VAT_TAX_ID, AR_TAX.SET_OF_BOOKS_ID, NVL(AR_REC.GL_DATE, TRUNC(SYSDATE)) FROM RA_CUSTOMER_TRX_LINES AR_TAX, RA_CUST_TRX_LINE_GL_DIST AR_REC WHERE AR_TAX.CUSTOMER_TRX_LINE_ID = :B1 AND AR_TAX.CUSTOMER_TRX_ID = AR_REC.CUSTOMER_TRX_ID AND AR_REC.ACCOUNT_CLASS = 'REC' AND AR_REC.LATEST_REC_FLAG = 'Y' |
| dvd0dadfqk23r | UPDATE MTL_TRANSACTIONS_INTERFACE MTI SET LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = :B5 , LAST_UPDATE_LOGIN = :B4 , PROGRAM_UPDATE_DATE = SYSDATE, PROCESS_FLAG = 3, LOCK_FLAG = 2, ERROR_CODE = SUBSTRB(:B3 , 1, 240), ERROR_EXPLANATION = SUBSTRB(:B2 , 1, 240) WHERE ROWID = :B1 AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR (TRANSACTION_SOURCE_TYPE_ID > 100 ) ) AND TRANSACTION_ACTION_ID IN (1, 27 ) AND PROCESS_FLAG = 1 AND EXISTS ( SELECT NULL FROM MTL_TRANSACTION_TYPES MTTY WHERE MTTY.TRANSACTION_TYPE_ID = MTI.TRANSACTION_TYPE_ID AND MTTY.TYPE_CLASS = 1 ) AND NOT EXISTS ( SELECT NULL FROM PA_PROJECTS_EXPEND_V PRJ1 WHERE PRJ1.PROJECT_ID = MTI.SOURCE_PROJECT_ID ) |
| f07ydzdqdk3u9 | SELECT NAME FROM RA_BATCH_SOURCES WHERE BATCH_SOURCE_ID = :B1 |
| gash0u2q9atsp | INSERT INTO RA_CUST_TRX_LINE_GL_DIST ( CUST_TRX_LINE_GL_DIST_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, SET_OF_BOOKS_ID, REQUEST_ID, CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, CUST_TRX_LINE_SALESREP_ID, PERCENT, AMOUNT, ACCTD_AMOUNT, ACCOUNT_CLASS, ACCOUNT_SET_FLAG, CONCATENATED_SEGMENTS, CODE_COMBINATION_ID, GL_DATE, ORIGINAL_GL_DATE, USSGL_TRANSACTION_CODE, USSGL_TRANSACTION_CODE_CONTEXT, POSTING_CONTROL_ID, LATEST_REC_FLAG, COLLECTED_TAX_CONCAT_SEG, COLLECTED_TAX_CCID, REC_OFFSET_FLAG , ORG_ID ) VALUES ( RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL, :B33 , SYSDATE, :B33 , SYSDATE, :B32 , :B31 , :B1 , :B2 , :B3 , ROUND(NVL(:B4 , 0), 4), DECODE(:B5 , 'Y', NULL, :B6 ), DECODE(:B7 , 'Y', NULL, :B8 ), :B9 , :B10 , DECODE(:B11 , '', DECODE(:B12 , -1, :B13 , NULL ), -1, :B14 , NULL), DECODE(:B15 , '', :B16 , :B17 ), TO_DATE(:B18 , 'J'), TO_DATE(:B19 , 'J'), :B20 , :B21 , -3, DECODE( :B22 , 'REC', 'Y', NULL), DECODE(:B23 , '', NULL, DECODE(:B24 , -1, :B25 , NULL)), DECODE(:B26 , '', NULL, :B27 ), DECODE(:B28 , 'Y', NULL, DECODE(:B29 , 'UNEARN', 'Y', NULL)) , :B30 ) RETURNING CUST_TRX_LINE_GL_DIST_ID INTO :O0 |
| gccbpwamzp78q | declare l_header_rounding_flag VARCHAR2 ( 1 ) ; BEGIN select trx_header_level_rounding into l_header_rounding_flag from ar_system_parameters ; :return_value:i_return_value := arp_rounding . correct_dist_rounding_errors ( :request_id , NULL , NULL , :rows_processed:i_rows_processed , :error_msg:i_error_msg , NULL , NULL , :trx_class , 'N' , :debug_mode , l_header_rounding_flag , 'N' ) ; END ; |
| gk8x2shgj6xwp | SELECT WDD.DELIVERY_DETAIL_ID FROM WSH_TRIP_STOPS WTS, WSH_DELIVERY_LEGS WDL, WSH_DELIVERY_ASSIGNMENTS_V WDA, WSH_DELIVERY_DETAILS WDD WHERE WDD.INV_INTERFACED_FLAG IN ('N', 'P') AND WTS.BATCH_ID = :B1 AND WTS.STOP_LOCATION_ID = WDD.SHIP_FROM_LOCATION_ID AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID AND WDL.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_ID IS NOT NULL AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WDD.SOURCE_CODE IN ('OE', 'OKE', 'WSH') AND WDD.RELEASED_STATUS <> 'D' AND NVL(WDD.LINE_DIRECTION , 'O') IN ('O', 'IO') AND ROWNUM = 1 |
| gnhmcahkmrnzs | INSERT INTO WF_ITEM_ACTIVITY_STATUSES ( ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY, ACTIVITY_STATUS, ACTIVITY_RESULT_CODE, ASSIGNED_USER, NOTIFICATION_ID, BEGIN_DATE, END_DATE, DUE_DATE, EXECUTION_TIME, OUTBOUND_QUEUE_ID ) VALUES ( :B13 , :B12 , :B11 , :B6 , :B10 , NULL, NULL, :B9 , :B8 , :B7 , DECODE(:B6 , :B5 , :B2 , :B4 , :B2 , :B3 , :B2 , NULL), :B1 ) |
| gsj6516ntm9ra | SELECT ROWID , DENSE_RANK() OVER (PARTITION BY AE_HEADER_ID ORDER BY AE_HEADER_ID , GL_TRANSFER_MODE_CODE , ACCOUNTING_CLASS_CODE , EVENT_TYPE_CODE , ROUNDING_CLASS_CODE , DOCUMENT_ROUNDING_LEVEL , CURRENCY_CODE , CURRENCY_CONVERSION_TYPE , CURRENCY_CONVERSION_DATE , CURRENCY_CONVERSION_RATE , PARTY_ID , PARTY_SITE_ID , PARTY_TYPE_CODE , CODE_COMBINATION_ID , CODE_COMBINATION_STATUS_CODE , SEGMENT1 , SEGMENT2 , SEGMENT3 , SEGMENT4 , SEGMENT5 , SEGMENT6 , SEGMENT7 , SEGMENT8 , SEGMENT9 , SEGMENT10 , SEGMENT11 , SEGMENT12 , SEGMENT13 , SEGMENT14 , SEGMENT15 , SEGMENT16 , SEGMENT17 , SEGMENT18 , SEGMENT19 , SEGMENT20 , SEGMENT21 , SEGMENT22 , SEGMENT23 , SEGMENT24 , SEGMENT25 , SEGMENT26 , SEGMENT27 , SEGMENT28 , SEGMENT29 , SEGMENT30 , ALT_CODE_COMBINATION_ID , ALT_CCID_STATUS_CODE , ALT_SEGMENT1 , ALT_SEGMENT2 , ALT_SEGMENT3 , ALT_SEGMENT4 , ALT_SEGMENT5 , ALT_SEGMENT6 , ALT_SEGMENT7 , ALT_SEGMENT8 , ALT_SEGMENT9 , ALT_SEGMENT10 , ALT_SEGMENT11 , ALT_SEGMENT12 , ALT_SEGMENT13 , ALT_SEGMENT14 , ALT_SEGMENT15 , ALT_SEGMENT16 , ALT_SEGMENT17 , ALT_SEGMENT18 , ALT_SEGMENT19 , ALT_SEGMENT20 , ALT_SEGMENT21 , ALT_SEGMENT22 , ALT_SEGMENT23 , ALT_SEGMENT24 , ALT_SEGMENT25 , ALT_SEGMENT26 , ALT_SEGMENT27 , ALT_SEGMENT28 , ALT_SEGMENT29 , ALT_SEGMENT30 , DESCRIPTION , JGZZ_RECON_REF , USSGL_TRANSACTION_CODE , MERGE_DUPLICATE_CODE , ANALYTICAL_BALANCE_FLAG , ANC_ID_1 , ANC_ID_2 , ANC_ID_3 , ANC_ID_4 , ANC_ID_5 , ANC_ID_6 , ANC_ID_7 , ANC_ID_8 , ANC_ID_9 , ANC_ID_10 , ANC_ID_11 , ANC_ID_12 , ANC_ID_13 , ANC_ID_14 , ANC_ID_15 , ANC_ID_16 , ANC_ID_17 , ANC_ID_18 , ANC_ID_19 , ANC_ID_20 , ANC_ID_21 , ANC_ID_22 , ANC_ID_23 , ANC_ID_24 , ANC_ID_25 , ANC_ID_26 , ANC_ID_27 , ANC_ID_28 , ANC_ID_29 , ANC_ID_30 , ANC_ID_31 , ANC_ID_32 , ANC_ID_33 , ANC_ID_34 , ANC_ID_35 , ANC_ID_36 , ANC_ID_37 , ANC_ID_38 , ANC_ID_39 , ANC_ID_40 , ANC_ID_41 , ANC_ID_42 , ANC_ID_43 , ANC_ID_44 , ANC_ID_45 , ANC_ID_46 , ANC_ID_47 , ANC_ID_48 , ANC_ID_49 , ANC_ID_50 , ANC_ID_51 , ANC_ID_52 , ANC_ID_53 , ANC_ID_54 , ANC_ID_55 , ANC_ID_56 , ANC_ID_57 , ANC_ID_58 , ANC_ID_59 , ANC_ID_60 , ANC_ID_61 , ANC_ID_62 , ANC_ID_63 , ANC_ID_64 , ANC_ID_65 , ANC_ID_66 , ANC_ID_67 , ANC_ID_68 , ANC_ID_69 , ANC_ID_70 , ANC_ID_71 , ANC_ID_72 , ANC_ID_73 , ANC_ID_74 , ANC_ID_75 , ANC_ID_76 , ANC_ID_77 , ANC_ID_78 , ANC_ID_79 , ANC_ID_80 , ANC_ID_81 , ANC_ID_82 , ANC_ID_83 , ANC_ID_84 , ANC_ID_85 , ANC_ID_86 , ANC_ID_87 , ANC_ID_88 , ANC_ID_89 , ANC_ID_90 , ANC_ID_91 , ANC_ID_92 , ANC_ID_93 , ANC_ID_94 , ANC_ID_95 , ANC_ID_96 , ANC_ID_97 , ANC_ID_98 , ANC_ID_99 , ANC_ID_100 , LINE_DEFINITION_OWNER_CODE , LINE_DEFINITION_CODE , BUSINESS_CLASS_CODE , MPA_ACCRUAL_ENTRY_FLAG , ENCUMBRANCE_TYPE_ID , MERGE_INDEX , CALCULATE_G_L_AMTS_FLAG , ENTERED_CURRENCY_MAU) AE_LINE_NUM FROM XLA_AE_LINES_GT WHERE TEMP_LINE_NUM <> 0 |
| gxx5fj67t61nb | SELECT CSI_II_FORWARD_SYNC_TEMP_S.CURRVAL FROM DUAL |