AP_PO_RECEIPT_MATCH

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
SOLDTO_LE_ID 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 PAYMENT_TYPE_LOOKUP_CODE PAYMENT_TYPE SCHEDULE_STATUS_LOOKUP_CODE SCHEDULE_STATUS CLOSED_REASON SHIP_TO_LOCATION_ID BUYER SHIPMENT_ITEM_DESCRIPTION ITEM_CATEGORY TERM_ID PAYMENT_TERMS FRIEGHT_TERMS QUANTITY_BILLED QUANTITY_FINANCED PO_UOM_LOOKUP_CODE RECEIPT_UOM_LOOKUP_CODE PAY_ON_CODE SHIPPED_DATE BILL_OF_LADING CONTAINER FRIEGHT_CARRIER PACKAGING_SLIP WAYBILL_AIRBILL_NUMBER QUANTITY_RECEIVED QUANTITY_ACCEPTED AMOUNT_RECEIVED RECEIPT_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG QUANTITY_SHIPPED AMOUNT_SHIPPED QUANTITY QUANTITY_CANCELLED AMOUNT AMOUNT_BILLED AMOUNT_FINANCED AMOUNT_CANCELLED UNIT_PRICE ACCRUE_ON_RECEIPT_FLAG ATTRIBUTE1 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE_CATEGORY LINE_LOCATION_ID SHIPMENT_NUM CURRENCY_CODE VENDOR_ID GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE20 MATCH_OPTION MATCH_OPTION_DISPLAY MATCH_TYPE ORG_ID TYPE_1099 PO_LINE_ID RCV_SHIPMENT_LINE_ID RCV_SHIPMENT_HEADER_ID SHIP_TO_LOCATION ASSET_CATEGORY_ID FREIGHT_TERMS DOCUMENT_STATUS_LOOKUP_CODE ALLOW_FINAL_MATCH EMAIL_ADDRESS ITEM_NUMBER ITEM_CATEGORY_NAME VALUE_BASIS QUANTITY_RECOUPED AMOUNT_RECOUPED ASSESSABLE_VALUE PROJECT TASK ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 CONSUMPTION_HEADER_ID CONSUMPTION_LINE_ID CONSUMPTION_NUMBER CONSUMPTION_LINE_NUMBER CONSUMPTION_DATE CONSUMED_QUANTITY CONSUMED_QUANTITY_UOM_CODE CONS_BILLED_QUANTITY CONS_BILLED_AMOUNT CONS_CURRENCY_CODE CONS_CURRENCY_CONVERSION_RATE CONSUMPTION_UNIT_PRICE SHIP_TO_CUST_LOCATION_ID

Name

SOLDTO_LE_ID

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

PAYMENT_TYPE_LOOKUP_CODE

PAYMENT_TYPE

SCHEDULE_STATUS_LOOKUP_CODE

SCHEDULE_STATUS

CLOSED_REASON

SHIP_TO_LOCATION_ID

BUYER

SHIPMENT_ITEM_DESCRIPTION

ITEM_CATEGORY

TERM_ID

PAYMENT_TERMS

FRIEGHT_TERMS

QUANTITY_BILLED

QUANTITY_FINANCED

PO_UOM_LOOKUP_CODE

RECEIPT_UOM_LOOKUP_CODE

PAY_ON_CODE

SHIPPED_DATE

BILL_OF_LADING

CONTAINER

FRIEGHT_CARRIER

PACKAGING_SLIP

WAYBILL_AIRBILL_NUMBER

QUANTITY_RECEIVED

QUANTITY_ACCEPTED

AMOUNT_RECEIVED

RECEIPT_REQUIRED_FLAG

INSPECTION_REQUIRED_FLAG

QUANTITY_SHIPPED

AMOUNT_SHIPPED

QUANTITY

QUANTITY_CANCELLED

AMOUNT

AMOUNT_BILLED

AMOUNT_FINANCED

AMOUNT_CANCELLED

UNIT_PRICE

ACCRUE_ON_RECEIPT_FLAG

ATTRIBUTE1

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE_CATEGORY

LINE_LOCATION_ID

SHIPMENT_NUM

CURRENCY_CODE

VENDOR_ID

GLOBAL_ATTRIBUTE_CATEGORY

GLOBAL_ATTRIBUTE1

GLOBAL_ATTRIBUTE2

GLOBAL_ATTRIBUTE3

GLOBAL_ATTRIBUTE4

GLOBAL_ATTRIBUTE5

GLOBAL_ATTRIBUTE6

GLOBAL_ATTRIBUTE7

GLOBAL_ATTRIBUTE8

GLOBAL_ATTRIBUTE9

GLOBAL_ATTRIBUTE10

GLOBAL_ATTRIBUTE11

GLOBAL_ATTRIBUTE12

GLOBAL_ATTRIBUTE13

GLOBAL_ATTRIBUTE14

GLOBAL_ATTRIBUTE15

GLOBAL_ATTRIBUTE16

GLOBAL_ATTRIBUTE17

GLOBAL_ATTRIBUTE18

GLOBAL_ATTRIBUTE19

GLOBAL_ATTRIBUTE20

MATCH_OPTION

MATCH_OPTION_DISPLAY

MATCH_TYPE

ORG_ID

TYPE_1099

PO_LINE_ID

