Skip to Main Content

Application Development Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Debugging

Rajesh LApr 18 2024

Hi,

i am facing some issue when i execute this below packages my data should 9 but it was updated in 6 total
the particular table.
total_carton should be 9, but it was updated 6 TOTAL_CARTON

  AT\_SEQUENCE\_ID    AT\_ACTION    AT\_DATE    AT\_USER\_ID    PKT\_CTRL\_NBR    PICK\_WAVE\_NBR    SHIP\_WAVE\_NBR    WAVE\_STAT\_CODE    WAVE\_SEQ\_NBR    RTE\_WAVE\_NBR    RTE\_SWC\_NBR    STAT\_CODE    STAGE\_INDIC    TOTAL\_CARTON  

1 35082918842 CHANGE 9/4/2024 15:31 wm/geninvoice_GroupA.sh/sqlplus@gaalplpapp008ad (TNS V1-V3)/gaalplpapp008ad 8017765097 202404093450.00 202404093519 30 1 20 0 6
total_carton should be 9, but it was updated 6 TOTAL_CARTON
i have executed two packages here

8017765097
select * from pkt_hdr_intrnl where pkt_ctrl_nbr='8017765097'

--first was i executed this below package

CREATE OR REPLACE PROCEDURE WMS_WH_GHC1.UPS_PRE_INVOICE_SCRIPT(p_pkt_ctrl_nbr pkt_hdr.pkt_ctrl_nbr%type) is
/*************************************************************************************
Change History
--------------------------------------------------------------------------------------
Ver Date Name SCR Reason
--------------------------------------------------------------------------------------
1.0 07/01/2022 Spandana Chelikani 57569 Boston Scientific Outbound Customization
**************************************************************************************/
BEGIN
begin
DECLARE

pkt_dtl_cnt number(6);
crtn_cnt number(6);
crtn_dtl_cnt number(6);
o_pkt_dtl_cnt number(6);
o_crtn_cnt number(6);
o_crtn_dtl_cnt number(6);

p_rc number(4);

v_cd_master_id CD_MASTER.CD_MASTER_ID%TYPE;
v_host VARCHAR2(100);

FUNCTION UPS_CHECK_PIS_CUST_LOGIC(p_whse outpt_pkt_hdr.whse%TYPE, p_cd_master_id IN CD_MASTER.CD_MASTER_ID%TYPE) RETURN BOOLEAN AS
BEGIN

DECLARE
L_RETURN_CODE BOOLEAN := false;
v_count NUMBER(4) := 0;

BEGIN

   /\* use cd\_sys\_code instead of sys\_code \*/

   SELECT count(1) INTO v\_count from cd\_sys\_code where rec\_type = 'C' and code\_type = 'CPI'  
          and  code\_id = p\_whse and cd\_master\_id = p\_cd\_master\_id;

   if( v\_count > 0 )  
   THEN  
       L\_RETURN\_CODE := true;  
   END IF;

   return  L\_RETURN\_CODE;

EXCEPTION  
   WHEN OTHERS THEN  
       RETURN FALSE;  

END;

END;

BEGIN
begin
SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST')
INTO v_host
FROM V$SESSION
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID;

v_host := 'wm@' || v_host;
EXCEPTION
WHEN OTHERS THEN
v_host:='wm@gaalpltapp0180';
END;
FOR i IN (SELECT oph.invc_batch_nbr, oph.pkt_ctrl_nbr,oph.stat_code,oph.create_date_time, oph.whse, oph.co,oph.div,
oph.user_id
FROM outpt_pkt_hdr oph
WHERE (oph.proc_stat_code = 31)
and oph.pkt_ctrl_nbr=p_pkt_ctrl_nbr
ORDER BY oph.create_date_time,oph.proc_stat_code desc)
LOOP
BEGIN
pkt_dtl_cnt :=0;
crtn_cnt :=0;
crtn_dtl_cnt :=0;
o_pkt_dtl_cnt :=0;
o_crtn_cnt :=0;
o_crtn_dtl_cnt :=0;
SELECT COUNT(1)
INTO pkt_dtl_cnt
FROM pkt_dtl
WHERE pkt_ctrl_nbr = i.pkt_ctrl_nbr
AND stat_code != 99;
SELECT COUNT(1)
INTO o_pkt_dtl_cnt
FROM outpt_pkt_dtl
WHERE pkt_ctrl_nbr = i.pkt_ctrl_nbr
AND invc_batch_nbr = i.invc_batch_nbr;
IF i.stat_code = 10 AND o_pkt_dtl_cnt <> pkt_dtl_cnt AND pkt_dtl_cnt > 0 THEN
DELETE FROM outpt_pkt_dtl
WHERE pkt_ctrl_nbr = i.pkt_ctrl_nbr
AND invc_batch_nbr = i.invc_batch_nbr;

 INSERT INTO  OUTPT\_PKT\_DTL  
 (SELECT i.invc\_batch\_nbr, pd.PKT\_CTRL\_NBR, pd.PKT\_SEQ\_NBR, i.co, i.div,  
         im.SIZE\_REL\_POSN\_IN\_TABLE, im.SEASON, orig.season ORIG\_SEASON, im.SEASON\_YR,  
         orig.season\_yr ORIG\_SEASON\_YR, im.STYLE, orig.style ORIG\_STYLE, im.STYLE\_SFX,  
         orig.style\_sfx ORIG\_STYLE\_SFX, im.COLOR, orig.color as ORIG\_COLOR, im.COLOR\_SFX,  
         orig.color\_sfx ORIG\_COLOR\_SFX, im.SEC\_DIM,  
         orig.sec\_dim as ORIG\_SEC\_DIM, im.QUAL, orig.qual ORIG\_QUAL, pd.BATCH\_NBR, pd.INVN\_TYPE,  
         pd.PROD\_STAT, pd.SKU\_ATTR\_1, pd.SKU\_ATTR\_2, pd.SKU\_ATTR\_3,  
         pd.SKU\_ATTR\_4, pd.SKU\_ATTR\_5,  
         pd.CNTRY\_OF\_ORGN, pd.orig\_PKT\_QTY, pd.units\_pakd as SHPD\_QTY,  
         pd.RSN\_CODE, im.SIZE\_RANGE\_CODE, im.SIZE\_DESC, orig.size\_desc as ORIG\_SIZE\_DESC,  
         pd.UPC\_PRE\_DIGIT, pd.UPC\_VENDOR\_CODE,  
         pd.UPC\_SRL\_PROD\_NBR, pd.UPC\_POST\_DIGIT, AREA, ZONE, AISLE, BAY, LVL, POSN,  
         pd.SPL\_INSTR\_CODE\_1, pd.SPL\_INSTR\_CODE\_2, pd.SPL\_INSTR\_CODE\_3,  
         pd.SPL\_INSTR\_CODE\_4, pd.SPL\_INSTR\_CODE\_5, null as MISC\_INSTR\_10\_BYTE\_1, null as  MISC\_INSTR\_10\_BYTE\_2, null as BATCH\_CTRL\_NBR, '' REC\_XPANS\_FIELD  
         , null,    sysdate, 0, pd.ASSORT\_NBR, pd.CANCEL\_QTY, pd.LINE\_TYPE, pd.ORIG\_ORD\_LINE\_NBR,  
         pd.ORIG\_PKT\_LINE\_NBR, pd.ORIG\_ORD\_QTY, pd.ORIG\_PKT\_QTY, pd.PRICE, pd.RETAIL\_PRICE, pd.SKU\_ID, orig.SKU\_ID,  
         decode(pd.UNIT\_WT,0,im.unit\_wt,pd.unit\_wt), decode(pd.UNIT\_VOL,0,im.unit\_vol,pd.unit\_vol),  
         nvl(pd.UOM,im.dsp\_qty\_uom), nvl(pd.TEMP\_ZONE,im.temp\_zone), pd.BACK\_ORD\_QTY,  
         decode(pd.SHELF\_DAYS,0,iwm.shelf\_days,pd.shelf\_days),  
         sd.DISTRO\_NBR, nvl(sd.seq\_nbr,0),  
         sd.DISTRO\_TYPE, null PO\_NBR, pd.PPACK\_GRP\_CODE, pd.PPACK\_QTY, null TMS\_PO\_PKT, EXP\_INFO\_CODE, CUST\_PO\_LINE\_NBR,  
         sysdate, sysdate, i.USER\_ID  
    FROM pkt\_dtl pd  
    INNER JOIN pkt\_hdr ph ON ph.pkt\_ctrl\_nbr = pd.pkt\_ctrl\_nbr  
    INNER JOIN item\_master im ON im.Cd\_Master\_Id = ph.cd\_master\_id  
                             AND im.sku\_id = pd.sku\_id  
    LEFT OUTER JOIN item\_master orig ON orig.sku\_id = im.sku\_id  
    INNER JOIN item\_whse\_master iwm ON iwm.sku\_id = pd.sku\_id  
                                   AND iwm.whse = ph.whse  
    LEFT OUTER JOIN store\_distro sd ON  sd.PKT\_CTRL\_NBR = pd.pkt\_ctrl\_nbr  
                                   AND sd.pkt\_seq\_nbr = pd.pkt\_seq\_nbr  
    LEFT OUTER JOIN locn\_hdr lh ON lh.locn\_id = pd.pick\_locn\_id  
   WHERE    pd.pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr AND i.stat\_code = 10  
   );  
 COMMIT;  

