AP_PO_RECEIPT_CORRECTIONS

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
INVOICE_ID INVOICE_NUMBER INVOICE_LINE INVOICE_LINE_AMOUNT INVOICE_DATE ACCOUNTING_DATE LINE_QUANTITY_INVOICED LINE_UNIT_PRICE UNIT_MEAS_LOOKUP_CODE PO_NUMBER PO_LINE PO_SCHEDULE RECEIPT_NUMBER RCV_TRANSACTION_DATE BILL_OF_LADING SHIP_TO_LOCATION ITEM_ID ITEM_CATEGORY PACKING_SLIP WAYBILL_AIRBILL_NUM SHIPPED_DATE INVOICE_AMOUNT RCV_TRANSACTION_ID LINE_LOCATION_ID ITEM_DESCRIPTION SUPPLIER_ITEM_NUMBER PO_HEADER_ID RCV_SHIPMENT_LINE_NUMBER NEED_BY_DATE MATCHING_BASIS_LOOKUP_CODE MATCHING_BASIS BUYER SHIPMENT_ITEM_DESCRIPTION TERM_ID PAYMENT_TERMS FREIGHT_TERMS QUANTITY_BILLED AMOUNT_ACCEPTED CANCEL_FLAG QUANTITY_RECEIVED QUANTITY_ACCEPTED AMOUNT_RECEIVED QUANTITY_SHIPPED AMOUNT_SHIPPED QUANTITY QUANTITY_CANCELLED AMOUNT AMOUNT_BILLED AMOUNT_CANCELLED ATTRIBUTE1 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE_CATEGORY CURRENCY_CODE VENDOR_ID MATCH_OPTION_LOOKUP_CODE MATCH_OPTION ORG_ID LEGAL_ENTITY_ID TYPE_1099 PO_LINE_ID RCV_SHIPMENT_HEADER_ID RCV_SHIPMENT_LINE_ID HOLD_REASON CLOSED_CODE_LOOKUP_CODE CLOSED_CODE CLOSED_REASON PAY_ON_CODE PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_TASK_ID PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_WORK_TYPE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_RESERVED_ATTRIBUTE10 PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_USER_DEF_ATTRIBUTE10 SHIP_TO_LOCATION_ID PRIMARY_INTENDED_USE PRODUCT_FISC_CLASSIFICATION TRX_BUSINESS_CATEGORY PRODUCT_TYPE PRODUCT_CATEGORY USER_DEFINED_FISC_CLASS MERCHANT_DOCUMENT_NUMBER MERCHANT_NAME MERCHANT_REFERENCE MERCHANT_TAX_REG_NUMBER MERCHANT_TAXPAYER_ID COUNTRY_OF_SUPPLY CREDIT_CARD_TRX_ID COMPANY_PREPAID_INVOICE_ID CC_REVERSAL_FLAG REFERENCE_1 REFERENCE_2 ASSETS_TRACKING_FLAG ASSET_BOOK_TYPE_CODE ASSET_CATEGORY_ID REQUESTER_ID DESCRIPTION INVENTORY_ITEM_ID MANUFACTURER SERIAL_NUMBER MODEL_NUMBER ACCOUNT_SEGMENT BALANCING_SEGMENT COST_CENTER_SEGMENT OVERLAY_DIST_CODE_CONCAT DEFAULT_DIST_CCID PO_DISTRIBUTION_ID RECEIPT_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG DELIVER_TO_PERSON CONTAINER_NUM SHIPMENT_TYPE_LOOKUP_CODE SHIPMENT_TYPE ITEM_NUMBER ITEM_CATEGORY_NAME ASSESSABLE_VALUE AMOUNT_FINANCED QUANTITY_FINANCED AMOUNT_RECOUPED QUANTITY_RECOUPED BASE_INVOICE_TYPE TAX_ALREADY_CALCULATED_FLAG AWT_GROUP_ID BUDGET_DATE CONSUMPTION_ADVICE_HEADER_ID CONSUMPTION_ADVICE_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 PRC_BU_ID ORCHESTRATION_ORDER_FLAG SHIP_FROM_LOCATION_ID FINAL_DISCHARGE_LOCATION_ID AGENT_ID SHIP_TO_CUST_LOCATION_ID SHIP_TO_CUSTOMER_NAME DEF_ACCTG_START_DATE DEF_ACCTG_END_DATE DEF_ACCTG_ACCRUAL_CCID CREDIT_FLAG