RCV_SHIPMENT_LINE_ID

RCV_SHIPMENT_HEADER_ID

SHIP_TO_LOCATION

ASSET_CATEGORY_ID

FREIGHT_TERMS

DOCUMENT_STATUS_LOOKUP_CODE

ALLOW_FINAL_MATCH

EMAIL_ADDRESS

ITEM_NUMBER

ITEM_CATEGORY_NAME

VALUE_BASIS

QUANTITY_RECOUPED

AMOUNT_RECOUPED

ASSESSABLE_VALUE

PROJECT

TASK

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

CONSUMPTION_HEADER_ID

CONSUMPTION_LINE_ID

CONSUMPTION_NUMBER

CONSUMPTION_LINE_NUMBER

CONSUMPTION_DATE

CONSUMED_QUANTITY

CONSUMED_QUANTITY_UOM_CODE

CONS_BILLED_QUANTITY

CONS_BILLED_AMOUNT

CONS_CURRENCY_CODE

CONS_CURRENCY_CONVERSION_RATE

CONSUMPTION_UNIT_PRICE

SHIP_TO_CUST_LOCATION_ID

Query

SQL_Statement
SELECT ph.soldto_le_id, NULL , NULL , pl.item_id , pl.item_description , NULL , pll.po_header_id , ph.segment1 po_number , pl.line_num line_num , pll.shipment_num , NULL , NULL , pll.need_by_date , alc.lookup_code , alc.displayed_field , alc2.lookup_code , alc2.displayed_field , NULL , NULL , NVL(pll.SCHEDULE_STATUS,'INCOMPLETE') , (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type='ORDER_STATUS' AND lookup_code = NVL(pll.SCHEDULE_STATUS,'INCOMPLETE') ), pll.closed_reason , pll.ship_to_location_id , (SELECT he.full_name FROM per_workforce_current_x he WHERE he.person_id = ph.agent_id AND rownum = 1 ), NVL(pll.description,pl.item_description), pl.category_id , AT.term_id , AT.name , plc.meaning displayed_field , pll.quantity_billed , pll.quantity_financed , NVL(pll.uom_code, pl.uom_code) , NULL , ph.pay_on_code , NULL , NULL , NULL , NULL , NULL , NULL , pll.quantity_received , pll.quantity_accepted , pll.amount_received , pll.receipt_required_flag , pll.inspection_required_flag , pll.quantity_shipped , pll.amount_shipped , pll.quantity quantity , pll.quantity_cancelled , pll.amount , pll.amount_billed , pll.amount_financed , pll.amount_cancelled , NVL(pll.price_override,pl.unit_price) , pll.accrue_on_receipt_flag , pll.attribute1 , pll.attribute10 , pll.attribute11 , pll.attribute12 , pll.attribute13 , pll.attribute14 , pll.attribute15 , pll.attribute2 , pll.attribute3 , pll.attribute4 , pll.attribute5 , pll.attribute6 , pll.attribute7 , pll.attribute8 , pll.attribute9 , pll.attribute_category , pll.line_location_id , pll.shipment_num , ph.currency_code , ph.vendor_id , pll.global_attribute_category , pll.global_attribute1 , pll.global_attribute2 , pll.global_attribute3 , pll.global_attribute4 , pll.global_attribute5 , pll.global_attribute6 , pll.global_attribute7 , pll.global_attribute8 , pll.global_attribute9 , pll.global_attribute10 , pll.global_attribute11 , pll.global_attribute12 , pll.global_attribute13 , pll.global_attribute14 , pll.global_attribute15 , pll.global_attribute16 , pll.global_attribute17 , pll.global_attribute18 , pll.global_attribute19 , pll.global_attribute20 , plc4.lookup_code , plc4.displayed_field , NULL , ph.billto_bu_id , pl.type_1099 , pll.po_line_id , NULL , NULL , (SELECT hl.location_name FROM hr_locations_all_f_vl hl WHERE hl.LOCATION_ID = pll.ship_to_location_id AND TRUNC(sysdate) BETWEEN TRUNC(hl.effective_start_date) AND TRUNC(hl.effective_end_date) ) , NULL, PLC.MEANING FREIGHT_TERMS, NVL(ph.DOCUMENT_STATUS,'INCOMPLETE'), (SELECT allow_final_match_flag FROM ap_system_parameters_all WHERE org_id = ph.billto_bu_id ), (SELECT he.email_address FROM per_workforce_current_x he WHERE he.person_id = ph.agent_id AND rownum = 1 ), egp.item_number, (SELECT category_name FROM egp_categories_vl ecs WHERE ecs.category_id = pl.category_id ) item_category_name, pll.value_basis, pll.quantity_recouped, pll.amount_recouped, pll.assessable_value, NULL project, NULL task, pll.attribute_number1 attribute_number1, pll.attribute_number2 attribute_number2, pll.attribute_number3 attribute_number3, pll.attribute_number4 attribute_number4, pll.attribute_number5 attribute_number5, pll.attribute_date1 attribute_date1, pll.attribute_date2 attribute_date2, pll.attribute_date3 attribute_date3, pll.attribute_date4 attribute_date4, pll.attribute_date5 attribute_date5, NULL consumption_header_id, NULL consumption_line_id, NULL consumption_number, NULL consumption_line_number, NULL consumption_date, NULL consumed_quantity, NULL consumed_quantity_uom_code, NULL cons_billed_quantity, NULL cons_billed_amount, NULL cons_currency_code, NULL cons_currency_conversion_rate, NULL consumption_unit_price, pll.ship_to_cust_location_id ship_to_cust_location_id FROM fnd_lookups plc , ap_terms_v AT , po_headers_all ph , po_lines_all pl , po_line_locations pll, ap_lookup_codes alc , ap_lookup_codes alc2 , po_lookup_codes plc4 , egp_system_items egp , po_system_parameters_all psp WHERE pll.po_header_id = ph.po_header_id AND pll.po_line_id = pl.po_line_id AND ph.terms_id = AT.term_id(+) AND NVL(pll.consigned_flag, 'N') = 'N' AND alc.lookup_type(+) = 'MATCHING BASIS' AND alc.lookup_code(+) = pll.matching_basis AND alc2.lookup_type(+) = 'SHIPMENT TYPE' AND alc2.lookup_code(+) = pll.shipment_type AND plc4.lookup_type(+) = 'PO INVOICE MATCH OPTION' AND plc4.lookup_code(+) = pll.match_option AND pll.match_option ='P' AND psp.prc_bu_id = ph.prc_bu_id AND egp.inventory_item_id (+) = pl.item_id AND NVL(egp.organization_id, pll.ship_to_organization_id) = pll.ship_to_organization_id AND PLC.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND PLC.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE UNION SELECT ph.soldto_le_id, RT.TRANSACTION_ID , RT.TRANSACTION_DATE , SL.ITEM_ID , PL.ITEM_DESCRIPTION , SL.VENDOR_ITEM_NUM , ps.po_header_id , PH.SEGMENT1 , PL.LINE_NUM , PS.SHIPMENT_NUM , SH.RECEIPT_NUM , SL.LINE_NUM , PS.NEED_BY_DATE , ALC.LOOKUP_CODE , ALC.DISPLAYED_FIELD , ALC2.LOOKUP_CODE , ALC2.DISPLAYED_FIELD , NULL , NULL , NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') , (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type='ORDER_STATUS' AND lookup_code = NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') ), PS.CLOSED_REASON CLOSED_REASON, SL.SHIP_TO_LOCATION_ID , (SELECT he.full_name FROM per_workforce_current_x he WHERE he.person_id = ph.agent_id AND rownum = 1 ), NVL(SL.ITEM_DESCRIPTION,pl.item_description), SL.CATEGORY_ID , AT.term_id , AT.name , PLC.MEANING DISPLAYED_FIELD , RT.QUANTITY_BILLED , NULL , PL.Uom_codE PO_UOM_LOOKUP_CODE , NVL(RT.BILLING_UOM_CODE, RT.UOM_CODE) RECEIPT_UOM_LOOKUP_CODE , PH.PAY_ON_CODE , SH.SHIPPED_DATE , SH.BILL_OF_LADING , SL.CONTAINER_NUM , NULL , SH.PACKING_SLIP , SH.WAYBILL_AIRBILL_NUM , NULL , NULL , NULL , PS.RECEIPT_REQUIRED_FLAG , PS.INSPECTION_REQUIRED_FLAG , PS.QUANTITY_SHIPPED , PS.AMOUNT_SHIPPED , RT.QUANTITY , NULL , RT.AMOUNT , RT.AMOUNT_BILLED , NULL , NULL , NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) , 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 , RT.PO_LINE_LOCATION_ID , PS.SHIPMENT_NUM , PH.CURRENCY_CODE , PH.VENDOR_ID , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , plc4.lookup_code , plc4.displayed_field , NULL , ph.billto_bu_id , pl.type_1099 , RT.po_line_id , SL.SHIPMENT_LINE_ID , SH.SHIPMENT_HEADER_ID , (SELECT hl.location_name FROM hr_locations_all_f_vl hl WHERE hl.LOCATION_ID = ps.ship_to_location_id AND TRUNC(sysdate) BETWEEN TRUNC(hl.effective_start_date) AND TRUNC(hl.effective_end_date) ) , NULL, PLC.meaning FREIGHT_TERMS, NVL(ph.DOCUMENT_STATUS,'INCOMPLETE'), (SELECT allow_final_match_flag FROM ap_system_parameters_all WHERE org_id = ph.billto_bu_id ), (SELECT he.email_address FROM per_workforce_current_x he WHERE he.person_id = ph.agent_id AND rownum = 1 ), egp.item_number, (SELECT category_name FROM egp_categories_vl ecs WHERE ecs.category_id = sl.category_id ) item_category_name, ps.value_basis, ps.quantity_recouped, ps.amount_recouped, ps.assessable_value, NULL project, NULL task, sl.attribute_number1 attribute_number1, sl.attribute_number2 attribute_number2, sl.attribute_number3 attribute_number3, sl.attribute_number4 attribute_number4, sl.attribute_number5 attribute_number5, sl.attribute_date1 attribute_date1, sl.attribute_date2 attribute_date2, sl.attribute_date3 attribute_date3, sl.attribute_date4 attribute_date4, sl.attribute_date5 attribute_date5, NULL consumption_header_id, NULL consumption_line_id, NULL consumption_number, NULL consumption_line_number, NULL consumption_date, NULL consumed_quantity, NULL consumed_quantity_uom_code, NULL cons_billed_quantity, NULL cons_billed_amount, NULL cons_currency_code, NULL cons_currency_conversion_rate, NULL consumption_unit_price, PS.ship_to_cust_location_id ship_to_cust_location_id FROM RCV_TRANSACTIONS RT, PO_HEADERS PH , PO_LINES_ALL PL , PO_LINE_LOCATIONS_ALL PS , RCV_SHIPMENT_HEADERS SH, RCV_SHIPMENT_LINES SL , AP_TERMS_V AT , AP_LOOKUP_CODES ALC , FND_LOOKUPS PLC , AP_LOOKUP_CODES ALC2 , PO_LOOKUP_CODES PLC4 , egp_system_items egp , po_system_parameters_all psp 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 AND PH.PO_HEADER_ID = PS.PO_HEADER_ID AND PH.TERMS_ID = AT.TERM_ID(+) AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH') AND ALC.LOOKUP_TYPE = 'MATCHING BASIS' AND ALC.LOOKUP_CODE = PS.MATCHING_BASIS AND PLC.lookup_type(+) = 'FREIGHT TERMS' AND PLC.lookup_code(+) = PH.freight_terms_lookup_code AND ALC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE' AND ALC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE AND plc4.lookup_type(+) = 'PO INVOICE MATCH OPTION' AND plc4.lookup_code(+) = PS.match_option AND PS.match_option ='R' AND psp.prc_bu_id = ph.prc_bu_id AND egp.inventory_item_id (+) = pl.item_id AND NVL(egp.organization_id, ps.ship_to_organization_id) = ps.ship_to_organization_id UNION SELECT ph.soldto_le_id, RT.TRANSACTION_ID , RT.TRANSACTION_DATE , SL.ITEM_ID , PL.ITEM_DESCRIPTION , SL.VENDOR_ITEM_NUM , ps.po_header_id , PH.SEGMENT1 , PL.LINE_NUM , PS.SHIPMENT_NUM , SH.RECEIPT_NUM , SL.LINE_NUM , PS.NEED_BY_DATE , ALC.LOOKUP_CODE , ALC.DISPLAYED_FIELD , ALC2.LOOKUP_CODE , ALC2.DISPLAYED_FIELD , NULL , NULL , NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') , (SELECT displayed_field FROM po_lookup_codes WHERE lookup_type='ORDER_STATUS' AND lookup_code = NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') ), PS.CLOSED_REASON CLOSED_REASON, SL.SHIP_TO_LOCATION_ID , (SELECT he.full_name FROM per_workforce_current_x he WHERE he.person_id = ph.agent_id AND rownum = 1 ), NVL(SL.ITEM_DESCRIPTION,pl.item_description), SL.CATEGORY_ID , AT.term_id , AT.name , PLC.MEANING DISPLAYED_FIELD , RT.QUANTITY_BILLED , NULL , PL.Uom_codE PO_UOM_LOOKUP_CODE , NVL(RT.BILLING_UOM_CODE, RT.UOM_CODE) RECEIPT_UOM_LOOKUP_CODE , PH.PAY_ON_CODE , SH.SHIPPED_DATE , SH.BILL_OF_LADING , SL.CONTAINER_NUM , NULL , SH.PACKING_SLIP , SH.WAYBILL_AIRBILL_NUM , NULL , NULL , NULL , PS.RECEIPT_REQUIRED_FLAG , PS.INSPECTION_REQUIRED_FLAG , PS.QUANTITY_SHIPPED , PS.AMOUNT_SHIPPED , RT.QUANTITY , NULL , RT.AMOUNT , RT.AMOUNT_BILLED , NULL , NULL , NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) , 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 , RT.PO_LINE_LOCATION_ID , PS.SHIPMENT_NUM , PH.CURRENCY_CODE , PH.VENDOR_ID , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , plc4.lookup_code , plc4.displayed_field , NULL , ph.billto_bu_id , pl.type_1099 , RT.po_line_id , SL.SHIPMENT_LINE_ID , SH.SHIPMENT_HEADER_ID , (SELECT hl.location_name FROM hr_locations_all_f_vl hl WHERE hl.LOCATION_ID = ps.ship_to_location_id AND TRUNC(sysdate) BETWEEN TRUNC(hl.effective_start_date) AND TRUNC(hl.effective_end_date) ) , NULL, PLC.meaning FREIGHT_TERMS, NVL(ph.DOCUMENT_STATUS,'INCOMPLETE'), (SELECT allow_final_match_flag FROM ap_system_parameters_all WHERE org_id = ph.billto_bu_id ), (SELECT he.email_address FROM per_workforce_current_x he WHERE he.person_id = ph.agent_id AND rownum = 1 ), egp.item_number, (SELECT category_name FROM egp_categories_vl ecs WHERE ecs.category_id = sl.category_id ) item_category_name, ps.value_basis, ps.quantity_recouped, ps.amount_recouped, ps.assessable_value, NULL project, NULL task, sl.attribute_number1 attribute_number1, sl.attribute_number2 attribute_number2, sl.attribute_number3 attribute_number3, sl.attribute_number4 attribute_number4, sl.attribute_number5 attribute_number5, sl.attribute_date1 attribute_date1, sl.attribute_date2 attribute_date2, sl.attribute_date3 attribute_date3, sl.attribute_date4 attribute_date4, sl.attribute_date5 attribute_date5, imt.consumption_header_id consumption_header_id, imt.consumption_line_id consumption_line_id, imt.consumption_number consumption_number, imt.consumption_line_number consumption_line_number, imt.consumption_advice_gen_date consumption_date, imt.consumed_quantity consumed_quantity, imt.consumed_quantity_uom_code consumed_quantity_uom_code, imt.billed_quantity cons_billed_quantity, imt.billed_amount cons_billed_amount, imt.currency_code cons_currency_code, imt.currency_conversion_rate cons_currency_conversion_rate, imt.CONSUMPTION_UNIT_PRICE consumption_unit_price, PS.ship_to_cust_location_id ship_to_cust_location_id FROM RCV_TRANSACTIONS RT, PO_HEADERS PH , PO_LINES_ALL PL , PO_LINE_LOCATIONS_ALL PS , RCV_SHIPMENT_HEADERS SH, RCV_SHIPMENT_LINES SL , AP_TERMS_V AT , AP_LOOKUP_CODES ALC , FND_LOOKUPS PLC , AP_LOOKUP_CODES ALC2 , PO_LOOKUP_CODES PLC4 , egp_system_items egp , po_system_parameters_all psp, inv_cons_advice_txns_v IMT 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 AND PH.PO_HEADER_ID = PS.PO_HEADER_ID AND PH.TERMS_ID = AT.TERM_ID(+) AND ALC.LOOKUP_TYPE = 'MATCHING BASIS' AND ALC.LOOKUP_CODE = PS.MATCHING_BASIS AND PLC.lookup_type(+) = 'FREIGHT TERMS' AND PLC.lookup_code(+) = PH.freight_terms_lookup_code AND ALC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE' AND ALC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE AND plc4.lookup_type(+) = 'PO INVOICE MATCH OPTION' AND plc4.lookup_code(+) = PS.match_option AND PS.match_option ='C' AND psp.prc_bu_id = ph.prc_bu_id AND egp.inventory_item_id (+) = pl.item_id AND NVL(egp.organization_id, ps.ship_to_organization_id) = ps.ship_to_organization_id AND IMT.po_schedule_id = ps.line_location_id AND IMT.rcv_transaction_id = RT.transaction_id

