AP_OTH_CHG_TO_RECEIPTS_MATCH

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
RCV_TRANSACTION_ID RCV_TRANSACTION_DATE ITEM_ID ITEM_DESCRIPTION SUPPLIER_ITEM PO_HEADER_ID PO_NUMBER PO_LINE PO_SCHEDULE RECEIPT_NUMBER RECEIPT_LINE NEED_BY_DATE MATCH_BASIS_LOOKUP_CODE MATCH_BASIS SHIPMENT_TYPE_LOOKUP_CODE SHIPMENT_TYPE CLOSED_CODE_LOOKUP_CODE CLOSED_CODE CLOSED_REASON SHIP_TO_LOCATION SHIP_TO_LOCATION_NAME SHIPMENT_ITEM_DESCRIPTION ITEM_CATEGORY TERM_ID PAYMENT_TERMS FRIEGHT_TERMS QUANTITY_BILLED PO_UNIT_MEAS_LOOKUP_CODE RECEIPT_UNIT_MEAS_LOOKUP_CODE PAY_ON_CODE SHIPPED_DATE BILL_OF_LADING CONTAINER PACKAGING_SLIP WAYBILL_AIRBILL_NUMBER CANCEL_FLAG RECEIPT_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG QUANTITY_SHIPPED AMOUNT_SHIPPED RECEIPT_QUANTITY AMOUNT_BILLED PRICE_OVERRIDE ACCRUE_ON_RECEIPT_FLAG ATTRIBUTE1 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE_CATEGORY SHIPMENT_NUM APPROVED_FLAG CURRENCY_CODE VENDOR_ID MATCH_OPTION_LOOKUP_CODE MATCH_OPTION ORG_ID LEGAL_ENTITY_ID TYPE_1099 LINE_ID RCV_SHIPMENT_LINE_ID RCV_SHIPMENT_HEADER_ID SUPPLIER_NAME SUPPLIER_NUMBER SUPPLIER_SITE VENDOR_SITE_ID VAT_REGISTRATION_NUM LINE_LOCATION_ID INVENTORY_ORGANIZATION_ID DELIVER_TO_PERSON ITEM_NUMBER ITEM_CATEGORY_NAME ASSESSABLE_VALUE ORCHESTRATION_ORDER_FLAG SHIP_TO_CUST_LOCATION_ID SHIP_TO_CUSTOMER_NAME

Name

RCV_TRANSACTION_ID

RCV_TRANSACTION_DATE

ITEM_ID

ITEM_DESCRIPTION

SUPPLIER_ITEM

PO_HEADER_ID

PO_NUMBER

PO_LINE

PO_SCHEDULE

RECEIPT_NUMBER

RECEIPT_LINE

NEED_BY_DATE

MATCH_BASIS_LOOKUP_CODE

MATCH_BASIS

SHIPMENT_TYPE_LOOKUP_CODE

SHIPMENT_TYPE

CLOSED_CODE_LOOKUP_CODE

CLOSED_CODE

CLOSED_REASON

SHIP_TO_LOCATION

SHIP_TO_LOCATION_NAME

SHIPMENT_ITEM_DESCRIPTION

ITEM_CATEGORY

TERM_ID

PAYMENT_TERMS

FRIEGHT_TERMS

QUANTITY_BILLED

PO_UNIT_MEAS_LOOKUP_CODE

RECEIPT_UNIT_MEAS_LOOKUP_CODE

PAY_ON_CODE

SHIPPED_DATE

BILL_OF_LADING

CONTAINER

PACKAGING_SLIP

WAYBILL_AIRBILL_NUMBER

CANCEL_FLAG

RECEIPT_REQUIRED_FLAG

INSPECTION_REQUIRED_FLAG

QUANTITY_SHIPPED

AMOUNT_SHIPPED

RECEIPT_QUANTITY

AMOUNT_BILLED

PRICE_OVERRIDE

ACCRUE_ON_RECEIPT_FLAG

ATTRIBUTE1

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE_CATEGORY

SHIPMENT_NUM

APPROVED_FLAG

CURRENCY_CODE

VENDOR_ID

MATCH_OPTION_LOOKUP_CODE

MATCH_OPTION

ORG_ID

LEGAL_ENTITY_ID

TYPE_1099

LINE_ID

