AR_CUST_TRX_LINES_L

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
TRX_LINE_ACCT_RULE_DURATION TRX_LINE_AMT_INCLUDES_TAX_FLAG TRX_LINE_ATTR1 TRX_LINE_ATTR10 TRX_LINE_ATTR11 TRX_LINE_ATTR12 TRX_LINE_ATTR13 TRX_LINE_ATTR14 TRX_LINE_ATTR15 TRX_LINE_ATTR2 TRX_LINE_ATTR3 TRX_LINE_ATTR4 TRX_LINE_ATTR5 TRX_LINE_ATTR6 TRX_LINE_ATTR7 TRX_LINE_ATTR8 TRX_LINE_ATTR9 TRX_LINE_ATTR_CAT TRX_LINE_CUSTOMER_TRX_LINE_ID TRX_LINE_DESCRIPTION PARENT_TRX_LINE_DESCRIPTION TRX_LINE_GLOBAL_ATTR1 TRX_LINE_GLOBAL_ATTR10 TRX_LINE_GLOBAL_ATTR11 TRX_LINE_GLOBAL_ATTR12 TRX_LINE_GLOBAL_ATTR13 TRX_LINE_GLOBAL_ATTR14 TRX_LINE_GLOBAL_ATTR15 TRX_LINE_GLOBAL_ATTR16 TRX_LINE_GLOBAL_ATTR17 TRX_LINE_GLOBAL_ATTR18 TRX_LINE_GLOBAL_ATTR19 TRX_LINE_GLOBAL_ATTR2 TRX_LINE_GLOBAL_ATTR20 TRX_LINE_GLOBAL_ATTR3 TRX_LINE_GLOBAL_ATTR4 TRX_LINE_GLOBAL_ATTR5 TRX_LINE_GLOBAL_ATTR6 TRX_LINE_GLOBAL_ATTR7 TRX_LINE_GLOBAL_ATTR8 TRX_LINE_GLOBAL_ATTR9 TRX_LINE_GLOBAL_ATTR_CAT TRX_LINE_GROSS_UNIT_SELL_PRICE TRX_LINE_INTERFACE_LINE_ATTR1 TRX_LINE_INTERFACE_LINE_ATTR10 TRX_LINE_INTERFACE_LINE_ATTR11 TRX_LINE_INTERFACE_LINE_ATTR12 TRX_LINE_INTERFACE_LINE_ATTR13 TRX_LINE_INTERFACE_LINE_ATTR14 TRX_LINE_INTERFACE_LINE_ATTR15 TRX_LINE_INTERFACE_LINE_ATTR2 TRX_LINE_INTERFACE_LINE_ATTR3 TRX_LINE_INTERFACE_LINE_ATTR4 TRX_LINE_INTERFACE_LINE_ATTR5 TRX_LINE_INTERFACE_LINE_ATTR6 TRX_LINE_INTERFACE_LINE_ATTR7 TRX_LINE_INTERFACE_LINE_ATTR8 TRX_LINE_INTERFACE_LINE_ATTR9 TRX_LINE_INTERFACE_LINE_CT TRX_LINE_ITEM_CT TRX_LINE_LINE_NUMBER TRX_LINE_LINE_TYPE TRX_LINE_QUANTITY_CREDITED TRX_LINE_QUANTITY_INVOICED TRX_LINE_QUANTITY_ORDERED TRX_LINE_REASON_CODE TRX_LINE_RULE_START_DATE TRX_LINE_SALES_ORDER TRX_LINE_SALES_ORDER_DATE TRX_LINE_SALES_ORDER_LINE TRX_LINE_SALES_ORDER_SOURCE TRX_LINE_TAX_EXEMPT_FLAG TRX_LINE_TAX_EXEMPT_NUMBER TRX_LINE_TAX_EXMPT_REASON_CODE TRX_LINE_TAX_PRECEDENCE TRX_LINE_TAX_RATE TRX_LINE_TRANSLATED_DESC TRX_LINE_UNIT_SELLING_PRICE TRX_LINE_UNIT_STANDARD_PRICE TRX_LINE_UOM_CODE TRX_LINE_DIST_ACCOUNT_CLASS TRX_LINE_DIST_AMT TRX_LINE_DIST_ATTR1 TRX_LINE_DIST_ATTR10 TRX_LINE_DIST_ATTR11 TRX_LINE_DIST_ATTR12 TRX_LINE_DIST_ATTR13 TRX_LINE_DIST_ATTR14 TRX_LINE_DIST_ATTR15 TRX_LINE_DIST_ATTR2 TRX_LINE_DIST_ATTR3 TRX_LINE_DIST_ATTR4 TRX_LINE_DIST_ATTR5 TRX_LINE_DIST_ATTR6 TRX_LINE_DIST_ATTR7 TRX_LINE_DIST_ATTR8 TRX_LINE_DIST_ATTR9 TRX_LINE_DIST_ATTR_CAT TRX_LINE_DIST_CCSEG TRX_LINE_DIST_CCID TRX_LINE_DIST_COL_TAX_CCID TRX_LINE_DIST_COL_TAX_CCSEG CARRIER SHIPPING_REFERENCE SHIP_DATE TRX_LINE_DIST_COMMENTS TRX_LINE_DIST_ID TRX_LINE_DIST_GL_DATE TRX_LINE_DIST_PERCENT TRX_LINE_DIST_REV_ADJ_CLASS TRX_LINE_DIST_PAIRED_CCID MEMO_LINE_GLOBAL_ATTR1 MEMO_LINE_GLOBAL_ATTR2 MEMO_LINE_GLOBAL_ATTR3 MEMO_LINE_GLOBAL_ATTR4 MEMO_LINE_GLOBAL_ATTR5 MEMO_LINE_GLOBAL_ATTR6 MEMO_LINE_GLOBAL_ATTR7 MEMO_LINE_GLOBAL_ATTR_CAT MEMO_LINE_REV_CCID MEMO_LINE_TYPE MEMO_LINE PARENT_MEMO_LINE MEMO_LINE_SEQ_ID MEMO_LINE_TAX_CODE MEMO_LINE_UNIT_STD_PRICE MEMO_LINE_UOM_CODE ACCT_RULE_DEFERRED_REV_FLAG ACCT_RULE_DESCRIPTION ACCT_RULE_FREQUENCY ACCT_RULE_NAME ACCT_RULE_OCCURRENCES ACCT_RULE_ID ACCT_RULE_TYPE INVOICING_RULE TRX_DISTRIBUTION_TYPE EVENT_ID LINE_NUMBER LANGUAGE LEDGER_ID TRX_LINE_DIST_PAIRED_ACC_CLASS TRX_TAX_LINE_ID CUSTOMER_TRX_ID TRX_PRODUCT_TYPE REC_OFFSET_FLAG AR_JV_CODE_COMBINATION_ID TRX_TAX_INVOICE_NUMBER TRX_TAX_INVOICE_DATE

