CMR_AUTO_ACCRUAL_CLR

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
CMR_PO_DISTRIBUTION_ID PO_DISTRIBUTION_NUMBER BUSINESS_UNIT_ID POSTATUS POMATCHOPTION INVOICEFINALMATCH INVENTORY_ITEM_ID ITEM_NUMBER INVENTORY_ORG_ID CATEGORY_ID CATEGORY_NAME SUPPLIERID VENDOR_NAME SUPPLIERSITEID VENDOR_SITE_CODE INVOICEAGE RECEIPTAGE OVERINVOICEDQTY UNDERINVOICEDQTY PER_OVER_INVOICED PER_UNDER_INVOICED PERPOACCRUALAMT ACCRUALCLRAMT CMRACCRUALAMT APACCRUALAMT RECEIVEDACCRUALAMT RECEIVEDNONRECTAXAMT RECEIVEDRECTAXAMT INVOICEACCRUALAMT INVOICENONRECTAXAMT INVOICERECTAXAMT PO_NUMBER LINE_NUMBER SHIPMENT_NUMBER PO_QUANTITY PO_AMOUNT PURCHASE_BASIS ORGANIZATION_CODE CMR_PO_LINE_LOCATION_ID ACCRUALAMTDIFF NONRECTAXAMTDIFF PROCUREMENT_BU_ID SHIP_TO_LOCATION_ID RECEIVEDQTY INVOICEQTY BU_NAME LOCATION_CODE PROFIT_CENTER_BU_ID PROFIT_CENTER_BU PO_DESTINATION_TYPE ITEM_DESCRIPTION

Name

CMR_PO_DISTRIBUTION_ID

PO_DISTRIBUTION_NUMBER

BUSINESS_UNIT_ID

POSTATUS

POMATCHOPTION

INVOICEFINALMATCH

INVENTORY_ITEM_ID

ITEM_NUMBER

INVENTORY_ORG_ID

CATEGORY_ID

CATEGORY_NAME

SUPPLIERID

VENDOR_NAME

SUPPLIERSITEID

VENDOR_SITE_CODE

INVOICEAGE

RECEIPTAGE

OVERINVOICEDQTY

UNDERINVOICEDQTY

PER_OVER_INVOICED

PER_UNDER_INVOICED

PERPOACCRUALAMT

ACCRUALCLRAMT

CMRACCRUALAMT

APACCRUALAMT

RECEIVEDACCRUALAMT

RECEIVEDNONRECTAXAMT

RECEIVEDRECTAXAMT

INVOICEACCRUALAMT

INVOICENONRECTAXAMT

INVOICERECTAXAMT

PO_NUMBER

LINE_NUMBER

SHIPMENT_NUMBER

PO_QUANTITY

PO_AMOUNT

PURCHASE_BASIS

ORGANIZATION_CODE

CMR_PO_LINE_LOCATION_ID

ACCRUALAMTDIFF

NONRECTAXAMTDIFF

PROCUREMENT_BU_ID

SHIP_TO_LOCATION_ID

RECEIVEDQTY

INVOICEQTY

BU_NAME

LOCATION_CODE

PROFIT_CENTER_BU_ID

PROFIT_CENTER_BU

PO_DESTINATION_TYPE

ITEM_DESCRIPTION

Query

