SELECT DISTINCT d.contract_number , a.trx_number « Numéro de facture » , TO_CHAR( b.date_billed_from , `DD-MON-YYYY HH24-MI` ) « Bill From » , TO_CHAR( b.date_billed_to , `DD-MON-YYYY HH24-MI` ) « Bill To » , b.amount FROM oks_bill_transactions a , oks_bill_txn_lines aa , oks_bill_cont_lines b , okc_k_lines_b c , okc_k_headers_all_b d WHERE a.id = aa.btn_id AND aa.bcl_id = b.id AND b.cle_id = c.cle_id AND c.dnz_chr_id = d.id AND d.id = « ;D 2. Diese Abfrage zeigt die Details der Fakturatransaktion an. Die Daten in dieser Tabelle werden auf der Registerkarte Verlauf des Formulars Abrechnungszeitplan im Vertrag angezeigt. Für die bill_action haben die Codes folgende Bedeutungen: Reguläre Rechnung -RI, Kündigungsgutschrift – TR, Mittelwertbildung – AV, Abrechnungsrechnung – SRI, Abrechnungsgutschrift – STR. SELECT hdr.contract_number « Agreement » , hdr.contract_number_modifier « Modifier » , hdr.id , TO_CHAR( cont.creation_date , `DD-MY-YYYY HH24:MI` ) » Date of creation » , bill_action , btn_id » Billing Transaction ID » , Amount , TO_CHAR( date_billed_from , `DD-MY-YYYY` ) » Settlement date of » , TO_CHAR( date_billed_to , `DD-MY-YYYY` ) » Date of payment on » OF oks_bill_cont_lines following , line okc_k_lines_b , okc_k_headers_all_b hdr WHERE hdr.id = line.dnz_chr_id AND cont.cle_id = line.id AND hdr.id = « ORDER BY cont.creation_date; E1. Query to return all data in the RA interface table for a specific service contract. This returns the data that was inserted into the table by the main billing of the service contracts. Note that this query does not return any data if Autoinvoice is executed, because records are deleted from this table after they have been successfully processed by Autoinvoice. SELECT osh.instance_id , okh.contract_number , okh.contract_number_modifier , okl.line_number FROM oks_subscr_header_b osh , okc_k_headers_all_b okh , okc_k_lines_b okl WHERE osh.dnz_chr_id = okh.id AND osh.cle_id = okl.id AND okl.chr_id = okh.id AND okh.contract_number = » AND NVL( okh.contract_number_modifier, `-` ) = NVL( « , `-` ); F2. Suchen Sie nach den Installationsbasisinstanzen, die von einem Abonnementvertrag als Ergebnis der Abonnementerfüllung erstellt wurden. SELECT csi.instance_number FROM oks_subscr_elements ose , csi_item_instances csi WHERE ose.dnz_chr_id IN (SELECT id FROM okc_k_headers_all_b WHERE contract_number = » AND NVL( contract_number_modifier, `-` ) = NVL( « , `-` )) AND ose.order_line_id = csi.last_oe_order_line_id;f3.
ABFRAGE, um herauszufinden, welche Abonnementvertragszeile DIE Installationsbasisinstanz für eine s/ubscription-Erfüllung erstellt hat. SELECT okh.contract_number , okh.contract_number_modifier , okl.line_number FROM oks_subscr_elements ose , csi_item_instances csi , okc_k_headers_all_b okh , okc_k_lines_b okl WHERE csi.instance_number = » AND ose.order_line_id = csi.last_oe_order_line_id AND okh.id = ose.dnz_chr_id AND okl.chr_id = okh.id AND okl.id = ose.dnz_cle_id; Note: In OKC_K_LINES_B, the chr_id field is populated only with the contract header ID for contract items. For contract sublines, this value is NULL. Dnz_chr_id is populated with the contract header ID for rows and sublines. B1. This SQL takes data from views rather than actual contract tables and is useful for validating the data, but not ideal for verifying that the underlying tables contain correct data. SÉLECTIONNEZ OAL. LINE_NUMBER DISTINCT , oll.lse_name , oal.sts_code « Statut » , oal.trn_code , oal.lse_id , old.service_name , oal.currency_code « Währung| Code » , TO_CHAR( oal.start_date , `DD-MON-YYYY` ) « Date de début » , TO_CHAR( oal.end_date , `DD-MON-YYYY` ) « Date de fin » , qpl.name « Nom de la liste de prix » , cust_acct_id , bill_to_site_use_id , inv_rule_id , ship_to_site_use_id , ship_to_site_use_id , acct_rule_id , usage_period , usage_type , uom_quantified , billing_schedule_type , invoice_text FROM oks_auth_lines_v oal , okc_launch_lgrid_v oll , qp_pricelists_lov_v qpl , oks_line_details_v alte OÙ oal.id = oll.id ET cle_id EST NULL ET qpl.price_list_id = oal.price_list_id ET old.contract_id = oll.chr_id ET oll.chr_id = » D4.
Diese Abfrage gibt Informationen über den Abrechnungszeitplan des Vertrags für einen Vertrag zurück und kann verwendet werden, um Beträge zu untersuchen, die in einem Abrechnungszeitraum voraussichtlich in Rechnung gestellt werden. SELECT TO_CHAR( bcl.id ) , TO_CHAR( bsl.id ) , TO_CHAR( lvl.id ) , lvl.date_start , bsl.date_billed_from , lvl.date_end , bsl.date_billed_to , bcl.date_next_invoice , lvl.date_transaction , lvl.date_to_interface , lvl.date_completed , TO_CHAR( rul_id ) , TO_CHAR ( lvl.parent_cle_id ) , bsl.amount FROM oks_bill_sub_lines bsl , oks_bill_cont_lines bcl , oks_level_elements lvl , okc_k_lines_b kl , okc_k_headers_all_b kh WObei kh.contract_number = » UND kl.dnz_chr_id = kh.id UND lvl.dnz_chr_id = kh.id UND bcl.cle_id = kl.id UND bcl.id = bsl.bcl_id UND lvl.cle_id = bsl.cle_id; UND lse_id IN (8, 7, 9, 10, 11, 35); C2. Abfragen von Vertragsunterpositionen nur für eine bestimmte Vertragsposition. Ersetzen Sie durch die Zeilennummer der gewünschten Vertragszeile (z.B. 1, 2. 3), die entweder aus dem Vertragsformular oder aus der Abfrage B2 stammt. (Note that this query appears to return duplicate rows because the query on okc_k_headers_all_b returns multiple contracts if the contract has been renewed.) Select * in OKC_K_HEADERS_ALL_B, where contract_number such as :p_contract_number; For 11.5.10 queries, see « Querying Service Contract Tables for Header, Row, Subline, and Billing Information in 11i. » This note contains a variety of queries that can be used to find the data required for a particular contract. Represents the text of the specified SQL statements that should be replaced by the actual values specified in square brackets. For example, if you want to run a query on a contract with the number Test1, change it to Test1 when you see below in SQL.
The sections of this note are as follows: A. Contract header data B. Vertragspositionsdaten C. Vertragssubliniendaten D. Vertragsfakturierungsdaten E. Forderungsschnittstellendaten F. Abonnementverträge SELECT kl.line_number , ks.name , i.segment1 FROM okc_k_headers_all_b kh , okc_k_lines_b kl , okc_k_items ki , okc_line_styles_v ks , csi_item_instances c , mtl_system_items_b i WHERE kh.contract_number = » AND kh.contract_number_modifier IS NULL –can be filling AND kh.id = kl.dnz_chr_id AND kh.id = ki.dnz_chr_id AND kl.id = ki.cle_id AND kl.lse_id = ks.id AND ki.jtot_object1_ code IN (`OKX_CUSTPROD`) ET c.last_vld_organization_id = i.organization_id UND TO_NUMBER( ki.object1_id1 ) = c.instance_id UND c.inventory_item_id = i.inventory_item_id; C3. Diese Abfrage gibt das Inventarelement für eine bestimmte Vertragsunterzeile zurück, wobei level = Product (d. h. die Subline für eine bestimmte Installationsbasisinstanz gilt). BESTELLUNG NACH TO_NUMBER( line_number ); B2.
Daten, die direkt aus der Vertragstabelle stammen. (Note that this query appears to return duplicate rows because the query on okc_k_headers_all_b returns multiple contracts if the contract has been renewed.) SELECT * FROM okc_k_lines_b WHERE chr_id IN (SELECT id FROM okc_k_headers_all_b How to query service contract database tables (and tables of other schemas) to find underlying data about contract headers, rows, sublines, and contract billing in R12. Note: When you add a subline to a contract OKC_K_LINES_B filled with data, some of the data created in it for each subline is internal data. Use the LSE_ID to restrict the data returned during the query. C1. Query all sublines of a contract with a level type that can be displayed when creating the contract (that is, limited to rows with Product Level, Site, Item, System, Customer, or Site). This query returns data about the contract line IDs that correspond to each invoice, as well as the invoice details. SELECT bcl.id , bcl.cle_id , bcl.btn_id , bcl.bill_action , okl.id « Line id » , okh.id « Contract id » , btn.trx_number « Invoice » , bcl.date_billed_from , bcl.date_billed_to FROM oks_bill_cont_lines bcl , okc_k_lines_b okl , okc_k_headers_all_b okh , oks_bill_transactions btn , oks_bill_txn_lines btl WHERE okh.contract_number = » AND okh.id = okl.dnz_chr_id AND okl.cle_id IS NULL AND okl.id = bcl.cle_id AND btn.id = bcl.btn_id AND btl.btn_id = btn.id F1. To find the base installation instance that was created for the subscription location.
(Note that when you enter a subscription line, the application automatically creates an Oracle Install Base item instance. This is what retrieves this query). Note: The last line of the query can be commented out if your contract does not have a modifier. .