RCV_SHIPMENT_LINE_ID

RCV_SHIPMENT_HEADER_ID

SUPPLIER_NAME

SUPPLIER_NUMBER

SUPPLIER_SITE

VENDOR_SITE_ID

VAT_REGISTRATION_NUM

LINE_LOCATION_ID

INVENTORY_ORGANIZATION_ID

DELIVER_TO_PERSON

ITEM_NUMBER

ITEM_CATEGORY_NAME

ASSESSABLE_VALUE

ORCHESTRATION_ORDER_FLAG

SHIP_TO_CUST_LOCATION_ID

SHIP_TO_CUSTOMER_NAME

Query

SQL_Statement
SELECT RT.TRANSACTION_ID RCV_TRANSACTION_ID , TRUNC(RT.TRANSACTION_DATE) RCV_TRANSACTION_DATE , SL.ITEM_ID ITEM_ID , /* bug#32220237/ NVL(PS.DESCRIPTION,PL.ITEM_DESCRIPTION) ITEM_DESCRIPTION , PL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM , ps.po_header_id PO_HEADER_ID , PH.SEGMENT1 PO_NUMBER , PL.LINE_NUM PO_LINE , PS.SHIPMENT_NUM PO_SCHEDULE , SH.RECEIPT_NUM RECEIPT_NUMBER , SL.LINE_NUM RECEIPT_LINE , PS.NEED_BY_DATE NEED_BY_DATE , ALC1.lookup_code MATCH_BASIS_LOOKUP_CODE , ALC1.displayed_field MATCH_BASIS , / bug#22652320 / (SELECT ALC2.lookup_code FROM AP_LOOKUP_CODES ALC2 WHERE alc2.lookup_type = 'SHIPMENT TYPE' AND alc2.lookup_code = PS.shipment_type) SHIPMENT_TYPE_LOOKUP_CODE , (SELECT ALC2.displayed_field FROM AP_LOOKUP_CODES ALC2 WHERE alc2.lookup_type = 'SHIPMENT TYPE' AND alc2.lookup_code = PS.shipment_type) SHIPMENT_TYPE , NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') CLOSED_CODE_LOOKUP_CODE , (SELECT PLC1.displayed_field FROM PO_LOOKUP_CODES PLC1 WHERE PLC1.lookup_type = 'ORDER_STATUS' AND PLC1.lookup_code = PS.schedule_status) CLOSED_CODE , PS.CLOSED_REASON CLOSED_REASON , SL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION , (SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL_F_VL LOT WHERE LOT.LOCATION_ID= SL.SHIP_TO_LOCATION_ID AND TRUNC(SYSDATE) BETWEEN TRUNC(LOT.EFFECTIVE_START_DATE) AND TRUNC(LOT.EFFECTIVE_END_DATE)) SHIP_TO_LOCATION_NAME, NVL(SL.ITEM_DESCRIPTION,pl.item_description) SHIPMENT_ITEM_DESCRIPTION, SL.CATEGORY_ID ITEM_CATEGORY , (SELECT AT.term_id from ap_terms_v at where ph.terms_id = at.term_id) term_id, / bug#22652320 / (SELECT AT.name from ap_terms_v at where ph.terms_id = at.term_id) payment_terms, / bug#22652320 / / bug#22652320 / (SELECT PLC.MEANING FROM FND_LOOKUPS PLC WHERE PLC.lookup_type = 'FREIGHT TERMS' AND PLC.lookup_code = PH.freight_terms_lookup_code) FRIEGHT_TERMS , RT.QUANTITY_BILLED QUANTITY_BILLED , PL.UOM_CODE PO_UNIT_MEAS_LOOKUP_CODE , RT.UOM_CODE RECEIPT_UNIT_MEAS_LOOKUP_CODE , PH.PAY_ON_CODE PAY_ON_CODE , SH.SHIPPED_DATE SHIPPED_DATE , SH.BILL_OF_LADING BILL_OF_LADING , SL.CONTAINER_NUM CONTAINER , SH.PACKING_SLIP PACKAGING_SLIP , SH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUMBER , PS.CANCEL_FLAG CANCEL_FLAG , PS.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG , PS.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG , PS.QUANTITY_SHIPPED , PS.AMOUNT_SHIPPED , RT.QUANTITY RECEIPT_QUANTITY , RT.AMOUNT_BILLED , NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) PRICE_OVERRIDE , PS.ACCRUE_ON_RECEIPT_FLAG , RT.attribute1 , RT.attribute10 , RT.attribute11 , RT.attribute12 , RT.attribute13 , RT.attribute14 , RT.attribute15 , RT.attribute2 , RT.attribute3 , RT.attribute4 , RT.attribute5 , RT.attribute6 , RT.attribute7 , RT.attribute8 , RT.attribute9 , RT.attribute_category , PS.SHIPMENT_NUM , null APPROVED_FLAG , RT.CURRENCY_CODE , RT.VENDOR_ID , / bug#22652320 / (SELECT ALC.LOOKUP_CODE FROM AP_LOOKUP_CODES ALC WHERE ALC.lookup_type = 'MATCH_OPTION' AND ALC.lookup_code = PS.match_option) MATCH_OPTION_LOOKUP_CODE , (SELECT ALC.DISPLAYED_FIELD FROM AP_LOOKUP_CODES ALC WHERE ALC.lookup_type = 'MATCH_OPTION' AND ALC.lookup_code = PS.match_option) MATCH_OPTION , ph.billto_bu_id ORG_ID , ph.soldto_le_id LEGAL_ENTITY_ID , pl.type_1099 , RT.po_line_id LINE_ID , SL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID , SH.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID , (SELECT HP.PARTY_NAME FROM HZ_PARTIES HP WHERE HP.PARTY_ID = VE.PARTY_ID) AS SUPPLIER_NAME , VE.SEGMENT1 SUPPLIER_NUMBER , VS.VENDOR_SITE_CODE SUPPLIER_SITE , VS.VENDOR_SITE_ID , (SELECT ZPTP.REP_REGISTRATION_NUMBER FROM ZX_PARTY_TAX_PROFILE ZPTP WHERE ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY' AND ZPTP.PARTY_ID = VE.PARTY_ID ) AS VAT_REGISTRATION_NUM , PS.line_location_id LINE_LOCATION_ID, SL.TO_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID, null deliver_to_person, (select esi.item_number from egp_system_items esi where esi.inventory_item_id = pl.item_id and esi.organization_id = ph.billto_bu_id) item_number, (select category_name from egp_categories_vl ecs where ecs.category_id = pl.category_id) item_category_name, PS.assessable_value, PH.ORCHESTRATION_ORDER_FLAG, PS.ship_to_cust_location_id ship_to_cust_location_id, (select party.party_name from hz_parties party where party.party_id=PS.ship_to_cust_id) ship_to_customer_name FROM RCV_TRANSACTIONS RT , PO_HEADERS_ALL PH , PO_LINES_ALL PL , PO_LINE_LOCATIONS_ALL PS , RCV_SHIPMENT_HEADERS SH , RCV_SHIPMENT_LINES SL , / AP_TERMS_V AT , bug#22652320/ POZ_SUPPLIERS VE , POZ_SUPPLIER_SITES_ALL_M PSS , AP_POZ_SITE_AND_ASSIGNMENT_V VS , AP_LOOKUP_CODES ALC1 / Commented for bug#22652320 FND_LOOKUPS PLC , PO_LOOKUP_CODES PLC1 , AP_LOOKUP_CODES ALC2 , AP_LOOKUP_CODES ALC / WHERE 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.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID / bug#22652320 AND PH.TERMS_ID = AT.TERM_ID(+) / AND RT.VENDOR_ID = VE.VENDOR_ID AND RT.vendor_site_id = VS.vendor_site_id AND PH.BILLTO_BU_ID = VS.BU_ID AND RT.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH') AND ALC1.LOOKUP_TYPE = 'MATCHING BASIS' AND ALC1.LOOKUP_CODE = PS.MATCHING_BASIS / bug#22652320 AND PLC.lookup_type(+) = 'FREIGHT TERMS' AND PLC.lookup_code(+) = PH.freight_terms_lookup_code AND PLC1.lookup_type(+) = 'ORDER_STATUS' AND PLC1.lookup_code(+) = PS.schedule_status AND ALC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE' AND ALC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE AND ALC.lookup_type(+) = 'MATCH_OPTION' AND ALC.lookup_code(+) = PS.match_option */

