Audit Report for France

WITH RECEIVABLES_TBL AS
(
/* inv, credit memo, debit memo */
SELECT HP.party_name cust
  , HCA.account_number cust_acc_num
  ,  RCTA.customer_trx_id cust_id
  , 'TRANSACTIONS' ENTITY_CODE
FROM ra_customer_trx_all RCTA
  , hz_parties HP
  , hz_cust_accounts HCA
WHERE 1=1
  AND RCTA.bill_to_customer_id   = HCA.cust_account_id
  AND HCA.party_id = HP.party_id
  
UNION 
/* Receipts*/ 
SELECT HP.party_name cust
  , HCA.account_number cust_acc_num
  , ACRA.cash_receipt_id cust_id
  , 'RECEIPTS' ENTITY_CODE
FROM ar_cash_receipts_all ACRA
  , hz_parties HP
  , hz_cust_accounts HCA
WHERE 1=1
  AND ACRA.pay_from_customer = HCA.cust_account_id
  AND HCA.party_id = HP.party_id
  
UNION 
/* Adjustments */
SELECT HP.party_name cust
  , HCA.account_number cust_acc_num
  --,  RCTA.customer_trx_id 
  , adj.adjustment_id cust_id
  , 'ADJUSTMENTS' ENTITY_CODE
FROM ar_adjustments_all adj
, ar_payment_schedules_all ps
, ra_customer_trx_all RCTA
, hz_cust_accounts HCA
, hz_parties HP
WHERE 1=1
and adj.payment_schedule_id = ps.payment_schedule_id
and ps.customer_trx_id = RCTA.customer_trx_id
AND RCTA.bill_to_customer_id   = HCA.cust_account_id
AND HCA.party_id = HP.party_id
)

SELECT NVL(FLVSRC.MEANING, GJS.USER_JE_SOURCE_NAME)|| ' ' ||NVL(FLVCAT.MEANING, GJC.USER_JE_CATEGORY_NAME) AS "JournalCode"
, NVL(FLVCAT.MEANING, GJC.USER_JE_CATEGORY_NAME) AS "JournalLib"
, NVL(XAH.close_acct_seq_value , GJH.close_acct_seq_value) AS "EcritureNum"
, TO_CHAR(GJH.default_effective_date, 'YYYYMMDD')  AS "EcritureDate"
, GCC.segment4                                     AS "CompteNum"
--, gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.segment4) seg_desc

/* CompteLib in French, fallback to base function if no translation */
, NVL(fvt_fr.description, gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.segment4)) "CompteLib"

 /* Supplier or Customer Number */
, (CASE WHEN UPPER(gjh.je_source) IN ('PAYABLES') 
         THEN aps.segment1
		WHEN UPPER(gjh.je_source) IN ('RECEIVABLES') 
		 THEN RT.cust_acc_num
		ELSE NULL END) "CompAuxNum"

  /* Supplier or Customer Name */
, (CASE WHEN UPPER(gjh.je_source) IN ('PAYABLES') 
         THEN aps.vendor_name
		WHEN UPPER(gjh.je_source) IN ('RECEIVABLES') 
		 THEN RT.cust
		ELSE NULL END) "CompAuxLib"

  /* Invoice Number */	
, (CASE WHEN UPPER(gjh.je_source) IN ('PAYABLES','RECEIVABLES') 
         THEN XTE.transaction_number
		ELSE NULL END) "PieceRef"

   /* Subledger Line Description */
, NVL(XAL.description, gjh.name) "EcritureLib"
			
   /* Amounts (European format) */
, CASE WHEN NVL(XAL.accounted_cr, gjl.accounted_cr) > 0 THEN NULL
     ELSE TO_CHAR(NVL(XAL.accounted_dr, gjl.accounted_dr), 'FM9999999990D00',
            'NLS_NUMERIC_CHARACTERS='',.''') END AS "DebitAmount" 
			
, CASE WHEN NVL(XAL.accounted_dr, gjl.accounted_dr) > 0 THEN NULL
   ELSE TO_CHAR(NVL(XAL.accounted_cr, gjl.accounted_cr), 'FM9999999990D00',
            'NLS_NUMERIC_CHARACTERS='',.''')END AS "CreditAmount"			
    
