View - PO_AP_RECEIPT_MATCH_V

 SELECT RT.TRANSACTION_ID RCV_TRANSACTION_ID

, RT.TRANSACTION_DATE RCV_TRANSACTION_DATE
, RT.QUANTITY RCV_TRANSACTION_QUANTITY
, SH.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID
, SH.RECEIPT_NUM RECEIPT_NUMBER
, SL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID
, SL.LINE_NUM RCV_SHIPMENT_LINE_NUMBER
, RT.PO_HEADER_ID PO_HEADER_ID
, NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBER
, /*9481666 - CLM*/ RT.PO_LINE_ID PO_LINE_ID
, NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUMBER
, /*9481666 - CLM*/ RT.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID
, PS.SHIPMENT_NUM PO_LINE_LOCATION_NUMBER
, NVL(PS.PRICE_OVERRIDE
, PL.UNIT_PRICE) PO_UNIT_PRICE
, RT.PO_RELEASE_ID PO_RELEASE_ID
, PR.RELEASE_NUM PO_RELEASE_NUM
, PL.UNIT_MEAS_LOOKUP_CODE PO_UOM_LOOKUP_CODE
, RT.UNIT_OF_MEASURE RECEIPT_UOM_LOOKUP_CODE
, /* BUG 4158565 */ SL.ITEM_ID ITEM_ID
, SL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, SL.CATEGORY_ID CATEGORY_ID
, SL.VENDOR_ITEM_NUM SUPPLIER_ITEM_NUMBER
, SL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, LO.LOCATION_CODE SHIP_TO_LOCATION
, PH.AGENT_ID BUYER_ID
, BU.FULL_NAME BUYER
, PH.FREIGHT_TERMS_LOOKUP_CODE PO_FREIGHT_TERMS
, AT.NAME PO_PAYMENT_TERMS
, PS.TAXABLE_FLAG TAXABLE_FLAG
, PS.TAX_CODE_ID TAX_CODE_ID
, TX.NAME TAX_NAME
, SH.BILL_OF_LADING BILL_OF_LADING
, SH.PACKING_SLIP PACKING_SLIP
, PS.APPROVED_FLAG PO_APPROVED_FLAG
, PS.APPROVED_DATE PO_APPROVED_DATE
, RT.VENDOR_ID VENDOR_ID
, (SELECT VENDOR_NAME
FROM AP_SUPPLIERS AS1 /*
WHERE RT.VENDOR_ID = AS1.VENDOR_NAME COMMENTED
AND ADDED FOR BUG 10205013 */
WHERE RT.VENDOR_ID = AS1.VENDOR_ID ) VENDOR_NAME
, /*VE.VENDOR_NAME VENDOR_NAME
, COMMENTED FOR BUG#10072369 */ RT.VENDOR_SITE_ID VENDOR_SITE_ID
, /*VS.VENDOR_SITE_CODE VENDOR_SITE_NAME
, COMMENTED FOR BUG#10072369 */ (SELECT VENDOR_SITE_CODE
FROM AP_SUPPLIER_SITES_ALL ASSA
WHERE RT.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID ) VENDOR_SITE_NAME
, RT.CURRENCY_CODE CURRENCY_CODE
, SL.TO_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, OD.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE
, PS.MATCH_OPTION PO_MATCH_OPTION
, SH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM
, SH.SHIPPED_DATE SHIPPED_DATE
, SH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE
, PS.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT_FLAG
, PL.TYPE_1099 TYPE_1099
, PH.ORG_ID ORG_ID
, SL.CONTAINER_NUM CONTAINER_NUM
, PS.SHIPMENT_TYPE SHIPMENT_TYPE
, RT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1 ATTRIBUTE1
, RT.ATTRIBUTE2 ATTRIBUTE2
, RT.ATTRIBUTE3 ATTRIBUTE3
, RT.ATTRIBUTE4 ATTRIBUTE4
, RT.ATTRIBUTE5 ATTRIBUTE5
, RT.ATTRIBUTE6 ATTRIBUTE6
, RT.ATTRIBUTE7 ATTRIBUTE7
, RT.ATTRIBUTE8 ATTRIBUTE8
, RT.ATTRIBUTE9 ATTRIBUTE9
, RT.ATTRIBUTE10 ATTRIBUTE10
, RT.ATTRIBUTE11 ATTRIBUTE11
, RT.ATTRIBUTE12 ATTRIBUTE12
, RT.ATTRIBUTE13 ATTRIBUTE13
, RT.ATTRIBUTE14 ATTRIBUTE14
, RT.ATTRIBUTE15 ATTRIBUTE15
, DECODE(PR.RELEASE_NUM
, NULL
, PH.PAY_ON_CODE
, PR.PAY_ON_CODE) PAY_ON_CODE
, PS.CANCELLED_BY CANCELLED_BY
, PS.CANCEL_DATE CANCEL_DATE
, PS.CANCEL_FLAG CANCEL_FLAG
, PS.CANCEL_REASON CANCEL_REASON
, PS.CLOSED_BY CLOSED_BY
, NVL(PS.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, PS.CLOSED_DATE CLOSED_DATE
, PS.CLOSED_FLAG CLOSED_FLAG
, PS.CLOSED_REASON CLOSED_REASON
, RT.INTERFACE_TRANSACTION_ID INTERFACE_TRANSACTION_ID
, PS.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, /* 2319154 */ PS.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, /* 2319154 */ /*BUG 3308298 */ RT.QUANTITY_BILLED QUANTITY_BILLED
, PS.CONSIGNED_FLAG CONSIGNED_FLAG
, RT.AMOUNT RCV_TRANSACTION_AMOUNT
, /* AMOUNT BASED MATCHING */ RT.AMOUNT_BILLED AMOUNT_BILLED
, /* AMOUNT BASED MATCHING */ ALC.DISPLAYED_FIELD MATCHING_BASIS
, /* AMOUNT BASED MATCHING */ PS.MATCHING_BASIS MATCHING_BASIS_LOOKUP_CODE
, /* AMOUNT BASED MATCHING */ /*CONTRACT PAYMENTS: PROGRESS PAYMENTS */ PS.DESCRIPTION DESCRIPTION
, PLC2.DISPLAYED_FIELD SHIPMENT_TYPE_DSP
, PS.PAYMENT_TYPE PAYMENT_TYPE
, PLC3.DISPLAYED_FIELD PAYMENT_TYPE_DSP
, PS.NEED_BY_DATE NEED_BY_DATE
, PS.QUANTITY_SHIPPED QUANTITY_SHIPPED
, PS.AMOUNT_SHIPPED AMOUNT_SHIPPED
, PS.VALUE_BASIS VALUE_BASIS /*BUGFIX:4236086*/ FROM RCV_TRANSACTIONS RT
, PO_HEADERS PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PS
, PO_RELEASES_ALL PR
, RCV_SHIPMENT_HEADERS SH
, RCV_SHIPMENT_LINES SL
, AP_TERMS AT
, /* PO_VENDORS VE
, PO_VENDOR_SITES_ALL VS
, COMMENTED FOR BUG#10072369 */ ORG_ORGANIZATION_DEFINITIONS OD
, PER_PEOPLE_F BU
, HR_LOCATIONS_ALL_TL LO
, AP_TAX_CODES_ALL TX
, AP_LOOKUP_CODES ALC
, /* AMOUNT BASED MATCHING */ /* CONTRACT PAYMENTS: PROGRESS PAYMENTS*/ PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3 WHERE SH.RECEIPT_SOURCE_CODE = 'VENDOR' AND RT.PO_HEADER_ID = PH.PO_HEADER_ID AND RT.PO_LINE_ID = PL.PO_LINE_ID AND RT.PO_LINE_LOCATION_ID = PS.LINE_LOCATION_ID AND RT.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND RT.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID AND PH.TERMS_ID = AT.TERM_ID(+) AND /* RT.VENDOR_ID = VE.VENDOR_ID(+) AND RT.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+)
AND COMMENTED FOR BUG#10072369 */ SL.TO_ORGANIZATION_ID = OD.ORGANIZATION_ID(+) AND PH.AGENT_ID = BU.PERSON_ID(+) AND BU.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND BU.EFFECTIVE_END_DATE(+)>= TRUNC(SYSDATE) AND SL.SHIP_TO_LOCATION_ID = LO.LOCATION_ID(+) AND LO.LANGUAGE(+) = USERENV('LANG') AND PS.TAX_CODE_ID = TX.TAX_ID(+) AND RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'MATCH') AND ALC.LOOKUP_TYPE = 'MATCHING BASIS'
AND /* AMOUNT BASED MATCHING */ ALC.LOOKUP_CODE = PS.MATCHING_BASIS /* AMOUNT BASED MATCHING */ /* CONTRACT PAYMENTS : PROGRESS PAYMENTS*/
AND PLC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE'
AND PLC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE
AND PLC3.LOOKUP_TYPE(+) = 'PAYMENT TYPE'
AND PLC3.LOOKUP_CODE(+) = PS.PAYMENT_TYPE
AND ((PS.PO_RELEASE_ID IS NOT NULL
AND PR.PCARD_ID IS NULL) OR (PS.PO_RELEASE_ID IS NULL
AND PH.PCARD_ID IS NULL ))/*BUG 4775154*/