END IF;
IF i.stat_code >= 20 THEN
SELECT COUNT(1)
INTO crtn_cnt
FROM carton_hdr WHERE pkt_ctrl_nbr = i.pkt_ctrl_nbr
AND stat_code != 99; /* check count of cartons created after split */
-- AND create_date_time <=i.create_date_time;
SELECT COUNT(1)
INTO o_crtn_cnt
FROM outpt_carton_hdr
WHERE pkt_ctrl_nbr = i.pkt_ctrl_nbr
AND invc_batch_nbr = i.invc_batch_nbr;

 --  AND carton\_nbr IN (SELECT carton\_nbr  
 --                       FROM carton\_hdr  
 --                      WHERE stat\_code != 99);

SELECT count(cd.carton\_seq\_nbr)  
  INTO crtn\_dtl\_cnt  
  FROM carton\_dtl cd,carton\_hdr ch  
 WHERE cd.pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND cd.carton\_nbr = ch.carton\_nbr  
   AND ((ch.stat\_code \<= 10 AND cd.units\_pakd = 0) OR (ch.stat\_code >= 20 AND cd.units\_pakd > 0)  
   OR (ch.stat\_code = 15 AND cd.to\_be\_pakd\_units > 0))  
   AND ch.stat\_code != 99;                         /\* check count of cartons created after split \*/  
   --  AND cd.create\_date\_time \<=i.create\_date\_time ;

 SELECT count(1) INTO o\_crtn\_dtl\_cnt  
   FROM outpt\_carton\_dtl  
  WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
    AND invc\_batch\_nbr = i.invc\_batch\_nbr;  
 --   AND carton\_nbr IN  
 --      (SELECT carton\_nbr  
 --         FROM carton\_hdr  
 --        WHERE stat\_code != 99);


select cm.cd\_master\_id into v\_cd\_master\_id from whse\_master wm, wcd\_master wcm, cd\_master cm  
           where wm.whse = i.whse  
           and wm.whse\_master\_id = wcm.whse\_master\_id  
           and wcm.cd\_master\_id = cm.cd\_master\_id  
           and cm.co = i.co  
           and cm.div = i.div;

/* if carton(s) has been split or combined, re-create pre-invoice data */

   /\* Check for ELS here \*/

IF (UPS_CHECK_PIS_CUST_LOGIC(i.whse, v_cd_master_id) AND ((pkt_dtl_cnt != o_pkt_dtl_cnt) OR ( crtn_cnt != o_crtn_cnt) OR ( crtn_dtl_cnt != o_crtn_dtl_cnt)
) ) THEN

   insert into ups\_pre\_invoice\_log  
           values( i.pkt\_ctrl\_nbr, pkt\_dtl\_cnt, o\_pkt\_dtl\_cnt, crtn\_cnt, o\_crtn\_cnt, crtn\_dtl\_cnt, o\_crtn\_dtl\_cnt, sysdate, sysdate);

   /\* pass cd\_master\_id and crtn\_cnt \*/

   ups\_pre\_invoice\_data(i.pkt\_ctrl\_nbr, i.whse, i.co, i.div, v\_cd\_master\_id, crtn\_cnt, i.invc\_batch\_nbr, (pkt\_dtl\_cnt != o\_pkt\_dtl\_cnt),  
           ( crtn\_cnt != o\_crtn\_cnt) OR ( crtn\_dtl\_cnt != o\_crtn\_dtl\_cnt), p\_rc);


SELECT COUNT(1)  
  INTO pkt\_dtl\_cnt  
  FROM pkt\_dtl  
  WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
  AND stat\_code != 99;

SELECT COUNT(1)  
  INTO o\_pkt\_dtl\_cnt  
  FROM outpt\_pkt\_dtl  
  WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
  AND invc\_batch\_nbr = i.invc\_batch\_nbr;


SELECT COUNT(1)  
  INTO crtn\_cnt  
  FROM carton\_hdr WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND stat\_code != 99;                            /\* check count of cartons created after split \*/  
   -- AND create\_date\_time \<=i.create\_date\_time;

SELECT COUNT(1)  
  INTO o\_crtn\_cnt  
  FROM outpt\_carton\_hdr  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;

 --  AND carton\_nbr IN (SELECT carton\_nbr  
 --                       FROM carton\_hdr  
 --                      WHERE stat\_code != 99);

