1. Tables and Views for SCM
  2. CJM_XLA_SUPPLIER_ACC_HDR_V

CJM_XLA_SUPPLIER_ACC_HDR_V

Details

  • Schema: FUSION

  • Object owner: CJM

  • Object type: VIEW

Columns

Name

EVENT_ID

THIRD_PARTY_TYPE

VENDOR_ID

VENDOR_SITE_ID

ACCRUAL_AMOUNT

ACCRUAL_CURRENCY

ACCRUAL_LEDGER_AMOUNT

DISTRIBUTION_TYPE

DISTRIBUTION_ID

ACCRUAL_BU_ID

ITEM_LEVEL_CODE

ITEM_ID

ITEM_NUMBER

PROGRAM_TEMPLATE_ID

PROGRAM_TYPE_ID

PROGRAM_HEADER_ID

SEEDED_PROGRAM_TYPE_CODE

PROGRAM_CODE

PROGRAM_LINE_ID

UTILIZATION_TYPE

ADJUSTMENT_TYPE

ADJUSTMENT_TYPE_ID

EXCHANGE_RATE

EXCHANGE_RATE_DATE

EXCHANGE_RATE_TYPE

GL_DATE

OBJECT_TYPE

OBJECT_ID

OBJECT_NUMBER

OBJECT_LINE_ID

REFERENCE_TYPE

REFERENCE_ID

SHIPMENT_DATE

SHIPMENT_NUMBER

INVOICE_DATE

INVOICE_NUMBER

INVOICE_ID

INVOICE_SOURCE_CODE

AUTHORIZATION_NUMBER

WAYBILL_NUMBER

CUSTOMER_PROGRAM_FLAG

ACCOUNTED_AMOUNT_OVERRIDE_FLAG

CUSTOMER_TRX_LINE_ID

SUPPLIER_INVOICE_DATE

SUPPLIER_INVOICE_NUMBER

SUPPLIER_INVOICE_ID

SUPPLIER_INVOICE_SOURCE

SUPPLIER_INVOICE_LINE_NUMBER

RECEIPT_ID

RECEIPT_NUMBER

PO_HEADER_ID

PO_NUMBER

PO_BILL_TO_LOC_ID

PO_SHIP_TO_LOC_ID

PGM_HEADER_ATTRIB_CATGORY

PGM_HEADER_ATTRIB_1

PGM_HEADER_ATTRIB_2

PGM_HEADER_ATTRIB_3

PGM_HEADER_ATTRIB_4

PGM_HEADER_ATTRIB_5

PGM_HEADER_ATTRIB_6

PGM_HEADER_ATTRIB_7

PGM_HEADER_ATTRIB_8

PGM_HEADER_ATTRIB_9

PGM_HEADER_ATTRIB_10

PGM_HEADER_ATTRIB_11

PGM_HEADER_ATTRIB_12

PGM_HEADER_ATTRIB_13

PGM_HEADER_ATTRIB_14

PGM_HEADER_ATTRIB_15

PGM_HEADER_ATTRIB_16

PGM_HEADER_ATTRIB_17

PGM_HEADER_ATTRIB_18

PGM_HEADER_ATTRIB_19

PGM_HEADER_ATTRIB_20

PGM_HEADER_ATTRIB_NUM_1

PGM_HEADER_ATTRIB_NUM_2

PGM_HEADER_ATTRIB_NUM_3

PGM_HEADER_ATTRIB_NUM_4

PGM_HEADER_ATTRIB_NUM_5

PGM_HEADER_ATTRIB_NUM_6

PGM_HEADER_ATTRIB_NUM_7

PGM_HEADER_ATTRIB_NUM_8

PGM_HEADER_ATTRIB_NUM_9

PGM_HEADER_ATTRIB_NUM_10

PGM_HEADER_ATTRIB_DATE_1

PGM_HEADER_ATTRIB_DATE_2

PGM_HEADER_ATTRIB_DATE_3

PGM_HEADER_ATTRIB_DATE_4

PGM_HEADER_ATTRIB_DATE_5

Query

SQL_Statement

