To create a view execute the following command
CREATE OR REPLACE FORCE VIEW DALERP.VW_DELIVERY_ITEM
(
ITEM_PID,
ITEM_CODE,
ITEM_NAME,
ITEM_GROUP_PID,
GROUP_CODE,
GROUP_NAME,
MU_PID,
NET_WEIGHT,
MARKET_UNIT_PID,
SHORT_NAME,
UNIT_PID,
SKU,
CTN_UNIT_PID,
CTN,
CONVERSION_VALUE,
RATE,
SKU_RATE,
ISSUE_QTY,
ISSUE_QTY_CTN,
ISSUE_VALUE,
ISSUE_PID,
ISSUE_NO,
ISSUE_DATE,
ISSUE_FROM,
ISSUE_FROM_NAME,
ISSUE_TO,
ISSUE_TO_NAME,
STORE_TYPE_PID,
TYPE_NAME,
DELIVERED_QTY,
DELIVERY_QTY_CTN,
DELIVERED_VALUE,
DELIVERY_PID,
USER_DELIVERY_NO,
DELIVERY_DATE,
DELIVERY_FROM,
DELIVERY_FROM_NAME,
DELIVERY_TO,
DELIVERY_TO_NAME,
ADDRESS,
LOCATION,
CONTACT_NO,
APPROVE_BY,
APPROVE_DT,
DELIVERY_ENT_DATE,
RECOMMEND_BY,
RECOMMEND_DT,
TRANS_TYPE,
USER_ID,
DELIVERY_ENT_BY,
DELIVERY_ENT_BY_NAME,
CUM_DEL_QTY,
PENDING_QTY,
PENDING_QTY_CTN,
PENDING_VALUE,
STATUS,
STATUS_DESC
)
AS
SELECT iss.item_pid,
iss.item_code,
iss.item_name,
iss.item_group_pid,
ig.group_code,
ig.group_name,
iss.mu_pid,
iss.net_weight,
iss.market_unit_pid,
u.short_name,
iss.unit_pid,
u1.short_name sku,
iss.ctn_unit_pid,
u2.short_name ctn,
iss.conversion_value,
iss.rate,
iss.sku_rate,
iss.issue_qty,
iss.issue_qty_ctn,
iss.issue_value,
iss.issue_pid,
iss.issue_no,
iss.issue_date,
iss.issue_from,
su1.store_unit_name issue_from_name,
iss.issue_to,
su.store_unit_name issue_to_name,
iss.store_type_pid,
st.type_name,
dli.delivery_qty delivered_qty,
dli.delivery_qty_ctn,
dli.delivered_value,
dli.delivery_pid,
dli.user_delivery_no,
dli.delivery_date,
dli.delivery_from,
su3.store_unit_name delivery_from_name,
dli.delivery_to,
su2.store_unit_name delivery_to_name,
su2.address,
su2.location,
su2.contact_no,
dli.approve_by,
dli.approve_dt,
dli.ent_date delivery_ent_date,
dli.recommend_by,
dli.recommend_dt,
dli.trans_type,
dli.user_id,
dli.user_id delivery_ent_by,
dut.user_name delivery_ent_by_name,
dli.cum_del_qty,
iss.issue_qty - NVL (dli.cum_del_qty, 0) pending_qty,
iss.issue_qty_ctn
- DECODE (iss.market_unit_pid,
dli.market_unit_pid, NVL (dli.cum_del_qty, 0),
NVL (dli.cum_del_qty, 0) / iss.conversion_value)
pending_qty_ctn,
(iss.issue_qty - NVL (dli.cum_del_qty, 0)) * dli.rt pending_value,
iss.status,
sas.status_desc
FROM (SELECT si.item_pid,
i.item_group_pid,
i.item_code,
i.item_name,
si.mu_pid,
si.net_weight,
si.market_unit_pid,
si.conversion_value,
i.unit_pid,
i.market_unit_pid ctn_unit_pid,
si.rate,
si.sku_rate,
si.issue_qty,
DECODE (si.market_unit_pid,
i.market_unit_pid, si.issue_qty,
si.issue_qty / si.conversion_value)
issue_qty_ctn,
si.issue_qty
* DECODE (si.market_unit_pid,
i.market_unit_pid, si.rate,
si.sku_rate)
issue_value,
si.issue_pid,
sm.user_issue_no issue_no,
sm.issue_date,
sm.issue_from,
sm.issue_to,
sm.store_type_pid,
sm.status
FROM store_issuechd si, store_issuemst sm, item i
WHERE si.issue_pid = sm.pid AND si.item_pid = i.pid) iss,
(SELECT sdgg.pid,
sdgg.issue_pid,
sdgg.item_pid,
sdgg.rate,
sdgg.sku_rate,
sdgg.delivery_qty,
DECODE (sdgg.market_unit_pid,
i.market_unit_pid, sdgg.delivery_qty,
sdgg.delivery_qty / sdgg.conversion_value)
delivery_qty_ctn,
sdgg.delivery_qty
* DECODE (sdgg.market_unit_pid,
i.market_unit_pid, sdgg.rate,
sdgg.sku_rate)
delivered_value,
sdgg.delivery_pid,
sd.user_delivery_no,
sd.delivery_date,
sd.delivery_from,
sd.delivery_to,
sd.approve_by,
sd.approve_dt,
sd.ent_date,
sd.recommend_by,
sd.recommend_dt,
sd.trans_type,
sd.user_id,
(SUM (
sdgg.delivery_qty)
OVER (
PARTITION BY sdgg.issue_pid, sdgg.item_pid, sdgg.rate
ORDER BY sd.delivery_date, sd.user_delivery_no))
cum_del_qty,
DECODE (sdgg.market_unit_pid,
i.market_unit_pid, sdgg.rate,
sdgg.sku_rate)
rt,
i.market_unit_pid
FROM store_deliverygrandgrandchd sdgg,
store_deliverymst sd,
item i
WHERE sdgg.delivery_pid = sd.pid AND sdgg.item_pid = i.pid) dli,
item_group ig,
unit u,
unit u1,
unit u2,
store_unit su,
store_unit su1,
store_unit su2,
store_unit su3,
store_type st,
user_tbl dut,
store_action_status sas
WHERE iss.issue_pid = dli.issue_pid(+)
AND iss.item_pid = dli.item_pid(+)
AND iss.rate = dli.rate(+)
AND iss.item_group_pid = ig.pid
AND iss.store_type_pid = st.pid
AND dli.user_id = dut.user_id(+)
AND iss.market_unit_pid = u.pid
AND iss.unit_pid = u1.pid
AND iss.ctn_unit_pid = u2.pid
AND iss.issue_to = su.pid
AND iss.issue_from = su1.pid
AND dli.delivery_to = su2.pid(+)
AND dli.delivery_from = su3.pid(+)
AND iss.status = sas.id(+);
CREATE OR REPLACE FORCE VIEW DALPDFPRINT.ALL_PO_PDF_V (PO_NO)
AS
SELECT DISTINCT PO_NO
from(
SELECT distinct a.PO_NO
FROM DALPDFPRINT.FULL_PO a
WHERE SUBSTR(TRIM(A.PO_NO),0,3) ='460'
OR SUBSTR(TRIM(A.PO_NO),0,3) ='461'
UNION ALL
SELECT distinct a.PO_NO
FROM DALPDFPRINT.FULL_PO a,DALPDFPRINT.REST_PO B,DALPDFPRINT.PACK P,DALPDFPRINT.FULL_PO_PACKS PP
WHERE A.ID = PP.FULLPO_ID
AND P.ID = PP.PACKS_ID
AND SUBSTR(TRIM(A.PO_NO),0,3) !='460'
AND SUBSTR(TRIM(A.PO_NO),0,3) !='461'
AND SUBSTR(TRIM(A.PO_NO),-3) = SUBSTR(TRIM(B.RETEK_ORDER_NO),-3)
UNION ALL
SELECT distinct a.PO_NO PO_NO
FROM DALPDFPRINT.PARTIAL_PO A,DALPDFPRINT.REST_PO B,DALPDFPRINT.PACK P,DALPDFPRINT.PARTIAL_PO_PACKS PP
WHERE A.ID = PP.PARTIALPO_ID
AND P.ID = PP.PACKS_ID
AND SUBSTR(TRIM(A.PO_NO),-3) = SUBSTR(TRIM(B.RETEK_ORDER_NO),-3)
)
order by PO_NO asc;
