How To Load data In Staging Table

Interface is used to transfer data from Flat file to Temporary tables.
interface creation.........................
1)create csv file using the xls file
2)create ctl file
3) create New table in given instance or in database
4)then ccreate concurrent program with name as (sql loader program...as
concurrent program) to load
data from the csv to the database table


steps to remember remark..........

always give same name to all csv,ctl,table in apps,
concurreent program and all

move csv and ctl file in the bin of custom_top

always do the validation while loading data.................

always refer the ctl
always use the append mode to append the data in the table it does not
append the data  but it is replacing the data to the table................
modes
1)insert
if the table alredy contains the data....................
 then use the following options to load the data..................
2)append   APPEND REPLACE TRUNCATE
3)update
4)modify
give path of csv in ctl
and table name of oracle apps and the
all fields of table in oracle apps


and the ctll file is............


LOAD DATA
INFILE '/u03/DEV/devappl/custom_top/11.5.10/bin/Invoice_headers90.csv'
APPEND INTO TABLE INVOICE_HEADERS_STG90
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  INVOICE_AMOUNT,
  INVOICE_CURRENCY_CODE,
  PAYMENT_CURRENCY_CODE,
  INVOICE_DATE DATE 'MM-DD-YYYY HH24:MI:SS',
  INVOICE_NUM,
  INVOICE_TYPE_LOOKUP_CODE,
  PAYMENT_METHOD,
  TERMS_NAME,
  TERMS_DATE DATE 'MM-DD-YYYY HH24:MI:SS',
  VENDOR_NAME,
  VENDOR_SITE_CODE,
  INVOICE_HEADERS_STG90_ID "INVOICE_HEADERS_STG90_S.NEXTVAL"
 )


for ctl file......and interface information....

http://www.aboutoracleapps.com/2007/08/sqlloader-qns-what-is-sqlloader-ans.html
http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_use_the_SQL.2ALoader_utility.3F

imp file for ctl studyyy...............
---http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm

Forms Compilation in putty and how to modify template.fmb

........to compile  form file..................

Steps to create .fmx from .fmb using putty
1---------$ cd /u01/CLONE/cloneappl-------------path where we get the APPSORA.env file  instance changes path also changessss
2---------$ pwd
3---------$ ls *.env
4---------$ . APPSORA.env
5---------$  which f60gen
6---------$ cd /u01/CLONE/cloneappl/au/11.5.0/forms/US
7---------$ f60gen module=XXDIPFORM1.fmb userid=apps/apps output_file=/u01/CLONE/cloneappl/msftcust/11.5.10/forms/US/XXDIPFORM2.fmx


IMPP PATH TO STORE FMX FILE AFTER COMPILATION............
f60gen module=XX_DIP91.fmb userid=apps/apps output_file=/u03/DEV/devappl/msftcust/11.5.10/forms/US/XX_DIP91.fmx


WHAT TO SET FOR FORMS

impp.....move the fmb from desktop to the au top forms....imppppp....

before starting.............
1)remove predefine block,predefine canvas,predefine window of
 blockname from template form


1)always save the form name and name in the form builder with xx......and should be same.

1)CTREATE NEW WINDOW
  create new canvas
  create new block too
  add new items with property classes
2)SET CANVAS PREMARY TO THE WINDOW
3)SET WINDOW TO THE CANVAS
4)IN PRE-FORM TRIGGER SET THE primary WINDOW NAME 
5)SET PROPERTY CLASS OF EACH OBJECT OF FORMS
6)IMP ALWAYS SET THE BLOCKNAME TO THE FORMNAME .....VERY IMP SO FORM WILL DISPLAYYY................
 

Oracle Alerts registration and queryyy..................

1).........EVENT ALERT.............

1)create concurreent program in sysstem admin
2) check the execution. of program
3)select alert manager as resposibility
4) define theresposibility in human resourse as application name
5) first event alert
   1) tab
   2)select table form specific application for which u have to fire aleret
   3)write the queryyy assss follows

