line status code

Line status code

DROP TABLE if EXISTS sandbox.line_status
 SELECT CASE
                               WHEN sales_doc_billing_code IS NOT NULL
                                   AND sales_doc_billing_invoice_type_name = 'Rechnung'
                                   AND billing_doc_last_change_date_local <= '2020-03-09'
                                   THEN 'invoiced'
                               WHEN sales_doc_cancellation_confirmed_reason_description IS NOT NULL
                                   OR (sales_doc_cancellation_requested_reason_type != ''
                                        AND sales_doc_cancellation_date_local <= '2020-03-09')
                                   OR (sales_doc_cancellation_requested_reason_type != ''
                                        AND sales_doc_item_category in  ('YOBF','YOBP','YPKO','YPKP','YSKO','YSKP','ZKBN','ZSKK'))
                                   THEN 'cancelled'
                               WHEN sales_doc_delivery_type IS NOT NULL
                                   AND sales_doc_delivery_shipped_qty >= 1
                                   AND sales_doc_delivery_proof_of_delivery_date_local IS NOT NULL
                                   AND sales_doc_delivery_proof_of_delivery_date_local <= '2020-03-09'
                                   THEN 'delivered'
                               WHEN purchase_doc_billing_type = 'E'
                                   AND purchase_doc_movement_type = 101
                                   AND purchase_doc_booking_date_local <= '2020-03-09'
                                   THEN 'delivered'
                               WHEN sales_doc_delivery_warehouse_goods_movement_date_local_actual IS NOT NULL
                                   AND sales_doc_delivery_type IS NOT NULL
                                   AND sales_doc_delivery_shipped_qty >= 1
                                   AND sales_doc_delivery_last_change_date_local <= '2020-03-09'
                                   THEN 'shipped'
                               WHEN sales_doc_delivery_type IS NOT NULL
                                   AND sales_doc_delivery_shipped_qty >= 1
                                   AND sales_doc_delivery_last_change_date_local <= '2020-03-09'
                               THEN 'delivery_created'
                               WHEN sales_doc_delivery_block_type != ''
                                   THEN 'unreleased'
                               ELSE 'disposition'
                               END    AS line_status,
                           sales_doc_code,
                           sales_doc_item_position,
                           '2020-03-09'::DATE AS period_date_local into sandbox.line_status
                      FROM star_summary.f_fulfilment_latest;
 
 truncate table star_ods.sales_doc_line_status;
                      
                      insert into star_ods.sales_doc_line_status
select b.line_status_mapping_evalutation_sequence,
       a.sales_doc_code,
       a.sales_doc_item_position,
       a.period_date_local
from sandbox.line_status a
inner join star_ods.line_status_mapping b on a.line_status = b.line_status_mapping_description