Name

TRX_LINE_ACCT_RULE_DURATION

TRX_LINE_AMT_INCLUDES_TAX_FLAG

TRX_LINE_ATTR1

TRX_LINE_ATTR10

TRX_LINE_ATTR11

TRX_LINE_ATTR12

TRX_LINE_ATTR13

TRX_LINE_ATTR14

TRX_LINE_ATTR15

TRX_LINE_ATTR2

TRX_LINE_ATTR3

TRX_LINE_ATTR4

TRX_LINE_ATTR5

TRX_LINE_ATTR6

TRX_LINE_ATTR7

TRX_LINE_ATTR8

TRX_LINE_ATTR9

TRX_LINE_ATTR_CAT

TRX_LINE_CUSTOMER_TRX_LINE_ID

TRX_LINE_DESCRIPTION

PARENT_TRX_LINE_DESCRIPTION

TRX_LINE_GLOBAL_ATTR1

TRX_LINE_GLOBAL_ATTR10

TRX_LINE_GLOBAL_ATTR11

TRX_LINE_GLOBAL_ATTR12

TRX_LINE_GLOBAL_ATTR13

TRX_LINE_GLOBAL_ATTR14

TRX_LINE_GLOBAL_ATTR15

TRX_LINE_GLOBAL_ATTR16

TRX_LINE_GLOBAL_ATTR17

TRX_LINE_GLOBAL_ATTR18

TRX_LINE_GLOBAL_ATTR19

TRX_LINE_GLOBAL_ATTR2

TRX_LINE_GLOBAL_ATTR20

TRX_LINE_GLOBAL_ATTR3

TRX_LINE_GLOBAL_ATTR4

TRX_LINE_GLOBAL_ATTR5

TRX_LINE_GLOBAL_ATTR6

TRX_LINE_GLOBAL_ATTR7

TRX_LINE_GLOBAL_ATTR8

TRX_LINE_GLOBAL_ATTR9

TRX_LINE_GLOBAL_ATTR_CAT

TRX_LINE_GROSS_UNIT_SELL_PRICE

TRX_LINE_INTERFACE_LINE_ATTR1

TRX_LINE_INTERFACE_LINE_ATTR10

TRX_LINE_INTERFACE_LINE_ATTR11

TRX_LINE_INTERFACE_LINE_ATTR12

TRX_LINE_INTERFACE_LINE_ATTR13

TRX_LINE_INTERFACE_LINE_ATTR14

TRX_LINE_INTERFACE_LINE_ATTR15

TRX_LINE_INTERFACE_LINE_ATTR2

TRX_LINE_INTERFACE_LINE_ATTR3

TRX_LINE_INTERFACE_LINE_ATTR4

TRX_LINE_INTERFACE_LINE_ATTR5

TRX_LINE_INTERFACE_LINE_ATTR6

TRX_LINE_INTERFACE_LINE_ATTR7

TRX_LINE_INTERFACE_LINE_ATTR8

TRX_LINE_INTERFACE_LINE_ATTR9

TRX_LINE_INTERFACE_LINE_CT

TRX_LINE_ITEM_CT

TRX_LINE_LINE_NUMBER

TRX_LINE_LINE_TYPE

TRX_LINE_QUANTITY_CREDITED

TRX_LINE_QUANTITY_INVOICED

TRX_LINE_QUANTITY_ORDERED

TRX_LINE_REASON_CODE

TRX_LINE_RULE_START_DATE

TRX_LINE_SALES_ORDER

TRX_LINE_SALES_ORDER_DATE

TRX_LINE_SALES_ORDER_LINE

TRX_LINE_SALES_ORDER_SOURCE

TRX_LINE_TAX_EXEMPT_FLAG

TRX_LINE_TAX_EXEMPT_NUMBER

TRX_LINE_TAX_EXMPT_REASON_CODE

TRX_LINE_TAX_PRECEDENCE

TRX_LINE_TAX_RATE

TRX_LINE_TRANSLATED_DESC

TRX_LINE_UNIT_SELLING_PRICE

TRX_LINE_UNIT_STANDARD_PRICE

TRX_LINE_UOM_CODE

TRX_LINE_DIST_ACCOUNT_CLASS

TRX_LINE_DIST_AMT

TRX_LINE_DIST_ATTR1

TRX_LINE_DIST_ATTR10

TRX_LINE_DIST_ATTR11

TRX_LINE_DIST_ATTR12

TRX_LINE_DIST_ATTR13

TRX_LINE_DIST_ATTR14

TRX_LINE_DIST_ATTR15

TRX_LINE_DIST_ATTR2

TRX_LINE_DIST_ATTR3

TRX_LINE_DIST_ATTR4

TRX_LINE_DIST_ATTR5

TRX_LINE_DIST_ATTR6

TRX_LINE_DIST_ATTR7

TRX_LINE_DIST_ATTR8

TRX_LINE_DIST_ATTR9

TRX_LINE_DIST_ATTR_CAT

TRX_LINE_DIST_CCSEG

TRX_LINE_DIST_CCID

TRX_LINE_DIST_COL_TAX_CCID

TRX_LINE_DIST_COL_TAX_CCSEG

CARRIER

SHIPPING_REFERENCE

SHIP_DATE

TRX_LINE_DIST_COMMENTS

TRX_LINE_DIST_ID

TRX_LINE_DIST_GL_DATE

TRX_LINE_DIST_PERCENT

TRX_LINE_DIST_REV_ADJ_CLASS

