1 package wip_wictpg as
2 /* $Header: wiptpgs.pls 120.1.12000000.1 2007/01/18 22:22:36 appldev ship $ */
3
4 -- Args:
5 -- p_report_key identifier used in query for delete report
6 -- p_org_id organization ID to delete from
7 -- p_cutoff_date date to start purge from (goes backward)
8 -- p_primary_item_id item ID to delete (finds jobs with this assembly)
9 -- p_conf_flag delete configuration item job data
10 -- p_header_flag delete job header record
11 -- p_detail_flag delete job details
12 -- p_move_trx_flag delete move transactions associated with job
13 -- p_cost_trx_flag delete resource transactions associated with job
14 -- p_option purge and report(1), or report(2), purge only(3)
15 -- p_commit_flag perform commits to prevent full rollback segments
16 --
17 -- Notes:
18 -- 1) For the data types of each parameter, refer to the specification
19 -- declaration of purge_job().
20 -- 2) Use p_report => 1 to get a report with the count of records
21 -- affected by the purge. It is highly recommended that you run the
22 -- report option w/o delete to get an idea of what is being purge
23 -- before actually running with p_option => 3 or 2.
24 -- 3) After the purge run, enter the following SQL to get the report:
25 -- SELECT DESCRIPTION
26 -- FROM WIP_TEMP_REPORTS
27 -- WHERE ATTRIBUTE1 = <REPORT KEY>
28 -- ORDER BY KEY1
29 -- where <REPORT KEY> is the value you entered for p_report_key.
30 -- Be sure to delete these records using
31 -- DELETE FROM WIP_TEMP_REPORTS WHERE ATTRIBUTE1 = <REPORT KEY>
32 -- 4) Conditions checked are:
33 -- o Org must be valid
34 -- o Cutoff date must be after the first recorded period and before
35 -- most recent closed period date
36 -- o If primary item ID is not null, then it must be valid
37 -- o You must delete details and transactions if deleting job header
38 -- o If deleting job headers then
39 -- o WIP_PERIOD_BALANCES must be zero charges
40 -- o no sales order can be linked to the job
41 -- o no foreign key references to WIP_ENTITY_ID in tables listed
42 -- at the end of this spec
43 -- 5) You may modify the internal function delete_job_header() to
44 -- customize whatever checks you need to delete the job header record.
45
46 PURGE_JOBS constant number := 1;
47 PURGE_SCHEDS constant number := 2;
48 PURGE_ALL constant number := 4;
49
50 /* Added for osfm purge job */
51
52 PURGE_LOTBASED constant number := 5;
53
54 type purge_report_type is record (
55 group_id number,
56 org_id number,
57 wip_entity_id number,
58 schedule_id number,
59 primary_item_id number,
60 line_id number,
61 start_date date,
62 complete_date date,
63 close_date date,
64 table_name varchar2(30),
65 info_type number,
66 info varchar2(240),
67 entity_name varchar2(240),
68 line_code varchar2(10)
69 );
70
71 cursor get_purge_requests(
72 c_purge_type number,
73 c_group_id number) is
74 select tmp.organization_id,
75 tmp.wip_entity_id,
76 we.entity_type,
77 tmp.repetitive_schedule_id,
78 tmp.primary_item_id,
79 tmp.line_id,
80 tmp.start_date,
81 tmp.complete_date,
82 tmp.close_date,
83 tmp.status_type,
84 decode(we.entity_type, 1, we.wip_entity_name,
85 3, we.wip_entity_name,
86 8, we.wip_entity_name,
87 2, NULL) wip_entity_name ,
88 wl.line_code
89 from Wip_Purge_Temp tmp,
90 wip_entities we,
91 wip_lines wl
92 where tmp.group_id = c_group_id
93 and we.wip_entity_id = tmp.wip_entity_id
94 and wl.line_id (+) = tmp.line_id
95 and ((tmp.repetitive_schedule_id is NULL
96 and
97 c_purge_type in (PURGE_JOBS, PURGE_LOTBASED, PURGE_ALL))
98 or
99 (tmp.repetitive_schedule_id is NOT NULL
100 and
101 c_purge_type in (PURGE_SCHEDS, PURGE_ALL)));
102
103 REPORT_ONLY constant number := 1;
104 PURGE_AND_REPORT constant number := 2;
105 PURGE_ONLY constant number := 3;
106
107 -- Info type
108 EXCEPTIONS constant number := 1;
109 ROWS_AFFECTED constant number := 2;
110
111
112 -- Bug 5129924
113 -- Added a new parameter
114 -- p_days_before_cutoff to the
115 -- function
116 -- ntungare Thu May 25 11:41:12 PDT 2006
117 --
118 function purge(
119 p_purge_type in number,
120 p_group_id in number,
121 p_org_id in number,
122 p_cutoff_date in date,
123 p_days_before_cutoff in number,
124 p_from_job in varchar2,
125 p_to_job in varchar2,
126 p_primary_item_id in number,
127 p_line_id in number,
128 p_option in number default NULL,
129 p_conf_flag in boolean default NULL,
130 p_header_flag in boolean default NULL,
131 p_detail_flag in boolean default NULL,
132 p_move_trx_flag in boolean default NULL,
133 p_cost_trx_flag in boolean default NULL,
134 p_err_num in out NOCOPY number,
135 p_error_text in out NOCOPY varchar2
136 ) return number ;
137
138
139 -- Below are non-WIP tables that have foreign key references to WIP_ENTITY_ID
140 -- (note that WIP tables also have foreign key references to WIP_ENTITY_ID
141 -- of WIP_DISCRETE_JOBS/WIP_ENTITIES. Please refer to the WIP Technical
142 -- Reference Manual for a list of those tables).
143 --
144 -- cst_std_cost_adj_values
145 -- (wip_entity_id)
146 -- cst_wip_entity_find
147 -- (wip_entity_id)
148 -- po_distributions
149 -- (wip_entity_id)
150 -- po_requisition_lines
151 -- (wip_entity_id)
152 -- qa_results
153 -- (wip_entity_id)
154 -- rcv_transactions
155 -- (wip_entity_id)
156 -- mtl_demand
157 -- (supply_source_type = 5, supply_source_header_id)
158 -- mtl_user_supply
159 -- (source_type_id = 5, source_id)
160 -- mtl_user_demand
161 -- (source_type_id = 5, source_id)
162 -- mtl_serial_numbers
163 -- (original_wip_entity_id)
164 -- mtl_material_transactions
165 -- (transaction_source_type_id = 5, transaction_source_id)
166 -- mtl_transaction_accounts
167 -- (transaction_source_type_id = 5, transaction_source_id)
168 -- mtl_transaction_lot_numbers
169 -- (transaction_source_type_id = 5, transaction_source_id)
170 -- mtl_unit_transactions
171 -- (transaction_source_type_id = 5, transaction_source_id)
172 --
173 -- Below are interface tables that have foreign key references to
174 -- WIP_ENTITY_ID. However, purge will not check against these table when
175 -- delete the job header.
176 --
177 -- cst_period_value_temp
178 -- (wip_entity_id)
179 -- cst_std_cost_adj_temp
180 -- (wip_entity_id)
181 -- po_requisitions_interface
182 -- (wip_entity_id)
183 -- rcv_transactions_interface
184 -- (wip_entity_id)
185 -- mrp_relief_interface
186 -- (dispostion_type = 1, disposition_id)
187 -- mtl_demand_interface
188 -- (supply_source_type = 5, supply_header_id)
189 -- mtl_material_transactions_temp
190 -- (transaction_source_type_id = 5, transaction_source_id)
191 -- mtl_transactions_interface
192 -- (transaction_source_type_id = 5, transaction_source_id)
193
194 end wip_wictpg;