fndload commands Key Flexfield and Descriptive Flexfield descriptions

KFF - Keyflexfields are used throughout the Applications to uniquely identify informations such as GL accounts, INV items, FIxed Assets and other entities that every business needs to keep track of. 
We can't able to create a new kff but we can modify the existing one

DFF- Descriptive flexfields enable you to capture additional pieces of information from transaction entered into Oracle EBS.

DFF and KFF share common features. They are multisegment fields. Application validates individual segments based ons makes valueset rules and ensures that the segments makes sense using CVR (cross-validation rules).

Context Sensitive Segments  in DFF:

Context Sensitive Segments are conditional DFFs. Only when a condition  is met a particular field 'appears' and we are able to capture details. So for that first DFF is made context sesitive and then second one if made reference.

DFF has two types of segments

1. Global Segments
2. Context Sensitive Segments

Context Sensitive Segments depends on value of another field. 

REFERENCE Field - If the context sensitive information is derived from a field in the form, then that field is called as a Reference Field.

CONTEXT Field - If the context sensitive information is derived from a field / segment in the DFF pop up windown, then that field is called as a Context Field.

Downloading a ldt file for dff 

Download command

FNDLOAD un/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct address.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="AR" DESCRIPTIVE_FLEXFIELD_NAME="Remit Address HZ"

query below using title to get the DESCRIPTIVE_FLEXFIELD_NAME


select * from apps.FND_DESCRIPTIVE_FLEXS_VL where title='Address';

p_context_code is not used in the fndload download command because not using this for downloading a specific code in the screen shot if needed to downlaod specific code then below command is used


FNDLOAD:

FNDLOAD un/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="AR" DESCRIPTIVE_FLEXFIELD_NAME="Remit Address HZ"
p_context_code='give the code name'


Upload Command:

FNDLOAD un/pwd 0 Y UPLOAD @FND:patch/115/import/afffload.lct filename.ldt custom_mode=FORCE

Query to find active customer accounts and sites in oracle apps r12

Query to find active customer accounts and sites in oracle apps r12:

SELECT
       hp.party_name                        "PARTY_NAME",      
       hp.party_number                      "PARTY_NUM",
       DECODE(hp.status,
              'A', 'Active',
              'I', 'Inactive',
              hp.status)                    "Party Status",
       hca.account_name                     "ACCOUNT_NAME",
       hca.account_number                   "ACCOUNT_NUM",
       DECODE(hca.status,
              'A', 'Active',
              'I', 'Inactive',
              hca.status)                   "Account Status",
       hcas.creation_date                   "SITE_CREATE_DT",
       DECODE(hcas.status,
              'A', 'Active',
              'I','Inactive')                   "Site Status",
       hps.party_site_number                "PARTY_SITE_NUMBER",
       hou.name                             "ORG",
       hcsua.site_use_code                  "SITE_USE_CODE",
       hcsua.location                       "LOCATION",
       col.name                             "COLLECTOR",
       hcpc.name                            "PROFILE_CLASS",
       rt.name                              "PAY_TERM",
       hcp.Credit_rating                    "CREDIT_RATING",
       hcp.credit_checking                  "CREDIT_CHECKING",
       hcp.credit_hold                      "CREDIT_HOLD",
       hcp.tolerance                        "TOLERANCE",
       hcp.override_terms                   "OVERRIDE_TERMS",
       hcpa.trx_credit_limit                "TRX_CREDIT_LIMIT",
       hcpa.overall_credit_limit            "OVERALL_CREDIT_LIMIT",
       arsc.name                            "STATEMENT_CYCLE",
       hl.address1                          "ADDRESS1",
       hl.address2                          "ADDRESS2",
       hl.address3                          "ADDRESS3",
       hl.address4                          "ADDRESS4",
       hl.city                              "CITY",
       hl.state                             "STATE",
       hl.postal_code                       "ZIP_CODE",
       hl.country                           "COUNTRY"
  FROM
       hz_parties              hp,
       hz_party_sites          hps,
       hz_cust_accounts_all    hca,
       hz_cust_acct_sites_all  hcas,
       hz_cust_site_uses_all   hcsua,
       hz_customer_profiles    hcp,
       hz_cust_profile_classes hcpc,
       hz_cust_profile_amts    hcpa,
       hz_locations            hl,
       ar_collectors           col,
       AR_STATEMENT_CYCLES arsc,
       hr_operating_units hou,
       ra_terms rt
 WHERE
       1=1
   AND hp.party_id            =  hca.party_id
   AND hca.cust_account_id    =  hcas.cust_account_id(+)
   AND hps.party_site_id(+)   =  hcas.party_site_id
   AND hp.party_id            =  hcp.party_id
   AND hca.cust_account_id    =  hcp.cust_account_id
   AND hps.location_id        =  hl.location_id(+)
   AND col.collector_id       =  hcp.collector_id
   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
   AND hcas.cust_acct_site_id=hcsua.cust_acct_site_id
   AND hcp.statement_cycle_id=arsc.statement_cycle_id
   AND hcas.org_id=hou.organization_id
   AND hcpc.profile_class_id=hcp.profile_class_id
   AND hca.payment_term_id=rt.term_id(+)
   AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types
   AND hp.status              = 'A'               -- only Active Parties/Customers
   AND hca.status = 'A'    --Only active sites
   AND hcas.status = 'A'   -- Only active sites
   AND  hcsua.site_use_code='BILL_TO'
   AND hou.name in ('US Operating Unit','IE Operating Unit')
   --AND hp.party_name like 'DELL%'
   --AND hca.account_number='11506'
   ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;

l_path_value../usr/tmp Invalid File Operation : ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation in oracle apps

The log file shows an error like below:

Entered into Begin.
l_path_value../usr/tmp
Invalid File Operation : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

This is not due to the permission like read/write in the directory path /usr/tmp/
It is due to from one login say sysadmin submitted the program and generates the .csv or .txt file in /usr/tmp/ directory. The file will get saved in the directory. when one more time needs to submit from another user login then the error will throw in log file 

Entered into Begin.
l_path_value../usr/tmp
Invalid File Operation : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

At first needs to delete the .csv file in the /usr/tmp/ directory and then from another login can able to submit the program and generate the .csv file. Because my 2 test instances are commonly having /usr/tmp/ directory so from one test instance the file first generted then while running from another test instance the csv file won't get replaced the existing file permission denied will come through putty also we can't able to delete or replace the file from another login credentials.

There is another way one way is to delete the file and generate the file or
Needs to give permissions for the file in the directory by chmod 777 filename or winscp
so that from any login submitted the program it will replace the old file as of now it will be generated in read mode only.

Query to find the customer standard details in order management->customer->standard form

Query-