TRX_LINE_DIST_PAIRED_CCID

MEMO_LINE_GLOBAL_ATTR1

MEMO_LINE_GLOBAL_ATTR2

MEMO_LINE_GLOBAL_ATTR3

MEMO_LINE_GLOBAL_ATTR4

MEMO_LINE_GLOBAL_ATTR5

MEMO_LINE_GLOBAL_ATTR6

MEMO_LINE_GLOBAL_ATTR7

MEMO_LINE_GLOBAL_ATTR_CAT

MEMO_LINE_REV_CCID

MEMO_LINE_TYPE

MEMO_LINE

PARENT_MEMO_LINE

MEMO_LINE_SEQ_ID

MEMO_LINE_TAX_CODE

MEMO_LINE_UNIT_STD_PRICE

MEMO_LINE_UOM_CODE

ACCT_RULE_DEFERRED_REV_FLAG

ACCT_RULE_DESCRIPTION

ACCT_RULE_FREQUENCY

ACCT_RULE_NAME

ACCT_RULE_OCCURRENCES

ACCT_RULE_ID

ACCT_RULE_TYPE

INVOICING_RULE

TRX_DISTRIBUTION_TYPE

EVENT_ID

LINE_NUMBER

LANGUAGE

LEDGER_ID

TRX_LINE_DIST_PAIRED_ACC_CLASS

TRX_TAX_LINE_ID

CUSTOMER_TRX_ID

TRX_PRODUCT_TYPE

REC_OFFSET_FLAG

AR_JV_CODE_COMBINATION_ID

TRX_TAX_INVOICE_NUMBER

TRX_TAX_INVOICE_DATE

Query

