Contrôle de la reprise des données Oracle AP

002.- Pour démarrer leur activité dans Oracle, les comptables fournisseurs ont besoin de récupérer leurs factures de leur ancien système. Après le paramétrage structurant des organisations, des entités comptables et de la clé comptable flexible, il convient de reprendre les données nécessaires à l'activité opérationnelle, à savoir :

• Les fournisseurs
• Les RIB rattachés
• Les factures

Ces objets sont récupérées via des open interfaces, voici un kit de scripts en PL/SQL qui permettent d'en vérifier la complétude :

--liste de sites fournisseurs

SELECT haou.NAME,pv.vendor_name, pvsa.vendor_site_code, pvsa.attribute15, pvsa.*

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

  hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND haou.NAME LIKE 'TheFirm_FR470%'

AND pvsa.VENDOR_SITE_CODE !='DOMICILE'

--AND pv.creation_date >= TO_DATE('21/01/2008 14:00:00','dd/mm/yyyy HH24:MI:SS')

-nombre de payment_method code en WIRE

SELECT pvsa.payment_method_lookup_code, haou.NAME, pv.vendor_name, pvsa.vendor_site_code, pvsa.*

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

     hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND haou.NAME LIKE 'TheFirm_FR301%'

AND pvsa.payment_method_lookup_code = 'WIRE'

--nombre de contacts

SELECT pv.vendor_name, pvsa.vendor_site_code , pvc.last_name, pvc.phone, pvc.*

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

     po_vendor_contacts pvc,

     hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND pvc.vendor_site_id = pvsa.vendor_site_id

AND haou.NAME LIKE 'TheFirm_FR470%'