SQL_Statement
with cmr_bus as (select organization_id bu_id, name as bu_name from hr_organization_units_f_tl where language = userenv('LANG') and trunc(sysdate) between effective_start_date and effective_end_date ) SELECT /+ FIRST_ROWS(10) / cred.cmr_po_distribution_id, cred.po_distribution_number, cred.business_unit_id, cred.postatus, cred.pomatchoption, cred.invoicefinalmatch, cred.inventory_item_id, egp.item_number, cred.inventory_org_id, cred.category_id, egpc.category_name, cred.supplierid, supplier.vendor_name, cred.suppliersiteid, suppliersite.vendor_site_code, cred.invoiceage, cred.receiptage, cred.overinvoicedqty, cred.underinvoicedqty, cred.per_over_invoiced, cred.per_under_invoiced, cred.perpoaccrualamt, cred.accrualclramt, cred.cmraccrualamt, cred.apaccrualamt, cred.receivedaccrualamt, cred.receivednonrectaxamt, cred.receivedrectaxamt, cred.invoiceaccrualamt, cred.invoicenonrectaxamt, cred.invoicerectaxamt, cred.po_number, cred.line_number, cred.shipment_number, cred.po_quantity, cred.po_amount, cred.purchase_basis, inv.organization_code, cred.cmr_po_line_location_id, cred.accrualamtdiff, cred.nonrectaxamtdiff, cred.procurement_bu_id, cred.ship_to_location_id, cred.receivedqty, cred.invoiceqty, bu.bu_name, (select loc.location_code from hr_locations_all_f_vl loc where loc.location_id = cred.ship_to_location_id and trunc(sysdate) between loc.effective_start_date and loc.effective_end_date) as location_code, cred.PROFIT_CENTER_BU_ID, pcbu.bu_name as profit_center_bu, cred.po_destination_type, cred.item_description FROM (SELECT cre.cmr_po_distribution_id, cre.po_distribution_number, cre.business_unit_id, cre.postatus, cre.pomatchoption, nvl(caid.invoicefinalmatch, 'No') invoicefinalmatch, cre.inventory_item_id, cre.inventory_org_id, cre.category_id, cre.supplierid, cre.suppliersiteid, nvl(caid.invoiceage, 0) invoiceage, nvl(cre.receiptage, 0) receiptage, decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), -1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), ABS(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 0, 0, 1, 0) overinvoicedqty, decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), ABS(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 0, 0, -1, 0) underinvoicedqty, decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), -1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)) / decode(cre.netrecptqty, 0, 1, NULL, 1, cre.netrecptqty) 100, ABS(nvl(cre.netrecptamt,0) -nvl(caid.apaccrualamt,0)) / decode(cre.netrecptamt, 0, 1, NULL, 1, cre.netrecptamt) 100), 0, 0, 1, 0) per_over_invoiced, decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)) / decode(cre.netrecptqty, 0, 1, NULL, 1, cre.netrecptqty) 100, ABS(nvl(cre.netrecptamt,0) -nvl(caid.apaccrualamt,0)) / decode(cre.netrecptamt, 0, 1, NULL, 1, cre.netrecptamt) 100), 0, 0, -1, 0) per_under_invoiced, (cre.cmr_accrual_amt + nvl(caid.apaccrualamt, 0)) / decode(cre.poamount, NULL, 1, 0, 1, cre.poamount) 100 perpoaccrualamt, (cre.cmr_accrual_amt + nvl(caid.apaccrualamt, 0)) accrualclramt, cre.cmr_accrual_amt cmraccrualamt, cre.received_accrual_amt receivedaccrualamt, cre.received_nonrectax_amt receivednonrectaxamt, cre.received_rectax_amt receivedrectaxamt, nvl(caid.apaccrualamt, 0) apaccrualamt, nvl(caid.invoice_accrual_amt,0) invoiceaccrualamt, nvl(caid.invoice_nonrectax_amt,0) invoicenonrectaxamt, nvl(caid.invoice_rectax_amt,0) invoicerectaxamt, cre.received_accrual_amt + nvl(caid.invoice_accrual_amt,0) accrualamtdiff, cre.received_nonrectax_amt + nvl(caid.invoice_nonrectax_amt,0) nonrectaxamtdiff, cre.cmr_po_line_location_id, cre.po_number, cre.line_number, cre.shipment_number, cre.po_quantity, cre.po_amount, cre.purchase_basis, cre.procurement_bu_id, cre.ship_to_location_id, cre.netrecptqty receivedqty, nvl(caid.invoiceqty,0) invoiceqty, cre.PROFIT_CENTER_BU_ID, cre.po_destination_type, cre.item_description FROM (SELECT cpod.BILL_TO_BUSINESS_UNIT_ID business_unit_id, cpod.cmr_po_distribution_id cmr_po_distribution_id, cpod.distribution_number po_distribution_number, SUM(round(nvl(crd.ledger_amount, 0),fncur.precision)) cmr_accrual_amt, SUM(DECODE(crec.event_cost_source, 'TRANSACTION_TAX', 0, round(nvl(crd.ledger_amount, 0),fncur.precision))) received_accrual_amt, SUM(DECODE(crec.event_cost_source_type, 'NONREC_TAX', round(nvl(crd.ledger_amount, 0),fncur.precision), 0)) received_nonrectax_amt, SUM(DECODE(crec.event_cost_source_type, 'REC_TAX', round(nvl(crd.ledger_amount, 0),fncur.precision), 0)) received_rectax_amt, SUM(decode(cpod.purchase_basis, 'GOODS', DECODE(cre1.event_class_code, 'RECEIVING', DECODE(crec.event_cost_source, 'PO_PRICE', NVL(cre1.source_doc_qty, 0),0),'TRADE_ACCRUALS', DECODE(crec.event_cost_source, 'PO_PRICE', NVL(cre1.source_doc_qty, 0),0), 0),0)) netrecptqty, SUM(decode(cpod.purchase_basis, 'SERVICES', decode(cre1.event_class_code, 'RECEIVING', DECODE(crec.event_cost_source, 'PO_PRICE', cre1.transaction_amt), 'TRADE_ACCRUALS', DECODE(crec.event_cost_source, 'PO_PRICE', cre1.transaction_amt), 0), 0)) netrecptamt, TRUNC(sysdate -MAX(decode(cre1.event_class_code, 'RECEIVING', cre1.transaction_date, to_date('01/01/1900', 'dd/mm/YYYY'))), 0) receiptage, decode(cpod.purchase_basis, 'GOODS', MAX(nvl(cpod.price, 0)) MAX((cpod.quantity_ordered -nvl(cpod.quantity_cancelled, 0))), MAX((cpod.amount_ordered -nvl(cpod.amount_cancelled, 0)))) poamount, decode(cpod.event_type, 'PO Close', 'Closed', 'Open') postatus, decode(cpod.match_option, 'R', 'Receipt', 'P', 'PO', 'C', 'Consigned') pomatchoption, cpod.purchase_basis, cpod.inventory_item_id, cre1.inventory_org_id, cpod.category_id, cpod.vendor_id supplierid, cpod.vendor_site_id suppliersiteid, cpod.cmr_po_line_location_id, cpod.po_number, cpod.line_number, cpod.shipment_number, MAX((nvl(cpod.quantity_ordered,0) -nvl(cpod.quantity_cancelled, 0))) po_quantity, MAX((nvl(cpod.amount_ordered,0) -nvl(cpod.amount_cancelled, 0))) po_amount, cpod.procurement_business_unit_id procurement_bu_id, cpod.ship_to_location_id, cre1.BUSINESS_UNIT_ID PROFIT_CENTER_BU_ID, cpod.destination_type_code po_destination_type, cpod.item_description item_description FROM cmr_rcv_events cre1, cmr_rcv_distributions crd, cmr_rcv_event_costs crec, cmr_purchase_order_dtls cpod, fnd_currencies fncur WHERE cre1.accounting_event_id = crec.accounting_event_id AND crec.event_cost_id = crd.event_cost_id AND crd.accounting_event_id = cre1.accounting_event_id AND crd.accounting_line_type IN('ACCRUAL', 'EXPENSE_ACCRUAL') AND cpod.active_flag = 'Y' AND cpod.accrue_on_receipt_flag = 'Y' AND cpod.cmr_po_distribution_id = cre1.cmr_po_distribution_id and crd.ledger_currency_code=fncur.currency_code GROUP BY cpod.cmr_po_distribution_id, cpod.distribution_number, cre1.business_unit_id, cpod.purchase_basis, cpod.event_type, cpod.match_option, cpod.inventory_item_id, cre1.inventory_org_id, cpod.category_id, cpod.vendor_id, cpod.vendor_site_id, cpod.cmr_po_line_location_id, cpod.po_number, cpod.line_number, cpod.shipment_number, cpod.procurement_business_unit_id, cpod.ship_to_location_id, cpod.BILL_TO_BUSINESS_UNIT_ID, cpod.destination_type_code, cpod.item_description) cre, (SELECT cpod.cmr_po_distribution_id cmr_po_distribution_id, cpod.cmr_po_line_location_id, decode(SUM(decode(final_match_flag, 'Y', 1, 0, NULL, 0)), 0, 'No', 'Yes') invoicefinalmatch, TRUNC(sysdate -MAX(TRUNC(accounting_date)), 0) invoiceage, SUM(decode(invoice_distribution_type, 'ACCRUAL', nvl(invoice_qty_in_po_uom, 0), 0)) invoiceqty, SUM(nvl(invoice_base_amount,0)) apaccrualamt, SUM(DECODE(invoice_distribution_type, 'ACCRUAL', NVL(invoice_base_amount, 0), 0)) invoice_accrual_amt, SUM(DECODE(invoice_distribution_type, 'NONREC_TAX', NVL(invoice_base_amount, 0), 0)) invoice_nonrectax_amt, SUM(DECODE(invoice_distribution_type, 'REC_TAX', NVL(invoice_base_amount, 0), 0)) invoice_rectax_amt FROM cmr_ap_invoice_dtls cap, CMR_PURCHASE_ORDER_DTLS cpod WHERE (invoice_distribution_type IN('ACCRUAL', 'NONREC_TAX') or (invoice_distribution_type = 'REC_TAX' and tax_point_basis = 'DELIVERY')) AND NVL(self_assessed_flag,'N') <> 'Y' and cap.cmr_po_distribution_id = cpod.cmr_po_distribution_id and cpod.active_flag = 'Y' and cpod.accrue_on_receipt_flag = 'Y' GROUP BY cpod.cmr_po_distribution_id, cpod.cmr_po_line_location_id) caid WHERE caid.cmr_po_distribution_id(+) = cre.cmr_po_distribution_id and caid.cmr_po_line_location_id(+) = cre.cmr_po_line_location_id ) cred, egp_system_items_vl egp, inv_org_parameters iop, egp_categories_vl egpc, poz_suppliers_v supplier, poz_supplier_sites_all_m suppliersite, inv_org_parameters inv, cmr_bus bu, cmr_bus pcbu WHERE cred.inventory_item_id = egp.inventory_item_id(+) AND cred.inventory_org_id = iop.organization_id AND nvl(iop.master_organization_id,cred.inventory_org_id) = egp.organization_id(+) AND cred.inventory_org_id = inv.organization_id AND cred.category_id = egpc.category_id AND cred.supplierid = supplier.vendor_id(+) AND cred.suppliersiteid = suppliersite.vendor_site_id(+) AND bu.bu_id = cred.procurement_bu_id AND cred.accrualclramt <> 0 and pcbu.bu_id = cred.PROFIT_CENTER_BU_ID