SELECT count(cd.carton\_seq\_nbr)  
  INTO crtn\_dtl\_cnt  
  FROM carton\_dtl cd,carton\_hdr ch  
 WHERE cd.pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND cd.carton\_nbr = ch.carton\_nbr  
   AND ((ch.stat\_code \<= 10 AND cd.units\_pakd = 0) OR (ch.stat\_code >= 20)  /\*  remove UNITS\_PAKD > 0 check \*/  
   OR (ch.stat\_code = 15 AND cd.to\_be\_pakd\_units > 0))  
   AND ch.stat\_code != 99;                         /\* check count of cartons created after split \*/  
   --  AND cd.create\_date\_time \<=i.create\_date\_time ;

 SELECT count(1) INTO o\_crtn\_dtl\_cnt  
   FROM outpt\_carton\_dtl  
  WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
    AND invc\_batch\_nbr = i.invc\_batch\_nbr;  
 --   AND carton\_nbr IN  
 --      (SELECT carton\_nbr  
 --         FROM carton\_hdr  
 --        WHERE stat\_code != 99);  

END IF;

   END IF;

IF (pkt_dtl_cnt = o_pkt_dtl_cnt) AND ( crtn_cnt = o_crtn_cnt) AND ( crtn_dtl_cnt = o_crtn_dtl_cnt) THEN

UPDATE outpt\_pkt\_hdr  
   SET proc\_stat\_code = 89  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;  
UPDATE outpt\_pkt\_dtl  
   SET proc\_stat\_code = 89  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;

UPDATE outpt\_carton\_hdr  
   SET proc\_stat\_code = 89  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;  
UPDATE outpt\_carton\_dtl  
   SET proc\_stat\_code = 89  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;

   COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

     UPS\_SEND\_MAIL (v\_host,'upsusgwsdev@ups.com','UPS\_PRE\_INVOICE\_HC EXCEPTION','PKT:'||i.pkt\_ctrl\_nbr||',BATCH:'||i.invc\_batch\_nbr||',STAT\_CODE:'||i.stat\_code||','||substr(sqlerrm,1,200));

UPDATE outpt\_pkt\_hdr  
   SET proc\_stat\_code = -88  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;  
UPDATE outpt\_pkt\_dtl  
   SET proc\_stat\_code = -88  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;

UPDATE outpt\_carton\_hdr  
   SET proc\_stat\_code = -88  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;  
UPDATE outpt\_carton\_dtl  
   SET proc\_stat\_code = -88  
 WHERE pkt\_ctrl\_nbr = i.pkt\_ctrl\_nbr  
   AND invc\_batch\_nbr = i.invc\_batch\_nbr;

COMMIT;

END;
COMMIT;

END LOOP;
COMMIT;
END;
end;

------------Update Pro Number---------------------
UPDATE OUTPT_PKT_HDR OPH
SET OPH.PRO_NBR = (SELECT (CASE WHEN OUTBD_STOP.PRO_NBR IS NULL THEN OUTBD_LOAD.PRO_NBR ELSE OUTBD_STOP.PRO_NBR END)PRONBR
FROM OUTBD_LOAD, OUTBD_STOP
WHERE OUTBD_LOAD.WHSE = OPH.WHSE AND OUTBD_LOAD.LOAD_NBR = OPH.PLAN_LOAD_NBR
AND OPH.PLAN_SHPMT_NBR = OUTBD_STOP.SHPMT_NBR AND OPH.PLAN_LOAD_NBR = OUTBD_STOP.LOAD_NBR
AND OUTBD_LOAD.LOAD_NBR = OUTBD_STOP.LOAD_NBR AND OPH.PLAN_BOL = OUTBD_STOP.BOL)
WHERE EXISTS
(SELECT 1 FROM CD_SYS_CODE CSC, PKT_HDR PH
WHERE CSC.CODE_TYPE = 'PRO' AND CSC.REC_TYPE = 'C'
AND CSC.MISC_FLAGS = 'Y' AND PH.PKT_CTRL_NBR = OPH.PKT_CTRL_NBR
AND CSC.CODE_ID = PH.WHSE AND CSC.CD_MASTER_ID = PH.CD_MASTER_ID)
AND EXISTS
(SELECT 1 FROM SHIP_VIA, CARR_HDR
WHERE SHIP_VIA.CARR_ID = CARR_HDR.CARR_ID
AND SHIP_VIA.SHIP_VIA = OPH.SHIP_VIA AND CARR_HDR.CARR_TYPE >= 50)
AND EXISTS
(SELECT 1 FROM PKT_HDR_INTRNL PHI
WHERE PHI.PKT_CTRL_NBR = OPH.PKT_CTRL_NBR
AND PHI.STAT_CODE = 90) and oph.proc_stat_code = 89
and oph.mod_date_time > sysdate - 1/24;

COMMIT;

-- Fix for Merck e-coding issue
UPDATE OUTPT_CARTON_HDR OCH
SET OCH.PLT_EPC = (SELECT CARTON_HDR.REF_CASE_NBR
FROM CARTON_HDR
WHERE OCH.CARTON_NBR = CARTON_HDR.CARTON_NBR)
WHERE EXISTS (SELECT 1
FROM CD_SYS_CODE CSC, CARTON_HDR CH
WHERE CSC.CODE_TYPE = 'ECD'
AND CSC.REC_TYPE = 'C'
AND CSC.MISC_FLAGS = 'Y'
AND CH.PKT_CTRL_NBR = OCH.PKT_CTRL_NBR
AND CSC.CODE_ID = CH.WHSE
AND CSC.CD_MASTER_ID = CH.CD_MASTER_ID)
AND EXISTS (SELECT 1
FROM PKT_HDR_INTRNL PHI
WHERE PHI.PKT_CTRL_NBR = OCH.PKT_CTRL_NBR
AND PHI.STAT_CODE = 90)
AND OCH.PROC_STAT_CODE = 89
AND OCH.MOD_DATE_TIME > SYSDATE - 1 / 24;

commit;

---Merck Carton Dim Update##########
update outpt_carton_hdr och set CARTON_EPC = (select case when CARTON_SIZE is null then LEN||'|'||WIDTH||'|'||HT ELSE
(Select LEN||'|'||WIDTH||'|'||HT from CNTR_TYPE ct where ct.CNTR_TYPE = och.CARTON_TYPE and ct.CNTR_SIZE = och.CARTON_SIZE
and ct.whse = och.whse) END
from
carton_hdr ch where ch.carton_nbr = och.carton_nbr) where
proc_stat_code = 89 and exists (
select 1 from pkt_hdr ph, cd_sys_code dim
where dim.code_type='DIM' AND dim.REC_TYPE='C'
AND dim.CD_MASTER_ID = PH.CD_MASTER_ID AND dim.code_id=PH.WHSE and ph.pkt_ctrl_nbr = och.pkt_ctrl_nbr );

commit;

----------
--serial number updates
-- copied code block from N-HC
begin
declare
v_errmsg varchar2(32767);
v_host VARCHAR2(100);
begin
begin
SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST')
INTO v_host
FROM V$SESSION
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID;

