amazon redshift – Stored Procedure not returning any data but executed successfully

Below stored procedure executing sucessfully but not returning any data, could you please help me in addressing the issue

CREATE OR REPLACE PROCEDURE fact.build_poh_fact(p_days_back integer, p_days_to integer) LANGUAGE plpgsql AS $$ DECLARE l_days_back INT; l_begin_ts TIMESTAMP; l_days_to INT; l_end_ts TIMESTAMP; l_update_ts TIMESTAMP;

BEGIN
         if p_days_back IS NULL THEN
            l_days_back := 7;
         ELSE
            l_days_back := p_days_back;
         END if;
         if p_days_to IS NULL THEN
            l_days_to := 1;
         ELSE
            l_days_to := p_days_to;
         END if;
         -- Variable Initialization
         l_begin_ts := date_trunc('day', date_add('day', -1 * l_days_back, current_date));
         l_end_ts := date_add('microsecond', -1, date_trunc('day', date_add('day', -1 * (l_days_to - 1) , current_date)));
         l_update_ts := current_timestamp;  


drop table if exists tmp_bt_bd_lines;        
create temp table tmp_bt_bd_lines 
as select bl.bt_id as bt_id,
btf.line_id as line_id,
bh.ghxordernumber as ghx_order_number,
bh.ponumber as po_number,
bh.currency as currency,
bh.sendereid as buyer_eid,
bh.receivereid as supplier_eid,
bl.supplierpartnumber as supplier_item_code,
bl.uom as uom,
bh.billtoeid as bill_to_eid,
bh.shiptoeid as ship_to_eid,
bh.status,
btf.confirmation_number as confirmation_number,
bl.facility_id as buyer_id,
bh.potype as po_type,
bl.extendedprice as po_amount,
btf.invoice_amount as invoice_amount,
bh.totaltaxes as tax_amount,
btf.charge_amount as charge_amount,
--bl.receivedfrompartnerts as po_date,
bh.receivedfrompartnerts as po_date,
btf.latest_response_date as latest_response_date,
btf.invoice_received_date as invoice_received_date,
bh.createdts as source_header_created_date,
bh.updatedts as source_header_updated_date,
bh.totallinescount as line_total_count,
btf.line_ordered_count as line_ordered_count,
btf.line_invoiced_count as line_invoiced_count,
btf.line_invoice_pending_count as line_invoice_pending_count,
btf.exception_total_count as exception_total_count,
btf.exception_open_count as exception_open_count,
btf.exception_invoice_count as exception_invoice_count,
bl.doc_id as order_doc_id, 
bl.linenumber as line_number,
bl.quantity as latest_quantity, --bl.received_quantity as latest_quantity,
bl.unitprice as unit_price,
bl.extendedprice as extended_price,
bl.line_status as line_status,
btf.ordered_count as ordered_count,
btf.filled_count as filled_count,
btf.shipped_count as shipped_count,
btf.invoiced_count as invoiced_count,
btf.channel_in as channel_in,
btf.channel_out as channel_out,
bl.facility_id,
bl.vendor_id as vendor_id,
bl.manufacturer_id as manufacturer_id,
btf.supplied_unit_price,
bl.doc_id as business_document_id,
bl.lineid as doc_line_id,
bl.buyerpartnumber,
bl.manufacturerpartnumber,
btf.has_po
bh.receiver as vendor_name,
bl.quantity_of_each as quantity_of_each
from corex_stage.bd_poh_header bh 
join corex_stage.bd_poh_lines bl
on bh.doc_id=bl.doc_id
left join corex_stage.bt_to_bd_lines_bridge bridge
       on bl.doc_id=bridge.business_document_id
       and bl.lineid=bridge.line_id
       and doc_sub_type="PO"
left join fact.business_transaction_fact btf 
       on bl.bt_id=btf.bt_id
      and bridge.btl_line_id=btf.line_id
      and btf.has_poh=true
where bh.receivedfrompartnerts >= '2018-01-01'::DATE
      AND bh.receivedfrompartnerts between l_begin_ts and l_end_ts
;

