1. Tables and Views for SCM
  2. CJM_XLA_SR_TRX_V

CJM_XLA_SR_TRX_V

Details

  • Schema: FUSION

  • Object owner: CJM

  • Object type: VIEW

Columns

Name

DOCUMENT_BATCH_ID

DOCUMENT_HEADER_ID

DOCUMENT_LINE_ID

RECEIVING_TRANSACTION_ID

PO_BILL_TO_LOCATION

PO_BILL_TO_LOCATION_ID

PO_BILL_TO_LOCATION_CITY

PO_BILL_TO_LOCATION_COUNTY

PO_BILL_TO_LOCATION_COUNTRY

PO_BILL_TO_LOCATION_STATE

PO_BILL_TO_LOCATION_PROVINCE

PO_BILL_TO_LOC_POSTAL_CODE

PO_SHIP_TO_LOCATION

PO_SHIP_TO_LOCATION_ID

PO_SHIP_TO_LOCATION_CITY

PO_SHIP_TO_LOCATION_COUNTY

PO_SHIP_TO_LOCATION_COUNTRY

PO_SHIP_TO_LOCATION_STATE

PO_SHIP_TO_LOCATION_PROVINCE

PO_SHIP_TO_LOC_POSTAL_CODE

BATCH_DATA_SOURCE

PO_DATE

SUPPLIER_ITEM

INVENTORY_ORGANIZATION

INVENTORY_ORGANIZATION_ID

SUP_TRX_HDR_ATTR_CATGORY

SUP_TRX_HDR_ATTR_1

SUP_TRX_HDR_ATTR_2

SUP_TRX_HDR_ATTR_3

SUP_TRX_HDR_ATTR_4

SUP_TRX_HDR_ATTR_5

SUP_TRX_HDR_ATTR_6

SUP_TRX_HDR_ATTR_7

SUP_TRX_HDR_ATTR_8

SUP_TRX_HDR_ATTR_9

SUP_TRX_HDR_ATTR_10

SUP_TRX_HDR_ATTR_11

SUP_TRX_HDR_ATTR_12

SUP_TRX_HDR_ATTR_13

SUP_TRX_HDR_ATTR_14

SUP_TRX_HDR_ATTR_15

SUP_TRX_HDR_ATTR_16

SUP_TRX_HDR_ATTR_17

SUP_TRX_HDR_ATTR_18

SUP_TRX_HDR_ATTR_19

SUP_TRX_HDR_ATTR_20

SUP_TRX_HDR_ATTR_NUM_1

SUP_TRX_HDR_ATTR_NUM_2

SUP_TRX_HDR_ATTR_NUM_3

SUP_TRX_HDR_ATTR_NUM_4

SUP_TRX_HDR_ATTR_NUM_5

SUP_TRX_HDR_ATTR_NUM_6

SUP_TRX_HDR_ATTR_NUM_7

SUP_TRX_HDR_ATTR_NUM_8

SUP_TRX_HDR_ATTR_NUM_9

SUP_TRX_HDR_ATTR_NUM_10

SUP_TRX_HDR_ATTR_DATE_1

SUP_TRX_HDR_ATTR_DATE_2

SUP_TRX_HDR_ATTR_DATE_3

SUP_TRX_HDR_ATTR_DATE_4

SUP_TRX_HDR_ATTR_DATE_5

SUP_TRX_LINE_ATTR_1

SUP_TRX_LINE_ATTR_2

SUP_TRX_LINE_ATTR_3

SUP_TRX_LINE_ATTR_4

SUP_TRX_LINE_ATTR_5

SUP_TRX_LINE_ATTR_6

SUP_TRX_LINE_ATTR_7

SUP_TRX_LINE_ATTR_8

SUP_TRX_LINE_ATTR_9

SUP_TRX_LINE_ATTR_10

SUP_TRX_LINE_ATTR_11

SUP_TRX_LINE_ATTR_12

SUP_TRX_LINE_ATTR_13

SUP_TRX_LINE_ATTR_14

SUP_TRX_LINE_ATTR_15

SUP_TRX_LINE_ATTR_16

SUP_TRX_LINE_ATTR_17

SUP_TRX_LINE_ATTR_18

SUP_TRX_LINE_ATTR_19

SUP_TRX_LINE_ATTR_20

SUP_TRX_LINE_ATTR_CATGORY

SUP_TRX_LINE_ATTR_NUM_1

SUP_TRX_LINE_ATTR_NUM_2

SUP_TRX_LINE_ATTR_NUM_3

SUP_TRX_LINE_ATTR_NUM_4

SUP_TRX_LINE_ATTR_NUM_5

SUP_TRX_LINE_ATTR_NUM_6