SQL_Statement

SELECT ph.soldto_le_id,

NULL ,

pl.item_id ,

pl.item_description ,

NULL ,

pll.po_header_id ,

ph.segment1 po_number ,

pl.line_num line_num ,

pll.shipment_num ,

NULL ,

pll.need_by_date ,

alc.lookup_code ,

alc.displayed_field ,

alc2.lookup_code ,

alc2.displayed_field ,

NULL ,

NVL(pll.SCHEDULE_STATUS,'INCOMPLETE') ,

(SELECT displayed_field

FROM po_lookup_codes

WHERE lookup_type='ORDER_STATUS'

AND lookup_code = NVL(pll.SCHEDULE_STATUS,'INCOMPLETE')

),

pll.closed_reason ,

pll.ship_to_location_id ,

(SELECT he.full_name

FROM per_workforce_current_x he

WHERE he.person_id = ph.agent_id

AND rownum = 1

),

NVL(pll.description,pl.item_description),

pl.category_id ,

AT.term_id ,

AT.name ,

plc.meaning displayed_field ,

pll.quantity_billed ,

pll.quantity_financed ,

NVL(pll.uom_code, pl.uom_code) ,

NULL ,

ph.pay_on_code ,

NULL ,

pll.quantity_received ,

pll.quantity_accepted ,

