[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1557
  • Last Modified:

oracle procedure

could you pls decode it simple words 1 thru 20 ..because I dont understand..I understand after that.

1.      CREATE OR REPLACE PROCEDURE LOAD_FID_ORDER_DETAIL_F (gl_period varchar2)
2.      AS
3.                     process_name        varchar2(30);
4.                     result              varchar2(20);
5.                     message             varchar2(255) := null;
6.                     records_deleted     number := 0;
7.                      records_inserted    number := 0;
8.                        run_start           date;
9.                       run_finish          date;
10.                  c9                  NUMBER;        
11.                    ret                 NUMBER;
12.                   strIndex            VARCHAR2(256);
13.                     max_update_date     date;

14.      begin

15.      select 'LOAD_FID_ORDER_DETAIL_F' into process_name from                               dual;

16.      select sysdate into run_start from dual;

17.      select nvl(max(lines_last_upd_dt),'01-JAN-1900') into max_update_date
18.      from FID_order_detail_f
19.      where header_id is not null;

20.      dssmart_truncate('FID_TEMP_ORDER_DETAIL_F');


    I don’t understand (1-20)
==========================



    I understand
=================
    -- Insert new or modified orders into temp table.
    INSERT INTO FID_TEMP_ORDER_DETAIL_F
    (ORDER_NUMBER,
    CANCELLED_QUANTITY,
    CANCEL_CODE,
    CHANGE_DATE,
    CURRENCY,
    CUSTOMER_ID,
    DATE_ORDERED,
    DATE_REQUESTED_CURRENT,
    HEADER_ID,
    market_code,
    INVENTORY_ITEM_ID,
    LINE_ID,
    line_prefix,
    LINE_NUMBER,
    OPERATING_UNIT_ID,
    ORDERED_QUANTITY,
    ORDER_TYPE_ID,
    PAYMENT_TERM_ID,
    PO_NUMBER,
    PROMISE_DATE,
    SALE_CHANNEL_CODE,
    SCHEDULE_SHIP_DATE,
    SHIPPED_QUANTITY,
    UNIT_LIST_PRICE,
    UNIT_SELLING_PRICE,
    WAREHOUSE_ID,
    ordered_by_contact_id,
    --ship_to_site_use_id,
    ship_to_address_id,
    ship_to_contact_id,
    --invoice_to_site_use_id,
    invoice_to_address_id,
    invoice_to_contact_id,
    payment_type_code,
    payment_amount,
    headers_last_upd_dt,
    lines_last_upd_dt
    )
    SELECT
    SO_HEADERS_ALL.ORDER_NUMBER,
    nvl(SO_LINES_ALL.CANCELLED_QUANTITY,0),
    'N/A',
    SO_LINES_ALL.LAST_UPDATE_DATE,
    NVL(SO_HEADERS_ALL.CURRENCY_CODE,'USD'),
    nvl(SO_HEADERS_ALL.CUSTOMER_ID,'9999999999'),
    TRUNC(SO_HEADERS_ALL.DATE_ORDERED),
    SO_LINES_ALL.DATE_REQUESTED_CURRENT,
    SO_HEADERS_ALL.HEADER_ID,
    SO_HEADERS_ALL.ATTRIBUTE1,
    SO_LINES_ALL.INVENTORY_ITEM_ID,
    --MTL_SYSTEM_ITEMS.SEGMENT1,  Changed from FID Item number to Oracle Item Number
    SO_LINES_ALL.LINE_ID,
    SO_LINES_ALL.ATTRIBUTE1,
    SO_LINES_ALL.LINE_NUMBER,
    SO_LINES_ALL.ORG_ID,
    SO_LINES_ALL.ORDERED_QUANTITY,
    SO_HEADERS_ALL.ORDER_TYPE_ID,
    NVL(SO_HEADERS_ALL.TERMS_ID,999999),
    SO_HEADERS_ALL.PURCHASE_ORDER_NUM,
    TRUNC(SO_LINES_ALL.PROMISE_DATE),
    SO_HEADERS_ALL.SALES_CHANNEL_CODE,
    SO_LINES_ALL.SCHEDULE_DATE,
    NVL(SO_LINES_ALL.SHIPPED_QUANTITY,0),
    SO_LINES_ALL.LIST_PRICE,
    NVL(SO_LINES_ALL.SELLING_PRICE,0),
    SO_LINES_ALL.WAREHOUSE_ID,
    nvl(SO_HEADERS_ALL.ORDERED_BY_CONTACT_ID,'9999999999'),
--    nvl(SO_HEADERS_ALL.SHIP_TO_SITE_USE_ID,'9999999999'),     Replaced with address_key
    nvl(ship_to.address_id, '9999999999'),
    nvl(SO_HEADERS_ALL.SHIP_TO_CONTACT_ID,'9999999999'),
--    nvl(SO_HEADERS_ALL.INVOICE_TO_SITE_USE_ID,'9999999999'),  Replaced with address_key
    nvl(bill_to.address_id, '9999999999'),
    nvl(SO_HEADERS_ALL.INVOICE_TO_CONTACT_ID,'9999999999'),
    SO_HEADERS_ALL.PAYMENT_TYPE_CODE,
    SO_HEADERS_ALL.PAYMENT_AMOUNT,
    SO_HEADERS_ALL.LAST_UPDATE_DATE,
    SO_LINES_ALL.LAST_UPDATE_DATE
    FROM
    INV.MTL_CATEGORIES@PROD.WORLD MTL_CATEGORIES,           -- Changed to PROD from TEST - d.baker 7-30-2001
    INV.MTL_ITEM_CATEGORIES@PROD.WORLD MTL_ITEM_CATEGORIES, -- Changed to PROD from TEST - d.baker 7-30-2001
    INV.MTL_SYSTEM_ITEMS@PROD.WORLD MTL_SYSTEM_ITEMS,       -- Changed to PROD from TEST - d.baker 7-30-2001
    OE.SO_LINES_ALL@PROD.WORLD SO_LINES_ALL,                -- Changed to PROD from TEST - d.baker 7-30-2001
    OE.SO_HEADERS_ALL@PROD.WORLD SO_HEADERS_ALL,            -- Changed to PROD from TEST - d.baker 7-30-2001
    ar.ra_site_uses_all@PROD.WORLD ship_to,                 -- Changed to PROD from TEST - d.baker 7-30-2001
    ar.ra_site_uses_all@PROD.WORLD bill_to                  -- Changed to PROD from TEST - d.baker 7-30-2001
    WHERE
    SO_LINES_ALL.INVENTORY_ITEM_ID = MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID(+)
    AND SO_LINES_ALL.WAREHOUSE_ID = MTL_SYSTEM_ITEMS.ORGANIZATION_ID(+)
    AND SO_HEADERS_ALL.ORDER_CATEGORY IN ('R','P')
    AND SO_LINES_ALL.INVENTORY_ITEM_ID = MTL_ITEM_CATEGORIES.INVENTORY_ITEM_ID(+)
    AND SO_LINES_ALL.WAREHOUSE_ID =  MTL_SYSTEM_ITEMS.ORGANIZATION_ID(+)
    AND MTL_ITEM_CATEGORIES.CATEGORY_ID = MTL_CATEGORIES.CATEGORY_ID (+)
    AND MTL_ITEM_CATEGORIES.CATEGORY_SET_ID (+) = 1
    AND SO_HEADERS_ALL.HEADER_ID = SO_LINES_ALL.HEADER_ID
      AND SO_HEADERS_ALL.ORG_ID = 221
    --AND SO_HEADERS_ALL.DATE_ORDERED >= TO_DATE('06-FEB-2001','DD-MON-YYYY')
    --AND SO_HEADERS_ALL.DATE_ORDERED < (TO_DATE('07-FEB-2001','DD-MON-YYYY') + 1)
    AND SO_LINES_ALL.WAREHOUSE_ID = MTL_ITEM_CATEGORIES.ORGANIZATION_ID(+)
    and so_headers_all.ship_to_site_use_id = ship_to.site_use_id(+)
    and so_headers_all.invoice_to_site_use_id = bill_to.site_use_id(+)
    and trunc(so_lines_all.last_update_date) >= trunc(max_update_date)
    and SO_LINES_ALL.line_type_code <> 'DETAIL'
    ;

    commit;
I understand
==============
    -- Delete modified orders from existing order detail fact table.
    delete from FID_order_detail_f
    where header_id is not null     -- This avoids the legacy records.
    and header_id || line_id in (select header_id || line_id from FID_temp_order_detail_f)
    ;

    commit;


    -- Insert new or modified orders into order detail fact table.
    insert into FID_order_detail_f
    select * from FID_temp_order_detail_f;

    commit;

    -- Count the number of records inserted.
    select count(*) into records_inserted
    from FID_temp_order_detail_f;




    select sysdate into run_finish from dual;

    select 'SUCCESS' into result from dual;

    insert into FID_batch_log values (FID_batch_seq.nextval, gl_period,
        process_name, result, message, 0, records_inserted, run_start, run_finish);

    commit;

exception when others then

    select 'ERROR' into result from dual;

    message := sqlerrm;

    insert into FID_batch_log values (FID_batch_seq.nextval, gl_period,
        process_name, result, message, records_deleted, 0, run_start, run_finish);

    commit;

END;
/

0
eileenbanik
Asked:
eileenbanik
  • 2
1 Solution
 
geotigerCommented:
Here it it:

-- start creating or replacing the procedure  LOAD_FID_ORDER_DETAIL_F with one input variable gl_period
1.     CREATE OR REPLACE PROCEDURE LOAD_FID_ORDER_DETAIL_F (gl_period varchar2)
-- AS the following
2.     AS
-- Variable declaration block 3-13
3.                    process_name        varchar2(30);
4.                    result              varchar2(20);
5.                    message             varchar2(255) := null;
6.                    records_deleted     number := 0;
7.                     records_inserted    number := 0;
8.                       run_start           date;
9.                      run_finish          date;
10.                 c9                  NUMBER;        
11.                   ret                 NUMBER;
12.                  strIndex            VARCHAR2(256);
13.                    max_update_date     date;
-- begin the procedure block
14.     begin
-- Save the LOAD_FID_ORDER_DETAIL_F procedure name in table  process_name
15.     select 'LOAD_FID_ORDER_DETAIL_F' into process_name from                               dual;
-- Save current date and time to table  run_start
16.     select sysdate into run_start from dual;
-- Save the most recent update date into table max_update_date
17.     select nvl(max(lines_last_upd_dt),'01-JAN-1900') into max_update_date
18.     from FID_order_detail_f
19.     where header_id is not null;
-- run the dssmart_truncate procedure
20.     dssmart_truncate('FID_TEMP_ORDER_DETAIL_F');
0
 
geotigerCommented:

-- Insert word 'ERROR' into result table
  select 'ERROR' into result from dual;
-- Assign SQL error message to variable message
    message := sqlerrm;
-- insert a new record into table FID_batch_log
    insert into FID_batch_log values (FID_batch_seq.nextval, gl_period,
        process_name, result, message, records_deleted, 0, run_start, run_finish);
-- Commit the change/insertion
    commit;
0
 
Mark GeerlingsDatabase AdministratorCommented:
Line 1 is just the name of the procedure and the name of the one input variable, which must be a character (varchar2) value.

Lines 3 - 13 just define some variables that will be used in the procedure.

Line 14 (begin) indicates start of the actual executable statements.

Lines 15 and 16 are legal, but very innefficient.  They just assign values to two variables.  They should be:
 process_name :=  'LOAD_FID_ORDER_DETAIL_F';
 run_start := sysdate;

Lines 17-19 get the latest update date (or a default value of Jan. 1, 1900 if no records are found) from the table:  FID_order_detail_f for records that have a non-blank header_id.

Line 20 executes a different procedure named "dssmart_truncate" and passes it the value 'FID_TEMP_ORDER_DETAIL_F'.  I would guess that that procedure truncates (that is, quickly deletes) the records from that table: FID_TEMP_ORDER_DETAIL_F.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now