PACKAGE BODY: APPS.CSTACOSN

Source

1 PACKAGE BODY CSTACOSN AS
2 /* $Header: CSTACOSB.pls 115.4 2002/11/08 01:15:21 awwang ship $ */
3 
4 FUNCTION op_snapshot(
5           I_TXN_TEMP_ID       IN      NUMBER,
6           ERR_NUM             OUT NOCOPY     NUMBER,
7           ERR_CODE            OUT NOCOPY     VARCHAR2,
8           ERR_MSG             OUT NOCOPY     VARCHAR2)
9 RETURN INTEGER
10 is
11    stmt_num			NUMBER;
12    l_txn_temp_id		NUMBER;
13    l_wip_entity_id		NUMBER;
14    l_primary_quantity	 	NUMBER;
15    l_operation_seq_num		NUMBER;
16    l_transaction_action_id	NUMBER;
17 
18    CURSOR completion IS
19    SELECT
20       transaction_temp_id,
21       transaction_source_id,
22       primary_quantity,
23       operation_seq_num,
24       transaction_action_id
25    FROM
26       mtl_material_transactions_temp
27    WHERE
28       transaction_temp_id = i_txn_temp_id;
29 
30 BEGIN
31 
32    SAVEPOINT op_snapshot_1;
33 
34    OPEN completion;
35    FETCH completion INTO l_txn_temp_id,
36 	    	         l_wip_entity_id,
37 			 l_primary_quantity,
38 			 l_operation_seq_num,
39 			 l_transaction_action_id;
40    CLOSE completion;
41 
42    stmt_num := 10;
43 
44    INSERT INTO cst_comp_snap_temp
45       (transaction_temp_id,
46        wip_entity_id,
47        operation_seq_num,
48        last_update_date,
49        last_updated_by,
50        creation_date,
51        created_by,
52        last_update_login,
53        new_operation_flag,
54        primary_quantity,
55        quantity_completed,
56        prior_completion_quantity,
57        prior_scrap_quantity,
58        request_id,
59        program_application_id,
60        program_id,
61        program_update_date)
62    SELECT
63       l_txn_temp_id,
64       l_wip_entity_id,
65       operation_seq_num,
66       sysdate,
67       -1,
68       sysdate,
69       -1,
70       -1,
71       2,
72        --
73        -- Bug 608310
74        -- If the transaction is a scrap transaction
75        -- Operation after the scrap operation should be
76        -- primary_quantity of zero.
77        --
78        decode(l_transaction_action_id,
79 	      30,
80 	      decode(sign(operation_seq_num - l_operation_seq_num),
81 		     1,0,
82 		     l_primary_quantity),
83 	      l_primary_quantity),
84        quantity_completed,
85        0,
86        0,
87        -1,
88        -1,
89        -1,
90        sysdate
91     FROM
92        wip_operations wo
93     WHERE
94        wo.wip_entity_id = l_wip_entity_id;
95 
96 
97    RETURN(1);
98 
99  EXCEPTION
100 
101  WHEN OTHERS THEN
102 
103     ROLLBACK TO op_snapshot_1;
104     err_num := SQLCODE;
105     err_msg := 'CSTACOSN:op_snapshot' || to_char(stmt_num) || substr(SQLERRM,1,150);
106     return(-999);
107 
108  END op_snapshot;
109 
110  END CSTACOSN;