pll.amount_received ,

pll.receipt_required_flag ,

pll.inspection_required_flag ,

pll.quantity_shipped ,

pll.amount_shipped ,

pll.quantity quantity ,

pll.quantity_cancelled ,

pll.amount ,

pll.amount_billed ,

pll.amount_financed ,

pll.amount_cancelled ,

NVL(pll.price_override,pl.unit_price) ,

pll.accrue_on_receipt_flag ,

pll.attribute1 ,

pll.attribute10 ,

pll.attribute11 ,

pll.attribute12 ,

pll.attribute13 ,

pll.attribute14 ,

pll.attribute15 ,

pll.attribute2 ,

pll.attribute3 ,

pll.attribute4 ,

pll.attribute5 ,

pll.attribute6 ,

pll.attribute7 ,

pll.attribute8 ,

pll.attribute9 ,

pll.attribute_category ,

pll.line_location_id ,

pll.shipment_num ,

ph.currency_code ,

ph.vendor_id ,

pll.global_attribute_category ,

pll.global_attribute1 ,

pll.global_attribute2 ,

pll.global_attribute3 ,

pll.global_attribute4 ,

pll.global_attribute5 ,

pll.global_attribute6 ,

pll.global_attribute7 ,

pll.global_attribute8 ,

pll.global_attribute9 ,