DROP TABLE IF EXISTS  bt_line_deduplicate_temp;
CREATE TEMP TABLE  bt_line_deduplicate_temp distkey(supplier_eid) sortkey(supplier_eid,supplier_item_code,uom)
AS
SELECT bt_id,
line_id,
ghx_order_number,
po_number,
currency,
buyer_eid,
supplier_eid,
supplier_item_code,
uom,
bill_to_eid,
ship_to_eid,
status,
confirmation_number,
buyer_id,
po_type,
po_amount,
invoice_amount,
tax_amount,
charge_amount,
po_date,
latest_response_date,
invoice_received_date,
source_header_created_date,
source_header_updated_date,
line_total_count,
line_ordered_count,
line_invoiced_count,
line_invoice_pending_count,
exception_total_count,
exception_open_count,
exception_invoice_count,
order_doc_id,
line_number,
latest_quantity,
unit_price,
extended_price,
line_status,
ordered_count,
filled_count,
shipped_count,
invoiced_count,
channel_in,
channel_out,
facility_id,
vendor_id,
manufacturer_id,
supplied_unit_price,
business_document_id,
doc_line_id,
buyerpartnumber,
manufacturerpartnumber,
has_po,
vendor_name,
quantity_of_each
            FROM (SELECT bt_id,
line_id,
ghx_order_number,
po_number,
currency,
buyer_eid,
supplier_eid,
supplier_item_code,
uom,
bill_to_eid,
ship_to_eid,
status,
confirmation_number,
buyer_id,
po_type,
po_amount,
invoice_amount,
tax_amount,
charge_amount,
po_date,
latest_response_date,
invoice_received_date,
source_header_created_date,
source_header_updated_date,
line_total_count,
line_ordered_count,
line_invoiced_count,
line_invoice_pending_count,
exception_total_count,
exception_open_count,
exception_invoice_count,
order_doc_id,
line_number,
latest_quantity,
unit_price,
extended_price,
line_status,
ordered_count,
filled_count,
shipped_count,
invoiced_count,
channel_in,
channel_out,
facility_id,
vendor_id,
manufacturer_id,
supplied_unit_price,
business_document_id,
doc_line_id,
buyerpartnumber,
manufacturerpartnumber,
has_po,
pg_catalog.row_number() OVER (PARTITION BY bt_id,doc_line_id ORDER BY line_invoiced_count DESC) r_num,
vendor_name,
quantity_of_each
FROM tmp_bt_bd_lines) a
WHERE r_num = 1; 
            
                                                                      
----connect BT with supplier_item_dim table 
DROP TABLE IF EXISTS  bt_item_temp;

            CREATE TEMP TABLE  bt_item_temp sortkey 
            (
              supplier_eid,
              supplier_item_code,
              uom            
            )
            AS
            SELECT bt_id,
line_id,
ghx_order_number,
po_number,
currency,
buyer_eid,
bt.supplier_eid,
bt.supplier_item_code,
bt.uom,
bill_to_eid,
ship_to_eid,
status,
confirmation_number,
buyer_id,
po_type,
po_amount,
invoice_amount,
tax_amount,
charge_amount,
po_date,
latest_response_date,
invoice_received_date,
source_header_created_date,
source_header_updated_date,
line_total_count,
line_ordered_count,
line_invoiced_count,
line_invoice_pending_count,
exception_total_count,
exception_open_count,
exception_invoice_count,
order_doc_id,
line_number,
latest_quantity,
unit_price,
extended_price,
line_status,
ordered_count,
filled_count,
shipped_count,
invoiced_count,
channel_in,
channel_out,
facility_id,
vendor_id,
manufacturer_id,
supplied_unit_price,
business_document_id,
doc_line_id,
buyerpartnumber,
manufacturerpartnumber,
has_po,
item_dim.supplier_item_key,
vendor_name,
quantity_of_each
FROM  bt_line_deduplicate_temp bt
LEFT JOIN   dim.supplier_item_dim item_dim
ON item_dim.supplier_eid = bt.supplier_eid
AND item_dim.supplier_item_code = bt.supplier_item_code
AND item_dim.uom = bt.uom  ;                                                                          
   
                                                                           
-----------------Connect bt_dim and ccx lines 

            DROP TABLE IF EXISTS  bt_item_ccx_temp;

            CREATE TEMP TABLE  bt_item_ccx_temp sortkey 
            (
              supplier_eid,
              supplier_item_code,
              uom 
            )
            AS
            SELECT  bt.bt_id,
                   bt.line_id,
                   bt.ghx_order_number,
                   bt.po_number,
                   bt.currency,
                   bt.buyer_eid,
                   bt.supplier_eid,
                   bt.supplier_item_code,
                   bt.uom,
                   bt.bill_to_eid,
                   bt.ship_to_eid,
                   bt.status,
                   bt.confirmation_number,
                   bt.buyer_id,
                   bt.po_type,
                   bt.po_amount,
                   bt.invoice_amount,
                   bt.tax_amount,
                   bt.charge_amount,
                   bt.po_date,
                   bt.latest_response_date,
                   bt.invoice_received_date,
                   bt.source_header_created_date,
                   bt.source_header_updated_date,
                   bt.line_total_count,
                   bt.line_ordered_count,
                   bt.line_invoiced_count,
                   bt.line_invoice_pending_count,
                   bt.exception_total_count,
                   bt.exception_open_count,
                   bt.exception_invoice_count,
                   bt.order_doc_id,
                   bt.line_number,
                   supplier_item_key,
                   bt.latest_quantity,
                   bt.unit_price,
                   bt.extended_price,
                   bt.line_status,
                   bt.ordered_count,
                   bt.filled_count,
                   bt.shipped_count,
                   bt.invoiced_count,
                   bt.has_po,
                   bt.facility_id,
                   bt.vendor_id,
                   ccxl.contract_partnumber as contract_item_code,
                   --ccxl contract_id,
                   --contract_source
                   ccxl.contract_number AS contract_number,
                   ccxl.contract_type AS contract_type,
                   ccxl.contract_price AS contract_price,
                   ccxl.contract_calculatedprice AS contract_calculated_price,
                   ccxl.contract_uom AS contract_uom,
                   --contract_start_date
                   ccxl.contract_expirationdate AS contract_expiration_date,
                   ccxl.contract_itemmanufacturername AS contract_item_manufacturer_name,
                   ccxl.contract_contractqoe AS contract_qoe,
                   ccxl.contract_org_id AS contract_organization_id,
                   --as contract_owner,
                   bt.channel_in,
                   bt.channel_out,
                   bt.manufacturer_id,
                   bt.supplied_unit_price,
                   bt.business_document_id,
                   bt.doc_line_id,
                   bt.buyerpartnumber,
                   bt.manufacturerpartnumber,
                   bt.vendor_name,
                   bt.quantity_of_each
            FROM  bt_item_temp bt
              LEFT JOIN corex_stage.bt_ccx_lines ccxl
                     ON ccxl.bt_id = bt.bt_id
                    AND ccxl.btl_line_id = bt.line_id;
                                                                            
