CMR_R_ACCRUAL_CLR_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 PO_PRICE PO_MATCH_OPTION UOM PO_CURRENCY DELIVER_TO_INVENTORY_ORG_ID VENDOR_ID VENDOR_SITE_ID INVENTORY_ITEM_ID CATEGORY_ID SOLD_TO_BUSINESS_UNIT_ID CMR_PO_DISTRIBUTION_ID CMR_ACCRUAL_AMT AP_ACCRUAL_AMT ACCRUAL_CLR_AMT DESTINATION_TYPE ACCRUAL_ACCOUNT_ID ACCRUAL_CLR_RULE_NAME RECEIVING_TRANSACTION_ID INVOICE_DISTRIBUTION_ID CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_DATE TRANSACTION_DATE FUNCTIONAL_AMOUNT FUNCTIONAL_PRICE ENTERED_AMOUNT TRANSACTION_QTY ACCRUAL_CLR_DTL_ID CLEARING_MODE INVOICE_NUMBER RECEIPT_NUMBER TRANSACTION_TYPE

Name

PO_DISTRIBUTION_NUMBER

PO_SHIPMENT_NUMBER

PO_LINE_NUMBER

PO_NUMBER

PO_QUANTITY

PO_PRICE

PO_MATCH_OPTION

UOM

PO_CURRENCY

DELIVER_TO_INVENTORY_ORG_ID

VENDOR_ID

VENDOR_SITE_ID

INVENTORY_ITEM_ID

CATEGORY_ID

SOLD_TO_BUSINESS_UNIT_ID

CMR_PO_DISTRIBUTION_ID

CMR_ACCRUAL_AMT

AP_ACCRUAL_AMT

ACCRUAL_CLR_AMT

DESTINATION_TYPE

ACCRUAL_ACCOUNT_ID

ACCRUAL_CLR_RULE_NAME

RECEIVING_TRANSACTION_ID

INVOICE_DISTRIBUTION_ID

CURRENCY_CONVERSION_TYPE

CURRENCY_CONVERSION_RATE

CURRENCY_CONVERSION_DATE

TRANSACTION_DATE

FUNCTIONAL_AMOUNT

FUNCTIONAL_PRICE

ENTERED_AMOUNT

TRANSACTION_QTY

ACCRUAL_CLR_DTL_ID

CLEARING_MODE

INVOICE_NUMBER

RECEIPT_NUMBER

TRANSACTION_TYPE

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.PRICE PO_PRICE, CPOD.MATCH_OPTION PO_MATCH_OPTION, CPOD.UOM_CODE UOM , CPOD.CURRENCY_CODE PO_CURRENCY , CPOD.DELIVER_TO_INVENTORY_ORG_ID, CPOD.VENDOR_ID, CPOD.VENDOR_SITE_ID, CPOD.INVENTORY_ITEM_ID, CPOD.CATEGORY_ID, CACA.SOLD_TO_BUSINESS_UNIT_ID , CACA.CMR_PO_DISTRIBUTION_ID , CACA.CMR_ACCRUAL_AMT, CACA.AP_ACCRUAL_AMT , CACA.ACCRUAL_CLR_AMT , CACA.DESTINATION_TYPE_CODE DESTINATION_TYPE , CACA.ACCRUAL_ACCOUNT_ID , CACA.ACCRUAL_CLR_RULE_NAME , CACAD.CMR_RCV_TRANSACTION_ID RECEIVING_TRANSACTION_ID , CACAD.INVOICE_DISTRIBUTION_ID , CACAD.CURRENCY_CONVERSION_TYPE , CACAD.CURRENCY_CONVERSION_RATE , CACAD.CURRENCY_CONVERSION_DATE , CACAD.TRANSACTION_DATE TRANSACTION_DATE , CACAD.TXN_ACCOUNTED_AMT FUNCTIONAL_AMOUNT , DECODE(CACAD.TRANSACTION_QTY, NULL, NULL, CACAD.TXN_ENTERED_AMT/CACAD.TRANSACTION_QTY) FUNCTIONAL_PRICE , CACAD.TXN_ENTERED_AMT ENTERED_AMOUNT , CACAD.TRANSACTION_QTY , CACAD.ACCRUAL_CLR_DTL_ID , CACAD.ACCRUAL_CLR_MODE CLEARING_MODE , CAID.INVOICE_NUMBER , CRT.RECEIPT_NUMBER , CRET.EVENT_TYPE_CODE TRANSACTION_TYPE FROM CMR_ACCRUAL_CLR_AMTS CACA , CMR_ACCRUAL_CLR_AMT_DTLS CACAD , CMR_PURCHASE_ORDER_DTLS CPOD , CMR_RCV_TRANSACTIONS CRT , CMR_AP_INVOICE_DTLS CAID , CMR_RCV_EVENT_TYPES CRET WHERE CACA.ACCRUAL_CLR_ID =CACAD.ACCRUAL_CLR_ID AND CPOD.ACCRUE_ON_RECEIPT_FLAG ='Y' AND CPOD.ACTIVE_FLAG ='Y' AND CPOD.CMR_PO_DISTRIBUTION_ID =CACA.CMR_PO_DISTRIBUTION_ID AND CPOD.SOLD_TO_BUSINESS_UNIT_ID =CACA.sold_to_BUSINESS_UNIT_ID AND CACAD.CMR_RCV_TRANSACTION_ID =CRT.CMR_RCV_TRANSACTION_ID(+) AND CACAD.INVOICE_DISTRIBUTION_ID =CAID.CMR_AP_INVOICE_DIST_ID(+) AND CRET.EVENT_TYPE_CODE =CACAD.EVENT_TYPE_CODE AND CRET.EVENT_CLASS_CODE = 'ACCRUAL_CLEARING'

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.PRICE PO_PRICE,