SQL_Statement
SELECT /+ INDEX(l AR_XLA_LINES_EXTRACT_N1) / ctlaf.accounting_rule_duration trx_line_acct_rule_duration , ctlaf.amount_includes_tax_flag trx_line_amt_includes_tax_flag , ctlaf.attribute1 trx_line_attr1 , ctlaf.attribute10 trx_line_attr10 , ctlaf.attribute11 trx_line_attr11 , ctlaf.attribute12 trx_line_attr12 , ctlaf.attribute13 trx_line_attr13 , ctlaf.attribute14 trx_line_attr14 , ctlaf.attribute15 trx_line_attr15 , ctlaf.attribute2 trx_line_attr2 , ctlaf.attribute3 trx_line_attr3 , ctlaf.attribute4 trx_line_attr4 , ctlaf.attribute5 trx_line_attr5 , ctlaf.attribute6 trx_line_attr6 , ctlaf.attribute7 trx_line_attr7 , ctlaf.attribute8 trx_line_attr8 , ctlaf.attribute9 trx_line_attr9 , ctlaf.attribute_category trx_line_attr_cat , ctlaf.customer_trx_line_id trx_line_customer_trx_line_id , ctlaf.description trx_line_description , Decode(ctlaf.line_type,'TAX', parent_line.description, 'FREIGHT', parent_line.description,ctlaf.description) parent_trx_line_description , ctlaf.global_attribute1 trx_line_global_attr1 , ctlaf.global_attribute10 trx_line_global_attr10 , ctlaf.global_attribute11 trx_line_global_attr11 , ctlaf.global_attribute12 trx_line_global_attr12 , ctlaf.global_attribute13 trx_line_global_attr13 , ctlaf.global_attribute14 trx_line_global_attr14 , ctlaf.global_attribute15 trx_line_global_attr15 , ctlaf.global_attribute16 trx_line_global_attr16 , ctlaf.global_attribute17 trx_line_global_attr17 , ctlaf.global_attribute18 trx_line_global_attr18 , ctlaf.global_attribute19 trx_line_global_attr19 , ctlaf.global_attribute2 trx_line_global_attr2 , ctlaf.global_attribute20 trx_line_global_attr20 , ctlaf.global_attribute3 trx_line_global_attr3 , ctlaf.global_attribute4 trx_line_global_attr4 , ctlaf.global_attribute5 trx_line_global_attr5 , ctlaf.global_attribute6 trx_line_global_attr6 , ctlaf.global_attribute7 trx_line_global_attr7 , ctlaf.global_attribute8 trx_line_global_attr8 , ctlaf.global_attribute9 trx_line_global_attr9 , ctlaf.global_attribute_category trx_line_global_attr_cat , ctlaf.gross_unit_selling_price trx_line_gross_unit_sell_price , ctlaf.interface_line_attribute1 trx_line_interface_line_attr1 , ctlaf.interface_line_attribute10 trx_line_interface_line_attr10 , ctlaf.interface_line_attribute11 trx_line_interface_line_attr11 , ctlaf.interface_line_attribute12 trx_line_interface_line_attr12 , ctlaf.interface_line_attribute13 trx_line_interface_line_attr13 , ctlaf.interface_line_attribute14 trx_line_interface_line_attr14 , ctlaf.interface_line_attribute15 trx_line_interface_line_attr15 , ctlaf.interface_line_attribute2 trx_line_interface_line_attr2 , ctlaf.interface_line_attribute3 trx_line_interface_line_attr3 , ctlaf.interface_line_attribute4 trx_line_interface_line_attr4 , ctlaf.interface_line_attribute5 trx_line_interface_line_attr5 , ctlaf.interface_line_attribute6 trx_line_interface_line_attr6 , ctlaf.interface_line_attribute7 trx_line_interface_line_attr7 , ctlaf.interface_line_attribute8 trx_line_interface_line_attr8 , ctlaf.interface_line_attribute9 trx_line_interface_line_attr9 , ctlaf.interface_line_context trx_line_interface_line_ct , ctlaf.item_context trx_line_item_ct , ctlaf.line_number trx_line_line_number , ctlaf.line_type trx_line_line_type , ctlaf.quantity_credited trx_line_quantity_credited , ctlaf.quantity_invoiced trx_line_quantity_invoiced , ctlaf.quantity_ordered trx_line_quantity_ordered , ctlaf.reason_code trx_line_reason_code , ctlaf.rule_start_date trx_line_rule_start_date , ctlaf.sales_order trx_line_sales_order , ctlaf.sales_order_date trx_line_sales_order_date , ctlaf.sales_order_line trx_line_sales_order_line , ctlaf.sales_order_source trx_line_sales_order_source , Decode(ctlaf.line_type,'LINE',ctlaf.tax_exempt_flag,'TAX', parent_line.tax_exempt_flag, 'FREIGHT', parent_line.tax_exempt_flag,ctlaf.tax_exempt_flag) trx_line_tax_exempt_flag , ctlaf.tax_exempt_number trx_line_tax_exempt_number , ctlaf.tax_exempt_reason_code trx_line_tax_exmpt_reason_code , ctlaf.tax_precedence trx_line_tax_precedence , ctlaf.tax_rate trx_line_tax_rate , ctlaf.translated_description trx_line_translated_desc , ctlaf.unit_selling_price trx_line_unit_selling_price , ctlaf.unit_standard_price trx_line_unit_standard_price , ctlaf.uom_code trx_line_uom_code , ctlgdf.account_class trx_line_dist_account_class , ctlgdf.amount trx_line_dist_amt , ctlgdf.attribute1 trx_line_dist_attr1 , ctlgdf.attribute10 trx_line_dist_attr10 , ctlgdf.attribute11 trx_line_dist_attr11 , ctlgdf.attribute12 trx_line_dist_attr12 , ctlgdf.attribute13 trx_line_dist_attr13 , ctlgdf.attribute14 trx_line_dist_attr14 , ctlgdf.attribute15 trx_line_dist_attr15 , ctlgdf.attribute2 trx_line_dist_attr2 , ctlgdf.attribute3 trx_line_dist_attr3 , ctlgdf.attribute4 trx_line_dist_attr4 , ctlgdf.attribute5 trx_line_dist_attr5 , ctlgdf.attribute6 trx_line_dist_attr6 , ctlgdf.attribute7 trx_line_dist_attr7 , ctlgdf.attribute8 trx_line_dist_attr8 , ctlgdf.attribute9 trx_line_dist_attr9 , ctlgdf.attribute_category trx_line_dist_attr_cat , ctlgdf.concatenated_segments trx_line_dist_ccseg , ctlgdf.code_combination_id trx_line_dist_ccid , ctlgdf.collected_tax_ccid trx_line_dist_col_tax_ccid , ctlgdf.collected_tax_concat_seg trx_line_dist_col_tax_ccseg , ctaf.ship_via carrier , ctaf.waybill_number shipping_reference , ctaf.ship_date_actual ship_date , ctlgdf.comments trx_line_dist_comments , l.cust_trx_line_gl_dist_id trx_line_dist_id , ctlgdf.gl_date trx_line_dist_gl_date , ctlgdf.percent trx_line_dist_percent , ctlgdf.rev_adj_class_temp trx_line_dist_rev_adj_class , l.paired_ccid trx_line_dist_paired_ccid , mlabf.global_attribute1 memo_line_global_attr1 , mlabf.global_attribute2 memo_line_global_attr2 , mlabf.global_attribute3 memo_line_global_attr3 , mlabf.global_attribute4 memo_line_global_attr4 , mlabf.global_attribute5 memo_line_global_attr5 , mlabf.global_attribute6 memo_line_global_attr6 , mlabf.global_attribute7 memo_line_global_attr7 , mlabf.global_attribute_category memo_line_global_attr_cat , refacc.REV_CCID memo_line_rev_ccid , mlabf.line_type memo_line_type , mlabf.name memo_line , Decode(ctlaf.line_type,'TAX', parent_mline.name, 'FREIGHT', parent_mline.name,mlabf.name) parent_memo_line , mlabf.memo_line_seq_id memo_line_seq_id , mlabf.tax_code memo_line_tax_code , mlabf.unit_std_price memo_line_unit_std_price , mlabf.uom_code memo_line_uom_code , rarlf.deferred_revenue_flag acct_rule_deferred_rev_flag , rarlf.description acct_rule_description , case when ctlaf.accounting_rule_id is NULL THEN NULL else gll.meaning end acct_rule_frequency , rarlf.name acct_rule_name , rarlf.occurrences acct_rule_occurrences , rarlf.rule_id acct_rule_id , rarlf.type acct_rule_type , rarlf1.name invoicing_rule , 'RA_CUST_TRX_LINE_GL_DIST_ALL' trx_distribution_type , l.event_id event_id , l.line_number line_number , l.language language , l.ledger_id ledger_id , pctlgd.account_class TRX_LINE_DIST_PAIRED_ACC_CLASS , ctlaf.tax_line_id trx_tax_line_id, ctlgdf.customer_trx_id, ctlaf.product_type trx_product_type, nvl(ctlgdf.rec_offset_flag,'N'), decode(ctlaf.interface_line_context, 'Joint Venture Invoice', to_number(nvl(ctlaf.interface_line_attribute6,'-1')),null) AR_JV_CODE_COMBINATION_ID, ctlaf.tax_invoice_number trx_tax_invoice_number, ctlaf.tax_invoice_date trx_tax_invoice_date FROM ar_xla_lines_extract l , ra_cust_trx_line_gl_dist_all ctlgdf , ra_customer_trx_lines_all ctlaf , ra_customer_trx_lines_all parent_line , ar_memo_lines_all_vl mlabf , ar_memo_lines_all_vl parent_mline , ra_rules rarlf , ra_rules rarlf1 , ra_cust_trx_line_gl_dist_all pctlgd, AR_REF_ACCOUNTS_ALL refacc, ra_customer_trx_all ctaf, gl_ledgers lgr, gl_calendars cal, gl_lookups gll WHERE ctlgdf.cust_trx_line_gl_dist_id = l.cust_trx_line_gl_dist_id AND ctaf.customer_trx_id = ctlgdf.customer_trx_id AND ctlaf.customer_trx_line_id(+) = l.customer_trx_line_id AND refacc.source_ref_account_id (+) = ctlaf.memo_line_seq_id AND refacc.SOURCE_REF_TABLE (+) = 'AR_MEMO_LINES_ALL_B' AND refacc.bu_id (+) = ctlaf.org_id AND l.level_flag = 'L' AND ctlaf.memo_line_seq_id = mlabf.memo_line_seq_id (+) AND ctlaf.accounting_rule_id = rarlf.rule_id (+) AND ctaf.invoicing_rule_id = rarlf1.rule_id (+) AND l.paire_dist_id = pctlgd.cust_trx_line_gl_dist_id(+) AND ctlaf.link_to_cust_trx_line_id = parent_line.customer_trx_line_id(+) AND parent_line.memo_line_seq_id = parent_mline.memo_line_seq_id (+) AND lgr.period_set_name = cal.period_set_name AND lgr.accounted_period_type = cal.period_type AND gll.lookup_type (+) = 'CAL_NON_ADJ_FREQ' AND gll.lookup_code (+) = cal.non_adj_period_freq_code AND lgr.ledger_id = ctaf.set_of_books_id UNION ALL SELECT /+ INDEX(l AR_XLA_LINES_EXTRACT_N1) / ctlaf.accounting_rule_duration trx_line_acct_rule_duration , ctlaf.amount_includes_tax_flag trx_line_amt_includes_tax_flag , ctlaf.attribute1 trx_line_attr1 , ctlaf.attribute10 trx_line_attr10 , ctlaf.attribute11 trx_line_attr11 , ctlaf.attribute12 trx_line_attr12 , ctlaf.attribute13 trx_line_attr13 , ctlaf.attribute14 trx_line_attr14 , ctlaf.attribute15 trx_line_attr15 , ctlaf.attribute2 trx_line_attr2 , ctlaf.attribute3 trx_line_attr3 , ctlaf.attribute4 trx_line_attr4 , ctlaf.attribute5 trx_line_attr5 , ctlaf.attribute6 trx_line_attr6 , ctlaf.attribute7 trx_line_attr7 , ctlaf.attribute8 trx_line_attr8 , ctlaf.attribute9 trx_line_attr9 , ctlaf.attribute_category trx_line_attr_cat , ctlaf.customer_trx_line_id trx_line_customer_trx_line_id , ctlaf.description trx_line_description , Decode(ctlaf.line_type,'TAX', parent_line.description, 'FREIGHT', parent_line.description,ctlaf.description) parent_trx_line_description , ctlaf.global_attribute1 trx_line_global_attr1 , ctlaf.global_attribute10 trx_line_global_attr10 , ctlaf.global_attribute11 trx_line_global_attr11 , ctlaf.global_attribute12 trx_line_global_attr12 , ctlaf.global_attribute13 trx_line_global_attr13 , ctlaf.global_attribute14 trx_line_global_attr14 , ctlaf.global_attribute15 trx_line_global_attr15 , ctlaf.global_attribute16 trx_line_global_attr16 , ctlaf.global_attribute17 trx_line_global_attr17 , ctlaf.global_attribute18 trx_line_global_attr18 , ctlaf.global_attribute19 trx_line_global_attr19 , ctlaf.global_attribute2 trx_line_global_attr2 , ctlaf.global_attribute20 trx_line_global_attr20 , ctlaf.global_attribute3 trx_line_global_attr3 , ctlaf.global_attribute4 trx_line_global_attr4 , ctlaf.global_attribute5 trx_line_global_attr5 , ctlaf.global_attribute6 trx_line_global_attr6 , ctlaf.global_attribute7 trx_line_global_attr7 , ctlaf.global_attribute8 trx_line_global_attr8 , ctlaf.global_attribute9 trx_line_global_attr9 , ctlaf.global_attribute_category trx_line_global_attr_cat , ctlaf.gross_unit_selling_price trx_line_gross_unit_sell_price , ctlaf.interface_line_attribute1 trx_line_interface_line_attr1 , ctlaf.interface_line_attribute10 trx_line_interface_line_attr10 , ctlaf.interface_line_attribute11 trx_line_interface_line_attr11 , ctlaf.interface_line_attribute12 trx_line_interface_line_attr12 , ctlaf.interface_line_attribute13 trx_line_interface_line_attr13 , ctlaf.interface_line_attribute14 trx_line_interface_line_attr14 , ctlaf.interface_line_attribute15 trx_line_interface_line_attr15 , ctlaf.interface_line_attribute2 trx_line_interface_line_attr2 , ctlaf.interface_line_attribute3 trx_line_interface_line_attr3 , ctlaf.interface_line_attribute4 trx_line_interface_line_attr4 , ctlaf.interface_line_attribute5 trx_line_interface_line_attr5 , ctlaf.interface_line_attribute6 trx_line_interface_line_attr6 , ctlaf.interface_line_attribute7 trx_line_interface_line_attr7 , ctlaf.interface_line_attribute8 trx_line_interface_line_attr8 , ctlaf.interface_line_attribute9 trx_line_interface_line_attr9 , ctlaf.interface_line_context trx_line_interface_line_ct , ctlaf.item_context trx_line_item_ct , ctlaf.line_number trx_line_line_number , ctlaf.line_type trx_line_line_type , ctlaf.quantity_credited trx_line_quantity_credited , ctlaf.quantity_invoiced trx_line_quantity_invoiced , ctlaf.quantity_ordered trx_line_quantity_ordered , ctlaf.reason_code trx_line_reason_code , ctlaf.rule_start_date trx_line_rule_start_date , ctlaf.sales_order trx_line_sales_order , ctlaf.sales_order_date trx_line_sales_order_date , ctlaf.sales_order_line trx_line_sales_order_line , ctlaf.sales_order_source trx_line_sales_order_source , Decode(ctlaf.line_type,'LINE',ctlaf.tax_exempt_flag,'TAX', parent_line.tax_exempt_flag, 'FREIGHT', parent_line.tax_exempt_flag,ctlaf.tax_exempt_flag) trx_line_tax_exempt_flag , ctlaf.tax_exempt_number trx_line_tax_exempt_number , ctlaf.tax_exempt_reason_code trx_line_tax_exmpt_reason_code , ctlaf.tax_precedence trx_line_tax_precedence , zx.tax_rate trx_line_tax_rate , ctlaf.translated_description trx_line_translated_desc , ctlaf.unit_selling_price trx_line_unit_selling_price , ctlaf.unit_standard_price trx_line_unit_standard_price , ctlaf.uom_code trx_line_uom_code , zx.account_class trx_line_dist_account_class , zx.TRX_LINE_DIST_TAX_AMT trx_line_dist_amt , NULL trx_line_dist_attr1 , NULL trx_line_dist_attr10 , NULL trx_line_dist_attr11 , NULL trx_line_dist_attr12 , NULL trx_line_dist_attr13 , NULL trx_line_dist_attr14 , NULL trx_line_dist_attr15 , NULL trx_line_dist_attr2 , NULL trx_line_dist_attr3 , NULL trx_line_dist_attr4 , NULL trx_line_dist_attr5 , NULL trx_line_dist_attr6 , NULL trx_line_dist_attr7 , NULL trx_line_dist_attr8 , NULL trx_line_dist_attr9 , NULL trx_line_dist_attr_cat , zx.ACCOUNT_STRING trx_line_dist_ccseg , nvl(zx.DEBIT_ACCOUNT_CCID, zx.CREDIT_ACCOUNT_CCID) trx_line_dist_ccid , zx.collected_tax_ccid trx_line_dist_col_tax_ccid , zx.collected_tax_concat_seg trx_line_dist_col_tax_ccseg ,/* ctlgdf.concatenated_segments trx_line_dist_ccseg , ctlgdf.code_combination_id trx_line_dist_ccid , ctlgdf.collected_tax_ccid trx_line_dist_col_tax_ccid , ctlgdf.collected_tax_concat_seg trx_line_dist_col_tax_ccseg ,*/ ctaf.ship_via carrier , ctaf.waybill_number shipping_reference , ctaf.ship_date_actual ship_date , NULL trx_line_dist_comments , zx.TAX_DIST_ID trx_line_dist_id , zx.gl_date trx_line_dist_gl_date, 100 trx_line_dist_percent , NULL trx_line_dist_rev_adj_class , l.paired_ccid trx_line_dist_paired_ccid , mlabf.global_attribute1 memo_line_global_attr1 , mlabf.global_attribute2 memo_line_global_attr2 , mlabf.global_attribute3 memo_line_global_attr3 , mlabf.global_attribute4 memo_line_global_attr4 , mlabf.global_attribute5 memo_line_global_attr5 , mlabf.global_attribute6 memo_line_global_attr6 , mlabf.global_attribute7 memo_line_global_attr7 , mlabf.global_attribute_category memo_line_global_attr_cat , refacc.REV_CCID memo_line_rev_ccid , mlabf.line_type memo_line_type , mlabf.name memo_line , Decode(ctlaf.line_type,'TAX', parent_mline.name, 'FREIGHT', parent_mline.name,mlabf.name) parent_memo_line , mlabf.memo_line_seq_id memo_line_seq_id , mlabf.tax_code memo_line_tax_code , mlabf.unit_std_price memo_line_unit_std_price , mlabf.uom_code memo_line_uom_code , rarlf.deferred_revenue_flag acct_rule_deferred_rev_flag , rarlf.description acct_rule_description , case when ctlaf.accounting_rule_id is NULL THEN NULL else gll.meaning end acct_rule_frequency , rarlf.name acct_rule_name , rarlf.occurrences acct_rule_occurrences , rarlf.rule_id acct_rule_id , rarlf.type acct_rule_type , rarlf1.name invoicing_rule , 'RA_CUST_TRX_LINE_GL_DIST_ALL' trx_distribution_type , l.event_id event_id , -ZX.TAX_DIST_ID line_number , l.language language , l.ledger_id ledger_id , pctlgd.account_class TRX_LINE_DIST_PAIRED_ACC_CLASS , ctlaf.tax_line_id trx_tax_line_id, l.customer_trx_id, ctlaf.product_type trx_product_type, nvl(pctlgd.rec_offset_flag,'N') , decode(ctlaf.interface_line_context, 'Joint Venture Invoice', to_number(nvl(ctlaf.interface_line_attribute6,'-1')),null) AR_JV_CODE_COMBINATION_ID, ctlaf.tax_invoice_number trx_tax_invoice_number, ctlaf.tax_invoice_date trx_tax_invoice_date FROM ar_xla_lines_extract l , ZX_TRX_TAX_DISTRIBUTIONS zx, ra_customer_trx_lines_all ctlaf , ra_customer_trx_lines_all parent_line , ar_memo_lines_all_vl mlabf , ar_memo_lines_all_vl parent_mline , ra_rules rarlf , ra_rules rarlf1 , ra_cust_trx_line_gl_dist_all pctlgd, AR_REF_ACCOUNTS_ALL refacc, ra_customer_trx_all ctaf, gl_ledgers lgr, gl_calendars cal, gl_lookups gll WHERE zx.TRX_LINE_DIST_ID = l.cust_trx_line_gl_dist_id AND zx.application_id = 222 AND ctaf.customer_trx_id = zx.trx_id AND zx.entity_code = 'TRANSACTIONS' AND zx.event_class_code ='INVOICE' AND l.event_id = zx.ACCOUNTING_EVENT_ID AND ctaf.customer_trx_id = zx.trx_id AND ctlaf.customer_trx_line_id(+) = l.customer_trx_line_id AND refacc.source_ref_account_id (+) = ctlaf.memo_line_seq_id AND refacc.SOURCE_REF_TABLE (+) = 'AR_MEMO_LINES_ALL_B' AND refacc.bu_id (+) = ctlaf.org_id AND l.level_flag = 'L' AND ctlaf.memo_line_seq_id = mlabf.memo_line_seq_id (+) AND ctlaf.accounting_rule_id = rarlf.rule_id (+) AND ctaf.invoicing_rule_id = rarlf1.rule_id (+) AND l.paire_dist_id = pctlgd.cust_trx_line_gl_dist_id(+) AND ctlaf.link_to_cust_trx_line_id = parent_line.customer_trx_line_id(+) AND parent_line.memo_line_seq_id = parent_mline.memo_line_seq_id (+) AND lgr.period_set_name = cal.period_set_name AND lgr.accounted_period_type = cal.period_type AND gll.lookup_type (+) = 'CAL_NON_ADJ_FREQ' AND gll.lookup_code (+) = cal.non_adj_period_freq_code AND lgr.ledger_id = ctaf.set_of_books_id

