CMR_R_UNINV_ACCR_DTLS_V

Details

• Schema: FUSION

• Object owner: CMR

• Object type: VIEW

Columns

Name

CMR_PO_DISTRIBUTION_ID CMR_RCV_TRANSACTION_ID CMR_AP_INVOICE_DIST_ID SOLD_TO_BUSINESS_UNIT_ID INVENTORY_ITEM_ID DELIVER_TO_INVENTORY_ORG_ID VENDOR_ID VENDOR_SITE_ID CATEGORY_ID PO_NUMBER LINE_NUMBER SHIPMENT_NUMBER DISTRIBUTION_NUMBER CURRENCY_CODE LINE_TYPE PURCHASE_BASIS PO_PRICE UOM_CODE CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_DATE QUANTITY_ORDERED QUANTITY_CANCELLED AMOUNT_ORDERED AMOUNT_CANCELLED TRANSACTION_DATE SOURCE_DOC_QTY TRANSACTION_AMT EVENT_DATE EVENT_TYPE ACCOUNTING_DATE INVOICED_QTY INVOICED_AMT INVOICE_VARIANCE_AMT_FUNC_CURR INVOICE_AMT_FUNC_CURR ITEM_DESCRIPTION

Query

SQL_Statement

select po.cmr_po_distribution_id, cr.cmr_rcv_transaction_id, NULL as cmr_ap_invoice_dist_id, po.bill_to_business_unit_id as sold_to_business_unit_id, po.inventory_item_id, po.deliver_to_inventory_org_id, po.vendor_id, po.vendor_site_id, po.category_id, po.po_number, po.line_number, po.shipment_number, po.distribution_number, po.currency_code, po.purchase_basis as line_type, po.purchase_basis, /* (po.price + NVL(po.NONRECOVERABLE_TAX,0) ) as po_price, */ DECODE(po.purchase_basis,'SERVICES', NULL , (po.tax_exclusive_price + NVL(po.NONRECOVERABLE_TAX,0) + nvl(po.nonrecoverable_inclusive_tax,0) ) ) as po_price, po.uom_code, po.currency_conversion_type, po.currency_conversion_rate, po.currency_conversion_date, po.quantity_ordered, po.quantity_cancelled, po.amount_ordered, po.amount_cancelled, CST_LE_TIMEZONE_GRP.Get_Le_Day_Time(cr.transaction_date, 'INVENTORY_ORG_ID', cr.SHIP_TO_ORGANIZATION_ID) as transaction_date, cr.source_doc_qty, /* NVL(cr.transaction_amt,0) * ( 1 + NVL(po.NONRECOVERABLE_TAX,0) ) as transaction_amt,*/ DECODE(po.purchase_basis,'SERVICES', (NVL(cr.transaction_amt,0)/ (1 + nvl(po.recoverable_inclusive_tax,0) + nvl(po.nonrecoverable_inclusive_tax,0) )) * ( 1 + NVL(po.NONRECOVERABLE_TAX,0) + nvl(po.nonrecoverable_inclusive_tax,0) ) , NVL(cr.transaction_amt,0) * ( 1 + NVL(po.NONRECOVERABLE_TAX,0) ) ) as transaction_amt, po.event_date, po.event_type, NULL as accounting_date, 0 as invoiced_qty, 0 as invoiced_amt, 0 as INVOICE_VARIANCE_AMT_FUNC_CURR, 0 as INVOICE_AMT_FUNC_CURR, po.ITEM_DESCRIPTION from cmr_purchase_order_dtls po, cmr_transactions cr where po.destination_type_code = 'EXPENSE' and po.active_flag ='Y' and po.accrue_on_receipt_flag = 'N' and cr.cmr_po_distribution_id = po.cmr_po_distribution_id and cr.TRANSACTION_TYPE IN (SELECT transaction_type from CMR_RCV_EVENT_TYPES cret WHERE cret.event_class_code = 'RECEIVING' ) UNION ALL select po.cmr_po_distribution_id, NULL as cmr_rcv_transaction_id, ap.cmr_ap_invoice_dist_id as cmr_ap_invoice_dist_id, po.bill_to_business_unit_id as sold_to_business_unit_id, po.inventory_item_id, po.deliver_to_inventory_org_id, po.vendor_id, po.vendor_site_id, po.category_id, po.po_number, po.line_number, po.shipment_number, po.distribution_number, po.currency_code, po.purchase_basis as line_type, po.purchase_basis, /* (po.price + NVL(po.NONRECOVERABLE_TAX,0) ) as po_price, */ DECODE(po.purchase_basis,'SERVICES', NULL , (po.tax_exclusive_price + NVL(po.NONRECOVERABLE_TAX,0) + nvl(po.nonrecoverable_inclusive_tax,0) ) ) as po_price, po.uom_code, po.currency_conversion_type, po.currency_conversion_rate, po.currency_conversion_date, po.quantity_ordered, po.quantity_cancelled, po.amount_ordered, po.amount_cancelled, NULL as transaction_date, NULL as source_doc_qty, NULL as transaction_amt, po.event_date, po.event_type, ap.accounting_date as accounting_date, nvl(ap.invoice_qty_in_po_uom,0) as invoiced_qty, DECODE(AP.INVOICE_DISTRIBUTION_TYPE, 'ITEM' ,INVOICE_AMT, 'NONREC_TAX', INVOICE_AMT, 0) AS INVOICED_AMT, DECODE(AP.INVOICE_DISTRIBUTION_TYPE, 'ITEM' ,0, 'NONREC_TAX', 0, 'REC_TAX',0,INVOICE_BASE_AMOUNT) AS INVOICE_VARIANCE_AMT_FUNC_CURR, DECODE(AP.INVOICE_DISTRIBUTION_TYPE, 'ITEM' ,INVOICE_BASE_AMOUNT, 'NONREC_TAX', INVOICE_BASE_AMOUNT, 0) as INVOICE_AMT_FUNC_CURR, po.ITEM_DESCRIPTION from cmr_purchase_order_dtls po, cmr_ap_invoice_dtls ap where po.destination_type_code = 'EXPENSE' and po.active_flag ='Y' and po.accrue_on_receipt_flag = 'N' and ap.cmr_po_distribution_id = po.cmr_po_distribution_id and exists (select 'receiptExists' from cmr_transactions ct where ct.cmr_po_distribution_id = po.cmr_po_distribution_id)