Name

INVOICE_ID

INVOICE_NUMBER

INVOICE_LINE

INVOICE_LINE_AMOUNT

INVOICE_DATE

ACCOUNTING_DATE

LINE_QUANTITY_INVOICED

LINE_UNIT_PRICE

UNIT_MEAS_LOOKUP_CODE

PO_NUMBER

PO_LINE

PO_SCHEDULE

RECEIPT_NUMBER

RCV_TRANSACTION_DATE

BILL_OF_LADING

SHIP_TO_LOCATION

ITEM_ID

ITEM_CATEGORY

PACKING_SLIP

WAYBILL_AIRBILL_NUM

SHIPPED_DATE

INVOICE_AMOUNT

RCV_TRANSACTION_ID

LINE_LOCATION_ID

ITEM_DESCRIPTION

SUPPLIER_ITEM_NUMBER

PO_HEADER_ID

RCV_SHIPMENT_LINE_NUMBER

NEED_BY_DATE

MATCHING_BASIS_LOOKUP_CODE

MATCHING_BASIS

BUYER

SHIPMENT_ITEM_DESCRIPTION

TERM_ID

PAYMENT_TERMS

FREIGHT_TERMS

QUANTITY_BILLED

AMOUNT_ACCEPTED

CANCEL_FLAG

QUANTITY_RECEIVED

QUANTITY_ACCEPTED

AMOUNT_RECEIVED

QUANTITY_SHIPPED

AMOUNT_SHIPPED

QUANTITY

QUANTITY_CANCELLED

AMOUNT

AMOUNT_BILLED

AMOUNT_CANCELLED

ATTRIBUTE1

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE_CATEGORY

CURRENCY_CODE

VENDOR_ID

MATCH_OPTION_LOOKUP_CODE

MATCH_OPTION

ORG_ID

LEGAL_ENTITY_ID

TYPE_1099

PO_LINE_ID

RCV_SHIPMENT_HEADER_ID

RCV_SHIPMENT_LINE_ID

HOLD_REASON

CLOSED_CODE_LOOKUP_CODE

CLOSED_CODE

CLOSED_REASON

PAY_ON_CODE

PJC_CONTEXT_CATEGORY

PJC_PROJECT_ID

PJC_TASK_ID

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_WORK_TYPE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_RESERVED_ATTRIBUTE1

PJC_RESERVED_ATTRIBUTE2

PJC_RESERVED_ATTRIBUTE3

PJC_RESERVED_ATTRIBUTE4

PJC_RESERVED_ATTRIBUTE5

PJC_RESERVED_ATTRIBUTE6

PJC_RESERVED_ATTRIBUTE7

PJC_RESERVED_ATTRIBUTE8

PJC_RESERVED_ATTRIBUTE9

PJC_RESERVED_ATTRIBUTE10

PJC_USER_DEF_ATTRIBUTE1

PJC_USER_DEF_ATTRIBUTE2

PJC_USER_DEF_ATTRIBUTE3

PJC_USER_DEF_ATTRIBUTE4

PJC_USER_DEF_ATTRIBUTE5

PJC_USER_DEF_ATTRIBUTE6

PJC_USER_DEF_ATTRIBUTE7

PJC_USER_DEF_ATTRIBUTE8

PJC_USER_DEF_ATTRIBUTE9

PJC_USER_DEF_ATTRIBUTE10

SHIP_TO_LOCATION_ID

PRIMARY_INTENDED_USE

PRODUCT_FISC_CLASSIFICATION

TRX_BUSINESS_CATEGORY

PRODUCT_TYPE

PRODUCT_CATEGORY

USER_DEFINED_FISC_CLASS

MERCHANT_DOCUMENT_NUMBER

MERCHANT_NAME

MERCHANT_REFERENCE

MERCHANT_TAX_REG_NUMBER

MERCHANT_TAXPAYER_ID

COUNTRY_OF_SUPPLY

CREDIT_CARD_TRX_ID

COMPANY_PREPAID_INVOICE_ID

CC_REVERSAL_FLAG

REFERENCE_1

REFERENCE_2

ASSETS_TRACKING_FLAG

ASSET_BOOK_TYPE_CODE