SQL_Statement

with cmr_bus as (select organization_id bu_id, name as bu_name from

hr_organization_units_f_tl where language = userenv('LANG') and trunc(sysdate)

between effective_start_date and effective_end_date )

SELECT

/*+ FIRST_ROWS(10) */ cred.cmr_po_distribution_id,

cred.po_distribution_number,

cred.business_unit_id,

cred.postatus,

cred.pomatchoption,

cred.invoicefinalmatch,

cred.inventory_item_id,

egp.item_number,

cred.inventory_org_id,

cred.category_id,

egpc.category_name,

cred.supplierid,

supplier.vendor_name,

cred.suppliersiteid,

suppliersite.vendor_site_code,

cred.invoiceage,

cred.receiptage,

cred.overinvoicedqty,

cred.underinvoicedqty,

cred.per_over_invoiced,

cred.per_under_invoiced,

cred.perpoaccrualamt,

cred.accrualclramt,

cred.cmraccrualamt,

cred.apaccrualamt,

cred.receivedaccrualamt,

cred.receivednonrectaxamt,

cred.receivedrectaxamt,

cred.invoiceaccrualamt,

cred.invoicenonrectaxamt,

cred.invoicerectaxamt,

cred.po_number,

cred.line_number,

cred.shipment_number,

cred.po_quantity,