/*, CASE
  WHEN gl.currency_code <> gjl.currency_code THEN
    NVL(
      TO_CHAR(gjl.entered_dr, 'FM999999999D00',
              'NLS_NUMERIC_CHARACTERS='',.'''),
      '-' || TO_CHAR(gjl.entered_cr, 'FM999999999D00',
                     'NLS_NUMERIC_CHARACTERS='',.''')
    )
  ELSE NULL
END 
AS Montantdevise */

, CASE
  WHEN gl.currency_code <> gjl.currency_code THEN
    NVL(
      TO_CHAR(NVL(XAL.entered_dr, gjl.entered_dr), 'FM9999999990D00',
              'NLS_NUMERIC_CHARACTERS='',.''')
	 ,
      TO_CHAR(NVL(XAL.entered_cr,gjl.entered_cr)*-1, 'FM9999999990D00',
                     'NLS_NUMERIC_CHARACTERS='',.''')
    )
  ELSE NULL
END  AS "Montantdevise" 

/* Reconciliation Info */
, NVL(recon.jgzz_recon_ref, TO_CHAR(recon.jgzz_recon_id)) AS "EcritureLet"

, TO_CHAR(gjh.default_effective_date, 'YYYYMMDD') AS "PieceDate"
, TO_CHAR(recon.jgzz_recon_date,    'YYYYMMDD')   AS "DateLet"
, TO_CHAR(gjl.effective_date,       'YYYYMMDD')   AS "ValidDate"
, CASE WHEN gl.currency_code <> gjl.currency_code 
        THEN gjl.currency_code  ELSE NULL END AS "Idevise"
	
	
/* , gjb.name as "batch_name"
, gjh.name as "journal_name"
, gjh.period_name "accounting_period"
, gps.effective_period_num
, gl.currency_code "accounter_curr"
, gjl.currency_code "entered_curr"
, gjl.accounted_dr "gl_acc_dr"
, XAL.accounted_dr "xl_acc_dr"
, gjl.accounted_cr "gl_acc_cr"
, XAL.accounted_cr "xl_acc_cr"
, gjl.entered_dr "gl_ent_dr"
, XAL.entered_dr "xl_ent_dr"
, gjl.entered_cr "gl_ent_cr"
, XAL.entered_cr "xl_ent_cr"
, gjl.EFFECTIVE_DATE "accounting_date"
, gl.name "ledger_name" */

/* , xte.entity_code
, xte.source_id_int_1 
, RT.cust "cust_name"
, RT.cust_acc_num "cust_acc"
, aps.vendor_name AS "supplier_name"
, aps.segment1 AS "supplier_number" */
		
FROM gl_je_batches GJB
  /* , gl_je_sources_b GJS
  , gl_je_sources_tl GJST
  , gl_je_categories_b GJC
  , gl_je_categories_tl GJCT */
  , GL_JE_SOURCES gjs
  , GL_JE_CATEGORIES gjc
  , gl_je_headers GJH
  , gl_je_lines GJL
  , gl_code_combinations GCC
  , gl_je_lines_recon recon
  , gl_ledgers GL
  , gl_period_statuses GPS
  , gl_periods GP
  , xle_entity_profiles  xep 
  
  , fnd_id_flex_segments idfs
  , fnd_flex_values fv
  , fnd_flex_values_tl fvt_fr
  
  , gl_import_references GIR
  , xla_ae_headers XAH
  , xla_ae_lines XAL
  , xla_transaction_entities XTE
  
  , RECEIVABLES_TBL RT
  , ap_invoices_all aia  -- invoices
  , ap_checks_all ac   -- payments 
  , poz_suppliers_v aps
  
  , fnd_lookup_values FLVSRC  -- lookup for source
  , fnd_lookup_values FLVCAT -- lookup for category

WHERE 1 = 1
  AND GJB.je_source   = GJS.je_source_name
  AND GJB.je_batch_id = GJH.je_batch_id
  AND GJH.je_category = GJC.je_category_name
  AND GJH.je_header_id = GJL.je_header_id
  AND GJL.code_combination_id = GCC.code_combination_id
  AND GJL.ledger_id = GL.ledger_id
  
  /* AND GJS.JE_SOURCE_NAME = GJST.JE_SOURCE_NAME
  AND UPPER(GJST.LANGUAGE) = 'F'
  AND GJC.JE_CATEGORY_NAME = GJCT.JE_CATEGORY_NAME
  AND UPPER(GJCT.LANGUAGE) = 'F' */
  
  AND GJL.je_header_id = recon.je_header_id(+)
  AND GJL.je_line_num  = recon.je_line_num(+)
  
  /* GL Period Statuses for effective_period_num-based filtering */

   AND gps.period_name    = gjh.period_name
   AND gps.ledger_id      = gjh.ledger_id 
   AND GL.ledger_id = GPS.set_of_books_id
   AND gps.application_id = 101   /* GL */
  