-- AND pvc.creation_date >= TO_DATE('18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

-- comptes bancaires

SELECT  pv.vendor_name,

  aba.ACCOUNT_HOLDER_NAME,

        pvs.vendor_site_code,

  abb.BANK_NAME,

  abb.BANK_BRANCH_NAME,

  aba.BANK_ACCOUNT_NUM

FROM ap_bank_account_uses_all abau,

     ap_bank_accounts_all aba,

     ap_bank_branches abb,

     po_vendors pv,

     po_vendor_sites_all pvs,

  hr_operating_units hou

WHERE 1=1

AND abau.external_bank_account_id = aba.bank_account_id

AND aba.bank_branch_id = abb.bank_branch_id

AND abau.vendor_id = pv.vendor_id

AND abau.vendor_id = pvs.vendor_id

AND abau.vendor_site_id = pvs.vendor_site_id

AND hou.ORGANIZATION_ID =  pvs.ORG_ID

AND hou.NAME LIKE 'TheFirm_FR470%'

AND abau.end_date IS NULL

--AND abau.creation_date >= TO_DATE('18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

ORDER BY aba.account_holder_name,

         pvs.vendor_site_code

            

            

--- liste des lignes de factures par organisation et par lot

SELECT --aida.attribute_category,

       aida.DIST_CODE_COMBINATION_ID,

       aida.DISTRIBUTION_LINE_NUMBER,

    aida.attribute15,

       aba.batch_name, aba.control_invoice_total,

       pv.vendor_name,

       pvsa.vendor_site_code ,

       aia.TERMS_DATE, --date d'échéance

       aia.attribute15, -- code affaire

       aida.*

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

     ap_invoices_all aia,

     ap_invoice_distributions_all aida,

     ap_batches_all aba,

     hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND aia.vendor_site_id = pvsa.vendor_site_id

AND aida.invoice_id = aia.invoice_id

AND aia.batch_id = aba.batch_id

AND haou.NAME LIKE 'TheFirm_FR%'

--and aida.attribute15 is not null

--and aida.attribute15 != 'NR'

and aida.attribute_category is null

--AND aba.batch_name != 'REPCOTRE%

--and pv.vendor_name like 'ALD%'

--and aia.invoice_num = '00562557*'

--AND pvc.creation_date >= TO_DATE('18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

--18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

-- mise à jour des factures de l'INT018 sans attribute_category

UPDATE ap_invoice_distributions_all aida

SET attribute_category = (SELECT segment2

                                    FROM gl_code_combinations gcc

                                    WHERE code_combination_id = aida.dist_code_combination_id)

WHERE org_id IN (SELECT organization_id

                         FROM hr_all_organization_units haou

                         WHERE haou.NAME LIKE 'TheFirm_FR%')

AND attribute_category IS NULL

AND attribute15 IS NOT NULL

AND attribute15 <> 'NR'

-- comptes bancaires

SELECT  pv.vendor_name,

  aba.ACCOUNT_HOLDER_NAME,

        pvs.vendor_site_code,

  abb.BANK_NAME,

  abb.BANK_BRANCH_NAME,

  aba.BANK_ACCOUNT_NUM

FROM ap_bank_account_uses_all abau,

     ap_bank_accounts_all aba,

     ap_bank_branches abb,

     po_vendors pv,

     po_vendor_sites_all pvs,

  hr_operating_units       hou

WHERE 1=1

AND abau.external_bank_account_id = aba.bank_account_id

AND aba.bank_branch_id = abb.bank_branch_id

AND abau.vendor_id = pv.vendor_id

AND abau.vendor_id = pvs.vendor_id

AND abau.vendor_site_id = pvs.vendor_site_id

AND hou.ORGANIZATION_ID =  pvs.ORG_ID

AND  hou.NAME LIKE 'TheFirm_FR300%'

AND abau.end_date IS NULL

--AND abau.creation_date >= TO_DATE('18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

ORDER BY aba.account_holder_name,

         pvs.vendor_site_code

   

--comptes bancaires internes

SELECT    aba.ACCOUNT_HOLDER_NAME,

          aba.BANK_ACCOUNT_NAME,

          abb.BANK_NAME,

    abb.BANK_BRANCH_NAME,

    aba.BANK_ACCOUNT_NUM

FROM ap_bank_accounts_all aba,

     ap_bank_branches abb,

     hr_operating_units hou

WHERE 1=1

AND aba.bank_branch_id = abb.bank_branch_id

AND aba.account_type = 'INTERNAL'

AND hou.ORGANIZATION_ID =  aba.ORG_ID

AND  hou.NAME LIKE 'TheFirm_FR100%'

--AND abau.creation_date >= TO_DATE('18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

ORDER BY aba.account_holder_name

-- utilisation du compte bancaire

SELECT hou.NAME, COUNT(*), SUM(aca.amount)

FROM ap_checks_all aca,

     hr_operating_units hou

  WHERE  1=1

AND hou.NAME LIKE 'TheFirm_FR%'

AND hou.ORGANIZATION_ID =  aca.org_id

--AND aca.creation_date >= TO_DATE('26/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

GROUP BY hou.NAME

          

   

   

 

   

--- liste des lignes de factures par organisation et par lot

SELECT aba.batch_name,

       pv.vendor_name,

    pvsa.vendor_site_code ,

    aia.invoice_num,

    aida.line_type_lookup_code,

    aia.TERMS_DATE, --date d'échéance

    aia.attribute15, -- code affaire

    aida.*

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

  ap_invoices_all aia,

  ap_invoice_distributions_all aida,

  ap_batches_all aba,

  hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND aia.vendor_site_id = pvsa.vendor_site_id

AND aida.invoice_id = aia.invoice_id

AND aia.batch_id = aba.batch_id

AND haou.NAME LIKE 'TheFirm_FR300%'

--AND aia.invoice_type_lookup_code = 'CREDIT'

--AND pvc.creation_date >= TO_DATE('18/01/2008 16:00:00','dd/mm/yyyy HH24:MI:SS')

  -- SYNTHESE --

 

  --comptage des sites fournisseurs par organisation

SELECT haou.NAME,COUNT(*)

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

  hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa

.org_id

AND haou.NAME LIKE 'TheFirm_FR%'

AND pvsa.attribute15 IS  NOT NULL

--AND pv.creation_date >= TO_DATE('21/01/2008 12:00:00','dd/mm/yyyy HH24:MI:SS')

GROUP BY haou.NAME

-- comptage des contacts par organisation

SELECT haou.NAME, COUNT(*)

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

  po_vendor_contacts pvc,

  hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND pvc.vendor_site_id = pvsa.vendor_site_id

AND haou.NAME LIKE 'TheFirm_FR%'

GROUP BY haou.NAME

--comptage des comptes bancaires par organisation

SELECT  hou.NAME, COUNT(*)

FROM ap_bank_account_uses_all abau,

     ap_bank_accounts_all aba,

     ap_bank_branches abb,

     po_vendors pv,

     po_vendor_sites_all pvs,

  hr_operating_units       hou

WHERE 1=1

AND abau.external_bank_account_id = aba.bank_account_id

AND aba.bank_branch_id = abb.bank_branch_id

AND abau.vendor_id = pv.vendor_id

AND abau.vendor_id = pvs.vendor_id

AND abau.vendor_site_id = pvs.vendor_site_id

AND hou.ORGANIZATION_ID =  pvs.ORG_ID

AND  hou.NAME LIKE 'TheFirm_FR%'

AND abau.end_date IS NULL

--AND abau.creation_date >= TO_DATE('25/01/2008 12:00:00','dd/mm/yyyy HH24:MI:SS')

GROUP BY hou.NAME

--comptage des lignes de factures par organisation

SELECT haou.NAME, aba.batch_name,  aia.invoice_type_lookup_code, COUNT(*), SUM(aia.INVOICE_AMOUNT)

FROM po_vendors pv,

     po_vendor_sites_all pvsa,

  ap_invoices_all aia,

  ap_invoice_distributions_all aida,

  ap_batches_all aba,

  hr_all_organization_units haou 

WHERE 1=1

AND pv.vendor_id = pvsa.vendor_id

AND haou.organization_id = pvsa.org_id

AND aia.vendor_site_id = pvsa.vendor_site_id

AND aida.invoice_id = aia.invoice_id

AND aia.batch_id = aba.batch_id

AND haou.NAME LIKE 'TheFirm_FR100%'

and aba.batch_name like '%AMEX%'

GROUP BY haou.NAME,

   aba.batch_name,

   aia.invoice_type_lookup_code