| SQL Id |
SQL Text |
| 05s9358mm6vrr |
begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end; |
| 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 ; |
| 162ttnv4ubqun |
begin OE_DS_PVT.Check_PO_Approved( :t_application_id, :t_entity_short_name, :t_validation_entity_short_name, :t_validation_tmplt_short_name, :t_record_set_short_name, :t_scope, :t_result ); end; |
| 1du9v021t8m46 |
insert into gl_interface(created_by, date_created, status, actual_flag, group_id, set_of_books_id, user_je_source_name, user_je_category_name, accounting_date, subledger_doc_sequence_id, subledger_doc_sequence_value, ussgl_transaction_code, currency_code, code_combination_id, entered_dr, entered_cr, accounted_dr, accounted_cr, reference1, reference10, reference21, reference22, reference23, reference24, reference25, reference26, reference27, reference28, reference29, reference30, request_id)selec t /*+ ORDERED USE_NL (l ctlgd ct ctt cust) INDEX (ctlgd, RA_CUST_TRX_LINE_GL_DIST_N5) +*/ :b0 created_by , trunc(sysdate ) date_created , :b1 status , :b2 actual_flag , :b3 group_id , :b4 sob_id , :b5 source_name , decode(ctt.type, 'CM', :b6, 'DM', :b7, 'CB', :b8, :b9) category , ctlgd.gl_date gl_date , ct.doc_sequence_id doc_seqid , ct.doc_sequence_value doc_num , ctlgd.ussgl_transaction_code ussgl , ct.invoice_currency_code currency , ctlgd.code_combination_id ccid , decod e(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), null , nvl(ctlgd.amount, 0)), 'ROUND', decode(sign(nvl(ctlgd.acctd_amount, 0)), (-1), (-nvl(ctlgd.amount, 0)), null ), decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.amount, 0)), null )) entered_dr , decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.amount, 0)), null ), 'ROUND', decode(sign(nvl(ctlgd.acctd_amount, 0)), (-1), null , nvl(ctlgd.amount, 0)), decode(sign(nvl(ctlgd.amount , 0)), (-1), null , nvl(ctlgd.amount, 0))) entered_cr , decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), null , nvl(ctlgd.acctd_amount, 0)), 'ROUND', decode(sign(nvl(ctlgd.acctd_amount, 0)), (-1), (-nvl(ctlgd.acctd_amount, 0)), null ), decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.acctd_amount, 0)), null )) acctd_dr , decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.acctd_amount, 0)), null ), 'ROUND', decode(sign(nvl(ctlgd. acctd_amount, 0)), (-1), null , nvl(ctlgd.acctd_amount, 0)), decode(sign(nvl(ctlgd.amount, 0)), (-1), null , nvl(ctlgd.acctd_amount, 0))) acctd_cr , (:b10||to_char(:b3)) ref1 , decode(:b12, 'Y', null , ((((((l.meaning||' ')||:b13)||decode(ctt.type, 'CB', :b14, 'CM', :b15, 'DEP', :b16, 'DM', :b17, 'GUAR', :b18, 'INV', :b19, null ))||' ')||ct.trx_number)||:b20)) ref10 , to_char(:b3) ref21 , to_char(ct.customer_trx_id) ref22 , to_char(ctlgd.cust_trx_line_gl_dist_id) ref23 , ct.trx_number ref24 , cu st.account_number ref25 , 'CUSTOMER' ref26 , to_char(ct.bill_to_customer_id) ref27 , decode(ctt.type, 'CM', 'CM', 'DM', 'DM', 'CB', 'CB', 'INV') ref28 , (decode(ctt.type, 'CM', 'CM_', 'DM', 'DM_', 'CB', 'CB_', 'INV_')||ctlgd.account_class) ref29 , 'RA_CUST_TRX_LINE_GL_DIST' ref30 , :b22 from ra_cust_trx_line_gl_dist ctlgd , ra_cust_trx_line_gl_dist rec , ar_lookups l , ra_customer_trx ct , ra_cust_trx_types ctt , hz_cust_accounts cust where ((((((((((((((((ctlgd.cust_trx_line_gl_dist_id+0)<: b23 and ctlgd.gl_date between to_date((:b24||' 00:00:00'), 'DD-MON-RR HH24:MI:SS') and to_date((:b25||' 23:59:59'), 'DD-MON-RR HH24:MI:SS')) and ctlgd.posting_control_id=(-3)) and ctlgd.set_of_books_id=:b4) and ctlgd.account_set_flag='N') and ctlgd.customer_trx_id=ct.customer_trx_id) and l.lookup_type='AUTOGL_TYPE') and l.lookup_code=nvl(ctlgd.account_class, 'REV')) and ct.complete_flag='Y') and ct.bill_to_customer_id=cust.cust_account_id) and ct.cust_trx_type_id=ctt.cust_trx_type_id) and ctlgd. customer_trx_id=rec.customer_trx_id) and rec.account_class='REC') and rec.latest_rec_flag='Y') and rec.account_set_flag='N') and 'NOT_MFAR_TYPE'=AR_MFAR_PKG.ar_mfar_trx_post(ctlgd.customer_trx_id, :b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b12, :b13, :b20, :b14, :b15, :b17, :b16, :b18, :b19, :b24, :b25, :b23)) |
| 1q78dwnrrkf6c |
BEGIN ASN_SALES_PVT.Lead_Process_After_Update( P_Api_Version_Number => 2.0, P_Init_Msg_List => FND_API.G_TRUE, p_Commit => FND_API.G_FALSE, p_Validation_Level => FND_API.G_VALID_LEVEL_NONE, P_Identity_Salesforce_Id => :1, P_Salesgroup_id => :2, P_Sales_Lead_Id => :3, X_Return_Status => :4, X_Msg_Count => :5, X_Msg_Data => :6); END; |
| 20rc3cmapyvnz |
select q_name, state, delay, expiration, rowid, msgid, dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protocol from "APPLSYS"."WF_DEFERRED_TABLE_M" where msgid = :1 |
| 2fjd6cndjby9p |
INSERT INTO RA_CUST_TRX_LINE_GL_DIST ( CUSTOMER_TRX_LINE_ID, CUSTOMER_TRX_ID, CODE_COMBINATION_ID, SET_OF_BOOKS_ID, ACCOUNT_CLASS, ACCOUNT_SET_FLAG, PERCENT, AMOUNT, ACCTD_AMOUNT, GL_DATE, CUST_TRX_LINE_SALESREP_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, POSTING_CONTROL_ID, ORIGINAL_GL_DATE, CUST_TRX_LINE_GL_DIST_ID ) SELECT /*+ ORDERED */ ASS.CUSTOMER_TRX_LINE_ID, LINES.CUSTOMER_TRX _ID, DIST.CODE_COMBINATION_ID, :B12 , ASS.ACCOUNT_CLASS, 'N', ROUND( (DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * DECODE(ASS.AMOUNT, 0, DECODE(ASS.ACCOUNT_CLASS, 'REV', DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, 1, -1 ), DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, -1, 1 ) ), ASS.AMOUNT ), FC.PRECISION), ROUND( ((DIST.PERCENT/100) * DECODE(ASS.AMOUNT, 0, DECODE(ASS.ACCOUNT_CLASS, 'REV', DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, 1, -1) , DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, -1, 1) ), ASS.AMOUNT) ) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT) / DECODE(LINES.EXTENDED_AMOUNT, 0, 1, LINES.EXTENDED_AMOUNT)) * DECODE(ASS.AMOUNT, 0, 0, 100), 4), DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * ASS.AMOUNT, FC.PRECISION), ROUND( ((DIST.PERCENT/100) * ASS.AMOUNT) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT), DECODE(:B10 , NULL, ROUND( NVL(HEADER.EXCHANGE_ RATE, 1) * DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * ASS.AMOUNT, FC.PRECISION), ROUND( ((DIST.PERCENT/100) * ASS.AMOUNT) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT ), :B11 ), ROUND( ( NVL(HEADER.EXCHANGE_RATE, 1) * DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * ASS.AMOUNT, FC.PRECISION), ROUND( ( (DIST.PERCENT/100) * ASS.AMOUNT) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT ) ) / :B10 ) * :B10 ), DEC ODE(REC.GL_DATE, NULL, NULL, ARP_AUTO_RULE.ASSIGN_GL_DATE(ASS.GL_DATE) ), DECODE(DIST.CUSTOMER_TRX_ID, HEADER.CUSTOMER_TRX_ID, DIST.CUST_TRX_LINE_SALESREP_ID, CMSREP.CUST_TRX_LINE_SALESREP_ID), :B9 , :B8 , :B7 , SYSDATE, SYSDATE, :B6 , SYSDATE, :B6 , -3, ASS.GL_DATE, RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL FROM RA_CUSTOMER_TRX HEADER, FND_CURRENCIES FC, RA_CUSTOMER_TRX_LINES LINES, RA_RULES ACC_RULE, RA_CUST_TRX_LINE_GL_DIST REC, RA_CUST_TRX_LINE_GL_DIST DIST, RA_CUST_TRX_LINE _SALESREPS CMSREP, AR_REVENUE_ASSIGNMENTS ASS WHERE HEADER.CUSTOMER_TRX_ID = :B1 AND HEADER.COMPLETE_FLAG = 'Y' AND FC.CURRENCY_CODE = HEADER.INVOICE_CURRENCY_CODE AND LINES.CUSTOMER_TRX_ID = HEADER.CUSTOMER_TRX_ID AND LINES.AUTORULE_COMPLETE_FLAG||'' = 'N' AND ASS.CUSTOMER_TRX_LINE_ID = LINES.CUSTOMER_TRX_LINE_ID AND ASS.PERIOD_SET_NAME = :B5 AND ACC_RULE.RULE_ID = LINES.ACCOUNTING_RULE_ID AND DECODE(HEADER.INVOICING_RULE_ID, -3, :B3 , DECODE(NVL(ACC_RULE.DEFERRED_REVENUE_FLAG, 'N'), 'Y', :B2 , DECODE(:B4 , 'N', :B3 , DECODE(HEADER.PREVIOUS_CUSTOMER_TRX_ID, NULL, AR_REVENUE_MANAGEMENT_PVT.LINE_COLLECTIBILITY(:B1 , LINES.CUSTOMER_TRX_LINE_ID), AR_REVENUE_MANAGEMENT_PVT.CASH_BASED(HEADER.PREVIOUS_CUSTOMER_TRX_ID))))) <> :B2 AND REC.CUSTOMER_TRX_ID = HEADER.CUSTOMER_TRX_ID AND REC.ACCOUNT_CLASS = 'REC' AND REC.LATEST_REC_FLAG = 'Y' AND DIST.CUSTOMER_TRX_LINE_ID = (SELECT DECODE(COUNT(CUST_TRX_LINE_GL_DIST_ID), 0, NVL(LINES.PREVIOUS_CUSTOMER_TRX_LINE_ID, LINES.CUST OMER_TRX_LINE_ID), LINES.CUSTOMER_TRX_LINE_ID) FROM RA_CUST_TRX_LINE_GL_DIST SUBDIST2 WHERE SUBDIST2.CUSTOMER_TRX_LINE_ID = LINES.CUSTOMER_TRX_LINE_ID AND SUBDIST2.ACCOUNT_SET_FLAG = 'Y' AND SUBDIST2.GL_DATE IS NULL AND ROWNUM < 2 ) AND DIST.ACCOUNT_CLASS = ASS.ACCOUNT_CLASS AND DIST.ACCOUNT_SET_FLAG = 'Y' AND DIST.CUST_TRX_LINE_SALESREP_ID = CMSREP.PREV_CUST_TRX_LINE_SALESREP_ID (+) AND :B1 = CMSREP.CUSTOMER_TRX_ID (+) AND NOT EXISTS ( SELECT 'distribution exists' FROM RA_CUST_TRX_LINE_GL_D IST SUBDIST WHERE SUBDIST.CUSTOMER_TRX_LINE_ID = ASS.CUSTOMER_TRX_LINE_ID AND SUBDIST.CUSTOMER_TRX_ID + 0 = LINES.CUSTOMER_TRX_ID AND SUBDIST.ACCOUNT_SET_FLAG = 'N' AND SUBDIST.ACCOUNT_CLASS = ASS.ACCOUNT_CLASS AND SUBDIST.ORIGINAL_GL_DATE = ASS.GL_DATE ) |
| 2fr5f5xzmffd8 |
SELECT decode ( : P_sortby , 'PO NUMBER' , null , poh . document_buyer_last_name ) , decode ( : P_sortby , 'PO NUMBER' , null , poh . document_buyer_first_name ) , decode ( : po_num_type , 'NUMERIC' , null , poh . po_num ) , decode ( : po_num_type , 'NUMERIC' , decode ( rtrim ( poh . po_num , '0123456789' ) , NULL , to_number ( poh . po_num ) , - 1 ) , null ) , poh . po_type poh_po_type , por . release_type poh_release_type , poh . po_num || decode ( poh . po_type , 'RELE ASE' , '-' || por . release_num , null ) poh_po_num , poh . po_num security_poh_po_num , por . release_num security_por_po_num , poh . revision_num poh_revision_num , poh . vendor_name poh_vendor_name , poh . vendor_address_line1 poh_vendor_address_line1 , poh . vendor_address_line2 poh_vendor_address_line2 , poh . vendor_address_line3 poh_vendor_address_line3 , decode ( poh . vendor_city , null , poh . vendor_state || ' ' || poh . vendor_postal_code , poh . vendor_city || ', ' || poh . vendor_state || ' ' || poh . vendor_postal_code ) poh_vendor_adr_info , poh . vendor_country poh_vendor_country , poh . customer_num poh_customer , poh . vendor_num poh_vendor_num , poh . creation_date poh_creation_date , poh . revised_date poh_revised_date , substr ( substr ( poh . document_buyer_first_name , 1 , 1 ) || ' ' || poh . document_buyer_last_name , 1 , 12 ) poh_buyer , substr ( trim ( substr ( poh . archive_buyer_first_name , 1 , 1 ) || ' ' || poh . archive_buyer_l ast_name ) , 1 , 12 ) poh_archive_buyer , poh . document_buyer_agent_id poh_agent_id , poh . payment_terms poh_payment_terms , poh . ship_via poh_ship_via , poh . fob poh_fob , poh . freight_terms poh_freight_terms , substr ( poh . vendor_contact_first_name , 1 , 1 ) || ' ' || substr ( poh . vendor_contact_last_name , 1 , 10 ) poh_vendor_contact_name , poh . vendor_phone poh_vendor_phone , poh . vendor_contact_phone poh_vendor_contact_phone , poh . note_to_vendor poh_note_to_vendo r , poh . printed_date poh_printed_date , poh . amount_agreed poh_amount_agreed , poh . cancel_flag poh_cancel_flag , poh . confirming_order_flag poh_confirming_order_flag , poh . acceptance_required_flag poh_acceptance_req_flag , poh . acceptance_due_date poh_acceptance_due_date , poh . currency_code poh_currency_code , poh . currency_code C_CURRENCY , poh . currency_name poh_currency_name , poh . currency_conversion_rate poh_currency_conversion_rate , poh . bill_to_location_id poh_b ill_to_location , poh . ship_to_location_id poh_ship_to_location , poh . po_header_id poh_po_header_id , poh . po_release_id poh_po_release_id , poh . po_type poh_po_type , poh . approved_flag poh_approved_flag , poh . print_count poh_print_count , poh . effective_date poh_effective_date , poh . expiration_date poh_expiration_date , nvl ( poh . po_release_id , - 1 ) poh_join_release_id , poh . vendor_site_id poh_vendor_site_id , poh . vendor_id poh_vendor_id FROM po_headers_print poh , po_releases por WHERE poh . po_release_id = por . po_release_id (+) AND ( nvl ( por . release_num , - 1 ) BETWEEN nvl ( : P_release_num_from , nvl ( por . release_num , - 1 ) ) AND nvl ( : P_release_num_to , nvl ( por . release_num , - 1 ) ) OR poh . po_type != 'RELEASE' ) AND poh . document_buyer_agent_id = nvl ( : P_agent_id , poh . document_buyer_agent_id ) AND ( poh . release_date is NULL OR trunc ( poh . release_date ) BETWEEN nvl ( : P_date_from , trunc ( poh . release_date ) ) AND nvl ( : P_date_to , trunc ( poh . release_date ) + 1 ) ) AND nvl ( poh . approved_flag , 'N' ) = nvl ( : P_approved_flag , nvl ( poh . approved_flag , 'N' ) ) AND ( ( nvl ( : P_print_releases , 'Y' ) = 'Y' ) or ( nvl ( : P_print_releases , 'Y' ) = 'N' and poh . po_type != 'RELEASE' ) ) AND nvl ( poh . consigned_consumption_flag , 'N' ) <> 'Y' AND nvl ( por . consigned_consumption_flag , 'N' ) <> 'Y' AND :P_SINGLE_PO_PRINT != 1 AND :P_PO_ NUM_TYPE = 'NUMERIC' AND decode(rtrim(poh.po_num, '0123456789'), NULL, to_number(poh.po_num), -1) BETWEEN decode(rtrim(nvl(:P_po_num_from, poh.po_num), '0123456789'), NULL, to_number(nvl(:P_po_num_from, poh.po_num)), -1) AND decode(rtrim(nvl(:P_po_num_to, poh.po_num), '0123456789'), NULL, to_number(nvl(:P_po_num_to, poh.po_num)), -1) AND nvl(poh.print_count, 0) = 0 AND NOT EXISTS (SELECT 'X' from ECE_TP_DETAILS ETD, ECE_TP_HEADERS ETH, PO_VENDOR_SITES E_PVS WHERE E_PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND ETH.TP_HEADER_ID = E_PVS.TP_HEADER_ID AND ETD.TP_HEADER_ID = ETH.TP_HEADER_ID AND ETD.EDI_FLAG = 'Y' AND ETD.DOCUMENT_ID = 'POO' AND ETD.DOCUMENT_TYPE = POH.PO_TYPE ) and 1=1 ORDER BY 1 , 2 , 3 , 4 |
| 38dj2kuc5m9q3 |
SELECT TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'DY')REACT_DAY_CHAR , TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D') REACT_DAY_NUM , DECODE(SIGN((TO_NUMBER(TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D')) - TO_NUMBER(TO_CHAR(:B1 , 'D')))+1), 1 , TO_NUMBER(TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D')) - TO_NUMBER(TO_CHAR(:B1 , 'D')) , TO_NUMBER(TO_CHAR(NEXT_DAY(:B1 , OKS_ENTI TLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D')) - TO_NUMBER(TO_CHAR(:B1 , 'D')) + 7) REQDAY_RELATIVE FROM DUAL |
| 3c086a4sm741b |
select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . cust_trx_type_id ) dummy_id , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . name ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , cust_acct . cust_account_id customer_id , cust_acct . account_number customer_number , types . name type , ps . payment_schedule_id payment_sched_id , ps . class class , ps . due_date due_date , decode ( : rp_convert_flag , 'Y' , ps . acctd_amount_due_remaining , ps . amount_due_remaining ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_DATE - ps . due_date ) days_past_due , ps . amount_adjusted amount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , ps . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . excha nge_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp _bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bu cket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b1 , decode ( : rp_bucket_line_type_2 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp _bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_3 , ceil ( : P _AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( par ty . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from ra_cust_trx_types_all types , hz_cust_accounts cust_acct , hz_parties party , AR_PAYMENT_SCHEDULES_ALL ps , RA_CUST_TRX_LINE_GL_DIST_ALL gld , gl_code_combinations cc where TRUNC ( ps.gl_date ) <= : p_as_of_date and ps.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and cust_acct.account_number >= : p_customer_number_low and cust_acct.account_number <= : p_custome r_number_high and ps.cust_trx_type_id = types.cust_trx_type_id and nvl ( ps.org_id , - 99 ) = nvl ( types.org_id , - 99 ) and ps.gl_date_closed > : P_AS_OF_DATE and ps.customer_trx_id + 0 = gld.customer_trx_id and gld.account_class = 'REC' and gld.latest_rec_flag = 'Y' and gld.code_combination_id = cc.code_combination_id AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( TYPES.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( GLD.ORG_I D , : p_reporting_entity_id ) = : p_reporting_entity_id UNION ALL select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , - 999 ) dummy_id , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , decode ( app . applied_payment_schedule_id , - 4 , : c_claim_meaning , : c_payment_meaning ) ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , nvl ( cust_acct . cust_account_id , - 999 ) customer_id , cust_acct . account_number customer_ number , decode ( : p_order_by , 'XXX' , NULL , decode ( app . applied_payment_schedule_id , - 4 , : c_claim_meaning , : c_payment_meaning ) ) type , ps . payment_schedule_id payment_sched_id , ps . class class , ps . due_date due_date , - sum ( decode ( : rp_convert_flag , 'Y' , app . acctd_amount_applied_from , app . amount_applied ) ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_DATE - ps . due_date ) days_past_due , ps . amount_adjusted am ount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , ps . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . exchange_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b1 , decode ( : rp_bucket_line_type_2 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , c eil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISP UTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( party . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from AR_PAYMENT_SCHEDULES_ALL ps , AR_RECEIVABLE_APPLICATIONS_ALL app , gl_code_combinations cc , hz_cust_accounts cust_acct , hz_parti es party where app.gl_date + 0 <= : P_AS_OF_DATE and ps.customer_id = cust_acct.cust_account_id (+) and cust_acct.party_id = party.party_id (+) and nvl ( cust_acct.account_number , : pd_customer_number_low ) >= : p_customer_number_low and nvl ( cust_acct.account_number , : pd_customer_number_low ) <= : p_customer_number_high and ps.cash_receipt_id + 0 = app.cash_receipt_id and app.code_combination_id = cc.code_combination_id and app.status in ( 'ACC' , 'UNAPP' , 'UNID' , 'OTHER A CC' ) and nvl ( app.confirmed_flag , 'Y' ) = 'Y' and ps.gl_date_closed > : P_AS_OF_DATE and ( ( app.reversal_gl_date is not null AND ps.gl_date <= : P_AS_OF_DATE ) OR app.reversal_gl_date is null ) and nvl ( ps.receipt_confirmed_flag , 'Y' ) = 'Y' AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( APP.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id group by party.party_name , cust_acct.account_number , cust_acct.cust_account_id , ps.paym ent_schedule_id , app.applied_payment_schedule_id , ps.due_date , ps.amount_due_original , ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.gl_date , ps.amount_in_dispute , ps.amount_adjusted_pending , ps.invoice_currency_code , ps.exchange_rate , ps.class , cc.SEGMENT1 , decode ( app.status , 'UNID' , 'UNID' , 'UNAPP' ) UNION ALL select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , - 999 ) , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , : c_risk_meaning ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , nvl ( cust_acct . cust_account_id , - 999 ) customer_id , cust_acct . account_number customer_number , : c_risk_meaning type , ps . payment_schedule_id payment_sched_id , : c_risk_meaning class , ps . due_date due_date , decode ( : rp_convert_flag , 'Y' , crh . acctd_amount , crh . amount ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_ DATE - ps . due_date ) days_past_due , ps . amount_adjusted amount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , crh . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . exchange_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( gre atest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b1 , decode ( : rp_bucket_line_type _2 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( greatest ( : rp_bucket_days_from_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ce il ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( party . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from hz_cust_accounts cust_acct , hz_parties party , AR_PAYMENT_SCHEDULES_ALL ps , AR_CASH_RECEIPTS_ALL cr , AR_CASH_RECEIPT_HISTORY_ALL crh , gl_code_combinations cc where crh.gl_date + 0 <= : P_AS_OF_DATE and upper ( : p_risk_option ) != 'NONE' and ps.customer_id = cust_acct.cu st_account_id (+) and cust_acct.party_id = party.party_id (+) and cust_acct.account_number >= : p_customer_number_low and cust_acct.account_number <= : p_customer_number_high and ps.cash_receipt_id = cr.cash_receipt_id and cr.cash_receipt_id = crh.cash_receipt_id and crh.account_code_combination_id = cc.code_combination_id and ( crh.current_record_flag = 'Y' or crh.reversal_gl_date > : p_as_of_date ) and crh.status not in ( decode ( crh.factor_flag , 'Y' , 'RISK_ELIMINATED' , 'N' , 'CLEARED' ) , 'REVERSED' ) and 0 = ( select nvl ( sum ( ra.amount_applied ) , 0 ) from AR_RECEIVABLE_APPLICATIONS_ALL ra where ra.cash_receipt_id = cr.cash_receipt_id and ra.status = 'ACTIVITY' and applied_payment_schedule_id = - 2 and ra.gl_date <= to_date ( : p_as_of_date ) ) AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( CR.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( CRH.ORG_ID , : p_reporting_entity_id ) = : p_reporting_ entity_id UNION ALL select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . cust_trx_type_id ) dummy_id , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . name ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , cust_acct . cust_account_id customer_id , cust_acct . account_number customer_number , types . name type , ps . payment_schedule_id payment_sched_id , ps . class class , ps . due_date due_date , decode ( : rp_convert_flag , 'Y' , ps . acctd_amount_due_remaining , ps . amount_due_remaining ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_DATE - ps . due_date ) days_past_due , ps . amount_adjusted amount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , ps . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . exchange_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b1 , decode ( : rp_bucket_line_type_2 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_ from_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( party . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from ra_cust_trx_types_all types , hz_cust_accounts cust_acct , hz_parties party , AR_PAYMENT_SCHEDULES_ALL ps , ar_transaction_history_all th , AR_DISTRIBUTIONS_ALL dist , gl_code_combinations cc where TRUNC ( ps.gl_date ) <= : p_as_of_date and ps.class = 'BR' and ps.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and cust_acct.account_number >= : p_ customer_number_low and cust_acct.account_number <= : p_customer_number_high and ps.cust_trx_type_id = types.cust_trx_type_id and nvl ( ps.org_id , - 99 ) = nvl ( types.org_id , - 99 ) and ps.gl_date_closed > : P_AS_OF_DATE and ps.customer_trx_id + 0 = th.customer_trx_id and th.transaction_history_id = dist.source_id and dist.source_table = 'TH' and th.transaction_history_id = ( select max ( transaction_history_id ) from ar_transaction_history_all th2 , AR_DISTRIBUTIONS_ALL dist2 where th2.transaction_history_id = dist2.source_id and dist2.source_table = 'TH' and th2.gl_date <= to_date ( : p_as_of_date ) and dist2.amount_dr is not null and th2.customer_trx_id = ps.customer_trx_id ) and dist.amount_dr is not null and dist.source_table_secondary is NULL and dist.code_combination_id = cc.code_combination_id AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( TYPES.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id ORDER BY 23 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC , 23 , 3 , 5 |
| 3dssuqfjsynrk |
SELECT count(1) FROM (SELECT * FROM ( SELECT LeadEO.sales_lead_id as SalesLeadId , to_char(hz_timezone_pub.convert_datetime(:1, hcp.timezone_id, SYSDATE), 'HH:MI AM ') ||' '||ht.standard_time_short_code as PrimaryContactLocalTime , SUBSTRB(LeadEO.description, 1, 240) as Description , hp.party_name as ContactName , custParty.party_name as CustomerName , aslt.meaning as RankName , ast.meaning as LeadStatus , ROUND(SYSDATE - LeadEO.creation_date) as Age , DECODE(LeadEO.status_code, ' CONVERTED_TO_OPPORTUNITY', 'ASNCnvToOpptyDsbld', 'ASNCnvToOpptyEnbld') as ASNLeadLstCnvToOppty , LeadEO.customer_id , hp.party_id , LeadEO.primary_contact_party_id , hr.relationship_id FROM as_sales_leads LeadEO , ( SELECT /*+ no_merge */ distinct aaa.sales_lead_id, aaa.customer_id FROM as_accesses_all aaa WHERE aaa.sales_lead_id IS NOT NULL AND aaa.lead_id IS NULL AND aaa.salesforce_id = :2) secu , as_statuses_tl ast , as_sales_lead_ranks_tl aslt , hz_parties CustPa rty , hz_parties hp, hz_relationships hr , hz_contact_points hcp , hz_timezones ht , ams_source_codes amsc WHERE LeadEO.sales_lead_id = secu.sales_lead_id AND LeadEO.customer_id = secu.customer_id AND LeadEO.status_code = ast.status_code AND ast.language = USERENV('LANG') AND LeadEO.lead_rank_id = aslt.rank_id (+) AND aslt.language (+) = USERENV('LANG') AND LeadEO.customer_id = CustParty.party_id AND LeadEO.primary_contact_party_id = hr.party_id(+) AND hr.subject_ id = hp.party_id (+) AND hr.subject_table_name (+) = 'HZ_PARTIES' AND hr.object_id(+) = LeadEO.customer_id AND hr.object_table_name (+) = 'HZ_PARTIES' AND LeadEO.primary_contact_party_id = hcp.owner_table_id(+) AND hcp.owner_table_name (+) = 'HZ_PARTIES' AND hcp.primary_flag (+) = 'Y' AND hcp.contact_point_type (+) = 'PHONE' AND hcp.timezone_id = ht.timezone_id (+) AND LeadEO.source_promotion_id = amsc.source_code_id AND amsc.source_code_for_id = :3 AND LeadEO.sta tus_code = :4 ORDER BY age ) ) |
| 3g160axpg863b |
SELECT fnd_number.number_to_canonical(/*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0) ) FROM pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and nvl(PACT.future_process_mode, 'Y') = 'Y' and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and pay_core_utils.include_action_in_payment(BACT.action_type, BAL_ASSACT.assignment_action_id, ASSACT.assignment_action_i d) = 'Y' and NOT EXISTS (SELECT NULL FROM pay_payroll_actions RPACT , pay_assignment_actions RASSACT , pay_action_interlocks RINTLK where ASSACT.assignment_action_id = RINTLK.locked_action_id and RINTLK.locking_action_id = RASSACT.assignment_action_id and RPACT.payroll_action_id = RASSACT.payroll_action_id and RPACT.action_type = 'V') |
| 3prk3vg9u0abb |
BEGIN FND_GLOBAL.APPS_INITIALIZE( user_id=>:1 , resp_id=>:2 , resp_appl_id=>:3 , security_group_id=>:4 , server_id=>:5);END; |
| 3vy5h9xkfv1s5 |
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES ( ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE, NUMBER_VALUE, DATE_VALUE ) SELECT :B1 , :B2 , WIA.NAME, WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT FROM WF_ITEM_ATTRIBUTES WIA WHERE WIA.ITEM_TYPE = :B1 |
| 4ds53shhfcynn |
declare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end; |
| 4yak7x2zqz0dh |
BEGIN inv_transfer_order_pvt.finalize_pick_confirm(:1, :2, :3, :4, :5, :6, :7); END; |
| 546kskr40arst |
select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV EE ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, IV PAY_INPUT_VALUES_F_PK, RRV PAY_RUN_RESULT_VALUES_N50, LIV +*/ EL.element_link_id , EL.costable_type , decode(:b0, 0, EL.cost_allocation_keyflex_id, EL2.cost_allocation_keyflex_id) , decode(:b0, 0, EL.balancing_keyflex_id, EL2.balancing_keyflex_id) , EL.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , decode(:b2, 'A', decode(EE.entry_type, 'A', EE.target_entry_id, 'R', EE.target_entry_id, RR.source_id), RR.source_id) , EE.creator_type , EE.cost_allocation_keyflex_id , EE.source_id , EE.source_asg_action_id , EE.source_link_id , RR.element_type_id , to_char(RRV.input_value_id) , RRV.result_value , IV.uom , decode(EL.costable_type, 'D', decode(EE.creator_type, 'E', 2, 'EE', 2, 3), 1) , RR.jurisdi ction_code , 'E' , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b0, 0, PA.date_earned, nvl(RR.end_date, PA.date_earned)), 'YYYY/MM/DD') from pay_assignment_actions AA , pay_payroll_actions PA , pay_run_results RR , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_input_values_f IV , pay_element_entries_f EE , pay_element_types_f ET , fnd_currencies FC , pay_element_classifications EC , pay_element_links_f EL , pay_element_links_f EL2 where (((((((((( (((((((((((((((((AA.assignment_action_id=:b4 and AA.payroll_action_id=PA.payroll_action_id) and RR.assignment_action_id=:b4) and RR.status like 'P%') and RR.source_type not in ('I', 'V')) and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and PA.date_earned between IV.effective_start_date and IV.effective_end_date) and IV.element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b6, 'Y', 1, fnd_ number.canonical_to_number(RRV.result_value))+decode(IV.uom, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and PA.date_earned between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_id=EL.element_link_id) and RR.source_id=EE.element_entry_id) and nvl(RR.end_date, PA.date_earned) between EE.effective_start_date and EE.effective_end_date) and ((PA.action_type='B' and nvl(EE.BALANCE_ADJ_COST_FLAG, 'N')='Y') or PA.action_type<& gt;'B')) and EE.element_link_id=EL.element_link_id) and PA.date_earned between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and (EL.element_type_id+0)=(ET.element_type_id+0)) and PA.date_earned between ET.effective_start_date and ET.effective_end_date) and ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and EL2.element_link_id=EL.element_link_id) and nvl(decode(EL.costable_type, 'D', PA.date_earned, RR.end_date), PA. date_earned) between EL2.effective_start_date and EL2.effective_end_date) union all select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV PERA ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, IV PAY_INPUT_VALUES_F_PK, PERA PER_ASSIGNMENTS_F_PK, RRV PA +*/ EL.element_link_id , EL.costable_type , decode(:b0, 0, EL.cost_allocation_keyflex_id, EL2.cost_allocation_keyflex_id) , decode(:b0, 0, EL. balancing_keyflex_id, EL2.balancing_keyflex_id) , EL.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , RR.source_id , 'N' , 0 , 0 , 0 , 0 , RR.element_type_id , to_char(RRV.input_value_id) , RRV.result_value , IV.uom , decode(EL.costable_type, 'D', 3, 1) , RR.jurisdiction_code , decode(RR.source_type, 'I', 'I', 'V', 'V', 'D') , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b0, 0, PA.da te_earned, nvl(RR.end_date, PA.date_earned)), 'YYYY/MM/DD') from pay_assignment_actions AA , pay_payroll_actions PA , pay_run_results RR , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_input_values_f IV , per_all_assignments_f PERA , pay_element_types_f ET , fnd_currencies FC , pay_element_classifications EC , pay_element_links_f EL , pay_element_links_f EL2 where (((((((((((((((((((((((((((((((((((((AA.assignment_action_id=:b4 and AA.payroll_action_id=PA.payroll_action_id) and ( not exists (select null from pay_element_entries_f EE1 where (RR.source_id=EE1.element_entry_id and nvl(RR.end_date, PA.date_earned) between EE1.effective_start_date and EE1.effective_end_date)) or RR.source_type in ('I', 'V'))) and RR.assignment_action_id=:b4) and RR.status like 'P%') and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and PA.date_earned between IV.effective_start_date and IV.effective_end_date) and IV .element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b6, 'Y', 1, fnd_number.canonical_to_number(RRV.result_value))+decode(IV.uom, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and PA.date_earned between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_id=EL.element_link_id) and RR.element_type_id=ET.element_type_id) and PA.date_earned between ET.effective_start_date and ET.effective_end_date) an d ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and (ET.element_type_id+0)=(EL.element_type_id+0)) and PA.date_earned between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and ((PA.action_type='B' and hr_cost.cost_bal_adj(RR.source_id, PA.date_earned)='Y') or PA.action_type<>'B')) and PERA.assignment_id=:b13) and (PERA.business_group_id+0)=(EL.business_group_id+0)) and PA.date_earned between PERA.effective_sta rt_date and PERA.effective_end_date) and (((EL.payroll_id is not null and EL.payroll_id=PERA.payroll_id) or (EL.link_to_all_payrolls_flag='Y' and PERA.payroll_id is not null )) or EL.payroll_id is null )) and (EL.organization_id=PERA.organization_id or EL.organization_id is null )) and (EL.position_id=PERA.position_id or EL.position_id is null )) and (EL.job_id=PERA.job_id or EL.job_id is null )) and (EL.grade_id=PERA.grade_id or EL.grade_id is null )) and (EL.location_id=PERA.location_id or EL.location_id is null )) and (EL.pay_basis_id=PERA.pay_basis_id or EL.pay_basis_id is null )) and (EL.employment_category=PERA.employment_category or EL.employment_category is null )) and (EL.people_group_id is null or exists (select 1 from pay_assignment_link_usages_f PAL where ((PAL.assignment_id=:b13 and PAL.element_link_id=EL.element_link_id) and PA.date_earned between PAL.effective_start_date and PAL.effective_end_date)))) and EL2.element_link_id=EL.element_link_id) and nvl(decode(EL.cos table_type, 'D', PA.date_earned, RR.end_date), PA.date_earned) between EL2.effective_start_date and EL2.effective_end_date) order by 19, 8, 16 |
| 64qc329wgjv39 |
select /*+ ORDERED INDEX(ASSBAL PAY_ASSIGNMENT_LATEST_BALA_N1) INDEX(DEFBAL PAY_DEFINED_BALANCES_PK) INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PACT PAY_PAYROLL_ACTIONS_PK) INDEX(VALUE PAY_BALANCE_CONTEXT_VALUES_PK) USE_NL(ASSBAL DEFBAL ACT PACT VALUE) +*/ ASSBAL.defined_balance_id , DEFBAL.balance_dimension_id , to_char(ASSBAL.value) , ASSBAL.latest_balance_id , ASSBAL.rowid , to_char(ASSBAL.assignment_action_id) , ACT.payroll_action_id , to_char(PACT.effective_date, :b0) , to_number(to_cha r(PACT.effective_date, 'J')) , nvl(VALUE.context_id, 0) , nvl(VALUE.value, 'NULL') , to_char(ASSBAL.expired_assignment_action_id) , to_char(ASSBAL.expired_value) , to_char(ASSBAL.prev_assignment_action_id) , to_char(ASSBAL.prev_balance_value) from pay_assignment_latest_balances ASSBAL , pay_defined_balances DEFBAL , pay_assignment_actions ACT , pay_payroll_actions PACT , pay_balance_context_values VALUE where ((((ASSBAL.assignment_id=:b1 and ACT.assignment_action_id=ASSBAL.assignment_action_id) and PACT.payroll_action_id=ACT.payroll_action_id) and DEFBAL.defined_balance_id=ASSBAL.defined_balance_id) and VALUE.latest_balance_id(+)=ASSBAL.latest_balance_id) union all select /*+ ORDERED INDEX(PERBAL PAY_PERSON_LATEST_BALANCES_N2) INDEX(DEFBAL PAY_DEFINED_BALANCES_PK) INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK) INDEX(VALUE PAY_BALANCE_CONTEXT_VALUES_PK) INDEX(PACT PAY_PAYROLL_ACTIONS_PK) USE_NL(PERBAL DEFBAL ACT PACT VALUE) +*/ PERBAL.defined_balance_id , DEFBAL.balance_dimension_id , to_char (PERBAL.value) , PERBAL.latest_balance_id , PERBAL.rowid , to_char(PERBAL.assignment_action_id) , ACT.payroll_action_id , to_char(PACT.effective_date, :b0) , to_number(to_char(PACT.effective_date, 'J')) , nvl(VALUE.context_id, 0) , nvl(VALUE.value, 'NULL') , to_char(PERBAL.expired_assignment_action_id) , to_char(PERBAL.expired_value) , to_char(PERBAL.prev_assignment_action_id) , to_char(PERBAL.prev_balance_value) from pay_person_latest_balances PERBAL , pay_defined_balances DEFBAL , pay_assign ment_actions ACT , pay_payroll_actions PACT , pay_balance_context_values VALUE where ((((PERBAL.person_id=:b3 and ACT.assignment_action_id=PERBAL.assignment_action_id) and PACT.payroll_action_id=ACT.payroll_action_id) and DEFBAL.defined_balance_id=PERBAL.defined_balance_id) and VALUE.latest_balance_id(+)=PERBAL.latest_balance_id) order by 6, 2, 1, 4, 10 desc |
| 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 |
| 6cjfxb1c4kx16 |
SELECT PROFILE_OPTION_ID, APPLICATION_ID, SITE_ENABLED_FLAG , APP_ENABLED_FLAG , RESP_ENABLED_FLAG , USER_ENABLED_FLAG, ORG_ENABLED_FLAG , SERVER_ENABLED_FLAG, HIERARCHY_TYPE FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME = UPPER(:B1 ) AND START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE |
| 6gnth2mfbs2nq |
declare v_ret number := 0; begin if (INV_TABLE_LOCK_PVT.lock_onhand_records(:1, :2, :3, :4, :5, :6, :7) ) then v_ret := 1; end if; :8 := v_ret; end; |
| 6h2tpcxpymqu7 |
BEGIN :1 := FND_AOLJ_UTIL.is_Valid_ICX(:2, NULL, 'Y', TRUE, TRUE, NULL, NULL, NULL, NULL, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, TRUE);:19 := fnd_session_management.g_enc_key;:20 := fnd_session_management.g_mac_key;END; |
| 72cqcsuq5wgh0 |
SELECT aslrt.meaning as rank_name , aslrt.rank_id, sum(case when asl.status_open_flag = 'Y' AND round(sysdate-asl.creation_date)<= 2 THEN 1 else 0 END) as day0_2, sum(case when asl.status_open_flag = 'Y' AND round((sysdate - asl.creation_date)) between 3 and 7 THEN 1 else 0 END) as day3_7, sum(case when asl.status_open_flag = 'Y' AND round((sysdate - asl.creation_date)) between 8 and 30 THEN 1 else 0 END) as day8_30, sum(case when asl.status_open_fla g = 'Y' AND round((sysdate - asl.creation_date)) >= 31 THEN 1 else 0 END) as day_31, sum(case when nvl(asl.status_open_flag, 'N') = 'N' AND asl.status_code <> 'CONVERTED_TO_OPPORTUNITY' THEN 1 ELSE 0 END) as closed, sum(case when asl.status_code = 'CONVERTED_TO_OPPORTUNITY' THEN 1 ELSE 0 END) as converted, round((sum(case when asl.status_code = 'CONVERTED_TO_OPPORTUNITY' THEN 1 ELSE 0 END)/count(asl.sales_lead_id)) * 100, 2) as conversion, count(aslrt.ra nk_id) total_leads FROM as_sales_leads asl, as_sales_lead_ranks_tl aslrt, as_sales_lead_ranks_b aslrb, ( SELECT /*+ no_merge */ distinct aaa.sales_lead_id , aaa.customer_id FROM as_accesses_all aaa WHERE aaa.lead_id is null AND aaa.sales_lead_id IS NOT NULL AND aaa.salesforce_id = :1)secu WHERE asl.lead_rank_id = aslrt.rank_id AND aslrb.rank_id = aslrt.rank_id AND aslrt.language = USERENV('LANG') AND secu.sa les_lead_id = asl.sales_lead_id AND secu.customer_id = asl.customer_id GROUP BY aslrt.rank_id, aslrt.meaning, aslrb.min_score ORDER BY aslrb.min_score DESC |
| 73z18fnnbb7dw |
delete from sys.wri$_optstat_histhead_history where savtime < :1 |
| 7g05xfvc5vp74 |
BEGIN arp_run.revenue_recognition( :errbuf, :rc, :A0, :A1, :A2); END; |
| 8ay7m8qubw98j |
select * from PO_TAX_LINES_SUMMARY_V where trx_header_id = :b1 ORDER BY trx_header_id, trx_line_id, trx_shipment_id , trx_distribution_id |
| 8bsm049u7thjd |
select min(next_date) from "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_T" |
| 8c30hkqv9n7cr |
BEGIN OE_BULK_ORDER_IMPORT_PVT.ORDER_IMPORT_CONC_PGM( :errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8); END; |
| 8c8cf4vtv4tx2 |
SELECT * FROM ( SELECT LeadEO.sales_lead_id as SalesLeadId , to_char(hz_timezone_pub.convert_datetime(:1, hcp.timezone_id, SYSDATE), 'HH:MI AM ') ||' '||ht.standard_time_short_code as PrimaryContactLocalTime , SUBSTRB(LeadEO.description, 1, 240) as Description , hp.party_name as ContactName , custParty.party_name as CustomerName , aslt.meaning as RankName , ast.meaning as LeadStatus , ROUND(SYSDATE - LeadEO.creation_date) as Age , DECODE(LeadEO.status_code, 'CONVERTED_TO_OPPORTUNI TY', 'ASNCnvToOpptyDsbld', 'ASNCnvToOpptyEnbld') as ASNLeadLstCnvToOppty , LeadEO.customer_id , hp.party_id , LeadEO.primary_contact_party_id , hr.relationship_id FROM as_sales_leads LeadEO , ( SELECT /*+ no_merge */ distinct aaa.sales_lead_id, aaa.customer_id FROM as_accesses_all aaa WHERE aaa.sales_lead_id IS NOT NULL AND aaa.lead_id IS NULL AND aaa.salesforce_id = :2) secu , as_statuses_tl ast , as_sales_lead_ranks_tl aslt , hz_parties CustParty , hz_parties hp, hz_relationships hr , hz_contact_points hcp , hz_timezones ht , ams_source_codes amsc WHERE LeadEO.sales_lead_id = secu.sales_lead_id AND LeadEO.customer_id = secu.customer_id AND LeadEO.status_code = ast.status_code AND ast.language = USERENV('LANG') AND LeadEO.lead_rank_id = aslt.rank_id (+) AND aslt.language (+) = USERENV('LANG') AND LeadEO.customer_id = CustParty.party_id AND LeadEO.primary_contact_party_id = hr.party_id(+) AND hr.subject_id = hp.party_id (+) AND hr.subject_table_name (+) = 'HZ_PARTIES' AND hr.object_id(+) = LeadEO.customer_id AND hr.object_table_name (+) = 'HZ_PARTIES' AND LeadEO.primary_contact_party_id = hcp.owner_table_id(+) AND hcp.owner_table_name (+) = 'HZ_PARTIES' AND hcp.primary_flag (+) = 'Y' AND hcp.contact_point_type (+) = 'PHONE' AND hcp.timezone_id = ht.timezone_id (+) AND LeadEO.source_promotion_id = amsc.source_code_id AND amsc.source_code_for_id = :3 AND LeadEO.status_code = :4 ORDER BY age ) |
| 8camsgjsr9zya |
begin OE_TRANSACTION_TYPES_PKG.Val_Ord_Change( :t_application_id, :t_entity_short_name, :t_validation_entity_short_name, :t_validation_tmplt_short_name, :t_record_set_short_name, :t_scope, :t_result ); end; |
| 8n2cx11wsxuqv |
select int.rowid , decode(int.SEGMENT1|| int.SEGMENT2|| int.SEGMENT3|| int.SEGMENT4|| int.SEGMENT5 , '', replace(ccid_cc.SEGMENT3, '-', ' ') || '-' || replace(ccid_cc.SEGMENT1, '-', ' ') || '-' || replace(ccid_cc.SEGMENT2, '-', ' ') || '-' || replace(ccid_cc.SEGMENT4, '-', ' ') || '-' || replace(ccid_cc.SEGMENT5, '-', ' ') , replace(int.SEGMENT3, '-', ' ') || '-' || replace(int.SEGMENT1, '-', ' ') || '-' || replace(int.SEGMENT2, '-', ' ') || '-' || replace(int.SEGMENT4, '-', ' ') || '-' | | replace(int.SEGMENT5, '-', ' ') ) flexfield , nvl(flex_cc.code_combination_id, nvl(int.code_combination_id, -4)) , decode(int.SEGMENT1|| int.SEGMENT2|| int.SEGMENT3|| int.SEGMENT4|| int.SEGMENT5 , '', decode(ccid_cc.code_combination_id, null, decode(int.code_combination_id, null, -4, -5), decode(sign(nvl(ccid_cc.start_date_active, int.accounting_date-1) - int.accounting_date), 1, -1, decode(sign(nvl(ccid_cc.end_date_active, int.accounting_date +1) - int.accounting_date), -1, -1, 0)) + decode(ccid_cc.enabled_flag, 'N', -10, 0) + decode(ccid_cc.summary_flag, 'Y', -100, decode(int.actual_flag, 'B', decode(ccid_cc.detail_budgeting_allowed_flag, 'N', -100, 0), decode(ccid_cc.detail_posting_allowed_flag, 'N', -100, 0)))), decode(flex_cc.code_combination_id, null, -4, decode(sign(nvl(flex_cc.start_date_active, int.accounting_date-1) - int.accounting_date), 1, -1, decode(sign(nvl(flex_cc.end_date_active, int.accounting_date +1) - int.accountin g_date), -1, -1, 0)) + decode(flex_cc.enabled_flag, 'N', -10, 0) + decode(flex_cc.summary_flag, 'Y', -100, decode(int.actual_flag, 'B', decode(flex_cc.detail_budgeting_allowed_flag, 'N', -100, 0), decode(flex_cc.detail_posting_allowed_flag, 'N', -100, 0))))) , int.user_je_category_name , int.user_je_category_name , 'UNKNOWN' period_name , decode(actual_flag, 'B' , decode(period_name, NULL, '-1' , period_name), nvl(period_name, '0')) period_name2 , currency_c ode , decode(actual_flag , 'A', actual_flag , 'B', decode(budget_version_id , 1002, '2' , 1003, '2' , 1004, '2' , 1005, '2' , 1006, '2' , 1065, '2' , 1066, '2' , 1087, '2' , 1290, actual_flag , 1311, actual_flag , 1351, actual_flag , 1659, actual_flag , 1939, '2' , 1972, actual_flag , NULL, '1', '6') , 'E', decode(encumbrance_type_id , 1000 , actual_flag , 1001, actual_flag , 1022, actual_flag , 1023, actual_flag , 1024, actual_flag , 1048, actual_flag , 1049, actual_flag , 1050, actual_flag , 1025, actual_flag , 999, actual_flag , 1045, actual_flag , 1046, actual_flag , 1047, actual_flag , 1068, actual_flag , 1088, actual_flag , NULL, '3', '4'), '5') actual_flag , '0' exception_rate , decode( currency_code , 'USD', 1 , 'STAT', 1 , decode(actual_flag, 'E', -8, 'B', 1 , decode(user_currency_conversion_type , 'User', decode(currency_conversion_rate, NULL, -1, currency_conversion_rate) , 'Corporate', decode(currency_conversion_date, NULL, -2, -6) , 'Spot', decode(currency_conversion_date, NULL, -2, -6) , 'Reporting', decode(currency_conversion_date, NULL, -2, -6) , 'HRUK', decode(currency_conversion_date, NULL, -2, -6) , 'DALY', decode(currency_conversion_date, NULL, -2, -6) , NULL, decode(currency_conversion_rate, NULL, decode(decode(nvl(to_char(entered_dr), 'X'), 'X', 1, 2), decode(nvl(to_char(accounted_dr), 'X'), 'X', 1, 2), decode(decode(nvl(to_char(entered_cr), 'X'), 'X', 1, 2), decode(nvl(to_char(accounted_cr), 'X'), 'X', 1, 2), -20, -3), -3), -9), -9))) currency_conversion_rate , to_number(to_char(nvl(int.currency_conversion_date, in t.accounting_date), 'J')) , decode(int.actual_flag , 'A', decode(int.currency_code , 'USD', 'User' , 'STAT', 'User' , nvl(int.user_currency_conversion_type, 'User')) , 'B', 'User', 'E', 'User' , nvl(int.user_currency_conversion_type, 'User')) user_currency_conversion_type , ltrim(rtrim(substrb(rtrim(substrb(int.reference1, 1, 50)) || ' ' || int.user_je_source_name || ' 2749104: ' || int.actual_flag || ' ' || int.group_id, 1, 100))) , rtrim(substrb(nvl(rtrim(in t.reference2), 'Journal Import ' || int.user_je_source_name || ' 2749104:'), 1, 240)) , ltrim(rtrim(substrb(rtrim(rtrim(substrb(int.reference4, 1, 25)) || ' ' || int.user_je_category_name || ' ' || int.currency_code || decode(int.actual_flag, 'E', ' ' || int.encumbrance_type_id, 'B', ' ' || int.budget_version_id, '') || ' ' || int.user_currency_conversion_type || ' ' || decode(int.user_currency_conversion_type, NULL, '', 'User', to_char(int.currency_conversion_rate), to_char(in t.currency_conversion_date))) || ' ' || substrb(int.reference8, 1, 15) || int.originating_bal_seg_value, 1, 100))) , rtrim(nvl(rtrim(int.reference5), 'Journal Import 2749104:')) , rtrim(substrb(nvl(rtrim(int.reference6), 'Journal Import Created'), 1, 80)) , rtrim(decode(upper(substrb(nvl(rtrim(int.reference7), 'N'), 1, 1)), 'Y', 'Y', 'N')) , decode(upper(substrb(int.reference7, 1, 1)), 'Y', decode(rtrim(reference8), NULL, '-1', rtrim(substrb(reference8, 1, 15))), NULL) , rtrim(upper(substrb(int.reference9, 1, 1))) , rtrim(nvl(rtrim(int.reference10), nvl(to_char(int.subledger_doc_sequence_value), 'Journal Import Created'))) , int.entered_dr , int.entered_cr , to_number(to_char(int.accounting_date, 'J')) , to_char(int.accounting_date, 'YYYY/MM/DD') , int.user_je_source_name , nvl(int.encumbrance_type_id, -1) , nvl(int.budget_version_id, -1) , NULL , decode(int.stat_amount, '0', NULL, int.stat_amount) , decode(int.actual_flag , 'E', dec ode(int.currency_code, 'STAT', '1', '0'), '0') , decode(int.actual_flag , 'A', decode(int.budget_version_id , NULL, decode(int.encumbrance_type_id, NULL, '0', '1') , decode(int.encumbrance_type_id, NULL, '2', '3')) , 'B', decode(int.encumbrance_type_id , NULL, '0', '4') , 'E', decode(int.budget_version_id , NULL, '0', '5'), '0') , int.accounted_dr , int.accounted_cr , nvl(int.group_id, -1) , nvl(int.average_journal_flag, 'N') , int.or iginating_bal_seg_value from GL_INTERFACE int, gl_code_combinations flex_cc, gl_code_combinations ccid_cc where int.set_of_books_id = 1 and int.status != 'PROCESSED' and accounting_date between to_date('2006/03/11', 'YYYY/MM/DD') and to_date('2007/04/11', 'YYYY/MM/DD') and (int.user_je_source_name, group_id) in (('Receivables', 49823)) and flex_cc.SEGMENT1(+) = int.SEGMENT1 and flex_cc.SEGMENT2(+) = int.SEGMENT2 and flex_cc.SEGMENT3(+) = int.SEGMENT3 and flex_cc.SEGMENT4(+) = int.SEGMENT4 and flex_cc.SEGMENT5(+) = int.SEGMENT5 and flex_cc.chart_of_accounts_id(+) = 101 and flex_cc.template_id(+) is NULL and ccid_cc.code_combination_id(+) = int.code_combination_id and ccid_cc.chart_of_accounts_id(+) = 101 and ccid_cc.template_id(+) is NULL order by decode(int.SEGMENT1|| int.SEGMENT2|| int.SEGMENT3|| int.SEGMENT4|| int.SEGMENT5 , '' , rpad(ccid_cc.SEGMENT3, 30) || '-' || rpad(ccid_cc.SEGMENT1, 30) || '-' || rpad(ccid_cc.SEGMENT2, 30) || '-' || rpad(ccid_cc.SEGMENT4, 30) || '-' || rpad(ccid_cc.SEGMENT5, 30) , rpad(int.SEGMENT3, 30) || '-' || rpad(int.SEGMENT1, 30) || '-' || rpad(int.SEGMENT2, 30) || '-' || rpad(int.SEGMENT4, 30) || '-' || rpad(int.SEGMENT5, 30) ) , int.entered_dr, int.accounted_dr, int.entered_cr, int.accounted_cr, int.accounting_date |
| 8q8zmrwmrccz4 |
SELECT /*+ ORDERED USE_NL(adj qplh) */ ADJ.LIST_LINE_ID CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , ADJ.PRICE_ADJUSTMENT_ID LINE_DETAIL_INDEX , ADJ.LIST_LINE_TYPE_CODE CREATED_FROM_LIST_LINE_TYPE , ADJ.LIST_HEADER_ID CREATED_FROM_LIST_HEADER_ID , ADJ.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , ADJ.RANGE_BREAK_QUANTITY PRICED_QUANTITY , LINE.P RICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , ADJ.AUTOMATIC_FLAG , ADJ.UPDATE_ALLOWED OVERRIDE_FLAG , ADJ.PRICING_GROUP_SEQUENCE , ADJ.ARITHMETIC_OPERATOR OPERAND_CALCULATION_CODE , NVL(ADJ.OPERAND_PER_PQTY, ADJ.OPERAND) OPERAND_VALUE , ADJ.ADJUSTED_AMOUNT_PER_PQTY ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , ADJ.ACCRUAL_FLAG , NVL(ADJ.UPDATED_FLAG, :B16 ) , 'N' PROCESS_CODE , 'N' PRICING_STATUS_CODE , ' ' PRICING_STATUS_TEXT , ADJ.PRICE_BREAK_TYPE_CODE , ADJ. CHARGE_TYPE_CODE , ADJ.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , ADJ.PRICING_PHASE_ID , '' CREATED_FROM_LIST_TYPE_CODE , '' LIMIT_CODE , '' LIMIT_TEXT , ADJ.LIST_LINE_NO , ADJ.MODIFIER_LEVEL_CODE , ADJ.RANGE_BREAK_QUANTITY GROUP_QUANTITY , ADJ.RANGE_BREAK_QUANTITY GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B15 IS_LDET_REC , LINE.LINE_TYPE_CODE , NULL NET_AMOUNT_FLAG , NULL CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LI NE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , NULL LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE, OE_PRICE_ADJUSTMENTS ADJ, QP_LIST_HEADERS_B QPLH WHERE :B14 = :B7 AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND ADJ.LINE_ID = LINE.LINE_ID AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND LINE.LINE_TYPE_CODE = :B4 AN D LINE.UNIT_PRICE IS NOT NULL AND (ADJ.UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR (:B5 IS NULL AND ADJ.UPDATED_FLAG IS NULL) OR ADJ.PRICING_PHASE_ID NOT IN (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT_EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING _EVENT_CODE||', ') > 0 AND (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B7 OR (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND ADJ.MODIFIER_LEVEL_CODE IN (:B4 , :B3 ) AND QPLH.LIST_HEADER_ID = ADJ.LIST_HEADER_ID AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) AND NOT EXISTS (SELECT 'x' FROM OE_PRICE_ADJ_ASSOCS A, O E_PRICE_ADJUSTMENTS B WHERE A.RLTD_PRICE_ADJ_ID = ADJ.PRICE_ADJUSTMENT_ID AND B.PRICE_ADJUSTMENT_ID = A.PRICE_ADJUSTMENT_ID AND B.LIST_LINE_TYPE_CODE = :B18 ) UNION SELECT /*+ ORDERED USE_NL(adj line qplh) */ ADJ.LIST_LINE_ID CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , ADJ.PRICE_ADJUSTMENT_ID LINE_DETAIL_INDEX , ADJ.LIST_LINE_TYPE_CODE CREATED_FROM_LIST_LINE_TYPE , ADJ.LIST_HEADER_ID CREATED_FROM_LIST_HEADER_ID , ADJ.APPL IED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , ADJ.RANGE_BREAK_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , ADJ.AUTOMATIC_FLAG , ADJ.UPDATE_ALLOWED OVERRIDE_FLAG , ADJ.PRICING_GROUP_SEQUENCE , ADJ.ARITHMETIC_OPERATOR OPERAND_CALCULATION_CODE , NVL(ADJ.OPERAND_PER_PQTY, ADJ.OPERAND) OPERAND_VALUE , ADJ.ADJUSTED_AMOUNT_PER_PQTY ADJUSTMENT_AMOUNT , LINE.UNI T_PRICE , ADJ.ACCRUAL_FLAG , NVL(ADJ.UPDATED_FLAG, :B16 ) , 'N' PROCESS_CODE , 'N' PRICING_STATUS_CODE , ' ' PRICING_STATUS_TEXT , ADJ.PRICE_BREAK_TYPE_CODE , ADJ.CHARGE_TYPE_CODE , ADJ.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , ADJ.PRICING_PHASE_ID , '' CREATED_FROM_LIST_TYPE_CODE , '' LIMIT_CODE , '' LIMIT_TEXT , ADJ.LIST_LINE_NO , ADJ.MODIFIER_LEVEL_CODE , ADJ.RANGE_BREAK_QUANTITY GROUP_QUANTITY , ADJ.RANGE_BREAK_QUANTITY GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICI NG_STATUS_CODE , :B20 IS_LDET_REC , LINE.LINE_TYPE_CODE , NULL NET_AMOUNT_FLAG , NULL CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , NULL LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE1, OE_PRICE_ADJUSTMENTS ADJ , QP_NPREQ_LINES_TMP LINE, QP_LIST_HEADERS_B QPLH WHERE :B14 = :B7 AND LINE1.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE1.LINE_ID = ADJ.HEADER_ID AND LINE1.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.LINE_TYPE_CODE = :B19 AND (LINE1.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE1.PROCESSED_CODE, '0') = :B8 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND (LINE.UNIT_PRICE IS NOT NULL OR LINE.LINE_TYPE_CODE = :B19 ) AND (ADJ .UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR (:B5 IS NULL AND ADJ.UPDATED_FLAG IS NULL) OR ADJ.PRICING_PHASE_ID NOT IN (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT_EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING_EVENT_CODE||', ') > 0 AND (NVL(QP_ PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B7 OR (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND ADJ.MODIFIER_LEVEL_CODE = :B19 AND ADJ.LINE_ID IS NULL AND QPLH.LIST_HEADER_ID = ADJ.LIST_HEADER_ID AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) UNION SELECT /*+ ORDERED USE_NL(ldet) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQ UENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LE VEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B23 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE, QP_NP REQ_LDETS_TMP LDET WHERE LINE.LINE_INDEX = LDET.LINE_INDEX AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND LINE.LINE_TYPE_CODE = :B4 AND LINE.UNIT_PRICE IS NOT NULL AND LDET.PRICING_STATUS_CODE IN (:B13 , :B12 ) AND (LDET.APPLIED_FLAG = :B7 OR (LDET.APPLIED_FLAG = :B16 AND LDET.CREATED_FROM_LIST_LINE_TYPE = :B22 AND LDET.AUTOMAT IC_FLAG = :B16 )) AND LDET.CREATED_FROM_LIST_TYPE_CODE NOT IN (:B2 , :B1 ) AND LDET.MODIFIER_LEVEL_CODE IN (:B4 , :B3 ) AND NVL(LDET.LINE_DETAIL_TYPE_CODE, 'NULL') <> :B21 UNION SELECT /*+ ORDERED USE_NL(ldet line) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQUENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LEVEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B24 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE1, QP_NPREQ_LDETS_TMP LDET , QP_NPREQ_LINES_TMP LINE WHERE LDET.LINE_INDEX = LINE1.LINE_INDEX AND LINE1.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD '||:B10 ) AND LINE1.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.PRICE_FLAG IN (:B7 , :B9 ) AND LINE1.LINE_TYPE_CODE = :B19 AND (LINE.UNIT_PRICE IS NOT NULL OR LINE.LINE_TYPE_CODE = :B19 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LDET.PRICING_STATUS_CODE IN (:B13 , :B12 ) AND (LDET.APPLIED_FLAG = :B7 OR (LDET.APPLIED_FLAG = :B16 AND LDET.CREATED_FROM_LIST_LINE_TYPE = :B22 AND LDET.AUTOMATIC_FLAG = :B16 )) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCES SED_CODE, '0') = :B8 ) AND LDET.MODIFIER_LEVEL_CODE = :B19 AND LDET.CREATED_FROM_LIST_TYPE_CODE NOT IN (:B2 , :B1 ) UNION SELECT /*+ ORDERED USE_NL(ldet qplh) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNI T_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQUENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CH ARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LEVEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B25 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER _QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE, QP_NPREQ_LDETS_TMP LDET, QP_LIST_HEADERS_B QPLH WHERE NVL(:B14 , :B16 ) <> :B7 AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) A ND LINE.LINE_TYPE_CODE = :B4 AND LINE.UNIT_PRICE IS NOT NULL AND LDET.LINE_INDEX = LINE.LINE_INDEX AND LDET.PRICING_STATUS_CODE = :B10 AND (LDET.UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR LDET.PRICING_PHASE_ID NOT IN (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT _EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING_EVENT_CODE||', ') > 0 AND (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B7 OR (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND LDET.MODIFIER_LEVEL_CODE IN (:B4 , :B3 ) AND QPLH.LIST_HEADER_ID = LDET.CREATED_FROM_LIST_HEADER_I D AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) AND NVL(LDET.LINE_DETAIL_TYPE_CODE, 'NULL') <> :B21 UNION SELECT /*+ ORDERED USE_NL(ldet line qplh) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQUENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CHARGE_SU BTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LEVEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B26 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTI TY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE1, QP_NPREQ_LDETS_TMP LDET , QP_NPREQ_LINES_TMP LINE, QP_LIST_HEADERS_B QPLH WHERE NVL(:B14 , :B16 ) <> :B7 AND LINE1.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE1.LINE_TYPE_CODE = :B19 AND (LINE1.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE1.PROCESSED_CODE, '0') = :B8 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND (LINE.UNIT_PRICE IS NOT NULL OR LINE.LINE_TYPE_CODE = :B19 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.LINE_INDEX = LDET.LINE_INDEX AND LDET.PRICING_STATUS_CODE = :B10 AND (LDET.UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR LDET.PRICING_PHASE_ID NOT I N (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT_EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING_EVENT_CODE||', ') > 0 AND (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B7 OR (NVL(QP_ PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND LDET.MODIFIER_LEVEL_CODE = :B19 AND QPLH.LIST_HEADER_ID = LDET.CREATED_FROM_LIST_HEADER_ID AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) ORDER BY PRICING_GROUP_SEQUENCE, LINE_IND, IS_LDET_REC |
| 97mt0g20bcfc3 |
begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end; |
| 9dvfguzd15kpv |
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = :B1 |
| avc1jqzz04wpr |
SELECT 'x' FROM DUAL |
| azfadzhfxbxj3 |
SELECT ROW_ID, APBA_INACTIVE_DATE, DEFAULT_TAX_EXEMPT_FLAG, BS_BATCH_SOURCE_NAME, TRX_NUMBER, RA_BILLING_NUMBER, SOA_AGREEMENT_NAME, RAB_BATCH_NAME, RAC_BILL_TO_CUSTOMER_NAME, RAC_BILL_TO_CUSTOMER_NUM, RAA_BILL_TO_CONCAT_ADDRESS, RAA_BILL_TO_ADDRESS1, RAA_BILL_TO_ADDRESS2, RAA_BILL_TO_ADDRESS3, RAA_BILL_TO_ADDRESS3_DB, RAA_BILL_TO_CITY, FT_BILL_TO_COUNTRY, RAA_BILL_TO_COUNTY, RAA_BILL_TO_POSTAL_CODE, RAA_BILL_TO_PROVINCE, RAA_BILL_TO_STATE, RACO_BILL_TO_CONTACT_NAME, BILL_TO_TAXPAYER_ID, SU_BILL _TO_LOCATION, OF_SHIP_VIA_NAME, CTT_CLASS, COMMENTS, END_DATE_COMMITMENT, START_DATE_COMMITMENT, COMPLETE_FLAG, CT_RELATED_TRX_NUMBER, APBA_BANK_ACCOUNT_NAME, APBA_BANK_ACCOUNT_NUM, APB_CUSTOMER_BANK_BRANCH_NAME, APB_CUSTOMER_BANK_NAME, CUSTOMER_REFERENCE, CUSTOMER_REFERENCE_DATE, INVOICE_CURRENCY_CODE, AL_DEFAULT_TAX_EXEMPT_FLAG, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, PS_DISPUTE_AMOUNT, PS_DISPUTE_DATE, DOC_SEQUENCE_VALUE, TERM_DUE_DATE, EXCHANGE_DATE, EXCHANGE_RATE, GDCT_USER_EXCHANGE_RATE_TYPE, AL_FOB_MEANING, FINANCE_CHARGES, GD_GL_DATE, INVOICING_RULE_ID, CT_MODEL_TRX_NUMBER, PRINTING_ORIGINAL_DATE, CT_INVOICE_FOR_CB, PURCHASE_ORDER_DATE, PURCHASE_ORDER_REVISION, SU_PAYING_CUSTOMER_LOCATION, RAC_PAYING_CUSTOMER_NAME, RAC_PAYING_CUSTOMER_NUM, ARM_RECEIPT_METHOD_NAME, PRINTING_COUNT, PRINTING_LAST_PRINTED, PRINTING_OPT ION, PURCHASE_ORDER, CT_REFERENCE, RAA_CONCAT_REMIT_TO_ADDRESS, RAA_REMIT_TO_ADDRESS1, RAA_REMIT_TO_ADDRESS2, RAA_REMIT_TO_ADDRESS3, RAA_REMIT_TO_ADDRESS3_DB, RAA_REMIT_TO_CITY, RAA_REMIT_TO_COUNTY, RAA_REMIT_TO_STATE, RAA_REMIT_TO_PROVINCE, RAA_REMIT_TO_POSTAL_CODE, FT_REMIT_TO_COUNTRY, CREDIT_METHOD_FOR_RULES, RAS_PRIMARY_SALESREP_NAME, RAS_PRIMARY_SALESREP_NUM, SHIP_DATE_ACTUAL, RAA_SHIP_TO_CONCAT_ADDRESS, RAA_SHIP_TO_ADDRESS1, RAA_SHIP_TO_ADDRESS2, RAA_SHIP_TO_ADDRESS3, RAA_SHIP_TO_ADDRESS3_ DB, RAA_SHIP_TO_CITY, RAA_SHIP_TO_COUNTY, RAA_SHIP_TO_STATE, RAA_SHIP_TO_PROVINCE, RAA_SHIP_TO_POSTAL_CODE, FT_SHIP_TO_COUNTRY, RAC_SHIP_TO_CUSTOMER_NAME, RACO_SHIP_TO_CONTACT_NAME, RAC_SHIP_TO_CUSTOMER_NUM, SU_SHIP_TO_LOCATION, SHIP_TO_TAXPAYER_ID, RAC_SOLD_TO_CUSTOMER_NAME, RAC_SOLD_TO_CUSTOMER_NUM, INTERNAL_NOTES, CREDIT_METHOD_FOR_INSTALLMENTS, STATUS_TRX, RAT_TERM_NAME, DEFAULT_USSGL_TRANSACTION_CODE, TRX_DATE, INTERFACE_HEADER_CONTEXT, INTERFACE_HEADER_ATTRIBUTE1, INTERFACE_HEADER_ATTRIBUT E2, 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, CTT_TYPE_NAME, WAYBILL_NUMBER, CUSTOMER_TRX_ID, PREVIOUS_CUSTOMER_TRX_ID, INITIAL_CUSTOMER_TRX_ID, RELATED_BATCH_ SOURCE_ID, RELATED_CUSTOMER_TRX_ID, CUST_TRX_TYPE_ID, BATCH_ID, BATCH_SOURCE_ID, REASON_CODE, TERM_ID, PRIMARY_SALESREP_ID, AGREEMENT_ID, RECEIPT_METHOD_ID, SHIP_VIA, FOB_POINT, CUSTOMER_BANK_ACCOUNT_ID, RECURRED_FROM_TRX_NUMBER, SOLD_TO_CUSTOMER_ID, SOLD_TO_SITE_USE_ID, SOLD_TO_CONTACT_ID, BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID, BILL_TO_CONTACT_ID, SHIP_TO_CUSTOMER_ID, SHIP_TO_SITE_USE_ID, SHIP_TO_CONTACT_ID, REMIT_TO_ADDRESS_ID, CREATED_FROM, PRINTING_PENDING, LAST_PRINTED_SEQUENCE_NUM, EXCH ANGE_RATE_TYPE, TERRITORY_ID, DOC_SEQUENCE_ID, PAYING_CUSTOMER_ID, PAYING_SITE_USE_ID, DH_MAX_DISPUTE_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, BS_AUTO_TRX_NUMBERING_FLAG, BS_BATCH_SOURCE_TYPE, REV_RECOG_RUN_FLAG, CTT_OPEN_RECEIVABLES_FLAG, CTT_ALLOW_FREIGHT_FLAG, CTT_POST_TO_GL_FLAG, CTT_CREATION_SIGN, CTT_ALLOW_OVERAPPLICATION_FLAG, CTT_NATURAL_APP_ONLY_FLAG, CTT_TAX_CALCULATION_FLAG, CTT_DEFAULT_STATUS, CTT_DEFAULT_TERM, CTT_DEFAULT_PRINTING_OPTION , RAA_BILL_TO_ADDRESS_ID, RAA_SHIP_TO_ADDRESS_ID, ARC_CREATION_METHOD_CODE, ARM_PAYMENT_TYPE_CODE, RAT_TERM_IN_USE_FLAG, RULES_FLAG, ACTIVITY_FLAG, PRINTED_FLAG, POSTED_FLAG, CM_AGAINST_TRX_FLAG, SELECTED_FOR_PAYMENT_FLAG, OVERRIDE_TERMS, COMMITMENTS_EXIST_FLAG, AGREEMENTS_EXIST_FLAG, ATCHMT_FLAG, GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6, GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10, GLOBAL_ATT RIBUTE11, GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18, GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY, ORIG_SYSTEM_BATCH_NAME, OLD_TRX_NUMBER, BS_COPY_DOC_NUMBER_FLAG FROM RA_CUSTOMER_TRX_PARTIAL_V WHERE NVL(:1 , 0) = NVL(of_organization_id, NVL(:2, 0)) and (CUSTOMER_TRX_ID=:3) order by CUSTOMER_TRX_ID |
| bf4kcv5v66czb |
SELECT 'NOTE', '2', b.creation_date, b.entered_by, b.jtf_note_id, t.notes, b.note_status, lkcmttype.meaning, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, sysdate, sysdate, r.resource_id FROM fnd_lookup_values_vl lkcmttype, jtf_notes_tl t, jtf_notes_b b, fnd_user u, per_people_f e, hz_parties p, jtf_rs_r esource_extns r WHERE b.jtf_note_id = t.jtf_note_id AND t.language = userenv('LANG') AND b.source_object_code = 'SR' AND b.note_status = 'E' AND b.source_object_id = :1 AND lkcmttype.lookup_type = 'JTF_NOTE_TYPE' AND b.note_type = lkcmttype.lookup_code AND b.created_by = u.user_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(e.effective_start_date, SYSDATE)) AND TRUNC(NVL(e.effective_end_date, SYSDATE)) UNION ALL SELECT 'ATTACHMENT', '3', lobs.upload_date, atth.created_by, lobs.file_id, null, null, lobs.file_name, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, SYSDATE, SYSDATE, r.resource_id FROM fnd_lobs lobs, fnd_attached_docs_form_vl atth, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extn s r WHERE atth.entity_name = 'CS_INCIDENTS' AND atth.pk1_value = :2 AND atth.created_by = u.user_id AND atth.function_name = :3 AND atth.media_id = lobs.file_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(e.effective_start_date, SYSDATE)) AND TRUNC(NVL(e.effective_end_date, SYSDATE)) UNION ALL SELECT 'TASK', '5', task.creation_date, task.created_by, task.task_id, tl.task_name, tl.description, s tl.name, tktype.name, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, task.planned_start_date, task.planned_end_date, r.resource_id FROM jtf_tasks_b task, jtf_tasks_tl tl, jtf_task_statuses_tl stl, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extns r, jtf_task_types_tl tktype WHERE task.source_object_id = :4 AND task.source_object_type_code = 'SR' AND tl.language = userenv ('LANG') AND stl.language = userenv('LANG') AND tktype.task_type_id = task.task_type_id AND tktype.language = userenv('LANG') AND task.task_status_id = stl.task_status_id AND task.created_by = u.user_id AND task.task_id = tl.task_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND trunc(sysdate) between trunc(nvl(e.effective_start_date, sysdate)) AND trunc(nvl(e.effecti ve_end_date, sysdate)) and task.publish_flag = 'Y' UNION ALL SELECT 'AUDIT', '4', audi.creation_date, audi.created_by, audi.incident_audit_id, null, null, null, AUDI.CHANGE_INCIDENT_SEVERITY_FLAG, sevt_old.name, sevt.name, AUDI.CHANGE_INCIDENT_TYPE_FLAG, typet_old.name, typet.name, AUDI.CHANGE_INCIDENT_STATUS_FLAG, stat_old.name, stat.name, AUDI.CHANGE_INCIDENT_URGENCY_FLAG, urgt_old.name, urgt.name, DECODE(AUDI.CHANGE_CUSTOMER_PRODUCT_FLAG, 'Y', AUDI.CHANGE_CUSTOMER_PRO DUCT_FLAG, AUDI.CHANGE_INVENTORY_ITEM_FLAG), DECODE(AUDI.CHANGE_CUSTOMER_PRODUCT_FLAG, 'Y', DECODE(AUDI.CHANGE_INVENTORY_ITEM_FLAG, 'Y', NVL(to_char(audi.old_customer_product_id), 'INV'||audi.old_inventory_item_id), audi.old_customer_product_id), 'INV'||audi.old_inventory_item_id), DECODE(AUDI.CHANGE_CUSTOMER_PRODUCT_FLAG, 'Y', DECODE(AUDI.CHANGE_INVENTORY_ITEM_FLA G, 'Y', NVL(to_char(audi.customer_product_id), 'INV'||audi.inventory_item_id), audi.customer_product_id), 'INV'||audi.inventory_item_id), u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, sysdate, sysdate, r.resource_id FROM cs_incidents_audit_b audi, CS_INCIDENT_SEVERITIES_VL sevt_old, CS_INCIDENT_SEVERITIES_VL sevt, cs_incident_types_VL typet_old, c s_incident_types_VL typet, cs_incident_statuses_VL stat_old, cs_incident_statuses_VL stat, cs_incident_urgencies_VL urgt_old, cs_incident_urgencies_VL urgt, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extns r WHERE audi.incident_id = :5 AND sevt_old.incident_severity_id = AUDI.old_incident_severity_id AND sevt.incident_severity_id = AUDI.incident_severity_id AND typet_old.incident_type_id = AUDI.old_incident_type_id AND typet.incident_type_id = AUDI.incident_type_id AND sta t_old.incident_status_id = AUDI.old_incident_status_id AND stat.incident_status_id = AUDI.incident_status_id AND urgt_old.incident_urgency_id(+) = AUDI.old_incident_urgency_id AND urgt.incident_urgency_id(+) = AUDI.incident_urgency_id AND audi.created_by = u.user_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND trunc(sysdate) between trunc(nvl(e.effective_start_date, sysdate)) AND trunc(nvl(e.effective_end_date, sysdate)) UNION ALL SELE CT 'SOLUTION', '6', link.creation_date, link.created_by, solu.set_id, solu.set_name, solu.set_number, solu.set_link_type_meaning, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, SYSDATE, SYSDATE, r.resource_id FROM cs_kb_linked_solutions_view solu, cs_kb_set_links link, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extns r WHERE solu.other_id = :6 AND solu.set_id = link.set_id AND solu.object_code = 'SR' AND link.created_by = u.user_id AND link.other_id = solu.other_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(e.effective_start_date, SYSDATE)) AND TRUNC(NVL(e.effective_end_date, SYSDATE)) ORDER BY 3 desc |
| bk9zgysk994av |
SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENC E , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ .LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.QUALIFIER_ATTR_VALUE = QPLATQ.VALUE_FROM AND QPQ.COMPARISON_OPERATOR_CODE = '=' AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIV E , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE U NION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_o uter_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER _PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.QUALIFIER_ATTR_VALUE = QPLATQ.VALUE_FROM AND QPQ.COMPARISON_OPERATOR_CODE = '=' AND QPQ.SEARCH_IND = 2 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_ CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DA TE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_m ini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_AC TIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = :B14 AND QPQ.QUALIFIER_DATATYPE = :B13 AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(QPQ.QUALIFIER_DATATYPE, :B13 , QPLATQ.VALUE_FRO M, NULL)) BETWEEN QPQ.QUAL_ATTR_VALUE_FROM_NUMBER AND QPQ.QUAL_ATTR_VALUE_TO_NUMBER AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIV E, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 A ND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_ou ter_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIE R_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_ CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = : B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = :B14 AND QPQ.QUALIFIER_DATATYPE = :B13 AND QPQ.SEARCH_IND = 2 AND FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(QPQ .QUALIFIER_DATATYPE, :B13 , QPLATQ.VALUE_FROM, NULL)) BETWEEN QPQ.QUAL_ATTR_VALUE_FROM_NUMBER AND QPQ.QUAL_ATTR_VALUE_TO_NUMBER AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DAT E_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_T YPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATT RIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FI RST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFI ER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.CO MPARISON_OPERATOR_CODE = :B14 AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND QPQ.QUALIFIER_DATATYPE IN (:B17 , :B16 , :B15 ) AND QPLATQ.VALUE_FROM BETWEEN QPQ.QUALIFIER_ATTR_VALUE AND QPQ.QUALIFIER_ATTR_VALUE_TO AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND QPH.ACTIVE_FLAG = :B5 AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPQ.ACTIVE_FLAG = :B5 AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_out er_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_ FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PR ICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_D ATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = :B14 AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_DATATYPE IN (:B17 , :B16 , :B15 ) AND QPLATQ.VALUE_FROM BETWEEN QPQ.QUALIFIER_ATTR_VALUE AND QPQ.QUALIFIER_ATTR_VALUE_TO AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND QPH.ACTIVE_FLAG = :B5 AND ((:B7 BETWEEN NV L(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPQ.ACTIVE_FLAG = :B5 AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH. LIST_HEADER_ID AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH .START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMI T_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = 'NOT =' AND QPQ.QUALIFIER_ATTR_VALUE <> QPLATQ.VALUE_FROM AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.ST ART_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLA TQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_out er_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_E FFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SEC OND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ .COMPARISON_OPERATOR_CODE = 'NOT =' AND QPQ.QUALIFIER_ATTR_VALUE <> QPLATQ.VALUE_FROM AND QPQ.SEARCH_IND = 2 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , : B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE |
| bkxrrs52m7d8p |
BEGIN WSH_SHIP_CONFIRM_ACTIONS.interface_ALL_wrp( :errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8); END; |
| bt0w082dfrm8v |
BEGIN WSH_SHIP_CONFIRM_ACTIONS.interface_ALL_wrp( :errbuf, :rc, :A0, :A1, :A2, :A3); END; |
| c1a0aqnhptp0c |
update GL_INTERFACE set status = :status , status_description = :description , je_batch_id = :batch_id , je_header_id = :header_id , je_line_num = :line_num , code_combination_id = decode(:ccid, '-1', code_combination_id, :ccid) , accounted_dr = :acc_dr , accounted_cr = :acc_cr , descr_flex_error_message = :descr_description , request_id = to_number(:req_id) where rowid = :row_id |
| c4y7jp0q2awbf |
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 |
| 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 |
| cgb6kp2umq52a |
select t.schema, t.name, t.flags, q.name, t.timezone from system.aq$_queue_tables t, system.aq$_queues q where t.objno = :1 and q.table_objno = t.objno and q.usage = 0 and NOT ( t.name in ('DEF$_AQCALL' , 'DEF$_AQERROR') and t.schema = 'SYSTEM') |
| cwyx16zn1hpfb |
select parameter, value from nls_session_parameters |
| fmfdkztk8vx23 |
SELECT CATEGORY, SEVERITY, FND_LOG_SEVERITY FROM FND_NEW_MESSAGES WHERE MESSAGE_NAME = :B1 AND APPLICATION_ID = :B2 AND LANGUAGE_CODE = :B3 AND ROWNUM = 1 |
| fw8fr3nddyd26 |
insert into pay_run_balances(run_balance_id, assignment_action_id, assignment_id, action_sequence, defined_balance_id, balance_value, payroll_action_id, effective_date, tax_unit_id, jurisdiction_code, jurisdiction_comp1, jurisdiction_comp2, jurisdiction_comp3, source_id, source_text, tax_group, source_number, source_text2) values (:b1, :b2:b3, :b4:b5, :b6:b7, :b8, :b9, :b10:b11, to_date(:b12, :b13), :b14:b15, :b16:b17, :b18:b19, :b20:b21, :b22:b23, :b24:b25, :b26:b27, :b28:b29, :b30:b31, :b32:b3 3) |
| ghw9svn8x4vzu |
BEGIN WSH_PICK_LIST.RELEASE_BATCH_SRS( :errbuf, :rc, :A0, :A1, :A2); END; |
| 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 ) |