pll.global_attribute10 ,

pll.global_attribute11 ,

pll.global_attribute12 ,

pll.global_attribute13 ,

pll.global_attribute14 ,

pll.global_attribute15 ,

pll.global_attribute16 ,

pll.global_attribute17 ,

pll.global_attribute18 ,

pll.global_attribute19 ,

pll.global_attribute20 ,

plc4.lookup_code ,

plc4.displayed_field ,

NULL ,

ph.billto_bu_id ,

pl.type_1099 ,

pll.po_line_id ,

NULL ,

(SELECT hl.location_name

FROM hr_locations_all_f_vl hl

WHERE hl.LOCATION_ID = pll.ship_to_location_id

AND TRUNC(sysdate) BETWEEN TRUNC(hl.effective_start_date) AND TRUNC(hl.effective_end_date)

) ,

NULL,

PLC.MEANING FREIGHT_TERMS,

NVL(ph.DOCUMENT_STATUS,'INCOMPLETE'),

(SELECT allow_final_match_flag

FROM ap_system_parameters_all

WHERE org_id = ph.billto_bu_id

),

(SELECT he.email_address

FROM per_workforce_current_x he

WHERE he.person_id = ph.agent_id

AND rownum = 1

),

egp.item_number,

(SELECT category_name

FROM egp_categories_vl ecs

WHERE ecs.category_id = pl.category_id

) item_category_name,