-----------Delete the updates 
             DELETE FROM fact.poh_fact
             USING  bt_item_ccx_temp temp
             WHERE 
             fact.poh_fact.bt_id=temp.bt_id         
             AND  fact.poh_fact.document_line_id = temp.doc_line_id;                                                                            
   
------ Insert the updates 
INSERT INTO fact.poh_fact
(
  bt_id,
  ghx_order_number,
  po_number,
  currency,
  buyer_eid,
  supplier_eid,
  manufacturer_eid,
  bill_to_eid,
  ship_to_eid,
  status,
  confirmation_number,
  buyer_id,
  po_type,
  po_amount,
  invoice_amount,
  tax_amount,
  charge_amount,
  po_date,
  latest_response_date,
  invoice_received_date,
  source_header_created_date,
  source_header_updated_date,
  line_total_count,
  line_ordered_count,
  line_invoiced_count,
  line_invoice_pending_count,
  exception_total_count,
  exception_open_count,
  exception_invoice_count,
  order_doc_id,
  line_id,
  line_number,
  supplier_item_key,
  latest_quantity,
  unit_price,
  extended_price,
  line_status,
  ordered_count,
  filled_count,
  shipped_count,
  invoiced_count,
  contract_item_code,
  contract_id,
  contract_source,
  contract_number,
  contract_type,
  contract_price,
  contract_calculated_price,
  contract_uom,
  contract_start_date,
  contract_expiration_date,
  contract_item_manufacturer_name,
  contract_qoe,
  contract_organization_id,
  contract_owner,
  channel_in,
  channel_out,
  dw_created_user,
  dw_created_timestamp,
  dw_updated_user,
  dw_updated_timestamp,
  has_po,
  facility_id,
  vendor_id,
  manufacturer_id,
  supplied_unit_price,
  business_document_id,
  document_line_id,
  buyer_part_number,
  manufacturer_part_number,
  vendor_name,
  quantity_of_each
)
SELECT DISTINCT bt_id,
       ghx_order_number,
       po_number,
       currency,
       buyer_eid,
       supplier_eid,
       NULL as manufacturer_eid,
       bill_to_eid,
       ship_to_eid,
       status,
       confirmation_number,
       buyer_id,
       po_type,
       po_amount,
       invoice_amount,
       tax_amount,
       charge_amount,
       po_date,
       latest_response_date,
       invoice_received_date,
       source_header_created_date,
       source_header_updated_date,
       line_total_count,
       line_ordered_count,
       line_invoiced_count,
       line_invoice_pending_count,
       exception_total_count,
       exception_open_count,
       exception_invoice_count,
       order_doc_id,
       line_id,
       line_number,
       supplier_item_key,
       latest_quantity,
       unit_price,
       extended_price,
       line_status,
       ordered_count,
       filled_count,
       shipped_count,
       invoiced_count,
       contract_item_code,
       NULL as contract_id,
       NULL as contract_source,
       contract_number,
       contract_type,
       contract_price,
       contract_calculated_price,
       contract_uom,
       CAST(NULL AS TIMESTAMP) as contract_start_date,
       contract_expiration_date,
       contract_item_manufacturer_name,
       contract_qoe,
       contract_organization_id,
       NULL as contract_owner,
       channel_in,
       channel_out,
       CURRENT_USER AS dw_created_user,
       getdate() AS dw_created_timestamp,
       CURRENT_USER AS dw_updated_user,
       getdate() AS dw_updated_timestamp,
       has_po,
       facility_id,
       vendor_id,
       manufacturer_id,
       supplied_unit_price,
       business_document_id,
       doc_line_id,
       buyerpartnumber,
       manufacturerpartnumber
       vendor_name,
       quantity_of_each
FROM bt_item_ccx_temp ;
                                                                                                
EXCEPTION
   WHEN others THEN
      RAISE INFO 'error message SQLERRM %', SQLERRM;
      RAISE INFO 'error message SQLSTATE %', SQLSTATE;

END;

$$

Result:

SELECT executed successfully

Execution time: 31.57s

SELECT executed successfully

Execution time: 0.79s

SELECT executed successfully

Execution time: 0.71s

SELECT executed successfully

Execution time: 0.74s

0 rows affected call executed successfully

Execution time: 26m 43s

Leave a Comment