cred.po_amount,

cred.purchase_basis,

inv.organization_code,

cred.cmr_po_line_location_id,

cred.accrualamtdiff,

cred.nonrectaxamtdiff,

cred.procurement_bu_id,

cred.ship_to_location_id,

cred.receivedqty,

cred.invoiceqty,

bu.bu_name,

(select loc.location_code from hr_locations_all_f_vl loc where loc.location_id = cred.ship_to_location_id and trunc(sysdate) between loc.effective_start_date and loc.effective_end_date) as location_code,

cred.PROFIT_CENTER_BU_ID,

pcbu.bu_name as profit_center_bu,

cred.po_destination_type,

cred.item_description

FROM

(SELECT cre.cmr_po_distribution_id,

cre.po_distribution_number,

cre.business_unit_id,

cre.postatus,

cre.pomatchoption,

nvl(caid.invoicefinalmatch, 'No') invoicefinalmatch,

cre.inventory_item_id,

cre.inventory_org_id,

cre.category_id,

cre.supplierid,

cre.suppliersiteid,

nvl(caid.invoiceage, 0) invoiceage,

nvl(cre.receiptage, 0) receiptage,

decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), -1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), ABS(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 0, 0, 1, 0) overinvoicedqty,

decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), ABS(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 0, 0, -1, 0) underinvoicedqty,

decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), -1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)) / decode(cre.netrecptqty, 0, 1, NULL, 1, cre.netrecptqty) *100, ABS(nvl(cre.netrecptamt,0) -nvl(caid.apaccrualamt,0)) / decode(cre.netrecptamt, 0, 1, NULL, 1, cre.netrecptamt) *100), 0, 0, 1, 0) per_over_invoiced,

decode(decode(cre.purchase_basis, 'GOODS', SIGN(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)), SIGN(nvl(cre.netrecptamt, 0) -nvl(caid.apaccrualamt, 0))), 1, decode(cre.purchase_basis, 'GOODS', ABS(nvl(cre.netrecptqty, 0) -nvl(caid.invoiceqty, 0)) / decode(cre.netrecptqty, 0, 1, NULL, 1, cre.netrecptqty) *100, ABS(nvl(cre.netrecptamt,0) -nvl(caid.apaccrualamt,0)) / decode(cre.netrecptamt, 0, 1, NULL, 1, cre.netrecptamt) *100), 0, 0, -1, 0) per_under_invoiced,

(cre.cmr_accrual_amt + nvl(caid.apaccrualamt, 0)) / decode(cre.poamount, NULL, 1, 0, 1, cre.poamount) *100 perpoaccrualamt,

(cre.cmr_accrual_amt + nvl(caid.apaccrualamt, 0)) accrualclramt,

cre.cmr_accrual_amt cmraccrualamt,

cre.received_accrual_amt receivedaccrualamt,

cre.received_nonrectax_amt receivednonrectaxamt,

cre.received_rectax_amt receivedrectaxamt,

nvl(caid.apaccrualamt, 0) apaccrualamt,

nvl(caid.invoice_accrual_amt,0) invoiceaccrualamt,

nvl(caid.invoice_nonrectax_amt,0) invoicenonrectaxamt,

nvl(caid.invoice_rectax_amt,0) invoicerectaxamt,

cre.received_accrual_amt + nvl(caid.invoice_accrual_amt,0) accrualamtdiff,