v_host := 'wm@' || v_host;
EXCEPTION
WHEN OTHERS THEN
v_host:='wm@gaalpltapp0180';
END;
/* fetch outpt_pkt_hdr with SRL_NBR_TRACK in status 70
UNION ALL
fetch outpt_pkt_hdr with mismatch in SRL_NBR_TRACK and UNITS_PAKD */
for i in ( select och.invc_batch_nbr,och.carton_nbr,ocd.carton_seq_nbr,
ocd.sku_id, ocd.pkt_ctrl_nbr,ocd.pkt_seq_nbr,
och.whse, sum(ocd.units_pakd) units_pakd,oph.stat_code pkt_stat
,70 as stat_code
from
outpt_carton_dtl ocd, outpt_carton_hdr och, outpt_pkt_hdr oph
where
och.proc_stat_code = 89
and oph.proc_stat_code = 89
--and och.co='GNN'
AND oph.pkt_ctrl_nbr = och.pkt_ctrl_nbr
AND oph.invc_batch_nbr = och.invc_batch_nbr
AND och.invc_batch_nbr = ocd.invc_batch_nbr
AND ocd.carton_nbr = och.carton_nbr
-- and oph.stat_code = 40
and ocd.proc_stat_code =89
and oph.stat_code != 90 /* logic for PRE-INVOICE ONLY */
AND exists ( select 1 from srl_nbr_track snt where ocd.sku_id = snt.sku_id and ocd.pkt_ctrl_nbr = snt.pkt_ctrl_nbr
and ocd.pkt_seq_nbr = snt.pkt_seq_nbr and och.carton_nbr = snt.carton_nbr
-- and ocd.carton_seq_nbr = snt.carton_seq_nbr
and och.whse=snt.whse and snt.stat_code != 99 )
group by och.invc_batch_nbr,och.carton_nbr,ocd.carton_seq_nbr,ocd.sku_id, ocd.pkt_ctrl_nbr,ocd.pkt_seq_nbr, och.whse, oph.stat_code
order by invc_batch_nbr
)
loop
begin

delete from outpt_carton_srl_nbr where carton_nbr = i.carton_nbr and invc_batch_nbr = i.invc_batch_nbr
and srl_nbr IN (SELECT srl_nbr FROM srl_nbr_track where carton_nbr = i.carton_nbr and carton_seq_nbr = i.carton_seq_nbr
and pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr );

for j in (select rownum myrow,snt.* from srl_nbr_track snt where
i.sku_id = snt.sku_id and i.pkt_ctrl_nbr = snt.pkt_ctrl_nbr
and i.pkt_seq_nbr = snt.pkt_seq_nbr and i.carton_nbr = snt.carton_nbr and i.carton_seq_nbr = snt.carton_seq_nbr and i.whse=snt.whse and snt.stat_code != 99
and rownum < i.units_pakd + 1 AND user_id <>'SRLUPD')
loop

/* create outpt_carton_srl_nbr */
Insert into OUTPT_CARTON_SRL_NBR
(INVC_BATCH_NBR, CARTON_NBR, CARTON_SEQ_NBR, seq_nbr, SRL_NBR, QTY, CREATE_DATE_TIME, MOD_DATE_TIME, USER_ID)
Values
(i.invc_batch_nbr, i.carton_nbr, j.carton_seq_nbr, j.seq_nbr, j.srl_nbr,
1, sysdate, sysdate, 'SRLUPD');

update srl_nbr_track set carton_seq_nbr = j.carton_seq_nbr, user_id = 'SRLUPD', mod_date_time = sysdate where srl_nbr = j.srl_nbr
and carton_nbr = i.carton_nbr and pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr and whse = i.whse;

end loop;

UPDATE srl_nbr_track
SET user_id ='WM'
WHERE carton_nbr = i.carton_nbr and carton_seq_nbr = i.carton_seq_nbr and pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr and whse = i.whse
AND user_id ='SRLUPD';

commit;
exception when others then
v_errmsg := 'Serial nbr updates failed for carton :' || i.carton_nbr || ' sku id : ' || i.sku_id
|| UTL_TCP.crlf
|| ' Error is : ' || sqlerrm
|| UTL_TCP.crlf
|| ' Leaving the pkt ' || i.pkt_ctrl_nbr || ' in Unprocessed Status ';

ups_msg_handler_pkg.exception_msg_log_insert('OUTPT_SERIAL_UPD',
v_errmsg);

UPS_SEND_MAIL (v_host,'upsgwsimp@ups.com, upsusgwsdev@ups.com','HC-Output Serial Updates Failed',v_errmsg);
rollback;
update outpt_pkt_hdr set proc_stat_code = -87 where pkt_ctrl_nbr = i.pkt_ctrl_nbr;
update outpt_carton_hdr set proc_stat_code = -87 where carton_nbr = i.carton_nbr;
commit;
end;
end loop;
end;
end;

----------
--serial number updates
-- copied code block from N-HC
begin
declare
v_errmsg varchar2(32767);
v_host VARCHAR2(100);
begin
begin
SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST')
INTO v_host
FROM V$SESSION
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID;

v_host := 'wm@' || v_host;
EXCEPTION
WHEN OTHERS THEN
v_host:='wm@gaalpltapp0180';
END;
/* fetch outpt_pkt_hdr with SRL_NBR_TRACK in status 70
UNION ALL
fetch outpt_pkt_hdr with mismatch in SRL_NBR_TRACK and UNITS_PAKD */
for i in (
select och.invc_batch_nbr,och.carton_nbr,ocd.carton_seq_nbr,rownum myrow,ocd.sku_id,ocd.pkt_ctrl_nbr,ocd.pkt_seq_nbr, och.whse,och.user_id,ocd.units_pakd
,oph.stat_code pkt_stat, 90 as stat_code
from
outpt_carton_dtl ocd,outpt_carton_hdr och,outpt_pkt_hdr oph
where och.proc_stat_code =89
and ocd.carton_nbr = och.carton_nbr
--and och.co='GNN'
AND och.invc_batch_nbr = ocd.invc_batch_nbr
AND oph.pkt_ctrl_nbr = och.pkt_ctrl_nbr
AND oph.invc_batch_nbr = och.invc_batch_nbr
and ocd.proc_stat_code = 89
and oph.proc_stat_code = 89
and oph.stat_code =90
and exists ( select 1 from srl_nbr_track snt,outpt_carton_dtl ocd1 where ocd.carton_nbr = ocd1.carton_nbr and ocd1.sku_id = ocd.sku_id
and ocd1.proc_stat_code = 89 and
ocd.sku_id = snt.sku_id and ocd.pkt_ctrl_nbr = snt.pkt_ctrl_nbr
and ocd.pkt_seq_nbr = snt.pkt_seq_nbr and och.carton_nbr = snt.carton_nbr and ocd1.carton_seq_nbr = snt.carton_seq_nbr and
och.whse=snt.whse and (snt.stat_code = 90 AND oph.stat_code =90)
group by ocd1.carton_nbr,ocd1.sku_id,ocd1.carton_seq_nbr,ocd1.units_pakd having count(distinct snt.srl_nbr) <> ocd1.units_pakd )
order by invc_batch_nbr , och.carton_nbr,ocd.carton_seq_nbr
)
loop
begin
/* delete outpt_carton_srl_nbr only for Ship confirms */

-- if i.myrow = 1 then

