pl/sql view

By ukmodak | June 6th 2022 12:23:03 PM | viewed 189 times

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;

bONEandALL
Visitor

Total : 18980

Today :9

Today Visit Country :

  • Germany
  • Singapore
  • United States
  • Russia