SELECT   role_acct.account_number, hzp.party_name, hl.address1, hl.address2,
         hl.city, hl.state, hl.postal_code, cont_party.person_last_name,
         cont_party.person_first_name,
         DECODE (hcp.phone_line_type,
                 'GEN', hcp.phone_area_code || hcp.phone_number,
                 NULL
                ) phone_number,
         hcp.phone_extension,
         DECODE (hcp.phone_line_type,
                 'FAX', hcp.phone_area_code || hcp.phone_number,
                 NULL
                ) fax_number,
         cont.orig_system_reference
    FROM hz_cust_account_roles cont,
         hz_parties cont_party,
         hz_relationships cont_rel,
         hz_org_contacts cont_org,
         hz_parties cont_rel_party,
         ar_lookups l,
         ar_lookups l1,
         hz_contact_restrictions cont_res,
         hz_person_language per_lang,
         hz_contact_points cont_point,
         hz_cust_account_roles acct_role,
         hz_parties party,
         hz_parties rel_party,
         hz_relationships rel,
         hz_org_contacts org_cont,
         hz_cust_accounts role_acct,
         hz_cust_accounts cont_role_acct,
         hz_contact_points hcp,
         hz_parties hzp,
         hz_party_sites hps,
         hz_cust_acct_sites_all hcas,
         hz_locations hl
   WHERE cont_org.title = l.lookup_code(+)
     AND l.lookup_type(+) = 'CONTACT_TITLE'
     AND cont_org.job_title_code = l1.lookup_code(+)
     AND l1.lookup_type(+) = 'RESPONSIBILITY'
     AND cont.cust_account_role_id = acct_role.cust_account_role_id
     AND cont.party_id = cont_rel.party_id
     AND cont.role_type = 'CONTACT'
     AND cont_org.party_relationship_id = cont_rel.relationship_id
     AND cont_rel.subject_id = cont_party.party_id
     AND cont_rel.party_id = cont_rel_party.party_id
     AND acct_role.party_id = rel.party_id
     AND acct_role.role_type = 'CONTACT'
     AND org_cont.party_relationship_id = rel.relationship_id
     AND rel.subject_id = party.party_id
     AND rel_party.party_id = rel.party_id
     AND party.party_id = per_lang.party_id(+)
     AND per_lang.native_language(+) = 'Y'
     AND cont_point.owner_table_id(+) = rel_party.party_id
     AND cont_point.contact_point_type(+) = 'EMAIL'
     AND cont_point.primary_flag(+) = 'Y'
     AND party.party_id = cont_res.subject_id(+)
     AND cont_res.subject_table(+) = 'HZ_PARTIES'
     AND cont.cust_account_id = cont_role_acct.cust_account_id
     AND cont_role_acct.party_id = cont_rel.object_id
     AND acct_role.cust_account_id = role_acct.cust_account_id
     AND role_acct.party_id = rel.object_id
     AND rel_party.party_id = hcp.owner_table_id(+)
     AND hzp.party_id = role_acct.party_id
     AND role_acct.party_id = hps.party_id
     AND hps.party_site_id = hcas.party_site_id
     AND hl.location_id = hps.location_id
     AND cont_org.party_site_id = hcas.party_site_id
     AND role_acct.account_number='10508'
     AND cont_point.email_address like 'DOCLINKCUSTOMERCARE@QLOGIC.COM'
ORDER BY role_acct.account_number, cont.cust_account_role_id;

To find the concurrent request program submitted through backend query in oracle apps r12

select * from apps.fnd_concurrent_requests
where concurrent_program_id in (select concurrent_program_id from apps.fnd_concurrent_programs where concurrent_program_name like 'XX_O2C_RAXINV')
order by request_id desc;

From the above query take the requested by and query below with user id to see the user name

select * from apps.fnd_user where user_id=1005316;

Take the responsibility id from above query and search below to find the responsibility name.

select * from apps.fnd_responsibility_vl where responsibility_id=54314;

The argument 1 to argument 15 from first query shows the parameters that are from querying the concurrent program name in system administrator ->program name-> parameters not while submitting passing parameters. Refer that with while submitting parameter names

PRAGMA in oracle

PRAGMA: 

To create exceptions with standard oracle errors by using the PRAGMA EXCEPTION_INIT function are called non- predefined exceptions.

PRAGMA( also called as pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the pl/sql block is executed.Rather it directs the pl/sql compiler to interpret all occurences of the exception name within the block as the associated oracle server error number.

SYNTAX:

declare 

exception EXCEPTION;

PRAGMA EXCEPTION_INIT(exception,error_number);

error_number is the standard oracle server error number

Non pre defined error i.e user defined error

To trap oracle server error number -01400

("Cannot insert null");

set serveroutput on

declare

insert_excep exception;

PRAGMA EXCEPTION_INIT

(insert_excep,-01400);

begin

insert into departments

(department_id,department_name) values (280,null);

EXCEPTION

WHEN insert_excep THEN

DBMS_OUTPUT.PUT_LINE('Insert Operation Failed');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

where department_name is not null column.

Cursors,parameter cursors

Cursors: A cursor is like a pointer in c. It holds the address location instead of item.

The cursor is of 2 types

a) implicit cursor
b) explicit cursor

• Implicit cursors:

Declared and managed by pl/sql for all dml and pl/sql select statements.

• Explicit cursors:

For queries return more than one row,explicit cursors are declared and managed by the programmer and manipulated through specific statements in the block's executable actions.

Explicit cursor attributes:

Attribute	Type	Description
%ISOPEN	Boolean	Evaluates to true if the cursor is open
%NOTFOUND	Boolean	Evaluates to true if the recent fetch does not return a row
%FOUND	Boolean	Evaluates to true if the recent fetch returns a row
%ROWCOUNT	Number	Evaluates to the total number of rows

ex-

Begin

open emp_cursor;

loop

fetch emp_cursor into empno,ename;

EXIT WHEN emp_cursor%ROWCOUNT > 10 OR  emp_cursor%NOTFOUND;

end loop;

close emp_cursor;

end;

PARAMETER CURSORS: 

We can pass values inside the cursor by using a parameterized cursors.

ex-

set serveroutput on

declare

dept_id number;

lname varchar2(15);

 cursor emp_cursor ( deptno NUMBER ) IS

select employee_id,last_name

from employees

where department_id=deptno;

BEGIN

open emp_cursor(10);

...

close emp_cursor;

open emp_cursor(20);

...

close emp_cursor;

...

end;

Difference between stored procedure and functions in oracle

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of Stored Procedure and Different types of Function.

Basic Difference

1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8. We can go for Transaction Management in Procedure whereas we can't go in Function.

fndload - downloading valueset set with values

Fndload - downloading valueset set with values

FNDLOAD apps/pswapps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXPMI_PRICELISTS.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXPMI_PRICELISTS'

Uploading Value Set-

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXPMI_PRICELISTS.ldt

Creating a function with cursor with parameter example

Creating a function with cursor with parameter example


create or replace
FUNCTION xx_get_serial_number(p_del_detail_ID IN NUMBER) RETURN VARCHAR2 IS
CURSOR get_ser_num(C_delivery_detail_ID NUMBER) IS
select fm_serial_number
from WSH_SERIAL_NUMBERS
where delivery_detail_ID = C_delivery_detail_ID; --32299178
l_result varchar2(32767);
l_value varchar2(32767) := NULL;
l_ser_num varchar2(120);

i         number :=1;
BEGIN
FOR GET_SER_NUM_REC IN GET_SER_NUM(p_del_detail_ID)
LOOP
l_ser_num := GET_SER_NUM_REC.fm_serial_number;

if l_ser_num is not null then
IF i=1 then
l_value:=l_ser_num;
elsif i mod 4 !=0 then

l_value := l_value ||' '||','|| l_ser_num ;
else
l_value := l_value ||' '||','|| l_ser_num||chr(9) ;

end if;
i := i +1;


end if;


END LOOP;
RETURN (l_value);
END;

