Tech Trainings

Oracle Fusion ERP – AP to SLA Linkage

July 7, 2025 by admin

SELECT 
BU_NAME,
batch_name,
journal_name,
invoice_num,
invoice_type_lookup_code,
SupplierName,
a.invoice_id,
COA_SEGM,
INV_Created_by,
accounted_dr,
accounted_cr,
payment_currency_code  ,                                       
(CASE 
           WHEN (a.invoice_type_lookup_code  <> 'PREPAYMENT') THEN 
           (SELECT  SUM(NVL(amount_remaining,0))
                  FROM  ap_payment_schedules_all apsa
                 WHERE a.invoice_id                = apsa.invoice_id
                   AND a.org_id                    = apsa.org_id)
           WHEN (a.invoice_type_lookup_code  =  'PREPAYMENT')
           THEN   (SELECT SUM(NVL(aida.prepay_amount_remaining, aida.amount)) 
                  FROM  ap_invoice_distributions_all aida
                 WHERE a.invoice_id               = aida.invoice_id
                   AND a.org_id                   = aida.org_id  )
         END )                                                                        "Invoice_Amount"   

FROM  (

SELECT 
hou.name BU_NAME,
hp.party_name SupplierName,
batch.name batch_name,
header.name journal_name,
inv.payment_currency_code                                                  ,
		inv.invoice_type_lookup_code,
		 inv.org_id , 
		 inv.created_by INV_Created_by,
		 inv.invoice_id,
inv.invoice_num,
SUM(NVL(link.unrounded_accounted_dr,0)) accounted_dr,
SUM(NVL(link.unrounded_accounted_cr,0)) accounted_cr,
SUM(NVL(link.unrounded_accounted_dr,0) - NVL(link.unrounded_accounted_cr,0)) balance,
       GCC.segment1   ||'.'||
       GCC.segment2   ||'.'||
       GCC.segment3    ||'.'||
       GCC.segment4    ||'.'||
       GCC.segment5    ||'.'||
       GCC.segment6    ||'.'||
       GCC.segment7    ||'.'||
       GCC.segment8    ||'.'||
       GCC.segment9    COA_SEGM
	   
	   
FROM
    gl_je_batches batch,
    gl_je_headers header,
    gl_je_lines lines,
    gl_import_references REF,
    xla_ae_lines xla_lines,
    xla_ae_headers xla_headers,
    xla_events events,
    ap_invoice_distributions_all dist,
    ap_invoice_lines_all ap_lines,
    ap_invoices_all inv,
    xla_distribution_links link,
	hr_operating_units hou ,
	GL_CODE_COMBINATIONS  GCC,
	poz_suppliers asp,
    hz_parties   hp
	
WHERE     batch.je_batch_id = header.je_batch_id
    AND   header.je_header_id = lines.je_header_id
    AND   header.ledger_id = lines.ledger_id
    AND   header.je_source = 'Payables'
    AND   REF.je_batch_id = batch.je_batch_id
    AND   REF.je_header_id = header.je_header_id
    AND   REF.je_line_num = lines.je_line_num
    AND   REF.gl_sl_link_id = xla_lines.gl_sl_link_id
    AND   xla_lines.ae_header_id = xla_headers.ae_header_id
    AND   xla_headers.ledger_id = xla_lines.ledger_id
    AND   xla_headers.event_id = events.event_id
    AND   xla_headers.ledger_id = header.ledger_id
    AND   events.event_id = dist.accounting_event_id
    AND   dist.invoice_id = ap_lines.invoice_id
    AND   dist.invoice_id = inv.invoice_id
    AND   dist.invoice_line_number = ap_lines.line_number
    AND   link.ae_header_id = xla_headers.ae_header_id
    AND   link.ae_line_num = xla_lines.ae_line_num
    AND   link.source_distribution_id_num_1 = dist.invoice_distribution_id
	AND GCC.CODE_COMBINATION_ID = lines.CODE_COMBINATION_ID
	and asp.vendor_id = inv.vendor_id 
	and asp.party_id = hp.party_id 
    --AND   inv.invoice_num = '150'
	and inv.created_by in ( 'DH_DM_PH2_FIN_USER' ,'151704')
	and inv.org_id = hou.organization_id
  GROUP BY
        batch.name,
        header.name,
        inv.invoice_num,
		hou.name ,
		inv.invoice_type_lookup_code,
		 inv.org_id ,
		 inv.created_by,
		 inv.invoice_id,
		 inv.payment_currency_code ,
		  GCC.segment1    ,
 GCC.segment2    ,
 GCC.segment3    ,
 GCC.segment4    ,
 GCC.segment5    ,
 GCC.segment6    ,
 GCC.segment7    ,
 GCC.segment8    ,
 GCC.segment9  ,
 hp.party_name
  ORDER BY hou.name, inv.invoice_num
  
  ) A

Comments |0|