SQL_Statement

SELECT

/*+ INDEX(l AR_XLA_LINES_EXTRACT_N1) */

ctlaf.accounting_rule_duration trx_line_acct_rule_duration ,

ctlaf.amount_includes_tax_flag trx_line_amt_includes_tax_flag ,

ctlaf.attribute1 trx_line_attr1 ,

ctlaf.attribute10 trx_line_attr10 ,

ctlaf.attribute11 trx_line_attr11 ,

ctlaf.attribute12 trx_line_attr12 ,

ctlaf.attribute13 trx_line_attr13 ,

ctlaf.attribute14 trx_line_attr14 ,

ctlaf.attribute15 trx_line_attr15 ,

ctlaf.attribute2 trx_line_attr2 ,

ctlaf.attribute3 trx_line_attr3 ,

ctlaf.attribute4 trx_line_attr4 ,

ctlaf.attribute5 trx_line_attr5 ,

ctlaf.attribute6 trx_line_attr6 ,

ctlaf.attribute7 trx_line_attr7 ,

ctlaf.attribute8 trx_line_attr8 ,

ctlaf.attribute9 trx_line_attr9 ,

ctlaf.attribute_category trx_line_attr_cat ,

ctlaf.customer_trx_line_id trx_line_customer_trx_line_id ,

