VIEW: APPS.POFV_PURCHASE_ORDERS

Source

View Text - Preformatted

SELECT PH.PO_HEADER_ID, PH.SEGMENT1, PH.ORG_ID, OP.NAME, '_LA:ph.TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:DOCUMENT SUBTYPE:DISPLAYED_FIELD', PH.REVISION_NUM, PH.REVISED_DATE, PH.COMMENTS, '_LA:ph.AUTHORIZATION_STATUS:PO_LOOKUP_CODES:AUTHORIZATION STATUS:DISPLAYED_FIELD', PH.APPROVED_DATE, '_LA:ph.CANCEL_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:ph.CLOSED_CODE:PO_LOOKUP_CODES:DOCUMENT STATE:DISPLAYED_FIELD', PH.CLOSED_DATE, '_LA:ph.FIRM_STATUS_LOOKUP_CODE:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:ph.FROZEN_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:ph.USER_HOLD_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.RATE, PH.RATE_DATE, '_LA:ph.FREIGHT_TERMS_LOOKUP_CODE:PO_LOOKUP_CODES:FREIGHT TERMS:DISPLAYED_FIELD', '_LA:ph.FOB_LOOKUP_CODE:AR_LOOKUPS:FOB:MEANING', PH.START_DATE, PH.END_DATE, PH.BLANKET_TOTAL_AMOUNT, PH.AMOUNT_LIMIT, PH.MIN_RELEASE_AMOUNT, PH.NOTE_TO_RECEIVER, PH.NOTE_TO_VENDOR, '_LA:ph.ACCEPTANCE_REQUIRED_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.ACCEPTANCE_DUE_DATE, '_LA:ph.CONFIRMING_ORDER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:ph.SUPPLY_AGREEMENT_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.PRINT_COUNT, PH.PRINTED_DATE, PH.EDI_PROCESSED_FLAG, PH.EDI_PROCESSED_STATUS, PH.VENDOR_ORDER_NUM, PH.PRICE_UPDATE_TOLERANCE, '_LA:ph.PAY_ON_CODE:PO_LOOKUP_CODES:ERS PAY_ON_CODE:DISPLAYED_FIELD', PH.USSGL_TRANSACTION_CODE, '_DF:PO:PO_HEADERS:ph', PH.VENDOR_ID, VE.VENDOR_NAME, PH.VENDOR_SITE_ID, VS.VENDOR_SITE_CODE, PH.VENDOR_CONTACT_ID, VC.LAST_NAME || ', ' || VC.FIRST_NAME, PH.AGENT_ID, AG.EMPLOYEE_NUMBER, AG.FULL_NAME, PH.BILL_TO_LOCATION_ID, BO.LOCATION_CODE, PH.SHIP_TO_LOCATION_ID, SO.LOCATION_CODE, PH.TERMS_ID, PT.NAME, PH.SHIP_VIA_LOOKUP_CODE, FR.DESCRIPTION, FR.ORGANIZATION_ID, MP.ORGANIZATION_CODE, PH.CURRENCY_CODE, CU.NAME, PH.RATE_TYPE, RT.USER_CONVERSION_TYPE, RT.DESCRIPTION, PH.PCARD_ID, CD.CARD_NUMBER, PH.CREATION_DATE, PH.CREATED_BY, PH.LAST_UPDATE_DATE, PH.LAST_UPDATED_BY, '_LA:ph.XML_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.XML_SEND_DATE, PH.XML_CHANGE_SEND_DATE, '_LA:ph.GLOBAL_AGREEMENT_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.CBC_ACCOUNTING_DATE, '_LA:ph.CONSUME_REQ_DEMAND_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:ph.CONSIGNED_CONSUMPTION_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.CHANGE_REQUESTED_BY, PH.PENDING_SIGNATURE_FLAG, '_LA:ph.CONTERMS_EXIST_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PH.CONTERMS_ARTICLES_UPD_DATE, PH.CONTERMS_DELIV_UPD_DATE , PH.DOCUMENT_CREATION_METHOD, PH.SUBMIT_DATE, PH.AUTO_SOURCING_FLAG, PH.UPDATE_SOURCING_RULES_FLAG, PH.RETRO_PRICE_APPLY_UPDATES_FLAG, PH.RETRO_PRICE_COMM_UPDATES_FLAG, PH.EMAIL_ADDRESS, PH.FAX, PH.SUPPLIER_NOTIF_METHOD, PH.SUPPLIER_AUTH_ENABLED_FLAG, PH.CAT_ADMIN_AUTH_ENABLED_FLAG, PH.STYLE_ID, PH.TAX_ATTRIBUTE_UPDATE_CODE FROM PO_HEADERS_ALL PH, HR_ALL_ORGANIZATION_UNITS OP, AP_TERMS PT, FND_CURRENCIES_VL CU, GL_DAILY_CONVERSION_TYPES RT, PER_PEOPLE_F AG, HR_LOCATIONS BO, HR_LOCATIONS SO, ORG_FREIGHT FR, FINANCIALS_SYSTEM_PARAMS_ALL FP, MTL_PARAMETERS MP, AP_CARDS_ALL CD, PO_VENDORS VE, PO_VENDOR_CONTACTS VC, PO_VENDOR_SITES_ALL VS WHERE PH.ORG_ID = FP.ORG_ID AND PH.ORG_ID = OP.ORGANIZATION_ID AND FP.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID AND PH.TERMS_ID = PT.TERM_ID(+) AND PH.CURRENCY_CODE = CU.CURRENCY_CODE AND PH.RATE_TYPE = RT.CONVERSION_TYPE(+) AND PH.AGENT_ID = AG.PERSON_ID(+) AND AG.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND AG.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) AND PH.BILL_TO_LOCATION_ID = BO.LOCATION_ID(+) AND PH.SHIP_TO_LOCATION_ID = SO.LOCATION_ID(+) AND PH.SHIP_VIA_LOOKUP_CODE = FR.FREIGHT_CODE(+) AND NVL(FR.ORGANIZATION_ID, FP.INVENTORY_ORGANIZATION_ID) = FP.INVENTORY_ORGANIZATION_ID AND PH.VENDOR_ID = VE.VENDOR_ID(+) AND PH.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+) AND PH.ORG_ID = VS.ORG_ID(+) AND PH.VENDOR_CONTACT_ID = VC.VENDOR_CONTACT_ID(+) AND (PH.VENDOR_CONTACT_ID IS NULL OR PH.VENDOR_SITE_ID=VC.VENDOR_SITE_ID) AND PH.PCARD_ID = CD.CARD_ID(+) WITH READ ONLY