delete from outpt_carton_srl_nbr where carton_nbr = i.carton_nbr and invc_batch_nbr = i.invc_batch_nbr
AND srl_nbr IN (SELECT srl_nbr FROM srl_nbr_track where carton_nbr = i.carton_nbr and carton_seq_nbr = i.carton_seq_nbr
AND pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr );

update srl_nbr_track set stat_code = 70 where carton_nbr = i.carton_nbr
and carton_seq_nbr = i.carton_seq_nbr
AND stat_code <> 70 and stat_code <> 99
AND pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr;

-- end if;

for j in (select rownum myrow,snt.* from srl_nbr_track snt where
i.sku_id = snt.sku_id and i.pkt_ctrl_nbr = snt.pkt_ctrl_nbr
and i.pkt_seq_nbr = snt.pkt_seq_nbr and i.carton_nbr = snt.carton_nbr
and i.carton_seq_nbr = snt.carton_seq_nbr and i.whse = snt.whse and snt.stat_code =70
-- and rownum < i.units_pakd + 1
AND user_id <>'SRLUPD')
loop

/* create outpt_carton_srl_nbr */
Insert into OUTPT_CARTON_SRL_NBR
(INVC_BATCH_NBR, CARTON_NBR, CARTON_SEQ_NBR, seq_nbr, SRL_NBR, QTY, CREATE_DATE_TIME, MOD_DATE_TIME, USER_ID)
Values
(i.invc_batch_nbr, i.carton_nbr, j.carton_seq_nbr, j.seq_nbr, j.srl_nbr,
1, sysdate, sysdate, i.user_id);

/* update to status 90 only for ship confirms */
update srl_nbr_track set carton_seq_nbr = j.carton_seq_nbr,stat_code = 90, user_id = 'SRLUPD', mod_date_time = sysdate
where srl_nbr = j.srl_nbr
and carton_nbr = i.carton_nbr and pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr and whse = i.whse;

end loop;

UPDATE srl_nbr_track
SET user_id ='WM'
WHERE carton_nbr = i.carton_nbr and carton_seq_nbr = i.carton_seq_nbr
AND pkt_ctrl_nbr = i.pkt_ctrl_nbr and pkt_seq_nbr =i.pkt_seq_nbr and whse = i.whse
AND user_id ='SRLUPD';

commit;
exception when others then

v_errmsg := 'Serial nbr updates failed for carton :' || i.carton_nbr || ' sku id : ' || i.sku_id
|| UTL_TCP.crlf
|| ' Error is : ' || sqlerrm
|| UTL_TCP.crlf
|| ' Leaving the pkt ' || i.pkt_ctrl_nbr || ' in Unprocessed Status ';

ups_msg_handler_pkg.exception_msg_log_insert('OUTPT_SERIAL_UPD',
v_errmsg);

UPS_SEND_MAIL (v_host,'upsgwsimp@ups.com, upsusgwsdev@ups.com','HC-Output Serial Updates Failed',v_errmsg);
rollback;
update outpt_pkt_hdr set proc_stat_code = -87 where pkt_ctrl_nbr = i.pkt_ctrl_nbr;
update outpt_carton_hdr set proc_stat_code = -87 where carton_nbr = i.carton_nbr;
commit;
end;
end loop;
end;
end;

---------

--Update the proc stat code to 31 before processing
update outpt_pkt_hdr set proc_stat_code = 31 where proc_stat_code = 89 and co||div in (select co||div from cd_master where cd_master_id in (select cd_master_id from cd_sys_code where code_type = 'OCP' and rec_type ='C' and NVL(substr(misc_flags,1,1),'N')='Y'));
update outpt_pkt_dtl set proc_stat_code = 31 where proc_stat_code = 89 and co||div in (select co||div from cd_master where cd_master_id in (select cd_master_id from cd_sys_code where code_type = 'OCP' and rec_type ='C' AND NVL(substr(misc_flags,1,1),'N')='Y'));
update outpt_carton_hdr set proc_stat_code = 31 where proc_stat_code = 89 and co||div in (select co||div from cd_master where cd_master_id in (select cd_master_id from cd_sys_code where code_type = 'OCP' and rec_type ='C' AND NVL(substr(misc_flags,1,1),'N')='Y'));
update outpt_carton_dtl set proc_stat_code = 31 where proc_stat_code = 89 and co||div in (select co||div from cd_master where cd_master_id in (select cd_master_id from cd_sys_code where code_type = 'OCP' and rec_type ='C' AND NVL(substr(misc_flags,1,1),'N')='Y'));
commit;
END;

--second one i have exceute this below packages

CREATE OR REPLACE PROCEDURE WMS_WH_GHC1.ups_pre_invoice_data(p_pkt_ctrl_nbr pkt_hdr.pkt_ctrl_nbr%TYPE,
p_whse outpt_pkt_hdr.whse%TYPE,
p_co outpt_pkt_hdr.co%TYPE,
p_div outpt_pkt_hdr.div%TYPE,
p_cd_master_id cd_master.cd_master_id%TYPE, /* new parameter */
p_crtn_cnt outpt_pkt_hdr.total_nbr_of_carton%TYPE, /* new parameter */
p_invc_batch_nbr outpt_pkt_hdr.INVC_BATCH_NBR%TYPE,
p_delete_pkt_dtl boolean,
p_delete_ctn boolean,
p_rc out number) AS

/*********************************************************************************************************
Date Author Revision Description
***********************************************************************************************************

10/29/2014 Sanjay Verma 1.1 SCR 34176 Added logic to check if carton counts match and re-create
output carton data
11/11/2014 Sanjay Verma 1.2 SCR 34176 Corrected issue with Split/Combine cartons

01/05/2015 Sanjay Verma 1.3 SCR 34600 Fixed defect - some Invoice messages sent without carton info

02/20/2015 Sanjay Verma 1.4 SCR 34993 Change for defect
OUTPT_CARTON_HDR lines get deleted for picktickets with
same invc_batch_nbr.
ToTAL_NBR_OF_CARTON in PackSlip is incorrect

**********************************************************************************************************/

v_whse outpt_pkt_hdr.whse%TYPE;
v_co outpt_pkt_hdr.co%TYPE;
v_div outpt_pkt_hdr.div%TYPE;

v_user_id outpt_pkt_hdr.user_id%TYPE;
v_stat_code pkt_hdr_intrnl.stat_code%TYPE;
v_invc_batch_nbr outpt_pkt_hdr.invc_batch_nbr%type := 0;
v_major_pkt_ctrl_nbr outpt_pkt_hdr.major_pkt_ctrl_nbr%TYPE;
p_total_nbr_of_units outpt_pkt_hdr.total_nbr_of_units%TYPE;
p_total_nbr_of_plt outpt_pkt_hdr.total_nbr_of_plt%TYPE;
p_host_flag outpt_pkt_hdr.SPL_INSTR_CODE_10%TYPE;