ctlaf.description trx_line_description ,

Decode(ctlaf.line_type,'TAX', parent_line.description, 'FREIGHT', parent_line.description,ctlaf.description) parent_trx_line_description ,

ctlaf.global_attribute1 trx_line_global_attr1 ,

ctlaf.global_attribute10 trx_line_global_attr10 ,

ctlaf.global_attribute11 trx_line_global_attr11 ,

ctlaf.global_attribute12 trx_line_global_attr12 ,

ctlaf.global_attribute13 trx_line_global_attr13 ,

ctlaf.global_attribute14 trx_line_global_attr14 ,

ctlaf.global_attribute15 trx_line_global_attr15 ,

ctlaf.global_attribute16 trx_line_global_attr16 ,

ctlaf.global_attribute17 trx_line_global_attr17 ,

ctlaf.global_attribute18 trx_line_global_attr18 ,

ctlaf.global_attribute19 trx_line_global_attr19 ,

ctlaf.global_attribute2 trx_line_global_attr2 ,

ctlaf.global_attribute20 trx_line_global_attr20 ,

ctlaf.global_attribute3 trx_line_global_attr3 ,

ctlaf.global_attribute4 trx_line_global_attr4 ,

ctlaf.global_attribute5 trx_line_global_attr5 ,

ctlaf.global_attribute6 trx_line_global_attr6 ,

