Oracle eBusiness Suite

Contrôle de la reprise des données clients

003.- Le pendant de Oracle AP pour les clients est Oracle AR pour Accounts Receivable.
Lors de la reprise des données de l'ancien système, la MOE doit contrôler l'exhaustivité des données reprises, à savoir :

Les clients,
Les factures

Voici quelques requêtes qui fourniront des éléments de contrôles simples et efficaces.
Après avoir fait le travail de comptage des sites clients et des lignes de factures clients pour toutes les sociétés, les contrôles doivent être consignés dans des tableaux de synthèse de la reprise.

-- COMPTABILITE CLIENTS ORACLE AR --

-- comptage des sites contacts clients

SELECT hou.NAME,party.party_name,CONTACTS.party_name contact_name

FROM hz_parties             party,

     hz_cust_accounts       cust_acct,

     hz_cust_acct_sites_all acct_site,

     hz_party_sites         party_site,

     hr_operating_units   hou,

(SELECT rel.object_id party_id,

         acct_role.cust_account_id ,

  acct_role.cust_acct_site_id,

  cont_party.party_name

  FROM

         hz_parties cont_party,

                hz_relationships rel,

           hz_org_contacts org_cont,

           hz_parties rel_party,

           hz_cust_accounts cont_acct,

  hz_cust_account_roles acct_role

  WHERE acct_role.party_id = rel.party_id

        AND acct_role.role_type  = 'CONTACT'

  AND org_cont.party_relationship_id = rel.relationship_id

  AND rel.subject_id = cont_party.party_id

  AND rel.party_id = rel_party.party_id

  AND rel.subject_table_name = 'HZ_PARTIES'

  AND rel.object_table_name = 'HZ_PARTIES'

  AND rel.object_id   = cont_acct.party_id

  AND acct_role.cust_account_id = cont_acct.cust_account_id

  -- AND cust_acct.cust_account_id  = acct_role.cust_account_id

  --AND acct_role.cust_acct_site_id= acct_site.cust_acct_site_id

  AND acct_role.cust_acct_site_id IS NOT NULL

) CONTACTS

WHERE cust_acct.party_id      = party.party_id

AND acct_site.party_site_id   = party_site.party_site_id

AND party.party_id            = party_site.party_id

AND hou.organization_id       = acct_site.org_id

AND hou.NAME                   LIKE  'TheFirm_FR470%'

--AND cust_acct.cust_account_id  = CONTACTS.cust_account_id(+)

AND CONTACTS.cust_acct_site_id(+)  = acct_site.cust_acct_site_id

ORDER BY party.party_name