p_shpng_chrg outpt_pkt_hdr.SHPNG_CHRG%TYPE;
p_hndl_chrg outpt_pkt_hdr.HNDL_CHRG%TYPE;
p_insur_chrg outpt_pkt_hdr.INSUR_CHRG%TYPE;
p_tax_chrg outpt_pkt_hdr.TAX_CHRG%TYPE;
p_misc_chrg outpt_pkt_hdr.MISC_CHRG%TYPE;
p_total_carton outpt_pkt_hdr.total_nbr_of_carton%TYPE;
p_total_wt outpt_pkt_hdr.total_wt%TYPE;
p_ship_date_time outpt_pkt_hdr.ship_date_time%TYPE;
p_pkt_prt_date_time outpt_pkt_hdr.pkt_prt_date%TYPE;

v_errmsg VARCHAR2(500);

BEGIN

select whse,
co,
div,
user_id,
stat_code,
major_pkt_ctrl_nbr,
total_nbr_of_units,
total_nbr_of_plt,
SPL_INSTR_CODE_10,
total_nbr_of_carton,
total_wt,
ship_date_time,
pkt_prt_date
INTO v_whse,
v_co,
v_div,
v_user_id,
v_stat_code,
v_major_pkt_ctrl_nbr,
p_total_nbr_of_units,
p_total_nbr_of_plt,
p_host_flag,
p_total_carton,
p_total_wt,
p_ship_date_time,
p_pkt_prt_date_time
from outpt_pkt_hdr
where pkt_ctrl_nbr = p_pkt_ctrl_nbr
and whse = p_whse
and co = p_co
and div = p_div
and INVC_BATCH_NBR = p_invc_batch_nbr;

BEGIN

if(p_delete_ctn) THEN

/* delete based on pkt_ctrl_nbr and invc_batch_nbr */

delete from outpt_carton_srl_nbr where invc_batch_nbr = p_invc_batch_nbr and carton_nbr in
( select carton_nbr from outpt_carton_hdr where invc_batch_nbr = p_invc_batch_nbr and pkt_ctrl_nbr = p_pkt_ctrl_nbr ) ;

delete from outpt_carton_dtl where invc_batch_nbr = p_invc_batch_nbr and pkt_ctrl_nbr = p_pkt_ctrl_nbr;
delete from outpt_carton_hdr where invc_batch_nbr = p_invc_batch_nbr and pkt_ctrl_nbr = p_pkt_ctrl_nbr;

END IF;

if(p_delete_pkt_dtl) THEN

/* delete based on pkt_ctrl_nbr and invc_batch_nbr */

 delete from outpt\_pkt\_dtl where invc\_batch\_nbr = p\_invc\_batch\_nbr and pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr;

END IF;

p_rc := 0;

if(p_delete_ctn) THEN

/* update pkt_hdr.total_nbr_of_carton and pkt_hdr_intrnl.total_carton */

 update outpt\_pkt\_hdr set total\_nbr\_of\_carton = p\_crtn\_cnt  
           where invc\_batch\_nbr = p\_invc\_batch\_nbr and pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr;

 update pkt\_hdr\_intrnl set total\_carton = p\_crtn\_cnt  
           where pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr;

begin
insert into outpt_carton_dtl ocd
(select p_invc_batch_nbr,
cd.CARTON_NBR,
CARTON_SEQ_NBR,
v_co,
v_div,
cd.PKT_CTRL_NBR,
cd.PKT_SEQ_NBR,
SEASON,

           SEASON\_YR,  
           STYLE,  
           STYLE\_SFX,  
           COLOR,  
           COLOR\_SFX,  
           SEC\_DIM,  
           QUAL,  
           SIZE\_RANGE\_CODE,  
           SIZE\_REL\_POSN\_IN\_TABLE,  
           SIZE\_DESC,  
           cd.INVN\_TYPE,  
           CUST\_SKU,  
           cd.PROD\_STAT,  
           cd.BATCH\_NBR,  
           cd.SKU\_ATTR\_1,  
           cd.SKU\_ATTR\_2,  
           cd.SKU\_ATTR\_3,

           cd.SKU\_ATTR\_4,  
           cd.SKU\_ATTR\_5,  
           cd.CNTRY\_OF\_ORGN,  
           pd.UPC\_PRE\_DIGIT,  
           pd.UPC\_VENDOR\_CODE,  
           pd.UPC\_SRL\_PROD\_NBR,  
           im.UPC\_POST\_DIGIT,  
           (case  
             when v\_stat\_code >= 40 then  
              cd.UNITS\_PAKD  
             else  
              cd.to\_be\_pakd\_units  
           end),  
           (case  
             when v\_stat\_code >= 40 then  
              cd.UNITS\_PAKD  
             else  
              cd.to\_be\_pakd\_units  
           end) \* im.std\_bundl\_qty BUNDLES\_PAKD,  
           ASSORT\_NBR,  
           null,  
           sysdate,  
           0,  
           VENDOR\_ID,

           cd.VENDOR\_ITEM\_NBR,  
           CONS\_PRTY\_DATE,  
           ph.ORD\_TYPE,  
           cd.SKU\_ID,  
           IN\_STORE\_DATE,  
           pd.PKT\_CTRL\_NBR,  
           pd.PO\_NBR,  
           sysdate,  
           sysdate,

           v\_user\_id  
      from carton\_dtl  cd,  
           pkt\_dtl     pd,  
           item\_master im,  
           pkt\_hdr     ph,  
           carton\_hdr  ch  
     where pd.pkt\_ctrl\_nbr = cd.pkt\_ctrl\_nbr  
       and pd.pkt\_seq\_nbr = cd.pkt\_seq\_nbr  
       and ph.pkt\_ctrl\_nbr = cd.pkt\_ctrl\_nbr  
       and im.sku\_id = cd.sku\_id  
       and cd.pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr  
       and ch.carton\_nbr = cd.carton\_nbr  
       AND ch.stat\_code \<> 99);

 Insert into OUTPT\_CARTON\_HDR  
   select p\_invc\_batch\_nbr,  
          CARTON\_NBR,  
          ch.WHSE,  
          v\_co,  
          v\_div,  
          ch.PKT\_CTRL\_NBR,  
          PKT\_NBR,  
          PKT\_SFX,  
          ORD\_NBR,  
          ORD\_SFX,  
          ph.SOLDTO,  
          ph.SHIPTO,  
          CUST\_PO\_NBR,  
          TRKG\_NBR,  
          CARTON\_TYPE,  
          CARTON\_SIZE,  
          ch.est\_VOL,  
          ch.EST\_WT,  
          ACTL\_WT,

          TOTAL\_QTY,  
          FRT\_CHRG,  
          CARTON\_NBR\_X\_OF\_Y,  
          PLT\_ID,  
          ch.SHPMT\_NBR,  
          ch.BOL,  
          ch.MANIF\_NBR,  
          TRLR\_NBR,  
          ch.SHIP\_VIA,  
          APPT\_NBR,

          NBR\_OF\_TIMES\_APPT\_CHGD,  
          APPT\_DATE,  
          null,  
          (case  
            when ch.load\_nbr is not null then  
             ROW\_NUMber()  
             over(partition by ch.load\_nbr order by carton\_nbr)

            else  
             0  
          end),  
          AUDTR,  
          PIKR,  
          PAKR,  
          MISC\_NUM\_1,  
          MISC\_NUM\_2,  
          MISC\_INSTR\_CODE\_1,  
          MISC\_INSTR\_CODE\_2,  
          MISC\_INSTR\_CODE\_3,  
          MISC\_INSTR\_CODE\_4,  
          MISC\_INSTR\_CODE\_5,  
          SYSDATE,  
          0,  
          LOAD\_NBR,  
          CH.RTE\_ID,  
          LOAD\_DATE\_TIME,  
          MASTER\_BOL,

          PARCL\_SHPMT\_NBR,  
          STORE\_NBR,  
          MISC\_CARTON,  
          BASE\_CHRG,  
          ADDNL\_OPTN\_CHRG,  
          INSUR\_CHRG,  
          ACTL\_CHRG\_AMT,  
          PRE\_BULK\_BASE\_CHRG,

          PRE\_BULK\_ADDNL\_OPTN\_CHRG,  
          PLT\_TYPE,  
          PLT\_SIZE,  
          DIST\_CHRG,  
          SHIP\_DATE\_TIME,  
          EPC\_MATCH\_FLAG,  
          NULL,  
          null,  
          LOADED\_POSN,  
          PLT\_MASTER\_CARTON\_FLAG,  
          sysdate,  
          sysdate,  
          v\_user\_id  
     from carton\_hdr ch  
    INNER JOIN pkt\_hdr ph  
       ON ph.pkt\_ctrl\_nbr = ch.pkt\_ctrl\_nbr  
    WHERE ch.pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr  
      AND ch.stat\_code \<> 99;