ASSET_CATEGORY_ID

REQUESTER_ID

DESCRIPTION

INVENTORY_ITEM_ID

MANUFACTURER

SERIAL_NUMBER

MODEL_NUMBER

ACCOUNT_SEGMENT

BALANCING_SEGMENT

COST_CENTER_SEGMENT

OVERLAY_DIST_CODE_CONCAT

DEFAULT_DIST_CCID

PO_DISTRIBUTION_ID

RECEIPT_REQUIRED_FLAG

INSPECTION_REQUIRED_FLAG

DELIVER_TO_PERSON

CONTAINER_NUM

SHIPMENT_TYPE_LOOKUP_CODE

SHIPMENT_TYPE

ITEM_NUMBER

ITEM_CATEGORY_NAME

ASSESSABLE_VALUE

AMOUNT_FINANCED

QUANTITY_FINANCED

AMOUNT_RECOUPED

QUANTITY_RECOUPED

BASE_INVOICE_TYPE

TAX_ALREADY_CALCULATED_FLAG

AWT_GROUP_ID

BUDGET_DATE

CONSUMPTION_ADVICE_HEADER_ID

CONSUMPTION_ADVICE_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

PRC_BU_ID

ORCHESTRATION_ORDER_FLAG

SHIP_FROM_LOCATION_ID

FINAL_DISCHARGE_LOCATION_ID

AGENT_ID

SHIP_TO_CUST_LOCATION_ID

SHIP_TO_CUSTOMER_NAME

DEF_ACCTG_START_DATE

DEF_ACCTG_END_DATE

DEF_ACCTG_ACCRUAL_CCID

CREDIT_FLAG

Query