SQL_Statement

SELECT RT.TRANSACTION_ID RCV_TRANSACTION_ID ,

TRUNC(RT.TRANSACTION_DATE) RCV_TRANSACTION_DATE ,

SL.ITEM_ID ITEM_ID ,

/* bug#32220237*/

NVL(PS.DESCRIPTION,PL.ITEM_DESCRIPTION) ITEM_DESCRIPTION ,

PL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM ,

ps.po_header_id PO_HEADER_ID ,

PH.SEGMENT1 PO_NUMBER ,

PL.LINE_NUM PO_LINE ,

PS.SHIPMENT_NUM PO_SCHEDULE ,

SH.RECEIPT_NUM RECEIPT_NUMBER ,

SL.LINE_NUM RECEIPT_LINE ,

PS.NEED_BY_DATE NEED_BY_DATE ,

ALC1.lookup_code MATCH_BASIS_LOOKUP_CODE ,

ALC1.displayed_field MATCH_BASIS ,

/* bug#22652320 */

(SELECT ALC2.lookup_code

FROM AP_LOOKUP_CODES ALC2

WHERE alc2.lookup_type = 'SHIPMENT TYPE'

AND alc2.lookup_code = PS.shipment_type) SHIPMENT_TYPE_LOOKUP_CODE ,