exception
when no_data_found then
p_rc := 0;
end;

END IF;

if(p_delete_pkt_dtl) THEN

Insert into OUTPT_PKT_DTL
(select p_invc_batch_nbr,
pd.PKT_CTRL_NBR,
pd.PKT_SEQ_NBR,
v_co,
v_div,
im.SIZE_REL_POSN_IN_TABLE,
im.SEASON,

         orig.season    ORIG\_SEASON,  
         im.SEASON\_YR,  
         orig.season\_yr ORIG\_SEASON\_YR,  
         im.STYLE,  
         orig.style     ORIG\_STYLE,  
         im.STYLE\_SFX,  
         orig.style\_sfx ORIG\_STYLE\_SFX,

         im.COLOR,  
         orig.color     as ORIG\_COLOR,  
         im.COLOR\_SFX,  
         orig.color\_sfx ORIG\_COLOR\_SFX,  
         im.SEC\_DIM,  
         orig.sec\_dim   as ORIG\_SEC\_DIM,  
         im.QUAL,  
         orig.qual      ORIG\_QUAL,  
         pd.BATCH\_NBR,  
         pd.INVN\_TYPE,  
         pd.PROD\_STAT,

         pd.SKU\_ATTR\_1,  
         pd.SKU\_ATTR\_2,  
         pd.SKU\_ATTR\_3,  
         pd.SKU\_ATTR\_4,  
         pd.SKU\_ATTR\_5,  
         pd.CNTRY\_OF\_ORGN,  
         pd.PKT\_QTY,  
         pd.units\_pakd      as SHPD\_QTY,  
         pd.RSN\_CODE,  
         im.SIZE\_RANGE\_CODE,  
         im.SIZE\_DESC,

         orig.size\_desc      as ORIG\_SIZE\_DESC,  
         pd.UPC\_PRE\_DIGIT,  
         pd.UPC\_VENDOR\_CODE,  
         pd.UPC\_SRL\_PROD\_NBR,  
         pd.UPC\_POST\_DIGIT,  
         AREA,  
         ZONE,  
         AISLE,  
         BAY,  
         LVL,  
         POSN,  
         pd.SPL\_INSTR\_CODE\_1,

         pd.SPL\_INSTR\_CODE\_2,  
         pd.SPL\_INSTR\_CODE\_3,  
         pd.SPL\_INSTR\_CODE\_4,  
         pd.SPL\_INSTR\_CODE\_5,  
         null as MISC\_INSTR\_10\_BYTE\_1,  
         null as MISC\_INSTR\_10\_BYTE\_2,  
         null as

         BATCH\_CTRL\_NBR,  
         '' REC\_XPANS\_FIELD,  
         null,  
         sysdate,  
         0,  
         pd.ASSORT\_NBR,  
         pd.CANCEL\_QTY,  
         pd.LINE\_TYPE,  
         pd.ORIG\_ORD\_LINE\_NBR,  
         pd.ORIG\_PKT\_LINE\_NBR,

         pd.ORIG\_ORD\_QTY,  
         pd.ORIG\_PKT\_QTY,  
         pd.PRICE,  
         pd.RETAIL\_PRICE,  
         pd.SKU\_ID,  
         pd.ORIG\_SKU\_ID,  
         decode(pd.UNIT\_WT, 0, im.unit\_wt, pd.unit\_wt),  
         decode

         (pd.UNIT\_VOL, 0, im.unit\_vol, pd.unit\_vol),  
         nvl(pd.UOM, im.dsp\_qty\_uom),  
         nvl(pd.TEMP\_ZONE, im.temp\_zone),  
         pd.BACK\_ORD\_QTY,  
         decode

         (pd.SHELF\_DAYS, 0, iwm.shelf\_days, pd.shelf\_days),  
         sd.DISTRO\_NBR,  
         nvl(sd.seq\_nbr, 0),  
         SD.DISTRO\_TYPE,  
         pd.PO\_NBR,  
         pd.PPACK\_GRP\_CODE,  
         pd.PPACK\_QTY,  
         TMS\_PO\_PKT,  
         EXP\_INFO\_CODE,  
         CUST\_PO\_LINE\_NBR,  
         sysdate,

         sysdate,  
         v\_user\_id  
    from pkt\_dtl pd  
   inner join item\_master im  
      on im.sku\_id = pd.sku\_id  
    left outer join item\_master orig  
      on im.sku\_id = pd.orig\_sku\_id  
   inner join pkt\_hdr ph  
      on ph.pkt\_ctrl\_nbr = pd.pkt\_ctrl\_nbr  
   inner join item\_whse\_master iwm  
      on iwm.sku\_id = pd.sku\_id  
     and iwm.whse = ph.whse  
    left outer join store\_distro sd  
      on sd.PKT\_CTRL\_NBR = pd.pkt\_ctrl\_nbr  
     and sd.pkt\_seq\_nbr = pd.pkt\_seq\_nbr  
    left outer join locn\_hdr lh  
      on lh.locn\_id = pd.pick\_locn\_id  
   where pd.pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr  
     and v\_stat\_code \<> 10  
  union all  
  select p\_invc\_batch\_nbr,  
         pd.PKT\_CTRL\_NBR,  
         pd.PKT\_SEQ\_NBR,  
         v\_co,  
         v\_div,  
         im.SIZE\_REL\_POSN\_IN\_TABLE,  
         im.SEASON,

         orig.season    ORIG\_SEASON,  
         im.SEASON\_YR,  
         orig.season\_yr ORIG\_SEASON\_YR,  
         im.STYLE,  
         orig.style     ORIG\_STYLE,  
         im.STYLE\_SFX,  
         orig.style\_sfx ORIG\_STYLE\_SFX,

         im.COLOR,  
         orig.color     as ORIG\_COLOR,  
         im.COLOR\_SFX,  
         orig.color\_sfx ORIG\_COLOR\_SFX,  
         im.SEC\_DIM,  
         orig.sec\_dim   as ORIG\_SEC\_DIM,  
         im.QUAL,  
         orig.qual      ORIG\_QUAL,  
         pd.BATCH\_NBR,  
         pd.INVN\_TYPE,  
         pd.PROD\_STAT,

         pd.SKU\_ATTR\_1,  
         pd.SKU\_ATTR\_2,  
         pd.SKU\_ATTR\_3,  
         pd.SKU\_ATTR\_4,  
         pd.SKU\_ATTR\_5,  
         pd.CNTRY\_OF\_ORGN,  
         pd.orig\_PKT\_QTY,  
         pd.units\_pakd      as SHPD\_QTY,  
         pd.RSN\_CODE,  
         im.SIZE\_RANGE\_CODE,  
         im.SIZE\_DESC,

         orig.size\_desc      as ORIG\_SIZE\_DESC,  
         pd.UPC\_PRE\_DIGIT,  
         pd.UPC\_VENDOR\_CODE,  
         pd.UPC\_SRL\_PROD\_NBR,  
         pd.UPC\_POST\_DIGIT,  
         AREA,  
         ZONE,  
         AISLE,  
         BAY,  
         LVL,  
         POSN,  
         pd.SPL\_INSTR\_CODE\_1,

         pd.SPL\_INSTR\_CODE\_2,  
         pd.SPL\_INSTR\_CODE\_3,  
         pd.SPL\_INSTR\_CODE\_4,  
         pd.SPL\_INSTR\_CODE\_5,  
         null as MISC\_INSTR\_10\_BYTE\_1,  
         null as MISC\_INSTR\_10\_BYTE\_2,  
         null as

         BATCH\_CTRL\_NBR,  
         '' REC\_XPANS\_FIELD,  
         null,  
         sysdate,  
         0,  
         pd.ASSORT\_NBR,  
         pd.CANCEL\_QTY,  
         pd.LINE\_TYPE,  
         pd.ORIG\_ORD\_LINE\_NBR,  
         pd.ORIG\_PKT\_LINE\_NBR,

         pd.ORIG\_ORD\_QTY,  
         pd.ORIG\_PKT\_QTY,  
         pd.PRICE,  
         pd.RETAIL\_PRICE,  
         pd.SKU\_ID,  
         orig.SKU\_ID,  
         decode(pd.UNIT\_WT, 0, im.unit\_wt, pd.unit\_wt),  
         decode

         (pd.UNIT\_VOL, 0, im.unit\_vol, pd.unit\_vol),  
         nvl(pd.UOM, im.dsp\_qty\_uom),  
         nvl(pd.TEMP\_ZONE, im.temp\_zone),  
         pd.BACK\_ORD\_QTY,  
         decode

         (pd.SHELF\_DAYS, 0, iwm.shelf\_days, pd.shelf\_days),  
         sd.DISTRO\_NBR,  
         nvl(sd.seq\_nbr, 0),  
         DISTRO\_TYPE,  
         null PO\_NBR,  
         pd.PPACK\_GRP\_CODE,  
         pd.PPACK\_QTY,  
         null TMS\_PO\_PKT,  
         EXP\_INFO\_CODE,  
         CUST\_PO\_LINE\_NBR,

         sysdate,  
         sysdate,  
         v\_user\_id  
    from inpt\_pkt\_dtl pd  
   inner join item\_master im  
      on im.Cd\_Master\_Id = p\_Cd\_Master\_Id  
     AND NVL(im.Season, ' ') = NVL(pd.Season, ' ')  
     AND NVL(im.Season\_Yr, ' ') = NVL(pd.Season\_Yr, ' ')  
     AND NVL(im.Style, ' ') = NVL(pd.Style, ' ')  
     AND NVL(im.Style\_Sfx, ' ') = NVL(pd.Style\_Sfx, ' ')  
     AND NVL(im.Color, ' ') = NVL(pd.Color, ' ')  
     AND NVL(im.Color\_Sfx, ' ') = NVL(pd.Color\_Sfx, ' ')  
     AND NVL(im.Sec\_Dim, ' ') = NVL(pd.Sec\_Dim, ' ')  
     AND NVL(im.Qual, ' ') = NVL(pd.Qual, ' ')  
     AND NVL(im.Size\_Desc, ' ') = NVL(pd.Size\_Desc, ' ')  
    left outer join item\_master orig  
      on orig.sku\_id = im.sku\_id  
   inner join pkt\_hdr ph  
      on ph.pkt\_ctrl\_nbr = pd.pkt\_ctrl\_nbr  
   inner join item\_whse\_master iwm  
      on iwm.sku\_id = pd.sku\_id  
     and iwm.whse = ph.whse  
    left outer join store\_distro sd  
      on sd.PKT\_CTRL\_NBR = pd.pkt\_ctrl\_nbr  
     and sd.pkt\_seq\_nbr = pd.pkt\_seq\_nbr  
    left outer join locn\_hdr lh  
      on lh.locn\_id = pd.pick\_locn\_id  
   where pd.pkt\_ctrl\_nbr = p\_pkt\_ctrl\_nbr  
     and v\_stat\_code = 10);

     END IF;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
