[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROLV.SHIPMENT_HEADER_ID,
ROLV.SHIPMENT_LINE_ID,
ROLV.ITEM_ID,
ROLV.ITEM,
ROLV.ORGANIZATION_CODE,
ROLV.ITEM_DESCRIPTION,
ROLV.OPSM_INTEGRATED_FLAG,
ROLV.SERIAL_TYPE,
ROLV.QUANTITY_SHIPPED,
ROLV.QUANTITY_RECEIVED,
ROLV.UNIT_OF_MEASURE,
ROLV.SHIPMENT_LINE_STATUS_CODE,
ROLV.BAR_CODE_LABEL,
ROLV.QC_GRADE,
ROLV.ASN_LINE_FLAG,
ROLV.DESTINATION_TYPE_CODE,
ROLV.LOT_CONTROL_CODE,
ROLV.SERIAL_NUMBER_CONTROL_CODE,
ROLV.OE_ORDER_HEADER_ID,
ROLV.ORDER_NUMBER,
ROLV.ORDER_TYPE,
ROLV.LINE_NUMBER,
ROLV.PARENT_TRANSACTION_ID,
ROLV.TRANSACTION_ID,
ROLV.TRANSACTION_TYPE,
ROLV.TRANSACTION_DATE,
ROLV.TRANSACTION_QTY,
ROLV.TRANSACTION_UOM,
ROLV.PRIMARY_QUANTITY,
ROLV.PRIMARY_UOM_CODE,
ROLV.SECONDARY_QUANTITY,
ROLV.SECONDARY_UOM_CODE,
ROLV.SOURCE_DOCUMENT_CODE,
ROLV.TRANS_DESTINATION_TYPE_CODE,
ROLV.SOLDBYORGID,
ROLV.SOLDBYCONTACTID,
ROLV.SHIPFROMORGID,
ROLV.SHIPFROMCONTACTID,
ROLV.BILLEDBYORGID,
ROLV.BILLEDBYCONTACTID,
ROLV.GROUP_ID
FROM RCV_OPSM_LINE_V ROLV,
MTL_CROSS_REFERENCES_VL MCRV,
MTL_PARAMETERS MP
WHERE ROLV.SHIPMENT_HEADER_ID =p_shipmentheaderid
AND ROLV.GROUP_ID =p_groupid
AND ROLV.SOURCE_DOCUMENT_CODE ='RMA RECEIPT'
AND ROLV.LOT_CONTROL_CODE = 2
AND MP.OPSM_ENABLED_FLAG = 'Y'
AND MP.ORGANIZATION_ID = ROLV.ORGANIZATION_ID
AND MP.MASTER_ORGANIZATION_ID = MCRV.ORGANIZATION_ID
AND ROLV.ITEM_ID = MCRV.INVENTORY_ITEM_ID
AND MCRV.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND MCRV.CROSS_REFERENCE ='YES'
AND NOT EXISTS ( SELECT 'Y' FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = ROLV.ITEM_ID
AND mcr.organization_id = ROLV.ORGANIZATION_ID
AND mcr.cross_reference_type ='OPSM INTEGRATED'
AND upper(mcr.cross_reference)= 'NO');
SELECT distinct ROLV.SHIPMENT_HEADER_ID,
ROMAV.ORGANIZATION_ID,
MP.ORGANIZATION_CODE,
ROMAV.RECEIPT_NUM,
ROCAV.CHANGEOFOWNERSHIP,
ROMAV.BILL_OF_LADING,
ROMAV.CREATED_BY,
ROMAV.CREATION_DATE,
ROMAV.LAST_UPDATED_BY,
ROMAV.LAST_UPDATE_DATE,
ROCAV.SOLDBYORGID,
ROCAV.SOLDBYCONTACTID,
ROCAV.SOLDBYORGCODE,
ROCAV.SOLDBYORGNAME,
ROCAV.SOLDBYLINE1 ,
ROCAV.SOLDBYLINE2 ,
ROCAV.SOLDBYLINE3 ,
ROCAV.SOLDBYLINE4 ,
ROCAV.SOLDBYCITY ,
ROCAV.SOLDBYSTATE ,
ROCAV.SOLDBYCOUNTRY ,
ROCAV.SOLDBYPOSTALCODE ,
ROCAV.SOLDBYCONTACTNAME ,
ROCAV.SOLDBYPHONECOUNTRYCODE ,
ROCAV.SOLDBYPHONEAREACODE ,
ROCAV.SOLDBYPHONENUMBER ,
ROCAV.SOLDBYPHONEEXTENSION ,
ROCAV.SOLDBYCONTACTEMAIL ,
ROCAV.BILLEDBYORGID ,
ROCAV.BILLEDBYCONTACTID ,
ROCAV.BILLEDBYORGCODE ,
ROCAV.BILLEDBYORGNAME ,
ROCAV.BILLEDBYLINE1 ,
ROCAV.BILLEDBYLINE2 ,
ROCAV.BILLEDBYLINE3 ,
ROCAV.BILLEDBYLINE4 ,
ROCAV.BILLEDBYCITY ,
ROCAV.BILLEDBYSTATE ,
ROCAV.BILLEDBYCOUNTRY ,
ROCAV.BILLEDBYPOSTALCODE ,
ROCAV.BILLEDBYCONTACTNAME ,
ROCAV.BILLEDBYPHONECOUNTRYCODE ,
ROCAV.BILLEDBYPHONEAREACODE ,
ROCAV.BILLEDBYPHONENUMBER ,
ROCAV.BILLEDBYPHONEEXTENSION ,
ROCAV.BILLEDBYCONTACTEMAIL ,
ROCAV.SHIPFROMORGID ,
ROCAV.SHIPFROMCONTACTID ,
ROCAV.SHIPFROMORGCODE ,
ROCAV.SHIPFROMORGNAME ,
ROCAV.SHIPFROMLINE1 ,
ROCAV.SHIPFROMLINE2 ,
ROCAV.SHIPFROMLINE3 ,
ROCAV.SHIPFROMLINE4 ,
ROCAV.SHIPFROMCITY ,
ROCAV.SHIPFROMSTATE ,
ROCAV.SHIPFROMCOUNTRY ,
ROCAV.SHIPFROMPOSTALCODE ,
ROCAV.SHIPFROMCONTACTNAME ,
ROCAV.SHIPFROMPHONECOUNTRYCODE ,
ROCAV.SHIPFROMPHONEAREACODE ,
ROCAV.SHIPFROMPHONENUMBER ,
ROCAV.SHIPFROMPHONEEXTENSION ,
ROCAV.SHIPFROMCONTACTEMAIL ,
ROMAV.SHIPTOORGID ,
ROMAV.SHIPTOORGCODE ,
ROMAV.SHIPTOORGNAME ,
ROMAV.SHIPTOLINE1 ,
ROMAV.SHIPTOLINE2 ,
ROMAV.SHIPTOLINE3 ,
ROMAV.SHIPTOCITY ,
ROMAV.SHIPTOSTATE ,
ROMAV.SHIPTOCOUNTRY ,
ROMAV.SHIPTOPOSTALCODE ,
ROMAV.SHIPTOCONTACTNAME ,
ROMAV.SHIPTOCONTACTPHONE ,
ROMAV.SHIPTOCONTACTEMAIL ,
ROMAV.SOLDTOORGID ,
ROMAV.SOLDTOORGCODE ,
ROMAV.SOLDTOORGNAME ,
ROMAV.SOLDTOLINE1 ,
ROMAV.SOLDTOLINE2 ,
ROMAV.SOLDTOLINE3 ,
ROMAV.SOLDTOCITY ,
ROMAV.SOLDTOSTATE ,
ROMAV.SOLDTOCOUNTRY ,
ROMAV.SOLDTOPOSTALCODE ,
ROMAV.SOLDTOCONTACTNAME ,
ROMAV.SOLDTOCONTACTPHONE ,
ROMAV.SOLDTOCONTACTEMAIL
FROM RCV_OPSM_LINE_V ROLV,
RCV_OPSM_MANUFACT_ADDRESS_V ROMAV,
RCV_OPSM_CUSTOMERADDRESS_V ROCAV,
MTL_PARAMETERS MP
WHERE ROLV.SHIPMENT_HEADER_ID=p_shipmentheaderid
AND ROLV.GROUP_ID=p_groupid
AND ROLV.SHIPMENT_HEADER_ID=ROMAV.SHIPMENT_HEADER_ID
AND ROLV.SHIPMENT_LINE_ID=ROMAV.SHIPMENT_LINE_ID
AND ROMAV.SHIPMENT_HEADER_ID=ROCAV.SHIPMENT_HEADER_ID
AND ROMAV.SHIPMENT_LINE_ID=ROCAV.SHIPMENT_LINE_ID
AND ROMAV.SHIP_TO_ORG_ID=MP.ORGANIZATION_ID
AND NVL(ROLV.SOLDBYORGID,-999)=NVL(p_soldbyorgid,-999)
AND NVL(ROLV.SOLDBYCONTACTID,-999)=NVL(p_soldbycontactid,-999)
AND NVL(ROLV.SHIPFROMCONTACTID,-999)=NVL(p_shipfromcontactid,-999)
AND NVL(ROLV.BILLEDBYORGID,-999)=NVL(p_billedbyorgid,-999)
AND NVL(ROLV.BILLEDBYCONTACTID,-999)=NVL(p_billedbycontactid,-999);
SELECT DISTINCT ROLSV.SHIPMENT_LINE_ID,
ROLSV.SHIPMENT_HEADER_ID,
ROLSV.TRANSACTION_ID,
ROLSV.PARENT_LOT_NUMBER,
ROLSV.LOT_NUM,
ROLSV.ORGANIZATION_CODE,
ROLSV.LOTQTY,
ROLSV.LOTQTYUOM,
ROLSV.PRIMARY_QUANTITY,
ROLSV.PRIMARY_UOM_CODE,
ROLSV.SECONDARY_QUANTITY,
ROLSV.SECONDARY_UOM_CODE,
ROLSV.QC_GRADE,
ROLSV.REASON_CODE,
ROLSV.ORIGINATION_DATE,
ROLSV.BEST_BY_DATE,
ROLSV.RETEST_DATE,
ROLSV.EXPIRATION_DATE
FROM RCV_OPSM_LOT_SERIAL_V ROLSV
WHERE ROLSV.SHIPMENT_HEADER_ID=p_header_id
AND ROLSV.SHIPMENT_LINE_ID=p_line_id;
SELECT ROLSV.SHIPMENT_LINE_ID,
ROLSV.TRANSACTION_ID,
ROLSV.LOT_NUM,
ROLSV.SERIAL_NUM
FROM RCV_OPSM_LOT_SERIAL_V ROLSV
WHERE ROLSV.SHIPMENT_LINE_ID=p_shipment_line_id
AND ROLSV.TRANSACTION_ID= p_transaction_id
AND NVL(ROLSV.LOT_NUM,-999)=NVL(p_lotnum,-999);
del_count.DELETE(TO_CHAR(v_cur_lines_info.SHIPFROMCONTACTID)
||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYORGID)
||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYCONTACTID)
||'.'||TO_CHAR(v_cur_lines_info.SOLDBYORGID)
||'.'||TO_CHAR(v_cur_lines_info.SOLDBYCONTACTID));
v_cur_headers_info.LAST_UPDATED_BY ,
v_cur_headers_info.LAST_UPDATE_DATE ,
v_cur_headers_info.SOLDBYORGID ,
v_cur_headers_info.SOLDBYCONTACTID ,
v_cur_headers_info.SOLDBYORGCODE ,
v_cur_headers_info.SOLDBYORGNAME ,
v_cur_headers_info.SOLDBYLINE1 ,
v_cur_headers_info.SOLDBYLINE2 ,
v_cur_headers_info.SOLDBYLINE3 ,
v_cur_headers_info.SOLDBYLINE4 ,
v_cur_headers_info.SOLDBYCITY ,
v_cur_headers_info.SOLDBYSTATE ,
v_cur_headers_info.SOLDBYCOUNTRY ,
v_cur_headers_info.SOLDBYPOSTALCODE ,
v_cur_headers_info.SOLDBYCONTACTNAME ,
v_cur_headers_info.SOLDBYPHONECOUNTRYCODE ,
v_cur_headers_info.SOLDBYPHONEAREACODE ,
v_cur_headers_info.SOLDBYPHONENUMBER ,
v_cur_headers_info.SOLDBYPHONEEXTENSION ,
v_cur_headers_info.SOLDBYCONTACTEMAIL ,
v_cur_headers_info.BILLEDBYORGID ,
v_cur_headers_info.BILLEDBYCONTACTID ,
v_cur_headers_info.BILLEDBYORGCODE ,
v_cur_headers_info.BILLEDBYORGNAME ,
v_cur_headers_info.BILLEDBYLINE1 ,
v_cur_headers_info.BILLEDBYLINE2 ,
v_cur_headers_info.BILLEDBYLINE3 ,
v_cur_headers_info.BILLEDBYLINE4 ,
v_cur_headers_info.BILLEDBYCITY ,
v_cur_headers_info.BILLEDBYSTATE ,
v_cur_headers_info.BILLEDBYCOUNTRY ,
v_cur_headers_info.BILLEDBYPOSTALCODE ,
v_cur_headers_info.BILLEDBYCONTACTNAME ,
v_cur_headers_info.BILLEDBYPHONECOUNTRYCODE ,
v_cur_headers_info.BILLEDBYPHONEAREACODE ,
v_cur_headers_info.BILLEDBYPHONENUMBER ,
v_cur_headers_info.BILLEDBYPHONEEXTENSION ,
v_cur_headers_info.BILLEDBYCONTACTEMAIL ,
v_cur_headers_info.SHIPFROMORGID ,
v_cur_headers_info.SHIPFROMCONTACTID ,
v_cur_headers_info.SHIPFROMORGCODE ,
v_cur_headers_info.SHIPFROMORGNAME ,
v_cur_headers_info.SHIPFROMLINE1 ,
v_cur_headers_info.SHIPFROMLINE2 ,
v_cur_headers_info.SHIPFROMLINE3 ,
v_cur_headers_info.SHIPFROMLINE4 ,
v_cur_headers_info.SHIPFROMCITY ,
v_cur_headers_info.SHIPFROMSTATE ,
v_cur_headers_info.SHIPFROMCOUNTRY ,
v_cur_headers_info.SHIPFROMPOSTALCODE ,
v_cur_headers_info.SHIPFROMCONTACTNAME ,
v_cur_headers_info.SHIPFROMPHONECOUNTRYCODE ,
v_cur_headers_info.SHIPFROMPHONEAREACODE ,
v_cur_headers_info.SHIPFROMPHONENUMBER ,
v_cur_headers_info.SHIPFROMPHONEEXTENSION ,
v_cur_headers_info.SHIPFROMCONTACTEMAIL ,
v_cur_headers_info.SHIPTOORGID ,
v_cur_headers_info.SHIPTOORGCODE ,
v_cur_headers_info.SHIPTOORGNAME ,
v_cur_headers_info.SHIPTOLINE1 ,
v_cur_headers_info.SHIPTOLINE2 ,
v_cur_headers_info.SHIPTOLINE3 ,
v_cur_headers_info.SHIPTOCITY ,
v_cur_headers_info.SHIPTOSTATE ,
v_cur_headers_info.SHIPTOCOUNTRY ,
v_cur_headers_info.SHIPTOPOSTALCODE ,
v_cur_headers_info.SHIPTOCONTACTNAME ,
v_cur_headers_info.SHIPTOCONTACTPHONE ,
v_cur_headers_info.SHIPTOCONTACTEMAIL ,
v_cur_headers_info.SOLDTOORGID ,
v_cur_headers_info.SOLDTOORGCODE ,
v_cur_headers_info.SOLDTOORGNAME ,
v_cur_headers_info.SOLDTOLINE1 ,
v_cur_headers_info.SOLDTOLINE2 ,
v_cur_headers_info.SOLDTOLINE3 ,
v_cur_headers_info.SOLDTOCITY ,
v_cur_headers_info.SOLDTOSTATE ,
v_cur_headers_info.SOLDTOCOUNTRY ,
v_cur_headers_info.SOLDTOPOSTALCODE ,
v_cur_headers_info.SOLDTOCONTACTNAME ,
v_cur_headers_info.SOLDTOCONTACTPHONE ,
v_cur_headers_info.SOLDTOCONTACTEMAIL);
SELECT ROLV.item_id,ROLV.organization_id
FROM RCV_OPSM_LINE_V ROLV
WHERE rolv.shipment_header_id=p_shipmentheaderid
AND rolv.GROUP_ID=p_groupid;
SELECT 1
INTO l_count
FROM MTL_PARAMETERS MP,
MTL_CROSS_REFERENCES_VL MCRV,
MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.ORGANIZATION_ID = v_opsm_items.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = v_opsm_items.ITEM_ID
AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.OPSM_ENABLED_FLAG = 'Y'
AND MSIB.LOT_CONTROL_CODE = 2 --(hard coded 2 refers lot control)
AND MP.MASTER_ORGANIZATION_ID =MCRV.ORGANIZATION_ID
AND MCRV.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MCRV.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND MCRV.CROSS_REFERENCE = 'YES'
AND NOT EXISTS ( SELECT 'Y' FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = v_opsm_items.ITEM_ID
AND mcr.organization_id = v_opsm_items.ORGANIZATION_ID
AND mcr.cross_reference_type ='OPSM INTEGRATED'
AND upper(mcr.cross_reference)= 'NO');