Cursor showing first group by then having then order by clause (Order Clause)

CURSOR xx_docimg_receipts_in (P_PO_Line_ID IN VARCHAR2) IS
      SELECT rh.creation_date, rh.receipt_num, rh.packing_slip, rt.po_line_id, rt.po_line_location_id,
                            SUM(NVL(rt.quantity,0) *
                         DECODE(rt.transaction_type,'RECEIVE',1,'RETURN TO VENDOR', -1, 'REJECT', -1, 'CORRECT',DECODE(rt1.transaction_type, 'RETURN TO VENDOR', 1, -1),1)) -
                         ( SUM(NVL(rt.quantity_billed,0))
                       --  + SUM(NVL(xx.quantity_applied,0))  commented by Kiran
                         ) qty_not_billed
      FROM rcv_transactions rt,  rcv_shipment_headers rh, rcv_transactions rt1,
                 ( SELECT  xx.receipt_number,  sum(nvl((xx.amount/(pll.quantity*pll.unit_price)) * pll.quantity,0)) quantity_applied ----Added By Junaid Khan
                        --SUM(NVL(xx.quantity_invoiced,0)) quantity_applied -- Commented By Junaid Khan
                      FROM xx_ap_inv_lines_interface xx,
                     po_lines_all pll
                     WHERE xx.receipt_number IS NOT NULL
                     and xx.po_line_id = pll.po_line_id
                     GROUP BY xx.receipt_number ) xx
      WHERE rt.shipment_header_id = rh.shipment_header_id AND
                        rh.receipt_num = xx.receipt_number(+) AND
                        rt.parent_transaction_id = rt1.transaction_id(+)  AND
                        rt.transaction_type IN ('RECEIVE','RETURN TO VENDOR', 'CORRECT', 'REJECT') AND
                        rt.po_line_id = P_PO_Line_ID
      GROUP BY  rh.creation_date, rh.receipt_num, rh.packing_slip, rt.po_line_id, rt.po_line_location_id
         HAVING  SUM(NVL(rt.quantity,0) *
                         DECODE(rt.transaction_type,'RECEIVE',1,'RETURN TO VENDOR', -1, 'REJECT', -1, 'CORRECT',DECODE(rt1.transaction_type, 'RETURN TO VENDOR', 1, -1),1)) -
                         ( SUM(NVL(rt.quantity_billed,0))
                        -- + SUM(NVL(xx.quantity_applied,0))  commented by Kiran
                         ) <> 0
      ORDER BY rh.creation_date;

Creating a cartesian product query for supplier and supplier sites with null bank branches information in oracle apps R12

What is a cartesian product in oracle

ex - There are 2 tables say a and b
a contains 5 records with empno 12345
b contains 5 records with empno 12345

select * from a,b
o/p:
25 records will come because there is no Join condition of a and b table.
So this results cartesian product.

To use this at where means say supplier tables are different and supplier sites information are different so i make supplier as x and supplier sites level as y and the query here i performed

Code with data definition:

