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
Commentaires