Scribd
Upload
11 views6 pages

Quick Links

The document outlines the steps involved in the purchase order and invoicing process, detailing the creation and approval of purchase requisitions, purchase orders, and invoice matching. It also includes SQL table definitions and queries for financial line items, integrating data from various accounting tables like BSID, BSAD, BSIK, BSAK, BKPF, and BSEG into a central fact table. Additionally, it describes the relationships and join keys between these tables to facilitate data extraction and reporting.

Uploaded by

thanmayasanthoshkumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views6 pages

Quick Links

The document outlines the steps involved in the purchase order and invoicing process, detailing the creation and approval of purchase requisitions, purchase orders, and invoice matching. It also includes SQL table definitions and queries for financial line items, integrating data from various accounting tables like BSID, BSAD, BSIK, BSAK, BKPF, and BSEG into a central fact table. Additionally, it describes the relationships and join keys between these tables to facilitate data extraction and reporting.

Uploaded by

thanmayasanthoshkumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 6

Vicky*4321 outlook

https://www.pickl.ai/blog/hierarchies-in-dimensional-modelling/
https://www.thoughtspot.com/data-trends/data-modeling/dimensional-data-modeling
https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-
dimension-tables

steps in purchase order and invoicing.

Vicky*4321!! -x.ai sanrosh


@sanrosh82 -- sanroshk@yahoo.com

Deepseek- Cric*4321

chatgpt Temple*123456!

purchase requistion - approved


PO creation - Approved
PO dispatch
Supplier
goods receipt
Invoicing matching with supplier invoice
Invoice approval and payment
PO closure.