/* --- Flex value joins for French description of Segment 4 --- */ 
  AND idfs.application_id(+) = 101        -- GL
  AND idfs.id_flex_code(+)   = 'GL#'      -- Accounting KFF
  AND idfs.id_flex_num(+)    = gcc.chart_of_accounts_id
  AND idfs.segment_num(+)    = 4          -- Segment 4
  
  AND fv.flex_value_set_id(+) = idfs.flex_value_set_id
  AND fv.flex_value(+)        = gcc.segment4
  
  AND fvt_fr.flex_value_id(+) = fv.flex_value_id
  AND fvt_fr.language(+)      = 'F'      -- use 'F' if needed
/* --- End flex joins --- */

  AND gp.period_set_name = gl.period_set_name
  AND gjh.period_name  = gp.period_name
  AND gjh.legal_entity_id = xep.legal_entity_id
  AND gjh.status = 'P'
  
 AND gps.effective_period_num BETWEEN
        NVL(:p_from_period_num, gps.effective_period_num) AND
        NVL(:p_to_period_num,   gps.effective_period_num) 
		
/* --- Accounting period filter--- */
/* AND gjl.EFFECTIVE_DATE >=nvl((select distinct START_DATE from GL_PERIODS gp where gp.period_name = :p_from_period_num AND GP.PERIOD_SET_NAME = GL.PERIOD_SET_NAME),gjl.EFFECTIVE_DATE ) 

AND gjl.EFFECTIVE_DATE <=nvl((select distinct END_DATE from GL_PERIODS gp2 where gp2.period_name = :p_to_period_num AND GP2.PERIOD_SET_NAME=GL.PERIOD_SET_NAME),gjl.EFFECTIVE_DATE ) */
/* --- End  --- */
	
		
  AND gl.ledger_id = :p_ledger_name
  AND   xep.legal_entity_id  = (:p_legal_entity)
  AND (NVL(FLVSRC.MEANING, GJS.USER_JE_SOURCE_NAME) IN (:P_Source)
    OR  'All'                					IN (:P_Source||'All'))
  AND (gp.adjustment_period_flag 	            IN (:P_accounting_period_type)
    OR  'All'                					IN (:P_accounting_period_type||'All')) 
AND (
       :p_adj_period_flag = 'Y'                -- Yes => include all
    OR gp.adjustment_period_flag = 'N'         -- No  => only normal
)

  AND GJL.je_line_num = GIR.je_line_num(+)
  AND GJH.je_header_id = GIR.je_header_id(+)
  AND GIR.gl_sl_link_id = XAL.gl_sl_link_id(+)
  AND GIR.gl_sl_link_table = XAL.gl_sl_link_table(+)
  AND XAL.ae_header_id = XAH.ae_header_id(+)
  AND XAH.entity_id = XTE.entity_id(+)
 
 AND XTE.entity_code = RT.entity_code(+) 
 AND XTE.source_id_int_1 = RT.cust_id(+)
 
 AND XTE.source_id_int_1 = aia.invoice_id(+)  -- invoice
 AND XTE.source_id_int_1 = ac.check_id(+)  -- paymeent
 
 and XAL.party_id =  aps.vendor_id(+)
 
AND FLVSRC.LOOKUP_TYPE(+) IN ('XXC_FRANCE_SOURCE_TRASLATIONS')
AND FLVSRC.LOOKUP_CODE(+) = GJS.USER_JE_SOURCE_NAME -- GJST
AND FLVSRC.LANGUAGE(+)='US'

AND FLVCAT.LOOKUP_TYPE(+) IN ('XXC_FRANCE_CATEGORY_TRASLATION')
AND FLVCAT.LOOKUP_CODE(+) = GJC.USER_JE_CATEGORY_NAME --GJCT
AND FLVCAT.LANGUAGE(+)='US'

--AND gjb.name  IN ('Receivables A 2919681000002 2919680 N')
 
ORDER BY gjh.close_acct_seq_value