K_NITECMB - Combined LOGPROD-PRODUCT-PRODSOQ

Apr 26, 2024

SET SCHEMA XXX_5DTA;
CREATE OR REPLACE VIEW K_NITECMB AS
        SELECT LG_COMP,
               LG_LOCN,
               LG_BUYR,
               LG_SUPL,
               LG_SUPLSUB,
               LG_SUPLUSR,
               LG_SUPLUSB,
               LG_SUPLNAM,
               LG_PROD,
               LG_DESC1,
               LG_LOGTYPE,
               LG_DELTCNT,
               LG_TIMESTP,
               LG_PRVSUPL,
               LG_PRVSUB,
               LG_PRVSPLU,
               LG_PRVSPLB,
               LG_PRVNAME,
               LG_USER,
               PR_USRSTAT,
               PR_SYSSTAT,
               PR_GROUP1,
               PR_GROUP2,
               PR_GROUP3,
               PR_GROUP4,
               PR_GROUP5,
               PQ_SOQSEQ#
            
FROM K_LOGPROD
                 LEFT JOIN K_PRODUCT
                     ON LG_COMP = PR_COMP
                         AND LG_LOCN = PR_LOCN
                         AND LG_SUPL = PR_SUPL
                         AND LG_SUPLSUB = PR_SUPLSUB
                         AND LG_PROD = PR_PROD
                         AND LG_SUPLUSR = PR_SUPLUSR
                         AND LG_SUPLUSB = PR_SUPLUSB
                 LEFT JOIN (
                         SELECT PQ_COMP,
                                PQ_LOCN,
                                PQ_BUYR,
                                PQ_SUPL,
                                PQ_SUPLSUB,
                                PQ_SUPLUSR,
                                PQ_SUPLUSB,
                                PQ_PROD,
                                MAX(PQ_SOQSEQ#) AS PQ_SOQSEQ#
                             FROM K_PRODSOQ
                             GROUP BY PQ_COMP,
                                      PQ_LOCN,
                                      PQ_BUYR,
                                      PQ_SUPL,
                                      PQ_SUPLSUB,
                                      PQ_SUPLUSR,
                                      PQ_SUPLUSB,
                                      PQ_PROD
                     ) AS K_PRODSOQ
                     ON LG_COMP = PQ_COMP
                         AND LG_LOCN = PQ_LOCN
                         AND LG_SUPL = PQ_SUPL
                         AND LG_SUPLSUB = PQ_SUPLSUB
                         AND LG_PROD = PQ_PROD
                         AND LG_SUPLUSR = PQ_SUPLUSR
                         AND LG_SUPLUSB = PQ_SUPLUSB;

LABEL ON TABLE K_NITECMB IS 'Combined LOGPROD-PRODUCT-PRODSOQ';

---