level-3-support

Task #15868

Updated by Redmine Admin 3 months ago

``` 
 /* 
 Billing Rules: 
 Booking must be accepted by Customs 
 Booking must have atleast 1 active container 
 Booking vessel must be departed 
 Booking must have atleast 1 container loaded. 
 */ 

 WITH B 
 AS 
 ( 
 SELECT B.SHIPPING_AGENT_CODE, B.SHIPPING_LINE_BOOKINGNO BOOKING_NO, V.VESSEL_NAME, B.ATP, V.ACTUAL_DEPARTURE_TIME, 	
 TO_CHAR(MIN(TO_DATE(LOADING_DATE, 'DD-MON-YYYY HH24:MI')), 'DD-MON-YYYY HH24:MI') LAST_LOADING_DATE 
 FROM BOOKING B 
 INNER JOIN OCEAN_COMMON.VOYAGE_HEADER V ON B.ATP = V.ATP 
 INNER JOIN LOADING L ON B.SHIPPING_LINE_BOOKINGNO = L.SHIPPING_LINE_BOOKING_NO 
 WHERE B.APPROVED_AT_CUSTOMS = 'Y' 
 AND TO_DATE(ACTUAL_DEPARTURE_TIME, 'DD-MON-YYYY HH24:MI') between :IN_DATE_FROM and :IN_DATE_TO 
 GROUP BY B.SHIPPING_AGENT_CODE, B.SHIPPING_LINE_BOOKINGNO, V.VESSEL_NAME, B.ATP, V.ACTUAL_DEPARTURE_TIME 
 ) 
 SELECT /*SELECT * FROM B 
 WHERE 1 = 1 
 AND LAST_LOADING_DATE IS NOT NULL; NULL 
 AND BOOKING_NO = 'EBKG09815175';*/ 
 --useful query 
 /*SELECT SELECT DISTINCT N.MANIFESTID, N.HOUSEREFERENCE ATP,  
     CASE  
         WHEN TO_DATE(SUBSTR(N.transactiondate, 1, 19), 'YYYY-MM-DD HH24:MI:SS') < TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') THEN 'BILLED IN PERIOD '||N.BILLING_PERIOD||', SHOULD HAVE BEEN BILLED IN YEAR ' || SUBSTR(N.transactiondate, 1, 4) ||' DEPARTED ON '||V.ACTUAL_DEPARTURE_TIME||' loaded on '||l.loading_date 
         WHEN L.LOADING_ID IS NULL THEN 'OVERBILLED DUR TO MISSIGN LOADING' 
         WHEN V.ACTUAL_DEPARTURE_TIME IS NULL THEN 'OVERBILLED DUR TO MISSING ACTUAL DEPARTURE DATE' 
         ELSE 'OVERBILLING'  
     END AS STATUS 
 FROM NB_EXP_BL N 
 LEFT JOIN B on B.BOOKING_NO = N.MANIFESTID AND LAST_LOADING_DATE IS NOT NULL--AND B.ATP = N.ATP 
 LEFT JOIN LOADING L ON N.MANIFESTID = L.SHIPPING_LINE_BOOKING_NO 
 LEFT JOIN OCEAN_COMMON.VOYAGE_HEADER V ON N.HOUSEREFERENCE = V.ATP 
 WHERE B.BOOKING_NO IS NULL 	
 AND TO_DATE(N.BILLING_PERIOD, 'MON-YYYY') BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') - 1 
 UNION 
 SELECT B.BOOKING_NO, B.ATP, 'UNDERBILLING' 
 FROM B 
 LEFT JOIN NB_EXP_BL N on B.BOOKING_NO = N.MANIFESTID 
 WHERE N.MANIFESTID IS NULL 
 */ 
 ; 
 ```

Back