APPS.GMI_ICR04_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 13

    SELECT
      SUM(T.TRANS_QTY)
    INTO COMPLETED_TRANS_QTY
    FROM
      IC_TRAN_CMP T,
      PM_MATL_DTL M
    WHERE DOC_TYPE_1 in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
      AND REASON_CODE_1 in (
      SELECT
        REASON_CODE
      FROM
        SY_REAS_CDS
      WHERE FLOW_TYPE = 0 )
      AND T.TRANS_DATE >= FROM_DATE
      AND T.TRANS_DATE <= TO_DATE
      AND T.DOC_TYPE = 'PROD'
      AND M.LINE_TYPE = - 1
      AND T.DOC_ID = M.BATCH_ID
      AND T.LINE_ID = M.LINE_ID
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null;

Line: 39

    SELECT
      SUM(T.TRANS_QTY)
    INTO REAS_TRANS_QTY
    FROM
      IC_TRAN_CMP T,
      SY_REAS_CDS R
    WHERE T.TRANS_DATE >= FROM_DATE
      AND T.TRANS_DATE <= TO_DATE
      AND R.FLOW_TYPE = 0
      AND T.REASON_CODE = R.REASON_CODE
      AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null
      AND R.DELETE_MARK = 0;

Line: 58

    SELECT
      SUM(T.TRANS_QTY)
    INTO PENDING_TRANS_QTY
    FROM
      IC_TRAN_PND T,
      PM_MATL_DTL M
    WHERE T.TRANS_DATE >= FROM_DATE
      AND T.TRANS_DATE <= TO_DATE
      AND T.DOC_TYPE = 'PROD'
      AND M.LINE_TYPE = - 1
      AND T.DOC_ID = M.BATCH_ID
      AND T.LINE_ID = M.LINE_ID
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null
      AND T.DELETE_MARK = 0
      AND T.COMPLETED_IND = 1;

Line: 79

    SELECT
      SUM(T.TRANS_QTY)
    INTO REASP_TRANS_QTY
    FROM
      IC_TRAN_PND T,
      SY_REAS_CDS R
    WHERE T.TRANS_DATE >= FROM_DATE
      AND T.TRANS_DATE <= TO_DATE
      AND R.FLOW_TYPE = 0
      AND T.REASON_CODE = R.REASON_CODE
      AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null
      AND R.DELETE_MARK = 0
      AND T.DELETE_MARK = 0
      AND T.COMPLETED_IND = 1;

Line: 114

    SELECT
      SUM(T.TRANS_QTY)
    INTO COMPLETED_TRANS_QTY
    FROM
      IC_TRAN_CMP T,
      PM_MATL_DTL M
    WHERE DOC_TYPE_1 in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
      AND REASON_CODE_1 in (
      SELECT
        REASON_CODE
      FROM
        SY_REAS_CDS
      WHERE FLOW_TYPE = 0 )
      AND T.TRANS_DATE >= (
      SELECT
        BEGIN_DATE
      FROM
        IC_CLDR_HDR
      WHERE ORGN_CODE = T.ORGN_CODE
        AND FISCAL_YEAR = FISCAL_YEAR )
      AND T.TRANS_DATE <= TO_DATE
      AND T.DOC_TYPE = 'PROD'
      AND M.LINE_TYPE = - 1
      AND T.DOC_ID = M.BATCH_ID
      AND T.LINE_ID = M.LINE_ID
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null;

Line: 146

    SELECT
      SUM(T.TRANS_QTY)
    INTO REAS_TRANS_QTY
    FROM
      IC_TRAN_CMP T,
      SY_REAS_CDS R
    WHERE T.TRANS_DATE >= (
      SELECT
        BEGIN_DATE
      FROM
        IC_CLDR_HDR
      WHERE ORGN_CODE = T.ORGN_CODE
        AND FISCAL_YEAR = FISCAL_YEAR )
      AND T.TRANS_DATE <= TO_DATE
      AND R.FLOW_TYPE = 0
      AND T.REASON_CODE = R.REASON_CODE
      AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null
      AND R.DELETE_MARK = 0;

Line: 171

    SELECT
      SUM(T.TRANS_QTY)
    INTO PENDING_TRANS_QTY
    FROM
      IC_TRAN_PND T,
      PM_MATL_DTL M
    WHERE T.TRANS_DATE >= (
      SELECT
        BEGIN_DATE
      FROM
        IC_CLDR_HDR
      WHERE ORGN_CODE = T.ORGN_CODE
        AND FISCAL_YEAR = FISCAL_YEAR )
      AND T.TRANS_DATE <= TO_DATE
      AND T.DOC_TYPE = 'PROD'
      AND M.LINE_TYPE = - 1
      AND T.DOC_ID = M.BATCH_ID
      AND T.LINE_ID = M.LINE_ID
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null
      AND T.DELETE_MARK = 0
      AND T.COMPLETED_IND = 1;

Line: 198

    SELECT
      SUM(T.TRANS_QTY)
    INTO REASP_TRANS_QTY
    FROM
      IC_TRAN_PND T,
      SY_REAS_CDS R
    WHERE T.TRANS_DATE >= (
      SELECT
        BEGIN_DATE
      FROM
        IC_CLDR_HDR
      WHERE ORGN_CODE = T.ORGN_CODE
        AND FISCAL_YEAR = FISCAL_YEAR )
      AND T.TRANS_DATE <= TO_DATE
      AND R.FLOW_TYPE = 0
      AND T.REASON_CODE = R.REASON_CODE
      AND T.DOC_TYPE in ( 'ADJI' , 'ADJR' , 'PICY' , 'PIPH' , 'REPI' , 'REPR' )
      AND T.ITEM_ID = ITEM_ID_1
      AND T.WHSE_CODE = WHSE_CODE_1
      AND DOC_TYPE_INCP is null
      AND DOC_TYPE_OUTCP is null
      AND R.DELETE_MARK = 0
      AND T.DELETE_MARK = 0
      AND T.COMPLETED_IND = 1;

Line: 250

    select 'TRUE' into return_flag from dual
    where reason_code_1 in (select reason_code from sy_reas_cds where flow_type in (1,-1));

Line: 261

    select 'TRUE' into return_flag from dual
    where doc_type_1 in ('PORD','RECV','CREI','CRER','FPO','REQ')
    or    (doc_type_1 in ('TRNI','TRNR')and quantity_1 > 0)
    or    (doc_type_1 ='PROD' and line_id_1 in (select line_id from pm_matl_dtl
	   where line_type in (1,2) and batch_id = doc_id_1 and line_id=line_id_1))
    or    (doc_type_1 in ('ADJI','ADJR','PICY','PIPH','REPI','REPR') and reason_code_1
	   in (select reason_code from sy_reas_cds where flow_type=1)) ;

Line: 276

   select 'TRUE' into return_flag from dual
    where doc_type_1 in ('OPCR','OPSO','OPSP')
    or    (doc_type_1 in ('TRNI','TRNR','MTRI')and quantity_1 < 0)
    or    (doc_type_1 ='PROD' and line_id_1 in (select line_id from pm_matl_dtl
	   where line_type =-1 and batch_id = doc_id_1 and line_id=line_id_1))
    or    (doc_type_1 in ('ADJI','ADJR','PICY','PIPH','REPI','REPR') and reason_code_1
	   in (select reason_code from sy_reas_cds where flow_type=-1)) ;