(SELECT ALC2.displayed_field

FROM AP_LOOKUP_CODES ALC2

WHERE alc2.lookup_type = 'SHIPMENT TYPE'

AND alc2.lookup_code = PS.shipment_type) SHIPMENT_TYPE ,

NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') CLOSED_CODE_LOOKUP_CODE ,

(SELECT PLC1.displayed_field

FROM PO_LOOKUP_CODES PLC1

WHERE PLC1.lookup_type = 'ORDER_STATUS'

AND PLC1.lookup_code = PS.schedule_status) CLOSED_CODE ,

PS.CLOSED_REASON CLOSED_REASON ,

SL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION ,

(SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL_F_VL LOT WHERE LOT.LOCATION_ID= SL.SHIP_TO_LOCATION_ID

AND TRUNC(SYSDATE) BETWEEN TRUNC(LOT.EFFECTIVE_START_DATE) AND TRUNC(LOT.EFFECTIVE_END_DATE)) SHIP_TO_LOCATION_NAME,

NVL(SL.ITEM_DESCRIPTION,pl.item_description) SHIPMENT_ITEM_DESCRIPTION,

SL.CATEGORY_ID ITEM_CATEGORY ,

(SELECT AT.term_id from ap_terms_v at where ph.terms_id = at.term_id) term_id, /* bug#22652320 */

(SELECT AT.name from ap_terms_v at where ph.terms_id = at.term_id) payment_terms, /* bug#22652320 */

/* bug#22652320 */

(SELECT PLC.MEANING

FROM FND_LOOKUPS PLC

WHERE PLC.lookup_type = 'FREIGHT TERMS'

AND PLC.lookup_code = PH.freight_terms_lookup_code) FRIEGHT_TERMS ,

RT.QUANTITY_BILLED QUANTITY_BILLED ,

PL.UOM_CODE PO_UNIT_MEAS_LOOKUP_CODE ,

RT.UOM_CODE RECEIPT_UNIT_MEAS_LOOKUP_CODE ,

PH.PAY_ON_CODE PAY_ON_CODE ,

SH.SHIPPED_DATE SHIPPED_DATE ,

SH.BILL_OF_LADING BILL_OF_LADING ,

SL.CONTAINER_NUM CONTAINER ,

SH.PACKING_SLIP PACKAGING_SLIP ,

SH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUMBER ,

PS.CANCEL_FLAG CANCEL_FLAG ,

PS.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG ,

PS.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG ,

PS.QUANTITY_SHIPPED ,

PS.AMOUNT_SHIPPED ,

RT.QUANTITY RECEIPT_QUANTITY ,

RT.AMOUNT_BILLED ,

NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) PRICE_OVERRIDE ,

PS.ACCRUE_ON_RECEIPT_FLAG ,

RT.attribute1 ,

RT.attribute10 ,

RT.attribute11 ,

RT.attribute12 ,

RT.attribute13 ,

RT.attribute14 ,

RT.attribute15 ,

