SQL * LOADER

SQL* Loader:-

           SQL* Loader is one of the Oracle tool which will be used to transfer data from flat file to Oracle database tables.

SQL* Loader Files:-  SQL * Loader files are 5 types. They were given below.

Flat File :-     Flat file contains the data in specific format. This format may be fixed length or ‘,’ separated or variable format. Extensions of the flat files are ‘.txt’,’.dat’,’.csv’ (comma separated view) or Excel sheet.

Control File :-          Control file is the SQL * Loader file or program it will be used to transfer data from file to table. It contains flat file path and table name and column mappings. Extension of control file is ‘.ctl’.

Syntax for Control file:-

           LOAD DATA

           INFILE ‘DATA FILE PATH’

           INSERT INTO TABLE <TABLE NAME>

FIELDS TERMINATED BY ‘,’

(COLUMN 1, COLUMN2)

Bad File :-     whenever we execute the control file bad file will be created automatically if SQL * Loader rejects the records. Extinction of bad file is ‘.bad’. Bad file will have the rejected records which are rejected by SQL * Loader. SQL * Loader will rejects the records if data is not incorrect format and if any internal error occurs.

Discard File :-          It contains the records, which are rejected by control file. Control file will rejects the records if we write any conditions system will check each record based whether it is satisfies the condition or not . If record is not satisfying the condition it will come into the discard file. Extension for discard file is ‘.dis’.

Log File :-     It will be created automatically after completion of control file execution process. It contains the information like successful records count and bad file discard file count and so on. Extension for log file is ‘.log’.

SQL * Loader Methods:-     There are 3 types of methods in SQL *Loader. They were given below

INSERT:-  In INSERT mode system will insert the data into the table but table should be empty.

APPEND:-  In APPEND mode system will add the new data for the existing data.

REPLACE:-  In REPLACE mode system will delete or remove the existing data and inserts the new data into the table.

SQL * Loader Command:-  After completing the control file we will execute the control file by using following command.

1.     Login to command prompt where the SQL *Loader was installed.

2.     sql ldr userid/password@database name press ENTER then

control = <control filename>

ex:-  c:\orant\bin>   sql ldr apps/apps@prod

      control  =  emp.ctl

1. Before going to work with SQL * Loader we should have to verify two  things
1. Data file
2. Table
2. Develop the control file as per the flat file and table format.
3. Go to the command prompt execute the control file by using following syntax.

Example:-

           Create table custom_dept (deptno number(3), dname varchar2(100), project varchar2(10), start date, end date)

Data file:-

           10-jan-2002,10-mar-2002,10,computers,sap

           10-aug-2000, 15-sep-2000,10,computers,sap

           10-mar-2002,15-mar-2002,20,finance,oracle

Control file:-

           LOAD DATE

           INFILE ‘C:/DEPT.TXT’

INSERT INTO TABLE CUSTOM_DEPT

FIELDS TERMINATED BY ‘,’

(STARTDATE,ENDDATE,DEPTNO,DNAME,PROJECT)

open the command prompt

   c:\orant\bin> sql ldr scott/tiger prod

           control= c:\emp.ctl

Example1:-

           Create table csv_emp (empno number(3), ename varchar2(100), joindate date, deptno number(3));

Control file:-

           LOAD DATE

           INFILE ‘C:/SLOADER\EMP_DETAILS.CSV’

           DISCARD FILE ‘C:\SLOADER\EMP_DETAILS.DIS’

INSERT INTO TABLE CSV_EMP

WHERE DEPTNO=’10’

FIELDS TERMINATED BY ‘,’

(STARTDATE,ENDDATE,DEPTNO,DNAME,PROJECT)

Trialing NULL Columns:-   We can use this syntax into the null columns to insert null values if data file is not having data.

Syntax:-

           LOAD DATE

           INFILE ‘C:/SLOADER\EMP_DETAILS.CSV’

           DISCARD FILE ‘C:\SLOADER\EMP_DETAILS.DIS’

INSERT INTO TABLE CSV_EMP

WHERE DEPTNO=’10’

