Untitled
unknown
plain_text
2 years ago
52 kB
7
Indexable
create or replace PROCEDURE REQ_CZ_TMP_BBM_SLI_MESSAGE (
ORIGINATOR_REF IN VARCHAR, -- PREPARATIONS ORIGINATOR REFERENCE
YEAR_NUM IN INT, -- YEAR NUMBER
ORD_NUM IN INT, -- PREPARATIONS NUMBER
RESULTS OUT SYS_REFCURSOR
)
AS
DEPOT_CODE VARCHAR2(3);
ACTIVITY_CODE VARCHAR2(3);
BEGIN
DEPOT_CODE := 'TMP';
ACTIVITY_CODE := 'BBM';
OPEN RESULTS FOR
WITH
HL52110 AS(
SELECT DISTINCT 110 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52110'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(HL_PREPA_ENTETE.PECTPR AS CHAR(3)) -- PREPARATION TYPE CODE
|| CAST(HL_PREPA_ENTETE.PECDES AS CHAR(13)) -- PREPARATION CONSIGNEE CODE
|| CAST(HL_PREPA_ENTETE.PECDES AS CHAR(13)) -- DESPATCH CONSIGNEE CODE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEQTPP * 1000,'00000000000'))) AS CHAR(11))
-- TRANSPORT QUANTITY
|| CAST(' ' AS CHAR(3)) -- TRANSPORT QUANTITY UNIT
|| CAST(HL_PREPA_ENTETE.PECMOP AS CHAR(3)) -- PREPARATION ORDER REASON CODE
|| CAST(HL_PREPA_ENTETE.PECRGE AS CHAR(13)) -- DESPATCH GROUPING CODE
|| CAST(' ' AS CHAR(20)) -- TRANSPORT DESPATCH ID
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52120 AS(
SELECT DISTINCT 120 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52120'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PESLEP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE - PLANNED DELIVERY DATE - CENTURY
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEALEP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE - PLANNED DELIVERY DATE - YEAR
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEMLEP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE - PLANNED DELIVERY DATE - MONTH
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEJLEP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE - PLANNED DELIVERY DATE - DAY
|| CAST(SUBSTR('0000'||HL_PREPA_ENTETE.PEHDEP, -4) AS CHAR(4)) -- DESPATCH CONSIGNEE - PLANNED DELIVERY START TIME
|| CAST(SUBSTR('0000'||HL_PREPA_ENTETE.PEHFEP, -4) AS CHAR(4)) -- DESPATCH CONSIGNEE - PLANNED DELIVERY END TIME
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52130 AS(
SELECT DISTINCT 130 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52130'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PESREP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE APPOINTMENT DATE - CENTURY
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEAREP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE APPOINTMENT DATE - YEAR
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEMREP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE APPOINTMENT DATE - MONTH
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEJREP, -2) AS CHAR(2)) -- DESPATCH CONSIGNEE APPOINTMENT DATE - DAY
|| CAST(SUBSTR('0000'||HL_PREPA_ENTETE.PEHDRP, -4) AS CHAR(4)) -- DESPATCH CONSIGNEE APPOINTMENT START TIME
|| CAST(SUBSTR('0000'||HL_PREPA_ENTETE.PEHFRP, -4) AS CHAR(4)) -- DESPATCH CONSIGNEE APPOINTMENT END TIME
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52140 AS (
SELECT DISTINCT 140 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52140'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PET1PP,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL LEVEL 1 TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PET2PP,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL LEVEL 2 TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PET3PP,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL LEVEL 3 TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PETBPP,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL IN BASE LVS TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PETG1P,'000000000'))) AS CHAR(9))
-- PREPARATION - GENERAL TOTAL LEVEL 1 TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PETG2P,'000000000'))) AS CHAR(9))
-- PREPARATION - GENERAL TOTAL LEVEL 2 TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEPNTA * 1000,'00000000000'))) AS CHAR(11))
-- PREPARATION - TOTAL NET WEIGHT TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEPBTA * 1000,'00000000000'))) AS CHAR(11))
-- PREPARATION - TOTAL GROSS WEIGHT TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEVTAP * 1000,'00000000000'))) AS CHAR(11))
-- PREPARATION - TOTAL VOLUME TO PREPARE
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52150 AS (
SELECT DISTINCT 150 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52150'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PET1PV,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL LEVEL 1 CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PET2PV,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL LEVEL 2 CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PET3PV,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL LEVEL 3 CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PETBPV,'000000000'))) AS CHAR(9))
-- PREPARATION - TOTAL IN BASE LVS CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PETG1V,'000000000'))) AS CHAR(9))
-- PREPARATION - GENERAL TOTAL LEVEL 1 CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PETG2V,'000000000'))) AS CHAR(9))
-- PREPARATION - GENERAL TOTAL LEVEL 2 CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEPNTV * 1000,'00000000000'))) AS CHAR(11))
-- PREPARATION - TOTAL NET WEIGHT CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEPBTV * 1000,'00000000000'))) AS CHAR(11))
-- PREPARATION - TOTAL GROSS WEIGHT CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_ENTETE.PEVTAV * 1000,'00000000000'))) AS CHAR(11))
-- PREPARATION - TOTAL VOLUME CONFIRMED
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52160 AS (
SELECT DISTINCT 160 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52160'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PESSOR, -2) AS CHAR(2)) -- PREPARATION - STOCK DESPATCH DATE - CENTURY
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEASOR, -2) AS CHAR(2)) -- PREPARATION - STOCK DESPATCH DATE - YEAR
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEMSOR, -2) AS CHAR(2)) -- PREPARATION - STOCK DESPATCH DATE - MONTH
|| CAST(SUBSTR('00'||HL_PREPA_ENTETE.PEJSOR, -2) AS CHAR(2)) -- PREPARATION - STOCK DESPATCH DATE - DAY
|| CAST(SUBSTR('000000'||HL_PREPA_ENTETE.PEHSOR, -6) AS CHAR(6)) -- PREPARATION - STOCK DESPATCH TIME
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
-- HL52165
HL52199 AS (
SELECT DISTINCT 199 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52199'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST (NVL(HL_COMMENTAIRE.COTXTC, ' ') AS CHAR(70)) -- COMMENT
|| CAST (NVL(HL_COMMENTAIRE.COCFCO, ' ') AS CHAR(3)) -- COMMENT GROUP
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
ON HL_COMMENTAIRE.CONCOM = HL_PREPA_ENTETE.PENCOM
AND HL_COMMENTAIRE.COCFCO IN ('WSN', 'SRN', 'SH1', 'SH0')
-- IFC - if oreder was fully cancelled
-- SH1 - ShippingAgentCode
-- SH0 - ShippingAgentServiceCode
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
UNION ALL
-- IFC - if oreder was fully cancelled
SELECT DISTINCT 199 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52199'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST ('FULLY CANCELLED ORDER' AS CHAR(70)) -- COMMENT
|| CAST ('IFC' AS CHAR(3)) -- COMMENT GROUP
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
JOIN SD_HLCOMMP HL_COMMENTAIRE
ON HL_COMMENTAIRE.CONCOM = HL_PREPA_LIGNE.P1NCOM
AND HL_COMMENTAIRE.COCFCO IN ('OOS', 'CCL')
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
AND HL_PREPA_ENTETE.PETSOL = 1
AND HL_PREPA_ENTETE.PETVAP = 1
UNION ALL
-- MDT - MESSAGE DATE TIME - MAX VALUE FROM HL230
SELECT DISTINCT 199 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", '0' AS "LINE_NB",
'HL52199'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- YEAR NUMBER
|| CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9)) -- PREPARATION NUMBER
|| CAST (MAX( CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1SVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - CENTURY
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1AVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - YEAR
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1MVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - MONTH
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1JVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - DAY
|| CAST(SUBSTR('000000'||HL_PREPA_LIGNE.P1HVLP, -6) AS CHAR(6))) -- PREPARATION LINE CONFIRMATION TIME -- COMMENT
AS CHAR(70))
|| CAST ('MDT' AS CHAR(3)) -- COMMENT GROUP
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
GROUP BY 199, 0
,CAST(ACTIVITY_CODE AS CHAR(3))
,CAST(DEPOT_CODE AS CHAR(3))
,CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2))
,CAST(SUBSTR('000000000'||HL_PREPA_ENTETE.PENPRE, -9) AS CHAR(9))
,CAST ('MDT' AS CHAR(3))
),
---------------------- DETAILS
HL52210 AS(
SELECT DISTINCT 210 AS "SEQ", 0 AS "SEQ2", 0 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52210'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(HL_PREPA_LIGNE.PECDOD AS CHAR(3)) -- PREPARATION ORDER PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANO AS CHAR(2)) -- PREPARATION ORDER YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NODP, 9, '0') AS CHAR(9)) -- PREPARATION ORDER NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLOP, 7, '0') AS CHAR(7)) -- PREPARATION ORDER LINE NUMBER
|| CAST(HL_PREPA_LIGNE.P1CART AS CHAR(16)) -- ITEM CODE
|| CAST(HL_PREPA_LIGNE.P1CVLA AS CHAR(2)) -- ITEM LV CODE
|| CAST(LPAD(HL_PREPA_LIGNE.P1CPRP, 3, '0') AS CHAR(3)) -- OWNER CODE
|| CAST(LPAD(HL_PREPA_LIGNE.P1CQAL, 3, '0') AS CHAR(3)) -- GRADE CODE
|| CAST(HL_PREPA_LIGNE.P1CDES AS CHAR(13)) -- END CONSIGNEE CODE
|| CAST(HL_PROPRIETAIRE.PRCLPR AS CHAR(13)) -- OWNER LONG CODE
|| CAST(HL_PREPA_ENTETE.PERODP AS CHAR(20)) -- PREPARATION ORIGINATOR REFERENCE
|| CAST(LPAD(HL_ODP_LIGNE.OLLROP, 7, '0') AS CHAR(7)) -- PREPARATION ORIGINATOR REFERENCE LINE NUMBER
|| CAST(HL_ODP_LIGNE.OLRCDD AS CHAR(20)) -- CONSIGNEE ITEM REFERENCE
|| CAST(HL_PREPA_LIGNE.P1CKIL AS CHAR(1)) -- KIT CODE
|| CAST(HL_ODP_ENTETE.OERODD AS CHAR(20)) -- END CONSIGNEE REFERENCE
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
JOIN SD_HLODPLP HL_ODP_LIGNE
ON HL_PREPA_LIGNE.PECDOD = HL_ODP_LIGNE.OLCDPO
AND HL_PREPA_LIGNE.P1CACT = HL_ODP_LIGNE.OLCACT
AND HL_PREPA_LIGNE.P1NANO = HL_ODP_LIGNE.OLNANN
AND HL_PREPA_LIGNE.P1NODP = HL_ODP_LIGNE.OLNODP
AND HL_PREPA_LIGNE.P1NLOP = HL_ODP_LIGNE.OLNLOP
JOIN SD_HLODPEP HL_ODP_ENTETE
ON HL_ODP_LIGNE.OLCACT = HL_ODP_ENTETE.OECACT
AND HL_ODP_LIGNE.OLCDPO = HL_ODP_ENTETE.OECDPO
AND HL_ODP_LIGNE.OLNANN = HL_ODP_ENTETE.OENANN
AND HL_ODP_LIGNE.OLNODP = HL_ODP_ENTETE.OENODP
JOIN SD_HLPROPP HL_PROPRIETAIRE
ON HL_PREPA_LIGNE.P1CACT = HL_PROPRIETAIRE.PRCACT
AND HL_PREPA_LIGNE.P1CPRP = HL_PROPRIETAIRE.PRCPRP
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52220 AS(
SELECT DISTINCT 210 AS "SEQ", 1 AS "SEQ2", 0 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52220'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1QODP, 7, '0') AS CHAR(7)) -- PRO PREPARATION LINE - QUANTITY IN BASE LVS TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_LIGNE.P1PNOD * 1000,'000000000'))) AS CHAR(9))
-- PRO PREPARATION LINE - NET WEIGHT TO PREPARE
|| CAST(LPAD(HL_PREPA_LIGNE.P1QAPR, 7, '0') AS CHAR(7)) -- PREPARATION LINE - QUANTITY IN BASE LVS TO PREPARE
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_LIGNE.P1PAPN * 1000,'000000000'))) AS CHAR(9))
-- PREPARATION LINE - NET WEIGHT TO PREPARE
|| CAST(LPAD(HL_PREPA_LIGNE.P1QPRE, 7, '0') AS CHAR(7)) -- PREPARATION LINE - QUANTITY IN BASE LVS CONFIRMED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_LIGNE.P1PNPR * 1000,'000000000'))) AS CHAR(9))
-- PREPARATION LINE - NET WEIGHT CONFIRMED
|| CAST(LPAD(HL_PREPA_LIGNE.P1QRGE, 7, '0') AS CHAR(7)) -- PREPARATION LINE - QUANTITY IN BASE LVS OF GENERATED BACK ORDER ITEMS
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_LIGNE.P1PNRG * 1000,'000000000'))) AS CHAR(9))
-- PREPARATION LINE - NET WEIGHT OF BACK ORDER ITEMS GENERATED
|| CAST(LPAD(HL_PREPA_LIGNE.P1NQSB, 7, '0') AS CHAR(7)) -- PREPARATION LINE - NUMERATOR QUANTITY SUBSTITUTED
|| CAST(LPAD(HL_PREPA_LIGNE.P1DQSB, 7, '0') AS CHAR(7)) -- PREPARATION LINE - DENOMINATOR QUANTITY SUBSTITUTED
|| CAST(LTRIM(RTRIM(TO_CHAR(HL_PREPA_LIGNE.P1PNSU * 1000,'000000000'))) AS CHAR(9))
-- PREPARATION LINE - NET WEIGHT SUBSTITUTED
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52230 AS(
SELECT DISTINCT 210 AS "SEQ", 2 AS "SEQ2", 0 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52230'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NJMF, 3, '0') AS CHAR(3)) -- FORCED PRIORITY DATE MINIMUM NUMBER OF DAYS
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1SVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - CENTURY
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1AVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - YEAR
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1MVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - MONTH
|| CAST(SUBSTR('00'||HL_PREPA_LIGNE.P1JVLP, -2) AS CHAR(2)) -- PREPARATION LINE CONFIRMATION DATE - DAY
|| CAST(SUBSTR('000000'||HL_PREPA_LIGNE.P1HVLP, -6) AS CHAR(6)) -- PREPARATION LINE CONFIRMATION TIME
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52240 AS(
SELECT DISTINCT 210 AS "SEQ", 3 AS "SEQ2", 0 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52240'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST(' ' AS CHAR(3)) -- ACTIVITY CODE OF CORRESPONDING BACK-ORDER PREPARATION LINE
|| CAST(' ' AS CHAR(3)) -- PHYSICAL DEPOT CODE OF CORRESPONDING BACK-ORDER PREPARATION LINE
|| CAST(' ' AS CHAR(2)) -- YEAR NUMBER OF CORRESPONDING BACK-ORDER PREPARATION LINE
|| CAST(' ' AS CHAR(13)) -- NUMBER OF CORRESPONDING BACK-ORDER PREPARATION LINE
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52250 AS(
SELECT DISTINCT 210 AS "SEQ", 4 AS "SEQ2", 1 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52250'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST( LPAD(HL_GEI_SORTI.GSQGEI, 7, '0') AS CHAR(7)) -- IPG QUANTITY IN BASE LVs
|| CAST( LPAD(HL_GEI_SORTI.GSPNGE * 1000, 9, '0') AS CHAR(9)) -- IPG NET WEIGHT
|| CAST(HL_GEI_SORTI.GSCFOU AS CHAR(13)) -- SUPPLIER CODE
|| CAST(HL_GEI_SORTI.GSNCOL AS CHAR(18)) -- CARTON NUMBER (ZAMIENIONA KOLEJNOSC Z CARTON, BO LINKER ZLE WYSYLA)
|| CAST(HL_GEI_SORTI.GSNSUP AS CHAR(18)) -- HD NUMBER(ZAMIENIONA KOLEJNOSC Z CARTON, BO LINKER ZLE WYSYLA)
|| CAST(LPAD(HL_GEI_SORTI.GSPRGE * 1000, 11, '0') AS CHAR(11)) -- PRICE
|| CAST(HL_GEI_SORTI.GSLOTF AS CHAR(20)) -- BATCH 1
|| CAST( LPAD(HL_GEI_SORTI.GSSFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - CENTURY
|| CAST( LPAD(HL_GEI_SORTI.GSAFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - YEAR
|| CAST( LPAD(HL_GEI_SORTI.GSMFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - MONTH
|| CAST( LPAD(HL_GEI_SORTI.GSJFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - DAY
|| CAST( LPAD(HL_GEI_SORTI.GSSREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - CENTURY
|| CAST( LPAD(HL_GEI_SORTI.GSAREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - YEAR
|| CAST( LPAD(HL_GEI_SORTI.GSMREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - MONTH
|| CAST( LPAD(HL_GEI_SORTI.GSJREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - DAY
|| CAST( LPAD(HL_GEI_SORTI.GSSDLU, 2, '0') AS CHAR(2)) -- IPG BBD - CENTURY
|| CAST( LPAD(HL_GEI_SORTI.GSADLU, 2, '0') AS CHAR(2)) -- IPG BBD - YEAR
|| CAST( LPAD(HL_GEI_SORTI.GSMDLU, 2, '0') AS CHAR(2)) -- IPG BBD - MONTH
|| CAST( LPAD(HL_GEI_SORTI.GSJDLU, 2, '0') AS CHAR(2)) -- IPG BBD - DAY
|| CAST( LPAD(HL_GEI_SORTI.GSSDLV, 2, '0') AS CHAR(2)) -- IPG SBD - CENTURY
|| CAST( LPAD(HL_GEI_SORTI.GSADLV, 2, '0') AS CHAR(2)) -- IPG SBD - YEAR
|| CAST( LPAD(HL_GEI_SORTI.GSMDLV, 2, '0') AS CHAR(2)) -- IPG SBD - MONTH
|| CAST( LPAD(HL_GEI_SORTI.GSJDLV, 2, '0') AS CHAR(2)) -- IPG SBD - DAY
|| CAST( LPAD(HL_GEI_SORTI.GSSDLC, 2, '0') AS CHAR(2)) -- IPG UBD - CENTURY
|| CAST( LPAD(HL_GEI_SORTI.GSADLC, 2, '0') AS CHAR(2)) -- IPG UBD - YEAR
|| CAST( LPAD(HL_GEI_SORTI.GSMDLC, 2, '0') AS CHAR(2)) -- IPG UBD - MONTH
|| CAST( LPAD(HL_GEI_SORTI.GSJDLC, 2, '0') AS CHAR(2)) -- IPG UBD - DAY
|| CAST(HL_GEI_SORTI.GSLOT2 AS CHAR(20)) -- BATCH 2
|| CAST(HL_GEI_SORTI.GSLOT3 AS CHAR(20)) -- BATCH 3
-- || CAST(HL_GEI_SORTI.GSCTSU AS CHAR(3)) -- HD TYPE CODE
-- || CAST(HL_GEI_SORTI.GSCTCO AS CHAR(3)) -- CARTON TYPE CODE
|| CAST(' ' AS CHAR(6))
|| CAST(' ' AS CHAR(35)) -- TRANSPORT DESPATCH UNIT ID
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE -- PREPARATION LINE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
-- JOIN SD_HLPLLPP HL_PRL_LIG_PRP -- PICK/PREPARATION LINE
-- ON HL_PRL_LIG_PRP.PPCACP = HL_PREPA_LIGNE.P1CACT
-- AND HL_PRL_LIG_PRP.PPCDPP = HL_PREPA_LIGNE.P1CDPO
-- AND HL_PRL_LIG_PRP.PPNANP = HL_PREPA_LIGNE.P1NANN
-- AND HL_PRL_LIG_PRP.PPNLPR = HL_PREPA_LIGNE.P1NLPR
-- JOIN SD_HLPRELP HL_PRELEVEMENT -- PICK
-- ON HL_PRELEVEMENT.PVCACT = HL_PRL_LIG_PRP.PPCACT
-- AND HL_PRELEVEMENT.PVCDPO = HL_PRL_LIG_PRP.PPCDPO
-- AND HL_PRELEVEMENT.PVNANN = HL_PRL_LIG_PRP.PPNANN
-- AND HL_PRELEVEMENT.PVNPRL = HL_PRL_LIG_PRP.PPNPRL
-- AND HL_PRELEVEMENT.PVTVPR = '1'
-- AND HL_PRELEVEMENT.PVTNPR = '0'
--== DESPATCHED IPG ==--
-- CONNECT BY 'IPG AFTER PICK' WITH PICK
JOIN SD_HLGESOP HL_GEI_SORTI
-- ON HL_GEI_SORTI.GSNGEI = HL_PRELEVEMENT.PVNGEG
-- AND HL_GEI_SORTI.GSNSUP = HL_PRELEVEMENT.PVNSUG
-- AND HL_GEI_SORTI.GSNAPP != 0
-- AND HL_GEI_SORTI.GSNPRE != 0
ON HL_GEI_SORTI.GSCACT = HL_PREPA_LIGNE.P1CACT
AND HL_GEI_SORTI.GSCDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_GEI_SORTI.GSNALI = HL_PREPA_LIGNE.P1NANP
AND HL_GEI_SORTI.GSNLPR = HL_PREPA_LIGNE.P1NLPR
-- JOIN SD_HLMISSP HL_MISSION -- Pick batch
-- ON HL_PRELEVEMENT.PVCDPO = HL_MISSION.MICDPO
-- AND HL_PRELEVEMENT.PVNANN = HL_MISSION.MINANN
-- AND HL_PRELEVEMENT.PVNMIS = HL_MISSION.MINMIS
-- AND NOT (MITMVA = 1 AND MITBPM = 0)
-- JOIN SD_HLPOPRP HL_POOL_PREPA -- Preparation for preparation pool
-- ON HL_PREPA_ENTETE.PECACT = HL_POOL_PREPA.POPCACT
-- AND HL_PREPA_ENTETE.PECDPO = HL_POOL_PREPA.POPCDPO
-- AND HL_PREPA_ENTETE.PENANN = HL_POOL_PREPA.POPNAPRE
-- AND HL_PREPA_ENTETE.PENPRE = HL_POOL_PREPA.POPNPRE
--
-- JOIN SD_HLPVPOP HL_PRELEV_POOL -- Pick - Preparation pool
-- ON HL_PRL_LIG_PRP.PPCACP = HL_PRELEV_POOL.PPOCACT
-- AND HL_PRL_LIG_PRP.PPCDPP = HL_PRELEV_POOL.PPOCDPO
-- AND HL_PRL_LIG_PRP.PPNANN = HL_PRELEV_POOL.PPONANN
-- AND HL_PRL_LIG_PRP.PPNPRL = HL_PRELEV_POOL.PPONPRL
-- AND HL_POOL_PREPA.POPNANN = HL_PRELEV_POOL.PPONAPOO
-- AND HL_POOL_PREPA.POPNUMPOPR = HL_PRELEV_POOL.PPONUMPOPR
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
AND HL_PREPA_ENTETE.PETVAP = '1' -- Flag - Preparation confirmed
AND HL_PREPA_ENTETE.PETESS = '1' -- Flag - Preparation stock despatch in progress
AND HL_PREPA_ENTETE.PETSOP = '1' -- Flag - Stock despatch made for preparation
UNION ALL
-- SLI AFTER CONFIRM BUT BEFORE DESPATCH
-- DATA FROM HL_PRELEVEMENT + HL_GEL
SELECT DISTINCT 210 AS "SEQ", 4 AS "SEQ2", 1 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52250'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST( LPAD(HL_GEI.GEQGEI, 7, '0') AS CHAR(7)) -- IPG QUANTITY IN BASE LVs
|| CAST( LPAD(HL_GEI.GEPNGE * 1000, 9, '0') AS CHAR(9)) -- IPG NET WEIGHT
|| CAST(HL_GEI.GECFOU AS CHAR(13)) -- SUPPLIER CODE
|| CAST(HL_GEI.GENCOL AS CHAR(18)) -- CARTON NUMBER (ZAMIENIONA KOLEJNOSC Z HD, BO LINKER ZLE WYSYLA)
|| CAST(HL_GEI.GENSUP AS CHAR(18)) -- HD NUMBER (ZAMIENIONA KOLEJNOSC Z CARTON, BO LINKER ZLE WYSYLA)
|| CAST(LPAD(HL_GEI.GEPRGE * 1000, 11, '0') AS CHAR(11)) -- PRICE
|| CAST(HL_GEI.GELOTF AS CHAR(20)) -- BATCH 1
|| CAST( LPAD(HL_GEI.GESFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - CENTURY
|| CAST( LPAD(HL_GEI.GEAFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - YEAR
|| CAST( LPAD(HL_GEI.GEMFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - MONTH
|| CAST( LPAD(HL_GEI.GEJFAB, 2, '0') AS CHAR(2)) -- IPG DATE OF MANUFACTURE - DAY
|| CAST( LPAD(HL_GEI.GESREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - CENTURY
|| CAST( LPAD(HL_GEI.GEAREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - YEAR
|| CAST( LPAD(HL_GEI.GEMREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - MONTH
|| CAST( LPAD(HL_GEI.GEJREG, 2, '0') AS CHAR(2)) -- IPG RECEIPT DATE - DAY
|| CAST( LPAD(HL_GEI.GESDLU, 2, '0') AS CHAR(2)) -- IPG BBD - CENTURY
|| CAST( LPAD(HL_GEI.GEADLU, 2, '0') AS CHAR(2)) -- IPG BBD - YEAR
|| CAST( LPAD(HL_GEI.GEMDLU, 2, '0') AS CHAR(2)) -- IPG BBD - MONTH
|| CAST( LPAD(HL_GEI.GEJDLU, 2, '0') AS CHAR(2)) -- IPG BBD - DAY
|| CAST( LPAD(HL_GEI.GESDLV, 2, '0') AS CHAR(2)) -- IPG SBD - CENTURY
|| CAST( LPAD(HL_GEI.GEADLV, 2, '0') AS CHAR(2)) -- IPG SBD - YEAR
|| CAST( LPAD(HL_GEI.GEMDLV, 2, '0') AS CHAR(2)) -- IPG SBD - MONTH
|| CAST( LPAD(HL_GEI.GEJDLV, 2, '0') AS CHAR(2)) -- IPG SBD - DAY
|| CAST( LPAD(HL_GEI.GESDLC, 2, '0') AS CHAR(2)) -- IPG UBD - CENTURY
|| CAST( LPAD(HL_GEI.GEADLC, 2, '0') AS CHAR(2)) -- IPG UBD - YEAR
|| CAST( LPAD(HL_GEI.GEMDLC, 2, '0') AS CHAR(2)) -- IPG UBD - MONTH
|| CAST( LPAD(HL_GEI.GEJDLC, 2, '0') AS CHAR(2)) -- IPG UBD - DAY
|| CAST(HL_GEI.GELOT2 AS CHAR(20)) -- BATCH 2
|| CAST(HL_GEI.GELOT3 AS CHAR(20)) -- BATCH 3
-- || CAST(HL_PRELEVEMENT.PVCTSU AS CHAR(3)) -- HD TYPE CODE
-- || CAST(HL_PRELEVEMENT.PVCTCO AS CHAR(3)) -- CARTON TYPE CODE
|| CAST(' ' AS CHAR(6))
|| CAST(' ' AS CHAR(35)) -- TRANSPORT DESPATCH UNIT ID
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE -- PREPARATION LINE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
JOIN SD_HLPLLPP HL_PRL_LIG_PRP -- PICK/PREPARATION LINE
ON HL_PRL_LIG_PRP.PPCACP = HL_PREPA_LIGNE.P1CACT
AND HL_PRL_LIG_PRP.PPCDPP = HL_PREPA_LIGNE.P1CDPO
AND HL_PRL_LIG_PRP.PPNANP = HL_PREPA_LIGNE.P1NANN
AND HL_PRL_LIG_PRP.PPNLPR = HL_PREPA_LIGNE.P1NLPR
JOIN SD_HLPRELP HL_PRELEVEMENT -- PICK
ON HL_PRELEVEMENT.PVCACT = HL_PRL_LIG_PRP.PPCACT
AND HL_PRELEVEMENT.PVCDPO = HL_PRL_LIG_PRP.PPCDPO
AND HL_PRELEVEMENT.PVNANN = HL_PRL_LIG_PRP.PPNANN
AND HL_PRELEVEMENT.PVNPRL = HL_PRL_LIG_PRP.PPNPRL
AND HL_PRELEVEMENT.PVTVPR = '1'
AND HL_PRELEVEMENT.PVTNPR = '0'
JOIN SD_HLMISSP HL_MISSION -- Pick batch
ON HL_PRELEVEMENT.PVCDPO = HL_MISSION.MICDPO
AND HL_PRELEVEMENT.PVNANN = HL_MISSION.MINANN
AND HL_PRELEVEMENT.PVNMIS = HL_MISSION.MINMIS
AND NOT (MITMVA = 1 AND MITBPM = 0)
JOIN SD_HLLPRGP HL_LIG_PRP_GEI
ON HL_PREPA_LIGNE.P1NANN = HL_LIG_PRP_GEI.LGNANN
AND HL_PREPA_LIGNE.P1NLPR = HL_LIG_PRP_GEI.LGNLPR
AND HL_PREPA_LIGNE.P1CACT = HL_LIG_PRP_GEI.LGCACT
AND HL_PREPA_LIGNE.P1CDPO = HL_LIG_PRP_GEI.LGCDPO
--== NOT DESPATCHED IPG ==--
-- CONNECT BY 'IPG AFTER PICK' WITH PICK
JOIN SD_HLGEINP HL_GEI
-- ON HL_GEI.GENGEI = HL_PRELEVEMENT.PVNGEG
ON HL_LIG_PRP_GEI.LGNGEI = HL_GEI.GENGEI
-- JOIN SD_HLPOPRP HL_POOL_PREPA -- Preparation for preparation pool
-- ON HL_PREPA_ENTETE.PECACT = HL_POOL_PREPA.POPCACT
-- AND HL_PREPA_ENTETE.PECDPO = HL_POOL_PREPA.POPCDPO
-- AND HL_PREPA_ENTETE.PENANN = HL_POOL_PREPA.POPNAPRE
-- AND HL_PREPA_ENTETE.PENPRE = HL_POOL_PREPA.POPNPRE
--
-- JOIN SD_HLPVPOP HL_PRELEV_POOL -- Pick - Preparation pool
-- ON HL_PRL_LIG_PRP.PPCACP = HL_PRELEV_POOL.PPOCACT
-- AND HL_PRL_LIG_PRP.PPCDPP = HL_PRELEV_POOL.PPOCDPO
-- AND HL_PRL_LIG_PRP.PPNANN = HL_PRELEV_POOL.PPONANN
-- AND HL_PRL_LIG_PRP.PPNPRL = HL_PRELEV_POOL.PPONPRL
-- AND HL_POOL_PREPA.POPNANN = HL_PRELEV_POOL.PPONAPOO
-- AND HL_POOL_PREPA.POPNUMPOPR = HL_PRELEV_POOL.PPONUMPOPR
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
AND HL_PREPA_ENTETE.PETVAP = '1' -- Flag - Preparation confirmed
AND HL_PREPA_ENTETE.PETESS = '0' -- Flag - Preparation stock despatch in progress
AND HL_PREPA_ENTETE.PETSOP = '0' -- Flag - Stock despatch made for preparation
),
HL52251 AS(
SELECT DISTINCT 210 AS "SEQ", 4 AS "SEQ2", 2 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52251'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE -- PREPARATION LINE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
HL52299 AS(
SELECT DISTINCT 210 AS "SEQ", 4 AS "SEQ2", 3 AS "SEQ3", CAST(HL_PREPA_LIGNE.P1NLOP || '/' || HL_PREPA_LIGNE.P1NLPR || '/' || HL_PREPA_LIGNE.P1CART AS CHAR(16) ) AS "LINE_NB",
'HL52299'
|| CAST(ACTIVITY_CODE AS CHAR(3)) -- ACTIVITY CODE
|| CAST(DEPOT_CODE AS CHAR(3)) -- PHYSICAL DEPOT CODE
|| CAST(HL_PREPA_LIGNE.P1NANN AS CHAR(2)) -- PREPARATION LINE YEAR NUMBER
|| CAST(LPAD(HL_PREPA_LIGNE.P1NLPR, 13, '0') AS CHAR(13)) -- PREPARATION LINE NUMBER
|| CAST(HL_PREPA_ENTETE.PENANN AS CHAR(2)) -- PREPARATION YEAR NUMBER
|| CAST(LPAD(HL_PREPA_ENTETE.PENPRE, 9, '0') AS CHAR(9)) -- PREPARATION NUMBER
|| CAST (NVL(HL_COMMENTAIRE.COTXTC, ' ') AS CHAR(70)) -- COMMENT
|| CAST (NVL(HL_COMMENTAIRE.COCFCO, ' ') AS CHAR(3)) -- COMMENT GROUP
|| '''' AS LINE
FROM SD_HLPRENP HL_PREPA_ENTETE
JOIN SD_HLPRPLP HL_PREPA_LIGNE -- PREPARATION LINE
ON HL_PREPA_ENTETE.PECACT = HL_PREPA_LIGNE.P1CACT
AND HL_PREPA_ENTETE.PECDPO = HL_PREPA_LIGNE.P1CDPO
AND HL_PREPA_ENTETE.PENANN = HL_PREPA_LIGNE.P1NANP
AND HL_PREPA_ENTETE.PENPRE = HL_PREPA_LIGNE.P1NPRE
LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
ON HL_COMMENTAIRE.CONCOM = HL_PREPA_LIGNE.P1NCOM
AND HL_COMMENTAIRE.COCFCO IN ('LIN', 'OOS', 'CCL', 'EAN', 'DS1', 'DS2', 'TTN', 'PLE', 'PWI', 'PHE', 'PWE', 'PVO')
-- TTN - PackageTrackingNo
WHERE HL_PREPA_ENTETE.PENPRE = ORD_NUM
AND HL_PREPA_ENTETE.PENANN = YEAR_NUM
AND HL_PREPA_ENTETE.PERODP = ORIGINATOR_REF
AND HL_PREPA_ENTETE.PECACT = ACTIVITY_CODE
),
OUT AS (
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52110
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52120
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52130
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52140
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52150
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52160
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52199
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52210
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52220
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52230
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52250
UNION ALL
SELECT SEQ, SEQ2, SEQ3, LINE_NB, LINE FROM HL52299
ORDER BY SEQ, LINE_NB, SEQ2, SEQ3
)
SELECT LPAD(CAST (ROWNUM AS VARCHAR(7)),7,'0')|| LINE AS LINE FROM OUT;
END;Editor is loading...