cre.received_nonrectax_amt + nvl(caid.invoice_nonrectax_amt,0) nonrectaxamtdiff,

cre.cmr_po_line_location_id,

cre.po_number,

cre.line_number,

cre.shipment_number,

cre.po_quantity,

cre.po_amount,

cre.purchase_basis,

cre.procurement_bu_id,

cre.ship_to_location_id,

cre.netrecptqty receivedqty,

nvl(caid.invoiceqty,0) invoiceqty,

cre.PROFIT_CENTER_BU_ID,

cre.po_destination_type,

cre.item_description

FROM

(SELECT cpod.BILL_TO_BUSINESS_UNIT_ID business_unit_id,

cpod.cmr_po_distribution_id cmr_po_distribution_id,

cpod.distribution_number po_distribution_number,

SUM(round(nvl(crd.ledger_amount, 0),fncur.precision)) cmr_accrual_amt,

SUM(DECODE(crec.event_cost_source, 'TRANSACTION_TAX', 0, round(nvl(crd.ledger_amount, 0),fncur.precision))) received_accrual_amt,

SUM(DECODE(crec.event_cost_source_type, 'NONREC_TAX', round(nvl(crd.ledger_amount, 0),fncur.precision), 0)) received_nonrectax_amt,

SUM(DECODE(crec.event_cost_source_type, 'REC_TAX', round(nvl(crd.ledger_amount, 0),fncur.precision), 0)) received_rectax_amt,

SUM(decode(cpod.purchase_basis, 'GOODS', DECODE(cre1.event_class_code, 'RECEIVING', DECODE(crec.event_cost_source, 'PO_PRICE', NVL(cre1.source_doc_qty, 0),0),'TRADE_ACCRUALS', DECODE(crec.event_cost_source, 'PO_PRICE', NVL(cre1.source_doc_qty, 0),0), 0),0)) netrecptqty,

SUM(decode(cpod.purchase_basis, 'SERVICES', decode(cre1.event_class_code, 'RECEIVING', DECODE(crec.event_cost_source, 'PO_PRICE', cre1.transaction_amt), 'TRADE_ACCRUALS', DECODE(crec.event_cost_source, 'PO_PRICE', cre1.transaction_amt), 0), 0)) netrecptamt,

TRUNC(sysdate -MAX(decode(cre1.event_class_code, 'RECEIVING', cre1.transaction_date, to_date('01/01/1900', 'dd/mm/YYYY'))), 0) receiptage,

decode(cpod.purchase_basis, 'GOODS', MAX(nvl(cpod.price, 0)) *MAX((cpod.quantity_ordered -nvl(cpod.quantity_cancelled, 0))), MAX((cpod.amount_ordered -nvl(cpod.amount_cancelled, 0)))) poamount,

decode(cpod.event_type, 'PO Close', 'Closed', 'Open') postatus,

decode(cpod.match_option, 'R', 'Receipt', 'P', 'PO', 'C', 'Consigned') pomatchoption,

cpod.purchase_basis,

cpod.inventory_item_id,

cre1.inventory_org_id,

cpod.category_id,

cpod.vendor_id supplierid,

cpod.vendor_site_id suppliersiteid,

cpod.cmr_po_line_location_id,

cpod.po_number,

cpod.line_number,

cpod.shipment_number,

MAX((nvl(cpod.quantity_ordered,0) -nvl(cpod.quantity_cancelled, 0))) po_quantity,

MAX((nvl(cpod.amount_ordered,0) -nvl(cpod.amount_cancelled, 0))) po_amount,

cpod.procurement_business_unit_id procurement_bu_id,

cpod.ship_to_location_id,

cre1.BUSINESS_UNIT_ID PROFIT_CENTER_BU_ID,

cpod.destination_type_code po_destination_type,

cpod.item_description item_description

FROM cmr_rcv_events cre1,

cmr_rcv_distributions crd,

cmr_rcv_event_costs crec,

cmr_purchase_order_dtls cpod,

fnd_currencies fncur

WHERE cre1.accounting_event_id = crec.accounting_event_id

AND crec.event_cost_id = crd.event_cost_id

AND crd.accounting_event_id = cre1.accounting_event_id

AND crd.accounting_line_type IN('ACCRUAL', 'EXPENSE_ACCRUAL')

AND cpod.active_flag = 'Y'

AND cpod.accrue_on_receipt_flag = 'Y'

AND cpod.cmr_po_distribution_id = cre1.cmr_po_distribution_id

and crd.ledger_currency_code=fncur.currency_code

GROUP BY cpod.cmr_po_distribution_id,

cpod.distribution_number,

cre1.business_unit_id,

cpod.purchase_basis,

cpod.event_type,

cpod.match_option,

cpod.inventory_item_id,

cre1.inventory_org_id,

cpod.category_id,

cpod.vendor_id,

cpod.vendor_site_id,

cpod.cmr_po_line_location_id,

cpod.po_number,

cpod.line_number,

cpod.shipment_number,

cpod.procurement_business_unit_id,

cpod.ship_to_location_id,

cpod.BILL_TO_BUSINESS_UNIT_ID,

cpod.destination_type_code,

cpod.item_description)

