Ocpdeji Welcomes You To my Blog

My People @ Glo

Check Out My Script

SELECT A.ORGANIZATION_ID, item_id, ITEM,ITEM_CATEGORY, DESCRIPTION, FR_SUBINV,

              subinv_code,TO_CHAR(TO_DATE(:p_start_date),'DD-MON-RRRR') TRANS_DATE,CUSTOMER, TRX,  qty, R_K ,OPERAND UNIT_PRICE

FROM (

(

SELECT * FROM (

SELECT mmt.ORGANIZATION_ID,mmt.inventory_item_id item_id,GLO_ITEM_NAME(mmt.inventory_item_id) ITEM,MIC.CATEGORY_CONCAT_SEGS ITEM_CATEGORY,'BALANCE' DESCRIPTION,'' FR_SUBINV,

CASE WHEN  mmt.subinventory_code IN ('INBOUND', 'PRODUCTION', 'VAULT 2','VAULT 1','OUTBOUND','INTVAULT 2','INTVAULT 1')

THEN 'OREGUN' ELSE  mmt.subinventory_code END  subinv_code

              ,TO_CHAR(TO_DATE(:p_start_date),'DD-MON-RRRR') TRANS_DATE,'' CUSTOMER,'OPENING BALANCE' TRX,

              SUM (primary_quantity) qty,1 R_K

       FROM   mtl_material_transactions mmt,

              mtl_txn_source_types mtst,

              mtl_parameters mp,

              MTL_item_categories_v MIC

      WHERE   mmt.organization_id = '142' AND mp.organization_id = '142'

              AND trunc(transaction_date) <= to_date(:p_start_date) 

              AND NVL (mmt.owning_tp_type, 2) =

                    DECODE (2, 2, 2, NVL (mmt.owning_tp_type, 2))

              AND mmt.transaction_source_type_id =

                    mtst.transaction_source_type_id

              AND NVL (mmt.logical_transaction, 2) <> 1 

              AND mmt.subinventory_code<>'Stage'

                AND mmt.ORGANIZATION_ID=MIC.organization_id

              AND mmt.inventory_item_id = MIC.INVENTORY_ITEM_ID

--             AND mmt.subinventory_code= :LOC

   GROUP BY   

              mmt.inventory_item_id,mmt.organization_id ,mmt.subinventory_code,:p_start_date,MIC.CATEGORY_CONCAT_SEGS

              )

        WHERE subinv_code= :LOC

 ) 

 UNION

(  

Select * from (

 SELECT A.organization_id ,

              A.inventory_item_id item_id,ITEM_DESCRIPTION ITEM,CATEGORY_CONCAT_SEGS ITEM_CATEGORY,REQUEST_NUMBER||'-'||DESCRIPTION DESCRIPTION,              

              CASE WHEN FR_SUBINV IN ('INBOUND', 'PRODUCTION', 'VAULT 2','VAULT 1','OUTBOUND','INTVAULT 2','INTVAULT 1')

THEN 'OREGUN' ELSE FR_SUBINV END FR_SUBINV

              ,CASE WHEN A.TO_SUBINVENTORY_CODE IN ('INBOUND', 'PRODUCTION', 'VAULT 2','VAULT 1','OUTBOUND','INTVAULT 2','INTVAULT 1')

THEN 'OREGUN' ELSE A.TO_SUBINVENTORY_CODE END subinv_code

              ,TO_CHAR(TO_DATE(:p_start_date),'DD-MON-RRRR') TRANS_DATE ,'' CUSTOMER,'INTRANSIT STOCK' TRX,SUM(L_LOT_QTY) QTY,5 R_K

               FROM XXGLO_INTRANSIT_SHIP_V A

               WHERE trunc(transaction_date) = to_date(:p_start_date) 

           -- AND A.TO_SUBINVENTORY_CODE= :LOC

               AND A.TO_SUBINVENTORY_CODE IS NOT NULL

                 GROUP BY   

              A.inventory_item_id,A.organization_id ,TO_SUBINVENTORY_CODE,CATEGORY_CONCAT_SEGS,DESCRIPTION,

              :p_start_date,REQUEST_NUMBER,A.ITEM_DESCRIPTION  

              ,CASE WHEN FR_SUBINV IN ('INBOUND', 'PRODUCTION', 'VAULT 2','VAULT 1','OUTBOUND','INTVAULT 2','INTVAULT 1')

THEN 'OREGUN' ELSE FR_SUBINV END 

              )

            WHERE subinv_code =:LOC   

              )

UNION

              (

select organization_id,item_id, item,ITEM_CATEGORY,DESCRIPTION,FR_SUBINV, subinv_code,TO_CHAR(trans_date,'DD-MON-RRRR') trans_date,

 CUSTOMER, 'STOCK ISSUED SOLD' Trx ,sum(qty) qty,3 R_K

from (

select msi.organization_id,msi.inventory_item_id item_id,msi.segment1 item,MIC.CATEGORY_CONCAT_SEGS ITEM_CATEGORY,'SALES NUM'||':'||ORDER_NUMBER DESCRIPTION,'' FR_SUBINV

,CASE WHEN mmt.subinventory_code  IN ('INBOUND', 'PRODUCTION', 'VAULT 2','VAULT 1','OUTBOUND','INTVAULT 2','INTVAULT 1')

THEN 'OREGUN' ELSE mmt.subinventory_code  END subinv_code,

TRUNC(mmt.transaction_date) trans_date,'STOCK ISSUED SOLD' Trx,  ROUND (SUM  (mmt.transaction_quantity * -1),2) qty,3 R_K

,HP.ATTRIBUTE2||' - '||PARTY_NAME CUSTOMER

from mtl_system_items_b msi, 

mtl_material_transactions mmt, 

mtl_transaction_types mtt,

MTL_item_categories_v MIC,

oe_order_headers_all ooha

,oe_order_lines_all oola

,hz_cust_accounts_all hca,

HZ_PARTIES hp

WHERE msi.inventory_item_id = mmt.inventory_item_id 

AND msi.organization_id = mmt.organization_id 

AND mmt.transaction_type_id = mtt.transaction_type_id 

AND msi.inventory_item_status_code = 'Active' 

--AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date AND :p_start_date 

AND TRUNC (mmt.transaction_date)= TRUNC(to_date(:p_start_date))  --BETWEEN :p_start_date AND :p_start_date 

AND mmt.transaction_type_id  IN (52,63) 

AND mmt.subinventory_code<>'Stage'

--AND mmt.subinventory_code= :LOC

AND msi.organization_id = '142'

  AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID

 AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID

 AND  ooha.HEADER_ID=oola.HEADER_ID

and oola.LINE_ID=mmt.TRX_SOURCE_LINE_ID 

and ooha.sold_to_org_id = hca.cust_account_id

and HP.PARTY_ID=hca.PARTY_ID

-- AND TRANSACTION_ID=11349051

group by msi.organization_id,msi.inventory_item_id,mmt.subinventory_code,TRUNC(mmt.transaction_date),MIC.CATEGORY_CONCAT_SEGS,msi.DESCRIPTION,ORDER_NUMBER

,HP.PARTY_NAME,HP.ATTRIBUTE2,msi.segment1

union

select msi.organization_id,msi.inventory_item_id item_id,msi.segment1 item,MIC.CATEGORY_CONCAT_SEGS ITEM_CATEGORY,'SALES NUM'||':'||ORDER_NUMBER DESCRIPTION,'' FR_SUBINV

,CASE WHEN mmt.subinventory_code  IN ('INBOUND', 'PRODUCTION', 'VAULT 2','VAULT 1','OUTBOUND','INTVAULT 2','INTVAULT 1')

THEN 'OREGUN' ELSE mmt.subinventory_code  END subinv_code

,TRUNC(mmt.transaction_date) P_START_DATE,'STOCK ISSUED SOLD' Trx,  SUM  (0) qty,3 R_K

,HP.ATTRIBUTE2||' - '||PARTY_NAME CUSTOMER

from mtl_system_items_b msi, 

mtl_material_transactions mmt, 

mtl_transaction_types mtt,

MTL_item_categories_v MIC,

oe_order_headers_all ooha

,oe_order_lines_all oola

,hz_cust_accounts_all hca,

HZ_PARTIES hp

WHERE msi.inventory_item_id = mmt.inventory_item_id 

AND msi.organization_id = mmt.organization_id 

AND mmt.transaction_type_id = mtt.transaction_type_id 

AND msi.inventory_item_status_code = 'Active' 

--AND TRUNC (mmt.transaction_date) BETWEEN :p_start_date AND :p_start_date 

AND TRUNC (mmt.transaction_date)= TRUNC(to_date(:p_start_date))  --BETWEEN :p_start_date AND :p_start_date 

AND mmt.transaction_type_id  IN (52,63) 

AND mmt.subinventory_code<>'Stage'

--AND mmt.subinventory_code= :LOC

AND msi.organization_id = '142'

  AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID

 AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID

 AND  ooha.HEADER_ID=oola.HEADER_ID

and oola.LINE_ID=mmt.TRX_SOURCE_LINE_ID 

and ooha.sold_to_org_id = hca.cust_account_id

and HP.PARTY_ID=hca.PARTY_ID

-- AND TRANSACTION_ID=11349051

group by msi.organization_id,msi.inventory_item_id,mmt.subinventory_code,TRUNC(mmt.transaction_date),MIC.CATEGORY_CONCAT_SEGS,msi.DESCRIPTION,ORDER_NUMBER

,HP.PARTY_NAME,HP.ATTRIBUTE2,msi.segment1

)

WHERE subinv_code= :LOC

group by organization_id,item_id, item,ITEM_CATEGORY,DESCRIPTION,FR_SUBINV, subinv_code,trans_date, CUSTOMER, 'STOCK ISSUED SOLD'  

              )      

UNION

     ( -- deji cont

    SELECT A.organization_id,A.inventory_item_id item_id,A.ITEM_DESCRIPTION item,A.CATEGORY_CONCAT_SEGS ITEM_CATEGORY,

A.REQUEST_NUMBER||'-'||A.LONG_DESCRIPTION DESCRIPTION

,A.FR_SUBINV,UPPER(NVL(nvl(A.TO_SUBINV,a.LOC_CODE),LOCATION_CODE))  subinv_code,A.transaction_date trans_date

,'' CUSTOMER   ,'STOCK RECEIVED'TRX

, ROUND (SUM  (A.TRANSACTION_QTY ),2) qty ,2 R_K

FROM GLO_MOVE_ORDER_V A

WHERE TRANSACTION_DATE=  TRUNC(to_date(:p_start_date)) 

AND STATUS='Transacted'

--AND ITEM_DESCRIPTION='7.2 MODEM + SIM'

AND UPPER(NVL(nvl(A.TO_SUBINV,a.LOC_CODE),LOCATION_CODE))=:LOC

--and request_number='215845'

GROUP BY A.organization_id,A.inventory_item_id,A.ITEM_DESCRIPTION,A.CATEGORY_CONCAT_SEGS ,A.TO_SUBINV,A.FR_SUBINV  ,A.transaction_date,LONG_DESCRIPTION

,A.LOCATION_CODE,a.LOC_CODE

,A.REQUEST_NUMBER

    ) 

UNION

    (

SELECT A.organization_id,A.inventory_item_id item_id,A.ITEM_DESCRIPTION item,A.CATEGORY_CONCAT_SEGS ITEM_CATEGORY,

A.REQUEST_NUMBER||'-'||A.LONG_DESCRIPTION DESCRIPTION

,UPPER(NVL(nvl(A.TO_SUBINV,a.LOC_CODE),LOCATION_CODE)) FR_SUBINV,A.FR_SUBINV subinv_code 

,A.transaction_date trans_date

,'' CUSTOMER   ,'STOCK TRANSFERRED' TRX

, ROUND (SUM  (A.TRANSACTION_QTY ),2) qty ,4 R_K

FROM GLO_MOVE_ORDER_V A

WHERE TRANSACTION_DATE=  TRUNC(to_date(:p_start_date)) 

AND STATUS='Transacted'

--AND ITEM_DESCRIPTION='7.2 MODEM + SIM'

--AND UPPER(NVL(nvl(A.TO_SUBINV,a.LOC_CODE),LOCATION_CODE))=:LOC

AND A.FR_SUBINV =:LOC

--and request_number='215845'

GROUP BY A.organization_id,A.inventory_item_id,A.ITEM_DESCRIPTION,A.CATEGORY_CONCAT_SEGS ,A.TO_SUBINV,A.FR_SUBINV  ,A.transaction_date,LONG_DESCRIPTION

,A.LOCATION_CODE,a.LOC_CODE

,A.REQUEST_NUMBER

    )

    ) A

    ,( select msi.organization_id,msi.INVENTORY_ITEM_ID,qpll.operand,qlhb.currency_code

from qp_list_headers_b qlhb,qp_list_headers_tl qlht,qp_list_lines qpll, qp_pricing_attributes qppr,mtl_system_items_b msi

WHERE qlhb.LIST_HEADER_ID = qlht.LIST_HEADER_ID

and qlhb.ACTIVE_FLAG = 'Y'

and qlhb.END_DATE_ACTIVE is null

and qlhb.LIST_TYPE_CODE = 'PRL'

and qpll.LIST_HEADER_ID = qlhb.LIST_HEADER_ID

and qppr.LIST_LINE_ID = qpll.LIST_LINE_ID

and qppr.LIST_HEADER_ID = qlhb.LIST_HEADER_ID

and TO_CHAR(msi.INVENTORY_ITEM_ID) = qppr.PRODUCT_ATTR_VALUE

and msi.organization_id = '142') prl

    WHERE    A.organization_id  = prl.organization_id 

and A.item_id = prl.inventory_item_id 

    ORDER BY  R_K