<?xml version="1.0" encoding="utf-8"?>
<dataTemplate name="XX_SUPPLIER_BANK_ACCOUNTS_AUDIT_DD" DESCRIPTION=" " version="1.0">
  <properties>
    <property name="xml_tag_case" value="upper" />
  </properties>
  <parameters>
    <parameter name="P_ORGANIZATION_NAME" dataType="VARCHAR2"></parameter>
    <parameter name="P_LAST_MODIFIED_DATE_FROM" dataType="DATE"></parameter>
    <parameter name="P_LAST_MODIFIED_DATE_TO" dataType="DATE"></parameter>
  </parameters>
  <lexicals></lexicals>
  <dataQuery>
    <sqlStatement name="Q1"><![CDATA[
SELECT DISTINCT nvl(y.operating_unit,x.operating_unit) operating_unit,
         nvl(y.supplier_name,x.supplier_name) supplier_name,
         nvl(y.supplier_number,x.supplier_number) supplier_number,
           (SELECT CASE WHEN iao1.end_date IS NULL OR iao1.end_date>SYSDATE THEN 'ACTIVE'
               ELSE 'INACTIVE'
          END status
           FROM iby_account_owners iao1
          WHERE iao1.ext_bank_account_id=x.ext_bank_account_id
            AND iao1.primary_flag='Y') status    ,  
         x.bank_name,
         x.country_code country,
         x.party_name branch_name,
         x.bank_or_branch_number branch_number,
         x.bank_account_name account_name,
CAST(x.bank_account_num AS CHAR(28)) AS account_number,  --Added by sathish for Request id:208025 on 12-May-2014
--x.bank_account_num account_number,  --Commented by sathish for Request id:208025 on 12-May-2014
         x.bic,
         x.iban,
         x.currency_code currency,
         x.order_of_preference PRIMARY,
         x.start_date effective_date_from,
         x.end_date  end_date, --Added by Shivani on 19-Nov-2013
         y.end_date1 end_date1, --Added by Shivani on 21-Nov-2013
         TO_CHAR (x.creation_date, 'MM/DD/YYYY HH24:MI:SS') created_date,
         (SELECT fu.user_name
            FROM fnd_user fu
           WHERE fu.user_id = x.created_by) created_by,
         TO_CHAR (x.last_update_date, 'MM/DD/YYYY HH24:MI:SS') updated_date,
         (SELECT fu.user_name
            FROM fnd_user fu
           WHERE fu.user_id = x.last_updated_by) updated_by,
         y.site_name,
         nvl(x.payment_method_code,y.payment_method_code) payment_method,
          DECODE(y.account_owner_id,NULL,NULL,(CASE WHEN y.effective_date_to1 IS NULL OR y.effective_date_to1>SYSDATE THEN 'ACTIVE'
               ELSE 'INACTIVE'
          END ))status1,
         y.bank_name1,
         y.country1,
         y.branch_name1,
         y.branch_number1,
         y.bank_account_name1,
CAST(y.account_number1 AS CHAR(28)) AS account_number1,  --Added by sathish for Request id:208025 on 12-May-2014
         --y.account_number1, --Commented by sathish for Request id:208025 on 12-May-2014
         y.bic1,
         y.iban1,
         y.primary1,
         y.currency1,
         y.effective_date_from1,
         y.effective_date_to1,
         TO_CHAR (y.creation_date, 'MM/DD/YYYY HH24:MI:SS') created_date1,
         (SELECT fu.user_name
            FROM fnd_user fu
           WHERE fu.user_id = y.created_by) created_by1,
         TO_CHAR (y.last_update_date,
                  'MM/DD/YYYY HH24:MI:SS') updated_date1,
         (SELECT fu.user_name
            FROM fnd_user fu
           WHERE fu.user_id = y.last_updated_by) updated_by1
FROM
        (
SELECT hou.NAME operating_unit,
       aps.vendor_name supplier_name,
       aba.ext_bank_account_id,
       hou.organization_id,
       aba.party_id,
       aps.segment1 supplier_number,
       ass.vendor_site_code site_name,
       iepm.payment_method_code,
       hzpbank1.party_name bank_name1,
       aba.country_code country1,
       hzpbranch1.party_name branch_name1,
       hopbranch1.bank_or_branch_number branch_number1,
       aba.bank_account_name bank_account_name1,
       aba.bank_account_num account_number1,
       abau.order_of_preference primary1,
       aba.currency_code currency1,
       abau.start_date effective_date_from1,
       abau.end_date end_date1,
       iao.end_date effective_date_to1,
       iao.account_owner_id,
       aba.creation_date,
       aba.created_by,
       aba.last_update_date,
       aba.last_updated_by,
       branchcp.eft_swift_code bic1,
       aba.iban iban1    
  FROM ap_suppliers aps,
       ap_supplier_sites_all ass,
       iby_payee_assigned_bankacct_v abau,
       iby_payee_all_bankacct_v aba,
       hr_all_organization_units hou,
       hz_parties hzpbank1,
       hz_parties hzpbranch1,
       hz_organization_profiles hopbank1,
       hz_organization_profiles hopbranch1,
       iby_account_owners iao,
       iby_pmt_instr_uses_all ipi,
       iby_ext_party_pmt_mthds iepm,
       hz_contact_points branchcp
 WHERE --hou.organization_id = NVL (:p_organization_id, hou.organization_id)
       hou.NAME = NVL (:p_organization_name, hou.NAME)
   AND NOT EXISTS (SELECT 1
         FROM fnd_lookup_values flv
        WHERE flv.lookup_type = 'XX_INACTIVE_OU_LIST'
          AND flv.meaning = hou.NAME
          AND flv.enabled_flag='Y')
   AND aba.party_id = aps.party_id
   AND abau.ext_bank_account_id = aba.ext_bank_account_id
   AND iao.ext_bank_account_id = aba.ext_bank_account_id
   AND iepm.ext_pmt_party_id = ipi.ext_pmt_party_id
   AND ((iepm.inactive_date IS NULL) OR (iepm.inactive_date > SYSDATE))
   AND iepm.primary_flag = 'Y'
   AND iao.ext_bank_account_id = ipi.instrument_id
   AND iao.primary_flag = 'Y'
   AND abau.party_id = aps.party_id
   AND aba.bank_id = hzpbank1.party_id
   AND aba.branch_id = hzpbranch1.party_id
   AND hzpbank1.party_id = hopbank1.party_id
   AND hzpbranch1.party_id = hopbranch1.party_id
   AND ((    abau.party_site_id = ass.party_site_id
         AND abau.supplier_site_id = ass.vendor_site_id
        )
       )
   AND hou.organization_id = ass.org_id
   AND ass.vendor_id = aps.vendor_id
   AND branchcp.owner_table_name(+) = 'HZ_PARTIES'
   AND branchcp.owner_table_id(+) = hzpbranch1.party_id
   AND branchcp.contact_point_type(+) = 'EFT'
   AND branchcp.status(+) = 'A'
   AND ass.inactive_date is null        --Added by sathish to filter Active supplier Records alone for Request Id#208274 on 20-05-2014
   ) y   FULL OUTER JOIN      
            (
SELECT hou.NAME operating_unit,
       aps.vendor_name supplier_name,
       aps.segment1 supplier_number,
       iep.payee_party_id,
       ieb.ext_bank_account_id,
       ipi.instrument_id,
       iep.ext_payee_id,
       ipi.ext_pmt_party_id,
       hzpbank.party_id,
       hzpbank.party_name bank_name,
       ieb.country_code,
       hzpbranch.party_name,
       hopbranch.bank_or_branch_number,
       ieb.bank_account_name,
       ieb.bank_account_num,
       ieb.currency_code,
       ipi.order_of_preference,
       ipi.start_date,
       ipi.end_date
       ieb.creation_date,
       ieb.created_by,
       ieb.last_update_date,
       ieb.last_updated_by,
       iepm.payment_method_code,
       branchcp.eft_swift_code bic ,
       ieb.iban  
  FROM hr_all_organization_units hou,
       ap_suppliers aps,
       iby_external_payees_all iep,
       iby_pmt_instr_uses_all ipi,
       iby_ext_bank_accounts ieb,
       iby_ext_party_pmt_mthds iepm,
       hz_parties hzpbank,
       hz_parties hzpbranch,
       hz_organization_profiles hopbank,
       hz_organization_profiles hopbranch,
       hz_contact_points branchcp,
       iby_account_owners iao -- Added by Shivani 19-Nov-2013  
 WHERE iep.ext_payee_id = ipi.ext_pmt_party_id
  -- AND hou.organization_id = NVL (:p_organization_id, hou.organization_id)
   AND hou.NAME = NVL (:p_organization_name, hou.NAME)
   AND NOT EXISTS (SELECT 1
         FROM fnd_lookup_values flv
        WHERE flv.lookup_type = 'XX_INACTIVE_OU_LIST'
          AND flv.meaning = hou.NAME
          AND flv.enabled_flag='Y')
   AND EXISTS (
          SELECT 1
            FROM ap_supplier_sites_all ass
           WHERE ass.vendor_id = aps.vendor_id
             AND hou.organization_id = ass.org_id
             AND ass.inactive_date is null        --Added by sathish to filter Active supplier Records alone for Request Id#208274 on 20-05-2014
             )
   AND iep.payee_party_id = aps.party_id
   AND iepm.ext_pmt_party_id = ipi.ext_pmt_party_id
   AND ((iepm.inactive_date IS NULL) OR (iepm.inactive_date > SYSDATE))
   AND iepm.primary_flag = 'Y'
   AND ipi.instrument_id = ieb.ext_bank_account_id
   AND ieb.bank_id = hzpbank.party_id
   AND ieb.branch_id = hzpbranch.party_id
   AND hzpbank.party_id = hopbank.party_id
   AND hzpbranch.party_id = hopbranch.party_id
   AND iep.party_site_id IS NULL
   AND iep.supplier_site_id IS NULL
   AND branchcp.owner_table_name(+) = 'HZ_PARTIES'
   AND branchcp.owner_table_id(+) = hzpbranch.party_id
   AND branchcp.contact_point_type(+) = 'EFT'
   AND branchcp.status(+) = 'A'
   AND iao.ext_bank_account_id = ieb.ext_bank_account_id
   AND iao.primary_flag='Y'
   UNION   --Added by Sathish for Request Id: 212205 to bring the suppliers where bank branch name and id is null also
   SELECT hou.NAME operating_unit,
       aps.vendor_name supplier_name,
       aps.segment1 supplier_number,
       iep.payee_party_id,
       ieb.ext_bank_account_id,
       ipi.instrument_id,
       iep.ext_payee_id,
       ipi.ext_pmt_party_id,
       hzpbank.party_id,
       hzpbank.party_name bank_name,
       ieb.country_code,
       --hzpbranch.party_name,
       --hopbranch.bank_or_branch_number,
      ( select hzpbranch.party_name from hz_parties hzpbranch where hzpbranch.party_id=ieb.branch_id) party_name,
       ( select hopbranch.bank_or_branch_number from hz_organization_profiles hopbranch where hopbranch.party_id=( select hzpbranch.party_id from hz_parties hzpbranch
         where hzpbranch.party_id=ieb.branch_id )) bank_or_branch_number,
       ieb.bank_account_name,
       ieb.bank_account_num,
       ieb.currency_code,
       ipi.order_of_preference,
       ipi.start_date,
       ipi.end_date ,
       ieb.creation_date,
       ieb.created_by,
       ieb.last_update_date,
       ieb.last_updated_by,
       iepm.payment_method_code,
       branchcp.eft_swift_code bic,
       ieb.iban  
  FROM hr_all_organization_units hou,
       ap_suppliers aps,
       iby_external_payees_all iep,
       iby_pmt_instr_uses_all ipi,
       iby_ext_bank_accounts ieb,
       iby_ext_party_pmt_mthds iepm,
       hz_parties hzpbank,
      --  hz_parties hzpbranch,
       hz_organization_profiles hopbank,
      -- hz_organization_profiles hopbranch,
       hz_contact_points branchcp,
       iby_account_owners iao -- Added by Shivani 19-Nov-2013
  Where IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
   AND hou.NAME = NVL (:p_organization_name, hou.NAME)
    AND NOT EXISTS (SELECT 1
         FROM fnd_lookup_values flv
        WHERE flv.lookup_type = 'XX_INACTIVE_OU_LIST'
          AND flv.meaning = hou.NAME
          AND flv.enabled_flag='Y')
   AND EXISTS (
          SELECT 1
            FROM ap_supplier_sites_all ass
           WHERE ass.vendor_id = aps.vendor_id
             AND hou.organization_id = ass.org_id
             AND ass.inactive_date is null      
             )
   AND iep.payee_party_id = aps.party_id
   AND iepm.ext_pmt_party_id = ipi.ext_pmt_party_id
   AND ((iepm.inactive_date IS NULL) OR (iepm.inactive_date > SYSDATE))
   AND iepm.primary_flag = 'Y'
   AND ipi.instrument_id = ieb.ext_bank_account_id
   AND IEB.BANK_ID = HZPBANK.PARTY_ID
  -- AND ieb.branch_id = hzpbranch.party_id
   AND ieb.branch_id is null
   AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
  -- AND hzpbranch.party_id = hopbranch.party_id
   AND iep.party_site_id IS NULL
   AND iep.supplier_site_id IS NULL
   AND branchcp.owner_table_name(+) = 'HZ_PARTIES'
   AND branchcp.owner_table_id(+) = hzpbank.party_id
   AND branchcp.contact_point_type(+) = 'EFT'
   AND branchcp.status(+) = 'A'
   AND iao.ext_bank_account_id = ieb.ext_bank_account_id
   AND IAO.PRIMARY_FLAG='Y'
   )  x
ON x.payee_party_id = y.party_id
AND x.operating_unit=y.operating_unit
AND x.supplier_number=y.supplier_number
  WHERE  ( TRUNC (x.last_update_date)
                BETWEEN NVL (:p_last_modified_date_from,
                             TRUNC (x.last_update_date)
                            )
                    AND NVL (:p_last_modified_date_to,
                             TRUNC (x.last_update_date)
                            )
         OR TRUNC (y.last_update_date)
                BETWEEN NVL (:p_last_modified_date_from,
                             TRUNC (y.last_update_date)
                            )
                    AND NVL (:p_last_modified_date_to,
                             TRUNC (y.last_update_date)
                            )                          
         )
ORDER BY 2, 3]]></sqlStatement>
    <sqlStatement name="Q2"><![CDATA[SELECT TO_CHAR(:P_LAST_MODIFIED_DATE_FROM,'MM/DD/YYYY')          START_DATE,
                TO_CHAR(:P_LAST_MODIFIED_DATE_TO,'MM/DD/YYYY')            END_DATE
  FROM DUAL
]]></sqlStatement>
  </dataQuery>
  <dataStructure>
    <group name="G_SUPPLIERS" dataType="varchar2" source="Q1">
      <element name="OPERATING_UNIT" value="OPERATING_UNIT" />
      <element name="SUPPLIER_NAME" value="SUPPLIER_NAME" />
      <element name="SUPPLIER_NUMBER" value="SUPPLIER_NUMBER" />
      <element name="STATUS" value="STATUS" />
      <element name="BANK_NAME" value="BANK_NAME"></element>
      <element name="COUNTRY" value="COUNTRY" />
      <element name="BRANCH_NAME" value="BRANCH_NAME" />
      <element name="BRANCH_NUMBER" value="BRANCH_NUMBER" />
      <element name="ACCOUNT_NAME" value="ACCOUNT_NAME" />
      <element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER" />
      <element name="BIC" value="BIC"></element>
      <element name="IBAN" value="IBAN"></element>
      <element name="CURRENCY" value="CURRENCY" />
      <element name="PRIMARY" value="PRIMARY" />
      <element name="EFFECTIVE_DATE_FROM" value="EFFECTIVE_DATE_FROM" />
      <element name="END_DATE" value="END_DATE" />--Added by Shivani on 20-Nov-2013
      <element name="END_DATE1" value="END_DATE1" />--Added by Shivani on 20-Nov-2013
      <element name="CREATED_DATE" value="CREATED_DATE" />
      <element name="CREATED_BY" value="CREATED_BY" />
      <element name="UPDATED_DATE" value="UPDATED_DATE" />
      <element name="UPDATED_BY" value="UPDATED_BY" />
      <element name="SITE_NAME" value="SITE_NAME" />
      <element name="PAYMENT_METHOD" value="PAYMENT_METHOD"></element>
      <element name="STATUS1" value="STATUS1"></element>
      <element name="BANK_NAME1" value="BANK_NAME1"></element>
      <element name="COUNTRY1" value="COUNTRY1"></element>
      <element name="BRANCH_NAME1" value="BRANCH_NAME1"></element>
      <element name="BRANCH_NUMBER1" value="BRANCH_NUMBER1"></element>
      <element name="BANK_ACCOUNT_NAME1" value="BANK_ACCOUNT_NAME1"></element>
      <element name="ACCOUNT_NUMBER1" value="ACCOUNT_NUMBER1"></element>
      <element name="BIC1" value="BIC1"></element>
      <element name="IBAN1" value="IBAN1"></element>
      <element name="PRIMARY1" value="PRIMARY1"></element>
      <element name="CURRENCY1" value="CURRENCY1"></element>
      <element name="EFFECTIVE_DATE_FROM1" value="EFFECTIVE_DATE_FROM1"></element>
      <element name="EFFECTIVE_DATE_TO1" value="EFFECTIVE_DATE_TO1"></element>
      <element name="CREATED_DATE1" value="CREATED_DATE1"></element>
      <element name="CREATED_BY1" value="CREATED_BY1"></element>
      <element name="UPDATED_DATE1" value="UPDATED_DATE1"></element>
      <element name="UPDATED_BY1" value="UPDATED_BY1"></element>
    </group>
    <group name="G_DATES" dataType="varchar2" source="Q2">
      <element name="START_DATE" dataType="varchar2" value="START_DATE" />
      <element name="END_DATE" dataType="varchar2" value="END_DATE" />
    </group>
  </dataStructure>
