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;
/

Add CAST Operator in sql query so that the output goes to excel will treat as text instead of some special characters on scientific notation i.e e^1 like that

Add CAST Operator in sql query so that the output goes to excel will treat as text instead of some special characters on scientific notation i.e e^1

CAST(x.bank_account_num AS CHAR(28)) AS account_number

cursor with parameters with prompt messages in pl/sql program

REM dbdrv: none
        /*=======================================================================+
  |  Copyright (c) 2009 Oracle Corporation Redwood Shores, California, USA|
  |                            All rights reserved.                       |
  +=======================================================================*/

 /* $Header: poxrespo.sql 120.0.12010000.4 2010/06/09 00:12:31 vrecharl noship $ */
SET SERVEROUTPUT ON
SET VERIFY OFF;


/* PLEASE READ NOTE 390023.1 CAREFULLY BEFORE EXECUTING THIS SCRIPT.

 This script will:
* reset the document to incomplete/requires reapproval status.
* delete/update action history as desired (refere note 390023.1 for more details).
* abort all the related workflows

* If there is a distribution with wrong encumbrance amount related to this PO,
* it will: skip the reset action on the document.
*/

set serveroutput on size 100000
prompt
prompt
accept sql_po_number prompt 'Please enter the PO number to reset : ';
accept sql_org_id default NULL prompt 'Please enter the organization id to which the PO belongs (Default NULL) : ';
accept delete_act_hist prompt 'Do you want to delete the action history since the last approval ? (Y/N) ';
prompt


DECLARE

/* select only the POs which are in preapproved, in process state and are not finally closed
   cancelled */
 
CURSOR potoreset(po_number varchar2, x_org_id number) is
SELECT wf_item_type, wf_item_key, po_header_id, segment1,
       revision_num, type_lookup_code,approved_date
FROM po_headers_all
WHERE segment1 = po_number
and NVL(org_id,-99) = NVL(x_org_id,-99)
-- bug 5015493: Need to allow reset of blankets and PPOs also.
-- and type_lookup_code = 'STANDARD'
and authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
and NVL(cancel_flag, 'N') = 'N'
and NVL(closed_code, 'OPEN') <> 'FINALLY_CLOSED';

/* select the max sequence number with NULL action code */

CURSOR maxseq(id number, subtype po_action_history.object_sub_type_code%type) is
SELECT nvl(max(sequence_num), 0)
FROM   po_action_history
WHERE  object_type_code IN ('PO', 'PA')
AND    object_sub_type_code = subtype
AND    object_id = id
AND    action_code is NULL;

/* select the max sequence number with submit action */

CURSOR poaction(id number, subtype po_action_history.object_sub_type_code%type) is
SELECT nvl(max(sequence_num), 0)
FROM   po_action_history
WHERE  object_type_code IN ('PO', 'PA')
AND    object_sub_type_code = subtype
AND    object_id = id
AND    action_code = 'SUBMIT';

cursor wfstoabort(st_item_type varchar2,st_item_key varchar2) is
select level,item_type,item_key,end_date
from wf_items
start with
    item_type = st_item_type and
    item_key =  st_item_key
connect by
    prior item_type = parent_item_type and
    prior item_key = parent_item_key
order by level desc;

wf_rec wfstoabort%ROWTYPE;

submitseq po_action_history.sequence_num%type;
nullseq po_action_history.sequence_num%type;

 x_organization_id number ;
 x_po_number varchar2(20);
 po_enc_flag varchar2(1);
 x_open_notif_exist varchar2(1);
 pos potoreset%ROWTYPE;

 x_progress varchar2(500);
 x_cont varchar2(10);
 x_active_wf_exists varchar2(1);
 l_delete_act_hist varchar2(1);
 l_change_req_exists varchar2(1);
 l_res_seq po_action_history.sequence_num%TYPE;
 l_sub_res_seq po_action_history.sequence_num%TYPE;
 l_res_act po_action_history.action_code%TYPE;
 l_del_res_hist varchar2(1);


 /* For encumbrance actions */

 NAME_ALREADY_USED EXCEPTION;
 PRAGMA Exception_Init(NAME_ALREADY_USED,-955);
 X_STMT VARCHAR2(2000);
 disallow_script VARCHAR2(1);

TYPE enc_tbl_number is TABLE OF NUMBER;
TYPE enc_tbl_flag   is TABLE OF VARCHAR2(1);