RT.attribute2 ,

RT.attribute3 ,

RT.attribute4 ,

RT.attribute5 ,

RT.attribute6 ,

RT.attribute7 ,

RT.attribute8 ,

RT.attribute9 ,

RT.attribute_category ,

PS.SHIPMENT_NUM ,

null APPROVED_FLAG ,

RT.CURRENCY_CODE ,

RT.VENDOR_ID ,

/* bug#22652320 */

(SELECT ALC.LOOKUP_CODE

FROM AP_LOOKUP_CODES ALC

WHERE ALC.lookup_type = 'MATCH_OPTION'

AND ALC.lookup_code = PS.match_option) MATCH_OPTION_LOOKUP_CODE ,

(SELECT ALC.DISPLAYED_FIELD

FROM AP_LOOKUP_CODES ALC

WHERE ALC.lookup_type = 'MATCH_OPTION'

AND ALC.lookup_code = PS.match_option) MATCH_OPTION ,

ph.billto_bu_id ORG_ID ,

ph.soldto_le_id LEGAL_ENTITY_ID ,

pl.type_1099 ,

RT.po_line_id LINE_ID ,

SL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID ,

SH.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID ,

(SELECT HP.PARTY_NAME

FROM HZ_PARTIES HP

WHERE HP.PARTY_ID = VE.PARTY_ID) AS SUPPLIER_NAME ,

VE.SEGMENT1 SUPPLIER_NUMBER ,

VS.VENDOR_SITE_CODE SUPPLIER_SITE ,

VS.VENDOR_SITE_ID ,

(SELECT ZPTP.REP_REGISTRATION_NUMBER

FROM ZX_PARTY_TAX_PROFILE ZPTP

WHERE ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY'

AND ZPTP.PARTY_ID = VE.PARTY_ID

) AS VAT_REGISTRATION_NUM ,

PS.line_location_id LINE_LOCATION_ID,

SL.TO_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID,

null deliver_to_person,

(select esi.item_number from egp_system_items esi where esi.inventory_item_id = pl.item_id and esi.organization_id = ph.billto_bu_id) item_number,

(select category_name from egp_categories_vl ecs where ecs.category_id = pl.category_id) item_category_name,

PS.assessable_value,

PH.ORCHESTRATION_ORDER_FLAG,

PS.ship_to_cust_location_id ship_to_cust_location_id,

(select party.party_name from hz_parties party where party.party_id=PS.ship_to_cust_id) ship_to_customer_name

FROM RCV_TRANSACTIONS RT ,

PO_HEADERS_ALL PH ,

PO_LINES_ALL PL ,

PO_LINE_LOCATIONS_ALL PS ,

RCV_SHIPMENT_HEADERS SH ,

RCV_SHIPMENT_LINES SL ,

/* AP_TERMS_V AT , bug#22652320*/

POZ_SUPPLIERS VE ,

POZ_SUPPLIER_SITES_ALL_M PSS ,

AP_POZ_SITE_AND_ASSIGNMENT_V VS ,

AP_LOOKUP_CODES ALC1

/* Commented for bug#22652320

FND_LOOKUPS PLC ,

PO_LOOKUP_CODES PLC1 ,

AP_LOOKUP_CODES ALC2 ,

AP_LOOKUP_CODES ALC */

WHERE 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.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID

AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID

/* bug#22652320

AND PH.TERMS_ID = AT.TERM_ID(+) */

AND RT.VENDOR_ID = VE.VENDOR_ID

AND RT.vendor_site_id = VS.vendor_site_id

AND PH.BILLTO_BU_ID = VS.BU_ID

AND RT.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID

AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH')

AND ALC1.LOOKUP_TYPE = 'MATCHING BASIS'

AND ALC1.LOOKUP_CODE = PS.MATCHING_BASIS

/* bug#22652320

AND PLC.lookup_type(+) = 'FREIGHT TERMS'

AND PLC.lookup_code(+) = PH.freight_terms_lookup_code

AND PLC1.lookup_type(+) = 'ORDER_STATUS'

AND PLC1.lookup_code(+) = PS.schedule_status

AND ALC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE'

AND ALC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE

AND ALC.lookup_type(+) = 'MATCH_OPTION'

AND ALC.lookup_code(+) = PS.match_option */