SELECT /*+ leading(XEG) use_nl(XEG oxa )*/

xeg.event_id,

'S' third_party_type,

util.vendor_id,

util.vendor_site_id,

util.transaction_curr_amount accrual_amount,

util.transaction_currency_code accrual_currency,

util.acctd_amount accrual_ledger_amount,

'SUPPLIER_ACCRUAL' distribution_type,

util.utilization_id distribution_id,

util.bu_id accrual_bu_id,

util.item_level_code item_level_code,

util.item_id item_id,

CJM_UTILITY_PKG.get_item_name(util.item_level_code,util.item_id,util.bu_id) item_number,

template_b.program_type_id program_template_id,

ptype_b.program_type_id program_type_id,

pgm_b.program_header_id program_header_id,

pgm_b.program_type_code seeded_program_type_code,

pgm_b.program_code,

util.program_line_id,

util.utilization_type utilization_type,

util.adjustment_type adjustment_type,

util.adjustment_type_id adjustment_type_id,

util.exchange_rate exchange_rate,

util.exchange_rate_date exchange_rate_date,

util.exchange_rate_type exchange_rate_type,

util.gl_date gl_date,

util.object_type object_type,

util.object_id object_id,

DECODE(util.object_type, 'ORDER', doh.order_number, NULL) object_number,

util.object_line_id object_line_id,

util.reference_type reference_type,

util.reference_id reference_id,

util.shipment_date shipment_date,

util.shipment_number shipment_number,

DECODE(util.invoice_source_application,'AR',util.invoice_date, NULL) invoice_date,

DECODE(util.invoice_source_application,'AR',util.invoice_number, NULL) invoice_number,

DECODE(util.invoice_source_application,'AR',util.invoice_id, NULL) invoice_id,

DECODE(util.invoice_source_application,'AR',util.invoice_source_code, NULL) invoice_source_code,

pgm_b.authorization_number authorization_number,

util.waybill_number waybill_number,

ptype_b.customer_program customer_program_flag,

'Y' accounted_amount_override_flag,

DECODE(util.invoice_source_application,'AR',util.customer_trx_line_id, NULL) customer_trx_line_id,

DECODE(util.invoice_source_application,'AP',util.invoice_date, NULL) supplier_invoice_date,

DECODE(util.invoice_source_application,'AP',util.invoice_number, NULL) supplier_invoice_number,

DECODE(util.invoice_source_application,'AP',util.invoice_id, NULL) supplier_invoice_id,

DECODE(util.invoice_source_application,'AP',util.invoice_source_code, NULL) supplier_invoice_source,

DECODE(util.invoice_source_application,'AP',util.invoice_line_number, NULL) supplier_invoice_line_number,

DECODE(util.seeded_program_type_code,'SUPPLIER_REBATE',util.receipt_id,NULL) receipt_id,

DECODE(util.seeded_program_type_code,'SUPPLIER_REBATE',util.receipt_number,NULL) receipt_number,

DECODE(util.seeded_program_type_code,'SUPPLIER_REBATE',util.po_header_id,NULL) po_header_id,

DECODE(util.seeded_program_type_code,'SUPPLIER_REBATE',util.po_number,NULL) po_number,

util.bill_to_location_id po_bill_to_loc_id,

util.ship_to_location_id po_ship_to_loc_id,

pgm_b.ATTRIBUTE_CATEGORY PGM_HEADER_ATTRIB_CATGORY ,

pgm_b.ATTRIBUTE_CHAR1 PGM_HEADER_ATTRIB_1 ,

pgm_b.ATTRIBUTE_CHAR2 PGM_HEADER_ATTRIB_2 ,

pgm_b.ATTRIBUTE_CHAR3 PGM_HEADER_ATTRIB_3 ,

pgm_b.ATTRIBUTE_CHAR4 PGM_HEADER_ATTRIB_4 ,

pgm_b.ATTRIBUTE_CHAR5 PGM_HEADER_ATTRIB_5 ,

pgm_b.ATTRIBUTE_CHAR6 PGM_HEADER_ATTRIB_6 ,