</dataTemplate>

Enabling multiple org through back end by package in oracle apps R12.1.3 Enclosed the pkb and pks query here

By SQL Developer for one particular operating unit:

How to find which org_id is currently set in back end

set serveroutput on;
declare
gn_org_id NUMBER ;
begin
gn_org_id := apps.fnd_profile.VALUE('ORG_ID');
dbms_output.put_line(gn_org_id);
end;

How to set Org id in back end

 mo_global.init('ONT');  -- order management
 mo_global.set_policy_context('S','5758');
 fnd_global.apps_initialize(0,52123,660); -- pass in user_id, responsibility_id, and application_id

or
simply give

exec mo_global.set_policy_context('S',6359);



By Report-
Whenever the report needs to run for multiple operating unit then while submitting the report the user selects Ireland operating unit in parameter then it will goes to the report as organization id of that op unit and from the org id the query gets this org id and get executed.

create or replace PACKAGE XX_CUST_DQM_TRX_PKG AUTHID CURRENT_USER
AS

/*-- +==================================================================================+
-- | Name         : XX_CUST_DQM_TRX_PKG                |
-- |                                                                                  |
-- | Created By   : NA                                                    |
-- | Called From  : Concurrent Program                                                |
-- |                                                                                  |
-- | CHANGE HISTORY:                                                                  |
-- |                                                                                  |
-- |Version       Date       Name             Description                             |
-- |-------       --------   -------------    -------------------------               |
-- |Draft         NA       NA Initial Draft Version                 |
-- |1.1           22-Apr-2014  sathish          CR#205238 Commented p_ou for enabling MOAC in pkg itself|
-- +==================================================================================+*/

