Split the journal tracing view 3-from the purchase credential type to the purchase receipt

The source certificate type is purchase receipt (View: XLA_PO_AEL_GL_V)

Select gjh. je_source,

Gjh. name,

Gjh.doc _ sequence_value,

Xag. vendor_id,

Xag. vendor_name, -- Supplier name

Xag. vendor_site_id,

Xag. vendor_site_code vendor_address, -- supplier location

Null gl_desc,

'Purchase receipt \ return to supplier 'invoice_desc, -- invoice description (Abstract)

Gjh. default_1_tive_date, -- GL date

-- Xag. accounting_date,

Gcc. segment4, -- Subject

Nvl (xag. entered_dr, 0) dr_quantity, -- borrower

Nvl (xag. entered_cr, 0) cr_quantity, -- lender

From gl. gl_je_headers gjh,

Gl. gl_je_lines gjl,

(Select 1, 201,

Poh.org _ id,

Rrs. code_combination_id,

Rrs. entered_dr,

Rrs. entered_cr,

Rrs. accounted_dr,

Rrs. accounted_cr,

Rrs. accounting_date,

-- RRS. CURRENCY_CONVERSION_DATE,

Pov. vendor_id,

Pov. vendor_name,

Poh. vendor_site_id,

Pvs. vendor_site_code,

Rrs. accounting_date,

-- NVL (RRS. SOURCE_DOC_QUANTITY, NVL (RDBMS. SOURCE_DOC_QUANTITY, 0 )),

-- NVL (PLL. PRICE_OVERRIDE, POL. UNIT_PRICE ),

R. je_header_id,

R. je_line_num

From gl_import_references r,

Rcv_receiving_sub_ledger rrs,

Po_distributions_all pod,

Po_line_locations_all pll,

Po_lines_all pol,

Po_headers_all poh,

Po_vendors pov,

Po_vendor_sites_all pvs

Where pov. vendor_id = poh. vendor_id

And pvs. vendor_site_id = poh. vendor_site_id

And nvl (pvs.org _ id,-99) = nvl (poh.org _ id,-99)

And poh. po_header_id = pol. po_header_id

And pol. po_line_id = pll. po_line_id

And pll. line_location_id = pod. line_location_id

And pod. po_distribution_id = rrs. referenpushed

And r. gl_sl_link_table = 'rsl'

And rrs. gl_sl_link_id = r. gl_sl_link_id

And rrs. rcv_transaction_id = r. reference_5) xag,

-- XLA_PO_AEL_GL_V xag, -- Procurement source tracing View

Rcv_vrc_txs_vendint_v rvh, Which is traced back to the purchase order table

Gl_code_combinations_kfv gcc

Where gjh. je_source = 'purchasing'

And gjl. je_header_id = gjh. je_header_id

And gjh. status = 'P'

And gjl. code_combination_id = gcc. code_combination_id

And gcc. segment4 = '000000' -- no ticket payable

And gcc. segment2 = nvl (p_syb_name, gcc. segment2)-Business Department

And xag. vendor_id = nvl (p_vendor_name, xag. vendor_id)

And xag. vendor_site_id = nvl (p_vendor_site, xag. vendor_site_id)

And gjh. period_name between '2014-01 'and '2014-01'

And xag. je_header_id = gjl. je_header_id

And xag. je_line_num = gjl. je_line_num

And xag. rcv_receipt_num = rvh. order_num -- purchase order number