SQL_Statement
SELECT ai.invoice_id invoice_id, ai.invoice_num invoice_number, ail.line_number invoice_line, ail.amount invoice_line_amount, ai.invoice_date invoice_date, ail.accounting_date accounting_date, ail.quantity_invoiced line_quantity_invoiced, ail.unit_price line_unit_price, /* bug#22652320 / (SELECT inv.unit_of_measure FROM inv_units_of_measure_vl inv WHERE inv.uom_code = ail.unit_meas_lookup_code ) unit_meas_lookup_code, ph.segment1 po_number, pl.line_num po_line, pll.shipment_num po_schedule, sh.receipt_num receipt_number, rt.transaction_date rcv_transaction_date, sh.bill_of_lading bill_of_lading, (SELECT hl.location_code 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) ) ship_to_location, pl.item_id item_id, ail.purchasing_category_id item_category, sh.packing_slip packing_slip, sh.waybill_airbill_num waybill_airbill_num, sh.shipped_date shipped_date, ai.invoice_amount invoice_amount, ail.rcv_transaction_id rcv_transaction_id, pll.line_location_id line_location_id, NVL(pll.description,pl.item_description) item_description, /Bug#32220237/ pl.vendor_product_num supplier_item_number, ail.po_header_id po_header_id, sl.line_num rcv_shipment_line_number, pll.need_by_date need_by_date, pll.matching_basis matching_basis_lookup_code, alc.displayed_field matching_basis, (select he.full_name from per_workforce_current_x he where ph.agent_id = he.person_id) buyer, pll.description shipment_item_description, (SELECT AT.term_id from ap_terms at where ph.terms_id = at.term_id) term_id, / bug#22652320 / (SELECT AT.name from ap_terms at where ph.terms_id = at.term_id) payment_terms, / bug#22652320 / / bug#22652320 / (SELECT FL.MEANING FROM FND_LOOKUPS FL WHERE fl.lookup_type = 'FREIGHT TERMS' AND fl.lookup_code = ph.freight_terms_lookup_code) freight_terms, pll.quantity_billed quantity_billed, pll.amount_accepted amount_accepted, pll.schedule_status cancel_flag, pll.quantity_received, pll.quantity_accepted, pll.amount_received, pll.quantity_shipped quantity_shipped, pll.amount_shipped amount_shipped, pll.quantity quantity, pll.quantity_cancelled quantity_cancelled, pll.amount amount, DECODE(ail.rcv_transaction_id, NULL, pll.amount_billed, rt.amount_billed) amount_billed, pll.amount_cancelled amount_cancelled, ail.attribute1 attribute1, ail.attribute10 attribute10, ail.attribute11 attribute11, ail.attribute12 attribute12, ail.attribute13 attribute13, ail.attribute14 attribute14, ail.attribute15 attribute15, ail.attribute2 attribute2, ail.attribute3 attribute3, ail.attribute4 attribute4, ail.attribute5 attribute5, ail.attribute6 attribute6, ail.attribute7 attribute7, ail.attribute8 attribute8, ail.attribute9 attribute9, ail.attribute_category attribute_category, ph.currency_code currency_code, ph.vendor_id vendor_id, pll.match_option match_option_lookup_code, / bug#22652320 / (SELECT ALC1.Displayed_field FROM PO_LOOKUP_CODES ALC1 WHERE ALC1.lookup_type = 'PO INVOICE MATCH OPTION' AND ALC1.lookup_code = pll.match_option) match_option, ph.billto_bu_id org_id, ph.soldto_le_id legal_entity_id, pl.type_1099 type_1099, ail.po_line_id po_line_id, sh.shipment_header_id rcv_shipment_header_id, sl.shipment_line_id rcv_shipment_line_id, (SELECT hold_reason FROM ap_holds_all ah WHERE ah.invoice_id = ai.invoice_id AND ah.rcv_transaction_id = rt.transaction_id AND rownum =1 ) hold_reason, NVL(pll.schedule_status,'INCOMPLETE') closed_code_lookup_code, / bug#22652320 / (SELECT plc1.displayed_field FROM PO_lookup_codes plc1 WHERE plc1.lookup_type = 'ORDER_SCHEDULE_STATUS' AND plc1.lookup_code = pll.schedule_status) closed_code, pll.closed_reason closed_reason, ph.pay_on_code PAY_ON_CODE, ail.pjc_context_category pjc_context_category, ail.pjc_project_id pjc_project_id, ail.pjc_task_id pjc_task_id, ail.pjc_expenditure_type_id pjc_expenditure_type_id, ail.pjc_expenditure_item_date pjc_expenditure_item_date, ail.pjc_organization_id pjc_organization_id, ail.pjc_billable_flag pjc_billable_flag, ail.pjc_capitalizable_flag pjc_capitalizable_flag, ail.pjc_work_type_id pjc_work_type_id, ail.pjc_contract_id pjc_contract_id, ail.pjc_contract_line_id pjc_contract_line_id, ail.pjc_funding_allocation_id pjc_funding_allocation_id, ail.pjc_reserved_attribute1 pjc_reserved_attribute1, ail.pjc_reserved_attribute2 pjc_reserved_attribute2, ail.pjc_reserved_attribute3 pjc_reserved_attribute3, ail.pjc_reserved_attribute4 pjc_reserved_attribute4, ail.pjc_reserved_attribute5 pjc_reserved_attribute5, ail.pjc_reserved_attribute6 pjc_reserved_attribute6, ail.pjc_reserved_attribute7 pjc_reserved_attribute7, ail.pjc_reserved_attribute8 pjc_reserved_attribute8, ail.pjc_reserved_attribute9 pjc_reserved_attribute9, ail.pjc_reserved_attribute10 pjc_reserved_attribute10, ail.pjc_user_def_attribute1 pjc_user_def_attribute1, ail.pjc_user_def_attribute2 pjc_user_def_attribute2, ail.pjc_user_def_attribute3 pjc_user_def_attribute3, ail.pjc_user_def_attribute4 pjc_user_def_attribute4, ail.pjc_user_def_attribute5 pjc_user_def_attribute5, ail.pjc_user_def_attribute6 pjc_user_def_attribute6, ail.pjc_user_def_attribute7 pjc_user_def_attribute7, ail.pjc_user_def_attribute8 pjc_user_def_attribute8, ail.pjc_user_def_attribute9 pjc_user_def_attribute9, ail.pjc_user_def_attribute10 pjc_user_def_attribute10, ail.ship_to_location_id ship_to_location_id, ail.primary_intended_use primary_intended_use, ail.product_fisc_classification product_fisc_classification, ail.trx_business_category trx_business_category, ail.product_type product_type, ail.product_category product_category, ail.user_defined_fisc_class user_defined_fisc_class, ail.merchant_document_number merchant_document_number , ail.merchant_name merchant_name, ail.merchant_reference merchant_reference, ail.merchant_tax_reg_number merchant_tax_reg_number, ail.merchant_taxpayer_id merchant_taxpayer_id, ail.country_of_supply country_of_supply, ail.credit_card_trx_id credit_card_trx_id, ail.company_prepaid_invoice_id company_prepaid_invoice_id, ail.cc_reversal_flag cc_reversal_flag, ail.reference_1 reference_1, ail.reference_2 reference_2, ail.assets_tracking_flag assets_tracking_flag, ail.asset_book_type_code asset_book_type_code, ail.asset_category_id asset_category_id, ail.requester_id requester_id, ail.description description, ail.inventory_item_id inventory_item_id, ail.manufacturer manufacturer, ail.serial_number serial_number, ail.model_number model_number, ail.account_segment account_segment, ail.balancing_segment balancing_segment, ail.cost_center_segment cost_center_segment, ail.overlay_dist_code_concat overlay_dist_code_concat, ail.default_dist_ccid default_dist_ccid, ail.po_distribution_id po_distribution_id, pll.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, pll.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG , NULL deliver_to_person, sl.container_num container_num, / bug#22652320 / (SELECT ALC2.LOOKUP_CODE FROM AP_LOOKUP_CODES ALC2 WHERE alc2.lookup_type = 'SHIPMENT TYPE' AND alc2.lookup_code = pll.shipment_type) shipment_type_lookup_code, / bug#22652320 / (SELECT ALC2.Displayed_field FROM AP_LOOKUP_CODES ALC2 WHERE alc2.lookup_type = 'SHIPMENT TYPE' AND alc2.lookup_code = pll.shipment_type) shipment_type, (SELECT esi.item_number FROM egp_system_items esi WHERE esi.inventory_item_id = pl.item_id AND esi.organization_id = ai.org_id ) item_number, (SELECT category_name FROM egp_categories_vl ecs WHERE ecs.category_id = ail.purchasing_category_id ) item_category_name, pll.assessable_value, pll.amount_financed , pll.quantity_financed , pll.amount_recouped, pll.quantity_recouped, ai.invoice_type_lookup_code base_invoice_type, ail.tax_already_calculated_flag, ail.awt_group_id, ail.budget_date, ail.consumption_advice_header_id, ail.consumption_advice_line_id, / bug#22652320 / ICAH.consumption_number consumption_number, ICAL.consumption_line_number consumption_line_number, ICAH.consumption_date consumption_date, ICAL.consumed_quantity consumed_quantity, ICAL.consumed_quantity_uom_code consumed_quantity_uom_code, ICAL.billed_quantity cons_billed_quantity, ICAL.billed_amount cons_billed_amount, ICAL.currency_code cons_currency_code, ICAL.currency_conversion_rate cons_currency_conversion_rate, ICAL.consumption_unit_price consumption_unit_price, ph.prc_bu_id, ph.ORCHESTRATION_ORDER_FLAG, ail.ship_from_location_id, ail.final_discharge_location_id, ph.agent_id, ail.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, ail.def_acctg_start_date def_acctg_start_date, ail.def_acctg_end_date def_acctg_end_date, ail.def_acctg_accrual_ccid def_acctg_accrual_ccid, pl.credit_flag credit_flag FROM ap_invoices_all ai, ap_invoice_lines_all ail, po_headers_all ph, po_lines_all pl, po_line_locations_all pll, rcv_transactions rt, rcv_shipment_headers sh, rcv_shipment_lines sl, / bug#22652320 ap_terms AT, / ap_lookup_codes alc, / bug#22652320 per_workforce_current_x he, inv_cons_advice_txns_v IMT / INV_CONS_ADVICE_HEADERS ICAH, INV_CONS_ADVICE_LINES ICAL WHERE ai.invoice_id = ail.invoice_id AND ail.line_type_lookup_code = 'ITEM' AND ail.po_line_location_id = pll.line_location_id AND pll.po_header_id = ph.po_header_id AND pll.po_line_id = pl.po_line_id AND ail.rcv_transaction_id = rt.transaction_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 ph.agent_id = he.person_id / AND alc.lookup_type = 'MATCHING BASIS' AND alc.lookup_code = pll.matching_basis AND ail.match_type NOT IN('QTY_CORRECTION','PRICE_CORRECTION','AMOUNT_CORRECTION') AND PLL.schedule_status <> 'FINALLY CLOSED' / Bug 31999240 - Corrections not allowed for Complex PO matched invoices / AND ail.retained_amount IS NULL AND ail.consumption_advice_header_id = ICAH.consumption_header_id (+) AND ail.consumption_advice_line_id = ICAL.consumption_line_id(+) AND ail.rcv_transaction_id = ICAL.RECEIVE_TRANSACTION_ID (+) / bug#22652320 / / bug#22652320 Bug#31111944:Commented below subquery inaddtion to Bug#28435877*/ AND (NVL(pl.CREDIT_FLAG,'N') <> 'Y')