-- -----------------------------------------------------------------------------
-- Procedure for Order Details
-- -----------------------------------------------------------------------------
   PROCEDURE XX_order_inv_dtls(errbuf    OUT      VARCHAR2,
                               retcode   OUT      VARCHAR2,
                               P_CUST_NO IN       VARCHAR2
                              );
  PROCEDURE XX_order_count_dtls(errbuf    OUT      VARCHAR2,
                              retcode   OUT      VARCHAR2,
                              -- P_OU      IN       VARCHAR2, --Commented by sathish for request id#205238 on 09-Apr-2014
                              --P_CUST_NO IN       VARCHAR2
      P_CUST_NAME VARCHAR2
                              );
  END XX_CUST_DQM_TRX_PKG ;
/
-----------------------------------------------------------------------------------------------------------
*********************************************************************************
                                              PKS ENDS
*********************************************************************************
------------------------------------------------------------------------------------------------------------
create or replace
PACKAGE BODY XX_CUST_DQM_TRX_PKG
AS

/*-- +==================================================================================+
-- | Name         : XX_CUST_DQM_TRX_PKG                |
-- |                                                                                  |
-- | Created By   : NA                                                    |
-- | Called From  : Concurrent Program                                                |
-- |                                                                                  |
-- | CHANGE HISTORY:                                                                  |
-- |                                                                                  |
-- |Version       Date       Name             Description                             |
-- |-------       --------   -------------    -------------------------               |
-- |Draft         NA       NA      Initial Draft Version                   |
-- |1.1           23-Apr-2014  sathish        CR#205238 Commented p_ou for enabling MOAC in pkg itself|
-- |      added g_org_id variable for enabling moac|
-- +==================================================================================+*/

g_org_id hr_operating_units.organization_id%TYPE := SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10);

-- := fnd_profile.VALUE ('ORG_ID');

--Added g_org_id by sathish for request id#205238 on 09-Apr-2014

PROCEDURE XX_order_inv_dtls (errbuf    OUT      VARCHAR2,
                             retcode   OUT      VARCHAR2,
                             P_CUST_NO IN       VARCHAR2)
IS

CURSOR CUR_ORDER_DTLS(P_CUST_NO VARCHAR2) IS
SELECT DISTINCT
       ORDER_DTLS.OU,
       ORDER_DTLS.PARTY_NAME,
       ORDER_DTLS.CUSTOMER_NUMBER,
       ORDER_DTLS.ORDER_NUMBER,
       ORDER_DTLS.ORDERED_DATE,
       ORDER_DTLS.LOCATION,
       ORDER_DTLS.SITE_USE_CODE,
       ORDER_DTLS.INVNO,
       ORDER_DTLS.INVDATE,
       ORDER_DTLS.SITNO
FROM
(SELECT HR.NAME OU,
        HP.PARTY_NAME,
        hzca.account_number CUSTOMER_NUMBER,
        ORDER_NUMBER,ordered_date,
        location,
        hzcst.site_use_code,
        INVOICE_DET.TRX_NUMBER INVNO,
        INVOICE_DET.TRX_DATE INVDATE,party_site.PARTY_SITE_NUMBER SITNO
   FROM hz_cust_accounts hzca,
        hz_party_sites party_site  ,
        hz_locations loc           ,
        hz_cust_acct_sites_all hzcs,
        hz_cust_site_uses_all hzcst,
        hz_parties hp,
        oe_order_headers_all OEH,
        HR_OPERATING_UNITS HR,
       (SELECT ct_reference,TRX_NUMBER,TRX_DATE FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET
  WHERE hzca.cust_account_id = hzcs.cust_account_id
AND hzcs.cust_acct_site_id   =hzcst.cust_acct_site_id
AND invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
AND hzcs.party_site_id       = party_site.party_site_id
AND loc.location_id          = party_site.location_id
AND hzcs.cust_acct_site_id   =hzcst.cust_acct_site_id
AND hp.party_id              = hzca.party_id
AND hzcst.ORG_ID             = HR.organization_id
AND hzca.cust_account_id     =OEH.sold_to_org_id
AND hzcst.site_use_id        =OEH.invoice_to_org_id --Invoice to
AND hzcs.status              = 'A'
AND hzcst.status             = 'A'
AND trunc(ORDERED_DATE)   between trunc(sysdate-365) and trunc(sysdate)
UNION
 SELECT HR.NAME OU,
        HP.PARTY_NAME,
        hzca.account_number CUSTOMER_NUMBER,
        ORDER_NUMBER,ordered_date,
        location,
        hzcst.site_use_code,
        INVOICE_DET.TRX_NUMBER INVNO,
        INVOICE_DET.TRX_DATE INVDATE,party_site.PARTY_SITE_NUMBER SITNO
   FROM hz_cust_accounts hzca,
        hz_party_sites party_site  ,
        hz_locations loc           ,
        hz_cust_acct_sites_all hzcs,
        hz_cust_site_uses_all hzcst,
        hz_parties hp, oe_order_headers_all OEH,HR_OPERATING_UNITS HR,
        (SELECT ct_reference,TRX_NUMBER,TRX_DATE FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET
  WHERE hzca.cust_account_id = hzcs.cust_account_id
AND hzcs.cust_acct_site_id   =hzcst.cust_acct_site_id
AND invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
AND hzcs.party_site_id       = party_site.party_site_id
AND loc.location_id          = party_site.location_id
AND hzcs.cust_acct_site_id   =hzcst.cust_acct_site_id
AND hp.party_id              = hzca.party_id
AND hzcst.ORG_ID             = HR.organization_id
AND hzca.cust_account_id     =OEH.sold_to_org_id
AND hzcst.site_use_id        =OEH.ship_to_org_id --ship to
AND hzcs.status              = 'A'
AND hzcst.status             = 'A'
AND trunc(ORDERED_DATE)   between trunc(sysdate-365) and trunc(sysdate)
ORDER BY 4)
ORDER_DTLS
WHERE ORDER_DTLS.CUSTOMER_NUMBER =NVL(P_CUST_NO,ORDER_DTLS.CUSTOMER_NUMBER);

BEGIN

fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                      || '                                         :###XXogic Customer Last One Year Order Details###:                       '
                     );



  fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                      || '***********************************************************************************************************************************************************************************************'
                     );
   fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                        ||RPAD('Operating Unit',20)
                        ||RPAD('Customer Name',50)
                        ||RPAD('Cust No#',13)
                        ||RPAD('Site No#',13)
                        ||RPAD('OrderNo#',13)
                        ||RPAD('Order Date',13)
                        ||RPAD('Location',35)
                        ||RPAD('Site Usage',13)
                        ||RPAD('Invoice#',13)
                        ||RPAD('Inv Date',13)
                     );

   fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                      || '**********************************************************************************************************************************************************************************************'
                     );
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output, ' ');

   FOR crec IN CUR_ORDER_DTLS(P_CUST_NO)
   LOOP
     fnd_file.put_line (fnd_file.output,
                        RPAD (' ', 3, ' ')
                        ||RPAD(crec.ou,20)
                        ||RPAD(crec.PARTY_NAME,50)
                        ||RPAD(crec.CUSTOMER_NUMBER,13)
                        ||RPAD(crec.SITNO,13)
                        ||RPAD(crec.ORDER_NUMBER,13)
                        ||RPAD(crec.ORDERED_DATE,13)
                        ||RPAD(crec.LOCATION,35)
                        ||RPAD(crec.SITE_USE_CODE,13)
                        ||RPAD(crec.INVNO,13)
                        ||RPAD(crec.INVDATE,13)
                        );

   END LOOP;

   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output,
                      RPAD (' ', 10, ' ') || '***End of Order Details***'
                     );