l_dist_id       enc_tbl_number;
l_enc_flag     enc_tbl_flag;
l_enc_amount   enc_tbl_number;
l_gl_amount     enc_tbl_number;
l_manual_cand   enc_tbl_flag;
l_req_dist_id   enc_tbl_number;
l_req_enc_flag enc_tbl_flag;
l_req_enc_amount enc_tbl_number;
l_req_gl_amount enc_tbl_number;
l_req_qty_bill_del   enc_tbl_number;
l_rate_table       enc_tbl_number;
l_price_table       enc_tbl_number;
l_qty_ordered_table enc_tbl_number;
l_req_price_table   enc_tbl_number;
l_req_encumbrance_flag varchar2(1);
l_purch_encumbrance_flag varchar2(1);
l_remainder_qty         NUMBER;
l_bill_del_amount   NUMBER;
l_req_bill_del_amount   NUMBER;
l_qty_bill_del     NUMBER;
l_timestamp     date;
l_eff_quantity NUMBER;
l_rate         NUMBER;
l_price         NUMBER;
l_ordered_quantity NUMBER;
l_tax       NUMBER;
l_amount       NUMBER;
l_precision     fnd_currencies.precision%type;
l_min_acc_unit fnd_currencies.minimum_accountable_unit%TYPE;
l_approved_flag po_line_locations_all.approved_flag%TYPE;
i number;
j number;
k number;

BEGIN

select '&delete_act_hist'
into l_delete_act_hist
from dual;

select &sql_org_id
  into x_organization_id
  from dual;

select '&sql_po_number'
  into x_po_number
  from dual;


x_progress := '010: start';

   begin
   select 'Y'
    into x_open_notif_exist
    from dual
    where exists (select 'open notifications'
   from wf_item_activity_statuses wias,
wf_notifications wfn,
po_headers_all poh
   where wias.notification_id is not null
     and wias.notification_id = wfn.group_id
     and wfn.status = 'OPEN'
     and wias.item_type = 'POAPPRV'
     and wias.item_key = poh.wf_item_key
     and NVL(poh.org_id,-99) = NVL(x_organization_id,-99)
     and poh.segment1=x_po_number
     and poh.authorization_status IN ('IN PROCESS', 'PRE-APPROVED'));
   exception
   when NO_DATA_FOUND then
     null;
   end;
   
x_progress := '020: selected open notif';

if (x_open_notif_exist = 'Y') then
   dbms_output.put_line('  ');
   dbms_output.put_line('An Open notification exists for this document, you may want to use the notification to process this document. Do not commit if you wish to use the notification');
end if;  

begin
select 'Y'
  into l_change_req_exists
  from dual
  where exists (select 'po with change request'
   from po_headers_all h
 where h.segment1 = x_po_number
   and nvl(h.org_id, -99) = NVL(x_organization_id, -99)
   and h.change_requested_by in ('REQUESTER', 'SUPPLIER'));
exception
   when NO_DATA_FOUND then
     null;
end;

if (l_change_req_exists = 'Y') then
   dbms_output.put_line('  ');
   dbms_output.put_line('ATTENTION !!! There is an open change request against this PO. You should respond to the notification for the same.');
   return;
--   dbms_output.put_line('If you are running this script unaware of the change request, Please ROLLBACK');
end if;
   
open potoreset(x_po_number, x_organization_id);

fetch potoreset into pos;
if potoreset%NOTFOUND then
   dbms_output.put_line('No PO with PO Number '||x_po_number ||
    ' exists in org '||to_char(x_organization_id)
|| ' which requires to be reset');
   return;
end if;
close potoreset;

 x_progress := '030 checking enc action ';

 -- If there exists any open shipment with one of its distributions reserved, then
 -- 1. For a Standard PO, check whether the present Encumbrance amount on the distribution
 --    is correct or not. If its not correct do not reset the document.
 -- 2. For a Blanket PO (irrespective of Encumbrance enabled or not), reset the document.
 -- 3. For a Planned PO, always do not reset the document.
 disallow_script := 'N';
 begin
  SELECT 'Y'
  INTO   disallow_script
  FROM   dual
  WHERE  EXISTS (SELECT 'Wrong Encumbrance Amount'
                 FROM   po_headers_all h,
                        po_lines_all l,
                        po_line_locations_all s,
                        po_distributions_all d
                 WHERE  s.line_location_id = d.line_location_id
                        AND l.po_line_id = s.po_line_id
                        AND h.po_header_id = d.po_header_id
                        AND d.po_header_id = pos.po_header_id
                        AND l.matching_basis = 'QUANTITY'
                        AND Nvl(d.encumbered_flag, 'N') = 'Y'
                        AND Nvl(s.cancel_flag, 'N') = 'N'
                        AND Nvl(s.closed_code, 'OPEN') <> 'FINALLY CLOSED'
                        AND Nvl(d.prevent_encumbrance_flag, 'N') = 'N'
                        AND d.budget_account_id IS NOT NULL
                        AND Nvl(s.shipment_type,'BLANKET') = 'STANDARD'
                        AND (Round(Nvl(d.encumbered_amount, 0), 2) <>
                             Round((s.price_override * d.quantity_ordered *
                                    Nvl(d.rate, 1) + Nvl(d.nonrecoverable_tax, 0) *
                                    Nvl(d.rate, 1) ), 2))
                 UNION
                 SELECT 'Wrong Encumbrance Amount'
                 FROM   po_headers_all h,
                        po_lines_all l,
                        po_line_locations_all s,
                        po_distributions_all d
                 WHERE  s.line_location_id = d.line_location_id
                        AND l.po_line_id = s.po_line_id
                        AND h.po_header_id = d.po_header_id
                        AND d.po_header_id = pos.po_header_id
                        AND l.matching_basis = 'AMOUNT'
                        AND Nvl(d.encumbered_flag, 'N') = 'Y'
                        AND Nvl(s.cancel_flag, 'N') = 'N'
                        AND Nvl(s.closed_code, 'OPEN') <> 'FINALLY CLOSED'
                        AND Nvl(d.prevent_encumbrance_flag, 'N') = 'N'
                        AND d.budget_account_id IS NOT NULL
                        AND Nvl(s.shipment_type,'BLANKET') = 'STANDARD'
                        AND (Round(Nvl(d.encumbered_amount, 0), 2) <>
                             Round((d.amount_ordered + Nvl(d.nonrecoverable_tax, 0) ) *
                                    Nvl(d.rate, 1),2))
                 UNION
                 SELECT 'Wrong Encumbrance Amount'
                 FROM   po_headers_all h,
                        po_lines_all l,
                        po_line_locations_all s,
                        po_distributions_all d
                 WHERE  s.line_location_id = d.line_location_id
                        AND l.po_line_id = s.po_line_id
                        AND h.po_header_id = d.po_header_id
                        AND d.po_header_id = pos.po_header_id
                        AND Nvl(d.encumbered_flag, 'N') = 'Y'
                        AND Nvl(d.prevent_encumbrance_flag, 'N') = 'N'
                        AND d.budget_account_id IS NOT NULL
                        AND Nvl(s.shipment_type,'BLANKET') = 'PLANNED');
 EXCEPTION
 when NO_DATA_FOUND THEN
   NULL;
 end;

 if disallow_script = 'Y' then
    dbms_output.put_line('This PO has at least one distribution with wrong Encumbrance amount.');
    dbms_output.put_line('Hence this PO can not be reset.');
    return;
 end if;              

      dbms_output.put_line('Processing '||pos.type_lookup_code
                            ||' PO Number: '
                            ||pos.segment1);
      dbms_output.put_line('......................................');
           
      begin
       select 'Y'
         into x_active_wf_exists
         from wf_items wfi
        where wfi.item_type = pos.wf_item_type
     and wfi.item_key = pos.wf_item_key
 and wfi.end_date is null;

      exception
      when NO_DATA_FOUND then
      x_active_wf_exists := 'N';
      end;

      if (x_active_wf_exists = 'Y') then
         dbms_output.put_line('Aborting Workflow...');
         open wfstoabort(pos.wf_item_type,pos.wf_item_key);
         loop
         fetch wfstoabort into wf_rec;
if wfstoabort%NOTFOUND then
   close wfstoabort;
   exit;
end if;

if (wf_rec.end_date is null) then
BEGIN
  WF_Engine.AbortProcess(wf_rec.item_type, wf_rec.item_key);
         EXCEPTION
           WHEN OTHERS THEN
                dbms_output.put_line(' workflow not aborted :'
||wf_rec.item_type ||'-'||wf_rec.item_key);
               
         END;

end if;
end loop;
      end if;

      dbms_output.put_line('Updating PO Status..');
      UPDATE po_headers_all
         SET authorization_status = decode(pos.approved_date, NULL, 'INCOMPLETE',
                                        'REQUIRES REAPPROVAL'),
          wf_item_type = NULL,
          wf_item_key = NULL,
 approved_flag = decode(pos.approved_date, NULL, 'N', 'R')
       WHERE po_header_id = pos.po_header_id;

      OPEN  maxseq(pos.po_header_id, pos.type_lookup_code);
      FETCH maxseq into nullseq;
      CLOSE maxseq;

      OPEN  poaction(pos.po_header_id, pos.type_lookup_code);
      FETCH poaction into submitseq;
      CLOSE poaction;
      IF nullseq > submitseq THEN
       
if nvl(l_delete_act_hist,'N') = 'N' then
      Update po_action_history
         set action_code = 'NO ACTION',
             action_date = trunc(sysdate),
             note = 'updated by reset script on '||to_char(trunc(sysdate))
           WHERE object_id = pos.po_header_id
             AND  object_type_code = decode(pos.type_lookup_code,
       'STANDARD','PO',
   'PLANNED', 'PO', --future plan to enhance for planned PO
   'PA')
             AND object_sub_type_code = pos.type_lookup_code
             AND sequence_num = nullseq
             AND action_code is NULL;
        else

  Delete po_action_history
   where object_id = pos.po_header_id
     and object_type_code = decode(pos.type_lookup_code,
       'STANDARD','PO',
   'PLANNED', 'PO', --future plan to enhance for planned PO
   'PA')
     and object_sub_type_code = pos.type_lookup_code  
     and sequence_num >= submitseq
     and sequence_num <= nullseq;

end if;

      END IF;

      dbms_output.put_line('Done Approval Processing.');
     
 select nvl(purch_encumbrance_flag,'N')
   into l_purch_encumbrance_flag
   from financials_system_params_all fspa
   where NVL(fspa.org_id,-99) = NVL(x_organization_id,-99);
 
   if (l_purch_encumbrance_flag='N')
      -- bug 5015493 : Need to allow reset for blankets also
      OR (pos.type_lookup_code = 'BLANKET') then
 
      if (pos.type_lookup_code = 'BLANKET') then
      dbms_output.put_line('document reset successfully');
      dbms_output.put_line('If you are using Blanket encumbrance, Please ROLLBACK, else COMMIT');
      else
      dbms_output.put_line('document reset successfully');
      dbms_output.put_line('please COMMIT data');
      end if;
      return;
   end if;

-- reserve action history stuff
-- check the action history and delete any reserve to submit actions if all the distributions
-- are now unencumbered, this should happen only if we are deleting the action history

if l_delete_act_hist = 'Y' then

   -- first get the last sequence and action code from action history
   begin
      select sequence_num, action_code
        into l_res_seq, l_res_act
from po_action_history pah
WHERE pah.object_id = pos.po_header_id
          AND  pah.object_type_code = decode(pos.type_lookup_code,
       'STANDARD','PO',
   'PLANNED', 'PO', --future plan to enhance for planned PO
   'PA')
          AND pah.object_sub_type_code = pos.type_lookup_code
 AND sequence_num in (select max(sequence_num)
  from po_action_history pah1
where pah1.object_id = pah.object_id
           AND  pah1.object_type_code =pah.object_type_code
                   AND  pah1.object_sub_type_code =pah.object_sub_type_code);
   exception
   when TOO_MANY_ROWS then
     dbms_output.put_line('action history needs to be corrected separately ');
   when NO_DATA_FOUND then
     null;
   end;

   -- now if the last action is reserve get the last submit action sequence

   if (l_res_act = 'RESERVE') then
   begin
      select max(sequence_num)
        into l_sub_res_seq
       from  po_action_history pah
      where action_code = 'SUBMIT'
        and  pah.object_id = pos.po_header_id
        and  pah.object_type_code = decode(pos.type_lookup_code,
          'STANDARD','PO',
      'PLANNED', 'PO', --future plan to enhance for planned PO
      'PA')
        and pah.object_sub_type_code = pos.type_lookup_code;
   exception
   when NO_DATA_FOUND then
     null;
   end;

      -- check if we need to delete the action history, ie. if all the distbributions
      -- are unreserved

      if ((l_sub_res_seq is not null ) and (l_res_seq > l_sub_res_seq)) then

begin
         select 'Y'
            into l_del_res_hist
           from dual
           where not exists (select 'encumbered dist'
      from po_distributions_all pod
where pod.po_header_id = pos.po_header_id
 and nvl(pod.encumbered_flag,'N') = 'Y'
 and nvl(pod.prevent_encumbrance_flag,'N')='N');
         exception
when NO_DATA_FOUND then
   l_del_res_hist := 'N';
         end;

         if l_del_res_hist = 'Y' THEN

   dbms_output.put_line('deleting reservation action history ... ');

            delete po_action_history pah
              where pah.object_id = pos.po_header_id
                and  pah.object_type_code = decode(pos.type_lookup_code,
              'STANDARD','PO',
          'PLANNED', 'PO', --future plan to enhance for planned PO
          'PA')
                and pah.object_sub_type_code = pos.type_lookup_code
                and sequence_num >= l_sub_res_seq
                and sequence_num <= l_res_seq;
         end if;
     
      end if; -- l_res_seq > l_sub_res_seq

   end if;

end if;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('some exception occured '||sqlerrm||' rolling back'||x_progress);
  rollback;
END;
/