SQL_Statement

SELECT ai.invoice_id invoice_id,

ai.invoice_num invoice_number,

ail.line_number invoice_line,

ail.amount invoice_line_amount,

ai.invoice_date invoice_date,

ail.accounting_date accounting_date,

ail.quantity_invoiced line_quantity_invoiced,

ail.unit_price line_unit_price,

/* bug#22652320 */

(SELECT inv.unit_of_measure

FROM inv_units_of_measure_vl inv

WHERE inv.uom_code = ail.unit_meas_lookup_code

) unit_meas_lookup_code,

ph.segment1 po_number,

pl.line_num po_line,

pll.shipment_num po_schedule,

sh.receipt_num receipt_number,

rt.transaction_date rcv_transaction_date,

sh.bill_of_lading bill_of_lading,

(SELECT hl.location_code

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)

) ship_to_location,

pl.item_id item_id,

ail.purchasing_category_id item_category,

sh.packing_slip packing_slip,

sh.waybill_airbill_num waybill_airbill_num,

sh.shipped_date shipped_date,

ai.invoice_amount invoice_amount,

ail.rcv_transaction_id rcv_transaction_id,

pll.line_location_id line_location_id,

NVL(pll.description,pl.item_description) item_description, /*Bug#32220237*/

pl.vendor_product_num supplier_item_number,