pll.value_basis,

pll.quantity_recouped,

pll.amount_recouped,

pll.assessable_value,

NULL project,

NULL task,

pll.attribute_number1 attribute_number1,

pll.attribute_number2 attribute_number2,

pll.attribute_number3 attribute_number3,

pll.attribute_number4 attribute_number4,

pll.attribute_number5 attribute_number5,

pll.attribute_date1 attribute_date1,

pll.attribute_date2 attribute_date2,

pll.attribute_date3 attribute_date3,

pll.attribute_date4 attribute_date4,

pll.attribute_date5 attribute_date5,

NULL consumption_header_id,

NULL consumption_line_id,

NULL consumption_number,

NULL consumption_line_number,

NULL consumption_date,

NULL consumed_quantity,

NULL consumed_quantity_uom_code,

NULL cons_billed_quantity,

NULL cons_billed_amount,

NULL cons_currency_code,

NULL cons_currency_conversion_rate,

NULL consumption_unit_price,

pll.ship_to_cust_location_id ship_to_cust_location_id

FROM fnd_lookups plc ,

ap_terms_v AT ,

po_headers_all ph ,

po_lines_all pl ,

po_line_locations pll,

ap_lookup_codes alc ,

ap_lookup_codes alc2 ,

po_lookup_codes plc4 ,

egp_system_items egp ,

po_system_parameters_all psp

WHERE pll.po_header_id = ph.po_header_id

AND pll.po_line_id = pl.po_line_id

AND ph.terms_id = AT.term_id(+)

AND NVL(pll.consigned_flag, 'N') = 'N'

AND alc.lookup_type(+) = 'MATCHING BASIS'

AND alc.lookup_code(+) = pll.matching_basis

AND alc2.lookup_type(+) = 'SHIPMENT TYPE'

AND alc2.lookup_code(+) = pll.shipment_type

AND plc4.lookup_type(+) = 'PO INVOICE MATCH OPTION'

AND plc4.lookup_code(+) = pll.match_option

AND pll.match_option ='P'

AND psp.prc_bu_id = ph.prc_bu_id

AND egp.inventory_item_id (+) = pl.item_id

AND NVL(egp.organization_id, pll.ship_to_organization_id) = pll.ship_to_organization_id

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

AND PLC.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE

UNION

SELECT ph.soldto_le_id,

RT.TRANSACTION_ID ,

RT.TRANSACTION_DATE ,

SL.ITEM_ID ,

PL.ITEM_DESCRIPTION ,

SL.VENDOR_ITEM_NUM ,

ps.po_header_id ,

PH.SEGMENT1 ,

PL.LINE_NUM ,

PS.SHIPMENT_NUM ,

SH.RECEIPT_NUM ,

SL.LINE_NUM ,

PS.NEED_BY_DATE ,

ALC.LOOKUP_CODE ,

ALC.DISPLAYED_FIELD ,

ALC2.LOOKUP_CODE ,

ALC2.DISPLAYED_FIELD ,

NULL ,

NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') ,

(SELECT displayed_field

FROM po_lookup_codes

WHERE lookup_type='ORDER_STATUS'

AND lookup_code = NVL(PS.SCHEDULE_STATUS,'INCOMPLETE')

),

PS.CLOSED_REASON CLOSED_REASON,

SL.SHIP_TO_LOCATION_ID ,

(SELECT he.full_name

FROM per_workforce_current_x he

WHERE he.person_id = ph.agent_id

AND rownum = 1

),

NVL(SL.ITEM_DESCRIPTION,pl.item_description),

SL.CATEGORY_ID ,

AT.term_id ,

AT.name ,

PLC.MEANING DISPLAYED_FIELD ,

RT.QUANTITY_BILLED ,

NULL ,

PL.Uom_codE PO_UOM_LOOKUP_CODE ,

NVL(RT.BILLING_UOM_CODE, RT.UOM_CODE) RECEIPT_UOM_LOOKUP_CODE ,

PH.PAY_ON_CODE ,

SH.SHIPPED_DATE ,

SH.BILL_OF_LADING ,

SL.CONTAINER_NUM ,

NULL ,

SH.PACKING_SLIP ,

SH.WAYBILL_AIRBILL_NUM ,

NULL ,