SUP_TRX_LINE_ATTR_NUM_7

SUP_TRX_LINE_ATTR_NUM_8

SUP_TRX_LINE_ATTR_NUM_9

SUP_TRX_LINE_ATTR_NUM_10

SUP_TRX_LINE_ATTR_DATE_1

SUP_TRX_LINE_ATTR_DATE_2

SUP_TRX_LINE_ATTR_DATE_3

SUP_TRX_LINE_ATTR_DATE_4

SUP_TRX_LINE_ATTR_DATE_5

Query

SQL_Statement

SELECT

cdb.document_batch_id "DOCUMENT_BATCH_ID",

cdh.document_header_id "DOCUMENT_HEADER_ID",

cdl.document_line_id "DOCUMENT_LINE_ID",

cdl.transaction_id "RECEIVING_TRANSACTION_ID",

Bill_HRL.location_name "PO_BILL_TO_LOCATION",

cdl.bill_to_location_id "PO_BILL_TO_LOCATION_ID",

Bill_HRL.TOWN_OR_CITY "PO_BILL_TO_LOCATION_CITY",

Bill_HRL.COUNTRY "PO_BILL_TO_LOCATION_COUNTY",

Bill_HRL.REGION_1 "PO_BILL_TO_LOCATION_COUNTRY",

Bill_HRL.REGION_2 "PO_BILL_TO_LOCATION_STATE",

Bill_HRL.REGION_3 "PO_BILL_TO_LOCATION_PROVINCE",

Bill_HRL.POSTAL_CODE "PO_BILL_TO_LOC_POSTAL_CODE",

Ship_HRL.location_name "PO_SHIP_TO_LOCATION",

cdl.ship_to_location_id "PO_SHIP_TO_LOCATION_ID",

Ship_HRL.TOWN_OR_CITY "PO_SHIP_TO_LOCATION_CITY",

Ship_HRL.COUNTRY "PO_SHIP_TO_LOCATION_COUNTY",

Ship_HRL.REGION_1 "PO_SHIP_TO_LOCATION_COUNTRY",

Ship_HRL.REGION_2 "PO_SHIP_TO_LOCATION_STATE",

Ship_HRL.REGION_3 "PO_SHIP_TO_LOCATION_PROVINCE",

Ship_HRL.POSTAL_CODE "PO_SHIP_TO_LOC_POSTAL_CODE",

cdb.data_source_code "BATCH_DATA_SOURCE",

cdl.po_date "PO_DATE",

cdl.tp_item_num "SUPPLIER_ITEM",

(SELECT hou.name

FROM fusion.HR_ORGANIZATION_UNITS_F_TL hou

WHERE hou.organization_id = cdl.inventory_organization_id

AND hou.LANGUAGE = USERENV('LANG')

AND rownum = 1) "INVENTORY_ORGANIZATION",

cdl.inventory_organization_id "INVENTORY_ORGANIZATION_ID",

cdh.attribute_category SUP_TRX_HDR_ATTR_CATGORY,

cdh.attribute_char1 SUP_TRX_HDR_ATTR_1,

cdh.attribute_char2 SUP_TRX_HDR_ATTR_2,

cdh.attribute_char3 SUP_TRX_HDR_ATTR_3,

cdh.attribute_char4 SUP_TRX_HDR_ATTR_4,

cdh.attribute_char5 SUP_TRX_HDR_ATTR_5,

cdh.attribute_char6 SUP_TRX_HDR_ATTR_6,

cdh.attribute_char7 SUP_TRX_HDR_ATTR_7,

cdh.attribute_char8 SUP_TRX_HDR_ATTR_8,

cdh.attribute_char9 SUP_TRX_HDR_ATTR_9,

cdh.attribute_char10 SUP_TRX_HDR_ATTR_10,

cdh.attribute_char11 SUP_TRX_HDR_ATTR_11,

cdh.attribute_char12 SUP_TRX_HDR_ATTR_12,

cdh.attribute_char13 SUP_TRX_HDR_ATTR_13,

cdh.attribute_char14 SUP_TRX_HDR_ATTR_14,

cdh.attribute_char15 SUP_TRX_HDR_ATTR_15,

cdh.attribute_char16 SUP_TRX_HDR_ATTR_16,

cdh.attribute_char17 SUP_TRX_HDR_ATTR_17,

cdh.attribute_char18 SUP_TRX_HDR_ATTR_18,

cdh.attribute_char19 SUP_TRX_HDR_ATTR_19,

cdh.attribute_char20 SUP_TRX_HDR_ATTR_20,

cdh.attribute_number1 SUP_TRX_HDR_ATTR_NUM_1,

