VIEW: APPS.PO_VENDORS_AP_V

Source

View Text - Preformatted

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 Text - HTML Formatted

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)