APPS.CST_RES_COST_IMPORT_INTERFACE SQL Statements

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

Line: 25

Update CST_RESOURCE_COSTS_INTERFACE crci
SET error_flag = 'E',
    error_code = 'CST_NULL_ORGANIZATION',
    error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ORGANIZATION'),1,240)
where (Organization_id is null AND organization_code is null)
AND error_flag is null
AND crci.group_id = SEQ_NEXTVAL;

Line: 38

Update CST_RESOURCE_COSTS_INTERFACE crci
SET error_flag = 'E',
    error_code = 'CST_INVALID_ORGANIZATION',
    error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ORGANIZATION'),1,240)
WHERE crci.error_flag is null
AND crci.group_id = SEQ_NEXTVAL
AND NOT EXISTS (select 1 from mtl_parameters mp
                where NVL(crci.organization_id,mp.organization_id) = mp.organization_id
                AND NVL(crci.organization_code,mp.organization_code) = mp.organization_code
                );

Line: 54

Update CST_RESOURCE_COSTS_INTERFACE crci
SET organization_id = (select organization_id
                       FROM mtl_parameters mp
                       WHERE mp.organization_code = crci.organization_code
                       )
WHERE crci.organization_id is null
AND crci.error_flag is null
AND crci.group_id = SEQ_NEXTVAL;

Line: 69

Update CST_RESOURCE_COSTS_INTERFACE ct
SET error_flag = 'E',
    error_code = 'CST_PROCESS_ORG_ERROR',
    error_explanation =
substrb(fnd_message.get_string(
    'GMF','GMF_PROCESS_ORG_ERROR'),1,240)
WHERE ct.error_flag is null
AND   ct.group_id = SEQ_NEXTVAL
AND EXISTS (select 'This is a process manufacturing org'
            from   mtl_parameters mp
            where  mp.organization_id = ct.organization_id
            AND    NVL(mp.process_enabled_flag, 'N') = 'Y'
           )
;

Line: 91

Update CST_RESOURCE_COSTS_INTERFACE crci
SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,
    error_code =null,
    error_explanation = null,
    request_id = FND_GLOBAL.CONC_REQUEST_ID,
    program_application_id = FND_GLOBAL.PROG_APPL_ID,
    program_id = FND_GLOBAL.CONC_PROGRAM_ID,
    program_update_date = sysdate,
    process_flag = 2
where group_id=SEQ_NEXTVAL
AND error_flag is null
AND process_flag = 1;

Line: 111

UPDATE CST_RESOURCE_COSTS_INTERFACE crci
set crci.Error_flag = 'E',
    crci.Error_code = 'CST_NOT_COSTINGORG',
    crci.Error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_COSTINGORG'),1,240)
WHERE crci.group_id = SEQ_NEXTVAL
AND crci.error_flag is null
AND EXISTS ( Select 1 from mtl_parameters mp
             WHERE mp.cost_organization_id <> mp.organization_id
             AND mp.organization_id = crci.organization_id);

Line: 127

Update CST_RESOURCE_COSTS_INTERFACE crci
SET crci.cost_type_id = (select cost_type_id from CST_COST_TYPES cct
                    WHERE cct.cost_type =  i_new_csttype
                    ),
    crci.cost_type = i_new_csttype
WHERE crci.group_id = SEQ_NEXTVAL
AND crci.error_flag is null;

Line: 141

Update CST_RESOURCE_COSTS_INTERFACE crci
SET crci.error_flag = 'E',
    crci.error_code = 'CST_NULL_SUBELEMENT',
    crci.error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_SUBELEMENT'),1,240)
WHERE crci.error_flag is null
AND crci.group_id = SEQ_NEXTVAL
AND (crci.resource_id is null AND crci.resource_code is null);

Line: 153

Update CST_RESOURCE_COSTS_INTERFACE crci set
  error_flag = 'E',
  error_code = 'CST_INVALID_RESOURCE',
  error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_RESOURCE'),1,240)
WHERE crci.error_flag is null
AND crci.group_id = SEQ_NEXTVAL
AND NOT EXISTS (select 1 from bom_resources bm
                WHERE NVL(crci.resource_id,bm.resource_id)=bm.resource_id
                AND NVL(crci.resource_code,bm.resource_code)=bm.resource_code
                AND (bm.cost_element_id = 3 OR bm.cost_element_id = 4)
                AND bm.functional_currency_flag <> 1
                AND crci.organization_id = bm.organization_id
               );

Line: 171

Update CST_RESOURCE_COSTS_INTERFACE crci set
   crci.resource_id = (select bm.resource_id from bom_resources bm
                       WHERE bm.resource_code = crci.resource_code
                       AND (bm.cost_element_id = 3 or bm.cost_element_id = 4)
                       AND bm.functional_currency_flag <> 1
                       AND bm.organization_id = crci.organization_id)
WHERE crci.error_flag is null
AND crci.resource_id is null
AND  crci.group_id = SEQ_NEXTVAL;

Line: 186

Update CST_RESOURCE_COSTS_INTERFACE crci
set crci.error_flag = 'E',
    crci.error_code = 'CST_EXP_SUBELEMENT',
    crci.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
where crci.error_flag is null
AND crci.group_id = SEQ_NEXTVAL
AND EXISTS ( select 1 from BOM_RESOURCES bm
             WHERE bm.organization_id = crci.organization_id
             AND (bm.cost_element_id = 3 OR bm.cost_element_id = 4)
             AND bm.resource_id = crci.resource_id
             AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2)));