CPOD.MATCH_OPTION PO_MATCH_OPTION,

CPOD.UOM_CODE UOM ,

CPOD.CURRENCY_CODE PO_CURRENCY ,

CPOD.DELIVER_TO_INVENTORY_ORG_ID,

CPOD.VENDOR_ID,

CPOD.VENDOR_SITE_ID,

CPOD.INVENTORY_ITEM_ID,

CPOD.CATEGORY_ID,

CACA.SOLD_TO_BUSINESS_UNIT_ID ,

CACA.CMR_PO_DISTRIBUTION_ID ,

CACA.CMR_ACCRUAL_AMT,

CACA.AP_ACCRUAL_AMT ,

CACA.ACCRUAL_CLR_AMT ,

CACA.DESTINATION_TYPE_CODE DESTINATION_TYPE ,

CACA.ACCRUAL_ACCOUNT_ID ,

CACA.ACCRUAL_CLR_RULE_NAME ,

CACAD.CMR_RCV_TRANSACTION_ID RECEIVING_TRANSACTION_ID ,

CACAD.INVOICE_DISTRIBUTION_ID ,

CACAD.CURRENCY_CONVERSION_TYPE ,

CACAD.CURRENCY_CONVERSION_RATE ,

CACAD.CURRENCY_CONVERSION_DATE ,

CACAD.TRANSACTION_DATE TRANSACTION_DATE ,

CACAD.TXN_ACCOUNTED_AMT FUNCTIONAL_AMOUNT ,

DECODE(CACAD.TRANSACTION_QTY, NULL, NULL, CACAD.TXN_ENTERED_AMT/CACAD.TRANSACTION_QTY) FUNCTIONAL_PRICE ,

CACAD.TXN_ENTERED_AMT ENTERED_AMOUNT ,

CACAD.TRANSACTION_QTY ,

CACAD.ACCRUAL_CLR_DTL_ID ,

CACAD.ACCRUAL_CLR_MODE CLEARING_MODE ,

CAID.INVOICE_NUMBER ,

CRT.RECEIPT_NUMBER ,

CRET.EVENT_TYPE_CODE TRANSACTION_TYPE

FROM CMR_ACCRUAL_CLR_AMTS CACA ,

CMR_ACCRUAL_CLR_AMT_DTLS CACAD ,

CMR_PURCHASE_ORDER_DTLS CPOD ,

CMR_RCV_TRANSACTIONS CRT ,

CMR_AP_INVOICE_DTLS CAID ,

CMR_RCV_EVENT_TYPES CRET

WHERE CACA.ACCRUAL_CLR_ID =CACAD.ACCRUAL_CLR_ID

AND CPOD.ACCRUE_ON_RECEIPT_FLAG ='Y'

AND CPOD.ACTIVE_FLAG ='Y'

AND CPOD.CMR_PO_DISTRIBUTION_ID =CACA.CMR_PO_DISTRIBUTION_ID

AND CPOD.SOLD_TO_BUSINESS_UNIT_ID =CACA.sold_to_BUSINESS_UNIT_ID

AND CACAD.CMR_RCV_TRANSACTION_ID =CRT.CMR_RCV_TRANSACTION_ID(+)

AND CACAD.INVOICE_DISTRIBUTION_ID =CAID.CMR_AP_INVOICE_DIST_ID(+)

AND CRET.EVENT_TYPE_CODE =CACAD.EVENT_TYPE_CODE

AND CRET.EVENT_CLASS_CODE = 'ACCRUAL_CLEARING'