FND Design Data [Home] [Help]

View: AP_RECEIPT_CORRECTIONS_V

Product: AP - Payables
Description:
Implementation/DBA Data: ViewAPPS.AP_RECEIPT_CORRECTIONS_V
View Text

SELECT RT.TRANSACTION_ID RCV_TRANSACTION_ID
, RT.TRANSACTION_DATE RCV_TRANSACTION_DATE
, RT.QUANTITY RCV_TRANSACTION_QUANTITY
, RT.AMOUNT RCV_TRANSACTION_AMOUNT
, 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
, SL.UNIT_OF_MEASURE RECEIPT_UOM_LOOKUP_CODE
, 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
, 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
, VE.VENDOR_NAME VENDOR_NAME
, RT.VENDOR_SITE_ID VENDOR_SITE_ID
, VS.VENDOR_SITE_CODE 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
, LT.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE
, 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
, PS.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, AI.INVOICE_NUM INVOICE_NUMBER
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_AMOUNT INVOICE_AMOUNT
, AI.INVOICE_DATE INVOICE_DATE
, AIL.LINE_NUMBER LINE_NUMBER
, AIL.QUANTITY_INVOICED LINE_QUANTITY_INVOICED
, AIL.UNIT_PRICE LINE_UNIT_PRICE
, AIL.AMOUNT LINE_AMOUNT
, LT.MATCHING_BASIS MATCHING_BASIS_LOOKUP_CODE
, ALC.DISPLAYED_FIELD MATCHING_BASIS
, PS.AMOUNT_SHIPPED AMOUNT_SHIPPED
, PS.QUANTITY_SHIPPED QUANTITY_SHIPPED
, PS.PAYMENT_TYPE PAYMENT_TYPE
, PLC2.DISPLAYED_FIELD SHIPMENT_TYPE_DSP
, PLC3.DISPLAYED_FIELD PAYMENT_TYPE_DSP
, PS.NEED_BY_DATE NEED_BY_DATE
, PS.DESCRIPTION DESCRIPTION
, PL.VENDOR_PRODUCT_NUM
, PL.TASK_ID TASK_ID FROM AP_INVOICES AI
, /*BUG#10134010 MODIFED
FROM AP_INVOICES_ALL TO SECURED SYNONYM */ AP_INVOICE_LINES_ALL AIL
, RCV_TRANSACTIONS RT
, PO_HEADERS_ALL 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
, ORG_ORGANIZATION_DEFINITIONS OD
, PER_PEOPLE_F BU
, HR_LOCATIONS_ALL_TL LO
, PO_LINE_TYPES LT
, AP_LOOKUP_CODES ALC
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3 WHERE AI.INVOICE_ID = AIL.INVOICE_ID AND AIL.RCV_SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND 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 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 PL.LINE_TYPE_ID = LT.LINE_TYPE_ID(+) AND RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'MATCH')
AND ALC.LOOKUP_TYPE(+) = 'MATCH_BASIS'
AND ALC.LOOKUP_CODE(+) = LT.MATCHING_BASIS
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 AIL.MATCH_TYPE NOT IN ('QTY_CORRECTION'
, 'PRICE_CORRECTION'
, 'AMOUNT_CORRECTION')
AND AI.CANCELLED_DATE IS NULL /* BUG 9727651 */
AND NVL(AIL.DISCARDED_FLAG
, 'N') <> 'Y' /* BUG 9727651 */
AND AI.INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD'
, 'MIXED') /*BUG9570368*/