END;

PROCEDURE XX_order_count_dtls (errbuf    OUT      VARCHAR2,
                               retcode   OUT      VARCHAR2,
                              -- P_OU      IN       VARCHAR2, --Commented by sathish for request id#205238 on 09-Apr-2014
                              -- P_CUST_NO IN       VARCHAR2,
      P_CUST_NAME VARCHAR2
      )
IS

CURSOR CUR_ORDER_DTLS_CNTS(g_org_id hr_operating_units.organization_id%TYPE, --Added by sathish for request id#205238 on 09-Apr-2014
--P_OU VARCHAR2, --Commented by sathish for request id#205238 on 09-Apr-2014
                           --P_CUST_NO VARCHAR2,
  P_CUST_NAME VARCHAR2) IS
SELECT TRAN_DTLS.NAME OperatingUnit,
       TRAN_DTLS.party_number Party_Number,
       TRAN_DTLS.Customer_Name,TRAN_DTLS.Customer_Number,
       TRAN_DTLS.party_site_number,TRAN_DTLS.location,
       TRAN_DTLS.site_use,
       TRAN_DTLS.ORDERNO#,
       TRAN_DTLS.INV#,
       TRAN_DTLS.address1                 ,
       TRAN_DTLS.address2                 ,
       TRAN_DTLS.address3                 ,
       TRAN_DTLS.address4                 ,
       TRAN_DTLS.city                     ,
       TRAN_DTLS.state                    ,
       TRAN_DTLS.postal_code              ,
       TRAN_DTLS.country  ,
       TRAN_DTLS.organization_id ----Added by sathish for request id#205238 on 09-Apr-2014
FROM
(SELECT DISTINCT CUST_DTLS.name,
                CUST_DTLS.Customer_Number,
CUST_DTLS.party_number,
                CUST_DTLS.Customer_Name,
                CUST_DTLS.party_site_number,
                CUST_DTLS.location,
                CUST_DTLS.site_use,
               count(oola.ORDER_NUMBER)ORDERNO#,
               count(INVOICE_DET.TRX_NUMBER)INV#,
       CUST_DTLS.address1                 ,
                CUST_DTLS.address2                 ,
CUST_DTLS.address3                 ,
CUST_DTLS.address4                 ,
                CUST_DTLS.city                     ,
                CUST_DTLS.state                    ,
                CUST_DTLS.postal_code              ,
                CUST_DTLS.country   ,
CUST_DTLS.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
FROM
(SELECT INVOICE_to_org_id,sold_to_org_id,ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL WHERE trunc(ORDERED_DATE)  between trunc(sysdate-365) and trunc(sysdate)) OOLA,
(SELECT ct_reference,TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET,
(SELECT distinct
        party.party_number,
        substrb(party.party_name,1,50) Customer_Name ,
        cust.account_number Customer_Number,
        party_site.party_site_number,
        location,
        substrb(look.meaning, 1, 8) Site_Use,
        site_uses.site_use_id ,cust.cust_account_id,name,
        loc.address1                 ,
loc.address2                 ,
loc.address3                 ,
loc.address4                 ,
        loc.city                     ,
        loc.state                    ,
        loc.postal_code              ,
        loc.country     ,
hr.organization_id             --Added by sathish for request id#205238 on 09-Apr-2014
   FROM ar_lookups look           ,
  ar_lookups look_status          ,
  hz_cust_accounts_all cust       ,
  hz_parties party                ,
  hz_cust_site_uses_all site_uses ,
  hz_cust_acct_sites_all acct_site,
  hz_party_sites party_site       ,
  hz_locations loc,HR_OPERATING_UNITS HR
  WHERE cust.cust_account_id    = acct_site.cust_account_id
AND cust.party_id               = party.party_id
AND acct_site.party_site_id     = party_site.party_site_id(+)
AND loc.location_id(+)          = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+)         = 'SITE_USE_CODE'
AND look.lookup_code(+)         = site_uses.site_use_code
AND look_status.lookup_type(+)  = 'CODE_STATUS'
AND look_status.lookup_code(+)  = NVL(cust.status, 'A')
AND site_uses.SITE_USE_CODE     ='BILL_TO'
AND site_uses.ORG_ID            = HR.organization_id
AND site_uses.STATUS            = 'A'
AND cust.status = 'A'
) CUST_DTLS
WHERE CUST_DTLS.site_use_id     = OOLA.INVOICE_to_org_id(+)
AND   CUST_DTLS.cust_account_id = OOLA.sold_to_org_id(+)
AND   invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
GROUP BY
CUST_DTLS.name,
CUST_DTLS.Customer_Number,
CUST_DTLS.party_number,
CUST_DTLS.Customer_Name,
CUST_DTLS.party_site_number,
CUST_DTLS.location,
CUST_DTLS.site_use,
CUST_DTLS.address1                 ,
CUST_DTLS.address2                 ,
CUST_DTLS.address3                 ,
CUST_DTLS.address4                 ,
CUST_DTLS.city                     ,
CUST_DTLS.state                    ,
CUST_DTLS.postal_code              ,
CUST_DTLS.country   ,
CUST_DTLS.organization_id --Added by sathish for request id#205238 on 09-Apr-2014
UNION
SELECT DISTINCT CUST_DTLS.name,
                CUST_DTLS.Customer_Number,
CUST_DTLS.Party_Number,
                CUST_DTLS.Customer_Name,
                CUST_DTLS.party_site_number,
                CUST_DTLS.location,
                CUST_DTLS.site_use,
               count(oola.ORDER_NUMBER)ORDERNO#,
               count(INVOICE_DET.TRX_NUMBER)INV#,
      CUST_DTLS.address1                 ,
CUST_DTLS.address2                 ,
                CUST_DTLS.address3                 ,
                CUST_DTLS.address4                 ,
                CUST_DTLS.city                     ,
                CUST_DTLS.state                    ,
                CUST_DTLS.postal_code              ,
                CUST_DTLS.country                  ,
CUST_DTLS.organization_id   --Added by sathish for request id#205238 on 09-Apr-2014
FROM
(SELECT ship_to_org_id,sold_to_org_id,ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL WHERE trunc(ORDERED_DATE)  between trunc(sysdate-365) and trunc(sysdate)) OOLA,
(SELECT ct_reference,TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL) INVOICE_DET,
(SELECT distinct
        party.party_number,
        substrb(party.party_name,1,50) Customer_Name ,
        cust.account_number Customer_Number,
        party_site.party_site_number,
        location,
        substrb(look.meaning, 1, 8) Site_Use,
        site_uses.site_use_id ,cust.cust_account_id,name,
loc.address1                 ,
loc.address2                 ,
        loc.address3                 ,
        loc.address4                 ,
        loc.city                     ,
        loc.state                    ,
        loc.postal_code              ,
        loc.country             ,
hr.organization_id             --Added by sathish for request id#205238 on 09-Apr-2014
   FROM ar_lookups look           ,
  ar_lookups look_status          ,
  hz_cust_accounts_all cust       ,
  hz_parties party                ,
  hz_cust_site_uses_all site_uses ,
  hz_cust_acct_sites_all acct_site,
  hz_party_sites party_site       ,
  hz_locations loc,HR_OPERATING_UNITS HR
  WHERE cust.cust_account_id    = acct_site.cust_account_id
AND cust.party_id               = party.party_id
AND acct_site.party_site_id     = party_site.party_site_id(+)
AND loc.location_id(+)          = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+)         = 'SITE_USE_CODE'
AND look.lookup_code(+)         = site_uses.site_use_code
AND look_status.lookup_type(+)  = 'CODE_STATUS'
AND look_status.lookup_code(+)  = NVL(cust.status, 'A')
AND site_uses.SITE_USE_CODE     ='SHIP_TO'
AND site_uses.ORG_ID            = HR.organization_id
AND site_uses.STATUS            = 'A'
AND cust.status = 'A'
) CUST_DTLS
WHERE CUST_DTLS.site_use_id     = OOLA.ship_to_org_id(+)
AND   CUST_DTLS.cust_account_id = OOLA.sold_to_org_id(+)
AND   invoice_det.ct_reference(+)=to_char(ORDER_NUMBER)
GROUP BY
CUST_DTLS.name,
CUST_DTLS.Customer_Number,
CUST_DTLS.Party_Number,
CUST_DTLS.Customer_Name,
CUST_DTLS.party_site_number,
CUST_DTLS.location,
CUST_DTLS.site_use,
CUST_DTLS.address1                 ,
CUST_DTLS.address2                 ,
CUST_DTLS.address3                 ,
CUST_DTLS.address4                 ,
CUST_DTLS.city                     ,
CUST_DTLS.state                    ,
CUST_DTLS.postal_code              ,
CUST_DTLS.country   ,
CUST_DTLS.organization_id) TRAN_DTLS  --Added CUST_DTLS.organization_id by sathish for request id#205238 on 09-Apr-2014
WHERE TRAN_DTLS.CUSTOMER_NAME  LIKE NVL(P_CUST_NAME,TRAN_DTLS.CUSTOMER_NAME)
and TRAN_DTLS.organization_id=NVL(g_org_id,TRAN_DTLS.organization_id) --Added by sathish for request id#205238 on 09-Apr-2014
--and TRAN_DTLS.CUSTOMER_NUMBER =NVL(P_CUST_NO,TRAN_DTLS.CUSTOMER_NUMBER)
--AND   TRAN_DTLS.NAME in ('US Operating Unit','IE Operating Unit')
--AND   TRAN_DTLS.NAME = NVL(P_OU,TRAN_DTLS.NAME) --Commented by sathish for request id#205238 on 09-Apr-2014
order by 4,6;

