VIEW: APPS.AP_RECEIPT_CORRECTIONS_V

Source

View Text - Preformatted

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,
 PH.SEGMENT1  PO_NUMBER,
 RT.PO_LINE_ID  PO_LINE_ID,
 PL.LINE_NUM  PO_LINE_NUMBER,
 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_ALL AI,
 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')

View Text - HTML Formatted

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
, PH.SEGMENT1 PO_NUMBER
, RT.PO_LINE_ID PO_LINE_ID
, PL.LINE_NUM PO_LINE_NUMBER
, 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_ALL AI
, 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')