1. Tables and Views for SCM
  2. CJM_XLA_SUPP_CLAIM_LNS_MLS_V

CJM_XLA_SUPP_CLAIM_LNS_MLS_V

Details

  • Schema: FUSION

  • Object owner: CJM

  • Object type: VIEW

Columns

Name

EVENT_ID

LINE_NUMBER

UTIL_PROGRAM_TEMPLATE_NAME

UTIL_PROGRAM_TYPE_NAME

UTIL_ADJUSTMENT_TYPE_NAME

UTIL_PROGRAM_NAME

LANGUAGE

Query

SQL_Statement

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

xeg.event_id event_id,

oxl.line_number line_number,

template_tl.program_type_name util_program_template_name,

ptype_tl.program_type_name util_program_type_name,

adj.adjustment_type_name util_adjustment_type_name,

pgm_tl.name util_program_name,

pgm_tl.language language

FROM

cjm_claims_all cla,

cjm_claim_lines_all cln,

cjm_claim_lines_util_all clu,

cjm_programs_utilized_all_b util,

cjm_xla_claim_lines oxl,

cjm_xla_claim_headers oxh,

cjm_programs_all_b pgm_b,

cjm_programs_all_tl pgm_tl,

cjm_program_types_b template_b,

cjm_program_types_tl template_tl,

cjm_program_types_b ptype_b,

cjm_program_types_tl ptype_tl,

(SELECT adjtype_tl.adjustment_type_name, adjtype_b.adjustment_type_id, adjtype_tl.language

FROM cjm_adjustment_types_b adjtype_b,

cjm_adjustment_types_tl adjtype_tl

WHERE adjtype_b.adjustment_type_id = adjtype_tl.adjustment_type_id) adj,

xla_events_gt xeg

WHERE xeg.entity_code = 'SUPPLIER_CLAIM_SETTLEMENT'

AND cla.claim_for_code = 'SUPPLIER'

AND xeg.source_id_int_1 = oxh.claim_id

AND oxh.event_type_code = xeg.event_type_code

AND xeg.event_id = oxh.event_id

AND xeg.application_id = 10566

AND oxh.claim_id = cla.claim_id

AND cla.claim_id = cln.claim_id

AND cln.claim_line_id = clu.claim_line_id

AND clu.utilization_id = util.utilization_id

AND oxh.xla_claim_header_id = oxl.xla_claim_header_id

AND oxl.claim_line_util_id = clu.claim_line_util_id

AND util.adjustment_type_id = adj.adjustment_type_id(+)

AND util.program_header_id = pgm_b.program_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

AND pgm_b.program_header_id = pgm_tl.program_header_id

AND template_b.program_type_id = template_tl.program_type_id

AND ptype_b.program_type_id = ptype_tl.program_type_id

AND template_tl.language = pgm_tl.language

AND ptype_tl.language = pgm_tl.language

AND adj.language(+) = pgm_tl.language