View Text - HTML Formatted

SELECT PH.PO_HEADER_ID
, PH.SEGMENT1
, PH.ORG_ID
, OP.NAME
, '_LA:PH.TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:DOCUMENT SUBTYPE:DISPLAYED_FIELD'
, PH.REVISION_NUM
, PH.REVISED_DATE
, PH.COMMENTS
, '_LA:PH.AUTHORIZATION_STATUS:PO_LOOKUP_CODES:AUTHORIZATION STATUS:DISPLAYED_FIELD'
, PH.APPROVED_DATE
, '_LA:PH.CANCEL_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:PH.CLOSED_CODE:PO_LOOKUP_CODES:DOCUMENT STATE:DISPLAYED_FIELD'
, PH.CLOSED_DATE
, '_LA:PH.FIRM_STATUS_LOOKUP_CODE:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:PH.FROZEN_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:PH.USER_HOLD_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.RATE
, PH.RATE_DATE
, '_LA:PH.FREIGHT_TERMS_LOOKUP_CODE:PO_LOOKUP_CODES:FREIGHT TERMS:DISPLAYED_FIELD'
, '_LA:PH.FOB_LOOKUP_CODE:AR_LOOKUPS:FOB:MEANING'
, PH.START_DATE
, PH.END_DATE
, PH.BLANKET_TOTAL_AMOUNT
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT
, PH.NOTE_TO_RECEIVER
, PH.NOTE_TO_VENDOR
, '_LA:PH.ACCEPTANCE_REQUIRED_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.ACCEPTANCE_DUE_DATE
, '_LA:PH.CONFIRMING_ORDER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:PH.SUPPLY_AGREEMENT_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.PRINT_COUNT
, PH.PRINTED_DATE
, PH.EDI_PROCESSED_FLAG
, PH.EDI_PROCESSED_STATUS
, PH.VENDOR_ORDER_NUM
, PH.PRICE_UPDATE_TOLERANCE
, '_LA:PH.PAY_ON_CODE:PO_LOOKUP_CODES:ERS PAY_ON_CODE:DISPLAYED_FIELD'
, PH.USSGL_TRANSACTION_CODE
, '_DF:PO:PO_HEADERS:PH'
, PH.VENDOR_ID
, VE.VENDOR_NAME
, PH.VENDOR_SITE_ID
, VS.VENDOR_SITE_CODE
, PH.VENDOR_CONTACT_ID
, VC.LAST_NAME || '
, ' || VC.FIRST_NAME
, PH.AGENT_ID
, AG.EMPLOYEE_NUMBER
, AG.FULL_NAME
, PH.BILL_TO_LOCATION_ID
, BO.LOCATION_CODE
, PH.SHIP_TO_LOCATION_ID
, SO.LOCATION_CODE
, PH.TERMS_ID
, PT.NAME
, PH.SHIP_VIA_LOOKUP_CODE
, FR.DESCRIPTION
, FR.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, PH.CURRENCY_CODE
, CU.NAME
, PH.RATE_TYPE
, RT.USER_CONVERSION_TYPE
, RT.DESCRIPTION
, PH.PCARD_ID
, CD.CARD_NUMBER
, PH.CREATION_DATE
, PH.CREATED_BY
, PH.LAST_UPDATE_DATE
, PH.LAST_UPDATED_BY
, '_LA:PH.XML_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.XML_SEND_DATE
, PH.XML_CHANGE_SEND_DATE
, '_LA:PH.GLOBAL_AGREEMENT_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.CBC_ACCOUNTING_DATE
, '_LA:PH.CONSUME_REQ_DEMAND_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:PH.CONSIGNED_CONSUMPTION_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.CHANGE_REQUESTED_BY
, PH.PENDING_SIGNATURE_FLAG
, '_LA:PH.CONTERMS_EXIST_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PH.CONTERMS_ARTICLES_UPD_DATE
, PH.CONTERMS_DELIV_UPD_DATE
, PH.DOCUMENT_CREATION_METHOD
, PH.SUBMIT_DATE
, PH.AUTO_SOURCING_FLAG
, PH.UPDATE_SOURCING_RULES_FLAG
, PH.RETRO_PRICE_APPLY_UPDATES_FLAG
, PH.RETRO_PRICE_COMM_UPDATES_FLAG
, PH.EMAIL_ADDRESS
, PH.FAX
, PH.SUPPLIER_NOTIF_METHOD
, PH.SUPPLIER_AUTH_ENABLED_FLAG
, PH.CAT_ADMIN_AUTH_ENABLED_FLAG
, PH.STYLE_ID
, PH.TAX_ATTRIBUTE_UPDATE_CODE
FROM PO_HEADERS_ALL PH
, HR_ALL_ORGANIZATION_UNITS OP
, AP_TERMS PT
, FND_CURRENCIES_VL CU
, GL_DAILY_CONVERSION_TYPES RT
, PER_PEOPLE_F AG
, HR_LOCATIONS BO
, HR_LOCATIONS SO
, ORG_FREIGHT FR
, FINANCIALS_SYSTEM_PARAMS_ALL FP
, MTL_PARAMETERS MP
, AP_CARDS_ALL CD
, PO_VENDORS VE
, PO_VENDOR_CONTACTS VC
, PO_VENDOR_SITES_ALL VS
WHERE PH.ORG_ID = FP.ORG_ID
AND PH.ORG_ID = OP.ORGANIZATION_ID
AND FP.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PH.TERMS_ID = PT.TERM_ID(+)
AND PH.CURRENCY_CODE = CU.CURRENCY_CODE
AND PH.RATE_TYPE = RT.CONVERSION_TYPE(+)
AND PH.AGENT_ID = AG.PERSON_ID(+)
AND AG.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND AG.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE)
AND PH.BILL_TO_LOCATION_ID = BO.LOCATION_ID(+)
AND PH.SHIP_TO_LOCATION_ID = SO.LOCATION_ID(+)
AND PH.SHIP_VIA_LOOKUP_CODE = FR.FREIGHT_CODE(+)
AND NVL(FR.ORGANIZATION_ID
, FP.INVENTORY_ORGANIZATION_ID) = FP.INVENTORY_ORGANIZATION_ID
AND PH.VENDOR_ID = VE.VENDOR_ID(+)
AND PH.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+)
AND PH.ORG_ID = VS.ORG_ID(+)
AND PH.VENDOR_CONTACT_ID = VC.VENDOR_CONTACT_ID(+)
AND (PH.VENDOR_CONTACT_ID IS NULL OR PH.VENDOR_SITE_ID=VC.VENDOR_SITE_ID)
AND PH.PCARD_ID = CD.CARD_ID(+) WITH READ ONLY