ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A666368252112708E053BB6B63580FD9Y select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, cms.customer_class_code, hp.party_name customer, hca.account_number customer_number, cms.order_number, cms.line_number order_line, ottt.name order_type, trunc(cms.gl_date) transaction_date, msiv.concatenated_segments item, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, &category_columns msiv.primary_uom_code uom_code, round(sum(nvl(cms.invoiced_amount,0))/decode(sum(nvl(cms.invoice_line_quantity,0)),0,1,sum(nvl(cms.invoice_line_quantity,0))),5) unit_price, round(sum(nvl(cms.cogs_amount,0))/decode(sum(nvl(cms.ship_quantity,0)),0,1,sum(nvl(cms.ship_quantity,0))),5) unit_cost, sum(nvl(cms.invoice_line_quantity,0)) invoice_quantity, sum(nvl(cms.ship_quantity,0)) ship_quantity, sum(nvl(cms.invoiced_amount,0)) sales_amount, sum(nvl(cms.cogs_amount,0)) cogs_amount, sum(nvl(cms.invoiced_amount,0))-sum(nvl(cms.cogs_amount,0)) margin, round((sum(nvl(cms.invoiced_amount,0))-sum(nvl(cms.cogs_amount,0)))/decode(sum(nvl(cms.invoiced_amount,0)),0,1,sum(nvl(cms.invoiced_amount,0)))*100,1) percent from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, cst_margin_summary cms, oe_transaction_types_tl ottt, hz_cust_accounts hca, hz_parties hp, mtl_system_items_vl msiv where 1=1 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=cms.parent_organization_id and haouv_ou.organization_id=cms.org_id and cms.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') and cms.customer_id=hca.cust_account_id and hca.party_id=hp.party_id and cms.parent_organization_id=msiv.organization_id and cms.parent_inventory_item_id=msiv.inventory_item_id group by nvl(gl.short_name,gl.name), haouv_ou.name, mp.organization_code, cms.customer_class_code, cms.sold_to_customer_name, hp.party_name, hca.account_number, cms.order_number, cms.line_number, ottt.name, trunc(cms.gl_date), msiv.concatenated_segments, msiv.description, msiv.item_type, msiv.primary_uom_code, msiv.inventory_item_id, msiv.organization_id order by ledger, operating_unit, org_code, customer, cms.order_number, cms.line_number Rebuilt to the Blitz Report SQL standards. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. D CAC Margin Analysis Summary Bericht für die Marge aus den Kundenrechnungen und Sendungen, basierend auf der Standard-Oracle-Margen-Tabelle, cst_margin_summary. (Wenn Sie die COGS- und Verkaufskonten anzeigen möchten, verwenden Sie den Bericht CAC Margin Analysis Account Summary). Sie müssen zuerst die Anforderung "Margenanalyse-Ladevorgang" ausführen, um diese Tabelle aufzufüllen. /* +=============================================================================+ -- | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | -- | Alle Rechte vorbehalten. | -- | | Die Erlaubnis, diesen Code zu verwenden, 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 Kundentransporte -- | 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; musste Umsatz- und COGS-Konten entfernen, da diese -- | auf verschiedenen Zeilen sind und den Code neu schreiben müssten, um dies zu berücksichtigen. -- | Außerdem wurde ein Join für mic und mcs für category_set_id hinzugefügt, um doppelte Zeilen zu vermeiden. -- | Und eine Having-Klausel hinzugefügt, um Nullzeilen auszusieben. -- | 1.6 25. Feb 2013 Douglas Volz hat die Tabelle apps.mtl_default_category_sets mdcs 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, Artikel verwenden -- | Stamm, OE-Transaktionsarten und HR-Organisationsnamen. -- +=============================================================================+*/ F CAC Margin Analysis Summary Rapport pour la marge des factures clients et des expéditions, basé sur le tableau standard Oracle Margin, cst_margin_summary. (Si vous souhaitez afficher le COGS et les comptes de ventes, utilisez le rapport CAC Margin Analysis Account Summary). Vous devez d'abord exécuter la demande de chargement de l'analyse de marge, pour remplir ce tableau. /* +=============================================================================+ | 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 ; a dû supprimer les comptes de ventes et de COGS car | sur différentes lignes et il faudrait réécrire le code pour inclure ceci. | a également ajouté une jointure pour mic et mcs pour category_set_id afin d'éviter les doublons. -- Et ajouté une clause "having" pour éliminer les lignes nulles. | 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 le tableau multilingue pour le code UOM, point |, les types de transactions OE et les noms des organisations RH. -- +=============================================================================+*/ I CAC Margin Analysis Summary Report per il margine dalle fatture dei clienti e dalle spedizioni, basato sulla tabella standard Oracle Margin, cst_margin_summary. (Se vuoi mostrare i conti COGS e vendite usa il rapporto CAC Margin Analysis Account Summary). Devi prima eseguire la richiesta Margin Analysis Load Run, per popolare questa tabella. /* +=============================================================================+ -- | 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 report; ho dovuto rimuovere i conti vendite e COGS perché questi sono -- | su righe diverse e sarebbe necessario riscrivere il codice per includerlo. -- | Aggiunto anche un join per mic e mcs per category_set_id per evitare righe duplicate. -- | E aggiunto una clausola having per schermare le righe zero. -- | 1.6 25 Feb 2013 Douglas Volz Aggiunto apps.mtl_default_category_sets mdcs table -- | | 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, articolo -- | master, tipi di transazione OE e nomi di organizzazioni hr. -- +=============================================================================+*/ KO CAC Margin Analysis Summary 표준 Oracle Margin 테이블 cst_margin_summary를 기반으로 고객 송장 및 배송의 마진을보고합니다. (COGS 및 판매 계정을 표시하려면 CAC 마진 분석 계정 요약 보고서를 사용하십시오.)이 테이블을 채우려면 먼저 마진 분석로드 실행 요청을 실행해야합니다. / * + ============================================= ============================= + -| 저작권 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 동일한 형식을 갖는 고정 날짜 매개 변수 -| 다른 보고서로; 판매 및 COGS 계정을 제거해야했습니다. -| 이를 포함하도록 코드를 다시 작성해야합니다. -| 또한 중복 행을 방지하기 위해 category_set_id에 대한 mic 및 mcs에 대한 조인을 추가했습니다. -| 그리고 0 행을 걸러 내기 위해 having 절을 추가했습니다. -| 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 조직 이름. -+ ============================================== ============================= + * / PTB CAC Margin Analysis Summary Relatório para a margem das faturas e remessas dos clientes, baseado na tabela padrão de Margens Oracle, cst_margin_summary. (Se você quiser mostrar o COGS e Contas de Vendas use o relatório CAC Margin Analysis Account Summary). Para preencher esta tabela, é necessário primeiro executar a solicitação de Análise de Margem de Carga. /* +=============================================================================+ | 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; teve que remover as contas de vendas e COGS como estas são | em linhas diferentes e precisaria reescrever o código para incluir isto. -- | também acrescentou um join para microfone e mcs para category_set_id para evitar a duplicação de linhas. -- | E adicionou uma cláusula de ter para filtrar linhas zero. -- | 1.6 25 Fev 2013 Douglas Volz Adicionado aplicativos.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 | master, tipos de transação OE e nomes de organizações hr. -- +=============================================================================+*/ S CAC Margin Analysis Summary Rapportera för marginalen från kundfakturor och leveranser, baserat på standard Oracle Margin-tabellen, cst_margin_summary. (Om du vill visa COGS och försäljningskonton använder rapporten CAC marginalanalys kontosammanfattning.) Du måste först köra begäran om marginalanalys belastningskörning för att fylla i denna tabell. / * + ================================================== ================================ + - | 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; var tvungen att ta bort försäljnings- och COGS-konton som dessa är - | på olika rader och skulle behöva skriva om koden för att inkludera den. - | Har också lagt till en koppling för mikrofon och MC för kategori_set_id för att undvika dubbla rader. - | Och lade till en ha klausul för att skärma ut noll rader. - | 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, artikel - | master, OE-transaktionstyper och HR-organisationsnamn. - + ================================================== ================================ + * / TR CAC Margin Analysis Summary Standart Oracle Margin tablosu cst_margin_summary'ye dayalı olarak müşteri faturalarından ve sevkiyatlarından elde edilen marj için rapor. (SMM'yi ve Satış Hesaplarını göstermek istiyorsanız, CAC Marjin Analizi Hesap Özeti raporunu kullanın.) Bu tabloyu doldurmak için önce Marjin Analizi Yükleme Çalıştırma isteğini çalıştırmanız gerekir. / * + ============================================= ============================ + - | 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; satışları ve SMM hesaplarını kaldırmak zorunda kaldılar - | farklı satırlarda ve bunu eklemek için kodu yeniden yazmanız gerekir. - | Ayrıca yinelenen satırları önlemek için category_set_id için mikrofon ve mcs için bir birleştirme eklendi. - | Ve sıfır satırı elemek için bir sahip olan yan tümce eklendi. - | 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, madde - | ana, OE işlem türleri ve insan kaynakları organizasyon adları. - + =============================================== ============================ + * / US CAC Margin Analysis Summary Report for the margin from the customer invoices and shipments, based on the standard Oracle Margin table, cst_margin_summary. (If you want to show the COGS and Sales Accounts use report CAC Margin Analysis Account Summary.) Note: in order to run this report, you first need to run the Margin Analysis Load Run request (to populate the standard Oracle Margin table). 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) -- | -- | 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 apps.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 14 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and org access controls. -- | 1.12 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ ZHS CAC Margin Analysis Summary 根据标准的Oracle Margin表cst_margin_summary，报告客户发票和发货的利润率。 如果你想显示COGS和销售账户，请使用报告CAC Margin Analysis Account Summary）。 你首先需要运行Margin Analysis Load Run请求，来填充这个表。 /* +=============================================================================+ -- 版权所有 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 修正了日期参数，使其具有相同的格式。 -- 作为其他报告；必须删除销售和COGS账户，因为这些账户是： -- 在不同的行上，需要重写代码来包含这一点。 -- 还为mic和mcs的category_set_id添加了一个连接，以避免重复的记录。 -- 添加了一个Having子句来筛选出零记录。 -- 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代码中使用多语言表，项目。 -- 主、业务约定交易类型和人力资源组织名称。 -- +=============================================================================+*/ Enginatics &category_columns 1=1 1 10 1=1 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 2 20 1=1 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 3 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 4 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 5 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 6 60 1=1 hp.party_name=:p_customer_name LOV AR Customer Name (Organizations Only) A7086A92F0E7B16EE053BB6B63589F6B 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 7 70 1=1 msiv.concatenated_segments=:p_item_number LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 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 8 80 1=1 mp.organization_code=:p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 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 ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 9 90 1=1 haouv_ou.name=:p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB 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 in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and (:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 10 100 1=1 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 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 ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) 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 BE1FE6917756074E4D1291C7D478B4B1 Pivot by Org Y MFG 25 COGS_AMOUNT 5 CUSTOMER 4 CUSTOMER_CLASS_CODE 6 CUSTOMER_NUMBER 17 Class 16 Family 22 INVOICE_QUANTITY 11 ITEM 12 ITEM_DESCRIPTION 13 ITEM_TYPE 18 Inv.Items Description 1 LEDGER 26 MARGIN 2 OPERATING_UNIT 8 ORDER_LINE 7 ORDER_NUMBER 9 ORDER_TYPE 3 ORG_CODE 27 PERCENT 14 Product Cat 15 Product Description 24 SALES_AMOUNT 23 SHIP_QUANTITY 10 TRANSACTION_DATE 21 UNIT_COST 20 UNIT_PRICE 19 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 BE1FE6917756074E4D1291C7D478B4B1