8/12/2019 795 Bom Instructions

1/2

Create a new table

1. Use GUI client such as Oracle SQL Developer to connect to your source DB using appslogin

2. Run this SQL

CR!" ta#le BO$%&'LOSIO(%"$'%OBI! as select ) *ro+BO$%&'LOSIO(%"$',

Create a new stored procedure

-. (avigate to 'acages an/ !pply 0ilter OBC"%(!$ BO$'&'L

3. Right Clic on BO$'&'L 4 5port DLL 4 Save to 0ile BO$'&'L%OBI!.SQL

6. Open BO$'&'L%OBI!.SQL

Replace BO$%&'LOSIO(%"$' as BO$%&'LOSIO(%"$'%OBI!Replace BO$'&'L as BO$'&'L%OBI!

7. 0or your convenience8 the +o/i*ie/ *ile *or version 11i 19cu2 is attache/ here.

BOMPEXPL_OBIA.sql

:. 5ecute the SQL BO$'&'L%OBI!.SQL

(O"; BS R12 custo+er nee/s to copy the S' *ro+ BS instea/ o* using the attache/ SQL

/irectly as its 11i speci*ic < so i* you *ollo= step - 3 6 a#ove you /on>t nee/ to use step 7

Modify the Informatica Objects

?. Connect to your In*or+atica 'o=erCenter Designer. Open your SD *ol/er8 e.g.

SD%OR!11619%!/aptor@. 5pense Sources 4 OL"'8 chec out Aclic +enu ersioning 4 chec out ta#le

BO$%&'LOSIO(%"$' an/ rena+e it as BO$%&'LOSIO(%"$'%OBI!.

19. Chec out an/ open +apping SD%OR!%BO$Ite+0act. /it trans*or+ation

S'%BO$'&'L%&'LODR%USR&I". Go to 'roperties "a#. Change Store/ 'roce/ure(a+e *ro+ #o+pe5pl.e5plo/er%usere5it to #o+pe5pl%OBI!.e5plo/er%usere5it

11. Right Clic on $applet +plt%BC%OR!%BO$Ite+0act an/ select Open $applet. "hen chec it

out. Open trans*or+ation SQ%BO$%&'LOSIO(%"$'. Go to the 'roperties "a# an/ e/it the

Sl Query. In the SQL replace all BO$%&'LOSIO(%"$' asBO$%&'LOSIO(%"$'%OBI!

12. Checout an/ Open $applet +plt%BC%OR!%BO$Eea/erDi+ension8 open trans*or+ationSQ%BO$%I(("ORF%CO$'O(("S an/ then the 'roperties "a#. nter this in the 're

SQL;

"RU(C!" "!BL BO$%&'LOSIO(%"$'%OBI!,1-. Save an/ chec in the all o#ects

Result

13. !*ter e5ecution8 you shoul/ see /ata co+ing to H%BO$%I"$%0S in the =arehouse. Foushoul/ also see /ata in BO$%&'LOSIO(%"$'%OBI! in the OL"'. In a//ition8 this ta#le

shoul/ not have accu+ulate/ /ata8 #ecause =e have a truncate state+ent to han/le the clean upeach run.

Unit Test and Validation

16. Connect to the Data Harehouse. truncate =%#o+%hea/er%/s an/ =%#o+%ite+%*s17. 5ecute SD%OR!%BO$Eea/erDi+ension

1:. 5ecute SD%OR!%BO$Ite+0act an/ vali/ate the result

1?. Connect to the OL"' an/ count A) *ro+ BO$%&'LOSIO(%"$'%OBI!.

1@. Repeat 161?. "he ne= count shoul/ #e the sa+e as the previous ti+e8 not /ou#le the nu+#er.

8/12/2019 795 Bom Instructions

2/2

Also In DAC

1. In the top /rop /o=n8 select your container an/ navigate to Design 4 "ass.

2. Query *or (a+e SD%OR!%BO$Ite+0act

-. In the chil/ tap JSource "a#lesK8 change BO$%&'LOSIO(%"$' toBO$%&'LOSIO(%"$'%OBI!

Save.