Cotton Pending Status Report

CREATE TABLE COTTON PENDING BOOKING

DROP TABLE APPS.CUST_COT_PEND_BOOKING CASCADE CONSTRAINTS;

CREATE TABLE APPS.CUST_COT_PEND_BOOKING

(

  PO_HEADER_ID   NUMBER,

  VENDOR_ID      NUMBER,

  PO_NUM         VARCHAR2(20 BYTE),

  CONT_NUMB      VARCHAR2(20 BYTE),

  CONTR_DATE     DATE,

  BROKER         VARCHAR2(20 BYTE),

  PARTY_NAME     VARCHAR2(240 BYTE),

  STATION        VARCHAR2(60 BYTE),

  NUB_OF_BALES   VARCHAR2(20 BYTE),

  RATE           NUMBER,

  MOISTURE       VARCHAR2(20 BYTE),

  TRASH          VARCHAR2(20 BYTE),

  ARRIVAL_DATE   DATE,

  BALES_ARRIVED  VARCHAR2(20 BYTE),

  REP_PARA       DATE,

  AS_ON_RECEIPT  NUMBER,

  BALANCE_BALES  NUMBER

)

TABLESPACE APPS_TS_TX_DATA

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          128K

            NEXT             128K

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

CREATE PROCEDURE   COTTON PENDING

CREATE OR REPLACE PROCEDURE APPS.CUST_COT_PEND_PROC

(

    ERRBUF                      OUT     VARCHAR2,

    RETCODE                     OUT     VARCHAR2

--    --P_AS_ON_DATE                        VARCHAR2

)

AS

--P_DATE_AS_ON                 DATE;

BEGIN

--P_DATE_AS_ON := TO_DATE(SUBSTR(P_AS_ON_DATE,1,10),'YYYY/MM/DD');

            DELETE FROM     CUST_COT_PEND_BOOKING;

            COMMIT;

            INSERT INTO     CUST_COT_PEND_BOOKING                 

select          distinct

                poh.PO_HEADER_ID    ,   poh.VENDOR_ID

            ,   poh.SEGMENT1  po_num  ,   pol.ATTRIBUTE5 cont_numb 

            ,   poh.CREATION_DATE   contr_date     ,   pol.ATTRIBUTE6  broker

            ,   aps.VENDOR_NAME     party_name

            ,   pvs.CITY station

            ,   pol.ATTRIBUTE1  nub_of_bales

            ,   pol.UNIT_PRICE  rate

            ,   pol.ATTRIBUTE2  moisture

            ,   pol.ATTRIBUTE3  trash

            ,   rt.TRANSACTION_DATE arrival_date

            ,   to_number(rt.ATTRIBUTE7) bales_arrived

            ,   rt.TRANSACTION_DATE rep_para

            ,   to_number(rt.ATTRIBUTE7)   as_on_receipt

            ,   (pol.ATTRIBUTE1-rt.ATTRIBUTE7)  balance_bales 

from            PO_HEADERS_ALL POH

            ,   PO_LINES_ALL POL

            ,   rcv_transactions rt

            ,   rcv_transactions rt1

            ,   rcv_transactions rt2

            ,   rcv_shipment_headers sh

            ,   rcv_shipment_lines sl

            ,   mtl_system_items_b msi

            ,   AP_SUPPLIERS APS

            ,   AP_SUPPLIER_SITES_ALL PVS

where           poh.PO_HEADER_ID        =   pol.PO_HEADER_ID

and             poh.VENDOR_ID           =   aps.VENDOR_ID

and             aps.VENDOR_ID           =   pvs.VENDOR_ID

and             poh.PO_HEADER_ID        =   rt.PO_HEADER_ID

and             rt.TRANSACTION_ID       =   rt1.PARENT_TRANSACTION_ID

and             rt1.TRANSACTION_ID      =   rt2.PARENT_TRANSACTION_ID

and             rt.shipment_header_id   =   sh.shipment_header_id

AND             rt.po_header_id         =   sl.po_header_id

AND             rt.po_line_id           =   sl.po_line_id

AND             sl.item_id              =   msi.inventory_item_id

and             msi.SEGMENT1            =   '05'

and             rt.TRANSACTION_TYPE     =   'RECEIVE'

and             rt2.TRANSACTION_TYPE <> 'RETURN TO VENDOR'

union

select          distinct

                poh.PO_HEADER_ID    ,   poh.VENDOR_ID

            ,   poh.SEGMENT1  po_num  ,   pol.ATTRIBUTE5 cont_numb 

            ,   poh.CREATION_DATE   contr_date     ,   pol.ATTRIBUTE6  broker

            ,   aps.VENDOR_NAME     party_name

            ,   pvs.CITY station

            ,   pol.ATTRIBUTE1  nub_of_bales

            ,   pol.UNIT_PRICE  rate

            ,   pol.ATTRIBUTE2  moisture

            ,   pol.ATTRIBUTE3  trash

            ,   null arrival_date

            ,   0 bales_arrived

            ,   null rep_para

            ,   0    as_on_receipt

            ,     to_number(pol.ATTRIBUTE1)  balance_bales

from            PO_HEADERS_ALL POH

            ,   PO_LINES_ALL POL

            ,   mtl_system_items_b msi

            ,   AP_SUPPLIERS APS

            ,   AP_SUPPLIER_SITES_ALL PVS

            ,   rcv_transactions rt

where           poh.PO_HEADER_ID        =   pol.PO_HEADER_ID

and             poh.VENDOR_ID           =   aps.VENDOR_ID

and             aps.VENDOR_ID           =   pvs.VENDOR_ID

and             msi.INVENTORY_ITEM_ID   =   pol.ITEM_ID

and             pol.PO_HEADER_ID        =   rt.PO_HEADER_ID(+)

and             rt.TRANSACTION_DATE is null

and             msi.SEGMENT1            =   '05'

and             NVL (poh.closed_code, 'OPEN') <> 'CLOSED'

;

COMMIT;

           

EXCEPTION

   WHEN OTHERS

   THEN

      NULL;

END;

/