CMR_R_ACCR_RECON_DTLS

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
PO_DISTRIBUTION_NUMBER PO_SHIPMENT_NUMBER PO_LINE_NUMBER PO_NUMBER PO_QUANTITY DESTINATION_TYPE PO_PRICE PO_MATCH_OPTION DELIVER_TO_INVENTORY_ORG_ID VENDOR_ID VENDOR_SITE_ID INVENTORY_ITEM_ID CATEGORY_ID ACCRUAL_ACCOUNT_ID CHART_OF_ACCOUNTS_ID CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_DATE TRANSACTION_TYPE QUANTITY TRANSACTION_PRICE ENTERED_AMOUNT FUNCTIONAL_PRICE FUNCTIONAL_AMOUNT TRANSACTION_DATE SOLD_TO_BUSINESS_UNIT_ID PO_CURRENCY UOM CMR_PO_DISTRIBUTION_ID RECEIVING_TRANSACTION_ID INVOICE_DISTRIBUTION_ID INVOICE_NUMBER INVOICE_LINE_NUMBER RECEIPT_NUMBER CMR_ACCRUAL_AMT AP_ACCRUAL_AMT NET_ACCRUAL_BALANCE_ABS NET_ACCRUAL_BALANCE

Name

PO_DISTRIBUTION_NUMBER

PO_SHIPMENT_NUMBER

PO_LINE_NUMBER

PO_NUMBER

PO_QUANTITY

DESTINATION_TYPE

PO_PRICE

PO_MATCH_OPTION

DELIVER_TO_INVENTORY_ORG_ID

VENDOR_ID

VENDOR_SITE_ID

INVENTORY_ITEM_ID

CATEGORY_ID

ACCRUAL_ACCOUNT_ID

CHART_OF_ACCOUNTS_ID

CURRENCY_CONVERSION_TYPE

CURRENCY_CONVERSION_RATE

CURRENCY_CONVERSION_DATE

TRANSACTION_TYPE

QUANTITY

TRANSACTION_PRICE

ENTERED_AMOUNT

FUNCTIONAL_PRICE

FUNCTIONAL_AMOUNT

TRANSACTION_DATE

SOLD_TO_BUSINESS_UNIT_ID

PO_CURRENCY

UOM

CMR_PO_DISTRIBUTION_ID

RECEIVING_TRANSACTION_ID

INVOICE_DISTRIBUTION_ID

INVOICE_NUMBER

INVOICE_LINE_NUMBER

RECEIPT_NUMBER

CMR_ACCRUAL_AMT

AP_ACCRUAL_AMT

NET_ACCRUAL_BALANCE_ABS

NET_ACCRUAL_BALANCE

Query

SQL_Statement
select cpod.distribution_number po_distribution_number, cpod.shipment_number po_shipment_number, cpod.line_number po_line_number, cpod.po_number, cpod.quantity_ordered-cpod.quantity_cancelled po_quantity, cpod.destination_type_code destination_type, cpod.price po_price, cpod.match_option po_match_option, cpod.deliver_to_inventory_org_id, cpod.vendor_id, cpod.vendor_site_id, cpod.inventory_item_id, cpod.category_id, crs.accrual_account_id, crs.chart_of_accounts_id, card.currency_conversion_type, card.currency_conversion_rate, card.currency_conversion_date, decode(card.invoice_distribution_id, null, cret.event_type_code, caid.invoice_type) transaction_type, card.event_qty quantity, decode(card.event_qty, null, null, card.event_entered_amt/card.event_qty) transaction_price, card.event_entered_amt entered_amount, decode(card.event_qty, null, null, card.event_accounted_amt/card.event_qty) functional_price, card.event_accounted_amt functional_amount, card.event_transaction_date transaction_date, crs.sold_to_business_unit_id, card.currency_code po_currency, card.event_uom_code uom, crs.cmr_po_distribution_id, card.cmr_rcv_transaction_id receiving_transaction_id, card.invoice_distribution_id, caid.invoice_number, caid.invoice_line_number, crt.receipt_number, crs.cmr_accrual_amt, crs.ap_accrual_amt, abs(crs.cmr_accrual_amt-crs.ap_accrual_amt) net_accrual_balance_abs, crs.ap_accrual_amt-crs.cmr_accrual_amt net_accrual_balance from cmr_accr_reconc_dtls card, cmr_purchase_order_dtls cpod, cmr_rcv_transactions crt, cmr_ap_invoice_dtls caid, cmr_rcv_event_types cret, cmr_accr_reconc_headers crs where cpod.accrue_on_receipt_flag ='Y' and cpod.active_flag ='Y' and cpod.cmr_po_distribution_id =crs.cmr_po_distribution_id and cpod.sold_to_business_unit_id =crs.sold_to_business_unit_id and card.cmr_rcv_transaction_id =crt.cmr_rcv_transaction_id(+) and card.invoice_distribution_id =caid.cmr_ap_invoice_dist_id(+) and cret.event_type_code(+) =card.event_type_code and crs.cmr_accr_reconc_header_id =card.cmr_accr_reconc_header_id

SQL_Statement

select cpod.distribution_number po_distribution_number,

cpod.shipment_number po_shipment_number,

cpod.line_number po_line_number,

cpod.po_number,

cpod.quantity_ordered-cpod.quantity_cancelled po_quantity,

cpod.destination_type_code destination_type,

cpod.price po_price,

cpod.match_option po_match_option,

cpod.deliver_to_inventory_org_id,

cpod.vendor_id,

cpod.vendor_site_id,

cpod.inventory_item_id,

cpod.category_id,

crs.accrual_account_id,

crs.chart_of_accounts_id,

card.currency_conversion_type,

card.currency_conversion_rate,

card.currency_conversion_date,

decode(card.invoice_distribution_id, null, cret.event_type_code, caid.invoice_type) transaction_type,

card.event_qty quantity,

decode(card.event_qty, null, null, card.event_entered_amt/card.event_qty) transaction_price,

card.event_entered_amt entered_amount,

decode(card.event_qty, null, null, card.event_accounted_amt/card.event_qty) functional_price,

card.event_accounted_amt functional_amount,

card.event_transaction_date transaction_date,

crs.sold_to_business_unit_id,

card.currency_code po_currency,

card.event_uom_code uom,

crs.cmr_po_distribution_id,

card.cmr_rcv_transaction_id receiving_transaction_id,

card.invoice_distribution_id,

caid.invoice_number,

caid.invoice_line_number,

crt.receipt_number,

crs.cmr_accrual_amt,

crs.ap_accrual_amt,

abs(crs.cmr_accrual_amt-crs.ap_accrual_amt) net_accrual_balance_abs,

crs.ap_accrual_amt-crs.cmr_accrual_amt net_accrual_balance

from cmr_accr_reconc_dtls card,

cmr_purchase_order_dtls cpod,

cmr_rcv_transactions crt,

cmr_ap_invoice_dtls caid,

cmr_rcv_event_types cret,

cmr_accr_reconc_headers crs

where cpod.accrue_on_receipt_flag ='Y'

and cpod.active_flag ='Y'

and cpod.cmr_po_distribution_id =crs.cmr_po_distribution_id

and cpod.sold_to_business_unit_id =crs.sold_to_business_unit_id

and card.cmr_rcv_transaction_id =crt.cmr_rcv_transaction_id(+)

and card.invoice_distribution_id =caid.cmr_ap_invoice_dist_id(+)

and cret.event_type_code(+) =card.event_type_code

and crs.cmr_accr_reconc_header_id =card.cmr_accr_reconc_header_id