AP_ISP_PO_RECEIPT_MATCH_V

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 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 CANCEL_FLAG 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 APPROVED_FLAG 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 LEGAL_ENTITY_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 QUANTITY_UNBILLED AMOUNT_UNBILLED TRX_BUSINESS_CATEGORY USER_DEFINED_FISC_CLASS PRODUCT_TYPE PRODUCT_CATEGORY LINE_INTENDED_USE_ID PRODUCT_FISC_CLASS_ID SHIP_FROM_LOCATION_ID FINAL_DISCHARGE_LOCATION_ID CREATION_DATE OBJECT_VERSION_NUMBER PRC_BU_ID CONSUMPTION_HEADER_ID CONSUMPTION_LINE_ID CONSUMPTION_NUMBER CONSUMPTION_LINE_NUMBER CONSUMPTION_DATE CONSUMED_QUANTITY SHIP_TO_CUST_LOCATION_ID SHIP_TO_CUSTOMER_NAME

Query

SQL_Statement

SELECT 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 , DECODE( PLL.shipment_type, 'PREPAYMENT' , pll.quantity_financed, NVL(PLL.quantity_billed,0)) QUANTITY_BILLED, pll.quantity_financed , NVL(pll.uom_code, pl.uom_code) , NULL , ph.pay_on_code , NULL , NULL , NULL , NULL , NULL , null , ph.cancel_flag , 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 , DECODE( PLL.shipment_type, 'PREPAYMENT' , pll.amount_financed, NVL(PLL.amount_billed,0)) 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.approved_flag , 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 , ph.soldto_le_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, (NVL(PLL.QUANTITY, 0) - DECODE( PLL.shipment_type, 'PREPAYMENT' , pll.quantity_financed, NVL(PLL.quantity_billed,0)) - NVL(pll.quantity_cancelled,0)) QUANTITY_UNBILLED, (NVL(PLL.amount, 0) - DECODE( PLL.shipment_type, 'PREPAYMENT' , pll.amount_financed, NVL(PLL.amount_billed,0)) - NVL(pll.amount_cancelled,0)) AMOUNT_UNBILLED, pll.trx_business_category , pll.user_defined_fisc_class , pll.product_type , pll.product_category , pll.line_intended_use_id , pll.product_fisc_class_id , NULL ship_from_location_id , NULL final_discharge_location_id , ph.creation_date , ph.object_version_number , ph.prc_bu_id, NULL consumption_header_id, NULL consumption_line_id, NULL consumption_number, NULL consumption_line_number, NULL consumption_date, 0 consumed_quantity, pll.ship_to_cust_location_id ship_to_cust_location_id, (select party.party_name from hz_parties party where party.party_id=pll.ship_to_cust_id) ship_to_customer_name 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 plc.lookup_type(+) = 'FREIGHT TERMS' AND plc.lookup_code(+) = ph.freight_terms_lookup_code AND ph.terms_id = AT.term_id(+) AND NVL(ph.orchestration_order_flag, 'N') <> 'Y' 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 and nvl(PH.PAY_ON_CODE, 'null') <> 'RECEIPT' AND PH.document_status in ('OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','FINALLY CLOSED','CLOSED') UNION SELECT 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 , AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt(RSL.shipment_line_id, PLL.MATCHING_BASIS, 'QUANTITY_BILLED') QUANTITY_BILLED, pll.quantity_financed , NVL(pll.uom_code, pl.uom_code) , NULL , ph.pay_on_code , RSH.SHIPPED_DATE , RSH.BILL_OF_LADING , RSL.CONTAINER_NUM , NULL , RSH.PACKING_SLIP , RSH.WAYBILL_AIRBILL_NUM , ph.cancel_flag , AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt(RSL.shipment_line_id, PLL.MATCHING_BASIS, 'QUANTITY_RECEIVED') QUANTITY_RECEIVED, pll.quantity_accepted , AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt(RSL.shipment_line_id, PLL.MATCHING_BASIS, 'AMOUNT_RECEIVED') amount_received, pll.receipt_required_flag , pll.inspection_required_flag , pll.quantity_shipped , pll.amount_shipped , pll.quantity quantity , pll.quantity_cancelled , pll.amount , AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt( RSL.shipment_line_id, PLL.MATCHING_BASIS, 'AMOUNT_BILLED') 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.approved_flag , 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 , ph.soldto_le_id , pl.type_1099 , pll.po_line_id , RSL.shipment_line_id rcv_shipment_line_id , RSH.shipment_header_id rcv_shipment_header_id , (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, AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt( RSL.shipment_line_id, PLL.MATCHING_BASIS, 'QUANTITY_UNBILLED') QUANTITY_UNBILLED, AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt( RSL.shipment_line_id, PLL.MATCHING_BASIS, 'AMOUNT_UNBILLED') AMOUNT_UNBILLED, rt.trx_business_category , rt.user_defined_fisc_class , rt.product_type , rt.product_category , rt.intended_use_classif_id , rt.product_fisc_class_id , rt.ship_from_location_id , rt.final_discharge_location_id , ph.creation_date , ph.object_version_number , ph.prc_bu_id, NULL consumption_header_id, NULL consumption_line_id, NULL consumption_number, NULL consumption_line_number, NULL consumption_date, 0 consumed_quantity, pll.ship_to_cust_location_id ship_to_cust_location_id, (select party.party_name from hz_parties party where party.party_id=pll.ship_to_cust_id) ship_to_customer_name 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 , RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, po_system_parameters_all psp, RCV_TRANSACTIONS RT WHERE pll.po_header_id = ph.po_header_id AND pll.po_line_id = pl.po_line_id AND plc.lookup_type(+) = 'FREIGHT TERMS' AND plc.lookup_code(+) = ph.freight_terms_lookup_code AND ph.terms_id = AT.term_id(+) AND NVL(ph.orchestration_order_flag, 'N') <> 'Y' AND NVL(pll.consigned_flag, 'N') = 'N' AND alc.lookup_type(+) = 'MATCHING BASIS' AND alc.lookup_code(+) = nvl(pll.matching_basis, 'QUANTITY') 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 ='R' 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 RSL.PO_LINE_LOCATION_ID(+) = PLL.LINE_LOCATION_ID AND RSH.SHIPMENT_HEADER_ID(+) = RSL.SHIPMENT_HEADER_ID AND RT.PO_HEADER_ID = PH.PO_HEADER_ID AND RT.PO_LINE_ID = PL.PO_LINE_ID AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH') AND NVL(pll.CANCEL_FLAG, 'N') = 'N' AND ph.pcard_id is null AND pll.shipment_type in ('STANDARD','BLANKET', 'SCHEDULED', 'PREPAYMENT') and nvl(PH.PAY_ON_CODE, 'null') <> 'RECEIPT' AND PH.document_status in ('OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','FINALLY CLOSED','CLOSED') UNION SELECT RT.transaction_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 , 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 , imt.billed_quantity , pll.quantity_financed , imt.consumed_quantity_uom_code , NULL , ph.pay_on_code , RSH.SHIPPED_DATE , RSH.BILL_OF_LADING , RSL.CONTAINER_NUM , NULL , RSH.PACKING_SLIP , RSH.WAYBILL_AIRBILL_NUM , ph.cancel_flag , AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt(RSL.shipment_line_id, PLL.MATCHING_BASIS, 'QUANTITY_RECEIVED') QUANTITY_RECEIVED, pll.quantity_accepted , AP_MATCHING_UTILS_PKG.Get_rcv_ship_qty_amt(RSL.shipment_line_id, PLL.MATCHING_BASIS, 'AMOUNT_RECEIVED') amount_received, pll.receipt_required_flag , pll.inspection_required_flag , pll.quantity_shipped , pll.amount_shipped , pll.quantity quantity , pll.quantity_cancelled , pll.amount , imt.billed_amount , pll.amount_financed , pll.amount_cancelled , imt.consumption_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.approved_flag , imt.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 , ph.soldto_le_id , pl.type_1099 , pll.po_line_id , RSL.shipment_line_id rcv_shipment_line_id , RSH.shipment_header_id rcv_shipment_header_id , (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, imt.remaining_quantity quantity_unbilled, 0 amount_unbilled , imt.trx_business_category , imt.user_defined_fisc_class , imt.product_type , imt.product_category , imt.intended_use_classif_id , imt.prod_fc_categ_id , imt.ship_from_location_id , imt.final_discharge_location_id , ph.creation_date , ph.object_version_number , ph.prc_bu_id, 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, pll.ship_to_cust_location_id ship_to_cust_location_id, (select party.party_name from hz_parties party where party.party_id=pll.ship_to_cust_id) ship_to_customer_name 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 , RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, po_system_parameters_all psp, RCV_TRANSACTIONS RT, inv_cons_advice_txns_v IMT WHERE pll.po_header_id = ph.po_header_id AND pll.po_line_id = pl.po_line_id AND plc.lookup_type(+) = 'FREIGHT TERMS' AND plc.lookup_code(+) = ph.freight_terms_lookup_code AND ph.terms_id = AT.term_id(+) AND NVL(ph.orchestration_order_flag, 'N') <> 'Y' AND alc.lookup_type(+) = 'MATCHING BASIS' AND alc.lookup_code(+) = nvl(pll.matching_basis, 'QUANTITY') 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 ='C' 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 RSH.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 = PLL.LINE_LOCATION_ID AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND IMT.po_schedule_id = PLL.line_location_id AND IMT.rcv_transaction_id = RT.transaction_id AND NVL(pll.CANCEL_FLAG, 'N') = 'N' AND ph.pcard_id is null AND pll.shipment_type in ('STANDARD','BLANKET', 'SCHEDULED', 'PREPAYMENT') and nvl(PH.PAY_ON_CODE, 'null') <> 'RECEIPT' AND PH.document_status in ('OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','FINALLY CLOSED','CLOSED')