CMR_ALL_INTF_TXNS

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
TRANSACTION_ID SOURCE_TABLE BILL_TO_BUSINESS_UNIT_ID INVENTORY_ORG_ID ITEM_ORGANIZATION_ID TRANSACTION_TYPE DOCUMENT_TYPE_CODE TRANSACTION_NUMBER TRANSACTION_DATE TRANSACTION_LINE_NUMBER DESTINATION_TYPE_CODE DISTRIBUTION_TYPE INVENTORY_ITEM_ID TRANSACTION_QTY TRANSACTION_UOM_CODE TRANSACTION_AMT CURRENCY_CODE CONVERT_TO_LETZ

Name

TRANSACTION_ID

SOURCE_TABLE

BILL_TO_BUSINESS_UNIT_ID

INVENTORY_ORG_ID

ITEM_ORGANIZATION_ID

TRANSACTION_TYPE

DOCUMENT_TYPE_CODE

TRANSACTION_NUMBER

TRANSACTION_DATE

TRANSACTION_LINE_NUMBER

DESTINATION_TYPE_CODE

DISTRIBUTION_TYPE

INVENTORY_ITEM_ID

TRANSACTION_QTY

TRANSACTION_UOM_CODE

TRANSACTION_AMT

CURRENCY_CODE

CONVERT_TO_LETZ

Query

