APPS.CSTPOYUT SQL Statements

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

Line: 28

Function update_mat_cost (i_cost_type_id            IN   NUMBER,
                          i_txn_id                  IN   NUMBER,
                          i_org_id                  IN   NUMBER,
                          i_op_seq_num              IN   NUMBER,
                          i_item_id                 IN   NUMBER,
                          i_txn_qty                 IN   NUMBER,
                          i_entity_id               IN   NUMBER,
                          i_entity_type             IN   NUMBER,
                          i_user_id                 IN   NUMBER,
                          i_login_id                IN   NUMBER,
                          i_prg_appl_id             IN   NUMBER,
                          i_prg_id                  IN   NUMBER,
                          i_req_id                  IN   NUMBER)
RETURN Number IS

/* Changes for Optional Scrap */
x_est_scrap_acct_flag		NUMBER := 0;

Line: 65

    /* Update WIP_OPERATION_YIELDS */

        UPDATE wip_operation_yields woy
        SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
            REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
            operation_cost,
            status )=
        (SELECT
            SYSDATE, i_user_id, i_login_id,
            DECODE(i_req_id, -1, NULL, i_req_id),
            DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
            DECODE(i_prg_id, -1, NULL, i_prg_id),
            DECODE(i_req_id, -1, NULL, SYSDATE),
            (NVL(woy.operation_cost, 0) +
            (NVL(cic.material_cost,0) +
             NVL(cic.material_overhead_cost,0)  +
             NVL(cic.resource_cost,0)  +
             NVL(cic.outside_processing_cost,0) +
             NVL(cic.overhead_cost,0)) * -1 * i_txn_qty) ,
               1
        FROM  cst_item_costs cic,  mtl_parameters mp
        WHERE cic.inventory_item_id = i_item_id
        AND   cic.organization_id = mp.cost_organization_id
        AND   mp.organization_id = i_org_id
        AND   cic.cost_type_id = i_cost_type_id
        )
        WHERE  woy.wip_entity_id = i_entity_id
        AND    woy.organization_id = i_org_id
        AND    woy.operation_seq_num = i_op_seq_num
        AND    EXISTS
            (SELECT 'Check if the item has cost'
             FROM   CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
             WHERE  CIC.INVENTORY_ITEM_ID = i_item_id
             AND    CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
             AND    MP.ORGANIZATION_ID = i_org_id
             AND    CIC.COST_TYPE_ID = i_cost_type_id);

Line: 118

END update_mat_cost;

Line: 140

Function update_wip_cost (i_group_id       IN   NUMBER,
                          i_user_id        IN   NUMBER,
                          i_login_id       IN   NUMBER,
                          i_prg_appl_id    IN   NUMBER,
                          i_prg_id         IN   NUMBER,
                          i_req_id         IN   NUMBER,
                          o_err_msg      OUT NOCOPY  VARCHAR2)
return Number IS

/* Changes for Optional Scrap */
x_err_num NUMBER := 0;

Line: 156

SELECT distinct we.wip_entity_id
FROM wip_entities we, wip_transactions wt
WHERE we.wip_entity_id = wt.wip_entity_id
AND we.entity_type = 5
AND wt.group_id = i_group_id;

Line: 165

         /* Update Wip_operation_yields */

 	 /* Changes for Optional Scrap */
    	 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(c_we_rec.wip_entity_id, x_err_num, x_err_msg);

Line: 174

           UPDATE wip_operation_yields woy
           SET    (operation_cost,
                status,
                REQUEST_ID, PROGRAM_APPLICATION_ID,
                PROGRAM_ID, PROGRAM_UPDATE_DATE,
                LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) =
           (SELECT NVL(woy.operation_cost, 0) +
                    NVL(sum(acct.base_transaction_value), 0), 1,
                    i_req_id, i_prg_appl_id, i_prg_id, SYSDATE,
                    SYSDATE, i_user_id, i_login_id
            FROM    wip_transaction_accounts acct,
                    wip_transactions wt,
                    wip_entities we
            WHERE   we.wip_entity_id=c_we_rec.wip_entity_id
            AND     we.wip_entity_id = wt.wip_entity_id
	    AND     we.entity_type = 5
            AND     wt.wip_entity_id = woy.wip_entity_id
            AND     wt.organization_id = woy.organization_id
            AND     wt.operation_seq_num = woy.operation_seq_num
            AND     wt.transaction_id = acct.transaction_id
            AND     acct.accounting_line_type = 7
	    AND     wt.group_id = i_group_id
            )
            WHERE
                (woy.wip_entity_id, woy.operation_seq_num,
                 woy.organization_id )
            IN
                (SELECT wt.wip_entity_id, wt.operation_seq_num,
                        wt.organization_id
                 FROM   wip_transactions wt,
                        wip_transaction_accounts acct,
			wip_entities we
                 WHERE  wt.wip_entity_id=c_we_rec.wip_entity_id
                 AND    wt.transaction_id = acct.transaction_id
		 AND    acct.accounting_line_type = 7
		 AND    we.wip_entity_id = wt.wip_entity_id
		 AND    we.entity_type = 5
		 AND    wt.group_id = i_group_id
                 );

Line: 225

        o_err_msg := 'CSTPOYUT.update_wip_cost' ||
                        substr(SQLERRM,1,150);

Line: 228

END update_wip_cost;

Line: 252

Function update_woy_status (i_org_id              NUMBER,
                            i_wip_entity_id       NUMBER,
                            i_op_seq_num          NUMBER,
                            i_user_id        IN   NUMBER,
                            i_login_id       IN   NUMBER,
                            i_prg_appl_id    IN   NUMBER,
                            i_prg_id         IN   NUMBER,
                            i_req_id         IN   NUMBER,
                            o_err_num        OUT NOCOPY  NUMBER,
                            o_err_code     OUT NOCOPY  VARCHAR2,
                            o_err_msg      OUT NOCOPY  VARCHAR2)
return NUMBER IS

/* Changes for Optional Scrap */
x_est_scrap_acct_flag	NUMBER := 0;

Line: 287

    /* Update WIP_OPERATION_YIELDS */

        UPDATE wip_operation_yields
        SET  LAST_UPDATE_DATE = SYSDATE,
             LAST_UPDATED_BY = i_user_id,
             LAST_UPDATE_LOGIN = i_login_id,
             REQUEST_ID = DECODE(i_req_id, -1, NULL, i_req_id),
             PROGRAM_APPLICATION_ID = DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
             PROGRAM_ID = DECODE(i_prg_id, -1, NULL, i_prg_id),
             PROGRAM_UPDATE_DATE = DECODE(i_req_id, -1, NULL, SYSDATE),
             status  = 1
        WHERE  wip_entity_id = i_wip_entity_id
        AND    organization_id = i_org_id
        AND    operation_seq_num = i_op_seq_num ;

Line: 313

      o_err_msg := 'CSTPOYUT.update_woy_status:' || substrb(o_err_msg,1,150);

Line: 317

    o_err_msg := 'CSTPOYUT.update_woy_status:' || substrb(SQLERRM,1,150);

Line: 319

END update_woy_status;