Line: 204

UPDATE CST_RESOURCE_COSTS_INTERFACE crci
set error_flag = 'E',
    error_code = 'CST_NULL_RESRT',
    error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_RESRT'),1,240)
where crci.resource_rate is null
and crci.error_flag is null
and crci.group_id = SEQ_NEXTVAL;

Line: 213

Update CST_RESOURCE_COSTS_INTERFACE crci
set process_flag = 3
WHERE process_flag = 2
AND group_id = SEQ_NEXTVAL
AND error_flag is null;

Line: 223

  Update cst_resource_costs_interface crci
  set crci.error_flag = 'E',
      crci.error_code = 'CST_DUPL_ROWS',
      crci.error_explanation = substrb(fnd_message.get_string('BOM','CST_DUPL_ROWS'),1,240)
  where crci.error_flag is null
  AND crci.group_id = SEQ_NEXTVAL
  AND EXISTS (select 1 from cst_resource_costs_interface crci2
              WHERE  crci.resource_id = crci2.resource_id
              AND crci.organization_id = crci2.organization_id
              AND crci.cost_type_id = crci2.cost_type_id
              AND crci.rowid <> crci2.rowid
              AND crci2.group_id = SEQ_NEXTVAL);

Line: 238

Update CST_RESOURCE_COSTS_INTERFACE crci
set process_flag = 4
WHERE crci.process_flag = 3
AND crci.error_flag is null
AND crci.group_id = SEQ_NEXTVAL;

Line: 248

/* Now begin inserting rows into CST_RESOURCE_COSTS */

/* first check the run option */

 if i_run_option = 2 then
 l_stmt_no := 115;

Line: 255

 Delete from CST_RESOURCE_COSTS crc
 where EXISTS (select 1 from CST_RESOURCE_COSTS_INTERFACE crci
               where crci.organization_id = crc.organization_id
               AND crci.cost_type_id = crc.cost_type_id
               AND crci.resource_id = crc.resource_id
               AND crci.error_flag is null
               AND crci.group_id = SEQ_NEXTVAL);

Line: 265

 UPDATE CST_RESOURCE_COSTS_INTERFACE crci set
 error_flag ='E',
 error_code = 'CST_CANT_INSERT',
 error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
 where crci.error_flag is null
 AND crci.group_id = SEQ_NEXTVAL
 AND EXISTS (Select 1 from CST_RESOURCE_COSTS crc
             where crc.organization_id = crci.organization_id
             AND crc.cost_type_id = crci.cost_type_id
             AND crc.resource_id = crci.resource_id
             );

Line: 282

INSERT INTO CST_RESOURCE_COSTS(Resource_id,
                               cost_type_id,
                               last_update_date,
                               last_updated_by,
                               creation_date,
                               created_by,
                               organization_id,
                               Resource_rate,
                               request_id,
                               program_application_id,
                               program_id,
                               program_update_date,
                               Attribute_category,
                               attribute1,
                               attribute2,
                               attribute3,
                               attribute4,
                               attribute5,
                               attribute6,
                               attribute7,
                               attribute8,
                               attribute9,
                               attribute10,
                               attribute11,
                               attribute12,
                               attribute13,
                               attribute14,
                               attribute15)
                        SELECT resource_id,
                               cost_type_id,
                               sysdate,
                               FND_GLOBAL.USER_ID,
                               sysdate,
                               FND_GLOBAL.USER_ID,
                               organization_id,
                               resource_rate,
                               FND_GLOBAL.CONC_REQUEST_ID,
                               FND_GLOBAL.PROG_APPL_ID,
                               FND_GLOBAL.CONC_PROGRAM_ID,
                               sysdate,
                               attribute_category,
                               attribute1,
                               attribute2,
                               attribute3,
                               attribute4,
                               attribute5,
                               attribute6,
                               attribute7,
                               attribute8,
                               attribute9,
                               attribute10,
                               attribute11,
                               attribute12,
                               attribute13,
                               attribute14,
                               attribute15
                          FROM CST_RESOURCE_COSTS_INTERFACE
                          WHERE error_flag is null
                          AND group_id = SEQ_NEXTVAL;

Line: 341

fnd_file.put_line(fnd_file.log,'done inserting ' || to_char(SQL%ROWCOUNT) ||' rows into CST_RESOURCE_COSTS');

Line: 345

UPDATE CST_RESOURCE_COSTS_INTERFACE
 set process_flag = 5
 where process_flag = 4
 and error_flag is null
 and group_id = SEQ_NEXTVAL;

Line: 352

 delete from CST_RESOURCE_COSTS_INTERFACE
 WHERE process_flag = 5
 AND error_flag is null
 AND group_id = SEQ_NEXTVAL;

Line: 389

    UPDATE CST_RESOURCE_COSTS_INTERFACE crci
    SET group_id = i_grp_id
    where process_flag = 1
    AND error_flag is null;

Line: 395

Select count(*) into l_crci_count
FROM CST_RESOURCE_COSTS_INTERFACE crci
where  crci.group_id = i_grp_id
AND crci.error_flag is null
AND crci.process_flag = 1
AND rownum =1;

Line: 414

select count(*) into i_count from CST_RESOURCE_COSTS_INTERFACE
where group_id = i_grp_id
and error_flag ='E';