Generates "AR Cross-Clear" showing GL journal entries netting Revenue Payable against AR JIB accounts. Used to trigger "show me cross-clears" or "revenue netting".
Generate GL journal entries netting Revenue Payable (501.x) against AR JIB (130.x) from FO_PRODUCTION_DB.GOLD_FINANCIAL.gold_fct_gl_details.
Gather from the user:
period_start: Start date of period (YYYY-MM-DD, required)period_end: End date of period (YYYY-MM-DD, required)owner_code: Optional, filter to specific owner(s)company_code: Optional, default "200" (Formentera Operations LLC)SELECT
main_account,
sub_account,
account_name,
voucher_code,
entity_type,
entity_code,
entity_name,
CAST(journal_date AS DATE) AS journal_date,
CAST(accrual_date AS DATE) AS accrual_date,
gl_description,
net_amount
FROM FO_PRODUCTION_DB.GOLD_FINANCIAL.GOLD_FCT_GL_DETAILS
WHERE main_account IN ('501', '130')
AND sub_account IN ('1', '2', '3', '4')
AND (gl_description LIKE '%Net Revenue Against A/R%'
OR gl_description LIKE '%AR Cross Clear%')
AND journal_date >= :period_start
AND journal_date <= :period_end
AND company_code = :company_code
ORDER BY voucher_code, main_account
Add optional owner filter: AND entity_code IN (:owner_codes)
Each cross-clear creates two rows per voucher:
Main Account, Sub Account, Account Name, Voucher Code, Entity Type, Entity Code, Entity Name, Journal Date, Accrual Date, Description, Net Amount
AR Cross-Clear - {period} - {company}.xlsx