ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEC879D2E0530100007F1FF2 GL Chart of Accounts (inv org related) 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 + 1) > sysdate) order by fifsv.id_flex_structure_name AR دليل الحسابات المتعلقة بمنظمات المخزون D Kontenplan für Bestandsorganisationen E Plan de cuentas relacionado con las organizaciones de inventario F Plan comptable relatif aux organismes d'inventaire I Piano dei conti relativo alle organizzazioni dell'inventario JA 棚卸し組織に関する勘定科目一覧表 KO 재고 조직과 관련된 계정과 목표 PTB Plano de contas relacionado às organizações de inventário RU План счетов организаций, занимающихся инвентаризацией S Kontoplan relaterade till lagerorganisationer TR Envanter organizasyonları ile ilgili hesap planı US Chart of accounts related to inventory organizations 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 与所有现行清单组织有关的所有分类账 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 所有现行清单组织代码，不包括主组织 7F9E3C97A9C63299E0530100007F4ED4 select rcv_sum. Destination_Type, -- ============================================================= -- Get summary of net PO receiving activity by PO Distribution -- ============================================================= rcv_sum.Ledger, rcv_sum.Operating_Unit, rcv_sum.Org_Code, rcv_sum.Period_Name, rcv_sum.Accounting_Line_Type, rcv_sum.PO_Number, rcv_sum.PO_Line, rcv_sum.PO_Distribution_Id, &segment_columns rcv_sum.WIP_Job, rcv_sum.Item_Number, rcv_sum.Item_Description, -- Revision for version 1.5 fcl.meaning Item_Type, sum(rcv_sum.Amount) Amount from gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2, fnd_common_lookups fcl, -- ============================================================= -- Get the Receipts into Receiving Inspection and the Deliveries -- out of Receiving Inspection from Purchasing -- ============================================================= (select pod.destination_type_code Destination_Type, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, -- Revision for version 1.2 'RCV' Transaction_Source, rrsl.accounting_line_type Accounting_Line_Type, flv.meaning Transaction_Type, rt.transaction_id Transaction_Id, poh.segment1 PO_Number, pol.line_num PO_Line, pod.po_distribution_id PO_Distribution_Id, al.code_combination_id rcv_ccid, -- gcc1 pod.code_combination_id pod_ccid, -- gcc2 nvl((select we.wip_entity_name from wip_entities we where we.wip_entity_id = pod.wip_entity_id),'') WIP_Job, msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.5 msiv.item_type, sum(nvl(al.accounted_dr,0) - nvl(al.accounted_cr,0)) Amount from rcv_receiving_sub_ledger rrsl, rcv_transactions rt, po_headers_all poh, po_lines_all pol, rcv_shipment_lines rsl, po_distributions_all pod, fnd_lookup_values flv, mtl_system_items_vl msiv, org_acct_periods oap, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.4, remove tables to increase performance -- xla_transaction_entities ent, -- xla_events xe, -- End revision for version 1.4 xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al -- ======================================================== -- Material Transaction, org and item joins -- ======================================================== where rrsl.rcv_transaction_id = rt.transaction_id and rt.shipment_line_id = rsl.shipment_line_id and rsl.item_id = msiv.inventory_item_id (+) and rsl.to_organization_id = msiv.organization_id (+) and mp.organization_id = rt.organization_id -- RMAs and REQs are not accounted for by Purchasing and rt.source_document_code not in ('RMA', 'REQ') and pod.po_distribution_id = rsl.po_distribution_id and pod.po_header_id = poh.po_header_id and pod.po_line_id = pol.po_line_id and rrsl.accounting_line_type <> 'Accrual' -- ======================================================== -- Inventory Org accounting period joins -- ======================================================== and oap.period_name = ah.period_name and oap.organization_id = rt.organization_id -- ======================================================== -- Version 1.3, added lookup values to see more detail -- ======================================================== and flv.lookup_type = 'RCV TRANSACTION TYPE' and flv.lookup_code = rt.transaction_type -- Revision for version 1.2 -- and source_lang = 'US' -- and language = 'US' and flv.language = userenv('LANG') -- ======================================================== -- using the base tables to avoid the performance issues -- with org_organization_definitions and hr_operating_units -- ======================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = rt.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and 1=1 -- p_trx_date_from, p_trx_date_to, p_org_code, p_operating_unit, p_ledger 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) -- ======================================================== -- SLA table joins to get the exact account numbers -- ======================================================== -- Revision for version 1.4, performance improvements -- and ent.entity_code = 'RCV_ACCOUNTING_EVENTS' -- and ent.application_id = 707 -- and xe.application_id = ent.application_id -- and xe.event_id = xdl.event_id -- and ah.entity_id = ent.entity_id -- and ah.ledger_id = ent.ledger_id -- and ah.event_id = xe.event_id -- and al.application_id = ent.application_id -- and xdl.application_id = ent.application_id and ah.ledger_id = gl.ledger_id -- End revisions for version 1.4 and ah.application_id = al.application_id and ah.application_id = 707 and ah.ae_header_id = al.ae_header_id and al.ledger_id = ah.ledger_id and al.ae_header_id = xdl.ae_header_id and al.ae_line_num = xdl.ae_line_num and xdl.application_id = 707 and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER' and xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id -- Revision for version 1.4, outer join for CCIDs -- ======================================================== -- Account code combination id joins -- ======================================================== -- Revision for version 1.2 -- and gcc1.segment3 = '1449' and rrsl.code_combination_id in (select rp.receiving_account_id from rcv_parameters rp where rp.organization_id = mp.organization_id) -- Revision for version 1.4, outer join for CCIDs and rrsl.code_combination_id (+) = al.code_combination_id -- ========================================================== group by pod.destination_type_code, nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, -- Revision for version 1.2 'RCV', rrsl.accounting_line_type, flv.meaning, rt.transaction_id, poh.segment1, pol.line_num, pod.po_distribution_id, al.code_combination_id, -- rcv_ccid, -- gcc1 pod.code_combination_id, -- pod_ccid, -- gcc2 pod.wip_entity_id, msiv.concatenated_segments, msiv.description, -- Revision for version 1.5 msiv.item_type -- ============================================================= -- Get the Deliveries from Receiving Inspection to WIP for -- outside processing -- ============================================================= union all select pod.destination_type_code Destination_Type, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, -- Revision for version 1.2 'WIP' Transaction_Source, ml.meaning Accounting_Line_Type, ml2.meaning Transaction_Type, wt.transaction_id Transaction_Id, poh.segment1 PO_Number, pol.line_num PO_Line, pod.po_distribution_id PO_Distribution_Id, al.code_combination_id rcv_ccid, -- gcc1 pod.code_combination_id pod_ccid, -- gcc2 nvl((select we.wip_entity_name from wip_entities we where we.wip_entity_id = pod.wip_entity_id),'') WIP_Job, msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.5 msiv.item_type, sum(nvl(al.accounted_dr,0) - nvl(al.accounted_cr,0)) Amount from wip_transaction_accounts wta, wip_transactions wt, wip_accounting_classes wac, wip_discrete_jobs wdj, rcv_transactions rt, rcv_shipment_lines rsl, po_headers_all poh, po_lines_all pol, po_distributions_all pod, mfg_lookups ml2, mtl_system_items_vl msiv, org_acct_periods oap, mtl_parameters mp, mfg_lookups ml, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.4, remove tables to increase performance -- xla_transaction_entities ent, -- xla_events xe, -- End revision for version 1.4 xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al -- ======================================================== -- Material Transaction, org and item joins -- ======================================================== where wta.transaction_id = wt.transaction_id and wta.organization_id = msiv.organization_id and mp.organization_id = msiv.organization_id -- Oracle bug=> the accounting line type used is 4 (res absorption) and should be 5 (receiving) and wta.accounting_line_type in (4,5) -- get all of them to see the offset accounts and wt.rcv_transaction_id = rt.transaction_id and rt.shipment_line_id = rsl.shipment_line_id and rsl.item_id = msiv.inventory_item_id (+) and rsl.to_organization_id = msiv.organization_id (+) and mp.organization_id = rt.organization_id -- RMAs and REQs are not accounted for by Purchasing and rt.source_document_code not in ('RMA', 'REQ') and pod.po_distribution_id = rsl.po_distribution_id and pod.po_header_id = poh.po_header_id and pod.po_line_id = pol.po_line_id -- ======================================================== -- WIP class and WIP job joins -- ======================================================== and wac.class_code = wdj.class_code and wdj.wip_entity_id = wt.wip_entity_id and wdj.organization_id = wac.organization_id -- ======================================================== -- Inventory Org accounting period joins -- ======================================================== and oap.acct_period_id = wt.acct_period_id and oap.organization_id = wt.organization_id -- ======================================================== -- Version 1.3, added lookup values to see more detail -- ======================================================== and ml.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml.lookup_code = wta.accounting_line_type and ml2.lookup_type = 'WIP_TRANSACTION_TYPE_SHORT' and ml2.lookup_code = wt.transaction_type -- ======================================================== -- using the base tables to avoid the performance issues -- with org_organization_definitions and hr_operating_units -- ======================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = wta.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and 2=2 -- p_trx_date_from, p_trx_date_to, p_org_code, p_operating_unit, p_ledger and wta.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) -- ======================================================== -- SLA table joins to get the exact account numbers -- ======================================================== -- Revision for version 1.4, performance improvements -- and ent.entity_code = 'WIP_ACCOUNTING_EVENTS' -- and ent.application_id = 707 -- and xe.application_id = ent.application_id -- and xe.event_id = xdl.event_id -- and ah.entity_id = ent.entity_id -- and ah.ledger_id = ent.ledger_id -- and ah.event_id = xe.event_id -- and al.application_id = ent.application_id -- and xdl.application_id = ent.application_id and ah.ledger_id = gl.ledger_id -- End revisions for version 1.4 and ah.application_id = al.application_id and ah.application_id = 707 and ah.ae_header_id = al.ae_header_id and al.ledger_id = ah.ledger_id and al.ae_header_id = xdl.ae_header_id and al.ae_line_num = xdl.ae_line_num and xdl.application_id = 707 and xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = wta.wip_sub_ledger_id and wta.reference_account in (select rp.receiving_account_id from rcv_parameters rp where mp.organization_id = mp.organization_id) -- Revision for version 1.4, outer join for CCIDs and wta.reference_account (+) = al.code_combination_id -- ========================================================== group by pod.destination_type_code, nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, -- Revision for version 1.2 'WIP', ml.meaning, ml2.meaning, wt.transaction_id, poh.segment1, pol.line_num, pod.po_distribution_id, al.code_combination_id, -- rcv_ccid, -- gcc1 pod.code_combination_id, -- pod_ccid, -- gcc2 pod.wip_entity_id, msiv.concatenated_segments, msiv.description, -- Revision for version 1.5 msiv.item_type -- ============================================================= -- Get the Deliveries from Receiving Inspection to Stores -- inventory for purchase order receipt transactions -- ============================================================= union all select pod.destination_type_code Destination_Type, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, oap.period_name Period_Name, -- Revision for version 1.2 'INV' Transaction_Source, ml.meaning Accounting_Line_Type, mtt.transaction_type_name Transaction_Type, mmt.transaction_id Transaction_Id, poh.segment1 PO_Number, pol.line_num PO_Line, pod.po_distribution_id PO_Distribution_Id, al.code_combination_id rcv_ccid, -- gcc1 pod.code_combination_id pod_ccid, -- gcc2 nvl((select we.wip_entity_name from wip_entities we where we.wip_entity_id = pod.wip_entity_id),'') WIP_Job, msiv.concatenated_segments Item_Number, msiv.description Item_Description, -- Revision for version 1.5 msiv.item_type, sum(nvl(al.accounted_dr,0) - nvl(al.accounted_cr,0)) Amount from mtl_transaction_accounts mta, mtl_material_transactions mmt, rcv_transactions rt, rcv_shipment_lines rsl, po_headers_all poh, po_lines_all pol, po_distributions_all pod, mtl_transaction_types mtt, mtl_system_items_vl msiv, org_acct_periods oap, mtl_parameters mp, mfg_lookups ml, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.4, remove tables to increase performance -- xla_transaction_entities ent, -- xla_events xe, -- End revision for version 1.4 xla_distribution_links xdl, xla_ae_headers ah, xla_ae_lines al -- ======================================================== -- Material Transaction, org and item joins -- ======================================================== where mta.transaction_id = mmt.transaction_id and mmt.transaction_type_id = mtt.transaction_type_id and mta.organization_id = msiv.organization_id and mta.inventory_item_id = msiv.inventory_item_id and mp.organization_id = msiv.organization_id and mmt.transaction_source_type_id = 1 -- Purchasing and mta.accounting_line_type = 5 and mmt.rcv_transaction_id = rt.transaction_id and rt.shipment_line_id = rsl.shipment_line_id and rsl.item_id = msiv.inventory_item_id (+) and rsl.to_organization_id = msiv.organization_id (+) -- RMAs and REQs are not accounted for by Purchasing and rt.source_document_code not in ('RMA', 'REQ') and pod.po_distribution_id = rsl.po_distribution_id and pod.po_header_id = poh.po_header_id and pod.po_line_id = pol.po_line_id -- ======================================================== -- Inventory Org accounting period joins -- ======================================================== and oap.period_name = ah.period_name and oap.organization_id = mta.organization_id -- ======================================================== -- Version 1.3, added lookup values to see more detail -- ======================================================== and ml.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml.lookup_code = mta.accounting_line_type -- ======================================================== -- using the base tables to avoid the performance issues -- with org_organization_definitions and hr_operating_units -- ======================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mta.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and 3=3 -- p_trx_date_from, p_trx_date_to, p_org_code, p_operating_unit, p_ledger and mta.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) -- ======================================================== -- SLA table joins to get the exact account numbers -- ======================================================== -- Revision for version 1.23, performance improvements -- and ent.entity_code = 'MTL_ACCOUNTING_EVENTS' -- and ent.application_id = 707 -- and xe.application_id = ent.application_id -- and xe.event_id = xdl.event_id -- and ah.entity_id = ent.entity_id -- and ah.ledger_id = ent.ledger_id -- and ah.event_id = xe.event_id -- and al.application_id = ent.application_id -- and xdl.application_id = ent.application_id and ah.ledger_id = gl.ledger_id -- End revisions for version 1.23 and ah.application_id = al.application_id and ah.application_id = 707 and ah.ae_header_id = al.ae_header_id and al.ledger_id = ah.ledger_id and al.ae_header_id = xdl.ae_header_id and al.ae_line_num = xdl.ae_line_num and xdl.application_id = 707 and xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id -- Revision for version 1.4, for performance improvements and mta.reference_account in (select rp.receiving_account_id from rcv_parameters rp where rp.organization_id = mp.organization_id) -- Revision for version 1.4, outer join for CCIDs and mta.reference_account (+) = al.code_combination_id -- ========================================================== group by pod.destination_type_code, nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, oap.period_name, -- Revision for version 1.2 'INV', ml.meaning, mtt.transaction_type_name, mmt.transaction_id, poh.segment1, pol.line_num, pod.po_distribution_id, al.code_combination_id, -- rcv_ccid, -- gcc1 pod.code_combination_id, -- pod_ccid, -- gcc2 pod.wip_entity_id, msiv.concatenated_segments, msiv.description, -- Revision for version 1.5 msiv.item_type ) rcv_sum -- Revision for version 1.4, outer join for CCIDs where gcc1.code_combination_id (+) = rcv_sum.rcv_ccid and gcc2.code_combination_id (+) = rcv_sum.pod_ccid -- Revision for version 1.5 and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = rcv_sum.item_type group by rcv_sum.Destination_Type, rcv_sum.Ledger, rcv_sum.Operating_Unit, rcv_sum.Org_Code, rcv_sum.Period_Name, rcv_sum.Accounting_Line_Type, rcv_sum.PO_Number, rcv_sum.PO_Line, rcv_sum.PO_Distribution_Id, &segment_columns_grp rcv_sum.WIP_Job, rcv_sum.Item_Number, rcv_sum.Item_Description, -- Revision for version 1.5 fcl.meaning -- Item_Type having round(sum(rcv_sum.Amount),3) <> 0 order by 1,2,3,4,6,7,8,9 Replaced tab characters with spaces Y US CAC Receiving Activity Summary Especially for companies who do not use separate inspection receipt processes, this report nets the initial purchase order receipt into receiving against the delivery into stock and WIP outside processing (OSP), for the entered transaction date range. By comparing the Purchasing Receipt transactions with the Purchasing Delivery transactions (for the purchasing receipts, material deliveries and WIP OSP deliveries). Enter the date range you wish to compare, typically a monthly date range. Differences may be due to the initial receipt occurring in the prior month, not running Create Accounting in the current month (as this report uses the Subledger Accounting rules to report your Receiving Valuation Activity), the goods never delivered into stock or OSP or perhaps the delivery into stock or into WIP OSP was not processed by Create Accounting. Parameters: ========== Transaction Date From: enter the starting transaction date (mandatory). Defaults to the current period starting date. Transaction Date To: enter the ending transaction date (mandatory). Defaults to the current period ending date. Organization Code: enter the specific inventory organization(s) you wish to report (optional). Defaults to your session's organization code. 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 2010 - 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_rcv_activity_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 6 Jan 2010 Douglas Volz Initial Coding -- | 1.1 05 Apr 2010 Douglas Volz Added PO number, PO Distribution id to query -- | 1.2 25 Oct 2017 Douglas Volz Now use the receiving parameter Receiving ccid -- | and added transaction source. -- | 1.3 13 Mar 2018 Douglas Volz Added Ledger parameter -- | 1.4 08 Jul 2022 Douglas Volz Multi-language tables for item master. -- | 1.5 24 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and inventory org access controls. -- +=============================================================================+*/ Enginatics R12 only &segment_columns &segment_columns_grp 1=1 2=2 3=3 1 -100 &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 fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num ) select 'gcc1.'||lower(x.application_column_name)||' "Rcvg '||substr(x.form_left_prompt,1,21)||'",' text from x union all select 'gcc2.'||lower(x.application_column_name)||' "Chg '||substr(x.form_left_prompt,1,21)||'",' text from x LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 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 + 1) > sysdate) 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 &segment_columns_grp 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 fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num ) select 'gcc1.'||lower(x.application_column_name)||',' text from x union all select 'gcc2.'||lower(x.application_column_name)||',' text from x US Chart of Accounts 3 10 1=1 rt.transaction_date >= :p_trx_date_from and rrsl.transaction_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 4 2=2 wta.transaction_date >= :p_trx_date_from and wt.transaction_date >= :p_trx_date_from US Transaction Date From 5 3=3 mta.transaction_date >=:p_trx_date_from and mmt.transaction_date >=:p_trx_date_from US Transaction Date From 6 20 1=1 rt.transaction_date < :p_trx_date_to+1 and rrsl.transaction_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 7 2=2 wta.transaction_date < :p_trx_date_to+1 and wt.transaction_date < :p_trx_date_to+1 US Transaction Date To 8 3=3 mta.transaction_date < :p_trx_date_to+1 and mmt.transaction_date < :p_trx_date_to+1 US Transaction Date To 9 25 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 2=2 mp.organization_code = :p_org_code US Organization Code 11 3=3 mp.organization_code = :p_org_code US Organization Code 12 27 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 13 2=2 haou2.name = :p_operating_unit US Operating Unit 14 3=3 haou2.name = :p_operating_unit US Operating Unit 15 30 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 16 2=2 gl.name = :p_ledger US Ledger 17 3=3 gl.name = :p_ledger US Ledger :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code 620B4719CB29E7EBAFD6389E756561DB Pivot by Org by PO Pivot by Destination Type, Ledger, Operating Unit, Org Code and Purchase Order. MFG 6 ACCOUNTING_LINE_TYPE 23 AMOUNT 17 Chg Account 15 Chg Company 16 Chg Department 19 Chg Product 18 Chg Sub-Account 1 DESTINATION_TYPE 22 ITEM_DESCRIPTION 21 ITEM_NUMBER 2 LEDGER 3 OPERATING_UNIT 4 ORG_CODE 5 PERIOD_NAME 9 PO_DISTRIBUTION_ID 8 PO_LINE 7 PO_NUMBER 12 Rcvg Account 10 Rcvg Company 11 Rcvg Department 14 Rcvg Product 13 Rcvg Sub-Account 20 WIP_JOB AMOUNT VALUE 1 SUM DESTINATION_TYPE ROW 1 LEDGER ROW 2 OPERATING_UNIT ROW 3 ORG_CODE ROW 4 PO_NUMBER ROW 5 S Site 620B4719CB29E7EBAFD6389E756561DB