ail.po_header_id po_header_id,

sl.line_num rcv_shipment_line_number,

pll.need_by_date need_by_date,

pll.matching_basis matching_basis_lookup_code,

alc.displayed_field matching_basis,

(select he.full_name from per_workforce_current_x he where ph.agent_id = he.person_id) buyer,

pll.description shipment_item_description,

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

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

/* bug#22652320 */

(SELECT FL.MEANING

FROM FND_LOOKUPS FL

WHERE fl.lookup_type = 'FREIGHT TERMS'

AND fl.lookup_code = ph.freight_terms_lookup_code) freight_terms,

pll.quantity_billed quantity_billed,

pll.amount_accepted amount_accepted,

pll.schedule_status cancel_flag,

pll.quantity_received,

pll.quantity_accepted,

pll.amount_received,

pll.quantity_shipped quantity_shipped,

pll.amount_shipped amount_shipped,

pll.quantity quantity,

pll.quantity_cancelled quantity_cancelled,

pll.amount amount,

DECODE(ail.rcv_transaction_id, NULL, pll.amount_billed, rt.amount_billed) amount_billed,

pll.amount_cancelled amount_cancelled,

ail.attribute1 attribute1,

ail.attribute10 attribute10,

ail.attribute11 attribute11,

ail.attribute12 attribute12,

ail.attribute13 attribute13,

ail.attribute14 attribute14,

ail.attribute15 attribute15,

ail.attribute2 attribute2,

ail.attribute3 attribute3,

ail.attribute4 attribute4,

ail.attribute5 attribute5,

ail.attribute6 attribute6,

ail.attribute7 attribute7,

ail.attribute8 attribute8,

ail.attribute9 attribute9,

ail.attribute_category attribute_category,

ph.currency_code currency_code,

ph.vendor_id vendor_id,

pll.match_option match_option_lookup_code,

/* bug#22652320 */

(SELECT ALC1.Displayed_field

FROM PO_LOOKUP_CODES ALC1

WHERE ALC1.lookup_type = 'PO INVOICE MATCH OPTION'

AND ALC1.lookup_code = pll.match_option) match_option,

ph.billto_bu_id org_id,

ph.soldto_le_id legal_entity_id,

pl.type_1099 type_1099,

ail.po_line_id po_line_id,

sh.shipment_header_id rcv_shipment_header_id,