rollback;
p_rc := 1;
v_errmsg := 'Pre Invoice Extract failed ' || SQLERRM || 'sqlcode :' ||
SQLCODE || ' Whse: ' || v_whse || ' Co: ' || v_co ||
' Div: ' || v_div || 'Data :' || p_pkt_ctrl_nbr ||
'User:' || v_user_id || chr(60) ||
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 200);
/* ups_send_mail('wm@gaalpltapp0051.internal.dns',
l_emaillist,
'Pre Invoice Extract failed',
v_errmsg);
*/

 INSERT INTO msg\_log  
   (msg\_log\_id,  
    module,  
    msg\_id,  
    pgm\_id,  
    msg,  
    create\_date\_time,  
    user\_id,  
    whse,  
    cd\_master\_id,  
    ref\_code\_1,  
    log\_date\_time,  
    ref\_value\_1,  
    mod\_date\_time)  
 VALUES  
   (get\_nxt\_up\_cnt('\*', '054', 1, 10) + 1,  
    'SYSCONTROL',  
    '1001',  
    'PREINVC',  
    v\_errmsg,  
    SYSDATE,  
    'WM',  
    p\_whse,  
    NULL,  
    '11',  
    SYSDATE,  
    p\_pkt\_ctrl\_nbr || 'User:' || v\_user\_id,  
    SYSDATE);

COMMIT;

 raise\_application\_error(-20050, v\_errmsg);  

END;

END;

Comments
Post Details
Added on Apr 18 2024
0 comments
44 views