asked on

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;
/

geotiger

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');

geotiger

-- 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;

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial