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>
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>
No comments:
Post a Comment