SQL_Statement
SELECT TO_NUMBER(CRT.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID, 'CMR_I_RCV_TRANSACTIONS' SOURCE_TABLE, NVL(P.BILLTO_BU_ID, IOP.BUSINESS_UNIT_ID) BILL_TO_BUSINESS_UNIT_ID, CRT.SHIP_TO_ORGANIZATION_ID INVENTORY_ORG_ID, CRT.SHIP_TO_ORGANIZATION_ID ITEM_ORGANIZATION_ID, CRT.TRANSACTION_TYPE, 'Interface Receipts' DOCUMENT_TYPE_CODE, CRT.RECEIPT_NUMBER TRANSACTION_NUMBER, CRT.TRANSACTION_DATE, CRT.RECEIPT_LINE_NUMBER TRANSACTION_LINE_NUMBER, pll.DESTINATION_TYPE_CODE, /* This will show correct PO destination type / NULL DISTRIBUTION_TYPE, CRT.INVENTORY_ITEM_ID, CRT.TRANSACTION_QUANTITY TRANSACTION_QTY, CRT.TRANSACTION_UOM_CODE, NULL TRANSACTION_AMT, P.CURRENCY_CODE, 1 CONVERT_TO_LETZ FROM CMR_I_RCV_TRANSACTIONS CRT, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL P, INV_ORG_PARAMETERS IOP WHERE CRT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+) AND PLL.PO_HEADER_ID = P.PO_HEADER_ID AND CRT.SHIP_TO_ORGANIZATION_ID = IOP.ORGANIZATION_ID UNION ALL SELECT TO_NUMBER(CAID.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID, 'CMR_I_AP_INVOICE_DTLS' SOURCE_TABLE, P.BILLTO_BU_ID BILL_TO_BUSINESS_UNIT_ID, PLL.SHIP_TO_ORGANIZATION_ID INVENTORY_ORG_ID, PLL.SHIP_TO_ORGANIZATION_ID ITEM_ORGANIZATION_ID, CAID.INVOICE_TYPE TRANSACTION_TYPE, 'Interface Invoices' DOCUMENT_TYPE_CODE, CAID.INVOICE_NUMBER TRANSACTION_NUMBER, CAID.ACCOUNTING_DATE TRANSACTION_DATE, TO_NUMBER(CAID.INVOICE_LINE_NUMBER) TRANSACTION_LINE_NUMBER, PD.DESTINATION_TYPE_CODE, NULL DISTIRBUTION_TYPE, PL.ITEM_ID, CAID.INVOICE_QTY TRANSACTION_QTY, CAID.UNIT_OF_MEASURE TRANSACTION_UOM_CODE, CAID.INVOICE_AMT TRANSACTION_AMT, CAID.CURRENCY_CODE, 0 CONVERT_TO_LETZ FROM CMR_I_AP_INVOICE_DTLS CAID, PO_DISTRIBUTIONS_ALL PD, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL PL, PO_HEADERS_ALL P WHERE CAID.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND PLL.PO_LINE_ID = PL.PO_LINE_ID AND PD.PO_HEADER_ID = P.PO_HEADER_ID UNION ALL SELECT TO_NUMBER(CTE.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID, 'CST_I_TRADE_EVENTS' SOURCE_TABLE, CTE.BILL_TO_BU_ID BILL_TO_BUSINESS_UNIT_ID, CTE.INVENTORY_ORG_ID, (SELECT MASTER_ORGANIZATION_ID FROM INV_ORG_PARAMETERS OP WHERE OP.ORGANIZATION_ID = CTE.INVENTORY_ORG_ID) ITEM_ORGANIZATION_ID, DECODE(TRANSACTION_TYPE,'RECEIPT_ACCRUAL','Trade Receipt Accrual','RETURN_ACCRUAL','Trade Return Accrual', 'INTRANSIT_RETURN','Trade In-Transit Return', 'INTRANSIT_ISSUE','Trade In-Transit Issue' , 'INTRANSIT_RECEIPT','Trade In-Transit Receipt', 'INTRANSIT_RETURN_RECEIPT','Trade In-Transit Return Receipt', NULL) TRANSACTION_TYPE, 'Interface Trade Events' DOCUMENT_TYPE_CODE, TXN_SOURCE_DOC_NUMBER TRANSACTION_NUMBER, CTE.TRANSACTION_DATE, TO_NUMBER(NULL) TRANSACTION_LINE_NUMBER, DECODE(cte.po_line_location_id ,NULL, 'INVENTORY' , (select poll.destination_type_code from po_line_locations_all poll where poll.line_location_id = cte.po_line_location_id ) ) DESTINATION_TYPE_CODE, / Non Purchase Order flows are INVENTORY flows and PO flows using POLL / NULL DISTIRBUTION_TYPE, CTE.INVENTORY_ITEM_ID, CTE.TRANSACTION_QTY TRANSACTION_QTY, CTE.TRANSACTION_UOM_CODE TRANSACTION_UOM_CODE, NULL TRANSACTION_AMT, CTE.INVOICE_CURRENCY_CODE CURRENCY_CODE, 1 CONVERT_TO_LETZ FROM CST_I_TRADE_EVENTS CTE WHERE CTE.TRANSACTION_TYPE IN ( 'RECEIPT_ACCRUAL', 'RETURN_ACCRUAL') OR (CTE.TRANSACTION_TYPE IN ( 'INTRANSIT_RETURN', 'INTRANSIT_ISSUE', 'INTRANSIT_RECEIPT', 'INTRANSIT_RETURN_RECEIPT') AND CTE.INVENTORY_ITEM_ID IS NULL) UNION ALL SELECT TRANSACTION_ID TRANSACTION_ID, 'RCV_TRANSACTIONS' SOURCE_TABLE, NVL(P.BILLTO_BU_ID, IOP.BUSINESS_UNIT_ID) BILL_TO_BUSINESS_UNIT_ID, R.ORGANIZATION_ID INVENTORY_ORG_ID, R.ORGANIZATION_ID ITEM_ORGANIZATION_ID, R.TRANSACTION_TYPE, 'Unimported Receipts' DOCUMENT_TYPE_CODE, RSH.RECEIPT_NUM TRANSACTION_NUMBER, R.TRANSACTION_DATE, RSL.LINE_NUM TRANSACTION_LINE_NUMBER, R.DESTINATION_TYPE_CODE, NULL DISTIRBUTION_TYPE, RSL.ITEM_ID, R.QUANTITY TRANSACTION_QTY, R.UOM_CODE TRANSACTION_UOM_CODE, NULL TRANSACTION_AMT, P.CURRENCY_CODE, 1 CONVERT_TO_LETZ FROM RCV_TRANSACTIONS R, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_HEADERS_ALL P, INV_ORG_PARAMETERS IOP WHERE R.ACCRUAL_STATUS_CODE = 'PENDING' AND R.PO_HEADER_ID = P.PO_HEADER_ID (+) AND R.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND R.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND R.ORGANIZATION_ID = IOP.ORGANIZATION_ID UNION ALL SELECT to_number(ap.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID, 'AP_INVOICE_DISTRIBUTIONS_ALL' SOURCE_TABLE, ap.INVOICING_BUSINESS_UNIT_ID BILL_TO_BUSINESS_UNIT_ID, NULL INVENTORY_ORG_ID, NULL ITEM_ORGANIZATION_ID, AP.INVOICE_TYPE TRANSACTION_TYPE, 'Unimported Invoices' DOCUMENT_TYPE_CODE, ap.INVOICE_NUMBER TRANSACTION_NUMBER, ap.ACCOUNTING_DATE TRANSACTION_DATE, ap.invoice_line_number TRANSACTION_LINE_NUMBER, (select pod.DESTINATION_TYPE_CODE from po_distributions_all pod where POD.PO_DISTRIBUTION_ID = ap.PO_DISTRIBUTION_ID) DESTINATION_TYPE_CODE, /PO Destination Type*/ ap.INVOICE_DISTRIBUTION_TYPE DISTIRBUTION_TYPE, (select ail.INVENTORY_ITEM_ID from AP_INVOICE_LINES_ALL ail where ail.INVOICE_ID = ap.INVOICE_ID and ail.line_number = ap.invoice_line_number ) INVENTORY_ITEM_ID, ap.INVOICE_QTY TRANSACTION_QTY, ap.UNIT_OF_MEASURE TRANSACTION_UOM_CODE, ap.INVOICE_AMT TRANSACTION_AMT, ap.CURRENCY_CODE, 0 CONVERT_TO_LETZ from AP_COSTING_TRANSFER_EXTRACT_V ap

SQL_Statement

SELECT TO_NUMBER(CRT.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID,

'CMR_I_RCV_TRANSACTIONS' SOURCE_TABLE,

NVL(P.BILLTO_BU_ID, IOP.BUSINESS_UNIT_ID) BILL_TO_BUSINESS_UNIT_ID,

CRT.SHIP_TO_ORGANIZATION_ID INVENTORY_ORG_ID,

CRT.SHIP_TO_ORGANIZATION_ID ITEM_ORGANIZATION_ID,

CRT.TRANSACTION_TYPE,

'Interface Receipts' DOCUMENT_TYPE_CODE,

CRT.RECEIPT_NUMBER TRANSACTION_NUMBER,

CRT.TRANSACTION_DATE,

CRT.RECEIPT_LINE_NUMBER TRANSACTION_LINE_NUMBER,

pll.DESTINATION_TYPE_CODE, /* This will show correct PO destination type */

NULL DISTRIBUTION_TYPE,

CRT.INVENTORY_ITEM_ID,

CRT.TRANSACTION_QUANTITY TRANSACTION_QTY,

CRT.TRANSACTION_UOM_CODE,

NULL TRANSACTION_AMT,

P.CURRENCY_CODE,

1 CONVERT_TO_LETZ

FROM CMR_I_RCV_TRANSACTIONS CRT,

PO_LINE_LOCATIONS_ALL PLL,

PO_HEADERS_ALL P,

INV_ORG_PARAMETERS IOP

WHERE CRT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)

AND PLL.PO_HEADER_ID = P.PO_HEADER_ID

AND CRT.SHIP_TO_ORGANIZATION_ID = IOP.ORGANIZATION_ID

UNION ALL

SELECT TO_NUMBER(CAID.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID,

'CMR_I_AP_INVOICE_DTLS' SOURCE_TABLE,

P.BILLTO_BU_ID BILL_TO_BUSINESS_UNIT_ID,

PLL.SHIP_TO_ORGANIZATION_ID INVENTORY_ORG_ID,

PLL.SHIP_TO_ORGANIZATION_ID ITEM_ORGANIZATION_ID,

CAID.INVOICE_TYPE TRANSACTION_TYPE,

'Interface Invoices' DOCUMENT_TYPE_CODE,

CAID.INVOICE_NUMBER TRANSACTION_NUMBER,

CAID.ACCOUNTING_DATE TRANSACTION_DATE,

TO_NUMBER(CAID.INVOICE_LINE_NUMBER) TRANSACTION_LINE_NUMBER,

PD.DESTINATION_TYPE_CODE,

NULL DISTIRBUTION_TYPE,

PL.ITEM_ID,

CAID.INVOICE_QTY TRANSACTION_QTY,

CAID.UNIT_OF_MEASURE TRANSACTION_UOM_CODE,

CAID.INVOICE_AMT TRANSACTION_AMT,

CAID.CURRENCY_CODE,

0 CONVERT_TO_LETZ

FROM CMR_I_AP_INVOICE_DTLS CAID,

PO_DISTRIBUTIONS_ALL PD,

PO_LINE_LOCATIONS_ALL PLL,

PO_LINES_ALL PL,

PO_HEADERS_ALL P

WHERE CAID.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID

AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID

AND PLL.PO_LINE_ID = PL.PO_LINE_ID

AND PD.PO_HEADER_ID = P.PO_HEADER_ID

UNION ALL

SELECT TO_NUMBER(CTE.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID,

'CST_I_TRADE_EVENTS' SOURCE_TABLE,

CTE.BILL_TO_BU_ID BILL_TO_BUSINESS_UNIT_ID,

CTE.INVENTORY_ORG_ID,

(SELECT MASTER_ORGANIZATION_ID FROM INV_ORG_PARAMETERS OP WHERE OP.ORGANIZATION_ID = CTE.INVENTORY_ORG_ID) ITEM_ORGANIZATION_ID,

DECODE(TRANSACTION_TYPE,'RECEIPT_ACCRUAL','Trade Receipt Accrual','RETURN_ACCRUAL','Trade Return Accrual', 'INTRANSIT_RETURN','Trade In-Transit Return', 'INTRANSIT_ISSUE','Trade In-Transit Issue' , 'INTRANSIT_RECEIPT','Trade In-Transit Receipt', 'INTRANSIT_RETURN_RECEIPT','Trade In-Transit Return Receipt', NULL) TRANSACTION_TYPE,

'Interface Trade Events' DOCUMENT_TYPE_CODE,

TXN_SOURCE_DOC_NUMBER TRANSACTION_NUMBER,

CTE.TRANSACTION_DATE,

TO_NUMBER(NULL) TRANSACTION_LINE_NUMBER,

DECODE(cte.po_line_location_id ,NULL, 'INVENTORY' ,

(select poll.destination_type_code from po_line_locations_all poll where poll.line_location_id = cte.po_line_location_id )

) DESTINATION_TYPE_CODE, /* Non Purchase Order flows are INVENTORY flows and PO flows using POLL */

NULL DISTIRBUTION_TYPE,

CTE.INVENTORY_ITEM_ID,

CTE.TRANSACTION_QTY TRANSACTION_QTY,

CTE.TRANSACTION_UOM_CODE TRANSACTION_UOM_CODE,

NULL TRANSACTION_AMT,

CTE.INVOICE_CURRENCY_CODE CURRENCY_CODE,

1 CONVERT_TO_LETZ

FROM CST_I_TRADE_EVENTS CTE

WHERE CTE.TRANSACTION_TYPE IN ( 'RECEIPT_ACCRUAL', 'RETURN_ACCRUAL')

OR (CTE.TRANSACTION_TYPE IN ( 'INTRANSIT_RETURN', 'INTRANSIT_ISSUE', 'INTRANSIT_RECEIPT', 'INTRANSIT_RETURN_RECEIPT')

AND CTE.INVENTORY_ITEM_ID IS NULL)

UNION ALL

SELECT TRANSACTION_ID TRANSACTION_ID,

'RCV_TRANSACTIONS' SOURCE_TABLE,

NVL(P.BILLTO_BU_ID, IOP.BUSINESS_UNIT_ID) BILL_TO_BUSINESS_UNIT_ID,

R.ORGANIZATION_ID INVENTORY_ORG_ID,

R.ORGANIZATION_ID ITEM_ORGANIZATION_ID,

R.TRANSACTION_TYPE,

'Unimported Receipts' DOCUMENT_TYPE_CODE,

RSH.RECEIPT_NUM TRANSACTION_NUMBER,

R.TRANSACTION_DATE,

RSL.LINE_NUM TRANSACTION_LINE_NUMBER,

R.DESTINATION_TYPE_CODE,

NULL DISTIRBUTION_TYPE,

RSL.ITEM_ID,

R.QUANTITY TRANSACTION_QTY,

R.UOM_CODE TRANSACTION_UOM_CODE,

NULL TRANSACTION_AMT,

P.CURRENCY_CODE,

1 CONVERT_TO_LETZ

FROM RCV_TRANSACTIONS R,

RCV_SHIPMENT_HEADERS RSH,

RCV_SHIPMENT_LINES RSL,

PO_HEADERS_ALL P,

INV_ORG_PARAMETERS IOP

WHERE

R.ACCRUAL_STATUS_CODE = 'PENDING'

AND R.PO_HEADER_ID = P.PO_HEADER_ID (+)

AND R.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

AND R.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID

AND R.ORGANIZATION_ID = IOP.ORGANIZATION_ID

UNION ALL

SELECT to_number(ap.EXTERNAL_SYSTEM_REF_ID) TRANSACTION_ID,

'AP_INVOICE_DISTRIBUTIONS_ALL' SOURCE_TABLE,

ap.INVOICING_BUSINESS_UNIT_ID BILL_TO_BUSINESS_UNIT_ID,

NULL INVENTORY_ORG_ID,

NULL ITEM_ORGANIZATION_ID,

AP.INVOICE_TYPE TRANSACTION_TYPE,

'Unimported Invoices' DOCUMENT_TYPE_CODE,

ap.INVOICE_NUMBER TRANSACTION_NUMBER,

ap.ACCOUNTING_DATE TRANSACTION_DATE,

ap.invoice_line_number TRANSACTION_LINE_NUMBER,

(select pod.DESTINATION_TYPE_CODE from po_distributions_all pod where

POD.PO_DISTRIBUTION_ID = ap.PO_DISTRIBUTION_ID) DESTINATION_TYPE_CODE, /*PO

Destination Type*/

ap.INVOICE_DISTRIBUTION_TYPE DISTIRBUTION_TYPE,

(select ail.INVENTORY_ITEM_ID from AP_INVOICE_LINES_ALL ail where

ail.INVOICE_ID = ap.INVOICE_ID and ail.line_number = ap.invoice_line_number )

INVENTORY_ITEM_ID,

ap.INVOICE_QTY TRANSACTION_QTY,

ap.UNIT_OF_MEASURE TRANSACTION_UOM_CODE,

ap.INVOICE_AMT TRANSACTION_AMT,

ap.CURRENCY_CODE,

0 CONVERT_TO_LETZ

from AP_COSTING_TRANSFER_EXTRACT_V ap