SELECT

  'en-tete -->',

  ( SELECT o.NAME FROM hr_organization_units o  WHERE hcas.org_id = o.ORGANIZATION_ID) AS organization_name,

  ( SELECT mp.organization_code FROM mtl_parameters mp WHERE hcas.org_id = mp.organization_id) AS organization_code, 

  hp.PARTY_NUMBER,      -- N° organisation    toutes organisations

  hca.ACCOUNT_NUMBER,   -- N° client          propre organisation   

  hca.CUST_ACCOUNT_ID,

  hp.party_name AS NAME,

  hca.ATTRIBUTE1 AS code_ape_gfi1,

  hp.ATTRIBUTE1 AS code_ape_gfi2,

  hp.TAX_REFERENCE AS code_intracom,   

  hp.JGZZ_FISCAL_CODE AS siren, 

  hp.SIC_CODE AS code_ape,

  ( SELECT cpc.NAME FROM HZ_CUSTOMER_PROFILES CP, HZ_CUST_PROFILE_CLASSES CPC WHERE CP.SITE_USE_ID IS NULL AND CP.PROFILE_CLASS_ID = CPC.PROFILE_CLASS_ID AND cp.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID) AS profil_entete,

  ( SELECT l_class.lookup_code FROM AR_LOOKUPS l_class WHERE hca.CUSTOMER_CLASS_CODE = L_CLASS.LOOKUP_CODE(+) AND L_CLASS.LOOKUP_TYPE(+) = 'CUSTOMER CLASS') AS CLASS, 

  'Adresse -->', 

  hps.PARTY_SITE_NUMBER,

  hps.PARTY_SITE_ID,

  hl.location_id,

  hcas.CUST_ACCT_SITE_ID,

  hl.address1,

  hl.address2,

  hl.address3,

  hl.address4, 

  hl.postal_code, 

  hl.city, 

  hl.state,

  hl.country,

  hcas.attribute1 AS code_cotre,

  ( SELECT lang.DESCRIPTION FROM FND_LANGUAGES_TL lang WHERE hcas.LANGUAGE = lang.language_code AND lang.LANGUAGE = 'F') AS LANGUAGE,

  ( SELECT cpc.NAME FROM HZ_CUST_PROFILE_CLASSES CPC WHERE hprofile.PROFILE_CLASS_ID = CPC.PROFILE_CLASS_ID) AS profil_adresse,

  ( SELECT col.NAME FROM AR_COLLECTORS col WHERE hprofile.COLLECTOR_ID = COL.COLLECTOR_ID) AS profil_collector_name,

  hprofile.dunning_letters,

  ( SELECT dun_set.NAME FROM AR_DUNNING_LETTER_SETS DUN_SET WHERE hprofile.DUNNING_LETTER_SET_ID = DUN_SET.DUNNING_LETTER_SET_ID) AS lettre_relance,

  hprofile.send_statements,

  ( SELECT cyc.NAME FROM AR_STATEMENT_CYCLES CYC WHERE hprofile.statement_cycle_id = CYC.STATEMENT_CYCLE_ID) cycle_relance,

  ( SELECT amount.OVERALL_CREDIT_LIMIT FROM HZ_CUST_PROFILE_AMTS amount WHERE amount.currency_code = 'EUR' AND amount.cust_account_profile_id = hprofile.cust_account_profile_id) AS assurance,

  'BILL_TO -->',

  bill_to_su.site_use_id,

  ( SELECT su.LOCATION FROM hz_cust_site_uses_all su WHERE bill_to_su.site_use_id = su.site_use_id) LOCATION,

  ( SELECT ccid.segment2 FROM hz_cust_site_uses_all su, gl_code_combinations ccid WHERE bill_to_su.site_use_id = su.site_use_id AND su.gl_id_rec = ccid.code_combination_id) rec_segment2,

  ( SELECT ccid.segment8 FROM hz_cust_site_uses_all su, gl_code_combinations ccid WHERE bill_to_su.site_use_id = su.site_use_id AND su.gl_id_rec = ccid.code_combination_id) rec_segment8,

  ( SELECT ccid.segment2 FROM hz_cust_site_uses_all su, gl_code_combinations ccid WHERE bill_to_su.site_use_id = su.site_use_id AND su.gl_id_rev = ccid.code_combination_id) rev_segment2,

  ( SELECT ccid.segment8 FROM hz_cust_site_uses_all su, gl_code_combinations ccid WHERE bill_to_su.site_use_id = su.site_use_id AND su.gl_id_rev = ccid.code_combination_id) rev_segment8,

  ( SELECT ccid.segment2 FROM hz_cust_site_uses_all su, gl_code_combinations ccid WHERE bill_to_su.site_use_id = su.site_use_id AND su.gl_id_tax = ccid.code_combination_id) tax_segment2,

  ( SELECT ccid.segment8 FROM hz_cust_site_uses_all su, gl_code_combinations ccid WHERE bill_to_su.site_use_id = su.site_use_id AND su.gl_id_tax = ccid.code_combination_id) tax_segment8,

  'SHIP_TO -->',

  ( SELECT site_use_id FROM hz_cust_site_uses_all WHERE site_use_code = 'SHIP_TO' AND hcas.CUST_ACCT_SITE_ID = CUST_ACCT_SITE_ID AND status = 'A') AS ship_to_site_use_id,

  ( SELECT LOCATION FROM hz_cust_site_uses_all WHERE site_use_code = 'SHIP_TO' AND hcas.CUST_ACCT_SITE_ID = CUST_ACCT_SITE_ID AND status = 'A') AS ship_to_location,

  'DRAWEE -->',

  ( SELECT site_use_id FROM hz_cust_site_uses_all WHERE site_use_code = 'DRAWEE' AND hcas.CUST_ACCT_SITE_ID = CUST_ACCT_SITE_ID AND status = 'A') AS drawee_site_use_id,

  ( SELECT LOCATION FROM hz_cust_site_uses_all WHERE site_use_code = 'DRAWEE' AND hcas.CUST_ACCT_SITE_ID = CUST_ACCT_SITE_ID AND status = 'A') AS drawee_location,

  'Contacts -->',

  NVL(( SELECT COUNT(party.PERSON_LAST_NAME) AS nb

    FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_RELATIONSHIPS REL

    WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL.SUBJECT_TYPE = 'PERSON'

    AND ACCT_ROLE.STATUS = 'A' AND ACCT_ROLE.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_ID GROUP BY ACCT_ROLE.CUST_ACCT_SITE_ID), 0) AS nb_actif,

  NVL(( SELECT COUNT(party.PERSON_LAST_NAME) AS nb

    FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_RELATIONSHIPS REL

    WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL.SUBJECT_TYPE = 'PERSON'

    AND ACCT_ROLE.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_ID GROUP BY ACCT_ROLE.CUST_ACCT_SITE_ID), 0) AS nb,

  'xx'