PS.RECEIPT_REQUIRED_FLAG ,

PS.INSPECTION_REQUIRED_FLAG ,

PS.QUANTITY_SHIPPED ,

PS.AMOUNT_SHIPPED ,

RT.QUANTITY ,

NULL ,

RT.AMOUNT ,

RT.AMOUNT_BILLED ,

NULL ,

NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) ,

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 ,

RT.PO_LINE_LOCATION_ID ,

PS.SHIPMENT_NUM ,

PH.CURRENCY_CODE ,

PH.VENDOR_ID ,

NULL ,

plc4.lookup_code ,

plc4.displayed_field ,

NULL ,

ph.billto_bu_id ,

pl.type_1099 ,

RT.po_line_id ,

SL.SHIPMENT_LINE_ID ,

SH.SHIPMENT_HEADER_ID ,

(SELECT hl.location_name

FROM hr_locations_all_f_vl hl

WHERE hl.LOCATION_ID = ps.ship_to_location_id

AND TRUNC(sysdate) BETWEEN TRUNC(hl.effective_start_date) AND TRUNC(hl.effective_end_date)

) ,

NULL,

PLC.meaning FREIGHT_TERMS,

NVL(ph.DOCUMENT_STATUS,'INCOMPLETE'),

(SELECT allow_final_match_flag

FROM ap_system_parameters_all

WHERE org_id = ph.billto_bu_id

),

(SELECT he.email_address

FROM per_workforce_current_x he

WHERE he.person_id = ph.agent_id

AND rownum = 1

),

egp.item_number,

(SELECT category_name

FROM egp_categories_vl ecs

WHERE ecs.category_id = sl.category_id

) item_category_name,

ps.value_basis,

ps.quantity_recouped,

ps.amount_recouped,

ps.assessable_value,

NULL project,

NULL task,

sl.attribute_number1 attribute_number1,

sl.attribute_number2 attribute_number2,

sl.attribute_number3 attribute_number3,

sl.attribute_number4 attribute_number4,

sl.attribute_number5 attribute_number5,

sl.attribute_date1 attribute_date1,

sl.attribute_date2 attribute_date2,

sl.attribute_date3 attribute_date3,

sl.attribute_date4 attribute_date4,

sl.attribute_date5 attribute_date5,

NULL consumption_header_id,

NULL consumption_line_id,

NULL consumption_number,

NULL consumption_line_number,

NULL consumption_date,

NULL consumed_quantity,

NULL consumed_quantity_uom_code,

NULL cons_billed_quantity,

NULL cons_billed_amount,

NULL cons_currency_code,

NULL cons_currency_conversion_rate,

NULL consumption_unit_price,

PS.ship_to_cust_location_id ship_to_cust_location_id

FROM RCV_TRANSACTIONS RT,

PO_HEADERS PH ,

PO_LINES_ALL PL ,

PO_LINE_LOCATIONS_ALL PS ,

RCV_SHIPMENT_HEADERS SH,

RCV_SHIPMENT_LINES SL ,

AP_TERMS_V AT ,

AP_LOOKUP_CODES ALC ,

FND_LOOKUPS PLC ,

AP_LOOKUP_CODES ALC2 ,

PO_LOOKUP_CODES PLC4 ,

egp_system_items egp ,

po_system_parameters_all psp

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

AND PH.PO_HEADER_ID = PS.PO_HEADER_ID

AND PH.TERMS_ID = AT.TERM_ID(+)

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

AND ALC.LOOKUP_TYPE = 'MATCHING BASIS'

AND ALC.LOOKUP_CODE = PS.MATCHING_BASIS

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

AND PLC.lookup_code(+) = PH.freight_terms_lookup_code

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

AND ALC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE

AND plc4.lookup_type(+) = 'PO INVOICE MATCH OPTION'

AND plc4.lookup_code(+) = PS.match_option

AND PS.match_option ='R'

AND psp.prc_bu_id = ph.prc_bu_id

AND egp.inventory_item_id (+) = pl.item_id

AND NVL(egp.organization_id, ps.ship_to_organization_id) = ps.ship_to_organization_id

UNION

SELECT ph.soldto_le_id,

RT.TRANSACTION_ID ,

RT.TRANSACTION_DATE ,

SL.ITEM_ID ,

PL.ITEM_DESCRIPTION ,

SL.VENDOR_ITEM_NUM ,

ps.po_header_id ,

PH.SEGMENT1 ,

PL.LINE_NUM ,

PS.SHIPMENT_NUM ,

SH.RECEIPT_NUM ,

SL.LINE_NUM ,

PS.NEED_BY_DATE ,

ALC.LOOKUP_CODE ,

ALC.DISPLAYED_FIELD ,

ALC2.LOOKUP_CODE ,

ALC2.DISPLAYED_FIELD ,

NULL ,

NVL(PS.SCHEDULE_STATUS,'INCOMPLETE') ,

(SELECT displayed_field

FROM po_lookup_codes

WHERE lookup_type='ORDER_STATUS'

AND lookup_code = NVL(PS.SCHEDULE_STATUS,'INCOMPLETE')

),

PS.CLOSED_REASON CLOSED_REASON,

SL.SHIP_TO_LOCATION_ID ,

(SELECT he.full_name

FROM per_workforce_current_x he

WHERE he.person_id = ph.agent_id

AND rownum = 1

),