ctlaf.global_attribute7 trx_line_global_attr7 ,

ctlaf.global_attribute8 trx_line_global_attr8 ,

ctlaf.global_attribute9 trx_line_global_attr9 ,

ctlaf.global_attribute_category trx_line_global_attr_cat ,

ctlaf.gross_unit_selling_price trx_line_gross_unit_sell_price ,

ctlaf.interface_line_attribute1 trx_line_interface_line_attr1 ,

ctlaf.interface_line_attribute10 trx_line_interface_line_attr10 ,

ctlaf.interface_line_attribute11 trx_line_interface_line_attr11 ,

ctlaf.interface_line_attribute12 trx_line_interface_line_attr12 ,

ctlaf.interface_line_attribute13 trx_line_interface_line_attr13 ,

ctlaf.interface_line_attribute14 trx_line_interface_line_attr14 ,

ctlaf.interface_line_attribute15 trx_line_interface_line_attr15 ,

ctlaf.interface_line_attribute2 trx_line_interface_line_attr2 ,

ctlaf.interface_line_attribute3 trx_line_interface_line_attr3 ,

ctlaf.interface_line_attribute4 trx_line_interface_line_attr4 ,

ctlaf.interface_line_attribute5 trx_line_interface_line_attr5 ,

ctlaf.interface_line_attribute6 trx_line_interface_line_attr6 ,

ctlaf.interface_line_attribute7 trx_line_interface_line_attr7 ,

ctlaf.interface_line_attribute8 trx_line_interface_line_attr8 ,

ctlaf.interface_line_attribute9 trx_line_interface_line_attr9 ,

ctlaf.interface_line_context trx_line_interface_line_ct ,

ctlaf.item_context trx_line_item_ct ,

ctlaf.line_number trx_line_line_number ,

ctlaf.line_type trx_line_line_type ,

ctlaf.quantity_credited trx_line_quantity_credited ,

ctlaf.quantity_invoiced trx_line_quantity_invoiced ,

ctlaf.quantity_ordered trx_line_quantity_ordered ,

ctlaf.reason_code trx_line_reason_code ,

ctlaf.rule_start_date trx_line_rule_start_date ,

ctlaf.sales_order trx_line_sales_order ,

ctlaf.sales_order_date trx_line_sales_order_date ,

ctlaf.sales_order_line trx_line_sales_order_line ,

ctlaf.sales_order_source trx_line_sales_order_source ,

Decode(ctlaf.line_type,'LINE',ctlaf.tax_exempt_flag,'TAX', parent_line.tax_exempt_flag, 'FREIGHT', parent_line.tax_exempt_flag,ctlaf.tax_exempt_flag) trx_line_tax_exempt_flag ,

ctlaf.tax_exempt_number trx_line_tax_exempt_number ,

ctlaf.tax_exempt_reason_code trx_line_tax_exmpt_reason_code ,

ctlaf.tax_precedence trx_line_tax_precedence ,

ctlaf.tax_rate trx_line_tax_rate ,

ctlaf.translated_description trx_line_translated_desc ,

ctlaf.unit_selling_price trx_line_unit_selling_price ,

ctlaf.unit_standard_price trx_line_unit_standard_price ,

ctlaf.uom_code trx_line_uom_code ,

ctlgdf.account_class trx_line_dist_account_class ,

ctlgdf.amount trx_line_dist_amt ,

ctlgdf.attribute1 trx_line_dist_attr1 ,

ctlgdf.attribute10 trx_line_dist_attr10 ,

