[Home] [Help]
PACKAGE BODY: APPS.CSTPWCPX
Source
1 PACKAGE BODY CSTPWCPX AS
2 /* $Header: CSTPCPXB.pls 120.0 2005/05/25 05:13:56 appldev noship $ */
3
4 FUNCTION CMLCPX (
5 i_group_id IN NUMBER,
6 i_org_id IN NUMBER,
7 i_transaction_type IN NUMBER,
8 i_user_id IN NUMBER,
9 i_login_id IN NUMBER,
10 i_prg_appl_id IN NUMBER,
11 i_prg_id IN NUMBER,
12 i_req_id IN NUMBER,
13 err_buf OUT NOCOPY VARCHAR2)
14 RETURN INTEGER
15 IS
16
17 where_num NUMBER;
18 l_cost_type_id NUMBER;
19
20 BEGIN
21
22 err_buf := ' ';
23
24 /*---------------------------------------------------------------+
25 | Get the Cost Type associated with the organization |
26 +----------------------------------------------------------------*/
27 where_num := 50;
28
29 SELECT DECODE(PRIMARY_COST_METHOD, 1, 1,
30 NVL(AVG_RATES_COST_TYPE_ID,-1))
31 INTO l_cost_type_id
32 FROM MTL_PARAMETERS
33 WHERE ORGANIZATION_ID = i_org_id;
34
35 /*--------------------------------------------------------------+
36 | Copy rows from wip_cost_txn_interface to wip_transactions. |
37 | Copy NULL transaction quantity for IPV transfer transactions. |
38 | Copy the values charge_dept_id and instance_id for EAM support|
39 +---------------------------------------------------------------*/
40 where_num := 100;
41 INSERT INTO WIP_TRANSACTIONS
42 (TRANSACTION_ID, LAST_UPDATE_DATE,
43 LAST_UPDATED_BY, CREATION_DATE,
44 CREATED_BY, LAST_UPDATE_LOGIN,
45 ORGANIZATION_ID, WIP_ENTITY_ID,
46 ACCT_PERIOD_ID, DEPARTMENT_ID,
47 TRANSACTION_TYPE, TRANSACTION_DATE,
48 LINE_ID, SOURCE_CODE,
49 SOURCE_LINE_ID, OPERATION_SEQ_NUM,
50 RESOURCE_SEQ_NUM, EMPLOYEE_ID,
51 RESOURCE_ID, AUTOCHARGE_TYPE,
52 STANDARD_RATE_FLAG, USAGE_RATE_OR_AMOUNT,
53 BASIS_TYPE, TRANSACTION_QUANTITY,
54 TRANSACTION_UOM, PRIMARY_QUANTITY,
55 PRIMARY_UOM, ACTUAL_RESOURCE_RATE,
56 STANDARD_RESOURCE_RATE, CURRENCY_CODE,
57 CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE,
58 CURRENCY_CONVERSION_RATE, CURRENCY_ACTUAL_RESOURCE_RATE,
59 ACTIVITY_ID, REASON_ID,
60 REFERENCE, MOVE_TRANSACTION_ID,
61 PO_HEADER_ID, PO_LINE_ID,
62 RCV_TRANSACTION_ID, PRIMARY_ITEM_ID,
63 ATTRIBUTE_CATEGORY,
64 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
65 ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,
66 ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
67 ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
68 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
69 GROUP_ID,
70 PROJECT_ID, TASK_ID,
71 PM_COST_COLLECTED, COMPLETION_TRANSACTION_ID,
72 CHARGE_DEPARTMENT_ID, INSTANCE_ID)
73 SELECT
74 wcti.TRANSACTION_ID, SYSDATE,
75 i_user_id, wcti.CREATION_DATE,
76 wcti.CREATED_BY, i_login_id,
77 wcti.ORGANIZATION_ID, wcti.WIP_ENTITY_ID,
78 wcti.ACCT_PERIOD_ID, wcti.DEPARTMENT_ID,
79 wcti.TRANSACTION_TYPE, wcti.TRANSACTION_DATE,
80 wcti.LINE_ID, wcti.SOURCE_CODE,
81 wcti.SOURCE_LINE_ID, wcti.OPERATION_SEQ_NUM,
82 wcti.RESOURCE_SEQ_NUM, wcti.EMPLOYEE_ID,
83 wcti.RESOURCE_ID, wcti.AUTOCHARGE_TYPE,
84 wcti.STANDARD_RATE_FLAG, wcti.USAGE_RATE_OR_AMOUNT,
85 wcti.BASIS_TYPE,
86 DECODE(wcti.SOURCE_CODE, 'IPV', NULL,
87 wcti.TRANSACTION_QUANTITY ),
88 wcti.TRANSACTION_UOM,
89 DECODE(wcti.SOURCE_CODE, 'IPV', NULL,
90 wcti.PRIMARY_QUANTITY ),
91 wcti.PRIMARY_UOM, wcti.ACTUAL_RESOURCE_RATE,
92 decode(i_transaction_type,
93 1, decode(br.functional_currency_flag,
94 1, 1,
95 nvl(crc.resource_rate,0)),
96 NULL),
97 wcti.CURRENCY_CODE,
98 wcti.CURRENCY_CONVERSION_DATE, wcti.CURRENCY_CONVERSION_TYPE,
99 wcti.CURRENCY_CONVERSION_RATE, wcti.CURRENCY_ACTUAL_RESOURCE_RATE,
100 wcti.ACTIVITY_ID, wcti.REASON_ID,
101 wcti.REFERENCE, wcti.MOVE_TRANSACTION_ID,
102 wcti.PO_HEADER_ID, wcti.PO_LINE_ID,
103 wcti.RCV_TRANSACTION_ID, wcti.PRIMARY_ITEM_ID,
104 wcti.ATTRIBUTE_CATEGORY,
105 wcti.ATTRIBUTE1,wcti.ATTRIBUTE2, wcti.ATTRIBUTE3,wcti.ATTRIBUTE4,
106 wcti.ATTRIBUTE5, wcti.ATTRIBUTE6, wcti.ATTRIBUTE7,wcti.ATTRIBUTE8,
107 wcti.ATTRIBUTE9,wcti.ATTRIBUTE10, wcti.ATTRIBUTE11,wcti.ATTRIBUTE12,
108 wcti.ATTRIBUTE13,wcti.ATTRIBUTE14,wcti.ATTRIBUTE15,
109 DECODE(i_req_id, -1, NULL, i_req_id),
110 DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
111 DECODE(i_prg_id, NULL, i_prg_id),
112 DECODE(i_req_id, -1, NULL, SYSDATE),
113 wcti.GROUP_ID,
114 wcti.PROJECT_ID, wcti.TASK_ID,
115 'N', wcti.COMPLETION_TRANSACTION_ID,
116 wcti.CHARGE_DEPARTMENT_ID, wcti.INSTANCE_ID
117 FROM wip_cost_txn_interface wcti,
118 bom_resources br,
119 cst_resource_costs crc
120 WHERE wcti.group_id = i_group_id
121 AND wcti.process_status = 2
122 AND ( (i_transaction_type = 1
123 AND wcti.transaction_type IN (1,3))
124 OR
125 (i_transaction_type <> 1
126 AND wcti.transaction_type = i_transaction_type)
127 )
128 AND wcti.resource_id = br.resource_id (+)
129 AND wcti.resource_id = crc.resource_id (+)
130 AND crc.cost_type_id (+) = l_cost_type_id;
131
132 /*---------------------------------------------------------------+
133 | Delete rows from wip_cost_txn_interface
134 +---------------------------------------------------------------*/
135 where_num := 200;
136 DELETE FROM wip_cost_txn_interface
137 WHERE group_id = i_group_id
138 AND ( (i_transaction_type = 1
139 AND transaction_type IN (1,3))
140 OR
141 (i_transaction_type <> 1
142 AND transaction_type = i_transaction_type)
143 )
144 AND process_status = 2;
145
146 RETURN(0);
147
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 ROLLBACK;
152 err_buf := 'CSTPWCPX:' || to_char(where_num) || substr(SQLERRM,1,150);
153 RETURN(SQLCODE);
154
155 END CMLCPX;
156
157 END CSTPWCPX; /* end package body */