Buenas tardes estimado equipo de AMDOCS es correcto que para los ORDER_ACTION_KEY
('1158656459','1160768248')
con PRODUCT_KEY ('5671'). En su campo ASSIGNED_PRODUCT_STATUS_KEY venga con el valor
1091. De no ser así favor de regularizar la data.
Query sugerida:
with temporal_uno as (
SELECT
OA.ORDER_ACTION_KEY,
OA.ORDER_ACTION_COMPLETION_DATE,
OA.ORDER_KEY,
OA.REASON_FREE_TEXT,
OA.ORDER_ACTION_REASON_KEY,
OA.ORDER_SALES_CHANNEL_KEY,
OA.ASSIGNED_PRODUCT_ID,
OA.ASSIGNED_PRODUCT_KEY,
OA.AP_VERSION_ID,
OA.CUSTOMER_KEY,
OA.ORDER_ACTION_TYPE_KEY,
OA.ORDER_ACTION_CREATOR,
OA.ORDER_ACTION_START_DATE,
OA.SALES_CODE,
OA.ORDER_ACTION_STATUS_KEY,
AP.SERVICE_TYPE,
OA.ORDER_ACTION_COMMITTED_DATE
FROM ODSDMP.ORDER_ACTION OA
INNER JOIN ODSDMP.ASSIGNED_PRODUCT AP
ON OA.ASSIGNED_PRODUCT_KEY = AP.ASSIGNED_PRODUCT_KEY
where
----OA.ORDER_ACTION_KEY IN ('998929012','998929013')
OA.ORDER_ACTION_STATUS_KEY = '1426'
and OA.ORDER_STATUS_KEY = '1384'
/*and OA.ORDER_ACTION_COMPLETION_DATE >= TO_DATE(''||VFECINI||'','YYYYMMDD')
and OA.ORDER_ACTION_COMPLETION_DATE < TO_DATE(''||VFECFIN||'','YYYYMMDD') + 1*/
and OA.ORDER_ACTION_KEY in ('1158656459','1160768248')
),temporal_dos as (
SELECT
AP.SUBSCRIBER_KEY,
AP.PRODUCT_KEY,
AP.MAIN_COMPONENT_FLAG,
AP.ASSIGNED_PRODUCT_STATUS_KEY,
AP.ORDER_ACTION_KEY,
AP.ASSIGNED_PRODUCT_STATE_KEY,
AP.MAIN_COMPONENT_KEY,
AP.ASSIGNED_PRODUCT_ID,
AP.AP_VERSION_ID,
AP.ASSIGNED_PRODUCT_KEY,
AP.VIRT_MOD_VALUE,
AP.PRODUCT_OFFER_KEY
FROM ODSDMP.ASSIGNED_PRODUCT AP
WHERE AP.ORDER_ACTION_KEY IN (SELECT /*+ parallel(b,20) full(b) */ ORDER_ACTION_KEY
from temporal_uno b)
AND AP.ASSIGNED_PRODUCT_STATE_KEY= '1097'
and AP.ORDER_ACTION_KEY in ('1158656459','1160768248')
), temporal_3 as (
SELECT
SUBSCRIBER_KEY,
ASSIGNED_PRODUCT_ID,
AP_VERSION_ID,
PRODUCT_KEY,
MAIN_COMPONENT_FLAG,
ASSIGNED_PRODUCT_STATUS_KEY,
ORDER_ACTION_KEY,
ASSIGNED_PRODUCT_STATE_KEY,
MAIN_COMPONENT_KEY,
ASSIGNED_PRODUCT_KEY,
VIRT_MOD_VALUE,
PRODUCT_OFFER_KEY
FROM (
SELECT SUBSCRIBER_KEY,ASSIGNED_PRODUCT_ID,AP_VERSION_ID,PRODUCT_KEY,
MAIN_COMPONENT_FLAG ,ASSIGNED_PRODUCT_STATUS_KEY,ORDER_ACTION_KEY,ASSIGNED
_PRODUCT_STATE_KEY,
MAIN_COMPONENT_KEY,PRODUCT_OFFER_KEY,VIRT_MOD_VALUE,ASSIGNED_PRODUCT_KEY,
ROW_NUMBER() OVER( PARTITION BY
ORDER_ACTION_KEY,PRODUCT_KEY,ASSIGNED_PRODUCT_STATUS_KEY ORDER BY
ASSIGNED_PRODUCT_KEY DESC) AS ORDEN
FROM temporal_dos)
WHERE ORDEN=1
and ORDER_ACTION_KEY in ('1158656459','1160768248')
)
SELECT
A.ASSIGNED_PRODUCT_STATUS_KEY,
B.ORDER_KEY,
B.AP_VERSION_ID,
A.PRODUCT_KEY,
B.ORDER_ACTION_KEY
FROM temporal_3 A
INNER JOIN temporal_uno B
ON A.ORDER_ACTION_KEY=B.ORDER_ACTION_KEY
LEFT JOIN ODSDMP.ASSIGNED_PRODUCT_PARAMETER APP
ON APP.ASSIGNED_PRODUCT_KEY=A.ASSIGNED_PRODUCT_KEY
AND APP.ASSIGNED_PRODUCT_PARAMETER_ID='SIMGroup'
AND APP.VIRT_MOD_VALUE=A.VIRT_MOD_VALUE
LEFT JOIN ODSDMP.ASSIGNED_PRODUCT_PARAMETER APP1
ON APP1.ASSIGNED_PRODUCT_KEY=A.ASSIGNED_PRODUCT_KEY
AND APP1.ASSIGNED_PRODUCT_PARAMETER_ID='ICCID'
AND APP1.VIRT_MOD_VALUE=A.VIRT_MOD_VALUE
LEFT JOIN ODSDMP.ASSIGNED_PRODUCT_PARAMETER APP2
ON APP2.ASSIGNED_PRODUCT_KEY=A.ASSIGNED_PRODUCT_KEY
AND APP2.ASSIGNED_PRODUCT_PARAMETER_ID='SIMTypeSKU'
AND APP2.VIRT_MOD_VALUE=A.VIRT_MOD_VALUE
LEFT JOIN ODSDMP.ASSIGNED_PRODUCT_PARAMETER APP3
ON APP3.ASSIGNED_PRODUCT_KEY=A.ASSIGNED_PRODUCT_KEY
AND APP3.ASSIGNED_PRODUCT_PARAMETER_ID='AcquisitionType'
AND APP3.VIRT_MOD_VALUE=A.VIRT_MOD_VALUE
LEFT JOIN ODSDMP.ASSIGNED_PRODUCT_PARAMETER APP4
ON APP4.ASSIGNED_PRODUCT_KEY=A.ASSIGNED_PRODUCT_KEY
AND APP4.ASSIGNED_PRODUCT_PARAMETER_ID='ExternalPrice'
AND APP4.VIRT_MOD_VALUE=A.VIRT_MOD_VALUE
WHERE A.PRODUCT_KEY='5671'
and B.ORDER_ACTION_KEY in ('1158656459','1160768248');