ctlgdf.attribute11 trx_line_dist_attr11 ,

ctlgdf.attribute12 trx_line_dist_attr12 ,

ctlgdf.attribute13 trx_line_dist_attr13 ,

ctlgdf.attribute14 trx_line_dist_attr14 ,

ctlgdf.attribute15 trx_line_dist_attr15 ,

ctlgdf.attribute2 trx_line_dist_attr2 ,

ctlgdf.attribute3 trx_line_dist_attr3 ,

ctlgdf.attribute4 trx_line_dist_attr4 ,

ctlgdf.attribute5 trx_line_dist_attr5 ,

ctlgdf.attribute6 trx_line_dist_attr6 ,

ctlgdf.attribute7 trx_line_dist_attr7 ,

ctlgdf.attribute8 trx_line_dist_attr8 ,

ctlgdf.attribute9 trx_line_dist_attr9 ,

ctlgdf.attribute_category trx_line_dist_attr_cat ,

ctlgdf.concatenated_segments trx_line_dist_ccseg ,

ctlgdf.code_combination_id trx_line_dist_ccid ,

ctlgdf.collected_tax_ccid trx_line_dist_col_tax_ccid ,

ctlgdf.collected_tax_concat_seg trx_line_dist_col_tax_ccseg ,

ctaf.ship_via carrier ,

ctaf.waybill_number shipping_reference ,

ctaf.ship_date_actual ship_date ,

ctlgdf.comments trx_line_dist_comments ,

l.cust_trx_line_gl_dist_id trx_line_dist_id ,

ctlgdf.gl_date trx_line_dist_gl_date ,

ctlgdf.percent trx_line_dist_percent ,

ctlgdf.rev_adj_class_temp trx_line_dist_rev_adj_class ,

l.paired_ccid trx_line_dist_paired_ccid ,

mlabf.global_attribute1 memo_line_global_attr1 ,

mlabf.global_attribute2 memo_line_global_attr2 ,

mlabf.global_attribute3 memo_line_global_attr3 ,

mlabf.global_attribute4 memo_line_global_attr4 ,

mlabf.global_attribute5 memo_line_global_attr5 ,

mlabf.global_attribute6 memo_line_global_attr6 ,

mlabf.global_attribute7 memo_line_global_attr7 ,

mlabf.global_attribute_category memo_line_global_attr_cat ,

refacc.REV_CCID memo_line_rev_ccid ,

mlabf.line_type memo_line_type ,

mlabf.name memo_line ,

Decode(ctlaf.line_type,'TAX', parent_mline.name, 'FREIGHT', parent_mline.name,mlabf.name) parent_memo_line ,

mlabf.memo_line_seq_id memo_line_seq_id ,

mlabf.tax_code memo_line_tax_code ,

mlabf.unit_std_price memo_line_unit_std_price ,

mlabf.uom_code memo_line_uom_code ,

rarlf.deferred_revenue_flag acct_rule_deferred_rev_flag ,

rarlf.description acct_rule_description ,

case when ctlaf.accounting_rule_id is NULL THEN NULL

else gll.meaning

end acct_rule_frequency ,

rarlf.name acct_rule_name ,

rarlf.occurrences acct_rule_occurrences ,

rarlf.rule_id acct_rule_id ,

rarlf.type acct_rule_type ,

rarlf1.name invoicing_rule ,

'RA_CUST_TRX_LINE_GL_DIST_ALL' trx_distribution_type ,

l.event_id event_id ,

l.line_number line_number ,

l.language language ,

l.ledger_id ledger_id ,

pctlgd.account_class TRX_LINE_DIST_PAIRED_ACC_CLASS ,

ctlaf.tax_line_id trx_tax_line_id,

ctlgdf.customer_trx_id,

ctlaf.product_type trx_product_type,

nvl(ctlgdf.rec_offset_flag,'N'),

decode(ctlaf.interface_line_context, 'Joint Venture Invoice',

to_number(nvl(ctlaf.interface_line_attribute6,'-1')),null) AR_JV_CODE_COMBINATION_ID,

ctlaf.tax_invoice_number trx_tax_invoice_number,

ctlaf.tax_invoice_date trx_tax_invoice_date

FROM ar_xla_lines_extract l ,

ra_cust_trx_line_gl_dist_all ctlgdf ,

ra_customer_trx_lines_all ctlaf ,

ra_customer_trx_lines_all parent_line ,

ar_memo_lines_all_vl mlabf ,

ar_memo_lines_all_vl parent_mline ,

ra_rules rarlf ,

ra_rules rarlf1 ,

ra_cust_trx_line_gl_dist_all pctlgd,

AR_REF_ACCOUNTS_ALL refacc,

ra_customer_trx_all ctaf,

gl_ledgers lgr,

gl_calendars cal,

gl_lookups gll

WHERE ctlgdf.cust_trx_line_gl_dist_id = l.cust_trx_line_gl_dist_id

AND ctaf.customer_trx_id = ctlgdf.customer_trx_id

AND ctlaf.customer_trx_line_id(+) = l.customer_trx_line_id

AND refacc.source_ref_account_id (+) = ctlaf.memo_line_seq_id

AND refacc.SOURCE_REF_TABLE (+) = 'AR_MEMO_LINES_ALL_B'

AND refacc.bu_id (+) = ctlaf.org_id

AND l.level_flag = 'L'

AND ctlaf.memo_line_seq_id = mlabf.memo_line_seq_id (+)

AND ctlaf.accounting_rule_id = rarlf.rule_id (+)

AND ctaf.invoicing_rule_id = rarlf1.rule_id (+)

AND l.paire_dist_id = pctlgd.cust_trx_line_gl_dist_id(+)

AND ctlaf.link_to_cust_trx_line_id = parent_line.customer_trx_line_id(+)

AND parent_line.memo_line_seq_id = parent_mline.memo_line_seq_id (+)

AND lgr.period_set_name = cal.period_set_name

AND lgr.accounted_period_type = cal.period_type

AND gll.lookup_type (+) = 'CAL_NON_ADJ_FREQ'

AND gll.lookup_code (+) = cal.non_adj_period_freq_code

AND lgr.ledger_id = ctaf.set_of_books_id

UNION ALL