View - PO_VENDORS

 SELECT PAV.VENDOR_ID VENDOR_ID

, PAV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PAV.LAST_UPDATED_BY LAST_UPDATED_BY
, PAV.VENDOR_NAME VENDOR_NAME
, HP.ORGANIZATION_NAME_PHONETIC VENDOR_NAME_ALT
, PAV.SEGMENT1 SEGMENT1
, PAV.SUMMARY_FLAG SUMMARY_FLAG
, PAV.ENABLED_FLAG ENABLED_FLAG
, PAV.SEGMENT2 SEGMENT2
, PAV.SEGMENT3 SEGMENT3
, PAV.SEGMENT4 SEGMENT4
, PAV.SEGMENT5 SEGMENT5
, PAV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PAV.CREATION_DATE CREATION_DATE
, PAV.CREATED_BY CREATED_BY
, PAV.EMPLOYEE_ID EMPLOYEE_ID
, PAV.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE
, PAV.CUSTOMER_NUM CUSTOMER_NUM
, PAV.ONE_TIME_FLAG ONE_TIME_FLAG
, PAV.PARENT_VENDOR_ID PARENT_VENDOR_ID
, PAV.MIN_ORDER_AMOUNT MIN_ORDER_AMOUNT
, PAV.TERMS_ID TERMS_ID
, PAV.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PAV.ALWAYS_TAKE_DISC_FLAG ALWAYS_TAKE_DISC_FLAG
, PAV.PAY_DATE_BASIS_LOOKUP_CODE PAY_DATE_BASIS_LOOKUP_CODE
, PAV.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE
, PAV.PAYMENT_PRIORITY PAYMENT_PRIORITY
, PAV.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAV.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, PAV.INVOICE_AMOUNT_LIMIT INVOICE_AMOUNT_LIMIT
, PAV.HOLD_ALL_PAYMENTS_FLAG HOLD_ALL_PAYMENTS_FLAG
, PAV.HOLD_FUTURE_PAYMENTS_FLAG HOLD_FUTURE_PAYMENTS_FLAG
, PAV.HOLD_REASON HOLD_REASON
, HP.JGZZ_FISCAL_CODE NUM_1099
, PAV.INDIVIDUAL_1099 INDIVIDUAL_1099
, PAV.TYPE_1099 TYPE_1099
, PAV.WITHHOLDING_STATUS_LOOKUP_CODE WITHHOLDING_STATUS_LOOKUP_CODE
, PAV.WITHHOLDING_START_DATE WITHHOLDING_START_DATE
, PAV.ORGANIZATION_TYPE_LOOKUP_CODE ORGANIZATION_TYPE_LOOKUP_CODE
, PAV.START_DATE_ACTIVE START_DATE_ACTIVE
, PAV.END_DATE_ACTIVE END_DATE_ACTIVE
, PAV.MINORITY_GROUP_LOOKUP_CODE MINORITY_GROUP_LOOKUP_CODE
, PAV.WOMEN_OWNED_FLAG WOMEN_OWNED_FLAG
, PAV.SMALL_BUSINESS_FLAG SMALL_BUSINESS_FLAG
, PAV.STANDARD_INDUSTRY_CLASS STANDARD_INDUSTRY_CLASS
, PAV.HOLD_FLAG HOLD_FLAG
, PAV.PURCHASING_HOLD_REASON PURCHASING_HOLD_REASON
, PAV.HOLD_BY HOLD_BY
, PAV.HOLD_DATE HOLD_DATE
, PAV.TERMS_DATE_BASIS TERMS_DATE_BASIS
, PAV.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, PAV.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, PAV.QTY_RCV_TOLERANCE QTY_RCV_TOLERANCE
, PAV.QTY_RCV_EXCEPTION_CODE QTY_RCV_EXCEPTION_CODE
, PAV.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, PAV.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_RECEIPT_ALLOWED
, PAV.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED
, PAV.RECEIPT_DAYS_EXCEPTION_CODE RECEIPT_DAYS_EXCEPTION_CODE
, PAV.RECEIVING_ROUTING_ID RECEIVING_ROUTING_ID
, PAV.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PAV.ALLOW_UNORDERED_RECEIPTS_FLAG ALLOW_UNORDERED_RECEIPTS_FLAG
, PAV.HOLD_UNMATCHED_INVOICES_FLAG HOLD_UNMATCHED_INVOICES_FLAG
, PAV.TAX_VERIFICATION_DATE TAX_VERIFICATION_DATE
, PAV.NAME_CONTROL NAME_CONTROL
, PAV.STATE_REPORTABLE_FLAG STATE_REPORTABLE_FLAG
, PAV.FEDERAL_REPORTABLE_FLAG FEDERAL_REPORTABLE_FLAG
, PAV.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PAV.ATTRIBUTE1 ATTRIBUTE1
, PAV.ATTRIBUTE2 ATTRIBUTE2
, PAV.ATTRIBUTE3 ATTRIBUTE3
, PAV.ATTRIBUTE4 ATTRIBUTE4
, PAV.ATTRIBUTE5 ATTRIBUTE5
, PAV.ATTRIBUTE6 ATTRIBUTE6
, PAV.ATTRIBUTE7 ATTRIBUTE7
, PAV.ATTRIBUTE8 ATTRIBUTE8
, PAV.ATTRIBUTE9 ATTRIBUTE9
, PAV.ATTRIBUTE10 ATTRIBUTE10
, PAV.ATTRIBUTE11 ATTRIBUTE11
, PAV.ATTRIBUTE12 ATTRIBUTE12
, PAV.ATTRIBUTE13 ATTRIBUTE13
, PAV.ATTRIBUTE14 ATTRIBUTE14
, PAV.ATTRIBUTE15 ATTRIBUTE15
, PAV.REQUEST_ID REQUEST_ID
, PAV.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PAV.PROGRAM_ID PROGRAM_ID
, PAV.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, SUBSTR(HP.TAX_REFERENCE
, 1
, 20) VAT_REGISTRATION_NUM
, PAV.AUTO_CALCULATE_INTEREST_FLAG AUTO_CALCULATE_INTEREST_FLAG
, PAV.VALIDATION_NUMBER VALIDATION_NUMBER
, PAV.EXCLUDE_FREIGHT_FROM_DISCOUNT EXCLUDE_FREIGHT_FROM_DISCOUNT
, PAV.TAX_REPORTING_NAME TAX_REPORTING_NAME
, PAV.CHECK_DIGITS CHECK_DIGITS
, PAV.ALLOW_AWT_FLAG ALLOW_AWT_FLAG
, PAV.AWT_GROUP_ID AWT_GROUP_ID
, PAV.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, PAV.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, PAV.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, PAV.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, PAV.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, PAV.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, PAV.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, PAV.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, PAV.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, PAV.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, PAV.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, PAV.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, PAV.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, PAV.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, PAV.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, PAV.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, PAV.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, PAV.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, PAV.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, PAV.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, PAV.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, PAV.BANK_CHARGE_BEARER BANK_CHARGE_BEARER
, PAV.MATCH_OPTION MATCH_OPTION
, PAV.CREATE_DEBIT_MEMO_FLAG CREATE_DEBIT_MEMO_FLAG
, PAV.PARTY_ID PARTY_ID
, PAV.PARENT_PARTY_ID PARENT_PARTY_ID
, PAV.NI_NUMBER NI_NUMBER
, HP.PARTY_NUMBER PARTY_NUMBER
, PAV.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID FROM AP_SUPPLIERS PAV
, HZ_PARTIES HP WHERE PAV.PARTY_ID = HP.PARTY_ID