FIELDS TERMINATED BY ‘,’

TRAILING NULL COLS

(EMPNO “SEMPNO.NEXTVAL”,

 ENAME “INIT CAP(:ENAME)”,

DEPTNO,

JOINDATE   SYSDATE)

Whenever the client gave data file in fixed format like (data is not separated by commas(‘,’)) tempo is first 5 digits, ename is 10 digits and deptno is 2 digits. Then the control file is looks like this

LOAD DATA

INFILE ‘C:\SLOADER\FIXED_EMP.DAT’

INSERT INTO TABLE FIXED_EMP

(EMPNO POSITION  (1:5);

 ENAME POSITION (6:15);

 DEPTNO POSITION(16:17))

Inserting Data Into Multiple Tables:-

           LOAD DATA

INFILE ‘C:\SLOADER\FIXED.DAT’

INSERT INTO TABLE FIXED_EMP

WHEN EMPNO = ‘’

(EMP NO  POSITION (1:5),

 ENAME POSITION(6:15),

 DEPT NO (16:17))

INTO TABLE  FIXED_DEPT

WHERE DNAME = ‘’

(DNAME POSITION (18:25).,

 PROJECT POSITION (26:28),

STARTDATE POSITION(29 : 39)

Inserting Data into Single Table from Multiple Files:-

           LOAD DATA

INFILE ‘C:\FIRST.DAT’

INFILE ‘C:\SECOND.DAT’

INSERT INTO TABLE FIXED_EMP

FIELDES TERMINATED BY ‘,’

(EMPNO, ENAME, DEPTNO)

With out data file creating the control file:-

           LOAD DATA

INFILE *

INSERT INTO TABLE FIXED_EMP

FIELDES TERMINATED BY ‘,’

(EMPNO, ENAME, DEPTNO)

           BEGIN DATA

                       1014,operations,10

           1234,Scott,10

           456,urman,20

           789,sysadmin,10

          

FILLER:-      If you want to ignore complete data in a column we can used the ‘FILLER’. It is reserved word for SQL * Loader. System will not consider the particular column.

SQL * Loader program registered in Oracle Apps:-

1.     We will develop the SQL* Loader file as per data file and table format.

2.     Move this control file into the server.

3.     Connect to System Administrator Responsibility then create executable and execution method as SQL * Loader and execution file as control file name without extinction.

4.     Create Concurrent Program and attach executable parameters and incompatibilities.

5.     Create Request Group attach Concurrent Program.

6.     Create Responsibility attach Request Group to Responsility.

7.     Create User attach Responsibility to User.

8.     User will submit the Request from SRS Window.

SQL * Script Registration into Oracle Apps:-

1.     We will develop the SQL* Script as per client requirement which has got SQL* Plus commands.

2.     We will move this ‘.sql’ file into the server.

3.     Create Executable with execution method as SQL* Plus and we will give the SQL filename.

4.     Create Concurrent Program and attach executable parameters and incompatibilities.

5.     Create Request Group attaches Concurrent Program.

6.     Create Responsibility attach Request Group to Responsibly.

7.     Create User attach Responsibility to User.

8.     User will submit the Request from SRS Window.

Develop the SQL* script save it as .sql file move this file into sql folder and create executable

Complete GL Interface,

Complete GL Interface

 Pre-Requisites:
    --------------
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined

    Interface Tables:
    ----------------
      GL_INTERFACE
  
    Base Tables:
    -----------
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES

 Standard Program:
 Go to General Ledger Vision Operations(U.S.A)
   Run =>Import
 Here Give the Source name and Save.
While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there...........

   Validation Columns:
   ------------------
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id 

    Source = 'NEW'
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.

Control file for GL_Interface:

LOAD DATA

INFILE *

TRUNCATE INTO TABLE GL_INTERFACE_TEMP

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(STATUS,

 SET_OF_BOOKS_ID,

 ACCOUNTING_DATE,

 CURRENCY_CODE,

 DATE_CREATED,

 CREATED_BY,

 ACTUAL_FLAG,

 USER_JE_CATEGORY_NAME,

 USER_JE_SOURCE_NAME,

 SEGMENT1,

 SEGMENT2,

 SEGMENT3,

 SEGMENT4,

 SEGMENT5,

 ENTERED_DR,

 ENTERED_CR,  

 ACCOUNTED_DR,

 ACCOUNTED_CR,

 GROUP_ID)

BEGINDATA

NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11

NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11

Script

sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl'    log='/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log'

exit 0

GL Interface Package:

CREATE OR REPLACE package body APPS.xx_gl_int_pkg

is

procedure dis_log(p_msg in varchar2)

is 

begin

fnd_file.put_line(fnd_file.log,p_msg);

end;

procedure main(errbuf out varchar2,

               retcode out varchar2

               )

 is

 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;

 v_gl_int    gl_interface%rowtype;

 v_process_flag    varchar2(10);

 v_error_msg   varchar2(100);

 v_tot_err_msg   varchar2(1000);

 

 begin

  

   dis_log('before entering the loop');

   

  

 for i in c1 loop

 

                v_error_msg :=null;

                v_process_flag:='S';

                v_tot_err_msg:=null;

                v_gl_int:=null;

 

              --currency_code validation

                begin

                select  currency_code into v_gl_int.currency_code

                                      from fnd_currencies

                                     where currency_code=i.currency_code;

                 exception

                 when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid Currency Code =>'||i.currency_code;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                 when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at Currency Code =>'||i.currency_code;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                end;      

                

                

                --user_je_source_name validation

                

                begin

                

                  select user_je_source_name into v_gl_int.user_je_source_name

                                             from gl_je_sources

                                            where user_je_source_name=i.user_je_source_name;

                  exception

                 when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid Sourec Name =>'||i.user_je_source_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                 when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at Sourec Name =>'||i.user_je_source_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                end;      

                

                --category_name  validation

                begin

                     select user_je_category_name into v_gl_int.user_je_category_name

                     from gl_je_categories

                     where user_je_category_name=i.user_je_category_name;

                  exception

                  when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid category_name =>'||i.user_je_category_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                  when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at category_name =>'||i.user_je_category_name;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;    

                  

                end;

                

                 --user id validation 

                 

                begin

                     select user_id into v_gl_int.created_by from fnd_user

                                   where  user_id = i.created_by;

                  exception

                  when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid user id =>'||i.created_by;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                  when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception at user id =>'||i.created_by;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ; 

                    

                end;

                

                 -- set of books id validation

                 

                begin

                 

                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id 

                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;

                   exception

                  when no_data_found then

                    v_process_flag:='E';

                    v_error_msg  := 'Invalid set of books id =>'||i.set_of_books_id;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ;

                  when others then

                    v_process_flag:='E';

                    v_error_msg   := ' Exception atset of books id =>'||i.set_of_books_id;

                    v_tot_err_msg:= v_tot_err_msg||' '||v_error_msg ; 

                end;

              

                         v_gl_int.status                    :=i.status;

                        -- v_gl_int.set_of_books_id           :=i.set_of_books_id;

                         v_gl_int.accounting_date           :=i.accounting_date;

                        -- v_gl_int.currency_code             :=i.currency_code;

                         v_gl_int.date_created              :=i.date_created;

                         --v_gl_int.created_by                :=i.created_by;

                         v_gl_int.actual_flag               :=i.actual_flag ;

                         --v_gl_int.user_je_category_name     :=i.user_je_category_name;

                        --v_gl_int.user_je_source_name       :=i.user_je_source_name;

                         v_gl_int.segment1                  :=i.segment1;

                         v_gl_int.segment2                  :=i.segment2;

                         v_gl_int.segment3                  :=i.segment3;

                         v_gl_int.segment4                  :=i.segment4;

                         v_gl_int.segment5                  :=i.segment5 ;

                         v_gl_int.entered_dr                :=i.entered_dr;

                         v_gl_int.entered_cr                :=i.entered_cr;

                         v_gl_int.accounted_dr               :=i.accounted_dr;

                         v_gl_int.accounted_cr              :=i.accounted_cr;

                         v_gl_int.group_id                  :=i.group_id;

                         

               

                 

                 dis_log('before inserting the loop');   

                 

                   if v_process_flag = 'S' then      

                

                    insert into gl_interface values v_gl_int;

                 

                   end if;

           update GL_INTERFACE_TEMP set process_flag=v_process_flag,

                                           error_message=v_tot_err_msg

                       where rowid=i.row_id;

                  

                 dis_log('after inserting the loop');      

 end loop;

 exception

 when others then

 dis_log('exception occured at main loop');

 

 end main;

 end xx_gl_int_pkg;

Vendor wise PO and Invoice

Vendor wise PO and Invoice

select  pv.VENDOR_NAME,
        poh.SEGMENT1 po_num,
        aia.INVOICE_NUM,
        aia.INVOICE_AMOUNT
  from  po_vendors pv,
        po_headers_all poh,
        po_lines_all pol,
        po_distributions_all pod,
        ap_invoices_all aia,
        ap_invoice_distributions_all aid
 where  pv.VENDOR_ID=poh.vendor_id
   and  poh.PO_HEADER_ID=pol.PO_HEADER_ID
   and  pol.PO_LINE_ID=pod.PO_LINE_ID
   and  poh.VENDOR_ID=aia.VENDOR_ID
   and  aia.INVOICE_ID=aid.INVOICE_ID
   and  pod.PO_DISTRIBUTION_ID=aid.PO_DISTRIBUTION_ID
   and  poh.ORG_ID='204'

PO Approved Report

PO Approved Report

SELECT PV.SEGMENT1 SUPNUM,
       PV.VENDOR_NAME SUPNAME,
       POH.SEGMENT1 PONUM,
       POH.CREATION_DATE PODATE,
       PAPF.FULL_NAME BUYER,
       HL1.LOCATION_CODE SHIP_TO,
       HL1.LOCATION_CODE BILL_TO,
       MSI.SEGMENT1 ITEM,
       MSI.DESCRIPTION,
       POL.LINE_NUM,
       POL.QUANTITY,
       POL.UNIT_PRICE,
       (POL.QUANTITY * POL.UNIT_PRICE) AMOUNT,
       GCC.CONCATENATED_SEGMENTS CHARGEACCOUNT
  FROM PO_VENDORS PV,
       PO_HEADERS_ALL POH,
       PER_ALL_PEOPLE_F PAPF,
       HR_LOCATIONS HL1,
       PO_LINES_ALL POL,
       MTL_SYSTEM_ITEMS_B MSI,
       PO_DISTRIBUTIONS_ALL POD,
       GL_CODE_COMBINATIONS_KFV GCC,
       GL_SETS_OF_BOOKS GSB
 WHERE PV.VENDOR_ID = POH.VENDOR_ID
   AND POH.SHIP_TO_LOCATION_ID=HL1.LOCATION_ID
   AND POH.BILL_TO_LOCATION_ID=HL1.LOCATION_ID
   AND POH.AGENT_ID=PAPF.PERSON_ID
   AND MSI.INVENTORY_ITEM_ID=POL.ITEM_ID
   AND MSI.ORGANIZATION_ID=POL.ORG_ID
   AND POH.PO_HEADER_ID=POL.PO_HEADER_ID
   AND POL.PO_LINE_ID=POD.PO_LINE_ID
   AND POD.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND POD.SET_OF_BOOKS_ID=GSB.SET_OF_BOOKS_ID
   AND GCC.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
   AND POH.AUTHORIZATION_STATUS = 'APPROVED'
   AND POH.ORG_ID=NVL(:P_ORG_ID,POH.ORG_ID)
   AND POH.TYPE_LOOKUP_CODE=NVL(:P_TYPE,POH.TYPE_LOOKUP_CODE)
   AND POH.SEGMENT1=NVL(:P_PO_NUM,POH.SEGMENT1)
   ORDER BY 3,10

Shipping Extraction Report

Shipping Extraction Report 

SELECT OOH.ORDER_NUMBER,

       HCA.ACCOUNT_NUMBER PARTY_NUMBER,

       HP.PARTY_NAME,

       OOL.LINE_NUMBER,

       MSI.SEGMENT1 ITEM,

       OOL.ORDERED_QUANTITY,

       OOL.FLOW_STATUS_CODE,

       OOL.REQUEST_DATE,

       (OOL.UNIT_LIST_PRICE * OOL.ORDERED_QUANTITY) TOTAL_SELLING_PRICE,

       MSI.UNIT_VOLUME,

       MSI.UNIT_WEIGHT,

       (MSI.UNIT_VOLUME * OOL.ORDERED_QUANTITY) TOTAL_VOLUME,

       (MSI.UNIT_WEIGHT * OOL.ORDERED_QUANTITY) TOTAL_WEIGHT

  FROM OE_ORDER_HEADERS_ALL OOH,

       OE_ORDER_LINES_ALL OOL,

       HZ_CUST_ACCOUNTS HCA,

       HZ_PARTIES HP,

       MTL_SYSTEM_ITEMS_B MSI

 WHERE OOH.HEADER_ID=OOL.HEADER_ID

   AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID

   AND HCA.PARTY_ID=HP.PARTY_ID

   AND MSI.INVENTORY_ITEM_ID=OOL.INVENTORY_ITEM_ID

   AND MSI.ORGANIZATION_ID = OOH.ORG_ID

   AND HP.PARTY_NAME=NVL(:P_PARTY_NAME,HP.PARTY_NAME)

   AND HCA.ACCOUNT_NUMBER=NVL(:P_ACC_NUM,HCA.ACCOUNT_NUMBER)

   AND OOL.FLOW_STATUS_CODE= NVL(:P_STATUS,OOH.FLOW_STATUS_CODE)

   ORDER BY 1,4

Sales Order Report

Sales Order Report

SELECT HP.PARTY_NAME,

       OOH.ORDER_NUMBER,

       OTT.NAME,

       OOL.LINE_NUMBER,

       MSI.SEGMENT1 ITEM,

       MSI.DESCRIPTION,

       OOL.ORDERED_QUANTITY,

       OOL.UNIT_LIST_PRICE,       

       (OOL.ORDERED_QUANTITY * OOL.UNIT_LIST_PRICE) SALE_PRICE

  FROM OE_ORDER_HEADERS_ALL OOH,

       OE_ORDER_LINES_ALL OOL,

       OE_TRANSACTION_TYPES_TL OTT ,

       HZ_PARTIES HP,

       HZ_CUST_ACCOUNTS HCA ,

       MTL_SYSTEM_ITEMS_B MSI

 WHERE OOH.HEADER_ID=OOL.HEADER_ID

   AND OOH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID

   AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID

   AND HCA.PARTY_ID=HP.PARTY_ID

   AND MSI.INVENTORY_ITEM_ID=OOL.INVENTORY_ITEM_ID

   AND MSI.ORGANIZATION_ID=OOH.ORG_ID

   AND MSI.ORGANIZATION_ID=NVL(:P_ORG_ID,MSI.ORGANIZATION_ID)

   AND OTT.NAME=NVL(:P_TYPE,OTT.NAME)

   ORDER BY 4

   

GL Wise Report

GL Wise Report

SELECT GJH.DEFAULT_EFFECTIVE_DATE VOCHERDATE,

       GJH.DOC_SEQUENCE_VALUE VOCHER_NUM,

       GJH.DESCRIPTION,

       GJL.ACCOUNTED_CR ACC_CR,

       GJL.ACCOUNTED_DR ACC_DR,

       GJL.ACCOUNTED_DR F_CURRENCY_AMOUNT,

       GCC.CONCATENATED_SEGMENTS

  FROM GL_JE_HEADERS GJH,

       GL_JE_LINES GJL,

       GL_CODE_COMBINATIONS_KFV GCC

 WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID

   AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID

   AND GJH.JE_HEADER_ID = NVL(:P_HEADER_ID,GJH.JE_HEADER_ID)

BackOrderd Report

BackOrderd Report

SELECT  OOH.ORDER_NUMBER,

        OOL.LINE_ID,

        OOL.LINE_NUMBER,

        OOL.SHIPMENT_NUMBER,

        OOL.ORDERED_ITEM,

        OOL.ORDERED_QUANTITY,

        HP.PARTY_NAME,

        WDV.RELEASED_STATUS_NAME,

        WDV.SOURCE_NAME

  FROM  OE_ORDER_HEADERS_ALL OOH,

        OE_ORDER_LINES_ALL OOL,

        HZ_CUST_ACCOUNTS HCA,

        HZ_PARTIES HP,

        WSH_DELIVERY_DETAILS WDD,

        WSH_DELIVERABLES_V WDV

 WHERE  OOH.HEADER_ID=OOL.HEADER_ID 

   AND  OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID

   AND  HCA.PARTY_ID=HP.PARTY_ID

   AND  OOL.LINE_ID=WDD.SOURCE_LINE_ID

   AND  WDD.DELIVERY_DETAIL_ID=WDV.DELIVERY_DETAIL_ID

   AND  WDV.RELEASED_STATUS_NAME LIKE 'Backordered'

   AND  OOH.ORG_ID=NVL(:p_org_id,OOH.ORG_ID)

   AND  HP.PARTY_NAME=NVL(:P_PARTY_NAME,HP.PARTY_NAME)

AR Receipt Information Report

AR Receipt Information Report

SELECT ACRA.RECEIPT_NUMBER,

       ACRA.RECEIPT_DATE,

       ACRA.AMOUNT,

       ARM.NAME,

       RCT.TRX_NUMBER,

       HP.PARTY_NAME,

       HCA.ACCOUNT_NUMBER

  FROM AR_CASH_RECEIPTS_ALL ACRA,

       AR_RECEIPT_METHODS ARM,

       AR_RECEIVABLE_APPLICATIONS_ALL ARAA,

       AR_PAYMENT_SCHEDULES_ALL APSC,

       RA_CUSTOMER_TRX_ALL RCT,

       HZ_PARTIES HP,

       HZ_CUST_ACCOUNTS HCA

 WHERE ACRA.RECEIPT_METHOD_ID=ARM.RECEIPT_METHOD_ID

   AND ACRA.CASH_RECEIPT_ID=ARAA.CASH_RECEIPT_ID

   AND ARAA.APPLIED_PAYMENT_SCHEDULE_ID=APSC.PAYMENT_SCHEDULE_ID

   AND APSC.CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID

   AND ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID(+)

   AND HCA.PARTY_ID = HP.PARTY_ID(+)

   AND ACRA.RECEIPT_NUMBER=NVL(:P_RNO,ACRA.RECEIPT_NUMBER)

   AND ACRA.ORG_ID=204

   

Approved Requisition Report

Approved Requisition Report

SELECT PRHA.SEGMENT1 REQNUM,
       PRHA.TYPE_LOOKUP_CODE REQTYPE,
       PRHA.CREATION_DATE REQDATE,
       PAPF.FULL_NAME PREPARERNAME,
       PRLL.LINE_NUM,
       PRLL.QUANTITY,
       PRLL.UNIT_PRICE,
       MSI.SEGMENT1 ITEM,
       MSI.DESCRIPTION,
       (PRLL.QUANTITY * PRLL.UNIT_PRICE) AMOUNT,
       GCC.CONCATENATED_SEGMENTS CHARGEACCOUNT,
       OOD.ORGANIZATION_CODE ORGNAME,
       PAPF.FULL_NAME REQUESTOR,
       HL.LOCATION_CODE LOCATIONNAME
  FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLL,
       MTL_SYSTEM_ITEMS_B MSI,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       GL_CODE_COMBINATIONS_KFV GCC,
       GL_SETS_OF_BOOKS GSB,
       ORG_ORGANIZATION_DEFINITIONS OOD,
       PER_ALL_PEOPLE_F PAPF,
       HR_LOCATIONS HL
 WHERE PRHA.REQUISITION_HEADER_ID=PRLL.REQUISITION_HEADER_ID
   AND PRLL.ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND PRLL.DESTINATION_ORGANIZATION_ID=MSI.ORGANIZATION_ID
   AND PRLL.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
   AND PRDA.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND GCC.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
   AND GSB.SET_OF_BOOKS_ID=PRDA.SET_OF_BOOKS_ID
   AND OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND PAPF.PERSON_ID=PRHA.PREPARER_ID
   AND HL.LOCATION_ID=PRLL.DELIVER_TO_LOCATION_ID
   AND PRHA.AUTHORIZATION_STATUS = 'APPROVED'
   AND PRHA.ORG_ID=204
  -- AND  PRHA.SEGMENT1='2738'  
   AND ROWNUM<200
   ORDER BY 1,5 DESC
 

Total Requisition Code

SELECT  REH.SEGMENT1 REQ_NUM,
        REH.TYPE_LOOKUP_CODE,
        REH.AUTHORIZATION_STATUS,
        PPF.FULL_NAME,
        TO_CHAR((PRL.QUANTITY * PRL.UNIT_PRICE),'99,999.99') TOT_AMOUNT,
        PRL.LINE_NUM,
        PLT.LINE_TYPE TYPE,
        MSI.SEGMENT1 ITEM   ,
        MC.CONCATENATED_SEGMENTS ITEM_CATEGORY,
        MUM.UNIT_OF_MEASURE,
        PRL.QUANTITY,
        PRL.UNIT_PRICE,
        PRL.NEED_BY_DATE,
        PRL.SUGGESTED_VENDOR_NAME  SUP_NAME,
        PRL.SUGGESTED_VENDOR_LOCATION SUP_SITE,
        PRD.DISTRIBUTION_NUM ,
        PRD.REQ_LINE_QUANTITY DIST_QTY,
        GCC.CONCATENATED_SEGMENTS "ACC_NUM"        
  FROM  PO_REQUISITION_HEADERS_ALL REH,
        PO_REQUISITION_LINES_ALL PRL,
        PO_REQ_DISTRIBUTIONS_ALL PRD,
        PER_ALL_PEOPLE_F PPF,
        PO_LINE_TYPES PLT,
        MTL_CATEGORIES_KFV MC,
        MTL_CATEGORY_SETS  MCS,
        MTL_ITEM_CATEGORIES MIC,
        MTL_SYSTEM_ITEMS_B MSI,
        MTL_UNITS_OF_MEASURE MUM,
        GL_CODE_COMBINATIONS_KFV GCC,
        GL_SETS_OF_BOOKS GSB
 WHERE  REH.PREPARER_ID=PPF.PERSON_ID
   AND  REH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID
   AND  PRL.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
   AND  PRL.LINE_TYPE_ID=PLT.LINE_TYPE_ID
   AND  PRL.ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND  PRL.ORG_ID=MSI.ORGANIZATION_ID
   AND  PRL.CATEGORY_ID=MC.CATEGORY_ID
   AND  MC.STRUCTURE_ID=MCS.STRUCTURE_ID
   AND  MCS.CATEGORY_SET_ID=MIC.CATEGORY_SET_ID
   AND  MIC.CATEGORY_ID=MC.CATEGORY_ID
   AND  MIC.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND  MSI.ORGANIZATION_ID=MIC.ORGANIZATION_ID
   AND  MSI.PRIMARY_UOM_CODE=MUM.UOM_CODE
   AND  PRD.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND  GCC.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
   AND REH.ORG_ID =NVL(:P_ORG_ID,REH.ORG_ID)
   AND REH.SEGMENT1='5962'

Total Purchase Order Code(Imp)

select         poh.SEGMENT1,
               poh.TYPE_LOOKUP_CODE,
               poh.CREATION_DATE,
               pov.VENDOR_NAME,
               pvs.VENDOR_SITE_CODE,
               hrl.LOCATION_CODE"ship_to",
               hrl.LOCATION_CODE"bill_to",
               poh.AUTHORIZATION_STATUS "status",
               papf.FULL_NAME,
               poh.CURRENCY_CODE,
               pol.QUANTITY*pol.UNIT_PRICE "amount",
               pol.LINE_NUM,
               plt.LINE_TYPE,
               msi.SEGMENT1"item",
               mck.CONCATENATED_SEGMENTS,
               mum.UNIT_OF_MEASURE,
               pol.QUANTITY "qty",
               pol.UNIT_PRICE "price",
               pll.SHIPMENT_NUM,
               ood.ORGANIZATION_CODE,
               hrl.LOCATION_CODE,
               pll.QUANTITY,
               pll.NEED_BY_DATE,
               pod.DISTRIBUTION_NUM,            
               pod.QUANTITY_ORDERED,
               GCC.CONCATENATED_SEGMENTS "account num"
  from         po_headers_all poh,
               po_vendors pov,
               po_vendor_sites_all pvs,
               hr_locations hrl,
               per_all_people_f papf,
               po_agents poa,
               po_lines_all pol,
               mtl_system_items_b msi,
               mtl_categories_kfv mck,
               mtl_item_categories mic,
               mtl_category_sets mcs,
               mtl_units_of_measure mum,
               po_line_locations_all pll,
               org_organization_definitions ood,
               po_distributions_all pod,
               po_line_types plt  ,
               GL_CODE_COMBINATIONS_KFV GCC,
               GL_SETS_OF_BOOKS GSB            
 where         poh.segment1='4696'
 and           poh.VENDOR_ID=pov.VENDOR_ID
 and           poh.VENDOR_SITE_ID=pvs.VENDOR_SITE_ID
 and           poh.SHIP_TO_LOCATION_ID=hrl.SHIP_TO_LOCATION_ID
 and           poh.BILL_TO_LOCATION_ID=hrl.LOCATION_ID
 and           poh.AGENT_ID=poa.AGENT_ID
 and           poa.AGENT_ID=papf.PERSON_ID
 and           poh.PO_HEADER_ID=pol.PO_HEADER_ID
 and           pol.ITEM_ID=msi.INVENTORY_ITEM_ID
 and           pol.ORG_ID=msi.ORGANIZATION_ID
 and           pol.CATEGORY_ID=mck.CATEGORY_ID
 and           mck.CATEGORY_ID=mic.CATEGORY_ID
 and           mic.CATEGORY_SET_ID=mcs.CATEGORY_SET_ID
 and           mic.ORGANIZATION_ID=msi.ORGANIZATION_ID
 and           mic.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
 and           mum.UOM_CODE=msi.PRIMARY_UOM_CODE
 and           pol.PO_LINE_ID=pll.PO_LINE_ID
 and           pol.ORG_ID=ood.ORGANIZATION_ID
 and           pll.SHIP_TO_LOCATION_ID=hrl.SHIP_TO_LOCATION_ID
 and           pll.LINE_LOCATION_ID=pod.LINE_LOCATION_ID
 and           pol.LINE_TYPE_ID=plt.LINE_TYPE_ID
 and           gcc.CODE_COMBINATION_ID=pod.CODE_COMBINATION_ID
 and           gcc.CHART_OF_ACCOUNTS_ID=gsb.CHART_OF_ACCOUNTS_ID

For Displaying concurrent program dynamically In Reports etc...........

For  displaying concurrent programm name by declaring user parameter as p_conc_request_id and
writing trigger as srw.user_exit('fnd srwinit') in before report trigger and  srw.user_exit('fnd srwexit') in after report trigger.
now we can take one place holder column in report level... so, now we r going to use this place holder column in one formaula column.
for that take one formula column and in that we want to write a piece of code which is shown below....

select     fcp.USER_CONCURRENT_PROGRAM_NAME into :cp_conc_name(place holder column name)
from       fnd_concurrent_requests fcr,
           fnd_concurrent_programs_vl fcp
where      fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
and        fcr.REQUEST_ID=nvl(:p_conc_request_id,fcr.REQUEST_ID);

and for request id we can declare in same in formula column
as
return(fnd_profile.value(':p_conc_request_id'));