BEGIN

--Added fnd_file.put_line by sathish for request id#205238 on 09-Apr-2014 for getting operating unit value
                                                               
      fnd_file.put_line (fnd_file.LOG, 'g_org_id ' || g_org_id);

/*
fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                      || '                                         :###XXogic Customer Summary  Report ###:                       '
                     );



  fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                      || '***********************************************************************************************************************************************************************************************************************************************************************************************************************************************************'
                     ); */
   
   fnd_file.put_line (fnd_file.output,
                       --  RPAD (' ', 3, ' ')
                        RPAD('Operating Unit',20)
                        ||CHR(9)||RPAD('Party Number',15)
                        ||CHR(9)||RPAD('Customer Name',50)
                        ||CHR(9)||RPAD('Cust No#',13)
                        ||CHR(9)||RPAD('Site No#',13)
                        ||CHR(9)||RPAD('Location#',45)
                        ||CHR(9)||RPAD('Site Usage',13)
                        ||CHR(9)||RPAD('Orders Count#',20)
                        ||CHR(9)||RPAD('Invoice Count#',20)
||CHR(9)||RPAD('Address1',50)
||CHR(9)||RPAD('Address2',50)
||CHR(9)||RPAD('Address3',50)
||CHR(9)||RPAD('Address4',50)
                        ||CHR(9)||RPAD('City',35)
                        ||CHR(9)||RPAD('Zip Code',15)
                        ||CHR(9)||RPAD('State',25)
                        ||CHR(9)||RPAD('Country',10)
                     );

  /* fnd_file.put_line (fnd_file.output,
                         RPAD (' ', 3, ' ')
                      || '***********************************************************************************************************************************************************************************************************************************************************************************************************************************************************'
                     );
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output, ' ');*/

   FOR crec IN CUR_ORDER_DTLS_CNTS(g_org_id, --Added by sathish for request id#205238 on 09-Apr-2014
   --P_OU, --Commented by sathish for request id#205238 on 09-Apr-2014
  --P_CUST_NO,
  P_CUST_NAME)
   LOOP
     fnd_file.put_line (fnd_file.output,
                        --RPAD (' ', 3, ' ')
                        RPAD(crec.OperatingUnit,20)
                        ||CHR(9)||RPAD(crec.Party_Number,15)
                        ||CHR(9)||RPAD(crec.Customer_Name,50)
                        ||CHR(9)||RPAD(crec.CUSTOMER_NUMBER,13)
                        ||CHR(9)||RPAD(crec.party_site_number,13)
                        ||CHR(9)||RPAD(crec.LOCATION,45)
                        ||CHR(9)||RPAD(crec.site_use,13)
                        ||CHR(9)||RPAD(crec.ORDERNO#,20)
                        ||CHR(9)||RPAD(crec.INV#,20)
||CHR(9)||RPAD(nvl(crec.address1,' '),50)
||CHR(9)||RPAD(nvl(crec.address2,' '),50)
||CHR(9)||RPAD(nvl(crec.address3,' '),50)
||CHR(9)||RPAD(nvl(crec.address4,' '),50)
                        ||CHR(9)||RPAD(nvl(crec.City,' '),35)
                        ||CHR(9)||RPAD(nvl(crec.postal_code,' '),15)
                        ||CHR(9)||RPAD(nvl(crec.State,' '),25)
                        ||CHR(9)||RPAD(crec.country,10)
                        );

   END LOOP;
/*
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output, ' ');
   fnd_file.put_line (fnd_file.output,
                      RPAD (' ', 10, ' ') || '***End of Order Details***'
                     );*/
END;

END XX_CUST_DQM_TRX_PKG;
/