SELECT HOU.NAME Operating_Unit,
PV.VENDOR_NAME Supplier_Name,
PV.SEGMENT1 Supplier_Number,
PV.NUM_1099 Tax_Identification_Number,
PV.VAT_REGISTRATION_NUM GST_HST_Number,
PV.VENDOR_TYPE_LOOKUP_CODE Supplier_Type,
--'DUNS_Number-Header',
PV.CREATION_DATE Creation_Date_Header,
PV.CREATED_BY Created_By_Header,
PV.LAST_UPDATE_DATE Update_Date_Header,
PV.LAST_UPDATED_BY Updated_By_Header,
PVS.VENDOR_SITE_CODE Supplier_Site,
PVS.ADDRESS_LINE1||','||
PVS.ADDRESS_LINES_ALT||','||
PVS.ADDRESS_LINE2||','||
PVS.ADDRESS_LINE3||','||
PVS.CITY||','||
PVS.STATE||','||
PVS.ZIP||','||
PVS.COUNTRY||','||
PVS.AREA_CODE||','||
PVS.PHONE ADDRESS,
PVS.DUNS_Number DUNS_Number_Site,
PVS.CREATION_DATE Creation_Date_Site,
PVS.CREATED_BY Created_By_Site,
PVS.LAST_UPDATE_DATE Update_Date_Site,
PVS.LAST_UPDATED_BY Updated_By_Site,
DECODE(PVS.INACTIVE_DATE,NULL,'Active','Inactive') STATUS,
--PVS.BANK_ACCOUNT_NAME SUPPSITE_BANK_ACCT,
--PVS.BANK_ACCOUNT_NUM SUPPSITE_BANK_NUM,
ABB.BANK_NAME Bank_Name,
ABB.BANK_NUMBER BANK_NUMBER,
ABB.BANK_BRANCH_NAME BANK_BRANCH,
ABB.BANK_NUM BANK_BRANCH_NUM,
ATT.NAME Payment_Terms,
PVS.PAYMENT_METHOD_LOOKUP_CODE Payment_Method
FROM PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
HR_OPERATING_UNITS HOU,
AP_BANK_ACCOUNTS ABA,
AP_BANK_BRANCHES ABB,
AP_TERMS_TL ATT,
PO_VENDOR_CONTACTS PVC
WHERE PV.VENDOR_ID=PVS.VENDOR_ID
AND PVS.ORG_ID=HOU.ORGANIZATION_ID
AND PVS.BANK_ACCOUNT_NAME=ABA.BANK_ACCOUNT_NAME(+)
AND PVS.BANK_ACCOUNT_NUM=ABA.BANK_ACCOUNT_NUM(+)
AND ABA.BANK_BRANCH_ID=ABB.BANK_BRANCH_ID(+)
AND PVC.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PVS.TERMS_ID=ATT.TERM_ID(+)
AND ATT.LANGUAGE=USERENV('LANG')
AND (to_date(PV.last_update_date,'DD-MON-RRRR') BETWEEN to_date(:P_FROM_UPDATE_DATE,'DD-MON-RRRR') AND to_date(:P_TO_UPDATE_DATE,'DD-MON-RRRR')
OR to_date(PVS.last_update_date,'DD-MON-RRRR') BETWEEN to_date(:P_FROM_UPDATE_DATE,'DD-MON-RRRR') AND to_date(:P_TO_UPDATE_DATE,'DD-MON-RRRR')
OR to_date(ABA.last_update_date,'DD-MON-RRRR') BETWEEN to_date(:P_FROM_UPDATE_DATE,'DD-MON-RRRR') AND to_date(:P_TO_UPDATE_DATE,'DD-MON-RRRR'))
--AND (PV.last_update_date BETWEEN :CP_FROM_UPDATE_DATE AND :CP_TO_UPDATE_DATE
-- OR PVS.last_update_date BETWEEN :CP_FROM_UPDATE_DATE AND :CP_TO_UPDATE_DATE
-- OR PVC.last_update_date BETWEEN :CP_FROM_UPDATE_DATE AND :CP_TO_UPDATE_DATE
-- OR ABA.last_update_date BETWEEN :CP_FROM_UPDATE_DATE AND :CP_TO_UPDATE_DATE)
AND (PV.LAST_UPDATED_BY = NVL(:CP_UPDATED_BY,PV.LAST_UPDATED_BY)
OR PVS.LAST_UPDATED_BY = NVL(:CP_UPDATED_BY,PVS.LAST_UPDATED_BY)
OR PVC.LAST_UPDATED_BY = NVL(:CP_UPDATED_BY,PVC.LAST_UPDATED_BY)
OR ABA.LAST_UPDATED_BY = NVL(:CP_UPDATED_BY,ABA.LAST_UPDATED_BY))
AND HOU.NAME=NVL(:CP_NAME,HOU.NAME);