pgm_b.ATTRIBUTE_CHAR7 PGM_HEADER_ATTRIB_7 ,

pgm_b.ATTRIBUTE_CHAR8 PGM_HEADER_ATTRIB_8 ,

pgm_b.ATTRIBUTE_CHAR9 PGM_HEADER_ATTRIB_9 ,

pgm_b.ATTRIBUTE_CHAR10 PGM_HEADER_ATTRIB_10 ,

pgm_b.ATTRIBUTE_CHAR11 PGM_HEADER_ATTRIB_11 ,

pgm_b.ATTRIBUTE_CHAR12 PGM_HEADER_ATTRIB_12 ,

pgm_b.ATTRIBUTE_CHAR13 PGM_HEADER_ATTRIB_13 ,

pgm_b.ATTRIBUTE_CHAR14 PGM_HEADER_ATTRIB_14 ,

pgm_b.ATTRIBUTE_CHAR15 PGM_HEADER_ATTRIB_15 ,

pgm_b.ATTRIBUTE_CHAR16 PGM_HEADER_ATTRIB_16 ,

pgm_b.ATTRIBUTE_CHAR17 PGM_HEADER_ATTRIB_17 ,

pgm_b.ATTRIBUTE_CHAR18 PGM_HEADER_ATTRIB_18 ,

pgm_b.ATTRIBUTE_CHAR19 PGM_HEADER_ATTRIB_19 ,

pgm_b.ATTRIBUTE_CHAR20 PGM_HEADER_ATTRIB_20 ,

pgm_b.ATTRIBUTE_NUMBER1 PGM_HEADER_ATTRIB_NUM_1 ,

pgm_b.ATTRIBUTE_NUMBER2 PGM_HEADER_ATTRIB_NUM_2 ,

pgm_b.ATTRIBUTE_NUMBER3 PGM_HEADER_ATTRIB_NUM_3 ,

pgm_b.ATTRIBUTE_NUMBER4 PGM_HEADER_ATTRIB_NUM_4 ,

pgm_b.ATTRIBUTE_NUMBER5 PGM_HEADER_ATTRIB_NUM_5 ,

pgm_b.ATTRIBUTE_NUMBER6 PGM_HEADER_ATTRIB_NUM_6 ,

pgm_b.ATTRIBUTE_NUMBER7 PGM_HEADER_ATTRIB_NUM_7 ,

pgm_b.ATTRIBUTE_NUMBER8 PGM_HEADER_ATTRIB_NUM_8 ,

pgm_b.ATTRIBUTE_NUMBER9 PGM_HEADER_ATTRIB_NUM_9 ,

pgm_b.ATTRIBUTE_NUMBER10 PGM_HEADER_ATTRIB_NUM_10 ,

pgm_b.ATTRIBUTE_DATE1 PGM_HEADER_ATTRIB_DATE_1 ,

pgm_b.ATTRIBUTE_DATE2 PGM_HEADER_ATTRIB_DATE_2 ,

pgm_b.ATTRIBUTE_DATE3 PGM_HEADER_ATTRIB_DATE_3 ,

pgm_b.ATTRIBUTE_DATE4 PGM_HEADER_ATTRIB_DATE_4 ,

pgm_b.ATTRIBUTE_DATE5 PGM_HEADER_ATTRIB_DATE_5

FROM

cjm_programs_utilized_all_b util,

cjm_xla_accruals oxa,

cjm_programs_all_b pgm_b,

cjm_program_types_b template_b,

cjm_program_types_b ptype_b,

doo_headers_all doh,

xla_events_gt xeg

WHERE xeg.application_id = 10566

AND util.accrual_type = 'SUPPLIER'

AND xeg.entity_code = 'SUPPLIER_ACCRUAL'

AND util.utilization_id = oxa.utilization_id

AND util.program_header_id = pgm_b.program_header_id

AND xeg.source_id_int_1 = oxa.utilization_id

AND oxa.event_type_code = xeg.event_type_code

AND util.object_id = doh.header_id (+)

AND pgm_b.program_type_id = ptype_b.program_type_id

AND ptype_b.parent_program_type_id = template_b.program_type_id