Journal Voucher Report for China Secondary Ledger Report

WITH SECURITY_TBL AS
(
SELECT DISTINCT GL.LEDGER_ID
FROM 
GL_LEDGERS GL,
GL_ACCESS_SETS GAS,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU

WHERE 
UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GAS.ACCESS_SET_ID=GL.IMPLICIT_ACCESS_SET_ID
	AND (GAS.ACCESS_SET_ID =FURDA.ACCESS_SET_ID OR GL.LEDGER_ID=FURDA.LEDGER_ID)
	AND UPPER(FURDA.role_name) in ('XXC_GL_INQUIRY_JOB','XXC_GENERAL_ACCOUNTANT_JOB','XXC_GENERAL_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND  UPPER(PU.USERNAME) = UPPER(:xdo_user_name)
	AND PU.USER_GUID = FURDA.USER_GUID

)

SELECT DISTINCT

  ffvv_entity_tl.description legal_entity_name
 ,gjh.period_name accounting_period
 ,gjh.default_effective_date accounting_date
 ,gjh.currency_conversion_rate exchange_rate
 ,gjl.currency_code currency
 ,gjsv.user_je_source_name journal_source
 ,gjc.user_je_category_name journal_category
 ,gjh.posting_acct_seq_value acc_seq_num
 ,gjl.je_line_num line_number
 --,xal.description journal_line_desc
 ,(CASE WHEN gjsv.je_source_name='Payables' THEN
		    (CASE WHEN gjc.je_category_name IN ('Debit Memos','Credit Memos','Purchase Invoices')
				  THEN (xal.description||'-'||(SELECT DISTINCT vendor_name FROM poz_suppliers_v poz WHERE xal.party_id=poz.vendor_id)||'-'||xte.transaction_number)
				  WHEN GJC.je_category_name IN ('Payments','Reconciled Payments')
				  THEN (xte.transaction_number||'-'||(SELECT DISTINCT ac.vendor_name FROM ap_checks_all ac WHERE xte.source_id_int_1=ac.check_id)) 
			END) 
	    WHEN gjsv.je_source_name='Receivables' THEN 
		    (CASE WHEN gjc.je_category_name IN ('Sales Invoices','Debit Memos','Credit Memos','Adjustment') 
				  THEN ((SELECT DISTINCT party_name FROM hz_parties hp, hz_cust_accounts hca WHERE xal.party_id = hca.cust_account_id AND hca.party_id =hp.party_id)||'-'||xte.transaction_number)
				  WHEN gjc.je_category_name IN ('Receipts','Misc Receipts')
				  THEN (xte.transaction_number||'-'||(SELECT DISTINCT party_name FROM hz_parties HP, hz_cust_accounts HCA WHERE XAL.party_id = HCA.cust_account_id AND HCA.party_id =HP.party_id)) 
			END)
	    WHEN gjsv.je_source_name='Assets' THEN gjc.user_je_category_name 
	    WHEN gjsv.je_source_name='Project Accounting' THEN xal.description
	    WHEN gjsv.je_source_name='Revaluation' THEN xah.description 
 ELSE gjl.description 
 END) journal_line_desc
 ,gcc.segment4 account
 ,ffvv_account_tl.description account_description
 ,NVL(gjl.entered_dr,0)+NVL(gjl.entered_cr,0) entered_currency_amt
 ,gjl.accounted_dr acc_debit_amt
 ,gjl.accounted_cr acc_credit_amt
 ,gjal_created.user_id journal_preparer
 ,gjal_approved.user_id journal_approver

FROM 

  gl_je_batches gjb,
  gl_je_headers gjh,
  gl_je_lines gjl,
  gl_periods gp, 
  gl_ledgers gll,
  gl_ledger_relationships glr, 
  gl_code_combinations gcc,
  fnd_kf_str_instances_vl fksiv, 
  fnd_flex_values ffvv_entity,
  fnd_flex_values ffvv_account,
  fnd_flex_values_tl ffvv_entity_tl,
  fnd_flex_values_tl ffvv_account_tl,
  gl_je_categories_tl gjc,
  gl_je_sources_tl gjsv,
  gl_je_action_log gjal_created,
  gl_je_action_log gjal_approved,
  gl_import_references gir,
  xla_ae_lines xal,
  xla_ae_headers xah,
  xla_transaction_entities 	xte,
  SECURITY_TBL ST

WHERE 

1=1
AND ST.LEDGER_ID=GLL.LEDGER_ID
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.status = 'P'
AND gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = gll.ledger_id 
AND gjl.ledger_id = gll.ledger_id  -- newly added
AND gjh.period_name = gp.period_name 
AND gp.period_set_name=gll.period_set_name 
AND gll.ledger_id = glr.target_ledger_id
AND gjh.ledger_id = glr.target_ledger_id 
AND gjl.ledger_id = glr.target_ledger_id  
AND gll.ledger_category_code = glr.target_ledger_category_code 
AND glr.application_id = 101
AND glr.target_ledger_category_code = 'SECONDARY'
AND gcc.code_combination_id = gjl.code_combination_id 
AND gcc.chart_of_accounts_id = fksiv.structure_instance_number 
AND fksiv.application_id  = 101
AND fksiv.key_flexfield_code = 'GL#'
AND ffvv_entity.flex_value = gcc.segment1
AND ffvv_entity.value_category = 'ENTITY VALUE SET'
AND ffvv_entity.flex_value_id = ffvv_entity_tl.flex_value_id
AND ffvv_entity_tl.LANGUAGE	= 'ZHS'
AND ffvv_account.flex_value = gcc.segment4
AND ffvv_account.value_category IN ('ACCOUNT VALUE SET','CHINA SER SYS ACCOUNT VALUE SET','CHINA MKTG STD ACCOUNT VALUE SET','CHINA MFR SYS ACCOUNT VALUE SET','CHINA MFR STD ACCOUNT VALUE SET') 
AND GLL.NAME IN ('SL CHINA CNY LOCAL MKTG STD', 'SL CHINA CNY LOCAL MFR STD', 'SL CHINA CNY LOCAL SER STD', 'SL CHINA CNY LOCAL SER SYS', 'SL CHINA CNY LOCAL MFR SYS' )
AND ffvv_account.flex_value_id = ffvv_account_tl.flex_value_id
AND ffvv_account_tl.LANGUAGE = 'ZHS'
AND gjh.je_source = gjsv.je_source_name (+)
AND gjsv.LANGUAGE = 'ZHS'
AND gjh.je_category = gjc.je_category_name (+)
AND gjc.LANGUAGE = 'ZHS'
AND gjal_created.action_code (+) ='CREATED'
AND gjal_created.je_batch_id (+) = gjh.je_batch_id
AND gjal_approved.action_code (+) ='APPROVED'
AND gjal_approved.je_batch_id (+) = gjh.je_batch_id
AND gir.je_line_num (+) = gjl.je_line_num
AND gir.je_header_id (+) = gjl.je_header_id
AND xal.gl_sl_link_id (+) = gir.gl_sl_link_id
AND xal.gl_sl_link_table (+) = gir.gl_sl_link_table
AND xah.ae_header_id (+) = xal.ae_header_id
and gll.ledger_id = xah.ledger_id (+) 
and xah.application_id = xte.application_id(+)
and xah.entity_id = xte.entity_id(+)

-------parameter section----
AND gjh.period_name BETWEEN :p_period_name_from AND :p_period_name_to
AND NVL(gjh.posting_acct_seq_value,-1) BETWEEN COALESCE(CAST(:p_acc_seq_num_from AS NUMERIC),gjh.posting_acct_seq_value,-1) AND COALESCE(CAST(:p_acc_seq_num_to AS NUMERIC),gjh.posting_acct_seq_value,-1)
AND fksiv.name = :p_chart_of_acc
AND ffvv_entity.flex_value = :p_legal_entity
AND gll.name = :p_ledger_name
AND (gjsv.user_je_source_name  in  (:p_journal_source) or 'All' IN (:p_journal_source||'All')) 
-----------------------------

ORDER BY
 gjh.posting_acct_seq_value asc
,gjh.default_effective_date