Hi All,
I have a business requirement, where I need to extract all the items from Oracle EBS and dump into a staging table. When the program is launched for the first time, I will load all the data into the staging table and the next runs need to load only the changes made on the item.
For example, on the first day, I will load all items and on second day somebody changes the item description, I need to update the record in the staging table. If someone creates a new item, then I need to insert the new record into the staging table.
I tried to provide the sample data that reflects the actual data.
| Item No | Item Desc | ATTR1 | ATTR2 | ATTR3 | Status Flag |
|---|
| ABC | ABC Desc | Attr 1 Desc - 01 | Attr 2 Desc - 01 | | |
| ABC | ABC Desc | Attr 1 Desc - 02 | Attr 2 Desc - 02 | | |
| DEF | DEF Desc | | | Attr 3 Desc - 01 | |
| DEF | DEF Desc | | | Attr 3 Desc - 01 | |
I cannot use the last update date to check for updates or insert because, the data doesn't come from one table. I have to compare each column for each item.
I have tried sql merge command, but it's not working in all cases
MERGE INTO xx_inv_item_tbl dst
USING (select msi.inventory_item_id id
,msi.segment1
,msi.description
,'TX' country
--,'NEW'
from mtl_system_items_b msi
where msi.organization_id = 123
and msi.segment1 = 'ABC') src
ON (src.id = dst.id)
WHEN MATCHED THEN
UPDATE
SET dst.country =
CASE
WHEN (dst.country != src.country)
THEN src.country
ELSE dst.country
END
,dst.status_flag =
CASE
WHEN (dst.country != src.country)
THEN 'NEW'
ELSE
'OLD'
END
I think I need to use pl/sql collections. Can anyone guide me how to proceed further and do it efficiently.
Any help is appreciated..
Thanks,
Srkanth