[Home] [Help]
View: 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*/