Team,
I would like to create Global variables to the query to make it more efficient and I am new to PL SQL
Instead of changing the code in all the areas of the query. Can someone help me on this.
Basically, I need a Start_Date and End_Date Variable at the Top, so I change it at one place,
WITH ORDER_DATE AS
(SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT WHERE COMPLETION_STATUS= 'Success' AND FORM_ID IN ('SubmitOrder' , 'SubmitOrderRIM') AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY')
UNION ALL
SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT_HISTORY WHERE COMPLETION_STATUS= 'Success' AND FORM_ID IN ('SubmitOrder', 'SubmitOrderRIM') AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY') )
, PMNT_DATE AS -- Direct Fullfillment Payment but we need to check for other payments
(SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT WHERE COMPLETION_STATUS = 'Success' AND FORM_ID='DFPayment' AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY')
UNION ALL
SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT_HISTORY WHERE COMPLETION_STATUS = 'Success' AND FORM_ID='DFPayment' AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY'))
, SHIP_DATE AS
(SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT WHERE COMPLETION_STATUS = 'Success' AND FORM_ID='CompleteShippingOrder' AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY')
UNION ALL
SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT_HISTORY WHERE COMPLETION_STATUS = 'Success' AND FORM_ID='CompleteShippingOrder' AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY') )
, ACTV_DATE AS
(SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT WHERE COMPLETION_STATUS = 'ActivationCompleted' AND FORM_ID='HandleActivationResponse' AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('30-SEP-2022','DD-MON-YYYY')
UNION ALL
SELECT /*+ PARALLEL(8) */ * FROM TBASSIGNMENT_HISTORY WHERE COMPLETION_STATUS = 'ActivationCompleted' AND FORM_ID='HandleActivationResponse' AND
EXECUTION_DATE BETWEEN TO_DATE('01-SEP-2022','DD-MON-YYYY') AND TO_DATE('330-SEP-2022','DD-MON-YYYY') )
, SALE_DATE AS
(
SELECT /*+ FULL(F) PARALLEL(F, 8) */
SALE_DATE_KEY
, DT.DAY_DT SALE_DATE
, C.CUSTOMER_ID
, S.MASTER_SUBSCRIBER_ID
, S.SUBSCRIBER_MDN
,CURR_SALE_CHNL_LVL_2_DESC
, LOCATION_ID, LOCATION_NM
, D.DEVICE_ID
FROM FAS_DEVICE_LIFECYCLE@DWPRD F
INNER JOIN DIM_LOCATION@DWPRD L ON F.SALE_LOCATION_KEY = L.LOCATION_KEY
INNER JOIN DIM_CUSTOMER@DWPRD C ON F.CUSTOMER_KEY = C.CUSTOMER_KEY
INNER JOIN DIM_DEVICE@DWPRD D ON D.DEVICE_KEY = F.DEVICE_KEY
INNER JOIN DIM_SUBSCRIBER@DWPRD S ON F.SUBSCRIBER_KEY = S.SUBSCRIBER_KEY
INNER JOIN DIM_DATE@DWPRD DT ON F.SALE_DATE_KEY = DT.DATE_KEY
WHERE SALE_DATE_KEY BETWEEN 20220901 AND 20220930
AND EMBEDDED_DEVICE_RESELLER_IND ='N'
)
SELECT * FROM (
SELECT /*+ FULL(OA) FULL(SALE) FULL(SSCR) FULL(DVFN) PARALLEL(8)*/
SD.SALE_DATE_KEY
, OA.CREATION_DATE AS CREATE_DT --Sale date is Matching for direct; As we don't know when indirect(agent) sells to Customer in TOPS (It is a feed from SAP), So, we have to use FAS_DEVICE_LIFECYCLE for Indirect sales.
, SALE.PROV_DATE --looks like activation date
, OA.CUSTOMER_ID AS CUST_ID
, OA.ORDER_ID, OA.ORDER_UNIT_ID AS ORDER_ACTION_ID
, OA.STATUS AS OA_STATUS
, OA.ACTION_TYPE AS OA_ACT_TYPE
, OA.SALES_CHANNEL AS OA_CHNL --RT (Company-Owned), RL (Agent), WR (Web), CC (Customer Care/Telesales); However, LOC_ID is more reliable because ex. CC may do it in behalf of Company-owned
, SALE.AP_VERSION_ID
, V.VALUE AS COMP_ID, V2.VALUE AS LOC_ID
, SSCR.MAIN_ITEM_ID AS MSTR_SSCR_ID, SSCR.MDN AS SSCR_MDN, SSCR.SUBSCRIBER_ID AS SSCR_ID
, SALE.STATUS AS SALE_STATUS --AC, SU, CE
, SALE.STATE AS SALE_STATE --OR (Ordered), CA (Cancelled), AS (Assigned)
, SALE.SAPITID
, SALE.ESNMEID AS SALE_ESN, SALE.MAKE, SALE.MODL, SALE.SERVICE_TYPE AS SALE_TYPE
, SALE.INVTYPE
, SALE.DEVOP--DF seems to be direct fulfillment
, DVFN.AP_ID AS EIP_CONTRACT_ID, DVFN.DEVAPID AS EIP_AP_ID, DVFN.ESNMEID AS EIP_ESN
, TO_DATE(SUBSTR(DVFN.COMMST, 1, 10), 'DD/MM/YYYY') AS EIP_START, TO_DATE(SUBSTR(DVFN.COMMED, 1, 10), 'DD/MM/YYYY') AS EIP_END
, CASE WHEN DVFN.PROMODEVCRED IS NULL OR DVFN.PROMODEVCRED ='NA' THEN SALE.PROMODEVCRED ELSE DVFN.PROMODEVCRED END PROMO
, DVFN.MSRP, DVFN.INSTAM, DVFN.NOINST, (DVFN.INSTAM * DVFN.NOINST) FINANCE_MSRP
, SALE.DFPURC -- Device Financing
, SALE.LOGSTS_ACA
, SALE.LOGSTS_IAL
, SALE.AGENTSOLD
, ORDER_DATE.COMPLETION_DATE ORDER_DATE
, PMNT_DATE.COMPLETION_DATE PMNT_DATE
, SHIP_DATE.COMPLETION_DATE SHIP_DATE
, ACTV_DATE.COMPLETION_DATE ACTV_DATE
--, SSCR.*
, CASE WHEN SALE.SERVICE_TYPE IN ('USSD','COEQ') THEN (ROW_NUMBER() OVER (PARTITION BY SALE.ESNMEID,SALE.INVTYPE ORDER by SALE.START_DATE)) ELSE 1 END RNK
, CASE WHEN SALE.PROV_DATE IS NOT NULL then (1) ELSE (0) END ACTN_CNT
FROM TBORDER_ACTION OA
INNER JOIN TBAP_ITEM_NOLOB_MV SALE on OA.ORDER_UNIT_ID = SALE.ORDER_ACTION_ID AND SALE.SERVICE_TYPE IN ('USSD','COEQ','ACCS') -- ,'ACCS' is for Accessories
AND SALE.STATUS = 'AC'
AND SALE.STATE != 'CA' --This looks like AS if activated; OR if sold but not activated yet; CA if Cancelled; Therefore if you need sold even if not activated, maybe better <> CA
AND SALE.AP_VERSION_ID=0 --It will Pick the First version Record
INNER JOIN TBAP_ITEM_NOLOB_MV SSCR on OA.ORDER_UNIT_ID = SSCR.ORDER_ACTION_ID AND SSCR.SERVICE_TYPE IN ('TM','CD') AND SSCR.STATUS = 'AC' --Subscriber
LEFT JOIN ORDER_DATE ON SALE.ORDER_ACTION_ID=ORDER_DATE.ORDER_ACTION_ID -- Direct Fullfillement Ordered Date
LEFT JOIN SHIP_DATE ON SALE.ORDER_ACTION_ID=SHIP_DATE.ORDER_ACTION_ID -- Direct Fullfillement Shipped Date
LEFT JOIN PMNT_DATE ON SALE.ORDER_ACTION_ID=PMNT_DATE.ORDER_ACTION_ID -- Direct Fullfillement PAYMENT Date
LEFT JOIN ACTV_DATE ON SALE.ORDER_ACTION_ID=ACTV_DATE.ORDER_ACTION_ID -- Direct FullFillement Activation Date
LEFT JOIN TBAP_ITEM_NOLOB_MV DVFN on OA.ORDER_UNIT_ID = DVFN.ORDER_ACTION_ID AND DVFN.SERVICE_TYPE = 'DVFN' --Device Finance
AND SALE.ESNMEID = DVFN.ESNMEID
AND DVFN.STATUS = 'AC'
LEFT JOIN TBDYNORDER_ATR_VAL V ON OA.ORDER_UNIT_ID = V.ORDER_UNIT_ID AND V.CODE_NAME = 'COMP_ID'
LEFT JOIN TBDYNORDER_ATR_VAL V2 ON OA.ORDER_UNIT_ID = V2.ORDER_UNIT_ID AND V2.CODE_NAME = 'LOCATION_ID'
LEFT JOIN SALE_DATE SD ON SALE.ESNMEID = SD.DEVICE_ID
WHERE
OA.STATUS NOT IN ('NE', 'DC', 'TC', 'IN', 'OH','CA')
AND
(
(SALE.SERVICE_TYPE IN ('USSD','COEQ') AND SALE.INVTYPE != 'BYOD' AND SALE.sapitid NOT LIKE 'BYOD%')
OR
(SALE.SERVICE_TYPE = 'ACCS' AND 1=1)
)
--AND SSCR.MAIN_ITEM_ID = '9809630948' --Master Subscriber ID
--AND SALE.ESNMEID = '089780327006369636' -- This MEID is Used to test Other Inventory Type Sales, Same Device ID was sold with different Inventory Pool Types - they not be considered as Sales
--AND SALE.ESNMEID = '089708781105333624'
--AND OA.CUSTOMER_ID = '204921153'
--AND OA.CTDB_CRE_DATETIME BETWEEN '01-AUG-2022' AND '10-AUG-2022' AND OA.SALES_CHANNEL = 'RT'
AND
CASE WHEN SD.DEVICE_ID is NULL AND OA.CREATION_DATE BETWEEN to_Date('01-SEP-2022','dd-mon-yyyy') AND to_Date('30-SEP-2022','dd-mon-yyyy') THEN ('FILTER')
WHEN SD.DEVICE_ID is NOT NULL AND SD.SALE_DATE_KEY BETWEEN 20220901 AND 20220930 then ('FILTER')
ELSE 'PASSTHROUGH'
END = 'FILTER'
-- AND OA.CREATION_DATE BETWEEN to_Date('01-SEP-2022','dd-mon-yyyy') AND to_Date('30-SEP-2022','dd-mon-yyyy') --do we use CREATION_DATE or CTDB_CRE_DATETIME
AND SALE.SAPITID IN (
'239930','239931','239932','239933','239938','240724','240725'
,'240726','240797','240798','240799','240800','240801','240802'
,'240803','240804','240805','240820','240864','240865')
) T1 WHERE T1.RNK=1
ORDER BY CUST_ID, MSTR_SSCR_ID
;