cre,

(SELECT cpod.cmr_po_distribution_id cmr_po_distribution_id,

cpod.cmr_po_line_location_id,

decode(SUM(decode(final_match_flag, 'Y', 1, 0, NULL, 0)), 0, 'No', 'Yes') invoicefinalmatch,

TRUNC(sysdate -MAX(TRUNC(accounting_date)), 0) invoiceage,

SUM(decode(invoice_distribution_type, 'ACCRUAL', nvl(invoice_qty_in_po_uom, 0), 0)) invoiceqty,

SUM(nvl(invoice_base_amount,0)) apaccrualamt,

SUM(DECODE(invoice_distribution_type, 'ACCRUAL', NVL(invoice_base_amount, 0), 0)) invoice_accrual_amt,

SUM(DECODE(invoice_distribution_type, 'NONREC_TAX', NVL(invoice_base_amount, 0), 0)) invoice_nonrectax_amt,

SUM(DECODE(invoice_distribution_type, 'REC_TAX', NVL(invoice_base_amount, 0), 0)) invoice_rectax_amt

FROM cmr_ap_invoice_dtls cap, CMR_PURCHASE_ORDER_DTLS cpod

WHERE (invoice_distribution_type IN('ACCRUAL', 'NONREC_TAX') or (invoice_distribution_type = 'REC_TAX' and tax_point_basis = 'DELIVERY'))

AND NVL(self_assessed_flag,'N') <> 'Y'

and cap.cmr_po_distribution_id = cpod.cmr_po_distribution_id

and cpod.active_flag = 'Y'

and cpod.accrue_on_receipt_flag = 'Y'

GROUP BY cpod.cmr_po_distribution_id,

cpod.cmr_po_line_location_id)

caid

WHERE caid.cmr_po_distribution_id(+) = cre.cmr_po_distribution_id

and caid.cmr_po_line_location_id(+) = cre.cmr_po_line_location_id

)

cred,

egp_system_items_vl egp,

inv_org_parameters iop,

egp_categories_vl egpc,

poz_suppliers_v supplier,

poz_supplier_sites_all_m suppliersite,

inv_org_parameters inv,

cmr_bus bu,

cmr_bus pcbu

WHERE cred.inventory_item_id = egp.inventory_item_id(+)

AND cred.inventory_org_id = iop.organization_id

AND nvl(iop.master_organization_id,cred.inventory_org_id) = egp.organization_id(+)

AND cred.inventory_org_id = inv.organization_id

AND cred.category_id = egpc.category_id

AND cred.supplierid = supplier.vendor_id(+)

AND cred.suppliersiteid = suppliersite.vendor_site_id(+)

AND bu.bu_id = cred.procurement_bu_id

AND cred.accrualclramt <> 0

and pcbu.bu_id = cred.PROFIT_CENTER_BU_ID