FROM 

  hz_parties hp,         -- le plus global

  hz_cust_accounts hca,

--

  hz_locations hl, 

  hz_cust_acct_sites_all hcas,

  hz_party_sites hps,

  (SELECT CUST_ACCT_SITE_ID, site_use_id FROM hz_cust_site_uses_all WHERE site_use_code = 'BILL_TO' AND status = 'A') BILL_TO_su,

  HZ_CUSTOMER_PROFILES hprofile

--

--

WHERE 1=1

  AND hp.party_id = hca.party_id

  AND hp.party_id = hps.party_id

  AND hps.location_id = hl.location_id

  AND hca.cust_account_id = hcas.cust_account_id

  AND hcas.party_site_id = hps.party_site_id

  AND hcas.CUST_ACCT_SITE_ID = bill_to_su.CUST_ACCT_SITE_ID (+)

  AND bill_to_su.site_use_id = hprofile.SITE_USE_ID (+)

--

  AND ( SELECT o.NAME FROM hr_organization_units o  WHERE hcas.org_id = o.ORGANIZATION_ID) LIKE '%FR470%'

-- comptage des lignes de mouvements et factures clients

-- nombre de factures

SELECT o.NAME, COUNT(*)

FROM

  RA_CUSTOMER_TRX_ALL CT,

  hr_organization_units o

WHERE ct.org_id = o.ORGANIZATION_ID

AND  o.NAME LIKE '%FR%'

group by o.name

--nombre de lignes

SELECT o.NAME, COUNT(*) 

FROM

  RA_CUSTOMER_TRX_ALL CT,

  RA_CUSTomer_TRX_LINEs_ALL GD,

  hr_organization_units o,

  fnd_document_sequences fds,

  ra_cust_trx_types_all rctt

WHERE ct.customer_trx_id = gd.customer_trx_id

AND ct.org_id = o.ORGANIZATION_ID

AND fds.doc_sequence_id = ct.doc_sequence_id

AND rctt.cust_trx_type_id = ct.cust_trx_type_id

AND  o.NAME LIKE '%FR%'

--and fds.name like '%509'

GROUP BY o.NAME

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

Site du consultant Oracle Applications

001.- Encore un nouveau site ! Je suis consultant Oracle Applications et je commence aujourd'hui le 007 mars 2008 un blog autour de l'ERP Oracle Applications que j'ai pu étudier au cours de quelques missions au sein de boutiques comme SOPRA, ARTHUR ANDERSEN, PRICEWATERHOUSECOOPERS, CSC, ATOS ORIGIN et GFI.
En vrac, on y trouvera aussi bien des anecdotes de missions que des trucs et astuces utiles à un bon fonctionnel avisé qui en vaut deux.
Je remercie Olivier Chappert et Jean-Marc Allouët pour m'avoir mis le pied à l'étrier et jeté dans la marmite Oracle e-Business Suite le 1er septembre 1999.
Bonne lecture.