LineItemStatus VARCHAR(20) CHECK (LineItemStatus IN ('Ordered', 'Partially


Received', 'Received', 'Cancelled')),

----

CREATE TABLE fact_financial_line_items (


document_number VARCHAR(10),
company_code VARCHAR(4),
fiscal_year VARCHAR(4),
line_item VARCHAR(3),
party_id VARCHAR(10), -- KUNNR (customer) or LIFNR (vendor)
party_type VARCHAR(10), -- 'Customer' or 'Vendor'
posting_date DATE,
clearing_date DATE,
document_type VARCHAR(2),
amount DECIMAL(15,2),
currency VARCHAR(5),
status VARCHAR(10), -- 'Open' or 'Cleared'
gl_account VARCHAR(10),
reference VARCHAR(20),
dunning_level CHAR(1),
payment_terms VARCHAR(4),
aging_bucket VARCHAR(20),
created_at TIMESTAMP
);

SELECT
BELNR AS document_number,
BUKRS AS company_code,
GJAHR AS fiscal_year,
BUZEI AS line_item,
KUNNR AS party_id,
'Customer' AS party_type,
BUDAT AS posting_date,
NULL AS clearing_date,
BLART AS document_type,
WRBTR AS amount,
WAERS AS currency,
'Open' AS status,
HKONT AS gl_account,
XREF1 AS reference,
MABER AS dunning_level,
ZTERM AS payment_terms,
<aging_logic> AS aging_bucket,
CURRENT_TIMESTAMP AS created_at
FROM BSID;

-- Extract from BSID (Open Items)


SELECT
BELNR AS document_number,
BUKRS AS company_code,
GJAHR AS fiscal_year,
BUZEI AS line_item,
KUNNR AS customer_number,
BUDAT AS posting_date,
NULL AS clearing_date,
BLART AS document_type,
WRBTR AS amount,
WAERS AS currency,
'Open' AS status,
HKONT AS gl_account,
XREF1 AS reference,
MABER AS dunning_level,
ZTERM AS payment_terms,
<calculate_aging_bucket(BUDAT)> AS aging_bucket,
CURRENT_TIMESTAMP AS created_at
FROM BSID;

-- Extract from BSAD (Cleared Items)


SELECT
BELNR AS document_number,
BUKRS AS company_code,
GJAHR AS fiscal_year,
BUZEI AS line_item,
KUNNR AS customer_number,
BUDAT AS posting_date,
AUGDT AS clearing_date,
BLART AS document_type,
WRBTR AS amount,
WAERS AS currency,
'Cleared' AS status,
HKONT AS gl_account,
XREF1 AS reference,
MABER AS dunning_level,
ZTERM AS payment_terms,
<calculate_aging_bucket(BUDAT, AUGDT)> AS aging_bucket,
CURRENT_TIMESTAMP AS created_at
FROM BSAD;

-- BSIK (Vendor Open Items)


SELECT
BELNR AS document_number,
BUKRS AS company_code,
GJAHR AS fiscal_year,
BUZEI AS line_item,
LIFNR AS party_id,
'Vendor' AS party_type,
BUDAT AS posting_date,
NULL AS clearing_date,
BLART AS document_type,
WRBTR AS amount,
WAERS AS currency,
'Open' AS status,
HKONT AS gl_account,
XREF1 AS reference,
MABER AS dunning_level,
ZTERM AS payment_terms,
<aging_logic> AS aging_bucket,
CURRENT_TIMESTAMP AS created_at
FROM BSIK;

-- BSAK (Vendor Cleared Items)


SELECT
BELNR AS document_number,
BUKRS AS company_code,
GJAHR AS fiscal_year,
BUZEI AS line_item,
LIFNR AS party_id,
'Vendor' AS party_type,
BUDAT AS posting_date,
AUGDT AS clearing_date,
BLART AS document_type,
WRBTR AS amount,
WAERS AS currency,
'Cleared' AS status,
HKONT AS gl_account,
XREF1 AS reference,
MABER AS dunning_level,
ZTERM AS payment_terms,
<aging_logic> AS aging_bucket,
CURRENT_TIMESTAMP AS created_at
FROM BSAK;

CREATE OR REPLACE VIEW dwh.vw_fact_financial_line_items_ext AS


SELECT
f.document_number,
f.company_code,
f.fiscal_year,
f.line_item,
f.party_id,
f.party_type,
f.posting_date,
f.clearing_date,
f.document_type,
f.amount,
f.currency,
f.status,
f.gl_account,
f.reference,
f.dunning_level,
f.payment_terms,
f.aging_bucket,
f.created_at,

-- BKPF enrichments
bkpf.XBLNR AS external_reference, -- Original invoice/reference number
bkpf.BLDAT AS document_date, -- Date document was created
bkpf.USNAM AS posted_by_user, -- User ID who posted the document
bkpf.TCODE AS transaction_code, -- TCode used to post
bkpf.AWTYP AS reference_type, -- E.g., "BKPF", "RMRP"

-- BSEG enrichments (line-level details)


bseg.SHKZG AS debit_credit_indicator, -- S or H
bseg.SGTXT AS line_item_text, -- Description entered
bseg.KOSTL AS cost_center, -- Relevant for CO
bseg.PRCTR AS profit_center -- Profit center
FROM dwh.fact_financial_line_items f
LEFT JOIN sap_bkpf bkpf
ON f.document_number = bkpf.BELNR
AND f.company_code = bkpf.BUKRS
AND f.fiscal_year = bkpf.GJAHR

LEFT JOIN sap_bseg bseg


ON f.document_number = bseg.BELNR
AND f.company_code = bseg.BUKRS
AND f.fiscal_year = bseg.GJAHR
AND f.line_item = bseg.BUZEI;

+----------------------------+
| fact_financial_line_items |
|----------------------------|
| document_number |
| company_code |
| fiscal_year |
| line_item |
| party_id |
| party_type |
| posting_date |
| clearing_date |
| document_type |
| amount |
| currency |
| status |
| gl_account |
| reference |
| dunning_level |
| payment_terms |
| aging_bucket |
| created_at |
+----------------------------+
|
| (Join on document_number, company_code, fiscal_year)
v
+----------------------------+
| BKPF |
|----------------------------|
| BELNR (document_number) |
| BUKRS (company_code) |
| GJAHR (fiscal_year) |
| BLDAT (document_date) |
| BUDAT (posting_date) |
| BLART (document_type) |
| XBLNR (external_reference) |
| USNAM (posted_by_user) |
| TCODE (transaction_code) |
+----------------------------+
|
| (Join on document_number, company_code, fiscal_year, line_item)
v
+----------------------------+
| BSEG |
|----------------------------|
| BELNR (document_number) |
| BUKRS (company_code) |
| GJAHR (fiscal_year) |
| BUZEI (line_item) |
| HKONT (gl_account) |
| DMBTR (amount) |
| WRBTR (amount_local) |
| SHKZG (debit_credit_indicator) |
| SGTXT (line_item_text) |
| KOSTL (cost_center) |
| PRCTR (profit_center) |
+----------------------------+

+------------------+
| BKPF | <------+
|------------------| |
| BELNR (Doc Num) | |
| BUKRS (Co Code) | |
| GJAHR (Year) | |
| BLDAT, BUDAT | |
| XBLNR, USNAM | |
+------------------+ |
|
|
+--------+ +--------+ +--------+ +--------+
| BSID | | BSAD | | BSIK | | BSAK |
|--------| |--------| |--------| |--------|
| BELNR | | BELNR | | BELNR | | BELNR |
| BUKRS | | BUKRS | | BUKRS | | BUKRS |
| GJAHR | | GJAHR | | GJAHR | | GJAHR |
| BUZEI | | BUZEI | | BUZEI | | BUZEI |
| KUNNR | | KUNNR | | LIFNR | | LIFNR |
+--------+ +--------+ +--------+ +--------+
\ / \ /
\ / \ /
\ / \ /
\ / \ /
\ / \ /
\ / \ /
v v v v
+-----------------------------------+
| fact_financial_line_items |
|-----------------------------------|
| document_number (BELNR) |
| company_code (BUKRS) |
| fiscal_year (GJAHR) |
| line_item (BUZEI) |
| party_id (KUNNR/LIFNR) |
| party_type |
| status ('Open'/'Cleared') |
| amount, gl_account, currency |
| posting_date, clearing_date |
| aging_bucket, reference |
+-----------------------------------+
|
|
v
+-------------+
| BSEG |
|-------------|
| BELNR |
| BUKRS |
| GJAHR |
| BUZEI |
| HKONT |
| SHKZG |
| SGTXT |
| PRCTR |
+-------------+

BSID, BSAD, BSIK, BSAK: These tables serve as secondary indexes for customer and
vendor line items. They provide a more accessible view of open and cleared items
compared to the cluster table BSEG.

BSID: Contains open customer items.

BSAD: Contains cleared customer items.

BSIK: Contains open vendor items.

BSAK: Contains cleared vendor items.

BKPF: The document header table that contains metadata for accounting documents,
such as the document number (BELNR), company code (BUKRS), fiscal year (GJAHR), and
others.

BSEG: The document line item table that contains detailed information for each
accounting document line, including general ledger account (HKONT), amount (DMBTR),
and cost center (KOSTL).

fact_financial_line_items: Your data warehouse's central fact table that


consolidates data from BSID, BSAD, BSIK, BSAK, BKPF, and BSEG to provide a unified
view of financial transactions.

Source Table Target Table Join Keys


BSID BKPF BELNR, BUKRS, GJAHR
BSAD BKPF BELNR, BUKRS, GJAHR
BSIK BKPF BELNR, BUKRS, GJAHR
BSAK BKPF BELNR, BUKRS, GJAHR
BKPF BSEG BELNR, BUKRS, GJAHR
BSEG fact_financial_line_items document_number, company_code, fiscal_year,
line_item

You might also like