View - PO_VENDORS_AP_V

 SELECT HZ.PARTY_NAME TRADING_PARTNER

, ASUP.SEGMENT1 VENDOR_NUMBER
, ASUP.NUM_1099 NUM_1099
, ASUP.VAT_REGISTRATION_NUM VAT_REGISTRATION_NUM
, ASUP.VENDOR_ID VENDOR_ID
, ASUP.HOLD_ALL_PAYMENTS_FLAG HOLD_ALL_PAYMENTS_FLAG
, ASUP.ENABLED_FLAG ENABLED_FLAG
, ASUP.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE
, DECODE(ASUP.FEDERAL_REPORTABLE_FLAG
, 'Y'
, TYPE_1099
, '') TYPE_1099
, ASUP.EMPLOYEE_ID EMPLOYEE_ID
, ASUP.AWT_GROUP_ID AWT_GROUP_ID
, ASUP.ALLOW_AWT_FLAG ALLOW_AWT_FLAG
, ASUP.AUTO_CALCULATE_INTEREST_FLAG AUTO_CALCULATE_INTEREST_FLAG
, DECODE(SIGN(TO_DATE(TO_CHAR(ASUP.START_DATE_ACTIVE
, 'DD-MM-YYYY')
, 'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE
, 'DD-MM-YYYY')
, 'DD-MM-YYYY'))
, 1
, 'N'
, DECODE(SIGN(TO_DATE(TO_CHAR(ASUP.END_DATE_ACTIVE
, 'DD-MM-YYYY')
, 'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE
, 'DD-MM-YYYY')
, 'DD-MM-YYYY'))
, -1
, 'N'
, 0
, 'N'
, 'Y')) ACTIVE_FLAG
, PO_VENDORS_AP_PKG.GET_NUM_ACTIVE_PAY_SITES(ASUP.VENDOR_ID
, HR.ORGANIZATION_ID) NUM_ACTIVE_PAY_SITES
, PO_VENDORS_AP_PKG.GET_NUM_INACTIVE_PAY_SITES(ASUP.VENDOR_ID
, HR.ORGANIZATION_ID) NUM_INACTIVE_PAY_SITES
, AP_INVOICES_PKG.GET_TOTAL_PREPAYS(ASUP.VENDOR_ID
, HR.ORGANIZATION_ID) TOTAL_PREPAYS
, AP_INVOICES_PKG.GET_AVAILABLE_PREPAYS(ASUP.VENDOR_ID
, HR.ORGANIZATION_ID) AVAILABLE_PREPAYS
, HR.ORGANIZATION_ID ORG_ID
, ASUP.PARTY_ID PARTY_ID
, PAYEE.PAYMENT_FUNCTION
, PAYEE.EXCLUSIVE_PAYMENT_FLAG
, PAYEE.SETTLEMENT_PRIORITY
, PAYEE.BANK_CHARGE_BEARER
FROM AP_SUPPLIERS ASUP
, HR_OPERATING_UNITS HR
, IBY_EXTERNAL_PAYEES_ALL PAYEE
, HZ_PARTIES HZ
WHERE MO_GLOBAL.CHECK_ACCESS(HR.ORGANIZATION_ID) = 'Y'
AND ASUP.PARTY_ID = PAYEE.PAYEE_PARTY_ID
AND ASUP.PARTY_ID = HZ.PARTY_ID UNION SELECT HP.PARTY_NAME TRADING_PARTNER
, NULL VENDOR_NUMBER
, HP.JGZZ_FISCAL_CODE NUM_1099
, TAX_REFERENCE VAT_REGISTRATION_NUM
, NULL VENDOR_ID
, NULL HOLD_ALL_PAYMENTS_FLAG
, DECODE(STATUS
, 'A'
, 'Y'
, 'N') ENABLED_FLAG
, NULL VENDOR_TYPE_LOOKUP_CODE
, NULL TYPE_1099
, NULL EMPLOYEE_ID
, NULL AWT_GROUP_ID
, NULL ALLOW_AWT_FLAG
, NULL AUTO_CALCULATE_INTEREST_FLAG
, DECODE(STATUS
, 'A'
, 'Y'
, 'N') ACTIVE_FLAG
, NULL NUM_ACTIVE_PAY_SITES
, NULL NUM_INACTIVE_PAY_SITES
, NULL TOTAL_PREPAYS
, NULL AVAILABLE_PREPAYS
, NULL ORG_ID
, HP.PARTY_ID PARTY_ID
, PAYEE.PAYMENT_FUNCTION
, PAYEE.EXCLUSIVE_PAYMENT_FLAG
, PAYEE.SETTLEMENT_PRIORITY
, PAYEE.BANK_CHARGE_BEARER
FROM HZ_PARTIES HP
, IBY_EXTERNAL_PAYEES_ALL PAYEE
WHERE PAYEE.PAYEE_PARTY_ID = HP.PARTY_ID
AND NOT EXISTS (SELECT 'X'
FROM AP_SUPPLIERS ASUP
WHERE ASUP.PARTY_ID = PAYEE.PAYEE_PARTY_ID)

