ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A7086A92F0E7B16EE053BB6B63589F6B AR Customer Name (Organizations Only) Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hp.party_type='ORGANIZATION' order by hp.party_name US 8E2FF36EDEC579D2E0530100007F1FF2 GL Chart of Accounts (inv org restricted) Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and fifsv.id_flex_num in (select oav.chart_of_accounts_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by fifsv.id_flex_structure_name AR مخطط الحسابات المتعلق بمؤسسات المخزون المعينة لمسؤولية تسجيل الدخول الحالية D Kontenplan für Inventarorganisationen, die der aktuellen Anmeldeverantwortung zugeordnet sind E Plan de cuentas relacionado con las organizaciones de inventario asignadas a la responsabilidad de inicio de sesión actual F Plan comptable relatif aux organismes d'inventaire affectés à la responsabilité de la connexion actuelle I Piano dei conti relativo alle organizzazioni di inventario assegnate alla responsabilità di login corrente JA 現在のログイン責任者に割り当てられた在庫組織に関連する勘定科目一覧表 KO 현재 로그인 권한에 지정된 재고 조직과 관련된 계정과 목표 PTB Plano de contas relacionado às organizações de inventário atribuídas à responsabilidade de login atual RU План счетов, относящихся к инвентаризационным организациям, на которые возложена ответственность за текущий вход в систему S Kontokarta relaterade till lagerorganisationer som tilldelats det nuvarande inloggningsansvaret TR Mevcut oturum açma sorumluluğuna atanan envanter organizasyonlarıyla ilgili hesap planı US Chart of accounts related to inventory organizations assigned to the current login responsibility ZHS 与分配给当前登录责任的清单组织有关的账户表 8E2FF36EDF3A79D2E0530100007F1FF2 GL Ledger (inv org related) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name AR جميع دفاتر الأستاذ المتعلقة بجميع منظمات المخزون النشطة D Alle Ledger, die sich auf alle aktiven Inventarorganisationen beziehen E Todos los libros de contabilidad relacionados con todas las organizaciones de inventario activas F Tous les grands livres relatifs à tous les organismes d'inventaire actifs I Tutti i libri contabili relativi a tutte le organizzazioni di inventario attive JA すべてのアクティブな在庫組織に関連するすべての台帳 KO 모든 활성 재고 조직과 관련된 모든 원장 PTB Todos os livros contábeis relacionados a todas as organizações de inventário ativas RU Все бухгалтерские книги, относящиеся ко всем организациям, занимающимся активным инвентаризацией. S Alla huvudböcker relaterade till alla aktiva lagerorganisationer TR Tüm aktif envanter organizasyonlarıyla ilgili tüm defterler US All ledgers related to all active inventory organizations ZHS 与所有现行清单组织有关的所有分类账 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US EC5CECA520510FC8E0530100007F5EB3 INV Item Number (costing enabled only items) Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US 9B9627743E84607DE053BB6B635805FB INV Operating Unit Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 AR جميع وحدات التشغيل المرتبطة بمؤسسة مخزون غير رئيسية نشطة D Alle Betriebseinheiten, die mit einer aktiven Nicht-Master-Inventarisierungsorganisation verbunden sind E Todas las unidades operativas relacionadas con una organización de inventario no maestra activa F Toutes les unités opérationnelles liées à un organisme d'inventaire non maître actif I Tutte le unità operative legate a un'organizzazione di inventario non master attiva JA アクティブな非マスターインベントリ組織に関連するすべてのオペレーティング・ユニット KO 활성 비 마스터 재고 조직과 관련된 모든 운영 단위 PTB Todas as unidades operacionais relacionadas a uma organização ativa de inventário não-mestre RU Все операционные единицы, относящиеся к действующей организации, не являющейся ведущим поставщиком инвентарных запасов. S Alla operativa enheter relaterade till en aktiv organisation som inte är huvudlager TR Etkin bir ana envanter organizasyonu ile ilgili tüm işletim birimleri US All operating units related to an active non master inventory organization ZHS 与在用的非总清单组织有关的所有业务单位： 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 A66E65A81E4F42D7E053BB6B63582F56 select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.7 -- cms_sum.sold_to_customer_name Sold_To_Customer, -- Revision for version 1.10 cms_sum.customer_class_code Customer_Class_Code, hz.party_name Customer, hca.account_number Customer_Number, -- Revision for version 1.8 -- Not useful at client site -- rsa.name Sales_Rep, cms_sum.order_number Order_Number, cms_sum.line_number Order_Line, -- Revision for version 1.10 -- sot.name Order_Type, ottt.name Order_Type, cms_sum.gl_date Transaction_Date, &segment_columns msiv.concatenated_segments Item, msiv.description Item_Description, -- Revision for version 1.10 fcl.meaning Item_Type, -- Revision for version 1.9 &category_columns muomv.uom_code UOM_Code, round(cms_sum.invoiced_amount / decode(cms_sum.invoice_line_quantity, 0, 1, cms_sum.invoice_line_quantity),5) Unit_Price, round(cms_sum.cogs_amount / decode(cms_sum.ship_quantity,0,1, cms_sum.ship_quantity),5) Unit_Cost, cms_sum.invoice_line_quantity Invoice_Quantity, cms_sum.ship_quantity Ship_Quantity, cms_sum.invoiced_amount Sales_Amount, cms_sum.cogs_amount COGS_Amount, cms_sum.invoiced_amount - cms_sum.cogs_amount Margin, round((cms_sum.invoiced_amount - cms_sum.cogs_amount) / decode(cms_sum.invoiced_amount, 0, 1, cms_sum.invoiced_amount) * 100,1) Percent from -- Revision for version 1.10 mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, fnd_common_lookups fcl, -- End for revision for version 1.10 gl_code_combinations gcc_cogs, gl_code_combinations gcc_sales, mtl_parameters mp, so_order_types_all sot, -- Revision for version 1.10 oe_transaction_types_tl ottt, -- Revision for version 1.7 -- ra_customers rc, hz_cust_accounts_all hca, hz_parties hz, -- End revision for version 1.7 -- Revision for version 1.8 -- Causing cross-joining -- ra_salesreps_all rsa, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- ================================================= -- Condense the Sales and COGS entries into one row -- ================================================= (select cms.org_id, cms.customer_class_code, cms.parent_organization_id, cms.parent_inventory_item_id, sum(cms.sales_account) sales_account, sum(cms.cogs_account) cogs_account, cms.customer_id, cms.sold_to_customer_name, cms.order_number, cms.line_number, cms.order_type_id, cms.gl_date, sum(cms.invoice_line_quantity) invoice_line_quantity, sum(cms.ship_quantity) ship_quantity, sum(cms.invoiced_amount) invoiced_amount, sum(cms.cogs_amount) cogs_amount from (select cms.org_id, cms.customer_class_code, cms.parent_organization_id, cms.parent_inventory_item_id, 0 sales_account, -- Revision for verison 1.11 min(cms.cogs_account) cogs_account, cms.customer_id, cms.sold_to_customer_name, cms.order_number, cms.line_number, cms.order_type_id, -- COGS entry has a timestamp trunc(cms.gl_date) gl_date, sum(0) invoice_line_quantity, sum(nvl(cms.ship_quantity,0)) ship_quantity, sum(0) invoiced_amount, sum(nvl(cms.cogs_amount,0)) cogs_amount from cst_margin_summary cms where cms.source = 'COGS' and 4=4 -- p_trx_date_from, p_trx_date_to group by cms.org_id, cms.customer_class_code, cms.parent_organization_id, cms.parent_inventory_item_id, -- Revision for verison 1.11 -- cms.cogs_account, 0, -- sales_account cms.customer_id, cms.sold_to_customer_name, cms.order_number, cms.line_number, cms.order_type_id, -- COGS entry has a timestamp trunc(cms.gl_date) union all select cms.org_id, cms.customer_class_code, cms.parent_organization_id, cms.parent_inventory_item_id, cms.sales_account, 0 cogs_account, cms.customer_id, cms.sold_to_customer_name, cms.order_number, cms.line_number, cms.order_type_id, cms.gl_date, sum(nvl(cms.invoice_line_quantity,0)) invoice_line_quantity, sum(0) ship_quantity, sum(nvl(cms.invoiced_amount,0)) invoiced_amount, sum(0) cogs_amount from cst_margin_summary cms where cms.source = 'INVOICE' and 4=4 -- p_trx_date_from, p_trx_date_to group by cms.org_id, cms.customer_class_code, cms.parent_organization_id, cms.parent_inventory_item_id, cms.sales_account, 0, -- cogs_account, cms.customer_id, cms.sold_to_customer_name, cms.order_number, cms.line_number, cms.order_type_id, cms.gl_date ) cms group by cms.org_id, cms.customer_class_code, cms.parent_organization_id, cms.parent_inventory_item_id, cms.customer_id, cms.sold_to_customer_name, cms.order_number, cms.line_number, cms.order_type_id, cms.gl_date ) cms_sum -- Revision for version 1.10 where msiv.organization_id = mp.organization_id and msiv.organization_id = cms_sum.parent_organization_id and msiv.inventory_item_id = cms_sum.parent_inventory_item_id and muomv.uom_code = msiv.primary_uom_code and gcc_cogs.code_combination_id (+) = cms_sum.cogs_account and gcc_sales.code_combination_id (+) = cms_sum.sales_account and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' and ottt.transaction_type_id = sot.order_type_id and ottt.language = userenv('lang') -- End for revision for version 1.10 and sot.order_type_id = cms_sum.order_type_id -- Performance improvement, comment this out -- and sot.org_id = cms.org_id -- Revision for version 1.7 -- and rc.customer_id = cms_sum.customer_id and hca.cust_account_id = cms_sum.customer_id and hz.party_id = hca.party_id -- Revision for version 1.8 -- Causing cross-joining -- and rsa.salesrep_id = cms.primary_salesrep_id -- =================================================================== -- using the base tables to avoid using -- org_organization_definitions and hr_operating_units -- =================================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and cms_sum.org_id = haou2.organization_id and haou2.organization_id = hoi.org_information3 -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 1=1 -- p_customer_name, p_item_number, p_org_code, p_operating_unit, p_ledger order by nvl(gl.short_name, gl.name), -- Ledger haou2.name, -- Operating_Unit mp.organization_code, -- Org_Code hz.party_name, -- Customer cms_sum.order_number, -- Order_Number cms_sum.line_number -- Order_Line Y D CAC Margin Analysis Account Summary Bericht für die Marge aus den Kundenrechnungen und Sendungen, einschließlich der Verkaufs- und COGS-Konten, basierend auf der Standard-Oracle-Margen-Tabelle, cst_margin_summary. Sie müssen zuerst die Anforderung "Margenanalyse-Ladevorgang" ausführen, um diese Tabelle aufzufüllen. Beachten Sie, dass dieser Bericht nur das erste COGS-Konto anzeigt, wenn Sie die Nebenbuchhaltung angepasst oder benutzerdefinierte Programme verwendet haben, um COGS nach Kostenarten aufzuzeichnen, da es nur eine Berichtszeile pro Kundenauftragszeile gibt. /* +=============================================================================+ -- | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | -- | Alle Rechte vorbehalten. | -- | | Die Erlaubnis zur Verwendung dieses Codes wird erteilt, sofern der ursprüngliche Autor | -- | | anerkannt wird. Diese | -- | Erlaubnis beinhaltet keine ausdrücklichen oder sonstigen Garantien. | | | Erlaubnis. | -- +=============================================================================+ -- | -- | Originalautor: Douglas Volz (doug@volzconsulting.com) -- | -- | Parameter: -- | p_trx_date_from -- Beginn des Transaktionsdatums für die Kundenlieferungen -- | p_trx_date_to -- Endes Transaktionsdatum für die Kundensendungen -- | p_category_set1 -- Der erste zu berichtende Positionstyp, typischerweise der -- | Kosten- oder Produktlinien-Kategoriesatz -- | p_category_set2 -- Die zweite zu meldende Artikelkategorie, typischerweise die -- | Bestandskategoriesatz -- | p_customer_name -- Geben Sie den spezifischen Kundennamen ein, über den Sie berichten möchten (optional) -- | p_item_number -- Geben Sie die spezifische Artikelnummer ein, die Sie melden möchten (optional) -- | p_org_code -- Spezifische Bestandsorganisation, die Sie melden möchten (optional) -- | p_operating_unit -- Betriebseinheit, die Sie melden möchten, für alle leer lassen -- | operating units (optional) -- | p_ledger -- Hauptbuch, über das Sie berichten möchten, für alle leer lassen -- | ledger (optional) -- | -- | Beschreibung: -- | Bericht für die Marge aus den Kundenrechnungen und Sendungen, basierend | | auf der Standard-Oracle-Margen-Tabelle, cst_margin_summary. Sie müssen zuerst -- Sie müssen zuerst die Anforderung "Margenanalyse erstellen" ausführen, um diese Tabelle aufzufüllen. -- | -- | Version Geändert am Geändert von Beschreibung -- | ======= =========== ============== ========================================= -- | 1.0 18 APR 2006 Douglas Volz Erstkodierung -- | 1.1 15 MAI 2006 Douglas Volz Arbeitsversion -- | 1.2 17 MAI 2006 Douglas Volz Bestellzeile und Datumsangaben hinzugefügt -- | 1.3 14. Dez. 2012 Douglas Volz Modifiziert für Garlock, Kategoriensatz geändert -- | 1.4 19. Dez 2012 Douglas Volz Fehlerkorrektur für Kategoriesatzname -- | 1.5 29 Jan 2013 Douglas Volz Datumsparameter so korrigiert, dass sie dasselbe Format haben -- | | wie andere Berichte; -- | 1.6 25 Feb 2013 Douglas Volz Apps.mtl_default_category_sets mdcs-Tabelle hinzugefügt -- | um das Skript generischer zu machen -- | 1.7 27 Feb 2017 Douglas Volz Modifiziert für Artikelkategorie und Kunden -- | Informationen. -- | 1.8 28 Feb 2017 Douglas Volz Entfernte Vertriebsmitarbeiter-Informationen, -- | verursachte Querverknüpfungen. -- | 1.9 22. Mai 2017 Douglas Volz Inventar-Artikelkategorie hinzugefügt. -- | 1.10 23. Mai 2020 Douglas Volz Mehrsprachige Tabelle für UOM-Code, Artikelstamm verwenden -- | OE-Transaktionsarten und HR-Organisationsnamen. -- | 1.11 06 Nov 2020 Douglas Volz Fix für das Vorhandensein von benutzerdefinierten, mehreren COGS-Konten nach -- | Kostenart. Jetzt nur noch ein COGS-Konto.+=============================================================================+*/ F CAC Margin Analysis Account Summary Rapport sur la marge des factures clients et des expéditions, y compris les comptes des ventes et du COGS, basé sur le tableau standard Oracle Margin, cst_margin_summary. Vous devez d'abord exécuter la demande de chargement de l'analyse de la marge, pour remplir ce tableau. Notez que si vous avez personnalisé la comptabilité auxiliaire ou utilisé des programmes personnalisés pour enregistrer le COGS par nature comptable, ce rapport n'affiche que le premier compte COGS, car il n'y a qu'une ligne déclarée par ligne de commande client. /* +=============================================================================+ | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | | Tous droits réservés. | -- | L'autorisation d'utiliser ce code est accordée à condition que l'auteur original soit | -- | reconnu. Aucune garantie, expresse ou autre, n'est incluse dans ce | -- | permission. | -- +=============================================================================+ -- | -- | Auteur original : Douglas Volz (doug@volzconsulting.com) -- | -- | Paramètres : -- | p_trx_date_from -- Date de début de la transaction pour les envois aux clients | p_trx_date_to -- Date de fin de la transaction pour les envois des clients | p_category_set1 -- La première catégorie d'articles à signaler, généralement le | Ensemble de catégories de coûts ou de lignes de produits | p_category_set2 -- La deuxième catégorie d'articles à signaler, généralement le | Ensemble de catégories d'inventaire | p_customer_name -- Entrez le nom du client que vous souhaitez signaler (facultatif) -- | p_item_number -- Entrez le numéro de l'article spécifique que vous souhaitez signaler (facultatif) | p_org_code -- Organisation spécifique de l'inventaire que vous souhaitez signaler (facultatif) | p_operating_unit -- Unité opérationnelle que vous souhaitez signaler, laissez vide pour tous | unités d'exploitation (facultatif) -- | p_ledger -- Grand livre général que vous souhaitez déclarer, laissez vide pour tous -- | grands livres (facultatif) -- | -- | Description : -- Rapport sur la marge des factures clients et des expéditions, basé sur -- | sur la table standard Oracle Margin, cst_margin_summary. Vous devez d'abord | pour exécuter la demande de construction d'analyse de marge, pour remplir ce tableau. -- | -- | Version modifiée le Modifié par la description -- | ======= =========== ============== ========================================= | 1.0 18 APR 2006 Codage initial de Douglas Volz | 1.1 15 MAI 2006 Douglas Volz Version de travail | 1.2 17 MAI 2006 Douglas Volz Ajout d'informations sur la ligne de commande et la date | 1.3 14 déc 2012 Douglas Volz Modifié pour Garlock, a changé de catégorie | 1.4 19 déc 2012 Douglas Volz Correction d'un bug pour le nom de la catégorie | 1.5 29 Jan 2013 Douglas Volz Paramètres de date fixe pour avoir le même format -- | comme d'autres rapports ; -- | 1.6 25 fév 2013 Douglas Volz a ajouté le tableau apps.mtl_default_category_sets mdcs | pour rendre le scénario plus générique | 1.7 27 fév 2017 Douglas Volz Modifié pour la catégorie d'article et le client -- | information. | 1.8 28 fév 2017 Douglas Volz a supprimé les informations sur les représentants commerciaux, -- a causé des jonctions croisées. -- | 1.9 22 mai 2017 Douglas Volz ajoute une catégorie d'articles à l'inventaire | 1.10 23 mai 2020 Douglas Volz Utiliser un tableau multilingue pour le code UOM, item master -- | Types de transactions OE et noms des organisations RH. | 1.11 06 Nov 2020 Douglas Volz Fixe pour avoir des comptes COGS multiples personnalisés par -- | élément de coût. N'obtenez plus qu'un seul compte COGS. +=============================================================================+*/ I CAC Margin Analysis Account Summary Report per il margine dalle fatture dei clienti e dalle spedizioni, inclusi i conti vendite e COGS, basati sulla tabella standard Oracle Margin, cst_margin_summary. È necessario prima eseguire la richiesta Margin Analysis Load Run, per popolare questa tabella. Nota che se hai personalizzato la contabilità Subledger o hai usato programmi personalizzati per registrare il COGS per elemento di costo, questo rapporto mostra solo il primo conto COGS, dato che c'è solo una riga riportata per linea d'ordine di vendita. /* +=============================================================================+ -- | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | -- | Tutti i diritti riservati. | -- | Il permesso di usare questo codice è concesso a condizione che l'autore originale sia | -- | | riconosciuto. Nessuna garanzia, espressa o di altro tipo, è inclusa in questa | -- -- | permesso. | -- +=============================================================================+ -- | -- | Autore originale: Douglas Volz (doug@volzconsulting.com) -- | -- | Parametri: -- | p_trx_date_from -- Data di inizio della transazione per le spedizioni del cliente -- | p_trx_date_to -- Data di fine della transazione per le spedizioni del cliente -- | p_category_set1 -- La prima categoria di articoli da riportare, tipicamente la -- | Set di categorie di costo o di linea di prodotto -- | p_category_set2 -- La seconda categoria di articoli da segnalare, tipicamente la -- | Set di categorie di inventario -- | p_customer_name -- Inserisci il nome specifico del cliente che vuoi segnalare (opzionale) -- | p_item_number -- Inserisci lo specifico numero di articolo che vuoi segnalare (opzionale) -- | p_org_code -- Specifica organizzazione dell'inventario che vuoi segnalare (opzionale) -- | p_operating_unit -- Unità operativa che volete segnalare, lasciate vuoto per tutte -- | unità operative (opzionale) -- | p_ledger -- Contabilità generale che vuoi segnalare, lascia in bianco per tutti -- | ledger (opzionale) -- | -- | Descrizione: -- | Report per il margine dalle fatture dei clienti e dalle spedizioni, basato -- | sulla tabella standard Oracle Margin, cst_margin_summary. È necessario prima -- | | eseguire la richiesta Margin Analysis Build, per popolare questa tabella. -- | -- | Versione Modificata il Modificato da Descrizione -- | ======= =========== ============== ========================================= -- | 1.0 18 APR 2006 Douglas Volz Codifica iniziale -- | 1.1 15 MAGGIO 2006 Douglas Volz Versione di lavoro -- | 1.2 17 MAGGIO 2006 Douglas Volz Aggiunta linea d'ordine e informazioni sulla data -- | 1.3 14 dic 2012 Douglas Volz Modificato per Garlock, cambiato set di categorie -- | 1.4 19 dic 2012 Douglas Volz Bug fix per il nome del set di categorie -- | 1.5 29 Jan 2013 Douglas Volz Corretti i parametri della data per avere lo stesso formato -- | come gli altri rapporti; -- | 1.6 25 feb 2013 Douglas Volz Aggiunta tabella mdcs apps.mtl_default_category_sets -- | per rendere lo script più generico -- | 1.7 27 Feb 2017 Douglas Volz Modificato per la categoria degli articoli e il cliente -- | informazioni. -- | 1.8 28 Feb 2017 Douglas Volz Rimosse le informazioni sui rappresentanti di vendita, -- | | stava causando dei cross-joining. -- | 1.9 22 May 2017 Douglas Volz Aggiunta categoria articolo inventario. -- | 1.10 23 maggio 2020 Douglas Volz Utilizzata tabella multilingue per codice UOM, anagrafica articoli -- | OE tipi di transazione e nomi di organizzazioni hr. -- | 1.11 06 Nov 2020 Douglas Volz Fix per avere conti COGS personalizzati e multipli per -- | elemento di costo. Ora si ottiene solo un conto COGS.+=============================================================================+*/ KO CAC Margin Analysis Account Summary 표준 Oracle Margin 테이블 인 cst_margin_summary를 기반으로 판매 및 COGS 계정을 포함한 고객 송장 및 배송의 마진을보고합니다. 이 테이블을 채우려면 먼저 마진 분석로드 실행 요청을 실행해야합니다. 보조 원장 회계를 사용자 정의했거나 사용자 정의 프로그램을 사용하여 원가 요소별로 COGS를 기록한 경우 판매 주문 라인 당보고 된 행이 하나만 있으므로이 보고서는 첫 번째 COGS 계정 만 표시합니다. / * + ============================================= ============================= + -| 저작권 2006-2020 Douglas Volz Consulting, Inc. | -| 판권 소유. | -| 이 코드를 사용할 수있는 권한은 원래 작성자가 | -| 인정. 여기에는 명시 적 또는 기타 어떠한 보증도 포함되지 않습니다. -| 허가. | -+ ============================================= ============================= + -| -| 원저자 : Douglas Volz (doug@volzconsulting.com) -| -| 매개 변수 : -| p_trx_date_from-고객 배송에 대한 거래 시작일 -| p_trx_date_to-고객 배송에 대한 거래 종료 날짜 -| p_category_set1-보고 할 첫 번째 항목 범주 세트, 일반적으로 -| 비용 또는 제품 라인 범주 세트 -| p_category_set2-보고 할 두 번째 항목 범주로, 일반적으로 -| 재고 범주 세트 -| p_customer_name-보고 할 특정 고객 이름을 입력합니다 (선택 사항). -| p_item_number-보고 할 특정 품목 번호를 입력합니다 (선택 사항). -| p_org_code-보고 할 특정 재고 조직 (선택 사항) -| p_operating_unit-보고 할 운영 단위, 모두 비워 둡니다. -| 운영 단위 (선택 사항) -| p_ledger-보고하려는 총계정 원장, 모두 비워 둡니다. -| 원장 (선택 사항) -| -| 기술: -| 고객 송장 및 배송의 마진을 기준으로보고 -| 표준 Oracle Margin 테이블, cst_margin_summary. 먼저 필요 -| 마진 분석 빌드 요청을 실행하고이 테이블을 채 웁니다. -| -| 수정 된 버전 설명에 의해 수정 됨 -| ======= =========== =============================== ======================= -| 1.0 18 APR 2006 Douglas Volz 초기 코딩 -| 1.1 2006 년 5 월 15 일 Douglas Volz Working 버전 -| 1.2 2006 년 5 월 17 일 Douglas Volz 추가 된 주문 라인 및 날짜 정보 -| 1.3 2012 년 12 월 14 일 Douglas Volz Garlock 용으로 수정, 카테고리 세트 변경 -| 1.4 2012 년 12 월 19 일 Douglas Volz 카테고리 세트 이름 버그 수정 -| 1.5 2013 년 1 월 29 일 Douglas Volz 동일한 형식을 갖는 고정 날짜 매개 변수 -| 다른 보고서로; -| 1.6 2013 년 2 월 25 일 Douglas Volz는 apps.mtl_default_category_sets mdcs 테이블을 추가했습니다. -| 스크립트를 더 일반적으로 만들기 위해 -| 1.7 2017 년 2 월 27 일 Douglas Volz가 품목 범주 및 고객에 대해 수정 됨 -| 정보. -| 1.8 2017 년 2 월 28 일 Douglas Volz 영업 담당자 정보 삭제, -| 교차 결합을 유발했습니다. -| 1.9 2017 년 5 월 22 일 Douglas Volz 재고 항목 카테고리 추가 -| 1.10 2020 년 5 월 23 일 Douglas Volz UOM 코드, 항목 마스터에 다국어 테이블 사용 -| OE 트랜잭션 유형 및 HR 조직 이름. -| 1.11 2020 년 11 월 6 일 Douglas Volz는 -| 비용 요소. 이제 하나의 COGS 계정 만 가져 오십시오. ================================= + * / PTB CAC Margin Analysis Account Summary Relatório da margem das faturas e remessas dos clientes, incluindo as contas de Vendas e COGS, com base na tabela padrão de Margens Oracle, cst_margin_summary. Para preencher esta tabela, primeiro é necessário executar a solicitação de Análise de Margem de Carga, para preencher esta tabela. Observe que se você tiver uma Contabilidade Subledger personalizada ou tiver usado programas personalizados para registrar o CPV por classe de custo, este relatório mostra apenas a primeira conta CPV, já que há apenas uma linha reportada por linha de ordem de venda. /* +=============================================================================+ | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | | Todos os direitos reservados. | | A permissão para usar este código é concedida desde que o autor original seja | | reconhecido. Nenhuma garantia, expressa ou não, está incluída nisto |. | permissão. | -- +=============================================================================+ -- | Autor original: Douglas Volz (doug@volzconsulting.com) | Parâmetros: | p_trx_date_from -- Data de início da transação para as remessas do cliente | p_trx_date_to -- Data de término da transação para as remessas do cliente | p_category_set1 -- A primeira categoria de item definida para relatar, tipicamente o | Conjunto de categorias de custo ou linha de produtos | p_category_set2 -- A segunda categoria de item definida para relatar, tipicamente o | Conjunto de Categorias de Estoque | p_nome_do_cliente -- Digite o nome do cliente específico que você deseja informar (opcional) | p_item_number -- Digite o número do item específico que você deseja relatar (opcional) | p_org_code -- Organização de inventário específica que você deseja relatar (opcional) | p_operating_unit -- Unidade operacional que você deseja relatar, deixe em branco para todos | unidades operacionais (opcional) | p_ledger -- Razão geral que você deseja relatar, deixe em branco para todos | ledgers (opcional) -- | Descrição: | Relatório para a margem das faturas e remessas dos clientes, com base | na tabela de margem padrão do Oracle, cst_margin_summary. Você precisa primeiro | para executar o pedido de Margin Analysis Build, para povoar esta tabela. -- | | Versão Modificada em Modificada por Descrição -- | ======= =========== ============== ========================================= | 1.0 18 ABR 2006 Douglas Volz Codificação Inicial | 1.1 15 MAIO 2006 Douglas Volz Versão funcional | 1.2 17 MAIO 2006 Douglas Volz Acrescentou informações sobre linha de pedido e data | 1.3 14 Dez 2012 Douglas Volz Modificado para Garlock, alterado conjunto de categorias | 1.4 19 Dez 2012 Douglas Volz Bug fixou o nome do conjunto de categorias | 1.5 29 jan 2013 Douglas Volz Parâmetros de data fixos para ter o mesmo formato | como outros relatórios; | 1.6 25 Fev 2013 Douglas Volz Added apps.mtl_default_category_sets mdcs tabela | para tornar o roteiro mais genérico | 1.7 27 Fev 2017 Douglas Volz Modificado para Categoria de Item e Cliente | informações. | 1.8 28 Fev 2017 Douglas Volz Removido informações do representante de vendas, | estava causando a união cruzada. | 1,9 22 de maio de 2017 Douglas Volz Adição de categoria de item de inventário | 1.10 23 de maio de 2020 Douglas Volz Usar tabela multilíngüe para o Código UOM, item mestre | Tipos de transações de equipamento original e nomes de organizações de equipamento original. | 1.11 06 Nov 2020 Douglas Volz Fix por ter contas COGS personalizadas e múltiplas por -- | elemento de custo. Agora, obtenha apenas uma conta COGS.+=============================================================================+*/ S CAC Margin Analysis Account Summary Rapportera för marginalen från kundfakturor och leveranser, inklusive försäljnings- och COGS-konton, baserat på standardtabellen Oracle Margin, cst_margin_summary. Du måste först köra begäran om marginalanalys belastning för att fylla i tabellen. Observera att om du har anpassat subledger-redovisning eller använt anpassade program för att registrera COGS efter kostnadselement, visar den här rapporten endast det första COGS-kontot, eftersom det bara finns en rapporterad rad per försäljningsorderrad. / * + ================================================== ================================ + - | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | - | Alla rättigheter förbehållna. | - | Tillstånd att använda den här koden beviljas förutsatt att den ursprungliga författaren är | - | erkänd. Inga garantier, uttryckliga eller på annat sätt ingår i detta | - | lov. | - + ================================================= ================================ + - | - | Ursprunglig författare: Douglas Volz (doug@volzconsulting.com) - | - | Parametrar: - | p_trx_date_from - Startdatum för transaktioner från kunder - | p_trx_date_to - Transaktionsdatum för kundtransporter - | p_category_set1 - Den första artikelkategorin som ska rapporteras, vanligtvis - | Kostnad eller produktlinjekategoriuppsättning - | p_category_set2 - Den andra artikelkategorin som ska rapporteras, vanligtvis - | Inventory Category Set - | p_customer_name - Ange det specifika kundnamn du vill rapportera (valfritt) - | p_item_number - Ange det specifika artikelnummer du vill rapportera (valfritt) - | p_org_code - Specifik inventeringsorganisation som du vill rapportera (valfritt) - | p_operating_unit - Operationsenhet som du vill rapportera, lämna tom för alla - | manöverenheter (tillval) - | p_ledger - Huvudbok som du vill rapportera, lämna tomt för alla - | ledgers (valfritt) - | - | Beskrivning: - | Rapportera för marginalen från kundfakturor och leveranser, baserat - | på standard Oracle Margin-tabellen, cst_margin_summary. Du behöver först - | för att köra begäran om marginalanalysbyggnad, för att fylla i denna tabell. - | - | Version modifierad på modifierad av beskrivning - | ===================================================== ========================= - | 1.0 18 APR 2006 Douglas Volz inledande kodning - | 1.1 15 MAJ 2006 Douglas Volz arbetsversion - | 1.2 17 MAJ 2006 Douglas Volz Lade till orderinformation och datuminformation - | 1.3 14 dec 2012 Douglas Volz modifierad för Garlock, ändrad kategorisats - | 1.4 19 december 2012 Douglas Volz Buggfix för kategorisatsnamn - | 1.5 29 jan 2013 Douglas Volz Parametrar för fasta datum har samma format - | som andra rapporter; - | 1.6 25 feb 2013 Douglas Volz har lagt till apps.mtl_default_category_sets mdcs-tabell - | för att göra manuset mer generiskt - | 1.7 27 feb 2017 Douglas Volz modifierad för artikelkategori och kund - | information. - | 1.8 28 feb 2017 Douglas Volz tog bort information om säljare, - | orsakade tvärförening. - | 1.9 22 maj 2017 Douglas Volz Lägg till kategori för inventeringsartiklar - | 1.10 23 maj 2020 Douglas Volz Använd flerspråkig tabell för UOM-kod, artikelmästare - | OE-transaktionstyper och HR-organisationsnamn. - | 1.11 06 nov 2020 Douglas Volz Fix för att ha anpassade, flera COGS-konton av - | kostnadselement. Nu får du bara ett COGS-konto. + ============================================ ===================================== + * / TR CAC Margin Analysis Account Summary Standart Oracle Margin tablosu cst_margin_summary'ye dayalı olarak Satış ve SMM hesapları dahil olmak üzere müşteri faturalarından ve sevkiyatlarından elde edilen marj için rapor. Bu tabloyu doldurmak için önce Margin Analysis Load Run isteğini çalıştırmanız gerekir. Yardımcı Defter Muhasebesini özelleştirdiyseniz veya SMM'yi maliyet öğesine göre kaydetmek için özel programlar kullandıysanız, satış siparişi satırı başına yalnızca bir rapor edilen satır olduğundan bu raporun yalnızca ilk SMM hesabını gösterdiğini unutmayın. / * + ============================================= ============================ + - | Telif Hakkı 2006 - 2020 Douglas Volz Consulting, Inc. | - | Her hakkı saklıdır. | - | Bu kodu kullanma izni, orijinal yazarın | - | kabul edildi. Buna açık veya başka hiçbir garanti dahil edilmemiştir | - | izin. | - + =============================================== ============================ + - | - | Orijinal Yazar: Douglas Volz (doug@volzconsulting.com) - | - | Parametreler: - | p_trx_date_from - Müşteri gönderileri için başlangıç işlem tarihi - | p_trx_date_to - Müşteri gönderileri için işlem tarihinin sona ermesi - | p_category_set1 - Raporlanacak ilk öğe kategorisi, genellikle - | Maliyet veya Ürün Grubu Kategori Kümesi - | p_category_set2 - Raporlanacak ikinci öğe kategorisi, genellikle - | Envanter Kategori Kümesi - | p_customer_name - Bildirmek istediğiniz belirli müşteri adını girin (isteğe bağlı) - | p_item_number - Bildirmek istediğiniz belirli öğe numarasını girin (isteğe bağlı) - | p_org_code - Raporlamak istediğiniz belirli envanter organizasyonu (isteğe bağlı) - | p_operating_unit - Bildirmek istediğiniz İşletim Birimi, tümü için boş bırakın - | işletim birimleri (isteğe bağlı) - | p_ledger - Bildirmek istediğiniz genel muhasebe, tümü için boş bırakın - | defterler (isteğe bağlı) - | - | Açıklama: - | Müşteri faturaları ve gönderilerinden elde edilen marj raporu, - | standart Oracle Margin tablosunda, cst_margin_summary. İlk ihtiyacın var - | Marjin Analizi Oluşturma isteğini çalıştırmak, bu tabloyu doldurmak için. - | - | Açıklamaya Göre Değiştirildiğinde Değiştirilen Sürüm - | ======= =========== ============== ================== ======================= - | 1.0 18 Nisan 2006 Douglas Volz İlk Kodlama - | 1.1 15 MAYIS 2006 Douglas Volz Çalışma versiyonu - | 1.2 17 MAYIS 2006 Douglas Volz Sipariş satırı ve tarih bilgisi eklendi - | 1.3 14 Ara 2012 Douglas Volz Garlock için Değiştirildi, kategori seti değiştirildi - | 1.4 19 Ara 2012 Douglas Volz Kategori kümesi adı için hata düzeltmesi - | 1.5 29 Ocak 2013 Douglas Volz Sabit tarih parametreleri aynı formatta olacak - | diğer raporlar gibi; - | 1.6 25 Şub 2013 Douglas Volz apps.mtl_default_category_sets mdcs tablosu eklendi - | senaryoyu daha genel hale getirmek için - | 1.7 27 Şub 2017 Douglas Volz Ürün Kategorisi ve müşteri için değiştirildi - | bilgi. - | 1.8 28 Şub 2017 Douglas Volz Satış temsilcisi bilgileri kaldırıldı, - | çapraz birleşmeye neden oluyordu. - | 1.9 22 Mayıs 2017 Douglas Volz Envanter öğesi kategorisi ekleniyor - | 1.10 23 Mayıs 2020 Douglas Volz UOM Kodu için çoklu dil tablosu kullanın, öğe ana - | OE işlem türleri ve insan kaynakları organizasyon adları. - | 1.11 06 Kas 2020 Douglas Volz, özel, birden fazla SMM hesabına sahip olma - | Maliyet unsuru. Şimdi yalnızca bir SMM hesabı edinin. + ======================================== ================================= + * / US CAC Margin Analysis Account Summary Report for the margin from the customer invoices and shipments, including the Sales and COGS accounts, based on the standard Oracle Margin table, cst_margin_summary. (If you want to show the COGS and Sales Margins without accounts, use report CAC Margin Analysis Summary.) Notes: 1) In order to run this report, you first need to run the Margin Analysis Load Run request (to populate the standard Oracle Margin table). 2) If you have customized Subledger Accounting or used custom programs to record COGS by cost element, this report shows only the first COGS account, as there is only one reported row per sales order line. Parameters: =========== Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Customer Name: enter the specific customer name you wish to report (optional). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2006 - 2024 Douglas Volz Consulting, inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this -- | permission. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: xxx_margin_analysis_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 18 APR 2006 Douglas Volz Initial Coding -- | 1.1 15 MAY 2006 Douglas Volz Working version -- | 1.2 17 MAY 2006 Douglas Volz Added order line and date information -- | 1.3 14 Dec 2012 Douglas Volz Modified for Garlock, changed category set -- | 1.4 19 Dec 2012 Douglas Volz Bug fix for category set name -- | 1.5 29 Jan 2013 Douglas Volz Fixed date parameters to have same format -- | as other reports; had to remove sales and COGS accounts as these are -- | on different rows and would need to rewrite the code to include this. -- | Also added a join for mic and mcs for category_set_id to avoid duplicate rows. -- | And added a having clause to screen out zero rows. -- | 1.6 25 Feb 2013 Douglas Volz Added mtl_default_category_sets mdcs table -- | to make the script more generic -- | 1.7 27 Feb 2017 Douglas Volz Modified for Item Category and customer -- | information. -- | 1.8 28 Feb 2017 Douglas Volz Removed sales rep information, -- | was causing cross-joining. -- | 1.9 22 May 2017 Douglas Volz Adding Inventory item category -- | 1.10 23 May 2020 Douglas Volz Use multi-language table for UOM Code, item master -- | OE transaction types and hr organization names. -- | 1.11 06 Nov 2020 Douglas Volz Fix for having custom, multiple COGS accounts by -- | cost element. Now only get one COGS account. -- | 1.12 14 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and org access controls. -- +=============================================================================+*/ ZHS CAC Margin Analysis Account Summary 根据标准的Oracle保证金表cst_margin_summary，报告来自客户发票和发货的保证金，包括销售和COGS账户。 你首先需要运行保证金分析加载运行请求，来填充这个表。 请注意，如果你有自定义的子账会计或使用自定义程序按成本元素记录COGS，这个报告只显示第一个COGS账户，因为每个销售订单行只有一行报告。 /* +=============================================================================+ -- 版权所有 2006 - 2020 道格拉斯-沃尔兹咨询公司。 | -- 保留所有权利。 | -- 允许使用该代码，但前提是原作者必须是｜。 -- 认可。 不包含任何明示或暗示的保证。 -- 允许。 | -- +=============================================================================+ -- | -- 原作者：Douglas Volz ()Douglas Volz (doug@volzconsulting.com) -- | -- 参数。 -- p_trx_date_from -- 客户货物的起始交易日期。 -- p_trx_date_to -- 客户货物的交易结束日期。 -- p_category_set1 -- 要报告的第一个项目类别，通常是p_category_set1。 -- 成本或产品系列类别集 -- p_category_set2 -- 要报告的第二个项目类别，通常是p_category_set2。 -- 库存类别集 -- p_customer_name -- 输入您希望报告的具体客户名称（可选）。 -- | p_item_number -- 输入您希望报告的具体项目编号（可选）。 -- -- p_org_code -- -- 您希望报告的具体清单组织（可选）。 -- | p_operating_unit -- 您希望报告的操作单位，所有单位留空。 -- 操作单元(可选) -- p_ledger -- 你想报告的总账，所有的总账留空。 -- 账目（可选） -- | -- 描述 -- -- -- 报告客户发票和货物的利润率，基于 -- 在标准的Oracle Margin表中，cst_margin_summary。 你首先需要 -- 运行保证金分析构建请求，以填充该表。 -- | -- | ======= =========== ============== ========================================= -- 2006年4月18日Douglas Volz初步编码。 -- 1.1 2006年5月15日 Douglas Volz 工作版 -- 1.2 2006年5月17日 Douglas Volz 添加了订单行和日期信息。 | 1.3 2012年12月14日 Douglas Volz为Garlock修改，改变了类别设置。 -- | 1.4 2012年12月19日 Douglas Volz 修复了类别集名称的错误。 -- 1.5 2013年1月29日 Douglas Volz 修正了日期参数，使其具有相同的格式。 -- | 作为其他报告。 -- 1.6 2013年2月25日 Douglas Volz 添加了 apps.mtl_default_category_sets mdcs 表。 -- 使脚本更加通用 -- | 1.7 2017年2月27日 Douglas Volz 为项目类别和客户修改。 -- 信息。 -- | 1.8 2017年2月28日 Douglas Volz 删除了销售代表信息。 -- | 是造成交叉连接。 | 1.9 2017年5月22日 Douglas Volz 增加库存项目类别。 -- 1.10 2020年5月23日 Douglas Volz 为UOM代码、项目主表使用多语言表。 -- 1.11 2020年11月6日 Douglas Volz 修正了自定义多个COGS账户的问题。 --成本要素。 现在只得到一个COGS账户。+=============================================================================+*/。 Enginatics R12 only &category_columns &segment_columns 1=1 4=4 1 -10 &segment_columns with x as ( select fifsv.application_column_name, fifsv.form_left_prompt from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num ) select 'gcc_sales.'||lower(x.application_column_name)||' "Sales '||substr(x.form_left_prompt,1,21)||'",' text from x union all select 'gcc_cogs.'||lower(x.application_column_name)||' "COGS '||substr(x.form_left_prompt,1,21)||'",' text from x LOV GL Chart of Accounts (inv org restricted) 8E2FF36EDEC579D2E0530100007F1FF2 Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and fifsv.id_flex_num in (select oav.chart_of_accounts_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and fifsv.id_flex_num in (select oav.chart_of_accounts_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id = nvl(fnd_profile.value('MFG_ORGANIZATION_ID'),ood.organization_id)) Y US Chart of Accounts 2 10 4=4 cms.gl_date >= :p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date From 3 20 4=4 cms.gl_date < :p_trx_date_to + 1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date To 4 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 5 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 6 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 7 60 1=1 hz.party_name = :p_customer_name LOV AR Customer Name (Organizations Only) A7086A92F0E7B16EE053BB6B63589F6B Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hp.party_type='ORGANIZATION' order by hp.party_name US Customer Name 8 70 1=1 msiv.concatenated_segments = :p_item_number LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Item Number 9 80 1=1 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 10 90 1=1 haou2.name = :p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 11 100 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code Item Number B5D4B4EE2E9DB387B125B41E14F8CD2A Pivot by Org Y MFG 18 COGS Account 16 COGS Company 17 COGS Department 20 COGS Product 19 COGS Sub-Account 35 COGS_AMOUNT 5 CUSTOMER 4 CUSTOMER_CLASS_CODE 6 CUSTOMER_NUMBER 27 Class 26 Family 32 INVOICE_QUANTITY 21 ITEM 22 ITEM_DESCRIPTION 23 ITEM_TYPE 28 Inv.Items Description 1 LEDGER 36 MARGIN 2 OPERATING_UNIT 8 ORDER_LINE 7 ORDER_NUMBER 9 ORDER_TYPE 3 ORG_CODE 37 PERCENT 24 Product Cat 25 Product Description 34 SALES_AMOUNT 33 SHIP_QUANTITY 13 Sales Account 11 Sales Company 12 Sales Department 15 Sales Product 14 Sales Sub-Account 10 TRANSACTION_DATE 31 UNIT_COST 30 UNIT_PRICE 29 UOM_CODE COGS_AMOUNT VALUE 4 SUM INVOICE_QUANTITY VALUE 1 SUM LEDGER ROW 1 MARGIN VALUE 5 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 SALES_AMOUNT VALUE 3 SUM SHIP_QUANTITY VALUE 2 SUM S Site B5D4B4EE2E9DB387B125B41E14F8CD2A