DBA Data[Home] [Help]

PACKAGE: APPS.WIP_WICTPG

Source


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;