View - PO_VENDOR_CONTACTS

 SELECT PVC.VENDOR_CONTACT_ID VENDOR_CONTACT_ID

, PVS.VENDOR_SITE_ID VENDOR_SITE_ID
, PVC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PVC.LAST_UPDATED_BY LAST_UPDATED_BY
, PVC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PVC.CREATION_DATE CREATION_DATE
, PVC.CREATED_BY CREATED_BY
, SUBSTR(HP.PERSON_FIRST_NAME
, 1
, 15) FIRST_NAME
, SUBSTR(HP.PERSON_MIDDLE_NAME
, 1
, 15) MIDDLE_NAME
, SUBSTR(HP.PERSON_LAST_NAME
, 1
, 15) LAST_NAME
, NVL(HP. PERSON_PRE_NAME_ADJUNCT
, HP.SALUTATION) PREFIX
, SUBSTR(HP.PERSON_TITLE
, 1
, 30) TITLE --BUG9215166
, HPS.MAILSTOP MAIL_STOP
, (SELECT HP2.PRIMARY_PHONE_AREA_CODE
FROM HZ_PARTIES HP2
WHERE PVC.REL_PARTY_ID =HP2.PARTY_ID
AND HP2.PRIMARY_PHONE_LINE_TYPE='GEN') AREA_CODE
, (SELECT HP2.PRIMARY_PHONE_NUMBER
FROM HZ_PARTIES HP2
WHERE PVC.REL_PARTY_ID =HP2.PARTY_ID
AND HP2.PRIMARY_PHONE_LINE_TYPE='GEN') PHONE
, PVC.REQUEST_ID REQUEST_ID
, PVC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PVC.PROGRAM_ID PROGRAM_ID
, PVC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, HP. ORGANIZATION_NAME_PHONETIC CONTACT_NAME_ALT
, HP.PERSON_FIRST_NAME_PHONETIC FIRST_NAME_ALT
, HP.PERSON_LAST_NAME_PHONETIC LAST_NAME_ALT
, HOC.DEPARTMENT DEPARTMENT
, NVL((SELECT HCP8.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP8
WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP8.OWNER_TABLE_ID
AND HCP8.CONTACT_POINT_TYPE = 'EMAIL' --AND HCP8.PRIMARY_FLAG = 'Y' --AND HCP8.EMAIL_ADDRESS IS NOT NULL
AND STATUS = 'A'
AND ROWNUM < 2 )
, HP2.EMAIL_ADDRESS) EMAIL_ADDRESS
, HP2.URL URL
, (SELECT HCP4.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP4
WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP4.OWNER_TABLE_ID
AND HCP4.CONTACT_POINT_TYPE = 'PHONE'
AND HCP4.PHONE_LINE_TYPE = 'PHONE'
AND HCP4.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_AREA_CODE
, (SELECT HCP7.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP7
WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP7.OWNER_TABLE_ID
AND HCP7.CONTACT_POINT_TYPE = 'PHONE'
AND HCP7.PHONE_LINE_TYPE = 'PHONE'
AND HCP7.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_PHONE
, (SELECT HCP5.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP5
WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
AND HCP5.PHONE_LINE_TYPE = 'FAX' /*
AND HCP5.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP5.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX_AREA_CODE
, (SELECT HCP6.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP6
WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
AND HCP6.PHONE_LINE_TYPE = 'FAX' /*
AND HCP6.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP6.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX
, LEAST(NVL(HPR.END_DATE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR'))
, NVL(PVC.INACTIVE_DATE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR'))) INACTIVE_DATE /* BUG 7551007 */
, PVC.PER_PARTY_ID PER_PARTY_ID
, PVC.RELATIONSHIP_ID RELATIONSHIP_ID
, PVC.REL_PARTY_ID REL_PARTY_ID
, PVC.PARTY_SITE_ID PARTY_SITE_ID
, PVC.ORG_CONTACT_ID ORG_CONTACT_ID
, PVC.ORG_PARTY_SITE_ID ORG_PARTY_SITE_ID
, PVC.ATTRIBUTE_CATEGORY
, PVC.ATTRIBUTE1
, PVC.ATTRIBUTE2
, PVC.ATTRIBUTE3
, PVC.ATTRIBUTE4
, PVC.ATTRIBUTE5
, PVC.ATTRIBUTE6
, PVC.ATTRIBUTE7
, PVC.ATTRIBUTE8
, PVC.ATTRIBUTE9
, PVC.ATTRIBUTE10
, PVC.ATTRIBUTE11
, PVC.ATTRIBUTE12
, PVC.ATTRIBUTE13
, PVC.ATTRIBUTE14
, PVC.ATTRIBUTE15
, PVS.VENDOR_ID FROM AP_SUPPLIER_CONTACTS PVC
, AP_SUPPLIER_SITES_ALL PVS
, HZ_PARTIES HP
, HZ_RELATIONSHIPS HPR
, HZ_PARTY_SITES HPS
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP2
, AP_SUPPLIERS APS WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
AND PVC.REL_PARTY_ID = HP2.PARTY_ID
AND PVC.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
AND HPR.DIRECTIONAL_FLAG='F'
AND PVS.PARTY_SITE_ID = PVC.ORG_PARTY_SITE_ID
AND PVS.VENDOR_ID = APS.VENDOR_ID
AND NVL( APS.VENDOR_TYPE_LOOKUP_CODE
, 'DUMMY' ) <> 'EMPLOYEE' UNION SELECT PVC.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, PVC.VENDOR_SITE_ID VENDOR_SITE_ID
, PVC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PVC.LAST_UPDATED_BY LAST_UPDATED_BY
, PVC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PVC.CREATION_DATE CREATION_DATE
, PVC.CREATED_BY CREATED_BY
, SUBSTR(HP.PERSON_FIRST_NAME
, 1
, 15) FIRST_NAME
, SUBSTR(HP.PERSON_MIDDLE_NAME
, 1
, 15) MIDDLE_NAME
, SUBSTR(HP.PERSON_LAST_NAME
, 1
, 15) LAST_NAME
, NVL(HP. PERSON_PRE_NAME_ADJUNCT
, HP.SALUTATION) PREFIX
, SUBSTR(HP.PERSON_TITLE
, 1
, 30) TITLE --BUG9215166
, NULL MAIL_STOP
, HP2.PRIMARY_PHONE_AREA_CODE AREA_CODE
, HP2.PRIMARY_PHONE_NUMBER PHONE
, PVC.REQUEST_ID REQUEST_ID
, PVC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PVC.PROGRAM_ID PROGRAM_ID
, PVC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, HP. ORGANIZATION_NAME_PHONETIC CONTACT_NAME_ALT
, HP.PERSON_FIRST_NAME_PHONETIC FIRST_NAME_ALT
, HP.PERSON_LAST_NAME_PHONETIC LAST_NAME_ALT
, HOC.DEPARTMENT DEPARTMENT
, NVL((SELECT HCP8.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP8
WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP8.OWNER_TABLE_ID
AND HCP8.CONTACT_POINT_TYPE = 'EMAIL' --AND HCP8.PRIMARY_FLAG = 'Y' --AND HCP8.EMAIL_ADDRESS IS NOT NULL
AND STATUS = 'A'
AND ROWNUM < 2 )
, HP2.EMAIL_ADDRESS) EMAIL_ADDRESS
, HP2.URL URL
, (SELECT HCP4.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP4
WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP4.OWNER_TABLE_ID
AND HCP4.CONTACT_POINT_TYPE = 'PHONE'
AND HCP4.PHONE_LINE_TYPE = 'PHONE'
AND HCP4.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_AREA_CODE
, (SELECT HCP7.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP7
WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP7.OWNER_TABLE_ID
AND HCP7.CONTACT_POINT_TYPE = 'PHONE'
AND HCP7.PHONE_LINE_TYPE = 'PHONE'
AND HCP7.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_PHONE
, (SELECT HCP5.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP5
WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
AND HCP5.PHONE_LINE_TYPE = 'FAX' /*
AND HCP5.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP5.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX_AREA_CODE
, (SELECT HCP6.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP6
WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
AND HCP6.PHONE_LINE_TYPE = 'FAX' /*
AND HCP6.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP6.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX
, HPR.END_DATE INACTIVE_DATE
, PVC.PER_PARTY_ID PER_PARTY_ID
, PVC.RELATIONSHIP_ID RELATIONSHIP_ID
, PVC.REL_PARTY_ID REL_PARTY_ID
, PVC.PARTY_SITE_ID PARTY_SITE_ID
, PVC.ORG_CONTACT_ID ORG_CONTACT_ID
, PVC.ORG_PARTY_SITE_ID ORG_PARTY_SITE_ID
, PVC.ATTRIBUTE_CATEGORY
, PVC.ATTRIBUTE1
, PVC.ATTRIBUTE2
, PVC.ATTRIBUTE3
, PVC.ATTRIBUTE4
, PVC.ATTRIBUTE5
, PVC.ATTRIBUTE6
, PVC.ATTRIBUTE7
, PVC.ATTRIBUTE8
, PVC.ATTRIBUTE9
, PVC.ATTRIBUTE10
, PVC.ATTRIBUTE11
, PVC.ATTRIBUTE12
, PVC.ATTRIBUTE13
, PVC.ATTRIBUTE14
, PVC.ATTRIBUTE15
, APS.VENDOR_ID FROM AP_SUPPLIER_CONTACTS PVC
, HZ_PARTIES HP
, HZ_RELATIONSHIPS HPR
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP2
, AP_SUPPLIERS APS WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
AND PVC.REL_PARTY_ID = HP2.PARTY_ID
AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
AND HPR.DIRECTIONAL_FLAG='F'
AND PVC.ORG_PARTY_SITE_ID IS NULL
AND PVC.VENDOR_SITE_ID IS NULL
AND HPR.OBJECT_ID = APS.PARTY_ID
AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF'
AND HPR.OBJECT_TYPE = 'ORGANIZATION'
AND NVL( APS.VENDOR_TYPE_LOOKUP_CODE
, 'DUMMY' ) <> 'EMPLOYEE'