APPS.PO_PALT_MS_COMPLETION SQL Statements

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

Line: 9

    SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Solicit_Creation_Dt
    FROM   ( SELECT MAX(NVL(PAIP.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
          FROM    PO_REQUISITION_LINES_ALL PRL,
                  PON_BACKING_REQUISITIONS PBR,
                  PON_AUCTION_ITEM_PRICES_ALL PAIP
          WHERE   PRL.REQUISITION_LINE_ID = PBR.REQUISITION_LINE_ID (+)
          AND     NVL(PRL.CLM_INFO_FLAG,'N') <> 'Y'
          AND     PBR.AUCTION_HEADER_ID   = PAIP.AUCTION_HEADER_ID (+)
          AND     PBR.LINE_NUMBER         = PAIP.LINE_NUMBER (+)
          AND     PRL.ASSIGNMENT_NUMBER   = pAssignment_Number
          );

Line: 35

    SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Solicit_Publish_Dt
    FROM   ( SELECT MAX(NVL(PUBLISH_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
          FROM    PO_REQUISITION_LINES_ALL PRLA,
                  PON_BACKING_REQUISITIONS PBR ,
                  PON_AUCTION_HEADERS_ALL PAHA
          WHERE   PRLA.REQUISITION_LINE_ID = PBR.REQUISITION_LINE_ID(+)
          AND     NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
          AND     PBR.AUCTION_HEADER_ID    = PAHA.AUCTION_HEADER_ID (+)
          AND     PAHA.AUCTION_STATUS (+)     = 'ACTIVE'
          AND     PRLA.ASSIGNMENT_NUMBER   = pAssignment_Number
          );

Line: 61

    SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Award_Creation_Dt
    FROM   (
    SELECT MAX(NVL(PLLA.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
    FROM   PO_REQUISITION_LINES_ALL PRLA,
           PO_REQ_DISTRIBUTIONS_ALL PRD,
           PO_DISTRIBUTIONS_ALL PD,
          PO_LINE_LOCATIONS_ALL PLLA
    WHERE  PRD.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
    AND    PRD.DISTRIBUTION_ID=PD.REQ_DISTRIBUTION_ID(+)
    AND    PD.LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID(+)
    AND    NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
    AND    PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
   UNION
   SELECT MAX(NVL(PLL.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
    FROM   PO_REQUISITION_LINES_ALL PRLA,
           PO_LINES_ALL PLL
    WHERE  PRLA.PO_LINE_ID = PLL.PO_LINE_ID(+)
    AND    NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
    AND    ((nvl(PRLA.CLM_OPTION_INDICATOR,'N')='O' and nvl(PRLA.CLM_OPTION_EXERCISED,'N')='N') OR nvl(PRLA.FUND_SOURCE_NOT_KNOWN,'N')='Y')
    AND    PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
   UNION
   SELECT MAX(NVL(PLLDA.CREATION_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
    FROM   PO_REQUISITION_LINES_ALL PRLA,
           PO_REQ_DISTRIBUTIONS_ALL PRD,
           PO_DISTRIBUTIONS_DRAFT_ALL PDD,
           PO_LINE_LOCATIONS_DRAFT_ALL PLLDA
    WHERE  PRD.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
    AND    PRD.DISTRIBUTION_ID=PDD.REQ_DISTRIBUTION_ID
    AND    PDD.LINE_LOCATION_ID=PLLDA.LINE_LOCATION_ID
    AND    PDD.CHANGE_STATUS='NEW'
    AND    NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
    AND    PRLA.ASSIGNMENT_NUMBER = pAssignment_Number
   );

Line: 108

    SELECT DECODE(val,TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), to_date(NULL), val) INTO L_Award_Approval_Dt
    FROM   ( SELECT MAX(NVL(PLLA.APPROVED_DATE, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))) val
    FROM   PO_REQUISITION_LINES_ALL PRLA,
           PO_REQ_DISTRIBUTIONS_ALL PRD,
           PO_DISTRIBUTIONS_ALL PD,
           PO_LINE_LOCATIONS_ALL PLLA
    WHERE  PRD.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
    AND    PRD.DISTRIBUTION_ID=PD.REQ_DISTRIBUTION_ID
    AND    PD.LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID
    AND    NVL(PRLA.CLM_INFO_FLAG,'N') <> 'Y'
    AND    PLLA.APPROVED_FLAG(+)            = 'Y'
    AND    PRLA.ASSIGNMENT_NUMBER = pAssignment_Number);

Line: 148

SELECT ASSIGNMENT_NUMBER,ASSIGNMENT_ID FROM PO_CLM_ASSIGNMENTS
WHERE ASSIGNMENT_STATUS IN (1,2,7);

Line: 152

SELECT MILESTONE_CODE FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
WHERE MILESTONE_TYPE = 'S'
AND ACTUAL_COMPLETION_DATE IS NULL
AND ASSIGNMENT_ID = l_Assignment_Id;

Line: 185

         UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
         SET ACTUAL_COMPLETION_DATE = L_Solicit_Creation_Dt,
             last_updated_by       = fnd_global.user_id  ,
             last_update_date      = SYSDATE             ,
             last_update_login     = fnd_global.login_id
         WHERE ASSIGNMENT_ID = l_Assignment_Id
         and MILESTONE_CODE = 'SOCREATE' ;

Line: 205

         UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
         SET ACTUAL_COMPLETION_DATE = L_Solicit_Publish_Dt,
             last_updated_by       = fnd_global.user_id  ,
             last_update_date      = SYSDATE             ,
             last_update_login     = fnd_global.login_id
         WHERE ASSIGNMENT_ID = l_Assignment_Id
         and MILESTONE_CODE = 'SOPUBLIS' ;

Line: 225

         UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
         SET ACTUAL_COMPLETION_DATE = L_Award_Creation_Dt,
             last_updated_by       = fnd_global.user_id  ,
             last_update_date      = SYSDATE             ,
             last_update_login     = fnd_global.login_id
         WHERE ASSIGNMENT_ID = l_Assignment_Id
         and MILESTONE_CODE = 'AWCREATE' ;

Line: 247

         UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
         SET ACTUAL_COMPLETION_DATE = L_Award_Approval_Dt,
             last_updated_by       = fnd_global.user_id  ,
             last_update_date      = SYSDATE             ,
             last_update_login     = fnd_global.login_id
         WHERE ASSIGNMENT_ID = l_Assignment_Id
         and MILESTONE_CODE = 'AWAPPROV' ;

Line: 261

     /* Added code to update completion dates for all milestones other than the 4 seeded milestones.*/
     SELECT Count(*)
     INTO l_incomp_MS_count
     FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
     WHERE ((MILESTONE_TYPE = 'S' AND MILESTONE_CODE NOT IN ('SOCREATE','SOPUBLIS','AWCREATE','AWAPPROV'))
            OR MILESTONE_TYPE = 'U')
     AND ACTUAL_COMPLETION_DATE IS NULL
     AND ASSIGNMENT_ID = l_Assignment_Id;

Line: 272

         PO_PALT_MILESTONE_CMPL_HOOK.Custom_Update_MS_Completion_Dt(l_Assignment_Number);

Line: 279

         SELECT Count(*)
         INTO l_incomplete_MS_Count
         FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
         WHERE ASSIGNMENT_ID = l_Assignment_Id
         and ACTUAL_COMPLETION_DATE is NULL ;

Line: 287

             UPDATE PO_CLM_ASSIGNMENTS
             SET ACTUAL_COMPLETION_DATE = (SELECT Max(ACTUAL_COMPLETION_DATE)
                                             FROM PO_CLM_ASSGNMT_MILESTONE_DTLS
                                            WHERE ASSIGNMENT_ID = l_Assignment_Id) ,
                 ASSIGNMENT_STATUS = 4,
             last_updated_by       = fnd_global.user_id  ,
             last_update_date      = SYSDATE             ,
             last_update_login     = fnd_global.login_id
             WHERE ASSIGNMENT_ID = l_Assignment_Id;