sl.shipment_line_id rcv_shipment_line_id,

(SELECT hold_reason

FROM ap_holds_all ah

WHERE ah.invoice_id = ai.invoice_id

AND ah.rcv_transaction_id = rt.transaction_id

AND rownum =1

) hold_reason,

NVL(pll.schedule_status,'INCOMPLETE') closed_code_lookup_code,

/* bug#22652320 */

(SELECT plc1.displayed_field

FROM PO_lookup_codes plc1

WHERE plc1.lookup_type = 'ORDER_SCHEDULE_STATUS'

AND plc1.lookup_code = pll.schedule_status) closed_code,

pll.closed_reason closed_reason,

ph.pay_on_code PAY_ON_CODE,

ail.pjc_context_category pjc_context_category,

ail.pjc_project_id pjc_project_id,

ail.pjc_task_id pjc_task_id,

ail.pjc_expenditure_type_id pjc_expenditure_type_id,

ail.pjc_expenditure_item_date pjc_expenditure_item_date,

ail.pjc_organization_id pjc_organization_id,

ail.pjc_billable_flag pjc_billable_flag,

ail.pjc_capitalizable_flag pjc_capitalizable_flag,

ail.pjc_work_type_id pjc_work_type_id,

ail.pjc_contract_id pjc_contract_id,

ail.pjc_contract_line_id pjc_contract_line_id,

ail.pjc_funding_allocation_id pjc_funding_allocation_id,

ail.pjc_reserved_attribute1 pjc_reserved_attribute1,

ail.pjc_reserved_attribute2 pjc_reserved_attribute2,

ail.pjc_reserved_attribute3 pjc_reserved_attribute3,

ail.pjc_reserved_attribute4 pjc_reserved_attribute4,

ail.pjc_reserved_attribute5 pjc_reserved_attribute5,

ail.pjc_reserved_attribute6 pjc_reserved_attribute6,

ail.pjc_reserved_attribute7 pjc_reserved_attribute7,

ail.pjc_reserved_attribute8 pjc_reserved_attribute8,

ail.pjc_reserved_attribute9 pjc_reserved_attribute9,

ail.pjc_reserved_attribute10 pjc_reserved_attribute10,

ail.pjc_user_def_attribute1 pjc_user_def_attribute1,

ail.pjc_user_def_attribute2 pjc_user_def_attribute2,

ail.pjc_user_def_attribute3 pjc_user_def_attribute3,

ail.pjc_user_def_attribute4 pjc_user_def_attribute4,

ail.pjc_user_def_attribute5 pjc_user_def_attribute5,

ail.pjc_user_def_attribute6 pjc_user_def_attribute6,

ail.pjc_user_def_attribute7 pjc_user_def_attribute7,

ail.pjc_user_def_attribute8 pjc_user_def_attribute8,

ail.pjc_user_def_attribute9 pjc_user_def_attribute9,

ail.pjc_user_def_attribute10 pjc_user_def_attribute10,

ail.ship_to_location_id ship_to_location_id,

ail.primary_intended_use primary_intended_use,

ail.product_fisc_classification product_fisc_classification,

ail.trx_business_category trx_business_category,

ail.product_type product_type,

ail.product_category product_category,

ail.user_defined_fisc_class user_defined_fisc_class,

ail.merchant_document_number merchant_document_number ,

ail.merchant_name merchant_name,

ail.merchant_reference merchant_reference,

ail.merchant_tax_reg_number merchant_tax_reg_number,

ail.merchant_taxpayer_id merchant_taxpayer_id,

ail.country_of_supply country_of_supply,

ail.credit_card_trx_id credit_card_trx_id,

ail.company_prepaid_invoice_id company_prepaid_invoice_id,

ail.cc_reversal_flag cc_reversal_flag,

ail.reference_1 reference_1,

ail.reference_2 reference_2,

ail.assets_tracking_flag assets_tracking_flag,

ail.asset_book_type_code asset_book_type_code,

ail.asset_category_id asset_category_id,

ail.requester_id requester_id,

ail.description description,

ail.inventory_item_id inventory_item_id,

ail.manufacturer manufacturer,

ail.serial_number serial_number,

ail.model_number model_number,

ail.account_segment account_segment,

ail.balancing_segment balancing_segment,

ail.cost_center_segment cost_center_segment,

