Some simple formatting of your code shows the FROM portion to have 2 references to the same table with the same alias; any calls to elements from this table naturally are going to be confused
FROM WC_POS_ITEM_F WC_POS_ITEM_F
, WC_POS_ITEM_F WC_POS_ITEM_F
, W_ORDER_F W_ORDER_F
,
That said... your code being a mess makes it harder to see these types of things.
It does not take much to make apply some logical formatting.. I would also recommend that if you are going to
alias some names you may want to shorten the names up a bit for readability.
CREATE TABLE WC_COVID1 AS
SELECT '1060' COMPANY_CODE,
'AP' STATE_CODE,
WC_POS_ITEM_F.X_DRC_CODE MARKET_CODE,
'AP' MARKET_NAME,
'AP' PPAC_DIST_CODE,
'AP' DISTRICT_NAME,
W_INT_ORG_D.X_SAP_ID DISTRIBUTOR_CODE,
W_INT_ORG_D.X_NAME,
3||WC_POS_ITEM_F.x_rel_id PG_ID,
W_PARTY_ORG_D.NAME CUSTOMER_NAME,
CASE
WHEN WC_POS_ITEM_F.X_CYL_UNIT_SIZE = '14.2' THEN 'D142'
ELSE 'D005'
END EQUIPMENT_CODE,
ORDER_NUM REFILL_ID,
ORDER_DT Refill_Booking_Date,
X_FRGHT_AMT_DT Refill_Delivery_Date,
WC_POS_ITEM_F.DEBIT_NOTE_AMT RSP_CLMD_AMT,
x_batchid Bank_Batch_ID,
WC_POS_ITEM_F.x_bankdos Bank_Batch_ID_Date,
WC_POS_ITEM_F.DEBIT_NOTE_NUM RSP_TFD_AMT,
W_ORDER_MD.STATUS_CD,
to_char(X_FRGHT_AMT_DT, 'mm_yyyy')||'_'||
case
when to_char(X_FRGHT_AMT_DT, 'DD') between '01' and '07' then '01_07'
when to_char(X_FRGHT_AMT_DT, 'DD') between '08' and '15' then '08_15'
when to_char(X_FRGHT_AMT_DT, 'DD') between '16' and '22' then '16_22'
when to_char(X_FRGHT_AMT_DT, 'DD') > '22' then '23_EOM'
end CLAIM_MONTH,
W_INT_ORG_D.X_PR_ADDR_ID
FROM WC_POS_ITEM_F WC_POS_ITEM_F
, WC_POS_ITEM_F WC_POS_ITEM_F
, W_ORDER_F W_ORDER_F
, W_ORDER_MD
, W_INT_ORG_D W_INT_ORG_D
, W_PARTY_ORG_D W_PARTY_ORG_D
, W_PRODUCT_D W_PRODUCT_D
WHERE WC_POS_ITEM_F.TYPE_CD = 'COVID'
AND WC_POS_ITEM_F.PROD_INT_ID = W_PRODUCT_D.INTEGRATION_ID
AND WC_POS_ITEM_F.X_REL_ID = W_PARTY_ORG_D.X_SAP_ID
AND W_PARTY_ORG_D.X_REL_TYPE = '112'
AND W_ORDER_F.PR_VIS_ORG_WID = W_INT_ORG_D.SCD1_WID
AND W_ORDER_MD.ROW_WID = W_ORDER_F.ORDER_ATTR_WID
AND W_ORDER_F.CUSTOMER_WID = W_PARTY_ORG.ROW_WID
AND WC_POS_ITEM_F.X_IOCLBANKTIDSTATUS = 'SU'
and WC_POS_ITEM_F.X_INV_ID is not null
and WC_POS_ITEM_F.claim_status_cd = 'Settled';