cdh.attribute_number2 SUP_TRX_HDR_ATTR_NUM_2,

cdh.attribute_number3 SUP_TRX_HDR_ATTR_NUM_3,

cdh.attribute_number4 SUP_TRX_HDR_ATTR_NUM_4,

cdh.attribute_number5 SUP_TRX_HDR_ATTR_NUM_5,

cdh.attribute_number6 SUP_TRX_HDR_ATTR_NUM_6,

cdh.attribute_number7 SUP_TRX_HDR_ATTR_NUM_7,

cdh.attribute_number8 SUP_TRX_HDR_ATTR_NUM_8,

cdh.attribute_number9 SUP_TRX_HDR_ATTR_NUM_9,

cdh.attribute_number10 SUP_TRX_HDR_ATTR_NUM_10,

cdh.attribute_date1 SUP_TRX_HDR_ATTR_DATE_1,

cdh.attribute_date2 SUP_TRX_HDR_ATTR_DATE_2,

cdh.attribute_date3 SUP_TRX_HDR_ATTR_DATE_3,

cdh.attribute_date4 SUP_TRX_HDR_ATTR_DATE_4,

cdh.attribute_date5 SUP_TRX_HDR_ATTR_DATE_5,

cdl.attribute_char1 SUP_TRX_LINE_ATTR_1,

cdl.attribute_char2 SUP_TRX_LINE_ATTR_2,

cdl.attribute_char3 SUP_TRX_LINE_ATTR_3,

cdl.attribute_char4 SUP_TRX_LINE_ATTR_4,

cdl.attribute_char5 SUP_TRX_LINE_ATTR_5,

cdl.attribute_char6 SUP_TRX_LINE_ATTR_6,

cdl.attribute_char7 SUP_TRX_LINE_ATTR_7,

cdl.attribute_char8 SUP_TRX_LINE_ATTR_8,

cdl.attribute_char9 SUP_TRX_LINE_ATTR_9,

cdl.attribute_char10 SUP_TRX_LINE_ATTR_10,

cdl.attribute_char11 SUP_TRX_LINE_ATTR_11,

cdl.attribute_char12 SUP_TRX_LINE_ATTR_12,

cdl.attribute_char13 SUP_TRX_LINE_ATTR_13,

cdl.attribute_char14 SUP_TRX_LINE_ATTR_14,

cdl.attribute_char15 SUP_TRX_LINE_ATTR_15,

cdl.attribute_char16 SUP_TRX_LINE_ATTR_16,

cdl.attribute_char17 SUP_TRX_LINE_ATTR_17,

cdl.attribute_char18 SUP_TRX_LINE_ATTR_18,

cdl.attribute_char19 SUP_TRX_LINE_ATTR_19,

cdl.attribute_char20 SUP_TRX_LINE_ATTR_20,

cdl.attribute_category SUP_TRX_LINE_ATTR_CATGORY,

cdl.attribute_number1 SUP_TRX_LINE_ATTR_NUM_1,

cdl.attribute_number2 SUP_TRX_LINE_ATTR_NUM_2,

cdl.attribute_number3 SUP_TRX_LINE_ATTR_NUM_3,

cdl.attribute_number4 SUP_TRX_LINE_ATTR_NUM_4,

cdl.attribute_number5 SUP_TRX_LINE_ATTR_NUM_5,

cdl.attribute_number6 SUP_TRX_LINE_ATTR_NUM_6,

cdl.attribute_number7 SUP_TRX_LINE_ATTR_NUM_7,

cdl.attribute_number8 SUP_TRX_LINE_ATTR_NUM_8,

cdl.attribute_number9 SUP_TRX_LINE_ATTR_NUM_9,

cdl.attribute_number10 SUP_TRX_LINE_ATTR_NUM_10,

cdl.attribute_date1 SUP_TRX_LINE_ATTR_DATE_1,

cdl.attribute_date2 SUP_TRX_LINE_ATTR_DATE_2,

cdl.attribute_date3 SUP_TRX_LINE_ATTR_DATE_3,

cdl.attribute_date4 SUP_TRX_LINE_ATTR_DATE_4,

cdl.attribute_date5 SUP_TRX_LINE_ATTR_DATE_5

FROM

cjm_document_headers_all cdh,

cjm_document_lines_all cdl,

cjm_document_batches_all cdb,

hr_locations_all_vl ship_hrl,

hr_locations_all_vl bill_hrl

WHERE cdh.document_header_id = cdl.document_header_id

AND cdb.document_batch_id = cdl.document_batch_id

AND cdl.bill_to_location_id = Bill_HRL.location_id(+)

AND cdl.ship_to_location_id = Ship_HRL.location_id(+)