View: POABV_EDW_CONTRACT_AGRMNTS_FCV

View Text

SELECT INC.SEQ_ID SEQ_ID
, 1 VIEW_ID
, TO_CHAR(POH.PO_HEADER_ID) || '-' || ELI.INSTANCE_CODE CONTRACT_PK
, ELI.INSTANCE_CODE INSTANCE_FK
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (POH.AGENT_ID
, ELI.INSTANCE_CODE) BUYER_FK
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (NVL(POA_EDW_SPEND_PKG.APPROVED_BY (POH.PO_HEADER_ID)
, POH.AGENT_ID)
, ELI.INSTANCE_CODE) APPROVER_FK
, EDW_TRD_PARTNER_PKG.SUPPLIER_SITE_FK (POH.VENDOR_SITE_ID
, POH.ORG_ID
, ELI.INSTANCE_CODE) SUPPLIER_SITE_FK
, EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK (POH.ORG_ID
, ELI.INSTANCE_CODE) OPERATING_UNIT_FK
, EDW_TIME_PKG.CAL_DAY_FK( POH.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE) CREATION_DATE_FK
, DECODE(NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_POH(POH.CREATION_DATE
, POH.PO_HEADER_ID)
, POH.APPROVED_DATE)
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_POH(POH.CREATION_DATE
, POH.PO_HEADER_ID)
, POH.APPROVED_DATE)
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) APPROVED_DATE_FK
, DECODE(POH.REVISED_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK ( TO_DATE(POH.REVISED_DATE
, 'DD-MON-RR HH24:MI')
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) REVISED_DATE_FK
, DECODE(POH.ACCEPTANCE_DUE_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.ACCEPTANCE_DUE_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) ACCPT_DUE_DATE_FK
, DECODE(POH.START_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.START_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) START_DATE_FK
, DECODE(POH.END_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.END_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) END_DATE_FK
, DECODE(POH.PRINTED_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.PRINTED_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) PRINTED_DATE_FK
, DECODE(POH.RATE_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.RATE_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)) TXN_CUR_DATE_FK
, DECODE(POH.TERMS_ID
, NULL
, NULL
, POH.TERMS_ID || '-' || 'AP' || '-' || ELI.INSTANCE_CODE) AP_TERMS_FK
, DECODE(POH.CLOSED_CODE
, NULL
, NULL
, UPPER(POH.CLOSED_CODE) || '-' || 'DOCUMENT STATE' || '-' || 'PO') CLOSED_FK
, DECODE(POH.TYPE_LOOKUP_CODE
, NULL
, NULL
, UPPER(POH.TYPE_LOOKUP_CODE) || '-' || 'AGREEMENT TYPE' || '-' || 'POD') PO_TYPE_FK
, DECODE(POH.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, UPPER(POH.SHIP_VIA_LOOKUP_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG') SHIP_VIA_FK
, DECODE(POH.FOB_LOOKUP_CODE
, NULL
, NULL
, UPPER(POH.FOB_LOOKUP_CODE) || '-' || 'FOB' || '-' || 'PO') FOB_FK
, DECODE(POH.FREIGHT_TERMS_LOOKUP_CODE
, NULL
, NULL
, UPPER(POH.FREIGHT_TERMS_LOOKUP_CODE) || '-' || 'FREIGHT TERMS' || '-' || 'PO') FREIGHT_TERMS_FK
, NVL(POH.ACCEPTANCE_REQUIRED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' ACCPT_REQUIRED_FK
, NVL(POH.FROZEN_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' FROZEN_FK
, NVL(POH.CANCEL_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' CANCELLED_FK
, NVL(POH.USER_HOLD_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' USER_HOLD_FK
, NVL(POH.CONFIRMING_ORDER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' CONFIRM_ORDER_FK
, NVL(POH.APPROVED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' APPROVED_FK
, NVL(POH.EDI_PROCESSED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND' EDI_PROCESSED_FK
, DECODE(POH.START_DATE
, LEAST(POH.START_DATE
, SYSDATE)
, DECODE(POH.END_DATE
, GREATEST(POH.END_DATE
, SYSDATE)
, 'Y'
, DECODE(POH.END_DATE
, NULL
, 'Y'
, 'N'))
, DECODE(POH.START_DATE
, NULL
, DECODE(POH.END_DATE
, GREATEST(POH.END_DATE
, SYSDATE)
, 'Y'
, DECODE(POH.END_DATE
, NULL
, 'Y'
, 'N'))
, 'N')) || '-' || 'YES_NO' || '-' || 'FND' CONTRACT_EFFECTIVE_FK
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY) SUP_SITE_GEOG_FK
, DECODE(POH.BILL_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, HRL2.TOWN_OR_CITY || '-' || HRL2.POSTAL_CODE || '-' || HRL2.REGION_2 || '-' || HRL2.COUNTRY) BILL_LOCATION_FK
, DECODE(POH.SHIP_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL1.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(POH.SHIP_TO_LOCATION_ID)
, HRL1.TOWN_OR_CITY || '-' || HRL1.POSTAL_CODE || '-' || HRL1.REGION_2 || '-' || HRL1.COUNTRY)) SHIP_LOCATION_FK
, POH.PO_HEADER_ID || '-' || ELI.INSTANCE_CODE CONTRACT_NUM_FK
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE) TXN_CUR_CODE_FK
, POH.RATE_TYPE TXN_CUR_RATE_TYPE
, /*** MEASURES ***/ POH.BLANKET_TOTAL_AMOUNT AMT_AGREED_T
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POH.BLANKET_TOTAL_AMOUNT
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.BLANKET_TOTAL_AMOUNT
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, POH.RATE_TYPE)) AMT_AGREED_G
, POH.AMOUNT_LIMIT AMT_LIMIT_T
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POH.AMOUNT_LIMIT
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.AMOUNT_LIMIT
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, POH.RATE_TYPE)) AMT_LIMIT_G
, POH.MIN_RELEASE_AMOUNT AMT_MIN_RELEASE_T
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POH.MIN_RELEASE_AMOUNT
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.MIN_RELEASE_AMOUNT
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, POH.RATE_TYPE)) AMT_MIN_RELEASE_G
, POA_EDW_SPEND_PKG.CONTRACT_AMT_RELEASED( POH.PO_HEADER_ID
, POH.ORG_ID
, POH.TYPE_LOOKUP_CODE) AMT_RELEASED_T
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POA_EDW_SPEND_PKG.CONTRACT_AMT_RELEASED( POH.PO_HEADER_ID
, POH.ORG_ID
, POH.TYPE_LOOKUP_CODE)
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POA_EDW_SPEND_PKG.CONTRACT_AMT_RELEASED( POH.PO_HEADER_ID
, POH.ORG_ID
, POH.TYPE_LOOKUP_CODE)
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, POH.RATE_TYPE)) AMT_RELEASED_G
, NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_POH(POH.CREATION_DATE
, POH.PO_HEADER_ID)
, POH.APPROVED_DATE) - POH.CREATION_DATE NUM_DAYS_CREATE_TO_APP
, NVL(POH.PRINTED_DATE
, POH.APPROVED_DATE) - POH.APPROVED_DATE NUM_DAYS_APP_TO_SEND
, POH.ACCEPTANCE_DUE_DATE - NVL(POH.PRINTED_DATE
, POH.APPROVED_DATE) NUM_DAYS_APP_SEND_TO_ACCPT
, /*** ATTRIBUTES ***/ POH.PO_HEADER_ID PO_HEADER_ID
, POH.NOTE_TO_VENDOR SUPPLIER_NOTE
, POH.COMMENTS COMMENTS
, POH.NOTE_TO_RECEIVER RECEIVER_NOTE
, POH.REVISION_NUM REVISION_NUM
, POH.SEGMENT1 CONTRACT_NUM
, POH.RATE TXN_CUR_RATE
, GREATEST(POH.LAST_UPDATE_DATE
, FSP.LAST_UPDATE_DATE
, GSOB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, POH.CREATION_DATE CREATION_DATE
, NULL DUNS_FK
, NULL SIC_CODE_FK
, DECODE(EDW_CURRENCY.GET_RATE( DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POH.RATE_DATE
, POH.CREATION_DATE)
, NULL)
, -1
, 'RATE NOT AVAILABLE'
, -2
, 'INVALID CURRENCY'
, 'LOCAL READY') COLLECTION_STATUS
, '_DF:PO:PO_HEADERS:POH'
FROM POA_EDW_CONTRACT_INC INC
, PO_HEADERS_ALL POH
, GL_SETS_OF_BOOKS GSOB
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, EDW_LOCAL_INSTANCE ELI
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
WHERE INC.PRIMARY_KEY = POH.PO_HEADER_ID
AND NVL(POH.ORG_ID
, -999) = NVL(FSP.ORG_ID
, -999)
AND GSOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND PVS.VENDOR_ID (+)= POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+)= POH.VENDOR_SITE_ID
AND POH.SHIP_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND POH.BILL_TO_LOCATION_ID = HRL2.LOCATION_ID (+)

Columns