Bonjour à tous,
Je travaille sous Oracle 10g et ne suis malheureusement pas spécialiste en PL/SQL même si j'ai déjà créé quelques fonctions.
Je vais essayer d'être clair pour vous expliquer mon besoin mais c'est un peu complexe, désolé d'avance... Voici le contexte :
Nous commercialisons des produits via des catalogues renouvelés chaque année. Certains produits présentés sur des catalogues passés peuvent être reconduits sur les catalogues suivants, ou pas.
Pour des questions de mise en page des catalogues, les produits d'une même gamme vendus sous différents coloris (ex. : des canapés) sont présentés au client avec une et une seule référence (appelée référence commerciale) à laquelle on associe des codes couleurs. Lorsque le client passe commande, il indique donc la référence commerciale et le code couleur. L'association "référence commerciale + code couleur" permet de retrouver quelle référence physique est concernée (une référence physique pour le canapé rouge, une autre pour le bleu).
Malheureusement, même si les références physiques qui se cachent derrière les références commerciales restent inchangées dans le temps, il n'en va pas de même pour les références commerciales : le canapé présent sur le catalogue de l'année dernière aura une référence commerciale différente de celle du même canapé reconduit sur le catalogue actuel (c'est balot, mais notre outil de production nous l'impose). Par contre, ces 2 références commerciales, qui concernent la même gamme de canapés, pointeront bien vers les mêmes références physiques en fonction des codes couleurs.
Pour compliquer l'affaire s'il en est besoin, un coloris présent sur un catalogue passé peut être abandonné sur le catalogue actuel si ses ventes ont été considérées comme insuffisantes. A contrario, de nouveaux coloris peuvent apparaitre pour cette gamme de canapés dans les catalogues à venir.
Voici à présent ce qu'on m'a demandé : nos chefs de produits voudraient pouvoir suivre la vente de la gamme de canapés (et de tous les autres produits évidemment !) via un code (que nous appellerons code modèle) qui regrouperait toutes les références commerciales successives (et donc toutes les références physiques qui leur sont rattachées) attribuées au canapé, et ce que les coloris soient toujours en vente ou qu'ils aient été abandonnés. Ce code modèle sera porté au final par les références physiques dans notre système d'information.
Je dois donc écrire un fonction PL/SQL qui prendra en paramètre une référence physique et me retournera son code modèle.
Pour faire simple, on dira que le code modèle retourné sera basé sur le code de la référence commerciale la plus récemment créée pour l'ensemble des références physiques de la gamme concernée. Ainsi, si la référence physique passée en paramètre de la fonction correspond à un colori abandonné, la référence commerciale retenue pour déterminer son code modèle ne sera pas la celle à laquelle elle était affectée mais la dernière créée, toutes références physiques de la gamme confondues.
J'ai donc besoin de connaître, en partant d'une référence physique donnée, la liste de toutes les références physiques de la gamme concernée pour au final obtenir la liste de toutes les références commerciales auxquelles elles ont été affectées. Lorsque j'aurai cette liste de références commerciales, je saurai déterminer celle que je dois conserver pour construire le code modèle attendu.
Voici le descriptif (simplifié) de la table ZREFAFF dont je dispose pour effectuer ce travail :
Voici comment je peux déterminer manuellement, en partant d'une référence physique donnée, la liste exhaustive des références commerciales portant l'ensemble des références physiques de la gamme (chaque ordre reprend l'ordre complet précédent dans son IN) :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 Name Null Type ------------ -------- ------------ ZCODSUP_0 NOT NULL VARCHAR2(18) --> code catalogue (code support) ITMREF_0 NOT NULL VARCHAR2(60) --> référence physique (item reference) ZREFCOM_0 NOT NULL VARCHAR2(24) --> référence commerciale ZREFCOMDAT_0 NOT NULL DATE --> date de création de la référence commerciale
Première boucle :
Passe a1 : recherche des références commerciales contenant la référence physique passée en paramètre : 2 références commerciales retournées
Passe a2 : recherche des références physiques portées par les références commerciales retournées par la passe a1 : 4 références physiques retournées
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B');
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'));
Seconde boucle :
Passe b1 : recherche des références commerciales contenant les références physiques retournées par la passe a2 : 9 références commerciales retournées
Passe b2 : recherche des références physiques portées par les références commerciales retournées par la passe b1 : 8 références physiques retournées
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B')));
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'))));
Troisième boucle :
Passe c1 : recherche des références commerciales contenant les références physiques retournées par la passe b2 : 10 références commerciales retournées
Passe c2 : recherche des références physiques portées par les références commerciales retournées par la passe c1 : 8 références physiques retournées
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B')))));
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'))))));
A ce stade, je stoppe les recherches car la passe c2 me retourne un nombre de références physiques identique à celui de la passe b2 -> La liste des références commerciales que je retiendrai est donc celle retournée par la passe c1.
Il semblerait donc que j'aie besoin d'une fonction PL/SQL récursive acceptant en paramètre un VARCHAR2(60) lors de sa première exécution (-> la référence physique dont on recherche le code modèle). Lors de cette première exécution, elle effectuera le travail des passes a1 et a2 décrites ci-dessus puis s'appellera elle-même pour prendre en paramètre la liste des références physiques retournées par la passe a2. Le paramètre passé à la fonction à partir de la seconde passe sera donc une liste de VARCHAR2(60) et non plus un VARCHAR2(60) seul. Elle effectuera alors le travail des passes b1 et b2 et ainsi de suite jusqu'à ce quelle constate que les passes c2 et b2 retournent le même nombre d'enregistrements.
Dernière précision, je souhaiterais pouvoir utiliser cette fonction dans un ordre SQL du type : SELECT f_modele('MA REFERENCE PHYSIQUE') FROM dual;
Je ne sais malheureusement pas comment gérer le type de paramètre dont cette fonction a besoin pour fonctionner. Merci d'avance si êtes arrivés à me lire jusqu'au bout ! et encore plus si vous pouvez m'aider à résoudre ce problème.
N'hésitez pas à me poser des questions si je n'ai pas été clair.
Cordialement.
Partager