ail.overlay_dist_code_concat overlay_dist_code_concat,

ail.default_dist_ccid default_dist_ccid,

ail.po_distribution_id po_distribution_id,

pll.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,

pll.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG ,

NULL deliver_to_person,

sl.container_num container_num,

/* bug#22652320 */

(SELECT ALC2.LOOKUP_CODE

FROM AP_LOOKUP_CODES ALC2

WHERE alc2.lookup_type = 'SHIPMENT TYPE'

AND alc2.lookup_code = pll.shipment_type) shipment_type_lookup_code,

/* bug#22652320 */

(SELECT ALC2.Displayed_field

FROM AP_LOOKUP_CODES ALC2

WHERE alc2.lookup_type = 'SHIPMENT TYPE'

AND alc2.lookup_code = pll.shipment_type) shipment_type,

(SELECT esi.item_number

FROM egp_system_items esi

WHERE esi.inventory_item_id = pl.item_id

AND esi.organization_id = ai.org_id

) item_number,

(SELECT category_name

FROM egp_categories_vl ecs

WHERE ecs.category_id = ail.purchasing_category_id

) item_category_name,

pll.assessable_value,

pll.amount_financed ,

pll.quantity_financed ,

pll.amount_recouped,

pll.quantity_recouped,

ai.invoice_type_lookup_code base_invoice_type,

ail.tax_already_calculated_flag,

ail.awt_group_id,

ail.budget_date,

ail.consumption_advice_header_id,

ail.consumption_advice_line_id,

/* bug#22652320 */

ICAH.consumption_number consumption_number,

ICAL.consumption_line_number consumption_line_number,

ICAH.consumption_date consumption_date,

ICAL.consumed_quantity consumed_quantity,

ICAL.consumed_quantity_uom_code consumed_quantity_uom_code,

ICAL.billed_quantity cons_billed_quantity,

ICAL.billed_amount cons_billed_amount,

ICAL.currency_code cons_currency_code,

ICAL.currency_conversion_rate cons_currency_conversion_rate,

ICAL.consumption_unit_price consumption_unit_price,

ph.prc_bu_id,

ph.ORCHESTRATION_ORDER_FLAG,

ail.ship_from_location_id,

ail.final_discharge_location_id,

ph.agent_id,

ail.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,

ail.def_acctg_start_date def_acctg_start_date,

ail.def_acctg_end_date def_acctg_end_date,

ail.def_acctg_accrual_ccid def_acctg_accrual_ccid,

pl.credit_flag credit_flag

FROM ap_invoices_all ai,

ap_invoice_lines_all ail,

po_headers_all ph,

po_lines_all pl,

po_line_locations_all pll,

rcv_transactions rt,

rcv_shipment_headers sh,

rcv_shipment_lines sl,

/* bug#22652320

ap_terms AT, */

ap_lookup_codes alc,

/* bug#22652320

per_workforce_current_x he,

inv_cons_advice_txns_v IMT */

INV_CONS_ADVICE_HEADERS ICAH,

INV_CONS_ADVICE_LINES ICAL

WHERE ai.invoice_id = ail.invoice_id

AND ail.line_type_lookup_code = 'ITEM'

AND ail.po_line_location_id = pll.line_location_id

AND pll.po_header_id = ph.po_header_id

AND pll.po_line_id = pl.po_line_id

AND ail.rcv_transaction_id = rt.transaction_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 ph.agent_id = he.person_id */

AND alc.lookup_type = 'MATCHING BASIS'

AND alc.lookup_code = pll.matching_basis

AND ail.match_type NOT IN('QTY_CORRECTION','PRICE_CORRECTION','AMOUNT_CORRECTION')

AND PLL.schedule_status <> 'FINALLY CLOSED'

/* Bug 31999240 - Corrections not allowed for Complex PO matched invoices */

AND ail.retained_amount IS NULL

AND ail.consumption_advice_header_id = ICAH.consumption_header_id (+)

AND ail.consumption_advice_line_id = ICAL.consumption_line_id(+)

AND ail.rcv_transaction_id = ICAL.RECEIVE_TRANSACTION_ID (+) /* bug#22652320 */

/* bug#22652320 Bug#31111944:Commented below subquery inaddtion to Bug#28435877*/

AND (NVL(pl.CREDIT_FLAG,'N') <> 'Y')