select full_name,
last_name,
date_of_birth,
nationality,
employee_number,
sex,
original_date_of_hire into &full_name1,
&last_name1,
&date_of_birth1,
&nationality1,
&employee_number1,
&sex1,
&original_date_of_hire1 from per_all_people_f where rowid=:rowid

click on verify and run the queryyy.....

6)give the action name and in action detail
                          1)specify action name as ...1)concurre=nyt program
                                                       2)message
                                                       3)sql script
                          2)application name
                           3)concurrent program name
                           4)argument to pass to concurrent program
7)specify the action set in action set define the action set details
define the member that u have previously define as action name
8)alert details in installation we have to specify the  apps-mspl
                                                        apps-msspl
9)and save the alert to fire...

10)check the out put in the view in request........all..

2).............PERIODIC ALERT................
        

NO MSPL AND MSSPL IN PERIODIC ALERT JUST GIVE THE QUERY TO RUN THE ALERT...

1)create concurreent program in sysstem admin
2) check the execution. of program
3)select alert manager as resposibility
4) define theresposibility in human resourse as application name
5) first PERIODIC alert
    1) SELECT WHEN U HAVE TO FIRE THE ALERT ACCORDING TO THE TYPE OF ALERTTTTT CHOOSE...
   1) WRITE QUERY IN THE GIVEN SECTION AS GIVEN BELOW

select full_name,
last_name,
date_of_birth,
nationality,
employee_number,
sex,
original_date_of_hire into &full_name1,
&last_name1,
&date_of_birth1,
&nationality1,
&employee_number1,
&sex1,
&original_date_of_hire1 from per_all_people_f where rowid=:rowid

click on verify and run the queryyy.....

6)give the action name and in action detail
                          1)specify action name as ...1)concurre=TyPE CONCURRENT  program NAME HERE
                                                       2)message
                                                       3)sql script
                          2)application name
                           3)concurrent program name
                           4)argument to pass to concurrent program
7)specify the action set in action set define the action set details
define the member that u have previously define as action name
8)IMP STEP TO REMBER.....NO NEED TO GIVE THE MSPL AND MSSPL
 IN CASE OF THE PERIODIC ALERT IT FIRE THE CON CURRENT PROGRAM FOR THE TWO TIMESSS
 no needdd  of mspl,.............
alert details in installation we have to specify the  apps-mspl
                                                        apps-msspl
9)and save the alert to fire...

10)check the out put in the view in request........all..

3)package for Alert..............
package specification ...............
CREATE OR REPLACE PACKAGE XX_TRAINNING_DIP90 AS
PROCEDURE XXSHOW_DATA90(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2);
END XX_TRAINNING_DIP90;
/


package boddyyyyy..................

CREATE OR REPLACE PACKAGE BODY XX_TRAINNING_DIP90 AS
PROCEDURE XXSHOW_DATA90(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
IS
CURSOR C1 IS select * from per_all_people_f where original_date_of_hire=trunc(sysdate) ;
DIP_PRODUCT1 per_all_people_f%ROWTYPE;
 BEGIN
  OPEN C1;
    Fnd_file.put_line(fnd_file.output,'Employess Details who updated his data todays..................');
    Fnd_file.put_line(fnd_file.output,'full_name ,Date_of_birth ,Nationality ,employee_number ,sex ,original_date_of_hire .');
   LOOP
    FETCH C1 INTO DIP_PRODUCT1;
          EXIT WHEN C1%NOTFOUND; 
     Fnd_file.put_line(fnd_file.output,DIP_PRODUCT1.full_name||'  , '||DIP_PRODUCT1.date_of_birth||'  , '||DIP_PRODUCT1.nationality||'  ,'||DIP_PRODUCT1.employee_number||'  ,'||DIP_PRODUCT1.sex||'  ,'||DIP_PRODUCT1.original_date_of_hire);
    END LOOP;
 CLOSE C1;
 END XXSHOW_DATA90;
 END XX_TRAINNING_DIP90;
/