NVL(SL.ITEM_DESCRIPTION,pl.item_description),

SL.CATEGORY_ID ,

AT.term_id ,

AT.name ,

PLC.MEANING DISPLAYED_FIELD ,

RT.QUANTITY_BILLED ,

NULL ,

PL.Uom_codE PO_UOM_LOOKUP_CODE ,

NVL(RT.BILLING_UOM_CODE, RT.UOM_CODE) RECEIPT_UOM_LOOKUP_CODE ,

PH.PAY_ON_CODE ,

SH.SHIPPED_DATE ,

SH.BILL_OF_LADING ,

SL.CONTAINER_NUM ,

NULL ,

SH.PACKING_SLIP ,

SH.WAYBILL_AIRBILL_NUM ,

NULL ,

PS.RECEIPT_REQUIRED_FLAG ,

PS.INSPECTION_REQUIRED_FLAG ,

PS.QUANTITY_SHIPPED ,

PS.AMOUNT_SHIPPED ,

RT.QUANTITY ,

NULL ,

RT.AMOUNT ,

RT.AMOUNT_BILLED ,

NULL ,

NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) ,

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 ,

RT.PO_LINE_LOCATION_ID ,

PS.SHIPMENT_NUM ,

PH.CURRENCY_CODE ,

PH.VENDOR_ID ,

NULL ,

plc4.lookup_code ,

plc4.displayed_field ,

NULL ,

ph.billto_bu_id ,

pl.type_1099 ,

RT.po_line_id ,

SL.SHIPMENT_LINE_ID ,

SH.SHIPMENT_HEADER_ID ,

(SELECT hl.location_name

FROM hr_locations_all_f_vl hl

WHERE hl.LOCATION_ID = ps.ship_to_location_id

AND TRUNC(sysdate) BETWEEN TRUNC(hl.effective_start_date) AND TRUNC(hl.effective_end_date)

) ,

NULL,

PLC.meaning FREIGHT_TERMS,

NVL(ph.DOCUMENT_STATUS,'INCOMPLETE'),

(SELECT allow_final_match_flag

FROM ap_system_parameters_all

WHERE org_id = ph.billto_bu_id

),

(SELECT he.email_address

FROM per_workforce_current_x he

WHERE he.person_id = ph.agent_id

AND rownum = 1

),

egp.item_number,

(SELECT category_name

FROM egp_categories_vl ecs

WHERE ecs.category_id = sl.category_id

) item_category_name,

ps.value_basis,

ps.quantity_recouped,

ps.amount_recouped,

ps.assessable_value,

NULL project,

NULL task,

sl.attribute_number1 attribute_number1,

sl.attribute_number2 attribute_number2,

sl.attribute_number3 attribute_number3,

sl.attribute_number4 attribute_number4,

sl.attribute_number5 attribute_number5,

sl.attribute_date1 attribute_date1,

sl.attribute_date2 attribute_date2,

sl.attribute_date3 attribute_date3,

sl.attribute_date4 attribute_date4,

sl.attribute_date5 attribute_date5,

imt.consumption_header_id consumption_header_id,

imt.consumption_line_id consumption_line_id,

imt.consumption_number consumption_number,

imt.consumption_line_number consumption_line_number,

imt.consumption_advice_gen_date consumption_date,

imt.consumed_quantity consumed_quantity,

imt.consumed_quantity_uom_code consumed_quantity_uom_code,

imt.billed_quantity cons_billed_quantity,

imt.billed_amount cons_billed_amount,

imt.currency_code cons_currency_code,

imt.currency_conversion_rate cons_currency_conversion_rate,

imt.CONSUMPTION_UNIT_PRICE consumption_unit_price,

PS.ship_to_cust_location_id ship_to_cust_location_id

FROM RCV_TRANSACTIONS RT,

PO_HEADERS PH ,

PO_LINES_ALL PL ,

PO_LINE_LOCATIONS_ALL PS ,

RCV_SHIPMENT_HEADERS SH,

RCV_SHIPMENT_LINES SL ,

AP_TERMS_V AT ,

AP_LOOKUP_CODES ALC ,

FND_LOOKUPS PLC ,

AP_LOOKUP_CODES ALC2 ,

PO_LOOKUP_CODES PLC4 ,

egp_system_items egp ,

po_system_parameters_all psp,

inv_cons_advice_txns_v IMT

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

AND PH.PO_HEADER_ID = PS.PO_HEADER_ID

AND PH.TERMS_ID = AT.TERM_ID(+)

AND ALC.LOOKUP_TYPE = 'MATCHING BASIS'

AND ALC.LOOKUP_CODE = PS.MATCHING_BASIS

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

AND PLC.lookup_code(+) = PH.freight_terms_lookup_code

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

AND ALC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE

AND plc4.lookup_type(+) = 'PO INVOICE MATCH OPTION'

AND plc4.lookup_code(+) = PS.match_option

AND PS.match_option ='C'

AND psp.prc_bu_id = ph.prc_bu_id

AND egp.inventory_item_id (+) = pl.item_id

AND NVL(egp.organization_id, ps.ship_to_organization_id) = ps.ship_to_organization_id

AND IMT.po_schedule_id = ps.line_location_id

AND IMT.rcv_transaction_id = RT.transaction_id