VIEW: APPS.GMF_LC_ADJ_DETAILS_V

Source

View Text - Preformatted

SELECT NVL(sl.parent_ship_line_id,sl.ship_line_id) parent_ship_line_id, a.adjustment_num, DECODE(a.adjustment_num,cl.adjustment_num,'Y', DECODE(a.adjustment_num,tl.adjustment_num,'Y', DECODE(sl.parent_ship_line_id,sl2.parent_ship_line_id, DECODE(a.adjustment_num,sl.adjustment_num,'Y','N'), DECODE(a.adjustment_num,sl2.adjustment_num,'Y','N')))) AS current_adjustment_flag, a.ship_header_id, sl.ship_line_group_id, a.ship_line_id, m.match_id, m.adj_group_date, m.from_parent_table_name, m.from_parent_table_id, i.invoice_num, i.invoice_date, i.party_id, i.party_site_id, il.line_number invoice_line_number, id.invoice_distribution_id, id.amount invoice_distr_amt, i.invoice_currency_code FROM ap_invoices_all i, ap_invoice_lines_all il, ap_invoice_distributions_all id, inl_matches m, inl_ship_lines_all sl2, inl_tax_lines tl, inl_charge_lines cl, inl_ship_lines_all slp, inl_ship_lines_all sl, inl_allocations a WHERE i.invoice_id = il.invoice_id AND il.invoice_id = id.invoice_id AND il.line_number = id.invoice_line_number AND id.invoice_distribution_id = m.from_parent_table_id AND (m.match_amount_id = DECODE(a.from_parent_table_name,'INL_CHARGE_LINES',cl.match_amount_id, 'INL_TAX_LINES',tl.match_amount_id, 'INL_SHIP_LINES',NULL,NULL) OR m.match_id = DECODE(a.from_parent_table_name,'INL_CHARGE_LINES',NULL, 'INL_TAX_LINES',NULL, 'INL_SHIP_LINES',DECODE(sl.parent_ship_line_id,sl2.parent_ship_line_id,sl.match_id,sl2.match_id),NULL)) AND (sl.adjustment_num <= a.adjustment_num) AND sl2.ship_line_id (+) = DECODE(a.from_parent_table_name,'INL_SHIP_LINES',a.from_parent_table_id,NULL) AND tl.tax_line_id (+) = DECODE(a.from_parent_table_name,'INL_TAX_LINES',a.from_parent_table_id,NULL) AND cl.charge_line_id (+) = DECODE(a.from_parent_table_name,'INL_CHARGE_LINES',a.from_parent_table_id,NULL) AND sl.parent_ship_line_id = slp.parent_ship_line_id AND (slp.adjustment_num = sl.adjustment_num OR sl2.adjustment_num IS NOT NULL) AND slp.ship_line_id = a.ship_line_id AND a.landed_cost_flag = 'Y'

View Text - HTML Formatted

SELECT NVL(SL.PARENT_SHIP_LINE_ID
, SL.SHIP_LINE_ID) PARENT_SHIP_LINE_ID
, A.ADJUSTMENT_NUM
, DECODE(A.ADJUSTMENT_NUM
, CL.ADJUSTMENT_NUM
, 'Y'
, DECODE(A.ADJUSTMENT_NUM
, TL.ADJUSTMENT_NUM
, 'Y'
, DECODE(SL.PARENT_SHIP_LINE_ID
, SL2.PARENT_SHIP_LINE_ID
, DECODE(A.ADJUSTMENT_NUM
, SL.ADJUSTMENT_NUM
, 'Y'
, 'N')
, DECODE(A.ADJUSTMENT_NUM
, SL2.ADJUSTMENT_NUM
, 'Y'
, 'N')))) AS CURRENT_ADJUSTMENT_FLAG
, A.SHIP_HEADER_ID
, SL.SHIP_LINE_GROUP_ID
, A.SHIP_LINE_ID
, M.MATCH_ID
, M.ADJ_GROUP_DATE
, M.FROM_PARENT_TABLE_NAME
, M.FROM_PARENT_TABLE_ID
, I.INVOICE_NUM
, I.INVOICE_DATE
, I.PARTY_ID
, I.PARTY_SITE_ID
, IL.LINE_NUMBER INVOICE_LINE_NUMBER
, ID.INVOICE_DISTRIBUTION_ID
, ID.AMOUNT INVOICE_DISTR_AMT
, I.INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL I
, AP_INVOICE_LINES_ALL IL
, AP_INVOICE_DISTRIBUTIONS_ALL ID
, INL_MATCHES M
, INL_SHIP_LINES_ALL SL2
, INL_TAX_LINES TL
, INL_CHARGE_LINES CL
, INL_SHIP_LINES_ALL SLP
, INL_SHIP_LINES_ALL SL
, INL_ALLOCATIONS A
WHERE I.INVOICE_ID = IL.INVOICE_ID
AND IL.INVOICE_ID = ID.INVOICE_ID
AND IL.LINE_NUMBER = ID.INVOICE_LINE_NUMBER
AND ID.INVOICE_DISTRIBUTION_ID = M.FROM_PARENT_TABLE_ID
AND (M.MATCH_AMOUNT_ID = DECODE(A.FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, CL.MATCH_AMOUNT_ID
, 'INL_TAX_LINES'
, TL.MATCH_AMOUNT_ID
, 'INL_SHIP_LINES'
, NULL
, NULL) OR M.MATCH_ID = DECODE(A.FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, NULL
, 'INL_TAX_LINES'
, NULL
, 'INL_SHIP_LINES'
, DECODE(SL.PARENT_SHIP_LINE_ID
, SL2.PARENT_SHIP_LINE_ID
, SL.MATCH_ID
, SL2.MATCH_ID)
, NULL))
AND (SL.ADJUSTMENT_NUM <= A.ADJUSTMENT_NUM)
AND SL2.SHIP_LINE_ID (+) = DECODE(A.FROM_PARENT_TABLE_NAME
, 'INL_SHIP_LINES'
, A.FROM_PARENT_TABLE_ID
, NULL)
AND TL.TAX_LINE_ID (+) = DECODE(A.FROM_PARENT_TABLE_NAME
, 'INL_TAX_LINES'
, A.FROM_PARENT_TABLE_ID
, NULL)
AND CL.CHARGE_LINE_ID (+) = DECODE(A.FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, A.FROM_PARENT_TABLE_ID
, NULL)
AND SL.PARENT_SHIP_LINE_ID = SLP.PARENT_SHIP_LINE_ID
AND (SLP.ADJUSTMENT_NUM = SL.ADJUSTMENT_NUM OR SL2.ADJUSTMENT_NUM IS NOT NULL)
AND SLP.SHIP_LINE_ID = A.SHIP_LINE_ID
AND A.LANDED_COST_FLAG = 'Y'